SELECT KP.TAB_ID AS TAB_ID ,
KP.INDICATOR AS INDICATOR ,
DM1.OPERATION||'('||NVL(BSC_APPS.GET_PROPERTY_VALUE(DM1.S_COLOR_FORMULA , 'PFORMULASOURCE') , DM1.MEASURE_COL)||')'||DS.OPERATION||DECODE(DS.MEASURE_ID2 , NULL , NULL , DM2.OPERATION||'('||NVL(BSC_APPS.GET_PROPERTY_VALUE(DM2.S_COLOR_FORMULA , 'PFORMULASOURCE') , DM2.MEASURE_COL)||')') AS MEASURE_FORMULA ,
DS.COLOR_METHOD AS COLOR_METHOD ,
DS.FORMAT_ID AS FORMAT_ID
FROM
BSC_TAB_INDICATORS KP ,
BSC_DB_COLOR_KPI_DEFAULTS_V KD ,
BSC_SYS_DATASETS_B DS ,
BSC_SYS_MEASURES DM1 ,
BSC_SYS_MEASURES DM2 ,
BSC_KPIS_B K
WHERE KP.INDICATOR = KD.INDICATOR
AND KD.DATASET_ID = DS.DATASET_ID
AND DS.MEASURE_ID1 = DM1.MEASURE_ID
AND NVL(DS.MEASURE_ID2 , DS.MEASURE_ID1) = DM2.MEASURE_ID
AND KP.INDICATOR = K.INDICATOR
AND NOT (K.INDICATOR_TYPE = 1 AND K.CONFIG_TYPE = 7)
UNION
SELECT KP.TAB_ID AS TAB_ID ,
KP.INDICATOR AS INDICATOR ,
DM.S_COLOR_FORMULA AS MEASURE_FORMULA ,
TN.COLOR_METHOD AS COLOR_METHOD ,
TN.FORMAT_ID AS FORMAT_ID
FROM
BSC_TAB_INDICATORS KP ,
BSC_SYS_MEASURES DM ,
BSC_KPI_PROPERTIES P1 ,
BSC_KPI_PROPERTIES P2 ,
BSC_KPI_TREE_NODES_B TN ,
BSC_KPIS_B K
WHERE KP.INDICATOR = P1.INDICATOR
AND P1.PROPERTY_CODE = 'S_MEASURE_ID'
AND DM.MEASURE_ID = P1.PROPERTY_VALUE
AND KP.INDICATOR = TN.INDICATOR
AND KP.INDICATOR = P2.INDICATOR
AND P2.PROPERTY_CODE = 'S_NODE_ID'
AND TN.NODE_ID = P2.PROPERTY_VALUE
AND KP.INDICATOR = K.INDICATOR
AND K.INDICATOR_TYPE = 1 AND K.CONFIG_TYPE = 7