View: PA_FP_SUM_PV_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_FP_SUM_PV_V

View Text

SELECT PBV.PROJECT_ID AS PROJECT_ID
, PRA.RESOURCE_ASSIGNMENT_ID AS RESOURCE_ASSIGNMENT_ID
, PBV.VERSION_NAME AS VERSION_NAME
, PBV.VERSION_NUMBER AS VERSION_NUM
, PATL.AMOUNT_TYPE_NAME AS AMOUNT_TYPE_NAME
, PPPD.AMOUNT_TYPE_CODE AS AMOUNT_TYPE_CODE
, DECODE(PPPD.AMOUNT_TYPE_CODE
, 'MARGIN'
, 'N'
, 'MARGIN_PERCENT'
, 'N'
, 'QUANTITY'
, 'N'
, 'Y') AS SHOW_URL_FLAG
, PPPD.PRECEDING_PERIODS_AMOUNT
, PPPD.SUCCEEDING_PERIODS_AMOUNT
, SUM(DECODE(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.AMOUNT_TYPE_CODE
, 'MARGIN_PERCENT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_PA_AMOUNT('MARGIN_PERCENT')
, 'UTILIZATION'
, PA_FP_ORG_FCST_UTILS. CALCULATE_PA_AMOUNT('UTILIZATION')
, 'HEADCOUNT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_PA_AMOUNT('HEADCOUNT')
, SUM(DECODE(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.AMOUNT_TYPE_CODE
, 'MARGIN_PERCENT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_GL_AMOUNT('MARGIN_PERCENT')
, 'UTILIZATION'
, PA_FP_ORG_FCST_UTILS. CALCULATE_GL_AMOUNT('UTILIZATION')
, 'HEADCOUNT'
, PA_FP_ORG_FCST_UTILS. CALCULATE_GL_AMOUNT('HEADCOUNT')
, SUM(DECODE(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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(PPPD.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_RESOURCE_ASSIGNMENTS PRA
, PA_BUDGET_VERSIONS PBV
, PA_FP_PERIOD_VALUES_V PPPD
, PA_AMOUNT_TYPES_VL PATL
WHERE PBV.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PRA.PROJECT_ID = PBV.PROJECT_ID
AND PRA.BUDGET_VERSION_ID=PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PRA.RESOURCE_ASSIGNMENT_TYPE='PROJECT'
AND PPPD.BUDGET_VERSION_ID=PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND PPPD.RESOURCE_ASSIGNMENT_ID=PRA.RESOURCE_ASSIGNMENT_ID
AND PPPD.AMOUNT_TYPE_ID = PATL.AMOUNT_TYPE_ID
AND PPPD.AMOUNT_TYPE_CODE != 'QUANTITY' GROUP BY PPPD.AMOUNT_TYPE_CODE
, PBV.PROJECT_ID
, PRA.RESOURCE_ASSIGNMENT_ID
, PATL.AMOUNT_TYPE_NAME
, PBV.VERSION_NAME
, PBV.VERSION_NUMBER
, PPPD.PRECEDING_PERIODS_AMOUNT
, PPPD.SUCCEEDING_PERIODS_AMOUNT
, PATL.AMOUNT_TYPE_ID ORDER BY PATL.AMOUNT_TYPE_ID

Columns