PJO_PROJ_PLAN_XBS_ACCUM_V

Details

• Schema: FUSION

• Object owner: PJO

• Object type: VIEW

Columns

Name

PROJECT_ID STRUCTURE_VERSION_ID PROJECT_ORG_ID PROJECT_ELEMENT_ID RBS_AGGR_LEVEL WBS_ROLLUP_FLAG RBS_VERSION_ID RBS_ELEMENT_ID PLAN_VERSION_ID CURRENCY_CODE START_DATE END_DATE ACT_START_DATE ACT_END_DATE PFC_RAW_COST PC_RAW_COST TC_RAW_COST PFC_BRDN_COST PC_BRDN_COST TC_BRDN_COST QUANTITY LABOR_HRS EQUIP_HRS PFC_ACT_RAW_COST PC_ACT_RAW_COST TC_ACT_RAW_COST PFC_ACT_BRDN_COST PC_ACT_BRDN_COST TC_ACT_BRDN_COST ACT_QUANTITY ACT_LABOR_HRS ACT_EQUIP_HRS PFC_ETC_RAW_COST PC_ETC_RAW_COST TC_ETC_RAW_COST PFC_ETC_BRDN_COST PC_ETC_BRDN_COST TC_ETC_BRDN_COST ETC_QUANTITY ETC_LABOR_HRS ETC_EQUIP_HRS RR_PFC_RAW_COST RR_PC_RAW_COST RR_TC_RAW_COST RR_PFC_BRDN_COST RR_PC_BRDN_COST RR_TC_BRDN_COST RR_QUANTITY RR_LABOR_HRS RR_EQUIP_HRS RR_PFC_ACT_RAW_COST RR_PC_ACT_RAW_COST RR_TC_ACT_RAW_COST RR_PFC_ACT_BRDN_COST RR_PC_ACT_BRDN_COST RR_TC_ACT_BRDN_COST RR_ACT_QUANTITY RR_ACT_LABOR_HRS RR_ACT_EQUIP_HRS BSLN_PFC_RAW_COST BSLN_PC_RAW_COST BSLN_TC_RAW_COST BSLN_PFC_BRDN_COST BSLN_PC_BRDN_COST BSLN_TC_BRDN_COST BSLN_QUANTITY BSLN_LABOR_HRS BSLN_EQUIP_HRS RR_BSLN_PFC_RAW_COST RR_BSLN_PC_RAW_COST RR_BSLN_TC_RAW_COST RR_BSLN_PFC_BRDN_COST RR_BSLN_PC_BRDN_COST RR_BSLN_TC_BRDN_COST RR_BSLN_QUANTITY RR_BSLN_LABOR_HRS RR_BSLN_EQUIP_HRS PFC_REVENUE PC_REVENUE TC_REVENUE PFC_ACT_REVENUE PC_ACT_REVENUE TC_ACT_REVENUE RR_PFC_REVENUE RR_PC_REVENUE RR_TC_REVENUE RR_PFC_ACT_REVENUE RR_PC_ACT_REVENUE RR_TC_ACT_REVENUE RR_BSLN_PFC_REVENUE RR_BSLN_PC_REVENUE RR_BSLN_TC_REVENUE BSLN_PFC_REVENUE BSLN_PC_REVENUE BSLN_TC_REVENUE

Query

SQL_Statement

