[Home] [Help]
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:
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:
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
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;
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
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
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;
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);
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);
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
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
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
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
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;
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;
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;
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
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;
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;
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;
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
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;
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);
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
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
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;
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));
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';
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;
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;
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;
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;
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
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
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;
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;
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
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;
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;
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;
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;
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;
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
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
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
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:
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;
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:
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);
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;
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;
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;
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
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;
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;
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
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
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;
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;
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;
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;
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: <
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;
754: l_table.originTable := l_table.originTable||l_table_origin;
755: End If;
756: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
757: <
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);
755: End If;
756: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
757: <
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;
757: <
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;
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;
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;
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;
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;
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;
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:
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);
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;
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;
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,
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);
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;
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;
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 = '||
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);
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;
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);
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:
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;
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;
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;
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
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;
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;
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, ',');
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('');
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);
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(
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,
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(
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;
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 '||
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
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
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;
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;
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);
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
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);
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);
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;
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;
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;
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;
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;
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;
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:
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);
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;
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
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
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);
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;
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,
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
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;
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
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;
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;
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
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
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;
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;
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;
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
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);
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;
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
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;
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:
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
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
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;
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
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);
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);
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;
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)
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;
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;
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;
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;
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;
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, ',' );
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;
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;
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
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;
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;
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;
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;
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);
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;
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;
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;
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;
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;
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 ;
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;
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:
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
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;
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('---------------------------------');
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
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
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);
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,
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;
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);
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;
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
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;
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;
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);
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;
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;
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.
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;
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;
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
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);
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.
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
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;
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
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);
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 ');
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);
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
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;
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);
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 ');
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);
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;
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
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
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;
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;
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;
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;
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;
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;
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 ;
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;
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;
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;
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
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
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;
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
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
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;
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:
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;
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
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
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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
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
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;
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
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;
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
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;
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;
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;
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
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
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;
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,
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;
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;
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
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.
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;
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
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
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:
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;
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;
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 = ');
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:
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);
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 ;
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;
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;
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;
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;
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;
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;
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;
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);
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;
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
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
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;
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:
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
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;
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);
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;
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
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 );
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
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);
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;
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,
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
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;
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
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
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;
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
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;
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;
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;
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;
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 = ');
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;
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);
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);
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;
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);
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);
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);
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;
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;
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;
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;
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:
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:
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
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);
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
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);
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
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
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);
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
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_');
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;
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
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;
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;
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 '||
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
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;
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);
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');
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');
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;
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);
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');
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;
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
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);
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
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);
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');
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;
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;
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;
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);
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);
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);
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);
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: ------------------------------------------------------------
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;
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;
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:
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;
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);
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;
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;
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;
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;
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
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
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);
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
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);
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 '||
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