AnsweredAssumed Answered

Gantt link on portlet error

Question asked by DZimmermann on Oct 18, 2016
Latest reply on Oct 18, 2016 by DZimmermann

Hi guys,

 

I'm facing a bizarre error here. I made a multi-level query to a portlet with a Gantt link. The investment came with the right id, but the Gantt have a very strange value on the link, going to a 500 server error. Could you please help me with that?

 

What is the column name that Clarity use create the Gantt link?

 

Here comes a sample about the right and the wrong link, followed by the NSQL query.

 

Many thanks!

 


https://www.ppm.celepar.pr.gov.br/niku/nu#action:pma.investmentProperties&id=5049003&investment_code=PROJECT&return_to=licencas (right link to the investment)

 

https://www.ppm.celepar.pr.gov.br/niku/nu#action:projmgr.wbsTaskList&id=1-5049003999999999999999999999501600850490031&return_to=projmgr.organizerTaskListReturn&drillpos=1-5049003999999999999999999999501600850490031 (Gantt wrong address resulting in the 500 server error)

 


NSQL:

 

SELECT DISTINCT @SELECT:DIM:USER_DEF:IMPLIED:TRX:A.Dim:Dim_Id@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Inv_int_id:Invest_Int_Id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.project_id:project_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Inv_Name:Invest_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.odf_object_code:odf_object_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:@UPPER@(A.odf_object_code):UP_odf_object_Code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Object_Type_Name:Object_Type_Name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.invCode:obj_id_code@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.inv_ManagerId:manager_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.manager_name:manager_name@, @select:dim_prop:user_def:implied:TRX:CASE WHEN A.inv_status = 1 THEN 1 WHEN A.inv_status = 8 THEN 1 ELSE 0 END:investment_status_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.task_int_Id:task_int_Id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.task_int_Id:object_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Task_name:Task_name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Task_seq:wbs_sequence@,  @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) THEN A.inv_Start_Date            WHEN A.QryLevel IN ( 3 ) THEN A.Task_Start_Date           WHEN A.QryLevel IN ( 4 ) THEN A.AssignStart           ELSE NULL           END:Task_Start@, @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) THEN A.inv_Finish_Date            WHEN A.QryLevel IN ( 3 ) THEN A.Task_Finish_Date           WHEN A.QryLevel IN ( 4 ) THEN A.AssignFinish           ELSE NULL           END:Task_Finish@,  @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:A.Baseline_Start:Baseline_Start@, @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:A.Baseline_Finish:Baseline_Finish@, @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:A.AssignStart:Assignment_Start@, @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:A.AssignFinish:Assignment_Finish@,  @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:CASE WHEN A.Task_Start_Date IS NULL AND A.AssignStart IS NOT NULL THEN A.AssignStart ELSE A.Task_Start_Date END:Task_Start_Display@,  @SELECT:DIM_PROP:USER_DEF:datenotime:TRX:CASE WHEN A.Task_Finish_Date IS NULL AND A.AssignFinish IS NOT NULL THEN A.AssignFinish ELSE A.Task_Finish_Date END:Task_Finish_Display@,  @SELECT:METRIC:USER_DEF:IMPLIED:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.HasRights = 1 THEN iDL.Days_late            WHEN A.QryLevel IN ( 3 ) THEN tDL.Days_late           ELSE NULL           END:Days_late@, @SELECT:METRIC:USER_DEF:IMPLIED:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.HasRights = 1 THEN iDL.Days_late            WHEN A.QryLevel IN ( 3 ) THEN tDL.Days_late           ELSE NULL           END:Days_late_sl@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Res_int_id:Res_Int_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Res_Int_id:resource_id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Res_Name:Resource_Name@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Act_Qty:Quantity@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Act_Cost:Cost@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:0:Billing@,   @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:0:Remaining@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.LAYOUT:LAYOUT@,@SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.IMPORTANCIA:IMPORTANCIA@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.EQUACIONAMENTO:EQUACIONAMENTO@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.SITUACAO_PONDERADA:SITUACAO_PONDERADA@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.obj_align_factor1:prioridadeCelepar@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:isnull(A.horaspendentes,0):horaspendentes@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:        CASE WHEN isnull(A.horaspendentes,0)=0 THEN '--'             ELSE str(isnull(A.horaspendentes,0),100,2)         END:horaspendentes2@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.ETC_QTY:etc_qty@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.etc_cost:etc_cost@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Base_Qty:Base_Qty@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Base_Cost:Base_Cost@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.EAC_QTY:Total_Effort@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.EAC_Cost:EAC@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:(A.EAC_Cost - A.Base_Cost):Cost_Var@, /*EAC-BASE*/ @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.Base_Cost = 0 THEN 0      ELSE ( ( A.Act_Cost + A.etc_cost - A.Base_Cost )  / A.Base_Cost )* 100      END:Cost_Var_Perc_sl@, /* ((ACT+ETC - BASE) / BASE) *100 */ @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.ACT_QTY + A.ETC_Qty - A.Base_Qty:Hours_Var@,   /* ACT+ETC-BASE */ @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.Base_Qty = 0 THEN 0      ELSE ( ( A.Act_Qty + A.etc_Qty - A.Base_Qty )  / A.Base_Qty ) * 100      END:Hours_Var_Perc_sl@, /* (ACT+ETC-BASE)/BASE *100  */ @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) THEN A.inv_name            WHEN A.QryLevel IN ( 3 ) THEN A.task_Name           WHEN A.QryLevel IN ( 4 ) THEN A.res_Name           ELSE NULL           END:descr@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.QryLevel:Level_Id@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.qryLevelType != 'SubProject'            THEN '0' @+@ A.inv_name            WHEN A.QryLevel = 1 AND  A.qryLevelType = 'SubProject' THEN right('0000' + cast(A.Task_Seq as varchar(4)), 4)           WHEN A.QryLevel IN ( 3 ) THEN right('0000' + cast(A.Task_Seq as varchar(4)), 4)            WHEN A.QryLevel IN ( 4 ) THEN A.res_Name           ELSE NULL           END:sort1@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.qryLevelType != 'SubProject'            THEN '0' @+@ A.inv_name            WHEN A.QryLevel = 1 AND  A.qryLevelType = 'SubProject' THEN right('0000' + cast(A.Task_Seq as varchar(4)), 4)           WHEN A.QryLevel IN ( 3 ) THEN right('0000' + cast(A.Task_Seq as varchar(4)), 4)           WHEN A.QryLevel IN ( 4 ) THEN A.res_Name           ELSE NULL           END:sort2@,    @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.IsTask:Istask@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.progress:PROGRESS@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Open_For_Time:Open_For_Time@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.IsMilestone:Is_Milestone@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Task_StatusId:Task_Status@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Task_Status_Name:Task_StatusName@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.Charge_Code:Task_Charge_Code@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.HasRights = 1 THEN A.Inv_Pct_Complete*100             WHEN A.QryLevel IN ( 3 ) THEN A.Task_Percent_Complete*100 ELSE NULL  END:Task_Percent_Complete@,   @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel = 1 THEN 1 ELSE 0 END:Investment_Level@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel = 1 AND A.HasRights = 1 THEN 1 ELSE 0 END:Show_Dashboard@,   @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN ( @NVL@(A.child_invs,0) + @NVL@(A.child_Tasks,0) + @NVL@(A.Child_Res,0) ) > 0 THEN Dim ELSE NULL END:hg_has_children@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.linkcode:linkcode@, /*  NEW ATTRIBUTES  */ @SELECT:METRIC:USER_DEF:IMPLIED:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.HasRights = 1 THEN iDL.Days_late_pct           WHEN A.QryLevel IN ( 3 ) THEN tDL.Days_late_pct           ELSE NULL           END:Days_late_pct@, @SELECT:METRIC:USER_DEF:IMPLIED:CASE WHEN A.QryLevel IN ( 1, 2 ) AND A.HasRights = 1 THEN iDL.Days_late_pct           WHEN A.QryLevel IN ( 3 ) THEN tDL.Days_late_pct           ELSE NULL           END:Days_late_pct_sl@,    @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 1, 2 ) THEN 1           ELSE 0           END:Is_PrjPrgLevel@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 3 ) THEN 1           ELSE 0           END:Is_TaskLevel@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:CASE WHEN A.QryLevel IN ( 4 ) THEN 1           ELSE 0           END:Is_ResLevel@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.IsSubProj:IsSubProj@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.HasRights:HasRights@ FROM (/* TOP LEVEL PROJECT */     SELECTI.DIM,I.qryLevel,I.qryLevelType,I.inv_int_id,I.project_id,I.inv_name,I.odf_object_code,I.Is_Program, I.Inv_Start_Date,I.Inv_Finish_Date,I.inv_status,I.inv_managerId,I.Inv_Pct_Complete,I.child_Invs,I.child_Tasks,I.child_res,I.Progress,I.IsSubProj,  I.Task_int_id,I.Task_Name,I.Task_ext_id,I.Task_Level,I.Task_seq,I.Task_Start_Date,I.Task_Finish_Date,I.Task_SubPrj,I.IsTask,I.IsMilestone,I.IsKey,I.Task_Pct_Complete,I.isSelf,I.Res_int_id,I.Res_Name,I.AssignStart,I.AssignFinish,I.LAYOUT,I.importancia,I.EQUACIONAMENTO,I.SITUACAO_PONDERADA,I.obj_align_factor1,sum(ISNULL(CAST(horaspendentes AS decimal(10,2)) ,0)) as horasPendentes,I.ETC_QTY,I.ETC_COst,I.Base_Qty,     I.Base_Cost,I.Act_Qty,I.Act_Cost,I.EAC_QTY,I.EAC_Cost,I.baseline_start,I.baseline_finish,I.baseline_id,I.linkcode,I.Task_Percent_Complete,I.Charge_Code,I.Task_Status_Name,I.Task_StatusId,I.Open_For_Time,I.manager_name,I.invCode,I.Object_Type_Name,I.HasRights

FROM(

SELECT DISTINCT     '1-' @+@ @DBUSER@.CMN_TO_CHAR_FCT(i.id) @+@ '9999999' @+@ '9999999' @+@ '9999999' @+@NVL@(RIGHT(REPLICATE('0',10) + CAST(i.manager_id AS VARCHAR(7)),7), '9999999')  @+@ @DBUSER@.CMN_TO_CHAR_FCT(i.id) @+@ @DBUSER@.CMN_TO_CHAR_FCT(i.status) DIM,     1 qryLevel,     'Project' qryLevelType,    I.id                                          AS inv_int_id,    I.id                                          AS project_id,    I.name                                        AS inv_name,    I.odf_object_code                             AS odf_object_code,    COALESCE(P.Is_Program,0) Is_Program,         I.schedule_Start                              AS Inv_Start_Date,    @DBUSER@.cop_calc_finish_time_fct(i.schedule_finish) AS Inv_Finish_Date,    CASE WHEN I.STATUS IN (1,8) THEN 1 ELSE 0 END AS inv_status,    I.manager_id                                  AS inv_managerId,    p.PERCENT_COMPLETE                            AS Inv_Pct_Complete,    ( SELECT COUNT(*) FROM INV_HIERARCHIES sqi WHERE sqi.parent_id = i.id ) AS child_Invs,    ( SELECT COUNT(*) FROM PrTask sqt WHERE sqt.PrProjectId = i.id ) AS child_Tasks,         0                                            AS child_res,     i.progress                                   AS Progress,         CASE WHEN (SELECT SP.PrRefProjectId AS spInvId                 FROM PrSubProject      AS SP                 JOIN PrTask            AS SPTASKS   ON SPTASKS.PrId = SP.PrTaskId                 JOIN inv_investments   AS ParentPrj ON ParentPrj.id = SPTASKS.PrProjectId                 JOIN INV_PROJECTS      AS Px        ON Px.Prid = ParentPrj.id                WHERE SP.PrRefProjectId = I.id                  AND ParentPrj.is_active = 1                  AND px.is_program = 0                  AND ParentPrj.id NOT IN (SELECT prid                                             FROM inv_projects                                            WHERE is_template = 1)                               ) IS NULL THEN 0           ELSE 1        END                                        AS IsSubProj,

     /* TASK INFO */     NULL                                        AS Task_int_id,     NULL                                        AS Task_Name,     NULL                                        AS Task_ext_id,     NULL                                        AS Task_Level,     NULL                                        AS Task_seq,     NULL                                        AS Task_Start_Date,     NULL                                        AS Task_Finish_Date,     NULL                                        AS Task_SubPrj,     0                                           AS IsTask,     0                                           AS IsMilestone,     0                                           AS IsKey,     0                                           AS Task_Pct_Complete,     0                                           AS isSelf,     /* RESOURCE AND ASSIGNMENT INFO */     NULL                                        AS Res_int_id,     NULL                                        AS Res_Name,     NULL                                        AS AssignStart,     NULL                                        AS AssignFinish,     /* MEASURES */     (SELECT TOP 1 LAY.NAME FROM CMN_LOOKUPS_V AS LAY  WHERE LAY.LOOKUP_CODE = OCP.layout AND LAY.LOOKUP_TYPE LIKE 'LAYOUT') AS LAYOUT,     OCP.importancia                             AS importancia,     OCP.EQUACIONAMENTO                          AS equacionamento,     OCP.SITUACAO_PONDERADA                      AS SITUACAO_PONDERADA,     (SELECT OCI.obj_align_factor1 FROM ODF_CA_INV AS OCI WHERE OCI.ID = P.Prid) AS obj_align_factor1,      (         SELECT sum((ISNULL(CAST(A.prpendactsum AS decimal(10,2)),0) / (CASE WHEN R.RESOURCE_TYPE IN (0,1) THEN 3600 ELSE 1 END)))                 FROM PrTask AS TASKS      LEFT OUTER JOIN PRASSIGNMENT  AS A ON A.PRTASKID = TASKS.PRID     LEFT OUTER JOIN SRM_RESOURCES AS R ON R.id = A.PRRESOURCEID               WHERE TASKS.prprojectid = I.id    )  AS horasPendentes,     pv.etc                                      AS ETC_QTY,     pv.ev_etc                                   AS ETC_COST,     pv.baseline_usage                           AS Base_Qty,          pv.ev_bac                                   AS Base_Cost,     pv.actuals                                  AS Act_Qty,     pv.ev_acwp                                  AS Act_Cost,     pv.labor_effort                             AS EAC_QTY,     pv.ev_acwp + pv.ev_etc                      AS EAC_Cost,     pv.baseline_start                           AS baseline_start,     @DBUSER@.cop_calc_finish_time_fct(pv.baseline_finish) AS baseline_finish,     pv.baseline_id                              AS baseline_id,     'projectProperties'                         AS linkcode,     NULL                                        AS Task_Percent_Complete,     (SELECT CC.PrName FROM PrChargeCode AS CC WHERE CC.PrId = I.chargeCodeId) AS Charge_Code,     (SELECT TS.Name FROM Cmn_Lookups_V AS TS WHERE TS.Lookup_enum = I.progress AND TS.Lookup_Type = 'INVESTMENT_OBJ_PROGRESS' AND TS.Language_code = @WHERE:PARAM:LANGUAGE@) AS Task_Status_Name,          i.progress                                  AS Task_StatusId,     i.is_open_for_te                            AS Open_For_Time,     (SELECT mgr.full_name FROM  srm_resources AS mgr WHERE mgr.user_id = I.manager_id) AS manager_name,          i.code                                      AS invCode,     L.Name                                      AS Object_Type_Name,     1                                           AS HasRights                 FROM INV_INVESTMENTS AS I          INNER JOIN cmn_lookups_v   AS L      ON L.lookup_type = 'INVESTMENT_OBJ_TYPE'                                               AND L.language_code = @WHERE:PARAM:LANGUAGE@                                               AND L.Lookup_Code = @UPPER@(i.odf_object_code)          INNER JOIN cmn_sec_assgnd_obj_perm_v AS seg ON i.id = seg.object_instance_id      LEFT OUTER JOIN INV_PROJECTS    AS P      ON P.Prid = i.id     LEFT OUTER JOIN ODF_CA_PROJECT  AS OCP    ON OCP.ID = P.Prid                                                AND p.is_template <>1     LEFT OUTER JOIN odf_project_v2  AS PV     ON PV.odf_pk = i.id          LEFT OUTER JOIN prj_obs_associations obs ON i.id = obs.record_id        and obs.table_name = 'SRM_PROJECTS'     LEFT OUTER JOIN prj_obs_units_flat flat_inv ON obs.unit_id = flat_inv.unit_id

     WHERE 1=1       AND i.is_active = 1        AND i.odf_object_code <>'idea'       AND seg.permission_code IN ('ProjectEditManagement','InvestmentEditManagement')       AND seg.object_code IN( 'PRJ_PROJECT' , 'INV_OTHER')       AND seg.object_type = 'RECORD'        AND seg.component_code IN ('PRJ', 'INV')       AND seg.USER_ID = @WHERE:PARAM:USER_ID@       AND     @WHERE:SECURITY:PROJECT:i.id@        AND ( @WHERE:PARAM:USER_DEF:STRING:hg_row_id@ IS NULL )       AND    ((@WHERE:PARAM:USER_DEF:INTEGER:INVESTMENT_OBS@ IS NULL) OR        (flat_inv.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:INVESTMENT_OBS@)       )) AS I

GROUP BY DIM, qryLevel, qryLevelType, inv_int_id, project_id, inv_name, odf_object_code, Is_Program, Inv_Start_Date, Inv_Finish_Date, inv_status, inv_managerId, Inv_Pct_Complete, child_Invs,child_Tasks, child_res, Progress, IsSubProj, Task_int_id, Task_Name, Task_ext_id, Task_Level,Task_seq, Task_Start_Date, Task_Finish_Date, Task_SubPrj, IsTask, IsMilestone, IsKey,Task_Pct_Complete, isSelf,  Res_int_id, Res_Name, AssignStart, AssignFinish, LAYOUT,importancia, EQUACIONAMENTO, SITUACAO_PONDERADA, obj_align_factor1,  ETC_QTY, ETC_COst,Base_Qty, Base_Cost, Act_Qty, Act_Cost, EAC_QTY, EAC_Cost, baseline_start, baseline_finish,baseline_id, linkcode, Task_Percent_Complete, Charge_Code, Task_Status_Name, Task_StatusId,Open_For_Time, manager_name, invCode, Object_Type_Name, HasRights

UNION ALL /*  TASKS - WBS LEVEL  */     SELECT       CASE WHEN SP.PrRefProjectId IS NULL THEN        '3-' @+@ @DBUSER@.CMN_TO_CHAR_FCT(TASKS.PrProjectId) @+@ @DBUSER@.CMN_TO_CHAR_FCT(TASKS.PrId) @+@ '9999999' @+@ @NVL@(@DBUSER@.CMN_TO_CHAR_FCT(SP.PrRefProjectId),  '9999999' ) @+@  pass.PassMgrId  @+@  pass.PassInvId  @+@ pass.PassStatus       ELSE       '9-' @+@ @DBUSER@.CMN_TO_CHAR_FCT(SP.PrRefProjectId) @+@ '9999999' @+@ '9999999' @+@ @NVL@(@DBUSER@.CMN_TO_CHAR_FCT(SP.PrRefProjectId),  '9999999' ) @+@  pass.PassMgrId  @+@  pass.PassInvId @+@ pass.PassStatus  END Dim,      3 qryLevel,     'WBS' qryLevelType,     /* INV INFO */ @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassInvId) inv_int_id,     TASKS.PrProjectId project_id,     i.name inv_name,     i.odf_object_code,     0 Is_Program,     i.schedule_Start Inv_Start_Date,     @DBUSER@.cop_calc_finish_time_fct(i.schedule_finish) Inv_Finish_Date,     @DBUSER@.CMN_TO_NUMBER_FCT(pass.passStatus) inv_status,     @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassMgrId) inv_managerId,     P.PrPctComplete Inv_Pct_Complete,     CASE WHEN SP.PrRefProjectId IS NULL THEN 0 ELSE 1 END child_Invs,     ( SELECT @NVL@(COUNT(*),0) FROM PrTask sqT WHERE TASKS.PrProjectId = sqT.PrProjectId            AND sqT.WBS_PARSEQ = TASKS.PRWBSSEQUENCE ) child_Tasks,      ( SELECT @NVL@(COUNT(*),0) FROM PRASSIGNMENT sqA WHERE sqA.PrTaskId = TASKS.PrId ) child_res,     TASKS.PrStatus Progress,     0 IsSubProj,      /* TASK INFO */     TASKS.PrId Task_int_id,      TASKS.PrName Task_Name,      TASKS.PrExternalId Task_Ext_id,       TASKS.PrWbsLevel Task_Level,      TASKS.PrWbsSequence Task_Seq,      TASKS.PrStart Task_Start_Date,     @DBUSER@.cop_calc_finish_time_fct(TASKS.PrFinish) Task_Finish_Date,     SP.PrRefProjectId Task_SubPrj,     TASKS.PrIsTask IsTask,     TASKS.PrIsMilestone IsMilestone,     TASKS.PrIsKey IsKey,     TASKS.PrPctComplete Task_Pct_Complete,     /* Now Find the Parent for the record... */      CASE WHEN Pass.ParentInvID = TASKS.PrId THEN 1 ELSE 0 END IsSelf,     /* RESOURCE AND ASSIGNMENT INFO */     NULL Res_int_id,     NULL Res_Name,     NULL AssignStart,     NULL AssignFinish,     /* MEASURES */     NULL layout,     null importancia,    null EQUACIONAMENTO,    null SITUACAO_PONDERADA,    null obj_align_factor1,    isnull(AA.horasp,0) horasPendentes,

     tv.PrEstSum ETC_QTY,     tv.ev_etc ETC_COst,     tv.baseline_usage Base_Qty,     tv.ev_bac Base_Cost,     tv.PrActSum Act_Qty,     tv.ev_acwp Act_Cost,     tv.ttl_effort EAC_QTY,     tv.ev_acwp + tv.ev_etc EAC_Cost,     /* BASELINE INFO */     BASE.start_date baseline_start,     @DBUSER@.cop_calc_finish_time_fct(BASE.finish_Date) baseline_finish,     BASE.id baseline_id, /* LEGACY INFO */ 'taskProperties' linkcode, TASKS.PrPctComplete  Task_Percent_Complete, CC.PrName Charge_Code, TS.Name Task_Status_Name, TASKS.PrStatus Task_StatusId, TASKS.Is_Open_TE Open_For_Time, NULL manager_name, TASKS.PrExternalId invCode, L.Name Object_Type_Name, 1 HasRights     FROM PrTask TASKS LEFT OUTER join  (select sum(prpendactsum)horasp, prtaskid from niku.ODF_ASSIGNMENT_V2 group by prtaskid) AA on AA.prtaskid = TASKS.PRID     LEFT OUTER JOIN odf_task_v2 TV ON TV.odf_pk = TASKS.PrId LEFT OUTER JOIN PrChargeCode CC ON CC.PrId = TASKS.PrChargeCodeId     LEFT OUTER JOIN Cmn_Lookups_V TS ON TS.Lookup_Type = 'prTaskStatus'    AND TS.Lookup_enum = TASKS.PrStatus    AND TS.Language_code = @WHERE:PARAM:LANGUAGE@      LEFT OUTER JOIN PRJ_BASELINE_DETAILS Base ON Base.Object_Type = 'TASK' AND Base.IS_Current = 1        AND Base.OBJECT_ID = TASKS.PrId     LEFT OUTER JOIN inv_investments i ON i.id = TASKS.PrProjectId     LEFT OUTER JOIN INV_PROJECTS P ON P.Prid = i.id     LEFT OUTER JOIN ( SELECT                        @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 3,7) ParentInvID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 10,7) ParentTaskID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 17,7) ResourceId,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 24,7) SubProj,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 1, 1 ) PassLevel,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 31, 7 ) PassMgrId,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 38, 7 ) PassInvId,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 45, 1 ) PassStatus                  FROM Dual) Pass ON 1=1                       LEFT OUTER JOIN PrSubProject SP ON SP.PrTaskId = TASKS.PrId     INNER JOIN cmn_lookups_v L ON L.lookup_type = 'INVESTMENT_OBJ_TYPE'         AND L.language_code = @WHERE:PARAM:LANGUAGE@ AND L.Lookup_Code = @UPPER@(i.odf_object_code)      WHERE 1=1     AND SP.PRID IS NULL /* ENSURE TASK IS NOT A SUB-PROJECT */     AND TASKS.PrProjectId = Pass.ParentInvID     /*  Only Select WHERE the parent has a task id (PrId)  = hg_row_id ... */     AND (             ( @NVL@((SELECT PrId FROM PrTask PARENT WHERE PARENT.PrProjectId = TASKS.PrProjectId       AND TASKS.WBS_PARSEQ = PARENT.PRWBSSEQUENCE),TASKS.PrId) = Pass.ParentTaskID )     OR       ( TASKS.WBS_ParSeq = -1 AND Pass.ParentTaskID = '9999999' )    OR       ( TASKS.PrId = Pass.ParentTaskID )          )        AND Pass.ParentTaskID != TASKS.PrId /* Don't Include Self */UNION ALL/*  RESOURCE LEVEL  */     SELECT     '4-' @+@ @DBUSER@.CMN_TO_CHAR_FCT(TASKS.PrProjectId) @+@ @DBUSER@.CMN_TO_CHAR_FCT(TASKS.PrId) @+@ @DBUSER@.CMN_TO_CHAR_FCT(r.id) @+@ '9999999' @+@ @DBUSER@.CMN_TO_CHAR_FCT(pass.PassMgrId) @+@ @DBUSER@.CMN_TO_CHAR_FCT(pass.PassInvId) @+@ pass.PassStatus DIM,     4 qryLevel,     'Resources' qryLevelType,     /* INV INFO */ @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassInvId) inv_int_id,     TASKS.PrProjectId project_id,     i.name inv_name,     i.odf_object_code,     0 Is_Program,     i.schedule_Start Inv_Start_Date,     @DBUSER@.cop_calc_finish_time_fct(i.schedule_finish) Inv_Finish_Date,          @DBUSER@.CMN_TO_NUMBER_FCT(pass.passStatus) inv_status,     @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassMgrId) inv_managerId,     P.PrPctComplete Inv_Pct_Complete,     0 child_Invs,        0 child_Tasks,     0 child_res,     TASKS.PrStatus Progress,     0 IsSubProj,       /* TASK INFO */     TASKS.PrId Task_int_id,      TASKS.PrName Task_Name,      TASKS.PrExternalId Task_Ext_id,       TASKS.PrWbsLevel Task_Level,      TASKS.PrWbsSequence Task_Seq,      TASKS.PrStart Task_Start_Date,     @DBUSER@.cop_calc_finish_time_fct(TASKS.PrFinish) Task_Finish_Date,     NULL TaskHas_SubPrj,          TASKS.PrIsTask IsTask,      TASKS.PrIsMilestone IsMilestone,     TASKS.PrIsKey IsKey,     TASKS.PrPctComplete Task_Pct_Complete,     CASE WHEN Pass.ParentInvID = TASKS.PrId THEN 1 ELSE 0 END IsSelf,     /* RESOURCE AND ASSIGNMENT INFO */     R.id Res_int_id,     R.Full_Name Res_Name,     A.PrStart Assignment_Start,     @DBUSER@.cop_calc_finish_time_fct(A.PrFinish) Assignment_Finish,     /* MEASURES */      NULL layout,         null importancia,    null EQUACIONAMENTO,    null SITUACAO_PONDERADA,    null obj_align_factor1,   (ISNULL(A.prpendactsum,0)) horasPendentes ,

     @NVL@(a.prestsum,0) etc_qty,     @NVL@(a.prestsum,0) * @NVL@(Rates.cost_rate,0) etc_cost,     @NVL@(A.baseline_usage,0) base_qty,      @NVL@(A.Baseline_Cost,0) base_cost,     @NVL@(a.prActSum,0) Act_Qty,                   @NVL@(A.ActCost_Sum,0) Act_Cost,    (@NVL@(a.prActSum,0) + @NVL@(a.prestsum,0) ) EAC_QTY,     @NVL@(A.ActCost_Sum,0) + (  @NVL@(a.prestsum,0) * @NVL@(Rates.cost_rate,0)) EAC_Cost,     /* BASELINE INFO */     A.Baseline_Start baseline_start,     @DBUSER@.cop_calc_finish_time_fct(A.Baseline_Finish) baseline_finish,     BASE.id baseline_id, /* LEGACY INFO */ 'resourceProperties' linkcode, NULL Task_Percent_Complete, NULL Charge_Code, NULL Task_Status_Name, NULL Task_StatusId, TASKS.Is_Open_TE Open_For_Time, NULL manager_name, NULL invCode, L.Name Object_Type_Name, 1 HasRights     FROM PrTask TASKS     LEFT OUTER JOIN ODF_CA_TASK CUS_TASK ON CUS_TASK.id = TASKS.PrId      LEFT OUTER JOIN ODF_ASSIGNMENT_V2 A ON TASKS.PrId = A.PrTaskId     LEFT OUTER JOIN SRM_RESOURCES R ON r.id = A.PrResourceId      LEFT OUTER JOIN PRJ_BASELINE_DETAILS Base ON Base.Object_Type = 'ASSIGNMENT' AND Base.IS_Current = 1        AND Base.OBJECT_ID = A.odf_pk     LEFT OUTER JOIN INV_INVESTMENTS I ON I.id = TASKS.PrProjectId     LEFT OUTER JOIN INV_PROJECTS P ON P.Prid = i.id     LEFT OUTER JOIN NBI_PROJ_RES_RATES_AND_COSTS Rates ON Rates.Project_Id = I.id AND Rates.Task_Id = TASKS.PrId       AND Resource_Id = A.PrResourceId      AND a.prFinish  BETWEEN Rates.from_date AND Rates.to_date                   LEFT OUTER JOIN ( SELECT                        @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 3,7) ParentInvID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 10,7) ParentTaskID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 17,7) ResourceId,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 24,7) SubProj,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 1, 1 ) PassLevel,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 31, 7 ) PassMgrId,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 38, 7 ) PassInvId,   @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 45, 1 ) PassStatus                  FROM Dual) Pass ON 1=1     INNER JOIN cmn_lookups_v L ON L.lookup_type = 'INVESTMENT_OBJ_TYPE'         AND L.language_code = @WHERE:PARAM:LANGUAGE@ AND L.Lookup_Code = @UPPER@(i.odf_object_code)       WHERE 1=1     AND TASKS.PrId = Pass.ParentTaskID     AND @WHERE:PARAM:USER_DEF:STRING:hg_row_id@ IS NOT NULL     AND Pass.ResourceId = '9999999'     AND r.id IS NOT NULLUNION ALL/* SUB PROJECT LEVEL - Only When Shown BELOW a parent */     SELECT DISTINCT     '1-' @+@ @DBUSER@.CMN_TO_CHAR_FCT(i.id) @+@ '9999999' @+@ '9999999' @+@ '9999999' @+@ @DBUSER@.CMN_TO_CHAR_FCT(pass.PassMgrId) @+@ @DBUSER@.CMN_TO_CHAR_FCT(pass.PassInvId) @+@ pass.PassStatus DIM,     1 qryLevel,     'SubProject' qryLevelType,     /* INV INFO */ @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassInvId) inv_int_id,     i.id project_id_id,     i.name inv_name,     i.odf_object_code,     @NVL@(P.Is_Program,0) Is_Program,      i.schedule_Start Inv_Start_Date,     @DBUSER@.cop_calc_finish_time_fct(i.schedule_finish) Inv_Finish_Date,     CASE SecCheck.HasRights WHEN 1 THEN @DBUSER@.CMN_TO_NUMBER_FCT(pass.passStatus) ELSE NULL END inv_status,     CASE SecCheck.HasRights WHEN 1 THEN @DBUSER@.CMN_TO_NUMBER_FCT(pass.PassMgrId) ELSE NULL END inv_managerId,     CASE SecCheck.HasRights WHEN 1 THEN P.PrPctComplete ELSE NULL END Inv_Pct_Complete,     CASE SecCheck.HasRights WHEN 1 THEN     ( SELECT COUNT(*) FROM INV_HIERARCHIES sqi WHERE sqi.parent_id = i.id )    ELSE 0  END child_Invs, CASE SecCheck.HasRights WHEN 1 THEN         ( SELECT COUNT(*) FROM PrTask sqt WHERE sqt.PrProjectId = i.id )   ELSE 0 END child_Tasks,     0 child_res,     CASE SecCheck.HasRights WHEN 1 THEN i.progress ELSE NULL END Progress,     CASE WHEN  SP2.spInvId  IS NULL THEN 0 ELSE 1 END IsSubProj,       /* TASK INFO */     NULL Task_int_id,     NULL Task_Name,     NULL Task_ext_id,     NULL Task_Level,     TASKS.PrWbsSequence Task_seq,     NULL Task_Start_Date,     NULL Task_Finish_Date,     NULL Task_SubPrj,     0 IsTask,     0 IsMilestone,     0 IsKey,     0 Task_Pct_Complete,     0 isSelf,     /* RESOURCE AND ASSIGNMENT INFO */     NULL Res_int_id,     NULL Res_Name,     NULL AssignStart,     NULL AssignFinish,     /* MEASURES */     NULL layout,    null importancia,    null EQUACIONAMENTO,    null SITUACAO_PONDERADA,    null obj_align_factor1,sum(ISNULL(A.prpendactsum,0))  horasPendentes,     CASE SecCheck.HasRights WHEN 1 THEN pv.etc ELSE NULL END ETC_QTY,     CASE SecCheck.HasRights WHEN 1 THEN pv.ev_etc ELSE NULL END  ETC_COst,     CASE SecCheck.HasRights WHEN 1 THEN pv.baseline_usage ELSE NULL END  Base_Qty,     CASE SecCheck.HasRights WHEN 1 THEN pv.ev_bac ELSE NULL END  Base_Cost,     CASE SecCheck.HasRights WHEN 1 THEN pv.actuals ELSE NULL END  Act_Qty,     CASE SecCheck.HasRights WHEN 1 THEN pv.ev_acwp ELSE NULL END  Act_Cost,     CASE SecCheck.HasRights WHEN 1 THEN pv.labor_effort ELSE NULL END  EAC_QTY,     CASE SecCheck.HasRights WHEN 1 THEN pv.ev_acwp + pv.ev_etc ELSE NULL END EAC_Cost,     /* BASELINE INFO */     CASE SecCheck.HasRights WHEN 1 THEN pv.baseline_start ELSE NULL END Baseline_Start,     CASE SecCheck.HasRights WHEN 1 THEN @DBUSER@.cop_calc_finish_time_fct(pv.baseline_finish) ELSE NULL END  baseline_finish,     CASE SecCheck.HasRights WHEN 1 THEN pv.baseline_id ELSE NULL END Baseline_id, /* LEGACY INFO */ 'projectProperties' linkcode, NULL Task_Percent_Complete, CC.PrName Charge_Code, TS.Name Task_Status_Name, i.progress Task_StatusId, i.is_open_for_te Open_For_Time, CASE SecCheck.HasRights WHEN 1 THEN mgr.full_name ELSE NULL END manager_name, i.code invCode, L.Name Object_Type_Name, @NVL@(SecCheck.HasRights, 0) HasRights     FROM PrTask TASKS      LEFT OUTER JOIN PRASSIGNMENT A ON TASKS.PrId = A.PrTaskId     LEFT OUTER JOIN ( SELECT                        @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,  3,7) ParentInvID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 10,7) ParentTaskID,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 17,7) ResourceId,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 24,7) SubProj,                       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,  1,1) PassLevel,       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 31,7) PassMgrId,       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 38,7) PassInvId,       @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 45,1) PassStatus                  FROM Dual) Pass ON 1=1                       INNER JOIN PrSubProject SP ON SP.PrTaskId = TASKS.PrId     INNER JOIN inv_investments i ON i.id = SP.PRREFPROJECTID     LEFT OUTER JOIN INV_PROJECTS P ON P.Prid = i.id     LEFT OUTER JOIN odf_project_v2 PV ON PV.odf_pk = i.id

     LEFT OUTER JOIN ( SELECT                        SP.PrRefProjectId spInvId                       FROM PrSubProject SP                       LEFT OUTER JOIN PrTask SPTASKS ON SPTASKS.PrId = SP.PrTaskId                       LEFT OUTER JOIN inv_investments ParentPrj ON ParentPrj.id = SPTASKS.PrProjectId                       LEFT OUTER JOIN INV_PROJECTS Px ON Px.Prid = ParentPrj.id                          WHERE 1=1                       AND ParentPrj.odf_object_code <> 'idea'                            AND ParentPrj.is_active = 1                       AND ParentPrj.id NOT IN(SELECT prid from inv_projects WHERE is_template = 1)    AND px.is_program = 0        ) SP2 ON SP2.spInvId = i.id        LEFT OUTER JOIN srm_resources mgr on i.manager_id = mgr.user_id     INNER JOIN cmn_lookups_v L ON L.lookup_type = 'INVESTMENT_OBJ_TYPE'         AND L.language_code = @WHERE:PARAM:LANGUAGE@ AND L.Lookup_Code = @UPPER@(i.odf_object_code)     LEFT OUTER JOIN PrChargeCode CC ON CC.PrId = i.chargeCodeId     LEFT OUTER JOIN Cmn_Lookups_V TS ON TS.Lookup_Type = 'INVESTMENT_OBJ_PROGRESS'    AND TS.Lookup_enum = i.progress   AND TS.Language_code = @WHERE:PARAM:LANGUAGE@     LEFT OUTER JOIN ( SELECT                    secP.id,   CASE WHEN @NVL@(COUNT(secT.PrId),0) >=1 THEN 1 ELSE 0 END HasRights                   FROM PrTask secT   INNER JOIN PrSubProject secSP ON secSP.PrTaskId = secT.PrId   INNER JOIN inv_investments secP ON secP.id = secSP.PRREFPROJECTID   WHERE secT.PrProjectId =  @DBUSER@.CMN_TO_NUMBER_FCT( @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@, 3, 7 ) )   AND @WHERE:SECURITY:PROJECT:secP.id@   /* Below is a check to ensure the user has Proj Mgt RIGHTS to the Sub Project */                       AND  ((EXISTS (SELECT user_id                                      FROM   cmn_sec_assgnd_obj_perm_r_v                                      WHERE  object_type = 'RECORD'                                      AND    object_code = 'PRJ_PROJECT'                                      AND    permission_code = 'ProjectViewManagement'  AND    component_code = 'PRJ'                                       AND    user_id = @WHERE:PARAM:USER_ID@ ))                              OR                             (EXISTS (SELECT object_instance_id                                      FROM   cmn_sec_assgnd_obj_perm_v                                      WHERE  object_instance_id = secP.id                                       AND    object_type = 'RECORD'                                      AND    object_code = 'PRJ_PROJECT'                                      AND    permission_code = 'ProjectViewManagement'                                      AND    component_code = 'PRJ'                                      AND    user_id = @WHERE:PARAM:USER_ID@ )))   GROUP BY secP.id                      ) SecCheck ON SecCheck.id = i.id     WHERE 1=1     AND SP.PRID IS NOT NULL /* ONLY WHEN TASK IS A SUB-PROJECT */     AND @WHERE:PARAM:USER_DEF:STRING:hg_row_id@ IS NOT NULL     AND TASKS.PrProjectId = Pass.ParentInvID     /*  Only Select WHERE the parent has a task id (PrId)  = hg_row_id ... */     AND (             ( @NVL@((SELECT PrId FROM PrTask PARENT WHERE PARENT.PrProjectId = TASKS.PrProjectId                  AND TASKS.WBS_PARSEQ = PARENT.PRWBSSEQUENCE),TASKS.PrId) = Pass.ParentTaskID )     OR       ( TASKS.WBS_ParSeq = -1 AND Pass.ParentTaskID = '9999999' )    OR       ( TASKS.PrId = Pass.ParentTaskID )          OR      ( Pass.ParentTaskID = '9999999' AND ( SELECT COUNT(*) FROM PrTask XT WHERE XT.PrProjectId = i.id) = 0 )      ) GROUP BY      i.id, Pass.PassInvId, i.name, i.odf_object_code, P.Is_Program, i.schedule_Start, i.schedule_finish,SecCheck.HasRights , pass.passStatus, pass.PassMgrId, P.PrPctComplete, i.progress,SP2.spInvId, TASKS.PrWbsSequence, pv.etc, pv.ev_etc, pv.baseline_usage, pv.ev_bac ,pv.actuals, pv.ev_acwp, pv.labor_effort, pv.ev_acwp, pv.ev_etc, pv.baseline_start, pv.baseline_finish, pv.baseline_id, CC.PrName,  TS.Name, i.progress, i.is_open_for_te,mgr.full_name, i.code, L.Name

) A

