[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_SOURCES_PKG
Source
1 PACKAGE BODY fa_xla_cmp_sources_pkg AS
2 /* $Header: faxlacsb.pls 120.17.12010000.4 2009/01/14 13:44:33 bridgway ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | fa_xla_cmp_sources_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for to determine sql statments for GT insert based on sources in |
14 | use for each extract type |
15 | |
16 | NOTES |
17 | This package relies on some static business logic specific to FA |
18 | as well as the XLA AAD setups for determining addition sources, |
19 | tables, columns to extract. |
20 | |
21 | Primary restrictions: |
22 | 1) in base R12 standard sources can only be seeded by ORACLE |
23 | thus attempts to add additional sources may either be ignored |
24 | or fail (depending on whether FKs for table/column are loaded) |
25 | 2) on a related note, the tables allowed in the seeded setup are |
26 | a small subset and to add a table requires not only the seed |
27 | and case impacts (GT), but also if it is a new table not yet |
28 | recognized by this program, various sections of this code must be |
29 | updated - including arrays and the "where clause append" section |
30 | WITHOUT the correct where clause the risks of excluding data or |
31 | causing cartesion products (and thus ora-1) will appear |
32 | 3) New sources from existing tables can be added at any time |
33 | without impact to this program, but require case changes to the |
34 | GT extract tables to hold those sources |
35 | 4) *** NEW *** |
36 | DO NOT EDIT /SAVE THIS FILE WITH TABS!!!!!!! |
37 | String comparison in particular looks for spaces and you risk |
38 | breaking the logic. If your editor does this, than use vi!!!!! |
39 | if you do this, then dont !!!! |
40 | |
41 | *** CUSTOMIZATION OF THIS PACKAGE OR STANDARD SOURCES IS NOT SUPPORTED *** |
42 | |
43 | HISTORY |
44 | 25-FEB-2006 BRIDGWAY Created |
45 | |
46 +===========================================================================*/
47
48
49 --+==========================================================================+
50 --| |
51 --| Private global constants |
52 --| |
53 --+==========================================================================+
54
55 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
56 C_CREATED CONSTANT BOOLEAN := TRUE;
57
58 g_Max_line CONSTANT NUMBER := 225;
59 g_chr_quote CONSTANT VARCHAR2(10):='''';
60 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
61
62 g_log_level_rec fa_api_types.log_level_rec_type;
63
64 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
65
66 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
67 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
68 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
69 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
70 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
71 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
72
73 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_header_pkg.';
74
75 G_initialized boolean := FALSE;
76
77 -- deprn
78 G_deprn_event_class_table fa_char30_tbl_type;
79 G_known_deprn_hdr_tables fa_char30_tbl_type;
80 G_known_deprn_line_tables fa_char30_tbl_type;
81
82 -- deferred
83 G_def_event_class_table fa_char30_tbl_type;
84 G_known_def_hdr_tables fa_char30_tbl_type;
85 G_known_def_line_tables fa_char30_tbl_type;
86
87 -- transactions (header and statging)
88 G_trx1_hdr_event_class_table fa_char30_tbl_type;
89 G_trx2_hdr_event_class_table fa_char30_tbl_type;
90 G_known_trx_hdr_tables fa_char30_tbl_type;
91 G_known_stg_tables fa_char30_tbl_type;
92
93 -- line level event classes
94 G_fin1_line_event_class_table fa_char30_tbl_type;
95 G_fin2_line_event_class_table fa_char30_tbl_type;
96 G_xfr_line_event_class_table fa_char30_tbl_type;
97 G_dist_line_event_class_table fa_char30_tbl_type;
98 G_ret_line_event_class_table fa_char30_tbl_type;
99
100 -- line level tables
101 G_known_fin1_line_tables fa_char30_tbl_type;
102 G_known_fin2_line_tables fa_char30_tbl_type;
103 G_known_xfr_line_tables fa_char30_tbl_type;
104 G_known_dist1_line_tables fa_char30_tbl_type;
105 G_known_dist2_line_tables fa_char30_tbl_type;
106 G_known_ret_line_tables fa_char30_tbl_type;
107
108 -- mls level tables
109 G_trx_mls_event_class_table fa_char30_tbl_type;
110 G_known_mls_tables fa_char30_tbl_type;
111
112 G_known_schemas fa_char30_tbl_type;
113
114 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
115 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
116 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
117
118
119 -- header level constant values
120
121 c_hdr_insert CONSTANT VARCHAR2(32000) := '
122 insert into fa_xla_ext_headers_b_gt (
123 event_id ,
124 DEFAULT_CCID ,
125 BOOK_TYPE_CODE ,
126 PERIOD_NAME ,
127 PERIOD_CLOSE_DATE ,
128 PERIOD_COUNTER ,
129 ACCOUNTING_DATE ,
130 TRANSFER_TO_GL_FLAG ';
131
132 c_hdr_select CONSTANT VARCHAR2(32000) := ' )
133 select ctlgd.event_id,
134 bc.FLEXBUILDER_DEFAULTS_CCID ,
135 bc.book_type_code ,
136 dp.PERIOD_NAME ,
137 dp.CALENDAR_PERIOD_CLOSE_DATE ,
138 dp.PERIOD_COUNTER ,
139 ctlgd.event_date ,';
140
141 c_hdr_select1 CONSTANT VARCHAR2(32000) := '
142 ''Y'' ' ;
143
144 c_hdr_select2 CONSTANT VARCHAR2(32000) := '
145 decode(bc.GL_POSTING_ALLOWED_FLAG ,
146 ''YES'', ''Y'',''N'') ';
147
148 c_hdr_from CONSTANT VARCHAR2(32000) := '
149 FROM xla_events_gt ctlgd,
150 fa_deprn_periods dp,
151 fa_book_controls bc ';
152
153 c_hdr_where_trx CONSTANT VARCHAR2(32000) := '
154 WHERE ctlgd.entity_code = ''TRANSACTIONS''
155 AND ctlgd.event_type_code in (''ADDITIONS'', ''CIP_ADDITIONS'',
156 ''ADJUSTMENTS'', ''CIP_ADJUSTMENTS'',
157 ''CAPITALIZATION'', ''REVERSE_CAPITALIZATION'',
158 ''REVALUATION'', ''CIP_REVALUATION'',
159 ''TRANSFERS'', ''CIP_TRANSFERS'',
160 ''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
161 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'',
162 ''RETIREMENTS'', ''CIP_RETIREMENTS'',
163 ''REINSTATEMENTS'', ''CIP_REINSTATEMENTS'',
164 ''DEPRECIATION_ADJUSTMENTS'',
165 ''UNPLANNED_DEPRECIATION'',
166 ''TERMINAL_GAIN_LOSS'',
167 ''RETIREMENT_ADJUSTMENT'')
168 AND th.transaction_header_id = ctlgd.source_id_int_1
169 AND ctlgd.valuation_method = dp.book_type_code
170 AND ctlgd.valuation_method = bc.book_type_code
171 AND th.date_effective between dp.period_open_date and
172 nvl(dp.period_close_date, sysdate) ';
173
174 c_hdr_where_itrx CONSTANT VARCHAR2(32000) := '
175 WHERE ctlgd.entity_code = ''INTER_ASSET_TRANSACTIONS''
176 AND ctlgd.event_type_code in (''SOURCE_LINE_TRANSFERS'',
177 ''CIP_SOURCE_LINE_TRANSFERS'',
178 ''RESERVE_TRANSFERS'')
179 AND trx.trx_reference_id = ctlgd.source_id_int_1
180 AND trx.event_id = ctlgd.event_id
181 AND trx.book_type_code = dp.book_type_code
182 AND trx.book_type_code = bc.book_type_code
183 AND dp.book_type_code = trx.book_type_code
184 AND trx.creation_date between dp.period_open_date and
185 nvl(dp.period_close_date, sysdate) ';
186
187 c_hdr_where_deprn CONSTANT VARCHAR2(32000) := '
188 WHERE ctlgd.entity_code = ''DEPRECIATION''
189 AND ctlgd.event_type_code = ''DEPRECIATION''
190 AND dp.book_type_code = ctlgd.source_id_char_1
191 AND dp.period_counter = ctlgd.source_id_int_2
192 AND bc.book_type_code = ctlgd.source_id_char_1';
193
194 c_hdr_where_def CONSTANT VARCHAR2(32000) := '
195 WHERE ctlgd.entity_code = ''DEFERRED_DEPRECIATION''
196 AND ctlgd.event_type_code = ''DEFERRED_DEPRECIATION''
197 AND bc.book_type_code = ctlgd.source_id_char_1
198 AND dp.book_type_code = ctlgd.source_id_char_1
199 AND dp.period_counter = ctlgd.source_id_int_2 ';
200
201 -- line level constant values
202
203 -- deprn
204
205 c_line_insert_deprn CONSTANT VARCHAR2(32000) := '
206 insert into fa_xla_ext_lines_b_gt (
207 EVENT_ID ,
208 LINE_NUMBER ,
209 DISTRIBUTION_ID ,
210 DISTRIBUTION_TYPE_CODE ,
211 LEDGER_ID ,
212 CURRENCY_CODE ,
213 ENTERED_AMOUNT ,
214 BONUS_ENTERED_AMOUNT ,
215 REVAL_ENTERED_AMOUNT ,
216 GENERATED_CCID ,
217 GENERATED_OFFSET_CCID ,
218 BONUS_GENERATED_CCID ,
219 BONUS_GENERATED_OFFSET_CCID ,
220 REVAL_GENERATED_CCID ,
221 REVAL_GENERATED_OFFSET_CCID ,
222 RESERVE_ACCOUNT_CCID ,
223 DEPRN_EXPENSE_ACCOUNT_CCID ,
224 BONUS_RESERVE_ACCT_CCID ,
225 BONUS_EXPENSE_ACCOUNT_CCID ,
226 REVAL_AMORT_ACCOUNT_CCID ,
227 REVAL_RESERVE_ACCOUNT_CCID ,
228 BOOK_TYPE_CODE ,
229 PERIOD_COUNTER '; -- Bug:6399642
230
231 c_line_select_deprn CONSTANT VARCHAR2(32000) := ' )
232 select ctlgd.EVENT_ID ,
233 dd.distribution_id as distribution_id,
234 dd.distribution_id as dist_id,
235 ''DEPRN'' ,
236 bc.set_of_books_id ,
237 le.currency_code ,
238 dd.deprn_amount
239 - dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
240 dd.bonus_deprn_amount
241 - dd.bonus_deprn_adjustment_amount ,
242 dd.reval_amortization ,
243 dd.deprn_expense_ccid ,
244 dd.deprn_reserve_ccid ,
245 dd.bonus_deprn_expense_ccid ,
246 dd.bonus_deprn_reserve_ccid ,
247 dd.reval_amort_ccid ,
248 dd.reval_reserve_ccid ,
249 cb.RESERVE_ACCOUNT_CCID ,
250 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
251 cb.BONUS_RESERVE_ACCT_CCID ,
252 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
253 cb.REVAL_AMORT_ACCOUNT_CCID ,
254 cb.REVAL_RESERVE_ACCOUNT_CCID ,
255 ctlgd.source_id_char_1 ,
256 dd.period_counter '; -- Bug:6399642
257
258 c_line_from_deprn CONSTANT VARCHAR2(32000) := '
259 from xla_events_gt ctlgd,
260 fa_deprn_detail dd,
261 fa_distribution_history dh,
262 fa_additions_b ad,
263 fa_asset_history ah,
264 fa_category_books cb,
265 fa_book_controls bc,
266 gl_ledgers le ';
267
268 -- NOTE: we do not post track or zero lines
269 -- will taken care of in preprocessing hook (check track)
270
271 c_line_where_deprn CONSTANT VARCHAR2(32000) := '
272 where ctlgd.entity_code = ''DEPRECIATION''
273 AND ctlgd.event_type_code = ''DEPRECIATION''
274 AND dd.asset_id = ctlgd.source_id_int_1
275 AND dd.book_type_code = ctlgd.source_id_char_1
276 AND dd.period_counter = ctlgd.source_id_int_2
277 AND dd.deprn_run_id = ctlgd.source_id_int_3
278 AND ad.asset_id = ctlgd.source_id_int_1
279 AND dd.distribution_id = dh.distribution_id
280 AND ah.asset_id = ctlgd.source_id_int_1
281 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
282 AND nvl(DH.Date_ineffective, SYSDATE) <=
283 nvl(AH.Date_ineffective, SYSDATE)
284 AND cb.category_id = ah.category_id
285 AND cb.book_type_code = ctlgd.source_id_char_1
286 AND ah.asset_type in (''CAPITALIZED'', ''GROUP'')
287 AND ad.asset_type in (''CAPITALIZED'', ''GROUP'')
288 AND bc.book_type_code = ctlgd.source_id_char_1
289 AND le.ledger_id = bc.set_of_books_id ';
290
291
292
293 -- deferred
294
295
296 c_line_insert_def CONSTANT VARCHAR2(32000) := '
297 insert into fa_xla_ext_lines_b_gt (
298 EVENT_ID ,
299 LINE_NUMBER ,
300 DISTRIBUTION_ID ,
301 DISTRIBUTION_TYPE_CODE ,
302 LEDGER_ID ,
303 CURRENCY_CODE ,
304 ENTERED_AMOUNT ,
305 BOOK_TYPE_CODE ,
306 TAX_BOOK_TYPE_CODE ,
307 GENERATED_CCID ,
308 GENERATED_OFFSET_CCID ';
309
310 c_line_select_def CONSTANT VARCHAR2(32000) := ' )
311 select ctlgd.EVENT_ID ,
312 df.distribution_id as distribution_id,
313 df.distribution_id as dist_id,
314 ''DEFERRED'' ,
315 bc.set_of_books_id ,
316 le.currency_code ,
317 df.deferred_deprn_expense_amount ,
318 df.corp_book_type_code ,
319 df.tax_book_type_code ,
320 df.deferred_deprn_expense_ccid ,
321 df.deferred_deprn_reserve_ccid ';
322
323 c_line_from_def CONSTANT VARCHAR2(32000) := '
324 from fa_additions_b ad,
325 fa_asset_history ah,
326 fa_book_controls bc,
327 fa_category_books cb,
328 fa_distribution_history dh,
329 fa_deferred_deprn df,
330 gl_ledgers le,
331 xla_events_gt ctlgd ';
332
333 c_line_where_def CONSTANT VARCHAR2(32000) := '
334 where ctlgd.entity_code = ''DEFERRED_DEPRECIATION''
335 AND ctlgd.event_type_code = ''DEFERRED_DEPRECIATION''
336 AND df.asset_id = ctlgd.source_id_int_1
337 AND df.corp_book_type_code = ctlgd.source_id_char_1
338 AND df.corp_period_counter = ctlgd.source_id_int_2
339 AND df.tax_book_type_code = ctlgd.source_id_char_2
340 AND df.event_id = ctlgd.event_id
341 AND ad.asset_id = ctlgd.source_id_int_1
342 AND dh.distribution_id = df.distribution_id
343 AND ah.asset_id = ctlgd.source_id_int_1
344 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
345 AND nvl(DH.Date_ineffective, SYSDATE) <=
346 nvl(AH.Date_ineffective, SYSDATE)
347 AND cb.category_id = ah.category_id
348 AND cb.book_type_code = ctlgd.source_id_char_1
349 AND ah.asset_type in (''CAPITALIZED'', ''GROUP'')
350 AND ad.asset_type in (''CAPITALIZED'', ''GROUP'')
351 AND bc.book_type_code = ctlgd.source_id_char_1
352 AND le.ledger_id = bc.set_of_books_id ';
353
354
355 -- trx-staging
356
357 c_line_insert_stg CONSTANT VARCHAR2(32000) := '
358 insert into fa_xla_ext_lines_stg_gt (
359 EVENT_ID ,
360 EVENT_TYPE_CODE ,
361 TRANSACTION_HEADER_ID ,
362 MEMBER_TRANSACTION_HEADER_ID ,
363 DISTRIBUTION_TYPE_CODE ,
364 BOOK_TYPE_CODE ,
365 LEDGER_ID ,
366 CURRENCY_CODE ,
367 ASSET_TYPE ,
368 ASSET_COST_ACCOUNT_CCID ,
369 ASSET_CLEARING_ACCOUNT_CCID ,
370 CIP_COST_ACCOUNT_CCID ,
371 CIP_CLEARING_ACCOUNT_CCID ,
372 RESERVE_ACCOUNT_CCID ,
373 DEPRN_EXPENSE_ACCOUNT_CCID ,
374 BONUS_RESERVE_ACCT_CCID ,
375 BONUS_EXPENSE_ACCOUNT_CCID ,
376 REVAL_AMORT_ACCOUNT_CCID ,
377 REVAL_RESERVE_ACCOUNT_CCID ,
378 UNPLAN_EXPENSE_ACCOUNT_CCID ,
379 ALT_COST_ACCOUNT_CCID ,
380 WRITE_OFF_ACCOUNT_CCID ';
381
382 c_line_select_stg CONSTANT VARCHAR2(32000) := ' )
383 select ctlgd.EVENT_ID ,
384 ctlgd.event_type_code ,
385 th.transaction_header_id ,
386 nvl(th.member_transaction_header_id,
387 th.transaction_header_id) ,
388 ''TRX'' ,
389 bc.book_type_code , -- Bug:6272229
390 bc.set_of_books_id ,
391 le.currency_code ,
392 ah.asset_type ,
393 cb.ASSET_COST_ACCOUNT_CCID ,
394 cb.ASSET_CLEARING_ACCOUNT_CCID ,
395 cb.WIP_COST_ACCOUNT_CCID ,
396 cb.WIP_CLEARING_ACCOUNT_CCID ,
397 cb.RESERVE_ACCOUNT_CCID ,
398 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
399 cb.BONUS_RESERVE_ACCT_CCID ,
400 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
401 cb.REVAL_AMORT_ACCOUNT_CCID ,
402 cb.REVAL_RESERVE_ACCOUNT_CCID ,
403 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
404 cb.ALT_COST_ACCOUNT_CCID ,
405 cb.WRITE_OFF_ACCOUNT_CCID ';
406
407 c_line_from_stg1 CONSTANT VARCHAR2(32000) := '
408 from fa_additions_b ad,
409 fa_asset_history ah,
410 fa_book_controls bc,
411 fa_category_books cb,
412 gl_ledgers le,
413 fa_transaction_headers th,
414 xla_events_gt ctlgd ';
415
416 c_line_from_stg2 CONSTANT VARCHAR2(32000) := '
417 from fa_additions_b ad,
418 fa_asset_history ah,
419 fa_book_controls bc,
420 fa_category_books cb,
421 gl_ledgers le,
422 fa_transaction_headers th,
423 fa_trx_references trx,
424 xla_events_gt ctlgd ';
425
426 c_line_where_stg1 CONSTANT VARCHAR2(32000) := '
427 where ctlgd.entity_code = ''TRANSACTIONS''
428 AND ctlgd.event_type_code in (''ADDITIONS'', ''CIP_ADDITIONS'',
429 ''ADJUSTMENTS'', ''CIP_ADJUSTMENTS'',
430 ''CAPITALIZATION'', ''REVERSE_CAPITALIZATION'',
431 ''REVALUATION'', ''CIP_REVALUATION'',
432 ''TRANSFERS'', ''CIP_TRANSFERS'',
433 ''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
434 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'',
435 ''RETIREMENTS'', ''CIP_RETIREMENTS'',
436 ''REINSTATEMENTS'', ''CIP_REINSTATEMENTS'',
437 ''DEPRECIATION_ADJUSTMENTS'',
438 ''UNPLANNED_DEPRECIATION'',
439 ''TERMINAL_GAIN_LOSS'',
440 ''RETIREMENT_ADJUSTMENT'')
441 AND (th.transaction_header_id = ctlgd.source_id_int_1 or
442 th.member_transaction_header_id = ctlgd.source_id_int_1) -- this is what grabs the groups
443 -- AND th.book_type_code = ctlgd.valuation_method -- Bug:6272229
444 AND bc.book_type_code = ctlgd.valuation_method
445 AND le.ledger_id = bc.set_of_books_id
446 AND ad.asset_id = th.asset_id
447 AND ah.asset_id = th.asset_id
448 AND th.transaction_header_id between ah.transaction_header_id_in and
449 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
450 AND cb.category_id = ah.category_id
451 AND cb.book_type_code = ctlgd.valuation_method
452 AND ah.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'')
453 AND ad.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'') ';
454
455 c_line_where_stg2 CONSTANT VARCHAR2(32000) := '
456 where ctlgd.entity_code = ''INTER_ASSET_TRANSACTIONS''
457 AND ctlgd.event_type_code in (''SOURCE_LINE_TRANSFERS'',
458 ''CIP_SOURCE_LINE_TRANSFERS'',
459 ''RESERVE_TRANSFERS'')
460 AND trx.trx_reference_id = ctlgd.source_id_int_1
461 AND (th.transaction_header_id = trx.src_transaction_header_id or
462 th.transaction_header_id = trx.dest_transaction_header_id )
463 AND bc.book_type_code = ctlgd.valuation_method
464 AND le.ledger_id = bc.set_of_books_id
465 AND ad.asset_id = th.asset_id
466 AND ah.asset_id = th.asset_id
467 AND th.transaction_header_id between ah.transaction_header_id_in and
468 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
469 AND cb.category_id = ah.category_id
470 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
471 AND ah.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'')
472 AND ad.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'') ';
473
474 c_line_where_stg3 CONSTANT VARCHAR2(32000) := '
475 where ctlgd.entity_code = ''INTER_ASSET_TRANSACTIONS''
476 AND ctlgd.event_type_code in (''SOURCE_LINE_TRANSFERS'',
477 ''CIP_SOURCE_LINE_TRANSFERS'',
478 ''RESERVE_TRANSFERS'')
479 AND trx.trx_reference_id = ctlgd.source_id_int_1
480 AND (th.member_transaction_header_id = trx.src_transaction_header_id or
481 th.member_transaction_header_id = trx.dest_transaction_header_id )
482 AND bc.book_type_code = ctlgd.valuation_method
483 AND le.ledger_id = bc.set_of_books_id
484 AND ad.asset_id = th.asset_id
485 AND ah.asset_id = th.asset_id
486 AND th.transaction_header_id between ah.transaction_header_id_in and
487 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
488 AND cb.category_id = ah.category_id
489 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
490 AND ah.asset_type in (''GROUP'')
491 AND ad.asset_type in (''GROUP'') ';
492
493 -- trx - lines
494
495 c_line_insert_trx CONSTANT VARCHAR2(32000) := '
496 insert into fa_xla_ext_lines_b_gt (
497 EVENT_ID ,
498 LINE_NUMBER ,
499 DISTRIBUTION_ID ,
500 DISTRIBUTION_TYPE_CODE ,
501 LEDGER_ID ,
502 CURRENCY_CODE ,
503 BOOK_TYPE_CODE ,
504 GENERATED_CCID ,
505 ASSET_ID ,
506 ASSET_TYPE ,
507 ASSET_COST_ACCOUNT_CCID ,
508 ASSET_CLEARING_ACCOUNT_CCID ,
509 CIP_COST_ACCOUNT_CCID ,
510 CIP_CLEARING_ACCOUNT_CCID ,
511 RESERVE_ACCOUNT_CCID ,
512 DEPRN_EXPENSE_ACCOUNT_CCID ,
513 BONUS_RESERVE_ACCT_CCID ,
514 BONUS_EXPENSE_ACCOUNT_CCID ,
515 REVAL_AMORT_ACCOUNT_CCID ,
516 REVAL_RESERVE_ACCOUNT_CCID ,
517 UNPLAN_EXPENSE_ACCOUNT_CCID ,
518 ALT_COST_ACCOUNT_CCID ,
519 WRITE_OFF_ACCOUNT_CCID ,
520 ENTERED_AMOUNT '; -- Bug:6399642
521
522 c_line_select_trx CONSTANT VARCHAR2(32000) := ' )
523 select stg.EVENT_ID ,
524 adj.adjustment_line_id ,
525 adj.distribution_id ,
526 stg.distribution_type_code ,
527 stg.ledger_id ,
528 stg.currency_code ,
529 stg.book_type_code ,
530 adj.code_combination_id ,
531 adj.asset_id ,
532 stg.asset_type ,
533 stg.ASSET_COST_ACCOUNT_CCID ,
534 stg.ASSET_CLEARING_ACCOUNT_CCID ,
535 stg.CIP_COST_ACCOUNT_CCID ,
536 stg.CIP_CLEARING_ACCOUNT_CCID ,
537 stg.RESERVE_ACCOUNT_CCID ,
538 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
539 stg.BONUS_RESERVE_ACCT_CCID ,
540 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
541 stg.REVAL_AMORT_ACCOUNT_CCID ,
542 stg.REVAL_RESERVE_ACCOUNT_CCID ,
543 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
544 stg.ALT_COST_ACCOUNT_CCID ,
545 stg.WRITE_OFF_ACCOUNT_CCID , ';
546
547 -- BUG# 7693865
548 c_line_select_trx_dist1 CONSTANT VARCHAR2(32000) := ' )
549 select stg.EVENT_ID ,
550 adj.adjustment_line_id ,
551 adj.distribution_id ,
552 stg.distribution_type_code ,
553 stg.ledger_id ,
554 stg.currency_code ,
555 stg.book_type_code ,
556 adj.code_combination_id ,
557 adj.asset_id ,
558 stg.asset_type ,
559 cb.ASSET_COST_ACCOUNT_CCID ,
560 cb.ASSET_CLEARING_ACCOUNT_CCID ,
561 cb.WIP_COST_ACCOUNT_CCID ,
562 cb.WIP_CLEARING_ACCOUNT_CCID ,
563 cb.RESERVE_ACCOUNT_CCID ,
564 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
565 cb.BONUS_RESERVE_ACCT_CCID ,
566 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
567 cb.REVAL_AMORT_ACCOUNT_CCID ,
568 cb.REVAL_RESERVE_ACCOUNT_CCID ,
569 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
570 cb.ALT_COST_ACCOUNT_CCID ,
571 cb.WRITE_OFF_ACCOUNT_CCID , ';
572
573
574 -- adjustment_amount decode handling
575
576 c_line_adj_amt_fin1 CONSTANT VARCHAR2(32000) := '
577 decode(adj.adjustment_type,
578 ''COST CLEARING'',
579 decode(debit_credit_flag,
580 ''CR'', adjustment_amount,
581 -1 * adjustment_amount),
582 ''RESERVE'',
583 decode(debit_credit_flag,
584 ''CR'', adjustment_amount,
585 -1 * adjustment_amount),
586 ''BONUS RESERVE'',
587 decode(debit_credit_flag,
588 ''CR'', adjustment_amount,
589 -1 * adjustment_amount),
590 ''REVAL RESERVE'',
591 decode(debit_credit_flag,
592 ''CR'', adjustment_amount,
593 -1 * adjustment_amount),
594 ''CIP COST'',
595 decode(stg.event_type_code,
596 ''CAPITALIZATION'',
597 decode(debit_credit_flag,
598 ''CR'', adjustment_amount,
599 -1 * adjustment_amount),
600 ''REVERSE_CAPITALIZATION'',
601 decode(debit_credit_flag,
602 ''CR'', adjustment_amount,
603 -1 * adjustment_amount),
604 decode(debit_credit_flag,
605 ''DR'', adjustment_amount,
606 -1 * adjustment_amount)),
607 ''COST'',
608 decode(debit_credit_flag,
609 ''DR'', adjustment_amount,
610 -1 * adjustment_amount),
611 ''EXPENSE'',
612 decode(debit_credit_flag,
613 ''DR'', adjustment_amount,
614 -1 * adjustment_amount),
615 ''BONUS EXPENSE'',
616 decode(debit_credit_flag,
617 ''DR'', adjustment_amount,
618 -1 * adjustment_amount),
619 ''NBV RETIRED'',
620 decode(debit_credit_flag,
621 ''DR'', adjustment_amount,
622 -1 * adjustment_amount),
623 decode(debit_credit_flag,
624 ''DR'', adjustment_amount,
625 -1 * adjustment_amount)) ';
626
627
628 c_line_adj_amt_fin2 CONSTANT VARCHAR2(32000) := '
629 decode(adj.source_dest_code,
630 ''SOURCE'',
631 decode(adj.adjustment_type,
632 ''RESERVE'',
633 decode(debit_credit_flag,
634 ''DR'', adjustment_amount,
635 -1 * adjustment_amount),
636 ''BONUS RESERVE'',
637 decode(debit_credit_flag,
638 ''DR'', adjustment_amount,
639 -1 * adjustment_amount),
640 ''REVAL RESERVE'',
641 decode(debit_credit_flag,
642 ''DR'', adjustment_amount,
643 -1 * adjustment_amount),
644 decode(debit_credit_flag,
645 ''CR'', adjustment_amount,
646 -1 * adjustment_amount)),
647 decode(adj.adjustment_type,
648 ''RESERVE'',
649 decode(debit_credit_flag,
650 ''CR'', adjustment_amount,
651 -1 * adjustment_amount),
652 ''BONUS RESERVE'',
653 decode(debit_credit_flag,
654 ''CR'', adjustment_amount,
655 -1 * adjustment_amount),
656 ''REVAL RESERVE'',
657 decode(debit_credit_flag,
658 ''CR'', adjustment_amount,
659 -1 * adjustment_amount),
660 decode(debit_credit_flag,
661 ''DR'', adjustment_amount,
662 -1 * adjustment_amount))) ';
663
664 c_line_adj_amt_xfr CONSTANT VARCHAR2(32000) := '
665 decode(adj.source_dest_code,
666 ''SOURCE'',
667 decode(adj.adjustment_type,
668 ''RESERVE'',
669 decode(debit_credit_flag,
670 ''DR'', adjustment_amount,
671 -1 * adjustment_amount),
672 ''BONUS RESERVE'',
673 decode(debit_credit_flag,
674 ''DR'', adjustment_amount,
675 -1 * adjustment_amount),
676 ''REVAL RESERVE'',
677 decode(debit_credit_flag,
678 ''DR'', adjustment_amount,
679 -1 * adjustment_amount),
680 decode(debit_credit_flag,
681 ''CR'', adjustment_amount,
682 -1 * adjustment_amount)),
683 decode(adj.adjustment_type,
684 ''RESERVE'',
685 decode(debit_credit_flag,
686 ''CR'', adjustment_amount,
687 -1 * adjustment_amount),
688 ''BONUS RESERVE'',
689 decode(debit_credit_flag,
690 ''CR'', adjustment_amount,
691 -1 * adjustment_amount),
692 ''REVAL RESERVE'',
693 decode(debit_credit_flag,
694 ''CR'', adjustment_amount,
695 -1 * adjustment_amount),
696 decode(debit_credit_flag,
697 ''DR'', adjustment_amount,
698 -1 * adjustment_amount))) ';
699
700 c_line_adj_amt_dist1 CONSTANT VARCHAR2(32000) := '
701 decode(adj.adjustment_type,
702 ''RESERVE'',
703 decode(debit_credit_flag,
704 ''DR'', adjustment_amount,
705 -1 * adjustment_amount),
706 ''BONUS RESERVE'',
707 decode(debit_credit_flag,
708 ''DR'', adjustment_amount,
709 -1 * adjustment_amount),
710 ''REVAL RESERVE'',
711 decode(debit_credit_flag,
712 ''DR'', adjustment_amount,
713 -1 * adjustment_amount),
714 decode(debit_credit_flag,
715 ''CR'', adjustment_amount,
716 -1 * adjustment_amount)) ';
717
718 c_line_adj_amt_dist2 CONSTANT VARCHAR2(32000) := '
719 decode(adj.adjustment_type,
720 ''RESERVE'',
721 decode(debit_credit_flag,
722 ''CR'', adjustment_amount,
723 -1 * adjustment_amount),
724 ''BONUS RESERVE'',
725 decode(debit_credit_flag,
726 ''CR'', adjustment_amount,
727 -1 * adjustment_amount),
728 ''REVAL RESERVE'',
729 decode(debit_credit_flag,
730 ''CR'', adjustment_amount,
731 -1 * adjustment_amount),
732 decode(debit_credit_flag,
733 ''DR'', adjustment_amount,
734 -1 * adjustment_amount)) ';
735
736 c_line_adj_amt_ret CONSTANT VARCHAR2(32000) := '
737 decode(adj.adjustment_type,
738 ''RESERVE'',
739 decode(debit_credit_flag,
740 ''DR'', adjustment_amount,
741 -1 * adjustment_amount),
742 ''BONUS RESERVE'',
743 decode(debit_credit_flag,
744 ''DR'', adjustment_amount,
745 -1 * adjustment_amount),
746 ''REVAL RESERVE'',
747 decode(debit_credit_flag,
748 ''DR'', adjustment_amount,
749 -1 * adjustment_amount),
750 ''NBV RETIRED'',
751 decode(debit_credit_flag,
752 ''DR'', adjustment_amount,
753 -1 * adjustment_amount),
754 ''PROCEEDS CLR'',
755 decode(debit_credit_flag,
756 ''DR'', adjustment_amount,
757 -1 * adjustment_amount),
758 ''REMOVALCOST'',
759 decode(debit_credit_flag,
760 ''DR'', adjustment_amount,
761 -1 * adjustment_amount),
762 decode(debit_credit_flag,
763 ''CR'', adjustment_amount,
764 -1 * adjustment_amount)) ';
765
766
767
768
769
770 c_line_from_trx CONSTANT VARCHAR2(32000) := '
771 from fa_xla_ext_lines_stg_gt stg,
772 fa_adjustments adj,
773 fa_distribution_history dh,
774 fa_locations loc,
775 fa_lookups lu ';
776
777 c_line_from_trx_dist1 CONSTANT VARCHAR2(32000) := ',
778 fa_asset_history ah,
779 fa_category_books cb ';
780
781 c_line_from_trx_ret CONSTANT VARCHAR2(32000) := ',
782 fa_retirements ret ';
783
784 c_line_where_trx CONSTANT VARCHAR2(32000) := '
785 WHERE adj.transaction_header_id = stg.transaction_header_id
786 AND adj.book_type_code = stg.book_type_code
787 AND adj.distribution_id = dh.distribution_id
788 AND dh.location_id = loc.location_id
789 -- AND dh.assigned_to = emp.employee_id(+)
790 AND lu.lookup_type = ''JOURNAL ENTRIES''
791 AND lu.lookup_code = adj.source_type_code || '' '' ||
792 decode (adj.adjustment_type,
793 ''CIP COST'', ''COST'',
794 adj.adjustment_type)
795 AND adj.adjustment_type not in (''REVAL EXPENSE'', ''REVAL AMORT'')
796 AND nvl(adj.track_member_flag, ''N'') = ''N''
797 AND adj.adjustment_amount <> 0 ';
798
799 c_line_where_trx_fin1 CONSTANT VARCHAR2(32000) := '
800 AND stg.event_type_code in (''ADDITIONS'', ''CIP_ADDITIONS'',
801 ''ADJUSTMENTS'', ''CIP_ADJUSTMENTS'',
802 ''CAPITALIZATION'', ''REVERSE_CAPITALIZATION'',
803 ''REVALUATION'', ''CIP_REVALUATION'',
804 ''DEPRECIATION_ADJUSTMENTS'',
805 ''UNPLANNED_DEPRECIATION'',
806 ''TERMINAL_GAIN_LOSS'',
807 ''RETIREMENT_ADJUSTMENT'') ';
808
809 c_line_where_trx_fin2 CONSTANT VARCHAR2(32000) := '
810 AND stg.event_type_code in (''SOURCE_LINE_TRANSFERS'',
811 ''CIP_SOURCE_LINE_TRANSFERS'',
812 ''RESERVE_TRANSFERS'') ';
813
814 c_line_where_trx_xfr CONSTANT VARCHAR2(32000) := '
815 AND stg.event_type_code in (''TRANSFERS'', ''CIP_TRANSFERS'') ';
816
817 c_line_where_trx_dist1 CONSTANT VARCHAR2(32000) := '
818 AND stg.event_type_code in (''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
819 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'')
820 AND adj.asset_id = ah.asset_id
821 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
822 AND cb.category_id = ah.category_id
823 AND cb.book_type_code = adj.book_type_code
824 AND adj.source_dest_code = ''SOURCE'' ';
825
826
827 c_line_where_trx_dist2 CONSTANT VARCHAR2(32000) := '
828 AND stg.event_type_code in (''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
829 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'')
830 AND adj.source_dest_code = ''DEST'' ';
831
832
833 -- need to think about group in the following!!!
834
835 c_line_where_trx_ret CONSTANT VARCHAR2(32000) := '
836 AND stg.event_type_code in (''RETIREMENTS'', ''CIP_RETIREMENTS'')
837 AND ret.transaction_header_id_in = stg.member_transaction_header_id ';
838
839
840 c_line_where_trx_res CONSTANT VARCHAR2(32000) := '
841 AND stg.event_type_code in (''REINSTATEMENTS'',''CIP_REINSTATEMENTS'')
842 AND ret.transaction_header_id_out = stg.member_transaction_header_id ';
843
844
845
846
847 c_line_rowcount_debug CONSTANT VARCHAR2(32000) := '
848 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
849 fnd_log.string(G_LEVEL_PROCEDURE,
850 G_MODULE_NAME||l_procedure_name,
851 ''Rows inserted into lines: '' || to_char(SQL%ROWCOUNT));
852 END IF;
853
854 ';
855
856
857 c_mc_if_condition CONSTANT VARCHAR2(32000) := '
858 if (fa_xla_extract_util_pkg.G_alc_enabled) then
859
860 ';
861
862
863 -- header level constant values
864
865 c_mls_insert CONSTANT VARCHAR2(32000) := '
866 insert into fa_xla_ext_lines_tl_gt (
867 event_id ,
868 line_number ,
869 LEDGER_ID ,
870 TRANSACTION_HEADER_ID ,
871 ASSET_ID ,
872 DEPRN_RUN_ID ,
873 BOOK_TYPE_CODE ,
874 PERIOD_COUNTER '; -- Bug:6399642
875
876 c_mls_select CONSTANT VARCHAR2(32000) := ' )
877 select xl.event_id ,
878 xl.line_number ,
879 xl.ledger_id ,
880 xl.TRANSACTION_HEADER_ID ,
881 xl.ASSET_ID ,
882 xl.DEPRN_RUN_ID ,
883 xl.BOOK_TYPE_CODE ,
884 xl.PERIOD_COUNTER '; -- Bug:6399642
885
886 c_mls_from CONSTANT VARCHAR2(32000) := '
887 FROM fa_xla_ext_lines_b_gt xl ';
888
889
890
891 --+============================================+
892 --| |
893 --| PRIVATE PROCEDURES/FUNCTIONS |
894 --| |
895 --+============================================+
896
897 -- AddMember
898 -- Extends and Inserts a value into table
899
900 Procedure AddMember (p_table IN OUT NOCOPY fa_char30_tbl_type,
901 p_value IN VARCHAR2)IS
902
903 l_procedure_name varchar2(80) := 'AddMember';
904
905 BEGIN
906
907 p_table.EXTEND;
908 p_table(p_table.last) := p_value;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
913 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
914 fnd_message.set_token('ORACLE_ERR',SQLERRM);
915 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
916 END IF;
917 RAISE;
918
919 END AddMember;
920
921
922
923
924 procedure delete_table_member (p_table IN OUT NOCOPY v30_tbl,
925 p_index IN number) is
926
927 l_procedure_name varchar2(80) := ' delete_table_member';
928 l_count number;
929
930 begin
931 if nvl(p_index, 0) > 0 then
932
933 p_table.delete(p_index);
934
935 l_count := p_table.count;
936
937 for i in p_index..l_count loop
938
939 -- copy the next member into the current one
940 p_table(i) := p_table(i+1);
941 end loop;
942
943 -- delete the last member in the array which is now a duplicate
944 p_table.delete(l_count + 1);
945
946 end if;
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
951 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
952 fnd_message.set_token('ORACLE_ERR',SQLERRM);
953 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
954 END IF;
955 RAISE;
956
957 end delete_table_member;
958
959
960
961 -- Initialize
962 -- Loads plsql tables for known tables, enttites and events classes
963
964 Procedure initialize is
965
966 l_procedure_name varchar2(80) := 'Initialize';
967
968 BEGIN
969
970 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
971 fnd_log.string(G_LEVEL_PROCEDURE,
972 G_MODULE_NAME||l_procedure_name||'.begin',
973 'Beginning of procedure');
974 END IF;
975
976 -- schemas
977 G_known_schemas := fa_char30_tbl_type();
978 AddMember(G_known_schemas, 'FA');
979 AddMember(G_known_schemas, 'GL');
980 AddMember(G_known_schemas, 'XLA');
981
982
983 -- deprn (used for header and lines)
984 G_deprn_event_class_table := fa_char30_tbl_type();
985 AddMember(G_deprn_event_class_table, 'DEPRECIATION');
986
987 G_known_deprn_hdr_tables := fa_char30_tbl_type();
988 G_known_deprn_line_tables := fa_char30_tbl_type();
989
990 -- header
991 AddMember(G_known_deprn_hdr_tables, 'FA_BOOK_CONTROLS');
992 AddMember(G_known_deprn_hdr_tables, 'FA_DEPRN_PERIODS');
993 AddMember(G_known_deprn_hdr_tables, 'XLA_EVENTS_GT');
994
995 -- lines standard
996 AddMember(G_known_deprn_line_tables, 'FA_ADDITIONS_B');
997 AddMember(G_known_deprn_line_tables, 'FA_ASSET_HISTORY');
998 AddMember(G_known_deprn_line_tables, 'FA_BOOK_CONTROLS');
999 AddMember(G_known_deprn_line_tables, 'FA_DISTRIBUTION_HISTORY');
1000 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_DETAIL');
1001 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_PERIODS');
1002 AddMember(G_known_deprn_line_tables, 'GL_LEDGERS');
1003 AddMember(G_known_deprn_line_tables, 'XLA_EVENTS_GT');
1004
1005 -- lines non-standard
1006 AddMember(G_known_deprn_line_tables, 'FA_ASSET_KEYWORDS');
1007 AddMember(G_known_deprn_line_tables, 'FA_BOOKS');
1008 AddMember(G_known_deprn_line_tables, 'FA_CATEGORIES_B');
1009 AddMember(G_known_deprn_line_tables, 'FA_CATEGORY_BOOKS');
1010 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_SUMMARY');
1011 AddMember(G_known_deprn_line_tables, 'FA_LEASES');
1012 AddMember(G_known_deprn_line_tables, 'FA_LOCATIONS');
1013 AddMember(G_known_deprn_line_tables, 'FA_METHODS');
1014
1015
1016 -- deferred (used for header and lines)
1017 G_def_event_class_table := fa_char30_tbl_type();
1018 AddMember(G_def_event_class_table, 'DEFERRED_DEPRECIATION');
1019
1020 -- deferred
1021 G_known_def_hdr_tables := fa_char30_tbl_type();
1022 G_known_def_hdr_tables := G_known_deprn_hdr_tables;
1023 G_known_def_line_tables := fa_char30_tbl_type();
1024
1025 -- standard
1026 AddMember(G_known_def_line_tables, 'FA_ADDITIONS_B');
1027 AddMember(G_known_def_line_tables, 'FA_ASSET_HISTORY');
1028 AddMember(G_known_def_line_tables, 'FA_BOOK_CONTROLS');
1029 AddMember(G_known_def_line_tables, 'FA_DISTRIBUTION_HISTORY');
1030 AddMember(G_known_def_line_tables, 'FA_DEFERRED_DEPRN');
1031 AddMember(G_known_def_line_tables, 'FA_DEPRN_PERIODS');
1032 AddMember(G_known_def_line_tables, 'GL_LEDGERS');
1033 AddMember(G_known_def_line_tables, 'XLA_EVENTS_GT');
1034
1035 -- non-standard
1036 AddMember(G_known_def_line_tables, 'FA_ASSET_KEYWORDS');
1037 AddMember(G_known_def_line_tables, 'FA_BOOKS');
1038 AddMember(G_known_def_line_tables, 'FA_CATEGORIES_B');
1039 AddMember(G_known_def_line_tables, 'FA_CATEGORY_BOOKS');
1040 AddMember(G_known_def_line_tables, 'FA_LEASES');
1041 AddMember(G_known_def_line_tables, 'FA_LOCATIONS');
1042 AddMember(G_known_def_line_tables, 'FA_METHODS');
1043
1044
1045 -- headers only
1046 -- transactions
1047 G_trx1_hdr_event_class_table := fa_char30_tbl_type();
1048
1049 AddMember(G_trx1_hdr_event_class_table, 'ADDITIONS');
1050 AddMember(G_trx1_hdr_event_class_table, 'CIP_ADDITIONS');
1051 AddMember(G_trx1_hdr_event_class_table, 'ADJUSTMENTS');
1052 AddMember(G_trx1_hdr_event_class_table, 'CIP_ADJUSTMENTS');
1053 AddMember(G_trx1_hdr_event_class_table, 'CAPITALIZATION');
1054 AddMember(G_trx1_hdr_event_class_table, 'REVALUATION');
1055 AddMember(G_trx1_hdr_event_class_table, 'CIP_REVALUATION');
1056 AddMember(G_trx1_hdr_event_class_table, 'TRANSFERS');
1057 AddMember(G_trx1_hdr_event_class_table, 'CIP_TRANSFERS');
1058 AddMember(G_trx1_hdr_event_class_table, 'CATEGORY_RECLASS');
1059 AddMember(G_trx1_hdr_event_class_table, 'CIP_CATEGORY_RECLASS');
1060 AddMember(G_trx1_hdr_event_class_table, 'UNIT_ADJUSTMENTS');
1061 AddMember(G_trx1_hdr_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1062 AddMember(G_trx1_hdr_event_class_table, 'RETIREMENTS');
1063 AddMember(G_trx1_hdr_event_class_table, 'CIP_RETIREMENTS');
1064 AddMember(G_trx1_hdr_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1065 AddMember(G_trx1_hdr_event_class_table, 'UNPLANNED_DEPRECIATION');
1066 AddMember(G_trx1_hdr_event_class_table, 'TERMINAL_GAIN_LOSS');
1067 AddMember(G_trx1_hdr_event_class_table, 'RETIREMENT_ADJUSTMENT');
1068
1069 -- inter asset trxs
1070 -- used for staging and line
1071 G_trx2_hdr_event_class_table := fa_char30_tbl_type();
1072
1073 AddMember(G_trx2_hdr_event_class_table, 'SOURCE_LINE_TRANSFERS');
1074 AddMember(G_trx2_hdr_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1075 AddMember(G_trx2_hdr_event_class_table, 'RESERVE_TRANSFERS');
1076
1077
1078 -- line level event classes
1079 G_fin1_line_event_class_table := fa_char30_tbl_type();
1080 G_fin2_line_event_class_table := fa_char30_tbl_type();
1081 G_xfr_line_event_class_table := fa_char30_tbl_type();
1082 G_dist_line_event_class_table := fa_char30_tbl_type();
1083 G_ret_line_event_class_table := fa_char30_tbl_type();
1084
1085 AddMember(G_fin1_line_event_class_table, 'ADDITIONS');
1086 AddMember(G_fin1_line_event_class_table, 'CIP_ADDITIONS');
1087 AddMember(G_fin1_line_event_class_table, 'ADJUSTMENTS');
1088 AddMember(G_fin1_line_event_class_table, 'CIP_ADJUSTMENTS');
1089 AddMember(G_fin1_line_event_class_table, 'CAPITALIZATION');
1090 AddMember(G_fin1_line_event_class_table, 'REVALUATION');
1091 AddMember(G_fin1_line_event_class_table, 'CIP_REVALUATION');
1092 AddMember(G_fin1_line_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1093 AddMember(G_fin1_line_event_class_table, 'UNPLANNED_DEPRECIATION');
1094 AddMember(G_fin1_line_event_class_table, 'TERMINAL_GAIN_LOSS');
1095 AddMember(G_fin1_line_event_class_table, 'RETIREMENT_ADJUSTMENT');
1096
1097 AddMember(G_fin2_line_event_class_table, 'SOURCE_LINE_TRANSFERS');
1098 AddMember(G_fin2_line_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1099 AddMember(G_fin2_line_event_class_table, 'RESERVE_TRANSFERS');
1100
1101 AddMember(G_xfr_line_event_class_table, 'TRANSFERS');
1102 AddMember(G_xfr_line_event_class_table, 'CIP_TRANSFERS');
1103
1104 AddMember(G_dist_line_event_class_table, 'CATEGORY_RECLASS');
1105 AddMember(G_dist_line_event_class_table, 'CIP_CATEGORY_RECLASS');
1106 AddMember(G_dist_line_event_class_table, 'UNIT_ADJUSTMENTS');
1107 AddMember(G_dist_line_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1108
1109 AddMember(G_ret_line_event_class_table, 'RETIREMENTS');
1110 AddMember(G_ret_line_event_class_table, 'CIP_RETIREMENTS');
1111
1112 G_known_trx_hdr_tables := fa_char30_tbl_type();
1113 G_known_trx_hdr_tables := G_known_deprn_hdr_tables;
1114
1115 -- line level tables
1116 G_known_fin1_line_tables := fa_char30_tbl_type();
1117 G_known_fin2_line_tables := fa_char30_tbl_type();
1118 G_known_xfr_line_tables := fa_char30_tbl_type();
1119 G_known_dist1_line_tables := fa_char30_tbl_type();
1120 G_known_dist2_line_tables := fa_char30_tbl_type();
1121 G_known_ret_line_tables := fa_char30_tbl_type();
1122
1123 AddMember(G_known_fin1_line_tables, 'FA_XLA_EXT_LINES_STG_GT');
1124 AddMember(G_known_fin1_line_tables, 'FA_ADJUSTMENTS');
1125 AddMember(G_known_fin1_line_tables, 'FA_DISTRIBUTION_HISTORY');
1126 AddMember(G_known_fin1_line_tables, 'FA_LOCATIONS');
1127 AddMember(G_known_fin1_line_tables, 'FA_LOOKUPS');
1128
1129 G_known_fin2_line_tables := G_known_fin1_line_tables;
1130 G_known_xfr_line_tables := G_known_fin1_line_tables;
1131 G_known_dist1_line_tables := G_known_fin1_line_tables;
1132 G_known_dist2_line_tables := G_known_fin1_line_tables;
1133 G_known_ret_line_tables := G_known_fin1_line_tables;
1134
1135 AddMember(G_known_fin1_line_tables, 'FA_ASSET_INVOICES');
1136 AddMember(G_known_fin2_line_tables, 'FA_ASSET_INVOICES');
1137 AddMember(G_known_dist1_line_tables, 'FA_ASSET_HISTORY');
1138 AddMember(G_known_dist1_line_tables, 'FA_CATEGORY_BOOKS');
1139 AddMember(G_known_dist1_line_tables, 'FA_CATEGORIES_B');
1140
1141 AddMember(G_known_ret_line_tables, 'FA_ASSET_INVOICES');
1142 AddMember(G_known_ret_line_tables, 'FA_RETIREMENTS');
1143
1144
1145 -- staging
1146 G_known_stg_tables := fa_char30_tbl_type();
1147
1148 -- standard
1149 AddMember(G_known_stg_tables, 'FA_ADDITIONS_B');
1150 AddMember(G_known_stg_tables, 'FA_ASSET_HISTORY');
1151 AddMember(G_known_stg_tables, 'FA_CATEGORY_BOOKS');
1152 AddMember(G_known_stg_tables, 'FA_BOOK_CONTROLS');
1153 AddMember(G_known_stg_tables, 'FA_TRANSACTION_HEADERS');
1154 AddMember(G_known_stg_tables, 'GL_LEDGERS');
1155 AddMember(G_known_stg_tables, 'XLA_EVENTS_GT');
1156
1157 -- non-standard
1158 AddMember(G_known_stg_tables, 'FA_ASSET_KEYWORDS');
1159 AddMember(G_known_stg_tables, 'FA_CATEGORIES_B');
1160 AddMember(G_known_stg_tables, 'FA_LEASES');
1161 AddMember(G_known_stg_tables, 'FA_METHODS');
1162
1163 -- mls
1164 G_trx_mls_event_class_table := fa_char30_tbl_type();
1165 AddMember(G_trx_mls_event_class_table, 'ADDITIONS');
1166 AddMember(G_trx_mls_event_class_table, 'CIP_ADDITIONS');
1167 AddMember(G_trx_mls_event_class_table, 'ADJUSTMENTS');
1168 AddMember(G_trx_mls_event_class_table, 'CIP_ADJUSTMENTS');
1169 AddMember(G_trx_mls_event_class_table, 'CAPITALIZATION');
1170 AddMember(G_trx_mls_event_class_table, 'REVALUATION');
1171 AddMember(G_trx_mls_event_class_table, 'CIP_REVALUATION');
1172 AddMember(G_trx_mls_event_class_table, 'TRANSFERS');
1173 AddMember(G_trx_mls_event_class_table, 'CIP_TRANSFERS');
1174 AddMember(G_trx_mls_event_class_table, 'CATEGORY_RECLASS');
1175 AddMember(G_trx_mls_event_class_table, 'CIP_CATEGORY_RECLASS');
1176 AddMember(G_trx_mls_event_class_table, 'UNIT_ADJUSTMENTS');
1177 AddMember(G_trx_mls_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1178 AddMember(G_trx_mls_event_class_table, 'RETIREMENTS');
1179 AddMember(G_trx_mls_event_class_table, 'CIP_RETIREMENTS');
1180 AddMember(G_trx_mls_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1181 AddMember(G_trx_mls_event_class_table, 'UNPLANNED_DEPRECIATION');
1182 AddMember(G_trx_mls_event_class_table, 'TERMINAL_GAIN_LOSS');
1183 AddMember(G_trx_mls_event_class_table, 'RETIREMENT_ADJUSTMENT');
1184 AddMember(G_trx_mls_event_class_table, 'SOURCE_LINE_TRANSFERS');
1185 AddMember(G_trx_mls_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1186 AddMember(G_trx_mls_event_class_table, 'RESERVE_TRANSFERS');
1187 AddMember(G_trx_mls_event_class_table, 'DEPRECIATION');
1188 AddMember(G_trx_mls_event_class_table, 'DEFERRED_DEPRECIATION');
1189
1190
1191 G_known_mls_tables := fa_char30_tbl_type();
1192 AddMember(G_known_mls_tables, 'FA_ADDITIONS_TL');
1193 AddMember(G_known_mls_tables, 'FA_CATEGORIES_TL');
1194
1195
1196
1197
1198 G_initialized := TRUE;
1199
1200 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1201 fnd_log.string(G_LEVEL_PROCEDURE,
1202 G_MODULE_NAME||l_procedure_name||'.end',
1203 'End of procedure');
1204 END IF;
1205
1206
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1210 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1211 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1212 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1213 END IF;
1214 RAISE;
1215
1216 end initialize;
1217
1218
1219
1220
1221
1222
1223 --+==========================================================================+
1224 --| |
1225 --| PUBLIC Procedure GenerateSourcesExtract |
1226 --| |
1227 --|
1228 --|
1229 --| valid params:
1230 --| HEADER/DEPRN
1231 --| HEADER/DEF
1232 --| HEADER/TRX1
1233 --| HEADER/TRX2
1234 --|
1235 --| STG/TRX1
1236 --| STG/TRX2
1237 --|
1238 --| LINE/FIN1
1239 --| LINE/FIN2
1240 --| LINE/XFR
1241 --| LINE/DIST1
1242 --| LINE/DIST2
1243 --| LINE/RET
1244 --| LINE/RES
1245 --|
1246 --| LINE/DEPRN
1247 --| LINE/DEF
1248 --|
1249 --| MLS/DEPRN
1250 --| MLS/DEF
1251 --| MLS/TRX
1252 --|
1253 --+==========================================================================+
1254
1255
1256 FUNCTION GenerateSourcesExtract
1257 (p_extract_type IN VARCHAR2, -- dep/trx/def
1258 p_level IN VARCHAR2, -- header/line/stg
1259 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
1260
1261
1262 cursor c_tables (p_tables fa_char30_tbl_type,
1263 p_schemas fa_char30_tbl_type) is
1264 select distinct table_name,
1265 decode(table_name,
1266 -- standard headers/lines
1267 'FA_BOOK_CONTROLS' , 'bc',
1268 'FA_DEPRN_PERIODS' , 'dp',
1269 'XLA_EVENTS_GT' , 'ctgld',
1270 -- standard lines
1271 'FA_ADDITIONS_B' , 'ad',
1272 'FA_ADJUSTMENTS' , 'adj',
1273 'FA_ASSET_HISTORY' , 'ah',
1274 'FA_CATEGORY_BOOKS' , 'cb',
1275 'FA_DISTRIBUTION_HISTORY' , 'dh',
1276 'FA_DEFERRED_DEPRN' , 'df',
1277 'FA_DEPRN_DETAIL' , 'dd',
1278 'FA_LOOKUPS' , 'lu',
1279 'FA_TRANSACTION_HEADERS' , 'th',
1280 'FA_RETIREMENTS' , 'ret',
1281 'FA_XLA_EXT_LINES_STG_GT' , 'stg',
1282 'GL_LEDGERS' , 'le',
1283 -- non-standard
1284 'FA_ADDITIONS_TL' , 'adtl',
1285 'FA_ASSET_INVOICES' , 'ai',
1286 'FA_ASSET_KEYWORDS' , 'key',
1287 'FA_BOOKS' , 'bk',
1288 'FA_CATEGORIES_B' , 'cat',
1289 'FA_CATEGORIES_TL' , 'cattl',
1290 'FA_DEPRN_SUMMARY' , 'ds',
1291 'FA_LEASES' , 'ls',
1292 'FA_LOCATIONS' , 'loc',
1293 'FA_METHODS' , 'mt',
1294 'INVALID')
1295 from all_tables tab,
1296 TABLE(CAST(p_tables AS fa_char30_tbl_type)) fatab,
1297 TABLE(CAST(p_schemas AS fa_char30_tbl_type)) stab
1298 where tab.table_name = fatab.column_value
1299 and tab.owner = stab.column_value;
1300
1301
1302 -- NOTE: we use four versions of this due to the use of the
1303 -- intermediate staging table for trxs...
1304 --
1305 -- 1) for header and all deprn/def line sources,
1306 -- select is as would be expected.
1307 -- 2) for trx staging, we only use known tables within the event classes
1308 -- 3) for trx lines, for sources already in staging table,
1309 -- stg becomes the table/alias and the source_code becomes the column name
1310 -- 4) for mls level, we use line level source but force the
1311 -- values selected to use one of the two known MLS tables
1312
1313 cursor c_sources (p_entity_code VARCHAR2,
1314 p_source_level_code VARCHAR2,
1315 p_event_class_table fa_char30_tbl_type) is
1316 select distinct
1317 sources.source_code,
1318 sources.source_table_name,
1319 sources.source_column_name
1320 from xla_aad_sources aad,
1321 xla_sources_b sources,
1322 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1323 where aad.application_id = 140
1324 and sources.application_id = 140
1325 and aad.entity_code = p_entity_code
1326 and aad.source_level_code = p_source_level_code
1327 and aad.event_class_code = fatab.column_value
1328 and aad.source_code = sources.source_code
1329 and sources.source_table_name is not null
1330 and sources.source_table_name not in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
1331 order by 2,1;
1332
1333 cursor c_sources_stg (p_entity_code VARCHAR2,
1334 p_source_level_code VARCHAR2,
1335 p_event_class_table fa_char30_tbl_type,
1336 p_known_tables fa_char30_tbl_type) is
1337 select distinct
1338 sources.source_code,
1339 sources.source_table_name,
1340 sources.source_column_name
1341 from xla_aad_sources aad,
1342 xla_sources_b sources,
1343 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1344 where aad.application_id = 140
1345 and sources.application_id = 140
1346 and aad.entity_code = p_entity_code
1347 and aad.source_level_code = p_source_level_code
1348 and aad.event_class_code = fatab.column_value
1349 and aad.source_code = sources.source_code
1350 and sources.source_table_name is not null
1351 and sources.source_table_name in
1352 (select fatab2.column_value
1353 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1354 order by 2,1;
1355
1356 cursor c_sources_trx (p_entity_code VARCHAR2,
1357 p_source_level_code VARCHAR2,
1358 p_event_class_table fa_char30_tbl_type,
1359 p_known_tables fa_char30_tbl_type) is
1360 select distinct
1361 sources.source_code,
1362 'FA_XLA_EXT_LINES_STG_GT',
1363 sources.source_code
1364 from xla_aad_sources aad,
1365 xla_sources_b sources,
1366 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1367 where aad.application_id = 140
1368 and sources.application_id = 140
1369 and aad.entity_code = p_entity_code
1370 and aad.source_level_code = p_source_level_code
1371 and aad.event_class_code = fatab.column_value
1372 and aad.source_code = sources.source_code
1373 and sources.source_table_name is not null
1374 and sources.source_table_name not in
1375 (select fatab2.column_value
1376 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1377 union
1378 select distinct
1379 sources.source_code,
1380 sources.source_table_name,
1381 sources.source_column_name
1382 from xla_aad_sources aad,
1383 xla_sources_b sources,
1384 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1385 where aad.application_id = 140
1386 and sources.application_id = 140
1387 and aad.entity_code = p_entity_code
1388 and aad.source_level_code = p_source_level_code
1389 and aad.event_class_code = fatab.column_value
1390 and aad.source_code = sources.source_code
1391 and sources.source_table_name is not null
1392 and sources.source_table_name in
1393 (select fatab2.column_value
1394 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1395 order by 2,1;
1396
1397
1398 cursor c_sources_mls (p_entity_code VARCHAR2,
1399 p_source_level_code VARCHAR2,
1400 p_event_class_table fa_char30_tbl_type) is
1401 select distinct
1402 sources.source_code,
1403 sources.source_table_name,
1404 sources.source_column_name
1405 from xla_aad_sources aad,
1406 xla_sources_b sources,
1407 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1408 where aad.application_id = 140
1409 and sources.application_id = 140
1410 and aad.entity_code = p_entity_code
1411 and aad.source_level_code = p_source_level_code
1412 and aad.event_class_code = fatab.column_value
1413 and aad.source_code = sources.source_code
1414 and sources.source_table_name in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
1415 order by 2,1;
1416
1417 l_insert varchar2(32000);
1418 l_select varchar2(32000);
1419 l_from varchar2(32000);
1420 l_where varchar2(32000);
1421
1422 -- fetching sources
1423 l_source_code v30_tbl;
1424 l_table_name v30_tbl;
1425 l_column_name v30_tbl;
1426 l_alias v30_tbl;
1427
1428 -- uses to fetch known tables / sources
1429 l_table_known v30_tbl;
1430 l_alias_known v30_tbl;
1431
1432 -- used to set to the global constants from initialization
1433 l_known_tables fa_char30_tbl_type;
1434 l_entity_code varchar2(30);
1435 l_event_class_table fa_char30_tbl_type;
1436
1437 l_level varchar2(30);
1438 l_found boolean := false;
1439 l_loop_total number := 1;
1440 l_loop_index number := 1;
1441 l_count number;
1442 l_count2 number;
1443 l_index number;
1444
1445 l_add_tl_in_use boolean := FALSE;
1446
1447 l_array_pkg DBMS_SQL.VARCHAR2S;
1448 l_BodyPkg VARCHAR2(32000);
1449 l_array_body DBMS_SQL.VARCHAR2S;
1450 l_procedure_name varchar2(80) := 'GenerateSourcesExtract';
1451
1452 invalid_mode EXCEPTION;
1453 table_not_found EXCEPTION;
1454
1455 BEGIN
1456
1457 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1458 fnd_log.string(G_LEVEL_PROCEDURE,
1459 G_MODULE_NAME||l_procedure_name||'.begin',
1460 'Beginning of procedure');
1461 END IF;
1462
1463 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
1464 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
1465
1466 l_known_tables := fa_char30_tbl_type();
1467 l_event_class_table := fa_char30_tbl_type();
1468
1469
1470 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1471 fnd_log.string(G_LEVEL_STATEMENT,
1472 G_MODULE_NAME||l_procedure_name,
1473 'p_extract_type: ' || p_extract_type);
1474 fnd_log.string(G_LEVEL_STATEMENT,
1475 G_MODULE_NAME||l_procedure_name,
1476 'p_level: ' || p_level);
1477 END IF;
1478
1479
1480 if (not g_initialized) then
1481 initialize;
1482 end if;
1483
1484 -- load known tables and columns
1485 if (p_level = 'HEADER') then
1486
1487 l_loop_total := 1;
1488 l_level := 'HEADER';
1489
1490 l_insert := c_hdr_insert;
1491 l_select := c_hdr_select;
1492 l_from := c_hdr_from;
1493
1494 if (p_extract_type = 'DEF') then
1495 l_select := l_select || c_hdr_select1;
1496 else
1497 l_select := l_select || c_hdr_select2;
1498 end if;
1499
1500 -- FYI: deprn and deferred do not need additional joins
1501 if (p_extract_type = 'DEPRN') then
1502 l_where := c_hdr_where_deprn;
1503
1504 l_entity_code := 'DEPRECIATION';
1505 l_known_tables := G_known_deprn_hdr_tables;
1506 l_event_class_table := G_deprn_event_class_table;
1507
1508 elsif (p_extract_type = 'DEF') then
1509 l_where := c_hdr_where_def;
1510
1511 l_entity_code := 'DEFERRED_DEPRECIATION';
1512 l_known_tables := G_known_def_hdr_tables;
1513 l_event_class_table := G_def_event_class_table;
1514
1515 elsif (p_extract_type = 'TRX1') then
1516 l_where := c_hdr_where_trx;
1517 l_from := l_from || ',' || fa_cmp_string_pkg.g_chr_newline ||
1518 ' FA_TRANSACTION_HEADERS th ';
1519
1520 l_entity_code := 'TRANSACTIONS';
1521 l_known_tables := G_known_trx_hdr_tables;
1522 l_event_class_table := G_trx1_hdr_event_class_table;
1523
1524 elsif (p_extract_type = 'TRX2') then
1525 l_where := c_hdr_where_itrx;
1526 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline ||
1527 ' FA_TRX_REFERENCES trx ' ;
1528
1529 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1530 l_known_tables := G_known_trx_hdr_tables;
1531 l_event_class_table := G_trx2_hdr_event_class_table;
1532
1533 else
1534 raise invalid_mode;
1535 end if;
1536
1537 elsif (p_level = 'LINE') then
1538
1539 l_loop_total := 2;
1540
1541 l_level := 'LINE';
1542
1543 if (p_extract_type = 'DEPRN') then
1544 l_insert := c_line_insert_deprn;
1545 l_select := c_line_select_deprn;
1546 l_from := c_line_from_deprn;
1547 l_where := c_line_where_deprn;
1548
1549 l_select := replace(l_select, 'select ' ,
1550 'select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ');
1551
1552 l_entity_code := 'DEPRECIATION';
1553 l_known_tables := G_known_deprn_line_tables;
1554 l_event_class_table := G_deprn_event_class_table;
1555
1556 elsif (p_extract_type = 'DEF') then
1557
1558 l_insert := c_line_insert_def;
1559 l_select := c_line_select_def;
1560 l_from := c_line_from_def;
1561 l_where := c_line_where_def;
1562
1563 l_entity_code := 'DEFERRED_DEPRECIATION';
1564 l_known_tables := G_known_def_line_tables;
1565 l_event_class_table := G_def_event_class_table;
1566
1567 elsif (p_extract_type in ('FIN1','FIN2','XFR','DIST1','DIST2','RET','RES')) then
1568
1569 l_insert := c_line_insert_trx;
1570
1571 -- BUG# 7693865
1572 if (p_extract_type = 'DIST1') then
1573 l_select := c_line_select_trx_dist1;
1574 else
1575 l_select := c_line_select_trx;
1576 end if;
1577
1578 l_from := c_line_from_trx;
1579 l_where := c_line_where_trx;
1580
1581 -- NOTE: constants for from clause already include the proceeding comma!!!
1582
1583 if (p_extract_type = 'FIN1') then
1584 l_select := l_select || c_line_adj_amt_fin1;
1585 l_where := l_where || c_line_where_trx_fin1;
1586
1587 l_entity_code := 'TRANSACTIONS';
1588 l_known_tables := G_known_fin1_line_tables;
1589 l_event_class_table := G_fin1_line_event_class_table;
1590
1591 elsif (p_extract_type = 'FIN2') then
1592 l_select := l_select || c_line_adj_amt_fin2;
1593 l_where := l_where || c_line_where_trx_fin2;
1594
1595 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1596 l_known_tables := G_known_fin2_line_tables;
1597 l_event_class_table := G_fin2_line_event_class_table;
1598
1599 elsif (p_extract_type = 'XFR') then
1600 l_select := l_select || c_line_adj_amt_xfr;
1601 l_where := l_where || c_line_where_trx_xfr;
1602
1603 l_entity_code := 'TRANSACTIONS';
1604 l_known_tables := G_known_xfr_line_tables;
1605 l_event_class_table := G_xfr_line_event_class_table;
1606
1607 elsif (p_extract_type = 'DIST1') then
1608 l_select := l_select || c_line_adj_amt_dist1;
1609 l_from := l_from || c_line_from_trx_dist1;
1610 l_where := l_where || c_line_where_trx_dist1;
1611
1612 l_entity_code := 'TRANSACTIONS';
1613 l_known_tables := G_known_dist1_line_tables;
1614 l_event_class_table := G_dist_line_event_class_table;
1615
1616 elsif (p_extract_type = 'DIST2') then
1617 l_select := l_select || c_line_adj_amt_dist2;
1618 l_where := l_where || c_line_where_trx_dist2;
1619
1620 l_entity_code := 'TRANSACTIONS';
1621 l_known_tables := G_known_dist2_line_tables;
1622 l_event_class_table := G_dist_line_event_class_table;
1623
1624 elsif (p_extract_type = 'RET') then
1625 l_select := l_select || c_line_adj_amt_ret;
1626 l_from := l_from || c_line_from_trx_ret;
1627 l_where := l_where || c_line_where_trx_ret;
1628
1629 l_entity_code := 'TRANSACTIONS';
1630 l_known_tables := G_known_ret_line_tables;
1631 l_event_class_table := G_ret_line_event_class_table;
1632
1633 elsif (p_extract_type = 'RES') then
1634 l_select := l_select || c_line_adj_amt_ret;
1635 l_from := l_from || c_line_from_trx_ret;
1636 l_where := l_where || c_line_where_trx_res;
1637
1638 l_entity_code := 'TRANSACTIONS';
1639 l_known_tables := G_known_ret_line_tables;
1640 l_event_class_table := G_ret_line_event_class_table;
1641 else
1642 raise invalid_mode;
1643 end if;
1644
1645 -- perf to insure we lead by gt and use adj_u1,
1646 -- add hint where appropriate
1647 l_select := replace(l_select, 'select ' ,
1648 'select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ ');
1649 else
1650 raise invalid_mode;
1651 end if;
1652
1653 elsif (p_level = 'STG') then
1654
1655 l_loop_total := 1;
1656 l_level := 'LINE';
1657
1658 l_insert := c_line_insert_stg;
1659 l_select := c_line_select_stg;
1660
1661 if (p_extract_type = 'TRX1') then
1662 l_from := c_line_from_stg1;
1663 l_where := c_line_where_stg1;
1664
1665 l_entity_code := 'TRANSACTIONS';
1666 l_known_tables := G_known_stg_tables;
1667 l_event_class_table := G_trx1_hdr_event_class_table;
1668
1669 elsif (p_extract_type = 'TRX2') then
1670
1671 l_select := replace(l_select, 'select ' ,
1672 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ');
1673
1674 l_from := c_line_from_stg2;
1675 l_where := c_line_where_stg2;
1676
1677 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1678 l_known_tables := G_known_stg_tables;
1679 l_event_class_table := G_trx2_hdr_event_class_table;
1680
1681 elsif (p_extract_type = 'TRX3') then
1682
1683 l_select := replace(l_select, 'select ' ,
1684 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ');
1685
1686 l_from := c_line_from_stg2;
1687 l_where := c_line_where_stg3;
1688
1689 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1690 l_known_tables := G_known_stg_tables;
1691 l_event_class_table := G_trx2_hdr_event_class_table;
1692
1693 else
1694 raise invalid_mode;
1695 end if;
1696
1697 elsif (p_level = 'MLS') then
1698 l_loop_total := 1;
1699 l_level := 'LINE';
1700
1701 l_insert := c_mls_insert;
1702 l_select := c_mls_select;
1703 l_from := c_mls_from;
1704 l_where := '';
1705
1706 if (p_extract_type = 'DEPRN') then
1707 l_entity_code := 'DEPRECIATION';
1708 l_known_tables := G_known_mls_tables;
1709 l_event_class_table := G_deprn_event_class_table;
1710
1711 elsif (p_extract_type = 'DEF') then
1712 l_entity_code := 'DEFERRED_DEPRECIATION';
1713 l_known_tables := G_known_mls_tables;
1714 l_event_class_table := G_def_event_class_table;
1715
1716 elsif (p_extract_type = 'TRX') then
1717 l_entity_code := 'TRANSACTIONS';
1718 l_known_tables := G_known_mls_tables;
1719 l_event_class_table := G_trx_mls_event_class_table;
1720 else
1721 raise invalid_mode;
1722 end if;
1723 else
1724 raise invalid_mode;
1725 end if;
1726
1727
1728
1729 -- determine known tables - this will return all known tables we can handle
1730 -- across event classes so if an invalid one is used, we will trap later...
1731 open c_tables (p_tables => l_known_tables,
1732 p_schemas => G_known_schemas);
1733 fetch c_tables bulk collect
1734 into l_table_known,
1735 l_alias_known;
1736 close c_tables;
1737
1738
1739 -- fetch the sources actually used
1740 if ((l_entity_code = 'TRANSACTIONS' or
1741 l_entity_code = 'INTER_ASSET_TRANSACTIONS') and
1742 p_level = 'LINE') then
1743
1744 open c_sources_trx (p_entity_code => l_entity_code,
1745 p_source_level_code => l_level,
1746 p_event_class_table => l_event_class_table,
1747 p_known_tables => l_known_tables);
1748 fetch c_sources_trx bulk collect
1749 into l_source_code,
1750 l_table_name,
1751 l_column_name;
1752 close c_sources_trx;
1753 elsif ((l_entity_code = 'TRANSACTIONS' or
1754 l_entity_code = 'INTER_ASSET_TRANSACTIONS') and
1755 p_level = 'STG') then
1756 open c_sources_stg (p_entity_code => l_entity_code,
1757 p_source_level_code => l_level,
1758 p_event_class_table => l_event_class_table,
1759 p_known_tables => l_known_tables);
1760 fetch c_sources_stg bulk collect
1761 into l_source_code,
1762 l_table_name,
1763 l_column_name;
1764 close c_sources_stg;
1765 elsif (p_level = 'MLS') then
1766
1767 open c_sources_mls (p_entity_code => l_entity_code,
1768 p_source_level_code => l_level,
1769 p_event_class_table => l_event_class_table );
1770 fetch c_sources_mls bulk collect
1771 into l_source_code,
1772 l_table_name,
1773 l_column_name;
1774 close c_sources_mls;
1775
1776
1777 -- for mls, if neither table is in use, return a dummy line to the calling code
1778 if (l_source_code.count = 0) then
1779 l_bodypkg := ' return; ';
1780
1781 fa_cmp_string_pkg.CreateString
1782 (p_package_text => l_BodyPkg
1783 ,p_array_string => l_array_pkg);
1784
1785 p_package_body := l_array_pkg;
1786
1787 return true;
1788 end if;
1789 else
1790 open c_sources (p_entity_code => l_entity_code,
1791 p_source_level_code => l_level,
1792 p_event_class_table => l_event_class_table );
1793 fetch c_sources bulk collect
1794 into l_source_code,
1795 l_table_name,
1796 l_column_name;
1797 close c_sources;
1798 end if;
1799
1800
1801 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1802 fnd_log.string(G_LEVEL_STATEMENT,
1803 G_MODULE_NAME||l_procedure_name,
1804 'l_source_code.count: ' || to_char(l_source_code.count));
1805 END IF;
1806
1807
1808 -- remove all sources already in the base statements
1809 l_count := 0;
1810 l_count2 := l_source_code.count;
1811 l_index := 1;
1812
1813 for i in 1..l_count2 loop
1814
1815 if (instr(upper(l_insert), ' ' || l_source_code(l_index) || ' ') > 0) then -- BUG# 6779783
1816
1817 delete_table_member(l_source_code, l_index);
1818 delete_table_member(l_column_name, l_index);
1819 delete_table_member(l_table_name, l_index);
1820 l_count := l_count + 1;
1821 else
1822 l_index := l_index + 1;
1823 end if;
1824
1825 end loop;
1826
1827 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1828 fnd_log.string(G_LEVEL_STATEMENT,
1829 G_MODULE_NAME||l_procedure_name,
1830 'l_source_code.count after deletion: ' || to_char(l_source_code.count));
1831 END IF;
1832
1833
1834 -- build the alias column array and insure validity
1835 for i in 1..l_table_name.count loop
1836
1837 l_found := false;
1838
1839 for x in 1..l_table_known.count loop
1840 if (l_table_known(x) = l_table_name(i)) then
1841 if (l_table_name(i) <> 'INVALID') then
1842 l_alias(i) := l_alias_known(x);
1843 l_found := true;
1844 end if;
1845 end if;
1846 end loop;
1847
1848 -- if no match found - problem!!!
1849 if (not l_found) then
1850
1851 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1852 fnd_log.string(G_LEVEL_STATEMENT,
1853 G_MODULE_NAME||l_procedure_name,
1854 'no match: l_table_name(i): ' || l_table_name(i) );
1855 fnd_log.string(G_LEVEL_STATEMENT,
1856 G_MODULE_NAME||l_procedure_name,
1857 'no match: l_source_code(i): ' || l_source_code(i) );
1858 END IF;
1859
1860 raise table_not_found;
1861 end if;
1862
1863 end loop;
1864
1865
1866 -- build the insert/select clause by appending new aliases/columns
1867
1868 for i in 1..l_source_code.count loop
1869 l_insert := l_insert || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_source_code(i);
1870 l_select := l_select || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_alias(i) || '.' || l_column_name(i) ;
1871 end loop;
1872
1873
1874 -- only line level can increase the from/to...
1875 -- for transactions where we break into staging vs lines,
1876 -- the local variable for known_table already is restricted to tables we can handle
1877 -- so any violation would have been caught above - no further breakdown needed
1878
1879 if (p_level <> 'HEADER') then
1880
1881 -- find distinct alias/tables for adding to from / where clause
1882 for i in 1..l_table_name.count loop
1883 l_found := false;
1884
1885 -- first look in the existing from clause to see if table is being selected
1886 -- we previously set all the aliases and checked to insure the table names are valid
1887 --
1888 -- note that we need to verify validity here... across not only
1889 -- event classes, but also within transactions across staging vs lines object!!!!
1890 -- finally, if we add to staging, we must by nature add the columns to lines too
1891
1892 if (instr(upper(l_from),l_table_name(i))) = 0 then
1893
1894 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_table_name(i) || ' ' || l_alias(i);
1895
1896 if (l_table_name(i) = 'FA_ASSET_KEYWORDS') then
1897 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1898 ' AND ad.asset_key_ccid = key.code_combination_id(+) ';
1899 elsif (l_table_name(i) = 'FA_ASSET_INVOICES') then
1900 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1901 ' AND adj.source_line_id = ai.source_line_id(+) ';
1902 elsif (l_table_name(i) = 'FA_CATEGORIES_B') then
1903 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1904 ' AND cat.category_id = ah.category_id ';
1905 elsif (l_table_name(i) = 'FA_LEASES') then
1906 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1907 ' AND ad.lease_id = ls.lease_id(+) ';
1908 elsif (l_table_name(i) = 'FA_LOCATIONS') then
1909 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1910 ' AND dh.location_id = loc.location_id ';
1911 elsif (l_table_name(i) = 'FA_METHODS') then
1912 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1913 ' AND mt.method_code = bk.deprn_method_code ';
1914 elsif (l_table_name(i) = 'FA_BOOKS') then
1915 if (p_extract_type = 'DEPRN') then
1916 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1917 ' AND bk.asset_id = dd.asset_id ' || ' ' ||
1918 ' AND bk.book_type_code = dd.book_type_code ' || ' ' ||
1919 ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and ' || ' ' ||
1920 ' nvl(bk.date_ineffective, sysdate) ';
1921 elsif (p_extract_type = 'TRX') then
1922 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1923 ' AND bk.asset_id = th.asset_id ' || ' ' ||
1924 ' AND bk.book_type_code = th.book_type_code ' || ' ' ||
1925 ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and ' || ' ' ||
1926 ' nvl(bk.date_ineffective, sysdate) ';
1927 else -- deferred
1928 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1929 ' AND bk.asset_id = df.asset_id' || ' ' ||
1930 ' AND bk.book_type_code = df.book_type_code' || ' ' ||
1931 ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and' || ' ' ||
1932 ' nvl(bk.date_ineffective, sysdate) ';
1933
1934 end if;
1935 elsif (l_table_name(i) = 'FA_DEPRN_SUMMARY') then
1936 if (p_extract_type = 'DEPRN') then
1937 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1938 ' AND ds.asset_id = ctlgd.source_id_int_1 ' || ' ' ||
1939 ' AND ds.book_type_code = ctlgd.source_id_char_1 ' || ' ' ||
1940 ' AND ds.period_counter = ctlgd.source_id_int_2 ' || ' ' ||
1941 ' AND ds.deprn_run_id = ctlgd.source_id_int_3 ';
1942
1943 else
1944 raise table_not_found;
1945 end if;
1946 elsif (l_table_name(i) = 'FA_ADDITIONS_TL') then
1947 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1948 ' WHERE adtl.asset_id = xl.asset_id ';
1949 l_add_tl_in_use := TRUE;
1950 elsif (l_table_name(i) = 'FA_CATEGORIES_TL') then
1951 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1952 ' WHERE cattl.category_id = xl.cat_id ';
1953 if (l_add_tl_in_use) then
1954 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
1955 ' AND cattl.language = adtl.language ';
1956 l_where := replace(l_where, 'WHERE cattl', 'AND cattl');
1957
1958 end if;
1959 end if;
1960 end if;
1961 end loop;
1962 end if;
1963
1964
1965 -- loop (if applicable) for generating both the primary and the reporting statements
1966 -- we only loop for lines (hdr/stg are single)
1967
1968 for l_loop_index in 1..l_loop_total loop
1969
1970 -- for mrc insert the MC_ prefix
1971
1972 if (l_loop_index = 2) then
1973
1974 -- alter index name to have MC
1975 l_select := replace(l_select, 'FA_ADJUSTMENTS_U1' ,
1976 'FA_MC_ADJUSTMENTS_U1') ;
1977
1978 l_from := replace(l_from, 'fa_book_controls', 'fa_mc_book_controls');
1979
1980 if (p_extract_type = 'DEPRN') then
1981 l_from := replace(l_from, 'fa_deprn_summary', 'fa_mc_deprn_summary');
1982 l_from := replace(l_from, 'fa_deprn_detail', 'fa_mc_deprn_detail');
1983
1984 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
1985 ' AND dd.set_of_books_id = bc.set_of_books_id';
1986
1987 -- only apend the DS clause if it's used!!!!
1988 if (instr(l_from,'fa_mc_deprn_summary') > 0) then
1989 l_where := l_where || ' and ds.set_of_books_id = bc.set_of_books_id' || fa_cmp_string_pkg.g_chr_newline ;
1990 end if;
1991 elsif (p_extract_type = 'DEF') then
1992 l_from := replace(l_from, 'fa_deferred_deprn', 'fa_mc_deferred_deprn');
1993
1994 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
1995 ' AND df.set_of_books_id = bc.set_of_books_id';
1996 else
1997 if (instr(l_from,'fa_book_controls') = 0) then
1998 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' fa_mc_book_controls bc ';
1999 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' gl_ledgers le ';
2000
2001 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2002 ' AND bc.book_type_code = stg.book_type_code ' || fa_cmp_string_pkg.g_chr_newline ||
2003 ' AND bc.set_of_books_id = le.ledger_id ';
2004
2005 end if;
2006
2007 l_select := replace(l_select, 'stg.ledger_id', 'bc.set_of_books_id');
2008 l_select := replace(l_select, 'stg.currency_code', 'le.currency_code');
2009
2010 l_from := replace(l_from, 'fa_adjustments', 'fa_mc_adjustments');
2011
2012 -- Bug 5159010 changed fa_asset_invoices to upper case
2013 l_from := replace(l_from, 'FA_ASSET_INVOICES', 'fa_mc_asset_invoices');
2014
2015 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2016 ' AND adj.set_of_books_id = bc.set_of_books_id ' ;
2017
2018 -- only apend the AI clause if it's used!!!!
2019 if (instr(l_from,'fa_mc_asset_invoices') > 0) then
2020 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2021 ' AND adj.set_of_books_id = ai.set_of_books_id(+) ' ;
2022 end if;
2023
2024 end if;
2025
2026 end if;
2027
2028 -- concatonate all the clauses into a single statment
2029 l_bodypkg := l_insert || l_select || l_from || l_where || ';' || fa_cmp_string_pkg.g_chr_newline ;
2030
2031 -- add rowcount debug after the primary statement and before report select
2032 -- also add an if condition around the mrc so we don't needlessly execute statements when mrc is not enabled
2033 if (l_loop_index = 2) then
2034 l_bodypkg := c_line_rowcount_debug || fa_cmp_string_pkg.g_chr_newline ||
2035 c_mc_if_condition || fa_cmp_string_pkg.g_chr_newline ||
2036 l_bodypkg || fa_cmp_string_pkg.g_chr_newline ||
2037 ' end if; ' || fa_cmp_string_pkg.g_chr_newline ;
2038 end if;
2039
2040 -- build the package value to return
2041
2042 if (l_loop_index = 1) then
2043 fa_cmp_string_pkg.CreateString
2044 (p_package_text => l_BodyPkg
2045 ,p_array_string => l_array_pkg);
2046 else
2047 fa_cmp_string_pkg.CreateString
2048 (p_package_text => l_BodyPkg
2049 ,p_array_string => l_array_body);
2050
2051 l_array_pkg :=
2052 fa_cmp_string_pkg.ConcatTwoStrings
2053 (p_array_string_1 => l_array_pkg
2054 ,p_array_string_2 => l_array_body);
2055 end if;
2056
2057 end loop;
2058
2059 p_package_body := l_array_pkg;
2060
2061 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2062 fnd_log.string(G_LEVEL_PROCEDURE,
2063 G_MODULE_NAME||l_procedure_name||'.end',
2064 'End of procedure');
2065 END IF;
2066
2067 RETURN TRUE;
2068
2069 EXCEPTION
2070
2071 WHEN invalid_mode THEN
2072 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2073 fnd_log.string(G_LEVEL_STATEMENT,
2074 G_MODULE_NAME||l_procedure_name,
2075 'invalid mode');
2076 END IF;
2077 RETURN FALSE;
2078
2079 WHEN table_not_found THEN
2080 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2081 fnd_log.string(G_LEVEL_STATEMENT,
2082 G_MODULE_NAME||l_procedure_name,
2083 'table not found');
2084 END IF;
2085 RETURN FALSE;
2086
2087 WHEN OTHERS THEN
2088 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2089 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2090 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2091 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2092 END IF;
2093 RETURN FALSE;
2094
2095 END GenerateSourcesExtract;
2096
2097 END fa_xla_cmp_sources_pkg;