[Home] [Help]
View: PA_REP_RES_AVL_WK_V
Product: | PA - Projects |
Description: | PA_REP_RES_AVL_WK_V is a view that shows resource capacity for a gloabl week |
Implementation/DBA Data: |
APPS.PA_REP_RES_AVL_WK_V
|
View Text
SELECT PRD.RESOURCE_ID RES_ID
, PRD.RESOURCE_NAME
, PRD.MANAGER_ID
, PRD.MANAGER_NAME
, PRD.RESOURCE_ORGANIZATION_ID
, PFI.GLOBAL_EXP_PERIOD_END_DATE
, TO_NUMBER(TO_CHAR(PFI.GLOBAL_EXP_PERIOD_END_DATE
, 'MM'))
, TO_NUMBER(TO_CHAR(PFI.GLOBAL_EXP_PERIOD_END_DATE
, 'YYYY'))
, PRD.RESOURCE_EFFECTIVE_START_DATE + TEMP.SEQ_NUMBER
, PFI.CAPACITY_QUANTITY
, PFI.AVAILABILITY_QUANTITY
, PA_RESOURCE_UTILS.GET_PROVISIONAL_HOURS (PRD.RESOURCE_ID
, PRD.RESOURCE_EFFECTIVE_START_DATE + TEMP.SEQ_NUMBER)
, PJ.NAME
, PRD.RESOURCE_JOB_LEVEL
FROM PA_RESOURCES_DENORM PRD
, PA_REP_SEQ_NUMBER TEMP
, PER_JOBS PJ
, PA_FORECAST_ITEMS PFI
, (SELECT NVL(FND_NUMBER.CANONICAL_TO_NUMBER(FND_PROFILE.VALUE('PA_RES_CAPACITY_PERCENTAGE'))
, 0)/100 VALUE_PERCENT
FROM DUAL) PROFILE
WHERE PRD.JOB_ID = PJ.JOB_ID
AND PRD.RESOURCE_EFFECTIVE_END_DATE >= SYSDATE
AND TEMP.SEQ_NUMBER > 0
AND PFI.RESOURCE_ID = PRD.RESOURCE_ID
AND PRD.SCHEDULABLE_FLAG = 'Y'
AND PFI.FORECAST_ITEM_TYPE = 'U'
AND PFI.DELETE_FLAG = 'N'
AND PFI.AVAILABILITY_FLAG = 'Y'
AND PFI.OVERCOMMITMENT_FLAG = 'N'
AND PFI.ITEM_DATE = (PRD.RESOURCE_EFFECTIVE_START_DATE + TEMP.SEQ_NUMBER)
AND PFI.AVAILABILITY_QUANTITY >= CAPACITY_QUANTITY * PROFILE.VALUE_PERCENT
Columns
Name |
RESOURCE_ID |
RESOURCE_NAME |
MANAGER_ID |
MANAGER_NAME |
ORGANIZATION_ID |
GLOBAL_WEEK_END_DATE |
MONTH |
YEAR |
WEEK_DAY |
CAPACITY_HOURS |
DEFINITE_AVAILABLE_HOURS |
PROVISIONAL_HOURS |
JOB_NAME |
RESOURCE_JOB_LEVEL |