View: CRP_OTHER_AVAILABLE_HOURS

Product:	CRP - Capacity
Description:	View of daily available capacity of non-24 hour resources
Implementation/DBA Data:	APPS.CRP_OTHER_AVAILABLE_HOURS

View Text

SELECT DEPT_RES.DEPARTMENT_ID
, DEPTS.DEPARTMENT_CODE
, DEPTS.DESCRIPTION
, DEPT_RES.RESOURCE_ID
, RES.RESOURCE_CODE
, RES.DESCRIPTION
, RES.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, SHIFT_DATES.SHIFT_DATE
, SUM(((DECODE(LEAST(SHIFTS.TO_TIME
, SHIFTS.FROM_TIME)
, SHIFTS.TO_TIME
, SHIFTS.TO_TIME + 24*3600
, SHIFTS.TO_TIME) - SHIFTS.FROM_TIME) * OWNER_RES.CAPACITY_UNITS)/3600)
FROM MTL_PARAMETERS PARAM
, BOM_DEPARTMENTS DEPTS
, BOM_RESOURCES RES
, BOM_DEPARTMENT_RESOURCES DEPT_RES
, BOM_DEPARTMENT_RESOURCES OWNER_RES
, BOM_RESOURCE_SHIFTS RES_SHIFTS
, BOM_SHIFT_TIMES SHIFTS
, BOM_SHIFT_DATES SHIFT_DATES
WHERE SHIFT_DATES.SEQ_NUM IS NOT NULL
AND SHIFT_DATES.SHIFT_NUM = RES_SHIFTS.SHIFT_NUM
AND SHIFT_DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
AND SHIFT_DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND SHIFTS.CALENDAR_CODE = PARAM.CALENDAR_CODE
AND SHIFTS.SHIFT_NUM = RES_SHIFTS.SHIFT_NUM
AND PARAM.ORGANIZATION_ID = DEPTS.ORGANIZATION_ID
AND OWNER_RES.DEPARTMENT_ID = RES_SHIFTS.DEPARTMENT_ID
AND RES.RESOURCE_ID = RES_SHIFTS.RESOURCE_ID
AND OWNER_RES.AVAILABLE_24_HOURS_FLAG = 2
AND OWNER_RES.DEPARTMENT_ID = NVL(DEPT_RES.SHARE_FROM_DEPT_ID
, DEPTS.DEPARTMENT_ID)
AND OWNER_RES.RESOURCE_ID = RES.RESOURCE_ID
AND DEPT_RES.AVAILABLE_24_HOURS_FLAG = 2
AND DEPTS.DEPARTMENT_ID = DEPT_RES.DEPARTMENT_ID
AND RES.RESOURCE_ID = DEPT_RES.RESOURCE_ID
AND DEPTS.ORGANIZATION_ID = RES.ORGANIZATION_ID GROUP BY DEPT_RES.DEPARTMENT_ID
, DEPTS.DEPARTMENT_CODE
, DEPTS.DESCRIPTION
, DEPT_RES.RESOURCE_ID
, RES.RESOURCE_CODE
, RES.DESCRIPTION
, RES.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, SHIFT_DATES.SHIFT_DATE

Columns