[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:
40: END LOOP;
41: CLOSE cv;
42: return l_data;
43: EXCEPTION WHEN OTHERS THEN
44: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_measures_for_table:'||sqlerrm);
45: raise;
46: END;
47:
48: FUNCTION get_measure_group(p_table IN VARCHAR2, p_data IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField, p_indicator NUMBER, p_dim_set NUMBER) return NUMBER IS
59: IF (p_data.count =0) then
60: return l_measure_group;
61: END IF;
62: l_obj_measures := get_measures_for_table('BSC_S_'||p_indicator||'_'||p_dim_set||'%');
63: bsc_mo_helper_pkg.writeTmp('# of measures in BSC_S_'||p_indicator||'_'||p_dim_set ||'% is '||l_obj_measures.count||', # in p_data='||p_data.count);
64:
65: FOR i IN p_data.first..p_data.last LOOP
66: FOR j IN 1..l_obj_measures.count LOOP
67: IF (p_data(i).fieldName = l_obj_measures(j).fieldName AND
71: END LOOP;
72: END LOOP;
73: return l_measure_group;
74: EXCEPTION WHEN OTHERS THEN
75: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_measure_group:'||sqlerrm);
76: raise;
77: END;
78:
79:
99:
100: BEGIN
101:
102: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
103: bsc_mo_helper_pkg.writeTmp('set_origin_table_from_db for table='||KpiTable.name);
104: END IF;
105: l_index := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, KpiTable.Indicator);
106: If BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).OptimizationMode = 0 Then
107: --The indicator is pre-calculated.
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
164: IF (KpiTable.originTable IS NOT NULL) THEN
165: KpiTable.originTable := KpiTable.originTable ||',';
166: END IF;
167: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
168: bsc_mo_helper_pkg.writeTmp('1. Adding Origin table for '||KpiTable.name||' = '||SourceTableName);
169: END IF;
170: KpiTable.originTable := KpiTable.originTable ||SourceTableName;
171: --Add the table to the gloabel array garrTablesUpgrade
172: If Not bsc_mo_helper_pkg.searchStringExists(
168: bsc_mo_helper_pkg.writeTmp('1. Adding Origin table for '||KpiTable.name||' = '||SourceTableName);
169: END IF;
170: KpiTable.originTable := KpiTable.originTable ||SourceTableName;
171: --Add the table to the gloabel array garrTablesUpgrade
172: If Not bsc_mo_helper_pkg.searchStringExists(
173: bsc_metadata_optimizer_pkg.garrTablesUpgradeT,
174: bsc_metadata_optimizer_pkg.gnumTablesUpgradeT,
175: SourceTableName) Then
176: bsc_metadata_optimizer_pkg.garrTablesUpgradeT(bsc_metadata_optimizer_pkg.gnumTablesUpgradeT) := SourceTableName;
196: l_counter := KpiTable.keys.next(l_counter);
197: END LOOP;
198: EXCEPTION WHEN OTHERS THEN
199: l_error := sqlerrm;
200: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in set_origin_table_from_db : '||l_error);
201: raise;
202: End ;
203:
204: PROCEDURE load_upgrade_tables_db IS
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
253: END IF;
254:
255: For i IN 0..bsc_metadata_optimizer_pkg.gnumTablesUpgrade - 1 LOOP
256: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
257: BSC_MO_HELPER_PKG.writeTmp('');
258: BSC_MO_HELPER_PKG.writeTmp('Processing table '||bsc_metadata_optimizer_pkg.garrTablesUpgrade(i));
259: END IF;
260: OPEN cTable(bsc_metadata_optimizer_pkg.garrTablesUpgrade(i));
261: FETCH cTable INTO cTableRow;
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';
321: END IF;
322: l_stack := l_stack || g_newline||'Step 3.4';
323: l_table.originTable := l_table.originTable || cTableRels0Row.SOURCE_TABLE_NAME;
324: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
325: bsc_mo_helper_pkg.writeTmp('Adding Origin table '||cTableRels0Row.SOURCE_TABLE_NAME);
326: END IF;
327: END LOOP;
328: Close cTableRels0;
329: l_stack := l_stack || g_newline||'Step 4';
340: END IF;
341: l_stack := l_stack || g_newline||'Step 4.4';
342: l_stack := l_stack || g_newline||'Adding Origin table '||cTableRels1Row.SOURCE_TABLE_NAME;
343: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
344: bsc_mo_helper_pkg.writeTmp('Adding Origin table '||cTableRels1Row.SOURCE_TABLE_NAME);
345: END IF;
346: l_table.originTable := l_table.originTable || cTableRels1Row.SOURCE_TABLE_NAME;
347: END LOOP;
348: Close cTableRels1;
350: l_table.Indicator := 0;
351: l_table.Configuration := 0;
352: l_table.upgradeFlag := 1;
353: --Add table to collection
354: BSC_MO_HELPER_PKG.addTable(l_table, l_table.keys, l_table.data, 'load_upgrade_tables_db');
355: --l_table_keys.delete;
356: --l_table_measures.Delete;
357: l_table := BSC_MO_HELPER_PKG.new_clsTable;
358: End If;
353: --Add table to collection
354: BSC_MO_HELPER_PKG.addTable(l_table, l_table.keys, l_table.data, 'load_upgrade_tables_db');
355: --l_table_keys.delete;
356: --l_table_measures.Delete;
357: l_table := BSC_MO_HELPER_PKG.new_clsTable;
358: End If;
359: Close cTable;
360: IF (length(l_stack) > 30000) THEN
361: l_stack := null;
361: l_stack := null;
362: END IF;
363: END LOOP;
364: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
365: BSC_MO_HELPER_PKG.writeTmp('Completed load_upgrade_tables_db');
366: END IF;
367: EXCEPTION WHEN OTHERS THEN
368: l_error := sqlerrm;
369: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in load_upgrade_tables_db : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
365: BSC_MO_HELPER_PKG.writeTmp('Completed load_upgrade_tables_db');
366: END IF;
367: EXCEPTION WHEN OTHERS THEN
368: l_error := sqlerrm;
369: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in load_upgrade_tables_db : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
370: BSC_MO_HELPER_PKG.writeTmp('l_stack ='||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
371: raise;
372: End ;
373:
366: END IF;
367: EXCEPTION WHEN OTHERS THEN
368: l_error := sqlerrm;
369: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in load_upgrade_tables_db : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
370: BSC_MO_HELPER_PKG.writeTmp('l_stack ='||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
371: raise;
372: End ;
373:
374:
403: END LOOP;
404: return true;
405: EXCEPTION WHEN OTHERS THEN
406: l_error := sqlerrm;
407: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in all_key_comb_registered : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
408: raise;
409: End;
410:
411:
429: l_origins DBMS_SQL.VARCHAR2_TABLE;
430: l_error VARCHAR2(1000);
431: BEGIN
432: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
433: BSC_MO_HELPER_PKG.writeTmp('Inside TableOriginExists, key_combinations = ', FND_LOG.LEVEL_PROCEDURE);
434: BSC_MO_HELPER_PKG.writeTmp(' Parameter p_table_name='||p_table_name
435: ||', and p_table_origins is '||p_table_origins, FND_LOG.LEVEL_STATEMENT);
436: END IF;
437: l_origins := BSC_MO_HELPER_PKG.getDecomposedString(p_table_origins, ',');
430: l_error VARCHAR2(1000);
431: BEGIN
432: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
433: BSC_MO_HELPER_PKG.writeTmp('Inside TableOriginExists, key_combinations = ', FND_LOG.LEVEL_PROCEDURE);
434: BSC_MO_HELPER_PKG.writeTmp(' Parameter p_table_name='||p_table_name
435: ||', and p_table_origins is '||p_table_origins, FND_LOG.LEVEL_STATEMENT);
436: END IF;
437: l_origins := BSC_MO_HELPER_PKG.getDecomposedString(p_table_origins, ',');
438: IF (l_origins.count = 0) THEN
433: BSC_MO_HELPER_PKG.writeTmp('Inside TableOriginExists, key_combinations = ', FND_LOG.LEVEL_PROCEDURE);
434: BSC_MO_HELPER_PKG.writeTmp(' Parameter p_table_name='||p_table_name
435: ||', and p_table_origins is '||p_table_origins, FND_LOG.LEVEL_STATEMENT);
436: END IF;
437: l_origins := BSC_MO_HELPER_PKG.getDecomposedString(p_table_origins, ',');
438: IF (l_origins.count = 0) THEN
439: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
440: BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning false', FND_LOG.LEVEL_PROCEDURE);
441: END IF;
436: END IF;
437: l_origins := BSC_MO_HELPER_PKG.getDecomposedString(p_table_origins, ',');
438: IF (l_origins.count = 0) THEN
439: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
440: BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning false', FND_LOG.LEVEL_PROCEDURE);
441: END IF;
442: return false;
443: END IF;
444: l_count1 := l_origins.first;
445: LOOP
446: l_table := l_origins(l_count1);
447: If UPPER(l_table) = UPPER(p_table_name) Then
448: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
449: BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning true', FND_LOG.LEVEL_PROCEDURE);
450: END IF;
451: return true;
452: End If;
453: EXIT WHEN l_count1= l_origins.last;
453: EXIT WHEN l_count1= l_origins.last;
454: l_count1 := l_origins.next(l_count1);
455: END LOOP;
456: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
457: BSC_MO_HELPER_PKG.writeTmp('Completed TableOriginExists, returning false', FND_LOG.LEVEL_PROCEDURE);
458: END IF;
459: return false;
460: EXCEPTION WHEN OTHERS THEN
461: l_error := sqlerrm;
458: END IF;
459: return false;
460: EXCEPTION WHEN OTHERS THEN
461: l_error := sqlerrm;
462: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in TableOriginExists : '||l_error);
463: raise;
464: End ;
465: --****************************************************************************
466: -- IndexTablaTemporalMismaDesagyCodAgrupCampo : get_matching_tables
500: l_return_table DBMS_SQL.NUMBER_TABLE ;
501: BEGIN
502: IF (p_BTTables.count = 0) THEN
503: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
504: BSC_MO_HELPER_PKG.writeTmp('Done with get_matching_tables, returning -1', FND_LOG.LEVEL_PROCEDURE);
505: END IF;
506: return l_return_table;
507: END IF;
508:
506: return l_return_table;
507: END IF;
508:
509: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
510: BSC_MO_HELPER_PKG.writeTmp('Within get_matching_tables, measure='||p_measure_col||', source='||p_measure_source||', Periodicity = '||pPeriodicity||', dataFieldGroup ='||dataFieldGroup||', impl_type='||p_impl_type);
511: END IF;
512: i := p_BTTables.first;
513: LOOP
514: toBeConsidered := True;
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
540: If Not BSC_MO_INDICATOR_PKG.keyFieldExists(p_keys, l_key.keyName) Then
541: keysEqual := False;
542: Exit ;
543: Else
544: l_temp := BSC_MO_HELPER_PKG.findIndex(p_keys, l_key.keyName);
545: If UPPER(l_key.FilterViewName) <> UPPER(p_keys(l_temp).FilterViewName) Then
546: keysEqual := False;
547: Exit ;
548: End If;
557: BSC_MO_INDICATOR_PKG.DataFieldExistsforSource(p_BTTables(i).Data, p_measure_col, p_measure_source)=false THEN
558: null;
559: ELSE
560: l_return_table(l_return_table.count) := i;
561: bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
562: END IF;
563: ELSE
564: l_return_table(l_return_table.count) := i;
565: bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
561: bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
562: END IF;
563: ELSE
564: l_return_table(l_return_table.count) := i;
565: bsc_mo_helper_pkg.writeTmp('Adding table '||p_BTTables(i).name);
566: END IF;
567: End If;
568: End If;
569: End If;
573: END LOOP;
574: return l_return_table;
575: EXCEPTION WHEN OTHERS THEN
576: l_error := sqlerrm;
577: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_matching_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
578: IF (l_temp=-1) THEN
579: BSC_MO_HELPER_PKG.writeTmp('Measure '||p_BTTables(i).Data(0).fieldName||', source=' ||p_BTTables(i).Data(0).source||' does not exist in the list of measures in bsc_sys_measures.', FND_LOG.LEVEL_STATEMENT, true);
580: END IF;
581: raise;
575: EXCEPTION WHEN OTHERS THEN
576: l_error := sqlerrm;
577: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_matching_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
578: IF (l_temp=-1) THEN
579: BSC_MO_HELPER_PKG.writeTmp('Measure '||p_BTTables(i).Data(0).fieldName||', source=' ||p_BTTables(i).Data(0).source||' does not exist in the list of measures in bsc_sys_measures.', FND_LOG.LEVEL_STATEMENT, true);
580: END IF;
581: raise;
582: End ;
583:
644: return l_max;
645:
646: EXCEPTION WHEN OTHERS THEN
647: l_error := sqlerrm;
648: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in getMaxTableIndex : '||l_error);
649: raise;
650:
651: End;
652:
680: l_error VARCHAR2(1000);
681: l_table_null BSC_METADATA_OPTIMIZER_PKG.clsTable;
682: BEGIN
683: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
684: BSC_MO_HELPER_PKG.writeTmp('Inside connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
685: END IF;
686: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
687: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
688: BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
684: BSC_MO_HELPER_PKG.writeTmp('Inside connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
685: END IF;
686: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
687: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
688: BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
689: END IF;
690: return;
691: END IF;
692: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
696: IF (l_table.isProductionTable) THEN
697: goto ignore;
698: END IF;
699: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
700: BSC_MO_HELPER_PKG.writeTmp('');
701: BSC_MO_HELPER_PKG.writeTmp('Processing table gTables('||l_index1||
702: ') : '||l_table.name, FND_LOG.LEVEL_STATEMENT);
703: END IF;
704: Tabla_Origin.delete;
697: goto ignore;
698: END IF;
699: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
700: BSC_MO_HELPER_PKG.writeTmp('');
701: BSC_MO_HELPER_PKG.writeTmp('Processing table gTables('||l_index1||
702: ') : '||l_table.name, FND_LOG.LEVEL_STATEMENT);
703: END IF;
704: Tabla_Origin.delete;
705: Tabla_Origin := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
701: BSC_MO_HELPER_PKG.writeTmp('Processing table gTables('||l_index1||
702: ') : '||l_table.name, FND_LOG.LEVEL_STATEMENT);
703: END IF;
704: Tabla_Origin.delete;
705: Tabla_Origin := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
706: --For tables with no origin (base tables)
707: If Tabla_origin.Count = 0 And l_table.Type <> 0 Then
708: TablaE := bsc_mo_helper_pkg.new_clsTable;
709: TablaE.Name := 'BSC_I_' ||( BSC_METADATA_OPTIMIZER_PKG.gMaxI + 1);
704: Tabla_Origin.delete;
705: Tabla_Origin := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
706: --For tables with no origin (base tables)
707: If Tabla_origin.Count = 0 And l_table.Type <> 0 Then
708: TablaE := bsc_mo_helper_pkg.new_clsTable;
709: TablaE.Name := 'BSC_I_' ||( BSC_METADATA_OPTIMIZER_PKG.gMaxI + 1);
710: BSC_METADATA_OPTIMIZER_PKG.gMaxI := BSC_METADATA_OPTIMIZER_PKG.gMaxI + 1;
711: TablaE.Type := 0;
712: TablaE.Periodicity := l_table.Periodicity;
717: --Key columns
718: IF (l_table.keys.count>0)THEN
719: l_index2 := l_table.keys.first;
720: LOOP
721: l_keyE := bsc_mo_helper_pkg.new_clskeyfield;
722: l_keyE := l_table.keys(l_index2);
723: TablaE.keys(TablaE.keys.count) := l_keyE;
724: l_table.keys(L_INDEX2).Origin := l_table.keys(L_INDEX2).keyName;
725: EXIT WHEN l_index2 = l_table.keys.last;
730: --Data columns
731: IF (l_table.data.count>0) THEN
732: l_index2 := l_table.data.first;
733: LOOP
734: l_measureE := bsc_mo_helper_pkg.new_clsDataField;
735: l_measure := l_table.data(l_index2);
736: l_measureE.fieldName := l_measure.fieldName;
737: l_measureE.source := l_measure.source;
738: l_measureE.measureGroup := l_measure.measureGroup;
742: EXIT WHEN l_index2 = l_table.data.last;
743: l_index2 := l_table.data.next(l_index2);
744: END LOOP;
745: END IF;
746: BSC_MO_HELPER_PKG.addTable(TablaE, TablaE.keys, TablaE.Data, 'connect_i_to_b_tables');
747: l_table_origin := TablaE.Name;
748: IF (l_table.originTable IS NOT NULL ) THEN
749: l_table.originTable := l_table.originTable||',';
750: END IF;
748: IF (l_table.originTable IS NOT NULL ) THEN
749: l_table.originTable := l_table.originTable||',';
750: END IF;
751: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
752: bsc_mo_helper_pkg.writeTmp('2. Adding Origin table for '||l_table.name||' = '||l_table_origin);
753: END IF;
754: l_table.originTable := l_table.originTable||l_table_origin;
755: End If;
756: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
758: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
759: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
760: END LOOP;
761: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
762: BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
763: END IF;
764: EXCEPTION WHEN OTHERS THEN
765: l_error := sqlerrm;
766: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_i_to_b_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
762: BSC_MO_HELPER_PKG.writeTmp('Completed connect_i_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
763: END IF;
764: EXCEPTION WHEN OTHERS THEN
765: l_error := sqlerrm;
766: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_i_to_b_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
767: raise;
768: End;
769: --****************************************************************************
770: -- add_to_gtables: AdicTablasTempATablasSistema
789: BEGIN
790:
791: IF (p_tables.count =0) THEN
792: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
793: BSC_MO_HELPER_PKG.writeTmp('Done with add_to_gtables, p_tables.count=0',
794: FND_LOG.LEVEL_PROCEDURE);
795: END IF;
796: return;
797: END IF;
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');
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
804: EXIT WHEN l_index1 = p_tables.last;
805: l_index1 := p_tables.next(l_index1);
806: END LOOP;
807: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
808: BSC_MO_HELPER_PKG.writeTmp('Completed add_to_gtables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
809: END IF;
810: EXCEPTION WHEN OTHERS THEN
811: l_error := sqlerrm;
812: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_gtables : '||l_error);
808: BSC_MO_HELPER_PKG.writeTmp('Completed add_to_gtables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
809: END IF;
810: EXCEPTION WHEN OTHERS THEN
811: l_error := sqlerrm;
812: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_gtables : '||l_error);
813: raise;
814: End;
815:
816:
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 = '||
859: end if;
860: l_measure_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, fieldName, p_source);
861: l_measure_group := BSC_METADATA_OPTIMIZER_PKG.gLov(l_measure_index).groupCode;
862: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
863: BSC_MO_HELPER_PKG.writeTmp('Inside get_origin_table, pPeriodicity='||pPeriodicity
864: ||', fieldName ='||fieldName||', source='||p_source||', measure_group='||p_measure_group||', p_impl_type='||p_impl_type||', p_tables.count = '||
865: p_tables.count, FND_LOG.LEVEL_PROCEDURE);
866: END IF;
867: l_index1 := p_tables.first;
880: l_key := L_Table.keys(l_index2);
881: If Not BSC_MO_INDICATOR_PKG.keyFieldExists(keys, l_key.keyName) Then
882: keysEqual := False;
883: Else
884: l_temp := BSC_MO_HELPER_PKG.findIndex(keys, l_key.keyName);
885: If UPPER(l_key.FilterViewName) <> UPPER(keys(l_temp).FilterViewName) Then
886: keysEqual := False;
887: Exit ;
888: End If;
891: l_index2 := L_Table.keys.next(l_index2);
892: END LOOP;
893: If keysEqual Then
894: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
895: BSC_MO_HELPER_PKG.writeTmp('Compl get_origin_table, returning '||
896: L_Table.Name, FND_LOG.LEVEL_PROCEDURE);
897: BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||
898: (sysdate-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
899: END IF;
893: If keysEqual Then
894: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
895: BSC_MO_HELPER_PKG.writeTmp('Compl get_origin_table, returning '||
896: L_Table.Name, FND_LOG.LEVEL_PROCEDURE);
897: BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||
898: (sysdate-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
899: END IF;
900: return L_Table.Name;
901: End If;
905: EXIT WHEN l_index1 = p_tables.last;
906: l_index1 := p_tables.next(l_index1);
907: END LOOP;
908: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
909: BSC_MO_HELPER_PKG.writeTmp('Compl get_origin_table, returning null', FND_LOG.LEVEL_PROCEDURE);
910: END IF;
911: return null;
912:
913: EXCEPTION WHEN OTHERS THEN
911: return null;
912:
913: EXCEPTION WHEN OTHERS THEN
914: l_error := sqlerrm;
915: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_origin_table : '||l_error);
916: raise;
917: End ;
918: --****************************************************************************
919: -- ConectarTablasIndicadoresConTemporales : connect_s_to_b_tables
940: l_error VARCHAR2(1000);
941:
942: BEGIN
943: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
944: BSC_MO_HELPER_PKG.writeTmp('Inside connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
945: BSC_MO_HELPER_PKG.writeTmp('System time is '||to_char(sysdate, 'hh24:mi:ss'), FND_LOG.LEVEL_STATEMENT);
946: END IF;
947: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
948: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
941:
942: BEGIN
943: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
944: BSC_MO_HELPER_PKG.writeTmp('Inside connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
945: BSC_MO_HELPER_PKG.writeTmp('System time is '||to_char(sysdate, 'hh24:mi:ss'), FND_LOG.LEVEL_STATEMENT);
946: END IF;
947: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
948: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
949: BSC_MO_HELPER_PKG.writeTmp('Compl connect_s_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
945: BSC_MO_HELPER_PKG.writeTmp('System time is '||to_char(sysdate, 'hh24:mi:ss'), FND_LOG.LEVEL_STATEMENT);
946: END IF;
947: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count=0) THEN
948: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
949: BSC_MO_HELPER_PKG.writeTmp('Compl connect_s_to_b_tables, gTables.count was 0', FND_LOG.LEVEL_PROCEDURE);
950: END IF;
951: return;
952: END IF;
953: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
953: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
954: LOOP
955: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
956: Tabla_originTable.delete;
957: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
958: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
959: BSC_MO_HELPER_PKG.writeTmp('');
960: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961: l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
955: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
956: Tabla_originTable.delete;
957: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
958: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
959: BSC_MO_HELPER_PKG.writeTmp('');
960: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961: l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
962: FND_LOG.LEVEL_STATEMENT);
963: BSC_MO_HELPER_PKG.writeTmp('------------------------------------------------');
956: Tabla_originTable.delete;
957: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
958: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
959: BSC_MO_HELPER_PKG.writeTmp('');
960: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961: l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
962: FND_LOG.LEVEL_STATEMENT);
963: BSC_MO_HELPER_PKG.writeTmp('------------------------------------------------');
964: END IF;
957: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(l_table.originTable, ',');
958: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
959: BSC_MO_HELPER_PKG.writeTmp('');
960: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961: l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
962: FND_LOG.LEVEL_STATEMENT);
963: BSC_MO_HELPER_PKG.writeTmp('------------------------------------------------');
964: END IF;
965: --Only consider tables with no origin
959: BSC_MO_HELPER_PKG.writeTmp('');
960: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_index1||' '||
961: l_table.name||', System time is '||bsc_mo_helper_pkg.get_time,
962: FND_LOG.LEVEL_STATEMENT);
963: BSC_MO_HELPER_PKG.writeTmp('------------------------------------------------');
964: END IF;
965: --Only consider tables with no origin
966: If Tabla_OriginTable.Count = 0 Then
967: --Key columns
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(
1009: l_table.impl_type,
1010: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
1011: End If;
1012: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1013: BSC_MO_HELPER_PKG.writeTmp('10. Origin = '||l_origin_table, FND_LOG.LEVEL_STATEMENT);
1014: END IF;
1015: IF (l_origin_table is null) THEN
1016: BSC_MO_HELPER_PKG.writeTmp('ERROR:connect_s_to_b_tables: Unable to find source table for '||
1017: l_table.name||'.'||l_measure.fieldName, FND_LOG.LEVEL_EXCEPTION, true);
1012: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1013: BSC_MO_HELPER_PKG.writeTmp('10. Origin = '||l_origin_table, FND_LOG.LEVEL_STATEMENT);
1014: END IF;
1015: IF (l_origin_table is null) THEN
1016: BSC_MO_HELPER_PKG.writeTmp('ERROR:connect_s_to_b_tables: Unable to find source table for '||
1017: l_table.name||'.'||l_measure.fieldName, FND_LOG.LEVEL_EXCEPTION, true);
1018: raise bsc_metadata_optimizer_pkg.optimizer_exception;
1019: END IF;
1020: --In the Indicator tables l_measure.Origen was already set
1020: --In the Indicator tables l_measure.Origen was already set
1021: --TablasOri
1022: If Not TableOriginExists(l_table.originTable, l_origin_table) Then
1023: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1024: BSC_MO_HELPER_PKG.writeTmp('11. Table does not have source ', FND_LOG.LEVEL_STATEMENT);
1025: END IF;
1026: l_table_origin := l_origin_table;
1027: IF (l_table.originTable IS NOT NULL) THEN
1028: l_table.originTable := l_table.originTable||',';
1027: IF (l_table.originTable IS NOT NULL) THEN
1028: l_table.originTable := l_table.originTable||',';
1029: END IF;
1030: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1031: bsc_mo_helper_pkg.writeTmp('3. Adding Origin table for '||l_table.name||' = '||l_table_origin);
1032: END IF;
1033: l_table.originTable := l_table.originTable||l_table_origin;
1034: End If;
1035: End If;
1035: End If;
1036: END LOOP;
1037: END IF;
1038: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1039: bsc_mo_helper_pkg.writeTmp('Origin count for gTables('||l_index1||') was zero, reassigning this table ');
1040: END IF;
1041: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1) := l_table;
1042: End If;
1043:
1045: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1046: END LOOP;
1047: l_end := sysdate;
1048: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1049: BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||(l_end-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
1050: END IF;
1051: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1052: BSC_MO_HELPER_PKG.writeTmp('Completed connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1053: END IF;
1048: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1049: BSC_MO_HELPER_PKG.writeTmp('Elapsed time (secs) '||(l_end-l_start_time)*86400, FND_LOG.LEVEL_STATEMENT);
1050: END IF;
1051: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1052: BSC_MO_HELPER_PKG.writeTmp('Completed connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1053: END IF;
1054: EXCEPTION WHEN OTHERS THEN
1055: l_error := sqlerrm;
1056: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_s_to_b_tables : '||l_error);
1052: BSC_MO_HELPER_PKG.writeTmp('Completed connect_s_to_b_tables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1053: END IF;
1054: EXCEPTION WHEN OTHERS THEN
1055: l_error := sqlerrm;
1056: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in connect_s_to_b_tables : '||l_error);
1057: raise;
1058: End ;
1059:
1060:
1087: IF ( BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count=0) THEN
1088: return;
1089: END IF;
1090: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1091: BSC_MO_HELPER_PKG.writeTmp(g_newline||g_newline||g_newline);
1092: BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables_precalc, # of precalc measures = '||BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||', system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1093: END IF;
1094: l_index1 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.first;
1095:
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
1095:
1096: LOOP
1097: uniqueField := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_index1);
1098: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1099: BSC_MO_HELPER_PKG.writeTmp('Looping for pre calc field = '|| uniqueField.fieldName||', has '||
1100: uniqueField.key_combinations.count||' disaggs ');
1101: bsc_mo_helper_pkg.write_this(g_unique, uniqueField);
1102: END IF;
1103: --Until all disagregations of the unique field are registered
1097: uniqueField := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_index1);
1098: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1099: BSC_MO_HELPER_PKG.writeTmp('Looping for pre calc field = '|| uniqueField.fieldName||', has '||
1100: uniqueField.key_combinations.count||' disaggs ');
1101: bsc_mo_helper_pkg.write_this(g_unique, uniqueField);
1102: END IF;
1103: --Until all disagregations of the unique field are registered
1104: IF NOT all_key_comb_registered(uniqueField.key_combinations) THEN
1105: FOR l_index2 IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1104: IF NOT all_key_comb_registered(uniqueField.key_combinations) THEN
1105: FOR l_index2 IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1106: LOOP
1107: l_key_combination := uniqueField.key_combinations(l_index2);
1108: bsc_mo_helper_pkg.writeTmp('Processing key combination '||l_index2);
1109: IF (l_key_combination.registered=false) THEN
1110: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, uniqueField.fieldName, uniqueField.source);
1111: iMatchingTableIndex := get_matching_tables(
1112: uniqueField.fieldName,
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,
1117: uniqueField.impl_type,
1118: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
1119: If iMatchingTableIndex.count = 0 Then
1120: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1121: BSC_MO_HELPER_PKG.writeTmp(' No existing temporal table with the same periodicity and disagregation');
1122: END IF;
1123: --There was not found an existing temporal table with the same periodicity and disagregation
1124: --and filter and whose fields can be grouped with this field
1125: l_table := bsc_mo_helper_pkg.new_clsTable;
1121: BSC_MO_HELPER_PKG.writeTmp(' No existing temporal table with the same periodicity and disagregation');
1122: END IF;
1123: --There was not found an existing temporal table with the same periodicity and disagregation
1124: --and filter and whose fields can be grouped with this field
1125: l_table := bsc_mo_helper_pkg.new_clsTable;
1126: l_table.Name := 'BSC_B_'||( BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1);
1127: BSC_METADATA_OPTIMIZER_PKG.gMaxB := BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1;
1128: l_table.Type := 1;
1129: l_table.Periodicity := l_key_combination.Periodicity;
1133: l_table.keys := l_key_combination.keys;
1134: l_table.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1135: --Data columns
1136: --It is initialized with only with the this field
1137: l_datafield := bsc_mo_helper_pkg.new_clsDataField;
1138: l_datafield.fieldName := uniqueField.fieldName;
1139: l_datafield.source := uniqueField.source;
1140: l_datafield.measureGroup := l_table.measureGroup;
1141: l_datafield.aggFunction := uniqueField.aggFunction;
1143: --Note: other properties for internal columns are not used in input, base, temporal tables
1144: l_table.data(l_table.data.count) := l_datafield;
1145: --Add the table to the collection g_bt_tables_precalc
1146: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc.count) := l_table;
1147: bsc_mo_helper_pkg.writeTmp('Adding following table to g_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT,false);
1148: bsc_mo_helper_pkg.write_this(l_table);
1149: ELSE
1150: --Add the field to the temporal table
1151: --Data columns
1144: l_table.data(l_table.data.count) := l_datafield;
1145: --Add the table to the collection g_bt_tables_precalc
1146: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc.count) := l_table;
1147: bsc_mo_helper_pkg.writeTmp('Adding following table to g_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT,false);
1148: bsc_mo_helper_pkg.write_this(l_table);
1149: ELSE
1150: --Add the field to the temporal table
1151: --Data columns
1152: --Add this field
1149: ELSE
1150: --Add the field to the temporal table
1151: --Data columns
1152: --Add this field
1153: l_datafield := bsc_mo_helper_pkg.new_clsDataField;
1154: l_datafield.fieldName := uniqueField.fieldName;
1155: l_datafield.source := uniqueField.source;
1156: l_datafield.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1157: l_datafield.aggFunction := uniqueField.aggFunction;
1171: l_index1 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.next(l_index1);
1172: END LOOP;
1173:
1174: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1175: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_precalc is ', FND_LOG.LEVEL_STATEMENT);
1176: BSC_MO_HELPER_PKG.write_THIS(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc, FND_LOG.LEVEL_STATEMENT);
1177: BSC_MO_HELPER_PKG.writeTmp('Completed deduce_bt_tables_precalc, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1178: END IF;
1179:
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
1173:
1174: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1175: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_precalc is ', FND_LOG.LEVEL_STATEMENT);
1176: BSC_MO_HELPER_PKG.write_THIS(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc, FND_LOG.LEVEL_STATEMENT);
1177: BSC_MO_HELPER_PKG.writeTmp('Completed deduce_bt_tables_precalc, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1178: END IF;
1179:
1180: EXCEPTION WHEN OTHERS THEN
1181: l_error := sqlerrm;
1178: END IF;
1179:
1180: EXCEPTION WHEN OTHERS THEN
1181: l_error := sqlerrm;
1182: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in deduce_bt_tables_precalc : '||l_error);
1183: raise;
1184: End;
1185:
1186: --****************************************************************************
1208:
1209: l_error VARCHAR2(4000);
1210: BEGIN
1211: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1212: BSC_MO_HELPER_PKG.writeTmp('Inside areDisaggsSame', FND_LOG.LEVEL_PROCEDURE);
1213: END IF;
1214: If pPeriodicityA = pPeriodicityB Then
1215: If keysA.Count = keysB.Count Then
1216: keysEqual := True;
1227: END LOOP;
1228: END IF;
1229: If keysEqual Then
1230: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1231: BSC_MO_HELPER_PKG.writeTmp('Completed areDisaggsSame, ret true', FND_LOG.LEVEL_PROCEDURE);
1232: END IF;
1233: return true;
1234: End If;
1235: End If;
1235: End If;
1236: End If;
1237:
1238: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1239: BSC_MO_HELPER_PKG.writeTmp('Completed areDisaggsSame ret false', FND_LOG.LEVEL_PROCEDURE);
1240: END IF;
1241: return false;
1242: EXCEPTION WHEN OTHERS THEN
1243: l_error := sqlerrm;
1240: END IF;
1241: return false;
1242: EXCEPTION WHEN OTHERS THEN
1243: l_error := sqlerrm;
1244: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1241: return false;
1242: EXCEPTION WHEN OTHERS THEN
1243: l_error := sqlerrm;
1244: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249: BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1242: EXCEPTION WHEN OTHERS THEN
1243: l_error := sqlerrm;
1244: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249: BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1250: raise;
1243: l_error := sqlerrm;
1244: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249: BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1250: raise;
1251: End ;
1244: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in areDisaggsSame, '||l_error);
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249: BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1250: raise;
1251: End ;
1252: --****************************************************************************
1245: BSC_MO_HELPER_PKG.writeTmp('pPeriodicityA='||pPeriodicityA||', pPeriodicityB='||pPeriodicityB, FND_LOG.LEVEL_EXCEPTION, true);
1246: BSC_MO_HELPER_PKG.writeTmp('KeysA=', FND_LOG.LEVEL_EXCEPTION, true);
1247: BSC_MO_HELPER_PKG.write_this(keysA, FND_LOG.LEVEL_EXCEPTION, true);
1248: BSC_MO_HELPER_PKG.writeTmp('KeysB=', FND_LOG.LEVEL_EXCEPTION, true);
1249: BSC_MO_HELPER_PKG.write_this(keysB, FND_LOG.LEVEL_EXCEPTION, true);
1250: raise;
1251: End ;
1252: --****************************************************************************
1253: -- CreaLoopDesagDestOri:circular_dependency_exists
1274: l_index2 NUMBER;
1275: l_error VARCHAR2(4000);
1276: begin
1277: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1278: BSC_MO_HELPER_PKG.writeTmp('Inside circular_dependency_exists for p_target_keys='||p_target_keys||', p_origin_keys='||p_origin_keys, FND_LOG.LEVEL_PROCEDURE);
1279: BSC_MO_HELPER_PKG.write_this(p_key_combinations);
1280: END IF;
1281: l_index1 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_origin_keys);
1282: l_index2 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_target_keys);
1275: l_error VARCHAR2(4000);
1276: begin
1277: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1278: BSC_MO_HELPER_PKG.writeTmp('Inside circular_dependency_exists for p_target_keys='||p_target_keys||', p_origin_keys='||p_origin_keys, FND_LOG.LEVEL_PROCEDURE);
1279: BSC_MO_HELPER_PKG.write_this(p_key_combinations);
1280: END IF;
1281: l_index1 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_origin_keys);
1282: l_index2 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_target_keys);
1283: If p_key_combinations(l_index1).Origin = 0 Then
1277: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1278: BSC_MO_HELPER_PKG.writeTmp('Inside circular_dependency_exists for p_target_keys='||p_target_keys||', p_origin_keys='||p_origin_keys, FND_LOG.LEVEL_PROCEDURE);
1279: BSC_MO_HELPER_PKG.write_this(p_key_combinations);
1280: END IF;
1281: l_index1 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_origin_keys);
1282: l_index2 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_target_keys);
1283: If p_key_combinations(l_index1).Origin = 0 Then
1284: res := False;
1285: ElsIf p_key_combinations(l_index1).Origin = p_target_keys Then
1278: BSC_MO_HELPER_PKG.writeTmp('Inside circular_dependency_exists for p_target_keys='||p_target_keys||', p_origin_keys='||p_origin_keys, FND_LOG.LEVEL_PROCEDURE);
1279: BSC_MO_HELPER_PKG.write_this(p_key_combinations);
1280: END IF;
1281: l_index1 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_origin_keys);
1282: l_index2 := BSC_MO_HELPER_PKG.findIndex(p_key_combinations, p_target_keys);
1283: If p_key_combinations(l_index1).Origin = 0 Then
1284: res := False;
1285: ElsIf p_key_combinations(l_index1).Origin = p_target_keys Then
1286: res := True;
1287: Else
1288: res := circular_dependency_exists(p_target_keys, p_key_combinations(l_index1).Origin, p_key_combinations);
1289: End If;
1290: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1291: BSC_MO_HELPER_PKG.writeTmp('Completed circular_dependency_exists, res='||
1292: bsc_mo_helper_pkg.boolean_decode(res), FND_LOG.LEVEL_PROCEDURE);
1293: END IF;
1294: return res;
1295: EXCEPTION WHEN OTHERS THEN
1288: res := circular_dependency_exists(p_target_keys, p_key_combinations(l_index1).Origin, p_key_combinations);
1289: End If;
1290: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1291: BSC_MO_HELPER_PKG.writeTmp('Completed circular_dependency_exists, res='||
1292: bsc_mo_helper_pkg.boolean_decode(res), FND_LOG.LEVEL_PROCEDURE);
1293: END IF;
1294: return res;
1295: EXCEPTION WHEN OTHERS THEN
1296: l_error := sqlerrm;
1293: END IF;
1294: return res;
1295: EXCEPTION WHEN OTHERS THEN
1296: l_error := sqlerrm;
1297: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in circular_dependency_exists, '||l_error);
1298: raise;
1299: End ;
1300:
1301:
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, ',' );
1342: BEGIN
1343: If p_key_comb_target.Periodicity <> p_key_comb_origin.Periodicity Then
1344: l_index := BSC_MO_HELPER_PKG.FindIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_key_comb_target.Periodicity);
1345: IF (l_index = -1) THEN -- metadata bad
1346: BSC_MO_HELPER_PKG.writeTmp('Bad Periodicities metadata for Periodicity='||p_key_comb_target.Periodicity, FND_LOG.LEVEL_EXCEPTION, true);
1347: END IF;
1348: l_per_origin := BSC_MO_HELPER_PKG.decomposeStringtoNumber(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).PeriodicityOrigin, ',' );
1349: If BSC_MO_HELPER_PKG.findIndex(l_per_origin, p_key_comb_origin.Periodicity) = -1 Then
1350: return false;
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;
1345: IF (l_index = -1) THEN -- metadata bad
1346: BSC_MO_HELPER_PKG.writeTmp('Bad Periodicities metadata for Periodicity='||p_key_comb_target.Periodicity, FND_LOG.LEVEL_EXCEPTION, true);
1347: END IF;
1348: l_per_origin := BSC_MO_HELPER_PKG.decomposeStringtoNumber(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).PeriodicityOrigin, ',' );
1349: If BSC_MO_HELPER_PKG.findIndex(l_per_origin, p_key_comb_origin.Periodicity) = -1 Then
1350: return false;
1351: End If;
1352: End If;
1353:
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
1367: End If;
1368: l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, l_key_origin.keyName);
1369: l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, l_key_target.keyName);
1370: IF (l_index1 = -1) THEN -- metadata bad
1371: BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_origin.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1372: END IF;
1373: IF (l_index2 = -1) THEN -- metadata bad
1374: BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_target.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1375: END IF;
1370: IF (l_index1 = -1) THEN -- metadata bad
1371: BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_origin.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1372: END IF;
1373: IF (l_index2 = -1) THEN -- metadata bad
1374: BSC_MO_HELPER_PKG.writeTmp('Bad dimension metadata for key='||l_key_target.keyName, FND_LOG.LEVEL_EXCEPTION, true);
1375: END IF;
1376: If BSC_MO_INDICATOR_PKG.IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
1377: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
1378: If Not BSC_MO_INDICATOR_PKG.keyFieldExists(l_changed_levels, l_key_origin.keyName) Then
1376: If BSC_MO_INDICATOR_PKG.IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
1377: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
1378: If Not BSC_MO_INDICATOR_PKG.keyFieldExists(l_changed_levels, l_key_origin.keyName) Then
1379: isDerivable := True;
1380: changedDrill := bsc_mo_helper_pkg.new_clsKeyField;
1381: changedDrill.keyName := l_key_origin.keyName;
1382: l_changed_levels(l_changed_levels.count) := changedDrill;
1383: Exit ;
1384: End If;
1393: return true;
1394:
1395: EXCEPTION WHEN OTHERS THEN
1396: l_error := sqlerrm;
1397: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in can_derive_keys : '||l_error);
1398: BSC_MO_HELPER_PKG.writeTmp('p_key_comb_targetCode ='||p_key_comb_targetCode||',p_key_comb_originCode ='||p_key_comb_originCode||', pTableName='||pTableName, FND_LOG.LEVEL_EXCEPTION, true);
1399: BSC_MO_HELPER_PKG.write_this(p_key_comb_target, 1, FND_LOG.LEVEL_EXCEPTION, true);
1400: BSC_MO_HELPER_PKG.write_this(p_key_comb_origin, 1, FND_LOG.LEVEL_EXCEPTION, true);
1401: raise;
1394:
1395: EXCEPTION WHEN OTHERS THEN
1396: l_error := sqlerrm;
1397: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in can_derive_keys : '||l_error);
1398: BSC_MO_HELPER_PKG.writeTmp('p_key_comb_targetCode ='||p_key_comb_targetCode||',p_key_comb_originCode ='||p_key_comb_originCode||', pTableName='||pTableName, FND_LOG.LEVEL_EXCEPTION, true);
1399: BSC_MO_HELPER_PKG.write_this(p_key_comb_target, 1, FND_LOG.LEVEL_EXCEPTION, true);
1400: BSC_MO_HELPER_PKG.write_this(p_key_comb_origin, 1, FND_LOG.LEVEL_EXCEPTION, true);
1401: raise;
1402: End ;
1395: EXCEPTION WHEN OTHERS THEN
1396: l_error := sqlerrm;
1397: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in can_derive_keys : '||l_error);
1398: BSC_MO_HELPER_PKG.writeTmp('p_key_comb_targetCode ='||p_key_comb_targetCode||',p_key_comb_originCode ='||p_key_comb_originCode||', pTableName='||pTableName, FND_LOG.LEVEL_EXCEPTION, true);
1399: BSC_MO_HELPER_PKG.write_this(p_key_comb_target, 1, FND_LOG.LEVEL_EXCEPTION, true);
1400: BSC_MO_HELPER_PKG.write_this(p_key_comb_origin, 1, FND_LOG.LEVEL_EXCEPTION, true);
1401: raise;
1402: End ;
1403: --****************************************************************************
1396: l_error := sqlerrm;
1397: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in can_derive_keys : '||l_error);
1398: BSC_MO_HELPER_PKG.writeTmp('p_key_comb_targetCode ='||p_key_comb_targetCode||',p_key_comb_originCode ='||p_key_comb_originCode||', pTableName='||pTableName, FND_LOG.LEVEL_EXCEPTION, true);
1399: BSC_MO_HELPER_PKG.write_this(p_key_comb_target, 1, FND_LOG.LEVEL_EXCEPTION, true);
1400: BSC_MO_HELPER_PKG.write_this(p_key_comb_origin, 1, FND_LOG.LEVEL_EXCEPTION, true);
1401: raise;
1402: End ;
1403: --****************************************************************************
1404: -- InicListaTablasTemporalesyBasicas :deduce_bt_tables
1433: l_loop_ctr NUMBER:=0;
1434: bMeasureLogged boolean ;
1435: BEGIN
1436: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1437: BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables, forTargets = '||
1438: bsc_mo_helper_pkg.boolean_decode(forTargets)||' , system time is '||
1439: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1440: END IF;
1441:
1434: bMeasureLogged boolean ;
1435: BEGIN
1436: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1437: BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables, forTargets = '||
1438: bsc_mo_helper_pkg.boolean_decode(forTargets)||' , system time is '||
1439: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1440: END IF;
1441:
1442: If forTargets Then
1435: BEGIN
1436: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1437: BSC_MO_HELPER_PKG.writeTmp('Inside deduce_bt_tables, forTargets = '||
1438: bsc_mo_helper_pkg.boolean_decode(forTargets)||' , system time is '||
1439: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1440: END IF;
1441:
1442: If forTargets Then
1443: l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt;
1449: l_tablename := g_unique;
1450: End If;
1451:
1452: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1453: BSC_MO_HELPER_PKG.writeTmp('Total # of unique measures = '||l_unique_measures.count||', Temp measures = '||l_BTTables.count);
1454: END IF;
1455:
1456: l_count1 := l_unique_measures.first;
1457: LOOP
1460: bMeasureLogged := false;
1461: --Loop through list of dissagregations of the unique field until all have been registered
1462: WHILE NOT all_key_comb_registered(uniqueField.key_combinations) LOOP
1463: IF bMeasureLogged =false AND BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1464: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1465: BSC_MO_HELPER_PKG.writeTmp('Looping for Measure '||l_count1||' = '|| uniqueField.fieldName ||
1466: ', source='||uniqueField.source||', disaggs are ');
1467: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1468: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1461: --Loop through list of dissagregations of the unique field until all have been registered
1462: WHILE NOT all_key_comb_registered(uniqueField.key_combinations) LOOP
1463: IF bMeasureLogged =false AND BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1464: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1465: BSC_MO_HELPER_PKG.writeTmp('Looping for Measure '||l_count1||' = '|| uniqueField.fieldName ||
1466: ', source='||uniqueField.source||', disaggs are ');
1467: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1468: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1469: END IF;
1463: IF bMeasureLogged =false AND BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1464: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1465: BSC_MO_HELPER_PKG.writeTmp('Looping for Measure '||l_count1||' = '|| uniqueField.fieldName ||
1466: ', source='||uniqueField.source||', disaggs are ');
1467: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1468: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1469: END IF;
1470: bMeasureLogged := true;
1471: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1464: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1465: BSC_MO_HELPER_PKG.writeTmp('Looping for Measure '||l_count1||' = '|| uniqueField.fieldName ||
1466: ', source='||uniqueField.source||', disaggs are ');
1467: BSC_MO_HELPER_PKG.writeTmp('---------------------------------');
1468: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1469: END IF;
1470: bMeasureLogged := true;
1471: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1472: BSC_MO_HELPER_PKG.writeTmp('Atleast one disagg is not registered', FND_LOG.LEVEL_STATEMENT);
1468: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, uniqueField.key_combinations);
1469: END IF;
1470: bMeasureLogged := true;
1471: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1472: BSC_MO_HELPER_PKG.writeTmp('Atleast one disagg is not registered', FND_LOG.LEVEL_STATEMENT);
1473: END IF;
1474: IF (uniqueField.key_combinations.count>0) THEN
1475: FOR l_count2 IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1476: LOOP
1476: LOOP
1477: l_key_combination := uniqueField.key_combinations(l_count2);
1478: If Not l_key_combination.Registered Then
1479: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1480: BSC_MO_HELPER_PKG.writeTmp(' Considering disagg ');
1481: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, l_key_combination);
1482: END IF;
1483: If (l_key_combination.Origin = 0) Then
1484: toBeConsidered := True;
1477: l_key_combination := uniqueField.key_combinations(l_count2);
1478: If Not l_key_combination.Registered Then
1479: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1480: BSC_MO_HELPER_PKG.writeTmp(' Considering disagg ');
1481: BSC_MO_HELPER_PKG.write_this(l_tablename, uniqueField.fieldName, l_key_combination);
1482: END IF;
1483: If (l_key_combination.Origin = 0) Then
1484: toBeConsidered := True;
1485: ElsIf (l_key_combination.Origin <> 0 And
1482: END IF;
1483: If (l_key_combination.Origin = 0) Then
1484: toBeConsidered := True;
1485: ElsIf (l_key_combination.Origin <> 0 And
1486: uniqueField.key_combinations(BSC_MO_HELPER_PKG.FindIndex(uniqueField.key_combinations, l_key_combination.Origin)).Registered) Then
1487: toBeConsidered := True;
1488: Else
1489: toBeConsidered := False;
1490: End If;
1489: toBeConsidered := False;
1490: End If;
1491:
1492: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1493: BSC_MO_HELPER_PKG.writeTmp(' To be considered = '||bsc_mo_helper_pkg.boolean_decode(toBeConsidered));
1494: END IF;
1495: If toBeConsidered Then
1496: l_temp := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gLov, uniqueField.fieldName, uniqueField.source);
1497: iMatchingTableIndex := get_matching_tables(
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,
1510: --It was found a existing temporal table with the same periodicity and disagregation and same field grouping.
1511: --Check if the disagregation and periodicity of the origin tables of the temporal table
1512: --are the same as origin disagregation of the current one
1513: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1514: BSC_MO_HELPER_PKG.writeTmp('Found a existing temporal table with the same periodicity and '||
1515: ' disagregation and same field grouping.');
1516: BSC_MO_HELPER_PKG.writeTmp('Table name = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name||
1517: ', origin table = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable , FND_LOG.LEVEL_STATEMENT);
1518: END IF;
1512: --are the same as origin disagregation of the current one
1513: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1514: BSC_MO_HELPER_PKG.writeTmp('Found a existing temporal table with the same periodicity and '||
1515: ' disagregation and same field grouping.');
1516: BSC_MO_HELPER_PKG.writeTmp('Table name = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name||
1517: ', origin table = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable , FND_LOG.LEVEL_STATEMENT);
1518: END IF;
1519: l_BTTables_origin := BSC_MO_HELPER_PKG.getDecomposedString(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, ',');
1520: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1515: ' disagregation and same field grouping.');
1516: BSC_MO_HELPER_PKG.writeTmp('Table name = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name||
1517: ', origin table = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable , FND_LOG.LEVEL_STATEMENT);
1518: END IF;
1519: l_BTTables_origin := BSC_MO_HELPER_PKG.getDecomposedString(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, ',');
1520: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1521: BSC_MO_HELPER_PKG.writeTmp('7. l_key_combination.Origin = '||l_key_combination.Origin ||
1522: ', l_BTTables_origin.Count = '||l_BTTables_origin.Count||' , system time is '||
1523: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1517: ', origin table = '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable , FND_LOG.LEVEL_STATEMENT);
1518: END IF;
1519: l_BTTables_origin := BSC_MO_HELPER_PKG.getDecomposedString(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, ',');
1520: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1521: BSC_MO_HELPER_PKG.writeTmp('7. l_key_combination.Origin = '||l_key_combination.Origin ||
1522: ', l_BTTables_origin.Count = '||l_BTTables_origin.Count||' , system time is '||
1523: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1524: END IF;
1525: If l_key_combination.Origin = 0 Then
1519: l_BTTables_origin := BSC_MO_HELPER_PKG.getDecomposedString(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, ',');
1520: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1521: BSC_MO_HELPER_PKG.writeTmp('7. l_key_combination.Origin = '||l_key_combination.Origin ||
1522: ', l_BTTables_origin.Count = '||l_BTTables_origin.Count||' , system time is '||
1523: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1524: END IF;
1525: If l_key_combination.Origin = 0 Then
1526: If l_BTTables_origin.Count = 0 -- if its a B table added in this run
1527: -- or if its a production B table
1531: needNewTable := False;
1532: End If;
1533: Else -- this key combination can be derived from another key combination
1534: If l_BTTables_origin.Count <> 0 THEN --AND (NOT l_BTTables(l_loop_ctr).isProductionTable)Then
1535: l_temp := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1536: BSC_MO_HELPER_PKG.write_to_stack('7.1 l_temp='||l_temp);
1537: l_temp3 := BSC_MO_HELPER_PKG.findIndex(l_BTTables, l_BTTables_origin(l_BTTables_origin.first));
1538: IF (l_temp3 = -1 AND
1539: l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable AND
1532: End If;
1533: Else -- this key combination can be derived from another key combination
1534: If l_BTTables_origin.Count <> 0 THEN --AND (NOT l_BTTables(l_loop_ctr).isProductionTable)Then
1535: l_temp := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1536: BSC_MO_HELPER_PKG.write_to_stack('7.1 l_temp='||l_temp);
1537: l_temp3 := BSC_MO_HELPER_PKG.findIndex(l_BTTables, l_BTTables_origin(l_BTTables_origin.first));
1538: IF (l_temp3 = -1 AND
1539: l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable AND
1540: BSC_DBGEN_UTILS.get_table_type(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name)='B') THEN
1533: Else -- this key combination can be derived from another key combination
1534: If l_BTTables_origin.Count <> 0 THEN --AND (NOT l_BTTables(l_loop_ctr).isProductionTable)Then
1535: l_temp := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1536: BSC_MO_HELPER_PKG.write_to_stack('7.1 l_temp='||l_temp);
1537: l_temp3 := BSC_MO_HELPER_PKG.findIndex(l_BTTables, l_BTTables_origin(l_BTTables_origin.first));
1538: IF (l_temp3 = -1 AND
1539: l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTable AND
1540: BSC_DBGEN_UTILS.get_table_type(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name)='B') THEN
1541: -- this is a production B table, but we havent loaded I table into memory
1540: BSC_DBGEN_UTILS.get_table_type(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Name)='B') THEN
1541: -- this is a production B table, but we havent loaded I table into memory
1542: l_temp3 := iMatchingTableIndex(l_loop_ctr);
1543: END IF;
1544: BSC_MO_HELPER_PKG.write_to_stack('7.2 l_temp3='||l_temp3);
1545: BSC_MO_HELPER_PKG.write_to_stack('7.3 l_BTTables(l_temp3).Keys.count='||l_BTTables(l_temp3).Keys.count);
1546: If areDisaggsSame(uniqueField.key_combinations(l_temp).Periodicity,
1547: uniqueField.key_combinations(l_temp).keys,
1548: l_BTTables(l_temp3).Periodicity,
1541: -- this is a production B table, but we havent loaded I table into memory
1542: l_temp3 := iMatchingTableIndex(l_loop_ctr);
1543: END IF;
1544: BSC_MO_HELPER_PKG.write_to_stack('7.2 l_temp3='||l_temp3);
1545: BSC_MO_HELPER_PKG.write_to_stack('7.3 l_BTTables(l_temp3).Keys.count='||l_BTTables(l_temp3).Keys.count);
1546: If areDisaggsSame(uniqueField.key_combinations(l_temp).Periodicity,
1547: uniqueField.key_combinations(l_temp).keys,
1548: l_BTTables(l_temp3).Periodicity,
1549: l_BTTables(l_temp3).Keys) Then
1547: uniqueField.key_combinations(l_temp).keys,
1548: l_BTTables(l_temp3).Periodicity,
1549: l_BTTables(l_temp3).Keys) Then
1550: needNewTable := False;
1551: BSC_MO_HELPER_PKG.write_to_stack('7.5 needNewTable := False;');
1552: End If;
1553: End If;
1554: End If;
1555: IF (NOT needNewTable) THEN
1558: exit when l_loop_ctr = iMatchingTableIndex.last;
1559: l_loop_ctr:= iMatchingTableIndex.next(l_loop_ctr);
1560: END LOOP;
1561: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562: BSC_MO_HELPER_PKG.writeTmp('needNewTable = '||bsc_mo_helper_pkg.boolean_decode(needNewTable));
1563: END IF;
1564: If needNewTable Then
1565: --Add a new table
1566: --Name
1563: END IF;
1564: If needNewTable Then
1565: --Add a new table
1566: --Name
1567: l_table := bsc_mo_helper_pkg.new_clsTable;
1568: If l_key_combination.Origin <> 0 Then
1569: l_table.Name := 'BSC_T_' || (BSC_METADATA_OPTIMIZER_PKG.gMaxT + 1);
1570: BSC_METADATA_OPTIMIZER_PKG.gMaxT := BSC_METADATA_OPTIMIZER_PKG.gMaxT + 1;
1571: Else
1572: l_table.Name := 'BSC_B_' || (BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1);
1573: BSC_METADATA_OPTIMIZER_PKG.gMaxB := BSC_METADATA_OPTIMIZER_PKG.gMaxB + 1;
1574: End If;
1575: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1576: BSC_MO_HELPER_PKG.writeTmp('Going to add table '||l_table.Name);
1577: END IF;
1578: l_table.measureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1579: l_table.Type := 1;
1580: l_table.Periodicity := l_key_combination.Periodicity;
1582: l_table.IsTargetTable := forTargets;
1583: l_table.IsProductionTable := false;
1584: l_table.impl_type := uniqueField.impl_type;
1585: -- BSC AW
1586: --l_key_combination_keys := BSC_MO_HELPER_PKG.getDisaggKeys(l_tablename, uniqueField.fieldName, l_key_combination.code);
1587: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1588: l_key_combination.keys := UniqueField.key_Combinations(l_index).keys;
1589: l_count3 := l_key_combination.keys.first;
1590: --Key columns
1583: l_table.IsProductionTable := false;
1584: l_table.impl_type := uniqueField.impl_type;
1585: -- BSC AW
1586: --l_key_combination_keys := BSC_MO_HELPER_PKG.getDisaggKeys(l_tablename, uniqueField.fieldName, l_key_combination.code);
1587: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1588: l_key_combination.keys := UniqueField.key_Combinations(l_index).keys;
1589: l_count3 := l_key_combination.keys.first;
1590: --Key columns
1591: l_table.keys := l_key_combination.keys;
1590: --Key columns
1591: l_table.keys := l_key_combination.keys;
1592: --Data columns
1593: --It is initialized only with the current field
1594: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
1595: l_measure_column.fieldName := uniqueField.fieldName;
1596: l_measure_column.source := uniqueField.source;
1597: l_measure_column.MeasureGroup := l_table.measureGroup;
1598: l_measure_column.aggFunction := uniqueField.aggFunction;
1597: l_measure_column.MeasureGroup := l_table.measureGroup;
1598: l_measure_column.aggFunction := uniqueField.aggFunction;
1599:
1600: If l_key_combination.Origin <> 0 Then
1601: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1602: l_origin_table := get_origin_table(
1603: uniqueField.key_combinations(l_index).Periodicity,
1604: uniqueField.key_combinations(l_index).keys,
1605: uniqueField.fieldName,
1608: uniqueField.impl_type,
1609: l_BTTables);
1610: End If;
1611: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1612: BSC_MO_HELPER_PKG.writeTmp('14. l_origin_table = '||l_origin_table||' system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1613: END IF;
1614: --Note: removed the name of the table as prefix of the column
1615: --I do not see that the same column could be in two origin tables.
1616: l_measure_column.Origin := l_measure_column.aggFunction|| '('|| l_measure_column.fieldName || ')';
1622: IF (l_table.originTable IS NOT NULL) THEN
1623: l_table.originTable := l_table.originTable||',';
1624: END IF;
1625: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1626: bsc_mo_helper_pkg.writeTmp('4. Adding Origin table for '||l_table.name||' = '||l_origin_table);
1627: END IF;
1628: l_table.originTable := l_table.originTable||l_origin_table;
1629: END IF;
1630:
1629: END IF;
1630:
1631: --Add the table to the collection l_BTTables
1632: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1633: bsc_mo_helper_pkg.writeTmp('Adding table at l_BTTables('||l_BTTables.count||'), table is ');
1634: bsc_mo_helper_pkg.write_this(l_table);
1635: END IF;
1636: l_BTTables(l_BTTables.count) := l_table;
1637: l_key_combination.Registered := True;
1630:
1631: --Add the table to the collection l_BTTables
1632: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1633: bsc_mo_helper_pkg.writeTmp('Adding table at l_BTTables('||l_BTTables.count||'), table is ');
1634: bsc_mo_helper_pkg.write_this(l_table);
1635: END IF;
1636: l_BTTables(l_BTTables.count) := l_table;
1637: l_key_combination.Registered := True;
1638: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1635: END IF;
1636: l_BTTables(l_BTTables.count) := l_table;
1637: l_key_combination.Registered := True;
1638: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1639: BSC_MO_HELPER_PKG.writeTmp('15. Registered key combination', FND_LOG.LEVEL_STATEMENT);
1640: END IF;
1641: Else -- Table already exists
1642: --Add the field to the temporal table
1643: --Data columns
1641: Else -- Table already exists
1642: --Add the field to the temporal table
1643: --Data columns
1644: --Add the current field
1645: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
1646: l_measure_column.fieldName := uniqueField.fieldName;
1647: l_measure_column.source := uniqueField.source;
1648: l_measure_column.MeasureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1649: l_measure_column.aggFunction := uniqueField.aggFunction;
1647: l_measure_column.source := uniqueField.source;
1648: l_measure_column.MeasureGroup := get_measure_group(uniqueField.fieldname, uniqueField.source);
1649: l_measure_column.aggFunction := uniqueField.aggFunction;
1650: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1651: BSC_MO_HELPER_PKG.writeTmp('16. Add current field '||l_measure_column.fieldName, FND_LOG.LEVEL_STATEMENT);
1652: END IF;
1653: If l_key_combination.Origin <> 0 Then
1654: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1655: l_origin_table := get_origin_table(
1650: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1651: BSC_MO_HELPER_PKG.writeTmp('16. Add current field '||l_measure_column.fieldName, FND_LOG.LEVEL_STATEMENT);
1652: END IF;
1653: If l_key_combination.Origin <> 0 Then
1654: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.Origin);
1655: l_origin_table := get_origin_table(
1656: uniqueField.key_combinations(l_index).Periodicity,
1657: uniqueField.key_combinations(l_index).keys,
1658: uniqueField.fieldName,
1661: uniqueField.impl_type,
1662: l_BTTables);
1663: End If;
1664: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1665: BSC_MO_HELPER_PKG.writeTmp('17. Origin table is '||l_origin_table, FND_LOG.LEVEL_STATEMENT);
1666: END IF;
1667: --Note: removed the name of the table as prefix of the column
1668: --I do not see that the same column could be in two origin tables.
1669: l_measure_column.Origin := l_measure_column.aggFunction || '(' || l_measure_column.fieldName || ')';
1671: NOT BSC_MO_INDICATOR_PKG.DataFieldExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data, l_measure_column.fieldName)) THEN
1672: l_BTTables(iMatchingTableIndex(l_loop_ctr)).isProductionTableAltered := true;
1673: l_measure_column.changeType := 'NEW';
1674: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1675: BSC_MO_HELPER_PKG.writeTmp('ChangeType for '||l_measure_column.fieldName ||' = NEW', FND_LOG.LEVEL_STATEMENT);
1676: END IF;
1677: END IF;
1678: --Note: other properties for internal columns are not used in input, base, temporal tables
1679: -- If the field does not exist already in the table, then add it.
1678: --Note: other properties for internal columns are not used in input, base, temporal tables
1679: -- If the field does not exist already in the table, then add it.
1680: IF( NOT BSC_MO_INDICATOR_PKG.DataFieldExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data, l_measure_column.fieldName))THEN
1681: l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data(l_BTTables(iMatchingTableIndex(l_loop_ctr)).Data.count) := l_measure_column;
1682: BSC_MO_HELPER_PKG.writeTmp('18. Add origin table '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
1683: --TablasOri
1684: --Add to the list the name of the origin table
1685: IF l_key_combination.Origin <> 0 Then
1686: IF Not TableOriginExists(l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable, l_origin_table) Then
1687: IF (l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable IS NOT NULL) THEN
1688: l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable := l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable||',';
1689: END IF;
1690: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1691: bsc_mo_helper_pkg.writeTmp('5. Adding Origin table for '||l_BTTables(iMatchingTableIndex(l_loop_ctr)).name||' = '||l_origin_table);
1692: END IF;
1693: l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable := l_BTTables(iMatchingTableIndex(l_loop_ctr)).originTable||l_origin_table;
1694: END If;
1695: END If;
1695: END If;
1696: END IF;
1697:
1698: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1699: BSC_MO_HELPER_PKG.writeTmp('19. Registered key combination', FND_LOG.LEVEL_STATEMENT);
1700: END IF;
1701: l_key_combination.Registered := True;
1702: End If; -- End of NeedNewTable
1703: End If;-- ENd of to be considered
1714: IF forTargets THEN
1715: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt := l_unique_measures ;
1716: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt := l_BTTables;
1717: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1718: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt is ');
1719: BSC_MO_HELPER_PKG.write_this(g_target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
1720: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_tgt is ');
1721: BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
1722: END IF;
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
1716: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt := l_BTTables;
1717: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1718: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt is ');
1719: BSC_MO_HELPER_PKG.write_this(g_target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
1720: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables_tgt is ');
1721: BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
1722: END IF;
1723: ELSE
1724: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures := l_unique_measures;
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;
1723: ELSE
1724: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures := l_unique_measures;
1725: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables := l_BTTables;
1726: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1727: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures is ');
1728: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures, FND_LOG.LEVEL_STATEMENT, false);
1729: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables is ');
1730: BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables, FND_LOG.LEVEL_STATEMENT, false, false);
1731: END IF;
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;
1725: BSC_METADATA_OPTIMIZER_PKG.g_bt_tables := l_BTTables;
1726: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1727: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures is ');
1728: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures, FND_LOG.LEVEL_STATEMENT, false);
1729: BSC_MO_HELPER_PKG.writeTmp('g_bt_tables is ');
1730: BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables, FND_LOG.LEVEL_STATEMENT, false, false);
1731: END IF;
1732: END IF;
1733:
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
1731: END IF;
1732: END IF;
1733:
1734: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1735: BSC_MO_HELPER_PKG.writeTmp('Compl deduce_bt_tables, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1736: END IF;
1737:
1738: EXCEPTION WHEN OTHERS THEN
1739: l_error := sqlerrm;
1736: END IF;
1737:
1738: EXCEPTION WHEN OTHERS THEN
1739: l_error := sqlerrm;
1740: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in deduce_bt_tables : '||l_error);
1741: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'deduce_bt_tables');
1742: raise;
1743: End ;
1744:
1737:
1738: EXCEPTION WHEN OTHERS THEN
1739: l_error := sqlerrm;
1740: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in deduce_bt_tables : '||l_error);
1741: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'deduce_bt_tables');
1742: raise;
1743: End ;
1744:
1745: --****************************************************************************
1773: IF (p_unique_measures.count =0) THEN
1774: return;
1775: END IF;
1776: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1777: BSC_MO_HELPER_PKG.writeTmp(' ');
1778: BSC_MO_HELPER_PKG.writeTmp(' ');
1779: BSC_MO_HELPER_PKG.writeTmp('Inside resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1780: END IF;
1781: IF (p_unique_measures.count>0) THEN
1774: return;
1775: END IF;
1776: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1777: BSC_MO_HELPER_PKG.writeTmp(' ');
1778: BSC_MO_HELPER_PKG.writeTmp(' ');
1779: BSC_MO_HELPER_PKG.writeTmp('Inside resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1780: END IF;
1781: IF (p_unique_measures.count>0) THEN
1782: FOR i IN p_unique_measures.first..p_unique_measures.last
1775: END IF;
1776: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1777: BSC_MO_HELPER_PKG.writeTmp(' ');
1778: BSC_MO_HELPER_PKG.writeTmp(' ');
1779: BSC_MO_HELPER_PKG.writeTmp('Inside resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1780: END IF;
1781: IF (p_unique_measures.count>0) THEN
1782: FOR i IN p_unique_measures.first..p_unique_measures.last
1783: LOOP
1790: l_key_combination := uniqueField.key_combinations(j);
1791: IF (l_key_combination.registered = false) THEN -- ignore registered disaggs from prod. tables
1792:
1793: IF bMeasureLogged =false and BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1794: BSC_MO_HELPER_PKG.writeTmp(' ');
1795: BSC_MO_HELPER_PKG.writeTmp('Looping for unique field : '||uniqueField.fieldName||', source='||uniqueField.source, FND_LOG.LEVEL_STATEMENT);
1796: bsc_mo_helper_pkg.write_this(null, uniqueField);
1797: END IF;
1798: bMeasureLogged := true;
1791: IF (l_key_combination.registered = false) THEN -- ignore registered disaggs from prod. tables
1792:
1793: IF bMeasureLogged =false and BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1794: BSC_MO_HELPER_PKG.writeTmp(' ');
1795: BSC_MO_HELPER_PKG.writeTmp('Looping for unique field : '||uniqueField.fieldName||', source='||uniqueField.source, FND_LOG.LEVEL_STATEMENT);
1796: bsc_mo_helper_pkg.write_this(null, uniqueField);
1797: END IF;
1798: bMeasureLogged := true;
1799: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1792:
1793: IF bMeasureLogged =false and BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1794: BSC_MO_HELPER_PKG.writeTmp(' ');
1795: BSC_MO_HELPER_PKG.writeTmp('Looping for unique field : '||uniqueField.fieldName||', source='||uniqueField.source, FND_LOG.LEVEL_STATEMENT);
1796: bsc_mo_helper_pkg.write_this(null, uniqueField);
1797: END IF;
1798: bMeasureLogged := true;
1799: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1800: BSC_MO_HELPER_PKG.writeTmp(' Disagg is '||l_key_combination.code, FND_LOG.LEVEL_STATEMENT);
1796: bsc_mo_helper_pkg.write_this(null, uniqueField);
1797: END IF;
1798: bMeasureLogged := true;
1799: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1800: BSC_MO_HELPER_PKG.writeTmp(' Disagg is '||l_key_combination.code, FND_LOG.LEVEL_STATEMENT);
1801: END IF;
1802: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1803: l_key_combination.keys := uniqueField.key_combinations(l_index).keys;
1804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1798: bMeasureLogged := true;
1799: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1800: BSC_MO_HELPER_PKG.writeTmp(' Disagg is '||l_key_combination.code, FND_LOG.LEVEL_STATEMENT);
1801: END IF;
1802: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1803: l_key_combination.keys := uniqueField.key_combinations(l_index).keys;
1804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1805: BSC_MO_HELPER_PKG.writeTmp(' Disagg Keys are ', FND_LOG.LEVEL_STATEMENT);
1806: BSC_MO_HELPER_PKG.write_this(l_key_combination.keys);
1801: END IF;
1802: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1803: l_key_combination.keys := uniqueField.key_combinations(l_index).keys;
1804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1805: BSC_MO_HELPER_PKG.writeTmp(' Disagg Keys are ', FND_LOG.LEVEL_STATEMENT);
1806: BSC_MO_HELPER_PKG.write_this(l_key_combination.keys);
1807: END IF;
1808: FOR k IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1809: LOOP
1802: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, l_key_combination.code);
1803: l_key_combination.keys := uniqueField.key_combinations(l_index).keys;
1804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1805: BSC_MO_HELPER_PKG.writeTmp(' Disagg Keys are ', FND_LOG.LEVEL_STATEMENT);
1806: BSC_MO_HELPER_PKG.write_this(l_key_combination.keys);
1807: END IF;
1808: FOR k IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1809: LOOP
1810: Desag1 := uniqueField.key_combinations(k);
1807: END IF;
1808: FOR k IN uniqueField.key_combinations.first..uniqueField.key_combinations.last
1809: LOOP
1810: Desag1 := uniqueField.key_combinations(k);
1811: l_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, desag1.code);
1812: If Desag1.Code <> l_key_combination.Code Then
1813: If can_derive_keys(--uniqueField.fieldName,
1814: l_key_combination, l_key_combination.code, Desag1, Desag1.code, l_tablename) Then
1815: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1812: If Desag1.Code <> l_key_combination.Code Then
1813: If can_derive_keys(--uniqueField.fieldName,
1814: l_key_combination, l_key_combination.code, Desag1, Desag1.code, l_tablename) Then
1815: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1816: BSC_MO_HELPER_PKG.writeTmp(' Disagg.code is '||l_key_combination.code||' and Disagg1.code is '||desag1.code, FND_LOG.LEVEL_STATEMENT);
1817: BSC_MO_HELPER_PKG.writeTmp('Origin exists, verify there is no loop ', FND_LOG.LEVEL_STATEMENT);
1818: END IF;
1819: --verify that it is not creating a loop when l_key_combination is originated from Desag1
1820: If Not circular_dependency_exists(l_key_combination.Code, Desag1.Code, uniqueField.key_combinations) Then
1813: If can_derive_keys(--uniqueField.fieldName,
1814: l_key_combination, l_key_combination.code, Desag1, Desag1.code, l_tablename) Then
1815: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1816: BSC_MO_HELPER_PKG.writeTmp(' Disagg.code is '||l_key_combination.code||' and Disagg1.code is '||desag1.code, FND_LOG.LEVEL_STATEMENT);
1817: BSC_MO_HELPER_PKG.writeTmp('Origin exists, verify there is no loop ', FND_LOG.LEVEL_STATEMENT);
1818: END IF;
1819: --verify that it is not creating a loop when l_key_combination is originated from Desag1
1820: If Not circular_dependency_exists(l_key_combination.Code, Desag1.Code, uniqueField.key_combinations) Then
1821: l_key_combination.Origin := Desag1.Code;
1824: LOOP
1825: l_key := l_key_combination.keys(l);
1826: l_key.Origin := BSC_MO_INDICATOR_PKG.getKeyOrigin(Desag1.keys, l_key.keyName);
1827: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1828: bsc_mo_helper_pkg.writeTmp('Changing table = '||l_tableName ||', field '||uniqueField.fieldName
1829: ||', code = '||l_key_combination.code||'''s origin to '||l_key.Origin);
1830: END IF;
1831: l_key_combination.keys(l) := l_key;
1832: END LOOP;
1845: p_unique_measures(i) := uniqueField;
1846: END LOOP;
1847: END IF;
1848: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1849: BSC_MO_HELPER_PKG.writeTmp('Completed resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1850: BSC_MO_HELPER_PKG.write_This(null,p_unique_measures, FND_LOG.LEVEL_STATEMENT, true);
1851: END IF;
1852: EXCEPTION WHEN OTHERS THEN
1853: l_error := sqlerrm;
1846: END LOOP;
1847: END IF;
1848: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1849: BSC_MO_HELPER_PKG.writeTmp('Completed resolve_key_origins '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1850: BSC_MO_HELPER_PKG.write_This(null,p_unique_measures, FND_LOG.LEVEL_STATEMENT, true);
1851: END IF;
1852: EXCEPTION WHEN OTHERS THEN
1853: l_error := sqlerrm;
1854: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in resolve_key_origins : '||l_error);
1850: BSC_MO_HELPER_PKG.write_This(null,p_unique_measures, FND_LOG.LEVEL_STATEMENT, true);
1851: END IF;
1852: EXCEPTION WHEN OTHERS THEN
1853: l_error := sqlerrm;
1854: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in resolve_key_origins : '||l_error);
1855: raise;
1856: End ;
1857:
1858:
1905: END IF;
1906: l_stack := l_stack || g_newline||'Loop1';
1907: l_key_combination := colDesags(i);
1908: --BSC AW
1909: --l_key_combination_keys := BSC_MO_HELPER_PKG.getDisaggKeys(pTableName, pFieldName, l_key_combination.Code);
1910: --l_key_combination_keys := l_key_combination.keys;
1911: If l_key_combination.Periodicity = pPeriodicity Then
1912: If l_key_combination.keys.Count = p_keys.Count Then
1913: keysEqual := True;
1919: If Not BSC_MO_INDICATOR_PKG.KeyfieldExists(p_keys, l_key.keyName) Then
1920: keysEqual := False;
1921: Exit ;
1922: Else
1923: l_temp := BSC_MO_HELPER_PKG.findIndex(p_keys, l_key.keyName);
1924: If UPPER(l_key.FilterViewName) <> UPPER(p_keys(l_temp).FilterViewName) Then
1925: keysEqual := False;
1926: Exit ;
1927: End If;
1940: return -1;
1941:
1942: EXCEPTION WHEN OTHERS THEN
1943: l_error := sqlerrm;
1944: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDisaggCode :'||l_error);
1945: BSC_MO_HELPER_PKG.writeTmp('Stack is :'||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1946: raise;
1947:
1948: End ;
1941:
1942: EXCEPTION WHEN OTHERS THEN
1943: l_error := sqlerrm;
1944: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDisaggCode :'||l_error);
1945: BSC_MO_HELPER_PKG.writeTmp('Stack is :'||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1946: raise;
1947:
1948: End ;
1949:
1984: l_count := p_unique_measure_list.next(l_count);
1985: END LOOP;
1986: return FALSE;
1987: EXCEPTION WHEN OTHERS THEN
1988: BSC_MO_HELPER_PKG.writeTmp('Exception in UniqueFieldExists, '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1989: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in UniqueFieldExists, '||sqlerrm);
1990: raise;
1991: End ;
1992:
1985: END LOOP;
1986: return FALSE;
1987: EXCEPTION WHEN OTHERS THEN
1988: BSC_MO_HELPER_PKG.writeTmp('Exception in UniqueFieldExists, '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1989: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in UniqueFieldExists, '||sqlerrm);
1990: raise;
1991: End ;
1992:
1993: --****************************************************************************
2026: IF (p_tables.count=0) THEN
2027: return;
2028: END IF;
2029: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2030: BSC_MO_HELPER_PKG.writeTmp('Inside init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2031: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2032: END IF;
2033: l_count1 := p_tables.first;
2034: LOOP
2027: return;
2028: END IF;
2029: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2030: BSC_MO_HELPER_PKG.writeTmp('Inside init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2031: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2032: END IF;
2033: l_count1 := p_tables.first;
2034: LOOP
2035: IF (length(l_stack) > 30000) THEN
2036: l_stack := null;
2037: END IF;
2038: l_table := p_tables(l_count1);
2039: IF (Tabla_OriginTable IS NOT NULL) THEN
2040: Tabla_OriginTable := bsc_mo_helper_pkg.getDecomposedString(l_table.originTable, ',');
2041: END IF;
2042: --Only consider tables not having origin and belong to indicators precalculated
2043: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_table.Indicator);
2044: IF (l_index = -1) THEN
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
2041: END IF;
2042: --Only consider tables not having origin and belong to indicators precalculated
2043: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_table.Indicator);
2044: IF (l_index = -1) THEN
2045: l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_table.Indicator, 'DB_TRANSFORM', 1);
2046: l_impl_type := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_table.Indicator, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1);
2047: ELSE
2048: l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).OptimizationMode;
2049: l_impl_type := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).impl_type;
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;
2055: If ((l_table.originTable IS NULL AND l_table.isProductionTable=false) OR
2056: (l_table.originTable IS NOT NULL AND l_table.isProductionTable=true))
2057: And l_optimizationMode = 0 Then
2058: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2059: BSC_MO_HELPER_PKG.writeTmp('');
2060: bsc_mo_helper_pkg.writeTmp(' Processing table '||l_table.name);
2061: bsc_mo_helper_pkg.write_this(l_table);
2062: END IF;
2063:
2056: (l_table.originTable IS NOT NULL AND l_table.isProductionTable=true))
2057: And l_optimizationMode = 0 Then
2058: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2059: BSC_MO_HELPER_PKG.writeTmp('');
2060: bsc_mo_helper_pkg.writeTmp(' Processing table '||l_table.name);
2061: bsc_mo_helper_pkg.write_this(l_table);
2062: END IF;
2063:
2064: l_count2 := l_table.data.first;
2057: And l_optimizationMode = 0 Then
2058: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2059: BSC_MO_HELPER_PKG.writeTmp('');
2060: bsc_mo_helper_pkg.writeTmp(' Processing table '||l_table.name);
2061: bsc_mo_helper_pkg.write_this(l_table);
2062: END IF;
2063:
2064: l_count2 := l_table.data.first;
2065: LOOP
2069: --tables and are not going to be base or input tables.
2070: If l_measure.InternalColumnType = 0 Then
2071: If Not UniqueFieldExists(l_measure.fieldName, l_measure.source, l_impl_type, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc) Then
2072: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2073: bsc_mo_helper_pkg.writeTmp(' '||l_measure.fieldName||'('||l_measure.source||') Field does not exist --> Add it');
2074: END IF;
2075: --Field does not exists --> Add it
2076: uniqueField := bsc_mo_helper_pkg.new_clsUniqueField;
2077: uniqueField.fieldName := l_measure.fieldName;
2072: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2073: bsc_mo_helper_pkg.writeTmp(' '||l_measure.fieldName||'('||l_measure.source||') Field does not exist --> Add it');
2074: END IF;
2075: --Field does not exists --> Add it
2076: uniqueField := bsc_mo_helper_pkg.new_clsUniqueField;
2077: uniqueField.fieldName := l_measure.fieldName;
2078: uniqueField.source := l_measure.source;
2079: uniqueField.measureGroup := get_measure_Group(l_measure.fieldName, l_measure.source);
2080: uniqueField.aggFunction := l_measure.aggFunction;
2085: END IF;
2086: uniqueField.impl_type := l_impl_type;
2087: --Desags. It initialized with one element that correspond to the disagregation
2088: --and periodicity of the table
2089: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2090: disaggField.Code := 1;
2091: disaggField.Periodicity := l_table.Periodicity;
2092: --Key columns. Same as the table
2093: l_count3 := l_table.keys.first;
2103: --Add the unique field to the collection g_unique_measures_precalc
2104: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count) := uniqueField;
2105: ELSE
2106: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2107: bsc_mo_helper_pkg.writeTmp( l_measure.fieldName||'('||l_measure.source||') field already exists. Check periodicity and disaggregation');
2108: END IF;
2109: --The field already exists. So check if its periodicity and disagregation
2110: -- is in its list of disagregations
2111: l_field_index := BSC_MO_HELPER_PKG.findIndex(
2107: bsc_mo_helper_pkg.writeTmp( l_measure.fieldName||'('||l_measure.source||') field already exists. Check periodicity and disaggregation');
2108: END IF;
2109: --The field already exists. So check if its periodicity and disagregation
2110: -- is in its list of disagregations
2111: l_field_index := BSC_MO_HELPER_PKG.findIndex(
2112: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc,
2113: l_measure.fieldName,
2114: l_measure.source,
2115: l_table.impl_type);
2113: l_measure.fieldName,
2114: l_measure.source,
2115: l_table.impl_type);
2116: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2117: bsc_mo_helper_pkg.writeTmp('Field Index is '||l_field_index);
2118: END IF;
2119: uniqueField.key_combinations.delete;
2120: uniqueField.key_combinations := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations;
2121: CodDesag := GetDisaggCode(
2125: l_table.keys,
2126: l_table.name);
2127: If CodDesag = -1 Then
2128: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2129: bsc_mo_helper_pkg.writeTmp( 'Disaggregation does not exist, add it');
2130: END IF;
2131: --It does not exist --> Add it
2132: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2133: uniqueField.key_combinations.delete;
2128: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2129: bsc_mo_helper_pkg.writeTmp( 'Disaggregation does not exist, add it');
2130: END IF;
2131: --It does not exist --> Add it
2132: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2133: uniqueField.key_combinations.delete;
2134: uniqueField.key_combinations := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations;
2135: disaggField.Code := uniqueField.key_combinations.Count + 1;
2136: disaggField.Periodicity := l_table.Periodicity;
2149: --The dissagregation exists. We need to use the property NecesitaCod0 because
2150: --the table could need zero code in some key where the dissagregation dont need
2151: --so far.
2152: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2153: bsc_mo_helper_pkg.writeTmp( 'Disaggregation already exists');
2154: END IF;
2155: l_disagg_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, CodDesag);
2156: uniqueField_disAggKeys.delete;
2157: uniqueField_disAggKeys := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations(l_disagg_index).keys;
2151: --so far.
2152: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2153: bsc_mo_helper_pkg.writeTmp( 'Disaggregation already exists');
2154: END IF;
2155: l_disagg_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, CodDesag);
2156: uniqueField_disAggKeys.delete;
2157: uniqueField_disAggKeys := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations(l_disagg_index).keys;
2158: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2159: bsc_mo_helper_pkg.writeTmp( 'Matching disagg index '||l_disagg_index);
2155: l_disagg_index := BSC_MO_HELPER_PKG.findIndex(uniqueField.key_combinations, CodDesag);
2156: uniqueField_disAggKeys.delete;
2157: uniqueField_disAggKeys := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc(l_field_index).key_combinations(l_disagg_index).keys;
2158: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2159: bsc_mo_helper_pkg.writeTmp( 'Matching disagg index '||l_disagg_index);
2160: END IF;
2161: IF uniqueField_disAggKeys.count>0 THEN
2162: FOR l_count3 IN uniqueField_disAggKeys.first..uniqueField_disAggKeys.last
2163: LOOP
2161: IF uniqueField_disAggKeys.count>0 THEN
2162: FOR l_count3 IN uniqueField_disAggKeys.first..uniqueField_disAggKeys.last
2163: LOOP
2164: l_key := uniqueField_disAggKeys(l_count3);
2165: l_temp := BSC_MO_HELPER_PKG.findIndex(l_table.keys, l_key.keyName);
2166: If l_table.keys(l_temp).NeedsCode0 Then
2167: l_key.NeedsCode0 := l_table.keys(l_temp).NeedsCode0;
2168: End If;
2169: uniqueField_disAggKeys(l_count3) := l_key;
2187: l_count1 := p_tables.next(l_count1);
2188: l_stack := null;
2189: END LOOP;
2190: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2191: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2192: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2193: END IF;
2194: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_precalc = ');
2195: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc );
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:
2190: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2191: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures_precalc, g_unique_measures_precalc.count='||
2192: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2193: END IF;
2194: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_precalc = ');
2195: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc );
2196:
2197: EXCEPTION WHEN OTHERS THEN
2198: l_error := sqlerrm;
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);
2195: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_precalc );
2196:
2197: EXCEPTION WHEN OTHERS THEN
2198: l_error := sqlerrm;
2199: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures_precalc : '||l_error);
2200: BSC_MO_HELPER_PKG.writeTmp('l_stack : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2201: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PC_DATACOL_FAILED', 'init_s_table_measures_precalc');
2202: raise;
2203: End;
2196:
2197: EXCEPTION WHEN OTHERS THEN
2198: l_error := sqlerrm;
2199: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures_precalc : '||l_error);
2200: BSC_MO_HELPER_PKG.writeTmp('l_stack : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2201: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PC_DATACOL_FAILED', 'init_s_table_measures_precalc');
2202: raise;
2203: End;
2204:
2197: EXCEPTION WHEN OTHERS THEN
2198: l_error := sqlerrm;
2199: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures_precalc : '||l_error);
2200: BSC_MO_HELPER_PKG.writeTmp('l_stack : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2201: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PC_DATACOL_FAILED', 'init_s_table_measures_precalc');
2202: raise;
2203: End;
2204:
2205:
2242: IF (p_tables.count=0) THEN
2243: return;
2244: END IF;
2245: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2246: BSC_MO_HELPER_PKG.writeTmp('Inside init_s_table_measures, p_tables.count = '||p_tables.count||', g_unique_measures .count='||
2247: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures .count||
2248: ', g_unique_measures_tgt.count='||BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count||' '||bsc_mo_helper_pkg.get_time
2249: , FND_LOG.LEVEL_PROCEDURE);
2250: END IF;
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
2259: --Only consider tables not having origin and belong to indicators no-precalculated
2260: --Also only consider tables that are not for targets
2261: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_clstable.Indicator);
2262: IF (l_temp = -1) THEN
2263: l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_clstable.Indicator, 'DB_TRANSFORM', 1);
2264: l_impl_type := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_clstable.Indicator, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1);
2265: ELSE
2266: l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).OptimizationMode;
2267: l_impl_type := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_temp).impl_type;
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;
2274: If ((l_clstable.originTable IS NULL AND l_clstable.isProductionTable=false) OR
2275: l_clstable.isProductionTable=true
2276: )
2277: And l_optimizationMode <> 0 Then
2278: BSC_MO_HELPER_PKG.writeTmp(' ');
2279: BSC_MO_HELPER_PKG.writeTmp(' ');
2280: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_clstable.name);
2281: --l_table_measures := l_clstable.Data;
2282: --l_table_keys := l_clstable.keys;
2275: l_clstable.isProductionTable=true
2276: )
2277: And l_optimizationMode <> 0 Then
2278: BSC_MO_HELPER_PKG.writeTmp(' ');
2279: BSC_MO_HELPER_PKG.writeTmp(' ');
2280: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_clstable.name);
2281: --l_table_measures := l_clstable.Data;
2282: --l_table_keys := l_clstable.keys;
2283: BSC_MO_HELPER_PKG.writeTmp(' l_clstable.data.count = '||l_clstable.data.count||', l_clstable.keys.count='||l_clstable.keys.count );
2276: )
2277: And l_optimizationMode <> 0 Then
2278: BSC_MO_HELPER_PKG.writeTmp(' ');
2279: BSC_MO_HELPER_PKG.writeTmp(' ');
2280: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_clstable.name);
2281: --l_table_measures := l_clstable.Data;
2282: --l_table_keys := l_clstable.keys;
2283: BSC_MO_HELPER_PKG.writeTmp(' l_clstable.data.count = '||l_clstable.data.count||', l_clstable.keys.count='||l_clstable.keys.count );
2284: If l_clstable.IsTargetTable Then
2279: BSC_MO_HELPER_PKG.writeTmp(' ');
2280: BSC_MO_HELPER_PKG.writeTmp('Processing table '||l_clstable.name);
2281: --l_table_measures := l_clstable.Data;
2282: --l_table_keys := l_clstable.keys;
2283: BSC_MO_HELPER_PKG.writeTmp(' l_clstable.data.count = '||l_clstable.data.count||', l_clstable.keys.count='||l_clstable.keys.count );
2284: If l_clstable.IsTargetTable Then
2285: l_stack := l_stack||' Target table';
2286: l_unique_measures := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt;
2287: Else
2301: --tables and are not going to be base or input tables.
2302: If l_measure.InternalColumnType = 0 Then
2303: l_stack := l_stack||g_newline||' Checking field '||l_measure.fieldName;
2304: l_stack := l_stack||g_newline||' --------------------';
2305: BSC_MO_HELPER_PKG.writeTmp(' ');
2306: BSC_MO_HELPER_PKG.writeTmp(' Checking field '||l_measure.fieldName||', source='||l_measure.source);
2307: BSC_MO_HELPER_PKG.writeTmp(' --------------------------------------');
2308: IF length(l_stack )> 31000 THEN
2309: l_stack := substr(l_stack, 30000);
2302: If l_measure.InternalColumnType = 0 Then
2303: l_stack := l_stack||g_newline||' Checking field '||l_measure.fieldName;
2304: l_stack := l_stack||g_newline||' --------------------';
2305: BSC_MO_HELPER_PKG.writeTmp(' ');
2306: BSC_MO_HELPER_PKG.writeTmp(' Checking field '||l_measure.fieldName||', source='||l_measure.source);
2307: BSC_MO_HELPER_PKG.writeTmp(' --------------------------------------');
2308: IF length(l_stack )> 31000 THEN
2309: l_stack := substr(l_stack, 30000);
2310: END IF;
2303: l_stack := l_stack||g_newline||' Checking field '||l_measure.fieldName;
2304: l_stack := l_stack||g_newline||' --------------------';
2305: BSC_MO_HELPER_PKG.writeTmp(' ');
2306: BSC_MO_HELPER_PKG.writeTmp(' Checking field '||l_measure.fieldName||', source='||l_measure.source);
2307: BSC_MO_HELPER_PKG.writeTmp(' --------------------------------------');
2308: IF length(l_stack )> 31000 THEN
2309: l_stack := substr(l_stack, 30000);
2310: END IF;
2311: If UniqueFieldExists(l_measure.fieldName, l_measure.source, l_impl_type, l_unique_measures)=false Then
2310: END IF;
2311: If UniqueFieldExists(l_measure.fieldName, l_measure.source, l_impl_type, l_unique_measures)=false Then
2312: l_stack := l_stack||g_newline||'..Field does not exist';
2313: -- If its a production table, dont add the measures that are used by Indicators in this iteration
2314: BSC_MO_HELPER_PKG.writeTmp('..does not exist --> Add it');
2315: --Field does not exists --> Add it
2316: uniqueField := BSC_Mo_helper_PKG.new_clsUniqueField;
2317: uniqueField.fieldName := l_measure.fieldName;
2318: uniqueField.source := l_measure.source;
2312: l_stack := l_stack||g_newline||'..Field does not exist';
2313: -- If its a production table, dont add the measures that are used by Indicators in this iteration
2314: BSC_MO_HELPER_PKG.writeTmp('..does not exist --> Add it');
2315: --Field does not exists --> Add it
2316: uniqueField := BSC_Mo_helper_PKG.new_clsUniqueField;
2317: uniqueField.fieldName := l_measure.fieldName;
2318: uniqueField.source := l_measure.source;
2319: uniqueField.measureGroup := get_measure_group(uniqueField.fieldName, l_measure.source);
2320: uniqueField.aggFunction := l_measure.aggFunction;
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;
2325: l_stack := l_stack||g_newline||'l_impl_type='||l_impl_type;
2326: uniqueField.impl_type := l_impl_type;
2327: --Desags. It initialized with one element that correspond to the disagregation
2328: --and periodicity of the table
2329: disaggField := BSC_Mo_helper_PKG.new_clsDisAggField;
2330: disaggField.Code := 1;
2331: disaggField.Periodicity := l_clstable.Periodicity;
2332: -- Important, mark this as from existing table
2333: disaggField.isProduction := l_clsTable.isProductionTable;
2336: l_stack := l_stack||g_newline||'Table keys.count='||l_clstable.keys.count;
2337: IF (l_clstable.keys.count>0) THEN
2338: FOR l_count3 IN l_clstable.keys.first..l_clstable.keys.last
2339: LOOP
2340: l_key := BSC_Mo_helper_PKG.new_clsKeyField;
2341: l_key := l_clstable.keys (l_count3);
2342: disaggKeyField := bsc_mo_helper_pkg.new_clsKeyField;
2343: disaggKeyField.keyName := l_key.keyName;
2344: disaggKeyField.Origin := l_key.Origin;
2338: FOR l_count3 IN l_clstable.keys.first..l_clstable.keys.last
2339: LOOP
2340: l_key := BSC_Mo_helper_PKG.new_clsKeyField;
2341: l_key := l_clstable.keys (l_count3);
2342: disaggKeyField := bsc_mo_helper_pkg.new_clsKeyField;
2343: disaggKeyField.keyName := l_key.keyName;
2344: disaggKeyField.Origin := l_key.Origin;
2345: disaggKeyField.NeedsCode0 := False;
2346: disaggKeyField.CalculateCode0 := False;
2358: disaggField.isProduction := False;
2359: END IF;
2360: l_stack := l_stack||g_newline||'Disagg keys = '||disaggField.keys.count;
2361: uniqueField.key_combinations(uniqueField.key_combinations.count) := disaggField;
2362: BSC_MO_HELPER_PKG.writeTmp('Adding to unique field');
2363: BSC_MO_HELPER_PKG.write_this(null, uniqueField);
2364: --Add the unique field to the collection l_unique_measures
2365: If l_clstable.IsTargetTable Then
2366: l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count;
2359: END IF;
2360: l_stack := l_stack||g_newline||'Disagg keys = '||disaggField.keys.count;
2361: uniqueField.key_combinations(uniqueField.key_combinations.count) := disaggField;
2362: BSC_MO_HELPER_PKG.writeTmp('Adding to unique field');
2363: BSC_MO_HELPER_PKG.write_this(null, uniqueField);
2364: --Add the unique field to the collection l_unique_measures
2365: If l_clstable.IsTargetTable Then
2366: l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count;
2367: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_index2) := uniqueField;
2365: If l_clstable.IsTargetTable Then
2366: l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt.count;
2367: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_index2) := uniqueField;
2368: l_stack := l_stack||g_newline||'Added g_unique_measures ('||l_index2||'), count = '||l_index2;
2369: BSC_MO_HELPER_PKG.writeTMp('Added '||l_measure.fieldName||', source='||l_measure.source||
2370: ' to g_unique_measures_tgt at index='||l_index2 );
2371: Else
2372: l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures.count;
2373: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_index2) := uniqueField;
2371: Else
2372: l_index2 := BSC_METADATA_OPTIMIZER_PKG.g_unique_measures.count;
2373: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_index2) := uniqueField;
2374: l_stack := l_stack||g_newline||'Added g_unique_measures ('||l_index2||'), count = '||l_index2;
2375: BSC_MO_HELPER_PKG.writeTMp('Added '||l_measure.fieldName||', source='||l_measure.source||
2376: ' to g_unique_measures at index='||l_index2);
2377: End If;
2378: Else
2379: --The field already exists. So check if its periodicity and disagregation
2378: Else
2379: --The field already exists. So check if its periodicity and disagregation
2380: -- is in its list of disagregations
2381: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2382: BSC_MO_HELPER_PKG.writeTMp(l_measure.fieldName||'..Field already exists --> check periodicity and disagregation');
2383: END IF;
2384: l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2385: l_disagg_code := GetDisaggCode(l_unique_measures(l_temp).fieldName,
2386: l_unique_measures(l_temp).key_combinations,
2380: -- is in its list of disagregations
2381: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2382: BSC_MO_HELPER_PKG.writeTMp(l_measure.fieldName||'..Field already exists --> check periodicity and disagregation');
2383: END IF;
2384: l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2385: l_disagg_code := GetDisaggCode(l_unique_measures(l_temp).fieldName,
2386: l_unique_measures(l_temp).key_combinations,
2387: l_clstable.Periodicity,
2388: l_clstable.keys,
2388: l_clstable.keys,
2389: l_Tablename);
2390: IF l_disagg_code = -1 Then
2391: l_stack := l_stack||g_newline||'Disagg does not exist -> Add it ';
2392: bsc_mo_helper_pkg.writeTmp( 'Disagg does not exist -> Add it ');
2393: --It does not exist --> Add it
2394: l_temp := bsc_mo_helper_pkg.findIndex(l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2395: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2396: disaggField.Code := l_unique_measures(l_temp).key_combinations.count + 1;
2390: IF l_disagg_code = -1 Then
2391: l_stack := l_stack||g_newline||'Disagg does not exist -> Add it ';
2392: bsc_mo_helper_pkg.writeTmp( 'Disagg does not exist -> Add it ');
2393: --It does not exist --> Add it
2394: l_temp := bsc_mo_helper_pkg.findIndex(l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2395: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2396: disaggField.Code := l_unique_measures(l_temp).key_combinations.count + 1;
2397: disaggField.Periodicity := l_clstable.Periodicity;
2398: --Key columns. Same as the table
2391: l_stack := l_stack||g_newline||'Disagg does not exist -> Add it ';
2392: bsc_mo_helper_pkg.writeTmp( 'Disagg does not exist -> Add it ');
2393: --It does not exist --> Add it
2394: l_temp := bsc_mo_helper_pkg.findIndex(l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2395: disaggField := bsc_mo_helper_pkg.new_clsDisAggField;
2396: disaggField.Code := l_unique_measures(l_temp).key_combinations.count + 1;
2397: disaggField.Periodicity := l_clstable.Periodicity;
2398: --Key columns. Same as the table
2399: IF (l_clstable.keys.count>0) THEN
2398: --Key columns. Same as the table
2399: IF (l_clstable.keys.count>0) THEN
2400: FOR l_count3 IN l_clstable.keys.first..l_clstable.keys.last LOOP
2401: l_key := l_clstable.keys(l_count3);
2402: disaggKeyField := bsc_mo_helper_pkg.new_clsKeyField;
2403: disaggKeyField.keyName := l_key.keyName;
2404: disaggKeyField.Origin := l_key.Origin;
2405: disaggKeyField.NeedsCode0 := False;
2406: disaggKeyField.CalculateCode0 := False;
2419: disaggField.isProduction := False;
2420: END IF;
2421: l_stack := l_stack||g_newline||'2. Disagg keys = '||disaggField.keys.count;
2422: --Add the disagregation to the collection Desags of the unique field
2423: l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2424: --Copy back the unique field to the collection l_unique_measures
2425: If l_clstable.IsTargetTable Then
2426: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations
2427: (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations.count) := disaggField;
2424: --Copy back the unique field to the collection l_unique_measures
2425: If l_clstable.IsTargetTable Then
2426: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations
2427: (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations.count) := disaggField;
2428: bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures_tgt at index='||l_temp);
2429: Else
2430: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations
2431: (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures(l_temp).key_combinations.count) := disaggField;
2432: bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures at index='||l_temp);
2428: bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures_tgt at index='||l_temp);
2429: Else
2430: BSC_METADATA_OPTIMIZER_PKG.g_unique_measures (l_temp).key_combinations
2431: (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures(l_temp).key_combinations.count) := disaggField;
2432: bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures at index='||l_temp);
2433: End If;
2434: ELSE -- disagg code is <> -1
2435: IF (l_clstable.isProductionTable) THEN
2436: l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2432: bsc_mo_helper_pkg.writeTmp('Copy back disagg to g_unique_measures at index='||l_temp);
2433: End If;
2434: ELSE -- disagg code is <> -1
2435: IF (l_clstable.isProductionTable) THEN
2436: l_temp := BSC_MO_HELPER_PKG.findIndex (l_unique_measures, l_measure.fieldName, l_measure.source, l_impl_type);
2437: If l_clstable.IsTargetTable Then
2438: -- If another table requires this disagg, then we should still leave it as unregistered
2439: IF BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt(l_temp).key_combinations(l_disagg_code-1).Registered = false THEN
2440: null;
2464: EXIT WHEN l_count = p_tables.last;
2465: l_count := p_tables.next(l_count);
2466: END LOOP;
2467: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2468: BSC_MO_HELPER_PKG.writeTmp('Done with init_s_table_measures, writing output now');
2469: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures = ');
2470: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures );
2471: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt = ');
2472: BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2465: l_count := p_tables.next(l_count);
2466: END LOOP;
2467: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2468: BSC_MO_HELPER_PKG.writeTmp('Done with init_s_table_measures, writing output now');
2469: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures = ');
2470: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures );
2471: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt = ');
2472: BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2473: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
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;
2467: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2468: BSC_MO_HELPER_PKG.writeTmp('Done with init_s_table_measures, writing output now');
2469: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures = ');
2470: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures );
2471: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt = ');
2472: BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2473: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2474: END IF;
2475: EXCEPTION WHEN OTHERS THEN
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);
2469: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures = ');
2470: BSC_MO_HELPER_PKG.write_this(g_unique, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures );
2471: BSC_MO_HELPER_PKG.writeTmp('g_unique_measures_tgt = ');
2472: BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2473: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2474: END IF;
2475: EXCEPTION WHEN OTHERS THEN
2476: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2477: BSC_MO_HELPER_PKG.writeTmp('l_stack = '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2472: BSC_MO_HELPER_PKG.write_this(g_Target, BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt);
2473: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2474: END IF;
2475: EXCEPTION WHEN OTHERS THEN
2476: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2477: BSC_MO_HELPER_PKG.writeTmp('l_stack = '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2478: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2479: raise;
2480: End ;
2473: BSC_MO_HELPER_PKG.writeTmp('Completed init_s_table_measures, system time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
2474: END IF;
2475: EXCEPTION WHEN OTHERS THEN
2476: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2477: BSC_MO_HELPER_PKG.writeTmp('l_stack = '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2478: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2479: raise;
2480: End ;
2481:
2474: END IF;
2475: EXCEPTION WHEN OTHERS THEN
2476: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2477: BSC_MO_HELPER_PKG.writeTmp('l_stack = '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2478: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in init_s_table_measures : '||sqlerrm);
2479: raise;
2480: End ;
2481:
2482:
2493: KpiTable bsc_metadata_optimizer_pkg.clsTable;
2494: l_index NUMBER;
2495: l_error VARCHAR2(3000);
2496: BEGIN
2497: BSC_MO_HELPER_PKG.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
2498: BSC_MO_HELPER_PKG.writeTmp('Inside InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2499: --BSC-MV Note: If the indicator is not processed for structural changes and
2500: --the summarization level was changed from NULL to NOT NULL (upgrade)
2501: --then I need to get from the database the source table of the indicator and set
2494: l_index NUMBER;
2495: l_error VARCHAR2(3000);
2496: BEGIN
2497: BSC_MO_HELPER_PKG.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
2498: BSC_MO_HELPER_PKG.writeTmp('Inside InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2499: --BSC-MV Note: If the indicator is not processed for structural changes and
2500: --the summarization level was changed from NULL to NOT NULL (upgrade)
2501: --then I need to get from the database the source table of the indicator and set
2502: --the origin in gTablas.
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;
2528: --b. EDW KPIs and BSC KPIs dont share periodicities.
2529: --c. EDW KPIs and BSC KPIs dont share measures.
2530: --Initilize the list of unique measures for indicators being processed
2531: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures');
2532: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures', FND_LOG.LEVEL_STATEMENT, true);
2533: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.gTables);
2534:
2535: --Initilize the list of unique measures for indicators being processed - Precalc
2536: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc');
2533: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.gTables);
2534:
2535: --Initilize the list of unique measures for indicators being processed - Precalc
2536: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc');
2537: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc', FND_LOG.LEVEL_STATEMENT, true);
2538: init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.gTables);
2539:
2540: -- Load the existing B and T tables into memory for better optimization
2541: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling LOAD_B_T_TABLES_FROM_DB');
2538: init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.gTables);
2539:
2540: -- Load the existing B and T tables into memory for better optimization
2541: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling LOAD_B_T_TABLES_FROM_DB');
2542: BSC_MO_HELPER_PKG.writeTmp('Calling LOAD_B_T_TABLES_FROM_DB', FND_LOG.LEVEL_STATEMENT, true);
2543: LOAD_B_T_TABLES_FROM_DB;
2544:
2545: --Resolve the origin of each dissagregation of each field
2546: --This is done only for non-precalculated fields
2544:
2545: --Resolve the origin of each dissagregation of each field
2546: --This is done only for non-precalculated fields
2547: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling resolve_key_origins');
2548: BSC_MO_HELPER_PKG.writeTmp('Calling resolve_key_origins', FND_LOG.LEVEL_STATEMENT, true);
2549: resolve_key_origins (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures , false);
2550:
2551: --Resolve the origin of each dissagregation for Targets
2552: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling resolve_key_origins for Targets');
2549: resolve_key_origins (BSC_METADATA_OPTIMIZER_PKG.g_unique_measures , false);
2550:
2551: --Resolve the origin of each dissagregation for Targets
2552: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling resolve_key_origins for Targets');
2553: BSC_MO_HELPER_PKG.writeTmp('Calling resolve_key_origins for Targets', FND_LOG.LEVEL_STATEMENT, true);
2554: resolve_key_origins ( BSC_METADATA_OPTIMIZER_PKG.g_unique_measures_tgt, true);
2555:
2556: --Make the list of temporal and base tables
2557: BSC_METADATA_OPTIMIZER_PKG.gMaxT := getMaxTableIndex('BSC_T_');
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;
2566: BSC_METADATA_OPTIMIZER_PKG.gMaxB||', gMaxI='||BSC_METADATA_OPTIMIZER_PKG.gMaxI);
2567: IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2568: return;
2569: END IF;
2570: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Normal', FND_LOG.LEVEL_STATEMENT, true);
2571: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables - Normal');
2572: deduce_bt_tables(False);
2573:
2574: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Targets', FND_LOG.LEVEL_STATEMENT, true);
2570: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Normal', FND_LOG.LEVEL_STATEMENT, true);
2571: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables - Normal');
2572: deduce_bt_tables(False);
2573:
2574: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables - Targets', FND_LOG.LEVEL_STATEMENT, true);
2575: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables - Targets');
2576: deduce_bt_tables (True );--For targets
2577:
2578: IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2578: IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2579: return;
2580: END IF;
2581: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2582: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT);
2583: END IF;
2584: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT, true);
2585: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables_precalc');
2586: deduce_bt_tables_precalc;
2580: END IF;
2581: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2582: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT);
2583: END IF;
2584: BSC_MO_HELPER_PKG.writeTmp('Calling deduce_bt_tables_precalc', FND_LOG.LEVEL_STATEMENT, true);
2585: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling deduce_bt_tables_precalc');
2586: deduce_bt_tables_precalc;
2587:
2588: IF (bsc_metadata_optimizer_pkg.g_retcode <> 0) THEN
2589: return;
2590: END IF;
2591:
2592: --Connect base indicator tables with temporal tables
2593: BSC_MO_HELPER_PKG.writeTmp('Calling connect_s_to_b_tables', FND_LOG.LEVEL_STATEMENT, true);
2594: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling connect_s_to_b_tables');
2595: connect_s_to_b_tables;
2596:
2597: --Add each temporal table to the collection of system tables
2594: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling connect_s_to_b_tables');
2595: connect_s_to_b_tables;
2596:
2597: --Add each temporal table to the collection of system tables
2598: BSC_MO_HELPER_PKG.writeTmp('Calling add_to_gtables', FND_LOG.LEVEL_STATEMENT, true);
2599:
2600: add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
2601: add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2602: add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2601: add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2602: add_to_gtables (BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2603:
2604: --Make input tables and connect them to the base tables
2605: BSC_MO_HELPER_PKG.writeTmp('Calling connect_i_to_b_tables', FND_LOG.LEVEL_STATEMENT, true);
2606: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling connect_i_to_b_tables');
2607: connect_i_to_b_tables;
2608:
2609:
2618: bsc_metadata_optimizer_pkg.garrTablesUpgrade);
2619: bsc_metadata_optimizer_pkg.gnumTablesUpgrade := bsc_metadata_optimizer_pkg.garrTablesUpgrade.count;
2620: load_upgrade_tables_db;
2621: End If;
2622: BSC_MO_HELPER_PKG.writeTmp('-------------------------------------', FND_LOG.LEVEL_STATEMENT, true);
2623: BSC_MO_HELPER_PKG.writeTmp('Completed InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2624:
2625: EXCEPTION WHEN OTHERS THEN
2626: l_error := sqlerrm;
2619: bsc_metadata_optimizer_pkg.gnumTablesUpgrade := bsc_metadata_optimizer_pkg.garrTablesUpgrade.count;
2620: load_upgrade_tables_db;
2621: End If;
2622: BSC_MO_HELPER_PKG.writeTmp('-------------------------------------', FND_LOG.LEVEL_STATEMENT, true);
2623: BSC_MO_HELPER_PKG.writeTmp('Completed InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2624:
2625: EXCEPTION WHEN OTHERS THEN
2626: l_error := sqlerrm;
2627: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InputTables : '||l_error);
2623: BSC_MO_HELPER_PKG.writeTmp('Completed InputTables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2624:
2625: EXCEPTION WHEN OTHERS THEN
2626: l_error := sqlerrm;
2627: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InputTables : '||l_error);
2628: BSC_MO_HELPER_PKG.TerminateWithError ('BSC_ITABLE_PROD_FAILED', 'InputTables');
2629: raise;
2630: End ;
2631:
2624:
2625: EXCEPTION WHEN OTHERS THEN
2626: l_error := sqlerrm;
2627: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InputTables : '||l_error);
2628: BSC_MO_HELPER_PKG.TerminateWithError ('BSC_ITABLE_PROD_FAILED', 'InputTables');
2629: raise;
2630: End ;
2631:
2632:
2697: l_src_table VARCHAR2(100);
2698: l_state varchar2(4000);
2699:
2700: BEGIN
2701: bsc_mo_helper_pkg.writeTmp('Loading table '||p_table||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2702: l_kpis_using_table := BSC_MO_HELPER_PKG.find_objectives_for_table(p_table);
2703: IF (l_kpis_using_table.count=0) THEN
2704: return;
2705: END IF;
2698: l_state varchar2(4000);
2699:
2700: BEGIN
2701: bsc_mo_helper_pkg.writeTmp('Loading table '||p_table||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2702: l_kpis_using_table := BSC_MO_HELPER_PKG.find_objectives_for_table(p_table);
2703: IF (l_kpis_using_table.count=0) THEN
2704: return;
2705: END IF;
2706: bsc_mo_helper_pkg.writeTmp('# of objectives using this table= '||l_kpis_using_table.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2702: l_kpis_using_table := BSC_MO_HELPER_PKG.find_objectives_for_table(p_table);
2703: IF (l_kpis_using_table.count=0) THEN
2704: return;
2705: END IF;
2706: bsc_mo_helper_pkg.writeTmp('# of objectives using this table= '||l_kpis_using_table.count||' '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
2707: OPEN cKeys;
2708: LOOP
2709: FETCH cKeys INTO l_column;
2710: EXIT WHEN cKeys%NOTFOUND;
2719: l_key.needsCode0 := true;
2720: l_key.calculateCode0 := true;
2721: END IF;
2722: -- find out if filters exist
2723: IF BSC_MO_HELPER_PKG.filters_exist(
2724: l_kpis_using_table(0).kpi_number,
2725: l_kpis_using_table(0).dim_set_id,
2726: l_column,
2727: l_filter_view ) THEN
2729: END IF;
2730: l_key_list(l_key_list.count) := l_key;
2731: END LOOP;
2732: CLOSE cKeys;
2733: l_state := 'Loaded keys '||bsc_mo_helper_pkg.get_time;
2734: -- now onto the measure columns
2735: OPEN cData(bsc_metadata_optimizer_pkg.gBSCSchema);
2736: LOOP
2737: FETCH cData INTO l_column, l_source, l_measure_group;
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);
2749: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, l_kpis_using_table(0).kpi_number);
2750: IF (l_index <> -1) THEN
2751: l_optimizationMode := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).optimizationMode ;
2752: ELSE
2753: l_optimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(l_kpis_using_table(0).kpi_number, 'DB_TRANSFORM', 1);
2754: END IF;
2755: l_table.Name := p_table;
2756: l_table.Type := 1;-- base, temporal or summary
2757: l_table.keys := l_key_list;
2755: l_table.Name := p_table;
2756: l_table.Type := 1;-- base, temporal or summary
2757: l_table.keys := l_key_list;
2758: l_table.data := l_data_list;
2759: l_table.Periodicity := BSC_MO_HELPER_PKG.getPeriodicityForTable(p_table);
2760: l_table.originTable := null;--BSC_MO_HELPER_PKG.getSourceTable(p_table);
2761: l_table.Indicator := l_kpis_using_table(0).kpi_number;
2762: l_table.Configuration := l_kpis_using_table(0).dim_set_id;
2763: l_table.EDW_Flag := 0;
2756: l_table.Type := 1;-- base, temporal or summary
2757: l_table.keys := l_key_list;
2758: l_table.data := l_data_list;
2759: l_table.Periodicity := BSC_MO_HELPER_PKG.getPeriodicityForTable(p_table);
2760: l_table.originTable := null;--BSC_MO_HELPER_PKG.getSourceTable(p_table);
2761: l_table.Indicator := l_kpis_using_table(0).kpi_number;
2762: l_table.Configuration := l_kpis_using_table(0).dim_set_id;
2763: l_table.EDW_Flag := 0;
2764: l_table.IsTargetTable := false;
2769: l_table.measureGroup := l_data_list(l_data_list.first).measureGroup;
2770: else
2771: l_table.measureGroup := get_measure_group(p_table, l_data_list, p_indicator_num, p_dim_set);
2772: end if;
2773: bsc_mo_helper_pkg.writeTmp('measure group = '||l_table.measureGroup);
2774: -- Important, flag that this is as a production table, so it isnt dropped and recreated !
2775: l_table.isProductionTable := true;
2776: l_table.isProductionTableAltered := false;
2777: l_state := l_state || ' Loaded other properties ';
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;
2804: END IF;
2805: l_state := l_state||'
2806: Loaded table '||p_table;
2807: EXCEPTION WHEN OTHERS THEN
2808: BSC_MO_HELPER_PKG.writeTmp('state = '||l_state, FND_LOG.LEVEL_EXCEPTION, true);
2809: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Load_Table_From_DB : '||sqlerrm);
2810:
2811: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'Load_Table_From_DB');
2812: raise;
2805: l_state := l_state||'
2806: Loaded table '||p_table;
2807: EXCEPTION WHEN OTHERS THEN
2808: BSC_MO_HELPER_PKG.writeTmp('state = '||l_state, FND_LOG.LEVEL_EXCEPTION, true);
2809: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Load_Table_From_DB : '||sqlerrm);
2810:
2811: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'Load_Table_From_DB');
2812: raise;
2813: END;
2807: EXCEPTION WHEN OTHERS THEN
2808: BSC_MO_HELPER_PKG.writeTmp('state = '||l_state, FND_LOG.LEVEL_EXCEPTION, true);
2809: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Load_Table_From_DB : '||sqlerrm);
2810:
2811: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'Load_Table_From_DB');
2812: raise;
2813: END;
2814:
2815: FUNCTION is_production_table(p_table_name IN VARCHAR2, p_indicator OUT NOCOPY NUMBER, p_dim_set OUT NOCOPY NUMBER) return boolean
2854: l_stmt VARCHAR2(1000);
2855: l_indicator_num number;
2856: l_dim_set number;
2857: BEGIN
2858: BSC_MO_HELPER_PKG.writeTmp('Inside LOAD_B_T_TABLES_FROM_DB, system time is '||
2859: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2860: l_stmt := 'select distinct table_name from bsc_db_tables where (table_name like ''BSC_B%'' or table_name like ''BSC_T%'' ) and table_type=1';
2861: OPEN cv FOR l_stmt ;
2862: LOOP
2855: l_indicator_num number;
2856: l_dim_set number;
2857: BEGIN
2858: BSC_MO_HELPER_PKG.writeTmp('Inside LOAD_B_T_TABLES_FROM_DB, system time is '||
2859: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2860: l_stmt := 'select distinct table_name from bsc_db_tables where (table_name like ''BSC_B%'' or table_name like ''BSC_T%'' ) and table_type=1';
2861: OPEN cv FOR l_stmt ;
2862: LOOP
2863: FETCH cv INTO l_table;
2862: LOOP
2863: FETCH cv INTO l_table;
2864: EXIT WHEN cv%NOTFOUND;
2865: IF is_production_table(l_table, l_indicator_num, l_dim_set) THEN
2866: BSC_MO_HELPER_PKG.writeTmp('Loading production table '||l_table||' into memory '||BSC_MO_HELPER_PKG.get_time, FND_LOG.LEVEL_PROCEDURE, false);
2867: Load_Table_From_DB(l_table, l_indicator_num, l_dim_set);
2868: END IF;
2869: END LOOP;
2870:
2869: END LOOP;
2870:
2871: -- Intialize the disaggregations in the Production tables - Targets
2872: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures for Production tables - Targets');
2873: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures for Production tables - Targets', FND_LOG.LEVEL_STATEMENT, true);
2874: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2875:
2876: -- Intialize the disaggregations in the Production tables
2877: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures for Production tables - Normal');
2874: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_tgt);
2875:
2876: -- Intialize the disaggregations in the Production tables
2877: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures for Production tables - Normal');
2878: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures for Production tables - Normal', FND_LOG.LEVEL_STATEMENT, true);
2879: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
2880:
2881: -- Intialize the disaggregations in the Production tables - precalc
2882: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc for Production tables');
2879: init_s_table_measures(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables);
2880:
2881: -- Intialize the disaggregations in the Production tables - precalc
2882: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc for Production tables');
2883: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc for Production tables', FND_LOG.LEVEL_STATEMENT, true);
2884: init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2885:
2886: BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2882: BSC_METADATA_OPTIMIZER_PKG.logProgress('INPUT', 'Calling init_s_table_measures_precalc for Production tables');
2883: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc for Production tables', FND_LOG.LEVEL_STATEMENT, true);
2884: init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2885:
2886: BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2888: EXCEPTION WHEN OTHERS THEN
2889: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in LOAD_B_T_TABLES_FROM_DB : '||sqlerrm);
2890: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'LOAD_B_T_TABLES_FROM_DB');
2883: BSC_MO_HELPER_PKG.writeTmp('Calling init_s_table_measures_precalc for Production tables', FND_LOG.LEVEL_STATEMENT, true);
2884: init_s_table_measures_precalc(BSC_METADATA_OPTIMIZER_PKG.g_bt_tables_precalc);
2885:
2886: BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2888: EXCEPTION WHEN OTHERS THEN
2889: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in LOAD_B_T_TABLES_FROM_DB : '||sqlerrm);
2890: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'LOAD_B_T_TABLES_FROM_DB');
2891: raise;
2885:
2886: BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2888: EXCEPTION WHEN OTHERS THEN
2889: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in LOAD_B_T_TABLES_FROM_DB : '||sqlerrm);
2890: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'LOAD_B_T_TABLES_FROM_DB');
2891: raise;
2892: END;
2893:
2886: BSC_MO_HELPER_PKG.writeTmp('Completed LOAD_B_T_TABLES_FROM_DB, system time is '||
2887: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2888: EXCEPTION WHEN OTHERS THEN
2889: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in LOAD_B_T_TABLES_FROM_DB : '||sqlerrm);
2890: BSC_MO_HELPER_PKG.terminateWithError('BSC_RETR_TTABLES_FAILED', 'LOAD_B_T_TABLES_FROM_DB');
2891: raise;
2892: END;
2893:
2894: END BSC_MO_INPUT_TABLE_PKG ;