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_FCST_PROJ_PERIODIC_V

Source

View Text - Preformatted


SELECT asgn.project_id project_id, asgn.assignment_id assignment_id, asgn.assignment_name assignment_name, ra.resource_assignment_id resource_assignment_id, bl.period_name period_name, bl.start_date period_start_date, decode(ra.plan_error_code,NULL, '-1', '0' ) plan_processing_code, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', Round(ra.standard_bill_rate,2), 'COST', NULL, 'LABOR_HOURS', NULL, 'MARGIN', Round(ra.standard_bill_rate,2), 'MARGIN_PERCENTAGE', Round(ra.standard_bill_rate,2), NULL) std_bill_rate, DECODE( pa_fcst_global.GetProjFcstShowAmount, 'COST', Round(average_cost_rate,2), DECODE(pa_fcst_global.find_project_fixed_price(asgn.project_id), 'Y', NULL, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', Round(ra.average_bill_rate,2), 'LABOR_HOURS', NULL, 'MARGIN', Round(ra.average_bill_rate,2), 'MARGIN_PERCENTAGE', Round(ra.average_bill_rate,2), NULL))) avg_bill_rate, Round(ra.average_discount_percentage,2) avg_discount_percentage, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'MARGIN_PERCENTAGE', Round( DECODE(NVL(ra.total_plan_revenue,0),0,null, ((NVL(ra.total_plan_revenue,0)- NVL(ra.total_plan_burdened_cost,0))/NVL(ra.total_plan_revenue,1)))*100 * DECODE(pa_fcst_global.GetProbabilityPerFlag,'N',1,pa_fcst_global.GetProbabilityPer) ,2), Round(DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', ra.total_plan_revenue, 'COST', ra.total_plan_burdened_cost, 'LABOR_HOURS', ra.total_plan_quantity, 'MARGIN', NVL(ra.total_plan_revenue,0) - NVL(ra.total_plan_burdened_cost,0) ,0)* DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1,pa_fcst_global.GetProbabilityPer) ,2) )project_total, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'MARGIN_PERCENTAGE', Round( DECODE(NVL(bl.revenue,0),0,null, ((NVL(bl.revenue,0) - NVL(bl.burdened_cost,0))/ NVL(bl.revenue,1)))*100 * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, pa_fcst_global.GetProbabilityPer) ,2), Round(DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', bl.revenue, 'COST', bl.burdened_cost, 'LABOR_HOURS', bl.quantity, 'MARGIN', NVL(bl.revenue,0) - NVL(bl.burdened_cost,0) ,0)* DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1,pa_fcst_global.GetProbabilityPer) ,2) ) value FROM pa_budget_versions bv, pa_project_assignments asgn, pa_resource_assignments ra, pa_budget_lines bl, ( SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate, pa_fcst_global.GetProjectId ProjectId FROM DUAL) FilterBy WHERE bv.project_id = FilterBy.ProjectId AND bv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND bv.current_original_flag ='X' AND bv.resource_accumulated_flag='X' AND asgn.project_id = bv.project_id AND ra.project_id = asgn.project_id AND ra.project_assignment_id = asgn.assignment_id AND ra.budget_version_id = bv.budget_version_id 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)) UNION SELECT asgn.project_id project_id, asgn.assignment_id assignment_id, asgn.assignment_name assignment_name, ra.resource_assignment_id resource_assignment_id, per.period_name period_name, per.start_date period_start_date, decode( ra.plan_error_code, 'E','0','-1') plan_processing_code, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', Round(ra.standard_bill_rate,2), 'COST', NULL, 'LABOR_HOURS', NULL , 'MARGIN', Round(ra.standard_bill_rate,2), 'MARGIN_PERCENTAGE', Round(ra.standard_bill_rate,2), NULL) std_bill_rate, DECODE( pa_fcst_global.GetProjFcstShowAmount, 'COST', average_cost_rate, DECODE(pa_fcst_global.find_project_fixed_price(asgn.project_id), 'Y', NULL, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', Round(ra.average_bill_rate,2), 'LABOR_HOURS', NULL , 'MARGIN', Round(ra.average_bill_rate,2) , 'MARGIN_PERCENTAGE', Round(ra.average_bill_rate,2), NULL))) avg_bill_rate, Round(ra.average_discount_percentage,2) avg_discount_percentage, DECODE(pa_fcst_global.GetProjFcstShowAmount, 'MARGIN_PERCENTAGE', Round( DECODE(NVL(ra.total_plan_revenue,0),0,null, ((NVL(ra.total_plan_revenue,0)- NVL(ra.total_plan_burdened_cost,0))/NVL(ra.total_plan_revenue,1)))*100 *DECODE(pa_fcst_global.GetProbabilityPerFlag,'N',1,pa_fcst_global.GetProbabilityPer) ,2), Round(DECODE(pa_fcst_global.GetProjFcstShowAmount, 'REVENUE', ra.total_plan_revenue, 'COST', ra.total_plan_burdened_cost, 'LABOR_HOURS', ra.total_plan_quantity, 'MARGIN', NVL(ra.total_plan_revenue,0)-NVL(ra.total_plan_burdened_cost,0) ,0)* DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1,pa_fcst_global.GetProbabilityPer) ,2) )project_total, 0 value FROM pa_project_assignments asgn, pa_resource_assignments ra, pa_budget_versions bv, pa_fcst_periods_tmp_v per, ( SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate, pa_fcst_global.GetProjectId ProjectId FROM DUAL) FilterBy WHERE bv.project_id = FilterBy.ProjectId AND bv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND bv.current_original_flag ='X' AND bv.resource_accumulated_flag='X' AND asgn.project_id =bv.project_id AND ra.project_id = asgn.project_id AND ra.project_assignment_id = asgn.assignment_id AND ra.budget_version_id = bv.budget_version_id AND per.period_type = fnd_profile.value('PA_FORECASTING_PERIOD_TYPE') AND ((per.start_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate) OR (per.end_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate)) AND NOT EXISTS (SELECT NULL FROM pa_budget_lines bl WHERE bl.resource_assignment_id = ra.resource_assignment_id AND per.period_name = bl.period_name)

