PJO_DIS_PD_DETAIL_ALL_V

Details

• Schema: FUSION

• Object owner: PJO

• Object type: VIEW

Columns

Name

PERIOD_NAME START_PERIOD_NAME END_PERIOD_NAME START_DATE END_DATE SESSION_ID FROM_ANCHOR_START PLANNING_ELEMENT_ID PERIOD_PROFILE_ID CURRENT_PERIOD_NAME PROJECT_ID OBJECT_ID1 TASK_ID RBS_ELEMENT_ID TXN_CURRENCY_CODE PLAN_VERSION_ID PLAN_TYPE_ID PLANNING_START_DATE PLANNING_END_DATE UNIT_OF_MEASURE SPREAD_CURVE_ID MARGIN_DERIVED_FROM_CODE PLAN_LINE_ID ETC_START_DATE PLAN_CLASS_CODE FROM_ANCHOR_POSITION DISPLAY_NAME SP_FIXED_DATE BURDEN_MULTIPLIER PROJECT_CURRENCY_CODE PROJFUNC_CURRENCY_CODE QUANTITY TC_RAW_COST PC_RAW_COST PFC_RAW_COST TC_BRDND_COST PC_BRDND_COST PFC_BRDND_COST TC_REVENUE PC_REVENUE PFC_REVENUE TC_MARGIN PC_MARGIN PFC_MARGIN TC_MARGIN_PCT PC_MARGIN_PCT PFC_MARGIN_PCT ACT_QUANTITY TC_ACT_RAW_COST PC_ACT_RAW_COST PFC_ACT_RAW_COST TC_ACT_BRDND_COST PC_ACT_BRDND_COST PFC_ACT_BRDND_COST TC_ACT_REVENUE PC_ACT_REVENUE PFC_ACT_REVENUE TC_ACT_MARGIN PC_ACT_MARGIN PFC_ACT_MARGIN QUANTITY_ORIG TC_RAW_COST_ORIG TC_BRDND_COST_ORIG TC_REVENUE_ORIG ETC_QUANTITY_ORIG TC_ETC_RAW_COST_ORIG TC_ETC_BRDND_COST_ORIG TC_ETC_REVENUE_ORIG TC_BILL_RATE_ORIG TC_RAW_COST_RATE_ORIG TC_BRDND_COST_RATE_ORIG PFC_COST_RATE_TYPE PFC_COST_EXCHANGE_RATE PFC_COST_RATE_DATE_TYPE PFC_COST_RATE_DATE PFC_REV_RATE_TYPE PFC_REV_EXCHANGE_RATE PFC_REV_RATE_DATE_TYPE PFC_REV_RATE_DATE PC_COST_RATE_TYPE PC_COST_EXCHANGE_RATE PC_COST_RATE_DATE_TYPE PC_COST_RATE_DATE PC_REV_RATE_TYPE PC_REV_EXCHANGE_RATE PC_REV_RATE_DATE_TYPE PC_REV_RATE_DATE COST_REJ_CODE BURD_REJ_CODE PC_REJ_CODE PFC_REJ_CODE TC_BILL_RATE TC_RAW_COST_RATE TC_BRDND_COST_RATE TC_ACT_RAW_COST_RATE PC_ACT_RAW_COST_RATE PFC_ACT_RAW_COST_RATE TC_ACT_BRDND_COST_RATE PC_ACT_BRDND_COST_RATE PFC_ACT_BRDND_COST_RATE TC_ACT_BILL_RATE PC_ACT_BILL_RATE PFC_ACT_BILL_RATE ETC_QUANTITY TC_ETC_RAW_COST PC_ETC_RAW_COST PFC_ETC_RAW_COST TC_ETC_BRDND_COST PC_ETC_BRDND_COST PFC_ETC_BRDND_COST TC_ETC_REVENUE PC_ETC_REVENUE PFC_ETC_REVENUE CHANGE_QUANTITY TC_CHANGE_RAW_COST PC_CHANGE_RAW_COST PFC_CHANGE_RAW_COST TC_CHANGE_BRDND_COST PC_CHANGE_BRDND_COST PFC_CHANGE_BRDND_COST TC_CHANGE_REVENUE PC_CHANGE_REVENUE PFC_CHANGE_REVENUE PO_QUANTITY TC_PO_RAW_COST PC_PO_RAW_COST PFC_PO_RAW_COST TC_PO_BRDND_COST PC_PO_BRDND_COST PFC_PO_BRDND_COST SI_QUANTITY TC_SI_RAW_COST PC_SI_RAW_COST PFC_SI_RAW_COST TC_SI_BRDND_COST PC_SI_BRDND_COST PFC_SI_BRDND_COST EXT_QUANTITY TC_EXT_RAW_COST PC_EXT_RAW_COST PFC_EXT_RAW_COST TC_EXT_BRDND_COST PC_EXT_BRDND_COST PFC_EXT_BRDND_COST REQ_QUANTITY TC_REQ_RAW_COST PC_REQ_RAW_COST PFC_REQ_RAW_COST TC_REQ_BRDND_COST PC_REQ_BRDND_COST PFC_REQ_BRDND_COST TO_QUANTITY TC_TO_RAW_COST PC_TO_RAW_COST PFC_TO_RAW_COST TC_TO_BRDND_COST PC_TO_BRDND_COST PFC_TO_BRDND_COST RCPT_QUANTITY TC_RCPT_RAW_COST PC_RCPT_RAW_COST PFC_RCPT_RAW_COST TC_RCPT_BRDND_COST PC_RCPT_BRDND_COST PFC_RCPT_BRDND_COST TC_STD_BRDND_MULTIPLIER TC_STD_BRDND_COST_RATE TC_STD_RAW_COST_RATE TC_STD_BILL_RATE TC_MARKUP_PERCENT RATE_UPDATED_DATE MANUAL_SPREAD_FLAG CHANGE_DOCUMENT_ID CHANGE_IMPACT_ID ADDL_ATTRIBUTE_NUM_3

