[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure removes the invalid and deleted Objects from the locking tables
When we delete an Object or update the key value of an Object,
the entries in the lock tables remain there. After some time,
more and more invalid rows will be in the lock tables.
This SYNCHRONIZE api cleans up the lock tables and synchronizes the data
with the latest metadata. It will be called by the Generate Database
concurrent request after the generation process has completed.
p_program_id: -100 = Data Loader UI
-101 = Data Loader Backend
-200 = Generate Database
-201 = Generate Documentation
-202 = Rename Interface Table
-203 = Generate Database Configuration
-300 = Administrator
-400 = Objective Designer
-500 = Builder
-600 = Performance Scorecard
-700 = System Upgrade
-800 = System Migration
p_user_id: Application User ID
-------------------------------------------------------------------------------------------*/
PROCEDURE SYNCHRONIZE(
p_program_id IN number
,p_user_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'SYNCHRONIZE';
SELECT DISTINCT OBJECT_TYPE, OBJECT_KEY
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY <> 'ALL'
AND ((OBJECT_TYPE = 'OBJECTIVE' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(INDICATOR)
FROM BSC_KPIS_B))
OR (OBJECT_TYPE = 'SCORECARD' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(TAB_ID)
FROM BSC_TABS_B))
OR (OBJECT_TYPE = 'DIMENSION' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(DIM_GROUP_ID)
FROM BSC_SYS_DIM_GROUPS_TL))
OR (OBJECT_TYPE = 'DIMENSION_OBJECT' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(DIM_LEVEL_ID)
FROM BSC_SYS_DIM_LEVELS_B
UNION
SELECT LEVEL_TABLE_NAME
FROM BSC_SYS_DIM_LEVELS_B))
OR (OBJECT_TYPE = 'MEASURE' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(DATASET_ID)
FROM BSC_SYS_DATASETS_B))
OR (OBJECT_TYPE = 'DATA_COLUMN' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(MEASURE_ID)
FROM BSC_SYS_MEASURES))
OR (OBJECT_TYPE = 'CUSTOM_VIEW' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID)
FROM BSC_TAB_VIEWS_B))
OR (OBJECT_TYPE = 'LAUNCHPAD' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(MENU_ID)
FROM FND_MENUS))
OR (OBJECT_TYPE = 'PERIODICITY' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(PERIODICITY_ID)
FROM BSC_SYS_PERIODICITIES))
OR (OBJECT_TYPE = 'CALENDAR' AND
OBJECT_KEY NOT IN (
SELECT TO_CHAR(CALENDAR_ID)
FROM BSC_SYS_CALENDARS_B))
OR (OBJECT_TYPE = 'TABLE' AND
OBJECT_KEY NOT IN (
SELECT TABLE_NAME
FROM BSC_DB_TABLES))
OR (OBJECT_TYPE NOT IN (
'SCORECARD',
'OBJECTIVE',
'DIMENSION',
'DIMENSION_OBJECT',
'MEASURE',
'DATA_COLUMN',
'CUSTOM_VIEW',
'LAUNCHPAD',
'PERIODICITY',
'CALENDAR',
'TABLE')));
BSC_LOCKS_PVT.DELETE_LOCK_INFO
(
p_object_key => l_object_key
,p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_USER_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_user_type => 'M'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_LOCK_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => p_lock_type
,p_last_save_time => SYSDATE
,p_session_id => l_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT DISTINCT OBJECT_KEY
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_TYPE = c_object_type;
BSC_LOCKS_PVT.UPDATE_USER_INFO
(
p_object_key => cobj.OBJECT_KEY
,p_object_type => p_object_type
,p_user_type => 'M'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_LOCK_INFO
(
p_object_key => cobj.OBJECT_KEY
,p_object_type => p_object_type
,p_lock_type => p_lock_type
,p_last_save_time => SYSDATE
,p_session_id => l_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_insert_flag BOOLEAN;
SELECT LAST_SAVE_TIME
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
AND LOCK_TYPE = c_lock_type;
l_insert_flag := FALSE;
l_insert_flag := (c_locate_object%NOTFOUND);
IF (l_insert_flag) THEN
BSC_LOCKS_PVT.INSERT_LOCK_ALL_AUTONOMOUS
(
p_object_type => 'ALL'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,p_session_id => l_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_USER_INFO_AUTONOMOUS
(
p_object_key => 'ALL'
,p_object_type => 'ALL'
,p_user_type => 'L'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_USER_INFO
(
p_object_key => 'ALL'
,p_object_type => 'ALL'
,p_user_type => 'ALL'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => l_machine
,p_terminal => l_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.UPDATE_LOCK_INFO
(
p_object_key => 'ALL'
,p_object_type => 'ALL'
,p_lock_type => 'ALL'
,p_last_save_time => SYSDATE
,p_session_id => l_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
the new Object-Level locking will be proceeded. Next, a row will be inserted
into BSC_CURRENT_SESSIONS. It will be seen as a whole system exclusive lock by
modules haven't implemented the new locking scheme. For modules that have uptaken
the new locking scheme, those entries in BSC_CURRENT_SESSION will be ignored.
p_program_id: -100 = Data Loader UI
-101 = Data Loader Backend
-200 = Generate Database
-201 = Generate Documentation
-202 = Rename Interface Table
-203 = Generate Database Configuration
-300 = Administrator
-400 = Objective Designer
-500 = Builder
-600 = Performance Scorecard
-700 = System Upgrade
-800 = System Migration
p_user_id: Application User ID
p_icx_session_id: Application Session ID
-------------------------------------------------------------------------------------------*/
Procedure GET_SYSTEM_LOCK (
p_program_id IN number
,p_user_id IN number
,p_icx_session_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT c.program_id, u.user_name, s.machine, s.terminal
FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u
WHERE c.session_id = s.audsid
AND ((c_program_id = -100 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
OR (c_program_id = -101 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
OR (c_program_id = -200 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
OR (c_program_id = -201 AND c.program_id IN (-400, -500, -700, -800, -802))
OR (c_program_id = -202 AND c.program_id IN (-700, -800, -802))
OR (c_program_id = -300 AND c.program_id IN (-100, -101, -200, -700, -800, -802))
OR (c_program_id = -400 AND c.program_id IN (-100, -101, -200, -201, -700, -800, -802))
OR (c_program_id = -500 AND c.program_id IN (-100, -101, -200, -201, -700, -800, -802))
OR (c_program_id = -600 AND c.program_id IN (-700, -800))
OR (c_program_id = -801 AND c.program_id IN (-700, -800, -801, -802))
OR (c_program_id = -802 AND c.program_id IN (-100, -101, -200, -201, -202, -300, -400, -500, -700, -800, -801))
OR (c_program_id NOT IN (-100, -101, -200, -201, -202, -300, -400, -500, -600, -801, -802)
AND c.program_id IN (-100, -101, -200, -201, -202, -300, -400, -500, -600, -700, -800, -801, -802)))
AND c.session_id <> USERENV('SESSIONID')
AND c.user_id = u.user_id (+);
SELECT SESSION_ID
FROM BSC_CURRENT_SESSIONS
WHERE SESSION_ID = USERENV('SESSIONID')
AND ICX_SESSION_ID = c_icx_session_id
AND PROGRAM_ID = c_program_id;
INSERT INTO BSC_CURRENT_SESSIONS (
SESSION_ID,
PROGRAM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
USER_ID,
ICX_SESSION_ID
) VALUES (
USERENV('SESSIONID'),
p_program_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
USERENV('SESSIONID'),
p_user_id,
p_icx_session_id
);
will be deleted
none
-------------------------------------------------------------------------------------------*/
Procedure REMOVE_SYSTEM_LOCK
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE BSC_CURRENT_SESSIONS
WHERE SESSION_ID = USERENV('SESSIONID');