VIEW: APPS.CSP_OPEN_CLOSED_V

Source

View Text - Preformatted

SELECT jcb.calendar_id, jcsc.begin_time+ ( TRUNC(sysdate)-TRUNC(jcsc.begin_time)) start_time, jcsc.end_time + ( TRUNC(sysdate)-TRUNC(jcsc.end_time)) end_time FROM jtf_cal_shift_constructs jcsc, jtf_cal_shifts_b jcsb, jtf_cal_shift_assign jcsa, jtf_calendars_b jcb WHERE jcsa.calendar_id = jcb.calendar_id AND jcsb.shift_id = jcsa.shift_id AND jcsc.shift_id = jcsa.shift_id AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jcb.start_date_active,sysdate)) AND TRUNC(NVL(jcb.end_date_active,sysdate)) AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jcsa.shift_start_date,sysdate)) AND TRUNC(NVL(jcsa.shift_end_date,sysdate)) AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jcsb.start_date_active,sysdate)) AND TRUNC(NVL(jcsb.end_date_active,sysdate)) AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jcsc.start_date_active,sysdate)) AND TRUNC(NVL(jcsc.end_date_active,sysdate)) AND jcsc.unit_of_time_value = TO_CHAR(sysdate,'D') AND NOT EXISTS (SELECT 'x' FROM jtf_cal_exception_assign jcea, jtf_cal_exceptions_b jceb WHERE jcea.calendar_id = jcb.calendar_id AND sysdate BETWEEN jceb.start_date_time AND jceb.end_date_time AND sysdate BETWEEN jcea.start_date_active AND jcea.end_date_active AND jcea.exception_id = jceb.exception_id )

View Text - HTML Formatted

SELECT JCB.CALENDAR_ID
, JCSC.BEGIN_TIME+ ( TRUNC(SYSDATE)-TRUNC(JCSC.BEGIN_TIME)) START_TIME
, JCSC.END_TIME + ( TRUNC(SYSDATE)-TRUNC(JCSC.END_TIME)) END_TIME
FROM JTF_CAL_SHIFT_CONSTRUCTS JCSC
, JTF_CAL_SHIFTS_B JCSB
, JTF_CAL_SHIFT_ASSIGN JCSA
, JTF_CALENDARS_B JCB
WHERE JCSA.CALENDAR_ID = JCB.CALENDAR_ID
AND JCSB.SHIFT_ID = JCSA.SHIFT_ID
AND JCSC.SHIFT_ID = JCSA.SHIFT_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(JCB.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(JCB.END_DATE_ACTIVE
, SYSDATE))
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(JCSA.SHIFT_START_DATE
, SYSDATE))
AND TRUNC(NVL(JCSA.SHIFT_END_DATE
, SYSDATE))
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(JCSB.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(JCSB.END_DATE_ACTIVE
, SYSDATE))
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(JCSC.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(JCSC.END_DATE_ACTIVE
, SYSDATE))
AND JCSC.UNIT_OF_TIME_VALUE = TO_CHAR(SYSDATE
, 'D')
AND NOT EXISTS (SELECT 'X'
FROM JTF_CAL_EXCEPTION_ASSIGN JCEA
, JTF_CAL_EXCEPTIONS_B JCEB
WHERE JCEA.CALENDAR_ID = JCB.CALENDAR_ID
AND SYSDATE BETWEEN JCEB.START_DATE_TIME
AND JCEB.END_DATE_TIME
AND SYSDATE BETWEEN JCEA.START_DATE_ACTIVE
AND JCEA.END_DATE_ACTIVE
AND JCEA.EXCEPTION_ID = JCEB.EXCEPTION_ID )