[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_BASE_V2
Source
1 PACKAGE BODY BSC_UPDATE_BASE_V2 AS
2 /* $Header: BSCDBV2B.pls 120.12.12000000.2 2007/01/30 10:04:30 rkumar ship $ */
3
4 --
5 -- Package constants
6 --
7
8 -- Formats
9
10 --
11 -- Package variables
12 --
13
14 /*===========================================================================+
15 | FUNCTION Calculate_Base_Table
16 +============================================================================*/
17 FUNCTION Calculate_Base_Table (
18 x_base_table IN VARCHAR2,
19 x_input_table IN VARCHAR2,
20 x_correction_flag IN BOOLEAN,
21 x_aw_flag IN BOOLEAN
22 ) RETURN BOOLEAN IS
23
24 e_error_calc_base_table_v2 EXCEPTION;
25 e_periodicity_null EXCEPTION;
26 e_calc_period_input_table EXCEPTION;
27 e_calc_period_base_table EXCEPTION;
28 e_get_info_data_columns EXCEPTION;
29 e_get_info_key_columns EXCEPTION;
30 e_create_types_for_mv_load EXCEPTION;
31
32 h_j NUMBER;
33
34 h_return_status VARCHAR2(50);
35 h_error_message VARCHAR2(4000);
36
37 h_list dbms_sql.varchar2_table;
38 h_values dbms_sql.varchar2_table;
39
40 h_proj_tbl_name VARCHAR2(30);
41 h_rowid_tbl_name VARCHAR2(30);
42
43 h_partition_info BSC_DBGEN_STD_METADATA.clsTablePartition;
44 h_num_partitions NUMBER;
45 h_partition_names dbms_sql.varchar2_table;
46 h_batch_values dbms_sql.number_table;
47
48 h_parallel_jobs VARCHAR2(1);
49 h_job_name VARCHAR2(100);
50 h_process VARCHAR2(32000);
51 h_job_status bsc_aw_utility.parallel_job_tb;
52
53 h_aw_flag_t VARCHAR2(15);
54 h_correction_flag_t VARCHAR2(15);
55
56 h_change_vector_value NUMBER;
57
58 h_periodicity NUMBER;
59 h_calendar_id NUMBER;
60 h_current_fy NUMBER;
61 h_per_input_table NUMBER;
62 h_current_per_base_table NUMBER;
63 h_per_base_table NUMBER;
64
65 h_sql VARCHAR2(32000);
66 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
67 h_num_bind_vars NUMBER;
68
69 h_num_rows NUMBER;
70 h_num_loads NUMBER;
71
72 h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73 h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
74 h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75 h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76 h_num_data_columns NUMBER;
77
78 h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
80 h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
81 h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
82 h_num_key_columns NUMBER;
83
84 BEGIN
85
86 -- New strategy for better performance. B has only actual data and there is a projection table
87 -- for projection. B and projection tables may be partitioned in which case
88 -- we lauch n number of jobs for each partition.
89
90 h_num_key_columns := 0;
91 h_num_data_columns := 0;
92 h_num_partitions := 0;
93 h_num_bind_vars := 0;
94 h_num_rows := 0;
95 h_num_loads := 0;
96
97
98 -- Get the periodicity of the base table
99 -- Note: By design the periodicity of the input table and the base table are the same
100 h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);
101 IF h_periodicity IS NULL THEN
102 RAISE e_periodicity_null;
103 END IF;
104
105 -- Get the calendar id of the input/base table
106 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
107 -- Get the current fiscal year
108 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
109
110 -- Get the new period of the input table
111 h_per_input_table := 0;
112 -- If the base table is being re-calculated for incremental changes
113 -- then we do not consider the input table to calculate new period of the base table.
114 IF NOT x_correction_flag THEN
115 Calc_New_Period_Input_Table(x_input_table,
116 h_periodicity,
117 h_current_fy,
118 h_per_input_table,
119 h_return_status,
120 h_error_message);
121 IF h_return_status = 'error' THEN
122 RAISE e_calc_period_input_table;
123 END IF;
124 END IF;
125
126 -- Calculate new period of the base table based on the new current period of the input table
127 Calc_New_Period_Base_Table(x_base_table,
128 h_periodicity,
129 h_current_fy,
130 h_per_input_table,
131 h_current_per_base_table,
132 h_per_base_table,
133 h_return_status,
134 h_error_message);
135 IF h_return_status = 'error' THEN
136 RAISE e_calc_period_base_table;
137 END IF;
138
139 -- Get data columns
140 IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
141 h_data_columns,
142 h_data_formulas,
143 h_data_proj_methods,
144 h_data_measure_types,
145 h_num_data_columns) THEN
146 RAISE e_get_info_data_columns;
147 END IF;
148
149 -- Get key columns
150 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
151 h_key_columns,
152 h_key_dim_tables,
153 h_source_columns,
154 h_source_dim_tables,
155 h_num_key_columns) THEN
156 RAISE e_get_info_key_columns;
157 END IF;
158
159 -- Get base table projection table name
160 h_proj_tbl_name := Get_Base_Proj_Tbl_Name(x_base_table);
161
162 -- Get input table row id table name
163 h_list.delete;
164 h_list(1) := BSC_DBGEN_STD_METADATA.BSC_I_ROWID_TABLE;
165 h_values := BSC_DBGEN_METADATA_READER.Get_Table_Properties(x_input_table, h_list);
166 h_rowid_tbl_name := h_values(1);
167
168 -- Get base table partitions
169 h_partition_info := BSC_DBGEN_METADATA_READER.Get_Partition_Info(x_base_table);
170 h_num_partitions := h_partition_info.partition_count;
171 -- Fix bug#4882239 If h_num_partitinos is NULL then assign it to 0
172 IF h_num_partitions IS NULL THEN
173 h_num_partitions := 0;
174 END IF;
175 FOR h_j IN 1..h_partition_info.partition_info.count LOOP
176 h_partition_names(h_j) := h_partition_info.partition_info(h_j).partition_name;
177 h_batch_values(h_j) := h_partition_info.partition_info(h_j).partition_value;
178 END LOOP;
179
180 -- Truncate projection table. It needs to be done here outside each job. We always recalculate projection
181 BSC_UPDATE_UTIL.Truncate_Table(h_proj_tbl_name);
182
183 -- Initialize the row id table. It needs to be done here outside each job.
184 IF NOT x_correction_flag THEN
185 BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);
186 h_sql := 'insert /*+ append';
187 IF BSC_UPDATE_UTIL.is_parallel THEN
188 h_sql := h_sql||' parallel('||h_rowid_tbl_name||')';
189 END IF;
190 h_sql := h_sql||' */ into '||h_rowid_tbl_name||
191 ' select';
192 IF BSC_UPDATE_UTIL.is_parallel THEN
193 h_sql := h_sql||' /*+ parallel('||x_input_table||') */';
194 END IF;
195 h_sql := h_sql||' rowid, trunc((rownum - :1)/ :2)'||
196 ' from '||x_input_table;
197 h_bind_vars_values.delete;
198 h_bind_vars_values(1) := 1;
199 h_bind_vars_values(2) := 1000000;
200 h_num_rows := BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, 2);
201 commit;
202 h_num_loads := trunc((h_num_rows - 1)/1000000);
203
204 END IF;
205
206 -- Create types needed to load input table in MV architecture. Need to be here outside each job.
207 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
208 Create_Types_For_MV_Load(x_base_table, h_key_columns, h_num_key_columns, h_data_columns, h_num_data_columns,
209 h_return_status, h_error_message);
210 IF h_return_status = 'error' THEN
211 RAISE e_create_types_for_mv_load;
212 END IF;
213 END IF;
214
215 --AW_INTEGRATION: init change vector value
216 IF x_aw_flag THEN
217 bsc_aw_load.init_bt_change_vector(x_base_table);
218 bsc_aw_load.init_bt_change_vector(h_proj_tbl_name);
219 h_change_vector_value := bsc_aw_load.get_bt_next_change_vector(x_base_table);
220 ELSE
221 h_change_vector_value := 0;
222 END IF;
223
224 -- Analyze the input table before loading it
225 IF NOT x_correction_flag THEN
226 BSC_BIA_WRAPPER.Analyze_Table(x_input_table);
227 commit;
228 END IF;
229
230 IF h_num_partitions > 1 AND bsc_aw_utility.can_launch_dbms_job(h_num_partitions) = 'Y' THEN
231 h_parallel_jobs := 'Y';
232 bsc_aw_utility.clean_up_jobs('all');
233 IF x_aw_flag THEN
234 h_aw_flag_t := 'TRUE';
235 ELSE
236 h_aw_flag_t := 'FALSE';
237 END IF;
238 IF x_correction_flag THEN
239 h_correction_flag_t := 'TRUE';
240 ELSE
241 h_correction_flag_t := 'FALSE';
242 END IF;
243 FOR h_j IN 1..h_num_partitions LOOP
244 h_job_name := x_base_table||'_P_'||h_j;
245 h_process := 'BSC_UPDATE_BASE_V2.Update_Base_Table_Job('||
246 ''''||x_base_table||''','||
247 ''''||x_input_table||''','||
248 h_correction_flag_t||','||
249 h_aw_flag_t||','||
250 h_change_vector_value||','||
251 h_periodicity||','||
252 h_calendar_id||','||
253 h_current_fy||','||
254 h_per_base_table||','||
255 h_current_per_base_table||','||
256 ''''||h_proj_tbl_name||''','||
257 ''''||h_rowid_tbl_name||''','||
258 ''''||h_partition_names(h_j)||''','||
259 h_batch_values(h_j)||','||
260 h_num_partitions||','||
261 h_num_loads||','||
262 ''''||h_job_name||''''||
263 ');';
264 bsc_aw_utility.start_job(h_job_name, h_j, h_process, null);
265 END LOOP;
266 bsc_aw_utility.wait_on_jobs(null, h_job_status);
267
268 FOR h_j IN 1..h_job_status.count LOOP
269 IF h_job_status(h_j).status = 'error' THEN
270 h_error_message := h_job_status(h_j).message;
271 RAISE e_error_calc_base_table_v2;
272 END IF;
273 END LOOP;
274 ELSE
275 h_parallel_jobs := 'N';
276 Update_Base_Table(x_base_table,
277 x_input_table,
278 x_correction_flag,
279 x_aw_flag,
280 h_change_vector_value,
281 h_periodicity,
282 h_calendar_id,
283 h_current_fy,
284 h_per_base_table,
285 h_current_per_base_table,
286 h_key_columns,
287 h_key_dim_tables,
288 h_num_key_columns,
289 h_data_columns,
290 h_data_formulas,
291 h_data_proj_methods,
292 h_data_measure_types,
293 h_num_data_columns,
294 h_proj_tbl_name,
295 h_rowid_tbl_name,
296 null,
297 null,
298 h_num_partitions,
299 h_num_loads,
300 h_parallel_jobs,
301 h_return_status,
302 h_error_message);
303 IF h_return_status = 'error' THEN
304 RAISE e_error_calc_base_table_v2;
305 END IF;
306 END IF;
307
308 -- In AW architecture we need to update change vector value in aw metadata
309 IF x_aw_flag THEN
310 bsc_aw_load.update_bt_change_vector(x_base_table, h_change_vector_value);
311 bsc_aw_load.update_bt_change_vector(h_proj_tbl_name, h_change_vector_value);
312 commit;
313 END IF;
314
315 -- Store the update period of input table and base table
316 IF NOT x_correction_flag THEN
317 update bsc_db_tables
318 set current_period = h_per_input_table
319 where table_name = x_input_table;
320
321 update bsc_db_tables
322 set current_period = h_per_base_table
323 where table_name = x_base_table;
324
325 commit;
326
327 -- Delete data from input table
328 BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
329 END IF;
330
331 --Fix bug#4962928: add this call
332 IF x_aw_flag THEN
333 BSC_AW_LOAD.update_bt_current_period(x_base_table, h_per_base_table, h_current_fy);
334 commit;
335 END IF;
336
337 COMMIT;
338 RETURN TRUE;
339
340 EXCEPTION
341 WHEN e_error_calc_base_table_v2 THEN
342 ROLLBACK;
343 BSC_MESSAGE.Add(x_message => h_error_message,
344 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
345 RETURN FALSE;
346
347 WHEN e_periodicity_null THEN
348 ROLLBACK;
349 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
350 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
351 RETURN FALSE;
352
353 WHEN e_calc_period_input_table THEN
354 ROLLBACK;
355 BSC_MESSAGE.Add(x_message => h_error_message,
356 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
357 RETURN FALSE;
358
359 WHEN e_calc_period_base_table THEN
360 ROLLBACK;
361 BSC_MESSAGE.Add(x_message => h_error_message,
362 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
363 RETURN FALSE;
364
365 WHEN e_get_info_data_columns THEN
366 ROLLBACK;
367 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
368 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
369 RETURN FALSE;
370
371 WHEN e_get_info_key_columns THEN
372 ROLLBACK;
373 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
374 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
375 RETURN FALSE;
376
377 WHEN e_create_types_for_mv_load THEN
378 ROLLBACK;
379 BSC_MESSAGE.Add(x_message => h_error_message,
380 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
381 RETURN FALSE;
382
383 WHEN OTHERS THEN
384 ROLLBACK;
385 BSC_MESSAGE.Add(x_message => SQLERRM,
386 x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
387 RETURN FALSE;
388
389 END Calculate_Base_Table;
390
391 /*===========================================================================+
392 | FUNCTION Calculate_Base_Table_AT
393 +============================================================================*/
394 FUNCTION Calculate_Base_Table_AT (
395 x_base_table IN VARCHAR2,
396 x_input_table IN VARCHAR2,
397 x_correction_flag IN BOOLEAN,
398 x_aw_flag IN BOOLEAN
399 ) RETURN BOOLEAN IS
400 PRAGMA AUTONOMOUS_TRANSACTION;
401 h_b BOOLEAN;
402 BEGIN
403 h_b := Calculate_Base_Table(x_base_table, x_input_table, x_correction_flag, x_aw_flag);
404 commit; -- all autonomous transaction needs to commit
405 RETURN h_b;
406 END Calculate_Base_Table_AT;
407
408
409 /*===========================================================================+
410 | PROCEDURE Calc_New_Period_Input_Table
411 +============================================================================*/
412 PROCEDURE Calc_New_Period_Input_Table(
413 x_input_table IN VARCHAR2,
414 x_periodicity IN NUMBER,
415 x_current_fy IN NUMBER,
416 x_period OUT NOCOPY NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_error_message OUT NOCOPY VARCHAR2
419 ) IS
420
421 TYPE t_cursor IS REF CURSOR;
422 h_cursor t_cursor;
423 h_sql VARCHAR2(32700);
424
425 h_current_period NUMBER;
426 h_reported_period NUMBER;
427
428 h_yearly_flag NUMBER;
429 h_target_flag NUMBER;
430 h_calendar_id NUMBER;
431 h_calendar_source VARCHAR2(20);
432 h_periodicity_type NUMBER;
433
434 BEGIN
435 h_reported_period := 0;
436 h_yearly_flag := 0;
437 h_calendar_id := NULL;
438 h_calendar_source := NULL;
439
440 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
441 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
442
443 -- Get Target_Flag of the input table
444 h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
445
446 -- Get the current period of the input table
447 BEGIN
448 SELECT NVL(current_period, 0)
449 INTO h_current_period
450 FROM bsc_db_tables
451 WHERE table_name = x_input_table;
452 EXCEPTION
453 WHEN NO_DATA_FOUND THEN
454 h_current_period := 0;
455 END;
456
457 -- Get yearly flag of the periodicity
458 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
459
460 -- Get the maximun period of real data reported in the input table
461 IF h_yearly_flag = 1 THEN -- Annually
462 -- The update period of an annual table is always the current
463 -- fiscal year
464 h_reported_period := x_current_fy;
465 ELSE -- Other periodicities
466 IF h_calendar_source = 'BSC' THEN
467 h_sql := 'SELECT MAX(PERIOD) '||
468 'FROM '||x_input_table||' '||
469 'WHERE year = :1';
470
471 IF h_target_flag = 1 THEN
472 -- The input tables is used only for targets only
473 -- No condition on TYPE to get the update period of the input table.
474 OPEN h_cursor FOR h_sql USING x_current_fy;
475 ELSE
476 -- The input table is for fact and target data
477 -- The update period is calculated based on fact data only.
478 h_sql := h_sql||' AND type = :2';
479 OPEN h_cursor FOR h_sql USING x_current_fy, 0;
480 END IF;
481 ELSE
482 -- BIS periodicity
483 --BSC-BIS-DIMENSIONS: The input table has a column called TIME_FK instead of YEAR, PERIOD
484
485 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
486
487 IF h_periodicity_type = 9 THEN
488 -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
489 h_sql := 'SELECT MAX(p.period_id)'||
490 ' FROM '||x_input_table||' i, bsc_sys_periods p'||
491 ' WHERE p.periodicity_id = :1 AND TRUNC(i.time_fk) = TRUNC(TO_DATE(p.time_fk, ''MM/DD/YYYY''))'||
492 ' AND p.year =:2';
493 ELSE
494 -- Other periodicity. TIME_FK is VARCHAR2
495 h_sql := 'SELECT MAX(p.period_id)'||
496 ' FROM '||x_input_table||' i, bsc_sys_periods p'||
497 ' WHERE p.periodicity_id = :1 AND i.time_fk = p.time_fk AND p.year =:2';
498 END IF;
499
500 IF h_target_flag = 1 THEN
501 -- The input tables is used only for targets only
502 -- No condition on TYPE to get the update period of the input table.
503 OPEN h_cursor FOR h_sql USING x_periodicity, x_current_fy;
504 ELSE
505 -- The input table is for fact and target data
506 -- The update period is calculated based on fact data only.
507 h_sql := h_sql||' AND type = :3';
508 OPEN h_cursor FOR h_sql USING x_periodicity, x_current_fy, 0;
509 END IF;
510 END IF;
511
512 FETCH h_cursor INTO h_reported_period;
513 IF h_cursor%FOUND THEN
514 IF h_reported_period IS NULL THEN
515 h_reported_period := 0;
516 END IF;
517 ELSE
518 h_reported_period := 0;
519 END IF;
520 CLOSE h_cursor;
521 END IF;
522
523 -- Assign the new update period
524 IF h_reported_period > h_current_period THEN
525 x_period := h_reported_period;
526 ELSE
527 x_period := h_current_period;
528 END IF;
529
530 x_return_status := 'success';
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 x_return_status := 'error';
535 x_error_message := 'Calc_New_Period_Input_Table.e_others: '||SQLERRM;
536
537 END Calc_New_Period_Input_Table;
538
539
540 /*===========================================================================+
541 | PROCEDURE Calc_New_Period_Base_Table
542 +============================================================================*/
543 PROCEDURE Calc_New_Period_Base_Table(
544 x_base_table IN VARCHAR2,
545 x_periodicity IN NUMBER,
546 x_current_fy IN NUMBER,
547 x_per_input_table IN NUMBER,
548 x_current_per_base_table OUT NOCOPY NUMBER,
549 x_per_base_table OUT NOCOPY NUMBER,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_error_message OUT NOCOPY VARCHAR2
552 ) IS
553
554 TYPE t_cursor IS REF CURSOR;
555 h_cursor t_cursor;
556 h_current_period NUMBER;
557 h_calendar_id NUMBER;
558 h_base_calendar_col_name VARCHAR2(30);
559 h_yearly_flag NUMBER;
560 h_edw_flag NUMBER;
561
562 BEGIN
563
564 h_yearly_flag := 0;
565 h_edw_flag := 0;
566
567 -- Get the current period of the base table
568 BEGIN
569 SELECT NVL(current_period, 0)
570 INTO h_current_period
571 FROM bsc_db_tables
572 WHERE table_name = x_base_table;
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN
575 h_current_period := 0;
576 END;
577
578 -- Calculate the period of the base table based on
579 -- the period of the input table
580
581 -- By design we know that there is no change of periodicity between input and base table
582 x_per_base_table := x_per_input_table;
583
584 -- The update period of the base table is the maximun between the current
585 -- period and the period calculated from the input table,
586 IF h_current_period > x_per_base_table THEN
587 x_per_base_table := h_current_period;
588 END IF;
589
590 x_current_per_base_table := h_current_period;
591
592 x_return_status := 'success';
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 x_return_status := 'error';
597 x_error_message := 'Calc_New_Period_Base_Table.e_others: '||SQLERRM;
598
599 END Calc_New_Period_Base_Table;
600
601
602 /*===========================================================================+
603 | PROCEDURE Calc_Projection
604 +============================================================================*/
605 PROCEDURE Calc_Projection(
606 x_base_table IN VARCHAR2,
607 x_proj_table IN VARCHAR2,
608 x_aw_flag IN BOOLEAN,
609 x_change_vector_value IN NUMBER,
610 x_periodicity IN NUMBER,
611 x_calendar_id IN NUMBER,
612 x_current_period IN NUMBER,
613 x_current_fy IN NUMBER,
614 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
615 x_num_key_columns IN NUMBER,
616 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
617 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
618 x_num_data_columns IN NUMBER,
619 x_partition_name IN VARCHAR2,
620 x_batch_value IN NUMBER,
621 x_parallel_jobs IN VARCHAR2,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_error_message OUT NOCOPY VARCHAR2
624 ) IS
625
626 --h_sql clob;
627 h_sql dbms_sql.varchar2A;
628 --h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
629 h_bind_vars_values dbms_sql.varchar2_table;
630 h_num_bind_vars NUMBER;
631 h_i NUMBER;
632 h_j NUMBER;
633 h_many_methods BOOLEAN;
634 h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
635 h_num_avg_cols NUMBER;
636 h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
637 h_num_perf_cols NUMBER;
638 h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
639 h_num_custom_cols NUMBER;
640 h_num_proj_cols NUMBER;
641
642 h_yearly_flag NUMBER;
643 h_per_column VARCHAR2(100);
644 h_num_of_years NUMBER;
645 h_previous_years NUMBER;
646 h_min_year NUMBER;
647 h_max_year NUMBER;
648 h_per_ini NUMBER;
649 h_year_ini NUMBER;
650 h_num_per_back NUMBER;
651 h_init_per NUMBER;
652 h_end_per NUMBER;
653
654 BEGIN
655 h_num_avg_cols := 0;
656 h_num_perf_cols := 0;
657 h_num_custom_cols := 0;
658 h_num_proj_cols := 0;
659 h_num_bind_vars := 0;
660
661 FOR h_i IN 1..x_num_data_columns LOOP
662 IF x_data_proj_methods(h_i) = 1 THEN
663 -- average last year method
664 h_num_avg_cols := h_num_avg_cols + 1;
665 h_avg_cols(h_num_avg_cols) := x_data_columns(h_i);
666 ELSIF x_data_proj_methods(h_i) = 3 THEN
667 -- 3 periods performance method
668 h_num_perf_cols := h_num_perf_cols + 1;
669 h_perf_cols(h_num_perf_cols) := x_data_columns(h_i);
670 h_num_proj_cols := h_num_proj_cols + 1;
671 ELSIF x_data_proj_methods(h_i) = 4 THEN
672 -- Custom projection
673 h_num_custom_cols := h_num_custom_cols + 1;
674 h_custom_cols(h_num_custom_cols) := x_data_columns(h_i);
675 h_num_proj_cols := h_num_proj_cols + 1;
676 END IF;
677 END LOOP;
678
679 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
680 h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
681 IF h_yearly_flag = 1 THEN
682 IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
683 h_num_of_years := 2;
684 h_previous_years := 1;
685 END IF;
686 h_min_year := x_current_fy - h_previous_years;
687 h_max_year := h_min_year + h_num_of_years - 1;
688 END IF;
689
690 -- Calculate projection for data columns with projection method 3 (performance) and 4 (custom)
691 -- Moving Average cannot be calculated with a single query so it is calculated later
692 IF (h_num_perf_cols > 0) OR (h_num_custom_cols > 0) THEN
693 IF (h_num_proj_cols = h_num_perf_cols) or (h_num_proj_cols = h_num_custom_cols) THEN
694 h_many_methods := FALSE;
695 ELSE
696 h_many_methods := TRUE;
697 END IF;
698
699 bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
700 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
701 bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702 END IF;
703 bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704 IF x_parallel_jobs = 'Y' THEN
705 bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
706 END IF;
707 bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
708 FOR h_i IN 1..x_num_key_columns LOOP
709 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
710 END LOOP;
711 bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
712 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
713 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
714 END IF;
715 FOR h_i IN 1..x_num_data_columns LOOP
716 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
717 END LOOP;
718 IF x_aw_flag THEN
719 bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
720 END IF;
721 bsc_dbgen_utils.add_string(h_sql, ')');
722 IF h_many_methods THEN
723 bsc_dbgen_utils.add_string(h_sql, ' select');
724 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726 END IF;
727 bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728 FOR h_i IN 1..x_num_key_columns LOOP
729 bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
730 END LOOP;
731 bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
732 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
733 bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
734 END IF;
735 FOR h_i IN 1..x_num_data_columns LOOP
736 bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||')');
737 END LOOP;
738 IF x_aw_flag THEN
739 bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
740 END IF;
741 bsc_dbgen_utils.add_string(h_sql, ' from (');
742 END IF;
743 IF h_num_perf_cols > 0 THEN
744 -- all measures with this projection method goes here other goes with null
745 h_num_per_back := 3;
746 IF h_yearly_flag = 1 THEN
747 h_year_ini := x_current_fy - h_num_per_back + 1;
748 ELSE
749 h_per_ini := x_current_period - h_num_per_back + 1;
750 IF h_per_ini <= 0 THEN
751 h_per_ini := 1;
752 END IF;
753 END IF;
754 bsc_dbgen_utils.add_string(h_sql, ' select');
755 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
756 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757 END IF;
758 bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759 FOR h_i IN 1..x_num_key_columns LOOP
760 bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
761 END LOOP;
762 bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
763 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
764 bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
765 END IF;
766 FOR h_i IN 1..x_num_data_columns LOOP
767 IF x_data_proj_methods(h_i) = 3 THEN
768 bsc_dbgen_utils.add_string(h_sql,
769 ', case when (tr.tr'||h_i||'>0 and tp.tp'||h_i||'>0) and ((decode(tp.tp'||h_i||',0,'||
770 '0,tr.tr'||h_i||'/tp.tp'||h_i||')*pp.p'||h_i||')>(2*pp.p'||h_i||'))'||
771 ' then 2*pp.p'||h_i||
772 ' when tr.tr'||h_i||'>0 and tp.tp'||h_i||'>0'||
773 ' then (tr.tr'||h_i||'/tp.tp'||h_i||')*pp.p'||h_i||
774 ' when ((tr.tr'||h_i||'<0 and tp.tp'||h_i||'<0) or (tr.tr'||h_i||'<0 and tp.tp'||h_i||'>0)'||
775 ' or (tr.tr'||h_i||'>0 and tp.tp'||h_i||'<0)) and (pp.p'||h_i||'=0 or (pp.p'||h_i||' is null))'||
776 ' then (tr.tr'||h_i||'-tp.tp'||h_i||')/3'||
777 ' when ((tr.tr'||h_i||'<0 and tp.tp'||h_i||'<0) or (tr.tr'||h_i||'<0 and tp.tp'||h_i||'>0)'||
778 ' or (tr.tr'||h_i||'>0 and tp.tp'||h_i||'<0)) and not(pp.p'||h_i||'=0 or (pp.p'||h_i||' is null))'||
779 ' then pp.p'||h_i||'+((tr.tr'||h_i||'-tp.tp'||h_i||')/3)'||
780 ' when (tr.tr'||h_i||'<>0 and (tp.tp'||h_i||'=0 or (tp.tp'||h_i||' is null)))'||
781 ' and (pp.p'||h_i||'=0 or (pp.p'||h_i||' is null))'||
782 ' then tr.tr'||h_i||'/3'||
783 ' when (tr.tr'||h_i||'<>0 and (tp.tp'||h_i||'=0 or (tp.tp'||h_i||' is null)))'||
784 ' and not(pp.p'||h_i||'=0 or (pp.p'||h_i||' is null))'||
785 ' then pp.p'||h_i||'+(tr.tr'||h_i||'/3)'||
786 ' when ((tr.tr'||h_i||'=0 or (tr.tr'||h_i||' is null)) and (tp.tp'||h_i||'=0 or'||
787 ' (tp.tp'||h_i||' is null)))'||
788 ' then pp.p'||h_i||' end '||x_data_columns(h_i));
789 ELSE
790 bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
791 END IF;
792 END LOOP;
793 IF x_aw_flag THEN
794 bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
795 END IF;
796 bsc_dbgen_utils.add_string(h_sql, ' from (select');
797 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799 END IF;
800 bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801 FOR h_i IN 1..x_num_key_columns LOOP
802 bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
803 END LOOP;
804 bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year, 0 type, bsc_tmp_all_periods.period');
805 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806 bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807 END IF;
808 bsc_dbgen_utils.add_string(h_sql, ' from (select');
809 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811 END IF;
812 bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813 FOR h_i IN 1..x_num_key_columns LOOP
814 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
815 END LOOP;
816 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
817 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
818 bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
819 IF x_parallel_jobs = 'Y' THEN
820 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
821 END IF;
822 ELSE
823 IF x_parallel_jobs = 'Y' THEN
824 bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
825 END IF;
826 END IF;
827 bsc_dbgen_utils.add_string(h_sql, ') a,');
828 IF h_yearly_flag <> 1 THEN
829 bsc_dbgen_utils.add_string(h_sql, ' (select');
830 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
832 END IF;
833 bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
834 ' from bsc_db_calendar'||
835 ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
836 ' and '||h_per_column||' > '||x_current_period||
837 ' ) bsc_tmp_all_periods');
838 ELSE
839 bsc_dbgen_utils.add_string(h_sql, ' (select');
840 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
841 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
842 END IF;
843 bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
844 ' from bsc_db_calendar'||
845 ' where year > '||x_current_fy||' and year <= '||h_max_year||
846 ' and calendar_id = '||x_calendar_id||
847 ' ) bsc_tmp_all_periods');
848 END IF;
849 bsc_dbgen_utils.add_string(h_sql, ' ) p,'||
850 ' (select ');
851 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
852 bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
853 END IF;
854 FOR h_i IN 1..x_num_key_columns LOOP
855 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
856 END LOOP;
857 bsc_dbgen_utils.add_string(h_sql, '0 type');
858 FOR h_i IN 1..x_num_data_columns LOOP
859 IF x_data_proj_methods(h_i) = 3 THEN
860 bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tp'||h_i);
861 END IF;
862 END LOOP;
863 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
864 IF h_yearly_flag <> 1 THEN
865 bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and'||
866 ' period >= '||h_per_ini||' and period <= '||x_current_period);
867 ELSE
868 bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 1');
869 END IF;
870 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
871 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
872 END IF;
873 IF x_parallel_jobs = 'Y' THEN
874 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
875 END IF;
876 IF x_num_key_columns > 0 THEN
877 bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
878 FOR h_i IN 2..x_num_key_columns LOOP
879 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
880 END LOOP;
881 END IF;
882 bsc_dbgen_utils.add_string(h_sql, ') tp,'||
883 ' (select ');
884 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
885 bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
886 END IF;
887 FOR h_i IN 1..x_num_key_columns LOOP
888 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
889 END LOOP;
890 bsc_dbgen_utils.add_string(h_sql, '0 type');
891 FOR h_i IN 1..x_num_data_columns LOOP
892 IF x_data_proj_methods(h_i) = 3 THEN
893 bsc_dbgen_utils.add_string(h_sql, ', sum('||x_data_columns(h_i)||') tr'||h_i);
894 END IF;
895 END LOOP;
896 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
897 IF h_yearly_flag <> 1 THEN
898 bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 0 and'||
899 ' period >= '||h_per_ini||' and period <= '||x_current_period);
900 ELSE
901 bsc_dbgen_utils.add_string(h_sql, ' year >= '||h_year_ini||' and year <= '||x_current_fy||' and type = 0');
902 END IF;
903 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
904 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
905 END IF;
906 IF x_parallel_jobs = 'Y' THEN
907 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
908 END IF;
909 IF x_num_key_columns > 0 THEN
910 bsc_dbgen_utils.add_string(h_sql, ' group by '||x_key_columns(1));
911 FOR h_i IN 2..x_num_key_columns LOOP
912 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
913 END LOOP;
914 END IF;
915 bsc_dbgen_utils.add_string(h_sql, ') tr,'||
916 ' (select ');
917 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
918 bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
919 END IF;
920 FOR h_i IN 1..x_num_key_columns LOOP
921 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');
922 END LOOP;
923 bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
924 FOR h_i IN 1..x_num_data_columns LOOP
925 IF x_data_proj_methods(h_i) = 3 THEN
926 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' p'||h_i);
927 END IF;
928 END LOOP;
929 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||' where');
930 IF h_yearly_flag <> 1 THEN
931 bsc_dbgen_utils.add_string(h_sql, ' year = '||x_current_fy||' and type = 1 and period > '||x_current_period);
932 ELSE
933 bsc_dbgen_utils.add_string(h_sql, ' year > '||x_current_fy||' and year <= '||h_max_year||' and type = 1');
934 END IF;
935 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
936 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
937 END IF;
938 IF x_parallel_jobs = 'Y' THEN
939 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
940 END IF;
941 bsc_dbgen_utils.add_string(h_sql, ') pp'||
942 ' where');
943 FOR h_i IN 1..x_num_key_columns LOOP
944 bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tp.key'||h_i||'(+) and');
945 END LOOP;
946 bsc_dbgen_utils.add_string(h_sql, ' p.type = tp.type(+) and');
947 FOR h_i IN 1..x_num_key_columns LOOP
948 bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = tr.key'||h_i||'(+) and');
949 END LOOP;
950 bsc_dbgen_utils.add_string(h_sql, ' p.type = tr.type (+) and');
951 FOR h_i IN 1..x_num_key_columns LOOP
952 bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = pp.key'||h_i||'(+) and');
953 END LOOP;
954 bsc_dbgen_utils.add_string(h_sql, ' p.year = pp.year (+) and'||
955 ' p.type = pp.type (+) and p.period = pp.period (+)');
956 END IF;
957 IF h_num_custom_cols > 0 THEN
958 IF h_num_perf_cols > 0 THEN
959 bsc_dbgen_utils.add_string(h_sql, ' UNION ALL');
960 END IF;
961
962 -- all measures with this projection method goes here other goes with null
963 bsc_dbgen_utils.add_string(h_sql, ' select');
964 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
965 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966 END IF;
967 bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968 FOR h_i IN 1..x_num_key_columns LOOP
969 bsc_dbgen_utils.add_string(h_sql, 'p.key'||h_i||', ');
970 END LOOP;
971 bsc_dbgen_utils.add_string(h_sql, 'p.year, p.type, p.period');
972 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
973 bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
974 END IF;
975 FOR h_i IN 1..x_num_data_columns LOOP
976 IF x_data_proj_methods(h_i) = 4 THEN
977 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
978 ELSE
979 bsc_dbgen_utils.add_string(h_sql, ', null '||x_data_columns(h_i));
980 END IF;
981 END LOOP;
982 IF x_aw_flag THEN
983 bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
984 END IF;
985 bsc_dbgen_utils.add_string(h_sql, ' from (select');
986 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988 END IF;
989 bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990 FOR h_i IN 1..x_num_key_columns LOOP
991 bsc_dbgen_utils.add_string(h_sql, 'a.'||x_key_columns(h_i)||' key'||h_i||', ');
992 END LOOP;
993 bsc_dbgen_utils.add_string(h_sql, 'bsc_tmp_all_periods.year year, 0 type, bsc_tmp_all_periods.period period');
994 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995 bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996 END IF;
997 bsc_dbgen_utils.add_string(h_sql, ' from (select');
998 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000 END IF;
1001 bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002 FOR h_i IN 1..x_num_key_columns LOOP
1003 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1004 END LOOP;
1005 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table);
1006 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1007 bsc_dbgen_utils.add_string(h_sql, ' where periodicity_id = '||x_periodicity);
1008 IF x_parallel_jobs = 'Y' THEN
1009 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1010 END IF;
1011 ELSE
1012 IF x_parallel_jobs = 'Y' THEN
1013 bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1014 END IF;
1015 END IF;
1016 bsc_dbgen_utils.add_string(h_sql, ') a,');
1017 IF h_yearly_flag <> 1 THEN
1018 bsc_dbgen_utils.add_string(h_sql, ' (select');
1019 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1021 END IF;
1022 bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
1023 ' from bsc_db_calendar'||
1024 ' where year = '||x_current_fy||' and calendar_id = '||x_calendar_id||
1025 ' and '||h_per_column||' > '||x_current_period||
1026 ' ) bsc_tmp_all_periods');
1027 ELSE
1028 bsc_dbgen_utils.add_string(h_sql, ' (select');
1029 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1030 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1031 END IF;
1032 bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
1033 ' from bsc_db_calendar'||
1034 ' where year > '||x_current_fy||' and year <= '||h_max_year||
1035 ' and calendar_id = '||x_calendar_id||
1036 ' ) bsc_tmp_all_periods');
1037 END IF;
1038 bsc_dbgen_utils.add_string(h_sql, ') p,'||
1039 ' (select ');
1040 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1041 bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
1042 END IF;
1043 FOR h_i IN 1..x_num_key_columns LOOP
1044 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1045 END LOOP;
1046 bsc_dbgen_utils.add_string(h_sql, 'year, 0 type, period');
1047 FOR h_i IN 1..h_num_custom_cols LOOP
1048 bsc_dbgen_utils.add_string(h_sql, ', '||h_custom_cols(h_i));
1049 END LOOP;
1050 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1051 ' where type = 90');
1052 IF h_yearly_flag <> 1 THEN
1053 bsc_dbgen_utils.add_string(h_sql, ' and year = '||x_current_fy||' and period > '||x_current_period);
1054 ELSE
1055 bsc_dbgen_utils.add_string(h_sql, ' and year > '||x_current_fy||' and year <= '||h_max_year);
1056 END IF;
1057 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1058 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = '||x_periodicity);
1059 END IF;
1060 IF x_parallel_jobs = 'Y' THEN
1061 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = '||x_batch_value);
1062 END IF;
1063 bsc_dbgen_utils.add_string(h_sql, ') b'||
1064 ' where');
1065 FOR h_i IN 1..x_num_key_columns LOOP
1066 bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||' = b.'||x_key_columns(h_i)||'(+) and');
1067 END LOOP;
1068 bsc_dbgen_utils.add_string(h_sql, ' p.year = b.year(+) and p.type = b.type(+) and'||
1069 ' p.period = b.period(+)');
1070 END IF;
1071 IF h_many_methods THEN
1072 bsc_dbgen_utils.add_string(h_sql, ') p'||
1073 ' group by p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||',');
1074 FOR h_i IN 1..x_num_key_columns LOOP
1075 bsc_dbgen_utils.add_string(h_sql, ' p.key'||h_i||',');
1076 END LOOP;
1077 bsc_dbgen_utils.add_string(h_sql, ' p.year, p.type, p.period');
1078 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1079 bsc_dbgen_utils.add_string(h_sql, ', p.periodicity_id');
1080 END IF;
1081 --Fix bug#5155388
1082 IF x_aw_flag THEN
1083 bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
1084 END IF;
1085 END IF;
1086 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1087 BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
1088 commit;
1089 END IF;
1090
1091 -- Now calculate projection for data columns with projection method 1 (moving average)
1092 --Fix bug#5155388
1093 h_sql.delete;
1094
1095 IF h_num_avg_cols > 0 THEN
1096 IF h_yearly_flag = 1 THEN
1097 h_init_per := x_current_fy + 1;
1098 h_end_per := h_max_year;
1099 ELSE
1100 h_init_per := x_current_period + 1;
1101 h_end_per := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
1102 END IF;
1103 IF (h_num_perf_cols > 0) OR (h_num_custom_cols > 0) THEN
1104 -- There are rows in the projection table already. So we need to update.
1105 bsc_dbgen_utils.add_string(h_sql, 'update '||x_proj_table||' p'||
1106 ' set ('||h_avg_cols(1));
1107 FOR h_i IN 2..h_num_avg_cols LOOP
1108 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1109 END LOOP;
1110 bsc_dbgen_utils.add_string(h_sql, ') = ('||
1111 ' select ');
1112 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1113 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114 END IF;
1115 bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116 FOR h_i IN 2..h_num_avg_cols LOOP
1117 bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1118 END LOOP;
1119 bsc_dbgen_utils.add_string(h_sql, ' from ('||
1120 ' select ');
1121 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1122 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
1123 END IF;
1124 FOR h_i IN 1..x_num_key_columns LOOP
1125 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1126 END LOOP;
1127 bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1128 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1129 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1130 END IF;
1131 FOR h_i IN 1..h_num_avg_cols LOOP
1132 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1133 END LOOP;
1134 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1135 ' where type = :1');
1136 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1137 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :2');
1138 END IF;
1139 IF x_parallel_jobs = 'Y' THEN
1140 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :3');
1141 END IF;
1142 bsc_dbgen_utils.add_string(h_sql, ' union all'||
1143 ' select ');
1144 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1145 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1146 END IF;
1147 FOR h_i IN 1..x_num_key_columns LOOP
1148 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1149 END LOOP;
1150 bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1151 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1152 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1153 END IF;
1154 FOR h_i IN 1..h_num_avg_cols LOOP
1155 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1156 END LOOP;
1157 bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1158 ' WHERE type = :4');
1159 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1160 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :5');
1161 END IF;
1162 IF x_parallel_jobs = 'Y' THEN
1163 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6');
1164 END IF;
1165 bsc_dbgen_utils.add_string(h_sql, ') u'||
1166 ' where');
1167 IF h_yearly_flag = 1 THEN
1168 bsc_dbgen_utils.add_string(h_sql, ' (u.year between :7 AND :8)');
1169 ELSE
1170 bsc_dbgen_utils.add_string(h_sql, ' (u.year * 1000 + u.period) between (:7 * 1000 + :8) and (:9 * 1000 + :10)');
1171 END IF;
1172 FOR h_i IN 1..x_num_key_columns LOOP
1173 bsc_dbgen_utils.add_string(h_sql, ' and p.'||x_key_columns(h_i)||' = u.'||x_key_columns(h_i));
1174 END LOOP;
1175 bsc_dbgen_utils.add_string(h_sql, ' and p.type = u.type');
1176 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1177 bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = u.periodicity_id');
1178 END IF;
1179 bsc_dbgen_utils.add_string(h_sql, ')');
1180 IF h_yearly_flag = 1 THEN
1181 bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11');
1182 ELSE
1183 bsc_dbgen_utils.add_string(h_sql, ' where p.year = :11 and p.period = :12');
1184 END IF;
1185 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1186 bsc_dbgen_utils.add_string(h_sql, ' and p.periodicity_id = :13');
1187 END IF;
1188 IF x_parallel_jobs = 'Y' THEN
1189 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :14');
1190 END IF;
1191
1192 FOR h_j IN h_init_per..h_end_per LOOP
1193 h_bind_vars_values.delete;
1194 h_num_bind_vars := 0;
1195
1196 h_num_bind_vars := h_num_bind_vars + 1;
1197 h_bind_vars_values(h_num_bind_vars) := '0';
1198 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1199 h_num_bind_vars := h_num_bind_vars + 1;
1200 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
1201 END IF;
1202 IF x_parallel_jobs = 'Y' THEN
1203 h_num_bind_vars := h_num_bind_vars + 1;
1204 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1205 END IF;
1206 h_num_bind_vars := h_num_bind_vars + 1;
1207 h_bind_vars_values(h_num_bind_vars) := '0';
1208 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1209 h_num_bind_vars := h_num_bind_vars + 1;
1210 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
1211 END IF;
1212 IF x_parallel_jobs = 'Y' THEN
1213 h_num_bind_vars := h_num_bind_vars + 1;
1214 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1215 END IF;
1216 IF h_yearly_flag = 1 THEN
1217 h_num_bind_vars := h_num_bind_vars + 1;
1218 h_bind_vars_values(h_num_bind_vars) := (h_j - h_previous_years);
1219 h_num_bind_vars := h_num_bind_vars + 1;
1220 h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1221 h_num_bind_vars := h_num_bind_vars + 1;
1222 h_bind_vars_values(h_num_bind_vars) := h_j;
1223 ELSE
1224 h_num_bind_vars := h_num_bind_vars + 1;
1225 h_bind_vars_values(h_num_bind_vars) := (x_current_fy - 1);
1226 h_num_bind_vars := h_num_bind_vars + 1;
1227 h_bind_vars_values(h_num_bind_vars) := h_j;
1228 h_num_bind_vars := h_num_bind_vars + 1;
1229 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
1230 h_num_bind_vars := h_num_bind_vars + 1;
1231 h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1232 h_num_bind_vars := h_num_bind_vars + 1;
1233 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
1234 h_num_bind_vars := h_num_bind_vars + 1;
1235 h_bind_vars_values(h_num_bind_vars) := h_j;
1236 END IF;
1237 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1238 h_num_bind_vars := h_num_bind_vars + 1;
1239 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
1240 END IF;
1241 IF x_parallel_jobs = 'Y' THEN
1242 h_num_bind_vars := h_num_bind_vars + 1;
1243 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1244 END IF;
1245 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1246 BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1247 commit;
1248 END LOOP;
1249 ELSE
1250 -- There is no rows in the projection table. So we need to insert.
1251 bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
1252 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1253 bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254 END IF;
1255 bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256 IF x_parallel_jobs = 'Y' THEN
1257 bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
1258 END IF;
1259 bsc_dbgen_utils.add_string(h_sql, ' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1260 FOR h_i IN 1..x_num_key_columns LOOP
1261 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1262 END LOOP;
1263 bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1264 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1265 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1266 END IF;
1267 FOR h_i IN 1..h_num_avg_cols LOOP
1268 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1269 END LOOP;
1270 IF x_aw_flag THEN
1271 bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
1272 END IF;
1273 bsc_dbgen_utils.add_string(h_sql, ')'||
1274 ' select');
1275 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1276 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277 END IF;
1278 bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1279 FOR h_i IN 1..x_num_key_columns LOOP
1280 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1281 END LOOP;
1282 IF h_yearly_flag = 1 THEN
1283 bsc_dbgen_utils.add_string(h_sql, ':1, 0, 0');
1284 ELSE
1285 bsc_dbgen_utils.add_string(h_sql, ':1, 0, :2');
1286 END IF;
1287 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1288 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1289 END IF;
1290 FOR h_i IN 1..h_num_avg_cols LOOP
1291 bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1292 END LOOP;
1293 IF x_aw_flag THEN
1294 bsc_dbgen_utils.add_string(h_sql, ', :3, :4');
1295 END IF;
1296 bsc_dbgen_utils.add_string(h_sql, ' from ('||
1297 ' select');
1298 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1299 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300 END IF;
1301 bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302 FOR h_i IN 1..x_num_key_columns LOOP
1303 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1304 END LOOP;
1305 bsc_dbgen_utils.add_string(h_sql, ' year, type, period');
1306 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1307 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1308 END IF;
1309 FOR h_i IN 1..h_num_avg_cols LOOP
1310 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1311 END LOOP;
1312 bsc_dbgen_utils.add_string(h_sql, ' from '||x_base_table||
1313 ' WHERE type = :5');
1314 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1315 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :6');
1316 END IF;
1317 IF x_parallel_jobs = 'Y' THEN
1318 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :7');
1319 END IF;
1320 bsc_dbgen_utils.add_string(h_sql, ' union all'||
1321 ' select ');
1322 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1323 bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324 END IF;
1325 bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326 FOR h_i IN 1..x_num_key_columns LOOP
1327 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
1328 END LOOP;
1329 bsc_dbgen_utils.add_string(h_sql, 'year, type, period');
1330 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1331 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1332 END IF;
1333 FOR h_i IN 1..h_num_avg_cols LOOP
1334 bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1335 END LOOP;
1336 bsc_dbgen_utils.add_string(h_sql, ' from '||x_proj_table||
1337 ' WHERE type = :8');
1338 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1339 bsc_dbgen_utils.add_string(h_sql, ' and periodicity_id = :9');
1340 END IF;
1341 IF x_parallel_jobs = 'Y' THEN
1342 bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :10');
1343 END IF;
1344 bsc_dbgen_utils.add_string(h_sql, ') u'||
1345 ' where');
1346 IF h_yearly_flag = 1 THEN
1347 bsc_dbgen_utils.add_string(h_sql, ' (year between :11 AND :12)');
1348 ELSE
1349 bsc_dbgen_utils.add_string(h_sql, ' (year * 1000 + period) between (:11 * 1000 + :12) and (:13 * 1000 + :14)');
1350 END IF;
1351 bsc_dbgen_utils.add_string(h_sql, ' group by '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1352 FOR h_i IN 1..x_num_key_columns LOOP
1353 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_columns(h_i));
1354 END LOOP;
1355 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1356 bsc_dbgen_utils.add_string(h_sql, ', periodicity_id');
1357 END IF;
1358
1359 FOR h_j IN h_init_per..h_end_per LOOP
1360 h_bind_vars_values.delete;
1361 h_num_bind_vars := 0;
1362
1363 IF h_yearly_flag = 1 THEN
1364 h_num_bind_vars := h_num_bind_vars + 1;
1365 h_bind_vars_values(h_num_bind_vars) := h_j;
1366 ELSE
1367 h_num_bind_vars := h_num_bind_vars + 1;
1368 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
1369 h_num_bind_vars := h_num_bind_vars + 1;
1370 h_bind_vars_values(h_num_bind_vars) := h_j;
1371 END IF;
1372 IF x_aw_flag THEN
1373 h_num_bind_vars := h_num_bind_vars + 1;
1374 h_bind_vars_values(h_num_bind_vars) := 'Y';
1375 h_num_bind_vars := h_num_bind_vars + 1;
1376 h_bind_vars_values(h_num_bind_vars) := x_change_vector_value;
1377 END IF;
1378 h_num_bind_vars := h_num_bind_vars + 1;
1379 h_bind_vars_values(h_num_bind_vars) := '0';
1380 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1381 h_num_bind_vars := h_num_bind_vars + 1;
1382 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
1383 END IF;
1384 IF x_parallel_jobs = 'Y' THEN
1385 h_num_bind_vars := h_num_bind_vars + 1;
1386 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1387 END IF;
1388 h_num_bind_vars := h_num_bind_vars + 1;
1389 h_bind_vars_values(h_num_bind_vars) := '0';
1390 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1391 h_num_bind_vars := h_num_bind_vars + 1;
1392 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
1393 END IF;
1394 IF x_parallel_jobs = 'Y' THEN
1395 h_num_bind_vars := h_num_bind_vars + 1;
1396 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1397 END IF;
1398 IF h_yearly_flag = 1 THEN
1399 h_num_bind_vars := h_num_bind_vars + 1;
1400 h_bind_vars_values(h_num_bind_vars) := (h_j - h_previous_years);
1401 h_num_bind_vars := h_num_bind_vars + 1;
1402 h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1403 ELSE
1404 h_num_bind_vars := h_num_bind_vars + 1;
1405 h_bind_vars_values(h_num_bind_vars) := (x_current_fy - 1);
1406 h_num_bind_vars := h_num_bind_vars + 1;
1407 h_bind_vars_values(h_num_bind_vars) := h_j;
1408 h_num_bind_vars := h_num_bind_vars + 1;
1409 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
1410 h_num_bind_vars := h_num_bind_vars + 1;
1411 h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1412 END IF;
1413 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1414 BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1415 commit;
1416 END LOOP;
1417 END IF;
1418 END IF;
1419
1420 x_return_status := 'success';
1421
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 rollback;
1425 x_return_status := 'error';
1426 x_error_message := 'Calc_Projection.e_others: '||SQLERRM;
1427
1428 END Calc_Projection;
1429
1430
1431 /*===========================================================================+
1432 | FUNCTION Create_Generic_Temp_Tables |
1433 +============================================================================*/
1434 FUNCTION Create_Generic_Temp_Tables RETURN BOOLEAN IS
1435
1436 e_unexpected_error EXCEPTION;
1437
1438 h_table_name VARCHAR2(30);
1439 h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
1440 h_num_columns NUMBER;
1441
1442 BEGIN
1443
1444 -- BSC_DB_CALENDAR_TEMP:
1445 h_table_name := 'BSC_DB_CALENDAR_TEMP';
1446 h_table_columns.delete;
1447 h_num_columns := 0;
1448 h_num_columns := h_num_columns + 1;
1449 h_table_columns(h_num_columns).column_name := 'LOWER_PERIOD';
1450 h_table_columns(h_num_columns).data_type := 'NUMBER';
1451 h_table_columns(h_num_columns).data_size := NULL;
1452 h_table_columns(h_num_columns).add_to_index := 'N';
1453 h_num_columns := h_num_columns + 1;
1454 h_table_columns(h_num_columns).column_name := 'UPPER_PERIOD';
1455 h_table_columns(h_num_columns).data_type := 'NUMBER';
1456 h_table_columns(h_num_columns).data_size := NULL;
1457 h_table_columns(h_num_columns).add_to_index := 'N';
1458 h_num_columns := h_num_columns + 1;
1459 h_table_columns(h_num_columns).column_name := 'YEAR';
1460 h_table_columns(h_num_columns).data_type := 'NUMBER';
1461 h_table_columns(h_num_columns).data_size := NULL;
1462 h_table_columns(h_num_columns).add_to_index := 'N';
1463 h_num_columns := h_num_columns + 1;
1464 h_table_columns(h_num_columns).column_name := 'LAST_PERIOD';
1465 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1466 h_table_columns(h_num_columns).data_size := 2;
1467 h_table_columns(h_num_columns).add_to_index := 'N';
1468 h_num_columns := h_num_columns + 1;
1469 h_table_columns(h_num_columns).column_name := 'PERIODICITY_ID';
1470 h_table_columns(h_num_columns).data_type := 'NUMBER';
1471 h_table_columns(h_num_columns).data_size := NULL;
1472 h_table_columns(h_num_columns).add_to_index := 'N';
1473 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1474 RAISE e_unexpected_error;
1475 END IF;
1476
1477 RETURN TRUE;
1478
1479 EXCEPTION
1480 WHEN e_unexpected_error THEN
1481 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1482 x_source => 'BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables');
1483 RETURN FALSE;
1484
1485 WHEN OTHERS THEN
1486 BSC_MESSAGE.Add(x_message => SQLERRM,
1487 x_source => 'BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables');
1488 RETURN FALSE;
1489 END Create_Generic_Temp_Tables;
1490
1491
1492 /*===========================================================================+
1493 | FUNCTION Create_Generic_Temp_Tables_AT
1494 +============================================================================*/
1495 FUNCTION Create_Generic_Temp_Tables_AT RETURN BOOLEAN IS
1496 PRAGMA AUTONOMOUS_TRANSACTION;
1497 h_b BOOLEAN;
1498 BEGIN
1499 h_b := Create_Generic_Temp_Tables;
1500 commit; -- all autonomous transaction needs to commit
1501 RETURN h_b;
1502 END Create_Generic_Temp_Tables_AT;
1503
1504
1505 /*===========================================================================+
1506 | PROCEDURE Create_Proc_Load_Tbl_MV
1507 +============================================================================*/
1508 PROCEDURE Create_Proc_Load_Tbl_MV(
1509 x_proc_name IN VARCHAR2,
1510 x_base_table IN VARCHAR2,
1511 x_input_table IN VARCHAR2,
1512 x_periodicity IN NUMBER,
1513 x_calendar_id IN NUMBER,
1514 x_current_period IN NUMBER,
1515 x_old_current_period IN NUMBER,
1516 x_current_fy IN NUMBER,
1517 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1518 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519 x_num_key_columns IN NUMBER,
1520 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1522 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
1523 x_num_data_columns IN NUMBER,
1524 x_partition_name IN VARCHAR2,
1525 x_batch_value IN NUMBER,
1526 x_num_partitions IN NUMBER,
1527 x_parallel_jobs IN VARCHAR2,
1528 x_rowid_table IN VARCHAR2,
1529 x_num_loads IN NUMBER,
1530 x_return_status OUT NOCOPY VARCHAR2,
1531 x_error_message OUT NOCOPY VARCHAR2
1532 ) IS
1533 --h_sql CLOB;
1534 h_sql dbms_sql.varchar2A;
1535 h_i NUMBER;
1536 h_calendar_source VARCHAR2(20);
1537 h_yearly_flag NUMBER;
1538 h_periodicity_type NUMBER;
1539 h_bal_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1540 h_num_bal_columns NUMBER;
1541 l_sql varchar2(10000);
1542 l_newline varchar2(10):='
1543 ';
1544 BEGIN
1545
1546 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1547 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1548 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
1549 h_num_bal_columns := 0;
1550 FOR h_i IN 1..x_num_data_columns LOOP
1551 IF x_data_measure_types(h_i) <> 1 THEN
1552 h_num_bal_columns := h_num_bal_columns + 1;
1553 h_bal_columns(h_num_bal_columns) := x_data_columns(h_i);
1554 END IF;
1555 END LOOP;
1556
1557 bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||l_newline||
1558 ' type bsc_b_r is record('||l_newline);
1559 FOR h_i IN 1..x_num_key_columns LOOP
1560 --Fix bug#4880895 use varchar2 to support bis dimensions
1561 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), '||l_newline);
1562 END LOOP;
1563 bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5), periodicity_id number'||l_newline);
1564 FOR h_i IN 1..x_num_data_columns LOOP
1565 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||l_newline||
1566 ', B_DATA'||h_i||' number');
1567 END LOOP;
1568 bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||l_newline||
1569 ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||l_newline||
1570 ' type t_rowid_table is table of rowid index by pls_integer;'||l_newline||
1571 ' h_load_batch number;'||l_newline);
1572
1573 IF x_parallel_jobs = 'Y' THEN
1574 bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := '||x_batch_value||';'||l_newline);
1575 ELSE
1576 bsc_dbgen_utils.add_string(h_sql, ' h_batch_value number := 0;'||l_newline);
1577 END IF;
1578 bsc_dbgen_utils.add_string(h_sql,
1579 ' h_num_partitions number := '||x_num_partitions||';'||l_newline||
1580 ' h_periodicity number := '||x_periodicity||';'||l_newline||
1581 ' h_current_fy number := '||x_current_fy||';'||l_newline||
1582 ' cursor c1 is'||l_newline||
1583 ' with bsc_i_data as ('||l_newline||
1584 ' SELECT ');
1585 FOR h_i IN 1..x_num_key_columns LOOP
1586 bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', '||l_newline);
1587 END LOOP;
1588 IF h_calendar_source = 'BSC' THEN
1589 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
1590 ELSE
1591 -- BIS calendar
1592 IF h_yearly_flag = 1 THEN
1593 bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
1594 ELSE
1595 bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
1596 END IF;
1597 END IF;
1598 bsc_dbgen_utils.add_string(h_sql, ', h_periodicity periodicity_id'||l_newline);
1599 FOR h_i IN 1..x_num_data_columns LOOP
1600 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline);
1601 END LOOP;
1602 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1603 ' FROM (select /*+ordered*/ ');
1604 IF x_parallel_jobs = 'Y' THEN
1605 bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1606 ELSE
1607 IF x_num_partitions > 0 THEN
1608 bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
1609 FOR h_i IN 1..x_num_key_columns LOOP
1610 IF h_i = 1 THEN
1611 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1612 ELSE
1613 bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1614 END IF;
1615 END LOOP;
1616 bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1617 ELSE
1618 bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1619 END IF;
1620 END IF;
1621 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
1622 ' FROM '||x_rowid_table||', '||x_input_table||
1623 ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
1624 ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
1625 IF x_parallel_jobs = 'Y' THEN
1626 bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
1627 FOR h_i IN 1..x_num_key_columns LOOP
1628 IF h_i = 1 THEN
1629 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||l_newline);
1630 ELSE
1631 bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i)||l_newline);
1632 END IF;
1633 END LOOP;
1634 bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
1635 END IF;
1636 bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
1637 FOR h_i IN 1..x_num_key_columns LOOP
1638 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i)||l_newline);
1639 END LOOP;
1640 IF h_calendar_source <> 'BSC' THEN
1641 -- BIS calendar
1642 bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
1643 END IF;
1644 IF x_num_key_columns > 0 THEN
1645 bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
1646 x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
1647 FOR h_i IN 2..x_num_key_columns LOOP
1648 bsc_dbgen_utils.add_string(h_sql, ' AND '||l_newline||
1649 x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
1650 END LOOP;
1651 END IF;
1652 IF h_calendar_source <> 'BSC' THEN
1653 -- BIS calendar
1654 IF x_num_key_columns > 0 THEN
1655 bsc_dbgen_utils.add_string(h_sql, ' AND');
1656 ELSE
1657 bsc_dbgen_utils.add_string(h_sql, ' WHERE');
1658 END IF;
1659 IF h_periodicity_type = 9 THEN
1660 -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
1661 bsc_dbgen_utils.add_string(h_sql,
1662 ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
1663 ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1664 ELSE
1665 -- Other periodicity. TIME_FK is VARCHAR2
1666 -- Fix bug#5175277 missing space ANDBSC_SYS_PERIODS....
1667 bsc_dbgen_utils.add_string(h_sql,
1668 ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
1669 ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
1670 END IF;
1671 END IF;
1672 bsc_dbgen_utils.add_string(h_sql, ') select ');
1673 FOR h_i IN 1..x_num_key_columns LOOP
1674 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', '||l_newline);
1675 END LOOP;
1676 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD'||
1677 ', '||x_input_table||'.PERIODICITY_ID');
1678 FOR h_i IN 1..x_num_data_columns LOOP
1679 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||l_newline||
1680 ', '||x_base_table||'.'||x_data_columns(h_i));
1681 END LOOP;
1682 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', '||x_base_table||'.rowid row_id'||
1683 ' FROM (SELECT ');
1684 FOR h_i IN 1..x_num_key_columns LOOP
1685 bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1686 END LOOP;
1687 bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_i_data.PERIOD, bsc_i_data.periodicity_id');
1688 FOR h_i IN 1..x_num_data_columns LOOP
1689 bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||x_data_columns(h_i)||l_newline);
1690 END LOOP;
1691 bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1692 ' from bsc_i_data'||l_newline||
1693 ' union all'||l_newline||
1694 ' select '||l_newline);
1695 FOR h_i IN 1..x_num_key_columns LOOP
1696 bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.'||x_key_columns(h_i)||', '||l_newline);
1697 END LOOP;
1698 bsc_dbgen_utils.add_string(h_sql, 'bsc_i_data.YEAR, bsc_i_data.TYPE, bsc_db_calendar_temp.upper_PERIOD PERIOD'||
1699 ', bsc_db_calendar_temp.periodicity_id');
1700 FOR h_i IN 1..x_num_data_columns LOOP
1701 IF x_data_measure_types(h_i) = 1 THEN
1702 -- Activity measure
1703 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_formulas(h_i)||' '||x_data_columns(h_i));
1704 ELSE
1705 -- Balance measure
1706 bsc_dbgen_utils.add_string(h_sql, ', SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_data_columns(h_i)||', NULL))'||
1707 ' '||x_data_columns(h_i));
1708 END IF;
1709 END LOOP;
1710 --Fix bug#5155523 do not use max(bsc_i_data.batch_column_name) instead add it to the group by
1711 bsc_dbgen_utils.add_string(h_sql, ', bsc_i_data.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' '||
1712 BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1713 ' from bsc_i_data, bsc_db_calendar_temp'||l_newline||
1714 ' where bsc_i_data.period = bsc_db_calendar_temp.lower_period and'||l_newline||
1715 ' bsc_i_data.year = bsc_db_calendar_temp.year'||l_newline||
1716 ' group by ');
1717 FOR h_i IN 1..x_num_key_columns LOOP
1718 bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.'||x_key_columns(h_i)||', ');
1719 END LOOP;
1720 bsc_dbgen_utils.add_string(h_sql, 'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||l_newline||
1721 ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID, BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||l_newline||
1722 ') '||x_input_table||l_newline||
1723 ', (select * from '||x_base_table||l_newline);
1724 IF x_parallel_jobs = 'Y' THEN
1725 bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
1726 END IF;
1727 bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||l_newline||
1728 ' where ');
1729 FOR h_i IN 1..x_num_key_columns LOOP
1730 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and '||l_newline);
1731 END LOOP;
1732 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||l_newline||
1733 x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||l_newline||
1734 x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+) and '||l_newline||
1735 x_input_table||'.PERIODICITY_ID = '||x_base_table||'.PERIODICITY_ID(+);'||l_newline||
1736 ' v1 bsc_b_t;'||l_newline||
1737 ' v1_join_rollup '||x_base_table||'_tt := '||x_base_table||'_tt();'||l_newline||
1738 ' v1_rollup bsc_b_t;'||l_newline||
1739 ' type temp_cal_tt is table of bsc_db_calendar_temp%rowtype index by pls_integer;'||l_newline||
1740 ' c1_correct temp_cal_tt;'||l_newline||
1741 ' before_period number := '||x_old_current_period||';'||l_newline||
1742 ' after_period number := '||x_current_period||';'||l_newline||
1743 ' cursor c2 is'||l_newline||
1744 ' select '||l_newline);
1745 FOR h_i IN 1..x_num_key_columns LOOP
1746 bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', '||l_newline);
1747 END LOOP;
1748 bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE, '||
1749 'bsc_db_calendar_temp.upper_PERIOD period, bsc_db_calendar_temp.periodicity_id'||l_newline);
1750 FOR h_i IN 1..x_num_data_columns LOOP
1751 IF x_data_measure_types(h_i) = 1 THEN
1752 -- Activity measure
1753 bsc_dbgen_utils.add_string(h_sql, ', '||
1754 replace(x_data_formulas(h_i),
1755 '('||x_data_columns(h_i)||')',
1756 '('||x_base_table||'.'||x_data_columns(h_i)||')')||
1757 ' '||x_data_columns(h_i)||
1758 ', null B_DATA'||h_i);
1759 ELSE
1760 -- Balance measure
1761 bsc_dbgen_utils.add_string(h_sql, ', '||
1762 'SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_base_table||'.'||x_data_columns(h_i)||', NULL))'||
1763 ' '||x_data_columns(h_i)||
1764 ', null B_DATA'||h_i);
1765
1766 END IF;
1767 END LOOP;
1768 bsc_dbgen_utils.add_string(h_sql, ', max('||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') '||
1769 BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
1770 ', chartorowid(tt.row_id) row_id'||
1771 ' from table(cast(v1_join_rollup as '||x_base_table||'_tt)) tt,'||
1772 ' bsc_db_calendar_temp, '||x_base_table||
1773 ' where ');
1774 FOR h_i IN 1..x_num_key_columns LOOP
1775 bsc_dbgen_utils.add_string(h_sql, 'tt.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||' and ');
1776 END LOOP;
1777 bsc_dbgen_utils.add_string(h_sql, 'tt.year = bsc_db_calendar_temp.year and'||l_newline||
1778 ' tt.type = '||x_base_table||'.type and'||l_newline||
1779 ' tt.period = bsc_db_calendar_temp.upper_period and'||l_newline||
1780 ' tt.periodicity_id = bsc_db_calendar_temp.periodicity_id and'||l_newline||
1781 ' bsc_db_calendar_temp.year = '||x_base_table||'.year and'||l_newline||
1782 ' bsc_db_calendar_temp.lower_period = '||x_base_table||'.period and'||l_newline||
1783 ' '||x_base_table||'.periodicity_id = h_periodicity'||l_newline);
1784 IF x_parallel_jobs = 'Y' THEN
1785 bsc_dbgen_utils.add_string(h_sql, ' and '||x_base_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value'||l_newline);
1786 END IF;
1787 bsc_dbgen_utils.add_string(h_sql, ' group by '||l_newline);
1788 FOR h_i IN 1..x_num_key_columns LOOP
1789 bsc_dbgen_utils.add_string(h_sql, x_base_table||'.'||x_key_columns(h_i)||', ');
1790 END LOOP;
1791 bsc_dbgen_utils.add_string(h_sql, x_base_table||'.YEAR, '||x_base_table||'.TYPE,'||
1792 ' bsc_db_calendar_temp.upper_PERIOD, bsc_db_calendar_temp.periodicity_id, tt.row_id;');
1793 FOR h_i IN 1..x_num_data_columns LOOP
1794 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||' dbms_sql.number_table;'||l_newline);
1795 END LOOP;
1796 bsc_dbgen_utils.add_string(h_sql, ' u_rowid t_rowid_table;');
1797 FOR h_i IN 1..x_num_key_columns LOOP
1798 -- Fix bug#4880895 user varchar2 to support bis dimensions
1799 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;'||l_newline);
1800 END LOOP;
1801 bsc_dbgen_utils.add_string(h_sql,
1802 ' i_year dbms_sql.number_table;'||l_newline||
1803 ' i_type dbms_sql.number_table;'||l_newline||
1804 ' i_period dbms_sql.number_table;'||l_newline||
1805 ' i_periodicity_id dbms_sql.number_table;'||l_newline);
1806 FOR h_i IN 1..x_num_data_columns LOOP
1807 bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;'||l_newline);
1808 END LOOP;
1809 bsc_dbgen_utils.add_string(h_sql,
1810 ' i_batch dbms_sql.number_table;'||l_newline||
1811 ' u_count integer := 0;'||l_newline||
1812 ' i_count integer := 0;'||l_newline||
1813 ' begin'||l_newline);
1814 -- Balance correction
1815 -- NOTE: Do this only if there are balance columns and the periodicity of the table is not yearly
1816 -- Also no need to do it if before_period = after_period
1817 -- Also this has to be done outside the p_load_batch loop
1818 IF (h_num_bal_columns > 0) AND (h_yearly_flag <> 1) AND (x_current_period > x_old_current_period) THEN
1819 bsc_dbgen_utils.add_string(h_sql, ' declare'||l_newline||
1820 ' cursor c_t is'||l_newline||
1821 ' select * from bsc_db_calendar_temp'||l_newline||
1822 ' order by periodicity_id,year,lower_period;'||l_newline||
1823 ' cursor c_cb(p_lower_periodicity number,p_lower_year number,p_lower_period number,'||l_newline||
1824 ' p_upper_periodicity number,p_upper_year number,p_upper_period number) is'||l_newline||
1825 ' select ');
1826 FOR h_i IN 1..h_num_bal_columns LOOP
1827 bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||h_bal_columns(h_i)||' '||h_bal_columns(h_i)||', '||l_newline);
1828 END LOOP;
1829 bsc_dbgen_utils.add_string(h_sql, 'b_upper.rowid row_id'||
1830 ' from '||x_base_table||' b_lower, '||x_base_table||' b_upper'||l_newline||
1831 ' where '||l_newline);
1832 IF x_parallel_jobs = 'Y' THEN
1833 bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1834 END IF;
1835 bsc_dbgen_utils.add_string(h_sql, 'b_lower.periodicity_id(+) = p_lower_periodicity and'||l_newline||
1836 ' b_lower.year(+) = p_lower_year and'||l_newline||
1837 ' b_lower.period(+) = p_lower_period and '||l_newline);
1838 IF x_parallel_jobs = 'Y' THEN
1839 bsc_dbgen_utils.add_string(h_sql, 'b_upper.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value and ');
1840 END IF;
1841 bsc_dbgen_utils.add_string(h_sql, 'b_upper.periodicity_id = p_upper_periodicity and'||l_newline||
1842 ' b_upper.year = p_upper_year and'||l_newline||
1843 ' b_upper.period = p_upper_period and '||l_newline);
1844 FOR h_i IN 1..x_num_key_columns LOOP
1845 bsc_dbgen_utils.add_string(h_sql, 'b_lower.'||x_key_columns(h_i)||'(+) = b_upper.'||x_key_columns(h_i)||' and '||l_newline);
1846 END LOOP;
1847 bsc_dbgen_utils.add_string(h_sql, ' b_lower.type(+) = b_upper.type;'||l_newline);
1848 FOR h_i IN 1..h_num_bal_columns LOOP
1849 bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||' dbms_sql.number_table;'||l_newline);
1850 END LOOP;
1851 bsc_dbgen_utils.add_string(h_sql, l_newline||
1852 ' l_cb_rowid t_rowid_table;'||l_newline||
1853 ' begin'||l_newline||
1854 ' open c_t;'||l_newline||
1855 ' loop'||l_newline||
1856 ' fetch c_t bulk collect into c1_correct;'||l_newline||
1857 ' exit when c_t%notfound;'||l_newline||
1858 ' end loop;'||l_newline||
1859 ' close c_t;'||l_newline||
1860 ' for i in 1..c1_correct.count loop'||l_newline||
1861 ' if c1_correct(i).lower_period = before_period and'||l_newline||
1862 ' c1_correct(i).year = h_current_fy and c1_correct(i).last_period <> ''Y'' then'||l_newline||
1863 ' for j in i..c1_correct.count loop'||l_newline||
1864 ' if c1_correct(i).periodicity_id = c1_correct(j).periodicity_id and'||l_newline||
1865 ' (c1_correct(j).last_period=''Y'' or'||l_newline||
1866 ' (c1_correct(j).lower_period = after_period and c1_correct(j).year = h_current_fy)) then'||l_newline);
1867 FOR h_i IN 1..h_num_bal_columns LOOP
1868 bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||'.delete;'||l_newline);
1869 END LOOP;
1870 bsc_dbgen_utils.add_string(h_sql,
1871 ' l_cb_rowid.delete;'||l_newline||
1872 ' open c_cb(h_periodicity, c1_correct(j).year, c1_correct(j).lower_period,'||l_newline||
1873 ' c1_correct(j).periodicity_id, c1_correct(j).year, c1_correct(j).upper_period);'||l_newline||
1874 ' loop'||l_newline||
1875 ' fetch c_cb bulk collect into ');
1876 FOR h_i IN 1..h_num_bal_columns LOOP
1877 bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||', ');
1878 END LOOP;
1879 bsc_dbgen_utils.add_string(h_sql, 'l_cb_rowid;'||l_newline||
1880 ' exit when c_cb%notfound;'||l_newline||
1881 ' end loop;'||l_newline||
1882 ' close c_cb;'||l_newline||
1883 ' forall k in 1..l_cb_rowid.count'||l_newline||
1884 ' update '||x_base_table||l_newline||
1885 ' set '||h_bal_columns(1)||' = l_cb_data1(k)');
1886 FOR h_i IN 2..h_num_bal_columns LOOP
1887 bsc_dbgen_utils.add_string(h_sql,
1888 ', '||h_bal_columns(h_i)||' = l_cb_data'||h_i||'(k)'||l_newline);
1889 END LOOP;
1890 bsc_dbgen_utils.add_string(h_sql, ' where rowid = l_cb_rowid(k);'||l_newline||
1891 ' commit;'||l_newline||
1892 ' exit;'||l_newline||
1893 ' end if;'||l_newline||
1894 ' end loop;'||l_newline||
1895 ' end if;'||l_newline||
1896 ' end loop;'||l_newline||
1897 ' end;');
1898 END IF;
1899 -- Start key translation
1900 bsc_dbgen_utils.add_string(h_sql,
1901 ' for k in 0..'||x_num_loads||' loop'||l_newline||
1902 ' h_load_batch := k;'||l_newline||
1903 ' v1.delete;'||l_newline||
1904 ' v1_join_rollup.delete;'||l_newline||
1905 ' v1_rollup.delete;'||l_newline);
1906 FOR h_i IN 1..x_num_data_columns LOOP
1907 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'.delete;'||l_newline);
1908 END LOOP;
1909 bsc_dbgen_utils.add_string(h_sql, ' u_rowid.delete;'||l_newline);
1910 FOR h_i IN 1..x_num_key_columns LOOP
1911 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
1912 END LOOP;
1913
1914 bsc_dbgen_utils.add_string(h_sql,
1915 ' i_year.delete;'||l_newline||
1916 ' i_type.delete;'||l_newline||
1917 ' i_period.delete;'||l_newline||
1918 ' i_periodicity_id.delete;'||l_newline);
1919 FOR h_i IN 1..x_num_data_columns LOOP
1920 bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;'||l_newline);
1921 END LOOP;
1922
1923 bsc_dbgen_utils.add_string(h_sql,
1924 ' i_batch.delete;'||l_newline||
1925 ' u_count := 0;'||l_newline||
1926 ' i_count := 0;'||l_newline||
1927 ' open c1;'||l_newline||
1928 ' loop'||l_newline||
1929 ' fetch c1 bulk collect into v1;'||l_newline||
1930 ' exit when c1%notfound;'||l_newline||
1931 ' end loop;'||l_newline||
1932 ' close c1;'||l_newline||
1933 ' for i in 1..v1.count loop'||l_newline||
1934 ' if v1(i).row_id is not null and v1(i).periodicity_id <> h_periodicity then'||l_newline||
1935 ' v1_join_rollup.extend;'||l_newline||
1936 ' v1_join_rollup(v1_join_rollup.count) := '||x_base_table||'_t('||l_newline);
1937 FOR h_i IN 1..x_num_key_columns LOOP
1938 bsc_dbgen_utils.add_string(h_sql, 'v1(i).'||x_key_columns(h_i)||', '||l_newline);
1939 END LOOP;
1940
1941 bsc_dbgen_utils.add_string(h_sql, 'v1(i).year, v1(i).type, v1(i).period, v1(i).periodicity_id');
1942 FOR h_i IN 1..x_num_data_columns LOOP
1943 bsc_dbgen_utils.add_string(h_sql, ', null');
1944 END LOOP;
1945 --Fix bug#4915276: use nvl() since 10<>null is false
1946 bsc_dbgen_utils.add_string(h_sql, ', rowidtochar(v1(i).row_id), null);'||l_newline||
1947 ' end if;'||l_newline||
1948 ' end loop;'||l_newline||
1949 ' u_count := 0;'||l_newline||
1950 ' for i in 1..v1.count loop'||l_newline||
1951 ' if v1(i).row_id is not null then'||l_newline||
1952 ' if (nvl(v1(i).b_data1,-9999999999)<>nvl(v1(i).'||x_data_columns(1)||',-9999999999))'||l_newline);
1953 FOR h_i IN 2..x_num_data_columns LOOP
1954 bsc_dbgen_utils.add_string(h_sql, ' or (nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999))'||l_newline);
1955 END LOOP;
1956
1957 bsc_dbgen_utils.add_string(h_sql, ' then'||l_newline||
1958 ' if v1(i).periodicity_id = h_periodicity then'||l_newline||
1959 ' u_count:=u_count+1;'||l_newline||
1960 ' u_rowid(u_count):=v1(i).row_id;'||l_newline);
1961 FOR h_i IN 1..x_num_data_columns LOOP
1962 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'(u_count):=v1(i).'||x_data_columns(h_i)||';'||l_newline);
1963 END LOOP;
1964 bsc_dbgen_utils.add_string(h_sql,
1965 ' end if;'||l_newline||
1966 ' end if;'||l_newline||
1967 ' else'||l_newline||
1968 ' i_count:=i_count+1;'||l_newline);
1969 FOR h_i IN 1..x_num_key_columns LOOP
1970 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count):=v1(i).'||x_key_columns(h_i)||';'||l_newline);
1971 END LOOP;
1972
1973 bsc_dbgen_utils.add_string(h_sql,
1974 ' i_YEAR(i_count):=v1(i).year;'||l_newline||
1975 ' i_TYPE(i_count):=v1(i).type;'||l_newline||
1976 ' i_PERIOD(i_count):=v1(i).period;'||l_newline||
1977 ' i_periodicity_id(i_count):=v1(i).periodicity_id;'||l_newline);
1978
1979 l_sql := null;
1980 FOR h_i IN 1..x_num_data_columns LOOP
1981 l_sql := l_sql ||' i_data'||h_i||'(i_count):=v1(i).'||x_data_columns(h_i)||';'||l_newline;
1982 END LOOP;
1983 bsc_dbgen_utils.add_string(h_sql, l_sql);
1984 l_sql := null;
1985 l_sql :=
1986 ' i_batch(i_count):=v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';'||l_newline||
1987 ' end if;'||l_newline||
1988 ' end loop;'||l_newline||
1989 ' forall i in 1..u_count'||l_newline||
1990 ' update '||x_base_table||l_newline;
1991 IF x_parallel_jobs = 'Y' THEN
1992 l_sql := l_sql||' partition('||x_partition_name||')';
1993 END IF;
1994 bsc_dbgen_utils.add_string(h_sql, l_sql);
1995 l_sql := null;
1996 l_sql := ' set '||x_data_columns(1)||'=u_data1(i)'||l_newline;
1997 FOR h_i IN 2..x_num_data_columns LOOP
1998 l_sql := l_sql||', '||x_data_columns(h_i)||' = u_data'||h_i||'(i)'||l_newline;
1999 END LOOP;
2000 bsc_dbgen_utils.add_string(h_sql, l_sql);
2001 l_sql := null;
2002 l_sql := ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2003 ' forall i in 1..i_count'||l_newline||
2004 ' insert /*+append*/ into '||x_base_table||l_newline;
2005 bsc_dbgen_utils.add_string(h_sql, l_sql);
2006 l_sql := null;
2007 -- ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2008 -- ' forall i in 1..i_count'||l_newline||
2009 -- ' insert /*+append*/ into '||x_base_table||l_newline;
2010 IF x_parallel_jobs = 'Y' THEN
2011 bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2012 END IF;
2013 bsc_dbgen_utils.add_string(h_sql, ' (');
2014 FOR h_i IN 1..x_num_key_columns LOOP
2015 l_sql := x_key_columns(h_i)||', '||l_newline;
2016 bsc_dbgen_utils.add_string(h_sql, l_sql);
2017 END LOOP;
2018 bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD, PERIODICITY_ID');
2019 FOR h_i IN 1..x_num_data_columns LOOP
2020 l_sql := ', '||x_data_columns(h_i)||l_newline;
2021 bsc_dbgen_utils.add_string(h_sql, l_sql);
2022 END LOOP;
2023
2024 l_sql := ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||l_newline||
2025 ' values('||l_newline;
2026 FOR h_i IN 1..x_num_key_columns LOOP
2027 l_sql := l_sql||'i_'||x_key_columns(h_i)||'(i), ';
2028 END LOOP;
2029 l_sql := l_sql||'i_YEAR(i),i_TYPE(i),i_PERIOD(i),i_periodicity_id(i)';
2030 bsc_dbgen_utils.add_string(h_sql, l_sql);
2031 l_sql := null;
2032 FOR h_i IN 1..x_num_data_columns LOOP
2033 l_sql := ', i_data'||h_i||'(i)';
2034 --bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)';
2035 bsc_dbgen_utils.add_string(h_sql, l_sql);
2036 END LOOP;
2037 l_sql := ', i_BATCH(i));'||l_newline||
2038 ' commit;'|| l_newline||
2039 ' v1_rollup.delete;'||l_newline||
2040 ' open c2;'||l_newline||
2041 ' loop'||l_newline||
2042 ' fetch c2 bulk collect into v1_rollup;'||l_newline||
2043 ' exit when c2%notfound;'||l_newline||
2044 ' end loop;'||l_newline||
2045 ' close c2;'||l_newline||
2046 ' u_count:=0;'||l_newline||
2047 ' u_rowid.delete;'||l_newline;
2048 FOR h_i IN 1..x_num_data_columns LOOP
2049 l_sql := l_sql||' u_data'||h_i||'.delete;'||l_newline;
2050 END LOOP;
2051 l_sql := l_sql||
2052 ' for i in 1..v1_rollup.count loop'||l_newline||
2053 ' u_count:=u_count+1;'||l_newline||
2054 ' u_rowid(u_count):=v1_rollup(i).row_id;'||l_newline;
2055 FOR h_i IN 1..x_num_data_columns LOOP
2056 l_sql := l_sql||' u_data'||h_i||'(u_count):=v1_rollup(i).'||x_data_columns(h_i)||';'||l_newline;
2057 END LOOP;
2058 l_sql := l_sql||
2059 ' end loop;'||l_newline||
2060 ' forall i in 1..u_count'||l_newline||
2061 ' update '||x_base_table||l_newline;
2062 bsc_dbgen_utils.add_string(h_sql, l_sql);
2063 l_sql :=null;
2064
2065 IF x_parallel_jobs = 'Y' THEN
2066 l_sql := l_sql||' partition('||x_partition_name||')';
2067 END IF;
2068 l_sql := l_sql||
2069 ' set '||x_data_columns(1)||'=u_data1(i)';
2070 FOR h_i IN 2..x_num_data_columns LOOP
2071 l_sql := l_sql||', '||x_data_columns(h_i)||' = u_data'||h_i||'(i)'||l_newline;
2072 END LOOP;
2073 l_sql := l_sql||
2074 ' where '||x_base_table||'.rowid=u_rowid(i);'||l_newline||
2075 ' commit;'||l_newline||
2076 ' end loop;'||l_newline||
2077 ' end;';
2078 bsc_dbgen_utils.add_string(h_sql, l_sql);
2079
2080 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2081 BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2082 commit;
2083 x_return_status := 'success';
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 x_return_status := 'error';
2088 x_error_message := 'Create_Proc_Load_Tbl_MV.e_others: '||SQLERRM;
2089
2090 END Create_Proc_Load_Tbl_MV;
2091
2092
2093 /*===========================================================================+
2094 | PROCEDURE Create_Proc_Load_Tbl_SUM_AW
2095 +============================================================================*/
2096 PROCEDURE Create_Proc_Load_Tbl_SUM_AW(
2097 x_proc_name IN VARCHAR2,
2098 x_base_table IN VARCHAR2,
2099 x_input_table IN VARCHAR2,
2100 x_aw_flag IN BOOLEAN,
2101 x_change_vector_value IN NUMBER,
2102 x_periodicity IN NUMBER,
2103 x_calendar_id IN NUMBER,
2104 x_current_period IN NUMBER,
2105 x_current_fy IN NUMBER,
2106 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2107 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2108 x_num_key_columns IN NUMBER,
2109 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2110 x_num_data_columns IN NUMBER,
2111 x_partition_name IN VARCHAR2,
2112 x_batch_value IN NUMBER,
2113 x_num_partitions IN NUMBER,
2114 x_parallel_jobs IN VARCHAR2,
2115 x_rowid_table IN VARCHAR2,
2116 x_num_loads IN NUMBER,
2117 x_return_status OUT NOCOPY VARCHAR2,
2118 x_error_message OUT NOCOPY VARCHAR2
2119 ) IS
2120 --h_sql CLOB;
2121 h_sql dbms_sql.varchar2A;
2122 h_i NUMBER;
2123 h_calendar_source VARCHAR2(20);
2124 h_yearly_flag NUMBER;
2125 h_periodicity_type NUMBER;
2126
2127 BEGIN
2128 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2129 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2130 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2131
2132 bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||
2133 ' type bsc_b_r is record(');
2134 FOR h_i IN 1..x_num_key_columns LOOP
2135 -- Fix bug#4880895 use varchar2 to support bis dimensions
2136 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' varchar2(400), ');
2137 END LOOP;
2138 bsc_dbgen_utils.add_string(h_sql, 'year number(5), type number(3), period number(5)');
2139 FOR h_i IN 1..x_num_data_columns LOOP
2140 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' number'||
2141 ', B_DATA'||h_i||' number');
2142 END LOOP;
2143 bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' number, row_id rowid);'||
2144 ' type bsc_b_t is table of bsc_b_r index by pls_integer;'||
2145 ' type t_rowid_table is table of rowid index by pls_integer;'||
2146 ' h_load_batch number;'||
2147 ' h_batch_value number;'||
2148 ' h_num_partitions number;'||
2149 ' h_periodicity number := '||x_periodicity||';'||
2150 ' cursor c1 is'||
2151 ' select ');
2152 FOR h_i IN 1..x_num_key_columns LOOP
2153 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||', ');
2154 END LOOP;
2155 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2156 FOR h_i IN 1..x_num_data_columns LOOP
2157 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i)||
2158 ', '||x_base_table||'.'||x_data_columns(h_i));
2159 END LOOP;
2160 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2161 ', '||x_base_table||'.rowid row_id'||
2162 ' FROM (SELECT ');
2163 FOR h_i IN 1..x_num_key_columns LOOP
2164 bsc_dbgen_utils.add_string(h_sql, x_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', ');
2165 END LOOP;
2166 IF h_calendar_source = 'BSC' THEN
2167 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD');
2168 ELSE
2169 -- BIS calendar
2170 IF h_yearly_flag = 1 THEN
2171 bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD');
2172 ELSE
2173 bsc_dbgen_utils.add_string(h_sql, 'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD');
2174 END IF;
2175 END IF;
2176 FOR h_i IN 1..x_num_data_columns LOOP
2177 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||x_data_columns(h_i));
2178 END LOOP;
2179 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||
2180 ' FROM (SELECT /*+ ordered */ ');
2181 IF x_parallel_jobs = 'Y' THEN
2182 bsc_dbgen_utils.add_string(h_sql, 'h_batch_value '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2183 ELSE
2184 IF x_num_partitions > 0 THEN
2185 bsc_dbgen_utils.add_string(h_sql, ' dbms_utility.get_hash_value(');
2186 FOR h_i IN 1..x_num_key_columns LOOP
2187 IF h_i = 1 THEN
2188 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2189 ELSE
2190 bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2191 END IF;
2192 END LOOP;
2193 bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2194 ELSE
2195 bsc_dbgen_utils.add_string(h_sql, ' 0 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
2196 END IF;
2197 END IF;
2198 bsc_dbgen_utils.add_string(h_sql, ', '||x_input_table||'.*'||
2199 ' FROM '||x_rowid_table||', '||x_input_table||
2200 ' WHERE '||x_rowid_table||'.row_id = '||x_input_table||'.rowid and'||
2201 ' '||x_rowid_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_load_batch');
2202 IF x_parallel_jobs = 'Y' THEN
2203 bsc_dbgen_utils.add_string(h_sql, ' and dbms_utility.get_hash_value(');
2204 FOR h_i IN 1..x_num_key_columns LOOP
2205 IF h_i = 1 THEN
2206 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i));
2207 ELSE
2208 bsc_dbgen_utils.add_string(h_sql, '||''.''||'||x_key_columns(h_i));
2209 END IF;
2210 END LOOP;
2211 bsc_dbgen_utils.add_string(h_sql, ', 0, h_num_partitions) = h_batch_value');
2212 END IF;
2213 bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table);
2214 FOR h_i IN 1..x_num_key_columns LOOP
2215 bsc_dbgen_utils.add_string(h_sql, ', '||x_key_dim_tables(h_i));
2216 END LOOP;
2217 IF h_calendar_source <> 'BSC' THEN
2218 -- BIS calendar
2219 bsc_dbgen_utils.add_string(h_sql, ', BSC_SYS_PERIODS');
2220 END IF;
2221 IF x_num_key_columns > 0 THEN
2222 bsc_dbgen_utils.add_string(h_sql, ' WHERE '||
2223 x_input_table||'.'||x_key_columns(1)||' = '||x_key_dim_tables(1)||'.USER_CODE');
2224 FOR h_i IN 2..x_num_key_columns LOOP
2225 bsc_dbgen_utils.add_string(h_sql, ' AND '||
2226 x_input_table||'.'||x_key_columns(h_i)||' = '||x_key_dim_tables(h_i)||'.USER_CODE');
2227 END LOOP;
2228 END IF;
2229 IF h_calendar_source <> 'BSC' THEN
2230 -- BIS calendar
2231 IF x_num_key_columns > 0 THEN
2232 bsc_dbgen_utils.add_string(h_sql, ' AND');
2233 ELSE
2234 bsc_dbgen_utils.add_string(h_sql, ' WHERE');
2235 END IF;
2236 IF h_periodicity_type = 9 THEN
2237 -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
2238 bsc_dbgen_utils.add_string(h_sql,
2239 ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
2240 ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2241 ELSE
2242 -- Other periodicity. TIME_FK is VARCHAR2
2243 bsc_dbgen_utils.add_string(h_sql,
2244 ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
2245 ' BSC_SYS_PERIODS.PERIODICITY_ID = h_periodicity');
2246 END IF;
2247 END IF;
2248 bsc_dbgen_utils.add_string(h_sql, ') '||x_input_table||
2249 ', (select * from '||x_base_table);
2250 IF x_parallel_jobs = 'Y' THEN
2251 bsc_dbgen_utils.add_string(h_sql, ' where '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = h_batch_value');
2252 END IF;
2253 bsc_dbgen_utils.add_string(h_sql, ') '||x_base_table||
2254 ' where ');
2255 FOR h_i IN 1..x_num_key_columns LOOP
2256 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.'||x_key_columns(h_i)||' = '||x_base_table||'.'||x_key_columns(h_i)||'(+) and ');
2257 END LOOP;
2258 bsc_dbgen_utils.add_string(h_sql, x_input_table||'.YEAR = '||x_base_table||'.YEAR(+) and '||
2259 x_input_table||'.TYPE = '||x_base_table||'.TYPE(+) and '||
2260 x_input_table||'.PERIOD = '||x_base_table||'.PERIOD(+);'||
2261 ' v1 bsc_b_t;');
2262 FOR h_i IN 1..x_num_data_columns LOOP
2263 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table dbms_sql.number_table;');
2264 END LOOP;
2265 bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table t_rowid_table;');
2266 FOR h_i IN 1..x_num_key_columns LOOP
2267 -- Fix bug#4880895 use varchar2 to support bis dimensions
2268 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||' dbms_sql.varchar2_table;');
2269 END LOOP;
2270 bsc_dbgen_utils.add_string(h_sql,
2271 ' i_YEAR dbms_sql.number_table;'||
2272 ' i_TYPE dbms_sql.number_table;'||
2273 ' i_PERIOD dbms_sql.number_table;');
2274 FOR h_i IN 1..x_num_data_columns LOOP
2275 bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||' dbms_sql.number_table;');
2276 END LOOP;
2277 bsc_dbgen_utils.add_string(h_sql,
2278 ' i_BATCH dbms_sql.number_table;');
2279 IF x_aw_flag THEN
2280 bsc_dbgen_utils.add_string(h_sql,
2281 ' i_PROJECTION dbms_sql.varchar2_table;');
2282 END IF;
2283 bsc_dbgen_utils.add_string(h_sql,
2284 ' u_count integer := 0;'||
2285 ' i_count integer := 0;'||
2286 ' begin');
2287 IF x_parallel_jobs = 'Y' THEN
2288 bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := '||x_batch_value||';');
2289 ELSE
2290 bsc_dbgen_utils.add_string(h_sql, ' h_batch_value := 0;');
2291 END IF;
2292 bsc_dbgen_utils.add_string(h_sql,
2293 ' h_num_partitions := '||x_num_partitions||';'||
2294 ' for k in 0..'||x_num_loads||' loop'||
2295 ' h_load_batch := k;'||
2296 ' v1.delete;');
2297 FOR h_i IN 1..x_num_data_columns LOOP
2298 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table.delete;');
2299 END LOOP;
2300 bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
2301 FOR h_i IN 1..x_num_key_columns LOOP
2302 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
2303 END LOOP;
2304 bsc_dbgen_utils.add_string(h_sql,
2305 ' i_YEAR.delete;'||
2306 ' i_TYPE.delete;'||
2307 ' i_PERIOD.delete;');
2308 FOR h_i IN 1..x_num_data_columns LOOP
2309 bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;');
2310 END LOOP;
2311 bsc_dbgen_utils.add_string(h_sql,
2312 ' i_BATCH.delete;');
2313 IF x_aw_flag THEN
2314 bsc_dbgen_utils.add_string(h_sql,
2315 ' i_PROJECTION.delete;');
2316 END IF;
2317 -- Fix bug#4915276: use nvl() since 10<>null is false
2318 bsc_dbgen_utils.add_string(h_sql,
2319 ' u_count := 0;'||
2320 ' i_count := 0;'||
2321 ' open c1;'||
2322 ' loop'||
2323 ' fetch c1 bulk collect into v1;'||
2324 ' exit when c1%notfound;'||
2325 ' end loop;'||
2326 ' close c1;'||
2327 ' for i in 1..v1.count loop'||
2328 ' if v1(i).row_id is not null then'||
2329 ' if nvl(v1(i).b_data1,-9999999999)<>nvl(v1(i).'||x_data_columns(1)||',-9999999999)');
2330 FOR h_i IN 2..x_num_data_columns LOOP
2331 bsc_dbgen_utils.add_string(h_sql, ' OR nvl(v1(i).b_data'||h_i||',-9999999999)<>nvl(v1(i).'||x_data_columns(h_i)||',-9999999999)');
2332 END LOOP;
2333 bsc_dbgen_utils.add_string(h_sql, ' then'||
2334 ' u_count := u_count+1;'||
2335 ' u_rowid_table(u_count) := v1(i).row_id;');
2336 FOR h_i IN 1..x_num_data_columns LOOP
2337 bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table(u_count) := v1(i).'||x_data_columns(h_i)||';');
2338 END LOOP;
2339 bsc_dbgen_utils.add_string(h_sql,
2340 ' end if;'||
2341 ' else'||
2342 ' i_count := i_count+1;');
2343 FOR h_i IN 1..x_num_key_columns LOOP
2344 bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'(i_count) := v1(i).'||x_key_columns(h_i)||';');
2345 END LOOP;
2346 bsc_dbgen_utils.add_string(h_sql,
2347 ' i_YEAR(i_count) := v1(i).year;'||
2348 ' i_TYPE(i_count) := v1(i).type;'||
2349 ' i_PERIOD(i_count) := v1(i).period;');
2350 FOR h_i IN 1..x_num_data_columns LOOP
2351 bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'(i_count) := v1(i).'||x_data_columns(h_i)||';');
2352 END LOOP;
2353 bsc_dbgen_utils.add_string(h_sql,
2354 ' i_BATCH(i_count) := v1(i).'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||';');
2355 IF x_aw_flag THEN
2356 IF h_yearly_flag = 1 THEN
2357 bsc_dbgen_utils.add_string(h_sql,
2358 ' if v1(i).year>'||x_current_fy||' then');
2359 ELSE
2360 bsc_dbgen_utils.add_string(h_sql,
2361 ' if (v1(i).year='||x_current_fy||' and v1(i).period>'||x_current_period||') or'||
2362 ' v1(i).year>'||x_current_fy||' then');
2363 END IF;
2364 bsc_dbgen_utils.add_string(h_sql,
2365 ' i_PROJECTION(i_count) := ''Y'';'||
2366 ' else'||
2367 ' i_PROJECTION(i_count) := ''N'';'||
2368 ' end if;');
2369 END IF;
2370 bsc_dbgen_utils.add_string(h_sql,
2371 ' end if;'||
2372 ' end loop;'||
2373 ' forall i in 1..u_count'||
2374 ' update '||x_base_table);
2375 IF x_parallel_jobs = 'Y' THEN
2376 bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2377 END IF;
2378 bsc_dbgen_utils.add_string(h_sql,
2379 ' set '||x_data_columns(1)||' = u_data1_table(i)');
2380 FOR h_i IN 2..x_num_data_columns LOOP
2381 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i)||' = u_data'||h_i||'_table(i)');
2382 END LOOP;
2383 IF x_aw_flag THEN
2384 bsc_dbgen_utils.add_string(h_sql, ', CHANGE_VECTOR = '||x_change_vector_value);
2385 END IF;
2386 bsc_dbgen_utils.add_string(h_sql, ' where '||x_base_table||'.rowid = u_rowid_table(i);'||
2387 ' forall i in 1..i_count'||
2388 ' insert /*+append*/ into '||x_base_table);
2389 IF x_parallel_jobs = 'Y' THEN
2390 bsc_dbgen_utils.add_string(h_sql, ' partition('||x_partition_name||')');
2391 END IF;
2392 bsc_dbgen_utils.add_string(h_sql, ' (');
2393 FOR h_i IN 1..x_num_key_columns LOOP
2394 bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');
2395 END LOOP;
2396 bsc_dbgen_utils.add_string(h_sql, 'YEAR, TYPE, PERIOD');
2397 FOR h_i IN 1..x_num_data_columns LOOP
2398 bsc_dbgen_utils.add_string(h_sql, ', '||x_data_columns(h_i));
2399 END LOOP;
2400 IF x_aw_flag THEN
2401 bsc_dbgen_utils.add_string(h_sql, ', PROJECTION, CHANGE_VECTOR');
2402 END IF;
2403 bsc_dbgen_utils.add_string(h_sql, ', '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||')'||
2404 ' values(');
2405 FOR h_i IN 1..x_num_key_columns LOOP
2406 bsc_dbgen_utils.add_string(h_sql, 'i_'||x_key_columns(h_i)||'(i), ');
2407 END LOOP;
2408 bsc_dbgen_utils.add_string(h_sql, 'i_YEAR(i), i_TYPE(i), i_PERIOD(i)');
2409 FOR h_i IN 1..x_num_data_columns LOOP
2410 bsc_dbgen_utils.add_string(h_sql, ', i_data'||h_i||'(i)');
2411 END LOOP;
2412 IF x_aw_flag THEN
2413 bsc_dbgen_utils.add_string(h_sql, ', i_PROJECTION(i), '||x_change_vector_value);
2414 END IF;
2415 bsc_dbgen_utils.add_string(h_sql, ', i_BATCH(i));'||
2416 ' commit;'||
2417 ' end loop;'||
2418 ' end;');
2419
2420 -- Create the stored procedure
2421 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2422 BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2423 commit;
2424
2425 x_return_status := 'success';
2426
2427 EXCEPTION
2428 WHEN OTHERS THEN
2429 x_return_status := 'error';
2430 x_error_message := 'Create_Proc_Load_Tbl_SUM_AW.e_others: '||SQLERRM;
2431
2432 END Create_Proc_Load_Tbl_SUM_AW;
2433
2434
2435 /*===========================================================================+
2436 | PROCEDURE Create_Types_for_MV_Load
2437 +============================================================================*/
2438 PROCEDURE Create_Types_For_MV_Load(
2439 x_base_table IN VARCHAR2,
2440 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2441 x_num_key_columns IN NUMBER,
2442 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2443 x_num_data_columns IN NUMBER,
2444 x_return_status OUT NOCOPY VARCHAR2,
2445 x_error_message OUT NOCOPY VARCHAR2
2446 ) IS
2447
2448 h_i NUMBER;
2449 h_sql VARCHAR2(32000);
2450
2451 BEGIN
2452
2453 h_sql := 'drop type '||x_base_table||'_tt';
2454 begin execute immediate h_sql; exception when others then null; end;
2455
2456 h_sql := 'drop type '||x_base_table||'_t';
2457 begin execute immediate h_sql; exception when others then null; end;
2458
2459 h_sql := 'create or replace type '||x_base_table||'_t as object(';
2460 for h_i IN 1..x_num_key_columns loop
2461 -- Fix bug#4880895 use varchar2 to support bis dimensions
2462 h_sql := h_sql|| ' '||x_key_columns(h_i)||' varchar2(400),';
2463 end loop;
2464 h_sql := h_sql|| ' year number(5), type number(3), period number(5), periodicity_id number';
2465 for h_i IN 1..x_num_data_columns loop
2466 h_sql := h_sql|| ', '||x_data_columns(h_i)||' number';
2467 end loop;
2468 h_sql := h_sql|| ', row_id varchar2(32), is_null number)';
2469 execute immediate h_sql;
2470
2471 h_sql := 'create or replace type '||x_base_table||'_tt is table of '||x_base_table||'_t';
2472 execute immediate h_sql;
2473
2474 x_return_status := 'success';
2475
2476 EXCEPTION
2477 WHEN OTHERS THEN
2478 x_return_status := 'error';
2479 x_error_message := 'Create_Types_For_MV_Load.e_others: '||SQLERRM;
2480
2481 END Create_Types_For_MV_Load;
2482
2483
2484 /*===========================================================================+
2485 | FUNCTION Get_Base_Proj_Tbl_Name
2486 +============================================================================*/
2487 FUNCTION Get_Base_Proj_Tbl_Name(
2488 x_base_table IN VARCHAR2
2489 ) RETURN VARCHAR2 IS
2490 h_list dbms_sql.varchar2_table;
2491 h_values dbms_sql.varchar2_table;
2492 BEGIN
2493 h_list.delete;
2494 h_list(1) := BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE;
2495 h_values := BSC_DBGEN_METADATA_READER.Get_Table_Properties(x_base_table, h_list);
2496 RETURN h_values(1);
2497 END Get_Base_Proj_Tbl_Name;
2498
2499
2500 /*===========================================================================+
2501 | PROCEDURE Init_Bsc_Db_Calendar_Temp
2502 +============================================================================*/
2503 PROCEDURE Init_Bsc_Db_Calendar_Temp(
2504 x_base_table IN VARCHAR2,
2505 x_periodicity IN NUMBER,
2506 x_calendar_id IN NUMBER,
2507 x_current_period IN NUMBER,
2508 x_current_fy IN NUMBER,
2509 x_return_status OUT NOCOPY VARCHAR2,
2510 x_error_message OUT NOCOPY VARCHAR2
2511 ) IS
2512
2513 h_sql VARCHAR2(32000);
2514 TYPE t_cursor IS REF CURSOR;
2515 h_cursor t_cursor;
2516
2517 CURSOR c_upper_periodicities (p_table_name VARCHAR2, p_calc_type NUMBER) IS
2518 SELECT c.parameter1, p.yearly_flag, p.db_column_name
2519 FROM bsc_db_calculations c, bsc_sys_periodicities p
2520 WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
2521 c.parameter1 = p.periodicity_id;
2522
2523 h_column_name VARCHAR2(30);
2524 h_up_periodicity NUMBER;
2525 h_up_yearly_flag NUMBER;
2526 h_up_column_name VARCHAR2(30);
2527 h_up_current_period NUMBER;
2528 h_y VARCHAR2(1);
2529 h_n VARCHAR2(1);
2530
2531 BEGIN
2532 BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2533 commit;
2534
2535 h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2536 h_y := 'Y';
2537 h_n := 'N';
2538
2539 OPEN c_upper_periodicities(x_base_table, 6);
2540 LOOP
2541 FETCH c_upper_periodicities INTO h_up_periodicity, h_up_yearly_flag, h_up_column_name;
2542 EXIT WHEN c_upper_periodicities%NOTFOUND;
2543
2544 IF h_up_yearly_flag = 1 THEN
2545 h_sql := 'insert into bsc_db_calendar_temp ('||
2546 ' lower_period, upper_period, year, last_period, periodicity_id)'||
2547 ' select cal.'||h_column_name||', 0, cal.year,'||
2548 ' case when cal.'||h_column_name||' = :1 and cal.year = :2 then :3'||
2549 ' when cal.year <> :4 and bal.'||h_column_name||' is not null then :5'||
2550 ' else :6 end case, :7'||
2551 ' from '||
2552 ' (select distinct '||h_column_name||', year'||
2553 ' from bsc_db_calendar'||
2554 ' where calendar_id = :8) cal,'||
2555 ' (select max('||h_column_name||') '||h_column_name||', year'||
2556 ' from bsc_db_calendar'||
2557 ' where calendar_id = :9'||
2558 ' group by year) bal'||
2559 ' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
2560 ' cal.year = bal.year (+)';
2561 execute immediate h_sql using x_current_period, x_current_fy, h_y, x_current_fy,
2562 h_y, h_n, h_up_periodicity, x_calendar_id, x_calendar_id;
2563 commit;
2564 ELSE
2565 -- Get current period in upper periodicity
2566 h_sql := 'select max('||h_up_column_name||')'||
2567 ' from bsc_db_calendar'||
2568 ' where calendar_id = :1 and '||h_column_name||' = :2 and year = :3';
2569 OPEN h_cursor FOR h_sql USING x_calendar_id, x_current_period, x_current_fy;
2570 FETCH h_cursor INTO h_up_current_period;
2571 CLOSE h_cursor;
2572
2573 h_sql := 'insert into bsc_db_calendar_temp ('||
2574 ' lower_period, upper_period, year, last_period, periodicity_id)'||
2575 ' select cal.'||h_column_name||', cal.'||h_up_column_name||', cal.year,'||
2576 ' case when cal.'||h_column_name||' = :1 and cal.year = :2 then :3'||
2577 ' when not (cal.'||h_up_column_name||' = :4 and cal.year = :5) and'||
2578 ' bal.'||h_column_name||' is not null then :6'||
2579 ' else :7 end case, :8'||
2580 ' from'||
2581 ' (select distinct '||h_column_name||', '||h_up_column_name||', year'||
2582 ' from bsc_db_calendar'||
2583 ' where calendar_id = :9) cal,'||
2584 ' (select max('||h_column_name||') '||h_column_name||', '||h_up_column_name||', year'||
2585 ' from bsc_db_calendar'||
2586 ' where calendar_id = :10'||
2587 ' group by '||h_up_column_name||', year) bal'||
2588 ' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
2589 ' cal.'||h_up_column_name||' = bal.'||h_up_column_name||' (+) and'||
2590 ' cal.year = bal.year (+)';
2591 execute immediate h_sql using x_current_period, x_current_fy, h_y, h_up_current_period,
2592 x_current_fy, h_y, h_n, h_up_periodicity, x_calendar_id, x_calendar_id;
2593 commit;
2594 END IF;
2595 END LOOP;
2596 CLOSE c_upper_periodicities;
2597
2598 x_return_status := 'success';
2599
2600 EXCEPTION
2601 WHEN OTHERS THEN
2602 x_return_status := 'error';
2603 x_error_message := 'Init_Bsc_Db_Calendar_Temp.e_others: '||SQLERRM;
2604
2605 END Init_Bsc_Db_Calendar_Temp;
2606
2607
2608 /*===========================================================================+
2609 | PROCEDURE Init_Bsc_Db_Calendar_Temp_Proj
2610 +============================================================================*/
2611 PROCEDURE Init_Bsc_Db_Calendar_Temp_Proj(
2612 x_base_table IN VARCHAR2,
2613 x_periodicity IN NUMBER,
2614 x_calendar_id IN NUMBER,
2615 x_current_period IN NUMBER,
2616 x_current_fy IN NUMBER,
2617 x_return_status OUT NOCOPY VARCHAR2,
2618 x_error_message OUT NOCOPY VARCHAR2
2619 ) IS
2620
2621 h_sql VARCHAR2(32000);
2622 TYPE t_cursor IS REF CURSOR;
2623 h_cursor t_cursor;
2624
2625 CURSOR c_upper_periodicities (p_table_name VARCHAR2, p_calc_type NUMBER) IS
2626 SELECT c.parameter1, p.yearly_flag, p.db_column_name
2627 FROM bsc_db_calculations c, bsc_sys_periodicities p
2628 WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
2629 c.parameter1 = p.periodicity_id;
2630
2631 h_column_name VARCHAR2(30);
2632 h_up_periodicity NUMBER;
2633 h_up_yearly_flag NUMBER;
2634 h_up_column_name VARCHAR2(30);
2635 h_up_current_period NUMBER;
2636 h_y VARCHAR2(1);
2637 h_n VARCHAR2(1);
2638
2639 BEGIN
2640 BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2641 commit;
2642
2643 h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2644 h_y := 'Y';
2645 h_n := 'N';
2646
2647 OPEN c_upper_periodicities(x_base_table, 6);
2648 LOOP
2649 FETCH c_upper_periodicities INTO h_up_periodicity, h_up_yearly_flag, h_up_column_name;
2650 EXIT WHEN c_upper_periodicities%NOTFOUND;
2651
2652 -- We do not insert yearly periodicity. Projection need to be recalculated for yearly.
2653 -- We do not want periods corresponding to actual
2654
2655 IF h_up_yearly_flag <> 1 THEN
2656 -- Get current period in upper periodicity
2657 h_sql := 'select max('||h_up_column_name||')'||
2658 ' from bsc_db_calendar'||
2659 ' where calendar_id = :1 and '||h_column_name||' = :2 and year = :3';
2660 OPEN h_cursor FOR h_sql USING x_calendar_id, x_current_period, x_current_fy;
2661 FETCH h_cursor INTO h_up_current_period;
2662 CLOSE h_cursor;
2663
2664 h_sql := 'insert into bsc_db_calendar_temp ('||
2665 ' lower_period, upper_period, year, last_period, periodicity_id)'||
2666 ' select cal.'||h_column_name||', cal.'||h_up_column_name||', cal.year,'||
2667 ' case when bal.'||h_column_name||' is not null then :1'||
2668 ' else :2 end case, :3'||
2669 ' from (select distinct '||h_column_name||', '||h_up_column_name||', year'||
2670 ' from bsc_db_calendar'||
2671 ' where calendar_id = :4 and year = :5 and '||h_up_column_name||' > :6) cal,'||
2672 ' (select max('||h_column_name||') '||h_column_name||', '||h_up_column_name||', year'||
2673 ' from bsc_db_calendar'||
2674 ' where calendar_id = :7 and year = :8 and '||h_up_column_name||' > :9'||
2675 ' group by '||h_up_column_name||', year) bal'||
2676 ' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
2677 ' cal.'||h_up_column_name||' = bal.'||h_up_column_name||' (+) and'||
2678 ' cal.year = bal.year (+)';
2679 execute immediate h_sql using h_y, h_n, h_up_periodicity, x_calendar_id, x_current_fy,
2680 h_up_current_period, x_calendar_id, x_current_fy, h_up_current_period;
2681 commit;
2682 END IF;
2683 END LOOP;
2684 CLOSE c_upper_periodicities;
2685
2686 x_return_status := 'success';
2687
2688 EXCEPTION
2689 WHEN OTHERS THEN
2690 x_return_status := 'error';
2691 x_error_message := 'Init_Bsc_Db_Calendar_Temp_Proj.e_others: '||SQLERRM;
2692
2693 END Init_Bsc_Db_Calendar_Temp_Proj;
2694
2695
2696 /*===========================================================================+
2697 | PROCEDURE Load_Input_Table_Inc
2698 +============================================================================*/
2699 PROCEDURE Load_Input_Table_Inc(
2700 x_base_table IN VARCHAR2,
2701 x_input_table IN VARCHAR2,
2702 x_aw_flag IN BOOLEAN,
2703 x_change_vector_value IN NUMBER,
2704 x_periodicity IN NUMBER,
2705 x_calendar_id IN NUMBER,
2706 x_current_period IN NUMBER,
2707 x_old_current_period IN NUMBER,
2708 x_current_fy IN NUMBER,
2709 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2710 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711 x_num_key_columns IN NUMBER,
2712 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2714 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2715 x_num_data_columns IN NUMBER,
2716 x_partition_name IN VARCHAR2,
2717 x_batch_value IN NUMBER,
2718 x_num_partitions IN NUMBER,
2719 x_parallel_jobs IN VARCHAR2,
2720 x_rowid_table IN VARCHAR2,
2721 x_num_loads IN NUMBER,
2722 x_return_status OUT NOCOPY VARCHAR2,
2723 x_error_message OUT NOCOPY VARCHAR2
2724 ) IS
2725
2726 e_create_proc_load_tbl_sum_aw EXCEPTION;
2727 e_create_proc_load_tbl_mv EXCEPTION;
2728 e_create_dynamic_proc_name EXCEPTION;
2729
2730 h_return_status VARCHAR2(50);
2731 h_error_message VARCHAR2(2000);
2732
2733 h_i NUMBER;
2734 h_sql VARCHAR2(32000);
2735 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2736
2737 h_level_table_name VARCHAR2(100);
2738 h_level_short_name VARCHAR2(300);
2739 h_level_source VARCHAR2(100);
2740 h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
2741
2742 h_proc_name VARCHAR2(30);
2743 h_proc_temp VARCHAR2(30);
2744 h_proc_occur NUMBER;
2745 h_proc_loop_size NUMBER;
2746 h_proc_count NUMBER; --rkumar:bug#5721341
2747 TYPE t_cursor IS REF CURSOR;
2748 h_cursor t_cursor;
2749
2750 BEGIN
2751
2752 FOR h_i IN 1..x_num_key_columns LOOP
2753 h_level_table_name := x_key_dim_tables(h_i);
2754 -- BSC-BIS-DIMENSIONS: If the dimension is a DBI dimension and it is materialized and
2755 -- the table exists then we use the table created in BSC to materialize
2756 -- the DBI dimension to translate from USER_CODE into CODE.
2757 -- Note that is only in MV Architecture.
2758 IF BSC_APPS.bsc_mv THEN
2759 SELECT short_name, source
2760 INTO h_level_short_name, h_level_source
2761 FROM bsc_sys_dim_levels_b
2762 WHERE level_view_name = h_level_table_name;
2763
2764 IF (h_level_source = 'PMF') AND (h_level_short_name IS NOT NULL) THEN
2765 BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);
2766 IF (h_dbi_dim_data.short_name IS NOT NULL) AND
2767 (h_dbi_dim_data.table_name IS NOT NULL) AND
2768 (h_dbi_dim_data.materialized='YES') THEN
2769 IF BSC_APPS.Table_Exists(h_dbi_dim_data.table_name) THEN
2770 h_level_table_name := h_dbi_dim_data.table_name;
2771 END IF;
2772 END IF;
2773 END IF;
2774 END IF;
2775 h_key_dim_tables(h_i) := h_level_table_name;
2776 END LOOP;
2777
2778 --rkumar:bug5721341 (Long input table names results in "character string buffer too small error")
2779 --h_proc_count is the counter based on which we are creating the dynamic procedure
2780 --h_proc_occur stores the number of time a particular request occurs (if at all)
2781 --h_proc_loop_size determines how many times should we loop (normally its 99
2782 --but in case of parallel jobs its 9 only
2783 h_proc_name := x_input_table;
2784 h_proc_loop_size :=99;
2785 h_proc_count :=0;
2786 h_proc_occur :=0;
2787 h_sql:='select count (*) from USER_OBJECTS where OBJECT_TYPE=''PROCEDURE'''||
2788 'and OBJECT_NAME=:1';
2789 IF x_parallel_jobs = 'Y' THEN
2790 h_proc_name := h_proc_name||substr(x_partition_name,instr(x_partition_name,'_')+1);
2791 h_proc_loop_size :=9;
2792 END IF;
2793 h_proc_temp :='LD$'||h_proc_name;
2794 --rkumar:check if the procedure is already there in the database.
2795 open h_cursor for h_sql using h_proc_temp;
2796 fetch h_cursor into h_proc_occur;
2797 close h_cursor;
2798 while h_proc_occur > 0 LOOP
2799 if (h_proc_count > h_proc_loop_size) then
2800 h_error_message:='Loader process can not create load procedure '||h_proc_name
2801 ||'as it already exists. Please contact your System Administrator or Oracle Support.';
2802 RAISE e_create_dynamic_proc_name;
2803 end if;
2804 h_proc_occur :=0;
2805 h_proc_temp := 'LD$'||h_proc_count||h_proc_name;
2806 h_proc_count := h_proc_count +1;
2807 open h_cursor for h_sql using h_proc_temp;
2808 fetch h_cursor into h_proc_occur;
2809 close h_cursor;
2810 END LOOP;
2811 h_proc_name := h_proc_temp; --rkumar:this is the final procedure name, now create it in the next line
2812
2813 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2814 -- MV architecture
2815 Create_Proc_Load_Tbl_MV(h_proc_name,
2816 x_base_table,
2817 x_input_table,
2818 x_periodicity,
2819 x_calendar_id,
2820 x_current_period,
2821 x_old_current_period,
2822 x_current_fy,
2823 x_key_columns,
2824 h_key_dim_tables,
2825 x_num_key_columns,
2826 x_data_columns,
2827 x_data_formulas,
2828 x_data_measure_types,
2829 x_num_data_columns,
2830 x_partition_name,
2831 x_batch_value,
2832 x_num_partitions,
2833 x_parallel_jobs,
2834 x_rowid_table,
2835 x_num_loads,
2836 h_return_status,
2837 h_error_message);
2838 IF h_return_status = 'error' THEN
2839 RAISE e_create_proc_load_tbl_mv;
2840 END IF;
2841
2842 ELSE
2843 -- Summary or AW architecture
2844 -- Create dynamically the stored procedure to load the input table
2845 Create_Proc_Load_Tbl_SUM_AW(h_proc_name,
2846 x_base_table,
2847 x_input_table,
2848 x_aw_flag,
2849 x_change_vector_value,
2850 x_periodicity,
2851 x_calendar_id,
2852 x_current_period,
2853 x_current_fy,
2854 x_key_columns,
2855 h_key_dim_tables,
2856 x_num_key_columns,
2857 x_data_columns,
2858 x_num_data_columns,
2859 x_partition_name,
2860 x_batch_value,
2861 x_num_partitions,
2862 x_parallel_jobs,
2863 x_rowid_table,
2864 x_num_loads,
2865 h_return_status,
2866 h_error_message);
2867 IF h_return_status = 'error' THEN
2868 RAISE e_create_proc_load_tbl_sum_aw;
2869 END IF;
2870 END IF;
2871
2872 -- Execute the stored procedure
2873 h_sql := 'BEGIN '||h_proc_name||'; END;';
2874 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2875 COMMIT;
2876
2877 -- Drop the stored procedure
2878 h_sql := 'DROP PROCEDURE '||h_proc_name;
2879 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2880 COMMIT;
2881 x_return_status := 'success';
2882
2883 EXCEPTION
2884 WHEN e_create_proc_load_tbl_sum_aw THEN
2885 rollback;
2886 x_return_status := 'error';
2887 x_error_message := 'Load_Input_Table_Inc.e_create_proc_load_tbl_sum_aw: '||h_error_message;
2888
2889 WHEN e_create_proc_load_tbl_mv THEN
2890 rollback;
2891 x_return_status := 'error';
2892 x_error_message := 'Load_Input_Table_Inc.e_create_proc_load_tbl_mv: '||h_error_message;
2893
2894 WHEN e_create_dynamic_proc_name THEN --rkumar:bug5721341
2895 rollback;
2896 x_return_status := 'error';
2897 x_error_message := 'Load_Input_Table_Inc.e_create_dynamic_proc_name: '||h_error_message;
2898
2899 WHEN OTHERS THEN
2900 rollback;
2901 x_return_status := 'error';
2902 x_error_message := 'Load_Input_Table_Inc.e_others: '||SQLERRM;
2903
2904 END Load_Input_Table_Inc;
2905
2906
2907
2908 /*===========================================================================+
2909 | PROCEDURE Load_Input_Table_Initial
2910 +============================================================================*/
2911 PROCEDURE Load_Input_Table_Initial(
2912 x_base_table IN VARCHAR2,
2913 x_input_table IN VARCHAR2,
2914 x_aw_flag IN BOOLEAN,
2915 x_change_vector_value IN NUMBER,
2916 x_periodicity IN NUMBER,
2917 x_calendar_id IN NUMBER,
2918 x_current_period IN NUMBER,
2919 x_current_fy IN NUMBER,
2920 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2921 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922 x_num_key_columns IN NUMBER,
2923 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2925 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2926 x_num_data_columns IN NUMBER,
2927 x_partition_name IN VARCHAR2,
2928 x_batch_value IN NUMBER,
2929 x_num_partitions IN NUMBER,
2930 x_parallel_jobs IN VARCHAR2,
2931 x_return_status OUT NOCOPY VARCHAR2,
2932 x_error_message OUT NOCOPY VARCHAR2
2933 ) IS
2934
2935 h_sql VARCHAR2(32000);
2936 h_i NUMBER;
2937 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2938
2939 h_level_table_name VARCHAR2(100);
2940 h_level_short_name VARCHAR2(300);
2941 h_level_source VARCHAR2(100);
2942 h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
2943
2944 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2945 h_num_bind_vars NUMBER;
2946
2947 h_calendar_source VARCHAR2(20);
2948 h_yearly_flag NUMBER;
2949 h_periodicity_type NUMBER;
2950
2951 BEGIN
2952 h_num_bind_vars := 0;
2953
2954 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2955 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2956 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2957
2958 FOR h_i IN 1..x_num_key_columns LOOP
2959 h_level_table_name := x_key_dim_tables(h_i);
2960 -- BSC-BIS-DIMENSIONS: If the dimension is a DBI dimension and it is materialized and
2961 -- the table exists then we use the table created in BSC to materialize
2962 -- the DBI dimension to translate from USER_CODE into CODE.
2963 -- Note that is only in MV Architecture.
2964 IF BSC_APPS.bsc_mv THEN
2965 SELECT short_name, source
2966 INTO h_level_short_name, h_level_source
2967 FROM bsc_sys_dim_levels_b
2968 WHERE level_view_name = h_level_table_name;
2969
2970 IF (h_level_source = 'PMF') AND (h_level_short_name IS NOT NULL) THEN
2971 BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);
2972 IF (h_dbi_dim_data.short_name IS NOT NULL) AND
2973 (h_dbi_dim_data.table_name IS NOT NULL) AND
2974 (h_dbi_dim_data.materialized='YES') THEN
2975 IF BSC_APPS.Table_Exists(h_dbi_dim_data.table_name) THEN
2976 h_level_table_name := h_dbi_dim_data.table_name;
2977 END IF;
2978 END IF;
2979 END IF;
2980 END IF;
2981 h_key_dim_tables(h_i) := h_level_table_name;
2982 END LOOP;
2983
2984 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2985 -- MV Architecture
2986 h_num_bind_vars := 0;
2987 h_bind_vars_values.delete;
2988
2989 h_sql := 'INSERT /*+ append';
2990 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
2991 h_sql := h_sql||' parallel ('||x_base_table||')';
2992 END IF;
2993 h_sql := h_sql||' */ INTO '||x_base_table;
2994 IF x_parallel_jobs = 'Y' THEN
2995 h_sql := h_sql||' PARTITION('||x_partition_name||')';
2996 END IF;
2997 h_sql := h_sql||' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
2998 FOR h_i IN 1..x_num_key_columns LOOP
2999 h_sql := h_sql||x_key_columns(h_i)||', ';
3000 END LOOP;
3001 h_sql := h_sql||'YEAR, TYPE, PERIOD, PERIODICITY_ID';
3002 FOR h_i IN 1..x_num_data_columns LOOP
3003 h_sql := h_sql||', '||x_data_columns(h_i);
3004 END LOOP;
3005 h_sql := h_sql||')'||
3006 ' WITH BSC_I_DATA AS ('||
3007 ' SELECT';
3008 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3009 h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
3010 FOR h_i IN 1..x_num_key_columns LOOP
3011 h_sql := h_sql||' parallel('||h_key_dim_tables(h_i)||')';
3012 END LOOP;
3013 IF h_calendar_source <> 'BSC' THEN
3014 -- Input table is using a BIS calendar
3015 h_sql := h_sql||' parallel(BSC_SYS_PERIODS)';
3016 END IF;
3017 h_sql := h_sql||' */';
3018 END IF;
3019 h_sql := h_sql||' '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3020 FOR h_i IN 1..x_num_key_columns LOOP
3021 h_sql := h_sql||h_key_dim_tables(h_i)||'.CODE '||x_key_columns(h_i)||', ';
3022 END LOOP;
3023 IF h_calendar_source = 'BSC' THEN
3024 h_sql := h_sql||x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD';
3025 ELSE
3026 -- BIS calendar
3027 IF h_yearly_flag = 1 THEN
3028 h_sql := h_sql||'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD';
3029 ELSE
3030 h_sql := h_sql||'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD';
3031 END IF;
3032 END IF;
3033 h_sql := h_sql||', :1 PERIODICITY_ID';
3034 h_num_bind_vars := h_num_bind_vars + 1;
3035 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3036 FOR h_i IN 1..x_num_data_columns LOOP
3037 h_sql := h_sql||', '||x_input_table||'.'||x_data_columns(h_i);
3038 END LOOP;
3039 h_sql := h_sql||' FROM (SELECT';
3040 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3041 h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3042 END IF;
3043 IF x_parallel_jobs = 'Y' THEN
3044 h_sql := h_sql||' :2 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3045 h_num_bind_vars := h_num_bind_vars + 1;
3046 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3047 ELSE
3048 IF x_num_partitions > 0 THEN
3049 h_sql := h_sql||' DBMS_UTILITY.Get_Hash_Value(';
3050 FOR h_i IN 1..x_num_key_columns LOOP
3051 IF h_i = 1 THEN
3052 h_sql := h_sql||x_key_columns(h_i);
3053 ELSE
3054 h_sql := h_sql||'||''.''||'||x_key_columns(h_i);
3055 END IF;
3056 END LOOP;
3057 h_sql := h_sql||', 0, :2) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3058 h_num_bind_vars := h_num_bind_vars + 1;
3059 h_bind_vars_values(h_num_bind_vars) := x_num_partitions;
3060 ELSE
3061 h_sql := h_sql||' :2 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3062 h_num_bind_vars := h_num_bind_vars + 1;
3063 h_bind_vars_values(h_num_bind_vars) := 0;
3064 END IF;
3065 END IF;
3066 h_sql := h_sql||', '||x_input_table||'.*'||
3067 ' FROM '||x_input_table;
3068 IF x_parallel_jobs = 'Y' THEN
3069 h_sql := h_sql||' WHERE DBMS_UTILITY.Get_Hash_Value(';
3070 FOR h_i IN 1..x_num_key_columns LOOP
3071 IF h_i = 1 THEN
3072 h_sql := h_sql||x_key_columns(h_i);
3073 ELSE
3074 h_sql := h_sql||'||''.''||'||x_key_columns(h_i);
3075 END IF;
3076 END LOOP;
3077 h_sql := h_sql||', 0, :3) = :4';
3078 h_num_bind_vars := h_num_bind_vars + 1;
3079 h_bind_vars_values(h_num_bind_vars) := x_num_partitions;
3080 h_num_bind_vars := h_num_bind_vars + 1;
3081 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3082 END IF;
3083 h_sql := h_sql||') '||x_input_table;
3084 FOR h_i IN 1..x_num_key_columns LOOP
3085 h_sql := h_sql||', '||h_key_dim_tables(h_i);
3086 END LOOP;
3087 IF h_calendar_source <> 'BSC' THEN
3088 -- BIS calendar
3089 h_sql := h_sql||', BSC_SYS_PERIODS';
3090 END IF;
3091 IF x_num_key_columns > 0 THEN
3092 h_sql := h_sql||' WHERE'||
3093 ' '||x_input_table||'.'||x_key_columns(1)||' = '||h_key_dim_tables(1)||'.USER_CODE';
3094 FOR h_i IN 2..x_num_key_columns LOOP
3095 h_sql := h_sql||' AND '||x_input_table||'.'||x_key_columns(h_i)||' = '||h_key_dim_tables(h_i)||'.USER_CODE';
3096 END LOOP;
3097 END IF;
3098 IF h_calendar_source <> 'BSC' THEN
3099 -- BIS calendar
3100 IF x_num_key_columns > 0 THEN
3101 h_sql := h_sql||' AND';
3102 ELSE
3103 h_sql := h_sql||' WHERE';
3104 END IF;
3105 IF h_periodicity_type = 9 THEN
3106 -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
3107 h_sql := h_sql||
3108 ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
3109 ' BSC_SYS_PERIODS.PERIODICITY_ID = :5';
3110 ELSE
3111 -- Other periodicity. TIME_FK is VARCHAR2
3112 h_sql := h_sql||
3113 ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
3114 ' BSC_SYS_PERIODS.PERIODICITY_ID = :5';
3115 END IF;
3116 h_num_bind_vars := h_num_bind_vars + 1;
3117 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3118 END IF;
3119 h_sql := h_sql||')'||
3120 ' SELECT';
3121 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3122 h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3123 END IF;
3124 h_sql := h_sql||' '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3125 FOR h_i IN 1..x_num_key_columns LOOP
3126 h_sql := h_sql||x_input_table||'.'||x_key_columns(h_i)||', ';
3127 END LOOP;
3128 h_sql := h_sql||x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD, '||
3129 x_input_table||'.PERIODICITY_ID';
3130 FOR h_i IN 1..x_num_data_columns LOOP
3131 h_sql := h_sql||', '||x_input_table||'.'||x_data_columns(h_i);
3132 END LOOP;
3133 h_sql := h_sql||' FROM ('||
3134 ' SELECT';
3135 -- Fix bug#5155523 Do not use parallel hint on BSC_I_DATA
3136 --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3137 -- h_sql := h_sql||' /*+ parallel (BSC_I_DATA) */';
3138 --END IF;
3139 h_sql := h_sql||' BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3140 FOR h_i IN 1..x_num_key_columns LOOP
3141 h_sql := h_sql||'BSC_I_DATA.'||x_key_columns(h_i)||', ';
3142 END LOOP;
3143 h_sql := h_sql||'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_I_DATA.PERIOD, BSC_I_DATA.PERIODICITY_ID';
3144 FOR h_i IN 1..x_num_data_columns LOOP
3145 h_sql := h_sql||', BSC_I_DATA.'||x_data_columns(h_i);
3146 END LOOP;
3147 h_sql := h_sql||' FROM BSC_I_DATA'||
3148 ' UNION ALL'||
3149 ' SELECT';
3150 -- Fix bug#5155523 Do not use parallel hint on BSC_I_DATA or BSC_DB_CALENDAR_TEMP
3151 --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3152 -- h_sql := h_sql||' /*+ parallel (BSC_I_DATA) parallel (BSC_DB_CALENDAR_TEMP) */';
3153 --END IF;
3154 --Fix bug#5155523 Do not use max(bsc_i_data.batch_column_name) instead add it to the group by
3155 h_sql := h_sql||' BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' '||
3156 BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3157 FOR h_i IN 1..x_num_key_columns LOOP
3158 h_sql := h_sql||'BSC_I_DATA.'||x_key_columns(h_i)||', ';
3159 END LOOP;
3160 h_sql := h_sql||'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD PERIOD,'||
3161 ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID';
3162 FOR h_i IN 1..x_num_data_columns LOOP
3163 IF x_data_measure_types(h_i) = 1 THEN
3164 -- Activity measure
3165 h_sql := h_sql||', '||x_data_formulas(h_i)||' '||x_data_columns(h_i);
3166 ELSE
3167 -- Balance measure
3168 h_sql := h_sql||', SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_data_columns(h_i)||', NULL))'||
3169 ' '||x_data_columns(h_i);
3170 END IF;
3171 END LOOP;
3172 h_sql := h_sql||' FROM BSC_I_DATA, BSC_DB_CALENDAR_TEMP'||
3173 ' WHERE BSC_I_DATA.PERIOD = BSC_DB_CALENDAR_TEMP.LOWER_PERIOD AND'||
3174 ' BSC_I_DATA.YEAR = BSC_DB_CALENDAR_TEMP.YEAR'||
3175 ' GROUP BY BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3176 FOR h_i IN 1..x_num_key_columns LOOP
3177 h_sql := h_sql||'BSC_I_DATA.'||x_key_columns(h_i)||', ';
3178 END LOOP;
3179 h_sql := h_sql||'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||
3180 ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID'||
3181 ') '||x_input_table;
3182
3183 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3184 COMMIT;
3185 ELSE
3186 -- Summary or AW architecture. No higher periodicities in the base table
3187 h_num_bind_vars := 0;
3188 h_bind_vars_values.delete;
3189
3190 h_sql := 'INSERT /*+ append';
3191 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3192 h_sql := h_sql||' parallel ('||x_base_table||')';
3193 END IF;
3194 h_sql := h_sql||' */ INTO '||x_base_table;
3195 IF x_parallel_jobs = 'Y' THEN
3196 h_sql := h_sql||' PARTITION('||x_partition_name||')';
3197 END IF;
3198 h_sql := h_sql||' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3199 FOR h_i IN 1..x_num_key_columns LOOP
3200 h_sql := h_sql||x_key_columns(h_i)||', ';
3201 END LOOP;
3202 h_sql := h_sql||'YEAR, TYPE, PERIOD';
3203 FOR h_i IN 1..x_num_data_columns LOOP
3204 h_sql := h_sql||', '||x_data_columns(h_i);
3205 END LOOP;
3206 IF x_aw_flag THEN
3207 h_sql := h_sql||', PROJECTION, CHANGE_VECTOR';
3208 END IF;
3209 h_sql := h_sql||')'||
3210 ' SELECT';
3211 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3212 h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
3213 FOR h_i IN 1..x_num_key_columns LOOP
3214 h_sql := h_sql||' parallel('||h_key_dim_tables(h_i)||')';
3215 END LOOP;
3216 IF h_calendar_source <> 'BSC' THEN
3217 -- Input table is using a BIS calendar
3218 h_sql := h_sql||' parallel(BSC_SYS_PERIODS)';
3219 END IF;
3220 h_sql := h_sql||' */';
3221 END IF;
3222 h_sql := h_sql||' '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3223 FOR h_i IN 1..x_num_key_columns LOOP
3224 h_sql := h_sql||h_key_dim_tables(h_i)||'.CODE, ';
3225 END LOOP;
3226 IF h_calendar_source = 'BSC' THEN
3227 h_sql := h_sql||x_input_table||'.YEAR, '||x_input_table||'.TYPE, '||x_input_table||'.PERIOD';
3228 ELSE
3229 -- BIS calendar
3230 IF h_yearly_flag = 1 THEN
3231 h_sql := h_sql||'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, 0 PERIOD';
3232 ELSE
3233 h_sql := h_sql||'BSC_SYS_PERIODS.YEAR, '||x_input_table||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID PERIOD';
3234 END IF;
3235 END IF;
3236 FOR h_i IN 1..x_num_data_columns LOOP
3237 h_sql := h_sql||', '||x_input_table||'.'||x_data_columns(h_i);
3238 END LOOP;
3239 IF x_aw_flag THEN
3240 IF h_calendar_source = 'BSC' THEN
3241 IF h_yearly_flag = 1 THEN
3242 h_sql := h_sql||', '||
3243 ' case when '||x_input_table||'.YEAR > :1'||
3244 ' then ''Y'' else ''N'' end PROJECTION';
3245 h_num_bind_vars := h_num_bind_vars + 1;
3246 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3247 ELSE
3248 h_sql := h_sql||', '||
3249 ' case when ('||x_input_table||'.YEAR = :1 AND '||
3250 x_input_table||'.PERIOD > :2) OR ('||
3251 x_input_table||'.YEAR > :3)'||
3252 ' then ''Y'' else ''N'' end PROJECTION';
3253 h_num_bind_vars := h_num_bind_vars + 1;
3254 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3255 h_num_bind_vars := h_num_bind_vars + 1;
3256 h_bind_vars_values(h_num_bind_vars) := x_current_period;
3257 h_num_bind_vars := h_num_bind_vars + 1;
3258 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3259 END IF;
3260 ELSE
3261 -- BIS calendar
3262 IF h_yearly_flag = 1 THEN
3263 h_sql := h_sql||', '||
3264 ' case when BSC_SYS_PERIODS.YEAR > :1'||
3265 ' then ''Y'' else ''N'' end PROJECTION';
3266 h_num_bind_vars := h_num_bind_vars + 1;
3267 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3268 ELSE
3269 h_sql := h_sql||', '||
3270 ' case when (BSC_SYS_PERIODS.YEAR = :1 AND '||
3271 'BSC_SYS_PERIODS.PERIOD_ID > :2) OR ('||
3272 'BSC_SYS_PERIODS.YEAR > :3)'||
3273 ' then ''Y'' else ''N'' end PROJECTION';
3274 h_num_bind_vars := h_num_bind_vars + 1;
3275 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3276 h_num_bind_vars := h_num_bind_vars + 1;
3277 h_bind_vars_values(h_num_bind_vars) := x_current_period;
3278 h_num_bind_vars := h_num_bind_vars + 1;
3279 h_bind_vars_values(h_num_bind_vars) := x_current_fy;
3280 END IF;
3281 END IF;
3282 h_sql := h_sql||', :4 CHANGE_VECTOR';
3283 h_num_bind_vars := h_num_bind_vars + 1;
3284 h_bind_vars_values(h_num_bind_vars) := x_change_vector_value;
3285 END IF;
3286 h_sql := h_sql||' FROM (SELECT';
3287 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3288 h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3289 END IF;
3290 IF x_parallel_jobs = 'Y' THEN
3291 h_sql := h_sql||' :5 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3292 h_num_bind_vars := h_num_bind_vars + 1;
3293 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3294 ELSE
3295 IF x_num_partitions > 0 THEN
3296 h_sql := h_sql||' DBMS_UTILITY.Get_Hash_Value(';
3297 FOR h_i IN 1..x_num_key_columns LOOP
3298 IF h_i = 1 THEN
3299 h_sql := h_sql||x_key_columns(h_i);
3300 ELSE
3301 h_sql := h_sql||'||''.''||'||x_key_columns(h_i);
3302 END IF;
3303 END LOOP;
3304 h_sql := h_sql||', 0, :5) '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3305 h_num_bind_vars := h_num_bind_vars + 1;
3306 h_bind_vars_values(h_num_bind_vars) := x_num_partitions;
3307 ELSE
3308 h_sql := h_sql||' :5 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
3309 h_num_bind_vars := h_num_bind_vars + 1;
3310 h_bind_vars_values(h_num_bind_vars) := 0;
3311 END IF;
3312 END IF;
3313 h_sql := h_sql||', '||x_input_table||'.*'||
3314 ' FROM '||x_input_table;
3315 IF x_parallel_jobs = 'Y' THEN
3316 h_sql := h_sql||' WHERE DBMS_UTILITY.Get_Hash_Value(';
3317 FOR h_i IN 1..x_num_key_columns LOOP
3318 IF h_i = 1 THEN
3319 h_sql := h_sql||x_key_columns(h_i);
3320 ELSE
3321 h_sql := h_sql||'||''.''||'||x_key_columns(h_i);
3322 END IF;
3323 END LOOP;
3324 h_sql := h_sql||', 0, :6) = :7';
3325 h_num_bind_vars := h_num_bind_vars + 1;
3326 h_bind_vars_values(h_num_bind_vars) := x_num_partitions;
3327 h_num_bind_vars := h_num_bind_vars + 1;
3328 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3329 END IF;
3330 h_sql := h_sql||') '||x_input_table;
3331 FOR h_i IN 1..x_num_key_columns LOOP
3332 h_sql := h_sql||', '||h_key_dim_tables(h_i);
3333 END LOOP;
3334 IF h_calendar_source <> 'BSC' THEN
3335 -- BIS calendar
3336 h_sql := h_sql||', BSC_SYS_PERIODS';
3337 END IF;
3338 IF x_num_key_columns > 0 THEN
3339 h_sql := h_sql||' WHERE'||
3340 ' '||x_input_table||'.'||x_key_columns(1)||' = '||h_key_dim_tables(1)||'.USER_CODE';
3341 FOR h_i IN 2..x_num_key_columns LOOP
3342 h_sql := h_sql||' AND '||x_input_table||'.'||x_key_columns(h_i)||' = '||h_key_dim_tables(h_i)||'.USER_CODE';
3343 END LOOP;
3344 END IF;
3345 IF h_calendar_source <> 'BSC' THEN
3346 -- BIS calendar
3347 IF x_num_key_columns > 0 THEN
3348 h_sql := h_sql||' AND';
3349 ELSE
3350 h_sql := h_sql||' WHERE';
3351 END IF;
3352 IF h_periodicity_type = 9 THEN
3353 -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
3354 h_sql := h_sql||
3355 ' TRUNC('||x_input_table||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND'||
3356 ' BSC_SYS_PERIODS.PERIODICITY_ID = :8';
3357 ELSE
3358 -- Other periodicity. TIME_FK is VARCHAR2
3359 h_sql := h_sql||
3360 ' '||x_input_table||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND'||
3361 ' BSC_SYS_PERIODS.PERIODICITY_ID = :8';
3362 END IF;
3363 h_num_bind_vars := h_num_bind_vars + 1;
3364 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3365 END IF;
3366
3367 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3368 COMMIT;
3369 END IF;
3370
3371 x_return_status := 'success';
3372
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375 rollback;
3376 x_return_status := 'error';
3377 x_error_message := 'Load_Input_Table_Initial.e_others: '||SQLERRM;
3378 END Load_Input_Table_Initial;
3379
3380
3381 /*===========================================================================+
3382 | PROCEDURE Calc_Higher_Periodicities
3383 +============================================================================*/
3384 PROCEDURE Calc_Higher_Periodicities(
3385 x_base_table IN VARCHAR2,
3386 x_periodicity IN NUMBER,
3387 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3388 x_num_key_columns IN NUMBER,
3389 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3390 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3391 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3392 x_num_data_columns IN NUMBER,
3393 x_partition_name IN VARCHAR2,
3394 x_batch_value IN NUMBER,
3395 x_parallel_jobs IN VARCHAR2,
3396 x_return_status OUT NOCOPY VARCHAR2,
3397 x_error_message OUT NOCOPY VARCHAR2
3398 ) IS
3399
3400 h_sql VARCHAR2(32000);
3401 h_i NUMBER;
3402 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
3403 h_num_bind_vars NUMBER;
3404
3405 BEGIN
3406 -- Insert higher periodicities
3407 h_bind_vars_values.delete;
3408 h_num_bind_vars := 0;
3409
3410 h_sql := 'INSERT /*+ append';
3411 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3412 h_sql := h_sql||' parallel ('||x_base_table||')';
3413 END IF;
3414 h_sql := h_sql||' */ INTO '||x_base_table;
3415 IF x_parallel_jobs = 'Y' THEN
3416 h_sql := h_sql||' PARTITION('||x_partition_name||')';
3417 END IF;
3418 h_sql := h_sql||' ('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3419 FOR h_i IN 1..x_num_key_columns LOOP
3420 h_sql := h_sql||x_key_columns(h_i)||', ';
3421 END LOOP;
3422 h_sql := h_sql||'YEAR, TYPE, PERIOD, PERIODICITY_ID';
3423 FOR h_i IN 1..x_num_data_columns LOOP
3424 h_sql := h_sql||', '||x_data_columns(h_i);
3425 END LOOP;
3426 h_sql := h_sql||')'||
3427 ' SELECT';
3428 IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3429 -- Fix bug#5155523 Do not use parallel hint on BSC_DB_CALENDAR_TEMP
3430 h_sql := h_sql||' /*+ parallel (BSC_B_DATA) */';
3431 END IF;
3432 h_sql := h_sql||' MAX(BSC_B_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') '||
3433 BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3434 FOR h_i IN 1..x_num_key_columns LOOP
3435 h_sql := h_sql||'BSC_B_DATA.'||x_key_columns(h_i)||', ';
3436 END LOOP;
3437 h_sql := h_sql||'BSC_B_DATA.YEAR, BSC_B_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD PERIOD,'||
3438 ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID';
3439 FOR h_i IN 1..x_num_data_columns LOOP
3440 IF x_data_measure_types(h_i) = 1 THEN
3441 -- Activity measure
3442 h_sql := h_sql||', '||x_data_formulas(h_i)||' '||x_data_columns(h_i);
3443 ELSE
3444 -- Balance measure
3445 h_sql := h_sql||', SUM(DECODE(BSC_DB_CALENDAR_TEMP.LAST_PERIOD,''Y'','||x_data_columns(h_i)||', NULL))'||
3446 ' '||x_data_columns(h_i);
3447 END IF;
3448 END LOOP;
3449 h_sql := h_sql||' FROM '||x_base_table||' BSC_B_DATA, BSC_DB_CALENDAR_TEMP'||
3450 ' WHERE BSC_B_DATA.PERIODICITY_ID = :1'||
3451 ' AND BSC_B_DATA.PERIOD = BSC_DB_CALENDAR_TEMP.LOWER_PERIOD'||
3452 ' AND BSC_B_DATA.YEAR = BSC_DB_CALENDAR_TEMP.YEAR';
3453 h_num_bind_vars := h_num_bind_vars + 1;
3454 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3455 IF x_parallel_jobs = 'Y' THEN
3456 h_sql := h_sql||' AND BSC_B_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :2';
3457 h_num_bind_vars := h_num_bind_vars + 1;
3458 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3459 END IF;
3460 h_sql := h_sql||' GROUP BY ';
3461 FOR h_i IN 1..x_num_key_columns LOOP
3462 h_sql := h_sql||'BSC_B_DATA.'||x_key_columns(h_i)||', ';
3463 END LOOP;
3464 h_sql := h_sql||'BSC_B_DATA.YEAR, BSC_B_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||
3465 ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID';
3466 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3467 COMMIT;
3468
3469 x_return_status := 'success';
3470
3471 EXCEPTION
3472 WHEN OTHERS THEN
3473 rollback;
3474 x_return_status := 'error';
3475 x_error_message := 'Calc_Higher_Periodicities.e_others: '||SQLERRM;
3476
3477 END Calc_Higher_Periodicities;
3478
3479
3480 /*===========================================================================+
3481 | PROCEDURE Update_Base_Table_Job
3482 +============================================================================*/
3483 PROCEDURE Update_Base_Table_Job (
3484 x_base_table IN VARCHAR2,
3485 x_input_table IN VARCHAR2,
3486 x_correction_flag IN BOOLEAN,
3487 x_aw_flag IN BOOLEAN,
3488 x_change_vector_value IN NUMBER,
3489 x_periodicity IN NUMBER,
3490 x_calendar_id IN NUMBER,
3491 x_current_fy IN NUMBER,
3492 x_current_period IN NUMBER,
3493 x_old_current_period IN NUMBER,
3494 x_proj_table IN VARCHAR2,
3495 x_rowid_table IN VARCHAR2,
3496 x_partition_name IN VARCHAR2,
3497 x_batch_value IN NUMBER,
3498 x_num_partitions IN NUMBER,
3499 x_num_loads IN NUMBER,
3500 x_job_name IN VARCHAR2
3501 ) IS
3502
3503 h_return_status VARCHAR2(50);
3504 h_error_message VARCHAR2(2000);
3505 h_sql VARCHAR2(32000);
3506
3507 e_error_calc_base_table EXCEPTION;
3508 e_get_info_data_columns EXCEPTION;
3509 e_get_info_key_columns EXCEPTION;
3510 e_unexpected_error EXCEPTION;
3511
3512 h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3513 h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
3514 h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515 h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516 h_num_data_columns NUMBER;
3517
3518 h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3520 h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3521 h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3522 h_num_key_columns NUMBER;
3523
3524 BEGIN
3525
3526 --- Note that each job runs in a new session, we need to initialized session variables
3527 h_num_key_columns := 0;
3528 h_num_data_columns := 0;
3529
3530 -- Initialize BSC/APPS global variables
3531 BSC_APPS.Init_Bsc_Apps;
3532
3533 -- Initialize the temporal table used for big 'in' conditions
3534 BSC_APPS.Init_Big_In_Cond_Table;
3535 BSC_MESSAGE.Init('NO');
3536
3537 -- Initializes g_session_id, g_user_id and g_schema_name
3538 IF NOT BSC_UPDATE.Init_Env_Values THEN
3539 RAISE e_unexpected_error;
3540 END IF;
3541
3542 /*
3543 -- TRACE ----------------------------------------------------------------
3544 -- Set sql trace
3545 execute immediate 'alter session set MAX_DUMP_FILE_SIZE=UNLIMITED';
3546 execute immediate 'alter session set tracefile_identifier='''||x_base_table||'_'||x_partition_name||'''';
3547 execute immediate 'alter session set sql_trace=true';
3548 --execute immediate 'alter session set events= ''10046 trace name context forever, level 12''';
3549 -- ----------------------------------------------------------------------------------
3550 */
3551
3552 h_sql := 'alter session set hash_area_size=50000000';
3553 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3554
3555 h_sql := 'alter session set sort_area_size=50000000';
3556 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3557
3558 IF BSC_UPDATE_UTIL.is_parallel THEN
3559 COMMIT;
3560 h_sql := 'alter session enable parallel dml';
3561 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3562 COMMIT;
3563 END IF;
3564
3565 -- Get data columns
3566 IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
3567 h_data_columns,
3568 h_data_formulas,
3569 h_data_proj_methods,
3570 h_data_measure_types,
3571 h_num_data_columns) THEN
3572 RAISE e_get_info_data_columns;
3573 END IF;
3574
3575 -- Get key columns
3576 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
3577 h_key_columns,
3578 h_key_dim_tables,
3579 h_source_columns,
3580 h_source_dim_tables,
3581 h_num_key_columns) THEN
3582 RAISE e_get_info_key_columns;
3583 END IF;
3584
3585 Update_Base_Table(x_base_table,
3586 x_input_table,
3587 x_correction_flag,
3588 x_aw_flag,
3589 x_change_vector_value,
3590 x_periodicity,
3591 x_calendar_id,
3592 x_current_fy,
3593 x_current_period,
3594 x_old_current_period,
3595 h_key_columns,
3596 h_key_dim_tables,
3597 h_num_key_columns,
3598 h_data_columns,
3599 h_data_formulas,
3600 h_data_proj_methods,
3601 h_data_measure_types,
3602 h_num_data_columns,
3603 x_proj_table,
3604 x_rowid_table,
3605 x_partition_name,
3606 x_batch_value,
3607 x_num_partitions,
3608 x_num_loads,
3609 'Y',
3610 h_return_status,
3611 h_error_message);
3612
3613 IF h_return_status = 'error' THEN
3614 RAISE e_error_calc_base_table;
3615 END IF;
3616
3617 bsc_aw_utility.send_pipe_message(x_job_name, 'status=success');
3618
3619 EXCEPTION
3620 WHEN e_get_info_data_columns THEN
3621 rollback;
3622 bsc_aw_utility.send_pipe_message(x_job_name, 'status=error,message=e_get_info_data_columns');
3623
3624 WHEN e_get_info_key_columns THEN
3625 rollback;
3626 bsc_aw_utility.send_pipe_message(x_job_name, 'status=error,message=e_get_info_key_columns');
3627
3628 WHEN e_error_calc_base_table THEN
3629 rollback;
3630 bsc_aw_utility.send_pipe_message(x_job_name, 'status=error,message='||h_error_message);
3631
3632 WHEN e_unexpected_error THEN
3633 rollback;
3634 bsc_aw_utility.send_pipe_message(x_job_name, 'status=error,message=e_unexpected_error');
3635
3636 WHEN OTHERS THEN
3637 rollback;
3638 bsc_aw_utility.send_pipe_message(x_job_name, 'status=error,message='||SQLERRM);
3639
3640 END Update_Base_Table_Job;
3641
3642
3643 /*===========================================================================+
3644 | PROCEDURE Update_Base_Table
3645 +============================================================================*/
3646 PROCEDURE Update_Base_Table (
3647 x_base_table IN VARCHAR2,
3648 x_input_table IN VARCHAR2,
3649 x_correction_flag IN BOOLEAN,
3650 x_aw_flag IN BOOLEAN,
3651 x_change_vector_value IN NUMBER,
3652 x_periodicity IN NUMBER,
3653 x_calendar_id IN NUMBER,
3654 x_current_fy IN NUMBER,
3655 x_current_period IN NUMBER,
3656 x_old_current_period IN NUMBER,
3657 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3658 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659 x_num_key_columns IN NUMBER,
3660 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
3663 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3664 x_num_data_columns IN NUMBER,
3665 x_proj_table IN VARCHAR2,
3666 x_rowid_table IN VARCHAR2,
3667 x_partition_name IN VARCHAR2,
3668 x_batch_value IN NUMBER,
3669 x_num_partitions IN NUMBER,
3670 x_num_loads IN NUMBER,
3671 x_parallel_jobs IN VARCHAR2,
3672 x_return_status OUT NOCOPY VARCHAR2,
3673 x_error_message OUT NOCOPY VARCHAR2
3674 ) IS
3675
3676 e_init_calendar_temp_tbl EXCEPTION;
3677 e_load_input_table_initial EXCEPTION;
3678 e_load_input_table_inc EXCEPTION;
3679 e_calc_higher_periodicities EXCEPTION;
3680 e_init_calendar_temp_tbl_proj EXCEPTION;
3681 e_calc_projection EXCEPTION;
3682
3683 h_return_status VARCHAR2(50);
3684 h_error_message VARCHAR2(2000);
3685 h_sql VARCHAR2(32000);
3686 TYPE t_cursor IS REF CURSOR;
3687 h_cursor t_cursor;
3688 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
3689 h_num_bind_vars NUMBER;
3690 h_num_rows NUMBER;
3691 h_projection_flag VARCHAR2(3);
3692 h_yearly_periodicity NUMBER;
3693 h_yearly_flag NUMBER;
3694
3695 BEGIN
3696
3697 h_num_bind_vars := 0;
3698 h_num_rows := 0;
3699
3700 -- Init temporary table BSC_DB_CALENDAR_TEMP to do the rollup to higher periodicities
3701 -- in MV architecture
3702 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
3703 Init_Bsc_Db_Calendar_Temp(x_base_table,
3704 x_periodicity,
3705 x_calendar_id,
3706 x_current_period,
3707 x_current_fy,
3708 h_return_status,
3709 h_error_message);
3710 IF h_return_status = 'error' THEN
3711 RAISE e_init_calendar_temp_tbl;
3712 END IF;
3713 END IF;
3714
3715 -- Load data from the input table
3716 IF NOT x_correction_flag THEN
3717 -- Know if the base table has data or not
3718 h_sql := 'select count(*) from '||x_base_table||' where rownum < :1';
3719 IF x_parallel_jobs = 'Y' THEN
3720 h_sql := h_sql||' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :2';
3721 OPEN h_cursor FOR h_sql USING 2, x_batch_value;
3722 ELSE
3723 OPEN h_cursor FOR h_sql USING 2;
3724 END IF;
3725 FETCH h_cursor INTO h_num_rows;
3726 CLOSE h_cursor;
3727
3728 IF h_num_rows > 0 THEN
3729 -- Base table has data --> Incremental Load
3730
3731 -- AW_INTEGRATION: PROJECTION flag is now set to Y for all the rows beyond
3732 -- current period no matter if it is target or actual. So if the current period changes
3733 -- we need to update to N between old current period and new current period for type <> 0
3734 IF x_aw_flag THEN
3735 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
3736 IF (h_yearly_flag <> 1) AND (x_current_period > x_old_current_period) THEN
3737 h_num_bind_vars := 0;
3738 h_bind_vars_values.delete;
3739
3740 h_sql := 'UPDATE '||x_base_table;
3741 IF x_parallel_jobs = 'Y' THEN
3742 h_sql := h_sql||' partition('||x_partition_name||')';
3743 END IF;
3744 h_sql := h_sql||
3745 ' SET projection = ''N'', change_vector = :1'||
3746 ' WHERE YEAR = :2 AND PERIOD > :3 AND PERIOD <= :4 AND TYPE <> :5';
3747 h_bind_vars_values(1) := x_change_vector_value;
3748 h_bind_vars_values(2) := x_current_fy;
3749 h_bind_vars_values(3) := x_old_current_period;
3750 h_bind_vars_values(4) := x_current_period;
3751 h_bind_vars_values(5) := 0;
3752 h_num_bind_vars := 5;
3753 IF x_parallel_jobs = 'Y' THEN
3754 h_sql := h_sql||' AND '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6';
3755 h_num_bind_vars := h_num_bind_vars + 1;
3756 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3757 END IF;
3758 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3759 commit;
3760 END IF;
3761 END IF;
3762
3763 Load_Input_Table_Inc(x_base_table,
3764 x_input_table,
3765 x_aw_flag,
3766 x_change_vector_value,
3767 x_periodicity,
3768 x_calendar_id,
3769 x_current_period,
3770 x_old_current_period,
3771 x_current_fy,
3772 x_key_columns,
3773 x_key_dim_tables,
3774 x_num_key_columns,
3775 x_data_columns,
3776 x_data_formulas,
3777 x_data_measure_types,
3778 x_num_data_columns,
3779 x_partition_name,
3780 x_batch_value,
3781 x_num_partitions,
3782 x_parallel_jobs,
3783 x_rowid_table,
3784 x_num_loads,
3785 h_return_status,
3786 h_error_message);
3787 IF h_return_status = 'error' THEN
3788 RAISE e_load_input_table_inc;
3789 END IF;
3790 ELSE
3791 -- Base table is empty --> Initial Load
3792 Load_Input_Table_Initial(x_base_table,
3793 x_input_table,
3794 x_aw_flag,
3795 x_change_vector_value,
3796 x_periodicity,
3797 x_calendar_id,
3798 x_current_period,
3799 x_current_fy,
3800 x_key_columns,
3801 x_key_dim_tables,
3802 x_num_key_columns,
3803 x_data_columns,
3804 x_data_formulas,
3805 x_data_measure_types,
3806 x_num_data_columns,
3807 x_partition_name,
3808 x_batch_value,
3809 x_num_partitions,
3810 x_parallel_jobs,
3811 h_return_status,
3812 h_error_message);
3813 IF h_return_status = 'error' THEN
3814 RAISE e_load_input_table_initial;
3815 END IF;
3816 END IF;
3817 ELSE
3818 -- No data coming from the input table
3819 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
3820 -- In MV architecture we need to re-rollup to higher periodicities since the aggregation function
3821 -- or the balance type could have changed. We re-rollup the entire table
3822
3823 -- Delete rows for highwer periodicities
3824 h_num_bind_vars := 0;
3825 h_sql := 'DELETE FROM '||x_base_table;
3826 IF x_parallel_jobs = 'Y' THEN
3827 h_sql := h_sql||' partition('||x_partition_name||')';
3828 END IF;
3829 h_sql := h_sql||' WHERE PERIODICITY_ID <> :1';
3830 h_num_bind_vars := h_num_bind_vars + 1;
3831 h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3832 IF x_parallel_jobs = 'Y' THEN
3833 h_sql := h_sql||' AND '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :2';
3834 h_num_bind_vars := h_num_bind_vars + 1;
3835 h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3836 END IF;
3837 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3838 COMMIT;
3839
3840 -- Insert rows for higer periodicities
3841 Calc_Higher_Periodicities(x_base_table,
3842 x_periodicity,
3843 x_key_columns,
3844 x_num_key_columns,
3845 x_data_columns,
3846 x_data_formulas,
3847 x_data_measure_types,
3848 x_num_data_columns,
3849 x_partition_name,
3850 x_batch_value,
3851 x_parallel_jobs,
3852 h_return_status,
3853 h_error_message);
3854 IF h_return_status = 'error' THEN
3855 RAISE e_calc_higher_periodicities;
3856 END IF;
3857 END IF;
3858 END IF;
3859
3860 -- Analyze the B table partition
3861 IF x_parallel_jobs = 'Y' THEN
3862 dbms_stats.gather_table_stats(
3863 ownname => BSC_APPS.BSC_APPS_SCHEMA,
3864 tabname => x_base_table,
3865 partname => x_partition_name,
3866 estimate_percent => 1);
3867 ELSE
3868 dbms_stats.gather_table_stats(
3869 ownname => BSC_APPS.BSC_APPS_SCHEMA,
3870 tabname => x_base_table);
3871 END IF;
3872 commit;
3873
3874 -- Calculate projection
3875 -- Note that the projection table is empty. It was truncated already outside this procedure.
3876 IF BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_base_table) THEN
3877 -- Calculate projection for base periodicity
3878 Calc_Projection(x_base_table,
3879 x_proj_table,
3880 x_aw_flag,
3881 x_change_vector_value,
3882 x_periodicity,
3883 x_calendar_id,
3884 x_current_period,
3885 x_current_fy,
3886 x_key_columns,
3887 x_num_key_columns,
3888 x_data_columns,
3889 x_data_proj_methods,
3890 x_num_data_columns,
3891 x_partition_name,
3892 x_batch_value,
3893 x_parallel_jobs,
3894 h_return_status,
3895 h_error_message);
3896 IF h_return_status = 'error' THEN
3897 RAISE e_calc_projection;
3898 END IF;
3899
3900 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
3901 -- Init temporary table BSC_DB_CALENDAR_TEMP to do the rollup to higher periodicities
3902 -- for projection.
3903 Init_Bsc_Db_Calendar_Temp_Proj(x_base_table,
3904 x_periodicity,
3905 x_calendar_id,
3906 x_current_period,
3907 x_current_fy,
3908 h_return_status,
3909 h_error_message);
3910 IF h_return_status = 'error' THEN
3911 RAISE e_init_calendar_temp_tbl_proj;
3912 END IF;
3913
3914 -- In MV architecture we need to rollup the projection table
3915 -- to higher periodicities different from yearly
3916 Calc_Higher_Periodicities(x_proj_table,
3917 x_periodicity,
3918 x_key_columns,
3919 x_num_key_columns,
3920 x_data_columns,
3921 x_data_formulas,
3922 x_data_measure_types,
3923 x_num_data_columns,
3924 x_partition_name,
3925 x_batch_value,
3926 x_parallel_jobs,
3927 h_return_status,
3928 h_error_message);
3929 IF h_return_status = 'error' THEN
3930 RAISE e_calc_higher_periodicities;
3931 END IF;
3932
3933 -- Calculate projection for yearly periodicity if this is the case
3934 -- See if yearly is one of the higher periodicities
3935 BEGIN
3936 select c.parameter1
3937 into h_yearly_periodicity
3938 from bsc_db_calculations c, bsc_sys_periodicities p
3939 where c.table_name = x_base_table and
3940 c.calculation_type = 6 and
3941 c.parameter1 = p.periodicity_id and
3942 p.yearly_flag = 1;
3943 EXCEPTION
3944 WHEN NO_DATA_FOUND THEN
3945 h_yearly_periodicity := NULL;
3946 END;
3947 IF h_yearly_periodicity IS NOT NULL THEN
3948 Calc_Projection(x_base_table,
3949 x_proj_table,
3950 x_aw_flag,
3951 x_change_vector_value,
3952 h_yearly_periodicity,
3953 x_calendar_id,
3954 x_current_fy,
3955 x_current_fy,
3956 x_key_columns,
3957 x_num_key_columns,
3958 x_data_columns,
3959 x_data_proj_methods,
3960 x_num_data_columns,
3961 x_partition_name,
3962 x_batch_value,
3963 x_parallel_jobs,
3964 h_return_status,
3965 h_error_message);
3966 IF h_return_status = 'error' THEN
3967 RAISE e_calc_projection;
3968 END IF;
3969 END IF;
3970 END IF;
3971 END IF;
3972
3973 commit;
3974 x_return_status := 'success';
3975
3976 EXCEPTION
3977 WHEN e_init_calendar_temp_tbl THEN
3978 rollback;
3979 x_return_status := 'error';
3980 x_error_message := 'Update_Base_Table.e_init_calendar_temp_tbl: '||h_error_message;
3981
3982 WHEN e_load_input_table_initial THEN
3983 rollback;
3984 x_return_status := 'error';
3985 x_error_message := 'Update_Base_Table.e_load_input_table_initial: '||h_error_message;
3986
3987 WHEN e_load_input_table_inc THEN
3988 rollback;
3989 x_return_status := 'error';
3990 x_error_message := 'Update_Base_Table.e_load_input_table_inc: '||h_error_message;
3991
3992 WHEN e_calc_higher_periodicities THEN
3993 rollback;
3994 x_return_status := 'error';
3995 x_error_message := 'Update_Base_Table.e_calc_higher_periodicities: '||h_error_message;
3996
3997 WHEN e_init_calendar_temp_tbl_proj THEN
3998 rollback;
3999 x_return_status := 'error';
4000 x_error_message := 'Update_Base_Table.e_init_calendar_temp_tbl_proj: '||h_error_message;
4001
4002 WHEN e_calc_projection THEN
4003 rollback;
4004 x_return_status := 'error';
4005 x_error_message := 'Update_Base_Table.e_calc_projection: '||h_error_message;
4006
4007 WHEN OTHERS THEN
4008 rollback;
4009 x_return_status := 'error';
4010 x_error_message := 'Update_Base_Table.e_others: '||SQLERRM;
4011
4012 END Update_Base_Table;
4013
4014 END BSC_UPDATE_BASE_V2;