PJO_CURR_PERIOD_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 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 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 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 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 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

Query

SQL_Statement

SELECT cr.PROJECT_ID , cr.STRUCTURE_VERSION_ID , cr.PROJECT_ORG_ID , cr.PROJECT_ELEMENT_ID , cr.RBS_AGGR_LEVEL , cr.WBS_ROLLUP_FLAG , cr.RBS_VERSION_ID , cr.RBS_ELEMENT_ID , cr.PLAN_VERSION_ID , cr.CURRENCY_CODE, LEAST(NVL(cr.START_DATE,TRUNC(SYSDATE)),NVL(pr.START_DATE,TRUNC(SYSDATE))) START_DATE , GREATEST(NVL(cr.END_DATE,TRUNC(SYSDATE)),NVL(pr.END_DATE,TRUNC(SYSDATE))) END_DATE, NVL(cr.ACT_START_DATE, pr.ACT_START_DATE) ACT_START_DATE, NVL(cr.ACT_END_DATE, pr.ACT_END_DATE) ACT_END_DATE, cr.PFC_RAW_COST, cr.PC_RAW_COST, cr.TC_RAW_COST, cr.PFC_BRDN_COST, cr.PC_BRDN_COST, cr.TC_BRDN_COST, cr.QUANTITY, cr.LABOR_HRS, cr.EQUIP_HRS, cr.RR_PFC_RAW_COST RR_PFC_RAW_COST, cr.RR_PC_RAW_COST RR_PC_RAW_COST, cr.RR_TC_RAW_COST RR_TC_RAW_COST, cr.RR_PFC_BRDN_COST RR_PFC_BRDN_COST, cr.RR_PC_BRDN_COST RR_PC_BRDN_COST, cr.RR_TC_BRDN_COST RR_TC_BRDN_COST, cr.RR_QUANTITY RR_QUANTITY, cr.RR_LABOR_HRS RR_LABOR_HRS, cr.RR_EQUIP_HRS RR_EQUIP_HRS, pr.PFC_ACT_RAW_COST PFC_ACT_RAW_COST, pr.PC_ACT_RAW_COST PC_ACT_RAW_COST, pr.TC_ACT_RAW_COST TC_ACT_RAW_COST, pr.PFC_ACT_BRDN_COST PFC_ACT_BRDN_COST, pr.PC_ACT_BRDN_COST PC_ACT_BRDN_COST, pr.TC_ACT_BRDN_COST TC_ACT_BRDN_COST, pr.ACT_QUANTITY ACT_QUANTITY, pr.ACT_LABOR_HRS ACT_LABOR_HRS, pr.ACT_EQUIP_HRS ACT_EQUIP_HRS, pr.RR_PFC_ACT_RAW_COST RR_PFC_ACT_RAW_COST, pr.RR_PC_ACT_RAW_COST RR_PC_ACT_RAW_COST, pr.RR_TC_ACT_RAW_COST RR_TC_ACT_RAW_COST, pr.RR_PFC_ACT_BRDN_COST RR_PFC_ACT_BRDN_COST, pr.RR_PC_ACT_BRDN_COST RR_PC_ACT_BRDN_COST, pr.RR_TC_ACT_BRDN_COST RR_TC_ACT_BRDN_COST, pr.RR_ACT_QUANTITY RR_ACT_QUANTITY, pr.RR_ACT_LABOR_HRS RR_ACT_LABOR_HRS, pr.RR_ACT_EQUIP_HRS RR_ACT_EQUIP_HRS, cr.PFC_ETC_RAW_COST, cr.PC_ETC_RAW_COST, cr.TC_ETC_RAW_COST, cr.PFC_ETC_BRDN_COST, cr.PC_ETC_BRDN_COST, cr.TC_ETC_BRDN_COST, cr.ETC_QUANTITY, cr.ETC_LABOR_HRS, cr.ETC_EQUIP_HRS, cr.BSLN_PFC_RAW_COST, cr.BSLN_PC_RAW_COST, cr.BSLN_TC_RAW_COST, cr.BSLN_PFC_BRDN_COST, cr.BSLN_PC_BRDN_COST, cr.BSLN_TC_BRDN_COST, cr.BSLN_QUANTITY, cr.BSLN_LABOR_HRS, cr.BSLN_EQUIP_HRS, cr.RR_BSLN_PFC_RAW_COST, cr.RR_BSLN_PC_RAW_COST, cr.RR_BSLN_TC_RAW_COST, cr.RR_BSLN_PFC_BRDN_COST, cr.RR_BSLN_PC_BRDN_COST, cr.RR_BSLN_TC_BRDN_COST, cr.RR_BSLN_QUANTITY, cr.RR_BSLN_LABOR_HRS, cr.RR_BSLN_EQUIP_HRS FROM PJO_PROJ_PLAN_XBS_ACCUM_V cr, ( 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 , WBS.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(RBS_AGGR_LEVEL,'R',NULL,PFC_ACT_RAW_COST)) PFC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,PC_ACT_RAW_COST)) PC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,TC_ACT_RAW_COST)) TC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,PFC_ACT_BRDN_COST)) PFC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,PC_ACT_BRDN_COST)) PC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,TC_ACT_BRDN_COST)) TC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,ACT_QUANTITY)) ACT_QUANTITY, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,ACT_LABOR_HRS)) ACT_LABOR_HRS, SUM(DECODE(RBS_AGGR_LEVEL,'R',NULL,ACT_EQUIP_HRS)) ACT_EQUIP_HRS, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,PFC_ACT_RAW_COST)) RR_PFC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,PC_ACT_RAW_COST)) RR_PC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,TC_ACT_RAW_COST)) RR_TC_ACT_RAW_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,PFC_ACT_BRDN_COST)) RR_PFC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,PC_ACT_BRDN_COST)) RR_PC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,TC_ACT_BRDN_COST)) RR_TC_ACT_BRDN_COST, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,ACT_QUANTITY)) RR_ACT_QUANTITY, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,ACT_LABOR_HRS)) RR_ACT_LABOR_HRS, SUM(DECODE(RBS_AGGR_LEVEL,'T',NULL,ACT_EQUIP_HRS)) RR_ACT_EQUIP_HRS FROM PJO_PLAN_VERSIONS_B WBS, PJO_XBS_ACCUM_F FACT, PJO_PLANNING_OPTIONS PPO WHERE FACT.PROJECT_ID = WBS.PROJECT_ID AND FACT.PLAN_VERSION_ID = WBS.PLAN_VERSION_ID AND (WBS.PLAN_STATUS_CODE = 'W' AND WBS.CURRENT_PLAN_STATUS_FLAG = 'Y' AND (FACT.RBS_AGGR_LEVEL = 'T' OR (FACT.RBS_AGGR_LEVEL in ('L','R') AND FACT.WBS_ROLLUP_FLAG = 'N'))) AND PPO.PLAN_VERSION_ID = WBS.PLAN_VERSION_ID AND FACT.PERIOD_TYPE_ID = DECODE(PPO.TIME_PHASED_CODE,'N',2048,32) AND FACT.START_DATE <= TRUNC(SYSDATE) 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 , WBS.PLAN_VERSION_ID ) pr WHERE cr.PROJECT_ID = pr.PROJECT_ID(+) AND cr.STRUCTURE_VERSION_ID = pr.STRUCTURE_VERSION_ID (+) AND cr.PROJECT_ORG_ID = pr.PROJECT_ORG_ID(+) AND cr.PROJECT_ELEMENT_ID = pr.PROJECT_ELEMENT_ID(+) AND cr.RBS_AGGR_LEVEL = pr.RBS_AGGR_LEVEL(+) AND cr.WBS_ROLLUP_FLAG = pr.WBS_ROLLUP_FLAG(+) AND cr.RBS_VERSION_ID = pr.RBS_VERSION_ID(+) AND cr.RBS_ELEMENT_ID = pr.RBS_ELEMENT_ID(+) AND cr.plan_version_id = pr.plan_version_id(+) AND NVL(cr.CURRENCY_CODE,'TXN') = NVL(pr.CURRENCY_CODE(+),'TXN')