[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_UTIL
Source
1 PACKAGE BODY BSC_UPDATE_UTIL AS
2 /* $Header: BSCDUTIB.pls 120.10 2007/03/15 09:46:11 ankgoel ship $ */
3
4
5 /*===========================================================================+
6 | PROCEDURE CloneBSCPeriodicitybyCalendar
7 +============================================================================*/
8 PROCEDURE CloneBSCPeriodicitybyCalendar (
9 x_calendar_id IN NUMBER
10 ) IS
11
12 sql_stmt VARCHAR2(2000); -- Sql statement string
13 e_error EXCEPTION;
14
15 TYPE t_cursor IS REF CURSOR;
16
17 CURSOR c_base_per (p_calendar_id NUMBER, p_custom_code NUMBER) IS
18 SELECT PERIODICITY_ID
19 FROM BSC_SYS_PERIODICITIES
20 WHERE CALENDAR_ID = p_calendar_id AND CUSTOM_CODE < p_custom_code
21 ORDER BY PERIODICITY_ID;
22
23 CURSOR c_new_per (p_calendar_id NUMBER) IS
24 SELECT PERIODICITY_ID,SOURCE
25 FROM BSC_SYS_PERIODICITIES
26 WHERE CALENDAR_ID = p_calendar_id
27 ORDER BY PERIODICITY_ID;
28
29 h_periodicity_type NUMBER;
30
31 CURSOR c_get_per (p_calendar_id NUMBER, p_periodicity_type NUMBER) IS
32 SELECT PERIODICITY_ID
33 FROM BSC_SYS_PERIODICITIES
34 WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type;
35
36 h_periodicity_id NUMBER;
37 h_source VARCHAR2(200);
38 h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
39 h_count NUMBER;
40 h_i NUMBER;
41 h_new_per_id NUMBER;
42 h_new_source VARCHAR2(200);
43
44 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
45 h_num_bind_vars NUMBER;
46
47 BEGIN
48 -- Init BSC/APPS global variables
49 BSC_APPS.Init_Bsc_Apps;
50
51 --Get all The Base periodicities
52 --OPEN c_base_per FOR c_base_per_sql USING 1, 1;
53 OPEN c_base_per(1, 1);
54 FETCH c_base_per INTO h_periodicity_id;
55 WHILE c_base_per%FOUND LOOP
56 --Copy the periodicity with a new PERIODICITY_ID and CALENDAR_ID
57 /*
58 sql_stmt :=
59 ' INSERT INTO BSC_SYS_PERIODICITIES ' ||
60 ' (PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,PERIOD_COL_NAME, ' ||
61 ' SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID, ' ||
62 ' CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE) ' ||
63 ' SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS, '||
64 ' PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG, '||
65 ' :1 CALENDAR_ID,EDW_PERIODICITY_ID,CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE '||
66 ' FROM BSC_SYS_PERIODICITIES ' ||
67 ' WHERE PERIODICITY_ID = :2';
68
69 h_bind_vars_values.delete;
70 h_bind_vars_values(1) := x_calendar_id;
71 h_bind_vars_values(2) := h_periodicity_id;
72 h_num_bind_vars := 2;
73 BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
74 */
75 INSERT INTO BSC_SYS_PERIODICITIES (
76 PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,PERIOD_COL_NAME,
77 SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
78 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE)
79 SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
80 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,
81 x_calendar_id CALENDAR_ID,EDW_PERIODICITY_ID,CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE
82 FROM BSC_SYS_PERIODICITIES
83 WHERE PERIODICITY_ID = h_periodicity_id;
84
85 FETCH c_base_per INTO h_periodicity_id;
86 END LOOP;
87 CLOSE c_base_per;
88
89 --- Update the SOURCE columns
90 --OPEN c_new_per FOR c_new_per_sql USING x_calendar_id;
91 OPEN c_new_per(x_calendar_id);
92 FETCH c_new_per INTO h_periodicity_id,h_source;
93 WHILE c_new_per%FOUND LOOP
94 h_new_source := '';
95 IF h_source IS NOT NULL THEN
96 h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
97 FOR h_i IN 1.. h_count LOOP
98 -- Get New Value
99 h_periodicity_type := h_tmp_array(h_i);
100 --OPEN c_get_per FOR c_get_per_sql USING x_calendar_id, h_periodicity_type;
101 OPEN c_get_per(x_calendar_id, h_periodicity_type);
102 FETCH c_get_per INTO h_new_per_id;
103 IF c_get_per%FOUND THEN
104 IF h_new_source IS NOT NULL THEN
105 h_new_source := h_new_source || ',' || h_new_per_id;
106 ELSE
107 h_new_source :=h_new_per_id;
108 END IF;
109 END IF;
110 CLOSE c_get_per;
111 END LOOP;
112 -- Update the source
113 /*
114 sql_stmt := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1 WHERE PERIODICITY_ID = :2';
115 EXECUTE IMMEDIATE sql_stmt USING h_new_source, h_periodicity_id;
116 */
117 UPDATE BSC_SYS_PERIODICITIES
118 SET SOURCE = h_new_source
119 WHERE PERIODICITY_ID = h_periodicity_id;
120 END IF;
121 FETCH c_new_per INTO h_periodicity_id,h_source;
122 END LOOP;
123 CLOSE c_new_per;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 BSC_MESSAGE.Add(x_message => SQLERRM,
128 x_source => 'BSC_UPDATE_UTIL.CloneBSCPeriodicitybyCalendar',
129 x_mode => 'I');
130 COMMIT;
131 END CloneBSCPeriodicitybyCalendar;
132
133
134 /*===========================================================================+
135 | FUNCTION Create_Unique_Index
136 +============================================================================*/
137 FUNCTION Create_Unique_Index(
138 x_table_name IN VARCHAR2,
139 x_index_name IN VARCHAR2,
140 x_lst_columns IN VARCHAR2,
141 x_tbs_type IN VARCHAR2
142 ) RETURN BOOLEAN IS
143
144 h_sql VARCHAR2(32700);
145
146 TYPE t_cursor IS REF CURSOR;
147
148 CURSOR c_index (p_index_name VARCHAR2) IS
149 SELECT index_name
150 FROM user_indexes
151 WHERE index_name = p_index_name;
152
153 CURSOR c_index_apps (p_index_name VARCHAR2, p_owner VARCHAR2) IS
154 SELECT index_name
155 FROM all_indexes
156 WHERE index_name = p_index_name AND owner = p_owner;
157
158 h_index VARCHAR2(30);
159 h_do_it BOOLEAN;
160 h_index_name VARCHAR2(50);
161
162 BEGIN
163 h_do_it := FALSE;
164
165 h_index_name := UPPER(x_index_name);
166
167 IF NOT BSC_APPS.APPS_ENV THEN
168 -- Personal
169 --OPEN c_index FOR c_index_sql USING h_index_name;
170 OPEN c_index(h_index_name);
171 FETCH c_index INTO h_index;
172 IF c_index%NOTFOUND THEN
173 h_do_it := TRUE;
174 END IF;
175 CLOSE c_index;
176 ELSE
177 -- APPS
178 --OPEN c_index_apps FOR c_index_apps_sql USING h_index_name, BSC_APPS.BSC_APPS_SCHEMA;
179 OPEN c_index_apps(h_index_name, BSC_APPS.BSC_APPS_SCHEMA);
180 FETCH c_index_apps INTO h_index;
181 IF c_index_apps%NOTFOUND THEN
182 h_do_it := TRUE;
183 END IF;
184 CLOSE c_index_apps;
185 END IF;
186
187 IF h_do_it THEN
188 h_sql := 'CREATE UNIQUE INDEX '||x_index_name||
189 ' ON '||x_table_name||' ('||x_lst_columns||
190 ') TABLESPACE '||BSC_APPS.Get_Tablespace_Name(x_tbs_type)||
191 ' '||BSC_APPS.bsc_storage_clause;
192 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_INDEX, x_index_name);
193 END IF;
194
195 RETURN TRUE;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 BSC_MESSAGE.Add(x_message => SQLERRM,
200 x_source => 'BSC_UPDATE_UTIL.Create_Unique_Index');
201 RETURN FALSE;
202
203 END Create_Unique_Index;
204
205
206 /*===========================================================================+
207 | FUNCTION getSmallerColumnList
208 | Added for Bug 4099338
209 +============================================================================*/
210 function getSmallerColumnList(h_lst_cols_index in varchar2) return varchar2 is
211 l_list varchar2(2000);
212 l_index number;
213 begin
214 l_list:='';
215 l_index:=INSTR(h_lst_cols_index, ',', -1, 1);
216 l_list:=substr(h_lst_cols_index, 1, l_index-1);
217 return l_list;
218 end;
219
220
221 /*===========================================================================+
222 | FUNCTION Create_Global_Temp_Table
223 +============================================================================*/
224 FUNCTION Create_Global_Temp_Table(
225 x_table_name IN VARCHAR2,
226 x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
227 x_num_columns IN NUMBER
228 ) RETURN BOOLEAN IS
229
230 h_sql VARCHAR2(32700);
231
232 h_create_it BOOLEAN;
233 h_drop_it BOOLEAN;
234
235 CURSOR c_table_columns (p_table_name VARCHAR2, p_owner VARCHAR2) IS
236 SELECT column_name, data_type, data_length, data_precision
237 FROM all_tab_columns
238 WHERE table_name = p_table_name AND owner = p_owner
239 ORDER BY column_id;
240
241 h_column_name VARCHAR2(30);
242 h_data_type VARCHAR2(200);
243 h_data_length NUMBER;
244 h_data_precision NUMBER;
245
246 h_lst_cols_desc VARCHAR2(32000);
247 h_lst_cols_index VARCHAR2(32000);
248
249 h_i NUMBER;
250 h_count NUMBER;
251 h_index_name VARCHAR2(30);
252 h_need_index BOOLEAN;
253
254 BEGIN
255
256 h_drop_it := FALSE;
257 h_create_it := FALSE;
258
259 -- If the table exists and the structure is the same then we do not
260 -- re-create the temporal table
261
262 SELECT COUNT(*)
263 INTO h_count
264 FROM all_tab_columns
265 WHERE table_name = x_table_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
266
267 IF h_count = 0 THEN
268 -- table does not exists
269 h_create_it := TRUE;
270 ELSE
271 IF h_count <> x_num_columns THEN
272 -- structure is different or table does not exist
273 h_drop_it := TRUE;
274 h_create_it := TRUE;
275 ELSE
276 h_i := 1;
277 OPEN c_table_columns(x_table_name, BSC_APPS.BSC_APPS_SCHEMA);
278 LOOP
279 FETCH c_table_columns INTO h_column_name, h_data_type, h_data_length, h_data_precision;
280 EXIT WHEN c_table_columns%NOTFOUND;
281 IF (UPPER(x_table_columns(h_i).column_name) <> h_column_name) OR
282 (x_table_columns(h_i).data_type <> h_data_type) OR
283 (h_data_type = 'VARCHAR2' AND (NVL(x_table_columns(h_i).data_size, 0) <> h_data_length)) OR
284 (h_data_type = 'NUMBER' AND (NVL(x_table_columns(h_i).data_size, 0) <> NVL(h_data_precision, 0))) THEN
285 -- structure is different
286 h_drop_it := TRUE;
287 h_create_it := TRUE;
288 EXIT;
289 END IF;
290 h_i := h_i + 1;
291 END LOOP;
292 CLOSE c_table_columns;
293 END IF;
294 END IF;
295
296 IF h_drop_it THEN
297 h_sql := 'DROP TABLE '||x_table_name;
298 BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, x_table_name);
299 END IF;
300
301 IF h_create_it THEN
302 h_lst_cols_desc := NULL;
303 h_lst_cols_index := NULL;
304
305 FOR h_i IN 1..x_num_columns LOOP
306 IF h_lst_cols_desc IS NOT NULL THEN
307 h_lst_cols_desc := h_lst_cols_desc||', ';
308 END IF;
309 h_lst_cols_desc := h_lst_cols_desc||x_table_columns(h_i).column_name||
310 ' '||x_table_columns(h_i).data_type;
311 IF x_table_columns(h_i).data_size IS NOT NULL THEN
312 h_lst_cols_desc := h_lst_cols_desc||'('||x_table_columns(h_i).data_size||')';
313 END IF;
314
315 IF x_table_columns(h_i).add_to_index = 'Y' THEN
316 IF h_lst_cols_index IS NOT NULL THEN
317 h_lst_cols_index := h_lst_cols_index||', ';
318 END IF;
319 h_lst_cols_index := h_lst_cols_index||x_table_columns(h_i).column_name;
320 END IF;
321 END LOOP;
322
323 h_sql := 'CREATE GLOBAL TEMPORARY TABLE '||x_table_name||' ('||h_lst_cols_desc||
324 ') ON COMMIT PRESERVE ROWS';
325 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_TABLE, x_table_name);
326
327 -- Create index
328 IF h_lst_cols_index IS NOT NULL THEN
329 h_index_name := x_table_name||'_N1';
330 loop --modifed code for 4099338
331 begin
332 h_sql := 'CREATE INDEX '||h_index_name||
333 ' ON '||x_table_name||' ('||h_lst_cols_index||')';
334 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_INDEX, h_index_name);
335 exit;
336 exception
337 when others then
338 if h_lst_cols_index is null then
339 BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
340 x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
341 BSC_MESSAGE.Add(x_message => 'l_index_count=0.Unable to create Index on any Key columns',
342 x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
343 RETURN FALSE;
344 end if;
345 h_lst_cols_index:=getSmallerColumnList(h_lst_cols_index);
346 end;
347 end loop;
348 END IF;
349 ELSE
350 -- Bug#3875046
351 -- there was no need to re-create the table, but may be the case
352 -- we want to drop existing index since the index is not needed anymore
353 h_need_index := FALSE;
354
355 FOR h_i IN 1..x_num_columns LOOP
356 IF x_table_columns(h_i).add_to_index = 'Y' THEN
357 h_need_index := TRUE;
358 EXIT;
359 END IF;
360 END LOOP;
361
362 IF NOT h_need_index THEN
363 h_index_name := x_table_name||'_N1';
364
365 SELECT COUNT(*)
366 INTO h_count
367 FROM all_indexes
368 WHERE index_name = h_index_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
369
370 IF h_count > 0 THEN
371 -- Index exist but it is not needed
372 h_sql := 'DROP INDEX '||h_index_name;
373 BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_INDEX, h_index_name);
374 END IF;
375 END IF;
376 END IF;
377
378 RETURN TRUE;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
383 x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
384 BSC_MESSAGE.Add(x_message => SQLERRM,
385 x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
386 RETURN FALSE;
387
388 END Create_Global_Temp_Table;
389
390
391 /*===========================================================================+
392 | FUNCTION Create_Permanent_Table
393 +============================================================================*/
394 FUNCTION Create_Permanent_Table(
395 x_table_name IN VARCHAR2,
396 x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
397 x_num_columns IN NUMBER,
398 x_tablespace IN VARCHAR2,
399 x_idx_tablespace IN VARCHAR2
400 ) RETURN BOOLEAN IS
401
402 h_sql VARCHAR2(32700);
403
404 h_create_it BOOLEAN;
405 h_drop_it BOOLEAN;
406
407 CURSOR c_table_columns (p_table_name VARCHAR2, p_owner VARCHAR2) IS
408 SELECT column_name, data_type, data_length, data_precision
409 FROM all_tab_columns
410 WHERE table_name = p_table_name AND owner = p_owner
411 ORDER BY column_id;
412
413 h_column_name VARCHAR2(30);
414 h_data_type VARCHAR2(200);
415 h_data_length NUMBER;
416 h_data_precision NUMBER;
417
418 h_lst_cols_desc VARCHAR2(32000);
419 h_lst_cols_index VARCHAR2(32000);
420
421 h_i NUMBER;
422 h_count NUMBER;
423 h_index_name VARCHAR2(30);
424
425 BEGIN
426
427 h_drop_it := FALSE;
428 h_create_it := FALSE;
429
430 -- If the table exists and the structure is the same then we do not
431 -- re-create the temporal table
432
433 SELECT COUNT(*)
434 INTO h_count
435 FROM all_tab_columns
436 WHERE table_name = x_table_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
437
438 IF h_count = 0 THEN
439 -- table does not exists
440 h_create_it := TRUE;
441 ELSE
442 IF h_count <> x_num_columns THEN
443 -- structure is different or table does not exist
444 h_drop_it := TRUE;
445 h_create_it := TRUE;
446 ELSE
447 h_i := 1;
448 OPEN c_table_columns(x_table_name, BSC_APPS.BSC_APPS_SCHEMA);
449 LOOP
450 FETCH c_table_columns INTO h_column_name, h_data_type, h_data_length, h_data_precision;
451 EXIT WHEN c_table_columns%NOTFOUND;
452 IF (UPPER(x_table_columns(h_i).column_name) <> h_column_name) OR
453 (x_table_columns(h_i).data_type <> h_data_type) OR
454 (h_data_type = 'VARCHAR2' AND (NVL(x_table_columns(h_i).data_size, 0) <> h_data_length)) OR
455 (h_data_type = 'NUMBER' AND (NVL(x_table_columns(h_i).data_size, 0) <> NVL(h_data_precision, 0))) THEN
456 -- structure is different
457 h_drop_it := TRUE;
458 h_create_it := TRUE;
459 EXIT;
460 END IF;
461 h_i := h_i + 1;
462 END LOOP;
463 CLOSE c_table_columns;
464 END IF;
465 END IF;
466
467 IF h_drop_it THEN
468 h_sql := 'DROP TABLE '||x_table_name;
469 BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, x_table_name);
470 END IF;
471
472 IF h_create_it THEN
473 h_lst_cols_desc := NULL;
474 h_lst_cols_index := NULL;
475
476 FOR h_i IN 1..x_num_columns LOOP
477 IF h_lst_cols_desc IS NOT NULL THEN
478 h_lst_cols_desc := h_lst_cols_desc||', ';
479 END IF;
480 h_lst_cols_desc := h_lst_cols_desc||x_table_columns(h_i).column_name||
481 ' '||x_table_columns(h_i).data_type;
482 IF x_table_columns(h_i).data_size IS NOT NULL THEN
483 h_lst_cols_desc := h_lst_cols_desc||'('||x_table_columns(h_i).data_size||')';
484 END IF;
485
486 IF x_table_columns(h_i).add_to_index = 'Y' THEN
487 IF h_lst_cols_index IS NOT NULL THEN
488 h_lst_cols_index := h_lst_cols_index||', ';
489 END IF;
490 h_lst_cols_index := h_lst_cols_index||x_table_columns(h_i).column_name;
491 END IF;
492 END LOOP;
493
494 h_sql := 'CREATE TABLE '||x_table_name||' ('||h_lst_cols_desc||')'||
495 ' TABLESPACE '||x_tablespace||
496 ' '||BSC_APPS.bsc_storage_clause;
497 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_TABLE, x_table_name);
498
499 -- Create index
500 IF h_lst_cols_index IS NOT NULL THEN
501 h_index_name := x_table_name||'_N1';
502 h_sql := 'CREATE INDEX '||h_index_name||
503 ' ON '||x_table_name||' ('||h_lst_cols_index||')'||
504 ' TABLESPACE '||x_idx_tablespace||
505 ' '||BSC_APPS.bsc_storage_clause;
506 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_INDEX, h_index_name);
507 END IF;
508 END IF;
509
510 RETURN TRUE;
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
515 x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
516 BSC_MESSAGE.Add(x_message => SQLERRM,
517 x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
518 RETURN FALSE;
519
520 END Create_Permanent_Table;
521
522
523 /*===========================================================================+
524 | FUNCTION Decompose_Numeric_List
525 +============================================================================*/
526 FUNCTION Decompose_Numeric_List(
527 x_string IN VARCHAR2,
528 x_number_array IN OUT NOCOPY t_array_of_number,
529 x_separator IN VARCHAR2
530 ) RETURN NUMBER IS
531
532 h_num_items NUMBER;
533
534 h_sub_string VARCHAR2(32700);
535 h_position NUMBER;
536
537 BEGIN
538 h_num_items := 0;
539
540 IF x_string IS NOT NULL THEN
541 h_sub_string := x_string;
542 h_position := INSTR(h_sub_string, x_separator);
543
544 WHILE h_position <> 0 LOOP
545 h_num_items := h_num_items + 1;
546 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1))));
547
548 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
549 h_position := INSTR(h_sub_string, x_separator);
550 END LOOP;
551
552 h_num_items := h_num_items + 1;
553 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(h_sub_string)));
554
555 END IF;
556
557 RETURN h_num_items;
558
559 END Decompose_Numeric_List;
560
561
562 /*===========================================================================+
563 | FUNCTION Decompose_Varchar2_List
564 +============================================================================*/
565 FUNCTION Decompose_Varchar2_List(
566 x_string IN VARCHAR2,
567 x_array IN OUT NOCOPY t_array_of_varchar2,
568 x_separator IN VARCHAR2
569 ) RETURN NUMBER IS
570
571 h_num_items NUMBER;
572
573 h_sub_string VARCHAR2(32700);
574 h_position NUMBER;
575
576 BEGIN
577 h_num_items := 0;
578
579 IF x_string IS NOT NULL THEN
580 h_sub_string := x_string;
581 h_position := INSTR(h_sub_string, x_separator);
582
583 WHILE h_position <> 0 LOOP
584 h_num_items := h_num_items + 1;
585 x_array(h_num_items) := RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1)));
586
587 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
588 h_position := INSTR(h_sub_string, x_separator);
589 END LOOP;
590
591 h_num_items := h_num_items + 1;
592 x_array(h_num_items) := RTRIM(LTRIM(h_sub_string));
593
594 END IF;
595
596 RETURN h_num_items;
597
598 END Decompose_Varchar2_List;
599
600
601 /*===========================================================================+
602 | FUNCTION Drop_Index
603 +============================================================================*/
604 FUNCTION Drop_Index(
605 x_index_name IN VARCHAR2
606 ) RETURN BOOLEAN IS
607
608 h_sql VARCHAR2(200);
609
610 TYPE t_cursor IS REF CURSOR;
611
612 /*
613 c_index t_cursor; -- x_index_name
614 c_index_sql VARCHAR2(2000) := 'SELECT index_name'||
615 ' FROM user_indexes'||
616 ' WHERE index_name = :1';
617 */
618 CURSOR c_index (p_index_name VARCHAR2) IS
619 SELECT index_name
620 FROM user_indexes
621 WHERE index_name = p_index_name;
622
623 /*
624 c_index_apps t_cursor; -- x_index_name, BSC_APPS.BSC_APPS_SCHEMA
625 c_index_apps_sql VARCHAR2(2000) := 'SELECT index_name'||
626 ' FROM all_indexes'||
627 ' WHERE index_name = :1 AND owner = :2';
628 */
629 CURSOR c_index_apps (p_index_name VARCHAR2, p_owner VARCHAR2) IS
630 SELECT index_name
631 FROM all_indexes
632 WHERE index_name = p_index_name AND owner = p_owner;
633
634 h_index VARCHAR2(30);
635 h_do_it BOOLEAN;
636 h_index_name VARCHAR2(50);
637 BEGIN
638 h_do_it := FALSE;
639
640 h_index_name := UPPER(x_index_name);
641
642 IF NOT BSC_APPS.APPS_ENV THEN
643 -- Personal
644 --OPEN c_index FOR c_index_sql USING h_index_name;
645 OPEN c_index(h_index_name);
646 FETCH c_index INTO h_index;
647 IF c_index%FOUND THEN
648 h_do_it := TRUE;
649 END IF;
650 CLOSE c_index;
651 ELSE
652 -- Personal
653 --OPEN c_index_apps FOR c_index_apps_sql USING h_index_name, BSC_APPS.BSC_APPS_SCHEMA;
654 OPEN c_index_apps(h_index_name, BSC_APPS.BSC_APPS_SCHEMA);
655 FETCH c_index_apps INTO h_index;
656 IF c_index_apps%FOUND THEN
657 h_do_it := TRUE;
658 END IF;
659 CLOSE c_index_apps;
660 END IF;
661
662 IF h_do_it THEN
663 h_sql := 'DROP INDEX '||x_index_name;
664 BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_INDEX, x_index_name);
665 END IF;
666
667 RETURN TRUE;
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 BSC_MESSAGE.Add(x_message => SQLERRM,
672 x_source => 'BSC_UPDATE_UTIL.Drop_Index');
673 RETURN FALSE;
674 END Drop_Index;
675
676
677 /*===========================================================================+
678 | FUNCTION Drop_Table
679 +============================================================================*/
680 FUNCTION Drop_Table(
681 x_table_name IN VARCHAR2
682 ) RETURN BOOLEAN IS
683
684 h_sql VARCHAR2(200);
685
686 BEGIN
687 -- Drop table if exits
688 IF Table_Exists(x_table_name) THEN
689 -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
690 Truncate_Table(x_table_name);
691
692 h_sql := 'DROP TABLE '||x_table_name;
693 BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, x_table_name);
694 END IF;
695
696 RETURN TRUE;
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 BSC_MESSAGE.Add(x_message => SQLERRM,
701 x_source => 'BSC_UPDATE_UTIL.Drop_Table');
702 RETURN FALSE;
703 END Drop_Table;
704
705
706 /*===========================================================================+
707 | PROCEDURE Execute_Immediate
708 +============================================================================*/
709 PROCEDURE Execute_Immediate(
710 x_sql IN VARCHAR2
711 ) IS
712
713 BEGIN
714 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
715 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
716
717 EXECUTE IMMEDIATE x_sql;
718
719 END Execute_Immediate;
720
721
722 -- ENH_B_TABLES_PERF: new procedure
723 /*===========================================================================+
724 | PROCEDURE Execute_Immediate
725 +============================================================================*/
726 PROCEDURE Execute_Immediate(
727 x_sql IN clob
728 ) IS
729
730 type t_array_of_varchar2 IS TABLE OF VARCHAR2(20000) INDEX BY BINARY_INTEGER;
731 h_sql_tbl t_array_of_varchar2;
732 h_i number;
733 h_j number;
734 h_offset number;
735 h_clob_length number;
736 h_str_length number;
737
738 BEGIN
739 h_str_length := 16000;
740 h_clob_length := dbms_lob.getlength(x_sql);
741 h_sql_tbl.delete;
742 h_i := 0;
743 h_offset := 1;
744 loop
745 h_i := h_i + 1;
746 h_sql_tbl(h_i) := dbms_lob.substr(x_sql, h_str_length, h_offset);
747 h_offset := h_offset + h_str_length;
748 exit when h_offset > h_clob_length;
749 end loop;
750
751 for h_j in (h_i + 1)..20 loop
752 h_sql_tbl(h_j) := null;
753 end loop;
754
755 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
756 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
757 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
758 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20);
759
760 END Execute_Immediate;
761
762
763 --Fix bug#3875046
764 /*===========================================================================+
765 | FUCTION Execute_Immediate
766 +============================================================================*/
767 FUNCTION Execute_Immediate(
768 x_sql IN VARCHAR2
769 ) RETURN NUMBER IS
770
771 BEGIN
772 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
773 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
774
775 EXECUTE IMMEDIATE x_sql;
776 RETURN SQL%ROWCOUNT;
777
778 END Execute_Immediate;
779
780
781 /*===========================================================================+
782 | PROCEDURE Execute_Immediate
783 +============================================================================*/
784 PROCEDURE Execute_Immediate(
785 x_sql IN VARCHAR2,
786 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
787 x_num_bind_vars IN NUMBER
788 ) IS
789
790 l_sql VARCHAR2(32700);
791 l_sql_quote VARCHAR2(32700);
792
793 BEGIN
794 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
795 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
796
797 IF x_num_bind_vars = 0 THEN
798 EXECUTE IMMEDIATE x_sql;
799 ELSIF x_num_bind_vars = 1 THEN
800 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1);
801 ELSIF x_num_bind_vars = 2 THEN
802 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2);
803 ELSIF x_num_bind_vars = 3 THEN
804 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
805 x_bind_vars_values(3);
806 ELSIF x_num_bind_vars = 4 THEN
807 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
808 x_bind_vars_values(3), x_bind_vars_values(4);
809 ELSIF x_num_bind_vars = 5 THEN
810 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
811 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
812 ELSIF x_num_bind_vars = 6 THEN
813 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
814 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
815 x_bind_vars_values(6);
816 ELSIF x_num_bind_vars = 7 THEN
817 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
818 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
819 x_bind_vars_values(6), x_bind_vars_values(7);
820 ELSIF x_num_bind_vars = 8 THEN
821 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
822 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
823 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
824 ELSIF x_num_bind_vars = 9 THEN
825 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
826 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
827 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
828 x_bind_vars_values(9);
829 ELSIF x_num_bind_vars = 10 THEN
830 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
831 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
832 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
833 x_bind_vars_values(9), x_bind_vars_values(10);
834 ELSIF x_num_bind_vars = 11 THEN
835 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
836 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
837 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
838 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
839 ELSIF x_num_bind_vars = 12 THEN
840 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
841 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
842 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
843 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
844 x_bind_vars_values(12);
845 ELSIF x_num_bind_vars = 13 THEN
846 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
847 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
848 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
849 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
850 x_bind_vars_values(12), x_bind_vars_values(13);
851 ELSIF x_num_bind_vars = 14 THEN
852 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
853 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
854 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
855 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
856 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
857 ELSIF x_num_bind_vars = 15 THEN
858 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
859 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
860 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
861 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
862 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14)
863 , x_bind_vars_values(15);
864 ELSIF x_num_bind_vars = 16 THEN
865 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
866 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
867 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
868 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
869 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
870 x_bind_vars_values(15), x_bind_vars_values(16);
871 ELSIF x_num_bind_vars = 17 THEN
872 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
873 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
874 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
875 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
876 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
877 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
878 ELSIF x_num_bind_vars = 18 THEN
879 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
880 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
881 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
882 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
883 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
884 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
885 x_bind_vars_values(18);
886 ELSIF x_num_bind_vars = 19 THEN
887 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
888 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
889 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
890 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
891 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
892 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
893 x_bind_vars_values(18), x_bind_vars_values(19);
894 ELSIF x_num_bind_vars = 20 THEN
895 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
896 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
897 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
898 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
899 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
900 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
901 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
902 ELSE
903 l_sql_quote := REPLACE(x_sql, '''', '''''');
904 l_sql := 'BEGIN EXECUTE IMMEDIATE '''||l_sql_quote||''' USING';
905 FOR h_i IN 1..x_num_bind_vars LOOP
906 IF h_i > 1 THEN
907 l_sql := l_sql||',';
908 END IF;
909 l_sql := l_sql||' '''||x_bind_vars_values(h_i)||'''';
910 END LOOP;
911 l_sql := l_sql||'; END;';
912 EXECUTE IMMEDIATE l_sql;
913 END IF;
914
915 END Execute_Immediate;
916
917
918 -- ENH_B_TABLES_PERF: new procedure
919 /*===========================================================================+
920 | PROCEDURE Execute_Immediate
921 +============================================================================*/
922 PROCEDURE Execute_Immediate(
923 x_sql IN clob,
924 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
925 x_num_bind_vars IN NUMBER
926 ) IS
927
928 l_sql VARCHAR2(32700);
929 l_sql_quote VARCHAR2(32700);
930
931 type t_array_of_varchar2 IS TABLE OF VARCHAR2(20000) INDEX BY BINARY_INTEGER;
932 h_sql_tbl t_array_of_varchar2;
933 h_i number;
934 h_j number;
935 h_offset number;
936 h_clob_length number;
937 h_str_length number;
938
939 BEGIN
940
941 h_str_length := 16000;
942 h_clob_length := dbms_lob.getlength(x_sql);
943 h_sql_tbl.delete;
944 h_i := 0;
945 h_offset := 1;
946 loop
947 h_i := h_i + 1;
948 h_sql_tbl(h_i) := dbms_lob.substr(x_sql, h_str_length, h_offset);
949 h_offset := h_offset + h_str_length;
950 exit when h_offset > h_clob_length;
951 end loop;
952
953 for h_j in (h_i + 1)..20 loop
954 h_sql_tbl(h_j) := null;
955 end loop;
956
957 IF x_num_bind_vars = 0 THEN
958 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
959 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
960 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
961 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20);
962 ELSIF x_num_bind_vars = 1 THEN
963 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
964 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
965 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
966 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
967 USING x_bind_vars_values(1);
968 ELSIF x_num_bind_vars = 2 THEN
969 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
970 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
971 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
972 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
973 USING x_bind_vars_values(1), x_bind_vars_values(2);
974 ELSIF x_num_bind_vars = 3 THEN
975 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
976 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
977 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
978 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
979 USING x_bind_vars_values(1), x_bind_vars_values(2),
980 x_bind_vars_values(3);
981 ELSIF x_num_bind_vars = 4 THEN
982 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
983 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
984 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
985 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
986 USING x_bind_vars_values(1), x_bind_vars_values(2),
987 x_bind_vars_values(3), x_bind_vars_values(4);
988 ELSIF x_num_bind_vars = 5 THEN
989 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
990 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
991 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
992 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
993 USING x_bind_vars_values(1), x_bind_vars_values(2),
994 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
995 ELSIF x_num_bind_vars = 6 THEN
996 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
997 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
998 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
999 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1000 USING x_bind_vars_values(1), x_bind_vars_values(2),
1001 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1002 x_bind_vars_values(6);
1003 ELSIF x_num_bind_vars = 7 THEN
1004 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1005 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1006 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1007 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1008 USING x_bind_vars_values(1), x_bind_vars_values(2),
1009 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1010 x_bind_vars_values(6), x_bind_vars_values(7);
1011 ELSIF x_num_bind_vars = 8 THEN
1012 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1013 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1014 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1015 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1016 USING x_bind_vars_values(1), x_bind_vars_values(2),
1017 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1018 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
1019 ELSIF x_num_bind_vars = 9 THEN
1020 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1021 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1022 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1023 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1024 USING x_bind_vars_values(1), x_bind_vars_values(2),
1025 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1026 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1027 x_bind_vars_values(9);
1028 ELSIF x_num_bind_vars = 10 THEN
1029 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1030 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1031 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1032 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1033 USING x_bind_vars_values(1), x_bind_vars_values(2),
1034 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1035 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1036 x_bind_vars_values(9), x_bind_vars_values(10);
1037 ELSIF x_num_bind_vars = 11 THEN
1038 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1039 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1040 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1041 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1042 USING x_bind_vars_values(1), x_bind_vars_values(2),
1043 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1044 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1045 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
1046 ELSIF x_num_bind_vars = 12 THEN
1047 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1048 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1049 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1050 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1051 USING x_bind_vars_values(1), x_bind_vars_values(2),
1052 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1053 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1054 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1055 x_bind_vars_values(12);
1056 ELSIF x_num_bind_vars = 13 THEN
1057 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1058 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1059 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1060 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1061 USING x_bind_vars_values(1), x_bind_vars_values(2),
1062 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1063 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1064 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1065 x_bind_vars_values(12), x_bind_vars_values(13);
1066 ELSIF x_num_bind_vars = 14 THEN
1067 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1068 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1069 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1070 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1071 USING x_bind_vars_values(1), x_bind_vars_values(2),
1072 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1073 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1074 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1075 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
1076 ELSIF x_num_bind_vars = 15 THEN
1077 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1078 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1079 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1080 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1081 USING x_bind_vars_values(1), x_bind_vars_values(2),
1082 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1083 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1084 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1085 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14)
1086 , x_bind_vars_values(15);
1087 ELSIF x_num_bind_vars = 16 THEN
1088 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1089 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1090 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1091 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1092 USING x_bind_vars_values(1), x_bind_vars_values(2),
1093 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1094 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1095 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1096 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1097 x_bind_vars_values(15), x_bind_vars_values(16);
1098 ELSIF x_num_bind_vars = 17 THEN
1099 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1100 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1101 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1102 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1103 USING x_bind_vars_values(1), x_bind_vars_values(2),
1104 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1105 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1106 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1107 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1108 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
1109 ELSIF x_num_bind_vars = 18 THEN
1110 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1111 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1112 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1113 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1114 USING x_bind_vars_values(1), x_bind_vars_values(2),
1115 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1116 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1117 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1118 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1119 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1120 x_bind_vars_values(18);
1121 ELSIF x_num_bind_vars = 19 THEN
1122 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1123 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1124 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1125 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1126 USING x_bind_vars_values(1), x_bind_vars_values(2),
1127 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1128 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1129 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1130 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1131 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1132 x_bind_vars_values(18), x_bind_vars_values(19);
1133 ELSIF x_num_bind_vars = 20 THEN
1134 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1135 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1136 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1137 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1138 USING x_bind_vars_values(1), x_bind_vars_values(2),
1139 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1140 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1141 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1142 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1143 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1144 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
1145 END IF;
1146
1147 END Execute_Immediate;
1148
1149
1150 --Fix bug#3875046
1151 /*===========================================================================+
1152 | FUNCTION Execute_Immediate
1153 +============================================================================*/
1154 FUNCTION Execute_Immediate(
1155 x_sql IN VARCHAR2,
1156 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1157 x_num_bind_vars IN NUMBER
1158 ) RETURN NUMBER IS
1159
1160 l_sql VARCHAR2(32700);
1161 l_sql_quote VARCHAR2(32700);
1162
1163 BEGIN
1164 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
1165 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
1166
1167 IF x_num_bind_vars = 0 THEN
1168 EXECUTE IMMEDIATE x_sql;
1169 ELSIF x_num_bind_vars = 1 THEN
1170 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1);
1171 ELSIF x_num_bind_vars = 2 THEN
1172 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2);
1173 ELSIF x_num_bind_vars = 3 THEN
1174 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1175 x_bind_vars_values(3);
1176 ELSIF x_num_bind_vars = 4 THEN
1177 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1178 x_bind_vars_values(3), x_bind_vars_values(4);
1179 ELSIF x_num_bind_vars = 5 THEN
1180 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1181 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
1182 ELSIF x_num_bind_vars = 6 THEN
1183 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1184 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1185 x_bind_vars_values(6);
1186 ELSIF x_num_bind_vars = 7 THEN
1187 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1188 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1189 x_bind_vars_values(6), x_bind_vars_values(7);
1190 ELSIF x_num_bind_vars = 8 THEN
1191 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1192 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1193 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
1194 ELSIF x_num_bind_vars = 9 THEN
1195 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1196 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1197 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1198 x_bind_vars_values(9);
1199 ELSIF x_num_bind_vars = 10 THEN
1200 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1201 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1202 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1203 x_bind_vars_values(9), x_bind_vars_values(10);
1204 ELSIF x_num_bind_vars = 11 THEN
1205 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1206 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1207 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1208 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
1209 ELSIF x_num_bind_vars = 12 THEN
1210 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1211 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1212 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1213 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1214 x_bind_vars_values(12);
1215 ELSIF x_num_bind_vars = 13 THEN
1216 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1217 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1218 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1219 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1220 x_bind_vars_values(12), x_bind_vars_values(13);
1221 ELSIF x_num_bind_vars = 14 THEN
1222 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1223 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1224 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1225 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1226 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
1227 ELSIF x_num_bind_vars = 15 THEN
1228 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1229 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1230 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1231 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1232 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14)
1233 , x_bind_vars_values(15);
1234 ELSIF x_num_bind_vars = 16 THEN
1235 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1236 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1237 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1238 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1239 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1240 x_bind_vars_values(15), x_bind_vars_values(16);
1241 ELSIF x_num_bind_vars = 17 THEN
1242 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1243 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1244 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1245 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1246 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1247 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
1248 ELSIF x_num_bind_vars = 18 THEN
1249 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1250 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1251 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1252 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1253 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1254 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1255 x_bind_vars_values(18);
1256 ELSIF x_num_bind_vars = 19 THEN
1257 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1258 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1259 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1260 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1261 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1262 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1263 x_bind_vars_values(18), x_bind_vars_values(19);
1264 ELSIF x_num_bind_vars = 20 THEN
1265 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1266 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1267 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1268 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1269 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1270 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1271 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
1272 ELSE
1273 l_sql_quote := REPLACE(x_sql, '''', '''''');
1274 l_sql := 'BEGIN EXECUTE IMMEDIATE '''||l_sql_quote||''' USING';
1275 FOR h_i IN 1..x_num_bind_vars LOOP
1276 IF h_i > 1 THEN
1277 l_sql := l_sql||',';
1278 END IF;
1279 l_sql := l_sql||' '''||x_bind_vars_values(h_i)||'''';
1280 END LOOP;
1281 l_sql := l_sql||'; END;';
1282 EXECUTE IMMEDIATE l_sql;
1283 END IF;
1284
1285 RETURN SQL%ROWCOUNT;
1286
1287 END Execute_Immediate;
1288
1289
1290 /*===========================================================================+
1291 | PROCEDURE Execute_Immediate
1292 +============================================================================*/
1293 PROCEDURE Execute_Immediate(
1294 x_sql IN VARCHAR2,
1295 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1296 x_num_bind_vars IN NUMBER
1297 ) IS
1298
1299 l_sql VARCHAR2(32700);
1300 l_sql_quote VARCHAR2(32700);
1301
1302 BEGIN
1303 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
1304 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
1305
1306 IF x_num_bind_vars = 0 THEN
1307 EXECUTE IMMEDIATE x_sql;
1308 ELSIF x_num_bind_vars = 1 THEN
1309 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1);
1310 ELSIF x_num_bind_vars = 2 THEN
1311 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2);
1312 ELSIF x_num_bind_vars = 3 THEN
1313 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1314 x_bind_vars_values(3);
1315 ELSIF x_num_bind_vars = 4 THEN
1316 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1317 x_bind_vars_values(3), x_bind_vars_values(4);
1318 ELSIF x_num_bind_vars = 5 THEN
1319 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1320 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
1321 ELSIF x_num_bind_vars = 6 THEN
1322 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1323 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1324 x_bind_vars_values(6);
1325 ELSIF x_num_bind_vars = 7 THEN
1326 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1327 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1328 x_bind_vars_values(6), x_bind_vars_values(7);
1329 ELSIF x_num_bind_vars = 8 THEN
1330 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1331 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1332 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
1333 ELSIF x_num_bind_vars = 9 THEN
1334 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1335 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1336 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1337 x_bind_vars_values(9);
1338 ELSIF x_num_bind_vars = 10 THEN
1339 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1340 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1341 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1342 x_bind_vars_values(9), x_bind_vars_values(10);
1343 ELSIF x_num_bind_vars = 11 THEN
1344 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1345 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1346 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1347 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
1348 ELSIF x_num_bind_vars = 12 THEN
1349 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1350 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1351 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1352 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1353 x_bind_vars_values(12);
1354 ELSIF x_num_bind_vars = 13 THEN
1355 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1356 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1357 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1358 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1359 x_bind_vars_values(12), x_bind_vars_values(13);
1360 ELSIF x_num_bind_vars = 14 THEN
1361 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1362 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1363 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1364 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1365 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
1366 ELSIF x_num_bind_vars = 15 THEN
1367 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1368 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1369 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1370 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1371 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1372 x_bind_vars_values(15);
1373 ELSIF x_num_bind_vars = 16 THEN
1374 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1375 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1376 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1377 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1378 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1379 x_bind_vars_values(15), x_bind_vars_values(16);
1380 ELSIF x_num_bind_vars = 17 THEN
1381 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1382 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1383 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1384 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1385 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1386 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
1387 ELSIF x_num_bind_vars = 18 THEN
1388 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1389 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1390 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1391 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1392 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1393 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1394 x_bind_vars_values(18);
1395 ELSIF x_num_bind_vars = 19 THEN
1396 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1397 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1398 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1399 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1400 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1401 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1402 x_bind_vars_values(18), x_bind_vars_values(19);
1403 ELSIF x_num_bind_vars = 20 THEN
1404 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1405 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1406 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1407 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1408 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1409 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1410 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
1411 ELSE
1412 l_sql_quote := REPLACE(x_sql, '''', '''''');
1413 l_sql := 'BEGIN EXECUTE IMMEDIATE '''||l_sql_quote||''' USING';
1414 FOR h_i IN 1..x_num_bind_vars LOOP
1415 IF h_i > 1 THEN
1416 l_sql := l_sql||',';
1417 END IF;
1418 l_sql := l_sql||' '||x_bind_vars_values(h_i);
1419 END LOOP;
1420 l_sql := l_sql||'; END;';
1421 EXECUTE IMMEDIATE l_sql;
1422 END IF;
1423
1424 END Execute_Immediate;
1425
1426
1427 -- ENH_B_TABLES_PERF: new procedure
1428 /*===========================================================================+
1429 | PROCEDURE Execute_Immediate
1430 +============================================================================*/
1431 PROCEDURE Execute_Immediate(
1432 x_sql IN clob,
1433 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1434 x_num_bind_vars IN NUMBER
1435 ) IS
1436
1437 l_sql VARCHAR2(32700);
1438 l_sql_quote VARCHAR2(32700);
1439
1440 type t_array_of_varchar2 IS TABLE OF VARCHAR2(20000) INDEX BY BINARY_INTEGER;
1441 h_sql_tbl t_array_of_varchar2;
1442 h_i number;
1443 h_j number;
1444 h_offset number;
1445 h_clob_length number;
1446 h_str_length number;
1447
1448 BEGIN
1449
1450 h_str_length := 16000;
1451 h_clob_length := dbms_lob.getlength(x_sql);
1452 h_sql_tbl.delete;
1453 h_i := 0;
1454 h_offset := 1;
1455 loop
1456 h_i := h_i + 1;
1457 h_sql_tbl(h_i) := dbms_lob.substr(x_sql, h_str_length, h_offset);
1458 h_offset := h_offset + h_str_length;
1459 exit when h_offset > h_clob_length;
1460 end loop;
1461
1462 for h_j in (h_i + 1)..20 loop
1463 h_sql_tbl(h_j) := null;
1464 end loop;
1465
1466 IF x_num_bind_vars = 0 THEN
1467 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1468 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1469 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1470 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20);
1471 ELSIF x_num_bind_vars = 1 THEN
1472 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1473 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1474 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1475 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1476 USING x_bind_vars_values(1);
1477 ELSIF x_num_bind_vars = 2 THEN
1478 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1479 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1480 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1481 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1482 USING x_bind_vars_values(1), x_bind_vars_values(2);
1483 ELSIF x_num_bind_vars = 3 THEN
1484 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1485 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1486 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1487 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1488 USING x_bind_vars_values(1), x_bind_vars_values(2),
1489 x_bind_vars_values(3);
1490 ELSIF x_num_bind_vars = 4 THEN
1491 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1492 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1493 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1494 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1495 USING x_bind_vars_values(1), x_bind_vars_values(2),
1496 x_bind_vars_values(3), x_bind_vars_values(4);
1497 ELSIF x_num_bind_vars = 5 THEN
1498 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1499 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1500 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1501 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1502 USING x_bind_vars_values(1), x_bind_vars_values(2),
1503 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
1504 ELSIF x_num_bind_vars = 6 THEN
1505 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1506 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1507 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1508 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1509 USING x_bind_vars_values(1), x_bind_vars_values(2),
1510 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1511 x_bind_vars_values(6);
1512 ELSIF x_num_bind_vars = 7 THEN
1513 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1514 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1515 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1516 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1517 USING x_bind_vars_values(1), x_bind_vars_values(2),
1518 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1519 x_bind_vars_values(6), x_bind_vars_values(7);
1520 ELSIF x_num_bind_vars = 8 THEN
1521 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1522 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1523 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1524 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1525 USING x_bind_vars_values(1), x_bind_vars_values(2),
1526 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1527 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
1528 ELSIF x_num_bind_vars = 9 THEN
1529 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1530 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1531 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1532 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1533 USING x_bind_vars_values(1), x_bind_vars_values(2),
1534 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1535 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1536 x_bind_vars_values(9);
1537 ELSIF x_num_bind_vars = 10 THEN
1538 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1539 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1540 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1541 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1542 USING x_bind_vars_values(1), x_bind_vars_values(2),
1543 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1544 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1545 x_bind_vars_values(9), x_bind_vars_values(10);
1546 ELSIF x_num_bind_vars = 11 THEN
1547 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1548 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1549 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1550 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1551 USING x_bind_vars_values(1), x_bind_vars_values(2),
1552 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1553 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1554 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
1555 ELSIF x_num_bind_vars = 12 THEN
1556 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1557 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1558 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1559 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1560 USING x_bind_vars_values(1), x_bind_vars_values(2),
1561 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1562 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1563 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1564 x_bind_vars_values(12);
1565 ELSIF x_num_bind_vars = 13 THEN
1566 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1567 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1568 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1569 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1570 USING x_bind_vars_values(1), x_bind_vars_values(2),
1571 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1572 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1573 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1574 x_bind_vars_values(12), x_bind_vars_values(13);
1575 ELSIF x_num_bind_vars = 14 THEN
1576 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1577 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1578 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1579 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1580 USING x_bind_vars_values(1), x_bind_vars_values(2),
1581 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1582 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1583 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1584 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
1585 ELSIF x_num_bind_vars = 15 THEN
1586 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1587 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1588 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1589 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1590 USING x_bind_vars_values(1), x_bind_vars_values(2),
1591 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1592 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1593 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1594 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14)
1595 , x_bind_vars_values(15);
1596 ELSIF x_num_bind_vars = 16 THEN
1597 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1598 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1599 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1600 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1601 USING x_bind_vars_values(1), x_bind_vars_values(2),
1602 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1603 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1604 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1605 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1606 x_bind_vars_values(15), x_bind_vars_values(16);
1607 ELSIF x_num_bind_vars = 17 THEN
1608 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1609 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1610 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1611 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1612 USING x_bind_vars_values(1), x_bind_vars_values(2),
1613 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1614 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1615 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1616 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1617 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
1618 ELSIF x_num_bind_vars = 18 THEN
1619 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1620 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1621 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1622 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1623 USING x_bind_vars_values(1), x_bind_vars_values(2),
1624 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1625 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1626 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1627 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1628 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1629 x_bind_vars_values(18);
1630 ELSIF x_num_bind_vars = 19 THEN
1631 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1632 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1633 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1634 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1635 USING x_bind_vars_values(1), x_bind_vars_values(2),
1636 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1637 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1638 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1639 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1640 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1641 x_bind_vars_values(18), x_bind_vars_values(19);
1642 ELSIF x_num_bind_vars = 20 THEN
1643 EXECUTE IMMEDIATE h_sql_tbl(1)||h_sql_tbl(2)||h_sql_tbl(3)||h_sql_tbl(4)||h_sql_tbl(5)||
1644 h_sql_tbl(6)||h_sql_tbl(7)||h_sql_tbl(8)||h_sql_tbl(9)||h_sql_tbl(10)||
1645 h_sql_tbl(11)||h_sql_tbl(12)||h_sql_tbl(13)||h_sql_tbl(14)||h_sql_tbl(15)||
1646 h_sql_tbl(16)||h_sql_tbl(17)||h_sql_tbl(18)||h_sql_tbl(19)||h_sql_tbl(20)
1647 USING x_bind_vars_values(1), x_bind_vars_values(2),
1648 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1649 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1650 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1651 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1652 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1653 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
1654 END IF;
1655
1656 END Execute_Immediate;
1657
1658
1659 --Fix bug#3875046
1660 /*===========================================================================+
1661 | FUNCTION Execute_Immediate
1662 +============================================================================*/
1663 FUNCTION Execute_Immediate(
1664 x_sql IN VARCHAR2,
1665 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1666 x_num_bind_vars IN NUMBER
1667 ) RETURN NUMBER IS
1668
1669 l_sql VARCHAR2(32700);
1670 l_sql_quote VARCHAR2(32700);
1671
1672 BEGIN
1673 --BIS_IM_UTILS.write_to_log_file_n(x_sql);
1674 --BSC_UPDATE_LOG.Write_Line_Log(x_sql, BSC_UPDATE_LOG.LOG);
1675
1676 IF x_num_bind_vars = 0 THEN
1677 EXECUTE IMMEDIATE x_sql;
1678 ELSIF x_num_bind_vars = 1 THEN
1679 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1);
1680 ELSIF x_num_bind_vars = 2 THEN
1681 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2);
1682 ELSIF x_num_bind_vars = 3 THEN
1683 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1684 x_bind_vars_values(3);
1685 ELSIF x_num_bind_vars = 4 THEN
1686 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1687 x_bind_vars_values(3), x_bind_vars_values(4);
1688 ELSIF x_num_bind_vars = 5 THEN
1689 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1690 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5);
1691 ELSIF x_num_bind_vars = 6 THEN
1692 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1693 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1694 x_bind_vars_values(6);
1695 ELSIF x_num_bind_vars = 7 THEN
1696 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1697 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1698 x_bind_vars_values(6), x_bind_vars_values(7);
1699 ELSIF x_num_bind_vars = 8 THEN
1700 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1701 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1702 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8);
1703 ELSIF x_num_bind_vars = 9 THEN
1704 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1705 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1706 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1707 x_bind_vars_values(9);
1708 ELSIF x_num_bind_vars = 10 THEN
1709 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1710 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1711 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1712 x_bind_vars_values(9), x_bind_vars_values(10);
1713 ELSIF x_num_bind_vars = 11 THEN
1714 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1715 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1716 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1717 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11);
1718 ELSIF x_num_bind_vars = 12 THEN
1719 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1720 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1721 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1722 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1723 x_bind_vars_values(12);
1724 ELSIF x_num_bind_vars = 13 THEN
1725 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1726 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1727 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1728 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1729 x_bind_vars_values(12), x_bind_vars_values(13);
1730 ELSIF x_num_bind_vars = 14 THEN
1731 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1732 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1733 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1734 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1735 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14);
1736 ELSIF x_num_bind_vars = 15 THEN
1737 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1738 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1739 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1740 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1741 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1742 x_bind_vars_values(15);
1743 ELSIF x_num_bind_vars = 16 THEN
1744 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1745 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1746 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1747 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1748 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1749 x_bind_vars_values(15), x_bind_vars_values(16);
1750 ELSIF x_num_bind_vars = 17 THEN
1751 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1752 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1753 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1754 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1755 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1756 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17);
1757 ELSIF x_num_bind_vars = 18 THEN
1758 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1759 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1760 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1761 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1762 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1763 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1764 x_bind_vars_values(18);
1765 ELSIF x_num_bind_vars = 19 THEN
1766 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1767 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1768 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1769 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1770 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1771 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1772 x_bind_vars_values(18), x_bind_vars_values(19);
1773 ELSIF x_num_bind_vars = 20 THEN
1774 EXECUTE IMMEDIATE x_sql USING x_bind_vars_values(1), x_bind_vars_values(2),
1775 x_bind_vars_values(3), x_bind_vars_values(4), x_bind_vars_values(5),
1776 x_bind_vars_values(6), x_bind_vars_values(7), x_bind_vars_values(8),
1777 x_bind_vars_values(9), x_bind_vars_values(10), x_bind_vars_values(11),
1778 x_bind_vars_values(12), x_bind_vars_values(13), x_bind_vars_values(14),
1779 x_bind_vars_values(15), x_bind_vars_values(16), x_bind_vars_values(17),
1780 x_bind_vars_values(18), x_bind_vars_values(19), x_bind_vars_values(20);
1781 ELSE
1782 l_sql_quote := REPLACE(x_sql, '''', '''''');
1783 l_sql := 'BEGIN EXECUTE IMMEDIATE '''||l_sql_quote||''' USING';
1784 FOR h_i IN 1..x_num_bind_vars LOOP
1785 IF h_i > 1 THEN
1786 l_sql := l_sql||',';
1787 END IF;
1788 l_sql := l_sql||' '||x_bind_vars_values(h_i);
1789 END LOOP;
1790 l_sql := l_sql||'; END;';
1791 EXECUTE IMMEDIATE l_sql;
1792 END IF;
1793
1794 RETURN SQL%ROWCOUNT;
1795
1796 END Execute_Immediate;
1797
1798
1799 /*===========================================================================+
1800 | FUNCTION Exist_Periodicity_Rel
1801 +============================================================================*/
1802 FUNCTION Exist_Periodicity_Rel(
1803 x_periodicity_id IN NUMBER,
1804 x_source_periodicity_id IN NUMBER
1805 ) RETURN BOOLEAN IS
1806
1807 h_i NUMBER;
1808
1809 BEGIN
1810
1811 FOR h_i IN 1..g_array_periodicity_rels.COUNT LOOP
1812 IF g_array_periodicity_rels(h_i).periodicity_id = x_periodicity_id AND
1813 g_array_periodicity_rels(h_i).source_periodicity_id = x_source_periodicity_id THEN
1814 RETURN TRUE;
1815 END IF;
1816 END LOOP;
1817
1818 RETURN FALSE;
1819
1820 END Exist_Periodicity_Rel;
1821
1822
1823 /*===========================================================================+
1824 | FUNCTION Get_Calendar_EDW_Flag
1825 +============================================================================*/
1826 FUNCTION Get_Calendar_EDW_Flag(
1827 x_calendar_id IN NUMBER
1828 ) RETURN NUMBER IS
1829
1830 h_edw_flag NUMBER;
1831
1832 TYPE t_cursor IS REF CURSOR;
1833 h_cursor t_cursor;
1834 h_sql VARCHAR2(32000);
1835
1836 BEGIN
1837
1838 IF g_array_calendars.exists(x_calendar_id) THEN
1839 IF g_array_calendars(x_calendar_id).edw_flag IS NOT NULL THEN
1840 RETURN g_array_calendars(x_calendar_id).edw_flag;
1841 END IF;
1842 END IF;
1843
1844 /* h_sql := 'SELECT edw_flag'||
1845 ' FROM bsc_sys_calendars_b'||
1846 ' WHERE calendar_id = :1';
1847 OPEN h_cursor FOR h_sql USING x_calendar_id;
1848 FETCH h_cursor INTO h_edw_flag;
1849 CLOSE h_cursor; */
1850 begin
1851 SELECT edw_flag
1852 into h_edw_flag
1853 FROM bsc_sys_calendars_b
1854 where calendar_id = x_calendar_id;
1855 exception when no_data_found then h_edw_flag := null;
1856 end;
1857
1858 g_array_calendars(x_calendar_id).edw_flag := h_edw_flag;
1859
1860 RETURN h_edw_flag;
1861
1862 END Get_Calendar_EDW_Flag;
1863
1864
1865 /*===========================================================================+
1866 | FUNCTION Get_Calendar_Source
1867 +============================================================================*/
1868 FUNCTION Get_Calendar_Source(
1869 x_calendar_id IN NUMBER
1870 ) RETURN VARCHAR2 IS
1871
1872 h_calendar_source VARCHAR2(20);
1873
1874 h_sql VARCHAR2(32000);
1875
1876 BEGIN
1877
1878 IF g_array_calendars.exists(x_calendar_id) THEN
1879 IF g_array_calendars(x_calendar_id).source IS NOT NULL THEN
1880 RETURN g_array_calendars(x_calendar_id).source;
1881 END IF;
1882 END IF;
1883
1884 begin
1885 SELECT decode(nvl(edw_calendar_type_id, 0), 1, 'PMF', 'BSC')
1886 INTO h_calendar_source
1887 FROM bsc_sys_calendars_b
1888 where calendar_id = x_calendar_id;
1889 exception when no_data_found then h_calendar_source := null;
1890 end;
1891
1892 g_array_calendars(x_calendar_id).source := h_calendar_source;
1893
1894 RETURN h_calendar_source;
1895
1896 END Get_Calendar_Source;
1897
1898
1899 /*===========================================================================+
1900 | FUNCTION Get_Calendar_Fiscal_Year
1901 +============================================================================*/
1902 FUNCTION Get_Calendar_Fiscal_Year(
1903 x_calendar_id IN NUMBER
1904 ) RETURN NUMBER IS
1905
1906 h_fiscal_year NUMBER;
1907
1908 TYPE t_cursor IS REF CURSOR;
1909 h_cursor t_cursor;
1910 h_sql VARCHAR2(32000);
1911
1912 BEGIN
1913 h_fiscal_year := 0;
1914
1915 IF g_array_calendars.exists(x_calendar_id) THEN
1916 IF g_array_calendars(x_calendar_id).fiscal_year IS NOT NULL THEN
1917 RETURN g_array_calendars(x_calendar_id).fiscal_year;
1918 END IF;
1919 END IF;
1920
1921 /*
1922 h_sql := 'SELECT fiscal_year'||
1923 ' FROM bsc_sys_calendars_b'||
1924 ' WHERE calendar_id = :1';
1925 OPEN h_cursor FOR h_sql USING x_calendar_id;
1926 FETCH h_cursor INTO h_fiscal_year;
1927 CLOSE h_cursor;
1928 */
1929 SELECT fiscal_year
1930 INTO h_fiscal_year
1931 FROM bsc_sys_calendars_b
1932 WHERE calendar_id = x_calendar_id;
1933
1934 g_array_calendars(x_calendar_id).fiscal_year := h_fiscal_year;
1935
1936 RETURN h_fiscal_year;
1937
1938 END Get_Calendar_Fiscal_Year;
1939
1940
1941 /*===========================================================================+
1942 | FUNCTION Get_Calendar_Id
1943 +============================================================================*/
1944 FUNCTION Get_Calendar_Id(
1945 x_periodicity_id IN NUMBER
1946 ) RETURN NUMBER IS
1947
1948 h_calendar_id NUMBER;
1949
1950 TYPE t_cursor IS REF CURSOR;
1951 h_cursor t_cursor;
1952 h_sql VARCHAR2(32000);
1953
1954 BEGIN
1955
1956 IF g_array_periodicities.exists(x_periodicity_id) THEN
1957 IF g_array_periodicities(x_periodicity_id).calendar_id IS NOT NULL THEN
1958 RETURN g_array_periodicities(x_periodicity_id).calendar_id;
1959 END IF;
1960 END IF;
1961
1962 /*
1963 h_sql := 'SELECT calendar_id'||
1964 ' FROM bsc_sys_periodicities'||
1965 ' WHERE periodicity_id = :1';
1966 OPEN h_cursor FOR h_sql USING x_periodicity_id;
1967 FETCH h_cursor INTO h_calendar_id;
1968 CLOSE h_cursor;
1969 */
1970 SELECT calendar_id
1971 INTO h_calendar_id
1972 FROM bsc_sys_periodicities
1973 WHERE periodicity_id = x_periodicity_id;
1974
1975 g_array_periodicities(x_periodicity_id).calendar_id := h_calendar_id;
1976
1977 RETURN h_calendar_id;
1978
1979 END Get_Calendar_Id;
1980
1981
1982 /*===========================================================================+
1983 | FUNCTION Get_Calendar_Name
1984 +============================================================================*/
1985 FUNCTION Get_Calendar_Name(
1986 x_calendar_id IN NUMBER
1987 ) RETURN VARCHAR2 IS
1988
1989 h_calendar_name VARCHAR2(400);
1990
1991 TYPE t_cursor IS REF CURSOR;
1992 h_cursor t_cursor;
1993 h_sql VARCHAR2(32000);
1994
1995 BEGIN
1996 /*
1997 h_sql := 'SELECT DECODE(C.EDW_FLAG, 0, C.NAME, C.NAME||'' (''||T.NAME||'')'')'||
1998 ' FROM BSC_SYS_CALENDARS_VL C, BSC_EDW_CALENDAR_TYPE_VL T'||
1999 ' WHERE C.CALENDAR_ID = :1 AND C.EDW_CALENDAR_TYPE_ID = T.EDW_CALENDAR_TYPE_ID (+)';
2000 OPEN h_cursor FOR h_sql USING x_calendar_id;
2001 FETCH h_cursor INTO h_calendar_name;
2002 CLOSE h_cursor;
2003 */
2004 SELECT DECODE(C.EDW_FLAG, 0, C.NAME, C.NAME||' ('||T.NAME||')')
2005 INTO h_calendar_name
2006 FROM BSC_SYS_CALENDARS_VL C, BSC_EDW_CALENDAR_TYPE_VL T
2007 WHERE C.CALENDAR_ID = x_calendar_id AND C.EDW_CALENDAR_TYPE_ID = T.EDW_CALENDAR_TYPE_ID (+);
2008
2009 RETURN h_calendar_name;
2010
2011 END Get_Calendar_Name;
2012
2013
2014 /*===========================================================================+
2015 | FUNCTION Get_Calendar_Start_Date
2016 +============================================================================*/
2017 FUNCTION Get_Calendar_Start_Date(
2018 x_calendar_id IN NUMBER,
2019 x_current_fy IN NUMBER,
2020 x_start_year OUT NOCOPY NUMBER,
2021 x_start_month OUT NOCOPY NUMBER,
2022 x_start_day OUT NOCOPY NUMBER
2023 ) RETURN BOOLEAN IS
2024
2025 TYPE t_cursor IS REF CURSOR;
2026 h_cursor t_cursor;
2027 h_sql VARCHAR2(32000);
2028
2029 BEGIN
2030 IF g_array_calendars.exists(x_calendar_id) THEN
2031 IF g_array_calendars(x_calendar_id).start_year IS NOT NULL THEN
2032 x_start_year := g_array_calendars(x_calendar_id).start_year;
2033 x_start_month := g_array_calendars(x_calendar_id).start_month;
2034 x_start_day := g_array_calendars(x_calendar_id).start_day;
2035 RETURN TRUE;
2036 END IF;
2037 END IF;
2038
2039 /*
2040 h_sql := 'SELECT current_year, start_month, start_day'||
2041 ' FROM bsc_sys_calendars_b'||
2042 ' WHERE calendar_id = :1';
2043 OPEN h_cursor FOR h_sql USING x_calendar_id;
2044 FETCH h_cursor INTO x_start_year, x_start_month, x_start_day;
2045 CLOSE h_cursor;
2046 */
2047 SELECT current_year, start_month, start_day
2048 INTO x_start_year, x_start_month, x_start_day
2049 FROM bsc_sys_calendars_b
2050 WHERE calendar_id = x_calendar_id;
2051
2052 -- Fix the start year
2053 IF x_start_month = 1 THEN
2054 x_start_year := x_current_fy;
2055 ELSE
2056 x_start_year := x_current_fy - 1;
2057 END IF;
2058
2059 UPDATE bsc_sys_calendars_b
2060 SET current_year = x_start_year
2061 WHERE calendar_id = x_calendar_id;
2062
2063 g_array_calendars(x_calendar_id).start_year := x_start_year;
2064 g_array_calendars(x_calendar_id).start_month := x_start_month;
2065 g_array_calendars(x_calendar_id).start_day := x_start_day;
2066
2067 RETURN TRUE;
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 BSC_MESSAGE.Add(x_message => SQLERRM,
2072 x_source => 'BSC_UPDATE_UTIL.Get_Calendar_Start_Date');
2073 RETURN FALSE;
2074 END Get_Calendar_Start_Date;
2075
2076
2077 /*===========================================================================+
2078 | FUNCTION Get_Calendar_Table_Col_Name
2079 +============================================================================*/
2080 FUNCTION Get_Calendar_Table_Col_Name(
2081 x_periodicity_id IN NUMBER
2082 ) RETURN VARCHAR2 IS
2083
2084 h_db_column_name VARCHAR2(50);
2085
2086 TYPE t_cursor IS REF CURSOR;
2087 h_cursor t_cursor;
2088 h_sql VARCHAR2(32000);
2089
2090 BEGIN
2091
2092 IF g_array_periodicities.exists(x_periodicity_id) THEN
2093 IF g_array_periodicities(x_periodicity_id).db_column_name IS NOT NULL THEN
2094 RETURN g_array_periodicities(x_periodicity_id).db_column_name;
2095 END IF;
2096 END IF;
2097
2098 /*
2099 h_sql := 'SELECT db_column_name'||
2100 ' FROM bsc_sys_periodicities'||
2101 ' WHERE periodicity_id = :1';
2102 OPEN h_cursor FOR h_sql USING x_periodicity_id;
2103 FETCH h_cursor INTO h_db_column_name;
2104 CLOSE h_cursor;
2105 */
2106 SELECT db_column_name
2107 INTO h_db_column_name
2108 FROM bsc_sys_periodicities
2109 WHERE periodicity_id = x_periodicity_id;
2110
2111 g_array_periodicities(x_periodicity_id).db_column_name := h_db_column_name;
2112
2113 RETURN h_db_column_name;
2114
2115 END Get_Calendar_Table_Col_Name;
2116
2117
2118 --AW_INTEGRATION: New function
2119 /*===========================================================================+
2120 | FUNCTION Get_Dim_Level_Table_Name
2121 +============================================================================*/
2122 FUNCTION Get_Dim_Level_Table_Name(
2123 x_level_pk_col IN VARCHAR2
2124 ) RETURN VARCHAR2 IS
2125
2126 h_level_table_name VARCHAR2(50);
2127
2128 BEGIN
2129 h_level_table_name := NULL;
2130
2131 SELECT level_table_name
2132 INTO h_level_table_name
2133 FROM bsc_sys_dim_levels_b
2134 WHERE level_pk_col = x_level_pk_col;
2135
2136 RETURN h_level_table_name;
2137
2138 END Get_Dim_Level_Table_Name;
2139
2140
2141 --AW_INTEGRATION: New function
2142 /*===========================================================================+
2143 | FUNCTION Get_Dim_Level_View_Name
2144 +============================================================================*/
2145 FUNCTION Get_Dim_Level_View_Name(
2146 x_level_pk_col IN VARCHAR2
2147 ) RETURN VARCHAR2 IS
2148
2149 h_level_view_name VARCHAR2(50);
2150
2151 BEGIN
2152 h_level_view_name := NULL;
2153
2154 SELECT level_view_name
2155 INTO h_level_view_name
2156 FROM bsc_sys_dim_levels_b
2157 WHERE level_pk_col = x_level_pk_col;
2158
2159 RETURN h_level_view_name;
2160
2161 END Get_Dim_Level_View_Name;
2162
2163
2164 /*===========================================================================+
2165 | FUNCTION Get_EDW_Materialized_View_Name
2166 +============================================================================*/
2167 FUNCTION Get_EDW_Materialized_View_Name(
2168 x_table_name IN VARCHAR2
2169 ) RETURN VARCHAR2 IS
2170 BEGIN
2171 RETURN (x_table_name||'_MV_V');
2172 END Get_EDW_Materialized_View_Name;
2173
2174
2175 /*===========================================================================+
2176 | FUNCTION Get_EDW_Union_View_Name
2177 +============================================================================*/
2178 FUNCTION Get_EDW_Union_View_Name(
2179 x_table_name IN VARCHAR2
2180 ) RETURN VARCHAR2 IS
2181 BEGIN
2182 RETURN (x_table_name||'_V');
2183 END Get_EDW_Union_View_Name;
2184
2185
2186 /*===========================================================================+
2187 | FUNCTION Get_Free_Div_Zero_Expression
2188 +============================================================================*/
2189 FUNCTION Get_Free_Div_Zero_Expression(
2190 x_expression IN VARCHAR2
2191 ) RETURN VARCHAR2 IS
2192
2193 h_expression VARCHAR2(32700);
2194
2195 h_pos NUMBER;
2196 h_pos1 NUMBER;
2197 h_aux VARCHAR2(32700);
2198 h_field VARCHAR2(32700);
2199 h_fieldE VARCHAR2(26);
2200 h_decodeF VARCHAR2(7);
2201 h_group_counter NUMBER;
2202 h_groups BOOLEAN;
2203
2204 BEGIN
2205 h_fieldE := 'DECODE(FIELD,0,NULL,FIELD)';
2206 h_decodeF := 'DECODE(';
2207
2208 h_expression := x_expression;
2209 h_pos1 := INSTR(h_expression, '/', 1);
2210
2211 IF h_pos1 > 0 THEN
2212 LOOP
2213 IF h_pos1 > 0 THEN
2214 h_pos := h_pos1 + 1;
2215 h_field := NULL;
2216 h_group_counter := 0;
2217 h_groups := FALSE;
2218
2219 WHILE h_pos <= LENGTH(h_expression) LOOP
2220 h_aux := SUBSTR(h_expression, h_pos, 1);
2221 IF h_aux IN ('+', '-', '*', '/', '(', ')', ',') THEN
2222 IF h_aux = '(' THEN
2223 IF h_groups = FALSE THEN
2224 h_groups := TRUE;
2225 h_group_counter := 0;
2226 END IF;
2227 END IF;
2228
2229 IF h_groups = FALSE THEN
2230 EXIT;
2231 ELSE
2232 IF h_aux = '(' THEN
2233 h_group_counter := h_group_counter + 1;
2234 ELSIF h_aux = ')' THEN
2235 h_group_counter := h_group_counter - 1;
2236 IF h_group_counter = 0 THEN
2237 h_groups := FALSE;
2238 END IF;
2239 END IF;
2240 h_field := h_field||h_aux;
2241 h_pos := h_pos + 1;
2242 END IF;
2243 ELSE
2244 h_field := h_field||h_aux;
2245 h_pos := h_pos + 1;
2246 END IF;
2247 END LOOP;
2248
2249 IF h_field IS NOT NULL THEN
2250 h_aux := REPLACE(h_FieldE, 'FIELD', h_field);
2251 h_expression := SUBSTR(h_expression, 1, h_pos1)||h_aux||SUBSTR(h_expression, h_pos);
2252 END IF;
2253 h_pos := h_pos1 + LENGTH(h_decodeF);
2254 ELSE
2255 EXIT;
2256 END IF;
2257 h_pos1 := INSTR(h_expression, '/', h_pos);
2258 END LOOP;
2259 END IF;
2260
2261 RETURN h_expression;
2262
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 RETURN NULL;
2266 END Get_Free_Div_Zero_Expression;
2267
2268
2269 /*===========================================================================+
2270 | FUNCTION Get_Indic_Range_Of_Years
2271 +============================================================================*/
2272 FUNCTION Get_Indic_Range_Of_Years(
2273 x_indicator IN NUMBER,
2274 x_periodicity IN NUMBER,
2275 x_num_of_years OUT NOCOPY NUMBER,
2276 x_previous_years OUT NOCOPY NUMBER
2277 ) RETURN BOOLEAN IS
2278
2279 TYPE t_cursor IS REF CURSOR;
2280
2281 /*
2282 c_indic t_cursor; -- x_indicator, x_periodicity
2283 c_indic_sql VARCHAR2(2000) := 'SELECT num_of_years, previous_years'||
2284 ' FROM bsc_kpi_periodicities'||
2285 ' WHERE indicator = :1 AND periodicity_id = :2';
2286 */
2287
2288 BEGIN
2289 /*
2290 OPEN c_indic FOR c_indic_sql USING x_indicator, x_periodicity;
2291 FETCH c_indic INTO x_num_of_years, x_previous_years;
2292 IF c_indic%NOTFOUND THEN
2293 x_num_of_years := 2;
2294 x_previous_years := 1;
2295 ELSE
2296 IF NVL(x_num_of_years, 0) = 0 THEN
2297 x_num_of_years := 2;
2298 END IF;
2299
2300 IF NVL(x_previous_years, 0) = 0 THEN
2301 x_previous_years := 1;
2302 END IF;
2303 END IF;
2304 CLOSE c_indic;
2305 */
2306 BEGIN
2307 SELECT num_of_years, previous_years
2308 INTO x_num_of_years, x_previous_years
2309 FROM bsc_kpi_periodicities
2310 WHERE indicator = x_indicator AND periodicity_id = x_periodicity;
2311 EXCEPTION
2312 WHEN NO_DATA_FOUND THEN
2313 x_num_of_years := 2;
2314 x_previous_years := 1;
2315 END;
2316 IF NVL(x_num_of_years, 0) = 0 THEN
2317 x_num_of_years := 2;
2318 END IF;
2319
2320 IF NVL(x_previous_years, 0) = 0 THEN
2321 x_previous_years := 1;
2322 END IF;
2323
2324 RETURN TRUE;
2325
2326 EXCEPTION
2327 WHEN OTHERS THEN
2328 BSC_MESSAGE.Add(x_message => SQLERRM,
2329 x_source => 'BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years');
2330 RETURN FALSE;
2331
2332 END Get_Indic_Range_Of_Years;
2333
2334
2335 /*===========================================================================+
2336 | FUNCTION Get_Information_Data_Columns
2337 +============================================================================*/
2338 FUNCTION Get_Information_Data_Columns(
2339 x_table IN VARCHAR2,
2340 x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2341 x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2342 x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2343 x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2344 x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2345
2346 -- BSC-BIS-DIMENSIONS Note: From thsi implemetation we can run loader when
2347 -- there are indicators in prototype. This open a case where a measure is
2348 -- configured in BSC_DB_TABLES_COLS but it does not exists in BSC_DB_MEASURES_COLS_VL
2349 -- because the user could delete the measure. I am changing the query to return
2350 -- default values in case the measure does not exists in BSC_DB_MEASURES_COLS_VL
2351
2352 -- SUPPORT_BSC_BIS_MEASURES: Only BSC measures exists in bsc_db_measure_cols_vl.
2353 -- For BIS measures by design we assumed that projection method is 0 (no projection)
2354 -- and measure type is 1 (Total)
2355 CURSOR c_data_columns (p_table_name VARCHAR2, p_column_type VARCHAR2) IS
2356 SELECT c.column_name, c.source_formula,
2357 DECODE(NVL(c.source,'BSC'),'BSC',NVL(m.projection_id, 0),0),
2358 DECODE(NVL(c.source,'BSC'),'BSC',NVL(m.measure_type, 1),1)
2359 FROM bsc_db_tables_cols c, bsc_db_measure_cols_vl m
2360 WHERE c.column_name = m.measure_col (+) AND
2361 c.table_name = p_table_name AND c.column_type = p_column_type;
2362
2363 h_column_type_a VARCHAR2(1);
2364
2365 h_column_name bsc_db_tables_cols.column_name%TYPE;
2366 h_source_formula bsc_db_tables_cols.source_formula%TYPE;
2367 h_projection_id bsc_db_tables_cols.projection_id%TYPE;
2368 h_measure_type bsc_db_measure_cols_vl.measure_type%TYPE;
2369
2370 BEGIN
2371 h_column_type_a := 'A';
2372
2373 --OPEN c_data_columns FOR c_data_columns_sql USING x_table, h_column_type_a;
2374 OPEN c_data_columns(x_table, h_column_type_a);
2375 FETCH c_data_columns INTO h_column_name, h_source_formula, h_projection_id, h_measure_type;
2376 WHILE c_data_columns%FOUND LOOP
2377 x_num_data_columns := x_num_data_columns + 1;
2378 x_data_columns(x_num_data_columns) := h_column_name;
2379 x_data_formulas(x_num_data_columns) := h_source_formula;
2380
2381 IF h_measure_type IS NULL THEN
2382 h_measure_type := 1; -- Total
2383 END IF;
2384
2385 x_data_measure_types(x_num_data_columns) := h_measure_type;
2386
2387 IF h_projection_id IS NULL THEN
2388 h_projection_id := 0;
2389 END IF;
2390
2391 x_data_proj_methods(x_num_data_columns) := h_projection_id;
2392
2393 FETCH c_data_columns INTO h_column_name, h_source_formula, h_projection_id, h_measure_type;
2394 END LOOP;
2395 CLOSE c_data_columns;
2396
2397 RETURN TRUE;
2398
2399 EXCEPTION
2400 WHEN OTHERS THEN
2401 BSC_MESSAGE.Add(x_message => SQLERRM,
2402 x_source => 'BSC_UPDATE_UTIL.Get_Information_Data_Columns');
2403 RETURN FALSE;
2404
2405 END Get_Information_Data_Columns;
2406
2407
2408 /*===========================================================================+
2409 | FUNCTION Get_Information_Key_Columns
2410 +============================================================================*/
2411 FUNCTION Get_Information_Key_Columns(
2412 x_table IN VARCHAR2,
2413 x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2414 x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2415 x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2416 x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2417 x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2418
2419 TYPE t_cursor IS REF CURSOR;
2420
2421 /*
2422 c_key_columns t_cursor; -- x_table, h_column_type_p
2423 c_key_columns_sql VARCHAR2(2000) := 'SELECT t.column_name, d.level_view_name, t.source_column, d1.level_view_name'||
2424 ' FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b d1'||
2425 ' WHERE t.table_name = :1 AND t.column_type = :2 AND'||
2426 ' t.column_name = d.level_pk_col AND'||
2427 ' t.source_column = d1.level_pk_col';
2428 */
2429 CURSOR c_key_columns (p_table_name VARCHAR2, p_column_type VARCHAR2) IS
2430 SELECT t.column_name, d.level_view_name, t.source_column, d1.level_view_name
2431 FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b d1
2432 WHERE t.table_name = p_table_name AND t.column_type = p_column_type AND
2433 t.column_name = d.level_pk_col AND
2434 t.source_column = d1.level_pk_col
2435 ORDER BY d.dim_level_id;
2436
2437 h_column_type_p VARCHAR2(1);
2438
2439 h_key_column bsc_db_tables_cols.column_name%TYPE;
2440 h_key_dim_table bsc_sys_dim_levels_b.level_view_name%TYPE;
2441 h_source_column bsc_db_tables_cols.source_column%TYPE;
2442 h_source_dim_table bsc_sys_dim_levels_b.level_view_name%TYPE;
2443
2444 BEGIN
2445 h_column_type_p := 'P';
2446
2447 --OPEN c_key_columns FOR c_key_columns_sql USING x_table, h_column_type_p;
2448 OPEN c_key_columns(x_table, h_column_type_p);
2449 FETCH c_key_columns INTO h_key_column, h_key_dim_table, h_source_column, h_source_dim_table;
2450
2451 WHILE c_key_columns%FOUND LOOP
2452 x_num_key_columns := x_num_key_columns + 1;
2453
2454 x_key_columns(x_num_key_columns) := h_key_column;
2455 x_key_dim_tables(x_num_key_columns) := h_key_dim_table;
2456 x_source_columns(x_num_key_columns) := h_source_column;
2457 x_source_dim_tables(x_num_key_columns) := h_source_dim_table;
2458
2459 FETCH c_key_columns INTO h_key_column, h_key_dim_table, h_source_column, h_source_dim_table;
2460
2461 END LOOP;
2462 CLOSE c_key_columns;
2463
2464 RETURN TRUE;
2465
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 BSC_MESSAGE.Add(x_message => SQLERRM,
2469 x_source => 'BSC_UPDATE_UTIL.Get_Information_Key_Columns');
2470 RETURN FALSE;
2471
2472 END Get_Information_Key_Columns;
2473
2474
2475 /*===========================================================================+
2476 | FUNCTION Get_Init_Variable_Value
2477 +============================================================================*/
2478 FUNCTION Get_Init_Variable_Value(
2479 x_variable_name IN VARCHAR2,
2480 x_variable_value OUT NOCOPY VARCHAR2
2481 ) RETURN BOOLEAN IS
2482
2483 TYPE t_cursor IS REF CURSOR;
2484
2485 /*
2486 c_init t_cursor; -- x_variable_name
2487 c_init_sql VARCHAR2(2000) := 'SELECT property_value'||
2488 ' FROM bsc_sys_init'||
2489 ' WHERE property_code = :1';
2490 */
2491
2492 h_message VARCHAR2(4000);
2493
2494 BEGIN
2495 /*
2496 OPEN c_init FOR c_init_sql USING x_variable_name;
2497 FETCH c_init INTO x_variable_value;
2498 IF c_init%NOTFOUND THEN
2499 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2500 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2501 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2502 x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2503 x_variable_value := NULL;
2504 END IF;
2505 CLOSE c_init;
2506 */
2507 BEGIN
2508 SELECT property_value
2509 INTO x_variable_value
2510 FROM bsc_sys_init
2511 WHERE property_code = x_variable_name;
2512 EXCEPTION
2513 WHEN NO_DATA_FOUND THEN
2514 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2515 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2516 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2517 x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2518 x_variable_value := NULL;
2519 END;
2520
2521 RETURN TRUE;
2522
2523 EXCEPTION
2524 WHEN OTHERS THEN
2525 BSC_MESSAGE.Add(x_message => SQLERRM,
2526 x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2527 RETURN FALSE;
2528
2529 END Get_Init_Variable_Value;
2530
2531
2532 /*===========================================================================+
2533 | FUNCTION Get_Input_Table_Source
2534 +============================================================================*/
2535 FUNCTION Get_Input_Table_Source(
2536 x_input_table IN VARCHAR2,
2537 x_source_type OUT NOCOPY NUMBER,
2538 x_source_name OUT NOCOPY VARCHAR2
2539 ) RETURN BOOLEAN IS
2540
2541 TYPE t_cursor IS REF CURSOR;
2542 h_cursor t_cursor;
2543 h_sql VARCHAR2(32000);
2544
2545 BEGIN
2546 /* h_sql := 'SELECT source_data_type, source_file_name'||
2547 ' FROM bsc_db_tables'||
2548 ' WHERE table_name = :1';
2549 OPEN h_cursor FOR h_sql USING x_input_table;
2550 FETCH h_cursor INTO x_source_type, x_source_name;
2551 CLOSE h_cursor; */
2552 SELECT source_data_type, TRIM(source_file_name)
2553 INTO x_source_type, x_source_name
2554 FROM bsc_db_tables
2555 WHERE table_name = x_input_table ;
2556
2557 RETURN TRUE;
2558
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 BSC_MESSAGE.Add(x_message => SQLERRM,
2562 x_source => 'BSC_UPDATE_UTIL.Get_Input_Table_Source');
2563 RETURN FALSE;
2564 END Get_Input_Table_Source;
2565
2566
2567 /*===========================================================================+
2568 | FUNCTION Get_Installed_Languages
2569 +============================================================================*/
2570 FUNCTION Get_Installed_Languages(
2571 x_languages IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2572 ) RETURN NUMBER IS
2573
2574 TYPE t_cursor IS REF CURSOR;
2575
2576 /*
2577 c_languages t_cursor; -- h_inst_lang, h_base_lang
2578 c_languages_sql VARCHAR2(2000) := 'SELECT DISTINCT LANGUAGE_CODE'||
2579 ' FROM FND_LANGUAGES'||
2580 ' WHERE INSTALLED_FLAG IN (:1, :2)';
2581 */
2582 CURSOR c_languages (p_param1 VARCHAR2, p_param2 VARCHAR2) IS
2583 SELECT DISTINCT LANGUAGE_CODE
2584 FROM FND_LANGUAGES
2585 WHERE INSTALLED_FLAG IN (p_param1, p_param2);
2586
2587 h_inst_lang VARCHAR2(1);
2588 h_base_lang VARCHAR2(1);
2589
2590 h_num_languages NUMBER;
2591 h_language VARCHAR2(10);
2592
2593 BEGIN
2594 h_inst_lang := 'I';
2595 h_base_lang := 'B';
2596 h_num_languages := 0;
2597
2598 --OPEN c_languages FOR c_languages_sql USING h_inst_lang, h_base_lang;
2599 OPEN c_languages(h_inst_lang, h_base_lang);
2600 FETCH c_languages INTO h_language;
2601 WHILE c_languages%FOUND LOOP
2602 h_num_languages := h_num_languages + 1;
2603 x_languages(h_num_languages) := h_language;
2604
2605 FETCH c_languages INTO h_language;
2606 END LOOP;
2607 CLOSE c_languages;
2608
2609 RETURN h_num_languages;
2610
2611 EXCEPTION
2612 WHEN OTHERS THEN
2613 BSC_MESSAGE.Add(x_message => SQLERRM,
2614 x_source => 'BSC_UPDATE_UTIL.Get_Installed_Languages');
2615 RETURN -1;
2616
2617 END Get_Installed_Languages;
2618
2619
2620 /*===========================================================================+
2621 | FUNCTION Get_Lookup_Value
2622 +============================================================================*/
2623 FUNCTION Get_Lookup_Value(
2624 x_lookup_type IN VARCHAR2,
2625 x_lookup_code IN VARCHAR2
2626 ) RETURN VARCHAR2 IS
2627
2628 TYPE t_cursor IS REF CURSOR;
2629
2630 /*
2631 c_lookup_value t_cursor; -- x_lookup_type, x_lookup_code
2632 c_lookup_value_sql VARCHAR2(2000) := 'SELECT meaning'||
2633 ' FROM bsc_lookups'||
2634 ' WHERE lookup_type = :1 AND lookup_code = :2';
2635 */
2636
2637 h_lookup_value VARCHAR2(4000);
2638
2639 h_message VARCHAR2(4000);
2640
2641 BEGIN
2642 /*
2643 OPEN c_lookup_value FOR c_lookup_value_sql USING x_lookup_type, x_lookup_code;
2644 FETCH c_lookup_value INTO h_lookup_value;
2645 IF c_lookup_value%NOTFOUND THEN
2646 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2647 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2648 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2649 BSC_MESSAGE.Add(x_message => h_message,
2650 x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2651 h_lookup_value := NULL;
2652 END IF;
2653 CLOSE c_lookup_value;
2654 */
2655 BEGIN
2656 SELECT meaning
2657 INTO h_lookup_value
2658 FROM bsc_lookups
2659 WHERE lookup_type = x_lookup_type AND lookup_code = x_lookup_code;
2660 EXCEPTION
2661 WHEN NO_DATA_FOUND THEN
2662 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2663 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2664 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2665 BSC_MESSAGE.Add(x_message => h_message,
2666 x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2667 h_lookup_value := NULL;
2668 END;
2669
2670 RETURN h_lookup_value;
2671
2672 END Get_Lookup_Value;
2673
2674
2675 /*===========================================================================+
2676 | FUNCTION Get_Message
2677 +============================================================================*/
2678 FUNCTION Get_Message(
2679 x_message_name IN VARCHAR2
2680 ) RETURN VARCHAR2 IS
2681
2682 TYPE t_cursor IS REF CURSOR;
2683
2684 /*
2685 c_message t_cursor; -- x_message_name
2686 c_message_sql VARCHAR2(2000) := 'SELECT message_text'||
2687 ' FROM bsc_messages'||
2688 ' WHERE message_name = :1';
2689 */
2690
2691 h_message VARCHAR2(4000);
2692
2693 BEGIN
2694 /*
2695 OPEN c_message FOR c_message_sql USING x_message_name;
2696 FETCH c_message INTO h_message;
2697 IF c_message%NOTFOUND THEN
2698 h_message := NULL;
2699 END IF;
2700 CLOSE c_message;
2701 */
2702 BEGIN
2703 SELECT message_text
2704 INTO h_message
2705 FROM bsc_messages
2706 WHERE message_name = x_message_name;
2707 EXCEPTION
2708 WHEN NO_DATA_FOUND THEN
2709 h_message := NULL;
2710 END;
2711
2712 RETURN h_message;
2713
2714 END Get_Message;
2715
2716
2717 /*===========================================================================+
2718 | FUNCTION Get_Num_Periods_Periodicity
2719 +============================================================================*/
2720 FUNCTION Get_Num_Periods_Periodicity(
2721 x_periodicity IN NUMBER,
2722 x_current_fy IN NUMBER
2723 ) RETURN NUMBER IS
2724
2725 TYPE t_cursor IS REF CURSOR;
2726 h_cursor t_cursor;
2727
2728 h_sql VARCHAR2(2000);
2729
2730 h_calendar_col_name VARCHAR2(30);
2731 h_num_periods NUMBER;
2732
2733 h_edw_flag NUMBER;
2734 h_calendar_id NUMBER;
2735
2736 BEGIN
2737 h_edw_flag := 0;
2738
2739 h_edw_flag := Get_Periodicity_EDW_Flag(x_periodicity);
2740 h_calendar_id := Get_Calendar_Id(x_periodicity);
2741
2742 IF h_edw_flag = 0 THEN
2743 -- BSC periodicity
2744 h_calendar_col_name := Get_Calendar_Table_Col_Name(x_periodicity);
2745
2746 h_sql := 'SELECT MAX('||h_calendar_col_name||')'||
2747 ' FROM bsc_db_calendar'||
2748 ' WHERE year = :1 AND calendar_id = :2';
2749
2750 OPEN h_cursor FOR h_sql USING x_current_fy, h_calendar_id;
2751 FETCH h_cursor INTO h_num_periods;
2752 IF h_cursor%NOTFOUND THEN
2753 h_num_periods := NULL;
2754 END IF;
2755 CLOSE h_cursor;
2756
2757 ELSE
2758 -- EDW periodicity
2759 h_num_periods := BSC_INTEGRATION_APIS.Get_Number_Of_Periods(x_current_fy, x_periodicity, h_calendar_id);
2760 IF BSC_APPS.CheckError('BSC_INTEGRATION_APIS.Get_Number_Of_Periods') THEN
2761 -- Error
2762 RETURN NULL;
2763 END IF;
2764
2765 END IF;
2766
2767 RETURN h_num_periods;
2768
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 BSC_MESSAGE.Add(x_message => SQLERRM,
2772 x_source => 'BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity');
2773 RETURN NULL;
2774
2775 END Get_Num_Periods_Periodicity;
2776
2777
2778 /*===========================================================================+
2779 | FUNCTION Get_Period_Cols_Names
2780 +============================================================================*/
2781 FUNCTION Get_Period_Cols_Names(
2782 x_periodicity_cod IN NUMBER,
2783 x_period_col_name OUT NOCOPY VARCHAR2,
2784 x_subperiod_col_name OUT NOCOPY VARCHAR2
2785 ) RETURN BOOLEAN IS
2786
2787 TYPE t_cursor IS REF CURSOR;
2788
2789 /*
2790 c_period_names t_cursor; -- x_periodicity_cod
2791 c_period_names_sql VARCHAR2(2000) := 'SELECT NVL(period_col_name, ''PERIOD''), subperiod_col_name'||
2792 ' FROM bsc_sys_periodicities'||
2793 ' WHERE periodicity_id = :1';
2794 */
2795 BEGIN
2796 IF g_array_periodicities.exists(x_periodicity_cod) THEN
2797 IF g_array_periodicities(x_periodicity_cod).period_col_name IS NOT NULL THEN
2798 x_period_col_name := g_array_periodicities(x_periodicity_cod).period_col_name;
2799 x_subperiod_col_name := g_array_periodicities(x_periodicity_cod).sub_period_col_name;
2800 RETURN TRUE;
2801 END IF;
2802 END IF;
2803
2804 /*
2805 OPEN c_period_names FOR c_period_names_sql USING x_periodicity_cod;
2806 FETCH c_period_names INTO x_period_col_name, x_subperiod_col_name;
2807 IF c_period_names%NOTFOUND THEN
2808 x_period_col_name := NULL;
2809 x_subperiod_col_name := NULL;
2810 END IF;
2811 CLOSE c_period_names;
2812 */
2813 BEGIN
2814 SELECT NVL(period_col_name, 'PERIOD'), subperiod_col_name
2815 INTO x_period_col_name, x_subperiod_col_name
2816 FROM bsc_sys_periodicities
2817 WHERE periodicity_id = x_periodicity_cod;
2818 EXCEPTION
2819 WHEN NO_DATA_FOUND THEN
2820 x_period_col_name := NULL;
2821 x_subperiod_col_name := NULL;
2822 END;
2823
2824 g_array_periodicities(x_periodicity_cod).period_col_name := x_period_col_name;
2825 g_array_periodicities(x_periodicity_cod).sub_period_col_name := x_subperiod_col_name;
2826
2827 RETURN TRUE;
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 BSC_MESSAGE.Add(x_message => SQLERRM,
2832 x_source => 'BSC_UPDATE_UTIL.Get_Period_Cols_Names');
2833 RETURN FALSE;
2834
2835 END Get_Period_Cols_Names;
2836
2837
2838 /*===========================================================================+
2839 | FUNCTION Get_Period_Other_Periodicity
2840 +============================================================================*/
2841 FUNCTION Get_Period_Other_Periodicity(
2842 p_periodicity_id IN NUMBER,
2843 p_calendar_id IN NUMBER,
2844 p_yearly_flag IN NUMBER,
2845 p_current_fy IN NUMBER,
2846 p_source_periodicity_id IN NUMBER,
2847 p_source_period IN NUMBER
2848 ) RETURN NUMBER IS
2849
2850 TYPE t_cursor IS REF CURSOR;
2851 h_cursor t_cursor;
2852
2853 h_period NUMBER;
2854 h_source_col_name VARCHAR2(30);
2855 h_col_name VARCHAR2(30);
2856
2857 h_sql VARCHAR2(32000);
2858
2859 BEGIN
2860 h_period := 0;
2861
2862 IF p_yearly_flag = 1 THEN
2863 h_period := p_current_fy;
2864 ELSE
2865 h_source_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_source_periodicity_id);
2866 h_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_periodicity_id);
2867
2868 h_sql := 'SELECT DISTINCT '||h_col_name||
2869 ' FROM bsc_db_calendar'||
2870 ' WHERE calendar_id = :1 AND year = :2'||
2871 ' AND '||h_source_col_name||' = :3';
2872 OPEN h_cursor FOR h_sql USING p_calendar_id, p_current_fy, p_source_period;
2873 FETCH h_cursor INTO h_period;
2874 CLOSE h_cursor;
2875 END IF;
2876
2877 RETURN h_period;
2878
2879 END Get_Period_Other_Periodicity;
2880
2881
2882 /*===========================================================================+
2883 | FUNCTION Get_Periodicity_EDW_Flag
2884 +============================================================================*/
2885 FUNCTION Get_Periodicity_EDW_Flag(
2886 x_periodicity_id IN NUMBER
2887 ) RETURN NUMBER IS
2888
2889 h_edw_flag NUMBER;
2890
2891 TYPE t_cursor IS REF CURSOR;
2892 h_cursor t_cursor;
2893 h_sql VARCHAR2(2000);
2894
2895 BEGIN
2896
2897 IF g_array_periodicities.exists(x_periodicity_id) THEN
2898 IF g_array_periodicities(x_periodicity_id).edw_flag IS NOT NULL THEN
2899 RETURN g_array_periodicities(x_periodicity_id).edw_flag;
2900 END IF;
2901 END IF;
2902
2903 /*
2904 h_sql := 'SELECT edw_flag'||
2905 ' FROM bsc_sys_periodicities'||
2906 ' WHERE periodicity_id = :1';
2907 OPEN h_cursor FOR h_sql USING x_periodicity_id;
2908 FETCH h_cursor INTO h_edw_flag;
2909 CLOSE h_cursor;
2910 */
2911 SELECT edw_flag
2912 INTO h_edw_flag
2913 FROM bsc_sys_periodicities
2914 WHERE periodicity_id = x_periodicity_id;
2915
2916 g_array_periodicities(x_periodicity_id).edw_flag := h_edw_flag;
2917
2918 RETURN h_edw_flag;
2919
2920 END Get_Periodicity_EDW_Flag;
2921
2922
2923 /*===========================================================================+
2924 | FUNCTION Get_Periodicity_Type
2925 +============================================================================*/
2926 FUNCTION Get_Periodicity_Type(
2927 x_periodicity_id IN NUMBER
2928 ) RETURN NUMBER IS
2929
2930 h_periodicity_type NUMBER;
2931
2932 TYPE t_cursor IS REF CURSOR;
2933 h_cursor t_cursor;
2934 h_sql VARCHAR2(2000);
2935
2936 BEGIN
2937
2938 IF g_array_periodicities.exists(x_periodicity_id) THEN
2939 IF g_array_periodicities(x_periodicity_id).periodicity_type IS NOT NULL THEN
2940 RETURN g_array_periodicities(x_periodicity_id).periodicity_type;
2941 END IF;
2942 END IF;
2943
2944 /*
2945 h_sql := 'SELECT periodicity_type'||
2946 ' FROM bsc_sys_periodicities'||
2947 ' WHERE periodicity_id = :1';
2948 OPEN h_cursor FOR h_sql USING x_periodicity_id;
2949 FETCH h_cursor INTO h_periodicity_type;
2950 CLOSE h_cursor;
2951 */
2952 SELECT periodicity_type
2953 INTO h_periodicity_type
2954 FROM bsc_sys_periodicities
2955 WHERE periodicity_id = x_periodicity_id;
2956
2957 g_array_periodicities(x_periodicity_id).periodicity_type := h_periodicity_type;
2958
2959 RETURN h_periodicity_type;
2960
2961 END Get_Periodicity_Type;
2962
2963
2964 /*===========================================================================+
2965 | FUNCTION Get_Periodicity_Yearly_Flag
2966 +============================================================================*/
2967 FUNCTION Get_Periodicity_Yearly_Flag(
2968 x_periodicity_id IN NUMBER
2969 ) RETURN NUMBER IS
2970
2971 h_yearly_flag NUMBER;
2972
2973 TYPE t_cursor IS REF CURSOR;
2974 h_cursor t_cursor;
2975 h_sql VARCHAR2(2000);
2976
2977 BEGIN
2978
2979 IF g_array_periodicities.exists(x_periodicity_id) THEN
2980 IF g_array_periodicities(x_periodicity_id).yearly_flag IS NOT NULL THEN
2981 RETURN g_array_periodicities(x_periodicity_id).yearly_flag;
2982 END IF;
2983 END IF;
2984
2985 /*
2986 h_sql := 'SELECT yearly_flag'||
2987 ' FROM bsc_sys_periodicities'||
2988 ' WHERE periodicity_id = :1';
2989 OPEN h_cursor FOR h_sql USING x_periodicity_id;
2990 FETCH h_cursor INTO h_yearly_flag;
2991 CLOSE h_cursor;
2992 */
2993 SELECT yearly_flag
2994 INTO h_yearly_flag
2995 FROM bsc_sys_periodicities
2996 WHERE periodicity_id = x_periodicity_id;
2997
2998 g_array_periodicities(x_periodicity_id).yearly_flag := h_yearly_flag;
2999
3000 RETURN h_yearly_flag;
3001
3002 END Get_Periodicity_Yearly_Flag;
3003
3004
3005 /*===========================================================================+
3006 | FUNCTION Get_Source_Periodicities
3007 +============================================================================*/
3008 FUNCTION Get_Source_Periodicities(
3009 x_periodicity_id IN NUMBER,
3010 x_source_periodicities IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
3011 ) RETURN NUMBER IS
3012
3013 h_num_source_periodicities NUMBER;
3014 h_i NUMBER;
3015
3016 BEGIN
3017 h_num_source_periodicities := 0;
3018
3019 FOR h_i IN 1..g_array_periodicity_rels.COUNT LOOP
3020 IF g_array_periodicity_rels(h_i).periodicity_id = x_periodicity_id THEN
3021 h_num_source_periodicities := h_num_source_periodicities + 1;
3022 x_source_periodicities(h_num_source_periodicities) := g_array_periodicity_rels(h_i).source_periodicity_id;
3023 END IF;
3024 END LOOP;
3025
3026 RETURN h_num_source_periodicities;
3027
3028 END Get_Source_Periodicities;
3029
3030
3031 /*===========================================================================+
3032 | FUNCTION Get_Table_EDW_Flag
3033 +============================================================================*/
3034 FUNCTION Get_Table_EDW_Flag(
3035 x_table_name IN VARCHAR2
3036 ) RETURN NUMBER IS
3037
3038 h_edw_flag NUMBER;
3039
3040 TYPE t_cursor IS REF CURSOR;
3041 h_cursor t_cursor;
3042 h_sql VARCHAR2(2000);
3043
3044 BEGIN
3045 h_edw_flag := 0;
3046
3047 /* h_sql := 'SELECT NVL(edw_flag, 0)'||
3048 ' FROM bsc_db_tables'||
3049 ' WHERE table_name = :1';
3050 OPEN h_cursor FOR h_sql USING x_table_name;
3051 FETCH h_cursor INTO h_edw_flag;
3052 CLOSE h_cursor; */
3053 begin
3054 SELECT NVL(edw_flag, 0)
3055 INTO h_edw_flag
3056 FROM bsc_db_tables
3057 WHERE table_name = x_table_name;
3058 exception
3059 when no_data_found then h_edw_flag := null;
3060 end;
3061
3062 RETURN h_edw_flag;
3063
3064 END Get_Table_EDW_Flag;
3065
3066
3067 /*===========================================================================+
3068 | FUNCTION Get_Table_Generation_Type
3069 +============================================================================*/
3070 FUNCTION Get_Table_Generation_Type(
3071 x_table_name IN VARCHAR2
3072 ) RETURN NUMBER IS
3073
3074 h_table_generation_type bsc_db_tables.generation_type%TYPE;
3075
3076 BEGIN
3077 h_table_generation_type := NULL;
3078
3079 SELECT generation_type
3080 INTO h_table_generation_type
3081 FROM bsc_db_tables
3082 WHERE table_name = x_table_name;
3083
3084 RETURN h_table_generation_type;
3085
3086 EXCEPTION
3087 WHEN OTHERS THEN
3088 BSC_MESSAGE.Add(x_message => SQLERRM,
3089 x_source => 'BSC_UPDATE_UTIL.Get_Table_Generation_Type');
3090 RETURN NULL;
3091
3092 END Get_Table_Generation_Type;
3093
3094
3095 /*===========================================================================+
3096 | FUNCTION Get_Table_Type
3097 +============================================================================*/
3098 FUNCTION Get_Table_Type(
3099 x_table_name IN VARCHAR2
3100 ) RETURN NUMBER IS
3101
3102 h_table_type bsc_db_tables.table_type%TYPE;
3103
3104 BEGIN
3105 h_table_type := NULL;
3106
3107 SELECT table_type
3108 INTO h_table_type
3109 FROM bsc_db_tables
3110 WHERE table_name = x_table_name;
3111
3112 RETURN h_table_type;
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 BSC_MESSAGE.Add(x_message => SQLERRM,
3117 x_source => 'BSC_UPDATE_UTIL.Get_Table_Type');
3118 RETURN NULL;
3119
3120 END Get_Table_Type;
3121
3122
3123 /*===========================================================================+
3124 | FUNCTION Get_Table_Periodicity
3125 +============================================================================*/
3126 FUNCTION Get_Table_Periodicity(
3127 x_table_name IN VARCHAR2
3128 ) RETURN NUMBER IS
3129
3130 TYPE t_cursor IS REF CURSOR;
3131
3132 /*
3133 c_table_periodicity t_cursor; -- x_table_name
3134 c_table_periodicity_sql VARCHAR2(2000) := 'SELECT periodicity_id'||
3135 ' FROM bsc_db_tables'||
3136 ' WHERE table_name = :1';
3137 */
3138
3139 h_table_periodicity bsc_db_tables.periodicity_id%TYPE;
3140
3141 BEGIN
3142 h_table_periodicity := NULL;
3143
3144 /*
3145 OPEN c_table_periodicity FOR c_table_periodicity_sql USING x_table_name;
3146 FETCH c_table_periodicity INTO h_table_periodicity;
3147 IF c_table_periodicity%NOTFOUND THEN
3148 h_table_periodicity := NULL;
3149 END IF;
3150 CLOSE c_table_periodicity;
3151 */
3152 BEGIN
3153 SELECT periodicity_id
3154 INTO h_table_periodicity
3155 FROM bsc_db_tables
3156 WHERE table_name = x_table_name;
3157 EXCEPTION
3158 WHEN NO_DATA_FOUND THEN
3159 h_table_periodicity := NULL;
3160 END;
3161
3162 RETURN h_table_periodicity;
3163
3164 EXCEPTION
3165 WHEN OTHERS THEN
3166 BSC_MESSAGE.Add(x_message => SQLERRM,
3167 x_source => 'BSC_UPDATE_UTIL.Get_Table_Periodicity');
3168 RETURN NULL;
3169
3170 END Get_Table_Periodicity;
3171
3172
3173 /*===========================================================================+
3174 | FUNCTION Get_Table_Range_Of_Years
3175 +============================================================================*/
3176 FUNCTION Get_Table_Range_Of_Years(
3177 x_table_name IN VARCHAR2,
3178 x_num_of_years OUT NOCOPY NUMBER,
3179 x_previous_years OUT NOCOPY NUMBER
3180 ) RETURN BOOLEAN IS
3181
3182 BEGIN
3183 BEGIN
3184 SELECT num_of_years, previous_years
3185 INTO x_num_of_years, x_previous_years
3186 FROM bsc_db_tables
3187 WHERE table_name = x_table_name;
3188 EXCEPTION
3189 WHEN NO_DATA_FOUND THEN
3190 x_num_of_years := 2;
3191 x_previous_years := 1;
3192 END;
3193
3194 IF x_num_of_years IS NULL THEN
3195 x_num_of_years := 2;
3196 END IF;
3197
3198 IF x_previous_years IS NULL THEN
3199 x_previous_years := 1;
3200 END IF;
3201
3202 RETURN TRUE;
3203
3204 EXCEPTION
3205 WHEN OTHERS THEN
3206 BSC_MESSAGE.Add(x_message => SQLERRM,
3207 x_source => 'BSC_UPDATE_UTIL.Get_Table_Range_Of_Years');
3208 RETURN FALSE;
3209
3210 END Get_Table_Range_Of_Years;
3211
3212
3213 /*===========================================================================+
3214 | FUNCTION Get_Kpis_Using_Table
3215 +============================================================================*/
3216 FUNCTION Get_Kpis_Using_Table(
3217 x_table_name IN VARCHAR2,
3218 x_kpis IN OUT NOCOPY t_array_kpis
3219 ) RETURN NUMBER IS
3220
3221 CURSOR c_kpis IS
3222 SELECT DISTINCT k.indicator, k.prototype_flag
3223 FROM bsc_kpi_data_tables t, bsc_db_tables_rels r, bsc_kpis_b k
3224 WHERE t.table_name = r.table_name AND
3225 t.indicator = k.indicator AND
3226 (t.table_name = x_table_name OR
3227 (r.source_table_name = x_table_name AND r.relation_type = 1));
3228
3229 h_num_kpis NUMBER;
3230 h_kpi NUMBER;
3231 h_prototype_flag NUMBER;
3232
3233 BEGIN
3234 h_num_kpis := 0;
3235
3236 OPEN c_kpis;
3237 LOOP
3238 FETCH c_kpis INTO h_kpi, h_prototype_flag;
3239 EXIT WHEN c_kpis%NOTFOUND;
3240 h_num_kpis := h_num_kpis + 1;
3241 x_kpis(h_num_kpis).indicator := h_kpi;
3242 x_kpis(h_num_kpis).prototype_flag := h_prototype_flag;
3243 END LOOP;
3244 --Fix bug#3899842: Close cursor;
3245 CLOSE c_kpis;
3246 RETURN h_num_kpis;
3247
3248 END Get_Kpis_Using_Table;
3249
3250
3251 /*===========================================================================+
3252 | FUNCTION Is_Kpi_In_Production
3253 +============================================================================*/
3254 FUNCTION Is_Kpi_In_Production(
3255 x_kpi IN NUMBER
3256 ) RETURN BOOLEAN IS
3257
3258 h_num_rows NUMBER;
3259
3260 BEGIN
3261 SELECT COUNT(indicator)
3262 INTO h_num_rows
3263 FROM bsc_kpis_b
3264 WHERE indicator = x_kpi
3265 AND prototype_flag IN (0,6,7);
3266
3267 IF h_num_rows > 0 THEN
3268 RETURN TRUE;
3269 ELSE
3270 RETURN FALSE;
3271 END IF;
3272
3273 END Is_Kpi_In_Production;
3274
3275
3276 /*FUNCTION Is_Kpi_Measure_In_Production (
3277 p_objective_id IN NUMBER
3278 , p_kpi_measure_id IN NUMBER
3279 )
3280 RETURN BOOLEAN IS
3281
3282 h_num_rows NUMBER;
3283
3284 BEGIN
3285 SELECT COUNT(indicator)
3286 INTO h_num_rows
3287 FROM bsc_kpi_analysis_measures_b
3288 WHERE kpi_measure_id = p_kpi_measure_id
3289 AND prototype_flag = 7;
3290
3291 IF h_num_rows > 0 THEN
3292 RETURN TRUE;
3293 ELSE
3294 RETURN FALSE;
3295 END IF;
3296
3297 EXCEPTION
3298 WHEN OTHERS THEN
3299 RETURN FALSE;
3300 END Is_Kpi_Measure_In_Production;*/
3301
3302
3303 /*===========================================================================+
3304 | FUNCTION Get_Table_Target_Flag
3305 +============================================================================*/
3306 FUNCTION Get_Table_Target_Flag(
3307 x_table_name IN VARCHAR2
3308 ) RETURN NUMBER IS
3309
3310 h_target_flag NUMBER;
3311
3312 TYPE t_cursor IS REF CURSOR;
3313 h_cursor t_cursor;
3314 h_sql VARCHAR2(32000);
3315
3316 BEGIN
3317 h_target_flag := 0;
3318
3319 /*
3320 h_sql := 'SELECT NVL(target_flag, 0)'||
3321 ' FROM bsc_db_tables'||
3322 ' WHERE table_name = :1';
3323 OPEN h_cursor FOR h_sql USING x_table_name;
3324 FETCH h_cursor INTO h_target_flag;
3325 CLOSE h_cursor;
3326 */
3327 SELECT NVL(target_flag, 0)
3328 INTO h_target_flag
3329 FROM bsc_db_tables
3330 WHERE table_name = x_table_name;
3331
3332 RETURN h_target_flag;
3333
3334 END Get_Table_Target_Flag;
3335
3336
3337 /*===========================================================================+
3338 | FUNCTION Init_Calendar_Tables
3339 +============================================================================*/
3340 FUNCTION Init_Calendar_Tables (
3341 x_calendar_id IN NUMBER,
3342 x_action IN NUMBER
3343 ) RETURN BOOLEAN IS
3344
3345 e_unexpected_error EXCEPTION;
3346
3347 h_current_fy NUMBER;
3348 h_start_year NUMBER;
3349 h_start_month NUMBER;
3350 h_start_day NUMBER;
3351 h_min_year NUMBER;
3352
3353 TYPE t_cursor IS REF CURSOR;
3354
3355 --Fix bug#4063282, add source periodicity to this cursor
3356 CURSOR c_custom_pers (p_calendar_id NUMBER) IS
3357 SELECT periodicity_id, custom_code, DECODE(INSTR(source, ','), 0, source, SUBSTR(source, 1, INSTR(source, ',') - 1))
3358 FROM bsc_sys_periodicities
3359 WHERE calendar_id = p_calendar_id;
3360
3361 h_periodicity_id NUMBER;
3362 h_custom_code NUMBER;
3363 h_source_periodicity NUMBER;
3364
3365 h_calculated_pers BSC_UPDATE_UTIL.t_array_of_number;
3366 h_num_calculated_pers NUMBER;
3367
3368 TYPE t_custom_per IS RECORD (
3369 periodicity_id NUMBER,
3370 custom_code NUMBER,
3371 source_periodicity NUMBER
3372 );
3373
3374 TYPE t_custom_pers IS TABLE OF t_custom_per
3375 INDEX BY BINARY_INTEGER;
3376
3377 h_custom_pers t_custom_pers;
3378 h_num_custom_pers NUMBER;
3379 h_num_periodicities NUMBER;
3380
3381 h_i NUMBER;
3382
3383 h_count NUMBER;
3384
3385 BEGIN
3386 -- x_action = 1 || x_action = NULL
3387 -- Drop Indexes from calendar tables: BSC_DB_CALENDAR, BSC_DB_WEEK_MAPS,
3388 -- BSC_SYS_PERIODS_TL to improve performance
3389 -- Note: Indexes on BSC_DB_CALENDAR and BSC_DB_WEEK_MAPS tables were removed.
3390
3391 --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3392 -- we cannot load different calendars at the same time
3393 --IF NVL(x_action, 1) = 1 THEN
3394 -- IF NOT Drop_Index('BSC_SYS_PERIODS_TL_U1') THEN
3395 -- RAISE e_unexpected_error;
3396 -- END IF;
3397 --END IF;
3398
3399
3400 -- Fix bug#4536286: validate source and db_column_name in bsc_sys_periodicities
3401 -- cannot be null for custom periodicities
3402 select count(periodicity_id)
3403 into h_count
3404 from bsc_sys_periodicities
3405 where calendar_id = x_calendar_id and nvl(custom_code, -1) <> 0 and
3406 (source is null or db_column_name is null);
3407 IF h_count > 0 THEN
3408 RAISE e_unexpected_error;
3409 END IF;
3410
3411 -- x_action = 2 ||x_action = NULL
3412 -- Populate calendar tables
3413 IF NVL(x_action, 2) = 2 THEN
3414 -- Get the current fiscal year
3415 h_current_fy := Get_Calendar_Fiscal_Year(x_calendar_id);
3416
3417 -- Get the start date of fiscal year
3418 IF NOT Get_Calendar_Start_Date(x_calendar_id,
3419 h_current_fy,
3420 h_start_year,
3421 h_start_month,
3422 h_start_day) THEN
3423 RAISE e_unexpected_error;
3424 END IF;
3425
3426 -- Populate BSC_DB_CALENDAR
3427 -- It insert row for predefined periodicities
3428 IF NOT Populate_Bsc_Db_Calendar(x_calendar_id,
3429 h_current_fy,
3430 h_start_year,
3431 h_start_month,
3432 h_start_day) THEN
3433 RAISE e_unexpected_error;
3434 END IF;
3435
3436 -- Verify custom periodicities
3437 -- It check that there are records for all fiscal years
3438 -- in BSC_SYS_PERIODS. In case a fiscal year dont have
3439 -- records, it generate them automatically taking the
3440 -- parameters of the current fiscal year.
3441 -- If there are no records in BSC_SYS_PERIODS for current
3442 -- fiscal year (i.e in year change process)
3443 -- then it look for the latest year which have records in
3444 -- BSC_SYS_PERIODS.
3445 -- After that it updates the corresponding column
3446 -- in BSC_DB_CALENDAR table
3447
3448 -- Fix bug#4063282: Need to process custom periodicities in order.
3449 -- First the source, then the target periodicity
3450
3451 h_num_calculated_pers := 0;
3452 h_num_custom_pers := 0;
3453 h_num_periodicities := 0;
3454
3455 OPEN c_custom_pers(x_calendar_id);
3456 FETCH c_custom_pers INTO h_periodicity_id, h_custom_code, h_source_periodicity;
3457 WHILE c_custom_pers%FOUND LOOP
3458 IF h_custom_code = 0 THEN
3459 -- This is a pre-defined periodicity
3460 h_num_calculated_pers := h_num_calculated_pers + 1;
3461 h_calculated_pers(h_num_calculated_pers) := h_periodicity_id;
3462 ELSE
3463 -- This is a custom periodicity
3464 h_num_custom_pers := h_num_custom_pers + 1;
3465 h_custom_pers(h_num_custom_pers).periodicity_id := h_periodicity_id;
3466 h_custom_pers(h_num_custom_pers).custom_code := h_custom_code;
3467 h_custom_pers(h_num_custom_pers).source_periodicity := h_source_periodicity;
3468 END IF;
3469 h_num_periodicities := h_num_periodicities + 1;
3470 FETCH c_custom_pers INTO h_periodicity_id, h_custom_code, h_source_periodicity;
3471 END LOOP;
3472 CLOSE c_custom_pers;
3473
3474 WHILE h_num_calculated_pers <> h_num_periodicities LOOP
3475 FOR h_i IN 1..h_num_custom_pers LOOP
3476 IF NOT Item_Belong_To_Array_Number(h_custom_pers(h_i).periodicity_id,
3477 h_calculated_pers,
3478 h_num_calculated_pers) THEN
3479 -- This custom periodicity has not been processed yet
3480 IF Item_Belong_To_Array_Number(h_custom_pers(h_i).source_periodicity,
3481 h_calculated_pers,
3482 h_num_calculated_pers) THEN
3483 -- Source periodicity already was processed, so we can process this periodicity now
3484 IF NOT Verify_Custom_Periodicity(x_calendar_id,
3485 h_custom_pers(h_i).periodicity_id,
3486 h_custom_pers(h_i).custom_code) THEN
3487 RAISE e_unexpected_error;
3488 END IF;
3489
3490 h_num_calculated_pers := h_num_calculated_pers + 1;
3491 h_calculated_pers(h_num_calculated_pers) := h_custom_pers(h_i).periodicity_id;
3492 END IF;
3493 END IF;
3494 END LOOP;
3495 END LOOP;
3496
3497 -- Populate BSC_DB_WEEK_MAPS
3498 IF NOT Populate_Bsc_Db_Week_Maps(x_calendar_id) THEN
3499 RAISE e_unexpected_error;
3500 END IF;
3501
3502 -- Populate BSC_SYS_PERIODS_TL
3503 IF NOT Populate_Bsc_Sys_Periods_Tl(x_calendar_id) THEN
3504 RAISE e_unexpected_error;
3505 END IF;
3506 END IF;
3507
3508 -- x_action = 3 || x_action = NULL
3509 -- Enable indexes
3510 --LOCKING: We are not going to drop indexes anymore. If we remove indexes
3511 -- we cannot load different calendars at the same time
3512 --IF NVL(x_action, 3) = 3 THEN
3513 -- IF NOT Create_Unique_Index('BSC_SYS_PERIODS_TL',
3514 -- 'BSC_SYS_PERIODS_TL_U1',
3515 -- 'YEAR, PERIODICITY_ID, PERIOD_ID, MONTH, LANGUAGE',
3516 -- BSC_APPS.other_index_tbs_type) THEN
3517 -- RAISE e_unexpected_error;
3518 -- END IF;
3519 -- COMMIT;
3520 --END IF;
3521
3522 RETURN TRUE;
3523
3524 EXCEPTION
3525 WHEN e_unexpected_error THEN
3526 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3527 x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3528 RETURN FALSE;
3529
3530 WHEN OTHERS THEN
3531 BSC_MESSAGE.Add(x_message => SQLERRM,
3532 x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3533 RETURN FALSE;
3534
3535 END Init_Calendar_Tables;
3536
3537
3538 /*===========================================================================+
3539 | FUNCTION Init_Calendar_Tables
3540 +============================================================================*/
3541 FUNCTION Init_Calendar_Tables (
3542 x_calendar_id IN NUMBER
3543 ) RETURN BOOLEAN IS
3544
3545 e_unexpected_error EXCEPTION;
3546
3547 BEGIN
3548 IF NOT Init_Calendar_Tables(x_calendar_id, NULL) THEN
3549 RAISE e_unexpected_error;
3550 END IF;
3551
3552 RETURN TRUE;
3553
3554 EXCEPTION
3555 WHEN e_unexpected_error THEN
3556 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3557 x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3558 RETURN FALSE;
3559
3560 WHEN OTHERS THEN
3561 BSC_MESSAGE.Add(x_message => SQLERRM,
3562 x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3563 RETURN FALSE;
3564
3565 END Init_Calendar_Tables;
3566
3567
3568 --LOCKING: new function
3569 /*===========================================================================+
3570 | FUNCTION Init_Calendar_Tables_AT
3571 +============================================================================*/
3572 FUNCTION Init_Calendar_Tables_AT(
3573 x_calendar_id IN NUMBER
3574 ) RETURN BOOLEAN IS
3575 PRAGMA AUTONOMOUS_TRANSACTION;
3576 h_b BOOLEAN;
3577 BEGIN
3578 h_b := Init_Calendar_Tables(x_calendar_id);
3579 commit; -- all autonomous transaction needs to commit
3580 RETURN h_b;
3581 END Init_Calendar_Tables_AT;
3582
3583
3584 -- ENH_B_TABLES_PERF: new function
3585 /*===========================================================================+
3586 | FUNCTION Is_Base_Table
3587 +============================================================================*/
3588 FUNCTION Is_Base_Table(
3589 x_table_name IN VARCHAR2
3590 ) RETURN BOOLEAN IS
3591
3592 CURSOR c_table_type IS
3593 SELECT t.table_type
3594 FROM bsc_db_tables t, bsc_db_tables_rels r
3595 WHERE t.table_name = r.source_table_name and
3596 r.table_name = x_table_name;
3597
3598 h_table_type NUMBER;
3599
3600 BEGIN
3601
3602 OPEN c_table_type;
3603 FETCH c_table_type INTO h_table_type;
3604 IF c_table_type%NOTFOUND THEN
3605 h_table_type := 1;
3606 END IF;
3607 CLOSE c_table_type;
3608
3609 IF h_table_type = 0 THEN
3610 RETURN TRUE;
3611 ELSE
3612 RETURN FALSE;
3613 END IF;
3614
3615 END Is_Base_Table;
3616
3617
3618 /*===========================================================================+
3619 | FUNCTION Is_EDW_Kpi_Table
3620 +============================================================================*/
3621 FUNCTION Is_EDW_Kpi_Table(
3622 x_table_name IN VARCHAR2
3623 ) RETURN BOOLEAN IS
3624
3625 h_b BOOLEAN;
3626
3627 /* TYPE t_cursor IS REF CURSOR;
3628
3629 c_indicator t_cursor; -- x_table_name, 1
3630 c_indicator_sql VARCHAR2(2000) := 'SELECT t.indicator'||
3631 ' FROM bsc_kpi_data_tables t, bsc_kpis_b k'||
3632 ' WHERE t.indicator = k.indicator AND'||
3633 ' table_name = :1 AND NVL(k.edw_flag, 0) = :2';
3634 */
3635 cursor c_indicator (pTableName varchar2, pEDWFlag number ) is
3636 SELECT t.indicator
3637 FROM bsc_kpi_data_tables t, bsc_kpis_b k
3638 WHERE t.indicator = k.indicator AND
3639 table_name = pTableName AND NVL(k.edw_flag, 0) = pEDWFlag;
3640
3641 h_indicator NUMBER;
3642
3643 BEGIN
3644 h_b := FALSE;
3645
3646 --OPEN c_indicator FOR c_indicator_sql USING x_table_name, 1;
3647 OPEN c_indicator(x_table_name, 1);
3648 FETCH c_indicator INTO h_indicator;
3649 IF c_indicator%FOUND THEN
3650 h_b := TRUE;
3651 END IF;
3652 CLOSE c_indicator;
3653
3654 RETURN h_b;
3655
3656 END Is_EDW_Kpi_Table;
3657
3658
3659 /*===========================================================================+
3660 | FUNCTION Item_Belong_To_Array_Number
3661 +============================================================================*/
3662 FUNCTION Item_Belong_To_Array_Number(
3663 x_item IN NUMBER,
3664 x_array IN t_array_of_number,
3665 x_num_items IN NUMBER
3666 ) RETURN BOOLEAN IS
3667
3668 h_i NUMBER;
3669
3670 BEGIN
3671 FOR h_i IN 1 .. x_num_items LOOP
3672 IF x_array(h_i) = x_item THEN
3673 RETURN TRUE;
3674 END IF;
3675 END LOOP;
3676
3677 RETURN FALSE;
3678
3679 END Item_Belong_To_Array_Number;
3680
3681
3682 /*===========================================================================+
3683 | FUNCTION Item_Belong_To_Array_Varchar2
3684 +============================================================================*/
3685 FUNCTION Item_Belong_To_Array_Varchar2(
3686 x_item IN VARCHAR2,
3687 x_array IN t_array_of_varchar2,
3688 x_num_items IN NUMBER
3689 ) RETURN BOOLEAN IS
3690
3691 h_i NUMBER;
3692
3693 BEGIN
3694 FOR h_i IN 1 .. x_num_items LOOP
3695 IF UPPER(x_array(h_i)) = UPPER(x_item) THEN
3696 RETURN TRUE;
3697 END IF;
3698 END LOOP;
3699
3700 RETURN FALSE;
3701
3702 END Item_Belong_To_Array_Varchar2;
3703
3704
3705 --LOCKING: New procedure
3706 /*===========================================================================+
3707 | PROCEDURE Load_Calendar_Into_AW_AT
3708 +============================================================================*/
3709 PROCEDURE Load_Calendar_Into_AW_AT(
3710 x_calendar_id IN NUMBER
3711 ) IS
3712 PRAGMA AUTONOMOUS_TRANSACTION;
3713 BEGIN
3714 --Fix bug#4360037: load calendar into aw only if there are aw indicators
3715 IF Calendar_Used_In_AW_Kpi(x_calendar_id) THEN
3716 bsc_aw_calendar.create_calendar(
3717 p_calendar => x_calendar_id,
3718 p_options => 'DEBUG LOG, RECREATE'
3719 );
3720 bsc_aw_calendar.load_calendar(
3721 p_calendar => x_calendar_id,
3722 p_options => 'DEBUG LOG'
3723 );
3724 END IF;
3725 commit; -- all autonomous transaction needs to commit
3726 END Load_Calendar_Into_AW_AT;
3727
3728
3729 /*===========================================================================+
3730 | FUNCTION Load_Periodicity_Rels
3731 +============================================================================*/
3732 FUNCTION Load_Periodicity_Rels RETURN BOOLEAN IS
3733
3734 h_sql VARCHAR2(32000);
3735
3736 CURSOR c_per_rels IS
3737 SELECT PERIODICITY_ID, SOURCE
3738 FROM BSC_SYS_PERIODICITIES
3739 ORDER BY PERIODICITY_ID;
3740
3741 h_periodicity_id NUMBER;
3742 h_source VARCHAR2(500);
3743
3744 h_index NUMBER;
3745
3746 h_sources BSC_UPDATE_UTIL.t_array_of_number;
3747 h_num_sources NUMBER;
3748 h_i NUMBER;
3749 h_j NUMBER;
3750 h_source_periodicity_id NUMBER;
3751
3752 h_arr_new_periodicity_rels BSC_UPDATE_UTIL.t_array_periodicity_rels;
3753 h_num_new_periodicity_rels NUMBER;
3754
3755 BEGIN
3756 h_sql := NULL;
3757 h_index := 0;
3758
3759 IF g_array_periodicity_rels.COUNT > 0 THEN
3760 -- array already initialized
3761 RETURN TRUE;
3762 END IF;
3763
3764 OPEN c_per_rels;
3765 LOOP
3766 FETCH c_per_rels INTO h_periodicity_id, h_source;
3767 EXIT WHEN c_per_rels%NOTFOUND;
3768
3769 h_num_sources := Decompose_Numeric_List(h_source, h_sources, ',');
3770
3771 FOR h_i IN 1..h_num_sources LOOP
3772 h_index := h_index + 1;
3773 g_array_periodicity_rels(h_index).periodicity_id := h_periodicity_id;
3774 g_array_periodicity_rels(h_index).source_periodicity_id := h_sources(h_i);
3775 END LOOP;
3776
3777 END LOOP;
3778 CLOSE c_per_rels;
3779
3780 -- Completes the source of periodicities. For example
3781 -- if periodicity A can be calculated from B and B can be calculated from C then
3782 -- A also can be calculated from C
3783 LOOP
3784 h_num_new_periodicity_rels := 0;
3785 h_arr_new_periodicity_rels.delete;
3786
3787 FOR h_i IN 1..g_array_periodicity_rels.COUNT LOOP
3788 h_periodicity_id := g_array_periodicity_rels(h_i).periodicity_id;
3789 h_source_periodicity_id := g_array_periodicity_rels(h_i).source_periodicity_id;
3790
3791 h_num_sources := Get_Source_Periodicities(h_source_periodicity_id, h_sources);
3792
3793 FOR h_j IN 1..h_num_sources LOOP
3794 IF NOT Exist_Periodicity_Rel(h_periodicity_id, h_sources(h_j)) THEN
3795 h_num_new_periodicity_rels := h_num_new_periodicity_rels + 1;
3796 h_arr_new_periodicity_rels(h_num_new_periodicity_rels).periodicity_id := h_periodicity_id;
3797 h_arr_new_periodicity_rels(h_num_new_periodicity_rels).source_periodicity_id := h_sources(h_j);
3798 END IF;
3799 END LOOP;
3800 END LOOP;
3801
3802 FOR h_j IN 1..h_num_new_periodicity_rels LOOP
3803 h_index := h_index + 1;
3804 g_array_periodicity_rels(h_index).periodicity_id := h_arr_new_periodicity_rels(h_j).periodicity_id;
3805 g_array_periodicity_rels(h_index).source_periodicity_id := h_arr_new_periodicity_rels(h_j).source_periodicity_id;
3806 END LOOP;
3807
3808 EXIT WHEN (h_num_new_periodicity_rels = 0);
3809 END LOOP;
3810
3811 RETURN TRUE;
3812
3813 EXCEPTION
3814 WHEN OTHERS THEN
3815 BSC_MESSAGE.Add(x_message => SQLERRM,
3816 x_source => 'BSC_UPDATE_UTIL.Load_Periodicity_Rels');
3817 RETURN FALSE;
3818
3819 END Load_Periodicity_Rels;
3820
3821
3822 /*===========================================================================+
3823 | FUNCTION Make_Lst_Cond_Join
3824 +============================================================================*/
3825 FUNCTION Make_Lst_Cond_Join(
3826 x_table_1 IN VARCHAR2,
3827 x_key_columns_1 IN t_array_of_varchar2,
3828 x_table_2 IN VARCHAR2,
3829 x_key_columns_2 IN t_array_of_varchar2,
3830 x_num_key_columns IN NUMBER,
3831 x_separator IN VARCHAR2
3832 ) RETURN VARCHAR2 IS
3833
3834 h_i NUMBER;
3835 h_lst VARCHAR2(32700);
3836
3837 BEGIN
3838 h_lst := NULL;
3839
3840 FOR h_i IN 1 .. x_num_key_columns LOOP
3841 IF h_lst IS NULL THEN
3842 h_lst := x_table_1||'.'||x_key_columns_1(h_i)||' = '||
3843 x_table_2||'.'||x_key_columns_2(h_i);
3844 ELSE
3845 h_lst := h_lst||' '||x_separator||' '||
3846 x_table_1||'.'||x_key_columns_1(h_i)||' = '||
3847 x_table_2||'.'||x_key_columns_2(h_i);
3848 END IF;
3849 END LOOP;
3850
3851 RETURN h_lst;
3852
3853 END Make_Lst_Cond_Join;
3854
3855
3856 /*===========================================================================+
3857 | FUNCTION Make_Lst_Cond_Left_Join
3858 +============================================================================*/
3859 FUNCTION Make_Lst_Cond_Left_Join(
3860 x_table_1 IN VARCHAR2,
3861 x_key_columns_1 IN t_array_of_varchar2,
3862 x_table_2 IN VARCHAR2,
3863 x_key_columns_2 IN t_array_of_varchar2,
3864 x_num_key_columns IN NUMBER,
3865 x_separator IN VARCHAR2
3866 ) RETURN VARCHAR2 IS
3867
3868 h_i NUMBER;
3869 h_lst VARCHAR2(32700);
3870
3871 BEGIN
3872 h_lst := NULL;
3873
3874 FOR h_i IN 1 .. x_num_key_columns LOOP
3875 IF h_lst IS NULL THEN
3876 h_lst := x_table_1||'.'||x_key_columns_1(h_i)||' = '||
3877 x_table_2||'.'||x_key_columns_2(h_i)||' (+)';
3878 ELSE
3879 h_lst := h_lst||' '||x_separator||' '||
3880 x_table_1||'.'||x_key_columns_1(h_i)||' = '||
3881 x_table_2||'.'||x_key_columns_2(h_i)||' (+)';
3882 END IF;
3883 END LOOP;
3884
3885 RETURN h_lst;
3886
3887 END Make_Lst_Cond_Left_Join;
3888
3889
3890 /*===========================================================================+
3891 | FUNCTION Make_Lst_Cond_Null
3892 +============================================================================*/
3893 FUNCTION Make_Lst_Cond_Null(
3894 x_table IN VARCHAR2,
3895 x_key_columns IN t_array_of_varchar2,
3896 x_num_key_columns IN NUMBER,
3897 x_separator IN VARCHAR2
3898 ) RETURN VARCHAR2 IS
3899
3900 h_i NUMBER;
3901 h_lst VARCHAR2(32700);
3902
3903 BEGIN
3904 h_lst := NULL;
3905
3906 FOR h_i IN 1 .. x_num_key_columns LOOP
3907 IF h_lst IS NULL THEN
3908 h_lst := x_table||'.'||x_key_columns(h_i)||' IS NULL';
3909 ELSE
3910 h_lst := h_lst||' '||x_separator||' '||
3911 x_table||'.'||x_key_columns(h_i)||' IS NULL';
3912 END IF;
3913 END LOOP;
3914
3915 RETURN h_lst;
3916
3917 END Make_Lst_Cond_Null;
3918
3919
3920 /*===========================================================================+
3921 | FUNCTION Make_Lst_Cond_Number
3922 +============================================================================*/
3923 FUNCTION Make_Lst_Cond_Number(
3924 x_column IN VARCHAR2,
3925 x_values IN t_array_of_number,
3926 x_num_values IN NUMBER,
3927 x_separator IN VARCHAR2
3928 ) RETURN VARCHAR2 IS
3929
3930 h_i NUMBER;
3931 h_lst VARCHAR2(32700);
3932
3933 BEGIN
3934 h_lst := NULL;
3935
3936 FOR h_i IN 1 .. x_num_values LOOP
3937 IF h_lst IS NULL THEN
3938 h_lst := x_column||'='||x_values(h_i);
3939 ELSE
3940 h_lst := h_lst||' '||x_separator||' '||
3941 x_column||'='||x_values(h_i);
3942 END IF;
3943 END LOOP;
3944
3945 RETURN h_lst;
3946
3947 END Make_Lst_Cond_Number;
3948
3949
3950 /*===========================================================================+
3951 | FUNCTION Make_Lst_Description
3952 +============================================================================*/
3953 FUNCTION Make_Lst_Description(
3954 x_array IN t_array_of_varchar2,
3955 x_num_items IN NUMBER,
3956 x_data_type IN VARCHAR2
3957 ) RETURN VARCHAR2 IS
3958
3959 h_i NUMBER;
3960 h_lst VARCHAR2(32700);
3961
3962 BEGIN
3963 h_lst := NULL;
3964
3965 FOR h_i IN 1 .. x_num_items LOOP
3966 IF h_lst IS NULL THEN
3967 h_lst := x_array(h_i)||' '||x_data_type;
3968 ELSE
3969 h_lst := h_lst||', '||x_array(h_i)||' '||x_data_type;
3970 END IF;
3971 END LOOP;
3972
3973 RETURN h_lst;
3974
3975 END Make_Lst_Description;
3976
3977
3978 /*===========================================================================+
3979 | FUNCTION Make_Lst_From_Array_Varchar2
3980 +============================================================================*/
3981 FUNCTION Make_Lst_From_Array_Varchar2(
3982 x_array IN t_array_of_varchar2,
3983 x_num_items IN NUMBER
3984 ) RETURN VARCHAR2 IS
3985
3986 h_i NUMBER;
3987 h_lst VARCHAR2(32700);
3988
3989 BEGIN
3990 h_lst := NULL;
3991
3992 FOR h_i IN 1 .. x_num_items LOOP
3993 IF h_lst IS NULL THEN
3994 h_lst := x_array(h_i);
3995 ELSE
3996 h_lst := h_lst||', '||x_array(h_i);
3997 END IF;
3998 END LOOP;
3999
4000 RETURN h_lst;
4001
4002 END Make_Lst_From_Array_Varchar2;
4003
4004
4005 /*===========================================================================+
4006 | FUNCTION Make_Lst_Fixed_Column
4007 +============================================================================*/
4008 FUNCTION Make_Lst_Fixed_Column(
4009 x_fixed_column_name IN VARCHAR2,
4010 x_num_items IN NUMBER
4011 ) RETURN VARCHAR2 IS
4012
4013 h_i NUMBER;
4014 h_lst VARCHAR2(32700);
4015
4016 BEGIN
4017 h_lst := NULL;
4018
4019 FOR h_i IN 1 .. x_num_items LOOP
4020 IF h_lst IS NULL THEN
4021 h_lst := x_fixed_column_name||h_i;
4022 ELSE
4023 h_lst := h_lst||', '||x_fixed_column_name||h_i;
4024 END IF;
4025 END LOOP;
4026
4027 RETURN h_lst;
4028
4029 END Make_Lst_Fixed_Column;
4030
4031
4032 /*===========================================================================+
4033 | FUNCTION Make_Lst_Table_Column
4034 +============================================================================*/
4035 FUNCTION Make_Lst_Table_Column(
4036 x_table_name IN VARCHAR2,
4037 x_columns IN t_array_of_varchar2,
4038 x_num_columns IN NUMBER
4039 ) RETURN VARCHAR2 IS
4040
4041 h_i NUMBER;
4042 h_lst VARCHAR2(32700);
4043
4044 BEGIN
4045 h_lst := NULL;
4046
4047 FOR h_i IN 1 .. x_num_columns LOOP
4048 IF h_lst IS NULL THEN
4049 h_lst := x_table_name||'.'||x_columns(h_i);
4050 ELSE
4051 h_lst := h_lst||', '||x_table_name||'.'||x_columns(h_i);
4052 END IF;
4053 END LOOP;
4054
4055 RETURN h_lst;
4056
4057 END Make_Lst_Table_Column;
4058
4059
4060 /*===========================================================================+
4061 | FUNCTION Populate_Bsc_Db_Calendar
4062 +============================================================================*/
4063 FUNCTION Populate_Bsc_Db_Calendar(
4064 x_calendar_id NUMBER,
4065 X_Current_Fiscal_Yr NUMBER,
4066 X_Fy_Start_Yr NUMBER,
4067 X_Fy_Start_Mth NUMBER,
4068 X_Fy_Start_Day NUMBER
4069 ) RETURN BOOLEAN IS
4070
4071 sql_stmt VARCHAR2(2000); -- Sql statement string
4072
4073 num_foryears NUMBER; -- Number of forward years
4074 -- (number_of_years - number_of_backyears)
4075 num_backyears NUMBER; -- Number of back years(number_of_backyears)
4076
4077 h_first_year NUMBER;
4078 h_year_save NUMBER;
4079 h_last_year NUMBER;
4080
4081 h_fy_start_date DATE;
4082 h_fy_end_date DATE;
4083
4084 h_current_date DATE;
4085 h_monthly NUMBER;
4086 h_semester NUMBER;
4087 h_quarterly NUMBER;
4088 h_bimonthly NUMBER;
4089 h_weekly52 NUMBER;
4090 h_weekly4 NUMBER;
4091 h_daily365 NUMBER;
4092 h_daily30 NUMBER;
4093 h_year NUMBER;
4094 h_month NUMBER;
4095 h_day NUMBER;
4096
4097 h_pername_list VARCHAR2(300);
4098 y NUMBER;
4099
4100 TYPE t_cursor IS REF CURSOR;
4101
4102 -- Only look at tables using bsc calendar
4103 /*
4104 get_range_yr t_cursor; -- 2, 0, x_calendar_id
4105 get_range_yr_sql VARCHAR2(2000) := 'SELECT nvl(max(num_of_years - previous_years), 1),'||
4106 ' nvl(max(previous_years), 1)'||
4107 ' FROM bsc_db_tables'||
4108 ' WHERE table_type <> :1 AND nvl(num_of_years, 0) > :2 AND'||
4109 ' periodicity_id IN (SELECT periodicity_id'||
4110 ' FROM bsc_sys_periodicities'||
4111 ' WHERE calendar_id = :3)';
4112 */
4113 CURSOR get_range_yr (p_table_type NUMBER, p_num_of_years NUMBER, p_calendar_id NUMBER) IS
4114 SELECT nvl(max(num_of_years - previous_years), 1), nvl(max(previous_years), 1)
4115 FROM bsc_db_tables
4116 WHERE table_type <> p_table_type AND nvl(num_of_years, 0) > p_num_of_years AND
4117 periodicity_id IN (
4118 SELECT periodicity_id
4119 FROM bsc_sys_periodicities
4120 WHERE calendar_id = p_calendar_id);
4121
4122
4123 h_message VARCHAR2(4000);
4124
4125 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
4126 h_num_bind_vars NUMBER;
4127
4128 BEGIN
4129 /* Get the range of years the system tables need. */
4130 --OPEN get_range_yr FOR get_range_yr_sql USING 2, 0, x_calendar_id;
4131 OPEN get_range_yr(2, 0, x_calendar_id);
4132 FETCH get_range_yr INTO num_foryears, num_backyears;
4133 IF get_range_yr%NOTFOUND THEN
4134 num_foryears := 1;
4135 num_backyears := 1;
4136 END IF;
4137 CLOSE get_range_yr;
4138
4139 h_first_year := X_Fy_Start_Yr - num_backyears;
4140 h_year_save := X_Current_Fiscal_Yr - num_backyears;
4141 h_last_year := h_first_year + (num_backyears + num_foryears) - 1;
4142
4143 -- Delete all rows from bsc_db_calendar
4144 /*
4145 sql_stmt := 'DELETE FROM bsc_db_calendar WHERE calendar_id = :1';
4146 EXECUTE IMMEDIATE sql_stmt USING x_calendar_id;
4147 */
4148 DELETE FROM bsc_db_calendar WHERE calendar_id = x_calendar_id;
4149
4150 h_pername_list := 'year, semester, quarter, bimester, month, week52, '||
4151 'week4, day365, day30';
4152
4153 FOR y in h_first_year .. h_last_year LOOP
4154 -- Get the calendar dates for a fiscal year start and end.
4155 h_fy_start_date := to_date(to_char(X_Fy_Start_Day) || '-' ||
4156 to_char(X_Fy_Start_Mth) || '-' ||
4157 to_char(y), 'DD-MM-YYYY');
4158 h_fy_end_date := Add_Months(h_fy_start_date, 12) - 1;
4159
4160 h_monthly := 1;
4161 h_semester := 1;
4162 h_quarterly := 1;
4163 h_bimonthly := 1;
4164 h_weekly52 := 1;
4165 h_weekly4 := 1;
4166 h_daily365 := 1;
4167 h_daily30 := 1;
4168
4169 h_current_date := h_fy_start_date;
4170
4171 WHILE (h_current_date <= h_fy_end_date) LOOP
4172 h_day := to_number(to_char(h_current_date, 'DD'));
4173 h_month := to_number(to_char(h_current_date, 'MM'));
4174 h_year := to_number(to_char(h_current_date, 'YYYY'));
4175
4176 /*
4177 sql_stmt :=
4178 'INSERT INTO bsc_db_calendar (calendar_id, calendar_year, calendar_month,'||
4179 ' calendar_day, ' || h_pername_list ||
4180 ') VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)';
4181
4182 h_bind_vars_values.delete;
4183 h_bind_vars_values(1) := x_calendar_id;
4184 h_bind_vars_values(2) := h_year;
4185 h_bind_vars_values(3) := h_month;
4186 h_bind_vars_values(4) := h_day;
4187 h_bind_vars_values(5) := h_year_save;
4188 h_bind_vars_values(6) := h_semester;
4189 h_bind_vars_values(7) := h_quarterly;
4190 h_bind_vars_values(8) := h_bimonthly;
4191 h_bind_vars_values(9) := h_monthly;
4192 h_bind_vars_values(10) := h_weekly52;
4193 h_bind_vars_values(11) := h_weekly4;
4194 h_bind_vars_values(12) := h_daily365;
4195 h_bind_vars_values(13) := h_daily30;
4196 h_num_bind_vars := 13;
4197 BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
4198 */
4199
4200 INSERT INTO bsc_db_calendar (calendar_id, calendar_year, calendar_month, calendar_day,
4201 year, semester, quarter, bimester, month, week52, week4, day365, day30)
4202 VALUES (x_calendar_id, h_year, h_month, h_day, h_year_save, h_semester, h_quarterly,
4203 h_bimonthly, h_monthly, h_weekly52, h_weekly4, h_daily365, h_daily30);
4204
4205 -- Increment current date by one day.
4206 h_current_date := h_current_date + 1;
4207
4208 IF (to_char(h_current_date, 'MM') <>
4209 to_char(h_current_date - 1, 'MM')) THEN
4210 -- In a different month.
4211 h_monthly := h_monthly + 1;
4212 h_semester := floor((h_monthly - 1) / 6 + 1);
4213 h_quarterly := floor((h_monthly - 1) / 3 + 1);
4214 h_bimonthly := floor((h_monthly - 1) / 2 + 1);
4215 h_daily30 := 1;
4216 ELSE
4217 -- In the same month.
4218 h_daily30 := h_daily30 + 1;
4219 END IF;
4220
4221 h_daily365 := h_daily365 + 1;
4222
4223 IF (to_char(h_current_date, 'DY') =
4224 to_char(h_fy_start_date, 'DY')) THEN
4225 -- Current date is on the same day of the week as the fiscal
4226 -- year start date. Increment week values by 1.
4227 IF (to_char(h_current_date, 'MM') <>
4228 to_char(h_current_date - 7, 'MM')) THEN
4229 -- In a different month.
4230 h_weekly4 := 1;
4231 ELSE
4232 -- In the same month.
4233 h_weekly4 := h_weekly4 + 1;
4234 END IF;
4235
4236 h_weekly52 := h_weekly52 + 1;
4237 END IF;
4238 END LOOP; /* WHILE (h_current_date <= h_fy_end_date) LOOP */
4239
4240 h_year_save := h_year_save + 1;
4241
4242 END LOOP; /* FOR y in h_first_year .. h_last_year LOOP */
4243
4244 RETURN TRUE;
4245
4246 EXCEPTION
4247 WHEN OTHERS THEN
4248 BSC_MESSAGE.Add(
4249 X_Message => SQLERRM,
4250 X_Source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Calendar');
4251 RETURN FALSE;
4252
4253 END Populate_Bsc_Db_Calendar;
4254
4255
4256 /*===========================================================================+
4257 | FUNCTION Populate_Bsc_Db_Week_Maps
4258 +============================================================================*/
4259 FUNCTION Populate_Bsc_Db_Week_Maps (
4260 x_calendar_id IN NUMBER
4261 ) RETURN BOOLEAN IS
4262
4263 sql_stmt VARCHAR2(2000);
4264 h_message VARCHAR2(4000);
4265
4266 BEGIN
4267 -- Delete all rows from bsc_db_week_maps
4268 /* sql_stmt := 'DELETE FROM BSC_DB_WEEK_MAPS'||
4269 ' WHERE CALENDAR_ID = :1';
4270 EXECUTE IMMEDIATE sql_stmt USING x_calendar_id; */
4271
4272 DELETE FROM BSC_DB_WEEK_MAPS
4273 WHERE CALENDAR_ID = x_calendar_id;
4274
4275 -- Insert the records
4276 /* sql_stmt := 'INSERT INTO bsc_db_week_maps (year, month, week, week52, calendar_id)'||
4277 ' SELECT year, min(month), week4, week52, calendar_id'||
4278 ' FROM bsc_db_calendar'||
4279 ' WHERE calendar_id = :1'||
4280 ' GROUP BY year, week4, week52, calendar_id';
4281 EXECUTE IMMEDIATE sql_stmt USING x_calendar_id; */
4282 INSERT INTO bsc_db_week_maps (year, month, week, week52, calendar_id)
4283 SELECT year, min(month), week4, week52, calendar_id
4284 FROM bsc_db_calendar
4285 WHERE calendar_id = x_calendar_id
4286 GROUP BY year, week4, week52, calendar_id;
4287
4288 RETURN TRUE;
4289
4290 EXCEPTION
4291 WHEN OTHERS THEN
4292 BSC_MESSAGE.Add(
4293 x_message => SQLERRM,
4294 x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Week_Maps');
4295 RETURN FALSE;
4296
4297 END Populate_Bsc_Db_Week_Maps;
4298
4299
4300 /*===========================================================================+
4301 | FUNCTION Populate_Bsc_Sys_Periods_Tl
4302 +============================================================================*/
4303 FUNCTION Populate_Bsc_Sys_Periods_Tl(
4304 x_calendar_id IN NUMBER
4305 ) RETURN BOOLEAN IS
4306
4307 h_sql VARCHAR2(32700);
4308 h_message VARCHAR2(4000);
4309
4310 TYPE t_cursor IS REF CURSOR;
4311
4312 CURSOR c_custom_pers (p_calendar_id NUMBER, p_custom_code NUMBER) IS
4313 SELECT periodicity_id, db_column_name
4314 FROM bsc_sys_periodicities
4315 WHERE calendar_id = p_calendar_id AND custom_code <> p_custom_code;
4316
4317 h_periodicity_id NUMBER;
4318 h_db_column_name VARCHAR2(50);
4319
4320 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
4321 h_num_bind_vars NUMBER;
4322
4323 BEGIN
4324 -- Delete all rows from bsc_sys_periods_tl
4325 DELETE FROM BSC_SYS_PERIODS_TL
4326 WHERE PERIODICITY_ID IN (
4327 SELECT PERIODICITY_ID
4328 FROM BSC_SYS_PERIODICITIES
4329 WHERE CALENDAR_ID = x_calendar_id);
4330
4331
4332 -- A. LABELS FOR PREDEFINED PERIODICITIES
4333 -- Insert the records
4334 INSERT INTO BSC_SYS_PERIODS_TL (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH,
4335 LANGUAGE, SOURCE_LANG, NAME, SHORT_NAME)
4336 SELECT
4337 CA.YEAR,
4338 CA.PERIODICITY_ID,
4339 CA.PERIOD_ID,
4340 1 AS MONTH,
4341 L.LANGUAGE_CODE AS LANGUAGE,
4342 L.LANGUAGE_CODE AS SOURCE_LANG,
4343 CA.NAME,
4344 NULL AS SHORT_NAME
4345 FROM
4346 ( SELECT
4347 C.YEAR AS YEAR,
4348 P.PERIODICITY_ID AS PERIODICITY_ID,
4349 C.SEMESTER AS PERIOD_ID,
4350 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
4351 FROM
4352 BSC_DB_CALENDAR C,
4353 BSC_DB_CALENDAR C2,
4354 BSC_SYS_PERIODICITIES P
4355 WHERE
4356 P.CALENDAR_ID = x_calendar_id AND
4357 P.PERIODICITY_TYPE = 2 AND
4358 C.YEAR = C2.YEAR AND
4359 C.CALENDAR_ID = C2.CALENDAR_ID AND
4360 C.CALENDAR_ID = P.CALENDAR_ID AND
4361 C.SEMESTER = C2.SEMESTER AND
4362 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
4363 (SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4364 FROM BSC_DB_CALENDAR C1
4365 WHERE C1.YEAR = C.YEAR AND C1.SEMESTER = C.SEMESTER AND C1.CALENDAR_ID = C.CALENDAR_ID
4366 ) AND
4367 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
4368 (SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4369 FROM BSC_DB_CALENDAR C1
4370 WHERE C1.YEAR = C2.YEAR AND C1.SEMESTER = C2.SEMESTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
4371 )
4372 UNION
4373 SELECT
4374 C.YEAR AS YEAR,
4375 P.PERIODICITY_ID AS PERIODICITY_ID,
4376 C.QUARTER AS PERIOD_ID,
4377 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
4378 FROM
4379 BSC_DB_CALENDAR C,
4380 BSC_DB_CALENDAR C2,
4381 BSC_SYS_PERIODICITIES P
4382 WHERE
4383 P.CALENDAR_ID = x_calendar_id AND
4384 P.PERIODICITY_TYPE = 3 AND
4385 C.YEAR = C2.YEAR AND
4386 C.CALENDAR_ID = C2.CALENDAR_ID AND
4387 C.CALENDAR_ID = P.CALENDAR_ID AND
4388 C.QUARTER = C2.QUARTER AND
4389 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
4390 (SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4391 FROM BSC_DB_CALENDAR C1
4392 WHERE C1.YEAR = C.YEAR AND C1.QUARTER = C.QUARTER AND C1.CALENDAR_ID = C.CALENDAR_ID
4393 ) AND
4394 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
4395 (SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4396 FROM BSC_DB_CALENDAR C1
4397 WHERE C1.YEAR = C2.YEAR AND C1.QUARTER = C2.QUARTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
4398 )
4399 UNION
4400 SELECT
4401 C.YEAR AS YEAR,
4402 P.PERIODICITY_ID AS PERIODICITY_ID,
4403 C.BIMESTER AS PERIOD_ID,
4404 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
4405 FROM
4406 BSC_DB_CALENDAR C,
4407 BSC_DB_CALENDAR C2,
4408 BSC_SYS_PERIODICITIES P
4409 WHERE
4410 P.CALENDAR_ID = x_calendar_id AND
4411 P.PERIODICITY_TYPE = 4 AND
4412 C.YEAR = C2.YEAR AND
4413 C.CALENDAR_ID = C2.CALENDAR_ID AND
4414 C.CALENDAR_ID = P.CALENDAR_ID AND
4415 C.BIMESTER = C2.BIMESTER AND
4416 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
4417 (SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4418 FROM BSC_DB_CALENDAR C1
4419 WHERE C1.YEAR = C.YEAR AND C1.BIMESTER = C.BIMESTER AND C1.CALENDAR_ID = C.CALENDAR_ID
4420 ) AND
4421 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
4422 (SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4423 FROM BSC_DB_CALENDAR C1
4424 WHERE C1.YEAR = C2.YEAR AND C1.BIMESTER = C2.BIMESTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
4425 )
4426 UNION
4427 SELECT
4428 C.YEAR AS YEAR,
4429 P.PERIODICITY_ID AS PERIODICITY_ID,
4430 C.MONTH AS PERIOD_ID,
4431 TO_CHAR(C.CALENDAR_MONTH) AS NAME
4432 FROM
4433 BSC_DB_CALENDAR C,
4434 BSC_SYS_PERIODICITIES P
4435 WHERE
4436 P.CALENDAR_ID = x_calendar_id AND
4437 P.PERIODICITY_TYPE = 5 AND
4438 C.CALENDAR_ID = P.CALENDAR_ID
4439 GROUP BY C.YEAR, C.MONTH, C.CALENDAR_MONTH, P.PERIODICITY_ID
4440 UNION
4441 SELECT
4442 C.YEAR AS YEAR,
4443 P.PERIODICITY_ID AS PERIODICITY_ID,
4444 C.WEEK52 AS PERIOD_ID,
4445 C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
4446 FROM
4447 BSC_DB_CALENDAR C,
4448 BSC_SYS_PERIODICITIES P
4449 WHERE
4450 P.CALENDAR_ID = x_calendar_id AND
4451 C.CALENDAR_ID = P.CALENDAR_ID AND
4452 P.PERIODICITY_TYPE = 7 AND
4453 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
4454 (SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
4455 FROM BSC_DB_CALENDAR C1
4456 WHERE C1.YEAR = C.YEAR AND C1.WEEK52 = C.WEEK52 AND C1.CALENDAR_ID = C.CALENDAR_ID
4457 )
4458 UNION
4459 SELECT
4460 C.YEAR AS YEAR,
4461 P.PERIODICITY_ID AS PERIODICITY_ID,
4462 C.DAY365 AS PERIOD_ID,
4463 C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
4464 FROM
4465 BSC_DB_CALENDAR C,
4466 BSC_SYS_PERIODICITIES P
4467 WHERE
4468 P.CALENDAR_ID = x_calendar_id AND
4469 P.PERIODICITY_TYPE = 9 AND
4470 C.CALENDAR_ID = P.CALENDAR_ID) CA, FND_LANGUAGES L WHERE L.INSTALLED_FLAG <> 'D';
4471
4472
4473 -- B. LABELS FOR CUSTOM PERIODICITIES
4474 --OPEN c_custom_pers FOR c_custom_pers_sql USING x_calendar_id, 0;
4475 OPEN c_custom_pers(x_calendar_id, 0);
4476 FETCH c_custom_pers INTO h_periodicity_id, h_db_column_name;
4477 WHILE c_custom_pers%FOUND LOOP
4478 h_sql := 'INSERT INTO BSC_SYS_PERIODS_TL (
4479 YEAR,
4480 PERIODICITY_ID,
4481 PERIOD_ID,
4482 MONTH,
4483 LANGUAGE,
4484 SOURCE_LANG,
4485 NAME,
4486 SHORT_NAME)
4487 SELECT
4488 C.YEAR AS YEAR,
4489 :1 AS PERIODICITY_ID,
4490 C.'||h_db_column_name||' AS PERIOD_ID,
4491 1 AS MONTH,
4492 L.LANGUAGE_CODE AS LANGUAGE,
4493 L.LANGUAGE_CODE AS SOURCE_LANG,
4494 C.CALENDAR_MONTH||'';''||C.CALENDAR_DAY AS NAME,
4495 NULL AS SHORT_NAME
4496 FROM
4497 BSC_DB_CALENDAR C,
4498 FND_LANGUAGES L
4499 WHERE
4500 L.INSTALLED_FLAG <> ''D'' AND
4501 C.CALENDAR_ID = :2 AND
4502 TO_DATE(C.CALENDAR_YEAR||''-''||C.CALENDAR_MONTH||''-''||C.CALENDAR_DAY,''YYYY-MM-DD'') =
4503 (SELECT
4504 MAX(TO_DATE(C1.CALENDAR_YEAR||''-''||C1.CALENDAR_MONTH||''-''||C1.CALENDAR_DAY,''YYYY-MM-DD''))
4505 FROM
4506 BSC_DB_CALENDAR C1
4507 WHERE
4508 C1.YEAR = C.YEAR AND
4509 C1.'||h_db_column_name||' = C.'||h_db_column_name||' AND
4510 C1.CALENDAR_ID = C.CALENDAR_ID
4511 )';
4512
4513 h_bind_vars_values.delete;
4514 h_bind_vars_values(1) := h_periodicity_id;
4515 h_bind_vars_values(2) := x_calendar_id;
4516 h_num_bind_vars := 2;
4517 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
4518
4519 FETCH c_custom_pers INTO h_periodicity_id, h_db_column_name;
4520 END LOOP;
4521 CLOSE c_custom_pers;
4522
4523
4524 RETURN TRUE;
4525
4526 EXCEPTION
4527 WHEN OTHERS THEN
4528 BSC_MESSAGE.Add(
4529 x_message => SQLERRM,
4530 x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');
4531 RETURN FALSE;
4532
4533 END Populate_Bsc_Sys_Periods_Tl;
4534
4535
4536 /*===========================================================================+
4537 | PROCEDURE Populate_Calendar_Tables
4538 +============================================================================*/
4539 PROCEDURE Populate_Calendar_Tables (
4540 x_calendar_id IN NUMBER,
4541 x_action IN NUMBER
4542 ) IS
4543
4544 e_error EXCEPTION;
4545 e_error_load_rpt_cal EXCEPTION;
4546 h_error_message VARCHAR2(2000);
4547
4548 BEGIN
4549 -- Init BSC/APPS global variables
4550 BSC_APPS.Init_Bsc_Apps;
4551
4552 -- Init calendar tables
4553 IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, x_action) THEN
4554 RAISE e_error;
4555 END IF;
4556
4557 -- BSC_MV Note: Populate reporting calendar
4558 --Fix bug#3847656: We only need to call Load_Reporting_Calendar when x_action=2
4559 IF x_action = 2 THEN
4560 IF BSC_APPS.bsc_mv THEN
4561 --Fix bug#4027813: call reporting calendar onlyto process this calendar
4562 IF NOT BSC_BIA_WRAPPER.Load_Reporting_Calendar(x_calendar_id, h_error_message) THEN
4563 RAISE e_error_load_rpt_cal;
4564 END IF;
4565
4566 -- AW_INTEGRATION: Call aw api to import calendar into aw world
4567 --Fix bug#4360037: load calendar into aw only if there are aw indicators
4568 IF Calendar_Used_In_Aw_Kpi(x_calendar_id) THEN
4569 bsc_aw_calendar.create_calendar(
4570 p_calendar => x_calendar_id,
4571 p_options => 'DEBUG LOG, RECREATE'
4572 );
4573 bsc_aw_calendar.load_calendar(
4574 p_calendar => x_calendar_id,
4575 p_options => 'DEBUG LOG'
4576 );
4577 END IF;
4578 END IF;
4579 END IF;
4580
4581 EXCEPTION
4582 WHEN e_error THEN
4583 BSC_MESSAGE.flush;
4584 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED'),
4585 x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4586 x_mode => 'I');
4587 COMMIT;
4588
4589 WHEN e_error_load_rpt_cal THEN
4590 BSC_MESSAGE.flush;
4591 BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar: '||h_error_message,
4592 x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4593 x_mode => 'I');
4594 COMMIT;
4595
4596 WHEN OTHERS THEN
4597 BSC_MESSAGE.Add(x_message => SQLERRM,
4598 x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4599 x_mode => 'I');
4600 COMMIT;
4601 END Populate_Calendar_Tables;
4602
4603
4604 /*===========================================================================+
4605 | PROCEDURE Populate_Calendar_Tables
4606 +============================================================================*/
4607 PROCEDURE Populate_Calendar_Tables (
4608 x_calendar_id IN NUMBER
4609 ) IS
4610
4611 BEGIN
4612 Populate_Calendar_Tables(x_calendar_id, NULL);
4613
4614 EXCEPTION
4615 WHEN OTHERS THEN
4616 BSC_MESSAGE.Add(x_message => SQLERRM,
4617 x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4618 x_mode => 'I');
4619 COMMIT;
4620 END Populate_Calendar_Tables;
4621
4622
4623 --Fix bug#4508980 : this api is provided to be called from OAF Calendar UI
4624 -- Note that from now on, load reporting calendar and load calendar into aw will be done in GDB
4625 /*===========================================================================+
4626 | PROCEDURE Populate_Calendar_Tables
4627 +============================================================================*/
4628 PROCEDURE Populate_Calendar_Tables (
4629 p_commit VARCHAR2,
4630 p_calendar_id NUMBER,
4631 x_return_status OUT NOCOPY VARCHAR2,
4632 x_msg_count OUT NOCOPY NUMBER,
4633 x_msg_data OUT NOCOPY VARCHAR2
4634 ) IS
4635
4636 e_error EXCEPTION;
4637 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Calendar_Tables';
4638
4639 BEGIN
4640 SAVEPOINT BscUpdateUtilPopCalTables;
4641 FND_MSG_PUB.Initialize;
4642 x_return_status := FND_API.G_RET_STS_SUCCESS;
4643
4644 -- Init BSC/APPS global variables
4645 BSC_APPS.Init_Bsc_Apps;
4646
4647 -- Init calendar tables
4648 IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(p_calendar_id) THEN
4649 RAISE e_error;
4650 END IF;
4651
4652 IF (p_commit = FND_API.G_TRUE) THEN
4653 COMMIT;
4654 END IF;
4655
4656 EXCEPTION
4657 WHEN e_error THEN
4658 ROLLBACK TO BscUpdateUtilPopCalTables;
4659 FND_MSG_PUB.Add_Exc_Msg(
4660 G_PKG_NAME,
4661 l_api_name,
4662 BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED')
4663 );
4664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4665 FND_MSG_PUB.Count_And_Get(
4666 p_encoded => 'F'
4667 ,p_count => x_msg_count
4668 ,p_data => x_msg_data
4669 );
4670 WHEN FND_API.G_EXC_ERROR THEN
4671 ROLLBACK TO BscUpdateUtilPopCalTables;
4672 x_return_status := FND_API.G_RET_STS_ERROR;
4673 FND_MSG_PUB.Count_And_Get(
4674 p_encoded => 'F'
4675 ,p_count => x_msg_count
4676 ,p_data => x_msg_data
4677 );
4678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4679 ROLLBACK TO BscUpdateUtilPopCalTables;
4680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4681 FND_MSG_PUB.Count_And_Get(
4682 p_encoded => 'F'
4683 ,p_count => x_msg_count
4684 ,p_data => x_msg_data
4685 );
4686 WHEN OTHERS THEN
4687 ROLLBACK TO BscUpdateUtilPopCalTables;
4688 FND_MSG_PUB.Add_Exc_Msg(
4689 G_PKG_NAME,
4690 l_api_name,
4691 SQLERRM
4692 );
4693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4694 FND_MSG_PUB.Count_And_Get(
4695 p_encoded => 'F'
4696 ,p_count => x_msg_count
4697 ,p_data => x_msg_data
4698 );
4699 END Populate_Calendar_Tables;
4700
4701
4702 /*===========================================================================+
4703 | FUNCTON Replace_Token
4704 +============================================================================*/
4705 FUNCTION Replace_Token(
4706 x_message IN VARCHAR2,
4707 x_token_name IN VARCHAR2,
4708 x_token_value IN VARCHAR2
4709 ) RETURN VARCHAR2 IS
4710
4711 h_message VARCHAR2(4000);
4712
4713 BEGIN
4714 h_message := REPLACE(x_message, '&'||x_token_name, x_token_value);
4715 RETURN h_message;
4716 END Replace_Token;
4717
4718
4719 /*===========================================================================+
4720 | FUNTION Set_Calendar_Fiscal_Year
4721 +============================================================================*/
4722 FUNCTION Set_Calendar_Fiscal_Year(
4723 x_calendar_id IN NUMBER,
4724 x_fiscal_year IN NUMBER
4725 ) RETURN BOOLEAN IS
4726
4727 h_user_id NUMBER;
4728
4729 TYPE t_cursor IS REF CURSOR;
4730 h_cursor t_cursor;
4731 h_sql VARCHAR2(32000);
4732
4733 h_sessionid NUMBER;
4734
4735 BEGIN
4736 h_sessionid := USERENV('SESSIONID');
4737
4738 -- Ref: bug#3482442 In corner cases this query can return more than one
4739 -- row and it will fail. AUDSID is not PK. After meeting with
4740 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
4741 h_user_id := BSC_APPS.fnd_global_user_id;
4742
4743 /*
4744 h_sql := 'UPDATE bsc_sys_calendars_b'||
4745 ' SET fiscal_year = :1, last_updated_by = :2, last_update_date = SYSDATE'||
4746 ' WHERE calendar_id = :3';
4747 EXECUTE IMMEDIATE h_sql USING x_fiscal_year, h_user_id, x_calendar_id;
4748 */
4749 UPDATE bsc_sys_calendars_b
4750 SET fiscal_year = x_fiscal_year, last_updated_by = h_user_id, last_update_date = SYSDATE
4751 WHERE calendar_id = x_calendar_id;
4752
4753 -- Fix bug#3636273 No need this commit here
4754 --COMMIT;
4755
4756 --Fix Bug in year change process.
4757 g_array_calendars(x_calendar_id).fiscal_year := x_fiscal_year;
4758
4759 RETURN TRUE;
4760
4761 EXCEPTION
4762 WHEN OTHERS THEN
4763 BSC_MESSAGE.Add(
4764 X_Message => SQLERRM,
4765 X_Source => 'BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year');
4766 RETURN FALSE;
4767
4768 END Set_Calendar_Fiscal_Year;
4769
4770
4771 /*===========================================================================+
4772 | FUNCTION Table_Exists |
4773 +============================================================================*/
4774 FUNCTION Table_Exists(
4775 X_Table VARCHAR2
4776 ) RETURN BOOLEAN IS
4777
4778 TYPE t_cursor IS REF CURSOR;
4779
4780 /*
4781 get_table t_cursor; -- X_Table
4782 get_table_sql VARCHAR2(2000) := 'SELECT table_name'||
4783 ' FROM USER_TABLES'||
4784 ' WHERE table_name = :1';
4785 */
4786 CURSOR get_table (p_table_name VARCHAR2) IS
4787 SELECT table_name
4788 FROM USER_TABLES
4789 WHERE table_name = p_table_name;
4790
4791 /*
4792 get_table_apps t_cursor; -- X_Table, BSC_APPS.BSC_APPS_SCHEMA
4793 get_table_apps_sql VARCHAR2(2000) := 'SELECT table_name'||
4794 ' FROM ALL_TABLES'||
4795 ' WHERE table_name = :1'||
4796 ' AND owner = :2';
4797 */
4798 CURSOR get_table_apps (p_table_name VARCHAR2, p_owner VARCHAR2) IS
4799 SELECT table_name
4800 FROM ALL_TABLES
4801 WHERE table_name = p_table_name AND owner = p_owner;
4802
4803 h_tbl VARCHAR2(30);
4804 h_table VARCHAR2(30);
4805 BEGIN
4806 h_table := UPPER(X_Table);
4807
4808 IF NOT BSC_APPS.APPS_ENV THEN
4809 -- Personal
4810 --OPEN get_table FOR get_table_sql USING h_table;
4811 OPEN get_table(h_table);
4812 FETCH get_table INTO h_tbl;
4813 IF get_table%NOTFOUND THEN
4814 CLOSE get_table;
4815 RETURN (FALSE);
4816 END IF;
4817 CLOSE get_table;
4818 ELSE
4819 -- APPS
4820 --OPEN get_table_apps FOR get_table_apps_sql USING h_table, BSC_APPS.BSC_APPS_SCHEMA;
4821 OPEN get_table_apps(h_table, BSC_APPS.BSC_APPS_SCHEMA);
4822 FETCH get_table_apps INTO h_tbl;
4823 IF get_table_apps%NOTFOUND THEN
4824 CLOSE get_table_apps;
4825 RETURN (FALSE);
4826 END IF;
4827 CLOSE get_table_apps;
4828 END IF;
4829
4830 RETURN (TRUE);
4831 END Table_Exists;
4832
4833
4834 /*===========================================================================+
4835 | FUNCTION Table_Has_Any_Row
4836 +============================================================================*/
4837 FUNCTION Table_Has_Any_Row(
4838 x_table_name IN VARCHAR2,
4839 x_condition IN VARCHAR2
4840 ) RETURN BOOLEAN IS
4841
4842 TYPE t_cursor IS REF CURSOR;
4843 h_cursor t_cursor;
4844
4845 h_sql VARCHAR2(2000);
4846
4847 h_num_rows NUMBER;
4848 h_res BOOLEAN;
4849
4850 BEGIN
4851 h_sql := 'SELECT COUNT(*) FROM '||x_table_name||' WHERE ROWNUM < :1';
4852 IF x_condition IS NOT NULL THEN
4853 h_sql := h_sql||' AND '||x_condition;
4854 END IF;
4855
4856 OPEN h_cursor FOR h_sql USING 2;
4857 FETCH h_cursor INTO h_num_rows;
4858 IF h_num_rows > 0 THEN
4859 h_res := TRUE;
4860 ELSE
4861 h_res := FALSE;
4862 END IF;
4863 CLOSE h_cursor;
4864
4865 RETURN h_res;
4866
4867 EXCEPTION
4868 WHEN OTHERS THEN
4869 BSC_MESSAGE.Add(
4870 X_Message => SQLERRM,
4871 X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
4872 RETURN NULL;
4873
4874 END Table_Has_Any_Row;
4875
4876
4877 /*===========================================================================+
4878 | FUNCTION Table_Has_Any_Row
4879 +============================================================================*/
4880 FUNCTION Table_Has_Any_Row(
4881 x_table_name IN VARCHAR2
4882 ) RETURN BOOLEAN IS
4883
4884 h_res BOOLEAN;
4885
4886 BEGIN
4887 h_res := Table_Has_Any_Row(x_table_name, NULL);
4888 RETURN h_res;
4889
4890 EXCEPTION
4891 WHEN OTHERS THEN
4892 BSC_MESSAGE.Add(
4893 X_Message => SQLERRM,
4894 X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
4895 RETURN NULL;
4896
4897 END Table_Has_Any_Row;
4898
4899
4900 /*===========================================================================+
4901 | PROCEDURE Truncate_Table
4902 +============================================================================*/
4903 PROCEDURE Truncate_Table(
4904 x_table_name IN VARCHAR2
4905 ) IS
4906
4907 h_sql VARCHAR2(200);
4908 h_bsc_schema VARCHAR2(30);
4909
4910 BEGIN
4911
4912 h_bsc_schema := BSC_APPS.bsc_apps_schema;
4913 IF h_bsc_schema IS NOT NULL THEN
4914 h_bsc_schema := h_bsc_schema||'.';
4915 END IF;
4916
4917 h_sql := 'TRUNCATE TABLE '||h_bsc_schema||x_table_name;
4918 Execute_Immediate(h_sql);
4919 END Truncate_Table;
4920
4921
4922 --LOCKING: new procedure
4923 /*===========================================================================+
4924 | PROCEDURE Truncate_Table_AT
4925 +============================================================================*/
4926 PROCEDURE Truncate_Table_AT(
4927 x_table_name IN VARCHAR2
4928 ) IS
4929 PRAGMA AUTONOMOUS_TRANSACTION;
4930 BEGIN
4931 Truncate_Table(x_table_name);
4932 END Truncate_Table_AT;
4933
4934
4935 /*===========================================================================+
4936 | PROCEDURE Update_AnualPeriodicity_Src
4937 +============================================================================*/
4938 PROCEDURE Update_AnualPeriodicity_Src (
4939 x_calendar_id IN NUMBER,
4940 x_periodicity_id IN NUMBER,
4941 x_action IN NUMBER
4942 ) IS
4943
4944 e_error EXCEPTION;
4945
4946 TYPE t_cursor IS REF CURSOR;
4947
4948 /*
4949 c_new_per t_cursor; -- x_calendar_id, 1
4950 c_new_per_sql VARCHAR2(2000) := 'SELECT SOURCE'||
4951 ' FROM BSC_SYS_PERIODICITIES'||
4952 ' WHERE CALENDAR_ID = :1 AND PERIODICITY_TYPE = :2'||
4953 ' ORDER BY PERIODICITY_ID';
4954 */
4955 CURSOR c_new_per (p_calendar_id NUMBER, p_periodicity_type NUMBER) IS
4956 SELECT SOURCE
4957 FROM BSC_SYS_PERIODICITIES
4958 WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type
4959 ORDER BY PERIODICITY_ID;
4960
4961 h_source VARCHAR2(200);
4962 h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
4963 h_count NUMBER;
4964 h_i NUMBER;
4965 h_new_per_id NUMBER;
4966 h_new_source VARCHAR2(200);
4967 x_exist BOOLEAN;
4968
4969 h_sql VARCHAR2(32000);
4970
4971 BEGIN
4972 -- Init BSC/APPS global variables
4973 BSC_APPS.Init_Bsc_Apps;
4974 --- Update the SOURCE columns
4975 --OPEN c_new_per FOR c_new_per_sql USING x_calendar_id, 1;
4976 OPEN c_new_per(x_calendar_id, 1);
4977 FETCH c_new_per INTO h_source;
4978 IF c_new_per%FOUND THEN
4979 h_new_source := '';
4980 x_exist := FALSE;
4981 IF h_source IS NOT NULL THEN
4982 h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
4983 FOR h_i IN 1.. h_count LOOP
4984 -- Get New Value
4985 h_new_per_id := h_tmp_array(h_i);
4986 --Delete
4987 IF x_action = 2 THEN
4988 IF h_new_per_id <> x_periodicity_id THEN
4989 IF h_new_source IS NOT NULL THEN
4990 h_new_source := h_new_source || ',' || h_new_per_id;
4991 ELSE
4992 h_new_source := h_new_per_id;
4993 END IF;
4994 END IF;
4995 ELSE
4996 --Update/add
4997 IF h_new_per_id = x_periodicity_id THEN
4998 x_exist := TRUE;
4999 END IF;
5000 IF h_new_source IS NOT NULL THEN
5001 h_new_source := h_new_source || ',' || h_new_per_id;
5002 ELSE
5003 h_new_source := h_new_per_id;
5004 END IF;
5005 END IF;
5006 END LOOP;
5007 IF x_action = 1 AND x_exist = FALSE THEN
5008 h_new_source := h_new_source || ',' || x_periodicity_id ;
5009 END IF;
5010
5011 -- Update the source
5012 /*
5013 h_sql := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1'||
5014 ' WHERE CALENDAR_ID = :2 AND PERIODICITY_TYPE = :3';
5015 EXECUTE IMMEDIATE h_sql USING h_new_source, x_calendar_id, 1;
5016 */
5017 UPDATE BSC_SYS_PERIODICITIES SET SOURCE = h_new_source
5018 WHERE CALENDAR_ID = x_calendar_id AND PERIODICITY_TYPE = 1;
5019
5020 ELSE
5021 IF x_action = 1 THEN
5022 h_new_source := x_periodicity_id ;
5023 -- Update the source
5024 /*
5025 h_sql := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1'||
5026 ' WHERE CALENDAR_ID = :2 AND PERIODICITY_TYPE = :3';
5027 EXECUTE IMMEDIATE h_sql USING h_new_source, x_calendar_id, 1;
5028 */
5029 UPDATE BSC_SYS_PERIODICITIES SET SOURCE = h_new_source
5030 WHERE CALENDAR_ID = x_calendar_id AND PERIODICITY_TYPE = 1;
5031 END IF;
5032 END IF;
5033 END IF;
5034 CLOSE c_new_per;
5035
5036 EXCEPTION
5037 WHEN OTHERS THEN
5038 BSC_MESSAGE.Add(x_message => SQLERRM,
5039 x_source => 'BSC_UPDATE_UTIL.UpdAnualPeriodicitySrc',
5040 x_mode => 'I');
5041 COMMIT;
5042 END Update_AnualPeriodicity_Src;
5043
5044
5045 /*===========================================================================+
5046 | PROCEDURE Update_Kpi_Period_Name
5047 +============================================================================*/
5048 FUNCTION Update_Kpi_Period_Name(
5049 x_indicator IN NUMBER
5050 ) RETURN BOOLEAN IS
5051
5052 h_edw_flag NUMBER;
5053 h_current_fy NUMBER;
5054 h_sql VARCHAR2(32700);
5055
5056 TYPE t_cursor IS REF CURSOR;
5057 h_cursor t_cursor;
5058
5059 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5060 h_num_bind_vars NUMBER;
5061
5062 BEGIN
5063 -- Get Kpi EDW Flag
5064 /* h_sql := 'SELECT NVL(edw_flag, 0)'||
5065 ' FROM bsc_kpis_b'||
5066 ' WHERE indicator = :1';
5067 OPEN h_cursor FOR h_sql USING x_indicator;
5068 FETCH h_cursor INTO h_edw_flag;
5069 CLOSE h_cursor; */
5070 SELECT NVL(edw_flag, 0)
5071 INTO h_edw_flag
5072 FROM bsc_kpis_b
5073 WHERE indicator = x_indicator;
5074
5075 IF h_edw_flag = 0 THEN
5076 -- BSC Kpi => BSC Periodicity
5077 -- Labels are in BSC_SYS_PERIODS_TL
5078
5079 h_sql := ' UPDATE
5080 BSC_KPI_DEFAULTS_TL D
5081 SET
5082 PERIOD_NAME = (
5083 SELECT
5084 DECODE(P.YEARLY_FLAG,
5085 1, K.PERIODICITY_ID||''-''||C.FISCAL_YEAR,
5086 (SELECT
5087 K.PERIODICITY_ID||''-''||L.NAME
5088 FROM
5089 BSC_KPI_PERIODICITIES KP,
5090 BSC_SYS_PERIODS_TL L
5091 WHERE
5092 K.INDICATOR = KP.INDICATOR AND
5093 K.PERIODICITY_ID = KP.PERIODICITY_ID AND
5094 C.FISCAL_YEAR = L.YEAR AND
5095 KP.PERIODICITY_ID = L.PERIODICITY_ID AND
5096 KP.CURRENT_PERIOD = L.PERIOD_ID AND
5097 D.LANGUAGE = L.LANGUAGE
5098 ))
5099 FROM
5100 BSC_DB_COLOR_KPI_V K,
5101 BSC_SYS_PERIODICITIES P,
5102 BSC_SYS_CALENDARS_B C
5103 WHERE
5104 K.TAB_ID = D.TAB_ID AND
5105 K.INDICATOR = D.INDICATOR AND
5106 K.PERIODICITY_ID = P.PERIODICITY_ID AND
5107 P.CALENDAR_ID = C.CALENDAR_ID
5108 )
5109 WHERE
5110 INDICATOR = :1';
5111
5112 h_bind_vars_values.delete;
5113 h_bind_vars_values(1) := x_indicator;
5114 h_num_bind_vars := 1;
5115 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5116
5117 ELSE
5118 -- EDW Kpi => EDW Periodicity
5119 -- Labels are in BSC_EDW_PERIODS_TL
5120 h_sql := 'UPDATE
5121 BSC_KPI_DEFAULTS_TL D
5122 SET
5123 PERIOD_NAME = (
5124 SELECT
5125 DECODE(P.YEARLY_FLAG,
5126 1, ''EDW-''||C.FISCAL_YEAR,
5127 (SELECT
5128 ''EDW-''||L.NAME
5129 FROM
5130 BSC_KPI_PERIODICITIES KP,
5131 BSC_EDW_PERIODS_TL L
5132 WHERE
5133 K.INDICATOR = KP.INDICATOR AND
5134 K.PERIODICITY_ID = KP.PERIODICITY_ID AND
5135 C.FISCAL_YEAR = L.YEAR AND
5136 KP.PERIODICITY_ID = L.PERIODICITY_ID AND
5137 KP.CURRENT_PERIOD = L.PERIOD_ID AND
5138 D.LANGUAGE = L.LANGUAGE
5139 ))
5140 FROM
5141 BSC_DB_COLOR_KPI_V K,
5142 BSC_SYS_PERIODICITIES P,
5143 BSC_SYS_CALENDARS_B C
5144 WHERE
5145 K.TAB_ID = D.TAB_ID AND
5146 K.INDICATOR = D.INDICATOR AND
5147 K.PERIODICITY_ID = P.PERIODICITY_ID AND
5148 P.CALENDAR_ID = C.CALENDAR_ID
5149 )
5150 WHERE
5151 INDICATOR = x_indicator' ;
5152
5153 h_bind_vars_values.delete;
5154 h_bind_vars_values(1) := x_indicator;
5155 h_num_bind_vars := 1;
5156 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5157
5158 END IF;
5159
5160 RETURN TRUE;
5161
5162 EXCEPTION
5163 WHEN OTHERS THEN
5164 BSC_MESSAGE.Add(
5165 X_Message => SQLERRM,
5166 X_Source => 'BSC_UPDATE_UTIL.Update_Kpi_Period_Name');
5167 RETURN FALSE;
5168
5169 END Update_Kpi_Period_Name;
5170
5171
5172 /*===========================================================================+
5173 | PROCEDURE Update_Kpi_Time_Stamp
5174 +============================================================================*/
5175 PROCEDURE Update_Kpi_Time_Stamp(
5176 x_indicator IN NUMBER
5177 ) IS
5178
5179 h_user_id NUMBER;
5180
5181 TYPE t_cursor IS REF CURSOR;
5182 h_cursor t_cursor;
5183 h_sql VARCHAR2(32000);
5184
5185 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5186 h_num_bind_vars NUMBER;
5187
5188 BEGIN
5189 -- Ref: bug#3482442 In corner cases this query can return more than one
5190 -- row and it will fail. AUDSID is not PK. After meeting with
5191 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5192 h_user_id := BSC_APPS.fnd_global_user_id;
5193
5194 /*
5195 h_sql := 'UPDATE bsc_kpis_b'||
5196 ' SET last_updated_by = :1,'||
5197 ' last_update_date = SYSDATE'||
5198 ' WHERE indicator = :2';
5199
5200 h_bind_vars_values.delete;
5201 h_bind_vars_values(1) := h_user_id;
5202 h_bind_vars_values(2) := x_indicator;
5203 h_num_bind_vars := 2;
5204 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5205 */
5206 UPDATE bsc_kpis_b
5207 SET last_updated_by = h_user_id,
5208 last_update_date = SYSDATE
5209 WHERE indicator = x_indicator;
5210
5211 --LOCKING: remove this commit
5212 --COMMIT;
5213
5214 END Update_Kpi_Time_Stamp;
5215
5216
5217 /*===========================================================================+
5218 | PROCEDURE Update_Kpi_Time_Stamp
5219 +============================================================================*/
5220 PROCEDURE Update_Kpi_Time_Stamp(
5221 x_condition IN VARCHAR2
5222 ) IS
5223
5224 h_user_id NUMBER;
5225
5226 TYPE t_cursor IS REF CURSOR;
5227 h_cursor t_cursor;
5228 h_sql VARCHAR2(32000);
5229
5230 h_sessionid NUMBER;
5231
5232 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5233 h_num_bind_vars NUMBER;
5234
5235 BEGIN
5236 h_sessionid := USERENV('SESSIONID');
5237
5238 -- Ref: bug#3482442 In corner cases this query can return more than one
5239 -- row and it will fail. AUDSID is not PK. After meeting with
5240 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5241 h_user_id := BSC_APPS.fnd_global_user_id;
5242
5243 h_sql := 'UPDATE bsc_kpis_b'||
5244 ' SET last_updated_by = :1, last_update_date = SYSDATE'||
5245 ' WHERE '||x_condition;
5246
5247 h_bind_vars_values.delete;
5248 h_bind_vars_values(1) := h_user_id;
5249 h_num_bind_vars := 1;
5250 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5251
5252 --LOCKING: remove this commit
5253 --COMMIT;
5254
5255 END Update_Kpi_Time_Stamp;
5256
5257
5258 /*===========================================================================+
5259 | PROCEDURE Update_Kpi_Tab_Time_Stamp
5260 +============================================================================*/
5261 PROCEDURE Update_Kpi_Tab_Time_Stamp(
5262 x_indicator IN NUMBER
5263 ) IS
5264
5265 h_user_id NUMBER;
5266
5267 TYPE t_cursor IS REF CURSOR;
5268 h_cursor t_cursor;
5269 h_sql VARCHAR2(32000);
5270
5271 h_sessionid NUMBER;
5272
5273 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5274 h_num_bind_vars NUMBER;
5275
5276 BEGIN
5277 h_sessionid := USERENV('SESSIONID');
5278
5279 -- Ref: bug#3482442 In corner cases this query can return more than one
5280 -- row and it will fail. AUDSID is not PK. After meeting with
5281 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5282 h_user_id := BSC_APPS.fnd_global_user_id;
5283
5284 /*
5285 h_sql := 'UPDATE bsc_tabs_b'||
5286 ' SET last_updated_by = :1,'||
5287 ' last_update_date = SYSDATE'||
5288 ' WHERE tab_id IN ('||
5289 ' SELECT tab_id'||
5290 ' FROM bsc_tab_indicators'||
5291 ' WHERE indicator = :2)';
5292
5293 h_bind_vars_values.delete;
5294 h_bind_vars_values(1) := h_user_id;
5295 h_bind_vars_values(2) := x_indicator;
5296 h_num_bind_vars := 2;
5297 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5298 */
5299 UPDATE bsc_tabs_b
5300 SET last_updated_by = h_user_id,
5301 last_update_date = SYSDATE
5302 WHERE tab_id IN (
5303 SELECT tab_id
5304 FROM bsc_tab_indicators
5305 WHERE indicator = x_indicator);
5306
5307 --LOCKING: remove this commit
5308 --COMMIT;
5309
5310 END Update_Kpi_Tab_Time_Stamp;
5311
5312
5313 /*===========================================================================+
5314 | PROCEDURE Update_Kpi_Tab_Time_Stamp
5315 +============================================================================*/
5316 PROCEDURE Update_Kpi_Tab_Time_Stamp(
5317 x_condition IN VARCHAR2
5318 ) IS
5319
5320 h_user_id NUMBER;
5321
5322 TYPE t_cursor IS REF CURSOR;
5323 h_cursor t_cursor;
5324 h_sql VARCHAR2(32000);
5325
5326 h_sessionid NUMBER;
5327
5328 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5329 h_num_bind_vars NUMBER;
5330
5331 BEGIN
5332 h_sessionid := USERENV('SESSIONID');
5333
5334 -- Ref: bug#3482442 In corner cases this query can return more than one
5335 -- row and it will fail. AUDSID is not PK. After meeting with
5336 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5337 h_user_id := BSC_APPS.fnd_global_user_id;
5338
5339 h_sql := 'UPDATE bsc_tabs_b'||
5340 ' SET last_updated_by = :1, last_update_date = SYSDATE'||
5341 ' WHERE tab_id IN ('||
5342 ' SELECT tab_id'||
5343 ' FROM bsc_tab_indicators'||
5344 ' WHERE '||x_condition||')';
5345
5346 h_bind_vars_values.delete;
5347 h_bind_vars_values(1) := h_user_id;
5348 h_num_bind_vars := 1;
5349 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5350
5351 --LOCKING: remove this commit
5352 --COMMIT;
5353
5354 END Update_Kpi_Tab_Time_Stamp;
5355
5356
5357 /*===========================================================================+
5358 | PROCEDURE Update_Kpi_Table_Time_Stamp
5359 +============================================================================*/
5360 PROCEDURE Update_Kpi_Table_Time_Stamp(
5361 x_table_name IN VARCHAR2
5362 ) IS
5363
5364 h_user_id NUMBER;
5365
5366 TYPE t_cursor IS REF CURSOR;
5367 h_cursor t_cursor;
5368 h_sql VARCHAR2(32000);
5369
5370 h_sessionid NUMBER;
5371
5372 BEGIN
5373 h_sessionid := USERENV('SESSIONID');
5374
5375 -- Ref: bug#3482442 In corner cases this query can return more than one
5376 -- row and it will fail. AUDSID is not PK. After meeting with
5377 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5378 h_user_id := BSC_APPS.fnd_global_user_id;
5379
5380 /*
5381 h_sql := 'UPDATE bsc_kpis_b'||
5382 ' SET last_updated_by = :1,'||
5383 ' last_update_date = SYSDATE'||
5384 ' WHERE indicator IN ('||
5385 ' SELECT indicator'||
5386 ' FROM bsc_kpi_data_tables'||
5387 ' WHERE table_name = :2)';
5388 EXECUTE IMMEDIATE h_sql USING h_user_id, x_table_name;
5389 */
5390
5391 UPDATE bsc_kpis_b
5392 SET last_updated_by = h_user_id,
5393 last_update_date = SYSDATE
5394 WHERE indicator IN (
5395 SELECT indicator
5396 FROM bsc_kpi_data_tables
5397 WHERE table_name = x_table_name);
5398
5399 --LOCKING: remove this commit
5400 --COMMIT;
5401
5402 END Update_Kpi_Table_Time_Stamp;
5403
5404
5405 /*===========================================================================+
5406 | PROCEDURE Update_System_Time_Stamp
5407 +============================================================================*/
5408 PROCEDURE Update_System_Time_Stamp IS
5409
5410 h_user_id NUMBER;
5411
5412 TYPE t_cursor IS REF CURSOR;
5413 h_cursor t_cursor;
5414 h_sql VARCHAR2(32000);
5415
5416 h_sessionid NUMBER;
5417
5418 h_lock_property_code VARCHAR2(30);
5419 BEGIN
5420
5421 h_sessionid := USERENV('SESSIONID');
5422 h_lock_property_code := 'LOCK_SYSTEM';
5423
5424
5425 -- Ref: bug#3482442 In corner cases this query can return more than one
5426 -- row and it will fail. AUDSID is not PK. After meeting with
5427 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5428 h_user_id := BSC_APPS.fnd_global_user_id;
5429
5430 /*
5431 h_sql := 'UPDATE bsc_sys_init'||
5432 ' SET last_updated_by = :1,'||
5433 ' last_update_date = SYSDATE'||
5434 ' WHERE property_code = :2';
5435 EXECUTE IMMEDIATE h_sql USING h_user_id, h_lock_property_code;
5436 */
5437 UPDATE bsc_sys_init
5438 SET last_updated_by = h_user_id,
5439 last_update_date = SYSDATE
5440 WHERE property_code = h_lock_property_code;
5441
5442 --LOCKING: remove this commit
5443 --COMMIT;
5444
5445 END Update_System_Time_Stamp;
5446
5447
5448 /*===========================================================================+
5449 | FUNCTION Verify_Custom_Periodicity
5450 +============================================================================*/
5451 FUNCTION Verify_Custom_Periodicity(
5452 x_calendar_id IN NUMBER,
5453 x_periodicity_id IN NUMBER,
5454 x_custom_code IN NUMBER
5455 ) RETURN BOOLEAN IS
5456
5457 TYPE t_cursor IS REF CURSOR;
5458 h_cursor t_cursor;
5459
5460 CURSOR c_missing_years (p_calendar_id NUMBER, p_periodicity_id NUMBER) IS
5461 SELECT DISTINCT c.year
5462 FROM bsc_db_calendar c, bsc_sys_periods p
5463 WHERE c.calendar_id = p_calendar_id AND p_periodicity_id = p.periodicity_id (+) AND
5464 c.year = p.year (+) AND p.year IS NULL;
5465
5466 h_year NUMBER;
5467
5468 CURSOR c_check_fy (p_periodicity_id NUMBER, p_calendar_id NUMBER) IS
5469 SELECT DISTINCT year
5470 FROM bsc_sys_periods
5471 WHERE periodicity_id = p_periodicity_id AND
5472 year = (SELECT fiscal_year
5473 FROM bsc_sys_calendars_b
5474 WHERE calendar_id = p_calendar_id);
5475
5476 h_model_year NUMBER;
5477
5478 CURSOR c_source_periodicity (p_periodicity_id NUMBER) IS
5479 SELECT DECODE(INSTR(source, ','), 0, source, SUBSTR(source, 1, INSTR(source, ',') - 1))
5480 FROM bsc_sys_periodicities
5481 WHERE periodicity_id = p_periodicity_id;
5482
5483 h_source_periodicity NUMBER;
5484
5485 h_db_column_name VARCHAR2(50);
5486 h_db_source_column_name VARCHAR2(50);
5487
5488 h_sql VARCHAR2(32000);
5489
5490 CURSOR c_feb_issue (p_periodicity_id NUMBER, p_year NUMBER, p_in1 NUMBER, p_in2 NUMBER) IS
5491 select p2.period_id, p2.start_date - p1.end_date as issue_type
5492 from bsc_sys_periods p1, bsc_sys_periods p2
5493 where p1.periodicity_id = p2.periodicity_id and
5494 p1.year = p2.year and p1.period_id = p2.period_id - 1 and
5495 p1.periodicity_id = p_periodicity_id and p1.year = p_year and
5496 p2.start_date - p1.end_date IN (p_in1, p_in2);
5497
5498 h_issue_type NUMBER;
5499 h_bad_period NUMBER;
5500
5501 CURSOR c_fix_overlap_period (p_num1 NUMBER, p_periodicity_id NUMBER, p_year NUMBER, p_num2 NUMBER, p_num3 NUMBER) IS
5502 select period_id, abs(p_num1 - period_id) as distance
5503 from bsc_sys_periods
5504 where periodicity_id = p_periodicity_id and year = p_year and
5505 end_date - start_date > p_num2
5506 order by abs(p_num3 - period_id), period_id;
5507
5508 h_fix_overlap_period NUMBER;
5509 h_distance NUMBER;
5510
5511 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5512 h_num_bind_vars NUMBER;
5513
5514 BEGIN
5515 -- Check whether the current fical year exists in BSC_SYS_PERIODS
5516 -- If it exists, then it will be used as the model year.
5517 --OPEN c_check_fy FOR c_check_fy_sql USING x_periodicity_id, x_calendar_id;
5518 OPEN c_check_fy(x_periodicity_id, x_calendar_id);
5519 FETCH c_check_fy INTO h_model_year;
5520 IF c_check_fy%NOTFOUND THEN
5521 h_model_year := 0;
5522 END IF;
5523 CLOSE c_check_fy;
5524
5525 -- Generate automatically information for missing years
5526 -- based on the information for the current fiscal year
5527 -- Note: the information for the current fiscal year
5528 -- MUST exist.
5529 --OPEN c_missing_years FOR c_missing_years_sql USING x_calendar_id, x_periodicity_id;
5530 OPEN c_missing_years(x_calendar_id, x_periodicity_id);
5531 FETCH c_missing_years INTO h_year;
5532 WHILE c_missing_years%FOUND LOOP
5533 -- Get the year to take as a model.
5534 -- Use the current fiscal year if it exists in BSC_SYS_PERIODS
5535 -- Otherwise, use the latest year for which there are records in BSC_SYS_PERIODS
5536 IF h_model_year = 0 THEN
5537 -- The current fiscal year does not exists in BSC_SYS_PERIODS, then
5538 -- we get the latest year.
5539 /*
5540 h_sql := 'SELECT MAX(year)'||
5541 ' FROM bsc_sys_periods'||
5542 ' WHERE periodicity_id = :1';
5543 OPEN h_cursor FOR h_sql USING x_periodicity_id;
5544 FETCH h_cursor INTO h_model_year;
5545 CLOSE h_cursor;
5546 */
5547 SELECT MAX(year)
5548 INTO h_model_year
5549 FROM bsc_sys_periods
5550 WHERE periodicity_id = x_periodicity_id;
5551 END IF;
5552
5553 IF x_custom_code = 1 THEN
5554 -- Based on range of dates --> Use start_date and end_date
5555 /*
5556 h_sql := 'INSERT INTO bsc_sys_periods (
5557 periodicity_id,
5558 year,
5559 period_id,
5560 start_date,
5561 end_date,
5562 start_period,
5563 end_period,
5564 created_by,
5565 creation_date,
5566 last_updated_by,
5567 last_update_date,
5568 last_update_login)
5569 SELECT
5570 p.periodicity_id,
5571 :1,
5572 p.period_id,
5573 add_months(p.start_date, 12*(:2 - :3)),
5574 add_months(p.end_date, 12*(:4 - :5)),
5575 p.start_period,
5576 p.end_period,
5577 p.created_by,
5578 sysdate,
5579 p.last_updated_by,
5580 sysdate,
5581 p.last_update_login
5582 FROM
5583 bsc_sys_periods p
5584 WHERE
5585 periodicity_id = :6 AND
5586 year = :7';
5587
5588 h_bind_vars_values.delete;
5589 h_bind_vars_values(1) := h_year;
5590 h_bind_vars_values(2) := h_year;
5591 h_bind_vars_values(3) := h_model_year;
5592 h_bind_vars_values(4) := h_year;
5593 h_bind_vars_values(5) := h_model_year;
5594 h_bind_vars_values(6) := x_periodicity_id;
5595 h_bind_vars_values(7) := h_model_year;
5596 h_num_bind_vars := 7;
5597 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5598 */
5599 INSERT INTO bsc_sys_periods (
5600 periodicity_id,
5601 year,
5602 period_id,
5603 start_date,
5604 end_date,
5605 start_period,
5606 end_period,
5607 created_by,
5608 creation_date,
5609 last_updated_by,
5610 last_update_date,
5611 last_update_login)
5612 SELECT
5613 p.periodicity_id,
5614 h_year,
5615 p.period_id,
5616 add_months(p.start_date, 12*(h_year - h_model_year)),
5617 add_months(p.end_date, 12*(h_year - h_model_year)),
5618 p.start_period,
5619 p.end_period,
5620 p.created_by,
5621 sysdate,
5622 p.last_updated_by,
5623 sysdate,
5624 p.last_update_login
5625 FROM
5626 bsc_sys_periods p
5627 WHERE
5628 periodicity_id = x_periodicity_id AND
5629 year = h_model_year;
5630
5631
5632 -- Fix the issue with FEB-28 AND FEB-29
5633 -- This issue coul happen because the function add_months in the previous query
5634 -- can automatically convert FEB-28 in FEB-29 or FEB-29 in FEB-28
5635 -- This can generate (rare cases but they could happen) two kind of issues:
5636 -- Example1:
5637 -- Base year 1998:
5638 -- START_DATE END_DATE
5639 -- 01-FEB-1998 27-FEB-1998
5640 -- 28-FEB-1998 28-FEB-1998
5641 -- Records for 1999 resulting in:
5642 -- START_DATE END_DATE
5643 -- 01-FEB-1999 27-FEB-1999
5644 -- 29-FEB-1999 29-FEB-1999 (Wrong!! There is a hole)
5645 -- Example2:
5646 -- Base year 1999:
5647 -- START_DATE END_DATE
5648 -- 01-FEB-1999 28-FEB-1999
5649 -- 29-FEB-1999 29-FEB-1999
5650 -- Records for 1998 resulting in:
5651 -- START_DATE END_DATE
5652 -- 01-FEB-1998 28-FEB-1998
5653 -- 28-FEB-1998 28-FEB-1998 (Wrong!! There is a overlap)
5654 -- Note: I have identified that the issue could happen (not always) when in
5655 -- the base year appears FEB-28 or FEB-29 as START_DATE.
5656 -- There is no issue when FEB-28 or FEB-29 are use as END_DATE: Let's see:
5657 -- One case: Look no 28 or 29 as START_DATE
5658 -- 26 26 26 26
5659 -- 27 28 ----> 27 29 Works!!
5660 -- 01 01 01 01
5661 -- The other case: Look no 28 or 29 as START_DATE
5662 -- 26 26 26 26
5663 -- 27 29 ----> 27 28 Works!!
5664 -- 01 01 01 01
5665
5666 --Find the issue:
5667 -- We know that only is possible to find ONE issue:
5668 -- Overlaping: Type issue = 0
5669 -- Hole: Type issue = 2
5670 --OPEN c_feb_issue FOR c_feb_issue_sql USING x_periodicity_id, h_year, 0, 2;
5671 OPEN c_feb_issue(x_periodicity_id, h_year, 0, 2);
5672 FETCH c_feb_issue INTO h_bad_period, h_issue_type;
5673 IF c_feb_issue%FOUND THEN
5674 IF h_issue_type = 0 THEN
5675 -- Overlap
5676
5677 -- Find the closest period to the period where the issue was found
5678 -- to define the range of affected periods to fix the problem.
5679 --OPEN c_fix_overlap_period FOR c_fix_overlap_period_sql
5680 --USING h_bad_period, x_periodicity_id, h_year, 0, h_bad_period;
5681 OPEN c_fix_overlap_period(h_bad_period, x_periodicity_id, h_year, 0, h_bad_period);
5682 FETCH c_fix_overlap_period INTO h_fix_overlap_period, h_distance;
5683 IF c_fix_overlap_period%FOUND THEN
5684 IF h_fix_overlap_period < h_bad_period THEN
5685 /*
5686 h_sql := 'update bsc_sys_periods'||
5687 ' set'||
5688 ' start_date = DECODE(period_id, :1, start_date, start_date-1),'||
5689 ' end_date = end_date-1'||
5690 ' where'||
5691 ' periodicity_id = :2 and'||
5692 ' year = :3 and'||
5693 ' period_id < :4 and'||
5694 ' period_id >= :5';
5695
5696 h_bind_vars_values.delete;
5697 h_bind_vars_values(1) := h_fix_overlap_period;
5698 h_bind_vars_values(2) := x_periodicity_id;
5699 h_bind_vars_values(3) := h_year;
5700 h_bind_vars_values(4) := h_bad_period;
5701 h_bind_vars_values(5) := h_fix_overlap_period;
5702 h_num_bind_vars := 5;
5703 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5704 */
5705 update bsc_sys_periods
5706 set start_date = DECODE(period_id, h_fix_overlap_period, start_date, start_date-1),
5707 end_date = end_date-1
5708 where periodicity_id = x_periodicity_id and
5709 year = h_year and
5710 period_id < h_bad_period and
5711 period_id >= h_fix_overlap_period;
5712
5713 ELSE
5714 /*
5715 h_sql := 'update bsc_sys_periods'||
5716 ' set'||
5717 ' start_date = start_date+1,'||
5718 ' end_date = DECODE(period_id, :1, end_date, end_date+1)'||
5719 ' where'||
5720 ' periodicity_id = :2 and'||
5721 ' year = :3 and'||
5722 ' period_id <= :4 and'||
5723 ' period_id >= :5';
5724
5725 h_bind_vars_values.delete;
5726 h_bind_vars_values(1) := h_fix_overlap_period;
5727 h_bind_vars_values(2) := x_periodicity_id;
5728 h_bind_vars_values(3) := h_year;
5729 h_bind_vars_values(4) := h_fix_overlap_period;
5730 h_bind_vars_values(5) := h_bad_period;
5731 h_num_bind_vars := 5;
5732 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5733 */
5734 update bsc_sys_periods
5735 set start_date = start_date+1,
5736 end_date = DECODE(period_id, h_fix_overlap_period, end_date, end_date+1)
5737 where periodicity_id = x_periodicity_id and
5738 year = h_year and
5739 period_id <= h_fix_overlap_period and
5740 period_id >= h_bad_period;
5741
5742 END IF;
5743 END IF;
5744 CLOSE c_fix_overlap_period;
5745 END IF;
5746
5747 IF h_issue_type = 2 THEN
5748 --Hole
5749 /*
5750 h_sql := 'update bsc_sys_periods'||
5751 ' set start_date = start_date - 1'||
5752 ' where periodicity_id = :1 and'||
5753 ' year = :2 and period_id = :3';
5754
5755 h_bind_vars_values.delete;
5756 h_bind_vars_values(1) := x_periodicity_id;
5757 h_bind_vars_values(2) := h_year;
5758 h_bind_vars_values(3) := h_bad_period;
5759 h_num_bind_vars := 3;
5760 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5761 */
5762 update bsc_sys_periods
5763 set start_date = start_date - 1
5764 where periodicity_id = x_periodicity_id and
5765 year = h_year and
5766 period_id = h_bad_period;
5767 END IF;
5768 END IF;
5769 CLOSE c_feb_issue;
5770
5771 ELSE
5772 -- Based on other periodicity --> Use start_period and end_period
5773 /*
5774 h_sql := 'INSERT INTO bsc_sys_periods (
5775 periodicity_id,
5776 year,
5777 period_id,
5778 start_date,
5779 end_date,
5780 start_period,
5781 end_period,
5782 created_by,
5783 creation_date,
5784 last_updated_by,
5785 last_update_date,
5786 last_update_login)
5787 SELECT
5788 p.periodicity_id,
5789 :1,
5790 p.period_id,
5791 p.start_date,
5792 p.end_date,
5793 p.start_period,
5794 p.end_period,
5795 p.created_by,
5796 sysdate,
5797 p.last_updated_by,
5798 sysdate,
5799 p.last_update_login
5800 FROM
5801 bsc_sys_periods p
5802 WHERE
5803 periodicity_id = :2 AND
5804 year = :3';
5805
5806 h_bind_vars_values.delete;
5807 h_bind_vars_values(1) := h_year;
5808 h_bind_vars_values(2) := x_periodicity_id;
5809 h_bind_vars_values(3) := h_model_year;
5810 h_num_bind_vars := 3;
5811 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5812 */
5813 INSERT INTO bsc_sys_periods (
5814 periodicity_id,
5815 year,
5816 period_id,
5817 start_date,
5818 end_date,
5819 start_period,
5820 end_period,
5821 created_by,
5822 creation_date,
5823 last_updated_by,
5824 last_update_date,
5825 last_update_login)
5826 SELECT
5827 p.periodicity_id,
5828 h_year,
5829 p.period_id,
5830 p.start_date,
5831 p.end_date,
5832 p.start_period,
5833 p.end_period,
5834 p.created_by,
5835 sysdate,
5836 p.last_updated_by,
5837 sysdate,
5838 p.last_update_login
5839 FROM
5840 bsc_sys_periods p
5841 WHERE
5842 periodicity_id = x_periodicity_id AND
5843 year = h_model_year;
5844
5845 END IF;
5846 FETCH c_missing_years INTO h_year;
5847 END LOOP;
5848 CLOSE c_missing_years;
5849
5850 -- Update the corresponding column in BSC_DB_CALENDAR for this periodicity
5851 h_db_column_name := Get_Calendar_Table_Col_Name(x_periodicity_id);
5852
5853 IF x_custom_code = 1 THEN
5854 -- Based on range of dates --> Use start_date and end_date
5855 h_sql := 'UPDATE
5856 bsc_db_calendar d
5857 SET '||h_db_column_name||' = (
5858 SELECT
5859 p.period_id
5860 FROM
5861 bsc_sys_periods p
5862 WHERE
5863 p.periodicity_id = :1 AND
5864 p.year = d.year AND
5865 TO_DATE(d.calendar_year||''-''||d.calendar_month||''-''||d.calendar_day, ''YYYY-MM-DD'')
5866 BETWEEN p.start_date AND p.end_date
5867 )
5868 WHERE
5869 d.calendar_id = :2';
5870
5871 h_bind_vars_values.delete;
5872 h_bind_vars_values(1) := x_periodicity_id;
5873 h_bind_vars_values(2) := x_calendar_id;
5874 h_num_bind_vars := 2;
5875 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5876
5877 ELSE
5878 -- Based on other periodicity --> Use start_period and end_period
5879 --OPEN c_source_periodicity FOR c_source_periodicity_sql USING x_periodicity_id;
5880 OPEN c_source_periodicity(x_periodicity_id);
5881 FETCH c_source_periodicity INTO h_source_periodicity;
5882 CLOSE c_source_periodicity;
5883
5884 IF h_source_periodicity IS NOT NULL THEN
5885 h_db_source_column_name := Get_Calendar_Table_Col_Name(h_source_periodicity);
5886
5887 h_sql := 'UPDATE
5888 bsc_db_calendar d
5889 SET '||h_db_column_name||' = (
5890 SELECT
5891 p.period_id
5892 FROM
5893 bsc_sys_periods p
5894 WHERE
5895 p.periodicity_id = :1 AND
5896 p.year = d.year AND
5897 d.'||h_db_source_column_name||' BETWEEN p.start_period AND p.end_period
5898 )
5899 WHERE
5900 d.calendar_id = :2';
5901
5902 h_bind_vars_values.delete;
5903 h_bind_vars_values(1) := x_periodicity_id;
5904 h_bind_vars_values(2) := x_calendar_id;
5905 h_num_bind_vars := 2;
5906 Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5907
5908 END IF;
5909 END IF;
5910
5911 RETURN TRUE;
5912 EXCEPTION
5913 WHEN OTHERS THEN
5914 BSC_MESSAGE.Add(x_message => SQLERRM,
5915 x_source => 'BSC_UPDATE_UTIL.Verify_Custom_Periodicity');
5916 RETURN FALSE;
5917 END Verify_Custom_Periodicity;
5918
5919
5920 /*===========================================================================+
5921 | FUNCTION Write_Init_Variable_Value
5922 +============================================================================*/
5923 FUNCTION Write_Init_Variable_Value(
5924 x_variable_name IN VARCHAR2,
5925 x_variable_value IN VARCHAR2
5926 ) RETURN BOOLEAN IS
5927
5928 h_user_id NUMBER;
5929
5930 TYPE t_cursor IS REF CURSOR;
5931 h_cursor t_cursor;
5932 h_sql VARCHAR2(32000);
5933
5934 h_sessionid NUMBER;
5935
5936 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5937 h_num_bind_vars NUMBER;
5938
5939 BEGIN
5940 h_sessionid := USERENV('SESSIONID');
5941
5942 -- Ref: bug#3482442 In corner cases this query can return more than one
5943 -- row and it will fail. AUDSID is not PK. After meeting with
5944 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
5945 h_user_id := BSC_APPS.fnd_global_user_id;
5946
5947 /*
5948 h_sql := 'UPDATE bsc_sys_init'||
5949 ' SET property_value = :1,'||
5950 ' last_updated_by = :2,'||
5951 ' last_update_date = SYSDATE'||
5952 ' WHERE property_code = :3';
5953 EXECUTE IMMEDIATE h_sql USING x_variable_value, h_user_id, x_variable_name;
5954 */
5955 UPDATE bsc_sys_init
5956 SET property_value = x_variable_value,
5957 last_updated_by = h_user_id,
5958 last_update_date = SYSDATE
5959 WHERE property_code = x_variable_name;
5960
5961 IF SQL%NOTFOUND THEN
5962 /*
5963 h_sql := 'INSERT INTO bsc_sys_init (property_code, property_value,'||
5964 ' created_by, creation_date, last_updated_by, last_update_date)'||
5965 ' VALUES (:1, :2, :3, SYSDATE, :4 , SYSDATE)';
5966 EXECUTE IMMEDIATE h_sql USING x_variable_name, x_variable_value, h_user_id, h_user_id;
5967 */
5968 INSERT INTO bsc_sys_init (property_code, property_value,
5969 created_by, creation_date, last_updated_by, last_update_date)
5970 VALUES (x_variable_name, x_variable_value, h_user_id, SYSDATE, h_user_id , SYSDATE);
5971 END IF;
5972
5973 RETURN TRUE;
5974
5975 EXCEPTION
5976 WHEN OTHERS THEN
5977 BSC_MESSAGE.Add(x_message => SQLERRM,
5978 x_source => 'BSC_UPDATE_UTIL.Write_Init_Variable_Value');
5979 RETURN FALSE;
5980
5981 END Write_Init_Variable_Value;
5982
5983 function is_parallel return boolean is
5984 Begin
5985 --Enable parallel
5986 if g_parallel is null then
5987 g_parallel:=true;
5988 end if;
5989 g_parallel:=true;
5990 return g_parallel;
5991 EXCEPTION
5992 WHEN OTHERS THEN
5993 BSC_MESSAGE.Add(x_message => SQLERRM,
5994 x_source => 'BSC_UPDATE_UTIL.is_parallel');
5995 RETURN FALSE;
5996 END is_parallel;
5997
5998
5999 /*******************************************************************************
6000 ********************************************************************************/
6001 FUNCTION get_Product_Version
6002 (
6003 p_Product IN VARCHAR2
6004 ) RETURN VARCHAR2
6005 IS
6006 l_variable BSC_SYS_INIT.Property_Code%TYPE;
6007 l_version BSC_SYS_INIT.Property_Value%TYPE;
6008 BEGIN
6009 l_variable := NULL;
6010 l_version := NULL;
6011
6012 IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013 l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014 ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015 l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016 ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017 l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018 ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019 l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020 END IF;
6021 IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
6022 x_variable_name => l_variable
6023 , x_variable_value => l_version)) THEN
6024 RETURN l_version;
6025 ELSE
6026 RETURN NULL;
6027 END IF;
6028 EXCEPTION
6029 WHEN OTHERS THEN
6030 BSC_MESSAGE.Add
6031 ( x_message => SQLERRM
6032 , x_source => 'BSC_UPDATE_UTIL.get_Product_Version'
6033 );
6034 RETURN NULL;
6035 END get_Product_Version;
6036
6037 /*******************************************************************************
6038 ********************************************************************************/
6039 FUNCTION set_Product_Version
6040 (
6041 p_Product IN VARCHAR2
6042 , p_Version IN VARCHAR2
6043 ) RETURN BOOLEAN
6044 IS
6045 l_variable BSC_SYS_INIT.Property_Code%TYPE;
6046 BEGIN
6047 l_variable := NULL;
6048
6049 IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050 l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051 ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052 l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053 ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054 l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055 ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056 l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057 END IF;
6058 IF(l_variable IS NOT NULL) THEN
6059 RETURN BSC_UPDATE_UTIL.Write_Init_Variable_Value
6060 ( x_variable_name => l_variable
6061 , x_variable_value => p_Version
6062 );
6063 ELSE
6064 RETURN FALSE;
6065 END IF;
6066 EXCEPTION
6067 WHEN OTHERS THEN
6068 BSC_MESSAGE.Add
6069 ( x_message => SQLERRM
6070 , x_source => 'BSC_UPDATE_UTIL.set_Product_Version'
6071 );
6072 RETURN FALSE;
6073 END set_Product_Version;
6074
6075
6076 -- AW_INTEGRATION: New function
6077 /*===========================================================================+
6078 | FUNCTION Is_Table_For_AW_Kpi
6079 +============================================================================*/
6080 FUNCTION Is_Table_For_AW_Kpi(
6081 x_table_name IN VARCHAR2
6082 ) RETURN BOOLEAN IS
6083 h_count NUMBER;
6084 h_aw_kpi_type NUMBER;
6085 h_aw_impl_type_name VARCHAR2(100);
6086 BEGIN
6087 h_count := 0;
6088 h_aw_impl_type_name := 'IMPLEMENTATION_TYPE';
6089 h_aw_kpi_type := 2;
6090
6091 select count(table_name)
6092 into h_count
6093 from (
6094 select distinct table_name
6095 from bsc_db_tables_rels
6096 start with table_name in (
6097 select distinct kd.table_name
6098 from bsc_kpi_data_tables kd, bsc_kpi_properties k
6099 where k.indicator = kd.indicator and
6100 k.property_code = h_aw_impl_type_name and
6101 k.property_value = h_aw_kpi_type and
6102 kd.table_name is not null
6103 )
6104 connect by table_name = prior source_table_name
6105 )
6106 where table_name = x_table_name;
6107
6108 IF h_count > 0 THEN
6109 RETURN TRUE;
6110 ELSE
6111 RETURN FALSE;
6112 END IF;
6113 END Is_Table_For_AW_Kpi;
6114
6115
6116 -- AW_INTEGRATION: New function
6117 /*===========================================================================+
6118 | FUNCTION Get_Kpi_Impl_Type
6119 +============================================================================*/
6120 FUNCTION Get_Kpi_Impl_Type(
6121 x_kpi IN NUMBER
6122 ) RETURN NUMBER IS
6123
6124 CURSOR c_impl_type (p_kpi NUMBER, p_prop_code VARCHAR2) IS
6125 select property_value
6126 from bsc_kpi_properties
6127 where indicator = p_kpi and property_code = p_prop_code;
6128
6129 h_impl_type NUMBER;
6130 h_impl_type_name VARCHAR2(100);
6131
6132 BEGIN
6133 h_impl_type_name := 'IMPLEMENTATION_TYPE';
6134
6135 OPEN c_impl_type(x_kpi, h_impl_type_name);
6136 FETCH c_impl_type INTO h_impl_type;
6137 IF c_impl_type%NOTFOUND THEN
6138 h_impl_type := 1;
6139 END IF;
6140 CLOSE c_impl_type;
6141
6142 RETURN h_impl_type;
6143
6144 END Get_Kpi_Impl_Type;
6145
6146
6147 -- AW_INTEGRATION: New function
6148 /*===========================================================================+
6149 | FUNCTION Exists_AW_Kpi
6150 +============================================================================*/
6151 FUNCTION Exists_AW_Kpi RETURN BOOLEAN IS
6152 h_count NUMBER;
6153 h_aw_kpi_type NUMBER;
6154 h_aw_impl_type_name VARCHAR2(100);
6155 BEGIN
6156 h_count := 0;
6157 h_aw_impl_type_name := 'IMPLEMENTATION_TYPE';
6158 h_aw_kpi_type := 2;
6159
6160 select count(k.indicator)
6161 into h_count
6162 from bsc_kpis_b k, bsc_kpi_properties p
6163 where k.indicator = p.indicator and
6164 k.prototype_flag in (0,6,7) and
6165 p.property_code = h_aw_impl_type_name and
6166 p.property_value = h_aw_kpi_type;
6167
6168 IF h_count > 0 THEN
6169 RETURN TRUE;
6170 ELSE
6171 RETURN FALSE;
6172 END IF;
6173 END Exists_AW_Kpi;
6174
6175
6176 -- AW_INTEGRATION: New function
6177 /*===========================================================================+
6178 | FUNCTION Calendar_Used_In_AW_Kpi
6179 +============================================================================*/
6180 FUNCTION Calendar_Used_In_AW_Kpi(
6181 x_calendar_id IN VARCHAR2
6182 ) RETURN BOOLEAN IS
6183 h_count NUMBER;
6184 h_aw_impl_type NUMBER;
6185 h_aw_impl_type_name VARCHAR2(100);
6186 BEGIN
6187 h_count := 0;
6188 h_aw_impl_type_name := 'IMPLEMENTATION_TYPE';
6189 h_aw_impl_type := 2;
6190
6191 select count(k.calendar_id)
6192 into h_count
6193 from bsc_kpis_vl k, bsc_kpi_properties p
6194 where k.indicator = p.indicator and
6195 k.calendar_id = x_calendar_id and
6196 k.prototype_flag in (0,6,7) and
6197 p.property_code = h_aw_impl_type_name and
6198 p.property_value = h_aw_impl_type;
6199
6200 IF h_count > 0 THEN
6201 RETURN TRUE;
6202 ELSE
6203 RETURN FALSE;
6204 END IF;
6205
6206 END Calendar_Used_In_AW_Kpi;
6207
6208
6209 PROCEDURE Get_Kpi_Dim_Props (
6210 p_objective_id IN NUMBER
6211 , p_kpi_measure_id IN NUMBER
6212 , x_dim_props_rec OUT NOCOPY BSC_UPDATE_UTIL.t_kpi_dim_props_rec
6213 )
6214 IS
6215 CURSOR c_dim_props(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
6216 SELECT ds.dim_set_id dim_set_id,
6217 kpi_dim.level_pk_col comp_level_pk_col
6218 FROM bsc_db_dataset_dim_sets_v ds,
6219 bsc_kpi_dim_levels_vl kpi_dim
6220 WHERE ds.indicator = kpi_dim.indicator(+)
6221 AND ds.dim_set_id = kpi_dim.dim_set_id(+)
6222 AND kpi_dim.default_value(+) = 'C'
6223 AND ds.kpi_measure_id = p_kpi_measure_id
6224 AND ds.indicator = p_indicator;
6225 l_dim_props c_dim_props%ROWTYPE;
6226
6227 BEGIN
6228
6229 IF c_dim_props%ISOPEN THEN
6230 CLOSE c_dim_props;
6231 END IF;
6232 OPEN c_dim_props (p_objective_id, p_kpi_measure_id);
6233 FETCH c_dim_props INTO l_dim_props;
6234 IF c_dim_props%FOUND THEN
6235
6236 x_dim_props_rec.dim_set_id := l_dim_props.dim_set_id;
6237 x_dim_props_rec.comp_level_pk_col := l_dim_props.comp_level_pk_col;
6238
6239 END IF;
6240 CLOSE c_dim_props;
6241
6242 EXCEPTION
6243 WHEN OTHERS THEN
6244 IF c_dim_props%ISOPEN THEN
6245 CLOSE c_dim_props;
6246 END IF;
6247 BSC_MESSAGE.Add(x_message => SQLERRM,
6248 x_source => 'BSC_UPDATE_UTIL.Get_Kpi_Dim_Props');
6249 RAISE;
6250 END Get_Kpi_Dim_Props;
6251
6252
6253 FUNCTION get_kpi_measure_formula (
6254 p_objective_id IN NUMBER
6255 , p_kpi_measure_id IN NUMBER
6256 )
6257 RETURN VARCHAR2 IS
6258 CURSOR c_measure_formula(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
6259 SELECT m1.operation || '(' ||
6260 NVL(BSC_APPS.Get_Property_Value(m1.s_color_formula, 'pFormulaSource'), m1.measure_col) || ')' ||
6261 ds.operation ||
6262 DECODE(ds.measure_id2,
6263 NULL, NULL,
6264 m2.operation || '(' || NVL(BSC_APPS.Get_Property_Value(m2.s_color_formula, 'pFormulaSource'), m2.measure_col) || ')' ) measure_formula
6265 FROM bsc_tab_indicators tab_ind,
6266 bsc_kpi_analysis_measures_b anal_meas,
6267 bsc_sys_datasets_b ds,
6268 bsc_sys_measures m1,
6269 bsc_sys_measures m2,
6270 bsc_kpis_b obj
6271 WHERE anal_meas.dataset_id = ds.dataset_id
6272 AND ds.measure_id1 = m1.measure_id
6273 AND NVL(ds.measure_id2, ds.measure_id1) = m2.measure_id
6274 AND obj.indicator = anal_meas.indicator
6275 AND tab_ind.indicator = obj.indicator
6276 AND anal_meas.kpi_measure_id = p_kpi_measure_id
6277 AND obj.indicator = p_indicator;
6278
6279 l_measure_formula VARCHAR2(4000);
6280
6281 BEGIN
6282
6283 IF c_measure_formula%ISOPEN THEN
6284 CLOSE c_measure_formula;
6285 END IF;
6286 OPEN c_measure_formula (p_objective_id, p_kpi_measure_id);
6287 FETCH c_measure_formula INTO l_measure_formula;
6288 IF c_measure_formula%NOTFOUND THEN
6289 RETURN NULL;
6290 END IF;
6291 CLOSE c_measure_formula;
6292
6293 RETURN l_measure_formula;
6294
6295 EXCEPTION
6296 WHEN OTHERS THEN
6297 IF c_measure_formula%ISOPEN THEN
6298 CLOSE c_measure_formula;
6299 END IF;
6300 BSC_MESSAGE.Add(x_message => SQLERRM,
6301 x_source => 'BSC_UPDATE_UTIL.get_kpi_measure_formula');
6302 RETURN NULL;
6303 END get_kpi_measure_formula;
6304
6305
6306 FUNCTION Get_SimObj_Color_Formula
6307 ( p_objective_id IN NUMBER
6308 , p_kpi_measure_id IN NUMBER
6309 )
6310 RETURN VARCHAR2
6311 IS
6312 CURSOR c_kpi_source(p_indicator NUMBER, p_kpi_measure NUMBER) IS
6313 SELECT ind.measure_type source,
6314 dts.dataset_id
6315 FROM bsc_sys_datasets_b dts,
6316 bis_indicators ind,
6317 bsc_kpi_analysis_measures_b am
6318 WHERE dts.dataset_id = am.dataset_id
6319 AND dts.dataset_id = ind.dataset_id
6320 AND am.indicator = p_indicator
6321 AND am.kpi_measure_id = p_kpi_measure;
6322 l_kpi_source_rec c_kpi_source%ROWTYPE;
6323
6324 l_source bsc_sys_datasets_b.source%TYPE;
6325 l_default_node_dataset_id bsc_sys_datasets_b.dataset_id%TYPE;
6326 l_measure_formula VARCHAR2(4000);
6327
6328 BEGIN
6329
6330 l_measure_formula := NULL;
6331
6332 FOR l_kpi_source_rec IN c_kpi_source(p_objective_id, p_kpi_measure_id) LOOP
6333 -- Ideally only 1 row must be returned since duplicate datasets are not allowed in Simulation Objective
6334 l_source := l_kpi_source_rec.source;
6335 l_default_node_dataset_id := l_kpi_source_rec.dataset_id;
6336 END LOOP;
6337
6338 IF l_source = BSC_SIMULATION_VIEW_PUB.c_CALCULATED_KPI THEN
6339
6340 l_measure_formula := BSC_SIMULATION_VIEW_PUB.Get_Kpi_MeasureCol(l_default_node_dataset_id);
6341
6342 l_measure_formula := BSC_SIMULATION_VIEW_PUB.Get_Formula_Base_Columns ( p_indicator => p_objective_id
6343 , p_dataset_id => l_default_node_dataset_id
6344 , p_meas_col => l_measure_formula
6345 );
6346
6347 ELSE
6348 l_measure_formula := get_kpi_measure_formula ( p_objective_id => p_objective_id
6349 , p_kpi_measure_id => p_kpi_measure_id
6350 );
6351 END IF;
6352
6353 RETURN l_measure_formula;
6354
6355 EXCEPTION
6356 WHEN OTHERS THEN
6357 BSC_MESSAGE.Add(x_message => SQLERRM,
6358 x_source => 'BSC_UPDATE_UTIL.Get_SimObj_Color_Formula');
6359 RETURN NULL;
6360 END Get_SimObj_Color_Formula;
6361
6362
6363 FUNCTION Get_Measure_Formula (
6364 p_objective_id IN NUMBER
6365 , p_kpi_measure_id IN NUMBER := NULL
6366 , p_sim_objective IN BOOLEAN := FALSE
6367 )
6368 RETURN VARCHAR2 IS
6369 l_measure_formula VARCHAR2(4000);
6370 BEGIN
6371
6372 IF NOT p_sim_objective THEN
6373
6374 l_measure_formula := get_kpi_measure_formula ( p_objective_id => p_objective_id
6375 , p_kpi_measure_id => p_kpi_measure_id
6376 );
6377
6378 ELSE
6379
6380 l_measure_formula := Get_SimObj_Color_Formula ( p_objective_id => p_objective_id
6381 , p_kpi_measure_id => p_kpi_measure_id
6382 );
6383
6384 END IF;
6385
6386 RETURN l_measure_formula;
6387
6388 EXCEPTION
6389 WHEN OTHERS THEN
6390 BSC_MESSAGE.Add(x_message => SQLERRM,
6391 x_source => 'BSC_UPDATE_UTIL.Get_Measure_Formula');
6392 RETURN NULL;
6393 END Get_Measure_Formula;
6394
6395
6396 FUNCTION Get_Color_By_Total (
6397 p_objective_id IN NUMBER
6398 , p_kpi_measure_id IN NUMBER := NULL
6399 )
6400 RETURN NUMBER IS
6401
6402 CURSOR c_color_by_total(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
6403 SELECT color_by_total
6404 FROM bsc_kpi_measure_props
6405 WHERE indicator = p_indicator
6406 AND kpi_measure_id = p_kpi_measure_id;
6407
6408 l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;
6409
6410 BEGIN
6411
6412 l_color_by_total := 1;
6413
6414 IF c_color_by_total%ISOPEN THEN
6415 CLOSE c_color_by_total;
6416 END IF;
6417 OPEN c_color_by_total (p_objective_id, p_kpi_measure_id);
6418 FETCH c_color_by_total INTO l_color_by_total;
6419 IF c_color_by_total%NOTFOUND THEN
6420 l_color_by_total := 1;
6421 END IF;
6422 CLOSE c_color_by_total;
6423
6424 RETURN l_color_by_total;
6425
6426 EXCEPTION
6427 WHEN OTHERS THEN
6428 IF c_color_by_total%ISOPEN THEN
6429 CLOSE c_color_by_total;
6430 END IF;
6431 BSC_MESSAGE.Add(x_message => SQLERRM,
6432 x_source => 'BSC_UPDATE_UTIL.Get_Color_By_Total');
6433 RETURN NULL;
6434 END Get_Color_By_Total;
6435
6436
6437 FUNCTION get_ytd_flag (
6438 p_objective_id IN NUMBER
6439 , p_kpi_measure_id IN NUMBER
6440 )
6441 RETURN NUMBER IS
6442
6443 CURSOR c_ytd_flag(p_indicator NUMBER, p_kpi_measure NUMBER) IS
6444 SELECT COUNT(default_calculation) ytd_flag
6445 FROM bsc_kpi_measure_props
6446 WHERE default_calculation = 2
6447 AND kpi_measure_id = p_kpi_measure
6448 AND indicator = p_indicator;
6449
6450 l_ytd_flag NUMBER;
6451
6452 BEGIN
6453
6454 l_ytd_flag := 0;
6455
6456 IF c_ytd_flag%ISOPEN THEN
6457 CLOSE c_ytd_flag;
6458 END IF;
6459 OPEN c_ytd_flag(p_objective_id, p_kpi_measure_id);
6460 FETCH c_ytd_flag INTO l_ytd_flag;
6461 IF c_ytd_flag%NOTFOUND THEN
6462 l_ytd_flag := 0;
6463 END IF;
6464 CLOSE c_ytd_flag;
6465
6466 RETURN l_ytd_flag;
6467
6468 EXCEPTION
6469 WHEN OTHERS THEN
6470 IF c_ytd_flag%ISOPEN THEN
6471 CLOSE c_ytd_flag;
6472 END IF;
6473 BSC_MESSAGE.Add(x_message => SQLERRM,
6474 x_source => 'BSC_UPDATE_UTIL.get_ytd_flag');
6475 RETURN l_ytd_flag;
6476 END get_ytd_flag;
6477
6478
6479 FUNCTION Get_Apply_Color_Flag (
6480 p_objective_id IN NUMBER
6481 , p_kpi_measure_id IN NUMBER := NULL
6482 )
6483 RETURN NUMBER IS
6484
6485 CURSOR c_apply_color_flag(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
6486 SELECT apply_color_flag
6487 FROM bsc_kpi_measure_props
6488 WHERE indicator = p_indicator
6489 AND kpi_measure_id = p_kpi_measure_id;
6490
6491 l_apply_color_flag bsc_kpi_measure_props.apply_color_flag%TYPE;
6492
6493 BEGIN
6494
6495 l_apply_color_flag := 0;
6496
6497 IF c_apply_color_flag%ISOPEN THEN
6498 CLOSE c_apply_color_flag;
6499 END IF;
6500 OPEN c_apply_color_flag (p_objective_id, p_kpi_measure_id);
6501 FETCH c_apply_color_flag INTO l_apply_color_flag;
6502 IF c_apply_color_flag%NOTFOUND THEN
6503 l_apply_color_flag := 0;
6504 END IF;
6505 CLOSE c_apply_color_flag;
6506
6507 RETURN l_apply_color_flag;
6508
6509 EXCEPTION
6510 WHEN OTHERS THEN
6511 IF c_apply_color_flag%ISOPEN THEN
6512 CLOSE c_apply_color_flag;
6513 END IF;
6514 BSC_MESSAGE.Add(x_message => SQLERRM,
6515 x_source => 'BSC_UPDATE_UTIL.Get_Apply_Color_Flag');
6516 RETURN NULL;
6517 END Get_Apply_Color_Flag;
6518
6519
6520 END BSC_UPDATE_UTIL;