PJS_FP_PROJ_V

Details

• Schema: FUSION

• Object owner: PJS

• Object type: VIEW

Columns

Name

PJS_SUMMARY_ID PROJECT_ID PROJECT_ELEMENT_ID PROJECT_ELEMENT_ID_FLAG CALENDAR_ID CALENDAR_TYPE PERIOD_NAME TIME_ID CURRENCY_CODE CURRENCY_TYPE START_DATE END_DATE SCENARIO TXN_ACCUM_HEADER_ID UOM_CODE RBS_VERSION_ID RBS_ELEMENT_ID RBS_ELEMENT_ID_FLAG RESOURCE_CLASS_ID QUANTITY RAW_COST BRDN_COST LABOR_HRS LABOR_RAW_COST LABOR_BRDN_COST EQUIPMENT_HRS EQUIPMENT_RAW_COST EQUIPMENT_BRDN_COST EXPENSE_QUANTITY EXP_RAW_COST EXP_BRDN_COST BILL_LABOR_HRS BILL_EQUIPMENT_HRS BILL_QUANTITY BILL_LABOR_RAW_COST BILL_LABOR_BRDN_COST BILL_RAW_COST BILL_BRDN_COST CAP_QUANTITY CAP_RAW_COST CAP_BRDN_COST RETIRE_RAW_COST RETIRE_BRDN_COST CMT_PO_QUANTITY PO_CMT_RAW_COST PO_COMMITTED_COST CMT_PR_QUANTITY PR_CMT_RAW_COST PR_COMMITTED_COST CMT_OTH_QUANTITY OTH_CMT_RAW_COST OTH_COMMITTED_COST CMT_SUP_INV_QUANTITY SUP_INV_CMT_RAW_COST SUP_INV_COMMITTED_COST CMT_TO_QUANTITY TO_CMT_RAW_COST TO_COMMITTED_COST TTL_CMT_QUANTITY TTL_CMT_RAW_COST TTL_COMMITTED_COST CUR_BUD_QUANTITY CUR_BUD_RAW_COST CUR_BUD_BRDN_COST CUR_BUD_REVENUE ORIG_BUD_QUANTITY ORIG_BUD_RAW_COST ORIG_BUD_BRDN_COST ORIG_BUD_REVENUE CUR_FCST_QUANTITY CUR_FCST_RAW_COST CUR_FCST_BRDN_COST CUR_FCST_REVENUE PRI_FCST_QUANTITY PRI_FCST_RAW_COST PRI_FCST_BRDN_COST PRI_FCST_REVENUE REVENUE LABOR_REVENUE INVOICE_AMT CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN

Query

SQL_Statement

