VIEW: APPS.BSC_SYS_PERIODS_VL

Source

View Text - Preformatted

SELECT TL.YEAR, TL.PERIODICITY_ID, TL.PERIOD_ID, TL.MONTH, CASE WHEN C.EDW_CALENDAR_TYPE_ID = 1 THEN TL.NAME ELSE DECODE(P.PERIODICITY_TYPE, 2, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), 'Month')), 3, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), 'Month')), 4, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), 'Month')), 5, RTRIM(TO_CHAR(TO_DATE(TL.NAME, 'MM'), 'Month')), 7, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||' '|| RTRIM(SUBSTR(NAME, INSTR(TL.NAME, ';') + 1)), 9, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||' '|| RTRIM(SUBSTR(NAME, INSTR(TL.NAME, ';') + 1)), RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), 'Month'))||' '|| RTRIM(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1))) END NAME, CASE WHEN C.EDW_CALENDAR_TYPE_ID = 1 THEN TL.NAME ELSE DECODE(P.PERIODICITY_TYPE, 2, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon')))), 3, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon')))), 4, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, INSTR(TL.NAME, ';') + 1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon')))), 5, RTRIM(TO_CHAR(TO_DATE(TL.NAME, 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon')))), 7, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))|| RTRIM(SUBSTR(NAME, INSTR(TL.NAME, ';') + 1)), 9, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME, 1, INSTR(TL.NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))|| RTRIM(SUBSTR(NAME, INSTR(TL.NAME, ';') + 1)), RTRIM(TO_CHAR(TO_DATE(SUBSTR(NAME, 1,INSTR(NAME, ';') -1), 'MM'), (decode(userenv('lang'),'JA','Month','ZHT','Month','ZHS','Month','Mon'))))|| RTRIM(SUBSTR(NAME, INSTR(NAME, ';') + 1))) END SHORT_NAME FROM BSC_SYS_PERIODS_TL TL, BSC_SYS_PERIODICITIES P, BSC_SYS_CALENDARS_B C WHERE TL.PERIODICITY_ID = P.PERIODICITY_ID AND TL.LANGUAGE = USERENV('LANG') AND P.CALENDAR_ID = C.CALENDAR_ID
   

View Text - HTML Formatted

SELECT TL.YEAR
, TL.PERIODICITY_ID
, TL.PERIOD_ID
, TL.MONTH
, CASE WHEN C.EDW_CALENDAR_TYPE_ID = 1 THEN TL.NAME ELSE DECODE(P.PERIODICITY_TYPE
, 2
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, 'MONTH'))
, 3
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, 'MONTH'))
, 4
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, 'MONTH'))
, 5
, RTRIM(TO_CHAR(TO_DATE(TL.NAME
, 'MM')
, 'MONTH'))
, 7
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(NAME
, INSTR(TL.NAME
, ';') + 1))
, 9
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(NAME
, INSTR(TL.NAME
, ';') + 1))
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1))) END NAME
, CASE WHEN C.EDW_CALENDAR_TYPE_ID = 1 THEN TL.NAME ELSE DECODE(P.PERIODICITY_TYPE
, 2
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))
, 3
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))
, 4
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, INSTR(TL.NAME
, ';') + 1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))
, 5
, RTRIM(TO_CHAR(TO_DATE(TL.NAME
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))
, 7
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))|| RTRIM(SUBSTR(NAME
, INSTR(TL.NAME
, ';') + 1))
, 9
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.NAME
, 1
, INSTR(TL.NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))|| RTRIM(SUBSTR(NAME
, INSTR(TL.NAME
, ';') + 1))
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(NAME
, 1
, INSTR(NAME
, ';') -1)
, 'MM')
, (DECODE(USERENV('LANG')
, 'JA'
, 'MONTH'
, 'ZHT'
, 'MONTH'
, 'ZHS'
, 'MONTH'
, 'MON'))))|| RTRIM(SUBSTR(NAME
, INSTR(NAME
, ';') + 1))) END SHORT_NAME
FROM BSC_SYS_PERIODS_TL TL
, BSC_SYS_PERIODICITIES P
, BSC_SYS_CALENDARS_B C
WHERE TL.PERIODICITY_ID = P.PERIODICITY_ID
AND TL.LANGUAGE = USERENV('LANG')
AND P.CALENDAR_ID = C.CALENDAR_ID