eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.CSP_NEXT_OPEN_V

Source

View Text - Preformatted


SELECT jcb.calendar_id, MIN(jcsc.begin_time+ ( TRUNC(sysdate+flvv.lookup_code)-TRUNC(jcsc.begin_time))), MIN(jcsc.end_time + ( TRUNC(sysdate+flvv.lookup_code)-TRUNC(jcsc.end_time))) FROM jtf_cal_shift_constructs jcsc, jtf_cal_shifts_b jcsb, jtf_cal_shift_assign jcsa, jtf_calendars_b jcb, fnd_lookup_values_vl flvv WHERE jcsa.calendar_id = jcb.calendar_id AND jcsb.shift_id = jcsa.shift_id AND jcsc.shift_id = jcsa.shift_id AND flvv.lookup_type = 'NUMBERS' AND flvv.lookup_code BETWEEN 1 AND 15 AND TRUNC(sysdate +flvv.lookup_code) BETWEEN TRUNC(NVL(jcb.start_date_active,sysdate+flvv.lookup_code)) AND TRUNC(NVL(jcb.end_date_active,sysdate+flvv.lookup_code)) AND TRUNC(sysdate +flvv.lookup_code) BETWEEN TRUNC(NVL(jcsa.shift_start_date,sysdate+flvv.lookup_code)) AND TRUNC(NVL(jcsa.shift_end_date,sysdate+flvv.lookup_code)) AND TRUNC(sysdate +flvv.lookup_code) BETWEEN TRUNC(NVL(jcsb.start_date_active,sysdate+flvv.lookup_code)) AND TRUNC(NVL(jcsb.end_date_active,sysdate+flvv.lookup_code)) AND TRUNC(sysdate +flvv.lookup_code) BETWEEN TRUNC(NVL(jcsc.start_date_active,sysdate+flvv.lookup_code)) AND TRUNC(NVL(jcsc.end_date_active,sysdate+flvv.lookup_code)) AND jcsc.unit_of_time_value = TO_CHAR(sysdate+flvv.lookup_code,'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 ) GROUP BY jcb.calendar_id

View Text - HTML Formatted

SELECT JCB.CALENDAR_ID
, MIN(JCSC.BEGIN_TIME+ ( TRUNC(SYSDATE+FLVV.LOOKUP_CODE)-TRUNC(JCSC.BEGIN_TIME)))
, MIN(JCSC.END_TIME + ( TRUNC(SYSDATE+FLVV.LOOKUP_CODE)-TRUNC(JCSC.END_TIME)))
FROM JTF_CAL_SHIFT_CONSTRUCTS JCSC
, JTF_CAL_SHIFTS_B JCSB
, JTF_CAL_SHIFT_ASSIGN JCSA
, JTF_CALENDARS_B JCB
, FND_LOOKUP_VALUES_VL FLVV
WHERE JCSA.CALENDAR_ID = JCB.CALENDAR_ID
AND JCSB.SHIFT_ID = JCSA.SHIFT_ID
AND JCSC.SHIFT_ID = JCSA.SHIFT_ID
AND FLVV.LOOKUP_TYPE = 'NUMBERS'
AND FLVV.LOOKUP_CODE BETWEEN 1
AND 15
AND TRUNC(SYSDATE +FLVV.LOOKUP_CODE) BETWEEN TRUNC(NVL(JCB.START_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(NVL(JCB.END_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(SYSDATE +FLVV.LOOKUP_CODE) BETWEEN TRUNC(NVL(JCSA.SHIFT_START_DATE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(NVL(JCSA.SHIFT_END_DATE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(SYSDATE +FLVV.LOOKUP_CODE) BETWEEN TRUNC(NVL(JCSB.START_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(NVL(JCSB.END_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(SYSDATE +FLVV.LOOKUP_CODE) BETWEEN TRUNC(NVL(JCSC.START_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND TRUNC(NVL(JCSC.END_DATE_ACTIVE
, SYSDATE+FLVV.LOOKUP_CODE))
AND JCSC.UNIT_OF_TIME_VALUE = TO_CHAR(SYSDATE+FLVV.LOOKUP_CODE
, '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 ) GROUP BY JCB.CALENDAR_ID