[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator AND
prototype_flag <> 2 AND
share_flag = 2;
SELECT
a0_default,a1_default,a2_default
INTO
l_AnaOpt0_Default, l_AnaOpt1_Default, l_AnaOpt2_Default
FROM
bsc_db_color_ao_defaults_v
WHERE
indicator = p_Indicator;
SELECT
MAX(analysis_group_id)
INTO
l_Max_Group_Id
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator;
UPDATE bsc_kpi_analysis_options_b
SET
user_level0 = 2
,user_level1 = 2
WHERE indicator = p_Indicator;
UPDATE bsc_kpi_analysis_options_b
SET
user_level0 = 1
,user_level1 = 1
WHERE
indicator = p_Indicator AND
((analysis_group_id = 0 AND option_id = l_AnaOpt0_Default) OR
(analysis_group_id = 1 AND option_id = l_AnaOpt1_Default AND parent_option_id = l_AO1_Parent_Id) OR
(analysis_group_id = 2 AND option_id = l_AnaOpt2_Default AND parent_option_id = l_AO2_Parent_Id AND grandparent_option_id = l_AO2_GrandParent_Id));
UPDATE bsc_kpi_analysis_options_b
SET
user_level0 = 2
,user_level1 = 2
WHERE indicator = cd.Indicator;
UPDATE bsc_kpi_analysis_options_b
SET
user_level0 = 1
,user_level1 = 1
WHERE
indicator = cd.Indicator AND
((analysis_group_id = 0 AND option_id = l_AnaOpt0_Default) OR
(analysis_group_id = 1 AND option_id = l_AnaOpt1_Default AND parent_option_id = l_AO1_Parent_Id) OR
(analysis_group_id = 2 AND option_id = l_AnaOpt2_Default AND parent_option_id = l_AO2_Parent_Id AND grandparent_option_id = l_AO2_GrandParent_Id));
PROCEDURE Update_Change_DimSet (
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Indicator IN NUMBER
,p_Analysis_Group_Id IN NUMBER
,p_Change_Dim_Set IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
BEGIN
SAVEPOINT Update_Change_DimSet_PVT;
UPDATE
bsc_kpi_analysis_groups
SET
change_dim_set = p_Change_Dim_Set
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id;
UPDATE
bsc_kpi_analysis_groups
SET
change_dim_set = 0
WHERE
indicator = p_Indicator AND
analysis_group_id <> p_Analysis_Group_Id;
ROLLBACK TO Update_Change_DimSet_PVT;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Change_DimSet ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Change_DimSet ';
END Update_Change_DimSet;
PROCEDURE Update_Default_Flag_Value(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_dependency_flag(p_ana_grp_id NUMBER) IS
SELECT
dependency_flag
FROM
bsc_kpi_analysis_groups
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = p_ana_grp_id;
SAVEPOINT Update_Default_Flag_Val_PVT;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
WHERE
indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = l_Temp_Ana_Option_Id
WHERE
indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
analysis_group_id = l_Temp_Ana_Grp_Id;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = l_Temp_Ana_Option_Id
WHERE
indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
analysis_group_id = l_Temp_Ana_Grp_Id;
ROLLBACK TO Update_Default_Flag_Val_PVT;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Default_Flag_Val ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Default_Flag_Val ';
END Update_Default_Flag_Value;
SELECT
default_value
FROM
bsc_kpi_calculations
WHERE
indicator = p_Indicator AND
calculation_id = 2; -- YTD Default Value
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_measures_b km,
bsc_sys_dataset_calc bd
WHERE
km.indicator = p_Indicator AND
km.dataset_id = bd.dataset_id AND
km.analysis_option0 = p_AO0 AND
km.analysis_option1 = p_AO1 AND
km.analysis_option2 = p_AO2 AND
bd.disabled_calc_id = 2;
SELECT
a0_default,a1_default,a2_default
INTO
l_AO0_Default, l_AO1_Default, l_AO2_Default
FROM
bsc_db_color_ao_defaults_v
WHERE
indicator = p_Indicator;
UPDATE
bsc_kpi_calculations
SET
default_value = 0,
user_level0 = 1,
user_level1 = 1
WHERE indicator = p_Indicator;
SELECT
default_value
FROM
bsc_kpi_analysis_groups
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
SELECT
series_id,budget_flag
FROM
bsc_kpi_analysis_Measures_b
WHERE
analysis_option0 = p_AO0 AND
analysis_option1 = p_AO1 AND
analysis_option2 = p_AO2 AND
default_value = 1;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
prototype_flag <> 2 AND
share_flag = 2;
Update_Default_Flag_Value (
p_commit => l_commit
,p_Anal_Opt_Rec => p_Anal_Opt_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Default_Flag_Value (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Anal_Opt_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT
NVL(change_dim_set,0)
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id;
SELECT
default_value
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id ;
SELECT
ag.dependency_flag
FROM
bsc_kpi_analysis_groups ag
WHERe
ag.indicator = p_Indicator AND
ag.analysis_group_id = p_group_id;
SELECT DISTINCT
dim.dim_set_id, dim.dataset_id,0
BULK COLLECT INTO
x_dim_Dataset_map
FROM
bsc_db_dataset_dim_sets_v dim,
bsc_sys_datasets_b ds
WHERE
dim.indicator = p_Indicator AND
dim.dataset_id = ds.dataset_id AND
ds.source = 'BSC'
ORDER BY
dim_set_id, dataset_id;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator AND
prototype_flag <> 2 AND
share_flag = 2;
ROLLBACK TO Update_Analayis_Option_PUB;
ROLLBACK TO Update_Analayis_Option_PUB;
PROCEDURE Update_Analysis_Option_UI(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Indicator IN NUMBER
,p_Analysis_Group_Id IN NUMBER := 0
,p_Option_Id IN NUMBER := 0
,p_Parent_Option_Id IN NUMBER := 0
,p_GrandParent_Option_Id IN NUMBER := 0
,p_Dependency_Flag IN NUMBER := 0
,p_DataSet_Id IN NUMBER := NULL
,p_DimSet_Id IN NUMBER := 0
,p_Default_Flag IN NUMBER := 0
,p_Option_Name IN VARCHAR2
,p_Option_Help IN VARCHAR2
,p_Change_Dim_Set IN NUMBER := 0
,p_default_calculation IN NUMBER := NULL
,p_Create_Flow IN VARCHAR2 := FND_API.G_FALSE
,p_time_stamp IN VARCHAR2 := NULL
,p_olddim_Dataset_map IN BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_AO0 bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
SELECT
dataset_id
FROM
BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Indicator
AND analysis_option0 = p_AO0
AND analysis_option1 = p_AO1
AND analysis_option2 = p_AO2;
SELECT
dim_set_id
FROM
bsc_kpi_analysis_options_b
WHERE analysis_group_id = p_Analysis_Group_Id
AND option_id = p_Option_Id
AND parent_option_id = p_Parent_Option_Id
AND grandparent_option_id = p_GrandParent_Option_Id;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator AND
prototype_flag <> 2 AND
share_flag = 2;
SAVEPOINT Update_Analayis_Option_PUB;
SELECT indicator_type,config_type
INTO l_indicator_type,l_config_type
FROM bsc_kpis_b
WHERE indicator = p_Indicator;
BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options -- This will cascade to the shared
( p_commit => l_commit
, p_kpi_id => p_Indicator
, p_data_source => l_Measure_Source
, p_analysis_group_id => p_Analysis_Group_Id
, p_analysis_option_id0 => p_Option_Id
, p_analysis_option_id1 => p_Parent_Option_Id
, p_analysis_option_id2 => p_GrandParent_Option_Id
, p_series_id => 0
, p_data_set_id => p_DataSet_Id
, p_dim_set_id => l_DimSet_Id
, p_option0_Name => p_Option_Name
, p_option1_Name => NULL
, p_option2_Name => NULL
, p_measure_short_name => NULL
, p_dim_obj_short_names => NULL
, p_default_short_names => NULL
, p_view_by_name => NULL
, p_measure_name => p_Option_Name
, p_measure_help => p_Option_Help
, p_default_value => NULL
, p_time_stamp => NULL
, p_update_ana_opt => TRUE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Bsc_Analysis_Option_Pvt.Update_Analysis_Measures (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_Msg_Data
);
Bsc_Analysis_Option_Pvt.Update_Analysis_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
,p_data_source => l_Measure_Source
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_Msg_Data
);
Bsc_Analysis_Option_Pvt.Update_Analysis_Measures (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_Msg_Data
);
Bsc_Analysis_Option_Pvt.Update_Analysis_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
,p_data_source => l_Measure_Source
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_Msg_Data
);
BSC_DESIGNER_PVT.Deflt_Update_SN_FM_CM(x_indicator => p_Indicator);
Update_Change_DimSet (
p_commit => FND_API.G_FALSE
,p_Indicator => p_Indicator
,p_Analysis_Group_Id => p_Analysis_Group_Id
,p_Change_Dim_Set => p_Change_Dim_Set
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
p_commit => l_commit
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Change_DimSet (
p_commit => FND_API.G_FALSE
,p_Indicator => cd.Indicator
,p_Analysis_Group_Id => p_Analysis_Group_Id
,p_Change_Dim_Set => p_Change_Dim_Set
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
p_commit => l_commit
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => p_Indicator);
BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => cd.indicator );
ROLLBACK TO Update_Analayis_Option_PUB;
ROLLBACK TO Update_Analayis_Option_PUB;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Option_UI ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Option_UI ';
END Update_Analysis_Option_UI;
PROCEDURE Delete_Mind_Options(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
,p_Dependency01 IN NUMBER
,p_Dependency12 IN NUMBER
,p_Initial_Group_Id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Ana_Opt_Count NUMBER := 0;
l_DeleteChildren BOOLEAN := FALSE;
l_Deletegrandchildren BOOLEAN := FALSE;
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_options_b
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
SELECT
option_id
FROM
bsc_kpi_analysis_options_b
WHERE
indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
analysis_group_id = 1 AND
parent_option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
SAVEPOINT Delete_Mind_Options_PVT;
l_sql := 'DELETE FROM bsc_kpi_analysis_options_b ' || l_criteria;
l_sql := 'DELETE FROM bsc_kpi_analysis_options_tl ' || l_criteria;
/*Delete the child analysis options recursively if the following conditions satisty
1. If the current analysis group has a dependent relationship with the child group
then delete the children
2. If the current analysis option is the last analysis option in that particular
group , the children should be deleted even if it is an independent relationship*/
IF (l_Anal_Opt_Rec.Bsc_Analysis_Group_Id = 0 AND (p_Dependency01 = 1 OR l_Ana_Opt_Count = 0) ) THEN
l_Next_Group_Id := 1;
l_DeleteChildren := TRUE;
l_DeleteGrandChildren := TRUE;
l_DeleteChildren := TRUE;
IF l_DeleteGrandChildren THEN
FOR cd in c_Child_Options LOOP
l_Par_Opt_Rec.Bsc_Analysis_Group_Id := l_GrandChild_Group_Id;
Delete_Mind_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Par_Opt_Rec
,p_Dependency01 => p_Dependency01
,p_Dependency12 => p_Dependency12
,p_Initial_Group_Id => p_Initial_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF l_DeleteChildren = TRUE THEN
l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_Next_Group_Id;
Delete_Mind_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Anal_Opt_Rec
,p_Dependency01 => p_Dependency01
,p_Dependency12 => p_Dependency12
,p_Initial_Group_Id => p_Initial_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Delete_Mind_Options_PVT;
x_msg_data := x_msg_data||' ->BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Options ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Options ';
END Delete_Mind_Options;
PROCEDURE Delete_Mind_Data(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
,p_Parent_Group_Id IN NUMBER
,p_Grandparent_Group_Id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
TYPE cursor_type IS REF CURSOR;
SAVEPOINT Delete_Mind_Data_PVT;
l_sql := 'SELECT COUNT(1) FROM bsc_kpi_analysis_options_b WHERE indicator = ' || l_Anal_Opt_Rec.Bsc_Kpi_Id ;
l_sql := 'DELETE FROM bsc_kpi_analysis_measures_b ' || l_criteria;
l_sql := 'DELETE FROM bsc_kpi_analysis_measures_tl ' || l_criteria;
l_sql := 'DELETE FROM bsc_kpi_subtitles_tl ' || l_criteria;
ROLLBACK TO Delete_Mind_Data_PVT;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Data ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Data ';
END Delete_Mind_Data;
SELECT
MAX(analysis_group_id)
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
l_options_sql := 'SELECT option_id FROM bsc_kpi_analysis_options_b ';
l_sql := 'UPDATE bsc_kpi_analysis_measures_b SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
l_sql := 'UPDATE bsc_kpi_analysis_measures_tl SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
l_sql := 'UPDATE bsc_kpi_subtitles_tl SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
l_sql := 'UPDATE bsc_kpi_analysis_options_b SET option_id='|| l_cur_index;
l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET option_id='|| l_cur_index;
l_sql := 'UPDATE bsc_kpi_analysis_options_b SET parent_option_id = '|| l_cur_index ;
l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET parent_option_id = '|| l_cur_index ;
l_sql := 'UPDATE bsc_kpi_analysis_options_b SET grandparent_option_id = '|| l_cur_index ;
l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET grandparent_option_id = '|| l_cur_index ;
PROCEDURE Update_Analysis_Opt_Count (
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Indicator IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Count NUMBER := 0;
SELECT MAX(option_id)
FROM
bsc_kpi_analysis_options_b
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id;
SELECT max(analysis_group_id)
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator;
SAVEPOINT Update_Ana_Opt_Count_PVT;
UPDATE
bsc_kpi_analysis_groups
SET
num_of_options = l_Count
WHERE
indicator = p_Indicator
AND analysis_group_id = i;
ROLLBACK TO Update_Ana_Opt_Count_PVT;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Opt_Count ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Opt_Count ';
END Update_Analysis_Opt_Count;
SELECT
dependency_flag
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id;
PROCEDURE Delete_Analysis_Option_Wrap (
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
,p_cascade_shared BOOLEAN := FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
prototype_flag <> 2 AND
share_flag = 2;
SAVEPOINT BscObjDeleteAnaOptWrap;
Delete_Mind_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
,p_Dependency01 => l_Dependency01
,p_Dependency12 => l_Dependency12
,p_Initial_Group_Id => p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Delete_Mind_Data (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
,p_Parent_Group_Id => l_Parent_Group_Id
,p_Grandparent_Group_Id => l_GrandParent_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Analysis_Opt_Count (
p_commit => l_commit
,p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Delete_Mind_Options (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
,p_Dependency01 => l_Dependency01
,p_Dependency12 => l_Dependency12
,p_Initial_Group_Id => p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Delete_Mind_Data (
p_commit => l_commit
,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
,p_Parent_Group_Id => l_Parent_Group_Id
,p_Grandparent_Group_Id => l_GrandParent_Group_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Analysis_Opt_Count (
p_commit => l_commit
,p_Indicator => l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO BscObjDeleteAnaOptWrap;
ROLLBACK TO BscObjDeleteAnaOptWrap;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_Wrap ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_Wrap ';
END Delete_Analysis_Option_Wrap;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = l_new_Default
WHERE
indicator = p_Indicator
AND analysis_Group_Id = p_Analysis_Group_Id;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = 0
WHERE
indicator = p_Indicator
AND analysis_Group_Id = 1;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = 0
WHERE
indicator = p_Indicator
AND analysis_Group_Id = 2;
UPDATE
bsc_kpi_analysis_groups
SET
default_value = 0
WHERE
indicator = p_Indicator
AND analysis_Group_Id = 2;
SELECT
MAX(analysis_group_id)
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator;
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_options_b
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Group_Id;
SELECT
change_dim_set
INTO
l_Change_Dim_Set
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = l_Cur_Group;
DELETE FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = l_Cur_Group;
UPDATE
bsc_kpi_analysis_groups
SET
change_dim_set = 1
WHERE
indicator = p_Indicator AND
analysis_group_id = 0;
PROCEDURE Delete_Analysis_Option_UI(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Indicator IN NUMBER
,p_Analysis_Group_Id IN NUMBER := 0
,p_Option_Id IN NUMBER := 0
,p_Parent_Option_Id IN NUMBER := 0
,p_GrandParent_Option_Id IN NUMBER := 0
,p_time_stamp IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator AND
prototype_flag <> 2 AND
share_flag = 2;
SELECT
kpi_dim.dim_set_id
FROM
bsc_kpis_b kpi,
bsc_kpi_dim_groups kpi_dim,
bsc_sys_dim_groups_vl sys_dim,
bsc_kpi_analysis_options_b kpi_opt
WHERE
kpi.indicator = p_Indicator AND
kpi.short_name IS NULL AND
kpi_dim.indicator = kpi.indicator AND
sys_dim.dim_group_id = kpi_dim.dim_group_id AND
kpi_opt.indicator = kpi.indicator AND
kpi_opt.dim_set_id = kpi_dim.dim_set_id AND
bsc_bis_dimension_pub.get_dimension_source(sys_dim.short_name) = BSC_UTILITY.c_PMF;
SAVEPOINT Delete_Analysis_Opt_UI_PVT;
Delete_Analysis_Option_Wrap (
p_commit => p_commit
,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
,p_cascade_shared => TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT column_value dim_set_id
BULK COLLECT
INTO l_Removed_Dim_Set_Ids
FROM
(SELECT
t.column_value
FROM
TABLE(CAST(l_olddim_set_ids AS FND_TABLE_OF_NUMBER)) t
MINUS
SELECT
t.column_value
FROM
TABLE(CAST(l_newdim_set_ids AS FND_TABLE_OF_NUMBER)) t );
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_Indicator
, p_dim_set_id => l_Removed_Dim_Set_Ids(i)
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
p_commit => p_commit
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => cd.Indicator
, p_dim_set_id => l_Removed_Dim_Set_Ids(i)
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
p_commit => p_commit
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Delete_Analysis_Opt_UI_PVT;
ROLLBACK TO Delete_Analysis_Opt_UI_PVT;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_UI ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_UI ';
END Delete_Analysis_Option_UI;
INSERT INTO bsc_kpi_analysis_groups (
indicator,
analysis_group_id,
num_of_options,
dependency_flag,
parent_analysis_id,
change_dim_set,
default_value,
short_name
) VALUES(
p_Indicator
,p_Analysis_Group_Id
,p_Num_Of_Options
,p_Dependency_Flag
,p_Parent_Analysis_Id
,p_Change_Dim_Set
,p_Default_Value
,p_Short_Name
);
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_Indicator AND
analysis_option0 = p_Analysis_Option0 AND
analysis_option1 = p_Analysis_Option1 AND
analysis_option2 = p_Analysis_Option2;
l_sql := 'SELECT option_id FROM bsc_kpi_analysis_options_b WHERE indicator = :1 ';
SELECT dependency_flag
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Ana_Grp_Id;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator AND
prototype_flag <> 2 AND
share_flag = 2;
SELECT DISTINCT
dim.dim_set_id, dim.dataset_id,0
BULK COLLECT INTO
l_olddim_Dataset_map
FROM
bsc_db_dataset_dim_sets_v dim,
bsc_sys_datasets_b ds
WHERE
dim.indicator = p_Indicator AND
dim.dataset_id = ds.dataset_id AND
ds.source = 'BSC'
ORDER BY
dim_set_id, dataset_id;
SELECT COUNT(1) INTO l_Count
FROM bsc_kpi_analysis_groups
WHERE indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id;
SELECT source
INTO l_measure_source
FROM bsc_sys_datasets_vl
WHERE dataset_id = p_Dataset_id;
Update_Analysis_Opt_Count (
p_Indicator => p_Indicator
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Analysis_Opt_Count (
p_Indicator => cd.indicator
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Analysis_Option_UI(
p_Indicator => p_Indicator
,p_Analysis_Group_Id => p_Analysis_Group_Id
,p_Option_Id => p_Option_Id
,p_Parent_Option_Id => p_Parent_Option_Id
,p_Grandparent_Option_Id => p_Grandparent_Option_Id
,p_Dependency_Flag => p_Dependency_Flag
,p_DataSet_Id => p_DataSet_Id
,p_DimSet_Id => p_DimSet_Id
,p_Default_Flag => p_Default_Flag
,p_Option_Name => p_Option_Name
,p_Option_Help => p_Option_Help
,p_Change_Dim_Set => p_Change_Dim_Set
,p_default_calculation=> p_default_calculation
,p_Create_Flow => FND_API.G_TRUE
,p_time_stamp => p_time_stamp
,p_olddim_Dataset_map => l_olddim_Dataset_map
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PROCEDURE Val_Delete_Analysis_Option(
p_Indicator IN NUMBER
,p_Analysis_Group_Id IN NUMBER := 0
,p_Option_Id IN NUMBER := 0
,p_Parent_Option_Id IN NUMBER := 0
,p_GrandParent_Option_Id IN NUMBER := 0
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Grp_Count NUMBER := 0;
SELECT
num_of_options
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator AND
Analysis_Group_Id = p_Ana_Grp;
SELECT
MAX(analysis_group_id)
FROM
bsc_kpi_analysis_groups
WHERE
indicator = p_Indicator;
FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AG_DEPEN');
l_sql := 'SELECT COUNT(1) FROM bsc_kpi_analysis_options_b WHERE indicator = ' || p_Indicator ;
l_sql := 'SELECT kpi_measure_id FROM bsc_kpi_measure_weights WHERE indicator = ' || p_Indicator;
l_sql := l_sql || ' AND weight > 0 INTERSECT SELECT kpi_measure_id FROM bsc_kpi_analysis_measures_b ';
l_sql := ' SELECT full_name FROM bsc_oaf_analysys_opt_comb_v '|| l_criteria;
x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Validate_Delete_Analysis_Option ';
x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Validate_Delete_Analysis_Option ';
END Val_Delete_Analysis_Option;
SELECT
MAX(option_id) AS MAX
FROM
bsc_kpi_analysis_options_b
WHERE
indicator = p_Indicator
AND analysis_group_id = p_Analysis_Group_Id
AND parent_option_id = p_Parent
AND grandparent_option_id = p_GrandParent
GROUP BY
indicator,analysis_group_id,parent_option_id,grandparent_option_id;
SELECT
indicator_type
FROM
bsc_kpis_b
WHERE indicator = p_Indicator;
SELECT
dataset_id
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_Indicator AND
analysis_option0 = p_AO0 AND
analysis_option1 = p_AO1 AND
analysis_option2 = p_AO2 AND
series_id = 0;
SELECT
bk.bm_group_id
FROM
bsc_kpis_b bk, bsc_sys_bm_groups bg,bsc_sys_benchmarks_b be
WHERE
bk.bm_group_id = bg.bm_group_id AND
be.bm_id = bg.bm_id AND
be.data_type = 1 AND
bk.indicator = p_Indicator;
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_indicator AND
analysis_option0 = p_AO0 AND
analysis_option1 = p_AO1 AND
analysis_option2 = p_AO2 AND
default_value = 1 AND
budget_flag = 1;
SELECT
a0_default,a1_default,a2_default
INTO
l_AO0_Default, l_AO1_Default, l_AO2_Default
FROM
bsc_db_color_ao_defaults_v
WHERE
indicator = p_Indicator;
SELECT
indicator_type,config_type
FROM
bsc_kpis_b
WHERE
indicator = p_Indicator;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
indicator = p_Indicator OR
(source_indicator = p_Indicator AND prototype_flag <> 2);
UPDATE bsc_kpis_b
SET apply_color_flag = l_apply_color
WHERE indicator = cd.indicator;
SELECT
ag.dependency_flag
FROM
bsc_kpi_analysis_groups ag
WHERe
ag.indicator = p_Indicator AND
ag.analysis_group_id = p_group_id;
SELECT
name
FROM
bsc_kpi_analysis_options_vl o
WHERE
o.indicator = p_Indicator AND
o.analysis_group_id = p_Group_Id AND
o.option_id = l_option_id AND
o.parent_option_id = l_parent_id AND
o.grandparent_option_id = l_grandParentId;
SELECT
ag.dependency_flag
FROM
bsc_kpi_analysis_groups ag
WHERe
ag.indicator = p_Indicator AND
ag.analysis_group_id = p_group_id;
SELECT
COUNT(1)
FROM
bsc_kpi_analysis_options_vl o
WHERE
o.indicator = p_Indicator AND
o.analysis_group_id = p_Group_Id AND
o.option_id = l_option_id AND
o.parent_option_id = l_parent_id AND
o.grandparent_option_id = l_grandParentId;
SELECT
dim_set_id
FROM
bsc_kpi_analysis_options_b
WHERE
indicator = p_Indicator AND
analysis_group_id = p_Analysis_Group_Id AND
option_id = p_Option_Id AND
parent_option_id = p_Parent_Id AND
grandparent_option_id = p_GrandParentId;
SELECT
indicator_type
FROM
bsc_kpis_b
WHERE indicator = p_Indicator;
SELECT
kpi_measure_id
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_Indicator AND
analysis_option0 = p_AO0 AND
analysis_option1 = p_AO1 AND
analysis_option2 = p_AO2 AND
series_id = 0;
SELECT
dataset_id
INTO
l_Property_Value
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_Indicator
AND kpi_measure_id = l_kpi_measure_id;
SELECT
default_calculation
INTO
l_Property_Value
FROM
bsc_kpi_measure_props
WHERE
indicator = p_Indicator
AND kpi_measure_id = l_kpi_measure_id;