[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| BSC_KPI_ANALYSIS_OPTIONS_VL in select statement |
| 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
| |
| 08-SEP-2003 kyadamak FIX THE BUG 3124010 |
| 14-NOV-2003 PAJOHRI Bug #3248729 |
| 17-NOV-2003 wcano Bug #3248729 |
| 09-DEC-2003 PAJOHRI Bug #3293895 |
| Added new procedures Set_Default_Value & |
| Swap_Option_Id |
| and modified the procedures Delete_Analysis_Measures |
| Delete_Analysis_Options |
| 23-DEC-2003 ashankar Bug#3327016 |
| Modified the procedure Set_Default_Value |
| to update BSC_KPI_ANALYSIS_GROUPS |
| 14-JUN-2004 adrao Enh#3540302, added SHORT_NAME column to the Analysis |
| Options table. Tracked in Bug#3691035 |
| 02-jul-2004 rpenneru Modified for Enhancement#3532517 |
| 14-jul-2004 rpenneru Modified for bug#3746564 |
| 07-JAN-2005 ashankar Fix for the bug #4099597 |
| 20-APR-2005 adrao added API Cascade_Series_Default_Value |
| 11-MAY-2005 adrao Removed incremental change during series cascading |
| 22-AUG-2005 ashankar Bug#4220400 added the method |
| Set_Default_Analysis_Option |
| 11-APR-2006 visuri Bug#5151997 Changes for Protoype Flag change during |
| update of PMF Measure in Objective |
| 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality from |
| VB to Html |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_ANALYSIS_OPTION_PVT';
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Parent_Option_Id = p_Parent
AND Analysis_Group_Id = p_Group
AND Indicator = p_kpi_Id;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Parent_Option_Id = p_Parent
AND OPTION_ID = p_child
AND Analysis_Group_Id = p_Group
AND Indicator = p_kpi_Id;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE GrandParent_Option_Id = p_GrandParent
AND Analysis_Group_Id = p_Group
AND Indicator =p_kpi_Id;
SELECT COUNT(0)
INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = p_group_count + 1
AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count)
AND Grandparent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count - 1);
SELECT COUNT(0)
INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = p_group_count + 1
AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
SELECT COUNT(0)
INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = p_group_count + 1
AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
SELECT PARENT_OPTION_ID
INTO l_parent_option
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = p_Group_id
AND Option_Id = p_Option_id;
SELECT name, short_name
FROM BSC_KPIS_VL WHERE Indicator = p_Kpi_id;
SELECT analysis_group_id
, COUNT(option_id) option_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_Kpi_Id
GROUP BY analysis_group_id;
SELECT Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND ROWNUM < 2;
SELECT Dependency_Flag, Default_Value
INTO l_Dependency_Flag, l_Default_Value
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
SELECT COUNT(*) INTO l_User_Default
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND User_Level0 = 1;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_next_option;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = l_next_option
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
SELECT COUNT(*) INTO l_User_Default
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND User_Level0 = 1;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_next_option;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = l_next_option
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
SELECT COUNT(*) INTO l_User_Default
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND User_Level0 = 1;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_next_option
AND Parent_Option_Id = p_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = l_next_option
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
SELECT COUNT(*) INTO l_User_Default
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND User_Level0 = 1;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_next_option
AND Parent_Option_Id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = l_next_option
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
SELECT COUNT(*)
INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET DEFAULT_VALUE = l_default_value
WHERE INDiCATOR = p_Kpi_Id
AND ANALYSIS_GROUP_ID = p_group_Id;
SELECT Option_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
ORDER BY Option_ID;
SELECT Option_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Parent_Option_Id = p_parent_option_Id
AND GrandParent_Option_Id = p_grand_parent_option_Id
ORDER BY Option_ID;
SELECT Parent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = 1
AND Parent_Option_Id = l_parent_option_Id
ORDER BY Parent_Option_Id;
SELECT Parent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = 2
AND Parent_Option_Id = l_parent_option_Id
ORDER BY Parent_Option_Id;
SELECT GrandParent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = 2
AND GrandParent_Option_Id = l_grand_parent_option_Id
ORDER BY GrandParent_Option_Id;
SELECT Dependency_Flag
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = DECODE(p_Group_Id, 0, 1, p_Group_Id);
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND Option_Id = cd.Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND Option_Id = cd.Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Parent_Option_Id = ck.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Parent_Option_Id = ck.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Grandparent_Option_Id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Grandparent_Option_Id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Option_Id = cd.Option_Id
AND Analysis_Group_ID = p_Group_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND Option_Id = cd.Option_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 1
AND Parent_Option_Id = cm.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 1
AND Parent_Option_Id = cm.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Parent_Option_Id = ck.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Parent_Option_Id = ck.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Grandparent_Option_Id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Grandparent_Option_Id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 2
AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Option_Id = cd.Option_Id
AND Analysis_Group_ID = p_Group_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND Option_Id = cd.Option_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 1
AND parent_option_id = cm.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET parent_option_id = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = 1
AND parent_option_id = cm.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Option_Id = cd.Option_Id
AND Analysis_Group_ID = p_Group_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET Option_ID = l_Count
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND Option_Id = cd.Option_Id
AND parent_option_id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id
AND Analysis_Option0 = p_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id
AND Analysis_Option0 = p_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id
AND Analysis_Option1 = p_Parent_Option_Id
AND Analysis_Option0 = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id
AND Analysis_Option1 = p_parent_option_Id
AND Analysis_Option0 = p_grand_parent_option_Id;
SELECT COUNT(0)
INTO l_count
FROM BSC_KPIS_B
WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR
,ANALYSIS_GROUP_ID
,OPTION_ID
,PARENT_OPTION_ID
,GRANDPARENT_OPTION_ID
,DIM_SET_ID
,USER_LEVEL0
,USER_LEVEL1
,USER_LEVEL1_DEFAULT
,USER_LEVEL2
,USER_LEVEL2_DEFAULT
,SHORT_NAME)
VALUES( p_Anal_Opt_Rec.Bsc_Kpi_Id
,p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
,p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
,p_Anal_Opt_Rec.Bsc_Parent_Option_Id
,p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,p_Anal_Opt_Rec.Bsc_Dim_Set_Id
,p_Anal_Opt_Rec.Bsc_User_Level0
,p_Anal_Opt_Rec.Bsc_User_Level1
,p_Anal_Opt_Rec.Bsc_User_Level1_Default
,p_Anal_Opt_Rec.Bsc_User_Level2
,p_Anal_Opt_Rec.Bsc_User_Level2_Default
,p_Anal_Opt_Rec.Bsc_Option_Short_Name);
INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_TL( INDICATOR
,ANALYSIS_GROUP_ID
,OPTION_ID
,PARENT_OPTION_ID
,GRANDPARENT_OPTION_ID
,LANGUAGE
,SOURCE_LANG
,NAME
,HELP)
select p_Anal_Opt_Rec.Bsc_Kpi_Id
,p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
,p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
,p_Anal_Opt_Rec.Bsc_Parent_Option_Id
,p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,L.LANGUAGE_CODE
,userenv('LANG')
,p_Anal_Opt_Rec.Bsc_Option_Name
,p_Anal_Opt_Rec.Bsc_Option_Help
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BSC_KPI_ANALYSIS_OPTIONS_TL T
where T.indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and T.analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and T.option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and T.parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
and T.grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
update BSC_KPI_ANALYSIS_GROUPS
set num_of_options = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id + 1
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT DISTINCT GRANDPARENT_OPTION_ID
,DIM_SET_ID
,USER_LEVEL0
,USER_LEVEL1
,USER_LEVEL1_DEFAULT
,USER_LEVEL2
,USER_LEVEL2_DEFAULT
,NAME
,HELP
,SHORT_NAME
into x_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,x_Anal_Opt_Rec.Bsc_Dim_Set_Id
,x_Anal_Opt_Rec.Bsc_User_Level0
,x_Anal_Opt_Rec.Bsc_User_Level1
,x_Anal_Opt_Rec.Bsc_User_Level1_Default
,x_Anal_Opt_Rec.Bsc_User_Level2
,x_Anal_Opt_Rec.Bsc_User_Level2_Default
,x_Anal_Opt_Rec.Bsc_Option_Name
,x_Anal_Opt_Rec.Bsc_Option_Help
,x_Anal_Opt_Rec.Bsc_Option_Short_Name
from BSC_KPI_ANALYSIS_OPTIONS_VL
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
SELECT DISTINCT DIM_SET_ID
,USER_LEVEL0
,USER_LEVEL1
,USER_LEVEL1_DEFAULT
,USER_LEVEL2
,USER_LEVEL2_DEFAULT
,NAME
,HELP
,SHORT_NAME
into x_Anal_Opt_Rec.Bsc_Dim_Set_Id
,x_Anal_Opt_Rec.Bsc_User_Level0
,x_Anal_Opt_Rec.Bsc_User_Level1
,x_Anal_Opt_Rec.Bsc_User_Level1_Default
,x_Anal_Opt_Rec.Bsc_User_Level2
,x_Anal_Opt_Rec.Bsc_User_Level2_Default
,x_Anal_Opt_Rec.Bsc_Option_Name
,x_Anal_Opt_Rec.Bsc_Option_Help
,x_Anal_Opt_Rec.Bsc_Option_Short_Name
from BSC_KPI_ANALYSIS_OPTIONS_VL
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
SELECT DISTINCT PARENT_OPTION_ID
,GRANDPARENT_OPTION_ID
,DIM_SET_ID
,USER_LEVEL0
,USER_LEVEL1
,USER_LEVEL1_DEFAULT
,USER_LEVEL2
,USER_LEVEL2_DEFAULT
,NAME
,HELP
,SHORT_NAME
into x_Anal_Opt_Rec.Bsc_Parent_Option_Id
,x_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,x_Anal_Opt_Rec.Bsc_Dim_Set_Id
,x_Anal_Opt_Rec.Bsc_User_Level0
,x_Anal_Opt_Rec.Bsc_User_Level1
,x_Anal_Opt_Rec.Bsc_User_Level1_Default
,x_Anal_Opt_Rec.Bsc_User_Level2
,x_Anal_Opt_Rec.Bsc_User_Level2_Default
,x_Anal_Opt_Rec.Bsc_Option_Name
,x_Anal_Opt_Rec.Bsc_Option_Help
,x_Anal_Opt_Rec.Bsc_Option_Short_Name
from BSC_KPI_ANALYSIS_OPTIONS_VL
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
procedure Update_Analysis_Options
(
p_commit IN varchar2 -- := FND_API.G_FALSE
, p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
, p_data_source IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
SAVEPOINT UpdateBSCAnaOptPVT;
SELECT COUNT(0)
INTO l_count
FROM BSC_KPIS_B
WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
update BSC_KPI_ANALYSIS_OPTIONS_B
set grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
update BSC_KPI_ANALYSIS_OPTIONS_TL
set name = l_Anal_Opt_Rec.Bsc_Option_Name
,help = l_Anal_Opt_Rec.Bsc_Option_Help
,source_lang = userenv('LANG')
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update BSC_KPI_ANALYSIS_OPTIONS_B
set dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
update BSC_KPI_ANALYSIS_OPTIONS_TL
set name = l_Anal_Opt_Rec.Bsc_Option_Name
,help = l_Anal_Opt_Rec.Bsc_Option_Help
,source_lang = userenv('LANG')
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update BSC_KPI_ANALYSIS_OPTIONS_B
set parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
,grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
update BSC_KPI_ANALYSIS_OPTIONS_TL
set name = l_Anal_Opt_Rec.Bsc_Option_Name
,help = l_Anal_Opt_Rec.Bsc_Option_Help
,source_lang = userenv('LANG')
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
ROLLBACK TO UpdateBSCAnaOptPVT;
ROLLBACK TO UpdateBSCAnaOptPVT;
ROLLBACK TO UpdateBSCAnaOptPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
ROLLBACK TO UpdateBSCAnaOptPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
end Update_Analysis_Options;
PROCEDURE Delete_Analysis_Options
( 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
l_Group_ID BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
l_AnaOpt_Delete BOOLEAN := TRUE;
l_delete VARCHAR2(1);
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
SAVEPOINT DeleteBSCAnaOptPVT;
l_delete := Delete_Analysis_Option( l_Anal_Opt_Rec.Bsc_Kpi_Id
,l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
,x_return_status
,x_msg_count
,x_msg_data
,l_Anal_Opt_Rec.Bsc_Analysis_Group_Id);
IF (l_delete = 'S') THEN
FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
FND_MESSAGE.SET_TOKEN('BSC_AO_DELETE', l_Anal_Opt_Rec.Bsc_Kpi_Id);
IF l_delete = 'L' THEN
IF is_custom_kpi(l_Anal_Opt_Rec.Bsc_Kpi_Id,l_Kpi_Name) = FALSE THEN
FND_MESSAGE.SET_NAME('BSC','BSC_LAST_AO_IN_KPI');
l_AnaOpt_Delete := TRUE;
IF (l_AnaOpt_Delete) THEN
SELECT MAX(Analysis_Group_Id) INTO l_Group_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
AND grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
AND grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures( p_commit
,l_Anal_Opt_Rec
,x_return_status
,x_msg_count
,x_msg_data);
SELECT DEFAULT_VALUE
INTO l_default_value
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE INDICATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND ANALYSIS_GROUP_ID = 0;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET DEFAULT_VALUE = l_default_value
WHERE INDiCATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id
AND ANALYSIS_GROUP_ID = 0;
IF (l_AnaOpt_Delete) THEN
IF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
(l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
(l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
delete from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
and grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
delete from BSC_KPI_ANALYSIS_OPTIONS_TL
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
and grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
delete from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
delete from BSC_KPI_ANALYSIS_OPTIONS_TL
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
delete from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
delete from BSC_KPI_ANALYSIS_OPTIONS_TL
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures( p_commit
,l_Anal_Opt_Rec
,x_return_status
,x_msg_count
,x_msg_data);
ROLLBACK TO DeleteBSCAnaOptPVT;
ROLLBACK TO DeleteBSCAnaOptPVT;
ROLLBACK TO DeleteBSCAnaOptPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
ROLLBACK TO DeleteBSCAnaOptPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
END Delete_Analysis_Options;
SELECT COUNT(0)
INTO l_count
FROM BSC_KPIS_B
WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
insert into BSC_KPI_ANALYSIS_MEASURES_B( indicator
,ANALYSIS_OPTION0
,ANALYSIS_OPTION1
,ANALYSIS_OPTION2
,SERIES_ID
,DATASET_ID
,AXIS
,SERIES_TYPE
,STACK_SERIES_ID
,BM_FLAG
,BUDGET_FLAG
,DEFAULT_VALUE
,SERIES_COLOR
,BM_COLOR
,PROTOTYPE_FLAG
,KPI_MEASURE_ID)
values( p_Anal_Opt_Rec.Bsc_Kpi_Id
,p_Anal_Opt_Rec.Bsc_Option_Group0
,p_Anal_Opt_Rec.Bsc_Option_Group1
,p_Anal_Opt_Rec.Bsc_Option_Group2
,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
,p_Anal_Opt_Rec.Bsc_Dataset_Id
,p_Anal_Opt_Rec.Bsc_Dataset_Axis
,p_Anal_Opt_Rec.Bsc_Dataset_Series_Type
,p_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
,p_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
,p_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
,p_Anal_Opt_Rec.Bsc_Dataset_Default_Value
,p_Anal_Opt_Rec.Bsc_Dataset_Series_Color
,p_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
,p_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
,p_Anal_Opt_Rec.Bsc_Kpi_Measure_Id);
insert into BSC_KPI_ANALYSIS_MEASURES_TL( indicator
,analysis_option0
,analysis_option1
,analysis_option2
,series_id
,language
,source_lang
,name
,help)
select p_Anal_Opt_Rec.Bsc_Kpi_Id
,p_Anal_Opt_Rec.Bsc_Option_Group0
,p_Anal_Opt_Rec.Bsc_Option_Group1
,p_Anal_Opt_Rec.Bsc_Option_Group2
,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
,L.LANGUAGE_CODE
,userenv('LANG')
,p_Anal_Opt_Rec.Bsc_Measure_Long_Name
,p_Anal_Opt_Rec.Bsc_Measure_Help
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BSC_KPI_ANALYSIS_MEASURES_TL T
where T.indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and T.analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
and T.analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
and T.analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
and T.series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
select distinct series_id
,dataset_id
,axis
,series_type
,stack_series_id
,bm_flag
,budget_flag
,default_value
,series_color
,bm_color
,prototype_flag
,name
,help
,kpi_measure_id
into x_Anal_Opt_Rec.Bsc_Dataset_Series_Id
,x_Anal_Opt_Rec.Bsc_Dataset_Id
,x_Anal_Opt_Rec.Bsc_Dataset_Axis
,x_Anal_Opt_Rec.Bsc_Dataset_Series_Type
,x_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
,x_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
,x_Anal_Opt_Rec.Bsc_Dataset_Default_Value
,x_Anal_Opt_Rec.Bsc_Dataset_Series_Color
,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
,x_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
,x_Anal_Opt_Rec.Bsc_Measure_Long_Name
,x_Anal_Opt_Rec.Bsc_Measure_Help
,x_Anal_Opt_Rec.Bsc_Kpi_Measure_Id
from BSC_KPI_ANALYSIS_MEASURES_VL
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
select distinct dataset_id
,axis
,series_type
,stack_series_id
,bm_flag
,budget_flag
,default_value
,series_color
,bm_color
,prototype_flag
,name
,help
into x_Anal_Opt_Rec.Bsc_Dataset_Id
,x_Anal_Opt_Rec.Bsc_Dataset_Axis
,x_Anal_Opt_Rec.Bsc_Dataset_Series_Type
,x_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
,x_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
,x_Anal_Opt_Rec.Bsc_Dataset_Default_Value
,x_Anal_Opt_Rec.Bsc_Dataset_Series_Color
,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
,x_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
,x_Anal_Opt_Rec.Bsc_Measure_Long_Name
,x_Anal_Opt_Rec.Bsc_Measure_Help
from BSC_KPI_ANALYSIS_MEASURES_VL
where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
and series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
procedure Update_Analysis_Measures
(
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
l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
SAVEPOINT UpdateBSCAnaMeasPVT;
SELECT COUNT(0)
INTO l_count
FROM BSC_KPIS_B
WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT color_method
INTO l_old_color_method
FROM bsc_sys_datasets_b
WHERE dataset_id = p_Anal_Opt_Rec.Bsc_Dataset_Id;
SELECT color_method
INTO l_new_color_method
FROM bsc_sys_datasets_b
WHERE dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id;
SELECT source
INTO l_source
FROM bsc_sys_datasets_b
WHERE dataset_id = p_Anal_Opt_Rec.Bsc_Dataset_Id;
SELECT SHORT_NAME
INTO l_sname
FROM BSC_KPIS_B
WHERE INDICATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id;
update BSC_KPI_ANALYSIS_MEASURES_B
set series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id
,dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id
,axis = l_Anal_Opt_Rec.Bsc_Dataset_Axis
,series_type = l_Anal_Opt_Rec.Bsc_Dataset_Series_Type
,stack_series_id = l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
,bm_flag = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
,budget_flag = l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
,default_value = l_Anal_Opt_Rec.Bsc_Dataset_Default_Value
,series_color = l_Anal_Opt_Rec.Bsc_Dataset_Series_Color
,bm_color = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
,prototype_flag = l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2;
update BSC_KPI_ANALYSIS_MEASURES_TL
set name = l_Anal_Opt_Rec.Bsc_Measure_Long_Name
,help = l_Anal_Opt_Rec.Bsc_Measure_Help
,source_lang = userenv('LANG')
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
and indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update BSC_KPI_ANALYSIS_MEASURES_B
set series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id
,dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id
,axis = l_Anal_Opt_Rec.Bsc_Dataset_Axis
,series_type = l_Anal_Opt_Rec.Bsc_Dataset_Series_Type
,stack_series_id = l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
,bm_flag = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
,budget_flag = l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
,default_value = l_Anal_Opt_Rec.Bsc_Dataset_Default_Value
,series_color = l_Anal_Opt_Rec.Bsc_Dataset_Series_Color
,bm_color = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
,prototype_flag = l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
and series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
update BSC_KPI_ANALYSIS_MEASURES_TL
set name = l_Anal_Opt_Rec.Bsc_Measure_Long_Name
,help = l_Anal_Opt_Rec.Bsc_Measure_Help
,source_lang = userenv('LANG')
where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
and indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
SELECT kpi_measure_id
INTO l_kpi_measure_id
FROM bsc_kpi_analysis_measures_b
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id;
BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
,p_kpi_measure_id => l_kpi_measure_id
,p_cascade_shared => TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
ROLLBACK TO UpdateBSCAnaMeasPVT;
ROLLBACK TO UpdateBSCAnaMeasPVT;
ROLLBACK TO UpdateBSCAnaMeasPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
ROLLBACK TO UpdateBSCAnaMeasPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
end Update_Analysis_Measures;
procedure Delete_Analysis_Measures(
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
l_count number;
SELECT A.Option_ID Option_Id
, B.Option_ID Parent_Option_Id
, C.Option_ID GrandParent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_OPTIONS_B B
, BSC_KPI_ANALYSIS_OPTIONS_B C
WHERE A.Indicator = B.Indicator
AND A.Indicator = C.Indicator
AND A.Analysis_Group_Id = 0
AND B.Analysis_Group_Id = 1
AND C.Analysis_Group_Id = 2
AND A.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT A.Option_ID Option_Id
, B.Option_ID Parent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_OPTIONS_B B
WHERE A.Indicator = B.Indicator
AND A.Analysis_Group_Id = 0
AND B.Analysis_Group_Id = 1
AND A.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT Dependency_Flag
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = 2;
SELECT Dependency_Flag
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = 1;
l_Delete_Flag BOOLEAN := FALSE;
SAVEPOINT DeleteBSCAnaMeasPVT;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
SELECT COUNT(*) INTO l_COunt
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = cd.Option_Id
AND analysis_option1 = cd.Parent_Option_Id
AND analysis_option2 = cd.GrandParent_Option_Id;
l_Delete_Flag := TRUE;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_MEASURES_B D
WHERE D.Indicator = A.Indicator
AND A.Analysis_Group_Id = 2
AND A.Option_Id = D.Analysis_Option2
AND A.Parent_Option_Id = D.Analysis_Option1
AND A.GrandParent_Option_Id = D.Analysis_Option0
AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND D.Analysis_Option0 = cd.Option_Id
AND D.Analysis_Option1 = cd.Parent_Option_Id
AND D.Analysis_Option2 = cd.GrandParent_Option_Id;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_MEASURES_B D
WHERE D.Indicator = A.Indicator
AND A.Analysis_Group_Id = 1
AND A.Option_Id = D.Analysis_Option1
AND A.Parent_Option_Id = D.Analysis_Option0
AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND D.Analysis_Option0 = cd.Option_Id
AND D.Analysis_Option1 = cd.Parent_Option_Id;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_MEASURES_B D
WHERE D.Indicator = A.Indicator
AND A.Analysis_Group_Id = 2
AND A.Parent_Option_Id = D.Analysis_Option1
AND A.GrandParent_Option_Id = 0
AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND D.Analysis_Option1 = cd.Parent_Option_Id
AND D.Analysis_Option2 = cd.GrandParent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Indicator = -999
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = cd.Option_Id
AND analysis_option1 = l_Parent_Analysis
AND analysis_option2 = l_Grand_Parent_Analysis;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Indicator = -999
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = cd.Option_Id
AND analysis_option1 = l_Parent_Analysis
AND analysis_option2 = l_Grand_Parent_Analysis;
IF (l_Delete_Flag) THEN
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
WHERE Indicator = -999;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
WHERE Indicator = -999;
l_Delete_Flag := TRUE;
SELECT Dependency_Flag INTO l_Parent_Dependent
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = 1;
SELECT COUNT(*)
INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = 1;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B A
, BSC_KPI_ANALYSIS_MEASURES_B D
WHERE D.Indicator = A.Indicator
AND A.Analysis_Group_Id = 1
AND A.Option_Id = D.Analysis_Option1
AND A.Parent_Option_Id = D.Analysis_Option0
AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND D.Analysis_Option0 = cd.Option_Id
AND D.Analysis_Option1 = cd.Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Indicator = -999
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = cd.Option_Id
AND analysis_option1 = l_Parent_Analysis
AND analysis_option2 = 0;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Indicator = -999
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = cd.Option_Id
AND analysis_option1 = l_Parent_Analysis
AND analysis_option2 = 0;
IF (l_Delete_Flag) THEN
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
WHERE Indicator = -999;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
WHERE Indicator = -999;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
ROLLBACK TO DeleteBSCAnaMeasPVT;
ROLLBACK TO DeleteBSCAnaMeasPVT;
ROLLBACK TO DeleteBSCAnaMeasPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
ROLLBACK TO DeleteBSCAnaMeasPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
END Delete_Analysis_Measures;
FUNCTION Delete_Analysis_Option
( p_kpi_id IN NUMBER
, p_anal_option_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_anal_group_id IN NUMBER DEFAULT 0
) RETURN VARCHAR2
IS
-- This function checks if an analysis option may be deleted. The checks are: If
-- this is the last analysis Option then it may not be deleted. If it is used by a
-- shared KPI then if it is being displayed then it may not be deleted. Any other
-- result allows deletion.
l_kpi_id NUMBER;
SELECT DISTINCT A.Indicator
, B.User_Level1
FROM BSC_KPIS_B A
, BSC_KPI_ANALYSIS_OPTIONS_B B
WHERE Source_Indicator = p_kpi_id
AND A.Indicator = B.Indicator
AND B.analysis_group_id = p_anal_group_id
AND B.option_id = p_anal_option_id
AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
SELECT DISTINCT(Share_Flag)
INTO l_value
FROM BSC_KPIS_B
WHERE indicator = p_kpi_id;
SELECT COUNT(option_id)
INTO l_value
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_kpi_id;
SELECT COUNT(indicator)
INTO l_value
FROM BSC_KPIS_B
WHERE source_indicator = p_kpi_id
AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
end Delete_Analysis_Option;
SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = l_group_count
AND Option_Id = l_option_id
AND Parent_Option_Id = l_parent_option_id;
SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = l_group_count
AND Option_Id = l_option_id
AND Parent_Option_Id = l_parent_option_id
AND Grandparent_Option_Id = l_grand_parent_option_id;
SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_id
AND Analysis_Group_Id = l_group_count
AND Option_Id = l_option_id;
PROCEDURE Delete_Ana_Opt_Mult_Groups
( p_commit IN VARCHAR2:=FND_API.G_FALSE
, p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
, p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
, p_max_group_count IN NUMBER
, p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
, 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
l_count NUMBER;
SAVEPOINT DeleteBSCAnaOptMultGroups;
FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, 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
);
ROLLBACK TO DeleteBSCAnaOptMultGroups;
ROLLBACK TO DeleteBSCAnaOptMultGroups;
ROLLBACK TO DeleteBSCAnaOptMultGroups;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
ROLLBACK TO DeleteBSCAnaOptMultGroups;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
END Delete_Ana_Opt_Mult_Groups;
l_Anal_Grp_Opt_Tbl.DELETE(table_index);
DELETE FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = l_old_group_count - 1;
l_Anal_Opt_Tbl.DELETE(l_old_group_count - 1);
SELECT COUNT(DISTINCT(Option_Id)) INTO l_Num_Opt_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = l_group_count;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Num_Of_Options = l_Num_Opt_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = l_group_count;
API is to be called after an Analysis Option has been deleted, etc from Start-end-KPI UI
WARNING: This should not be used from within PMD. Its been implemented only for START-TO-END KPI.
*/
-- Added for Start-to-End KPI Project, Bug#3691035
PROCEDURE Refresh_Short_Names (
p_Commit IN VARCHAR2
, p_Kpi_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
) IS
CURSOR c_Update_Short_Names IS
SELECT INDICATOR, OPTION_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE INDICATOR = p_Kpi_Id
AND ANALYSIS_GROUP_ID = 0
AND SHORT_NAME IS NOT NULL;
FOR cUSN IN c_Update_Short_Names LOOP
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET SHORT_NAME = BSC_ANALYSIS_OPTION_PUB.C_BSC_UNDERSCORE || cUSN.INDICATOR || '_' || cUSN.OPTION_ID
WHERE INDICATOR = cUSN.INDICATOR
AND OPTION_ID = cUSN.OPTION_ID
AND ANALYSIS_GROUP_ID = 0
AND SHORT_NAME IS NOT NULL;
l_Allow_Delete BOOLEAN;
SELECT c.actual_data_source actual_data_source , c.function_name function_name
,a.dataset_id dataset_id
,a.full_name name
FROM bsc_oaf_analysys_opt_comb_v a,
bsc_sys_datasets_b b,
bis_indicators c
WHERE a.dataset_id = b.dataset_id
AND b.dataset_id = c.dataset_id
AND a.Indicator = p_kpi_id
AND a.Analysis_Option0 = p_option0
AND a.Analysis_Option1 = p_option1
AND a.Analysis_Option2 = p_option2
AND a.SERIES_ID = p_series_id;
SELECT a.short_name , b.name
FROM bsc_kpi_analysis_options_b a, bsc_kpis_vl b
WHERE a.indicator = p_kpi_id
AND a.option_id = p_option0
AND a.parent_option_id = p_option1
AND a.grandparent_option_id = p_option2
AND a.indicator = b.indicator;
SELECT a.name KPI_NAME, b.name OBJECTVIE_NAME
FROM BSC_KPI_ANALYSIS_MEASURES_VL a
,BSC_KPIS_VL b
WHERE a.indicator = b.indicator
AND a.dataset_id = p_dataset_id
AND a.Indicator <> p_kpi_id
AND a.Analysis_Option0 <> p_option0
AND a.Analysis_Option1 <> p_option1
AND a.Analysis_Option2 <> p_option2
AND a.SERIES_ID <> p_series_id;
SELECT name
FROM BSC_KPIS_VL
WHERE indicator = p_kpi_id;
l_Allow_Delete := TRUE;
l_Allow_Delete := FALSE;
l_Allow_Delete := FALSE;
IF (l_Allow_Delete = FALSE) THEN
IF (c_KpiName%ISOPEN) THEN
CLOSE c_KpiMeasure;
FND_MESSAGE.SET_NAME('BSC','BSC_OBJ_DELETE');
FND_MESSAGE.SET_NAME('BSC','BSC_MEASURE_DELETE');
PROCEDURE delete_extra_series(
p_Bsc_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
l_Bsc_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = 0
AND analysis_option1 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id > 0;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = 0
AND analysis_option1 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id > 0;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
END delete_extra_series;
procedure Delete_Data_Series(
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_Dataseries is
SELECT SERIES_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID > p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
ORDER BY SERIES_ID;
SAVEPOINT DeleteBSCDataSeriesPVT;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND K.ANALYSIS_OPTION1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND K.ANALYSIS_OPTION2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND K.DEFAULT_VALUE = 1;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
SET K.DEFAULT_VALUE = 1
WHERE K.INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND K.ANALYSIS_OPTION1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND K.ANALYSIS_OPTION2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND K.SERIES_ID = 0;
ROLLBACK TO DeleteBSCDataSeriesPVT;
ROLLBACK TO DeleteBSCDataSeriesPVT;
ROLLBACK TO DeleteBSCDataSeriesPVT;
x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Data_Series ';
x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Data_Series ';
End Delete_Data_Series;
SELECT SERIES_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id
WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
If in "UPDATE" mode
-------------------
1) If we have p_Default_Value passed as 0 and the table BSC_KPI_ANALYSIS_MEASURES_B
has only one single entry, then x_Default_Value will be returned as 1
2) If we have p_Default_Value passed as 0 for a series which already has
default_value as 1, then the next subsequent series is set with 1 and if the
series being updated is already the last one, then SERIES_ID = 0 will be updated
with default_value = 1.
3) If One of the default value is being changed from 0 to 1, then the rest of
default_Value is set to 0 and the current series is set to 1.
The API ensures that there is exactly one entry in BSC_kPI_ANALYSIS_OPTIONS_B
table for DEFAULT_VALUE =1 for the (kpi,option0,option1,option2) combination.
Appropriate color changes are cascaded into the Objectives (and Shared)
Also changes will be cascaded only if the current analysis option combination
is the default combination
/---------------------------------------------------------------------------------*/
PROCEDURE Cascade_Series_Default_Value (
p_Commit IN VARCHAR2
, p_Api_Mode IN VARCHAR2
, p_Kpi_Id IN NUMBER
, p_Option0 IN NUMBER
, p_Option1 IN NUMBER
, p_Option2 IN NUMBER
, p_Series_Id IN NUMBER
, p_Default_Value IN NUMBER
, x_Default_Value OUT NOCOPY NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
) IS
CURSOR c_Shared_Objectives IS
SELECT K.INDICATOR
FROM BSC_KPIS_B K
WHERE K.SOURCE_INDICATOR = p_Kpi_Id
AND K.PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG;
SELECT COUNT(1) INTO l_Count
FROM bsc_db_color_ao_defaults_v
WHERE indicator = p_Kpi_Id
AND a0_default = p_Option0
AND a1_default = p_Option1
AND a2_default = p_Option2;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
SET K.DEFAULT_VALUE = 0
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2;
ELSIF (p_Api_Mode = C_API_UPDATE) THEN
SELECT K.DEFAULT_VALUE INTO l_Default_Value
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2
AND K.SERIES_ID = p_Series_Id;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
SET K.DEFAULT_VALUE = 0
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2;
SELECT NVL(MAX(K.SERIES_ID), 0) INTO l_Max_Series_Id
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
SET K.DEFAULT_VALUE = 0
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
SET K.DEFAULT_VALUE = 1
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2
AND K.SERIES_ID = l_Upd_Series_Id;
SELECT K.DEFAULT_VALUE INTO x_Default_Value
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = p_Option0
AND K.ANALYSIS_OPTION1 = p_Option1
AND K.ANALYSIS_OPTION2 = p_Option2
AND K.SERIES_ID = p_Series_Id;
SELECT
K.SHORT_NAME
FROM
BSC_KPIS_B K,
BSC_KPI_ANALYSIS_MEASURES_B M
WHERE
K.INDICATOR = M.INDICATOR
AND M.DATASET_ID = p_Dataset_Id
AND K.SHORT_NAME IS NOT NULL
AND ROWNUM <= 1
ORDER BY K.CREATION_DATE;
UPDATE BIS_INDICATORS I
SET I.ACTUAL_DATA_SOURCE_TYPE = l_Actual_Data_Source_Type
, I.ACTUAL_DATA_SOURCE = l_Actual_Data_Source
, I.FUNCTION_NAME = l_Function_Name
, I.ENABLE_LINK = l_Enable_Link
, I.COMPARISON_SOURCE = l_Comparison_Source
WHERE I.SHORT_NAME = p_Measure_Short_Name;
UPDATE bsc_kpi_analysis_groups
SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_DISABLED
WHERE indicator = p_obj_id;
UPDATE bsc_kpi_analysis_groups
SET default_value = l_Anal_Opt_Comb_Tbl(l_anal_grp_id)
WHERE indicator = p_obj_id
AND analysis_group_id = l_anal_grp_id;
UPDATE bsc_kpi_analysis_measures_b
SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_DISABLED
WHERE indicator = p_obj_id
AND analysis_option0 = l_Anal_Opt_Comb_Tbl(0)
AND analysis_option1 = l_Anal_Opt_Comb_Tbl(1)
AND analysis_option2 = l_Anal_Opt_Comb_Tbl(2);
UPDATE bsc_kpi_analysis_measures_b
SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_ENABLED
WHERE indicator = p_obj_id
AND analysis_option0 = l_Anal_Opt_Comb_Tbl(0)
AND analysis_option1 = l_Anal_Opt_Comb_Tbl(1)
AND analysis_option2 = l_Anal_Opt_Comb_Tbl(2)
AND series_id = l_Anal_Opt_Comb_Tbl(3);