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 '
as varchar)
as varchar)
cast(
as varchar)
)
@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@, @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@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TRX:A.ACT_QTY + A.ETC_Qty - A.Base_Qty:Hours_Var@, @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@, @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@, @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 ( 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 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, 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, NULL Res_int_id, NULL Res_Name, NULL AssignStart, NULL AssignFinish, LAY.NAME LAYOUT, OCP.importancia, OCP.EQUACIONAMENTO, OCP.SITUACAO_PONDERADA, OCI.obj_align_factor1, 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, pv.ev_acwp Act_Cost, pv.labor_effort EAC_QTY, pv.ev_acwp + pv.ev_etc EAC_Cost, pv.baseline_start, @DBUSER@.cop_calc_finish_time_fct(pv.baseline_finish) baseline_finish, pv.baseline_id, '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 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, @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, 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, CASE WHEN Pass.ParentInvID = TASKS.PrId THEN 1 ELSE 0 END IsSelf, NULL Res_int_id, NULL Res_Name, NULL AssignStart, NULL AssignFinish, 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, BASE.start_date baseline_start, @DBUSER@.cop_calc_finish_time_fct(BASE.finish_Date) baseline_finish, BASE.id baseline_id, '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 AND TASKS.PrProjectId = Pass.ParentInvID 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 UNION ALL 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, @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, 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, R.id Res_int_id, R.Full_Name Res_Name, A.PrStart Assignment_Start, @DBUSER@.cop_calc_finish_time_fct(A.PrFinish) Assignment_Finish, 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, A.Baseline_Start baseline_start, @DBUSER@.cop_calc_finish_time_fct(A.Baseline_Finish) baseline_finish, BASE.id baseline_id, '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 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, @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, 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, NULL Res_int_id, NULL Res_Name, NULL AssignStart, NULL AssignFinish, 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, 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, '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@ 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 AND @WHERE:PARAM:USER_DEF:STRING:hg_row_id@ IS NOT NULL AND TASKS.PrProjectId = Pass.ParentInvID 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@