View Text - HTML Formatted

SELECT ASGN.PROJECT_ID PROJECT_ID
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, RA.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, BL.PERIOD_NAME PERIOD_NAME
, BL.START_DATE PERIOD_START_DATE
, DECODE(RA.PLAN_ERROR_CODE
, NULL
, '-1'
, '0' ) PLAN_PROCESSING_CODE
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, 'COST'
, NULL
, 'LABOR_HOURS'
, NULL
, 'MARGIN'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, 'MARGIN_PERCENTAGE'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, NULL) STD_BILL_RATE
, DECODE( PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'COST'
, ROUND(AVERAGE_COST_RATE
, 2)
, DECODE(PA_FCST_GLOBAL.FIND_PROJECT_FIXED_PRICE(ASGN.PROJECT_ID)
, 'Y'
, NULL
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, 'LABOR_HOURS'
, NULL
, 'MARGIN'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, 'MARGIN_PERCENTAGE'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, NULL))) AVG_BILL_RATE
, ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2) AVG_DISCOUNT_PERCENTAGE
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'MARGIN_PERCENTAGE'
, ROUND( DECODE(NVL(RA.TOTAL_PLAN_REVENUE
, 0)
, 0
, NULL
, ((NVL(RA.TOTAL_PLAN_REVENUE
, 0)- NVL(RA.TOTAL_PLAN_BURDENED_COST
, 0))/NVL(RA.TOTAL_PLAN_REVENUE
, 1)))*100 * DECODE(PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2)
, ROUND(DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, RA.TOTAL_PLAN_REVENUE
, 'COST'
, RA.TOTAL_PLAN_BURDENED_COST
, 'LABOR_HOURS'
, RA.TOTAL_PLAN_QUANTITY
, 'MARGIN'
, NVL(RA.TOTAL_PLAN_REVENUE
, 0) - NVL(RA.TOTAL_PLAN_BURDENED_COST
, 0)
, 0)* DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2) )PROJECT_TOTAL
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'MARGIN_PERCENTAGE'
, ROUND( DECODE(NVL(BL.REVENUE
, 0)
, 0
, NULL
, ((NVL(BL.REVENUE
, 0) - NVL(BL.BURDENED_COST
, 0))/ NVL(BL.REVENUE
, 1)))*100 * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2)
, ROUND(DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, BL.REVENUE
, 'COST'
, BL.BURDENED_COST
, 'LABOR_HOURS'
, BL.QUANTITY
, 'MARGIN'
, NVL(BL.REVENUE
, 0) - NVL(BL.BURDENED_COST
, 0)
, 0)* DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2) ) VALUE
FROM PA_BUDGET_VERSIONS BV
, PA_PROJECT_ASSIGNMENTS ASGN
, PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_LINES BL
, ( SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
, PA_FCST_GLOBAL.GETPROJECTID PROJECTID
FROM DUAL) FILTERBY
WHERE BV.PROJECT_ID = FILTERBY.PROJECTID
AND BV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND BV.CURRENT_ORIGINAL_FLAG ='X'
AND BV.RESOURCE_ACCUMULATED_FLAG='X'
AND ASGN.PROJECT_ID = BV.PROJECT_ID
AND RA.PROJECT_ID = ASGN.PROJECT_ID
AND RA.PROJECT_ASSIGNMENT_ID = ASGN.ASSIGNMENT_ID
AND RA.BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID
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)) UNION SELECT ASGN.PROJECT_ID PROJECT_ID
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, RA.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PER.PERIOD_NAME PERIOD_NAME
, PER.START_DATE PERIOD_START_DATE
, DECODE( RA.PLAN_ERROR_CODE
, 'E'
, '0'
, '-1') PLAN_PROCESSING_CODE
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, 'COST'
, NULL
, 'LABOR_HOURS'
, NULL
, 'MARGIN'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, 'MARGIN_PERCENTAGE'
, ROUND(RA.STANDARD_BILL_RATE
, 2)
, NULL) STD_BILL_RATE
, DECODE( PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'COST'
, AVERAGE_COST_RATE
, DECODE(PA_FCST_GLOBAL.FIND_PROJECT_FIXED_PRICE(ASGN.PROJECT_ID)
, 'Y'
, NULL
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, 'LABOR_HOURS'
, NULL
, 'MARGIN'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, 'MARGIN_PERCENTAGE'
, ROUND(RA.AVERAGE_BILL_RATE
, 2)
, NULL))) AVG_BILL_RATE
, ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2) AVG_DISCOUNT_PERCENTAGE
, DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'MARGIN_PERCENTAGE'
, ROUND( DECODE(NVL(RA.TOTAL_PLAN_REVENUE
, 0)
, 0
, NULL
, ((NVL(RA.TOTAL_PLAN_REVENUE
, 0)- NVL(RA.TOTAL_PLAN_BURDENED_COST
, 0))/NVL(RA.TOTAL_PLAN_REVENUE
, 1)))*100 *DECODE(PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2)
, ROUND(DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'REVENUE'
, RA.TOTAL_PLAN_REVENUE
, 'COST'
, RA.TOTAL_PLAN_BURDENED_COST
, 'LABOR_HOURS'
, RA.TOTAL_PLAN_QUANTITY
, 'MARGIN'
, NVL(RA.TOTAL_PLAN_REVENUE
, 0)-NVL(RA.TOTAL_PLAN_BURDENED_COST
, 0)
, 0)* DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2) )PROJECT_TOTAL
, 0 VALUE
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_VERSIONS BV
, PA_FCST_PERIODS_TMP_V PER
, ( SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
, PA_FCST_GLOBAL.GETPROJECTID PROJECTID
FROM DUAL) FILTERBY
WHERE BV.PROJECT_ID = FILTERBY.PROJECTID
AND BV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND BV.CURRENT_ORIGINAL_FLAG ='X'
AND BV.RESOURCE_ACCUMULATED_FLAG='X'
AND ASGN.PROJECT_ID =BV.PROJECT_ID
AND RA.PROJECT_ID = ASGN.PROJECT_ID
AND RA.PROJECT_ASSIGNMENT_ID = ASGN.ASSIGNMENT_ID
AND RA.BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID
AND PER.PERIOD_TYPE = FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE')
AND ((PER.START_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE) OR (PER.END_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE))
AND NOT EXISTS (SELECT NULL
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID
AND PER.PERIOD_NAME = BL.PERIOD_NAME)