[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql := 'SELECT NAME FROM BSC_SYS_DATASETS_VL WHERE DATASET_ID =:1';
l_sql := 'SELECT MEASURE_COL FROM BSC_SYS_MEASURES WHERE MEASURE_ID =:1';
SELECT LAST_UPDATE_DATE
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID =:1';
SELECT LAST_UPDATE_DATE
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID =:1';
Bug#4045278: Overloaded for setting Time Stamp for Dataset to take in last_update_date parameter
-------------------------------------------------------------------------------------------*/
Procedure SET_TIME_STAMP_DATASET (
p_dim_set_id IN number
,p_lud IN BSC_SYS_DATASETS_B.LAST_UPDATE_DATE%TYPE
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_sql varchar2(32000);
UPDATE BSC_SYS_DATASETS_B
SET LAST_UPDATE_DATE = :1
WHERE DATASET_ID =:2';
Bug#4045278: Overloaded for setting Time Stamp for Datasource to take in last_update_date parameter
-------------------------------------------------------------------------------------------*/
Procedure SET_TIME_STAMP_DATASOURCE (
p_measure_id IN number
,p_lud IN BSC_SYS_MEASURES.LAST_UPDATE_DATE%TYPE
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_sql varchar2(32000);
UPDATE BSC_SYS_MEASURES
SET LAST_UPDATE_DATE = :1
WHERE MEASURE_ID =:2';
l_last_update_date date;
l_meaning varchar2(60); -- Added by ADRAO for Delete Message.
l_sql := 'SELECT DATASET_ID, LAST_UPDATE_DATE
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID =:1
FOR UPDATE NOWAIT';
fetch l_cursor into l_object_id, l_last_update_date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE'); -- ADRAO changed Measage
if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
--dbms_output.put_line(' p_dataset_id = ' || p_dataset_id || ' updated by other user ');
l_last_update_date := l_last_update_date;
SELECT NAME, MEASURE_ID1, OPERATION, MEASURE_ID2
FROM BSC_SYS_DATASETS_VL
WHERE DATASET_ID =:1';
l_last_update_date date;
SELECT MEASURE_ID, LAST_UPDATE_DATE
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID =:1
FOR UPDATE NOWAIT';
fetch l_cursor into l_object_id, l_last_update_date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_MEASURE');
if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
--dbms_output.put_line(' p_measure_id = ' || p_measure_id || ' updated by other user ');
l_last_update_date := l_last_update_date;
l_sql := 'SELECT NAME FROM BSC_SYS_DIM_LEVELS_VL WHERE DIM_LEVEL_ID =:1';
l_sql := 'SELECT NAME FROM BSC_SYS_DIM_GROUPS_VL WHERE DIM_GROUP_ID =:1';
l_sql := 'SELECT NAME FROM bsc_kpi_dim_sets_vl
WHERE INDICATOR =:1 AND DIM_SET_ID =:2';
l_sql := 'SELECT NAME FROM BSC_KPIS_VL WHERE INDICATOR =:1';
l_sql := 'SELECT NAME FROM BSC_TAB_VIEWS_VL WHERE TAB_ID =:1 AND TAB_VIEW_ID=:2';
Procedure get_selected_dim_objs(
p_dimension_id IN NUMBER
,x_selected_dim_objs OUT NOCOPY t_lock_table
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
SELECT DIM_LEVEL_ID
FROM BSC_sys_dim_levels_by_group
WHERE DIM_GROUP_ID =:1
ORDER BY DIM_LEVEL_INDEX';
x_selected_dim_objs(l_lock_Rec.obj_key1) := l_lock_Rec;
END get_selected_dim_objs;
Procedure get_selected_dimensions(
p_dim_obj_id IN NUMBER
,x_selected_dimensions OUT NOCOPY t_lock_table
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
SELECT DIM_GROUP_ID
FROM BSC_sys_dim_levels_by_group
WHERE DIM_LEVEL_ID =:1';
x_selected_dimensions(l_lock_Rec.obj_key1) := l_lock_Rec;
END get_selected_dimensions;
It compare selected and previous objects and return the
impacted object.
It used t_lock_Rec.obj_Flag : 'D'= Delected , 'A'= Added
object Id is used as t_lock_table index
----------------------------------------------------------------------------*/
Procedure get_impacted_objects(
p_selected_objects IN t_lock_table
,p_previous_objects IN t_lock_table
,x_impacted_objects OUT NOCOPY t_lock_table
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_lock_Rec t_lock_Rec;
if p_selected_objects.COUNT > 0 then
object_id := p_selected_objects.FIRST;
l_lock_Rec := p_selected_objects(object_id);
IF object_id = p_selected_objects.LAST then
exit;
object_id := p_selected_objects.NEXT(object_id);
/* Find deleted objects */
if p_previous_objects.COUNT > 0 then
object_id := p_previous_objects.FIRST;
if NOT p_selected_objects.EXISTS(object_id) then
l_lock_Rec := p_previous_objects(object_id);
p_selected_dimensions IN t_lock_table
,x_selected_dim_sets OUT NOCOPY t_lock_table
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
if p_selected_dimensions.COUNT > 0 then
/* build the dimanic query */
l_sql := '
SELECT DISTINCT INDICATOR, DIM_SET_ID
FROM bsc_kpi_dim_groups
WHERE';
l_index := p_selected_dimensions.FIRST;
l_sql := l_sql || ' DIM_GROUP_ID = ' || p_selected_dimensions(l_index).obj_key1;
IF l_index <> p_selected_dimensions.LAST then
l_sql := l_sql || ' OR ' ;
l_index := p_selected_dimensions.NEXT(l_index);
x_selected_dim_sets(l_count) := l_lock_Rec;
,x_selected_dim_sets OUT NOCOPY t_lock_table
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
Select DISTINCT C.K, C.DS
FROM (
SELECT K.INDICATOR K, K.DIM_SET_ID DS, S.DIM_LEVEL_ID DL
FROM bsc_kpi_dim_levels_b K,
bsc_sys_dim_levels_b s
WHERE K.LEVEL_TABLE_NAME = S.LEVEL_TABLE_NAME
)P,
( SELECT K.INDICATOR K, K.DIM_SET_ID DS, S.DIM_LEVEL_ID DL
FROM bsc_kpi_dim_levels_b K,
bsc_sys_dim_levels_b s
WHERE K.LEVEL_TABLE_NAME = S.LEVEL_TABLE_NAME
)C
WHERE C.K = P.K
AND C.DS = P.DS
AND( C.DL =:1 AND P.DL =:2 )';
x_selected_dim_sets(l_count) := l_lock_Rec;
l_last_update_date date;
SELECT DIM_LEVEL_ID, LAST_UPDATE_DATE
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID =:1
FOR UPDATE NOWAIT';
fetch l_cursor into l_object_id, l_last_update_date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_LEVEL');
if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
--dbms_output.put_line('Dimension Level Id = ' || p_dim_level_id || ' updated by other user ');
l_last_update_date := l_last_update_date;
l_last_update_date date;
SELECT DIM_GROUP_ID
, LAST_UPDATE_DATE
FROM BSC_SYS_DIM_GROUPS_TL
WHERE DIM_GROUP_ID = :1
AND LANGUAGE = USERENV(''LANG'')
ORDER BY LAST_UPDATE_DATE DESC
FOR UPDATE NOWAIT';
fetch l_cursor into l_object_id, l_last_update_date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_GROUP');
if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
--dbms_output.put_line('Dimension Group Id = ' || p_dim_group_id || ' updated by other user ');
l_last_update_date date;
SELECT INDICATOR, DIM_SET_ID, LAST_UPDATE_DATE
FROM bsc_kpi_dim_sets_tl
WHERE INDICATOR =:1
AND DIM_SET_ID =:2
ORDER BY LAST_UPDATE_DATE DESC
FOR UPDATE NOWAIT';
fetch l_cursor into l_kpi_id, l_dim_set_id, l_last_update_date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_SET');
if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
--dbms_output.put_line('Kpi = ' || p_kpi_id || ' Dimension Set Id = ' || p_dim_set_id || ' updated by other user ');
l_last_update_date varchar2(50);
SELECT PROPERTY_CODE
FROM BSC_KPI_PROPERTIES
WHERE PROPERTY_CODE = ''LOCK_INDICATOR''
AND INDICATOR =:1
FOR UPDATE NOWAIT';
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_KPI_1');
l_last_update_date := get_time_stamp_kpi( p_kpi_Id);
if p_time_stamp <> l_last_update_date then
--dbms_output.put_line('Kpi = ' || p_kpi_id || ' updated by other user ');
SELECT INDICATOR, DIM_SET_ID
FROM bsc_kpi_dim_sets_tl
WHERE INDICATOR =:1
FOR UPDATE NOWAIT';
SELECT LAST_UPDATE_DATE
FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID =:1';
SELECT LAST_UPDATE_DATE
FROM BSC_SYS_DIM_GROUPS_VL
WHERE DIM_GROUP_ID = :1';
SELECT MAX(LAST_UPDATE_DATE)
FROM bsc_kpi_dim_sets_tl
WHERE INDICATOR =:1
AND DIM_SET_ID =:2';
l_sql := 'SELECT LAST_UPDATE_DATE ' ||
' FROM BSC_KPIS_B ' ||
' WHERE INDICATOR =:1' ;
temp BSC_TABS_B.last_update_date%TYPE;
l_sql := 'SELECT LAST_UPDATE_DATE ' ||
' FROM BSC_TABS_B ' ||
' WHERE TAB_ID =:1 ';
l_sql := 'SELECT LAST_UPDATE_DATE ' ||
' FROM BSC_TAB_VIEWS_B ' ||
' WHERE TAB_ID =:1 AND TAB_VIEW_ID =:2' ;
UPDATE BSC_SYS_DIM_LEVELS_B
SET LAST_UPDATE_DATE = sysdate
WHERE DIM_LEVEL_ID =:1';
UPDATE BSC_SYS_DIM_GROUPS_TL
SET LAST_UPDATE_DATE = SYSDATE
WHERE DIM_GROUP_ID =:1
AND USERENV(''LANG'') IN (LANGUAGE, SOURCE_LANG)';
UPDATE bsc_kpi_dim_sets_tl
SET LAST_UPDATE_DATE = sysdate
WHERE INDICATOR =:1
AND DIM_SET_ID =:2';
l_sql := ' UPDATE BSC_KPIS_B ' ||
' SET LAST_UPDATE_DATE = SYSDATE ' ||
' WHERE INDICATOR =:1';
p_selected_dim_objets: Array with the Ids corresponding to the Dimesion Objects
that will be assigned to the new dimension.
-------------------------------------------------------------------------------------------*/
Procedure LOCK_CREATE_DIMENSION (
p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_index number;
if p_selected_dim_objets.COUNT > 0 then
l_index := p_selected_dim_objets.FIRST;
l_dim_level_id := p_selected_dim_objets(l_index);
if l_index = p_selected_dim_objets.LAST then
exit;
l_index := p_selected_dim_objets.NEXT(l_index);
Procedure LOCK_UPDATE_DIMENSION
This Procedure will make all the necessaries locks to Update a Dimension (Dimension Group)
according with the PMD UI for 'Performance Measures > Dimensions > Update Dimension'
This procedure will lock the dimension passed in the parameter p_dimension_id,
the dimension objects passed in the parameter p_selected_dim_objets,
and the dimension set (in the kpis) that uses the dimension when it is necessary.
p_dimension_id: Dimension Id (Dimension Group) to update
p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
that will have the dimension.
p_time_stamp: Last update of dimension information changed by the user
-------------------------------------------------------------------------------------------*/
Procedure LOCK_UPDATE_DIMENSION (
p_dimension_id IN number
,p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
,p_time_stamp IN varchar2/* := null */
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_index number;
l_selected_dim_objets t_lock_table;
/* 0. Just passed selected Dimension Object into l_selected_dim_objets */
if p_selected_dim_objets.COUNT > 0 then
l_count := 1;
l_index := p_selected_dim_objets.FIRST;
l_dim_level_id := p_selected_dim_objets(l_index);
l_selected_dim_objets(l_dim_level_id) := l_lock_Rec;
IF l_index = p_selected_dim_objets.LAST then
exit;
l_index := p_selected_dim_objets.NEXT(l_index);
get_selected_dim_objs(
p_dimension_id => p_dimension_id
,x_selected_dim_objs => l_previous_dim_objets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/* 3.1 Find the Deleted dimension objects */
if l_previous_dim_objets.COUNT > 0 then
l_index := l_previous_dim_objets.FIRST;
if NOT l_selected_dim_objets.EXISTS(l_dim_level_id) then
/* 4.1 Lock Dimension Objects deleted from de list*/
BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
p_dim_level_id => l_dim_level_id
,p_time_stamp => null
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
if l_selected_dim_objets.COUNT > 0 then
l_index := l_selected_dim_objets.FIRST;
l_dim_level_id := l_selected_dim_objets(l_index).obj_key1;
if l_selected_dim_objets(l_index).obj_index <>
l_previous_dim_objets(l_index).obj_index then
l_change_dim_sets_flag := true;
If l_index = l_selected_dim_objets.LAST then
exit;
l_index := l_selected_dim_objets.NEXT(l_index);
p_selected_dimensions => l_impacted_dimentions
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
end LOCK_UPDATE_DIMENSION;
Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM
This procedure will make all the necessaries locks to Update a Dimension
Object propertis in a dimencion.
(Dimension level properties in a Dimension Group
-------------------------------------------------------------------------------------------*/
Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM(
p_dim_object_id IN number
,p_dimension_id IN number
,p_time_stamp IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
)is
l_impacted_dim_sets t_lock_table;
p_selected_dimensions => l_impacted_dimentions
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
End LOCK_UPDATE_DIM_OBJ_IN_DIM;
p_selected_dimensions: This Array has the Ids corresponding to the Dimensions where
the dimension object will be assigned.
-------------------------------------------------------------------------------------------*/
Procedure LOCK_CREATE_DIMENSION_OBJECT(
p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_index number;
if p_selected_dimensions.COUNT > 0 then
l_index := p_selected_dimensions.FIRST;
l_dim_group_id := p_selected_dimensions(l_index);
if l_index = p_selected_dimensions.LAST then
exit;
l_index := p_selected_dimensions.NEXT(l_index);
/* 2. Get all the KPI Dimension Sets using the selected Dimensions
(Dimension Groups) for the new Dimension object*/
get_kpi_dim_sets_by_dim(
p_selected_dimensions => l_impacted_dimentions
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Procedure LOCK_UPDATE_DIMENSION_OBJECT
This procedure will make all the necessaries locks to Update a Dimension Object (Dimension Level)
according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
Update Dimension Object'
p_dim_object_id: Dimension Object Id (Dimension Level) to update
p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
that will have the dimension.
p_time_stamp: Last update of dimension object information changed by the user.
It is mandatory in order of checking if the dimension object has been
updated by other user.
-------------------------------------------------------------------------------------------*/
Procedure LOCK_UPDATE_DIMENSION_OBJECT(
p_dim_object_id IN number
,p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
,p_time_stamp IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
l_selected_dimensions t_lock_table;
/* 0. Just passed p_selected Dimension into l_selected Dimension */
if p_selected_dimensions.COUNT > 0 then
l_count := 1;
l_index := p_selected_dimensions.FIRST;
l_dimension_id := p_selected_dimensions(l_index);
l_selected_dimensions(l_dimension_id) := l_lock_Rec;
IF l_index = p_selected_dimensions.LAST then
exit;
l_index := p_selected_dimensions.NEXT(l_index);
/* 1. Lock the Dimension object that will be updated */
BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
p_dim_level_id => p_dim_object_id
,p_time_stamp => p_time_stamp
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT INDICATOR, DIM_SET_ID
FROM BSC_sys_dim_levels_by_group DLG,
bsc_kpi_dim_groups KDG
WHERE DLG.DIM_LEVEL_ID =:1
AND KDG.DIM_GROUP_ID = DLG.DIM_GROUP_ID';
/* Followed instructions are for changes in the Selected Dimensions
for the current Dimension Object */
/* 4. Get previous Selected Dimension : */
get_selected_dimensions(
p_dim_obj_id => p_dim_object_id
,x_selected_dimensions => l_previous_dimensions
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Selected Dimension in p_Selected_Dimensions to find Dimension
that was deleted from or added to the Selected Dimension list; This are
/* 5.1 Find the deleted dimension. */
if l_previous_dimensions.COUNT > 0 then
l_dimension_id := l_previous_dimensions.FIRST;
if NOT l_selected_dimensions.EXISTS(l_dimension_id) then
l_lock_Rec := l_previous_dimensions(l_dimension_id);
if l_selected_dimensions.COUNT > 0 then
l_dimension_id := l_selected_dimensions.FIRST;
l_lock_Rec := l_selected_dimensions(l_dimension_id);
IF l_dimension_id = l_selected_dimensions.LAST then
exit;
l_dimension_id := l_selected_dimensions.NEXT(l_dimension_id);
p_selected_dimensions => l_impacted_dimentions
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
end LOCK_UPDATE_DIMENSION_OBJECT;
Procedure LOCK_UPDATE_DIM_OBJ_RELATIONSHIPS
This process Lock all affected object when the relationships for a given dimension
object are updated.
p_dim_object_id: Dimension Object Id (Dimension Level) to update
p_selected_parends: This array has the Ids corresponding to the Parent Dimension Objects
that will have the dimension object (Selected Parent Dimension Objects)
p_selected_childs: This array has the Ids corresponding to the Child Dimension Objects
that will have the dimension object (Selected Child Dimension Objects).
p_time_stamp: Last update of dimension object information changed by the user.
It is mandatory in order of checking if the dimension object has
been updated by other user.
-------------------------------------------------------------------------------------------*/
Procedure LOCK_UPDATE_RELATIONSHIPS(
p_dim_object_id IN number
,p_selected_parends IN BSC_BIS_LOCKS_PUB.t_numberTable
,p_selected_childs IN BSC_BIS_LOCKS_PUB.t_numberTable
,p_time_stamp IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
l_selected_parends t_lock_table;
l_selected_childs t_lock_table;
/*1. Lock the Dimension object that will be updated: */
BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
p_dim_level_id => p_dim_object_id
,p_time_stamp => p_time_stamp
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT PARENT_DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE DIM_LEVEL_ID =:1';
/*3. Compare Selected parent in p_Selected_Parents and previous parents
queried in Step 1 to find deleted and added parent in
l_impacted_dim_objects */
convert_table(
p_numberTable => p_selected_parends
,x_lock_table => l_selected_parends
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
p_selected_objects => l_selected_parends
,p_previous_objects => l_previous_parends
,x_impacted_objects => l_impacted_dim_objects
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*4. Lock Deleted and added Parent Dimension object */
BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
p_dim_level_id => l_parent_dim_object_id
,p_time_stamp => null
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*5. Get Dimension Sets where the deleted or added Relationship */
get_kpi_dim_sets_by_Rel(
p_child_dim_obj => p_dim_object_id
,p_parent_dim_obj => l_parent_dim_object_id
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE PARENT_DIM_LEVEL_ID =:1';
/*8. Compare Selected Child in p_Selected_Chlilds and previous
childs queried in Step 7 to find deleted and added childs. These are
l_impacted_dim_objects */
convert_table(
p_numberTable => p_selected_childs
,x_lock_table => l_selected_childs
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
p_selected_objects => l_selected_childs
,p_previous_objects => l_previous_childs
,x_impacted_objects => l_impacted_dim_objects
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*9. Lock deleted and added Child Dimension object*/
BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
p_dim_level_id => l_child_dim_object_id
,p_time_stamp => null
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,x_selected_dim_sets => l_impacted_dim_sets
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
end LOCK_UPDATE_RELATIONSHIPS;
l_last_update_date VARCHAR2(50);
l_sql := ' SELECT TAB_ID
FROM BSC_TABS_B
WHERE TAB_ID = :1
FOR UPDATE NOWAIT ';
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
l_last_update_date := get_tab_time_stamp
( p_tab_id => p_tab_id
);
IF (p_time_stamp <> l_last_update_date) THEN
FND_MSG_PUB.Initialize;
l_last_update_date VARCHAR2(50);
SELECT TAB_ID,TAB_VIEW_ID
FROM BSC_TAB_VIEWS_B
WHERE TAB_ID = :1
AND TAB_VIEW_ID = :2
FOR UPDATE NOWAIT ';
FND_MESSAGE.SET_NAME('BSC','The current tab view has been deleted by another user');
l_last_update_date := get_tabview_time_stamp(
p_tab_id => p_tab_id
,p_tab_view_id=> p_tab_view_id
);
IF p_time_stamp <> l_last_update_date THEN
--dbms_output.put_line('Kpi = ' || p_kpi_id || ' updated by other user ');
l_Last_Update_Date DATE;
l_Sql := ' SELECT CALENDAR_ID, LAST_UPDATE_DATE '
||' FROM BSC_SYS_CALENDARS_B '
||' WHERE CALENDAR_ID = :1 '
||' FOR UPDATE NOWAIT ';
FETCH l_Cursor INTO l_Object_Id, l_Last_Update_Date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
IF p_Time_Stamp <> TO_CHAR(l_Last_Update_Date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) THEN
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_CALENDAR');
l_Last_Update_Date DATE;
l_Sql := ' SELECT PERIODICITY_ID, LAST_UPDATE_DATE '
||' FROM BSC_SYS_PERIODICITIES_TL '
||' WHERE PERIODICITY_ID = :1 '
||' AND LANGUAGE = USERENV(''LANG'') '
||' FOR UPDATE NOWAIT ';
FETCH l_Cursor INTO l_Object_Id, l_Last_Update_Date;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
IF p_Time_Stamp <> TO_CHAR(l_Last_Update_Date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) THEN
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_PERIODICITY');
SELECT C.NAME INTO l_Calendar_Name
FROM BSC_SYS_CALENDARS_VL C
WHERE C.CALENDAR_ID = p_Calendar_Id;
SELECT P.NAME INTO l_Periodicity_Name
FROM BSC_SYS_PERIODICITIES_VL P
WHERE P.PERIODICITY_ID = p_Periodicity_Id;