[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_deleted_rows NUMBER;
l_deleted_rows := 0;
-- Delete the Common Levels that not applay any more
DELETE FROM BSC_SYS_COM_DIM_LEVELS
WHERE TAB_ID = p_Tab_Id
AND DIM_LEVEL_INDEX >= v_Index;
l_deleted_rows := sql%rowcount;
-- Delete Records from BSC_USER_LIST_ACCESS that not apply any more
DELETE FROM BSC_USER_LIST_ACCESS
WHERE TAB_ID = p_Tab_Id
AND DIM_LEVEL_INDEX >= v_Index;
IF l_deleted_rows <> 0 THEN
--DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels - l_deleted_rows = '||l_deleted_rows);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data
);
To Check Common dimension levels when dimension level is updated
ot deleted, etc
-------------------------------------------------------------------------------------------------------------------*/
PROCEDURE Check_Common_Dim_Levels_DL(
p_Dim_Level_Id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
-- Query to get the tabs where a dimension object is used
-- as common dimension level
CURSOR c_tabs_to_check is
select TAB_ID
from BSC_SYS_COM_DIM_LEVELS
Where DIM_LEVEL_id = p_Dim_Level_Id;
Top be use when a Dimension (Dimension Group is updated)
---------------------------------------------------------------------------------*/
PROCEDURE Check_Common_Dim_Levels_by_Dim(
p_Dimension_Id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
-- Query to get the tabs where a dimension object is used
-- as common dimension level
CURSOR c_tabs_to_check is
SELECT DISTINCT B.TAB_ID
FROM BSC_KPI_DIM_GROUPS A
,BSC_TAB_INDICATORS B
WHERE A.INDICATOR = B.INDICATOR
AND A.DIM_GROUP_ID = p_Dimension_Id;
SELECT DISTINCT KL.LEVEL_TABLE_NAME, KL.DIM_LEVEL_INDEX, NVL(KL.PARENT_LEVEL_INDEX, -1), SL.DIM_LEVEL_ID
FROM BSC_TAB_INDICATORS TI,
BSC_KPIS_B KB,
BSC_KPI_DIM_LEVELS_VL KL,
BSC_SYS_DIM_LEVELS_VL SL
WHERE TI.TAB_ID = p_Tab_Id
AND KB.INDICATOR = TI.INDICATOR
AND KB.PROTOTYPE_FLAG <> 2
AND KL.INDICATOR = KB.INDICATOR
AND KL.TABLE_RELATION IS NULL
AND KL.STATUS <> 0
AND KL.DEFAULT_KEY_VALUE IS NULL
AND ( KL.DEFAULT_VALUE = 'T' OR KL.DEFAULT_VALUE LIKE 'D%')
AND KL.LEVEL_SOURCE ='BSC'
AND SL.LEVEL_TABLE_NAME = KL.LEVEL_TABLE_NAME
GROUP BY KL.LEVEL_TABLE_NAME,
KL.PARENT_LEVEL_INDEX,
KL.DIM_LEVEL_INDEX,
STATUS,
KL.TABLE_RELATION,
SL.DIM_LEVEL_ID
HAVING Count(KL.DIM_SET_ID) = v_Num_Dim_Sets_In_Tab
ORDER BY KL.DIM_LEVEL_INDEX;
SELECT KL.INDICATOR
, KL.DIM_SET_ID
, KL.DIM_LEVEL_INDEX
, SLG.DEFAULT_VALUE
, KL.PARENT_LEVEL_INDEX
, KL.DEFAULT_KEY_VALUE
FROM BSC_TAB_INDICATORS TI
, BSC_KPIS_B KB
, BSC_KPI_DIM_LEVELS_VL KL
, BSC_KPI_DIM_GROUPS KG
, BSC_SYS_DIM_LEVELS_BY_GROUP SLG
, BSC_SYS_DIM_LEVELS_VL SL
WHERE TI.TAB_ID = p_Tab_Id
AND KB.INDICATOR = TI.INDICATOR
AND KB.PROTOTYPE_FLAG <> 2
AND KL.INDICATOR = KB.INDICATOR
AND KG.INDICATOR = KL.INDICATOR
AND KG.DIM_SET_ID = KL.DIM_SET_ID
AND SLG.DIM_GROUP_ID = KG.DIM_GROUP_ID
AND SL.DIM_LEVEL_ID = SLG.DIM_LEVEL_ID
AND SL.LEVEL_TABLE_NAME = KL.LEVEL_TABLE_NAME
ORDER BY KL.INDICATOR, KL.DIM_SET_ID, KL.DIM_LEVEL_INDEX;
SELECT COUNT(SOURCE)
INTO v_Num_KPI_Default_PMF
FROM (SELECT DISTINCT KM.INDICATOR, DS.DATASET_ID, DS.SOURCE --, KM.PROTOTYPE_FLAG
FROM BSC_TAB_INDICATORS TI,
BSC_KPI_ANALYSIS_MEASURES_B KM ,
(SELECT INDICATOR, DEFAULT_VALUE
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID = 0 ) A0,
(SELECT INDICATOR, DEFAULT_VALUE
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID = 1 ) A1,
(SELECT INDICATOR, DEFAULT_VALUE
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID = 2 ) A2,
BSC_SYS_DATASETS_B DS
WHERE TI.TAB_ID = p_Tab_Id
AND KM.INDICATOR = TI.INDICATOR
AND KM.DEFAULT_VALUE = 1
AND KM.INDICATOR = A0.INDICATOR (+)
AND KM.ANALYSIS_OPTION0 = NVL(A0.DEFAULT_VALUE, 0)
AND KM.INDICATOR = A1.INDICATOR (+)
AND KM.ANALYSIS_OPTION1 = NVL(A1.DEFAULT_VALUE, 0)
AND KM.INDICATOR = A2.INDICATOR (+)
AND KM.ANALYSIS_OPTION2 = NVL(A2.DEFAULT_VALUE, 0)
AND DS.DATASET_ID = KM.DATASET_ID
)
WHERE SOURCE <> 'BSC';
SELECT COUNT (DIM_SET_ID)
INTO v_Num_Dim_Sets_In_Tab
FROM (
SELECT DISTINCT INDICATOR, DIM_SET_ID, SOURCE
FROM
( SELECT KB.INDICATOR, KDS.DIM_SET_ID, SL.DIM_LEVEL_ID, SL.SOURCE
FROM BSC_TAB_INDICATORS TI,
BSC_KPIS_B KB,
BSC_KPI_DIM_SETS_VL KDS,
BSC_KPI_DIM_GROUPS KDG,
BSC_SYS_DIM_LEVELS_BY_GROUP SLG,
BSC_SYS_DIM_LEVELS_B SL
WHERE TI.TAB_ID = p_Tab_Id
AND KB.INDICATOR = TI.INDICATOR
AND KB.PROTOTYPE_FLAG <> 2
AND KDS.INDICATOR = KB.INDICATOR
AND KDG.INDICATOR (+) = KDS.INDICATOR
AND NVL(KDG.DIM_SET_ID , KDS.DIM_SET_ID) = KDS.DIM_SET_ID
AND SLG.DIM_GROUP_ID (+) = KDG.DIM_GROUP_ID
AND SL.DIM_LEVEL_ID (+) = SLG.DIM_LEVEL_ID
ORDER BY KB.INDICATOR, KDS.DIM_SET_ID , KDG.DIM_GROUP_INDEX, SLG.DIM_LEVEL_INDEX
)
)
WHERE (SOURCE <> 'PMF' OR SOURCE IS NULL);
x_Dim_Level_Tbl.DELETE;
SELECT SL.LEVEL_TABLE_NAME,
CL.DIM_LEVEL_INDEX,
CL.PARENT_LEVEL_INDEX,
CL.DIM_LEVEL_ID,
CL.PARENT_DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVELS_B SL,
BSC_SYS_COM_DIM_LEVELS CL
WHERE CL.TAB_ID = p_Tab_Id
AND SL.DIM_LEVEL_ID (+) = CL.DIM_LEVEL_ID
ORDER BY CL.DIM_LEVEL_INDEX;
SELECT INDICATOR
FROM BSC_TAB_INDICATORS
WHERE TAB_ID = p_Tab_Id;
UPDATE BSC_KPI_DIM_LEVELS_B SET DEFAULT_VALUE = 'T'
WHERE INDICATOR = v_Indicator AND DEFAULT_VALUE Like 'D%';
UPDATE BSC_KPI_DIM_LEVELS_B SET DEFAULT_VALUE = 'D' || v_Dim_Level_Rec_R.Bsc_Level_Index
WHERE INDICATOR = v_Indicator AND LEVEL_TABLE_NAME = v_Dim_Level_Rec_R.Bsc_Level_View_Name;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = l_Kpi_Id
AND Prototype_Flag <> 2;
SELECT tab_id
FROM BSC_TAB_INDICATORS
WHERE indicator = l_Kpi_Id;