[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.38.12020000.4 2012/11/30 11:50:31 spooyath 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 g_select VARCHAR2(32000);
119 g_where VARCHAR2(32000);
120 g_rowcount_debug VARCHAR2(32000);
121
122 -- header level constant values
123
124 c_hdr_insert CONSTANT VARCHAR2(32000) := '
125 insert into fa_xla_ext_headers_b_gt (
126 event_id ,
127 DEFAULT_CCID ,
128 BOOK_TYPE_CODE ,
129 PERIOD_NAME ,
130 PERIOD_CLOSE_DATE ,
131 PERIOD_COUNTER ,
132 ACCOUNTING_DATE ,
133 TRANSFER_TO_GL_FLAG ';
134
135 c_hdr_select CONSTANT VARCHAR2(32000) := ' )
136 select ctlgd.event_id,
137 bc.FLEXBUILDER_DEFAULTS_CCID ,
138 bc.book_type_code ,
139 dp.PERIOD_NAME ,
140 dp.CALENDAR_PERIOD_CLOSE_DATE ,
141 dp.PERIOD_COUNTER ,
142 ctlgd.event_date ,';
143
144 c_hdr_select1 CONSTANT VARCHAR2(32000) := '
145 ''Y'' ' ;
146
147 c_hdr_select2 CONSTANT VARCHAR2(32000) := '
148 decode(bc.GL_POSTING_ALLOWED_FLAG ,
149 ''YES'', ''Y'',''N'') ';
150
151 c_hdr_from CONSTANT VARCHAR2(32000) := '
152 FROM xla_events_gt ctlgd,
153 fa_deprn_periods dp,
154 fa_book_controls bc ';
155
156 c_hdr_where_trx CONSTANT VARCHAR2(32000) := '
157 WHERE ctlgd.entity_code = ''TRANSACTIONS''
158 AND ctlgd.event_type_code <> ''INFLATION_REVALUATION''
159 AND th.transaction_header_id = ctlgd.source_id_int_1
160 AND ctlgd.valuation_method = dp.book_type_code
161 AND ctlgd.valuation_method = bc.book_type_code
162 AND th.date_effective between dp.period_open_date and
163 nvl(dp.period_close_date, sysdate) ';
164
165 c_hdr_where_itrx CONSTANT VARCHAR2(32000) := '
166 WHERE ctlgd.entity_code = ''INTER_ASSET_TRANSACTIONS''
167 AND trx.trx_reference_id = ctlgd.source_id_int_1
168 AND trx.event_id = ctlgd.event_id
169 AND trx.book_type_code = dp.book_type_code
170 AND trx.book_type_code = bc.book_type_code
171 AND dp.book_type_code = trx.book_type_code
172 AND trx.creation_date between dp.period_open_date and
173 nvl(dp.period_close_date, sysdate) ';
174
175 c_hdr_where_deprn CONSTANT VARCHAR2(32000) := '
176 WHERE ctlgd.entity_code = ''DEPRECIATION''
177 AND ctlgd.event_type_code = ''DEPRECIATION''
178 AND dp.book_type_code = ctlgd.source_id_char_1
179 AND dp.period_counter = ctlgd.source_id_int_2
180 AND bc.book_type_code = ctlgd.source_id_char_1';
181
182 c_hdr_where_def CONSTANT VARCHAR2(32000) := '
183 WHERE ctlgd.entity_code = ''DEFERRED_DEPRECIATION''
184 AND ctlgd.event_type_code = ''DEFERRED_DEPRECIATION''
185 AND bc.book_type_code = ctlgd.source_id_char_1
186 AND dp.book_type_code = ctlgd.source_id_char_1
187 AND dp.period_counter = ctlgd.source_id_int_2 ';
188
189 -- line level constant values
190
191 -- deprn
192
193 c_line_insert_deprn CONSTANT VARCHAR2(32000) := '
194 insert into fa_xla_ext_lines_b_gt (
195 EVENT_ID ,
196 LINE_NUMBER ,
197 DISTRIBUTION_ID ,
198 DISTRIBUTION_TYPE_CODE ,
199 LEDGER_ID ,
200 CURRENCY_CODE ,
201 CAT_ID ,
202 ENTERED_AMOUNT ,
203 BONUS_ENTERED_AMOUNT ,
204 REVAL_ENTERED_AMOUNT ,
205 GENERATED_CCID ,
206 GENERATED_OFFSET_CCID ,
207 BONUS_GENERATED_CCID ,
208 BONUS_GENERATED_OFFSET_CCID ,
209 REVAL_GENERATED_CCID ,
210 REVAL_GENERATED_OFFSET_CCID ,
211 RESERVE_ACCOUNT_CCID ,
212 DEPRN_EXPENSE_ACCOUNT_CCID ,
213 BONUS_RESERVE_ACCT_CCID ,
214 BONUS_EXPENSE_ACCOUNT_CCID ,
215 REVAL_AMORT_ACCOUNT_CCID ,
216 REVAL_RESERVE_ACCOUNT_CCID ,
217 IMPAIR_EXPENSE_ACCOUNT_CCID ,
218 IMPAIR_RESERVE_ACCOUNT_CCID ,
219 CAPITAL_ADJ_ACCOUNT_CCID ,
220 GENERAL_FUND_ACCOUNT_CCID ,
221 BOOK_TYPE_CODE ,
222 PERIOD_COUNTER '; -- Bug:6399642
223
224 c_line_select_deprn CONSTANT VARCHAR2(32000) := ' )
225 select ctlgd.EVENT_ID ,
226 dd.distribution_id as distribution_id,
227 dd.distribution_id as dist_id,
228 ''DEPRN'' ,
229 bc.set_of_books_id ,
230 le.currency_code ,
231 cb.category_id ,
232 dd.deprn_amount
233 - dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
234 dd.bonus_deprn_amount
235 - dd.bonus_deprn_adjustment_amount ,
236 dd.reval_amortization ,
237 dd.deprn_expense_ccid ,
238 dd.deprn_reserve_ccid ,
239 dd.bonus_deprn_expense_ccid ,
240 dd.bonus_deprn_reserve_ccid ,
241 dd.reval_amort_ccid ,
242 dd.reval_reserve_ccid ,
243 cb.RESERVE_ACCOUNT_CCID ,
244 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
245 cb.BONUS_RESERVE_ACCT_CCID ,
246 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
247 cb.REVAL_AMORT_ACCOUNT_CCID ,
248 cb.REVAL_RESERVE_ACCOUNT_CCID ,
249 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
250 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
251 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
252 cb.GENERAL_FUND_ACCOUNT_CCID ,
253 ctlgd.source_id_char_1 ,
254 dp.period_counter '; -- Bug:8702451
255
256 c_line_from_deprn CONSTANT VARCHAR2(32000) := '
257 from xla_events_gt ctlgd,
258 fa_deprn_detail dd,
259 fa_distribution_history dh,
260 fa_additions_b ad,
261 fa_asset_history ah,
262 fa_category_books cb,
263 fa_book_controls bc,
264 gl_ledgers le,
265 fa_deprn_periods dp '; -- Bug 8702451
266
267 -- NOTE: we do not post track or zero lines
268 -- will taken care of in preprocessing hook (check track)
269
270 c_line_where_deprn CONSTANT VARCHAR2(32000) := '
271 where ctlgd.entity_code = ''DEPRECIATION''
272 AND ctlgd.event_type_code = ''DEPRECIATION''
273 AND dd.asset_id = ctlgd.source_id_int_1
274 AND dd.book_type_code = ctlgd.source_id_char_1
275 AND dd.period_counter = ctlgd.source_id_int_2
276 AND dd.deprn_run_id = ctlgd.source_id_int_3
277 AND ad.asset_id = ctlgd.source_id_int_1
278 AND dd.distribution_id = dh.distribution_id
279 AND ah.asset_id = ctlgd.source_id_int_1
280 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
281 AND nvl(DH.Date_ineffective, SYSDATE) <=
282 nvl(AH.Date_ineffective, SYSDATE)
283 AND cb.category_id = ah.category_id
284 AND cb.book_type_code = ctlgd.source_id_char_1
285 AND ah.asset_type in (''CAPITALIZED'', ''GROUP'')
286 AND ad.asset_type in (''CAPITALIZED'', ''GROUP'')
287 AND bc.book_type_code = ctlgd.source_id_char_1
288 AND le.ledger_id = bc.set_of_books_id
289 AND dp.book_type_code = ctlgd.source_id_char_1
290 AND dp.period_counter = ctlgd.source_id_int_2 ';
291
292
293
294 -- deferred
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 CAT_ID ,
305 ENTERED_AMOUNT ,
306 BOOK_TYPE_CODE ,
307 TAX_BOOK_TYPE_CODE ,
308 GENERATED_CCID ,
309 GENERATED_OFFSET_CCID ';
310
311 c_line_select_def CONSTANT VARCHAR2(32000) := ' )
312 select ctlgd.EVENT_ID ,
313 df.distribution_id as distribution_id,
314 df.distribution_id as dist_id,
315 ''DEFERRED'' ,
316 bc.set_of_books_id ,
317 le.currency_code ,
318 ah.category_id ,
319 df.deferred_deprn_expense_amount ,
320 df.corp_book_type_code ,
321 df.tax_book_type_code ,
322 df.deferred_deprn_expense_ccid ,
323 df.deferred_deprn_reserve_ccid ';
324
325 c_line_from_def CONSTANT VARCHAR2(32000) := '
326 from fa_additions_b ad,
327 fa_asset_history ah,
328 fa_book_controls bc,
329 fa_category_books cb,
330 fa_distribution_history dh,
331 fa_deferred_deprn df,
332 gl_ledgers le,
333 xla_events_gt ctlgd ';
334
335 c_line_where_def CONSTANT VARCHAR2(32000) := '
336 where ctlgd.entity_code = ''DEFERRED_DEPRECIATION''
337 AND ctlgd.event_type_code = ''DEFERRED_DEPRECIATION''
338 AND df.asset_id = ctlgd.source_id_int_1
339 AND df.corp_book_type_code = ctlgd.source_id_char_1
340 AND df.corp_period_counter = ctlgd.source_id_int_2
341 AND df.tax_book_type_code = ctlgd.source_id_char_2
342 AND df.event_id = ctlgd.event_id
343 AND ad.asset_id = ctlgd.source_id_int_1
344 AND dh.distribution_id = df.distribution_id
345 AND ah.asset_id = ctlgd.source_id_int_1
346 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
347 AND nvl(DH.Date_ineffective, SYSDATE) <=
348 nvl(AH.Date_ineffective, SYSDATE)
349 AND cb.category_id = ah.category_id
350 AND cb.book_type_code = ctlgd.source_id_char_1
351 AND ah.asset_type in (''CAPITALIZED'', ''GROUP'')
352 AND ad.asset_type in (''CAPITALIZED'', ''GROUP'')
353 AND bc.book_type_code = ctlgd.source_id_char_1
354 AND le.ledger_id = bc.set_of_books_id ';
355
356
357 -- trx-staging
358
359 c_line_insert_stg CONSTANT VARCHAR2(32000) := '
360 insert into fa_xla_ext_lines_stg_gt (
361 EVENT_ID ,
362 EVENT_TYPE_CODE ,
363 TRANSACTION_HEADER_ID ,
364 MEMBER_TRANSACTION_HEADER_ID ,
365 DISTRIBUTION_TYPE_CODE ,
366 BOOK_TYPE_CODE ,
367 LEDGER_ID ,
368 CURRENCY_CODE ,
369 CAT_ID ,
370 ASSET_TYPE ,
371 ASSET_COST_ACCOUNT_CCID ,
372 ASSET_CLEARING_ACCOUNT_CCID ,
373 CIP_COST_ACCOUNT_CCID ,
374 CIP_CLEARING_ACCOUNT_CCID ,
375 RESERVE_ACCOUNT_CCID ,
376 DEPRN_EXPENSE_ACCOUNT_CCID ,
377 BONUS_RESERVE_ACCT_CCID ,
378 BONUS_EXPENSE_ACCOUNT_CCID ,
379 REVAL_AMORT_ACCOUNT_CCID ,
380 REVAL_RESERVE_ACCOUNT_CCID ,
381 UNPLAN_EXPENSE_ACCOUNT_CCID ,
382 ALT_COST_ACCOUNT_CCID ,
383 WRITE_OFF_ACCOUNT_CCID ,
384 IMPAIR_EXPENSE_ACCOUNT_CCID ,
385 IMPAIR_RESERVE_ACCOUNT_CCID ,
386 CAPITAL_ADJ_ACCOUNT_CCID ,
387 GENERAL_FUND_ACCOUNT_CCID ,
388 DEPRN_EXPENSE_ACCT ';
389
390 c_line_select_stg CONSTANT VARCHAR2(32000) := ' )
391 select ctlgd.EVENT_ID ,
392 ctlgd.event_type_code ,
393 th.transaction_header_id ,
394 nvl(th.member_transaction_header_id,
395 th.transaction_header_id) ,
396 ''TRX'' ,
397 bc.book_type_code , -- Bug:6272229
398 bc.set_of_books_id ,
399 le.currency_code ,
400 cb.category_id ,
401 ah.asset_type ,
402 cb.ASSET_COST_ACCOUNT_CCID ,
403 cb.ASSET_CLEARING_ACCOUNT_CCID ,
404 cb.WIP_COST_ACCOUNT_CCID ,
405 cb.WIP_CLEARING_ACCOUNT_CCID ,
406 cb.RESERVE_ACCOUNT_CCID ,
407 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
408 cb.BONUS_RESERVE_ACCT_CCID ,
409 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
410 cb.REVAL_AMORT_ACCOUNT_CCID ,
411 cb.REVAL_RESERVE_ACCOUNT_CCID ,
412 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
413 cb.ALT_COST_ACCOUNT_CCID ,
414 cb.WRITE_OFF_ACCOUNT_CCID ,
415 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
416 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
417 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
418 cb.GENERAL_FUND_ACCOUNT_CCID ,
419 cb.DEPRN_EXPENSE_ACCT ';
420
421 c_line_from_stg1 CONSTANT VARCHAR2(32000) := '
422 from fa_additions_b ad,
423 fa_asset_history ah,
424 fa_book_controls bc,
425 fa_category_books cb,
426 gl_ledgers le,
427 fa_transaction_headers th,
428 xla_events_gt ctlgd ';
429
430 c_line_from_stg2 CONSTANT VARCHAR2(32000) := '
431 from fa_additions_b ad,
432 fa_asset_history ah,
433 fa_book_controls bc,
434 fa_category_books cb,
435 gl_ledgers le,
436 fa_transaction_headers th,
437 fa_trx_references trx,
438 xla_events_gt ctlgd ';
439
440 c_line_where_stg1 CONSTANT VARCHAR2(32000) := '
441 where ctlgd.entity_code = ''TRANSACTIONS''
442 AND bc.book_type_code = ctlgd.valuation_method
443 AND le.ledger_id = bc.set_of_books_id
444 AND ad.asset_id = th.asset_id
445 AND ah.asset_id = th.asset_id
446 AND th.transaction_header_id between ah.transaction_header_id_in and
447 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
448 AND cb.category_id = ah.category_id
449 AND cb.book_type_code = ctlgd.valuation_method
450 AND ah.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'')
451 AND ad.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'') ';
452
453 c_line_where_stg1a CONSTANT VARCHAR2(32000) := '
454 AND th.transaction_header_id = ctlgd.source_id_int_1 ';
455
456 c_line_where_stg1b CONSTANT VARCHAR2(32000) := '
457 AND th.member_transaction_header_id = ctlgd.source_id_int_1 ';
458
459 c_line_where_stg2 CONSTANT VARCHAR2(32000) := '
460 where ctlgd.entity_code = ''INTER_ASSET_TRANSACTIONS''
461 AND trx.trx_reference_id = ctlgd.source_id_int_1
462 AND bc.book_type_code = ctlgd.valuation_method
463 AND le.ledger_id = bc.set_of_books_id
464 AND ad.asset_id = th.asset_id
465 AND ah.asset_id = th.asset_id
466 AND th.transaction_header_id between ah.transaction_header_id_in and
467 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
468 AND cb.category_id = ah.category_id
469 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
470 AND ah.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'')
471 AND ad.asset_type in (''CAPITALIZED'', ''CIP'', ''GROUP'') ';
472
473 c_line_where_stg2a CONSTANT VARCHAR2(32000) := '
474 AND th.transaction_header_id = trx.src_transaction_header_id ';
475
476 c_line_where_stg2b CONSTANT VARCHAR2(32000) := '
477 AND th.member_transaction_header_id = trx.src_transaction_header_id ';
478
479 c_line_where_stg2c CONSTANT VARCHAR2(32000) := '
480 AND th.transaction_header_id = trx.dest_transaction_header_id ';
481
482 c_line_where_stg2d CONSTANT VARCHAR2(32000) := '
483 AND th.member_transaction_header_id = trx.dest_transaction_header_id ';
484
485 -- trx - lines
486
487 c_line_insert_trx CONSTANT VARCHAR2(32000) := '
488 insert into fa_xla_ext_lines_b_gt (
489 EVENT_ID ,
490 LINE_NUMBER ,
491 DISTRIBUTION_ID ,
492 DISTRIBUTION_TYPE_CODE ,
493 LEDGER_ID ,
494 CURRENCY_CODE ,
495 BOOK_TYPE_CODE ,
496 GENERATED_CCID ,
497 ASSET_ID ,
498 CAT_ID ,
499 ASSET_TYPE ,
500 ASSET_COST_ACCOUNT_CCID ,
501 ASSET_CLEARING_ACCOUNT_CCID ,
502 CIP_COST_ACCOUNT_CCID ,
503 CIP_CLEARING_ACCOUNT_CCID ,
504 RESERVE_ACCOUNT_CCID ,
505 DEPRN_EXPENSE_ACCOUNT_CCID ,
506 BONUS_RESERVE_ACCT_CCID ,
507 BONUS_EXPENSE_ACCOUNT_CCID ,
508 REVAL_AMORT_ACCOUNT_CCID ,
509 REVAL_RESERVE_ACCOUNT_CCID ,
510 UNPLAN_EXPENSE_ACCOUNT_CCID ,
511 ALT_COST_ACCOUNT_CCID ,
512 WRITE_OFF_ACCOUNT_CCID ,
513 DEPRN_EXPENSE_ACCT ,
514 IMPAIR_EXPENSE_ACCOUNT_CCID ,
515 IMPAIR_RESERVE_ACCOUNT_CCID ,
516 CAPITAL_ADJ_ACCOUNT_CCID ,
517 GENERAL_FUND_ACCOUNT_CCID ,
518 ENTERED_AMOUNT '; -- Bug:6399642
519
520 c_line_select_trx CONSTANT VARCHAR2(32000) := ' )
521 select stg.EVENT_ID ,
522 adj.adjustment_line_id ,
523 adj.distribution_id ,
524 stg.distribution_type_code ,
525 stg.ledger_id ,
526 stg.currency_code ,
527 stg.book_type_code ,
528 adj.code_combination_id ,
529 adj.asset_id ,
530 stg.cat_id ,
531 stg.asset_type ,
532 stg.ASSET_COST_ACCOUNT_CCID ,
533 stg.ASSET_CLEARING_ACCOUNT_CCID ,
534 stg.CIP_COST_ACCOUNT_CCID ,
535 stg.CIP_CLEARING_ACCOUNT_CCID ,
536 stg.RESERVE_ACCOUNT_CCID ,
537 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
538 stg.BONUS_RESERVE_ACCT_CCID ,
539 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
540 stg.REVAL_AMORT_ACCOUNT_CCID ,
541 stg.REVAL_RESERVE_ACCOUNT_CCID ,
542 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
543 stg.ALT_COST_ACCOUNT_CCID ,
544 stg.WRITE_OFF_ACCOUNT_CCID ,
545 stg.DEPRN_EXPENSE_ACCT ,
546 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
547 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
548 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
549 stg.GENERAL_FUND_ACCOUNT_CCID ,';
550
551 -- BUG# 7693865
552 c_line_select_trx_dist1 CONSTANT VARCHAR2(32000) := ' )
553 select stg.EVENT_ID ,
554 adj.adjustment_line_id ,
555 adj.distribution_id ,
556 stg.distribution_type_code ,
557 stg.ledger_id ,
558 stg.currency_code ,
559 stg.book_type_code ,
560 adj.code_combination_id ,
561 adj.asset_id ,
562 cb.category_id ,
563 stg.asset_type ,
564 cb.ASSET_COST_ACCOUNT_CCID ,
565 cb.ASSET_CLEARING_ACCOUNT_CCID ,
566 cb.WIP_COST_ACCOUNT_CCID ,
567 cb.WIP_CLEARING_ACCOUNT_CCID ,
568 cb.RESERVE_ACCOUNT_CCID ,
569 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
570 cb.BONUS_RESERVE_ACCT_CCID ,
571 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
572 cb.REVAL_AMORT_ACCOUNT_CCID ,
573 cb.REVAL_RESERVE_ACCOUNT_CCID ,
574 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
575 cb.ALT_COST_ACCOUNT_CCID ,
576 cb.WRITE_OFF_ACCOUNT_CCID ,
577 cb.DEPRN_EXPENSE_ACCT ,
578 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
579 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
580 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
581 cb.GENERAL_FUND_ACCOUNT_CCID ,';
582
583 -- adjustment_amount decode handling
584
585 c_line_adj_amt_fin1 CONSTANT VARCHAR2(32000) := '
586 decode(adj.adjustment_type,
587 ''COST CLEARING'',
588 decode(debit_credit_flag,
589 ''CR'', adjustment_amount,
590 -1 * adjustment_amount),
591 ''RESERVE'',
592 decode(debit_credit_flag,
593 ''CR'', adjustment_amount,
594 -1 * adjustment_amount),
595 ''BONUS RESERVE'',
596 decode(debit_credit_flag,
597 ''CR'', adjustment_amount,
598 -1 * adjustment_amount),
599 ''REVAL RESERVE'',
600 decode(debit_credit_flag,
601 ''CR'', adjustment_amount,
602 -1 * adjustment_amount),
603 ''CIP COST'',
604 decode(stg.event_type_code,
605 ''CAPITALIZATION'',
606 decode(debit_credit_flag,
607 ''CR'', adjustment_amount,
608 -1 * adjustment_amount),
609 ''REVERSE_CAPITALIZATION'',
610 decode(debit_credit_flag,
611 ''CR'', adjustment_amount,
612 -1 * adjustment_amount),
613 decode(debit_credit_flag,
614 ''DR'', adjustment_amount,
615 -1 * adjustment_amount)),
616 ''COST'',
617 decode(debit_credit_flag,
618 ''DR'', adjustment_amount,
619 -1 * adjustment_amount),
620 ''EXPENSE'',
621 decode(debit_credit_flag,
622 ''DR'', adjustment_amount,
623 -1 * adjustment_amount),
624 ''BONUS EXPENSE'',
625 decode(debit_credit_flag,
626 ''DR'', adjustment_amount,
627 -1 * adjustment_amount),
628 ''NBV RETIRED'',
629 decode(debit_credit_flag,
630 ''DR'', adjustment_amount,
631 -1 * adjustment_amount),
632 ''PROCEEDS CLR'',
633 decode(debit_credit_flag,
634 ''DR'', adjustment_amount,
635 -1 * adjustment_amount),
636 ''REMOVALCOST CLR'',
637 decode(debit_credit_flag,
638 ''CR'', adjustment_amount,
639 -1 * adjustment_amount),
640 ''IMPAIR EXPENSE'',
641 decode(debit_credit_flag,
642 ''DR'',adjustment_amount,
643 -1 * adjustment_amount),
644 ''IMPAIR RESERVE'',
645 decode(debit_credit_flag,
646 ''CR'',adjustment_amount,
647 -1 * adjustment_amount),
648 ''CAPITAL ADJ'',
649 decode(debit_credit_flag,
650 ''DR'',adjustment_amount,
651 -1 * adjustment_amount),
652 ''GENERAL FUND'',
653 decode(debit_credit_flag,
654 ''CR'',adjustment_amount,
655 -1 * adjustment_amount),
656 ''LINK IMPAIR EXP'',
657 decode(debit_credit_flag,
658 ''CR'',adjustment_amount,
659 -1 * adjustment_amount),
660 ''REVAL LOSS'',
661 decode(debit_credit_flag,
662 ''DR'',adjustment_amount,
663 -1 * adjustment_amount),
664 ''IMPAIR OFF EXP'',
665 decode(debit_credit_flag,
666 ''DR'',adjustment_amount,
667 -1 * adjustment_amount),
668 ''REVAL OFF EXP'',
669 decode(debit_credit_flag,
670 ''DR'',adjustment_amount,
671 -1 * adjustment_amount),
672 decode(debit_credit_flag,
673 ''DR'', adjustment_amount,
674 -1 * adjustment_amount)) ';
675
676
677 c_line_adj_amt_fin2 CONSTANT VARCHAR2(32000) := '
678 decode(adj.source_dest_code,
679 ''SOURCE'',
680 decode(adj.adjustment_type,
681 ''RESERVE'',
682 decode(debit_credit_flag,
683 ''DR'', adjustment_amount,
684 -1 * adjustment_amount),
685 ''BONUS RESERVE'',
686 decode(debit_credit_flag,
687 ''DR'', adjustment_amount,
688 -1 * adjustment_amount),
689 ''REVAL RESERVE'',
690 decode(debit_credit_flag,
691 ''DR'', adjustment_amount,
692 -1 * adjustment_amount),
693 ''IMPAIR RESERVE'',
694 decode(debit_credit_flag,
695 ''DR'', adjustment_amount,
696 -1 * adjustment_amount),
697 ''CAPITAL ADJ'',
698 decode(debit_credit_flag,
699 ''CR'', adjustment_amount,
700 -1 * adjustment_amount),
701 ''GENERAL FUND'',
702 decode(debit_credit_flag,
703 ''DR'', adjustment_amount,
704 -1 * adjustment_amount),
705 decode(debit_credit_flag,
706 ''CR'', adjustment_amount,
707 -1 * adjustment_amount)),
708 decode(adj.adjustment_type,
709 ''RESERVE'',
710 decode(debit_credit_flag,
711 ''CR'', adjustment_amount,
712 -1 * adjustment_amount),
713 ''BONUS RESERVE'',
714 decode(debit_credit_flag,
715 ''CR'', adjustment_amount,
716 -1 * adjustment_amount),
717 ''REVAL RESERVE'',
718 decode(debit_credit_flag,
719 ''CR'', adjustment_amount,
720 -1 * adjustment_amount),
721 ''IMPAIR RESERVE'',
722 decode(debit_credit_flag,
723 ''CR'', adjustment_amount,
724 -1 * adjustment_amount),
725 ''CAPITAL ADJ'',
726 decode(debit_credit_flag,
727 ''DR'', adjustment_amount,
728 -1 * adjustment_amount),
729 ''GENERAL FUND'',
730 decode(debit_credit_flag,
731 ''CR'', adjustment_amount,
732 -1 * adjustment_amount),
733 decode(debit_credit_flag,
734 ''DR'', adjustment_amount,
735 -1 * adjustment_amount))) ';
736
737 c_line_adj_amt_xfr CONSTANT VARCHAR2(32000) := '
738 decode(adj.source_dest_code,
739 ''SOURCE'',
740 decode(adj.adjustment_type,
741 ''RESERVE'',
742 decode(debit_credit_flag,
743 ''DR'', adjustment_amount,
744 -1 * adjustment_amount),
745 ''BONUS RESERVE'',
746 decode(debit_credit_flag,
747 ''DR'', adjustment_amount,
748 -1 * adjustment_amount),
749 ''REVAL RESERVE'',
750 decode(debit_credit_flag,
751 ''DR'', adjustment_amount,
752 -1 * adjustment_amount),
753 ''IMPAIR RESERVE'',
754 decode(debit_credit_flag,
755 ''DR'', adjustment_amount,
756 -1 * adjustment_amount),
757 ''CAPITAL ADJ'',
758 decode(debit_credit_flag,
759 ''CR'', adjustment_amount,
760 -1 * adjustment_amount),
761 ''GENERAL FUND'',
762 decode(debit_credit_flag,
763 ''DR'', adjustment_amount,
764 -1 * adjustment_amount),
765 decode(debit_credit_flag,
766 ''CR'', adjustment_amount,
767 -1 * adjustment_amount)),
768 decode(adj.adjustment_type,
769 ''RESERVE'',
770 decode(debit_credit_flag,
771 ''CR'', adjustment_amount,
772 -1 * adjustment_amount),
773 ''BONUS RESERVE'',
774 decode(debit_credit_flag,
775 ''CR'', adjustment_amount,
776 -1 * adjustment_amount),
777 ''REVAL RESERVE'',
778 decode(debit_credit_flag,
779 ''CR'', adjustment_amount,
780 -1 * adjustment_amount),
781 ''IMPAIR RESERVE'',
782 decode(debit_credit_flag,
783 ''CR'', adjustment_amount,
784 -1 * adjustment_amount),
785 ''CAPITAL ADJ'',
786 decode(debit_credit_flag,
787 ''DR'', adjustment_amount,
788 -1 * adjustment_amount),
789 ''GENERAL FUND'',
790 decode(debit_credit_flag,
791 ''CR'', adjustment_amount,
792 -1 * adjustment_amount),
793 decode(debit_credit_flag,
794 ''DR'', adjustment_amount,
795 -1 * adjustment_amount))) ';
796
797 c_line_adj_amt_dist1 CONSTANT VARCHAR2(32000) := '
798 decode(adj.adjustment_type,
799 ''RESERVE'',
800 decode(debit_credit_flag,
801 ''DR'', adjustment_amount,
802 -1 * adjustment_amount),
803 ''BONUS RESERVE'',
804 decode(debit_credit_flag,
805 ''DR'', adjustment_amount,
806 -1 * adjustment_amount),
807 ''REVAL RESERVE'',
808 decode(debit_credit_flag,
809 ''DR'', adjustment_amount,
810 -1 * adjustment_amount),
811 ''IMPAIR RESERVE'',
812 decode(debit_credit_flag,
813 ''DR'', adjustment_amount,
814 -1 * adjustment_amount),
815 ''CAPITAL ADJ'',
816 decode(debit_credit_flag,
817 ''CR'', adjustment_amount,
818 -1 * adjustment_amount),
819 ''GENERAL FUND'',
820 decode(debit_credit_flag,
821 ''DR'', adjustment_amount,
822 -1 * adjustment_amount),
823 decode(debit_credit_flag,
824 ''CR'', adjustment_amount,
825 -1 * adjustment_amount)) ';
826
827 c_line_adj_amt_dist2 CONSTANT VARCHAR2(32000) := '
828 decode(adj.adjustment_type,
829 ''RESERVE'',
830 decode(debit_credit_flag,
831 ''CR'', adjustment_amount,
832 -1 * adjustment_amount),
833 ''BONUS RESERVE'',
834 decode(debit_credit_flag,
835 ''CR'', adjustment_amount,
836 -1 * adjustment_amount),
837 ''REVAL RESERVE'',
838 decode(debit_credit_flag,
839 ''CR'', adjustment_amount,
840 -1 * adjustment_amount),
841 ''IMPAIR RESERVE'',
842 decode(debit_credit_flag,
843 ''CR'', adjustment_amount,
844 -1 * adjustment_amount),
845 ''CAPITAL ADJ'',
846 decode(debit_credit_flag,
847 ''DR'', adjustment_amount,
848 -1 * adjustment_amount),
849 ''GENERAL FUND'',
850 decode(debit_credit_flag,
851 ''CR'', adjustment_amount,
852 -1 * adjustment_amount),
853 decode(debit_credit_flag,
854 ''DR'', adjustment_amount,
855 -1 * adjustment_amount)) ';
856
857 c_line_adj_amt_ret CONSTANT VARCHAR2(32000) := '
858 decode(adj.adjustment_type,
859 ''RESERVE'',
860 decode(debit_credit_flag,
861 ''DR'', adjustment_amount,
862 -1 * adjustment_amount),
863 ''BONUS RESERVE'',
864 decode(debit_credit_flag,
865 ''DR'', adjustment_amount,
866 -1 * adjustment_amount),
867 ''REVAL RESERVE'',
868 decode(debit_credit_flag,
869 ''DR'', adjustment_amount,
870 -1 * adjustment_amount),
871 ''NBV RETIRED'',
872 decode(debit_credit_flag,
873 ''DR'', adjustment_amount,
874 -1 * adjustment_amount),
875 ''PROCEEDS CLR'',
876 decode(debit_credit_flag,
877 ''DR'', adjustment_amount,
878 -1 * adjustment_amount),
879 ''REMOVALCOST'',
880 decode(debit_credit_flag,
881 ''DR'', adjustment_amount,
882 -1 * adjustment_amount),
883 ''IMPAIR RESERVE'',
884 decode(debit_credit_flag,
885 ''DR'', adjustment_amount,
886 -1 * adjustment_amount),
887 ''CAPITAL ADJ'',
888 decode(debit_credit_flag,
889 ''DR'', adjustment_amount,
890 -1 * adjustment_amount),
891 ''GENERAL FUND'',
892 decode(debit_credit_flag,
893 ''CR'', adjustment_amount,
894 -1 * adjustment_amount),
895 decode(debit_credit_flag,
896 ''CR'', adjustment_amount,
897 -1 * adjustment_amount)) ';
898
899
900 c_line_from_trx CONSTANT VARCHAR2(32000) := '
901 from fa_xla_ext_lines_stg_gt stg,
902 fa_adjustments adj,
903 fa_distribution_history dh,
904 fa_locations loc,
905 fa_lookups lu ';
906
907 c_line_from_trx_dist1 CONSTANT VARCHAR2(32000) := ',
908 fa_asset_history ah,
909 fa_category_books cb ';
910
911 c_line_from_trx_ret CONSTANT VARCHAR2(32000) := ',
912 fa_retirements ret ';
913
914 c_line_where_trx CONSTANT VARCHAR2(32000) := '
915 WHERE adj.transaction_header_id = stg.transaction_header_id
916 AND adj.book_type_code = stg.book_type_code
917 AND adj.distribution_id = dh.distribution_id
918 AND dh.location_id = loc.location_id
919 -- AND dh.assigned_to = emp.employee_id(+)
920 AND lu.lookup_type = ''JOURNAL ENTRIES''
921 AND lu.lookup_code = adj.source_type_code || '' '' ||
922 decode (adj.adjustment_type,
923 ''CIP COST'', ''COST'',
924 adj.adjustment_type)
925 AND adj.adjustment_type not in (''REVAL EXPENSE'', ''REVAL AMORT'')
926 AND nvl(adj.track_member_flag, ''N'') = ''N''
927 AND adj.adjustment_amount <> 0 ';
928
929 c_line_where_trx_fin1 CONSTANT VARCHAR2(32000) := '
930 AND stg.event_type_code in (''ADDITIONS'', ''CIP_ADDITIONS'',
931 ''ADJUSTMENTS'', ''CIP_ADJUSTMENTS'',
932 ''CAPITALIZATION'', ''REVERSE_CAPITALIZATION'',
933 ''REVALUATION'', ''CIP_REVALUATION'',
934 ''ROLLBACK_REVALUATION'', ''CIP_ROLLBACK_REVALUATION'',
935 ''DEPRECIATION_ADJUSTMENTS'',
936 ''UNPLANNED_DEPRECIATION'',
937 ''TERMINAL_GAIN_LOSS'',
938 ''RETIREMENT_ADJUSTMENTS'',
939 ''IMPAIRMENT'', ''ROLLBACK_IMPAIRMENT'') ';
940
941 c_line_where_trx_fin2 CONSTANT VARCHAR2(32000) := '
942 AND stg.event_type_code in (''SOURCE_LINE_TRANSFERS'',
943 ''CIP_SOURCE_LINE_TRANSFERS'',
944 ''RESERVE_TRANSFERS'') ';
945
946 c_line_where_trx_xfr CONSTANT VARCHAR2(32000) := '
947 AND stg.event_type_code in (''TRANSFERS'', ''CIP_TRANSFERS'') ';
948
949 c_line_where_trx_dist1 CONSTANT VARCHAR2(32000) := '
950 AND stg.event_type_code in (''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
951 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'')
952 AND adj.asset_id = ah.asset_id
953 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
954 AND cb.category_id = ah.category_id
955 AND cb.book_type_code = adj.book_type_code
956 AND adj.source_dest_code = ''SOURCE'' ';
957
958
959 c_line_where_trx_dist2 CONSTANT VARCHAR2(32000) := '
960 AND stg.event_type_code in (''CATEGORY_RECLASS'', ''CIP_CATEGORY_RECLASS'',
961 ''UNIT_ADJUSTMENTS'', ''CIP_UNIT_ADJUSTMENTS'')
962 AND adj.source_dest_code = ''DEST'' ';
963
964
965 -- need to think about group in the following!!!
966
967 c_line_where_trx_ret CONSTANT VARCHAR2(32000) := '
968 AND stg.event_type_code in (''RETIREMENTS'', ''CIP_RETIREMENTS'')
969 AND ret.transaction_header_id_in = stg.member_transaction_header_id ';
970
971
972 c_line_where_trx_res CONSTANT VARCHAR2(32000) := '
973 AND stg.event_type_code in (''REINSTATEMENTS'',''CIP_REINSTATEMENTS'')
974 AND ret.transaction_header_id_out = stg.member_transaction_header_id ';
975
976
977 c_rowcount_debug CONSTANT VARCHAR2(32000) := '
978 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
979 fnd_log.string(G_LEVEL_PROCEDURE,
980 G_MODULE_NAME||l_procedure_name,
981 ''Rows inserted into lines: '' || to_char(SQL%ROWCOUNT));
982 END IF;
983
984 ';
985
986 c_sec_if_condition CONSTANT VARCHAR2(32000) := '
987 if (fa_xla_extract_util_pkg.G_secondary_special) then
988
989 ';
990
991 c_non_sec_if_condition CONSTANT VARCHAR2(32000) := '
992 if (not fa_xla_extract_util_pkg.G_secondary_special) then
993
994 ';
995
996 c_mc_if_condition CONSTANT VARCHAR2(32000) := '
997 if (fa_xla_extract_util_pkg.G_alc_enabled or
998 fa_xla_extract_util_pkg.G_secondary_special) then
999
1000 ';
1001
1002 c_group_if_condition CONSTANT VARCHAR2(32000) := '
1003 if (fa_xla_extract_util_pkg.G_group_enabled) then
1004
1005 ';
1006
1007
1008 -- header level constant values
1009
1010 c_mls_insert CONSTANT VARCHAR2(32000) := '
1011 insert into fa_xla_ext_lines_tl_gt (
1012 event_id ,
1013 line_number ,
1014 LEDGER_ID ,
1015 TRANSACTION_HEADER_ID ,
1016 ASSET_ID ,
1017 DEPRN_RUN_ID ,
1018 BOOK_TYPE_CODE ,
1019 PERIOD_COUNTER '; -- Bug:6399642
1020
1021 c_mls_select CONSTANT VARCHAR2(32000) := ' )
1022 select xl.event_id ,
1023 xl.line_number ,
1024 xl.ledger_id ,
1025 xl.TRANSACTION_HEADER_ID ,
1026 xl.ASSET_ID ,
1027 xl.DEPRN_RUN_ID ,
1028 xl.BOOK_TYPE_CODE ,
1029 xl.PERIOD_COUNTER '; -- Bug:6399642
1030
1031 c_mls_from CONSTANT VARCHAR2(32000) := '
1032 FROM fa_xla_ext_lines_b_gt xl ';
1033
1034
1035
1036 --+============================================+
1037 --| |
1038 --| PRIVATE PROCEDURES/FUNCTIONS |
1039 --| |
1040 --+============================================+
1041
1042 -- AddMember
1043 -- Extends and Inserts a value into table
1044
1045 Procedure AddMember (p_table IN OUT NOCOPY fa_char30_tbl_type,
1046 p_value IN VARCHAR2)IS
1047
1048 l_procedure_name varchar2(80) := 'AddMember';
1049
1050 BEGIN
1051
1052 p_table.EXTEND;
1053 p_table(p_table.last) := p_value;
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1058 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1059 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1060 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1061 END IF;
1062 RAISE;
1063
1064 END AddMember;
1065
1066
1067
1068
1069 procedure delete_table_member (p_table IN OUT NOCOPY v30_tbl,
1070 p_index IN number) is
1071
1072 l_procedure_name varchar2(80) := ' delete_table_member';
1073 l_count number;
1074
1075 begin
1076 if nvl(p_index, 0) > 0 then
1077
1078 p_table.delete(p_index);
1079
1080 l_count := p_table.count;
1081
1082 for i in p_index..l_count loop
1083
1084 -- copy the next member into the current one
1085 p_table(i) := p_table(i+1);
1086 end loop;
1087
1088 -- delete the last member in the array which is now a duplicate
1089 p_table.delete(l_count + 1);
1090
1091 end if;
1092
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1096 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1097 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1098 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1099 END IF;
1100 RAISE;
1101
1102 end delete_table_member;
1103
1104 FUNCTION get_schema (p_app_short_name VARCHAR2) RETURN VARCHAR2 IS
1105
1106 l_schema varchar2(50);
1107 l_status varchar2(100);
1108 l_industry varchar2(100);
1109 schema_err exception;
1110 l_procedure_name varchar2(80) := 'get_schema';
1111
1112 BEGIN
1113
1114 -- Get schema
1115 if not (fnd_installation.get_app_info (
1116 application_short_name => p_app_short_name,
1117 status => l_status,
1118 industry => l_industry,
1119 oracle_schema => l_schema)) then
1120 raise schema_err;
1121 end if;
1122
1123 RETURN l_schema;
1124
1125 EXCEPTION
1126 WHEN schema_err THEN
1127 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1128 fnd_log.string(G_LEVEL_PROCEDURE,
1129 G_MODULE_NAME||l_procedure_name,
1130 'schema_err');
1131 END IF;
1132 raise;
1133 WHEN OTHERS THEN
1134 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1135 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1136 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1137 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1138 END IF;
1139 RAISE;
1140
1141 END get_schema;
1142
1143 -- Initialize
1144 -- Loads plsql tables for known tables, enttites and events classes
1145
1146 Procedure initialize is
1147
1148 l_procedure_name varchar2(80) := 'Initialize';
1149 l_schema varchar2(50);
1150
1151 BEGIN
1152
1153 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1154 fnd_log.string(G_LEVEL_PROCEDURE,
1155 G_MODULE_NAME||l_procedure_name||'.begin',
1156 'Beginning of procedure');
1157 END IF;
1158
1159 -- schemas
1160 G_known_schemas := fa_char30_tbl_type();
1161 l_schema := get_schema('OFA');
1162 AddMember(G_known_schemas, l_schema);
1163 l_schema := get_schema('GL');
1164 AddMember(G_known_schemas, l_schema);
1165 l_schema := get_schema('XLA');
1166 AddMember(G_known_schemas, l_schema);
1167
1168
1169 -- deprn (used for header and lines)
1170 G_deprn_event_class_table := fa_char30_tbl_type();
1171 AddMember(G_deprn_event_class_table, 'DEPRECIATION');
1172
1173 G_known_deprn_hdr_tables := fa_char30_tbl_type();
1174 G_known_deprn_line_tables := fa_char30_tbl_type();
1175
1176 -- header
1177 AddMember(G_known_deprn_hdr_tables, 'FA_BOOK_CONTROLS');
1178 AddMember(G_known_deprn_hdr_tables, 'FA_DEPRN_PERIODS');
1179 AddMember(G_known_deprn_hdr_tables, 'XLA_EVENTS_GT');
1180
1181 -- lines standard
1182 AddMember(G_known_deprn_line_tables, 'FA_ADDITIONS_B');
1183 AddMember(G_known_deprn_line_tables, 'FA_ASSET_HISTORY');
1184 AddMember(G_known_deprn_line_tables, 'FA_BOOK_CONTROLS');
1185 AddMember(G_known_deprn_line_tables, 'FA_DISTRIBUTION_HISTORY');
1186 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_DETAIL');
1187 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_PERIODS');
1188 AddMember(G_known_deprn_line_tables, 'GL_LEDGERS');
1189 AddMember(G_known_deprn_line_tables, 'XLA_EVENTS_GT');
1190
1191 -- lines non-standard
1192 AddMember(G_known_deprn_line_tables, 'FA_ASSET_KEYWORDS');
1193 AddMember(G_known_deprn_line_tables, 'FA_BOOKS');
1194 AddMember(G_known_deprn_line_tables, 'FA_CATEGORIES_B');
1195 AddMember(G_known_deprn_line_tables, 'FA_CATEGORY_BOOKS');
1196 AddMember(G_known_deprn_line_tables, 'FA_DEPRN_SUMMARY');
1197 AddMember(G_known_deprn_line_tables, 'FA_LEASES');
1198 AddMember(G_known_deprn_line_tables, 'FA_LOCATIONS');
1199 AddMember(G_known_deprn_line_tables, 'FA_METHODS');
1200
1201
1202 -- deferred (used for header and lines)
1203 G_def_event_class_table := fa_char30_tbl_type();
1204 AddMember(G_def_event_class_table, 'DEFERRED_DEPRECIATION');
1205
1206 -- deferred
1207 G_known_def_hdr_tables := fa_char30_tbl_type();
1208 G_known_def_hdr_tables := G_known_deprn_hdr_tables;
1209 G_known_def_line_tables := fa_char30_tbl_type();
1210
1211 -- standard
1212 AddMember(G_known_def_line_tables, 'FA_ADDITIONS_B');
1213 AddMember(G_known_def_line_tables, 'FA_ASSET_HISTORY');
1214 AddMember(G_known_def_line_tables, 'FA_BOOK_CONTROLS');
1215 AddMember(G_known_def_line_tables, 'FA_DISTRIBUTION_HISTORY');
1216 AddMember(G_known_def_line_tables, 'FA_DEFERRED_DEPRN');
1217 AddMember(G_known_def_line_tables, 'FA_DEPRN_PERIODS');
1218 AddMember(G_known_def_line_tables, 'GL_LEDGERS');
1219 AddMember(G_known_def_line_tables, 'XLA_EVENTS_GT');
1220
1221 -- non-standard
1222 AddMember(G_known_def_line_tables, 'FA_ASSET_KEYWORDS');
1223 AddMember(G_known_def_line_tables, 'FA_BOOKS');
1224 AddMember(G_known_def_line_tables, 'FA_CATEGORIES_B');
1225 AddMember(G_known_def_line_tables, 'FA_CATEGORY_BOOKS');
1226 AddMember(G_known_def_line_tables, 'FA_LEASES');
1227 AddMember(G_known_def_line_tables, 'FA_LOCATIONS');
1228 AddMember(G_known_def_line_tables, 'FA_METHODS');
1229
1230
1231 -- headers only
1232 -- transactions
1233 G_trx1_hdr_event_class_table := fa_char30_tbl_type();
1234
1235 AddMember(G_trx1_hdr_event_class_table, 'ADDITIONS');
1236 AddMember(G_trx1_hdr_event_class_table, 'CIP_ADDITIONS');
1237 AddMember(G_trx1_hdr_event_class_table, 'ADJUSTMENTS');
1238 AddMember(G_trx1_hdr_event_class_table, 'CIP_ADJUSTMENTS');
1239 AddMember(G_trx1_hdr_event_class_table, 'CAPITALIZATION');
1240 AddMember(G_trx1_hdr_event_class_table, 'REVALUATION');
1241 AddMember(G_trx1_hdr_event_class_table, 'CIP_REVALUATION');
1242 AddMember(G_trx1_hdr_event_class_table, 'TRANSFERS');
1243 AddMember(G_trx1_hdr_event_class_table, 'CIP_TRANSFERS');
1244 AddMember(G_trx1_hdr_event_class_table, 'CATEGORY_RECLASS');
1245 AddMember(G_trx1_hdr_event_class_table, 'CIP_CATEGORY_RECLASS');
1246 AddMember(G_trx1_hdr_event_class_table, 'UNIT_ADJUSTMENTS');
1247 AddMember(G_trx1_hdr_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1248 AddMember(G_trx1_hdr_event_class_table, 'RETIREMENTS');
1249 AddMember(G_trx1_hdr_event_class_table, 'CIP_RETIREMENTS');
1250 AddMember(G_trx1_hdr_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1251 AddMember(G_trx1_hdr_event_class_table, 'UNPLANNED_DEPRECIATION');
1252 AddMember(G_trx1_hdr_event_class_table, 'TERMINAL_GAIN_LOSS');
1253 AddMember(G_trx1_hdr_event_class_table, 'RETIREMENT_ADJUSTMENTS');
1254 AddMember(G_trx1_hdr_event_class_table, 'IMPAIRMENT');
1255
1256 -- inter asset trxs
1257 -- used for staging and line
1258 G_trx2_hdr_event_class_table := fa_char30_tbl_type();
1259
1260 AddMember(G_trx2_hdr_event_class_table, 'SOURCE_LINE_TRANSFERS');
1261 AddMember(G_trx2_hdr_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1262 AddMember(G_trx2_hdr_event_class_table, 'RESERVE_TRANSFERS');
1263
1264
1265 -- line level event classes
1266 G_fin1_line_event_class_table := fa_char30_tbl_type();
1267 G_fin2_line_event_class_table := fa_char30_tbl_type();
1268 G_xfr_line_event_class_table := fa_char30_tbl_type();
1269 G_dist_line_event_class_table := fa_char30_tbl_type();
1270 G_ret_line_event_class_table := fa_char30_tbl_type();
1271
1272 AddMember(G_fin1_line_event_class_table, 'ADDITIONS');
1273 AddMember(G_fin1_line_event_class_table, 'CIP_ADDITIONS');
1274 AddMember(G_fin1_line_event_class_table, 'ADJUSTMENTS');
1275 AddMember(G_fin1_line_event_class_table, 'CIP_ADJUSTMENTS');
1276 AddMember(G_fin1_line_event_class_table, 'CAPITALIZATION');
1277 AddMember(G_fin1_line_event_class_table, 'REVALUATION');
1278 AddMember(G_fin1_line_event_class_table, 'CIP_REVALUATION');
1279 AddMember(G_fin1_line_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1280 AddMember(G_fin1_line_event_class_table, 'UNPLANNED_DEPRECIATION');
1281 AddMember(G_fin1_line_event_class_table, 'TERMINAL_GAIN_LOSS');
1282 AddMember(G_fin1_line_event_class_table, 'RETIREMENT_ADJUSTMENTS');
1283 AddMember(G_fin1_line_event_class_table, 'IMPAIRMENT');
1284
1285 AddMember(G_fin2_line_event_class_table, 'SOURCE_LINE_TRANSFERS');
1286 AddMember(G_fin2_line_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1287 AddMember(G_fin2_line_event_class_table, 'RESERVE_TRANSFERS');
1288
1289 AddMember(G_xfr_line_event_class_table, 'TRANSFERS');
1290 AddMember(G_xfr_line_event_class_table, 'CIP_TRANSFERS');
1291
1292 AddMember(G_dist_line_event_class_table, 'CATEGORY_RECLASS');
1293 AddMember(G_dist_line_event_class_table, 'CIP_CATEGORY_RECLASS');
1294 AddMember(G_dist_line_event_class_table, 'UNIT_ADJUSTMENTS');
1295 AddMember(G_dist_line_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1296
1297 AddMember(G_ret_line_event_class_table, 'RETIREMENTS');
1298 AddMember(G_ret_line_event_class_table, 'CIP_RETIREMENTS');
1299
1300 G_known_trx_hdr_tables := fa_char30_tbl_type();
1301 G_known_trx_hdr_tables := G_known_deprn_hdr_tables;
1302
1303 -- line level tables
1304 G_known_fin1_line_tables := fa_char30_tbl_type();
1305 G_known_fin2_line_tables := fa_char30_tbl_type();
1306 G_known_xfr_line_tables := fa_char30_tbl_type();
1307 G_known_dist1_line_tables := fa_char30_tbl_type();
1308 G_known_dist2_line_tables := fa_char30_tbl_type();
1309 G_known_ret_line_tables := fa_char30_tbl_type();
1310
1311 AddMember(G_known_fin1_line_tables, 'FA_XLA_EXT_LINES_STG_GT');
1312 AddMember(G_known_fin1_line_tables, 'FA_ADJUSTMENTS');
1313 AddMember(G_known_fin1_line_tables, 'FA_DISTRIBUTION_HISTORY');
1314 AddMember(G_known_fin1_line_tables, 'FA_LOCATIONS');
1315 AddMember(G_known_fin1_line_tables, 'FA_LOOKUPS');
1316
1317 G_known_fin2_line_tables := G_known_fin1_line_tables;
1318 G_known_xfr_line_tables := G_known_fin1_line_tables;
1319 G_known_dist1_line_tables := G_known_fin1_line_tables;
1320 G_known_dist2_line_tables := G_known_fin1_line_tables;
1321 G_known_ret_line_tables := G_known_fin1_line_tables;
1322
1323 AddMember(G_known_fin1_line_tables, 'FA_ASSET_INVOICES');
1324 AddMember(G_known_fin2_line_tables, 'FA_ASSET_INVOICES');
1325 AddMember(G_known_dist1_line_tables, 'FA_ASSET_HISTORY');
1326 AddMember(G_known_dist1_line_tables, 'FA_CATEGORY_BOOKS');
1327 AddMember(G_known_dist1_line_tables, 'FA_CATEGORIES_B');
1328
1329 AddMember(G_known_ret_line_tables, 'FA_ASSET_INVOICES');
1330 AddMember(G_known_ret_line_tables, 'FA_RETIREMENTS');
1331
1332
1333 -- staging
1334 G_known_stg_tables := fa_char30_tbl_type();
1335
1336 -- standard
1337 AddMember(G_known_stg_tables, 'FA_ADDITIONS_B');
1338 AddMember(G_known_stg_tables, 'FA_ASSET_HISTORY');
1339 AddMember(G_known_stg_tables, 'FA_CATEGORY_BOOKS');
1340 AddMember(G_known_stg_tables, 'FA_BOOK_CONTROLS');
1341 AddMember(G_known_stg_tables, 'FA_TRANSACTION_HEADERS');
1342 AddMember(G_known_stg_tables, 'GL_LEDGERS');
1343 AddMember(G_known_stg_tables, 'XLA_EVENTS_GT');
1344
1345 -- non-standard
1346 AddMember(G_known_stg_tables, 'FA_ASSET_KEYWORDS');
1347 AddMember(G_known_stg_tables, 'FA_CATEGORIES_B');
1348 AddMember(G_known_stg_tables, 'FA_LEASES');
1349 AddMember(G_known_stg_tables, 'FA_METHODS');
1350 AddMember(G_known_stg_tables, 'FA_BOOKS');
1351
1352 -- mls
1353 G_trx_mls_event_class_table := fa_char30_tbl_type();
1354 AddMember(G_trx_mls_event_class_table, 'ADDITIONS');
1355 AddMember(G_trx_mls_event_class_table, 'CIP_ADDITIONS');
1356 AddMember(G_trx_mls_event_class_table, 'ADJUSTMENTS');
1357 AddMember(G_trx_mls_event_class_table, 'CIP_ADJUSTMENTS');
1358 AddMember(G_trx_mls_event_class_table, 'CAPITALIZATION');
1359 AddMember(G_trx_mls_event_class_table, 'REVALUATION');
1360 AddMember(G_trx_mls_event_class_table, 'CIP_REVALUATION');
1361 AddMember(G_trx_mls_event_class_table, 'TRANSFERS');
1362 AddMember(G_trx_mls_event_class_table, 'CIP_TRANSFERS');
1363 AddMember(G_trx_mls_event_class_table, 'CATEGORY_RECLASS');
1364 AddMember(G_trx_mls_event_class_table, 'CIP_CATEGORY_RECLASS');
1365 AddMember(G_trx_mls_event_class_table, 'UNIT_ADJUSTMENTS');
1366 AddMember(G_trx_mls_event_class_table, 'CIP_UNIT_ADJUSTMENTS');
1367 AddMember(G_trx_mls_event_class_table, 'RETIREMENTS');
1368 AddMember(G_trx_mls_event_class_table, 'CIP_RETIREMENTS');
1369 AddMember(G_trx_mls_event_class_table, 'DEPRECIATION_ADJUSTMENTS');
1370 AddMember(G_trx_mls_event_class_table, 'UNPLANNED_DEPRECIATION');
1371 AddMember(G_trx_mls_event_class_table, 'TERMINAL_GAIN_LOSS');
1372 AddMember(G_trx_mls_event_class_table, 'RETIREMENT_ADJUSTMENTS');
1373 AddMember(G_trx_mls_event_class_table, 'SOURCE_LINE_TRANSFERS');
1374 AddMember(G_trx_mls_event_class_table, 'CIP_SOURCE_LINE_TRANSFERS');
1375 AddMember(G_trx_mls_event_class_table, 'RESERVE_TRANSFERS');
1376 AddMember(G_trx_mls_event_class_table, 'DEPRECIATION');
1377 AddMember(G_trx_mls_event_class_table, 'DEFERRED_DEPRECIATION');
1378 AddMember(G_trx_mls_event_class_table, 'IMPAIRMENT');
1379
1380 G_known_mls_tables := fa_char30_tbl_type();
1381 AddMember(G_known_mls_tables, 'FA_ADDITIONS_TL');
1382 AddMember(G_known_mls_tables, 'FA_CATEGORIES_TL');
1383
1384 G_initialized := TRUE;
1385
1386 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1387 fnd_log.string(G_LEVEL_PROCEDURE,
1388 G_MODULE_NAME||l_procedure_name||'.end',
1389 'End of procedure');
1390 END IF;
1391
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1396 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1397 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1398 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1399 END IF;
1400 RAISE;
1401
1402 end initialize;
1403
1404
1405
1406
1407
1408
1409 --+==========================================================================+
1410 --| |
1411 --| PUBLIC Procedure GenerateSourcesExtract |
1412 --| |
1413 --|
1414 --|
1415 --| valid params:
1416 --| HEADER/DEPRN
1417 --| HEADER/DEF
1418 --| HEADER/TRX1
1419 --| HEADER/TRX2
1420 --|
1421 --| STG/TRX1
1422 --| STG/TRX2
1423 --|
1424 --| LINE/FIN1
1425 --| LINE/FIN2
1426 --| LINE/XFR
1427 --| LINE/DIST1
1428 --| LINE/DIST2
1429 --| LINE/RET
1430 --| LINE/RES
1431 --|
1432 --| LINE/DEPRN
1433 --| LINE/DEF
1434 --|
1435 --| MLS/DEPRN
1436 --| MLS/DEF
1437 --| MLS/TRX
1438 --|
1439 --+==========================================================================+
1440
1441
1442 FUNCTION GenerateSourcesExtract
1443 (p_extract_type IN VARCHAR2, -- dep/trx/def
1444 p_level IN VARCHAR2, -- header/line/stg
1445 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
1446
1447
1448 cursor c_tables (p_tables fa_char30_tbl_type,
1449 p_schemas fa_char30_tbl_type) is
1450 select distinct table_name,
1451 decode(table_name,
1452 -- standard headers/lines
1453 'FA_BOOK_CONTROLS' , 'bc',
1454 'FA_DEPRN_PERIODS' , 'dp',
1455 'XLA_EVENTS_GT' , 'ctgld',
1456 -- standard lines
1457 'FA_ADDITIONS_B' , 'ad',
1458 'FA_ADJUSTMENTS' , 'adj',
1459 'FA_ASSET_HISTORY' , 'ah',
1460 'FA_CATEGORY_BOOKS' , 'cb',
1461 'FA_DISTRIBUTION_HISTORY' , 'dh',
1462 'FA_DEFERRED_DEPRN' , 'df',
1463 'FA_DEPRN_DETAIL' , 'dd',
1464 'FA_LOOKUPS' , 'lu',
1465 'FA_TRANSACTION_HEADERS' , 'th',
1466 'FA_RETIREMENTS' , 'ret',
1467 'FA_XLA_EXT_LINES_STG_GT' , 'stg',
1468 'GL_LEDGERS' , 'le',
1469 -- non-standard
1470 'FA_ADDITIONS_TL' , 'adtl',
1471 'FA_ASSET_INVOICES' , 'ai',
1472 'FA_ASSET_KEYWORDS' , 'key',
1473 'FA_BOOKS' , 'bk',
1474 'FA_CATEGORIES_B' , 'cat',
1475 'FA_CATEGORIES_TL' , 'cattl',
1476 'FA_DEPRN_SUMMARY' , 'ds',
1477 'FA_LEASES' , 'ls',
1478 'FA_LOCATIONS' , 'loc',
1479 'FA_METHODS' , 'mt',
1480 'INVALID')
1481 from all_tables tab,
1482 TABLE(CAST(p_tables AS fa_char30_tbl_type)) fatab,
1483 TABLE(CAST(p_schemas AS fa_char30_tbl_type)) stab
1484 where tab.table_name = fatab.column_value
1485 and tab.owner = stab.column_value;
1486
1487
1488 -- NOTE: we use four versions of this due to the use of the
1489 -- intermediate staging table for trxs...
1490 --
1491 -- 1) for header and all deprn/def line sources,
1492 -- select is as would be expected.
1493 -- 2) for trx staging, we only use known tables within the event classes
1494 -- 3) for trx lines, for sources already in staging table,
1495 -- stg becomes the table/alias and the source_code becomes the column name
1496 -- 4) for mls level, we use line level source but force the
1497 -- values selected to use one of the two known MLS tables
1498
1499 cursor c_sources (p_entity_code VARCHAR2,
1500 p_source_level_code VARCHAR2,
1501 p_event_class_table fa_char30_tbl_type) is
1502 select distinct
1503 sources.source_code,
1504 sources.source_table_name,
1505 sources.source_column_name
1506 from xla_aad_sources aad,
1507 xla_sources_b sources,
1508 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1509 where aad.application_id = 140
1510 and sources.application_id = 140
1511 and aad.entity_code = p_entity_code
1512 and aad.source_level_code = p_source_level_code
1513 and aad.event_class_code = fatab.column_value
1514 and aad.source_code = sources.source_code
1515 and sources.source_table_name is not null
1516 and sources.source_table_name not in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
1517 order by 2,1;
1518
1519 cursor c_sources_stg (p_entity_code VARCHAR2,
1520 p_source_level_code VARCHAR2,
1521 p_event_class_table fa_char30_tbl_type,
1522 p_known_tables fa_char30_tbl_type) is
1523 select distinct
1524 sources.source_code,
1525 sources.source_table_name,
1526 sources.source_column_name
1527 from xla_aad_sources aad,
1528 xla_sources_b sources,
1529 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1530 where aad.application_id = 140
1531 and sources.application_id = 140
1532 and aad.entity_code = p_entity_code
1533 and aad.source_level_code = p_source_level_code
1534 and aad.event_class_code = fatab.column_value
1535 and aad.source_code = sources.source_code
1536 and sources.source_table_name is not null
1537 and sources.source_table_name in
1538 (select fatab2.column_value
1539 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1540 order by 2,1;
1541
1542 cursor c_sources_trx (p_entity_code VARCHAR2,
1543 p_source_level_code VARCHAR2,
1544 p_event_class_table fa_char30_tbl_type,
1545 p_known_tables fa_char30_tbl_type) is
1546 select distinct
1547 sources.source_code,
1548 'FA_XLA_EXT_LINES_STG_GT',
1549 sources.source_code
1550 from xla_aad_sources aad,
1551 xla_sources_b sources,
1552 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1553 where aad.application_id = 140
1554 and sources.application_id = 140
1555 and aad.entity_code = p_entity_code
1556 and aad.source_level_code = p_source_level_code
1557 and aad.event_class_code = fatab.column_value
1558 and aad.source_code = sources.source_code
1559 and sources.source_table_name is not null
1560 and sources.source_table_name not in
1561 (select fatab2.column_value
1562 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1563 union
1564 select distinct
1565 sources.source_code,
1566 sources.source_table_name,
1567 sources.source_column_name
1568 from xla_aad_sources aad,
1569 xla_sources_b sources,
1570 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1571 where aad.application_id = 140
1572 and sources.application_id = 140
1573 and aad.entity_code = p_entity_code
1574 and aad.source_level_code = p_source_level_code
1575 and aad.event_class_code = fatab.column_value
1576 and aad.source_code = sources.source_code
1577 and sources.source_table_name is not null
1578 and sources.source_table_name in
1579 (select fatab2.column_value
1580 from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
1581 order by 2,1;
1582
1583
1584 cursor c_sources_mls (p_entity_code VARCHAR2,
1585 p_source_level_code VARCHAR2,
1586 p_event_class_table fa_char30_tbl_type) is
1587 select distinct
1588 sources.source_code,
1589 sources.source_table_name,
1590 sources.source_column_name
1591 from xla_aad_sources aad,
1592 xla_sources_b sources,
1593 TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
1594 where aad.application_id = 140
1595 and sources.application_id = 140
1596 and aad.entity_code = p_entity_code
1597 and aad.source_level_code = p_source_level_code
1598 and aad.event_class_code = fatab.column_value
1599 and aad.source_code = sources.source_code
1600 and sources.source_table_name in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
1601 order by 2,1;
1602
1603 l_insert varchar2(32000);
1604 l_select varchar2(32000);
1605 l_from varchar2(32000);
1606 l_from_temp varchar2(32000);
1607 l_where varchar2(32000);
1608
1609 l_rowcount_debug varchar2(32000);
1610
1611 -- fetching sources
1612 l_source_code v30_tbl;
1613 l_table_name v30_tbl;
1614 l_column_name v30_tbl;
1615 l_alias v30_tbl;
1616
1617 -- uses to fetch known tables / sources
1618 l_table_known v30_tbl;
1619 l_alias_known v30_tbl;
1620
1621 -- used to set to the global constants from initialization
1622 l_known_tables fa_char30_tbl_type;
1623 l_entity_code varchar2(30);
1624 l_event_class_table fa_char30_tbl_type;
1625
1626 l_level varchar2(30);
1627 l_found boolean := false;
1628 l_loop_total number := 1;
1629 l_loop_index number := 1;
1630 l_count number;
1631 l_count2 number;
1632 l_index number;
1633
1634 l_add_tl_in_use boolean := FALSE;
1635
1636 l_array_pkg DBMS_SQL.VARCHAR2S;
1637 l_BodyPkg VARCHAR2(32000);
1638 l_array_body DBMS_SQL.VARCHAR2S;
1639 l_procedure_name varchar2(80) := 'GenerateSourcesExtract';
1640
1641 invalid_mode EXCEPTION;
1642 table_not_found EXCEPTION;
1643
1644 BEGIN
1645
1646 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1647 fnd_log.string(G_LEVEL_PROCEDURE,
1648 G_MODULE_NAME||l_procedure_name||'.begin',
1649 'Beginning of procedure');
1650 END IF;
1651
1652 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
1653 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
1654
1655 l_known_tables := fa_char30_tbl_type();
1656 l_event_class_table := fa_char30_tbl_type();
1657
1658
1659 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1660 fnd_log.string(G_LEVEL_STATEMENT,
1661 G_MODULE_NAME||l_procedure_name,
1662 'p_extract_type: ' || p_extract_type);
1663 fnd_log.string(G_LEVEL_STATEMENT,
1664 G_MODULE_NAME||l_procedure_name,
1665 'p_level: ' || p_level);
1666 END IF;
1667
1668
1669 if (not g_initialized) then
1670 initialize;
1671 end if;
1672
1673 -- load known tables and columns
1674 if (p_level = 'HEADER') then
1675
1676 l_loop_total := 2;
1677 l_level := 'HEADER';
1678 l_rowcount_debug := replace(c_rowcount_debug, 'lines' ,'headers');
1679
1680 l_insert := c_hdr_insert;
1681 l_select := c_hdr_select;
1682 l_from := c_hdr_from;
1683
1684 if (p_extract_type = 'DEF') then
1685 l_select := l_select || c_hdr_select1;
1686 else
1687 l_select := l_select || c_hdr_select2;
1688 end if;
1689
1690 -- FYI: deprn and deferred do not need additional joins
1691 if (p_extract_type = 'DEPRN') then
1692 l_where := c_hdr_where_deprn;
1693
1694 l_entity_code := 'DEPRECIATION';
1695 l_known_tables := G_known_deprn_hdr_tables;
1696 l_event_class_table := G_deprn_event_class_table;
1697
1698 elsif (p_extract_type = 'DEF') then
1699 l_where := c_hdr_where_def;
1700
1701 l_entity_code := 'DEFERRED_DEPRECIATION';
1702 l_known_tables := G_known_def_hdr_tables;
1703 l_event_class_table := G_def_event_class_table;
1704
1705 elsif (p_extract_type = 'TRX1') then
1706 l_where := c_hdr_where_trx;
1707 l_from := l_from || ',' || fa_cmp_string_pkg.g_chr_newline ||
1708 ' FA_TRANSACTION_HEADERS th ';
1709
1710 l_entity_code := 'TRANSACTIONS';
1711 l_known_tables := G_known_trx_hdr_tables;
1712 l_event_class_table := G_trx1_hdr_event_class_table;
1713
1714 elsif (p_extract_type = 'TRX2') then
1715 l_where := c_hdr_where_itrx;
1716 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline ||
1717 ' FA_TRX_REFERENCES trx ' ;
1718
1719 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1720 l_known_tables := G_known_trx_hdr_tables;
1721 l_event_class_table := G_trx2_hdr_event_class_table;
1722
1723 else
1724 raise invalid_mode;
1725 end if;
1726
1727 elsif (p_level = 'LINE') then
1728
1729 l_loop_total := 2;
1730 l_level := 'LINE';
1731 l_rowcount_debug := c_rowcount_debug;
1732
1733 if (p_extract_type = 'DEPRN') then
1734 l_insert := c_line_insert_deprn;
1735 l_select := c_line_select_deprn;
1736 l_from := c_line_from_deprn;
1737 l_where := c_line_where_deprn;
1738
1739 l_select := replace(l_select, 'select ' ,
1740 'select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ');
1741
1742 l_entity_code := 'DEPRECIATION';
1743 l_known_tables := G_known_deprn_line_tables;
1744 l_event_class_table := G_deprn_event_class_table;
1745
1746 elsif (p_extract_type = 'DEF') then
1747
1748 l_insert := c_line_insert_def;
1749 l_select := c_line_select_def;
1750 l_from := c_line_from_def;
1751 l_where := c_line_where_def;
1752
1753 l_entity_code := 'DEFERRED_DEPRECIATION';
1754 l_known_tables := G_known_def_line_tables;
1755 l_event_class_table := G_def_event_class_table;
1756
1757 elsif (p_extract_type in ('FIN1','FIN2','XFR','DIST1','DIST2','RET','RES')) then
1758
1759 l_insert := c_line_insert_trx;
1760
1761 -- BUG# 7693865
1762 if (p_extract_type = 'DIST1') then
1763 l_select := c_line_select_trx_dist1;
1764 else
1765 l_select := c_line_select_trx;
1766 end if;
1767
1768 l_from := c_line_from_trx;
1769 l_where := c_line_where_trx;
1770
1771 -- NOTE: constants for from clause already include the proceeding comma!!!
1772
1773 if (p_extract_type = 'FIN1') then
1774 l_select := l_select || c_line_adj_amt_fin1;
1775 l_where := l_where || c_line_where_trx_fin1;
1776
1777 l_entity_code := 'TRANSACTIONS';
1778 l_known_tables := G_known_fin1_line_tables;
1779 l_event_class_table := G_fin1_line_event_class_table;
1780
1781 elsif (p_extract_type = 'FIN2') then
1782 l_select := l_select || c_line_adj_amt_fin2;
1783 l_where := l_where || c_line_where_trx_fin2;
1784
1785 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1786 l_known_tables := G_known_fin2_line_tables;
1787 l_event_class_table := G_fin2_line_event_class_table;
1788
1789 elsif (p_extract_type = 'XFR') then
1790 l_select := l_select || c_line_adj_amt_xfr;
1791 l_where := l_where || c_line_where_trx_xfr;
1792
1793 l_entity_code := 'TRANSACTIONS';
1794 l_known_tables := G_known_xfr_line_tables;
1795 l_event_class_table := G_xfr_line_event_class_table;
1796
1797 elsif (p_extract_type = 'DIST1') then
1798 l_select := l_select || c_line_adj_amt_dist1;
1799 l_from := l_from || c_line_from_trx_dist1;
1800 l_where := l_where || c_line_where_trx_dist1;
1801
1802 l_entity_code := 'TRANSACTIONS';
1803 l_known_tables := G_known_dist1_line_tables;
1804 l_event_class_table := G_dist_line_event_class_table;
1805
1806 elsif (p_extract_type = 'DIST2') then
1807 l_select := l_select || c_line_adj_amt_dist2;
1808 l_where := l_where || c_line_where_trx_dist2;
1809
1810 l_entity_code := 'TRANSACTIONS';
1811 l_known_tables := G_known_dist2_line_tables;
1812 l_event_class_table := G_dist_line_event_class_table;
1813
1814 elsif (p_extract_type = 'RET') then
1815 l_select := l_select || c_line_adj_amt_ret;
1816 l_from := l_from || c_line_from_trx_ret;
1817 l_where := l_where || c_line_where_trx_ret;
1818
1819 l_entity_code := 'TRANSACTIONS';
1820 l_known_tables := G_known_ret_line_tables;
1821 l_event_class_table := G_ret_line_event_class_table;
1822
1823 elsif (p_extract_type = 'RES') then
1824 l_select := l_select || c_line_adj_amt_ret;
1825 l_from := l_from || c_line_from_trx_ret;
1826 l_where := l_where || c_line_where_trx_res;
1827
1828 l_entity_code := 'TRANSACTIONS';
1829 l_known_tables := G_known_ret_line_tables;
1830 l_event_class_table := G_ret_line_event_class_table;
1831 else
1832 raise invalid_mode;
1833 end if;
1834
1835 -- perf to insure we lead by gt and use adj_u1,
1836 -- add hint where appropriate
1837 l_select := replace(l_select, 'select ' ,
1838 'select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ ');
1839 else
1840 raise invalid_mode;
1841 end if;
1842
1843 elsif (p_level = 'STG') then
1844
1845 l_level := 'LINE';
1846 l_rowcount_debug := replace(c_rowcount_debug, 'lines' ,'staging lines');
1847
1848 l_insert := c_line_insert_stg;
1849 l_select := c_line_select_stg;
1850
1851 if (p_extract_type = 'TRX1') then
1852
1853 l_loop_total := 2;
1854
1855 l_from := c_line_from_stg1;
1856 l_where := c_line_where_stg1;
1857
1858 l_entity_code := 'TRANSACTIONS';
1859 l_known_tables := G_known_stg_tables;
1860 l_event_class_table := G_trx1_hdr_event_class_table;
1861
1862 elsif (p_extract_type = 'TRX2') then
1863
1864 l_loop_total := 4;
1865
1866 l_from := c_line_from_stg2;
1867 l_where := c_line_where_stg2;
1868
1869 l_entity_code := 'INTER_ASSET_TRANSACTIONS';
1870 l_known_tables := G_known_stg_tables;
1871 l_event_class_table := G_trx2_hdr_event_class_table;
1872
1873 else
1874 raise invalid_mode;
1875 end if;
1876
1877 elsif (p_level = 'MLS') then
1878
1879 l_loop_total := 1;
1880 l_level := 'LINE_MLS';
1881 l_rowcount_debug := replace(c_rowcount_debug, 'lines' ,'MLS lines');
1882
1883 l_insert := c_mls_insert;
1884 l_select := c_mls_select;
1885 l_from := c_mls_from;
1886 l_where := '';
1887
1888 if (p_extract_type = 'DEPRN') then
1889 l_entity_code := 'DEPRECIATION';
1890 l_known_tables := G_known_mls_tables;
1891 l_event_class_table := G_deprn_event_class_table;
1892
1893 elsif (p_extract_type = 'DEF') then
1894 l_entity_code := 'DEFERRED_DEPRECIATION';
1895 l_known_tables := G_known_mls_tables;
1896 l_event_class_table := G_def_event_class_table;
1897
1898 elsif (p_extract_type = 'TRX') then
1899 l_entity_code := 'TRANSACTIONS';
1900 l_known_tables := G_known_mls_tables;
1901 l_event_class_table := G_trx_mls_event_class_table;
1902 else
1903 raise invalid_mode;
1904 end if;
1905 else
1906 raise invalid_mode;
1907 end if;
1908
1909
1910
1911 -- determine known tables - this will return all known tables we can handle
1912 -- across event classes so if an invalid one is used, we will trap later...
1913 open c_tables (p_tables => l_known_tables,
1914 p_schemas => G_known_schemas);
1915 fetch c_tables bulk collect
1916 into l_table_known,
1917 l_alias_known;
1918 close c_tables;
1919
1920
1921 -- fetch the sources actually used
1922 if ((l_entity_code = 'TRANSACTIONS' or
1923 l_entity_code = 'INTER_ASSET_TRANSACTIONS') and
1924 p_level = 'LINE') then
1925
1926 open c_sources_trx (p_entity_code => l_entity_code,
1927 p_source_level_code => l_level,
1928 p_event_class_table => l_event_class_table,
1929 p_known_tables => l_known_tables);
1930 fetch c_sources_trx bulk collect
1931 into l_source_code,
1932 l_table_name,
1933 l_column_name;
1934 close c_sources_trx;
1935 elsif ((l_entity_code = 'TRANSACTIONS' or
1936 l_entity_code = 'INTER_ASSET_TRANSACTIONS') and
1937 p_level = 'STG') then
1938
1939 open c_sources_stg (p_entity_code => l_entity_code,
1940 p_source_level_code => l_level,
1941 p_event_class_table => l_event_class_table,
1942 p_known_tables => l_known_tables);
1943 fetch c_sources_stg bulk collect
1944 into l_source_code,
1945 l_table_name,
1946 l_column_name;
1947 close c_sources_stg;
1948
1949 elsif (p_level = 'MLS') then
1950
1951 open c_sources_mls (p_entity_code => l_entity_code,
1952 p_source_level_code => l_level,
1953 p_event_class_table => l_event_class_table );
1954 fetch c_sources_mls bulk collect
1955 into l_source_code,
1956 l_table_name,
1957 l_column_name;
1958 close c_sources_mls;
1959
1960 -- for mls, if neither table is in use, return a dummy line to the calling code
1961 if (l_source_code.count = 0) then
1962
1963 l_bodypkg := ' return; ';
1964
1965 fa_cmp_string_pkg.CreateString
1966 (p_package_text => l_BodyPkg
1967 ,p_array_string => l_array_pkg);
1968
1969 p_package_body := l_array_pkg;
1970
1971 return true;
1972 end if;
1973 else
1974 open c_sources (p_entity_code => l_entity_code,
1975 p_source_level_code => l_level,
1976 p_event_class_table => l_event_class_table );
1977 fetch c_sources bulk collect
1978 into l_source_code,
1979 l_table_name,
1980 l_column_name;
1981 close c_sources;
1982 end if;
1983
1984
1985 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1986 fnd_log.string(G_LEVEL_STATEMENT,
1987 G_MODULE_NAME||l_procedure_name,
1988 'l_source_code.count: ' || to_char(l_source_code.count));
1989 END IF;
1990
1991 -- remove all sources already in the base statements
1992 l_count := 0;
1993 l_count2 := l_source_code.count;
1994 l_index := 1;
1995
1996 for i in 1..l_count2 loop
1997
1998 if (instr(upper(l_insert), ' ' || l_source_code(l_index) || ' ') > 0) then -- BUG# 6779783
1999 delete_table_member(l_source_code, l_index);
2000 delete_table_member(l_column_name, l_index);
2001 delete_table_member(l_table_name, l_index);
2002 l_count := l_count + 1;
2003 else
2004 l_index := l_index + 1;
2005 end if;
2006
2007 end loop;
2008
2009 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2010 fnd_log.string(G_LEVEL_STATEMENT,
2011 G_MODULE_NAME||l_procedure_name,
2012 'l_source_code.count after deletion: ' || to_char(l_source_code.count));
2013 END IF;
2014
2015 -- build the alias column array and insure validity
2016 for i in 1..l_table_name.count loop
2017
2018 l_found := false;
2019
2020 for x in 1..l_table_known.count loop
2021 if (l_table_known(x) = l_table_name(i)) then
2022 if (l_table_name(i) <> 'INVALID') then
2023 l_alias(i) := l_alias_known(x);
2024 l_found := true;
2025 end if;
2026 end if;
2027 end loop;
2028
2029 -- if no match found - problem!!!
2030 if (not l_found) then
2031
2032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2033 fnd_log.string(G_LEVEL_STATEMENT,
2034 G_MODULE_NAME||l_procedure_name,
2035 'no match: l_table_name(i): ' || l_table_name(i) );
2036 fnd_log.string(G_LEVEL_STATEMENT,
2037 G_MODULE_NAME||l_procedure_name,
2038 'no match: l_source_code(i): ' || l_source_code(i) );
2039 END IF;
2040
2041 raise table_not_found;
2042 end if;
2043
2044 end loop;
2045
2046 -- build the insert/select clause by appending new aliases/columns
2047
2048 for i in 1..l_source_code.count loop
2049 l_insert := l_insert || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_source_code(i);
2050 l_select := l_select || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_alias(i) || '.' || l_column_name(i) ;
2051 end loop;
2052
2053 -- only line level can increase the from/to...
2054 -- for transactions where we break into staging vs lines,
2055 -- the local variable for known_table already is restricted to tables we can handle
2056 -- so any violation would have been caught above - no further breakdown needed
2057
2058 if (p_level <> 'HEADER') then
2059
2060 -- find distinct alias/tables for adding to from / where clause
2061 for i in 1..l_table_name.count loop
2062 l_found := false;
2063
2064 -- first look in the existing from clause to see if table is being selected
2065 -- we previously set all the aliases and checked to insure the table names are valid
2066 --
2067 -- note that we need to verify validity here... across not only
2068 -- event classes, but also within transactions across staging vs lines object!!!!
2069 -- finally, if we add to staging, we must by nature add the columns to lines too
2070
2071 if (instr(upper(l_from),l_table_name(i))) = 0 then
2072
2073 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_table_name(i) || ' ' || l_alias(i);
2074
2075 if (l_table_name(i) = 'FA_ASSET_KEYWORDS') then
2076 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2077 ' AND ad.asset_key_ccid = key.code_combination_id(+) ';
2078 elsif (l_table_name(i) = 'FA_ASSET_INVOICES') then
2079 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2080 ' AND adj.source_line_id = ai.source_line_id(+) ';
2081 elsif (l_table_name(i) = 'FA_CATEGORIES_B') then
2082 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2083 ' AND cat.category_id = ah.category_id ';
2084 elsif (l_table_name(i) = 'FA_LEASES') then
2085 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2086 ' AND ad.lease_id = ls.lease_id(+) ';
2087 elsif (l_table_name(i) = 'FA_LOCATIONS') then
2088 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2089 ' AND dh.location_id = loc.location_id ';
2090 elsif (l_table_name(i) = 'FA_BOOKS' or l_table_name(i) = 'FA_METHODS') then
2091 -- first, add to DP join to line level if needed
2092 if ((instr(upper(l_from),'FA_DEPRN_PERIODS') = 0 ) and
2093 (p_level= 'STG' or p_extract_type in ('DEPRN', 'DEF'))) then
2094
2095 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' ' || 'FA_DEPRN_PERIODS' || ' ' || 'dp';
2096 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND dp.book_type_code = ctlgd.valuation_method';
2097
2098 if (p_extract_type = 'DEPRN') then
2099 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND dp.period_counter = ctlgd.source_id_int_2';
2100 elsif (p_extract_type = 'DEF') then
2101 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND dp.period_counter = ctlgd.source_id_int_2';
2102 else
2103 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND th.date_effective between dp.period_open_date and nvl(dp.period_close_date, sysdate) ';
2104 end if;
2105 end if;
2106
2107 -- then add books (this must always be added in this block)
2108 -- if (instr(upper(l_from),'FA_BOOKS') = 0 ) then
2109 if (p_level= 'STG' or p_extract_type in ('DEPRN', 'DEF')) then
2110 if (p_extract_type = 'DEPRN') then
2111 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.asset_id = dd.asset_id ' ||
2112 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.book_type_code = dd.book_type_code ' ||
2113 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and nvl(bk.date_ineffective, sysdate) ';
2114 elsif (p_level = 'DEF') then -- deferred
2115 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.asset_id = df.asset_id' ||
2116 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.book_type_code = df.book_type_code' ||
2117 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and nvl(bk.date_ineffective, sysdate) ';
2118 else -- STG
2119 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.asset_id = th.asset_id ' ||
2120 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND bk.book_type_code = th.book_type_code ' ||
2121 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND nvl(dp.period_close_date, sysdate) between bk.date_effective and nvl(bk.date_ineffective, sysdate) ';
2122 end if;
2123
2124 -- need to add book if this is invoked from methods
2125 if (instr(upper(l_from),'FA_BOOKS') = 0 and
2126 (p_level= 'STG' or p_extract_type in ('DEPRN', 'DEF'))) then
2127 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' ' || 'FA_BOOKS' || ' ' || 'bk';
2128 end if;
2129
2130 end if;
2131
2132 -- then methods
2133 if ((l_table_name(i) = 'FA_METHODS') and
2134 (p_level= 'STG' or p_extract_type in ('DEPRN', 'DEF'))) then
2135
2136 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND mt.method_code = bk.deprn_method_code ';
2137 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND nvl(mt.life_in_months, -99) = nvl(bk.life_in_months, -99) ';
2138
2139 end if;
2140
2141 elsif (l_table_name(i) = 'FA_DEPRN_SUMMARY') then
2142 if (p_extract_type = 'DEPRN') then
2143 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND ds.asset_id = ctlgd.source_id_int_1 ' ||
2144 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND ds.book_type_code = ctlgd.source_id_char_1 ' ||
2145 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND ds.period_counter = ctlgd.source_id_int_2 ' ||
2146 fa_cmp_string_pkg.g_chr_newline || ' ' || ' AND ds.deprn_run_id = ctlgd.source_id_int_3 ';
2147
2148 else
2149 raise table_not_found;
2150 end if;
2151 elsif (l_table_name(i) = 'FA_ADDITIONS_TL') then
2152
2153 l_insert := l_insert || ', LANGUAGE ';
2154 l_select := l_select || ', adtl.language ';
2155 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2156 ' WHERE adtl.asset_id = xl.asset_id ' || ' ' ||
2157 ' AND xl.distribution_type_code = ''' || p_extract_type || ''' ';
2158 l_add_tl_in_use := TRUE;
2159 elsif (l_table_name(i) = 'FA_CATEGORIES_TL') then
2160
2161 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2162 ' WHERE cattl.category_id = xl.cat_id ';
2163 if (l_add_tl_in_use) then
2164 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2165 ' AND cattl.language = adtl.language ';
2166 l_where := replace(l_where, 'WHERE cattl', 'AND cattl');
2167
2168 else
2169 l_where := l_where || fa_cmp_string_pkg.g_chr_newline || ' ' ||
2170 ' AND xl.distribution_type_code = ''' || p_extract_type || ''' '; -- bug 8580251
2171 l_insert := l_insert || ', LANGUAGE ';
2172 l_select := l_select || ', cattl.language ';
2173
2174 end if;
2175 end if;
2176 end if;
2177 end loop;
2178 end if;
2179
2180
2181 -- loop (if applicable) for generating both the primary and the reporting statements
2182 -- we only loop for lines (hdr/stg are single)
2183 -- bug 8415466 - fyi: we now loop for staging too
2184
2185 if (p_level = 'STG') then
2186 g_select := l_select;
2187 g_where := l_where;
2188 g_rowcount_debug := l_rowcount_debug;
2189 end if;
2190
2191 for l_loop_index in 1..l_loop_total loop
2192
2193 -- for trx and inter, handle the thid vs member_thid and source vs dest
2194 if (p_level = 'STG') then
2195
2196 if (l_loop_index = 1 and p_extract_type = 'TRX1') then
2197 l_select := replace(g_select, 'select ' ,
2198 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ');
2199 l_where := g_where || c_line_where_stg1a;
2200 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'main lines');
2201 elsif (l_loop_index = 2 and p_extract_type = 'TRX1') then
2202 l_select := replace(g_select, 'select ' ,
2203 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ');
2204 l_where := g_where || c_line_where_stg1b;
2205 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'group lines');
2206 elsif (l_loop_index = 1 and p_extract_type = 'TRX2') then
2207 l_select := replace(g_select, 'select ' ,
2208 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ');
2209 l_where := g_where || c_line_where_stg2a;
2210 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'src main lines');
2211 elsif (l_loop_index = 2 and p_extract_type = 'TRX2') then
2212 l_select := replace(g_select, 'select ' ,
2213 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ');
2214 l_where := g_where || c_line_where_stg2b;
2215 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'src group lines');
2216 elsif (l_loop_index = 3 and p_extract_type = 'TRX2') then
2217 l_select := replace(g_select, 'select ' ,
2218 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ');
2219 l_where := g_where || c_line_where_stg2c;
2220 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'dest main lines');
2221 elsif (l_loop_index = 4 and p_extract_type = 'TRX2') then
2222 l_select := replace(g_select, 'select ' ,
2223 'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ');
2224 l_where := g_where || c_line_where_stg2d;
2225 l_rowcount_debug := replace(g_rowcount_debug, 'lines', 'dest group lines');
2226 end if;
2227
2228 -- for mrc insert the MC_ prefix
2229
2230 elsif (l_loop_index = 2 and p_level = 'LINE') then
2231
2232 -- alter index name to have MC
2233 l_select := replace(l_select, 'FA_ADJUSTMENTS_U1' ,
2234 'FA_MC_ADJUSTMENTS_U1') ;
2235
2236 l_from := replace(l_from, 'fa_book_controls', 'fa_mc_book_controls');
2237 l_rowcount_debug := replace(l_rowcount_debug, 'lines', 'alc lines');
2238
2239 if (p_extract_type = 'DEPRN') then
2240 l_from := replace(l_from, 'fa_deprn_summary', 'fa_mc_deprn_summary');
2241 l_from := replace(l_from, 'fa_deprn_detail', 'fa_mc_deprn_detail');
2242
2243 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2244 ' AND dd.set_of_books_id = bc.set_of_books_id';
2245 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2246 ' AND le.ledger_category_code = decode(l_secondary,' || fa_cmp_string_pkg.g_chr_newline ||
2247 ' 0, ''ALC'', ' || fa_cmp_string_pkg.g_chr_newline ||
2248 ' ''SECONDARY'')';
2249
2250 -- only apend the DS clause if it's used!!!!
2251 if (instr(l_from,'fa_mc_deprn_summary') > 0) then
2252 l_where := l_where || ' and ds.set_of_books_id = bc.set_of_books_id' || fa_cmp_string_pkg.g_chr_newline ;
2253 end if;
2254 elsif (p_extract_type = 'DEF') then
2255 l_from := replace(l_from, 'fa_deferred_deprn', 'fa_mc_deferred_deprn');
2256
2257 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2258 ' AND df.set_of_books_id = bc.set_of_books_id';
2259 else
2260 if (instr(l_from,'fa_book_controls') = 0) then
2261 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' fa_mc_book_controls bc ';
2262 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' gl_ledgers le ';
2263
2264 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2265 ' AND bc.book_type_code = stg.book_type_code ' || fa_cmp_string_pkg.g_chr_newline ||
2266 ' AND bc.set_of_books_id = le.ledger_id ' || fa_cmp_string_pkg.g_chr_newline ||
2267 ' AND le.ledger_category_code = decode(l_secondary,' || fa_cmp_string_pkg.g_chr_newline ||
2268 ' 0, ''ALC'', ' || fa_cmp_string_pkg.g_chr_newline ||
2269 ' ''SECONDARY'')';
2270
2271 end if;
2272
2273 -- Bug 13895687 : Use mc_retirements for ALC
2274 if (instr(l_from,'fa_retirements') > 0) then
2275 l_from := replace(l_from, 'fa_retirements', 'fa_mc_retirements');
2276
2277 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2278 ' AND ret.set_of_books_id = bc.set_of_books_id ' ;
2279 end if;
2280
2281 l_select := replace(l_select, 'stg.ledger_id', 'bc.set_of_books_id');
2282 l_select := replace(l_select, 'stg.currency_code', 'le.currency_code');
2283
2284 l_from := replace(l_from, 'fa_adjustments', 'fa_mc_adjustments');
2285
2286 -- Bug 5159010 changed fa_asset_invoices to upper case
2287 l_from := replace(l_from, 'FA_ASSET_INVOICES', 'fa_mc_asset_invoices');
2288
2289 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2290 ' AND adj.set_of_books_id = bc.set_of_books_id ' ;
2291
2292 -- only apend the AI clause if it's used!!!!
2293 if (instr(l_from,'fa_mc_asset_invoices') > 0) then
2294 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2295 ' AND adj.set_of_books_id = ai.set_of_books_id(+) ' ;
2296 end if;
2297
2298 end if;
2299 elsif (l_loop_index = 2 and p_level = 'HEADER') then
2300
2301 -- new logic to handle special secondary case
2302 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' fa_mc_book_controls mcbc ';
2303 l_from := l_from || ', ' || fa_cmp_string_pkg.g_chr_newline || ' gl_ledgers le ';
2304
2305 l_select := replace(l_select, 'bc.GL_POSTING_ALLOWED_FLAG', 'mcbc.GL_POSTING_ALLOWED_FLAG');
2306
2307 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2308 ' AND mcbc.book_type_code = bc.book_type_code ' || fa_cmp_string_pkg.g_chr_newline ||
2309 ' AND mcbc.set_of_books_id = ctlgd.ledger_id ' || fa_cmp_string_pkg.g_chr_newline ||
2310 ' AND le.ledger_id = mcbc.set_of_books_id ';
2311
2312 -- remove the bc sob join from secondary statement
2313 l_where := replace(l_where, ' AND bc.set_of_books_id = ctlgd.ledger_id ' || fa_cmp_string_pkg.g_chr_newline , '');
2314
2315 elsif (l_loop_index = 1 and p_level = 'HEADER') then
2316 l_where := l_where || fa_cmp_string_pkg.g_chr_newline ||
2317 ' AND bc.set_of_books_id = ctlgd.ledger_id ';
2318 end if;
2319
2320 -- concatonate all the clauses into a single statment
2321 l_bodypkg := l_insert || l_select || l_from || l_where || ';' || fa_cmp_string_pkg.g_chr_newline || l_rowcount_debug || fa_cmp_string_pkg.g_chr_newline;
2322
2323
2324 -- add rowcount debug after the primary statement and before report select
2325 -- also add an if condition around the mrc so we don't needlessly execute statements when mrc is not enabled
2326 if (l_loop_index <> 1) then
2327
2328 if (p_level = 'LINE') then
2329 l_bodypkg := c_mc_if_condition || fa_cmp_string_pkg.g_chr_newline ||
2330 l_bodypkg || fa_cmp_string_pkg.g_chr_newline ||
2331 ' end if; ' || fa_cmp_string_pkg.g_chr_newline ;
2332 elsif (p_level = 'HEADER') then
2333 l_bodypkg := c_sec_if_condition || fa_cmp_string_pkg.g_chr_newline ||
2334 l_bodypkg || fa_cmp_string_pkg.g_chr_newline ||
2335 ' end if; ' || fa_cmp_string_pkg.g_chr_newline ;
2336 end if;
2337
2338 if (p_level = 'STG' and l_loop_index in (2,4)) then
2339 l_bodypkg := c_group_if_condition || fa_cmp_string_pkg.g_chr_newline ||
2340 l_bodypkg || fa_cmp_string_pkg.g_chr_newline ||
2341 ' end if; ' || fa_cmp_string_pkg.g_chr_newline ;
2342 end if;
2343 elsif (l_loop_index = 1) then
2344 if (p_level = 'LINE' or p_level = 'HEADER') then
2345 l_bodypkg := c_non_sec_if_condition || fa_cmp_string_pkg.g_chr_newline ||
2346 l_bodypkg || fa_cmp_string_pkg.g_chr_newline ||
2347 ' end if; ' || fa_cmp_string_pkg.g_chr_newline ;
2348
2349 end if;
2350 end if;
2351
2352 -- build the package value to return
2353
2354 if (l_loop_index = 1) then
2355 fa_cmp_string_pkg.CreateString
2356 (p_package_text => l_BodyPkg
2357 ,p_array_string => l_array_pkg);
2358 else
2359 fa_cmp_string_pkg.CreateString
2360 (p_package_text => l_BodyPkg
2361 ,p_array_string => l_array_body);
2362
2363 l_array_pkg :=
2364 fa_cmp_string_pkg.ConcatTwoStrings
2365 (p_array_string_1 => l_array_pkg
2366 ,p_array_string_2 => l_array_body);
2367 end if;
2368
2369 end loop;
2370
2371 p_package_body := l_array_pkg;
2372
2373 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2374 fnd_log.string(G_LEVEL_PROCEDURE,
2375 G_MODULE_NAME||l_procedure_name||'.end',
2376 'End of procedure');
2377 END IF;
2378
2379 RETURN TRUE;
2380
2381 EXCEPTION
2382
2383 WHEN invalid_mode THEN
2384 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2385 fnd_log.string(G_LEVEL_STATEMENT,
2386 G_MODULE_NAME||l_procedure_name,
2387 'invalid mode');
2388 END IF;
2389 RETURN FALSE;
2390
2391 WHEN table_not_found THEN
2392 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2393 fnd_log.string(G_LEVEL_STATEMENT,
2394 G_MODULE_NAME||l_procedure_name,
2395 'table not found');
2396 END IF;
2397 RETURN FALSE;
2398
2399 WHEN OTHERS THEN
2400 IF c_tables%ISOPEN THEN
2401 close c_tables;
2402 END IF;
2403
2404 IF c_sources%ISOPEN THEN
2405 close c_sources;
2406 END IF;
2407
2408 IF c_sources_stg%ISOPEN THEN
2409 close c_sources_stg;
2410 END IF;
2411
2412 IF c_sources_trx%ISOPEN THEN
2413 close c_sources_trx;
2414 END IF;
2415
2416 IF c_sources_mls%ISOPEN THEN
2417 close c_sources_mls;
2418 END IF;
2419
2420 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2421 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2422 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2423 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2424 END IF;
2425 RETURN FALSE;
2426
2427 END GenerateSourcesExtract;
2428
2429 END fa_xla_cmp_sources_pkg;