[Home] [Help]
PACKAGE BODY: APPS.BSC_LOCKS_PVT
Source
1 PACKAGE BODY BSC_LOCKS_PVT AS
2 /*$Header: BSCVLOKB.pls 120.8 2005/12/07 18:27:12 calaw noship $ */
3
4 /*------------------------------------------------------------------------------------------
5 Procedure VALIDATE_OBJECT
6 This procedure inspects the validity of an Object.
7 An exception will be raised if the Object does not exist in the database.
8 <parameters>
9 p_object_key: The primary key of the Object, usually the TO_CHAR value
10 of the Object ID. If the Object has composite keys,
11 the value to pass in will be a concatenation of
12 all the keys, separated by commas
13 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
14 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
15 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
16 "PERIODICITY", and "TABLE"
17 -------------------------------------------------------------------------------------------*/
18 PROCEDURE VALIDATE_OBJECT(
19 p_object_key IN varchar2
20 ,p_object_type IN varchar2
21 ,x_return_status OUT NOCOPY varchar2
22 ,x_msg_count OUT NOCOPY number
23 ,x_msg_data OUT NOCOPY varchar2
24 ) IS
25
26 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_OBJECT';
27 l_valid NUMBER;
28 l_object_key1 NUMBER;
29 l_object_key2 NUMBER;
30
31 CURSOR c_validate_scorecard(c_tab_id NUMBER) IS
32 SELECT 1 FROM BSC_TABS_B
33 WHERE TAB_ID = c_tab_id;
34 CURSOR c_validate_objective(c_indicator NUMBER) IS
35 SELECT 1 FROM BSC_KPIS_B
36 WHERE INDICATOR = c_indicator;
37 CURSOR c_validate_dimension(c_dim_group_id NUMBER) IS
38 SELECT 1 FROM BSC_SYS_DIM_GROUPS_TL
39 WHERE DIM_GROUP_ID = c_dim_group_id;
40 CURSOR c_validate_dimension_object(c_dim_level_id NUMBER) IS
41 SELECT 1 FROM BSC_SYS_DIM_LEVELS_B
42 WHERE DIM_LEVEL_ID = c_dim_level_id;
43 CURSOR c_validate_measure(c_dataset_id NUMBER) IS
44 SELECT 1 FROM BSC_SYS_DATASETS_B
45 WHERE DATASET_ID = c_dataset_id;
46 CURSOR c_validate_data_column(c_measure_id NUMBER) IS
47 SELECT 1 FROM BSC_SYS_MEASURES
48 WHERE MEASURE_ID = c_measure_id;
49 CURSOR c_validate_custom_view(c_tab_id NUMBER, c_tab_view_id NUMBER) IS
50 SELECT 1 FROM BSC_TAB_VIEWS_B
51 WHERE TAB_ID = c_tab_id
52 AND TAB_VIEW_ID = c_tab_view_id;
53 CURSOR c_validate_launchpad(c_menu_id NUMBER) IS
54 SELECT 1 FROM FND_MENUS
55 WHERE MENU_ID = c_menu_id;
56 CURSOR c_validate_periodicity(c_periodicity_id NUMBER) IS
57 SELECT 1 FROM BSC_SYS_PERIODICITIES
58 WHERE PERIODICITY_ID = c_periodicity_id;
59 CURSOR c_validate_calendar(c_calendar_id NUMBER) IS
60 SELECT 1 FROM BSC_SYS_CALENDARS_B
61 WHERE CALENDAR_ID = c_calendar_id;
62 CURSOR c_validate_table(c_table_name VARCHAR2) IS
63 SELECT 1 FROM BSC_DB_TABLES
64 WHERE TABLE_NAME = c_table_name;
65
66 BEGIN
67 --DBMS_OUTPUT.PUT_LINE('VALIDATE_OBJECT: '||p_object_key||' '||p_object_type);
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 l_valid := 0;
71 IF (UPPER(p_object_type) = 'SCORECARD') THEN
72 l_object_key1 := TO_NUMBER(p_object_key);
73 OPEN c_validate_scorecard(l_object_key1);
74 FETCH c_validate_scorecard INTO l_valid;
75 CLOSE c_validate_scorecard;
76
77 ELSIF (UPPER(p_object_type) = 'OBJECTIVE') THEN
78 l_object_key1 := TO_NUMBER(p_object_key);
79 OPEN c_validate_objective(l_object_key1);
80 FETCH c_validate_objective INTO l_valid;
81 CLOSE c_validate_objective;
82
83 ELSIF (UPPER(p_object_type) = 'DIMENSION') THEN
84 l_object_key1 := TO_NUMBER(p_object_key);
85 OPEN c_validate_dimension(l_object_key1);
86 FETCH c_validate_dimension INTO l_valid;
87 CLOSE c_validate_dimension;
88
89 ELSIF (UPPER(p_object_type) = 'DIMENSION_OBJECT') THEN
90 l_object_key1 := TO_NUMBER(p_object_key);
91 OPEN c_validate_dimension_object(l_object_key1);
92 FETCH c_validate_dimension_object INTO l_valid;
93 CLOSE c_validate_dimension_object;
94
95 ELSIF (UPPER(p_object_type) = 'MEASURE') THEN
96 l_object_key1 := TO_NUMBER(p_object_key);
97 OPEN c_validate_measure(l_object_key1);
98 FETCH c_validate_measure INTO l_valid;
99 CLOSE c_validate_measure;
100
101 ELSIF (UPPER(p_object_type) = 'DATA_COLUMN') THEN
102 l_object_key1 := TO_NUMBER(p_object_key);
103 OPEN c_validate_data_column(l_object_key1);
104 FETCH c_validate_data_column INTO l_valid;
105 CLOSE c_validate_data_column;
106
107 ELSIF (UPPER(p_object_type) = 'CUSTOM_VIEW') THEN
108 l_object_key1 := SUBSTR(p_object_key, 1, INSTR(p_object_key, ',')-1);
109 l_object_key2 := SUBSTR(p_object_key, INSTR(p_object_key, ',')+1);
110 --DBMS_OUTPUT.PUT_LINE('l_object_key1 = '||l_object_key1);
111 --DBMS_OUTPUT.PUT_LINE('l_object_key2 = '||l_object_key2);
112 OPEN c_validate_custom_view(l_object_key1, l_object_key2);
113 FETCH c_validate_custom_view INTO l_valid;
114 CLOSE c_validate_custom_view;
115
116 ELSIF (UPPER(p_object_type) = 'LAUNCHPAD') THEN
117 l_object_key1 := TO_NUMBER(p_object_key);
118 OPEN c_validate_launchpad(l_object_key1);
119 FETCH c_validate_launchpad INTO l_valid;
120 CLOSE c_validate_launchpad;
121
122 ELSIF (UPPER(p_object_type) = 'PERIODICITY') THEN
123 l_object_key1 := TO_NUMBER(p_object_key);
124 OPEN c_validate_periodicity(l_object_key1);
125 FETCH c_validate_periodicity INTO l_valid;
126 CLOSE c_validate_periodicity;
127
128 ELSIF (UPPER(p_object_type) = 'CALENDAR') THEN
129 l_object_key1 := TO_NUMBER(p_object_key);
130 OPEN c_validate_calendar(l_object_key1);
131 FETCH c_validate_calendar INTO l_valid;
132 CLOSE c_validate_calendar;
133
134 ELSIF (UPPER(p_object_type) = 'TABLE') THEN
135 l_object_key1 := p_object_key;
136 OPEN c_validate_table(l_object_key1);
137 FETCH c_validate_table INTO l_valid;
138 CLOSE c_validate_table;
139
140 ELSE
141 RETURN;
142 END IF;
143
144 --DBMS_OUTPUT.PUT_LINE('l_valid = '||l_valid);
145 IF (l_valid <> 1) THEN
146 --DBMS_OUTPUT.PUT_LINE('OBJECT NOT VALID');
147 FND_MESSAGE.SET_NAME('BSC','BSC_LOCK_ERR_INVALID');
148 FND_MSG_PUB.ADD;
149 RAISE FND_API.G_EXC_ERROR;
150 END IF;
151 --DBMS_OUTPUT.PUT_LINE('OBJECT VALID');
152
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 FND_MSG_PUB.Count_And_Get(
157 p_encoded => 'F'
158 ,p_count => x_msg_count
159 ,p_data => x_msg_data
160 );
161 RAISE;
162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 FND_MSG_PUB.Count_And_Get(
165 p_encoded => 'F'
166 ,p_count => x_msg_count
167 ,p_data => x_msg_data
168 );
169 RAISE;
170 WHEN OTHERS THEN
171 FND_MSG_PUB.Add_Exc_Msg(
172 G_PKG_NAME,
173 l_api_name,
174 SQLERRM
175 );
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 FND_MSG_PUB.Count_And_Get(
178 p_encoded => 'F'
179 ,p_count => x_msg_count
180 ,p_data => x_msg_data
181 );
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END VALIDATE_OBJECT;
184
185
186 /*------------------------------------------------------------------------------------------
187 Function LOCK_OBJECT
188 This function puts a database lock on the corresponding row in the
189 lock table and returns the last_save_time value. If the row does
190 not exist, a new row will be inserted to the lock table.
191 In addition to that, the user table will also be updated.
192 If someone else already locked the object, an exception will be raised.
193 <parameters>
194 p_object_key: The primary key of the Object, usually the TO_CHAR value
195 of the Object ID. If the Object has composite keys,
196 the value to pass in will be a concatenation of
197 all the keys, separated by commas
198 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
199 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
200 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
201 "PERIODICITY", and "TABLE"
202 p_lock_type: 'W' for write lock, 'R' for read lock
203 p_query_time: The query time at the start of the process flow
204 p_program_id: -100 = Data Loader UI
205 -101 = Data Loader Backend
206 -200 = Generate Database
207 -201 = Generate Documentation
208 -202 = Rename Interface Table
209 -203 = Generate Database Configuration
210 -300 = Administrator
211 -400 = Objective Designer
212 -500 = Builder
213 -600 = Performance Scorecard
214 -700 = System Upgrade
215 -800 = System Migration
216 p_user_id: Application User ID
217 p_machine: The Machine
218 p_terminal: The Terminal
219 -------------------------------------------------------------------------------------------*/
220 FUNCTION LOCK_OBJECT(
221 p_object_key IN varchar2
222 ,p_object_type IN varchar2
223 ,p_lock_type IN varchar2
224 ,p_query_time IN date
225 ,p_program_id IN number
226 ,p_user_id IN number
227 ,p_machine IN varchar2
228 ,p_terminal IN varchar2
229 ,x_return_status OUT NOCOPY varchar2
230 ,x_msg_count OUT NOCOPY number
231 ,x_msg_data OUT NOCOPY varchar2
232 ) RETURN DATE IS
233
234 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT';
235 l_last_save_time DATE;
236 l_session_id NUMBER := USERENV('SESSIONID');
237 l_last_session_id NUMBER;
238
239 CURSOR c_get_session_id(
240 c_object_key VARCHAR2,
241 c_object_type VARCHAR2
242 ) IS
243 SELECT SESSION_ID
244 FROM BSC_OBJECT_LOCKS
245 WHERE OBJECT_KEY = c_object_key
246 AND OBJECT_TYPE = c_object_type
247 AND LOCK_TYPE = 'W';
248
249 BEGIN
250 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT: '||p_object_key||' '||p_object_type);
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 l_last_save_time := NULL;
253
254 IF (p_lock_type = 'R') THEN
255 l_last_save_time :=
256 BSC_LOCKS_PVT.LOCK_OBJECT_READ
257 (
258 p_object_key => p_object_key
259 ,p_object_type => p_object_type
260 ,p_program_id => p_program_id
261 ,p_user_id => p_user_id
262 ,p_machine => p_machine
263 ,p_terminal => p_terminal
264 ,x_return_status => x_return_status
265 ,x_msg_count => x_msg_count
266 ,x_msg_data => x_msg_data
267 );
268 ELSE
269 l_last_save_time :=
270 BSC_LOCKS_PVT.LOCK_OBJECT_WRITE
271 (
272 p_object_key => p_object_key
273 ,p_object_type => p_object_type
274 ,p_program_id => p_program_id
275 ,p_user_id => p_user_id
276 ,p_machine => p_machine
277 ,p_terminal => p_terminal
278 ,x_return_status => x_return_status
279 ,x_msg_count => x_msg_count
280 ,x_msg_data => x_msg_data
281 );
282 END IF;
283
284 -- Check the last save time
285 IF (l_last_save_time IS NOT NULL AND l_last_save_time > p_query_time) THEN
286 OPEN c_get_session_id(p_object_key, p_object_type);
287 FETCH c_get_session_id INTO l_last_session_id;
288 CLOSE c_get_session_id;
289 IF (l_last_session_id IS NOT NULL AND l_last_session_id <> l_session_id) THEN
290 BSC_LOCKS_PVT.RAISE_EXCEPTION
291 (
292 p_object_key => p_object_key
293 ,p_object_type => p_object_type
294 ,p_exception_type => 'M'
295 ,x_return_status => x_return_status
296 ,x_msg_count => x_msg_count
297 ,x_msg_data => x_msg_data
298 );
299 END IF;
300 END IF;
301 RETURN l_last_save_time;
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 FND_MSG_PUB.Count_And_Get(
307 p_encoded => 'F'
308 ,p_count => x_msg_count
309 ,p_data => x_msg_data
310 );
311 RAISE;
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 FND_MSG_PUB.Count_And_Get(
315 p_encoded => 'F'
316 ,p_count => x_msg_count
317 ,p_data => x_msg_data
318 );
319 RAISE;
320 WHEN OTHERS THEN
321 FND_MSG_PUB.Add_Exc_Msg(
322 G_PKG_NAME,
323 l_api_name,
324 SQLERRM
325 );
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 FND_MSG_PUB.Count_And_Get(
328 p_encoded => 'F'
329 ,p_count => x_msg_count
330 ,p_data => x_msg_data
331 );
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END LOCK_OBJECT;
334
335
336 /*------------------------------------------------------------------------------------------
337 Procedure LOCK_OBJECT
338 This procedure locks all the objects with a certain type
339 <parameters>
340 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
341 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
342 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
343 "PERIODICITY", and "TABLE"
344 p_lock_type: 'W' for write lock, 'R' for read lock
345 p_query_time: The query time at the start of the process flow
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 p_machine: The Machine
360 p_terminal: The Terminal
361 p_cascade_lock_level: Number of level for cascade locks
362 Default is -1 which means enable cascade locking
363 all the way to the lowest level
364 -------------------------------------------------------------------------------------------*/
365 Procedure LOCK_OBJECT(
366 p_object_type IN varchar2
367 ,p_lock_type IN varchar2
368 ,p_query_time IN date
369 ,p_program_id IN number
370 ,p_user_id IN number
371 ,p_machine IN varchar2
372 ,p_terminal IN varchar2
373 ,p_cascade_lock_level IN number
374 ,x_return_status OUT NOCOPY varchar2
375 ,x_msg_count OUT NOCOPY number
376 ,x_msg_data OUT NOCOPY varchar2
377 ) IS
378
379 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT';
380 l_last_save_time DATE;
381
382 CURSOR c_get_object(
383 c_object_type VARCHAR2
384 ) IS
385 SELECT DISTINCT OBJECT_KEY
386 FROM BSC_OBJECT_LOCKS
387 WHERE OBJECT_TYPE = c_object_type;
388
389 BEGIN
390 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT: '||p_object_type);
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392 l_last_save_time := NULL;
393
394 -- Lock the top level object
395 FOR cobj IN c_get_object(p_object_type) LOOP
396 l_last_save_time :=
397 BSC_LOCKS_PVT.LOCK_OBJECT
398 (
399 p_object_key => cobj.OBJECT_KEY
400 ,p_object_type => p_object_type
401 ,p_lock_type => p_lock_type
402 ,p_query_time => p_query_time
403 ,p_program_id => p_program_id
404 ,p_user_id => p_user_id
405 ,p_machine => p_machine
406 ,p_terminal => p_terminal
407 ,x_return_status => x_return_status
408 ,x_msg_count => x_msg_count
409 ,x_msg_data => x_msg_data
410 );
411 END LOOP;
412
413 -- Lock the ALL entry
414 l_last_save_time :=
415 BSC_LOCKS_PVT.LOCK_OBJECT
416 (
417 p_object_key => 'ALL'
418 ,p_object_type => p_object_type
419 ,p_lock_type => p_lock_type
420 ,p_query_time => p_query_time
421 ,p_program_id => p_program_id
422 ,p_user_id => p_user_id
423 ,p_machine => p_machine
424 ,p_terminal => p_terminal
425 ,x_return_status => x_return_status
426 ,x_msg_count => x_msg_count
427 ,x_msg_data => x_msg_data
428 );
429
430 -- Lock the child objects
431 IF (p_cascade_lock_level <> 0) THEN
432 IF (UPPER(p_object_type) = 'SCORECARD') THEN
433 BSC_LOCKS_PVT.LOCK_OBJECT
434 (
435 p_object_type => 'OBJECTIVE'
436 ,p_lock_type => p_lock_type
437 ,p_query_time => p_query_time
438 ,p_program_id => p_program_id
439 ,p_user_id => p_user_id
440 ,p_machine => p_machine
441 ,p_terminal => p_terminal
442 ,p_cascade_lock_level => p_cascade_lock_level - 1
443 ,x_return_status => x_return_status
444 ,x_msg_count => x_msg_count
445 ,x_msg_data => x_msg_data
446 );
447 BSC_LOCKS_PVT.LOCK_OBJECT
448 (
449 p_object_type => 'CUSTOM_VIEW'
450 ,p_lock_type => p_lock_type
451 ,p_query_time => p_query_time
452 ,p_program_id => p_program_id
453 ,p_user_id => p_user_id
454 ,p_machine => p_machine
455 ,p_terminal => p_terminal
456 ,p_cascade_lock_level => 0
457 ,x_return_status => x_return_status
458 ,x_msg_count => x_msg_count
459 ,x_msg_data => x_msg_data
460 );
461 IF (p_cascade_lock_level > 1) THEN
462 BSC_LOCKS_PVT.LOCK_OBJECT
463 (
464 p_object_type => 'LAUNCHPAD'
465 ,p_lock_type => p_lock_type
466 ,p_query_time => p_query_time
467 ,p_program_id => p_program_id
468 ,p_user_id => p_user_id
469 ,p_machine => p_machine
470 ,p_terminal => p_terminal
471 ,p_cascade_lock_level => 0
472 ,x_return_status => x_return_status
473 ,x_msg_count => x_msg_count
474 ,x_msg_data => x_msg_data
475 );
476 END IF;
477
478 -- No cascade lock for generate database configuration
479 ELSIF (UPPER(p_object_type) = 'OBJECTIVE' AND p_program_id <> -203) THEN
480 BSC_LOCKS_PVT.LOCK_OBJECT
481 (
482 p_object_type => 'DIMENSION'
483 ,p_lock_type => p_lock_type
484 ,p_query_time => p_query_time
485 ,p_program_id => p_program_id
486 ,p_user_id => p_user_id
487 ,p_machine => p_machine
488 ,p_terminal => p_terminal
489 ,p_cascade_lock_level => p_cascade_lock_level - 1
490 ,x_return_status => x_return_status
491 ,x_msg_count => x_msg_count
492 ,x_msg_data => x_msg_data
493 );
494 BSC_LOCKS_PVT.LOCK_OBJECT
495 (
496 p_object_type => 'MEASURE'
497 ,p_lock_type => p_lock_type
498 ,p_query_time => p_query_time
499 ,p_program_id => p_program_id
500 ,p_user_id => p_user_id
501 ,p_machine => p_machine
502 ,p_terminal => p_terminal
503 ,p_cascade_lock_level => p_cascade_lock_level - 1
504 ,x_return_status => x_return_status
505 ,x_msg_count => x_msg_count
506 ,x_msg_data => x_msg_data
507 );
508
509 ELSIF (UPPER(p_object_type) = 'DIMENSION') THEN
510 BSC_LOCKS_PVT.LOCK_OBJECT
511 (
512 p_object_type => 'DIMENSION_OBJECT'
513 ,p_lock_type => p_lock_type
514 ,p_query_time => p_query_time
515 ,p_program_id => p_program_id
516 ,p_user_id => p_user_id
517 ,p_machine => p_machine
518 ,p_terminal => p_terminal
519 ,p_cascade_lock_level => p_cascade_lock_level - 1
520 ,x_return_status => x_return_status
521 ,x_msg_count => x_msg_count
522 ,x_msg_data => x_msg_data
523 );
524
525 ELSIF (UPPER(p_object_type) = 'MEASURE') THEN
526 BSC_LOCKS_PVT.LOCK_OBJECT
527 (
528 p_object_type => 'DATA_COLUMN'
529 ,p_lock_type => p_lock_type
530 ,p_query_time => p_query_time
531 ,p_program_id => p_program_id
532 ,p_user_id => p_user_id
533 ,p_machine => p_machine
534 ,p_terminal => p_terminal
535 ,p_cascade_lock_level => p_cascade_lock_level - 1
536 ,x_return_status => x_return_status
537 ,x_msg_count => x_msg_count
538 ,x_msg_data => x_msg_data
539 );
540
541 -- Custom View: only lock 1 level down
542 ELSIF (UPPER(p_object_type) = 'CUSTOM_VIEW') THEN
543 BSC_LOCKS_PVT.LOCK_OBJECT
544 (
545 p_object_type => 'LAUNCHPAD'
546 ,p_lock_type => p_lock_type
547 ,p_query_time => p_query_time
548 ,p_program_id => p_program_id
549 ,p_user_id => p_user_id
550 ,p_machine => p_machine
551 ,p_terminal => p_terminal
552 ,p_cascade_lock_level => 0
553 ,x_return_status => x_return_status
554 ,x_msg_count => x_msg_count
555 ,x_msg_data => x_msg_data
556 );
557 BSC_LOCKS_PVT.LOCK_OBJECT
558 (
559 p_object_type => 'OBJECTIVE'
560 ,p_lock_type => p_lock_type
561 ,p_query_time => p_query_time
562 ,p_program_id => p_program_id
563 ,p_user_id => p_user_id
564 ,p_machine => p_machine
565 ,p_terminal => p_terminal
566 ,p_cascade_lock_level => 0
567 ,x_return_status => x_return_status
568 ,x_msg_count => x_msg_count
569 ,x_msg_data => x_msg_data
570 );
571 BSC_LOCKS_PVT.LOCK_OBJECT
572 (
573 p_object_type => 'MEASURE'
574 ,p_lock_type => p_lock_type
575 ,p_query_time => p_query_time
576 ,p_program_id => p_program_id
577 ,p_user_id => p_user_id
578 ,p_machine => p_machine
579 ,p_terminal => p_terminal
580 ,p_cascade_lock_level => 0
581 ,x_return_status => x_return_status
582 ,x_msg_count => x_msg_count
583 ,x_msg_data => x_msg_data
584 );
585 END IF;
586 END IF;
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 x_return_status := FND_API.G_RET_STS_ERROR;
591 FND_MSG_PUB.Count_And_Get(
592 p_encoded => 'F'
593 ,p_count => x_msg_count
594 ,p_data => x_msg_data
595 );
596 RAISE;
597 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
599 FND_MSG_PUB.Count_And_Get(
600 p_encoded => 'F'
601 ,p_count => x_msg_count
602 ,p_data => x_msg_data
603 );
604 RAISE;
605 WHEN OTHERS THEN
606 FND_MSG_PUB.Add_Exc_Msg(
607 G_PKG_NAME,
608 l_api_name,
609 SQLERRM
610 );
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
612 FND_MSG_PUB.Count_And_Get(
613 p_encoded => 'F'
614 ,p_count => x_msg_count
615 ,p_data => x_msg_data
616 );
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END LOCK_OBJECT;
619
620
621 /*------------------------------------------------------------------------------------------
622 Function LOCK_OBJECT_WRITE
623 This function acquires a write (exclusive) lock on an Object
624 and returns the last_save_time value.
625 <parameters>
626 p_object_key: The primary key of the Object, usually the TO_CHAR value
627 of the Object ID. If the Object has composite keys,
628 the value to pass in will be a concatenation of
629 all the keys, separated by commas
630 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
631 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
632 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
633 "PERIODICITY", and "TABLE"
634 p_program_id: -100 = Data Loader UI
635 -101 = Data Loader Backend
636 -200 = Generate Database
637 -201 = Generate Documentation
638 -202 = Rename Interface Table
639 -203 = Generate Database Configuration
640 -300 = Administrator
641 -400 = Objective Designer
642 -500 = Builder
643 -600 = Performance Scorecard
644 -700 = System Upgrade
645 -800 = System Migration
646 p_user_id: Application User ID
647 p_machine: The Machine
648 p_terminal: The Terminal
649 -------------------------------------------------------------------------------------------*/
650 FUNCTION LOCK_OBJECT_WRITE(
651 p_object_key IN varchar2
652 ,p_object_type IN varchar2
653 ,p_program_id IN number
654 ,p_user_id IN number
655 ,p_machine IN varchar2
656 ,p_terminal IN varchar2
657 ,x_return_status OUT NOCOPY varchar2
658 ,x_msg_count OUT NOCOPY number
659 ,x_msg_data OUT NOCOPY varchar2
660 ) RETURN DATE IS
661
662 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT_WRITE';
663 l_object_key BSC_OBJECT_LOCKS.OBJECT_KEY%TYPE;
664 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
665 l_insert_flag BOOLEAN;
666 l_last_save_time DATE;
667 l_session_id NUMBER := USERENV('SESSIONID');
668
669 CURSOR c_locate_lock(
670 c_object_key VARCHAR2,
671 c_object_type VARCHAR2,
672 c_lock_type VARCHAR2
673 ) IS
674 SELECT LAST_SAVE_TIME
675 FROM BSC_OBJECT_LOCKS
676 WHERE OBJECT_KEY = c_object_key
677 AND OBJECT_TYPE = c_object_type
678 AND LOCK_TYPE = c_lock_type
679 FOR UPDATE NOWAIT;
680
681 BEGIN
682 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT_WRITE: '||p_object_key||' '||p_object_type);
683 x_return_status := FND_API.G_RET_STS_SUCCESS;
684 l_last_save_time := NULL;
685 l_insert_flag := TRUE;
686
687 -- Lock the object
688 l_last_save_time :=
689 BSC_LOCKS_PVT.LOCK_OBJECT_WRITE
690 (
691 p_object_key => p_object_key
692 ,p_object_type => p_object_type
693 ,x_insert_flag => l_insert_flag
694 ,x_return_status => x_return_status
695 ,x_msg_count => x_msg_count
696 ,x_msg_data => x_msg_data
697 );
698
699 IF (l_insert_flag) THEN
700 l_object_key := p_object_key;
701 l_object_type := p_object_type;
702 BEGIN
703 SAVEPOINT BSCLocksPvtLockObjectWrite;
704
705 -- Check for "ALL" entries
706 l_object_key := 'ALL';
707 OPEN c_locate_lock(l_object_key, l_object_type, 'W');
708 FETCH c_locate_lock INTO l_last_save_time;
709 CLOSE c_locate_lock;
710 l_object_type := 'ALL';
711 OPEN c_locate_lock(l_object_key, l_object_type, 'W');
712 FETCH c_locate_lock INTO l_last_save_time;
713 CLOSE c_locate_lock;
714
715 -- Insert new lock entry
716 BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
717 (
718 p_object_key => p_object_key
719 ,p_object_type => p_object_type
720 ,p_lock_type => 'W'
721 ,p_last_save_time => NULL
722 ,p_session_id => l_session_id
723 ,x_return_status => x_return_status
724 ,x_msg_count => x_msg_count
725 ,x_msg_data => x_msg_data
726 );
727 ROLLBACK TO BSCLocksPvtLockObjectWrite;
728 EXCEPTION
729 WHEN OTHERS THEN
730 ROLLBACK TO BSCLocksPvtLockObjectWrite;
731 BSC_LOCKS_PVT.RAISE_EXCEPTION
732 (
733 p_object_key => l_object_key
734 ,p_object_type => l_object_type
735 ,p_exception_type => 'L'
736 ,x_return_status => x_return_status
737 ,x_msg_count => x_msg_count
738 ,x_msg_data => x_msg_data
739 );
740 END;
741
742 l_last_save_time :=
743 BSC_LOCKS_PVT.LOCK_OBJECT_WRITE
744 (
745 p_object_key => p_object_key
746 ,p_object_type => p_object_type
747 ,p_program_id => p_program_id
748 ,p_user_id => p_user_id
749 ,p_machine => p_machine
750 ,p_terminal => p_terminal
751 ,x_return_status => x_return_status
752 ,x_msg_count => x_msg_count
753 ,x_msg_data => x_msg_data
754 );
755
756 -- Update the user info
757 ELSE
758 BSC_LOCKS_PVT.UPDATE_USER_INFO_AUTONOMOUS
759 (
760 p_object_key => p_object_key
761 ,p_object_type => p_object_type
762 ,p_user_type => 'L'
763 ,p_program_id => p_program_id
764 ,p_user_id => p_user_id
765 ,p_machine => p_machine
766 ,p_terminal => p_terminal
767 ,x_return_status => x_return_status
768 ,x_msg_count => x_msg_count
769 ,x_msg_data => x_msg_data
770 );
771 END IF;
772 RETURN l_last_save_time;
773
774 EXCEPTION
775 WHEN FND_API.G_EXC_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_ERROR;
777 FND_MSG_PUB.Count_And_Get(
778 p_encoded => 'F'
779 ,p_count => x_msg_count
780 ,p_data => x_msg_data
781 );
782 RAISE;
783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 FND_MSG_PUB.Count_And_Get(
786 p_encoded => 'F'
787 ,p_count => x_msg_count
788 ,p_data => x_msg_data
789 );
790 RAISE;
791 WHEN OTHERS THEN
792 FND_MSG_PUB.Add_Exc_Msg(
793 G_PKG_NAME,
794 l_api_name,
795 SQLERRM
796 );
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 FND_MSG_PUB.Count_And_Get(
799 p_encoded => 'F'
800 ,p_count => x_msg_count
801 ,p_data => x_msg_data
802 );
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 END LOCK_OBJECT_WRITE;
805
806
807 /*------------------------------------------------------------------------------------------
808 Function LOCK_OBJECT_WRITE
809 This function acquires a write (exclusive) lock on an Object
810 and returns the last_save_time value.
811 <parameters>
812 p_object_key: The primary key of the Object, usually the TO_CHAR value
813 of the Object ID. If the Object has composite keys,
814 the value to pass in will be a concatenation of
815 all the keys, separated by commas
816 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
817 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
818 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
819 "PERIODICITY", and "TABLE"
820 x_insert_flag: True if the lock entry is missing in the lock table
821 -------------------------------------------------------------------------------------------*/
822 Function LOCK_OBJECT_WRITE(
823 p_object_key IN varchar2
824 ,p_object_type IN varchar2
825 ,x_insert_flag OUT NOCOPY boolean
826 ,x_return_status OUT NOCOPY varchar2
827 ,x_msg_count OUT NOCOPY number
828 ,x_msg_data OUT NOCOPY varchar2
829 ) return DATE IS
830
831 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT_WRITE';
832 l_count NUMBER;
833 l_last_save_time DATE;
834 l_lock_type BSC_OBJECT_LOCKS.LOCK_TYPE%TYPE;
835 l_user_type BSC_OBJECT_LOCK_USERS.USER_TYPE%TYPE;
836
837 CURSOR c_get_object(
838 c_object_key VARCHAR2,
839 c_object_type VARCHAR2
840 ) IS
841 SELECT LOCK_TYPE
842 FROM BSC_OBJECT_LOCKS
843 WHERE OBJECT_KEY = c_object_key
844 AND OBJECT_TYPE = c_object_type;
845
846 CURSOR c_lock_object(
847 c_object_key VARCHAR2,
848 c_object_type VARCHAR2
849 ) IS
850 SELECT LAST_SAVE_TIME
851 FROM BSC_OBJECT_LOCKS
852 WHERE OBJECT_KEY = c_object_key
853 AND OBJECT_TYPE = c_object_type
854 ORDER BY LOCK_TYPE
855 FOR UPDATE NOWAIT;
856
857 CURSOR c_locate_lock(
858 c_object_key VARCHAR2,
859 c_object_type VARCHAR2,
860 c_lock_type VARCHAR2
861 ) IS
862 SELECT LAST_SAVE_TIME
863 FROM BSC_OBJECT_LOCKS
864 WHERE OBJECT_KEY = c_object_key
865 AND OBJECT_TYPE = c_object_type
866 AND LOCK_TYPE = c_lock_type
867 FOR UPDATE NOWAIT;
868
869 BEGIN
870 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT_WRITE: '||p_object_key||' '||p_object_type);
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872
873 l_last_save_time := NULL;
874 x_insert_flag := TRUE;
875 BEGIN
876 FOR clock IN c_lock_object(p_object_key, p_object_type) LOOP
877 l_last_save_time := clock.LAST_SAVE_TIME;
878 x_insert_flag := FALSE;
879 END LOOP;
880 EXCEPTION
881 WHEN OTHERS THEN
882 l_user_type := 'L';
883 BEGIN
884 FOR cobj IN c_get_object(p_object_key, p_object_type) LOOP
885 l_lock_type := cobj.LOCK_TYPE;
886 l_user_type := REPLACE(REPLACE(l_lock_type,'R','L'),'W','L');
887 OPEN c_locate_lock(p_object_key, p_object_type, l_lock_type);
888 FETCH c_locate_lock INTO l_last_save_time;
889 CLOSE c_locate_lock;
890 END LOOP;
891 EXCEPTION
892 WHEN OTHERS THEN
893 BSC_LOCKS_PVT.RAISE_EXCEPTION
894 (
895 p_object_key => p_object_key
896 ,p_object_type => p_object_type
897 ,p_exception_type => l_user_type
898 ,x_return_status => x_return_status
899 ,x_msg_count => x_msg_count
900 ,x_msg_data => x_msg_data
901 );
902 END;
903 END;
904 RETURN l_last_save_time;
905
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR THEN
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 FND_MSG_PUB.Count_And_Get(
910 p_encoded => 'F'
911 ,p_count => x_msg_count
912 ,p_data => x_msg_data
913 );
914 RAISE;
915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 FND_MSG_PUB.Count_And_Get(
918 p_encoded => 'F'
919 ,p_count => x_msg_count
920 ,p_data => x_msg_data
921 );
922 RAISE;
923 WHEN OTHERS THEN
924 FND_MSG_PUB.Add_Exc_Msg(
925 G_PKG_NAME,
926 l_api_name,
927 SQLERRM
928 );
929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
930 FND_MSG_PUB.Count_And_Get(
931 p_encoded => 'F'
932 ,p_count => x_msg_count
933 ,p_data => x_msg_data
934 );
935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
936 END LOCK_OBJECT_WRITE;
937
938
939 /*------------------------------------------------------------------------------------------
940 Function LOCK_OBJECT_READ
941 This procedure acquires puts a read (shared) lock on the Object.
942 <parameters>
943 p_object_key: The primary key of the Object, usually the TO_CHAR value
944 of the Object ID. If the Object has composite keys,
945 the value to pass in will be a concatenation of
946 all the keys, separated by commas
947 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
948 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
949 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
950 "PERIODICITY", and "TABLE"
951 p_program_id: -100 = Data Loader UI
952 -101 = Data Loader Backend
953 -200 = Generate Database
954 -201 = Generate Documentation
955 -202 = Rename Interface Table
956 -203 = Generate Database Configuration
957 -300 = Administrator
958 -400 = Objective Designer
959 -500 = Builder
960 -600 = Performance Scorecard
961 -700 = System Upgrade
962 -800 = System Migration
963 p_user_id: Application User ID
964 p_machine: The Machine
965 p_terminal: The Terminal
966 -------------------------------------------------------------------------------------------*/
967 Function LOCK_OBJECT_READ(
968 p_object_key IN varchar2
969 ,p_object_type IN varchar2
970 ,p_program_id IN number
971 ,p_user_id IN number
972 ,p_machine IN varchar2
973 ,p_terminal IN varchar2
974 ,x_return_status OUT NOCOPY varchar2
975 ,x_msg_count OUT NOCOPY number
976 ,x_msg_data OUT NOCOPY varchar2
977 ) return DATE IS
978
979 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT_READ';
980 l_count NUMBER;
981 l_insert_flag BOOLEAN;
982 l_last_save_time DATE;
983 l_object_key BSC_OBJECT_LOCKS.OBJECT_KEY%TYPE;
984 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
985 l_lock_type BSC_OBJECT_LOCKS.LOCK_TYPE%TYPE;
986 l_user_type BSC_OBJECT_LOCK_USERS.USER_TYPE%TYPE;
987 l_session_id NUMBER := USERENV('SESSIONID');
988
989 CURSOR c_get_object(
990 c_object_key VARCHAR2,
991 c_object_type VARCHAR2
992 ) IS
993 SELECT OBJECT_KEY, OBJECT_TYPE, LOCK_TYPE
994 FROM BSC_OBJECT_LOCKS
995 WHERE OBJECT_KEY = c_object_key
996 AND OBJECT_TYPE = c_object_type
997 AND LENGTH(LOCK_TYPE) > 1
998 AND SUBSTR(LOCK_TYPE,1,1) = 'R'
999 ORDER BY LOCK_TYPE;
1000
1001 CURSOR c_locate_lock(
1002 c_object_key VARCHAR2,
1003 c_object_type VARCHAR2,
1004 c_lock_type VARCHAR2
1005 ) IS
1006 SELECT LAST_SAVE_TIME
1007 FROM BSC_OBJECT_LOCKS
1008 WHERE OBJECT_KEY = c_object_key
1009 AND OBJECT_TYPE = c_object_type
1010 AND LOCK_TYPE = c_lock_type
1011 FOR UPDATE NOWAIT;
1012
1013 CURSOR c_get_last_save_time(
1014 c_object_key VARCHAR2,
1015 c_object_type VARCHAR2
1016 ) IS
1017 SELECT LAST_SAVE_TIME
1018 FROM BSC_OBJECT_LOCKS
1019 WHERE OBJECT_KEY = c_object_key
1020 AND OBJECT_TYPE = c_object_type
1021 AND LOCK_TYPE = 'W';
1022
1023 BEGIN
1024 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT_READ: '||p_object_key||' '||p_object_type);
1025 x_return_status := FND_API.G_RET_STS_SUCCESS;
1026 l_last_save_time := NULL;
1027 l_insert_flag := TRUE;
1028 l_count := 0;
1029
1030 FOR cobj IN c_get_object(p_object_key, p_object_type) LOOP
1031 l_object_key := cobj.OBJECT_KEY;
1032 l_object_type := cobj.OBJECT_TYPE;
1033 l_lock_type := cobj.LOCK_TYPE;
1034 l_count := TO_NUMBER(SUBSTR(l_lock_type,2));
1035 BEGIN
1036 OPEN c_locate_lock(l_object_key, l_object_type, l_lock_type);
1037 FETCH c_locate_lock INTO l_last_save_time;
1038 CLOSE c_locate_lock;
1039 l_insert_flag := FALSE;
1040 EXIT;
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 NULL;
1044 END;
1045 END LOOP;
1046
1047 -- Check for write lock
1048 IF (l_insert_flag) THEN
1049 l_object_key := p_object_key;
1050 l_object_type := p_object_type;
1051 l_count := l_count + 1;
1052 BEGIN
1053 SAVEPOINT BSCLocksPvtLockObjectRead;
1054 OPEN c_locate_lock(l_object_key, l_object_type, 'W');
1055 FETCH c_locate_lock INTO l_last_save_time;
1056 l_insert_flag := (c_locate_lock%NOTFOUND);
1057 CLOSE c_locate_lock;
1058
1059 -- Check for "ALL" entries
1060 IF (l_insert_flag) THEN
1061 l_object_key := 'ALL';
1062 OPEN c_locate_lock(l_object_key, l_object_type, 'W');
1063 FETCH c_locate_lock INTO l_last_save_time;
1064 CLOSE c_locate_lock;
1065 l_object_type := 'ALL';
1066 OPEN c_locate_lock(l_object_key, l_object_type, 'W');
1067 FETCH c_locate_lock INTO l_last_save_time;
1068 CLOSE c_locate_lock;
1069
1070 -- Insert write lock entry
1071 BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
1072 (
1073 p_object_key => p_object_key
1074 ,p_object_type => p_object_type
1075 ,p_lock_type => 'W'
1076 ,p_last_save_time => NULL
1077 ,p_session_id => l_session_id
1078 ,x_return_status => x_return_status
1079 ,x_msg_count => x_msg_count
1080 ,x_msg_data => x_msg_data
1081 );
1082 END IF;
1083
1084 -- Insert read lock entry
1085 BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
1086 (
1087 p_object_key => p_object_key
1088 ,p_object_type => p_object_type
1089 ,p_lock_type => 'R'||TO_CHAR(l_count)
1090 ,p_last_save_time => NULL
1091 ,p_session_id => l_session_id
1092 ,x_return_status => x_return_status
1093 ,x_msg_count => x_msg_count
1094 ,x_msg_data => x_msg_data
1095 );
1096 ROLLBACK TO BSCLocksPvtLockObjectRead;
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 ROLLBACK TO BSCLocksPvtLockObjectRead;
1100 BSC_LOCKS_PVT.RAISE_EXCEPTION
1101 (
1102 p_object_key => l_object_key
1103 ,p_object_type => l_object_type
1104 ,p_exception_type => 'L'
1105 ,x_return_status => x_return_status
1106 ,x_msg_count => x_msg_count
1107 ,x_msg_data => x_msg_data
1108 );
1109 END;
1110
1111 -- Acquire read lock
1112 BEGIN
1113 OPEN c_locate_lock(p_object_key, p_object_type, 'R'||TO_CHAR(l_count));
1114 FETCH c_locate_lock INTO l_last_save_time;
1115 CLOSE c_locate_lock;
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 BSC_LOCKS_PVT.RAISE_EXCEPTION
1119 (
1120 p_object_key => p_object_key
1121 ,p_object_type => p_object_type
1122 ,p_exception_type => 'L'||TO_CHAR(l_count)
1123 ,x_return_status => x_return_status
1124 ,x_msg_count => x_msg_count
1125 ,x_msg_data => x_msg_data
1126 );
1127 END;
1128 END IF;
1129
1130 -- Update the user info
1131 BSC_LOCKS_PVT.UPDATE_USER_INFO_AUTONOMOUS
1132 (
1133 p_object_key => p_object_key
1134 ,p_object_type => p_object_type
1135 ,p_user_type => 'L'||TO_CHAR(l_count)
1136 ,p_program_id => p_program_id
1137 ,p_user_id => p_user_id
1138 ,p_machine => p_machine
1139 ,p_terminal => p_terminal
1140 ,x_return_status => x_return_status
1141 ,x_msg_count => x_msg_count
1142 ,x_msg_data => x_msg_data
1143 );
1144
1145 -- Get the last save time
1146 OPEN c_get_last_save_time(p_object_key, p_object_type);
1147 FETCH c_get_last_save_time INTO l_last_save_time;
1148 CLOSE c_get_last_save_time;
1149 RETURN l_last_save_time;
1150
1151 EXCEPTION
1152 WHEN FND_API.G_EXC_ERROR THEN
1153 x_return_status := FND_API.G_RET_STS_ERROR;
1154 FND_MSG_PUB.Count_And_Get(
1155 p_encoded => 'F'
1156 ,p_count => x_msg_count
1157 ,p_data => x_msg_data
1158 );
1159 RAISE;
1160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1162 FND_MSG_PUB.Count_And_Get(
1163 p_encoded => 'F'
1164 ,p_count => x_msg_count
1165 ,p_data => x_msg_data
1166 );
1167 RAISE;
1168 WHEN OTHERS THEN
1169 FND_MSG_PUB.Add_Exc_Msg(
1170 G_PKG_NAME,
1171 l_api_name,
1172 SQLERRM
1173 );
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1175 FND_MSG_PUB.Count_And_Get(
1176 p_encoded => 'F'
1177 ,p_count => x_msg_count
1178 ,p_data => x_msg_data
1179 );
1180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1181 END LOCK_OBJECT_READ;
1182
1183
1184 /*------------------------------------------------------------------------------------------
1185 Procedure LOCK_OBJECT_ALL
1186 This procedure locks the locking tables
1187 -------------------------------------------------------------------------------------------*/
1188 Procedure LOCK_OBJECT_ALL(
1189 x_return_status OUT NOCOPY varchar2
1190 ,x_msg_count OUT NOCOPY number
1191 ,x_msg_data OUT NOCOPY varchar2
1192 ) IS
1193
1194 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_OBJECT_ALL';
1195 l_object_key BSC_OBJECT_LOCKS.OBJECT_KEY%TYPE;
1196 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
1197 l_lock_type BSC_OBJECT_LOCKS.LOCK_TYPE%TYPE;
1198 l_user_type BSC_OBJECT_LOCK_USERS.USER_TYPE%TYPE;
1199 l_last_save_time BSC_OBJECT_LOCKS.LAST_SAVE_TIME%TYPE;
1200 l_exception_flag BOOLEAN;
1201
1202 CURSOR c_lock_object IS
1203 SELECT LAST_SAVE_TIME
1204 FROM BSC_OBJECT_LOCKS
1205 FOR UPDATE NOWAIT;
1206
1207 CURSOR c_get_object IS
1208 SELECT OBJECT_TYPE, OBJECT_KEY, LOCK_TYPE
1209 FROM BSC_OBJECT_LOCKS;
1210
1211 CURSOR c_locate_lock(
1212 c_object_key VARCHAR2,
1213 c_object_type VARCHAR2,
1214 c_lock_type VARCHAR2
1215 ) IS
1216 SELECT LAST_SAVE_TIME
1217 FROM BSC_OBJECT_LOCKS
1218 WHERE OBJECT_KEY = c_object_key
1219 AND OBJECT_TYPE = c_object_type
1220 AND LOCK_TYPE = c_lock_type
1221 FOR UPDATE NOWAIT;
1222
1223 BEGIN
1224 --DBMS_OUTPUT.PUT_LINE('LOCK_OBJECT_ALL');
1225 x_return_status := FND_API.G_RET_STS_SUCCESS;
1226 l_exception_flag := FALSE;
1227
1228 -- Lock the user entry for the top level object
1229 BEGIN
1230 LOCK TABLE BSC_OBJECT_LOCK_USERS IN EXCLUSIVE MODE NOWAIT;
1231 LOCK TABLE BSC_OBJECT_LOCKS IN EXCLUSIVE MODE NOWAIT;
1232 --FOR clock IN c_lock_object LOOP
1233 -- l_last_save_time := clock.LAST_SAVE_TIME;
1234 --END LOOP;
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 l_exception_flag := TRUE;
1238 FOR cobj IN c_get_object LOOP
1239 l_object_type := cobj.OBJECT_TYPE;
1240 l_object_key := cobj.OBJECT_KEY;
1241 l_lock_type := cobj.LOCK_TYPE;
1242 l_user_type := REPLACE(REPLACE(l_lock_type,'R','L'),'W','L');
1243 --DBMS_OUTPUT.PUT_LINE('objectType='||l_object_type||', objectKey='||l_object_key||', lockType='||l_lock_type||', userType='||l_user_type);
1244 BEGIN
1245 OPEN c_locate_lock(l_object_key, l_object_type, l_lock_type);
1246 FETCH c_locate_lock INTO l_last_save_time;
1247 CLOSE c_locate_lock;
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 BSC_LOCKS_PVT.RAISE_EXCEPTION
1251 (
1252 p_object_key => l_object_key
1253 ,p_object_type => l_object_type
1254 ,p_exception_type => l_user_type
1255 ,x_return_status => x_return_status
1256 ,x_msg_count => x_msg_count
1257 ,x_msg_data => x_msg_data
1258 );
1259 END;
1260 END LOOP;
1261 END;
1262
1263 -- Couldn't find the entry being locked, try again
1264 IF (l_exception_flag) THEN
1265 BSC_LOCKS_PVT.LOCK_OBJECT_ALL
1266 (
1267 x_return_status => x_return_status
1268 ,x_msg_count => x_msg_count
1269 ,x_msg_data => x_msg_data
1270 );
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN FND_API.G_EXC_ERROR THEN
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276 FND_MSG_PUB.Count_And_Get(
1277 p_encoded => 'F'
1278 ,p_count => x_msg_count
1279 ,p_data => x_msg_data
1280 );
1281 RAISE;
1282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1284 FND_MSG_PUB.Count_And_Get(
1285 p_encoded => 'F'
1286 ,p_count => x_msg_count
1287 ,p_data => x_msg_data
1288 );
1289 RAISE;
1290 WHEN OTHERS THEN
1291 FND_MSG_PUB.Add_Exc_Msg(
1292 G_PKG_NAME,
1293 l_api_name,
1294 SQLERRM
1295 );
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 FND_MSG_PUB.Count_And_Get(
1298 p_encoded => 'F'
1299 ,p_count => x_msg_count
1300 ,p_data => x_msg_data
1301 );
1302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1303 END LOCK_OBJECT_ALL;
1304
1305
1306 /*------------------------------------------------------------------------------------------
1307 Procedure LOCK_USER
1308 This procedure puts a database lock on the corresponding row in the
1309 lock user table. If the row does not exist, a new row will be inserted
1310 to the lock user table. If someone else already locked the object,
1311 an exception will be raised.
1312 <parameters>
1313 p_object_key: The primary key of the Object, usually the TO_CHAR value
1314 of the Object ID. If the Object has composite keys,
1315 the value to pass in will be a concatenation of
1316 all the keys, separated by commas
1317 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1318 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1319 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1320 "PERIODICITY", and "TABLE"
1321 p_user_type: "L" = Lock, "M" = Modify
1322 p_program_id: -100 = Data Loader UI
1323 -101 = Data Loader Backend
1324 -200 = Generate Database
1325 -201 = Generate Documentation
1326 -202 = Rename Interface Table
1327 -203 = Generate Database Configuration
1328 -300 = Administrator
1329 -400 = Objective Designer
1330 -500 = Builder
1331 -600 = Performance Scorecard
1332 -700 = System Upgrade
1333 -800 = System Migration
1334 p_user_id: Application User ID
1335 p_machine: The Machine
1336 p_terminal: The Terminal
1337 -------------------------------------------------------------------------------------------*/
1338 Procedure LOCK_USER(
1339 p_object_key IN varchar2
1340 ,p_object_type IN varchar2
1341 ,p_user_type IN varchar2
1342 ,p_program_id IN number
1343 ,p_user_id IN number
1344 ,p_machine IN varchar2
1345 ,p_terminal IN varchar2
1346 ,x_return_status OUT NOCOPY varchar2
1347 ,x_msg_count OUT NOCOPY number
1348 ,x_msg_data OUT NOCOPY varchar2
1349 ) IS
1350
1351 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_USER';
1352 l_user_id BSC_OBJECT_LOCK_USERS.USER_ID%TYPE;
1353 --l_insert_flag BOOLEAN;
1354
1355 CURSOR c_lock_user(c_object_key VARCHAR2, c_object_type VARCHAR2, c_user_type VARCHAR2) IS
1356 SELECT USER_ID FROM BSC_OBJECT_LOCK_USERS
1357 WHERE OBJECT_KEY = c_object_key
1358 AND OBJECT_TYPE = c_object_type
1359 AND USER_TYPE = c_user_type
1360 FOR UPDATE NOWAIT;
1361
1362 BEGIN
1363 --DBMS_OUTPUT.PUT_LINE('LOCK_USER: '||p_object_key||' '||p_object_type||' '||p_user_type);
1364 x_return_status := FND_API.G_RET_STS_SUCCESS;
1365
1366 l_user_id := NULL;
1367 BEGIN
1368 OPEN c_lock_user(p_object_key, p_object_type, p_user_type);
1369 FETCH c_lock_user INTO l_user_id;
1370 --l_insert_flag := (c_lock_user%NOTFOUND);
1371 CLOSE c_lock_user;
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 BSC_LOCKS_PVT.RAISE_EXCEPTION
1375 (
1376 p_object_key => p_object_key
1377 ,p_object_type => p_object_type
1378 ,p_exception_type => 'L'
1379 ,x_return_status => x_return_status
1380 ,x_msg_count => x_msg_count
1381 ,x_msg_data => x_msg_data
1382 );
1383 END;
1384
1385 -- Commended out because of compatibility issues with "ALL" entries
1386 --IF (l_insert_Flag) THEN
1387 -- BEGIN
1388 -- BSC_LOCKS_PVT.INSERT_LOCK_INFO_AUTONOMOUS
1389 -- (
1390 -- p_object_key => p_object_key
1391 -- ,p_object_type => p_object_type
1392 -- ,p_lock_type => 'W'
1393 -- ,p_last_save_time => NULL
1394 -- ,p_session_id => USERENV('SESSIONID')
1395 -- ,x_return_status => x_return_status
1396 -- ,x_msg_count => x_msg_count
1397 -- ,x_msg_data => x_msg_data
1398 -- );
1399 -- EXCEPTION
1400 -- WHEN OTHERS THEN
1401 -- NULL;
1402 -- END;
1403 -- BSC_LOCKS_PVT.INSERT_USER_INFO_AUTONOMOUS
1404 -- (
1405 -- p_object_key => p_object_key
1406 -- ,p_object_type => p_object_type
1407 -- ,p_user_type => p_user_type
1408 -- ,p_program_id => p_program_id
1409 -- ,p_user_id => p_user_id
1410 -- ,p_machine => p_machine
1411 -- ,p_terminal => p_terminal
1412 -- ,x_return_status => x_return_status
1413 -- ,x_msg_count => x_msg_count
1414 -- ,x_msg_data => x_msg_data
1415 -- );
1416 -- BSC_LOCKS_PVT.LOCK_USER
1417 -- (
1418 -- p_object_key => p_object_key
1419 -- ,p_object_type => p_object_type
1420 -- ,p_user_type => p_user_type
1421 -- ,p_program_id => p_program_id
1422 -- ,p_user_id => p_user_id
1423 -- ,p_machine => p_machine
1424 -- ,p_terminal => p_terminal
1425 -- ,x_return_status => x_return_status
1426 -- ,x_msg_count => x_msg_count
1427 -- ,x_msg_data => x_msg_data
1428 -- );
1429 --END IF;
1430
1431 EXCEPTION
1432 WHEN FND_API.G_EXC_ERROR THEN
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 FND_MSG_PUB.Count_And_Get(
1435 p_encoded => 'F'
1436 ,p_count => x_msg_count
1437 ,p_data => x_msg_data
1438 );
1439 RAISE;
1440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 FND_MSG_PUB.Count_And_Get(
1443 p_encoded => 'F'
1444 ,p_count => x_msg_count
1445 ,p_data => x_msg_data
1446 );
1447 RAISE;
1448 WHEN OTHERS THEN
1449 FND_MSG_PUB.Add_Exc_Msg(
1450 G_PKG_NAME,
1451 l_api_name,
1452 SQLERRM
1453 );
1454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1455 FND_MSG_PUB.Count_And_Get(
1456 p_encoded => 'F'
1457 ,p_count => x_msg_count
1458 ,p_data => x_msg_data
1459 );
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 END LOCK_USER;
1462
1463
1464 /*------------------------------------------------------------------------------------------
1465 Procedure GET_CHILD_OBJECTS
1466 This procedure retrieves the list of child objects down the hierarchy
1467 <parameters>
1468 p_object_key: The primary key of the Object, usually the TO_CHAR value
1469 of the Object ID. If the Object has composite keys,
1470 the value to pass in will be a concatenation of
1471 all the keys, separated by commas
1472 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1473 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1474 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1475 "PERIODICITY", and "TABLE"
1476 p_cascade_lock_level: Number of level for cascade locks
1477 Default is -1 which means enable cascade locking
1478 all the way to the lowest level
1479 p_lowest_level_type: The type of the lowest level object
1480 x_child_object_keys: Table of child object keys
1481 x_child_object_types: Table of child object types
1482 x_child_object_count: Total number of child objects (Should pass in 0 initially)
1483 -------------------------------------------------------------------------------------------*/
1484 Procedure GET_CHILD_OBJECTS(
1485 p_object_key IN varchar2
1486 ,p_object_type IN varchar2
1487 ,p_cascade_lock_level IN number
1488 ,p_lowest_level_type IN varchar2
1489 ,x_child_object_keys IN OUT NOCOPY t_array_object_key
1490 ,x_child_object_types IN OUT NOCOPY t_array_object_type
1491 ,x_child_object_count IN OUT NOCOPY number
1492 ,x_return_status OUT NOCOPY varchar2
1493 ,x_msg_count OUT NOCOPY number
1494 ,x_msg_data OUT NOCOPY varchar2
1495 ) IS
1496
1497 l_api_name CONSTANT VARCHAR2(30) := 'GET_CHILD_OBJECTS';
1498 l_object_key1 NUMBER;
1499 l_object_key2 NUMBER;
1500 l_initial_count NUMBER;
1501 l_cascade_lock_level NUMBER;
1502 l_measure_cols dbms_sql.varchar2_table;
1503
1504 CURSOR c_get_child_scorecard(c_tab_id NUMBER) IS
1505 SELECT TO_CHAR(INDICATOR) OBJECT_KEY,
1506 'OBJECTIVE' OBJECT_TYPE
1507 FROM BSC_TAB_INDICATORS
1508 WHERE TAB_ID = c_tab_id
1509 UNION
1510 SELECT TO_CHAR(c_tab_id)||','||TO_CHAR(TAB_VIEW_ID) OBJECT_KEY,
1511 'CUSTOM_VIEW' OBJECT_TYPE
1512 FROM BSC_TAB_VIEWS_B
1513 WHERE TAB_ID = c_tab_id;
1514
1515 CURSOR c_get_child_objective(c_indicator NUMBER) IS
1516 SELECT TO_CHAR(DIM_GROUP_ID) OBJECT_KEY,
1517 'DIMENSION' OBJECT_TYPE
1518 FROM BSC_KPI_DIM_GROUPS
1519 WHERE INDICATOR = c_indicator
1520 UNION
1521 SELECT TO_CHAR(DATASET_ID) OBJECT_KEY,
1522 'MEASURE' OBJECT_TYPE
1523 FROM BSC_DB_DATASET_DIM_SETS_V
1524 WHERE INDICATOR = c_indicator;
1525
1526 CURSOR c_get_child_dimension(c_dim_group_id NUMBER) IS
1527 SELECT TO_CHAR(DIM_LEVEL_ID) OBJECT_KEY,
1528 'DIMENSION_OBJECT' OBJECT_TYPE
1529 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
1530 WHERE DIM_GROUP_ID = c_dim_group_id;
1531
1532 CURSOR c_get_child_measure(c_dataset_id NUMBER) IS
1533 SELECT TO_CHAR(MEASURE_ID1) OBJECT_KEY,
1534 'DATA_COLUMN' OBJECT_TYPE
1535 FROM BSC_SYS_DATASETS_B
1536 WHERE DATASET_ID = c_dataset_id
1537 UNION
1538 SELECT TO_CHAR(MEASURE_ID2) OBJECT_KEY,
1539 'DATA_COLUMN' OBJECT_TYPE
1540 FROM BSC_SYS_DATASETS_B
1541 WHERE DATASET_ID = c_dataset_id
1542 AND MEASURE_ID2 IS NOT NULL;
1543
1544 CURSOR c_get_child_custom_view(c_tab_id NUMBER, c_tab_view_id NUMBER) IS
1545 SELECT TO_CHAR(LINK_ID) OBJECT_KEY,
1546 DECODE(LABEL_TYPE,
1547 2, 'LAUNCHPAD',
1548 3, 'MEASURE',
1549 4, 'OBJECTIVE') OBJECT_TYPE
1550 FROM BSC_TAB_VIEW_LABELS_B
1551 WHERE TAB_ID = c_tab_id
1552 AND TAB_VIEW_ID = c_tab_view_id
1553 AND LABEL_TYPE IN (2,3,4)
1554 AND LINK_ID IS NOT NULL;
1555
1556 CURSOR c_get_measure_col(c_measure_id NUMBER) IS
1557 SELECT MEASURE_COL, SOURCE
1558 FROM BSC_SYS_MEASURES
1559 WHERE MEASURE_ID = c_measure_id;
1560
1561 CURSOR c_get_measure_id(c_measure_col VARCHAR2, c_source VARCHAR2) IS
1562 SELECT TO_CHAR(MEASURE_ID) OBJECT_KEY,
1563 'DATA_COLUMN' OBJECT_TYPE
1564 FROM BSC_SYS_MEASURES
1565 WHERE MEASURE_COL = c_measure_col
1566 AND SOURCE = c_source;
1567
1568 BEGIN
1569 --DBMS_OUTPUT.PUT_LINE('GET_CHILD_OBJECTS: '||p_object_key||' '||p_object_type);
1570 --DBMS_OUTPUT.PUT_LINE('p_cascade_lock_level='||TO_CHAR(p_cascade_lock_level));
1571 --DBMS_OUTPUT.PUT_LINE('p_lowest_level_type='||p_lowest_level_type);
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573 l_initial_count := x_child_object_count;
1574 l_cascade_lock_level := p_cascade_lock_level;
1575
1576 IF ((p_lowest_level_type IS NULL OR p_object_type <> p_lowest_level_type) AND l_cascade_lock_level <> 0) THEN
1577 IF (UPPER(p_object_type) = 'SCORECARD') THEN
1578 l_object_key1 := TO_NUMBER(p_object_key);
1579 FOR cchild IN c_get_child_scorecard(l_object_key1) LOOP
1580 x_child_object_count := x_child_object_count + 1;
1581 x_child_object_keys(x_child_object_count) := cchild.OBJECT_KEY;
1582 x_child_object_types(x_child_object_count) := cchild.OBJECT_TYPE;
1583 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_KEY);
1584 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_TYPE);
1585 END LOOP;
1586
1587 ELSIF (UPPER(p_object_type) = 'OBJECTIVE') THEN
1588 l_object_key1 := TO_NUMBER(p_object_key);
1589 FOR cchild IN c_get_child_objective(l_object_key1) LOOP
1590 x_child_object_count := x_child_object_count + 1;
1591 x_child_object_keys(x_child_object_count) := cchild.OBJECT_KEY;
1592 x_child_object_types(x_child_object_count) := cchild.OBJECT_TYPE;
1593 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_KEY);
1594 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_TYPE);
1595 END LOOP;
1596
1597 ELSIF (UPPER(p_object_type) = 'DIMENSION') THEN
1598 l_object_key1 := TO_NUMBER(p_object_key);
1599 FOR cchild IN c_get_child_dimension(l_object_key1) LOOP
1600 x_child_object_count := x_child_object_count + 1;
1601 x_child_object_keys(x_child_object_count) := cchild.OBJECT_KEY;
1602 x_child_object_types(x_child_object_count) := cchild.OBJECT_TYPE;
1603 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_KEY);
1604 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_TYPE);
1605 END LOOP;
1606
1607 ELSIF (UPPER(p_object_type) = 'MEASURE') THEN
1608 l_object_key1 := TO_NUMBER(p_object_key);
1609 FOR cchild IN c_get_child_measure(l_object_key1) LOOP
1610 x_child_object_count := x_child_object_count + 1;
1611 x_child_object_keys(x_child_object_count) := cchild.OBJECT_KEY;
1612 x_child_object_types(x_child_object_count) := cchild.OBJECT_TYPE;
1613 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_KEY);
1614 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_TYPE);
1615 FOR c_measure_col IN c_get_measure_col(TO_NUMBER(cchild.OBJECT_KEY)) LOOP
1616 --DBMS_OUTPUT.PUT_LINE('PARENT COL = '||c_measure_col.MEASURE_COL);
1617 l_measure_cols := BSC_DBGEN_UTILS.get_measure_list(c_measure_col.MEASURE_COL);
1618 --DBMS_OUTPUT.PUT_LINE('MEASURE COL COUNT = '||TO_CHAR(l_measure_cols.count));
1619 IF (l_measure_cols.count > 0 AND l_measure_cols(l_measure_cols.first) <> c_measure_col.MEASURE_COL) THEN
1620 FOR i IN l_measure_cols.first..l_measure_cols.last LOOP
1621 --DBMS_OUTPUT.PUT_LINE('CHILD COL('||TO_CHAR(i)||') = '||l_measure_cols(i));
1622 FOR c_measure_id IN c_get_measure_id(l_measure_cols(i), c_measure_col.SOURCE) LOOP
1623 x_child_object_count := x_child_object_count + 1;
1624 x_child_object_keys(x_child_object_count) := c_measure_id.OBJECT_KEY;
1625 x_child_object_types(x_child_object_count) := c_measure_id.OBJECT_TYPE;
1626 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||c_measure_id.OBJECT_KEY);
1627 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||c_measure_id.OBJECT_TYPE);
1628 END LOOP;
1629 END LOOP;
1630 END IF;
1631 END LOOP;
1632 END LOOP;
1633
1634 ELSIF (UPPER(p_object_type) = 'CUSTOM_VIEW') THEN
1635 l_object_key1 := SUBSTR(p_object_key, 1, INSTR(p_object_key, ',')-1);
1636 l_object_key2 := SUBSTR(p_object_key, INSTR(p_object_key, ',')+1);
1637 --DBMS_OUTPUT.PUT_LINE('l_object_key1 = '||l_object_key1);
1638 --DBMS_OUTPUT.PUT_LINE('l_object_key2 = '||l_object_key2);
1639 FOR cchild IN c_get_child_custom_view(l_object_key1, l_object_key2) LOOP
1640 x_child_object_count := x_child_object_count + 1;
1641 x_child_object_keys(x_child_object_count) := cchild.OBJECT_KEY;
1642 x_child_object_types(x_child_object_count) := cchild.OBJECT_TYPE;
1643 --DBMS_OUTPUT.PUT_LINE('x_child_object_key('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_KEY);
1644 --DBMS_OUTPUT.PUT_LINE('x_child_object_type('||TO_CHAR(x_child_object_count)||') = '||cchild.OBJECT_TYPE);
1645 END LOOP;
1646 END IF;
1647
1648 IF (l_cascade_lock_level > 0) THEN
1649 l_cascade_lock_level := l_cascade_lock_level - 1;
1650 END IF;
1651 FOR i IN (l_initial_count+1)..x_child_object_count LOOP
1652 BSC_LOCKS_PVT.GET_CHILD_OBJECTS
1653 (
1654 p_object_key => x_child_object_keys(i)
1655 ,p_object_type => x_child_object_types(i)
1656 ,p_cascade_lock_level => l_cascade_lock_level
1657 ,p_lowest_level_type => p_lowest_level_type
1658 ,x_child_object_keys => x_child_object_keys
1659 ,x_child_object_types => x_child_object_types
1660 ,x_child_object_count => x_child_object_count
1661 ,x_return_status => x_return_status
1662 ,x_msg_count => x_msg_count
1663 ,x_msg_data => x_msg_data
1664 );
1665 END LOOP;
1666 END IF;
1667
1668 EXCEPTION
1669 WHEN FND_API.G_EXC_ERROR THEN
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 FND_MSG_PUB.Count_And_Get(
1672 p_encoded => 'F'
1673 ,p_count => x_msg_count
1674 ,p_data => x_msg_data
1675 );
1676 RAISE;
1677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1679 FND_MSG_PUB.Count_And_Get(
1680 p_encoded => 'F'
1681 ,p_count => x_msg_count
1682 ,p_data => x_msg_data
1683 );
1684 RAISE;
1685 WHEN OTHERS THEN
1686 FND_MSG_PUB.Add_Exc_Msg(
1687 G_PKG_NAME,
1688 l_api_name,
1689 SQLERRM
1690 );
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 FND_MSG_PUB.Count_And_Get(
1693 p_encoded => 'F'
1694 ,p_count => x_msg_count
1695 ,p_data => x_msg_data
1696 );
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 END GET_CHILD_OBJECTS;
1699
1700
1701 /*------------------------------------------------------------------------------------------
1702 Procedure INSERT_LOCK_ALL
1703 This procedure inserts the ALL entries into the lock table and user table
1704 <parameters>
1705 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1706 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1707 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1708 "PERIODICITY", and "TABLE"
1709 p_program_id: -100 = Data Loader UI
1710 -101 = Data Loader Backend
1711 -200 = Generate Database
1712 -201 = Generate Documentation
1713 -202 = Rename Interface Table
1714 -203 = Generate Database Configuration
1715 -300 = Administrator
1716 -400 = Objective Designer
1717 -500 = Builder
1718 -600 = Performance Scorecard
1719 -700 = System Upgrade
1720 -800 = System Migration
1721 p_user_id: Application User ID
1722 p_machine: The Machine
1723 p_terminal: The Terminal
1724 p_session_id: The Database Session ID
1725 -------------------------------------------------------------------------------------------*/
1726 Procedure INSERT_LOCK_ALL(
1727 p_object_type IN varchar2
1728 ,p_program_id IN number
1729 ,p_user_id IN number
1730 ,p_machine IN varchar2
1731 ,p_terminal IN varchar2
1732 ,p_session_id IN number
1733 ,x_return_status OUT NOCOPY varchar2
1734 ,x_msg_count OUT NOCOPY number
1735 ,x_msg_data OUT NOCOPY varchar2
1736 ) IS
1737
1738 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_ALL';
1739
1740 BEGIN
1741 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_ALL: '||p_object_type);
1742 x_return_status := FND_API.G_RET_STS_SUCCESS;
1743
1744 BSC_LOCKS_PVT.INSERT_LOCK_INFO
1745 (
1746 p_object_key => 'ALL'
1747 ,p_object_type => p_object_type
1748 ,p_lock_type => 'W'
1749 ,p_last_save_time => SYSDATE
1750 ,p_session_id => p_session_id
1751 ,x_return_status => x_return_status
1752 ,x_msg_count => x_msg_count
1753 ,x_msg_data => x_msg_data
1754 );
1755 BSC_LOCKS_PVT.INSERT_USER_INFO
1756 (
1757 p_object_key => 'ALL'
1758 ,p_object_type => p_object_type
1759 ,p_user_type => 'L'
1760 ,p_program_id => p_program_id
1761 ,p_user_id => p_user_id
1762 ,p_machine => p_machine
1763 ,p_terminal => p_terminal
1764 ,x_return_status => x_return_status
1765 ,x_msg_count => x_msg_count
1766 ,x_msg_data => x_msg_data
1767 );
1768 BSC_LOCKS_PVT.INSERT_USER_INFO
1769 (
1770 p_object_key => 'ALL'
1771 ,p_object_type => p_object_type
1772 ,p_user_type => 'M'
1773 ,p_program_id => p_program_id
1774 ,p_user_id => p_user_id
1775 ,p_machine => p_machine
1776 ,p_terminal => p_terminal
1777 ,x_return_status => x_return_status
1778 ,x_msg_count => x_msg_count
1779 ,x_msg_data => x_msg_data
1780 );
1781
1782 EXCEPTION
1783 WHEN FND_API.G_EXC_ERROR THEN
1784 x_return_status := FND_API.G_RET_STS_ERROR;
1785 FND_MSG_PUB.Count_And_Get(
1786 p_encoded => 'F'
1787 ,p_count => x_msg_count
1788 ,p_data => x_msg_data
1789 );
1790 RAISE;
1791 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1793 FND_MSG_PUB.Count_And_Get(
1794 p_encoded => 'F'
1795 ,p_count => x_msg_count
1796 ,p_data => x_msg_data
1797 );
1798 RAISE;
1799 WHEN OTHERS THEN
1800 FND_MSG_PUB.Add_Exc_Msg(
1801 G_PKG_NAME,
1802 l_api_name,
1803 SQLERRM
1804 );
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 FND_MSG_PUB.Count_And_Get(
1807 p_encoded => 'F'
1808 ,p_count => x_msg_count
1809 ,p_data => x_msg_data
1810 );
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812 END INSERT_LOCK_ALL;
1813
1814
1815 /*------------------------------------------------------------------------------------------
1816 Procedure INSERT_LOCK_ALL_AUTONOMOUS
1817 This procedure inserts the ALL entries into the lock table and user table
1818 <parameters>
1819 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1820 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1821 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1822 "PERIODICITY", and "TABLE"
1823 p_program_id: -100 = Data Loader UI
1824 -101 = Data Loader Backend
1825 -200 = Generate Database
1826 -201 = Generate Documentation
1827 -202 = Rename Interface Table
1828 -203 = Generate Database Configuration
1829 -300 = Administrator
1830 -400 = Objective Designer
1831 -500 = Builder
1832 -600 = Performance Scorecard
1833 -700 = System Upgrade
1834 -800 = System Migration
1835 p_user_id: Application User ID
1836 p_machine: The Machine
1837 p_terminal: The Terminal
1838 p_session_id: The Database Session ID
1839 -------------------------------------------------------------------------------------------*/
1840 Procedure INSERT_LOCK_ALL_AUTONOMOUS(
1841 p_object_type IN varchar2
1842 ,p_program_id IN number
1843 ,p_user_id IN number
1844 ,p_machine IN varchar2
1845 ,p_terminal IN varchar2
1846 ,p_session_id IN number
1847 ,x_return_status OUT NOCOPY varchar2
1848 ,x_msg_count OUT NOCOPY number
1849 ,x_msg_data OUT NOCOPY varchar2
1850 ) IS
1851 PRAGMA AUTONOMOUS_TRANSACTION;
1852
1853 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_ALL_AUTONOMOUS';
1854
1855 BEGIN
1856 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_ALL_AUTONOMOUS: '||p_object_type);
1857 x_return_status := FND_API.G_RET_STS_SUCCESS;
1858
1859 BSC_LOCKS_PVT.INSERT_LOCK_ALL
1860 (
1861 p_object_type => p_object_type
1862 ,p_program_id => p_program_id
1863 ,p_user_id => p_user_id
1864 ,p_machine => p_machine
1865 ,p_terminal => p_terminal
1866 ,p_session_id => p_session_id
1867 ,x_return_status => x_return_status
1868 ,x_msg_count => x_msg_count
1869 ,x_msg_data => x_msg_data
1870 );
1871 COMMIT;
1872
1873 EXCEPTION
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ROLLBACK;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MSG_PUB.Count_And_Get(
1878 p_encoded => 'F'
1879 ,p_count => x_msg_count
1880 ,p_data => x_msg_data
1881 );
1882 RAISE;
1883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1884 ROLLBACK;
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 FND_MSG_PUB.Count_And_Get(
1887 p_encoded => 'F'
1888 ,p_count => x_msg_count
1889 ,p_data => x_msg_data
1890 );
1891 RAISE;
1892 WHEN OTHERS THEN
1893 ROLLBACK;
1894 FND_MSG_PUB.Add_Exc_Msg(
1895 G_PKG_NAME,
1896 l_api_name,
1897 SQLERRM
1898 );
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1900 FND_MSG_PUB.Count_And_Get(
1901 p_encoded => 'F'
1902 ,p_count => x_msg_count
1903 ,p_data => x_msg_data
1904 );
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906 END INSERT_LOCK_ALL_AUTONOMOUS;
1907
1908
1909 /*------------------------------------------------------------------------------------------
1910 Procedure INSERT_LOCK_SCORECARD
1911 This procedure inserts scorecard entries into the lock table and user table
1912 <parameters>
1913 p_program_id: -100 = Data Loader UI
1914 -101 = Data Loader Backend
1915 -200 = Generate Database
1916 -201 = Generate Documentation
1917 -202 = Rename Interface Table
1918 -203 = Generate Database Configuration
1919 -300 = Administrator
1920 -400 = Objective Designer
1921 -500 = Builder
1922 -600 = Performance Scorecard
1923 -700 = System Upgrade
1924 -800 = System Migration
1925 p_user_id: Application User ID
1926 p_machine: The Machine
1927 p_terminal: The Terminal
1928 -------------------------------------------------------------------------------------------*/
1929 Procedure INSERT_LOCK_SCORECARD(
1930 p_program_id IN number
1931 ,p_user_id IN number
1932 ,p_machine IN varchar2
1933 ,p_terminal IN varchar2
1934 ,x_return_status OUT NOCOPY varchar2
1935 ,x_msg_count OUT NOCOPY number
1936 ,x_msg_data OUT NOCOPY varchar2
1937 ) IS
1938
1939 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_SCORECARD';
1940 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
1941 l_session_id NUMBER := USERENV('SESSIONID');
1942
1943 BEGIN
1944 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_SCORECARD');
1945 x_return_status := FND_API.G_RET_STS_SUCCESS;
1946 l_object_type := 'SCORECARD';
1947
1948 -- Insert the ALL entries
1949 BSC_LOCKS_PVT.INSERT_LOCK_ALL
1950 (
1951 p_object_type => l_object_type
1952 ,p_program_id => p_program_id
1953 ,p_user_id => p_user_id
1954 ,p_machine => p_machine
1955 ,p_terminal => p_terminal
1956 ,p_session_id => l_session_id
1957 ,x_return_status => x_return_status
1958 ,x_msg_count => x_msg_count
1959 ,x_msg_data => x_msg_data
1960 );
1961
1962 -- Insert Scorecard entries
1963 INSERT INTO BSC_OBJECT_LOCKS (
1964 OBJECT_KEY,
1965 OBJECT_TYPE,
1966 LOCK_TYPE,
1967 LAST_SAVE_TIME,
1968 CREATED_BY,
1969 CREATION_DATE,
1970 LAST_UPDATED_BY,
1971 LAST_UPDATE_DATE,
1972 LAST_UPDATE_LOGIN,
1973 SESSION_ID
1974 ) SELECT TO_CHAR(TAB_ID),
1975 l_object_type,
1976 'W',
1977 SYSDATE,
1978 FND_GLOBAL.USER_ID,
1979 SYSDATE,
1980 FND_GLOBAL.USER_ID,
1981 SYSDATE,
1982 FND_GLOBAL.USER_ID,
1983 l_session_id
1984 FROM BSC_TABS_B;
1985
1986 INSERT INTO BSC_OBJECT_LOCK_USERS (
1987 OBJECT_KEY,
1988 OBJECT_TYPE,
1989 USER_TYPE,
1990 PROGRAM_ID,
1991 USER_ID,
1992 MACHINE,
1993 TERMINAL,
1994 CREATED_BY,
1995 CREATION_DATE,
1996 LAST_UPDATED_BY,
1997 LAST_UPDATE_DATE,
1998 LAST_UPDATE_LOGIN
1999 ) SELECT TO_CHAR(TAB_ID),
2000 l_object_type,
2001 'L',
2002 p_program_id,
2003 p_user_id,
2004 p_machine,
2005 p_terminal,
2006 FND_GLOBAL.USER_ID,
2007 SYSDATE,
2008 FND_GLOBAL.USER_ID,
2009 SYSDATE,
2010 FND_GLOBAL.USER_ID
2011 FROM BSC_TABS_B;
2012
2013 INSERT INTO BSC_OBJECT_LOCK_USERS (
2014 OBJECT_KEY,
2015 OBJECT_TYPE,
2016 USER_TYPE,
2017 PROGRAM_ID,
2018 USER_ID,
2019 MACHINE,
2020 TERMINAL,
2021 CREATED_BY,
2022 CREATION_DATE,
2023 LAST_UPDATED_BY,
2024 LAST_UPDATE_DATE,
2025 LAST_UPDATE_LOGIN
2026 ) SELECT TO_CHAR(TAB_ID),
2027 l_object_type,
2028 'M',
2029 p_program_id,
2030 p_user_id,
2031 p_machine,
2032 p_terminal,
2033 FND_GLOBAL.USER_ID,
2034 SYSDATE,
2035 FND_GLOBAL.USER_ID,
2036 SYSDATE,
2037 FND_GLOBAL.USER_ID
2038 FROM BSC_TABS_B;
2039
2040 EXCEPTION
2041 WHEN FND_API.G_EXC_ERROR THEN
2042 x_return_status := FND_API.G_RET_STS_ERROR;
2043 FND_MSG_PUB.Count_And_Get(
2044 p_encoded => 'F'
2045 ,p_count => x_msg_count
2046 ,p_data => x_msg_data
2047 );
2048 RAISE;
2049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2051 FND_MSG_PUB.Count_And_Get(
2052 p_encoded => 'F'
2053 ,p_count => x_msg_count
2054 ,p_data => x_msg_data
2055 );
2056 RAISE;
2057 WHEN OTHERS THEN
2058 FND_MSG_PUB.Add_Exc_Msg(
2059 G_PKG_NAME,
2060 l_api_name,
2061 SQLERRM
2062 );
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 FND_MSG_PUB.Count_And_Get(
2065 p_encoded => 'F'
2066 ,p_count => x_msg_count
2067 ,p_data => x_msg_data
2068 );
2069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2070 END INSERT_LOCK_SCORECARD;
2071
2072
2073 /*------------------------------------------------------------------------------------------
2074 Procedure INSERT_LOCK_OBJECTIVE
2075 This procedure inserts objective entries into the lock table and user table
2076 <parameters>
2077 p_program_id: -100 = Data Loader UI
2078 -101 = Data Loader Backend
2079 -200 = Generate Database
2080 -201 = Generate Documentation
2081 -202 = Rename Interface Table
2082 -203 = Generate Database Configuration
2083 -300 = Administrator
2084 -400 = Objective Designer
2085 -500 = Builder
2086 -600 = Performance Scorecard
2087 -700 = System Upgrade
2088 -800 = System Migration
2089 p_user_id: Application User ID
2090 p_machine: The Machine
2091 p_terminal: The Terminal
2092 -------------------------------------------------------------------------------------------*/
2093 Procedure INSERT_LOCK_OBJECTIVE(
2094 p_program_id IN number
2095 ,p_user_id IN number
2096 ,p_machine IN varchar2
2097 ,p_terminal IN varchar2
2098 ,x_return_status OUT NOCOPY varchar2
2099 ,x_msg_count OUT NOCOPY number
2100 ,x_msg_data OUT NOCOPY varchar2
2101 ) IS
2102
2103 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_OBJECTIVE';
2104 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2105 l_session_id NUMBER := USERENV('SESSIONID');
2106
2107 BEGIN
2108 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_OBJECTIVE');
2109 x_return_status := FND_API.G_RET_STS_SUCCESS;
2110 l_object_type := 'OBJECTIVE';
2111
2112 -- Insert the ALL entries
2113 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2114 (
2115 p_object_type => l_object_type
2116 ,p_program_id => p_program_id
2117 ,p_user_id => p_user_id
2118 ,p_machine => p_machine
2119 ,p_terminal => p_terminal
2120 ,p_session_id => l_session_id
2121 ,x_return_status => x_return_status
2122 ,x_msg_count => x_msg_count
2123 ,x_msg_data => x_msg_data
2124 );
2125
2126 -- Insert Objective entries
2127 INSERT INTO BSC_OBJECT_LOCKS (
2128 OBJECT_KEY,
2129 OBJECT_TYPE,
2130 LOCK_TYPE,
2131 LAST_SAVE_TIME,
2132 CREATED_BY,
2133 CREATION_DATE,
2134 LAST_UPDATED_BY,
2135 LAST_UPDATE_DATE,
2136 LAST_UPDATE_LOGIN,
2137 SESSION_ID
2138 ) SELECT TO_CHAR(INDICATOR),
2139 l_object_type,
2140 'W',
2141 SYSDATE,
2142 FND_GLOBAL.USER_ID,
2143 SYSDATE,
2144 FND_GLOBAL.USER_ID,
2145 SYSDATE,
2146 FND_GLOBAL.USER_ID,
2147 l_session_id
2148 FROM BSC_KPIS_B;
2149
2150 INSERT INTO BSC_OBJECT_LOCK_USERS (
2151 OBJECT_KEY,
2152 OBJECT_TYPE,
2153 USER_TYPE,
2154 PROGRAM_ID,
2155 USER_ID,
2156 MACHINE,
2157 TERMINAL,
2158 CREATED_BY,
2159 CREATION_DATE,
2160 LAST_UPDATED_BY,
2161 LAST_UPDATE_DATE,
2162 LAST_UPDATE_LOGIN
2163 ) SELECT TO_CHAR(INDICATOR),
2164 l_object_type,
2165 'L',
2166 p_program_id,
2167 p_user_id,
2168 p_machine,
2169 p_terminal,
2170 FND_GLOBAL.USER_ID,
2171 SYSDATE,
2172 FND_GLOBAL.USER_ID,
2173 SYSDATE,
2174 FND_GLOBAL.USER_ID
2175 FROM BSC_KPIS_B;
2176
2177 INSERT INTO BSC_OBJECT_LOCK_USERS (
2178 OBJECT_KEY,
2179 OBJECT_TYPE,
2180 USER_TYPE,
2181 PROGRAM_ID,
2182 USER_ID,
2183 MACHINE,
2184 TERMINAL,
2185 CREATED_BY,
2186 CREATION_DATE,
2187 LAST_UPDATED_BY,
2188 LAST_UPDATE_DATE,
2189 LAST_UPDATE_LOGIN
2190 ) SELECT TO_CHAR(INDICATOR),
2191 l_object_type,
2192 'M',
2193 p_program_id,
2194 p_user_id,
2195 p_machine,
2196 p_terminal,
2197 FND_GLOBAL.USER_ID,
2198 SYSDATE,
2199 FND_GLOBAL.USER_ID,
2200 SYSDATE,
2201 FND_GLOBAL.USER_ID
2202 FROM BSC_KPIS_B;
2203
2204 EXCEPTION
2205 WHEN FND_API.G_EXC_ERROR THEN
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207 FND_MSG_PUB.Count_And_Get(
2208 p_encoded => 'F'
2209 ,p_count => x_msg_count
2210 ,p_data => x_msg_data
2211 );
2212 RAISE;
2213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2215 FND_MSG_PUB.Count_And_Get(
2216 p_encoded => 'F'
2217 ,p_count => x_msg_count
2218 ,p_data => x_msg_data
2219 );
2220 RAISE;
2221 WHEN OTHERS THEN
2222 FND_MSG_PUB.Add_Exc_Msg(
2223 G_PKG_NAME,
2224 l_api_name,
2225 SQLERRM
2226 );
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228 FND_MSG_PUB.Count_And_Get(
2229 p_encoded => 'F'
2230 ,p_count => x_msg_count
2231 ,p_data => x_msg_data
2232 );
2233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2234 END INSERT_LOCK_OBJECTIVE;
2235
2236
2237 /*------------------------------------------------------------------------------------------
2238 Procedure INSERT_LOCK_DIMENSION
2239 This procedure inserts dimension entries into the lock table and user table
2240 <parameters>
2241 p_program_id: -100 = Data Loader UI
2242 -101 = Data Loader Backend
2243 -200 = Generate Database
2244 -201 = Generate Documentation
2245 -202 = Rename Interface Table
2246 -203 = Generate Database Configuration
2247 -300 = Administrator
2248 -400 = Objective Designer
2249 -500 = Builder
2250 -600 = Performance Scorecard
2251 -700 = System Upgrade
2252 -800 = System Migration
2253 p_user_id: Application User ID
2254 p_machine: The Machine
2255 p_terminal: The Terminal
2256 -------------------------------------------------------------------------------------------*/
2257 Procedure INSERT_LOCK_DIMENSION(
2258 p_program_id IN number
2259 ,p_user_id IN number
2260 ,p_machine IN varchar2
2261 ,p_terminal IN varchar2
2262 ,x_return_status OUT NOCOPY varchar2
2263 ,x_msg_count OUT NOCOPY number
2264 ,x_msg_data OUT NOCOPY varchar2
2265 ) IS
2266
2267 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_DIMENSION';
2268 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2269 l_session_id NUMBER := USERENV('SESSIONID');
2270
2271 BEGIN
2272 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_DIMENSION');
2273 x_return_status := FND_API.G_RET_STS_SUCCESS;
2274 l_object_type := 'DIMENSION';
2275
2276 -- Insert the ALL entries
2277 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2278 (
2279 p_object_type => l_object_type
2280 ,p_program_id => p_program_id
2281 ,p_user_id => p_user_id
2282 ,p_machine => p_machine
2283 ,p_terminal => p_terminal
2284 ,p_session_id => l_session_id
2285 ,x_return_status => x_return_status
2286 ,x_msg_count => x_msg_count
2287 ,x_msg_data => x_msg_data
2288 );
2289
2290 -- Insert Dimension entries
2291 INSERT INTO BSC_OBJECT_LOCKS (
2292 OBJECT_KEY,
2293 OBJECT_TYPE,
2294 LOCK_TYPE,
2295 LAST_SAVE_TIME,
2296 CREATED_BY,
2297 CREATION_DATE,
2298 LAST_UPDATED_BY,
2299 LAST_UPDATE_DATE,
2300 LAST_UPDATE_LOGIN,
2301 SESSION_ID
2302 ) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
2303 l_object_type,
2304 'W',
2305 SYSDATE,
2306 FND_GLOBAL.USER_ID,
2307 SYSDATE,
2308 FND_GLOBAL.USER_ID,
2309 SYSDATE,
2310 FND_GLOBAL.USER_ID,
2311 l_session_id
2312 FROM BSC_SYS_DIM_GROUPS_TL;
2313
2314 INSERT INTO BSC_OBJECT_LOCK_USERS (
2315 OBJECT_KEY,
2316 OBJECT_TYPE,
2317 USER_TYPE,
2318 PROGRAM_ID,
2319 USER_ID,
2320 MACHINE,
2321 TERMINAL,
2322 CREATED_BY,
2323 CREATION_DATE,
2324 LAST_UPDATED_BY,
2325 LAST_UPDATE_DATE,
2326 LAST_UPDATE_LOGIN
2327 ) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
2328 l_object_type,
2329 'L',
2330 p_program_id,
2331 p_user_id,
2332 p_machine,
2333 p_terminal,
2334 FND_GLOBAL.USER_ID,
2335 SYSDATE,
2336 FND_GLOBAL.USER_ID,
2337 SYSDATE,
2338 FND_GLOBAL.USER_ID
2339 FROM BSC_SYS_DIM_GROUPS_TL;
2340
2341 INSERT INTO BSC_OBJECT_LOCK_USERS (
2342 OBJECT_KEY,
2343 OBJECT_TYPE,
2344 USER_TYPE,
2345 PROGRAM_ID,
2346 USER_ID,
2347 MACHINE,
2348 TERMINAL,
2349 CREATED_BY,
2350 CREATION_DATE,
2351 LAST_UPDATED_BY,
2352 LAST_UPDATE_DATE,
2353 LAST_UPDATE_LOGIN
2354 ) SELECT DISTINCT TO_CHAR(DIM_GROUP_ID),
2355 l_object_type,
2356 'M',
2357 p_program_id,
2358 p_user_id,
2359 p_machine,
2360 p_terminal,
2361 FND_GLOBAL.USER_ID,
2362 SYSDATE,
2363 FND_GLOBAL.USER_ID,
2364 SYSDATE,
2365 FND_GLOBAL.USER_ID
2366 FROM BSC_SYS_DIM_GROUPS_TL;
2367
2368 EXCEPTION
2369 WHEN FND_API.G_EXC_ERROR THEN
2370 x_return_status := FND_API.G_RET_STS_ERROR;
2371 FND_MSG_PUB.Count_And_Get(
2372 p_encoded => 'F'
2373 ,p_count => x_msg_count
2374 ,p_data => x_msg_data
2375 );
2376 RAISE;
2377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379 FND_MSG_PUB.Count_And_Get(
2380 p_encoded => 'F'
2381 ,p_count => x_msg_count
2382 ,p_data => x_msg_data
2383 );
2384 RAISE;
2385 WHEN OTHERS THEN
2386 FND_MSG_PUB.Add_Exc_Msg(
2387 G_PKG_NAME,
2388 l_api_name,
2389 SQLERRM
2390 );
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392 FND_MSG_PUB.Count_And_Get(
2393 p_encoded => 'F'
2394 ,p_count => x_msg_count
2395 ,p_data => x_msg_data
2396 );
2397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2398 END INSERT_LOCK_DIMENSION;
2399
2400
2401 /*------------------------------------------------------------------------------------------
2402 Procedure INSERT_LOCK_DIMENSION_OBJECT
2403 This procedure inserts dimension object entries into the lock table and user table
2404 <parameters>
2405 p_program_id: -100 = Data Loader UI
2406 -101 = Data Loader Backend
2407 -200 = Generate Database
2408 -201 = Generate Documentation
2409 -202 = Rename Interface Table
2410 -203 = Generate Database Configuration
2411 -300 = Administrator
2412 -400 = Objective Designer
2413 -500 = Builder
2414 -600 = Performance Scorecard
2415 -700 = System Upgrade
2416 -800 = System Migration
2417 p_user_id: Application User ID
2418 p_machine: The Machine
2419 p_terminal: The Terminal
2420 -------------------------------------------------------------------------------------------*/
2421 Procedure INSERT_LOCK_DIMENSION_OBJECT(
2422 p_program_id IN number
2423 ,p_user_id IN number
2424 ,p_machine IN varchar2
2425 ,p_terminal IN varchar2
2426 ,x_return_status OUT NOCOPY varchar2
2427 ,x_msg_count OUT NOCOPY number
2428 ,x_msg_data OUT NOCOPY varchar2
2429 ) IS
2430
2431 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_DIMENSION_OBJECT';
2432 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2433 l_session_id NUMBER := USERENV('SESSIONID');
2434
2435 BEGIN
2436 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_DIMENSION_OBJECT');
2437 x_return_status := FND_API.G_RET_STS_SUCCESS;
2438 l_object_type := 'DIMENSION_OBJECT';
2439
2440 -- Insert the ALL entries
2441 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2442 (
2443 p_object_type => l_object_type
2444 ,p_program_id => p_program_id
2445 ,p_user_id => p_user_id
2446 ,p_machine => p_machine
2447 ,p_terminal => p_terminal
2448 ,p_session_id => l_session_id
2449 ,x_return_status => x_return_status
2450 ,x_msg_count => x_msg_count
2451 ,x_msg_data => x_msg_data
2452 );
2453
2454 -- Insert Dimension Object entries
2455 INSERT INTO BSC_OBJECT_LOCKS (
2456 OBJECT_KEY,
2457 OBJECT_TYPE,
2458 LOCK_TYPE,
2459 LAST_SAVE_TIME,
2460 CREATED_BY,
2461 CREATION_DATE,
2462 LAST_UPDATED_BY,
2463 LAST_UPDATE_DATE,
2464 LAST_UPDATE_LOGIN,
2465 SESSION_ID
2466 ) SELECT TO_CHAR(DIM_LEVEL_ID),
2467 l_object_type,
2468 'W',
2469 SYSDATE,
2470 FND_GLOBAL.USER_ID,
2471 SYSDATE,
2472 FND_GLOBAL.USER_ID,
2473 SYSDATE,
2474 FND_GLOBAL.USER_ID,
2475 l_session_id
2476 FROM BSC_SYS_DIM_LEVELS_B;
2477
2478 INSERT INTO BSC_OBJECT_LOCK_USERS (
2479 OBJECT_KEY,
2480 OBJECT_TYPE,
2481 USER_TYPE,
2482 PROGRAM_ID,
2483 USER_ID,
2484 MACHINE,
2485 TERMINAL,
2486 CREATED_BY,
2487 CREATION_DATE,
2488 LAST_UPDATED_BY,
2489 LAST_UPDATE_DATE,
2490 LAST_UPDATE_LOGIN
2491 ) SELECT TO_CHAR(DIM_LEVEL_ID),
2492 l_object_type,
2493 'L',
2494 p_program_id,
2495 p_user_id,
2496 p_machine,
2497 p_terminal,
2498 FND_GLOBAL.USER_ID,
2499 SYSDATE,
2500 FND_GLOBAL.USER_ID,
2501 SYSDATE,
2502 FND_GLOBAL.USER_ID
2503 FROM BSC_SYS_DIM_LEVELS_B;
2504
2505 INSERT INTO BSC_OBJECT_LOCK_USERS (
2506 OBJECT_KEY,
2507 OBJECT_TYPE,
2508 USER_TYPE,
2509 PROGRAM_ID,
2510 USER_ID,
2511 MACHINE,
2512 TERMINAL,
2513 CREATED_BY,
2514 CREATION_DATE,
2515 LAST_UPDATED_BY,
2516 LAST_UPDATE_DATE,
2517 LAST_UPDATE_LOGIN
2518 ) SELECT TO_CHAR(DIM_LEVEL_ID),
2519 l_object_type,
2520 'M',
2521 p_program_id,
2522 p_user_id,
2523 p_machine,
2524 p_terminal,
2525 FND_GLOBAL.USER_ID,
2526 SYSDATE,
2527 FND_GLOBAL.USER_ID,
2528 SYSDATE,
2529 FND_GLOBAL.USER_ID
2530 FROM BSC_SYS_DIM_LEVELS_B;
2531
2532 EXCEPTION
2533 WHEN FND_API.G_EXC_ERROR THEN
2534 x_return_status := FND_API.G_RET_STS_ERROR;
2535 FND_MSG_PUB.Count_And_Get(
2536 p_encoded => 'F'
2537 ,p_count => x_msg_count
2538 ,p_data => x_msg_data
2539 );
2540 RAISE;
2541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2543 FND_MSG_PUB.Count_And_Get(
2544 p_encoded => 'F'
2545 ,p_count => x_msg_count
2546 ,p_data => x_msg_data
2547 );
2548 RAISE;
2549 WHEN OTHERS THEN
2550 FND_MSG_PUB.Add_Exc_Msg(
2551 G_PKG_NAME,
2552 l_api_name,
2553 SQLERRM
2554 );
2555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2556 FND_MSG_PUB.Count_And_Get(
2557 p_encoded => 'F'
2558 ,p_count => x_msg_count
2559 ,p_data => x_msg_data
2560 );
2561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562 END INSERT_LOCK_DIMENSION_OBJECT;
2563
2564
2565 /*------------------------------------------------------------------------------------------
2566 Procedure INSERT_LOCK_MEASURE
2567 This procedure inserts measure entries into the lock table and user table
2568 <parameters>
2569 p_program_id: -100 = Data Loader UI
2570 -101 = Data Loader Backend
2571 -200 = Generate Database
2572 -201 = Generate Documentation
2573 -202 = Rename Interface Table
2574 -203 = Generate Database Configuration
2575 -300 = Administrator
2576 -400 = Objective Designer
2577 -500 = Builder
2578 -600 = Performance Scorecard
2579 -700 = System Upgrade
2580 -800 = System Migration
2581 p_user_id: Application User ID
2582 p_machine: The Machine
2583 p_terminal: The Terminal
2584 -------------------------------------------------------------------------------------------*/
2585 Procedure INSERT_LOCK_MEASURE(
2586 p_program_id IN number
2587 ,p_user_id IN number
2588 ,p_machine IN varchar2
2589 ,p_terminal IN varchar2
2590 ,x_return_status OUT NOCOPY varchar2
2591 ,x_msg_count OUT NOCOPY number
2592 ,x_msg_data OUT NOCOPY varchar2
2593 ) IS
2594
2595 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_MEASURE';
2596 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2597 l_session_id NUMBER := USERENV('SESSIONID');
2598
2599 BEGIN
2600 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_MEASURE');
2601 x_return_status := FND_API.G_RET_STS_SUCCESS;
2602 l_object_type := 'MEASURE';
2603
2604 -- Insert the ALL entries
2605 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2606 (
2607 p_object_type => l_object_type
2608 ,p_program_id => p_program_id
2609 ,p_user_id => p_user_id
2610 ,p_machine => p_machine
2611 ,p_terminal => p_terminal
2612 ,p_session_id => l_session_id
2613 ,x_return_status => x_return_status
2614 ,x_msg_count => x_msg_count
2615 ,x_msg_data => x_msg_data
2616 );
2617
2618 -- Insert Measure entries
2619 INSERT INTO BSC_OBJECT_LOCKS (
2620 OBJECT_KEY,
2621 OBJECT_TYPE,
2622 LOCK_TYPE,
2623 LAST_SAVE_TIME,
2624 CREATED_BY,
2625 CREATION_DATE,
2626 LAST_UPDATED_BY,
2627 LAST_UPDATE_DATE,
2628 LAST_UPDATE_LOGIN,
2629 SESSION_ID
2630 ) SELECT TO_CHAR(DATASET_ID),
2631 l_object_type,
2632 'W',
2633 SYSDATE,
2634 FND_GLOBAL.USER_ID,
2635 SYSDATE,
2636 FND_GLOBAL.USER_ID,
2637 SYSDATE,
2638 FND_GLOBAL.USER_ID,
2639 l_session_id
2640 FROM BSC_SYS_DATASETS_B;
2641
2642 INSERT INTO BSC_OBJECT_LOCK_USERS (
2643 OBJECT_KEY,
2644 OBJECT_TYPE,
2645 USER_TYPE,
2646 PROGRAM_ID,
2647 USER_ID,
2648 MACHINE,
2649 TERMINAL,
2650 CREATED_BY,
2651 CREATION_DATE,
2652 LAST_UPDATED_BY,
2653 LAST_UPDATE_DATE,
2654 LAST_UPDATE_LOGIN
2655 ) SELECT TO_CHAR(DATASET_ID),
2656 l_object_type,
2657 'L',
2658 p_program_id,
2659 p_user_id,
2660 p_machine,
2661 p_terminal,
2662 FND_GLOBAL.USER_ID,
2663 SYSDATE,
2664 FND_GLOBAL.USER_ID,
2665 SYSDATE,
2666 FND_GLOBAL.USER_ID
2667 FROM BSC_SYS_DATASETS_B;
2668
2669 INSERT INTO BSC_OBJECT_LOCK_USERS (
2670 OBJECT_KEY,
2671 OBJECT_TYPE,
2672 USER_TYPE,
2673 PROGRAM_ID,
2674 USER_ID,
2675 MACHINE,
2676 TERMINAL,
2677 CREATED_BY,
2678 CREATION_DATE,
2679 LAST_UPDATED_BY,
2680 LAST_UPDATE_DATE,
2681 LAST_UPDATE_LOGIN
2682 ) SELECT TO_CHAR(DATASET_ID),
2683 l_object_type,
2684 'M',
2685 p_program_id,
2686 p_user_id,
2687 p_machine,
2688 p_terminal,
2689 FND_GLOBAL.USER_ID,
2690 SYSDATE,
2691 FND_GLOBAL.USER_ID,
2692 SYSDATE,
2693 FND_GLOBAL.USER_ID
2694 FROM BSC_SYS_DATASETS_B;
2695
2696 EXCEPTION
2697 WHEN FND_API.G_EXC_ERROR THEN
2698 x_return_status := FND_API.G_RET_STS_ERROR;
2699 FND_MSG_PUB.Count_And_Get(
2700 p_encoded => 'F'
2701 ,p_count => x_msg_count
2702 ,p_data => x_msg_data
2703 );
2704 RAISE;
2705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2707 FND_MSG_PUB.Count_And_Get(
2708 p_encoded => 'F'
2709 ,p_count => x_msg_count
2710 ,p_data => x_msg_data
2711 );
2712 RAISE;
2713 WHEN OTHERS THEN
2714 FND_MSG_PUB.Add_Exc_Msg(
2715 G_PKG_NAME,
2716 l_api_name,
2717 SQLERRM
2718 );
2719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2720 FND_MSG_PUB.Count_And_Get(
2721 p_encoded => 'F'
2722 ,p_count => x_msg_count
2723 ,p_data => x_msg_data
2724 );
2725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2726 END INSERT_LOCK_MEASURE;
2727
2728
2729 /*------------------------------------------------------------------------------------------
2730 Procedure INSERT_LOCK_DATA_COLUMN
2731 This procedure inserts data column entries into the lock table and user table
2732 <parameters>
2733 p_program_id: -100 = Data Loader UI
2734 -101 = Data Loader Backend
2735 -200 = Generate Database
2736 -201 = Generate Documentation
2737 -202 = Rename Interface Table
2738 -203 = Generate Database Configuration
2739 -300 = Administrator
2740 -400 = Objective Designer
2741 -500 = Builder
2742 -600 = Performance Scorecard
2743 -700 = System Upgrade
2744 -800 = System Migration
2745 p_user_id: Application User ID
2746 p_machine: The Machine
2747 p_terminal: The Terminal
2748 -------------------------------------------------------------------------------------------*/
2749 Procedure INSERT_LOCK_DATA_COLUMN(
2750 p_program_id IN number
2751 ,p_user_id IN number
2752 ,p_machine IN varchar2
2753 ,p_terminal IN varchar2
2754 ,x_return_status OUT NOCOPY varchar2
2755 ,x_msg_count OUT NOCOPY number
2756 ,x_msg_data OUT NOCOPY varchar2
2757 ) IS
2758
2759 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_DATA_COLUMN';
2760 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2761 l_session_id NUMBER := USERENV('SESSIONID');
2762
2763 BEGIN
2764 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_DATA_COLUMN');
2765 x_return_status := FND_API.G_RET_STS_SUCCESS;
2766 l_object_type := 'DATA_COLUMN';
2767
2768 -- Insert the ALL entries
2769 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2770 (
2771 p_object_type => l_object_type
2772 ,p_program_id => p_program_id
2773 ,p_user_id => p_user_id
2774 ,p_machine => p_machine
2775 ,p_terminal => p_terminal
2776 ,p_session_id => l_session_id
2777 ,x_return_status => x_return_status
2778 ,x_msg_count => x_msg_count
2779 ,x_msg_data => x_msg_data
2780 );
2781
2782 -- Insert Data Column entries
2783 INSERT INTO BSC_OBJECT_LOCKS (
2784 OBJECT_KEY,
2785 OBJECT_TYPE,
2786 LOCK_TYPE,
2787 LAST_SAVE_TIME,
2788 CREATED_BY,
2789 CREATION_DATE,
2790 LAST_UPDATED_BY,
2791 LAST_UPDATE_DATE,
2792 LAST_UPDATE_LOGIN,
2793 SESSION_ID
2794 ) SELECT TO_CHAR(MEASURE_ID),
2795 l_object_type,
2796 'W',
2797 SYSDATE,
2798 FND_GLOBAL.USER_ID,
2799 SYSDATE,
2800 FND_GLOBAL.USER_ID,
2801 SYSDATE,
2802 FND_GLOBAL.USER_ID,
2803 l_session_id
2804 FROM BSC_SYS_MEASURES;
2805
2806 INSERT INTO BSC_OBJECT_LOCK_USERS (
2807 OBJECT_KEY,
2808 OBJECT_TYPE,
2809 USER_TYPE,
2810 PROGRAM_ID,
2811 USER_ID,
2812 MACHINE,
2813 TERMINAL,
2814 CREATED_BY,
2815 CREATION_DATE,
2816 LAST_UPDATED_BY,
2817 LAST_UPDATE_DATE,
2818 LAST_UPDATE_LOGIN
2819 ) SELECT TO_CHAR(MEASURE_ID),
2820 l_object_type,
2821 'L',
2822 p_program_id,
2823 p_user_id,
2824 p_machine,
2825 p_terminal,
2826 FND_GLOBAL.USER_ID,
2827 SYSDATE,
2828 FND_GLOBAL.USER_ID,
2829 SYSDATE,
2830 FND_GLOBAL.USER_ID
2831 FROM BSC_SYS_MEASURES;
2832
2833 INSERT INTO BSC_OBJECT_LOCK_USERS (
2834 OBJECT_KEY,
2835 OBJECT_TYPE,
2836 USER_TYPE,
2837 PROGRAM_ID,
2838 USER_ID,
2839 MACHINE,
2840 TERMINAL,
2841 CREATED_BY,
2842 CREATION_DATE,
2843 LAST_UPDATED_BY,
2844 LAST_UPDATE_DATE,
2845 LAST_UPDATE_LOGIN
2846 ) SELECT TO_CHAR(MEASURE_ID),
2847 l_object_type,
2848 'M',
2849 p_program_id,
2850 p_user_id,
2851 p_machine,
2852 p_terminal,
2853 FND_GLOBAL.USER_ID,
2854 SYSDATE,
2855 FND_GLOBAL.USER_ID,
2856 SYSDATE,
2857 FND_GLOBAL.USER_ID
2858 FROM BSC_SYS_MEASURES;
2859
2860 EXCEPTION
2861 WHEN FND_API.G_EXC_ERROR THEN
2862 x_return_status := FND_API.G_RET_STS_ERROR;
2863 FND_MSG_PUB.Count_And_Get(
2864 p_encoded => 'F'
2865 ,p_count => x_msg_count
2866 ,p_data => x_msg_data
2867 );
2868 RAISE;
2869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2871 FND_MSG_PUB.Count_And_Get(
2872 p_encoded => 'F'
2873 ,p_count => x_msg_count
2874 ,p_data => x_msg_data
2875 );
2876 RAISE;
2877 WHEN OTHERS THEN
2878 FND_MSG_PUB.Add_Exc_Msg(
2879 G_PKG_NAME,
2880 l_api_name,
2881 SQLERRM
2882 );
2883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2884 FND_MSG_PUB.Count_And_Get(
2885 p_encoded => 'F'
2886 ,p_count => x_msg_count
2887 ,p_data => x_msg_data
2888 );
2889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890 END INSERT_LOCK_DATA_COLUMN;
2891
2892
2893 /*------------------------------------------------------------------------------------------
2894 Procedure INSERT_LOCK_CUSTOM_VIEW
2895 This procedure inserts custom view entries into the lock table and user table
2896 <parameters>
2897 p_program_id: -100 = Data Loader UI
2898 -101 = Data Loader Backend
2899 -200 = Generate Database
2900 -201 = Generate Documentation
2901 -202 = Rename Interface Table
2902 -203 = Generate Database Configuration
2903 -300 = Administrator
2904 -400 = Objective Designer
2905 -500 = Builder
2906 -600 = Performance Scorecard
2907 -700 = System Upgrade
2908 -800 = System Migration
2909 p_user_id: Application User ID
2910 p_machine: The Machine
2911 p_terminal: The Terminal
2912 -------------------------------------------------------------------------------------------*/
2913 Procedure INSERT_LOCK_CUSTOM_VIEW(
2914 p_program_id IN number
2915 ,p_user_id IN number
2916 ,p_machine IN varchar2
2917 ,p_terminal IN varchar2
2918 ,x_return_status OUT NOCOPY varchar2
2919 ,x_msg_count OUT NOCOPY number
2920 ,x_msg_data OUT NOCOPY varchar2
2921 ) IS
2922
2923 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_CUSTOM_VIEW';
2924 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
2925 l_session_id NUMBER := USERENV('SESSIONID');
2926
2927 BEGIN
2928 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_CUSTOM_VIEW');
2929 x_return_status := FND_API.G_RET_STS_SUCCESS;
2930 l_object_type := 'CUSTOM_VIEW';
2931
2932 -- Insert the ALL entries
2933 BSC_LOCKS_PVT.INSERT_LOCK_ALL
2934 (
2935 p_object_type => l_object_type
2936 ,p_program_id => p_program_id
2937 ,p_user_id => p_user_id
2938 ,p_machine => p_machine
2939 ,p_terminal => p_terminal
2940 ,p_session_id => l_session_id
2941 ,x_return_status => x_return_status
2942 ,x_msg_count => x_msg_count
2943 ,x_msg_data => x_msg_data
2944 );
2945
2946 -- Insert Custom View entries
2947 INSERT INTO BSC_OBJECT_LOCKS (
2948 OBJECT_KEY,
2949 OBJECT_TYPE,
2950 LOCK_TYPE,
2951 LAST_SAVE_TIME,
2952 CREATED_BY,
2953 CREATION_DATE,
2954 LAST_UPDATED_BY,
2955 LAST_UPDATE_DATE,
2956 LAST_UPDATE_LOGIN,
2957 SESSION_ID
2958 ) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
2959 l_object_type,
2960 'W',
2961 SYSDATE,
2962 FND_GLOBAL.USER_ID,
2963 SYSDATE,
2964 FND_GLOBAL.USER_ID,
2965 SYSDATE,
2966 FND_GLOBAL.USER_ID,
2967 l_session_id
2968 FROM BSC_TAB_VIEWS_B;
2969
2970 INSERT INTO BSC_OBJECT_LOCK_USERS (
2971 OBJECT_KEY,
2972 OBJECT_TYPE,
2973 USER_TYPE,
2974 PROGRAM_ID,
2975 USER_ID,
2976 MACHINE,
2977 TERMINAL,
2978 CREATED_BY,
2979 CREATION_DATE,
2980 LAST_UPDATED_BY,
2981 LAST_UPDATE_DATE,
2982 LAST_UPDATE_LOGIN
2983 ) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
2984 l_object_type,
2985 'L',
2986 p_program_id,
2987 p_user_id,
2988 p_machine,
2989 p_terminal,
2990 FND_GLOBAL.USER_ID,
2991 SYSDATE,
2992 FND_GLOBAL.USER_ID,
2993 SYSDATE,
2994 FND_GLOBAL.USER_ID
2995 FROM BSC_TAB_VIEWS_B;
2996
2997 INSERT INTO BSC_OBJECT_LOCK_USERS (
2998 OBJECT_KEY,
2999 OBJECT_TYPE,
3000 USER_TYPE,
3001 PROGRAM_ID,
3002 USER_ID,
3003 MACHINE,
3004 TERMINAL,
3005 CREATED_BY,
3006 CREATION_DATE,
3007 LAST_UPDATED_BY,
3008 LAST_UPDATE_DATE,
3009 LAST_UPDATE_LOGIN
3010 ) SELECT TO_CHAR(TAB_ID)||','||TO_CHAR(TAB_VIEW_ID),
3011 l_object_type,
3012 'M',
3013 p_program_id,
3014 p_user_id,
3015 p_machine,
3016 p_terminal,
3017 FND_GLOBAL.USER_ID,
3018 SYSDATE,
3019 FND_GLOBAL.USER_ID,
3020 SYSDATE,
3021 FND_GLOBAL.USER_ID
3022 FROM BSC_TAB_VIEWS_B;
3023
3024 EXCEPTION
3025 WHEN FND_API.G_EXC_ERROR THEN
3026 x_return_status := FND_API.G_RET_STS_ERROR;
3027 FND_MSG_PUB.Count_And_Get(
3028 p_encoded => 'F'
3029 ,p_count => x_msg_count
3030 ,p_data => x_msg_data
3031 );
3032 RAISE;
3033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3035 FND_MSG_PUB.Count_And_Get(
3036 p_encoded => 'F'
3037 ,p_count => x_msg_count
3038 ,p_data => x_msg_data
3039 );
3040 RAISE;
3041 WHEN OTHERS THEN
3042 FND_MSG_PUB.Add_Exc_Msg(
3043 G_PKG_NAME,
3044 l_api_name,
3045 SQLERRM
3046 );
3047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3048 FND_MSG_PUB.Count_And_Get(
3049 p_encoded => 'F'
3050 ,p_count => x_msg_count
3051 ,p_data => x_msg_data
3052 );
3053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3054 END INSERT_LOCK_CUSTOM_VIEW;
3055
3056
3057 /*------------------------------------------------------------------------------------------
3058 Procedure INSERT_LOCK_LAUNCHPAD
3059 This procedure inserts launchpad entries into the lock table and user table
3060 <parameters>
3061 p_program_id: -100 = Data Loader UI
3062 -101 = Data Loader Backend
3063 -200 = Generate Database
3064 -201 = Generate Documentation
3065 -202 = Rename Interface Table
3066 -203 = Generate Database Configuration
3067 -300 = Administrator
3068 -400 = Objective Designer
3069 -500 = Builder
3070 -600 = Performance Scorecard
3071 -700 = System Upgrade
3072 -800 = System Migration
3073 p_user_id: Application User ID
3074 p_machine: The Machine
3075 p_terminal: The Terminal
3076 -------------------------------------------------------------------------------------------*/
3077 Procedure INSERT_LOCK_LAUNCHPAD(
3078 p_program_id IN number
3079 ,p_user_id IN number
3080 ,p_machine IN varchar2
3081 ,p_terminal IN varchar2
3082 ,x_return_status OUT NOCOPY varchar2
3083 ,x_msg_count OUT NOCOPY number
3084 ,x_msg_data OUT NOCOPY varchar2
3085 ) IS
3086
3087 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_LAUNCHPAD';
3088 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
3089 l_session_id NUMBER := USERENV('SESSIONID');
3090
3091 BEGIN
3092 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_LAUNCHPAD');
3093 x_return_status := FND_API.G_RET_STS_SUCCESS;
3094 l_object_type := 'LAUNCHPAD';
3095
3096 -- Insert the ALL entries
3097 BSC_LOCKS_PVT.INSERT_LOCK_ALL
3098 (
3099 p_object_type => l_object_type
3100 ,p_program_id => p_program_id
3101 ,p_user_id => p_user_id
3102 ,p_machine => p_machine
3103 ,p_terminal => p_terminal
3104 ,p_session_id => l_session_id
3105 ,x_return_status => x_return_status
3106 ,x_msg_count => x_msg_count
3107 ,x_msg_data => x_msg_data
3108 );
3109
3110 -- Insert Launchpad entries
3111 INSERT INTO BSC_OBJECT_LOCKS (
3112 OBJECT_KEY,
3113 OBJECT_TYPE,
3114 LOCK_TYPE,
3115 LAST_SAVE_TIME,
3116 CREATED_BY,
3117 CREATION_DATE,
3118 LAST_UPDATED_BY,
3119 LAST_UPDATE_DATE,
3120 LAST_UPDATE_LOGIN,
3121 SESSION_ID
3122 ) SELECT TO_CHAR(LINK_ID),
3123 l_object_type,
3124 'W',
3125 SYSDATE,
3126 FND_GLOBAL.USER_ID,
3127 SYSDATE,
3128 FND_GLOBAL.USER_ID,
3129 SYSDATE,
3130 FND_GLOBAL.USER_ID,
3131 l_session_id
3132 FROM BSC_TAB_VIEW_LABELS_B
3133 WHERE LABEL_TYPE = 2;
3134
3135 INSERT INTO BSC_OBJECT_LOCK_USERS (
3136 OBJECT_KEY,
3137 OBJECT_TYPE,
3138 USER_TYPE,
3139 PROGRAM_ID,
3140 USER_ID,
3141 MACHINE,
3142 TERMINAL,
3143 CREATED_BY,
3144 CREATION_DATE,
3145 LAST_UPDATED_BY,
3146 LAST_UPDATE_DATE,
3147 LAST_UPDATE_LOGIN
3148 ) SELECT TO_CHAR(LINK_ID),
3149 l_object_type,
3150 'L',
3151 p_program_id,
3152 p_user_id,
3153 p_machine,
3154 p_terminal,
3155 FND_GLOBAL.USER_ID,
3156 SYSDATE,
3157 FND_GLOBAL.USER_ID,
3158 SYSDATE,
3159 FND_GLOBAL.USER_ID
3160 FROM BSC_TAB_VIEW_LABELS_B
3161 WHERE LABEL_TYPE = 2;
3162
3163 INSERT INTO BSC_OBJECT_LOCK_USERS (
3164 OBJECT_KEY,
3165 OBJECT_TYPE,
3166 USER_TYPE,
3167 PROGRAM_ID,
3168 USER_ID,
3169 MACHINE,
3170 TERMINAL,
3171 CREATED_BY,
3172 CREATION_DATE,
3173 LAST_UPDATED_BY,
3174 LAST_UPDATE_DATE,
3175 LAST_UPDATE_LOGIN
3176 ) SELECT TO_CHAR(LINK_ID),
3177 l_object_type,
3178 'M',
3179 p_program_id,
3180 p_user_id,
3181 p_machine,
3182 p_terminal,
3183 FND_GLOBAL.USER_ID,
3184 SYSDATE,
3185 FND_GLOBAL.USER_ID,
3186 SYSDATE,
3187 FND_GLOBAL.USER_ID
3188 FROM BSC_TAB_VIEW_LABELS_B
3189 WHERE LABEL_TYPE = 2;
3190
3191 EXCEPTION
3192 WHEN FND_API.G_EXC_ERROR THEN
3193 x_return_status := FND_API.G_RET_STS_ERROR;
3194 FND_MSG_PUB.Count_And_Get(
3195 p_encoded => 'F'
3196 ,p_count => x_msg_count
3197 ,p_data => x_msg_data
3198 );
3199 RAISE;
3200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3202 FND_MSG_PUB.Count_And_Get(
3203 p_encoded => 'F'
3204 ,p_count => x_msg_count
3205 ,p_data => x_msg_data
3206 );
3207 RAISE;
3208 WHEN OTHERS THEN
3209 FND_MSG_PUB.Add_Exc_Msg(
3210 G_PKG_NAME,
3211 l_api_name,
3212 SQLERRM
3213 );
3214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3215 FND_MSG_PUB.Count_And_Get(
3216 p_encoded => 'F'
3217 ,p_count => x_msg_count
3218 ,p_data => x_msg_data
3219 );
3220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3221 END INSERT_LOCK_LAUNCHPAD;
3222
3223
3224 /*------------------------------------------------------------------------------------------
3225 Procedure INSERT_LOCK_PERIODICITY
3226 This procedure inserts periodicity entries into the lock table and user table
3227 <parameters>
3228 p_program_id: -100 = Data Loader UI
3229 -101 = Data Loader Backend
3230 -200 = Generate Database
3231 -201 = Generate Documentation
3232 -202 = Rename Interface Table
3233 -203 = Generate Database Configuration
3234 -300 = Administrator
3235 -400 = Objective Designer
3236 -500 = Builder
3237 -600 = Performance Scorecard
3238 -700 = System Upgrade
3239 -800 = System Migration
3240 p_user_id: Application User ID
3241 p_machine: The Machine
3242 p_terminal: The Terminal
3243 -------------------------------------------------------------------------------------------*/
3244 Procedure INSERT_LOCK_PERIODICITY(
3245 p_program_id IN number
3246 ,p_user_id IN number
3247 ,p_machine IN varchar2
3248 ,p_terminal IN varchar2
3249 ,x_return_status OUT NOCOPY varchar2
3250 ,x_msg_count OUT NOCOPY number
3251 ,x_msg_data OUT NOCOPY varchar2
3252 ) IS
3253
3254 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_PERIODICITY';
3255 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
3256 l_session_id NUMBER := USERENV('SESSIONID');
3257
3258 BEGIN
3259 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_PERIODICITY');
3260 x_return_status := FND_API.G_RET_STS_SUCCESS;
3261 l_object_type := 'PERIODICITY';
3262
3263 -- Insert the ALL entries
3264 BSC_LOCKS_PVT.INSERT_LOCK_ALL
3265 (
3266 p_object_type => l_object_type
3267 ,p_program_id => p_program_id
3268 ,p_user_id => p_user_id
3269 ,p_machine => p_machine
3270 ,p_terminal => p_terminal
3271 ,p_session_id => l_session_id
3272 ,x_return_status => x_return_status
3273 ,x_msg_count => x_msg_count
3274 ,x_msg_data => x_msg_data
3275 );
3276
3277 -- Insert Periodicity entries
3278 INSERT INTO BSC_OBJECT_LOCKS (
3279 OBJECT_KEY,
3280 OBJECT_TYPE,
3281 LOCK_TYPE,
3282 LAST_SAVE_TIME,
3283 CREATED_BY,
3284 CREATION_DATE,
3285 LAST_UPDATED_BY,
3286 LAST_UPDATE_DATE,
3287 LAST_UPDATE_LOGIN,
3288 SESSION_ID
3289 ) SELECT TO_CHAR(PERIODICITY_ID),
3290 l_object_type,
3291 'W',
3292 SYSDATE,
3293 FND_GLOBAL.USER_ID,
3294 SYSDATE,
3295 FND_GLOBAL.USER_ID,
3296 SYSDATE,
3297 FND_GLOBAL.USER_ID,
3298 l_session_id
3299 FROM BSC_SYS_PERIODICITIES;
3300
3301 INSERT INTO BSC_OBJECT_LOCK_USERS (
3302 OBJECT_KEY,
3303 OBJECT_TYPE,
3304 USER_TYPE,
3305 PROGRAM_ID,
3306 USER_ID,
3307 MACHINE,
3308 TERMINAL,
3309 CREATED_BY,
3310 CREATION_DATE,
3311 LAST_UPDATED_BY,
3312 LAST_UPDATE_DATE,
3313 LAST_UPDATE_LOGIN
3314 ) SELECT TO_CHAR(PERIODICITY_ID),
3315 l_object_type,
3316 'L',
3317 p_program_id,
3318 p_user_id,
3319 p_machine,
3320 p_terminal,
3321 FND_GLOBAL.USER_ID,
3322 SYSDATE,
3323 FND_GLOBAL.USER_ID,
3324 SYSDATE,
3325 FND_GLOBAL.USER_ID
3326 FROM BSC_SYS_PERIODICITIES;
3327
3328 INSERT INTO BSC_OBJECT_LOCK_USERS (
3329 OBJECT_KEY,
3330 OBJECT_TYPE,
3331 USER_TYPE,
3332 PROGRAM_ID,
3333 USER_ID,
3334 MACHINE,
3335 TERMINAL,
3336 CREATED_BY,
3337 CREATION_DATE,
3338 LAST_UPDATED_BY,
3339 LAST_UPDATE_DATE,
3340 LAST_UPDATE_LOGIN
3341 ) SELECT TO_CHAR(PERIODICITY_ID),
3342 l_object_type,
3343 'M',
3344 p_program_id,
3345 p_user_id,
3346 p_machine,
3347 p_terminal,
3348 FND_GLOBAL.USER_ID,
3349 SYSDATE,
3350 FND_GLOBAL.USER_ID,
3351 SYSDATE,
3352 FND_GLOBAL.USER_ID
3353 FROM BSC_SYS_PERIODICITIES;
3354
3355 EXCEPTION
3356 WHEN FND_API.G_EXC_ERROR THEN
3357 x_return_status := FND_API.G_RET_STS_ERROR;
3358 FND_MSG_PUB.Count_And_Get(
3359 p_encoded => 'F'
3360 ,p_count => x_msg_count
3361 ,p_data => x_msg_data
3362 );
3363 RAISE;
3364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3366 FND_MSG_PUB.Count_And_Get(
3367 p_encoded => 'F'
3368 ,p_count => x_msg_count
3369 ,p_data => x_msg_data
3370 );
3371 RAISE;
3372 WHEN OTHERS THEN
3373 FND_MSG_PUB.Add_Exc_Msg(
3374 G_PKG_NAME,
3375 l_api_name,
3376 SQLERRM
3377 );
3378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3379 FND_MSG_PUB.Count_And_Get(
3380 p_encoded => 'F'
3381 ,p_count => x_msg_count
3382 ,p_data => x_msg_data
3383 );
3384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3385 END INSERT_LOCK_PERIODICITY;
3386
3387
3388 /*------------------------------------------------------------------------------------------
3389 Procedure INSERT_LOCK_CALENDAR
3390 This procedure inserts calendar entries into the lock table and user table
3391 <parameters>
3392 p_program_id: -100 = Data Loader UI
3393 -101 = Data Loader Backend
3394 -200 = Generate Database
3395 -201 = Generate Documentation
3396 -202 = Rename Interface Table
3397 -203 = Generate Database Configuration
3398 -300 = Administrator
3399 -400 = Objective Designer
3400 -500 = Builder
3401 -600 = Performance Scorecard
3402 -700 = System Upgrade
3403 -800 = System Migration
3404 p_user_id: Application User ID
3405 p_machine: The Machine
3406 p_terminal: The Terminal
3407 -------------------------------------------------------------------------------------------*/
3408 Procedure INSERT_LOCK_CALENDAR(
3409 p_program_id IN number
3410 ,p_user_id IN number
3411 ,p_machine IN varchar2
3412 ,p_terminal IN varchar2
3413 ,x_return_status OUT NOCOPY varchar2
3414 ,x_msg_count OUT NOCOPY number
3415 ,x_msg_data OUT NOCOPY varchar2
3416 ) IS
3417
3418 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_CALENDAR';
3419 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
3420 l_session_id NUMBER := USERENV('SESSIONID');
3421
3422 BEGIN
3423 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_CALENDAR');
3424 x_return_status := FND_API.G_RET_STS_SUCCESS;
3425 l_object_type := 'CALENDAR';
3426
3427 -- Insert the ALL entries
3428 BSC_LOCKS_PVT.INSERT_LOCK_ALL
3429 (
3430 p_object_type => l_object_type
3431 ,p_program_id => p_program_id
3432 ,p_user_id => p_user_id
3433 ,p_machine => p_machine
3434 ,p_terminal => p_terminal
3435 ,p_session_id => l_session_id
3436 ,x_return_status => x_return_status
3437 ,x_msg_count => x_msg_count
3438 ,x_msg_data => x_msg_data
3439 );
3440
3441 -- Insert Calendar entries
3442 INSERT INTO BSC_OBJECT_LOCKS (
3443 OBJECT_KEY,
3444 OBJECT_TYPE,
3445 LOCK_TYPE,
3446 LAST_SAVE_TIME,
3447 CREATED_BY,
3448 CREATION_DATE,
3449 LAST_UPDATED_BY,
3450 LAST_UPDATE_DATE,
3451 LAST_UPDATE_LOGIN,
3452 SESSION_ID
3453 ) SELECT TO_CHAR(CALENDAR_ID),
3454 l_object_type,
3455 'W',
3456 SYSDATE,
3457 FND_GLOBAL.USER_ID,
3458 SYSDATE,
3459 FND_GLOBAL.USER_ID,
3460 SYSDATE,
3461 FND_GLOBAL.USER_ID,
3462 l_session_id
3463 FROM BSC_SYS_CALENDARS_B;
3464
3465 INSERT INTO BSC_OBJECT_LOCK_USERS (
3466 OBJECT_KEY,
3467 OBJECT_TYPE,
3468 USER_TYPE,
3469 PROGRAM_ID,
3470 USER_ID,
3471 MACHINE,
3472 TERMINAL,
3473 CREATED_BY,
3474 CREATION_DATE,
3475 LAST_UPDATED_BY,
3476 LAST_UPDATE_DATE,
3477 LAST_UPDATE_LOGIN
3478 ) SELECT TO_CHAR(CALENDAR_ID),
3479 l_object_type,
3480 'L',
3481 p_program_id,
3482 p_user_id,
3483 p_machine,
3484 p_terminal,
3485 FND_GLOBAL.USER_ID,
3486 SYSDATE,
3487 FND_GLOBAL.USER_ID,
3488 SYSDATE,
3489 FND_GLOBAL.USER_ID
3490 FROM BSC_SYS_CALENDARS_B;
3491
3492 INSERT INTO BSC_OBJECT_LOCK_USERS (
3493 OBJECT_KEY,
3494 OBJECT_TYPE,
3495 USER_TYPE,
3496 PROGRAM_ID,
3497 USER_ID,
3498 MACHINE,
3499 TERMINAL,
3500 CREATED_BY,
3501 CREATION_DATE,
3502 LAST_UPDATED_BY,
3503 LAST_UPDATE_DATE,
3504 LAST_UPDATE_LOGIN
3505 ) SELECT TO_CHAR(CALENDAR_ID),
3506 l_object_type,
3507 'M',
3508 p_program_id,
3509 p_user_id,
3510 p_machine,
3511 p_terminal,
3512 FND_GLOBAL.USER_ID,
3513 SYSDATE,
3514 FND_GLOBAL.USER_ID,
3515 SYSDATE,
3516 FND_GLOBAL.USER_ID
3517 FROM BSC_SYS_CALENDARS_B;
3518
3519 EXCEPTION
3520 WHEN FND_API.G_EXC_ERROR THEN
3521 x_return_status := FND_API.G_RET_STS_ERROR;
3522 FND_MSG_PUB.Count_And_Get(
3523 p_encoded => 'F'
3524 ,p_count => x_msg_count
3525 ,p_data => x_msg_data
3526 );
3527 RAISE;
3528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3530 FND_MSG_PUB.Count_And_Get(
3531 p_encoded => 'F'
3532 ,p_count => x_msg_count
3533 ,p_data => x_msg_data
3534 );
3535 RAISE;
3536 WHEN OTHERS THEN
3537 FND_MSG_PUB.Add_Exc_Msg(
3538 G_PKG_NAME,
3539 l_api_name,
3540 SQLERRM
3541 );
3542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3543 FND_MSG_PUB.Count_And_Get(
3544 p_encoded => 'F'
3545 ,p_count => x_msg_count
3546 ,p_data => x_msg_data
3547 );
3548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3549 END INSERT_LOCK_CALENDAR;
3550
3551
3552 /*------------------------------------------------------------------------------------------
3553 Procedure INSERT_LOCK_TABLE
3554 This procedure inserts table entries into the lock table and user table
3555 <parameters>
3556 p_program_id: -100 = Data Loader UI
3557 -101 = Data Loader Backend
3558 -200 = Generate Database
3559 -201 = Generate Documentation
3560 -202 = Rename Interface Table
3561 -203 = Generate Database Configuration
3562 -300 = Administrator
3563 -400 = Objective Designer
3564 -500 = Builder
3565 -600 = Performance Scorecard
3566 -700 = System Upgrade
3567 -800 = System Migration
3568 p_user_id: Application User ID
3569 p_machine: The Machine
3570 p_terminal: The Terminal
3571 -------------------------------------------------------------------------------------------*/
3572 Procedure INSERT_LOCK_TABLE(
3573 p_program_id IN number
3574 ,p_user_id IN number
3575 ,p_machine IN varchar2
3576 ,p_terminal IN varchar2
3577 ,x_return_status OUT NOCOPY varchar2
3578 ,x_msg_count OUT NOCOPY number
3579 ,x_msg_data OUT NOCOPY varchar2
3580 ) IS
3581
3582 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_TABLE';
3583 l_object_type BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE;
3584 l_session_id NUMBER := USERENV('SESSIONID');
3585
3586 BEGIN
3587 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_TABLE');
3588 x_return_status := FND_API.G_RET_STS_SUCCESS;
3589 l_object_type := 'TABLE';
3590
3591 -- Insert the ALL entries
3592 BSC_LOCKS_PVT.INSERT_LOCK_ALL
3593 (
3594 p_object_type => l_object_type
3595 ,p_program_id => p_program_id
3596 ,p_user_id => p_user_id
3597 ,p_machine => p_machine
3598 ,p_terminal => p_terminal
3599 ,p_session_id => l_session_id
3600 ,x_return_status => x_return_status
3601 ,x_msg_count => x_msg_count
3602 ,x_msg_data => x_msg_data
3603 );
3604
3605 -- Insert Table entries
3606 INSERT INTO BSC_OBJECT_LOCKS (
3607 OBJECT_KEY,
3608 OBJECT_TYPE,
3609 LOCK_TYPE,
3610 LAST_SAVE_TIME,
3611 CREATED_BY,
3612 CREATION_DATE,
3613 LAST_UPDATED_BY,
3614 LAST_UPDATE_DATE,
3615 LAST_UPDATE_LOGIN,
3616 SESSION_ID
3617 ) SELECT TABLE_NAME,
3618 l_object_type,
3619 'W',
3620 SYSDATE,
3621 FND_GLOBAL.USER_ID,
3622 SYSDATE,
3623 FND_GLOBAL.USER_ID,
3624 SYSDATE,
3625 FND_GLOBAL.USER_ID,
3626 l_session_id
3627 FROM BSC_DB_TABLES;
3628
3629 INSERT INTO BSC_OBJECT_LOCK_USERS (
3630 OBJECT_KEY,
3631 OBJECT_TYPE,
3632 USER_TYPE,
3633 PROGRAM_ID,
3634 USER_ID,
3635 MACHINE,
3636 TERMINAL,
3637 CREATED_BY,
3638 CREATION_DATE,
3639 LAST_UPDATED_BY,
3640 LAST_UPDATE_DATE,
3641 LAST_UPDATE_LOGIN
3642 ) SELECT TABLE_NAME,
3643 l_object_type,
3644 'L',
3645 p_program_id,
3646 p_user_id,
3647 p_machine,
3648 p_terminal,
3649 FND_GLOBAL.USER_ID,
3650 SYSDATE,
3651 FND_GLOBAL.USER_ID,
3652 SYSDATE,
3653 FND_GLOBAL.USER_ID
3654 FROM BSC_DB_TABLES;
3655
3656 INSERT INTO BSC_OBJECT_LOCK_USERS (
3657 OBJECT_KEY,
3658 OBJECT_TYPE,
3659 USER_TYPE,
3660 PROGRAM_ID,
3661 USER_ID,
3662 MACHINE,
3663 TERMINAL,
3664 CREATED_BY,
3665 CREATION_DATE,
3666 LAST_UPDATED_BY,
3667 LAST_UPDATE_DATE,
3668 LAST_UPDATE_LOGIN
3669 ) SELECT TABLE_NAME,
3670 l_object_type,
3671 'M',
3672 p_program_id,
3673 p_user_id,
3674 p_machine,
3675 p_terminal,
3676 FND_GLOBAL.USER_ID,
3677 SYSDATE,
3678 FND_GLOBAL.USER_ID,
3679 SYSDATE,
3680 FND_GLOBAL.USER_ID
3681 FROM BSC_DB_TABLES;
3682
3683 EXCEPTION
3684 WHEN FND_API.G_EXC_ERROR THEN
3685 x_return_status := FND_API.G_RET_STS_ERROR;
3686 FND_MSG_PUB.Count_And_Get(
3687 p_encoded => 'F'
3688 ,p_count => x_msg_count
3689 ,p_data => x_msg_data
3690 );
3691 RAISE;
3692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3694 FND_MSG_PUB.Count_And_Get(
3695 p_encoded => 'F'
3696 ,p_count => x_msg_count
3697 ,p_data => x_msg_data
3698 );
3699 RAISE;
3700 WHEN OTHERS THEN
3701 FND_MSG_PUB.Add_Exc_Msg(
3702 G_PKG_NAME,
3703 l_api_name,
3704 SQLERRM
3705 );
3706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3707 FND_MSG_PUB.Count_And_Get(
3708 p_encoded => 'F'
3709 ,p_count => x_msg_count
3710 ,p_data => x_msg_data
3711 );
3712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3713 END INSERT_LOCK_TABLE;
3714
3715
3716 /*------------------------------------------------------------------------------------------
3717 Procedure INSERT_LOCK_INFO
3718 This procedure inserts a row in the lock table
3719 <parameters>
3720 p_object_key: The primary key of the Object, usually the TO_CHAR value
3721 of the Object ID. If the Object has composite keys,
3722 the value to pass in will be a concatenation of
3723 all the keys, separated by commas
3724 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
3725 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
3726 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
3727 "PERIODICITY", and "TABLE"
3728 p_lock_type: 'W' for write lock, 'R' for read lock
3729 p_last_save_time: The last time the Object being modified
3730 p_session_id: The Database Session ID
3731 -------------------------------------------------------------------------------------------*/
3732 Procedure INSERT_LOCK_INFO(
3733 p_object_key IN varchar2
3734 ,p_object_type IN varchar2
3735 ,p_lock_type IN varchar2
3736 ,p_last_save_time IN date
3737 ,p_session_id IN number
3738 ,x_return_status OUT NOCOPY varchar2
3739 ,x_msg_count OUT NOCOPY number
3740 ,x_msg_data OUT NOCOPY varchar2
3741 ) IS
3742
3743 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_INFO';
3744
3745 BEGIN
3746 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_INFO');
3747 x_return_status := FND_API.G_RET_STS_SUCCESS;
3748
3749 INSERT INTO BSC_OBJECT_LOCKS (
3750 OBJECT_KEY,
3751 OBJECT_TYPE,
3752 LOCK_TYPE,
3753 LAST_SAVE_TIME,
3754 CREATED_BY,
3755 CREATION_DATE,
3756 LAST_UPDATED_BY,
3757 LAST_UPDATE_DATE,
3758 LAST_UPDATE_LOGIN,
3759 SESSION_ID
3760 ) VALUES (
3761 p_object_key,
3762 p_object_type,
3763 p_lock_type,
3764 p_last_save_time,
3765 FND_GLOBAL.USER_ID,
3766 SYSDATE,
3767 FND_GLOBAL.USER_ID,
3768 SYSDATE,
3769 FND_GLOBAL.USER_ID,
3770 p_session_id
3771 );
3772
3773 EXCEPTION
3774 WHEN FND_API.G_EXC_ERROR THEN
3775 x_return_status := FND_API.G_RET_STS_ERROR;
3776 FND_MSG_PUB.Count_And_Get(
3777 p_encoded => 'F'
3778 ,p_count => x_msg_count
3779 ,p_data => x_msg_data
3780 );
3781 RAISE;
3782 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3784 FND_MSG_PUB.Count_And_Get(
3785 p_encoded => 'F'
3786 ,p_count => x_msg_count
3787 ,p_data => x_msg_data
3788 );
3789 RAISE;
3790 WHEN OTHERS THEN
3791 FND_MSG_PUB.Add_Exc_Msg(
3792 G_PKG_NAME,
3793 l_api_name,
3794 SQLERRM
3795 );
3796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3797 FND_MSG_PUB.Count_And_Get(
3798 p_encoded => 'F'
3799 ,p_count => x_msg_count
3800 ,p_data => x_msg_data
3801 );
3802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3803 END INSERT_LOCK_INFO;
3804
3805
3806 /*------------------------------------------------------------------------------------------
3807 Procedure INSERT_LOCK_INFO_AUTONOMOUS
3808 This procedure inserts a row in the lock table
3809 <parameters>
3810 p_object_key: The primary key of the Object, usually the TO_CHAR value
3811 of the Object ID. If the Object has composite keys,
3812 the value to pass in will be a concatenation of
3813 all the keys, separated by commas
3814 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
3815 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
3816 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
3817 "PERIODICITY", and "TABLE"
3818 p_lock_type: 'W' for write lock, 'R' for read lock
3819 p_last_save_time: The last time the Object being modified
3820 p_session_id: The Database Session ID
3821 -------------------------------------------------------------------------------------------*/
3822 Procedure INSERT_LOCK_INFO_AUTONOMOUS(
3823 p_object_key IN varchar2
3824 ,p_object_type IN varchar2
3825 ,p_lock_type IN varchar2
3826 ,p_last_save_time IN date
3827 ,p_session_id IN number
3828 ,x_return_status OUT NOCOPY varchar2
3829 ,x_msg_count OUT NOCOPY number
3830 ,x_msg_data OUT NOCOPY varchar2
3831 ) IS
3832 PRAGMA AUTONOMOUS_TRANSACTION;
3833
3834 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LOCK_INFO_AUTONOMOUS';
3835
3836 BEGIN
3837 --DBMS_OUTPUT.PUT_LINE('INSERT_LOCK_INFO_AUTONOMOUS');
3838 x_return_status := FND_API.G_RET_STS_SUCCESS;
3839
3840 BSC_LOCKS_PVT.INSERT_LOCK_INFO
3841 (
3842 p_object_key => p_object_key
3843 ,p_object_type => p_object_type
3844 ,p_lock_type => p_lock_type
3845 ,p_last_save_time => p_last_save_time
3846 ,p_session_id => p_session_id
3847 ,x_return_status => x_return_status
3848 ,x_msg_count => x_msg_count
3849 ,x_msg_data => x_msg_data
3850 );
3851 COMMIT;
3852
3853 EXCEPTION
3854 WHEN FND_API.G_EXC_ERROR THEN
3855 ROLLBACK;
3856 x_return_status := FND_API.G_RET_STS_ERROR;
3857 FND_MSG_PUB.Count_And_Get(
3858 p_encoded => 'F'
3859 ,p_count => x_msg_count
3860 ,p_data => x_msg_data
3861 );
3862 RAISE;
3863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3864 ROLLBACK;
3865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3866 FND_MSG_PUB.Count_And_Get(
3867 p_encoded => 'F'
3868 ,p_count => x_msg_count
3869 ,p_data => x_msg_data
3870 );
3871 RAISE;
3872 WHEN OTHERS THEN
3873 ROLLBACK;
3874 FND_MSG_PUB.Add_Exc_Msg(
3875 G_PKG_NAME,
3876 l_api_name,
3877 SQLERRM
3878 );
3879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3880 FND_MSG_PUB.Count_And_Get(
3881 p_encoded => 'F'
3882 ,p_count => x_msg_count
3883 ,p_data => x_msg_data
3884 );
3885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3886 END INSERT_LOCK_INFO_AUTONOMOUS;
3887
3888
3889 /*------------------------------------------------------------------------------------------
3890 Procedure UPDATE_LOCK_INFO
3891 This procedure updates the lock table
3892 <parameters>
3893 p_object_key: The primary key of the Object, usually the TO_CHAR value
3894 of the Object ID. If the Object has composite keys,
3895 the value to pass in will be a concatenation of
3896 all the keys, separated by commas
3897 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
3898 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
3899 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
3900 "PERIODICITY", and "TABLE"
3901 p_lock_type: 'W' for write lock, 'R' for read lock
3902 p_last_save_time: The last time the Object being modified
3903 p_session_id: The Database Session ID
3904 -------------------------------------------------------------------------------------------*/
3905 Procedure UPDATE_LOCK_INFO(
3906 p_object_key IN varchar2
3907 ,p_object_type IN varchar2
3908 ,p_lock_type IN varchar2
3909 ,p_last_save_time IN date
3910 ,p_session_id IN number
3911 ,x_return_status OUT NOCOPY varchar2
3912 ,x_msg_count OUT NOCOPY number
3913 ,x_msg_data OUT NOCOPY varchar2
3914 ) IS
3915
3916 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOCK_INFO';
3917
3918 BEGIN
3919 --DBMS_OUTPUT.PUT_LINE('UPDATE_LOCK_INFO');
3920 x_return_status := FND_API.G_RET_STS_SUCCESS;
3921
3922 IF (p_object_key = 'ALL' AND p_object_type = 'ALL') THEN
3923 UPDATE BSC_OBJECT_LOCKS
3924 SET LAST_SAVE_TIME = p_last_save_time,
3925 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3926 LAST_UPDATE_DATE = SYSDATE,
3927 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
3928 SESSION_ID = p_session_id
3929 WHERE p_lock_type = 'ALL'
3930 OR LOCK_TYPE = p_lock_type;
3931
3932 ELSE
3933 UPDATE BSC_OBJECT_LOCKS
3934 SET LAST_SAVE_TIME = p_last_save_time,
3935 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3936 LAST_UPDATE_DATE = SYSDATE,
3937 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
3938 SESSION_ID = p_session_id
3939 WHERE OBJECT_TYPE = p_object_type
3940 AND OBJECT_KEY = p_object_key
3941 AND LOCK_TYPE = p_lock_type;
3942
3943 IF (SQL%NOTFOUND) THEN
3944 BSC_LOCKS_PVT.INSERT_LOCK_INFO
3945 (
3946 p_object_key => p_object_key
3947 ,p_object_type => p_object_type
3948 ,p_lock_type => p_lock_type
3949 ,p_last_save_time => p_last_save_time
3950 ,p_session_id => p_session_id
3951 ,x_return_status => x_return_status
3952 ,x_msg_count => x_msg_count
3953 ,x_msg_data => x_msg_data
3954 );
3955 END IF;
3956 END IF;
3957
3958 EXCEPTION
3959 WHEN FND_API.G_EXC_ERROR THEN
3960 x_return_status := FND_API.G_RET_STS_ERROR;
3961 FND_MSG_PUB.Count_And_Get(
3962 p_encoded => 'F'
3963 ,p_count => x_msg_count
3964 ,p_data => x_msg_data
3965 );
3966 RAISE;
3967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3969 FND_MSG_PUB.Count_And_Get(
3970 p_encoded => 'F'
3971 ,p_count => x_msg_count
3972 ,p_data => x_msg_data
3973 );
3974 RAISE;
3975 WHEN OTHERS THEN
3976 FND_MSG_PUB.Add_Exc_Msg(
3977 G_PKG_NAME,
3978 l_api_name,
3979 SQLERRM
3980 );
3981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3982 FND_MSG_PUB.Count_And_Get(
3983 p_encoded => 'F'
3984 ,p_count => x_msg_count
3985 ,p_data => x_msg_data
3986 );
3987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3988 END UPDATE_LOCK_INFO;
3989
3990
3991 /*------------------------------------------------------------------------------------------
3992 Procedure UPDATE_LOCK_INFO_AUTONOMOUS
3993 This procedure updates the lock table
3994 <parameters>
3995 p_object_key: The primary key of the Object, usually the TO_CHAR value
3996 of the Object ID. If the Object has composite keys,
3997 the value to pass in will be a concatenation of
3998 all the keys, separated by commas
3999 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4000 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4001 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4002 "PERIODICITY", and "TABLE"
4003 p_lock_type: 'W' for write lock, 'R' for read lock
4004 p_last_save_time: The last time the Object being modified
4005 p_session_id: The Database Session ID
4006 -------------------------------------------------------------------------------------------*/
4007 Procedure UPDATE_LOCK_INFO_AUTONOMOUS(
4008 p_object_key IN varchar2
4009 ,p_object_type IN varchar2
4010 ,p_lock_type IN varchar2
4011 ,p_last_save_time IN date
4012 ,p_session_id IN number
4013 ,x_return_status OUT NOCOPY varchar2
4014 ,x_msg_count OUT NOCOPY number
4015 ,x_msg_data OUT NOCOPY varchar2
4016 ) IS
4017 PRAGMA AUTONOMOUS_TRANSACTION;
4018
4019 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOCK_INFO_AUTONOMOUS';
4020
4021 BEGIN
4022 --DBMS_OUTPUT.PUT_LINE('UPDATE_LOCK_INFO_AUTONOMOUS');
4023 x_return_status := FND_API.G_RET_STS_SUCCESS;
4024
4025 BSC_LOCKS_PVT.UPDATE_LOCK_INFO
4026 (
4027 p_object_key => p_object_key
4028 ,p_object_type => p_object_type
4029 ,p_lock_type => p_lock_type
4030 ,p_last_save_time => p_last_save_time
4031 ,p_session_id => p_session_id
4032 ,x_return_status => x_return_status
4033 ,x_msg_count => x_msg_count
4034 ,x_msg_data => x_msg_data
4035 );
4036 COMMIT;
4037
4038 EXCEPTION
4039 WHEN FND_API.G_EXC_ERROR THEN
4040 ROLLBACK;
4041 x_return_status := FND_API.G_RET_STS_ERROR;
4042 FND_MSG_PUB.Count_And_Get(
4043 p_encoded => 'F'
4044 ,p_count => x_msg_count
4045 ,p_data => x_msg_data
4046 );
4047 RAISE;
4048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4049 ROLLBACK;
4050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4051 FND_MSG_PUB.Count_And_Get(
4052 p_encoded => 'F'
4053 ,p_count => x_msg_count
4054 ,p_data => x_msg_data
4055 );
4056 RAISE;
4057 WHEN OTHERS THEN
4058 ROLLBACK;
4059 FND_MSG_PUB.Add_Exc_Msg(
4060 G_PKG_NAME,
4061 l_api_name,
4062 SQLERRM
4063 );
4064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4065 FND_MSG_PUB.Count_And_Get(
4066 p_encoded => 'F'
4067 ,p_count => x_msg_count
4068 ,p_data => x_msg_data
4069 );
4070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4071 END UPDATE_LOCK_INFO_AUTONOMOUS;
4072
4073
4074 /*------------------------------------------------------------------------------------------
4075 Procedure INSERT_USER_INFO
4076 This procedure inserts a row into the lock user table
4077 <parameters>
4078 p_object_key: The primary key of the Object, usually the TO_CHAR value
4079 of the Object ID. If the Object has composite keys,
4080 the value to pass in will be a concatenation of
4081 all the keys, separated by commas
4082 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4083 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4084 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4085 "PERIODICITY", and "TABLE"
4086 p_user_type: "L" = Lock, "M" = Modify
4087 p_program_id: -100 = Data Loader UI
4088 -101 = Data Loader Backend
4089 -200 = Generate Database
4090 -201 = Generate Documentation
4091 -202 = Rename Interface Table
4092 -203 = Generate Database Configuration
4093 -300 = Administrator
4094 -400 = Objective Designer
4095 -500 = Builder
4096 -600 = Performance Scorecard
4097 -700 = System Upgrade
4098 -800 = System Migration
4099 p_user_id: Application User ID
4100 p_machine: The Machine
4101 p_terminal: The Terminal
4102 -------------------------------------------------------------------------------------------*/
4103 Procedure INSERT_USER_INFO(
4104 p_object_key IN varchar2
4105 ,p_object_type IN varchar2
4106 ,p_user_type IN varchar2
4107 ,p_program_id IN number
4108 ,p_user_id IN number
4109 ,p_machine IN varchar2
4110 ,p_terminal IN varchar2
4111 ,x_return_status OUT NOCOPY varchar2
4112 ,x_msg_count OUT NOCOPY number
4113 ,x_msg_data OUT NOCOPY varchar2
4114 ) IS
4115
4116 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_USER_INFO';
4117
4118 BEGIN
4119 --DBMS_OUTPUT.PUT_LINE('INSERT_USER_INFO');
4120 x_return_status := FND_API.G_RET_STS_SUCCESS;
4121
4122 INSERT INTO BSC_OBJECT_LOCK_USERS (
4123 OBJECT_KEY,
4124 OBJECT_TYPE,
4125 USER_TYPE,
4126 PROGRAM_ID,
4127 USER_ID,
4128 MACHINE,
4129 TERMINAL,
4130 CREATED_BY,
4131 CREATION_DATE,
4132 LAST_UPDATED_BY,
4133 LAST_UPDATE_DATE,
4134 LAST_UPDATE_LOGIN
4135 ) VALUES (
4136 p_object_key,
4137 p_object_type,
4138 p_user_type,
4139 p_program_id,
4140 p_user_id,
4141 p_machine,
4142 p_terminal,
4143 FND_GLOBAL.USER_ID,
4144 SYSDATE,
4145 FND_GLOBAL.USER_ID,
4146 SYSDATE,
4147 FND_GLOBAL.USER_ID
4148 );
4149
4150 EXCEPTION
4151 WHEN FND_API.G_EXC_ERROR THEN
4152 x_return_status := FND_API.G_RET_STS_ERROR;
4153 FND_MSG_PUB.Count_And_Get(
4154 p_encoded => 'F'
4155 ,p_count => x_msg_count
4156 ,p_data => x_msg_data
4157 );
4158 RAISE;
4159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4161 FND_MSG_PUB.Count_And_Get(
4162 p_encoded => 'F'
4163 ,p_count => x_msg_count
4164 ,p_data => x_msg_data
4165 );
4166 RAISE;
4167 WHEN OTHERS THEN
4168 FND_MSG_PUB.Add_Exc_Msg(
4169 G_PKG_NAME,
4170 l_api_name,
4171 SQLERRM
4172 );
4173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4174 FND_MSG_PUB.Count_And_Get(
4175 p_encoded => 'F'
4176 ,p_count => x_msg_count
4177 ,p_data => x_msg_data
4178 );
4179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4180 END INSERT_USER_INFO;
4181
4182
4183 /*------------------------------------------------------------------------------------------
4184 Procedure INSERT_USER_INFO_AUTONOMOUS
4185 This procedure inserts a row into the lock user table
4186 <parameters>
4187 p_object_key: The primary key of the Object, usually the TO_CHAR value
4188 of the Object ID. If the Object has composite keys,
4189 the value to pass in will be a concatenation of
4190 all the keys, separated by commas
4191 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4192 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4193 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4194 "PERIODICITY", and "TABLE"
4195 p_user_type: "L" = Lock, "M" = Modify
4196 p_program_id: -100 = Data Loader UI
4197 -101 = Data Loader Backend
4198 -200 = Generate Database
4199 -201 = Generate Documentation
4200 -202 = Rename Interface Table
4201 -203 = Generate Database Configuration
4202 -300 = Administrator
4203 -400 = Objective Designer
4204 -500 = Builder
4205 -600 = Performance Scorecard
4206 -700 = System Upgrade
4207 -800 = System Migration
4208 p_user_id: Application User ID
4209 p_machine: The Machine
4210 p_terminal: The Terminal
4211 -------------------------------------------------------------------------------------------*/
4212 Procedure INSERT_USER_INFO_AUTONOMOUS(
4213 p_object_key IN varchar2
4214 ,p_object_type IN varchar2
4215 ,p_user_type IN varchar2
4216 ,p_program_id IN number
4217 ,p_user_id IN number
4218 ,p_machine IN varchar2
4219 ,p_terminal IN varchar2
4220 ,x_return_status OUT NOCOPY varchar2
4221 ,x_msg_count OUT NOCOPY number
4222 ,x_msg_data OUT NOCOPY varchar2
4223 ) IS
4224 PRAGMA AUTONOMOUS_TRANSACTION;
4225
4226 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_USER_INFO_AUTONOMOUS';
4227
4228 BEGIN
4229 --DBMS_OUTPUT.PUT_LINE('INSERT_USER_INFO_AUTONOMOUS');
4230 x_return_status := FND_API.G_RET_STS_SUCCESS;
4231
4232 BSC_LOCKS_PVT.INSERT_USER_INFO
4233 (
4234 p_object_key => p_object_key
4235 ,p_object_type => p_object_type
4236 ,p_user_type => p_user_type
4237 ,p_program_id => p_program_id
4238 ,p_user_id => p_user_id
4239 ,p_machine => p_machine
4240 ,p_terminal => p_terminal
4241 ,x_return_status => x_return_status
4242 ,x_msg_count => x_msg_count
4243 ,x_msg_data => x_msg_data
4244 );
4245 COMMIT;
4246
4247 EXCEPTION
4248 WHEN FND_API.G_EXC_ERROR THEN
4249 ROLLBACK;
4250 x_return_status := FND_API.G_RET_STS_ERROR;
4251 FND_MSG_PUB.Count_And_Get(
4252 p_encoded => 'F'
4253 ,p_count => x_msg_count
4254 ,p_data => x_msg_data
4255 );
4256 RAISE;
4257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4258 ROLLBACK;
4259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4260 FND_MSG_PUB.Count_And_Get(
4261 p_encoded => 'F'
4262 ,p_count => x_msg_count
4263 ,p_data => x_msg_data
4264 );
4265 RAISE;
4266 WHEN OTHERS THEN
4267 ROLLBACK;
4268 FND_MSG_PUB.Add_Exc_Msg(
4269 G_PKG_NAME,
4270 l_api_name,
4271 SQLERRM
4272 );
4273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4274 FND_MSG_PUB.Count_And_Get(
4275 p_encoded => 'F'
4276 ,p_count => x_msg_count
4277 ,p_data => x_msg_data
4278 );
4279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4280 END INSERT_USER_INFO_AUTONOMOUS;
4281
4282
4283 /*------------------------------------------------------------------------------------------
4284 Procedure UPDATE_USER_INFO
4285 This procedure updates the current user info
4286 <parameters>
4287 p_object_key: The primary key of the Object, usually the TO_CHAR value
4288 of the Object ID. If the Object has composite keys,
4289 the value to pass in will be a concatenation of
4290 all the keys, separated by commas
4291 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4292 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4293 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4294 "PERIODICITY", and "TABLE"
4295 p_user_type: "L" = Lock, "M" = Modify
4296 p_program_id: -100 = Data Loader UI
4297 -101 = Data Loader Backend
4298 -200 = Generate Database
4299 -201 = Generate Documentation
4300 -202 = Rename Interface Table
4301 -203 = Generate Database Configuration
4302 -300 = Administrator
4303 -400 = Objective Designer
4304 -500 = Builder
4305 -600 = Performance Scorecard
4306 -700 = System Upgrade
4307 -800 = System Migration
4308 p_user_id: Application User ID
4309 p_machine: The Machine
4310 p_terminal: The Terminal
4311 -------------------------------------------------------------------------------------------*/
4312 Procedure UPDATE_USER_INFO(
4313 p_object_key IN varchar2
4314 ,p_object_type IN varchar2
4315 ,p_user_type IN varchar2
4316 ,p_program_id IN number
4317 ,p_user_id IN number
4318 ,p_machine IN varchar2
4319 ,p_terminal IN varchar2
4320 ,x_return_status OUT NOCOPY varchar2
4321 ,x_msg_count OUT NOCOPY number
4322 ,x_msg_data OUT NOCOPY varchar2
4323 ) IS
4324
4325 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_USER_INFO';
4326
4327 BEGIN
4328 --DBMS_OUTPUT.PUT_LINE('UPDATE_USER_INFO');
4329 x_return_status := FND_API.G_RET_STS_SUCCESS;
4330
4331 IF (p_object_key = 'ALL' AND p_object_type = 'ALL') THEN
4332 UPDATE BSC_OBJECT_LOCK_USERS
4333 SET PROGRAM_ID = p_program_id,
4334 USER_ID = p_user_id,
4335 MACHINE = p_machine,
4336 TERMINAL = p_terminal,
4337 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4338 LAST_UPDATE_DATE = SYSDATE,
4339 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
4340 WHERE p_user_type = 'ALL'
4341 OR USER_TYPE = p_user_type;
4342
4343 ELSE
4344 UPDATE BSC_OBJECT_LOCK_USERS
4345 SET PROGRAM_ID = p_program_id,
4346 USER_ID = p_user_id,
4347 MACHINE = p_machine,
4348 TERMINAL = p_terminal,
4349 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4350 LAST_UPDATE_DATE = SYSDATE,
4351 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
4352 WHERE OBJECT_TYPE = p_object_type
4353 AND OBJECT_KEY = p_object_key
4354 AND USER_TYPE = p_user_type;
4355
4356 IF (SQL%NOTFOUND) THEN
4357 BSC_LOCKS_PVT.INSERT_USER_INFO_AUTONOMOUS
4358 (
4359 p_object_key => p_object_key
4360 ,p_object_type => p_object_type
4361 ,p_user_type => p_user_type
4362 ,p_program_id => p_program_id
4363 ,p_user_id => p_user_id
4364 ,p_machine => p_machine
4365 ,p_terminal => p_terminal
4366 ,x_return_status => x_return_status
4367 ,x_msg_count => x_msg_count
4368 ,x_msg_data => x_msg_data
4369 );
4370 BSC_LOCKS_PVT.LOCK_USER
4371 (
4372 p_object_key => p_object_key
4373 ,p_object_type => p_object_type
4374 ,p_user_type => p_user_type
4375 ,p_program_id => p_program_id
4376 ,p_user_id => p_user_id
4377 ,p_machine => p_machine
4378 ,p_terminal => p_terminal
4379 ,x_return_status => x_return_status
4380 ,x_msg_count => x_msg_count
4381 ,x_msg_data => x_msg_data
4382 );
4383 END IF;
4384
4385 IF (p_user_type = 'L') THEN
4386 UPDATE BSC_OBJECT_LOCK_USERS
4387 SET PROGRAM_ID = p_program_id,
4388 USER_ID = p_user_id,
4389 MACHINE = p_machine,
4390 TERMINAL = p_terminal,
4391 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4392 LAST_UPDATE_DATE = SYSDATE,
4393 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
4394 WHERE OBJECT_TYPE = p_object_type
4395 AND OBJECT_KEY = p_object_key
4396 AND SUBSTR(USER_TYPE,1,1) = 'L';
4397 END IF;
4398 END IF;
4399
4400 EXCEPTION
4401 WHEN FND_API.G_EXC_ERROR THEN
4402 x_return_status := FND_API.G_RET_STS_ERROR;
4403 FND_MSG_PUB.Count_And_Get(
4404 p_encoded => 'F'
4405 ,p_count => x_msg_count
4406 ,p_data => x_msg_data
4407 );
4408 RAISE;
4409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4411 FND_MSG_PUB.Count_And_Get(
4412 p_encoded => 'F'
4413 ,p_count => x_msg_count
4414 ,p_data => x_msg_data
4415 );
4416 RAISE;
4417 WHEN OTHERS THEN
4418 FND_MSG_PUB.Add_Exc_Msg(
4419 G_PKG_NAME,
4420 l_api_name,
4421 SQLERRM
4422 );
4423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4424 FND_MSG_PUB.Count_And_Get(
4425 p_encoded => 'F'
4426 ,p_count => x_msg_count
4427 ,p_data => x_msg_data
4428 );
4429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4430 END UPDATE_USER_INFO;
4431
4432
4433 /*------------------------------------------------------------------------------------------
4434 Procedure UPDATE_USER_INFO_AUTONOMOUS
4435 This procedure updates the current user info
4436 <parameters>
4437 p_object_key: The primary key of the Object, usually the TO_CHAR value
4438 of the Object ID. If the Object has composite keys,
4439 the value to pass in will be a concatenation of
4440 all the keys, separated by commas
4441 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4442 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4443 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4444 "PERIODICITY", and "TABLE"
4445 p_user_type: "L" = Lock, "M" = Modify
4446 p_program_id: -100 = Data Loader UI
4447 -101 = Data Loader Backend
4448 -200 = Generate Database
4449 -201 = Generate Documentation
4450 -202 = Rename Interface Table
4451 -203 = Generate Database Configuration
4452 -300 = Administrator
4453 -400 = Objective Designer
4454 -500 = Builder
4455 -600 = Performance Scorecard
4456 -700 = System Upgrade
4457 -800 = System Migration
4458 p_user_id: Application User ID
4459 p_machine: The Machine
4460 p_terminal: The Terminal
4461 -------------------------------------------------------------------------------------------*/
4462 Procedure UPDATE_USER_INFO_AUTONOMOUS(
4463 p_object_key IN varchar2
4464 ,p_object_type IN varchar2
4465 ,p_user_type IN varchar2
4466 ,p_program_id IN number
4467 ,p_user_id IN number
4468 ,p_machine IN varchar2
4469 ,p_terminal IN varchar2
4470 ,x_return_status OUT NOCOPY varchar2
4471 ,x_msg_count OUT NOCOPY number
4472 ,x_msg_data OUT NOCOPY varchar2
4473 ) IS
4474 PRAGMA AUTONOMOUS_TRANSACTION;
4475
4476 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_USER_INFO_AUTONOMOUS';
4477
4478 BEGIN
4479 --DBMS_OUTPUT.PUT_LINE('UPDATE_USER_INFO_AUTONOMOUS');
4480 x_return_status := FND_API.G_RET_STS_SUCCESS;
4481
4482 BSC_LOCKS_PVT.UPDATE_USER_INFO
4483 (
4484 p_object_key => p_object_key
4485 ,p_object_type => p_object_type
4486 ,p_user_type => p_user_type
4487 ,p_program_id => p_program_id
4488 ,p_user_id => p_user_id
4489 ,p_machine => p_machine
4490 ,p_terminal => p_terminal
4491 ,x_return_status => x_return_status
4492 ,x_msg_count => x_msg_count
4493 ,x_msg_data => x_msg_data
4494 );
4495 COMMIT;
4496
4497 EXCEPTION
4498 WHEN FND_API.G_EXC_ERROR THEN
4499 ROLLBACK;
4500 x_return_status := FND_API.G_RET_STS_ERROR;
4501 FND_MSG_PUB.Count_And_Get(
4502 p_encoded => 'F'
4503 ,p_count => x_msg_count
4504 ,p_data => x_msg_data
4505 );
4506 RAISE;
4507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4508 ROLLBACK;
4509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4510 FND_MSG_PUB.Count_And_Get(
4511 p_encoded => 'F'
4512 ,p_count => x_msg_count
4513 ,p_data => x_msg_data
4514 );
4515 RAISE;
4516 WHEN OTHERS THEN
4517 ROLLBACK;
4518 FND_MSG_PUB.Add_Exc_Msg(
4519 G_PKG_NAME,
4520 l_api_name,
4521 SQLERRM
4522 );
4523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4524 FND_MSG_PUB.Count_And_Get(
4525 p_encoded => 'F'
4526 ,p_count => x_msg_count
4527 ,p_data => x_msg_data
4528 );
4529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4530 END UPDATE_USER_INFO_AUTONOMOUS;
4531
4532
4533 /*------------------------------------------------------------------------------------------
4534 Procedure DELETE_LOCK_INFO
4535 This procedure delete the object from the lock tables
4536 <parameters>
4537 p_object_key: The primary key of the Object, usually the TO_CHAR value
4538 of the Object ID. If the Object has composite keys,
4539 the value to pass in will be a concatenation of
4540 all the keys, separated by commas
4541 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4542 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4543 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4544 "PERIODICITY", and "TABLE"
4545 p_program_id: -100 = Data Loader UI
4546 -101 = Data Loader Backend
4547 -200 = Generate Database
4548 -201 = Generate Documentation
4549 -202 = Rename Interface Table
4550 -203 = Generate Database Configuration
4551 -300 = Administrator
4552 -400 = Objective Designer
4553 -500 = Builder
4554 -600 = Performance Scorecard
4555 -700 = System Upgrade
4556 -800 = System Migration
4557 p_user_id: Application User ID
4558 p_machine: The Machine
4559 p_terminal: The Terminal
4560 -------------------------------------------------------------------------------------------*/
4561 Procedure DELETE_LOCK_INFO(
4562 p_object_key IN varchar2
4563 ,p_object_type IN varchar2
4564 ,p_program_id IN number
4565 ,p_user_id IN number
4566 ,p_machine IN varchar2
4567 ,p_terminal IN varchar2
4568 ,x_return_status OUT NOCOPY varchar2
4569 ,x_msg_count OUT NOCOPY number
4570 ,x_msg_data OUT NOCOPY varchar2
4571 ) IS
4572
4573 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LOCK_INFO';
4574 l_last_save_time DATE;
4575
4576 BEGIN
4577 --DBMS_OUTPUT.PUT_LINE('DELETE_LOCK_INFO');
4578 x_return_status := FND_API.G_RET_STS_SUCCESS;
4579
4580 -- Lock the user entry of the object
4581 BSC_LOCKS_PVT.LOCK_USER
4582 (
4583 p_object_key => p_object_key
4584 ,p_object_type => p_object_type
4585 ,p_user_type => 'M'
4586 ,p_program_id => p_program_id
4587 ,p_user_id => p_user_id
4588 ,p_machine => p_machine
4589 ,p_terminal => p_terminal
4590 ,x_return_status => x_return_status
4591 ,x_msg_count => x_msg_count
4592 ,x_msg_data => x_msg_data
4593 );
4594
4595 -- Lock the object
4596 l_last_save_time :=
4597 BSC_LOCKS_PVT.LOCK_OBJECT
4598 (
4599 p_object_key => p_object_key
4600 ,p_object_type => p_object_type
4601 ,p_lock_type => 'W'
4602 ,p_query_time => SYSDATE
4603 ,p_program_id => p_program_id
4604 ,p_user_id => p_user_id
4605 ,p_machine => p_machine
4606 ,p_terminal => p_terminal
4607 ,x_return_status => x_return_status
4608 ,x_msg_count => x_msg_count
4609 ,x_msg_data => x_msg_data
4610 );
4611
4612 DELETE FROM BSC_OBJECT_LOCKS
4613 WHERE OBJECT_TYPE = p_object_type
4614 AND OBJECT_KEY = p_object_key;
4615
4616 DELETE FROM BSC_OBJECT_LOCK_USERS
4617 WHERE OBJECT_TYPE = p_object_type
4618 AND OBJECT_KEY = p_object_key;
4619
4620 EXCEPTION
4621 WHEN FND_API.G_EXC_ERROR THEN
4622 x_return_status := FND_API.G_RET_STS_ERROR;
4623 FND_MSG_PUB.Count_And_Get(
4624 p_encoded => 'F'
4625 ,p_count => x_msg_count
4626 ,p_data => x_msg_data
4627 );
4628 RAISE;
4629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4631 FND_MSG_PUB.Count_And_Get(
4632 p_encoded => 'F'
4633 ,p_count => x_msg_count
4634 ,p_data => x_msg_data
4635 );
4636 RAISE;
4637 WHEN OTHERS THEN
4638 FND_MSG_PUB.Add_Exc_Msg(
4639 G_PKG_NAME,
4640 l_api_name,
4641 SQLERRM
4642 );
4643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4644 FND_MSG_PUB.Count_And_Get(
4645 p_encoded => 'F'
4646 ,p_count => x_msg_count
4647 ,p_data => x_msg_data
4648 );
4649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4650 END DELETE_LOCK_INFO;
4651
4652
4653 /*------------------------------------------------------------------------------------------
4654 Procedure DELETE_LOCK_INFO_AUTONOMOUS
4655 This procedure delete the object from the lock tables
4656 <parameters>
4657 p_object_key: The primary key of the Object, usually the TO_CHAR value
4658 of the Object ID. If the Object has composite keys,
4659 the value to pass in will be a concatenation of
4660 all the keys, separated by commas
4661 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4662 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4663 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4664 "PERIODICITY", and "TABLE"
4665 p_program_id: -100 = Data Loader UI
4666 -101 = Data Loader Backend
4667 -200 = Generate Database
4668 -201 = Generate Documentation
4669 -202 = Rename Interface Table
4670 -203 = Generate Database Configuration
4671 -300 = Administrator
4672 -400 = Objective Designer
4673 -500 = Builder
4674 -600 = Performance Scorecard
4675 -700 = System Upgrade
4676 -800 = System Migration
4677 p_user_id: Application User ID
4678 p_machine: The Machine
4679 p_terminal: The Terminal
4680 -------------------------------------------------------------------------------------------*/
4681 Procedure DELETE_LOCK_INFO_AUTONOMOUS(
4682 p_object_key IN varchar2
4683 ,p_object_type IN varchar2
4684 ,p_program_id IN number
4685 ,p_user_id IN number
4686 ,p_machine IN varchar2
4687 ,p_terminal IN varchar2
4688 ,x_return_status OUT NOCOPY varchar2
4689 ,x_msg_count OUT NOCOPY number
4690 ,x_msg_data OUT NOCOPY varchar2
4691 ) IS
4692 PRAGMA AUTONOMOUS_TRANSACTION;
4693
4694 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LOCK_INFO_AUTONOMOUS';
4695
4696 BEGIN
4697 --DBMS_OUTPUT.PUT_LINE('DELETE_LOCK_INFO_AUTONOMOUS');
4698 x_return_status := FND_API.G_RET_STS_SUCCESS;
4699
4700 BSC_LOCKS_PVT.DELETE_LOCK_INFO
4701 (
4702 p_object_key => p_object_key
4703 ,p_object_type => p_object_type
4704 ,p_program_id => p_program_id
4705 ,p_user_id => p_user_id
4706 ,p_machine => p_machine
4707 ,p_terminal => p_terminal
4708 ,x_return_status => x_return_status
4709 ,x_msg_count => x_msg_count
4710 ,x_msg_data => x_msg_data
4711 );
4712 COMMIT;
4713
4714 EXCEPTION
4715 WHEN FND_API.G_EXC_ERROR THEN
4716 ROLLBACK;
4717 x_return_status := FND_API.G_RET_STS_ERROR;
4718 FND_MSG_PUB.Count_And_Get(
4719 p_encoded => 'F'
4720 ,p_count => x_msg_count
4721 ,p_data => x_msg_data
4722 );
4723 RAISE;
4724 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4725 ROLLBACK;
4726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4727 FND_MSG_PUB.Count_And_Get(
4728 p_encoded => 'F'
4729 ,p_count => x_msg_count
4730 ,p_data => x_msg_data
4731 );
4732 RAISE;
4733 WHEN OTHERS THEN
4734 ROLLBACK;
4735 FND_MSG_PUB.Add_Exc_Msg(
4736 G_PKG_NAME,
4737 l_api_name,
4738 SQLERRM
4739 );
4740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4741 FND_MSG_PUB.Count_And_Get(
4742 p_encoded => 'F'
4743 ,p_count => x_msg_count
4744 ,p_data => x_msg_data
4745 );
4746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4747 END DELETE_LOCK_INFO_AUTONOMOUS;
4748
4749
4750 /*------------------------------------------------------------------------------------------
4751 Procedure RAISE_EXCEPTION
4752 This procedure retrieves the session information and raises an exception
4753 <parameters>
4754 p_object_key: The primary key of the Object, usually the TO_CHAR value
4755 of the Object ID. If the Object has composite keys,
4756 the value to pass in will be a concatenation of
4757 all the keys, separated by commas
4758 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
4759 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
4760 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
4761 "PERIODICITY", and "TABLE"
4762 p_exception_type: "L" = BSC_LOCK_ERR_LOCKED, "M" = BSC_LOCK_ERR_MODIFIED
4763 -------------------------------------------------------------------------------------------*/
4764 Procedure RAISE_EXCEPTION(
4765 p_object_key IN varchar2
4766 ,p_object_type IN varchar2
4767 ,p_exception_type IN varchar2
4768 ,x_return_status OUT NOCOPY varchar2
4769 ,x_msg_count OUT NOCOPY number
4770 ,x_msg_data OUT NOCOPY varchar2
4771 ) IS
4772
4773 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_EXCEPTION';
4774 l_component BSC_LOOKUPS.MEANING%TYPE;
4775 l_user_name BSC_APPS_USERS_V.USER_NAME%TYPE;
4776 l_machine BSC_OBJECT_LOCK_USERS.MACHINE%TYPE;
4777 l_terminal BSC_OBJECT_LOCK_USERS.TERMINAL%TYPE;
4778
4779 CURSOR c_get_user(
4780 c_object_key VARCHAR2
4781 ,c_object_type VARCHAR2
4782 ,c_user_type VARCHAR2
4783 ) IS
4784 SELECT L.PROGRAM_ID, U.USER_NAME, L.MACHINE, L.TERMINAL
4785 FROM BSC_OBJECT_LOCK_USERS L, BSC_APPS_USERS_V U
4786 WHERE L.OBJECT_KEY = c_object_key
4787 AND L.OBJECT_TYPE = c_object_type
4788 AND L.USER_TYPE = c_user_type
4789 AND L.USER_ID = U.USER_ID (+);
4790
4791 BEGIN
4792 --DBMS_OUTPUT.PUT_LINE('RAISE_EXCEPTION: '||p_object_key||' '||p_object_type||' '||p_exception_type);
4793 x_return_status := FND_API.G_RET_STS_SUCCESS;
4794
4795 l_component := NULL;
4796 l_user_name := NULL;
4797 l_machine := NULL;
4798 l_terminal := NULL;
4799 FOR cuser IN c_get_user(p_object_key, p_object_type, p_exception_type) LOOP
4800 l_component := g_modules(cuser.PROGRAM_ID);
4801 l_user_name := cuser.USER_NAME;
4802 l_machine := cuser.MACHINE;
4803 l_terminal := cuser.TERMINAL;
4804 END LOOP;
4805
4806 -- Cover the case when the whole table is locked
4807 IF (l_component IS NULL) THEN
4808 FOR cuser IN c_get_user('ALL', 'ALL', p_exception_type) LOOP
4809 l_component := g_modules(cuser.PROGRAM_ID);
4810 l_user_name := cuser.USER_NAME;
4811 l_machine := cuser.MACHINE;
4812 l_terminal := cuser.TERMINAL;
4813 END LOOP;
4814 END IF;
4815
4816 IF (p_exception_type = 'M') THEN
4817 FND_MESSAGE.SET_NAME('BSC','BSC_LOCK_ERR_MODIFIED');
4818 ELSE
4819 FND_MESSAGE.SET_NAME('BSC','BSC_LOCK_ERR_LOCKED');
4820 END IF;
4821 FND_MESSAGE.SET_TOKEN('COMPONENT', NVL(l_component, ''), TRUE);
4822 FND_MESSAGE.SET_TOKEN('USERNAME' , NVL(l_user_name, ''), TRUE);
4823 FND_MESSAGE.SET_TOKEN('MACHINE' , NVL(l_machine, ''), TRUE);
4824 FND_MESSAGE.SET_TOKEN('TERMINAL' , NVL(l_terminal, ''), TRUE);
4825 FND_MSG_PUB.ADD;
4826 RAISE FND_API.G_EXC_ERROR;
4827
4828 EXCEPTION
4829 WHEN FND_API.G_EXC_ERROR THEN
4830 x_return_status := FND_API.G_RET_STS_ERROR;
4831 FND_MSG_PUB.Count_And_Get(
4832 p_encoded => 'F'
4833 ,p_count => x_msg_count
4834 ,p_data => x_msg_data
4835 );
4836 RAISE;
4837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4839 FND_MSG_PUB.Count_And_Get(
4840 p_encoded => 'F'
4841 ,p_count => x_msg_count
4842 ,p_data => x_msg_data
4843 );
4844 RAISE;
4845 WHEN OTHERS THEN
4846 FND_MSG_PUB.Add_Exc_Msg(
4847 G_PKG_NAME,
4848 l_api_name,
4849 SQLERRM
4850 );
4851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4852 FND_MSG_PUB.Count_And_Get(
4853 p_encoded => 'F'
4854 ,p_count => x_msg_count
4855 ,p_data => x_msg_data
4856 );
4857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4858 END RAISE_EXCEPTION;
4859
4860
4861 /*------------------------------------------------------------------------------------------
4862 Procedure GET_SESSION
4863 This procedure retrieves the session information (machine, terminal, etc.)
4864 <parameters>
4865 x_machine: The machine
4866 x_terminal: The terminal
4867 -------------------------------------------------------------------------------------------*/
4868 Procedure GET_SESSION(
4869 x_machine OUT NOCOPY varchar2
4870 ,x_terminal OUT NOCOPY varchar2
4871 ,x_return_status OUT NOCOPY varchar2
4872 ,x_msg_count OUT NOCOPY number
4873 ,x_msg_data OUT NOCOPY varchar2
4874 ) IS
4875
4876 l_api_name CONSTANT VARCHAR2(30) := 'GET_SESSION';
4877
4878 CURSOR c_get_session(c_session_id NUMBER) IS
4879 SELECT MACHINE, TERMINAL
4880 FROM v$session S
4881 WHERE S.AUDSID = c_session_id;
4882
4883 BEGIN
4884 --DBMS_OUTPUT.PUT_LINE('GET_SESSION');
4885 x_return_status := FND_API.G_RET_STS_SUCCESS;
4886
4887 FOR csession IN c_get_session(USERENV('SESSIONID')) LOOP
4888 x_machine := csession.MACHINE;
4889 x_terminal := csession.TERMINAL;
4890 END LOOP;
4891
4892 EXCEPTION
4893 WHEN FND_API.G_EXC_ERROR THEN
4894 x_return_status := FND_API.G_RET_STS_ERROR;
4895 FND_MSG_PUB.Count_And_Get(
4896 p_encoded => 'F'
4897 ,p_count => x_msg_count
4898 ,p_data => x_msg_data
4899 );
4900 RAISE;
4901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4903 FND_MSG_PUB.Count_And_Get(
4904 p_encoded => 'F'
4905 ,p_count => x_msg_count
4906 ,p_data => x_msg_data
4907 );
4908 RAISE;
4909 WHEN OTHERS THEN
4910 FND_MSG_PUB.Add_Exc_Msg(
4911 G_PKG_NAME,
4912 l_api_name,
4913 SQLERRM
4914 );
4915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4916 FND_MSG_PUB.Count_And_Get(
4917 p_encoded => 'F'
4918 ,p_count => x_msg_count
4919 ,p_data => x_msg_data
4920 );
4921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4922 END GET_SESSION;
4923
4924 /*------------------------------------------------------------------------------------------
4925 Procedure INITIALIZE
4926
4927 DESCRIPTION:
4928 Populate global variables
4929 -------------------------------------------------------------------------------------------*/
4930 Procedure INITIALIZE
4931 IS
4932 BEGIN
4933 g_modules(-100) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader UI
4934 g_modules(-101) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader concurrent program
4935 g_modules(-200) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); --Generate Database
4936 g_modules(-201) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); --Generate documentation
4937 g_modules(-202) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); --Rename interface tables
4938 g_modules(-203) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); --Generate Database Configuration
4939 g_modules(-300) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_ADMINISTRATOR');
4940 g_modules(-400) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'KPI_DESIGNER');
4941 g_modules(-500) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_BUILDER');
4942 g_modules(-600) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'OBSC_VIEWER');
4943 g_modules(-700) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'UPGRADE');
4944 g_modules(-800) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
4945 g_modules(-801) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
4946 g_modules(-802) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
4947 EXCEPTION
4948 WHEN OTHERS THEN
4949 NULL;
4950 END;
4951
4952
4953 /*------------------------------------------------------------------------------------------
4954 GET_BSC_SCHEMA
4955
4956 DESCRIPTION:
4957 Returns the BSC schema name
4958 -------------------------------------------------------------------------------------------*/
4959 FUNCTION GET_BSC_SCHEMA
4960 RETURN VARCHAR2 IS
4961 l_bsc_schema VARCHAR2(32);
4962 dummy1 VARCHAR2(32);
4963 dummy2 VARCHAR2(32);
4964 dummy3 BOOLEAN;
4965 BEGIN
4966 l_bsc_schema := NULL;
4967 dummy1 := NULL;
4968 dummy2 := NULL;
4969 dummy3 := FND_INSTALLATION.GET_APP_INFO('BSC', dummy1, dummy2, l_bsc_schema);
4970 RETURN l_bsc_schema;
4971 EXCEPTION
4972 WHEN OTHERS THEN
4973 RETURN NULL;
4974 END;
4975
4976 END BSC_LOCKS_PVT;