View: PA_FCST_PROJ_PERIODIC_V

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

View Text

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)

Columns