View: BSC_SYS_PERIODS_VL

Product:	BSC - Balanced Scorecard
Description:	View of BSC_SYS_PERIODS_TL.
Implementation/DBA Data:	APPS.BSC_SYS_PERIODS_VL

View Text

SELECT TL.YEAR
, TL.PERIODICITY_ID
, TL.PERIOD_ID
, TL.MONTH
, 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))) NAME
, 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))) SHORT_NAME
FROM BSC_SYS_PERIODS_TL TL
, BSC_SYS_PERIODICITIES P
WHERE TL.PERIODICITY_ID = P.PERIODICITY_ID
AND TL.LANGUAGE = USERENV('LANG') UNION SELECT YEAR
, PERIODICITY_ID
, PERIOD_ID
, 1 AS MONTH
, NAME
, SHORT_NAME
FROM BSC_EDW_PERIODS_TL
WHERE LANGUAGE = USERENV('LANG')

Columns