VIEW: APPS.PA_FIN_PLAN_TYPES_B_V

Source

View Text - Preformatted

SELECT pfo.project_id, pt.name, pt.fin_plan_type_id, pt.fin_plan_type_code, pt.plan_class_code, pa_fin_plan_type_global.plantype_to_planclass (pfo.project_id, pt.fin_plan_type_id) as security_plan_class_code, pfo.fin_plan_preference_code, SUM(DECODE(pfo.report_labor_hrs_from_code, 'COST', DECODE(bv.version_type, 'COST', nvl(bv.labor_quantity,0), 'ALL', nvl(bv.labor_quantity,0), 'ORG_FORECAST', nvl(bv.labor_quantity,0), 0), 'REVENUE', DECODE(bv.version_type, 'REVENUE', nvl(bv.labor_quantity,0), 'ALL', nvl(bv.labor_quantity,0), 'ORG_FORECAST', nvl(bv.labor_quantity,0), 0), nvl(bv.labor_quantity,0))) as labor_quantity, DECODE(pfo.margin_derived_from_code, 'R', SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.raw_cost,0), NVL(bv.total_project_raw_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)), 'B', SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)), SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0))) as cost, SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0)) as revenue, DECODE(pfo.fin_plan_preference_code, 'COST_AND_REV_SAME', DECODE(pfo.margin_derived_from_code, 'R', (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.raw_cost,0), NVL(bv.total_project_raw_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0))), 'B', (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0))), (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))), 'COST_AND_REV_SEP', DECODE(pfo.margin_derived_from_code, 'R', (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.raw_cost,0), NVL(bv.total_project_raw_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0))), 'B', (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0))), (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))), 0) as margin, DECODE(pfo.fin_plan_preference_code, 'COST_AND_REV_SAME', DECODE((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 0, 0, DECODE(pfo.margin_derived_from_code, 'R', 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.raw_cost,0), NVL(bv.total_project_raw_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 'B', 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))))), 'COST_AND_REV_SEP', DECODE((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 0, 0, DECODE(pfo.margin_derived_from_code, 'R', 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.raw_cost,0), NVL(bv.total_project_raw_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 'B', 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))), 100*((SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))) - (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.burdened_cost,0), NVL(bv.total_project_burdened_cost,0))) + SUM(NVL(bv.total_lent_resource_cost,0)) + SUM(NVL(bv.total_unassigned_time_cost,0)) + SUM(NVL(bv.total_tp_cost_in,0)) + SUM(NVL(bv.total_cost_adj,0)) - SUM(NVL(bv.total_tp_cost_out,0)))) / (SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.revenue,0), NVL(bv.total_project_revenue,0))) + SUM(NVL(bv.total_tp_revenue_in,0)) + SUM(NVL(bv.total_borrowed_revenue, 0)) + SUM(NVL(bv.total_revenue_adj,0)) - SUM(NVL(bv.total_tp_revenue_out,0))))), 0) as margin_percent, SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.total_head_count,0) + NVL(bv.total_head_count_adj,0), 0)) as head_count, SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.total_utilization_hours,0), 0)) as total_utilization_hours, SUM(DECODE(bv.version_type, 'ORG_FORECAST', NVL(bv.total_utilization_percent,0) + NVL(bv.total_utilization_adj,0), 0)) as total_utilization_percent, pt.generated_flag, pfo.proj_fp_options_id as proj_fin_planning_options_id, DECODE(SUM(DECODE(bv.fin_plan_type_id, NULL, -1, 0)), 0, 'Y', 'N') as versions_flag, SUM(DECODE(pfo.report_labor_hrs_from_code, 'COST', DECODE(bv.version_type, 'COST', nvl(bv.equipment_quantity,0), 'ALL', nvl(bv.equipment_quantity,0), 'ORG_FORECAST', nvl(bv.equipment_quantity,0), 0), 'REVENUE', DECODE(bv.version_type, 'REVENUE', nvl(bv.equipment_quantity,0), 'ALL', nvl(bv.equipment_quantity,0), 'ORG_FORECAST', nvl(bv.equipment_quantity,0), 0), nvl(bv.equipment_quantity,0))) as equipment_quantity, SUM(DECODE(pfo.report_labor_hrs_from_code, 'COST', DECODE(bv.version_type, 'COST',nvl(bv.labor_quantity,0) , 'ALL', nvl(bv.labor_quantity,0) , 'ORG_FORECAST', nvl(bv.labor_quantity,0) , 0), 'REVENUE', DECODE(bv.version_type, 'REVENUE', nvl(bv.labor_quantity,0) , 'ALL', nvl(bv.labor_quantity,0) , 'ORG_FORECAST', nvl(bv.labor_quantity,0) , 0), nvl(bv.labor_quantity,0) )) as people_quantity, pt.edit_after_baseline_flag as allow_edit_after_baseline_flag from pa_fin_plan_types_vl pt, pa_budget_versions bv, pa_proj_fp_options pfo where pfo.project_id = pa_fin_plan_type_global.Get_Project_Id() and pfo.project_id=bv.project_id (+) and pfo.fin_plan_type_id=bv.fin_plan_type_id (+) and pfo.fin_plan_type_id=pt.fin_plan_type_id and pt.plan_class_code in ('BUDGET', 'FORECAST') and pfo.fin_plan_option_level_code='PLAN_TYPE' and bv.ci_id is null and bv.current_flag (+)='Y' and nvl(pt.use_for_workplan_flag,'N') = 'N' group by pfo.project_id, pt.plan_class_code, pt.name, pt.fin_plan_type_id, pt.fin_plan_type_code, pfo.fin_plan_preference_code, pt.generated_flag, pfo.proj_fp_options_id, pfo.margin_derived_from_code, pt.edit_after_baseline_flag order by pt.name

