[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 FROM BSC_TABS_B
WHERE TAB_ID = c_tab_id;
SELECT 1 FROM BSC_KPIS_B
WHERE INDICATOR = c_indicator;
SELECT 1 FROM BSC_SYS_DIM_GROUPS_TL
WHERE DIM_GROUP_ID = c_dim_group_id;
SELECT 1 FROM BSC_SYS_DIM_LEVELS_B
WHERE DIM_LEVEL_ID = c_dim_level_id;
SELECT 1 FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID = c_dataset_id;
SELECT 1 FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = c_measure_id;
SELECT 1 FROM BSC_TAB_VIEWS_B
WHERE TAB_ID = c_tab_id
AND TAB_VIEW_ID = c_tab_view_id;
SELECT 1 FROM FND_MENUS
WHERE MENU_ID = c_menu_id;
SELECT 1 FROM BSC_SYS_PERIODICITIES
WHERE PERIODICITY_ID = c_periodicity_id;
SELECT 1 FROM BSC_SYS_CALENDARS_B
WHERE CALENDAR_ID = c_calendar_id;
SELECT 1 FROM BSC_DB_TABLES
WHERE TABLE_NAME = c_table_name;
not exist, a new row will be inserted to the lock table.
In addition to that, the user table will also be updated.
If someone else already locked the object, an exception will be raised.
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_lock_type: 'W' for write lock, 'R' for read lock
p_query_time: The query time at the start of the process flow
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
FUNCTION LOCK_OBJECT(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_lock_type IN varchar2
,p_query_time IN date
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) RETURN DATE IS
l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT';
SELECT SESSION_ID
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
AND LOCK_TYPE = 'W';
SELECT DISTINCT OBJECT_KEY
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_TYPE = c_object_type;
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
FOR UPDATE NOWAIT;
l_insert_flag := TRUE;
,x_insert_flag => l_insert_flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF (l_insert_flag) THEN
l_object_key := p_object_key;
BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => 'W'
,p_last_save_time => NULL
,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 => p_object_key
,p_object_type => p_object_type
,p_user_type => 'L'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_insert_flag: True if the lock entry is missing in the lock table
-------------------------------------------------------------------------------------------*/
Function LOCK_OBJECT_WRITE(
p_object_key IN varchar2
,p_object_type IN varchar2
,x_insert_flag OUT NOCOPY boolean
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) return DATE IS
l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT_WRITE';
SELECT LOCK_TYPE
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type;
SELECT LAST_SAVE_TIME
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
ORDER BY LOCK_TYPE
FOR UPDATE NOWAIT;
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
FOR UPDATE NOWAIT;
x_insert_flag := TRUE;
x_insert_flag := FALSE;
l_insert_flag BOOLEAN;
SELECT OBJECT_KEY, OBJECT_TYPE, LOCK_TYPE
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
AND LENGTH(LOCK_TYPE) > 1
AND SUBSTR(LOCK_TYPE,1,1) = 'R'
ORDER BY LOCK_TYPE;
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
FOR UPDATE NOWAIT;
SELECT LAST_SAVE_TIME
FROM BSC_OBJECT_LOCKS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
AND LOCK_TYPE = 'W';
l_insert_flag := TRUE;
l_insert_flag := FALSE;
IF (l_insert_flag) THEN
l_object_key := p_object_key;
l_insert_flag := (c_locate_lock%NOTFOUND);
IF (l_insert_flag) THEN
l_object_key := 'ALL';
BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => 'W'
,p_last_save_time => NULL
,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.INSERT_LOCK_INFO_AUTONOMOUS
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => 'R'||TO_CHAR(l_count)
,p_last_save_time => NULL
,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 => p_object_key
,p_object_type => p_object_type
,p_user_type => 'L'||TO_CHAR(l_count)
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT LAST_SAVE_TIME
FROM BSC_OBJECT_LOCKS
FOR UPDATE NOWAIT;
SELECT OBJECT_TYPE, OBJECT_KEY, LOCK_TYPE
FROM BSC_OBJECT_LOCKS;
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
FOR UPDATE NOWAIT;
lock user table. If the row does not exist, a new row will be inserted
to the lock user table. If someone else already locked the object,
an exception will be raised.
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_user_type: "L" = Lock, "M" = Modify
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure LOCK_USER(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_user_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'LOCK_USER';
SELECT USER_ID FROM BSC_OBJECT_LOCK_USERS
WHERE OBJECT_KEY = c_object_key
AND OBJECT_TYPE = c_object_type
AND USER_TYPE = c_user_type
FOR UPDATE NOWAIT;
SELECT TO_CHAR(INDICATOR) OBJECT_KEY,
'OBJECTIVE' OBJECT_TYPE
FROM BSC_TAB_INDICATORS
WHERE TAB_ID = c_tab_id
UNION
SELECT TO_CHAR(c_tab_id)||','||TO_CHAR(TAB_VIEW_ID) OBJECT_KEY,
'CUSTOM_VIEW' OBJECT_TYPE
FROM BSC_TAB_VIEWS_B
WHERE TAB_ID = c_tab_id;
SELECT TO_CHAR(DIM_GROUP_ID) OBJECT_KEY,
'DIMENSION' OBJECT_TYPE
FROM BSC_KPI_DIM_GROUPS
WHERE INDICATOR = c_indicator
UNION
SELECT TO_CHAR(DATASET_ID) OBJECT_KEY,
'MEASURE' OBJECT_TYPE
FROM BSC_DB_DATASET_DIM_SETS_V
WHERE INDICATOR = c_indicator;
SELECT TO_CHAR(DIM_LEVEL_ID) OBJECT_KEY,
'DIMENSION_OBJECT' OBJECT_TYPE
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE DIM_GROUP_ID = c_dim_group_id;
SELECT TO_CHAR(MEASURE_ID1) OBJECT_KEY,
'DATA_COLUMN' OBJECT_TYPE
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID = c_dataset_id
UNION
SELECT TO_CHAR(MEASURE_ID2) OBJECT_KEY,
'DATA_COLUMN' OBJECT_TYPE
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID = c_dataset_id
AND MEASURE_ID2 IS NOT NULL;
SELECT TO_CHAR(LINK_ID) OBJECT_KEY,
DECODE(LABEL_TYPE,
2, 'LAUNCHPAD',
3, 'MEASURE',
4, 'OBJECTIVE') OBJECT_TYPE
FROM BSC_TAB_VIEW_LABELS_B
WHERE TAB_ID = c_tab_id
AND TAB_VIEW_ID = c_tab_view_id
AND LABEL_TYPE IN (2,3,4)
AND LINK_ID IS NOT NULL;
SELECT MEASURE_COL, SOURCE
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = c_measure_id;
SELECT TO_CHAR(MEASURE_ID) OBJECT_KEY,
'DATA_COLUMN' OBJECT_TYPE
FROM BSC_SYS_MEASURES
WHERE MEASURE_COL = c_measure_col
AND SOURCE = c_source;
Procedure INSERT_LOCK_ALL
This procedure inserts the ALL entries into the lock table and user table
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
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_machine: The Machine
p_terminal: The Terminal
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_ALL(
p_object_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,p_session_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) := 'INSERT_LOCK_ALL';
BSC_LOCKS_PVT.INSERT_LOCK_INFO
(
p_object_key => 'ALL'
,p_object_type => p_object_type
,p_lock_type => 'W'
,p_last_save_time => SYSDATE
,p_session_id => p_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.INSERT_USER_INFO
(
p_object_key => 'ALL'
,p_object_type => p_object_type
,p_user_type => 'L'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_LOCKS_PVT.INSERT_USER_INFO
(
p_object_key => 'ALL'
,p_object_type => p_object_type
,p_user_type => 'M'
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END INSERT_LOCK_ALL;
Procedure INSERT_LOCK_ALL_AUTONOMOUS
This procedure inserts the ALL entries into the lock table and user table
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
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_machine: The Machine
p_terminal: The Terminal
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_ALL_AUTONOMOUS(
p_object_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,p_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;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_ALL_AUTONOMOUS';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => p_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,p_session_id => p_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END INSERT_LOCK_ALL_AUTONOMOUS;
Procedure INSERT_LOCK_SCORECARD
This procedure inserts scorecard entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_SCORECARD(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_SCORECARD';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(TAB_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_TABS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(TAB_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TABS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(TAB_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TABS_B;
END INSERT_LOCK_SCORECARD;
Procedure INSERT_LOCK_OBJECTIVE
This procedure inserts objective entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_OBJECTIVE(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_OBJECTIVE';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(INDICATOR),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_KPIS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(INDICATOR),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_KPIS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(INDICATOR),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_KPIS_B;
END INSERT_LOCK_OBJECTIVE;
Procedure INSERT_LOCK_DIMENSION
This procedure inserts dimension entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_DIMENSION(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_DIMENSION';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_DIM_GROUPS_TL;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DIM_GROUPS_TL;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DIM_GROUPS_TL;
END INSERT_LOCK_DIMENSION;
Procedure INSERT_LOCK_DIMENSION_OBJECT
This procedure inserts dimension object entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_DIMENSION_OBJECT(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_DIMENSION_OBJECT';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(DIM_LEVEL_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_DIM_LEVELS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(DIM_LEVEL_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DIM_LEVELS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(DIM_LEVEL_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DIM_LEVELS_B;
END INSERT_LOCK_DIMENSION_OBJECT;
Procedure INSERT_LOCK_MEASURE
This procedure inserts measure entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_MEASURE(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_MEASURE';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(DATASET_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_DATASETS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(DATASET_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DATASETS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(DATASET_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_DATASETS_B;
END INSERT_LOCK_MEASURE;
Procedure INSERT_LOCK_DATA_COLUMN
This procedure inserts data column entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_DATA_COLUMN(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_DATA_COLUMN';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(MEASURE_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_MEASURES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(MEASURE_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_MEASURES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(MEASURE_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_MEASURES;
END INSERT_LOCK_DATA_COLUMN;
Procedure INSERT_LOCK_CUSTOM_VIEW
This procedure inserts custom view entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_CUSTOM_VIEW(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_CUSTOM_VIEW';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_TAB_VIEWS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TAB_VIEWS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TAB_VIEWS_B;
END INSERT_LOCK_CUSTOM_VIEW;
Procedure INSERT_LOCK_LAUNCHPAD
This procedure inserts launchpad entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_LAUNCHPAD(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_LAUNCHPAD';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(LINK_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_TAB_VIEW_LABELS_B
WHERE LABEL_TYPE = 2;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(LINK_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TAB_VIEW_LABELS_B
WHERE LABEL_TYPE = 2;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(LINK_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_TAB_VIEW_LABELS_B
WHERE LABEL_TYPE = 2;
END INSERT_LOCK_LAUNCHPAD;
Procedure INSERT_LOCK_PERIODICITY
This procedure inserts periodicity entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_PERIODICITY(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_PERIODICITY';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(PERIODICITY_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_PERIODICITIES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(PERIODICITY_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_PERIODICITIES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(PERIODICITY_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_PERIODICITIES;
END INSERT_LOCK_PERIODICITY;
Procedure INSERT_LOCK_CALENDAR
This procedure inserts calendar entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_CALENDAR(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_CALENDAR';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TO_CHAR(CALENDAR_ID),
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_SYS_CALENDARS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(CALENDAR_ID),
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_CALENDARS_B;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TO_CHAR(CALENDAR_ID),
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_SYS_CALENDARS_B;
END INSERT_LOCK_CALENDAR;
Procedure INSERT_LOCK_TABLE
This procedure inserts table entries into the lock table and user table
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_TABLE(
p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_LOCK_TABLE';
BSC_LOCKS_PVT.INSERT_LOCK_ALL
(
p_object_type => l_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_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
);
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) SELECT TABLE_NAME,
l_object_type,
'W',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_session_id
FROM BSC_DB_TABLES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TABLE_NAME,
l_object_type,
'L',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_DB_TABLES;
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) SELECT TABLE_NAME,
l_object_type,
'M',
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
FROM BSC_DB_TABLES;
END INSERT_LOCK_TABLE;
Procedure INSERT_LOCK_INFO
This procedure inserts a row in the lock table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_lock_type: 'W' for write lock, 'R' for read lock
p_last_save_time: The last time the Object being modified
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_INFO(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_lock_type IN varchar2
,p_last_save_time IN date
,p_session_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) := 'INSERT_LOCK_INFO';
INSERT INTO BSC_OBJECT_LOCKS (
OBJECT_KEY,
OBJECT_TYPE,
LOCK_TYPE,
LAST_SAVE_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SESSION_ID
) VALUES (
p_object_key,
p_object_type,
p_lock_type,
p_last_save_time,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
p_session_id
);
END INSERT_LOCK_INFO;
Procedure INSERT_LOCK_INFO_AUTONOMOUS
This procedure inserts a row in the lock table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_lock_type: 'W' for write lock, 'R' for read lock
p_last_save_time: The last time the Object being modified
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure INSERT_LOCK_INFO_AUTONOMOUS(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_lock_type IN varchar2
,p_last_save_time IN date
,p_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;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_INFO_AUTONOMOUS';
BSC_LOCKS_PVT.INSERT_LOCK_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => p_lock_type
,p_last_save_time => p_last_save_time
,p_session_id => p_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END INSERT_LOCK_INFO_AUTONOMOUS;
Procedure UPDATE_LOCK_INFO
This procedure updates the lock table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_lock_type: 'W' for write lock, 'R' for read lock
p_last_save_time: The last time the Object being modified
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure UPDATE_LOCK_INFO(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_lock_type IN varchar2
,p_last_save_time IN date
,p_session_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) := 'UPDATE_LOCK_INFO';
UPDATE BSC_OBJECT_LOCKS
SET LAST_SAVE_TIME = p_last_save_time,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
SESSION_ID = p_session_id
WHERE p_lock_type = 'ALL'
OR LOCK_TYPE = p_lock_type;
UPDATE BSC_OBJECT_LOCKS
SET LAST_SAVE_TIME = p_last_save_time,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
SESSION_ID = p_session_id
WHERE OBJECT_TYPE = p_object_type
AND OBJECT_KEY = p_object_key
AND LOCK_TYPE = p_lock_type;
BSC_LOCKS_PVT.INSERT_LOCK_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_lock_type => p_lock_type
,p_last_save_time => p_last_save_time
,p_session_id => p_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END UPDATE_LOCK_INFO;
Procedure UPDATE_LOCK_INFO_AUTONOMOUS
This procedure updates the lock table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_lock_type: 'W' for write lock, 'R' for read lock
p_last_save_time: The last time the Object being modified
p_session_id: The Database Session ID
-------------------------------------------------------------------------------------------*/
Procedure UPDATE_LOCK_INFO_AUTONOMOUS(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_lock_type IN varchar2
,p_last_save_time IN date
,p_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;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOCK_INFO_AUTONOMOUS';
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 => p_last_save_time
,p_session_id => p_session_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END UPDATE_LOCK_INFO_AUTONOMOUS;
Procedure INSERT_USER_INFO
This procedure inserts a row into the lock user table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_user_type: "L" = Lock, "M" = Modify
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_USER_INFO(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_user_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'INSERT_USER_INFO';
INSERT INTO BSC_OBJECT_LOCK_USERS (
OBJECT_KEY,
OBJECT_TYPE,
USER_TYPE,
PROGRAM_ID,
USER_ID,
MACHINE,
TERMINAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
p_object_key,
p_object_type,
p_user_type,
p_program_id,
p_user_id,
p_machine,
p_terminal,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
);
END INSERT_USER_INFO;
Procedure INSERT_USER_INFO_AUTONOMOUS
This procedure inserts a row into the lock user table
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_user_type: "L" = Lock, "M" = Modify
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure INSERT_USER_INFO_AUTONOMOUS(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_user_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_USER_INFO_AUTONOMOUS';
BSC_LOCKS_PVT.INSERT_USER_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_user_type => p_user_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END INSERT_USER_INFO_AUTONOMOUS;
Procedure UPDATE_USER_INFO
This procedure updates the current user info
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_user_type: "L" = Lock, "M" = Modify
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure UPDATE_USER_INFO(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_user_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'UPDATE_USER_INFO';
UPDATE BSC_OBJECT_LOCK_USERS
SET PROGRAM_ID = p_program_id,
USER_ID = p_user_id,
MACHINE = p_machine,
TERMINAL = p_terminal,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE p_user_type = 'ALL'
OR USER_TYPE = p_user_type;
UPDATE BSC_OBJECT_LOCK_USERS
SET PROGRAM_ID = p_program_id,
USER_ID = p_user_id,
MACHINE = p_machine,
TERMINAL = p_terminal,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE OBJECT_TYPE = p_object_type
AND OBJECT_KEY = p_object_key
AND USER_TYPE = p_user_type;
BSC_LOCKS_PVT.INSERT_USER_INFO_AUTONOMOUS
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_user_type => p_user_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE BSC_OBJECT_LOCK_USERS
SET PROGRAM_ID = p_program_id,
USER_ID = p_user_id,
MACHINE = p_machine,
TERMINAL = p_terminal,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE OBJECT_TYPE = p_object_type
AND OBJECT_KEY = p_object_key
AND SUBSTR(USER_TYPE,1,1) = 'L';
END UPDATE_USER_INFO;
Procedure UPDATE_USER_INFO_AUTONOMOUS
This procedure updates the current user info
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
p_user_type: "L" = Lock, "M" = Modify
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure UPDATE_USER_INFO_AUTONOMOUS(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_user_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_USER_INFO_AUTONOMOUS';
BSC_LOCKS_PVT.UPDATE_USER_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_user_type => p_user_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END UPDATE_USER_INFO_AUTONOMOUS;
Procedure DELETE_LOCK_INFO
This procedure delete the object from the lock tables
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure DELETE_LOCK_INFO(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,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) := 'DELETE_LOCK_INFO';
DELETE FROM BSC_OBJECT_LOCKS
WHERE OBJECT_TYPE = p_object_type
AND OBJECT_KEY = p_object_key;
DELETE FROM BSC_OBJECT_LOCK_USERS
WHERE OBJECT_TYPE = p_object_type
AND OBJECT_KEY = p_object_key;
END DELETE_LOCK_INFO;
Procedure DELETE_LOCK_INFO_AUTONOMOUS
This procedure delete the object from the lock tables
p_object_key: The primary key of the Object, usually the TO_CHAR value
of the Object ID. If the Object has composite keys,
the value to pass in will be a concatenation of
all the keys, separated by commas
p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
"LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
"DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
"PERIODICITY", and "TABLE"
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_machine: The Machine
p_terminal: The Terminal
-------------------------------------------------------------------------------------------*/
Procedure DELETE_LOCK_INFO_AUTONOMOUS(
p_object_key IN varchar2
,p_object_type IN varchar2
,p_program_id IN number
,p_user_id IN number
,p_machine IN varchar2
,p_terminal IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LOCK_INFO_AUTONOMOUS';
BSC_LOCKS_PVT.DELETE_LOCK_INFO
(
p_object_key => p_object_key
,p_object_type => p_object_type
,p_program_id => p_program_id
,p_user_id => p_user_id
,p_machine => p_machine
,p_terminal => p_terminal
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END DELETE_LOCK_INFO_AUTONOMOUS;
SELECT L.PROGRAM_ID, U.USER_NAME, L.MACHINE, L.TERMINAL
FROM BSC_OBJECT_LOCK_USERS L, BSC_APPS_USERS_V U
WHERE L.OBJECT_KEY = c_object_key
AND L.OBJECT_TYPE = c_object_type
AND L.USER_TYPE = c_user_type
AND L.USER_ID = U.USER_ID (+);
SELECT MACHINE, TERMINAL
FROM v$session S
WHERE S.AUDSID = c_session_id;