[Home] [Help]
3: g_newline VARCHAR2(10):= '
4: ';
5: g_error VARCHAR2(1000);
6: gRecDims VARCHAR2(1000) := null;
7: g_current_indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
8: g_current_dimset number;
9:
10:
11: TYPE cNumMeasuresMap IS RECORD(
26: PTName := substr(TableName, 1, pos) || 'PT';
27: Else
28: PTName := TableName || '_PT';
29: End If;
30: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
31: bsc_mo_helper_pkg.writeTmp('Done with GetProjectionTable, returning '||PTName);
32: END IF;
33:
34: return PTName;
53:
54: open cExp;
55: fetch cExp into l_res;
56: close cExp;
57: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
58: bsc_mo_helper_pkg.writeTmp( 'Done with GetFreeDivZeroExpression, returning '||l_res);
59: END IF;
60:
61: return l_res;
71: -- DESCRIPTION:
72: -- This function is used only in the BSC-MV Architecture.
73: --****************************************************************************
74:
75: Function getTableLevel(TableName IN VARCHAR2, colTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable) RETURN NUMBER IS
76: l_level NUMBER;
77: sourceTable VARCHAR2(100);
78: l_index NUMBER;
79: BEGIN
105: -- Key: dimension key
106: --
107: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
108: --***************************************************************************
109: Function FindDimensionGroupIndexForKey(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_Key IN VARCHAR2) return NUMBER IS
110: iDimensionLevels NUMBER;
111: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
112: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
113: i NUMBER;
107: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
108: --***************************************************************************
109: Function FindDimensionGroupIndexForKey(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_Key IN VARCHAR2) return NUMBER IS
110: iDimensionLevels NUMBER;
111: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
112: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
113: i NUMBER;
114:
115: l_groups DBMS_SQL.NUMBER_TABLE;
108: --***************************************************************************
109: Function FindDimensionGroupIndexForKey(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, p_Key IN VARCHAR2) return NUMBER IS
110: iDimensionLevels NUMBER;
111: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
112: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
113: i NUMBER;
114:
115: l_groups DBMS_SQL.NUMBER_TABLE;
116: l_group_id NUMBER;
148: raise;
149: End;
150:
151: FUNCTION get_n_parents(p_s_table IN VARCHAR2,
152: p_s_table_list BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
153: p_level_num IN NUMBER)
154: RETURN DBMS_SQL.VARCHAR2_TABLE IS
155: l_level NUMBER;
156: sourceTable VARCHAR2(100);
179: END;
180:
181: -- P1 4148992 for query configured in bsc_kpi_data_tables
182: --
183: PROCEDURE find_join_betweens_levels(p_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
184: p_zmv_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
185: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
186: p_join_level OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
187: p_join_parent OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
180:
181: -- P1 4148992 for query configured in bsc_kpi_data_tables
182: --
183: PROCEDURE find_join_betweens_levels(p_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
184: p_zmv_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
185: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
186: p_join_level OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
187: p_join_parent OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
188: p_join_parent_fk OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE)
181: -- P1 4148992 for query configured in bsc_kpi_data_tables
182: --
183: PROCEDURE find_join_betweens_levels(p_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
184: p_zmv_key IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
185: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
186: p_join_level OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
187: p_join_parent OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
188: p_join_parent_fk OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE)
189: IS
207: l_parent VARCHAR2(100);
208: l_dim_fk VARCHAR2(100);
209:
210: l_dim_group_index number ;
211: l_dimension_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
212: l_index NUMBER;
213: l_level_table_high VARCHAR2(100);
214: l_level_table_low VARCHAR2(100);
215: BEGIN
236: bsc_mo_helper_pkg.TerminateWithMsg('Exception in find_join_betweens_levels:'||sqlerrm);
237: raise;
238: END;
239:
240: PROCEDURE get_join_info(p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
241: p_zmv_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
242: p_zero_code_states IN DBMS_SQL.NUMBER_TABLE,
243: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
244: p_join_dimensions OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
237: raise;
238: END;
239:
240: PROCEDURE get_join_info(p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
241: p_zmv_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
242: p_zero_code_states IN DBMS_SQL.NUMBER_TABLE,
243: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
244: p_join_dimensions OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
245: p_join_parents OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
239:
240: PROCEDURE get_join_info(p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
241: p_zmv_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
242: p_zero_code_states IN DBMS_SQL.NUMBER_TABLE,
243: p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
244: p_join_dimensions OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
245: p_join_parents OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
246: p_join_dimension_fk OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
247: p_zmv_fk OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE) IS
298: -- is on level 3, we redirect the query to the lower level ZMV to use
299: -- the aggregated results from the lower level. We will need to join to
300: -- dimensions levels.
301: --****************************************************************************
302: FUNCTION optimize_zmv_clause(p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
303: p_s_table_list IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
304: p_s_table IN VARCHAR2,
305: p_table_level IN NUMBER,
306: p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
299: -- the aggregated results from the lower level. We will need to join to
300: -- dimensions levels.
301: --****************************************************************************
302: FUNCTION optimize_zmv_clause(p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
303: p_s_table_list IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
304: p_s_table IN VARCHAR2,
305: p_table_level IN NUMBER,
306: p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
307: p_zero_code_states IN DBMS_SQL.NUMBER_TABLE,
302: FUNCTION optimize_zmv_clause(p_dimensions IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
303: p_s_table_list IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
304: p_s_table IN VARCHAR2,
305: p_table_level IN NUMBER,
306: p_keys IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
307: p_zero_code_states IN DBMS_SQL.NUMBER_TABLE,
308: p_system_levels IN NUMBER,
309: p_sql_stmt IN OUT NOCOPY VARCHAR2)
310: RETURN BOOLEAN IS
309: p_sql_stmt IN OUT NOCOPY VARCHAR2)
310: RETURN BOOLEAN IS
311: l_nlevel_parents DBMS_SQL.VARCHAR2_TABLE;
312: l_highest_table_with_zmv VARCHAR2(400);
313: l_zmv_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
314: b_zmv_exists boolean := false;
315: l_zmv VARCHAR2(400);
316:
317: l_dim_group_index NUMBER;
315: l_zmv VARCHAR2(400);
316:
317: l_dim_group_index NUMBER;
318: l_index NUMBER;
319: l_dimension_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
320:
321: CURSOR cParentFK(p_lower_dim_table IN VARCHAR2, p_higher_dim_table IN VARCHAR2) IS
322: SELECT RELATION_COL
323: FROM BSC_SYS_DIM_LEVEL_RELS RELS, BSC_SYS_DIM_LEVELS_B LVLA, BSC_SYS_DIM_LEVELS_B LVLB
473: -- and the sql or mv to be used by iviewer
474: --
475: --****************************************************************************
476: Function GetColConfigKpiMV(
477: STable BSC_METADATA_OPTIMIZER_PKG.clsBasicTable,
478: TableLevel NUMBER,
479: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
475: --****************************************************************************
476: Function GetColConfigKpiMV(
477: STable BSC_METADATA_OPTIMIZER_PKG.clsBasicTable,
478: TableLevel NUMBER,
479: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
482: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV IS
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
476: Function GetColConfigKpiMV(
477: STable BSC_METADATA_OPTIMIZER_PKG.clsBasicTable,
478: TableLevel NUMBER,
479: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
482: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV IS
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
478: TableLevel NUMBER,
479: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
482: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV IS
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
485: MVName VARCHAR2(100);
486: zmvName VARCHAR2(100);
479: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
482: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV IS
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
485: MVName VARCHAR2(100);
486: zmvName VARCHAR2(100);
487: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
480: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable
481: )
482: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV IS
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
485: MVName VARCHAR2(100);
486: zmvName VARCHAR2(100);
487: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
488: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
483: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
485: MVName VARCHAR2(100);
486: zmvName VARCHAR2(100);
487: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
488: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
489: arrCombinationsB DBMS_SQL.VARCHAR2_TABLE;
490: numCombinationsB NUMBER;
491: arrCombinationsA DBMS_SQL.VARCHAR2_TABLE;
484: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
485: MVName VARCHAR2(100);
486: zmvName VARCHAR2(100);
487: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
488: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
489: arrCombinationsB DBMS_SQL.VARCHAR2_TABLE;
490: numCombinationsB NUMBER;
491: arrCombinationsA DBMS_SQL.VARCHAR2_TABLE;
492: numCombinationsA NUMBER;
496: newCombination VARCHAR2(100);
497: sql_stmt VARCHAR2(4000);
498: group_by VARCHAR2(1000);
499: state VARCHAR2(100);
500: New_clsConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
501: STable_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
502: STable_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
503: l_groups DBMS_SQL.NUMBER_TABLE;
504: bForcedSQL boolean := false;
497: sql_stmt VARCHAR2(4000);
498: group_by VARCHAR2(1000);
499: state VARCHAR2(100);
500: New_clsConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
501: STable_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
502: STable_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
503: l_groups DBMS_SQL.NUMBER_TABLE;
504: bForcedSQL boolean := false;
505:
498: group_by VARCHAR2(1000);
499: state VARCHAR2(100);
500: New_clsConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
501: STable_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
502: STable_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
503: l_groups DBMS_SQL.NUMBER_TABLE;
504: bForcedSQL boolean := false;
505:
506: -- added for bug 3944813
522: bForcedSQL := true;
523: bsc_mo_helper_pkg.writeTmp('Going to convert MVs to SQL because of DB limitation... # of levels = '||l_groups.count||' while max allowed = '||BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS, FND_LOG.LEVEL_statement, true);
524: --l_stack := l_stack || 'Going to convert MVs to SQL because of DB limitation... # of levels = '||l_groups.count||' while max allowed = '||BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS||l_newline;
525: END IF;
526: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
527: bsc_mo_helper_pkg.writeTmp(' ');
528: bsc_mo_helper_pkg.writeTmp('In GetColConfigKpiMV , TableLevel = '||TableLevel||', LevelConfig ='||STable.LevelConfig);
529: END IF;
530: bsc_mo_helper_pkg.write_this(STable);
614: configKpiMV.MVName := MVName;
615: configKpiMV.SqlStmt := null;
616: Else
617: l_stack := l_stack || 'Total comb is true'||l_newline;
618: -- bug 3835059, autogenerate sqls instead of MVs if # of levels > BSC_METADATA_OPTIMIZER_PKG.MAX_ALLOWED_LEVELS
619: If (NOT bForcedSQL) AND TableLevel <= to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level) Then
620: --There will be a MV for the zero code
621: configKpiMV.DataSource := 'MV';
622: configKpiMV.MVName := STable.name || '_ZMV';
615: configKpiMV.SqlStmt := null;
616: Else
617: l_stack := l_stack || 'Total comb is true'||l_newline;
618: -- bug 3835059, autogenerate sqls instead of MVs if # of levels > BSC_METADATA_OPTIMIZER_PKG.MAX_ALLOWED_LEVELS
619: If (NOT bForcedSQL) AND TableLevel <= to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level) Then
620: --There will be a MV for the zero code
621: configKpiMV.DataSource := 'MV';
622: configKpiMV.MVName := STable.name || '_ZMV';
623: configKpiMV.SqlStmt := null;
644: -- BEGIN added for bug 3944813
645: bsc_mo_helper_pkg.writeTmp('Calling Optimize ZMV clause', FND_LOG.level_Statement, false);
646: l_stack := l_stack || 'Calling Optimize ZMV clause'||l_newline;
647: IF (bForcedSQL =false) -- bug 4139837
648: AND TableLevel > to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level)
649: AND to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level) > 0
650: AND g_current_indicator.Impl_Type = 1
651: AND (optimize_zmv_clause(p_dimension_families,
652: colSummaryTables,
645: bsc_mo_helper_pkg.writeTmp('Calling Optimize ZMV clause', FND_LOG.level_Statement, false);
646: l_stack := l_stack || 'Calling Optimize ZMV clause'||l_newline;
647: IF (bForcedSQL =false) -- bug 4139837
648: AND TableLevel > to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level)
649: AND to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level) > 0
650: AND g_current_indicator.Impl_Type = 1
651: AND (optimize_zmv_clause(p_dimension_families,
652: colSummaryTables,
653: STable.Name,
653: STable.Name,
654: TableLevel,
655: STable_Keys,
656: l_zero_code_states,
657: to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level),
658: sql_stmt)=true) THEN
659: bsc_mo_helper_pkg.writeTmp('Optimized sql_stmt='||sql_stmt, FND_LOG.level_Statement, false);
660:
661: ELSE
689: raise;
690: End ;
691:
692:
693: PROCEDURE clearDrill(pDrill IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsLevels) IS
694: l_new BSC_METADATA_OPTIMIZER_PKG.clsLevels;
695: BEGIn
696: pDrill := l_new;
697: END;
690: End ;
691:
692:
693: PROCEDURE clearDrill(pDrill IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsLevels) IS
694: l_new BSC_METADATA_OPTIMIZER_PKG.clsLevels;
695: BEGIn
696: pDrill := l_new;
697: END;
698:
711:
712: Function fieldExistsInLov(measure IN VARCHAR2,
713: -- BSC Autogen
714: p_source IN VARCHAR2) RETURN BOOLEAN IS
715: measure_field BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV;
716: i NUMBER ;
717:
718: BEGIN
719: IF (BSC_METADATA_OPTIMIZER_PKG.gLOV.count = 0) THEN
715: measure_field BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV;
716: i NUMBER ;
717:
718: BEGIN
719: IF (BSC_METADATA_OPTIMIZER_PKG.gLOV.count = 0) THEN
720: return false;
721: END IF;
722: i := BSC_METADATA_OPTIMIZER_PKG.gLov.first;
723: LOOP
718: BEGIN
719: IF (BSC_METADATA_OPTIMIZER_PKG.gLOV.count = 0) THEN
720: return false;
721: END IF;
722: i := BSC_METADATA_OPTIMIZER_PKG.gLov.first;
723: LOOP
724: measure_field := BSC_METADATA_OPTIMIZER_PKG.gLOV(i);
725: IF (upper(measure_field.fieldName) = upper(measure)
726: -- BSC Autogen
720: return false;
721: END IF;
722: i := BSC_METADATA_OPTIMIZER_PKG.gLov.first;
723: LOOP
724: measure_field := BSC_METADATA_OPTIMIZER_PKG.gLOV(i);
725: IF (upper(measure_field.fieldName) = upper(measure)
726: -- BSC Autogen
727: AND upper(measure_field.source) = upper(p_source)) THEN
728: return True;
726: -- BSC Autogen
727: AND upper(measure_field.source) = upper(p_source)) THEN
728: return True;
729: END IF;
730: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gLov.last;
731: i := BSC_METADATA_OPTIMIZER_PKG.gLOV.next(i);
732: END LOOP;
733: return false;
734: EXCEPTION WHEN OTHERS THEN
727: AND upper(measure_field.source) = upper(p_source)) THEN
728: return True;
729: END IF;
730: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gLov.last;
731: i := BSC_METADATA_OPTIMIZER_PKG.gLOV.next(i);
732: END LOOP;
733: return false;
734: EXCEPTION WHEN OTHERS THEN
735: g_error := sqlerrm;
755: l_parent_name DBMS_SQL.VARCHAR2_TABLE;
756: l_dummy NUMBER;
757:
758: BEGIN
759: i := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, tablename);
760:
761:
762: IF (BSC_METADATA_OPTIMIZER_PKG.gMastertable(i).parent_name IS NULL ) THEN
763: return -1;
758: BEGIN
759: i := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, tablename);
760:
761:
762: IF (BSC_METADATA_OPTIMIZER_PKG.gMastertable(i).parent_name IS NULL ) THEN
763: return -1;
764: END IF;
765:
766: l_dummy := BSC_MO_HELPER_PKG.decomposestring(BSC_METADATA_OPTIMIZER_PKG.gMastertable(i).parent_name, ',', l_parent_name);
762: IF (BSC_METADATA_OPTIMIZER_PKG.gMastertable(i).parent_name IS NULL ) THEN
763: return -1;
764: END IF;
765:
766: l_dummy := BSC_MO_HELPER_PKG.decomposestring(BSC_METADATA_OPTIMIZER_PKG.gMastertable(i).parent_name, ',', l_parent_name);
767:
768: j := l_parent_name.first;
769: LOOP
770: IF UPPER(l_parent_name(j)) = UPPER(masterTableName) Then
778: EXCEPTION WHEN OTHERS THEN
779: g_error := sqlerrm;
780: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IndexRelation1N : '||g_error||', tablename='||tablename||', masterTableName='||masterTableName);
781: bsc_mo_helper_pkg.writeTmp('Dimension tables are ', FND_LOG.LEVEL_ERROR, true);
782: bsc_mo_helper_pkg.write_this(BSC_METADATA_OPTIMIZER_PKG.gMastertable, FND_LOG.LEVEL_ERROR, true);
783: raise;
784: End;
785:
786:
801: i NUMBER;
802: j NUMBER;
803:
804: BEGIN
805: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.count;
806:
807: IF (i = 0) THEN
808: return -1;
809: END IF;
807: IF (i = 0) THEN
808: return -1;
809: END IF;
810:
811: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first;
812: LOOP
813:
814: If ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableA)) And
815: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableB))) Or
810:
811: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first;
812: LOOP
813:
814: If ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableA)) And
815: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableB))) Or
816: ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableB)) And
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
811: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first;
812: LOOP
813:
814: If ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableA)) And
815: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableB))) Or
816: ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableB)) And
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
819: END IF;
812: LOOP
813:
814: If ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableA)) And
815: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableB))) Or
816: ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableB)) And
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
819: END IF;
820: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last;
813:
814: If ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableA)) And
815: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableB))) Or
816: ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableB)) And
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
819: END IF;
820: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last;
821: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.next(i);
816: ((UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableA) = UPPER(TableB)) And
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
819: END IF;
820: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last;
821: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.next(i);
822: END LOOP;
823:
824: return -1;
817: (UPPER(BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i).TableB) = UPPER(TableA))) Then
818: return i;
819: END IF;
820: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last;
821: i := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.next(i);
822: END LOOP;
823:
824: return -1;
825: EXCEPTION WHEN OTHERS THEN
846: --
847: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
848: --***************************************************************************
849:
850: Function GetPeriodicityOrigin(colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
851: Periodicity IN NUMBER,
852: forTargetLevel IN Boolean) RETURN NUMBER IS
853: PERIODIC BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
854: l_return NUMBER := -1;
849:
850: Function GetPeriodicityOrigin(colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
851: Periodicity IN NUMBER,
852: forTargetLevel IN Boolean) RETURN NUMBER IS
853: PERIODIC BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
854: l_return NUMBER := -1;
855: l_count NUMBER := -1;
856: l_per_table DBMS_SQL.NUMBER_TABLE;
857: l_dummy NUMBER;
860:
861:
862: l_index number;
863: BEGIN
864: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
865: bsc_mo_helper_pkg.writeTmp('Starting GetPeriodicityOrigin for Periodicity='|| Periodicity
866: ||', forTargetLevel='||bsc_mo_helper_pkg.boolean_decode(forTargetLevel));
867: bsc_mo_helper_pkg.write_this(colPeriodicities);
868: END IF;
881:
882: IF PERIODIC.Code <> Periodicity Then
883: IF (Not forTargetLevel) Or (forTargetLevel And PERIODIC.TargetLevel = 1) Then
884: l_stack := l_stack||g_newline||'check 2b';
885: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Periodicity);
886: l_stack := l_stack||g_newline||'check 2c, l_index = '||l_index;
887: l_per_table := BSC_MO_HELPER_PKG.decomposestringtonumber(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).PeriodicityOrigin, ',');
888: l_stack := l_stack||g_newline||'check 3';
889: IF BSC_MO_HELPER_PKG.FindIndex(l_per_table, PERIODIC.Code) >= 0 Then
883: IF (Not forTargetLevel) Or (forTargetLevel And PERIODIC.TargetLevel = 1) Then
884: l_stack := l_stack||g_newline||'check 2b';
885: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Periodicity);
886: l_stack := l_stack||g_newline||'check 2c, l_index = '||l_index;
887: l_per_table := BSC_MO_HELPER_PKG.decomposestringtonumber(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).PeriodicityOrigin, ',');
888: l_stack := l_stack||g_newline||'check 3';
889: IF BSC_MO_HELPER_PKG.FindIndex(l_per_table, PERIODIC.Code) >= 0 Then
890: l_stack := l_stack||g_newline||'check4';
891: l_return := PERIODIC.Code;
888: l_stack := l_stack||g_newline||'check 3';
889: IF BSC_MO_HELPER_PKG.FindIndex(l_per_table, PERIODIC.Code) >= 0 Then
890: l_stack := l_stack||g_newline||'check4';
891: l_return := PERIODIC.Code;
892: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
893: bsc_mo_helper_pkg.writeTmp('returning '||l_return);
894: END IF;
895: return l_return;
896: END IF;
901: EXIT WHEN l_count = colPeriodicities.last;
902: l_count := colPeriodicities.next(l_count);
903: l_stack := l_stack||g_newline||'l_count = '||l_count;
904: IF (length(l_stack) > 30000) THEN
905: if (BSC_METADATA_OPTIMIZER_PKG.g_log) Then
906: bsc_mo_helper_pkg.writeTmp(l_stack);
907: else
908: -- retain last 20000 chars
909: l_stack := substr(l_stack, 10000, length(l_stack));
909: l_stack := substr(l_stack, 10000, length(l_stack));
910: end if;
911: END IF;
912: END LOOP;
913: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
914: bsc_mo_helper_pkg.writeTmp('returning '||l_return);
915: END IF;
916: RETURN L_RETURN;
917:
970:
971: PROCEDURE init_measure_counts(l_list IN DBMS_SQL.number_table) IS
972: l_dummy varchar2(1000);
973: l_stmt varchar2(4000) := 'SELECT kpi.indicator||''_''|| i.dim_set_id hash_index, COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
974: FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I,
975: BSC_KPIS_VL kpi
976: WHERE I.MEASURE_ID = M.MEASURE_ID
977: AND kpi.indicator = i.indicator
978: AND M.TYPE = 0
1010: Function GetNumDataColumns(Indic IN NUMBER, DimSet IN NUMBER) RETURN NUMBER IS
1011:
1012: l_stmt varchar2(10000) :=
1013: 'SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
1014: FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I
1015: WHERE I.MEASURE_ID = M.MEASURE_ID
1016: AND I.DIM_SET_ID = :1
1017: AND I.INDICATOR = :2
1018: AND M.TYPE = 0
1020: AND NVL(M.SOURCE, ''BSC'') <> :3';
1021: numDataColumns number;
1022: cv CurTyp;
1023: CURSOR cExists IS
1024: select count(1) from user_objects where object_name = BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table;
1025:
1026: CURSOR cNumCols(pIgnore VARCHAR2) IS
1027: SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
1028: FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
1104: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1105: --*************************************************************************
1106: Function GetConfigurationsForIndic(Indic IN NUMBER) return DBMS_SQL.NUMBER_TABLE IS
1107: colConfigurationes dbms_sql.number_table;
1108: colMeasures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1109: Configuration NUMBER;
1110: CURSOR cConfigs IS
1111: SELECT DISTINCT DIM_SET_ID
1112: FROM BSC_DB_DATASET_DIM_SETS_V
1165: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1166: --***************************************************************************
1167:
1168: PROCEDURE ConfigureMasterSharedIndics IS
1169: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
1170: colConfigurationes dbms_sql.number_table;
1171: Configuration NUMBER;
1172: i NUMBER;
1173: j NUMBER;
1178:
1179: null;
1180:
1181:
1182: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count =0) THEN
1183: return;
1184: END IF;
1185: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
1186:
1181:
1182: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count =0) THEN
1183: return;
1184: END IF;
1185: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
1186:
1187:
1188: LOOP
1189: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(i);
1185: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
1186:
1187:
1188: LOOP
1189: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(i);
1190: --Only consider new indicators or indicators that have been modified.
1191: --BSC-MV Note: If there is change of summarization level
1192: --we need to process all the indicators.
1193:
1190: --Only consider new indicators or indicators that have been modified.
1191: --BSC-MV Note: If there is change of summarization level
1192: --we need to process all the indicators.
1193:
1194: If (Indicator.Action_Flag = 3) Or (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) Then
1195: --Get the list of configurations of the kpi
1196:
1197: colConfigurationes := GetConfigurationsForIndic(Indicator.Code);
1198: j := colConfigurationes.first;
1208: -- and PROJECTION_DATA
1209: --3182722
1210: l_stmt := 'INSERT INTO BSC_KPI_DATA_TABLES ( INDICATOR,PERIODICITY_ID,
1211: DIM_SET_ID, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION ';
1212: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1213: l_stmt := l_stmt ||', MV_NAME, PROJECTION_SOURCE , DATA_SOURCE , SQL_STMT , PROJECTION_DATA ';
1214: End If;
1215: l_stmt := l_stmt ||' ) SELECT :1, PERIODICITY_ID, :2, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION ';
1216: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1212: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1213: l_stmt := l_stmt ||', MV_NAME, PROJECTION_SOURCE , DATA_SOURCE , SQL_STMT , PROJECTION_DATA ';
1214: End If;
1215: l_stmt := l_stmt ||' ) SELECT :1, PERIODICITY_ID, :2, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION ';
1216: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1217: l_stmt := l_stmt ||', MV_NAME, PROJECTION_SOURCE , DATA_SOURCE , SQL_STMT, PROJECTION_DATA ';
1218: End If;
1219: l_stmt := l_stmt ||' FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = :3 AND DIM_SET_ID = :4';
1220:
1225: j := colConfigurationes.next(j);
1226: END LOOP;
1227:
1228: End If;
1229: EXIT WHEN i =BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
1230: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(i);
1231: END LOOP;
1232:
1233: EXCEPTION WHEN OTHERS THEN
1226: END LOOP;
1227:
1228: End If;
1229: EXIT WHEN i =BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
1230: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(i);
1231: END LOOP;
1232:
1233: EXCEPTION WHEN OTHERS THEN
1234: g_error := sqlerrm;
1247: -- Llave: key name
1248: --
1249: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1250: --***************************************************************************
1251: Function keyFieldExists(colCamposLlaves IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField, keyName IN VARCHAR2) return Boolean IS
1252: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1253: i NUMBER;
1254:
1255: BEGIN
1248: --
1249: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1250: --***************************************************************************
1251: Function keyFieldExists(colCamposLlaves IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField, keyName IN VARCHAR2) return Boolean IS
1252: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1253: i NUMBER;
1254:
1255: BEGIN
1256:
1289: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1290: --***************************************************************************
1291: Function SameDisaggregations(PeriodicityA IN NUMBER,
1292: --tableA IN VARCHAR2,
1293: tableA BSC_METADATA_OPTIMIZER_PKG.clsTable,
1294: PeriodicityB IN NUMBER,
1295: --tableB IN VARCHAR2
1296: tableB BSC_METADATA_OPTIMIZER_PKG.clsTable
1297: ) return Boolean IS
1292: --tableA IN VARCHAR2,
1293: tableA BSC_METADATA_OPTIMIZER_PKG.clsTable,
1294: PeriodicityB IN NUMBER,
1295: --tableB IN VARCHAR2
1296: tableB BSC_METADATA_OPTIMIZER_PKG.clsTable
1297: ) return Boolean IS
1298: keyNameIgual Boolean;
1299: keyNameA BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1300: l_res boolean := false;
1295: --tableB IN VARCHAR2
1296: tableB BSC_METADATA_OPTIMIZER_PKG.clsTable
1297: ) return Boolean IS
1298: keyNameIgual Boolean;
1299: keyNameA BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1300: l_res boolean := false;
1301: i NUMBER;
1302: keysA BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1303: keysB BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1298: keyNameIgual Boolean;
1299: keyNameA BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1300: l_res boolean := false;
1301: i NUMBER;
1302: keysA BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1303: keysB BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1304:
1305: BEGIN
1306:
1299: keyNameA BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1300: l_res boolean := false;
1301: i NUMBER;
1302: keysA BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1303: keysB BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1304:
1305: BEGIN
1306:
1307: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1303: keysB BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1304:
1305: BEGIN
1306:
1307: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1308: bsc_mo_helper_pkg.writeTmp('Inside SameDisaggregations, PeriodicityA='||PeriodicityA||', PeriodicityB='||PeriodicityB);
1309: END IF;
1310: --BSC Multiple optimizers
1311: keysA := tableA.keys;
1325: i := keysA.next(i);
1326: END LOOP;
1327: END IF;
1328: If keyNameIgual Then
1329: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1330: bsc_mo_helper_pkg.writeTmp('Completed SameDisaggregations, returning true');
1331: END IF;
1332: return true;
1333: END IF;
1332: return true;
1333: END IF;
1334: END IF;
1335: END IF;
1336: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1337: bsc_mo_helper_pkg.writeTmp('Completed SameDisaggregations, returning '||bsc_mo_helper_pkg.boolean_decode(l_res));
1338: END IF;
1339: return l_res;
1340: EXCEPTION WHEN OTHERS THEN
1353: -- It returns '' in case there is no target table.
1354: --
1355: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1356: --***************************************************************************
1357: Function GetTargetTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) return VARCHAR2 IS
1358:
1359: tbl BSC_METADATA_OPTIMIZER_PKG.clsTable;
1360: targetTable varchar2(100);
1361: keyField BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1355: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1356: --***************************************************************************
1357: Function GetTargetTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) return VARCHAR2 IS
1358:
1359: tbl BSC_METADATA_OPTIMIZER_PKG.clsTable;
1360: targetTable varchar2(100);
1361: keyField BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1362: i NUMBER;
1363:
1357: Function GetTargetTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) return VARCHAR2 IS
1358:
1359: tbl BSC_METADATA_OPTIMIZER_PKG.clsTable;
1360: targetTable varchar2(100);
1361: keyField BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1362: i NUMBER;
1363:
1364: BEGIN
1365: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1361: keyField BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1362: i NUMBER;
1363:
1364: BEGIN
1365: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1366: bsc_mo_helper_pkg.writeTmp('Inside GetTargetTable, pTable is');
1367: END IF;
1368: bsc_mo_helper_pkg.write_this(p_table);
1369: targetTable := null;
1366: bsc_mo_helper_pkg.writeTmp('Inside GetTargetTable, pTable is');
1367: END IF;
1368: bsc_mo_helper_pkg.write_this(p_table);
1369: targetTable := null;
1370: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1371: LOOP
1372: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1373: tbl := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1374: If tbl.Indicator = p_table.Indicator And tbl.Configuration = p_table.Configuration And tbl.IsTargetTable Then
1368: bsc_mo_helper_pkg.write_this(p_table);
1369: targetTable := null;
1370: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1371: LOOP
1372: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1373: tbl := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1374: If tbl.Indicator = p_table.Indicator And tbl.Configuration = p_table.Configuration And tbl.IsTargetTable Then
1375: If SameDisaggregations(tbl.Periodicity, tbl, p_table.Periodicity, p_table) Then
1376: targetTable := tbl.Name;
1369: targetTable := null;
1370: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1371: LOOP
1372: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1373: tbl := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1374: If tbl.Indicator = p_table.Indicator And tbl.Configuration = p_table.Configuration And tbl.IsTargetTable Then
1375: If SameDisaggregations(tbl.Periodicity, tbl, p_table.Periodicity, p_table) Then
1376: targetTable := tbl.Name;
1377: Exit;
1376: targetTable := tbl.Name;
1377: Exit;
1378: END IF;
1379: END IF;
1380: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1381: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1382: END LOOP;
1383: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1384: bsc_mo_helper_pkg.writeTmp('Completed GetTargetTable, returning '||targetTable);
1377: Exit;
1378: END IF;
1379: END IF;
1380: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1381: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1382: END LOOP;
1383: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1384: bsc_mo_helper_pkg.writeTmp('Completed GetTargetTable, returning '||targetTable);
1385: END IF;
1379: END IF;
1380: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1381: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1382: END LOOP;
1383: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1384: bsc_mo_helper_pkg.writeTmp('Completed GetTargetTable, returning '||targetTable);
1385: END IF;
1386: return targetTable;
1387: EXCEPTION WHEN OTHERS THEN
1389: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetTargetTable : '||g_error);
1390: raise;
1391: End;
1392:
1393: Function OriginTableHasTarget(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) return Boolean IS
1394: res Boolean;
1395: tableOri VARCHAR2(1000);
1396: l_res boolean :=false;
1397: i NUMBER;
1398: l_index NUMBER;
1399: l_origin_table DBMS_SQL.VARCHAR2_TABLE;
1400: l_dummy NUMBER;
1401: BEGIN
1402: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1403: bsc_mo_helper_pkg.writeTmp('Inside OriginTableHasTarget, p_table = ');
1404: END IF;
1405: l_dummy := BSC_MO_HELPER_PKG.decomposestring(p_table.originTable, ',', l_origin_table);
1406: i := l_origin_table.first;
1407: LOOP
1408: EXIT WHEN l_origin_table.count =0;
1409: tableOri := l_origin_table(i);
1410: l_res := True;
1411: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, tableOri);
1412: If Not BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).HasTargets Then
1413: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1414: bsc_mo_helper_pkg.writeTmp('Compl OriginTableHasTarget, returning false');
1415: END IF;
1408: EXIT WHEN l_origin_table.count =0;
1409: tableOri := l_origin_table(i);
1410: l_res := True;
1411: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, tableOri);
1412: If Not BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).HasTargets Then
1413: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1414: bsc_mo_helper_pkg.writeTmp('Compl OriginTableHasTarget, returning false');
1415: END IF;
1416: return false;
1409: tableOri := l_origin_table(i);
1410: l_res := True;
1411: l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, tableOri);
1412: If Not BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).HasTargets Then
1413: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1414: bsc_mo_helper_pkg.writeTmp('Compl OriginTableHasTarget, returning false');
1415: END IF;
1416: return false;
1417: END IF;
1417: END IF;
1418: EXIT WHEN i = l_origin_table.last;
1419: i := l_origin_table.next(i);
1420: END LOOP;
1421: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1422: bsc_mo_helper_pkg.writeTmp('Compl OriginTableHasTarget, returning '||bsc_mo_helper_pkg.boolean_decode(l_res));
1423: END IF;
1424: return l_res;
1425: EXCEPTION WHEN OTHERS THEN
1437: --
1438: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1439: --***************************************************************************
1440:
1441: Function TableAlreadyVisited(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, arrVisitedTables in dbms_sql.varchar2_table,
1442: numVisitedTables in number) return Boolean is
1443: OriTable VARCHAR2(1000);
1444: i NUMBER;
1445: l_origin_table DBMS_SQL.VARCHAR2_TABLE;
1475: -- Indicator: indicator
1476: -- Configuration: configuration
1477: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1478: --***************************************************************************
1479: PROCEDURE ConnectTargetTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
1480: arrVisitedTables dbms_sql.varchar2_table;
1481: numVisitedTables NUMBER;
1482: anyTableVisited Boolean;
1483: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1479: PROCEDURE ConnectTargetTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
1480: arrVisitedTables dbms_sql.varchar2_table;
1481: numVisitedTables NUMBER;
1482: anyTableVisited Boolean;
1483: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1484: targetTable varchar2(100);
1485: tableOri varchar2(100);
1486: i NUMBER;
1487: pt_name VARCHAR2(100);
1488: l_stmt VARCHAR2(1000);
1489: l_index1 NUMBER;
1490: l_next number;
1491: BEGIN
1492: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1493: bsc_mo_helper_pkg.writeTmp( 'In ConnectTargetTables, Configuration='||Configuration||', Indicator='||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
1494: END IF;
1495: bsc_mo_helper_pkg.write_this(Indicator);
1496: numVisitedTables := 0;
1505: --We need to visist the indicator tables in source-
1507: While anyTableVisited LOOP
1508: anyTableVisited := False;
1509: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0) THEN
1510: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1511: END IF;
1512: LOOP
1513: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1506: --and connect the target table
1507: While anyTableVisited LOOP
1508: anyTableVisited := False;
1509: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0) THEN
1510: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1511: END IF;
1512: LOOP
1513: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1514: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1509: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0) THEN
1510: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1511: END IF;
1512: LOOP
1513: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1514: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1515: --Check only tables of the given indicator and configuration and are not target tables
1516: IF (l_table.Indicator = Indicator.Code) And (l_table.Configuration = Configuration) And
1517: (Not l_table.IsTargetTable) THEN
1510: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1511: END IF;
1512: LOOP
1513: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
1514: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(i);
1515: --Check only tables of the given indicator and configuration and are not target tables
1516: IF (l_table.Indicator = Indicator.Code) And (l_table.Configuration = Configuration) And
1517: (Not l_table.IsTargetTable) THEN
1518: --Check if the table has not already been visited
1521: IF Not OriginTableHasTarget(l_table) THEN
1522: --If the origin table has targets then we do not need to
1523: --connect target table to this table
1524: targetTable := GetTargetTable(l_table);
1525: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1526: BSC_MO_HELPER_PKG.writeTmp('Target table is :' ||targetTable);
1527: END IF;
1528: IF targetTable IS NOT NULL THEN
1529: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1525: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1526: BSC_MO_HELPER_PKG.writeTmp('Target table is :' ||targetTable);
1527: END IF;
1528: IF targetTable IS NOT NULL THEN
1529: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1530: BSC_MO_HELPER_PKG.writeTmp('assigning target table is');
1531: END IF;
1532: tableOri := targetTable;
1533: IF (l_table.originTable1 IS NOT NULL) THEN
1534: l_table.originTable1 := l_table.originTable1 ||',';
1535: END IF;
1536: l_table.originTable1 := l_table.originTable1||tableOri;
1537: l_table.HasTargets := True;
1538: l_index1 := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, targetTable);
1539: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1).UsedForTargets := True;
1540: --BSC-MV Note: This table receives target. For that reason
1541: --it needs to calculate projection. The projection table needs to be
1542: --created in the database.
1535: END IF;
1536: l_table.originTable1 := l_table.originTable1||tableOri;
1537: l_table.HasTargets := True;
1538: l_index1 := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, targetTable);
1539: BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1).UsedForTargets := True;
1540: --BSC-MV Note: This table receives target. For that reason
1541: --it needs to calculate projection. The projection table needs to be
1542: --created in the database.
1543: --Also configure iViewer to read projection from this table.
1540: --BSC-MV Note: This table receives target. For that reason
1541: --it needs to calculate projection. The projection table needs to be
1542: --created in the database.
1543: --Also configure iViewer to read projection from this table.
1544: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table.impl_type=1 Then
1545: pt_name := GetProjectionTableName(l_table.name);
1546: l_Table.projectionTable := pt_name;
1547: UPDATE BSC_KPI_DATA_TABLES SET PROJECTION_SOURCE = 1,
1548: PROJECTION_DATA = pt_name
1557: --BSC-MV Note: This table does not receives direclty targets
1558: --but we need this table to maintain the projections
1559: --at higher levels. This table needs to be created in the database.
1560: --Also configure iViewer to read projection from this table.
1561: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table.impl_type=1 Then
1562: pt_name := GetProjectionTableName(l_Table.Name);
1563: l_Table.projectionTable := pt_name;
1564: UPDATE BSC_KPI_DATA_TABLES SET PROJECTION_SOURCE = 1,
1565: PROJECTION_DATA = pt_name
1573: numVisitedTables := numVisitedTables + 1;
1574: anyTableVisited := True;
1575: END IF;
1576: END IF;
1577: BSC_METADATA_OPTIMIZER_PKG.gTables(i) := l_table;
1578: END IF;
1579: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1580: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1581: END LOOP;
1575: END IF;
1576: END IF;
1577: BSC_METADATA_OPTIMIZER_PKG.gTables(i) := l_table;
1578: END IF;
1579: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1580: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1581: END LOOP;
1582: END LOOP; -- END OF WHILE
1583: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1576: END IF;
1577: BSC_METADATA_OPTIMIZER_PKG.gTables(i) := l_table;
1578: END IF;
1579: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1580: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1581: END LOOP;
1582: END LOOP; -- END OF WHILE
1583: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1584: BSC_MO_HELPER_PKG.writeTmp('remove target tables not being used');
1579: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1580: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1581: END LOOP;
1582: END LOOP; -- END OF WHILE
1583: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1584: BSC_MO_HELPER_PKG.writeTmp('remove target tables not being used');
1585: END IF;
1586: --remove target tables not being used
1587: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1583: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1584: BSC_MO_HELPER_PKG.writeTmp('remove target tables not being used');
1585: END IF;
1586: --remove target tables not being used
1587: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1588: LOOP
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1585: END IF;
1586: --remove target tables not being used
1587: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1588: LOOP
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1586: --remove target tables not being used
1587: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1588: LOOP
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1587: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1588: LOOP
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1588: LOOP
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1589: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.Count = 0;
1590: IF (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Indicator = Indicator.Code) And (BSC_METADATA_OPTIMIZER_PKG.gTables(i).Configuration = Configuration) And
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1597: EXIT;
1591: BSC_METADATA_OPTIMIZER_PKG.gTables(i).IsTargetTable And (Not BSC_METADATA_OPTIMIZER_PKG.gTables(i).UsedForTargets) Then
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1597: EXIT;
1598: ELSE
1599: l_next := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1592: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1593: BSC_MO_HELPER_PKG.writeTmp('1 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
1594: END IF;
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1597: EXIT;
1598: ELSE
1599: l_next := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1600: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1595: IF (i = BSC_METADATA_OPTIMIZER_PKG.gTables.last) THEN
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1597: EXIT;
1598: ELSE
1599: l_next := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1600: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1601: i := -1;
1602: END IF;
1603: END IF;
1596: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1597: EXIT;
1598: ELSE
1599: l_next := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1600: BSC_METADATA_OPTIMIZER_PKG.gTables.delete(i);
1601: i := -1;
1602: END IF;
1603: END IF;
1604: IF (i <> -1) THEN
1601: i := -1;
1602: END IF;
1603: END IF;
1604: IF (i <> -1) THEN
1605: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1606: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1607: ELSE
1608: i := l_next;
1609: END IF;
1602: END IF;
1603: END IF;
1604: IF (i <> -1) THEN
1605: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1606: i := BSC_METADATA_OPTIMIZER_PKG.gTables.next(i);
1607: ELSE
1608: i := l_next;
1609: END IF;
1610: END LOOP;
1607: ELSE
1608: i := l_next;
1609: END IF;
1610: END LOOP;
1611: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1612: bsc_mo_helper_pkg.writeTmp( 'Done with ConnectTargetTables, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
1613: END IF;
1614: EXCEPTION WHEN OTHERS THEN
1615: g_error := sqlerrm;
1625: -- p_dimension_families: collection of drill families
1626: -- NomLlave: key name
1627: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1628: --***************************************************************************
1629: Function GetKeyNum(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, NomkeyName IN VARCHAR2)
1630: return NUMBER IS
1631: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
1632: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
1633: i NUMBER;
1627: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1628: --***************************************************************************
1629: Function GetKeyNum(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, NomkeyName IN VARCHAR2)
1630: return NUMBER IS
1631: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
1632: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
1633: i NUMBER;
1634: j NUMBER;
1635: l_res NUMBER := -1;
1628: --***************************************************************************
1629: Function GetKeyNum(p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, NomkeyName IN VARCHAR2)
1630: return NUMBER IS
1631: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
1632: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
1633: i NUMBER;
1634: j NUMBER;
1635: l_res NUMBER := -1;
1636: l_groups DBMS_SQL.NUMBER_TABLE;
1682: -- p_dimension_families: collection of drill families
1683: -- forTargetLevel: true -Tables are for Targets
1684: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1685: --***************************************************************************
1686: PROCEDURE deduce_and_configure_s_tables (Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator,
1687: Configuration IN NUMBER,
1688: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
1689: colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
1690: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
1684: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
1685: --***************************************************************************
1686: PROCEDURE deduce_and_configure_s_tables (Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator,
1687: Configuration IN NUMBER,
1688: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
1689: colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
1690: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
1691: forTargetLevel IN Boolean)IS
1692:
1685: --***************************************************************************
1686: PROCEDURE deduce_and_configure_s_tables (Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator,
1687: Configuration IN NUMBER,
1688: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
1689: colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
1690: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
1691: forTargetLevel IN Boolean)IS
1692:
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1686: PROCEDURE deduce_and_configure_s_tables (Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator,
1687: Configuration IN NUMBER,
1688: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
1689: colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
1690: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
1691: forTargetLevel IN Boolean)IS
1692:
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1694: L_Periodicity_Origin NUMBER;
1689: colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity,
1690: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
1691: forTargetLevel IN Boolean)IS
1692:
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1694: L_Periodicity_Origin NUMBER;
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1691: forTargetLevel IN Boolean)IS
1692:
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1694: L_Periodicity_Origin NUMBER;
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1692:
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1694: L_Periodicity_Origin NUMBER;
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1700: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1693: L_Periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
1694: L_Periodicity_Origin NUMBER;
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1700: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1701:
1694: L_Periodicity_Origin NUMBER;
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1700: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1701:
1702: l_stmt VARCHAR2(1000);
1695: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1700: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1701:
1702: l_stmt VARCHAR2(1000);
1703: cond VARCHAR2(1000);
1696: L_Table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1697: keyBasica BSC_METADATA_OPTIMIZER_PKG.clskeyField;
1698: key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1699: DatoBasica BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1700: Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1701:
1702: l_stmt VARCHAR2(1000);
1703: cond VARCHAR2(1000);
1704: CodPrimerDril NUMBER;
1709: k NUMBER;
1710: l NUMBER;
1711:
1712: l_test NUMBER;
1713: basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1714: basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1715:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1710: l NUMBER;
1711:
1712: l_test NUMBER;
1713: basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1714: basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1715:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1718: TableLevel NUMBER;
1712: l_test NUMBER;
1713: basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1714: basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1715:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1718: TableLevel NUMBER;
1719: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
1720: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
1713: basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1714: basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1715:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1718: TableLevel NUMBER;
1719: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
1720: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
1721: l_counter NUMBER;
1715:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1718: TableLevel NUMBER;
1719: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
1720: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
1721: l_counter NUMBER;
1722: first_periodicity_id NUMBEr := 0;
1723:
1716: table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clskeyField;
1717: table_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1718: TableLevel NUMBER;
1719: configKpiMV BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
1720: colConfigKpiMV BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV;
1721: l_counter NUMBER;
1722: first_periodicity_id NUMBEr := 0;
1723:
1724: TYPE tab_clsKPIData IS TABLE OF BSC_KPI_DATA_TABLES%ROWTYPE index by binary_integer;
1741: return;
1742: END IF;
1743: LOOP
1744: L_Periodicity := colPeriodicities(i);
1745: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1746: BSC_MO_HELPER_PKG.writeTmp('Periodicity = '||l_periodicity.code);
1747: BSC_MO_HELPER_PKG.writeTmp('---------------');
1748: END IF;
1749: If (Not forTargetLevel) Or (forTargetLevel And l_periodicity.TargetLevel = 1) Then
1753: Else
1754: --the indicator is pre-calculated. All tables of the indicator will be base tables.
1755: L_Periodicity_Origin := -1;
1756: END IF;
1757: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1758: BSC_MO_HELPER_PKG.writeTmp('L_Periodicity_Origin = '||L_Periodicity_Origin);
1759: END IF;
1760: j := colSummaryTables.first;
1761: LOOP
1761: LOOP
1762: EXIT WHEN colSummaryTables.count =0;
1763: L_Table := bsc_mo_helper_pkg.new_clsTable;
1764: Basica := colSummaryTables(j);
1765: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1766: bsc_mo_helper_pkg.writeTmp( 'Processing table '||Basica.name);
1767: bsc_mo_helper_pkg.write_this(Basica);
1768: END IF;
1769: --BSC Multiple Optimizers
1788: key.Origin := keyBasica.keyName;
1789: key.NeedsCode0 := keyBasica.NeedsCode0;
1790: --BSC-MV Note: In BSC-MV architecture we need to configure zero code
1791: --on all the tables needing zero code.
1792: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1793: key.CalculateCode0 := keyBasica.CalculateCode0;
1794: Else
1795: key.CalculateCode0 := False;
1796: End If;
1889: L_Table.HasTargets := False;
1890: L_Table.UsedForTargets := False;
1891: --BSC-MV Note: If we are in upgrade (sum level changes from NULL to NOTNULL)
1892: --and the indicator is in production the falg this table with upgradeFlag = 1
1893: If (BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1) And (Indicator.Action_Flag <> 3) Then
1894: L_Table.upgradeFlag := 1;
1895: End If;
1896:
1897: L_Table.impl_type := g_current_indicator.Impl_Type;
1895: End If;
1896:
1897: L_Table.impl_type := g_current_indicator.Impl_Type;
1898: --Add the table to gTablas
1899: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count>0)THEN
1900: l_test := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, L_Table.name);
1901: IF (l_test >0 ) THEN --corruption
1902: l_test := -1;
1903: END IF;
1896:
1897: L_Table.impl_type := g_current_indicator.Impl_Type;
1898: --Add the table to gTablas
1899: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count>0)THEN
1900: l_test := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, L_Table.name);
1901: IF (l_test >0 ) THEN --corruption
1902: l_test := -1;
1903: END IF;
1904: BSC_MO_HELPER_PKG.addTable(L_Table, Table_Keys, Table_data, 'deduce_and_configure_s_tables');
1928: k := basic_keys.next(k);
1929: END LOOP;
1930: --EDW Note: For EDW KPIs we need to user the union view name in BSC_KPI_DATA_TABLES
1931: If L_Table.EDW_Flag = 1 Then
1932: TableName := L_Table.Name || BSC_METADATA_OPTIMIZER_PKG.EDW_UNION_VIEW_EXT;
1933: Else
1934: TableName := L_Table.Name;
1935: END IF;
1936: --BSC-MV Note: We need to configure one entry for each combination
1936: --BSC-MV Note: We need to configure one entry for each combination
1937: --of zero codes. If the level of the table is greater than
1938: --g_adv_sum_level then we configure a SQL statement else we
1939: --configure to read from the ZMV (MV for zero codes)
1940: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
1941: IF (g_current_indicator.Impl_Type=2) THEN -- AW, so set tablelevel as 0
1942: TableLevel := 0;
1943: ELSE
1944: TableLevel := getTableLevel(Basica.Name, colSummaryTables);
1943: ELSE
1944: TableLevel := getTableLevel(Basica.Name, colSummaryTables);
1945: END IF;
1946: -- bug 3835059, we need to create sql stmts instead of mv if # of
1947: -- levels > BSC_METADATA_OPTIMIZER_PKG.MAX_ALLOWED_LEVELS
1948: colConfigKpiMV := GetColConfigKpiMV(Basica, TableLevel, p_dimension_families, colSummaryTables);
1949: l_stmt := 'INSERT INTO BSC_KPI_DATA_TABLES (INDICATOR, PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB,
1950: TABLE_NAME, FILTER_CONDITION, MV_NAME, PROJECTION_SOURCE, DATA_SOURCE, SQL_STMT, PROJECTION_DATA)
1951: VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)';
2049: -- keyNameOri: collection of keys
2050: -- Llave: key name
2051: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2052: --***************************************************************************
2053: Function GetKeyOrigin(keyNamesOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField, keyName IN VARCHAR2) return VARCHAR2 IS
2054: keyNameOri BSC_METADATA_OPTIMIZER_PKG.clskeyField;
2055: i NUMBER;
2056: l_index1 number;
2057: l_index2 number;
2050: -- Llave: key name
2051: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2052: --***************************************************************************
2053: Function GetKeyOrigin(keyNamesOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField, keyName IN VARCHAR2) return VARCHAR2 IS
2054: keyNameOri BSC_METADATA_OPTIMIZER_PKG.clskeyField;
2055: i NUMBER;
2056: l_index1 number;
2057: l_index2 number;
2058:
2077: i := keyNamesOri.first;
2078: LOOP
2079: keyNameOri := keyNamesOri(i);
2080:
2081: l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyNameOri.keyName);
2082: l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyName);
2083: If (l_index1>=0 AND l_index2>=0 AND
2084: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
2085: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 ) Then
2078: LOOP
2079: keyNameOri := keyNamesOri(i);
2080:
2081: l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyNameOri.keyName);
2082: l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyName);
2083: If (l_index1>=0 AND l_index2>=0 AND
2084: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
2085: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 ) Then
2086: return keyNameOri.keyName;
2080:
2081: l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyNameOri.keyName);
2082: l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyName);
2083: If (l_index1>=0 AND l_index2>=0 AND
2084: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
2085: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 ) Then
2086: return keyNameOri.keyName;
2087: END IF;
2088: EXIT WHEN i = keyNamesOri.last;
2081: l_index1 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyNameOri.keyName);
2082: l_index2 := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, keyName);
2083: If (l_index1>=0 AND l_index2>=0 AND
2084: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name,
2085: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 ) Then
2086: return keyNameOri.keyName;
2087: END IF;
2088: EXIT WHEN i = keyNamesOri.last;
2089: i:= keyNamesOri.next(i);
2113: --
2114: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2115: --***************************************************************************
2116:
2117: Function keyOriginExists(keyNameDest IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2118: keyNameOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2119: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return Boolean IS
2120: DrilOri BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2121: DrilDest BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2114: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2115: --***************************************************************************
2116:
2117: Function keyOriginExists(keyNameDest IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2118: keyNameOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2119: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return Boolean IS
2120: DrilOri BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2121: DrilDest BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2122: numChanges NUMBER;
2115: --***************************************************************************
2116:
2117: Function keyOriginExists(keyNameDest IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2118: keyNameOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2119: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return Boolean IS
2120: DrilOri BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2121: DrilDest BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2122: numChanges NUMBER;
2123: originExists boolean;
2116:
2117: Function keyOriginExists(keyNameDest IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2118: keyNameOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2119: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return Boolean IS
2120: DrilOri BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2121: DrilDest BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2122: numChanges NUMBER;
2123: originExists boolean;
2124: FamilyIndex number;
2117: Function keyOriginExists(keyNameDest IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2118: keyNameOri IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
2119: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return Boolean IS
2120: DrilOri BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2121: DrilDest BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2122: numChanges NUMBER;
2123: originExists boolean;
2124: FamilyIndex number;
2125: i NUMBER;
2153: Exit;
2154: END IF;
2155: If FindDimensionGroupIndexForKey(p_dimension_families, DrilOri.keyName) = FamilyIndex Then
2156: --Both keys origin and target belong to the same familiy of drill within the kpi.
2157: l_index1 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilOri.keyName);
2158: l_index2 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilDest.keyName);
2159: If IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name ,
2160: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
2161: originExists := True;
2154: END IF;
2155: If FindDimensionGroupIndexForKey(p_dimension_families, DrilOri.keyName) = FamilyIndex Then
2156: --Both keys origin and target belong to the same familiy of drill within the kpi.
2157: l_index1 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilOri.keyName);
2158: l_index2 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilDest.keyName);
2159: If IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name ,
2160: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
2161: originExists := True;
2162: numChanges := numChanges + 1;
2155: If FindDimensionGroupIndexForKey(p_dimension_families, DrilOri.keyName) = FamilyIndex Then
2156: --Both keys origin and target belong to the same familiy of drill within the kpi.
2157: l_index1 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilOri.keyName);
2158: l_index2 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilDest.keyName);
2159: If IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name ,
2160: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
2161: originExists := True;
2162: numChanges := numChanges + 1;
2163: Exit;
2156: --Both keys origin and target belong to the same familiy of drill within the kpi.
2157: l_index1 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilOri.keyName);
2158: l_index2 := BSC_MO_HELPER_PKG.findKeyindex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, DrilDest.keyName);
2159: If IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index1).Name ,
2160: BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_index2).Name) >= 0 Then
2161: originExists := True;
2162: numChanges := numChanges + 1;
2163: Exit;
2164: END IF;
2194: --
2195: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2196: --***************************************************************************
2197: PROCEDURE DeduceInternalGraph(
2198: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
2199: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2200: forTargetLevel IN Boolean) IS
2201: l_s_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2202: Basica1 BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2195: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2196: --***************************************************************************
2197: PROCEDURE DeduceInternalGraph(
2198: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
2199: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2200: forTargetLevel IN Boolean) IS
2201: l_s_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2202: Basica1 BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2203: keyName BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2197: PROCEDURE DeduceInternalGraph(
2198: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
2199: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2200: forTargetLevel IN Boolean) IS
2201: l_s_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2202: Basica1 BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2203: keyName BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2204: originExists Boolean;
2205: i NUMBER;
2198: colSummaryTables IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
2199: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2200: forTargetLevel IN Boolean) IS
2201: l_s_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2202: Basica1 BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2203: keyName BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2204: originExists Boolean;
2205: i NUMBER;
2206: j NUMBER;
2199: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2200: forTargetLevel IN Boolean) IS
2201: l_s_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2202: Basica1 BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2203: keyName BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2204: originExists Boolean;
2205: i NUMBER;
2206: j NUMBER;
2207: k NUMBER;
2205: i NUMBER;
2206: j NUMBER;
2207: k NUMBER;
2208: l_index NUMBER;
2209: l_s_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2210: l_s_table_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2211: Basic1_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2212: Basic1_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2213:
2206: j NUMBER;
2207: k NUMBER;
2208: l_index NUMBER;
2209: l_s_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2210: l_s_table_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2211: Basic1_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2212: Basic1_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2213:
2214: BEGIN
2207: k NUMBER;
2208: l_index NUMBER;
2209: l_s_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2210: l_s_table_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2211: Basic1_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2212: Basic1_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2213:
2214: BEGIN
2215:
2208: l_index NUMBER;
2209: l_s_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2210: l_s_table_measures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2211: Basic1_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2212: Basic1_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2213:
2214: BEGIN
2215:
2216: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2212: Basic1_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2213:
2214: BEGIN
2215:
2216: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2217: bsc_mo_helper_pkg.writeTmp(' ');
2218: bsc_mo_helper_pkg.writeTmp('Inside DeduceInternalGraph, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
2219: bsc_mo_helper_pkg.writeTmp(' colSummaryTables is as above', FND_LOG.LEVEL_STATEMENT);
2220: END IF;
2235: If Basica1.Name <> l_s_table.Name Then
2236: Basic1_keys := Basica1.keys;
2237: Basic1_data := Basica1.data;
2238: If keyOriginExists(l_s_table_keys, Basic1_keys, p_dimension_families) Then
2239: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2240: bsc_mo_helper_pkg.writeTmp( ' ');
2241: END IF;
2242: --l_s_table can be originated from Basica1
2243: --For each key assign the properties Origen and CalcularCod0
2244: IF (l_s_table_keys.count > 0) THEN
2245: k := l_s_table_keys.first;
2246: LOOP
2247: keyName := l_s_table_keys(k);
2248: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2249: bsc_mo_helper_pkg.writeTmp( 'Processing key');
2250: bsc_mo_helper_pkg.write_this(keyName);
2251: END IF;
2252: --assign the origin field with the name of the key where it is originated from.
2253: keyName.Origin := GetKeyOrigin(Basic1_keys, keyName.keyName);
2254: --BSC-MV Note: In BSC-MV architecture we need to configure zero code
2255: --on all the tables needing zero code.
2256: --Tables for targets only there is no need to calculate zero code.
2257: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
2258: --BSC-MV/V Architecture
2259: If Not forTargetLevel Then
2260: If keyName.NeedsCode0 Then
2261: keyName.CalculateCode0 := True;
2263: End If;
2264: ELSE
2265: --Table architecture
2266: --If there is key change and the key needs code 0 then CalculateCode0 = True
2267: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2268: bsc_mo_helper_pkg.writeTmp( 'Table architecture, keyName.Origin='||
2269: keyName.Origin||', keyName.keyName='||keyName.keyName);
2270: END IF;
2271: If Upper(keyName.Origin) <> UPPER(keyName.keyName) Then
2269: keyName.Origin||', keyName.keyName='||keyName.keyName);
2270: END IF;
2271: If Upper(keyName.Origin) <> UPPER(keyName.keyName) Then
2272: If keyName.NeedsCode0 Then
2273: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2274: bsc_mo_helper_pkg.writeTmp('1 Switching CalculateCode0 to TRUE for table '||
2275: l_s_table.name||', key='||keyName.keyName);
2276: END IF;
2277: keyName.calculateCode0 := True;
2280: --If there is no key change and needs code 0 but the origin
2281: --key does not need code 0 then CalcularCod0 = true
2282: If keyName.NeedsCode0 Then
2283: l_index := BSC_MO_HELPER_PKG.findindex(Basic1_keys, keyName.Origin);
2284: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2285: bsc_mo_helper_pkg.writeTmp( 'l_index = '||l_index||', Basic1_keys('||l_index||') = ');
2286: bsc_mo_helper_pkg.write_this(Basic1_Keys(l_index));
2287: END IF;
2288: If L_INDEX>=0 THEN
2288: If L_INDEX>=0 THEN
2289: IF (Basic1_keys(l_index).NeedsCode0) Then
2290: null;
2291: ELSE
2292: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2293: bsc_mo_helper_pkg.writeTmp('2 Switching CalculateCode0 to TRUE for table '||l_s_table.name||', key='||keyName.keyName);
2294: END IF;
2295: keyName.calculateCode0 := True;
2296: END IF;
2316: EXIT WHEN j = colSummaryTables.last;
2317: j := colSummaryTables.next(j);
2318: END LOOP;
2319: If Not originExists Then
2320: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2321: bsc_mo_helper_pkg.writeTmp('It was not possible to generate the table from another one.');
2322: END IF;
2323: --It was not possible to generate the table from another one.
2324: --For each key, assign the properties Origen and CalcularCod0
2326: j := l_s_table_keys.first;
2327: LOOP
2328: keyName := l_s_table_keys(j);
2329: --Leave the field Origen in ''
2330: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2331: bsc_mo_helper_pkg.writeTmp( 'Processing key');
2332: bsc_mo_helper_pkg.write_this(keyName);
2333: END IF;
2334: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
2330: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2331: bsc_mo_helper_pkg.writeTmp( 'Processing key');
2332: bsc_mo_helper_pkg.write_this(keyName);
2333: END IF;
2334: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
2335: --BSC-MV Note: In this architecture
2336: -- No zero code needed in tables for targets
2337: If Not forTargetLevel Then
2338: --If the key needs code 0 then CalcularCod0 = True
2336: -- No zero code needed in tables for targets
2337: If Not forTargetLevel Then
2338: --If the key needs code 0 then CalcularCod0 = True
2339: If keyName.NeedsCode0 Then
2340: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2341: bsc_mo_helper_pkg.writeTmp('3 Switching CalculateCode0 to TRUE for table '||l_s_table.name||', key='||keyName.keyName);
2342: END IF;
2343: keyName.calculateCode0 := True;
2344: End If;
2346: Else
2347: --If the key needs code 0 then CalcularCod0 = True
2348: If keyName.NeedsCode0 Then
2349: keyName.CalculateCode0 := True;
2350: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2351: bsc_mo_helper_pkg.writeTmp('4 Switching CalculateCode0 to TRUE for table '||l_s_table.name||', key='||keyName.keyName);
2352: END IF;
2353: END IF;
2354: END IF;
2363: END IF;
2364: --leave the property TablaOri in ''
2365: END IF;
2366:
2367: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2368: bsc_mo_helper_pkg.writeTmp( ' ');
2369: END IF;
2370: colSummaryTables(i) := l_s_table;
2371: EXIT WHEN i = colSummaryTables.last;
2371: EXIT WHEN i = colSummaryTables.last;
2372: i := colSummaryTables.next(i);
2373: END LOOP;
2374:
2375: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2376: bsc_mo_helper_pkg.writeTmp('Completed DeduceInternalGraph, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
2377: bsc_mo_helper_pkg.writeTmp(' colSummaryTables is ', FND_LOG.LEVEL_STATEMENT);
2378: bsc_mo_helper_pkg.write_this(colSummaryTables);
2379: END IF;
2404: --
2405: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2406: --***************************************************************************
2407: Function order_level_string(p_level_string IN VARCHAR2,
2408: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels) return VARCHAR2
2409: IS
2410: arrOrdenDrilesActual dbms_sql.number_table;
2411: arrOrdenDriles dbms_sql.number_table;
2412: arrConfDriles dbms_sql.varchar2_table;
2415: j NUMBER;
2416: temp NUMBER;
2417: l_ordered_level_string varchar2(1000);
2418: tempC varchar2(1000);
2419: DimLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
2420: l_groups DBMS_SQL.NUMBER_TABLE;
2421: l_group_id NUMBER;
2422:
2423: BEGIN
2451: l_ordered_level_string := null;
2452: For i in 0..numDriles - 1 LOOP
2453: l_ordered_level_string := l_ordered_level_string || arrConfDriles(i);
2454: END LOOP;
2455: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2456: bsc_mo_helper_pkg.writeTmp( 'Done with order_level_string, returning '||l_ordered_level_string, FND_LOG.LEVEL_PROCEDURE);
2457: END IF;
2458:
2459: return l_ordered_level_string;
2489: WHERE INDICATOR = pIndicator
2490: AND DIM_SET_ID = pConfiguration
2491: AND LEVEL_PK_COL = pKeyCol;
2492: BEGIN
2493: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2494: bsc_mo_helper_pkg.writeTmp( 'Inside GetFilterViewName, Indicator='||Indicator||', Configuration='||Configuration
2495: ||', CampoLlave='||CampoLlave , FND_LOG.LEVEL_PROCEDURE);
2496: END IF;
2497:
2499: OPEN cLevelViewName(Indicator, Configuration, upper(CampoLlave));
2500: FETCH cLevelViewName INTO l_return;
2501: CLOSE cLevelViewName;
2502:
2503: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2504: bsc_mo_helper_pkg.writeTmp( 'Completed GetFilterViewName, returning '||l_return, FND_LOG.LEVEL_PROCEDURE);
2505: END IF;
2506:
2507: return l_return;
2525: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2526: --***************************************************************************
2527: Function IsIndicatorPnL(Ind IN Integer, pUseGIndics boolean) return Boolean IS
2528: l_index NUMBER;
2529: l_indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
2530: BEGIN
2531:
2532: if (pUseGIndics) then
2533: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2529: l_indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
2530: BEGIN
2531:
2532: if (pUseGIndics) then
2533: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2534: l_indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index);
2535: else
2536: l_index := BSC_MO_HELPER_PKG.findindex(BSC_MO_DOC_PKG.gDocIndicators, Ind);
2537: l_indicator := BSC_MO_DOC_PKG.gDocIndicators(l_index);
2530: BEGIN
2531:
2532: if (pUseGIndics) then
2533: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2534: l_indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index);
2535: else
2536: l_index := BSC_MO_HELPER_PKG.findindex(BSC_MO_DOC_PKG.gDocIndicators, Ind);
2537: l_indicator := BSC_MO_DOC_PKG.gDocIndicators(l_index);
2538: end if;
2562: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2563: --***************************************************************************
2564: Function IsIndicatorBalance(Ind IN NUMBER, pUseGIndics boolean) return Boolean IS
2565: l_index NUMBER;
2566: l_indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
2567: BEGIN
2568:
2569: if (pUseGIndics) then
2570: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2566: l_indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
2567: BEGIN
2568:
2569: if (pUseGIndics) then
2570: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2571: l_indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index);
2572: else
2573: l_index := BSC_MO_HELPER_PKG.findindex(BSC_MO_DOC_PKG.gDocIndicators, Ind);
2574: l_indicator := BSC_MO_DOC_PKG.gDocIndicators(l_index);
2567: BEGIN
2568:
2569: if (pUseGIndics) then
2570: l_index := BSC_MO_HELPER_PKG.findindex(BSC_METADATA_OPTIMIZER_PKG.gIndicators, Ind);
2571: l_indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index);
2572: else
2573: l_index := BSC_MO_HELPER_PKG.findindex(BSC_MO_DOC_PKG.gDocIndicators, Ind);
2574: l_indicator := BSC_MO_DOC_PKG.gDocIndicators(l_index);
2575: end if;
2619: -- colDataColumns is collection of object of class clsDataField
2620: --
2621: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2622: --***************************************************************************
2623: Function CopyOfColDataColumns(colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2624: return BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
2625: Begin
2626: return colDataColumns;
2627: End ;
2620: --
2621: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2622: --***************************************************************************
2623: Function CopyOfColDataColumns(colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2624: return BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
2625: Begin
2626: return colDataColumns;
2627: End ;
2628:
2667:
2668: If p_num_dimensions = 0 Then
2669: return;
2670: END IF;
2671: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2672: bsc_mo_helper_pkg.writeTmp( 'Inside CalcCartesianProduct, p_num_dimensions='||p_num_dimensions||' p_cartesian_product is ');
2673: bsc_mo_helper_pkg.write_this(p_cartesian_product, FND_LOG.LEVEL_STATEMENT);
2674: bsc_mo_helper_pkg.writeTmp( 'dimensionSizes is ', FND_LOG.LEVEL_STATEMENT);
2675: bsc_mo_helper_pkg.write_this(dimensionSizes, FND_LOG.LEVEL_STATEMENT);
2697: iTimes := iTimes * dimensionSizes(i);
2698:
2699: END LOOP;
2700:
2701: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2702: bsc_mo_helper_pkg.writeTmp( 'Compl. CalcCartesianProduct, Cartesian product is ', FND_LOG.LEVEL_PROCEDURE);
2703: bsc_mo_helper_pkg.write_this(p_cartesian_product, FND_LOG.LEVEL_STATEMENT);
2704: bsc_mo_helper_pkg.writeTmp( 'dimensionSizes is ', FND_LOG.LEVEL_STATEMENT);
2705: bsc_mo_helper_pkg.write_this(dimensionSizes, FND_LOG.LEVEL_STATEMENT);
2781: -- forTargetLevel: true -The procedure is called to get base tables for targets
2782: -- colDataColumns: collection of data columns
2783: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2784: --***************************************************************************
2785: Function GetBasicTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER,
2786: colDimLevelCombinations IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
2787: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2782: -- colDataColumns: collection of data columns
2783: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2784: --***************************************************************************
2785: Function GetBasicTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER,
2786: colDimLevelCombinations IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
2787: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2783: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2784: --***************************************************************************
2785: Function GetBasicTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER,
2786: colDimLevelCombinations IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
2787: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2785: Function GetBasicTables(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER,
2786: colDimLevelCombinations IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
2787: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2786: colDimLevelCombinations IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
2787: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2788: forTargetLevel IN Boolean,
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2795: Basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2796:
2789: colDataColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField)
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2795: Basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2796:
2797: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2790: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable IS
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2795: Basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2796:
2797: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2798: p_cartesian_product DBMS_SQL.NUMBER_TABLE;
2791:
2792: colBasicaTablas BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2795: Basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2796:
2797: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2798: p_cartesian_product DBMS_SQL.NUMBER_TABLE;
2799:
2793: Basica BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
2794: Basic_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
2795: Basic_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
2796:
2797: CampoLlave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
2798: p_cartesian_product DBMS_SQL.NUMBER_TABLE;
2799:
2800: NumDimensions NUMBER;
2801: NumLevels NUMBER;
2812: cLevel varchar2(1000);
2813: ConfDriles varchar2(1000);
2814: NumFamilias NUMBER;
2815: ifamilia NUMBER;
2816: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
2817: Dril1 BSC_METADATA_OPTIMIZER_PKG.clsLevels;
2818: msg varchar2(1000);
2819: TableNameStart varchar2(1000);
2820: l_temp NUMBER;
2813: ConfDriles varchar2(1000);
2814: NumFamilias NUMBER;
2815: ifamilia NUMBER;
2816: Dril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
2817: Dril1 BSC_METADATA_OPTIMIZER_PKG.clsLevels;
2818: msg varchar2(1000);
2819: TableNameStart varchar2(1000);
2820: l_temp NUMBER;
2821: l_tempv VARCHAR2(100);
2821: l_tempv VARCHAR2(100);
2822:
2823: l_groups DBMS_SQL.NUMBER_TABLE;
2824: l_group_id NUMBER;
2825: l_drillCombination BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
2826: l_drillCombination2 BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
2827:
2828: l_drillString VARCHAR2(4000);
2829: l_drillTable DBMS_SQL.VARCHAR2_TABLE;
2822:
2823: l_groups DBMS_SQL.NUMBER_TABLE;
2824: l_group_id NUMBER;
2825: l_drillCombination BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
2826: l_drillCombination2 BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
2827:
2828: l_drillString VARCHAR2(4000);
2829: l_drillTable DBMS_SQL.VARCHAR2_TABLE;
2830:
2829: l_drillTable DBMS_SQL.VARCHAR2_TABLE;
2830:
2831: l_level_groups DBMS_SQL.NUMBER_TABLE;
2832: l_level_group_id NUMBER;
2833: l_dimLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
2834:
2835: BEGIN
2836: l_groups := BSC_MO_HELPER_PKG.getGroupIDs(colDimLevelCombinations);
2837: l_level_groups := BSC_MO_HELPER_PKG.getGroupIDs(p_dimension_families);
2837: l_level_groups := BSC_MO_HELPER_PKG.getGroupIDs(p_dimension_families);
2838: NumDimensions := l_groups.count;
2839: bsc_mo_helper_pkg.writeTmp( 'Inside GetBasicTables, Configuration ='||Configuration||', forTargetLevel ='
2840: ||bsc_mo_helper_pkg.boolean_decode(forTargetLevel ) ||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_PROCEDURE, FALSE);
2841: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2842: bsc_mo_helper_pkg.writeTmp( 'Indicator is ');
2843: bsc_mo_helper_pkg.write_this(Indicator);
2844: bsc_mo_helper_pkg.writeTmp( 'colDimLevelCombinations is as above');
2845: bsc_mo_helper_pkg.writeTmp( 'p_dimension_families is as above');
2861: --Keys
2862: --It does not have. The table only has YEAR TYPE PERIOD
2863: --Confdriles is ''
2864: --TablaOri is ''
2865: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2866: bsc_mo_helper_pkg.writeTmp(' ');
2867: bsc_mo_helper_pkg.writeTmp('Adding colBasicaTablas, Basica is ');
2868: bsc_mo_helper_pkg.write_this(Basica);
2869: END IF;
2950: --Name
2951: --Bug 3108495 If the indicator has several dimensions (more that 10 independent) the
2952: --name of the table results too long
2953: Basica.Name := TableNameStart || Indicator.Code || '_' || Configuration || '_' || iPoints;
2954: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2955: bsc_mo_helper_pkg.writeTmp( ' ');
2956: bsc_mo_helper_pkg.writeTmp( ' ');
2957: END IF;
2958: --ConfDriles
2970: --BSC_MO_HELPER_PKG.insertBasicTable(Basica, Basic_Keys, colDataColumns);
2971: END LOOP;
2972: END IF;
2973: bsc_mo_helper_pkg.writeTmp( 'Compl GetBasicTables, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
2974: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2975: bsc_mo_helper_pkg.writeTmp( 'Returning colBasicaTablas as ');
2976: bsc_mo_helper_pkg.write_this(colBasicaTablas);
2977: END IF;
2978: return colBasicaTablas;
2997: -- Creates the record for the internal column in BSC_DB_MEASURE_COLS_TL
2998: --
2999: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3000: --***************************************************************************
3001: PROCEDURE InsertInDBMeasureCols(p_measure IN BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV) IS
3002:
3003: l_stmt VARCHAR2(1000);
3004: i NUMBER;
3005:
3003: l_stmt VARCHAR2(1000);
3004: i NUMBER;
3005:
3006: BEGIN
3007: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3008: bsc_mo_helper_pkg.writeTmp( 'Inside InsertInDBMeasureCols, p_measure = ');
3009: END IF;
3010:
3011: bsc_mo_helper_pkg.write_this(p_measure);
3013: l_stmt := 'DELETE FROM BSC_DB_MEASURE_COLS_TL WHERE MEASURE_COL = :1';
3014: EXECUTE IMMEDIATE l_stmt using p_measure.fieldName;
3015:
3016: --Because it is a TL table, we need to insert the record for every supported language
3017: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.first;
3018:
3019: LOOP
3020: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.count = 0;
3021: INSERT INTO BSC_DB_MEASURE_COLS_TL (
3016: --Because it is a TL table, we need to insert the record for every supported language
3017: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.first;
3018:
3019: LOOP
3020: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.count = 0;
3021: INSERT INTO BSC_DB_MEASURE_COLS_TL (
3022: MEASURE_COL, LANGUAGE, SOURCE_LANG,
3023: HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
3024: VALUES (p_measure.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
3020: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.count = 0;
3021: INSERT INTO BSC_DB_MEASURE_COLS_TL (
3022: MEASURE_COL, LANGUAGE, SOURCE_LANG,
3023: HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
3024: VALUES (p_measure.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
3025: p_measure.Description, p_measure.groupCode, p_measure.prjMethod,p_measure.measureType );
3026: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.last;
3027: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.next(i);
3028: END LOOP;
3022: MEASURE_COL, LANGUAGE, SOURCE_LANG,
3023: HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
3024: VALUES (p_measure.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
3025: p_measure.Description, p_measure.groupCode, p_measure.prjMethod,p_measure.measureType );
3026: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.last;
3027: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.next(i);
3028: END LOOP;
3029: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3030: bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');
3023: HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
3024: VALUES (p_measure.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i), BSC_METADATA_OPTIMIZER_PKG.gLangCode,
3025: p_measure.Description, p_measure.groupCode, p_measure.prjMethod,p_measure.measureType );
3026: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.last;
3027: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.next(i);
3028: END LOOP;
3029: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3030: bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');
3031: END IF;
3025: p_measure.Description, p_measure.groupCode, p_measure.prjMethod,p_measure.measureType );
3026: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.last;
3027: i := BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages.next(i);
3028: END LOOP;
3029: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3030: bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');
3031: END IF;
3032:
3033:
3051: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3052: --***************************************************************************
3053: PROCEDURE AddInternalColumnInDB(internalColumn IN VARCHAR2, InternalColumnType NUMBER,
3054: baseColumns IN dbms_sql.varchar2_table , numBaseColumns IN NUMBER) IS
3055: l_measure BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV;
3056: i NUMBER;
3057: prjMethod NUMBER;
3058: l_temp number;
3059: BEGIN
3056: i NUMBER;
3057: prjMethod NUMBER;
3058: l_temp number;
3059: BEGIN
3060: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3061: bsc_mo_helper_pkg.writeTmp( 'Inside AddInternalColumnInDB, internalColumn='||internalColumn
3062: ||', InternalColumnType='||InternalColumnType||', numBaseColumns='||numBaseColumns||' baseColumns=');
3063: bsc_mo_helper_pkg.write_this(baseColumns);
3064: END IF;
3064: END IF;
3065:
3066: l_measure.fieldName := internalColumn;
3067: l_measure.source := 'BSC';
3068: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first), 'BSC');
3069:
3070: l_measure.groupCode := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode;
3071: l_measure.Description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
3072:
3066: l_measure.fieldName := internalColumn;
3067: l_measure.source := 'BSC';
3068: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first), 'BSC');
3069:
3070: l_measure.groupCode := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode;
3071: l_measure.Description := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
3072:
3073: IF (InternalColumnType =1) THEN
3074: --Formula
3091: l_measure.prjMethod := 1; --Moving average has the lowest priority
3092: i := baseColumns.first;
3093: LOOP
3094: EXIT WHEN baseColumns.count = 0;
3095: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i), 'BSC');
3096: prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
3097: If prjMethod = 0 Then
3098: --No forecast
3099: l_measure.prjMethod := 0;
3092: i := baseColumns.first;
3093: LOOP
3094: EXIT WHEN baseColumns.count = 0;
3095: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i), 'BSC');
3096: prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
3097: If prjMethod = 0 Then
3098: --No forecast
3099: l_measure.prjMethod := 0;
3100: EXIT;
3121: --2: Balance
3122: i := baseColumns.first;
3123: LOOP
3124: EXIT WHEN baseColumns.count = 0;
3125: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i), 'BSC');
3126: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3127: If l_measure.measureType = 2 Then
3128: EXIT;
3129: END IF;
3122: i := baseColumns.first;
3123: LOOP
3124: EXIT WHEN baseColumns.count = 0;
3125: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(i), 'BSC');
3126: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3127: If l_measure.measureType = 2 Then
3128: EXIT;
3129: END IF;
3130: EXIT WHEN i = baseColumns.last;
3134: --Total and counter for Average at Lowest Level
3135:
3136: --Projection method and type are the same of the base column
3137: --In this case there is only one base column
3138: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first), 'BSC');
3139: l_measure.prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
3140: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3141: END IF;
3142:
3135:
3136: --Projection method and type are the same of the base column
3137: --In this case there is only one base column
3138: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first), 'BSC');
3139: l_measure.prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
3140: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3141: END IF;
3142:
3143: If Not FieldExistsInLoV(internalColumn, 'BSC') Then
3136: --Projection method and type are the same of the base column
3137: --In this case there is only one base column
3138: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, baseColumns(baseColumns.first), 'BSC');
3139: l_measure.prjMethod := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod;
3140: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3141: END IF;
3142:
3143: If Not FieldExistsInLoV(internalColumn, 'BSC') Then
3144: IF (BSC_METADATA_OPTIMIZER_PKG.gLov.count>0) THEN
3140: l_measure.measureType := BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType;
3141: END IF;
3142:
3143: If Not FieldExistsInLoV(internalColumn, 'BSC') Then
3144: IF (BSC_METADATA_OPTIMIZER_PKG.gLov.count>0) THEN
3145: BSC_METADATA_OPTIMIZER_PKG.gLov(BSC_METADATA_OPTIMIZER_PKG.gLov.last+1) := l_measure;
3146: ELSE
3147: BSC_METADATA_OPTIMIZER_PKG.gLov(0) := l_measure;
3148: END IF;
3141: END IF;
3142:
3143: If Not FieldExistsInLoV(internalColumn, 'BSC') Then
3144: IF (BSC_METADATA_OPTIMIZER_PKG.gLov.count>0) THEN
3145: BSC_METADATA_OPTIMIZER_PKG.gLov(BSC_METADATA_OPTIMIZER_PKG.gLov.last+1) := l_measure;
3146: ELSE
3147: BSC_METADATA_OPTIMIZER_PKG.gLov(0) := l_measure;
3148: END IF;
3149: Else
3143: If Not FieldExistsInLoV(internalColumn, 'BSC') Then
3144: IF (BSC_METADATA_OPTIMIZER_PKG.gLov.count>0) THEN
3145: BSC_METADATA_OPTIMIZER_PKG.gLov(BSC_METADATA_OPTIMIZER_PKG.gLov.last+1) := l_measure;
3146: ELSE
3147: BSC_METADATA_OPTIMIZER_PKG.gLov(0) := l_measure;
3148: END IF;
3149: Else
3150: --Update the filed with the new information
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3147: BSC_METADATA_OPTIMIZER_PKG.gLov(0) := l_measure;
3148: END IF;
3149: Else
3150: --Update the filed with the new information
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3152: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode := l_measure.groupCode;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3148: END IF;
3149: Else
3150: --Update the filed with the new information
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3152: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode := l_measure.groupCode;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3156: END IF;
3149: Else
3150: --Update the filed with the new information
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3152: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode := l_measure.groupCode;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3156: END IF;
3157: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3150: --Update the filed with the new information
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3152: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode := l_measure.groupCode;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3156: END IF;
3157: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3158: bsc_mo_helper_pkg.writeTmp( 'Going to InsertInDBMeasureCols');
3151: l_temp := bsc_mo_helper_pkg.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, internalColumn, 'BSC');
3152: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).groupCode := l_measure.groupCode;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3156: END IF;
3157: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3158: bsc_mo_helper_pkg.writeTmp( 'Going to InsertInDBMeasureCols');
3159: END IF;
3153: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description := l_measure.Description;
3154: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).measureType := l_measure.measureType;
3155: BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).prjMethod := l_measure.prjMethod;
3156: END IF;
3157: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3158: bsc_mo_helper_pkg.writeTmp( 'Going to InsertInDBMeasureCols');
3159: END IF;
3160:
3161: InsertInDBMeasureCols( l_measure);
3159: END IF;
3160:
3161: InsertInDBMeasureCols( l_measure);
3162:
3163: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3164: bsc_mo_helper_pkg.writeTmp( 'Compl AddInternalColumnInDB');
3165: END IF;
3166:
3167:
3167:
3168: EXCEPTION WHEN OTHERS THEN
3169: g_error := sqlerrm;
3170: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in AddInternalColumnInDB : '||g_error||', l_temp='||l_temp||', baseColumns(baseColumns.first)='||baseColumns(baseColumns.first)||' list of values is ');
3171: BSC_MO_HELPER_PKG.write_this(BSC_METADATA_OPTIMIZER_PKG.gLov, FND_LOG.LEVEL_EXCEPTION, true);
3172: raise;
3173: End;
3174:
3175:
3225:
3226: cRow c1%ROWTYPE;
3227:
3228: BEGIN
3229: OPEN c1(BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3230: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3231: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER,
3232: dataColumn);
3233: FETCH c1 INto cRow;
3226: cRow c1%ROWTYPE;
3227:
3228: BEGIN
3229: OPEN c1(BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3230: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3231: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER,
3232: dataColumn);
3233: FETCH c1 INto cRow;
3234: If c1%NOTFOUND Then
3227:
3228: BEGIN
3229: OPEN c1(BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3230: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3231: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER,
3232: dataColumn);
3233: FETCH c1 INto cRow;
3234: If c1%NOTFOUND Then
3235: FuncAgreg := null;
3284: -- colMeasures: collection
3285: -- measure: field name
3286: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3287: --***************************************************************************
3288: Function dataFieldExists(colMeasures IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField, measure IN VARCHAR2)
3289: RETURN BOOLEAN IS
3290: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3291: i NUMBER;
3292:
3286: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3287: --***************************************************************************
3288: Function dataFieldExists(colMeasures IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField, measure IN VARCHAR2)
3289: RETURN BOOLEAN IS
3290: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3291: i NUMBER;
3292:
3293: BEGIn
3294:
3322: -- colMeasures: collection
3323: -- measure: field name
3324: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3325: --***************************************************************************
3326: Function dataFieldExistsForSource(colMeasures IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField,
3327: measure IN VARCHAR2,
3328: p_source IN VARCHAR2
3329: )
3330: RETURN BOOLEAN IS
3327: measure IN VARCHAR2,
3328: p_source IN VARCHAR2
3329: )
3330: RETURN BOOLEAN IS
3331: l_measure BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3332: i NUMBER;
3333: BEGIn
3334: IF colMeasures.count = 0 THEN
3335: return FALSE;
3373: BEGIN
3374:
3375: cExpresion := Expresion;
3376: --Replace the operators by ' '
3377: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.first;
3378:
3379: LOOP
3380: cExpresion := Replace(cExpresion, BSC_METADATA_OPTIMIZER_PKG.gReservedOperators(i), ' ');
3381: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.last;
3376: --Replace the operators by ' '
3377: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.first;
3378:
3379: LOOP
3380: cExpresion := Replace(cExpresion, BSC_METADATA_OPTIMIZER_PKG.gReservedOperators(i), ' ');
3381: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.last;
3382: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.next(i);
3383: END LOOP;
3384:
3377: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.first;
3378:
3379: LOOP
3380: cExpresion := Replace(cExpresion, BSC_METADATA_OPTIMIZER_PKG.gReservedOperators(i), ' ');
3381: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.last;
3382: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.next(i);
3383: END LOOP;
3384:
3385: --Break down the expression which is separated by ' '
3378:
3379: LOOP
3380: cExpresion := Replace(cExpresion, BSC_METADATA_OPTIMIZER_PKG.gReservedOperators(i), ' ');
3381: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.last;
3382: i := BSC_METADATA_OPTIMIZER_PKG.gReservedOperators.next(i);
3383: END LOOP;
3384:
3385: --Break down the expression which is separated by ' '
3386:
3390: i:= Campos.first;
3391: LOOP
3392: EXIT WHEN Campos.count = 0;
3393: If Campos(i) IS NOT NULL Then
3394: If BSC_MO_HELPER_PKG.FindIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gReservedFunctions, Campos(i)) = -1 Then
3395: --The word campos(i) is not a reserved function
3396: If UPPER(Campos(i)) <> 'NULL' Then
3397: --the word is not 'NULL'
3398: If Not BSC_MO_HELPER_PKG.IsNumber(Campos(i)) Then
3426: -- Configuration: configuration
3427: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3428: --***************************************************************************
3429:
3430: PROCEDURE clearDataField(dataField IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsDataField) IS
3431: dataField_null BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3432: BEGIN
3433: dataField:=dataField_null ;
3434: END;
3427: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3428: --***************************************************************************
3429:
3430: PROCEDURE clearDataField(dataField IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsDataField) IS
3431: dataField_null BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3432: BEGIN
3433: dataField:=dataField_null ;
3434: END;
3435:
3446: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3447: --***************************************************************************
3448:
3449: Function GetDataFields(Indic IN NUMBER, Configuration IN NUMBER, WithInternalColumns IN Boolean)
3450: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
3451:
3452: l_stmt VARCHAR2(1000);
3453: l_measure_name varchar2(1000);
3454: l_measure_names_list dbms_sql.varchar2_table;
3453: l_measure_name varchar2(1000);
3454: l_measure_names_list dbms_sql.varchar2_table;
3455: l_num_measures NUMBER;
3456: FuncAgreg varchar2(1000);
3457: l_measure_column BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3458: l_col_measure_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
3459: TenerEnCuentaCampo NUMBER;
3460: i NUMBER;
3461: msg VARCHAR2(1000);
3454: l_measure_names_list dbms_sql.varchar2_table;
3455: l_num_measures NUMBER;
3456: FuncAgreg varchar2(1000);
3457: l_measure_column BSC_METADATA_OPTIMIZER_PKG.clsDataField;
3458: l_col_measure_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
3459: TenerEnCuentaCampo NUMBER;
3460: i NUMBER;
3461: msg VARCHAR2(1000);
3462:
3476: BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
3477: BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER '||
3478: -- BSC Autogen
3479: ', nvl(M.SOURCE, ''BSC'')
3480: FROM BSC_SYS_MEASURES M, '||BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table||' I
3481: WHERE I.MEASURE_ID = M.MEASURE_ID
3482: AND I.DIM_SET_ID = :5
3483: AND I.INDICATOR = :6
3484: AND M.TYPE = 0';
3507: l_source := null;
3508: -- BSC Autogen, comment below no longer valid
3509: --BSC-PMF Integration: Even though a PMF measure cannot be present in a BSC
3510: --dimension set, I am going to do the validation to filter out PMF measures
3511: OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
3512: BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3513: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3514: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, Configuration, Indic;
3515: LOOP
3508: -- BSC Autogen, comment below no longer valid
3509: --BSC-PMF Integration: Even though a PMF measure cannot be present in a BSC
3510: --dimension set, I am going to do the validation to filter out PMF measures
3511: OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
3512: BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3513: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3514: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, Configuration, Indic;
3515: LOOP
3516: FETCH cv INTO L_MEASURE_COL, L_OPER, L_PFORMULASOURCE, L_PAVGL, L_PAVGLTOTAL, L_PAVGLCOUNTER, l_source ;
3509: --BSC-PMF Integration: Even though a PMF measure cannot be present in a BSC
3510: --dimension set, I am going to do the validation to filter out PMF measures
3511: OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
3512: BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3513: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3514: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, Configuration, Indic;
3515: LOOP
3516: FETCH cv INTO L_MEASURE_COL, L_OPER, L_PFORMULASOURCE, L_PAVGL, L_PAVGLTOTAL, L_PAVGLCOUNTER, l_source ;
3517: EXIT WHEN cv%NOTFOUND;
3510: --dimension set, I am going to do the validation to filter out PMF measures
3511: OPEN cv for l_stmt2 USING BSC_METADATA_OPTIMIZER_PKG.C_PFORMULASOURCE,
3512: BSC_METADATA_OPTIMIZER_PKG.C_PAVGL,
3513: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL,
3514: BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, Configuration, Indic;
3515: LOOP
3516: FETCH cv INTO L_MEASURE_COL, L_OPER, L_PFORMULASOURCE, L_PAVGL, L_PAVGLTOTAL, L_PAVGLCOUNTER, l_source ;
3517: EXIT WHEN cv%NOTFOUND;
3518: FuncAgreg := L_OPER;
3562: baseColumn(0) := l_measure_names_list(i);
3563: If AvgLTotalColumn IS NULL Then
3564: AvgLTotalColumn := getNextInternalColumnName;
3565: --Update the measure property pAvgLTotal in the database
3566: SetMeasurePropertyDB (l_measure_names_list(i), BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL, AvgLTotalColumn);
3567: END IF;
3568: l_measure_column.AvgLTotalColumn := AvgLTotalColumn;
3569: AddInternalColumnInDB(AvgLTotalColumn, 2, baseColumn, 1);
3570: If AvgLCounterColumn IS NULL Then
3569: AddInternalColumnInDB(AvgLTotalColumn, 2, baseColumn, 1);
3570: If AvgLCounterColumn IS NULL Then
3571: AvgLCounterColumn := getNextInternalColumnName;
3572: --Update the measure property pAvgLCounter in the database
3573: SetMeasurePropertyDB (l_measure_names_list(i), BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, AvgLCounterColumn);
3574: END IF;
3575: l_measure_column.AvgLCounterColumn := AvgLCounterColumn;
3576: AddInternalColumnInDB(AvgLCounterColumn, 3, baseColumn, 1);
3577: END IF;
3616: fnd_message.set_token('MEASURE_NAME', l_measure_names_list(i));
3617: g_error := fnd_message.get;
3618: bsc_mo_helper_pkg.writeTmp('ERROR BSC_PMA_OPT_DUP_MEASURE(Duplicate measure names) : '||g_error, FND_LOG.LEVEL_EXCEPTION, true);
3619: bsc_mo_helper_pkg.terminateWithMsg(g_error);
3620: raise bsc_metadata_optimizer_pkg.optimizer_exception ;
3621: EXIT ;
3622: END IF;
3623: END IF;
3624: Else
3625: fnd_message.set_name('BSC', 'BSC_FIELDNME_NOT_REGISTERED');
3626: fnd_message.set_token('FIELD_NAME', l_measure_names_list(i));
3627: fnd_message.set_token('INDICATOR_CODE', Indic);
3628: g_error := fnd_message.get;
3629: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3630: bsc_mo_helper_pkg.writeTmp(g_error);
3631: bsc_mo_helper_pkg.writeTmp('ERROR : BSC_FIELDNME_NOT_REGISTERED : '||g_error);
3632: END IF;
3633: bsc_mo_helper_pkg.terminateWithMsg(g_error);
3630: bsc_mo_helper_pkg.writeTmp(g_error);
3631: bsc_mo_helper_pkg.writeTmp('ERROR : BSC_FIELDNME_NOT_REGISTERED : '||g_error);
3632: END IF;
3633: bsc_mo_helper_pkg.terminateWithMsg(g_error);
3634: raise bsc_metadata_optimizer_pkg.optimizer_exception ;
3635: EXIT ;
3636: END IF;
3637: END LOOP;
3638: --Now add internal column if the formula needs to calculated in another column
3653: --BSC_DB_MEASURES_COLS_TL table
3654: If pAvgLTotal IS NULL Then
3655: pAvgLTotal := getNextInternalColumnName ;
3656: --Update the measure property pAvgLTotal in the database
3657: SetMeasurePropertyDB( l_measure_name, BSC_METADATA_OPTIMIZER_PKG.C_PAVGLTOTAL, pAvgLTotal);
3658: END IF;
3659: AddInternalColumnInDB(pAvgLTotal, 2, l_measure_names_list, l_num_measures);
3660: l_measure_column.AvgLTotalColumn := pAvgLTotal;
3661: If pAvgLCounter IS NULL Then
3660: l_measure_column.AvgLTotalColumn := pAvgLTotal;
3661: If pAvgLCounter IS NULL Then
3662: pAvgLCounter := getNextInternalColumnName;
3663: --Update the measure property pAvgLTotal in the database
3664: SetMeasurePropertyDB( l_measure_name, BSC_METADATA_OPTIMIZER_PKG.C_PAVGLCOUNTER, pAvgLCounter);
3665: END IF;
3666: AddInternalColumnInDB( pAvgLCounter, 3, l_measure_names_list, l_num_measures);
3667: l_measure_column.AvgLCounterColumn := pAvgLCounter;
3668: END IF;
3811: LOOP
3812: EXIT WHEN Combination1.count =0;
3813: str1 := Combination1(j);
3814: --It is not a combination if there is at least one 1n relation between two drills
3815: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, str1);
3816: l_temp2 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, combo(0));
3817: If (l_temp1>=0 AND l_temp2>=0 AND
3818: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp1).Name,
3819: BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp2).Name) = -1 )Then
3812: EXIT WHEN Combination1.count =0;
3813: str1 := Combination1(j);
3814: --It is not a combination if there is at least one 1n relation between two drills
3815: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, str1);
3816: l_temp2 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, combo(0));
3817: If (l_temp1>=0 AND l_temp2>=0 AND
3818: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp1).Name,
3819: BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp2).Name) = -1 )Then
3820: Combination( Combination.count) := str1;
3814: --It is not a combination if there is at least one 1n relation between two drills
3815: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, str1);
3816: l_temp2 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, combo(0));
3817: If (l_temp1>=0 AND l_temp2>=0 AND
3818: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp1).Name,
3819: BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp2).Name) = -1 )Then
3820: Combination( Combination.count) := str1;
3821: Else
3822: Rel1NExists := True;
3815: l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, str1);
3816: l_temp2 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, combo(0));
3817: If (l_temp1>=0 AND l_temp2>=0 AND
3818: IndexRelation1N(BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp1).Name,
3819: BSC_METADATA_OPTIMIZER_PKG.gMastertable(l_temp2).Name) = -1 )Then
3820: Combination( Combination.count) := str1;
3821: Else
3822: Rel1NExists := True;
3823: Exit;
3853: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
3854: --***************************************************************************
3855:
3856: Function GetLevelCombinations(
3857: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
3858: forTargetLevel IN BOOLEAN)
3859: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations IS
3860:
3861: colDimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
3855:
3856: Function GetLevelCombinations(
3857: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
3858: forTargetLevel IN BOOLEAN)
3859: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations IS
3860:
3861: colDimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
3862: DimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
3863: LevelCombinations BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations;
3857: p_dimension_families IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
3858: forTargetLevel IN BOOLEAN)
3859: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations IS
3860:
3861: colDimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
3862: DimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
3863: LevelCombinations BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations;
3864:
3865: DrilC VARCHAR2(1000);
3858: forTargetLevel IN BOOLEAN)
3859: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations IS
3860:
3861: colDimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
3862: DimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
3863: LevelCombinations BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations;
3864:
3865: DrilC VARCHAR2(1000);
3866: ConfDriles VARCHAR2(1000);
3859: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations IS
3860:
3861: colDimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
3862: DimLevelCombinations BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations;
3863: LevelCombinations BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations;
3864:
3865: DrilC VARCHAR2(1000);
3866: ConfDriles VARCHAR2(1000);
3867:
3864:
3865: DrilC VARCHAR2(1000);
3866: ConfDriles VARCHAR2(1000);
3867:
3868: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
3869:
3870: idril NUMBER;
3871: jDril NUMBER;
3872: numDriles NUMBER;
3885:
3886: l_temp_rel VARCHAR2(100);
3887:
3888: l_groups DBMS_SQL.NUMBER_TABLE;
3889: DimLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
3890: l_level_group_id NUMBER;
3891: l_comb_group_id NUMBER := 0;
3892:
3893: l_dummy NUMBER := 0;
3897:
3898: BEGIN
3899:
3900:
3901: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3902: bsc_mo_helper_pkg.writeTmp( 'Inside GetLevelCombinations, p_dimension_families.count is '||p_dimension_families.count);
3903: END IF;
3904:
3905: bsc_mo_helper_pkg.write_this(p_dimension_families);
3907: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
3908: IF (l_groups.count >0) THEN
3909: l_ct1 := l_groups.first ;
3910: ELSE
3911: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3912: bsc_mo_helper_pkg.writeTmp( 'Compl GetLevelCombinations 0 values');
3913: END IF;
3914:
3915: return colDimLevelCombinations;
3989: l_stack := l_stack || ' GetLevelCombinations - 4.2';
3990: --Add the drills combinations to the list of drills combinations of the family
3991: --whose drills are related to drills which has mn relation with the current drill
3992: --get a set of all posible combinations between the drills which there was mn relationship
3993: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3994: bsc_mo_helper_pkg.writeTmp('Calling GetStrCombinationsMN with :');
3995: bsc_mo_helper_pkg.write_THIS(colRelsMN);
3996: bsc_mo_helper_pkg.writeTmp('......');
3997: END IF;
3996: bsc_mo_helper_pkg.writeTmp('......');
3997: END IF;
3998:
3999: conjCombinacsMN := GetStrCombinationsMN(colRelsMN);
4000: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4001: bsc_mo_helper_pkg.writeTmp('GetStrCombinationsMN returned :');
4002: bsc_mo_helper_pkg.write_THIS(conjCombinacsMN);
4003: bsc_mo_helper_pkg.writeTmp('......');
4004: END IF;
4097: l_ct1 := l_groups.next(l_ct1);
4098:
4099: END LOOP;
4100:
4101: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4102: bsc_mo_helper_pkg.writeTmp( 'Compl GetLevelCombinations');
4103: END IF;
4104:
4105:
4125: --
4126: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4127: --***************************************************************************
4128:
4129: Function get_dimension_family(tabtabDrills IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, dimTable IN VARCHAR2)
4130: return NUMBER IS
4131: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4132: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4133: l_ct NUMBER := 0;
4127: --***************************************************************************
4128:
4129: Function get_dimension_family(tabtabDrills IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, dimTable IN VARCHAR2)
4130: return NUMBER IS
4131: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4132: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4133: l_ct NUMBER := 0;
4134: l_ct2 NUMBER := 0;
4135: l_groups DBMS_SQL.NUMBER_TABLE;
4128:
4129: Function get_dimension_family(tabtabDrills IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels, dimTable IN VARCHAR2)
4130: return NUMBER IS
4131: l_levels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4132: l_level BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4133: l_ct NUMBER := 0;
4134: l_ct2 NUMBER := 0;
4135: l_groups DBMS_SQL.NUMBER_TABLE;
4136: l_dummy NUMBER;
4189: --
4190: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4191: --***************************************************************************
4192: Function GetLevelCollection(Indic IN NUMBER, Configuration IN NUMBER)
4193: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels IS
4194: l_dimension_families BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4195: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4196: cLevel BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4197: tDril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4190: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4191: --***************************************************************************
4192: Function GetLevelCollection(Indic IN NUMBER, Configuration IN NUMBER)
4193: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels IS
4194: l_dimension_families BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4195: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4196: cLevel BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4197: tDril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4198:
4191: --***************************************************************************
4192: Function GetLevelCollection(Indic IN NUMBER, Configuration IN NUMBER)
4193: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels IS
4194: l_dimension_families BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4195: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4196: cLevel BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4197: tDril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4198:
4199: l_parents1n varchar2(1000);
4192: Function GetLevelCollection(Indic IN NUMBER, Configuration IN NUMBER)
4193: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels IS
4194: l_dimension_families BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4195: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4196: cLevel BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4197: tDril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4198:
4199: l_parents1n varchar2(1000);
4200: l_parentsMN varchar2(1000);
4193: RETURN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels IS
4194: l_dimension_families BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4195: DimensionLevels BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels;
4196: cLevel BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4197: tDril BSC_METADATA_OPTIMIZER_PKG.clsLevels;
4198:
4199: l_parents1n varchar2(1000);
4200: l_parentsMN varchar2(1000);
4201: tPadre1n varchar2(1000);
4220: tdril_parentsMN DBMS_SQL.VARCHAR2_TABLE;
4221:
4222:
4223: BEGIN
4224: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4225: bsc_mo_helper_pkg.writeTmp( 'Inside GetLevelCollection, Indic = '||Indic||', Configuration = '||Configuration);
4226: END IF;
4227: l_stmt := 'SELECT DISTINCT DIM_LEVEL_INDEX, LEVEL_TABLE_NAME, LEVEL_PK_COL, NAME, NVL(TARGET_LEVEL,1) AS TAR_LEVEL' ||
4228: ' FROM BSC_KPI_DIM_LEVELS_VL WHERE INDICATOR = :1 AND DIM_SET_ID = :2 AND STATUS = 2';
4242: cLevel.dimTable := l_level_table;
4243: cLevel.Num := l_dim_index;
4244: cLevel.Name := Name;
4245: cLevel.TargetLevel := TargetLevel;
4246: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
4247: bsc_mo_helper_pkg.writeTmp('Considering level '||l_level_table||' checking for relationship to existing levels', 1, false);
4248: END IF;
4249: DimensionLevelsNum := get_dimension_family(l_dimension_families, l_level_table);
4250: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
4246: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
4247: bsc_mo_helper_pkg.writeTmp('Considering level '||l_level_table||' checking for relationship to existing levels', 1, false);
4248: END IF;
4249: DimensionLevelsNum := get_dimension_family(l_dimension_families, l_level_table);
4250: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
4251: bsc_mo_helper_pkg.writeTmp('DimensionLevelsNum = '||DimensionLevelsNum);
4252: END IF;
4253: -- Get the index of the dimension family which this drill belongs to.
4254: If DimensionLevelsNum <> -1 Then
4251: bsc_mo_helper_pkg.writeTmp('DimensionLevelsNum = '||DimensionLevelsNum);
4252: END IF;
4253: -- Get the index of the dimension family which this drill belongs to.
4254: If DimensionLevelsNum <> -1 Then
4255: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
4256: bsc_mo_helper_pkg.writeTmp('Relationship exists');
4257: END IF;
4258: --Level belongs to family DimensionLevelsNum.
4259: --Check each level of this family and see which drill has 1n or mn
4374: l_group_id := l_group_id +1;
4375: END IF;--If DimensionLevelsNum <> 0
4376: END Loop;
4377: close cv;
4378: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4379: bsc_mo_helper_pkg.writeTmp( 'Compl GetLevelCollection');
4380: bsc_mo_helper_pkg.write_this(l_dimension_families);
4381: END IF;
4382: return l_dimension_families;
4402: -- colPeriodicities: collection of clsIndicPeriodicity
4403: --
4404: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4405: --***************************************************************************
4406: PROCEDURE FlagTLOtherPeriodicities(colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity)
4407: IS
4408: atLeastOneChange Boolean;
4409: indicPer BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
4410: l_count NUMBER;
4405: --***************************************************************************
4406: PROCEDURE FlagTLOtherPeriodicities(colPeriodicities IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity)
4407: IS
4408: atLeastOneChange Boolean;
4409: indicPer BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
4410: l_count NUMBER;
4411:
4412: BEGIN
4413:
4410: l_count NUMBER;
4411:
4412: BEGIN
4413:
4414: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4415: bsc_mo_helper_pkg.writeTmp( 'Inside FlagTLOtherPeriodicities, colPeriodicities = ');
4416: bsc_mo_helper_pkg.write_this(colPeriodicities);
4417: END IF;
4418:
4438: l_count := colPeriodicities.next(l_count);
4439: END LOOP;
4440:
4441: END LOOP;
4442: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4443: bsc_mo_helper_pkg.writeTmp( 'Compl FlagTLOtherPeriodicities');
4444: END IF;
4445:
4446:
4460: -- Indic: indicator code
4461: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4462: --***************************************************************************
4463:
4464: Function GetPeriodicities(Indic IN NUMBER) RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity IS
4465:
4466: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4467: CURSOR cPeriodicities IS
4468: SELECT PERIODICITY_ID, NVL(TARGET_LEVEL, 1) AS TAR_LEVEL
4462: --***************************************************************************
4463:
4464: Function GetPeriodicities(Indic IN NUMBER) RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity IS
4465:
4466: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4467: CURSOR cPeriodicities IS
4468: SELECT PERIODICITY_ID, NVL(TARGET_LEVEL, 1) AS TAR_LEVEL
4469: FROM BSC_KPI_PERIODICITIES
4470: WHERE INDICATOR = Indic ORDER BY PERIODICITY_ID;
4470: WHERE INDICATOR = Indic ORDER BY PERIODICITY_ID;
4471: l_per NUMBER;
4472: l_tar NUMBER;
4473: cv CurTyp;
4474: l_periodicity BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity := null;
4475: BEGIN
4476:
4477: OPEN cPeriodicities;
4478: LOOP
4522: -- Indicator: indicator
4523: -- Configuration: configuration
4524: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4525: --***************************************************************************
4526: PROCEDURE ConfigureIndics(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4523: -- Configuration: configuration
4524: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4525: --***************************************************************************
4526: PROCEDURE ConfigureIndics(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4524: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4525: --***************************************************************************
4526: PROCEDURE ConfigureIndics(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4525: --***************************************************************************
4526: PROCEDURE ConfigureIndics(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4533: colDataColumns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4526: PROCEDURE ConfigureIndics(Indicator IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator, Configuration IN NUMBER) IS
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4533: colDataColumns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4534: i NUMBER;
4527: colPeriodicities BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4533: colDataColumns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4534: i NUMBER;
4535: iNext NUMBER;
4528: colDrills BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4533: colDataColumns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4534: i NUMBER;
4535: iNext NUMBER;
4536: bLast boolean;
4529: colDrillCombination BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4530: colDrillCombinationTL BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations;
4531: colBasicTables BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4532: colBasicTablesTL BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable;
4533: colDataColumns BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4534: i NUMBER;
4535: iNext NUMBER;
4536: bLast boolean;
4537: BEGIN
4557: FlagTLOtherPeriodicities (colPeriodicities);
4558: END IF;
4559: --Get the list of drill families of the indicator in the given configuration
4560: colDrills := GetLevelCollection(Indicator.Code, Configuration);
4561: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4562: bsc_mo_helper_pkg.writeTmp(' ');
4563: bsc_mo_helper_pkg.writeTmp('Level Collection is');
4564: bsc_mo_helper_pkg.write_this(colDrills);
4565: END IF;
4563: bsc_mo_helper_pkg.writeTmp('Level Collection is');
4564: bsc_mo_helper_pkg.write_this(colDrills);
4565: END IF;
4566: --Get the list of combinations of levels of each familiy
4567: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4568: bsc_mo_helper_pkg.writeTmp('Get the list of level combinations ');
4569: END IF;
4570: colDrillCombination := GetLevelCombinations(colDrills, False);
4571: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4567: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4568: bsc_mo_helper_pkg.writeTmp('Get the list of level combinations ');
4569: END IF;
4570: colDrillCombination := GetLevelCombinations(colDrills, False);
4571: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4572: bsc_mo_helper_pkg.write_this(colDrillCombination);
4573: bsc_mo_helper_pkg.writetmp(' ');
4574: END IF;
4575: If Indicator.OptimizationMode = 2 Then
4607: --(example for 2 to 3 or 3 to 2), I do not need the tables in gTablas.
4608: --I just wanted to re-configure BSC_KPI_DATA_TABLES and not re-configure loader.
4609: --Remove indicator tables from gTablas
4610: bLast := false;
4611: If (Indicator.Action_Flag = 0 Or Indicator.Action_Flag = 4) And bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 2 Then
4612: i := bsc_metadata_optimizer_pkg.gTables.first;
4613: LOOP
4614: EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4608: --I just wanted to re-configure BSC_KPI_DATA_TABLES and not re-configure loader.
4609: --Remove indicator tables from gTablas
4610: bLast := false;
4611: If (Indicator.Action_Flag = 0 Or Indicator.Action_Flag = 4) And bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 2 Then
4612: i := bsc_metadata_optimizer_pkg.gTables.first;
4613: LOOP
4614: EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4616: bLast := true;
4610: bLast := false;
4611: If (Indicator.Action_Flag = 0 Or Indicator.Action_Flag = 4) And bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 2 Then
4612: i := bsc_metadata_optimizer_pkg.gTables.first;
4613: LOOP
4614: EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4616: bLast := true;
4617: END IF;
4618: If (bsc_metadata_optimizer_pkg.gTables(i).Indicator = Indicator.Code) And
4611: If (Indicator.Action_Flag = 0 Or Indicator.Action_Flag = 4) And bsc_metadata_optimizer_pkg.g_Sum_Level_Change = 2 Then
4612: i := bsc_metadata_optimizer_pkg.gTables.first;
4613: LOOP
4614: EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4616: bLast := true;
4617: END IF;
4618: If (bsc_metadata_optimizer_pkg.gTables(i).Indicator = Indicator.Code) And
4619: (bsc_metadata_optimizer_pkg.gTables(i).Configuration = Configuration) Then
4614: EXIT WHEN bsc_metadata_optimizer_pkg.gTables.count = 0;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4616: bLast := true;
4617: END IF;
4618: If (bsc_metadata_optimizer_pkg.gTables(i).Indicator = Indicator.Code) And
4619: (bsc_metadata_optimizer_pkg.gTables(i).Configuration = Configuration) Then
4620: IF (NOT bLast) THEN
4621: iNext := bsc_metadata_optimizer_pkg.gTables.next(i);
4622: END IF;
4615: IF (i = bsc_metadata_optimizer_pkg.gTables.last) THEN
4616: bLast := true;
4617: END IF;
4618: If (bsc_metadata_optimizer_pkg.gTables(i).Indicator = Indicator.Code) And
4619: (bsc_metadata_optimizer_pkg.gTables(i).Configuration = Configuration) Then
4620: IF (NOT bLast) THEN
4621: iNext := bsc_metadata_optimizer_pkg.gTables.next(i);
4622: END IF;
4623: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4617: END IF;
4618: If (bsc_metadata_optimizer_pkg.gTables(i).Indicator = Indicator.Code) And
4619: (bsc_metadata_optimizer_pkg.gTables(i).Configuration = Configuration) Then
4620: IF (NOT bLast) THEN
4621: iNext := bsc_metadata_optimizer_pkg.gTables.next(i);
4622: END IF;
4623: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4624: BSC_MO_HELPER_PKG.writeTmp('2 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
4625: END IF;
4619: (bsc_metadata_optimizer_pkg.gTables(i).Configuration = Configuration) Then
4620: IF (NOT bLast) THEN
4621: iNext := bsc_metadata_optimizer_pkg.gTables.next(i);
4622: END IF;
4623: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4624: BSC_MO_HELPER_PKG.writeTmp('2 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
4625: END IF;
4626: bsc_metadata_optimizer_pkg.gTables.delete(i);
4627: i := iNext;
4620: IF (NOT bLast) THEN
4621: iNext := bsc_metadata_optimizer_pkg.gTables.next(i);
4622: END IF;
4623: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4624: BSC_MO_HELPER_PKG.writeTmp('2 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
4625: END IF;
4626: bsc_metadata_optimizer_pkg.gTables.delete(i);
4627: i := iNext;
4628: ELSE
4622: END IF;
4623: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4624: BSC_MO_HELPER_PKG.writeTmp('2 Going to delete '||BSC_METADATA_OPTIMIZER_PKG.gTables(i).name);
4625: END IF;
4626: bsc_metadata_optimizer_pkg.gTables.delete(i);
4627: i := iNext;
4628: ELSE
4629: i := bsc_metadata_optimizer_pkg.gTables.next(i);
4630: END IF;
4625: END IF;
4626: bsc_metadata_optimizer_pkg.gTables.delete(i);
4627: i := iNext;
4628: ELSE
4629: i := bsc_metadata_optimizer_pkg.gTables.next(i);
4630: END IF;
4631: EXIT WHEN bLast = true;
4632: END Loop;
4633: End If;
4653: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4654: --***************************************************************************
4655: Function GetColConfigForIndic(Indic IN NUMBER) return DBMS_SQL.NUMBER_TABLE IS
4656: colConfigurations dbms_sql.number_table;
4657: colMeasures BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
4658: Configuration NUMBER;
4659: DimSet NUMBER;
4660: CURSOR cConfigs IS
4661: SELECT DISTINCT DIM_SET_ID FROM BSC_DB_DATASET_DIM_SETS_V
4690: --
4691: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4692: --***************************************************************************
4693: PROCEDURE IndicatorTables IS
4694: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
4695: colConfigurations dbms_sql.number_table;
4696: l_Configuration Number;
4697: l_stmt VARCHAR2(1000);
4698: l_count number := 0;
4698: l_count number := 0;
4699: l_configs Number := 0;
4700: l_list dbms_sql.number_table;
4701: BEGIN
4702: bsc_mo_helper_pkg.writeTmp( 'Inside IndicatorTables, # = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count, fnd_log.level_statement, true);
4703: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators.count >0 THEN
4704: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
4705: END IF;
4706:
4699: l_configs Number := 0;
4700: l_list dbms_sql.number_table;
4701: BEGIN
4702: bsc_mo_helper_pkg.writeTmp( 'Inside IndicatorTables, # = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count, fnd_log.level_statement, true);
4703: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators.count >0 THEN
4704: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
4705: END IF;
4706:
4707: --Perf. fix, instead of getting # of data columns for each indicator, get it in one shot
4700: l_list dbms_sql.number_table;
4701: BEGIN
4702: bsc_mo_helper_pkg.writeTmp( 'Inside IndicatorTables, # = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count, fnd_log.level_statement, true);
4703: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators.count >0 THEN
4704: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
4705: END IF;
4706:
4707: --Perf. fix, instead of getting # of data columns for each indicator, get it in one shot
4708: FOR i IN BSC_METADATA_OPTIMIZER_PKG.gIndicators.first..BSC_METADATA_OPTIMIZER_PKG.gIndicators.last LOOP
4704: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
4705: END IF;
4706:
4707: --Perf. fix, instead of getting # of data columns for each indicator, get it in one shot
4708: FOR i IN BSC_METADATA_OPTIMIZER_PKG.gIndicators.first..BSC_METADATA_OPTIMIZER_PKG.gIndicators.last LOOP
4709: --Consider only new indicators or changed indicators
4710: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag = 3
4711: Or
4712: (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
4706:
4707: --Perf. fix, instead of getting # of data columns for each indicator, get it in one shot
4708: FOR i IN BSC_METADATA_OPTIMIZER_PKG.gIndicators.first..BSC_METADATA_OPTIMIZER_PKG.gIndicators.last LOOP
4709: --Consider only new indicators or changed indicators
4710: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag = 3
4711: Or
4712: (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
4713: l_list(l_list.count+1) := BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
4714: END IF;
4708: FOR i IN BSC_METADATA_OPTIMIZER_PKG.gIndicators.first..BSC_METADATA_OPTIMIZER_PKG.gIndicators.last LOOP
4709: --Consider only new indicators or changed indicators
4710: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag = 3
4711: Or
4712: (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
4713: l_list(l_list.count+1) := BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
4714: END IF;
4715: END LOOP;
4716: init_measure_counts(l_list);
4709: --Consider only new indicators or changed indicators
4710: IF BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag = 3
4711: Or
4712: (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
4713: l_list(l_list.count+1) := BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
4714: END IF;
4715: END LOOP;
4716: init_measure_counts(l_list);
4717: LOOP
4714: END IF;
4715: END LOOP;
4716: init_measure_counts(l_list);
4717: LOOP
4718: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gIndicators.count = 0;
4719: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_count);
4720: bsc_mo_helper_pkg.writeTmp('Processing indic ');
4721: bsc_mo_helper_pkg.write_this(Indicator);
4722: --Consider only new indicators or changed indicators
4715: END LOOP;
4716: init_measure_counts(l_list);
4717: LOOP
4718: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gIndicators.count = 0;
4719: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_count);
4720: bsc_mo_helper_pkg.writeTmp('Processing indic ');
4721: bsc_mo_helper_pkg.write_this(Indicator);
4722: --Consider only new indicators or changed indicators
4723: -- Note: ANy logic change shd be propagated to the init above
4722: --Consider only new indicators or changed indicators
4723: -- Note: ANy logic change shd be propagated to the init above
4724: IF Indicator.Action_Flag = 3
4725: Or
4726: (Indicator.Action_Flag <> 2 And BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0) THEN
4727: --Get the list of configurations of the kpi
4728: colConfigurations := GetColConfigForIndic(Indicator.Code);
4729: l_configs := colConfigurations.first;
4730: bsc_mo_helper_pkg.writeTmp('colConfigurations.count = '||colConfigurations.count);
4736: EXIT WHEN l_configs = colConfigurations.last;
4737: l_configs := colConfigurations.next(l_configs);
4738: END LOOP;
4739: --BSC-MV Note: Save the summarization level in BSC_KPI_PROPERTIES
4740: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
4741: bsc_mo_helper_pkg.WriteInfoMatrix(Indicator.Code, 'ADV_SUM_LEVEL',
4742: to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level));
4743: End If;
4744: END IF;
4738: END LOOP;
4739: --BSC-MV Note: Save the summarization level in BSC_KPI_PROPERTIES
4740: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
4741: bsc_mo_helper_pkg.WriteInfoMatrix(Indicator.Code, 'ADV_SUM_LEVEL',
4742: to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level));
4743: End If;
4744: END IF;
4745: EXIT when l_count = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
4746: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_count) ;
4741: bsc_mo_helper_pkg.WriteInfoMatrix(Indicator.Code, 'ADV_SUM_LEVEL',
4742: to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level));
4743: End If;
4744: END IF;
4745: EXIT when l_count = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
4746: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_count) ;
4747: END LOOP;
4748: --Configure shared indicators without filters same tables as master indicator
4749: ConfigureMasterSharedIndics;
4742: to_number(BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level));
4743: End If;
4744: END IF;
4745: EXIT when l_count = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
4746: l_count := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_count) ;
4747: END LOOP;
4748: --Configure shared indicators without filters same tables as master indicator
4749: ConfigureMasterSharedIndics;
4750: bsc_mo_helper_pkg.writeTmp( 'Compl IndicatorTables', fnd_log.level_statement, true);