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_HDR_V

Source

View Text - Preformatted


SELECT project_id, y1, y1_seq, y1_rightattribute1, y1_leftattribute1, x1, DENSE_RANK() OVER (PARTITION BY y1 ORDER BY period_start_date) AS x1_seq, value, period_start_date FROM ( SELECT pr.project_id project_id, pr.segment1 y1, 0 y1_seq, to_char(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), 'MARGIN_PERCENTAGE', 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) ,0)* DECODE(pa_fcst_global.GetProbabilityPerFlag,'N',1, pa_fcst_global.GetProbabilityPer) ,2),'999,999,999,999,990.99') y1_rightattribute1, to_char(Round(ra.average_discount_percentage,2),'999,999,990.99') y1_leftattribute1, bl.period_name x1, 0 x1_seq, 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, bl.start_date period_start_date FROM pa_resource_assignments ra, pa_projects_all pr, pa_budget_lines bl, pa_budget_versions bv, (SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate, pa_fcst_global.GetProjectId ProjectId FROM DUAL) FilterBy WHERE pr.project_id = FilterBy.ProjectId AND 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 ra.resource_list_member_id= 103 AND ra.resource_assignment_id = bl.resource_assignment_id AND ra.project_id = pr.project_id AND ((bl.start_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate) OR (bl.end_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate)) UNION SELECT pr.project_id project_id, pr.segment1 y1, 0 y1_seq, to_char(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), 'MARGIN_PERCENTAGE', 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) ,0)* DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, pa_fcst_global.GetProbabilityPer) ,2),'999,999,999,999,990.99') y1_rightattribute1, to_char(Round(ra.average_discount_percentage,2),'999,999,990.99') y1_leftattribute1, per.period_name x1, 0 x1_seq, 0 value, per.start_date period_start_date FROM pa_resource_assignments ra, pa_projects_all pr, 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 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 pr.project_id = FilterBy.ProjectId 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 ra.resource_list_member_id= 103 AND NOT EXISTS( SELECT NULL FROM pa_budget_lines bl WHERE ra.resource_assignment_id = bl.resource_assignment_id AND per.period_name = bl.period_name))

View Text - HTML Formatted

SELECT PROJECT_ID
, Y1
, Y1_SEQ
, Y1_RIGHTATTRIBUTE1
, Y1_LEFTATTRIBUTE1
, X1
, DENSE_RANK() OVER (PARTITION BY Y1 ORDER BY PERIOD_START_DATE) AS X1_SEQ
, VALUE
, PERIOD_START_DATE
FROM ( SELECT PR.PROJECT_ID PROJECT_ID
, PR.SEGMENT1 Y1
, 0 Y1_SEQ
, TO_CHAR(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)
, 'MARGIN_PERCENTAGE'
, 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)
, 0)* DECODE(PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2)
, '999
, 999
, 999
, 999
, 990.99') Y1_RIGHTATTRIBUTE1
, TO_CHAR(ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2)
, '999
, 999
, 990.99') Y1_LEFTATTRIBUTE1
, BL.PERIOD_NAME X1
, 0 X1_SEQ
, 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
, BL.START_DATE PERIOD_START_DATE
FROM PA_RESOURCE_ASSIGNMENTS RA
, PA_PROJECTS_ALL PR
, PA_BUDGET_LINES BL
, PA_BUDGET_VERSIONS BV
, (SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
, PA_FCST_GLOBAL.GETPROJECTID PROJECTID
FROM DUAL) FILTERBY
WHERE PR.PROJECT_ID = FILTERBY.PROJECTID
AND 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 RA.RESOURCE_LIST_MEMBER_ID= 103
AND RA.RESOURCE_ASSIGNMENT_ID = BL.RESOURCE_ASSIGNMENT_ID
AND RA.PROJECT_ID = PR.PROJECT_ID
AND ((BL.START_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE) OR (BL.END_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE)) UNION SELECT PR.PROJECT_ID PROJECT_ID
, PR.SEGMENT1 Y1
, 0 Y1_SEQ
, TO_CHAR(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)
, 'MARGIN_PERCENTAGE'
, 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)
, 0)* DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, PA_FCST_GLOBAL.GETPROBABILITYPER)
, 2)
, '999
, 999
, 999
, 999
, 990.99') Y1_RIGHTATTRIBUTE1
, TO_CHAR(ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2)
, '999
, 999
, 990.99') Y1_LEFTATTRIBUTE1
, PER.PERIOD_NAME X1
, 0 X1_SEQ
, 0 VALUE
, PER.START_DATE PERIOD_START_DATE
FROM PA_RESOURCE_ASSIGNMENTS RA
, PA_PROJECTS_ALL PR
, 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 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 PR.PROJECT_ID = FILTERBY.PROJECTID
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 RA.RESOURCE_LIST_MEMBER_ID= 103
AND NOT EXISTS( SELECT NULL
FROM PA_BUDGET_LINES BL
WHERE RA.RESOURCE_ASSIGNMENT_ID = BL.RESOURCE_ASSIGNMENT_ID
AND PER.PERIOD_NAME = BL.PERIOD_NAME))