[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_PL_PKG
Source
1 PACKAGE BODY FEM_INTG_PL_PKG AS
2 /* $Header: fem_intg_pl.plb 120.1 2005/09/09 15:22:33 appldev noship $ */
3
4 pc_api_version CONSTANT NUMBER := 1.0;
5
6 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
7 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
8 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
9 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
10 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
11 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
12 pc_module_name CONSTANT VARCHAR2(100):= 'fem.plsql.fem_intg_pl_pkg';
13
14 --
15 -- PUBLIC FUNCTIONS
16 --
17
18 FUNCTION Obj_Def_Data_Edit_Lock_Exists(p_object_definition_id NUMBER)
19 RETURN VARCHAR2 IS
20 edit_lock_exists VARCHAR2(1);
21 BEGIN
22 FEM_PL_PKG.obj_def_data_edit_lock_exists(
23 p_object_definition_id => p_object_definition_id,
24 x_data_edit_lock_exists => edit_lock_exists);
25
26 return edit_lock_exists;
27 END Obj_Def_Data_Edit_Lock_Exists;
28
29
30 FUNCTION Can_Delete_Object_Def(p_object_definition_id NUMBER)
31 RETURN VARCHAR2 IS
32 can_delete VARCHAR2(1);
33 msg_count NUMBER;
34 msg_data VARCHAR2(4000);
35 BEGIN
36 FEM_PL_PKG.can_delete_object_def(
37 p_object_definition_id => p_object_definition_id,
38 x_can_delete_obj_def => can_delete,
39 x_msg_count => msg_count,
40 x_msg_data => msg_data,
41 p_calling_program => 'can_delete_object');
42
43 return can_delete;
44 END Can_Delete_Object_Def;
45
46
47 FUNCTION Dimension_Rules_Have_Been_Run(p_chart_of_accounts_id NUMBER)
48 RETURN VARCHAR2 IS
49 has_been_run VARCHAR2(1);
50
51 -- Used to get a list of all dimension rules that must have been run
52 -- for this particular chart of accounts
53 CURSOR coa_objects_cursor IS
54 SELECT dim_rule_obj_id
55 FROM fem_intg_dim_rules
56 WHERE chart_of_accounts_id = p_chart_of_accounts_id;
57
58 dim_rule_obj_id NUMBER;
59
60 -- Used to check the execution status of a dimension rule the last
61 -- time it was executed
62 CURSOR exec_cursor(c_object_id NUMBER) IS
63 SELECT exec_status_code
64 FROM fem_pl_object_executions
65 WHERE object_id = c_object_id
66 ORDER BY event_order desc;
67
68 status VARCHAR2(100);
69 BEGIN
70 OPEN coa_objects_cursor;
71 FETCH coa_objects_cursor INTO dim_rule_obj_id;
72 WHILE (coa_objects_cursor%FOUND) LOOP
73 OPEN exec_cursor(dim_rule_obj_id);
74 FETCH exec_cursor INTO status;
75 IF (exec_cursor%NOTFOUND OR status <> 'SUCCESS') THEN
76 CLOSE exec_cursor;
77 CLOSE coa_objects_cursor;
78 return 'F';
79 END IF;
80 CLOSE exec_cursor;
81
82 FETCH coa_objects_cursor INTO dim_rule_obj_id;
83 END LOOP;
84 CLOSE coa_objects_cursor;
85
86 -- If we got this far, it means none of the dimension rules had issues
87 return 'T';
88 END Dimension_Rules_Have_Been_Run;
89
90
91 FUNCTION Effective_Date_Incl_Rslt_Data(
92 p_object_definition_id NUMBER,
93 p_new_effective_start_date DATE,
94 p_new_effective_end_date DATE)
95 RETURN VARCHAR2 IS
96 date_includes VARCHAR2(1);
97 msg_data VARCHAR2(4000);
98 msg_count NUMBER;
99 BEGIN
100 FEM_PL_PKG.Effective_Date_Incl_Rslt_Data(
101 p_object_definition_id => p_object_definition_id,
102 p_new_effective_start_date => p_new_effective_start_date,
103 p_new_effective_end_date => p_new_effective_end_date,
104 x_msg_count => msg_count,
105 x_msg_data => msg_data,
106 x_date_incl_rslt_data => date_includes);
107
108 return date_includes;
109 END Effective_Date_Incl_Rslt_Data;
110
111
112 PROCEDURE Register_Process_Execution (
113 p_obj_id IN NUMBER,
114 p_obj_def_id IN NUMBER,
115 p_req_id IN NUMBER,
116 p_user_id IN NUMBER,
117 p_login_id IN NUMBER,
118 p_pgm_id IN NUMBER,
119 p_pgm_app_id IN NUMBER,
120 p_module_name IN VARCHAR2,
121 p_hierarchy_name IN VARCHAR2 DEFAULT NULL,
122 x_completion_code OUT NOCOPY NUMBER
123 ) IS
124 v_func_name VARCHAR2(100);
125 v_msg_count NUMBER;
126 v_msg_data VARCHAR2(2000);
127 v_return_status VARCHAR2(1);
128 v_exec_state VARCHAR2(30);
129 v_prev_request_id NUMBER;
130 v_exec_lock_exists BOOLEAN := FALSE;
131
132 FEM_INTG_fatal_err EXCEPTION;
133 BEGIN
134
135 v_func_name := pc_module_name || '.Register_Process_Execution';
136
137 FEM_ENGINES_PKG.Tech_Message(
138 p_severity => pc_log_level_procedure,
139 p_module => p_module_name || '.begin',
140 p_app_name => 'FEM',
141 p_msg_name => 'FEM_GL_POST_201',
142 p_token1 => 'FUNC_NAME',
143 p_value1 => v_func_name,
144 p_token2 => 'TIME',
145 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
146 );
147
148 FND_MSG_PUB.Initialize;
149
150 FEM_PL_PKG.Register_Request(
151 p_api_version => pc_api_version,
152 p_commit => 'T',
153 p_cal_period_id => NULL,
154 p_ledger_id => NULL,
155 p_dataset_io_obj_def_id => NULL,
156 p_output_dataset_code => NULL,
157 p_source_system_code => NULL,
158 p_effective_date => NULL,
159 p_rule_set_obj_def_id => NULL,
160 p_rule_set_name => NULL,
161 p_request_id => p_req_id,
162 p_user_id => p_user_id,
163 p_last_update_login => p_login_id,
164 p_program_id => p_pgm_id,
165 p_program_login_id => p_login_id,
166 p_program_application_id => p_pgm_app_id,
167 p_exec_mode_code => 'S',
168 p_dimension_id => NULL,
169 p_table_name => NULL,
170 p_hierarchy_name => p_hierarchy_name,
171 x_msg_count => v_msg_count,
172 x_msg_data => v_msg_data,
173 x_return_status => v_return_status
174 );
175
176 IF v_return_status <> 'S' THEN
177
178 FEM_ENGINES_PKG.Tech_Message(
179 p_severity => pc_log_level_statement,
180 p_module => p_module_name || '.rrfe',
181 p_msg_text => 'raising FEM_INTG_fatal_err'
182 );
183
184 RAISE FEM_INTG_fatal_err;
185
186 END IF;
187
188 FND_MSG_PUB.Initialize;
189
190 FEM_PL_PKG.Register_Object_Execution(
191 p_api_version => pc_api_version,
192 p_commit => 'T',
193 p_request_id => p_req_id,
194 p_object_id => p_obj_id,
195 p_exec_object_definition_id => p_obj_def_id,
196 p_user_id => p_user_id,
197 p_last_update_login => p_login_id,
198 p_exec_mode_code => 'S',
199 x_exec_state => v_exec_state,
200 x_prev_request_id => v_prev_request_id,
201 x_msg_count => v_msg_count,
202 x_msg_data => v_msg_data,
203 x_return_status => v_return_status
204 );
205
206 IF v_return_status <> 'S' THEN
207
208 IF v_return_status = 'E' THEN
209 v_exec_lock_exists := TRUE;
210 END IF;
211
212 FEM_ENGINES_PKG.Tech_Message(
213 p_severity => pc_log_level_statement,
214 p_module => p_module_name || '.roefe',
215 p_msg_text => 'raising FEM_INTG_fatal_err'
216 );
217
218 RAISE FEM_INTG_fatal_err;
219
220 END IF;
221
222 FND_MSG_PUB.Initialize;
223
224 FEM_PL_PKG.Register_Object_Def(
225 p_api_version => pc_api_version,
226 p_commit => 'T',
227 p_request_id => p_req_id,
228 p_object_id => p_obj_id,
229 p_object_definition_id => p_obj_def_id,
230 p_user_id => p_user_id,
231 p_last_update_login => p_login_id,
232 x_msg_count => v_msg_count,
233 x_msg_data => v_msg_data,
234 x_return_status => v_return_status
235 );
236
237 IF v_return_status <> 'S' THEN
238
239 FEM_ENGINES_PKG.Tech_Message(
240 p_severity => pc_log_level_statement,
241 p_module => p_module_name || '.rodfe',
242 p_msg_text => 'raising FEM_INTG_fatal_err'
243 );
244 RAISE FEM_INTG_fatal_err;
245
246 END IF;
247
248 COMMIT;
249
250 FEM_ENGINES_PKG.Tech_Message(
251 p_severity => pc_log_level_procedure,
252 p_module => p_module_name || '.end',
253 p_app_name => 'FEM',
254 p_msg_name => 'FEM_GL_POST_202',
255 p_token1 => 'FUNC_NAME',
256 p_value1 => v_func_name,
257 p_token2 => 'TIME',
258 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
259 );
260
261 x_completion_code := 0;
262
263 EXCEPTION
264
265 WHEN FEM_INTG_fatal_err THEN
266
267 ROLLBACK;
268
269 FEM_ENGINES_PKG.Tech_Message(
270 p_severity => pc_log_level_statement,
271 p_module => p_module_name || '.v_msg_count',
272 p_app_name => 'FEM',
273 p_msg_name => 'FEM_GL_POST_204',
274 p_token1 => 'VAR_NAME',
275 p_value1 => 'v_msg_count',
276 p_token2 => 'VAR_VAL',
277 p_value2 => TO_CHAR(v_msg_count)
278 );
279
280 IF v_msg_count = 1 THEN
281
282 FEM_ENGINES_PKG.Tech_Message(
283 p_severity => pc_log_level_statement,
284 p_module => p_module_name || '.v_msg_data1',
285 p_app_name => 'FEM',
286 p_msg_name => 'FEM_GL_POST_204',
287 p_token1 => 'VAR_NAME',
288 p_value1 => 'v_msg_data',
289 p_token2 => 'VAR_VAL',
290 p_value2 => v_msg_data
291 );
292
293 FEM_ENGINES_PKG.User_Message(
294 p_app_name => 'FEM',
295 p_msg_text => v_msg_data
296 );
297
298 ELSIF v_msg_count > 1 THEN
299
300 FOR i IN 1 .. v_msg_count LOOP
301
302 v_msg_data := FND_MSG_PUB.Get(
303 p_msg_index => i,
304 p_encoded => 'F'
305 );
306
307 FEM_ENGINES_PKG.Tech_Message(
308 p_severity => pc_log_level_statement,
309 p_module => p_module_name || '.v_msg_data2',
310 p_app_name => 'FEM',
311 p_msg_name => 'FEM_GL_POST_204',
312 p_token1 => 'VAR_NAME',
313 p_value1 => 'v_msg_data',
314 p_token2 => 'VAR_VAL',
315 p_value2 => v_msg_data
316 );
317
318 FEM_ENGINES_PKG.User_Message(
319 p_app_name => 'FEM',
320 p_msg_text => v_msg_data
321 );
322
323 END LOOP;
324
325 END IF;
326
327 IF v_exec_lock_exists THEN
328
329 FEM_ENGINES_PKG.Tech_Message(
330 p_severity => pc_log_level_exception,
331 p_module => p_module_name || '.v_exec_lock_exists',
332 p_app_name => 'FEM',
333 p_msg_name => 'FEM_INTG_EXEC_LOCK_EXISTS'
334 );
335
336 FEM_ENGINES_PKG.User_Message(
337 p_app_name => 'FEM',
338 p_msg_name => 'FEM_INTG_EXEC_LOCK_EXISTS'
339 );
340
341 FEM_PL_PKG.Unregister_Request(
342 p_api_version => pc_api_version,
343 p_commit => 'T',
344 p_request_id => p_req_id,
345 x_msg_count => v_msg_count,
346 x_msg_data => v_msg_data,
347 x_return_status => v_return_status
348 );
349
350 END IF;
351
352 FEM_ENGINES_PKG.Tech_Message(
353 p_severity => pc_log_level_procedure,
354 p_module => p_module_name || '.fatal_err_exception',
355 p_app_name => 'FEM',
356 p_msg_name => 'FEM_GL_POST_203',
357 p_token1 => 'FUNC_NAME',
358 p_value1 => v_func_name,
359 p_token2 => 'TIME',
360 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
361 );
362
363 FEM_ENGINES_PKG.User_Message(
364 p_app_name => 'FEM',
365 p_msg_name => 'FEM_INTG_PROC_FAILURE'
366 );
367
368 x_completion_code := 2;
369
370 WHEN OTHERS THEN
371
372 ROLLBACK;
373
374 FEM_ENGINES_PKG.Tech_Message(
375 p_severity => pc_log_level_unexpected,
376 p_module => p_module_name || '.unexpected_exception',
377 p_app_name => 'FEM',
378 p_msg_name => 'FEM_GL_POST_215',
379 p_token1 => 'ERR_MSG',
380 p_value1 => SQLERRM
381 );
382
383 FEM_ENGINES_PKG.User_Message(
384 p_app_name => 'FEM',
385 p_msg_name => 'FEM_GL_POST_215',
386 p_token1 => 'ERR_MSG',
387 p_value1 => SQLERRM
388 );
389
390 FEM_ENGINES_PKG.Tech_Message(
391 p_severity => pc_log_level_procedure,
392 p_module => p_module_name || '.unexpected_exception',
393 p_app_name => 'FEM',
394 p_msg_name => 'FEM_GL_POST_203',
395 p_token1 => 'FUNC_NAME',
396 p_value1 => v_func_name,
397 p_token2 => 'TIME',
398 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
399 );
400
401 FEM_ENGINES_PKG.User_Message(
402 p_app_name => 'FEM',
403 p_msg_name => 'FEM_INTG_PROC_FAILURE'
404 );
405
406 x_completion_code := 2;
407
408 END Register_Process_Execution;
409
410
411 PROCEDURE Final_Process_Logging(
412 p_obj_id IN NUMBER,
413 p_obj_def_id IN NUMBER,
417 p_exec_status IN VARCHAR2,
414 p_req_id IN NUMBER,
415 p_user_id IN NUMBER,
416 p_login_id IN NUMBER,
418 p_row_num_loaded IN NUMBER,
419 p_err_num_count IN NUMBER,
420 p_final_msg_name IN VARCHAR2,
421 p_module_name IN VARCHAR2,
422 x_completion_code OUT NOCOPY NUMBER
423 ) IS
424 v_func_name VARCHAR2(100);
425 v_msg_count NUMBER;
426 v_msg_data VARCHAR2(2000);
427 v_return_status VARCHAR2(1);
428
429 FEM_INTG_warn EXCEPTION;
430
431 BEGIN
432
433 v_func_name := pc_module_name || '.Final_Process_Logging';
434
435 FEM_ENGINES_PKG.Tech_Message(
436 p_severity => pc_log_level_procedure,
437 p_module => p_module_name || '.begin',
438 p_app_name => 'FEM',
439 p_msg_name => 'FEM_GL_POST_201',
440 p_token1 => 'FUNC_NAME',
441 p_value1 => v_func_name,
442 p_token2 => 'TIME',
443 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
444 );
445
446 FND_MSG_PUB.Initialize;
447
448 FEM_PL_PKG.Update_Obj_Exec_Errors(
449 p_api_version => pc_api_version,
450 p_commit => 'T',
451 p_request_id => p_req_id,
452 p_object_id => p_obj_id,
453 p_errors_reported => p_err_num_count,
454 p_errors_reprocessed => 0,
455 p_user_id => p_user_id,
456 p_last_update_login => p_login_id,
457 x_msg_count => v_msg_count,
458 x_msg_data => v_msg_data,
459 x_return_status => v_return_status
460 );
461
462 IF v_msg_count = 1 THEN
463 FEM_ENGINES_PKG.Tech_Message(
464 p_severity => pc_log_level_statement,
465 p_module => p_module_name || '.uoee',
466 p_app_name => 'FEM',
467 p_msg_name => 'FEM_GL_POST_204',
468 p_token1 => 'VAR_NAME',
469 p_value1 => 'v_msg_data',
470 p_token2 => 'VAR_VAL',
471 p_value2 => v_msg_data
472 );
473
474 FEM_ENGINES_PKG.User_Message(
475 p_app_name => 'FEM',
476 p_msg_text => v_msg_data
477 );
478
479 RAISE FEM_INTG_warn;
480
481 END IF;
482
483 FEM_PL_PKG.Update_Obj_Exec_Status(
484 p_api_version => pc_api_version,
485 p_commit => 'T',
486 p_request_id => p_req_id,
487 p_object_id => p_obj_id,
488 p_exec_status_code => p_exec_status,
489 p_user_id => p_user_id,
490 p_last_update_login => p_login_id,
491 x_msg_count => v_msg_count,
492 x_msg_data => v_msg_data,
493 x_return_status => v_return_status
494 );
495
496 IF v_msg_count = 1 THEN
497
498 FEM_ENGINES_PKG.Tech_Message(
499 p_severity => pc_log_level_statement,
500 p_module => p_module_name || '.uoes',
501 p_app_name => 'FEM',
502 p_msg_name => 'FEM_GL_POST_204',
503 p_token1 => 'VAR_NAME',
504 p_value1 => 'v_msg_data',
505 p_token2 => 'VAR_VAL',
506 p_value2 => v_msg_data
507 );
508
509 FEM_ENGINES_PKG.User_Message(
510 p_app_name => 'FEM',
511 p_msg_text => v_msg_data
512 );
513
514 RAISE FEM_INTG_warn;
515
516 END IF;
517
518 FEM_PL_PKG.Update_Request_Status(
519 p_api_version => pc_api_version,
520 p_commit => 'T',
521 p_request_id => p_req_id,
522 p_exec_status_code => p_exec_status,
523 p_user_id => p_user_id,
524 p_last_update_login => p_login_id,
525 x_msg_count => v_msg_count,
526 x_msg_data => v_msg_data,
527 x_return_status => v_return_status
528 );
529
530 IF v_msg_count = 1 THEN
531
532 FEM_ENGINES_PKG.Tech_Message(
533 p_severity => pc_log_level_statement,
534 p_module => p_module_name || '.urs',
535 p_app_name => 'FEM',
536 p_msg_name => 'FEM_GL_POST_204',
537 p_token1 => 'VAR_NAME',
538 p_value1 => 'v_msg_data',
539 p_token2 => 'VAR_VAL',
540 p_value2 => v_msg_data
541 );
542
543 FEM_ENGINES_PKG.User_Message(
544 p_app_name => 'FEM',
545 p_msg_text => v_msg_data
546 );
547
548 RAISE FEM_INTG_warn;
549
550 END IF;
551
552 COMMIT;
553
554 FEM_ENGINES_PKG.Tech_Message(
555 p_severity => pc_log_level_statement,
556 p_module => p_module_name || '.p_exec_status',
557 p_app_name => 'FEM',
558 p_msg_name => 'FEM_GL_POST_204',
559 p_token1 => 'VAR_NAME',
560 p_value1 => 'p_exec_status',
561 p_token2 => 'VAR_VAL',
562 p_value2 => p_exec_status
563 );
564
565 FEM_ENGINES_PKG.Tech_Message(
566 p_severity => pc_log_level_statement,
567 p_module => p_module_name || '.p_final_msg_name',
568 p_app_name => 'FEM',
569 p_msg_name => p_final_msg_name
570 );
571
572 FEM_ENGINES_PKG.User_Message(
573 p_app_name => 'FEM',
574 p_msg_name => p_final_msg_name
575 );
576
577 FEM_ENGINES_PKG.Tech_Message(
578 p_severity => pc_log_level_procedure,
579 p_module => p_module_name || '.end',
580 p_app_name => 'FEM',
581 p_msg_name => 'FEM_GL_POST_202',
582 p_token1 => 'FUNC_NAME',
586 );
583 p_value1 => v_func_name,
584 p_token2 => 'TIME',
585 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
587
588 x_completion_code := 0;
589
590 EXCEPTION
591
592 WHEN FEM_INTG_warn THEN
593
594 FEM_ENGINES_PKG.Tech_Message(
595 p_severity => pc_log_level_exception,
596 p_module => p_module_name || '.warn_exception',
597 p_app_name => 'FEM',
598 p_msg_name => 'FEM_INTG_FAIL_FINAL_PROC_LOG'
599 );
600
601 FEM_ENGINES_PKG.User_Message(
602 p_app_name => 'FEM',
603 p_msg_name => 'FEM_INTG_FAIL_FINAL_PROC_LOG'
604 );
605
606 x_completion_code := 1;
607
608 WHEN OTHERS THEN
609
610 ROLLBACK;
611
612 FEM_ENGINES_PKG.Tech_Message(
613 p_severity => pc_log_level_unexpected,
614 p_module => p_module_name || '.unexpected_exception',
615 p_app_name => 'FEM',
616 p_msg_name => 'FEM_GL_POST_215',
617 p_token1 => 'ERR_MSG',
618 p_value1 => SQLERRM
619 );
620
621 FEM_ENGINES_PKG.User_Message(
622 p_app_name => 'FEM',
623 p_msg_name => 'FEM_GL_POST_215',
624 p_token1 => 'ERR_MSG',
625 p_value1 => SQLERRM
626 );
627
628 FEM_ENGINES_PKG.Tech_Message(
629 p_severity => pc_log_level_procedure,
630 p_module => p_module_name || '.unexpected_exception',
631 p_app_name => 'FEM',
632 p_msg_name => 'FEM_GL_POST_203',
633 p_token1 => 'FUNC_NAME',
634 p_value1 => v_func_name,
635 p_token2 => 'TIME',
636 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
637 );
638
639 x_completion_code := 2;
640
641 END Final_Process_Logging;
642
643 END FEM_INTG_PL_PKG;