[Home] [Help]
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;
35:
34: return PTName;
35:
36: EXCEPTION WHEN OTHERS THEN
37: g_error := sqlerrm;
38: bsc_mo_helper_pkg.TerminateWithMsg( 'Exception in GetProjectionTableName : '||g_error);
39: raise;
40: End ;
41: --****************************************************************************
42: --GetFreeDivZeroExpression
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;
62: EXCEPTION WHEN OTHERS THEN
60:
61: return l_res;
62: EXCEPTION WHEN OTHERS THEN
63: g_error := sqlerrm;
64: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetFreeDivZeroExpression :' ||g_error);
65: raise;
66: End;
67:
68: --****************************************************************************
76: l_level NUMBER;
77: sourceTable VARCHAR2(100);
78: l_index NUMBER;
79: BEGIN
80: l_index := BSC_MO_HELPER_PKG.findIndex(colTables, TableName);
81: sourceTable := colTables(l_index).originTable;
82: If sourceTable IS NOT NULL Then
83: l_level := 1 + getTableLevel(sourceTable, colTables);
84: Else
86: End If;
87: return l_level;
88: EXCEPTION WHEN OTHERS THEN
89: g_error := sqlerrm;
90: bsc_mo_helper_pkg.writeTmp('Exception, tableName='||TableName||' colTables=', FND_LOG.LEVEL_EXCEPTION, true);
91: bsc_mo_helper_pkg.write_this(colTables, FND_LOG.LEVEL_EXCEPTION, true);
92: bsc_mo_helper_pkg.TerminateWithMsg('Exception in getTableLevel : '||g_error);
93: raise;
94: End;
87: return l_level;
88: EXCEPTION WHEN OTHERS THEN
89: g_error := sqlerrm;
90: bsc_mo_helper_pkg.writeTmp('Exception, tableName='||TableName||' colTables=', FND_LOG.LEVEL_EXCEPTION, true);
91: bsc_mo_helper_pkg.write_this(colTables, FND_LOG.LEVEL_EXCEPTION, true);
92: bsc_mo_helper_pkg.TerminateWithMsg('Exception in getTableLevel : '||g_error);
93: raise;
94: End;
95:
88: EXCEPTION WHEN OTHERS THEN
89: g_error := sqlerrm;
90: bsc_mo_helper_pkg.writeTmp('Exception, tableName='||TableName||' colTables=', FND_LOG.LEVEL_EXCEPTION, true);
91: bsc_mo_helper_pkg.write_this(colTables, FND_LOG.LEVEL_EXCEPTION, true);
92: bsc_mo_helper_pkg.TerminateWithMsg('Exception in getTableLevel : '||g_error);
93: raise;
94: End;
95:
96: --***************************************************************************
118: BEGIN
119: IF (p_dimension_families.count =0) THEN
120: return -1;
121: END IF;
122: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
123: iDimensionLevels := l_groups.first;
124: LOOP
125: EXIT WHEN l_groups.count = 0;
126: l_group_id := l_groups(iDimensionLevels);
123: iDimensionLevels := l_groups.first;
124: LOOP
125: EXIT WHEN l_groups.count = 0;
126: l_group_id := l_groups(iDimensionLevels);
127: DimensionLevels := BSC_MO_HELPER_PKG.get_tab_clsLevels (p_dimension_families, l_group_id);
128: IF (DimensionLevels.count >0) THEN
129: i := DimensionLevels.first;
130: LOOP
131: Dril := DimensionLevels(i);
141: END LOOP;
142: return -1;
143: EXCEPTION WHEN OTHERS THEN
144: g_error := sqlerrm;
145: bsc_mo_helper_pkg.writeTmp('Exception, p_key='||p_key||', Dimension families=', FND_LOG.LEVEL_EXCEPTION, true);
146: bsc_mo_helper_pkg.write_this(p_dimension_families, FND_LOG.LEVEL_EXCEPTION, true);
147: bsc_mo_helper_pkg.TerminateWithMsg('Exception in FindDimensionGroupIndexForKey : '||g_error);
148: raise;
149: End;
142: return -1;
143: EXCEPTION WHEN OTHERS THEN
144: g_error := sqlerrm;
145: bsc_mo_helper_pkg.writeTmp('Exception, p_key='||p_key||', Dimension families=', FND_LOG.LEVEL_EXCEPTION, true);
146: bsc_mo_helper_pkg.write_this(p_dimension_families, FND_LOG.LEVEL_EXCEPTION, true);
147: bsc_mo_helper_pkg.TerminateWithMsg('Exception in FindDimensionGroupIndexForKey : '||g_error);
148: raise;
149: End;
150:
143: EXCEPTION WHEN OTHERS THEN
144: g_error := sqlerrm;
145: bsc_mo_helper_pkg.writeTmp('Exception, p_key='||p_key||', Dimension families=', FND_LOG.LEVEL_EXCEPTION, true);
146: bsc_mo_helper_pkg.write_this(p_dimension_families, FND_LOG.LEVEL_EXCEPTION, true);
147: bsc_mo_helper_pkg.TerminateWithMsg('Exception in FindDimensionGroupIndexForKey : '||g_error);
148: raise;
149: End;
150:
151: FUNCTION get_n_parents(p_s_table IN VARCHAR2,
160: BEGIN
161: l_n_parents(0) := p_s_table;
162: l_table_name := p_s_table;
163: FOR i IN 1..p_level_num LOOP
164: l_index := BSC_MO_HELPER_PKG.findIndex(p_s_table_list, l_table_name);
165: l_table_name := p_s_table_list(l_index).originTable;
166: IF (l_table_name IS NULL) THEN
167: return l_n_parents;
168: END IF;
170: END LOOP;
171: return l_n_parents;
172: EXCEPTION WHEN OTHERS THEN
173: g_error := sqlerrm;
174: bsc_mo_helper_pkg.writeTmp('Exception, p_s_table='||p_s_table||', p_s_table_list=', FND_LOG.LEVEL_EXCEPTION, true);
175: bsc_mo_helper_pkg.write_this(p_s_table_list, FND_LOG.LEVEL_EXCEPTION, true);
176: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_n_parents : '||g_error);
177: raise;
178:
171: return l_n_parents;
172: EXCEPTION WHEN OTHERS THEN
173: g_error := sqlerrm;
174: bsc_mo_helper_pkg.writeTmp('Exception, p_s_table='||p_s_table||', p_s_table_list=', FND_LOG.LEVEL_EXCEPTION, true);
175: bsc_mo_helper_pkg.write_this(p_s_table_list, FND_LOG.LEVEL_EXCEPTION, true);
176: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_n_parents : '||g_error);
177: raise;
178:
179: END;
172: EXCEPTION WHEN OTHERS THEN
173: g_error := sqlerrm;
174: bsc_mo_helper_pkg.writeTmp('Exception, p_s_table='||p_s_table||', p_s_table_list=', FND_LOG.LEVEL_EXCEPTION, true);
175: bsc_mo_helper_pkg.write_this(p_s_table_list, FND_LOG.LEVEL_EXCEPTION, true);
176: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_n_parents : '||g_error);
177: raise;
178:
179: END;
180:
212: l_index NUMBER;
213: l_level_table_high VARCHAR2(100);
214: l_level_table_low VARCHAR2(100);
215: BEGIN
216: bsc_mo_helper_pkg.writeTmp('In Find join betweeen levels, p_key='||p_key.keyName||', p_zmv_key='||p_zmv_key.keyName, FND_LOG.LEVEL_STATEMENT, false);
217: l_dim_group_index := FindDimensionGroupIndexForKey(p_dimensions, p_key.KeyName);
218: l_dimension_levels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_dimensions, l_dim_group_index) ;
219: -- Find the dimension level for the lower level key
220: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_key.KeyName);
214: l_level_table_low VARCHAR2(100);
215: BEGIN
216: bsc_mo_helper_pkg.writeTmp('In Find join betweeen levels, p_key='||p_key.keyName||', p_zmv_key='||p_zmv_key.keyName, FND_LOG.LEVEL_STATEMENT, false);
217: l_dim_group_index := FindDimensionGroupIndexForKey(p_dimensions, p_key.KeyName);
218: l_dimension_levels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_dimensions, l_dim_group_index) ;
219: -- Find the dimension level for the lower level key
220: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_key.KeyName);
221: l_level_table_high := l_dimension_levels(l_index).dimTable;
222: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_zmv_key.KeyName);
216: bsc_mo_helper_pkg.writeTmp('In Find join betweeen levels, p_key='||p_key.keyName||', p_zmv_key='||p_zmv_key.keyName, FND_LOG.LEVEL_STATEMENT, false);
217: l_dim_group_index := FindDimensionGroupIndexForKey(p_dimensions, p_key.KeyName);
218: l_dimension_levels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_dimensions, l_dim_group_index) ;
219: -- Find the dimension level for the lower level key
220: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_key.KeyName);
221: l_level_table_high := l_dimension_levels(l_index).dimTable;
222: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_zmv_key.KeyName);
223: l_level_table_low := l_dimension_levels(l_index).dimTable;
224: bsc_mo_helper_pkg.writeTmp('l_level_table_high='||l_level_table_high||', l_level_table_low='||l_level_table_low, FND_LOG.LEVEL_STATEMENT, false);
218: l_dimension_levels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_dimensions, l_dim_group_index) ;
219: -- Find the dimension level for the lower level key
220: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_key.KeyName);
221: l_level_table_high := l_dimension_levels(l_index).dimTable;
222: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_zmv_key.KeyName);
223: l_level_table_low := l_dimension_levels(l_index).dimTable;
224: bsc_mo_helper_pkg.writeTmp('l_level_table_high='||l_level_table_high||', l_level_table_low='||l_level_table_low, FND_LOG.LEVEL_STATEMENT, false);
225: OPEN cJoin(g_current_indicator.code, g_current_dimset, l_level_table_high);
226: LOOP
220: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_key.KeyName);
221: l_level_table_high := l_dimension_levels(l_index).dimTable;
222: l_index := BSC_MO_HELPER_PKG.FindIndex(l_dimension_levels,p_zmv_key.KeyName);
223: l_level_table_low := l_dimension_levels(l_index).dimTable;
224: bsc_mo_helper_pkg.writeTmp('l_level_table_high='||l_level_table_high||', l_level_table_low='||l_level_table_low, FND_LOG.LEVEL_STATEMENT, false);
225: OPEN cJoin(g_current_indicator.code, g_current_dimset, l_level_table_high);
226: LOOP
227: FETCH cJoin INTO l_dim, l_parent, l_dim_fk, l_index;
228: EXIT WHEN cJoin%NOTFOUND;
232: p_join_parent_fk(p_join_parent_fk.count) := l_dim_fk;
233: END LOOP;
234: CLOSE cJoin;
235: EXCEPTION WHEN OTHERS THEN
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,
250: l_join_parent_fk DBMS_SQL.VARCHAR2_TABLE;
251: l_stack varchar2(32000);
252: BEGIN
253: FOR i IN p_keys.first..p_keys.last LOOP
254: bsc_mo_helper_pkg.writeTmp('p_keys('||i||')='||p_keys(i).keyName, FND_LOG.LEVEL_STATEMENT, false);
255: IF (p_keys(i).keyName <> p_zmv_keys(i).keyName AND p_zero_code_states(i) =0) THEN
256: bsc_mo_helper_pkg.writeTmp('Find join betweeen levels', FND_LOG.LEVEL_STATEMENT, false);
257: find_join_betweens_levels(
258: p_keys(i),
252: BEGIN
253: FOR i IN p_keys.first..p_keys.last LOOP
254: bsc_mo_helper_pkg.writeTmp('p_keys('||i||')='||p_keys(i).keyName, FND_LOG.LEVEL_STATEMENT, false);
255: IF (p_keys(i).keyName <> p_zmv_keys(i).keyName AND p_zero_code_states(i) =0) THEN
256: bsc_mo_helper_pkg.writeTmp('Find join betweeen levels', FND_LOG.LEVEL_STATEMENT, false);
257: find_join_betweens_levels(
258: p_keys(i),
259: p_zmv_keys(i),
260: p_dimensions,
260: p_dimensions,
261: l_join_dimensions,
262: l_join_parent ,
263: l_join_parent_fk );
264: bsc_mo_helper_pkg.writeTmp('Found join betweeen levels ', FND_LOG.LEVEL_STATEMENT, false);
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
261: l_join_dimensions,
262: l_join_parent ,
263: l_join_parent_fk );
264: bsc_mo_helper_pkg.writeTmp('Found join betweeen levels ', FND_LOG.LEVEL_STATEMENT, false);
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
262: l_join_parent ,
263: l_join_parent_fk );
264: bsc_mo_helper_pkg.writeTmp('Found join betweeen levels ', FND_LOG.LEVEL_STATEMENT, false);
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
270: bsc_mo_helper_pkg.write_this(l_join_parent_fk, FND_LOG.LEVEL_STATEMENT, false);
263: l_join_parent_fk );
264: bsc_mo_helper_pkg.writeTmp('Found join betweeen levels ', FND_LOG.LEVEL_STATEMENT, false);
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
270: bsc_mo_helper_pkg.write_this(l_join_parent_fk, FND_LOG.LEVEL_STATEMENT, false);
271: IF (l_join_dimensions.count>0) THEN
264: bsc_mo_helper_pkg.writeTmp('Found join betweeen levels ', FND_LOG.LEVEL_STATEMENT, false);
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
270: bsc_mo_helper_pkg.write_this(l_join_parent_fk, FND_LOG.LEVEL_STATEMENT, false);
271: IF (l_join_dimensions.count>0) THEN
272: FOR j IN l_join_dimensions.first..l_join_dimensions.last LOOP
265: bsc_mo_helper_pkg.writeTmp('l_join_dimensions are ', FND_LOG.LEVEL_STATEMENT, false);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
270: bsc_mo_helper_pkg.write_this(l_join_parent_fk, FND_LOG.LEVEL_STATEMENT, false);
271: IF (l_join_dimensions.count>0) THEN
272: FOR j IN l_join_dimensions.first..l_join_dimensions.last LOOP
273: p_join_dimensions(p_join_dimensions.count) := l_join_dimensions(j);
266: bsc_mo_helper_pkg.write_this(l_join_dimensions, FND_LOG.LEVEL_STATEMENT, false);
267: bsc_mo_helper_pkg.writeTmp('l_join_parents are ', FND_LOG.LEVEL_STATEMENT, false);
268: bsc_mo_helper_pkg.write_this(l_join_parent, FND_LOG.LEVEL_STATEMENT, false);
269: bsc_mo_helper_pkg.writeTmp('l_join_parent_fk are ', FND_LOG.LEVEL_STATEMENT, false);
270: bsc_mo_helper_pkg.write_this(l_join_parent_fk, FND_LOG.LEVEL_STATEMENT, false);
271: IF (l_join_dimensions.count>0) THEN
272: FOR j IN l_join_dimensions.first..l_join_dimensions.last LOOP
273: p_join_dimensions(p_join_dimensions.count) := l_join_dimensions(j);
274: p_join_parents(p_join_parents.count) := l_join_parent(j);
278: END IF;
279: END IF;
280: END LOOP;
281: EXCEPTION WHEN OTHERS THEN
282: bsc_mo_helper_pkg.writeTmp('p_keys, p_zmv_keys, p_zero_code_states, p_dimensions in order', FND_LOG.LEVEL_EXCEPTION, true);
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
279: END IF;
280: END LOOP;
281: EXCEPTION WHEN OTHERS THEN
282: bsc_mo_helper_pkg.writeTmp('p_keys, p_zmv_keys, p_zero_code_states, p_dimensions in order', FND_LOG.LEVEL_EXCEPTION, true);
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
287: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_join_info:'||sqlerrm);
280: END LOOP;
281: EXCEPTION WHEN OTHERS THEN
282: bsc_mo_helper_pkg.writeTmp('p_keys, p_zmv_keys, p_zero_code_states, p_dimensions in order', FND_LOG.LEVEL_EXCEPTION, true);
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
287: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_join_info:'||sqlerrm);
288: raise;
281: EXCEPTION WHEN OTHERS THEN
282: bsc_mo_helper_pkg.writeTmp('p_keys, p_zmv_keys, p_zero_code_states, p_dimensions in order', FND_LOG.LEVEL_EXCEPTION, true);
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
287: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_join_info:'||sqlerrm);
288: raise;
289: END;
282: bsc_mo_helper_pkg.writeTmp('p_keys, p_zmv_keys, p_zero_code_states, p_dimensions in order', FND_LOG.LEVEL_EXCEPTION, true);
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
287: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_join_info:'||sqlerrm);
288: raise;
289: END;
290:
283: bsc_mo_helper_pkg.write_this(p_keys, FND_LOG.LEVEL_EXCEPTION, true);
284: bsc_mo_helper_pkg.write_this(p_zmv_keys, FND_LOG.LEVEL_EXCEPTION, true);
285: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.LEVEL_EXCEPTION, true);
286: bsc_mo_helper_pkg.write_this(p_dimensions, FND_LOG.LEVEL_EXCEPTION, true);
287: bsc_mo_helper_pkg.TerminateWithMsg('Exception in get_join_info:'||sqlerrm);
288: raise;
289: END;
290:
291: --****************************************************************************
345: return false;
346: END IF;
347: -- note that get_n_parents returns the current s_table at position 0, and then its parents
348: -- at positions 1, 2 etc
349: bsc_mo_helper_pkg.writeTmp('Inside Optimize ZMV clause, p_table_level='||p_table_level||', p_system_levels='||p_system_levels, FND_LOG.level_Statement, false);
350: bsc_mo_helper_pkg.writeTmp('Zero Code states = ', FND_LOG.level_Statement, false);
351: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.level_Statement, false);
352: l_select_rest := p_sql_stmt;
353: l_nlevel_parents := get_n_parents(p_s_table, p_s_table_list, p_table_level-p_system_levels);
346: END IF;
347: -- note that get_n_parents returns the current s_table at position 0, and then its parents
348: -- at positions 1, 2 etc
349: bsc_mo_helper_pkg.writeTmp('Inside Optimize ZMV clause, p_table_level='||p_table_level||', p_system_levels='||p_system_levels, FND_LOG.level_Statement, false);
350: bsc_mo_helper_pkg.writeTmp('Zero Code states = ', FND_LOG.level_Statement, false);
351: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.level_Statement, false);
352: l_select_rest := p_sql_stmt;
353: l_nlevel_parents := get_n_parents(p_s_table, p_s_table_list, p_table_level-p_system_levels);
354: bsc_mo_helper_pkg.write_this(l_nlevel_parents, FND_LOG.level_Statement, false);
347: -- note that get_n_parents returns the current s_table at position 0, and then its parents
348: -- at positions 1, 2 etc
349: bsc_mo_helper_pkg.writeTmp('Inside Optimize ZMV clause, p_table_level='||p_table_level||', p_system_levels='||p_system_levels, FND_LOG.level_Statement, false);
350: bsc_mo_helper_pkg.writeTmp('Zero Code states = ', FND_LOG.level_Statement, false);
351: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.level_Statement, false);
352: l_select_rest := p_sql_stmt;
353: l_nlevel_parents := get_n_parents(p_s_table, p_s_table_list, p_table_level-p_system_levels);
354: bsc_mo_helper_pkg.write_this(l_nlevel_parents, FND_LOG.level_Statement, false);
355: IF l_nlevel_parents.count = 1 THEN -- no parents, only 1 level, itself
350: bsc_mo_helper_pkg.writeTmp('Zero Code states = ', FND_LOG.level_Statement, false);
351: bsc_mo_helper_pkg.write_this(p_zero_code_states, FND_LOG.level_Statement, false);
352: l_select_rest := p_sql_stmt;
353: l_nlevel_parents := get_n_parents(p_s_table, p_s_table_list, p_table_level-p_system_levels);
354: bsc_mo_helper_pkg.write_this(l_nlevel_parents, FND_LOG.level_Statement, false);
355: IF l_nlevel_parents.count = 1 THEN -- no parents, only 1 level, itself
356: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause', FND_LOG.level_Statement, false);
357: return false;
358: END IF;
352: l_select_rest := p_sql_stmt;
353: l_nlevel_parents := get_n_parents(p_s_table, p_s_table_list, p_table_level-p_system_levels);
354: bsc_mo_helper_pkg.write_this(l_nlevel_parents, FND_LOG.level_Statement, false);
355: IF l_nlevel_parents.count = 1 THEN -- no parents, only 1 level, itself
356: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause', FND_LOG.level_Statement, false);
357: return false;
358: END IF;
359: l_highest_table_with_zmv := l_nlevel_parents(l_nlevel_parents.last);
360: -- BSC Multiple Optimizers to run
357: return false;
358: END IF;
359: l_highest_table_with_zmv := l_nlevel_parents(l_nlevel_parents.last);
360: -- BSC Multiple Optimizers to run
361: --l_zmv_keys := BSC_MO_HELPER_PKG.getAllKeyFields(l_highest_table_with_zmv);
362: l_index := BSC_MO_HELPER_PKG.findIndex(p_s_table_list, l_highest_table_with_zmv);
363: bsc_mo_helper_pkg.writeTmp('Highest table with zmv is '||l_highest_table_with_zmv||', with index='||l_index, FND_LOG.level_Statement, false);
364: l_zmv_keys := p_s_table_list(l_index).keys;
365:
358: END IF;
359: l_highest_table_with_zmv := l_nlevel_parents(l_nlevel_parents.last);
360: -- BSC Multiple Optimizers to run
361: --l_zmv_keys := BSC_MO_HELPER_PKG.getAllKeyFields(l_highest_table_with_zmv);
362: l_index := BSC_MO_HELPER_PKG.findIndex(p_s_table_list, l_highest_table_with_zmv);
363: bsc_mo_helper_pkg.writeTmp('Highest table with zmv is '||l_highest_table_with_zmv||', with index='||l_index, FND_LOG.level_Statement, false);
364: l_zmv_keys := p_s_table_list(l_index).keys;
365:
366: IF (p_keys.count <> l_zmv_keys.count) THEN -- MN rel
359: l_highest_table_with_zmv := l_nlevel_parents(l_nlevel_parents.last);
360: -- BSC Multiple Optimizers to run
361: --l_zmv_keys := BSC_MO_HELPER_PKG.getAllKeyFields(l_highest_table_with_zmv);
362: l_index := BSC_MO_HELPER_PKG.findIndex(p_s_table_list, l_highest_table_with_zmv);
363: bsc_mo_helper_pkg.writeTmp('Highest table with zmv is '||l_highest_table_with_zmv||', with index='||l_index, FND_LOG.level_Statement, false);
364: l_zmv_keys := p_s_table_list(l_index).keys;
365:
366: IF (p_keys.count <> l_zmv_keys.count) THEN -- MN rel
367: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause', FND_LOG.level_Statement, false);
363: bsc_mo_helper_pkg.writeTmp('Highest table with zmv is '||l_highest_table_with_zmv||', with index='||l_index, FND_LOG.level_Statement, false);
364: l_zmv_keys := p_s_table_list(l_index).keys;
365:
366: IF (p_keys.count <> l_zmv_keys.count) THEN -- MN rel
367: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause', FND_LOG.level_Statement, false);
368: return false;
369: END IF;
370: b_zmv_exists := false;
371: FOR i IN l_zmv_keys.first..l_zmv_keys.last LOOP
374: EXIT;
375: END IF;
376: END LOOP;
377: IF (b_zmv_exists = false) THEN
378: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, Zero code mv with values for this level comb does not exist, returning false', FND_LOG.level_Statement, false);
379: return false;
380: ELSE
381: bsc_mo_helper_pkg.writeTmp('ZMV exists ', FND_LOG.level_Statement, false);
382: END IF;
377: IF (b_zmv_exists = false) THEN
378: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, Zero code mv with values for this level comb does not exist, returning false', FND_LOG.level_Statement, false);
379: return false;
380: ELSE
381: bsc_mo_helper_pkg.writeTmp('ZMV exists ', FND_LOG.level_Statement, false);
382: END IF;
383: l_zmv := l_highest_table_with_zmv||'_ZMV';
384: -- Note: l_nlevel_parents(0) is the same as the current S table
385: -- Only l_nlevel_parents(1) and higher are the real parents
421: l_from_clause := substr(l_from_clause, 1, length(l_from_clause)-1);
422:
423: -- We need to add the ZMV. alias to the SELECT column keys
424: IF (b_dimensions_joined) THEN
425: bsc_mo_helper_pkg.writeTmp('Dimensions have been joined to, so we need to change the select clause', FND_LOG.level_Statement, false);
426: END IF;
427: l_select_keys_clause := 'SELECT ';
428: l_group_by_clause := ' GROUP BY ';
429: FOR j IN p_Keys.first..p_Keys.last LOOP
444: l_where_clause := null;
445: ELSE
446: l_where_clause := substr(l_where_clause, 1, length(l_where_clause)-5);
447: END IF;
448: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_Statement, false);
449:
450: bsc_mo_helper_pkg.writeTmp('l_select_keys_clause='||l_select_keys_clause, FND_LOG.level_Statement, false);
451: bsc_mo_helper_pkg.writeTmp('Intermediate l_group_by_clause='||l_group_by_clause, FND_LOG.level_Statement, false);
452: l_group_by_clause := l_group_by_clause ||' PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID ';
446: l_where_clause := substr(l_where_clause, 1, length(l_where_clause)-5);
447: END IF;
448: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_Statement, false);
449:
450: bsc_mo_helper_pkg.writeTmp('l_select_keys_clause='||l_select_keys_clause, FND_LOG.level_Statement, false);
451: bsc_mo_helper_pkg.writeTmp('Intermediate l_group_by_clause='||l_group_by_clause, FND_LOG.level_Statement, false);
452: l_group_by_clause := l_group_by_clause ||' PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID ';
453: bsc_mo_helper_pkg.writeTmp('l_group_by_clause final='||l_group_by_clause, FND_LOG.level_Statement, false);
454: p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
447: END IF;
448: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_Statement, false);
449:
450: bsc_mo_helper_pkg.writeTmp('l_select_keys_clause='||l_select_keys_clause, FND_LOG.level_Statement, false);
451: bsc_mo_helper_pkg.writeTmp('Intermediate l_group_by_clause='||l_group_by_clause, FND_LOG.level_Statement, false);
452: l_group_by_clause := l_group_by_clause ||' PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID ';
453: bsc_mo_helper_pkg.writeTmp('l_group_by_clause final='||l_group_by_clause, FND_LOG.level_Statement, false);
454: p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
455: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, sql_stmt='||p_sql_stmt, FND_LOG.level_Statement, false);
449:
450: bsc_mo_helper_pkg.writeTmp('l_select_keys_clause='||l_select_keys_clause, FND_LOG.level_Statement, false);
451: bsc_mo_helper_pkg.writeTmp('Intermediate l_group_by_clause='||l_group_by_clause, FND_LOG.level_Statement, false);
452: l_group_by_clause := l_group_by_clause ||' PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID ';
453: bsc_mo_helper_pkg.writeTmp('l_group_by_clause final='||l_group_by_clause, FND_LOG.level_Statement, false);
454: p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
455: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, sql_stmt='||p_sql_stmt, FND_LOG.level_Statement, false);
456: return true;
457: EXCEPTION WHEN OTHERS THEN
451: bsc_mo_helper_pkg.writeTmp('Intermediate l_group_by_clause='||l_group_by_clause, FND_LOG.level_Statement, false);
452: l_group_by_clause := l_group_by_clause ||' PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID ';
453: bsc_mo_helper_pkg.writeTmp('l_group_by_clause final='||l_group_by_clause, FND_LOG.level_Statement, false);
454: p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
455: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, sql_stmt='||p_sql_stmt, FND_LOG.level_Statement, false);
456: return true;
457: EXCEPTION WHEN OTHERS THEN
458: bsc_mo_helper_pkg.writeTmp('Completed optimize_zmv_clause with error : '||sqlerrm, fnd_log.level_exception, true);
459: bsc_mo_helper_pkg.TerminateWithMsg('Exception in optimize_zmv_clause:'||sqlerrm);
454: p_sql_stmt := l_select_keys_clause||' '||l_select_rest||' '||l_from_clause||' '||l_where_clause||' '||l_group_by_clause;
455: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, sql_stmt='||p_sql_stmt, FND_LOG.level_Statement, false);
456: return true;
457: EXCEPTION WHEN OTHERS THEN
458: bsc_mo_helper_pkg.writeTmp('Completed optimize_zmv_clause with error : '||sqlerrm, fnd_log.level_exception, true);
459: bsc_mo_helper_pkg.TerminateWithMsg('Exception in optimize_zmv_clause:'||sqlerrm);
460: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_exception, true);
461: raise;
462: return false;
455: bsc_mo_helper_pkg.writeTmp('Completed Optimize ZMV clause, sql_stmt='||p_sql_stmt, FND_LOG.level_Statement, false);
456: return true;
457: EXCEPTION WHEN OTHERS THEN
458: bsc_mo_helper_pkg.writeTmp('Completed optimize_zmv_clause with error : '||sqlerrm, fnd_log.level_exception, true);
459: bsc_mo_helper_pkg.TerminateWithMsg('Exception in optimize_zmv_clause:'||sqlerrm);
460: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_exception, true);
461: raise;
462: return false;
463: END;
456: return true;
457: EXCEPTION WHEN OTHERS THEN
458: bsc_mo_helper_pkg.writeTmp('Completed optimize_zmv_clause with error : '||sqlerrm, fnd_log.level_exception, true);
459: bsc_mo_helper_pkg.TerminateWithMsg('Exception in optimize_zmv_clause:'||sqlerrm);
460: bsc_mo_helper_pkg.writeTmp('l_where_clause final='||l_where_clause, FND_LOG.level_exception, true);
461: raise;
462: return false;
463: END;
464:
512: l_stack varchar2(32000);
513: l_newline varchar2(10):='
514: ';
515: BEGIN
516: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
517: bsc_mo_helper_pkg.writeTmp('# of levels = '||l_groups.count||' while max allowed = '||BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS, FND_LOG.LEVEL_STATEMENT, false);
518: -- AWs, assume ZMV exists
519: IF (g_current_indicator.Impl_Type = 2) THEN
520: bForcedSQL := false;
513: l_newline varchar2(10):='
514: ';
515: BEGIN
516: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
517: bsc_mo_helper_pkg.writeTmp('# of levels = '||l_groups.count||' while max allowed = '||BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS, FND_LOG.LEVEL_STATEMENT, false);
518: -- AWs, assume ZMV exists
519: IF (g_current_indicator.Impl_Type = 2) THEN
520: bForcedSQL := false;
521: ELSIF (l_groups.count > BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS) THEN
519: IF (g_current_indicator.Impl_Type = 2) THEN
520: bForcedSQL := false;
521: ELSIF (l_groups.count > BSC_BIA_WRAPPER.MAX_ALLOWED_LEVELS) THEN
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(' ');
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);
531: MVName := STable.Name || '_MV';
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);
531: MVName := STable.Name || '_MV';
532: If STable.LevelConfig IS NULL Then
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);
531: MVName := STable.Name || '_MV';
532: If STable.LevelConfig IS NULL Then
533: --Table has no dimensions
534: configKpiMV.LevelComb := '?';
542: anyKeyNeedZeroCode := False;
543: arrCombinationsA(0) := STable.LevelConfig;
544: numCombinationsA := 1;
545: --BSC Multiple Optimizers
546: --STable_Keys := bsc_mo_helper_pkg.getAllKeyFields(STable.name);
547: i := BSC_MO_HELPER_PKG.findIndex(colSummaryTables, STable.name);
548: STable_Keys := colSummaryTables (i).keys;
549: FOR i IN STable_Keys.first..STable_Keys.last LOOP
550: keyColumn := STable_keys(i);
543: arrCombinationsA(0) := STable.LevelConfig;
544: numCombinationsA := 1;
545: --BSC Multiple Optimizers
546: --STable_Keys := bsc_mo_helper_pkg.getAllKeyFields(STable.name);
547: i := BSC_MO_HELPER_PKG.findIndex(colSummaryTables, STable.name);
548: STable_Keys := colSummaryTables (i).keys;
549: FOR i IN STable_Keys.first..STable_Keys.last LOOP
550: keyColumn := STable_keys(i);
551: bsc_mo_helper_pkg.write_this(keyColumn);
547: i := BSC_MO_HELPER_PKG.findIndex(colSummaryTables, STable.name);
548: STable_Keys := colSummaryTables (i).keys;
549: FOR i IN STable_Keys.first..STable_Keys.last LOOP
550: keyColumn := STable_keys(i);
551: bsc_mo_helper_pkg.write_this(keyColumn);
552: If keyColumn.CalculateCode0 Then
553: anyKeyNeedZeroCode := True;
554: arrCombinationsB.delete;
555: numCombinationsB := 0;
570: numCombinationsA := arrCombinationsA.count;
571: End If;
572: END LOOP;
573: If anyKeyNeedZeroCode Then
574: bsc_mo_helper_pkg.writeTmp('Zero code is needed, no. of combinations = '||numCombinationsB, FND_LOG.level_Statement, false);
575: For i IN 0..numCombinationsB - 1 LOOP
576: bsc_mo_helper_pkg.writeTmp('Processing combination '||i||'='||arrCombinationsB(i), FND_LOG.level_Statement, false);
577: l_stack := null;
578: configKpiMV := New_clsConfigKpiMV;
572: END LOOP;
573: If anyKeyNeedZeroCode Then
574: bsc_mo_helper_pkg.writeTmp('Zero code is needed, no. of combinations = '||numCombinationsB, FND_LOG.level_Statement, false);
575: For i IN 0..numCombinationsB - 1 LOOP
576: bsc_mo_helper_pkg.writeTmp('Processing combination '||i||'='||arrCombinationsB(i), FND_LOG.level_Statement, false);
577: l_stack := null;
578: configKpiMV := New_clsConfigKpiMV;
579: configKpiMV.LevelComb := arrCombinationsB(i);
580: isTotalCombination := False;
628: sql_stmt := null;
629: sql_stmt := sql_stmt || 'PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID';
630: group_by := group_by || 'PERIODICITY_ID, YEAR, TYPE, PERIOD, PERIOD_TYPE_ID';
631: --BSC Multiple Optimizer
632: --STable_Data := bsc_mo_helper_pkg.getAllDataFields(STable.name);
633: STable_Data := STable.Data;
634: FOR j IN STable_Data.first..STable_Data.last LOOP
635: Dato := STable_Data(j);
636: If Dato.AvgLFlag = 'Y' Then
641: l_stack := l_stack || 'check 2, sql_stmt is '||sql_stmt||l_newline;
642: End If;
643: END LOOP;
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
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
662: sql_stmt := l_select_key_clause || sql_stmt || ' FROM ' || MVName;
663: sql_stmt := sql_stmt || ' GROUP BY ' || group_by;
682: End If;
683: return colConfigKpiMV;
684: EXCEPTION WHEN OTHERS THEN
685: g_error := sqlerrm;
686: bsc_mo_helper_pkg.writeTmp('Exception in GetColConfigKpiMV : '||g_error, FND_LOG.LEVEL_STATEMENT, true);
687: bsc_mo_helper_pkg.writeTmp('Stack is '||l_stack, FND_LOG.LEVEL_STATEMENT, true);
688: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetColConfigKpiMV : '||g_error);
689: raise;
690: End ;
683: return colConfigKpiMV;
684: EXCEPTION WHEN OTHERS THEN
685: g_error := sqlerrm;
686: bsc_mo_helper_pkg.writeTmp('Exception in GetColConfigKpiMV : '||g_error, FND_LOG.LEVEL_STATEMENT, true);
687: bsc_mo_helper_pkg.writeTmp('Stack is '||l_stack, FND_LOG.LEVEL_STATEMENT, true);
688: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetColConfigKpiMV : '||g_error);
689: raise;
690: End ;
691:
684: EXCEPTION WHEN OTHERS THEN
685: g_error := sqlerrm;
686: bsc_mo_helper_pkg.writeTmp('Exception in GetColConfigKpiMV : '||g_error, FND_LOG.LEVEL_STATEMENT, true);
687: bsc_mo_helper_pkg.writeTmp('Stack is '||l_stack, FND_LOG.LEVEL_STATEMENT, true);
688: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetColConfigKpiMV : '||g_error);
689: raise;
690: End ;
691:
692:
732: END LOOP;
733: return false;
734: EXCEPTION WHEN OTHERS THEN
735: g_error := sqlerrm;
736: bsc_mo_helper_pkg.TerminateWithMsg('Exception in fieldExistsInLov for field '||measure||' : '||g_error);
737: raise;
738: End ;
739:
740:
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;
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
776:
777: return -1;
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;
777: return -1;
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:
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:
823:
824: return -1;
825: EXCEPTION WHEN OTHERS THEN
826: g_error := sqlerrm;
827: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IndexRelationMN : '||g_error);
828: raise;
829: End;
830:
831:
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;
869: l_count := colPeriodicities.first;
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;
869: l_count := colPeriodicities.first;
870:
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;
869: l_count := colPeriodicities.first;
870:
871:
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;
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;
892: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
893: bsc_mo_helper_pkg.writeTmp('returning '||l_return);
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;
897: l_stack := l_stack||g_newline||'check5';
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));
910: end if;
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:
918: EXCEPTION WHEN OTHERS THEN
916: RETURN L_RETURN;
917:
918: EXCEPTION WHEN OTHERS THEN
919: g_error := sqlerrm;
920: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetPeriodicityOrigin, '||g_error);
921: bsc_mo_helper_pkg.writeTmp('Stack is '||l_stack, fnd_log.level_exception, true);
922: raise;
923: END;
924:
917:
918: EXCEPTION WHEN OTHERS THEN
919: g_error := sqlerrm;
920: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetPeriodicityOrigin, '||g_error);
921: bsc_mo_helper_pkg.writeTmp('Stack is '||l_stack, fnd_log.level_exception, true);
922: raise;
923: END;
924:
925: --****************************************************************************
962: END IF;
963:
964: EXCEPTION WHEN OTHERS THEN
965: g_error := sqlerrm;
966: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IsFilteredIndicator : '||g_error);
967: raise;
968: End;
969:
970:
983: l_hash_index dbms_sql.varchar2_table;
984: l_num_measures dbms_sql.number_table;
985: cv CurTyp;
986: BEGIN
987: l_dummy := bsc_mo_helper_pkg.Get_New_Big_In_Cond_Number(4, 'INDICATOR');
988: bsc_mo_helper_pkg.Add_Value_Bulk(4, l_list);
989: OPEN cv FOR l_stmt;
990: FETCH cv BULK COLLECT INTO l_hash_index, l_num_measures;
991: CLOSE cv;
984: l_num_measures dbms_sql.number_table;
985: cv CurTyp;
986: BEGIN
987: l_dummy := bsc_mo_helper_pkg.Get_New_Big_In_Cond_Number(4, 'INDICATOR');
988: bsc_mo_helper_pkg.Add_Value_Bulk(4, l_list);
989: OPEN cv FOR l_stmt;
990: FETCH cv BULK COLLECT INTO l_hash_index, l_num_measures;
991: CLOSE cv;
992: FOR i IN 1..l_hash_index.count LOOP
993: g_objective_measures(l_hash_index(i)).value := l_num_measures(i);
994: END LOOP;
995: EXCEPTION WHEN OTHERS THEN
996: g_error := sqlerrm;
997: bsc_mo_helper_pkg.TerminateWithMsg('Exception in init_measure_count : '||g_error||', stmt='||l_stmt);
998: raise;
999: END;
1000:
1001: --***************************************************************************
1056: return numDataColumns;
1057:
1058: EXCEPTION WHEN OTHERS THEN
1059: g_error := sqlerrm;
1060: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetNumDataColumns : '||g_error);
1061: raise;
1062: End;
1063:
1064:
1088:
1089: return l_ret;
1090: EXCEPTION WHEN OTHERS THEN
1091: g_error := sqlerrm;
1092: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetSourceDimensionSet : '||g_error);
1093: raise;
1094: End;
1095:
1096:
1143: --END IF;
1144: END LOOP;
1145: close cConfigs;
1146:
1147: --bsc_mo_helper_pkg.write_this(colConfigurationes);
1148: return colConfigurationes;
1149:
1150:
1151: EXCEPTION WHEN OTHERS THEN
1149:
1150:
1151: EXCEPTION WHEN OTHERS THEN
1152: g_error := sqlerrm;
1153: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetConfigurationsForIndic for Indic='||Indic||', error is '||g_error);
1154: fnd_message.set_name('BSC', 'BSC_RETR_DIMSET_KPI_FAILED');
1155: fnd_message.set_token('INDICATOR', Indic);
1156: app_exception.raise_exception;
1157:
1231: END LOOP;
1232:
1233: EXCEPTION WHEN OTHERS THEN
1234: g_error := sqlerrm;
1235: bsc_mo_helper_pkg.TerminateWithMsg('Exception in ConfigureMasterSharedIndics : '||g_error);
1236: raise;
1237: END;
1238:
1239: --***************************************************************************
1269:
1270: return false;
1271: EXCEPTION WHEN OTHERS THEN
1272: g_error := sqlerrm;
1273: bsc_mo_helper_pkg.TerminateWithMsg('EXCEPTION IN keyFieldExists : '||g_error);
1274: raise;
1275: End;
1276:
1277: --****************************************************************************
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;
1312: keysB := tableB.keys;
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;
1334: END IF;
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
1341: g_error := sqlerrm;
1338: END IF;
1339: return l_res;
1340: EXCEPTION WHEN OTHERS THEN
1341: g_error := sqlerrm;
1342: bsc_mo_helper_pkg.TerminateWithMsg('EXCEPTION in SameDisaggregations : '||g_error);
1343: raise;
1344: End ;
1345:
1346: --****************************************************************************
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;
1370: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
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;
1370: i := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1371: LOOP
1372: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gTables.count=0;
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
1388: g_error := sqlerrm;
1385: END IF;
1386: return targetTable;
1387: EXCEPTION WHEN OTHERS THEN
1388: g_error := sqlerrm;
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
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
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);
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;
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;
1418: EXIT WHEN i = l_origin_table.last;
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
1426: g_error := sqlerrm;
1423: END IF;
1424: return l_res;
1425: EXCEPTION WHEN OTHERS THEN
1426: g_error := sqlerrm;
1427: bsc_mo_helper_pkg.TerminateWithMsg('Exception in OriginTableHasTarget : '||g_error);
1428: raise;
1429: End;
1430:
1431: --****************************************************************************
1445: l_origin_table DBMS_SQL.VARCHAR2_TABLE;
1446: l_dummy NUMBER;
1447: BEGIN
1448:
1449: l_dummy := BSC_MO_HELPER_PKG.decomposestring(p_table.originTable, ',', l_origin_table);
1450: i := l_origin_table.first;
1451: LOOP
1452: EXIT WHEN l_origin_table.count=0;
1453: OriTable := l_origin_table(i);
1450: i := l_origin_table.first;
1451: LOOP
1452: EXIT WHEN l_origin_table.count=0;
1453: OriTable := l_origin_table(i);
1454: If Not BSC_MO_HELPER_PKG.searchStringExists(arrVisitedTables, numVisitedTables, OriTable) Then
1455: return false;
1456: END IF;
1457: EXIT WHEN i = l_origin_table.last;
1458: i := l_origin_table.next(i);
1459: END LOOP;
1460: return true;
1461: EXCEPTION WHEN OTHERS THEN
1462: g_error := sqlerrm;
1463: bsc_mo_helper_pkg.TerminateWithMsg('Exception in TableAlreadyVisited : '||g_error);
1464: raise;
1465: End;
1466: --****************************************************************************
1467: --ConnectTargetTables
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;
1497: anyTableVisited := True;
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;
1497: anyTableVisited := True;
1498: --BSC-MV Note: There is a special case in this Implementation with Targets at different
1499: --levels. When a table for targets merge into the summary table, we need to calculate
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
1519: IF Not BSC_MO_HELPER_PKG.searchStringExists(arrVisitedTables, numVisitedTables, l_table.Name) THEN
1520: IF TableAlreadyVisited(l_table, arrVisitedTables, numVisitedTables) THEN
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
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
1530: BSC_MO_HELPER_PKG.writeTmp('assigning target table is');
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 ||',';
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.
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;
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);
1597: EXIT;
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;
1616: bsc_mo_helper_pkg.TerminateWithMsg('Exception in ConnectTargetTables :' ||g_error);
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;
1616: bsc_mo_helper_pkg.TerminateWithMsg('Exception in ConnectTargetTables :' ||g_error);
1617: raise;
1618: END;
1619:
1620: --****************************************************************************
1637: group_id NUMBER;
1638:
1639: BEGIN
1640:
1641: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
1642: i := l_groups.first;
1643:
1644: LOOP
1645: EXIT WHEN l_groups.count = 0;
1642: i := l_groups.first;
1643:
1644: LOOP
1645: EXIT WHEN l_groups.count = 0;
1646: DimensionLevels := BSC_MO_HELPER_PKG.get_Tab_clsLevels(p_dimension_families, i) ;
1647: j := DimensionLevels.first;
1648: LOOP
1649: EXIT WHEN DimensionLevels.count=0;
1650: Dril := DimensionLevels(j);
1662: return l_res;
1663:
1664: EXCEPTION WHEN OTHERS THEN
1665: g_error := sqlerrm;
1666: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetKeyNum : '||g_error);
1667: raise;
1668: End;
1669:
1670: --****************************************************************************
1724: TYPE tab_clsKPIData IS TABLE OF BSC_KPI_DATA_TABLES%ROWTYPE index by binary_integer;
1725: l_kpidata_record BSC_KPI_DATA_TABLES%ROWTYPE ;
1726: l_tbl_kpidata tab_clsKPIData;
1727: BEGIN
1728: bsc_mo_helper_pkg.writeTmp( ' ');
1729: bsc_mo_helper_pkg.writeTmp( 'Inside deduce_and_configure_s_tables, Configuration = '||Configuration ||', forTargetLevel='
1730: ||bsc_mo_helper_pkg.boolean_decode(forTargetLevel)||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
1731: --Delete from BSC_KPI_DATA_TABLES the records for this indicator and configuration
1732: If Not forTargetLevel Then
1725: l_kpidata_record BSC_KPI_DATA_TABLES%ROWTYPE ;
1726: l_tbl_kpidata tab_clsKPIData;
1727: BEGIN
1728: bsc_mo_helper_pkg.writeTmp( ' ');
1729: bsc_mo_helper_pkg.writeTmp( 'Inside deduce_and_configure_s_tables, Configuration = '||Configuration ||', forTargetLevel='
1730: ||bsc_mo_helper_pkg.boolean_decode(forTargetLevel)||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
1731: --Delete from BSC_KPI_DATA_TABLES the records for this indicator and configuration
1732: If Not forTargetLevel Then
1733: --The tables are not for targets
1726: l_tbl_kpidata tab_clsKPIData;
1727: BEGIN
1728: bsc_mo_helper_pkg.writeTmp( ' ');
1729: bsc_mo_helper_pkg.writeTmp( 'Inside deduce_and_configure_s_tables, Configuration = '||Configuration ||', forTargetLevel='
1730: ||bsc_mo_helper_pkg.boolean_decode(forTargetLevel)||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
1731: --Delete from BSC_KPI_DATA_TABLES the records for this indicator and configuration
1732: If Not forTargetLevel Then
1733: --The tables are not for targets
1734: DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = Indicator.Code
1736: END IF;
1737: IF (colPeriodicities.count >0) THEN
1738: i := colPeriodicities.first;
1739: ELSE
1740: bsc_mo_helper_pkg.writeTmp( 'Compl. deduce_and_configure_s_tables, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
1741: return;
1742: END IF;
1743: LOOP
1744: L_Periodicity := colPeriodicities(i);
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
1750: If Indicator.OptimizationMode <> 0 Then
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
1750: If Indicator.OptimizationMode <> 0 Then
1751: --if the indicator is no-precalculated then it can have change of periodicity
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
1762: EXIT WHEN colSummaryTables.count =0;
1759: END IF;
1760: j := colSummaryTables.first;
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);
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
1770: --basic_keys := BSC_MO_HELPER_PKG.getAllKeyFields(Basica.Name);
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
1770: --basic_keys := BSC_MO_HELPER_PKG.getAllKeyFields(Basica.Name);
1771: --basic_data := BSC_MO_HELPER_PKG.getAllDataFields(Basica.Name);
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
1770: --basic_keys := BSC_MO_HELPER_PKG.getAllKeyFields(Basica.Name);
1771: --basic_data := BSC_MO_HELPER_PKG.getAllDataFields(Basica.Name);
1772: basic_keys := Basica.keys;
1773: basic_data := Basica.data;
1774: L_Table.Name := Basica.Name || '_'|| L_Periodicity.Code;
1767: bsc_mo_helper_pkg.write_this(Basica);
1768: END IF;
1769: --BSC Multiple Optimizers
1770: --basic_keys := BSC_MO_HELPER_PKG.getAllKeyFields(Basica.Name);
1771: --basic_data := BSC_MO_HELPER_PKG.getAllDataFields(Basica.Name);
1772: basic_keys := Basica.keys;
1773: basic_data := Basica.data;
1774: L_Table.Name := Basica.Name || '_'|| L_Periodicity.Code;
1775: L_Table.Type := 1;
1782: LOOP
1783: EXIT WHEN basic_keys.count=0;
1784: keyBasica.keyName := null;
1785: keyBasica := basic_keys(k);
1786: key := bsc_mo_helper_pkg.new_clsKeyField;
1787: key.keyName := keyBasica.keyName;
1788: key.Origin := keyBasica.keyName;
1789: key.NeedsCode0 := keyBasica.NeedsCode0;
1790: --BSC-MV Note: In BSC-MV architecture we need to configure zero code
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');
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');
1905: ELSE
1906: BSC_MO_HELPER_PKG.addTable(L_Table, Table_keys, Table_data, 'deduce_and_configure_s_tables');
1907: END IF;
1908: Table_keys.delete; -- cleanup
1902: l_test := -1;
1903: END IF;
1904: BSC_MO_HELPER_PKG.addTable(L_Table, Table_Keys, Table_data, 'deduce_and_configure_s_tables');
1905: ELSE
1906: BSC_MO_HELPER_PKG.addTable(L_Table, Table_keys, Table_data, 'deduce_and_configure_s_tables');
1907: END IF;
1908: Table_keys.delete; -- cleanup
1909: Table_data.delete; -- cleanup
1910: --Configure metadata in order to the indicator read from this table
2022: END IF;
2023: EXIT WHEN i = colPeriodicities.last;
2024: i := colPeriodicities.next(i);
2025: END LOOP;
2026: bsc_mo_helper_pkg.writeTmp( 'Compl. deduce_and_configure_s_tables, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
2027:
2028: EXCEPTION WHEN OTHERS THEN
2029: l_stmt := sqlerrm;
2030: bsc_mo_helper_pkg.writeTmp( 'exception in deduce_and_configure_s_tables:'||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
2026: bsc_mo_helper_pkg.writeTmp( 'Compl. deduce_and_configure_s_tables, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, false);
2027:
2028: EXCEPTION WHEN OTHERS THEN
2029: l_stmt := sqlerrm;
2030: bsc_mo_helper_pkg.writeTmp( 'exception in deduce_and_configure_s_tables:'||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
2031: fnd_message.set_name('BSC', 'BSC_KPICONFIG_SYSTABLES_FAILED');
2032: fnd_message.set_token('INDICATOR', Indicator.code);
2033: fnd_message.set_token('DIMENSION_SET', Configuration);
2034: g_error := fnd_message.get;
2031: fnd_message.set_name('BSC', 'BSC_KPICONFIG_SYSTABLES_FAILED');
2032: fnd_message.set_token('INDICATOR', Indicator.code);
2033: fnd_message.set_token('DIMENSION_SET', Configuration);
2034: g_error := fnd_message.get;
2035: bsc_mo_helper_pkg.terminatewithMsg(g_error);
2036: raise;
2037:
2038: End ;
2039:
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;
2091:
2092: return null;
2093: EXCEPTION WHEN OTHERS THEN
2094: g_error := sqlerrm;
2095: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetKeyOrigin : '||g_error);
2096: raise;
2097: End;
2098:
2099:
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;
2179: return true;
2180: END IF;
2181: EXCEPTION WHEN OTHERS THEN
2182: g_error := sqlerrm;
2183: bsc_mo_helper_pkg.TerminateWithMsg('Exception in keyOriginExists : '||g_error);
2184: raise;
2185: End ;
2186:
2187: --****************************************************************************
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;
2221: IF (colSummaryTables.count >0) THEN
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;
2221: IF (colSummaryTables.count >0) THEN
2222: i := colSummaryTables.first;
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;
2221: IF (colSummaryTables.count >0) THEN
2222: i := colSummaryTables.first;
2223: END IF;
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);
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
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
2272: If keyName.NeedsCode0 Then
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;
2278: End If;
2279: Else
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;
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
2289: IF (Basic1_keys(l_index).NeedsCode0) Then
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
2289: IF (Basic1_keys(l_index).NeedsCode0) Then
2290: null;
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;
2297: End If;
2303: k := l_s_table_keys.next(k);
2304: END LOOP;
2305: -- will delete and insert keys
2306: --BSC Multiple Optimizers
2307: --BSC_MO_HELPER_PKG.insertKeys(l_s_table.name, l_s_table_keys);
2308: l_s_table.keys := l_s_table_keys;
2309: END IF;
2310: --assign the property TablaOri with the name of the origin table
2311: l_s_table.originTable := Basica1.Name;
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
2325: IF (l_s_table_keys.count>0) THEN
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
2335: --BSC-MV Note: In this architecture
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
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
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;
2345: End If;
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;
2355: l_s_table_keys(j) := keyName;
2357: j := l_s_table_keys.next(j);
2358: END LOOP;
2359: -- will delete and insert keys
2360: --BSC Multiple Optimizers
2361: --BSC_MO_HELPER_PKG.insertKeys(l_s_table.name, l_s_table_keys);
2362: l_s_table.keys := l_s_table_keys;
2363: END IF;
2364: --leave the property TablaOri in ''
2365: 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;
2372: i := colSummaryTables.next(i);
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;
2380:
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;
2380:
2381: EXCEPTION WHEN OTHERS THEN
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;
2380:
2381: EXCEPTION WHEN OTHERS THEN
2382: g_error := sqlerrm;
2379: END IF;
2380:
2381: EXCEPTION WHEN OTHERS THEN
2382: g_error := sqlerrm;
2383: bsc_mo_helper_pkg.TerminateWithMsg('Exception in DeduceInternalGraph : '||g_error);
2384: fnd_message.set_name('BSC', 'BSC_REL_DEDUCTION_FAILED');
2385: app_exception.raise_exception;
2386: End;
2387:
2421: l_group_id NUMBER;
2422:
2423: BEGIN
2424:
2425: l_groups := bsc_mo_helper_pkg.getGroupIds(p_dimension_families);
2426: numDriles := 0;
2427: For i IN 0..l_groups.Count-1 loop
2428: DimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(i));
2429: For j IN 0..DimLevels.Count-1 LOOP
2424:
2425: l_groups := bsc_mo_helper_pkg.getGroupIds(p_dimension_families);
2426: numDriles := 0;
2427: For i IN 0..l_groups.Count-1 loop
2428: DimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(i));
2429: For j IN 0..DimLevels.Count-1 LOOP
2430: arrOrdenDrilesActual(numDriles) := DimLevels(j).Num;
2431: arrOrdenDriles(numDriles) := DimLevels(j).Num;
2432: arrConfDriles(numDriles) := Trim(substr(p_level_string, numDriles + 1, 1));
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;
2460:
2459: return l_ordered_level_string;
2460:
2461: EXCEPTION WHEN OTHERS THEN
2462: g_error := sqlerrm;
2463: bsc_mo_helper_pkg.TerminateWithMsg('Exception in order_level_string : '||g_error);
2464: RAISE;
2465: End;
2466:
2467:
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:
2498:
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;
2508:
2507: return l_return;
2508:
2509: EXCEPTION WHEN OTHERS THEN
2510: g_error := sqlerrm;
2511: bsc_mo_helper_pkg.TerminateWithMsg('Exception in GetFilterViewName : '||g_error);
2512: RAISE;
2513:
2514: End ;
2515:
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);
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;
2539:
2540: If l_indicator.IndicatorType = 1 And l_indicator.ConfigType = 3 Then
2544: END IF;
2545:
2546: EXCEPTION WHEN OTHERS THEN
2547: g_error := sqlerrm;
2548: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IsIndicatorPnL for '||Ind||' : '||g_error);
2549: RAISE;
2550:
2551: End;
2552:
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);
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;
2576:
2577: If l_indicator.IndicatorType = 1 And l_indicator.ConfigType = 2 Then
2580: return false;
2581: END IF;
2582: EXCEPTION WHEN OTHERS THEN
2583: g_error := sqlerrm;
2584: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IsIndicatorBalance for '||ind||' : '||g_error);
2585: RAISE;
2586: End;
2587:
2588: --****************************************************************************
2605: END IF;
2606:
2607: EXCEPTION WHEN OTHERS THEN
2608: g_error := sqlerrm;
2609: bsc_mo_helper_pkg.TerminateWithMsg('Exception in IsIndicatorBalanceOrPnL for '||ind||' : '||g_error);
2610: RAISE;
2611:
2612: End;
2613:
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);
2676: END IF;
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);
2676: END IF;
2677:
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);
2676: END IF;
2677:
2678: l_num_tables := 1;
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);
2676: END IF;
2677:
2678: l_num_tables := 1;
2679: For i in 0..p_num_dimensions - 1 LOOP
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);
2706: END IF;
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);
2706: END IF;
2707:
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);
2706: END IF;
2707:
2708: EXCEPTION WHEN OTHERS THEN
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);
2706: END IF;
2707:
2708: EXCEPTION WHEN OTHERS THEN
2709: g_error := sqlerrm;
2706: END IF;
2707:
2708: EXCEPTION WHEN OTHERS THEN
2709: g_error := sqlerrm;
2710: bsc_mo_helper_pkg.TerminateWithMsg('Exception in CalcCartesianProduct '||g_error);
2711: RAISE;
2712:
2713: End ;
2714:
2727: end loop;
2728: return gRecDims;
2729: EXCEPTION when others then
2730: g_error := sqlerrm;
2731: bsc_mo_helper_pkg.TerminateWithMsg('Exception in getRecursiveDimensions');
2732: RAISE;
2733: END;
2734:
2735: FUNCTION IsRecursiveKey(pIndicator IN NUMBER, pKey IN VARCHAR2) return BOOLEAN IS
2754: OPEN CV for l_stmt using pIndicator, pKey;
2755: FETCH CV INTO l_num;
2756: IF (CV%FOUND) THEN -- this is a recursive key
2757: CLOSE CV;
2758: bsc_mo_helper_pkg.writeTmp(pKey||' is a recursive key..', FND_LOG.LEVEL_STATEMENT, false);
2759: return true;
2760: END IF;
2761: CLOSE CV;
2762: return false;
2761: CLOSE CV;
2762: return false;
2763: EXCEPTION when others then
2764: g_error := sqlerrm;
2765: bsc_mo_helper_pkg.TerminateWithMsg('Exception in isRecursiveKey for Indicator='||pIndicator||', key='||pKey||':'||g_error);
2766: RAISE;
2767: END;
2768:
2769: --****************************************************************************
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);
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);
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);
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
2835: BEGIN
2836: l_groups := BSC_MO_HELPER_PKG.getGroupIDs(colDimLevelCombinations);
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);
2836: l_groups := BSC_MO_HELPER_PKG.getGroupIDs(colDimLevelCombinations);
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');
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');
2846: bsc_mo_helper_pkg.writeTmp( 'colDataColumns is as above');
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');
2846: bsc_mo_helper_pkg.writeTmp( 'colDataColumns is as above');
2847: END IF;
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');
2846: bsc_mo_helper_pkg.writeTmp( 'colDataColumns is as above');
2847: END IF;
2848: If forTargetLevel Then
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');
2846: bsc_mo_helper_pkg.writeTmp( 'colDataColumns is as above');
2847: END IF;
2848: If forTargetLevel Then
2849: TableNameStart := 'BSC_SB_';
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');
2846: bsc_mo_helper_pkg.writeTmp( 'colDataColumns is as above');
2847: END IF;
2848: If forTargetLevel Then
2849: TableNameStart := 'BSC_SB_';
2850: Else
2855: If NumDimensions = 0 Then
2856: --The indicator does not have dimensions. It does not have any level.
2857: --Only one table
2858: --Name
2859: Basica := BSC_MO_HELPER_PKG.new_clsBasicTable;
2860: Basica.Name := TableNameStart || Indicator.Code || '_'|| Configuration || '_'|| '0';
2861: --Keys
2862: --It does not have. The table only has YEAR TYPE PERIOD
2863: --Confdriles is ''
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;
2870: Basica.keys := Basic_keys;
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;
2870: Basica.keys := Basic_keys;
2871: Basica.data := colDataColumns;
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;
2870: Basica.keys := Basic_keys;
2871: Basica.data := colDataColumns;
2872: --BSC_MO_HELPER_PKG.insertBasicTable(Basica, Basic_keys, colDataColumns);
2868: bsc_mo_helper_pkg.write_this(Basica);
2869: END IF;
2870: Basica.keys := Basic_keys;
2871: Basica.data := colDataColumns;
2872: --BSC_MO_HELPER_PKG.insertBasicTable(Basica, Basic_keys, colDataColumns);
2873: colBasicaTablas(colBasicaTablas.count) := Basica;
2874: Else
2875: --The indicator has at least one dimension
2876: --Calculate the cartesian product between the combinations of each dimension level
2875: --The indicator has at least one dimension
2876: --Calculate the cartesian product between the combinations of each dimension level
2877: NumLevels := 0;
2878: For i in 0..NumDimensions-1 LOOP
2879: l_drillCombination := BSC_MO_HELPER_PKG.get_tab_clsLevelCombinations(colDimLevelCombinations, l_groups(i));
2880: dimensionSizes(i) := l_drillCombination.Count;
2881: If NumLevels = 0 Then
2882: NumLevels := dimensionSizes(i);
2883: Else
2890: --Keys
2891: --Add level from each dimension
2892: lstCodsDriles := null;
2893: ConfDriles := null;
2894: Basica := BSC_MO_HELPER_PKG.new_clsBasicTable;
2895: Basic_keys.delete;
2896: For idimension IN 0..NumDimensions - 1 LOOP
2897: l_index1 := to_number(p_cartesian_product(idimension*NumLevels + iPoints))-1;
2898: l_drillCombination := BSC_MO_HELPER_PKG.get_tab_clsLevelCombinations(colDimLevelCombinations, l_groups(idimension));
2894: Basica := BSC_MO_HELPER_PKG.new_clsBasicTable;
2895: Basic_keys.delete;
2896: For idimension IN 0..NumDimensions - 1 LOOP
2897: l_index1 := to_number(p_cartesian_product(idimension*NumLevels + iPoints))-1;
2898: l_drillCombination := BSC_MO_HELPER_PKG.get_tab_clsLevelCombinations(colDimLevelCombinations, l_groups(idimension));
2899: l_drillString := l_drillCombination(l_index1).Levels;
2900: l_drillTable := bsc_mo_helper_pkg.getDecomposedString(l_drillString, ',');
2901: j := l_drillTable.first;
2902: l_tempv :=null;
2896: For idimension IN 0..NumDimensions - 1 LOOP
2897: l_index1 := to_number(p_cartesian_product(idimension*NumLevels + iPoints))-1;
2898: l_drillCombination := BSC_MO_HELPER_PKG.get_tab_clsLevelCombinations(colDimLevelCombinations, l_groups(idimension));
2899: l_drillString := l_drillCombination(l_index1).Levels;
2900: l_drillTable := bsc_mo_helper_pkg.getDecomposedString(l_drillString, ',');
2901: j := l_drillTable.first;
2902: l_tempv :=null;
2903: LOOP
2904: EXIT WHEN l_drillTable.count = 0;
2902: l_tempv :=null;
2903: LOOP
2904: EXIT WHEN l_drillTable.count = 0;
2905: cLevel := l_drillTable(j);
2906: CampoLlave := bsc_mo_helper_pkg.new_clsKeyField;
2907: CampoLlave.keyName := cLevel;
2908: --NecesitaCod0
2909: --If the level is the first one in the dimension
2910: --is unique in the combination --> true
2908: --NecesitaCod0
2909: --If the level is the first one in the dimension
2910: --is unique in the combination --> true
2911: IF l_DrillTable.Count = 1 Then
2912: l_dimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(idimension));
2913: If UPPER(l_dimLevels(0).keyName) = UPPER(cLevel)
2914: -- AND (NOT IsRecursiveKey(Indicator.code, cLevel))
2915: Then
2916: --If the indicator is a Balance or PnL:
2935: CampoLlave.FilterViewName := GetFilterViewName(Indicator.Code, Configuration, CampoLlave.keyName);
2936: END IF;
2937: --BSC-MV Note: Need this property to store the index of dimension
2938: --within the kpi
2939: l_dimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(idimension));
2940: CampoLlave.dimIndex := l_dimLevels(bsc_mo_helper_pkg.findIndex(l_dimLevels, CampoLlave.keyName)).Num;
2941: --Add the key to the list of keys of the base table
2942: Basic_keys(Basic_keys.count) := CampoLlave;
2943: l_temp := BSC_MO_HELPER_PKG.findIndex(l_dimLevels, CampoLlave.keyName);
2936: END IF;
2937: --BSC-MV Note: Need this property to store the index of dimension
2938: --within the kpi
2939: l_dimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(idimension));
2940: CampoLlave.dimIndex := l_dimLevels(bsc_mo_helper_pkg.findIndex(l_dimLevels, CampoLlave.keyName)).Num;
2941: --Add the key to the list of keys of the base table
2942: Basic_keys(Basic_keys.count) := CampoLlave;
2943: l_temp := BSC_MO_HELPER_PKG.findIndex(l_dimLevels, CampoLlave.keyName);
2944: lstCodsDriles := lstCodsDriles || l_dimLevels(l_temp).Num;
2939: l_dimLevels := bsc_mo_helper_pkg.get_tab_clsLevels(p_dimension_families, l_groups(idimension));
2940: CampoLlave.dimIndex := l_dimLevels(bsc_mo_helper_pkg.findIndex(l_dimLevels, CampoLlave.keyName)).Num;
2941: --Add the key to the list of keys of the base table
2942: Basic_keys(Basic_keys.count) := CampoLlave;
2943: l_temp := BSC_MO_HELPER_PKG.findIndex(l_dimLevels, CampoLlave.keyName);
2944: lstCodsDriles := lstCodsDriles || l_dimLevels(l_temp).Num;
2945: EXIT WHEN j = l_drillTable.last;
2946: j:= l_drillTable.next(j);
2947: END LOOP;
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
2959: Basica.levelConfig := order_level_string(ConfDriles, p_dimension_families);
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
2959: Basica.levelConfig := order_level_string(ConfDriles, p_dimension_families);
2960: --Put ? at the begining in case the indicator is Balance or PnL because the drill 0
2966: --TablaOri is ''
2967: Basica.keys := Basic_Keys;
2968: Basica.Data := colDataColumns;
2969: colBasicaTablas(colBasicaTablas.count) := Basica;
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
2969: colBasicaTablas(colBasicaTablas.count) := Basica;
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;
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;
2979:
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;
2979:
2980: EXCEPTION WHEN OTHERS THEN
2977: END IF;
2978: return colBasicaTablas;
2979:
2980: EXCEPTION WHEN OTHERS THEN
2981: bsc_mo_helper_pkg.TerminateWithError('BSC_BASICTABLE_DEDUCT_FAILED');
2982: fnd_message.set_name('BSC', 'BSC_BASICTABLE_DEDUCT_FAILED');
2983: fnd_message.set_token('INDICATOR', Indicator.code);
2984: fnd_message.set_token('DIMENSION_SET', Configuration);
2985:
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);
3012: --Delete the records if exists
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);
3012: --Delete the records if exists
3013: l_stmt := 'DELETE FROM BSC_DB_MEASURE_COLS_TL WHERE MEASURE_COL = :1';
3014: EXECUTE IMMEDIATE l_stmt using p_measure.fieldName;
3015:
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:
3034: EXCEPTION WHEN OTHERS THEN
3032:
3033:
3034: EXCEPTION WHEN OTHERS THEN
3035: g_error := sqlerrm;
3036: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InsertInDBMeasureCols '||g_error);
3037: RAISE;
3038:
3039: End;
3040:
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;
3065:
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;
3065:
3066: l_measure.fieldName := internalColumn;
3067: l_measure.source := 'BSC';
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:
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
3075: --The projection method of the calculated column is deduced from the
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;
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;
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:
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;
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);
3162:
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:
3168: EXCEPTION WHEN OTHERS THEN
3166:
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:
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:
3194: AND TYPE = 0 AND NVL(SOURCE, 'BSC') = 'BSC';
3195:
3196: EXCEPTION WHEN OTHERS THEN
3197: g_error := sqlerrm;
3198: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in SetMeasurePropertyDB : '||g_error);
3199: raise;
3200: End;
3201:
3202:
3251: close c1;
3252:
3253: EXCEPTION WHEN OTHERS THEN
3254: g_error := sqlerrm;
3255: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetAggregateFunction '||g_error);
3256: RAISE;
3257:
3258: End;
3259:
3307: return false;
3308:
3309: EXCEPTION WHEN OTHERS THEN
3310: g_error := sqlerrm;
3311: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception dataFieldExists, '||g_error);
3312: raise;
3313: End;
3314:
3315: --****************************************************************************
3345: END LOOP;
3346: return false;
3347: EXCEPTION WHEN OTHERS THEN
3348: g_error := sqlerrm;
3349: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception dataFieldExistsForSource, '||g_error);
3350: raise;
3351: End;
3352:
3353: --****************************************************************************
3383: END LOOP;
3384:
3385: --Break down the expression which is separated by ' '
3386:
3387: NumCampos := BSC_MO_HELPER_PKG.DecomposeString(cExpresion, ' ', Campos);
3388: NumCampos := Campos.count;
3389: NumCamposExpresion := 0;
3390: i:= Campos.first;
3391: LOOP
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
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
3399: --the word is not a constant
3400: CamposExpresion(NumCamposExpresion) := Campos(i);
3401: NumCamposExpresion := NumCamposExpresion + 1;
3402: END IF;
3408: END LOOP;
3409: return NumCamposExpresion;
3410:
3411: EXCEPTION WHEN OTHERS THEN
3412: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetFieldExpresion : '||sqlerrm);
3413: raise;
3414:
3415: End;
3416:
3492: l_source VARCHAR2(100);
3493: cv CurTyp;
3494:
3495: BEGIN
3496: bsc_mo_helper_pkg.writeTmp( 'Inside GetDataFields, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, false);
3497: select short_name into l_source from bsc_kpis_vl
3498: where indicator=Indic;
3499: -- Bug 4301819
3500: -- Dont include PMF measures if this is created by objective definer
3535: FOR i IN l_measure_names_list.first..l_measure_names_list.last LOOP
3536: If fieldExistsINLOV(l_measure_names_list(i), l_source) Then
3537: If Not DataFieldExists(l_col_measure_columns, l_measure_names_list(i)) Then
3538: --Get the aggregation function and Avgl flag of the column (single column)
3539: bsc_mo_helper_pkg.writeTmp('Getting the aggregate function for '||l_measure_names_list(i));
3540: GetAggregateFunction (l_measure_names_list(i), FuncAgregSingleColumn, pAvgLSingleColumn, AvgLTotalColumn, AvgLCounterColumn);
3541: bsc_mo_helper_pkg.writeTmp('FuncAgregSingleColumn='||FuncAgregSingleColumn||', pAvgLSingleColumn='||pAvgLSingleColumn
3542: ||', AvgLTotalColumn='||AvgLTotalColumn||', AvgLCounterColumn='||AvgLCounterColumn);
3543: If FuncAgregSingleColumn IS NULL Then
3537: If Not DataFieldExists(l_col_measure_columns, l_measure_names_list(i)) Then
3538: --Get the aggregation function and Avgl flag of the column (single column)
3539: bsc_mo_helper_pkg.writeTmp('Getting the aggregate function for '||l_measure_names_list(i));
3540: GetAggregateFunction (l_measure_names_list(i), FuncAgregSingleColumn, pAvgLSingleColumn, AvgLTotalColumn, AvgLCounterColumn);
3541: bsc_mo_helper_pkg.writeTmp('FuncAgregSingleColumn='||FuncAgregSingleColumn||', pAvgLSingleColumn='||pAvgLSingleColumn
3542: ||', AvgLTotalColumn='||AvgLTotalColumn||', AvgLCounterColumn='||AvgLCounterColumn);
3543: If FuncAgregSingleColumn IS NULL Then
3544: FuncAgregSingleColumn := FuncAgreg;
3545: END IF;
3545: END IF;
3546: If pAvgLSingleColumn IS NULL Then
3547: pAvgLSingleColumn := pAvgL;
3548: END IF;
3549: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3550: l_measure_column.fieldName := l_measure_names_list(i);
3551: l_measure_column.source := nvl(l_source, 'BSC');
3552: l_measure_column.aggFunction := FuncAgregSingleColumn;
3553: --l_measure_column.Origen is not set
3551: l_measure_column.source := nvl(l_source, 'BSC');
3552: l_measure_column.aggFunction := FuncAgregSingleColumn;
3553: --l_measure_column.Origen is not set
3554: l_measure_column.AvgLFlag := pAvgLSingleColumn;
3555: bsc_mo_helper_pkg.writeTmp('l_measure_column.fieldName='||l_measure_column.fieldName||',l_measure_column.aggFunction='||
3556: l_measure_column.aggFunction||', pAvgLSingleColumn='||pAvgLSingleColumn);
3557: If pAvgLSingleColumn = 'Y' And WithInternalColumns Then
3558: --This is a single column, we can have AvgL on a single column.
3559: --We need to internal columns: one for total and one for counter
3579: l_col_measure_columns(l_col_measure_columns.count) := l_measure_column;
3580: If pAvgLSingleColumn = 'Y' And WithInternalColumns Then
3581: --Add the two internal column for AvgL in the collection
3582: --Column for Total
3583: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3584: l_measure_column.fieldName := AvgLTotalColumn;
3585: l_measure_column.source := nvl(l_source, 'BSC');
3586: l_measure_column.aggFunction := 'SUM';
3587: --l_measure_column.Origen is not set
3591: l_measure_column.InternalColumnType := 2; --Internal column for Total of AvgL
3592: l_measure_column.InternalColumnSource := l_measure_names_list(i);
3593: l_col_measure_columns(l_col_measure_columns.count) := l_measure_column;
3594: --Column for Counter
3595: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3596: l_measure_column.fieldName := AvgLCounterColumn;
3597: l_measure_column.source := nvl(l_source, 'BSC');
3598: l_measure_column.aggFunction := 'SUM';
3599: --l_measure_column.Origen is not set
3614: fnd_message.set_name('BSC', 'BSC_PMA_OPT_DUP_MEASURE');
3615: fnd_message.set_token('OBJECTIVE', Indic);
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;
3615: fnd_message.set_token('OBJECTIVE', Indic);
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;
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);
3634: raise bsc_metadata_optimizer_pkg.optimizer_exception ;
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);
3634: raise bsc_metadata_optimizer_pkg.optimizer_exception ;
3635: EXIT ;
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);
3634: raise bsc_metadata_optimizer_pkg.optimizer_exception ;
3635: EXIT ;
3636: END IF;
3637: END LOOP;
3639: If WithInternalColumns Then
3640: If pFormulaSource IS NOT NULL Then
3641: --Add the internal column in gLov and in BSC_DB_MEASURES_COLS_TL table
3642: AddInternalColumnInDB(pFormulaSource, 1, l_measure_names_list, l_num_measures);
3643: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3644: l_measure_column.fieldName := pFormulaSource;
3645: l_measure_column.source := nvl(l_source, 'BSC');
3646: l_measure_column.aggFunction := FuncAgreg;
3647: --l_measure_column.Origen is not set
3677: --We need to evaluate this situation adding te condition
3678: --If Not ExisteCampoDato(l_col_measure_columns,
3679: --Column for Total
3680: If Not DataFieldExists(l_col_measure_columns, pAvgLTotal) Then
3681: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3682: l_measure_column.fieldName := pAvgLTotal;
3683: l_measure_column.source := nvl(l_source, 'BSC');
3684: l_measure_column.aggFunction := 'SUM';
3685: --l_measure_column.Origen is not set
3689: l_col_measure_columns(l_col_measure_columns.last+1) := l_measure_column ;
3690: END IF;
3691: --Column for Counter
3692: If Not DataFieldExists(l_col_measure_columns, pAvgLCounter) Then
3693: l_measure_column := bsc_mo_helper_pkg.new_clsDataField;
3694: l_measure_column.fieldName := pAvgLCounter;
3695: l_measure_column.source := nvl(l_source, 'BSC');
3696: l_measure_column.aggFunction := 'SUM';
3697: --l_measure_column.Origen is not set
3706: END IF;
3707: END IF;
3708: END Loop;
3709: close cv;
3710: bsc_mo_helper_pkg.writeTmp( 'Compl. GetDataFields, System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, false);
3711: return l_col_measure_columns;
3712: EXCEPTION WHEN OTHERS THEN
3713: fnd_message.set_name('BSC', 'BSC_BASICTABLE_DEDUCT_FAILED');
3714: fnd_message.set_token('INDICATOR', Indic);
3713: fnd_message.set_name('BSC', 'BSC_BASICTABLE_DEDUCT_FAILED');
3714: fnd_message.set_token('INDICATOR', Indic);
3715: fnd_message.set_token('DIMENSION_SET', Configuration);
3716: g_error := fnd_message.get;
3717: bsc_mo_helper_pkg.terminatewithMsg(g_error);
3718: raise;
3719: --app_exception.raise_exception;
3720:
3721: End;
3775: i := StringCombination1.first;
3776: END IF;
3777: LOOP
3778: EXIT WHEN StringCombination1.count =0;
3779: Combination1 := BSC_MO_HELPER_PKG.getDecomposedString(StringCombination1(i), ',');
3780: Combination.delete;
3781: IF (Combination1.count >0) THEN
3782: j:= Combination1.first;
3783: LOOP
3790: EXIT WHEN j = Combination1.last;
3791: j := Combination1.next(j);
3792: END LOOP;
3793: END IF;
3794: StringCombination(StringCombination.count) := BSC_MO_HELPER_PKG.ConsolidateString(Combination, ',');
3795: EXIT WHEN i = StringCombination1.last;
3796: i := StringCombination1.next(i);
3797: END LOOP;
3798: IF (StringCombination1.count > 0) THEN
3799: i := StringCombination1.first;
3800: END IF;
3801: LOOP
3802: EXIT WHEN StringCombination1.count =0;
3803: Combination1 := BSC_MO_HELPER_PKG.getDecomposedString(StringCombination1(i), ',');
3804: Rel1NExists := False;
3805: Combination.delete;
3806: str := combo(combo.first) ;
3807: Combination(Combination.count) := str;
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;
3825: EXIT WHEN j = Combination1.last;
3826: j := Combination1.next(j);
3827: END LOOP;
3828: If Not Rel1NExists Then
3829: StringCombination(StringCombination.count) := BSC_MO_HELPER_PKG.ConsolidateString(Combination, ',');
3830: END IF;
3831: EXIT WHEN i = StringCombination1.last;
3832: i := StringCombination1.next(i);
3833: END LOOP;
3830: END IF;
3831: EXIT WHEN i = StringCombination1.last;
3832: i := StringCombination1.next(i);
3833: END LOOP;
3834: bsc_mo_helper_pkg.write_this(StringCombination);
3835: RETURN StringCombination;
3836: EXCEPTION WHEN OTHERS THEN
3837: g_error := sqlerrm;
3838: bsc_mo_helper_pkg.terminateWithMsg( 'Exception in GetStrCombinationsMN '||g_error);
3834: bsc_mo_helper_pkg.write_this(StringCombination);
3835: RETURN StringCombination;
3836: EXCEPTION WHEN OTHERS THEN
3837: g_error := sqlerrm;
3838: bsc_mo_helper_pkg.terminateWithMsg( 'Exception in GetStrCombinationsMN '||g_error);
3839: RAISE;
3840: End;
3841:
3842: --****************************************************************************
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);
3906:
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);
3906:
3907: l_groups := BSC_MO_HELPER_PKG.getGroupIds(p_dimension_families);
3908: IF (l_groups.count >0) THEN
3909: l_ct1 := l_groups.first ;
3903: END IF;
3904:
3905: bsc_mo_helper_pkg.write_this(p_dimension_families);
3906:
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
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;
3916: END IF;
3919: --For each drill family, it get the collection of drill combinations
3920:
3921: LOOP
3922: DimLevelCombinations.delete;
3923: DimensionLevels := BSC_MO_HELPER_PKG.get_tab_clsLevels(p_dimension_families, l_groups(l_ct1));
3924: --It creates a new element clsCombinacsFliasDriles for the current drill family
3925: --Go through the list of drills from the end to the beginning of the current drill family
3926: IF (length(l_stack) > 31000) THEN
3927: l_stack := null;
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;
3998:
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;
3998:
3999: conjCombinacsMN := GetStrCombinationsMN(colRelsMN);
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;
3998:
3999: conjCombinacsMN := GetStrCombinationsMN(colRelsMN);
4000: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
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;
4005:
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;
4005:
4006: l_stack := l_stack || ' GetLevelCombinations - 4.22';
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;
4005:
4006: l_stack := l_stack || ' GetLevelCombinations - 4.22';
4007:
4013:
4014: l_stack := l_stack || ' GetLevelCombinations - 4.25';
4015: EXIT WHEN conjCombinacsMN.count=0;
4016: l_stack := l_stack || ' GetLevelCombinations - 4.3';
4017: l_dummy := BSC_MO_HELPER_PKG.decomposeString(conjCombinacsMN(l_ct3), ',', CombinacMN);
4018: --For each combination MN it has to create an element in the list of drill combinations
4019: --for the family being analyzed
4020: --Drills
4021: --The list of drills is made up of elements of the combination mn andthe current drill
4018: --For each combination MN it has to create an element in the list of drill combinations
4019: --for the family being analyzed
4020: --Drills
4021: --The list of drills is made up of elements of the combination mn andthe current drill
4022: LevelCombinations := bsc_mo_helper_pkg.new_clsLevelCombinations;
4023: l_ct2 := CombinacMN.count;
4024: IF (l_ct2 >0) THEN
4025: l_ct2 := CombinacMN.first;
4026: END IF;
4057: --Character corresponding to the left of the current drill
4058: indexDrilComparar := idril;
4059: --jDril := idril -1;
4060: FOR jDril IN REVERSE 0..idril-1 LOOP
4061: If BSC_MO_HELPER_PKG.findindexVARCHAR2(CombinacMN, DimensionLevels(jDril).keyName) >= 0 Then
4062: --the drill belong to the current mn combination
4063: ConfDriles := '0' || ConfDriles;
4064: indexDrilComparar := jDril;
4065: Else
4062: --the drill belong to the current mn combination
4063: ConfDriles := '0' || ConfDriles;
4064: indexDrilComparar := jDril;
4065: Else
4066: l_dummy := BSC_MO_HELPER_PKG.decomposeString(DimensionLevels(indexDrilComparar).Parents1N, ',', l_varchar_table);
4067: If BSC_MO_HELPER_PKG.findindexVARCHAR2(l_varchar_table,
4068: DimensionLevels(jDril).keyName) >= 0 Then
4069: ConfDriles := '?'||ConfDriles;
4070: Else
4063: ConfDriles := '0' || ConfDriles;
4064: indexDrilComparar := jDril;
4065: Else
4066: l_dummy := BSC_MO_HELPER_PKG.decomposeString(DimensionLevels(indexDrilComparar).Parents1N, ',', l_varchar_table);
4067: If BSC_MO_HELPER_PKG.findindexVARCHAR2(l_varchar_table,
4068: DimensionLevels(jDril).keyName) >= 0 Then
4069: ConfDriles := '?'||ConfDriles;
4070: Else
4071: ConfDriles := '1' || ConfDriles;
4090: idril := DimensionLevels.prior(idril);
4091: END LOOP;
4092: l_stack := l_stack || ' GetLevelCombinations - 5';
4093:
4094: bsc_mo_helper_pkg.add_tabrec_clsLevelComb(colDimLevelCombinations, DimLevelCombinations, l_groups(l_ct1));
4095:
4096: EXIT WHEN l_ct1 = l_groups.last;
4097: l_ct1 := l_groups.next(l_ct1);
4098:
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:
4106: return colDimLevelCombinations;
4105:
4106: return colDimLevelCombinations;
4107: EXCEPTION WHEN OTHERS THEN
4108: DrilC := sqlerrm;
4109: bsc_mo_helper_pkg.terminateWithMsg('Exception in GetLevelCOmbinations : '||drilC);
4110: FND_FILE.put_line(FND_FILE.LOG, l_stack);
4111: raise;
4112: End;
4113:
4138: BEGIN
4139: IF (tabtabDrills.count =0 ) THEN
4140: return -1;
4141: END IF;
4142: l_groups := BSC_MO_HELPER_PKG.getgroupids(tabtabDrills);
4143: --l_ct := l_groups.first;
4144: FOR l_ct IN l_groups.first..l_groups.last LOOP
4145: l_levels := BSC_MO_HELPER_PKG.get_tab_clsLevels(tabtabDrills, l_groups(l_ct));
4146: --l_ct2 := l_levels.first;
4141: END IF;
4142: l_groups := BSC_MO_HELPER_PKG.getgroupids(tabtabDrills);
4143: --l_ct := l_groups.first;
4144: FOR l_ct IN l_groups.first..l_groups.last LOOP
4145: l_levels := BSC_MO_HELPER_PKG.get_tab_clsLevels(tabtabDrills, l_groups(l_ct));
4146: --l_ct2 := l_levels.first;
4147: FOR l_ct2 IN l_levels.first..l_levels.last LOOP
4148: l_level := l_levels(l_ct2);
4149: If IndexRelation1N(dimTable, l_level.dimTable) >= 0 Then
4147: FOR l_ct2 IN l_levels.first..l_levels.last LOOP
4148: l_level := l_levels(l_ct2);
4149: If IndexRelation1N(dimTable, l_level.dimTable) >= 0 Then
4150: -- check none of the other levels have this as a parent level
4151: /*l_dummy := BSC_MO_HELPER_PKG.decomposeString(l_level.Parents1N, ',', l_varchar_table);
4152: IF (l_varchar_table.count >0) THEN
4153: FOR k IN l_varchar_table.first..l_varchar_table.last LOOP
4154: If IndexRelation1N(l_varchar_Table(k), l_level.dimTable) >= 0 THEN
4155: return -1;
4170: END LOOP;
4171: return -1;
4172: EXCEPTION WHEN OTHERS THEN
4173: g_error := sqlerrm;
4174: bsc_mo_helper_pkg.terminateWithMsg('Exception in get_dimension_family '||g_error);
4175: RAISE;
4176: End ;
4177:
4178:
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';
4229: ----dbms_output.put_line('Chk1');
4236: OPEN cv FOR l_stmt using Indic, Configuration;
4237: LOOP
4238: Fetch cv into l_dim_index, l_level_table, l_level_pk_col, Name, TargetLevel;
4239: EXIT WHEN cv%NOTFOUND;
4240: cLevel := bsc_mo_helper_pkg.new_clsLevels;
4241: cLevel.keyName := l_level_pk_col;
4242: cLevel.dimTable := l_level_table;
4243: cLevel.Num := l_dim_index;
4244: cLevel.Name := Name;
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
4251: bsc_mo_helper_pkg.writeTmp('DimensionLevelsNum = '||DimensionLevelsNum);
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
4255: IF (BSC_METADATA_OPTIMIZER_PKG.g_log) THEN
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
4260: --relationship with this one
4257: END IF;
4258: --Level belongs to family DimensionLevelsNum.
4259: --Check each level of this family and see which drill has 1n or mn
4260: --relationship with this one
4261: DimensionLevels := BSC_MO_HELPER_PKG.get_tab_clsLevels(l_dimension_families, DimensionLevelsNum);
4262: l_count := DimensionLevels.first;
4263: LOOP
4264: EXIT WHEN DimensionLevels.count = 0;
4265: tdril := bsc_mo_helper_pkg.new_clsLevels;
4261: DimensionLevels := BSC_MO_HELPER_PKG.get_tab_clsLevels(l_dimension_families, DimensionLevelsNum);
4262: l_count := DimensionLevels.first;
4263: LOOP
4264: EXIT WHEN DimensionLevels.count = 0;
4265: tdril := bsc_mo_helper_pkg.new_clsLevels;
4266: tDril := DimensionLevels(l_count);
4267: If IndexRelation1N(l_level_table, tDril.dimTable) >= 0 Then
4268: --There is 1n relationship with this drill
4269: l_parents1n := tDril.keyName;
4272: END IF;
4273: cLevel.Parents1N := cLevel.Parents1N||l_parents1n;
4274: --The 1n relations of the parent drill are also (by transitivity)
4275: --1n with the current drill
4276: tDril_parents1N := bsc_mo_helper_pkg.getDecomposedString(tDril.Parents1N, ',');
4277: IF (tDril_parents1N.count>0)THEN
4278: l_ct := tDril_parents1N.first;
4279: LOOP
4280: tPadre1n := tDril_parents1N(l_ct);
4287: END LOOP;
4288: END IF;
4289: --The mn relations of the parent drill are also (by transitivity)
4290: --mn with the current drill
4291: cDril_parentsMN := bsc_mo_helper_pkg.getDecomposedString(cLevel.ParentsMN, ',');
4292: tDril_parentsMN := bsc_mo_helper_pkg.getDecomposedString(tDril.ParentsMN, ',');
4293: IF tDril_parentsMN.count > 0 THEN
4294: l_ct := tDril_parentsMN.first;
4295: LOOP
4288: END IF;
4289: --The mn relations of the parent drill are also (by transitivity)
4290: --mn with the current drill
4291: cDril_parentsMN := bsc_mo_helper_pkg.getDecomposedString(cLevel.ParentsMN, ',');
4292: tDril_parentsMN := bsc_mo_helper_pkg.getDecomposedString(tDril.ParentsMN, ',');
4293: IF tDril_parentsMN.count > 0 THEN
4294: l_ct := tDril_parentsMN.first;
4295: LOOP
4296: tPadremn := tDril_parentsMN(l_ct);
4311: END IF;
4312: cLevel.ParentsMN := cLevel.ParentsMN|| l_parentsMN;
4313: --The 1n relations of the parent drill are also (by transitivity)
4314: --mn with the current drill
4315: tDril_parents1n := bsc_mo_helper_pkg.getDecomposedString(tDril.Parents1N, ',');
4316: IF (tDril_parents1N.count >0) THEN
4317: l_ct := tDril_parents1N.first;
4318: LOOP
4319: tPadre1n := tDril_parents1N(l_ct) ;
4326: END LOOP;
4327: END IF;
4328: --The mn relations of the parent drill are also (by transitivity)
4329: --mn with the current drill
4330: tDril_parentsMN := bsc_mo_helper_pkg.getDecomposedString(tDril.ParentsMN, ',');
4331: IF (tDril_parentsMN.count >0) THEN
4332: l_ct := tDril_parentsMN.first;
4333: LOOP
4334: tPadremn := tDril_parentsMN(l_ct) ;
4360: END IF;
4361: END IF;
4362: DimensionLevels.delete;
4363: DimensionLevels(0) := cLevel;
4364: bsc_mo_helper_pkg.add_tabrec_clsLevels(l_dimension_families, DimensionLevels, DimensionLevelsNum);
4365: Else
4366: --The drill does not belong to any family previously created.
4367: --So, create a new family of drill with this drill
4368: --Review target level
4369: --This is the first drill in this family, then target must apply
4370: cLevel.TargetLevel := 1;
4371: DimensionLevels.delete;
4372: DimensionLevels(0) := cLevel;
4373: bsc_mo_helper_pkg.add_tabrec_clsLevels(l_dimension_families, DimensionLevels, l_group_id);
4374: l_group_id := l_group_id +1;
4375: END IF;--If DimensionLevelsNum <> 0
4376: END Loop;
4377: close cv;
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;
4383: EXCEPTION WHEN OTHERS THEN
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;
4383: EXCEPTION WHEN OTHERS THEN
4384: l_stmt := sqlerrm;
4381: END IF;
4382: return l_dimension_families;
4383: EXCEPTION WHEN OTHERS THEN
4384: l_stmt := sqlerrm;
4385: bsc_mo_helper_pkg.TerminateWithMsg( ' Exception in GetLevelCollection : '||l_stmt);
4386: fnd_message.set_name('BSC', 'BSC_RETR_DIM_KPI_FAILED');
4387: fnd_message.set_token('INDICATOR', Indic);
4388: fnd_message.set_token('DIMENSION_SET', Configuration);
4389: g_error := fnd_message.get;
4386: fnd_message.set_name('BSC', 'BSC_RETR_DIM_KPI_FAILED');
4387: fnd_message.set_token('INDICATOR', Indic);
4388: fnd_message.set_token('DIMENSION_SET', Configuration);
4389: g_error := fnd_message.get;
4390: bsc_mo_helper_pkg.terminatewithMsg(g_error);
4391: raise;
4392: --app_exception.raise_exception;
4393:
4394:
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:
4419: atLeastOneChange := True;
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:
4419: atLeastOneChange := True;
4420:
4429: --This periodicity has not been selected
4430: If GetPeriodicityOrigin(colPeriodicities, indicPer.Code, True) <> -1 Then
4431: indicPer.TargetLevel := 1;
4432: atLeastOneChange := True;
4433: bsc_mo_helper_pkg.writeTmp('atLeastOneChange is true');
4434: colPeriodicities(l_count) := indicPer;
4435: END IF;
4436: END IF;
4437: EXIT WHEN l_count = colPeriodicities.last;
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:
4447: EXCEPTION WHEN OTHERS THEN
4445:
4446:
4447: EXCEPTION WHEN OTHERS THEN
4448: G_ERROR := sqlerrm;
4449: bsc_mo_helper_pkg.TerminateWithMsg( 'Exception in FlagTLOtherPeriodicities '||G_ERROR);
4450: RAISE;
4451:
4452: End;
4453:
4490: return colPeriodicities;
4491:
4492:
4493: EXCEPTION WHEN OTHERS THEN
4494: BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_KPI_PERIOD_FAILED');
4495: fnd_message.set_name('BSC', 'BSC_RETR_KPI_PERIOD_FAILED');
4496: fnd_message.set_token('INDICATOR', Indic);
4497: app_exception.raise_exception;
4498:
4534: i NUMBER;
4535: iNext NUMBER;
4536: bLast boolean;
4537: BEGIN
4538: bsc_mo_helper_pkg.writeTmp(' ', fnd_log.level_statement, false);
4539: bsc_mo_helper_pkg.writeTmp( 'Inside ConfigureIndics for '||Indicator.code||', dimension set = '||Configuration||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
4540: g_current_dimset := Configuration;
4541: g_current_indicator := Indicator;
4542: If Indicator.Share_Flag = 0 Or Indicator.Share_Flag = 1 Or
4535: iNext NUMBER;
4536: bLast boolean;
4537: BEGIN
4538: bsc_mo_helper_pkg.writeTmp(' ', fnd_log.level_statement, false);
4539: bsc_mo_helper_pkg.writeTmp( 'Inside ConfigureIndics for '||Indicator.code||', dimension set = '||Configuration||', System time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
4540: g_current_dimset := Configuration;
4541: g_current_indicator := Indicator;
4542: If Indicator.Share_Flag = 0 Or Indicator.Share_Flag = 1 Or
4543: (
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;
4566: --Get the list of combinations of levels of each familiy
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;
4566: --Get the list of combinations of levels of each familiy
4567: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
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;
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 ');
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
4572: bsc_mo_helper_pkg.write_this(colDrillCombination);
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
4576: --The indicator needs targets at different levels
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
4576: --The indicator needs targets at different levels
4577: colDrillCombinationTL := GetLevelCombinations(colDrills, True);
4574: END IF;
4575: If Indicator.OptimizationMode = 2 Then
4576: --The indicator needs targets at different levels
4577: colDrillCombinationTL := GetLevelCombinations(colDrills, True);
4578: bsc_mo_helper_pkg.write_this(colDrillCombinationTL);
4579: END IF;
4580: --Get the list of data columns of the indicator in the given configuration
4581: colDataColumns := GetDataFields(Indicator.Code, Configuration, True);
4582: bsc_mo_helper_pkg.write_this(colDataColumns);
4578: bsc_mo_helper_pkg.write_this(colDrillCombinationTL);
4579: END IF;
4580: --Get the list of data columns of the indicator in the given configuration
4581: colDataColumns := GetDataFields(Indicator.Code, Configuration, True);
4582: bsc_mo_helper_pkg.write_this(colDataColumns);
4583: --Generate the list of base tables for the indicator
4584: colBasicTables := GetBasicTables(Indicator, Configuration, colDrillCombination, colDrills, False, colDataColumns);
4585: If Indicator.OptimizationMode = 2 Then
4586: --The indicator needs targets at different levels
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
4631: EXIT WHEN bLast = true;
4632: END Loop;
4633: End If;
4634: END IF;
4635: bsc_mo_helper_pkg.writeTmp( 'Compl ConfigureIndics, system time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
4636: EXCEPTION WHEN OTHERS THEN
4637: bsc_mo_helper_pkg.writeTmp( 'Exception in ConfigureIndics : '||sqlerrm, fnd_log.level_exception, true);
4638: fnd_message.set_name('BSC', 'BSC_KPI_TBLS_SET_DEDUCT_FAILED');
4639: fnd_message.set_token('INDICATOR', Indicator.code);
4633: End If;
4634: END IF;
4635: bsc_mo_helper_pkg.writeTmp( 'Compl ConfigureIndics, system time is '||bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
4636: EXCEPTION WHEN OTHERS THEN
4637: bsc_mo_helper_pkg.writeTmp( 'Exception in ConfigureIndics : '||sqlerrm, fnd_log.level_exception, true);
4638: fnd_message.set_name('BSC', 'BSC_KPI_TBLS_SET_DEDUCT_FAILED');
4639: fnd_message.set_token('INDICATOR', Indicator.code);
4640: fnd_message.set_token('DIMENSION_SET', Configuration);
4641: g_error := fnd_message.get ;
4638: fnd_message.set_name('BSC', 'BSC_KPI_TBLS_SET_DEDUCT_FAILED');
4639: fnd_message.set_token('INDICATOR', Indicator.code);
4640: fnd_message.set_token('DIMENSION_SET', Configuration);
4641: g_error := fnd_message.get ;
4642: bsc_mo_helper_pkg.terminatewithMsg(g_error);
4643: raise;
4644: End;
4645:
4646:
4676: CLOSE cConfigs;
4677: return colConfigurations;
4678: EXCEPTION WHEN OTHERS THEN
4679: g_error := sqlerrm;
4680: bsc_mo_helper_pkg.TerminateWithMsg(' Exception in GetColConfigForIndic : '||g_error);
4681: fnd_message.set_name('BSC', 'BSC_RETR_DIMSET_KPI_FAILED');
4682: fnd_message.set_token('INDICATOR', Indic);
4683: app_exception.raise_exception;
4684: End;
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:
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
4724: IF Indicator.Action_Flag = 3
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
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);
4731: LOOP
4732: EXIT WHEN colConfigurations.count = 0;
4733: l_configuration := colConfigurations(l_configs);
4734: bsc_mo_helper_pkg.writeTmp('Processing Indicator='||Indicator.Code||', dim set='||l_configuration||':'||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
4730: bsc_mo_helper_pkg.writeTmp('colConfigurations.count = '||colConfigurations.count);
4731: LOOP
4732: EXIT WHEN colConfigurations.count = 0;
4733: l_configuration := colConfigurations(l_configs);
4734: bsc_mo_helper_pkg.writeTmp('Processing Indicator='||Indicator.Code||', dim set='||l_configuration||':'||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
4735: ConfigureIndics(Indicator, l_configuration);
4736: EXIT WHEN l_configs = colConfigurations.last;
4737: l_configs := colConfigurations.next(l_configs);
4738: END LOOP;
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;
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);
4751: exception when others then
4752: g_error := sqlerrm;
4753: BSC_MO_HELPER_PKG.TerminateWithMsg('IndicatorTables failed with : '||g_error);
4754: raise;
4749: ConfigureMasterSharedIndics;
4750: bsc_mo_helper_pkg.writeTmp( 'Compl IndicatorTables', fnd_log.level_statement, true);
4751: exception when others then
4752: g_error := sqlerrm;
4753: BSC_MO_HELPER_PKG.TerminateWithMsg('IndicatorTables failed with : '||g_error);
4754: raise;
4755: --app_exception.raise_exception;
4756: End;
4757: END BSC_MO_INDICATOR_PKG;