[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.36.12020000.3 2012/11/30 11:52:10 spooyath 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 FA_BOOK_CONTROLS.Book_Type_Code%TYPE := ' ';
32 l_special_secondary_count number := 0;
33
34 cursor c_events is
35 select distinct valuation_method,
36 ENTITY_CODE,
37 EVENT_TYPE_CODE
38 from xla_events_gt;
39
40 l_procedure_name varchar2(80) := 'check_events';
41 l_corp_book_rec FA_BOOK_CONTROLS%RowType;
42
43 begin
44
45 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
46
47 fnd_log.string(G_LEVEL_PROCEDURE,
48 G_MODULE_NAME||l_procedure_name||'.begin',
49 'Beginning of procedure');
50
51 END IF;
52
53 -- reset globals to false
54
55 G_trx_exists := false;
56 G_inter_trx_exists := false;
57 G_dep_exists := false;
58 G_def_exists := false;
59
60 G_fin_trx_exists := false;
61 G_xfr_trx_exists := false;
62 G_dist_trx_exists := false;
63 G_ret_trx_exists := false;
64 G_res_trx_exists := false;
65 G_deprn_exists := false;
66 G_rollback_deprn_exists := false;
67
68 G_alc_enabled := false;
69 G_group_enabled := false;
70 G_sorp_enabled := false;
71 G_secondary_special := false;
72
73 open c_events;
74 fetch c_events BULK COLLECT
75 into t_book_type_code,
76 t_entity_code,
77 t_event_type_code;
78 close c_events;
79
80 for i in 1..t_book_type_code.count loop
81
82 if (t_book_type_code(i) <> l_last_book_used or
83 i = 1) then
84 if not fa_cache_pkg.fazcbc (t_book_type_code(i)) then
85 null;
86 end if;
87
88 if (nvl(fa_cache_pkg.fazcbc_record.mc_source_flag, 'N') = 'Y') then
89 G_alc_enabled := true;
90 end if;
91
92 if (nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y') then
93 G_group_enabled := true;
94 end if;
95
96 if (nvl(fa_cache_pkg.fazcbc_record.sorp_enabled_flag, 'N') = 'Y') then
97 G_sorp_enabled := true;
98 end if;
99
100 -- Secondary Changes start
101 if (fa_cache_pkg.fazcbc_record.book_class = 'TAX') then
102
103 -- call cache for corp
104 if not fa_cache_pkg.fazcbc (fa_cache_pkg.fazcbc_record.distribution_source_book) then
105 null;
106 end if;
107
108 l_corp_book_rec := fa_cache_pkg.fazcbc_record;
109
110 -- reset cache to tax
111 if not fa_cache_pkg.fazcbc (t_book_type_code(i)) then
112 null;
113 end if;
114 if (fa_cache_pkg.fazcbc_record.set_of_books_id = l_corp_book_rec.set_of_books_id) then
115
116 l_special_secondary_count := 0;
117
118 begin
119
120 select 1
121 into l_special_secondary_count
122 from dual
123 where exists
124 (select 1
125 from fa_mc_book_controls mcbc,
126 gl_ledgers gl
127 where mcbc.book_type_code = t_book_type_code(i)
128 and gl.ledger_id = mcbc.set_of_books_id
129 and gl.LEDGER_CATEGORY_CODE = 'SECONDARY');
130
131 exception
132 when others then
133 null;
134 end;
135
136 if (l_special_secondary_count > 0) then
137 G_secondary_special := true;
138 end if;
139
140 end if;
141 end if;
142
143 --Michelin Changes Ends
144
145 l_last_book_used := t_book_type_code(i);
146
147 end if;
148
149 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
150 fnd_log.string(G_LEVEL_STATEMENT,
151 G_MODULE_NAME||l_procedure_name,
152 't_entity_code(i): ' || t_entity_code(i));
153 END IF;
154
155 if (t_entity_code(i) = 'TRANSACTIONS') then
156 G_trx_exists := true;
157 elsif (t_entity_code(i) = 'INTER_ASSET_TRANSACTIONS') then
158 G_inter_trx_exists := true;
159 elsif (t_entity_code(i) = 'DEPRECIATION') then
160 G_dep_exists := true;
161 elsif (t_entity_code(i) = 'DEFERRED_DEPRECIATION') then
162 G_def_exists := true;
163 else
164 null;
165 end if;
166
167 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
168 fnd_log.string(G_LEVEL_STATEMENT,
169 G_MODULE_NAME||l_procedure_name,
170 't_event_type_code(i): ' || t_event_type_code(i));
171 END IF;
172
173 if (t_event_type_code(i) in ('ADDITIONS', 'CIP_ADDITIONS',
174 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
175 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
176 'REVALUATION', 'CIP_REVALUATION',
177 'ROLLBACK_REVALUATION', 'CIP_ROLLBACK_REVALUATION',
178 'DEPRECIATION_ADJUSTMENTS',
179 'UNPLANNED_DEPRECIATION',
180 'TERMINAL_GAIN_LOSS',
181 'RETIREMENT_ADJUSTMENTS',
182 'IMPAIRMENT','ROLLBACK_IMPAIRMENT')) then
183 G_fin_trx_exists := true;
184 elsif (t_event_type_code(i) in ('TRANSFERS', 'CIP_TRANSFERS')) then
185 G_xfr_trx_exists := true;
186 elsif (t_event_type_code(i) in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
187 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')) then
188 G_dist_trx_exists := true;
189 elsif (t_event_type_code(i) in ('RETIREMENTS', 'CIP_RETIREMENTS')) then
190 G_ret_trx_exists := true;
191 elsif (t_event_type_code(i) in ('REINSTATEMENTS','CIP_REINSTATEMENTS')) then
192 G_res_trx_exists := true;
193 elsif (t_event_type_code(i) = 'DEPRECIATION') then
194 G_deprn_exists := true;
195 elsif (t_event_type_code(i) = 'ROLLBACK_DEPRECIATION') then
196 G_rollback_deprn_exists := true;
197 else
198 null;
199 end if;
200 end loop;
201
202 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
203
204 fnd_log.string(G_LEVEL_PROCEDURE,
205 G_MODULE_NAME||l_procedure_name||'.end',
206 'End of procedure');
207
208 END IF;
209
210 EXCEPTION
211 WHEN others THEN
212 IF c_events%ISOPEN THEN
213 close c_events;
214 END IF;
215
216 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
217 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
218 fnd_message.set_token('ORACLE_ERR',SQLERRM);
219 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
220 END IF;
221 raise;
222
223 end check_events;
224
225 --------------------------------------------------------------------------------
226
227
228 --------------------------------------------------------------------------------
229 --
230 -- Main Locking program
231 -- This is the stub called from the locking_status subscription routine
232 --
233 -- NOTE: only certain events/transactions need locking as most transactions
234 -- in assets can't be undone / deleted / updated
235 --
236 -- Requiring locking: Deprn, Retirements, Additions (unprocessed)
237 --
238 --------------------------------------------------------------------------------
239
240 PROCEDURE lock_assets
241 (p_book_type_code varchar2,
242 p_ledger_id number) IS
243
244 l_procedure_name varchar2(80) := 'lock_assets';
245
246 BEGIN
247
248 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
249
250 fnd_log.string(G_LEVEL_PROCEDURE,
251 G_MODULE_NAME||l_procedure_name||'.begin',
252 'Beginning of procedure');
253
254 END IF;
255
256 if (p_book_type_code is null) then
257 update fa_book_controls
258 set create_accounting_request_id = fnd_global.conc_request_id
259 where set_of_books_id = p_ledger_id
260 and book_class <> 'BUDGET';
261 else
262 update fa_book_controls
263 set create_accounting_request_id = fnd_global.conc_request_id
264 where book_type_code = p_book_type_code;
265 end if;
266
267 commit;
268
269 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
270
271 fnd_log.string(G_LEVEL_PROCEDURE,
272 G_MODULE_NAME||l_procedure_name||'.end',
273 'End of procedure');
274
275 END IF;
276
277 return;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
282 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
283 fnd_message.set_token('ORACLE_ERR',SQLERRM);
284 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
285 END IF;
286 RAISE;
287
288 END lock_assets;
289
290 --------------------------------------------------------------------------------
291 --
292 -- Main UnLocking program
293 -- This is the stub called from the locking_status subscription routine
294 --
295 -- NOTE: only certain events/transactions need locking as most transactions
296 -- in assets can't be undone / deleted / updated
297 --
298 -- Requiring locking: Deprn, Retirements, Additions (unprocessed)
299 --
300 --------------------------------------------------------------------------------
301
302 PROCEDURE unlock_assets
303 (p_book_type_code varchar2,
304 p_ledger_id number) IS
305
306 l_procedure_name varchar2(80) := 'unlock_assets';
307
308 BEGIN
309
310 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
311
312 fnd_log.string(G_LEVEL_PROCEDURE,
313 G_MODULE_NAME||l_procedure_name||'.begin',
314 'Beginning of procedure');
315
316 END IF;
317
318 if (p_book_type_code is null) then
319 update fa_book_controls
320 set create_accounting_request_id = null
321 where set_of_books_id = p_ledger_id
322 and book_class <> 'BUDGET';
323 else
324 update fa_book_controls
325 set create_accounting_request_id = null
326 where book_type_code = p_book_type_code;
327 end if;
328
329 commit;
330
331 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
332
333 fnd_log.string(G_LEVEL_PROCEDURE,
334 G_MODULE_NAME||l_procedure_name||'.end',
335 'End of procedure');
336
337 END IF;
338
339 return;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
344 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
345 fnd_message.set_token('ORACLE_ERR',SQLERRM);
346 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
347 END IF;
348 RAISE;
349
350 END unlock_assets;
351
352
353
354 --------------------------------------------------------------------------------
355 --
356 -- Main nonaccountable events program
357 -- This is the stub called from the preaccounting subscription routine
358 --
359 --------------------------------------------------------------------------------
360
361 PROCEDURE update_nonaccountable_events
362 (p_book_type_code varchar2,
363 p_process_category varchar2,
364 p_ledger_id number) IS
365
366 l_appl_id number := 140;
367 l_entity_code varchar2(30);
368
369 l_trx number;
370 l_trx_count number;
371 l_inter_trx_count number;
372
373 l_procedure_name varchar2(80) := 'update_nonaccountable_events';
374
375 BEGIN
376
377 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
378
379 fnd_log.string(G_LEVEL_PROCEDURE,
380 G_MODULE_NAME||l_procedure_name||'.begin',
381 'Beginning of procedure');
382
383 END IF;
384
385 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
386
387 fnd_log.string(G_LEVEL_STATEMENT,
388 G_MODULE_NAME||l_procedure_name,
389 'p_book_type_code: ' || p_book_type_code);
390 fnd_log.string(G_LEVEL_STATEMENT,
391 G_MODULE_NAME||l_procedure_name,
392 'p_ledger_id: ' || to_char(p_ledger_id));
393 fnd_log.string(G_LEVEL_STATEMENT,
394 G_MODULE_NAME||l_procedure_name,
395 'p_process_category: ' || p_process_category);
396
397 END IF;
398
399 BEGIN
400
401 select 1
402 into l_trx
403 from dual
404 where exists(
405 select 1
406 from xla_event_class_attrs
407 where application_id = 140
408 and entity_code in ('TRANSACTIONS', 'INTER_ASSET_TRANSACTIONS')
409 and EVENT_CLASS_GROUP_CODE = nvl(p_process_category,
410 EVENT_CLASS_GROUP_CODE));
411
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 l_trx := 0;
415 WHEN OTHERS THEN
416 l_trx := 0;
417 END;
418
419 if (l_trx <> 0) then
420 -- BUG# 4439932
421 -- the following is for setting status on non-accountable events
422 -- Bug 10321435 : Excluded IAC events
423 -- Bug 13601181 : Added check for group assets
424
425 if (G_group_enabled) then
426
427 insert into xla_events_int_gt
428 (event_id,
429 event_status_code,
430 application_id,
431 ledger_id,
432 entity_code,
433 valuation_method)
434 select /*+ leading(EV,TE) use_nl(EV TE TH BC) */
435 ev.event_id,
436 'N',
437 140,
438 bc.set_of_books_id,
439 'TRANSACTIONS',
440 bc.book_type_code
441 from xla_transaction_entities te,
442 xla_events ev,
443 fa_transaction_headers th,
444 fa_book_controls bc
445 where te.application_id = l_appl_id
446 and te.ledger_id = p_ledger_id
447 and te.entity_code = 'TRANSACTIONS'
448 and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
449 and ev.application_id = l_appl_id
450 and ev.process_status_code in ('U','I','E')
451 and ev.event_status_code = 'U'
452 and ev.event_type_code <> 'INFLATION_REVALUATION'
453 and ev.entity_id = te.entity_id
454 and th.transaction_header_id = te.source_id_int_1
455 and bc.book_type_code = te.source_id_char_1
456 and bc.set_of_books_id = te.ledger_id
457 and not exists
458 (select /*+ no_unnest */ 1
459 from fa_adjustments adj
460 where adj.transaction_header_id = th.transaction_header_id
461 and adj.book_type_code = bc.book_type_code
462 and adj.adjustment_amount <> 0
463 and nvl(adj.track_member_flag, 'N') <> 'Y')
464 and not exists
465 (select /*+ no_unnest leading(th2) index(adj FA_ADJUSTMENTS_U1) */ 1
466 from fa_adjustments adj,
467 fa_transaction_headers th2
468 where th2.member_transaction_header_id = th.transaction_header_id
469 and adj.transaction_header_id = th2.transaction_header_id
470 and adj.book_type_code = bc.book_type_code
471 and adj.adjustment_amount <> 0
472 and nvl(adj.track_member_flag, 'N') <> 'Y');
473
474 else
475
476 insert into xla_events_int_gt
477 (event_id,
478 event_status_code,
479 application_id,
480 ledger_id,
481 entity_code,
482 valuation_method)
483 select /*+ leading(EV,TE) use_nl(EV TE TH BC) */
484 ev.event_id,
485 'N',
486 140,
487 bc.set_of_books_id,
488 'TRANSACTIONS',
489 bc.book_type_code
490 from xla_transaction_entities te,
491 xla_events ev,
492 fa_transaction_headers th,
493 fa_book_controls bc
494 where te.application_id = l_appl_id
495 and te.ledger_id = p_ledger_id
496 and te.entity_code = 'TRANSACTIONS'
497 and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
498 and ev.application_id = l_appl_id
499 and ev.process_status_code in ('U','I','E')
500 and ev.event_status_code = 'U'
501 and ev.event_type_code <> 'INFLATION_REVALUATION'
502 and ev.entity_id = te.entity_id
503 and th.transaction_header_id = te.source_id_int_1
504 and bc.book_type_code = te.source_id_char_1
505 and bc.set_of_books_id = te.ledger_id
506 and not exists
507 (select /*+ no_unnest */ 1
508 from fa_adjustments adj
509 where adj.transaction_header_id = th.transaction_header_id
510 and adj.book_type_code = bc.book_type_code
511 and adj.adjustment_amount <> 0
512 and nvl(adj.track_member_flag, 'N') <> 'Y');
513 end if;
514
515 l_trx_count := SQL%ROWCOUNT;
516
517 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
518 fnd_log.string(G_LEVEL_STATEMENT,
519 G_MODULE_NAME||l_procedure_name,
520 'Rows inserted into gt for non-accountable - trx: ' || to_char(l_trx_count));
521 END IF;
522
523 insert into xla_events_int_gt
524 (event_id,
525 event_status_code,
526 application_id,
527 ledger_id,
528 entity_code,
529 valuation_method)
530 select /*+ leading(EV,TE) use_nl(EV TE TRX BC) */
531 ev.event_id,
532 'N',
533 140,
534 bc.set_of_books_id,
535 'INTER_ASSET_TRANSACTIONS',
536 bc.book_type_code
537 from xla_events ev,
538 xla_transaction_entities te,
539 fa_trx_references trx,
540 fa_book_controls bc
541 where te.application_id = l_appl_id
542 and te.ledger_id = p_ledger_id
543 and te.entity_code = 'INTER_ASSET_TRANSACTIONS'
544 and te.valuation_method = nvl(p_book_type_code, te.valuation_method)
545 and ev.application_id = l_appl_id
546 and ev.process_status_code in ('U','I','E')
547 and ev.event_status_code = 'U'
548 and ev.entity_id = te.entity_id
549 and trx.trx_reference_id = te.source_id_int_1
550 and bc.book_type_code = te.source_id_char_1
551 and bc.set_of_books_id = te.ledger_id
552 and not exists
553 (select /*+ no_unnest */ 1
554 from fa_adjustments adj
555 where adj.transaction_header_id in
556 (trx.src_transaction_header_id, trx.dest_transaction_header_id)
557 and adj.book_type_code = bc.book_type_code
558 and adj.adjustment_amount <> 0
559 and nvl(adj.track_member_flag, 'N') <> 'Y')
560 and not exists
561 (select /*+ no_unnest leading(th2) index(adj FA_ADJUSTMENTS_U1) */ 1
562 from fa_adjustments adj,
563 fa_transaction_headers th2
564 where th2.member_transaction_header_id in
565 (trx.src_transaction_header_id, trx.dest_transaction_header_id)
566 and adj.transaction_header_id = th2.transaction_header_id
567 and adj.book_type_code = bc.book_type_code
568 and adj.adjustment_amount <> 0
569 and nvl(adj.track_member_flag, 'N') <> 'Y');
570
571 l_inter_trx_count := SQL%ROWCOUNT;
572
573 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
574 fnd_log.string(G_LEVEL_STATEMENT,
575 G_MODULE_NAME||l_procedure_name,
576 'Rows inserted into gt for non-accountable - intertrx: ' || to_char(l_inter_trx_count));
577 END IF;
578
579 if (l_trx_count <> 0 or
580 l_inter_trx_count <> 0) then
581 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
582 fnd_log.string(G_LEVEL_STATEMENT,
583 G_MODULE_NAME||l_procedure_name,
584 'calling: ' || 'xla_events_pub_pkg.update_bulk_event_statuses');
585 END IF;
586
587 xla_events_pub_pkg.update_bulk_event_statuses(p_application_id => 140);
588 end if;
589
590 end if;
591
592 commit;
593
594 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
595
596 fnd_log.string(G_LEVEL_PROCEDURE,
597 G_MODULE_NAME||l_procedure_name||'.end',
598 'End of procedure');
599
600 END IF;
601
602 return;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
607 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
608 fnd_message.set_token('ORACLE_ERR',SQLERRM);
609 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
610 END IF;
611 RAISE;
612
613 END update_nonaccountable_events;
614
615
616 --------------------------------------------------------------------------------
617 --
618 -- Main Extraction program
619 -- This is the stub called from the extract_status subscription routine
620 --
621 -- NOTE: the accounting programs will be the one determein which
622 -- event classes make it into the temp table. Thus
623 -- this will always run for all transactions types,
624 -- it just may not find any matches for some.
625 --
626 --------------------------------------------------------------------------------
627
628 PROCEDURE extract(p_accounting_mode IN VARCHAR2) IS
629
630 l_stmt_deprn varchar2(1000) :=
631 'BEGIN fa_xla_extract_deprn_pkg.load_data; END;';
632 l_stmt_def varchar2(1000) :=
633 'BEGIN fa_xla_extract_def_pkg.load_data; END;';
634 l_stmt_trx varchar2(1000) :=
635 'BEGIN fa_xla_extract_trx_pkg.load_data; END;';
636
637 l_procedure_name varchar2(80) := 'extract';
638
639 BEGIN
640
641 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
642
643 fnd_log.string(G_LEVEL_PROCEDURE,
644 G_MODULE_NAME||l_procedure_name||'.begin',
645 'Beginning of procedure');
646
647 END IF;
648
649 -- check what entities, types and books exist in the GT table
650 check_events;
651
652 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
653
654 if (G_trx_exists) then
655 fnd_log.string(G_LEVEL_STATEMENT,
656 G_MODULE_NAME||l_procedure_name,
657 'G_trx_exists: true' );
658 else
659 fnd_log.string(G_LEVEL_STATEMENT,
660 G_MODULE_NAME||l_procedure_name,
661 'G_trx_exists: false' );
662
663 end if;
664
665 if (G_inter_trx_exists) then
666 fnd_log.string(G_LEVEL_STATEMENT,
667 G_MODULE_NAME||l_procedure_name,
668 'G_inter_trx_exists: true' );
669 else
670 fnd_log.string(G_LEVEL_STATEMENT,
671 G_MODULE_NAME||l_procedure_name,
672 'G_inter_trx_exists: false' );
673
674 end if;
675
676 if (G_dep_exists) then
677 fnd_log.string(G_LEVEL_STATEMENT,
678 G_MODULE_NAME||l_procedure_name,
679 'G_dep_exists: true' );
680 else
681 fnd_log.string(G_LEVEL_STATEMENT,
682 G_MODULE_NAME||l_procedure_name,
683 'G_dep_exists: false' );
684
685 end if;
686
687 if (G_def_exists) then
688 fnd_log.string(G_LEVEL_STATEMENT,
689 G_MODULE_NAME||l_procedure_name,
690 'G_def_exists: true' );
691 else
692 fnd_log.string(G_LEVEL_STATEMENT,
693 G_MODULE_NAME||l_procedure_name,
694 'G_def_exists: false' );
695
696 end if;
697
698 if (G_fin_trx_exists) then
699 fnd_log.string(G_LEVEL_STATEMENT,
700 G_MODULE_NAME||l_procedure_name,
701 'G_fin_trx_exists: true' );
702 else
703 fnd_log.string(G_LEVEL_STATEMENT,
704 G_MODULE_NAME||l_procedure_name,
705 'G_fin_trx_exists: false' );
706 end if;
707
708 if (G_xfr_trx_exists) then
709 fnd_log.string(G_LEVEL_STATEMENT,
710 G_MODULE_NAME||l_procedure_name,
711 'G_xfr_trx_exists: true' );
712 else
713 fnd_log.string(G_LEVEL_STATEMENT,
714 G_MODULE_NAME||l_procedure_name,
715 'G_xfr_trx_exists: false' );
716 end if;
717
718 if (G_dist_trx_exists) then
719 fnd_log.string(G_LEVEL_STATEMENT,
720 G_MODULE_NAME||l_procedure_name,
721 'G_dist_trx_exists: true' );
722 else
723 fnd_log.string(G_LEVEL_STATEMENT,
724 G_MODULE_NAME||l_procedure_name,
725 'G_dist_trx_exists: false' );
726 end if;
727
728 if (G_ret_trx_exists) then
729 fnd_log.string(G_LEVEL_STATEMENT,
730 G_MODULE_NAME||l_procedure_name,
731 'G_ret_trx_exists: true' );
732 else
733 fnd_log.string(G_LEVEL_STATEMENT,
734 G_MODULE_NAME||l_procedure_name,
735 'G_ret_trx_exists: false' );
736 end if;
737
738 if (G_res_trx_exists) then
739 fnd_log.string(G_LEVEL_STATEMENT,
740 G_MODULE_NAME||l_procedure_name,
741 'G_res_trx_exists: true' );
742 else
743 fnd_log.string(G_LEVEL_STATEMENT,
744 G_MODULE_NAME||l_procedure_name,
745 'G_res_trx_exists: false' );
746 end if;
747
748 if (G_deprn_exists) then
749 fnd_log.string(G_LEVEL_STATEMENT,
750 G_MODULE_NAME||l_procedure_name,
751 'G_deprn_exists: true' );
752 else
753 fnd_log.string(G_LEVEL_STATEMENT,
754 G_MODULE_NAME||l_procedure_name,
755 'G_deprn_exists: false' );
756 end if;
757
758 if (G_rollback_deprn_exists) then
759 fnd_log.string(G_LEVEL_STATEMENT,
760 G_MODULE_NAME||l_procedure_name,
761 'G_rollback_deprn_exists: true' );
762 else
763 fnd_log.string(G_LEVEL_STATEMENT,
764 G_MODULE_NAME||l_procedure_name,
765 'G_rollback_deprn_exists: false' );
766 end if;
767
768 if (G_alc_enabled) then
769 fnd_log.string(G_LEVEL_STATEMENT,
770 G_MODULE_NAME||l_procedure_name,
771 'G_alc_enabled: true' );
772 else
773 fnd_log.string(G_LEVEL_STATEMENT,
774 G_MODULE_NAME||l_procedure_name,
775 'G_alc_enabled: false' );
776 end if;
777
778 if (G_sorp_enabled) then
779 fnd_log.string(G_LEVEL_STATEMENT,
780 G_MODULE_NAME||l_procedure_name,
781 'G_sorp_enabled: true' );
782 else
783 fnd_log.string(G_LEVEL_STATEMENT,
784 G_MODULE_NAME||l_procedure_name,
785 'G_sorp_enabled: false' );
786 end if;
787
788 END IF;
789
790 -- process trx level first (common for all trx level events)
791
792 if (G_trx_exists or G_inter_trx_exists) then
793 EXECUTE IMMEDIATE l_stmt_trx;
794 end if;
795
796 if (G_dep_exists) then
797 EXECUTE IMMEDIATE l_stmt_deprn;
798 end if;
799
800 if (G_def_exists) then
801 EXECUTE IMMEDIATE l_stmt_def;
802 end if;
803
804 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
805
806 fnd_log.string(G_LEVEL_PROCEDURE,
807 G_MODULE_NAME||l_procedure_name||'.end',
808 'End of procedure');
809
810 END IF;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
815 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
816 fnd_message.set_token('ORACLE_ERR',SQLERRM);
817 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
818 END IF;
819 RAISE;
820 END;
821
822 --------------------------------------------------------------------------------
823
824 END fa_xla_extract_util_pkg;