1 Package Body BSC_MO_INPUT_TABLE_PKG AS
2 /* $Header: BSCMOIPB.pls 120.28 2006/04/19 16:29:18 arsantha noship $ */
3 g_newline VARCHAR2(10):= '
4 ';
5
6
7 FUNCTION get_measure_group(p_field_name IN VARCHAR2, p_source IN VARCHAR2) return NUMBER IS
8 cursor cMeasureGroup IS
9 select measure_group_id from bsc_db_measure_cols_vl
10 where measure_col = p_field_name;
11 --and measure_type = 0;
12 l_measure_group number;
13 l_temp number;
14 BEGIN
15 l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, p_field_name, p_source, false);
16 l_measure_group := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode;
17 return l_measure_group;
18 END;
19
20
21 FUNCTION get_measures_for_table(p_table_pattern in varchar2) return BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
22 l_stmt varchar2(1000):='SELECT distinct column_Name, source
23 FROM bsc_db_tables_cols
24 WHERE column_type = :1
25 AND table_name like :2
26 AND column_name not like :3';
27 cv CurTyp;
28 l_measure_col VARCHAR2(320);
29 l_source VARCHAR2(100);
30 l_datafield BSC_METADATA_OPTIMIZER_PKG.clsDataField;
31 l_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
32 BEGIN
33 OPEN cv FOR l_stmt using 'A', p_table_pattern, 'BSCIC%';
34 LOOP
35 FETCH cv INTO l_measure_col, l_source;
36 EXIT WHEN cv%NOTFOUND;
37 l_datafield.fieldName := l_measure_col;
38 l_datafield.source := l_source;
39 l_data(l_data.count+1) := l_datafield;
40 END LOOP;
41 CLOSE cv;
42 return l_data;
43 EXCEPTION WHEN OTHERS THEN
44 bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_measures_for_table:'||sqlerrm);
45 raise;
46 END;
47
48 FUNCTION get_measure_group(p_table IN VARCHAR2, p_data IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField, p_indicator NUMBER, p_dim_set NUMBER) return NUMBER IS
49
50 l_obj_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
51 l_table_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
52 cursor cMeasureGroup(p_measure VARCHAR2) IS
53 select measure_group_id from bsc_db_measure_cols_vl
54 where measure_col = p_measure
55 and measure_type = 0;
56 l_measure_group number;
57 BEGIN
58 l_measure_group := -99999;
59 IF (p_data.count =0) then
60 return l_measure_group;
61 END IF;
62 l_obj_measures := get_measures_for_table('BSC_S_'||p_indicator||'_'||p_dim_set||'%');
63 bsc_mo_helper_pkg.writeTmp('# of measures in BSC_S_'||p_indicator||'_'||p_dim_set ||'% is '||l_obj_measures.count||', # in p_data='||p_data.count);
64
65 FOR i IN p_data.first..p_data.last LOOP
66 FOR j IN 1..l_obj_measures.count LOOP
67 IF (p_data(i).fieldName = l_obj_measures(j).fieldName AND
68 p_data(i).source = l_obj_measures(j).source) THEN
69 return get_measure_group(p_data(i).fieldName, p_data(i).source);
70 END IF;
71 END LOOP;
72 END LOOP;
73 return l_measure_group;
74 EXCEPTION WHEN OTHERS THEN
75 bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_measure_group:'||sqlerrm);
76 raise;
77 END;
78
79
80 PROCEDURE set_origin_table_from_db(KpiTable IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
81 TableName VARCHAR2(100);
82 SourceTableName VARCHAR2(100);
83 TablaOri VARCHAR2(1000);
84 l_stmt VARCHAR2(1000);
85 lstKeys VARCHAR2(1000);
86 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
87 l_index NUMBER;
88 l_counter NUMBER;
89 cv CurTyp;
90 KpiTableName VARCHAR2(100);
91 l_error VARCHAR2(1000);
92 CURSOR cTable(l_src IN VARCHAR2, l_tbl IN VARCHAR2) IS
93 SELECT table_name FROM bsc_db_tables_rels
94 WHERE source_table_name = l_src AND table_name LIKE l_tbl;
95
96 CURSOR cColumns(l_table IN VARCHAR2, l_column IN VARCHAR2, l_column_type IN VARCHAR2) IS
97 SELECT source_column FROM bsc_db_tables_cols
98 WHERE table_name = l_table AND UPPER(column_name) = l_column AND column_type = l_column_type;
99
100 BEGIN
101
102 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
103 bsc_mo_helper_pkg.writeTmp('set_origin_table_from_db for table='||KpiTable.name);
104 END IF;
105 l_index := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, KpiTable.Indicator);
106 If BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).OptimizationMode = 0 Then
107 --The indicator is pre-calculated.
108 TableName := 'BSC_S_' || KpiTable.Indicator || '_' || KpiTable.Configuration || '%';
109 If KpiTable.keys.Count = 0 Then
110 l_stmt := 'SELECT DISTINCT r.source_table_name FROM bsc_db_tables_rels r, bsc_db_tables t
111 WHERE r.table_name LIKE :1 AND
112 NOT r.source_table_name LIKE :2 AND
113 r.table_name = t.table_name AND
114 t.periodicity_id = :3 AND
115 r.relation_type = 0';
116 Else
117 lstKeys := null;
118 l_counter := KpiTable.keys.first;
119 LOOP
120 EXIT WHEN KpiTable.keys.count = 0;
121 l_key := KpiTable.keys(l_counter);
122 If lstKeys IS NOT NULL Then
123 lstKeys := lstKeys || ',';
124 End If;
125 lstKeys := lstKeys || ''''|| l_key.keyName ||'''';
126 EXIT WHEN l_counter = KpiTable.keys.last;
127 l_counter := KpiTable.keys.next(l_counter);
128 END LOOP;
129 l_stmt := 'SELECT DISTINCT table_name AS SOURCE_TABLE_NAME
130 FROM bsc_db_tables_cols
131 WHERE table_name IN (
132 SELECT r.source_table_name
133 FROM bsc_db_tables_rels r, bsc_db_tables t
134 WHERE r.table_name LIKE :1 AND
135 NOT r.source_table_name LIKE :2 AND
136 r.table_name = t.table_name AND
137 t.periodicity_id = :3 AND
138 r.relation_type = 0 ) AND
139 column_name IN (' || lstKeys || ') AND
140 column_type = ''P''
141 GROUP BY table_name
142 HAVING COUNT(column_name) = ' || KpiTable.keys.Count;
143 END IF;
144 ELSE
145 --The indicator is not pre-calculated
146 If KpiTable.IsTargetTable Then
147 TableName := 'BSC_SB_' || KpiTable.Indicator || '_' || KpiTable.Configuration || '%';
148 Else
149 TableName := 'BSC_S_' || KpiTable.Indicator || '_' || KpiTable.Configuration || '%';
150 End If;
151 l_stmt := 'SELECT DISTINCT r.source_table_name
152 FROM bsc_db_tables_rels r, bsc_db_tables t
153 WHERE r.table_name LIKE :1 AND
154 NOT r.source_table_name LIKE :2 AND
155 r.table_name = t.table_name AND
156 t.periodicity_id = :3 AND r.relation_type = 0';
157 End If;
158
159 OPEN cv FOR l_stmt using TableName,TableName, KpiTable.Periodicity;
160 LOOP
161 FETCH cv INTO SourceTableName;
162 EXIT WHEN cv%NOTFOUND;
163 --Add the table to the collection of origin tables of the table
164 IF (KpiTable.originTable IS NOT NULL) THEN
165 KpiTable.originTable := KpiTable.originTable ||',';
166 END IF;
167 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
168 bsc_mo_helper_pkg.writeTmp('1. Adding Origin table for '||KpiTable.name||' = '||SourceTableName);
169 END IF;
170 KpiTable.originTable := KpiTable.originTable ||SourceTableName;
171 --Add the table to the gloabel array garrTablesUpgrade
172 If Not bsc_mo_helper_pkg.searchStringExists(
173 bsc_metadata_optimizer_pkg.garrTablesUpgradeT,
174 bsc_metadata_optimizer_pkg.gnumTablesUpgradeT,
175 SourceTableName) Then
176 bsc_metadata_optimizer_pkg.garrTablesUpgradeT(bsc_metadata_optimizer_pkg.gnumTablesUpgradeT) := SourceTableName;
177 bsc_metadata_optimizer_pkg.gnumTablesUpgradeT := bsc_metadata_optimizer_pkg.gnumTablesUpgradeT + 1;
178 End If;
179 END LOOP;
180 Close cv;
181 --Bug#3340878 Need to set the source key column of the table
182 --Get the name of the table previous to the upgrade. Remember that the name of
183 --the table was changed from BSC_S_3001_0_12345_5 to BSC_S_3001_0_0_5
184 OPEN cTable(SourceTableName, TableName);
185 FETCH cTable INTO KpiTableName;
186 Close cTable;
187 l_counter := KpiTable.keys.first;
188 LOOP
189 EXIT WHEN KpiTable.keys.count = 0;
190 l_key := KpiTable.keys(l_counter);
191 OPEN cColumns (KpiTableName, UPPER(l_key.keyName), 'P');
192 FETCH cColumns INTO l_key.origin;
193 close cColumns;
194 KpiTable.keys(l_counter) := l_key ;
195 EXIT WHEN l_counter = KpiTable.keys.last;
196 l_counter := KpiTable.keys.next(l_counter);
197 END LOOP;
198 EXCEPTION WHEN OTHERS THEN
199 l_error := sqlerrm;
200 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in set_origin_table_from_db : '||l_error);
201 raise;
202 End ;
203
204 PROCEDURE load_upgrade_tables_db IS
205 i NUMBEr;
206 l_stmt VARCHAR2(4000);
207 l_table bsc_metadata_optimizer_pkg.clsTable;
208 l_key bsc_metadata_optimizer_pkg.clsKeyField;
209 l_measure bsc_metadata_optimizer_pkg.clsDataField;
210 TablaOri VARCHAR2(4000);
211 Target_Flag NUMBER;
212 cv CurTyp;
213 New_clsKeyField bsc_metadata_optimizer_pkg.clsKeyField;
214 New_clsDataField bsc_metadata_optimizer_pkg.clsDataField;
215 CURSOR cTable (pTable IN VARCHAR2) IS
216 SELECT TABLE_NAME, TABLE_TYPE, PERIODICITY_ID, EDW_FLAG, CURRENT_PERIOD, TARGET_FLAG
217 FROM BSC_DB_TABLES
218 WHERE TABLE_NAME = pTable;
219 cTableRow cTable%ROWTYPE;
220
221 CURSOR cCols (pTable IN VARCHAR2) IS
222 SELECT COLUMN_TYPE, COLUMN_NAME, SOURCE_COLUMN, SOURCE_FORMULA
223 --BSC Autogen
224 , SOURCE
225 , measure_group_id
226 FROM BSC_DB_TABLES_COLS cols, bsc_db_measure_cols_vl dbcols
227 WHERE cols.TABLE_NAME = pTable
228 AND cols.column_name = dbcols.measure_col(+);
229 cColsRow cCols%ROWTYPE;
230
231 CURSOR cTableRels0(pTable IN VARCHAR2) IS
232 SELECT SOURCE_TABLE_NAME
233 FROM BSC_DB_TABLES_RELS
234 WHERE TABLE_NAME = pTable
235 AND RELATION_TYPE = 0
236 ORDER BY SOURCE_TABLE_NAME;
237 cTableRels0Row cTableRels0%ROWTYPE;
238
239 CURSOR cTableRels1(pTable IN VARCHAR2) IS
240 SELECT SOURCE_TABLE_NAME
241 FROM BSC_DB_TABLES_RELS
242 WHERE TABLE_NAME = pTable
243 AND RELATION_TYPE = 1
244 ORDER BY SOURCE_TABLE_NAME;
245 cTableRels1Row cTableRels1%ROWTYPE;
246 l_error VARCHAR2(1000);
247 l_stack varchar2(32000);
248 l_table_null bsc_metadata_optimizer_pkg.clsTable;
249 BEGIN
250
251 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
252 BSC_MO_HELPER_PKG.writeTmp('Inside load_upgrade_tables_db, upg tables = '||bsc_metadata_optimizer_pkg.gnumTablesUpgrade);
253 END IF;
254
255 For i IN 0..bsc_metadata_optimizer_pkg.gnumTablesUpgrade - 1 LOOP
256 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
257 BSC_MO_HELPER_PKG.writeTmp('');
258 BSC_MO_HELPER_PKG.writeTmp('Processing table '||bsc_metadata_optimizer_pkg.garrTablesUpgrade(i));
259 END IF;
260 OPEN cTable(bsc_metadata_optimizer_pkg.garrTablesUpgrade(i));
261 FETCH cTable INTO cTableRow;
262 l_stack := l_stack || g_newline||'Step 1';
263 If cTable%FOUND Then
264 l_table := l_table_null;
265 l_table.Name := cTableRow.TABLE_NAME;
266 l_table.Type := cTableRow.TABLE_TYPE;
267 l_table.Periodicity := cTableRow.PERIODICITY_ID;
268 l_table.EDW_Flag := cTableRow.EDW_FLAG;
269 l_stack := l_stack || g_newline||'Step 1.1';
270 If cTableRow.TARGET_FLAG = 1 Then
271 l_table.IsTargetTable := True;
272 Else
273 l_table.IsTargetTable := False;
274 End If;
275 l_stack := l_stack || g_newline||'Step 1.2';
276 l_table.currentPeriod := cTableRow.CURRENT_PERIOD;
277 l_stack := l_stack || g_newline||'Step 2';
278 OPEN cCols(UPPER(l_table.name));
279 l_stack := l_stack || g_newline||'Step 2.1';
280 LOOP
281 l_stack := l_stack || g_newline||'Step 2.2';
282 FETCH cCols INTO cColsRow;
283 EXIT WHEN cCols%NOTFOUND;
284 l_stack := l_stack || g_newline||'Step 2.3';
285 If UPPER(cColsRow.COLUMN_TYPE) = 'P' Then
286 --Key column
287 l_stack := l_stack || g_newline||'Step 2.4';
288 l_key := New_clsKeyField;
289 l_key.keyName := cColsRow.COLUMN_NAME;
290 l_key.Origin := cColsRow.SOURCE_COLUMN;
291 l_key.NeedsCode0 := False;
292 l_key.CalculateCode0 := False;
293 l_key.FilterViewName := null;
294 l_table.keys(l_table.keys.count) := l_key;
295 l_stack := l_stack || g_newline||'Step 2.5';
296 Else
297 --Data column
298 l_stack := l_stack || g_newline||'Step 2.6';
299 l_measure := New_clsDataField;
300 l_measure.fieldName := cColsRow.COLUMN_NAME;
301 l_measure.source := cColsRow.source;
302 l_measure.measureGroup := cColsRow.measure_group_id;
303 l_measure.Origin := cColsRow.SOURCE_FORMULA;
304 l_measure.AggFunction := null;
305 l_table.data(l_table.data.count) := l_measure;
306 l_stack := l_stack || g_newline||'Step 2.7';
307 End If;
308 END Loop;
309 Close cCols;
310 l_stack := l_stack || g_newline||'Step 3';
311 --Source tables (Hard Relations)
312 OPEN cTableRels0 (UPPER(l_table.name));
313 l_stack := l_stack || g_newline||'Step 3.1';
314 LOOP
315 FETCH cTableRels0 INTO cTableRels0Row;
316 l_stack := l_stack || g_newline||'Step 3.2';
317 EXIT WHEN cTableRels0%NOTFOUND;
318 l_stack := l_stack || g_newline||'Step 3.3';
319 IF (l_table.originTable IS NOT NULL) THEN
320 l_table.originTable := l_table.originTable ||',';
321 END IF;
322 l_stack := l_stack || g_newline||'Step 3.4';
323 l_table.originTable := l_table.originTable || cTableRels0Row.SOURCE_TABLE_NAME;
324 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
325 bsc_mo_helper_pkg.writeTmp('Adding Origin table '||cTableRels0Row.SOURCE_TABLE_NAME);
326 END IF;
327 END LOOP;
328 Close cTableRels0;
329 l_stack := l_stack || g_newline||'Step 4';
330 --Source table (Soft Relations)
331 OPEN cTableRels1(UPPER(l_table.Name));
332 l_stack := l_stack || g_newline||'Step 4.1';
333 LOOP
334 FETCH cTableRels1 INTO cTableRels1Row;
335 l_stack := l_stack || g_newline||'Step 4.2';
336 EXIT WHEN cTableRels1%NOTFOUND;
337 l_stack := l_stack || g_newline||'Step 4.3';
338 IF (l_table.originTable IS NOT NULL) THEN
339 l_table.originTable := l_table.originTable ||',';
340 END IF;
341 l_stack := l_stack || g_newline||'Step 4.4';
342 l_stack := l_stack || g_newline||'Adding Origin table '||cTableRels1Row.SOURCE_TABLE_NAME;
343 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
344 bsc_mo_helper_pkg.writeTmp('Adding Origin table '||cTableRels1Row.SOURCE_TABLE_NAME);
345 END IF;
346 l_table.originTable := l_table.originTable || cTableRels1Row.SOURCE_TABLE_NAME;
347 END LOOP;
348 Close cTableRels1;
349 --The tables in garrTablesUpgrade() are not used direclty by any indicator
350 l_table.Indicator := 0;
351 l_table.Configuration := 0;
352 l_table.upgradeFlag := 1;
353 --Add table to collection
354 BSC_MO_HELPER_PKG.addTable(l_table, l_table.keys, l_table.data, 'load_upgrade_tables_db');
355 --l_table_keys.delete;
356 --l_table_measures.Delete;
357 l_table := BSC_MO_HELPER_PKG.new_clsTable;
358 End If;
359 Close cTable;
360 IF (length(l_stack) > 30000) THEN
361 l_stack := null;
362 END IF;
363 END LOOP;
364 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
365 BSC_MO_HELPER_PKG.writeTmp('Completed load_upgrade_tables_db');
366 END IF;
367 EXCEPTION WHEN OTHERS THEN
368 l_error := sqlerrm;
369 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in load_upgrade_tables_db : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
370 BSC_MO_HELPER_PKG.writeTmp('l_stack ='||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
371 raise;
372 End ;
373
374
375 --****************************************************************************
376 -- TodasDesagsRegistradas
377 --
378 -- DESCRIPTION:
379 -- Returns TRUE if all the dissagregations in the given collection
380 -- have been registered
381 --
382 -- PARAMETERS:
383 -- p_key_combinations: dissagregations
384 --
385 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
386 --****************************************************************************
387 Function all_key_comb_registered(p_key_combinations BSC_METADATA_OPTIMIZER_PKG.tab_clsDisaggField) return Boolean IS
388 key_combination BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
389 l_index NUMBER;
390 l_error VARCHAR2(1000);
391 BEGIN
392 IF (p_key_combinations.count=0) THEN
393 return true;
394 END IF;
395 l_index := p_key_combinations.first;
396 LOOP
397 key_combination := p_key_combinations(l_index);
398 If Not key_combination.Registered Then
399 return false;
400 End If;
401 EXIT WHEN l_index = p_key_combinations.last;
402 l_index := p_key_combinations.next(l_index);
403 END LOOP;
404 return true;
405 EXCEPTION WHEN OTHERS THEN
406 l_error := sqlerrm;
407 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in all_key_comb_registered : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
408 raise;
409 End;
410
411
412 --****************************************************************************
413 -- TableOriginExists
414 --
415 -- DESCRIPTION:
416 -- Return TRUE if the table exist in the collection. The collection
417 -- is of objects of type clsTablaOri
418 --
419 -- PARAMETERS:
420 -- p_table_origins: collection
421 -- p_table_name: table name
422 --
423 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
424 --****************************************************************************
425 Function TableOriginExists(p_table_origins IN VARCHAR2,
426 p_table_name IN VARCHAR2) RETURN Boolean IS
427 l_table VARCHAR2(100);
428 l_count1 NUMBER;
429 l_origins DBMS_SQL.VARCHAR2_TABLE;
430 l_error VARCHAR2(1000);
431 BEGIN
432 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
433 BSC_MO_HELPER_PKG.writeTmp('Inside TableOriginExists, key_combinations = ', FND_LOG.LEVEL_PROCEDURE);
434 BSC_MO_HELPER_PKG.writeTmp(' Parameter p_table_name='||p_table_name
435 ||', and p_table_origins is '||p_table_origins, FND_LOG.LEVEL_STATEMENT);
436 END IF;
437 l_origins := BSC_MO_HELPER_PKG.getDecomposedString(p_table_origins, ',');
438 IF (l_origins.count = 0) THEN
439 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
440 BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning false', FND_LOG.LEVEL_PROCEDURE);
441 END IF;
442 return false;
443 END IF;
444 l_count1 := l_origins.first;
445 LOOP
446 l_table := l_origins(l_count1);
447 If UPPER(l_table) = UPPER(p_table_name) Then
448 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
449 BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning true', FND_LOG.LEVEL_PROCEDURE);
450 END IF;
451 return true;
452 End If;
453 EXIT WHEN l_count1= l_origins.last;
454 l_count1 := l_origins.next(l_count1);
455 END LOOP;
456 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
457 BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning false', FND_LOG.LEVEL_PROCEDURE);
458 END IF;
459 return false;
460 EXCEPTION WHEN OTHERS THEN
461 l_error := sqlerrm;
462 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in TableOriginExists : '||l_error);
463 raise;
464 End ;
465 --****************************************************************************
466 -- IndexTablaTemporalMismaDesagyCodAgrupCampo : get_matching_tables
467 --
468 -- DESCRIPTION:
469 -- Returns the index on collection p_BTTables whose table
470 -- has the given periodicity and the given key columns and the first
471 -- data field of the temporal table has the given grouping code.
472 -- Returns -1 if it is not found.
473 --
474 -- PARAMETERS:
475 -- pPeriodicity: periodicity
476 -- keyCols: key columns
477 -- dataFieldGroup: grouping code of the data fields
478 --
479 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
480 --****************************************************************************
481 Function get_matching_tables(
482 p_measure_col IN VARCHAR2,
483 --BSC Autogen
484 p_measure_source IN VARCHAR2,
485 pPeriodicity IN NUMBER,
486 p_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
487 dataFieldGroup IN NUMBER,
488 p_impl_type IN NUMBER,
489 p_BTTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_ClsTable) RETURN DBMS_SQL.NUMBER_TABLE IS
490 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
491 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
492 keysEqual Boolean;
493 i Integer;
494 toBeConsidered Boolean;
495 l_return NUMBER := 0;
496 l_index1 NUMBER;
497 l_index2 NUMBER;
498 l_temp NUMBER;
499 l_error VARCHAR2(1000);
500 l_return_table DBMS_SQL.NUMBER_TABLE ;
501 BEGIN
502 IF (p_BTTables.count = 0) THEN
503 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
504 BSC_MO_HELPER_PKG.writeTmp('Done with get_matching_tables, returning -1', FND_LOG.LEVEL_PROCEDURE);
505 END IF;
506 return l_return_table;
507 END IF;
508
509 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
510 BSC_MO_HELPER_PKG.writeTmp('Within get_matching_tables, measure='||p_measure_col||', source='||p_measure_source||', Periodicity = '||pPeriodicity||', dataFieldGroup ='||dataFieldGroup||', impl_type='||p_impl_type);
511 END IF;
512 i := p_BTTables.first;
513 LOOP
514 toBeConsidered := True;
515
516 If p_BTTables(i).Data.Count > 0 Then
517 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov,
518 p_BTTables(i).Data(0).fieldName,
519 p_BTTables(i).Data(0).source);
520 If BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode <> dataFieldGroup
521 -- BSC AW
522 OR (p_BTTables(i).impl_type <> p_impl_type)
523 --BSC Autogen
524 -- If new table(not production table), column shouldnt already exist
525 OR (p_BTTables(i).isProductionTable=false AND BSC_MO_INDICATOR_PKG.DataFieldExists(p_BTTables(i).Data, p_measure_col) )
526 Then
527 toBeConsidered := False;
528 End If;
529 Else
530 toBeConsidered := false;
531 End If;
532 If toBeConsidered Then
533 If p_BTTables(i).Periodicity = pPeriodicity Then
534 If p_BTTables(i).keys.Count = p_keys.Count Then
535 keysEqual := True;
536 l_index1 := p_BTTables(i).keys.first;
537 LOOP
538 EXIT WHEN p_BTTables(i).keys.count=0;
539 l_key := p_BTTables(i).keys(l_index1);
540 If Not BSC_MO_INDICATOR_PKG.keyFieldExists(p_keys, l_key.keyName) Then
541 keysEqual := False;
542 Exit ;
543 Else
544 l_temp := BSC_MO_HELPER_PKG.findIndex(p_keys, l_key.keyName);
545 If UPPER(l_key.FilterViewName) <> UPPER(p_keys(l_temp).FilterViewName) Then
546 keysEqual := False;
547 Exit ;
548 End If;
549 End If;
550 EXIT WHEN l_index1 = p_BTTables(i).keys.last;
551 l_index1 := p_BTTables(i).keys.next(l_index1);
552 END LOOP;
553 If keysEqual Then -- see if this is a production table, and if so, if the column exists
554 IF (p_BTTables(i).isProductionTable) THEN
555 -- we can add a production table as a match ONLY if the column NAME doesnt exist
556 IF BSC_MO_INDICATOR_PKG.DataFieldExists(p_BTTables(i).Data, p_measure_col)=true AND
557 BSC_MO_INDICATOR_PKG.DataFieldExistsforSource(p_BTTables(i).Data, p_measure_col, p_measure_source)=false THEN
558 null;
559 ELSE
560 l_return_table(l_return_table.count) := i;
561 bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
562 END IF;
563 ELSE
564 l_return_table(l_return_table.count) := i;
565 bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
566 END IF;
567 End If;
568 End If;
569 End If;
570 End If;
571 EXIT WHEN i = p_BTTables.last;
572 i := p_BTTables.next(i);
573 END LOOP;
574 return l_return_table;
575 EXCEPTION WHEN OTHERS THEN
576 l_error := sqlerrm;
577 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_matching_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
578 IF (l_temp=-1) THEN
579 BSC_MO_HELPER_PKG.writeTmp('Measure '||p_BTTables(i).Data(0).fieldName||', source=' ||p_BTTables(i).Data(0).source||' does not exist in the list of measures in bsc_sys_measures.', FND_LOG.LEVEL_STATEMENT, true);
580 END IF;
581 raise;
582 End ;
583
584 --****************************************************************************
585 -- getMaxTableIndex : MaximoIndiceTablas
586 --
587 -- DESCRIPTION:
588 -- Look for tables whose name start with the given word in BSC_DB_TABLES
589 -- From those tables whose name end with a number, this function return
590 -- the maximun number.
591 -- Example. startsWith = 'T_'
592 -- In BSC_DB_TABLES exists the following tables that start with 'T_':
593 -- 'T_1', 'T_A', 'T_2' and 'T_3'. This function return 3
594 --
595 -- PARAMETERS:
596 -- startsWith: Word by which the table name start.
597 --
598 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
599 --****************************************************************************
600 Function getMaxTableIndex(startsWith IN VARCHAR2) return NUMBER IS
601 l_max NUMBER := 0;
602 cv CurTyp;
603 l_startsWith VARCHAR2(100);
604 cursor cTables(pStart IN VARCHAR2) IS
605 SELECT table_name from bsc_db_tables
606 where table_name like pStart;
607
608
609 l_str_len number := 0;
610
611 l_error VARCHAR2(2000);
612 l_table varchar2(100);
613 BEGIN
614
615 l_startsWith := startsWith;
616 l_max := 0;
617 IF (instr(l_startsWith, '%') = 0) THEN
618 l_startsWith := l_startsWith||'%';
619 l_str_len := length(startsWith) ;
620 ELSE
621 l_str_len := length(startsWith)-1 ;
622 END IF;
623
624
625 OPEN cTables(l_startsWith);
626 LOOP
627 FETCH cTables INTO l_table;
628 EXIT WHEN CTables%NOTFOUND;
629 l_table := substr(l_table, l_str_len +1);
630
631 BEGIN
632 If to_number(l_table) > l_max Then
633 l_max := to_number(l_table);
634 End If;
635 EXCEPTION WHEN OTHERS THEN -- not a number
636 null;
637 END ;
638 END Loop;
639 close cTables;
640 IF (l_max IS NULL) THEN
641 l_max := 0;
642 END IF;
643
644 return l_max;
645
646 EXCEPTION WHEN OTHERS THEN
647 l_error := sqlerrm;
648 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in getMaxTableIndex : '||l_error);
649 raise;
650
651 End;
652
653
654
655 --****************************************************************************
656 -- HacerTablasEyConectarATablasBasicas : connect_i_to_b_tables
657 --
658 -- DESCRIPTION:
659 -- Make input tables and connect to the base tables
660 --
661 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
662 --****************************************************************************
663 PROCEDURE connect_i_to_b_tables IS
664 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
665 TablaE BSC_METADATA_OPTIMIZER_PKG.clsTable;
666
667 Tabla_Origin DBMS_SQL.VARCHAR2_TABLE;
668
669 TablaE_Origin DBMS_SQL.VARCHAR2_TABLE;
670
671 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
672 l_keyE BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
673 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
674 l_measureE BSC_METADATA_OPTIMIZER_PKG.clsDataField;
675
676 l_table_origin VARCHAR2(100);
677 l_index1 NUMBER;
678 l_index2 NUMBER;
679
680 l_error VARCHAR2(1000);
681 l_table_null BSC_METADATA_OPTIMIZER_PKG.clsTable;
682 BEGIN
683 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
684 BSC_MO_HELPER_PKG.writeTmp('Inside connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
685 END IF;
686 IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
687 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
688 BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
689 END IF;
690 return;
691 END IF;
692 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
693 LOOP
694 l_table := l_Table_null;
695 l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
696 IF (l_table.isProductionTable) THEN
697 goto ignore;
698 END IF;
699 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
700 BSC_MO_HELPER_PKG.writeTmp('');
701 BSC_MO_HELPER_PKG.writeTmp('Processing table gTables('||l_index1||
702 ') : '||l_table.name, FND_LOG.LEVEL_STATEMENT);
703 END IF;
704 Tabla_Origin.delete;
705 Tabla_Origin := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
706 --For tables with no origin (base tables)
707 If Tabla_origin.Count = 0 And l_table.Type <> 0 Then
708 TablaE := bsc_mo_helper_pkg.new_clsTable;
709 TablaE.Name := 'BSC_I_' ||( BSC_METADATA_OPTIMIZER_PKG.gMaxI + 1);
710 BSC_METADATA_OPTIMIZER_PKG.gMaxI := BSC_METADATA_OPTIMIZER_PKG.gMaxI + 1;
711 TablaE.Type := 0;
712 TablaE.Periodicity := l_table.Periodicity;
713 TablaE.EDW_Flag := l_table.EDW_Flag;
714 TablaE.IsTargetTable := l_table.IsTargetTable;
715 TablaE.impl_type := l_table.impl_type;
716
717 --Key columns
718 IF (l_table.keys.count>0)THEN
719 l_index2 := l_table.keys.first;
720 LOOP
721 l_keyE := bsc_mo_helper_pkg.new_clskeyfield;
722 l_keyE := l_table.keys(l_index2);
723 TablaE.keys(TablaE.keys.count) := l_keyE;
724 l_table.keys(L_INDEX2).Origin := l_table.keys(L_INDEX2).keyName;
725 EXIT WHEN l_index2 = l_table.keys.last;
726 l_index2 := l_table.keys.next(l_index2);
727 END LOOP;
728 --l_table.keys := l_table_keys;
729 END IF;
730 --Data columns
731 IF (l_table.data.count>0) THEN
732 l_index2 := l_table.data.first;
733 LOOP
734 l_measureE := bsc_mo_helper_pkg.new_clsDataField;
735 l_measure := l_table.data(l_index2);
736 l_measureE.fieldName := l_measure.fieldName;
737 l_measureE.source := l_measure.source;
738 l_measureE.measureGroup := l_measure.measureGroup;
739 l_measureE.aggFunction := l_measure.aggFunction;
740 --Note: other properties for internal columns are not used in input tables
741 TablaE.Data(TablaE.Data.count) := l_measureE;
742 EXIT WHEN l_index2 = l_table.data.last;
743 l_index2 := l_table.data.next(l_index2);
744 END LOOP;
745 END IF;
746 BSC_MO_HELPER_PKG.addTable(TablaE, TablaE.keys, TablaE.Data, 'connect_i_to_b_tables');
747 l_table_origin := TablaE.Name;
748 IF (l_table.originTable IS NOT NULL ) THEN
749 l_table.originTable := l_table.originTable||',';
750 END IF;
751 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
752 bsc_mo_helper_pkg.writeTmp('2. Adding Origin table for '||l_table.name||' = '||l_table_origin);
753 END IF;
754 l_table.originTable := l_table.originTable||l_table_origin;
755 End If;
756 BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
757 <<ignore>>
758 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
759 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
760 END LOOP;
761 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
762 BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
763 END IF;
764 EXCEPTION WHEN OTHERS THEN
765 l_error := sqlerrm;
766 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_i_to_b_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
767 raise;
768 End;
769 --****************************************************************************
770 -- add_to_gtables: AdicTablasTempATablasSistema
771 --
772 -- DESCRIPTION:
773 -- Add each table of the collection g_bt_tables to the collection
774 -- gTablas.
775 --
776 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
777 --****************************************************************************
778 PROCEDURE add_to_gtables(p_tables IN BSC_METADATA_OPTIMIZER_PKG.tab_clsTable) IS
779 TablaTemp BSC_METADATA_OPTIMIZER_PKG.clsTable;
780 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
781 l_keyTemp BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
782 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
783 l_measureTemp BSC_METADATA_OPTIMIZER_PKG.clsDataField;
784 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
785 l_index1 NUMBER;
786 l_index2 NUMBER;
787
788 l_error VARCHAR2(1000);
789 BEGIN
790
791 IF (p_tables.count =0) THEN
792 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
793 BSC_MO_HELPER_PKG.writeTmp('Done with add_to_gtables, p_tables.count=0',
794 FND_LOG.LEVEL_PROCEDURE);
795 END IF;
796 return;
797 END IF;
798 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
799 BSC_MO_HELPER_PKG.writeTmp('Inside add_to_gtables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
800 END IF;
801 l_index1 := p_tables.first;
802 LOOP
803 BSC_MO_HELPER_PKG.addTable(p_tables(l_index1), 'add_to_gtables');
804 EXIT WHEN l_index1 = p_tables.last;
805 l_index1 := p_tables.next(l_index1);
806 END LOOP;
807 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
808 BSC_MO_HELPER_PKG.writeTmp('Completed add_to_gtables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
809 END IF;
810 EXCEPTION WHEN OTHERS THEN
811 l_error := sqlerrm;
812 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_gtables : '||l_error);
813 raise;
814 End;
815
816
817 --****************************************************************************
818 -- get_origin_table: GetNombreTablaTempOri
819 --
820 -- DESCRIPTION:
821 -- Return the name of the tempral table where the given field is found,
822 -- for the specified dissagregation and periodicity.
823 -- It look in the collection p_tables.
824 -- Note: By design, we know that it will be found.
825 --
826 -- PARAMETERS:
827 -- pPeriodicity: periodicity
828 -- Llaves: dissagregation
829 -- NombreCampo: field name
830 --
831 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
832 --****************************************************************************
833 Function get_origin_table(
834 pPeriodicity IN NUMBER,
835 keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
836 fieldName IN VARCHAR2,
837 p_source IN VARCHAR2,
838 p_measure_group IN OUT NOCOPY NUMBER,
839 p_impl_type IN NUMBER,
840 p_tables IN BSC_METADATA_OPTIMIZER_PKG.tab_clsTable) return VARCHAR2 IS
841 L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
842 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
843 keysEqual Boolean;
844 l_return VARCHAR2(300);
845 l_index1 NUMBER;
846 l_index2 NUMBER;
847 l_index3 NUMBER;
848 l_measure_index NUMBER;
849 l_start_time date := sysdate;
850 l_error VARCHAR2(1000);
851 l_temp number;
852 l_measure_group number;
853 BEGIN
854 IF (p_tables.count=0) THEN
855 return null;
856 END IF;
857 if (p_measure_group is null) then
858 p_measure_group := get_measure_group(fieldName, p_source);
859 end if;
860 l_measure_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, fieldName, p_source);
861 l_measure_group := BSC_METADATA_OPTIMIZER_PKG.gLov(l_measure_index).groupCode;
862 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
863 BSC_MO_HELPER_PKG.writeTmp('Inside get_origin_table, pPeriodicity='||pPeriodicity
864 ||', fieldName ='||fieldName||', source='||p_source||', measure_group='||p_measure_group||', p_impl_type='||p_impl_type||', p_tables.count = '||
865 p_tables.count, FND_LOG.LEVEL_PROCEDURE);
866 END IF;
867 l_index1 := p_tables.first;
868
869 LOOP
870 L_Table := p_tables(l_index1);
871 IF BSC_MO_INDICATOR_PKG.DataFieldExistsForSource(L_Table.Data, fieldName, p_source) AND
872 L_Table.impl_type = p_impl_type AND
873 nvl(l_table.measureGroup, -1) = nvl(p_measure_group, -1) THEN
874 If L_Table.Periodicity = pPeriodicity THEN
875 If L_Table.keys.Count = keys.Count THEN
876 keysEqual := True;
877 l_index2 := L_Table.keys.first;
878 LOOP
879 EXIT WHEN L_Table.keys.count = 0;
880 l_key := L_Table.keys(l_index2);
881 If Not BSC_MO_INDICATOR_PKG.keyFieldExists(keys, l_key.keyName) Then
882 keysEqual := False;
883 Else
884 l_temp := BSC_MO_HELPER_PKG.findIndex(keys, l_key.keyName);
885 If UPPER(l_key.FilterViewName) <> UPPER(keys(l_temp).FilterViewName) Then
886 keysEqual := False;
887 Exit ;
888 End If;
889 End If;
890 EXIT WHEN l_index2 = L_Table.keys.last;
891 l_index2 := L_Table.keys.next(l_index2);
892 END LOOP;
893 If keysEqual Then
894 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
895 BSC_MO_HELPER_PKG.writeTmp('Compl get_origin_table, returning '||
896 L_Table.Name, FND_LOG.LEVEL_PROCEDURE);
897 BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||
898 (sysdate-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
899 END IF;
900 return L_Table.Name;
901 End If;
902 End If;
903 End If;
904 End If;
905 EXIT WHEN l_index1 = p_tables.last;
906 l_index1 := p_tables.next(l_index1);
907 END LOOP;
908 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
909 BSC_MO_HELPER_PKG.writeTmp('Compl get_origin_table, returning null', FND_LOG.LEVEL_PROCEDURE);
910 END IF;
911 return null;
912
913 EXCEPTION WHEN OTHERS THEN
914 l_error := sqlerrm;
915 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_origin_table : '||l_error);
916 raise;
917 End ;
918 --****************************************************************************
919 -- ConectarTablasIndicadoresConTemporales : connect_s_to_b_tables
920 --
921 -- DESCRIPTION:
922 -- Connect the base tables of the indicators with the temporal tables.
923 --
924 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
925 --****************************************************************************
926 PROCEDURE connect_s_to_b_tables IS
927 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
928 l_table_origin VARCHAR2(100);
929 Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
930 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
931 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
932 l_origin_table VARCHAR2(300);
933 l_index1 NUMBER;
934 l_index2 NUMBER;
935 l_index3 NUMBER;
936 l_temp NUMBER;
937
938 l_start_time date := sysdate;
939 l_end date;
940 l_error VARCHAR2(1000);
941
942 BEGIN
943 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
944 BSC_MO_HELPER_PKG.writeTmp('Inside connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
945 BSC_MO_HELPER_PKG.writeTmp('System time is '||to_char(sysdate, 'hh24:mi:ss'), FND_LOG.LEVEL_STATEMENT);
946 END IF;
947 IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
948 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
949 BSC_MO_HELPER_PKG.writeTmp('Compl connect_s_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
950 END IF;
951 return;
952 END IF;
953 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
954 LOOP
955 l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
956 Tabla_originTable.delete;
957 Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
958 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
959 BSC_MO_HELPER_PKG.writeTmp('');
960 BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961 l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
962 FND_LOG.LEVEL_STATEMENT);
963 BSC_MO_HELPER_PKG.writeTmp('------------------------------------------------');
964 END IF;
965 --Only consider tables with no origin
966 If Tabla_OriginTable.Count = 0 Then
967 --Key columns
968 --For each key assign the origin key name with the same key name
969 IF l_table.keys.count >0 THEN
970 FOR l_index2 IN l_table.keys.first..l_table.keys.last LOOP
971 l_table.keys(l_index2).Origin := l_table.keys(l_index2).keyName;
972 END LOOP;
973 END IF;
974 IF (l_table.data.count>0) THEN
975 FOR l_index2 IN l_table.data.first..l_table.data.last LOOP
976 l_measure := l_table.data(l_index2);
977 If l_measure.InternalColumnType = 0 Then
978 --Do not see internal columns. They are not in base or temporal tables
979 l_temp := BSC_MO_HELPER_PKG.findIndex( BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_table.Indicator);
980 If BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).OptimizationMode <> 0 Then
981 --non pre-calculated
982 If l_table.IsTargetTable Then
983 l_origin_table := get_origin_table(
984 l_table.Periodicity,
985 l_table.keys,
986 l_measure.fieldName,
987 l_measure.source,
988 l_measure.measureGroup,
989 l_table.impl_type,
990 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
991 Else
992 l_origin_table := get_origin_table(
993 l_table.Periodicity,
994 l_table.keys,
995 l_measure.fieldName,
996 l_measure.source,
997 l_measure.measureGroup,
998 l_table.impl_type,
999 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
1000 End If;
1001 Else
1002 --pre-calculated
1003 l_origin_table := get_origin_table(
1004 l_table.Periodicity,
1005 l_table.keys,
1006 l_measure.fieldName,
1007 l_measure.source,
1008 l_measure.MeasureGroup,
1009 l_table.impl_type,
1010 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
1011 End If;
1012 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1013 BSC_MO_HELPER_PKG.writeTmp('10. Origin = '||l_origin_table, FND_LOG.LEVEL_STATEMENT);
1014 END IF;
1015 IF (l_origin_table is null) THEN
1016 BSC_MO_HELPER_PKG.writeTmp('ERROR:connect_s_to_b_tables: Unable to find source table for '||
1017 l_table.name||'.'||l_measure.fieldName, FND_LOG.LEVEL_EXCEPTION, true);
1018 raise bsc_metadata_optimizer_pkg.optimizer_exception;
1019 END IF;
1020 --In the Indicator tables l_measure.Origen was already set
1021 --TablasOri
1022 If Not TableOriginExists(l_table.originTable, l_origin_table) Then
1023 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1024 BSC_MO_HELPER_PKG.writeTmp('11. Table does not have source ', FND_LOG.LEVEL_STATEMENT);
1025 END IF;
1026 l_table_origin := l_origin_table;
1027 IF (l_table.originTable IS NOT NULL) THEN
1028 l_table.originTable := l_table.originTable||',';
1029 END IF;
1030 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1031 bsc_mo_helper_pkg.writeTmp('3. Adding Origin table for '||l_table.name||' = '||l_table_origin);
1032 END IF;
1033 l_table.originTable := l_table.originTable||l_table_origin;
1034 End If;
1035 End If;
1036 END LOOP;
1037 END IF;
1038 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1039 bsc_mo_helper_pkg.writeTmp('Origin count for gTables('||l_index1||') was zero, reassigning this table ');
1040 END IF;
1041 BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
1042 End If;
1043
1044 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1045 l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1046 END LOOP;
1047 l_end := sysdate;
1048 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1049 BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||(l_end-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
1050 END IF;
1051 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1052 BSC_MO_HELPER_PKG.writeTmp('Completed connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1053 END IF;
1054 EXCEPTION WHEN OTHERS THEN
1055 l_error := sqlerrm;
1056 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_s_to_b_tables : '||l_error);
1057 raise;
1058 End ;
1059
1060
1061 --****************************************************************************
1062 -- InicListaTablasTemporalesyBasicasPreCalc : deduce_bt_tables_precalc
1063 --
1064 -- DESCRIPTION:
1065 -- Initialize the collection g_bt_tables
1066 --
1067 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1068 --****************************************************************************
1069 PROCEDURE deduce_bt_tables_precalc IS
1070 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1071 uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
1072 l_key_combination BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
1073 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1074 iMatchingTableIndex DBMS_SQL.number_table;
1075 l_datafield BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1076 needNewTable Boolean;
1077 l_index1 NUMBER;
1078 l_index2 NUMBER;
1079 l_index3 NUMBER;
1080 l_temp NUMBER;
1081
1082 --l_key_combination_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1083 l_error VARCHAR2(1000);
1084 l_loop_Ctr NUMBER;
1085 BEGIN
1086
1087 IF ( BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count=0) THEN
1088 return;
1089 END IF;
1090 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1091 BSC_MO_HELPER_PKG.writeTmp(g_newline||g_newline||g_newline);
1092 BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables_precalc, # of precalc measures = '||BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||', system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1093 END IF;
1094 l_index1 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.first;
1095
1096 LOOP
1097 uniqueField := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_index1);
1098 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1099 BSC_MO_HELPER_PKG.writeTmp('Looping for pre calc field = '|| uniqueField.fieldName||', has '||
1100 uniqueField.key_combinations.count||' disaggs ');
1101 bsc_mo_helper_pkg.write_this(g_unique, uniqueField);
1102 END IF;
1103 --Until all disagregations of the unique field are registered
1104 IF NOT all_key_comb_registered(uniqueField.key_combinations) THEN
1105 FOR l_index2 IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1106 LOOP
1107 l_key_combination := uniqueField.key_combinations(l_index2);
1108 bsc_mo_helper_pkg.writeTmp('Processing key combination '||l_index2);
1109 IF (l_key_combination.registered=false) THEN
1110 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, uniqueField.fieldName, uniqueField.source);
1111 iMatchingTableIndex := get_matching_tables(
1112 uniqueField.fieldName,
1113 uniqueField.source,
1114 l_key_combination.Periodicity,
1115 l_key_combination.keys,
1116 BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode,
1117 uniqueField.impl_type,
1118 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
1119 If iMatchingTableIndex.count = 0 Then
1120 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1121 BSC_MO_HELPER_PKG.writeTmp(' No existing temporal table with the same periodicity and disagregation');
1122 END IF;
1123 --There was not found an existing temporal table with the same periodicity and disagregation
1124 --and filter and whose fields can be grouped with this field
1125 l_table := bsc_mo_helper_pkg.new_clsTable;
1126 l_table.Name := 'BSC_B_'||( BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1);
1127 BSC_METADATA_OPTIMIZER_PKG.gMaxB := BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1;
1128 l_table.Type := 1;
1129 l_table.Periodicity := l_key_combination.Periodicity;
1130 l_table.EDW_Flag := uniqueField.EDW_Flag;
1131 l_table.IsTargetTable := False;
1132 l_table.impl_type := uniqueField.impl_type;
1133 l_table.keys := l_key_combination.keys;
1134 l_table.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1135 --Data columns
1136 --It is initialized with only with the this field
1137 l_datafield := bsc_mo_helper_pkg.new_clsDataField;
1138 l_datafield.fieldName := uniqueField.fieldName;
1139 l_datafield.source := uniqueField.source;
1140 l_datafield.measureGroup := l_table.measureGroup;
1141 l_datafield.aggFunction := uniqueField.aggFunction;
1142 l_datafield.Origin := l_datafield.aggFunction || '(' ||l_datafield.fieldName ||')';
1143 --Note: other properties for internal columns are not used in input, base, temporal tables
1144 l_table.data(l_table.data.count) := l_datafield;
1145 --Add the table to the collection g_bt_tables_precalc
1146 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc.count) := l_table;
1147 bsc_mo_helper_pkg.writeTmp('Adding following table to g_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT,false);
1148 bsc_mo_helper_pkg.write_this(l_table);
1149 ELSE
1150 --Add the field to the temporal table
1151 --Data columns
1152 --Add this field
1153 l_datafield := bsc_mo_helper_pkg.new_clsDataField;
1154 l_datafield.fieldName := uniqueField.fieldName;
1155 l_datafield.source := uniqueField.source;
1156 l_datafield.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1157 l_datafield.aggFunction := uniqueField.aggFunction;
1158 l_datafield.Origin := l_datafield.aggFunction || '(' || l_datafield.fieldName || ')';
1159 --Note: other properties for internal columns are not used in input, base, temporal tables
1160 IF (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(iMatchingTableIndex(0)).isProductionTable) THEN
1161 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(iMatchingTableIndex(0)).isProductionTableAltered := true;
1162 l_datafield.changeType := 'NEW';
1163 END IF;
1164 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(iMatchingTableIndex(0)).data
1165 (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(iMatchingTableIndex(0)).data.count) := l_datafield;
1166 End If;
1167 END IF; -- if not registered
1168 END LOOP;
1169 END IF;
1170 EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.last;
1171 l_index1 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.next(l_index1);
1172 END LOOP;
1173
1174 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1175 BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_precalc is ', FND_LOG.LEVEL_STATEMENT);
1176 BSC_MO_HELPER_PKG.write_THIS(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc, FND_LOG.LEVEL_STATEMENT);
1177 BSC_MO_HELPER_PKG.writeTmp('Completed deduce_bt_tables_precalc, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1178 END IF;
1179
1180 EXCEPTION WHEN OTHERS THEN
1181 l_error := sqlerrm;
1182 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in deduce_bt_tables_precalc : '||l_error);
1183 raise;
1184 End;
1185
1186 --****************************************************************************
1187 -- areDisaggsSame : SonMismasDesagregaciones
1188 --
1189 -- DESCRIPTION:
1190 -- Say if the dissagregations are the same
1191 --
1192 -- PARAMETERS:
1193 -- pPeriodicityA: periodicity A
1194 -- KeysA: Dissagregation A
1195 -- pPeriodicityB: peridiodicity B
1196 -- KeysB: Dissagregation B
1197 --
1198 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1199 --****************************************************************************
1200 Function areDisaggsSame(
1201 pPeriodicityA IN NUMBER,
1202 keysA IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
1203 pPeriodicityB IN NUMBER,
1204 keysB IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField) return Boolean IS
1205 keysEqual Boolean;
1206 l_keyA BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1207 l_index1 NUMBER ;
1208
1209 l_error VARCHAR2(4000);
1210 BEGIN
1211 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1212 BSC_MO_HELPER_PKG.writeTmp('Inside areDisaggsSame', FND_LOG.LEVEL_PROCEDURE);
1213 END IF;
1214 If pPeriodicityA = pPeriodicityB Then
1215 If keysA.Count = keysB.Count Then
1216 keysEqual := True;
1217 IF (keysA.count> 0) THEN
1218 l_index1 := keysA.first;
1219 LOOP
1220 l_keyA := keysA(l_index1);
1221 If Not BSC_MO_INDICATOR_PKG.keyFieldExists(keysB, l_keyA.keyName) Then
1222 keysEqual := False;
1223 Exit;
1224 End If;
1225 EXIT WHEN l_index1 = keysA.last;
1226 l_index1 := keysA.next(l_index1);
1227 END LOOP;
1228 END IF;
1229 If keysEqual Then
1230 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1231 BSC_MO_HELPER_PKG.writeTmp('Completed areDisaggsSame, ret true', FND_LOG.LEVEL_PROCEDURE);
1232 END IF;
1233 return true;
1234 End If;
1235 End If;
1236 End If;
1237
1238 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1239 BSC_MO_HELPER_PKG.writeTmp('Completed areDisaggsSame ret false', FND_LOG.LEVEL_PROCEDURE);
1240 END IF;
1241 return false;
1242 EXCEPTION WHEN OTHERS THEN
1243 l_error := sqlerrm;
1244 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245 BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246 BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247 BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248 BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249 BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1250 raise;
1251 End ;
1252 --****************************************************************************
1253 -- CreaLoopDesagDestOri:circular_dependency_exists
1254 --
1255 -- DESCRIPTION:
1256 -- Returns TRUE if there will be a loop when the target dissagregation
1257 -- is originated from the source dissagregation.
1258 -- Example. Desag1 --> DesagOri ---> DesagDest ---
1259 -- ^-----------------------------------
1260 --
1261 -- PARAMETERS:
1262 -- p_target_keys: Target dissagregation
1263 -- p_origin_keys: Source dissagregation
1264 -- p_key_combinations: Collection of dissagregations
1265 --
1266 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1267 --****************************************************************************
1268 Function circular_dependency_exists(
1269 p_target_keys IN NUMBER,
1270 p_origin_keys IN NUMBER,
1271 p_key_combinations BSC_METADATA_OPTIMIZER_PKG.tab_clsDisaggField) RETURN Boolean is
1272 res Boolean;
1273 l_index1 NUMBER;
1274 l_index2 NUMBER;
1275 l_error VARCHAR2(4000);
1276 begin
1277 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1278 BSC_MO_HELPER_PKG.writeTmp('Inside circular_dependency_exists for p_target_keys='||p_target_keys||', p_origin_keys='||p_origin_keys, FND_LOG.LEVEL_PROCEDURE);
1279 BSC_MO_HELPER_PKG.write_this(p_key_combinations);
1280 END IF;
1281 l_index1 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_origin_keys);
1282 l_index2 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_target_keys);
1283 If p_key_combinations(l_index1).Origin = 0 Then
1284 res := False;
1285 ElsIf p_key_combinations(l_index1).Origin = p_target_keys Then
1286 res := True;
1287 Else
1288 res := circular_dependency_exists(p_target_keys, p_key_combinations(l_index1).Origin, p_key_combinations);
1289 End If;
1290 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1291 BSC_MO_HELPER_PKG.writeTmp('Completed circular_dependency_exists, res='||
1292 bsc_mo_helper_pkg.boolean_decode(res), FND_LOG.LEVEL_PROCEDURE);
1293 END IF;
1294 return res;
1295 EXCEPTION WHEN OTHERS THEN
1296 l_error := sqlerrm;
1297 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in circular_dependency_exists, '||l_error);
1298 raise;
1299 End ;
1300
1301
1302 --****************************************************************************
1303 -- can_derive_keys: SePuedeOriginarDesag
1304 --
1305 -- DESCRIPTION:
1306 -- Say if the target dissagregation can be originated from the source
1307 -- dissagregation.
1308 -- One dissagregation can be originated from another if:
1309 -- 1. The periodicities are the same or can be originated.
1310 -- 2. Each of the keys in the target dissagregation can be originated
1311 -- from any key of the source dissagregation.
1312 -- One key can be originated from another if they are the same or
1313 -- if the target key is parent of the sopurce key.
1314 -- There could exist several changes of dissagregation but one souce key
1315 -- can originate onlyu one target key
1316 --
1317 -- PARAMETERS:
1318 -- DesagDest: Target dissagregation
1319 -- DesagOri: Source dissagregation
1320 --
1321 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1322 --****************************************************************************
1323 Function can_derive_keys(
1324 p_key_comb_target BSC_METADATA_OPTIMIZER_PKG.clsDisAggField,
1325 p_key_comb_targetCode NUMBER,
1326 p_key_comb_origin BSC_METADATA_OPTIMIZER_PKG.clsDisAggField,
1327 p_key_comb_originCode NUMBER,
1328 pTableName VARCHAR2)
1329 return Boolean IS
1330 l_key_origin BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1331 l_key_target BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1332 isDerivable Boolean;
1333 l_changed_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1334 changedDrill BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1335 l_index NUMBER;
1336 l_return boolean;
1337 l_index1 number;
1338 l_index2 number;
1339 l_per_origin DBMS_SQL.NUMBER_TABLE;
1340 l_dummy NUMBER;
1341 l_error varchar2(1000);
1342 BEGIN
1343 If p_key_comb_target.Periodicity <> p_key_comb_origin.Periodicity Then
1344 l_index := BSC_MO_HELPER_PKG.FindIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_key_comb_target.Periodicity);
1345 IF (l_index = -1) THEN -- metadata bad
1346 BSC_MO_HELPER_PKG.writeTmp('Bad Periodicities metadata for Periodicity='||p_key_comb_target.Periodicity, FND_LOG.LEVEL_EXCEPTION, true);
1347 END IF;
1348 l_per_origin := BSC_MO_HELPER_PKG.decomposeStringtoNumber(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).PeriodicityOrigin, ',' );
1349 If BSC_MO_HELPER_PKG.findIndex(l_per_origin, p_key_comb_origin.Periodicity) = -1 Then
1350 return false;
1351 End If;
1352 End If;
1353
1354 IF p_key_comb_target.keys.count >0 THEN
1355 FOR i IN p_key_comb_target.keys.first..p_key_comb_target.keys.last
1356 LOOP
1357 l_key_target := p_key_comb_target.keys(i);
1358 isDerivable := False;
1359 IF (p_key_comb_origin.keys.count>0) THEN
1360 FOR j IN p_key_comb_origin.keys.first..p_key_comb_origin.keys.last
1361 LOOP
1362 l_key_origin := null;
1363 l_key_origin := p_key_comb_origin.keys(j);
1364 If UPPER(l_key_target.keyName) = Upper(l_key_origin.keyName) Then
1365 isDerivable := True;
1366 Exit;
1367 End If;
1368 l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, l_key_origin.keyName);
1369 l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, l_key_target.keyName);
1370 IF (l_index1 = -1) THEN -- metadata bad
1371 BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_origin.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1372 END IF;
1373 IF (l_index2 = -1) THEN -- metadata bad
1374 BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_target.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1375 END IF;
1376 If BSC_MO_INDICATOR_PKG.IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
1377 BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
1378 If Not BSC_MO_INDICATOR_PKG.keyFieldExists(l_changed_levels, l_key_origin.keyName) Then
1379 isDerivable := True;
1380 changedDrill := bsc_mo_helper_pkg.new_clsKeyField;
1381 changedDrill.keyName := l_key_origin.keyName;
1382 l_changed_levels(l_changed_levels.count) := changedDrill;
1383 Exit ;
1384 End If;
1385 End If;
1386 END LOOP;
1387 END IF;
1388 If Not isDerivable Then
1389 return False;
1390 End If;
1391 END LOOP;
1392 END IF;
1393 return true;
1394
1395 EXCEPTION WHEN OTHERS THEN
1396 l_error := sqlerrm;
1397 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in can_derive_keys : '||l_error);
1398 BSC_MO_HELPER_PKG.writeTmp('p_key_comb_targetCode ='||p_key_comb_targetCode||',p_key_comb_originCode ='||p_key_comb_originCode||', pTableName='||pTableName, FND_LOG.LEVEL_EXCEPTION, true);
1399 BSC_MO_HELPER_PKG.write_this(p_key_comb_target, 1, FND_LOG.LEVEL_EXCEPTION, true);
1400 BSC_MO_HELPER_PKG.write_this(p_key_comb_origin, 1, FND_LOG.LEVEL_EXCEPTION, true);
1401 raise;
1402 End ;
1403 --****************************************************************************
1404 -- InicListaTablasTemporalesyBasicas :deduce_bt_tables
1405 --
1406 -- DESCRIPTION:
1407 -- Initialize the collection g_bt_tables/g_bt_tables_tgt
1408 --
1409 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1410 --****************************************************************************
1411 PROCEDURE deduce_bt_tables(forTargets IN Boolean) IS
1412 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1413 uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
1414 l_key_combination BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
1415 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1416 toBeConsidered Boolean;
1417 iMatchingTableIndex DBMS_SQL.NUMBER_TABLE;
1418 l_origin_table VARCHAR2(300);
1419 l_measure_column BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1420 needNewTable Boolean;
1421 l_unique_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsUniqueField;
1422 l_BTTables BSC_METADATA_OPTIMIZER_PKG.tab_clsTable;
1423 l_count1 NUMBER;
1424 l_count2 NUMBER;
1425 l_count3 NUMBER;
1426 l_index NUMBER;
1427 l_temp NUMBER;
1428
1429 l_temp3 NUMBER;
1430 l_BTTables_origin DBMS_SQL.VARCHAR2_TABLE;
1431 l_tablename VARCHAR2(100);
1432 l_error VARCHAR2(1000);
1433 l_loop_ctr NUMBER:=0;
1434 bMeasureLogged boolean ;
1435 BEGIN
1436 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1437 BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables, forTargets = '||
1438 bsc_mo_helper_pkg.boolean_decode(forTargets)||' , system time is '||
1439 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1440 END IF;
1441
1442 If forTargets Then
1443 l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt;
1444 l_BTTables := BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt;
1445 l_tablename := g_target;
1446 Else
1447 l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures ;
1448 l_BTTables := BSC_METADATA_OPTIMIZER_PKG.g_bt_tables;
1449 l_tablename := g_unique;
1450 End If;
1451
1452 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1453 BSC_MO_HELPER_PKG.writeTmp('Total # of unique measures = '||l_unique_measures.count||', Temp measures = '||l_BTTables.count);
1454 END IF;
1455
1456 l_count1 := l_unique_measures.first;
1457 LOOP
1458 EXIT WHEN l_unique_measures.count=0;
1459 uniqueField := l_unique_measures(l_count1);
1460 bMeasureLogged := false;
1461 --Loop through list of dissagregations of the unique field until all have been registered
1462 WHILE NOT all_key_comb_registered(uniqueField.key_combinations) LOOP
1463 IF bMeasureLogged =false AND BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1464 BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1465 BSC_MO_HELPER_PKG.writeTmp('Looping for Measure '||l_count1||' = '|| uniqueField.fieldName ||
1466 ', source='||uniqueField.source||', disaggs are ');
1467 BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1468 BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1469 END IF;
1470 bMeasureLogged := true;
1471 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1472 BSC_MO_HELPER_PKG.writeTmp('Atleast one disagg is not registered', FND_LOG.LEVEL_STATEMENT);
1473 END IF;
1474 IF (uniqueField.key_combinations.count>0) THEN
1475 FOR l_count2 IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1476 LOOP
1477 l_key_combination := uniqueField.key_combinations(l_count2);
1478 If Not l_key_combination.Registered Then
1479 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1480 BSC_MO_HELPER_PKG.writeTmp(' Considering disagg ');
1481 BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, l_key_combination);
1482 END IF;
1483 If (l_key_combination.Origin = 0) Then
1484 toBeConsidered := True;
1485 ElsIf (l_key_combination.Origin <> 0 And
1486 uniqueField.key_combinations(BSC_MO_HELPER_PKG.FindIndex(uniqueField.key_combinations, l_key_combination.Origin)).Registered) Then
1487 toBeConsidered := True;
1488 Else
1489 toBeConsidered := False;
1490 End If;
1491
1492 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1493 BSC_MO_HELPER_PKG.writeTmp(' To be considered = '||bsc_mo_helper_pkg.boolean_decode(toBeConsidered));
1494 END IF;
1495 If toBeConsidered Then
1496 l_temp := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gLov, uniqueField.fieldName, uniqueField.source);
1497 iMatchingTableIndex := get_matching_tables(
1498 uniqueField.fieldName,
1499 uniqueField.source,
1500 l_key_combination.Periodicity,
1501 l_key_combination.keys,
1502 BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode,
1503 uniqueField.impl_type,
1504 l_BTTables
1505 );
1506 needNewTable := True;
1507 l_loop_ctr := iMatchingTableIndex.first;
1508 LOOP
1509 EXIT WHEN iMatchingTableIndex.count = 0;
1510 --It was found a existing temporal table with the same periodicity and disagregation and same field grouping.
1511 --Check if the disagregation and periodicity of the origin tables of the temporal table
1512 --are the same as origin disagregation of the current one
1513 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1514 BSC_MO_HELPER_PKG.writeTmp('Found a existing temporal table with the same periodicity and '||
1515 ' disagregation and same field grouping.');
1516 BSC_MO_HELPER_PKG.writeTmp('Table name = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name||
1517 ', origin table = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable , FND_LOG.LEVEL_STATEMENT);
1518 END IF;
1519 l_BTTables_origin := BSC_MO_HELPER_PKG.getDecomposedString(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, ',');
1520 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1521 BSC_MO_HELPER_PKG.writeTmp('7. l_key_combination.Origin = '||l_key_combination.Origin ||
1522 ', l_BTTables_origin.Count = '||l_BTTables_origin.Count||' , system time is '||
1523 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1524 END IF;
1525 If l_key_combination.Origin = 0 Then
1526 If l_BTTables_origin.Count = 0 -- if its a B table added in this run
1527 -- or if its a production B table
1528 OR (l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable
1529 AND BSC_DBGEN_UTILS.get_table_type(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name)='B')
1530 Then
1531 needNewTable := False;
1532 End If;
1533 Else -- this key combination can be derived from another key combination
1534 If l_BTTables_origin.Count <> 0 THEN --AND (NOT l_BTTables(l_loop_ctr).isProductionTable)Then
1535 l_temp := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1536 BSC_MO_HELPER_PKG.write_to_stack('7.1 l_temp='||l_temp);
1537 l_temp3 := BSC_MO_HELPER_PKG.findIndex(l_BTTables, l_BTTables_origin(l_BTTables_origin.first));
1538 IF (l_temp3 = -1 AND
1539 l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable AND
1540 BSC_DBGEN_UTILS.get_table_type(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name)='B') THEN
1541 -- this is a production B table, but we havent loaded I table into memory
1542 l_temp3 := iMatchingTableIndex(l_loop_ctr);
1543 END IF;
1544 BSC_MO_HELPER_PKG.write_to_stack('7.2 l_temp3='||l_temp3);
1545 BSC_MO_HELPER_PKG.write_to_stack('7.3 l_BTTables(l_temp3).Keys.count='||l_BTTables(l_temp3).Keys.count);
1546 If areDisaggsSame(uniqueField.key_combinations(l_temp).Periodicity,
1547 uniqueField.key_combinations(l_temp).keys,
1548 l_BTTables(l_temp3).Periodicity,
1549 l_BTTables(l_temp3).Keys) Then
1550 needNewTable := False;
1551 BSC_MO_HELPER_PKG.write_to_stack('7.5 needNewTable := False;');
1552 End If;
1553 End If;
1554 End If;
1555 IF (NOT needNewTable) THEN
1556 exit;
1557 END IF;
1558 exit when l_loop_ctr = iMatchingTableIndex.last;
1559 l_loop_ctr:= iMatchingTableIndex.next(l_loop_ctr);
1560 END LOOP;
1561 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562 BSC_MO_HELPER_PKG.writeTmp('needNewTable = '||bsc_mo_helper_pkg.boolean_decode(needNewTable));
1563 END IF;
1564 If needNewTable Then
1565 --Add a new table
1566 --Name
1567 l_table := bsc_mo_helper_pkg.new_clsTable;
1568 If l_key_combination.Origin <> 0 Then
1569 l_table.Name := 'BSC_T_' || (BSC_METADATA_OPTIMIZER_PKG.gMaxT + 1);
1570 BSC_METADATA_OPTIMIZER_PKG.gMaxT := BSC_METADATA_OPTIMIZER_PKG.gMaxT + 1;
1571 Else
1572 l_table.Name := 'BSC_B_' || (BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1);
1573 BSC_METADATA_OPTIMIZER_PKG.gMaxB := BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1;
1574 End If;
1575 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1576 BSC_MO_HELPER_PKG.writeTmp('Going to add table '||l_table.Name);
1577 END IF;
1578 l_table.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1579 l_table.Type := 1;
1580 l_table.Periodicity := l_key_combination.Periodicity;
1581 l_table.EDW_Flag := uniqueField.EDW_Flag;
1582 l_table.IsTargetTable := forTargets;
1583 l_table.IsProductionTable := false;
1584 l_table.impl_type := uniqueField.impl_type;
1585 -- BSC AW
1586 --l_key_combination_keys := BSC_MO_HELPER_PKG.getDisaggKeys(l_tablename, uniqueField.fieldName, l_key_combination.code);
1587 l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1588 l_key_combination.keys := UniqueField.key_Combinations(l_index).keys;
1589 l_count3 := l_key_combination.keys.first;
1590 --Key columns
1591 l_table.keys := l_key_combination.keys;
1592 --Data columns
1593 --It is initialized only with the current field
1594 l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
1595 l_measure_column.fieldName := uniqueField.fieldName;
1596 l_measure_column.source := uniqueField.source;
1597 l_measure_column.MeasureGroup := l_table.measureGroup;
1598 l_measure_column.aggFunction := uniqueField.aggFunction;
1599
1600 If l_key_combination.Origin <> 0 Then
1601 l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1602 l_origin_table := get_origin_table(
1603 uniqueField.key_combinations(l_index).Periodicity,
1604 uniqueField.key_combinations(l_index).keys,
1605 uniqueField.fieldName,
1606 uniqueField.source,
1607 uniqueField.MeasureGroup,
1608 uniqueField.impl_type,
1609 l_BTTables);
1610 End If;
1611 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1612 BSC_MO_HELPER_PKG.writeTmp('14. l_origin_table = '||l_origin_table||' system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1613 END IF;
1614 --Note: removed the name of the table as prefix of the column
1615 --I do not see that the same column could be in two origin tables.
1616 l_measure_column.Origin := l_measure_column.aggFunction|| '('|| l_measure_column.fieldName || ')';
1617 --Note: other properties for internal columns are not used in input, base, temporal tables
1618 l_table.data(l_table.data.count) := l_measure_column;
1619 --TablasOri
1620 --It is initializes with only the name of the origin table
1621 IF l_key_combination.Origin <> 0 Then
1622 IF (l_table.originTable IS NOT NULL) THEN
1623 l_table.originTable := l_table.originTable||',';
1624 END IF;
1625 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1626 bsc_mo_helper_pkg.writeTmp('4. Adding Origin table for '||l_table.name||' = '||l_origin_table);
1627 END IF;
1628 l_table.originTable := l_table.originTable||l_origin_table;
1629 END IF;
1630
1631 --Add the table to the collection l_BTTables
1632 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1633 bsc_mo_helper_pkg.writeTmp('Adding table at l_BTTables('||l_BTTables.count||'), table is ');
1634 bsc_mo_helper_pkg.write_this(l_table);
1635 END IF;
1636 l_BTTables(l_BTTables.count) := l_table;
1637 l_key_combination.Registered := True;
1638 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1639 BSC_MO_HELPER_PKG.writeTmp('15. Registered key combination', FND_LOG.LEVEL_STATEMENT);
1640 END IF;
1641 Else -- Table already exists
1642 --Add the field to the temporal table
1643 --Data columns
1644 --Add the current field
1645 l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
1646 l_measure_column.fieldName := uniqueField.fieldName;
1647 l_measure_column.source := uniqueField.source;
1648 l_measure_column.MeasureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1649 l_measure_column.aggFunction := uniqueField.aggFunction;
1650 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1651 BSC_MO_HELPER_PKG.writeTmp('16. Add current field '||l_measure_column.fieldName, FND_LOG.LEVEL_STATEMENT);
1652 END IF;
1653 If l_key_combination.Origin <> 0 Then
1654 l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1655 l_origin_table := get_origin_table(
1656 uniqueField.key_combinations(l_index).Periodicity,
1657 uniqueField.key_combinations(l_index).keys,
1658 uniqueField.fieldName,
1659 uniqueField.source,
1660 uniqueField.measureGroup,
1661 uniqueField.impl_type,
1662 l_BTTables);
1663 End If;
1664 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1665 BSC_MO_HELPER_PKG.writeTmp('17. Origin table is '||l_origin_table, FND_LOG.LEVEL_STATEMENT);
1666 END IF;
1667 --Note: removed the name of the table as prefix of the column
1668 --I do not see that the same column could be in two origin tables.
1669 l_measure_column.Origin := l_measure_column.aggFunction || '(' || l_measure_column.fieldName || ')';
1670 IF (l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable AND
1671 NOT BSC_MO_INDICATOR_PKG.DataFieldExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data, l_measure_column.fieldName)) THEN
1672 l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTableAltered := true;
1673 l_measure_column.changeType := 'NEW';
1674 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1675 BSC_MO_HELPER_PKG.writeTmp('ChangeType for '||l_measure_column.fieldName ||' = NEW', FND_LOG.LEVEL_STATEMENT);
1676 END IF;
1677 END IF;
1678 --Note: other properties for internal columns are not used in input, base, temporal tables
1679 -- If the field does not exist already in the table, then add it.
1680 IF( NOT BSC_MO_INDICATOR_PKG.DataFieldExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data, l_measure_column.fieldName))THEN
1681 l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data.count) := l_measure_column;
1682 BSC_MO_HELPER_PKG.writeTmp('18. Add origin table '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1683 --TablasOri
1684 --Add to the list the name of the origin table
1685 IF l_key_combination.Origin <> 0 Then
1686 IF Not TableOriginExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, l_origin_table) Then
1687 IF (l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable IS NOT NULL) THEN
1688 l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable := l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable||',';
1689 END IF;
1690 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1691 bsc_mo_helper_pkg.writeTmp('5. Adding Origin table for '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).name||' = '||l_origin_table);
1692 END IF;
1693 l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable := l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable||l_origin_table;
1694 END If;
1695 END If;
1696 END IF;
1697
1698 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1699 BSC_MO_HELPER_PKG.writeTmp('19. Registered key combination', FND_LOG.LEVEL_STATEMENT);
1700 END IF;
1701 l_key_combination.Registered := True;
1702 End If; -- End of NeedNewTable
1703 End If;-- ENd of to be considered
1704 uniqueField.key_combinations(l_count2) := l_key_combination;
1705 End If; --End of registered
1706 END LOOP;
1707 END IF;
1708 END Loop; -- end of while
1709 EXIT WHEN l_count1 = l_unique_measures.last;
1710 l_count1 := l_unique_measures.next(l_count1);
1711 END LOOP;
1712
1713
1714 IF forTargets THEN
1715 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt := l_unique_measures ;
1716 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt := l_BTTables;
1717 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1718 BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt is ');
1719 BSC_MO_HELPER_PKG.write_this(g_target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
1720 BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_tgt is ');
1721 BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
1722 END IF;
1723 ELSE
1724 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures := l_unique_measures;
1725 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables := l_BTTables;
1726 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1727 BSC_MO_HELPER_PKG.writeTmp('g_unique_measures is ');
1728 BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures, FND_LOG.LEVEL_STATEMENT, false);
1729 BSC_MO_HELPER_PKG.writeTmp('g_bt_tables is ');
1730 BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables, FND_LOG.LEVEL_STATEMENT, false, false);
1731 END IF;
1732 END IF;
1733
1734 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1735 BSC_MO_HELPER_PKG.writeTmp('Compl deduce_bt_tables, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1736 END IF;
1737
1738 EXCEPTION WHEN OTHERS THEN
1739 l_error := sqlerrm;
1740 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in deduce_bt_tables : '||l_error);
1741 BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'deduce_bt_tables');
1742 raise;
1743 End ;
1744
1745 --****************************************************************************
1746 -- ResolverOrigenDesagsCamposUnicos : resolve_key_origins
1747 -- DESCRIPTION:
1748 -- Resolve the origin of each dissagregation of each unique field
1749 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1750 --****************************************************************************
1751 PROCEDURE resolve_key_origins(p_unique_measures IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsUniqueField,
1752 pTargets IN BOOLEAN) IS
1753 uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
1754 l_key_combination BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
1755 Desag1 BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
1756 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1757 i NUMBER;
1758 j NUMBER;
1759 k NUMBER;
1760 l NUMBER;
1761 --l_key_combination_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1762 --Desag1_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1763 l_tableName VARCHAR2(100);
1764 l_error VARCHAR2(1000);
1765 l_index NUMBER;
1766 bMeasureLogged boolean ;
1767 BEGIN
1768 IF (pTargets) THEN
1769 l_tableName := g_target;
1770 ELSE
1771 l_tableName := g_unique;
1772 END IF;
1773 IF (p_unique_measures.count =0) THEN
1774 return;
1775 END IF;
1776 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1777 BSC_MO_HELPER_PKG.writeTmp(' ');
1778 BSC_MO_HELPER_PKG.writeTmp(' ');
1779 BSC_MO_HELPER_PKG.writeTmp('Inside resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1780 END IF;
1781 IF (p_unique_measures.count>0) THEN
1782 FOR i IN p_unique_measures.first..p_unique_measures.last
1783 LOOP
1784 uniqueField := p_unique_measures(i);
1785 bmeasureLogged := false;
1786 --for each dissagregation look if it could be originated from another
1787 IF uniqueField.key_combinations.count>0 THEN
1788 FOR j IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1789 LOOP
1790 l_key_combination := uniqueField.key_combinations(j);
1791 IF (l_key_combination.registered = false) THEN -- ignore registered disaggs from prod. tables
1792
1793 IF bMeasureLogged =false and BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1794 BSC_MO_HELPER_PKG.writeTmp(' ');
1795 BSC_MO_HELPER_PKG.writeTmp('Looping for unique field : '||uniqueField.fieldName||', source='||uniqueField.source, FND_LOG.LEVEL_STATEMENT);
1796 bsc_mo_helper_pkg.write_this(null, uniqueField);
1797 END IF;
1798 bMeasureLogged := true;
1799 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1800 BSC_MO_HELPER_PKG.writeTmp(' Disagg is '||l_key_combination.code, FND_LOG.LEVEL_STATEMENT);
1801 END IF;
1802 l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1803 l_key_combination.keys := uniqueField.key_combinations(l_index).keys;
1804 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1805 BSC_MO_HELPER_PKG.writeTmp(' Disagg Keys are ', FND_LOG.LEVEL_STATEMENT);
1806 BSC_MO_HELPER_PKG.write_this(l_key_combination.keys);
1807 END IF;
1808 FOR k IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1809 LOOP
1810 Desag1 := uniqueField.key_combinations(k);
1811 l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, desag1.code);
1812 If Desag1.Code <> l_key_combination.Code Then
1813 If can_derive_keys(--uniqueField.fieldName,
1814 l_key_combination, l_key_combination.code, Desag1, Desag1.code, l_tablename) Then
1815 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1816 BSC_MO_HELPER_PKG.writeTmp(' Disagg.code is '||l_key_combination.code||' and Disagg1.code is '||desag1.code, FND_LOG.LEVEL_STATEMENT);
1817 BSC_MO_HELPER_PKG.writeTmp('Origin exists, verify there is no loop ', FND_LOG.LEVEL_STATEMENT);
1818 END IF;
1819 --verify that it is not creating a loop when l_key_combination is originated from Desag1
1820 If Not circular_dependency_exists(l_key_combination.Code, Desag1.Code, uniqueField.key_combinations) Then
1821 l_key_combination.Origin := Desag1.Code;
1822 IF (l_key_combination.keys.count>0) THEN
1823 FOR l IN l_key_combination.keys.first..l_key_combination.keys.last
1824 LOOP
1825 l_key := l_key_combination.keys(l);
1826 l_key.Origin := BSC_MO_INDICATOR_PKG.getKeyOrigin(Desag1.keys, l_key.keyName);
1827 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1828 bsc_mo_helper_pkg.writeTmp('Changing table = '||l_tableName ||', field '||uniqueField.fieldName
1829 ||', code = '||l_key_combination.code||'''s origin to '||l_key.Origin);
1830 END IF;
1831 l_key_combination.keys(l) := l_key;
1832 END LOOP;
1833 END IF;
1834 uniqueField.key_combinations(j) := l_key_combination;
1835 uniqueField.key_combinations(j) := l_key_combination;
1836 uniqueField.key_combinations(j).keys := l_key_combination.keys;
1837 EXIT ;
1838 End If;
1839 End If;
1840 End If;
1841 END LOOP;
1842 END IF;
1843 END LOOP;
1844 END IF;
1845 p_unique_measures(i) := uniqueField;
1846 END LOOP;
1847 END IF;
1848 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1849 BSC_MO_HELPER_PKG.writeTmp('Completed resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1850 BSC_MO_HELPER_PKG.write_This(null,p_unique_measures, FND_LOG.LEVEL_STATEMENT, true);
1851 END IF;
1852 EXCEPTION WHEN OTHERS THEN
1853 l_error := sqlerrm;
1854 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in resolve_key_origins : '||l_error);
1855 raise;
1856 End ;
1857
1858
1859 --****************************************************************************
1860 -- GetCodDesagregacion : GetDisaggCode
1861 --
1862 -- DESCRIPTION:
1863 -- Returns the code of the dissagregation on collection colDesags where
1864 -- the periodicity and key columns are identical to the given ones.
1865 -- Return 0 if it is not found.
1866 -- Note: Additionally, it verify that it has the same filter.
1867 -- There is no filter defined for tables of no pre-calculated indicators,
1868 -- so the filter is not verified.
1869 -- Filter is taken into accout for tables of precalculated indicators.
1870 -- colDesags is a collection of objects of class clsdisaggField.
1871 -- p_keys is a collection of objects of class clsCampoLlave
1872 --
1873 -- PARAMETERS:
1874 -- colDesags: collection of dissagregations
1875 -- pPeriodicity: periodicity
1876 -- p_keys: key columns
1877 --
1878 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1879 --****************************************************************************
1880 Function GetDisaggCode(pFieldName IN VARCHAR2,
1881 colDesags IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDisaggField,
1882 pPeriodicity in number,
1883 p_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
1884 pTableName IN VARCHAR2)
1885 return NUMBER IS
1886 l_key_combination BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
1887 l_key BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1888 keysEqual Boolean;
1889 l_temp NUMBER;
1890
1891 --l_key_combination_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1892 l_error VARCHAR2(1000);
1893 l_stack VARCHAR2(32000);
1894
1895 BEGIN
1896
1897 IF( colDesags.count= 0) THEN
1898 return -1;
1899 END IF;
1900 l_stack := 'Inside GetDisaggCode, pPeriodicity='||pPeriodicity;
1901 FOR i IN colDesags.first..colDesags.last
1902 LOOP
1903 IF (length(l_stack) > 31000) THEN
1904 l_stack := null;
1905 END IF;
1906 l_stack := l_stack || g_newline||'Loop1';
1907 l_key_combination := colDesags(i);
1908 --BSC AW
1909 --l_key_combination_keys := BSC_MO_HELPER_PKG.getDisaggKeys(pTableName, pFieldName, l_key_combination.Code);
1910 --l_key_combination_keys := l_key_combination.keys;
1911 If l_key_combination.Periodicity = pPeriodicity Then
1912 If l_key_combination.keys.Count = p_keys.Count Then
1913 keysEqual := True;
1914 IF (l_key_combination.keys.count>0) THEN
1915 FOR j IN l_key_combination.keys.first..l_key_combination.keys.last
1916 LOOP
1917 l_stack := l_stack || g_newline||'Loop2 begin';
1918 l_key := l_key_combination.keys(j);
1919 If Not BSC_MO_INDICATOR_PKG.KeyfieldExists(p_keys, l_key.keyName) Then
1920 keysEqual := False;
1921 Exit ;
1922 Else
1923 l_temp := BSC_MO_HELPER_PKG.findIndex(p_keys, l_key.keyName);
1924 If UPPER(l_key.FilterViewName) <> UPPER(p_keys(l_temp).FilterViewName) Then
1925 keysEqual := False;
1926 Exit ;
1927 End If;
1928 End If;
1929 END LOOP;
1930 END IF;
1931 l_stack := l_stack || g_newline||'Loop2 end';
1932 END IF;
1933 If keysEqual Then
1934 l_stack := l_stack || g_newline||'Compl GetDisaggCode, returning '||l_key_combination.Code;
1935 return l_key_combination.Code;
1936 End If;
1937 End If;
1938 END LOOP;
1939 l_stack := l_stack || g_newline||'Compl GetDisaggCode, returning -1';
1940 return -1;
1941
1942 EXCEPTION WHEN OTHERS THEN
1943 l_error := sqlerrm;
1944 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDisaggCode :'||l_error);
1945 BSC_MO_HELPER_PKG.writeTmp('Stack is :'||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1946 raise;
1947
1948 End ;
1949
1950
1951
1952 --****************************************************************************
1953 -- ExisteuniqueField : UniqueFieldExists
1954 --
1955 -- DESCRIPTION:
1956 -- Return TRUE if the given field belongs to collection g_unique_measures .
1957 --
1958 -- PARAMETERS:
1959 -- Campo: field name
1960 --
1961 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1962 --****************************************************************************
1963 Function UniqueFieldExists(p_measure_name IN VARCHAR2,
1964 -- BSC Autogen
1965 p_source IN VARCHAR2,
1966 p_impl_type IN NUMBER, p_unique_measure_list IN BSC_METADATA_OPTIMIZER_PKG.tab_clsUniqueField) return Boolean IS
1967 l_uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
1968 l_count NUMBER ;
1969 BEGIN
1970 IF (p_unique_measure_list.count =0) THEN
1971 return false;
1972 END IF;
1973 l_count := p_unique_measure_list.first;
1974 LOOP
1975 l_uniqueField := p_unique_measure_list(l_count);
1976 If UPPER(l_uniqueField.fieldName) = UPPER(p_measure_name)
1977 -- for AW
1978 and l_uniqueField.impl_type = p_impl_type
1979 --BSC Autogen
1980 and l_uniqueField.source = p_source THEN
1981 return true;
1982 End If;
1983 EXIT WHEN l_count = p_unique_measure_list.last;
1984 l_count := p_unique_measure_list.next(l_count);
1985 END LOOP;
1986 return FALSE;
1987 EXCEPTION WHEN OTHERS THEN
1988 BSC_MO_HELPER_PKG.writeTmp('Exception in UniqueFieldExists, '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1989 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in UniqueFieldExists, '||sqlerrm);
1990 raise;
1991 End ;
1992
1993 --****************************************************************************
1994 -- init_s_table_measures_precalc : InicListaUnicaCamposPreCalc
1995 -- DESCRIPTION:
1996 -- Initialize the collection g_unique_measures_precalc, where are the data fields
1997 -- with all the information: field name, agregation function, and the list
1998 -- of all disagregations used by the indicators for the field.
1999 --****************************************************************************
2000 PROCEDURE init_s_table_measures_precalc(p_tables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsTable )IS
2001
2002 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
2003 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
2004 uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
2005 disaggField BSC_METADATA_OPTIMIZER_PKG.clsDisaggField;
2006 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2007 disaggKeyField BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2008 CodDesag NUMBER;
2009 l_count1 NUMBER;
2010 l_count2 NUMBER;
2011 l_count3 NUMBER;
2012 l_temp NUMBER;
2013 l_field_index NUMBER;
2014 l_disagg_index NUMBER;
2015 l_index NUMBER;
2016
2017 Tabla_OriginTable DBMS_SQL.VARCHAR2_TABLE;
2018
2019 uniqueField_disAggKeys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2020
2021 l_stack varchar2(32000):= null;
2022 l_error varchar2(2000);
2023 l_optimizationMode NUMBER;
2024 l_impl_type number;
2025 BEGIN
2026 IF (p_tables.count=0) THEN
2027 return;
2028 END IF;
2029 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2030 BSC_MO_HELPER_PKG.writeTmp('Inside init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2031 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2032 END IF;
2033 l_count1 := p_tables.first;
2034 LOOP
2035 IF (length(l_stack) > 30000) THEN
2036 l_stack := null;
2037 END IF;
2038 l_table := p_tables(l_count1);
2039 IF (Tabla_OriginTable IS NOT NULL) THEN
2040 Tabla_OriginTable := bsc_mo_helper_pkg.getDecomposedString(l_table.originTable, ',');
2041 END IF;
2042 --Only consider tables not having origin and belong to indicators precalculated
2043 l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_table.Indicator);
2044 IF (l_index = -1) THEN
2045 l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_table.Indicator, 'DB_TRANSFORM', 1);
2046 l_impl_type := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_table.Indicator, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1);
2047 ELSE
2048 l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).OptimizationMode;
2049 l_impl_type := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).impl_type;
2050 END IF;
2051
2052 IF (l_table.isProductionTable is null) THEN
2053 l_table.isProductionTable:=false;
2054 END IF;
2055 If ((l_table.originTable IS NULL AND l_table.isProductionTable=false) OR
2056 (l_table.originTable IS NOT NULL AND l_table.isProductionTable=true))
2057 And l_optimizationMode = 0 Then
2058 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2059 BSC_MO_HELPER_PKG.writeTmp('');
2060 bsc_mo_helper_pkg.writeTmp(' Processing table '||l_table.name);
2061 bsc_mo_helper_pkg.write_this(l_table);
2062 END IF;
2063
2064 l_count2 := l_table.data.first;
2065 LOOP
2066 EXIT WHEN l_table.data.count=0;
2067 l_measure := l_table.data(l_count2);
2068 --Do not consider internal columns. They are used only in indicator
2069 --tables and are not going to be base or input tables.
2070 If l_measure.InternalColumnType = 0 Then
2071 If Not UniqueFieldExists(l_measure.fieldName, l_measure.source, l_impl_type, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc) Then
2072 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2073 bsc_mo_helper_pkg.writeTmp(' '||l_measure.fieldName||'('||l_measure.source||') Field does not exist --> Add it');
2074 END IF;
2075 --Field does not exists --> Add it
2076 uniqueField := bsc_mo_helper_pkg.new_clsUniqueField;
2077 uniqueField.fieldName := l_measure.fieldName;
2078 uniqueField.source := l_measure.source;
2079 uniqueField.measureGroup := get_measure_Group(l_measure.fieldName, l_measure.source);
2080 uniqueField.aggFunction := l_measure.aggFunction;
2081 IF (l_index <> -1) THEN
2082 uniqueField.EDW_Flag := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).EDW_Flag;
2083 ELSE
2084 uniqueField.EDW_Flag := 0;
2085 END IF;
2086 uniqueField.impl_type := l_impl_type;
2087 --Desags. It initialized with one element that correspond to the disagregation
2088 --and periodicity of the table
2089 disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2090 disaggField.Code := 1;
2091 disaggField.Periodicity := l_table.Periodicity;
2092 --Key columns. Same as the table
2093 l_count3 := l_table.keys.first;
2094 disaggField.keys := l_table.keys;
2095 disaggField.Origin := 0;
2096 IF (l_table.isProductionTable ) THEN
2097 disaggField.Registered := True;
2098 ELSE
2099 disaggField.Registered := False;
2100 END IF;
2101 uniqueField.key_combinations(uniqueField.key_combinations.count) := disaggField;
2102 --uniqueField.key_combinations(0).keys := disaggField.Keys;
2103 --Add the unique field to the collection g_unique_measures_precalc
2104 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count) := uniqueField;
2105 ELSE
2106 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2107 bsc_mo_helper_pkg.writeTmp( l_measure.fieldName||'('||l_measure.source||') field already exists. Check periodicity and disaggregation');
2108 END IF;
2109 --The field already exists. So check if its periodicity and disagregation
2110 -- is in its list of disagregations
2111 l_field_index := BSC_MO_HELPER_PKG.findIndex(
2112 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc,
2113 l_measure.fieldName,
2114 l_measure.source,
2115 l_table.impl_type);
2116 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2117 bsc_mo_helper_pkg.writeTmp('Field Index is '||l_field_index);
2118 END IF;
2119 uniqueField.key_combinations.delete;
2120 uniqueField.key_combinations := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations;
2121 CodDesag := GetDisaggCode(
2122 uniqueField.fieldName,
2123 uniqueField.key_combinations,
2124 l_table.Periodicity,
2125 l_table.keys,
2126 l_table.name);
2127 If CodDesag = -1 Then
2128 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2129 bsc_mo_helper_pkg.writeTmp( 'Disaggregation does not exist, add it');
2130 END IF;
2131 --It does not exist --> Add it
2132 disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2133 uniqueField.key_combinations.delete;
2134 uniqueField.key_combinations := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations;
2135 disaggField.Code := uniqueField.key_combinations.Count + 1;
2136 disaggField.Periodicity := l_table.Periodicity;
2137 --Key columns. Same as the table
2138 disaggField.keys := l_table.keys;
2139 disaggField.Origin := 0;
2140 IF (l_table.isProductionTable ) THEN
2141 disaggField.Registered := True;
2142 ELSE
2143 disaggField.Registered := False;
2144 END IF;
2145 --Add the disagregation to the collection Desags of the unique field
2146 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations
2147 (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations.count) := disaggField;
2148 ELSE
2149 --The dissagregation exists. We need to use the property NecesitaCod0 because
2150 --the table could need zero code in some key where the dissagregation dont need
2151 --so far.
2152 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2153 bsc_mo_helper_pkg.writeTmp( 'Disaggregation already exists');
2154 END IF;
2155 l_disagg_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, CodDesag);
2156 uniqueField_disAggKeys.delete;
2157 uniqueField_disAggKeys := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations(l_disagg_index).keys;
2158 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2159 bsc_mo_helper_pkg.writeTmp( 'Matching disagg index '||l_disagg_index);
2160 END IF;
2161 IF uniqueField_disAggKeys.count>0 THEN
2162 FOR l_count3 IN uniqueField_disAggKeys.first..uniqueField_disAggKeys.last
2163 LOOP
2164 l_key := uniqueField_disAggKeys(l_count3);
2165 l_temp := BSC_MO_HELPER_PKG.findIndex(l_table.keys, l_key.keyName);
2166 If l_table.keys(l_temp).NeedsCode0 Then
2167 l_key.NeedsCode0 := l_table.keys(l_temp).NeedsCode0;
2168 End If;
2169 uniqueField_disAggKeys(l_count3) := l_key;
2170 END LOOP;
2171 END IF;
2172 IF (l_table.isProductionTable) THEN
2173 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc (l_field_index).key_combinations(CodDesag-1).Registered := true;
2174 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc (l_field_index).key_combinations(CodDesag-1).isProduction := true;
2175 END IF;
2176 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations(l_disagg_index).keys := uniqueField_disAggKeys;
2177
2178 END IF;
2179 END IF;
2180 END IF;
2181 EXIT WHEN l_count2=l_table.data.last;
2182 l_count2 := l_table.data.next(l_count2);
2183 END LOOP;
2184 p_tables(l_count1) := l_table;
2185 End If;
2186 EXIT WHEN l_count1 = p_tables.last;
2187 l_count1 := p_tables.next(l_count1);
2188 l_stack := null;
2189 END LOOP;
2190 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2191 BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2192 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2193 END IF;
2194 BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_precalc = ');
2195 BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc );
2196
2197 EXCEPTION WHEN OTHERS THEN
2198 l_error := sqlerrm;
2199 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures_precalc : '||l_error);
2200 BSC_MO_HELPER_PKG.writeTmp('l_stack : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2201 BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PC_DATACOL_FAILED', 'init_s_table_measures_precalc');
2202 raise;
2203 End;
2204
2205
2206 --****************************************************************************
2207 -- InicListaUnicaCampos : init_s_table_measures
2208 --
2209 -- DESCRIPTION:
2210 -- Initialize the collection g_unique_measures /g_unique_measures_tgt,
2211 -- where are the data fields with all the information: field name,
2212 -- agregation function, and the list of all disagregations used by the
2213 -- indicators for the field.
2214 --
2215 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2216 --****************************************************************************
2217 PROCEDURE init_s_table_measures(p_tables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsTable ) IS
2218
2219 l_clstable BSC_METADATA_OPTIMIZER_PKG.clsTable;
2220 l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
2221 uniqueField BSC_METADATA_OPTIMIZER_PKG.clsUniqueField ;
2222 l_unique_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsUniqueField ;
2223 disaggField BSC_METADATA_OPTIMIZER_PKG.clsDisAggField;
2224 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2225 disaggKeyField BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2226 l_count NUMBER := 0;
2227 l_count2 NUMBER := 0;
2228 l_count3 NUMBER := 0;
2229 l_index NUMBER := null;
2230 l_temp NUMBER;
2231 l_index2 NUMBER;
2232
2233 Tabla_OriginTable DBMS_SQL.VARCHAR2_TABLE;
2234 l_tablename VARCHAR2(100) ;
2235 l_error VARCHAR2(1000);
2236
2237 l_stack VARCHAR2(32000);
2238 l_OptimizationMode NUMBER;
2239 l_impl_type NUMBER;
2240 l_disagg_code NUMBER;
2241 BEGIN
2242 IF (p_tables.count=0) THEN
2243 return;
2244 END IF;
2245 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2246 BSC_MO_HELPER_PKG.writeTmp('Inside init_s_table_measures, p_tables.count = '||p_tables.count||', g_unique_measures .count='||
2247 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures .count||
2248 ', g_unique_measures_tgt.count='||BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count||' '||bsc_mo_helper_pkg.get_time
2249 , FND_LOG.LEVEL_PROCEDURE);
2250 END IF;
2251 l_count := p_tables.first;
2252 LOOP
2253 l_clstable := p_tables(l_count);
2254 IF (l_clstable.IsTargetTable) THEN
2255 l_tablename := g_target;
2256 ELSE
2257 l_tablename := g_unique;
2258 END IF;
2259 --Only consider tables not having origin and belong to indicators no-precalculated
2260 --Also only consider tables that are not for targets
2261 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_clstable.Indicator);
2262 IF (l_temp = -1) THEN
2263 l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_clstable.Indicator, 'DB_TRANSFORM', 1);
2264 l_impl_type := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_clstable.Indicator, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1);
2265 ELSE
2266 l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).OptimizationMode;
2267 l_impl_type := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).impl_type;
2268 END IF;
2269 l_stack := l_stack ||g_newline||'Table = '||l_clstable.name||', l_optimizationMode = '||l_optimizationmode||', l_impl_type='||l_impl_type;
2270
2271 IF (l_clstable.isProductionTable is null) THEN
2272 l_clstable.isProductionTable:=false;
2273 END IF;
2274 If ((l_clstable.originTable IS NULL AND l_clstable.isProductionTable=false) OR
2275 l_clstable.isProductionTable=true
2276 )
2277 And l_optimizationMode <> 0 Then
2278 BSC_MO_HELPER_PKG.writeTmp(' ');
2279 BSC_MO_HELPER_PKG.writeTmp(' ');
2280 BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_clstable.name);
2281 --l_table_measures := l_clstable.Data;
2282 --l_table_keys := l_clstable.keys;
2283 BSC_MO_HELPER_PKG.writeTmp(' l_clstable.data.count = '||l_clstable.data.count||', l_clstable.keys.count='||l_clstable.keys.count );
2284 If l_clstable.IsTargetTable Then
2285 l_stack := l_stack||' Target table';
2286 l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt;
2287 Else
2288 l_stack := l_stack||g_newline||' Not a target table';
2289 l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures ;
2290 End If;
2291 l_stack := l_stack||g_newline||' l_unique_measures.count = '||l_unique_measures.count;
2292 IF length(l_stack )> 31000 THEN
2293 l_stack := substr(l_stack, 30000);
2294 END IF;
2295 --We maintain a separate collection for data fields of targets tables
2296 IF l_clstable.data.count>0 THEN
2297 FOR l_count2 in l_clstable.data.first..l_clstable.data.last
2298 LOOP
2299 l_measure := l_clstable.data(l_count2);
2300 --Do not consider internal columns. They are used only in indicator
2301 --tables and are not going to be base or input tables.
2302 If l_measure.InternalColumnType = 0 Then
2303 l_stack := l_stack||g_newline||' Checking field '||l_measure.fieldName;
2304 l_stack := l_stack||g_newline||' --------------------';
2305 BSC_MO_HELPER_PKG.writeTmp(' ');
2306 BSC_MO_HELPER_PKG.writeTmp(' Checking field '||l_measure.fieldName||', source='||l_measure.source);
2307 BSC_MO_HELPER_PKG.writeTmp(' --------------------------------------');
2308 IF length(l_stack )> 31000 THEN
2309 l_stack := substr(l_stack, 30000);
2310 END IF;
2311 If UniqueFieldExists(l_measure.fieldName, l_measure.source, l_impl_type, l_unique_measures)=false Then
2312 l_stack := l_stack||g_newline||'..Field does not exist';
2313 -- If its a production table, dont add the measures that are used by Indicators in this iteration
2314 BSC_MO_HELPER_PKG.writeTmp('..does not exist --> Add it');
2315 --Field does not exists --> Add it
2316 uniqueField := BSC_Mo_helper_PKG.new_clsUniqueField;
2317 uniqueField.fieldName := l_measure.fieldName;
2318 uniqueField.source := l_measure.source;
2319 uniqueField.measureGroup := get_measure_group(uniqueField.fieldName, l_measure.source);
2320 uniqueField.aggFunction := l_measure.aggFunction;
2321 l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_clstable.Indicator);
2322 IF (l_temp <> -1) THEN
2323 uniqueField.EDW_Flag := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).EDW_Flag;
2324 END IF;
2325 l_stack := l_stack||g_newline||'l_impl_type='||l_impl_type;
2326 uniqueField.impl_type := l_impl_type;
2327 --Desags. It initialized with one element that correspond to the disagregation
2328 --and periodicity of the table
2329 disaggField := BSC_Mo_helper_PKG.new_clsDisAggField;
2330 disaggField.Code := 1;
2331 disaggField.Periodicity := l_clstable.Periodicity;
2332 -- Important, mark this as from existing table
2333 disaggField.isProduction := l_clsTable.isProductionTable;
2334 --Key columns. Same as the table
2335 --Dissagg_Keys.delete;
2336 l_stack := l_stack||g_newline||'Table keys.count='||l_clstable.keys.count;
2337 IF (l_clstable.keys.count>0) THEN
2338 FOR l_count3 IN l_clstable.keys.first..l_clstable.keys.last
2339 LOOP
2340 l_key := BSC_Mo_helper_PKG.new_clsKeyField;
2341 l_key := l_clstable.keys (l_count3);
2342 disaggKeyField := bsc_mo_helper_pkg.new_clsKeyField;
2343 disaggKeyField.keyName := l_key.keyName;
2344 disaggKeyField.Origin := l_key.Origin;
2345 disaggKeyField.NeedsCode0 := False;
2346 disaggKeyField.CalculateCode0 := False;
2347 disaggKeyField.FilterViewName := null;
2348 l_stack := l_stack||g_newline||'Adding disagg key='||disaggKeyField.keyName;
2349 disaggField.keys(disaggField.keys.count) := disaggKeyField;
2350 END LOOP;
2351 END IF;
2352 disaggField.Origin := 0;
2353 IF (l_clstable.isProductionTable) THEN
2354 disaggField.Registered := true;
2355 disaggField.isProduction := true;
2356 ELSE
2357 disaggField.Registered := False;
2358 disaggField.isProduction := False;
2359 END IF;
2360 l_stack := l_stack||g_newline||'Disagg keys = '||disaggField.keys.count;
2361 uniqueField.key_combinations(uniqueField.key_combinations.count) := disaggField;
2362 BSC_MO_HELPER_PKG.writeTmp('Adding to unique field');
2363 BSC_MO_HELPER_PKG.write_this(null, uniqueField);
2364 --Add the unique field to the collection l_unique_measures
2365 If l_clstable.IsTargetTable Then
2366 l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count;
2367 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_index2) := uniqueField;
2368 l_stack := l_stack||g_newline||'Added g_unique_measures ('||l_index2||'), count = '||l_index2;
2369 BSC_MO_HELPER_PKG.writeTMp('Added '||l_measure.fieldName||', source='||l_measure.source||
2370 ' to g_unique_measures_tgt at index='||l_index2 );
2371 Else
2372 l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures.count;
2373 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_index2) := uniqueField;
2374 l_stack := l_stack||g_newline||'Added g_unique_measures ('||l_index2||'), count = '||l_index2;
2375 BSC_MO_HELPER_PKG.writeTMp('Added '||l_measure.fieldName||', source='||l_measure.source||
2376 ' to g_unique_measures at index='||l_index2);
2377 End If;
2378 Else
2379 --The field already exists. So check if its periodicity and disagregation
2380 -- is in its list of disagregations
2381 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2382 BSC_MO_HELPER_PKG.writeTMp(l_measure.fieldName||'..Field already exists --> check periodicity and disagregation');
2383 END IF;
2384 l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2385 l_disagg_code := GetDisaggCode(l_unique_measures(l_temp).fieldName,
2386 l_unique_measures(l_temp).key_combinations,
2387 l_clstable.Periodicity,
2388 l_clstable.keys,
2389 l_Tablename);
2390 IF l_disagg_code = -1 Then
2391 l_stack := l_stack||g_newline||'Disagg does not exist -> Add it ';
2392 bsc_mo_helper_pkg.writeTmp( 'Disagg does not exist -> Add it ');
2393 --It does not exist --> Add it
2394 l_temp := bsc_mo_helper_pkg.findIndex(l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2395 disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2396 disaggField.Code := l_unique_measures(l_temp).key_combinations.count + 1;
2397 disaggField.Periodicity := l_clstable.Periodicity;
2398 --Key columns. Same as the table
2399 IF (l_clstable.keys.count>0) THEN
2400 FOR l_count3 IN l_clstable.keys.first..l_clstable.keys.last LOOP
2401 l_key := l_clstable.keys(l_count3);
2402 disaggKeyField := bsc_mo_helper_pkg.new_clsKeyField;
2403 disaggKeyField.keyName := l_key.keyName;
2404 disaggKeyField.Origin := l_key.Origin;
2405 disaggKeyField.NeedsCode0 := False;
2406 disaggKeyField.CalculateCode0 := False;
2407 disaggKeyField.FilterViewName := null;
2408 disaggField.keys(disaggField.keys.count) := disaggKeyField;
2409 END LOOP;
2410 END IF;
2411 --Origin
2412 disaggField.Origin := 0;
2413 --disaggField.Registered := False;
2414 IF (l_clstable.isProductionTable) THEN
2415 disaggField.Registered := true;
2416 disaggField.isProduction := true;
2417 ELSE
2418 disaggField.Registered := False;
2419 disaggField.isProduction := False;
2420 END IF;
2421 l_stack := l_stack||g_newline||'2. Disagg keys = '||disaggField.keys.count;
2422 --Add the disagregation to the collection Desags of the unique field
2423 l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2424 --Copy back the unique field to the collection l_unique_measures
2425 If l_clstable.IsTargetTable Then
2426 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations
2427 (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations.count) := disaggField;
2428 bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures_tgt at index='||l_temp);
2429 Else
2430 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations
2431 (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures(l_temp).key_combinations.count) := disaggField;
2432 bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures at index='||l_temp);
2433 End If;
2434 ELSE -- disagg code is <> -1
2435 IF (l_clstable.isProductionTable) THEN
2436 l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2437 If l_clstable.IsTargetTable Then
2438 -- If another table requires this disagg, then we should still leave it as unregistered
2439 IF BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations(l_disagg_code-1).Registered = false THEN
2440 null;
2441 ELSE
2442 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations(l_disagg_code-1).Registered := true;
2443 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations(l_disagg_code-1).isProduction := true;
2444 END IF;
2445 Else
2446 IF (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations(l_disagg_code-1).Registered =false) THEN
2447 null;
2448 ELSE
2449 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations(l_disagg_code-1).Registered := true;
2450 BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations(l_disagg_code-1).isProduction := true;
2451 END IF;
2452 End If;
2453 END IF;
2454 End If;
2455 End If;
2456 End If;
2457 END LOOP;
2458 END IF;
2459 End If;
2460 IF length(l_stack )> 31000 THEN
2461 l_stack := substr(l_stack, 30000);
2462 END IF;
2463 p_tables(l_count) := l_clstable;
2464 EXIT WHEN l_count = p_tables.last;
2465 l_count := p_tables.next(l_count);
2466 END LOOP;
2467 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2468 BSC_MO_HELPER_PKG.writeTmp('Done with init_s_table_measures, writing output now');
2469 BSC_MO_HELPER_PKG.writeTmp('g_unique_measures = ');
2470 BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures );
2471 BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt = ');
2472 BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2473 BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2474 END IF;
2475 EXCEPTION WHEN OTHERS THEN
2476 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2477 BSC_MO_HELPER_PKG.writeTmp('l_stack = '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2478 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2479 raise;
2480 End ;
2481
2482
2483 --****************************************************************************
2484 -- InputTables : TablasEntrada
2485 --
2486 -- DESCRIPTION:
2487 -- Deduce the input tables.
2488 --
2489 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2490 --****************************************************************************
2491 PROCEDURE InputTables IS
2492 l_counter NUMBER;
2493 KpiTable bsc_metadata_optimizer_pkg.clsTable;
2494 l_index NUMBER;
2495 l_error VARCHAR2(3000);
2496 BEGIN
2497 BSC_MO_HELPER_PKG.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
2498 BSC_MO_HELPER_PKG.writeTmp('Inside InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2499 --BSC-MV Note: If the indicator is not processed for structural changes and
2500 --the summarization level was changed from NULL to NOT NULL (upgrade)
2501 --then I need to get from the database the source table of the indicator and set
2502 --the origin in gTablas.
2503 --By doing this Metadata will not try to get new input, base and T tables for
2504 --those indicators.
2505 --Also add those tables to the global array garrTablesUpgradeT(). Those tables
2506 --will be used as start point to load all related tables from database to gTables.
2507
2508 If bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 1 Then
2509 bsc_metadata_optimizer_pkg.gnumTablesUpgradeT := 0;
2510 l_counter := bsc_metadata_optimizer_pkg.gTables.first;
2511 LOOP
2512 EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
2513 KpiTable := bsc_metadata_optimizer_pkg.gTables(l_counter);
2514 l_index := bsc_mo_helper_pkg.findIndex(bsc_metadata_optimizer_pkg.gIndicators, KpiTable.Indicator);
2515 If bsc_metadata_optimizer_pkg.gIndicators(l_index).Action_Flag <> 3 And KpiTable.originTable IS NULL Then
2516 set_origin_table_from_db(KpiTable);
2517 End If;
2518 bsc_metadata_optimizer_pkg.gTables(l_counter) := KpiTable;
2519 EXIT WHEN l_counter = bsc_metadata_optimizer_pkg.gTables.last;
2520 l_counter := bsc_metadata_optimizer_pkg.gTables.next(l_counter);
2521 END LOOP;
2522 End If;
2523
2524 --EDW Note:
2525 --I don't need to do anything special to separate tables for EDW KPIs from
2526 --BSC Kpis because the following reasons (Actually just one of them is enough):
2527 --a. EDW KPIs and BSC KPIs dont share dimensions.
2528 --b. EDW KPIs and BSC KPIs dont share periodicities.
2529 --c. EDW KPIs and BSC KPIs dont share measures.
2530 --Initilize the list of unique measures for indicators being processed
2531 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures');
2532 BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures', FND_LOG.LEVEL_STATEMENT, true);
2533 init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.gTables);
2534
2535 --Initilize the list of unique measures for indicators being processed - Precalc
2536 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc');
2537 BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc', FND_LOG.LEVEL_STATEMENT, true);
2538 init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.gTables);
2539
2540 -- Load the existing B and T tables into memory for better optimization
2541 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling LOAD_B_T_TABLES_FROM_DB');
2542 BSC_MO_HELPER_PKG.writeTmp('Calling LOAD_B_T_TABLES_FROM_DB', FND_LOG.LEVEL_STATEMENT, true);
2543 LOAD_B_T_TABLES_FROM_DB;
2544
2545 --Resolve the origin of each dissagregation of each field
2546 --This is done only for non-precalculated fields
2547 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling resolve_key_origins');
2548 BSC_MO_HELPER_PKG.writeTmp('Calling resolve_key_origins', FND_LOG.LEVEL_STATEMENT, true);
2549 resolve_key_origins (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures , false);
2550
2551 --Resolve the origin of each dissagregation for Targets
2552 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling resolve_key_origins for Targets');
2553 BSC_MO_HELPER_PKG.writeTmp('Calling resolve_key_origins for Targets', FND_LOG.LEVEL_STATEMENT, true);
2554 resolve_key_origins ( BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt, true);
2555
2556 --Make the list of temporal and base tables
2557 BSC_METADATA_OPTIMIZER_PKG.gMaxT := getMaxTableIndex('BSC_T_');
2558 BSC_METADATA_OPTIMIZER_PKG.gMaxB := getMaxTableIndex('BSC_B_');
2559 BSC_METADATA_OPTIMIZER_PKG.gMaxI := getMaxTableIndex('BSC_I_');
2560 IF (BSC_METADATA_OPTIMIZER_PKG.gMaxB>BSC_METADATA_OPTIMIZER_PKG.gMaxI) THEN
2561 BSC_METADATA_OPTIMIZER_PKG.gMaxI := BSC_METADATA_OPTIMIZER_PKG.gMaxB;
2562 ELSE
2563 BSC_METADATA_OPTIMIZER_PKG.gMaxB := BSC_METADATA_OPTIMIZER_PKG.gMaxI;
2564 END IF;
2565 BSC_MO_HELPER_PKG.writeTmp('gMaxT = '||BSC_METADATA_OPTIMIZER_PKG.gMaxT||', gMaxB is '||
2566 BSC_METADATA_OPTIMIZER_PKG.gMaxB||', gMaxI='||BSC_METADATA_OPTIMIZER_PKG.gMaxI);
2567 IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2568 return;
2569 END IF;
2570 BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Normal', FND_LOG.LEVEL_STATEMENT, true);
2571 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables - Normal');
2572 deduce_bt_tables(False);
2573
2574 BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Targets', FND_LOG.LEVEL_STATEMENT, true);
2575 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables - Targets');
2576 deduce_bt_tables (True );--For targets
2577
2578 IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2579 return;
2580 END IF;
2581 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2582 BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT);
2583 END IF;
2584 BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT, true);
2585 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables_precalc');
2586 deduce_bt_tables_precalc;
2587
2588 IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2589 return;
2590 END IF;
2591
2592 --Connect base indicator tables with temporal tables
2593 BSC_MO_HELPER_PKG.writeTmp('Calling connect_s_to_b_tables', FND_LOG.LEVEL_STATEMENT, true);
2594 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling connect_s_to_b_tables');
2595 connect_s_to_b_tables;
2596
2597 --Add each temporal table to the collection of system tables
2598 BSC_MO_HELPER_PKG.writeTmp('Calling add_to_gtables', FND_LOG.LEVEL_STATEMENT, true);
2599
2600 add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
2601 add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2602 add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2603
2604 --Make input tables and connect them to the base tables
2605 BSC_MO_HELPER_PKG.writeTmp('Calling connect_i_to_b_tables', FND_LOG.LEVEL_STATEMENT, true);
2606 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling connect_i_to_b_tables');
2607 connect_i_to_b_tables;
2608
2609
2610 --BSC-MV note: Stating from the tables in garrTablesUpgradeT()
2611 --we need to get all the origin tables until the input tables and
2612 --insert them into garrTablesUpgrade()
2613 --Then we need to add those tables in gTablas.
2614 --Loader configuration will be re-done and we need all those tables in gTablas
2615 If bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 1 Then
2616 bsc_metadata_optimizer_pkg.gnumTablesUpgrade := 0;
2617 BSC_MO_LOADER_CONFIG_PKG.InsertOriginTables (bsc_metadata_optimizer_pkg.garrTablesUpgradeT,
2618 bsc_metadata_optimizer_pkg.garrTablesUpgrade);
2619 bsc_metadata_optimizer_pkg.gnumTablesUpgrade := bsc_metadata_optimizer_pkg.garrTablesUpgrade.count;
2620 load_upgrade_tables_db;
2621 End If;
2622 BSC_MO_HELPER_PKG.writeTmp('-------------------------------------', FND_LOG.LEVEL_STATEMENT, true);
2623 BSC_MO_HELPER_PKG.writeTmp('Completed InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2624
2625 EXCEPTION WHEN OTHERS THEN
2626 l_error := sqlerrm;
2627 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InputTables : '||l_error);
2628 BSC_MO_HELPER_PKG.TerminateWithError ('BSC_ITABLE_PROD_FAILED', 'InputTables');
2629 raise;
2630 End ;
2631
2632
2633 -- Load the metadata for the specified table into memory variables
2634 PROCEDURE Load_Table_From_DB(p_table IN VARCHAR2, p_indicator_num NUMBER, p_dim_set NUMBER) IS
2635
2636 CURSOR cKeys IS
2637 SELECT column_name
2638 FROM
2639 bsc_db_tables_cols cols
2640 WHERE
2641 cols.table_name = p_table
2642 AND cols.column_type = 'P'
2643 ORDER BY cols.column_name;
2644
2645 CURSOR cData(p_owner varchar2) IS
2646 SELECT cols.column_name, nvl(cols.source, 'BSC'), nvl(dbcols.measure_group_id, -1)
2647 FROM
2648 bsc_db_tables_cols cols,
2649 all_tab_columns tabcols,
2650 bsc_sys_measures sysm,
2651 bsc_db_measure_cols_vl dbcols
2652 WHERE
2653 cols.table_name = p_table
2654 AND cols.table_name = tabcols.table_name
2655 and cols.column_name = tabcols.column_name
2656 and tabcols.owner = p_owner
2657 AND cols.column_type = 'A'
2658 AND cols.column_name = sysm.measure_col
2659 AND cols.column_name = dbcols.measure_col
2660 ORDER BY cols.column_name;
2661
2662 CURSOR cZeroCode (p_column in varchar2) IS
2663 select source_column, nvl(calculation_type, 0) from
2664 bsc_db_Tables_cols cols,
2665 bsc_db_calculations calc
2666 where
2667 cols.table_name = calc.table_name (+)
2668 and cols.table_name = p_table
2669 and cols.column_type = 'P'
2670 and calc.calculation_type (+)= 4
2671 and cols.column_name = p_column;
2672
2673 CURSOR cOriginTables IS
2674 SELECT source_table_name FROM
2675 BSC_DB_TABLES_RELS
2676 WHERE table_name=p_table;
2677
2678 l_stmt varchar2(1000);
2679 l_column VARCHAR2(100);
2680 l_source VARCHAR2(100);
2681 l_measure_group number;
2682
2683 l_kpis_using_table BSC_METADATA_OPTIMIZER_PKG.tab_clsKPIDimSet ;
2684 ------------------------------------------------------------
2685 l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2686 l_key_list BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2687 l_source_column VARCHAR2(100);
2688 l_calc_type NUMBER;
2689 l_filter_view VARCHAR2(1000);
2690 ------------------------------------------------------------
2691 l_data BSC_METADATA_OPTIMIZER_PKG.clsDataField;
2692 l_data_list BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2693 ------------------------------------------------------------
2694 l_index NUMBER := 0;
2695 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
2696 l_optimizationMode NUMBER;
2697 l_src_table VARCHAR2(100);
2698 l_state varchar2(4000);
2699
2700 BEGIN
2701 bsc_mo_helper_pkg.writeTmp('Loading table '||p_table||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2702 l_kpis_using_table := BSC_MO_HELPER_PKG.find_objectives_for_table(p_table);
2703 IF (l_kpis_using_table.count=0) THEN
2704 return;
2705 END IF;
2706 bsc_mo_helper_pkg.writeTmp('# of objectives using this table= '||l_kpis_using_table.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2707 OPEN cKeys;
2708 LOOP
2709 FETCH cKeys INTO l_column;
2710 EXIT WHEN cKeys%NOTFOUND;
2711 l_key.tableName := p_table;
2712 l_key.keyName := l_column;
2713 -- find out source column and zero code requirement
2714 OPEN cZeroCode(l_column);
2715 FETCH cZeroCode INTO l_source_column, l_calc_type;
2716 CLOSE cZeroCode;
2717 l_key.origin := l_source_column;
2718 IF (l_calc_type=4) THEN
2719 l_key.needsCode0 := true;
2720 l_key.calculateCode0 := true;
2721 END IF;
2722 -- find out if filters exist
2723 IF BSC_MO_HELPER_PKG.filters_exist(
2724 l_kpis_using_table(0).kpi_number,
2725 l_kpis_using_table(0).dim_set_id,
2726 l_column,
2727 l_filter_view ) THEN
2728 l_key.filterViewName := l_filter_view;
2729 END IF;
2730 l_key_list(l_key_list.count) := l_key;
2731 END LOOP;
2732 CLOSE cKeys;
2733 l_state := 'Loaded keys '||bsc_mo_helper_pkg.get_time;
2734 -- now onto the measure columns
2735 OPEN cData(bsc_metadata_optimizer_pkg.gBSCSchema);
2736 LOOP
2737 FETCH cData INTO l_column, l_source, l_measure_group;
2738 EXIT WHEN cData%NOTFOUND;
2739 l_data.tableName := p_table;
2740 l_data.fieldName := l_column;
2741 l_data.source := l_source;
2742 l_data.InternalColumnType := 0;
2743 l_data.measureGroup := l_measure_group;
2744
2745 l_data_list(l_data_list.count) := l_data;
2746 END LOOP;
2747 CLOSE cData;
2748 l_state := l_state||' Loaded Data.count= '||l_data_list.count;
2749 l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_kpis_using_table(0).kpi_number);
2750 IF (l_index <> -1) THEN
2751 l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).optimizationMode ;
2752 ELSE
2753 l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_kpis_using_table(0).kpi_number, 'DB_TRANSFORM', 1);
2754 END IF;
2755 l_table.Name := p_table;
2756 l_table.Type := 1;-- base, temporal or summary
2757 l_table.keys := l_key_list;
2758 l_table.data := l_data_list;
2759 l_table.Periodicity := BSC_MO_HELPER_PKG.getPeriodicityForTable(p_table);
2760 l_table.originTable := null;--BSC_MO_HELPER_PKG.getSourceTable(p_table);
2761 l_table.Indicator := l_kpis_using_table(0).kpi_number;
2762 l_table.Configuration := l_kpis_using_table(0).dim_set_id;
2763 l_table.EDW_Flag := 0;
2764 l_table.IsTargetTable := false;
2765 l_table.HasTargets := false;
2766 l_table.UsedForTargets := false;
2767 l_table.upgradeFlag := 0;
2768 if (l_data_list.count>0) then
2769 l_table.measureGroup := l_data_list(l_data_list.first).measureGroup;
2770 else
2771 l_table.measureGroup := get_measure_group(p_table, l_data_list, p_indicator_num, p_dim_set);
2772 end if;
2773 bsc_mo_helper_pkg.writeTmp('measure group = '||l_table.measureGroup);
2774 -- Important, flag that this is as a production table, so it isnt dropped and recreated !
2775 l_table.isProductionTable := true;
2776 l_table.isProductionTableAltered := false;
2777 l_state := l_state || ' Loaded other properties ';
2778 FOR i IN cOriginTables LOOP
2779 l_table.originTable := l_table.originTable||i.source_table_name||',';
2780 END LOOP;
2781 IF l_table.originTable IS NOT NULL THEN
2782 l_table.originTable := substr(l_table.originTable, 1, length(l_table.originTable)-1);
2783 END IF;
2784 l_state:= l_state||' Loaded origin tables ';
2785
2786 -- For AW
2787 IF (bsc_mo_helper_pkg.getKPIPropertyValue(l_table.Indicator, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1) = 2) THEN
2788 l_table.impl_type := 2;
2789 ELSE
2790 l_table.impl_type := 1;
2791 END IF;
2792 IF (l_optimizationMode = 0 ) THEN -- precalc
2793 l_state:=l_state|| '
2794 '||' Adding table '||p_table||' to g_bt_tables_precalc';
2795 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc.count) := l_table;
2796 ELSIF ( l_optimizationMode = 2 ) THEN -- targets
2797 l_state := l_state||'
2798 '||'Adding table '||p_table||' to g_bt_tables_tgt';
2799 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt.count) := l_table;
2800 ELSE
2801 l_state:=l_state||'
2802 '||'Adding table '||p_table||' to g_bt_tables';
2803 BSC_METADATA_OPTIMIZER_PKG.g_bt_tables(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables.count) := l_table;
2804 END IF;
2805 l_state := l_state||'
2806 Loaded table '||p_table;
2807 EXCEPTION WHEN OTHERS THEN
2808 BSC_MO_HELPER_PKG.writeTmp('state = '||l_state, FND_LOG.LEVEL_EXCEPTION, true);
2809 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Load_Table_From_DB : '||sqlerrm);
2810
2811 BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'Load_Table_From_DB');
2812 raise;
2813 END;
2814
2815 FUNCTION is_production_table(p_table_name IN VARCHAR2, p_indicator OUT NOCOPY NUMBER, p_dim_set OUT NOCOPY NUMBER) return boolean
2816 IS
2817 l_stmt VARCHAR2(1000) ;
2818 cv CurTyp;
2819 BEGIN
2820 l_stmt :=
2821 ' select kpi.indicator, tmp.dim_set_id
2822 from bsc_kpis_vl kpi,
2823 (
2824 select substr(table_name, instr(table_name, ''_'', 1, 2)+1,
2825 instr(table_name, ''_'', 1, 3)-instr(table_name, ''_'', 1, 2)-1) indicator,
2826 substr(table_name, instr(table_name, ''_'', 1, 3)+1,
2827 instr(table_name, ''_'', 1, 4)-instr(table_name, ''_'', 1, 3)-1) dim_set_id
2828 from bsc_db_tables_rels
2829 where table_name like ''BSC_S%''
2830 connect by prior table_name = source_table_name
2831 start with source_table_name = :1
2832 ) tmp
2833 where kpi.indicator = tmp.indicator
2834 and prototype_flag not in (2,3) -- no need for 4 as the B table typically doesnt get dropped for 4
2835 and kpi.share_flag<>2
2836 and rownum=1';
2837 OPEN cv FOR l_stmt USING p_table_name;
2838 FETCH cv INTO p_indicator, p_dim_set;
2839 CLOSE cv;
2840 IF (p_indicator is not null) THEN -- atleast one production table is using this
2841 return true;
2842 ELSE
2843 return false;
2844 END IF;
2845 END;
2846 -- Load the production tables which have measures belonging to indicators
2847 -- in the current metadata run
2848 PROCEDURE LOAD_B_T_TABLES_FROM_DB IS
2849 inMeasures VARCHAR2(1000);
2850 cv CurTyp;
2851 l_table VARCHAR2(100);
2852 l_src_table VARCHAR2(100);
2853 i NUMBER;
2854 l_stmt VARCHAR2(1000);
2855 l_indicator_num number;
2856 l_dim_set number;
2857 BEGIN
2858 BSC_MO_HELPER_PKG.writeTmp('Inside LOAD_B_T_TABLES_FROM_DB, system time is '||
2859 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2860 l_stmt := 'select distinct table_name from bsc_db_tables where (table_name like ''BSC_B%'' or table_name like ''BSC_T%'' ) and table_type=1';
2861 OPEN cv FOR l_stmt ;
2862 LOOP
2863 FETCH cv INTO l_table;
2864 EXIT WHEN cv%NOTFOUND;
2865 IF is_production_table(l_table, l_indicator_num, l_dim_set) THEN
2866 BSC_MO_HELPER_PKG.writeTmp('Loading production table '||l_table||' into memory '||BSC_MO_HELPER_PKG.get_time, FND_LOG.LEVEL_PROCEDURE, false);
2867 Load_Table_From_DB(l_table, l_indicator_num, l_dim_set);
2868 END IF;
2869 END LOOP;
2870
2871 -- Intialize the disaggregations in the Production tables - Targets
2872 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures for Production tables - Targets');
2873 BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures for Production tables - Targets', FND_LOG.LEVEL_STATEMENT, true);
2874 init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2875
2876 -- Intialize the disaggregations in the Production tables
2877 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures for Production tables - Normal');
2878 BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures for Production tables - Normal', FND_LOG.LEVEL_STATEMENT, true);
2879 init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
2880
2881 -- Intialize the disaggregations in the Production tables - precalc
2882 BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc for Production tables');
2883 BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc for Production tables', FND_LOG.LEVEL_STATEMENT, true);
2884 init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2885
2886 BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2888 EXCEPTION WHEN OTHERS THEN
2889 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in LOAD_B_T_TABLES_FROM_DB : '||sqlerrm);
2890 BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'LOAD_B_T_TABLES_FROM_DB');
2891 raise;
2892 END;
2893
2894 END BSC_MO_INPUT_TABLE_PKG ;
[Home] [Help]