Query

SQL_Statement

select PPE.PERIOD_PROMPT as PERIOD_NAME, PPE.start_period_name, PPE.end_period_name, PPE.start_date, PPE.end_date, PPE.SESSION_ID, PPE.FROM_ANCHOR_POSITION as FROM_ANCHOR_START, ppe.planning_element_id, PPE.period_profile_id, PPE.CURRENT_REPORTING_PERIOD as CURRENT_PERIOD_NAME, ppe.project_id, ppe.object_id1, ppe.task_id, ppe.rbs_element_id, ppe.txn_currency_code, ppe.plan_Version_id, ppo.plan_type_id, ppe.PLANNING_START_DATE, ppe.PLANNING_END_DATE, ppe.UNIT_OF_MEASURE, ppe.SPREAD_CURVE_ID, ppo.MARGIN_DERIVED_FROM_CODE, ppe.PLAN_LINE_ID, pvr.ETC_START_DATE, pvr.PLAN_CLASS_CODE, null FROM_ANCHOR_POSITION, null DISPLAY_NAME, to_date(null) as SP_FIXED_DATE, to_number(null) as BURDEN_MULTIPLIER, PRJ.PROJECT_CURRENCY_CODE, PRJ.PROJFUNC_CURRENCY_CODE, decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity)) as QUANTITY, decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost)) as TC_RAW_COST, decode(sum(ppld.pc_raw_cost),0,to_number(NULL),sum(ppld.pc_raw_cost)) as PC_RAW_COST, decode(sum(ppld.pfc_raw_cost),0,to_number(NULL),sum(ppld.pfc_raw_cost)) as PFC_RAW_COST, decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost)) as TC_BRDND_COST, decode(sum(ppld.pc_brdnd_cost),0,to_number(NULL),sum(ppld.pc_brdnd_cost)) as PC_BRDND_COST, decode(sum(ppld.pfc_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_brdnd_cost)) as PFC_BRDND_COST, decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)) as TC_REVENUE, decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)) as PC_REVENUE, decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)) as PFC_REVENUE, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_revenue) - sum(ppld.tc_brdnd_cost)),to_number(sum(ppld.tc_revenue) - sum(ppld.tc_raw_cost))) as TC_MARGIN, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pc_revenue) - sum(ppld.pc_brdnd_cost)),to_number(sum(ppld.pc_revenue) - sum(ppld.pc_raw_cost))) as PC_MARGIN, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_brdnd_cost)),to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_raw_cost))) as PFC_MARGIN, (decode(nvl((DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_revenue) - sum(ppld.tc_brdnd_cost)),to_number(sum(ppld.tc_revenue) - sum(ppld.tc_raw_cost)))), 0),0, to_number(NULL), (DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_revenue) - sum(ppld.tc_brdnd_cost)),to_number(sum(ppld.tc_revenue) - sum(ppld.tc_raw_cost))))) / decode(nvl((decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue))), 0),0, to_number(NULL), (decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue))))) as TC_MARGIN_PCT, (decode(nvl((DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pc_revenue) - sum(ppld.pc_brdnd_cost)),to_number(sum(ppld.pc_revenue) - sum(ppld.pc_raw_cost)))), 0),0, to_number(NULL), (DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pc_revenue) - sum(ppld.pc_brdnd_cost)),to_number(sum(ppld.pc_revenue) - sum(ppld.pc_raw_cost))))) / decode(nvl((decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue))), 0),0, to_number(NULL), (decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue))))) as PC_MARGIN_PCT, (decode(nvl((DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_brdnd_cost)),to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_raw_cost)))), 0),0, to_number(NULL), (DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_brdnd_cost)),to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_raw_cost))))) / decode(nvl((decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue))), 0),0, to_number(NULL), (decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue))))) as PFC_MARGIN_PCT, decode(sum(ppld.act_quantity),0,to_number(NULL),sum(ppld.act_quantity)) as ACT_QUANTITY, decode(sum(ppld.tc_act_raw_cost),0,to_number(NULL),sum(ppld.tc_act_raw_cost)) as TC_ACT_RAW_COST, decode(sum(ppld.pc_act_raw_cost),0,to_number(NULL),sum(ppld.pc_act_raw_cost)) as PC_ACT_RAW_COST, decode(sum(ppld.pfc_act_raw_cost),0,to_number(NULL),sum(ppld.pfc_act_raw_cost)) as PFC_ACT_RAW_COST, decode(sum(ppld.tc_act_brdnd_cost),0,to_number(NULL),sum(ppld.tc_act_brdnd_cost)) as TC_ACT_BRDND_COST, decode(sum(ppld.pc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pc_act_brdnd_cost)) as PC_ACT_BRDND_COST, decode(sum(ppld.pfc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_act_brdnd_cost)) as PFC_ACT_BRDND_COST, decode(sum(ppld.tc_act_revenue),0,to_number(null),sum(ppld.tc_act_revenue)) as TC_ACT_REVENUE, decode(sum(ppld.pc_act_revenue),0,to_number(NULL),sum(ppld.pc_act_revenue)) as PC_ACT_REVENUE, decode(sum(ppld.pfc_act_revenue),0,to_number(NULL),sum(ppld.pfc_act_revenue)) as PFC_ACT_REVENUE, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_act_revenue) - sum(ppld.tc_act_brdnd_cost)),to_number(sum(ppld.tc_act_revenue) - sum(ppld.tc_act_raw_cost))) as TC_ACT_MARGIN, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.pc_act_revenue) - sum(ppld.pc_act_brdnd_cost)),to_number(sum(ppld.pc_act_revenue) - sum(ppld.pc_act_raw_cost))) as PC_ACT_MARGIN, DECODE(ppo.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.pfc_act_revenue) - sum(ppld.pfc_act_brdnd_cost)),to_number(sum(ppld.pfc_act_revenue) - sum(ppld.pfc_act_raw_cost))) as PFC_ACT_MARGIN, decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity)) as QUANTITY_ORIG, decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost)) as TC_RAW_COST_ORIG, decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost)) as TC_BRDND_COST_ORIG, decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)) as TC_REVENUE_ORIG, to_number(null) as ETC_QUANTITY_ORIG, to_number(null) as TC_ETC_RAW_COST_ORIG, to_number(null) as TC_ETC_BRDND_COST_ORIG, to_number(null) as TC_ETC_REVENUE_ORIG, to_number(null) as TC_BILL_RATE_ORIG, to_number(null) as TC_RAW_COST_RATE_ORIG, to_number(null) as TC_BRDND_COST_RATE_ORIG, pcu.PFC_COST_RATE_TYPE as PFC_COST_RATE_TYPE, pcu.PFC_COST_EXCHANGE_RATE, pcu.PFC_COST_RATE_DATE_TYPE as PFC_COST_RATE_DATE_TYPE, pcu.PFC_COST_RATE_DATE as PFC_COST_RATE_DATE, pcu.PFC_REV_RATE_TYPE as PFC_REV_RATE_TYPE, pcu.PFC_REV_EXCHANGE_RATE, pcu.PFC_REV_RATE_DATE_TYPE as PFC_REV_RATE_DATE_TYPE, pcu.PFC_REV_RATE_DATE as PFC_REV_RATE_DATE, pcu.PC_COST_RATE_TYPE as PC_COST_RATE_TYPE, pcu.PC_COST_EXCHANGE_RATE, pcu.PC_COST_RATE_DATE_TYPE as PC_COST_RATE_DATE_TYPE, pcu.PC_COST_RATE_DATE as PC_COST_RATE_DATE, pcu.PC_REV_RATE_TYPE as PC_REV_RATE_TYPE, pcu.PC_REV_EXCHANGE_RATE, pcu.PC_REV_RATE_DATE_TYPE as PC_REV_RATE_DATE_TYPE, pcu.PC_REV_RATE_DATE as PC_REV_RATE_DATE, to_number(null) as COST_REJ_CODE, to_number(null) as BURD_REJ_CODE, to_number(null) as PC_REJ_CODE, to_number(null) as PFC_REJ_CODE, (sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity)))))) as TC_BILL_RATE, (sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity)))))) as TC_RAW_COST_RATE, (sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity)))))) as TC_BRDND_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.tc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.tc_act_raw_cost))/ SUM(ppld.act_quantity))) as TC_ACT_RAW_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.pc_act_raw_cost))/ SUM(ppld.act_quantity))) as PC_ACT_RAW_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pfc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.pfc_act_raw_cost))/ SUM(ppld.act_quantity))) as PFC_ACT_RAW_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.tc_act_brdnd_cost),0, TO_NUMBER (NULL),SUM (ppld.tc_act_brdnd_cost))/ SUM(ppld.act_quantity))) as TC_ACT_BRDND_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pc_act_brdnd_cost),0, TO_NUMBER (NULL),SUM (ppld.pc_act_brdnd_cost))/ SUM(ppld.act_quantity))) as PC_ACT_BRDND_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pfc_act_brdnd_cost),0, TO_NUMBER (NULL),SUM (ppld.pfc_act_brdnd_cost))/ SUM(ppld.act_quantity))) as PFC_ACT_BRDND_COST_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL),(DECODE (SUM (ppld.tc_act_revenue),0, TO_NUMBER (NULL),SUM (ppld.tc_act_revenue))/ SUM (ppld.act_quantity))) as TC_ACT_BILL_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL),(DECODE (SUM (ppld.pc_act_revenue),0, TO_NUMBER (NULL),SUM (ppld.pc_act_revenue))/ SUM (ppld.act_quantity))) as PC_ACT_BILL_RATE, DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL),(DECODE (SUM (ppld.pfc_act_revenue),0, TO_NUMBER (NULL),SUM (ppld.pfc_act_revenue))/ SUM (ppld.act_quantity))) as PFC_ACT_BILL_RATE, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity))), 0) - NVL((decode(sum(ppld.act_quantity),0,to_number(NULL),sum(ppld.act_quantity))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity))), 0) - NVL((decode(sum(ppld.act_quantity),0,to_number(NULL),sum(ppld.act_quantity))), 0)))) as ETC_QUANTITY, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost))), 0) - NVL((decode(sum(ppld.tc_act_raw_cost),0,to_number(NULL),sum(ppld.tc_act_raw_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost))), 0) - NVL((decode(sum(ppld.tc_act_raw_cost),0,to_number(NULL),sum(ppld.tc_act_raw_cost))),0)))) as TC_ETC_RAW_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pc_raw_cost),0,to_number(NULL),sum(ppld.pc_raw_cost))), 0) - NVL((decode(sum(ppld.pc_act_raw_cost),0,to_number(NULL),sum(ppld.pc_act_raw_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pc_raw_cost),0,to_number(NULL),sum(ppld.pc_raw_cost))), 0) - NVL((decode(sum(ppld.pc_act_raw_cost),0,to_number(NULL),sum(ppld.pc_act_raw_cost))),0)))) as PC_ETC_RAW_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pfc_raw_cost),0,to_number(NULL),sum(ppld.pfc_raw_cost))), 0) - NVL((decode(sum(ppld.pfc_act_raw_cost),0,to_number(NULL),sum(ppld.pfc_act_raw_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pfc_raw_cost),0,to_number(NULL),sum(ppld.pfc_raw_cost))), 0) - NVL((decode(sum(ppld.pfc_act_raw_cost),0,to_number(NULL),sum(ppld.pfc_act_raw_cost))),0)))) as PFC_ETC_RAW_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost))), 0) - NVL((decode(sum(ppld.tc_act_brdnd_cost),0,to_number(NULL),sum(ppld.tc_act_brdnd_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost))), 0) - NVL((decode(sum(ppld.tc_act_brdnd_cost),0,to_number(NULL),sum(ppld.tc_act_brdnd_cost))),0)))) as TC_ETC_BRDND_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pc_brdnd_cost),0,to_number(NULL),sum(ppld.pc_brdnd_cost))), 0) - NVL((decode(sum(ppld.pc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pc_act_brdnd_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pc_brdnd_cost),0,to_number(NULL),sum(ppld.pc_brdnd_cost))), 0) - NVL((decode(sum(ppld.pc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pc_act_brdnd_cost))),0)))) as PC_ETC_BRDND_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pfc_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_brdnd_cost))), 0) - NVL((decode(sum(ppld.pfc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_act_brdnd_cost))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pfc_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_brdnd_cost))), 0) - NVL((decode(sum(ppld.pfc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_act_brdnd_cost))),0)))) as PFC_ETC_BRDND_COST, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue))), 0) - NVL((decode(sum(ppld.tc_act_revenue),0,to_number(null),sum(ppld.tc_act_revenue))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue))), 0) - NVL((decode(sum(ppld.tc_act_revenue),0,to_number(null),sum(ppld.tc_act_revenue))),0)))) as TC_ETC_REVENUE, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue))), 0) - NVL((decode(sum(ppld.pc_act_revenue),0,to_number(NULL),sum(ppld.pc_act_revenue))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue))), 0) - NVL((decode(sum(ppld.pc_act_revenue),0,to_number(NULL),sum(ppld.pc_act_revenue))),0)))) as PC_ETC_REVENUE, DECODE(SIGN(PPE.end_date - pvr.ETC_START_DATE), -1, to_number(null), decode((NVL((decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue))), 0) - NVL((decode(sum(ppld.pfc_act_revenue),0,to_number(NULL),sum(ppld.pfc_act_revenue))), 0)), 0, to_number(null), (NVL((decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue))), 0) - NVL((decode(sum(ppld.pfc_act_revenue),0,to_number(NULL),sum(ppld.pfc_act_revenue))),0)))) as PFC_ETC_REVENUE, to_number(null) as CHANGE_QUANTITY, to_number(null) as TC_CHANGE_RAW_COST, to_number(null) as PC_CHANGE_RAW_COST, to_number(null) as PFC_CHANGE_RAW_COST, to_number(null) as TC_CHANGE_BRDND_COST, to_number(null) as PC_CHANGE_BRDND_COST, to_number(null) as PFC_CHANGE_BRDND_COST, to_number(null) as TC_CHANGE_REVENUE, to_number(null) as PC_CHANGE_REVENUE, to_number(null) as PFC_CHANGE_REVENUE, decode(sum(ppld.po_quantity),0,to_number(NULL),sum(ppld.po_quantity)) as PO_QUANTITY, decode(sum(ppld.tc_po_raw_cost),0,to_number(NULL),sum(ppld.tc_po_raw_cost)) as TC_PO_RAW_COST, decode(sum(ppld.pc_po_raw_cost),0,to_number(NULL),sum(ppld.pc_po_raw_cost)) as PC_PO_RAW_COST, decode(sum(ppld.pfc_po_raw_cost),0,to_number(NULL),sum(ppld.pfc_po_raw_cost)) as PFC_PO_RAW_COST, decode(sum(ppld.tc_po_brdnd_cost),0,to_number(NULL),sum(ppld.tc_po_brdnd_cost)) as TC_PO_BRDND_COST, decode(sum(ppld.pc_po_brdnd_cost),0,to_number(NULL),sum(ppld.pc_po_brdnd_cost)) as PC_PO_BRDND_COST, decode(sum(ppld.pfc_po_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_po_brdnd_cost)) as PFC_PO_BRDND_COST, decode(sum(ppld.si_quantity),0,to_number(NULL),sum(ppld.si_quantity)) as SI_QUANTITY, decode(sum(ppld.tc_si_raw_cost),0,to_number(NULL),sum(ppld.tc_si_raw_cost)) as TC_SI_RAW_COST, decode(sum(ppld.pc_si_raw_cost),0,to_number(NULL),sum(ppld.pc_si_raw_cost)) as PC_SI_RAW_COST, decode(sum(ppld.pfc_si_raw_cost),0,to_number(NULL),sum(ppld.pfc_si_raw_cost)) as PFC_SI_RAW_COST, decode(sum(ppld.tc_si_brdnd_cost),0,to_number(NULL),sum(ppld.tc_si_brdnd_cost)) as TC_SI_BRDND_COST, decode(sum(ppld.pc_si_brdnd_cost),0,to_number(NULL),sum(ppld.pc_si_brdnd_cost)) as PC_SI_BRDND_COST, decode(sum(ppld.pfc_si_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_si_brdnd_cost)) as PFC_SI_BRDND_COST, decode(sum(ppld.ext_quantity),0,to_number(NULL),sum(ppld.ext_quantity)) as EXT_QUANTITY, decode(sum(ppld.tc_ext_raw_cost),0,to_number(NULL),sum(ppld.tc_ext_raw_cost)) as TC_EXT_RAW_COST, decode(sum(ppld.pc_ext_raw_cost),0,to_number(NULL),sum(ppld.pc_ext_raw_cost)) as PC_EXT_RAW_COST, decode(sum(ppld.pfc_ext_raw_cost),0,to_number(NULL),sum(ppld.pfc_ext_raw_cost)) as PFC_EXT_RAW_COST, decode(sum(ppld.tc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.tc_ext_brdnd_cost)) as TC_EXT_BRDND_COST, decode(sum(ppld.pc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.pc_ext_brdnd_cost)) as PC_EXT_BRDND_COST, decode(sum(ppld.pfc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_ext_brdnd_cost)) as PFC_EXT_BRDND_COST, decode(sum(ppld.req_quantity),0,to_number(NULL),sum(ppld.req_quantity)) as REQ_QUANTITY, decode(sum(ppld.tc_req_raw_cost),0,to_number(NULL),sum(ppld.tc_req_raw_cost)) as TC_REQ_RAW_COST, decode(sum(ppld.pc_req_raw_cost),0,to_number(NULL),sum(ppld.pc_req_raw_cost)) as PC_REQ_RAW_COST, decode(sum(ppld.pfc_req_raw_cost),0,to_number(NULL),sum(ppld.pfc_req_raw_cost)) as PFC_REQ_RAW_COST, decode(sum(ppld.tc_req_brdnd_cost),0,to_number(NULL),sum(ppld.tc_req_brdnd_cost)) as TC_REQ_BRDND_COST, decode(sum(ppld.pc_req_brdnd_cost),0,to_number(NULL),sum(ppld.pc_req_brdnd_cost)) as PC_REQ_BRDND_COST, decode(sum(ppld.pfc_req_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_req_brdnd_cost)) as PFC_REQ_BRDND_COST, decode(sum(ppld.to_quantity),0,to_number(NULL),sum(ppld.to_quantity)) as TO_QUANTITY, decode(sum(ppld.tc_to_raw_cost),0,to_number(NULL),sum(ppld.tc_to_raw_cost)) as TC_TO_RAW_COST, decode(sum(ppld.pc_to_raw_cost),0,to_number(NULL),sum(ppld.pc_to_raw_cost)) as PC_TO_RAW_COST, decode(sum(ppld.pfc_to_raw_cost),0,to_number(NULL),sum(ppld.pfc_to_raw_cost)) as PFC_TO_RAW_COST, decode(sum(ppld.tc_to_brdnd_cost),0,to_number(NULL),sum(ppld.tc_to_brdnd_cost)) as TC_TO_BRDND_COST, decode(sum(ppld.pc_to_brdnd_cost),0,to_number(NULL),sum(ppld.pc_to_brdnd_cost)) as PC_TO_BRDND_COST, decode(sum(ppld.pfc_to_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_to_brdnd_cost)) as PFC_TO_BRDND_COST, decode(sum(ppld.rcpt_quantity),0,to_number(NULL),sum(ppld.rcpt_quantity)) as RCPT_QUANTITY, decode(sum(ppld.tc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.tc_rcpt_raw_cost)) as TC_RCPT_RAW_COST, decode(sum(ppld.pc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.pc_rcpt_raw_cost)) as PC_RCPT_RAW_COST, decode(sum(ppld.pfc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.pfc_rcpt_raw_cost)) as PFC_RCPT_RAW_COST, decode(sum(ppld.tc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.tc_rcpt_brdnd_cost)) as TC_RCPT_BRDND_COST, decode(sum(ppld.pc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.pc_rcpt_brdnd_cost)) as PC_RCPT_BRDND_COST, decode(sum(ppld.pfc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_rcpt_brdnd_cost)) as PFC_RCPT_BRDND_COST, Avg(ppld.TC_STD_BRDND_MULTIPLIER) as TC_STD_BRDND_MULTIPLIER, Avg(ppld.TC_STD_BRDND_COST_RATE) as TC_STD_BRDND_COST_RATE, Avg(ppld.TC_STD_RAW_COST_RATE) as TC_STD_RAW_COST_RATE, Avg(ppld.TC_STD_BILL_RATE) as TC_STD_BILL_RATE, Avg(ppld.TC_MARKUP_PERCENT) as TC_MARKUP_PERCENT, max(ppld.rate_updated_date) as rate_updated_date, max(ppld.manual_spread_flag) as manual_spread_flag, PPE.change_document_id, PPE.change_impact_id, PPE.ADDL_ATTRIBUTE_NUM_3 from pjo_plan_line_details ppld, PJO_PLANNING_OPTIONS PPO, PJO_DIS_PD_PERIOD_V PPE, PJF_PROJECTS_ALL_B PRJ, PJO_PLAN_VERSIONS_B PVR, PJO_DIS_PD_CURRENCY_ALL_V PCU WHERE ppe.planning_element_id = ppld.planning_element_id(+) and ppe.TXN_CURRENCY_CODE = ppld.TXN_CURRENCY_CODE(+) and ppe.planning_element_id = pcu.PLANNING_ELEMENT_ID and ppe.TXN_CURRENCY_CODE = pcu.TXN_CURRENCY_CODE and ppe.START_DATE = pcu.START_DATE and ppe.SESSION_ID = pcu.SESSION_ID and ppe.project_id = prj.project_id and ppe.plan_version_id = pvr.plan_version_id and ppe.plan_version_id = ppo.plan_version_id and CASE WHEN PVR.OBJECT_TYPE_CODE = 'AWARD' AND ppld.period_name(+) = ppe.start_period_name THEN 1 WHEN PVR.OBJECT_TYPE_CODE = 'PROJECT' AND ppld.start_date(+) <= PPE.end_date and ppld.end_date(+) >= PPE.start_date THEN 1 ELSE 0 END = 1 group by PPE.PERIOD_PROMPT, PPE.start_period_name, PPE.end_period_name, PPE.start_date, PPE.end_date, PPE.SESSION_ID, PPE.FROM_ANCHOR_POSITION, ppe.planning_element_id, PPE.period_profile_id, PPE.CURRENT_REPORTING_PERIOD, ppe.project_id, ppe.object_id1, ppe.task_id, ppe.rbs_element_id, ppe.txn_currency_code, ppe.plan_Version_id, ppo.plan_type_id, ppe.PLANNING_START_DATE, ppe.PLANNING_END_DATE, ppe.UNIT_OF_MEASURE, ppe.SPREAD_CURVE_ID, ppo.MARGIN_DERIVED_FROM_CODE, ppe.PLAN_LINE_ID, pvr.ETC_START_DATE, pvr.PLAN_CLASS_CODE, PRJ.PROJECT_CURRENCY_CODE, PRJ.PROJFUNC_CURRENCY_CODE, pcu.PFC_COST_RATE_TYPE, pcu.PFC_COST_EXCHANGE_RATE, pcu.PFC_COST_RATE_DATE_TYPE, pcu.PFC_COST_RATE_DATE, pcu.PFC_REV_RATE_TYPE, pcu.PFC_REV_EXCHANGE_RATE, pcu.PFC_REV_RATE_DATE_TYPE, pcu.PFC_REV_RATE_DATE, pcu.PC_COST_RATE_TYPE, pcu.PC_COST_EXCHANGE_RATE, pcu.PC_COST_RATE_DATE_TYPE, pcu.PC_COST_RATE_DATE, pcu.PC_REV_RATE_TYPE, pcu.PC_REV_EXCHANGE_RATE, pcu.PC_REV_RATE_DATE_TYPE, pcu.PC_REV_RATE_DATE, PPE.change_document_id, PPE.change_impact_id, PPE.ADDL_ATTRIBUTE_NUM_3