LEFT OUTER JOIN cop_inv_days_late_v iDL ON iDL.investment_id = A.project_idLEFT OUTER JOIN cop_tsk_days_late_v tDL ON tDL.task_id = A.Task_int_id

WHERE 1=1AND ( @WHERE:PARAM:USER_DEF:DATE:pStartDate@ IS NULL OR @WHERE:PARAM:USER_DEF:DATE:pStartDate@ IS NOT NULL )AND ( @WHERE:PARAM:USER_DEF:DATE:pEndDate@ IS NULL OR @WHERE:PARAM:USER_DEF:DATE:pEndDate@ IS NOT NULL )



AND  ((EXISTS ( SELECT AP.USER_ID USER_ID,          U.USER_NAME,          C.COMPONENT_CODE,          O.ID OBJECT_ID,          O.OBJECT_CODE,          L.LOOKUP_CODE OBJECT_TYPE,          AP.OBJECT_INSTANCE_ID,          PERMISSION_CODE,          PERMISSION_VALUE     FROM CMN_SEC_ASSGND_OBJ_PERM_V0 AP with (nolock),          CMN_SEC_USERS U with (nolock),          CMN_SEC_OBJECTS O,          CMN_COMPONENTS C,          CMN_LOOKUPS L with (nolock)              WHERE AP.USER_ID = U.ID      AND AP.OBJECT_ID = O.ID      AND C.ID = O.COMPONENT_ID      AND L.ID = O.OBJECT_TYPE_ID      AND permission_code  IN ('ProjectEditManagement','InvestmentEditManagement')      AND O.OBJECT_CODE IN( 'PRJ_PROJECT' , 'INV_OTHER')      AND L.LOOKUP_CODE = 'RECORD'       AND C.COMPONENT_CODE IN ('PRJ', 'INV')      AND AP.USER_ID = @WHERE:PARAM:USER_ID@ ))



)

AND @FILTER@

Outcomes