|
SELECT W1.BUSINESS_UNIT , W1.PROJECT_ID , W1.ACTIVITY_ID , W1.ANALYSIS_TYPE , CASE WHEN W1.RESOURCE_TYPE <> ' ' THEN W1.RESOURCE_TYPE ELSE 'N/A' END , CASE WHEN W1.RESOURCE_CATEGORY <> ' ' THEN W1.RESOURCE_CATEGORY ELSE 'N/A' END , CASE WHEN W1.RESOURCE_SUB_CAT <> ' ' THEN W1.RESOURCE_SUB_CAT ELSE 'N/A' END , CASE WHEN W1.RESOURCE_TYPE <> ' ' THEN G.DESCR ELSE 'N/A' END , W1.TRANS_DATE , W1.ACCOUNTING_DT , W1.BI_DISTRIB_STATUS , W1.GL_DISTRIB_STATUS , CASE WHEN W1.UNIT_OF_MEASURE <> ' ' THEN W1.UNIT_OF_MEASURE ELSE 'N/A' END , W1.CURRENCY_CD , CASE WHEN W1.EMPLID <> ' ' THEN W1.EMPLID ELSE 'N/A' END , D.BUDGET_PERIOD , W1.PROJ_DESCR , W1.ACTIVITY_DESCR , CASE WHEN W1.PROJECT_TYPE <> ' ' THEN W1.PROJECT_TYPE ELSE 'N/A' END , W1.EFF_STATUS , CASE WHEN W1.PROJECT_USER1 <> ' ' THEN W1.PROJECT_USER1 ELSE 'N/A' END , CASE WHEN W1.PROJECT_USER2 <> ' ' THEN W1.PROJECT_USER2 ELSE 'N/A' END , CASE WHEN W1.PROJECT_USER3 <> ' ' THEN W1.PROJECT_USER3 ELSE 'N/A' END , CASE WHEN W1.PROJECT_USER4 <> ' ' THEN W1.PROJECT_USER4 ELSE 'N/A' END , CASE WHEN W1.PROJECT_USER5 <> ' ' THEN W1.PROJECT_USER5 ELSE 'N/A' END , W1.EFF_STATUS , W1.PERCENT_COMPLETE , CASE WHEN W1.PC_MILESTONE_FLG <> ' ' THEN W1.PC_MILESTONE_FLG ELSE 'N' END , CASE WHEN W1.ACTIVITY_USER1 <> ' ' THEN W1.ACTIVITY_USER1 ELSE 'N/A' END , CASE WHEN W1.ACTIVITY_USER2 <> ' ' THEN W1.ACTIVITY_USER2 ELSE 'N/A' END , CASE WHEN W1.ACTIVITY_USER3 <> ' ' THEN W1.ACTIVITY_USER3 ELSE 'N/A' END , CASE WHEN W1.ACTIVITY_USER4 <> ' ' THEN W1.ACTIVITY_USER4 ELSE 'N/A' END , CASE WHEN W1.ACTIVITY_USER5 <> ' ' THEN W1.ACTIVITY_USER5 ELSE 'N/A' END , W1.SET_OVERRIDE , W1.AN_GRP_ACTV_BUD , W1.AN_GRP_TOT_COSTS , W1.PC_REV_BUD_AN_GRP , W1.AN_GRP_TOT_REV , W1.RESOURCE_QUANTITY , W1.RESOURCE_AMOUNT , W1.DTTM_STAMP , (CASE WHEN W1.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE THEN W1.RESOURCE_AMOUNT ELSE 0 END ) , (CASE WHEN W1.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE THEN W1.RESOURCE_AMOUNT ELSE 0 END ) , (CASE WHEN W1.PC_REV_BUD_AN_GRP = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE THEN W1.RESOURCE_AMOUNT ELSE 0 END ) , (CASE WHEN W1.AN_GRP_TOT_REV = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.GL_DISTRIB_STATUS = 'D' THEN W1.RESOURCE_AMOUNT ELSE 0 END ) , (CASE WHEN W1.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.UNIT_OF_MEASURE = 'MHR' THEN W1.RESOURCE_QUANTITY WHEN W1.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.UNIT_OF_MEASURE = 'MDY' THEN W1.RESOURCE_QUANTITY * W1.PC_HOURS_PER_DAY ELSE 0 END ) , (CASE WHEN W1.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.UNIT_OF_MEASURE = 'MHR' THEN W1.RESOURCE_QUANTITY WHEN W1.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.UNIT_OF_MEASURE = 'MDY' THEN W1.RESOURCE_QUANTITY * W1.PC_HOURS_PER_DAY ELSE 0 END ) , (CASE WHEN L.ANALYSIS_GROUP = 'PSBLD' AND ((W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.BI_DISTRIB_STATUS = 'W') OR (W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE_MAP AND W1.BI_DISTRIB_STATUS = 'D')) THEN W1.RESOURCE_AMOUNT ELSE 0 END ) FROM ( SELECT B.BUSINESS_UNIT , B.PROJECT_ID , B.SET_OVERRIDE , B.AN_GRP_ACTV_BUD , B.AN_GRP_TOT_COSTS , B.PC_REV_BUD_AN_GRP , B.AN_GRP_TOT_REV , A.EMPLID , A.ANALYSIS_TYPE , A.RESOURCE_TYPE , A.GL_DISTRIB_STATUS , A.BI_DISTRIB_STATUS , A.ACCOUNTING_DT , A.DTTM_STAMP , A.RESOURCE_AMOUNT , A.RESOURCE_QUANTITY , A.UNIT_OF_MEASURE , A.TRANS_DATE , A.RESOURCE_SUB_CAT , A.RESOURCE_CATEGORY , A.ACTIVITY_ID , B.PROJECT_USER5 , B.PROJECT_USER4 , B.PROJECT_USER3 , B.PROJECT_USER2 , B.PROJECT_USER1 , B.PROJECT_TYPE , B.DESCR AS PROJ_DESCR , B.PC_HOURS_PER_DAY , C.ACTIVITY_USER5 , C.ACTIVITY_USER4 , C.ACTIVITY_USER3 , C.ACTIVITY_USER2 , C.ACTIVITY_USER1 , C.PC_MILESTONE_FLG , C.PERCENT_COMPLETE , C.EFF_STATUS , C.DESCR AS ACTIVITY_DESCR , P.CURRENCY_CD FROM PS_BUS_UNIT_TBL_PC P , PS_PROJECT B , PS_PROJ_ACTIVITY C , PS_PC_SUMMARY_TBL A , PS_EID_ETL_LASTRUN X WHERE B.BUSINESS_UNIT = P.BUSINESS_UNIT AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.PROJECT_ID = B.PROJECT_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PROJECT_ID = C.PROJECT_ID AND A.ACTIVITY_ID = C.ACTIVITY_ID AND X.EID_DS_ID = 'Projects' AND (A.DTTM_STAMP >= X.LAST_RUN_DTM OR B.FMS_LASTUPDDTTM >= X.LAST_RUN_DTM OR C.FMS_LASTUPDDTTM >= X.LAST_RUN_DTM OR ( SELECT MAX(I.FMS_LASTUPDDTTM) FROM PS_PGM_PROJ_RISK I WHERE I.BUSINESS_UNIT = A.BUSINESS_UNIT AND I.PROJECT_ID = A.PROJECT_ID) >= X.LAST_RUN_DTM)) W1 LEFT OUTER JOIN PS_PROJ_AN_GRP_MAP L ON W1.SET_OVERRIDE = L.SETID AND (((W1.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP OR W1.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP OR W1.PC_REV_BUD_AN_GRP = L.ANALYSIS_GROUP) AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE) OR (W1.AN_GRP_TOT_REV = L.ANALYSIS_GROUP AND W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.GL_DISTRIB_STATUS = 'D') OR (L.ANALYSIS_GROUP = 'PSBLD' AND ((W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND W1.BI_DISTRIB_STATUS = 'W') OR (W1.ANALYSIS_TYPE = L.ANALYSIS_TYPE_MAP AND W1.BI_DISTRIB_STATUS = 'D')))) LEFT OUTER JOIN PS_PROJ_RES_TYPE G ON W1.SET_OVERRIDE = G.SETID AND W1.RESOURCE_TYPE = G.RESOURCE_TYPE AND G.EFF_STATUS = 'A' , PS_INSTALLATION_PC E , PS_CAL_BP_TBL D WHERE W1.SET_OVERRIDE = D.SETID AND D.CALENDAR_ID = E.PC_SUM_CAL_ID AND W1.ACCOUNTING_DT BETWEEN D.BEGIN_DT AND D.END_DT AND (G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_PROJ_RES_TYPE G1 WHERE G.SETID = G1.SETID AND G.RESOURCE_TYPE = G1.RESOURCE_TYPE AND G1.EFF_STATUS = 'A' AND G1.EFFDT <= %CurrentDateIn) OR G.EFFDT IS NULL)
|
BUSINESS_UNIT