eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_FP_VIEW_BY_COMP_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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