[Home] [Help]
PACKAGE BODY: APPS.FEM_REFRESH_ENG_PKG
Source
1 PACKAGE BODY FEM_REFRESH_ENG_PKG AS
2 -- $Header: fem_refresh_eng.plb 120.9 2007/06/26 16:54:26 rflippo ship $
3
4 /***************************************************************************
5 Copyright (c) 2003 Oracle Corporation
6 Redwood Shores, CA, USA
7 All rights reserved.
8 ***************************************************************************
9 FILENAME
10 fem_refresh_eng.plb
11
12 DESCRIPTION
13 See fem_refresh_eng.pls for details
14
15 HISTORY
16 Rob Flippo 02-May-2005 Created
17 Rob Flippo 08-JUN-2005 Modified to continue if error encountered with
18 a load of a seeded data file
19 Rob Flippo 09-JUN-2005 Modify reset_profile_options for dataset
20 profiles
21 Rob Flippo 22-JUL-2005 Modify tables cursor to only retrieve tables
22 that exist in the db
23 Rob Flippo 19-AUG-2005 Bug#4547880 Modify fem_rfsh_procedures cursor
24 to order by sub_phase asc - this ensures that
25 the proc to create the default cal period
26 happens first
27 Rob Flippo 16-MAY-2006 Bug#5223789 Add hard-coded call
28 to delete KFF registration info for
29 composite dimensions
30 Rob Flippo 14-SEP-2006 Bug#5520316 Refresh engine should continue on
31 even if get exception to KFF delete; Also
32 modified the clean_tables procedure so that
33 if get truncate_table failure, the refresh
34 continues
35 Rob Flippo 22-SEP-2006 Bug#5549010 Modified load_Seed_data procedure
36 so that it only runs the TL files in the base
37 language;
38 Rob Flippo 25-JAN-2007 Bug#5837043 Running the TL files only in the
39 base language is not sufficient, since non-trans
40 ldt files will not be populated in non-US
41 directories. To solve this will run US files
42 first to ensure that the data gets loaded, and
43 then when base lang <> 'US' will re-run the files
44 in the Base language files
45 **************************************************************************/
46
47 -------------------------------
48 -- Declare package variables --
49 -------------------------------
50 f_set_status BOOLEAN;
51
52 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
53 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
54 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
55 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
56 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
57 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
58
59 v_log_level NUMBER;
60
61 gv_prg_msg VARCHAR2(2000);
62 gv_callstack VARCHAR2(2000);
63
64
65 -- Private Internal Procedures
66 PROCEDURE Report_errors;
67
68
69 PROCEDURE Register_process_execution (p_object_id IN NUMBER
70 ,p_obj_def_id IN NUMBER
71 ,p_execution_status IN VARCHAR);
72
73
74
75 PROCEDURE Eng_Master_Prep (x_appltop OUT NOCOPY VARCHAR2
76 ,x_release OUT NOCOPY VARCHAR2
77 ,x_execution_status OUT NOCOPY VARCHAR2);
78
79 PROCEDURE Clean_tables (x_execution_status OUT NOCOPY VARCHAR2);
80
81 PROCEDURE Refresh_fndload (errbuf OUT NOCOPY VARCHAR2
82 ,retcode OUT NOCOPY VARCHAR2
83 ,x_sub_request_id OUT NOCOPY NUMBER
84 ,p_appltop IN VARCHAR2
85 ,p_release IN VARCHAR2
86 ,p_ldtpath IN VARCHAR2
87 ,p_lctpath IN VARCHAR2);
88
89 PROCEDURE Refresh_procedure (x_procedure_status OUT NOCOPY VARCHAR2
90 ,p_procedure_call IN VARCHAR2);
91
92
93 PROCEDURE Load_seed_data (p_appltop IN VARCHAR2
94 ,p_release IN VARCHAR2
95 ,x_execution_status OUT NOCOPY VARCHAR2);
96
97 PROCEDURE Reset_profile_options (x_execution_status IN OUT NOCOPY VARCHAR2);
98
99 PROCEDURE Refresh_completion;
100
101 -----------------------------------------------------------------------------
102 -- Package bodies for functions/procedures
103 -----------------------------------------------------------------------------
104
105 /*===========================================================================+
106 | PROCEDURE
107 | Engine Master Preparation
108 |
109 | DESCRIPTION
110 | Validates the FEM_TOP and splits it into component pieces so the
111 | APPL_TOP portion can be re-used to identify homes of other products
112 | SCOPE - PRIVATE
113 |
114 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
115 |
116 | ARGUMENTS : IN:
117 | OUT:
118 |
119 |
120 | NOTES
121 |
122 |
123 | MODIFICATION HISTORY
124 | Rob Flippo 03-MAY-05 Created
125 |
126 +===========================================================================*/
127 PROCEDURE Eng_Master_Prep (x_appltop OUT NOCOPY VARCHAR2
128 ,x_release OUT NOCOPY VARCHAR2
129 ,x_execution_status OUT NOCOPY VARCHAR2) IS
130
131 v_concurrent_status BOOLEAN;
132 v_ldtpath VARCHAR2(1000);
133 v_lctpath VARCHAR2(1000);
134
135 v_fem_start NUMBER :=0; -- point where the "fem" portion of the $FEM_TOP string begins
136 v_temp_fem_start NUMBER :=0; -- temporary variable for storing the starting position
137 -- of "/fem/" in the $FEM_TOP string
138
139 v_release_end NUMBER :=0; -- point where the release component ends in the $FEM_TOP string
140 v_release_len NUMBER :=0; -- number of chars in the release component
141 v_sub_request_id NUMBER;
142
143 v_parent_request_id NUMBER;
144
145 -- Exceptions
146 e_unable_parse_femtop EXCEPTION;
147 e_fndload_error EXCEPTION;
148 e_concurrent_manager EXCEPTION;
149
150
151 -- output variables for the wait_for_request function
152 f_req_wait BOOLEAN; -- return of the wait_for_request function
153 v_req_phase VARCHAR2(100);
154 v_req_status VARCHAR2(100);
155 v_dev_phase VARCHAR2(100);
156 v_dev_status VARCHAR2(100);
157 v_req_message VARCHAR2(100);
158
159 v_femtop VARCHAR2(1000);
160
161 errbuf VARCHAR2(4000);
162 retcode VARCHAR2(4000);
163
164 CURSOR c_sub_request IS
165 SELECT request_id
166 FROM fnd_concurrent_requests R,
167 fnd_concurrent_programs P
168 WHERE parent_request_id = v_parent_request_id
169 AND R.concurrent_program_id = P.concurrent_program_id
170 AND P.concurrent_program_name = c_FNDLOAD
171 ORDER BY request_id;
172
173
174
175 BEGIN
176
177 FEM_ENGINES_PKG.Tech_Message
178 (p_severity => c_log_level_1,
179 p_module => c_block||'.'||'Engine_master_prep.v_femtop',
180 p_msg_text => v_femtop);
181
182 v_parent_request_id := fnd_global.conc_request_id;
183 v_femtop := FND_PROFILE.Value_Specific('FEM_FEMTOP');
184
185 -- Parse the FEM_TOP to find the APPL_TOP and RELEASE components
186 -- we will look for the last occurrence (up to a max of 10 occurrences)
187 -- of '/fem/'
188 FOR i IN 1 .. 10 LOOP
189
190 v_temp_fem_start := instr(v_femtop,'/fem/',1,i);
191 IF v_temp_fem_start > 0 THEN
192 v_fem_start := v_temp_fem_start;
193 ELSE
194 EXIT;
195 END IF;
196 END LOOP;
197
198 IF v_fem_start = 0 THEN
199 RAISE e_unable_parse_femtop;
200 END IF;
201
202 -- Identify the $APPL_TOP as everyting preceeding the '/fem/' string
203 x_appltop := substr(v_femtop,1,v_fem_start);
204
205 -- Identify the release component of $FEM_TOP
206 -- we do this by first looking for the next '/' that is after '/fem/'
207 -- then we substr on v_femtop, starting at the end of '/fem',
208 -- and continuing for the length of the release string
209 v_release_end := instr(v_femtop,'/',v_fem_start+4,1);
210 v_release_len := v_release_end - v_fem_start+4;
211 x_release := substr(v_femtop,v_fem_start+4,v_release_len);
212 v_lctpath := x_appltop||'fem'||x_release||c_test_lct;
213 v_ldtpath := x_appltop||'fem'||x_release||c_test_ldt;
214
215 FEM_ENGINES_PKG.Tech_Message
216 (p_severity => c_log_level_1,
217 p_module => c_block||'.'||'Engine_master_prep.v_fem_start',
218 p_msg_text => v_fem_start);
219
220 FEM_ENGINES_PKG.Tech_Message
221 (p_severity => c_log_level_1,
222 p_module => c_block||'.'||'Engine_master_prep.x_appltop',
223 p_msg_text => x_appltop);
224
225 FEM_ENGINES_PKG.Tech_Message
226 (p_severity => c_log_level_1,
227 p_module => c_block||'.'||'Engine_master_prep.x_release',
228 p_msg_text => x_release);
229
230 FEM_ENGINES_PKG.Tech_Message
231 (p_severity => c_log_level_1,
232 p_module => c_block||'.'||'Engine_master_prep.lctpath',
233 p_msg_text => v_lctpath);
234
235 FEM_ENGINES_PKG.Tech_Message
236 (p_severity => c_log_level_1,
237 p_module => c_block||'.'||'Engine_master_prep.ldtpath',
238 p_msg_text => v_ldtpath);
239
240 -- Test the $FEM_TOP by running FNDLOAD for fem_dim.ldt
241 Refresh_fndload (errbuf
242 ,retcode
243 ,v_sub_request_id
244 ,x_appltop
245 ,x_release
246 ,v_ldtpath
247 ,v_lctpath);
248
249
250
251 FEM_ENGINES_PKG.Tech_Message
252 (p_severity => c_log_level_1,
253 p_module => c_block||'.'||'Engine_master_prep.v_sub_request_id',
254 p_msg_text => v_sub_request_id);
255
256 IF (v_sub_request_id = 0)
257 THEN
258 RAISE e_fndload_error;
259 ELSE
260 COMMIT;
261 END IF;
262
263 -- check status of the submitted request and exit when finished
264 LOOP
265 f_req_wait := FND_CONCURRENT.WAIT_FOR_REQUEST
266 (REQUEST_ID => v_sub_request_id
267 ,INTERVAL =>5
268 ,MAX_WAIT => 600
269 ,PHASE => v_req_phase
270 ,STATUS => v_req_status
271 ,DEV_PHASE => v_dev_phase
272 ,DEV_STATUS => v_dev_status
273 ,MESSAGE => v_req_message);
274
275 CASE v_dev_phase
276 WHEN 'COMPLETE' THEN EXIT;
277 WHEN 'INACTIVE' THEN EXIT;
278 ELSE NULL;
279 END CASE;
280 END LOOP;
281
282 FEM_ENGINES_PKG.Tech_Message
283 (p_severity => c_log_level_1,
284 p_module => c_block||'.'||'Engine_master_prep.v_dev_phase',
285 p_msg_text => v_dev_phase);
286
287
288 IF v_dev_phase NOT IN ('COMPLETE') THEN
289 RAISE e_concurrent_manager;
290 ELSIF v_dev_phase = 'COMPLETE' AND v_dev_status NOT IN ('NORMAL') THEN
291 RAISE e_fndload_error;
292 ELSIF v_dev_phase = 'COMPLETE' AND v_dev_status = 'NORMAL' THEN
293 x_execution_status := 'SUCCESS';
294 END IF;
295
296 EXCEPTION
297 WHEN e_unable_parse_femtop THEN
298 FEM_ENGINES_PKG.USER_MESSAGE
299 (P_APP_NAME => c_fem
300 ,P_MSG_NAME => 'FEM_RFSH_INVALID_FEM_TOP'
301 ,P_TOKEN1 => 'PATH'
302 ,P_VALUE1 => v_femtop);
303
304 FEM_ENGINES_PKG.Tech_Message
305 (p_severity => c_log_level_1,
306 p_module => c_block||'.'||'Engine_master_prep',
307 p_msg_text => 'Unable to find /fem/ in the FEM_FEMTOP profile option');
308
309 x_execution_status := 'ERROR_RERUN';
310
311 WHEN e_fndload_error THEN
312 FEM_ENGINES_PKG.USER_MESSAGE
313 (P_APP_NAME => c_fem
314 ,P_MSG_NAME => 'FEM_RFSH_FNDLOAD_ERROR'
315 ,P_TOKEN1 => 'LDTFILE'
316 ,P_VALUE1 => v_ldtpath
317 ,P_TOKEN2 => 'LCTFILE'
318 ,P_VALUE2 => v_lctpath);
319
320 FEM_ENGINES_PKG.Tech_Message
321 (p_severity => c_log_level_1,
322 p_module => c_block||'.'||'Engine_master_prep',
323 p_msg_text => 'Unable to run the FEM_RFSH_FNDLOAD concurrent program');
324
325 x_execution_status := 'ERROR_RERUN';
326
327 WHEN e_concurrent_manager THEN
328 FEM_ENGINES_PKG.USER_MESSAGE
329 (P_APP_NAME => c_fem
330 ,P_MSG_NAME => 'FEM_RFSH_CONCURRENT_ERROR');
331
332 FEM_ENGINES_PKG.Tech_Message
333 (p_severity => c_log_level_1,
334 p_module => c_block||'.'||'Engine_master_prep',
335 p_msg_text => 'Concurrent Manager is unable to process the refresh');
336
337 x_execution_status := 'ERROR_RERUN';
338
339 END Eng_Master_Prep;
340
341 /*===========================================================================+
342 | PROCEDURE
343 | Load_seed_data
344 |
345 | DESCRIPTION
346 | Loads all seeded data files (both ldt and sql) into the database
347 | and executes all procedures required for seeded data population.
348 | SCOPE - PRIVATE
349 |
350 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
351 |
352 | ARGUMENTS : IN:
353 | OUT:
354 |
355 |
356 | NOTES
357 | SQL files are not supported at this time, as there are no known
358 | cases (i.e. all sql files have been converted to a procedure call.
359 |
360 | MODIFICATION HISTORY
361 | Rob Flippo 05-MAY-05 Created
362 | Rob Flippo 08-JUN-05 Modified to continue if error encountered with
363 | a load of a seeded data file
364 | Rob Flippo 25-JAN-07 Bug#5837043 Running the TL files only in the
365 | base language is not sufficient, since non-trans
366 | ldt files will not be populated in non-US
367 | directories. To solve this will run US files
368 | first to ensure that the data gets loaded, and
369 | then when base lang <> 'US' will re-run the files
370 | in the Base language files
371 |
372 +===========================================================================*/
373 PROCEDURE Load_seed_data (p_appltop IN VARCHAR2
374 ,p_release IN VARCHAR2
375 ,x_execution_status OUT NOCOPY VARCHAR2) IS
376
377 v_procedure_status VARCHAR2(100);
378 v_concurrent_status BOOLEAN;
379 v_ldtpath VARCHAR2(1000);
380 v_lctpath VARCHAR2(1000);
381 v_sub_request_id NUMBER;
382 v_process_name VARCHAR2(150); -- temp variable for holding the procedure
383 -- name being processed - used by the exception
384 -- handler
385
386 -- language variables
387 v_base_lang FND_LANGUAGES.language_code%type;
388 v_lang_start NUMBER;
389 v_subdir_length NUMBER;
390 v_subdir_lang FND_LANGUAGES.language_code%type;
391 v_subdir_lang_length NUMBER;
392
393 -- output variables for the wait_for_request function
394 f_req_wait BOOLEAN; -- return of the wait_for_request function
395 v_req_phase VARCHAR2(100);
396 v_req_status VARCHAR2(100);
397 v_dev_phase VARCHAR2(100);
398 v_dev_status VARCHAR2(100);
399 v_req_message VARCHAR2(100);
400
401 errbuf VARCHAR2(4000);
402 retcode VARCHAR2(4000);
403
404 -- Exceptions
405 e_fndload_error EXCEPTION;
406 e_concurrent_manager EXCEPTION;
407 e_procedure_error EXCEPTION;
408
409 -- This cursor joins to AD_FILES so that we guarantee to run only those
410 -- files that have been installed and that we run for all languages
411 cursor c_ldt_files IS
412 SELECT R.file_name, ALDT.subdir file_directory_path, R.file_product_prefix,
413 R.lctfile_name, ALCT.subdir lctfile_directory_path, R.lctfile_product_prefix,
414 substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1) FILE_LANG,
415 DECODE(substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1),'US','1',substr(ALDT.subdir,instr(ALDT.subdir,'/',-1,1)+1,(length(ALDT.subdir) - instr(ALDT.subdir,'/',-1,1))+1)) ORDER_SEQ
416 FROM fem_rfsh_files R,
417 (SELECT distinct filename, subdir
418 FROM ad_files) ALDT,
419 (SELECT distinct filename, subdir
420 FROM ad_files
421 WHERE substr(subdir,1,5) = 'patch') ALCT
422 WHERE R.file_type = 'LDT'
423 AND UPPER(R.file_name) = UPPER(ALDT.filename)
424 AND UPPER(R.lctfile_name) = UPPER(ALCT.filename)
425 ORDER BY R.sub_phase, ORDER_SEQ, R.file_name;
426
427 cursor c_procs IS
428 SELECT distinct R.process_name, R.procedure_call, R.sub_phase
429 FROM fem_rfsh_procedures R, user_procedures U
430 WHERE R.package_name = U.object_name
431 AND R.procedure_name = U.procedure_name
432 ORDER BY sub_phase asc;
433
434 BEGIN
435 x_execution_status := 'SUCCESS';
436
437 -- Identify the base language
438 -- if there is some error with FND_LANGUAGES, we will
439 -- set the base langauge = to the env language of the
440 -- user running the refresh
441 BEGIN
442
443 SELECT language_code
444 INTO v_base_lang
445 FROM fnd_languages
446 WHERE installed_flag = 'B';
447 EXCEPTION
448 WHEN OTHERS THEN
449 SELECT userenv('LANG')
450 INTO v_base_lang
451 FROM dual;
452 END;
453
454 -- Load LDT Files
455 FOR ldtfile IN c_ldt_files LOOP
456
457 v_ldtpath := p_appltop||ldtfile.file_product_prefix||p_release||'/'||
458 ldtfile.file_directory_path||'/'||ldtfile.file_name;
459 v_lctpath := p_appltop||ldtfile.lctfile_product_prefix||p_release||'/'||
460 ldtfile.lctfile_directory_path||'/'||ldtfile.lctfile_name;
461
462 /*******************************************************
463 Putting this logic to get the language in the SELECT
464 so we can sort by lang
465 -- identify the language_code for the ldt file
466 -- we will only run ldt files of the base language
467 v_subdir_length := length(ldtfile.file_directory_path);
468 v_lang_start := instr(ldtfile.file_directory_path,'/',-1,1);
469 v_subdir_lang_length := (v_subdir_length - v_lang_start) + 1;
470
471 v_subdir_lang := substr(ldtfile.file_directory_path,v_lang_start+1,v_subdir_lang_length);
472 *************************************************************/
473
474 FEM_ENGINES_PKG.Tech_Message
475 (p_severity => c_log_level_1,
476 p_module => c_block||'.'||'Load_seed_data.ldtpath',
477 p_msg_text => v_ldtpath);
478
479 FEM_ENGINES_PKG.Tech_Message
480 (p_severity => c_log_level_1,
481 p_module => c_block||'.'||'Load_seed_data.lctpath',
482 p_msg_text => v_lctpath);
483
484 FEM_ENGINES_PKG.Tech_Message
485 (p_severity => c_log_level_1,
486 p_module => c_block||'.'||'ldt language',
487 p_msg_text => v_subdir_lang);
488
489 -- only load the ldt file if the language_code of the file is the same
490 -- as the base language or it is a US file
491 -- All US files get loaded first. If the base language is is non-US,
492 -- the base lang files will get loaded afterwards, so that the
493 -- translatable strings are all in the Base language
494 IF ldtfile.FILE_LANG = v_base_lang OR ldtfile.FILE_LANG = 'US' THEN
495
496 Refresh_fndload (errbuf
497 ,retcode
498 ,v_sub_request_id
499 ,p_appltop
500 ,p_release
501 ,v_ldtpath
502 ,v_lctpath);
503
504 IF (v_sub_request_id = 0) THEN
505 RAISE e_fndload_error;
506 ELSE
507 COMMIT;
508 END IF;
509
510 -- check status of the submitted request and exit when finished
511 LOOP
512 f_req_wait := FND_CONCURRENT.WAIT_FOR_REQUEST
513 (REQUEST_ID => v_sub_request_id
514 ,INTERVAL =>5
515 ,MAX_WAIT => 600
516 ,PHASE => v_req_phase
517 ,STATUS => v_req_status
518 ,DEV_PHASE => v_dev_phase
519 ,DEV_STATUS => v_dev_status
520 ,MESSAGE => v_req_message);
521
522 CASE v_dev_phase
523 WHEN 'COMPLETE' THEN EXIT;
524 WHEN 'INACTIVE' THEN EXIT;
525 ELSE NULL;
526 END CASE;
527 END LOOP; -- wait_for_request
528 FEM_ENGINES_PKG.Tech_Message
529 (p_severity => c_log_level_1,
530 p_module => c_block||'.'||'Load_seed_data.v_dev_phase',
531 p_msg_text => v_dev_phase);
532
533
534 IF v_dev_phase NOT IN ('COMPLETE') THEN
535 RAISE e_concurrent_manager;
536 ELSIF v_dev_phase = 'COMPLETE' AND v_dev_status NOT IN ('NORMAL') THEN
537 FEM_ENGINES_PKG.USER_MESSAGE
538 (P_APP_NAME => c_fem
539 ,P_MSG_NAME => 'FEM_RFSH_FNDLOAD_ERROR'
540 ,P_TOKEN1 => 'LDTFILE'
541 ,P_VALUE1 => v_ldtpath
542 ,P_TOKEN2 => 'LCTFILE'
543 ,P_VALUE2 => v_lctpath);
544
545 FEM_ENGINES_PKG.Tech_Message
546 (p_severity => c_log_level_1,
547 p_module => c_block||'.'||'Load_seed_data error: ldtfile = ',
548 p_msg_text => v_ldtpath);
549
550 x_execution_status := 'ERROR_RERUN';
551
552 ELSIF v_dev_phase = 'COMPLETE' AND v_dev_status = 'NORMAL' THEN
553 NULL; -- do nothing
554 END IF;
555 END IF; -- v_subdir_lang = v_base_lang
556
557 END LOOP;
558
559 -- Run procedures
560 FOR proc IN c_procs LOOP
561
562 v_process_name := proc.process_name;
563 refresh_procedure(v_procedure_status
564 ,proc.procedure_call);
565 IF v_procedure_status = 'ERROR' THEN
566 RAISE e_procedure_error;
567 END IF;
568 END LOOP;
569
570 EXCEPTION
571 WHEN e_concurrent_manager THEN
572 FEM_ENGINES_PKG.USER_MESSAGE
573 (P_APP_NAME => c_fem
574 ,P_MSG_NAME => 'FEM_RFSH_CONCURRENT_ERROR');
575
576 FEM_ENGINES_PKG.Tech_Message
577 (p_severity => c_log_level_1,
578 p_module => c_block||'.'||'Load_seed_data',
579 p_msg_text => 'Concurrent Manager is unable to process the refresh');
580
581 x_execution_status := 'ERROR_RERUN';
582
583 WHEN e_procedure_error THEN
584
585 FEM_ENGINES_PKG.USER_MESSAGE
586 (P_APP_NAME => c_fem
587 ,P_MSG_NAME => 'FEM_RFSH_PROCEDURE_ERROR'
588 ,P_TOKEN1 => 'PROCESS_NAME'
589 ,P_VALUE1 => v_process_name);
590
591 FEM_ENGINES_PKG.Tech_Message
592 (p_severity => c_log_level_1,
593 p_module => c_block||'.'||'Load_seed_data',
594 p_msg_text => 'Procedure '||v_process_name||' failed');
595
596 x_execution_status := 'ERROR_RERUN';
597
598 END Load_seed_data;
599
600
601 /*===========================================================================+
602 | PROCEDURE
603 | Refresh_fndload
604 |
605 | DESCRIPTION
606 | This procedure calls the FNDLOAD concurrent program for loading
607 | ldt files
608 | SCOPE - PRIVATE
609 |
610 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
611 |
612 | ARGUMENTS : IN:
613 | OUT:
614 |
615 |
616 | NOTES
617 |
618 | MODIFICATION HISTORY
619 | Rob Flippo 16-MAY-05 Created
620 |
621 +===========================================================================*/
622 PROCEDURE Refresh_fndload (errbuf OUT NOCOPY VARCHAR2
623 ,retcode OUT NOCOPY VARCHAR2
624 ,x_sub_request_id OUT NOCOPY NUMBER
625 ,p_appltop IN VARCHAR2
626 ,p_release IN VARCHAR2
627 ,p_ldtpath IN VARCHAR2
628 ,p_lctpath IN VARCHAR2) IS
629
630 v_sub_request_id NUMBER;
631 v_process_name VARCHAR2(150); -- temp variable for holding the procedure
632 -- name being processed - used by the exception
633 -- handler
634
635 -- output variables for the wait_for_request function
636 f_req_wait BOOLEAN; -- return of the wait_for_request function
637 v_req_phase VARCHAR2(100);
638 v_req_status VARCHAR2(100);
639 v_dev_phase VARCHAR2(100);
640 v_dev_status VARCHAR2(100);
641 v_req_message VARCHAR2(100);
642
643 -- Exceptions
644 e_fndload_error EXCEPTION;
645
646 BEGIN
647
648 x_sub_request_id := FND_REQUEST.SUBMIT_REQUEST(
649 application => 'FEM',
650 program => c_FNDLOAD,
651 sub_request => FALSE,
652 argument1 => 'UPLOAD',
653 argument2 => p_lctpath,
654 argument3 => p_ldtpath);
655
656 FEM_ENGINES_PKG.Tech_Message
657 (p_severity => c_log_level_1,
658 p_module => c_block||'.'||'Load_seed_data.x_sub_request_id',
659 p_msg_text => x_sub_request_id);
660
661 END Refresh_fndload;
662
663
664 /*===========================================================================+
665 | PROCEDURE
666 | Refresh_procedure
667 |
668 | DESCRIPTION
669 | This procedure executes the dynamic SQL to call procedures for
670 | required for seeded data population
671 | SCOPE - PRIVATE
672 |
673 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
674 |
675 | ARGUMENTS : IN:
676 | OUT:
677 |
678 |
679 | NOTES
680 |
681 | MODIFICATION HISTORY
682 | Rob Flippo 17-MAY-05 Created
683 |
684 +===========================================================================*/
685
686 PROCEDURE Refresh_procedure (x_procedure_status OUT NOCOPY VARCHAR2
687 ,p_procedure_call IN VARCHAR2) IS
688
689 BEGIN
690
691 EXECUTE IMMEDIATE p_procedure_call;
692 x_procedure_status := 'SUCCESS';
693 EXCEPTION
694 WHEN others THEN x_procedure_status := 'ERROR';
695 END Refresh_procedure;
696
697
698 /*===========================================================================+
699 | PROCEDURE
700 | Clean_tables
701 |
702 | DESCRIPTION
703 | Truncates all of the tables that can store user defined data
704 | This includes tables owned by teams other than FEM (i.e., RCM, PFT, etc)
705 | SCOPE - PRIVATE
706 |
707 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
708 |
709 | ARGUMENTS : IN:
710 | OUT:
711 |
712 |
713 | NOTES
714 |
715 | MODIFICATION HISTORY
716 | Rob Flippo 06-MAY-05 Created
717 | Rob Flippo 22-JUL-05 Modify cursor so only retrieve tables that exist
718 | Rob Flippo 16-MAY-06 bug#5223789 Add call to delete KFF reg info for
719 | composite dimensions
720 | Rob Flippo 14-SEP-06 Bug#5520316 Continue on if get KFF delete failure;
721 | Also continue on if get truncate table failure;
722 +===========================================================================*/
723 PROCEDURE Clean_tables (x_execution_status OUT NOCOPY VARCHAR2) IS
724
725 v_concurrent_status BOOLEAN;
726 v_target_schema VARCHAR2(100); -- temp variable used to identify schema
727 -- the schema name where tables reside
728 -- for a given application_id
729
730 v_app_id NUMBER; -- temp variable used to identify the a
731 -- application_id for which the tables are
732 -- being truncated
733 v_table VARCHAR2(30); -- temp variable to hold the table name being
734 -- truncated
735 v_fnd_status VARCHAR2(1000); -- return variable for get_app_info function
736 v_fnd_industry VARCHAR2(1000); -- return variable for get_app_info function
737 v_fnd_boolean BOOLEAN; -- return variable for get_app_info function
738 v_sql_stmt VARCHAR2(4000); -- dynamic sql for truncate
739
740 -- KFF delete variables
741 v_return_status varchar2(1);
742 v_msg_count number;
743 v_msg_data varchar2(4000);
744
745
746
747 cursor c_apps is
748 SELECT DISTINCT A1.application_short_name app_short_name
749 ,A1.application_id
750 FROM ( SELECT table_owner_application_id
751 FROM fem_rfsh_tables
752 UNION
753 SELECT table_owner_application_id
754 FROM fem_tables_b) R1,
755 fnd_application A1
756 WHERE R1.table_owner_application_id = A1.application_id;
757
758 cursor c_tables (p_app_id IN NUMBER,p_schema IN VARCHAR2) IS
759 SELECT table_name FROM
760 (SELECT R.table_name
761 FROM fem_rfsh_tables R
762 WHERE table_owner_application_id = p_app_id
763 AND EXISTS
764 (SELECT table_name FROM ALL_TABLES A2
765 WHERE A2.table_name = R.table_name
766 AND A2.owner = p_schema)
767 UNION
768 SELECT T.table_name
769 FROM fem_tables_b T
770 WHERE T.table_owner_application_id = p_app_id
771 AND T.table_name not in (select table_name
772 FROM fem_rfsh_tables R)) RT
773 WHERE EXISTS
774 (SELECT table_name FROM ALL_TABLES A1
775 WHERE A1.table_name = RT.table_name
776 AND A1.owner = p_schema);
777
778 -- Exceptions
779 e_composite_delete EXCEPTION;
780 e_table_not_exist EXCEPTION;
781 PRAGMA EXCEPTION_INIT(e_table_not_exist, -0942);
782
783
784 BEGIN
785 x_execution_status := 'SUCCESS';
786
787 -- remove KFF composite dimension registration
788 BEGIN
789 fem_setup_pkg.delete_flexfield
790 (p_api_version => 1.0,p_init_msg_list => NULL
791 ,p_commit => FND_API.G_TRUE
792 ,p_encoded => NULL
793 ,x_return_status=>v_return_status
794 ,x_msg_count=>v_msg_count
795 ,x_msg_data=>v_msg_data
796 ,p_dimension_varchar_label => 'ACTIVITY');
797 IF v_return_status NOT IN ('S') then
798 raise e_composite_delete;
799 END IF;
800 fem_setup_pkg.delete_flexfield
801 (p_api_version => 1.0,p_init_msg_list => NULL
802 ,p_commit => FND_API.G_TRUE
803 ,p_encoded => NULL
804 ,x_return_status=>v_return_status
805 ,x_msg_count=>v_msg_count
806 ,x_msg_data=>v_msg_data
807 ,p_dimension_varchar_label => 'COST_OBJECT');
808 IF v_return_status NOT IN ('S') then
809 raise e_composite_delete;
810 END IF;
811 EXCEPTION
812 WHEN e_composite_delete THEN
813 FEM_ENGINES_PKG.USER_MESSAGE
814 (P_APP_NAME => c_fem
815 ,P_MSG_NAME => 'FEM_RFSH_COMPOSITE_DELETE');
816
817 FEM_ENGINES_PKG.Tech_Message
818 (p_severity => c_log_level_1,
819 p_module => c_block||'.'||'Clean_tables',
820 p_msg_text => 'Failed to delete composite dimension registration');
821
822 x_execution_status := 'ERROR_RERUN';
823
824 END;
825
826
827 FOR app IN c_apps LOOP
828
829 v_app_id := app.application_id;
830 v_fnd_boolean := fnd_installation.get_app_info(
831 APPLICATION_SHORT_NAME => app.app_short_name
832 ,STATUS => v_fnd_status
833 ,INDUSTRY => v_fnd_industry
834 ,ORACLE_SCHEMA => v_target_schema);
835
836 FEM_ENGINES_PKG.Tech_Message
837 (p_severity => c_log_level_1,
838 p_module => c_block||'.'||'Clean_tables.v_target_schema',
839 p_msg_text => v_target_schema);
840
841 FOR tab IN c_tables (v_app_id, v_target_schema) LOOP
842
843 v_table := tab.table_name;
844 v_sql_stmt := 'TRUNCATE TABLE '||v_target_schema||'.'||v_table;
845
846 BEGIN
847 EXECUTE IMMEDIATE v_sql_stmt;
848
849 EXCEPTION
850 WHEN e_table_not_exist THEN
851 FEM_ENGINES_PKG.USER_MESSAGE
852 (P_APP_NAME => c_fem
853 ,P_MSG_NAME => 'FEM_RFSH_TRUNCATE_ERROR'
854 ,P_TOKEN1 => 'TABLE'
855 ,P_VALUE1 => v_table);
856
857 FEM_ENGINES_PKG.Tech_Message
858 (p_severity => c_log_level_1,
859 p_module => c_block||'.'||'Clean_tables',
860 p_msg_text => 'Failed to truncate table '||v_table);
861
862 x_execution_status := 'ERROR_RERUN';
863
864 END;
865 FEM_ENGINES_PKG.Tech_Message
866 (p_severity => c_log_level_1,
867 p_module => c_block||'.'||'Clean_tables.table_name',
868 p_msg_text => v_table);
869
870 END LOOP;
871 END LOOP;
872
873 END Clean_tables;
874
875 /*===========================================================================+
876 | PROCEDURE
877 | Reset_profile_options
878 |
879 | DESCRIPTION
880 | Resets all of the FEM profile options for all users
881 | SCOPE - PRIVATE
882 |
883 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
884 |
885 | ARGUMENTS : IN:
886 | OUT:
887 |
888 |
889 | NOTES
890 |
891 | MODIFICATION HISTORY
892 | Rob Flippo 09-MAY-05 Created
893 | Rob Flippo 12-JUL-06 Bug#5237422 No longer set Ledger profile
894 | or Default Actuals Dataset since there is no
895 | Default dataset
896 |
897 |
898 +===========================================================================*/
899 PROCEDURE Reset_profile_options (x_execution_status IN OUT NOCOPY VARCHAR2) IS
900
901 v_concurrent_status BOOLEAN;
902 v_app_id NUMBER; -- temp variable used to identify the a
903 -- application_id for which the tables are
904 -- being truncated
905 v_ledger_id NUMBER;
906 v_dataset_cd NUMBER;
907 v_process_name VARCHAR2(150); -- the process name that seeds the Default Ledger
908 -- used for error logging only
909 v_ldtfile VARCHAR2(150); -- ldtfile that seeds the Default dataset
910 -- used for error logging only
911 v_lctfile VARCHAR2(150); -- lctfile that seeds the Default dataset
912 -- used for error logging only
913 v_boolean BOOLEAN; -- Return value from FND API to reset profile option
914
915
916 cursor c_appl is
917 SELECT application_id
918 FROM fem_applications;
919
920 cursor c_user IS
921 SELECT user_id
922 FROM fnd_user;
923
924 -- Exceptions
925 e_no_default_ledger EXCEPTION;
926 e_no_default_dataset EXCEPTION;
927
928 BEGIN
929
930 /****************************************************
931 Bug#5237422 comment out
932 BEGIN
933 SELECT ledger_id
934 INTO v_ledger_id
935 FROM fem_ledgers_b
936 WHERE ledger_display_code = 'DEFAULT_LEDGER';
937 EXCEPTION
938 WHEN no_data_found THEN RAISE e_no_default_ledger;
939 END;
940
941 BEGIN
942 SELECT dataset_code
943 INTO v_dataset_cd
944 FROM fem_datasets_b
945 WHERE dataset_display_code = 'Default';
946 EXCEPTION
947 WHEN no_data_found THEN RAISE e_no_default_dataset;
948 END;
949 ******************************************************/
950
951
952 /*Setting Site level for all profiles */
953 v_boolean := FND_PROFILE.save('FEM_LEDGER', v_ledger_id, 'SITE');
954 v_boolean := FND_PROFILE.save('FEM_SIGNAGE_METHOD', NULL, 'SITE');
955 v_boolean := FND_PROFILE.save('FEM_PERIOD', NULL, 'SITE');
956 v_boolean := FND_PROFILE.save('FEM_SEC_FOLDER', NULL, 'SITE');
957 v_boolean := FND_PROFILE.save('FEM_IO_DATA_DEFINITION', NULL, 'SITE');
958 v_boolean := FND_PROFILE.save('FEM_DEFAULT_ACTUALS_DATASET', v_dataset_cd, 'SITE');
959 v_boolean := FND_PROFILE.save('FEM_DATASET', v_dataset_cd, 'SITE');
960
961 /* Setting Appl level for all profiles */
962 FOR appl IN c_appl LOOP
963 v_boolean := FND_PROFILE.save('FEM_LEDGER', NULL, 'APPL',appl.application_id);
964 v_boolean := FND_PROFILE.save('FEM_SIGNAGE_METHOD', NULL, 'APPL', appl.application_id);
965 v_boolean := FND_PROFILE.save('FEM_PERIOD', NULL, 'APPL', appl.application_id);
966 v_boolean := FND_PROFILE.save('FEM_SEC_FOLDER', NULL, 'APPL', appl.application_id);
967 v_boolean := FND_PROFILE.save('FEM_IO_DATA_DEFINITION', NULL, 'APPL', appl.application_id);
968 v_boolean := FND_PROFILE.save('FEM_DEFAULT_ACTUALS_DATASET', v_dataset_cd, 'APPL', appl.application_id);
969 v_boolean := FND_PROFILE.save('FEM_DATASET', v_dataset_cd, 'APPL', appl.application_id);
970 END LOOP;
971
972 /* Setting User level for all profiles */
973 FOR userid IN c_user LOOP
974 v_boolean := FND_PROFILE.save('FEM_LEDGER', NULL, 'USER',userid.user_id);
975 v_boolean := FND_PROFILE.save('FEM_SIGNAGE_METHOD', NULL, 'USER', userid.user_id);
976 v_boolean := FND_PROFILE.save('FEM_PERIOD', NULL, 'USER', userid.user_id);
977 v_boolean := FND_PROFILE.save('FEM_SEC_FOLDER', NULL, 'USER', userid.user_id);
978 v_boolean := FND_PROFILE.save('FEM_IO_DATA_DEFINITION', NULL, 'USER', userid.user_id);
979 v_boolean := FND_PROFILE.save('FEM_DEFAULT_ACTUALS_DATASET', v_dataset_cd, 'USER', userid.user_id);
980 v_boolean := FND_PROFILE.save('FEM_DATASET', v_dataset_cd, 'USER', userid.user_id);
981 END LOOP;
982
983 COMMIT;
984
985 EXCEPTION
986 WHEN e_no_default_ledger THEN
987 v_process_name := 'Create Seeded Ledgers';
988
989 FEM_ENGINES_PKG.USER_MESSAGE
990 (P_APP_NAME => c_fem
991 ,P_MSG_NAME => 'FEM_RFSH_PROCEDURE_ERROR'
992 ,P_TOKEN1 => 'PROCESS_NAME'
993 ,P_VALUE1 => v_process_name);
994
995 FEM_ENGINES_PKG.Tech_Message
996 (p_severity => c_log_level_1,
997 p_module => c_block||'.'||'Reset_profile_options',
998 p_msg_text => 'Default Ledger does not exist');
999
1000 x_execution_status := 'ERROR_RERUN';
1001
1002 WHEN e_no_default_dataset THEN
1003 v_ldtfile := 'fem_dataset.ldt';
1004 v_lctfile := 'fem_dataset.lct';
1005
1006 FEM_ENGINES_PKG.USER_MESSAGE
1007 (P_APP_NAME => c_fem
1008 ,P_MSG_NAME => 'FEM_RFSH_FNDLOAD_ERROR'
1009 ,P_TOKEN1 => 'LDTFILE'
1010 ,P_VALUE1 => v_ldtfile
1011 ,P_TOKEN2 => 'LCTFILE'
1012 ,P_VALUE2 => v_lctfile);
1013
1014 FEM_ENGINES_PKG.Tech_Message
1015 (p_severity => c_log_level_1,
1016 p_module => c_block||'.'||'Reset_profile_options',
1017 p_msg_text => 'Default Dataset does not exist');
1018
1019 x_execution_status := 'ERROR_RERUN';
1020
1021 END Reset_profile_options;
1022
1023
1024
1025 /*===========================================================================+
1026 | PROCEDURE
1027 | Report_Errors
1028 |
1029 | DESCRIPTION
1030 | Retrieves messages from the stack and reports them to the appropriate
1031 | log
1032 | SCOPE - PRIVATE
1033 |
1034 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1035 |
1036 | ARGUMENTS : IN:
1037 | OUT:
1038 |
1039 |
1040 | NOTES
1041 |
1042 |
1043 | MODIFICATION HISTORY
1044 | Rob Flippo 02-MAY-05 Created
1045 |
1046 +===========================================================================*/
1047 PROCEDURE Report_errors IS
1048
1049 v_msg_count NUMBER; -- this is the return count from FND of # messages
1050 v_msg_data VARCHAR2(1000); -- this is the message value when only 1 msg
1051 -- from FND
1052 v_message VARCHAR2(4000);
1053 v_msg_index_out NUMBER;
1054 v_block CONSTANT VARCHAR2(80) :=
1055 'fem.plsql.fem_refresh_eng_pkg.report_errors';
1056
1057
1058 BEGIN
1059 FEM_ENGINES_PKG.Tech_Message
1060 (p_severity => c_log_level_2,
1061 p_module => c_block||'.'||'Report_errors',
1062 p_msg_text => 'BEGIN');
1063
1064 -- Count the number of messages on the stack
1065 FND_MSG_PUB.count_and_get(p_encoded => c_false
1066 ,p_count => v_msg_count
1067 ,p_data => v_msg_data);
1068
1069
1070 IF (v_msg_count = 1) THEN
1071 FND_MESSAGE.Set_Encoded(v_msg_data);
1072 v_message := FND_MESSAGE.Get;
1073
1074 FEM_ENGINES_PKG.User_Message(
1075 p_msg_text => v_message);
1076
1077 FEM_ENGINES_PKG.TECH_MESSAGE
1078 (p_severity => c_log_level_2,
1079 p_module => v_block||'.msg_data',
1080 p_msg_text => v_message);
1081
1082 ELSIF (v_msg_count > 1) THEN
1083 FOR i IN 1..v_msg_count LOOP
1084 FND_MSG_PUB.Get(
1085 p_msg_index => i,
1086 p_encoded => c_false,
1087 p_data => v_message,
1088 p_msg_index_out => v_msg_index_out);
1089
1090 FEM_ENGINES_PKG.User_Message(
1091 p_msg_text => v_message);
1092
1093 FEM_ENGINES_PKG.TECH_MESSAGE
1094 (p_severity => c_log_level_2,
1095 p_module => v_block||'.msg_data',
1096 p_msg_text => v_message);
1097
1098 END LOOP;
1099 END IF;
1100
1101 FND_MSG_PUB.Initialize;
1102
1103 FEM_ENGINES_PKG.Tech_Message
1104 (p_severity => c_log_level_2,
1105 p_module => c_block||'.'||'Report_errors',
1106 p_msg_text => 'END');
1107
1108
1109 END Report_errors;
1110
1111 /*===========================================================================+
1112 | PROCEDURE
1113 | Refresh_completion
1114 |
1115 | DESCRIPTION
1116 | This procedure reports a completion message for each application
1117 | that is refreshed successfully. An App is identified as having been
1118 | refreshed as long as it had one table in fem_rfsh_tables that was
1119 | actually truncated.
1120 | SCOPE - PRIVATE
1121 |
1122 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1123 |
1124 | ARGUMENTS : IN:
1125 | OUT:
1126 |
1127 |
1128 | NOTES
1129 |
1130 | MODIFICATION HISTORY
1131 | Rob Flippo 19-MAY-05 Created
1132 |
1133 +===========================================================================*/
1134
1135 PROCEDURE Refresh_completion IS
1136
1137 cursor c1 IS
1138 SELECT A.application_id, V.application_short_name, V.application_name
1139 FROM fem_applications A, fnd_application_vl V
1140 WHERE A.application_id = V.application_id;
1141
1142 v_app_name VARCHAR2(240);
1143 v_app_id NUMBER;
1144 v_fnd_boolean BOOLEAN;
1145 v_fnd_status VARCHAR2(1000);
1146 v_fnd_industry VARCHAR2(1000); -- return variable for get_app_info function
1147 v_target_schema VARCHAR2(100);
1148 v_table_count NUMBER;
1149
1150 BEGIN
1151
1152 FOR app IN c1 LOOP
1153
1154 v_app_id := app.application_id;
1155 v_fnd_boolean := fnd_installation.get_app_info(
1156 APPLICATION_SHORT_NAME => app.application_short_name
1157 ,STATUS => v_fnd_status
1158 ,INDUSTRY => v_fnd_industry
1159 ,ORACLE_SCHEMA => v_target_schema);
1160
1161 SELECT count(*)
1162 INTO v_table_count
1163 FROM fem_rfsh_tables R, all_tables A
1164 WHERE R.table_name = A.table_name
1165 AND R.table_owner_application_id = v_app_id
1166 AND A.owner = v_target_schema;
1167
1168 IF v_table_count >0 THEN
1169
1170 FEM_ENGINES_PKG.USER_MESSAGE
1171 (P_APP_NAME => c_fem
1172 ,P_MSG_NAME => 'FEM_RFSH_COMPLETION'
1173 ,P_TOKEN1 => 'APP'
1174 ,P_VALUE1 => app.application_name);
1175 END IF;
1176
1177 END LOOP;
1178
1179 END Refresh_completion;
1180
1181
1182
1183 /*===========================================================================+
1184 | PROCEDURE
1185 | Register_process_execution
1186 |
1187 | DESCRIPTION
1188 | Registers the concurrent request in FEM_PL_REQUESTS, registers
1189 | the object execution in FEM_PL_OBJECT_EXECUTIION, obtaining an
1190 | FEM "execution lock, and performs other FEM process initialization
1191 | steps.
1192 | SCOPE - PRIVATE
1193 |
1194 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1195 |
1196 | ARGUMENTS : IN:
1197 |
1198 |
1199 | OUT:
1200 | x_completion_code returns 0 for success, 2 for failure.
1201 |
1202 |
1203 | IN/ OUT:
1204 |
1205 | RETURNS : NONE
1206 |
1207 | NOTES
1208 |
1209 |
1210 | MODIFICATION HISTORY
1211 | Rob Flippo 02-MAY-05 Created
1212 |
1213 +===========================================================================*/
1214
1215 PROCEDURE Register_process_execution (p_object_id IN NUMBER
1216 ,p_obj_def_id IN NUMBER
1217 ,p_execution_status IN VARCHAR)
1218 IS
1219
1220 v_API_return_status VARCHAR2(30);
1221 v_exec_state VARCHAR2(30); -- NORMAL, RESTART, RERUN
1222 v_num_msg NUMBER;
1223 v_stmt_type fem_pl_tables.statement_type%TYPE;
1224 i PLS_INTEGER;
1225 v_msg_count NUMBER;
1226 v_msg_data VARCHAR2(4000);
1227 v_previous_request_id NUMBER;
1228
1229 v_request_id NUMBER;
1230 v_apps_user_id NUMBER;
1231 v_login_id NUMBER;
1232 v_pgm_id NUMBER;
1233 v_pgm_app_id NUMBER;
1234 v_concurrent_status BOOLEAN;
1235
1236 Exec_Lock_Exists EXCEPTION;
1237 e_pl_register_req_failed EXCEPTION;
1238 e_exec_lock_failed EXCEPTION;
1239 e_post_process EXCEPTION;
1240
1241
1242 BEGIN
1243
1244 FEM_ENGINES_PKG.Tech_Message
1245 (p_severity => c_log_level_2,
1246 p_module => c_block||'.'||'Register_process_execution',
1247 p_msg_text => 'BEGIN');
1248
1249 v_request_id := fnd_global.conc_request_id;
1250 v_apps_user_id := FND_GLOBAL.User_Id;
1251 v_login_id := FND_GLOBAL.Login_Id;
1252 v_pgm_id := FND_GLOBAL.Conc_Program_Id;
1253 v_pgm_app_id := FND_GLOBAL.Prog_Appl_ID;
1254 -- Call the FEM_PL_PKG.Register_Request API procedure to register
1255 -- the concurrent request in FEM_PL_REQUESTS.
1256
1257 FEM_PL_PKG.Register_Request
1258 (P_API_VERSION => c_api_version,
1259 P_COMMIT => c_false,
1260 P_REQUEST_ID => v_request_id,
1261 P_USER_ID => v_apps_user_id,
1262 P_LAST_UPDATE_LOGIN => v_login_id,
1263 P_PROGRAM_ID => v_pgm_id,
1264 P_PROGRAM_LOGIN_ID => v_login_id,
1265 P_PROGRAM_APPLICATION_ID => v_pgm_app_id,
1266 X_MSG_COUNT => v_msg_count,
1267 X_MSG_DATA => v_msg_data,
1268 X_RETURN_STATUS => v_API_return_status);
1269
1270 FEM_ENGINES_PKG.Tech_Message
1271 (p_severity => c_log_level_1,
1272 p_module => c_block||'.'||'Register_request.v_api_return_status',
1273 p_msg_text => v_API_return_status);
1274
1275 IF v_API_return_status NOT IN ('S') THEN
1276 RAISE e_pl_register_req_failed;
1277 END IF;
1278 -- Check for process locks and process overlaps and register
1279 -- the execution in FEM_PL_OBJECT_EXECUTIONS, obtaining an execution lock.
1280
1281 FEM_PL_PKG.Register_Object_Execution
1282 (P_API_VERSION => c_api_version,
1283 P_COMMIT => c_false,
1284 P_REQUEST_ID => v_request_id,
1285 P_OBJECT_ID => p_object_id,
1286 P_EXEC_OBJECT_DEFINITION_ID => p_obj_def_id,
1287 P_USER_ID => v_apps_user_id,
1288 P_LAST_UPDATE_LOGIN => v_login_id,
1289 X_EXEC_STATE => v_exec_state,
1290 X_PREV_REQUEST_ID => v_previous_request_id,
1291 X_MSG_COUNT => v_msg_count,
1292 X_MSG_DATA => v_msg_data,
1293 X_RETURN_STATUS => v_API_return_status);
1294
1295 IF v_API_return_status NOT IN ('S') THEN
1296 -- Lock exists or API call failed
1297 RAISE e_exec_lock_failed;
1298 END IF;
1299
1300 FEM_PL_PKG.Register_Object_Def
1301 (P_API_VERSION => c_api_version,
1302 P_COMMIT => c_false,
1303 P_REQUEST_ID => v_request_id,
1304 P_OBJECT_ID => p_object_id,
1305 P_OBJECT_DEFINITION_ID => p_obj_def_id,
1306 P_USER_ID => v_apps_user_id,
1307 P_LAST_UPDATE_LOGIN => v_login_id,
1308 X_MSG_COUNT => v_msg_count,
1309 X_MSG_DATA => v_msg_data,
1310 X_RETURN_STATUS => v_API_return_status);
1311
1312 IF v_API_return_status NOT IN ('S') THEN
1313 -- Lock exists or API call failed
1314 RAISE e_exec_lock_failed;
1315 END IF;
1316
1317
1318 ------------------------------------
1319 -- Update Object Execution Status --
1320 ------------------------------------
1321 FEM_PL_PKG.Update_Obj_Exec_Status(
1322 P_API_VERSION => c_api_version,
1323 P_COMMIT => c_true,
1324 P_REQUEST_ID => v_request_id,
1325 P_OBJECT_ID => p_object_id,
1326 P_EXEC_STATUS_CODE => p_execution_status,
1327 P_USER_ID => v_apps_user_id,
1328 P_LAST_UPDATE_LOGIN => null,
1329 X_MSG_COUNT => v_msg_count,
1330 X_MSG_DATA => v_msg_data,
1331 X_RETURN_STATUS => v_API_return_status);
1332
1333 IF v_API_return_status NOT IN ('S') THEN
1334 RAISE e_post_process;
1335 END IF;
1336
1337 ---------------------------
1338 -- Update Request Status --
1339 ---------------------------
1340 FEM_PL_PKG.Update_Request_Status(
1341 P_API_VERSION => c_api_version,
1342 P_COMMIT => c_true,
1343 P_REQUEST_ID => v_request_id,
1344 P_EXEC_STATUS_CODE => p_execution_status,
1345 P_USER_ID => v_apps_user_id,
1346 P_LAST_UPDATE_LOGIN => null,
1347 X_MSG_COUNT => v_msg_count,
1348 X_MSG_DATA => v_msg_data,
1349 X_RETURN_STATUS => v_API_return_status);
1350
1351 IF v_API_return_status NOT IN ('S') THEN
1352 RAISE e_post_process;
1353 END IF;
1354
1355 FEM_ENGINES_PKG.Tech_Message
1356 (p_severity => c_log_level_2,
1357 p_module => c_block||'.'||'Register_process_execution',
1358 p_msg_text => 'END');
1359
1360 COMMIT;
1361
1362 EXCEPTION
1363 WHEN e_pl_register_req_failed THEN
1364 -- display user message
1365 FEM_ENGINES_PKG.USER_MESSAGE
1366 (P_APP_NAME => c_fem
1367 ,P_MSG_NAME => G_PL_REG_REQUEST_ERR);
1368
1369 WHEN e_exec_lock_failed THEN
1370 FEM_ENGINES_PKG.USER_MESSAGE
1371 (P_APP_NAME => c_fem
1372 ,P_MSG_NAME => G_PL_OBJ_EXEC_LOCK_ERR);
1373
1374 FEM_ENGINES_PKG.Tech_Message
1375 (p_severity => c_log_level_1,
1376 p_module => c_block||'.'||'Register_process_execution',
1377 p_msg_text => 'raising Exec_Lock_failed');
1378
1379 FEM_PL_PKG.Unregister_Request(
1380 P_API_VERSION => c_api_version,
1381 P_COMMIT => c_true,
1382 P_REQUEST_ID => v_request_id,
1383 X_MSG_COUNT => v_msg_count,
1384 X_MSG_DATA => v_msg_data,
1385 X_RETURN_STATUS => v_API_return_status);
1386 -- Technical messages have already been logged by the API;
1387
1388 WHEN e_post_process THEN
1389 FEM_ENGINES_PKG.TECH_MESSAGE
1390 (p_severity => c_log_level_1,
1391 p_module => c_block||'.'||'Register_process_execution',
1392 p_msg_text => 'Post Process failed');
1393
1394 FEM_ENGINES_PKG.USER_MESSAGE
1395 (P_APP_NAME => c_fem
1396 ,P_MSG_NAME => G_EXT_LDR_POST_PROC_ERR);
1397
1398 END Register_Process_Execution;
1399
1400
1401
1402 /*===========================================================================+
1403 | PROCEDURE
1404 | Main
1405 |
1406 | DESCRIPTION
1407 |
1408 |
1409 |
1410 | SCOPE - PUBLIC
1411 |
1412 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1413 |
1414 | ARGUMENTS : IN:
1415 |
1416 | OUT:
1417 |
1418 | IN/ OUT:
1419 |
1420 | RETURNS : NONE
1421 |
1422 | NOTES
1423 | This procedure is called by Concurrent Manager when the loader is launched
1424 | It performs the following:
1425 | 1) Validates $FEM_TOP and the input parameters passed by CM
1426 | 2) Truncates the tables
1427 | 3) Reloads the ldt files and the runs any procedures for seeded data
1428 | 4) Resets profile options
1429 | 5) Reports any error messages from the stack
1430 | 6) Registers the process execution
1431 | HISTORY
1432 | 08-JUN-05 Rob Flippo Modified to continue if error encountered during
1433 | load_seed_data
1434 | 14-SEP-06 Rob Flippo Bug#5520316 Modified so that clean_tables, load_seed_data
1435 | and reset_profile_options all will get executed
1436 | even if error encountered in any of those 3
1437 | procs
1438 ===========================================================================*/
1439 PROCEDURE Main (
1440 errbuf OUT NOCOPY VARCHAR2
1441 ,retcode OUT NOCOPY VARCHAR2
1442 )
1443
1444 IS
1445
1446 v_concurrent_status BOOLEAN;
1447 v_execution_status VARCHAR2(30);
1448
1449 v_appltop VARCHAR2(1000);
1450 v_release VARCHAR2(100);
1451 -- Nested Procedure declarations
1452
1453 ---------------------------------------------------------------------------
1454 -- Main body of the "Main" procedure
1455 ---------------------------------------------------------------------------
1456 BEGIN
1457 FEM_ENGINES_PKG.TECH_MESSAGE
1458 (p_severity => c_log_level_1
1459 ,p_module => c_block||'.'||c_proc_name||'.Main'
1460 ,p_msg_text => 'begin');
1461
1462 -- initialize the message stack
1463 FND_MSG_PUB.Initialize;
1464
1465 v_execution_status := 'SUCCESS';
1466 gv_request_id := fnd_global.conc_request_id;
1467
1468 Eng_master_prep (v_appltop, v_release, v_execution_status);
1469
1470 IF v_execution_status = 'SUCCESS' THEN
1471 Clean_tables(v_execution_status);
1472 Load_seed_data (v_appltop
1473 ,v_release
1474 ,v_execution_status);
1475 Reset_profile_options(v_execution_status);
1476 END IF;
1477
1478 IF v_execution_status = 'ERROR_RERUN' THEN
1479 Report_errors;
1480 END IF;
1481
1482 Register_process_execution (c_object_id
1483 ,c_object_definition_id
1484 ,v_execution_status);
1485
1486 IF v_execution_status = 'ERROR_RERUN' THEN
1487 retcode := 2;
1488 FEM_ENGINES_PKG.USER_MESSAGE
1489 (P_APP_NAME => c_fem
1490 ,P_MSG_NAME => 'FEM_EXEC_RERUN');
1491 ELSE
1492 Refresh_completion;
1493 END IF;
1494
1495
1496 EXCEPTION
1497
1498 WHEN OTHERS THEN
1499 retcode := 2;
1500 gv_prg_msg := sqlerrm;
1501 gv_callstack := dbms_utility.format_call_stack;
1502
1503 FEM_ENGINES_PKG.TECH_MESSAGE
1504 (p_severity => c_log_level_6
1505 ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
1506 ,p_msg_text => gv_prg_msg);
1507
1508 FEM_ENGINES_PKG.TECH_MESSAGE
1509 (p_severity => c_log_level_6
1510 ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
1511 ,p_msg_text => gv_callstack);
1512
1513 FEM_ENGINES_PKG.USER_MESSAGE
1514 (p_app_name => c_fem
1515 ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
1516 ,P_TOKEN1 => 'ERR_MSG'
1517 ,P_VALUE1 => gv_prg_msg);
1518 /*
1519 FEM_ENGINES_PKG.USER_MESSAGE
1520 (p_app_name => c_fem
1521 ,p_msg_text => gv_prg_msg); */
1522
1523
1524 END Main;
1525
1526 /***************************************************************************/
1527
1528 END FEM_REFRESH_ENG_PKG;