[Home] [Help]
31: and tbl.table_type = 0;
32:
33: l_table_type NUMBER;
34: BEGIN
35: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, p_table_name);
36: If l_temp <> -1 AND BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable IS NOT NULL Then
37: l_origin_table := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable, ',');
38: l_origin_name := l_origin_table(l_origin_table.first);
39: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_origin_name);
32:
33: l_table_type NUMBER;
34: BEGIN
35: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, p_table_name);
36: If l_temp <> -1 AND BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable IS NOT NULL Then
37: l_origin_table := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable, ',');
38: l_origin_name := l_origin_table(l_origin_table.first);
39: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_origin_name);
40: IF (l_temp2 = -1) THEN
33: l_table_type NUMBER;
34: BEGIN
35: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, p_table_name);
36: If l_temp <> -1 AND BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable IS NOT NULL Then
37: l_origin_table := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable, ',');
38: l_origin_name := l_origin_table(l_origin_table.first);
39: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_origin_name);
40: IF (l_temp2 = -1) THEN
41: OPEN cTableType;
35: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, p_table_name);
36: If l_temp <> -1 AND BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable IS NOT NULL Then
37: l_origin_table := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).originTable, ',');
38: l_origin_name := l_origin_table(l_origin_table.first);
39: l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_origin_name);
40: IF (l_temp2 = -1) THEN
41: OPEN cTableType;
42: FETCH cTableType INTO l_table_type ;
43: CLOSE cTableType;
46: ELSE
47: return true;
48: END IF;
49: END IF;
50: If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp2).Type = 0 Then
51: return true;
52: Else
53: return False;
54: End If;
79: -- PARAMETERS:
80: -- colDimensions: collection of drills families
81: -- p_level_code: drill code
82: --****************************************************************************
83: Function GetPhantomLevelPosition(colDimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_level_code IN NUMBER) RETURN NUMBER IS
84: posicion NUMBER;
85: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
86: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
87: l_index1 NUMBER;
81: -- p_level_code: drill code
82: --****************************************************************************
83: Function GetPhantomLevelPosition(colDimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_level_code IN NUMBER) RETURN NUMBER IS
84: posicion NUMBER;
85: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
86: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
87: l_index1 NUMBER;
88: l_index2 NUMBER;
89: l_groups DBMS_SQL.NUMBER_TABLE;
82: --****************************************************************************
83: Function GetPhantomLevelPosition(colDimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_level_code IN NUMBER) RETURN NUMBER IS
84: posicion NUMBER;
85: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
86: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
87: l_index1 NUMBER;
88: l_index2 NUMBER;
89: l_groups DBMS_SQL.NUMBER_TABLE;
90: BEGIN
169: -- Completes the configuration of drills for the phantom drills with '?'
170: --****************************************************************************
171: PROCEDURE Corrections IS
172:
173: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
174: colConfiguraciones DBMS_SQL.NUMBER_TABLE ;
175: Configuracion NUMBER;
176: colDimensions BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
177: l_stmt VARCHAR2(1000);
172:
173: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
174: colConfiguraciones DBMS_SQL.NUMBER_TABLE ;
175: Configuracion NUMBER;
176: colDimensions BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
177: l_stmt VARCHAR2(1000);
178: numPhantomLevels NUMBER;
179: arrPhantomLevels DBMS_SQL.NUMBER_TABLE;
180: i NUMBER;
192: FROM BSC_KPI_DATA_TABLES
193: WHERE INDICATOR = pIndicator
194: AND DIM_SET_ID = pDimSetID;
195: BEGIN
196: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count =0) THEN
197: return;
198: END IF;
199: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
200: LOOP
195: BEGIN
196: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count =0) THEN
197: return;
198: END IF;
199: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
200: LOOP
201: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
202: --Consider only new indicators or changed indicators
203: --BSC-MV Note: If there is change of summarization level
197: return;
198: END IF;
199: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
200: LOOP
201: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
202: --Consider only new indicators or changed indicators
203: --BSC-MV Note: If there is change of summarization level
204: --we need to process all the indicators.
205: If (Indicator.Action_Flag = 3) Or (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) Then
201: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
202: --Consider only new indicators or changed indicators
203: --BSC-MV Note: If there is change of summarization level
204: --we need to process all the indicators.
205: If (Indicator.Action_Flag = 3) Or (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) Then
206: --Get the list of configurations of the kpi
207: colConfiguraciones := BSC_MO_INDICATOR_PKG.GetConfigurationsForIndic(Indicator.Code);
208: IF (colConfiguraciones.count > 0) THEN
209: l_index2 := colConfiguraciones.first;
211: LOOP
212: EXIT WHEN colConfiguraciones.count = 0;
213: Configuracion := colConfiguraciones(l_index2);
214: numPhantomLevels := GetPhantomLevels(Indicator.Code, Configuracion, arrPhantomLevels);
215: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
216: bsc_mo_helper_pkg.writeTmp('# of phantom levels = '||numPhantomLevels);
217: END IF;
218: If numPhantomLevels > 0 Then
219: --Get the list of drills of the kpi in the given configuration
232: strDriles := substr(strDriles, 1, levelPos) || '?'||
233: substr(strDriles, levelPos - Length(strDriles) );
234: END LOOP;
235:
236: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
237: bsc_mo_helper_pkg.writeTmp('UPDATE BSC_KPI_DATA_TABLES SET LEVEL_COMB = '||
238: strDriles||' WHERE INDICATOR = '|| Indicator.Code||' AND DIM_SET_ID = '||
239: Configuracion||' AND PERIODICITY_ID = '|| l_periodicity_id||' AND LEVEL_COMB = '||strDrilesAnt, FND_LOG.LEVEL_STATEMENT);
240: END IF;
251: EXIT WHEN l_index2 = colConfiguraciones.last;
252: l_index2 := colConfiguraciones.next(l_index2);
253: END LOOP;
254: End If;
255: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
256: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
257: END LOOP;
258:
259: EXCEPTION WHEN OTHERS THEN
252: l_index2 := colConfiguraciones.next(l_index2);
253: END LOOP;
254: End If;
255: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
256: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
257: END LOOP;
258:
259: EXCEPTION WHEN OTHERS THEN
260: bsc_mo_helper_pkg.writeTmp('Exception in Corrections : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
290: AND TABLE_NAME IS NOT NULL;
291: l_temp NUMBER;
292: BEGIN
293: --Populate mapping tables for the indicator before create materialized views
294: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
295: bsc_mo_helper_pkg.writeTmp('Inside ReCreateMaterializedViewsIndic', FND_LOG.LEVEL_PROCEDURE);
296: END IF;
297: --l_stmt := 'begin BSC_INTEGRATION_APIS.Populate_Mapping_Tables('|| Indic ||'); end;';
298: --EXECUTE IMMEDIATE l_stmt;
304: LOOP
305: FETCH c1 INTO table_name, periodicity_id;
306: EXIT WHEN c1%NOTFOUND;
307: --Create the materialized view
308: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity_id);
309:
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
306: EXIT WHEN c1%NOTFOUND;
307: --Create the materialized view
308: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity_id);
309:
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
307: --Create the materialized view
308: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity_id);
309:
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
315:
308: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity_id);
309:
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
315:
316: start_year := fiscal_year - num_prev_years;
309:
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
315:
316: start_year := fiscal_year - num_prev_years;
317: end_year := start_year + num_years - 1;
310: cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarID;
311: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, cal_id);
312: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
313: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
314: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
315:
316: start_year := fiscal_year - num_prev_years;
317: end_year := start_year + num_years - 1;
318:
318:
319: --Create the union view
320: --We have to recreate the union view because the materializaed
321: --view is invalid until loader refresh it
322: uv_name := table_name || BSC_METADATA_OPTIMIZER_PKG.EDW_UNION_VIEW_EXT;
323: l_stmt := 'CREATE OR REPLACE VIEW '|| uv_name ||' AS SELECT * FROM '|| table_name;
324: BSC_MO_HELPER_PKG.do_ddl(l_stmt, ad_ddl.create_view, uv_name);
325:
326: END Loop;
324: BSC_MO_HELPER_PKG.do_ddl(l_stmt, ad_ddl.create_view, uv_name);
325:
326: END Loop;
327: CLOSE C1;
328: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
329: bsc_mo_helper_pkg.writeTmp('Completed ReCreateMaterializedViewsIndic', FND_LOG.LEVEL_PROCEDURE);
330: END IF;
331:
332: EXCEPTION WHEN OTHERS THEN
446:
447: strWhereInTables VARCHAR2(1000);
448: l_varchar2_list dbms_sql.varchar2_table;
449: BEGIN
450: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
451: BSC_MO_HELPER_PKG.writeTmp('Inside InsertOriginTables, arrTables.count = '||arrTables.count);
452: END IF;
453:
454: IF (arrTables.count = 0) THEN
451: BSC_MO_HELPER_PKG.writeTmp('Inside InsertOriginTables, arrTables.count = '||arrTables.count);
452: END IF;
453:
454: IF (arrTables.count = 0) THEN
455: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
456: BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, count = 0');
457: END IF;
458: return;
459: END IF;
482: END Loop;
483: Close CV;
484:
485: InsertOriginTables (arrTablesAux, arrOriginTables);
486: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
487: BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, arrOriginTables.count = '||arrOriginTables.count);
488: END IF;
489:
490:
525: AND RELATION_TYPE = 1;
526:
527: l_table VARCHAR2(100);
528: BEGIN
529: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
530: bsc_mo_helper_pkg.writeTmp('Inside InitarrTablesIndic');
531: END IF;
532: numTables := 0;
533: numIndicTables := 0;
553: arrIndicTables(numIndicTables) := l_table;
554: numIndicTables := numIndicTables + 1;
555: END Loop;
556: InsertOriginTables( arrIndicTables, arrTables);
557: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
558: bsc_mo_helper_pkg.writeTmp('Completed InitarrTablesIndic');
559: END IF;
560:
561: EXCEPTION WHEN OTHERS THEN
597: End;
598:
599:
600: PROCEDURE get_projection_and_gen_type(
601: Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable,
602: PeriodicityOrigin IN NUMBER,
603: p_projection OUT NOCOPY NUMBER,
604: p_gen_type OUT NOCOPY NUMBER) IS
605: Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
615: Else
616: --BSC-MV Note: generation_type will means:
617: -- 1: The table exists, calculate the table from source tables
618: -- -1: the table do not exists
619: If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
620: If isBasicTable(Tabla.Name) Then
621: p_gen_type := 1;
622: Else
623: --In BSC-MV implementation none of the summary tables exists.
639: p_projection := 1;
640: Else
641: --BSC-MV Note: In this architecture we only calculate projection
642: --on tables receiving directly targets (targets at different level)
643: If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
644: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
645: If (BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1) And
646: (Tabla.Periodicity <> PeriodicityOrigin) Then
647: p_projection := 1;
640: Else
641: --BSC-MV Note: In this architecture we only calculate projection
642: --on tables receiving directly targets (targets at different level)
643: If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
644: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
645: If (BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1) And
646: (Tabla.Periodicity <> PeriodicityOrigin) Then
647: p_projection := 1;
648: End If;
641: --BSC-MV Note: In this architecture we only calculate projection
642: --on tables receiving directly targets (targets at different level)
643: If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
644: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
645: If (BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1) And
646: (Tabla.Periodicity <> PeriodicityOrigin) Then
647: p_projection := 1;
648: End If;
649: End If;
649: End If;
650: End If;
651: End If;
652: --If any of the source tables (soft relations) is a target table then we need to proyect this table.
653: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
654: bsc_mo_helper_pkg.writeTmp('Checking Soft Relations');
655: END IF;
656: IF (Tabla.originTable1 IS NOT NULL) THEN
657: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
653: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
654: bsc_mo_helper_pkg.writeTmp('Checking Soft Relations');
655: END IF;
656: IF (Tabla.originTable1 IS NOT NULL) THEN
657: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
658: bsc_mo_helper_pkg.writeTmp('Tabla.originTable1 is not null');
659: END IF;
660: Tabla_originTable1 := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable1, ',');
661: l_index2 := Tabla_originTable1.first;
660: Tabla_originTable1 := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable1, ',');
661: l_index2 := Tabla_originTable1.first;
662: LOOP
663: l_table_origin := Tabla_originTable1(l_index2);
664: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
665: If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
666: p_projection := 1;
667: Exit;
668: End If;
661: l_index2 := Tabla_originTable1.first;
662: LOOP
663: l_table_origin := Tabla_originTable1(l_index2);
664: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
665: If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
666: p_projection := 1;
667: Exit;
668: End If;
669: EXIT WHEN l_index2 = Tabla_originTable1.last;
719: -- DESCRIPTION:
720: -- Configure metadata tables for Loader.
721: --****************************************************************************
722: PROCEDURE ConfigureActualization IS
723: Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable;
724: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
725: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
726: l_table_origin VARCHAR2(100);
727: l_stmt VARCHAR2(1000);
720: -- Configure metadata tables for Loader.
721: --****************************************************************************
722: PROCEDURE ConfigureActualization IS
723: Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable;
724: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
725: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
726: l_table_origin VARCHAR2(100);
727: l_stmt VARCHAR2(1000);
728: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
721: --****************************************************************************
722: PROCEDURE ConfigureActualization IS
723: Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable;
724: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
725: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
726: l_table_origin VARCHAR2(100);
727: l_stmt VARCHAR2(1000);
728: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
729: l_generation_type NUMBER := 0;
724: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
725: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
726: l_table_origin VARCHAR2(100);
727: l_stmt VARCHAR2(1000);
728: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
729: l_generation_type NUMBER := 0;
730: projection NUMBER := 0;
731: arrZeroCodeKeys DBMS_SQL.VARCHAR2_TABLE;
732: numZeroCodeKeys NUMBER := 0;
754: l_tempv varchar2(1000);
755:
756: Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
757: Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
758: Tabla_keyName BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
759: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
760:
761: l_dump DBMS_SQL.VARCHAR2_TABLE;
762: l_dumpcount NUMBER := 0;
755:
756: Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
757: Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
758: Tabla_keyName BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
759: Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
760:
761: l_dump DBMS_SQL.VARCHAR2_TABLE;
762: l_dumpcount NUMBER := 0;
763:
796: l_dep_tables dbms_sql.varchar2_table;
797: l_del_s_tables dbms_sql.varchar2_table;
798: BEGIN
799: --
800: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count =0) THEN
801: return;
802: END IF;
803: --
804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
800: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count =0) THEN
801: return;
802: END IF;
803: --
804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
805: BSC_MO_HELPER_PKG.writeTmp('Inside Loader Configuration ');
806: END IF;
807: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
808: -- Added 08/23/2005 for performance bug 4559323
803: --
804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
805: BSC_MO_HELPER_PKG.writeTmp('Inside Loader Configuration ');
806: END IF;
807: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
808: -- Added 08/23/2005 for performance bug 4559323
809: --
810: LOOP
811: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
807: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
808: -- Added 08/23/2005 for performance bug 4559323
809: --
810: LOOP
811: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
812: IF (Tabla.isProductionTable) THEN
813: null;
814: ELSE
815: l_tbl_delete(l_tbl_delete.count+1) := upper(Tabla.Name);
817: IF (tabla.name like 'BSC_S%') then
818: l_del_s_tables(l_del_s_tables.count+1):= upper(Tabla.Name);
819: END IF;
820: END IF;
821: exit when l_index1= BSC_METADATA_OPTIMIZER_PKG.gTables.last;
822: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
823: END LOOP;
824: --
825: -- Bug 4928585 - get the levels that have this as the source table
818: l_del_s_tables(l_del_s_tables.count+1):= upper(Tabla.Name);
819: END IF;
820: END IF;
821: exit when l_index1= BSC_METADATA_OPTIMIZER_PKG.gTables.last;
822: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
823: END LOOP;
824: --
825: -- Bug 4928585 - get the levels that have this as the source table
826: -- If dimensions are deleted, there could be other higher level tables that we need to delete
839: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);
840: FORALL i IN 1..l_tbl_delete.count
841: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);
842: l_tab_rels.delete;
843: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
844: LOOP
845: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
846: IF (Tabla.isProductionTable) THEN
847: goto skip_table;
841: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);
842: l_tab_rels.delete;
843: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
844: LOOP
845: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
846: IF (Tabla.isProductionTable) THEN
847: goto skip_table;
848: END IF;
849: Tabla_keyName.delete;
853: Tabla_originTable.delete;
854: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable, ',');
855: Tabla_originTable1.delete;
856: Tabla_originTable1 := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable1, ',');
857: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
858: BSC_MO_HELPER_PKG.writeTmp(' ');
859: BSC_MO_HELPER_PKG.writeTmp(' ');
860: BSC_MO_HELPER_PKG.writeTmp('Processing gTables('||l_index1||') '||bsc_mo_helper_pkg.get_time);
861: BSC_MO_HELPER_PKG.write_this(Tabla);
869: IF (Tabla_originTable.count >0 ) THEN
870: l_index2 := Tabla_originTable.first;
871: LOOP
872: l_table_origin := Tabla_originTable(l_index2);
873: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
874: IF (l_temp = -1 AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
875: PeriodicityOrigin := Tabla.periodicity;
876: ELSE
877: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
870: l_index2 := Tabla_originTable.first;
871: LOOP
872: l_table_origin := Tabla_originTable(l_index2);
873: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
874: IF (l_temp = -1 AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
875: PeriodicityOrigin := Tabla.periodicity;
876: ELSE
877: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
878: END IF;
873: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
874: IF (l_temp = -1 AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
875: PeriodicityOrigin := Tabla.periodicity;
876: ELSE
877: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
878: END IF;
879: OriTableName := l_table_origin;
880: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
881: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS1:'||Tabla.Name||', '||l_table_origin||', 0');
876: ELSE
877: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
878: END IF;
879: OriTableName := l_table_origin;
880: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
881: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS1:'||Tabla.Name||', '||l_table_origin||', 0');
882: END IF;
883: l_rels_record.table_name := upper(Tabla.Name);
884: l_rels_record.source_table_name := upper(l_table_origin);
888: l_index2 := Tabla_originTable.next(l_index2);
889: END LOOP;
890: END IF;
891: --Soft relations
892: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
893: bsc_mo_helper_pkg.writeTmp('Checking Soft Relations1');
894: bsc_mo_helper_pkg.writeTmp('Tabla_originTable1 = '||Tabla.originTable1||', Tabla_originTable1.count = '||Tabla_originTable1.count);
895: END IF;
896: IF (Tabla_originTable1.count > 0 ) THEN
896: IF (Tabla_originTable1.count > 0 ) THEN
897: l_index2 := Tabla_originTable1.first;
898: LOOP
899: l_table_origin := Tabla_originTable1(l_index2);
900: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
901: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS2:'||Tabla.Name||','||l_table_origin||',1');
902: END IF;
903: l_rels_record.table_name := upper(Tabla.Name);
904: l_rels_record.source_table_name := upper(l_table_origin);
907: EXIT WHEN l_index2 = Tabla_originTable1.last;
908: l_index2 := Tabla_originTable1.next(l_index2);
909: END LOOP;
910: END IF;
911: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
912: bsc_mo_helper_pkg.writeTmp('Done checking Soft Relations1');
913: END IF;
914: End If; -- IF TABLA.type = 1
915:
916: --BSC_DB_TABLES
917: get_projection_and_gen_type(Tabla, PeriodicityOrigin, projection, l_generation_type);
918:
919: --EDW Note: Each calendar has his own fiscal year, and range of years
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
917: get_projection_and_gen_type(Tabla, PeriodicityOrigin, projection, l_generation_type);
918:
919: --EDW Note: Each calendar has his own fiscal year, and range of years
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
918:
919: --EDW Note: Each calendar has his own fiscal year, and range of years
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
919: --EDW Note: Each calendar has his own fiscal year, and range of years
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
928: --Annual periodicity
921: Calendar_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).CalendarId;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
928: --Annual periodicity
929: Periodo_Act := fiscal_year;
922: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, calendar_id);
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
928: --Annual periodicity
929: Periodo_Act := fiscal_year;
930: Else
923: num_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).NumOfYears;
924: num_prev_years := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).PreviousYears;
925: fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp).CurrFiscalYear;
926: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);
927: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
928: --Annual periodicity
929: Periodo_Act := fiscal_year;
930: Else
931: --No Annual periodicity
934:
935: --BSC-MV Note: In the upgrade process (sum level change from NULL to NOTNULL)
936: --we canot reset the current period, specially in the base and input table.
937: --We need to use the current period of the table
938: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 Then
939: If Tabla.currentPeriod <> 0 Then
940: Periodo_Act := Tabla.currentPeriod;
941: End If;
942: End If;
952: Target_Flag := 1;
953: Else
954: Target_Flag := 0;
955: End If;
956: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
957: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES:'||
958: Tabla.Name||', type='|| Tabla.Type||', periodicity='||Tabla.Periodicity||', gen_type='||
959: l_generation_type ||', projection = '|| projection||', num_years'|| num_years||', num_prev_years= '||
960: num_prev_years||','||Periodo_Act||','|| SubPeriodo_Act||','|| EDW_Flag||',' ||Target_Flag||' '||bsc_mo_helper_pkg.get_time);
1001: || l_measure.AvgLCounterColumn || ')');
1002: Else
1003: ZeroCodeOrigin := l_measure.aggFunction|| '('|| l_measure.fieldName || ')';
1004: End If;
1005: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1006: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS1:'||
1007: Tabla.name||','||arrZeroCodeKeys(i)||','||i||','|| l_measure.fieldName||','||ZeroCodeOrigin||' '||bsc_mo_helper_pkg.get_time);
1008: END IF;
1009: l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1023: IF (Tabla_originTable1.count>0) THEN
1024: l_index2 := Tabla_originTable1.first;
1025: LOOP
1026: l_table_origin := Tabla_originTable1(l_index2);
1027: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1028: If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
1029: IF ( Tabla_Data.count>0) THEN
1030: l_index3 := Tabla_Data.first;
1031: LOOP
1024: l_index2 := Tabla_originTable1.first;
1025: LOOP
1026: l_table_origin := Tabla_originTable1(l_index2);
1027: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1028: If BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).IsTargetTable Then
1029: IF ( Tabla_Data.count>0) THEN
1030: l_index3 := Tabla_Data.first;
1031: LOOP
1032: l_measure := Tabla_Data(l_index3);
1029: IF ( Tabla_Data.count>0) THEN
1030: l_index3 := Tabla_Data.first;
1031: LOOP
1032: l_measure := Tabla_Data(l_index3);
1033: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1034: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS2:'||
1035: Tabla.Name||',5,'|| l_table_origin||','|| l_measure.fieldName);
1036: END IF;
1037: l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1057: IF (Tabla_keyName.count>0) THEN
1058: l_index2 := Tabla_keyName.first;
1059: LOOP
1060: l_key := Tabla_keyName(l_index2);
1061: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1062: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: table_name='||Tabla.Name||
1063: ', column_type=P, column_name = '||l_key.keyName||', source_column='||l_key.Origin||' '||bsc_mo_helper_pkg.get_time);
1064: END IF;
1065: BEGIN
1080: l_index2 := Tabla_Data.first;
1081: LOOP
1082: l_measure := Tabla_Data(l_index2);
1083: If projection = 1 Then
1084: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1085: l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1086: Else
1087: l_prj_method := 0;
1088: End If;
1081: LOOP
1082: l_measure := Tabla_Data(l_index2);
1083: If projection = 1 Then
1084: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1085: l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1086: Else
1087: l_prj_method := 0;
1088: End If;
1089: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1085: l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1086: Else
1087: l_prj_method := 0;
1088: End If;
1089: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1090: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: Table_name = '||Tabla.Name||', column_type = A, Column Name= '||
1091: l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin||' '||bsc_mo_helper_pkg.get_time );
1092: END IF;
1093: l_db_tables_cols_record.TABLE_NAME := upper(Tabla.Name);
1099: l_tab_db_tables_cols(l_tab_db_tables_cols.count+1) := l_db_tables_cols_record;
1100: EXIT WHEN l_index2 = Tabla_Data.last;
1101: l_index2 := Tabla_Data.next(l_index2);
1102: END LOOP;
1103: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1104: BSC_MO_HELPER_PKG.writeTmp('Data Cols insertion completed for Table='||Tabla.Name);
1105: END IF;
1106: END IF;
1107: --Special cases: This is a table of a Balance or PnL indicator
1105: END IF;
1106: END IF;
1107: --Special cases: This is a table of a Balance or PnL indicator
1108: --BSC-MV Note: Profit calculation will be done in the base tables at sub-account level
1109: If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1110: --current architecture
1111: If Tabla.Indicator <> 0 Then
1112: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1113: BSC_MO_HELPER_PKG.writeTmp('Indicator<>0');
1108: --BSC-MV Note: Profit calculation will be done in the base tables at sub-account level
1109: If Not BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1110: --current architecture
1111: If Tabla.Indicator <> 0 Then
1112: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1113: BSC_MO_HELPER_PKG.writeTmp('Indicator<>0');
1114: END IF;
1115: If BSC_MO_INDICATOR_PKG.IsIndicatorPnL(Tabla.Indicator, true ) Then
1116: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1112: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1113: BSC_MO_HELPER_PKG.writeTmp('Indicator<>0');
1114: END IF;
1115: If BSC_MO_INDICATOR_PKG.IsIndicatorPnL(Tabla.Indicator, true ) Then
1116: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1117: BSC_MO_HELPER_PKG.writeTmp('Indicator is PnL');
1118: END IF;
1119: --This is a table of a PnL indicator
1120: IF (tabla_keyName.count > 0 ) THEN
1124: --This is the table for the first drill (account drill).
1125: --We need to calculate the profit
1126: --BSC_DB_CALCULATIONS
1127: l_db_calc_1_delete(l_db_calc_1_delete.count+1) := UPPER(Tabla.Name);
1128: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1129: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS3:'||Tabla.Name||',1,'|| Dril1||' '||bsc_mo_helper_pkg.get_time);
1130: END IF;
1131: l_db_calculations_record.TABLE_NAME := upper(Tabla.name);
1132: l_db_calculations_record.CALCULATION_TYPE := 1;
1152: BSC_MO_HELPER_PKG.write_to_stack('OriginTable='||Tabla.originTable);
1153: OriTableName := null;
1154: IF (Tabla_originTable.count >0 ) THEN
1155: l_table_origin := Tabla_originTable(0);
1156: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1157: BSC_MO_HELPER_PKG.write_to_stack('l_temp='||l_temp);
1158: IF (l_temp = -1 OR BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
1159: PeriodicityOrigin := Tabla.periodicity;
1160: ELSE
1154: IF (Tabla_originTable.count >0 ) THEN
1155: l_table_origin := Tabla_originTable(0);
1156: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, l_table_origin);
1157: BSC_MO_HELPER_PKG.write_to_stack('l_temp='||l_temp);
1158: IF (l_temp = -1 OR BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
1159: PeriodicityOrigin := Tabla.periodicity;
1160: ELSE
1161: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
1162: END IF;
1157: BSC_MO_HELPER_PKG.write_to_stack('l_temp='||l_temp);
1158: IF (l_temp = -1 OR BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
1159: PeriodicityOrigin := Tabla.periodicity;
1160: ELSE
1161: PeriodicityOrigin := BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp).Periodicity;
1162: END IF;
1163: BSC_MO_HELPER_PKG.write_to_stack('PeriodicityOrigin='||PeriodicityOrigin);
1164: END IF;
1165: END IF;
1174: BSC_MO_HELPER_PKG.writeTmp('Considering '||Tabla.data(i).fieldName, FND_LOG.LEVEL_STATEMENT, false);
1175: BSC_MO_HELPER_PKG.write_this(Tabla.data(i));
1176: IF Tabla.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
1177: If projection = 1 Then
1178: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1179: BSC_MO_HELPER_PKG.writeTmp('gLov, l_temp='||l_temp, FND_LOG.LEVEL_STATEMENT, false);
1180: l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1181: Else
1182: l_prj_method := 0;
1176: IF Tabla.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
1177: If projection = 1 Then
1178: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1179: BSC_MO_HELPER_PKG.writeTmp('gLov, l_temp='||l_temp, FND_LOG.LEVEL_STATEMENT, false);
1180: l_prj_method := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
1181: Else
1182: l_prj_method := 0;
1183: End If;
1184: BSC_MO_HELPER_PKG.writeTmp('l_prj_method='||l_prj_method, FND_LOG.LEVEL_STATEMENT, false);
1182: l_prj_method := 0;
1183: End If;
1184: BSC_MO_HELPER_PKG.writeTmp('l_prj_method='||l_prj_method, FND_LOG.LEVEL_STATEMENT, false);
1185: FOR j IN Tabla_originTable.first..Tabla_originTable.last LOOP
1186: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1187: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS5: Table_name = '||Tabla_originTable(j)||', column_type = A, Column Name= '||
1188: l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin );
1189: END IF;
1190: l_db_cols_1_delete_table_name(l_db_cols_1_delete_table_name.count+1) := upper(Tabla_originTable(j));
1201: END IF;
1202: END LOOP;
1203: END IF;
1204: END IF;
1205: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1206: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1207: END LOOP;
1208: BSC_MO_HELPER_PKG.writeTmp('Checkpoint 11 '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
1209:
1202: END LOOP;
1203: END IF;
1204: END IF;
1205: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1206: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1207: END LOOP;
1208: BSC_MO_HELPER_PKG.writeTmp('Checkpoint 11 '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, false);
1209:
1210: --BULK DELETES
1277: END LOOP;
1278: END;
1279:
1280:
1281: If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1282: --BSC-MV Note: Now that all the Loader metadata is configured, We need to configure
1283: --the periodicity calculation in the base tables. It is going to call a Loader API
1284: --to do it. The same API is re-used in upgrade.
1285: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1281: If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1282: --BSC-MV Note: Now that all the Loader metadata is configured, We need to configure
1283: --the periodicity calculation in the base tables. It is going to call a Loader API
1284: --to do it. The same API is re-used in upgrade.
1285: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1286: LOOP
1287: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
1288: -- bug 4581847, should call this for production tables also
1289: If isBasicTable(Tabla.Name) Then
1283: --the periodicity calculation in the base tables. It is going to call a Loader API
1284: --to do it. The same API is re-used in upgrade.
1285: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1286: LOOP
1287: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
1288: -- bug 4581847, should call this for production tables also
1289: If isBasicTable(Tabla.Name) Then
1290: BSC_UPDATE.Configure_Periodicity_Calc_VB(tabla.name);
1291: BSC_MO_HELPER_PKG.CHeckError('BSC_UPDATE.Configure_Periodicity_Calc_VB');
1289: If isBasicTable(Tabla.Name) Then
1290: BSC_UPDATE.Configure_Periodicity_Calc_VB(tabla.name);
1291: BSC_MO_HELPER_PKG.CHeckError('BSC_UPDATE.Configure_Periodicity_Calc_VB');
1292: End If;
1293: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1294: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1295: END LOOP;
1296: BSC_MO_HELPER_PKG.writeTmp('Going to configure Profit Calculations '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
1297:
1290: BSC_UPDATE.Configure_Periodicity_Calc_VB(tabla.name);
1291: BSC_MO_HELPER_PKG.CHeckError('BSC_UPDATE.Configure_Periodicity_Calc_VB');
1292: End If;
1293: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1294: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
1295: END LOOP;
1296: BSC_MO_HELPER_PKG.writeTmp('Going to configure Profit Calculations '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
1297:
1298: --BSC-MV Note: Now that all the Loader metadata is configured, We need to configure
1301: BSC_UPDATE.Configure_Profit_Calc_VB;
1302: BSC_MO_HELPER_PKG.CheckError('BSC_UPDATE.Configure_Profit_Calc_VB');
1303: End If;
1304:
1305: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1306: BSC_MO_HELPER_PKG.writeTmp('Completed Loader Configuration '||bsc_mo_helper_pkg.get_time);
1307: END IF;
1308:
1309: EXCEPTION WHEN OTHERS THEN
1325: arrTables DBMS_SQL.VARCHAR2_TABLE;
1326: numTables NUMBER;
1327: arrIndicTables DBMS_SQL.VARCHAR2_TABLE;
1328: numIndicTables NUMBER;
1329: colDatos BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1330: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1331: i NUMBER;
1332: l_table_origin VARCHAR2(100);
1333: l_table_type NUMBER;
1326: numTables NUMBER;
1327: arrIndicTables DBMS_SQL.VARCHAR2_TABLE;
1328: numIndicTables NUMBER;
1329: colDatos BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1330: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1331: i NUMBER;
1332: l_table_origin VARCHAR2(100);
1333: l_table_type NUMBER;
1334: l_stmt VARCHAR2(1000);
1408: If (l_table_type = 3) Or (l_table_type = 4) Then
1409: --BSC-MV Note: In this Architecture there COULD be a projection
1410: --table created for target at different levels. In this case we
1411: --need to add this new column to the projection table.
1412: If BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV Then
1413: l_optimizationMode := bsc_mo_helper_pkg.getKPIPropertyValue(Indic, 'DB_TRANSFORM', 1);
1414: IF (l_optimizationMode = 2) THEN -- targets at different levels, PT tables shd exist
1415: l_table_name := BSC_MO_INDICATOR_PKG.GetProjectionTableName(arrTables(i));
1416: ELSE
1422: bsc_mo_helper_pkg.addStack (l_stack, ' Check1 : l_table_name = '||l_table_name);
1423: --This is table used directly by the indicator
1424: --We need to make sure that the internal column exists
1425: If substr(l_measure.fieldName, 1, 5) = 'BSCIC' Then
1426: IF (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV =false) OR (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV AND l_optimizationMode=2) THEN
1427: If Not BSC_MO_HELPER_PKG.table_column_exists(l_table_name, l_measure.fieldName) Then
1428: --Add the internal column
1429: ddl_sql := 'ALTER TABLE ' || l_table_name ||' ADD ' || l_measure.fieldName ||' NUMBER ';
1430: begin
1436: end;
1437: End If;
1438: END IF;
1439: -- Bug 4466627
1440: IF (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV) THEN
1441: SELECT COUNT(1) INTO l_count
1442: FROM BSC_DB_TABLES_COLS
1443: WHERE table_name=l_table_name AND column_name=l_measure.fieldName;
1444: BSC_MO_HELPER_PKG.writeTmp('MV arch, l_count= '||l_count||' for '||l_table_name ||' field='||l_measure.fieldName);
1462: bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt3 ');
1463: If newColumnFlag Then
1464: --This is a internal column that was added to the indicator table
1465: --If the table does not calculate zero code then no record is inserted
1466: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1467: BSC_MO_HELPER_PKG.writeTmp('4 INSERT DB_CALCULATIONS:');
1468: END IF;
1469: l_stmt := 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2 '||
1470: ', PARAMETER3, PARAMETER4, PARAMETER5) '||
1479: l_measure.fieldName, parameter4, ZeroCodeOrigin
1480: FROM BSC_DB_CALCULATIONS
1481: WHERE TABLE_NAME = UPPER(arrTables(i)) AND CALCULATION_TYPE = 4;
1482: Else
1483: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1484: BSC_MO_HELPER_PKG.writeTmp('4 UPDATE DB_CALCULATIONS:');
1485: END IF;
1486: --If the table does not calculate zero code then no record is updated
1487: l_stmt := 'UPDATE BSC_DB_CALCULATIONS SET PARAMETER5 = :1 WHERE '||
1498: --BSC_DB_CALCULATIONS (Merge targets)
1499: --No changes for current columns
1500: If newColumnFlag Then
1501: --This is a internal column that was added to the indicator table
1502: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1503: BSC_MO_HELPER_PKG.writeTmp('5 INSERT DB_CALCULATIONS:');
1504: END IF;
1505: l_stmt:= 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2) '||
1506: ' SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, :2'||
1550: bsc_mo_helper_pkg.addStack (l_stack, ' Chkpt5.1c, l_source = '||l_source);
1551: End IF;
1552: If newColumnFlag Then
1553: --This is a internal column that was added to the indicator table
1554: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1555: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS newcol: table_name='||
1556: arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source_column='||l_source);
1557: END IF;
1558: INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)
1557: END IF;
1558: INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)
1559: VALUES(upper(arrTables(i)), 'A', l_measure.fieldName, l_measure.source, 0, l_source);
1560: Else
1561: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562: BSC_MO_HELPER_PKG.writeTmp('UPDATE DB_TABLES_COLS newcol: table_name='||
1563: arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source='||l_measure.source||', source_column='||l_source);
1564: END IF;
1565: UPDATE BSC_DB_TABLES_COLS
1570: AND COLUMN_TYPE = 'A';
1571: End If;
1572: --BSC_DB_TABLES_COLS (Projection method)
1573: If CalcProjectionTable(arrTables(i)) Then
1574: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1575: UPDATE BSC_DB_TABLES_COLS
1576: SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod
1577: WHERE TABLE_NAME = UPPER(arrTables(i))
1578: AND COLUMN_NAME = l_measure.fieldName
1572: --BSC_DB_TABLES_COLS (Projection method)
1573: If CalcProjectionTable(arrTables(i)) Then
1574: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1575: UPDATE BSC_DB_TABLES_COLS
1576: SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod
1577: WHERE TABLE_NAME = UPPER(arrTables(i))
1578: AND COLUMN_NAME = l_measure.fieldName
1579: AND SOURCE = l_measure.source
1580: AND COLUMN_TYPE = 'A';
1585: End If;
1586: END LOOP;
1587: --Fix bug#3350103 If the user change the rollup method we need to update
1588: --the sql statements configured in BSC_KPI_DATA_TABLES for zero codes
1589: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1590:
1591: OPEN cRollups(indic, Configuracion);
1592: bsc_mo_helper_pkg.addStack (l_stack, ' Chk 8');
1593: zeroCodeDataColumns := null;