1 PACKAGE BSC_LOCKS_PVT AS
2 /* $Header: BSCVLOKS.pls 120.3 2005/12/07 18:25:34 calaw noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'BSC_LOCKS_PVT';
5 TYPE t_array_module IS TABLE OF BSC_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
6 TYPE t_array_object_key IS TABLE OF BSC_OBJECT_LOCKS.OBJECT_KEY%TYPE INDEX BY BINARY_INTEGER;
7 TYPE t_array_object_type IS TABLE OF BSC_OBJECT_LOCKS.OBJECT_TYPE%TYPE INDEX BY BINARY_INTEGER;
8 g_modules t_array_module;
9
10 /*------------------------------------------------------------------------------------------
11 Procedure VALIDATE_OBJECT
12 This procedure inspects the validity of an Object.
13 An exception will be raised if the Object does not exist in the database.
14 <parameters>
15 p_object_key: The primary key of the Object, usually the TO_CHAR value
16 of the Object ID. If the Object has composite keys,
17 the value to pass in will be a concatenation of
18 all the keys, separated by commas
19 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
20 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
21 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
22 "PERIODICITY", and "TABLE"
23 -------------------------------------------------------------------------------------------*/
24 Procedure VALIDATE_OBJECT (
25 p_object_key IN varchar2
26 ,p_object_type IN varchar2
27 ,x_return_status OUT NOCOPY varchar2
28 ,x_msg_count OUT NOCOPY number
29 ,x_msg_data OUT NOCOPY varchar2
30 );
31
32
33 /*------------------------------------------------------------------------------------------
34 Function LOCK_OBJECT
35 This function puts a database lock on the corresponding row in the
36 lock table and returns the last_save_time value. If the row does
37 not exist, a new row will be inserted to the lock table.
38 In addition to that, the user table will also be updated.
39 If someone else already locked the object, an exception will be raised.
40 <parameters>
41 p_object_key: The primary key of the Object, usually the TO_CHAR value
42 of the Object ID. If the Object has composite keys,
43 the value to pass in will be a concatenation of
44 all the keys, separated by commas
45 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
46 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
47 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
48 "PERIODICITY", and "TABLE"
49 p_lock_type: 'W' for write lock, 'R' for read lock
50 p_query_time: The query time at the start of the process flow
51 p_program_id: -100 = Data Loader UI
52 -101 = Data Loader Backend
53 -200 = Generate Database
54 -201 = Generate Documentation
55 -202 = Rename Interface Table
56 -203 = Generate Database Configuration
57 -300 = Administrator
58 -400 = Objective Designer
59 -500 = Builder
60 -600 = Performance Scorecard
61 -700 = System Upgrade
62 -800 = System Migration
63 p_user_id: Application User ID
64 p_machine: The Machine
65 p_terminal: The Terminal
66 -------------------------------------------------------------------------------------------*/
67 Function LOCK_OBJECT(
68 p_object_key IN varchar2
69 ,p_object_type IN varchar2
70 ,p_lock_type IN varchar2
71 ,p_query_time IN date
72 ,p_program_id IN number
73 ,p_user_id IN number
74 ,p_machine IN varchar2
75 ,p_terminal IN varchar2
76 ,x_return_status OUT NOCOPY varchar2
77 ,x_msg_count OUT NOCOPY number
78 ,x_msg_data OUT NOCOPY varchar2
79 ) return DATE;
80
81
82 /*------------------------------------------------------------------------------------------
83 Procedure LOCK_OBJECT
84 This procedure locks all the objects with a certain type
85 <parameters>
86 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
87 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
88 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
89 "PERIODICITY", and "TABLE"
90 p_lock_type: 'W' for write lock, 'R' for read lock
91 p_query_time: The query time at the start of the process flow
92 p_program_id: -100 = Data Loader UI
93 -101 = Data Loader Backend
94 -200 = Generate Database
95 -201 = Generate Documentation
96 -202 = Rename Interface Table
97 -203 = Generate Database Configuration
98 -300 = Administrator
99 -400 = Objective Designer
100 -500 = Builder
101 -600 = Performance Scorecard
102 -700 = System Upgrade
103 -800 = System Migration
104 p_user_id: Application User ID
105 p_machine: The Machine
106 p_terminal: The Terminal
107 p_cascade_lock_level: Number of level for cascade locks
108 Default is -1 which means enable cascade locking
109 all the way to the lowest level
110 -------------------------------------------------------------------------------------------*/
111 Procedure LOCK_OBJECT(
112 p_object_type IN varchar2
113 ,p_lock_type IN varchar2
114 ,p_query_time IN date
115 ,p_program_id IN number
116 ,p_user_id IN number
117 ,p_machine IN varchar2
118 ,p_terminal IN varchar2
119 ,p_cascade_lock_level IN number
120 ,x_return_status OUT NOCOPY varchar2
121 ,x_msg_count OUT NOCOPY number
122 ,x_msg_data OUT NOCOPY varchar2
123 );
124
125
126 /*------------------------------------------------------------------------------------------
127 Function LOCK_OBJECT_WRITE
128 This function acquires a write (exclusive) lock on an Object
129 and returns the last_save_time value.
130 <parameters>
131 p_object_key: The primary key of the Object, usually the TO_CHAR value
132 of the Object ID. If the Object has composite keys,
133 the value to pass in will be a concatenation of
134 all the keys, separated by commas
135 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
136 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
137 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
138 "PERIODICITY", and "TABLE"
139 p_program_id: -100 = Data Loader UI
140 -101 = Data Loader Backend
141 -200 = Generate Database
142 -201 = Generate Documentation
143 -202 = Rename Interface Table
144 -203 = Generate Database Configuration
145 -300 = Administrator
146 -400 = Objective Designer
147 -500 = Builder
148 -600 = Performance Scorecard
149 -700 = System Upgrade
150 -800 = System Migration
151 p_user_id: Application User ID
152 p_machine: The Machine
153 p_terminal: The Terminal
154 -------------------------------------------------------------------------------------------*/
155 Function LOCK_OBJECT_WRITE(
156 p_object_key IN varchar2
157 ,p_object_type IN varchar2
158 ,p_program_id IN number
159 ,p_user_id IN number
160 ,p_machine IN varchar2
161 ,p_terminal IN varchar2
162 ,x_return_status OUT NOCOPY varchar2
163 ,x_msg_count OUT NOCOPY number
164 ,x_msg_data OUT NOCOPY varchar2
165 ) return DATE;
166
167
168 /*------------------------------------------------------------------------------------------
169 Function LOCK_OBJECT_WRITE
170 This function acquires a write (exclusive) lock on an Object
171 and returns the last_save_time value.
172 <parameters>
173 p_object_key: The primary key of the Object, usually the TO_CHAR value
174 of the Object ID. If the Object has composite keys,
175 the value to pass in will be a concatenation of
176 all the keys, separated by commas
177 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
178 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
179 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
180 "PERIODICITY", and "TABLE"
181 x_insert_flag: True if the lock entry is missing in the lock table
182 -------------------------------------------------------------------------------------------*/
183 Function LOCK_OBJECT_WRITE(
184 p_object_key IN varchar2
185 ,p_object_type IN varchar2
186 ,x_insert_flag OUT NOCOPY boolean
187 ,x_return_status OUT NOCOPY varchar2
188 ,x_msg_count OUT NOCOPY number
189 ,x_msg_data OUT NOCOPY varchar2
190 ) return DATE;
191
192
193 /*------------------------------------------------------------------------------------------
194 Function LOCK_OBJECT_READ
195 This procedure acquires puts a read (shared) lock on the Object.
196 <parameters>
197 p_object_key: The primary key of the Object, usually the TO_CHAR value
198 of the Object ID. If the Object has composite keys,
199 the value to pass in will be a concatenation of
200 all the keys, separated by commas
201 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
202 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
203 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
204 "PERIODICITY", and "TABLE"
205 p_program_id: -100 = Data Loader UI
206 -101 = Data Loader Backend
207 -200 = Generate Database
208 -201 = Generate Documentation
209 -202 = Rename Interface Table
210 -203 = Generate Database Configuration
211 -300 = Administrator
212 -400 = Objective Designer
213 -500 = Builder
214 -600 = Performance Scorecard
215 -700 = System Upgrade
216 -800 = System Migration
217 p_user_id: Application User ID
218 p_machine: The Machine
219 p_terminal: The Terminal
220 -------------------------------------------------------------------------------------------*/
221 Function LOCK_OBJECT_READ(
222 p_object_key IN varchar2
223 ,p_object_type IN varchar2
224 ,p_program_id IN number
225 ,p_user_id IN number
226 ,p_machine IN varchar2
227 ,p_terminal IN varchar2
228 ,x_return_status OUT NOCOPY varchar2
229 ,x_msg_count OUT NOCOPY number
230 ,x_msg_data OUT NOCOPY varchar2
231 ) return DATE;
232
233
234 /*------------------------------------------------------------------------------------------
235 Procedure LOCK_OBJECT_ALL
236 This procedure locks the locking tables
237 -------------------------------------------------------------------------------------------*/
238 Procedure LOCK_OBJECT_ALL(
239 x_return_status OUT NOCOPY varchar2
240 ,x_msg_count OUT NOCOPY number
241 ,x_msg_data OUT NOCOPY varchar2
242 );
243
244
245 /*------------------------------------------------------------------------------------------
246 Procedure LOCK_USER
247 This procedure puts a database lock on the corresponding row in the
248 lock user table. If the row does not exist, a new row will be inserted
249 to the lock user table. If someone else already locked the object,
250 an exception will be raised.
251 <parameters>
252 p_object_key: The primary key of the Object, usually the TO_CHAR value
253 of the Object ID. If the Object has composite keys,
254 the value to pass in will be a concatenation of
255 all the keys, separated by commas
256 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
260 p_user_type: "L" = Lock, "M" = Modify
257 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
258 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
259 "PERIODICITY", and "TABLE"
261 p_program_id: -100 = Data Loader UI
262 -101 = Data Loader Backend
263 -200 = Generate Database
264 -201 = Generate Documentation
265 -202 = Rename Interface Table
266 -203 = Generate Database Configuration
267 -300 = Administrator
268 -400 = Objective Designer
269 -500 = Builder
270 -600 = Performance Scorecard
271 -700 = System Upgrade
272 -800 = System Migration
273 p_user_id: Application User ID
274 p_machine: The Machine
275 p_terminal: The Terminal
276 -------------------------------------------------------------------------------------------*/
277 Procedure LOCK_USER(
278 p_object_key IN varchar2
279 ,p_object_type IN varchar2
280 ,p_user_type IN varchar2
281 ,p_program_id IN number
282 ,p_user_id IN number
283 ,p_machine IN varchar2
284 ,p_terminal IN varchar2
285 ,x_return_status OUT NOCOPY varchar2
286 ,x_msg_count OUT NOCOPY number
287 ,x_msg_data OUT NOCOPY varchar2
288 );
289
290
291 /*------------------------------------------------------------------------------------------
292 Procedure GET_CHILD_OBJECTS
293 This procedure retrieves the list of child objects down the hierarchy
294 <parameters>
295 p_object_key: The primary key of the Object, usually the TO_CHAR value
296 of the Object ID. If the Object has composite keys,
297 the value to pass in will be a concatenation of
298 all the keys, separated by commas
299 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
300 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
301 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
302 "PERIODICITY", and "TABLE"
303 p_cascade_lock_level: Number of level for cascade locks
304 Default is -1 which means enable cascade locking
305 all the way to the lowest level
306 p_lowest_level_type: The type of the lowest level object
307 x_child_object_keys: Table of child object keys
308 x_child_object_types: Table of child object types
309 x_child_object_count: Total number of child objects
310 -------------------------------------------------------------------------------------------*/
311 Procedure GET_CHILD_OBJECTS(
312 p_object_key IN varchar2
313 ,p_object_type IN varchar2
314 ,p_cascade_lock_level IN number
315 ,p_lowest_level_type IN varchar2
316 ,x_child_object_keys IN OUT NOCOPY t_array_object_key
317 ,x_child_object_types IN OUT NOCOPY t_array_object_type
318 ,x_child_object_count IN OUT NOCOPY number
319 ,x_return_status OUT NOCOPY varchar2
320 ,x_msg_count OUT NOCOPY number
321 ,x_msg_data OUT NOCOPY varchar2
322 );
323
324
325 /*------------------------------------------------------------------------------------------
326 Procedure INSERT_LOCK_ALL
327 This procedure inserts the ALL entries into the lock table and user table
328 <parameters>
329 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
330 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
331 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
332 "PERIODICITY", and "TABLE"
333 p_program_id: -100 = Data Loader UI
334 -101 = Data Loader Backend
335 -200 = Generate Database
336 -201 = Generate Documentation
337 -202 = Rename Interface Table
338 -203 = Generate Database Configuration
339 -300 = Administrator
340 -400 = Objective Designer
341 -500 = Builder
342 -600 = Performance Scorecard
343 -700 = System Upgrade
344 -800 = System Migration
345 p_user_id: Application User ID
346 p_machine: The Machine
347 p_terminal: The Terminal
348 p_session_id: The Database Session ID
349 -------------------------------------------------------------------------------------------*/
350 Procedure INSERT_LOCK_ALL(
351 p_object_type IN varchar2
352 ,p_program_id IN number
353 ,p_user_id IN number
354 ,p_machine IN varchar2
355 ,p_terminal IN varchar2
356 ,p_session_id IN number
357 ,x_return_status OUT NOCOPY varchar2
358 ,x_msg_count OUT NOCOPY number
359 ,x_msg_data OUT NOCOPY varchar2
360 );
361
362
363 /*------------------------------------------------------------------------------------------
364 Procedure INSERT_LOCK_ALL_AUTONOMOUS
365 This procedure inserts the ALL entries into the lock table and user table
366 <parameters>
367 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
368 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
372 -101 = Data Loader Backend
369 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
370 "PERIODICITY", and "TABLE"
371 p_program_id: -100 = Data Loader UI
373 -200 = Generate Database
374 -201 = Generate Documentation
375 -202 = Rename Interface Table
376 -203 = Generate Database Configuration
377 -300 = Administrator
378 -400 = Objective Designer
379 -500 = Builder
380 -600 = Performance Scorecard
381 -700 = System Upgrade
382 -800 = System Migration
383 p_user_id: Application User ID
384 p_machine: The Machine
385 p_terminal: The Terminal
386 p_session_id: The Database Session ID
387 -------------------------------------------------------------------------------------------*/
388 Procedure INSERT_LOCK_ALL_AUTONOMOUS(
389 p_object_type IN varchar2
390 ,p_program_id IN number
391 ,p_user_id IN number
392 ,p_machine IN varchar2
393 ,p_terminal IN varchar2
394 ,p_session_id IN number
395 ,x_return_status OUT NOCOPY varchar2
396 ,x_msg_count OUT NOCOPY number
397 ,x_msg_data OUT NOCOPY varchar2
398 );
399
400
401 /*------------------------------------------------------------------------------------------
402 Procedure INSERT_LOCK_SCORECARD
403 This procedure inserts scorecard entries into the lock table and user table
404 <parameters>
405 p_program_id: -100 = Data Loader UI
406 -101 = Data Loader Backend
407 -200 = Generate Database
408 -201 = Generate Documentation
409 -202 = Rename Interface Table
410 -203 = Generate Database Configuration
411 -300 = Administrator
412 -400 = Objective Designer
413 -500 = Builder
414 -600 = Performance Scorecard
415 -700 = System Upgrade
416 -800 = System Migration
417 p_user_id: Application User ID
418 p_machine: The Machine
419 p_terminal: The Terminal
420 -------------------------------------------------------------------------------------------*/
421 Procedure INSERT_LOCK_SCORECARD(
422 p_program_id IN number
423 ,p_user_id IN number
424 ,p_machine IN varchar2
425 ,p_terminal IN varchar2
426 ,x_return_status OUT NOCOPY varchar2
427 ,x_msg_count OUT NOCOPY number
428 ,x_msg_data OUT NOCOPY varchar2
429 );
430
431
432 /*------------------------------------------------------------------------------------------
433 Procedure INSERT_LOCK_OBJECTIVE
434 This procedure inserts objective entries into the lock table and user table
435 <parameters>
436 p_program_id: -100 = Data Loader UI
437 -101 = Data Loader Backend
438 -200 = Generate Database
439 -201 = Generate Documentation
440 -202 = Rename Interface Table
441 -203 = Generate Database Configuration
442 -300 = Administrator
443 -400 = Objective Designer
444 -500 = Builder
445 -600 = Performance Scorecard
446 -700 = System Upgrade
447 -800 = System Migration
448 p_user_id: Application User ID
449 p_machine: The Machine
450 p_terminal: The Terminal
451 -------------------------------------------------------------------------------------------*/
452 Procedure INSERT_LOCK_OBJECTIVE(
453 p_program_id IN number
454 ,p_user_id IN number
455 ,p_machine IN varchar2
456 ,p_terminal IN varchar2
457 ,x_return_status OUT NOCOPY varchar2
458 ,x_msg_count OUT NOCOPY number
459 ,x_msg_data OUT NOCOPY varchar2
460 );
461
462
463 /*------------------------------------------------------------------------------------------
464 Procedure INSERT_LOCK_DIMENSION
465 This procedure inserts dimension entries into the lock table and user table
466 <parameters>
467 p_program_id: -100 = Data Loader UI
468 -101 = Data Loader Backend
469 -200 = Generate Database
470 -201 = Generate Documentation
471 -202 = Rename Interface Table
472 -203 = Generate Database Configuration
473 -300 = Administrator
474 -400 = Objective Designer
475 -500 = Builder
476 -600 = Performance Scorecard
477 -700 = System Upgrade
478 -800 = System Migration
479 p_user_id: Application User ID
480 p_machine: The Machine
481 p_terminal: The Terminal
482 -------------------------------------------------------------------------------------------*/
483 Procedure INSERT_LOCK_DIMENSION(
484 p_program_id IN number
485 ,p_user_id IN number
486 ,p_machine IN varchar2
487 ,p_terminal IN varchar2
488 ,x_return_status OUT NOCOPY varchar2
492
489 ,x_msg_count OUT NOCOPY number
490 ,x_msg_data OUT NOCOPY varchar2
491 );
493
494 /*------------------------------------------------------------------------------------------
495 Procedure INSERT_LOCK_DIMENSION_OBJECT
496 This procedure inserts dimension object entries into the lock table and user table
497 <parameters>
498 p_program_id: -100 = Data Loader UI
499 -101 = Data Loader Backend
500 -200 = Generate Database
501 -201 = Generate Documentation
502 -202 = Rename Interface Table
503 -203 = Generate Database Configuration
504 -300 = Administrator
505 -400 = Objective Designer
506 -500 = Builder
507 -600 = Performance Scorecard
508 -700 = System Upgrade
509 -800 = System Migration
510 p_user_id: Application User ID
511 p_machine: The Machine
512 p_terminal: The Terminal
513 -------------------------------------------------------------------------------------------*/
514 Procedure INSERT_LOCK_DIMENSION_OBJECT(
515 p_program_id IN number
516 ,p_user_id IN number
517 ,p_machine IN varchar2
518 ,p_terminal IN varchar2
519 ,x_return_status OUT NOCOPY varchar2
520 ,x_msg_count OUT NOCOPY number
521 ,x_msg_data OUT NOCOPY varchar2
522 );
523
524
525 /*------------------------------------------------------------------------------------------
526 Procedure INSERT_LOCK_MEASURE
527 This procedure inserts measure entries into the lock table and user table
528 <parameters>
529 p_program_id: -100 = Data Loader UI
530 -101 = Data Loader Backend
531 -200 = Generate Database
532 -201 = Generate Documentation
533 -202 = Rename Interface Table
534 -203 = Generate Database Configuration
535 -300 = Administrator
536 -400 = Objective Designer
537 -500 = Builder
538 -600 = Performance Scorecard
539 -700 = System Upgrade
540 -800 = System Migration
541 p_user_id: Application User ID
542 p_machine: The Machine
543 p_terminal: The Terminal
544 -------------------------------------------------------------------------------------------*/
545 Procedure INSERT_LOCK_MEASURE(
546 p_program_id IN number
547 ,p_user_id IN number
548 ,p_machine IN varchar2
549 ,p_terminal IN varchar2
550 ,x_return_status OUT NOCOPY varchar2
551 ,x_msg_count OUT NOCOPY number
552 ,x_msg_data OUT NOCOPY varchar2
553 );
554
555
556 /*------------------------------------------------------------------------------------------
557 Procedure INSERT_LOCK_DATA_COLUMN
558 This procedure inserts data column entries into the lock table and user table
559 <parameters>
560 p_program_id: -100 = Data Loader UI
561 -101 = Data Loader Backend
562 -200 = Generate Database
563 -201 = Generate Documentation
564 -202 = Rename Interface Table
565 -203 = Generate Database Configuration
566 -300 = Administrator
567 -400 = Objective Designer
568 -500 = Builder
569 -600 = Performance Scorecard
570 -700 = System Upgrade
571 -800 = System Migration
572 p_user_id: Application User ID
573 p_machine: The Machine
574 p_terminal: The Terminal
575 -------------------------------------------------------------------------------------------*/
576 Procedure INSERT_LOCK_DATA_COLUMN(
577 p_program_id IN number
578 ,p_user_id IN number
579 ,p_machine IN varchar2
580 ,p_terminal IN varchar2
581 ,x_return_status OUT NOCOPY varchar2
582 ,x_msg_count OUT NOCOPY number
583 ,x_msg_data OUT NOCOPY varchar2
584 );
585
586
587 /*------------------------------------------------------------------------------------------
588 Procedure INSERT_LOCK_CUSTOM_VIEW
589 This procedure inserts custom view entries into the lock table and user table
590 <parameters>
591 p_program_id: -100 = Data Loader UI
592 -101 = Data Loader Backend
593 -200 = Generate Database
594 -201 = Generate Documentation
595 -202 = Rename Interface Table
596 -203 = Generate Database Configuration
597 -300 = Administrator
598 -400 = Objective Designer
599 -500 = Builder
600 -600 = Performance Scorecard
601 -700 = System Upgrade
602 -800 = System Migration
603 p_user_id: Application User ID
604 p_machine: The Machine
605 p_terminal: The Terminal
606 -------------------------------------------------------------------------------------------*/
607 Procedure INSERT_LOCK_CUSTOM_VIEW(
608 p_program_id IN number
609 ,p_user_id IN number
610 ,p_machine IN varchar2
614 ,x_msg_data OUT NOCOPY varchar2
611 ,p_terminal IN varchar2
612 ,x_return_status OUT NOCOPY varchar2
613 ,x_msg_count OUT NOCOPY number
615 );
616
617
618 /*------------------------------------------------------------------------------------------
619 Procedure INSERT_LOCK_LAUNCHPAD
620 This procedure inserts launchpad entries into the lock table and user table
621 <parameters>
622 p_program_id: -100 = Data Loader UI
623 -101 = Data Loader Backend
624 -200 = Generate Database
625 -201 = Generate Documentation
626 -202 = Rename Interface Table
627 -203 = Generate Database Configuration
628 -300 = Administrator
629 -400 = Objective Designer
630 -500 = Builder
631 -600 = Performance Scorecard
632 -700 = System Upgrade
633 -800 = System Migration
634 p_user_id: Application User ID
635 p_machine: The Machine
636 p_terminal: The Terminal
637 -------------------------------------------------------------------------------------------*/
638 Procedure INSERT_LOCK_LAUNCHPAD(
639 p_program_id IN number
640 ,p_user_id IN number
641 ,p_machine IN varchar2
642 ,p_terminal IN varchar2
643 ,x_return_status OUT NOCOPY varchar2
644 ,x_msg_count OUT NOCOPY number
645 ,x_msg_data OUT NOCOPY varchar2
646 );
647
648
649 /*------------------------------------------------------------------------------------------
650 Procedure INSERT_LOCK_PERIODICITY
651 This procedure inserts periodicity entries into the lock table and user table
652 <parameters>
653 p_program_id: -100 = Data Loader UI
654 -101 = Data Loader Backend
655 -200 = Generate Database
656 -201 = Generate Documentation
657 -202 = Rename Interface Table
658 -203 = Generate Database Configuration
659 -300 = Administrator
660 -400 = Objective Designer
661 -500 = Builder
662 -600 = Performance Scorecard
663 -700 = System Upgrade
664 -800 = System Migration
665 p_user_id: Application User ID
666 p_machine: The Machine
667 p_terminal: The Terminal
668 -------------------------------------------------------------------------------------------*/
669 Procedure INSERT_LOCK_PERIODICITY(
670 p_program_id IN number
671 ,p_user_id IN number
672 ,p_machine IN varchar2
673 ,p_terminal IN varchar2
674 ,x_return_status OUT NOCOPY varchar2
675 ,x_msg_count OUT NOCOPY number
676 ,x_msg_data OUT NOCOPY varchar2
677 );
678
679
680 /*------------------------------------------------------------------------------------------
681 Procedure INSERT_LOCK_CALENDAR
682 This procedure inserts calendar entries into the lock table and user table
683 <parameters>
684 p_program_id: -100 = Data Loader UI
685 -101 = Data Loader Backend
686 -200 = Generate Database
687 -201 = Generate Documentation
688 -202 = Rename Interface Table
689 -203 = Generate Database Configuration
690 -300 = Administrator
691 -400 = Objective Designer
692 -500 = Builder
693 -600 = Performance Scorecard
694 -700 = System Upgrade
695 -800 = System Migration
696 p_user_id: Application User ID
697 p_machine: The Machine
698 p_terminal: The Terminal
699 -------------------------------------------------------------------------------------------*/
700 Procedure INSERT_LOCK_CALENDAR(
701 p_program_id IN number
702 ,p_user_id IN number
703 ,p_machine IN varchar2
704 ,p_terminal IN varchar2
705 ,x_return_status OUT NOCOPY varchar2
706 ,x_msg_count OUT NOCOPY number
707 ,x_msg_data OUT NOCOPY varchar2
708 );
709
710
711 /*------------------------------------------------------------------------------------------
712 Procedure INSERT_LOCK_TABLE
713 This procedure inserts table entries into the lock table and user table
714 <parameters>
715 p_program_id: -100 = Data Loader UI
716 -101 = Data Loader Backend
717 -200 = Generate Database
718 -201 = Generate Documentation
719 -202 = Rename Interface Table
720 -203 = Generate Database Configuration
721 -300 = Administrator
722 -400 = Objective Designer
723 -500 = Builder
724 -600 = Performance Scorecard
725 -700 = System Upgrade
726 -800 = System Migration
727 p_user_id: Application User ID
728 p_machine: The Machine
729 p_terminal: The Terminal
730 -------------------------------------------------------------------------------------------*/
731 Procedure INSERT_LOCK_TABLE(
732 p_program_id IN number
736 ,x_return_status OUT NOCOPY varchar2
733 ,p_user_id IN number
734 ,p_machine IN varchar2
735 ,p_terminal IN varchar2
737 ,x_msg_count OUT NOCOPY number
738 ,x_msg_data OUT NOCOPY varchar2
739 );
740
741
742 /*------------------------------------------------------------------------------------------
743 Procedure INSERT_LOCK_INFO
744 This procedure inserts a row in the lock table
745 <parameters>
746 p_object_key: The primary key of the Object, usually the TO_CHAR value
747 of the Object ID. If the Object has composite keys,
748 the value to pass in will be a concatenation of
749 all the keys, separated by commas
750 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
751 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
752 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
753 "PERIODICITY", and "TABLE"
754 p_lock_type: 'W' for write lock, 'R' for read lock
755 p_last_save_time: The last time the Object being modified
756 p_session_id: The Database Session ID
757 -------------------------------------------------------------------------------------------*/
758 Procedure INSERT_LOCK_INFO(
759 p_object_key IN varchar2
760 ,p_object_type IN varchar2
761 ,p_lock_type IN varchar2
762 ,p_last_save_time IN date
763 ,p_session_id IN number
764 ,x_return_status OUT NOCOPY varchar2
765 ,x_msg_count OUT NOCOPY number
766 ,x_msg_data OUT NOCOPY varchar2
767 );
768
769
770 /*------------------------------------------------------------------------------------------
771 Procedure INSERT_LOCK_INFO_AUTONOMOUS
772 This procedure inserts a row in the lock table
773 <parameters>
774 p_object_key: The primary key of the Object, usually the TO_CHAR value
775 of the Object ID. If the Object has composite keys,
776 the value to pass in will be a concatenation of
777 all the keys, separated by commas
778 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
779 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
780 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
781 "PERIODICITY", and "TABLE"
782 p_lock_type: 'W' for write lock, 'R' for read lock
783 p_last_save_time: The last time the Object being modified
784 p_session_id: The Database Session ID
785 -------------------------------------------------------------------------------------------*/
786 Procedure INSERT_LOCK_INFO_AUTONOMOUS(
787 p_object_key IN varchar2
788 ,p_object_type IN varchar2
789 ,p_lock_type IN varchar2
790 ,p_last_save_time IN date
791 ,p_session_id IN number
792 ,x_return_status OUT NOCOPY varchar2
793 ,x_msg_count OUT NOCOPY number
794 ,x_msg_data OUT NOCOPY varchar2
795 );
796
797
798 /*------------------------------------------------------------------------------------------
799 Procedure UPDATE_LOCK_INFO
800 This procedure updates the lock table
801 <parameters>
802 p_object_key: The primary key of the Object, usually the TO_CHAR value
803 of the Object ID. If the Object has composite keys,
804 the value to pass in will be a concatenation of
805 all the keys, separated by commas
806 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
807 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
808 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
809 "PERIODICITY", and "TABLE"
810 p_lock_type: 'W' for write lock, 'R' for read lock
811 p_last_save_time: The last time the Object being modified
812 p_session_id: The Database Session ID
813 -------------------------------------------------------------------------------------------*/
814 Procedure UPDATE_LOCK_INFO(
815 p_object_key IN varchar2
816 ,p_object_type IN varchar2
817 ,p_lock_type IN varchar2
818 ,p_last_save_time IN date
819 ,p_session_id IN number
820 ,x_return_status OUT NOCOPY varchar2
821 ,x_msg_count OUT NOCOPY number
822 ,x_msg_data OUT NOCOPY varchar2
823 );
824
825
826 /*------------------------------------------------------------------------------------------
827 Procedure UPDATE_LOCK_INFO_AUTONOMOUS
828 This procedure updates the lock table
829 <parameters>
830 p_object_key: The primary key of the Object, usually the TO_CHAR value
831 of the Object ID. If the Object has composite keys,
832 the value to pass in will be a concatenation of
833 all the keys, separated by commas
834 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
835 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
836 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
837 "PERIODICITY", and "TABLE"
838 p_lock_type: 'W' for write lock, 'R' for read lock
839 p_last_save_time: The last time the Object being modified
843 p_object_key IN varchar2
840 p_session_id: The Database Session ID
841 -------------------------------------------------------------------------------------------*/
842 Procedure UPDATE_LOCK_INFO_AUTONOMOUS(
844 ,p_object_type IN varchar2
845 ,p_lock_type IN varchar2
846 ,p_last_save_time IN date
847 ,p_session_id IN number
848 ,x_return_status OUT NOCOPY varchar2
849 ,x_msg_count OUT NOCOPY number
850 ,x_msg_data OUT NOCOPY varchar2
851 );
852
853
854 /*------------------------------------------------------------------------------------------
855 Procedure INSERT_USER_INFO
856 This procedure inserts a row into the lock user table
857 <parameters>
858 p_object_key: The primary key of the Object, usually the TO_CHAR value
859 of the Object ID. If the Object has composite keys,
860 the value to pass in will be a concatenation of
861 all the keys, separated by commas
862 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
863 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
864 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
865 "PERIODICITY", and "TABLE"
866 p_user_type: "L" = Lock, "M" = Modify
867 p_program_id: -100 = Data Loader UI
868 -101 = Data Loader Backend
869 -200 = Generate Database
870 -201 = Generate Documentation
871 -202 = Rename Interface Table
872 -203 = Generate Database Configuration
873 -300 = Administrator
874 -400 = Objective Designer
875 -500 = Builder
876 -600 = Performance Scorecard
877 -700 = System Upgrade
878 -800 = System Migration
879 p_user_id: Application User ID
880 p_machine: The Machine
881 p_terminal: The Terminal
882 -------------------------------------------------------------------------------------------*/
883 Procedure INSERT_USER_INFO(
884 p_object_key IN varchar2
885 ,p_object_type IN varchar2
886 ,p_user_type IN varchar2
887 ,p_program_id IN number
888 ,p_user_id IN number
889 ,p_machine IN varchar2
890 ,p_terminal IN varchar2
891 ,x_return_status OUT NOCOPY varchar2
892 ,x_msg_count OUT NOCOPY number
893 ,x_msg_data OUT NOCOPY varchar2
894 );
895
896
897 /*------------------------------------------------------------------------------------------
898 Procedure INSERT_USER_INFO_AUTONOMOUS
899 This procedure inserts a row into the lock user table
900 <parameters>
901 p_object_key: The primary key of the Object, usually the TO_CHAR value
902 of the Object ID. If the Object has composite keys,
903 the value to pass in will be a concatenation of
904 all the keys, separated by commas
905 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
906 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
907 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
908 "PERIODICITY", and "TABLE"
909 p_user_type: "L" = Lock, "M" = Modify
910 p_program_id: -100 = Data Loader UI
911 -101 = Data Loader Backend
912 -200 = Generate Database
913 -201 = Generate Documentation
914 -202 = Rename Interface Table
915 -203 = Generate Database Configuration
916 -300 = Administrator
917 -400 = Objective Designer
918 -500 = Builder
919 -600 = Performance Scorecard
920 -700 = System Upgrade
921 -800 = System Migration
922 p_user_id: Application User ID
923 p_machine: The Machine
924 p_terminal: The Terminal
925 -------------------------------------------------------------------------------------------*/
926 Procedure INSERT_USER_INFO_AUTONOMOUS(
927 p_object_key IN varchar2
928 ,p_object_type IN varchar2
929 ,p_user_type IN varchar2
930 ,p_program_id IN number
931 ,p_user_id IN number
932 ,p_machine IN varchar2
933 ,p_terminal IN varchar2
934 ,x_return_status OUT NOCOPY varchar2
935 ,x_msg_count OUT NOCOPY number
936 ,x_msg_data OUT NOCOPY varchar2
937 );
938
939
940 /*------------------------------------------------------------------------------------------
941 Procedure UPDATE_USER_INFO
942 This procedure updates the current user info
943 <parameters>
944 p_object_key: The primary key of the Object, usually the TO_CHAR value
945 of the Object ID. If the Object has composite keys,
946 the value to pass in will be a concatenation of
947 all the keys, separated by commas
948 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
949 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
950 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
951 "PERIODICITY", and "TABLE"
955 -200 = Generate Database
952 p_user_type: "L" = Lock, "M" = Modify
953 p_program_id: -100 = Data Loader UI
954 -101 = Data Loader Backend
956 -201 = Generate Documentation
957 -202 = Rename Interface Table
958 -203 = Generate Database Configuration
959 -300 = Administrator
960 -400 = Objective Designer
961 -500 = Builder
962 -600 = Performance Scorecard
963 -700 = System Upgrade
964 -800 = System Migration
965 p_user_id: Application User ID
966 p_machine: The Machine
967 p_terminal: The Terminal
968 -------------------------------------------------------------------------------------------*/
969 Procedure UPDATE_USER_INFO(
970 p_object_key IN varchar2
971 ,p_object_type IN varchar2
972 ,p_user_type IN varchar2
973 ,p_program_id IN number
974 ,p_user_id IN number
975 ,p_machine IN varchar2
976 ,p_terminal IN varchar2
977 ,x_return_status OUT NOCOPY varchar2
978 ,x_msg_count OUT NOCOPY number
979 ,x_msg_data OUT NOCOPY varchar2
980 );
981
982
983 /*------------------------------------------------------------------------------------------
984 Procedure UPDATE_USER_INFO_AUTONOMOUS
985 This procedure updates the current user info
986 <parameters>
987 p_object_key: The primary key of the Object, usually the TO_CHAR value
988 of the Object ID. If the Object has composite keys,
989 the value to pass in will be a concatenation of
990 all the keys, separated by commas
991 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
992 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
993 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
994 "PERIODICITY", and "TABLE"
995 p_user_type: "L" = Lock, "M" = Modify
996 p_program_id: -100 = Data Loader UI
997 -101 = Data Loader Backend
998 -200 = Generate Database
999 -201 = Generate Documentation
1000 -202 = Rename Interface Table
1001 -203 = Generate Database Configuration
1002 -300 = Administrator
1003 -400 = Objective Designer
1004 -500 = Builder
1005 -600 = Performance Scorecard
1006 -700 = System Upgrade
1007 -800 = System Migration
1008 p_user_id: Application User ID
1009 p_machine: The Machine
1010 p_terminal: The Terminal
1011 -------------------------------------------------------------------------------------------*/
1012 Procedure UPDATE_USER_INFO_AUTONOMOUS(
1013 p_object_key IN varchar2
1014 ,p_object_type IN varchar2
1015 ,p_user_type IN varchar2
1016 ,p_program_id IN number
1017 ,p_user_id IN number
1018 ,p_machine IN varchar2
1019 ,p_terminal IN varchar2
1020 ,x_return_status OUT NOCOPY varchar2
1021 ,x_msg_count OUT NOCOPY number
1022 ,x_msg_data OUT NOCOPY varchar2
1023 );
1024
1025
1026 /*------------------------------------------------------------------------------------------
1027 Procedure DELETE_LOCK_INFO
1028 This procedure deletes the lock object
1029 <parameters>
1030 p_object_key: The primary key of the Object, usually the TO_CHAR value
1031 of the Object ID. If the Object has composite keys,
1032 the value to pass in will be a concatenation of
1033 all the keys, separated by commas
1034 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1035 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1036 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1037 "PERIODICITY", and "TABLE"
1038 p_program_id: -100 = Data Loader UI
1039 -101 = Data Loader Backend
1040 -200 = Generate Database
1041 -201 = Generate Documentation
1042 -202 = Rename Interface Table
1043 -203 = Generate Database Configuration
1044 -300 = Administrator
1045 -400 = Objective Designer
1046 -500 = Builder
1047 -600 = Performance Scorecard
1048 -700 = System Upgrade
1049 -800 = System Migration
1050 p_user_id: Application User ID
1051 p_machine: The Machine
1052 p_terminal: The Terminal
1053 -------------------------------------------------------------------------------------------*/
1054 Procedure DELETE_LOCK_INFO(
1055 p_object_key IN varchar2
1056 ,p_object_type IN varchar2
1057 ,p_program_id IN number
1058 ,p_user_id IN number
1059 ,p_machine IN varchar2
1060 ,p_terminal IN varchar2
1061 ,x_return_status OUT NOCOPY varchar2
1062 ,x_msg_count OUT NOCOPY number
1063 ,x_msg_data OUT NOCOPY varchar2
1064 );
1065
1066
1067 /*------------------------------------------------------------------------------------------
1068 Procedure DELETE_LOCK_INFO_AUTONOMOUS
1069 This procedure deletes the lock object
1070 <parameters>
1071 p_object_key: The primary key of the Object, usually the TO_CHAR value
1072 of the Object ID. If the Object has composite keys,
1073 the value to pass in will be a concatenation of
1074 all the keys, separated by commas
1075 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1076 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1077 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1078 "PERIODICITY", and "TABLE"
1079 p_program_id: -100 = Data Loader UI
1080 -101 = Data Loader Backend
1081 -200 = Generate Database
1082 -201 = Generate Documentation
1083 -202 = Rename Interface Table
1084 -203 = Generate Database Configuration
1085 -300 = Administrator
1086 -400 = Objective Designer
1087 -500 = Builder
1088 -600 = Performance Scorecard
1089 -700 = System Upgrade
1090 -800 = System Migration
1091 p_user_id: Application User ID
1092 p_machine: The Machine
1093 p_terminal: The Terminal
1094 -------------------------------------------------------------------------------------------*/
1095 Procedure DELETE_LOCK_INFO_AUTONOMOUS(
1096 p_object_key IN varchar2
1097 ,p_object_type IN varchar2
1098 ,p_program_id IN number
1099 ,p_user_id IN number
1100 ,p_machine IN varchar2
1101 ,p_terminal IN varchar2
1102 ,x_return_status OUT NOCOPY varchar2
1103 ,x_msg_count OUT NOCOPY number
1104 ,x_msg_data OUT NOCOPY varchar2
1105 );
1106
1107
1108 /*------------------------------------------------------------------------------------------
1109 Procedure RAISE_EXCEPTION
1110 This procedure retrieves the session information and raises an exception
1111 <parameters>
1112 p_object_key: The primary key of the Object, usually the TO_CHAR value
1113 of the Object ID. If the Object has composite keys,
1114 the value to pass in will be a concatenation of
1115 all the keys, separated by commas
1116 p_object_type: Either "OVERVIEW_PAGE", "SCORECARD", "CUSTOM_VIEW",
1117 "LAUNCHPAD", "OBJECTIVE", "MEASURE", "DATA_COLUMN",
1118 "DIMENSION", "DIMENSION_OBJECT", "REPORT", "CALENDAR",
1119 "PERIODICITY", and "TABLE"
1120 p_exception_type: "L" = BSC_LOCK_ERR_LOCKED, "M" = BSC_LOCK_ERR_MODIFIED
1121 -------------------------------------------------------------------------------------------*/
1122 Procedure RAISE_EXCEPTION(
1123 p_object_key IN varchar2
1124 ,p_object_type IN varchar2
1125 ,p_exception_type IN varchar2
1126 ,x_return_status OUT NOCOPY varchar2
1127 ,x_msg_count OUT NOCOPY number
1128 ,x_msg_data OUT NOCOPY varchar2
1129 );
1130
1131
1132 /*------------------------------------------------------------------------------------------
1133 Procedure GET_SESSION
1134 This procedure retrieves the session information (machine, terminal, etc.)
1135 <parameters>
1136 x_machine: The machine
1137 x_terminal: The terminal
1138 -------------------------------------------------------------------------------------------*/
1139 Procedure GET_SESSION(
1140 x_machine OUT NOCOPY varchar2
1141 ,x_terminal OUT NOCOPY varchar2
1142 ,x_return_status OUT NOCOPY varchar2
1143 ,x_msg_count OUT NOCOPY number
1144 ,x_msg_data OUT NOCOPY varchar2
1145 );
1146
1147
1148 /*------------------------------------------------------------------------------------------
1149 Procedure INITIALIZE
1150
1151 DESCRIPTION:
1152 Populate global variables
1153 -------------------------------------------------------------------------------------------*/
1154 Procedure INITIALIZE;
1155
1156
1157 /*------------------------------------------------------------------------------------------
1158 Function GET_BSC_SCHEMA
1159
1160 DESCRIPTION:
1161 Returns the BSC schema name
1162 -------------------------------------------------------------------------------------------*/
1163 Function GET_BSC_SCHEMA
1164 return VARCHAR2;
1165
1166 /*------------------------------------------------------------------------------------------
1167 -------------------------------------------------------------------------------------------*/
1168 END BSC_LOCKS_PVT;
[Home] [Help]