Clarity

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@

"https://www.ppm.celepar.pr.gov.br/niku/nu#action:projmgr.wbsTaskList&id=1-5049003999999999999999999999501600850490031&return_to=projmgr.organizerTaskListReturn&drillpos=1-5049003999999999999999999999501600850490031"

It's pointing to a task in the task list

NJ

Hi, tks for reply. But the link is wrong, the value 1-5049003999999999999999999999501600850490031 isn't a valid task. The task should be the same value in the first link I sent before, like 5049003. Please repare that the id of the task is inside this huge number.
Any thoughts?

Tks!

You appear to be building that odd link in your NSQL yourself though;

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

So you have just picked the wrong link attribute to pass to the Gantt?

yeah, thats true, but how do I define the value to be passed to Gantt?

isn't it just the task id that you already have in your NSQL;

Task_int_id

Can be, but how do I set the column on Gantt?
I didn't find it.

In the NSQL query which drives the portlet that links to the Gantt, in its "Linking" Link Settings.

(if that portlet is not NSQL based, you'll have to work out what its doing - its clearly just passing your DIM attribute at the moment, perhaps you need to pass something different into that portlet from your NSQL query)

--

Looks like to link to the Gantt from NSQL you provide two Object IDs , not sure if they both are the task id or one is the project id and the other the task id ; you'll have to experiment.

I dont know if you can help me, but I'm doing an performance update at this query, but it still doesn't work with gantt.
Here is the original query, notice that there is no differences between the fields requested by select. I'm going mad about this.
The optimized query is the one in the first question.

Really tks for trying to help me!

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 '--'         WHEN odf_object_code = 'OTHER' THEN                   cast(--cast1                    LEFT(                    cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar), len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar))-2) +'.'                    + SUBSTRING ( cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar) ,len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar)) -1 ,                     len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar))                   )  --left    

                   as varchar)--cast1 WHEN odf_object_code = 'PROJECT' AND istask <>1 THEN                   cast(--cast1                    LEFT(                    cast(replace(cast(convert(numeric(10,2),horaspendentes/100) as varchar),'.','') as varchar), len(cast(replace(cast(convert(numeric(10,2),horaspendentes/100) as varchar),'.','') as varchar))-2) +'.'                    + SUBSTRING ( cast(replace(cast(convert(numeric(10,2),horaspendentes/100) as varchar),'.','') as varchar) ,len(cast(replace(cast(convert(numeric(10,2),horaspendentes/100) as varchar),'.','') as varchar)) -1 ,                     len(cast(replace(cast(convert(numeric(10,2),horaspendentes/100) as varchar),'.','') as varchar))                   )  --left    

                   as varchar)--cast1ELSE(

cast(--cast1   LEFT(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar), len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar))-2) +'.'+ SUBSTRING ( cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar) ,len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar)) -1 , len(cast(replace(cast(convert(numeric(10,2),horaspendentes) as varchar),'.','') as varchar)) )  --left    

 as varchar)--cast1  

     )--elseEND: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,     /* INV INFO */     i.id inv_int_id, i.id project_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 WHEN i.STATUS = 1 THEN 1 WHEN i.STATUS = 8 THEN 1 ELSE 0 END inv_status,     i.manager_id inv_managerId,     p.PERCENT_COMPLETE Inv_Pct_Complete,     ( SELECT COUNT(*) FROM INV_HIERARCHIES sqi WHERE sqi.parent_id = i.id ) child_Invs,     ( SELECT COUNT(*) FROM PrTask sqt WHERE sqt.PrProjectId = i.id ) child_Tasks,     0 child_res,     i.progress 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,     NULL 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 */     LAY.NAME LAYOUT,     OCP.importancia,     OCP.EQUACIONAMENTO,     OCP.SITUACAO_PONDERADA,     OCI.obj_align_factor1,    /* HORAS PENDENTES */sum((ISNULL(CAST(A.prpendactsum AS decimal(10,2)),0) / (CASE WHEN R.RESOURCE_TYPE  in (0,1) THEN 3600 ELSE 1 END))) horasPendentes,

     pv.etc ETC_QTY,     pv.ev_etc ETC_COst,     pv.baseline_usage Base_Qty,     pv.ev_bac Base_Cost,     pv.actuals Act_Qty,     /*i.labor_actsum/3600 Act_Qty,*/     pv.ev_acwp Act_Cost,     pv.labor_effort EAC_QTY,     pv.ev_acwp + pv.ev_etc EAC_Cost,     /* BASELINE INFO */     pv.baseline_start,     @DBUSER@.cop_calc_finish_time_fct(pv.baseline_finish) baseline_finish,     pv.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, mgr.full_name manager_name, i.code invCode, L.Name Object_Type_Name, 1 HasRights      FROM INV_INVESTMENTS i

     left outer join PrTask TASKS on prprojectid = i.id     LEFT OUTER JOIN PRASSIGNMENT A ON TASKS.PRID = A.PRTASKID     LEFT OUTER JOIN SRM_RESOURCES R ON r.id = A.PRRESOURCEID

     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 ODF_CA_PROJECT OCP ON OCP.ID = P.Prid  and p.is_template <>1     LEFT OUTER JOIN CMN_LOOKUPS_V LAY ON LAY.LOOKUP_CODE = OCP.layout  AND LAY.LOOKUP_TYPE LIKE 'LAYOUT'      LEFT OUTER JOIN ODF_CA_INV OCI ON OCI.ID = P.Prid     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.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 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_idINNER JOIN cmn_sec_assgnd_obj_perm_v seg ON i.id = seg.object_instance_id 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@

     WHERE 1=1



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@)       )     AND i.is_active = 1      and i.odf_object_code <>'idea'

 





      GROUP BY      i.id,i.name, i.odf_object_code,P.Is_Program,  i.schedule_Start,i.schedule_finish,     i.STATUS, i.manager_id,p.PERCENT_COMPLETE, i.progress, SP2.spInvId,        LAY.NAME, OCP.importancia, OCP.EQUACIONAMENTO, OCP.SITUACAO_PONDERADA,     OCI.obj_align_factor1, A.prpendactsum,R.RESOURCE_TYPE, pv.etc,     pv.ev_etc, pv.baseline_usage, pv.ev_bac, pv.actuals, i.labor_actsum,     pv.ev_acwp, pv.labor_effort, 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

) 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@