[Home] [Help]
PACKAGE BODY: APPS.BSC_MO_DOC_PKG
Source
1 PACKAGE BODY BSC_MO_DOC_PKG AS
2 /* $Header: BSCMODCB.pls 120.9.12000000.2 2007/02/13 09:02:00 rkumar ship $ */
3
4 gNameFileSTables VARCHAR2(1000);
5 g_error VARCHAR2(2000);
6 g_newline VARCHAR2(10):= '
7 ';
8 g_stack VARCHAR2(32767);
9
10 g_mode NUMBER := 1;
11
12 TYPE cPerCalMap IS RECORD(
13 periodicity_name varchar2(200),
14 calendar_id number,
15 calendar_name varchar2(200));
16 TYPE tab_cPerCalMap is table of cPerCalMap index by PLS_INTEGER;
17 gPeriod_CalName tab_cPerCalMap;
18 gPeriod_Cal_Init boolean := false;
19
20 PROCEDURE InitializePerCalMap IS
21 CURSOR perCal is
22 SELECT periodicity_id, per.name periodicity_name, cal.calendar_id, cal.NAME calendar_name
23 FROM BSC_SYS_CALENDARS_VL cal
24 , BSC_SYS_PERIODICITIES_VL per
25 WHERE per.CALENDAR_ID = cal.calendar_id
26 order by periodicity_id;
27 --TYPE l_record IS perCal%ROWTYPE;
28 TYPE l_table IS TABLE OF perCal%ROWTYPE;
29
30 l_records l_table;
31 BEGIN
32 open perCal;
33 fetch perCal BULK COLLECT INTO l_records;
34 close perCal;
35 for i in 1..l_records.count loop
36 gPeriod_CalName(l_records(i).periodicity_id).periodicity_name := l_records(i).periodicity_name;
37 gPeriod_CalName(l_records(i).periodicity_id).calendar_id := l_records(i).calendar_id;
38 gPeriod_CalName(l_records(i).periodicity_id).calendar_name := l_records(i).calendar_name;
39 end loop;
40 END;
41
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');
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
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;
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;
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;
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);
90 END IF;
91
92 BSC_MO_HELPER_PKG.InitializeMasterTables;
93 BSC_MO_HELPER_PKG.InitLOV;
94
95 EXCEPTION WHEN OTHERS THEN
96 g_error := sqlerrm;
97 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.Initialize :' ||g_error);
98 raise;
99 END;
100 --****************************************************************************
101 -- GetDbObjectType
102 -- DESCRIPTION:
103 -- Return object type after reading from database
104 --****************************************************************************
105
106 Function GetDbObjectType(objName IN VARCHAR2) RETURN VARCHAR2 IS
107 CURSOR cObjectType IS
108 SELECT OBJECT_TYPE FROM USER_OBJECTS
109 WHERE OBJECT_NAME = objName;
110 l_type VARCHAR2(100);
111 BEGIN
112 OPEN cObjectType;
113 FETCH cObjectType INTO l_type;
114 CLOSE cObjectType;
115 return l_type;
116 EXCEPTION WHEN OTHERS THEN
117 g_error := sqlerrm;
118 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetDbObjectType :' ||g_error);
119 raise;
120 End ;
121
122
123
124 --****************************************************************************
125 -- GetSumTableMVName
126 -- DESCRIPTION:
127 -- Get the name of the summary MV.
128 --
129 -- PARAMETERS:
130 -- TableName: Summary Table Name
131 --****************************************************************************
132
133 Function GetSumTableMVName(TableName IN VARCHAR2) RETURN VARCHAR2 IS
134 pos NUMBER;
135 pos1 NUMBER;
136 MVName VARCHAR2(30);
137 BEGIN
138 pos := InStr(TableName, '_', -1);
139
140 If pos > 0 Then
141 MVName := substr(TableName, 1, pos) || 'MV';
142 Else
143 MVName := TableName || '_MV';
144 End If;
145
146 Return MVName;
147 EXCEPTION WHEN OTHERS THEN
148 g_error := sqlerrm;
149 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetSumTableMVName :' ||g_error);
150 bsc_mo_helper_pkg.writeTmp('TableName :' ||TableName||', pos='||pos, fnd_log.level_statement, true);
151 raise;
152 End ;
153 --****************************************************************************
154 -- WRITELINETEXTFILE
155 -- DESCRIPTION:
156 -- Write to file
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
166 --****************************************************************************
167 -- EscribirEncabezado : WriteDocHeader
168 --
169 -- DESCRIPTION:
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( '+---------------------------------------------------------------------------+');
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
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 ;
191 --****************************************************************************
192 -- GetPeriodicityName : GetNombrePeriodicity
193 -- DESCRIPTION:
194 -- Get the name of the given periodicity.
195 --
196 -- PARAMETERS:
197 -- Periodicity: periodicity code
198 --****************************************************************************
199 Function GetPeriodicityName(Periodicity IN NUMBER) RETURN VARCHAR2 IS
200 CURSOR c1 IS
201 SELECT NAME FROM BSC_SYS_PERIODICITIES_VL
202 WHERE PERIODICITY_ID = Periodicity;
203 l_name varchar2(100) := null ;
204
205 BEGIN
206 IF (gPeriod_Cal_Init= false) then
207 InitializePerCalMap;
208 gPeriod_Cal_Init := true;
209 END IF;
210
211 return gPeriod_CalName(periodicity).periodicity_name;
212 /*
213 OPEN c1;
214 FETCH c1 INTO l_name;
215 CLOSE c1;
216 return l_name;
217 */
218 EXCEPTION WHEN OTHERS THEN
219 g_error := sqlerrm;
220 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetPeriodicityName :' ||g_error);
221 raise;
222 End ;
223
224 --****************************************************************************
225 Function GetPeriodicityCalendarName(periodicity_id IN NUMBER) RETURN VARCHAR2 IS
226 --Fix bug#2436864 Do not use collection becasue the periodicity could
227 -- be deleted
228 --Fix bug#1928043
229 --GetPeriodicityCalendarName = gCalendars(Trim(gPeriodicityes(Trim(periodicity_id)).Calendar_id)).Name
230
231 CURSOR c1 (p1 NUMBER) IS
232 SELECT NAME FROM BSC_SYS_CALENDARS_VL
233 WHERE CALENDAR_ID = (
234 SELECT CALENDAR_ID
235 FROM BSC_SYS_PERIODICITIES_VL
236 WHERE PERIODICITY_ID = p1);
237
238 l_name VARCHAR2(100);
239 BEGIN
240 IF (gPeriod_Cal_Init= false) then
241 InitializePerCalMap;
242 gPeriod_Cal_Init := true;
243 END IF;
244
245 return gPeriod_CalName(periodicity_id).calendar_name;
246
247 /* OPEN c1(periodicity_id);
248 FETCH c1 INTO l_name;
249 CLOSE c1;
250 return l_name;
251 */
252 EXCEPTION WHEN OTHERS THEN
253 g_error := sqlerrm;
254 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetPeriodicityCalendarName, periodicity ='||periodicity_id||' :' ||g_error);
255 raise;
256 End;
257
258
259
260 --****************************************************************************
261 -- StrX
262 --
263 -- DESCRIPTION:
264 -- Returns a string of the given lenght and made up of the given char.
265 -- Example: xcaracter = '-', p_length = 6
266 -- StrX = '------'
267 -- PARAMETERS:
268 -- xcaracter: char
269 -- p_length: lenght
270 --****************************************************************************
271 Function StrX(xcaracter IN VARCHAR2 , p_length IN NUMBER) RETURN VARCHAR2 IS
272 l_res VARCHAR2(4000) := null;
273 BEGIn
274
275 For j IN 1..p_length LOOP
276 l_res := l_res ||xcaracter;
277 END LOOP;
278 return l_res;
279
280 End ;
281 --****************************************************************************
282 -- DocResult : DocumentacionResult
283 --
284 -- DESCRIPTION:
285 -- Generate the result report
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;
295 ShowOldITables Boolean;
296 FirstTable Boolean;
297 PeriodicityName VARCHAR2(100);
298 CalendarName VARCHAR2(100);
299 l_index1 NUMBER;
300 l_index2 NUMBER;
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'))));
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
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';
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;
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
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
345 WriteLineTextFile (Indicator.Code || ' ' || Indicator.Name);
346 Else
347 WriteLineTextFile (Indicator.Code || ' ' || Indicator.Name || ' (' ||BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'EDW') || ')');
348 End If;
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);
358 ShowITables := False;
359 ShowOldITables := False;
360 Else
361 --There was just a change in the summarization level
362 --Input tables were not changed
363 --Bug#3306248
364 WriteLineTextFile (l_sumLevelChangedMsg);
365 fnd_message.set_name('BSC', 'BSC_INPUT_TABLES_NO_CHANGED');
366 msg := fnd_message.get;
367 WriteLineTextFile (msg);
368 ShowITables := False;
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');
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;
386 Else
387 --New indicator
388 WriteLineTextFile (BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'NEW_INDICATOR'));
389 ShowITables := False;
390 ShowOldITables := False;
391 End If;
392 ELSIF (Indicator.Action_Flag = 2) THEN
393 --Case 2
394 --Deleted
395 WriteLineTextFile(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'DELETED'));
396 ShowITables := False;
397 ShowOldITables := True;
398 ELSIF (Indicator.Action_Flag = 4 OR Indicator.Action_Flag = 5 OR Indicator.Action_Flag = 7) THEN
399 --Case 4, 5, 7
400 --Non-structural change(s):Change color or group functions.
401 --Input tables were not changed
402 msg := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'NONSTRUCTURAL_CHANGE');
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;
412 WriteLineTextFile (msg);
413 ShowITables := False;
414 ShowOldITables := False;
415 --Case Else
416 ELSE
417 ShowITables := False;
418 ShowOldITables := False;
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
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, ',');
438 --fields
439 j := l_fields.first;
440 LOOP
441 EXIT WHEN l_fields.count=0;
442 msg := StrX(' ', INDENT) || l_Fields(j);
443 WriteLineTextFile (msg );
444 EXIT WHEN j = l_fields.last;
445 j := l_fields.next(j);
446 END LOOP;
447 End If;
448 END LOOP;
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
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);
461 WriteLineTextFile (msg);
462 msg := StrFix(StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'OLD_INPUT_TABLE'))), TABLELENGTH) || StrFix(StrX('-', Length(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'BACKUP_TABLE'))), TABLELENGTH);
463 WriteLineTextFile (msg);
464 FirstTable := False;
465 End If;
466
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);
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
490 EXIT WHEN l_fields.count=0;
491 msg := StrX(' ', TABLELENGTH) || StrX(' ', INDENT) || l_Fields(j);
492 WriteLineTextFile (msg);
493 EXIT WHEN j= l_fields.last;
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;
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');
510 bsc_mo_helper_pkg.writeTmp('Exception in DocResult, g_stack = '||g_stack, FND_LOG.LEVEL_UNEXPECTED, true);
511 bsc_mo_helper_pkg.TerminateWithError('BSC_RESULT_REPORT_FAILED', 'DocResult');
512 raise;
513 End;
514
515 --****************************************************************************
516 -- GetMaxSubPeriodUsr
517 --
518 -- DESCRIPTION:
519 -- Returns the number of subperiods of the given periodicity.
520 -- It is read from BSC_SYS_PERIODICITIES table
521 --
522 -- PARAMETERS:
523 -- Periodicity: Periodicity code
524 --****************************************************************************
525 Function GetMaxSubPeriodUsr(Periodicity IN NUMBER) RETURN NUMBER IS
526
527 CURSOR c1 IS
528 SELECT NUM_OF_SUBPERIODS
529 FROM BSC_SYS_PERIODICITIES
530 WHERE PERIODICITY_ID = Periodicity;
531 l_num NUMBER := null;
532 BEGIn
533 OPEN c1;
534 FETCH c1 INTO l_num;
535 CLOSE c1;
536
537 IF (l_num is null ) THEN
538 return 0;
539 ELSE
540 return l_num;
541 END IF;
542 EXCEPTION WHEN OTHERS THEN
543 g_error := sqlerrm;
544 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetMaxSubPeriodUsr :' ||g_error);
545 raise;
546 End ;
547
548 --****************************************************************************
549 -- GetMaxPeriod
550 --
551 -- DESCRIPTION:
552 -- Return the number of periods of the given periodicity.
553 -- It is read from BSC_SYS_PERIODICITIES table
554 --
555 -- PARAMETERS:
556 -- Periodicity: Periodicity code
557 --****************************************************************************
558 Function GetMaxPeriod(Periodicity IN NUMBER) RETURN NUMBER IS
559 CURSOR c1 IS
560 SELECT NUM_OF_PERIODS
561 FROM BSC_SYS_PERIODICITIES
562 WHERE PERIODICITY_ID = Periodicity;
563 l_num NUMBER;
564 BEGIN
565 OPEN c1;
566 FETCH c1 INTO l_num;
567 CLOSE c1;
568 return l_num;
569 EXCEPTION WHEN OTHERS THEN
570 g_error := sqlerrm;
571 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetMaxPeriod :' ||g_error);
572 raise;
573 END;
574
575 --****************************************************************************
576 -- writeTableDescription
577 --
578 -- DESCRIPTION:
579 -- Write the description of the given table in the system tables
580 -- description file.
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;
590 SIZELENGTH NUMBER := 8;
591 DESCLENGTH NUMBER := 60;
592
593 l_periodicity_name VARCHAR2(100);
594 Linea VARCHAR2(1000);
595 l_period_column VARCHAR2(100);
596 MaxPeriod NUMBER;
597 l_subperiod_column VARCHAR2(100);
598 MaxSubPeriod NUMBER;
599 i NUMBER;
600
601 l_stmt VARCHAR2(1000);
602 rsColumn_Name VARCHAR2(100);
603 rsData_Type VARCHAR2(100);
604 rsData_Length VARCHAR2(100);
605 rsData_Precision VARCHAR2(100);
606 rsData_Scale VARCHAR2(100);
607 descCodeZero VARCHAR2(100);
608 msg VARCHAR2(100);
609 TableName VARCHAR2(100);
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);
619 l_DATA_LENGTH NUMBER;
620 l_DATA_PRECISION NUMBER;
621 l_DATA_SCALE NUMBER;
622 l_source VARCHAR2(30);
623 isBaseTable boolean;
624 cv CurTyp;
625
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;
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
660 l_periodicity_name := GetPeriodicityName(Tabla.Periodicity);
661 l_periodicity_name := l_periodicity_name || ' (' || GetPeriodicityCalendarName(Tabla.Periodicity) || ')';
662 l_period_column := BSC_MO_DB_PKG.GetPeriodColumnName(Tabla.Periodicity);
663 MaxPeriod := GetMaxPeriod(Tabla.Periodicity);
664 l_subperiod_column := BSC_MO_DB_PKG.GetSubperiodColumnName(Tabla.Periodicity);
665 MaxSubPeriod := GetMaxSubPeriodUsr(Tabla.Periodicity);
666 descCodeZero := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'REQUIRES_TOTALS_CODE_0');
667 Else
668 --system table
669 l_periodicity_name := GetPeriodicityName(Tabla.Periodicity) ;
670 l_periodicity_name := l_periodicity_name || ' (' || GetPeriodicityCalendarName(Tabla.Periodicity) || ')';
671 l_period_column := 'PERIOD';
672 MaxPeriod := GetMaxPeriod(Tabla.Periodicity);
673 l_subperiod_column := null;
674 MaxSubPeriod := 0;
675 End If;
676 --Table name and periodicity
677 If Tabla.EDW_Flag = 1 Then
678 TableName := Tabla.Name || ' (' || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'EDW') || ')';
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);
688 End If;
689 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'PERIODICITY') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) || l_periodicity_name);
690 Else
691 If Tabla.dbObjectType = 'VIEW' Then
692 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'VIEW') || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) || Tabla.MVName);
693 Else
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;
703 If l_mvname IS NOT NULL THEN
704 WriteLineTextFile (bsc_mo_helper_pkg.Get_LookUp_Value('BSC_UI_BACKEND', 'ASSOCIATED_ZMV') || bsc_mo_helper_pkg.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ' || l_mvname);
705 End If;
706 --Get the associated Projection table
707 OPEN cProjTable(substr(tabla.name,1, instr(tabla.name, '_', -1))||'PT');
708 FETCH cProjTable into l_mvname;
709 CLOSE cProjTable;
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 ';
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(+)) ';
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(+)) ';
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;
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
752 l_stmt := l_stmt ||' AND B.TABLE_NAME(+) = :2 AND A.COLUMN_NAME=upper(B.COLUMN_NAME(+)) ';
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);
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;
770 rsColumn_Name := l_COLUMN_NAME;
771 rsData_Type := l_DATA_TYPE;
772 rsData_Length := l_DATA_LENGTH;
773 If rsData_Length = 0 Then
774 rsData_Length := null;
775 End If;
776 rsData_Precision := l_DATA_PRECISION;
777 If rsData_Precision = 0 Then
778 rsData_Precision := null;
779 End If;
780 rsData_Scale := l_DATA_SCALE;
781 If rsData_Scale = 0 Then
782 rsData_Scale := null;
783 End If;
784 Linea := StrFix(rsColumn_Name, FIELDLENGTH);
785 Linea := Linea || StrFix(rsData_Type, TYPELENGTH);
786 If UPPER(rsData_Type) = 'NUMBER' Then
787 If rsData_Precision IS NULL Then
788 rsData_Length := null;
789 Linea := Linea || StrFix(' ', SIZELENGTH);
790 Else
791 If rsData_Scale IS NULL Then
792 rsData_Length := rsData_Precision;
793 Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
794 Else
795 rsData_Length := rsData_Precision;-- || ',' || rsData_Scale
796 IF (rsData_Length IS NOT NULL) THEN
797 Linea := Linea || StrFix(rsData_Length||',' || rsData_Scale, SIZELENGTH);
798 END IF;
799 End If;
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);
809 If Tabla.Type = 0 Then -- input table
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;
819 Linea := Linea || ' (' || msg || ')';
820 END IF;
821 If Tabla.Type = 0 Then --input table
822 l_temp := BSC_MO_HELPER_PKG.findIndex(Table_Keys, rsColumn_Name);
823 IF (l_temp <> -1) THEN
824 If Table_Keys(l_temp).NeedsCode0 Then
825 --If property NecesitaCod0 is TRUE the input table is for a precalculated indicator.
826 --The input tables for no-precalculated indicators have this property in FALSE
827 Linea := Linea || ' ' || descCodeZero;
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');
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;
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');
848 ElsIf UPPER(rsColumn_Name) = 'TYPE' Then
849 If (Tabla.EDW_Flag = 1 And Tabla.Type = 0) Or (Tabla.IsTargetTable) Then
850 --If the input table is for EDW or is a targets input table, say 'Type: 1: Plan. Do not load actuals.'
851 Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE_1_PLAN_EDW');
852 Else
853 Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE_0_ACTUAL_1_PLAN');
854 End If;
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.
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 ' ||
870 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_IVIEWER', 'TO') || ' ' || MaxPeriod;
871 End If;
872 END IF;
873 Else
874 --Do not mention more info about period. MV has multiple periodicities.
875 null;
876 --Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'PERIOD');
877 End If;
878 ElsIf UPPER(rsColumn_Name) = UPPER(l_subperiod_column) Then
879 Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SUBPERIOD_1_TO') || ' ' || MaxSubPeriod;
880 ElsIf UPPER(rsColumn_Name) = 'PERIODICITY_ID' OR UPPER(rsColumn_Name) = 'RECORD_TYPE' Then
881 Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
882 ELSE
883 Linea := Linea || BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INTERNAL_COLUMN');
884 End If;
885 WriteLineTextFile (Linea);
886 END Loop;
887 Close cv;
888 WriteLineTextFile ('');
889 WriteLineTextFile ('');
890 EXCEPTION WHEN OTHERS THEN
891 g_error := sqlerrm;
892 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.writeTableDescription :' ||sqlerrm);
893 fnd_message.set_name ('BSC', 'BSC_TABLENAME_DESCR_FAILED');
894 fnd_message.set_token('TABLE_NAME', Tabla.name);
895 g_error := fnd_message.get;
896 BSC_MO_HELPER_PKG.TerminateWithMsg(g_error);
897 raise;
898 End ;
899 --****************************************************************************
900 -- WriteInputAndSystemTables : DocumentacionTablasDatos
901 --
902 -- DESCRIPTION:
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;
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'));
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;
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'));
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)
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';
948 Else
949 MVName := GetSumTableMVName(l_table.Name);
950 dbObjectType := GetDbObjectType(MVName);
951 l_table.dbObjectType := dbObjectType;
952 l_table.MVName := MVName;
953 --Remember that 1 mv correespond to multiple summary tables (different periodicities)
954 If Not BSC_MO_HELPER_PKG.searchStringExists(arrMvs, numMVs, MVName) Then
955 If dbObjectType IS NOT NULL Then
956 --It is a MV or View
957 writeTableDescription (l_table);
958 End If;
959 arrMvs(numMVs) := MVName;
960 numMVs := numMVs + 1;
961 End If;
962 End If;
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
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);
976 bsc_mo_helper_pkg.writeTmp('Exception in WriteInputAndSystemTables, g_stack = '||g_stack, FND_LOG.LEVEL_UNEXPECTED, true);
977 bsc_mo_helper_pkg.TerminateWithError('BSC_DATATABLE_DOC_FAILED', 'WriteInputAndSystemTables');
978 raise;
979 End ;
980 --****************************************************************************
981 -- SetNeedCod0InOriTables
982 --
983 -- DESCRIPTION:
984 -- Mark the property NecesitaCod0 in the origin tables of the given
985 -- tables according to the the values already marked in the tables.
986 --****************************************************************************
987 PROCEDURE SetNeedCod0InOriTables(arrTablesCod0 IN DBMS_SQL.VARCHAR2_TABLE, numTablesCod0 IN NUMBER) IS
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
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
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);
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);
1025 --Because this function is just for precalculated kpis,
1026 --we know that there is not change of dissagregation
1027 l_key_index := BSC_MO_HELPER_PKG.findIndex(l_table_keys, keyColumn.keyName);
1028 if (l_key_index<>-1) then
1029 keyColumn.NeedsCode0 := l_table_keys(l_key_index).NeedsCode0;
1030 l_origin_table_keys(l_index2) := keyColumn;
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;
1040 EXIT WHEN l_origin_counter = l_originTable.last;
1041 l_origin_counter := l_originTable.next(l_origin_counter);
1042 END LOOP;
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;
1052 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.writeTableDescription :' ||g_error);
1053 raise;
1054 End ;
1055
1056
1057 --****************************************************************************
1058 -- GetLevelPKCol
1059 --
1060 -- DESCRIPTION:
1061 -- Returns level pk column name for the given indicator, configuration
1062 -- and dimension level index.
1063 --****************************************************************************
1064 Function GetLevelPKCol(Indicator IN NUMBER, Configuration IN NUMBER, DimLevelIndex IN NUMBER) return VARCHAR2 IS
1065 res VARCHAR2(100) := null;
1066 CURSOR c1 (p1 NUMBER, p2 NUMBER, p3 NUMBER) IS
1067 SELECT LEVEL_PK_COL
1068 FROM BSC_KPI_DIM_LEVELS_B
1069 WHERE INDICATOR = p1
1070 AND DIM_SET_ID = p2
1071 AND DIM_LEVEL_INDEX = p3;
1072 l_level_pk_col VARCHAR2(100);
1073 BEGIN
1074 OPEN c1(Indicator, Configuration, DimLevelIndex);
1075 FETCH c1 INTO l_level_pk_col;
1076 If c1%FOUND Then
1077 If l_level_pk_col IS NOT NULL Then
1078 res:= l_level_pk_col;
1079 End If;
1080 End If;
1081 Close c1;
1082 return res;
1083 EXCEPTION WHEN OTHERS THEN
1084 g_error := sqlerrm;
1085 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.GetLevelPKCol :' ||g_error);
1086 raise;
1087 End ;
1088 --****************************************************************************
1089 -- ReplaceStr : ReemplazarStr
1090 --
1091 -- DESCRIPTION:
1092 -- Replace a given sub-string by other sub_string in the given main string
1093 --
1094 -- PARAMETERS:
1095 -- p_string: main string
1096 -- p_replace_from: sub-string to be replaced
1097 -- p_replace_to: sub-string used to replace the previous one
1098 --****************************************************************************
1099 Function ReplaceStr(p_string IN VARCHAR2, p_replace_from IN VARCHAR2, p_replace_to IN VARCHAR2) RETURN VARCHAR2 IS
1100 l_temp_string VARCHAR2(100);
1101 l_result_string VARCHAR2(100);
1102 l_string VARCHAR2(100);
1103 l_position NUMBER;
1104 BEGIn
1105 If p_string IS NULL Then
1106 return p_string;
1107 END IF;
1108 l_string := p_string;
1109 l_result_string := null;
1110 l_position := InStr(l_string, p_replace_from);
1111 While l_position > 0 LOOP
1112 l_temp_string := substr(l_string, 1, l_position - 1);
1113 l_result_string := l_result_string || l_temp_string || p_replace_to;
1114 l_string := substr(l_string, l_position + Length(p_replace_from), length(l_string));
1115 l_position := InStr(l_string, p_replace_from);
1116 END Loop;
1117 l_result_string := l_result_string || l_string;
1118 return l_result_string;
1119 EXCEPTION WHEN OTHERS THEN
1120 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in ReplaceStr, param1= '||p_string||', param2='||p_replace_from||',param3='||p_replace_to);
1121 BSC_MO_HELPER_PKG.TerminateWithMsg(sqlerrm);
1122 BSC_MO_HELPER_PKG.writeTmp(sqlerrm, fnd_log.level_statement, true);
1123 raise;
1124 End ;
1125
1126 FUNCTION getIndicsForTable (l_tableName IN VARCHAR2, l_num OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1127
1128 -- get the indicator number from the table_name
1129 -- eg. bsc_s_3001_0_0_5 should give 3001
1130
1131 l_stmt varchar2(2000) :=
1132 'WITH main_indics AS
1133 (select distinct to_number(substr(table_name, instr(table_name, ''_'', 1, 2)+1,
1134 instr(table_name,''_'',1,3)-instr(table_name,''_'',1,2)-1)) indicator
1135 from
1136 ( select table_name from bsc_db_tables_rels
1137 where source_table_name not like ''BSC_S%''
1138 and table_name like ''BSC_S%''
1139 connect by source_table_name = prior table_name
1140 start with source_table_name = :l_table_name)
1141 where table_name like ''BSC_S%''
1142 )
1143 select indicator from
1144 (
1145 SELECT INDICATOR FROM main_indics
1146 union -- get dependant child indicators after validating filters are same
1147 select kpis.indicator
1148 from main_indics ind, bsc_kpis_vl kpis
1149 where ind.indicator = kpis.source_indicator
1150 and not exists
1151 (select 1 from bsc_kpi_dim_levels_b master, bsc_kpi_dim_levels_b child
1152 where master.indicator=kpis.source_indicator
1153 and child.indicator= ind.indicator
1154 and master.level_table_name = child.level_table_name
1155 and master.dim_level_index = child.dim_level_index
1156 and master.level_view_name <> child.level_view_name)
1157 )';
1158
1159 cv CurTyp;
1160 l_result VARCHAR2(32000) := null;
1161 l_indicator NUMBER;
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
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;
1179 EXIT WHEN CV%NOTFOUND;
1180 IF (l_result IS NOT NULL) THEN
1181 l_result := l_result||',';
1182 END IF;
1183 l_result := l_result || to_char(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;
1193 exception when others then
1194 l_error := sqlerrm;
1195 bsc_mo_helper_pkg.writeTmp(l_stmt);
1196 BSC_MO_HELPER_PKG.TerminateWithMsg('exception in getIndicsForTable : '||l_error) ;
1197 raise;
1198 END;
1199
1200 --****************************************************************************
1201 -- InicAllTables
1202 --
1203 -- DESCRIPTION:
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
1213 Indicator NUMBER;
1214 Configuration NUMBER;
1215 TableName VARCHAR2(100);
1216 Filter_Condition VARCHAR2(4000);
1217 arrFilterDims DBMS_SQL.VARCHAR2_TABLE;
1218 numFilterDims NUMBER;
1219 TotalSign VARCHAR2(100);
1220 Level_Comb VARCHAR2(100);
1221 KeyColumn VARCHAR2(100);
1222 i NUMBER;
1223 arrTablesCod0 DBMS_SQL.VARCHAR2_TABLE;
1224 numTablesCod0 NUMBER;
1225 Target_Flag NUMBER;
1226 l_temp NUMBER;
1227 l_temp2 NUMBER;
1228
1229 l_stmt varchar2(1000) :=
1230 'SELECT TABLE_NAME, TABLE_TYPE, PERIODICITY_ID, EDW_FLAG, TARGET_FLAG
1231 FROM BSC_DB_TABLES
1232 WHERE TABLE_TYPE <> 2';
1233
1234 l_table_name varchar2(100);
1235 l_table_type varchar2(100);
1236 l_periodicity_id number;
1237 l_edw_flag number;
1238 l_target_flag number;
1239
1240
1241 l_table_name_list dbms_sql.varchar2_table;
1242 l_table_type_list dbms_sql.varchar2_table;
1243 l_periodicity_id_list dbms_sql.number_table;
1244 l_edw_flag_list dbms_sql.number_table;
1245 l_target_flag_list dbms_sql.number_table;
1246
1247 cv Curtyp;
1248
1249 CURSOR c2(p1 VARCHAR2) IS
1250 SELECT COLUMN_TYPE, COLUMN_NAME, SOURCE
1251 FROM BSC_DB_TABLES_COLS
1252 WHERE TABLE_NAME = p1;
1253 cRow2 c2%ROWTYPE;
1254
1255 CURSOR c3(p1 VARCHAR2, p2 IN NUMBER) IS
1256 SELECT SOURCE_TABLE_NAME
1257 FROM BSC_DB_TABLES_RELS
1258 WHERE TABLE_NAME = p1
1259 AND RELATION_TYPE = p2
1260 ORDER BY SOURCE_TABLE_NAME;
1261 cRow3 c3%ROWTYPE;
1262
1263 CURSOR c4 (p1 IN VARCHAR2, p2 IN VARCHAR2) IS
1264 SELECT column_name
1265 FROM all_tab_columns
1266 WHERE table_name = p1
1267 AND owner = p2
1268 ORDER BY column_id;
1269 cRow4 c4%ROWTYPE;
1270
1271 /*CURSOR c5 IS
1272 SELECT INDICATOR, DIM_SET_ID, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION
1273 FROM BSC_KPI_DATA_TABLES_V T
1274 WHERE TABLE_NAME IS NOT NULL AND
1275 0 = (
1276 SELECT PROPERTY_VALUE
1277 FROM BSC_KPI_PROPERTIES P
1278 WHERE UPPER(PROPERTY_CODE) = 'DB_TRANSFORM'
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
1288 l_ind_list DBMS_SQL.NUMBER_TABLE;
1289 l_table_index NUMBER;
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
1299 l_stmt := l_stmt||
1300 ' and table_name in
1301 (
1302 SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
1303 CONNECT BY TABLE_NAME=prior SOURCE_TABLE_NAME
1304 START WITH table_name in
1305 (
1306 select table_name from bsc_db_tables dbtbl, bsc_tmp_opt_ui_kpis
1307 where dbtbl.table_name like ''BSC_S%''||indicator||''%''
1308 and indicator in
1309 (select indicator from bsc_tmp_opt_ui_kpis where process_id=:1)
1310 )
1311 UNION -- Get the S tables which are not sources
1312 SELECT TABLE_NAME FROM BSC_DB_TABLES dbtbl, bsc_tmp_opt_ui_kpis
1313 where dbtbl.table_name like ''BSC_S_%''||indicator||''%''
1314 and indicator in
1315 (select indicator from bsc_tmp_opt_ui_kpis where process_id=:1)
1316 )';
1317 end if;
1318 end if;
1319 l_stmt := l_stmt || ' ORDER BY TABLE_NAME';
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;
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;
1331 CLOSE cv;
1332
1333 FOR i IN 1..l_table_name_list.count LOOP
1334 l_table_name := l_table_name_list(i);
1335 l_table_type := l_table_type_list(i);
1336 l_periodicity_id := l_periodicity_id_list(i);
1337 l_edw_flag := l_edw_flag_list(i);
1338 l_target_flag := l_target_flag_list(i);
1339
1340 l_table := bsc_mo_helper_pkg.new_clsTable;
1341 Table_keys.delete;
1342 Table_data.delete;
1343 l_table.Name := l_TABLE_NAME;
1344 l_table.Type := l_TABLE_TYPE;
1345 l_table.Periodicity := l_PERIODICITY_ID;
1346 l_table.EDW_Flag :=l_EDW_FLAG;
1347 Target_Flag := l_TARGET_FLAG;
1348 If Target_Flag = 1 Then
1349 l_table.IsTargetTable := True;
1350 Else
1351 l_table.IsTargetTable := False;
1352 End If;
1353
1354 OPEN c2 (Upper(l_table.Name));
1355 LOOP
1356 FETCH c2 INTO cRow2;
1357 EXIT WHEN c2%NOTFOUND;
1358 If UPPER(cRow2.COLUMN_TYPE) = 'P' Then
1359 --Key column
1360 l_key := bsc_mo_helper_pkg.new_clsKeyField;
1361 l_key.keyName := cRow2.COLUMN_NAME;
1362 l_key.Origin := null;
1363 l_key.NeedsCode0 := False;
1364 l_key.CalculateCode0 := False;
1365 l_key.FilterViewName := null;
1366 Table_Keys(Table_Keys.count) := l_key;
1367 Else
1368 --Data column
1369 l_data := bsc_mo_helper_pkg.new_clsDataField;
1370 l_data.fieldName := cRow2.COLUMN_NAME;
1371 l_data.source := cRow2.source;
1372 l_data.Origin := null;
1373 l_data.aggFunction := null;
1374 Table_Data(Table_Data.count) := l_data;
1375 End If;
1376 END LOOP;
1377 Close c2;
1378
1379 --Source tables (Hard Relations)
1380 OPEN c3 (UPPER(l_table.Name), 0);
1381 LOOP
1382 FETCH c3 INTO cRow3;
1383 EXIT WHEN c3%NOTFOUND;
1384 TablaOri := cRow3.SOURCE_TABLE_NAME;
1385 IF (Tabla_originTable IS NOT NULL ) THEN
1386 l_table.originTable:= l_table.originTable||',';
1387 END IF;
1388 l_table.originTable:= l_table.originTable || TablaOri;
1389 END Loop;
1390 Close c3;
1391 --Source table (Soft Relations)
1392 OPEN c3(Upper(l_table.Name), 1);
1393 LOOP
1394 FETCH c3 INTO cRow3;
1395 EXIT WHEN c3%NOTFOUND;
1396 TablaOri := cRow3.SOURCE_TABLE_NAME;
1397 IF (l_table.originTable1 IS NOT NULL ) THEN
1398 l_table.originTable1 := l_table.originTable1||',';
1399 END IF;
1400 l_table.originTable1 := l_table.originTable1||TablaOri;
1401 END LOOP;
1402 Close C3;
1403 --I dont care about indicator and configuration
1404 l_table.Indicator := 0;
1405 l_table.Configuration := 0;
1406 --Add table to collection
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));
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 :=
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-
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;
1440
1441
1442 --Mark key columns which need cero code for precalculated indicators
1443 l_stmt := 'SELECT INDICATOR, DIM_SET_ID, LEVEL_COMB, TABLE_NAME, FILTER_CONDITION
1444 FROM BSC_KPI_DATA_TABLES_V T
1445 WHERE TABLE_NAME IS NOT NULL AND
1446 0 = (
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;
1456 end if;
1457 numTablesCod0 := 0;
1458 LOOP
1459 FETCH cv INTO Indicator, Configuration, level_comb, TableName, filter_condition;
1460 EXIT WHEN cv%NOTFOUND;
1461 Level_Comb := Trim(LEVEL_COMB);
1462 Filter_Condition := ReplaceStr(Filter_Condition, 'D', null);
1463 numFilterDims := BSC_MO_HELPER_PKG.DecomposeString(Filter_Condition, ',', arrFilterDims );
1464 For i IN 0..numFilterDims - 1 LOOP
1465 TotalSign := substr(Level_Comb, arrFilterDims(i) + 1, 1);
1466 If (TotalSign = '?' Or TotalSign = '1') Then
1467 --This dimension needs zero code
1468 If arrFilterDims(i) = 1 And BSC_MO_INDICATOR_PKG.IsIndicatorBalanceOrPnL(Indicator, false) Then
1469 --Drill 1 (Account) in a PL indicator does not need zero code
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
1479 arrTablesCod0(numTablesCod0) := TableName;
1480 numTablesCod0 := numTablesCod0 + 1;
1481 End If;
1482 End If;
1483 End If;
1484 End If;
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);
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);
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 --****************************************************************************
1506 -- InicAllIndicadores
1507 --
1508 -- DESCRIPTION:
1509 -- Initialize the collection gIndicadores with all indicators
1510 --****************************************************************************
1511 PROCEDURE InicAllIndicadores IS
1512
1513 l_stmt VARCHAR2(1000);
1514 Cod NUMBER;
1515 Name BSC_KPIS_VL.NAME%TYPE;
1516 l_indic_type NUMBER;
1517 l_config_type NUMBER;
1518 Per_Inter NUMBER;
1519 OptimizationMode NUMBER;
1520 Action_Flag NUMBER;
1521 Share_Flag NUMBER;
1522 Source_Indicator NUMBER;
1523 EDW_Flag NUMBER;
1524 CURSOR c1 IS
1525 SELECT DISTINCT INDICATOR, NAME, PROTOTYPE_FLAG,
1526 INDICATOR_TYPE, CONFIG_TYPE, PERIODICITY_ID, SHARE_FLAG,
1527 SOURCE_INDICATOR, EDW_FLAG FROM BSC_KPIS_VL
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;
1537 OPEN C1;
1538 LOOP
1539 FETCH c1 INTO cRow;
1540 EXIT WHEN c1%NOTFOUND;
1541 Cod := cRow.INDICATOR;
1542 Name := Trim(cRow.NAME);
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;
1552 Else
1553 Source_Indicator := cRow.SOURCE_INDICATOR;
1554 End If;
1555 EDW_Flag := cRow.EDW_FLAG;
1556
1557 BSC_MO_HELPER_PKG.AddIndicator(gDocIndicators, Cod, Name, l_indic_type,
1558 l_config_type, Per_Inter, OptimizationMode, Action_Flag,
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);
1568 bsc_mo_helper_pkg.writeTmp('Exception in InicAllIndicadores, g_stack = '||g_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1569 bsc_mo_helper_pkg.TerminateWithError('BSC_KPI_INIT_FAILED', 'InicAllIndicadores');
1570 raise;
1571 End ;
1572
1573 --****************************************************************************
1574 -- IndexCampoRelEnPadresTabladimTable : FindRelationIndex
1575 --
1576 -- DESCRIPTION:
1577 -- Returns the index of the relation field name in the collection
1578 -- of parents of the give dimension.
1579 --
1580 -- PARAMETERS:
1581 -- dimTable: dimension name
1582 -- CampoRel: relation field name
1583 --****************************************************************************
1584 Function FindRelationIndex(dimTable IN VARCHAR2, CampoRel IN VARCHAR2) RETURN NUMBER IS
1585 i NUMBER;
1586 l_temp NUMBER;
1587 l_return number := -1;
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;
1597 END IF;
1598
1599 i := l_parents_relcol.first;
1600 LOOP
1601 If UPPER(l_parents_relcol(i)) = UPPER(CampoRel) Then
1602 l_return := i;
1603 EXIT;
1604 End If;
1605
1606 EXIT WHEN i = l_parents_relcol.last;
1607 i := l_parents_relcol.next(i);
1608 END LOOP;
1609
1610 return l_return;
1611 EXCEPTION WHEN OTHERS THEN
1612 bsc_mo_helper_pkg.writeTmp(sqlerrm, fnd_log.level_statement, true);
1613 bsc_mo_helper_pkg.WriteTmp ('Exception FindRelationIndex, dimTable is '||dimTable||', CampoRel='||CampoRel, FND_LOG.LEVEL_UNEXPECTED, true);
1614 bsc_mo_helper_pkg.TerminateWithError('BSC_DIMTABLE_DESCR_FAILED', 'FindAuxillaryIndex');
1615 raise;
1616 End;
1617
1618
1619 --****************************************************************************
1620 -- FindAuxillaryIndex :IndexCampoAuxiliar
1621 --
1622 -- DESCRIPTION:
1623 -- Returns the index of the auxiliar field in the collection
1624 -- CamposAuxiliares of the given dimension table.
1625 -- Returns 0 if it is not found.
1626
1627 -- PARAMETERS:
1628 -- dimTable: dimension table name
1629 -- campoauxiliar: auxiliar field name
1630 --****************************************************************************
1631 Function FindAuxillaryIndex(dimTable IN VARCHAR2, CampoAuxiliar IN VARCHAR2) RETURN NUMBER IS
1632 i NUMBER;
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;
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
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
1655
1656 return i;
1657 END IF;
1658 EXIT WHEN i = l_AuxillaryFields.last;
1659 i := l_AuxillaryFields.next(i);
1660 END LOOP;
1661
1662 Return -1;
1663
1664 EXCEPTION WHEN OTHERS THEN
1665 bsc_mo_helper_pkg.writeTmp(sqlerrm, fnd_log.level_statement, true);
1666 bsc_mo_helper_pkg.WriteTmp ('Exception FindAuxillaryIndex, g_stack is '||g_stack, FND_LOG.LEVEL_UNEXPECTED,true);
1667 bsc_mo_helper_pkg.TerminateWithError('BSC_DIMTABLE_DESCR_FAILED', 'FindAuxillaryIndex');
1668 raise;
1669 End;
1670
1671 --****************************************************************************
1672 -- StrFix
1673 --
1674 -- DESCRIPTION:
1675 -- Returns a string wich is the same as the given string but adding spaces
1676 -- until complete the specified lenght.
1677 --
1678 -- PARAMETERS:
1679 -- p_string: string
1680 -- p_length: lenght
1681 -- p_center: center the string in the new string
1682 --****************************************************************************
1683 Function StrFix(p_string IN VARCHAR2, p_length IN NUMBER, p_center IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2 IS
1684 l_remaining_string VARCHAR2(1000);
1685 l_string_left VARCHAR2(1000);
1686 centrar NUMBER;
1687 l_return VARCHAR2(1000);
1688 l_space VARCHAR2(2000);
1689 BEGIN
1690 LOOP
1691 l_space := l_space || ' '; -- 100 spaces
1692 EXIT WHEN length(l_space) = 1000;
1693 END LOOP;
1694 If p_center Then
1695 If Length(p_string) >= p_length Then
1696 l_return := substr(p_string, 1, p_length);
1697 Else
1698 l_string_left := (p_length - Length(p_string)) / 2;
1699 l_remaining_string := p_length - Length(p_string) - l_string_left;
1700 l_return := substr(l_space, 1, l_string_left) || p_string ||substr(l_Space, 1, l_remaining_string);
1701 End If;
1702 Else
1703 If Length(p_string) >= p_length Then
1704 l_return := substr(p_string, 1, p_length);
1705 Else
1706 l_return := p_string || substr(l_Space, 1, p_length - Length(p_string));
1707 End If;
1708 End If;
1709 return l_return;
1710 End;
1711
1712 --****************************************************************************
1713 -- EscribirDescripcionTablaMaestraMN : WriteMNDescription
1714 --
1715 -- DESCRIPTION:
1716 -- Write the description of the given MN dimension table in the system tables
1717 -- description file.
1718 --
1719 -- PARAMETERS:
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;
1729 DESCLENGTH NUMBER := 60;
1730 Linea VARCHAR2(1000);
1731 iRel NUMBER;
1732 l_stmt VARCHAR2(1000);
1733 rsColumn_Name VARCHAR2(100);
1734 rsData_Type VARCHAR2(100);
1735 rsData_Length NUMBER;
1736 rsData_Precision NUMBER;
1737 rsData_Scale NUMBER;
1738 msg VARCHAR2(100);
1739 TableName VARCHAR2(100);
1740 CURSOR c1 (p1 VARCHAR2, p2 VARCHAR2) IS
1741 SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
1742 FROM ALL_TAB_COLUMNS
1743 WHERE TABLE_NAME = p1
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
1753 --Input table
1754 TableName := Tabla.InputTable;
1755 Else
1756 TableName := Tabla.TableRel;
1757 End If;
1758 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TABLE') ||
1759 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) ||
1760 TableName);
1761 --Input table name.
1762 If Not InputTableFlag Then
1763 --This information is only when the description is for the dimension table
1764 WriteLineTextFile('');
1765 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME') ||
1766 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) ||
1767 Tabla.InputTable);
1768 End If;
1769 --Headers
1770 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'FIELD'), FIELDLENGTH) ||
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;
1780 rsData_Type := cRow.DATA_TYPE;
1781 If cRow.DATA_LENGTH IS NULL Then
1782 rsData_Length := null;
1783 Else
1784 rsData_Length := cRow.DATA_LENGTH;
1785 If rsData_Length = 0 Then
1786 rsData_Length := null;
1787 End If;
1788 End If;
1789 If cRow.DATA_PRECISION IS NULL Then
1790 rsData_Precision := null;
1791 Else
1792 rsData_Precision := cRow.DATA_PRECISION;
1793 If rsData_Precision = 0 Then
1794 rsData_Precision := null;
1795 End If;
1796 End If;
1797 If cRow.DATA_SCALE IS NULL Then
1798 rsData_Scale := null;
1799 Else
1800 rsData_Scale := cRow.DATA_SCALE;
1801 If rsData_Scale = 0 Then
1802 rsData_Scale := null;
1803 End If;
1804 End If;
1805 Linea := StrFix(rsColumn_Name, FIELDLENGTH);
1806 Linea := Linea || StrFix(rsData_Type, TYPELENGTH);
1807 If rsData_Type = 'NUMBER' Then
1808 If rsData_Precision IS NULL Then
1809 rsData_Length := null;
1810 Linea := Linea || StrFix(' ', SIZELENGTH);
1811 Else
1812 If rsData_Scale IS NULL Then
1813 rsData_Length := rsData_Precision;
1814 Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
1815 Else
1816 rsData_Length := rsData_Precision; -- ',' || rsData_Scale
1817 Linea := Linea || StrFix(rsData_Length||','||rsData_Scale, SIZELENGTH);
1818 End If;
1819 End If;
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;
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;
1837 Linea := Linea || StrFix(msg, DESCLENGTH);
1838 End If;
1839 End If;
1840 WriteLineTextFile (Linea);
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;
1850 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.WriteMNDescription :' ||g_error);
1851 raise;
1852 End ;
1853 --****************************************************************************
1854 -- EscribirDescripcionTablaMaestra : WriteDimDescription
1855 -- DESCRIPTION:
1856 -- Write the description of the given dimension table in the system tables
1857 -- description file.
1858 -- PARAMETERS:
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;
1868 DESCLENGTH NUMBER := 60;
1869 Linea VARCHAR2(1000);
1870 iRel NUMBER;
1871 l_stmt VARCHAR2(1000);
1872 rsColumn_Name VARCHAR2(100);
1873 rsData_Type VARCHAR2(100);
1874 rsData_Length NUMBER;
1875 rsData_Precision NUMBER;
1876 rsData_Scale NUMBER;
1877 msg VARCHAR2(400);
1878 TableName VARCHAR2(100);
1879 l_value VARCHAR2(1000);
1880 cv CurTyp;
1881 l_msg VARCHAR2(1000);
1882 l_parents DBMS_SQL.varchar2_table;
1883 l_COLUMN_NAME VARCHAR2(30);
1884 l_DATA_TYPE VARCHAR2(30);
1885 l_DATA_LENGTH NUMBER;
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.
1895 --Also this procedure is never called with InputTableFlag=true for BIS dimensions
1896 --BIS DIMENSIONS: Check that the dimension view exists. BIS dimension views are created
1897 --only when it is used by a Kpi.
1898 If Tabla.Source = 'PMF' Then
1899 bsc_mo_helper_pkg.writeTmp('Source is PMF');
1900 l_bind_case := 1;
1901 --BIS dimension, the dimension table is a View
1902 l_stmt := 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
1903 FROM USER_TAB_COLUMNS
1904 WHERE TABLE_NAME = :1 ORDER BY COLUMN_ID';
1905 Else
1906 bsc_mo_helper_pkg.writeTmp('Source is BSC');
1907 --BSC dimension
1908 -- Bug 3830308 : Added owner clause here itself new GSCC validation isnt smart enough
1909 l_stmt := 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
1910 FROM ALL_TAB_COLUMNS WHERE OWNER = :1 AND ';
1911 If InputTableFlag Then
1912 l_bind_case := 2;
1913 l_stmt := l_stmt||' TABLE_NAME = :2';
1914 Else
1915 l_bind_Case := 3;
1916 l_stmt := l_stmt||' TABLE_NAME = :2';
1917 End If;
1918 l_stmt := l_stmt|| ' ORDER BY COLUMN_ID';
1919 End If;
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);
1929 FETCH cv INTO l_COLUMN_NAME, l_DATA_TYPE, l_DATA_LENGTH, l_DATA_PRECISION, l_DATA_SCALE;
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;
1939 CLOSE CV;
1940 --Table name
1941 If InputTableFlag Then
1942 --Input table
1943 TableName := Tabla.InputTable;
1944 Else
1945 If Tabla.Source = 'BSC' Then
1946 fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
1947 fnd_message.set_token('TABLE_NAME', Tabla.InputTable);
1948 msg := fnd_message.get;
1949 TableName := Tabla.Name || '(' || msg || ')';
1950 Else
1951 TableName := Tabla.Name;
1952 End If;
1953 End If;
1954 If InputTableFlag Then
1955 --Input table
1956 WriteLineTextFile('');
1957 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME') ||
1958 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) ||
1959 TableName);
1960 Else
1961 --BIS DIMENSIONS: Use View instead of Table in the title.
1962 If Tabla.Source = 'BSC' Then
1963 WriteLineTextFile( StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'TABLE') ||
1964 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) ||
1965 TableName);
1966 Else
1967 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'VIEW') ||
1968 BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'SYMBOL_COLON') || ' ', TABLELENGTH) ||
1969 TableName);
1970 End If;
1971 End If ;
1972 --Headers
1973 WriteLineTextFile (StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'FIELD'), FIELDLENGTH) ||
1974 StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'TYPE'), TYPELENGTH) ||
1975 StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'SIZE'), SIZELENGTH) ||
1976 StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'DESCRIPTION'), DESCLENGTH));
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
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;
1991 EXIT WHEN cv%NOTFOUND;
1992 rsColumn_Name := l_column_name;
1993 rsData_type := l_data_type;
1994 If (l_DATA_LENGTH IS NULL) Then
1995 rsData_Length := null;
1996 Else
1997 rsData_Length := l_DATA_LENGTH;
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;
2007 Else
2008 rsData_Precision := l_DATA_PRECISION;
2009 If rsData_Precision = 0 Then
2010 rsData_Precision := null;
2011 End If;
2012 End If;
2013 If l_DATA_SCALE IS NULL Then
2014 rsData_Scale := null;
2015 Else
2016 rsData_Scale := l_DATA_SCALE;
2017 If rsData_Scale = 0 Then
2018 rsData_Scale := null;
2019 End If;
2020 End If;
2021 Linea := StrFix(rsColumn_Name, FIELDLENGTH);
2022 Linea := Linea || StrFix(rsData_Type, TYPELENGTH);
2023 If rsData_Type = 'NUMBER' Then
2024 If rsData_Precision IS NULL Then
2025 rsData_Length := null;
2026 Linea := Linea || StrFix(' ', SIZELENGTH);
2027 Else
2028 If rsData_Scale IS NULL Then
2029 rsData_Length := rsData_Precision;
2030 Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
2031 Else
2032 rsData_Length := rsData_Precision; -- ',' || rsData_Scale
2033 Linea := Linea || StrFix(rsData_Length||','||rsData_Scale, SIZELENGTH);
2034 End If;
2035 End If;
2036 ELSE
2037 Linea := Linea || StrFix(rsData_Length, SIZELENGTH);
2038 End If;
2039 If UPPER(rsColumn_Name) = 'CODE' Then
2040 Linea := Linea || StrFix(BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'CODE'), DESCLENGTH);
2041 ElsIf UPPER(rsColumn_Name) = 'USER_CODE' Then
2042 l_value := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'USER_CODE');
2043 Linea := Linea || StrFix(l_value, DESCLENGTH);
2044 ElsIf UPPER(rsColumn_Name) = 'NAME' Then
2045 l_value := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_COMMON', 'NAME');
2046 Linea := Linea || StrFix(l_value, DESCLENGTH);
2047 ElsIf FindAuxillaryIndex(Tabla.Name, rsColumn_Name) <> -1 Then
2048 l_value := BSC_MO_HELPER_PKG.Get_LookUp_Value('BSC_UI_BACKEND', 'AUXILIARY_FIELD');
2049 Linea := Linea || StrFix(l_value, DESCLENGTH);
2050 Else
2051 If UPPER(substr(rsColumn_Name, -4)) = '_USR' Then
2052 iRel := FindRelationIndex(Tabla.Name, substr(rsColumn_Name, 1, Length(rsColumn_Name) - 4));
2053 If iRel <> -1 Then
2054 fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
2055 fnd_message.set_token('TABLE_NAME', l_Parents(iRel) ||'.USER_CODE');
2056 msg := fnd_message.get;
2057 Linea := Linea || StrFix(msg, DESCLENGTH);
2058 End If;
2059 Else
2060 iRel := FindRelationIndex(Tabla.Name, rsColumn_Name);
2061 If iRel <> -1 Then
2062 fnd_message.set_name('BSC', 'BSC_SEE_TABLE_NAME');
2063 fnd_message.set_token('TABLE_NAME', l_Parents(iRel) ||'.CODE');
2064 msg := fnd_message.get;
2065 Linea := Linea || StrFix(msg, DESCLENGTH);
2066 End If;
2067 End If;
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
2077 WriteLineTextFile('');
2078 WriteLineTextFile('');
2079 Else
2080 If Tabla.Source = 'PMF' Then
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);
2090 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception WriteDimDescription, Error is '||sqlerrm);
2091 bsc_mo_helper_pkg.WriteTmp ('Exception WriteDimDescription, g_stack is '||g_stack, FND_LOG.LEVEL_UNEXPECTED, true);
2092 bsc_mo_helper_pkg.TerminateWithError('BSC_DIMTABLE_DESCR_FAILED', 'WriteDimDescription');
2093 raise;
2094 End ;
2095 --****************************************************************************
2096 -- DocumentacionTablasMaestras: DocumentDimensionTables
2097 --
2098 -- DESCRIPTION:
2099 -- Generates the description of the dimension tables
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
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.
2119 If dim.Source = 'BSC' Then
2120 --Only for BSC Dimensions
2121 WriteDimDescription(dim, True);
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);
2131 END LOOP;
2132 END IF;
2133 WriteLineTextFile('');
2134 EXCEPTION WHEN OTHERS THEN
2135 BSC_MO_HELPER_PKG.TerminateWithError('BSC_DIMTABLE_DOC_FAILED', 'DocumentDimensionTables');
2136 raise;
2137 End ;
2138 --****************************************************************************
2139 -- Documentacion
2140 --
2141 -- DESCRIPTION:
2142 -- Generates documention
2143 -- pMode = 1 : write summary results for this run
2144 -- pMode = 2 : write entire system info
2145 --****************************************************************************
2146 PROCEDURE Documentation(pMode IN NUMBER DEFAULT 1) IS
2147 res NUMBER;
2148 msg VARCHAR2(1000);
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;
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;
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'));
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
2180 BSC_MO_HELPER_PKG.writeTmp('Calling WriteInputAndSystemTables', FND_LOG.LEVEL_STATEMENT);
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'));
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;
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;
2205 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in Documentation.Documentation :' ||g_error);
2206 BSC_MO_HELPER_PKG.TerminateWithError('BSC_DOC_PRODUCT_FAILED' , 'Documentation');
2207 raise;
2208 End;
2209 END BSC_MO_DOC_PKG;