View Text - HTML Formatted

SELECT PFO.PROJECT_ID
, PT.NAME
, PT.FIN_PLAN_TYPE_ID
, PT.FIN_PLAN_TYPE_CODE
, PT.PLAN_CLASS_CODE
, PA_FIN_PLAN_TYPE_GLOBAL.PLANTYPE_TO_PLANCLASS (PFO.PROJECT_ID
, PT.FIN_PLAN_TYPE_ID) AS SECURITY_PLAN_CLASS_CODE
, PFO.FIN_PLAN_PREFERENCE_CODE
, SUM(DECODE(PFO.REPORT_LABOR_HRS_FROM_CODE
, 'COST'
, DECODE(BV.VERSION_TYPE
, 'COST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ALL'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 0)
, 'REVENUE'
, DECODE(BV.VERSION_TYPE
, 'REVENUE'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ALL'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 0)
, NVL(BV.LABOR_QUANTITY
, 0))) AS LABOR_QUANTITY
, DECODE(PFO.MARGIN_DERIVED_FROM_CODE
, 'R'
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.RAW_COST
, 0)
, NVL(BV.TOTAL_PROJECT_RAW_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0))
, 'B'
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0))
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0))) AS COST
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)) AS REVENUE
, DECODE(PFO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SAME'
, DECODE(PFO.MARGIN_DERIVED_FROM_CODE
, 'R'
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.RAW_COST
, 0)
, NVL(BV.TOTAL_PROJECT_RAW_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))
, 'B'
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0))))
, 'COST_AND_REV_SEP'
, DECODE(PFO.MARGIN_DERIVED_FROM_CODE
, 'R'
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.RAW_COST
, 0)
, NVL(BV.TOTAL_PROJECT_RAW_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))
, 'B'
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))
, (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0))))
, 0) AS MARGIN
, DECODE(PFO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SAME'
, DECODE((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 0
, 0
, DECODE(PFO.MARGIN_DERIVED_FROM_CODE
, 'R'
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.RAW_COST
, 0)
, NVL(BV.TOTAL_PROJECT_RAW_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 'B'
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))))
, 'COST_AND_REV_SEP'
, DECODE((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 0
, 0
, DECODE(PFO.MARGIN_DERIVED_FROM_CODE
, 'R'
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.RAW_COST
, 0)
, NVL(BV.TOTAL_PROJECT_RAW_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 'B'
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))
, 100*((SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0))) - (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.BURDENED_COST
, 0)
, NVL(BV.TOTAL_PROJECT_BURDENED_COST
, 0))) + SUM(NVL(BV.TOTAL_LENT_RESOURCE_COST
, 0)) + SUM(NVL(BV.TOTAL_UNASSIGNED_TIME_COST
, 0)) + SUM(NVL(BV.TOTAL_TP_COST_IN
, 0)) + SUM(NVL(BV.TOTAL_COST_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_COST_OUT
, 0)))) / (SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.REVENUE
, 0)
, NVL(BV.TOTAL_PROJECT_REVENUE
, 0))) + SUM(NVL(BV.TOTAL_TP_REVENUE_IN
, 0)) + SUM(NVL(BV.TOTAL_BORROWED_REVENUE
, 0)) + SUM(NVL(BV.TOTAL_REVENUE_ADJ
, 0)) - SUM(NVL(BV.TOTAL_TP_REVENUE_OUT
, 0)))))
, 0) AS MARGIN_PERCENT
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.TOTAL_HEAD_COUNT
, 0) + NVL(BV.TOTAL_HEAD_COUNT_ADJ
, 0)
, 0)) AS HEAD_COUNT
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.TOTAL_UTILIZATION_HOURS
, 0)
, 0)) AS TOTAL_UTILIZATION_HOURS
, SUM(DECODE(BV.VERSION_TYPE
, 'ORG_FORECAST'
, NVL(BV.TOTAL_UTILIZATION_PERCENT
, 0) + NVL(BV.TOTAL_UTILIZATION_ADJ
, 0)
, 0)) AS TOTAL_UTILIZATION_PERCENT
, PT.GENERATED_FLAG
, PFO.PROJ_FP_OPTIONS_ID AS PROJ_FIN_PLANNING_OPTIONS_ID
, DECODE(SUM(DECODE(BV.FIN_PLAN_TYPE_ID
, NULL
, -1
, 0))
, 0
, 'Y'
, 'N') AS VERSIONS_FLAG
, SUM(DECODE(PFO.REPORT_LABOR_HRS_FROM_CODE
, 'COST'
, DECODE(BV.VERSION_TYPE
, 'COST'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 'ALL'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 0)
, 'REVENUE'
, DECODE(BV.VERSION_TYPE
, 'REVENUE'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 'ALL'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.EQUIPMENT_QUANTITY
, 0)
, 0)
, NVL(BV.EQUIPMENT_QUANTITY
, 0))) AS EQUIPMENT_QUANTITY
, SUM(DECODE(PFO.REPORT_LABOR_HRS_FROM_CODE
, 'COST'
, DECODE(BV.VERSION_TYPE
, 'COST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ALL'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 0)
, 'REVENUE'
, DECODE(BV.VERSION_TYPE
, 'REVENUE'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ALL'
, NVL(BV.LABOR_QUANTITY
, 0)
, 'ORG_FORECAST'
, NVL(BV.LABOR_QUANTITY
, 0)
, 0)
, NVL(BV.LABOR_QUANTITY
, 0) )) AS PEOPLE_QUANTITY
, PT.EDIT_AFTER_BASELINE_FLAG AS ALLOW_EDIT_AFTER_BASELINE_FLAG
FROM PA_FIN_PLAN_TYPES_VL PT
, PA_BUDGET_VERSIONS BV
, PA_PROJ_FP_OPTIONS PFO
WHERE PFO.PROJECT_ID = PA_FIN_PLAN_TYPE_GLOBAL.GET_PROJECT_ID()
AND PFO.PROJECT_ID=BV.PROJECT_ID (+)
AND PFO.FIN_PLAN_TYPE_ID=BV.FIN_PLAN_TYPE_ID (+)
AND PFO.FIN_PLAN_TYPE_ID=PT.FIN_PLAN_TYPE_ID
AND PT.PLAN_CLASS_CODE IN ('BUDGET'
, 'FORECAST')
AND PFO.FIN_PLAN_OPTION_LEVEL_CODE='PLAN_TYPE'
AND BV.CI_ID IS NULL
AND BV.CURRENT_FLAG (+)='Y'
AND NVL(PT.USE_FOR_WORKPLAN_FLAG
, 'N') = 'N' GROUP BY PFO.PROJECT_ID
, PT.PLAN_CLASS_CODE
, PT.NAME
, PT.FIN_PLAN_TYPE_ID
, PT.FIN_PLAN_TYPE_CODE
, PFO.FIN_PLAN_PREFERENCE_CODE
, PT.GENERATED_FLAG
, PFO.PROJ_FP_OPTIONS_ID
, PFO.MARGIN_DERIVED_FROM_CODE
, PT.EDIT_AFTER_BASELINE_FLAG ORDER BY PT.NAME