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_SUM_PV_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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