View: BSC_KPI_DEFAULTS_VL

View Text

SELECT B.TAB_ID
, B.INDICATOR
, TL.ANALYSIS_OPTION0_NAME
, TL.ANALYSIS_OPTION1_NAME
, TL.ANALYSIS_OPTION2_NAME
, DECODE ((SELECT NVL(C.EDW_CALENDAR_TYPE_ID
, 0)
FROM BSC_SYS_CALENDARS_B C
, BSC_SYS_PERIODICITIES P
WHERE P.CALENDAR_ID = C.CALENDAR_ID
AND P.PERIODICITY_ID = TO_NUMBER(SUBSTR(TL.PERIOD_NAME
, 1
, INSTR(TL.PERIOD_NAME
, '-') -1)))
, 1
, RTRIM(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1))
, DECODE((SELECT PERIODICITY_TYPE
FROM BSC_SYS_PERIODICITIES
WHERE PERIODICITY_ID = TO_NUMBER(SUBSTR(TL.PERIOD_NAME
, 1
, INSTR(TL.PERIOD_NAME
, '-') -1)))
, 1
, RTRIM(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1))
, 2
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1
, 1)
, 'MM')
, 'MONTH'))
, 3
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1
, 1)
, 'MM')
, 'MONTH'))
, 4
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||'-'|| RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1
, 1)
, 'MM')
, 'MONTH'))
, 5
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1)
, 'MM')
, 'MONTH'))
, 7
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1))
, 9
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1))
, RTRIM(TO_CHAR(TO_DATE(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, '-') +1
, 1)
, 'MM')
, 'MONTH'))||' '|| RTRIM(SUBSTR(TL.PERIOD_NAME
, INSTR(TL.PERIOD_NAME
, ';') +1)) ) ) PERIOD_NAME
, TL.SERIES_NAME
, B.FORMAT_MASK
, B.COLOR_METHOD
, B.COLOR_LEVEL1
, B.COLOR_LEVEL2
, B.COLOR_LEVEL3
, B.COLOR_LEVEL4
, B.DIM_SET_ID
, TL.DIM_LEVEL1_NAME
, TL.DIM_LEVEL2_NAME
, TL.DIM_LEVEL3_NAME
, TL.DIM_LEVEL4_NAME
, TL.DIM_LEVEL5_NAME
, TL.DIM_LEVEL6_NAME
, TL.DIM_LEVEL7_NAME
, TL.DIM_LEVEL8_NAME
, B.DIM_LEVEL1_VALUE
, B.DIM_LEVEL2_VALUE
, B.DIM_LEVEL3_VALUE
, B.DIM_LEVEL4_VALUE
, B.DIM_LEVEL5_VALUE
, B.DIM_LEVEL6_VALUE
, B.DIM_LEVEL7_VALUE
, B.DIM_LEVEL8_VALUE
, TL.DIM_LEVEL1_TEXT
, TL.DIM_LEVEL2_TEXT
, TL.DIM_LEVEL3_TEXT
, TL.DIM_LEVEL4_TEXT
, TL.DIM_LEVEL5_TEXT
, TL.DIM_LEVEL6_TEXT
, TL.DIM_LEVEL7_TEXT
, TL.DIM_LEVEL8_TEXT
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.MEASURE_SOURCE
FROM BSC_KPI_DEFAULTS_B B
, BSC_KPI_DEFAULTS_TL TL
WHERE B.TAB_ID = TL.TAB_ID
AND B.INDICATOR = TL.INDICATOR
AND TL.LANGUAGE = USERENV('LANG')