SELECT PJS_SUMMARY_ID , PROJECT_ID , PROJECT_ELEMENT_ID , PROJECT_ELEMENT_ID_FLAG , CALENDAR_ID , CALENDAR_TYPE , PERIOD_NAME , TIME_ID , CURRENCY_CODE , CURRENCY_TYPE , START_DATE , END_DATE , SCENARIO , TXN_ACCUM_HEADER_ID , UOM_CODE , RBS_VERSION_ID , RBS_ELEMENT_ID , RBS_ELEMENT_ID_FLAG , RESOURCE_CLASS_ID , QUANTITY , RAW_COST , BRDN_COST , LABOR_HRS , LABOR_RAW_COST , LABOR_BRDN_COST , EQUIPMENT_HRS , EQUIPMENT_RAW_COST , EQUIPMENT_BRDN_COST , EXPENSE_QUANTITY , EXP_RAW_COST , EXP_BRDN_COST , BILL_LABOR_HRS , BILL_EQUIPMENT_HRS , BILL_QUANTITY , BILL_LABOR_RAW_COST , BILL_LABOR_BRDN_COST , BILL_RAW_COST , BILL_BRDN_COST , CAP_QUANTITY , CAP_RAW_COST , CAP_BRDN_COST , RETIRE_RAW_COST , RETIRE_BRDN_COST , CMT_PO_QUANTITY , PO_CMT_RAW_COST , PO_COMMITTED_COST , CMT_PR_QUANTITY , PR_CMT_RAW_COST , PR_COMMITTED_COST , CMT_OTH_QUANTITY , OTH_CMT_RAW_COST , OTH_COMMITTED_COST , CMT_SUP_INV_QUANTITY , SUP_INV_CMT_RAW_COST , SUP_INV_COMMITTED_COST , CMT_TO_QUANTITY , TO_CMT_RAW_COST , TO_COMMITTED_COST , TTL_CMT_QUANTITY , TTL_CMT_RAW_COST , TTL_COMMITTED_COST , CUR_BUD_QUANTITY , CUR_BUD_RAW_COST , CUR_BUD_BRDN_COST , CUR_BUD_REVENUE , ORIG_BUD_QUANTITY , ORIG_BUD_RAW_COST , ORIG_BUD_BRDN_COST , ORIG_BUD_REVENUE , CUR_FCST_QUANTITY , CUR_FCST_RAW_COST , CUR_FCST_BRDN_COST , CUR_FCST_REVENUE , PRI_FCST_QUANTITY , PRI_FCST_RAW_COST , PRI_FCST_BRDN_COST , PRI_FCST_REVENUE , REVENUE , LABOR_REVENUE , INVOICE_AMT , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN FROM PJS_FP_PROJ_F WHERE not exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') UNION ALL SELECT fin_o.PROJECT_ID PJS_SUMMARY_ID , fin_o.PROJECT_ID , fin_o.TASK_ID PROJECT_ELEMENT_ID , decode(retire.leaf_node_flag, 'N', 'Y', 'N') PROJECT_ELEMENT_ID_FLAG , per.CALENDAR_ID , fin_o.CALENDAR_TYPE , fin_o.PERIOD_NAME , per.CAL_PERIOD_ID TIME_ID , fin_o.CURRENCY_CODE , fin_o.CURRENCY_TYPE , per.CAL_PERIOD_START_DATE START_DATE , per.CAL_PERIOD_END_DATE END_DATE , fin_o.SCENARIO , fin_o.TXN_ACCUM_HEADER_ID , fin_o.UOM_CODE , 0 RBS_VERSION_ID , 0 RBS_ELEMENT_ID , 'A' RBS_ELEMENT_ID_FLAG , fin_o.RESOURCE_CLASS_ID , sum(QUANTITY) QUANTITY , sum(RAW_COST) RAW_COST , sum(BRDN_COST) BRDN_COST , sum(LABOR_HRS) LABOR_HRS , sum(LABOR_RAW_COST) LABOR_RAW_COST , sum(LABOR_BRDN_COST) LABOR_BRDN_COST , sum(EQUIPMENT_HRS) EQUIPMENT_HRS , sum(EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST , sum(EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST , sum(EXPENSE_QUANTITY) EXPENSE_QUANTITY , sum(EXP_RAW_COST) EXP_RAW_COST , sum(EXP_BRDN_COST) EXP_BRDN_COST , sum(BILL_LABOR_HRS) BILL_LABOR_HRS , sum(BILL_EQUIPMENT_HRS) BILL_EQUIPMENT_HRS , sum(BILL_QUANTITY) BILL_QUANTITY , sum(BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST , sum(BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST , sum(BILL_RAW_COST) BILL_RAW_COST , sum(BILL_BRDN_COST) BILL_BRDN_COST , sum(CAP_QUANTITY) CAP_QUANTITY , sum(CAP_RAW_COST) CAP_RAW_COST , sum(CAP_BRDN_COST) CAP_BRDN_COST , sum(RETIRE_RAW_COST) RETIRE_RAW_COST , sum(RETIRE_BRDN_COST) RETIRE_BRDN_COST , sum(CMT_PO_QUANTITY) CMT_PO_QUANTITY , sum(PO_CMT_RAW_COST) PO_CMT_RAW_COST , sum(PO_COMMITTED_COST) PO_COMMITTED_COST , sum(CMT_PR_QUANTITY) CMT_PR_QUANTITY , sum(PR_CMT_RAW_COST) PR_CMT_RAW_COST , sum(PR_COMMITTED_COST) PR_COMMITTED_COST , sum(CMT_OTH_QUANTITY) CMT_OTH_QUANTITY , sum(OTH_CMT_RAW_COST) OTH_CMT_RAW_COST , sum(OTH_COMMITTED_COST) OTH_COMMITTED_COST , sum(CMT_SUP_INV_QUANTITY) CMT_SUP_INV_QUANTITY , sum(SUP_INV_CMT_RAW_COST) SUP_INV_CMT_RAW_COST , sum(SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST , sum(CMT_TO_QUANTITY) CMT_TO_QUANTITY , sum(TO_CMT_RAW_COST) TO_CMT_RAW_COST , sum(TO_COMMITTED_COST) TO_COMMITTED_COST , sum(TTL_CMT_QUANTITY) TTL_CMT_QUANTITY , sum(TTL_CMT_RAW_COST) TTL_CMT_RAW_COST , sum(TTL_COMMITTED_COST) TTL_COMMITTED_COST , sum(CUR_BUD_QUANTITY) CUR_BUD_QUANTITY , sum(CUR_BUD_RAW_COST) CUR_BUD_RAW_COST , sum(CUR_BUD_BRDN_COST) CUR_BUD_BRDN_COST , sum(CUR_BUD_REVENUE) CUR_BUD_REVENUE , sum(ORIG_BUD_QUANTITY) ORIG_BUD_QUANTITY , sum(ORIG_BUD_RAW_COST) ORIG_BUD_RAW_COST , sum(ORIG_BUD_BRDN_COST) ORIG_BUD_BRDN_COST , sum(ORIG_BUD_REVENUE) ORIG_BUD_REVENUE , sum(CUR_FCST_QUANTITY) CUR_FCST_QUANTITY , sum(CUR_FCST_RAW_COST) CUR_FCST_RAW_COST , sum(CUR_FCST_BRDN_COST) CUR_FCST_BRDN_COST , sum(CUR_FCST_REVENUE) CUR_FCST_REVENUE , sum(PRI_FCST_QUANTITY) PRI_FCST_QUANTITY , sum(PRI_FCST_RAW_COST) PRI_FCST_RAW_COST , sum(PRI_FCST_BRDN_COST) PRI_FCST_BRDN_COST , sum(PRI_FCST_REVENUE) PRI_FCST_REVENUE , sum(REVENUE) REVENUE , sum(LABOR_REVENUE) LABOR_REVENUE , sum(INVOICE_AMT) INVOICE_AMT , max(fin_o.CREATED_BY) CREATED_BY , max(fin_o.CREATION_DATE) CREATION_DATE , max(fin_o.LAST_UPDATED_BY) LAST_UPDATED_BY , max(fin_o.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(fin_o.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN FROM ( select base_fin.PROJECT_ID , base_fin.TASK_ID , base_fin.CALENDAR_TYPE , base_fin.PERIOD_NAME , base_fin.CURRENCY_CODE , base_fin.CURRENCY_TYPE , 'CCBF' SCENARIO , base_fin.TXN_ACCUM_HEADER_ID , base_fin.UOM_CODE , base_fin.RESOURCE_CLASS_ID , sum(base_fin.QUANTITY) QUANTITY , sum(base_fin.RAW_COST) RAW_COST , sum(base_fin.BRDN_COST) BRDN_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.QUANTITY, to_number(null))) LABOR_HRS , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.RAW_COST, to_number(null))) LABOR_RAW_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.BRDN_COST, to_number(null))) LABOR_BRDN_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 2, decode(base_fin.UOM_CODE, 'HOURS', base_fin.QUANTITY, to_number(null)), to_number(null))) EQUIPMENT_HRS , sum(decode(base_fin.RESOURCE_CLASS_ID, 2, base_fin.RAW_COST, to_number(null))) EQUIPMENT_RAW_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 2, base_fin.BRDN_COST, to_number(null))) EQUIPMENT_BRDN_COST , sum(base_fin.EXPENSE_QUANTITY) EXPENSE_QUANTITY , sum(base_fin.EXPENSE_RAW_COST) EXP_RAW_COST , sum(base_fin.EXPENSE_BRDN_COST) EXP_BRDN_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.BILL_QUANTITY, to_number(null))) BILL_LABOR_HRS , sum(decode(base_fin.RESOURCE_CLASS_ID, 2, decode(base_fin.UOM_CODE, 'HOURS', base_fin.BILL_QUANTITY, to_number(null)), to_number(null))) BILL_EQUIPMENT_HRS , sum(base_fin.BILL_QUANTITY) BILL_QUANTITY , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.BILL_RAW_COST, to_number(null))) BILL_LABOR_RAW_COST , sum(decode(base_fin.RESOURCE_CLASS_ID, 1, base_fin.BILL_BRDN_COST, to_number(null))) BILL_LABOR_BRDN_COST , sum(base_fin.BILL_RAW_COST) BILL_RAW_COST , sum(base_fin.BILL_BRDN_COST) BILL_BRDN_COST , sum(base_fin.CAP_QUANTITY) CAP_QUANTITY , sum(base_fin.CAP_RAW_COST) CAP_RAW_COST , sum(base_fin.CAP_BRDN_COST) CAP_BRDN_COST , sum(base_fin.RAW_COST) RETIRE_RAW_COST , sum(base_fin.BRDN_COST) RETIRE_BRDN_COST , to_number(null) CMT_PO_QUANTITY , to_number(null) PO_CMT_RAW_COST , to_number(null) PO_COMMITTED_COST , to_number(null) CMT_PR_QUANTITY , to_number(null) PR_CMT_RAW_COST , to_number(null) PR_COMMITTED_COST , to_number(null) CMT_OTH_QUANTITY , to_number(null) OTH_CMT_RAW_COST , to_number(null) OTH_COMMITTED_COST , to_number(null) CMT_SUP_INV_QUANTITY , to_number(null) SUP_INV_CMT_RAW_COST , to_number(null) SUP_INV_COMMITTED_COST , to_number(null) CMT_TO_QUANTITY , to_number(null) TO_CMT_RAW_COST , to_number(null) TO_COMMITTED_COST , to_number(null) TTL_CMT_QUANTITY , to_number(null) TTL_CMT_RAW_COST , to_number(null) TTL_COMMITTED_COST , to_number(null) CUR_BUD_QUANTITY , to_number(null) CUR_BUD_RAW_COST , to_number(null) CUR_BUD_BRDN_COST , to_number(null) CUR_BUD_REVENUE , to_number(null) ORIG_BUD_QUANTITY , to_number(null) ORIG_BUD_RAW_COST , to_number(null) ORIG_BUD_BRDN_COST , to_number(null) ORIG_BUD_REVENUE , to_number(null) CUR_FCST_QUANTITY , to_number(null) CUR_FCST_RAW_COST , to_number(null) CUR_FCST_BRDN_COST , to_number(null) CUR_FCST_REVENUE , to_number(null) PRI_FCST_QUANTITY , to_number(null) PRI_FCST_RAW_COST , to_number(null) PRI_FCST_BRDN_COST , to_number(null) PRI_FCST_REVENUE , to_number(null) REVENUE , to_number(null) LABOR_REVENUE , to_number(null) INVOICE_AMT , max(base_fin.CREATED_BY) CREATED_BY , max(base_fin.CREATION_DATE) CREATION_DATE , max(base_fin.LAST_UPDATED_BY) LAST_UPDATED_BY , max(base_fin.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(base_fin.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN from PJS_FP_BASE_FIN base_fin where base_fin.CURRENCY_TYPE IN ('P', 'F') and base_fin.CALENDAR_TYPE IN ('P', 'G') and exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') group by base_fin.PROJECT_ID , base_fin.TASK_ID , base_fin.CALENDAR_TYPE , base_fin.PERIOD_NAME , base_fin.CURRENCY_CODE , base_fin.CURRENCY_TYPE , base_fin.TXN_ACCUM_HEADER_ID , base_fin.UOM_CODE , base_fin.RESOURCE_CLASS_ID UNION ALL select base_cmt.PROJECT_ID , base_cmt.TASK_ID , base_cmt.CALENDAR_TYPE , base_cmt.PERIOD_NAME , base_cmt.CURRENCY_CODE , base_cmt.CURRENCY_TYPE , 'CCBF' SCENARIO , base_cmt.TXN_ACCUM_HEADER_ID , base_cmt.UOM_CODE , base_cmt.RESOURCE_CLASS_ID , to_number(null) QUANTITY , to_number(null) RAW_COST , to_number(null) BRDN_COST , to_number(null) LABOR_HRS , to_number(null) LABOR_RAW_COST , to_number(null) LABOR_BRDN_COST , to_number(null) EQUIPMENT_HRS , to_number(null) EQUIPMENT_RAW_COST , to_number(null) EQUIPMENT_BRDN_COST , to_number(null) EXPENSE_QUANTITY , to_number(null) EXP_RAW_COST , to_number(null) EXP_BRDN_COST , to_number(null) BILL_LABOR_HRS , to_number(null) BILL_EQUIPMENT_HRS , to_number(null) BILL_QUANTITY , to_number(null) BILL_LABOR_RAW_COST , to_number(null) BILL_LABOR_BRDN_COST , to_number(null) BILL_RAW_COST , to_number(null) BILL_BRDN_COST , to_number(null) CAP_QUANTITY , to_number(null) CAP_RAW_COST , to_number(null) CAP_BRDN_COST , to_number(null) RETIRE_RAW_COST , to_number(null) RETIRE_BRDN_COST , sum(base_cmt.CMT_PO_QUANTITY) CMT_PO_QUANTITY , sum(base_cmt.PO_CMT_RAW_COST) PO_CMT_RAW_COST , sum(base_cmt.PO_CMT_BRDN_COST) PO_COMMITTED_COST , sum(base_cmt.CMT_PR_QUANTITY) CMT_PR_QUANTITY , sum(base_cmt.PR_CMT_RAW_COST) PR_CMT_RAW_COST , sum(base_cmt.PR_CMT_BRDN_COST) PR_COMMITTED_COST , sum(base_cmt.CMT_OTH_QUANTITY) CMT_OTH_QUANTITY , sum(base_cmt.OTH_CMT_RAW_COST) OTH_CMT_RAW_COST , sum(base_cmt.OTH_CMT_BRDN_COST) OTH_COMMITTED_COST , sum(base_cmt.CMT_SUP_INV_QUANTITY) CMT_SUP_INV_QUANTITY , sum(base_cmt.SUP_INV_CMT_RAW_COST) SUP_INV_CMT_RAW_COST , sum(base_cmt.SUP_INV_CMT_BRDN_COST) SUP_INV_COMMITTED_COST , sum(base_cmt.TO_CMT_QUANTITY) CMT_TO_QUANTITY , sum(base_cmt.TO_CMT_RAW_COST) TO_CMT_RAW_COST , sum(base_cmt.TO_CMT_BRDN_COST) TO_COMMITTED_COST , sum(nvl(base_cmt.CMT_PO_QUANTITY, 0) + nvl(base_cmt.CMT_PR_QUANTITY, 0) + nvl(base_cmt.CMT_OTH_QUANTITY, 0) + nvl(base_cmt.CMT_SUP_INV_QUANTITY, 0) ) TTL_CMT_QUANTITY , sum(nvl(base_cmt.PO_CMT_RAW_COST, 0) + nvl(base_cmt.PR_CMT_RAW_COST, 0) + nvl(base_cmt.OTH_CMT_RAW_COST, 0) + nvl(base_cmt.SUP_INV_CMT_RAW_COST, 0) ) TTL_CMT_RAW_COST , sum(nvl(base_cmt.PO_CMT_BRDN_COST, 0) + nvl(base_cmt.PR_CMT_BRDN_COST, 0) + nvl(base_cmt.OTH_CMT_BRDN_COST, 0) + nvl(base_cmt.SUP_INV_CMT_BRDN_COST, 0) ) TTL_COMMITTED_COST , to_number(null) CUR_BUD_QUANTITY , to_number(null) CUR_BUD_RAW_COST , to_number(null) CUR_BUD_BRDN_COST , to_number(null) CUR_BUD_REVENUE , to_number(null) ORIG_BUD_QUANTITY , to_number(null) ORIG_BUD_RAW_COST , to_number(null) ORIG_BUD_BRDN_COST , to_number(null) ORIG_BUD_REVENUE , to_number(null) CUR_FCST_QUANTITY , to_number(null) CUR_FCST_RAW_COST , to_number(null) CUR_FCST_BRDN_COST , to_number(null) CUR_FCST_REVENUE , to_number(null) PRI_FCST_QUANTITY , to_number(null) PRI_FCST_RAW_COST , to_number(null) PRI_FCST_BRDN_COST , to_number(null) PRI_FCST_REVENUE , to_number(null) REVENUE , to_number(null) LABOR_REVENUE , to_number(null) INVOICE_AMT , max(base_cmt.CREATED_BY) CREATED_BY , max(base_cmt.CREATION_DATE) CREATION_DATE , max(base_cmt.LAST_UPDATED_BY) LAST_UPDATED_BY , max(base_cmt.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(base_cmt.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN from PJS_FP_BASE_CMT base_cmt where base_cmt.CURRENCY_TYPE IN ('P', 'F') and base_cmt.CALENDAR_TYPE IN ('P', 'G') and exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') group by base_cmt.PROJECT_ID , base_cmt.TASK_ID , base_cmt.CALENDAR_TYPE , base_cmt.PERIOD_NAME , base_cmt.CURRENCY_CODE , base_cmt.CURRENCY_TYPE , base_cmt.TXN_ACCUM_HEADER_ID , base_cmt.UOM_CODE , base_cmt.RESOURCE_CLASS_ID UNION ALL select base_con.LINKED_PROJECT_ID PROJECT_ID , decode(nvl(base_con.TRANSACTION_TASK_ID, -1), -1, emt.proj_element_id, base_con.TRANSACTION_TASK_ID) task_id , base_con.CALENDAR_TYPE , base_con.PERIOD_NAME , base_con.CURRENCY_CODE , decode(bitand(base_con.CURRENCY_TYPE_ID, invert.INV), 2, 'F', 8, 'P') CURRENCY_TYPE , 'RI' SCENARIO , base_con.TXN_ACCUM_HEADER_ID , to_char(null) UOM_CODE , txn.RESOURCE_CLASS_ID RESOURCE_CLASS_ID , to_number(null) QUANTITY , to_number(null) RAW_COST , to_number(null) BRDN_COST , to_number(null) LABOR_HRS , to_number(null) LABOR_RAW_COST , to_number(null) LABOR_BRDN_COST , to_number(null) EQUIPMENT_HRS , to_number(null) EQUIPMENT_RAW_COST , to_number(null) EQUIPMENT_BRDN_COST , to_number(null) EXPENSE_QUANTITY , to_number(null) EXP_RAW_COST , to_number(null) EXP_BRDN_COST , to_number(null) BILL_LABOR_HRS , to_number(null) BILL_EQUIPMENT_HRS , to_number(null) BILL_QUANTITY , to_number(null) BILL_LABOR_RAW_COST , to_number(null) BILL_LABOR_BRDN_COST , to_number(null) BILL_RAW_COST , to_number(null) BILL_BRDN_COST , to_number(null) CAP_QUANTITY , to_number(null) CAP_RAW_COST , to_number(null) CAP_BRDN_COST , to_number(null) RETIRE_RAW_COST , to_number(null) RETIRE_BRDN_COST , to_number(null) CMT_PO_QUANTITY , to_number(null) PO_CMT_RAW_COST , to_number(null) PO_COMMITTED_COST , to_number(null) CMT_PR_QUANTITY , to_number(null) PR_CMT_RAW_COST , to_number(null) PR_COMMITTED_COST , to_number(null) CMT_OTH_QUANTITY , to_number(null) OTH_CMT_RAW_COST , to_number(null) OTH_COMMITTED_COST , to_number(null) CMT_SUP_INV_QUANTITY , to_number(null) SUP_INV_CMT_RAW_COST , to_number(null) SUP_INV_COMMITTED_COST , to_number(null) CMT_TO_QUANTITY , to_number(null) TO_CMT_RAW_COST , to_number(null) TO_COMMITTED_COST , to_number(null) TTL_CMT_QUANTITY , to_number(null) TTL_CMT_RAW_COST , to_number(null) TTL_COMMITTED_COST , to_number(null) CUR_BUD_QUANTITY , to_number(null) CUR_BUD_RAW_COST , to_number(null) CUR_BUD_BRDN_COST , to_number(null) CUR_BUD_REVENUE , to_number(null) ORIG_BUD_QUANTITY , to_number(null) ORIG_BUD_RAW_COST , to_number(null) ORIG_BUD_BRDN_COST , to_number(null) ORIG_BUD_REVENUE , to_number(null) CUR_FCST_QUANTITY , to_number(null) CUR_FCST_RAW_COST , to_number(null) CUR_FCST_BRDN_COST , to_number(null) CUR_FCST_REVENUE , to_number(null) PRI_FCST_QUANTITY , to_number(null) PRI_FCST_RAW_COST , to_number(null) PRI_FCST_BRDN_COST , to_number(null) PRI_FCST_REVENUE , sum(decode(bitand(base_con.CURRENCY_TYPE_ID, invert.INV), 2, base_con.PFC_REVENUE, 8, base_con.PC_REVENUE)) REVENUE , sum(decode(txn.RESOURCE_CLASS_ID, 1, decode(bitand(base_con.CURRENCY_TYPE_ID, invert.INV), 2, base_con.PFC_REVENUE, 8, base_con.PC_REVENUE), to_number(null))) LABOR_REVENUE , sum(decode(bitand(base_con.CURRENCY_TYPE_ID, invert.INV), 2, base_con.PFC_BILLED, 8, base_con.PC_BILLED)) INVOICE_AMT , max(base_con.CREATED_BY) CREATED_BY , max(base_con.CREATION_DATE) CREATION_DATE , max(base_con.LAST_UPDATED_BY) LAST_UPDATED_BY , max(base_con.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(base_con.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN from PJS_CN_BASE_CON base_con , (select decode(measure_code, 'BRDN_COST#ACT#B', 2, 8) inv from pjs_mt_measures_b where measure_code in ('BRDN_COST#ACT#B', 'BRDN_COST#ACT#ITD')) invert , PJF_TXN_BASE_HEADER txn , PJF_PROJ_ELEM_VER_STRUCT emt where base_con.TXN_ACCUM_HEADER_ID = txn.TXN_ACCUM_HEADER_ID and bitand(base_con.CURRENCY_TYPE_ID, invert.INV) = invert.INV and base_con.CALENDAR_TYPE IN ('P', 'G') and base_con.LINKED_PROJECT_ID IS NOT NULL and base_con.LINKED_PROJECT_ID <> -1 and emt.project_id = base_con.LINKED_PROJECT_ID and exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') group by base_con.LINKED_PROJECT_ID , base_con.TRANSACTION_TASK_ID , base_con.CALENDAR_TYPE , base_con.PERIOD_NAME , base_con.CURRENCY_CODE , base_con.CURRENCY_TYPE_ID , invert.INV , base_con.TXN_ACCUM_HEADER_ID , txn.RESOURCE_CLASS_ID , emt.proj_element_id UNION ALL select base_plan.PROJECT_ID , decode(nvl(base_plan.TASK_ID, -1), -1, emt.proj_element_id, base_plan.TASK_ID) task_id , base_plan.CALENDAR_TYPE , base_plan.PERIOD_NAME , base_plan.CURRENCY_CODE , base_plan.CURRENCY_TYPE , 'CCBF' SCENARIO , base_plan.TXN_ACCUM_HEADER_ID , base_plan.UOM_CODE , base_plan.RESOURCE_CLASS_ID , to_number(null) QUANTITY , to_number(null) RAW_COST , to_number(null) BRDN_COST , to_number(null) LABOR_HRS , to_number(null) LABOR_RAW_COST , to_number(null) LABOR_BRDN_COST , to_number(null) EQUIPMENT_HRS , to_number(null) EQUIPMENT_RAW_COST , to_number(null) EQUIPMENT_BRDN_COST , to_number(null) EXPENSE_QUANTITY , to_number(null) EXP_RAW_COST , to_number(null) EXP_BRDN_COST , to_number(null) BILL_LABOR_HRS , to_number(null) BILL_EQUIPMENT_HRS , to_number(null) BILL_QUANTITY , to_number(null) BILL_LABOR_RAW_COST , to_number(null) BILL_LABOR_BRDN_COST , to_number(null) BILL_RAW_COST , to_number(null) BILL_BRDN_COST , to_number(null) CAP_QUANTITY , to_number(null) CAP_RAW_COST , to_number(null) CAP_BRDN_COST , to_number(null) RETIRE_RAW_COST , to_number(null) RETIRE_BRDN_COST , to_number(null) CMT_PO_QUANTITY , to_number(null) PO_CMT_RAW_COST , to_number(null) PO_COMMITTED_COST , to_number(null) CMT_PR_QUANTITY , to_number(null) PR_CMT_RAW_COST , to_number(null) PR_COMMITTED_COST , to_number(null) CMT_OTH_QUANTITY , to_number(null) OTH_CMT_RAW_COST , to_number(null) OTH_COMMITTED_COST , to_number(null) CMT_SUP_INV_QUANTITY , to_number(null) SUP_INV_CMT_RAW_COST , to_number(null) SUP_INV_COMMITTED_COST , to_number(null) CMT_TO_QUANTITY , to_number(null) TO_CMT_RAW_COST , to_number(null) TO_COMMITTED_COST , to_number(null) TTL_CMT_QUANTITY , to_number(null) TTL_CMT_RAW_COST , to_number(null) TTL_COMMITTED_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then base_plan.QUANTITY else 0 end) CUR_BUD_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then base_plan.RAW_COST else 0 end) CUR_BUD_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then base_plan.BRDN_COST else 0 end) CUR_BUD_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then base_plan.REVENUE else 0 end) CUR_BUD_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then base_plan.QUANTITY else 0 end) ORIG_BUD_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then base_plan.RAW_COST else 0 end) ORIG_BUD_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then base_plan.BRDN_COST else 0 end) ORIG_BUD_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then base_plan.REVENUE else 0 end) ORIG_BUD_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then base_plan.QUANTITY else 0 end) CUR_FCST_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then base_plan.RAW_COST else 0 end) CUR_FCST_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then base_plan.BRDN_COST else 0 end) CUR_FCST_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then base_plan.REVENUE else 0 end) CUR_FCST_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then base_plan.QUANTITY else 0 end) PRI_FCST_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then base_plan.RAW_COST else 0 end) PRI_FCST_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then base_plan.BRDN_COST else 0 end) PRI_FCST_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then base_plan.REVENUE else 0 end) PRI_FCST_REVENUE , to_number(null) REVENUE , to_number(null) LABOR_REVENUE , to_number(null) INVOICE_AMT , max(base_plan.CREATED_BY) CREATED_BY , max(base_plan.CREATION_DATE) CREATION_DATE , max(base_plan.LAST_UPDATED_BY) LAST_UPDATED_BY , max(base_plan.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(base_plan.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN from PJS_FP_BASE_PLAN base_plan , PJS_PLAN_VERSION_D pjs_plan , PJF_PROJ_ELEM_VER_STRUCT emt where pjs_plan.project_id = base_plan.project_id and pjs_plan.plan_version_id = base_plan.plan_version_id and base_plan.CURRENCY_TYPE IN ('P', 'F') and base_plan.CALENDAR_TYPE IN ('P', 'G') and emt.project_id = base_plan.project_id and emt.project_id = pjs_plan.project_id and exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') group by base_plan.PROJECT_ID , base_plan.TASK_ID , base_plan.CALENDAR_TYPE , base_plan.PERIOD_NAME , base_plan.CURRENCY_CODE , base_plan.CURRENCY_TYPE , base_plan.TXN_ACCUM_HEADER_ID , base_plan.UOM_CODE , base_plan.RESOURCE_CLASS_ID , emt.proj_element_id UNION ALL select opt_plan.PROJECT_ID , decode(nvl(opt_plan.TASK_ID, -1), -1, emt.proj_element_id, opt_plan.TASK_ID) task_id , opt_plan.CALENDAR_TYPE , opt_plan.PERIOD_NAME , opt_plan.CURRENCY_CODE , opt_plan.CURRENCY_TYPE , 'CCBF' SCENARIO , opt_plan.TXN_ACCUM_HEADER_ID , opt_plan.UOM_CODE , opt_plan.RESOURCE_CLASS_ID , to_number(null) QUANTITY , to_number(null) RAW_COST , to_number(null) BRDN_COST , to_number(null) LABOR_HRS , to_number(null) LABOR_RAW_COST , to_number(null) LABOR_BRDN_COST , to_number(null) EQUIPMENT_HRS , to_number(null) EQUIPMENT_RAW_COST , to_number(null) EQUIPMENT_BRDN_COST , to_number(null) EXPENSE_QUANTITY , to_number(null) EXP_RAW_COST , to_number(null) EXP_BRDN_COST , to_number(null) BILL_LABOR_HRS , to_number(null) BILL_EQUIPMENT_HRS , to_number(null) BILL_QUANTITY , to_number(null) BILL_LABOR_RAW_COST , to_number(null) BILL_LABOR_BRDN_COST , to_number(null) BILL_RAW_COST , to_number(null) BILL_BRDN_COST , to_number(null) CAP_QUANTITY , to_number(null) CAP_RAW_COST , to_number(null) CAP_BRDN_COST , to_number(null) RETIRE_RAW_COST , to_number(null) RETIRE_BRDN_COST , to_number(null) CMT_PO_QUANTITY , to_number(null) PO_CMT_RAW_COST , to_number(null) PO_COMMITTED_COST , to_number(null) CMT_PR_QUANTITY , to_number(null) PR_CMT_RAW_COST , to_number(null) PR_COMMITTED_COST , to_number(null) CMT_OTH_QUANTITY , to_number(null) OTH_CMT_RAW_COST , to_number(null) OTH_COMMITTED_COST , to_number(null) CMT_SUP_INV_QUANTITY , to_number(null) SUP_INV_CMT_RAW_COST , to_number(null) SUP_INV_COMMITTED_COST , to_number(null) CMT_TO_QUANTITY , to_number(null) TO_CMT_RAW_COST , to_number(null) TO_COMMITTED_COST , to_number(null) TTL_CMT_QUANTITY , to_number(null) TTL_CMT_RAW_COST , to_number(null) TTL_COMMITTED_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then opt_plan.QUANTITY else 0 end) CUR_BUD_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then opt_plan.RAW_COST else 0 end) CUR_BUD_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then opt_plan.BRDN_COST else 0 end) CUR_BUD_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.CURRENT_BUDGET_FLAG = 'Y' then opt_plan.REVENUE else 0 end) CUR_BUD_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then opt_plan.QUANTITY else 0 end) ORIG_BUD_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then opt_plan.RAW_COST else 0 end) ORIG_BUD_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then opt_plan.BRDN_COST else 0 end) ORIG_BUD_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'B' and pjs_plan.ORIGINAL_BUDGET_FLAG = 'Y' then opt_plan.REVENUE else 0 end) ORIG_BUD_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then opt_plan.QUANTITY else 0 end) CUR_FCST_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then opt_plan.RAW_COST else 0 end) CUR_FCST_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then opt_plan.BRDN_COST else 0 end) CUR_FCST_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.CURRENT_FORECAST_FLAG = 'Y' then opt_plan.REVENUE else 0 end) CUR_FCST_REVENUE , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then opt_plan.QUANTITY else 0 end) PRI_FCST_QUANTITY , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then opt_plan.RAW_COST else 0 end) PRI_FCST_RAW_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then opt_plan.BRDN_COST else 0 end) PRI_FCST_BRDN_COST , sum(case when pjs_plan.PLAN_CLASS_CODE = 'F' and pjs_plan.PRIOR_FORECAST_FLAG = 'Y' then opt_plan.REVENUE else 0 end) PRI_FCST_REVENUE , to_number(null) REVENUE , to_number(null) LABOR_REVENUE , to_number(null) INVOICE_AMT , max(opt_plan.CREATED_BY) CREATED_BY , max(opt_plan.CREATION_DATE) CREATION_DATE , max(opt_plan.LAST_UPDATED_BY) LAST_UPDATED_BY , max(opt_plan.LAST_UPDATE_DATE) LAST_UPDATE_DATE , max(opt_plan.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN from PJS_FP_OPT_PLAN opt_plan , PJS_PLAN_VERSION_D pjs_plan , PJF_PROJ_ELEM_VER_STRUCT emt where pjs_plan.project_id = opt_plan.project_id and pjs_plan.plan_version_id = opt_plan.plan_version_id and opt_plan.CURRENCY_TYPE IN ('P', 'F') and opt_plan.CALENDAR_TYPE IN ('P', 'G') and emt.project_id = opt_plan.project_id and emt.project_id = pjs_plan.project_id and exists (select 1 from pjs_system_parameters where name='PARAM53_DISABLE_PJS_FP_PROJ_F' and value = 'YES') group by opt_plan.PROJECT_ID , opt_plan.TASK_ID , opt_plan.CALENDAR_TYPE , opt_plan.PERIOD_NAME , opt_plan.CURRENCY_CODE , opt_plan.CURRENCY_TYPE , opt_plan.TXN_ACCUM_HEADER_ID , opt_plan.UOM_CODE , opt_plan.RESOURCE_CLASS_ID , emt.proj_element_id ) fin_o , PJF_PROJECTS_ALL_B proj , PJS_BU_INFO buinfo , PJS_TIME_D per , PJF_PROJ_ELEMENTS_B retire WHERE fin_o.project_id = proj.project_id AND proj.org_id = buinfo.business_unit_id AND decode(fin_o.CALENDAR_TYPE, 'P', buinfo.PA_CALENDAR_ID, 'G', buinfo.GL_CALENDAR_ID) = per.CALENDAR_ID AND fin_o.PERIOD_NAME = per.CAL_PERIOD_NAME AND retire.ELEMENT_TYPE IN ('FINANCIAL', 'FIN_EXEC') AND retire.PROJECT_ID = fin_o.PROJECT_ID AND retire.PROJECT_ID = proj.PROJECT_ID AND retire.PROJ_ELEMENT_ID = fin_o.TASK_ID GROUP BY fin_o.PROJECT_ID , fin_o.TASK_ID , retire.leaf_node_flag , per.CALENDAR_ID , fin_o.CALENDAR_TYPE , fin_o.PERIOD_NAME , per.CAL_PERIOD_ID , fin_o.CURRENCY_CODE , fin_o.CURRENCY_TYPE , per.CAL_PERIOD_START_DATE , per.CAL_PERIOD_END_DATE , fin_o.SCENARIO , fin_o.TXN_ACCUM_HEADER_ID , fin_o.UOM_CODE , fin_o.RESOURCE_CLASS_ID