[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_EXTRACT_UTIL_PKG
Source
1 PACKAGE BODY fa_xla_extract_util_pkg AS
2 /* $Header: FAXLAXUB.pls 120.25.12010000.2 2009/01/05 07:12:37 anujain ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12
13 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_extract_util_pkg.';
14
15 ----------------------------------------------------------------------------------
16 -- Check Events
17 -- This is called at the beginning to determine the books,
18 -- entities and event types in the xla GT table.
19 -- Allows us to skip over inserts which are not needed
20 --
21 --------------------------------------------------------------------------------
22
23 PROCEDURE check_events IS
24
25 TYPE tab_varchar IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
26
27 t_book_type_code tab_varchar;
28 t_event_type_code tab_varchar;
29 t_entity_code tab_varchar;
30
31 l_last_book_used varchar2(15) := ' ';
32
33 cursor c_events is
34 select distinct valuation_method,
35 ENTITY_CODE,
36 EVENT_TYPE_CODE
37 from xla_events_gt;
38
39 l_procedure_name varchar2(80) := 'check_events';
40
41 begin
42
43 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
44
45 fnd_log.string(G_LEVEL_PROCEDURE,
46 G_MODULE_NAME||l_procedure_name||'.begin',
47 'Beginning of procedure');
48
49 END IF;
50
51 -- reset globals to false
52
53 G_trx_exists := false;
54 G_inter_trx_exists := false;
55 G_dep_exists := false;
56 G_def_exists := false;
57
58 G_fin_trx_exists := false;
59 G_xfr_trx_exists := false;
60 G_dist_trx_exists := false;
61 G_ret_trx_exists := false;
62 G_res_trx_exists := false;
63 G_deprn_exists := false;
64 G_rollback_deprn_exists := false;
65
66 G_alc_enabled := false;
67
68 open c_events;
69 fetch c_events BULK COLLECT
70 into t_book_type_code,
71 t_entity_code,
72 t_event_type_code;
73 close c_events;
74
75 for i in 1..t_book_type_code.count loop
76
77 if (t_book_type_code(i) <> l_last_book_used or
78 i = 1) then
79 if not fa_cache_pkg.fazcbc (t_book_type_code(i)) then
80 null;
81 end if;
82
83 if (nvl(fa_cache_pkg.fazcbc_record.mc_source_flag, 'N') = 'Y') then
84 G_alc_enabled := true;
85 end if;
86
87 l_last_book_used := t_book_type_code(i);
88
89 end if;
90
91 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
92 fnd_log.string(G_LEVEL_STATEMENT,
93 G_MODULE_NAME||l_procedure_name,
94 't_entity_code(i): ' || t_entity_code(i));
95 END IF;
96
97 if (t_entity_code(i) = 'TRANSACTIONS') then
98 G_trx_exists := true;
99 elsif (t_entity_code(i) = 'INTER_ASSET_TRANSACTIONS') then
100 G_inter_trx_exists := true;
101 elsif (t_entity_code(i) = 'DEPRECIATION') then
102 G_dep_exists := true;
103 elsif (t_entity_code(i) = 'DEFERRED_DEPRECIATION') then
104 G_def_exists := true;
105 else
106 null;
107 end if;
108
109 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
110 fnd_log.string(G_LEVEL_STATEMENT,
111 G_MODULE_NAME||l_procedure_name,
112 't_event_type_code(i): ' || t_event_type_code(i));
113 END IF;
114
115 if (t_event_type_code(i) in ('ADDITIONS', 'CIP_ADDITIONS',
116 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
117 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
118 'REVALUATION', 'CIP_REVALUATION',
119 'DEPRECIATION_ADJUSTMENTS',
120 'UNPLANNED_DEPRECIATION',
121 'TERMINAL_GAIN_LOSS',
122 'RETIREMENT_ADJUSTMENT')) then
123 G_fin_trx_exists := true;
124 elsif (t_event_type_code(i) in ('TRANSFERS', 'CIP_TRANSFERS')) then
125 G_xfr_trx_exists := true;
126 elsif (t_event_type_code(i) in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
127 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')) then
128 G_dist_trx_exists := true;
129 elsif (t_event_type_code(i) in ('RETIREMENTS', 'CIP_RETIREMENTS')) then
130 G_ret_trx_exists := true;
131 elsif (t_event_type_code(i) in ('REINSTATEMENTS','CIP_REINSTATEMENTS')) then
132 G_res_trx_exists := true;
133 elsif (t_event_type_code(i) = 'DEPRECIATION') then
134 G_deprn_exists := true;
135 elsif (t_event_type_code(i) = 'ROLLBACK_DEPRECIATION') then
136 G_rollback_deprn_exists := true;
137 else
138 null;
139 end if;
140 end loop;
141
142 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
143
144 fnd_log.string(G_LEVEL_PROCEDURE,
145 G_MODULE_NAME||l_procedure_name||'.end',
146 'End of procedure');
147
148 END IF;
149
150 EXCEPTION
151 WHEN others THEN
152 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
153 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
154 fnd_message.set_token('ORACLE_ERR',SQLERRM);
155 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
156 END IF;
157 raise;
158
159 end check_events;
160
161 --------------------------------------------------------------------------------
162
163
164 --------------------------------------------------------------------------------
165 --
166 -- Main Locking program
167 -- This is the stub called from the locking_status subscription routine
168 --
169 -- NOTE: only certain events/transactions need locking as most transactions
170 -- in assets can't be undone / deleted / updated
171 --
172 -- Requiring locking: Deprn, Retirements, Additions (unprocessed)
173 --
174 --------------------------------------------------------------------------------
175
176 PROCEDURE lock_assets
177 (p_book_type_code varchar2) IS
178
179 l_procedure_name varchar2(80) := 'lock_assets';
180
181 BEGIN
182
183 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
184
185 fnd_log.string(G_LEVEL_PROCEDURE,
186 G_MODULE_NAME||l_procedure_name||'.begin',
187 'Beginning of procedure');
188
189 END IF;
190
191 update fa_book_controls
192 set create_accounting_request_id = fnd_global.conc_request_id
193 where book_type_code = p_book_type_code;
194
195 commit;
196
197 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
198
199 fnd_log.string(G_LEVEL_PROCEDURE,
200 G_MODULE_NAME||l_procedure_name||'.end',
201 'End of procedure');
202
203 END IF;
204
205 return;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
210 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
211 fnd_message.set_token('ORACLE_ERR',SQLERRM);
212 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
213 END IF;
214 RAISE;
215
216 END lock_assets;
217
218 --------------------------------------------------------------------------------
219 --
220 -- Main UnLocking program
221 -- This is the stub called from the locking_status subscription routine
222 --
223 -- NOTE: only certain events/transactions need locking as most transactions
224 -- in assets can't be undone / deleted / updated
225 --
226 -- Requiring locking: Deprn, Retirements, Additions (unprocessed)
227 --
228 --------------------------------------------------------------------------------
229
230 PROCEDURE unlock_assets
231 (p_book_type_code varchar2) IS
232
233 l_procedure_name varchar2(80) := 'unlock_assets';
234
235 BEGIN
236
237 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
238
239 fnd_log.string(G_LEVEL_PROCEDURE,
240 G_MODULE_NAME||l_procedure_name||'.begin',
241 'Beginning of procedure');
242
243 END IF;
244
245 update fa_book_controls
246 set create_accounting_request_id = null
247 where book_type_code = p_book_type_code;
248
249 commit;
250
251 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
252
253 fnd_log.string(G_LEVEL_PROCEDURE,
254 G_MODULE_NAME||l_procedure_name||'.end',
255 'End of procedure');
256
257 END IF;
258
259 return;
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
264 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
265 fnd_message.set_token('ORACLE_ERR',SQLERRM);
266 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
267 END IF;
268 RAISE;
269
270 END unlock_assets;
271
272
273
274 --------------------------------------------------------------------------------
275 --
276 -- Main nonaccountable events program
277 -- This is the stub called from the preaccounting subscription routine
278 --
279 --------------------------------------------------------------------------------
280
281 PROCEDURE update_nonaccountable_events
282 (p_book_type_code varchar2,
283 p_process_category varchar2) IS
284
285 l_appl_id number := 140;
286 l_entity_code varchar2(30);
287
288 l_trx number;
289 l_trx_count number;
290 l_inter_trx_count number;
291
292 l_procedure_name varchar2(80) := 'update_nonaccountable_events';
293
294 BEGIN
295
296 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
297
298 fnd_log.string(G_LEVEL_PROCEDURE,
299 G_MODULE_NAME||l_procedure_name||'.begin',
300 'Beginning of procedure');
301
302 END IF;
303
304 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
305
306 fnd_log.string(G_LEVEL_STATEMENT,
307 G_MODULE_NAME||l_procedure_name,
308 'p_book_type_code: ' || p_book_type_code);
309
310 fnd_log.string(G_LEVEL_STATEMENT,
311 G_MODULE_NAME||l_procedure_name,
312 'p_process_category: ' || p_process_category);
313
314
315 END IF;
316
317 BEGIN
318
319 select 1
320 into l_trx
321 from dual
322 where exists(
323 select 1
324 from xla_event_class_attrs
325 where application_id = 140
326 and entity_code in ('TRANSACTIONS', 'INTER_ASSET_TRANSACTIONS')
327 and EVENT_CLASS_GROUP_CODE = nvl(p_process_category,
328 EVENT_CLASS_GROUP_CODE));
329
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 l_trx := 0;
333 WHEN OTHERS THEN
334 l_trx := 0;
335 END;
336
337 if (l_trx <> 0) then
338 -- BUG# 4439932
339 -- the following is for setting status on non-accountable events
340
341 insert into xla_events_int_gt
342 (event_id,
343 event_status_code,
344 application_id,
345 ledger_id,
346 entity_code,
347 valuation_method)
348 select /*+ leading(EV,TE) use_nl(EV TE TH BC) */
349 ev.event_id,
350 'N',
351 140,
352 bc.set_of_books_id,
353 'TRANSACTIONS',
354 bc.book_type_code
355 from xla_transaction_entities te,
356 xla_events ev,
357 fa_transaction_headers th,
358 fa_book_controls bc
359 where te.application_id = l_appl_id
360 and te.ledger_id = bc.set_of_books_id
361 and te.entity_code = 'TRANSACTIONS'
362 and te.valuation_method = p_book_type_code
363 and ev.application_id = l_appl_id
364 and ev.process_status_code <> 'P'
365 and ev.entity_id = te.entity_id
366 and ev.request_id = fnd_global.conc_request_id --bug# 7642903
367 and ev.event_type_code in
368 ('ADDITIONS',
369 'ADJUSTMENTS',
370 'CAPITALIZATION',
371 'REVERSE_CAPITALIZATION',
372 'CATEGORY_RECLASS',
373 'CIP_ADDITIONS',
374 'CIP_ADJUSTMENTS',
375 'CIP_CATEGORY_RECLASS',
376 'CIP_REINSTATEMENTS',
377 'CIP_RETIREMENTS',
378 'CIP_REVALUATION',
379 'CIP_TRANSFERS',
380 'CIP_UNIT_ADJUSTMENTS',
381 'DEPRECIATION_ADJUSTMENTS',
382 'REINSTATEMENTS',
383 'RETIREMENTS',
384 'RETIREMENT_ADJUSTMENTS',
385 'REVALUATION',
386 'ROLLBACK_TERMINAL_GAIN_LOSS',
387 'TERMINAL_GAIN_LOSS',
388 'TRANSFERS',
389 'UNIT_ADJUSTMENTS',
390 'UNPLANNED_DEPRECIATION')
391 and th.transaction_header_id = te.source_id_int_1
392 and bc.book_type_code = p_book_type_code
393 and not exists
394 (select /*+ no_unnest */ 1
395 from fa_adjustments adj
396 where adj.transaction_header_id = th.transaction_header_id
397 and adj.book_type_code = bc.book_type_code
398 and adj.adjustment_amount <> 0
399 and nvl(adj.track_member_flag, 'N') <> 'Y')
400 and not exists
401 (select /*+ no_unnest index(adj FA_ADJUSTMENTS_U1) */ 1
402 from fa_adjustments adj,
403 fa_transaction_headers th2
404 where th2.member_transaction_header_id = th.transaction_header_id
405 and adj.transaction_header_id = th2.transaction_header_id
406 and adj.book_type_code = bc.book_type_code
407 and adj.adjustment_amount <> 0
408 and nvl(adj.track_member_flag, 'N') <> 'Y');
409
410 l_trx_count := SQL%ROWCOUNT;
411
412 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
413 fnd_log.string(G_LEVEL_STATEMENT,
414 G_MODULE_NAME||l_procedure_name,
415 'Rows inserted into gt for non-accountable - trx: ' || to_char(l_trx_count));
416 END IF;
417 /*--------------------------------------------------------*/
418 -- Bug 7642903. A new condition based on create accounting
419 -- request id, is added in the query torun it fast.
420 -- An index exists in Xla_events table on request_id field.
421 /*--------------------------------------------------------*/
422 insert into xla_events_int_gt
423 (event_id,
424 event_status_code,
425 application_id,
426 ledger_id,
427 entity_code,
428 valuation_method)
429 select /*+ leading(EV,TE) use_nl(EV TE TRX BC) */
430 ev.event_id,
431 'N',
432 140,
433 bc.set_of_books_id,
434 'INTER_ASSET_TRANSACTIONS',
435 bc.book_type_code
436 from xla_events ev,
437 xla_transaction_entities te,
438 fa_trx_references trx,
439 fa_book_controls bc
440 where te.application_id = l_appl_id
441 and te.ledger_id = bc.set_of_books_id
442 and te.entity_code = 'INTER_ASSET_TRANSACTIONS'
443 and te.valuation_method = p_book_type_code
444 and ev.application_id = l_appl_id
445 and ev.process_status_code <> 'P'
446 and ev.entity_id = te.entity_id
447 and ev.request_id = fnd_global.conc_request_id --bug# 7642903
448 and ev.event_type_code in
449 ('CIP_SOURCE_LINE_TRANSFERS',
450 'SOURCE_LINE_TRANSFERS',
451 'RESERVE_TRANSFERS')
452 and trx.trx_reference_id = te.source_id_int_1
453 and bc.book_type_code = p_book_type_code
454 and not exists
455 (select /*+ no_unnest */ 1
456 from fa_adjustments adj
457 where adj.transaction_header_id in
458 (trx.src_transaction_header_id, trx.dest_transaction_header_id)
459 and adj.book_type_code = bc.book_type_code
460 and adj.adjustment_amount <> 0
461 and nvl(adj.track_member_flag, 'N') <> 'Y')
462 and not exists
463 (select /*+ no_unnest index(adj FA_ADJUSTMENTS_U1) */ 1
464 from fa_adjustments adj,
465 fa_transaction_headers th2
466 where th2.member_transaction_header_id in
467 (trx.src_transaction_header_id, trx.dest_transaction_header_id)
468 and adj.transaction_header_id = th2.transaction_header_id
469 and adj.book_type_code = bc.book_type_code
470 and adj.adjustment_amount <> 0
471 and nvl(adj.track_member_flag, 'N') <> 'Y');
472
473
474 l_inter_trx_count := SQL%ROWCOUNT;
475
476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
477 fnd_log.string(G_LEVEL_STATEMENT,
478 G_MODULE_NAME||l_procedure_name,
479 'Rows inserted into gt for non-accountable - intertrx: ' || to_char(l_inter_trx_count));
480 END IF;
481
482
483 if (l_trx_count <> 0 or
484 l_inter_trx_count <> 0) then
485 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
486 fnd_log.string(G_LEVEL_STATEMENT,
487 G_MODULE_NAME||l_procedure_name,
488 'calling: ' || 'xla_events_pub_pkg.update_bulk_event_statuses');
489 END IF;
490
491
492 xla_events_pub_pkg.update_bulk_event_statuses(p_application_id => 140);
493 end if;
494
495 end if;
496
497 commit;
498
499 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
500
501 fnd_log.string(G_LEVEL_PROCEDURE,
502 G_MODULE_NAME||l_procedure_name||'.end',
503 'End of procedure');
504
505 END IF;
506
507 return;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
512 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
513 fnd_message.set_token('ORACLE_ERR',SQLERRM);
514 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
515 END IF;
516 RAISE;
517
518 END update_nonaccountable_events;
519
520
521 --------------------------------------------------------------------------------
522 --
523 -- Main Extraction program
524 -- This is the stub called from the extract_status subscription routine
525 --
526 -- NOTE: the accounting programs will be the one determein which
527 -- event classes make it into the temp table. Thus
528 -- this will always run for all transactions types,
529 -- it just may not find any matches for some.
530 --
531 --------------------------------------------------------------------------------
532
533 PROCEDURE extract(p_accounting_mode IN VARCHAR2) IS
534
535 l_stmt_deprn varchar2(1000) :=
536 'BEGIN fa_xla_extract_deprn_pkg.load_data; END;';
537 l_stmt_def varchar2(1000) :=
538 'BEGIN fa_xla_extract_def_pkg.load_data; END;';
539 l_stmt_trx varchar2(1000) :=
540 'BEGIN fa_xla_extract_trx_pkg.load_data; END;';
541
542 l_procedure_name varchar2(80) := 'extract';
543
544 BEGIN
545
546 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
547
548 fnd_log.string(G_LEVEL_PROCEDURE,
549 G_MODULE_NAME||l_procedure_name||'.begin',
550 'Beginning of procedure');
551
552 END IF;
553
554 -- check what entities, types and books exist in the GT table
555 check_events;
556
557 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
558
559 if (G_trx_exists) then
560 fnd_log.string(G_LEVEL_STATEMENT,
561 G_MODULE_NAME||l_procedure_name,
562 'G_trx_exists: true' );
563 else
564 fnd_log.string(G_LEVEL_STATEMENT,
565 G_MODULE_NAME||l_procedure_name,
566 'G_trx_exists: false' );
567
568 end if;
569
570 if (G_inter_trx_exists) then
571 fnd_log.string(G_LEVEL_STATEMENT,
572 G_MODULE_NAME||l_procedure_name,
573 'G_inter_trx_exists: true' );
574 else
575 fnd_log.string(G_LEVEL_STATEMENT,
576 G_MODULE_NAME||l_procedure_name,
577 'G_inter_trx_exists: false' );
578
579 end if;
580
581 if (G_dep_exists) then
582 fnd_log.string(G_LEVEL_STATEMENT,
583 G_MODULE_NAME||l_procedure_name,
584 'G_dep_exists: true' );
585 else
586 fnd_log.string(G_LEVEL_STATEMENT,
587 G_MODULE_NAME||l_procedure_name,
588 'G_dep_exists: false' );
589
590 end if;
591
592 if (G_def_exists) then
593 fnd_log.string(G_LEVEL_STATEMENT,
594 G_MODULE_NAME||l_procedure_name,
595 'G_def_exists: true' );
596 else
597 fnd_log.string(G_LEVEL_STATEMENT,
598 G_MODULE_NAME||l_procedure_name,
599 'G_def_exists: false' );
600
601 end if;
602
603 if (G_fin_trx_exists) then
604 fnd_log.string(G_LEVEL_STATEMENT,
605 G_MODULE_NAME||l_procedure_name,
606 'G_fin_trx_exists: true' );
607 else
608 fnd_log.string(G_LEVEL_STATEMENT,
609 G_MODULE_NAME||l_procedure_name,
610 'G_fin_trx_exists: false' );
611 end if;
612
613 if (G_xfr_trx_exists) then
614 fnd_log.string(G_LEVEL_STATEMENT,
615 G_MODULE_NAME||l_procedure_name,
616 'G_xfr_trx_exists: true' );
617 else
618 fnd_log.string(G_LEVEL_STATEMENT,
619 G_MODULE_NAME||l_procedure_name,
620 'G_xfr_trx_exists: false' );
621 end if;
622
623 if (G_dist_trx_exists) then
624 fnd_log.string(G_LEVEL_STATEMENT,
625 G_MODULE_NAME||l_procedure_name,
626 'G_dist_trx_exists: true' );
627 else
628 fnd_log.string(G_LEVEL_STATEMENT,
629 G_MODULE_NAME||l_procedure_name,
630 'G_dist_trx_exists: false' );
631 end if;
632
633 if (G_ret_trx_exists) then
634 fnd_log.string(G_LEVEL_STATEMENT,
635 G_MODULE_NAME||l_procedure_name,
636 'G_ret_trx_exists: true' );
637 else
638 fnd_log.string(G_LEVEL_STATEMENT,
639 G_MODULE_NAME||l_procedure_name,
640 'G_ret_trx_exists: false' );
641 end if;
642
643 if (G_res_trx_exists) then
644 fnd_log.string(G_LEVEL_STATEMENT,
645 G_MODULE_NAME||l_procedure_name,
646 'G_res_trx_exists: true' );
647 else
648 fnd_log.string(G_LEVEL_STATEMENT,
649 G_MODULE_NAME||l_procedure_name,
650 'G_res_trx_exists: false' );
651 end if;
652
653 if (G_deprn_exists) then
654 fnd_log.string(G_LEVEL_STATEMENT,
655 G_MODULE_NAME||l_procedure_name,
656 'G_deprn_exists: true' );
657 else
658 fnd_log.string(G_LEVEL_STATEMENT,
659 G_MODULE_NAME||l_procedure_name,
660 'G_deprn_exists: false' );
661 end if;
662
663 if (G_rollback_deprn_exists) then
664 fnd_log.string(G_LEVEL_STATEMENT,
665 G_MODULE_NAME||l_procedure_name,
666 'G_rollback_deprn_exists: true' );
667 else
668 fnd_log.string(G_LEVEL_STATEMENT,
669 G_MODULE_NAME||l_procedure_name,
670 'G_rollback_deprn_exists: false' );
671 end if;
672
673 if (G_alc_enabled) then
674 fnd_log.string(G_LEVEL_STATEMENT,
675 G_MODULE_NAME||l_procedure_name,
676 'G_alc_enabled: true' );
677 else
678 fnd_log.string(G_LEVEL_STATEMENT,
679 G_MODULE_NAME||l_procedure_name,
680 'G_alc_enabled: false' );
681 end if;
682
683 END IF;
684
685 -- process trx level first (common for all trx level events)
686
687 if (G_trx_exists or G_inter_trx_exists) then
688 EXECUTE IMMEDIATE l_stmt_trx;
689 end if;
690
691 if (G_dep_exists) then
692 EXECUTE IMMEDIATE l_stmt_deprn;
693 end if;
694
695 if (G_def_exists) then
696 EXECUTE IMMEDIATE l_stmt_def;
697 end if;
698
699 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
700
701 fnd_log.string(G_LEVEL_PROCEDURE,
702 G_MODULE_NAME||l_procedure_name||'.end',
703 'End of procedure');
704
705 END IF;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
710 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
711 fnd_message.set_token('ORACLE_ERR',SQLERRM);
712 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
713 END IF;
714 RAISE;
715 END;
716
717 --------------------------------------------------------------------------------
718
719 END fa_xla_extract_util_pkg;