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 IN (1,5,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.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 IS NULL THEN NULL WHEN A.Base_Cost = 0 THEN NULL 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 IS NULL THEN NULL WHEN A.Base_Qty = 0 THEN NULL 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.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.IsKey:Is_Key@, @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 WHEN A.QryLevel IN ( 3 ) THEN A.Task_Percent_Complete 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 */ 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 IN (1,5,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, 1 IsMilestone, 1 IsKey, 0 Task_Pct_Complete, 0 isSelf, /* RESOURCE AND ASSIGNMENT INFO */ NULL Res_int_id, NULL Res_Name, NULL AssignStart, NULL AssignFinish, /* MEASURES */ pv.etc ETC_QTY, pv.ev_etc ETC_COst, pv.baseline_usage Base_Qty, pv.ev_bac Base_Cost, pv.actuals 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 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 = 'project' 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_id WHERE 1=1 AND ( @WHERE:PARAM:USER_DEF:STRING:hg_row_id@ IS NULL ) AND i.is_active = 1 AND ( ( i.odf_object_code = 'project' AND P.Is_Template = 0 AND P.is_Program = 1 ) ) AND @WHERE:SECURITY:PROJECT:i.id@ 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.percent_complete 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 */ 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 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.percent_complete 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 */ @NVL@(a.prestsum,0) etc_qty, @NVL@(a.prestsum,0) * @NVL@(Rates.cost_rate,0) etc_cost, A.baseline_usage base_qty, A.Baseline_Cost 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, prjr.prisopen 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_RESOURCES prjr ON prjr.prid = r.id 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 NULL UNION 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.percent_complete 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, 1 IsMilestone, 1 IsKey, 0 Task_Pct_Complete, 0 isSelf, /* RESOURCE AND ASSIGNMENT INFO */ NULL Res_int_id, NULL Res_Name, NULL AssignStart, NULL AssignFinish, /* MEASURES */ 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 ( 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 = 'project' 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 ) ) ) A LEFT OUTER JOIN cop_inv_days_late_v iDL ON iDL.investment_id = A.project_id LEFT OUTER JOIN cop_tsk_days_late_v tDL ON tDL.task_id = A.Task_int_id WHERE 1=1 AND @FILTER@