1 Package Body BSC_MO_LOADER_CONFIG_PKG AS
2 /* $Header: BSCMOCFB.pls 120.9 2006/02/24 13:33:59 arsantha noship $ */
3
4 g_newline VARCHAR2(10) := '
5 ';
6
7 --****************************************************************************
8 -- isBasicTable
9 --
10 -- DESCRIPTION:
11 -- Retunr TRUE if the table is a base table. It means that the table
12 -- is originated from an input table.
13 --
14 -- PARAMETERS:
15 -- NomTabla: Table name
16 --****************************************************************************
17 Function isBasicTable(p_table_name IN VARCHAR2) RETURN Boolean IS
18 l_temp NUMBER;
19 l_temp2 NUMBER;
20
21 l_origin_table DBMS_SQL.VARCHAR2_TABLE;
22
23 l_origin_name VARCHAR2(100);
24 CURSOR cTableType IS
25 select count(1)
26 from bsc_db_tables tbl,
27 bsc_db_tables_rels rels
28 where
29 rels.table_name = p_table_name
30 and rels.source_table_name = tbl.table_name
31 and tbl.table_type = 0;
32
33 l_table_type NUMBER;
34 BEGIN
35 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, p_table_name);
36 If l_temp <> -1 AND BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable IS NOT NULL Then
37 l_origin_table := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable, ',');
38 l_origin_name := l_origin_table(l_origin_table.first);
39 l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_origin_name);
40 IF (l_temp2 = -1) THEN
41 OPEN cTableType;
42 FETCH cTableType INTO l_table_type ;
43 CLOSE cTableType;
44 IF (l_table_type=0) THEN
45 return false;
46 ELSE
47 return true;
48 END IF;
49 END IF;
50 If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp2).Type = 0 Then
51 return true;
52 Else
53 return False;
54 End If;
55 Else--Added for alterations to production tables
56 OPEN cTableType;
57 FETCH cTableType INTO l_table_type ;
58 CLOSE cTableType;
59 IF (l_table_type=0) THEN
60 return false;
61 ELSE
62 return true;
63 END IF;
64 return False;
65 End If;
66 EXCEPTION WHEN OTHERS THEN
67 bsc_mo_helper_pkg.writeTmp('Exception in isBasicTable:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
68 bsc_mo_helper_pkg.writeTmp('p_table_name = '||p_table_name||', l_temp = '||l_temp, FND_LOG.LEVEL_STATEMENT, true);
69 raise;
70 End ;
71
72 --****************************************************************************
73 -- GetPhantomLevelPosition
74 --
75 -- DESCRIPTION:
76 -- Returns the position of the given phantom drill within the collection
77 -- of drills families.
78 --
79 -- PARAMETERS:
80 -- colDimensions: collection of drills families
81 -- p_level_code: drill code
82 --****************************************************************************
83 Function GetPhantomLevelPosition(colDimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_level_code IN NUMBER) RETURN NUMBER IS
84 posicion NUMBER;
85 l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
86 l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
87 l_index1 NUMBER;
88 l_index2 NUMBER;
89 l_groups DBMS_SQL.NUMBER_TABLE;
90 BEGIN
91 posicion := 0;
92 IF (colDimensions.count =0 ) THEN
93 return posicion;
94 END IF;
95 l_groups := BSC_MO_HELPER_PKG.getGroupIds(colDimensions);
96 l_index1 := l_groups.first;
97
98 LOOP
99 l_levels := BSC_MO_HELPER_PKG.get_tab_clsLevels(colDimensions, l_groups(l_index1));
100 IF (l_levels.count >0) THEN
101 l_index2 := l_levels.first;
102 END IF;
103 LOOP
104 EXIT WHEN l_levels.count = 0;
105 l_level := l_levels(l_index2);
106
107 If p_level_code <= l_level.Num Then
108 return posicion;
109 End If;
110 posicion := posicion + 1;
111 EXIT WHEN l_index2 = l_levels.last;
112 l_index2 := l_levels.next(l_index2);
113 END LOOP;
114 EXIT WHEN l_index1 = l_groups.last;
115 l_index1 := l_groups.next(l_index1);
116 END LOOP;
117 return posicion;
118
119 EXCEPTION WHEN OTHERS THEN
120 bsc_mo_helper_pkg.writeTmp('Exception in GetPhantomLevelPosition:'||sqlerrm, FND_LOG.LEVEL_STATEMENT, true);
121 raise;
122 End ;
123
124
125 --****************************************************************************
126 -- GetPhantomLevels
127 --
128 -- DESCRIPTION:
129 -- Returns phantom levels of the given indicator and given configuration
130 -- PARAMETERS:
131 -- Indicator: indicator code
132 -- Configuracion: indicator configuration
133 -- arrPhantomLevels(): array to return phantom drills
134 --****************************************************************************
135 Function GetPhantomLevels(pIndicator IN NUMBER,
136 Configuracion IN NUMBER,
137 arrPhantomLevels IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE) RETURN NUMBER IS
138 numPhantomLevels NUMBER;
139 l_stmt VARCHAR2(1000);
140
141 l_level NUMBER;
142 CURSOR C1 is
143 SELECT DISTINCT DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
144 WHERE INDICATOR = pIndicator
145 AND DIM_SET_ID = Configuracion
146 AND STATUS <> 2
147 ORDER BY DIM_LEVEL_INDEX;
148 BEGIN
149 OPEN c1;
150 numPhantomLevels := 0;
151 LOOP
152 FETCH c1 INTO l_level;
153 EXIT WHEN c1%NOTFOUND;
154 arrPhantomLevels(numPhantomLevels) := l_level;
155 numPhantomLevels := numPhantomLevels + 1;
156 END Loop;
157 Close c1;
158 Return numPhantomLevels;
159
160 EXCEPTION WHEN OTHERS THEN
161 bsc_mo_helper_pkg.writeTmp('Exception in GetPhantomLevels:'||sqlerrm, FND_LOG.LEVEL_STATEMENT, true);
162 raise;
163 End;
164
165 --****************************************************************************
166 -- Corrections : CorreccionDrilesFantasmas
167 --
168 -- DESCRIPTION:
169 -- Completes the configuration of drills for the phantom drills with '?'
170 --****************************************************************************
171 PROCEDURE Corrections IS
172
173 Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
174 colConfiguraciones DBMS_SQL.NUMBER_TABLE ;
175 Configuracion NUMBER;
176 colDimensions BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
177 l_stmt VARCHAR2(1000);
178 numPhantomLevels NUMBER;
179 arrPhantomLevels DBMS_SQL.NUMBER_TABLE;
180 i NUMBER;
181 strDriles VARCHAR2(1000);
182 strDrilesAnt VARCHAR2(1000);
183 levelPos NUMBER;
184 l_periodicity_id NUMBER;
185
186 l_index1 NUMBER;
187 l_index2 NUMBER;
188 cv CurTyp ;
189
190 CURSOR cLevelCombinations (pIndicator IN NUMBER, pDimSetID IN NUMBER) IS
191 SELECT PERIODICITY_ID, LEVEL_COMB
192 FROM BSC_KPI_DATA_TABLES
193 WHERE INDICATOR = pIndicator
194 AND DIM_SET_ID = pDimSetID;
195 BEGIN
196 IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count =0) THEN
197 return;
198 END IF;
199 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
200 LOOP
201 Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
202 --Consider only new indicators or changed indicators
203 --BSC-MV Note: If there is change of summarization level
204 --we need to process all the indicators.
205 If (Indicator.Action_Flag = 3) Or (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) Then
206 --Get the list of configurations of the kpi
207 colConfiguraciones := BSC_MO_INDICATOR_PKG.GetConfigurationsForIndic(Indicator.Code);
208 IF (colConfiguraciones.count > 0) THEN
209 l_index2 := colConfiguraciones.first;
210 END IF;
211 LOOP
212 EXIT WHEN colConfiguraciones.count = 0;
213 Configuracion := colConfiguraciones(l_index2);
214 numPhantomLevels := GetPhantomLevels(Indicator.Code, Configuracion, arrPhantomLevels);
215 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
216 bsc_mo_helper_pkg.writeTmp('# of phantom levels = '||numPhantomLevels);
217 END IF;
218 If numPhantomLevels > 0 Then
219 --Get the list of drills of the kpi in the given configuration
220 colDimensions := BSC_MO_INDICATOR_PKG.GetLevelCollection(Indicator.Code, Configuracion);
221 If colDimensions.Count > 0 Then
222 --Only fix those having at least one family of drills
223 --l_stmt := 'SELECT PERIODICITY_ID, LEVEL_COMB FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = :1 AND DIM_SET_ID = :2';
224 OPEN cLevelCombinations(Indicator.Code, Configuracion);
225 LOOP
226 FETCH cLevelCombinations INTO l_periodicity_id, strDriles;
227 EXIT WHEN cLevelCombinations%NOTFOUND;
228 strDrilesAnt := strDriles;
229 For i IN 0..numPhantomLevels - 1 LOOP
230 levelPos := GetPhantomLevelPosition(colDimensions, arrPhantomLevels(i)) + i;
231
232 strDriles := substr(strDriles, 1, levelPos) || '?'||
233 substr(strDriles, levelPos - Length(strDriles) );
234 END LOOP;
235
236 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
237 bsc_mo_helper_pkg.writeTmp('UPDATE BSC_KPI_DATA_TABLES SET LEVEL_COMB = '||
238 strDriles||' WHERE INDICATOR = '|| Indicator.Code||' AND DIM_SET_ID = '||
239 Configuracion||' AND PERIODICITY_ID = '|| l_periodicity_id||' AND LEVEL_COMB = '||strDrilesAnt, FND_LOG.LEVEL_STATEMENT);
240 END IF;
241
242 UPDATE BSC_KPI_DATA_TABLES SET LEVEL_COMB = strDriles
243 WHERE INDICATOR = Indicator.Code AND DIM_SET_ID = Configuracion
244 AND PERIODICITY_ID = l_periodicity_id AND LEVEL_COMB = strDrilesAnt;
245 --EXECUTE IMMEDIATE l_stmt USING strDriles, Indicator.Code, Configuracion, l_periodicity_id, strDrilesAnt;
246
247 END Loop;
248 CLOSE cLevelCombinations;
249 End If;
250 End If;
251 EXIT WHEN l_index2 = colConfiguraciones.last;
252 l_index2 := colConfiguraciones.next(l_index2);
253 END LOOP;
254 End If;
255 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
256 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
257 END LOOP;
258
259 EXCEPTION WHEN OTHERS THEN
260 bsc_mo_helper_pkg.writeTmp('Exception in Corrections : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
261 bsc_mo_helper_pkg.terminateWithError('BSC_DIMCONFIG_FAILED', 'Corrections');
262 RAISE;
263
264 End ;
265
266
267 --****************************************************************************
268 -- RecreateMaterializedViewsIndic
269 --
270 -- DESCRIPTION:
271 -- Recreate the materialized view for the given indicator
272 -- The indicator is a EDW Kpi.
273 --****************************************************************************
274 PROCEDURE ReCreateMaterializedViewsIndic(Indic in number) IS
275 l_STMT VARCHAR2(300);
276 uv_name VARCHAR2(300);
277 table_name VARCHAR2(300);
278 periodicity_id NUMBER;
279 cal_id NUMBER;
280 num_years NUMBER;
281 num_prev_years NUMBER;
282 fiscal_year NUMBER;
283 start_year NUMBER;
284 end_year NUMBER;
285
286 CURSOR c1 IS
287 SELECT DISTINCT TABLE_NAME, PERIODICITY_ID
288 FROM BSC_KPI_DATA_TABLES_V
289 WHERE INDICATOR = indic
290 AND TABLE_NAME IS NOT NULL;
291 l_temp NUMBER;
292 BEGIN
293 --Populate mapping tables for the indicator before create materialized views
294 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
295 bsc_mo_helper_pkg.writeTmp('Inside ReCreateMaterializedViewsIndic', FND_LOG.LEVEL_PROCEDURE);
296 END IF;
297 --l_stmt := 'begin BSC_INTEGRATION_APIS.Populate_Mapping_Tables('|| Indic ||'); end;';
298 --EXECUTE IMMEDIATE l_stmt;
299 -- Obsoleted
300 --BSC_INTEGRATION_APIS.Populate_Mapping_Tables(Indic);
301
302 OPEN c1;
303
304 LOOP
305 FETCH c1 INTO table_name, periodicity_id;
306 EXIT WHEN c1%NOTFOUND;
307 --Create the materialized view
308 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity_id);
309
310 cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312 num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313 num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314 fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
315
316 start_year := fiscal_year - num_prev_years;
317 end_year := start_year + num_years - 1;
318
319 --Create the union view
320 --We have to recreate the union view because the materializaed
321 --view is invalid until loader refresh it
322 uv_name := table_name || BSC_METADATA_OPTIMIZER_PKG.EDW_UNION_VIEW_EXT;
323 l_stmt := 'CREATE OR REPLACE VIEW '|| uv_name ||' AS SELECT * FROM '|| table_name;
324 BSC_MO_HELPER_PKG.do_ddl(l_stmt, ad_ddl.create_view, uv_name);
325
326 END Loop;
327 CLOSE C1;
328 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
329 bsc_mo_helper_pkg.writeTmp('Completed ReCreateMaterializedViewsIndic', FND_LOG.LEVEL_PROCEDURE);
330 END IF;
331
332 EXCEPTION WHEN OTHERS THEN
333 bsc_mo_helper_pkg.writeTmp('Exception in ReCreateMaterializedViewsIndic : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
334 bsc_mo_helper_pkg.terminateWithMsg('Exception in ReCreateMaterializedViewsIndic : '||sqlerrm);
335 raise;
336 End ;
337
338
339 --****************************************************************************
340 -- CalcProjectionTable
341 --
342 -- DESCRIPTION:
343 -- Return true if the projection is calculated on the table
344 --****************************************************************************
345
346 Function CalcProjectionTable(TableName IN VARCHAR2) return BOOLEAN IS
347
348 CURSOR C1(p1 VARCHAR2) IS
349 SELECT PROJECT_FLAG FROM BSC_DB_TABLES
350 WHERE TABLE_NAME = p1;
351 l_proj NUMBER ;
352 l_ret boolean;
353 BEGIN
354 OPEN C1( Upper(tablename));
355 FETCH c1 INTO l_proj;
356
357 IF (c1%NOTFOUND) THEN
358 l_ret := FALSE;
359 Else
360 If l_proj = 0 Then
361 l_ret := false;
362 Else
363 l_ret := true;
364 End If;
365 End If;
366
367 close c1;
368 return l_ret;
369
370 EXCEPTION WHEN OTHERS THEN
371 bsc_mo_helper_pkg.writeTmp('Exception in CalcProjectionTable:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
372 raise;
373 End;
374
375
376 --****************************************************************************
377 -- GetIndicTableType
378 --
379 -- DESCRIPTION:
380 -- Return the type of the table according to BSC_DB_TABLES_RELS and BSC_DB_TABLES
381 -- arrIndicTables() contains the tables used directly by the indicator
382 -- 0 - Input table
383 -- 1 - Base table
384 -- 2 - Temporal Table
385 -- 3 - Indicator table (lowest level)
386 -- 4 - Indicator table (summary level)
387 --****************************************************************************
388 Function GetIndicTableType(TableName IN VARCHAR2,
389 arrIndicTables IN DBMS_SQL.VARCHAR2_TABLE,
390 numIndicTables IN NUMBER) RETURN NUMBER IS
391 l_table_name VARCHAR2(100);
392 l_table_type NUMBER;
393 CURSOR c1(p1 VARCHAR2) IS
394 SELECT T.TABLE_NAME, T.TABLE_TYPE
395 FROM BSC_DB_TABLES T, BSC_DB_TABLES_RELS R
396 WHERE T.TABLE_NAME = R.SOURCE_TABLE_NAME
397 AND R.TABLE_NAME = p1;
398 l_return NUMBER;
399 BEGIN
400 OPEN c1 (UPPER(TableName));
401 FETCH c1 INTO l_table_name, l_table_type;
402 IF (c1%NOTFOUND) THEN
403 l_return := 0; -- input table
404 Else
405 If (l_table_type) = 0 Then
406 l_return := 1; -- base table
407 Else
408 If BSC_MO_HELPER_PKG.findIndexVARCHAR2(arrIndicTables, TableName)>-1 Then
409 If BSC_MO_HELPER_PKG.findIndexVARCHAR2(arrIndicTables, l_table_name)=-1 Then
410 l_return := 3; -- Indicator table (lowest level)
411 Else
412 l_return := 4; -- Indicator table (summary level)
413 End If;
414 Else
415 l_return := 2; -- temporal table
416 End If;
417 End If;
418 End If;
419 CLOSE C1;
420 return l_return;
421
422 EXCEPTION WHEN OTHERS THEN
423 bsc_mo_helper_pkg.writeTmp('Exception in GetIndicTableType:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
424 raise;
425 End ;
426
427
428 --****************************************************************************
429 -- InsertOriginTables
430 --
431 -- DESCRIPTION:
432 -- Insert in the arry arrOriginTables() all the tables in the graph
433 -- where the tables in the array arrTables() come from, including
434 -- themself.
435 --****************************************************************************
436 PROCEDURE InsertOriginTables(arrTables IN DBMS_SQL.VARCHAR2_TABLE ,
437 arrOriginTables IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
438 ) IS
439
440 arrTablesAux DBMS_SQL.VARCHAR2_TABLE;
441 l_stmt VARCHAR2(2000) := null;
442
443 l_table VARCHAR2(100);
444 l_index NUMBER;
445 cv CurTyp ;
446
447 strWhereInTables VARCHAR2(1000);
448 l_varchar2_list dbms_sql.varchar2_table;
449 BEGIN
450 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
451 BSC_MO_HELPER_PKG.writeTmp('Inside InsertOriginTables, arrTables.count = '||arrTables.count);
452 END IF;
453
454 IF (arrTables.count = 0) THEN
455 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
456 BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, count = 0');
457 END IF;
458 return;
459 END IF;
460
461 strWhereInTables := BSC_MO_HELPER_PKG.Get_New_Big_In_Cond_Varchar2(1, 'TABLE_NAME');
462 l_index := arrTables.first;
463
464 LOOP
465 EXIT WHEN arrTables.count = 0;
466 If Not BSC_MO_HELPER_PKG.searchStringExists(arrOriginTables, arrOriginTables.count, arrTables(l_index)) Then
467 arrOriginTables(arrOriginTables.count) := arrTables(l_index);
468 l_varchar2_list(l_varchar2_list.count+1) := arrTables(l_index);
469 --BSC_MO_HELPER_PKG.Add_Value_Big_In_Cond_Varchar2 (1, arrTables(l_index));
470 End If;
471 EXIT WHEN l_index = arrTables.last;
472 l_index := arrTables.next(l_index);
473 END LOOP;
474 bsc_mo_helper_pkg.add_value_bulk(1, l_varchar2_list);
475 l_varchar2_list.delete;
476 l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
477 OPEN CV for l_stmt;
478 LOOP
479 FETCH CV INTO l_table;
480 EXIT WHEN CV%NOTFOUND;
481 arrTablesAux(arrTablesAux.count ) := l_table;
482 END Loop;
483 Close CV;
484
485 InsertOriginTables (arrTablesAux, arrOriginTables);
486 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
487 BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, arrOriginTables.count = '||arrOriginTables.count);
488 END IF;
489
490
491 EXCEPTION WHEN OTHERS THEN
492 bsc_mo_helper_pkg.writeTmp('Exception in InsertOriginTables:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
493 raise;
494 End;
495
496
497 --****************************************************************************
498 -- InitarrTablesIndic
499 --
500 -- DESCRIPTION:
501 -- Initialize the array arrIndicTables() with the tables used directly
502 -- by the indicator in the given configuration.
503 -- Also initializes arrTables() with the tables being used directly
504 -- by the indicator and all related tables until the input tables.
505 --****************************************************************************
506 PROCEDURE InitarrTablesIndic(Indic IN NUMBER, Configuration IN NUMBER,
507 arrTables OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE, numTables OUT NOCOPY NUMBER,
508 arrIndicTables OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE, numIndicTables OUT NOCOPY NUMBER) IS
509
510 l_STMT VARCHAR2(1000);
511 CURSOR C1 (p1 NUMBER, p2 NUMBER) IS
512 SELECT DISTINCT TABLE_NAME FROM BSC_KPI_DATA_TABLES_V
513 WHERE INDICATOR = p1
514 AND DIM_SET_ID = p2
515 AND TABLE_NAME IS NOT NULL;
516
517 CURSOR c2 (p1 NUMBER, p2 NUMBER) IS
518 SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
519 WHERE TABLE_NAME IN (
520 SELECT TABLE_NAME
521 FROM BSC_KPI_DATA_TABLES_V
522 WHERE INDICATOR = p1
523 AND DIM_SET_ID = p2
524 AND TABLE_NAME IS NOT NULL)
525 AND RELATION_TYPE = 1;
526
527 l_table VARCHAR2(100);
528 BEGIN
529 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
530 bsc_mo_helper_pkg.writeTmp('Inside InitarrTablesIndic');
531 END IF;
532 numTables := 0;
533 numIndicTables := 0;
534
535 OPEN c1(Indic, Configuration);
536
537 LOOP
538 FETCH c1 INTO l_table;
539 EXIT WHEN c1%NOTFOUND;
540 arrIndicTables(numIndicTables) := l_table;
541 numIndicTables := numIndicTables + 1;
542 END Loop;
543 CLOSE c1;
544
545 --Insert in the array arrIndicTables() the indicator tables created for targets (BSC_SB_<kpi code>_%)
546 --We need to consider them as tables used directly by the indicator
547
548 OPEN c2(Indic, Configuration);
549
550 LOOP
551 FETCH c2 INTO l_table;
552 EXIT WHEN c2%NOTFOUND;
553 arrIndicTables(numIndicTables) := l_table;
554 numIndicTables := numIndicTables + 1;
555 END Loop;
556 InsertOriginTables( arrIndicTables, arrTables);
557 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
558 bsc_mo_helper_pkg.writeTmp('Completed InitarrTablesIndic');
559 END IF;
560
561 EXCEPTION WHEN OTHERS THEN
562 bsc_mo_helper_pkg.writeTmp('Exception in InitarrTablesIndic:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
563 raise;
564 End ;
565
566
567
568 --****************************************************************************
569 -- NumeroDrilIndic
570 -- DESCRIPTION:
571 -- Returns the drill index of the given drill, configuration and indicator
572 -- PARAMETERS:
573 -- CodIndic: indicator code
574 -- Configuracion: indicator configuration
575 -- NomDril: drill name
576 --****************************************************************************
577 Function getLevelIndex(CodIndic IN NUMBER, Configuracion IN NUMBER, NomDril IN VARCHAR2) RETURN NUMBER IS
578
579 l_STMT VARCHAR2(1000);
580 l_ret NUMBER := -1;
581 CURSOR C1 (p1 NUMBER, p2 NUMBER, p3 VARCHAR2) IS
582 SELECT DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
583 WHERE
584 INDICATOR = p1 AND
585 DIM_SET_ID = p2 AND
586 LEVEL_PK_COL = p3;
587 BEGIN
588
589 OPEN c1(CodIndic, Configuracion, UPPER(nomDril));
590
591 FETCH c1 INTO l_ret;
592 CLOSE c1;
593 return l_ret;
594 EXCEPTION WHEN OTHERS THEN
595 bsc_mo_helper_pkg.writeTmp('Exception in getLevelIndex:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
596 raise;
597 End;
598
599
600 PROCEDURE get_projection_and_gen_type(
601 Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable,
602 PeriodicityOrigin IN NUMBER,
603 p_projection OUT NOCOPY NUMBER,
604 p_gen_type OUT NOCOPY NUMBER) IS
605 Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
606 Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
607 l_index2 number;
608 l_temp number;
609 l_table_origin VARCHAR2(100);
610
611 BEGIN
612 If Tabla.Type = 0 Then
613 p_gen_type := 0;
614 p_projection := 0;
615 Else
616 --BSC-MV Note: generation_type will means:
617 -- 1: The table exists, calculate the table from source tables
618 -- -1: the table do not exists
619 If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
620 If isBasicTable(Tabla.Name) Then
621 p_gen_type := 1;
622 Else
623 --In BSC-MV implementation none of the summary tables exists.
624 --There are projection tables (when targets at different levels
625 --but they are not the same summary tables.
626 p_gen_type := -1;
627 End If;
628 Else
629 p_gen_type := 1;
630 End If;
631 --If the table is only for targets, then this table is not projected.
632 If Tabla.IsTargetTable Then
633 p_projection := 0;
634 Else
635 If Tabla.EDW_Flag = 0 Then
636 --Table is for a BSC KPI
637 p_projection := 0;
638 If isBasicTable(Tabla.Name) Then
639 p_projection := 1;
640 Else
641 --BSC-MV Note: In this architecture we only calculate projection
642 --on tables receiving directly targets (targets at different level)
643 If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
644 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
645 If (BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1) And
646 (Tabla.Periodicity <> PeriodicityOrigin) Then
647 p_projection := 1;
648 End If;
649 End If;
650 End If;
651 End If;
652 --If any of the source tables (soft relations) is a target table then we need to proyect this table.
653 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
654 bsc_mo_helper_pkg.writeTmp('Checking Soft Relations');
655 END IF;
656 IF (Tabla.originTable1 IS NOT NULL) THEN
657 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
658 bsc_mo_helper_pkg.writeTmp('Tabla.originTable1 is not null');
659 END IF;
660 Tabla_originTable1 := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable1, ',');
661 l_index2 := Tabla_originTable1.first;
662 LOOP
663 l_table_origin := Tabla_originTable1(l_index2);
664 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
665 If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
666 p_projection := 1;
667 Exit;
668 End If;
669 EXIT WHEN l_index2 = Tabla_originTable1.last;
670 l_index2 := Tabla_originTable1.next(l_index2);
671 END LOOP;
672 END IF;
673 End If;--If Tabla.IsTargetTable Then
674 End If;--Tabla.Type = 0 Then
675 EXCEPTION WHEN OTHERS THEN
676 bsc_mo_helper_pkg.writeTmp('Exception in get_projection_and_gen_type:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
677 raise;
678
679 END;
680
681 PROCEDURE add_dependant_tables(p_del_s_tables IN dbms_sql.varchar2_table, p_del_tables IN OUT NOCOPY dbms_sql.varchar2_table )is
682
683 l_dummy varchar2(1000);
684
685 cursor MissingSTables IS
686 select table_name from bsc_db_Tables_rels
687 connect by source_table_name = prior table_name
688 start with source_table_name in
689 (
690 select value_v from bsc_tmp_big_in_cond
691 where variable_id=10
692 and session_id = userenv('SESSIONID')
693 )
694 union -- add deleted periodicities
695 select table_name from bsc_db_tables_rels
696 where substr(table_name, 1, instr(table_name, '_', -1)) in
697 (select substr(value_v, 1, instr(value_v, '_', -1))
698 from bsc_tmp_big_in_cond
699 where variable_id = 10
700 and session_id=userenv('SESSIONID')
701 )
702 ;
703
704 begin
705 IF (p_del_s_tables.count=0) then
706 return;
707 END IF;
708 l_dummy := BSC_MO_HELPER_PKG.Get_New_Big_In_Cond_Varchar2(10, 'SOURCE_TABLE_NAME');
709 bsc_mo_helper_pkg.add_value_bulk(10, p_del_s_tables);
710 for i in MissingSTables loop
711 p_del_tables(p_del_tables.count+1) := i.table_name;
712 end loop;
713 return;
714 end;
715
716 --****************************************************************************
717 -- ConfigurarActualizacion
718 --
719 -- DESCRIPTION:
720 -- Configure metadata tables for Loader.
721 --****************************************************************************
722 PROCEDURE ConfigureActualization IS
723 Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable;
724 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
725 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
726 l_table_origin VARCHAR2(100);
727 l_stmt VARCHAR2(1000);
728 Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
729 l_generation_type NUMBER := 0;
730 projection NUMBER := 0;
731 arrZeroCodeKeys DBMS_SQL.VARCHAR2_TABLE;
732 numZeroCodeKeys NUMBER := 0;
733 l_prj_method NUMBER := 0;
734 ZeroCodeOrigin VARCHAR2(300);
735 Periodo_Act NUMBER := 0;
736 SubPeriodo_Act NUMBER := 0;
737 EDW_Flag NUMBER := 0;
738 Dril1 VARCHAR2(300);
739 --MaestraDril1 VARCHAR2(300);
740 --Desag_Oper VARCHAR2(300);
741 --Operadores VARCHAR2(300);
742 i NUMBER := 0;
743 PeriodicityOrigin NUMBER := 0;
744 OriTableName VARCHAR2(300);
745 Calendar_id NUMBER := 0;
746 num_years VARCHAR2(300);
747 num_prev_years VARCHAR2(300);
748 fiscal_year NUMBER := 0;
749 Target_Flag NUMBER := 0;
750 l_index1 NUMBER := 0;
751 l_index2 NUMBER := 0;
752 l_index3 NUMBER := 0;
753 l_temp NUMBER := 0;
754 l_tempv varchar2(1000);
755
756 Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
757 Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
758 Tabla_keyName BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
759 Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
760
761 l_dump DBMS_SQL.VARCHAR2_TABLE;
762 l_dumpcount NUMBER := 0;
763
764 -- Added 08/23/2005 for performance bug 4559323
765 l_tbl_delete dbms_sql.varchar2_table;
766 l_tblrels_table_name dbms_sql.varchar2_table;
767 l_tblrels_src_table_name dbms_sql.varchar2_table;
768 l_tblrels_relation_type dbms_sql.number_table;
769
770 TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771 l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772 l_tab_rels tab_clsRels;
773
774 TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
775 l_db_tables_record BSC_DB_TABLES%ROWTYPE ;
776 l_tab_db_tables tab_clsDBTables;
777
778 TYPE tab_clsDBCalculations IS TABLE OF BSC_DB_CALCULATIONS%ROWTYPE index by binary_integer;
779 l_db_calculations_record BSC_DB_CALCULATIONS%ROWTYPE ;
780 l_tab_db_calculations tab_clsDBCalculations;
781
782 TYPE tab_clsDBTablesCols IS TABLE OF BSC_DB_TABLES_COLS%ROWTYPE index by binary_integer;
783 l_db_tables_cols_record BSC_DB_TABLES_COLS%ROWTYPE ;
784 l_tab_db_tables_cols tab_clsDBTablesCols;
785
786 l_db_calc_1_delete dbms_sql.varchar2_table;
787
788 l_db_cols_1_delete_table_name dbms_sql.varchar2_table;
789 l_db_cols_1_delete_field_name dbms_sql.varchar2_table;
790
791 l_calc4 number := 4;
792 l_calc5 number := 5;
793 l_colP varchar2(1) := 'P';
794 l_colA varchar2(1) := 'A';
795
796 l_dep_tables dbms_sql.varchar2_table;
797 l_del_s_tables dbms_sql.varchar2_table;
798 BEGIN
799 --
800 IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count =0) THEN
801 return;
802 END IF;
803 --
804 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
805 BSC_MO_HELPER_PKG.writeTmp('Inside Loader Configuration ');
806 END IF;
807 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
808 -- Added 08/23/2005 for performance bug 4559323
809 --
810 LOOP
811 Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
812 IF (Tabla.isProductionTable) THEN
813 null;
814 ELSE
815 l_tbl_delete(l_tbl_delete.count+1) := upper(Tabla.Name);
816 -- Bug 4928585, see below
817 IF (tabla.name like 'BSC_S%') then
818 l_del_s_tables(l_del_s_tables.count+1):= upper(Tabla.Name);
819 END IF;
820 END IF;
821 exit when l_index1= BSC_METADATA_OPTIMIZER_PKG.gTables.last;
822 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
823 END LOOP;
824 --
825 -- Bug 4928585 - get the levels that have this as the source table
826 -- If dimensions are deleted, there could be other higher level tables that we need to delete
827 -- for eg. if initially there was a dim with 3 dim objects parent->child and independant
828 -- there would be two levels in the summary
829 -- Now if we delete the parent->child from the dim, there would be only 1 level,
830 -- and so we need to clean out the higher levels (in this eg. 2nd level)
831 --
832 -- this procedure will add any missing S tables to l_tbl_delete
833 add_dependant_tables(l_del_s_tables, l_tbl_delete);
834 FORALL i IN 1..l_tbl_delete.count
835 DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);
836 FORALL i IN 1..l_tbl_delete.count
837 DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);
838 FORALL i IN 1..l_tbl_delete.count
839 DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);
840 FORALL i IN 1..l_tbl_delete.count
841 DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);
842 l_tab_rels.delete;
843 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
844 LOOP
845 Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
846 IF (Tabla.isProductionTable) THEN
847 goto skip_table;
848 END IF;
849 Tabla_keyName.delete;
850 Tabla_keyName := Tabla.keys;
851 Tabla_data.delete;
852 Tabla_Data := Tabla.Data;
853 Tabla_originTable.delete;
854 Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable, ',');
855 Tabla_originTable1.delete;
856 Tabla_originTable1 := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable1, ',');
857 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
858 BSC_MO_HELPER_PKG.writeTmp(' ');
859 BSC_MO_HELPER_PKG.writeTmp(' ');
860 BSC_MO_HELPER_PKG.writeTmp('Processing gTables('||l_index1||') '||bsc_mo_helper_pkg.get_time);
861 BSC_MO_HELPER_PKG.write_this(Tabla);
862 END IF;
863 bsc_mo_helper_pkg.writeTmp('Going to Delete Metadata tables for '||UPPER(Tabla.Name), FND_LOG.LEVEL_STATEMENT, false);
864 --BSC_DB_TABLES_RELS
865 If Tabla.Type = 1 Then
866 -- Changed to bulk deletes/inserts for better performance - bug 4559323
867 PeriodicityOrigin := 0;
868 OriTableName := null;
869 IF (Tabla_originTable.count >0 ) THEN
870 l_index2 := Tabla_originTable.first;
871 LOOP
872 l_table_origin := Tabla_originTable(l_index2);
873 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
874 IF (l_temp = -1 AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
875 PeriodicityOrigin := Tabla.periodicity;
876 ELSE
877 PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
878 END IF;
879 OriTableName := l_table_origin;
880 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
881 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS1:'||Tabla.Name||', '||l_table_origin||', 0');
882 END IF;
883 l_rels_record.table_name := upper(Tabla.Name);
884 l_rels_record.source_table_name := upper(l_table_origin);
885 l_rels_record.relation_type := 0;
886 l_tab_rels(l_tab_rels.count+1) := l_rels_record;
887 EXIT WHEN l_index2 = Tabla_originTable.last;
888 l_index2 := Tabla_originTable.next(l_index2);
889 END LOOP;
890 END IF;
891 --Soft relations
892 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
893 bsc_mo_helper_pkg.writeTmp('Checking Soft Relations1');
894 bsc_mo_helper_pkg.writeTmp('Tabla_originTable1 = '||Tabla.originTable1||', Tabla_originTable1.count = '||Tabla_originTable1.count);
895 END IF;
896 IF (Tabla_originTable1.count > 0 ) THEN
897 l_index2 := Tabla_originTable1.first;
898 LOOP
899 l_table_origin := Tabla_originTable1(l_index2);
900 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
901 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS2:'||Tabla.Name||','||l_table_origin||',1');
902 END IF;
903 l_rels_record.table_name := upper(Tabla.Name);
904 l_rels_record.source_table_name := upper(l_table_origin);
905 l_rels_record.relation_type := 1;
906 l_tab_rels(l_tab_rels.count+1) := l_rels_record;
907 EXIT WHEN l_index2 = Tabla_originTable1.last;
908 l_index2 := Tabla_originTable1.next(l_index2);
909 END LOOP;
910 END IF;
911 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
912 bsc_mo_helper_pkg.writeTmp('Done checking Soft Relations1');
913 END IF;
914 End If; -- IF TABLA.type = 1
915
916 --BSC_DB_TABLES
917 get_projection_and_gen_type(Tabla, PeriodicityOrigin, projection, l_generation_type);
918
919 --EDW Note: Each calendar has his own fiscal year, and range of years
920 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921 Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923 num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924 num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925 fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927 If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
928 --Annual periodicity
929 Periodo_Act := fiscal_year;
930 Else
931 --No Annual periodicity
932 Periodo_Act := 1;
933 End If;
934
935 --BSC-MV Note: In the upgrade process (sum level change from NULL to NOTNULL)
936 --we canot reset the current period, specially in the base and input table.
937 --We need to use the current period of the table
938 If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 Then
939 If Tabla.currentPeriod <> 0 Then
940 Periodo_Act := Tabla.currentPeriod;
941 End If;
942 End If;
943 If Tabla.Type = 0 Then
944 --Note: From version 4.6.0 we are not going to use Periodicities
945 --month-day and month-week (11, 12)
946 SubPeriodo_Act := 0;
947 Else
948 SubPeriodo_Act := 0;
949 End If;
950 EDW_Flag := Tabla.EDW_Flag;
951 If Tabla.IsTargetTable Then
952 Target_Flag := 1;
953 Else
954 Target_Flag := 0;
955 End If;
956 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
957 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES:'||
958 Tabla.Name||', type='|| Tabla.Type||', periodicity='||Tabla.Periodicity||', gen_type='||
959 l_generation_type ||', projection = '|| projection||', num_years'|| num_years||', num_prev_years= '||
960 num_prev_years||','||Periodo_Act||','|| SubPeriodo_Act||','|| EDW_Flag||',' ||Target_Flag||' '||bsc_mo_helper_pkg.get_time);
961 END IF;
962 l_db_tables_record.TABLE_NAME := upper(Tabla.Name);
963 l_db_tables_record.TABLE_TYPE := Tabla.Type;
964 l_db_tables_record.PERIODICITY_ID := Tabla.Periodicity;
965 l_db_tables_record.GENERATION_TYPE := l_generation_type;
966 l_db_tables_record.PROJECT_FLAG := projection;
967 l_db_tables_record.NUM_OF_YEARS := num_years;
968 l_db_tables_record.PREVIOUS_YEARS := num_prev_years;
969 l_db_tables_record.SOURCE_DATA_TYPE := 0;
970 l_db_tables_record.SOURCE_FILE_NAME := null;
971 l_db_tables_record.CURRENT_PERIOD := Periodo_Act;
972 l_db_tables_record.CURRENT_SUBPERIOD := SubPeriodo_Act;
973 l_db_tables_record.EDW_FLAG := EDW_Flag;
974 l_db_tables_record.TARGET_FLAG := Target_Flag;
975 l_tab_db_tables(l_tab_db_tables.count+1) := l_db_tables_record;
976
977 --BSC_DB_CALCULATIONS (Calculation of zero code)
978
979 numZeroCodeKeys := 0;
980 IF (Tabla_keyname.count >0 ) THEN
981 l_index2 := Tabla_keyname.first;
982 LOOP
983 l_key := Tabla_keyname(l_index2);
984 If l_key.CalculateCode0 Then
985 arrZeroCodeKeys(numZeroCodeKeys) := l_key.keyName;
986 numZeroCodeKeys := numZeroCodeKeys + 1;
987 End If;
988 Tabla_keyName(l_index2) := l_key;
989 EXIT WHEN l_index2 = Tabla_keyname.last;
990 l_index2 := Tabla_keyname.next(l_index2);
991 END LOOP;
992 END IF;
993
994 For i IN 0..numZeroCodeKeys - 1 LOOP
995 l_index2 := Tabla_Data.First;
996 LOOP
997 EXIT WHEN Tabla_Data.count = 0;
998 l_measure := Tabla_Data(l_index2);
999 If l_measure.AvgLFlag = 'Y' Then
1000 ZeroCodeOrigin := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression('SUM(' || l_measure.AvgLTotalColumn || ')/SUM('
1001 || l_measure.AvgLCounterColumn || ')');
1002 Else
1003 ZeroCodeOrigin := l_measure.aggFunction|| '('|| l_measure.fieldName || ')';
1004 End If;
1005 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1006 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS1:'||
1007 Tabla.name||','||arrZeroCodeKeys(i)||','||i||','|| l_measure.fieldName||','||ZeroCodeOrigin||' '||bsc_mo_helper_pkg.get_time);
1008 END IF;
1009 l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1010 l_db_calculations_record.CALCULATION_TYPE := 4;
1011 l_db_calculations_record.PARAMETER1 := arrZeroCodeKeys(i);
1012 l_db_calculations_record.PARAMETER2 := i;
1013 l_db_calculations_record.PARAMETER3 := l_measure.fieldName;
1014 l_db_calculations_record.PARAMETER4 := null;
1015 l_db_calculations_record.PARAMETER5 := ZeroCodeOrigin;
1016 l_tab_db_calculations(l_tab_db_calculations.count+1) := l_db_calculations_record;
1017 EXIT WHEN l_index2 = Tabla_Data.last;
1018 l_index2 := Tabla_Data.next(l_index2);
1019 END LOOP;
1020 END LOOP;
1021 --BSC_DB_CALCULATIONS (Merge targets)
1022
1023 IF (Tabla_originTable1.count>0) THEN
1024 l_index2 := Tabla_originTable1.first;
1025 LOOP
1026 l_table_origin := Tabla_originTable1(l_index2);
1027 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1028 If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
1029 IF ( Tabla_Data.count>0) THEN
1030 l_index3 := Tabla_Data.first;
1031 LOOP
1032 l_measure := Tabla_Data(l_index3);
1033 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1034 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS2:'||
1035 Tabla.Name||',5,'|| l_table_origin||','|| l_measure.fieldName);
1036 END IF;
1037 l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1038 l_db_calculations_record.CALCULATION_TYPE := 5;
1039 l_db_calculations_record.PARAMETER1 := l_table_origin;
1040 l_db_calculations_record.PARAMETER2 := l_measure.fieldName;
1041 l_db_calculations_record.PARAMETER3 := null;
1042 l_db_calculations_record.PARAMETER4 := null;
1043 l_db_calculations_record.PARAMETER5 := null;
1044 l_tab_db_calculations(l_tab_db_calculations.count+1) := l_db_calculations_record;
1045 EXIT WHEN l_index3 = Tabla_Data.last;
1046 l_index3 := Tabla_Data.next(l_index3);
1047 END LOOP;
1048 END IF;
1049 End If;
1050 EXIT WHEN l_index2 = Tabla_originTable1.last;
1051 l_index2 := Tabla_originTable1.next(l_index2);
1052 END LOOP;
1053 END IF;
1054
1055 --BSC_DB_TABLES_COLS (Key columns)
1056
1057 IF (Tabla_keyName.count>0) THEN
1058 l_index2 := Tabla_keyName.first;
1059 LOOP
1060 l_key := Tabla_keyName(l_index2);
1061 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1062 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: table_name='||Tabla.Name||
1063 ', column_type=P, column_name = '||l_key.keyName||', source_column='||l_key.Origin||' '||bsc_mo_helper_pkg.get_time);
1064 END IF;
1065 BEGIN
1066 l_db_tables_cols_record.TABLE_NAME := upper(Tabla.Name);
1067 l_db_tables_cols_record.COLUMN_TYPE := 'P';
1068 l_db_tables_cols_record.COLUMN_NAME := l_key.keyName;
1069 l_db_tables_cols_record.SOURCE_COLUMN := l_key.Origin;
1070 l_tab_db_tables_cols(l_tab_db_tables_cols.count+1) := l_db_tables_cols_record;
1071 END;
1072 EXIT WHEN l_index2 = Tabla_keyName.last;
1073 l_index2 := Tabla_keyName.next(l_index2);
1074 END LOOP;
1075 END IF;
1076
1077 --BSC_DB_TABLES_COLS (Data columns)
1078
1079 IF (Tabla_Data.count >0) THEN
1080 l_index2 := Tabla_Data.first;
1081 LOOP
1082 l_measure := Tabla_Data(l_index2);
1083 If projection = 1 Then
1084 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1085 l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1086 Else
1087 l_prj_method := 0;
1088 End If;
1089 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1090 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: Table_name = '||Tabla.Name||', column_type = A, Column Name= '||
1091 l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin||' '||bsc_mo_helper_pkg.get_time );
1092 END IF;
1093 l_db_tables_cols_record.TABLE_NAME := upper(Tabla.Name);
1094 l_db_tables_cols_record.COLUMN_TYPE := 'A';
1095 l_db_tables_cols_record.COLUMN_NAME := l_measure.fieldName;
1096 l_db_tables_cols_record.SOURCE := l_measure.source;
1097 l_db_tables_cols_record.projection_id := l_prj_method;
1098 l_db_tables_cols_record.SOURCE_FORMULA := l_measure.Origin;
1099 l_tab_db_tables_cols(l_tab_db_tables_cols.count+1) := l_db_tables_cols_record;
1100 EXIT WHEN l_index2 = Tabla_Data.last;
1101 l_index2 := Tabla_Data.next(l_index2);
1102 END LOOP;
1103 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1104 BSC_MO_HELPER_PKG.writeTmp('Data Cols insertion completed for Table='||Tabla.Name);
1105 END IF;
1106 END IF;
1107 --Special cases: This is a table of a Balance or PnL indicator
1108 --BSC-MV Note: Profit calculation will be done in the base tables at sub-account level
1109 If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1110 --current architecture
1111 If Tabla.Indicator <> 0 Then
1112 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1113 BSC_MO_HELPER_PKG.writeTmp('Indicator<>0');
1114 END IF;
1115 If BSC_MO_INDICATOR_PKG.IsIndicatorPnL(Tabla.Indicator, true ) Then
1116 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1117 BSC_MO_HELPER_PKG.writeTmp('Indicator is PnL');
1118 END IF;
1119 --This is a table of a PnL indicator
1120 IF (tabla_keyName.count > 0 ) THEN
1121 Dril1 := Tabla_keyName(Tabla_keyName.first).keyName;
1122 END IF;
1123 If getLevelIndex(Tabla.Indicator, Tabla.Configuration, Dril1) = 1 Then
1124 --This is the table for the first drill (account drill).
1125 --We need to calculate the profit
1126 --BSC_DB_CALCULATIONS
1127 l_db_calc_1_delete(l_db_calc_1_delete.count+1) := UPPER(Tabla.Name);
1128 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1129 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS3:'||Tabla.Name||',1,'|| Dril1||' '||bsc_mo_helper_pkg.get_time);
1130 END IF;
1131 l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1132 l_db_calculations_record.CALCULATION_TYPE := 1;
1133 l_db_calculations_record.PARAMETER1 := Dril1;
1134 l_db_calculations_record.PARAMETER2 := null;
1135 l_db_calculations_record.PARAMETER3 := null;
1136 l_db_calculations_record.PARAMETER4 := null;
1137 l_db_calculations_record.PARAMETER5 := null;
1138 l_tab_db_calculations(l_tab_db_calculations.count+1) := l_db_calculations_record;
1139 End If;
1140 End If;
1141 END IF;
1142 End If;
1143 <<skip_table>>
1144 IF ( Tabla.isProductionTable AND Tabla.isProductionTableAltered) THEN
1145 BSC_MO_HELPER_PKG.writeTmp('Production table altered', FND_LOG.LEVEL_STATEMENT, false);
1146 BSC_MO_HELPER_PKG.write_to_stack('Production table altered');
1147 -- table has been altered, so insert into bsc_db_tables_cols
1148 If Tabla.Type = 1 Then
1149 BSC_MO_HELPER_PKG.write_to_stack('Table Type=1');
1150 PeriodicityOrigin := 0;
1151 Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable, ',');
1152 BSC_MO_HELPER_PKG.write_to_stack('OriginTable='||Tabla.originTable);
1153 OriTableName := null;
1154 IF (Tabla_originTable.count >0 ) THEN
1155 l_table_origin := Tabla_originTable(0);
1156 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1157 BSC_MO_HELPER_PKG.write_to_stack('l_temp='||l_temp);
1158 IF (l_temp = -1 OR BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
1159 PeriodicityOrigin := Tabla.periodicity;
1160 ELSE
1161 PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
1162 END IF;
1163 BSC_MO_HELPER_PKG.write_to_stack('PeriodicityOrigin='||PeriodicityOrigin);
1164 END IF;
1165 END IF;
1166 IF (Tabla.data.count>0) THEN
1167 BSC_MO_HELPER_PKG.write_to_stack('going to get_projection_and_gen_type');
1168 get_projection_and_gen_type(Tabla, PeriodicityOrigin, projection, l_generation_type);
1169 -- Get list of source tables from this table to the Input table
1170 Tabla_originTable := BSC_DBGEN_UTILS.get_source_table_names(Tabla.name);
1171 Tabla_originTable(Tabla_originTable.count) := Tabla.Name;
1172 FOR i IN Tabla.data.first..Tabla.data.last LOOP
1173 l_measure := Tabla.data(i);
1174 BSC_MO_HELPER_PKG.writeTmp('Considering '||Tabla.data(i).fieldName, FND_LOG.LEVEL_STATEMENT, false);
1175 BSC_MO_HELPER_PKG.write_this(Tabla.data(i));
1176 IF Tabla.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
1177 If projection = 1 Then
1178 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1179 BSC_MO_HELPER_PKG.writeTmp('gLov, l_temp='||l_temp, FND_LOG.LEVEL_STATEMENT, false);
1180 l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1181 Else
1182 l_prj_method := 0;
1183 End If;
1184 BSC_MO_HELPER_PKG.writeTmp('l_prj_method='||l_prj_method, FND_LOG.LEVEL_STATEMENT, false);
1185 FOR j IN Tabla_originTable.first..Tabla_originTable.last LOOP
1186 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1187 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS5: Table_name = '||Tabla_originTable(j)||', column_type = A, Column Name= '||
1188 l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin );
1189 END IF;
1190 l_db_cols_1_delete_table_name(l_db_cols_1_delete_table_name.count+1) := upper(Tabla_originTable(j));
1191 l_db_cols_1_delete_field_name(l_db_cols_1_delete_field_name.count+1) := l_measure.fieldName;
1192 --DELETE FROM BSC_DB_TABLES_COLS WHERE table_name = upper(Tabla_originTable(j)) AND column_type='A' and column_name=l_measure.fieldName;
1193 l_db_tables_cols_record.TABLE_NAME := upper(Tabla_originTable(j));
1194 l_db_tables_cols_record.COLUMN_TYPE := 'A';
1195 l_db_tables_cols_record.COLUMN_NAME := l_measure.fieldName;
1196 l_db_tables_cols_record.SOURCE := l_measure.source;
1197 l_db_tables_cols_record.projection_id := l_prj_method;
1198 l_db_tables_cols_record.SOURCE_FORMULA := l_measure.Origin;
1199 l_tab_db_tables_cols(l_tab_db_tables_cols.count+1) := l_db_tables_cols_record;
1200 END LOOP;
1201 END IF;
1202 END LOOP;
1203 END IF;
1204 END IF;
1205 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1206 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1207 END LOOP;
1208 BSC_MO_HELPER_PKG.writeTmp('Checkpoint 11 '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
1209
1210 --BULK DELETES
1211 FORALL i IN 1..l_db_calc_1_delete.count
1212 DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_db_calc_1_delete(i) AND CALCULATION_TYPE = 1;
1213
1214 FORALL i IN 1..l_db_cols_1_delete_table_name.count
1215 DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME=l_db_cols_1_delete_table_name(i) AND column_name=l_db_cols_1_delete_field_name(i) and column_type='A';
1216
1217
1218 -- BULK INSERTS
1219 BEGIN
1220 FORALL i IN 1..l_tab_rels.count
1221 INSERT INTO BSC_DB_TABLES_RELS VALUES l_tab_rels(i);
1222 EXCEPTION
1223 WHEN others THEN
1224 FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1225 bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||
1226 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, FND_LOG.LEVEL_EXCEPTION, true);
1227 bsc_mo_helper_pkg.writeTmp('Oracle error is ' ||
1228 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),FND_LOG.LEVEL_EXCEPTION, true);
1229 END LOOP;
1230 FOR i IN 1..l_tab_rels.count LOOP
1231 bsc_mo_helper_pkg.writeTmp('Table='||l_tab_rels(i).table_name||', Source='||l_tab_rels(i).source_table_name||', Type='||l_tab_rels(i).relation_type,FND_LOG.LEVEL_EXCEPTION, true);
1232 END LOOP;
1233 END;
1234
1235 BEGIN
1236 FORALL i IN 1..l_tab_db_calculations.count
1237 INSERT INTO BSC_DB_CALCULATIONS VALUES l_tab_db_calculations(i);
1238 EXCEPTION
1239 WHEN others THEN
1240
1241 bsc_mo_helper_pkg.writeTmp('Insert calculations: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);
1242 FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1243 bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||
1244 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, FND_LOG.LEVEL_EXCEPTION, true);
1245 bsc_mo_helper_pkg.writeTmp('Oracle error is ' ||
1246 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),FND_LOG.LEVEL_EXCEPTION, true);
1247 END LOOP;
1248 END;
1249
1250 BEGIN
1251 FORALL i IN 1..l_tab_db_tables.count
1252 INSERT INTO BSC_DB_TABLES VALUES l_tab_db_tables(i);
1253 EXCEPTION
1254 WHEN others THEN
1255
1256 bsc_mo_helper_pkg.writeTmp('Insert tables: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);
1257 FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1258 bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||
1259 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, FND_LOG.LEVEL_EXCEPTION, true);
1260 bsc_mo_helper_pkg.writeTmp('Oracle error is ' ||
1261 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),FND_LOG.LEVEL_EXCEPTION, true);
1262 END LOOP;
1263 END;
1264
1265 BEGIN
1266 FORALL i IN 1..l_tab_db_tables_cols.count
1267 INSERT INTO BSC_DB_TABLES_COLS VALUES l_tab_db_tables_cols(i);
1268 EXCEPTION
1269 WHEN others THEN
1270
1271 bsc_mo_helper_pkg.writeTmp('Insert Cols: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);
1272 FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1273 bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||
1274 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, FND_LOG.LEVEL_EXCEPTION, true);
1275 bsc_mo_helper_pkg.writeTmp('Oracle error is ' ||
1276 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),FND_LOG.LEVEL_EXCEPTION, true);
1277 END LOOP;
1278 END;
1279
1280
1281 If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1282 --BSC-MV Note: Now that all the Loader metadata is configured, We need to configure
1283 --the periodicity calculation in the base tables. It is going to call a Loader API
1284 --to do it. The same API is re-used in upgrade.
1285 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1286 LOOP
1287 Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
1288 -- bug 4581847, should call this for production tables also
1289 If isBasicTable(Tabla.Name) Then
1290 BSC_UPDATE.Configure_Periodicity_Calc_VB(tabla.name);
1291 BSC_MO_HELPER_PKG.CHeckError('BSC_UPDATE.Configure_Periodicity_Calc_VB');
1292 End If;
1293 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1294 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1295 END LOOP;
1296 BSC_MO_HELPER_PKG.writeTmp('Going to configure Profit Calculations '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
1297
1298 --BSC-MV Note: Now that all the Loader metadata is configured, We need to configure
1299 --the Profit calculation in the base tables. It is going to call a Loader API
1300 --to do it. The same API is re-used in upgrade.
1301 BSC_UPDATE.Configure_Profit_Calc_VB;
1302 BSC_MO_HELPER_PKG.CheckError('BSC_UPDATE.Configure_Profit_Calc_VB');
1303 End If;
1304
1305 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1306 BSC_MO_HELPER_PKG.writeTmp('Completed Loader Configuration '||bsc_mo_helper_pkg.get_time);
1307 END IF;
1308
1309 EXCEPTION WHEN OTHERS THEN
1310 bsc_mo_helper_pkg.writeTmp( ' Exception in Loader Configuration:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1311 fnd_message.set_name('BSC', 'BSC_LOAD_CONFIGURATION_FAILED');
1312 app_exception.raise_exception;
1313 RAISE;
1314 End;
1315
1316 --****************************************************************************
1317 -- ReConfigureUploadFieldsIndic
1318 --
1319 -- DESCRIPTION:
1320 -- Re-configure the fields of data of the indicator tables.
1321 --
1322 --****************************************************************************
1323
1324 PROCEDURE ReConfigureUploadFieldsIndic(Indic IN NUMBER) IS
1325 arrTables DBMS_SQL.VARCHAR2_TABLE;
1326 numTables NUMBER;
1327 arrIndicTables DBMS_SQL.VARCHAR2_TABLE;
1328 numIndicTables NUMBER;
1329 colDatos BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1330 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1331 i NUMBER;
1332 l_table_origin VARCHAR2(100);
1333 l_table_type NUMBER;
1334 l_stmt VARCHAR2(1000);
1335
1336 colConfiguraciones DBMS_SQL.NUMBER_TABLE;
1337 Configuracion Number;
1338 newColumnFlag Boolean;
1339 ZeroCodeOrigin VARCHAR2(1000);
1340 l_source VARCHAR2(1000);
1341 ddl_sql VARCHAR2(1000);
1342
1343 l_index1 NUMBER;
1344 l_index2 NUMBER;
1345 l_index3 NUMBER;
1346 l_temp NUMBER;
1347
1348 l_table_name VARCHAR2(100);
1349 zeroCodeDataColumns VARCHAR2(1000) ;
1350 l_3 VARCHAR2(250);
1351 l_5 VARCHAR2(250);
1352 cv CurTyp ;
1353 l_stack varchar2(32000);
1354
1355 CURSOR cRollups(pIndicator IN NUMBER, pDimSetID IN NUMBER) IS
1356 SELECT DISTINCT parameter3, parameter5 FROM bsc_db_calculations
1357 WHERE table_name IN (SELECT table_name
1358 FROM bsc_kpi_data_tables
1359 WHERE indicator = pIndicator
1360 AND dim_set_id = pDimSetID
1361 AND sql_stmt IS NOT NULL)
1362 AND CALCULATION_TYPE = 4 ;
1363 -- bug 4114501
1364 l_optimizationMode number := 1;
1365 l_count number;
1366 l_cols_used VARCHAR2(32000);
1367 BEGIN
1368 bsc_mo_helper_pkg.writeTmp('Inside ReConfigureUploadFieldsIndic');
1369 --Get the list of configurations of the kpi
1370 colConfiguraciones := BSC_MO_INDICATOR_PKG.GetConfigurationsForIndic(Indic);
1371 IF (colConfiguraciones.count=0) THEN
1372 bsc_mo_helper_pkg.writeTmp('Completed ReConfigureUploadFieldsIndic, 0 count');
1373 return;
1374 END IF;
1375
1376 l_index1 := colConfiguraciones.first;
1377
1378 LOOP
1379 Configuracion := colConfiguraciones(l_index1);
1380 --Initialize the array arrTables() with all tables used by the indicator
1381 --in the given configuration
1382 --IndicTables() contains the tables used direclty by the indicator in the given configuration
1383 --including the target tables created for the indicator
1384 InitarrTablesIndic( Indic, Configuracion, arrTables, numTables, arrIndicTables, numIndicTables);
1385 --Initialize the collection of data columns of the indicator
1386 colDatos := BSC_MO_INDICATOR_PKG.GetDataFields(Indic, Configuracion, True);
1387 bsc_mo_helper_pkg.write_this(colDatos, FND_LOG.LEVEL_STATEMENT, false);
1388 --Reconfigure
1389 --The only non-structural changes that could happen are:
1390 --Change in the rollup method
1391 --Change in the projection method
1392 --Change on the rollup to formula option
1393 bsc_mo_helper_pkg.addStack (l_stack, ' NumTables = '||numTables);
1394 bsc_mo_helper_pkg.writeTmp(' NumTables = '||numTables);
1395 For i IN arrTables.first..arrTables.last LOOP
1396 l_table_type := GetIndicTableType(arrTables(i), arrIndicTables, numIndicTables);
1397 bsc_mo_helper_pkg.addStack (l_stack, ' Table = '||arrTables(i)||', l_table_type = '||l_table_type);
1398 bsc_mo_helper_pkg.writeTmp(' Table = '||arrTables(i)||', l_table_type = '||l_table_type);
1399 If l_table_type <> 0 Then --It is not an input table
1400 IF (colDatos.count > 0) THEN
1401 l_index2 := colDatos.First;
1402 END IF;
1403 LOOP
1404 EXIT WHEN colDatos.count = 0;
1405 l_measure := colDatos(l_index2);
1406 newColumnFlag := False;
1407 bsc_mo_helper_pkg.writeTmp('Processing '||l_measure.fieldName);
1408 If (l_table_type = 3) Or (l_table_type = 4) Then
1409 --BSC-MV Note: In this Architecture there COULD be a projection
1410 --table created for target at different levels. In this case we
1411 --need to add this new column to the projection table.
1412 If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1413 l_optimizationMode := bsc_mo_helper_pkg.getKPIPropertyValue(Indic, 'DB_TRANSFORM', 1);
1414 IF (l_optimizationMode = 2) THEN -- targets at different levels, PT tables shd exist
1415 l_table_name := BSC_MO_INDICATOR_PKG.GetProjectionTableName(arrTables(i));
1416 ELSE
1417 l_table_name := arrTables(i);
1418 END IF;
1419 Else
1420 l_table_name := arrTables(i);
1421 End If;
1422 bsc_mo_helper_pkg.addStack (l_stack, ' Check1 : l_table_name = '||l_table_name);
1423 --This is table used directly by the indicator
1424 --We need to make sure that the internal column exists
1425 If substr(l_measure.fieldName, 1, 5) = 'BSCIC' Then
1426 IF (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV =false) OR (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV AND l_optimizationMode=2) THEN
1427 If Not BSC_MO_HELPER_PKG.table_column_exists(l_table_name, l_measure.fieldName) Then
1428 --Add the internal column
1429 ddl_sql := 'ALTER TABLE ' || l_table_name ||' ADD ' || l_measure.fieldName ||' NUMBER ';
1430 begin
1431 BSC_MO_HELPER_PKG.Do_DDL( ddl_sql , ad_ddl.alter_table, l_table_name);
1432 newColumnFlag := True;
1433 exception when others then
1434 BSC_MO_HELPER_PKG.writeTmp('Error while executing : '||ddl_sql||', Error is '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1435 raise;
1436 end;
1437 End If;
1438 END IF;
1439 -- Bug 4466627
1440 IF (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV) THEN
1441 SELECT COUNT(1) INTO l_count
1442 FROM BSC_DB_TABLES_COLS
1443 WHERE table_name=l_table_name AND column_name=l_measure.fieldName;
1444 BSC_MO_HELPER_PKG.writeTmp('MV arch, l_count= '||l_count||' for '||l_table_name ||' field='||l_measure.fieldName);
1445 IF (l_count=0) THEN
1446 newColumnFlag := true;
1447 BSC_MO_HELPER_PKG.writeTmp('Setting new col flag to true');
1448 END IF;
1449 END IF;
1450 End If;
1451 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt2 ');
1452 End If;
1453 --BSC_DB_CALCULATIONS (Zero code calculation)
1454 If ((l_table_type = 3) Or (l_table_type = 4)) And (l_measure.AvgLFlag = 'Y') Then
1455 --This is table used directly by the indicator
1456 --and the data is AVGL
1457 ZeroCodeOrigin := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression('SUM(' || l_measure.AvgLTotalColumn ||')/SUM('||
1458 l_measure.AvgLCounterColumn ||')');
1459 Else
1460 ZeroCodeOrigin := l_measure.aggFunction || '(' || l_measure.fieldName || ')';
1461 End If;
1462 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt3 ');
1463 If newColumnFlag Then
1464 --This is a internal column that was added to the indicator table
1465 --If the table does not calculate zero code then no record is inserted
1466 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1467 BSC_MO_HELPER_PKG.writeTmp('4 INSERT DB_CALCULATIONS:');
1468 END IF;
1469 l_stmt := 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2 '||
1470 ', PARAMETER3, PARAMETER4, PARAMETER5) '||
1471 ' SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2, :1, parameter4, :2 FROM BSC_DB_CALCULATIONS '||
1472 ' WHERE TABLE_NAME = :3 AND CALCULATION_TYPE = 4';
1473 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt4.1, L_STMT = '||l_stmt);
1474 INSERT INTO BSC_DB_CALCULATIONS
1475 (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2
1476 , PARAMETER3, PARAMETER4, PARAMETER5)
1477 SELECT DISTINCT
1478 TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2,
1479 l_measure.fieldName, parameter4, ZeroCodeOrigin
1480 FROM BSC_DB_CALCULATIONS
1481 WHERE TABLE_NAME = UPPER(arrTables(i)) AND CALCULATION_TYPE = 4;
1482 Else
1483 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1484 BSC_MO_HELPER_PKG.writeTmp('4 UPDATE DB_CALCULATIONS:');
1485 END IF;
1486 --If the table does not calculate zero code then no record is updated
1487 l_stmt := 'UPDATE BSC_DB_CALCULATIONS SET PARAMETER5 = :1 WHERE '||
1488 ' TABLE_NAME = :2 AND PARAMETER3 = :3 AND CALCULATION_TYPE = 4 ';
1489 bsc_mo_helper_pkg.writeTmp(' Chkpt4.2, L_STMT = '||l_stmt||', :1='||ZeroCodeOrigin||', :2='||UPPER(arrTables(i))||', :3='||
1490 l_measure.fieldName);
1491 UPDATE BSC_DB_CALCULATIONS
1492 SET PARAMETER5 = ZeroCodeOrigin
1493 WHERE
1494 TABLE_NAME = UPPER(arrTables(i))
1495 AND PARAMETER3 = l_measure.fieldName
1496 AND CALCULATION_TYPE = 4 ;
1497 End If;
1498 --BSC_DB_CALCULATIONS (Merge targets)
1499 --No changes for current columns
1500 If newColumnFlag Then
1501 --This is a internal column that was added to the indicator table
1502 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1503 BSC_MO_HELPER_PKG.writeTmp('5 INSERT DB_CALCULATIONS:');
1504 END IF;
1505 l_stmt:= 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2) '||
1506 ' SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, :2'||
1507 ' FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = :2 AND CALCULATION_TYPE = 5';
1508 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt4.3, L_STMT = '||l_stmt);
1509 INSERT INTO BSC_DB_CALCULATIONS(TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2)
1510 SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, l_measure.fieldName
1511 FROM BSC_DB_CALCULATIONS
1512 WHERE TABLE_NAME = UPPER(arrTables(i))
1513 AND CALCULATION_TYPE = 5;
1514 End If;
1515 --BSC_DB_TABLES_COLS
1516 IF (l_table_type =3) THEN
1517 --Case 3
1518 --The table is not generated from another indicator table
1519 --This is a base table o the indicator.
1520 --We calculate average at lowest level and formula at lowest level
1521 --where the lowest level is the lowest level of the kpi.
1522 IF (l_measure.InternalColumnType=0) THEN
1523 --Case 0 --Normal (Non-Internal column)
1524 l_source := l_measure.aggFunction|| '(' || l_measure.fieldName || ')';
1525 ELSIF (l_measure.InternalColumnType=1) THEN
1526 --Case 1 --Formula at lowest level
1527 l_source := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression(l_measure.aggFunction|| '(' ||
1528 l_measure.InternalColumnSource || ')');
1529 ELSIF (l_measure.InternalColumnType=2) THEN
1530 --Case 2 --Total for Average at Lowest Level
1531 l_source := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression('SUM(' || l_measure.InternalColumnSource || ')');
1532 ELSIF (l_measure.InternalColumnType=3) THEN
1533 --Case 3 --Counter for Average at Lowest Level
1534 l_source := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression('COUNT(' || l_measure.InternalColumnSource || ')');
1535 End IF;
1536 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt5.1, l_source = '||l_source);
1537 ELSIF (l_table_type =4) THEN
1538 --Case 4
1539 --The table is originated from another indicator table
1540 If l_measure.AvgLFlag = 'Y' Then
1541 l_source := BSC_MO_INDICATOR_PKG.GetFreeDivZeroExpression('SUM(' || l_measure.AvgLTotalColumn || ')/SUM('||
1542 l_measure.AvgLCounterColumn || ')');
1543 Else
1544 l_source := l_measure.aggFunction || '(' || l_measure.fieldName || ')';
1545 End If;
1546 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt5.1b, l_source = '||l_source);
1547 ELSE -- Case else
1548 --Base tables, temporal tables
1549 l_source := l_measure.aggFunction || '(' || l_measure.fieldName || ')';
1550 bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt5.1c, l_source = '||l_source);
1551 End IF;
1552 If newColumnFlag Then
1553 --This is a internal column that was added to the indicator table
1554 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1555 BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS newcol: table_name='||
1556 arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source_column='||l_source);
1557 END IF;
1558 INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)
1559 VALUES(upper(arrTables(i)), 'A', l_measure.fieldName, l_measure.source, 0, l_source);
1560 Else
1561 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562 BSC_MO_HELPER_PKG.writeTmp('UPDATE DB_TABLES_COLS newcol: table_name='||
1563 arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source='||l_measure.source||', source_column='||l_source);
1564 END IF;
1565 UPDATE BSC_DB_TABLES_COLS
1566 SET SOURCE_FORMULA = l_source
1567 WHERE TABLE_NAME =UPPER(arrTables(i))
1568 AND COLUMN_NAME = l_measure.fieldName
1569 AND SOURCE = l_measure.source
1570 AND COLUMN_TYPE = 'A';
1571 End If;
1572 --BSC_DB_TABLES_COLS (Projection method)
1573 If CalcProjectionTable(arrTables(i)) Then
1574 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1575 UPDATE BSC_DB_TABLES_COLS
1576 SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod
1577 WHERE TABLE_NAME = UPPER(arrTables(i))
1578 AND COLUMN_NAME = l_measure.fieldName
1579 AND SOURCE = l_measure.source
1580 AND COLUMN_TYPE = 'A';
1581 End If;
1582 EXIT WHEN l_index2 = colDatos.last;
1583 l_index2 := colDatos.next(l_index2);
1584 END LOOP;
1585 End If;
1586 END LOOP;
1587 --Fix bug#3350103 If the user change the rollup method we need to update
1588 --the sql statements configured in BSC_KPI_DATA_TABLES for zero codes
1589 If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1590
1591 OPEN cRollups(indic, Configuracion);
1592 bsc_mo_helper_pkg.addStack (l_stack, ' Chk 8');
1593 zeroCodeDataColumns := null;
1594 LOOP
1595 FETCH cRollups INTO l_3, l_5;
1596 EXIT WHEN cRollups%NOTFOUND;
1597 zeroCodeDataColumns := zeroCodeDataColumns || ', ' || l_5|| ' ' || l_3;
1598 END Loop;
1599 Close cRollups;
1600 If zeroCodeDataColumns IS NOT NULL Then
1601 bsc_mo_helper_pkg.addStack (l_stack, ' Chk 9');
1602 l_stmt := ' UPDATE bsc_kpi_data_tables '||
1603 ' SET sql_stmt = SUBSTR(sql_stmt, 1, INSTR(sql_stmt, ''PERIOD_TYPE_ID'') - 1)||''PERIOD_TYPE_ID''||'''|| zeroCodeDataColumns || '''|| '||
1604 ' SUBSTR(sql_stmt, INSTR(sql_stmt, '' FROM ''))'||
1605 ' WHERE indicator = :1 ' ||
1606 ' AND dim_set_id = :2 ' ||
1607 ' AND sql_stmt IS NOT NULL';
1608
1609 EXECUTE IMMEDIATE l_stmt USING Indic, Configuracion;
1610 End If;
1611 bsc_mo_helper_pkg.addStack (l_stack, ' Chk 10');
1612 End If;
1613
1614
1615 EXIT WHEN l_index1 = colConfiguraciones.last;
1616 l_index1 := colConfiguraciones.next(l_index1);
1617 END LOOP;
1618 bsc_mo_helper_pkg.writeTmp('Completed ReConfigureUploadFieldsIndic');
1619 EXCEPTION WHEN OTHERS THEN
1620 bsc_mo_helper_pkg.writeTmp('Exception in ReConfigureUploadFieldsIndic:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1621 bsc_mo_helper_pkg.writeTmp('Stack is : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1622 raise;
1623 End ;
1624
1625 END BSC_MO_LOADER_CONFIG_PKG;
[Home] [Help]