SELECT /*+ LEADING(WBS) */ WBS.PROJECT_ID , WBS.STRUCTURE_VERSION_ID , FACT.PROJECT_ORG_ID , FACT.PROJECT_ELEMENT_ID , DECODE(FACT.RBS_AGGR_LEVEL,'R','L',FACT.RBS_AGGR_LEVEL) RBS_AGGR_LEVEL, DECODE(FACT.WBS_ROLLUP_FLAG,'Y','N','N') WBS_ROLLUP_FLAG, FACT.RBS_VERSION_ID , FACT.RBS_ELEMENT_ID , MAX(DECODE(WBS.PLAN_STATUS_CODE,'W',WBS.PLAN_VERSION_ID,-1)) PLAN_VERSION_ID, NVL(MAX(DECODE(FACT.CURRENCY_CODE,'PJO$NULL', NULL, FACT.CURRENCY_CODE)),'PJO$NULL') CURRENCY_CODE, NVL(MIN(FACT.START_DATE), TRUNC(SYSDATE)) START_DATE, NVL(MAX(FACT.END_DATE), TRUNC(SYSDATE)) END_DATE, MIN(FACT.ACT_START_DATE) ACT_START_DATE, MAX(FACT.ACT_END_DATE) ACT_END_DATE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_RAW_COST),NULL)) PFC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_RAW_COST),NULL)) PC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_RAW_COST),NULL)) TC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_BRDN_COST),NULL)) PFC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_BRDN_COST),NULL)) PC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_BRDN_COST),NULL)) TC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.QUANTITY),NULL)) QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.LABOR_HRS),NULL)) LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.EQUIP_HRS),NULL)) EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_ACT_RAW_COST),NULL)) PFC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_ACT_RAW_COST),NULL)) PC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_ACT_RAW_COST),NULL)) TC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_ACT_BRDN_COST),NULL)) PFC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_ACT_BRDN_COST),NULL)) PC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_ACT_BRDN_COST),NULL)) TC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ACT_QUANTITY),NULL)) ACT_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ACT_LABOR_HRS),NULL)) ACT_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ACT_EQUIP_HRS),NULL)) ACT_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_ETC_RAW_COST),NULL)) PFC_ETC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_ETC_RAW_COST),NULL)) PC_ETC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_ETC_RAW_COST),NULL)) TC_ETC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_ETC_BRDN_COST),NULL)) PFC_ETC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_ETC_BRDN_COST),NULL)) PC_ETC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_ETC_BRDN_COST),NULL)) TC_ETC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ETC_QUANTITY),NULL)) ETC_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ETC_LABOR_HRS),NULL)) ETC_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.ETC_EQUIP_HRS),NULL)) ETC_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_RAW_COST),NULL))RR_PFC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_RAW_COST),NULL)) RR_PC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_RAW_COST),NULL)) RR_TC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_BRDN_COST),NULL)) RR_PFC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_BRDN_COST),NULL)) RR_PC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_BRDN_COST),NULL)) RR_TC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.QUANTITY),NULL)) RR_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.LABOR_HRS),NULL)) RR_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.EQUIP_HRS),NULL)) RR_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_ACT_RAW_COST),NULL)) RR_PFC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_ACT_RAW_COST),NULL)) RR_PC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_ACT_RAW_COST),NULL)) RR_TC_ACT_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_ACT_BRDN_COST),NULL)) RR_PFC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_ACT_BRDN_COST),NULL)) RR_PC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_ACT_BRDN_COST),NULL)) RR_TC_ACT_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.ACT_QUANTITY),NULL)) RR_ACT_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.ACT_LABOR_HRS),NULL)) RR_ACT_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.ACT_EQUIP_HRS),NULL)) RR_ACT_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_RAW_COST),NULL)) BSLN_PFC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_RAW_COST),NULL)) BSLN_PC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_RAW_COST),NULL)) BSLN_TC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_BRDN_COST),NULL)) BSLN_PFC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_BRDN_COST),NULL)) BSLN_PC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_BRDN_COST),NULL)) BSLN_TC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.QUANTITY),NULL)) BSLN_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.LABOR_HRS),NULL)) BSLN_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.EQUIP_HRS),NULL)) BSLN_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_RAW_COST),NULL)) RR_BSLN_PFC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_RAW_COST),NULL)) RR_BSLN_PC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_RAW_COST),NULL)) RR_BSLN_TC_RAW_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_BRDN_COST),NULL)) RR_BSLN_PFC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_BRDN_COST),NULL)) RR_BSLN_PC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_BRDN_COST),NULL)) RR_BSLN_TC_BRDN_COST, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.QUANTITY),NULL)) RR_BSLN_QUANTITY, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.LABOR_HRS),NULL)) RR_BSLN_LABOR_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.EQUIP_HRS),NULL)) RR_BSLN_EQUIP_HRS, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_REVENUE),NULL)) PFC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_REVENUE),NULL)) PC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_REVENUE),NULL)) TC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_ACT_REVENUE),NULL)) PFC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_ACT_REVENUE),NULL)) PC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_ACT_REVENUE),NULL)) TC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_REVENUE),NULL)) RR_PFC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_REVENUE),NULL)) RR_PC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_REVENUE),NULL)) RR_TC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_ACT_REVENUE),NULL)) RR_PFC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_ACT_REVENUE),NULL)) RR_PC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'W',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_ACT_REVENUE),NULL)) RR_TC_ACT_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PFC_REVENUE),NULL)) RR_BSLN_PFC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.PC_REVENUE),NULL)) RR_BSLN_PC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'T',NULL,FACT.TC_REVENUE),NULL)) RR_BSLN_TC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PFC_REVENUE),NULL)) BSLN_PFC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.PC_REVENUE),NULL)) BSLN_PC_REVENUE, SUM(DECODE(WBS.PLAN_STATUS_CODE,'B',DECODE(RBS_AGGR_LEVEL,'R',NULL,FACT.TC_REVENUE),NULL)) BSLN_TC_REVENUE FROM PJO_PLAN_VERSIONS_B WBS, PJO_XBS_ACCUM_F FACT WHERE FACT.PROJECT_ID = WBS.PROJECT_ID AND FACT.PLAN_VERSION_ID = WBS.PLAN_VERSION_ID AND WBS.CURRENT_PLAN_STATUS_FLAG = 'Y' AND ((WBS.PLAN_STATUS_CODE = 'W' AND (FACT.RBS_AGGR_LEVEL = 'T' OR (FACT.RBS_AGGR_LEVEL in ('L','R') AND FACT.WBS_ROLLUP_FLAG = 'N'))) OR (WBS.PLAN_STATUS_CODE = 'B' AND (FACT.RBS_AGGR_LEVEL = 'T' OR (FACT.RBS_AGGR_LEVEL in ('L','R') AND FACT.WBS_ROLLUP_FLAG = 'N')))) AND FACT.PERIOD_TYPE_ID = 2048 AND WBS.STRUCTURE_VERSION_ID IS NOT NULL GROUP BY WBS.PROJECT_ID , WBS.STRUCTURE_VERSION_ID , FACT.PROJECT_ORG_ID , FACT.PROJECT_ELEMENT_ID , DECODE(FACT.RBS_AGGR_LEVEL,'R','L',FACT.RBS_AGGR_LEVEL), DECODE(FACT.WBS_ROLLUP_FLAG,'Y','N','N'), FACT.RBS_VERSION_ID , FACT.RBS_ELEMENT_ID