VIEW: APPS.PA_FCST_PROJECT_HDR_V

Source

View Text - Preformatted

SELECT pr.project_currency_code currency_code, to_char(ROUND(SUM(bl.burdened_cost * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1, 1, pr.PROBABILITY_PERCENTAGE/100))),2) ,'999,999,999,999,990.99'), to_char(ROUND(SUM(bl.revenue * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1,pr.PROBABILITY_PERCENTAGE/100))),2) ,'999,999,999,999,990.99'), to_char(ROUND(SUM(bl.quantity * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1,pr.PROBABILITY_PERCENTAGE/100))),2) ,'999,999,999,999,990.99'), to_char(ROUND(SUM((NVL(bl.revenue,0) - NVL(bl.burdened_cost,0)) * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1, 1,pr.PROBABILITY_PERCENTAGE/100))),2) ,'999,999,999,999,990.99'), to_char(ROUND(DECODE(SUM(nvl(bl.revenue,0)),0,NULL, (((SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.burdened_cost,0))) * 100)/ SUM(NVL(bl.revenue,0))) * AVG(DECODE(pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1,pr.PROBABILITY_PERCENTAGE/100)))),2) ,'999,999,990.99') FROM pa_fcst_project_list_v pr, pa_resource_assignments ra, pa_budget_lines bl, pa_budget_versions bv, pa_fcst_periods_tmp per WHERE per.period_name = bl.period_name AND bv.project_id =pr.project_id AND bv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND bv.budget_version_id = ra.budget_version_id AND pr.project_id = ra.project_id AND ra.resource_list_member_id = 103 AND bl.resource_assignment_id = ra.resource_assignment_id GROUP BY pr.project_currency_code

View Text - HTML Formatted

SELECT PR.PROJECT_CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(ROUND(SUM(BL.BURDENED_COST * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100)))
, 2)
, '999
, 999
, 999
, 999
, 990.99')
, TO_CHAR(ROUND(SUM(BL.REVENUE * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100)))
, 2)
, '999
, 999
, 999
, 999
, 990.99')
, TO_CHAR(ROUND(SUM(BL.QUANTITY * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100)))
, 2)
, '999
, 999
, 999
, 999
, 990.99')
, TO_CHAR(ROUND(SUM((NVL(BL.REVENUE
, 0) - NVL(BL.BURDENED_COST
, 0)) * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100)))
, 2)
, '999
, 999
, 999
, 999
, 990.99')
, TO_CHAR(ROUND(DECODE(SUM(NVL(BL.REVENUE
, 0))
, 0
, NULL
, (((SUM(NVL(BL.REVENUE
, 0)) - SUM(NVL(BL.BURDENED_COST
, 0))) * 100)/ SUM(NVL(BL.REVENUE
, 0))) * AVG(DECODE(PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))))
, 2)
, '999
, 999
, 990.99')
FROM PA_FCST_PROJECT_LIST_V PR
, PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_LINES BL
, PA_BUDGET_VERSIONS BV
, PA_FCST_PERIODS_TMP PER
WHERE PER.PERIOD_NAME = BL.PERIOD_NAME
AND BV.PROJECT_ID =PR.PROJECT_ID
AND BV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND PR.PROJECT_ID = RA.PROJECT_ID
AND RA.RESOURCE_LIST_MEMBER_ID = 103
AND BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID GROUP BY PR.PROJECT_CURRENCY_CODE