VIEW: APPS.BOM_CALENDAR_WEEKS_VIEW

Source

View Text - Preformatted

SELECT calendar_code, exception_set_id, to_number(to_char(calendar_date, 'YYYY')), substr(to_char(calendar_date, 'MON'),1,3), to_number(to_char(calendar_date, 'MM')), trunc((to_char(calendar_date, 'DD') + mod((to_char(calendar_date,'J') + 2 - to_char(calendar_date, 'DD')),7) + 6)/7), sum(decode(mod(to_char(calendar_date,'J')+1,7), 0, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 0, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 1, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 1, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 2, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 2, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 3, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 3, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 4, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 4, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 5, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 5, decode(seq_num,'','*',' '))), sum(decode(mod(to_char(calendar_date,'J')+1,7), 6, to_number(to_char(calendar_date,'DD')), NULL)), max(decode(mod(to_char(calendar_date,'J')+1,7), 6, decode(seq_num,'','*',' '))) FROM BOM_CALENDAR_DATES group by calendar_code, exception_set_id, to_number(to_char(calendar_date, 'YYYY')), substr(to_char(calendar_date, 'MON'),1,3), to_number(to_char(calendar_date, 'MM')), trunc((to_char(calendar_date, 'DD') + mod((to_char(calendar_date,'J') + 2 - to_char(calendar_date, 'DD')),7) + 6)/7)

View Text - HTML Formatted

SELECT CALENDAR_CODE
, EXCEPTION_SET_ID
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'YYYY'))
, SUBSTR(TO_CHAR(CALENDAR_DATE
, 'MON')
, 1
, 3)
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'MM'))
, TRUNC((TO_CHAR(CALENDAR_DATE
, 'DD') + MOD((TO_CHAR(CALENDAR_DATE
, 'J') + 2 - TO_CHAR(CALENDAR_DATE
, 'DD'))
, 7) + 6)/7)
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 0
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 0
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 1
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 1
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 2
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 2
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 3
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 3
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 4
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 4
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 5
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 5
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
, SUM(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 6
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'DD'))
, NULL))
, MAX(DECODE(MOD(TO_CHAR(CALENDAR_DATE
, 'J')+1
, 7)
, 6
, DECODE(SEQ_NUM
, ''
, '*'
, ' ')))
FROM BOM_CALENDAR_DATES GROUP BY CALENDAR_CODE
, EXCEPTION_SET_ID
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'YYYY'))
, SUBSTR(TO_CHAR(CALENDAR_DATE
, 'MON')
, 1
, 3)
, TO_NUMBER(TO_CHAR(CALENDAR_DATE
, 'MM'))
, TRUNC((TO_CHAR(CALENDAR_DATE
, 'DD') + MOD((TO_CHAR(CALENDAR_DATE
, 'J') + 2 - TO_CHAR(CALENDAR_DATE
, 'DD'))
, 7) + 6)/7)