[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 1. Modified Update Query for BSC_KPI_DIM_SETS_TL, |
| BSC_KPI_DIM_LEVELS_TL, and Insert Query for |
| BSC_KPI_DIM_LEVELS_TL. |
| 2. Changed nvl(l_Dim_Set_Rec.Bsc_Language, userenv('LANG')) |
| to userenv('LANG') |
| 18-MAR-2003 PAJOHRI Added Condition to update l_Dim_Set_Rec.Bsc_Dim_Set_Name, only |
| when it is Not Null |
| 24-JULY-2003 ASHANKAR Fix for the bug 3060555 |
| 05-SEP-2003 Adeulgao Fixed bug#3128103 |
| 07-JAN-2004 PAJOHRI Bug #3343860, modified procedure Create_Dim_Levels for when |
| there is no dimension group associated with dimension set |
| 25-FEB-2004 PAJOHRI Bug #3446359, modified insertion logic for |
| BSC_KPI_DIM_LEVELS_TL to allow NULL value if NULL values are |
| passed from the Public APIs |
| 21-JUL-2005 ashankar Bug#4314386 |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_SETS_PVT';
select count(*)
into l_count
from BSC_KPI_DIM_GROUPS
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
insert into BSC_KPI_DIM_GROUPS( indicator
,dim_set_id
,dim_group_id
,dim_group_index)
values( p_Dim_Set_Rec.Bsc_Kpi_Id
,p_Dim_Set_Rec.Bsc_Dim_Set_Id
,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Index);
select dim_set_id
,dim_group_index
into x_Dim_Set_Rec.Bsc_Dim_Set_Id
,x_Dim_Set_Rec.Bsc_Dim_Level_Group_Index
from BSC_KPI_DIM_GROUPS
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
and dim_set_id = NVL(p_Dim_Set_Rec.Bsc_Dim_Set_Id, 0);
procedure Update_Dim_Group_In_Dset(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SAVEPOINT UpdateBSCDimGrpInDSetPvt;
update BSC_KPI_DIM_GROUPS
set dim_group_index = l_Dim_Set_Rec.Bsc_Dim_Level_Group_Index
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_group_id = l_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id;
ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
end Update_Dim_Group_In_Dset;
procedure Delete_Dim_Group_In_Dset(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_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 DeleteBSCDimGrpInDSetPvt;
delete from BSC_KPI_DIM_GROUPS
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
end Delete_Dim_Group_In_Dset;
select count(*)
into l_Dim_Set_Rec.Bsc_Dim_Set_Id
from BSC_KPI_DIM_SETS_TL
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id;
select count(*)
into l_count
from BSC_KPI_DIM_SETS_TL
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id;
insert into BSC_KPI_DIM_SETS_TL( indicator
,dim_set_id
,language
,source_lang
,name
,created_by -- Added for PMD
,creation_date -- Added for PMD
,last_updated_by -- Added for PMD
,last_update_date -- Added for PMD
,last_update_login -- Added for PMD
,short_name
)
SELECT l_Dim_Set_Rec.Bsc_Kpi_Id
,l_Dim_Set_Rec.Bsc_Dim_Set_Id
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Dim_Set_Rec.Bsc_Dim_Set_Name
,fnd_global.USER_ID -- Added for PMD
,sysdate -- Added for PMD
,fnd_global.USER_ID -- Added for PMD
,sysdate -- Added for PMD
,fnd_global.LOGIN_ID -- Added for PMD
,l_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM BSC_KPI_DIM_SETS_TL T
WHERE T.indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
AND T.dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
AND T.LANGUAGE = L.LANGUAGE_CODE);
update BSC_KPI_ANALYSIS_GROUPS
set change_dim_set = 1
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and analysis_group_id = 0;
SELECT DISTINCT NAME
,SHORT_NAME
INTO x_Dim_Set_Rec.Bsc_Dim_Set_Name
,x_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
FROM BSC_KPI_DIM_SETS_TL
WHERE INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id
AND DIM_SET_ID = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND LANGUAGE = USERENV('LANG');
procedure Update_Bsc_Kpi_Dim_Sets_Tl(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SAVEPOINT UpdateBSCKpiDSetTlPvt;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPIS_B
WHERE INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id;
select count(dim_set_id)
into l_count
from BSC_KPI_DIM_SETS_TL
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
IF p_Dim_Set_Rec.Bsc_Last_Updated_By IS NULL THEN
l_Dim_Set_Rec.Bsc_Last_Updated_By := fnd_global.USER_ID;
IF p_Dim_Set_Rec.Bsc_Last_Update_Login IS NULL THEN
l_Dim_Set_Rec.Bsc_Last_Update_Login := fnd_global.LOGIN_ID;
UPDATE BSC_KPI_DIM_SETS_TL
SET NAME = l_Dim_Set_Rec.Bsc_Dim_Set_Name
,SOURCE_LANG = userenv('LANG')
, LAST_UPDATED_BY = l_Dim_Set_Rec.Bsc_Last_Updated_By -- Added for PMD
, LAST_UPDATE_DATE = SYSDATE -- Added for PMD
, LAST_UPDATE_LOGIN = l_Dim_Set_Rec.Bsc_Last_Update_Login -- Added for PMD
, SHORT_NAME = l_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO UpdateBSCKpiDSetTlPvt;
ROLLBACK TO UpdateBSCKpiDSetTlPvt;
ROLLBACK TO UpdateBSCKpiDSetTlPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
ROLLBACK TO UpdateBSCKpiDSetTlPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
end Update_Bsc_Kpi_Dim_Sets_Tl;
procedure Delete_Bsc_Kpi_Dim_Sets_Tl(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_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 DeleteBSCKpiDSetTlPvt;
select count(dim_group_id)
into l_count
from BSC_KPI_DIM_GROUPS
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
delete from BSC_KPI_DIM_SETS_TL
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
ROLLBACK TO DeleteBSCKpiDSetTlPvt;
ROLLBACK TO DeleteBSCKpiDSetTlPvt;
ROLLBACK TO DeleteBSCKpiDSetTlPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
ROLLBACK TO DeleteBSCKpiDSetTlPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
end Delete_Bsc_Kpi_Dim_Sets_Tl;
select count(*)
into l_count
from BSC_KPI_DIM_LEVEL_PROPERTIES
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
insert into BSC_KPI_DIM_LEVEL_PROPERTIES( indicator
,dim_set_id
,dim_level_id
,position
,total0
,level_display
,default_key_value
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,target_level)
values( p_Dim_Set_Rec.Bsc_Kpi_Id
,p_Dim_Set_Rec.Bsc_Dim_Set_Id
,p_Dim_Set_Rec.Bsc_Level_Id
,p_Dim_Set_Rec.Bsc_Dset_Position
,p_Dim_Set_Rec.Bsc_Dset_Total0
,p_Dim_Set_Rec.Bsc_Dset_Level_Display
,p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
,p_Dim_Set_Rec.Bsc_Dset_User_Level0
,p_Dim_Set_Rec.Bsc_Dset_User_Level1
,p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
,p_Dim_Set_Rec.Bsc_Dset_User_Level2
,p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
,p_Dim_Set_Rec.Bsc_Dset_Target_Level);
select distinct position
,total0
,level_display
,default_key_value
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
into x_Dim_Set_Rec.Bsc_Dset_Position
,x_Dim_Set_Rec.Bsc_Dset_Total0
,x_Dim_Set_Rec.Bsc_Dset_Level_Display
,x_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
,x_Dim_Set_Rec.Bsc_Dset_User_Level0
,x_Dim_Set_Rec.Bsc_Dset_User_Level1
,x_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
,x_Dim_Set_Rec.Bsc_Dset_User_Level2
,x_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
from BSC_KPI_DIM_LEVEL_PROPERTIES
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
procedure Update_Dim_Level_Properties(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SAVEPOINT UpdateBSCDimLevPropPvt;
select count(dim_set_id)
into l_count
from BSC_KPI_DIM_SETS_TL
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
update BSC_KPI_DIM_LEVEL_PROPERTIES
set position = l_Dim_Set_Rec.Bsc_Dset_Position
,total0 = l_Dim_Set_Rec.Bsc_Dset_Total0
,level_display = l_Dim_Set_Rec.Bsc_Dset_Level_Display
,default_key_value = l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
,user_level0 = l_Dim_Set_Rec.Bsc_Dset_User_Level0
,user_level1 = l_Dim_Set_Rec.Bsc_Dset_User_Level1
,user_level1_default = l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
,user_level2 =l_Dim_Set_Rec.Bsc_Dset_User_Level2
,user_level2_default = l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_level_id = l_Dim_Set_Rec.Bsc_Level_Id;
ROLLBACK TO UpdateBSCDimLevPropPvt;
ROLLBACK TO UpdateBSCDimLevPropPvt;
ROLLBACK TO UpdateBSCDimLevPropPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
ROLLBACK TO UpdateBSCDimLevPropPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
end Update_Dim_Level_Properties;
procedure Delete_Dim_Level_Properties(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_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 DeleteBSCDimLevPropPvt;
select count(dim_set_id)
into l_count
from BSC_KPI_DIM_SETS_TL
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
delete from BSC_KPI_DIM_LEVEL_PROPERTIES
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
ROLLBACK TO DeleteBSCDimLevPropPvt;
ROLLBACK TO DeleteBSCDimLevPropPvt;
ROLLBACK TO DeleteBSCDimLevPropPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
ROLLBACK TO DeleteBSCDimLevPropPvt;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
end Delete_Dim_Level_Properties;
SELECT Name
, Help
, Total_Disp_Name
, Comp_Disp_Name
, Language
, Source_Lang
FROM BSC_SYS_DIM_LEVELS_TL
WHERE DIM_LEVEL_ID = p_Dim_Set_Rec.Bsc_Level_Id;
SAVEPOINT DeleteBSCDimLevsPVT;
SELECT count(*)
INTO l_count
FROM BSC_KPI_DIM_LEVELS_B
WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND upper(level_table_name) = upper(p_Dim_Set_Rec.Bsc_Level_Name)
AND dim_level_index = p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1;
INSERT INTO BSC_KPI_DIM_LEVELS_B( indicator
,dim_set_id
,dim_level_index
,level_table_name
,level_view_name
,filter_column
,filter_value
,default_value
,default_type
,value_order_by
,comp_order_by
,level_pk_col
,parent_level_index
,parent_level_rel
,table_relation
,parent_level_index2
,parent_level_rel2
,status
,parent_in_total
,position
,total0
,level_display
,no_items
,default_key_value
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,target_level)
VALUES( p_Dim_Set_Rec.Bsc_Kpi_Id
,p_Dim_Set_Rec.Bsc_Dim_Set_Id
,p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
,p_Dim_Set_Rec.Bsc_Level_Name
,p_Dim_Set_Rec.Bsc_View_Name
,p_Dim_Set_Rec.Bsc_Dset_Filter_Column
,p_Dim_Set_Rec.Bsc_Dset_Filter_Value
,p_Dim_Set_Rec.Bsc_Dset_Default_Value
,p_Dim_Set_Rec.Bsc_Dset_Default_Type
,p_Dim_Set_Rec.Bsc_Dset_Value_Order
,p_Dim_Set_Rec.Bsc_Dset_Comp_Order
,p_Dim_Set_Rec.Bsc_Pk_Col
,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
,p_Dim_Set_Rec.Bsc_Dset_Table_Relation
,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
,p_Dim_Set_Rec.Bsc_Dset_Status
,p_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
,p_Dim_Set_Rec.Bsc_Dset_Position
,p_Dim_Set_Rec.Bsc_Dset_Total0
,p_Dim_Set_Rec.Bsc_Dset_Level_Display
,p_Dim_Set_Rec.Bsc_Dset_No_Items
,p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
,p_Dim_Set_Rec.Bsc_Dset_User_Level0
,p_Dim_Set_Rec.Bsc_Dset_User_Level1
,p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
,p_Dim_Set_Rec.Bsc_Dset_User_Level2
,p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
,p_Dim_Set_Rec.Bsc_Dset_Target_Level);
INSERT INTO BSC_KPI_DIM_LEVELS_TL
( Indicator
, Dim_Set_Id
, Dim_Level_Index
, Language
, Source_Lang
, Name
, Help
, Total_Disp_Name
, Comp_Disp_Name
)
SELECT p_Dim_Set_Rec.Bsc_Kpi_Id
, p_Dim_Set_Rec.Bsc_Dim_Set_Id
, p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
, L.LANGUAGE_CODE
, USERENV('LANG')
, p_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
, p_Dim_Set_Rec.Bsc_Dim_Level_Help
, p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
, p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM BSC_KPI_DIM_LEVELS_TL T
WHERE T.indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND dim_level_index = p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
AND T.LANGUAGE = L.LANGUAGE_CODE
);
INSERT INTO BSC_KPI_DIM_LEVELS_TL
( indicator
, dim_set_id
, dim_level_index
, language
, source_lang
, name
, help
, total_disp_name
, comp_disp_name
) VALUES
( p_Dim_Set_Rec.Bsc_Kpi_Id
, p_Dim_Set_Rec.Bsc_Dim_Set_Id
, (p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1)
, l_Dim_Set_Rec.Bsc_Language
, l_Dim_Set_Rec.Bsc_Source_Language
, l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
, l_Dim_Set_Rec.Bsc_Dim_Level_Help
, DECODE(p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name, NULL, NULL, l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name)
, DECODE(p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name, NULL, NULL, l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name)
);
ROLLBACK TO DeleteBSCDimLevsPVT;
ROLLBACK TO DeleteBSCDimLevsPVT;
ROLLBACK TO DeleteBSCDimLevsPVT;
ROLLBACK TO DeleteBSCDimLevsPVT;
select distinct a.indicator
, a.dim_set_id
, a.dim_level_index
, a.level_table_name
, a.level_view_name
, a.filter_column
, a.filter_value
, a.default_value
, a.default_type
, a.value_order_by
, a.comp_order_by
, a.level_pk_col
, a.parent_level_index
, a.parent_level_rel
, a.table_relation
, a.parent_level_index2
, a.parent_level_rel2
, a.status
, a.parent_in_total
, a.position
, a.total0
, a.level_display
, a.no_items
, a.default_key_value
, a.user_level0
, a.user_level1
, a.user_level1_default
, a.user_level2
, a.user_level2_default
, a.target_level
, b.name
, b.help
, b.total_disp_name
, b.comp_disp_name
into x_Dim_Set_Rec.Bsc_Kpi_Id
, x_Dim_Set_Rec.Bsc_Dim_Set_Id
, x_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
, x_Dim_Set_Rec.Bsc_Level_Name
, x_Dim_Set_Rec.Bsc_View_Name
, x_Dim_Set_Rec.Bsc_Dset_Filter_Column
, x_Dim_Set_Rec.Bsc_Dset_Filter_Value
, x_Dim_Set_Rec.Bsc_Dset_Default_Value
, x_Dim_Set_Rec.Bsc_Dset_Default_Type
, x_Dim_Set_Rec.Bsc_Dset_Value_Order
, x_Dim_Set_Rec.Bsc_Dset_Comp_Order
, x_Dim_Set_Rec.Bsc_Pk_Col
, x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
, x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
, x_Dim_Set_Rec.Bsc_Dset_Table_Relation
, x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
, x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
, x_Dim_Set_Rec.Bsc_Dset_Status
, x_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
, x_Dim_Set_Rec.Bsc_Dset_Position
, x_Dim_Set_Rec.Bsc_Dset_Total0
, x_Dim_Set_Rec.Bsc_Dset_Level_Display
, x_Dim_Set_Rec.Bsc_Dset_No_Items
, x_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
, x_Dim_Set_Rec.Bsc_Dset_User_Level0
, x_Dim_Set_Rec.Bsc_Dset_User_Level1
, x_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
, x_Dim_Set_Rec.Bsc_Dset_User_Level2
, x_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
, x_Dim_Set_Rec.Bsc_Dset_Target_Level
, x_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
, x_Dim_Set_Rec.Bsc_Dim_Level_Help
, x_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
, x_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
from BSC_KPI_DIM_LEVELS_B a
, BSC_KPI_DIM_LEVELS_TL b
where a.indicator = b.indicator
and a.dim_set_id = b.dim_set_id
and a.dim_level_index = b.dim_level_index
and a.indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and a.dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
and a.level_table_name = p_Dim_Set_Rec.Bsc_Level_Name
and b.language = USERENV('LANG');
procedure Update_Dim_Levels(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SAVEPOINT UpdateBSCDimLevsPVT;
select count(dim_set_id)
into l_count
from BSC_KPI_DIM_SETS_TL
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
select count(distinct dim_level_id)
into l_count
from BSC_SYS_DIM_LEVELS_B
where upper(level_table_name) = upper(p_Dim_Set_Rec.Bsc_Level_Name);
update BSC_KPI_DIM_LEVELS_B a
set dim_level_index = l_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
, level_view_name = l_Dim_Set_Rec.Bsc_View_Name
, filter_column = l_Dim_Set_Rec.Bsc_Dset_Filter_Column
, filter_value = l_Dim_Set_Rec.Bsc_Dset_Filter_Value
, default_value = l_Dim_Set_Rec.Bsc_Dset_Default_Value
, default_type = l_Dim_Set_Rec.Bsc_Dset_Default_Type
, value_order_by = l_Dim_Set_Rec.Bsc_Dset_Value_Order
, comp_order_by = l_Dim_Set_Rec.Bsc_Dset_Comp_Order
, level_pk_col = l_Dim_Set_Rec.Bsc_Pk_Col
, parent_level_index = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
, parent_level_rel = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
, table_relation = l_Dim_Set_Rec.Bsc_Dset_Table_Relation
, parent_level_index2 = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
, parent_level_rel2 = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
, status = l_Dim_Set_Rec.Bsc_Dset_Status
, parent_in_total = l_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
, position = l_Dim_Set_Rec.Bsc_Dset_Position
, total0 = l_Dim_Set_Rec.Bsc_Dset_Total0
, level_display = l_Dim_Set_Rec.Bsc_Dset_Level_Display
, no_items = l_Dim_Set_Rec.Bsc_Dset_No_Items
, default_key_value = l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
, user_level0 = l_Dim_Set_Rec.Bsc_Dset_User_Level0
, user_level1 = l_Dim_Set_Rec.Bsc_Dset_User_Level1
, user_level1_default = l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
, user_level2 = l_Dim_Set_Rec.Bsc_Dset_User_Level2
, user_level2_default = l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
, target_level = l_Dim_Set_Rec.Bsc_Dset_Target_Level
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
and level_table_name = l_Dim_Set_Rec.Bsc_Level_Name;
update BSC_KPI_DIM_LEVELS_TL
set name = l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
, help = l_Dim_Set_Rec.Bsc_Dim_Level_Help
, total_disp_name = l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
, comp_disp_name = l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
, source_lang = USERENV('LANG')
where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
and dim_level_index = l_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO UpdateBSCDimLevsPVT;
ROLLBACK TO UpdateBSCDimLevsPVT;
ROLLBACK TO UpdateBSCDimLevsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
ROLLBACK TO UpdateBSCDimLevsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
end Update_Dim_Levels;
procedure Delete_Dim_Levels(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_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 DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
WHERE DIM_SET_ID = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id
AND (LEVEL_TABLE_NAME = (SELECT LEVEL_TABLE_NAME FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_Dim_Set_Rec.Bsc_Level_Id)
OR LEVEL_TABLE_NAME = 'XXX')
ORDER BY DIM_LEVEL_INDEX DESC;
SAVEPOINT DeleteBSCDimLevsPVT;
DELETE
FROM bsc_kpi_dim_levels_b
WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND dim_level_index = l_dim_index;
DELETE
FROM bsc_kpi_dim_levels_tl
WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND dim_level_index = l_dim_index;
DELETE
FROM bsc_kpi_dim_levels_user
WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
AND dim_level_index = l_dim_index;
ROLLBACK TO DeleteBSCDimLevsPVT;
ROLLBACK TO DeleteBSCDimLevsPVT;
ROLLBACK TO DeleteBSCDimLevsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
ROLLBACK TO DeleteBSCDimLevsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
end Delete_Dim_Levels;
procedure Update_Kpi_Analysis_Options_B(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_option_id NUMBER := 0;
SAVEPOINT UpdateBSCKpiAnaOptsPVT;
update BSC_KPI_ANALYSIS_OPTIONS_B
set dim_set_id = 0
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and analysis_group_id = l_option_id
and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
When the user deletes the dimension set
within an indicator, we assign all the analysis
options the defualt dimension set i.e "0" */
else
update BSC_KPI_ANALYSIS_OPTIONS_B
set dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
and analysis_group_id = l_option_id
and option_id = p_Dim_Set_Rec.Bsc_Option_Id;
ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
end Update_Kpi_Analysis_Options_B;