View: PA_FCST_PROJ_HDR_V

Product:	PA - Projects
Description:	PA_FCST_PROJ_HDR_V is a view that shows project forecasting
Implementation/DBA Data:	APPS.PA_FCST_PROJ_HDR_V

View Text

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))

Columns