[Home] [Help]
PACKAGE BODY: APPS.BSC_LOCKS_PUB
Source
1 PACKAGE BODY BSC_LOCKS_PUB AS
2 /* $Header: BSCPLOKB.pls 120.10 2006/01/24 16:21:49 calaw noship $ */
3
4 /*------------------------------------------------------------------------------------------
5 Procedure CHECK_SYSTEM_LOCK
6 This procedure is called when users enter a UI flow. It verifies
7 that the Object being modified is not locked as a top-level Object.
8 If the Object is locked as a top-level Object, some other user
9 is currently in the process of saving his changes to the database.
10 As a result, the value retrieved from the database is still the old value.
11 An error will be raised to indicate that the user has to wait until
12 the save is completed.
13 <parameters>
14 p_object_key: The primary key of the Object, usually the TO_CHAR value
15 of the Object ID. If the Object has composite keys,
16 the value to pass in will be a concatenation of
17 all the keys, separated by commas
18 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
19 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
20 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
21 "PERIODICITY", and "TABLE"
22 p_program_id: -100 = Data Loader UI
23 -101 = Data Loader Backend
24 -200 = Generate Database
25 -201 = Generate Documentation
26 -202 = Rename Interface Table
27 -203 = Generate Database Configuration
28 -300 = Administrator
29 -400 = Objective Designer
30 -500 = Builder
31 -600 = Performance Scorecard
32 -700 = System Upgrade
33 -800 = System Migration
34 p_user_id: Application User ID
35 p_cascade_lock_level: Number of level for cascade locks
36 Default is -1 which means enable cascade locking
37 all the way to the lowest level
38 -------------------------------------------------------------------------------------------*/
39 Procedure CHECK_SYSTEM_LOCK (
40 p_object_key IN varchar2
41 ,p_object_type IN varchar2
42 ,p_program_id IN number
43 ,p_user_id IN number := NULL
44 ,p_cascade_lock_level IN number := -1
45 ,x_return_status OUT NOCOPY varchar2
46 ,x_msg_count OUT NOCOPY number
47 ,x_msg_data OUT NOCOPY varchar2
48 ) IS
49
50 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_SYSTEM_LOCK';
51 l_child_object_keys BSC_LOCKS_PVT.t_array_object_key;
52 l_child_object_types BSC_LOCKS_PVT.t_array_object_type;
53 l_child_object_count NUMBER := 0;
54 l_last_save_time DATE;
55 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
56 l_lowest_level_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
57 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
58 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
59
60 BEGIN
61 --DBMS_OUTPUT.PUT_LINE('CHECK_SYSTEM_LOCK: '||p_object_key||' '||p_object_type);
62 SAVEPOINT BSCLocksPubCheckSystemLock;
63 FND_MSG_PUB.Initialize;
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 BSC_LOCKS_PVT.Initialize;
66
67 -- Get the machine and terminal information
68 BSC_LOCKS_PVT.GET_SESSION
69 (
70 x_machine => l_machine
71 ,x_terminal => l_terminal
72 ,x_return_status => x_return_status
73 ,x_msg_count => x_msg_count
74 ,x_msg_data => x_msg_data
75 );
76
77 -- Check the top level object's lock
78 BSC_LOCKS_PVT.LOCK_USER
79 (
80 p_object_key => p_object_key
81 ,p_object_type => p_object_type
82 ,p_user_type => 'M'
83 ,p_program_id => p_program_id
84 ,p_user_id => p_user_id
85 ,p_machine => l_machine
86 ,p_terminal => l_terminal
87 ,x_return_status => x_return_status
88 ,x_msg_count => x_msg_count
89 ,x_msg_data => x_msg_data
90 );
91
92 SAVEPOINT BSCLocksPubCheckAll1;
93 BSC_LOCKS_PVT.LOCK_USER
94 (
95 p_object_key => 'ALL'
96 ,p_object_type => 'ALL'
97 ,p_user_type => 'M'
98 ,p_program_id => p_program_id
99 ,p_user_id => p_user_id
100 ,p_machine => l_machine
101 ,p_terminal => l_terminal
102 ,x_return_status => x_return_status
103 ,x_msg_count => x_msg_count
104 ,x_msg_data => x_msg_data
105 );
106 BSC_LOCKS_PVT.LOCK_USER
107 (
108 p_object_key => 'ALL'
109 ,p_object_type => p_object_type
110 ,p_user_type => 'M'
111 ,p_program_id => p_program_id
112 ,p_user_id => p_user_id
113 ,p_machine => l_machine
114 ,p_terminal => l_terminal
115 ,x_return_status => x_return_status
116 ,x_msg_count => x_msg_count
117 ,x_msg_data => x_msg_data
118 );
119 ROLLBACK TO BSCLocksPubCheckAll1;
120
121 -- Find out the child objects
122 l_lowest_level_type := NULL;
123 IF (p_program_id = -203 OR p_object_type = 'CUSTOM_VIEW') THEN
124 l_lowest_level_type := 'OBJECTIVE';
125 END IF;
126 BSC_LOCKS_PVT.GET_CHILD_OBJECTS
127 (
128 p_object_key => p_object_key
129 ,p_object_type => p_object_type
130 ,p_cascade_lock_level => p_cascade_lock_level
131 ,p_lowest_level_type => l_lowest_level_type
132 ,x_child_object_keys => l_child_object_keys
133 ,x_child_object_types => l_child_object_types
134 ,x_child_object_count => l_child_object_count
135 ,x_return_status => x_return_status
136 ,x_msg_count => x_msg_count
137 ,x_msg_data => x_msg_data
138 );
139
140 -- Check the child objects' locks
141 l_object_type := p_object_type;
142 FOR i IN 1..l_child_object_count LOOP
143 BSC_LOCKS_PVT.LOCK_USER
144 (
145 p_object_key => l_child_object_keys(i)
146 ,p_object_type => l_child_object_types(i)
147 ,p_user_type => 'M'
148 ,p_program_id => p_program_id
149 ,p_user_id => p_user_id
150 ,p_machine => l_machine
151 ,p_terminal => l_terminal
152 ,x_return_status => x_return_status
153 ,x_msg_count => x_msg_count
154 ,x_msg_data => x_msg_data
155 );
156 IF (l_object_type <> l_child_object_types(i)) THEN
157 l_object_type := l_child_object_types(i);
158 SAVEPOINT BSCLocksPubCheckAll2;
159 BSC_LOCKS_PVT.LOCK_USER
160 (
161 p_object_key => 'ALL'
162 ,p_object_type => l_object_type
163 ,p_user_type => 'M'
164 ,p_program_id => p_program_id
165 ,p_user_id => p_user_id
166 ,p_machine => l_machine
167 ,p_terminal => l_terminal
168 ,x_return_status => x_return_status
169 ,x_msg_count => x_msg_count
170 ,x_msg_data => x_msg_data
171 );
172 ROLLBACK TO BSCLocksPubCheckAll2;
173 END IF;
174 END LOOP;
175 ROLLBACK TO BSCLocksPubCheckSystemLock;
176
177 EXCEPTION
178 WHEN FND_API.G_EXC_ERROR THEN
179 ROLLBACK TO BSCLocksPubCheckSystemLock;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 FND_MSG_PUB.Count_And_Get(
182 p_encoded => 'F'
183 ,p_count => x_msg_count
184 ,p_data => x_msg_data
185 );
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 ROLLBACK TO BSCLocksPubCheckSystemLock;
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 FND_MSG_PUB.Count_And_Get(
190 p_encoded => 'F'
191 ,p_count => x_msg_count
192 ,p_data => x_msg_data
193 );
194 WHEN OTHERS THEN
195 ROLLBACK TO BSCLocksPubCheckSystemLock;
196 FND_MSG_PUB.Add_Exc_Msg(
197 G_PKG_NAME,
198 l_api_name,
199 SQLERRM
200 );
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get(
203 p_encoded => 'F'
204 ,p_count => x_msg_count
205 ,p_data => x_msg_data
206 );
207 END CHECK_SYSTEM_LOCK;
208
209
210 /*------------------------------------------------------------------------------------------
211 Procedure CHECK_SYSTEM_LOCKS
212 This procedure is called when users enter a UI flow. It verifies that the
213 Objects being modified are not locked. If any of the Objects is locked,
214 some other user is currently in the process of saving his changes to the database.
215 As a result, the value retrieved from the database is still the old value.
216 An error will be raised to indicate that the user has to wait until
217 the save is completed.
218 <parameters>
219 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
220 of the Object ID. If the Object has composite keys,
221 the value to pass in will be a concatenation of
222 all the keys, separated by commas
223 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
224 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
225 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
226 "PERIODICITY", and "TABLE"
227 p_program_id: -100 = Data Loader UI
228 -101 = Data Loader Backend
229 -200 = Generate Database
230 -201 = Generate Documentation
231 -202 = Rename Interface Table
232 -203 = Generate Database Configuration
233 -300 = Administrator
234 -400 = Objective Designer
235 -500 = Builder
236 -600 = Performance Scorecard
237 -700 = System Upgrade
238 -800 = System Migration
239 p_user_id: Application User ID
240 p_cascade_lock_level: Number of level for cascade locks
241 Default is -1 which means enable cascade locking
242 all the way to the lowest level
243 -------------------------------------------------------------------------------------------*/
244 Procedure CHECK_SYSTEM_LOCKS (
245 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
246 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
247 ,p_program_id IN number
248 ,p_user_id IN number := NULL
249 ,p_cascade_lock_level IN number := -1
250 ,x_return_status OUT NOCOPY varchar2
251 ,x_msg_count OUT NOCOPY number
252 ,x_msg_data OUT NOCOPY varchar2
253 ) IS
254
255 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_SYSTEM_LOCKS';
256
257 BEGIN
258 --DBMS_OUTPUT.PUT_LINE('CHECK_SYSTEM_LOCKS');
259 SAVEPOINT BSCLocksPubCheckSystemLocks;
260 FND_MSG_PUB.Initialize;
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262 BSC_LOCKS_PVT.Initialize;
263
264 IF (p_object_keys.COUNT <> 0 AND p_object_keys.COUNT = p_object_types.COUNT) THEN
265 FOR i IN p_object_keys.FIRST..p_object_keys.LAST LOOP
266 BSC_LOCKS_PUB.CHECK_SYSTEM_LOCK
267 (
268 p_object_key => p_object_keys(i)
269 ,p_object_type => p_object_types(i)
270 ,p_program_id => p_program_id
271 ,p_user_id => p_user_id
272 ,p_cascade_lock_level => p_cascade_lock_level
273 ,x_return_status => x_return_status
274 ,x_msg_count => x_msg_count
275 ,x_msg_data => x_msg_data
276 );
277 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278 EXIT;
279 END IF;
280 END LOOP;
281 END IF;
282 ROLLBACK TO BSCLocksPubCheckSystemLocks;
283
284 EXCEPTION
285 WHEN FND_API.G_EXC_ERROR THEN
286 ROLLBACK TO BSCLocksPubCheckSystemLocks;
287 x_return_status := FND_API.G_RET_STS_ERROR;
288 FND_MSG_PUB.Count_And_Get(
289 p_encoded => 'F'
290 ,p_count => x_msg_count
291 ,p_data => x_msg_data
292 );
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 ROLLBACK TO BSCLocksPubCheckSystemLocks;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 FND_MSG_PUB.Count_And_Get(
297 p_encoded => 'F'
298 ,p_count => x_msg_count
299 ,p_data => x_msg_data
300 );
301 WHEN OTHERS THEN
302 ROLLBACK TO BSCLocksPubCheckSystemLocks;
303 FND_MSG_PUB.Add_Exc_Msg(
304 G_PKG_NAME,
305 l_api_name,
306 SQLERRM
307 );
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 FND_MSG_PUB.Count_And_Get(
310 p_encoded => 'F'
311 ,p_count => x_msg_count
312 ,p_data => x_msg_data
313 );
314 END CHECK_SYSTEM_LOCKS;
315
316
317 /*------------------------------------------------------------------------------------------
318 Function GET_SYSTEM_TIME
319 This function returns the current database system date.
320 The system date will be cached by the calling module as the "Query Time",
321 which will be used by BSC_LOCKS_PUB.Get_System_Lock to determine
322 if a lock can be acquired on an Object.
323 <parameters>
324 none
325 -------------------------------------------------------------------------------------------*/
326 FUNCTION GET_SYSTEM_TIME
327 RETURN DATE IS
328 BEGIN
329 RETURN SYSDATE;
330 EXCEPTION
331 WHEN OTHERS THEN
332 RETURN NULL;
333 END GET_SYSTEM_TIME;
334
335
336 /*------------------------------------------------------------------------------------------
337 Procedure SYNCHRONIZE
338 This procedure removes the invalid and deleted Objects from the locking tables
339 When we delete an Object or update the key value of an Object,
340 the entries in the lock tables remain there. After some time,
341 more and more invalid rows will be in the lock tables.
342 This SYNCHRONIZE api cleans up the lock tables and synchronizes the data
343 with the latest metadata. It will be called by the Generate Database
344 concurrent request after the generation process has completed.
345 <parameters>
346 p_program_id: -100 = Data Loader UI
347 -101 = Data Loader Backend
348 -200 = Generate Database
349 -201 = Generate Documentation
350 -202 = Rename Interface Table
351 -203 = Generate Database Configuration
352 -300 = Administrator
353 -400 = Objective Designer
354 -500 = Builder
355 -600 = Performance Scorecard
356 -700 = System Upgrade
357 -800 = System Migration
358 p_user_id: Application User ID
359 -------------------------------------------------------------------------------------------*/
360 PROCEDURE SYNCHRONIZE(
361 p_program_id IN number
362 ,p_user_id IN number
363 ,x_return_status OUT NOCOPY varchar2
364 ,x_msg_count OUT NOCOPY number
365 ,x_msg_data OUT NOCOPY varchar2
366 ) IS
367
368 l_api_name CONSTANT VARCHAR2(30) := 'SYNCHRONIZE';
369 l_bsc_schema VARCHAR2(32);
370 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
374
371 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
372 l_object_key BSC_OBJECT_LOCKS.OBJECT_KEY%TYPE;
373 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
375 CURSOR c_get_invalid_objects
376 IS
377 SELECT DISTINCT OBJECT_TYPE, OBJECT_KEY
378 FROM BSC_OBJECT_LOCKS
379 WHERE OBJECT_KEY <> 'ALL'
380 AND ((OBJECT_TYPE = 'OBJECTIVE' AND
381 OBJECT_KEY NOT IN (
382 SELECT TO_CHAR(INDICATOR)
383 FROM BSC_KPIS_B))
384 OR (OBJECT_TYPE = 'SCORECARD' AND
385 OBJECT_KEY NOT IN (
386 SELECT TO_CHAR(TAB_ID)
387 FROM BSC_TABS_B))
388 OR (OBJECT_TYPE = 'DIMENSION' AND
389 OBJECT_KEY NOT IN (
390 SELECT TO_CHAR(DIM_GROUP_ID)
391 FROM BSC_SYS_DIM_GROUPS_TL))
392 OR (OBJECT_TYPE = 'DIMENSION_OBJECT' AND
393 OBJECT_KEY NOT IN (
394 SELECT TO_CHAR(DIM_LEVEL_ID)
395 FROM BSC_SYS_DIM_LEVELS_B
396 UNION
397 SELECT LEVEL_TABLE_NAME
398 FROM BSC_SYS_DIM_LEVELS_B))
399 OR (OBJECT_TYPE = 'MEASURE' AND
400 OBJECT_KEY NOT IN (
401 SELECT TO_CHAR(DATASET_ID)
402 FROM BSC_SYS_DATASETS_B))
403 OR (OBJECT_TYPE = 'DATA_COLUMN' AND
404 OBJECT_KEY NOT IN (
405 SELECT TO_CHAR(MEASURE_ID)
406 FROM BSC_SYS_MEASURES))
407 OR (OBJECT_TYPE = 'CUSTOM_VIEW' AND
408 OBJECT_KEY NOT IN (
409 SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID)
410 FROM BSC_TAB_VIEWS_B))
411 OR (OBJECT_TYPE = 'LAUNCHPAD' AND
412 OBJECT_KEY NOT IN (
413 SELECT TO_CHAR(MENU_ID)
414 FROM FND_MENUS))
415 OR (OBJECT_TYPE = 'PERIODICITY' AND
416 OBJECT_KEY NOT IN (
417 SELECT TO_CHAR(PERIODICITY_ID)
418 FROM BSC_SYS_PERIODICITIES))
419 OR (OBJECT_TYPE = 'CALENDAR' AND
420 OBJECT_KEY NOT IN (
421 SELECT TO_CHAR(CALENDAR_ID)
422 FROM BSC_SYS_CALENDARS_B))
423 OR (OBJECT_TYPE = 'TABLE' AND
424 OBJECT_KEY NOT IN (
425 SELECT TABLE_NAME
426 FROM BSC_DB_TABLES))
427 OR (OBJECT_TYPE NOT IN (
428 'SCORECARD',
429 'OBJECTIVE',
430 'DIMENSION',
431 'DIMENSION_OBJECT',
432 'MEASURE',
433 'DATA_COLUMN',
434 'CUSTOM_VIEW',
435 'LAUNCHPAD',
436 'PERIODICITY',
437 'CALENDAR',
438 'TABLE')));
439
440 BEGIN
441 --DBMS_OUTPUT.PUT_LINE('SYNCHRONIZE');
442 SAVEPOINT BSCLocksPubSynchronize;
443 FND_MSG_PUB.Initialize;
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445 BSC_LOCKS_PVT.Initialize;
446 l_bsc_schema := BSC_LOCKS_PVT.GET_BSC_SCHEMA;
447
448 -- Get the machine and terminal information
449 BSC_LOCKS_PVT.GET_SESSION
450 (
451 x_machine => l_machine
452 ,x_terminal => l_terminal
453 ,x_return_status => x_return_status
454 ,x_msg_count => x_msg_count
455 ,x_msg_data => x_msg_data
456 );
457
458 -- Find out the invalid objects
459 FOR cobj IN c_get_invalid_objects LOOP
460 l_object_type := cobj.OBJECT_TYPE;
461 l_object_key := cobj.OBJECT_KEY;
462 --DBMS_OUTPUT.PUT_LINE('Invalid Object: '||l_object_type||' '||l_object_key);
463 BEGIN
464 -- Lock the invalid object
465 BSC_LOCKS_PVT.DELETE_LOCK_INFO
466 (
467 p_object_key => l_object_key
468 ,p_object_type => l_object_type
469 ,p_program_id => p_program_id
470 ,p_user_id => p_user_id
471 ,p_machine => l_machine
472 ,p_terminal => l_terminal
473 ,x_return_status => x_return_status
474 ,x_msg_count => x_msg_count
475 ,x_msg_data => x_msg_data
476 );
477 EXCEPTION
478 WHEN OTHERS THEN
479 NULL;
480 END;
481 END LOOP;
482
483 ---- Check the Lock in the lock table
484 --SAVEPOINT BSCLocksPubLockObjectAll;
485 --BSC_LOCKS_PVT.LOCK_OBJECT_ALL
486 --(
487 -- x_return_status => x_return_status
488 -- ,x_msg_count => x_msg_count
489 -- ,x_msg_data => x_msg_data
490 --);
491 --ROLLBACK TO BSCLocksPubLockObjectAll;
492 --
493 ---- Truncate the lock table
494 --IF (l_bsc_schema IS NULL) THEN
495 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE BSC_OBJECT_LOCKS');
496 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE BSC_OBJECT_LOCK_USERS');
497 --ELSE
498 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE '||l_bsc_schema||'.BSC_OBJECT_LOCKS');
499 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE '||l_bsc_schema||'.BSC_OBJECT_LOCK_USERS');
500 --END IF;
501 --
502 ---- Create the entries for "ALL"
503 --BSC_LOCKS_PVT.INSERT_LOCK_ALL_AUTONOMOUS
504 --(
505 -- p_object_type => 'ALL'
506 -- ,p_program_id => p_program_id
507 -- ,p_user_id => p_user_id
508 -- ,p_machine => l_machine
509 -- ,p_terminal => l_terminal
513 -- ,x_msg_data => x_msg_data
510 -- ,p_session_id => USERENV('SESSIONID')
511 -- ,x_return_status => x_return_status
512 -- ,x_msg_count => x_msg_count
514 --);
515 --
516 ---- Lock the lock table
517 --BSC_LOCKS_PVT.LOCK_OBJECT_ALL
518 --(
519 -- x_return_status => x_return_status
520 -- ,x_msg_count => x_msg_count
521 -- ,x_msg_data => x_msg_data
522 --);
523 --
524 ---- Insert Scorecards
525 --BSC_LOCKS_PVT.INSERT_LOCK_SCORECARD
526 --(
527 -- p_program_id => p_program_id
528 -- ,p_user_id => p_user_id
529 -- ,p_machine => l_machine
530 -- ,p_terminal => l_terminal
531 -- ,x_return_status => x_return_status
532 -- ,x_msg_count => x_msg_count
533 -- ,x_msg_data => x_msg_data
534 --);
535 --
536 ---- Insert Objectives
537 --BSC_LOCKS_PVT.INSERT_LOCK_OBJECTIVE
538 --(
539 -- p_program_id => p_program_id
540 -- ,p_user_id => p_user_id
541 -- ,p_machine => l_machine
542 -- ,p_terminal => l_terminal
543 -- ,x_return_status => x_return_status
544 -- ,x_msg_count => x_msg_count
545 -- ,x_msg_data => x_msg_data
546 --);
547 --
548 ---- Insert Dimensions
549 --BSC_LOCKS_PVT.INSERT_LOCK_DIMENSION
550 --(
551 -- p_program_id => p_program_id
552 -- ,p_user_id => p_user_id
553 -- ,p_machine => l_machine
554 -- ,p_terminal => l_terminal
555 -- ,x_return_status => x_return_status
556 -- ,x_msg_count => x_msg_count
557 -- ,x_msg_data => x_msg_data
558 --);
559 --
560 ---- Insert Dimension Objects
561 --BSC_LOCKS_PVT.INSERT_LOCK_DIMENSION_OBJECT
562 --(
563 -- p_program_id => p_program_id
564 -- ,p_user_id => p_user_id
565 -- ,p_machine => l_machine
566 -- ,p_terminal => l_terminal
567 -- ,x_return_status => x_return_status
568 -- ,x_msg_count => x_msg_count
569 -- ,x_msg_data => x_msg_data
570 --);
571 --
572 ---- Insert Measures
573 --BSC_LOCKS_PVT.INSERT_LOCK_MEASURE
574 --(
575 -- p_program_id => p_program_id
576 -- ,p_user_id => p_user_id
577 -- ,p_machine => l_machine
578 -- ,p_terminal => l_terminal
579 -- ,x_return_status => x_return_status
580 -- ,x_msg_count => x_msg_count
581 -- ,x_msg_data => x_msg_data
582 --);
583 --
584 ---- Insert Data Columns
585 --BSC_LOCKS_PVT.INSERT_LOCK_DATA_COLUMN
586 --(
587 -- p_program_id => p_program_id
588 -- ,p_user_id => p_user_id
589 -- ,p_machine => l_machine
590 -- ,p_terminal => l_terminal
591 -- ,x_return_status => x_return_status
592 -- ,x_msg_count => x_msg_count
593 -- ,x_msg_data => x_msg_data
594 --);
595 --
596 ---- Insert Custom Views
597 --BSC_LOCKS_PVT.INSERT_LOCK_CUSTOM_VIEW
598 --(
599 -- p_program_id => p_program_id
600 -- ,p_user_id => p_user_id
601 -- ,p_machine => l_machine
602 -- ,p_terminal => l_terminal
603 -- ,x_return_status => x_return_status
604 -- ,x_msg_count => x_msg_count
605 -- ,x_msg_data => x_msg_data
606 --);
607 --
608 ---- Insert Launchpads
609 --BSC_LOCKS_PVT.INSERT_LOCK_LAUNCHPAD
610 --(
611 -- p_program_id => p_program_id
612 -- ,p_user_id => p_user_id
613 -- ,p_machine => l_machine
614 -- ,p_terminal => l_terminal
615 -- ,x_return_status => x_return_status
616 -- ,x_msg_count => x_msg_count
617 -- ,x_msg_data => x_msg_data
618 --);
619 --
620 ---- Insert Periodicities
621 --BSC_LOCKS_PVT.INSERT_LOCK_PERIODICITY
622 --(
623 -- p_program_id => p_program_id
624 -- ,p_user_id => p_user_id
625 -- ,p_machine => l_machine
626 -- ,p_terminal => l_terminal
627 -- ,x_return_status => x_return_status
628 -- ,x_msg_count => x_msg_count
629 -- ,x_msg_data => x_msg_data
630 --);
631 --
632 ---- Insert Calendars
633 --BSC_LOCKS_PVT.INSERT_LOCK_CALENDAR
634 --(
635 -- p_program_id => p_program_id
636 -- ,p_user_id => p_user_id
637 -- ,p_machine => l_machine
638 -- ,p_terminal => l_terminal
639 -- ,x_return_status => x_return_status
640 -- ,x_msg_count => x_msg_count
641 -- ,x_msg_data => x_msg_data
642 --);
643 --
644 ---- Insert Tables
645 --BSC_LOCKS_PVT.INSERT_LOCK_TABLE
646 --(
647 -- p_program_id => p_program_id
648 -- ,p_user_id => p_user_id
649 -- ,p_machine => l_machine
650 -- ,p_terminal => l_terminal
651 -- ,x_return_status => x_return_status
652 -- ,x_msg_count => x_msg_count
653 -- ,x_msg_data => x_msg_data
654 --);
655
656 COMMIT;
657 -- Gather Table Stats
658 DBMS_STATS.GATHER_TABLE_STATS(NVL(l_bsc_schema, 'BSC'), 'BSC_OBJECT_LOCKS');
659 DBMS_STATS.GATHER_TABLE_STATS(NVL(l_bsc_schema, 'BSC'), 'BSC_OBJECT_LOCK_USERS');
660
661 EXCEPTION
662 WHEN FND_API.G_EXC_ERROR THEN
663 ROLLBACK TO BSCLocksPubSynchronize;
667 ,p_count => x_msg_count
664 x_return_status := FND_API.G_RET_STS_ERROR;
665 FND_MSG_PUB.Count_And_Get(
666 p_encoded => 'F'
668 ,p_data => x_msg_data
669 );
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 ROLLBACK TO BSCLocksPubSynchronize;
672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 FND_MSG_PUB.Count_And_Get(
674 p_encoded => 'F'
675 ,p_count => x_msg_count
676 ,p_data => x_msg_data
677 );
678 WHEN OTHERS THEN
679 ROLLBACK TO BSCLocksPubSynchronize;
680 FND_MSG_PUB.Add_Exc_Msg(
681 G_PKG_NAME,
682 l_api_name,
683 SQLERRM
684 );
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686 FND_MSG_PUB.Count_And_Get(
687 p_encoded => 'F'
688 ,p_count => x_msg_count
689 ,p_data => x_msg_data
690 );
691 END SYNCHRONIZE;
692
693
694 /*------------------------------------------------------------------------------------------
695 Procedure GET_SYSTEN_LOCK
696 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
697 to be the locking procedure for BSC modules. Instead of calling the API
698 at the start of the process flow, this new API will be called right
699 before the changes are committed to the database.
700
701 Passing in the key and type of the top-level object, this API will
702 figure out all the related objects that needed to be locked.
703 If the locks cannot be acquired, either because some other users
704 are possessing one of more of the required locks or the data
705 has been modified since the last query time, an exception will be raised
706 indicating that the user has to requery and re-do the changes.
707 <parameters>
708 p_object_key: The primary key of the Object, usually the TO_CHAR value
709 of the Object ID. If the Object has composite keys,
710 the value to pass in will be a concatenation of
711 all the keys, separated by commas
712 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
713 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
714 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
715 "PERIODICITY", and "TABLE"
716 p_lock_type: 'W' for write lock, 'R' for read lock
717 p_query_time: The query time at the start of the process flow
718 p_program_id: -100 = Data Loader UI
719 -101 = Data Loader Backend
720 -200 = Generate Database
721 -201 = Generate Documentation
722 -202 = Rename Interface Table
723 -203 = Generate Database Configuration
724 -300 = Administrator
725 -400 = Objective Designer
726 -500 = Builder
727 -600 = Performance Scorecard
728 -700 = System Upgrade
729 -800 = System Migration
730 p_user_id: Application User ID
731 p_cascade_lock_level: Number of level for cascade locks
732 Default is -1 which means enable cascade locking
733 all the way to the lowest level
734 -------------------------------------------------------------------------------------------*/
735 PROCEDURE GET_SYSTEM_LOCK(
736 p_object_key IN varchar2
737 ,p_object_type IN varchar2
738 ,p_lock_type IN varchar2 := 'W'
739 ,p_query_time IN date
740 ,p_program_id IN number
741 ,p_user_id IN number := NULL
742 ,p_cascade_lock_level IN number := -1
743 ,x_return_status OUT NOCOPY varchar2
744 ,x_msg_count OUT NOCOPY number
745 ,x_msg_data OUT NOCOPY varchar2
746 ) IS
747
748 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCK';
749 l_child_object_keys BSC_LOCKS_PVT.t_array_object_key;
750 l_child_object_types BSC_LOCKS_PVT.t_array_object_type;
751 l_child_object_count NUMBER := 0;
752 l_last_save_time DATE;
753 l_lowest_level_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
754 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
755 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
756 l_session_id NUMBER := USERENV('SESSIONID');
757
758 BEGIN
759 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCK: '||p_object_key||' '||p_object_type||' '||p_lock_type);
760 SAVEPOINT BSCLocksPubGetSystemLock;
761 FND_MSG_PUB.Initialize;
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763 BSC_LOCKS_PVT.Initialize;
764
765 -- Validate the top level object
766 --BSC_LOCKS_PVT.VALIDATE_OBJECT
767 --(
768 -- p_object_key => p_object_key
769 -- ,p_object_type => p_object_type
770 -- ,x_return_status => x_return_status
771 -- ,x_msg_count => x_msg_count
772 -- ,x_msg_data => x_msg_data
773 --);
774
775 -- Get the machine and terminal information
776 BSC_LOCKS_PVT.GET_SESSION
777 (
778 x_machine => l_machine
779 ,x_terminal => l_terminal
780 ,x_return_status => x_return_status
781 ,x_msg_count => x_msg_count
782 ,x_msg_data => x_msg_data
783 );
784
785 -- Lock the user entry for the top level object
786 IF (p_lock_type <> 'R') THEN
787 BSC_LOCKS_PVT.LOCK_USER
788 (
789 p_object_key => p_object_key
793 ,p_user_id => p_user_id
790 ,p_object_type => p_object_type
791 ,p_user_type => 'M'
792 ,p_program_id => p_program_id
794 ,p_machine => l_machine
795 ,p_terminal => l_terminal
796 ,x_return_status => x_return_status
797 ,x_msg_count => x_msg_count
798 ,x_msg_data => x_msg_data
799 );
800 END IF;
801
802 -- Lock the top level object
803 l_last_save_time :=
804 BSC_LOCKS_PVT.LOCK_OBJECT
805 (
806 p_object_key => p_object_key
807 ,p_object_type => p_object_type
808 ,p_lock_type => p_lock_type
809 ,p_query_time => p_query_time
810 ,p_program_id => p_program_id
811 ,p_user_id => p_user_id
812 ,p_machine => l_machine
813 ,p_terminal => l_terminal
814 ,x_return_status => x_return_status
815 ,x_msg_count => x_msg_count
816 ,x_msg_data => x_msg_data
817 );
818
819 -- Find out the child objects
820 l_lowest_level_type := NULL;
821 IF (p_program_id = -203 OR p_object_type = 'CUSTOM_VIEW') THEN
822 l_lowest_level_type := 'OBJECTIVE';
823 END IF;
824 BSC_LOCKS_PVT.GET_CHILD_OBJECTS
825 (
826 p_object_key => p_object_key
827 ,p_object_type => p_object_type
828 ,p_cascade_lock_level => p_cascade_lock_level
829 ,p_lowest_level_type => l_lowest_level_type
830 ,x_child_object_keys => l_child_object_keys
831 ,x_child_object_types => l_child_object_types
832 ,x_child_object_count => l_child_object_count
833 ,x_return_status => x_return_status
834 ,x_msg_count => x_msg_count
835 ,x_msg_data => x_msg_data
836 );
837
838 -- Lock the child objects
839 FOR i IN 1..l_child_object_count LOOP
840 l_last_save_time :=
841 BSC_LOCKS_PVT.LOCK_OBJECT
842 (
843 p_object_key => l_child_object_keys(i)
844 ,p_object_type => l_child_object_types(i)
845 ,p_lock_type => p_lock_type
846 ,p_query_time => p_query_time
847 ,p_program_id => p_program_id
848 ,p_user_id => p_user_id
849 ,p_machine => l_machine
850 ,p_terminal => l_terminal
851 ,x_return_status => x_return_status
852 ,x_msg_count => x_msg_count
853 ,x_msg_data => x_msg_data
854 );
855 END LOOP;
856
857 IF (p_lock_type <> 'R') THEN
858 -- Update the user information
859 BSC_LOCKS_PVT.UPDATE_USER_INFO
860 (
861 p_object_key => p_object_key
862 ,p_object_type => p_object_type
863 ,p_user_type => 'M'
864 ,p_program_id => p_program_id
865 ,p_user_id => p_user_id
866 ,p_machine => l_machine
867 ,p_terminal => l_terminal
868 ,x_return_status => x_return_status
869 ,x_msg_count => x_msg_count
870 ,x_msg_data => x_msg_data
871 );
872
873 -- Update last_save_time
874 BSC_LOCKS_PVT.UPDATE_LOCK_INFO
875 (
876 p_object_key => p_object_key
877 ,p_object_type => p_object_type
878 ,p_lock_type => p_lock_type
879 ,p_last_save_time => SYSDATE
880 ,p_session_id => l_session_id
881 ,x_return_status => x_return_status
882 ,x_msg_count => x_msg_count
883 ,x_msg_data => x_msg_data
884 );
885 END IF;
886
887 EXCEPTION
888 WHEN FND_API.G_EXC_ERROR THEN
889 ROLLBACK TO BSCLocksPubGetSystemLock;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 FND_MSG_PUB.Count_And_Get(
892 p_encoded => 'F'
893 ,p_count => x_msg_count
894 ,p_data => x_msg_data
895 );
896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
897 ROLLBACK TO BSCLocksPubGetSystemLock;
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 FND_MSG_PUB.Count_And_Get(
900 p_encoded => 'F'
901 ,p_count => x_msg_count
902 ,p_data => x_msg_data
903 );
904 WHEN OTHERS THEN
905 ROLLBACK TO BSCLocksPubGetSystemLock;
906 FND_MSG_PUB.Add_Exc_Msg(
907 G_PKG_NAME,
908 l_api_name,
909 SQLERRM
910 );
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 FND_MSG_PUB.Count_And_Get(
913 p_encoded => 'F'
914 ,p_count => x_msg_count
915 ,p_data => x_msg_data
916 );
917 END GET_SYSTEM_LOCK;
918
919
920 /*------------------------------------------------------------------------------------------
921 Procedure GET_SYSTEN_LOCKS
922 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
923 to be the locking procedure for BSC modules. Instead of calling the API
924 at the start of the process flow, this new API will be called right
925 before the changes are committed to the database.
926
927 Passing in the keys and types of the top-level objects, this API will
928 figure out all the related objects that needed to be locked.
929 If the locks cannot be acquired, either because some other users
933 <parameters>
930 are possessing one of more of the required locks or the data
931 has been modified since the last query time, an exception will be raised
932 indicating that the user has to requery and re-do the changes.
934 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
935 of the Object IDs. If the Object has composite keys,
936 the value to pass in will be a concatenation of
937 all the keys, separated by commas
938 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
939 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
940 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
941 "PERIODICITY", and "TABLE"
942 p_lock_type: 'W' for write lock, 'R' for read lock
943 p_query_time: The query time at the start of the process flow
944 p_program_id: -100 = Data Loader UI
945 -101 = Data Loader Backend
946 -200 = Generate Database
947 -201 = Generate Documentation
948 -202 = Rename Interface Table
949 -203 = Generate Database Configuration
950 -300 = Administrator
951 -400 = Objective Designer
952 -500 = Builder
953 -600 = Performance Scorecard
954 -700 = System Upgrade
955 -800 = System Migration
956 p_user_id: Application User ID
957 p_cascade_lock_level: Number of level for cascade locks
958 Default is -1 which means enable cascade locking
959 all the way to the lowest level
960 -------------------------------------------------------------------------------------------*/
961 Procedure GET_SYSTEM_LOCKS (
962 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
963 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
964 ,p_lock_type IN varchar2 := 'W'
965 ,p_query_time IN date
966 ,p_program_id IN number
967 ,p_user_id IN number := NULL
968 ,p_cascade_lock_level IN number := -1
969 ,x_return_status OUT NOCOPY varchar2
970 ,x_msg_count OUT NOCOPY number
971 ,x_msg_data OUT NOCOPY varchar2
972 ) IS
973
974 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCKS';
975
976 BEGIN
977 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCKS');
978 SAVEPOINT BSCLocksPubGetSystemLocks;
979 FND_MSG_PUB.Initialize;
980 x_return_status := FND_API.G_RET_STS_SUCCESS;
981 BSC_LOCKS_PVT.Initialize;
982
983 IF (p_object_keys.COUNT <> 0 AND
984 p_object_keys.COUNT = p_object_types.COUNT) THEN
985 FOR i IN p_object_keys.FIRST..p_object_keys.LAST LOOP
986 FOR j IN p_object_keys.FIRST..i LOOP
987 IF (i = j) THEN
988 BSC_LOCKS_PUB.GET_SYSTEM_LOCK
989 (
990 p_object_key => p_object_keys(i)
991 ,p_object_type => p_object_types(i)
992 ,p_lock_type => p_lock_type
993 ,p_query_time => p_query_time
994 ,p_program_id => p_program_id
995 ,p_user_id => p_user_id
996 ,p_cascade_lock_level => p_cascade_lock_level
997 ,x_return_status => x_return_status
998 ,x_msg_count => x_msg_count
999 ,x_msg_data => x_msg_data
1000 );
1001 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1002 RAISE FND_API.G_EXC_ERROR;
1003 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006 ELSIF (p_object_keys(i) = p_object_keys(j) AND
1007 p_object_types(i) = p_object_types(j)) THEN
1008 EXIT;
1009 END IF;
1010 END LOOP;
1011 END LOOP;
1012 END IF;
1013
1014 EXCEPTION
1015 WHEN FND_API.G_EXC_ERROR THEN
1016 ROLLBACK TO BSCLocksPubGetSystemLocks;
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018 FND_MSG_PUB.Count_And_Get(
1019 p_encoded => 'F'
1020 ,p_count => x_msg_count
1021 ,p_data => x_msg_data
1022 );
1023 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1024 ROLLBACK TO BSCLocksPubGetSystemLocks;
1025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1026 FND_MSG_PUB.Count_And_Get(
1027 p_encoded => 'F'
1028 ,p_count => x_msg_count
1029 ,p_data => x_msg_data
1030 );
1031 WHEN OTHERS THEN
1032 ROLLBACK TO BSCLocksPubGetSystemLocks;
1033 FND_MSG_PUB.Add_Exc_Msg(
1034 G_PKG_NAME,
1035 l_api_name,
1036 SQLERRM
1037 );
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1039 FND_MSG_PUB.Count_And_Get(
1040 p_encoded => 'F'
1041 ,p_count => x_msg_count
1042 ,p_data => x_msg_data
1043 );
1044 END GET_SYSTEM_LOCKS;
1045
1046
1047 /*------------------------------------------------------------------------------------------
1048 Procedure GET_SYSTEN_LOCKS
1049 This procedure replaces the existing API BSC_SECURITY.Check_System_Lock
1053
1050 to be the locking procedure for BSC modules. Instead of calling the API
1051 at the start of the process flow, this new API will be called right
1052 before the changes are committed to the database.
1054 Passing in the keys and types of the top-level objects, this API will
1055 figure out all the related objects that needed to be locked.
1056 If the locks cannot be acquired, either because some other users
1057 are possessing one of more of the required locks or the data
1058 has been modified since the last query time, an exception will be raised
1059 indicating that the user has to requery and re-do the changes.
1060 <parameters>
1061 p_object_keys: The primary key of the Objects, usually the TO_CHAR value
1062 of the Object IDs. If the Object has composite keys,
1063 the value to pass in will be a concatenation of
1064 all the keys, separated by commas
1065 p_object_types: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1066 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1067 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1068 "PERIODICITY", and "TABLE"
1069 p_lock_types: 'W' for write lock, 'R' for read lock
1070 p_query_time: The query time at the start of the process flow
1071 p_program_id: -100 = Data Loader UI
1072 -101 = Data Loader Backend
1073 -200 = Generate Database
1074 -201 = Generate Documentation
1075 -202 = Rename Interface Table
1076 -203 = Generate Database Configuration
1077 -300 = Administrator
1078 -400 = Objective Designer
1079 -500 = Builder
1080 -600 = Performance Scorecard
1081 -700 = System Upgrade
1082 -800 = System Migration
1083 p_user_id: Application User ID
1084 p_cascade_lock_level: Number of level for cascade locks
1085 Default is -1 which means enable cascade locking
1086 all the way to the lowest level
1087 -------------------------------------------------------------------------------------------*/
1088 Procedure GET_SYSTEM_LOCKS (
1089 p_object_keys IN BSC_LOCK_OBJECT_KEY_LIST
1090 ,p_object_types IN BSC_LOCK_OBJECT_TYPE_LIST
1091 ,p_lock_types IN BSC_LOCK_LOCK_TYPE_LIST
1092 ,p_query_time IN date
1093 ,p_program_id IN number
1094 ,p_user_id IN number := NULL
1095 ,p_cascade_lock_level IN number := -1
1096 ,x_return_status OUT NOCOPY varchar2
1097 ,x_msg_count OUT NOCOPY number
1098 ,x_msg_data OUT NOCOPY varchar2
1099 ) IS
1100
1101 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCKS';
1102
1103 BEGIN
1104 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCKS');
1105 SAVEPOINT BSCLocksPubGetSystemLocks;
1106 FND_MSG_PUB.Initialize;
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108 BSC_LOCKS_PVT.Initialize;
1109
1110 IF (p_object_keys.COUNT <> 0 AND
1111 p_object_keys.COUNT = p_object_types.COUNT AND
1112 p_object_keys.COUNT = p_lock_types.COUNT) THEN
1113 FOR i IN p_object_keys.FIRST..p_object_keys.LAST LOOP
1114 IF (p_lock_types(i) = 'W') THEN
1115 FOR j IN p_object_keys.FIRST..i LOOP
1116 IF (i = j) THEN
1117 BSC_LOCKS_PUB.GET_SYSTEM_LOCK
1118 (
1119 p_object_key => p_object_keys(i)
1120 ,p_object_type => p_object_types(i)
1121 ,p_lock_type => p_lock_types(i)
1122 ,p_query_time => p_query_time
1123 ,p_program_id => p_program_id
1124 ,p_user_id => p_user_id
1125 ,p_cascade_lock_level => p_cascade_lock_level
1126 ,x_return_status => x_return_status
1127 ,x_msg_count => x_msg_count
1128 ,x_msg_data => x_msg_data
1129 );
1130 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1131 RAISE FND_API.G_EXC_ERROR;
1132 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1134 END IF;
1135 ELSIF (p_lock_types(j) = 'W' AND
1136 p_object_keys(i) = p_object_keys(j) AND
1137 p_object_types(i) = p_object_types(j)) THEN
1138 EXIT;
1139 END IF;
1140 END LOOP;
1141 END IF;
1142 END LOOP;
1143 FOR i IN p_object_keys.FIRST..p_object_keys.LAST LOOP
1144 IF (p_lock_types(i) <> 'W') THEN
1145 FOR j IN p_object_keys.FIRST..i LOOP
1146 IF (i = j) THEN
1147 BSC_LOCKS_PUB.GET_SYSTEM_LOCK
1148 (
1149 p_object_key => p_object_keys(i)
1150 ,p_object_type => p_object_types(i)
1151 ,p_lock_type => p_lock_types(i)
1152 ,p_query_time => p_query_time
1153 ,p_program_id => p_program_id
1154 ,p_user_id => p_user_id
1155 ,p_cascade_lock_level => p_cascade_lock_level
1159 );
1156 ,x_return_status => x_return_status
1157 ,x_msg_count => x_msg_count
1158 ,x_msg_data => x_msg_data
1160 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1161 RAISE FND_API.G_EXC_ERROR;
1162 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164 END IF;
1165 ELSIF (p_object_keys(i) = p_object_keys(j) AND
1166 p_object_types(i) = p_object_types(j)) THEN
1167 EXIT;
1168 END IF;
1169 END LOOP;
1170 END IF;
1171 END LOOP;
1172 END IF;
1173
1174 EXCEPTION
1175 WHEN FND_API.G_EXC_ERROR THEN
1176 ROLLBACK TO BSCLocksPubGetSystemLocks;
1177 x_return_status := FND_API.G_RET_STS_ERROR;
1178 FND_MSG_PUB.Count_And_Get(
1179 p_encoded => 'F'
1180 ,p_count => x_msg_count
1181 ,p_data => x_msg_data
1182 );
1183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1184 ROLLBACK TO BSCLocksPubGetSystemLocks;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 FND_MSG_PUB.Count_And_Get(
1187 p_encoded => 'F'
1188 ,p_count => x_msg_count
1189 ,p_data => x_msg_data
1190 );
1191 WHEN OTHERS THEN
1192 ROLLBACK TO BSCLocksPubGetSystemLocks;
1193 FND_MSG_PUB.Add_Exc_Msg(
1194 G_PKG_NAME,
1195 l_api_name,
1196 SQLERRM
1197 );
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1199 FND_MSG_PUB.Count_And_Get(
1200 p_encoded => 'F'
1201 ,p_count => x_msg_count
1202 ,p_data => x_msg_data
1203 );
1204 END GET_SYSTEM_LOCKS;
1205
1206
1207 /*------------------------------------------------------------------------------------------
1208 Procedure GET_SYSTEN_LOCK
1209 This procedure locks all the objects with a certain type
1210 <parameters>
1211 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1212 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1213 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1214 "PERIODICITY", and "TABLE"
1215 p_lock_type: 'W' for write lock, 'R' for read lock
1216 p_query_time: The query time at the start of the process flow
1217 p_program_id: -100 = Data Loader UI
1218 -101 = Data Loader Backend
1219 -200 = Generate Database
1220 -201 = Generate Documentation
1221 -202 = Rename Interface Table
1222 -203 = Generate Database Configuration
1223 -300 = Administrator
1224 -400 = Objective Designer
1225 -500 = Builder
1226 -600 = Performance Scorecard
1227 -700 = System Upgrade
1228 -800 = System Migration
1229 p_user_id: Application User ID
1230 p_cascade_lock_level: Number of level for cascade locks
1231 Default is -1 which means enable cascade locking
1232 all the way to the lowest level
1233 -------------------------------------------------------------------------------------------*/
1234 Procedure GET_SYSTEM_LOCK (
1235 p_object_type IN varchar2
1236 ,p_lock_type IN varchar2 := 'W'
1237 ,p_query_time IN date
1238 ,p_program_id IN number
1239 ,p_user_id IN number := NULL
1240 ,p_cascade_lock_level IN number := -1
1241 ,x_return_status OUT NOCOPY varchar2
1242 ,x_msg_count OUT NOCOPY number
1243 ,x_msg_data OUT NOCOPY varchar2
1244 ) IS
1245
1246 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCK';
1247 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
1248 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
1249 l_session_id NUMBER := USERENV('SESSIONID');
1250
1251 CURSOR c_get_object(
1252 c_object_type VARCHAR2
1253 ) IS
1254 SELECT DISTINCT OBJECT_KEY
1255 FROM BSC_OBJECT_LOCKS
1256 WHERE OBJECT_TYPE = c_object_type;
1257
1258 BEGIN
1259 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCK: '||p_object_type);
1260 SAVEPOINT BSCLocksPubGetSystemLock;
1261 FND_MSG_PUB.Initialize;
1262 x_return_status := FND_API.G_RET_STS_SUCCESS;
1263 BSC_LOCKS_PVT.Initialize;
1264
1265 -- Get the machine and terminal information
1266 BSC_LOCKS_PVT.GET_SESSION
1267 (
1268 x_machine => l_machine
1269 ,x_terminal => l_terminal
1270 ,x_return_status => x_return_status
1271 ,x_msg_count => x_msg_count
1272 ,x_msg_data => x_msg_data
1273 );
1274
1275 -- Lock the user entry for the top level object
1276 IF (p_lock_type <> 'R') THEN
1277 BSC_LOCKS_PVT.LOCK_USER
1278 (
1279 p_object_key => 'ALL'
1280 ,p_object_type => p_object_type
1281 ,p_user_type => 'M'
1282 ,p_program_id => p_program_id
1283 ,p_user_id => p_user_id
1284 ,p_machine => l_machine
1285 ,p_terminal => l_terminal
1286 ,x_return_status => x_return_status
1287 ,x_msg_count => x_msg_count
1291
1288 ,x_msg_data => x_msg_data
1289 );
1290 END IF;
1292 -- Lock the object
1293 BSC_LOCKS_PVT.LOCK_OBJECT
1294 (
1295 p_object_type => p_object_type
1296 ,p_lock_type => p_lock_type
1297 ,p_query_time => p_query_time
1298 ,p_program_id => p_program_id
1299 ,p_user_id => p_user_id
1300 ,p_machine => l_machine
1301 ,p_terminal => l_terminal
1302 ,p_cascade_lock_level => p_cascade_lock_level
1303 ,x_return_status => x_return_status
1304 ,x_msg_count => x_msg_count
1305 ,x_msg_data => x_msg_data
1306 );
1307
1308 IF (p_lock_type <> 'R') THEN
1309 FOR cobj IN c_get_object(p_object_type) LOOP
1310 -- Update the user information
1311 BSC_LOCKS_PVT.UPDATE_USER_INFO
1312 (
1313 p_object_key => cobj.OBJECT_KEY
1314 ,p_object_type => p_object_type
1315 ,p_user_type => 'M'
1316 ,p_program_id => p_program_id
1317 ,p_user_id => p_user_id
1318 ,p_machine => l_machine
1319 ,p_terminal => l_terminal
1320 ,x_return_status => x_return_status
1321 ,x_msg_count => x_msg_count
1322 ,x_msg_data => x_msg_data
1323 );
1324
1325 -- Update last_save_time
1326 BSC_LOCKS_PVT.UPDATE_LOCK_INFO
1327 (
1328 p_object_key => cobj.OBJECT_KEY
1329 ,p_object_type => p_object_type
1330 ,p_lock_type => p_lock_type
1331 ,p_last_save_time => SYSDATE
1332 ,p_session_id => l_session_id
1333 ,x_return_status => x_return_status
1334 ,x_msg_count => x_msg_count
1335 ,x_msg_data => x_msg_data
1336 );
1337 END LOOP;
1338 END IF;
1339
1340
1341 EXCEPTION
1342 WHEN FND_API.G_EXC_ERROR THEN
1343 ROLLBACK TO BSCLocksPubGetSystemLock;
1344 x_return_status := FND_API.G_RET_STS_ERROR;
1345 FND_MSG_PUB.Count_And_Get(
1346 p_encoded => 'F'
1347 ,p_count => x_msg_count
1348 ,p_data => x_msg_data
1349 );
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351 ROLLBACK TO BSCLocksPubGetSystemLock;
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353 FND_MSG_PUB.Count_And_Get(
1354 p_encoded => 'F'
1355 ,p_count => x_msg_count
1356 ,p_data => x_msg_data
1357 );
1358 WHEN OTHERS THEN
1359 ROLLBACK TO BSCLocksPubGetSystemLock;
1360 FND_MSG_PUB.Add_Exc_Msg(
1361 G_PKG_NAME,
1362 l_api_name,
1363 SQLERRM
1364 );
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1366 FND_MSG_PUB.Count_And_Get(
1367 p_encoded => 'F'
1368 ,p_count => x_msg_count
1369 ,p_data => x_msg_data
1370 );
1371 END GET_SYSTEM_LOCK;
1372
1373
1374 /*------------------------------------------------------------------------------------------
1375 Procedure GET_SYSTEN_LOCK
1376 This procedure lock the whole table. This feature is used in Migration.
1377 <parameters>
1378 p_program_id: -100 = Data Loader UI
1379 -101 = Data Loader Backend
1380 -200 = Generate Database
1381 -201 = Generate Documentation
1382 -202 = Rename Interface Table
1383 -203 = Generate Database Configuration
1384 -300 = Administrator
1385 -400 = Objective Designer
1386 -500 = Builder
1387 -600 = Performance Scorecard
1388 -700 = System Upgrade
1389 -800 = System Migration
1390 p_user_id: Application User ID
1391 -------------------------------------------------------------------------------------------*/
1392 PROCEDURE GET_SYSTEM_LOCK(
1393 p_program_id IN number
1394 ,p_user_id IN number
1395 ,x_return_status OUT NOCOPY varchar2
1396 ,x_msg_count OUT NOCOPY number
1397 ,x_msg_data OUT NOCOPY varchar2
1398 ) IS
1399
1400 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCK';
1401 l_last_save_time BSC_OBJECT_LOCKS.LAST_SAVE_TIME%TYPE;
1402 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
1403 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
1404 l_insert_flag BOOLEAN;
1405 l_session_id NUMBER := USERENV('SESSIONID');
1406
1407 CURSOR c_locate_object(
1408 c_object_key VARCHAR2,
1409 c_object_type VARCHAR2,
1410 c_lock_type VARCHAR2
1411 ) IS
1412 SELECT LAST_SAVE_TIME
1413 FROM BSC_OBJECT_LOCKS
1414 WHERE OBJECT_KEY = c_object_key
1415 AND OBJECT_TYPE = c_object_type
1416 AND LOCK_TYPE = c_lock_type;
1417
1418 BEGIN
1419 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCK');
1420 SAVEPOINT BSCLocksPubGetSystemLock;
1421 FND_MSG_PUB.Initialize;
1422 x_return_status := FND_API.G_RET_STS_SUCCESS;
1423 BSC_LOCKS_PVT.Initialize;
1424 l_insert_flag := FALSE;
1425
1426 -- Get the machine and terminal information
1427 BSC_LOCKS_PVT.GET_SESSION
1428 (
1429 x_machine => l_machine
1430 ,x_terminal => l_terminal
1431 ,x_return_status => x_return_status
1432 ,x_msg_count => x_msg_count
1436 -- Create the entries for "ALL"
1433 ,x_msg_data => x_msg_data
1434 );
1435
1437 OPEN c_locate_object('ALL', 'ALL', 'W');
1438 FETCH c_locate_object INTO l_last_save_time;
1439 l_insert_flag := (c_locate_object%NOTFOUND);
1440 CLOSE c_locate_object;
1441 IF (l_insert_flag) THEN
1442 BSC_LOCKS_PVT.INSERT_LOCK_ALL_AUTONOMOUS
1443 (
1444 p_object_type => 'ALL'
1445 ,p_program_id => p_program_id
1446 ,p_user_id => p_user_id
1447 ,p_machine => l_machine
1448 ,p_terminal => l_terminal
1449 ,p_session_id => l_session_id
1450 ,x_return_status => x_return_status
1451 ,x_msg_count => x_msg_count
1452 ,x_msg_data => x_msg_data
1453 );
1454 END IF;
1455
1456 -- Lock the user entry
1457 BSC_LOCKS_PVT.LOCK_USER
1458 (
1459 p_object_key => 'ALL'
1460 ,p_object_type => 'ALL'
1461 ,p_user_type => 'M'
1462 ,p_program_id => p_program_id
1463 ,p_user_id => p_user_id
1464 ,p_machine => l_machine
1465 ,p_terminal => l_terminal
1466 ,x_return_status => x_return_status
1467 ,x_msg_count => x_msg_count
1468 ,x_msg_data => x_msg_data
1469 );
1470
1471 -- Check the Lock in the lock table
1472 SAVEPOINT BSCLocksPubLockObjectAll;
1473 BSC_LOCKS_PVT.LOCK_OBJECT_ALL
1474 (
1475 x_return_status => x_return_status
1476 ,x_msg_count => x_msg_count
1477 ,x_msg_data => x_msg_data
1478 );
1479 ROLLBACK TO BSCLocksPubLockObjectAll;
1480
1481 -- Update the user information for 'ALL'
1482 BSC_LOCKS_PVT.UPDATE_USER_INFO_AUTONOMOUS
1483 (
1484 p_object_key => 'ALL'
1485 ,p_object_type => 'ALL'
1486 ,p_user_type => 'L'
1487 ,p_program_id => p_program_id
1488 ,p_user_id => p_user_id
1489 ,p_machine => l_machine
1490 ,p_terminal => l_terminal
1491 ,x_return_status => x_return_status
1492 ,x_msg_count => x_msg_count
1493 ,x_msg_data => x_msg_data
1494 );
1495
1496 -- Lock the lock table
1497 BSC_LOCKS_PVT.LOCK_OBJECT_ALL
1498 (
1499 x_return_status => x_return_status
1500 ,x_msg_count => x_msg_count
1501 ,x_msg_data => x_msg_data
1502 );
1503
1504 -- Update the user information
1505 BSC_LOCKS_PVT.UPDATE_USER_INFO
1506 (
1507 p_object_key => 'ALL'
1508 ,p_object_type => 'ALL'
1509 ,p_user_type => 'ALL'
1510 ,p_program_id => p_program_id
1511 ,p_user_id => p_user_id
1512 ,p_machine => l_machine
1513 ,p_terminal => l_terminal
1514 ,x_return_status => x_return_status
1515 ,x_msg_count => x_msg_count
1516 ,x_msg_data => x_msg_data
1517 );
1518
1519 -- Update last_save_time
1520 BSC_LOCKS_PVT.UPDATE_LOCK_INFO
1521 (
1522 p_object_key => 'ALL'
1523 ,p_object_type => 'ALL'
1524 ,p_lock_type => 'ALL'
1525 ,p_last_save_time => SYSDATE
1526 ,p_session_id => l_session_id
1527 ,x_return_status => x_return_status
1528 ,x_msg_count => x_msg_count
1529 ,x_msg_data => x_msg_data
1530 );
1531
1532 EXCEPTION
1533 WHEN FND_API.G_EXC_ERROR THEN
1534 ROLLBACK TO BSCLocksPubGetSystemLock;
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 FND_MSG_PUB.Count_And_Get(
1537 p_encoded => 'F'
1538 ,p_count => x_msg_count
1539 ,p_data => x_msg_data
1540 );
1541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1542 ROLLBACK TO BSCLocksPubGetSystemLock;
1543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1544 FND_MSG_PUB.Count_And_Get(
1545 p_encoded => 'F'
1546 ,p_count => x_msg_count
1547 ,p_data => x_msg_data
1548 );
1549 WHEN OTHERS THEN
1550 ROLLBACK TO BSCLocksPubGetSystemLock;
1551 FND_MSG_PUB.Add_Exc_Msg(
1552 G_PKG_NAME,
1553 l_api_name,
1554 SQLERRM
1555 );
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 FND_MSG_PUB.Count_And_Get(
1558 p_encoded => 'F'
1559 ,p_count => x_msg_count
1560 ,p_data => x_msg_data
1561 );
1562 END GET_SYSTEM_LOCK;
1563
1564
1565 /*------------------------------------------------------------------------------------------
1566 Procedure GET_SYSTEM_LOCK
1567 Due to the fact that not all the BSC modules will uptake the new locking scheme
1568 immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
1569 for backward compatibility issues. In this procedure, we will check for whole
1570 system exclusive locks acquired by modules that haven't uptaken the new
1571 locking scheme. Only when none of these modules have acquired locks that
1572 the new Object-Level locking will be proceeded. Next, a row will be inserted
1573 into BSC_CURRENT_SESSIONS. It will be seen as a whole system exclusive lock by
1574 modules haven't implemented the new locking scheme. For modules that have uptaken
1575 the new locking scheme, those entries in BSC_CURRENT_SESSION will be ignored.
1576 <parameters>
1577 p_program_id: -100 = Data Loader UI
1578 -101 = Data Loader Backend
1579 -200 = Generate Database
1583 -300 = Administrator
1580 -201 = Generate Documentation
1581 -202 = Rename Interface Table
1582 -203 = Generate Database Configuration
1584 -400 = Objective Designer
1585 -500 = Builder
1586 -600 = Performance Scorecard
1587 -700 = System Upgrade
1588 -800 = System Migration
1589 p_user_id: Application User ID
1590 p_icx_session_id: Application Session ID
1591 -------------------------------------------------------------------------------------------*/
1592 Procedure GET_SYSTEM_LOCK (
1593 p_program_id IN number
1594 ,p_user_id IN number
1595 ,p_icx_session_id IN number
1596 ,x_return_status OUT NOCOPY varchar2
1597 ,x_msg_count OUT NOCOPY number
1598 ,x_msg_data OUT NOCOPY varchar2
1599 ) IS
1600 PRAGMA AUTONOMOUS_TRANSACTION;
1601
1602 l_api_name CONSTANT VARCHAR2(30) := 'GET_SYSTEM_LOCK';
1603 l_session_id NUMBER;
1604
1605 CURSOR c_conflict_session(c_program_id NUMBER) IS
1606 SELECT c.program_id, u.user_name, s.machine, s.terminal
1607 FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u
1608 WHERE c.session_id = s.audsid
1609 AND ((c_program_id = -100 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1610 OR (c_program_id = -101 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1611 OR (c_program_id = -200 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1612 OR (c_program_id = -201 AND c.program_id IN (-400, -500, -700, -800, -802))
1613 OR (c_program_id = -202 AND c.program_id IN (-700, -800, -802))
1614 OR (c_program_id = -300 AND c.program_id IN (-100, -101, -200, -700, -800, -802))
1615 OR (c_program_id = -400 AND c.program_id IN (-100, -101, -200, -201, -700, -800, -802))
1616 OR (c_program_id = -500 AND c.program_id IN (-100, -101, -200, -201, -700, -800, -802))
1617 OR (c_program_id = -600 AND c.program_id IN (-700, -800))
1618 OR (c_program_id = -801 AND c.program_id IN (-700, -800, -801, -802))
1619 OR (c_program_id = -802 AND c.program_id IN (-100, -101, -200, -201, -202, -300, -400, -500, -700, -800, -801))
1620 OR (c_program_id NOT IN (-100, -101, -200, -201, -202, -300, -400, -500, -600, -801, -802)
1621 AND c.program_id IN (-100, -101, -200, -201, -202, -300, -400, -500, -600, -700, -800, -801, -802)))
1622 AND c.session_id <> USERENV('SESSIONID')
1623 AND c.user_id = u.user_id (+);
1624
1625 CURSOR c_existing_session(c_program_id NUMBER, c_icx_session_id NUMBER) IS
1626 SELECT SESSION_ID
1627 FROM BSC_CURRENT_SESSIONS
1628 WHERE SESSION_ID = USERENV('SESSIONID')
1629 AND ICX_SESSION_ID = c_icx_session_id
1630 AND PROGRAM_ID = c_program_id;
1631
1632 BEGIN
1633 --DBMS_OUTPUT.PUT_LINE('GET_SYSTEM_LOCK');
1634 FND_MSG_PUB.Initialize;
1635 x_return_status := FND_API.G_RET_STS_SUCCESS;
1636 BSC_LOCKS_PVT.Initialize;
1637
1638 BSC_SECURITY.Refresh_System_Lock(p_program_id);
1639 --Delete all orphan the sessions
1640 --DELETE BSC_CURRENT_SESSIONS
1641 --WHERE SESSION_ID NOT IN
1642 -- (SELECT VS.AUDSID
1643 -- FROM V$SESSION VS);
1644 --
1645 --Delete all the session not being reused by FND
1646 --DELETE BSC_CURRENT_SESSIONS
1647 --WHERE ICX_SESSION_ID IN (
1648 -- SELECT SESSION_ID
1649 -- FROM ICX_SESSIONS
1650 -- WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') <> 'VALID'));
1651 --
1652 --Delete all sessions, which have their concurrent programs in invalid or hang status
1653 --DELETE BSC_CURRENT_SESSIONS
1654 --WHERE SESSION_ID IN (
1655 -- SELECT NVL(ORACLE_SESSION_ID, -1)
1656 -- FROM FND_CONCURRENT_REQUESTS
1657 -- WHERE PHASE_CODE = 'C');
1658 --
1659 -- Kill IViewer Sessions that have been INACTIVE more than 20 minutes
1660 --IF p_program_id <> -600 THEN
1661 -- IF BSC_APPS.APPS_ENV THEN
1662 -- DELETE BSC_CURRENT_SESSIONS
1663 -- WHERE PROGRAM_ID = -600
1664 -- AND SESSION_ID IN (
1665 -- SELECT s.audsid
1666 -- FROM v$session s, v$session_wait w
1667 -- WHERE s.sid = w.sid
1668 -- AND w.seconds_in_wait > 1200);
1669 -- END IF;
1670 --END IF;
1671 --
1672 --Delete all the Killed Sessions
1673 --DELETE BSC_CURRENT_SESSIONS
1674 --WHERE SESSION_ID IN (
1675 -- SELECT VS.AUDSID
1676 -- FROM V$SESSION VS
1677 -- WHERE VS.STATUS = 'KILLED');
1678 --COMMIT;
1679
1680 FOR cd IN c_conflict_session(p_program_id) LOOP
1681 FND_MESSAGE.SET_NAME('BSC','BSC_SEC_LOCKED_SYSTEM');
1682 FND_MESSAGE.SET_TOKEN('COMPONENT',BSC_LOCKS_PVT.g_modules(cd.program_id) , TRUE);
1683 FND_MESSAGE.SET_TOKEN('USERNAME' ,cd.user_name, TRUE);
1684 FND_MESSAGE.SET_TOKEN('MACHINE' ,cd.machine, TRUE);
1685 FND_MESSAGE.SET_TOKEN('TERMINAL' ,cd.terminal, TRUE);
1686 FND_MSG_PUB.ADD;
1687 RAISE FND_API.G_EXC_ERROR;
1688 END LOOP;
1689
1690 OPEN c_existing_session(p_program_id, p_icx_session_id);
1691 FETCH c_existing_session INTO l_session_id;
1692 IF (c_existing_session%NOTFOUND) THEN
1693 INSERT INTO BSC_CURRENT_SESSIONS (
1694 SESSION_ID,
1695 PROGRAM_ID,
1696 CREATED_BY,
1697 CREATION_DATE,
1698 LAST_UPDATED_BY,
1699 LAST_UPDATE_DATE,
1700 LAST_UPDATE_LOGIN,
1701 USER_ID,
1702 ICX_SESSION_ID
1703 ) VALUES (
1704 USERENV('SESSIONID'),
1705 p_program_id,
1706 FND_GLOBAL.USER_ID,
1707 SYSDATE,
1708 FND_GLOBAL.USER_ID,
1709 SYSDATE,
1710 USERENV('SESSIONID'),
1711 p_user_id,
1712 p_icx_session_id
1713 );
1714 COMMIT;
1715 END IF;
1716 CLOSE c_existing_session;
1717
1718 EXCEPTION
1719 WHEN FND_API.G_EXC_ERROR THEN
1720 x_return_status := FND_API.G_RET_STS_ERROR;
1721 FND_MSG_PUB.Count_And_Get(
1722 p_encoded => 'F'
1723 ,p_count => x_msg_count
1724 ,p_data => x_msg_data
1725 );
1726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 FND_MSG_PUB.Count_And_Get(
1729 p_encoded => 'F'
1730 ,p_count => x_msg_count
1731 ,p_data => x_msg_data
1732 );
1733 WHEN OTHERS THEN
1734 FND_MSG_PUB.Add_Exc_Msg(
1735 G_PKG_NAME,
1736 l_api_name,
1737 SQLERRM
1738 );
1739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1740 FND_MSG_PUB.Count_And_Get(
1741 p_encoded => 'F'
1742 ,p_count => x_msg_count
1743 ,p_data => x_msg_data
1744 );
1745 END GET_SYSTEM_LOCK;
1746
1747
1748 /*------------------------------------------------------------------------------------------
1749 Procedure REMOVE_SYSTEM_LOCK
1750 Due to the fact that not all the BSC modules will uptake the new locking scheme
1751 immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
1752 for backward compatibility issues. This procedure will be called at the end
1753 of the process. The entry in BSC_CURRENT_SESSIONS for the current session
1754 will be deleted
1755 <parameters>
1756 none
1757 -------------------------------------------------------------------------------------------*/
1758 Procedure REMOVE_SYSTEM_LOCK
1759 IS
1760 PRAGMA AUTONOMOUS_TRANSACTION;
1761 BEGIN
1762 --DBMS_OUTPUT.PUT_LINE('REMOVE_SYSTEM_LOCK');
1763 DELETE BSC_CURRENT_SESSIONS
1764 WHERE SESSION_ID = USERENV('SESSIONID');
1765 COMMIT;
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 NULL;
1769 END REMOVE_SYSTEM_LOCK;
1770
1771 END BSC_LOCKS_PUB;