1 PACKAGE BODY BSC_UPDATE_CALC AS
2 /* $Header: BSCDCALB.pls 120.6 2006/03/07 13:37:07 meastmon noship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION Apply_Filters
7 +============================================================================*/
8 FUNCTION Apply_Filters(
9 x_table_name IN VARCHAR2
10 ) RETURN BOOLEAN IS
11
12 e_unexpected_error EXCEPTION;
13
14 -- If a table is used directly by an indicator, this table is in BSC_KPI_DATA_TABLES
15 -- Additionally, a table is used by an indicator if it is connected to an indicator table
16 -- by calculation type = 5 (Merge targets)
17 -- We need to filter target tables.
18 TYPE t_cursor IS REF CURSOR;
19
20 /* c_indicator t_cursor;
21 c_indicator_sql VARCHAR2(2000) := 'SELECT indicator, dim_set_id'||
22 ' FROM bsc_kpi_data_tables'||
23 ' WHERE table_name = :1 OR'||
24 ' table_name = ('||
25 ' SELECT DISTINCT table_name'||
26 ' FROM bsc_db_calculations'||
27 ' WHERE parameter1 = :2 AND'||
28 ' calculation_type = :3)'; */
29 cursor c_indicator( pTableName varchar2, pParam1 varchar2,pCalcType number) is
30 SELECT indicator, dim_set_id
31 FROM bsc_kpi_data_tables
32 WHERE table_name = pTableName
33 OR table_name = (
34 SELECT DISTINCT table_name
35 FROM bsc_db_calculations
36 WHERE parameter1 = pParam1
37 AND calculation_type = pCalcType );
38 h_calculation_type NUMBER;
39
40 h_indicator NUMBER;
41 h_dim_set_id NUMBER;
42
43 /* c_filters t_cursor;
44 c_filters_sql VARCHAR2(2000) := 'SELECT d.level_pk_col, d.level_view_name'||
45 ' FROM bsc_kpi_dim_levels_b d, bsc_db_tables_cols c'||
46 ' WHERE d.indicator = :1 AND d.dim_set_id = :2 AND d.status = :3 AND'||
47 ' d.level_view_name <> ('||
48 ' SELECT level_view_name'||
49 ' FROM bsc_sys_dim_levels_b s'||
50 ' WHERE d.level_pk_col = s.level_pk_col) AND'||
51 ' c.table_name = :4 AND'||
52 ' c.column_name = d.level_pk_col AND'||
53 ' c.column_type = :5'; */
54 cursor c_filters(pIndicator number,pDimSetId number , pStatus number,
55 pTableName varchar2,pColumnType varchar2) is
56 SELECT d.level_pk_col, d.level_view_name
57 FROM bsc_kpi_dim_levels_b d, bsc_db_tables_cols c
58 WHERE d.indicator = pIndicator AND d.dim_set_id = pDimSetId
59 AND d.status = pStatus
60 AND d.level_view_name <> (SELECT level_view_name
61 FROM bsc_sys_dim_levels_b s
62 WHERE d.level_pk_col = s.level_pk_col)
63 AND c.table_name = pTableName
64 AND c.column_name = d.level_pk_col
65 AND c.column_type = pColumnType;
66
67 h_status NUMBER;
68 h_column_type VARCHAR2(1);
69
70 h_key_column_name VARCHAR2(30);
71 h_view_name VARCHAR2(30);
72
73 h_sql VARCHAR2(32700);
74
75 BEGIN
76
77 h_calculation_type := 5;
78 h_status := 2;
79 h_column_type := 'P';
80
81 -- Get the indicator and dimension set that uses the table
82 -- OPEN c_indicator FOR c_indicator_sql USING x_table_name, x_table_name, h_calculation_type;
83 OPEN c_indicator(x_table_name,x_table_name,h_calculation_type);
84 FETCH c_indicator INTO h_indicator, h_dim_set_id;
85 IF c_indicator%NOTFOUND THEN
86 -- The table is not used by any indicator. So, the table doesn't have filter.
87 CLOSE c_indicator;
88 RETURN TRUE;
89 END IF;
90 CLOSE c_indicator;
91
92 -- Get the key columns and correspondig filter views for the table.
93 -- OPEN c_filters FOR c_filters_sql USING h_indicator, h_dim_set_id, h_status, x_table_name, h_column_type;
94 OPEN c_filters(h_indicator,h_dim_set_id,h_status,x_table_name,h_column_type);
95
96 FETCH c_filters INTO h_key_column_name, h_view_name;
97 WHILE c_filters%FOUND LOOP
98 -- Delete from table those records that dont belong to filter view
99 h_sql := 'DELETE FROM '||x_table_name||
100 ' WHERE '||h_key_column_name||' NOT IN ('||
101 ' SELECT CODE FROM '||h_view_name||
102 ')';
103 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
104
105 FETCH c_filters INTO h_key_column_name, h_view_name;
106 END LOOP;
107 CLOSE c_filters;
108
109
110 RETURN TRUE;
111 EXCEPTION
112 WHEN e_unexpected_error THEN
113 BSC_MESSAGE.Add(
114 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_APPLY_FILTER_FAILED'),
115 X_Source => 'BSC_UPDATE_CALC.Apply_Filters');
116 RETURN FALSE;
117
118 WHEN OTHERS THEN
119 BSC_MESSAGE.Add(
120 X_Message => SQLERRM,
121 X_Source => 'BSC_UPDATE_CALC.Apply_Filters');
122 RETURN FALSE;
123 END Apply_Filters;
124
125
126 /*===========================================================================+
127 | FUNCTION Calculate_Profit
128 +============================================================================*/
129 FUNCTION Calculate_Profit(
130 x_table_name IN VARCHAR2,
131 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
132 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
133 x_num_key_columns IN NUMBER,
134 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
135 x_num_data_columns IN NUMBER,
136 x_aw_flag IN BOOLEAN,
137 x_change_vector_value IN NUMBER
138 ) RETURN BOOLEAN IS
139
140 e_unexpected_error EXCEPTION;
141
142 TYPE t_cursor IS REF CURSOR;
143 h_cursor t_cursor;
144
145 h_i NUMBER;
146 h_sql VARCHAR2(32700);
147
148 cursor c_account_key( pTableName varchar2,pCalcType number) is
149 SELECT parameter1
150 FROM bsc_db_calculations
151 WHERE table_name = pTableName AND
152 calculation_type = pCalcType ;
153
154 h_calculation_type NUMBER;
155
156 h_account_key VARCHAR2(30);
157
158 cursor c_type_of_account_key( pLevelPkCol varchar2) is
159 SELECT r.relation_col
160 FROM bsc_sys_dim_levels_b e, bsc_sys_dim_level_rels r
161 WHERE e.dim_level_id = r.dim_level_id AND
162 e.level_pk_col = pLevelPkCol;
163
164 h_type_of_account_key VARCHAR2(30);
165
166 h_account_dim_table VARCHAR2(30);
167
168 h_profit_account NUMBER;
169
170 h_lst_keys_no_account VARCHAR2(32700);
171 h_arr_keys_no_account BSC_UPDATE_UTIL.t_array_of_varchar2;
172 h_num_keys_no_account NUMBER;
173
174 h_lst_data_columns VARCHAR2(32700);
175 h_lst_sum_profit VARCHAR2(32700);
176
177 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
178
179 cursor c_dim_table_name(pLevelPkCol varchar2) is
180 SELECT level_view_name
181 FROM bsc_sys_dim_levels_b
182 WHERE level_pk_col = pLevelPkCol;
183
184 h_acc_key VARCHAR2(30);
185 h_acc_table VARCHAR2(30);
186
187 BEGIN
188
189 h_calculation_type := 1;
190 h_lst_keys_no_account := NULL;
191 h_lst_data_columns := NULL;
192 h_lst_sum_profit := NULL;
193
194 -- BSC-MV Note: In this architecture profit calculation is only calculated in the base table
195 -- and the base table has sub_account not account.
196 -- In this context h_account_key means subaccount
197
198 -- Get the name of the account key
199 OPEN c_account_key (x_table_name, h_calculation_type);
200 FETCH c_account_key INTO h_account_key;
201 IF c_account_key%NOTFOUND THEN
202 RAISE e_unexpected_error;
203 END IF;
204 CLOSE c_account_key;
205
206 IF h_account_key IS NULL THEN
207 RAISE e_unexpected_error;
208 END IF;
209
210 -- Initialize the array of keys that are not the account key
211 h_num_keys_no_account := 0;
212
213 FOR h_i IN 1 .. x_num_key_columns LOOP
214 IF x_key_columns(h_i) <> h_account_key THEN
215 IF h_lst_keys_no_account IS NULL THEN
216 h_lst_keys_no_account := x_key_columns(h_i);
217 ELSE
218 h_lst_keys_no_account := h_lst_keys_no_account||', '||x_key_columns(h_i);
219 END IF;
220
221 h_num_keys_no_account := h_num_keys_no_account + 1;
222 h_arr_keys_no_account(h_num_keys_no_account) := x_key_columns(h_i);
223 ELSE
224 h_account_dim_table := x_key_dim_tables(h_i);
225 END IF;
226 END LOOP;
227
228 IF h_account_dim_table IS NULL THEN
229 RAISE e_unexpected_error;
230 END IF;
231
232 h_arr_keys_no_account(h_num_keys_no_account + 1) := 'YEAR';
233 h_arr_keys_no_account(h_num_keys_no_account + 2) := 'TYPE';
234 h_arr_keys_no_account(h_num_keys_no_account + 3) := 'PERIOD';
235 h_num_keys_no_account := h_num_keys_no_account + 3;
236
237 -- AW_INTEGRATION: Base table does not have periodicity_id
238 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
239 h_arr_keys_no_account(h_num_keys_no_account + 1) := 'PERIODICITY_ID';
240 h_num_keys_no_account := h_num_keys_no_account + 1;
241 END IF;
242
243 IF h_lst_keys_no_account IS NULL THEN
244 h_lst_keys_no_account := 'YEAR, TYPE, PERIOD';
245 ELSE
246 h_lst_keys_no_account := h_lst_keys_no_account||', YEAR, TYPE, PERIOD';
247 END IF;
248
249 -- AW_INTEGRATION: Base table does not have periodicity_id
250 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
251 h_lst_keys_no_account := h_lst_keys_no_account||', PERIODICITY_ID';
252 END IF;
253
254 -- Get the name of key column for type of account
255 OPEN c_type_of_account_key (h_account_key);
256 FETCH c_type_of_account_key INTO h_type_of_account_key;
257 IF c_type_of_account_key%NOTFOUND THEN
258 RAISE e_unexpected_error;
259 END IF;
260 CLOSE c_type_of_account_key;
261 IF BSC_APPS.bsc_mv THEN
262 -- Since h_account_key is subaccount, what we got was the key column
263 -- for account. We need to do it again to get the type of account
264 h_acc_key := h_type_of_account_key;
265 OPEN c_type_of_account_key (h_acc_key);
266 FETCH c_type_of_account_key INTO h_type_of_account_key;
267 IF c_type_of_account_key%NOTFOUND THEN
268 RAISE e_unexpected_error;
269 END IF;
270 CLOSE c_type_of_account_key;
271 END IF;
272
273 IF h_type_of_account_key IS NULL THEN
274 RAISE e_unexpected_error;
275 END IF;
276
277 -- Get the profit account
278 IF NOT BSC_APPS.bsc_mv THEN
279 h_sql := 'SELECT code'||
280 ' FROM '||h_account_dim_table||
281 ' WHERE '||h_type_of_account_key||' = :1';
282 OPEN h_cursor FOR h_sql USING 3;
283 FETCH h_cursor INTO h_profit_account;
284 IF h_cursor%NOTFOUND THEN
285 RAISE e_unexpected_error;
286 END IF;
287 CLOSE h_cursor;
288 ELSE
289 -- h_account_dim_table is the name of the subaccount table.
290 -- I need the name of the account table
291 OPEN c_dim_table_name(h_acc_key);
292 FETCH c_dim_table_name INTO h_acc_table;
293 IF c_dim_table_name%NOTFOUND THEN
294 RAISE e_unexpected_error;
295 END IF;
296 CLOSE c_dim_table_name;
297
298 h_sql := 'SELECT code'||
299 ' FROM '||h_account_dim_table||
300 ' WHERE '||h_acc_key||' = ('||
301 ' SELECT code'||
302 ' FROM '||h_acc_table||
303 ' WHERE '||h_type_of_account_key||' = :1)';
304 OPEN h_cursor FOR h_sql USING 3;
305 FETCH h_cursor INTO h_profit_account;
306 IF h_cursor%NOTFOUND THEN
307 RAISE e_unexpected_error;
308 END IF;
309 CLOSE h_cursor;
310 END IF;
311
312 -- Deletes the current profits records from the table
313 l_bind_vars_values.delete;
314 h_sql := 'DELETE FROM '||x_table_name||
315 ' WHERE '||h_account_key||' = :1';
316 l_bind_vars_values(1) := h_profit_account ;
317 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
318 --Fix bug#4116490 Need commit
319 commit;
320
321 -- Calculates the profit and insert it with the profit code
322
323 -- Initialize a list with the data columns
324 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
325
326 -- Get a list with the calculation of the profit for all data fields
327 FOR h_i IN 1 .. x_num_data_columns LOOP
328 IF h_lst_sum_profit IS NULL THEN
329 IF BSC_APPS.bsc_mv THEN
330 h_lst_sum_profit := 'SUM(DECODE('||h_acc_table;
331 ELSE
332 h_lst_sum_profit := 'SUM(DECODE('||h_account_dim_table;
333 END IF;
334 h_lst_sum_profit := h_lst_sum_profit||'.'||h_type_of_account_key||', '||
335 '1, '||x_table_name||'.'||x_data_columns(h_i)||', '||
336 '-'||x_table_name||'.'||x_data_columns(h_i)||'))';
337 ELSE
338 IF BSC_APPS.bsc_mv THEN
339 h_lst_sum_profit := h_lst_sum_profit||', '||
340 'SUM(DECODE('||h_acc_table;
341 ELSE
342 h_lst_sum_profit := h_lst_sum_profit||', '||
343 'SUM(DECODE('||h_account_dim_table;
344 END IF;
345 h_lst_sum_profit := h_lst_sum_profit||'.'||h_type_of_account_key||', '||
346 '1, '||x_table_name||'.'||x_data_columns(h_i)||', '||
347 '-'||x_table_name||'.'||x_data_columns(h_i)||'))';
348 END IF;
349 END LOOP;
350
351 -- Built and execute the query to calculate the profit
352 h_sql := 'INSERT /*+ append ';
353 if BSC_UPDATE_UTIL.is_parallel then
354 h_sql:=h_sql||'parallel ('||x_table_name||') ';
355 end if;
356 h_sql:=h_sql||' */';
357 h_sql:=h_sql||'INTO '||x_table_name||
358 ' ( '||h_account_key||', '||h_lst_keys_no_account||', '||h_lst_data_columns;
359 IF x_aw_flag THEN
360 h_sql:=h_sql||', PROJECTION, CHANGE_VECTOR';
361 END IF;
362 h_sql:=h_sql||') SELECT ';
363 if BSC_UPDATE_UTIL.is_parallel then
364 h_sql:=h_sql||'/*+ parallel ('||x_table_name||') parallel ('||h_account_dim_table||')*/ ';
365 end if;
366 h_sql:=h_sql||h_profit_account||', '||
367 BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account)||', '||
368 h_lst_sum_profit;
369 IF x_aw_flag THEN
370 h_sql:=h_sql||', '||x_table_name||'.PROJECTION, '||x_change_vector_value;
371 END IF;
372 h_sql:=h_sql||' FROM '||x_table_name||', '||h_account_dim_table;
373 IF BSC_APPS.bsc_mv THEN
374 h_sql := h_sql||', '||h_acc_table;
375 END IF;
376 h_sql := h_sql||' WHERE '||x_table_name||'.'||h_account_key||' = '||h_account_dim_table||'.CODE';
377 IF BSC_APPS.bsc_mv THEN
378 h_sql := h_sql||' AND '||h_account_dim_table||'.'||h_acc_key||' = '||h_acc_table||'.CODE';
379 END IF;
380 h_sql := h_sql||' GROUP BY '||
381 BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account);
382 --Fix bug#4593671: add projection to the group by in aw architecture
383 IF x_aw_flag THEN
384 h_sql := h_sql||', '||x_table_name||'.PROJECTION';
385 END IF;
386 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
387 commit;
388 RETURN TRUE;
389
390 EXCEPTION
391 WHEN e_unexpected_error THEN
392 BSC_MESSAGE.Add(
393 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROFIT_CALC_FAILED'),
394 X_Source => 'BSC_UPDATE_CALC.Calculate_Profit');
395 RETURN FALSE;
396
397 WHEN OTHERS THEN
398 BSC_MESSAGE.Add(
399 X_Message => SQLERRM,
400 X_Source => 'BSC_UPDATE_CALC.Calculate_Profit');
401 RETURN FALSE;
402
403 END Calculate_Profit;
404
405
406 /*===========================================================================+
407 | FUNCTION Calculate_Proj_Avg_Last_Year
408 +============================================================================*/
409 FUNCTION Calculate_Proj_Avg_Last_Year(
410 x_table_name IN VARCHAR2,
411 x_periodicity IN NUMBER,
412 x_period IN NUMBER,
413 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
414 x_num_key_columns IN NUMBER,
415 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
416 x_num_data_columns IN NUMBER,
417 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
418 x_current_fy IN NUMBER,
419 x_num_of_years IN NUMBER,
420 x_previous_years IN NUMBER,
421 x_is_base IN BOOLEAN,
422 x_aw_flag IN BOOLEAN
423 ) RETURN BOOLEAN IS
424
425 e_unexpected_error EXCEPTION;
426
427 h_lst_keys VARCHAR2(32700);
428 h_lst_keys_temp VARCHAR2(32700);
429 h_lst_keys_tochar VARCHAR2(32700);
430 h_lst_keys_nc VARCHAR2(32700);
431
432 h_num_periods NUMBER;
433 h_num_previous_periods NUMBER;
434
435 h_i NUMBER;
436 h_j NUMBER;
437
438 h_sql VARCHAR2(32700);
439
440 h_init_per NUMBER;
441
442 h_min_year NUMBER;
443 h_min_per NUMBER;
444
445 h_yearly_flag NUMBER;
446
447 h_uni_table VARCHAR2(32000);
448
449 l_bind_vars_union BSC_UPDATE_UTIL.t_array_of_number;
450 l_num_bind_vars_union NUMBER;
451 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
452 l_num_bind_vars NUMBER;
453 l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
454 l_num_bind_vars_post NUMBER;
455
456 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
457 h_lst_select VARCHAR2(32000);
458 h_mv_name VARCHAR2(30);
459 h_ref_table VARCHAR2(30);
460
461 h_lst_data_columns VARCHAR2(32000);
462 h_lst_data_columns_temp VARCHAR2(32000);
463 h_lst_xmed_columns VARCHAR2(32000);
464 h_lst_xmed_columns_p VARCHAR2(32000);
465 h_lst_avg_columns VARCHAR2(32000);
466
467 BEGIN
468
469 --ENH_PROJECTION_4235711: In this function we are going to calculate projection
470 --in the table BSC_TMP_PROJ_CALC. Later we will merge the projection into the base/summary table
471 --BSC_TMP_PROJ_CALC already have all the rows for the projected periods and only for the
472 --dimension combinations we need to calculate projection
473
474 -- Fix bug#4700221 Review this function completely. We need to consider projected periods already calculated
475 -- in bsc_tmp_xmd in order to calculate the next projection periods. This is a truly moving average
476
477 h_yearly_flag := 0;
478 l_num_bind_vars_union := 0;
479 l_num_bind_vars := 0;
480 l_num_bind_vars_post := 0;
481 h_lst_select := NULL;
482 h_lst_data_columns := NULL;
483 h_lst_data_columns_temp := NULL;
484 h_lst_xmed_columns := NULL;
485 h_lst_xmed_columns_p := NULL;
486 h_lst_avg_columns := NULL;
487 h_lst_keys_temp := NULL;
488 h_lst_keys_tochar := NULL;
489
490 -- New optimization: We are going to calculate projection on all data columns at the same time
491
492 FOR h_i IN 1..x_num_key_columns LOOP
493 h_key_columns_temp(h_i) := 'KEY'||h_i;
494 h_lst_keys_temp := h_lst_keys_temp||'KEY'||h_i||' '||x_key_columns(h_i)||', ';
495 h_lst_keys_tochar := h_lst_keys_tochar||'TO_CHAR('||x_key_columns(h_i)||') '||x_key_columns(h_i)||', ';
496 END LOOP;
497
498 h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
499
500 h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
501 h_lst_keys_nc := h_lst_keys;
502 IF h_lst_keys IS NOT NULL THEN
503 h_lst_keys := h_lst_keys||', ';
504 h_lst_select := h_lst_select||', ';
505 END IF;
506
507 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
508 h_lst_xmed_columns := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('XMED', x_num_data_columns);
509
510 FOR h_i IN 1..x_num_data_columns LOOP
511 IF h_i > 1 THEN
512 h_lst_avg_columns := h_lst_avg_columns||', ';
513 h_lst_xmed_columns_p := h_lst_xmed_columns_p||', ';
514 h_lst_data_columns_temp := h_lst_data_columns_temp||', ';
515 END IF;
516
517 h_lst_avg_columns := h_lst_avg_columns||'AVG('||x_data_columns(h_i)||')';
518 h_lst_xmed_columns_p := h_lst_xmed_columns_p||'P.XMED'||h_i;
519 h_lst_data_columns_temp := h_lst_data_columns_temp||'XMED'||h_i||' '||x_data_columns(h_i);
520 END LOOP;
521
522 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
523
524 --BSC-MV Note: In this architecture if the table is not a base table
525 -- the projected data is in the summary table and the other data including
526 -- targets are in the MV
527 --AW_INTEGRATION: In this architecture we never calculate projection in PT tables, only
528 -- in the base table. So no changes here
529 IF BSC_APPS.bsc_mv AND (NOT x_is_base) THEN
530 h_mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(x_table_name);
531 h_ref_table := h_mv_name;
532 ELSE
533 h_ref_table := x_table_name;
534 END IF;
535
536 l_bind_vars_union.delete;
537 l_num_bind_vars_union := 0;
538 h_uni_table := '(SELECT '||h_lst_keys_tochar||'YEAR, TYPE, PERIOD, ';
539 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
540 h_uni_table := h_uni_table||'PERIODICITY_ID, ';
541 END IF;
542 h_uni_table := h_uni_table||h_lst_data_columns||
543 ' FROM '||h_ref_table||
544 ' WHERE TYPE = :1';
545 l_num_bind_vars_union := l_num_bind_vars_union + 1;
546 l_bind_vars_union(l_num_bind_vars_union) := 0;
547 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
548 h_uni_table := h_uni_table||' AND PERIODICITY_ID = :2';
549 l_num_bind_vars_union := l_num_bind_vars_union + 1;
550 l_bind_vars_union(l_num_bind_vars_union) := x_periodicity;
551 END IF;
552 IF h_yearly_flag = 1 THEN
553 h_uni_table := h_uni_table||' AND YEAR <= :3';
554 l_num_bind_vars_union := l_num_bind_vars_union + 1;
555 l_bind_vars_union(l_num_bind_vars_union) := x_current_fy;
556 ELSE
557 h_uni_table := h_uni_table||' AND (YEAR < :3 OR (YEAR = :4 AND PERIOD <= :5))';
558 l_num_bind_vars_union := l_num_bind_vars_union + 1;
559 l_bind_vars_union(l_num_bind_vars_union) := x_current_fy;
560 l_num_bind_vars_union := l_num_bind_vars_union + 1;
561 l_bind_vars_union(l_num_bind_vars_union) := x_current_fy;
562 l_num_bind_vars_union := l_num_bind_vars_union + 1;
563 l_bind_vars_union(l_num_bind_vars_union) := x_period;
564 END IF;
565 h_uni_table := h_uni_table||' UNION '||
566 'SELECT '||h_lst_keys_temp||'YEAR, TYPE, PERIOD, ';
567 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
568 h_uni_table := h_uni_table||'PERIODICITY_ID, ';
569 END IF;
570 h_uni_table := h_uni_table||h_lst_data_columns_temp||
571 ' FROM bsc_tmp_xmd'||
572 ') u';
573
574 IF h_yearly_flag = 1 THEN -- Annual
575 -- For annual peridicity this method look back the number of previous years
576 -- defined for the table
577 h_num_periods := x_period + (x_num_of_years - x_previous_years) - 1;
578 h_num_previous_periods := x_previous_years;
579 ELSE
580 -- For other periodicities this method looks one year back
581 h_num_periods := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
582 IF h_num_periods IS NULL THEN
583 RAISE e_unexpected_error;
584 END IF;
585
586 h_num_previous_periods := h_num_periods;
587 END IF;
588
589 h_init_per := x_period + 1;
590
591 --Fix bug#3875046: We are going to insert all the records in bsc_tmp_xmd for all the periods
592 --and at the end we update the projection table BSC_TMP_PROJ_CALC only one time.
593 --For this reason I need to take out from the loop this truncate stmt
594 -- Delete all data from temporary table
595 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
596 commit;
597
598 FOR h_i IN h_init_per .. h_num_periods LOOP
599 l_bind_vars_values.delete;
600 l_num_bind_vars := 0;
601 l_bind_vars_post.delete;
602 l_num_bind_vars_post := 0;
603
604 IF h_yearly_flag = 1 THEN -- Annual
605 h_min_year := h_i - h_num_previous_periods;
606 ELSE
607 h_min_year := x_current_fy - 1;
608 -- Fix bug#4700221 moving average is not calculated correclty
609 --h_min_per := h_num_periods - MOD(h_num_previous_periods - x_period, h_num_periods);
610 h_min_per := h_i;
611 END IF;
612
613 -- Calculate the temporary table BSC_TMP_XMD with the average
614
615 -- Insert
616 l_bind_vars_values.delete ;
617 h_sql := 'INSERT /*+ append ';
618 if BSC_UPDATE_UTIL.is_parallel then
619 h_sql:=h_sql||'parallel (bsc_tmp_xmd) ';
620 end if;
621 h_sql:=h_sql||' */';
622 h_sql:=h_sql||'INTO bsc_tmp_xmd ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
623 -- AW_INTEGRATION: Base table does not have periodicity_id
624 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
625 h_sql := h_sql||'PERIODICITY_ID, ';
626 END IF;
627 h_sql := h_sql||h_lst_xmed_columns||')';
628 h_sql := h_sql||' SELECT ';
629 IF h_yearly_flag = 1 THEN -- Annual
630 h_sql := h_sql||h_lst_keys||':11, 0, 0, ';
631 l_bind_vars_values(1) := h_i;
632 l_num_bind_vars := 1;
633 ELSE
634 h_sql := h_sql||h_lst_keys||':11, 0, :12, ';
635 l_bind_vars_values(1) := x_current_fy;
636 l_bind_vars_values(2) := h_i;
637 l_num_bind_vars := 2;
638 END IF;
639 -- AW_INTEGRATION: Base table does not have periodicity_id
640 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
641 h_sql := h_sql||'PERIODICITY_ID, ';
642 END IF;
643 h_sql := h_sql||h_lst_avg_columns||
644 ' FROM '||h_uni_table;
645 IF h_yearly_flag = 1 THEN --Annual
646 h_sql := h_sql||' WHERE (year BETWEEN :13 AND :14) AND';
647 l_bind_vars_post(1) := h_min_year;
648 l_bind_vars_post(2) := h_i - 1;
649 l_num_bind_vars_post := 2;
650 ELSE
651 h_sql := h_sql||' WHERE (year * 1000 + period) BETWEEN (:13'
652 ||' * 1000 + :14 ) AND (:15 * 1000 + :16) AND';
653 l_bind_vars_post(1) := h_min_year;
654 l_bind_vars_post(2) := h_min_per;
655 l_bind_vars_post(3) := x_current_fy;
656 l_bind_vars_post(4) := h_i - 1;
657 l_num_bind_vars_post := 4;
658 END IF;
659 h_sql := h_sql||' type = 0';
660 -- AW_INTEGRATION: Base table does not have periodicity_id
661 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
662 h_sql := h_sql||' AND periodicity_id = :17';
663 l_num_bind_vars_post := l_num_bind_vars_post + 1;
664 l_bind_vars_post(l_num_bind_vars_post) := x_periodicity;
665 END IF;
666
667 IF h_lst_keys IS NOT NULL THEN
668 h_sql := h_sql||' GROUP BY '||h_lst_keys_nc;
669 -- AW_INTEGRATION: Base table does not have periodicity_id
670 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
671 h_sql := h_sql||', PERIODICITY_ID';
672 END IF;
673 ELSE
674 -- Fix bug#3381324 If there is no key columns we need to group by periodicity_id
675 -- AW_INTEGRATION: Base table does not have periodicity_id
676 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
677 h_sql := h_sql||' GROUP BY PERIODICITY_ID';
678 END IF;
679 END IF;
680
681 FOR h_j IN 1..l_num_bind_vars_union LOOP
682 l_num_bind_vars := l_num_bind_vars+1;
683 l_bind_vars_values(l_num_bind_vars) := l_bind_vars_union(h_j);
684 END LOOP;
685 FOR h_j IN 1..l_num_bind_vars_post LOOP
686 l_num_bind_vars := l_num_bind_vars+1;
687 l_bind_vars_values(l_num_bind_vars) := l_bind_vars_post(h_j);
688 END LOOP;
689
690 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
691 commit;
692
693 l_bind_vars_values.delete;
694
695 --Fix bug#3875046: We are going to insert all the records in bsc_tmp_xmd for all the periods
696 --and at the end we update the base table only one time.
697 --For this reason I need to take out from the loop the update part
698 END LOOP;
699
700 -- Update the projection table table BSC_TMP_PROJ_CALC
701 -- Fix performance bug#3665014. Instead of update the base table with a complex query
702 -- we are going to insert the records in BSC_TMP_XMD_Y with row_id
703 -- and then update the base table.
704 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
705 commit;
706
707 h_sql := 'INSERT /*+ append ';
708 IF BSC_UPDATE_UTIL.is_parallel THEN
709 h_sql := h_sql||'parallel (BSC_TMP_XMD_Y) ';
710 END IF;
711 h_sql := h_sql||' */';
712 h_sql := h_sql||' INTO BSC_TMP_XMD_Y (ROW_ID, '||h_lst_xmed_columns||')'||
713 ' SELECT B.ROWID, '||h_lst_xmed_columns_p||
714 ' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_XMD P'||
715 ' WHERE ';
716 IF x_num_key_columns > 0 THEN
717 h_sql := h_sql||
718 BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
719 h_key_columns_temp,
720 'P',
721 h_key_columns_temp,
722 x_num_key_columns,
723 'AND')||
724 ' AND';
725 END IF;
726 h_sql := h_sql||' B.YEAR = P.YEAR AND B.TYPE = P.TYPE AND B.PERIOD = P.PERIOD';
727 --AW_INTEGRATION: Base table does not have periodicity_id
728 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
729 h_sql := h_sql||' AND B.PERIODICITY_ID = P.PERIODICITY_ID';
730 END IF;
731 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
732 commit;
733
734 -- Analyze the BSC_TMP_XMD_Y
735 -- Bug#3740230: We cannot analyze. It is causing Loader hangs truncating this table
736 -- Bug#3756654: Oracle 8i does not support gathering stacts on temporary tables
737 --IF BSC_IM_UTILS.get_db_version <> '8i' THEN
738 -- BSC_BIA_WRAPPER.Analyze_Table('BSC_TMP_XMD_Y');
739 --END IF;
740
741 -- Update the base table
742 h_sql := 'UPDATE /*+ORDERED USE_NL(B)*/ BSC_TMP_PROJ_CALC B'||
743 ' SET ('||x_lst_data_temp;
744 h_sql := h_sql||') = ('||
745 ' SELECT '||h_lst_xmed_columns;
746 h_sql := h_sql||
747 ' FROM BSC_TMP_XMD_Y P'||
748 ' WHERE P.ROW_ID = B.ROWID)'||
749 ' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_XMD_Y)';
750 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
751 commit;
752
753 -- Fix bug#4463132: Truncate temporary table after use
754 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
755 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
756 commit;
757
758 RETURN TRUE;
759
760 EXCEPTION
761 WHEN e_unexpected_error THEN
762 BSC_MESSAGE.Add(
763 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_ALY_FAILED'),
764 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_Avg_Last_Year');
765 RETURN FALSE;
766
767 WHEN OTHERS THEN
768 BSC_MESSAGE.Add(
769 X_Message => SQLERRM,
770 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_Avg_Last_Year');
771 RETURN FALSE;
772
773 END Calculate_Proj_Avg_Last_Year;
774
775
776 /*===========================================================================+
777 | FUNCTION Calculate_Proj_3_Periods_Perf
778 +============================================================================*/
779 FUNCTION Calculate_Proj_3_Periods_Perf(
780 x_table_name IN VARCHAR2,
781 x_periodicity IN NUMBER,
782 x_period IN NUMBER,
783 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
784 x_num_key_columns IN NUMBER,
785 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
786 x_num_data_columns IN NUMBER,
787 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
788 x_current_fy IN NUMBER,
789 x_is_base IN BOOLEAN,
790 x_aw_flag IN BOOLEAN
791 ) RETURN BOOLEAN IS
792
793 e_unexpected_error EXCEPTION;
794
795 h_sql VARCHAR2(32700);
796
797 h_num_per_back NUMBER;
798 h_per_ini NUMBER;
799 h_per_end NUMBER;
800 h_num_pers NUMBER;
801
802 h_lst_keys VARCHAR2(32700);
803 h_lst_groupby VARCHAR2(32700);
804
805 h_sql_join VARCHAR2(32700);
806
807 h_yearly_flag NUMBER;
808
809 h_uni_table VARCHAR2(30);
810
811 -- Posco bind var fix
812 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
813 l_num_bind_vars NUMBER;
814
815 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
816 h_lst_select VARCHAR2(32700);
817 h_lst_totplan VARCHAR2(32700);
818 h_lst_totreal VARCHAR2(32700);
819 h_lst_plan VARCHAR2(32700);
820 h_lst_data VARCHAR2(32700);
821 h_lst_data_p VARCHAR2(32700);
822 h_lst_data_columns VARCHAR2(32700);
823 h_lst_sumdata VARCHAR2(32700);
824 h_lst_keys_p VARCHAR2(32700);
825 h_lst_data_proj VARCHAR2(32700);
826
827 BEGIN
828
829 --ENH_PROJECTION_4235711: In this function we are going to calculate projection
830 --in the table BSC_TMP_PROJ_CALC. Later we will merge the projection into the base/summary table
831 --BSC_TMP_PROJ_CALC already have all the rows for the projected periods and only for the
832 --dimension combinations we need to calculate projection
833
834 h_lst_keys := NULL;
835 h_lst_groupby := NULL;
836 h_sql_join := NULL;
837 h_yearly_flag := 0;
838 l_num_bind_vars := 0;
839 h_lst_select := NULL;
840 h_lst_totplan := NULL;
841 h_lst_totreal := NULL;
842 h_lst_plan := NULL;
843 h_lst_data := NULL;
844 h_lst_data_p := NULL;
845 h_lst_data_columns := NULL;
846 h_lst_sumdata := NULL;
847 h_lst_keys_p := NULL;
848 h_lst_data_proj := NULL;
849
850 h_num_per_back := 3;
851
852 -- BSC-MV Note: In this architecture if this is a summary table the it only
853 -- contains the projections. Base information is on the MV
854 --AW_INTEGRATION: In this architecture we never calculate projection in PT tables, only
855 -- in the base table. So no changes here
856 IF BSC_APPS.bsc_mv AND (NOT x_is_base) THEN
857 h_uni_table := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(x_table_name);
858 ELSE
859 h_uni_table := x_table_name;
860 END IF;
861
862 FOR h_i IN 1..x_num_key_columns LOOP
863 h_key_columns_temp(h_i) := 'KEY'||h_i;
864 END LOOP;
865
866 h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
867
868 h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
869 h_lst_keys_p := BSC_UPDATE_UTIL.Make_Lst_Table_Column('P', h_key_columns_temp, x_num_key_columns);
870 h_lst_groupby := h_lst_keys;
871
872 IF h_lst_keys IS NOT NULL THEN
873 h_lst_keys := h_lst_keys||', ';
874 h_lst_keys_p := h_lst_keys_p||', ';
875 h_lst_select := h_lst_select||', ';
876 END IF;
877
878 h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
879 h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
880 h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
881 h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
882 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
883
884 FOR h_i IN 1..x_num_data_columns LOOP
885 IF h_i > 1 THEN
886 h_lst_sumdata := h_lst_sumdata||', ';
887 h_lst_data_proj := h_lst_data_proj||', ';
888 h_lst_data_p := h_lst_data_p||', ';
889 END IF;
890
891 h_lst_sumdata := h_lst_sumdata||'SUM('||x_data_columns(h_i)||')';
892
893 h_lst_data_proj := h_lst_data_proj||
894 'case'||
895 ' when (tr.totreal'||h_i||'>0 and tp.totplan'||h_i||'>0) and'||
896 ' ((decode(tp.totplan'||h_i||',0,0,tr.totreal'||h_i||'/tp.totplan'||h_i||')*pp.plan'||h_i||')>(2*pp.plan'||h_i||'))'||
897 ' then 2*pp.plan'||h_i||''||
898 ' when tr.totreal'||h_i||'>0 and tp.totplan'||h_i||'>0'||
899 ' then (tr.totreal'||h_i||'/tp.totplan'||h_i||')*pp.plan'||h_i||''||
900 ' when ((tr.totreal'||h_i||'<0 and tp.totplan'||h_i||'<0) or (tr.totreal'||h_i||'<0 and tp.totplan'||h_i||'>0) or'||
901 ' (tr.totreal'||h_i||'>0 and tp.totplan'||h_i||'<0)) and (pp.plan'||h_i||'=0 or (pp.plan'||h_i||' IS NULL))'||
902 ' then (tr.totreal'||h_i||'-tp.totplan'||h_i||')/3'||
903 ' when ((tr.totreal'||h_i||'<0 and tp.totplan'||h_i||'<0) or (tr.totreal'||h_i||'<0 and tp.totplan'||h_i||'>0) or'||
904 ' (tr.totreal'||h_i||'>0 AND tp.totplan'||h_i||'<0)) and not(pp.plan'||h_i||'=0 or (pp.plan'||h_i||' IS NULL))'||
905 ' then pp.plan'||h_i||'+((tr.totreal'||h_i||'-tp.totplan'||h_i||')/3)'||
906 ' when (tr.totreal'||h_i||'<>0 and (tp.totplan'||h_i||'=0 or (tp.totplan'||h_i||' is null))) and'||
907 ' (pp.plan'||h_i||'=0 or (pp.plan'||h_i||' is null))'||
908 ' then tr.totreal'||h_i||'/3'||
909 ' when (tr.totreal'||h_i||'<>0 and (tp.totplan'||h_i||'=0 or (tp.totplan'||h_i||' is null))) and'||
910 ' not(pp.plan'||h_i||'=0 or (pp.plan'||h_i||' is null))'||
911 ' then pp.plan'||h_i||'+(tr.totreal'||h_i||'/3)'||
912 ' when ((tr.totreal'||h_i||'=0 or (tr.totreal'||h_i||' is null)) and'||
913 ' (tp.totplan'||h_i||'=0 or (tp.totplan'||h_i||' is null)))'||
914 ' then pp.plan'||h_i||''||
915 ' end';
916
917 h_lst_data_p := h_lst_data_p||'P.DATA'||h_i;
918
919 END LOOP;
920
921 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
922 IF h_yearly_flag = 1 THEN -- Annual
923 h_per_end := x_current_fy;
924 h_per_ini := x_current_fy - h_num_per_back + 1;
925 h_num_pers := h_per_end - h_per_ini + 1;
926 ELSE
927 h_per_end := x_period;
928 h_per_ini := x_period - h_num_per_back + 1;
929
930 IF h_per_ini <= 0 THEN
931 h_per_ini := 1;
932 END IF;
933
934 h_num_pers := h_per_end - h_per_ini + 1;
935 END IF;
936
937 -- Make a temporal table with the total of plan data of the last n-periods,
938 -- where n is the value of the variable h_num_per_back
939 --h_sql := 'DELETE FROM BSC_TMP_TOT_PLAN';
940 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
941 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
942 commit;
943
944 l_bind_vars_values.delete ;
945 h_sql := 'INSERT /*+ append ';
946 if BSC_UPDATE_UTIL.is_parallel then
947 h_sql:=h_sql||'parallel (bsc_tmp_tot_plan) ';
948 end if;
949 h_sql:=h_sql||' */';
950 h_sql:=h_sql||'INTO BSC_TMP_TOT_PLAN ('||h_lst_select||'TYPE, '||h_lst_totplan||')'||
951 ' SELECT ';
952 if BSC_UPDATE_UTIL.is_parallel then
953 h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
954 end if;
955 h_sql:=h_sql||h_lst_keys||'0, '||h_lst_sumdata||
956 ' FROM '||h_uni_table;
957 IF h_yearly_flag = 1 THEN
958 h_sql := h_sql||' WHERE YEAR >= :1 '||
959 ' And YEAR <= :2 '||
960 ' And TYPE = :3'||
961 ' And PERIOD = :4';
962 l_bind_vars_values(1) := h_per_ini;
963 l_bind_vars_values(2) := h_per_end;
964 l_bind_vars_values(3) := 1;
965 l_bind_vars_values(4) := 0;
966 l_num_bind_vars := 4;
967 ELSE
968 h_sql := h_sql||' WHERE YEAR = :1'||
969 ' And TYPE = :2'||
970 ' And PERIOD >= :3'||
971 ' And PERIOD <= :4';
972 l_bind_vars_values(1) := x_current_fy;
973 l_bind_vars_values(2) := 1 ;
974 l_bind_vars_values(3) := h_per_ini ;
975 l_bind_vars_values(4) := h_per_end ;
976 l_num_bind_vars := 4;
977 END IF;
978 --AW_INTEGRATION: Base table does not have periodicity_id
979 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
980 h_sql := h_sql||' AND PERIODICITY_ID = :5';
981 l_bind_vars_values(5) := x_periodicity;
982 l_num_bind_vars := 5;
983 END IF;
984 IF h_lst_groupby IS NOT NULL THEN
985 h_sql := h_sql||' GROUP BY '||h_lst_groupby;
986 END IF;
987 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
988 commit;
989
990 -- Make a temporal table with the total of real data of the last n-periods,
991 -- where n is the value of the variable h_num_per_back
992 --h_sql := 'DELETE FROM BSC_TMP_TOT_REAL';
993 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
994 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
995 commit;
996
997 l_bind_vars_values.delete ;
998 h_sql := 'INSERT /*+ append ';
999 if BSC_UPDATE_UTIL.is_parallel then
1000 h_sql:=h_sql||'parallel (bsc_tmp_tot_real) ';
1001 end if;
1002 h_sql:=h_sql||' */';
1003 h_sql:=h_sql||'INTO BSC_TMP_TOT_REAL('||h_lst_select||'TYPE, '||h_lst_totreal||')'||
1004 ' SELECT ';
1005 if BSC_UPDATE_UTIL.is_parallel then
1006 h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
1007 end if;
1008 h_sql:=h_sql||h_lst_keys||'0, '||h_lst_sumdata||
1009 ' FROM '||h_uni_table;
1010 IF h_yearly_flag = 1 THEN
1011 h_sql := h_sql||' WHERE YEAR >= :1'||
1012 ' And YEAR <= :2'||
1013 ' And TYPE = :3'||
1014 ' And PERIOD = :4';
1015 l_bind_vars_values(1) := (h_per_ini);
1016 l_bind_vars_values(2) := (h_per_end);
1017 l_bind_vars_values(3) := 0;
1018 l_bind_vars_values(4) := 0;
1019 l_num_bind_vars := 4;
1020 ELSE
1021 h_sql := h_sql||' WHERE YEAR = :1'||
1022 ' And TYPE = :2'||
1023 ' And PERIOD >= :3'||
1024 ' And PERIOD <= :4';
1025 l_bind_vars_values(1) := (x_current_fy);
1026 l_bind_vars_values(2) := 0 ;
1027 l_bind_vars_values(3) := (h_per_ini) ;
1028 l_bind_vars_values(4) := (h_per_end) ;
1029 l_num_bind_vars := 4;
1030 END IF;
1031 -- AW_INTEGRATION: Base table does not have peridicity_id
1032 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1033 h_sql := h_sql||' AND PERIODICITY_ID = :5';
1034 l_bind_vars_values(5) := (x_periodicity);
1035 l_num_bind_vars := 5;
1036 END IF;
1037 IF h_lst_groupby IS NOT NULL THEN
1038 h_sql := h_sql||' GROUP BY '||h_lst_groupby;
1039 END IF;
1040 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars );
1041 commit;
1042
1043 -- Make a temporal table with the plan of the projected periods
1044 --h_sql := 'DELETE FROM BSC_TMP_PLAN_PROJECTIONS';
1045 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1046 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1047 commit;
1048
1049 l_bind_vars_values.delete;
1050 h_sql := 'INSERT /*+ append ';
1051 if BSC_UPDATE_UTIL.is_parallel then
1052 h_sql:=h_sql||'parallel (BSC_TMP_PLAN_PROJECTIONS) ';
1053 end if;
1054 h_sql:=h_sql||' */';
1055 h_sql:=h_sql||'INTO BSC_TMP_PLAN_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, '||h_lst_plan||')'||
1056 ' SELECT ';
1057 if BSC_UPDATE_UTIL.is_parallel then
1058 h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
1059 end if;
1060 h_sql:=h_sql||h_lst_keys||'YEAR, 0, PERIOD, '||h_lst_data_columns||
1061 ' FROM '||h_uni_table;
1062 l_bind_vars_values.delete ;
1063 IF h_yearly_flag = 1 THEN
1064 h_sql := h_sql||' WHERE YEAR > :1'||
1065 ' AND TYPE = :2'||
1066 ' AND PERIOD = :3';
1067 l_bind_vars_values(1) := (h_per_end);
1068 l_bind_vars_values(2) := 1;
1069 l_bind_vars_values(3) := 0;
1070 l_num_bind_vars := 3;
1071 ELSE
1072 h_sql := h_sql||' WHERE YEAR = :1'||
1073 ' AND TYPE = :2'||
1074 ' AND PERIOD > :3';
1075 l_bind_vars_values(1) := (x_current_fy);
1076 l_bind_vars_values(2) := 1;
1077 l_bind_vars_values(3) := (h_per_end) ;
1078 l_num_bind_vars := 3;
1079 END IF;
1080 -- AW_INTEGRATION: Base table does not have periodicity_id
1081 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1082 h_sql := h_sql||' AND PERIODICITY_ID = :4';
1083 l_bind_vars_values(4) := (x_periodicity);
1084 l_num_bind_vars := 4;
1085 END IF;
1086 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1087 commit;
1088
1089 -- Calculate the projection in temporal table BSC_TMP_PROJECTIONS
1090 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1091 commit;
1092
1093 l_bind_vars_values.delete;
1094 h_sql := 'INSERT /*+ append ';
1095 if BSC_UPDATE_UTIL.is_parallel then
1096 h_sql:=h_sql||'parallel (BSC_TMP_PROJECTIONS) ';
1097 end if;
1098 h_sql:=h_sql||' */';
1099 h_sql:=h_sql||'INTO BSC_TMP_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
1100 -- AW_INTEGRATION: Base table does not have periodicity_id
1101 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1102 h_sql := h_sql||'PERIODICITY_ID, ';
1103 END IF;
1104 h_sql := h_sql||h_lst_data||')'||
1105 ' SELECT '||h_lst_keys_p||'P.YEAR, P.TYPE, P.PERIOD, ';
1106 -- AW_INTEGRATION: Base table does not have periodicity_id
1107 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1108 h_sql := h_sql||'P.PERIODICITY_ID, ';
1109 END IF;
1110 h_sql := h_sql||h_lst_data_proj||
1111 ' FROM BSC_TMP_PROJ_CALC P, BSC_TMP_TOT_PLAN TP, BSC_TMP_TOT_REAL TR, BSC_TMP_PLAN_PROJECTIONS PP'||
1112 ' WHERE ';
1113 IF x_num_key_columns > 0 THEN
1114 h_sql := h_sql||
1115 BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1116 h_key_columns_temp,
1117 'TP',
1118 h_key_columns_temp,
1119 x_num_key_columns,
1120 'AND')||
1121 ' AND ';
1122 END IF;
1123 h_sql := h_sql||'P.TYPE = TP.TYPE (+) AND ';
1124 IF x_num_key_columns > 0 THEN
1125 h_sql := h_sql||
1126 BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1127 h_key_columns_temp,
1128 'TR',
1129 h_key_columns_temp,
1130 x_num_key_columns,
1131 'AND')||
1132 ' AND ';
1133 END IF;
1134 h_sql := h_sql||'P.TYPE = TR.TYPE (+) AND ';
1135 IF x_num_key_columns > 0 THEN
1136 h_sql := h_sql||
1137 BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
1138 h_key_columns_temp,
1139 'PP',
1140 h_key_columns_temp,
1141 x_num_key_columns,
1142 'AND')||
1143 ' AND ';
1144 END IF;
1145 h_sql := h_sql||'P.YEAR = PP.YEAR (+) AND P.TYPE = PP.TYPE (+) AND P.PERIOD = PP.PERIOD (+) AND ';
1146 l_bind_vars_values.delete ;
1147 IF h_yearly_flag = 1 THEN
1148 h_sql := h_sql||'P.YEAR > :1 AND P.TYPE = :2 AND P.PERIOD = :3';
1149 l_bind_vars_values(1) := (h_per_end);
1150 l_bind_vars_values(2) := 0;
1151 l_bind_vars_values(3) := 0;
1152 l_num_bind_vars := 3;
1153 ELSE
1154 h_sql := h_sql||'P.YEAR = :1 AND P.TYPE = :2 AND P.PERIOD > :3';
1155 l_bind_vars_values(1) := (x_current_fy);
1156 l_bind_vars_values(2) := 0;
1157 l_bind_vars_values(3) := (h_per_end) ;
1158 l_num_bind_vars := 3;
1159 END IF;
1160 -- AW_INTEGRATION: Base table does not have periodicity_id
1161 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1162 h_sql := h_sql||' AND P.PERIODICITY_ID = :4';
1163 l_bind_vars_values(4) := (x_periodicity);
1164 l_num_bind_vars := 4;
1165 END IF;
1166 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1167 commit;
1168
1169 l_bind_vars_values.delete;
1170
1171 -- Update the projection table BSC_TMP_PROJ_CALC
1172 -- Fix performance bug#3665014. Instead of update the base table with a complex query
1173 -- we are going to insert the records in BSC_TMP_PROJECTIONS_Y with row_id
1174 -- and then update the base table.
1175
1176 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1177 commit;
1178
1179 h_sql := 'INSERT /*+ append ';
1180 IF BSC_UPDATE_UTIL.is_parallel THEN
1181 h_sql := h_sql||'parallel (BSC_TMP_PROJECTIONS_Y) ';
1182 END IF;
1183 h_sql := h_sql||' */';
1184 h_sql := h_sql||' INTO BSC_TMP_PROJECTIONS_Y (ROW_ID, '||h_lst_data||')'||
1185 ' SELECT B.ROWID, '||h_lst_data_p||
1186 ' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_PROJECTIONS P'||
1187 ' WHERE ';
1188 IF x_num_key_columns > 0 THEN
1189 h_sql := h_sql||
1190 BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
1191 h_key_columns_temp,
1192 'P',
1193 h_key_columns_temp,
1194 x_num_key_columns,
1195 'AND')||
1196 ' AND';
1197 END IF;
1198 h_sql := h_sql||' B.YEAR = P.YEAR AND B.TYPE = P.TYPE AND B.PERIOD = P.PERIOD';
1199 -- AW_INTEGRATION: Base table does not have periodicity_id
1200 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1201 h_sql := h_sql||' AND B.PERIODICITY_ID = P.PERIODICITY_ID';
1202 END IF;
1203 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1204 commit;
1205
1206 -- Analyze the BSC_TMP_PROJECTIONS_Y
1207 -- Bug#3740230: We cannot analyze. It is causing Loader hangs truncating this table
1208 -- Bug#3756654: Oracle 8i does not support gathering stacts on temporary tables
1209 --IF BSC_IM_UTILS.get_db_version <> '8i' THEN
1210 -- BSC_BIA_WRAPPER.Analyze_Table('BSC_TMP_PROJECTIONS_Y');
1211 --END IF;
1212
1213 -- Update the projection table BSC_TMP_PROJ_CALC
1214 h_sql := 'UPDATE /*+ORDERED USE_NL(B)*/ BSC_TMP_PROJ_CALC B'||
1215 ' SET ('||x_lst_data_temp;
1216 h_sql := h_sql||') = ('||
1217 ' SELECT '||h_lst_data;
1218 h_sql := h_sql||
1219 ' FROM BSC_TMP_PROJECTIONS_Y P'||
1220 ' WHERE P.ROW_ID = B.ROWID)'||
1221 ' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_PROJECTIONS_Y)';
1222 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1223 commit;
1224
1225 -- Fix bug#4463132: Truncate temporary table after use
1226 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
1227 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
1228 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
1229 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
1230 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
1231 commit;
1232
1233 RETURN TRUE;
1234
1235 EXCEPTION
1236 WHEN e_unexpected_error THEN
1237 BSC_MESSAGE.Add(
1238 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_THREEMONTH_FAILED'),
1239 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_3_Periods_Perf');
1240 RETURN FALSE;
1241
1242 WHEN OTHERS THEN
1243 BSC_MESSAGE.Add(
1244 X_Message => SQLERRM,
1245 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_3_Periods_Perf');
1246 RETURN FALSE;
1247
1248 END Calculate_Proj_3_Periods_Perf;
1249
1250
1251 /*===========================================================================+
1252 | FUNCTION Calculate_Proj_User_Defined
1253 +============================================================================*/
1254 FUNCTION Calculate_Proj_User_Defined(
1255 x_table_name IN VARCHAR2,
1256 x_periodicity IN NUMBER,
1257 x_period IN NUMBER,
1258 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1259 x_num_key_columns IN NUMBER,
1260 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1261 x_num_data_columns IN NUMBER,
1262 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
1263 x_current_fy IN NUMBER,
1264 x_is_base IN BOOLEAN,
1265 x_aw_flag IN BOOLEAN
1266 ) RETURN BOOLEAN IS
1267
1268 e_unexpected_error EXCEPTION;
1269
1270 h_sql VARCHAR2(32700);
1271
1272 h_yearly_flag NUMBER;
1273
1274 h_src_table VARCHAR2(30);
1275
1276 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1277 l_num_bind_vars NUMBER;
1278
1279 h_lst_data_columns VARCHAR2(32700);
1280
1281 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1282 h_i NUMBER;
1283
1284 BEGIN
1285 h_yearly_flag := 0;
1286 l_num_bind_vars := 0;
1287 h_lst_data_columns := NULL;
1288
1289 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1290
1291 FOR h_i IN 1..x_num_key_columns LOOP
1292 h_key_columns_temp(h_i) := 'KEY'||h_i;
1293 END LOOP;
1294
1295 --BSC-MV Note: In this architecture if this is a summary table the
1296 -- the custom projection TYPE=90 is in the MV.
1297 --AW_INTEGRATION: In this architecture we never calculate projection on PT tables,
1298 -- only on base tables, so no changes here
1299 IF BSC_APPS.bsc_mv AND (NOT x_is_base) THEN
1300 h_src_table := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(x_table_name);
1301 ELSE
1302 h_src_table := x_table_name;
1303 END IF;
1304
1305 l_bind_vars_values.delete;
1306
1307 h_sql := 'UPDATE BSC_TMP_PROJ_CALC T'||
1308 ' SET ('||x_lst_data_temp;
1309 h_sql := h_sql||') = ('||
1310 ' SELECT '||h_lst_data_columns;
1311 h_sql := h_sql||
1312 ' FROM '||h_src_table||' B'||
1313 ' WHERE ';
1314 IF x_num_key_columns > 0 THEN
1315 h_sql := h_sql||
1316 BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T',
1317 h_key_columns_temp,
1318 'B',
1319 x_key_columns,
1320 x_num_key_columns,
1321 'AND')||
1322 ' AND ';
1323 END IF;
1324 --AW_INTEGRATION: Base table does not have periodicity_id
1325 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1326 h_sql := h_sql||'T.PERIODICITY_ID = B.PERIODICITY_ID AND ';
1327 END IF;
1328 h_sql := h_sql||'T.YEAR = B.YEAR'||
1329 ' AND T.PERIOD = B.PERIOD'||
1330 ' AND B.TYPE = 90'||
1331 ')';
1332 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1333 IF h_yearly_flag = 1 THEN -- Annual
1334 h_sql := h_sql||' WHERE T.YEAR > :1'||
1335 ' AND T.TYPE = :2'||
1336 ' AND T.PERIOD = :3';
1337 l_bind_vars_values(1) := x_period;
1338 l_bind_vars_values(2) := 0;
1339 l_bind_vars_values(3) := 0;
1340 l_num_bind_vars := 3;
1341 ELSE
1342 h_sql := h_sql||' WHERE T.YEAR = :1'||
1343 ' AND T.TYPE = :2'||
1344 ' AND T.PERIOD > :3';
1345 l_bind_vars_values(1) := x_current_fy;
1346 l_bind_vars_values(2) := 0;
1347 l_bind_vars_values(3) := x_period;
1348 l_num_bind_vars := 3;
1349 END IF;
1350 -- AW_INTEGRATION: Base table does not have periodicity_id
1351 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1352 h_sql := h_sql||' AND T.PERIODICITY_ID = :4';
1353 l_bind_vars_values(4) := x_periodicity;
1354 l_num_bind_vars := 4;
1355 END IF;
1356
1357 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
1358 commit;
1359
1360 RETURN TRUE;
1361
1362 EXCEPTION
1363 WHEN e_unexpected_error THEN
1364 BSC_MESSAGE.Add(
1365 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_UD_FAILED'),
1366 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_User_Defined');
1367 RETURN FALSE;
1368
1369 WHEN OTHERS THEN
1370 BSC_MESSAGE.Add(
1371 X_Message => SQLERRM,
1372 X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_User_Defined');
1373 RETURN FALSE;
1374
1375 END Calculate_Proj_User_Defined;
1376
1377
1378 /*===========================================================================+
1379 | FUNCTION Calculate_Projection
1380 +============================================================================*/
1381 FUNCTION Calculate_Projection(
1382 x_table_name IN VARCHAR2,
1383 x_periodicity IN NUMBER,
1384 x_period IN NUMBER,
1385 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1386 x_num_key_columns IN NUMBER,
1387 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1388 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
1389 x_num_data_columns IN NUMBER,
1390 x_current_fy IN NUMBER,
1391 x_num_of_years IN NUMBER,
1392 x_previous_years IN NUMBER,
1393 x_is_base IN BOOLEAN,
1394 x_aw_flag IN BOOLEAN,
1395 x_change_vector_value IN NUMBER
1396 ) RETURN BOOLEAN IS
1397
1398 e_unexpected_error EXCEPTION;
1399
1400 h_i NUMBER;
1401
1402 h_sql VARCHAR2(32000);
1403 h_lst_key_columns VARCHAR2(32000);
1404 h_lst_data_columns VARCHAR2(32000);
1405 h_mv_name VARCHAR2(30);
1406 h_yearly_flag NUMBER;
1407
1408 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1409 h_num_bind_vars NUMBER;
1410
1411 h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1412 h_num_data_columns NUMBER;
1413
1414 h_lst_data_temp VARCHAR2(32000);
1415
1416 BEGIN
1417
1418 h_lst_key_columns := NULL;
1419 h_lst_data_columns := NULL;
1420 h_lst_data_temp := NULL;
1421
1422 --ENH_PROJECTION_4235711: Please review changes in this function and the functions called from here.
1423 -- We are going to calculate the projection in a temporary table called BSC_TMP_PROJ_CALC
1424 -- If it is a base table:
1425 -- 1. If the new current period is greater than the current period then we nedd to calculate
1426 -- projection for all the dimensions combinations.
1427 -- 2. If the new current period is equal to the current perido then we need to calculate projection
1428 -- only for the dimension combinations coming from bsc_tmp_base (rows from the input table)
1429 -- If it is in MV architecture we do not merge to the base/summary table here. There will be
1430 -- another function that will rollup the projection in BSC_TMP_PROJ_CALC to higher periodicities
1431 -- and then we will merge to the base/summary table once. This is to reduce the hit to the base table
1432 -- If it is Summary Tables Architecture or the table if for AW we know that there are no higher
1433 -- periodicities so we merge to the base/summary table here.
1434
1435 --BSC_UPDATE_LOG.Write_Line_Log('Calculating projection in '||x_table_name, BSC_UPDATE_LOG.LOG);
1436
1437 -- BSC-MV Note: In this architecture the summary table correspond to the projection table.
1438 -- The projection table was created to store the projection for indicators
1439 -- with targets at different levels.
1440
1441 -- Init BSC_TMP_PROJ_CALC with the rows for projection for the dimension combinations
1442 -- we need to recalculate projection
1443 -- AW_INTEGRATION: pass x_aw_flag to Init_Projection_Table
1444 IF NOT Init_Projection_Table(x_table_name,
1445 x_periodicity,
1446 x_key_columns,
1447 x_num_key_columns,
1448 x_current_fy,
1449 x_period,
1450 x_is_base,
1451 x_aw_flag,
1452 x_change_vector_value) THEN
1453 RAISE e_unexpected_error;
1454 END IF;
1455
1456 -- From now on we are going to calculate projection on all the data columns with same
1457 -- projection method at the same time.
1458
1459 -- Method 1: Avg last year
1460 h_num_data_columns := 0;
1461 h_lst_data_temp := NULL;
1462 FOR h_i IN 1 .. x_num_data_columns LOOP
1463 IF x_data_proj_methods(h_i) = 1 THEN
1464 h_num_data_columns := h_num_data_columns + 1;
1465 h_data_columns(h_num_data_columns) := x_data_columns(h_i);
1466 IF h_lst_data_temp IS NULL THEN
1467 h_lst_data_temp := 'DATA'||h_i;
1468 ELSE
1469 h_lst_data_temp := h_lst_data_temp||', DATA'||h_i;
1470 END IF;
1471 END IF;
1472 END LOOP;
1473
1474 IF h_num_data_columns > 0 THEN
1475 -- AW_INTEGRATION: Pass x_aw_flag to Calculate_Proj_Avg_Last_Year
1476 IF NOT Calculate_Proj_Avg_Last_Year(x_table_name,
1477 x_periodicity,
1478 x_period,
1479 x_key_columns,
1480 x_num_key_columns,
1481 h_data_columns,
1482 h_num_data_columns,
1483 h_lst_data_temp,
1484 x_current_fy,
1485 x_num_of_years,
1486 x_previous_years,
1487 x_is_base,
1488 x_aw_flag) THEN
1489 RAISE e_unexpected_error;
1490 END IF;
1491 END IF;
1492
1493 -- Method 3: Last 3 Periods Performance
1494 h_num_data_columns := 0;
1495 h_lst_data_temp := NULL;
1496 FOR h_i IN 1 .. x_num_data_columns LOOP
1497 IF x_data_proj_methods(h_i) = 3 THEN
1498 h_num_data_columns := h_num_data_columns + 1;
1499 h_data_columns(h_num_data_columns) := x_data_columns(h_i);
1500 IF h_lst_data_temp IS NULL THEN
1501 h_lst_data_temp := 'DATA'||h_i;
1502 ELSE
1503 h_lst_data_temp := h_lst_data_temp||', DATA'||h_i;
1504 END IF;
1505 END IF;
1506
1507 -- We can calculate projection on maximum 25 measures at the time.
1508 -- This restriction is for the big sql generated.
1509 IF (h_num_data_columns = 25) OR ((h_i = x_num_data_columns) AND (h_num_data_columns > 0)) THEN
1510 -- AW_INTEGRATION: Pass x_aw_flag to Calculate_Proj_3_Periods_Perf
1511 IF NOT Calculate_Proj_3_Periods_Perf(x_table_name,
1512 x_periodicity,
1513 x_period,
1514 x_key_columns,
1515 x_num_key_columns,
1516 h_data_columns,
1517 h_num_data_columns,
1518 h_lst_data_temp,
1519 x_current_fy,
1520 x_is_base,
1521 x_aw_flag) THEN
1522 RAISE e_unexpected_error;
1523 END IF;
1524 h_num_data_columns := 0;
1525 h_lst_data_temp := NULL;
1526 END IF;
1527 END LOOP;
1528
1529 -- Method 4: Custom projection
1530 h_num_data_columns := 0;
1531 h_lst_data_temp := NULL;
1532 FOR h_i IN 1 .. x_num_data_columns LOOP
1533 IF x_data_proj_methods(h_i) = 4 THEN
1534 h_num_data_columns := h_num_data_columns + 1;
1535 h_data_columns(h_num_data_columns) := x_data_columns(h_i);
1536 IF h_lst_data_temp IS NULL THEN
1537 h_lst_data_temp := 'DATA'||h_i;
1538 ELSE
1539 h_lst_data_temp := h_lst_data_temp||', DATA'||h_i;
1540 END IF;
1541 END IF;
1542 END LOOP;
1543
1544 IF h_num_data_columns > 0 THEN
1545 -- AW_INTEGRATION: pass x_aw_flag to Calculate_Proj_User_Defined
1546 IF NOT Calculate_Proj_User_Defined(x_table_name,
1547 x_periodicity,
1548 x_period,
1549 x_key_columns,
1550 x_num_key_columns,
1551 h_data_columns,
1552 h_num_data_columns,
1553 h_lst_data_temp,
1554 x_current_fy,
1555 x_is_base,
1556 x_aw_flag) THEN
1557 RAISE e_unexpected_error;
1558 END IF;
1559 END IF;
1560
1561 -- If we are in Summary tables architecture or AW we merge to the base table here
1562 -- If it is MV architecture we meger to the base table later when the projection
1563 -- have been rolled up to higher periodicities
1564 IF (NOT BSC_APPS.bsc_mv) OR x_aw_flag THEN
1565 IF NOT Merge_Projection(x_table_name,
1566 x_key_columns,
1567 x_num_key_columns,
1568 x_data_columns,
1569 x_num_data_columns,
1570 x_is_base,
1571 x_aw_flag) THEN
1572 RAISE e_unexpected_error;
1573 END IF;
1574
1575 -- Fix bug#4463132: Truncate temporary table after use
1576 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
1577 commit;
1578 END IF;
1579
1580 --BSC_UPDATE_LOG.Write_Line_Log('End Calculating projection in '||x_table_name, BSC_UPDATE_LOG.LOG);
1581
1582 COMMIT;
1583
1584 RETURN TRUE;
1585
1586 EXCEPTION
1587 WHEN e_unexpected_error THEN
1588 ROLLBACK;
1589 BSC_MESSAGE.Add(
1590 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
1591 X_Source => 'BSC_UPDATE_CALC.Calculate_Projection');
1592 RETURN FALSE;
1593
1594 WHEN OTHERS THEN
1595 ROLLBACK;
1596 BSC_MESSAGE.Add(
1597 X_Message => SQLERRM,
1598 X_Source => 'BSC_UPDATE_CALC.Calculate_Projection');
1599 RETURN FALSE;
1600
1601 END Calculate_Projection;
1602
1603
1604 /*===========================================================================+
1605 | FUNCTION Calculate_Zero_Code
1606 +============================================================================*/
1607 FUNCTION Calculate_Zero_Code(
1608 x_table_name IN VARCHAR2,
1609 x_zero_code_calc_method IN NUMBER,
1610 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1611 x_num_key_columns IN NUMBER,
1612 x_src_table IN VARCHAR2
1613 ) RETURN BOOLEAN IS
1614
1615 e_unexpected_error EXCEPTION;
1616
1617 -- x_zero_code_calc_method = 4
1618 -- Calculate the zero code for one or more key columns that
1619 -- are independents (Example: region_code, product_code)
1620
1621 -- Meaning of parameters in bsc_db_calculations
1622 -- parameter1 = key column name
1623 -- parameter2 = order
1624 -- parameter3 = data column name
1625 -- parameter4 = condition
1626 -- parameter5 = expression or formula
1627
1628 TYPE t_cursor IS REF CURSOR;
1629
1630 h_sql VARCHAR2(32700);
1631 h_i NUMBER;
1632 h_j NUMBER;
1633 h_count NUMBER;
1634
1635 cursor c_key_columns(pTableName varchar2, pCalcType number,
1636 pSrcTableName varchar2, pSrcCalcType number) is
1637 SELECT parameter1
1638 FROM bsc_db_calculations
1639 WHERE table_name = pTableName
1640 AND calculation_type = pCalcType
1641 AND parameter1 NOT IN (
1642 SELECT parameter1
1643 FROM bsc_db_calculations
1644 WHERE table_name = pSrcTableName
1645 AND calculation_type = pSrcCalcType
1646 )
1647 GROUP BY parameter1, TO_NUMBER(parameter2)
1648 ORDER BY TO_NUMBER(parameter2);
1649
1650 h_key_zero_code bsc_db_calculations.parameter1%TYPE;
1651
1652 cursor c_keys_needing_zero_code(pTableName varchar2) is
1653 SELECT DISTINCT c.parameter1
1654 FROM bsc_db_calculations c, bsc_kpi_data_tables kt, bsc_kpi_data_tables ktp
1655 WHERE c.table_name = kt.table_name AND
1656 c.calculation_type = 4 AND
1657 kt.indicator = ktp.indicator AND
1658 kt.dim_set_id = ktp.dim_set_id AND
1659 ktp.table_name = pTableName;
1660
1661 h_key VARCHAR2(50);
1662
1663 cursor c_data_columns(pTableName varchar2,pColumnType varchar2) is
1664 SELECT column_name, source_formula
1665 FROM bsc_db_tables_cols
1666 WHERE table_name = pTableName AND column_type = pColumnType;
1667
1668 --Fix bug#5057247 fix data type of this variables
1669 h_data_column bsc_db_tables_cols.column_name%TYPE;
1670 h_expression bsc_db_tables_cols.source_formula%TYPE;
1671
1672 h_lst_where VARCHAR2(32700);
1673 h_lst_keys VARCHAR2(32700);
1674 h_lst_select VARCHAR2(32700);
1675 h_lst_groupby VARCHAR2(32700);
1676
1677 h_lst_data_columns VARCHAR2(32700);
1678 h_lst_expressions VARCHAR2(32700);
1679
1680 CURSOR c_pt_name (p_sum_table VARCHAR2) IS
1681 SELECT DISTINCT projection_data
1682 FROM bsc_kpi_data_tables
1683 WHERE table_name = p_sum_table;
1684
1685 h_ref_table VARCHAR2(30);
1686
1687 h_zero_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1688 h_num_zero_key_columns NUMBER;
1689
1690 h_column_type VARCHAR2(10);
1691
1692 BEGIN
1693 h_lst_where := NULL;
1694 h_lst_keys := NULL;
1695 h_lst_select := NULL;
1696 h_lst_groupby := NULL;
1697 h_lst_data_columns := NULL;
1698 h_lst_expressions := NULL;
1699 h_ref_table := NULL;
1700
1701 -- BSC-MV Note: If this procedure is called, then the summary table has a
1702 -- corresponding projection table. The zero codes will be calculated on the projection
1703 -- table for all the periodicities. PERIODICITY_ID and PERIOD_TYPE_ID will be
1704 -- considered as key columns.
1705 IF BSC_APPS.bsc_mv THEN
1706 OPEN c_pt_name(x_table_name);
1707 FETCH c_pt_name INTO h_ref_table;
1708 CLOSE c_pt_name;
1709 ELSE
1710 h_ref_table := x_table_name;
1711 END IF;
1712
1713 -- Get the keys for zero codes
1714 h_num_zero_key_columns := 0;
1715 OPEN c_key_columns (x_table_name, x_zero_code_calc_method, x_src_table, x_zero_code_calc_method);
1716 FETCH c_key_columns INTO h_key_zero_code;
1717 WHILE c_key_columns%FOUND LOOP
1718 h_num_zero_key_columns := h_num_zero_key_columns + 1;
1719 h_zero_key_columns(h_num_zero_key_columns) := h_key_zero_code;
1720
1721 FETCH c_key_columns INTO h_key_zero_code;
1722 END LOOP;
1723 CLOSE c_key_columns;
1724
1725 -- Bug#3542344: Only in summary tables architecture, when this table receives targets,
1726 -- we need to re-calculate the zero code on keys needing zero.
1727 -- Keys needing zero code may not be configured in bsc_db_calculations for this table,
1728 -- for this reason we need to look in the origin tables until the base table looking
1729 -- for the keys that calcualted zero code.
1730 IF NOT BSC_APPS.bsc_mv THEN
1731 -- Only for summary tables architecture
1732 SELECT count(table_name)
1733 INTO h_count
1734 FROM bsc_db_calculations
1735 WHERE table_name = x_table_name AND calculation_type = 5;
1736
1737 IF h_count > 0 THEN
1738 -- This table receives targets
1739 -- We need to re-calculate zero code in all the keys needing zero cdoe.
1740
1741 -- The next cursor returns all the key columns calculating zero code
1742 -- between the tables used by the indicator and dim_set_id using this table.
1743 -- If this table is using one of those keys and the key is not
1744 -- already in h_zero_key_columns then we ned to add it.
1745 OPEN c_keys_needing_zero_code(x_table_name);
1746 LOOP
1747 FETCH c_keys_needing_zero_code INTO h_key;
1748 EXIT WHEN c_keys_needing_zero_code%NOTFOUND;
1749
1750 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, x_key_columns, x_num_key_columns) THEN
1751 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, h_zero_key_columns, h_num_zero_key_columns) THEN
1752 h_num_zero_key_columns := h_num_zero_key_columns + 1;
1753 h_zero_key_columns(h_num_zero_key_columns) := h_key;
1754 END IF;
1755 END IF;
1756 END LOOP;
1757 CLOSE c_keys_needing_zero_code;
1758 END IF;
1759 END IF;
1760
1761 -- Note: We only use and support method 4.
1762
1763 IF h_num_zero_key_columns = 0 THEN
1764 -- No columns to calculate zero code
1765 RETURN TRUE;
1766 END IF;
1767
1768 -- Delete existing zero codes
1769 -- BSC-BIS-DIMENSIONS: Need to use '0' in the condition to be compatible with
1770 -- NUMBER of VARCHAR2 in the key columns
1771 FOR h_j IN 1..h_num_zero_key_columns LOOP
1772 IF h_lst_where IS NULL THEN
1773 h_lst_where := '('||h_zero_key_columns(h_j)||' = ''0'')';
1774 ELSE
1775 h_lst_where := h_lst_where||' OR ('||h_zero_key_columns(h_j)||' = ''0'')';
1776 END IF;
1777 END LOOP;
1778
1779 h_sql := 'DELETE FROM '||h_ref_table||
1780 ' WHERE '||h_lst_where;
1781 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1782 -- Fix bug#4116490 need commit
1783 commit;
1784
1785 -- Data columns
1786 h_lst_data_columns := NULL;
1787 h_lst_expressions := NULL;
1788
1789 h_column_type := 'A';
1790 OPEN c_data_columns (x_table_name, h_column_type) ;
1791 FETCH c_data_columns INTO h_data_column, h_expression;
1792 WHILE c_data_columns%FOUND LOOP
1793 IF h_lst_data_columns IS NULL THEN
1794 h_lst_data_columns := h_data_column;
1795 ELSE
1796 h_lst_data_columns := h_lst_data_columns||', '||h_data_column;
1797 END IF;
1798
1799 IF h_lst_expressions IS NULL THEN
1800 h_lst_expressions := h_expression;
1801 ELSE
1802 h_lst_expressions := h_lst_expressions||', '||h_expression;
1803 END IF;
1804
1805 FETCH c_data_columns INTO h_data_column, h_expression;
1806 END LOOP;
1807 CLOSE c_data_columns;
1808
1809 -- Calculate the zero code per each key column
1810 FOR h_j IN 1..h_num_zero_key_columns LOOP
1811 -- Get each part of the query
1812
1813 -- Keys columns
1814 h_lst_keys := x_key_columns(1);
1815 IF x_key_columns(1) = h_zero_key_columns(h_j) THEN
1816 h_lst_select := '''0''';
1817 h_lst_groupby := NULL;
1818 ELSE
1819 h_lst_select := x_key_columns(1);
1820 h_lst_groupby := x_key_columns(1);
1821 END IF;
1822
1823 FOR h_i IN 2 .. x_num_key_columns LOOP
1824 h_lst_keys := h_lst_keys||', '||x_key_columns(h_i);
1825
1826 IF x_key_columns(h_i) = h_zero_key_columns(h_j) THEN
1827 h_lst_select := h_lst_select||', ''0''';
1828 ELSE
1829 h_lst_select := h_lst_select||', '||x_key_columns(h_i);
1830 IF h_lst_groupby IS NULL THEN
1831 h_lst_groupby := x_key_columns(h_i);
1832 ELSE
1833 h_lst_groupby := h_lst_groupby||', '||x_key_columns(h_i);
1834 END IF;
1835 END IF;
1836 END LOOP;
1837
1838 h_lst_keys := h_lst_keys||', YEAR, TYPE, PERIOD';
1839 h_lst_select := h_lst_select||', YEAR, TYPE, PERIOD';
1840 IF h_lst_groupby IS NULL THEN
1841 h_lst_groupby := 'YEAR, TYPE, PERIOD';
1842 ELSE
1843 h_lst_groupby := h_lst_groupby||', YEAR, TYPE, PERIOD';
1844 END IF;
1845
1846 --BSC-MV Note: Add periodicity_id and period_type_id
1847 IF BSC_APPS.bsc_mv THEN
1848 h_lst_keys := h_lst_keys||', PERIODICITY_ID, PERIOD_TYPE_ID';
1849 h_lst_select := h_lst_select||', PERIODICITY_ID, PERIOD_TYPE_ID';
1850 IF h_lst_groupby IS NULL THEN
1851 h_lst_groupby := 'PERIODICITY_ID, PERIOD_TYPE_ID';
1852 ELSE
1853 h_lst_groupby := h_lst_groupby||', PERIODICITY_ID, PERIOD_TYPE_ID';
1854 END IF;
1855 END IF;
1856
1857 -- Insert the zero code for the key column, h_zero_key_columns(h_j)
1858 h_sql := 'INSERT /*+ append ';
1859 if BSC_UPDATE_UTIL.is_parallel then
1860 h_sql:=h_sql||'parallel ('||h_ref_table||') ';
1861 end if;
1862 h_sql:=h_sql||' */';
1863 h_sql:=h_sql||'INTO '||h_ref_table||
1864 ' ('||h_lst_keys||', '||h_lst_data_columns||')'||
1865 ' SELECT ';
1866 if BSC_UPDATE_UTIL.is_parallel then
1867 h_sql:=h_sql||'/*+ parallel ('||h_ref_table||')*/ ';
1868 end if;
1869 h_sql:=h_sql||h_lst_select||', '||h_lst_expressions||
1870 ' FROM '||h_ref_table;
1871 h_sql := h_sql||' GROUP BY '||h_lst_groupby;
1872 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1873 commit;
1874 END LOOP;
1875 COMMIT;
1876
1877 RETURN TRUE;
1878
1879 EXCEPTION
1880 WHEN e_unexpected_error THEN
1881 ROLLBACK;
1882 BSC_MESSAGE.Add(
1883 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_ZEROCODE_CALC_FAILED'),
1884 X_Source => 'BSC_UPDATE_CALC.Calculate_Zero_Code');
1885 RETURN FALSE;
1886
1887 WHEN OTHERS THEN
1888 ROLLBACK;
1889 BSC_MESSAGE.Add(
1890 X_Message => SQLERRM,
1891 X_Source => 'BSC_UPDATE_CALC.Calculate_Zero_Code');
1892 RETURN FALSE;
1893
1894 END Calculate_Zero_Code;
1895
1896
1897 /*===========================================================================+
1898 | FUNCTION Create_Proj_Temps
1899 +============================================================================*/
1900 FUNCTION Create_Proj_Temps(
1901 x_periodicity IN NUMBER,
1902 x_current_fy IN NUMBER,
1903 x_num_of_years IN NUMBER,
1904 x_previous_years IN NUMBER,
1905 x_trunc_proj_table IN BOOLEAN
1906 ) RETURN BOOLEAN IS
1907
1908 e_unexpected_error EXCEPTION;
1909
1910 h_sql VARCHAR2(32700);
1911 h_i NUMBER;
1912
1913 h_init_period NUMBER;
1914 h_end_period NUMBER;
1915
1916 h_calendar_col_name VARCHAR2(30);
1917
1918 h_calendar_id NUMBER;
1919 h_yearly_flag NUMBER;
1920 h_edw_flag NUMBER;
1921
1922 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1923
1924 BEGIN
1925
1926 h_yearly_flag := 0;
1927 h_edw_flag := 0;
1928
1929 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1930 h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
1931 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1932
1933 -- BSC_TMP_ALL_PERIODS
1934 --h_sql := 'DELETE FROM BSC_TMP_ALL_PERIODS';
1935 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1936 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1937 commit;
1938
1939 IF h_yearly_flag = 1 THEN -- Annual
1940 h_init_period := x_current_fy - x_previous_years;
1941 h_end_period := h_init_period + x_num_of_years - 1;
1942
1943 FOR h_i IN h_init_period..h_end_period LOOP
1944 l_bind_vars_values.delete ;
1945 h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1946 ' VALUES (:1)';
1947 l_bind_vars_values(1) := (h_i);
1948 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1949 END LOOP;
1950 ELSE
1951 -- Periodicity different to Annual
1952 IF h_edw_flag = 0 THEN
1953 -- BSC periodicity
1954 h_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
1955
1956 l_bind_vars_values.delete ;
1957 h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS'||
1958 ' SELECT DISTINCT '||h_calendar_col_name||
1959 ' FROM bsc_db_calendar'||
1960 ' WHERE YEAR = :1'||' AND CALENDAR_ID = :2'||
1961 ' GROUP BY '||h_calendar_col_name;
1962 l_bind_vars_values(1) := (x_current_fy);
1963 l_bind_vars_values(2) := (h_calendar_id) ;
1964 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1965 ELSE
1966 -- EDW periodicity
1967 h_init_period := 1;
1968 h_end_period := BSC_INTEGRATION_APIS.Get_Number_Of_Periods(x_current_fy, x_periodicity, h_calendar_id);
1969 IF BSC_APPS.CheckError('BSC_INTEGRATION_APIS.Get_Number_Of_Periods') THEN
1970 RAISE e_unexpected_error;
1971 END IF;
1972
1973 FOR h_i IN h_init_period..h_end_period LOOP
1974 l_bind_vars_values.delete ;
1975 h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1976 ' VALUES (:1)';
1977 l_bind_vars_values(1) := (h_i) ;
1978 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1979 END LOOP;
1980 END IF;
1981 END IF;
1982
1983 --ENH_PROJECTION_4235711: truncate projectio table
1984 IF x_trunc_proj_table THEN
1985 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
1986 END IF;
1987
1988 RETURN TRUE;
1989
1990 EXCEPTION
1991 WHEN e_unexpected_error THEN
1992 BSC_MESSAGE.Add(
1993 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_TTABLES_FAILED'),
1994 X_Source => 'BSC_UPDATE_CALC.Create_Proj_Temps');
1995 RETURN FALSE;
1996
1997 WHEN OTHERS THEN
1998 BSC_MESSAGE.Add(
1999 X_Message => SQLERRM,
2000 X_Source => 'BSC_UPDATE_CALC.Create_Proj_Temps');
2001 RETURN FALSE;
2002
2003 END Create_Proj_Temps;
2004
2005
2006 /*===========================================================================+
2007 | FUNCTION Init_Projection_Table
2008 +============================================================================*/
2009 FUNCTION Init_Projection_Table(
2010 x_table_name IN VARCHAR2,
2011 x_periodicity IN NUMBER,
2012 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2013 x_num_key_columns IN NUMBER,
2014 x_current_fy IN NUMBER,
2015 x_current_period IN NUMBER,
2016 x_is_base IN BOOLEAN,
2017 x_aw_flag IN BOOLEAN,
2018 x_change_vector_value IN NUMBER
2019 ) RETURN BOOLEAN IS
2020
2021 h_i NUMBER;
2022 h_sql VARCHAR2(32700);
2023
2024 h_ref_table VARCHAR2(30);
2025
2026 h_lst_table_keys VARCHAR2(32700);
2027 h_lst_table_keys_a VARCHAR2(32700);
2028 h_lst_keys VARCHAR2(32700);
2029 h_lst_keys_nc VARCHAR2(32700);
2030
2031 h_yearly_flag NUMBER;
2032
2033 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2034 l_num_bind_vars NUMBER;
2035
2036 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
2037 h_lst_select VARCHAR2(32000);
2038
2039 h_old_current_period NUMBER;
2040
2041 BEGIN
2042
2043 h_lst_table_keys := NULL;
2044 h_lst_table_keys_a := NULL;
2045 h_lst_keys := NULL;
2046 h_lst_keys_nc := NULL;
2047 h_yearly_flag := 0;
2048 l_num_bind_vars := 0;
2049 h_lst_select := NULL;
2050
2051 FOR h_i IN 1..x_num_key_columns LOOP
2052 h_key_columns_temp(h_i) := 'KEY'||h_i;
2053 END LOOP;
2054
2055 IF x_is_base THEN
2056 SELECT current_period
2057 INTO h_old_current_period
2058 FROM bsc_db_tables
2059 WHERE table_name = x_table_name;
2060
2061 IF x_current_period > h_old_current_period THEN
2062 -- New current period, then we need to recalculate projection for all
2063 -- dimension combinations existing in the base table
2064 h_ref_table := x_table_name;
2065
2066 h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2067 x_key_columns,
2068 x_num_key_columns);
2069
2070 h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2071 x_key_columns,
2072 x_num_key_columns);
2073
2074 h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2075 ELSE
2076 -- There is no change in the current period, then we just need to
2077 -- recalculate projection for dimension combinations coming from
2078 -- the input table
2079 h_ref_table := 'BSC_TMP_BASE';
2080
2081 h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2082 h_key_columns_temp,
2083 x_num_key_columns);
2084
2085 h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2086 h_key_columns_temp,
2087 x_num_key_columns);
2088
2089 h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2090 END IF;
2091 ELSE
2092 --AW_INTEGRATION: In this architecture we do not calculate projections in PT tables
2093 -- only on base tables. So we know that the code does not get here. No changes here.
2094 IF BSC_APPS.bsc_mv THEN
2095 h_ref_table := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(x_table_name);
2096 ELSE
2097 h_ref_table := x_table_name;
2098 END IF;
2099
2100 h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
2101 x_key_columns,
2102 x_num_key_columns);
2103
2104 h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
2105 x_key_columns,
2106 x_num_key_columns);
2107
2108 h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2109 END IF;
2110
2111 h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2112
2113 IF h_lst_table_keys IS NOT NULL THEN
2114 h_lst_table_keys := h_lst_table_keys||', ';
2115 h_lst_table_keys_a := h_lst_table_keys_a||', ';
2116 h_lst_select := h_lst_select||', ';
2117 END IF;
2118
2119 -- AW_INTEGRATION: Base table does not have periodicity_id
2120 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2121 h_lst_table_keys := h_lst_table_keys||h_ref_table||'.PERIODICITY_ID, ';
2122 h_lst_table_keys_a := h_lst_table_keys_a||'A.PERIODICITY_ID, ';
2123 h_lst_select := h_lst_select||'PERIODICITY_ID, ';
2124
2125 IF h_lst_keys_nc IS NULL THEN
2126 h_lst_keys_nc := 'PERIODICITY_ID';
2127 ELSE
2128 h_lst_keys_nc := h_lst_keys_nc||', PERIODICITY_ID';
2129 END IF;
2130
2131 IF NOT x_is_base THEN
2132 h_lst_table_keys := h_lst_table_keys||h_ref_table||'.PERIOD_TYPE_ID, ';
2133 h_lst_table_keys_a := h_lst_table_keys_a||'A.PERIOD_TYPE_ID, ';
2134 h_lst_select := h_lst_select||'PERIOD_TYPE_ID, ';
2135 h_lst_keys_nc := h_lst_keys_nc||', PERIOD_TYPE_ID';
2136 END IF;
2137 END IF;
2138
2139 -- BSC-MV Note: Add condition on periodicity_id for new architecture
2140 -- Insert in the projection table BSC_TMP_PROJ_CALC the rows for the projected periods.
2141 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2142 l_bind_vars_values.delete ;
2143 IF h_yearly_flag = 1 THEN -- Annual
2144 h_sql := 'INSERT /*+ append ';
2145 IF BSC_UPDATE_UTIL.is_parallel THEN
2146 h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
2147 END IF;
2148 h_sql := h_sql||' */'||
2149 ' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
2150 -- AW_INTEGRATION: insert projection and change vector too
2151 IF x_aw_flag THEN
2152 h_sql := h_sql||', PROJECTION, CHANGE_VECTOR';
2153 END IF;
2154 h_sql := h_sql||')'||
2155 ' SELECT /*+ ordered */ '||h_lst_table_keys_a||'bsc_tmp_all_periods.period, 0, 0';
2156 -- AW_INTEGRATION: insert Y to projection column and x_change_vector to change_vector column
2157 IF x_aw_flag THEN
2158 h_sql := h_sql||', ''Y'', '||x_change_vector_value;
2159 END IF;
2160 h_sql := h_sql||' FROM ';
2161 IF h_lst_keys_nc IS NOT NULL THEN
2162 h_sql := h_sql||'('||
2163 ' SELECT DISTINCT '||h_lst_keys_nc||
2164 ' FROM '||h_ref_table;
2165 -- AW_INTEGRATION: Base table does not have periodicity_id
2166 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2167 h_sql := h_sql||' WHERE PERIODICITY_ID = :1';
2168 END IF;
2169 h_sql := h_sql||' ) A, ';
2170 END IF;
2171 h_sql := h_sql||'bsc_tmp_all_periods'||
2172 ' WHERE bsc_tmp_all_periods.period > :2';
2173 -- AW_INTEGRATION: Base table does not have periodicity_id
2174 IF (BSC_APPS.bsc_mv) AND (NOT x_aw_flag) AND (h_lst_keys_nc IS NOT NULL) THEN
2175 l_bind_vars_values(1) := (x_periodicity);
2176 l_bind_vars_values(2) := (x_current_fy);
2177 l_num_bind_vars := 2;
2178 ELSE
2179 l_bind_vars_values(1) := (x_current_fy);
2180 l_num_bind_vars := 1;
2181 END IF;
2182 ELSE
2183 h_sql := 'INSERT /*+ append ';
2184 IF BSC_UPDATE_UTIL.is_parallel THEN
2185 h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
2186 END IF;
2187 h_sql := h_sql||' */'||
2188 ' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
2189 -- AW_INTEGRATION: insert projection and change vector too
2190 IF x_aw_flag THEN
2191 h_sql := h_sql||', PROJECTION, CHANGE_VECTOR';
2192 END IF;
2193 h_sql := h_sql||')'||
2194 ' SELECT /*+ ordered */ '||h_lst_table_keys_a||':1, 0, bsc_tmp_all_periods.period';
2195 -- AW_INTEGRATION: insert Y to projection column and x_change_vector to change_vector column
2196 IF x_aw_flag THEN
2197 h_sql := h_sql||', ''Y'', '||x_change_vector_value;
2198 END IF;
2199 h_sql := h_sql||' FROM ';
2200 IF h_lst_keys_nc IS NOT NULL THEN
2201 h_sql := h_sql||'('||
2202 ' SELECT DISTINCT '||h_lst_keys_nc||
2203 ' FROM '||h_ref_table;
2204 -- AW_INTEGRATION: Base table does not have periodicity_id
2205 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2206 h_sql := h_sql||' WHERE PERIODICITY_ID = :2';
2207 END IF;
2208 h_sql := h_sql||' ) A, ';
2209 END IF;
2210 h_sql := h_sql||'bsc_tmp_all_periods'||
2211 ' WHERE bsc_tmp_all_periods.period > :3';
2212 -- AW_INTEGRATION: Base table does not have periodicity_id
2213 IF (BSC_APPS.bsc_mv) AND (NOT x_aw_flag) AND (h_lst_keys_nc IS NOT NULL) THEN
2214 l_bind_vars_values(1) := (x_current_fy);
2215 l_bind_vars_values(2) := (x_periodicity);
2216 l_bind_vars_values(3) := (x_current_period);
2217 l_num_bind_vars := 3;
2218 ELSE
2219 l_bind_vars_values(1) := (x_current_fy);
2220 l_bind_vars_values(2) := (x_current_period);
2221 l_num_bind_vars := 2;
2222 END IF;
2223 END IF;
2224 commit;
2225 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2226 commit;
2227
2228 RETURN TRUE;
2229
2230 EXCEPTION
2231 WHEN OTHERS THEN
2232 BSC_MESSAGE.Add(
2233 X_Message => SQLERRM,
2234 X_Source => 'BSC_UPDATE_CALC.Init_Projection_Table');
2235 RETURN FALSE;
2236
2237 END Init_Projection_Table;
2238
2239
2240 /*===========================================================================+
2241 | FUNCTION Delete_Projection
2242 +============================================================================*/
2243 FUNCTION Delete_Projection(
2244 x_table_name IN VARCHAR2,
2245 x_periodicity IN NUMBER,
2246 x_period IN NUMBER,
2247 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2248 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2249 x_num_data_columns IN NUMBER,
2250 x_current_fy IN NUMBER,
2251 x_is_base IN BOOLEAN
2252 ) RETURN BOOLEAN IS
2253
2254 h_i NUMBER;
2255 h_sql VARCHAR2(32700);
2256
2257 h_lst_set VARCHAR2(32700);
2258
2259 h_yearly_flag NUMBER;
2260
2261 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2262 l_num_bind_vars NUMBER;
2263
2264 BEGIN
2265 h_lst_set := NULL;
2266 h_yearly_flag := 0;
2267 l_num_bind_vars := 0;
2268
2269 FOR h_i IN 1 .. x_num_data_columns LOOP
2270 -- If it is a base table the delete the projection from all the data columns
2271 -- This is because some data column could had projection in the past but now
2272 -- do not aply projection
2273 IF x_is_base OR (x_data_proj_methods(h_i) <> 0) THEN
2274 IF h_lst_set IS NULL THEN
2275 h_lst_set := x_data_columns(h_i)||' = NULL';
2276 ELSE
2277 h_lst_set := h_lst_set||', '||x_data_columns(h_i)||' = NULL';
2278 END IF;
2279 END IF;
2280 END LOOP;
2281
2282 IF h_lst_set IS NOT NULL THEN
2283 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2284 l_bind_vars_values.delete ;
2285 IF h_yearly_flag = 1 THEN
2286 h_sql := 'UPDATE '||x_table_name||
2287 ' SET '||h_lst_set||
2288 ' WHERE YEAR > :1'||' AND TYPE = :2';
2289 l_bind_vars_values(1) := (x_period);
2290 l_bind_vars_values(2) := 0 ;
2291 l_num_bind_vars := 2 ;
2292
2293 -- BSC-MV Note: Add condition on periodicity_id
2294 IF BSC_APPS.bsc_mv THEN
2295 h_sql := h_sql||' AND PERIODICITY_ID = :3';
2296 l_bind_vars_values(3) := x_periodicity;
2297 l_num_bind_vars := 3;
2298 END IF;
2299 ELSE
2300 h_sql := 'UPDATE '||x_table_name||
2301 ' SET '||h_lst_set||
2302 ' WHERE YEAR = :1'||' AND PERIOD > :2'||' AND TYPE = :3';
2303 l_bind_vars_values(1) := (x_current_fy);
2304 l_bind_vars_values(2) := (x_period) ;
2305 l_bind_vars_values(3) := 0 ;
2306 l_num_bind_vars := 3 ;
2307
2308 -- BSC-MV Note: Add condition on periodicity_id
2309 IF BSC_APPS.bsc_mv THEN
2310 h_sql := h_sql||' AND PERIODICITY_ID = :4';
2311 l_bind_vars_values(4) := x_periodicity;
2312 l_num_bind_vars := 4;
2313 END IF;
2314 END IF;
2315
2316 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2317 END IF;
2318
2319 RETURN TRUE;
2320
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 BSC_MESSAGE.Add(
2324 X_Message => SQLERRM,
2325 X_Source => 'BSC_UPDATE_CALC.Delete_Projection');
2326 RETURN FALSE;
2327
2328 END Delete_Projection;
2329
2330
2331 /*===========================================================================+
2332 | FUNCTION Delete_Projection_Base_Table
2333 +============================================================================*/
2334 FUNCTION Delete_Projection_Base_Table(
2335 x_table_name IN VARCHAR2,
2336 x_periodicity IN NUMBER,
2337 x_current_period IN NUMBER,
2338 x_new_current_period IN NUMBER,
2339 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2340 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
2341 x_num_data_columns IN NUMBER,
2342 x_current_fy IN NUMBER,
2343 x_aw_flag IN BOOLEAN,
2344 x_change_vector_value IN NUMBER
2345 ) RETURN BOOLEAN IS
2346
2347 h_i NUMBER;
2348 h_sql VARCHAR2(32700);
2349
2350 h_lst_set VARCHAR2(32700);
2351 h_lst_where VARCHAR2(32700);
2352
2353 h_yearly_flag NUMBER;
2354
2355 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2356 l_num_bind_vars NUMBER;
2357
2358 BEGIN
2359 h_lst_set := NULL;
2360 h_lst_where := NULL;
2361 h_yearly_flag := 0;
2362 l_num_bind_vars := 0;
2363
2364 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2365
2366 --ENH_PROJECTION_4235711: Do this only if new current period > current period
2367 IF x_new_current_period > x_current_period THEN
2368 FOR h_i IN 1 .. x_num_data_columns LOOP
2369 -- Delete the projection from all the data columns
2370 IF h_lst_set IS NULL THEN
2371 h_lst_set := x_data_columns(h_i)||' = NULL';
2372 h_lst_where := x_data_columns(h_i)||' IS NOT NULL';
2373 ELSE
2374 h_lst_set := h_lst_set||', '||x_data_columns(h_i)||' = NULL';
2375 h_lst_where := h_lst_where||' OR '||x_data_columns(h_i)||' IS NOT NULL';
2376 END IF;
2377 END LOOP;
2378
2379 IF h_lst_set IS NOT NULL THEN
2380 l_bind_vars_values.delete ;
2381 IF h_yearly_flag = 1 THEN
2382 -- There is no need to delete projection for yearly periodicity
2383 -- The condition will be year > current_fy and year <= current_fy
2384 -- which always is false.
2385 NULL;
2386 ELSE
2387 -- AW_INTEGRATION: Need to set projection column to 'N' and change_vector to x_change_vector value
2388 IF BSC_APPS.bsc_mv AND x_aw_flag THEN
2389 h_lst_set := h_lst_set||', projection = ''N'', change_vector = '||x_change_vector_value;
2390 END IF;
2391
2392 h_sql := 'UPDATE '||x_table_name||
2393 ' SET '||h_lst_set||
2394 ' WHERE YEAR = :1 AND PERIOD > :2 AND PERIOD <= :3 AND TYPE = :4';
2395 l_bind_vars_values(1) := (x_current_fy);
2396 l_bind_vars_values(2) := (x_current_period) ;
2397 l_bind_vars_values(3) := (x_new_current_period) ;
2398 l_bind_vars_values(4) := 0 ;
2399 l_num_bind_vars := 4 ;
2400
2401 -- BSC-MV Note: Add condition on periodicity_id
2402 -- AW_INTEGRATION: If the base table is for AW then there is no higher periodicities
2403 -- and there is no PERIODICITY_ID column.
2404 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2405 h_sql := h_sql||' AND PERIODICITY_ID = :5';
2406 l_bind_vars_values(5) := x_periodicity;
2407 l_num_bind_vars := 5;
2408 END IF;
2409
2410 -- Fix bug#4653405 AW_INTEGRATION: in AW we need update the rows no matter if the data
2411 -- columns are already null, since we have to update the projection flag to N
2412 IF NOT x_aw_flag THEN
2413 h_sql := h_sql||' AND ('||h_lst_where||')';
2414 END IF;
2415 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2416 END IF;
2417 END IF;
2418 END IF;
2419
2420 -- Now delete the projection for data columns with no projection method
2421 h_lst_set := NULL;
2422 h_lst_where := NULL;
2423
2424 FOR h_i IN 1 .. x_num_data_columns LOOP
2425 IF x_data_proj_methods(h_i) = 0 THEN
2426 IF h_lst_set IS NULL THEN
2427 h_lst_set := x_data_columns(h_i)||' = NULL';
2428 h_lst_where := x_data_columns(h_i)||' IS NOT NULL';
2429 ELSE
2430 h_lst_set := h_lst_set||', '||x_data_columns(h_i)||' = NULL';
2431 h_lst_where := h_lst_where||' OR '||x_data_columns(h_i)||' IS NOT NULL';
2432 END IF;
2433 END IF;
2434 END LOOP;
2435
2436 IF h_lst_set IS NOT NULL THEN
2437 -- AW_INTEGRATION: Need to set projection column to 'N' and change_vector to x_change_vector_value
2438 -- Fix bug#4653405: AW_INTEGRATION: we cannot set projection = 'N' here, it remains in Y
2439 IF BSC_APPS.bsc_mv AND x_aw_flag THEN
2440 h_lst_set := h_lst_set||', change_vector = '||x_change_vector_value;
2441 END IF;
2442
2443 l_bind_vars_values.delete ;
2444 IF h_yearly_flag = 1 THEN
2445 h_sql := 'UPDATE '||x_table_name||
2446 ' SET '||h_lst_set||
2447 ' WHERE YEAR > :1'||' AND TYPE = :2';
2448 l_bind_vars_values(1) := (x_current_period);
2449 l_bind_vars_values(2) := 0 ;
2450 l_num_bind_vars := 2 ;
2451
2452 -- BSC-MV Note: Add condition on periodicity_id
2453 -- AW_INTEGRATION: If the base table is for AW then there is not periodicity_id
2454 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2455 h_sql := h_sql||' AND PERIODICITY_ID = :3';
2456 l_bind_vars_values(3) := x_periodicity;
2457 l_num_bind_vars := 3;
2458 END IF;
2459 ELSE
2460 h_sql := 'UPDATE '||x_table_name||
2461 ' SET '||h_lst_set||
2462 ' WHERE YEAR = :1'||' AND PERIOD > :2'||' AND TYPE = :3';
2463 l_bind_vars_values(1) := (x_current_fy);
2464 l_bind_vars_values(2) := (x_current_period) ;
2465 l_bind_vars_values(3) := 0 ;
2466 l_num_bind_vars := 3 ;
2467
2468 -- BSC-MV Note: Add condition on periodicity_id
2469 -- AW_INTEGRATION: If the base table is for AW then there is not periodicity_id
2470 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2471 h_sql := h_sql||' AND PERIODICITY_ID = :4';
2472 l_bind_vars_values(4) := x_periodicity;
2473 l_num_bind_vars := 4;
2474 END IF;
2475 END IF;
2476 h_sql := h_sql||' AND ('||h_lst_where||')';
2477 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2478 END IF;
2479
2480 RETURN TRUE;
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 BSC_MESSAGE.Add(
2485 X_Message => SQLERRM,
2486 X_Source => 'BSC_UPDATE_CALC.Delete_Projection_Base_Table');
2487 RETURN FALSE;
2488
2489 END Delete_Projection_Base_Table;
2490
2491
2492 /*===========================================================================+
2493 | FUNCTION Drop_Proj_Temps
2494 +============================================================================*/
2495 FUNCTION Drop_Proj_Temps RETURN BOOLEAN IS
2496
2497 h_sql VARCHAR2(32700);
2498 h_table_name VARCHAR2(30);
2499
2500 e_unexpected_error EXCEPTION;
2501
2502 BEGIN
2503 -- Now we use generic temporary tables. We do not drop these tables.
2504 RETURN TRUE;
2505
2506 EXCEPTION
2507 WHEN e_unexpected_error THEN
2508 BSC_MESSAGE.Add(
2509 X_Message => SQLERRM,
2510 X_Source => 'BSC_UPDATE_CALC.Drop_Proj_Temps');
2511 RETURN FALSE;
2512
2513 WHEN OTHERS THEN
2514 BSC_MESSAGE.Add(
2515 X_Message => SQLERRM,
2516 X_Source => 'BSC_UPDATE_CALC.Drop_Proj_Temps');
2517 RETURN FALSE;
2518
2519 END Drop_Proj_Temps;
2520
2521
2522 /*===========================================================================+
2523 | FUNCTION Get_Zero_Code_Calc_Method
2524 +============================================================================*/
2525 FUNCTION Get_Zero_Code_Calc_Method(
2526 x_table_name IN VARCHAR2
2527 ) RETURN NUMBER IS
2528
2529 TYPE t_cursor IS REF CURSOR;
2530
2531 /* c_calculation_type t_cursor;
2532 c_calculation_type_sql VARCHAR2(2000) := 'SELECT calculation_type'||
2533 ' FROM bsc_db_calculations'||
2534 ' WHERE table_name = :1 AND'||
2535 ' (calculation_type = :2 OR calculation_type = :3)'; */
2536 cursor c_calculation_type ( pTableName varchar2, pCalcType number, pCalcType2 number ) is
2537 SELECT calculation_type
2538 FROM bsc_db_calculations
2539 WHERE table_name = pTableName
2540 AND (calculation_type = pCalcType OR calculation_type = pCalcType2) ;
2541
2542 h_calculation_type NUMBER;
2543
2544 BEGIN
2545 -- OPEN c_calculation_type FOR c_calculation_type_sql USING x_table_name, 3, 4;
2546 OPEN c_calculation_type (x_table_name, 3, 4);
2547 FETCH c_calculation_type INTO h_calculation_type;
2548 IF c_calculation_type%NOTFOUND THEN
2549 h_calculation_type := 0;
2550 END IF;
2551 CLOSE c_calculation_type;
2552
2553 IF h_calculation_type IS NULL THEN
2554 h_calculation_type := 0;
2555 END IF;
2556
2557 RETURN h_calculation_type;
2558
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 BSC_MESSAGE.Add(
2562 X_Message => SQLERRM,
2563 X_Source => 'BSC_UPDATE_CALC.Get_Zero_Code_Calc_Method');
2564 RETURN NULL;
2565
2566 END Get_Zero_Code_Calc_Method;
2567
2568
2569 /*===========================================================================+
2570 | FUNCTION Merge_Data_From_Tables
2571 +============================================================================*/
2572 FUNCTION Merge_Data_From_Tables(
2573 x_table_name IN VARCHAR2,
2574 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2575 x_num_key_columns IN NUMBER
2576 ) RETURN BOOLEAN IS
2577
2578 TYPE t_cursor IS REF CURSOR;
2579
2580 /* c_source_tables t_cursor;
2581 c_source_tables_sql VARCHAR2(2000) := 'SELECT DISTINCT parameter1'||
2582 ' FROM bsc_db_calculations'||
2583 ' WHERE table_name = :1 AND'||
2584 ' calculation_type = :2'; */
2585 cursor c_source_tables(pTableName varchar2, pCalcType number) is
2586 SELECT DISTINCT parameter1
2587 FROM bsc_db_calculations
2588 WHERE table_name = pTableName
2589 AND calculation_type = pCalcType ;
2590
2591 h_source_table VARCHAR2(30);
2592
2593 /* c_data_columns t_cursor;
2594 c_data_columns_sql VARCHAR2(2000) := 'SELECT parameter2'||
2595 ' FROM bsc_db_calculations'||
2596 ' WHERE table_name = :1 AND'||
2597 ' calculation_type = :2 AND'||
2598 ' parameter1 = :3'; */
2599 cursor c_data_columns(pTableName varchar2,pCalcType number, pParam1 varchar2) is
2600 SELECT parameter2
2601 FROM bsc_db_calculations
2602 WHERE table_name = pTableName
2603 AND calculation_type = pCalcType
2604 AND parameter1 = pParam1 ;
2605
2606 h_data_column VARCHAR2(30);
2607
2608 h_sql VARCHAR2(32700);
2609 h_lst_data_columns VARCHAR2(32700);
2610 h_lst_key_columns VARCHAR2(32700);
2611
2612 BEGIN
2613 h_lst_data_columns := NULL;
2614 h_lst_key_columns := NULL;
2615
2616 -- Get the list of key columns
2617 h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2618 IF h_lst_key_columns IS NOT NULL THEN
2619 h_lst_key_columns := h_lst_key_columns||', ';
2620 END IF;
2621
2622 -- Loop within the source tables
2623 -- OPEN c_source_tables FOR c_source_tables_sql USING x_table_name, 5;
2624 OPEN c_source_tables (x_table_name, 5);
2625 FETCH c_source_tables INTO h_source_table;
2626 WHILE c_source_tables%FOUND LOOP
2627 -- Get list of data columns
2628 h_lst_data_columns := NULL;
2629 OPEN c_data_columns (x_table_name, 5, h_source_table);
2630 FETCH c_data_columns INTO h_data_column;
2631 WHILE c_data_columns%FOUND LOOP
2632 IF h_lst_data_columns IS NOT NULL THEN
2633 h_lst_data_columns := h_lst_data_columns||',';
2634 END IF;
2635 h_lst_data_columns := h_lst_data_columns||h_data_column;
2636 FETCH c_data_columns INTO h_data_column;
2637 END LOOP;
2638 CLOSE c_data_columns;
2639
2640 -- Update rows existing in the target table
2641 h_sql := 'UPDATE '||x_table_name||' T'||
2642 ' SET ('||h_lst_data_columns||') = ('||
2643 ' SELECT '||h_lst_data_columns||
2644 ' FROM '||h_source_table||' S'||
2645 ' WHERE ';
2646 IF x_num_key_columns > 0 THEN
2647 h_sql := h_sql||
2648 BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T', x_key_columns,
2649 'S', x_key_columns,
2650 x_num_key_columns, 'AND')||
2651 ' AND ';
2652 END IF;
2653 h_sql := h_sql||
2654 ' T.YEAR = S.YEAR AND T.TYPE = S.TYPE AND T.PERIOD = S.PERIOD'||
2655 ' )'||
2656 ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) IN ('||
2657 ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
2658 ' FROM '||h_source_table||
2659 ' )';
2660 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2661 --Fix bug#4116490 need commit
2662 commit;
2663
2664 -- Insert new rows
2665 h_sql := 'INSERT /*+ append ';
2666 if BSC_UPDATE_UTIL.is_parallel then
2667 h_sql:=h_sql||'parallel ('||x_table_name||') ';
2668 end if;
2669 h_sql:=h_sql||' */';
2670 h_sql:=h_sql||'INTO '||x_table_name||
2671 ' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD,'||h_lst_data_columns||')'||
2672 ' SELECT ';
2673 if BSC_UPDATE_UTIL.is_parallel then
2674 h_sql:=h_sql||'/*+ parallel ('||h_source_table||')*/ ';
2675 end if;
2676 h_sql:=h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD,'||h_lst_data_columns||
2677 ' FROM '||h_source_table||
2678 ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) NOT IN ('||
2679 ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
2680 ' FROM '||x_table_name||
2681 ' )';
2682 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2683 commit;
2684
2685 FETCH c_source_tables INTO h_source_table;
2686 END LOOP;
2687 CLOSE c_source_tables;
2688
2689
2690 RETURN TRUE;
2691
2692 EXCEPTION
2693 WHEN OTHERS THEN
2694 BSC_MESSAGE.Add(
2695 X_Message => SQLERRM,
2696 X_Source => 'BSC_UPDATE_CALC.Merge_Data_From_Tables');
2697 RETURN FALSE;
2698
2699 END Merge_Data_From_Tables;
2700
2701
2702 /*===========================================================================+
2703 | FUNCTION Merge_Projection |
2704 +============================================================================*/
2705 FUNCTION Merge_Projection(
2706 x_table_name VARCHAR2,
2707 x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2708 x_num_key_columns NUMBER,
2709 x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
2710 x_num_data_columns NUMBER,
2711 x_is_base BOOLEAN,
2712 x_aw_flag BOOLEAN
2713 ) RETURN BOOLEAN IS
2714
2715 h_sql VARCHAR2(32700);
2716 h_i NUMBER;
2717
2718 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
2719 h_lst_keys_temp VARCHAR2(32000);
2720 h_cond_join VARCHAR2(32000);
2721 h_lst_keys_a VARCHAR2(32000);
2722 h_lst_keys_b VARCHAR2(32000);
2723 h_lst_data_temp VARCHAR2(32000);
2724 h_lst_set_data VARCHAR2(32000);
2725 h_lst_data_a VARCHAR2(32000);
2726 h_lst_data_b VARCHAR2(32000);
2727
2728 BEGIN
2729 h_lst_keys_temp := NULL;
2730 h_cond_join := NULL;
2731 h_lst_keys_a := NULL;
2732 h_lst_keys_b := NULL;
2733 h_lst_data_temp := NULL;
2734 h_lst_set_data := NULL;
2735 h_lst_data_a := NULL;
2736 h_lst_data_b := NULL;
2737
2738 FOR h_i IN 1..x_num_key_columns LOOP
2739 h_key_columns_temp(h_i) := 'KEY'||h_i;
2740 END LOOP;
2741
2742 h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
2743 h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
2744 'B', h_key_columns_temp,
2745 x_num_key_columns, 'AND');
2746 h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
2747 h_lst_keys_b := BSC_UPDATE_UTIL.Make_Lst_Table_Column('B', h_key_columns_temp, x_num_key_columns);
2748
2749 IF h_lst_keys_temp IS NOT NULL THEN
2750 h_lst_keys_temp := h_lst_keys_temp||', ';
2751 h_cond_join := h_cond_join||' AND ';
2752 h_lst_keys_a := h_lst_keys_a||', ';
2753 h_lst_keys_b := h_lst_keys_b||', ';
2754 END IF;
2755
2756 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2757 h_lst_keys_temp := h_lst_keys_temp||'PERIODICITY_ID, ';
2758 h_cond_join := h_cond_join||'A.PERIODICITY_ID = B.PERIODICITY_ID AND ';
2759 h_lst_keys_a := h_lst_keys_a||'A.PERIODICITY_ID, ';
2760 h_lst_keys_b := h_lst_keys_b||'B.PERIODICITY_ID, ';
2761
2762 IF NOT x_is_base THEN
2763 h_lst_keys_temp := h_lst_keys_temp||'PERIOD_TYPE_ID, ';
2764 h_lst_keys_a := h_lst_keys_a||'A.PERIOD_TYPE_ID, ';
2765 h_lst_keys_b := h_lst_keys_b||'B.PERIOD_TYPE_ID, ';
2766 END IF;
2767 END IF;
2768
2769 h_lst_keys_temp := h_lst_keys_temp||'YEAR, TYPE, PERIOD';
2770 h_cond_join := h_cond_join||'A.YEAR = B.YEAR AND A.TYPE = B.TYPE AND A.PERIOD = B.PERIOD';
2771 h_lst_keys_a := h_lst_keys_a||'A.YEAR, A.TYPE, A.PERIOD';
2772 h_lst_keys_b := h_lst_keys_b||'B.YEAR, B.TYPE, B.PERIOD';
2773
2774 h_lst_data_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
2775
2776 FOR h_i IN 1..x_num_data_columns LOOP
2777 IF h_i = 1 THEN
2778 h_lst_set_data := 'A.'||x_data_columns(h_i)||' = B.DATA'||h_i;
2779 h_lst_data_a := 'A.'||x_data_columns(h_i);
2780 h_lst_data_b := 'B.DATA'||h_i;
2781 ELSE
2782 h_lst_set_data := h_lst_set_data||', A.'||x_data_columns(h_i)||' = B.DATA'||h_i;
2783 h_lst_data_a := h_lst_data_a||', A.'||x_data_columns(h_i);
2784 h_lst_data_b := h_lst_data_b||', B.DATA'||h_i;
2785 END IF;
2786 END LOOP;
2787
2788 IF x_aw_flag THEN
2789 h_lst_data_temp := h_lst_data_temp||', PROJECTION, CHANGE_VECTOR';
2790 h_lst_set_data := h_lst_set_data||', A.PROJECTION = B.PROJECTION, A.CHANGE_VECTOR = B.CHANGE_VECTOR';
2791 h_lst_data_a := h_lst_data_a||', A.PROJECTION, A.CHANGE_VECTOR';
2792 h_lst_data_b := h_lst_data_b||', B.PROJECTION, B.CHANGE_VECTOR';
2793 END IF;
2794
2795 h_sql := 'MERGE INTO '||x_table_name||' A'||
2796 ' USING (SELECT '||h_lst_keys_temp||', '||h_lst_data_temp||' FROM BSC_TMP_PROJ_CALC) B'||
2797 ' ON ('||h_cond_join||')'||
2798 ' WHEN MATCHED THEN UPDATE SET '||h_lst_set_data||
2799 ' WHEN NOT MATCHED THEN'||
2800 ' INSERT ('||h_lst_keys_a||', '||h_lst_data_a||')'||
2801 ' VALUES ('||h_lst_keys_b||', '||h_lst_data_b||')';
2802 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2803 commit;
2804
2805 RETURN TRUE;
2806
2807 EXCEPTION
2808 WHEN OTHERS THEN
2809 ROLLBACK;
2810
2811 BSC_MESSAGE.Add(
2812 X_Message => SQLERRM,
2813 X_Source => 'BSC_UPDATE_CALC.Merge_Projection');
2814 RETURN FALSE;
2815 END Merge_Projection;
2816
2817
2818 /*===========================================================================+
2819 | FUNCTION Refresh_EDW_Views
2820 +============================================================================*/
2821 FUNCTION Refresh_EDW_Views(
2822 x_table_name IN VARCHAR2,
2823 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2 ,
2824 x_num_key_columns IN NUMBER ,
2825 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2826 x_num_data_columns IN NUMBER,
2827 x_current_fy IN NUMBER,
2828 x_periodicity IN NUMBER,
2829 x_current_period OUT NOCOPY NUMBER
2830 ) RETURN BOOLEAN IS
2831
2832 e_unexpected_error EXCEPTION;
2833
2834 h_edw_mv_name VARCHAR2(50);
2835 h_edw_uv_name VARCHAR2(50);
2836
2837 h_lst_key_columns VARCHAR2(32700);
2838 h_lst_data_columns VARCHAR2(32700);
2839
2840 TYPE t_cursor IS REF CURSOR;
2841 h_cursor t_cursor;
2842
2843 h_sql VARCHAR2(32700);
2844
2845 h_yearly_flag NUMBER;
2846
2847 h_num_of_years NUMBER;
2848 h_previous_years NUMBER;
2849 h_start_year NUMBER;
2850 h_end_year NUMBER;
2851
2852 BEGIN
2853
2854 h_yearly_flag := 0;
2855
2856 -- Refresh materialized view.
2857 -- Get the number of years and previous years of the table
2858 IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_table_name, h_num_of_years, h_previous_years) THEN
2859 RAISE e_unexpected_error;
2860 END IF;
2861
2862 h_start_year := x_current_fy - h_previous_years;
2863 h_end_year := h_start_year + h_num_of_years - 1;
2864
2865 BSC_INTEGRATION_MV_GEN.Refresh_MVs(x_table_name, TO_CHAR(h_start_year)||'-'||TO_CHAR(h_end_year));
2866 IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Refresh_MVs') THEN
2867 RAISE e_unexpected_error;
2868 END IF;
2869
2870 -- Delete from x_table all rows existing in the materialized view
2871 h_edw_mv_name := BSC_UPDATE_UTIL.Get_EDW_Materialized_View_Name(x_table_name);
2872
2873 h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
2874 IF h_lst_key_columns IS NOT NULL THEN
2875 h_lst_key_columns := h_lst_key_columns||', ';
2876 END IF;
2877
2878 h_sql := 'DELETE FROM '||x_table_name||
2879 ' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) IN ('||
2880 ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD '||
2881 ' FROM '||h_edw_mv_name||')';
2882 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2883
2884 -- Create union view
2885 h_edw_uv_name := BSC_UPDATE_UTIL.Get_EDW_Union_View_Name(x_table_name);
2886 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
2887 h_sql := 'CREATE OR REPLACE VIEW '||h_edw_uv_name||' AS ('||
2888 ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
2889 ' FROM '||x_table_name||
2890 ' UNION '||
2891 ' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
2892 ' FROM '||h_edw_mv_name||')';
2893
2894 --AD_DDL has some issues creating the view. It could be because the materialized views
2895 --were not created using AD_DDL?
2896 --So, we create the view directly. This is not so bad because the views are created on APPS schema
2897 --BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_VIEW, h_edw_uv_name);
2898 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2899
2900 -- Get current period for the table.
2901 -- This is the maximun period reported in the materialized view
2902 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2903 IF h_yearly_flag = 1 THEN
2904 -- Annual periodicity --> current period is the current fiscal year
2905 x_current_period := x_current_fy;
2906 ELSE
2907 -- Get the maximum period reported in the materialized view
2908 h_sql := 'SELECT NVL(MAX(period),1) '||
2909 ' FROM '||h_edw_mv_name||
2910 ' WHERE year = :1'||
2911 ' AND type = :2';
2912
2913 OPEN h_cursor FOR h_sql USING x_current_fy, 0;
2914 FETCH h_cursor INTO x_current_period;
2915 IF h_cursor%NOTFOUND THEN
2916 x_current_period := 1;
2917 END IF;
2918 CLOSE h_cursor;
2919
2920 END IF;
2921
2922 RETURN TRUE;
2923
2924 EXCEPTION
2925 WHEN e_unexpected_error THEN
2926 BSC_MESSAGE.Add(
2927 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_VIEWS_FAILED'),
2928 X_Source => 'BSC_UPDATE_CALC.Refresh_EDW_Views');
2929 RETURN FALSE;
2930
2931 WHEN OTHERS THEN
2932 BSC_MESSAGE.Add(
2933 X_Message => SQLERRM,
2934 X_Source => 'BSC_UPDATE_CALC.Refresh_EDW_Views');
2935 RETURN FALSE;
2936
2937 END Refresh_EDW_Views;
2938
2939
2940 /*===========================================================================+
2941 | FUNCTION Rollup_Projection
2942 +============================================================================*/
2943 FUNCTION Rollup_Projection(
2944 x_periodicity IN NUMBER,
2945 x_period IN NUMBER,
2946 x_base_periodicity IN NUMBER,
2947 x_base_period IN NUMBER,
2948 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2949 x_num_key_columns IN NUMBER,
2950 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2951 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2952 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2953 x_num_data_columns IN NUMBER,
2954 x_current_fy IN NUMBER,
2955 x_is_base IN BOOLEAN
2956 ) RETURN BOOLEAN IS
2957
2958 e_unexpected_error EXCEPTION;
2959
2960 h_sql VARCHAR2(32700);
2961 h_i NUMBER;
2962 h_j NUMBER;
2963
2964 h_period_col_name VARCHAR2(30);
2965 h_origin_period_col_name VARCHAR2(30);
2966
2967 h_lst_key_columns VARCHAR2(32700);
2968
2969 h_lst_data_columns VARCHAR2(32700);
2970 h_lst_data_formulas VARCHAR2(32700);
2971
2972 h_lst_from VARCHAR2(32700);
2973 h_lst_on VARCHAR2(32700);
2974
2975 h_lst_select_disag VARCHAR2(32700);
2976 h_lst_select_per VARCHAR2(32700);
2977
2978 h_num_tot_data_columns NUMBER;
2979 h_lst_tot_data_columns VARCHAR2(32700);
2980 h_lst_tot_data_formulas VARCHAR2(32700);
2981 h_target_table_tot VARCHAR2(30);
2982 h_lst_from_tot VARCHAR2(32700);
2983 h_lst_on_tot VARCHAR2(32700);
2984
2985 h_num_bal_data_columns NUMBER;
2986 h_lst_bal_data_columns VARCHAR2(32700);
2987 h_lst_bal_data_formulas VARCHAR2(32700);
2988 h_target_table_bal VARCHAR2(30);
2989 h_lst_from_bal VARCHAR2(32700);
2990 h_lst_on_bal VARCHAR2(32700);
2991
2992 h_calendar_id NUMBER;
2993
2994 -- Bind var fix for Posco
2995 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2996 l_num_bind_vars NUMBER;
2997
2998 l_parallel_hint varchar2(20000);
2999 l_parallel_hint1 varchar2(20000);
3000 l_parallel_hint2 varchar2(20000);
3001
3002 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
3003 h_lst_key_columns_temp VARCHAR2(32000);
3004
3005 h_lst_tot_data_columns_temp VARCHAR2(32000);
3006 h_lst_bal_data_columns_temp VARCHAR2(32000);
3007 h_lst_tot_data_columns_temp_t VARCHAR2(32000);
3008 h_lst_bal_data_columns_temp_b VARCHAR2(32000);
3009
3010 h_lst_data_columns_temp VARCHAR2(32000);
3011 h_cond_data_values VARCHAR2(32000);
3012
3013 h_lst_data_p VARCHAR2(32000);
3014
3015 h_data_formula_temp VARCHAR2(32000);
3016 h_period_type_id NUMBER;
3017
3018 BEGIN
3019
3020 h_sql := NULL;
3021 h_lst_key_columns := NULL;
3022 h_lst_data_columns := NULL;
3023 h_lst_data_formulas := NULL;
3024 h_lst_from := NULL;
3025 h_lst_on := NULL;
3026 h_lst_select_disag := NULL;
3027 h_lst_select_per := NULL;
3028 h_num_tot_data_columns := 0;
3029 h_lst_tot_data_columns := NULL;
3030 h_lst_tot_data_formulas := NULL;
3031 h_target_table_tot := NULL;
3032 h_lst_from_tot := NULL;
3033 h_lst_on_tot := NULL;
3034 h_num_bal_data_columns := 0;
3035 h_lst_bal_data_columns := NULL;
3036 h_lst_bal_data_formulas := NULL;
3037 h_target_table_bal := NULL;
3038 h_lst_from_bal := NULL;
3039 h_lst_on_bal := NULL;
3040 l_num_bind_vars := 0;
3041 h_lst_key_columns_temp := NULL;
3042 h_lst_tot_data_columns_temp := NULL;
3043 h_lst_bal_data_columns_temp := NULL;
3044 h_lst_tot_data_columns_temp_t := NULL;
3045 h_lst_bal_data_columns_temp_b := NULL;
3046 h_lst_data_columns_temp := NULL;
3047 h_cond_data_values := NULL;
3048 h_lst_data_p := NULL;
3049
3050 --ENH_PROJECTION_4235711: We are going to rollup the projection in BSC_TMP_PROJ_CALC.
3051 -- We need to take in account balance columns!
3052 -- We need to take in account formulas
3053 -- Also we know that the target periodicity is not yearly.
3054 -- This code is only used in MV Architecture.
3055
3056 -- get period type id of the target periodicity
3057 select period_type_id
3058 into h_period_type_id
3059 from bsc_sys_periodicities
3060 where periodicity_id = x_periodicity;
3061
3062 -- New array used with generic temporary tables
3063 FOR h_i IN 1..x_num_key_columns LOOP
3064 h_key_columns_temp(h_i) := 'KEY'||h_i;
3065 END LOOP;
3066
3067 -- Some information about the periodicity
3068 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
3069
3070 -- Initialize some variables required for change of periodicity
3071 -- to handle balance and total data columns
3072
3073 -- Calculate the number of balance and total data columns
3074 -- By the way initialize arrays for total and balance data columns
3075 FOR h_i IN 1..x_num_data_columns LOOP
3076 h_data_formula_temp := x_data_formulas(h_i);
3077 FOR h_j IN 1..x_num_data_columns LOOP
3078 h_data_formula_temp := replace(h_data_formula_temp, '('||x_data_columns(h_j)||')', '(DATA'||h_j||')');
3079 END LOOP;
3080
3081 IF x_data_measure_types(h_i) = 1 THEN
3082 -- Total data column
3083 h_num_tot_data_columns := h_num_tot_data_columns + 1;
3084
3085 IF h_num_tot_data_columns = 1 THEN
3086 h_lst_tot_data_columns := 'DATA'||h_i;
3087 h_lst_tot_data_columns_temp := 'DATA'||h_i;
3088 h_lst_tot_data_columns_temp_t := 'T.DATA'||h_i;
3089 h_lst_tot_data_formulas := h_data_formula_temp;
3090 ELSE
3091 h_lst_tot_data_columns := h_lst_tot_data_columns||', DATA'||h_i;
3092 h_lst_tot_data_columns_temp := h_lst_tot_data_columns_temp||', DATA'||h_i;
3093 h_lst_tot_data_columns_temp_t := h_lst_tot_data_columns_temp_t||', T.DATA'||h_i;
3094 h_lst_tot_data_formulas := h_lst_tot_data_formulas||', '||h_data_formula_temp;
3095 END IF;
3096 ELSE
3097 -- Balance data column
3098 h_num_bal_data_columns := h_num_bal_data_columns + 1;
3099 IF h_num_bal_data_columns = 1 THEN
3100 h_lst_bal_data_columns := 'DATA'||h_i;
3101 h_lst_bal_data_columns_temp := 'DATA'||h_i;
3102 h_lst_bal_data_columns_temp_b := 'B.DATA'||h_i;
3103 h_lst_bal_data_formulas := h_data_formula_temp;
3104 ELSE
3105 h_lst_bal_data_columns := h_lst_bal_data_columns||', DATA'||h_i;
3106 h_lst_bal_data_columns_temp := h_lst_bal_data_columns_temp||', DATA'||h_i;
3107 h_lst_bal_data_columns_temp_b := h_lst_bal_data_columns_temp_b||', B.DATA'||h_i;
3108 h_lst_bal_data_formulas := h_lst_bal_data_formulas||', '||h_data_formula_temp;
3109 END IF;
3110 END IF;
3111 END LOOP;
3112
3113 -- Create a temporal table to make the change of periodicity
3114 h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
3115 h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_periodicity);
3116
3117 IF h_num_tot_data_columns > 0 THEN
3118 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3119
3120 h_sql := 'INSERT /*+ append ';
3121 if BSC_UPDATE_UTIL.is_parallel then
3122 h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
3123 end if;
3124 h_sql:=h_sql||' */';
3125 h_sql:=h_sql||'INTO bsc_tmp_per_change (year, src_per, trg_per)'||
3126 ' SELECT ';
3127 if BSC_UPDATE_UTIL.is_parallel then
3128 h_sql:=h_sql||'/*+ parallel (bsc_db_calendar)*/ ';
3129 end if;
3130 h_sql:=h_sql||
3131 'DISTINCT year, '||h_origin_period_col_name||' AS src_per, '||
3132 h_period_col_name||' AS trg_per'||
3133 ' FROM bsc_db_calendar'||
3134 ' WHERE calendar_id = :1 and year = :2';
3135 l_bind_vars_values.delete;
3136 l_bind_vars_values(1) := h_calendar_id;
3137 l_bind_vars_values(2) := x_current_fy;
3138 l_num_bind_vars := 2;
3139 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3140 commit;
3141 END IF;
3142
3143 IF h_num_bal_data_columns > 0 THEN
3144 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3145
3146 h_sql := 'INSERT /*+ append ';
3147 if BSC_UPDATE_UTIL.is_parallel then
3148 h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
3149 end if;
3150 h_sql:=h_sql||' */';
3151 h_sql:=h_sql||'INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
3152 ' SELECT ';
3153 if BSC_UPDATE_UTIL.is_parallel then
3154 h_sql:=h_sql||'/*+ parallel (bsc_db_calendar)*/ ';
3155 end if;
3156 h_sql:=h_sql||'year, MAX('||h_origin_period_col_name||') AS src_per, '||
3157 h_period_col_name||' AS trg_per'||
3158 ' FROM bsc_db_calendar'||
3159 ' WHERE calendar_id = :1 and year = :2'||
3160 ' GROUP BY year, '||h_period_col_name;
3161 l_bind_vars_values.delete;
3162 l_bind_vars_values(1) := h_calendar_id;
3163 l_bind_vars_values(2) := x_current_fy;
3164 l_num_bind_vars := 2;
3165 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3166 commit;
3167 END IF;
3168
3169 -- Create temporal tables to calculate total data columns and balance data columns separately
3170 -- and then merge them into the target summary table
3171 -- If all data columns are total or balance we dont need those temporal tables
3172 IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3173 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3174 h_target_table_tot := 'BSC_TMP_TOT_DATA';
3175
3176 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3177 h_target_table_bal := 'BSC_TMP_BAL_DATA';
3178 ELSE
3179 h_target_table_tot := 'BSC_TMP_PROJ_CALC';
3180 h_target_table_bal := 'BSC_TMP_PROJ_CALC';
3181 END IF;
3182
3183 -- create the string for FROM sub-statement
3184 h_lst_from := 'BSC_TMP_PROJ_CALC';
3185 l_parallel_hint:=l_parallel_hint||' parallel (BSC_TMP_PROJ_CALC)';
3186
3187 -- Initialize some lists that will be part of the query to generate the summary table
3188 h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3189 h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
3190
3191 IF h_lst_key_columns IS NOT NULL THEN
3192 h_lst_key_columns := h_lst_key_columns||', ';
3193 h_lst_key_columns_temp := h_lst_key_columns_temp||', ';
3194 END IF;
3195
3196 -- Create the string for the SELECT and FROM sub-statement
3197 FOR h_i IN 1..x_num_key_columns LOOP
3198 h_lst_select_disag := h_lst_select_disag||'BSC_TMP_PROJ_CALC.'||h_key_columns_temp(h_i)||', ';
3199 END LOOP;
3200
3201 -- Create the string for the SELECT and FROM sub-statement for the periodicity change.
3202 -- Note: We suppose that the change of periodicity is allowed
3203 -- (see bsc_sys_periodicites)
3204 IF h_num_tot_data_columns > 0 THEN
3205 h_lst_from_tot := h_lst_from||', BSC_TMP_PER_CHANGE';
3206 l_parallel_hint1:=l_parallel_hint||' parallel (BSC_TMP_PER_CHANGE)';
3207 h_lst_on_tot := 'BSC_TMP_PROJ_CALC.YEAR = BSC_TMP_PER_CHANGE.YEAR'||
3208 ' AND BSC_TMP_PROJ_CALC.PERIOD = BSC_TMP_PER_CHANGE.SRC_PER';
3209 h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.TYPE, BSC_TMP_PER_CHANGE.TRG_PER';
3210
3211 h_sql := 'INSERT /*+ append ';
3212 if BSC_UPDATE_UTIL.is_parallel then
3213 h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
3214 end if;
3215 h_sql:=h_sql||' */';
3216 h_sql:=h_sql||'INTO '||h_target_table_tot;
3217 h_sql:=h_sql||' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, PERIODICITY_ID, ';
3218 IF NOT x_is_base THEN
3219 h_sql:=h_sql||'PERIOD_TYPE_ID, ';
3220 END IF;
3221 h_sql:=h_sql||h_lst_tot_data_columns_temp||')';
3222 h_sql:=h_sql||' SELECT ';
3223 if BSC_UPDATE_UTIL.is_parallel then
3224 h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
3225 end if;
3226 h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', :1, ';
3227 IF NOT x_is_base THEN
3228 h_sql:=h_sql||':2, ';
3229 END IF;
3230 h_sql:=h_sql||h_lst_tot_data_formulas||
3231 ' FROM '||h_lst_from_tot||
3232 ' WHERE '||h_lst_on_tot||
3233 ' AND BSC_TMP_PROJ_CALC.PERIODICITY_ID = :3'||
3234 ' AND BSC_TMP_PER_CHANGE.TRG_PER > :4'||
3235 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
3236 l_bind_vars_values.delete;
3237 IF NOT x_is_base THEN
3238 l_bind_vars_values(1) := x_periodicity;
3239 l_bind_vars_values(2) := h_period_type_id;
3240 l_bind_vars_values(3) := x_base_periodicity;
3241 l_bind_vars_values(4) := x_period;
3242 l_num_bind_vars := 4;
3243 ELSE
3244 l_bind_vars_values(1) := x_periodicity;
3245 l_bind_vars_values(2) := x_base_periodicity;
3246 l_bind_vars_values(3) := x_period;
3247 l_num_bind_vars := 3;
3248 END IF;
3249 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3250 commit;
3251 END IF;
3252
3253 IF h_num_bal_data_columns > 0 THEN
3254 h_lst_from_bal := h_lst_from||', BSC_TMP_PER_CHANGE_BAL';
3255 l_parallel_hint2:=l_parallel_hint||' parallel (BSC_TMP_PER_CHANGE_BAL)';
3256 h_lst_on_bal := 'BSC_TMP_PROJ_CALC.YEAR = BSC_TMP_PER_CHANGE_BAL.YEAR'||
3257 ' AND BSC_TMP_PROJ_CALC.PERIOD = BSC_TMP_PER_CHANGE_BAL.SRC_PER';
3258 h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.TYPE, BSC_TMP_PER_CHANGE_BAL.TRG_PER';
3259
3260 h_sql := 'INSERT /*+ append ';
3261 if BSC_UPDATE_UTIL.is_parallel then
3262 h_sql:=h_sql||'parallel ('||h_target_table_bal||') ';
3263 end if;
3264 h_sql:=h_sql||' */';
3265 h_sql:=h_sql||'INTO '||h_target_table_bal;
3266 h_sql:=h_sql||' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, PERIODICITY_ID, ';
3267 IF NOT x_is_base THEN
3268 h_sql:=h_sql||'PERIOD_TYPE_ID, ';
3269 END IF;
3270 h_sql:=h_sql||h_lst_bal_data_columns_temp||')';
3271 h_sql := h_sql||' SELECT ';
3272 if BSC_UPDATE_UTIL.is_parallel then
3273 h_sql:=h_sql||'/*+'||l_parallel_hint2||'*/ ';
3274 end if;
3275 h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', :1, ';
3276 IF NOT x_is_base THEN
3277 h_sql:=h_sql||':2, ';
3278 END IF;
3279 h_sql:=h_sql||h_lst_bal_data_formulas||
3280 ' FROM '||h_lst_from_bal||
3281 ' WHERE '||h_lst_on_bal||
3282 ' AND BSC_TMP_PROJ_CALC.PERIODICITY_ID = :3'||
3283 ' AND BSC_TMP_PER_CHANGE_BAL.TRG_PER > :4'||
3284 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
3285 l_bind_vars_values.delete;
3286 IF NOT x_is_base THEN
3287 l_bind_vars_values(1) := x_periodicity;
3288 l_bind_vars_values(2) := h_period_type_id;
3289 l_bind_vars_values(3) := x_base_periodicity;
3290 l_bind_vars_values(4) := x_period;
3291 l_num_bind_vars := 4;
3292 ELSE
3293 l_bind_vars_values(1) := x_periodicity;
3294 l_bind_vars_values(2) := x_base_periodicity;
3295 l_bind_vars_values(3) := x_period;
3296 l_num_bind_vars := 3;
3297 END IF;
3298 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
3299 commit;
3300 END IF;
3301
3302 IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
3303 -- We need to merge BSC_TMP_TOT_DATA and BSC_TMP_BAL_DATA into BSC_TMP_PROJ_CALC
3304 -- Fix Bug#3131339 Do left join
3305 h_lst_on := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('T', h_key_columns_temp, 'B', h_key_columns_temp,
3306 x_num_key_columns, 'AND');
3307 IF h_lst_on IS NOT NULL THEN
3308 h_lst_on := h_lst_on||' AND ';
3309 END IF;
3310
3311 h_lst_select_disag := BSC_UPDATE_UTIL.Make_Lst_Table_Column('T', h_key_columns_temp, x_num_key_columns);
3312 IF h_lst_select_disag IS NOT NULL THEN
3313 h_lst_select_disag := h_lst_select_disag||', ';
3314 END IF;
3315
3316 h_sql := 'INSERT /*+ append ';
3317 if BSC_UPDATE_UTIL.is_parallel then
3318 h_sql:=h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
3319 end if;
3320 h_sql:=h_sql||' */';
3321 h_sql:=h_sql||'INTO BSC_TMP_PROJ_CALC'||
3322 ' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, PERIODICITY_ID, ';
3323 IF NOT x_is_base THEN
3324 h_sql:=h_sql||'PERIOD_TYPE_ID, ';
3325 END IF;
3326 h_sql:=h_sql||h_lst_tot_data_columns_temp||', '||h_lst_bal_data_columns_temp||')'||
3327 ' SELECT ';
3328 --Fix bug#3875046: Use hash hint
3329 h_sql:=h_sql||'/*+use_hash(T) use_hash(B)*/ ';
3330 if BSC_UPDATE_UTIL.is_parallel then
3331 h_sql:=h_sql||'/*+ parallel (T) parallel (B)*/ ';
3332 end if;
3333 h_sql:=h_sql||h_lst_select_disag||'T.YEAR, T.TYPE, T.PERIOD, T.PERIODICITY_ID, ';
3334 IF NOT x_is_base THEN
3335 h_sql:=h_sql||'T.PERIOD_TYPE_ID, ';
3336 END IF;
3337 h_sql:=h_sql||h_lst_tot_data_columns_temp_t||', '||h_lst_bal_data_columns_temp_b||
3338 ' FROM BSC_TMP_TOT_DATA T, BSC_TMP_BAL_DATA B'||
3339 ' WHERE '||h_lst_on;
3340 h_sql := h_sql||'T.YEAR = B.YEAR (+) AND T.TYPE = B.TYPE (+)'||
3341 ' AND T.PERIOD = B.PERIOD (+) AND T.PERIODICITY_ID = B.PERIODICITY_ID (+)';
3342 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3343 commit;
3344 END IF;
3345 commit;
3346
3347 -- Fix bug#4463132: Truncate temporary table after use
3348 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
3349 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
3350 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
3351 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
3352 commit;
3353
3354 RETURN TRUE;
3355
3356 EXCEPTION
3357 WHEN e_unexpected_error THEN
3358 ROLLBACK;
3359 BSC_MESSAGE.Add(
3360 X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
3361 X_Source => 'BSC_UPDATE_CALC.Rollup_Projection');
3362 RETURN FALSE;
3363
3364 WHEN OTHERS THEN
3365 ROLLBACK;
3366
3367 BSC_MESSAGE.Add(
3368 X_Message => SQLERRM,
3369 X_Source => 'BSC_UPDATE_CALC.Rollup_Projection');
3370 RETURN FALSE;
3371
3372 END Rollup_Projection;
3373
3374
3375 /*===========================================================================+
3376 | FUNCTION Table_Has_Profit_Calc
3377 +============================================================================*/
3378 FUNCTION Table_Has_Profit_Calc(
3379 x_table_name IN VARCHAR2
3380 ) RETURN BOOLEAN IS
3381
3382 cursor c_profit_calc (pTableName varchar2,pCalcType number) is
3383 SELECT calculation_type
3384 FROM bsc_db_calculations
3385 WHERE table_name = pTableName
3386 AND calculation_type = pCalcType;
3387
3388 h_profit_calc bsc_db_calculations.calculation_type%TYPE;
3389 h_ret BOOLEAN;
3390
3391 BEGIN
3392 -- OPEN c_profit_calc FOR c_profit_calc_sql USING x_table_name, 1;
3393 OPEN c_profit_calc (x_table_name, 1);
3394 FETCH c_profit_calc INTO h_profit_calc;
3395 IF c_profit_calc%NOTFOUND THEN
3396 h_ret := FALSE;
3397 ELSE
3398 h_ret := TRUE;
3399 END IF;
3400 CLOSE c_profit_calc;
3401
3402 RETURN h_ret;
3403
3404 EXCEPTION
3405 WHEN OTHERS THEN
3406 BSC_MESSAGE.Add(
3407 X_Message => SQLERRM,
3408 X_Source => 'BSC_UPDATE_CALC.Table_Has_Profit_Calc');
3409 RETURN NULL;
3410
3411 END Table_Has_Profit_Calc;
3412
3413
3414 /*===========================================================================+
3415 | FUNCTION Table_Has_Proj_Calc
3416 +============================================================================*/
3417 FUNCTION Table_Has_Proj_Calc(
3418 x_table_name IN VARCHAR2
3419 ) RETURN BOOLEAN IS
3420
3421 h_sql VARCHAR2(4000);
3422 h_column_type_a VARCHAR2(1);
3423 h_table_name VARCHAR2(30);
3424 h_ret BOOLEAN;
3425
3426 -- SUPPORT_BSC_BIS_MEASURES: Only BSC measures exists in bsc_db_measure_cols_vl and
3427 -- by design we assumed that BIS measures do not have projection.
3428 -- I have added the condition on source in bsc_db_tables_cols
3429 cursor h_cursor (pTableName varchar2,pProjFlag number,pColCnt number,
3430 pColType varchar2,pProjId number, pProjId2 number, pBSCSource varchar2,
3431 pBSCSource1 varchar2) is
3432 SELECT table_name
3433 FROM bsc_db_tables t
3434 WHERE table_name = pTableName AND project_flag = pProjFlag AND
3435 pColCnt <> (SELECT COUNT(tc.column_name)
3436 FROM bsc_db_tables_cols tc, bsc_db_measure_cols_vl m
3437 WHERE tc.table_name = t.table_name AND
3438 tc.column_type = pColType AND
3439 NVL(tc.source, pBSCSource) = pBSCSource1 AND
3440 tc.column_name = m.measure_col (+) AND
3441 NVL(m.projection_id, pProjId) <> pProjId2);
3442
3443 BEGIN
3444 h_column_type_a := 'A';
3445 h_table_name := NULL;
3446
3447 -- There are two conditions:
3448 -- 1. The table has projection flag = 1 and
3449 -- 2. At least one data column has projection method <> 0
3450
3451 OPEN h_cursor (x_table_name, 1, 0, h_column_type_a, 0, 0, 'BSC', 'BSC') ;
3452 FETCH h_cursor INTO h_table_name;
3453 IF h_cursor%NOTFOUND THEN
3454 h_ret := FALSE;
3455 ELSE
3456 h_ret := TRUE;
3457 END IF;
3458 CLOSE h_cursor;
3459
3460 RETURN h_ret;
3461
3462 EXCEPTION
3463 WHEN OTHERS THEN
3464 BSC_MESSAGE.Add(
3465 X_Message => SQLERRM,
3466 X_Source => 'BSC_UPDATE_CALC.Table_Has_Proj_Calc');
3467 RETURN NULL;
3468
3469 END Table_Has_Proj_Calc;
3470
3471
3472 END BSC_UPDATE_CALC;
[Home] [Help]