View: PA_FP_VIEW_BY_COMP_V

Product:	PA - Projects
Description:	The pa_fp_view_by_comp_v view store the forecast amounts by period for one amount types.
Implementation/DBA Data:	APPS.PA_FP_VIEW_BY_COMP_V

View Text

SELECT PATL.AMOUNT_TYPE_NAME AS AMOUNT_SUBTYPE_NAME
, PPPD.AMOUNT_SUBTYPE_CODE AS AMOUNT_SUBTYPE_CODE
, PPPD.AMOUNT_SUBTYPE_ID AS AMOUNT_SYBTYPE_ID
, PPPD.PRECEDING_PERIODS_AMOUNT
, PPPD.SUCCEEDING_PERIODS_AMOUNT
, PPPD.PERIOD_AMOUNT1 AS PERIOD1
, PPPD.PERIOD_AMOUNT2 AS PERIOD2
, PPPD.PERIOD_AMOUNT3 AS PERIOD3
, PPPD.PERIOD_AMOUNT4 AS PERIOD4
, PPPD.PERIOD_AMOUNT5 AS PERIOD5
, PPPD.PERIOD_AMOUNT6 AS PERIOD6
, PPPD.PERIOD_AMOUNT7 AS PERIOD7
, PPPD.PERIOD_AMOUNT8 AS PERIOD8
, PPPD.PERIOD_AMOUNT9 AS PERIOD9
, PPPD.PERIOD_AMOUNT10 AS PERIOD10
, PPPD.PERIOD_AMOUNT11 AS PERIOD11
, PPPD.PERIOD_AMOUNT12 AS PERIOD12
, PPPD.PERIOD_AMOUNT13 AS PERIOD13
, DECODE(PPPD.AMOUNT_SUBTYPE_CODE
, 'HEADCOUNT_ADJUSTMENTS'
, PA_FP_ORG_FCST_UTILS.CALCULATE_PA_AMOUNT('HEADCOUNT_ADJUSTMENTS')
, 'BEGIN_HEADCOUNT'
, PA_FP_ORG_FCST_UTILS.CALCULATE_PA_AMOUNT('BEGIN_HEADCOUNT')
, 'UTILIZATION_ADJUSTMENTS'
, PA_FP_ORG_FCST_UTILS.CALCULATE_PA_AMOUNT('UTILIZATION_ADJUSTMENTS')
, 'UTILIZATION_PERCENT'
, PA_FP_ORG_FCST_UTILS.CALCULATE_PA_AMOUNT('UTILIZATION_PERCENT')
, NVL(PPPD.PERIOD_AMOUNT1
, 0)+ NVL(PPPD.PERIOD_AMOUNT2
, 0)+ NVL(PPPD.PERIOD_AMOUNT3
, 0)+ NVL(PPPD.PERIOD_AMOUNT4
, 0)+ NVL(PPPD.PERIOD_AMOUNT5
, 0)+ NVL(PPPD.PERIOD_AMOUNT6
, 0)+ NVL(PPPD.PERIOD_AMOUNT7
, 0)+ NVL(PPPD.PERIOD_AMOUNT8
, 0)+ NVL(PPPD.PERIOD_AMOUNT9
, 0)+ NVL(PPPD.PERIOD_AMOUNT10
, 0)+ NVL(PPPD.PERIOD_AMOUNT11
, 0)+ NVL(PPPD.PERIOD_AMOUNT12
, 0)+ NVL(PPPD.PERIOD_AMOUNT13
, 0)) AS TOTAL_PA
, DECODE(PPPD.AMOUNT_SUBTYPE_CODE
, 'HEADCOUNT_ADJUSTMENTS'
, PA_FP_ORG_FCST_UTILS.CALCULATE_GL_AMOUNT('HEADCOUNT_ADJUSTMENTS')
, 'BEGIN_HEADCOUNT'
, PA_FP_ORG_FCST_UTILS.CALCULATE_GL_AMOUNT('BEGIN_HEADCOUNT')
, 'UTILIZATION_ADJUSTMENTS'
, PA_FP_ORG_FCST_UTILS.CALCULATE_GL_AMOUNT('UTILIZATION_ADJUSTMENTS')
, 'UTILIZATION_PERCENT'
, PA_FP_ORG_FCST_UTILS.CALCULATE_GL_AMOUNT('UTILIZATION_PERCENT')
, NVL(PPPD.PERIOD_AMOUNT1
, 0)+ NVL(PPPD.PERIOD_AMOUNT2
, 0)+ NVL(PPPD.PERIOD_AMOUNT3
, 0)+ NVL(PPPD.PERIOD_AMOUNT4
, 0)+ NVL(PPPD.PERIOD_AMOUNT5
, 0)+ NVL(PPPD.PERIOD_AMOUNT6
, 0)) AS TOTAL
FROM PA_FP_PERIOD_VALUES_V PPPD
, PA_AMOUNT_TYPES_VL PATL
WHERE PPPD.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PPPD.RESOURCE_ASSIGNMENT_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_RESOURCE_ASSIGNMENT_ID()
AND PPPD.AMOUNT_TYPE_CODE = PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
AND PPPD.AMOUNT_SUBTYPE_ID = PATL.AMOUNT_TYPE_ID UNION SELECT PALK.MEANING AS AMOUNT_SUBTYPE_NAME
, 'ZZZ' AS AMOUNT_SUBTYPE_CODE
, 9999 AS AMOUNT_SUBTYPE_ID
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PRECEDING_PERIODS_AMOUNT
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PRECEDING_PERIODS_AMOUNT
, 0)*-1
, NVL(PPPD.PRECEDING_PERIODS_AMOUNT
, 0) ) ) AS PRECEDING_PERIODS_AMOUNT
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.SUCCEEDING_PERIODS_AMOUNT
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.SUCCEEDING_PERIODS_AMOUNT
, 0)*-1
, NVL(PPPD.SUCCEEDING_PERIODS_AMOUNT
, 0) ) ) AS SUCCEEDING_PERIODS_AMOUNT
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT1
, 0) ) ) AS PERIOD1
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT2
, 0) ) ) AS PERIOD2
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT3
, 0) ) ) AS PERIOD3
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT4
, 0) ) ) AS PERIOD4
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT5
, 0) ) ) AS PERIOD5
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT6
, 0) ) ) AS PERIOD6
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT7
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT7
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT7
, 0) ) ) AS PERIOD7
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT8
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT8
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT8
, 0) ) ) AS PERIOD8
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT9
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT9
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT9
, 0) ) ) AS PERIOD9
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT10
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT10
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT10
, 0) ) ) AS PERIOD10
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT11
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT11
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT11
, 0) ) ) AS PERIOD11
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT12
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT12
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT12
, 0) ) ) AS PERIOD12
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT13
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT13
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT13
, 0) ) ) AS PERIOD13
, DECODE(PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
, 'HEADCOUNT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_PA_AMOUNT('HEADCOUNT')
, 'UTILIZATION'
, PA_FP_ORG_FCST_UTILS. CALCULATE_PA_AMOUNT('UTILIZATION')
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT1
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT2
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT3
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT4
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT5
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT6
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT7
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT7
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT7
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT8
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT8
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT8
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT9
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT9
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT9
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT10
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT10
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT10
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT11
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT11
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT11
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT12
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT12
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT12
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT13
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT13
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT13
, 0) ) ) ) AS TOTAL_PA
, DECODE(PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
, 'HEADCOUNT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_GL_AMOUNT('HEADCOUNT')
, 'UTILIZATION'
, PA_FP_ORG_FCST_UTILS. CALCULATE_GL_AMOUNT('UTILIZATION')
, SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT1
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT1
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT2
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT2
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT3
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT3
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT4
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT4
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT5
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT5
, 0) ) )+ SUM(DECODE(AMOUNT_SUBTYPE_CODE
, 'TP_REVENUE_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, 'TP_COST_OUT'
, NVL(PPPD.PERIOD_AMOUNT6
, 0)*-1
, NVL(PPPD.PERIOD_AMOUNT6
, 0) ) ) ) AS TOTAL
FROM PA_FP_PERIOD_VALUES_V PPPD
, PA_LOOKUPS PALK
WHERE PPPD.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PPPD.RESOURCE_ASSIGNMENT_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_RESOURCE_ASSIGNMENT_ID()
AND PPPD.AMOUNT_TYPE_CODE = PA_FIN_PLAN_VIEW_GLOBAL.GET_AMOUNT_TYPE_CODE()
AND PALK.LOOKUP_TYPE='TRANSLATION_FP'
AND PALK.LOOKUP_CODE='TOTAL' GROUP BY PALK.MEANING

Columns