VIEW: APPS.PA_FCST_PROJECT_SUM_V

Source

View Text - Preformatted

SELECT 'Y' forecast_exists_flag, pr.project_id, pr.project_number, pr.project_name , org.name , pr.customer_name , pr.project_currency_code , bl.period_name , fnd_profile.value('PA_FORECASTING_PERIOD_TYPE'), bl.start_date, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', ROUND(bl.revenue * DECODE(pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'COST', ROUND(bl.burdened_cost * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'LABOR_HOURS', ROUND(bl.quantity * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'MARGIN', ROUND((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), 'MARGIN_PERCENTAGE', ROUND(DECODE(NVL(bl.revenue,0),0,0, (((NVL(bl.revenue,0) - NVL(bl.burdened_cost,0))*100)/ NVL(bl.revenue,0))) * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2)) value FROM pa_fcst_project_list_v pr, pa_resource_assignments ra, pa_budget_lines bl, hr_all_organization_units_tl org, pa_budget_versions bv, (SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate FROM DUAL) FilterBY WHERE pr.project_id =bv.project_id AND bv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND bv.budget_version_id = ra.budget_version_id AND bv.CURRENT_ORIGINAL_FLAG ='X' AND bv.RESOURCE_ACCUMULATED_FLAG ='X' AND org.organization_id = pr.carrying_out_organization_id AND org.language = userenv('LANG') AND pr.project_id= ra.project_id AND ra.resource_list_member_id = 103 AND bl.resource_assignment_id = ra.resource_assignment_id AND ((bl.start_date BETWEEN FilterBY.StartDate AND FilterBY.EndDate) OR (bl.end_date BETWEEN FilterBY.StartDate AND FilterBY.EndDate))

View Text - HTML Formatted

SELECT 'Y' FORECAST_EXISTS_FLAG
, PR.PROJECT_ID
, PR.PROJECT_NUMBER
, PR.PROJECT_NAME
, ORG.NAME
, PR.CUSTOMER_NAME
, PR.PROJECT_CURRENCY_CODE
, BL.PERIOD_NAME
, FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE')
, BL.START_DATE
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, ROUND(BL.REVENUE * DECODE(PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'COST'
, ROUND(BL.BURDENED_COST * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'LABOR_HOURS'
, ROUND(BL.QUANTITY * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'MARGIN'
, ROUND((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)
, 'MARGIN_PERCENTAGE'
, ROUND(DECODE(NVL(BL.REVENUE
, 0)
, 0
, 0
, (((NVL(BL.REVENUE
, 0) - NVL(BL.BURDENED_COST
, 0))*100)/ NVL(BL.REVENUE
, 0))) * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)) VALUE
FROM PA_FCST_PROJECT_LIST_V PR
, PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_LINES BL
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, PA_BUDGET_VERSIONS BV
, (SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
FROM DUAL) FILTERBY
WHERE PR.PROJECT_ID =BV.PROJECT_ID
AND BV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND BV.CURRENT_ORIGINAL_FLAG ='X'
AND BV.RESOURCE_ACCUMULATED_FLAG ='X'
AND ORG.ORGANIZATION_ID = PR.CARRYING_OUT_ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND PR.PROJECT_ID= RA.PROJECT_ID
AND RA.RESOURCE_LIST_MEMBER_ID = 103
AND BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID
AND ((BL.START_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE) OR (BL.END_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE))