The following lines contain the word 'select', 'insert', 'update' or 'delete':
| select query. |
| 29-MAY-03 All Enhancement Phase I- Functions user group short_name if no id |
| 07-JUN-03 mahrao Modified for ALL enhancement |
| 13-JUN-03 ADEULGAO modified for BUG# 2878840 |
| 13-JUN-03 ADEULGAO modified procedure Create_Dimension_Group for BUG# 2878840 |
| 14-JUN-03 mahrao Added Translate_dimesnsion_group procedure |
| 17-JUL-03 mahrao Modified Translate_dimesnsion_group procedure |
| as part of forward porting of ALL enhancement to BSC 5.1 |
| Modified Retrieve_Dim_Group procedure |
| as part of forward porting of ALL enhancement to BSC 5.1 |
| 29-OCT-2003 mahrao bug#3209967 Added a column to bsc_sys_dim_levels_by_group |
| 17-NOV-2003 PAJOHRI Bug #3232366 |
| 17-NOV-2003 ADRAO Bug #3236356 - Removed comments which has Validate_Value() |
| 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
| 13-Oct-04 rpenneru Modified for bug#3945655 |
| 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
| 01-FEB-05 hengliu Modified for bug#4104065 - WHERE_CLAUSE can be null |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_GROUPS_PVT';
SELECT count(dim_group_id)
INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id = p_dim_group_id;
if l_Dim_Grp_Rec.Bsc_Last_Updated_By is null then
l_Dim_Grp_Rec.Bsc_Last_Updated_By := 0;
l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
insert into BSC_SYS_DIM_GROUPS_TL( dim_group_id
,language
,source_lang
,name
,short_name
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
select l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
,L.LANGUAGE_CODE
,userenv('LANG')
,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
,l_Dim_Grp_Rec.Bsc_Created_By -- PMD
,l_Dim_Grp_Rec.Bsc_Last_Update_Date -- PMD
,l_Dim_Grp_Rec.Bsc_Last_Updated_By -- PMD
,l_Dim_Grp_Rec.Bsc_Last_Update_Date -- PMD
,l_Dim_Grp_Rec.Bsc_Last_Update_Login -- PMD
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BSC_SYS_DIM_GROUPS_TL T
where T.dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
select distinct name
into x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
from BSC_SYS_DIM_GROUPS_VL
where dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
procedure Update_Dimension_Group(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_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_group_id
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_grp_short_name;
SAVEPOINT UpdateBSCDimGrpPVT;
IF p_Dim_Grp_Rec.Bsc_Last_Updated_By IS NULL THEN -- Cannot update p_Dim_Grp_Rec
l_Dim_Grp_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;
IF p_Dim_Grp_Rec.Bsc_Last_Update_Login IS NULL THEN
l_Dim_Grp_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
update BSC_SYS_DIM_GROUPS_TL
set name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
,source_lang = userenv('LANG')
,last_updated_by = l_Dim_Grp_Rec.Bsc_Last_Updated_By
,last_update_date = l_Dim_Grp_Rec.Bsc_Last_Update_Date
,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
ROLLBACK TO UpdateBSCDimGrpPVT;
ROLLBACK TO UpdateBSCDimGrpPVT;
ROLLBACK TO UpdateBSCDimGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
ROLLBACK TO UpdateBSCDimGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
end Update_Dimension_Group;
procedure Delete_Dimension_Group(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
SELECT dim_group_id
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_grp_short_name;
SAVEPOINT DeleteBSCDimGrpPVT;
select count(dim_group_id)
into l_count
from BSC_SYS_DIM_GROUPS_TL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select distinct dim_group_id
into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
from BSC_SYS_DIM_GROUPS_VL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select count(dim_group_id)
into l_count
from BSC_SYS_DIM_LEVELS_BY_GROUP
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
delete from BSC_SYS_DIM_GROUPS_TL
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
ROLLBACK TO DeleteBSCDimGrpPVT;
ROLLBACK TO DeleteBSCDimGrpPVT;
ROLLBACK TO DeleteBSCDimGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
ROLLBACK TO DeleteBSCDimGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
end Delete_Dimension_Group;
SELECT dim_group_id
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_grp_short_name;
select count(dim_group_id)
into l_count
from BSC_SYS_DIM_GROUPS_TL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select distinct dim_group_id
into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
from BSC_SYS_DIM_GROUPS_VL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select count(*)
into l_count
from BSC_SYS_DIM_LEVELS_BY_GROUP
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
select NVL((MAX(dim_level_index) + 1), 0)
into l_Dim_Grp_Rec.Bsc_Dim_Level_Index
from BSC_SYS_DIM_LEVELS_BY_GROUP
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
insert into BSC_SYS_DIM_LEVELS_BY_GROUP( dim_group_id
,dim_level_id
,dim_level_index
,total_flag
,comparison_flag
,filter_column
,filter_value
,default_value
,default_type
,parent_in_total
,no_items
,where_clause)
values( l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
,l_Dim_Grp_Rec.Bsc_Level_Id
,l_Dim_Grp_Rec.Bsc_Dim_Level_Index
,l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
,l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
,l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
,l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
,l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause);
SELECT DISTINCT dim_level_index
,total_flag
,comparison_flag
,filter_column
,filter_value
,default_value
,default_type
,parent_in_total
,no_items
,where_clause
INTO x_Dim_Grp_Rec.Bsc_Dim_Level_Index
,x_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
,x_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
,x_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
,x_Dim_Grp_Rec.Bsc_Group_Level_No_Items
,x_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
AND dim_level_id = p_Dim_Grp_Rec.Bsc_Level_Id;
procedure Update_Dim_Levels_In_Group(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
SELECT dim_group_id
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_grp_short_name;
SAVEPOINT UpdateBSCDimLevInGrpPVT;
select count(dim_group_id)
into l_count
from BSC_SYS_DIM_GROUPS_TL
where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select distinct dim_group_id
into l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Id
from BSC_SYS_DIM_GROUPS_VL
where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
update BSC_SYS_DIM_LEVELS_BY_GROUP
set dim_level_index = l_Dim_Grp_Rec.Bsc_Dim_Level_Index
,total_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
,comparison_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
,filter_column = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
,filter_value = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
,default_value = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
,default_type = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
,parent_in_total = l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
,no_items = l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
,where_clause = l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
ROLLBACK TO UpdateBSCDimLevInGrpPVT;
ROLLBACK TO UpdateBSCDimLevInGrpPVT;
ROLLBACK TO UpdateBSCDimLevInGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
ROLLBACK TO UpdateBSCDimLevInGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
end Update_Dim_Levels_In_Group;
procedure Delete_Dim_Levels_In_Group(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
SELECT dim_group_id
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_grp_short_name;
SAVEPOINT DeleteBSCDimLevInGrpPVT;
select count(dim_group_id)
into l_count
from BSC_SYS_DIM_GROUPS_TL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
select distinct dim_group_id
into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
from BSC_SYS_DIM_GROUPS_VL
where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
delete from BSC_SYS_DIM_LEVELS_BY_GROUP
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
delete from BSC_SYS_DIM_LEVELS_BY_GROUP
where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
ROLLBACK TO DeleteBSCDimLevInGrpPVT;
ROLLBACK TO DeleteBSCDimLevInGrpPVT;
ROLLBACK TO DeleteBSCDimLevInGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
ROLLBACK TO DeleteBSCDimLevInGrpPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
end Delete_Dim_Levels_In_Group;
Select distinct DIM_GROUP_ID
into v_Id
from BSC_SYS_DIM_GROUPS_TL
where SHORT_NAME = p_Short_Name;
l_Dim_Grp_rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
UPDATE bsc_sys_dim_groups_tl
SET name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
,source_lang = userenv('LANG')
,last_updated_by = NVL(l_Dim_Grp_Rec.Bsc_Last_Updated_By, p_Dim_Grp_Rec.Bsc_Last_Updated_By)
,last_update_date = l_Dim_Grp_rec.Bsc_Last_Update_Date
,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
SELECT distinct name, dim_group_id, short_name
INTO
x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name;
UPDATE BSC_SYS_DIM_GROUPS_TL
SET NAME = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
,SOURCE_LANG = p_Dim_Grp_Rec.Bsc_Source_Language
,LAST_UPDATE_DATE = p_Dim_Grp_Rec.Bsc_Last_Update_Date
WHERE SHORT_NAME = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
AND LANGUAGE = p_Dim_Grp_Rec.Bsc_Language;