View: PA_FCST_PROJECT_DTL_V

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

View Text

SELECT "PROJECT_ID"
, "PROJECT_NAME"
, "PROJECT_NUMBER"
, "ORGANIZATION_NAME"
, "CUSTOMER_NAME"
, "PROJECT_OWNER"
, "PROJECT_TYPE"
, "CLASSIFICATION"
, "PROJECT_STATUS"
, "AS_OF_DATE"
, "CURRENCY_CODE"
, "DISCOUNT_PERCENTAGE"
, "PROBABILITY_PERCENTAGE"
, "PROJECT_TOTAL"
FROM ( SELECT PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME PROJECT_NAME
, PR.PROJECT_NUMBER PROJECT_NUMBER
, HR.NAME ORGANIZATION_NAME
, PR.CUSTOMER_NAME CUSTOMER_NAME
, PA_FCST_GLOBAL.FIND_PROJECT_OWNER(PR.PROJECT_ID
, PR.START_DATE
, PR.COMPLETION_DATE) PROJECT_OWNER
, PR.PROJECT_TYPE PROJECT_TYPE
, PC.CLASS_CODE CLASSIFICATION
, PPS.PROJECT_STATUS_NAME PROJECT_STATUS
, PBV.PLAN_RUN_DATE AS_OF_DATE
, PR.PROJECT_CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2)
, '999
, 999
, 990.99') DISCOUNT_PERCENTAGE
, TO_CHAR(ROUND(PR.PROBABILITY_PERCENTAGE
, 2)
, '999
, 999
, 990.99') PROBABILITY_PERCENTAGE
, TO_CHAR(DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'LABOR_HOURS'
, ROUND(RA.TOTAL_PLAN_QUANTITY * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'COST'
, ROUND(RA.TOTAL_PLAN_BURDENED_COST * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'REVENUE'
, ROUND(RA.TOTAL_PLAN_REVENUE * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'MARGIN'
, ROUND((RA.TOTAL_PLAN_REVENUE - RA.TOTAL_PLAN_BURDENED_COST) * 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(RA.TOTAL_PLAN_REVENUE
, 0)
, 0
, 0
, ((RA.TOTAL_PLAN_REVENUE - RA.TOTAL_PLAN_BURDENED_COST) * 100)/RA.TOTAL_PLAN_REVENUE) * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2))
, '999
, 999
, 999
, 999
, 990.99') PROJECT_TOTAL
FROM PA_FCST_PROJECT_LIST_V PR
, PA_PROJECT_STATUSES PPS
, HR_ALL_ORGANIZATION_UNITS_TL HR
, PA_BUDGET_VERSIONS PBV
, PA_RESOURCE_ASSIGNMENTS RA
, PA_PROJECT_CLASSES PC
WHERE PR.PROJECT_ID =PBV.PROJECT_ID
AND PPS.PROJECT_STATUS_CODE = PR.PROJECT_STATUS_CODE
AND PBV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND PBV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND PBV.CURRENT_ORIGINAL_FLAG ='X'
AND PBV.RESOURCE_ACCUMULATED_FLAG ='X'
AND RA.RESOURCE_LIST_MEMBER_ID = 103
AND HR.ORGANIZATION_ID= PR.CARRYING_OUT_ORGANIZATION_ID
AND HR.LANGUAGE = USERENV('LANG')
AND PC.CLASS_CATEGORY(+) = PA_FCST_GLOBAL.GETCLASSCATGORY
AND PR.PROJECT_ID = PC.PROJECT_ID(+) UNION SELECT PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME PROJECT_NAME
, PR.PROJECT_NUMBER PROJECT_NUMBER
, HR.NAME ORGANIZATION_NAME
, PR.CUSTOMER_NAME CUSTOMER_NAME
, PA_FCST_GLOBAL.FIND_PROJECT_OWNER(PR.PROJECT_ID
, PR.START_DATE
, PR.COMPLETION_DATE) PROJECT_OWNER
, PR.PROJECT_TYPE PROJECT_TYPE
, PC.CLASS_CODE CLASSIFICATION
, PPS.PROJECT_STATUS_NAME PROJECT_STATUS
, SYSDATE AS_OF_DATE
, PR.PROJECT_CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(0
, '999
, 999
, 990.99') DISCOUNT_PERCENTAGE
, TO_CHAR(PR.PROBABILITY_PERCENTAGE
, '999
, 999
, 990.99') PROBABILITY_PERCENTAGE
, TO_CHAR(0
, '999
, 999
, 999
, 999
, 990.99') PROJECT_TOTAL
FROM PA_FCST_PROJECT_LIST_V PR
, PA_PROJECT_STATUSES PPS
, HR_ALL_ORGANIZATION_UNITS_TL HR
, PA_PROJECT_CLASSES PC
WHERE PPS.PROJECT_STATUS_CODE = PR.PROJECT_STATUS_CODE
AND HR.ORGANIZATION_ID= PR.CARRYING_OUT_ORGANIZATION_ID
AND HR.LANGUAGE = USERENV('LANG')
AND PC.CLASS_CATEGORY(+) = PA_FCST_GLOBAL.GETCLASSCATGORY
AND PR.PROJECT_ID = PC.PROJECT_ID(+)
AND NOT EXISTS (SELECT NULL
FROM PA_BUDGET_VERSIONS PBV
, PA_RESOURCE_ASSIGNMENTS RA
WHERE PBV.PROJECT_ID = PR.PROJECT_ID
AND PBV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND RA.PROJECT_ID = PR.PROJECT_ID
AND PBV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND PBV.CURRENT_ORIGINAL_FLAG ='X'
AND PBV.RESOURCE_ACCUMULATED_FLAG ='X') ) ORDER BY PROJECT_NAME
, PROJECT_ID

Columns