[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Modified Update Query for BSC_SYS_DIM_LEVELS_TL and Insert |
| Query for BSC_SYS_DIM_LEVELS_TL to handle MLS. |
| 22-APR-2003 ADRAO Modified Evaluate_Circular_Relationship() to allow, PMF |
| to insert Parent Dim Level being its own child Enh#2901823 |
| 07-MAY-2003 Retrieve_Relationship() Added by ADRAO for change Enh#2901823 |
| 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
| 07-JUN-2003 MAHRAO Modified Create_Dim_Level for ALL enhancement |
| 14-JUN-03 mahrao Added Translate_dimesnsion_level procedure for enh# 2842894 |
| 16-JUN-03 ADRAO Modified Get_Next_Value to use sequences for Granular Locking |
| Enh #2828689 |
| 17-JUL-03 mahrao Modified Retr_Updated_Bsc_Dim_Levels procedure |
| as part of forward porting of ALL enhancement to BSC 5.1 |
| Modified Translate_Dimension_Level procedure |
| as part of forward porting of ALL enhancement to BSC 5.1 |
| 16-SEP-03 Adeulgao fixed bug#3108877 |
| 04-NOV-2003 PAJOHRI Bug #3232366 |
| 25-MAR-2004 KAYAMAK Bug #3528143 (removed source language condition) |
| 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
| 10-Aug-04 arhegde bug# 3814375 Appsperf: reduce sql executions |
| 01-NOV-04 Krishna removed the cursor leaks |
| 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
| 15-FEB-05 ppandey Enh #4016669, support ID, Value for Autogenerated Dimension Obj|
| 27-JUN-05 arhegde bug# 4456833 relation_type in retrieve_relationship() |
| 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
| 08-FEB-2006 adrao Bug#5011937 Changed all the calls from Do_Ddl() to Do_Ddl_AT() |
| [Autonomous Transaction] |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_LEVELS_PVT';
SELECT COUNT(Dim_Level_Id)
INTO l_Count
FROM BSC_SYS_DIM_LEVELS_B
WHERE Dim_Level_Id = p_Dim_Level_Id;
procedure Update_Dim_Level(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_dummy varchar2(10);
SAVEPOINT UpdateBSCDimLevPVT;
ROLLBACK TO UpdateBSCDimLevPVT;
ROLLBACK TO UpdateBSCDimLevPVT;
ROLLBACK TO UpdateBSCDimLevPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
ROLLBACK TO UpdateBSCDimLevPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
end Update_Dim_Level;
procedure Delete_Dim_Level(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_sql varchar2(1000);
SELECT LEVEL_TABLE_NAME,
LEVEL_VIEW_NAME,
NVL(SOURCE, 'BSC') SOURCE
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id;
SAVEPOINT DeleteBSCDimLevPVT;
DELETE FROM BSC_DB_TABLES
WHERE Table_Name = 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
DELETE FROM BSC_DB_TABLES_RELS
WHERE Source_Table_Name = 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
' WHERE OBJECT_NAME = :1';
ROLLBACK TO DeleteBSCDimLevPVT;
ROLLBACK TO DeleteBSCDimLevPVT;
ROLLBACK TO DeleteBSCDimLevPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
ROLLBACK TO DeleteBSCDimLevPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
end Delete_Dim_Level;
l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
insert into BSC_SYS_DIM_LEVELS_B( dim_level_id
,level_table_name
,table_type
,level_pk_col
,abbreviation
,value_order_by
,comp_order_by
,custom_group
,user_key_size
,disp_key_size
,level_view_name
,short_name
,source
,created_by --PMD
,creation_date --PMD
,last_updated_by --PMD
,last_update_date --PMD
,last_update_login) --PMD
values( p_Dim_Level_Rec.Bsc_Level_Id
,p_Dim_Level_Rec.Bsc_Level_Name
,p_Dim_Level_Rec.Bsc_Level_Table_Type
,p_Dim_Level_Rec.Bsc_Pk_Col
,p_Dim_Level_Rec.Bsc_Level_Abbreviation
,p_Dim_Level_Rec.Bsc_Level_Value_Order_By
,p_Dim_Level_Rec.Bsc_Level_Comp_Order_By
,p_Dim_Level_Rec.Bsc_Level_Custom_Group
,p_Dim_Level_Rec.Bsc_Level_User_Key_Size
,p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
,l_level_view_name
,p_Dim_Level_Rec.Bsc_Level_Short_Name
,p_Dim_Level_Rec.Bsc_Source
,p_Dim_Level_Rec.Bsc_Created_By --PMD
,l_Dim_Level_Rec.Bsc_Last_Update_Date --PMD
,p_Dim_Level_Rec.Bsc_Last_Updated_By --PMD
,l_Dim_Level_Rec.Bsc_Last_Update_Date --PMD
,p_Dim_Level_Rec.Bsc_Last_Update_Login); --PMD
insert into BSC_SYS_DIM_LEVELS_TL( dim_level_id
,language
,source_lang
,name
,help
,total_disp_name
,comp_disp_name
)
SELECT p_Dim_Level_Rec.Bsc_Level_Id
, L.LANGUAGE_CODE
, USERENV('LANG')
, p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
, p_Dim_Level_Rec.Bsc_Dim_Level_Help
, p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
, p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM BSC_SYS_DIM_LEVELS_TL T
WHERE T.DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
AND T.LANGUAGE = L.LANGUAGE_CODE);
select a.level_view_name
,a.level_table_name
,a.table_type
,a.level_pk_col
,a.abbreviation
,a.value_order_by
,a.comp_order_by
,a.custom_group
,a.user_key_size
,a.disp_key_size
,a.short_name
,a.source
,b.name
,b.help
,b.total_disp_name
,b.comp_disp_name
,a.created_by -- PMD
,a.creation_date -- PMD
,a.last_updated_by -- PMD
,a.last_update_date -- PMD
,a.last_update_login -- PMD
into x_Dim_Level_Rec.Bsc_Level_View_Name
,x_Dim_Level_Rec.Bsc_Level_Name
,x_Dim_Level_Rec.Bsc_Level_Table_Type
,x_Dim_Level_Rec.Bsc_Pk_Col
,x_Dim_Level_Rec.Bsc_Level_Abbreviation
,x_Dim_Level_Rec.Bsc_Level_Value_Order_By
,x_Dim_Level_Rec.Bsc_Level_Comp_Order_By
,x_Dim_Level_Rec.Bsc_Level_Custom_Group
,x_Dim_Level_Rec.Bsc_Level_User_Key_Size
,x_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
,x_Dim_Level_Rec.Bsc_Level_Short_Name
,x_Dim_Level_Rec.Bsc_Source
,x_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
,x_Dim_Level_Rec.Bsc_Dim_Level_Help
,x_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
,x_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
,x_Dim_Level_Rec.Bsc_Created_By -- PMD
,x_Dim_Level_Rec.Bsc_Creation_Date -- PMD
,x_Dim_Level_Rec.Bsc_Last_Updated_By -- PMD
,x_Dim_Level_Rec.Bsc_Last_Update_Date -- PMD
,x_Dim_Level_Rec.Bsc_Last_Update_Login -- PMD
from BSC_SYS_DIM_LEVELS_B a
,BSC_SYS_DIM_LEVELS_TL b
where a.dim_level_id = b.dim_level_id
and a.dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
and b.language = NVL(p_Dim_Level_Rec.Bsc_Language,USERENV('LANG')); --Bug #3528143
procedure Update_Bsc_Dim_Levels_Md(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
SAVEPOINT UpdateBSCDimLevMdPVT;
if p_Dim_Level_Rec.Bsc_Last_Updated_By is not null then
l_Dim_Level_Rec.Bsc_Last_Updated_By := p_Dim_Level_Rec.Bsc_Last_Updated_By;
l_Dim_Level_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID; -- not null column
if p_Dim_Level_Rec.Bsc_Last_Update_Login is not null then
l_Dim_Level_Rec.Bsc_Last_Update_Login := p_Dim_Level_Rec.Bsc_Last_Update_Login;
l_Dim_Level_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
update BSC_SYS_DIM_LEVELS_B
set level_table_name = l_Dim_Level_Rec.Bsc_Level_Name
,table_type = l_Dim_Level_Rec.Bsc_Level_Table_Type
,level_pk_col = l_Dim_Level_Rec.Bsc_Pk_Col
,abbreviation = l_Dim_Level_Rec.Bsc_Level_Abbreviation
,value_order_by = l_Dim_Level_Rec.Bsc_Level_Value_Order_By
,comp_order_by = l_Dim_Level_Rec.Bsc_Level_Comp_Order_By
,custom_group = l_Dim_Level_Rec.Bsc_Level_Custom_Group
,user_key_size = l_Dim_Level_Rec.Bsc_Level_User_Key_Size
,disp_key_size = l_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
,level_view_name = l_level_view_name
,source = l_Dim_Level_Rec.Bsc_Source
,last_updated_by = l_Dim_Level_Rec.Bsc_Last_Updated_By -- PMD
,last_update_date = l_Dim_Level_Rec.Bsc_Last_Update_Date -- PMD
,last_update_login = l_Dim_Level_Rec.Bsc_Last_Update_Login -- PMD
where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id;
update BSC_SYS_DIM_LEVELS_TL
set name = l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
,help = l_Dim_Level_Rec.Bsc_Dim_Level_Help
,total_disp_name = l_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
,comp_disp_name = l_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
,SOURCE_LANG = userenv('LANG')
where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO UpdateBSCDimLevMdPVT;
ROLLBACK TO UpdateBSCDimLevMdPVT;
ROLLBACK TO UpdateBSCDimLevMdPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
ROLLBACK TO UpdateBSCDimLevMdPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
end Update_Bsc_Dim_Levels_Md;
procedure Delete_Bsc_Dim_Levels_Md(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_id number;
SAVEPOINT DeleteBSCDimLevMdPVT;
delete from BSC_SYS_DIM_LEVELS_B
where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
delete from BSC_SYS_DIM_LEVELS_TL
where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
DELETE FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
DELETE FROM BSC_SYS_DIM_LEVEL_RELS
WHERE dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
OR parent_dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
ROLLBACK TO DeleteBSCDimLevMdPVT;
ROLLBACK TO DeleteBSCDimLevMdPVT;
ROLLBACK TO DeleteBSCDimLevMdPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
ROLLBACK TO DeleteBSCDimLevMdPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
end Delete_Bsc_Dim_Levels_Md;
insert into BSC_SYS_DIM_LEVEL_COLS( dim_level_id
,column_name
,column_type)
values( p_Dim_Level_Rec.Bsc_Level_Id
,p_Dim_Level_Rec.Bsc_Level_Column_Name
,p_Dim_Level_Rec.Bsc_Level_Column_Type);
select distinct column_name
into x_Dim_Level_Rec.Bsc_Level_Column_Name
from BSC_SYS_DIM_LEVEL_COLS
where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
and column_type = p_Dim_Level_Rec.Bsc_Level_Column_Type;
procedure Update_Bsc_Sys_Dim_Lvl_Cols(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
SAVEPOINT UpdateBSCSysDimLevColsPVT;
update BSC_SYS_DIM_LEVEL_COLS
set column_name = l_Dim_Level_Rec.Bsc_Level_Column_Name
where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id
and column_type = l_Dim_Level_Rec.Bsc_Level_Column_Type;
ROLLBACK TO UpdateBSCSysDimLevColsPVT;
ROLLBACK TO UpdateBSCSysDimLevColsPVT;
ROLLBACK TO UpdateBSCSysDimLevColsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
ROLLBACK TO UpdateBSCSysDimLevColsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
end Update_Bsc_Sys_Dim_Lvl_Cols;
procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_id number;
SAVEPOINT DeleteBSCSysDimLevColsPVT;
delete from BSC_SYS_DIM_LEVEL_COLS
where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
ROLLBACK TO DeleteBSCSysDimLevColsPVT;
ROLLBACK TO DeleteBSCSysDimLevColsPVT;
ROLLBACK TO DeleteBSCSysDimLevColsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
ROLLBACK TO DeleteBSCSysDimLevColsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
end Delete_Bsc_Sys_Dim_Lvl_Cols;
SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL
INTO l_next
FROM DUAL;
SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL
INTO l_next
FROM DUAL;
SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL
INTO l_next
FROM DUAL;
SELECT BSC_SYS_DATASET_ID_S.NEXTVAL
INTO l_next
FROM DUAL;
SELECT BSC_INDICATOR_ID_S.NEXTVAL
INTO l_next
FROM DUAL;
l_sql := 'select NVL(max(' || p_column_name || '), 0) + 1 ' ||
' from ' || p_table_name;
l_sql := 'select distinct ' || p_column_ID_name ||
' from ' || p_table_name ||
' where upper(' || p_column_name || ') = upper(:1)';
SELECT COUNT(*) INTO l_Count
FROM BSC_SYS_DIM_LEVELS_B
WHERE Level_Table_Name = p_level_name;
SELECT COUNT(*) INTO l_Count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE UPPER(NAME) = UPPER(p_group_name);
l_sql := 'select distinct d.short_name, substr(e.name, 1, 45) ' ||
' from bis_indicators a ' ||
' ,bis_indicator_dimensions b ' ||
' ,bis_dimensions_tl c ' ||
' ,bis_levels d ' ||
' ,bis_levels_tl e ' ||
' ,bis_indicators_tl f ' ||
' ,bis_dimensions h ' ||
' ,( select distinct dimension1_level_id as tgt_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:1)' ||
' UNION select distinct dimension2_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:2)' ||
' UNION select distinct dimension3_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:3)' ||
' UNION select distinct dimension4_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:4)' ||
' UNION select distinct dimension5_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:5)' ||
' UNION select distinct dimension6_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:6)' ||
' UNION select distinct dimension7_level_id from bis_target_levels b, bis_indicators a ' ||
' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:7) ) g' ||
' where ' ||
' upper(a.short_name) like upper(:8) and ' ||
' a.indicator_id = b.indicator_id and ' ||
' b.dimension_id = c.dimension_id and ' ||
' c.dimension_id = d.dimension_id and ' ||
' d.level_id = e.level_id and ' ||
' a.indicator_id = f.indicator_id and ' ||
' d.level_id = g.tgt_level_id and ' ||
' b.dimension_id = h.dimension_id and ' ||
' h.short_name = :9';
l_sql := 'select count(distinct ' || p_Table_Column_Name || ')' ||
' from ' || p_Table_Name ||
' where ' || p_Table_Column_Name || ' = :1'; --|| p_Column_Value;
l_sql := 'select distinct ' || p_Table_Name_Column || ')' ||
' from ' || p_Table_Name ||
' where ' || p_Table_Id_Column || ' = :1' ;
SELECT MEANING FROM BSC_LOOKUPS
WHERE LOOKUP_TYPE = 'BSC_COMMON_UI'
AND LOOKUP_CODE IN ('TABLE', 'FUNCTION')
*/
l_temp_col :=
get_Relation_Column (
p_Dim_Level_Rec.Bsc_Level_Id
, p_Dim_Level_Rec.Bsc_Parent_Level_Id
, p_Dim_Level_Rec.Bsc_Relation_Type
, x_return_status
, x_msg_count
, x_msg_data
);
INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
RELATION_COL,
PARENT_DIM_LEVEL_ID,
RELATION_TYPE,
DIRECT_RELATION,
DATA_SOURCE_TYPE,
DATA_SOURCE)
VALUES (p_Dim_Level_Rec.Bsc_Level_Id,
l_temp_col,
p_Dim_Level_Rec.Bsc_Parent_Level_Id,
p_Dim_Level_Rec.Bsc_Relation_Type,
null,
p_Dim_Level_Rec.Bsc_Data_Source_Type,
p_Dim_Level_Rec.Bsc_Data_Source);
For MxN relationship types, we need to insert two columns,
With the Parent and the Child interchanged.
*/
INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
RELATION_COL,
PARENT_DIM_LEVEL_ID,
RELATION_TYPE,
DIRECT_RELATION,
DATA_SOURCE_TYPE,
DATA_SOURCE)
VALUES (p_Dim_Level_Rec.Bsc_Level_Id,
l_temp_col,
p_Dim_Level_Rec.Bsc_Parent_Level_Id,
p_Dim_Level_Rec.Bsc_Relation_Type,
null,
null,
null); -- There will be no Data Source/Data Type for BSC Dim Object
INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
RELATION_COL,
PARENT_DIM_LEVEL_ID,
RELATION_TYPE,
DIRECT_RELATION,
DATA_SOURCE_TYPE,
DATA_SOURCE)
VALUES (p_Dim_Level_Rec.Bsc_Parent_Level_Id,
l_temp_col,
p_Dim_Level_Rec.Bsc_Level_Id,
p_Dim_Level_Rec.Bsc_Relation_Type,
null,
null,
null); -- There will be no Data Source/Data Type for BSC Dim Object
Delete_Dim_Level_Relation:
Parameters: p_Dim_Level_Rec.Bsc_Level_Id
p_Dim_Level_Rec.Bsc_Parent_Level_Id
---------------------------------------------------------------------------------------*/
PROCEDURE Delete_Dim_Level_Relation(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
SAVEPOINT DeleteBSCDimLevRelsPVT;
DELETE FROM BSC_SYS_DIM_LEVEL_RELS
WHERE DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
AND PARENT_DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Parent_Level_Id;
ROLLBACK TO DeleteBSCDimLevRelsPVT;
ROLLBACK TO DeleteBSCDimLevRelsPVT;
ROLLBACK TO DeleteBSCDimLevRelsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
ROLLBACK TO DeleteBSCDimLevRelsPVT;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
END Delete_Dim_Level_Relation;
SELECT PARENT_DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE DIM_LEVEL_ID = p_Parent_Dim_Level_Id
AND RELATION_TYPE = 1;
SELECT COUNT(dim_level_id)
INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_Parent_Dim_Level_Id
AND source = 'PMF';
SELECT COUNT(dim_level_id)
INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE dim_level_id = p_Parent_Dim_Level_Id
AND source = 'PMF';
SELECT NAME INTO v_Dim_Level_Name FROM BSC_SYS_DIM_LEVELS_VL
WHERE DIM_LEVEL_ID = p_Child_level_Id;
Select DIM_LEVEL_ID
into v_Id
from BSC_SYS_DIM_LEVELS_B
where SHORT_NAME = p_Short_Name;
SELECT LR.DIM_LEVEL_ID
, LR.PARENT_DIM_LEVEL_ID
, DL.SHORT_NAME
, PL.SHORT_NAME
, DL.SOURCE
, PL.NAME
, PL.SOURCE
, LR.RELATION_COL
, LR.DATA_SOURCE_TYPE
, LR.DATA_SOURCE
, LR.RELATION_TYPE
FROM BSC_SYS_DIM_LEVEL_RELS LR, BSC_SYS_DIM_LEVELS_VL DL, BSC_SYS_DIM_LEVELS_VL PL
WHERE DL.dim_level_id = LR.dim_level_id
AND PL.dim_level_id = LR.parent_dim_level_id
AND LR.DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
AND LR.PARENT_DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Parent_Level_Id;
SELECT LR.DIM_LEVEL_ID
, LR.PARENT_DIM_LEVEL_ID
, DL.SHORT_NAME
, PL.SHORT_NAME
, DL.SOURCE
, PL.NAME
, PL.SOURCE
, LR.RELATION_COL
, LR.DATA_SOURCE_TYPE
, LR.DATA_SOURCE
, LR.RELATION_TYPE
FROM BSC_SYS_DIM_LEVEL_RELS LR, BSC_SYS_DIM_LEVELS_VL DL, BSC_SYS_DIM_LEVELS_VL PL
WHERE DL.dim_level_id = LR.dim_level_id
AND PL.dim_level_id = LR.parent_dim_level_id
AND DL.SHORT_NAME = p_Dim_Level_Rec.Bsc_Level_Short_Name
AND PL.SHORT_NAME = p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name;
SELECT COUNT(dim_level_id) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE short_name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
SELECT COUNT(dim_level_id) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE short_name = p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name;
SELECT ABBREVIATION
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_Child_level_Id;
SELECT ABBREVIATION
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_Parent_level_Id;
SELECT LEVEL_PK_COL
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = p_Parent_level_Id;
PROCEDURE Retr_Updated_Bsc_Dim_Levels(
p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status:= FND_API.G_RET_STS_SUCCESS;
SELECT
dim_level_id
,short_name
,name
,help
,total_disp_name
,comp_disp_name
INTO
x_Dim_Level_Rec.Bsc_Level_Id
,x_Dim_Level_Rec.Bsc_Level_Short_Name
,x_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
,x_Dim_Level_Rec.Bsc_Dim_Level_Help
,x_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
,x_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
FROM BSC_SYS_DIM_LEVELS_VL
WHERE short_name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
x_msg_data := 'BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels '||SQLERRM;
END Retr_Updated_Bsc_Dim_Levels;
BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels
(p_Dim_Level_Rec => l_Dim_Level_Rec_In
,x_Dim_Level_Rec => l_Dim_Level_Rec_out
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE bsc_sys_dim_levels_tl
SET name = l_Dim_Level_Rec_Out.Bsc_Dim_Level_Long_Name
,total_disp_name = l_Dim_Level_Rec_Out.Bsc_Dim_Tot_Disp_Name
,comp_disp_name = l_Dim_Level_Rec_Out.Bsc_Dim_Comp_Disp_Name
,help = l_Dim_Level_Rec_Out.Bsc_Dim_Level_Help
,SOURCE_LANG = userenv('LANG')
WHERE dim_level_id = l_Dim_Level_Rec_Out.Bsc_Level_Id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT Dim_Level_Id
INTO l_Dim_Level_Id
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
UPDATE BSC_SYS_DIM_LEVELS_TL
SET NAME = p_dim_level_rec.Bsc_Dim_Level_Long_Name
,HELP = p_dim_level_rec.Bsc_Dim_Level_Help
,SOURCE_LANG = p_dim_level_rec.Bsc_Source_Language
WHERE DIM_LEVEL_ID = l_Dim_Level_Id
AND LANGUAGE = p_dim_level_rec.Bsc_Language;