[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_LOCK
Source
1 PACKAGE BODY BSC_UPDATE_LOCK AS
2 /* $Header: BSCDLCKB.pls 120.2 2005/08/05 09:40:29 meastmon noship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION Lock_AW_Indicator_Cubes
7 +============================================================================*/
8 FUNCTION Lock_AW_Indicator_Cubes(
9 x_indicator IN NUMBER
10 ) RETURN BOOLEAN IS
11 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
12 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
13 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
14 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
15 h_num_objects NUMBER;
16 h_b BOOLEAN;
17 h_i NUMBER;
18 BEGIN
19 h_num_objects := 0;
20
21 h_num_objects := h_num_objects + 1;
22 h_object_keys(h_num_objects) := x_indicator;
23 h_object_types(h_num_objects) := 'AW_INDICATOR_CUBES';
24 h_lock_types(h_num_objects) := 'W';
25 h_cascade_levels(h_num_objects) := 0;
26
27 FOR h_i IN 1..h_num_objects LOOP
28 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
29 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
30 BSC_UPDATE_LOG.OUTPUT);
31 END LOOP;
32
33 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
34
35 RETURN h_b;
36 END Lock_AW_Indicator_Cubes;
37
38
39 /*===========================================================================+
40 | FUNCTION Lock_Calendar
41 +============================================================================*/
42 FUNCTION Lock_Calendar (
43 x_calendar_id IN NUMBER
44 ) RETURN BOOLEAN IS
45 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
46 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
47 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
48 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
49 h_num_objects NUMBER;
50 h_b BOOLEAN;
51 h_i NUMBER;
52
53 CURSOR c_periodicities IS
54 SELECT periodicity_id
55 FROM bsc_sys_periodicities
56 WHERE calendar_id = x_calendar_id;
57
58 h_periodicity_id NUMBER;
59
60 BEGIN
61 h_num_objects := 0;
62
63 -- Lock Calendar
64 h_num_objects := h_num_objects + 1;
65 h_object_keys(h_num_objects) := x_calendar_id;
66 h_object_types(h_num_objects) := 'CALENDAR';
67 h_lock_types(h_num_objects) := 'W';
68 h_cascade_levels(h_num_objects) := 0;
69
70 -- Lock all the periodicities of this calendar
71 OPEN c_periodicities;
72 LOOP
73 FETCH c_periodicities INTO h_periodicity_id;
74 EXIT WHEN c_periodicities%NOTFOUND;
75
76 h_num_objects := h_num_objects + 1;
77 h_object_keys(h_num_objects) := h_periodicity_id;
78 h_object_types(h_num_objects) := 'PERIODICITY';
79 h_lock_types(h_num_objects) := 'W';
80 h_cascade_levels(h_num_objects) := 0;
81 END LOOP;
82 CLOSE c_periodicities;
83
84 FOR h_i IN 1..h_num_objects LOOP
85 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
86 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
87 BSC_UPDATE_LOG.OUTPUT);
88 END LOOP;
89
90 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
91
92 RETURN h_b;
93 END Lock_Calendar;
94
95
96 /*===========================================================================+
97 | FUNCTION Lock_Calendar_Change
98 +============================================================================*/
99 FUNCTION Lock_Calendar_Change (
100 x_calendar_id IN NUMBER
101 ) RETURN BOOLEAN IS
102 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
103 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
104 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
105 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
106 h_num_objects NUMBER;
107 h_b BOOLEAN;
108 h_i NUMBER;
109
110 CURSOR c_periodicities IS
111 SELECT periodicity_id
112 FROM bsc_sys_periodicities
113 WHERE calendar_id = x_calendar_id;
114
115 h_periodicity_id NUMBER;
116
117 CURSOR c_indics IS
118 SELECT indicator
119 FROM bsc_kpis_b
120 WHERE calendar_id = x_calendar_id;
121
122 h_indicator NUMBER;
123
124 CURSOR c_tables IS
125 SELECT table_name
126 FROM bsc_db_tables
127 WHERE periodicity_id IN (
128 SELECT periodicity_id
129 FROM bsc_sys_periodicities
130 WHERE calendar_id = x_calendar_id
131 );
132
133 h_table_name VARCHAR2(50);
134
135 BEGIN
136 h_num_objects := 0;
137
138 -- Lock Calendar
139 h_num_objects := h_num_objects + 1;
140 h_object_keys(h_num_objects) := x_calendar_id;
141 h_object_types(h_num_objects) := 'CALENDAR';
142 h_lock_types(h_num_objects) := 'W';
143 h_cascade_levels(h_num_objects) := 0;
144
145 -- Lock all the periodicities of this calendar
146 OPEN c_periodicities;
147 LOOP
148 FETCH c_periodicities INTO h_periodicity_id;
149 EXIT WHEN c_periodicities%NOTFOUND;
150
151 h_num_objects := h_num_objects + 1;
152 h_object_keys(h_num_objects) := h_periodicity_id;
153 h_object_types(h_num_objects) := 'PERIODICITY';
154 h_lock_types(h_num_objects) := 'W';
155 h_cascade_levels(h_num_objects) := 0;
156 END LOOP;
157 CLOSE c_periodicities;
158
159 --Lock the indicator period and indicator color of the indicators using this calendar
160 OPEN c_indics;
161 LOOP
162 FETCH c_indics INTO h_indicator;
163 EXIT WHEN c_indics%NOTFOUND;
164
165 h_num_objects := h_num_objects + 1;
166 h_object_keys(h_num_objects) := h_indicator;
167 h_object_types(h_num_objects) := 'INDICATOR_PERIOD';
168 h_lock_types(h_num_objects) := 'W';
169 h_cascade_levels(h_num_objects) := 0;
170
171 h_num_objects := h_num_objects + 1;
172 h_object_keys(h_num_objects) := h_indicator;
173 h_object_types(h_num_objects) := 'INDICATOR_COLOR';
174 h_lock_types(h_num_objects) := 'W';
175 h_cascade_levels(h_num_objects) := 0;
176 END LOOP;
177 CLOSE c_indics;
178
179 --Lock the tables using this calendar
180 OPEN c_tables;
181 LOOP
182 FETCH c_tables INTO h_table_name;
183 EXIT WHEN c_tables%NOTFOUND;
184
185 h_num_objects := h_num_objects + 1;
186 h_object_keys(h_num_objects) := h_table_name;
187 h_object_types(h_num_objects) := 'TABLE';
188 h_lock_types(h_num_objects) := 'W';
189 h_cascade_levels(h_num_objects) := 0;
190 END LOOP;
191 CLOSE c_tables;
192
193 FOR h_i IN 1..h_num_objects LOOP
194 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
195 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
196 BSC_UPDATE_LOG.OUTPUT);
197 END LOOP;
198
199 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
200
201 RETURN h_b;
202 END Lock_Calendar_Change;
203
204
205 /*===========================================================================+
206 | FUNCTION Lock_Color_Indicator
207 +============================================================================*/
208 FUNCTION Lock_Color_Indicator(
209 x_indicator IN NUMBER
210 ) RETURN BOOLEAN IS
211 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
212 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
213 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
214 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
215 h_num_objects NUMBER;
216 h_b BOOLEAN;
217 h_i NUMBER;
218
219 CURSOR c_sum_tables IS
220 select distinct table_name
221 from bsc_kpi_data_tables
222 where indicator = x_indicator and table_name is not null;
223
224 h_table_name VARCHAR2(50);
225
226 BEGIN
227 h_num_objects := 0;
228
229 -- Lock the color of the indicator
230 h_num_objects := h_num_objects + 1;
231 h_object_keys(h_num_objects) := x_indicator;
232 h_object_types(h_num_objects) := 'INDICATOR_COLOR';
233 h_lock_types(h_num_objects) := 'W';
234 h_cascade_levels(h_num_objects) := 0;
235
236 -- Lock in READ mode the indicators summary tables
237 -- Remember the when we are refreshing MVs we are locking the summary tables not the MVs
238 -- If the indicator is implemented in AW we need to lock the AW cubes of the indicator
239 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_indicator) = 2 THEN
240 --AW indicator
241 h_num_objects := h_num_objects + 1;
242 h_object_keys(h_num_objects) := x_indicator;
243 h_object_types(h_num_objects) := 'AW_INDICATOR_CUBES';
244 h_lock_types(h_num_objects) := 'R';
245 h_cascade_levels(h_num_objects) := 0;
246 END IF;
247
248 OPEN c_sum_tables;
249 LOOP
250 FETCH c_sum_tables INTO h_table_name;
251 EXIT WHEN c_sum_tables%NOTFOUND;
252
253 h_num_objects := h_num_objects + 1;
254 h_object_keys(h_num_objects) := h_table_name;
255 h_object_types(h_num_objects) := 'TABLE';
256 h_lock_types(h_num_objects) := 'R';
257 h_cascade_levels(h_num_objects) := 0;
258 END LOOP;
259 CLOSE c_sum_tables;
260
261 FOR h_i IN 1..h_num_objects LOOP
262 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
263 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
264 BSC_UPDATE_LOG.OUTPUT);
265 END LOOP;
266
267 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
268
269 RETURN h_b;
270 END Lock_Color_Indicator;
271
272
273 /*===========================================================================+
274 | FUNCTION Lock_Color_Indicators
275 +============================================================================*/
276 FUNCTION Lock_Color_Indicators(
277 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
278 x_num_indicators IN NUMBER
279 ) RETURN BOOLEAN IS
280 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
281 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
282 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
283 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
284 h_num_objects NUMBER;
285 h_b BOOLEAN;
286 h_i NUMBER;
287 BEGIN
288 h_num_objects := 0;
289
290 FOR h_i IN 1..x_num_indicators LOOP
291 -- Lock the table
292 h_num_objects := h_num_objects + 1;
293 h_object_keys(h_num_objects) := x_indicators(h_i);
294 h_object_types(h_num_objects) := 'INDICATOR_COLOR';
295 h_lock_types(h_num_objects) := 'W';
296 h_cascade_levels(h_num_objects) := 0;
297 END LOOP;
298
299 FOR h_i IN 1..h_num_objects LOOP
300 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
301 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
302 BSC_UPDATE_LOG.OUTPUT);
303 END LOOP;
304
305 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
306
307 RETURN h_b;
308 END Lock_Color_Indicators;
309
310
311 /*===========================================================================+
312 | FUNCTION Lock_DBI_Dimension
313 +============================================================================*/
314 FUNCTION Lock_DBI_Dimension(
315 x_dim_short_name IN VARCHAR2
316 ) RETURN BOOLEAN IS
317 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
318 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
319 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
320 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
321 h_num_objects NUMBER;
322 h_b BOOLEAN;
323 h_i NUMBER;
324
325 CURSOR c_dim_level_id IS
326 SELECT dim_level_id
327 FROM bsc_sys_dim_levels_b
328 WHERE short_name = x_dim_short_name;
329
330 h_dim_level_id VARCHAR2(50);
331
332 BEGIN
333 h_num_objects := 0;
334
335 OPEN c_dim_level_id;
336 FETCH c_dim_level_id INTO h_dim_level_id;
337 IF c_dim_level_id%NOTFOUND THEN
338 CLOSE c_dim_level_id;
339 RETURN FALSE;
340 END IF;
341 CLOSE c_dim_level_id;
342
343 h_num_objects := h_num_objects + 1;
344 h_object_keys(h_num_objects) := h_dim_level_id;
345 h_object_types(h_num_objects) := 'DIMENSION_OBJECT';
346 h_lock_types(h_num_objects) := 'W';
347 h_cascade_levels(h_num_objects) := 0;
348
349 FOR h_i IN 1..h_num_objects LOOP
350 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
351 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
352 BSC_UPDATE_LOG.OUTPUT);
353 END LOOP;
354
355 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
356
357 RETURN h_b;
358 END Lock_DBI_Dimension;
359
360
361 /*===========================================================================+
362 | FUNCTION Lock_Import_Dbi_Plans
363 +============================================================================*/
364 FUNCTION Lock_Import_Dbi_Plans RETURN BOOLEAN IS
365 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
366 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
367 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
368 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
369 h_num_objects NUMBER;
370 h_b BOOLEAN;
371 h_i NUMBER;
372 BEGIN
373 h_num_objects := 0;
374
375 -- Lock BSC_SYS_BENCHMARKS
376 h_num_objects := h_num_objects + 1;
377 h_object_keys(h_num_objects) := 'BSC_SYS_BENCHMARKS';
378 h_object_types(h_num_objects) := 'TABLE';
379 h_lock_types(h_num_objects) := 'W';
380 h_cascade_levels(h_num_objects) := 0;
381
382 FOR h_i IN 1..h_num_objects LOOP
383 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
384 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
385 BSC_UPDATE_LOG.OUTPUT);
386 END LOOP;
387
388 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
389
390 RETURN h_b;
391 END Lock_Import_Dbi_Plans;
392
393
394 /*===========================================================================+
395 | FUNCTION Lock_Import_ITable
396 +============================================================================*/
397 FUNCTION Lock_Import_ITable(
398 x_input_table IN VARCHAR2
399 ) RETURN BOOLEAN IS
400 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
401 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
402 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
403 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
404 h_num_objects NUMBER;
405 h_b BOOLEAN;
406 h_i NUMBER;
407 BEGIN
408 h_num_objects := 0;
409
410 -- Lock the input table
411 h_num_objects := h_num_objects + 1;
412 h_object_keys(h_num_objects) := x_input_table;
413 h_object_types(h_num_objects) := 'TABLE';
414 h_lock_types(h_num_objects) := 'W';
415 h_cascade_levels(h_num_objects) := 0;
416
417 FOR h_i IN 1..h_num_objects LOOP
418 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
419 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
420 BSC_UPDATE_LOG.OUTPUT);
421 END LOOP;
422
423 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
424
425 RETURN h_b;
426 END Lock_Import_ITable;
427
428
429 /*===========================================================================+
430 | FUNCTION Lock_Incremental_Indicators
431 +============================================================================*/
432 FUNCTION Lock_Incremental_Indicators RETURN BOOLEAN IS
433 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
434 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
435 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
436 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
437 h_num_objects NUMBER;
438 h_b BOOLEAN;
439 h_i NUMBER;
440
441 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
442 h_num_indicators NUMBER;
443 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
444 h_num_input_tables NUMBER;
445
446 CURSOR c_mod_cal_kpis IS
447 select distinct indicator
448 from bsc_kpis_vl
449 where calendar_id in (
450 select calendar_id
451 from bsc_sys_calendars_b
452 where fiscal_change = 1
453 );
454
455 h_indicator NUMBER;
456
457 TYPE t_cursor IS REF CURSOR;
458 c_indicators t_cursor;
459 h_sql VARCHAR2(32000);
460 h_where_indics VARCHAR2(32000);
461
462 BEGIN
463 h_num_objects := 0;
464 h_num_indicators := 0;
465 h_num_input_tables := 0;
466
467 -- Lock indicators using modified calendars with fiscal change =1
468 OPEN c_mod_cal_kpis;
469 LOOP
470 FETCH c_mod_cal_kpis INTO h_indicator;
471 EXIT WHEN c_mod_cal_kpis%NOTFOUND;
472
473 h_num_objects := h_num_objects + 1;
474 h_object_keys(h_num_objects) := h_indicator;
475 h_object_types(h_num_objects) := 'OBJECTIVE';
476 h_lock_types(h_num_objects) := 'R';
477 h_cascade_levels(h_num_objects) := -1;
478 END LOOP;
479 CLOSE c_mod_cal_kpis;
480
481 -- Lock indicators with prototype flag 6 or 7
482 -- If Loader is running only for some specific indicators then we need to lock
483 -- only those indicators
484 h_sql := 'SELECT DISTINCT indicator'||
485 ' FROM bsc_kpis_vl'||
486 ' WHERE prototype_flag IN (6,7)';
487 IF BSC_UPDATE.g_kpi_mode THEN
488 IF BSC_UPDATE.g_num_indicators > 0 THEN
489 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
490 FOR h_i IN 1..BSC_UPDATE.g_num_indicators LOOP
491 BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
492 END LOOP;
493 h_sql := h_sql||' AND ('||h_where_indics||')';
494 END IF;
495 END IF;
496
497 OPEN c_indicators FOR h_sql;
498 LOOP
499 FETCH c_indicators INTO h_indicator;
500 EXIT WHEN c_indicators%NOTFOUND;
501
502 -- We are not going to consider the indicators affected by modified calendars.
503 -- The data of those indicators will be deleted
504 -- and the prototype flag will be set to 0. So they are not going to be recalculated
505 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_indicator, h_object_keys, h_num_objects) THEN
506 h_num_indicators := h_num_indicators + 1;
507 h_indicators(h_num_indicators) := h_indicator;
508 END IF;
509 END LOOP;
510 CLOSE c_indicators;
511
512 -- Get the affected indicators too
513 IF NOT BSC_UPDATE.Get_Input_Tables_Kpis(h_indicators, h_num_indicators, h_input_tables, h_num_input_tables) THEN
514 RETURN FALSE;
515 END IF;
516
517 IF NOT BSC_UPDATE.get_kpi_for_input_tables(h_input_tables,h_num_input_tables,h_indicators,h_num_indicators) THEN
518 RETURN FALSE;
519 END IF;
520
521 FOR h_i IN 1..h_num_indicators LOOP
522 IF BSC_UPDATE.g_kpi_mode THEN
523 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators(h_i),
524 BSC_UPDATE.g_indicators,
525 BSC_UPDATE.g_num_indicators) THEN
526 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_indicators(h_i), h_object_keys, h_num_objects) THEN
527 h_num_objects := h_num_objects + 1;
528 h_object_keys(h_num_objects) := h_indicators(h_i);
529 h_object_types(h_num_objects) := 'OBJECTIVE';
530 h_lock_types(h_num_objects) := 'R';
531 h_cascade_levels(h_num_objects) := -1;
532 END IF;
533 END IF;
534 ELSE
535 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_indicators(h_i), h_object_keys, h_num_objects) THEN
536 h_num_objects := h_num_objects + 1;
537 h_object_keys(h_num_objects) := h_indicators(h_i);
538 h_object_types(h_num_objects) := 'OBJECTIVE';
539 h_lock_types(h_num_objects) := 'R';
540 h_cascade_levels(h_num_objects) := -1;
541 END IF;
542 END IF;
543 END LOOP;
544
545 FOR h_i IN 1..h_num_objects LOOP
546 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
547 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
548 BSC_UPDATE_LOG.OUTPUT);
549 END LOOP;
550
551 --need to commit because it used bsc_tmp_big_in_cond
552 commit;
553
554 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
555
556 RETURN h_b;
557 END Lock_Incremental_Indicators;
558
559
560 /*===========================================================================+
561 | FUNCTION Lock_Indicators
562 +============================================================================*/
563 FUNCTION Lock_Indicators (
564 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
565 x_num_input_tables IN NUMBER
566 ) RETURN BOOLEAN IS
567 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
568 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
569 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
570 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
571 h_num_objects NUMBER;
572 h_b BOOLEAN;
573 h_i NUMBER;
574
575 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
576 h_num_indicators NUMBER;
577 BEGIN
578 h_num_objects := 0;
579 h_num_indicators := 0;
580
581 IF NOT BSC_UPDATE.get_kpi_for_input_tables(x_input_tables,x_num_input_tables,h_indicators,h_num_indicators) THEN
582 RETURN FALSE;
583 END IF;
584
585 FOR h_i IN 1..h_num_indicators LOOP
586 IF BSC_UPDATE.g_kpi_mode THEN
587 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators(h_i),
588 BSC_UPDATE.g_indicators,
589 BSC_UPDATE.g_num_indicators) THEN
590 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_indicators(h_i), h_object_keys, h_num_objects) THEN
591 h_num_objects := h_num_objects + 1;
592 h_object_keys(h_num_objects) := h_indicators(h_i);
593 h_object_types(h_num_objects) := 'OBJECTIVE';
594 h_lock_types(h_num_objects) := 'R';
595 h_cascade_levels(h_num_objects) := -1;
596 END IF;
597 END IF;
598 ELSE
599 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_indicators(h_i), h_object_keys, h_num_objects) THEN
600 h_num_objects := h_num_objects + 1;
601 h_object_keys(h_num_objects) := h_indicators(h_i);
602 h_object_types(h_num_objects) := 'OBJECTIVE';
603 h_lock_types(h_num_objects) := 'R';
604 h_cascade_levels(h_num_objects) := -1;
605 END IF;
606 END IF;
607 END LOOP;
608
609 FOR h_i IN 1..h_num_objects LOOP
610 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
611 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
612 BSC_UPDATE_LOG.OUTPUT);
613 END LOOP;
614
615 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
616
617 RETURN h_b;
618 END Lock_Indicators;
619
620
621 /*===========================================================================+
622 | FUNCTION Lock_Indicators_by_Calendar
623 +============================================================================*/
624 FUNCTION Lock_Indicators_by_Calendar (
625 x_calendars IN BSC_UPDATE_UTIL.t_array_of_number,
626 x_num_calendars IN NUMBER
627 ) RETURN BOOLEAN IS
628 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
629 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
630 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
631 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
632 h_num_objects NUMBER;
633 h_b BOOLEAN;
634 h_i NUMBER;
635
636 CURSOR c_indics (p_calendar_id NUMBER) IS
637 select indicator
638 from bsc_kpis_b
639 where calendar_id = p_calendar_id;
640
641 h_indicator NUMBER;
642
643 BEGIN
644 h_num_objects := 0;
645
646 FOR h_i IN 1..x_num_calendars LOOP
647 OPEN c_indics(x_calendars(h_i));
648 LOOP
649 FETCH c_indics INTO h_indicator;
650 EXIT WHEN c_indics%NOTFOUND;
651
652 h_num_objects := h_num_objects + 1;
653 h_object_keys(h_num_objects) := h_indicator;
654 h_object_types(h_num_objects) := 'OBJECTIVE';
655 h_lock_types(h_num_objects) := 'R';
656 h_cascade_levels(h_num_objects) := -1;
657 END LOOP;
658 CLOSE c_indics;
659 END LOOP;
660
661 FOR h_i IN 1..h_num_objects LOOP
662 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
663 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
664 BSC_UPDATE_LOG.OUTPUT);
665 END LOOP;
666
667 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
668
669 RETURN h_b;
670 END Lock_Indicators_by_Calendar;
671
672
673 /*===========================================================================+
674 | FUNCTION Lock_Indicators_To_Delete
675 +============================================================================*/
676 FUNCTION Lock_Indicators_To_Delete (
677 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
678 x_num_indicators IN NUMBER
679 ) RETURN BOOLEAN IS
680 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
681 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
682 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
683 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
684 h_num_objects NUMBER;
685 h_b BOOLEAN;
686 h_i NUMBER;
687
688 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
689 h_num_indicators NUMBER;
690 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
691 h_num_input_tables NUMBER;
692
693 TYPE t_cursor IS REF CURSOR;
694 c_indicators t_cursor;
695 h_sql VARCHAR2(32000);
696 h_where_indics VARCHAR2(32000);
697
698 BEGIN
699 h_num_objects := 0;
700 h_num_indicators := 0;
701 h_num_input_tables := 0;
702
703 -- Lock given indicators
704 FOR h_i IN 1..x_num_indicators LOOP
705 h_num_objects := h_num_objects + 1;
706 h_object_keys(h_num_objects) := x_indicators(h_i);
707 h_object_types(h_num_objects) := 'OBJECTIVE';
708 h_lock_types(h_num_objects) := 'R';
709 h_cascade_levels(h_num_objects) := -1;
710
711 h_num_indicators := h_num_indicators + 1;
712 h_indicators(h_num_indicators) := x_indicators(h_i);
713 END LOOP;
714
715 -- Lock the affected indicators too
716 IF NOT BSC_UPDATE.Get_Input_Tables_Kpis(h_indicators, h_num_indicators, h_input_tables, h_num_input_tables) THEN
717 RETURN FALSE;
718 END IF;
719
720 IF NOT BSC_UPDATE.get_kpi_for_input_tables(h_input_tables,h_num_input_tables,h_indicators,h_num_indicators) THEN
721 RETURN FALSE;
722 END IF;
723
724 FOR h_i IN 1..h_num_indicators LOOP
725 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators(h_i), x_indicators, x_num_indicators) THEN
726 h_num_objects := h_num_objects + 1;
727 h_object_keys(h_num_objects) := h_indicators(h_i);
728 h_object_types(h_num_objects) := 'OBJECTIVE';
729 h_lock_types(h_num_objects) := 'R';
730 h_cascade_levels(h_num_objects) := -1;
731 END IF;
732 END LOOP;
733
734 FOR h_i IN 1..h_num_objects LOOP
735 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
736 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
737 BSC_UPDATE_LOG.OUTPUT);
738 END LOOP;
739
740 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
741
742 RETURN h_b;
743 END Lock_Indicators_To_Delete;
744
745
746 /*===========================================================================+
747 | FUNCTION Lock_Load_Dimension_Table
748 +============================================================================*/
749 FUNCTION Lock_Load_Dimension_Table (
750 x_dim_table IN VARCHAR2,
751 x_input_table IN VARCHAR2
752 ) RETURN BOOLEAN IS
753 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
754 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
755 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
756 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
757 h_num_objects NUMBER;
758 h_b BOOLEAN;
759 h_i NUMBER;
760 h_dim_table_type NUMBER;
761 h_dim_level_id NUMBER;
762
763 CURSOR c_parent_dims IS
764 SELECT dp.dim_level_id
765 FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
766 WHERE d.dim_level_id = r.dim_level_id AND
767 r.parent_dim_level_id = dp.dim_level_id AND
768 DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = x_dim_table;
769
770 h_parent_level_id NUMBER;
771
772 CURSOR c_child_dims (p_dim_level_id NUMBER) IS
773 select distinct dim_level_id
774 from
775 (select dim_level_id, parent_dim_level_id
776 from bsc_sys_dim_level_rels
777 where relation_type = 1
778 )
779 start with parent_dim_level_id = p_dim_level_id
780 connect by parent_dim_level_id = prior dim_level_id;
781
782 h_child_level_id NUMBER;
783 h_child_rel_type NUMBER;
784 h_child_rel_col VARCHAR(50);
785
786 TYPE t_cursor IS REF CURSOR;
787 h_cursor t_cursor;
788 h_sql VARCHAR2(32000);
789 h_condition VARCHAR2(32000);
790 h_base_table VARCHAR2(50);
791 h_cond_for_base_tables VARCHAR2(32000);
792
793 BEGIN
794 h_num_objects := 0;
795 h_cond_for_base_tables := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'dim_level_id');
796
797 -- Lock the input table
798 h_num_objects := h_num_objects + 1;
799 h_object_keys(h_num_objects) := x_input_table;
800 h_object_types(h_num_objects) := 'TABLE';
801 h_lock_types(h_num_objects) := 'W';
802 h_cascade_levels(h_num_objects) := 0;
803
804 -- Lock the dimension table
805 h_dim_table_type := BSC_UPDATE_DIM.Get_Dim_Table_Type(x_dim_table);
806 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_UNKNOWN THEN
807 RETURN FALSE;
808 END IF;
809 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
810 SELECT dim_level_id
811 INTO h_dim_level_id
812 FROM bsc_sys_dim_levels_b
813 WHERE level_table_name = x_dim_table;
814
815 h_num_objects := h_num_objects + 1;
816 h_object_keys(h_num_objects) := h_dim_level_id;
817 h_object_types(h_num_objects) := 'DIMENSION_OBJECT';
818 h_lock_types(h_num_objects) := 'W';
819 h_cascade_levels(h_num_objects) := 0;
820
821 BSC_APPS.Add_Value_Big_In_Cond(1, h_dim_level_id);
822 ELSE
823 -- mn dimension table
824 h_num_objects := h_num_objects + 1;
825 h_object_keys(h_num_objects) := x_dim_table;
826 h_object_types(h_num_objects) := 'TABLE';
827 h_lock_types(h_num_objects) := 'W';
828 h_cascade_levels(h_num_objects) := 0;
829 END IF;
830
831 --Lock the parent dimensions
832 OPEN c_parent_dims;
833 LOOP
834 FETCH c_parent_dims INTO h_parent_level_id;
835 EXIT WHEN c_parent_dims%NOTFOUND;
836
837 h_num_objects := h_num_objects + 1;
838 h_object_keys(h_num_objects) := h_parent_level_id;
839 h_object_types(h_num_objects) := 'DIMENSION_OBJECT';
840 h_lock_types(h_num_objects) := 'W';
841 h_cascade_levels(h_num_objects) := 0;
842
843 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_MN THEN
844 BSC_APPS.Add_Value_Big_In_Cond(1, h_parent_level_id);
845 END IF;
846 END LOOP;
847 CLOSE c_parent_dims;
848
849 -- Lock the child dimensions (all the childs, grand childs etc)
850 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
851 --This query gets all the child, grand child all the way down.
852 OPEN c_child_dims(h_dim_level_id);
853 LOOP
854 FETCH c_child_dims INTO h_child_level_id;
855 EXIT WHEN c_child_dims%NOTFOUND;
856
857 --lock the child dimension
858 h_num_objects := h_num_objects + 1;
859 h_object_keys(h_num_objects) := h_child_level_id;
860 h_object_types(h_num_objects) := 'DIMENSION_OBJECT';
861 h_lock_types(h_num_objects) := 'W';
862 h_cascade_levels(h_num_objects) := 0;
863
864 BSC_APPS.Add_Value_Big_In_Cond(1, h_child_level_id);
865 END LOOP;
866 CLOSE c_child_dims;
867 END IF;
868
869 -- Lock base tables used by the dimension or any of the child dimensions
870 h_sql := 'SELECT DISTINCT bt.table_name'||
871 ' FROM ('||
872 ' SELECT DISTINCT table_name FROM bsc_db_tables_rels'||
873 ' WHERE source_table_name IN ('||
874 ' SELECT table_name FROM bsc_db_tables WHERE table_type = :1)) bt,'||
875 ' bsc_db_tables_cols c'||
876 ' WHERE bt.table_name = c.table_name AND'||
877 ' c.column_type = :2 AND c.column_name IN ('||
878 ' SELECT level_pk_col FROM bsc_sys_dim_levels_b'||
879 ' WHERE '||h_cond_for_base_tables||')';
880 OPEN h_cursor FOR h_sql USING 0, 'P';
881 LOOP
882 FETCH h_cursor INTO h_base_table;
883 EXIT WHEN h_cursor%NOTFOUND;
884
885 h_num_objects := h_num_objects + 1;
886 h_object_keys(h_num_objects) := h_base_table;
887 h_object_types(h_num_objects) := 'TABLE';
888 h_lock_types(h_num_objects) := 'W';
889 h_cascade_levels(h_num_objects) := 0;
890 END LOOP;
891 CLOSE h_cursor;
892
893 FOR h_i IN 1..h_num_objects LOOP
894 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
895 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
896 BSC_UPDATE_LOG.OUTPUT);
897 END LOOP;
898
899 --need to commit because it used bsc_tmp_big_in_cond
900 commit;
901
902 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
903
904 RETURN h_b;
905 END Lock_Load_Dimension_Table;
906
907
908 /*===========================================================================+
909 | FUNCTION Lock_Period_Indicator
910 +============================================================================*/
911 FUNCTION Lock_Period_Indicator(
912 x_indicator IN NUMBER
913 ) RETURN BOOLEAN IS
914 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
915 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
916 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
917 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
918 h_num_objects NUMBER;
919 h_b BOOLEAN;
920 h_i NUMBER;
921 BEGIN
922 h_num_objects := 0;
923
924 h_num_objects := h_num_objects + 1;
925 h_object_keys(h_num_objects) := x_indicator;
926 h_object_types(h_num_objects) := 'INDICATOR_PERIOD';
927 h_lock_types(h_num_objects) := 'W';
928 h_cascade_levels(h_num_objects) := 0;
929
930 FOR h_i IN 1..h_num_objects LOOP
931 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
932 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
933 BSC_UPDATE_LOG.OUTPUT);
934 END LOOP;
935
936 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
937
938 RETURN h_b;
939 END Lock_Period_Indicator;
940
941
942 /*===========================================================================+
943 | FUNCTION Lock_Period_Indicators
944 +============================================================================*/
945 FUNCTION Lock_Period_Indicators(
946 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
947 x_num_indicators IN NUMBER
948 ) RETURN BOOLEAN IS
949 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
950 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
951 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
952 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
953 h_num_objects NUMBER;
954 h_b BOOLEAN;
955 h_i NUMBER;
956 BEGIN
957 h_num_objects := 0;
958
959 FOR h_i IN 1..x_num_indicators LOOP
960 -- Lock the table
961 h_num_objects := h_num_objects + 1;
962 h_object_keys(h_num_objects) := x_indicators(h_i);
963 h_object_types(h_num_objects) := 'INDICATOR_PERIOD';
964 h_lock_types(h_num_objects) := 'W';
965 h_cascade_levels(h_num_objects) := 0;
966 END LOOP;
967
968 FOR h_i IN 1..h_num_objects LOOP
969 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
970 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
971 BSC_UPDATE_LOG.OUTPUT);
972 END LOOP;
973
974 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
975
976 RETURN h_b;
977 END Lock_Period_Indicators;
978
979
980 /*===========================================================================+
981 | FUNCTION Lock_Period_Indicators
982 +============================================================================*/
983 FUNCTION Lock_Period_Indicators(
984 x_table_name IN VARCHAR2
985 ) RETURN BOOLEAN IS
986 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
987 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
988 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
989 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
990 h_num_objects NUMBER;
991 h_b BOOLEAN;
992 h_i NUMBER;
993
994 CURSOR c_indicators IS
995 select distinct indicator
996 from bsc_kpi_data_tables
997 where table_name = x_table_name;
998
999 h_indicator NUMBER;
1000
1001 BEGIN
1002 h_num_objects := 0;
1003
1004 --Lock the period of the indicators using the given table
1005 OPEN c_indicators;
1006 LOOP
1007 FETCH c_indicators INTO h_indicator;
1008 EXIT WHEN c_indicators%NOTFOUND;
1009
1010 h_num_objects := h_num_objects + 1;
1011 h_object_keys(h_num_objects) := h_indicator;
1012 h_object_types(h_num_objects) := 'INDICATOR_PERIOD';
1013 h_lock_types(h_num_objects) := 'W';
1014 h_cascade_levels(h_num_objects) := 0;
1015 END LOOP;
1016 CLOSE c_indicators;
1017
1018 FOR h_i IN 1..h_num_objects LOOP
1019 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1020 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1021 BSC_UPDATE_LOG.OUTPUT);
1022 END LOOP;
1023
1024 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1025
1026 RETURN h_b;
1027 END Lock_Period_Indicators;
1028
1029
1030 /*===========================================================================+
1031 | FUNCTION Lock_Prototype_Indicator
1032 +============================================================================*/
1033 FUNCTION Lock_Prototype_Indicator(
1034 x_indicator IN NUMBER
1035 ) RETURN BOOLEAN IS
1036 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1037 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1038 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1039 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1040 h_num_objects NUMBER;
1041 h_b BOOLEAN;
1042 h_i NUMBER;
1043
1044 BEGIN
1045 h_num_objects := 0;
1046
1047 h_num_objects := h_num_objects + 1;
1048 h_object_keys(h_num_objects) := x_indicator;
1049 h_object_types(h_num_objects) := 'INDICATOR_PROTOTYPE';
1050 h_lock_types(h_num_objects) := 'W';
1051 h_cascade_levels(h_num_objects) := 0;
1052
1053 FOR h_i IN 1..h_num_objects LOOP
1054 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1055 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1056 BSC_UPDATE_LOG.OUTPUT);
1057 END LOOP;
1058
1059 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1060
1061 RETURN h_b;
1062 END Lock_Prototype_Indicator;
1063
1064
1065 /*===========================================================================+
1066 | FUNCTION Lock_Prototype_Indicators
1067 +============================================================================*/
1068 FUNCTION Lock_Prototype_Indicators(
1069 x_calendar_id IN NUMBER
1070 ) RETURN BOOLEAN IS
1071 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1072 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1073 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1074 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1075 h_num_objects NUMBER;
1076 h_b BOOLEAN;
1077 h_i NUMBER;
1078
1079 CURSOR c_indicators IS
1080 select distinct indicator
1081 from bsc_kpis_b
1082 where calendar_id = x_calendar_id;
1083
1084 h_indicator NUMBER;
1085
1086 BEGIN
1087 h_num_objects := 0;
1088
1089 OPEN c_indicators;
1090 LOOP
1091 FETCH c_indicators INTO h_indicator;
1092 EXIT WHEN c_indicators%NOTFOUND;
1093
1094 h_num_objects := h_num_objects + 1;
1095 h_object_keys(h_num_objects) := h_indicator;
1096 h_object_types(h_num_objects) := 'INDICATOR_PROTOTYPE';
1097 h_lock_types(h_num_objects) := 'W';
1098 h_cascade_levels(h_num_objects) := 0;
1099 END LOOP;
1100 CLOSE c_indicators;
1101
1102 FOR h_i IN 1..h_num_objects LOOP
1103 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1104 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1105 BSC_UPDATE_LOG.OUTPUT);
1106 END LOOP;
1107
1108 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1109
1110 RETURN h_b;
1111 END Lock_Prototype_Indicators;
1112
1113
1114 /*===========================================================================+
1115 | FUNCTION Lock_Prototype_Indicators
1116 +============================================================================*/
1117 FUNCTION Lock_Prototype_Indicators
1118 RETURN BOOLEAN IS
1119 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1120 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1121 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1122 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1123 h_num_objects NUMBER;
1124 h_b BOOLEAN;
1125 h_i NUMBER;
1126
1127 TYPE t_cursor IS REF CURSOR;
1128 c_indicators t_cursor;
1129 h_sql VARCHAR2(32000);
1130 h_where_indics VARCHAR2(32000);
1131 h_indicator NUMBER;
1132
1133 BEGIN
1134 h_num_objects := 0;
1135
1136 h_sql := 'SELECT DISTINCT indicator'||
1137 ' FROM bsc_kpis_vl'||
1138 ' WHERE prototype_flag IN (6,7)';
1139 IF BSC_UPDATE.g_kpi_mode THEN
1140 IF BSC_UPDATE.g_num_indicators > 0 THEN
1141 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
1142 FOR h_i IN 1..BSC_UPDATE.g_num_indicators LOOP
1143 BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
1144 END LOOP;
1145 h_sql := h_sql||' AND ('||h_where_indics||')';
1146 END IF;
1147 END IF;
1148
1149 OPEN c_indicators FOR h_sql;
1150 LOOP
1151 FETCH c_indicators INTO h_indicator;
1152 EXIT WHEN c_indicators%NOTFOUND;
1153
1154 h_num_objects := h_num_objects + 1;
1155 h_object_keys(h_num_objects) := h_indicator;
1156 h_object_types(h_num_objects) := 'INDICATOR_PROTOTYPE';
1157 h_lock_types(h_num_objects) := 'W';
1158 h_cascade_levels(h_num_objects) := 0;
1159 END LOOP;
1160 CLOSE c_indicators;
1161
1162 FOR h_i IN 1..h_num_objects LOOP
1163 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1164 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1165 BSC_UPDATE_LOG.OUTPUT);
1166 END LOOP;
1167
1168 --need to commit because it used bsc_tmp_big_in_cond
1169 commit;
1170
1171 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1172
1173 RETURN h_b;
1174 END Lock_Prototype_Indicators;
1175
1176
1177 /*===========================================================================+
1178 | FUNCTION Lock_Refresh_AW_Indicator
1179 +============================================================================*/
1180 FUNCTION Lock_Refresh_AW_Indicator(
1181 x_indicator IN NUMBER
1182 ) RETURN BOOLEAN IS
1183 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1184 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1185 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1186 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1187 h_num_objects NUMBER;
1188 h_b BOOLEAN;
1189 h_i NUMBER;
1190
1191 CURSOR c_indic_tables IS
1192 select distinct table_name
1193 from bsc_db_tables_rels
1194 start with table_name in (
1195 select distinct table_name
1196 from bsc_kpi_data_tables
1197 where indicator = x_indicator and
1198 table_name is not null
1199 )
1200 connect by table_name = prior source_table_name;
1201
1202 h_table_name VARCHAR2(50);
1203
1204 BEGIN
1205 h_num_objects := 0;
1206
1207 -- Lock the indicator aw cubes
1208 h_num_objects := h_num_objects + 1;
1209 h_object_keys(h_num_objects) := x_indicator;
1210 h_object_types(h_num_objects) := 'AW_INDICATOR_CUBES';
1211 h_lock_types(h_num_objects) := 'W';
1212 h_cascade_levels(h_num_objects) := 0;
1213
1214 -- Now all the summary tables of this indicator and its base tables
1215 OPEN c_indic_tables;
1216 LOOP
1217 FETCH c_indic_tables INTO h_table_name;
1218 EXIT WHEN c_indic_tables%NOTFOUND;
1219
1220 h_num_objects := h_num_objects + 1;
1221 h_object_keys(h_num_objects) := h_table_name;
1222 h_object_types(h_num_objects) := 'TABLE';
1223 h_lock_types(h_num_objects) := 'W';
1224 h_cascade_levels(h_num_objects) := 0;
1225 END LOOP;
1226 CLOSE c_indic_tables;
1227
1228 FOR h_i IN 1..h_num_objects LOOP
1229 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1230 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1231 BSC_UPDATE_LOG.OUTPUT);
1232 END LOOP;
1233
1234 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1235
1236 RETURN h_b;
1237 END Lock_Refresh_AW_Indicator;
1238
1239
1240 /*===========================================================================+
1241 | FUNCTION Lock_Refresh_AW_Table
1242 +============================================================================*/
1243 FUNCTION Lock_Refresh_AW_Table(
1244 x_summary_table IN VARCHAR2
1245 ) RETURN BOOLEAN IS
1246 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1247 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1248 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1249 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1250 h_num_objects NUMBER;
1251 h_b BOOLEAN;
1252 h_i NUMBER;
1253
1254 CURSOR c_source_tables IS
1255 select distinct source_table_name
1256 from bsc_db_tables_rels
1257 where table_name = x_summary_table;
1258
1259 h_table_name VARCHAR2(50);
1260
1261 BEGIN
1262 h_num_objects := 0;
1263
1264 -- Lock the summary table
1265 h_num_objects := h_num_objects + 1;
1266 h_object_keys(h_num_objects) := x_summary_table;
1267 h_object_types(h_num_objects) := 'TABLE';
1268 h_lock_types(h_num_objects) := 'W';
1269 h_cascade_levels(h_num_objects) := 0;
1270
1271 -- Now lock the source tables
1272 OPEN c_source_tables;
1273 LOOP
1274 FETCH c_source_tables INTO h_table_name;
1275 EXIT WHEN c_source_tables%NOTFOUND;
1276
1277 h_num_objects := h_num_objects + 1;
1278 h_object_keys(h_num_objects) := h_table_name;
1279 h_object_types(h_num_objects) := 'TABLE';
1280 h_lock_types(h_num_objects) := 'W';
1281 h_cascade_levels(h_num_objects) := 0;
1282 END LOOP;
1283 CLOSE c_source_tables;
1284
1285 FOR h_i IN 1..h_num_objects LOOP
1286 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1287 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1288 BSC_UPDATE_LOG.OUTPUT);
1289 END LOOP;
1290
1291 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1292
1293 RETURN h_b;
1294 END Lock_Refresh_AW_Table;
1295
1296
1297 /*===========================================================================+
1298 | FUNCTION Lock_Refresh_MV
1299 +============================================================================*/
1300 FUNCTION Lock_Refresh_MV(
1301 x_summary_table IN VARCHAR2
1302 ) RETURN BOOLEAN IS
1303 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1304 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1305 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1306 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1307 h_num_objects NUMBER;
1308 h_b BOOLEAN;
1309 h_i NUMBER;
1310
1311 CURSOR c_other_tables (p_like_name varchar2) IS
1312 select distinct table_name
1313 from bsc_db_tables
1314 where table_name like p_like_name;
1315
1316 CURSOR c_source_tables (p_like_name varchar2) IS
1317 select distinct source_table_name
1318 from bsc_db_tables_rels
1319 where table_name like p_like_name;
1320
1321 h_table_name VARCHAR2(50);
1322 h_like_name VARCHAR2(50);
1323 h_pos NUMBER;
1324
1325 BEGIN
1326 h_num_objects := 0;
1327
1328 -- Lock the summary table
1329 h_num_objects := h_num_objects + 1;
1330 h_object_keys(h_num_objects) := x_summary_table;
1331 h_object_types(h_num_objects) := 'TABLE';
1332 h_lock_types(h_num_objects) := 'W';
1333 h_cascade_levels(h_num_objects) := 0;
1334
1335 -- If the summary table is a BSC_T table then we do not need to lock any other object
1336 IF substr(x_summary_table, 1, 5) <> 'BSC_T' THEN
1337 -- Lock the other summary tables of the same level but with different periodicity
1338 -- that are contained in the same MV
1339 h_pos := INSTR(x_summary_table, '_', -1);
1340 h_like_name := SUBSTR(x_summary_table, 1, h_pos)||'%';
1341
1342 OPEN c_other_tables(h_like_name);
1343 LOOP
1344 FETCH c_other_tables INTO h_table_name;
1345 EXIT WHEN c_other_tables%NOTFOUND;
1346
1347 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, h_object_keys, h_num_objects) THEN
1348 h_num_objects := h_num_objects + 1;
1349 h_object_keys(h_num_objects) := h_table_name;
1350 h_object_types(h_num_objects) := 'TABLE';
1351 h_lock_types(h_num_objects) := 'W';
1352 h_cascade_levels(h_num_objects) := 0;
1353 END IF;
1354 END LOOP;
1355 CLOSE c_other_tables;
1356
1357 --Now lock the source tables contained in the source MVs
1358 OPEN c_source_tables(h_like_name);
1359 LOOP
1360 FETCH c_source_tables INTO h_table_name;
1361 EXIT WHEN c_source_tables%NOTFOUND;
1362
1363 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, h_object_keys, h_num_objects) THEN
1364 h_num_objects := h_num_objects + 1;
1365 h_object_keys(h_num_objects) := h_table_name;
1366 h_object_types(h_num_objects) := 'TABLE';
1367 h_lock_types(h_num_objects) := 'W';
1368 h_cascade_levels(h_num_objects) := 0;
1369 END IF;
1370 END LOOP;
1371 CLOSE c_source_tables;
1372 END IF;
1373
1374 FOR h_i IN 1..h_num_objects LOOP
1375 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1376 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1377 BSC_UPDATE_LOG.OUTPUT);
1378 END LOOP;
1379
1380 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1381
1382 RETURN h_b;
1383 END Lock_Refresh_MV;
1384
1385
1386 /*===========================================================================+
1387 | FUNCTION Lock_Refresh_Sum_Table
1388 +============================================================================*/
1389 FUNCTION Lock_Refresh_Sum_Table(
1390 x_summary_table IN VARCHAR2
1391 ) RETURN BOOLEAN IS
1392 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1393 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1394 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1395 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1396 h_num_objects NUMBER;
1397 h_b BOOLEAN;
1398 h_i NUMBER;
1399
1400 CURSOR c_source_tables IS
1401 select distinct source_table_name
1402 from bsc_db_tables_rels
1403 where table_name = x_summary_table;
1404
1405 h_table_name VARCHAR2(50);
1406
1407 BEGIN
1408 h_num_objects := 0;
1409
1410 -- Lock the summary table
1411 h_num_objects := h_num_objects + 1;
1412 h_object_keys(h_num_objects) := x_summary_table;
1413 h_object_types(h_num_objects) := 'TABLE';
1414 h_lock_types(h_num_objects) := 'W';
1415 h_cascade_levels(h_num_objects) := 0;
1416
1417 -- Now lock the source tables
1418 OPEN c_source_tables;
1419 LOOP
1420 FETCH c_source_tables INTO h_table_name;
1421 EXIT WHEN c_source_tables%NOTFOUND;
1422
1423 h_num_objects := h_num_objects + 1;
1424 h_object_keys(h_num_objects) := h_table_name;
1425 h_object_types(h_num_objects) := 'TABLE';
1426 h_lock_types(h_num_objects) := 'W';
1427 h_cascade_levels(h_num_objects) := 0;
1428 END LOOP;
1429 CLOSE c_source_tables;
1430
1431 FOR h_i IN 1..h_num_objects LOOP
1432 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1433 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1434 BSC_UPDATE_LOG.OUTPUT);
1435 END LOOP;
1436
1437 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1438
1439 RETURN h_b;
1440 END Lock_Refresh_Sum_Table;
1441
1442
1443 /*===========================================================================+
1444 | FUNCTION Lock_Update_Base_Table
1445 +============================================================================*/
1446 FUNCTION Lock_Update_Base_Table(
1447 x_input_table IN VARCHAR2,
1448 x_base_table IN VARCHAR2
1449 ) RETURN BOOLEAN IS
1450 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1451 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1452 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1453 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1454 h_num_objects NUMBER;
1455 h_b BOOLEAN;
1456 h_i NUMBER;
1457 BEGIN
1458 h_num_objects := 0;
1459
1460 -- Lock the input table
1461 h_num_objects := h_num_objects + 1;
1462 h_object_keys(h_num_objects) := x_input_table;
1463 h_object_types(h_num_objects) := 'TABLE';
1464 h_lock_types(h_num_objects) := 'W';
1465 h_cascade_levels(h_num_objects) := 0;
1466
1467 -- Lock the base table
1468 h_num_objects := h_num_objects + 1;
1469 h_object_keys(h_num_objects) := x_base_table;
1470 h_object_types(h_num_objects) := 'TABLE';
1471 h_lock_types(h_num_objects) := 'W';
1472 h_cascade_levels(h_num_objects) := 0;
1473
1474 FOR h_i IN 1..h_num_objects LOOP
1475 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1476 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1477 BSC_UPDATE_LOG.OUTPUT);
1478 END LOOP;
1479
1480 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1481
1482 RETURN h_b;
1483 END Lock_Update_Base_Table;
1484
1485
1486 /*===========================================================================+
1487 | FUNCTION Lock_Update_Date
1488 +============================================================================*/
1489 FUNCTION Lock_Update_Date RETURN BOOLEAN IS
1490 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1491 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1492 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1493 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1494 h_num_objects NUMBER;
1495 h_b BOOLEAN;
1496 h_i NUMBER;
1497 BEGIN
1498 h_num_objects := 0;
1499
1500 h_num_objects := h_num_objects + 1;
1501 h_object_keys(h_num_objects) := 0;
1502 h_object_types(h_num_objects) := 'UPDATE_DATE';
1503 h_lock_types(h_num_objects) := 'W';
1504 h_cascade_levels(h_num_objects) := 0;
1505
1506 FOR h_i IN 1..h_num_objects LOOP
1507 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1508 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1509 BSC_UPDATE_LOG.OUTPUT);
1510 END LOOP;
1511
1512 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1513
1514 RETURN h_b;
1515 END Lock_Update_Date;
1516
1517
1518 /*===========================================================================+
1519 | FUNCTION Lock_Table
1520 +============================================================================*/
1521 FUNCTION Lock_Table(
1522 x_table IN VARCHAR2
1523 ) RETURN BOOLEAN IS
1524 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1525 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1526 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1527 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1528 h_num_objects NUMBER;
1529 h_b BOOLEAN;
1530 h_i NUMBER;
1531 BEGIN
1532 h_num_objects := 0;
1533
1534 -- Lock the table
1535 h_num_objects := h_num_objects + 1;
1536 h_object_keys(h_num_objects) := x_table;
1537 h_object_types(h_num_objects) := 'TABLE';
1538 h_lock_types(h_num_objects) := 'W';
1539 h_cascade_levels(h_num_objects) := 0;
1540
1541 FOR h_i IN 1..h_num_objects LOOP
1542 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1543 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1544 BSC_UPDATE_LOG.OUTPUT);
1545 END LOOP;
1546
1547 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1548
1549 RETURN h_b;
1550 END Lock_Table;
1551
1552
1553 /*===========================================================================+
1554 | FUNCTION Lock_Tables
1555 +============================================================================*/
1556 FUNCTION Lock_Tables(
1557 x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1558 x_num_tables IN NUMBER
1559 ) RETURN BOOLEAN IS
1560 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1561 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1562 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1563 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1564 h_num_objects NUMBER;
1565 h_b BOOLEAN;
1566 h_i NUMBER;
1567 BEGIN
1568 h_num_objects := 0;
1569
1570 FOR h_i IN 1..x_num_tables LOOP
1571 -- Lock the table
1572 h_num_objects := h_num_objects + 1;
1573 h_object_keys(h_num_objects) := x_tables(h_i);
1574 h_object_types(h_num_objects) := 'TABLE';
1575 h_lock_types(h_num_objects) := 'W';
1576 h_cascade_levels(h_num_objects) := 0;
1577 END LOOP;
1578
1579 FOR h_i IN 1..h_num_objects LOOP
1580 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1581 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1582 BSC_UPDATE_LOG.OUTPUT);
1583 END LOOP;
1584
1585 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1586
1587 RETURN h_b;
1588 END Lock_Tables;
1589
1590
1591 /*===========================================================================+
1592 | FUNCTION Lock_Temp_Tables
1593 +============================================================================*/
1594 FUNCTION Lock_Temp_Tables(
1595 x_type IN VARCHAR2
1596 ) RETURN BOOLEAN IS
1597 h_object_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
1598 h_object_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1599 h_lock_types BSC_UPDATE_UTIL.t_array_of_varchar2;
1600 h_cascade_levels BSC_UPDATE_UTIL.t_array_of_number;
1601 h_num_objects NUMBER;
1602 h_b BOOLEAN;
1603 h_i NUMBER;
1604 BEGIN
1605 h_num_objects := 0;
1606
1607 -- Lock the table
1608 h_num_objects := h_num_objects + 1;
1609 h_object_keys(h_num_objects) := x_type;
1610 h_object_types(h_num_objects) := 'TEMP_TABLES';
1611 h_lock_types(h_num_objects) := 'W';
1612 h_cascade_levels(h_num_objects) := 0;
1613
1614 FOR h_i IN 1..h_num_objects LOOP
1615 BSC_UPDATE_LOG.Write_Line_Log('Locking: '||h_object_keys(h_i)||' '||h_object_types(h_i)||
1616 ' '||h_lock_types(h_i)||' '||h_cascade_levels(h_i),
1617 BSC_UPDATE_LOG.OUTPUT);
1618 END LOOP;
1619
1620 h_b := Request_Lock(h_object_keys, h_object_types, h_lock_types, h_cascade_levels, h_num_objects);
1621
1622 RETURN h_b;
1623 END Lock_Temp_Tables;
1624
1625
1626 /*===========================================================================+
1627 | FUNCTION Request_Lock
1628 +============================================================================*/
1629 FUNCTION Request_Lock (
1630 x_object_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1631 x_object_types IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1632 x_lock_types IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1633 x_cascade_levels IN BSC_UPDATE_UTIL.t_array_of_number,
1634 x_num_objects IN NUMBER
1635 ) RETURN BOOLEAN IS
1636
1637 h_i NUMBER;
1638 h_j NUMBER;
1639 h_num_locked_objects NUMBER;
1640
1641 h_return_status VARCHAR2(2000);
1642 h_msg_count NUMBER;
1643 h_msg_data VARCHAR2(4000);
1644
1645 BEGIN
1646
1647 LOOP
1648 h_num_locked_objects := 0;
1649
1650 FOR h_i IN 1..x_num_objects LOOP
1651 -- Try to get the lock
1652 BSC_LOCKS_PUB.GET_SYSTEM_LOCK (
1653 p_object_key => x_object_keys(h_i),
1654 p_object_type => x_object_types(h_i),
1655 p_lock_type => x_lock_types(h_i),
1656 p_query_time => SYSDATE, --BSC_LOCKS_PUB.Get_System_Time,
1657 p_program_id => -101,
1658 p_user_id => BSC_APPS.apps_user_id,
1659 p_cascade_lock_level => x_cascade_levels(h_i),
1660 x_return_status => h_return_status,
1661 x_msg_count => h_msg_count,
1662 x_msg_data => h_msg_data
1663 );
1664
1665 IF h_return_status = FND_API.G_RET_STS_SUCCESS THEN
1666 -- It got the lock
1667 h_num_locked_objects := h_num_locked_objects + 1;
1668 ELSE
1669 IF h_return_status = FND_API.G_RET_STS_ERROR THEN
1670 -- It could not get the lock
1671 -- Commit to release the objects already locked so far
1672 BSC_UPDATE_LOG.Write_Line_Log(x_object_keys(h_i)||' '||x_object_types(h_i)||': '||h_msg_data,
1673 BSC_UPDATE_LOG.OUTPUT);
1674 COMMIT;
1675 ELSE
1676 -- This is an unexpected error in the locking api
1677 -- Commit to release the objects already locked so far
1678 -- No reason to continue
1679 BSC_UPDATE_LOG.Write_Line_Log(h_msg_data, BSC_UPDATE_LOG.OUTPUT);
1680 COMMIT;
1681 RETURN FALSE;
1682 END IF;
1683
1684 EXIT;
1685 END IF;
1686 END LOOP;
1687
1688 IF h_num_locked_objects = x_num_objects THEN
1689 -- It got the lock on all the objects
1690 EXIT;
1691 END IF;
1692
1693 -- Wait for 2 seconds before trying to lock all the objects again
1694 DBMS_LOCK.Sleep(2);
1695 END LOOP;
1696
1697 RETURN TRUE;
1698
1699 END Request_Lock;
1700
1701 END BSC_UPDATE_LOCK;