[Home] [Help]
42: PROCEDURE Initialize IS
43:
44: BEGIN
45: IF (g_mode = 2) then -- called from separate conc. program
46: BSC_METADATA_OPTIMIZER_PKG.g_dir := null;
47: BSC_METADATA_OPTIMIZER_PKG.g_dir:=fnd_profile.value('UTL_FILE_LOG');
48: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
49: BSC_METADATA_OPTIMIZER_PKG.g_debug := TRUE;
50: BSC_METADATA_OPTIMIZER_PKG.g_log_level := fnd_profile.value('AFLOG_LEVEL');
43:
44: BEGIN
45: IF (g_mode = 2) then -- called from separate conc. program
46: BSC_METADATA_OPTIMIZER_PKG.g_dir := null;
47: BSC_METADATA_OPTIMIZER_PKG.g_dir:=fnd_profile.value('UTL_FILE_LOG');
48: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
49: BSC_METADATA_OPTIMIZER_PKG.g_debug := TRUE;
50: BSC_METADATA_OPTIMIZER_PKG.g_log_level := fnd_profile.value('AFLOG_LEVEL');
51: ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
45: IF (g_mode = 2) then -- called from separate conc. program
46: BSC_METADATA_OPTIMIZER_PKG.g_dir := null;
47: BSC_METADATA_OPTIMIZER_PKG.g_dir:=fnd_profile.value('UTL_FILE_LOG');
48: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
49: BSC_METADATA_OPTIMIZER_PKG.g_debug := TRUE;
50: BSC_METADATA_OPTIMIZER_PKG.g_log_level := fnd_profile.value('AFLOG_LEVEL');
51: ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
52: BSC_METADATA_OPTIMIZER_PKG.g_log_level := FND_LOG.g_current_runtime_level;
53: END IF;
46: BSC_METADATA_OPTIMIZER_PKG.g_dir := null;
47: BSC_METADATA_OPTIMIZER_PKG.g_dir:=fnd_profile.value('UTL_FILE_LOG');
48: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
49: BSC_METADATA_OPTIMIZER_PKG.g_debug := TRUE;
50: BSC_METADATA_OPTIMIZER_PKG.g_log_level := fnd_profile.value('AFLOG_LEVEL');
51: ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
52: BSC_METADATA_OPTIMIZER_PKG.g_log_level := FND_LOG.g_current_runtime_level;
53: END IF;
54:
48: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
49: BSC_METADATA_OPTIMIZER_PKG.g_debug := TRUE;
50: BSC_METADATA_OPTIMIZER_PKG.g_log_level := fnd_profile.value('AFLOG_LEVEL');
51: ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
52: BSC_METADATA_OPTIMIZER_PKG.g_log_level := FND_LOG.g_current_runtime_level;
53: END IF;
54:
55: IF BSC_METADATA_OPTIMIZER_PKG.g_dir is null THEN
56: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
51: ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
52: BSC_METADATA_OPTIMIZER_PKG.g_log_level := FND_LOG.g_current_runtime_level;
53: END IF;
54:
55: IF BSC_METADATA_OPTIMIZER_PKG.g_dir is null THEN
56: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
57: END IF;
58:
59: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
52: BSC_METADATA_OPTIMIZER_PKG.g_log_level := FND_LOG.g_current_runtime_level;
53: END IF;
54:
55: IF BSC_METADATA_OPTIMIZER_PKG.g_dir is null THEN
56: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
57: END IF;
58:
59: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
60: BSC_METADATA_OPTIMIZER_PKG.g_debug := true;
56: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
57: END IF;
58:
59: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
60: BSC_METADATA_OPTIMIZER_PKG.g_debug := true;
61: BSC_METADATA_OPTIMIZER_PKG.g_log := true;
62: END IF;
63:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
57: END IF;
58:
59: IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
60: BSC_METADATA_OPTIMIZER_PKG.g_debug := true;
61: BSC_METADATA_OPTIMIZER_PKG.g_log := true;
62: END IF;
63:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
60: BSC_METADATA_OPTIMIZER_PKG.g_debug := true;
61: BSC_METADATA_OPTIMIZER_PKG.g_log := true;
62: END IF;
63:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
61: BSC_METADATA_OPTIMIZER_PKG.g_log := true;
62: END IF;
63:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
69: fnd_file.put_names('META_DOC.log', 'META_DOC.out', BSC_METADATA_OPTIMIZER_PKG.g_dir);
63:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
69: fnd_file.put_names('META_DOC.log', 'META_DOC.out', BSC_METADATA_OPTIMIZER_PKG.g_dir);
70: BSC_METADATA_OPTIMIZER_PKG.g_fileOpened := true;
71:
64: IF (BSC_METADATA_OPTIMIZER_PKG.g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
69: fnd_file.put_names('META_DOC.log', 'META_DOC.out', BSC_METADATA_OPTIMIZER_PKG.g_dir);
70: BSC_METADATA_OPTIMIZER_PKG.g_fileOpened := true;
71:
72: bsc_apps.init_bsc_apps;
65: BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
69: fnd_file.put_names('META_DOC.log', 'META_DOC.out', BSC_METADATA_OPTIMIZER_PKG.g_dir);
70: BSC_METADATA_OPTIMIZER_PKG.g_fileOpened := true;
71:
72: bsc_apps.init_bsc_apps;
73: bsc_message.init('Y');
66: END IF;
67: BSC_METADATA_OPTIMIZER_PKG.g_log_level :=1;
68: BSC_METADATA_OPTIMIZER_PKG.g_log:=true;
69: fnd_file.put_names('META_DOC.log', 'META_DOC.out', BSC_METADATA_OPTIMIZER_PKG.g_dir);
70: BSC_METADATA_OPTIMIZER_PKG.g_fileOpened := true;
71:
72: bsc_apps.init_bsc_apps;
73: bsc_message.init('Y');
74: bsc_mo_helper_pkg.InitTablespaceNames;
73: bsc_message.init('Y');
74: bsc_mo_helper_pkg.InitTablespaceNames;
75: BSC_MO_HELPER_PKG.InitializePeriodicities;
76:
77: BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
78: BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
79: BSC_METADATA_OPTIMIZER_PKG.gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
80: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 0;
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
74: bsc_mo_helper_pkg.InitTablespaceNames;
75: BSC_MO_HELPER_PKG.InitializePeriodicities;
76:
77: BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
78: BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
79: BSC_METADATA_OPTIMIZER_PKG.gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
80: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 0;
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
82:
75: BSC_MO_HELPER_PKG.InitializePeriodicities;
76:
77: BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
78: BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
79: BSC_METADATA_OPTIMIZER_PKG.gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
80: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 0;
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
82:
83: END IF;
76:
77: BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
78: BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
79: BSC_METADATA_OPTIMIZER_PKG.gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
80: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 0;
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
82:
83: END IF;
84:
77: BSC_METADATA_OPTIMIZER_PKG.gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
78: BSC_METADATA_OPTIMIZER_PKG.gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
79: BSC_METADATA_OPTIMIZER_PKG.gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
80: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 0;
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
82:
83: END IF;
84:
85: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
81: BSC_METADATA_OPTIMIZER_PKG.initMVFlags ;
82:
83: END IF;
84:
85: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
86: BSC_MO_HELPER_PKG.writeTmp('---------------------------------------------------------'||g_newline);
87: BSC_MO_HELPER_PKG.writeTmp('Database Generator Documentation start time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
88: BSC_MO_HELPER_PKG.writeTmp('---------------------------------------------------------'||g_newline);
89: BSC_MO_HELPER_PKG.writeTmp(g_newline);
157: --****************************************************************************
158:
159: PROCEDURE WRITELINETEXTFILE( text IN VARCHAR2) is
160: BEGIN
161: --BSC_METADATA_OPTIMIZER_PKG.writeDoc(text);
162: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, text);
163: END;
164:
165:
170: -- Write the header in text file
171: --****************************************************************************
172: PROCEDURE WriteDocHeader IS
173: BEGIN
174: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
175: BSC_MO_HELPER_PKG.writeTmp('Inside WriteDocHeader', FND_LOG.LEVEL_PROCEDURE);
176: END IF;
177:
178: WriteLineTextFile( '+---------------------------------------------------------------------------+');
175: BSC_MO_HELPER_PKG.writeTmp('Inside WriteDocHeader', FND_LOG.LEVEL_PROCEDURE);
176: END IF;
177:
178: WriteLineTextFile( '+---------------------------------------------------------------------------+');
179: WriteLineTextFile( 'Oracle Balanced Scorecard: Version : ' || BSC_METADATA_OPTIMIZER_PKG.VERSION);
180: WriteLineTextFile( '');
181: WriteLineTextFile( 'Copyright (c) Oracle Corporation 1999. All rights reserved.');
182: WriteLineTextFile( '');
183: -- Changed 'Metadata Optimizer' to 'Generated Database' in the following statement
182: WriteLineTextFile( '');
183: -- Changed 'Metadata Optimizer' to 'Generated Database' in the following statement
184: WriteLineTextFile( 'Module: Generate Database');
185: WriteLineTextFile( '+---------------------------------------------------------------------------+');
186: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
187: BSC_MO_HELPER_PKG.writeTmp('Completed WriteDocHeader', FND_LOG.LEVEL_PROCEDURE);
188: END IF;
189:
190: End ;
286: --****************************************************************************
287: PROCEDURE DocResult IS
288: TABLELENGTH NUMBER := 40;
289: INDENT NUMBER := 5;
290: Indicator BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
291: msg VARCHAR2(1000);
292: i NUMBER;
293: j NUMBER;
294: ShowITables Boolean;
301: l_indic_table DBMS_SQL.NUMBER_TABLE;
302: l_fields DBMS_SQL.VARCHAR2_TABLE;
303: l_sumLevelChangedMsg VARCHAR2(1000);
304: BEGIN
305: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
306: bsc_mo_helper_pkg.writeTmp('Inside DocResult', FND_LOG.LEVEL_PROCEDURE);
307: END IF;
308: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'METADATA_OPTIMIZER_RESULT'));
309: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'METADATA_OPTIMIZER_RESULT'))));
308: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'METADATA_OPTIMIZER_RESULT'));
309: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'METADATA_OPTIMIZER_RESULT'))));
310: --Bug#3306248
311: --get message if summarization level was changed
312: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
313: l_sumLevelChangedMsg := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SUM_LEVEL_CHANGE') ||
314: BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ' ||
315: BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'FROM') || ' ';
316: If BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level IS NULL Then
312: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
313: l_sumLevelChangedMsg := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SUM_LEVEL_CHANGE') ||
314: BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ' ||
315: BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'FROM') || ' ';
316: If BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level IS NULL Then
317: l_sumLevelChangedMsg := l_sumLevelChangedMsg || 'NULL';
318: Else
319: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level;
320: End If;
315: BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'FROM') || ' ';
316: If BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level IS NULL Then
317: l_sumLevelChangedMsg := l_sumLevelChangedMsg || 'NULL';
318: Else
319: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level;
320: End If;
321: l_sumLevelChangedMsg := l_sumLevelChangedMsg || ' ' || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'TO') || ' ';
322: If BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level IS NULL Then
323: l_sumLevelChangedMsg := l_sumLevelChangedMsg || 'NULL';
318: Else
319: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Current_Adv_Sum_Level;
320: End If;
321: l_sumLevelChangedMsg := l_sumLevelChangedMsg || ' ' || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'TO') || ' ';
322: If BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level IS NULL Then
323: l_sumLevelChangedMsg := l_sumLevelChangedMsg || 'NULL';
324: Else
325: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level;
326: End If;
321: l_sumLevelChangedMsg := l_sumLevelChangedMsg || ' ' || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'TO') || ' ';
322: If BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level IS NULL Then
323: l_sumLevelChangedMsg := l_sumLevelChangedMsg || 'NULL';
324: Else
325: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level;
326: End If;
327: End If;
328: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count=0) THEN
329: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
324: Else
325: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level;
326: End If;
327: End If;
328: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count=0) THEN
329: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
330: bsc_mo_helper_pkg.writeTmp('Completed DocResult, gIndicators.count was zero');
331: END IF;
332: return;
325: l_sumLevelChangedMsg := l_sumLevelChangedMsg || BSC_METADATA_OPTIMIZER_PKG.g_Adv_Summarization_Level;
326: End If;
327: End If;
328: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count=0) THEN
329: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
330: bsc_mo_helper_pkg.writeTmp('Completed DocResult, gIndicators.count was zero');
331: END IF;
332: return;
333: END IF;
330: bsc_mo_helper_pkg.writeTmp('Completed DocResult, gIndicators.count was zero');
331: END IF;
332: return;
333: END IF;
334: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
335: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
336: bsc_mo_helper_pkg.writeTmp('gIndicators.count = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
337: END IF;
338: LOOP
331: END IF;
332: return;
333: END IF;
334: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
335: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
336: bsc_mo_helper_pkg.writeTmp('gIndicators.count = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
337: END IF;
338: LOOP
339: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
332: return;
333: END IF;
334: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
335: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
336: bsc_mo_helper_pkg.writeTmp('gIndicators.count = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
337: END IF;
338: LOOP
339: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
340: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
335: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
336: bsc_mo_helper_pkg.writeTmp('gIndicators.count = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
337: END IF;
338: LOOP
339: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
340: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
341: bsc_mo_helper_pkg.writeTmp('Indicator.code = '||Indicator.code||', Indicator.Action_Flag = '||Indicator.Action_Flag);
342: END IF;
343: --Indicator code and name
336: bsc_mo_helper_pkg.writeTmp('gIndicators.count = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
337: END IF;
338: LOOP
339: Indicator := BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index1);
340: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
341: bsc_mo_helper_pkg.writeTmp('Indicator.code = '||Indicator.code||', Indicator.Action_Flag = '||Indicator.Action_Flag);
342: END IF;
343: --Indicator code and name
344: If Indicator.EDW_Flag = 0 Then
349: IF (Indicator.Action_Flag = 0 OR Indicator.Action_Flag=6) THEN
350: --Case 0, 6
351: --No changes were made.
352: --BSC-MV Note: Show if there was a summarization level change
353: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 0 Then
354: --No changes were made.
355: fnd_message.set_name('BSC', 'BSC_NO_CHANGES_VERIF');
356: msg := fnd_message.get;
357: WriteLineTextFile (msg);
369: ShowOldITables := False;
370: End If;
371: ELSIF (Indicator.Action_Flag = 1 OR Indicator.Action_Flag=3) THEN
372: --Case 1, 3
373: If BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.garrOldIndicators, Indicator.Code)>=0 Then
374: --Structural changes: Add or drop a dimension, data set, analysis group, etc.
375: msg := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'STRUCTURAL_CHANGE');
376: msg := msg || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON');
377: fnd_message.set_name('BSC', 'BSC_ADD_DROP_DIMENSION');
377: fnd_message.set_name('BSC', 'BSC_ADD_DROP_DIMENSION');
378: msg := msg || ' ' ||fnd_message.get;
379: WriteLineTextFile (msg);
380: --Bug#3306248
381: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
382: WriteLineTextFile (l_sumLevelChangedMsg);
383: End If;
384: ShowITables := True;
385: ShowOldITables := True;
403: msg := msg || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON');
404: msg := msg || ' ' || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'CHANGE_COLOR_OR_GROUP_FUNCTION');
405: WriteLineTextFile (msg);
406: --Bug#3306248
407: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
408: WriteLineTextFile (l_sumLevelChangedMsg);
409: End If;
410: fnd_message.set_name('BSC', 'BSC_INPUT_TABLES_NO_CHANGED');
411: msg := fnd_message.get;
419: END IF;
420: --Input tables
421: If ShowITables Then
422: FirstTable := True;
423: For i IN 0..BSC_METADATA_OPTIMIZER_PKG.gnumNewITables - 1 LOOP
424: l_indic_table := BSC_MO_HELPER_PKG.decomposestringtonumber( BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Indicators, ',');
425: If BSC_MO_HELPER_PKG.findIndex(l_indic_table, Indicator.Code)>=0 Then
426: If FirstTable Then
427: --Write header
420: --Input tables
421: If ShowITables Then
422: FirstTable := True;
423: For i IN 0..BSC_METADATA_OPTIMIZER_PKG.gnumNewITables - 1 LOOP
424: l_indic_table := BSC_MO_HELPER_PKG.decomposestringtonumber( BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Indicators, ',');
425: If BSC_MO_HELPER_PKG.findIndex(l_indic_table, Indicator.Code)>=0 Then
426: If FirstTable Then
427: --Write header
428: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'INPUT_TABLE'));
429: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'INPUT_TABLE'))));
430: FirstTable := False;
431: End If;
432: --Table Name (Periodicity name)
433: msg := BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Name || ' (' ||
434: GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || ' (' ||
435: GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || '))';
436: WriteLineTextFile (msg);
437: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).fields, ',');
430: FirstTable := False;
431: End If;
432: --Table Name (Periodicity name)
433: msg := BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Name || ' (' ||
434: GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || ' (' ||
435: GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || '))';
436: WriteLineTextFile (msg);
437: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).fields, ',');
438: --fields
431: End If;
432: --Table Name (Periodicity name)
433: msg := BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Name || ' (' ||
434: GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || ' (' ||
435: GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || '))';
436: WriteLineTextFile (msg);
437: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).fields, ',');
438: --fields
439: j := l_fields.first;
433: msg := BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).Name || ' (' ||
434: GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || ' (' ||
435: GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).periodicity) || '))';
436: WriteLineTextFile (msg);
437: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrNewITables(i).fields, ',');
438: --fields
439: j := l_fields.first;
440: LOOP
441: EXIT WHEN l_fields.count=0;
449: End If;
450: --Old input tables
451: If ShowOldITables Then
452: FirstTable := True;
453: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.first;
454: LOOP
455: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.count = 0;
456: l_indic_table := BSC_MO_HELPER_PKG.decomposestringtonumber( BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Indicators, ',');
457: If BSC_MO_HELPER_PKG.findIndex(l_indic_table, Indicator.Code)>=0 Then
451: If ShowOldITables Then
452: FirstTable := True;
453: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.first;
454: LOOP
455: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.count = 0;
456: l_indic_table := BSC_MO_HELPER_PKG.decomposestringtonumber( BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Indicators, ',');
457: If BSC_MO_HELPER_PKG.findIndex(l_indic_table, Indicator.Code)>=0 Then
458: If FirstTable Then
459: --Write header
452: FirstTable := True;
453: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.first;
454: LOOP
455: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.count = 0;
456: l_indic_table := BSC_MO_HELPER_PKG.decomposestringtonumber( BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Indicators, ',');
457: If BSC_MO_HELPER_PKG.findIndex(l_indic_table, Indicator.Code)>=0 Then
458: If FirstTable Then
459: --Write header
460: msg := StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'OLD_INPUT_TABLE'), TABLELENGTH) || StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'BACKUP_TABLE'), TABLELENGTH);
467:
468: --Input table Backup table (Periodicity)
469: --Fix bug#2436864 The peridiodicity or calendar of a old table could be deleted.
470: --So show (Custom Periodicity) when that is the case.
471: msg := StrFix(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).InputTable, TABLELENGTH);
472: -- Show B table backup only if it has really been created in this round
473: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
474: msg := msg || BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name || '_BAK (';
475: PeriodicityName := GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
469: --Fix bug#2436864 The peridiodicity or calendar of a old table could be deleted.
470: --So show (Custom Periodicity) when that is the case.
471: msg := StrFix(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).InputTable, TABLELENGTH);
472: -- Show B table backup only if it has really been created in this round
473: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
474: msg := msg || BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name || '_BAK (';
475: PeriodicityName := GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
476: CalendarName := GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
477: If (PeriodicityName IS NULL Or CalendarName IS NULL) Then
470: --So show (Custom Periodicity) when that is the case.
471: msg := StrFix(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).InputTable, TABLELENGTH);
472: -- Show B table backup only if it has really been created in this round
473: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
474: msg := msg || BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name || '_BAK (';
475: PeriodicityName := GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
476: CalendarName := GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
477: If (PeriodicityName IS NULL Or CalendarName IS NULL) Then
478: msg := msg || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_KPIDESIGNER', 'CUSTOM_PERIODICITY');
471: msg := StrFix(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).InputTable, TABLELENGTH);
472: -- Show B table backup only if it has really been created in this round
473: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
474: msg := msg || BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name || '_BAK (';
475: PeriodicityName := GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
476: CalendarName := GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
477: If (PeriodicityName IS NULL Or CalendarName IS NULL) Then
478: msg := msg || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_KPIDESIGNER', 'CUSTOM_PERIODICITY');
479: Else
472: -- Show B table backup only if it has really been created in this round
473: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
474: msg := msg || BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name || '_BAK (';
475: PeriodicityName := GetPeriodicityName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
476: CalendarName := GetPeriodicityCalendarName(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).periodicity);
477: If (PeriodicityName IS NULL Or CalendarName IS NULL) Then
478: msg := msg || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_KPIDESIGNER', 'CUSTOM_PERIODICITY');
479: Else
480: msg := msg || PeriodicityName || ' (' || CalendarName || ')';
481: End If;
482: msg := msg || ')';
483: END IF;
484: WriteLineTextFile( msg);
485: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
486: --fields
487: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Fields, ',');
488: j := l_fields.first;
489: LOOP
483: END IF;
484: WriteLineTextFile( msg);
485: IF BSC_MO_HELPER_PKG.findIndexVARCHAR2(BSC_METADATA_OPTIMIZER_PKG.gBackedUpBTables, BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Name) >= 0 THEN
486: --fields
487: l_fields := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.garrOldBTables(i).Fields, ',');
488: j := l_fields.first;
489: LOOP
490: EXIT WHEN l_fields.count=0;
491: msg := StrX(' ', TABLELENGTH) || StrX(' ', INDENT) || l_Fields(j);
494: j := l_fields.next(j);
495: END LOOP;
496: END IF;
497: End If;
498: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.last;
499: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.next(i);
500: END LOOP;
501: End If;
502: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
495: END LOOP;
496: END IF;
497: End If;
498: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.last;
499: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.next(i);
500: END LOOP;
501: End If;
502: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
503: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
498: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.last;
499: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.next(i);
500: END LOOP;
501: End If;
502: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
503: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
504: END LOOP;
505: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
506: bsc_mo_helper_pkg.writeTmp('Completed DocResult', FND_LOG.LEVEL_PROCEDURE);
499: i := BSC_METADATA_OPTIMIZER_PKG.garrOldBTables.next(i);
500: END LOOP;
501: End If;
502: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
503: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
504: END LOOP;
505: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
506: bsc_mo_helper_pkg.writeTmp('Completed DocResult', FND_LOG.LEVEL_PROCEDURE);
507: END IF;
501: End If;
502: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
503: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_index1);
504: END LOOP;
505: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
506: bsc_mo_helper_pkg.writeTmp('Completed DocResult', FND_LOG.LEVEL_PROCEDURE);
507: END IF;
508: EXCEPTION WHEN OTHERS THEN
509: BSC_MO_HELPER_PKG.TerminateWithMsg(sqlerrm, 'DocResult');
581: --
582: -- PARAMETERS:
583: -- Tabla: object with the information of the table
584: --****************************************************************************
585: PROCEDURE writeTableDescription(Tabla BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
586:
587: TABLELENGTH NUMBER := 25;
588: FIELDLENGTH NUMBER := 30;
589: TYPELENGTH NUMBER := 15;
610:
611: l_temp NUMBER;
612: l_tempv VARCHAR2(1000);
613:
614: Table_Keys bsc_metadata_optimizer_pkg.tab_clsKeyField;
615: Table_Data bsc_metadata_optimizer_pkg.tab_clsDataField;
616:
617: l_COLUMN_NAME VARCHAR2(30);
618: l_DATA_TYPE VARCHAR2(106);
611: l_temp NUMBER;
612: l_tempv VARCHAR2(1000);
613:
614: Table_Keys bsc_metadata_optimizer_pkg.tab_clsKeyField;
615: Table_Data bsc_metadata_optimizer_pkg.tab_clsDataField;
616:
617: l_COLUMN_NAME VARCHAR2(30);
618: l_DATA_TYPE VARCHAR2(106);
619: l_DATA_LENGTH NUMBER;
626: CURSOR cZeroMV(p_pattern varchar2) IS
627: SELECT mview_name
628: FROM all_mviews
629: WHERE mview_name like p_pattern
630: AND owner in (BSC_METADATA_OPTIMIZER_PKG.gBSCSchema,BSC_METADATA_OPTIMIZER_PKG.gAppsSchema);
631: l_mvname VARCHAR2(100);
632:
633: /*CURSOR cProjTable IS
634: SELECT DISTINCT projection_data
635: FROM bsc_kpi_data_tables
636: WHERE table_name = Tabla.name;*/
637: CURSOR cProjTable(p_pt_table varchar2) IS
638: SELECT table_name from all_tables
639: WHERE owner = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema
640: AND table_name=p_pt_table;
641: l_col_id NUMBER;
642:
643: l_bind_case number;
642:
643: l_bind_case number;
644: BEGIN
645:
646: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
647: bsc_mo_helper_pkg.writeTmp('writeTableDescription, Table is : ');
648: bsc_mo_helper_pkg.write_this(tabla);
649: END IF;
650: --BSC Multiple Optimizers
651: -- Table_Keys := BSC_MO_HELPER_PKG.getAllKeyFields(Tabla.name);
652: -- Table_Data := BSC_MO_HELPER_PKG.getAllDataFields(Tabla.name);
653: Table_Keys := Tabla.keys;
654: Table_Data := Tabla.data;
655: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
656: isBaseTable := BSC_MO_LOADER_CONFIG_PKG.isBasicTable(Tabla.Name);
657: End If;
658: If Tabla.Type = 0 Then
659: --input table
679: Else
680: TableName := Tabla.Name;
681: End If;
682: --BSC-MV Note
683: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable)) Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
684: WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TABLE') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) || TableName);
685: If Tabla.IsTargetTable Then
686: fnd_message.set_name('BSC', 'BSC_TABLE_FOR_BENCHMARKS');
687: WriteLineTextFile (StrFix(' ', TABLELENGTH) || fnd_message.get);
694: WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TABLE') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) || Tabla.MVName);
695: End If;
696: End If;
697: --Bug3351483 Add reference to Zero code MVs and Projection Tabels in system.txt
698: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And Tabla.Type <> 0 And (Not isBaseTable) Then
699: --Only for MV architecture when the table is not an input table and it is not a base table
700: OPEN cZeroMV(substr(tabla.name, 1, instr(tabla.name, '_', -1))||'%MV');
701: FETCH cZeroMV INTO l_mvname;
702: CLOSE cZeroMV;
710: If l_mvname is not null Then
711: WriteLineTextFile(bsc_mo_helper_pkg.Get_LookUp_Value('BSC_UI_BACKEND', 'ASSOCIATED_PT') || bsc_mo_helper_pkg.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' '|| l_mvname);
712: End If;
713: End If;
714: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
715: bsc_mo_helper_pkg.writeTmp( 'Writing Headers');
716: END IF;
717: --Headers
718: WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'FIELD'), FIELDLENGTH) ||
719: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE'), TYPELENGTH) ||
720: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SIZE'), SIZELENGTH) ||
721: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DESCRIPTION'), DESCLENGTH));
722: --BSC_MV Note
723: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
724: bsc_mo_helper_pkg.writeTmp( 'Writing Columns');
725: END IF;
726: --Columns
727: l_stmt := 'SELECT DISTINCT A.COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, B.SOURCE, A.COLUMN_ID ';
724: bsc_mo_helper_pkg.writeTmp( 'Writing Columns');
725: END IF;
726: --Columns
727: l_stmt := 'SELECT DISTINCT A.COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, B.SOURCE, A.COLUMN_ID ';
728: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And Tabla.dbObjectType = 'VIEW' Then
729: l_bind_case := 1;
730: l_stmt := l_stmt ||' FROM USER_TAB_COLUMNS A, BSC_DB_TABLES_COLS B WHERE A.TABLE_NAME = :1';
731: --BSC Autogen
732: l_stmt := l_stmt ||' AND B.TABLE_NAME(+) = :2 AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
731: --BSC Autogen
732: l_stmt := l_stmt ||' AND B.TABLE_NAME(+) = :2 AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
733: Else
734: l_stmt := l_stmt ||' FROM ALL_TAB_COLUMNS A, BSC_DB_TABLES_COLS B ';
735: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable))
736: Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
737: l_bind_case := 2;
738: l_stmt := l_stmt ||' WHERE A.TABLE_NAME = :1 ';
739: --BSC Autogen
732: l_stmt := l_stmt ||' AND B.TABLE_NAME(+) = :2 AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
733: Else
734: l_stmt := l_stmt ||' FROM ALL_TAB_COLUMNS A, BSC_DB_TABLES_COLS B ';
735: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable))
736: Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
737: l_bind_case := 2;
738: l_stmt := l_stmt ||' WHERE A.TABLE_NAME = :1 ';
739: --BSC Autogen
740: l_stmt := l_stmt ||' AND A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
738: l_stmt := l_stmt ||' WHERE A.TABLE_NAME = :1 ';
739: --BSC Autogen
740: l_stmt := l_stmt ||' AND A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
741: l_stmt := l_stmt ||' AND A.OWNER = :2';
742: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
743: bsc_mo_helper_pkg.writeTmp( 'table');
744: END IF;
745: Else
746: l_bind_case := 3;
743: bsc_mo_helper_pkg.writeTmp( 'table');
744: END IF;
745: Else
746: l_bind_case := 3;
747: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
748: bsc_mo_helper_pkg.writeTmp( 'MV');
749: END IF;
750: l_stmt := l_stmt ||' WHERE A.TABLE_NAME = :1 ';
751: --BSC Autogen
753: l_stmt := l_stmt ||' AND OWNER = :3 ';
754: End If;
755: End If;
756: l_stmt := l_stmt ||' ORDER BY COLUMN_ID ';
757: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
758: bsc_mo_helper_pkg.writeTmp( 'l_stmt = '||l_stmt);
759: END IF;
760: IF (l_bind_case =1) THEN
761: OPEN cv for l_stmt using UPPER(Tabla.MVName) ,UPPER(Tabla.Name);
759: END IF;
760: IF (l_bind_case =1) THEN
761: OPEN cv for l_stmt using UPPER(Tabla.MVName) ,UPPER(Tabla.Name);
762: ELSIF l_bind_case=2 THEN
763: OPEN cv for l_stmt using Upper(Tabla.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema);
764: ELSE
765: OPEN cv for l_stmt using UPPER(Tabla.MVName) ,UPPER(Tabla.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gAppsSchema);
766: END IF;
767: LOOP
761: OPEN cv for l_stmt using UPPER(Tabla.MVName) ,UPPER(Tabla.Name);
762: ELSIF l_bind_case=2 THEN
763: OPEN cv for l_stmt using Upper(Tabla.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema);
764: ELSE
765: OPEN cv for l_stmt using UPPER(Tabla.MVName) ,UPPER(Tabla.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gAppsSchema);
766: END IF;
767: LOOP
768: FETCH cv INTO l_COLUMN_NAME, l_DATA_TYPE, l_DATA_LENGTH, l_DATA_PRECISION, l_DATA_SCALE, l_source, l_col_id;
769: EXIT WHEN cv%NOTFOUND;
800: End If;
801: ELSE
802: Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
803: End If;
804: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
805: BSC_MO_HELPER_PKG.writeTmp('Processing column:'||rsColumn_Name, FND_LOG.LEVEL_STATEMENT);
806: END IF;
807: If BSC_MO_INDICATOR_PKG.keyFieldExists(Table_Keys, rsColumn_Name) Then
808: BSC_MO_HELPER_PKG.writeTmp('Its a key', FND_LOG.LEVEL_STATEMENT);
810: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'USER_CODE');
811: Else
812: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'CODE');
813: End If;
814: l_temp := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, rsColumn_Name);
815: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
816: IF (l_temp <> -1) THEN
817: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).Name );
818: msg := fnd_message.get;
813: End If;
814: l_temp := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, rsColumn_Name);
815: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
816: IF (l_temp <> -1) THEN
817: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).Name );
818: msg := fnd_message.get;
819: Linea := Linea || ' (' || msg || ')';
820: END IF;
821: If Tabla.Type = 0 Then --input table
828: End If;
829: END IF;
830: End If;
831: ElsIf BSC_MO_INDICATOR_PKG.dataFieldExists(Table_Data, rsColumn_Name) Then
832: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
833: bsc_mo_helper_pkg.writeTmp('Data field exists, rsColumn_Name='||rsColumn_Name);
834: END IF;
835: If substr(rsColumn_Name, 1, 5) = 'BSCIC' Then
836: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
834: END IF;
835: If substr(rsColumn_Name, 1, 5) = 'BSCIC' Then
836: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
837: Else
838: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, rsColumn_name, l_source, true);
839: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
840: bsc_mo_helper_pkg.writeTmp(' l_temp = '||l_temp);
841: END IF;
842: IF (l_temp <>-1) THEN
835: If substr(rsColumn_Name, 1, 5) = 'BSCIC' Then
836: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
837: Else
838: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, rsColumn_name, l_source, true);
839: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
840: bsc_mo_helper_pkg.writeTmp(' l_temp = '||l_temp);
841: END IF;
842: IF (l_temp <>-1) THEN
843: Linea := Linea || BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description;
839: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
840: bsc_mo_helper_pkg.writeTmp(' l_temp = '||l_temp);
841: END IF;
842: IF (l_temp <>-1) THEN
843: Linea := Linea || BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).Description;
844: END IF;
845: End If;
846: ElsIf UPPER(rsColumn_Name) = 'YEAR' Then
847: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'YEAR_1999_2000_ACTUAL_YEAR');
855: --added for bug 3919130
856: ElsIf UPPER(rsColumn_Name) = 'TIME_FK' Then
857: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DATE');
858: ElsIf UPPER(rsColumn_Name) = UPPER(l_period_column) Then
859: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable))
860: Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
861: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gperiodicities, Tabla.periodicity);
862: IF (l_temp <> -1) THEN
863: --I am doing this because the periodicity could not exist in the system.
856: ElsIf UPPER(rsColumn_Name) = 'TIME_FK' Then
857: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DATE');
858: ElsIf UPPER(rsColumn_Name) = UPPER(l_period_column) Then
859: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable))
860: Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
861: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gperiodicities, Tabla.periodicity);
862: IF (l_temp <> -1) THEN
863: --I am doing this because the periodicity could not exist in the system.
864: --Documentation can be run at anytime and the user could have deleted the periodicity
857: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DATE');
858: ElsIf UPPER(rsColumn_Name) = UPPER(l_period_column) Then
859: If (BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV And (Tabla.Type = 0 Or isBaseTable))
860: Or (Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV) Then
861: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gperiodicities, Tabla.periodicity);
862: IF (l_temp <> -1) THEN
863: --I am doing this because the periodicity could not exist in the system.
864: --Documentation can be run at anytime and the user could have deleted the periodicity
865: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
861: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gperiodicities, Tabla.periodicity);
862: IF (l_temp <> -1) THEN
863: --I am doing this because the periodicity could not exist in the system.
864: --Documentation can be run at anytime and the user could have deleted the periodicity
865: If BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp).Yearly_Flag = 1 Then
866: Linea := Linea || ' 0'; --bug#3980028
867: Else
868: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'PERIOD'); --bug#3980028
869: Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' 1 ' ||
903: -- Generate the description of the input and system tables
904: --****************************************************************************
905: PROCEDURE WriteInputAndSystemTables IS
906:
907: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
908: l_index1 NUMBER;
909: dbObjectType VARCHAR2(106);
910: mvName VARCHAR2(100);
911: arrMvs DBMS_SQL.VARCHAR2_TABLE;
910: mvName VARCHAR2(100);
911: arrMvs DBMS_SQL.VARCHAR2_TABLE;
912: numMVs NUMBER := 0;
913: BEGIn
914: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
915: BSC_MO_HELPER_PKG.writeTmp('Inside WriteInputAndSystemTables', FND_LOG.LEVEL_PROCEDURE);
916: END IF;
917: --Input tables
918: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'));
916: END IF;
917: --Input tables
918: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'));
919: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'))));
920: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
921: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
922: LOOP
923: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
924: If l_table.Type = 0 Then
917: --Input tables
918: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'));
919: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'))));
920: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
921: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
922: LOOP
923: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
924: If l_table.Type = 0 Then
925: writeTableDescription (l_table);
919: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLES_DESCRIPTION'))));
920: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
921: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
922: LOOP
923: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
924: If l_table.Type = 0 Then
925: writeTableDescription (l_table);
926: End If;
927: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
923: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
924: If l_table.Type = 0 Then
925: writeTableDescription (l_table);
926: End If;
927: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
928: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
929: END LOOP;
930: END IF;
931: --System tables
924: If l_table.Type = 0 Then
925: writeTableDescription (l_table);
926: End If;
927: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
928: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
929: END LOOP;
930: END IF;
931: --System tables
932: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'));
930: END IF;
931: --System tables
932: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'));
933: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'))) );
934: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
935: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
936: LOOP
937: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
938: If l_table.Type <> 0 Then
931: --System tables
932: WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'));
933: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'))) );
934: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
935: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
936: LOOP
937: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
938: If l_table.Type <> 0 Then
939: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
933: WriteLineTextFile (StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SYSTEM_TABLES_DESCRIPTION'))) );
934: IF (BSC_METADATA_OPTIMIZER_PKG.gTables.count >0 ) THEN
935: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
936: LOOP
937: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
938: If l_table.Type <> 0 Then
939: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
940: --BSC-MV Note: If the table is a base table then write the description of the table.
941: --If not then write the description of the MV (if exists)
935: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
936: LOOP
937: l_table := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);
938: If l_table.Type <> 0 Then
939: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
940: --BSC-MV Note: If the table is a base table then write the description of the table.
941: --If not then write the description of the MV (if exists)
942: If BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_table.Name) Then
943: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
939: If BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV Then
940: --BSC-MV Note: If the table is a base table then write the description of the table.
941: --If not then write the description of the MV (if exists)
942: If BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_table.Name) Then
943: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
944: bsc_mo_helper_pkg.writeTmp('SYNONYM', FND_LOG.LEVEL_STATEMENT);
945: END IF;
946: writeTableDescription (l_table);
947: l_table.dbObjectType := 'SYNONYM';
963: ELSE
964: writeTableDescription (l_table);
965: END IF;
966: End If;
967: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
968: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
969: END LOOP;
970: END IF;
971: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
964: writeTableDescription (l_table);
965: END IF;
966: End If;
967: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
968: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
969: END LOOP;
970: END IF;
971: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
972: BSC_MO_HELPER_PKG.writeTmp('Completed WriteInputAndSystemTables', FND_LOG.LEVEL_PROCEDURE);
967: EXIT WHEN l_index1 = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
968: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index1);
969: END LOOP;
970: END IF;
971: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
972: BSC_MO_HELPER_PKG.writeTmp('Completed WriteInputAndSystemTables', FND_LOG.LEVEL_PROCEDURE);
973: END IF;
974: EXCEPTION WHEN OTHERS THEN
975: BSC_MO_HELPER_PKG.TerminateWithMsg(sqlerrm);
988: i NUMBER;
989: arrTablesOri DBMS_SQL.VARCHAR2_TABLE;
990: numTablesOri NUMBER;
991: TablaOri VARCHAR2(100);
992: keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
993:
994: l_origin_counter NUMBER;
995: l_index2 NUMBER;
996: l_table_index_in_gTables NUMBER;
997: l_origin_index_in_gTables NUMBER;
998:
999: l_key_index NUMBER;
1000: l_originTable DBMS_SQL.varchar2_table;
1001: l_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1002: l_origin_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1003:
1004:
1005: BEGIN
998:
999: l_key_index NUMBER;
1000: l_originTable DBMS_SQL.varchar2_table;
1001: l_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1002: l_origin_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1003:
1004:
1005: BEGIN
1006: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1002: l_origin_table_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1003:
1004:
1005: BEGIN
1006: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1007: bsc_mo_helper_pkg.writeTmp('Inside SetNeedCod0InOriTables', FND_LOG.LEVEL_PROCEDURE);
1008: END IF;
1009: numTablesOri := 0;
1010: For i IN 0..numTablesCod0 - 1 LOOP
1007: bsc_mo_helper_pkg.writeTmp('Inside SetNeedCod0InOriTables', FND_LOG.LEVEL_PROCEDURE);
1008: END IF;
1009: numTablesOri := 0;
1010: For i IN 0..numTablesCod0 - 1 LOOP
1011: l_table_index_in_gTables := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, arrTablesCod0(i));
1012: l_originTable := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index_in_gTables).originTable, ',');
1013: l_origin_counter := l_originTable.first;
1014: LOOP
1015: EXIT WHEN l_originTable.count=0;
1008: END IF;
1009: numTablesOri := 0;
1010: For i IN 0..numTablesCod0 - 1 LOOP
1011: l_table_index_in_gTables := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, arrTablesCod0(i));
1012: l_originTable := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index_in_gTables).originTable, ',');
1013: l_origin_counter := l_originTable.first;
1014: LOOP
1015: EXIT WHEN l_originTable.count=0;
1016: TablaOri := l_originTable(l_origin_counter);
1013: l_origin_counter := l_originTable.first;
1014: LOOP
1015: EXIT WHEN l_originTable.count=0;
1016: TablaOri := l_originTable(l_origin_counter);
1017: l_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index_in_gTables).keys;
1018: l_origin_index_in_gTables := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TablaOri);
1019: -- origin table keys
1020: l_origin_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_origin_index_in_gTables).keys;
1021: l_index2 := l_origin_table_keys.first;
1014: LOOP
1015: EXIT WHEN l_originTable.count=0;
1016: TablaOri := l_originTable(l_origin_counter);
1017: l_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index_in_gTables).keys;
1018: l_origin_index_in_gTables := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TablaOri);
1019: -- origin table keys
1020: l_origin_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_origin_index_in_gTables).keys;
1021: l_index2 := l_origin_table_keys.first;
1022: LOOP
1016: TablaOri := l_originTable(l_origin_counter);
1017: l_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index_in_gTables).keys;
1018: l_origin_index_in_gTables := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TablaOri);
1019: -- origin table keys
1020: l_origin_table_keys := BSC_METADATA_OPTIMIZER_PKG.gTables(l_origin_index_in_gTables).keys;
1021: l_index2 := l_origin_table_keys.first;
1022: LOOP
1023: EXIT WHEN l_origin_table_keys.count=0;
1024: keyColumn := l_origin_table_keys(l_index2);
1031: end if;
1032: EXIT WHEN l_index2 = l_origin_table_keys.last;
1033: l_index2 := l_origin_table_keys.next(l_index2);
1034: END LOOP;
1035: BSC_METADATA_OPTIMIZER_PKG.gTables(l_origin_index_in_gTables).keys := l_origin_table_keys;
1036: If Not BSC_MO_HELPER_PKG.searchStringExists(arrTablesOri, numTablesOri, TablaOri) Then
1037: arrTablesOri(numTablesOri) := TablaOri;
1038: numTablesOri := numTablesOri + 1;
1039: End If;
1043: END LOOP;
1044: If numTablesOri > 0 Then
1045: SetNeedCod0InOriTables(arrTablesOri, numTablesOri);
1046: End If;
1047: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1048: bsc_mo_helper_pkg.writeTmp('Completed SetNeedCod0InOriTables', FND_LOG.LEVEL_PROCEDURE);
1049: END IF;
1050: EXCEPTION WHEN OTHERS THEN
1051: g_error := sqlerrm;
1162: l_error VARCHAR2(1000);
1163: BEGIN
1164: l_num := 0;
1165: if (g_mode=1) then -- Doc results
1166: if (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1167: l_stmt := l_stmt ||' where indicator in (select indicator from bsc_tmp_opt_ui_kpis where process_id =:2)';
1168: end if;
1169: end if;
1170: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1166: if (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1167: l_stmt := l_stmt ||' where indicator in (select indicator from bsc_tmp_opt_ui_kpis where process_id =:2)';
1168: end if;
1169: end if;
1170: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1171: bsc_mo_helper_pkg.writeTmp('Case1, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1172: OPEN cv FOR l_stmt using l_tableName, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1173: else
1174: bsc_mo_helper_pkg.writeTmp('Case2, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1167: l_stmt := l_stmt ||' where indicator in (select indicator from bsc_tmp_opt_ui_kpis where process_id =:2)';
1168: end if;
1169: end if;
1170: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1171: bsc_mo_helper_pkg.writeTmp('Case1, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1172: OPEN cv FOR l_stmt using l_tableName, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1173: else
1174: bsc_mo_helper_pkg.writeTmp('Case2, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1175: OPEN cv for l_stmt using l_tableName;
1168: end if;
1169: end if;
1170: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1171: bsc_mo_helper_pkg.writeTmp('Case1, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1172: OPEN cv FOR l_stmt using l_tableName, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1173: else
1174: bsc_mo_helper_pkg.writeTmp('Case2, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1175: OPEN cv for l_stmt using l_tableName;
1176: end if;
1170: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1171: bsc_mo_helper_pkg.writeTmp('Case1, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1172: OPEN cv FOR l_stmt using l_tableName, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1173: else
1174: bsc_mo_helper_pkg.writeTmp('Case2, g_mode='||g_mode||' run_mode='||BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE);
1175: OPEN cv for l_stmt using l_tableName;
1176: end if;
1177: LOOP
1178: FETCH CV INTO l_indicator;
1184: l_num := l_num + 1;
1185: END LOOP;
1186: CLOSE cv;
1187: IF (l_num > 100) THEN
1188: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1189: bsc_mo_helper_pkg.writeTmp('# of results = '||l_num);
1190: END IF;
1191: END IF;
1192: return l_result;
1204: -- Initialize the collection gTablas with all tables
1205: --****************************************************************************
1206: PROCEDURE InicAllTables IS
1207:
1208: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1209: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1210: l_data BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1211: TablaOri VARCHAR2(100);
1212:
1205: --****************************************************************************
1206: PROCEDURE InicAllTables IS
1207:
1208: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1209: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1210: l_data BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1211: TablaOri VARCHAR2(100);
1212:
1213: Indicator NUMBER;
1206: PROCEDURE InicAllTables IS
1207:
1208: l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
1209: l_key BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1210: l_data BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1211: TablaOri VARCHAR2(100);
1212:
1213: Indicator NUMBER;
1214: Configuration NUMBER;
1279: AND P.INDICATOR = T.INDICATOR);
1280: cRow5 c5%ROWTYPE;*/
1281:
1282:
1283: Table_Keys bsc_metadata_optimizer_pkg.tab_clsKeyField;
1284: Table_Data bsc_metadata_optimizer_pkg.tab_clsDataField;
1285: Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
1286: Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
1287:
1280: cRow5 c5%ROWTYPE;*/
1281:
1282:
1283: Table_Keys bsc_metadata_optimizer_pkg.tab_clsKeyField;
1284: Table_Data bsc_metadata_optimizer_pkg.tab_clsDataField;
1285: Tabla_originTable DBMS_SQL.VARCHAR2_TABLE;
1286: Tabla_originTable1 DBMS_SQL.VARCHAR2_TABLE;
1287:
1288: l_ind_list DBMS_SQL.NUMBER_TABLE;
1290: l_key_index NUMBER;
1291:
1292: BEGIN
1293: --Set gTablas = New Collection
1294: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1295: bsc_mo_helper_pkg.writeTmp('Inside inicalltables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1296: END IF;
1297: if (g_mode = 1) then --
1298: if (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1294: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1295: bsc_mo_helper_pkg.writeTmp('Inside inicalltables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1296: END IF;
1297: if (g_mode = 1) then --
1298: if (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1299: l_stmt := l_stmt||
1300: ' and table_name in
1301: (
1302: SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
1320:
1321: --Since input tables for dimensions are in BSC_DB_TABLES
1322: --we dont want them in this query.
1323: --This array is used for result report.
1324: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := 0;
1325: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1326: OPEN cv for l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processid, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1327: else
1328: OPEN cv for l_stmt;
1321: --Since input tables for dimensions are in BSC_DB_TABLES
1322: --we dont want them in this query.
1323: --This array is used for result report.
1324: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := 0;
1325: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1326: OPEN cv for l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processid, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1327: else
1328: OPEN cv for l_stmt;
1329: end if;
1322: --we dont want them in this query.
1323: --This array is used for result report.
1324: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := 0;
1325: IF (g_mode=1) and (BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1326: OPEN cv for l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processid, BSC_METADATA_OPTIMIZER_PKG.g_processid;
1327: else
1328: OPEN cv for l_stmt;
1329: end if;
1330: FETCH cv bulk collect into l_table_name_list, l_table_type_list, l_periodicity_id_list, l_edw_flag_list, l_target_flag_list;
1407: bsc_mo_helper_pkg.writeTmp('Adding '||Upper(l_table.Name)||' to collection');
1408: BSC_MO_HELPER_PKG.addTable(l_table, Table_Keys, Table_Data, 'InicAllTables');
1409: If l_table.Type = 0 Then
1410: --Add the input table to array garrNewITables()
1411: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Name := l_table.Name;
1412: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).periodicity := l_table.Periodicity;
1413: --Fields
1414:
1415: OPEN c4 (Upper(l_table.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema));
1408: BSC_MO_HELPER_PKG.addTable(l_table, Table_Keys, Table_Data, 'InicAllTables');
1409: If l_table.Type = 0 Then
1410: --Add the input table to array garrNewITables()
1411: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Name := l_table.Name;
1412: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).periodicity := l_table.Periodicity;
1413: --Fields
1414:
1415: OPEN c4 (Upper(l_table.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema));
1416: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields := 0;
1411: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Name := l_table.Name;
1412: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).periodicity := l_table.Periodicity;
1413: --Fields
1414:
1415: OPEN c4 (Upper(l_table.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema));
1416: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields := 0;
1417: LOOP
1418: FETCH c4 INTO cRow4;
1419: EXIT WHEN c4%NOTFOUND;
1412: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).periodicity := l_table.Periodicity;
1413: --Fields
1414:
1415: OPEN c4 (Upper(l_table.Name), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema));
1416: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields := 0;
1417: LOOP
1418: FETCH c4 INTO cRow4;
1419: EXIT WHEN c4%NOTFOUND;
1420: IF BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields IS NOT NULL THEN
1416: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields := 0;
1417: LOOP
1418: FETCH c4 INTO cRow4;
1419: EXIT WHEN c4%NOTFOUND;
1420: IF BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields IS NOT NULL THEN
1421: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1417: LOOP
1418: FETCH c4 INTO cRow4;
1419: EXIT WHEN c4%NOTFOUND;
1420: IF BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields IS NOT NULL THEN
1421: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1418: FETCH c4 INTO cRow4;
1419: EXIT WHEN c4%NOTFOUND;
1420: IF BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields IS NOT NULL THEN
1421: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1426: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields :=
1420: IF BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields IS NOT NULL THEN
1421: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1426: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields :=
1427: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields + 1;
1428: END Loop;
1421: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1426: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields :=
1427: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields + 1;
1428: END Loop;
1429: Close c4;
1422: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||',';
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1426: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields :=
1427: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields + 1;
1428: END Loop;
1429: Close c4;
1430: --Indicators
1423: END IF;
1424: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields :=
1425: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Fields||cRow4.COLUMN_NAME;
1426: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields :=
1427: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).numFields + 1;
1428: END Loop;
1429: Close c4;
1430: --Indicators
1431: --The indicators array is initialized in the procedure -DocumentacionGrafo-
1430: --Indicators
1431: --The indicators array is initialized in the procedure -DocumentacionGrafo-
1432: --We do it there to avoid another recursive procedure
1433:
1434: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Indicators :=
1435: getIndicsForTable(l_table.name, BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).NumIndicators);
1436:
1437: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := BSC_METADATA_OPTIMIZER_PKG.gnumNewITables + 1;
1438: End If;
1431: --The indicators array is initialized in the procedure -DocumentacionGrafo-
1432: --We do it there to avoid another recursive procedure
1433:
1434: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Indicators :=
1435: getIndicsForTable(l_table.name, BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).NumIndicators);
1436:
1437: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := BSC_METADATA_OPTIMIZER_PKG.gnumNewITables + 1;
1438: End If;
1439: END Loop;
1433:
1434: BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).Indicators :=
1435: getIndicsForTable(l_table.name, BSC_METADATA_OPTIMIZER_PKG.garrNewITables(BSC_METADATA_OPTIMIZER_PKG.gnumNewITables).NumIndicators);
1436:
1437: BSC_METADATA_OPTIMIZER_PKG.gnumNewITables := BSC_METADATA_OPTIMIZER_PKG.gnumNewITables + 1;
1438: End If;
1439: END Loop;
1440:
1441:
1447: SELECT PROPERTY_VALUE
1448: FROM BSC_KPI_PROPERTIES P
1449: WHERE UPPER(PROPERTY_CODE) = ''DB_TRANSFORM''
1450: AND P.INDICATOR = T.INDICATOR)';
1451: if (g_mode=1 and BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1452: l_stmt := l_stmt||' and indicator in (select indicator from bsc_tmp_opt_ui_kpis where process_id=:1)';
1453: OPEN cv for l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processid;
1454: else
1455: OPEN cv for l_stmt;
1449: WHERE UPPER(PROPERTY_CODE) = ''DB_TRANSFORM''
1450: AND P.INDICATOR = T.INDICATOR)';
1451: if (g_mode=1 and BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE<>0) then
1452: l_stmt := l_stmt||' and indicator in (select indicator from bsc_tmp_opt_ui_kpis where process_id=:1)';
1453: OPEN cv for l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processid;
1454: else
1455: OPEN cv for l_stmt;
1456: end if;
1457: numTablesCod0 := 0;
1470: null;
1471: Else
1472: KeyColumn := GetLevelPKCol(Indicator, Configuration, arrFilterDims(i));
1473: If KeyColumn IS NOT NULL Then
1474: l_table_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TableName);
1475: l_key_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys, keyColumn);
1476: BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys(l_key_index).needsCode0 := true;
1477: --update BSC_TMP_OPT_key_cols set need_zero_code = 1 where table_name = TableName and key_name = keyColumn;
1478: If BSC_MO_HELPER_PKG.findIndexVARCHAR2(arrTablesCod0, TableName)<0 Then
1471: Else
1472: KeyColumn := GetLevelPKCol(Indicator, Configuration, arrFilterDims(i));
1473: If KeyColumn IS NOT NULL Then
1474: l_table_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TableName);
1475: l_key_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys, keyColumn);
1476: BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys(l_key_index).needsCode0 := true;
1477: --update BSC_TMP_OPT_key_cols set need_zero_code = 1 where table_name = TableName and key_name = keyColumn;
1478: If BSC_MO_HELPER_PKG.findIndexVARCHAR2(arrTablesCod0, TableName)<0 Then
1479: arrTablesCod0(numTablesCod0) := TableName;
1472: KeyColumn := GetLevelPKCol(Indicator, Configuration, arrFilterDims(i));
1473: If KeyColumn IS NOT NULL Then
1474: l_table_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables, TableName);
1475: l_key_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys, keyColumn);
1476: BSC_METADATA_OPTIMIZER_PKG.gTables(l_table_index).keys(l_key_index).needsCode0 := true;
1477: --update BSC_TMP_OPT_key_cols set need_zero_code = 1 where table_name = TableName and key_name = keyColumn;
1478: If BSC_MO_HELPER_PKG.findIndexVARCHAR2(arrTablesCod0, TableName)<0 Then
1479: arrTablesCod0(numTablesCod0) := TableName;
1480: numTablesCod0 := numTablesCod0 + 1;
1485: END LOOP;
1486: END Loop;
1487: Close cv;
1488:
1489: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1490: bsc_mo_helper_pkg.writeTmp('numTablesCod0 = '||numTablesCod0 );
1491: END IF;
1492: If numTablesCod0 > 0 Then
1493: SetNeedCod0InOriTables( arrTablesCod0, numTablesCod0);
1491: END IF;
1492: If numTablesCod0 > 0 Then
1493: SetNeedCod0InOriTables( arrTablesCod0, numTablesCod0);
1494: End If;
1495: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1496: bsc_mo_helper_pkg.writeTmp('Completed inicalltables '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
1497: END IF;
1498: Exception when others then
1499: bsc_mo_helper_pkg.writeTmp('l_stmt='||l_stmt, fnd_log.level_exception, true);
1497: END IF;
1498: Exception when others then
1499: bsc_mo_helper_pkg.writeTmp('l_stmt='||l_stmt, fnd_log.level_exception, true);
1500: BSC_MO_HELPER_PKG.TerminateWithMsg(' Exception in inicalltables:'||sqlerrm||', dumping gTables ');
1501: bsc_mo_helper_pkg.write_this(bsc_metadata_optimizer_pkg.gTables, FND_LOG.LEVEL_EXCEPTION, true);
1502: raise;
1503: End;
1504:
1505: --****************************************************************************
1528: ORDER BY INDICATOR;
1529: cRow c1%ROWTYPE;
1530: l_impl_type number;
1531: BEGIN
1532: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1533: bsc_mo_helper_pkg.writeTmp('Inside InicAllIndicadores', FND_LOG.LEVEL_PROCEDURE);
1534: END IF;
1535:
1536: gDocIndicators.delete;
1543: l_indic_type := cRow.INDICATOR_TYPE;
1544: l_config_type := cRow.CONFIG_TYPE;
1545: Per_Inter := cRow.PERIODICITY_ID;
1546: OptimizationMode := BSC_MO_HELPER_PKG.getKPIPropertyValue(Cod, 'DB_TRANSFORM', 1);
1547: l_impl_type := BSC_MO_HELPER_PKG.getKPIPropertyValue(Cod, BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE, 1);
1548: Action_Flag := cRow.PROTOTYPE_FLAG;
1549: Share_Flag := cRow.SHARE_FLAG;
1550: If cRow.SOURCE_INDICATOR IS NULL Then
1551: Source_Indicator := 0;
1559: Share_Flag, Source_Indicator, EDW_Flag, l_impl_type);
1560:
1561: END Loop;
1562: Close c1;
1563: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1564: bsc_mo_helper_pkg.writeTmp('Completed InicAllIndicadores', FND_LOG.LEVEL_PROCEDURE);
1565: END IF;
1566: EXCEPTION WHEN OTHERS THEN
1567: BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InicAllIndicadores :'||sqlerrm);
1588:
1589: l_parents_relcol DBMS_SQL.varchar2_table;
1590: BEGIN
1591:
1592: l_temp := BSC_MO_HELPER_PKG.findIndex( BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
1593: l_parents_relcol := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).parent_rel_col, ',');
1594:
1595: IF (l_parents_relcol.count=0) THEN
1596: return -1;
1589: l_parents_relcol DBMS_SQL.varchar2_table;
1590: BEGIN
1591:
1592: l_temp := BSC_MO_HELPER_PKG.findIndex( BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
1593: l_parents_relcol := BSC_MO_HELPER_PKG.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).parent_rel_col, ',');
1594:
1595: IF (l_parents_relcol.count=0) THEN
1596: return -1;
1597: END IF;
1633: l_temp NUMBER;
1634: l_auxillaryFields DBMS_SQL.varchar2_table;
1635: BEGIN
1636:
1637: l_temp := BSC_MO_HELPER_PKG.findIndex( BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
1638:
1639: IF l_temp = -1 THEN
1640: return -1;
1641: END IF;
1639: IF l_temp = -1 THEN
1640: return -1;
1641: END IF;
1642:
1643: l_auxillaryFields := bsc_mo_helper_pkg.getDecomposedString(BSC_METADATA_OPTIMIZER_PKG.gMasterTable(L_TEMP).auxillaryFields, ',');
1644: IF (l_auxillaryFields.count=0) THEN
1645: return -1;
1646: END IF;
1647:
1646: END IF;
1647:
1648: i := l_AuxillaryFields.first;
1649: LOOP
1650: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1651: bsc_mo_helper_pkg.writeTmp('looping...');
1652: END IF;
1653:
1654: IF UPPER(l_AuxillaryFields(i)) = UPPER(CampoAuxiliar) Then
1720: -- Tabla: object with the information of the MN dimension table
1721: -- InputTableFlag: True: Write the description of the input table
1722: -- False: Write the description of the dimension table
1723: --****************************************************************************
1724: PROCEDURE WriteMNDescription(Tabla IN BSC_METADATA_OPTIMIZER_PKG.clsRelationMN, InputTableFlag IN Boolean) IS
1725: TABLELENGTH NUMBER := 25;
1726: FIELDLENGTH NUMBER := 30;
1727: TYPELENGTH NUMBER := 15;
1728: SIZELENGTH NUMBER := 8;
1744: AND UPPER(OWNER) = p2
1745: ORDER BY COLUMN_ID;
1746: cRow c1%ROWTYPE;
1747: BEGIN
1748: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1749: bsc_mo_helper_pkg.WriteTmp ('Inside WriteMNDescription', FND_LOG.LEVEL_PROCEDURE);
1750: END IF;
1751: --Table name
1752: If InputTableFlag Then
1771: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE'), TYPELENGTH) ||
1772: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SIZE'), SIZELENGTH) ||
1773: StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DESCRIPTION'), DESCLENGTH));
1774: --Columns
1775: OPEN c1 (Upper(TableName), UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema) );
1776: LOOP
1777: FETCH c1 INTO cRow;
1778: EXIT WHEN c1%NOTFOUND;
1779: rsColumn_Name := cRow.COLUMN_NAME;
1820: ELSE
1821: Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
1822: End If;
1823: If UPPER(substr(rsColumn_Name, -4)) = '_USR' Then
1824: iRel := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, substr(rsColumn_Name, 1, Length(rsColumn_Name) - 4));
1825: If iRel <> -1 Then
1826: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
1827: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(iRel).Name || '.USER_CODE');
1828: msg := fnd_message.get;
1823: If UPPER(substr(rsColumn_Name, -4)) = '_USR' Then
1824: iRel := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, substr(rsColumn_Name, 1, Length(rsColumn_Name) - 4));
1825: If iRel <> -1 Then
1826: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
1827: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(iRel).Name || '.USER_CODE');
1828: msg := fnd_message.get;
1829: Linea := Linea || StrFix(msg, DESCLENGTH);
1830: End If;
1831: Else
1828: msg := fnd_message.get;
1829: Linea := Linea || StrFix(msg, DESCLENGTH);
1830: End If;
1831: Else
1832: iRel := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, rsColumn_Name);
1833: If iRel <> -1 Then
1834: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
1835: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(iRel).Name || '.CODE');
1836: msg := fnd_message.get;
1831: Else
1832: iRel := BSC_MO_HELPER_PKG.findKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, rsColumn_Name);
1833: If iRel <> -1 Then
1834: fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
1835: fnd_message.set_token('TABLE_NAME', BSC_METADATA_OPTIMIZER_PKG.gMasterTable(iRel).Name || '.CODE');
1836: msg := fnd_message.get;
1837: Linea := Linea || StrFix(msg, DESCLENGTH);
1838: End If;
1839: End If;
1841: END Loop;
1842: Close c1;
1843: WriteLineTextFile ('');
1844: WriteLineTextFile ('');
1845: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1846: bsc_mo_helper_pkg.WriteTmp ('Completed WriteMNDescription', FND_LOG.LEVEL_PROCEDURE);
1847: END IF;
1848: EXCEPTION WHEN OTHERS THEN
1849: g_error := sqlerrm;
1859: -- Tabla: object with the information of the dimension table
1860: -- InputTableFlag: True: Write the description of the input table
1861: -- False: Write the description of the dimension table
1862: --****************************************************************************
1863: PROCEDURE WriteDimDescription(Tabla IN BSC_METADATA_OPTIMIZER_PKG.clsMasterTable, InputTableFlag Boolean) IS
1864: TABLELENGTH NUMBER := 25;
1865: FIELDLENGTH NUMBER := 30;
1866: TYPELENGTH NUMBER := 15;
1867: SIZELENGTH NUMBER := 8;
1886: l_DATA_PRECISION NUMBER;
1887: l_DATA_SCALE NUMBER;
1888: l_bind_Case number;
1889: BEGIN
1890: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1891: bsc_mo_helper_pkg.writeTmp('Inside WriteDimDescription for '||Tabla.Name);
1892: END IF;
1893: l_parents := bsc_mo_helper_pkg.getDecomposedString(tabla.parent_name, ',');
1894: --BIS DIMENSIONS: We are going to print the documentation only if the view exists.
1920: bsc_mo_helper_pkg.writeTmp('Stmt is '||l_stmt);
1921: if (l_bind_case =1) THEN
1922: OPEN CV for l_stmt using UPPER(Tabla.Name);
1923: ELSIF l_bind_case=2 THEN
1924: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.InputTable);
1925: ELSE
1926: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.Name);
1927: END IF;
1928: bsc_mo_helper_pkg.writeTmp('Bind case is '||l_bind_case);
1922: OPEN CV for l_stmt using UPPER(Tabla.Name);
1923: ELSIF l_bind_case=2 THEN
1924: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.InputTable);
1925: ELSE
1926: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.Name);
1927: END IF;
1928: bsc_mo_helper_pkg.writeTmp('Bind case is '||l_bind_case);
1929: FETCH cv INTO l_COLUMN_NAME, l_DATA_TYPE, l_DATA_LENGTH, l_DATA_PRECISION, l_DATA_SCALE;
1930: If CV%NOTFOUND Then
1930: If CV%NOTFOUND Then
1931: --The table or view does not exist in the database.
1932: --So we cannot write the documentation
1933: Close CV;
1934: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1935: bsc_mo_helper_pkg.writeTmp('Completed WriteDimDescription');
1936: END IF;
1937: return;
1938: End If;
1977: --Columns
1978: if (l_bind_case =1) THEN
1979: OPEN CV for l_stmt using UPPER(Tabla.Name);
1980: ELSIF l_bind_case=2 THEN
1981: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.InputTable);
1982: ELSE
1983: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.Name);
1984: END IF;
1985:
1979: OPEN CV for l_stmt using UPPER(Tabla.Name);
1980: ELSIF l_bind_case=2 THEN
1981: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.InputTable);
1982: ELSE
1983: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.Name);
1984: END IF;
1985:
1986: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1987: bsc_mo_helper_pkg.writeTmp('Doc, writing columns ');
1982: ELSE
1983: OPEN CV for l_stmt using UPPER(BSC_METADATA_OPTIMIZER_PKG.gBscSchema), UPPER(Tabla.Name);
1984: END IF;
1985:
1986: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1987: bsc_mo_helper_pkg.writeTmp('Doc, writing columns ');
1988: END IF;
1989: LOOP
1990: FETCH cv INTO l_COLUMN_NAME, l_DATA_TYPE, l_DATA_LENGTH, l_DATA_PRECISION, l_DATA_SCALE;
1998: If rsData_Length = 0 Then
1999: rsData_Length := null;
2000: End If;
2001: End If;
2002: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2003: bsc_mo_helper_pkg.writeTmp('Columns Name is '||rsColumn_Name||', data type='||rsData_type);
2004: END IF;
2005: If l_DATA_PRECISION IS NULL Then
2006: rsData_Precision := null;
2068: End If;
2069: WriteLineTextFile (Linea);
2070: END Loop;
2071: Close cv;
2072: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2073: bsc_mo_helper_pkg.writeTmp('Doc, done writing columns ');
2074: END IF;
2075: If InputTableFlag Then
2076: --Input table
2081: WriteLineTextFile('');
2082: WriteLineTextFile('');
2083: End If;
2084: End If;
2085: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2086: bsc_mo_helper_pkg.writeTmp('Completed WriteDimDescription');
2087: END IF;
2088: EXCEPTION WHEN OTHERS THEN
2089: BSC_MO_HELPER_PKG.writeTmp('l_stmt = '||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
2100: --
2101: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2102: --****************************************************************************
2103: PROCEDURE DocumentDimensionTables IS
2104: dim BSC_METADATA_OPTIMIZER_PKG.clsMasterTable;
2105: dimMN BSC_METADATA_OPTIMIZER_PKG.clsRelationMN;
2106: title VARCHAR2(300);
2107: BEGIN
2108: --Description of dimension tables
2101: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2102: --****************************************************************************
2103: PROCEDURE DocumentDimensionTables IS
2104: dim BSC_METADATA_OPTIMIZER_PKG.clsMasterTable;
2105: dimMN BSC_METADATA_OPTIMIZER_PKG.clsRelationMN;
2106: title VARCHAR2(300);
2107: BEGIN
2108: --Description of dimension tables
2109: title := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'DIMENSION_TABLES_DESCRIPTION');
2110: WriteLineTextFile(title);
2111: WriteLineTextFile(StrX('-', Length(title)));
2112: WriteLineTextFile ('');
2113: --1N Dimensions
2114: IF (BSC_METADATA_OPTIMIZER_PKG.gMasterTable.count>0) THEN
2115: For i IN BSC_METADATA_OPTIMIZER_PKG.gMasterTable.first..BSC_METADATA_OPTIMIZER_PKG.gMasterTable.last LOOP
2116: dim := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(i);
2117: WriteDimDescription( dim, False);
2118: --BIS DIMENSIONS: BIS dimensions does not have input table.
2111: WriteLineTextFile(StrX('-', Length(title)));
2112: WriteLineTextFile ('');
2113: --1N Dimensions
2114: IF (BSC_METADATA_OPTIMIZER_PKG.gMasterTable.count>0) THEN
2115: For i IN BSC_METADATA_OPTIMIZER_PKG.gMasterTable.first..BSC_METADATA_OPTIMIZER_PKG.gMasterTable.last LOOP
2116: dim := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(i);
2117: WriteDimDescription( dim, False);
2118: --BIS DIMENSIONS: BIS dimensions does not have input table.
2119: If dim.Source = 'BSC' Then
2112: WriteLineTextFile ('');
2113: --1N Dimensions
2114: IF (BSC_METADATA_OPTIMIZER_PKG.gMasterTable.count>0) THEN
2115: For i IN BSC_METADATA_OPTIMIZER_PKG.gMasterTable.first..BSC_METADATA_OPTIMIZER_PKG.gMasterTable.last LOOP
2116: dim := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(i);
2117: WriteDimDescription( dim, False);
2118: --BIS DIMENSIONS: BIS dimensions does not have input table.
2119: If dim.Source = 'BSC' Then
2120: --Only for BSC Dimensions
2122: End If;
2123: END LOOP;
2124: END IF;
2125: --MN Dimensions
2126: IF (BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.count>0) THEN
2127: For i IN BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first..BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last LOOP
2128: dimMN := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i);
2129: WriteMNDescription(dimMN, False);
2130: WriteMNDescription(dimMN, True);
2123: END LOOP;
2124: END IF;
2125: --MN Dimensions
2126: IF (BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.count>0) THEN
2127: For i IN BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first..BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last LOOP
2128: dimMN := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i);
2129: WriteMNDescription(dimMN, False);
2130: WriteMNDescription(dimMN, True);
2131: END LOOP;
2124: END IF;
2125: --MN Dimensions
2126: IF (BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.count>0) THEN
2127: For i IN BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.first..BSC_METADATA_OPTIMIZER_PKG.gRelationsMN.last LOOP
2128: dimMN := BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(i);
2129: WriteMNDescription(dimMN, False);
2130: WriteMNDescription(dimMN, True);
2131: END LOOP;
2132: END IF;
2149: BEGIN
2150: --Open file of description of system tables (dimension, input and system tables)
2151: g_mode := pMode;
2152: Initialize;
2153: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2154: BSC_MO_HELPER_PKG.writeTmp('Starting InicAllIndicadores', FND_LOG.LEVEL_STATEMENT);
2155: END IF;
2156: --Initialize collection gIndicadores with ALL indicators
2157: InicAllIndicadores;
2154: BSC_MO_HELPER_PKG.writeTmp('Starting InicAllIndicadores', FND_LOG.LEVEL_STATEMENT);
2155: END IF;
2156: --Initialize collection gIndicadores with ALL indicators
2157: InicAllIndicadores;
2158: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2159: BSC_MO_HELPER_PKG.writeTmp('Starting InicAllTables', FND_LOG.LEVEL_STATEMENT);
2160: END IF;
2161: --Initialize collection gTablas with ALL tables
2162: InicAllTables;
2159: BSC_MO_HELPER_PKG.writeTmp('Starting InicAllTables', FND_LOG.LEVEL_STATEMENT);
2160: END IF;
2161: --Initialize collection gTablas with ALL tables
2162: InicAllTables;
2163: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2164: BSC_MO_HELPER_PKG.writeTmp('Inside Documentation', FND_LOG.LEVEL_PROCEDURE);
2165: END IF;
2166: writeDocHeader;
2167: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2163: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2164: BSC_MO_HELPER_PKG.writeTmp('Inside Documentation', FND_LOG.LEVEL_PROCEDURE);
2165: END IF;
2166: writeDocHeader;
2167: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2168: BSC_MO_HELPER_PKG.writeTmp('Wrote Doc Header', FND_LOG.LEVEL_STATEMENT);
2169: END IF;
2170: WriteLineTextFile( '');
2171: WriteLineTextFile( BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TIME') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || to_Char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2171: WriteLineTextFile( BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TIME') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || to_Char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2172: WriteLineTextFile( '');
2173: --Description of data tables
2174: IF (pMode = 2) THEN
2175: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2176: BSC_MO_HELPER_PKG.writeTmp('Calling DocumentDimensionTables', FND_LOG.LEVEL_STATEMENT);
2177: END IF;
2178: DocumentDimensionTables;
2179: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2175: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2176: BSC_MO_HELPER_PKG.writeTmp('Calling DocumentDimensionTables', FND_LOG.LEVEL_STATEMENT);
2177: END IF;
2178: DocumentDimensionTables;
2179: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2180: BSC_MO_HELPER_PKG.writeTmp('Calling WriteInputAndSystemTables', FND_LOG.LEVEL_STATEMENT);
2181: END IF;
2182: WriteInputAndSystemTables;
2183: return;
2181: END IF;
2182: WriteInputAndSystemTables;
2183: return;
2184: END IF;
2185: If BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE <> 9 And BSC_METADATA_OPTIMIZER_PKG.gSYSTEM_STAGE = 2 Then
2186: --Result report
2187: --Open the result report file
2188: WriteLineTextFile( '');
2189: WriteLineTextFile( BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TIME') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ' || to_char(sysdate, 'MMMM DD, YYYY HH:MM:SS'));
2187: --Open the result report file
2188: WriteLineTextFile( '');
2189: WriteLineTextFile( BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TIME') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ' || to_char(sysdate, 'MMMM DD, YYYY HH:MM:SS'));
2190: WriteLineTextFile( '');
2191: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2192: BSC_MO_HELPER_PKG.writeTmp('Starting DocResult '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
2193: END IF;
2194: -- to be called from BSC_METADATA_OPTIMIZER_PKG.Documentation
2195: DocResult;
2190: WriteLineTextFile( '');
2191: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2192: BSC_MO_HELPER_PKG.writeTmp('Starting DocResult '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
2193: END IF;
2194: -- to be called from BSC_METADATA_OPTIMIZER_PKG.Documentation
2195: DocResult;
2196: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2197: BSC_MO_HELPER_PKG.writeTmp('Done with DocResult', FND_LOG.LEVEL_STATEMENT);
2198: END IF;
2192: BSC_MO_HELPER_PKG.writeTmp('Starting DocResult '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT);
2193: END IF;
2194: -- to be called from BSC_METADATA_OPTIMIZER_PKG.Documentation
2195: DocResult;
2196: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2197: BSC_MO_HELPER_PKG.writeTmp('Done with DocResult', FND_LOG.LEVEL_STATEMENT);
2198: END IF;
2199: End If;
2200: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2196: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2197: BSC_MO_HELPER_PKG.writeTmp('Done with DocResult', FND_LOG.LEVEL_STATEMENT);
2198: END IF;
2199: End If;
2200: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2201: BSC_MO_HELPER_PKG.writeTmp('Completed Documentation', FND_LOG.LEVEL_PROCEDURE);
2202: END IF;
2203: EXCEPTION WHEN OTHERS THEN
2204: g_error := sqlerrm;