View: PA_FIN_PLAN_TYPES_B_V

Product:	PA - Projects
Description:	This view is used by the Plan Types page. It displays all baselined plan types for a given project id.
Implementation/DBA Data:	APPS.PA_FIN_PLAN_TYPES_B_V

View Text

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

Columns