1 Package Body BSC_METADATA_OPTIMIZER_PKG AS
2 /* $Header: BSCMOPTB.pls 120.25 2006/03/27 12:47:07 arsantha noship $ */
3
4
5 g_doc_file utl_file.file_type;
6 g_progressCounter NUMBER := 0;
7
8 PROCEDURE createTmpLogTables IS
9 l_stmt varchar2(1000);
10 l_table_name varchar2(30) := g_filtered_indics;
11 BEGIN
12 l_stmt := 'drop table '||l_table_name ;
13 begin
14 BSC_MO_HELPER_PKG.writeTmp('Going to drop '||l_table_name , FND_LOG.LEVEL_STATEMENT, false);
15 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.drop_table, l_table_name);
16 exception when others then
17 null;
18 --BSC_MO_HELPER_PKG.writeTmp(l_table_name ||' does not exist... ignoring error while trying to drop');
19 end;
20 l_stmt := 'create table '||l_table_name ||' TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName||' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause||
21 ' as
22 SELECT distinct k.indicator, k.dim_set_id, k.level_pk_col, k.level_view_name
23 FROM BSC_KPI_DIM_LEVELS_B K, BSC_SYS_DIM_LEVELS_B S, bsc_kpis_b kpi
24 WHERE
25 kpi.share_flag = 2
26 and kpi.indicator = k.indicator
27 and UPPER(K.LEVEL_TABLE_NAME) = UPPER(S.LEVEL_TABLE_NAME)
28 AND K.INDICATOR = kpi.indicator
29 --AND K.DIM_SET_ID = dimset.dim_set_id
30 AND UPPER(S.LEVEL_VIEW_NAME) <> UPPER(K.LEVEL_VIEW_NAME)
31 AND K.STATUS = 2 ';
32 BSC_MO_HELPER_PKG.writeTmp('Going to create '||l_table_name , FND_LOG.LEVEL_STATEMENT, false);
33 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_table, l_table_name);
34 l_stmt := 'create unique index '||l_table_name||'_u1 on '||l_table_name ||'(indicator, dim_set_id, level_pk_col)';
35 l_stmt := l_stmt ||' TABLESPACE '|| BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName||' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
36 BSC_MO_HELPER_PKG.writeTmp('Going to create '||l_table_name ||'_u1', FND_LOG.LEVEL_STATEMENT, false);
37 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.create_index, l_table_name ||'_u1');
38
39 END;
40
41 PROCEDURE writeTableCounts IS
42 l_num NUMBER;
43 l_problem_indics VARCHAR2(32000) := null;
44 cv CurTyp;
45 l_stmt varchar2(4000) := 'select distinct kpi.indicator, kpi.share_flag
46 from
47 bsc_kpis_b kpi,
48 bsc_tmp_opt_kpis_with_measures considerkpi
49 where kpi.indicator = considerkpi.indicator
50 and kpi.prototype_flag <> 2
51 -- get indicators without rows in bsc_db_tables
52 and not exists
53 (select 1 from bsc_db_tables where table_name like ''BSC_S_''||kpi.indicator||''%'')
54 -- skip shared but unfiltered indicators
55 and ( kpi.share_flag in (0,1)
56 or
57 (kpi.share_flag = 2
58 and exists
59 (SELECT 1
60 FROM bsc_tmp_opt_filtered_indics fil
61 where fil.indicator = kpi.indicator
62 )
63 )
64 )';
65 l_count number := 0;
66
67 BEGIN
68 return;
69 /*
70 bsc_mo_helper_pkg.writeTmp( 'Starting writeTableCounts, System time is '
71 ||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
72 SELECT count(1) INTO l_num
73 FROM bsc_db_tables
74 WHERE table_name like 'BSC_I_%';
75 bsc_mo_helper_pkg.writeTmp('No. of I tables in DB_tables = '||l_num, FND_LOG.LEVEL_STATEMENT, true);
76 SELECT count(1)INTO l_num
77 FROM bsc_db_tables
78 WHERE table_name like 'BSC_B_%';
79 bsc_mo_helper_pkg.writeTmp('No. of B tables in DB_tables = '||l_num, FND_LOG.LEVEL_STATEMENT, true);
80 SELECT count(1)INTO l_num
81 FROM bsc_db_tables
82 WHERE table_name like 'BSC_T_%';
83 bsc_mo_helper_pkg.writeTmp('No. of T tables in DB_tables = '||l_num, FND_LOG.LEVEL_STATEMENT, true);
84 SELECT count(1) INTO l_num
85 FROM bsc_db_tables
86 WHERE table_name like 'BSC_S_%';
87 bsc_mo_helper_pkg.writeTmp('No. of S tables in DB_tables = '||l_num, FND_LOG.LEVEL_STATEMENT, true);
88 bsc_mo_helper_pkg.writeTmp(bsc_mo_helper_pkg.get_time||' Indicators with no rows in BSC_DB_tables : ', FND_LOG.LEVEL_STATEMENT, true);
89 OPEN cv for l_stmt;
90 LOOP
91 FETCH cv INTO l_num;
92 EXIT WHEN cv%NOTFOUND;
93 l_count:= l_count + 1;
94 l_problem_indics := l_problem_indics||', '||l_num;
95 IF (l_count = 15) THEN
96 bsc_mo_helper_pkg.writeTmp(l_problem_indics, FND_LOG.LEVEL_STATEMENT, true);
97 l_count := 0;
98 l_problem_indics := null;
99 END IF;
100 END LOOP;
101 close cv;
102 if (l_count > 0 ) THEN
103 bsc_mo_helper_pkg.writeTmp(l_problem_indics, FND_LOG.LEVEL_STATEMENT, true);
104 end if;
105 bsc_mo_helper_pkg.writeTmp( 'Done writeTableCounts, System time is '||bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
106 */
107 END;
108
109 Function getUtlFileDir return VARCHAR2 IS
110 l_dir VARCHAR2(1000);
111 l_utl_dir VARCHAR2(100);
112 l_count NUMBER := 0;
113 l_log_begin NUMBER := 0;
114 l_log_end NUMBER := 0;
115 l_comma_pos NUMBER := 0;
116 stmt VARCHAR2(200);
117 cid NUMBER;
118 l_dummy NUMBER;
119
120 BEGIN
121 SELECT value INTO l_dir
122 FROM v$parameter param where upper(param.name) = 'UTL_FILE_DIR';
123 l_log_begin := INSTR(l_dir, '/log');
124
125 IF (l_log_begin = 0) THEN /* then get the first string */
126 l_utl_dir := substr(l_dir, 1, INSTR(l_dir, ',') - 1);
127 IF (l_utl_dir IS NOT NULL) THEN
128 return l_utl_dir;
129 ELSE
130 return l_dir;
131 END IF;
132 END IF;
133 l_log_end := INSTR(l_dir, ',', l_log_begin) - 1;
134 IF (l_log_end <= 0) THEN
135 l_log_end := length(l_dir);
136 END IF;
137 --have now determined the first occurrence of '/log' and the end pos
138 -- now to determine the start position of the log directory
139 l_dir := substr(l_dir, 0, l_log_end);
140 LOOP
141 l_comma_pos := INSTR(l_dir, ',', l_comma_pos+1);
142 IF (l_comma_pos <> 0) THEN
143 l_count := l_comma_pos + 1;
144 END IF;
145 EXIT WHEN l_comma_pos = 0;
146 END LOOP;
147 l_utl_dir := substr(l_dir, l_count+1, l_log_end);
148 RETURN l_utl_dir;
149 END;
150
151 /*---------------------------------------------------------------------
152 Write to the log file using utl_file. Write only IF the logging
153 flag is TRUE.
154 ---------------------------------------------------------------------*/
155
156 --Procedure writeLog(p_message IN VARCHAR2) IS
157 --BEGIN
158 -- fnd_file.put_line(FND_FILE.LOG, p_message);
159 --END;
160
161 /*Procedure writeOut(p_message IN VARCHAR2) IS
162 BEGIN
163 IF (g_OUT) THEN
164 fnd_file.put_line(FND_FILE.OUTPUT, p_message);
165 END IF;
166 END;
167 */
168
169 PROCEDURE InitLanguages IS
170 l_stmt VARCHAR2(1000);
171 l_lang_code varchar2(100);
172 l_nls_lang Varchar2(100);
173 cv CurTyp;
174
175 CURSOR c1 is
176 SELECT userenv('LANG') from dual;
177
178 CURSOR cLangs IS
179 SELECT DISTINCT LANGUAGE_CODE, NLS_LANGUAGE FROM FND_LANGUAGES
180 WHERE INSTALLED_FLAG IN ('I', 'B');
181
182 BEGIN
183 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
184 bsc_mo_helper_pkg.writeTmp('Inside InitLanguages', FND_LOG.LEVEL_PROCEDURE);
185 END IF;
186 gNumInstalled_Languages := 0;
187 OPEN c1;
188 FETCH c1 INTO gNLSLang;
189 close c1;
190 OPEN cLangs;
191 LOOP
192 Fetch cLangs INTO l_lang_code, l_nls_lang;
193 EXIT WHEN cLangs%NOTFOUND;
194 gInstalled_Languages(gNumInstalled_Languages) := l_lang_code;
195 gNumInstalled_Languages := gNumInstalled_Languages + 1;
196 If l_lang_code = gNLSLang Then
197 gLangCode := l_lang_code;
198 End If ;
199 END LOOP;
200 close cLangs;
201 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
202 bsc_mo_helper_pkg.writeTmp('Compl InitLanguages', FND_LOG.LEVEL_PROCEDURE);
203 bsc_mo_helper_pkg.writeTmp('gInstalled_Languages IS', FND_LOG.LEVEL_STATEMENT);
204 bsc_mo_helper_pkg.write_this(gInstalled_Languages, FND_LOG.LEVEL_STATEMENT);
205 END IF;
206 EXCEPTION WHEN others then
207 bsc_mo_helper_pkg.writeTmp('EXCEPTION in InitLanguages:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
208 RAISE;
209 END;
210
211 PROCEDURE InitReservedFunctions IS
212
213 l_stmt varchar2(1000);
214 l_reserved_word VARCHAR2(100);
215 cv CurTyp;
216 l_type number;
217 BEGIN
218 -- return if already initialized
219 IF (gNumReservedFunctions >0 and gNumReservedOperators >0) THEN
220 return;
221 END IF;
222 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
223 bsc_mo_helper_pkg.writeTmp('Inside InitReservedFunctions', FND_LOG.LEVEL_PROCEDURE);
224 END IF;
225 l_stmt := 'SELECT WORD FROM BSC_DB_RESERVED_WORDS WHERE WORD IS NOT NULL AND TYPE = 1';
226 gNumReservedFunctions := 0;
227
228 OPEN CV for l_stmt;
229 LOOP
230 FETCH cv INTO l_reserved_word;
231 EXIT WHEN cv%NOTFOUND;
232 gReservedFunctions(gNumReservedFunctions) := l_reserved_word;
233 gNumReservedFunctions := gNumReservedFunctions + 1;
234
235 END LOOP;
236 CLOSE CV;
237 gNumReservedOperators := 0;
238 l_stmt := 'SELECT WORD FROM BSC_DB_RESERVED_WORDS WHERE WORD IS NOT NULL AND TYPE = 2';
239 OPEN CV for l_stmt;
240 LOOP
241 FETCH cv INTO l_reserved_word;
242 EXIT WHEN cv%NOTFOUND;
243 --08/14/00 Bug#1377900 Exclude NOT, MOD from operators. They are reserved words
244 If Length(l_reserved_word) = 1 Then
245 gReservedOperators(gNumReservedOperators) := l_reserved_word;
246 gNumReservedOperators := gNumReservedOperators + 1;
247 END IF;
248
249 END LOOP;
250 CLOSE CV;
251 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
252 bsc_mo_helper_pkg.writeTmp('Compl InitReservedFunctions', FND_LOG.LEVEL_PROCEDURE);--commit;
253 bsc_mo_helper_pkg.writeTmp( 'gReservedOperators are', FND_LOG.LEVEL_STATEMENT);
254 bsc_mo_helper_pkg.write_this(gReservedOperators, FND_LOG.LEVEL_STATEMENT);
255 END IF;
256
257 EXCEPTION WHEN others then
258 bsc_mo_helper_pkg.writeTmp('EXCEPTION in InitReservedFunctions', FND_LOG.LEVEL_UNEXPECTED, TRUE);
259 RAISE;
260 END;
261
262
263 PROCEDURE logProgress(pStage IN VARCHAR2, pMessage IN VARCHAR2) IS
264
265 PRAGMA AUTONOMOUS_TRANSACTION;
266 BEGIN
267 g_progressCounter := g_progressCounter+1;
268
269 INSERT INTO BSC_TMP_BIG_IN_COND(session_id, VARIABLE_ID, VALUE_N, VALUE_V)
270 values (USERENV('SESSIONID'), -200, g_progressCounter,
271 pStage||' '||pMessage||' '||bsc_mo_helper_pkg.get_time);
272 commit;
273 EXCEPTION WHEN others then
274 bsc_mo_helper_pkg.writeTmp('EXCEPTION in logProgress'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
275 RAISE;
276 END;
277
278 function is_totally_shared_obj(p_objective in number) return boolean is
279 cursor cShared(p_pattern varchar2) is
280 select count(1) from bsc_kpis_vl
281 where indicator=p_objective
282 and share_flag=2
283 and not exists (select 1 from bsc_db_tables where instr(table_name, p_pattern)>0);
284 l_count number;
285 begin
286 open cShared ('BSC_S_'||p_objective||'_') ;
287 fetch cShared into l_count;
288 close cShared;
289 if l_count>0 then
290 return true;
291 else
292 return false;
293 end if;
294 end;
295
296 /***********************************************************************
297 DESCRIPTION:
298 This PROCEDURE is the body of the Metadata Optimizer process
299 Form this PROCEDURE are called all the sub-process
300 1. Initialize system
301 2. Dimension Tables
302 3. Indicators tables
303 4. Input Tables
304 5. Create tables in the database
305 6. Configure Loader
306 7. Documentation
307
308 AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
309 *************************************************************************/
310
311 PROCEDURE GenerateActualization IS
312 i NUMBER;
313 j NUMBER;
314 Indicator clsIndicator;
315 msg VARCHAR2(1000);
316 l_msg_metadata_proc_completion VARCHAR2(1000);
317 l_msg_path_file_creation VARCHAR2(1000);
318 l_msg_system_table_descrip VARCHAR2(1000);
319 l_msg_system_conf_tree VARCHAR2(1000);
320 l_msg_metadata_opt_result VARCHAR2(1000);
321
322 indic clsIndicator;
323
324 l_count NUMBER;
325 advSumLevel NUMBER;
326 l_stmt VARCHAR2(1000);
327
328 l_error VARCHAR2(1000);
329 l_num number;
330 l_aw_kpi_list DBMS_SQL.VARCHAR2_TABLE;
331
332 l_dummy1 varchar2(1000);
333 l_dummy2 varchar2(1000);
334 BEGIN
335 bsc_mo_helper_pkg.writeTmp('Inside GenerateActualization, system time is '||
336 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
337 bsc_mo_helper_pkg.writeTmp('Starting InitializePeriodicities, system time is '||
338 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
339 logProgress('INIT', 'Starting InitializePeriodicities');
340 BSC_MO_HELPER_PKG.InitializePeriodicities;
341 IF (g_retcode = 0) THEN
342 logProgress('INIT', 'Completed InitializePeriodicities');
343 ELSE
344 logProgress('INIT', 'InitializePeriodicities return code='||g_retcode||', so quitting');
345 return;
346 END IF;
347 bsc_mo_helper_pkg.writeTmp('Starting InitializeCalendars, system time is '||
348 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
349 logProgress('INIT', 'Starting InitializeCalendars');
350 BSC_MO_HELPER_PKG.InitializeCalendars;
351 logProgress('INIT', 'Completed InitializeCalendars');
352 IF (g_retcode = 0) THEN
353 logProgress('INIT', 'Completed InitializeCalendars');
354 ELSE
355 logProgress('INIT', 'InitializeCalendars return code='||g_retcode||', so quitting');
356 return;
357 END IF;
358 If gSYSTEM_STAGE = 2 Then
359 -- Create _LAST tables. This tables are to
360 -- get old input tables for the each kpi for
361 -- the result report.
362 logProgress('INIT', 'Starting CreateLastTables');
363 bsc_mo_helper_pkg.CreateLastTables;
364 logProgress('INIT', 'Completed CreateLastTables');
365 bsc_mo_helper_pkg.writeTmp('Done with createLastTables, system time is '||
366 bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
367 --Initialize array of old kpis and old base tables.
368 --This is done based on _LAST tables.
369 logProgress('INIT', 'Starting InitInfoOldSystem');
370 bsc_mo_helper_pkg.writeTmp('Starting InitInfoOldSystem, system time is '||
371 bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
372 bsc_mo_helper_pkg.InitInfoOldSystem;
373 bsc_mo_helper_pkg.writeTmp('Done InitInfoOldSystem, system time is '||
374 bsc_mo_helper_pkg.get_time, fnd_log.level_statement, true);
375 logProgress('INIT', 'Completed InitInfoOldSystem');
376 End If;
377 writeTableCounts;
378 bsc_mo_helper_pkg.writeTmp('Calling deletePreviousRunTables, system time is '||
379 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
380 logProgress('INIT', 'Starting deletePreviousRunTables');
381 bsc_mo_helper_pkg.deletePreviousRunTables;
382 logProgress('INIT', 'Completed deletePreviousRunTables');
383 bsc_mo_helper_pkg.writeTmp('Done with deletePreviousRunTables, system time is '||
384 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE);
385 writeTableCounts;
386 ------------------------------------------------------------
387 --Indicator Tables
388 --BSC-MV Note: Process indicator tables when there is structural changes
389 --or when there is summarization level change
390 bsc_mo_helper_pkg.writeTmp('Starting Indicator tables, system time is '||
391 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
392 logProgress('INDICATORS', 'Starting IndicatorTables');
393 BSC_MO_INDICATOR_PKG.IndicatorTables;
394 logProgress('INDICATORS', 'Completed IndicatorTables');
395 bsc_mo_helper_pkg.writeTmp('Done with Indicator tables, system time is '||
396 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
397 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
398 bsc_mo_helper_pkg.writeTmp( newline||newline, FND_LOG.LEVEL_STATEMENT);
399 bsc_mo_helper_pkg.writeTmp( 'gTables is ', FND_LOG.LEVEL_STATEMENT);
400 bsc_mo_helper_pkg.write_this(BSC_METADATA_OPTIMIZER_PKG.gTables, FND_LOG.LEVEL_STATEMENT);
401 bsc_mo_helper_pkg.writeTmp( newline||newline, FND_LOG.LEVEL_STATEMENT);
402 END IF;
403 ------------------------------------------------------------
404 --Input tables
405 --BSC-MV Note: We need to process input tables when there is structural changes
406 --or when there is summarization level change (upgrade case only)
407 bsc_mo_helper_pkg.writeTmp('Starting InputTables in GenerateActualization, system time is '||
408 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
409
410 logProgress('INPUT', 'Starting InputTables');
411 BSC_MO_INPUT_TABLE_PKG.InputTables;
412 logProgress('INPUT', 'Completed InputTables');
413 bsc_mo_helper_pkg.writeTmp('Done with InputTables in GenerateActualization, system time is '||
414 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
415 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
416 bsc_mo_helper_pkg.writeTmp( 'gTables is ', FND_LOG.LEVEL_STATEMENT, false);
417 bsc_mo_helper_pkg.write_this(BSC_METADATA_OPTIMIZER_PKG.gTables, FND_LOG.LEVEL_STATEMENT, false, true);
418 bsc_mo_helper_pkg.writeTmp( ' ');
419 END IF;
420 ------------------------------------------------------------
421 --Loader Configuration
422 bsc_mo_helper_pkg.writeTmp('Starting Loader Configuration, system time is '||
423 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
424 --BSC-MV Note: We need to configure loader when there is structural changes
425 --or when there is summarization level change (upgrade case only)
426
427 logProgress('CONFIG', 'Starting Loader Configuration');
428 BSC_MO_LOADER_CONFIG_PKG.ConfigureActualization;
429 logProgress('CONFIG', 'Completed Loader Configuration');
430 bsc_mo_helper_pkg.writeTmp('Done Loader Configuration... '||
431 'checking for incremental changes, system time is '||bsc_mo_helper_pkg.get_time,
432 FND_LOG.LEVEL_STATEMENT, true);
433
434 If gGAA_RUN_MODE <> 0 Then -- incremental
435 IF (gIndicators.count>0 ) THEN
436 i := gIndicators.first;
437 LOOP
438 Indic := gIndicators(i);
439 If Indic.Action_Flag = 4 Then
440 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
441 bsc_mo_helper_pkg.writeTmp( 'Has non structural change, calling ReConfigureUploadFieldsIndic', FND_LOG.LEVEL_STATEMENT);
442 END IF;
443 BSC_MO_LOADER_CONFIG_PKG.ReConfigureUploadFieldsIndic (Indic.Code);
444
445 --EDW Note: The materialize views are created taking rollup
446 --functions from BSC Metadata. So We need to recreate the
447 --Materialized views.
448 If Indic.EDW_Flag = 1 Then
449 BSC_MO_LOADER_CONFIG_PKG.ReCreateMaterializedViewsIndic (Indic.Code);
450 End If;
451 End If;
452 EXIT WHEN i = gIndicators.last;
453 i := gIndicators.next(i);
454 END LOOP;
455 END IF;
456 End If;
457 logProgress('CONFIG', 'Completed All of ConfigureActualization');
458 bsc_mo_helper_pkg.writeTmp('Done Loader Configuration, system time is '||
459 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
460 writeTableCounts;
461 logProgress('MISC', 'Starting drop_unused_columns');
462 IF BSC_METADATA_OPTIMIZER_PKG.gSYSTEM_STAGE = 2 THEN
463 bsc_mo_helper_pkg.writeTmp('Starting drop_unused_columns');
464 bsc_mo_helper_pkg.drop_unused_columns(null);
465 bsc_mo_helper_pkg.writeTmp('Done drop_unused_columns');
466 END IF;
467 -- Adjustments
468 --BSC-MV Note: Process indicators when there is structural changes
469 --or when there is summarization level change
470 bsc_mo_helper_pkg.writeTmp('Starting Corrections, system time is '||
471 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
472
473 logProgress('MISC', 'Starting Corrections');
474 BSC_MO_LOADER_CONFIG_PKG.Corrections;
475 logProgress('MISC', 'Completed Corrections');
476 bsc_mo_helper_pkg.writeTmp('Done with Corrections, system time is '||
477 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
478 writetableCounts;
479
480 --Create tables in the database
481 --BSC-MV Note: Process tables when there is structural changes
482 --or when there is summarization level change
483 bsc_mo_helper_pkg.writeTmp('Starting CreateAllTables,system time is '||
484 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
485 logProgress('DB', 'Starting CreateAllTables');
486 BSC_MO_DB_PKG.CreateAllTables;
487 logProgress('DB', 'Completed CreateAllTables');
488 bsc_mo_helper_pkg.writeTmp('Done with CreateAllTables, system time is '||
489 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
490 writetableCounts;
491 --Initialize year
492 --bug#3426728 Need to run reporting calendar before creating MVs
493 bsc_mo_helper_pkg.writeTmp('Initializing year', FND_LOG.LEVEL_STATEMENT, true);
494 logProgress('MISC', 'Starting InitializeYear');
495 BSC_MO_HELPER_PKG.InitializeYear;
496 logProgress('MISC', 'Completed InitializeYear');
497 writetableCounts;
498
499 --BSC-MV Note: In upgrade mode, we need to populate reporting calendar for first time
500 IF g_bsc_mv THEN
501 logProgress('MISC', 'Starting Load_Reporting_Calendar');
502 bsc_mo_helper_pkg.load_reporting_calendars;
503 logProgress('MISC', 'Completed Load_Reporting_Calendar');
504 END IF;
505 --BSC-MV Note: Drop reporting key global temporary tables
506 If g_BSC_MV Then
507 bsc_mo_helper_pkg.writeTmp('Drop reporting key global temporary tables', FND_LOG.LEVEL_STATEMENT, true);
508 logProgress('MISC', 'Drop reporting key global temporary tables');
509 BSC_BIA_WRAPPER.Drop_Rpt_Key_Table_VB(NULL);
510 BSC_MO_HELPER_PKG.CheckError('BSC_BIA_WRAPPER.Drop_Rpt_Key_Table_VB');
511 writetableCounts;
512 End If;
513 --BSC-MV Note: Create the MVs for the Kpis.
514 --Only for APPS systems.
515 --For each Kpi with structural change or with non-structural changes,
516 --implement the MV where it is appropiate.
517 IF g_BSC_MV THEN
518 -- Create DBI Dimension tables.
519 -- Added 03/14/2005 for AW project
520 IF NOT bsc_update_dim.create_dbi_dim_tables(l_error) THEN
521 logprogress('ERROR', 'Exception in bsc_update_dim.create_dbi_dim_tables');
522 BSC_MO_HELPER_PKG.TerminateWithMsg('EXCEPTION in bsc_update_dim.create_dbi_dim_tables : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
523 raise optimizer_exception;
524 END IF;
525 bsc_mo_helper_pkg.writeTmp('Create MVs for all Indicators', FND_LOG.LEVEL_STATEMENT, true);
526 logProgress('DB', 'Starting CreateMVs');
527 writetableCounts;
528 --For kpis with structural changes or non-structural changes
529 --Also create MVs when summarization level has changed
530 l_count := gIndicators.first;
531 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
532 bsc_mo_helper_pkg.writeTmp('Total indics = '||gIndicators.count, FND_LOG.LEVEL_PROCEDURE);
533 END IF;
534
535 LOOP
536 EXIT WHEN gIndicators.count = 0;
537 indic := gIndicators(l_count);
538 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
539 bsc_mo_helper_pkg.writeTmp('Processing Indic '||l_count);
540 END IF;
541 --totally shared objectives dont need MVs or AW summary objects
542 IF not is_totally_shared_obj(Indic.code) then
543 IF (Indic.impl_type=2) THEN
544 IF (Indic.Action_Flag = 3 OR Indic.Action_flag=4) THEN
545 bsc_mo_helper_pkg.writeTmp('AW implementation, so dont create MVs');
546 l_aw_kpi_list(l_aw_kpi_list.count+1) := Indic.code;
547 END IF;
548 ELSIF (Indic.Action_Flag = 3 Or Indic.Action_Flag = 4) Or (Indic.Action_Flag = 0 And g_Sum_Level_Change <> 0) Then
549 advSumLevel := to_number(g_Adv_Summarization_Level);
550 --BSC-MV Note: If the change is only the summarization level (example from 2 to 3)
551 --we pass pass Reset MV Levels in TRUE
552 bsc_mo_helper_pkg.writeTmp('Calling Create MV for Indicator '||Indic.code||', Time is '||
553 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, false);
554 logProgress('MV', 'Creating MV for '||Indic.code);
555 If Indic.Action_Flag = 0 And g_Sum_Level_Change = 2 Then
556 BSC_BIA_WRAPPER.Implement_Bsc_MV_VB(indic.code, advSumLevel, TRUE);
557 Else
558 BSC_BIA_WRAPPER.Implement_Bsc_MV_VB(indic.code, advSumLevel, FALSE);
559 End If;
560 logProgress('MISC', 'Compl creating MV for '||Indic.code||', checking error');
561 BSC_MO_HELPER_PKG.CheckError('BSC_BIA_WRAPPER.Implement_Bsc_MV_VB');
562 End If;
563 END IF; -- totally shared
564 EXIT WHEN l_count = gIndicators.last;
565 l_count := gIndicators.next(l_count);
566 END LOOP;
567 logProgress('DB', 'Completed CreateMVs');
568 IF (l_aw_kpi_list.count>0) THEN
569 logProgress('DB', 'Calling CreateAW');
570 bsc_mo_helper_pkg.writeTmp('Implementing AW objectives');
571 bsc_mo_helper_pkg.implement_aws(l_aw_kpi_list);
572 logProgress('DB', 'Completed Creating AWs');
573 END IF;
574 --Drop the tmp table created to store the CODE datatype
575 --Bug 3878968
576 if(BSC_OLAP_MAIN.b_table_col_type_created) then
577 BSC_OLAP_MAIN.drop_tmp_col_type_table;
578 end if;
579 END IF;
580 writetableCounts;
581 logProgress('MISC', 'Completed Reporting calendar calls');
582 --Clean un-used tables from the database
583 --This is just to make sure that the system is clean after user run Metadata Optmizer
584 --It will drop all tables that are not being used by any indidator.
585 --This situation should not happen, but due to some unkown issue in the past
586 --some tables could be there but no indicator is using it.
587
588 bsc_mo_helper_pkg.writeTmp('Starting CleanDatabase, system time is '||
589 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
590 logProgress('MISC', 'Starting CleanDatabase');
591 BSC_MO_HELPER_PKG.CleanDatabase;
592 logProgress('MISC', 'Completed CleanDatabase');
593 writeTableCounts;
594
595 --Generate documentation
596 bsc_mo_helper_pkg.writeTmp('Starting Doc, system time is '||bsc_mo_helper_pkg.get_time,
597 FND_LOG.LEVEL_STATEMENT, true);
598 logProgress('DOC', 'Starting Doc');
599 BSC_MO_DOC_PKG.Documentation(1);
600 logProgress('DOC', 'Completed Doc');
601 bsc_mo_helper_pkg.writeTmp('Done with Doc, system time is '||
602 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
603 writetableCounts;
604 bsc_mo_helper_pkg.writeTmp('Starting UpdateFlags, system time is '||
605 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
606 logProgress('MISC', 'Calling UpdateFlags');
607 --Update flags
608 BSC_MO_HELPER_PKG.UpdateFlags;
609 logProgress('MISC', 'Completed UpdateFlags');
610 bsc_mo_helper_pkg.writeTmp('Done with UpdateFlags, system time is '||
611 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
612 -- Call OUT API to populate display tables used by the UI
613 BEGIN
614 logProgress('MISC', 'Calling BSC_PMD_OPT_DOC_UTIL.GEN_TBL_RELS_DISPLAY');
615 execute immediate 'begin BSC_PMD_OPT_DOC_UTIL.GEN_TBL_RELS_DISPLAY; end;';
616 logProgress('MISC', 'Completed BSC_PMD_OPT_DOC_UTIL.GEN_TBL_RELS_DISPLAY');
617 EXCEPTION when others then
618 l_error := sqlerrm;
619 bsc_mo_helper_pkg.writeTmp('EXCEPTION in UI Call OUT BSC_PMD_OPT_DOC_UTIL.GEN_TBL_RELS_DISPLAY : '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
620 END;
621 bsc_mo_helper_pkg.writeTmp('Done with display tables API call, system time is '||
622 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
623 writeTableCounts;
624
625 fnd_stats.gather_table_stats(l_dummy1, l_dummy2, gBSCSchema,'BSC_KPI_DATA_TABLES');
626
627 EXCEPTION when others then
628 logprogress('ERROR', 'Exception in generateACtualization');
629 l_error := sqlerrm;
630 BSC_MO_HELPER_PKG.TerminateWithMsg('EXCEPTION in generateACtualization : '||l_error, FND_LOG.LEVEL_UNEXPECTED);
631 RAISE;
632 END;
633
634 PROCEDURE initMVFlags IS
635 BEGIN
636 --BSC-MV Note: Get advanced summarization level profile value
637 g_Sum_Level_Change := 0;
638 g_Adv_Summarization_Level := fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');
639 g_Current_Adv_Sum_Level := bsc_mo_helper_pkg.getInitColumn('ADV_SUM_LEVEL');
640
641 If (g_Current_Adv_Sum_Level IS NOT NULL AND g_Adv_Summarization_Level IS NULL) Then
642 --User cannot go back to old architecture.
643 bsc_mo_helper_pkg.writeTmp('Current MV levels ='||g_Current_Adv_Sum_Level ||', Target MV levels='||g_Adv_Summarization_Level, FND_LOG.LEVEL_STATEMENT,true);
644 bsc_mo_helper_pkg.writeTmp('Cannot go back to Summary table architecture', FND_LOG.LEVEL_STATEMENT, true); --force this OUT
645 BSC_MO_HELPER_PKG.TerminateWithError('BSC_SUM_LEVEL_INVALID');
646 raise optimizer_exception;
647 return;
648 End If;
649
650 --Bug 3305148: Even in gSYSTEM_STAGE=1 we need to set g_Sum_Level_Change = 1
651 --when it is the first time the system runs in MV architecture.
652
653 If (g_Current_Adv_Sum_Level IS NULL ) And (g_Adv_Summarization_Level IS NOT NULL) Then
654 --User wants to uptake the new architecture. Show warning messages.
655 IF (bsc_metadata_optimizer_pkg.gGAA_RUN_MODE=0) THEN
656 g_sum_level_change := 0; -- Entire system running for MVs first time, so not an upgrade
657 ELSE
658 g_Sum_Level_Change := 1; --Upgrade to new architecture (null to notnull)
659 END IF;
660 bsc_mo_helper_pkg.writeTmp('Upgrading to MV architecture, MV levels='|| g_Adv_Summarization_Level , FND_LOG.LEVEL_STATEMENT, true);
661 Else
662 If g_Current_Adv_Sum_Level <> g_Adv_Summarization_Level Then
663 g_Sum_Level_Change := 2;
664 Else
665 g_Sum_Level_Change := 0;
666 End If;
667 bsc_mo_helper_pkg.writeTmp('Current MV level = '||g_Current_Adv_Sum_Level||', Target MV level = '|| g_Adv_Summarization_Level , FND_LOG.LEVEL_STATEMENT, true); --force this OUT
668 End If;
669 If g_Adv_Summarization_Level IS NULL Then
670 bsc_mo_helper_pkg.writeTmp('Summary Table architecture', FND_LOG.LEVEL_STATEMENT, true);
671 g_BSC_MV := False;
672 Else
673 bsc_mo_helper_pkg.writeTmp('MV architecture', FND_LOG.LEVEL_STATEMENT, true);
674 g_BSC_MV := True;
675 End If;
676 END;
677
678 FUNCTION get_partition_clause return VARCHAR2 IS
679 l_stmt varchar2(1000);
680 BEGIN
681 if g_num_partitions <2 then
682 return null;
683 end if;
684 l_stmt := ' partition by list('||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') (';
685 for i in 1..g_num_partitions loop
686 if (i>1) then -- need comma
687 l_stmt := l_stmt ||',';
688 end if;
689 l_stmt := l_stmt ||' partition p_'||(i-1)||' values('||(i-1)||')';
690 end loop;
691 l_stmt := l_stmt ||')';
692 return l_stmt;
693 END;
694
695 PROCEDURE InitGlobalVars IS
696 BEGIN
697 bsc_mo_helper_pkg.writeTmp('Inside InitGlobalVars', FND_LOG.LEVEL_PROCEDURE, true);
698 logProgress('INIT', 'Starting InitGlobalVars');
699 InitLanguages;
700 InitReservedFunctions;
701 BSC_MO_HELPER_PKG.InitArrReservedWords;
702 gSYSTEM_STAGE := BSC_MO_HELPER_PKG.getInitColumn('SYSTEM_STAGE');
703
704 gStorageClause := BSC_MO_HELPER_PKG.getStorageClause;
705
706 BSC_MO_HELPER_PKG.InitTablespaceNames;
707
708 gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
709 gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
710 gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
711 g_num_partitions := bsc_dbgen_metadata_reader.get_max_partitions;
712 g_partition_clause := get_partition_clause;
713 -- Initialize DImension tables
714 logProgress('INIT', 'Starting InitializeMasterTables');
715 bsc_mo_helper_pkg.InitializeMasterTables;
716 logProgress('INIT', 'Starting CreateKPIDataTableTmp');
717 bsc_mo_helper_pkg.CreateKPIDataTableTmp;
718 logProgress('INIT', 'Starting CreateDBMeasureByDimSetTmp');
719 bsc_mo_helper_pkg.CreateDBMeasureByDimSetTmp;
720
721 logProgress('INIT', 'Starting CreateKPIDataTableTmp');
722 bsc_mo_helper_pkg.CreateKPIDataTableTmp;
723
724 logProgress('INIT', 'Starting CheckAllIndicsHaveSystem');
725 If gGAA_RUN_MODE = 0 Then -- entire system
726 BSC_MO_HELPER_PKG.CheckAllIndicsHaveSystem;
727 END IF;
728 --bsc_mo_helper_pkg.CheckError('BSC_SECURITY.CHECK_SYSTEM_LOCK');
729 logProgress('INIT', 'Starting CheckAllSharedIndicsSync');
730 BSC_MO_HELPER_PKG.CheckAllSharedIndicsSync;
731
732 --Check all EDW kpis have been fully mapped. Dimensions, Periodicities, Datasets are from EDW
733 --logProgress('INIT', 'Starting CheckAllEDWIndicsFullyMapped');
734 --BSC_MO_HELPER_PKG.CheckAllEDWIndicsFullyMapped;
735
736 --Initalize collection of indicators
737 logProgress('INIT', 'Starting initIndicators');
738 BSC_MO_HELPER_PKG.initIndicators;
739 logProgress('INIT', 'Completed initIndicators');
740
741 IF (g_retcode <> 0) THEN
742 bsc_mo_helper_pkg.writeTmp('initIndicators did not succeed :'||g_errbuf);
743 return;
744 END IF;
745
746 IF (BSC_MO_HELPER_PKG.validate_dimension_views=false) THEN
747 bsc_mo_helper_pkg.TerminateWithMsg('Validating Dimension Views did not succeed :'||sqlerrm);
748 return;
749 END IF;
750
751 If gIndicators.Count = 0 Then
752 If gSYSTEM_STAGE = 1 Then
753 --There is no configured indicators
754 BSC_MO_HELPER_PKG.TerminateWithError ('BSC_KPIS_MISSING', 'InitGlobalVars');
755 return;
756 Else
757 --There is no changed indicators
758 BSC_MO_HELPER_PKG.TerminateWithError ('BSC_NO_PENDING_CHANGES', 'Init');
759 fnd_message.set_name('BSC', 'BSC_NO_PENDING_CHANGES');
760 g_retcode := 1;
761 g_errbuf := fnd_message.get;
762 return;
763 End If;
764 End If;
765 -- called again to add measures for only the current objectives
766 logProgress('INIT', 'Starting CreateDBMeasureByDimSetTmp');
767 bsc_mo_helper_pkg.CreateDBMeasureByDimSetTmp;
768 logProgress('INIT', 'Starting initLOV');
769 BSC_MO_HELPER_PKG.InitLOV;
770 logProgress('INIT', 'Completed initLOV');
771 bsc_mo_helper_pkg.writeTmp('Completed InitGlobalVars', FND_LOG.LEVEL_PROCEDURE, true);
772 logProgress('INIT', 'Completed InitGlobalVars');
773 EXCEPTION when others then
774 bsc_mo_helper_pkg.writeTmp('EXCEPTION in InitGlobalVars:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
775 RAISE;
776 END;
777
778 PROCEDURE writeKPIList IS
779 cursor cList IS
780 SELECT KPIS.INDICATOR, KPIS.NAME, KPIS.PROTOTYPE_FLAG , decode(nvl(prop.property_value, 1), 1, 'Summary Tables/MVs', 'Analytical Workspace') impl_type
781 FROM BSC_KPIS_VL KPIS, BSC_KPI_PROPERTIES prop
782 WHERE kpis.INDICATOR IN
783 (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE process_id=g_processID)
784 AND kpis.indicator=prop.indicator(+)
785 and prop.property_code(+) = 'IMPLEMENTATION_TYPE'
786 order by indicator;
787 BEGIN
788 FOR i IN cList LOOP
789 bsc_mo_helper_pkg.writeTmp('Code = '||i.indicator||', Name ='||i.name||', prototype_flag='||i.prototype_flag||', Implementation='||i.impl_type, FND_LOG.LEVEL_PROCEDURE, true);
790 END LOOP;
791 END;
792
793 PROCEDURE Setup IS
794 l_error VARCHAR2(4000);
795 BEGIN
796
797 bsc_apps.init_bsc_apps;
798 g_bsc_apps_initialized := true;
799 bsc_apps.Init_Big_In_Cond_Table;
800 logProgress('INIT', 'Starting Setup');
801 g_dir := null;
802 g_dir:=fnd_profile.value('UTL_FILE_LOG');
803 IF g_dir is null THEN
804 g_dir:=getUtlFileDir;
805 END IF;
806 g_log_level := fnd_profile.value('AFLOG_LEVEL');
807 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
808 g_log := TRUE;
809 ELSE -- IF BIS_PMF_DEBUG is set, then enable logging automatically
810 g_log_level := FND_LOG.g_current_runtime_level;
811 END IF;
812 IF (g_dir is null OR fnd_global.CONC_REQUEST_ID = -1) THEN -- run manually
813 BSC_METADATA_OPTIMIZER_PKG.g_dir:=BSC_METADATA_OPTIMIZER_PKG.getUtlFileDir;
814 g_log := TRUE;
815 g_log_level := FND_LOG.LEVEL_STATEMENT;
816 END IF;
817 g_log_level := FND_LOG.LEVEL_STATEMENT;
818 fnd_file.put_names(g_filename||'.log', g_filename||'.OUT', g_dir);
819 g_fileOpened := TRUE;
820 --g_doc_file := utl_file.fOPEN(g_dir, 'METADATA.OUT' ,'w');
821 logProgress('INIT', 'Completed Setup');
822
823 bsc_mo_helper_pkg.writeTmp('---------------------------------------------------------'
824 ||newline, fnd_log.level_procedure, true);
825 bsc_mo_helper_pkg.writeTmp('Database Generator, Start Time is '
826 ||bsc_mo_helper_pkg.get_time, fnd_log.level_procedure, true);
827 bsc_mo_helper_pkg.writeTmp('---------------------------------------------------------'
828 ||newline, fnd_log.level_procedure, true);
829 bsc_mo_helper_pkg.writeTmp(newline);
830 bsc_mo_helper_pkg.writeTmp('Logging = '||bsc_mo_helper_pkg.boolean_decode(g_log)||
831 ', logging level='||g_log_level, FND_LOG.LEVEL_PROCEDURE, true);
832
833 EXCEPTION WHEN OTHERS THEN
834 l_error := sqlerrm;
835 bsc_mo_helper_pkg.writeTmp('EXCEPTION in Setup : '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
836 bsc_mo_helper_pkg.TerminatewithMsg('EXCEPTION in Seti[ : '||l_error);
837 RAISE;
838 end;
839
840 PROCEDURE Initialize IS
841 PRAGMA AUTONOMOUS_TRANSACTION;
842 l_error VARCHAR2(4000);
843 begin
844
845 IF (g_debug) THEN
846 bsc_message.init('Y');
847 ELSE
848 bsc_message.init('N');
849 END IF;
850 logProgress('INIT', 'Completed bsc_message.init');
851 logProgress('INIT', 'Starting initMVFlags');
852 initMVFlags;
853 logProgress('INIT', 'Completed initMVFlags');
854 IF (gGAA_RUN_MODE=0) THEN
855 bsc_mo_helper_pkg.writeTmp('Processing all objectives', FND_LOG.LEVEL_STATEMENT, true);
856 ELSIF (gGAA_RUN_MODE=1) THEN
857 bsc_mo_helper_pkg.writeTmp('Processing modified objectives', FND_LOG.LEVEL_STATEMENT, true);
858 writeKPIList;
859 ELSIF (gGAA_RUN_MODE=2) THEN
860 bsc_mo_helper_pkg.writeTmp('Processing selected objectives', FND_LOG.LEVEL_STATEMENT, true);
861 writeKPIList;
862 ELSE
863 bsc_mo_helper_pkg.writeTmp('Processing selected reports', FND_LOG.LEVEL_STATEMENT, true);
864 writeKPIList;
865 END IF;
866 bsc_mo_helper_pkg.writeTmp('---------------------------------------------------------'
867 ||newline, fnd_log.level_procedure, true);
868
869 bsc_mo_helper_pkg.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
870 bsc_mo_helper_pkg.writeTmp('Starting database generation process ', FND_LOG.LEVEL_STATEMENT, true);
871 bsc_mo_helper_pkg.writeTmp('Initializing System', FND_LOG.LEVEL_PROCEDURE, true);
872
873 InitGlobalVars;
874 IF (g_retcode <> 0) THEN
875 bsc_mo_helper_pkg.writeTmp('InitGlobalVars did not succeed :'||g_errbuf);
876 --autonomous txn, have to commit
877 commit;
878 return;
879 END IF;
880 -- new logging to check metadata corruption
881 createTmpLogTables;
882 IF (g_retcode <> 0) THEN
883 bsc_mo_helper_pkg.writeTmp('createTmpLogTables did not succeed :'||g_errbuf);
884 --autonomous txn, have to commit
885 commit;
886 return;
887 END IF;
888 bsc_mo_helper_pkg.writeTmp('Initialization completed.', FND_LOG.LEVEL_PROCEDURE, true);
889 logProgress('INIT', 'Completed Initialize');
890 --autonomous txn, have to commit
891 commit;
892 EXCEPTION when others then
893 l_error := sqlerrm;
894 bsc_mo_helper_pkg.writeTmp('EXCEPTION in Initialize : '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
895 bsc_mo_helper_pkg.TerminatewithMsg('EXCEPTION in Initialize : '||l_error);
896 commit;
897 RAISE;
898 END;
899
900 PROCEDURE wrapup IS
901 l_docRequestID NUMBER :=0;
902 x_return_status varchar2(1000);
903 x_msg_count number;
904 x_msg_data varchar2(1000);
905
906 BEGIN
907
908 bsc_mo_helper_pkg.writeTmp('Inside Wrapup', FND_LOG.LEVEL_PROCEDURE, true);
909 bsc_mo_helper_pkg.writeTmp('Starting Doc CP, system time is '||
910 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
911 --Launch doc as a separate process
912 l_docRequestID := FND_REQUEST.SUBMIT_REQUEST(
913 application=>'BSC',
914 program=>'BSC_METADATA_OPTIMIZER_DOC');
915
916 commit;
917 logProgress('DOC', 'Completed Submitting Doc process, request id = '||l_docRequestID);
918 bsc_mo_helper_pkg.writeTmp('Done with Doc, system time is '||
919 bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_STATEMENT, true);
920 bsc_mo_helper_pkg.writeTmp('Starting Doc CP, system time is '||
921 bsc_mo_helper_pkg.get_time||', request id = '||l_docRequestID, FND_LOG.LEVEL_STATEMENT, true);
922 bsc_mo_helper_pkg.writeTmp('Submitted Doc process, request id = '||l_docRequestID, FND_LOG.LEVEL_STATEMENT, true);
923 bsc_mo_helper_pkg.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
924 bsc_mo_helper_pkg.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
925 bsc_mo_helper_pkg.writeTmp(' ', FND_LOG.LEVEL_STATEMENT, true);
926 BEGIN
927 logProgress('MISC', 'Calling BSC_LOCKS_PUB.SYNCHRONIZE');
928 BSC_LOCKS_PUB.SYNCHRONIZE (-200, BSC_APPS.apps_user_id, x_return_status, x_msg_count, x_msg_data);
929 logProgress('MISC', 'Completed BSC_LOCKS_PUB.SYNCHRONIZE');
930 EXCEPTION when others then
931 g_retcode := 1;
932 bsc_mo_helper_pkg.writeTmp('WARNING: EXCEPTION in Call OUT BSC_LOCKS_PUB.SYNCHRONIZE : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
933 END;
934 bsc_mo_helper_pkg.writeTmp('Completed Wrapup', FND_LOG.LEVEL_PROCEDURE, true);
935 EXCEPTION when others then
936 logProgress('ALL', 'Exception in wrapup:'||sqlerrm);
937 bsc_mo_helper_pkg.writeTmp('Uh oh... EXCEPTION in Wrapup : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
938 RAISE;
939 END;
940
941 PROCEDURE run_metadata_optimizer_pvt IS
942 PRAGMA AUTONOMOUS_TRANSACTION;
943 BEGIN
944 IF (g_retcode = 0) THEN
945 GenerateActualization;
946 ELSE
947 bsc_mo_helper_pkg.writeTmp('Initalize did not succeed :'||g_errbuf);
948 END IF;
949 --bsc_security.Delete_Bsc_Session;
950 bsc_locks_pub.REMOVE_SYSTEM_LOCK;
951 IF (g_retcode = 0) THEN
952 logProgress('ALL', 'Completed GenerateActualization');
953 Wrapup;
954 logProgress('ALL', 'Completed Wrapup, retcode='||g_retcode);
955 ELSE
956 bsc_mo_helper_pkg.writeTmp('GenerateActualization did not succeed :retcode='||g_retcode||', error buffer='||g_errbuf, FND_LOG.level_exception, true);
957 logProgress('ALL', 'GenerateActualization did not succeed :retcode='||g_retcode||', error buffer='||g_errbuf);
958 END IF;
959
960 bsc_mo_helper_pkg.writeTmp('System time is '|| bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
961 bsc_mo_helper_pkg.writeTmp('Completed Generating Database, releasing file handle after the following message.... goodbye' , FND_LOG.LEVEL_PROCEDURE, true);
962 bsc_mo_helper_pkg.writeTmp(' ', FND_LOG.LEVEL_PROCEDURE, true);
963 bsc_mo_helper_pkg.writeTmp('---------------------------------------------------------', FND_LOG.LEVEL_PROCEDURE, true);
964 bsc_mo_helper_pkg.writeTmp('Generate Database process has completed successfully. Now, you must load data ', FND_LOG.LEVEL_PROCEDURE, true);
965 bsc_mo_helper_pkg.writeTmp('in the Interface Tables and run Data Loader to get information in the system.', FND_LOG.LEVEL_PROCEDURE, true);
966 logProgress('ALL', 'Completed Generate Database successfully');
967 fnd_file.release_names(g_filename||'.log', g_filename||'.OUT');
968 g_fileOpened := false;
969 commit;
970 END;
971
972 PROCEDURE Init_Locks IS
973 x_return_status varchar2(1000);
974 x_msg_count number;
975 x_msg_data varchar2(1000);
976 l_logging_flag boolean;
977 BEGIN
978 l_logging_flag := g_log;
979 g_log := false;
980 FND_MSG_PUB.Initialize;
981 BSC_LOCKS_PUB.GET_SYSTEM_LOCK (-200,BSC_APPS.apps_user_id, -1, x_return_status, x_msg_count, x_msg_data);
982 IF (gGAA_RUN_MODE = 0) THEN -- whole system
983 bsc_mo_helper_pkg.writeTmp('LOCKING going to lock entire system ', fnd_log.level_statement, true);
984 BSC_LOCKS_PUB.GET_SYSTEM_LOCK ('OBJECTIVE', 'W', sysdate, -200,
985 BSC_APPS.apps_user_id, -1, x_return_status, x_msg_count, x_msg_data);
986 IF (x_return_status<> FND_API.G_RET_STS_SUCCESS) THEN
987 bsc_mo_helper_pkg.writeTmp('EXCEPTION while trying to Lock in Initialize : '||
988 x_msg_data, FND_LOG.LEVEL_UNEXPECTED, true);
989 bsc_mo_helper_pkg.TerminateWithMsg(x_msg_data);
990 fnd_message.set_name('BSC', 'BSC_MUSERS_LOCKED_SYSTEM');
991 app_exception.raise_exception;
992 return;
993 END IF;
994 ELSE--selected or inter-related
995 FOR i IN (select indicator from bsc_tmp_opt_ui_kpis where process_id = g_processID) LOOP
996 logProgress('LOCK', 'Locking '||i.indicator);
997 bsc_mo_helper_pkg.writeTmp('LOCKING going to lock objective '||i.indicator, fnd_log.level_statement, true);
998 BSC_LOCKS_PUB.GET_SYSTEM_LOCK (
999 i.indicator, 'OBJECTIVE', 'W', sysdate, -200,BSC_APPS.apps_user_id, -1, x_return_status, x_msg_count, x_msg_data);
1000 IF (x_return_status<> FND_API.G_RET_STS_SUCCESS) THEN
1001 bsc_mo_helper_pkg.writeTmp('EXCEPTION while trying to Lock in Initialize : '||
1002 x_msg_data, FND_LOG.LEVEL_UNEXPECTED, true);
1003 bsc_mo_helper_pkg.TerminateWithMsg(x_msg_data);
1004 fnd_message.set_name('BSC', 'BSC_MUSERS_LOCKED_SYSTEM');
1005 app_exception.raise_exception;
1006 return;
1007 END IF;
1008 END LOOP;
1009 END IF;
1010 g_log := l_logging_flag;
1011 EXCEPTION WHEN OTHERS THEN
1012 g_retcode := 2;
1013 g_errbuf := x_msg_data;
1014 raise;
1015 END;
1016 PROCEDURE run_metadata_optimizer(
1017 Errbuf OUT NOCOPY Varchar2,
1018 Retcode OUT NOCOPY Varchar2,
1019 p_runMode IN NUMBER, -- 0 ALL, 1 INCREMENTAL, 2 SELECTED , (9 obsolete)
1020 p_processID IN NUMBER)
1021 IS
1022
1023 BEGIN
1024 gGAA_RUN_MODE := p_runMode;
1025 g_retcode := 0;
1026 g_processID := p_processID;
1027 delete bsc_tmp_big_in_cond where variable_id = -200;
1028 Setup;
1029 Init_Locks;
1030 Initialize;
1031 run_metadata_optimizer_pvt;
1032 retcode := g_retcode;
1033 errbuf := g_errbuf;
1034
1035 EXCEPTION
1036 when optimizer_exception then-- user defined exception, not a general failure
1037 logProgress('ALL', 'Failed to Generate Database, user_defined error:'||sqlerrm);
1038 retcode := g_retcode;
1039 errbuf := g_errbuf;
1040 --bsc_security.Delete_Bsc_Session;
1041 bsc_locks_pub.remove_system_lock;
1042 bsc_mo_helper_pkg.writeTmp('EXCEPTION in run_metadata_optimizer : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1043 fnd_file.release_names(g_filename||'.log', g_filename||'.OUT');
1044 when others then -- general failure, dump the stack
1045 logProgress('ALL', 'Failed to Generate Database, general failure, dumping stack');
1046 retcode := g_retcode;
1047 errbuf := g_errbuf;
1048 --bsc_security.Delete_Bsc_Session;
1049 bsc_locks_pub.remove_system_lock;
1050 bsc_mo_helper_pkg.writeTmp('EXCEPTION in run_metadata_optimizer : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1051 bsc_mo_helper_pkg.writeTmp('gTables is ', FND_LOG.LEVEL_UNEXPECTED, true);
1052 bsc_mo_helper_pkg.write_this(gTables, FND_LOG.LEVEL_ERROR, true);
1053 bsc_mo_helper_pkg.dump_stack;
1054 fnd_file.release_names(g_filename||'.log', g_filename||'.OUT');
1055 END;
1056 PROCEDURE Documentation(
1057 Errbuf OUT NOCOPY Varchar2,
1058 Retcode OUT NOCOPY Varchar2) IS
1059 x_return_status varchar2(1000);
1060 x_msg_count number;
1061 x_msg_data varchar2(1000);
1062 BEGIN
1063 gAppsSchema := BSC_MO_HELPER_PKG.getAppsSchema;
1064 gBSCSchema := BSC_MO_HELPER_PKG.getBSCSchema;
1065 gApplsysSchema := BSC_MO_HELPER_PKG.getApplsysSchema;
1066 -- modified call to include user_id so that session management module can recognise the
1067 -- session lock #bug 3593694
1068 --bsc_security.Check_System_Lock(-201,NULL,BSC_APPS.apps_user_id);
1069 bsc_mo_helper_pkg.writeTmp('LOCKING going to lock for documentation ', fnd_log.level_statement, true);
1070 BSC_LOCKS_PUB.GET_SYSTEM_LOCK(-201,BSC_APPS.apps_user_id, -1, x_return_status, x_msg_count, x_msg_data);
1071 IF (x_return_status<> FND_API.G_RET_STS_SUCCESS) THEN
1072 bsc_mo_helper_pkg.writeTmp('EXCEPTION while trying to Lock in Initialize : '||x_msg_data, FND_LOG.LEVEL_UNEXPECTED, true);
1073 bsc_mo_helper_pkg.TerminateWithMsg(x_msg_data);
1074 fnd_message.set_name('BSC', 'BSC_MUSERS_LOCKED_SYSTEM');
1075 app_exception.raise_exception;
1076 return;
1077 END IF;
1078
1079 BSC_MO_DOC_PKG.Documentation(2);
1080 -- bsc_security.Delete_Bsc_Session;
1081 bsc_locks_pub.remove_system_lock;
1082 EXCEPTION WHEN OTHERS THEN
1083 --bsc_security.Delete_Bsc_Session;
1084 bsc_locks_pub.remove_system_lock;
1085 bsc_mo_helper_pkg.writeTmp('EXCEPTION in Documentation : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1086 raise;
1087 END;
1088 END BSC_METADATA_OPTIMIZER_PKG;
[Home] [Help]