View: CRP_REQUIRED_HOURS

Product:	CRP - Capacity
Description:	View of required hours by day
Implementation/DBA Data:	APPS.CRP_REQUIRED_HOURS

View Text

SELECT RES_HOURS.RESOURCE_ID
, RES.RESOURCE_CODE
, RES.DESCRIPTION
, RES_HOURS.DEPARTMENT_ID
, DEPTS.DEPARTMENT_CODE
, DEPTS.DESCRIPTION
, RES_HOURS.BILL_OF_RESOURCES
, DATES.SCHEDULE_DESIGNATOR
, RES_HOURS.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, RESOURCE_DATE.CALENDAR_DATE
, SUM(DECODE(RES_HOURS.BASIS
, 1
, NVL(SCHEDULE_QUANTITY
, REPETITIVE_DAILY_RATE) *NVL(RES_HOURS.RESOURCE_DEPARTMENT_HOURS
, 0) *NVL(RES_HOURS.ASSEMBLY_USAGE
, 1)
, NVL(RES_HOURS.RESOURCE_DEPARTMENT_HOURS
, 0)/(LAST_DUE.SEQ_NUM-FIRST_DUE.SEQ_NUM+1)))
FROM CRP_RESOURCE_HOURS RES_HOURS
, MRP_SCHEDULE_DATES DATES
, MRP_SCHEDULE_DESIGNATORS DESIG
, MTL_PARAMETERS PARAM
, BOM_RESOURCES RES
, BOM_DEPARTMENTS DEPTS
, BOM_CALENDAR_DATES FIRST_DUE
, BOM_CALENDAR_DATES LAST_DUE
, BOM_CALENDAR_DATES FIRST_RES
, BOM_CALENDAR_DATES LAST_RES
, BOM_CALENDAR_DATES RESOURCE_DATE
WHERE RESOURCE_DATE.CALENDAR_DATE BETWEEN FIRST_RES.CALENDAR_DATE
AND LAST_RES.CALENDAR_DATE
AND RESOURCE_DATE.SEQ_NUM IS NOT NULL
AND RESOURCE_DATE.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND RESOURCE_DATE.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND LAST_RES.SEQ_NUM = LAST_DUE.SEQ_NUM - ROUND(RES_HOURS.SETBACK_DAYS)
AND LAST_RES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND LAST_RES.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND FIRST_RES.SEQ_NUM = FIRST_DUE.SEQ_NUM - ROUND(RES_HOURS.SETBACK_DAYS)
AND FIRST_RES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND FIRST_RES.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND LAST_DUE.CALENDAR_DATE = NVL(DATES.RATE_END_DATE
, DATES.SCHEDULE_WORKDATE)
AND LAST_DUE.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND LAST_DUE.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND FIRST_DUE.CALENDAR_DATE = DATES.SCHEDULE_WORKDATE
AND FIRST_DUE.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND FIRST_DUE.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND DEPTS.ORGANIZATION_ID = RES_HOURS.ORGANIZATION_ID
AND DEPTS.DEPARTMENT_ID = RES_HOURS.DEPARTMENT_ID
AND RES.ORGANIZATION_ID = RES_HOURS.ORGANIZATION_ID
AND RES.RESOURCE_ID = RES_HOURS.RESOURCE_ID
AND PARAM.ORGANIZATION_ID = RES_HOURS.ORGANIZATION_ID
AND DATES.SCHEDULE_LEVEL = 2
AND DATES.SUPPLY_DEMAND_TYPE = DECODE(DESIG.SCHEDULE_TYPE
, 1
, 1
, 2)
AND DATES.ORGANIZATION_ID = RES_HOURS.ORGANIZATION_ID
AND DATES.SCHEDULE_DESIGNATOR = DESIG.SCHEDULE_DESIGNATOR
AND DATES.INVENTORY_ITEM_ID = RES_HOURS.ASSEMBLY_ITEM_ID
AND DESIG.ORGANIZATION_ID = RES_HOURS.ORGANIZATION_ID
AND RES_HOURS.DEPARTMENT_ID IS NOT NULL
AND RES_HOURS.RESOURCE_ID IS NOT NULL GROUP BY RES_HOURS.ORGANIZATION_ID
, RES_HOURS.BILL_OF_RESOURCES
, DATES.SCHEDULE_DESIGNATOR
, RES_HOURS.RESOURCE_ID
, RES_HOURS.DEPARTMENT_ID
, RES.RESOURCE_CODE
, RES.DESCRIPTION
, DEPTS.DEPARTMENT_CODE
, DEPTS.DESCRIPTION
, RESOURCE_DATE.CALENDAR_DATE
, PARAM.ORGANIZATION_CODE

Columns