[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_EXTRACT_TRX_PKG
Source
1 PACKAGE BODY FA_XLA_EXTRACT_TRX_PKG AS
2
3 /*======================================================================+
4 | Copyright (c) 1997 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | Package Name |
9 | FA_XLA_EXTRACT_TRX_PKG |
10 | |
11 | DESCRIPTION |
12 | Package generated From FA AAD setups |
13 | |
14 | HISTORY |
15 | Generated at 13-08-2013 at 07:08:23 by user ANONYMOUS |
16 +=======================================================================*/
17
18
19 -- TYPES
20 -- globals / constants
21
22 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
23
24 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
25 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
26 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
27 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
28 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
29 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
30
31 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_extract_trx_pkg.';
32
33
34 --+============================================+
35 --| |
36 --| PRIVATE PROCEDURES/FUNCTIONS |
37 --| |
38 --+============================================+
39
40
41
42 /*======================================================================+
43 | |
44 | Private Function |
45 | load_header_data_stg1 |
46 | |
47 +======================================================================*/
48
49 PROCEDURE load_header_data_stg1 IS
50
51 l_procedure_name varchar2(80) := 'load_header_data_stg1';
52
53 BEGIN
54
55 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
56 fnd_log.string(G_LEVEL_PROCEDURE,
57 G_MODULE_NAME||l_procedure_name||'.begin',
58 'Beginning of procedure');
59 END IF;
60
61
62 if (not fa_xla_extract_util_pkg.G_secondary_special) then
63
64
65
66 insert into fa_xla_ext_headers_b_gt (
67 event_id ,
68 DEFAULT_CCID ,
69 BOOK_TYPE_CODE ,
70 PERIOD_NAME ,
71 PERIOD_CLOSE_DATE ,
72 PERIOD_COUNTER ,
73 ACCOUNTING_DATE ,
74 TRANSFER_TO_GL_FLAG ,
75 AP_INTERCOMPANY_ACCT,
76 AR_INTERCOMPANY_ACCT,
77 COST_OF_REMOVAL_CLEARING_ACCT,
78 COST_OF_REMOVAL_GAIN_ACCT,
79 COST_OF_REMOVAL_LOSS_ACCT,
80 DEPRN_ADJUSTMENT_ACCT,
81 NBV_RETIRED_GAIN_ACCT,
82 NBV_RETIRED_LOSS_ACCT,
83 PROCEEDS_OF_SALE_CLEARING_ACCT,
84 PROCEEDS_OF_SALE_GAIN_ACCT,
85 PROCEEDS_OF_SALE_LOSS_ACCT,
86 REVAL_RSV_RETIRED_GAIN_ACCT,
87 REVAL_RSV_RETIRED_LOSS_ACCT )
88 select ctlgd.event_id,
89 bc.FLEXBUILDER_DEFAULTS_CCID ,
90 bc.book_type_code ,
91 dp.PERIOD_NAME ,
92 dp.CALENDAR_PERIOD_CLOSE_DATE ,
93 dp.PERIOD_COUNTER ,
94 ctlgd.event_date ,
95 decode(bc.GL_POSTING_ALLOWED_FLAG ,
96 'YES', 'Y','N') ,
97 bc.AP_INTERCOMPANY_ACCT,
98 bc.AR_INTERCOMPANY_ACCT,
99 bc.COST_OF_REMOVAL_CLEARING_ACCT,
100 bc.COST_OF_REMOVAL_GAIN_ACCT,
101 bc.COST_OF_REMOVAL_LOSS_ACCT,
102 bc.DEPRN_ADJUSTMENT_ACCT,
103 bc.NBV_RETIRED_GAIN_ACCT,
104 bc.NBV_RETIRED_LOSS_ACCT,
105 bc.PROCEEDS_OF_SALE_CLEARING_ACCT,
106 bc.PROCEEDS_OF_SALE_GAIN_ACCT,
107 bc.PROCEEDS_OF_SALE_LOSS_ACCT,
108 bc.REVAL_RSV_RETIRED_GAIN_ACCT,
109 bc.REVAL_RSV_RETIRED_LOSS_ACCT
110 FROM xla_events_gt ctlgd,
111 fa_deprn_periods dp,
112 fa_book_controls bc ,
113 FA_TRANSACTION_HEADERS th
114 WHERE ctlgd.entity_code = 'TRANSACTIONS'
115 AND ctlgd.event_type_code <> 'INFLATION_REVALUATION'
116 AND th.transaction_header_id = ctlgd.source_id_int_1
117 AND ctlgd.valuation_method = dp.book_type_code
118 AND ctlgd.valuation_method = bc.book_type_code
119 AND th.date_effective between dp.period_open_date and
120 nvl(dp.period_close_date, sysdate)
121 AND bc.set_of_books_id = ctlgd.ledger_id ;
122
123 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
124 fnd_log.string(G_LEVEL_PROCEDURE,
125 G_MODULE_NAME||l_procedure_name,
126 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
127 END IF;
128
129
130
131 end if;
132
133 if (fa_xla_extract_util_pkg.G_secondary_special) then
134
135
136
137 insert into fa_xla_ext_headers_b_gt (
138 event_id ,
139 DEFAULT_CCID ,
140 BOOK_TYPE_CODE ,
141 PERIOD_NAME ,
142 PERIOD_CLOSE_DATE ,
143 PERIOD_COUNTER ,
144 ACCOUNTING_DATE ,
145 TRANSFER_TO_GL_FLAG ,
146 AP_INTERCOMPANY_ACCT,
147 AR_INTERCOMPANY_ACCT,
148 COST_OF_REMOVAL_CLEARING_ACCT,
149 COST_OF_REMOVAL_GAIN_ACCT,
150 COST_OF_REMOVAL_LOSS_ACCT,
151 DEPRN_ADJUSTMENT_ACCT,
152 NBV_RETIRED_GAIN_ACCT,
153 NBV_RETIRED_LOSS_ACCT,
154 PROCEEDS_OF_SALE_CLEARING_ACCT,
155 PROCEEDS_OF_SALE_GAIN_ACCT,
156 PROCEEDS_OF_SALE_LOSS_ACCT,
157 REVAL_RSV_RETIRED_GAIN_ACCT,
158 REVAL_RSV_RETIRED_LOSS_ACCT )
159 select ctlgd.event_id,
160 bc.FLEXBUILDER_DEFAULTS_CCID ,
161 bc.book_type_code ,
162 dp.PERIOD_NAME ,
163 dp.CALENDAR_PERIOD_CLOSE_DATE ,
164 dp.PERIOD_COUNTER ,
165 ctlgd.event_date ,
166 decode(mcbc.GL_POSTING_ALLOWED_FLAG ,
167 'YES', 'Y','N') ,
168 bc.AP_INTERCOMPANY_ACCT,
169 bc.AR_INTERCOMPANY_ACCT,
170 bc.COST_OF_REMOVAL_CLEARING_ACCT,
171 bc.COST_OF_REMOVAL_GAIN_ACCT,
172 bc.COST_OF_REMOVAL_LOSS_ACCT,
173 bc.DEPRN_ADJUSTMENT_ACCT,
174 bc.NBV_RETIRED_GAIN_ACCT,
175 bc.NBV_RETIRED_LOSS_ACCT,
176 bc.PROCEEDS_OF_SALE_CLEARING_ACCT,
177 bc.PROCEEDS_OF_SALE_GAIN_ACCT,
178 bc.PROCEEDS_OF_SALE_LOSS_ACCT,
179 bc.REVAL_RSV_RETIRED_GAIN_ACCT,
180 bc.REVAL_RSV_RETIRED_LOSS_ACCT
181 FROM xla_events_gt ctlgd,
182 fa_deprn_periods dp,
183 fa_book_controls bc ,
184 FA_TRANSACTION_HEADERS th ,
185 fa_mc_book_controls mcbc ,
186 gl_ledgers le
187 WHERE ctlgd.entity_code = 'TRANSACTIONS'
188 AND ctlgd.event_type_code <> 'INFLATION_REVALUATION'
189 AND th.transaction_header_id = ctlgd.source_id_int_1
190 AND ctlgd.valuation_method = dp.book_type_code
191 AND ctlgd.valuation_method = bc.book_type_code
192 AND th.date_effective between dp.period_open_date and
193 nvl(dp.period_close_date, sysdate)
194 AND mcbc.book_type_code = bc.book_type_code
195 AND mcbc.set_of_books_id = ctlgd.ledger_id
196 AND le.ledger_id = mcbc.set_of_books_id ;
197
198 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
199 fnd_log.string(G_LEVEL_PROCEDURE,
200 G_MODULE_NAME||l_procedure_name,
201 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
202 END IF;
203
204
205
206 end if;
207
208
209 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
210 fnd_log.string(G_LEVEL_PROCEDURE,
211 G_MODULE_NAME||l_procedure_name||'.end',
212 'End of procedure');
213 END IF;
214
215 EXCEPTION
216 WHEN others THEN
217 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
218 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
219 fnd_message.set_token('ORACLE_ERR',SQLERRM);
220 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
221 END IF;
222 raise;
223
224 end load_header_data_stg1;
225
226
227
228 /*======================================================================+
229 | |
230 | Private Function |
231 | load_header_data_stg2 |
232 | |
233 +======================================================================*/
234
235 PROCEDURE load_header_data_stg2 IS
236
237 l_procedure_name varchar2(80) := 'load_header_data_stg2';
238
239 BEGIN
240
241 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
242 fnd_log.string(G_LEVEL_PROCEDURE,
243 G_MODULE_NAME||l_procedure_name||'.begin',
244 'Beginning of procedure');
245 END IF;
246
247
248 if (not fa_xla_extract_util_pkg.G_secondary_special) then
249
250
251
252 insert into fa_xla_ext_headers_b_gt (
253 event_id ,
254 DEFAULT_CCID ,
255 BOOK_TYPE_CODE ,
256 PERIOD_NAME ,
257 PERIOD_CLOSE_DATE ,
258 PERIOD_COUNTER ,
259 ACCOUNTING_DATE ,
260 TRANSFER_TO_GL_FLAG ,
261 AP_INTERCOMPANY_ACCT,
262 AR_INTERCOMPANY_ACCT )
263 select ctlgd.event_id,
264 bc.FLEXBUILDER_DEFAULTS_CCID ,
265 bc.book_type_code ,
266 dp.PERIOD_NAME ,
267 dp.CALENDAR_PERIOD_CLOSE_DATE ,
268 dp.PERIOD_COUNTER ,
269 ctlgd.event_date ,
270 decode(bc.GL_POSTING_ALLOWED_FLAG ,
271 'YES', 'Y','N') ,
272 bc.AP_INTERCOMPANY_ACCT,
273 bc.AR_INTERCOMPANY_ACCT
274 FROM xla_events_gt ctlgd,
275 fa_deprn_periods dp,
276 fa_book_controls bc ,
277 FA_TRX_REFERENCES trx
278 WHERE ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
279 AND trx.trx_reference_id = ctlgd.source_id_int_1
280 AND trx.event_id = ctlgd.event_id
281 AND trx.book_type_code = dp.book_type_code
282 AND trx.book_type_code = bc.book_type_code
283 AND dp.book_type_code = trx.book_type_code
284 AND trx.creation_date between dp.period_open_date and
285 nvl(dp.period_close_date, sysdate)
286 AND bc.set_of_books_id = ctlgd.ledger_id ;
287
288 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
289 fnd_log.string(G_LEVEL_PROCEDURE,
290 G_MODULE_NAME||l_procedure_name,
291 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
292 END IF;
293
294
295
296 end if;
297
298 if (fa_xla_extract_util_pkg.G_secondary_special) then
299
300
301
302 insert into fa_xla_ext_headers_b_gt (
303 event_id ,
304 DEFAULT_CCID ,
305 BOOK_TYPE_CODE ,
306 PERIOD_NAME ,
307 PERIOD_CLOSE_DATE ,
308 PERIOD_COUNTER ,
309 ACCOUNTING_DATE ,
310 TRANSFER_TO_GL_FLAG ,
311 AP_INTERCOMPANY_ACCT,
312 AR_INTERCOMPANY_ACCT )
313 select ctlgd.event_id,
314 bc.FLEXBUILDER_DEFAULTS_CCID ,
315 bc.book_type_code ,
316 dp.PERIOD_NAME ,
317 dp.CALENDAR_PERIOD_CLOSE_DATE ,
318 dp.PERIOD_COUNTER ,
319 ctlgd.event_date ,
320 decode(mcbc.GL_POSTING_ALLOWED_FLAG ,
321 'YES', 'Y','N') ,
322 bc.AP_INTERCOMPANY_ACCT,
323 bc.AR_INTERCOMPANY_ACCT
324 FROM xla_events_gt ctlgd,
325 fa_deprn_periods dp,
326 fa_book_controls bc ,
327 FA_TRX_REFERENCES trx ,
328 fa_mc_book_controls mcbc ,
329 gl_ledgers le
330 WHERE ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
331 AND trx.trx_reference_id = ctlgd.source_id_int_1
332 AND trx.event_id = ctlgd.event_id
333 AND trx.book_type_code = dp.book_type_code
334 AND trx.book_type_code = bc.book_type_code
335 AND dp.book_type_code = trx.book_type_code
336 AND trx.creation_date between dp.period_open_date and
337 nvl(dp.period_close_date, sysdate)
338 AND mcbc.book_type_code = bc.book_type_code
339 AND mcbc.set_of_books_id = ctlgd.ledger_id
340 AND le.ledger_id = mcbc.set_of_books_id ;
341
342 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
343 fnd_log.string(G_LEVEL_PROCEDURE,
344 G_MODULE_NAME||l_procedure_name,
345 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
346 END IF;
347
348
349
350 end if;
351
352
353 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
354 fnd_log.string(G_LEVEL_PROCEDURE,
355 G_MODULE_NAME||l_procedure_name||'.end',
356 'End of procedure');
357 END IF;
358
359 EXCEPTION
360 WHEN others THEN
361 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
362 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
363 fnd_message.set_token('ORACLE_ERR',SQLERRM);
364 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
365 END IF;
366 raise;
367
368 end load_header_data_stg2;
369
370
371
372 /*======================================================================+
373 | |
374 | Private Function |
375 | load_line_data_stg1 |
376 | |
377 +======================================================================*/
378
379 PROCEDURE load_line_data_stg1 IS
380
381 l_procedure_name varchar2(80) := 'load_line_data_stg1';
382 l_secondary number := 0;
383
384 BEGIN
385
386 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
387 fnd_log.string(G_LEVEL_PROCEDURE,
388 G_MODULE_NAME||l_procedure_name||'.begin',
389 'Beginning of procedure');
390 END IF;
391
392 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
393 l_secondary := 1;
394 END IF;
395
396
397 insert into fa_xla_ext_lines_stg_gt (
398 EVENT_ID ,
399 EVENT_TYPE_CODE ,
400 TRANSACTION_HEADER_ID ,
401 MEMBER_TRANSACTION_HEADER_ID ,
402 DISTRIBUTION_TYPE_CODE ,
403 BOOK_TYPE_CODE ,
404 LEDGER_ID ,
405 CURRENCY_CODE ,
406 CAT_ID ,
407 ASSET_TYPE ,
408 ASSET_COST_ACCOUNT_CCID ,
409 ASSET_CLEARING_ACCOUNT_CCID ,
410 CIP_COST_ACCOUNT_CCID ,
411 CIP_CLEARING_ACCOUNT_CCID ,
412 RESERVE_ACCOUNT_CCID ,
413 DEPRN_EXPENSE_ACCOUNT_CCID ,
414 BONUS_RESERVE_ACCT_CCID ,
415 BONUS_EXPENSE_ACCOUNT_CCID ,
416 REVAL_AMORT_ACCOUNT_CCID ,
417 REVAL_RESERVE_ACCOUNT_CCID ,
418 UNPLAN_EXPENSE_ACCOUNT_CCID ,
419 ALT_COST_ACCOUNT_CCID ,
420 WRITE_OFF_ACCOUNT_CCID ,
421 IMPAIR_EXPENSE_ACCOUNT_CCID ,
422 IMPAIR_RESERVE_ACCOUNT_CCID ,
423 CAPITAL_ADJ_ACCOUNT_CCID ,
424 GENERAL_FUND_ACCOUNT_CCID ,
425 DEPRN_EXPENSE_ACCT ,
426 ASSET_CLEARING_ACCT,
427 ASSET_COST_ACCT,
428 BONUS_DEPRN_EXPENSE_ACCT,
429 BONUS_RESERVE_ACCT,
430 CIP_CLEARING_ACCT,
431 CIP_COST_ACCT,
432 DEPRN_RESERVE_ACCT,
433 REVAL_RESERVE_ACCT )
434 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ctlgd.EVENT_ID ,
435 ctlgd.event_type_code ,
436 th.transaction_header_id ,
437 nvl(th.member_transaction_header_id,
438 th.transaction_header_id) ,
439 'TRX' ,
440 bc.book_type_code , -- Bug:6272229
441 bc.set_of_books_id ,
442 le.currency_code ,
443 cb.category_id ,
444 ah.asset_type ,
445 cb.ASSET_COST_ACCOUNT_CCID ,
446 cb.ASSET_CLEARING_ACCOUNT_CCID ,
447 cb.WIP_COST_ACCOUNT_CCID ,
448 cb.WIP_CLEARING_ACCOUNT_CCID ,
449 cb.RESERVE_ACCOUNT_CCID ,
450 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
451 cb.BONUS_RESERVE_ACCT_CCID ,
452 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
453 cb.REVAL_AMORT_ACCOUNT_CCID ,
454 cb.REVAL_RESERVE_ACCOUNT_CCID ,
455 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
456 cb.ALT_COST_ACCOUNT_CCID ,
457 cb.WRITE_OFF_ACCOUNT_CCID ,
458 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
459 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
460 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
461 cb.GENERAL_FUND_ACCOUNT_CCID ,
462 cb.DEPRN_EXPENSE_ACCT ,
463 cb.ASSET_CLEARING_ACCT,
464 cb.ASSET_COST_ACCT,
465 cb.BONUS_DEPRN_EXPENSE_ACCT,
466 cb.BONUS_DEPRN_RESERVE_ACCT,
467 cb.CIP_CLEARING_ACCT,
468 cb.CIP_COST_ACCT,
469 cb.DEPRN_RESERVE_ACCT,
470 cb.REVAL_RESERVE_ACCT
471 from fa_additions_b ad,
472 fa_asset_history ah,
473 fa_book_controls bc,
474 fa_category_books cb,
475 gl_ledgers le,
476 fa_transaction_headers th,
477 xla_events_gt ctlgd
478 where ctlgd.entity_code = 'TRANSACTIONS'
479 AND bc.book_type_code = ctlgd.valuation_method
480 AND le.ledger_id = bc.set_of_books_id
481 AND ad.asset_id = th.asset_id
482 AND ah.asset_id = th.asset_id
483 AND th.transaction_header_id between ah.transaction_header_id_in and
484 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
485 AND cb.category_id = ah.category_id
486 AND cb.book_type_code = ctlgd.valuation_method
487 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
488 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
489 AND th.transaction_header_id = ctlgd.source_id_int_1 ;
490
491 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
492 fnd_log.string(G_LEVEL_PROCEDURE,
493 G_MODULE_NAME||l_procedure_name,
494 'Rows inserted into staging main lines: ' || to_char(SQL%ROWCOUNT));
495 END IF;
496
497
498
499 if (fa_xla_extract_util_pkg.G_group_enabled) then
500
501
502
503 insert into fa_xla_ext_lines_stg_gt (
504 EVENT_ID ,
505 EVENT_TYPE_CODE ,
506 TRANSACTION_HEADER_ID ,
507 MEMBER_TRANSACTION_HEADER_ID ,
508 DISTRIBUTION_TYPE_CODE ,
509 BOOK_TYPE_CODE ,
510 LEDGER_ID ,
511 CURRENCY_CODE ,
512 CAT_ID ,
513 ASSET_TYPE ,
514 ASSET_COST_ACCOUNT_CCID ,
515 ASSET_CLEARING_ACCOUNT_CCID ,
516 CIP_COST_ACCOUNT_CCID ,
517 CIP_CLEARING_ACCOUNT_CCID ,
518 RESERVE_ACCOUNT_CCID ,
519 DEPRN_EXPENSE_ACCOUNT_CCID ,
520 BONUS_RESERVE_ACCT_CCID ,
521 BONUS_EXPENSE_ACCOUNT_CCID ,
522 REVAL_AMORT_ACCOUNT_CCID ,
523 REVAL_RESERVE_ACCOUNT_CCID ,
524 UNPLAN_EXPENSE_ACCOUNT_CCID ,
525 ALT_COST_ACCOUNT_CCID ,
526 WRITE_OFF_ACCOUNT_CCID ,
527 IMPAIR_EXPENSE_ACCOUNT_CCID ,
528 IMPAIR_RESERVE_ACCOUNT_CCID ,
529 CAPITAL_ADJ_ACCOUNT_CCID ,
530 GENERAL_FUND_ACCOUNT_CCID ,
531 DEPRN_EXPENSE_ACCT ,
532 ASSET_CLEARING_ACCT,
533 ASSET_COST_ACCT,
534 BONUS_DEPRN_EXPENSE_ACCT,
535 BONUS_RESERVE_ACCT,
536 CIP_CLEARING_ACCT,
537 CIP_COST_ACCT,
538 DEPRN_RESERVE_ACCT,
539 REVAL_RESERVE_ACCT )
540 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ctlgd.EVENT_ID ,
541 ctlgd.event_type_code ,
542 th.transaction_header_id ,
543 nvl(th.member_transaction_header_id,
544 th.transaction_header_id) ,
545 'TRX' ,
546 bc.book_type_code , -- Bug:6272229
547 bc.set_of_books_id ,
548 le.currency_code ,
549 cb.category_id ,
550 ah.asset_type ,
551 cb.ASSET_COST_ACCOUNT_CCID ,
552 cb.ASSET_CLEARING_ACCOUNT_CCID ,
553 cb.WIP_COST_ACCOUNT_CCID ,
554 cb.WIP_CLEARING_ACCOUNT_CCID ,
555 cb.RESERVE_ACCOUNT_CCID ,
556 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
557 cb.BONUS_RESERVE_ACCT_CCID ,
558 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
559 cb.REVAL_AMORT_ACCOUNT_CCID ,
560 cb.REVAL_RESERVE_ACCOUNT_CCID ,
561 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
562 cb.ALT_COST_ACCOUNT_CCID ,
563 cb.WRITE_OFF_ACCOUNT_CCID ,
564 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
565 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
566 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
567 cb.GENERAL_FUND_ACCOUNT_CCID ,
568 cb.DEPRN_EXPENSE_ACCT ,
569 cb.ASSET_CLEARING_ACCT,
570 cb.ASSET_COST_ACCT,
571 cb.BONUS_DEPRN_EXPENSE_ACCT,
572 cb.BONUS_DEPRN_RESERVE_ACCT,
573 cb.CIP_CLEARING_ACCT,
574 cb.CIP_COST_ACCT,
575 cb.DEPRN_RESERVE_ACCT,
576 cb.REVAL_RESERVE_ACCT
577 from fa_additions_b ad,
578 fa_asset_history ah,
579 fa_book_controls bc,
580 fa_category_books cb,
581 gl_ledgers le,
582 fa_transaction_headers th,
583 xla_events_gt ctlgd
584 where ctlgd.entity_code = 'TRANSACTIONS'
585 AND bc.book_type_code = ctlgd.valuation_method
586 AND le.ledger_id = bc.set_of_books_id
587 AND ad.asset_id = th.asset_id
588 AND ah.asset_id = th.asset_id
589 AND th.transaction_header_id between ah.transaction_header_id_in and
590 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
591 AND cb.category_id = ah.category_id
592 AND cb.book_type_code = ctlgd.valuation_method
593 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
594 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
595 AND th.member_transaction_header_id = ctlgd.source_id_int_1 ;
596
597 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
598 fnd_log.string(G_LEVEL_PROCEDURE,
599 G_MODULE_NAME||l_procedure_name,
600 'Rows inserted into staging group lines: ' || to_char(SQL%ROWCOUNT));
601 END IF;
602
603
604
605 end if;
606
607
608 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
609 fnd_log.string(G_LEVEL_PROCEDURE,
610 G_MODULE_NAME||l_procedure_name||'.end',
611 'End of procedure');
612 END IF;
613
614 EXCEPTION
615 WHEN others THEN
616 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
617 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
618 fnd_message.set_token('ORACLE_ERR',SQLERRM);
619 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
620 END IF;
621 raise;
622
623 end load_line_data_stg1;
624
625
626
627 /*======================================================================+
628 | |
629 | Private Function |
630 | load_line_data_stg2 |
631 | |
632 +======================================================================*/
633
634 PROCEDURE load_line_data_stg2 IS
635
636 l_procedure_name varchar2(80) := 'load_line_data_stg2';
637 l_secondary number := 0;
638
639 BEGIN
640
641 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
642 fnd_log.string(G_LEVEL_PROCEDURE,
643 G_MODULE_NAME||l_procedure_name||'.begin',
644 'Beginning of procedure');
645 END IF;
646
647 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
648 l_secondary := 1;
649 END IF;
650
651
652 insert into fa_xla_ext_lines_stg_gt (
653 EVENT_ID ,
654 EVENT_TYPE_CODE ,
655 TRANSACTION_HEADER_ID ,
656 MEMBER_TRANSACTION_HEADER_ID ,
657 DISTRIBUTION_TYPE_CODE ,
658 BOOK_TYPE_CODE ,
659 LEDGER_ID ,
660 CURRENCY_CODE ,
661 CAT_ID ,
662 ASSET_TYPE ,
663 ASSET_COST_ACCOUNT_CCID ,
664 ASSET_CLEARING_ACCOUNT_CCID ,
665 CIP_COST_ACCOUNT_CCID ,
666 CIP_CLEARING_ACCOUNT_CCID ,
667 RESERVE_ACCOUNT_CCID ,
668 DEPRN_EXPENSE_ACCOUNT_CCID ,
669 BONUS_RESERVE_ACCT_CCID ,
670 BONUS_EXPENSE_ACCOUNT_CCID ,
671 REVAL_AMORT_ACCOUNT_CCID ,
672 REVAL_RESERVE_ACCOUNT_CCID ,
673 UNPLAN_EXPENSE_ACCOUNT_CCID ,
674 ALT_COST_ACCOUNT_CCID ,
675 WRITE_OFF_ACCOUNT_CCID ,
676 IMPAIR_EXPENSE_ACCOUNT_CCID ,
677 IMPAIR_RESERVE_ACCOUNT_CCID ,
678 CAPITAL_ADJ_ACCOUNT_CCID ,
679 GENERAL_FUND_ACCOUNT_CCID ,
680 DEPRN_EXPENSE_ACCT ,
681 ASSET_CLEARING_ACCT,
682 ASSET_COST_ACCT,
683 BONUS_DEPRN_EXPENSE_ACCT,
684 BONUS_RESERVE_ACCT,
685 CIP_CLEARING_ACCT,
686 CIP_COST_ACCT,
687 DEPRN_RESERVE_ACCT,
688 REVAL_RESERVE_ACCT )
689 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ctlgd.EVENT_ID ,
690 ctlgd.event_type_code ,
691 th.transaction_header_id ,
692 nvl(th.member_transaction_header_id,
693 th.transaction_header_id) ,
694 'TRX' ,
695 bc.book_type_code , -- Bug:6272229
696 bc.set_of_books_id ,
697 le.currency_code ,
698 cb.category_id ,
699 ah.asset_type ,
700 cb.ASSET_COST_ACCOUNT_CCID ,
701 cb.ASSET_CLEARING_ACCOUNT_CCID ,
702 cb.WIP_COST_ACCOUNT_CCID ,
703 cb.WIP_CLEARING_ACCOUNT_CCID ,
704 cb.RESERVE_ACCOUNT_CCID ,
705 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
706 cb.BONUS_RESERVE_ACCT_CCID ,
707 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
708 cb.REVAL_AMORT_ACCOUNT_CCID ,
709 cb.REVAL_RESERVE_ACCOUNT_CCID ,
710 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
711 cb.ALT_COST_ACCOUNT_CCID ,
712 cb.WRITE_OFF_ACCOUNT_CCID ,
713 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
714 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
715 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
716 cb.GENERAL_FUND_ACCOUNT_CCID ,
717 cb.DEPRN_EXPENSE_ACCT ,
718 cb.ASSET_CLEARING_ACCT,
719 cb.ASSET_COST_ACCT,
720 cb.BONUS_DEPRN_EXPENSE_ACCT,
721 cb.BONUS_DEPRN_RESERVE_ACCT,
722 cb.CIP_CLEARING_ACCT,
723 cb.CIP_COST_ACCT,
724 cb.DEPRN_RESERVE_ACCT,
725 cb.REVAL_RESERVE_ACCT
726 from fa_additions_b ad,
727 fa_asset_history ah,
728 fa_book_controls bc,
729 fa_category_books cb,
730 gl_ledgers le,
731 fa_transaction_headers th,
732 fa_trx_references trx,
733 xla_events_gt ctlgd
734 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
735 AND trx.trx_reference_id = ctlgd.source_id_int_1
736 AND bc.book_type_code = ctlgd.valuation_method
737 AND le.ledger_id = bc.set_of_books_id
738 AND ad.asset_id = th.asset_id
739 AND ah.asset_id = th.asset_id
740 AND th.transaction_header_id between ah.transaction_header_id_in and
741 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
742 AND cb.category_id = ah.category_id
743 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
744 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
745 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
746 AND th.transaction_header_id = trx.src_transaction_header_id ;
747
748 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
749 fnd_log.string(G_LEVEL_PROCEDURE,
750 G_MODULE_NAME||l_procedure_name,
751 'Rows inserted into staging src main lines: ' || to_char(SQL%ROWCOUNT));
752 END IF;
753
754
755
756 if (fa_xla_extract_util_pkg.G_group_enabled) then
757
758
759
760 insert into fa_xla_ext_lines_stg_gt (
761 EVENT_ID ,
762 EVENT_TYPE_CODE ,
763 TRANSACTION_HEADER_ID ,
764 MEMBER_TRANSACTION_HEADER_ID ,
765 DISTRIBUTION_TYPE_CODE ,
766 BOOK_TYPE_CODE ,
767 LEDGER_ID ,
768 CURRENCY_CODE ,
769 CAT_ID ,
770 ASSET_TYPE ,
771 ASSET_COST_ACCOUNT_CCID ,
772 ASSET_CLEARING_ACCOUNT_CCID ,
773 CIP_COST_ACCOUNT_CCID ,
774 CIP_CLEARING_ACCOUNT_CCID ,
775 RESERVE_ACCOUNT_CCID ,
776 DEPRN_EXPENSE_ACCOUNT_CCID ,
777 BONUS_RESERVE_ACCT_CCID ,
778 BONUS_EXPENSE_ACCOUNT_CCID ,
779 REVAL_AMORT_ACCOUNT_CCID ,
780 REVAL_RESERVE_ACCOUNT_CCID ,
781 UNPLAN_EXPENSE_ACCOUNT_CCID ,
782 ALT_COST_ACCOUNT_CCID ,
783 WRITE_OFF_ACCOUNT_CCID ,
784 IMPAIR_EXPENSE_ACCOUNT_CCID ,
785 IMPAIR_RESERVE_ACCOUNT_CCID ,
786 CAPITAL_ADJ_ACCOUNT_CCID ,
787 GENERAL_FUND_ACCOUNT_CCID ,
788 DEPRN_EXPENSE_ACCT ,
789 ASSET_CLEARING_ACCT,
790 ASSET_COST_ACCT,
791 BONUS_DEPRN_EXPENSE_ACCT,
792 BONUS_RESERVE_ACCT,
793 CIP_CLEARING_ACCT,
794 CIP_COST_ACCT,
795 DEPRN_RESERVE_ACCT,
796 REVAL_RESERVE_ACCT )
797 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ctlgd.EVENT_ID ,
798 ctlgd.event_type_code ,
799 th.transaction_header_id ,
800 nvl(th.member_transaction_header_id,
801 th.transaction_header_id) ,
802 'TRX' ,
803 bc.book_type_code , -- Bug:6272229
804 bc.set_of_books_id ,
805 le.currency_code ,
806 cb.category_id ,
807 ah.asset_type ,
808 cb.ASSET_COST_ACCOUNT_CCID ,
809 cb.ASSET_CLEARING_ACCOUNT_CCID ,
810 cb.WIP_COST_ACCOUNT_CCID ,
811 cb.WIP_CLEARING_ACCOUNT_CCID ,
812 cb.RESERVE_ACCOUNT_CCID ,
813 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
814 cb.BONUS_RESERVE_ACCT_CCID ,
815 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
816 cb.REVAL_AMORT_ACCOUNT_CCID ,
817 cb.REVAL_RESERVE_ACCOUNT_CCID ,
818 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
819 cb.ALT_COST_ACCOUNT_CCID ,
820 cb.WRITE_OFF_ACCOUNT_CCID ,
821 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
822 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
823 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
824 cb.GENERAL_FUND_ACCOUNT_CCID ,
825 cb.DEPRN_EXPENSE_ACCT ,
826 cb.ASSET_CLEARING_ACCT,
827 cb.ASSET_COST_ACCT,
828 cb.BONUS_DEPRN_EXPENSE_ACCT,
829 cb.BONUS_DEPRN_RESERVE_ACCT,
830 cb.CIP_CLEARING_ACCT,
831 cb.CIP_COST_ACCT,
832 cb.DEPRN_RESERVE_ACCT,
833 cb.REVAL_RESERVE_ACCT
834 from fa_additions_b ad,
835 fa_asset_history ah,
836 fa_book_controls bc,
837 fa_category_books cb,
838 gl_ledgers le,
839 fa_transaction_headers th,
840 fa_trx_references trx,
841 xla_events_gt ctlgd
842 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
843 AND trx.trx_reference_id = ctlgd.source_id_int_1
844 AND bc.book_type_code = ctlgd.valuation_method
845 AND le.ledger_id = bc.set_of_books_id
846 AND ad.asset_id = th.asset_id
847 AND ah.asset_id = th.asset_id
848 AND th.transaction_header_id between ah.transaction_header_id_in and
849 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
850 AND cb.category_id = ah.category_id
851 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
852 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
853 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
854 AND th.member_transaction_header_id = trx.src_transaction_header_id ;
855
856 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
857 fnd_log.string(G_LEVEL_PROCEDURE,
858 G_MODULE_NAME||l_procedure_name,
859 'Rows inserted into staging src group lines: ' || to_char(SQL%ROWCOUNT));
860 END IF;
861
862
863
864 end if;
865
866 insert into fa_xla_ext_lines_stg_gt (
867 EVENT_ID ,
868 EVENT_TYPE_CODE ,
869 TRANSACTION_HEADER_ID ,
870 MEMBER_TRANSACTION_HEADER_ID ,
871 DISTRIBUTION_TYPE_CODE ,
872 BOOK_TYPE_CODE ,
873 LEDGER_ID ,
874 CURRENCY_CODE ,
875 CAT_ID ,
876 ASSET_TYPE ,
877 ASSET_COST_ACCOUNT_CCID ,
878 ASSET_CLEARING_ACCOUNT_CCID ,
879 CIP_COST_ACCOUNT_CCID ,
880 CIP_CLEARING_ACCOUNT_CCID ,
881 RESERVE_ACCOUNT_CCID ,
882 DEPRN_EXPENSE_ACCOUNT_CCID ,
883 BONUS_RESERVE_ACCT_CCID ,
884 BONUS_EXPENSE_ACCOUNT_CCID ,
885 REVAL_AMORT_ACCOUNT_CCID ,
886 REVAL_RESERVE_ACCOUNT_CCID ,
887 UNPLAN_EXPENSE_ACCOUNT_CCID ,
888 ALT_COST_ACCOUNT_CCID ,
889 WRITE_OFF_ACCOUNT_CCID ,
890 IMPAIR_EXPENSE_ACCOUNT_CCID ,
891 IMPAIR_RESERVE_ACCOUNT_CCID ,
892 CAPITAL_ADJ_ACCOUNT_CCID ,
893 GENERAL_FUND_ACCOUNT_CCID ,
894 DEPRN_EXPENSE_ACCT ,
895 ASSET_CLEARING_ACCT,
896 ASSET_COST_ACCT,
897 BONUS_DEPRN_EXPENSE_ACCT,
898 BONUS_RESERVE_ACCT,
899 CIP_CLEARING_ACCT,
900 CIP_COST_ACCT,
901 DEPRN_RESERVE_ACCT,
902 REVAL_RESERVE_ACCT )
903 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ctlgd.EVENT_ID ,
904 ctlgd.event_type_code ,
905 th.transaction_header_id ,
906 nvl(th.member_transaction_header_id,
907 th.transaction_header_id) ,
908 'TRX' ,
909 bc.book_type_code , -- Bug:6272229
910 bc.set_of_books_id ,
911 le.currency_code ,
912 cb.category_id ,
913 ah.asset_type ,
914 cb.ASSET_COST_ACCOUNT_CCID ,
915 cb.ASSET_CLEARING_ACCOUNT_CCID ,
916 cb.WIP_COST_ACCOUNT_CCID ,
917 cb.WIP_CLEARING_ACCOUNT_CCID ,
918 cb.RESERVE_ACCOUNT_CCID ,
919 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
920 cb.BONUS_RESERVE_ACCT_CCID ,
921 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
922 cb.REVAL_AMORT_ACCOUNT_CCID ,
923 cb.REVAL_RESERVE_ACCOUNT_CCID ,
924 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
925 cb.ALT_COST_ACCOUNT_CCID ,
926 cb.WRITE_OFF_ACCOUNT_CCID ,
927 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
928 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
929 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
930 cb.GENERAL_FUND_ACCOUNT_CCID ,
931 cb.DEPRN_EXPENSE_ACCT ,
932 cb.ASSET_CLEARING_ACCT,
933 cb.ASSET_COST_ACCT,
934 cb.BONUS_DEPRN_EXPENSE_ACCT,
935 cb.BONUS_DEPRN_RESERVE_ACCT,
936 cb.CIP_CLEARING_ACCT,
937 cb.CIP_COST_ACCT,
938 cb.DEPRN_RESERVE_ACCT,
939 cb.REVAL_RESERVE_ACCT
940 from fa_additions_b ad,
941 fa_asset_history ah,
942 fa_book_controls bc,
943 fa_category_books cb,
944 gl_ledgers le,
945 fa_transaction_headers th,
946 fa_trx_references trx,
947 xla_events_gt ctlgd
948 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
949 AND trx.trx_reference_id = ctlgd.source_id_int_1
950 AND bc.book_type_code = ctlgd.valuation_method
951 AND le.ledger_id = bc.set_of_books_id
952 AND ad.asset_id = th.asset_id
953 AND ah.asset_id = th.asset_id
954 AND th.transaction_header_id between ah.transaction_header_id_in and
955 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
956 AND cb.category_id = ah.category_id
957 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
958 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
959 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
960 AND th.transaction_header_id = trx.dest_transaction_header_id ;
961
962 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
963 fnd_log.string(G_LEVEL_PROCEDURE,
964 G_MODULE_NAME||l_procedure_name,
965 'Rows inserted into staging dest main lines: ' || to_char(SQL%ROWCOUNT));
966 END IF;
967
968
969
970 if (fa_xla_extract_util_pkg.G_group_enabled) then
971
972
973
974 insert into fa_xla_ext_lines_stg_gt (
975 EVENT_ID ,
976 EVENT_TYPE_CODE ,
977 TRANSACTION_HEADER_ID ,
978 MEMBER_TRANSACTION_HEADER_ID ,
979 DISTRIBUTION_TYPE_CODE ,
980 BOOK_TYPE_CODE ,
981 LEDGER_ID ,
982 CURRENCY_CODE ,
983 CAT_ID ,
984 ASSET_TYPE ,
985 ASSET_COST_ACCOUNT_CCID ,
986 ASSET_CLEARING_ACCOUNT_CCID ,
987 CIP_COST_ACCOUNT_CCID ,
988 CIP_CLEARING_ACCOUNT_CCID ,
989 RESERVE_ACCOUNT_CCID ,
990 DEPRN_EXPENSE_ACCOUNT_CCID ,
991 BONUS_RESERVE_ACCT_CCID ,
992 BONUS_EXPENSE_ACCOUNT_CCID ,
993 REVAL_AMORT_ACCOUNT_CCID ,
994 REVAL_RESERVE_ACCOUNT_CCID ,
995 UNPLAN_EXPENSE_ACCOUNT_CCID ,
996 ALT_COST_ACCOUNT_CCID ,
997 WRITE_OFF_ACCOUNT_CCID ,
998 IMPAIR_EXPENSE_ACCOUNT_CCID ,
999 IMPAIR_RESERVE_ACCOUNT_CCID ,
1000 CAPITAL_ADJ_ACCOUNT_CCID ,
1001 GENERAL_FUND_ACCOUNT_CCID ,
1002 DEPRN_EXPENSE_ACCT ,
1003 ASSET_CLEARING_ACCT,
1004 ASSET_COST_ACCT,
1005 BONUS_DEPRN_EXPENSE_ACCT,
1006 BONUS_RESERVE_ACCT,
1007 CIP_CLEARING_ACCT,
1008 CIP_COST_ACCT,
1009 DEPRN_RESERVE_ACCT,
1010 REVAL_RESERVE_ACCT )
1011 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ctlgd.EVENT_ID ,
1012 ctlgd.event_type_code ,
1013 th.transaction_header_id ,
1014 nvl(th.member_transaction_header_id,
1015 th.transaction_header_id) ,
1016 'TRX' ,
1017 bc.book_type_code , -- Bug:6272229
1018 bc.set_of_books_id ,
1019 le.currency_code ,
1020 cb.category_id ,
1021 ah.asset_type ,
1022 cb.ASSET_COST_ACCOUNT_CCID ,
1023 cb.ASSET_CLEARING_ACCOUNT_CCID ,
1024 cb.WIP_COST_ACCOUNT_CCID ,
1025 cb.WIP_CLEARING_ACCOUNT_CCID ,
1026 cb.RESERVE_ACCOUNT_CCID ,
1027 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
1028 cb.BONUS_RESERVE_ACCT_CCID ,
1029 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
1030 cb.REVAL_AMORT_ACCOUNT_CCID ,
1031 cb.REVAL_RESERVE_ACCOUNT_CCID ,
1032 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1033 cb.ALT_COST_ACCOUNT_CCID ,
1034 cb.WRITE_OFF_ACCOUNT_CCID ,
1035 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
1036 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
1037 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
1038 cb.GENERAL_FUND_ACCOUNT_CCID ,
1039 cb.DEPRN_EXPENSE_ACCT ,
1040 cb.ASSET_CLEARING_ACCT,
1041 cb.ASSET_COST_ACCT,
1042 cb.BONUS_DEPRN_EXPENSE_ACCT,
1043 cb.BONUS_DEPRN_RESERVE_ACCT,
1044 cb.CIP_CLEARING_ACCT,
1045 cb.CIP_COST_ACCT,
1046 cb.DEPRN_RESERVE_ACCT,
1047 cb.REVAL_RESERVE_ACCT
1048 from fa_additions_b ad,
1049 fa_asset_history ah,
1050 fa_book_controls bc,
1051 fa_category_books cb,
1052 gl_ledgers le,
1053 fa_transaction_headers th,
1054 fa_trx_references trx,
1055 xla_events_gt ctlgd
1056 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
1057 AND trx.trx_reference_id = ctlgd.source_id_int_1
1058 AND bc.book_type_code = ctlgd.valuation_method
1059 AND le.ledger_id = bc.set_of_books_id
1060 AND ad.asset_id = th.asset_id
1061 AND ah.asset_id = th.asset_id
1062 AND th.transaction_header_id between ah.transaction_header_id_in and
1063 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
1064 AND cb.category_id = ah.category_id
1065 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
1066 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
1067 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
1068 AND th.member_transaction_header_id = trx.dest_transaction_header_id ;
1069
1070 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1071 fnd_log.string(G_LEVEL_PROCEDURE,
1072 G_MODULE_NAME||l_procedure_name,
1073 'Rows inserted into staging dest group lines: ' || to_char(SQL%ROWCOUNT));
1074 END IF;
1075
1076
1077
1078 end if;
1079
1080
1081 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1082 fnd_log.string(G_LEVEL_PROCEDURE,
1083 G_MODULE_NAME||l_procedure_name||'.end',
1084 'End of procedure');
1085 END IF;
1086
1087 EXCEPTION
1088 WHEN others THEN
1089 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1090 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1091 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1092 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1093 END IF;
1094 raise;
1095
1096 end load_line_data_stg2;
1097
1098
1099
1100 /*======================================================================+
1101 | |
1102 | Private Function |
1103 | load_line_data_fin1 |
1104 | |
1105 +======================================================================*/
1106
1107 PROCEDURE load_line_data_fin1 IS
1108
1109 l_procedure_name varchar2(80) := 'load_line_data_fin1';
1110 l_secondary number := 0;
1111
1112 BEGIN
1113
1114 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1115 fnd_log.string(G_LEVEL_PROCEDURE,
1116 G_MODULE_NAME||l_procedure_name||'.begin',
1117 'Beginning of procedure');
1118 END IF;
1119
1120 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
1121 l_secondary := 1;
1122 END IF;
1123
1124
1125 if (not fa_xla_extract_util_pkg.G_secondary_special) then
1126
1127
1128
1129 insert into fa_xla_ext_lines_b_gt (
1130 EVENT_ID ,
1131 LINE_NUMBER ,
1132 DISTRIBUTION_ID ,
1133 DISTRIBUTION_TYPE_CODE ,
1134 LEDGER_ID ,
1135 CURRENCY_CODE ,
1136 BOOK_TYPE_CODE ,
1137 GENERATED_CCID ,
1138 ASSET_ID ,
1139 CAT_ID ,
1140 ASSET_TYPE ,
1141 ASSET_COST_ACCOUNT_CCID ,
1142 ASSET_CLEARING_ACCOUNT_CCID ,
1143 CIP_COST_ACCOUNT_CCID ,
1144 CIP_CLEARING_ACCOUNT_CCID ,
1145 RESERVE_ACCOUNT_CCID ,
1146 DEPRN_EXPENSE_ACCOUNT_CCID ,
1147 BONUS_RESERVE_ACCT_CCID ,
1148 BONUS_EXPENSE_ACCOUNT_CCID ,
1149 REVAL_AMORT_ACCOUNT_CCID ,
1150 REVAL_RESERVE_ACCOUNT_CCID ,
1151 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1152 ALT_COST_ACCOUNT_CCID ,
1153 WRITE_OFF_ACCOUNT_CCID ,
1154 DEPRN_EXPENSE_ACCT ,
1155 IMPAIR_EXPENSE_ACCOUNT_CCID ,
1156 IMPAIR_RESERVE_ACCOUNT_CCID ,
1157 CAPITAL_ADJ_ACCOUNT_CCID ,
1158 GENERAL_FUND_ACCOUNT_CCID ,
1159 ENTERED_AMOUNT ,
1160 ADJUSTMENT_LINE_ID,
1161 ADJUSTMENT_TYPE,
1162 PAYABLES_CCID,
1163 EXPENSE_ACCOUNT_CCID,
1164 ASSET_CLEARING_ACCT,
1165 ASSET_COST_ACCT,
1166 BONUS_DEPRN_EXPENSE_ACCT,
1167 BONUS_RESERVE_ACCT,
1168 CIP_CLEARING_ACCT,
1169 CIP_COST_ACCT,
1170 DEPRN_RESERVE_ACCT,
1171 REVAL_RESERVE_ACCT,
1172 TRANSACTION_HEADER_ID )
1173 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1174 adj.adjustment_line_id ,
1175 adj.distribution_id ,
1176 stg.distribution_type_code ,
1177 stg.ledger_id ,
1178 stg.currency_code ,
1179 stg.book_type_code ,
1180 adj.code_combination_id ,
1181 adj.asset_id ,
1182 stg.cat_id ,
1183 stg.asset_type ,
1184 stg.ASSET_COST_ACCOUNT_CCID ,
1185 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1186 stg.CIP_COST_ACCOUNT_CCID ,
1187 stg.CIP_CLEARING_ACCOUNT_CCID ,
1188 stg.RESERVE_ACCOUNT_CCID ,
1189 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1190 stg.BONUS_RESERVE_ACCT_CCID ,
1191 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1192 stg.REVAL_AMORT_ACCOUNT_CCID ,
1193 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1194 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1195 stg.ALT_COST_ACCOUNT_CCID ,
1196 stg.WRITE_OFF_ACCOUNT_CCID ,
1197 stg.DEPRN_EXPENSE_ACCT ,
1198 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
1199 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
1200 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
1201 stg.GENERAL_FUND_ACCOUNT_CCID ,
1202 decode(adj.adjustment_type,
1203 'COST CLEARING',
1204 decode(debit_credit_flag,
1205 'CR', adjustment_amount,
1206 -1 * adjustment_amount),
1207 'RESERVE',
1208 decode(debit_credit_flag,
1209 'CR', adjustment_amount,
1210 -1 * adjustment_amount),
1211 'BONUS RESERVE',
1212 decode(debit_credit_flag,
1213 'CR', adjustment_amount,
1214 -1 * adjustment_amount),
1215 'REVAL RESERVE',
1216 decode(debit_credit_flag,
1217 'CR', adjustment_amount,
1218 -1 * adjustment_amount),
1219 'CIP COST',
1220 decode(stg.event_type_code,
1221 'CAPITALIZATION',
1222 decode(debit_credit_flag,
1223 'CR', adjustment_amount,
1224 -1 * adjustment_amount),
1225 'REVERSE_CAPITALIZATION',
1226 decode(debit_credit_flag,
1227 'CR', adjustment_amount,
1228 -1 * adjustment_amount),
1229 decode(debit_credit_flag,
1230 'DR', adjustment_amount,
1231 -1 * adjustment_amount)),
1232 'COST',
1233 decode(debit_credit_flag,
1234 'DR', adjustment_amount,
1235 -1 * adjustment_amount),
1236 'EXPENSE',
1237 decode(debit_credit_flag,
1238 'DR', adjustment_amount,
1239 -1 * adjustment_amount),
1240 'BONUS EXPENSE',
1241 decode(debit_credit_flag,
1242 'DR', adjustment_amount,
1243 -1 * adjustment_amount),
1244 'NBV RETIRED',
1245 decode(debit_credit_flag,
1246 'DR', adjustment_amount,
1247 -1 * adjustment_amount),
1248 'PROCEEDS CLR',
1249 decode(debit_credit_flag,
1250 'DR', adjustment_amount,
1251 -1 * adjustment_amount),
1252 'REMOVALCOST CLR',
1253 decode(debit_credit_flag,
1254 'CR', adjustment_amount,
1255 -1 * adjustment_amount),
1256 'IMPAIR EXPENSE',
1257 decode(debit_credit_flag,
1258 'DR',adjustment_amount,
1259 -1 * adjustment_amount),
1260 'IMPAIR RESERVE',
1261 decode(debit_credit_flag,
1262 'CR',adjustment_amount,
1263 -1 * adjustment_amount),
1264 'CAPITAL ADJ',
1265 decode(debit_credit_flag,
1266 'DR',adjustment_amount,
1267 -1 * adjustment_amount),
1268 'GENERAL FUND',
1269 decode(debit_credit_flag,
1270 'CR',adjustment_amount,
1271 -1 * adjustment_amount),
1272 'LINK IMPAIR EXP',
1273 decode(debit_credit_flag,
1274 'CR',adjustment_amount,
1275 -1 * adjustment_amount),
1276 'REVAL LOSS',
1277 decode(debit_credit_flag,
1278 'DR',adjustment_amount,
1279 -1 * adjustment_amount),
1280 'IMPAIR OFF EXP',
1281 decode(debit_credit_flag,
1282 'DR',adjustment_amount,
1283 -1 * adjustment_amount),
1284 'REVAL OFF EXP',
1285 decode(debit_credit_flag,
1286 'DR',adjustment_amount,
1287 -1 * adjustment_amount),
1288 decode(debit_credit_flag,
1289 'DR', adjustment_amount,
1290 -1 * adjustment_amount)) ,
1291 adj.ADJUSTMENT_LINE_ID,
1292 adj.ADJUSTMENT_TYPE,
1293 ai.PAYABLES_CODE_COMBINATION_ID,
1294 dh.CODE_COMBINATION_ID,
1295 stg.ASSET_CLEARING_ACCT,
1296 stg.ASSET_COST_ACCT,
1297 stg.BONUS_DEPRN_EXPENSE_ACCT,
1298 stg.BONUS_RESERVE_ACCT,
1299 stg.CIP_CLEARING_ACCT,
1300 stg.CIP_COST_ACCT,
1301 stg.DEPRN_RESERVE_ACCT,
1302 stg.REVAL_RESERVE_ACCT,
1303 stg.TRANSACTION_HEADER_ID
1304 from fa_xla_ext_lines_stg_gt stg,
1305 fa_adjustments adj,
1306 fa_distribution_history dh,
1307 fa_locations loc,
1308 fa_lookups lu ,
1309 FA_ASSET_INVOICES ai
1310 WHERE adj.transaction_header_id = stg.transaction_header_id
1311 AND adj.book_type_code = stg.book_type_code
1312 AND adj.distribution_id = dh.distribution_id
1313 AND dh.location_id = loc.location_id
1314 -- AND dh.assigned_to = emp.employee_id(+)
1315 AND lu.lookup_type = 'JOURNAL ENTRIES'
1316 AND lu.lookup_code = adj.source_type_code || ' ' ||
1317 decode (adj.adjustment_type,
1318 'CIP COST', 'COST',
1319 adj.adjustment_type)
1320 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1321 AND nvl(adj.track_member_flag, 'N') = 'N'
1322 AND adj.adjustment_amount <> 0
1323 AND stg.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
1324 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
1325 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
1326 'REVALUATION', 'CIP_REVALUATION',
1327 'ROLLBACK_REVALUATION', 'CIP_ROLLBACK_REVALUATION',
1328 'DEPRECIATION_ADJUSTMENTS',
1329 'UNPLANNED_DEPRECIATION',
1330 'TERMINAL_GAIN_LOSS',
1331 'RETIREMENT_ADJUSTMENTS',
1332 'IMPAIRMENT', 'ROLLBACK_IMPAIRMENT')
1333 AND adj.source_line_id = ai.source_line_id(+) ;
1334
1335 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1336 fnd_log.string(G_LEVEL_PROCEDURE,
1337 G_MODULE_NAME||l_procedure_name,
1338 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1339 END IF;
1340
1341
1342
1343 end if;
1344
1345 if (fa_xla_extract_util_pkg.G_alc_enabled or
1346 fa_xla_extract_util_pkg.G_secondary_special) then
1347
1348
1349
1350 insert into fa_xla_ext_lines_b_gt (
1351 EVENT_ID ,
1352 LINE_NUMBER ,
1353 DISTRIBUTION_ID ,
1354 DISTRIBUTION_TYPE_CODE ,
1355 LEDGER_ID ,
1356 CURRENCY_CODE ,
1357 BOOK_TYPE_CODE ,
1358 GENERATED_CCID ,
1359 ASSET_ID ,
1360 CAT_ID ,
1361 ASSET_TYPE ,
1362 ASSET_COST_ACCOUNT_CCID ,
1363 ASSET_CLEARING_ACCOUNT_CCID ,
1364 CIP_COST_ACCOUNT_CCID ,
1365 CIP_CLEARING_ACCOUNT_CCID ,
1366 RESERVE_ACCOUNT_CCID ,
1367 DEPRN_EXPENSE_ACCOUNT_CCID ,
1368 BONUS_RESERVE_ACCT_CCID ,
1369 BONUS_EXPENSE_ACCOUNT_CCID ,
1370 REVAL_AMORT_ACCOUNT_CCID ,
1371 REVAL_RESERVE_ACCOUNT_CCID ,
1372 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1373 ALT_COST_ACCOUNT_CCID ,
1374 WRITE_OFF_ACCOUNT_CCID ,
1375 DEPRN_EXPENSE_ACCT ,
1376 IMPAIR_EXPENSE_ACCOUNT_CCID ,
1377 IMPAIR_RESERVE_ACCOUNT_CCID ,
1378 CAPITAL_ADJ_ACCOUNT_CCID ,
1379 GENERAL_FUND_ACCOUNT_CCID ,
1380 ENTERED_AMOUNT ,
1381 ADJUSTMENT_LINE_ID,
1382 ADJUSTMENT_TYPE,
1383 PAYABLES_CCID,
1384 EXPENSE_ACCOUNT_CCID,
1385 ASSET_CLEARING_ACCT,
1386 ASSET_COST_ACCT,
1387 BONUS_DEPRN_EXPENSE_ACCT,
1388 BONUS_RESERVE_ACCT,
1389 CIP_CLEARING_ACCT,
1390 CIP_COST_ACCT,
1391 DEPRN_RESERVE_ACCT,
1392 REVAL_RESERVE_ACCT,
1393 TRANSACTION_HEADER_ID )
1394 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1395 adj.adjustment_line_id ,
1396 adj.distribution_id ,
1397 stg.distribution_type_code ,
1398 bc.set_of_books_id ,
1399 le.currency_code ,
1400 stg.book_type_code ,
1401 adj.code_combination_id ,
1402 adj.asset_id ,
1403 stg.cat_id ,
1404 stg.asset_type ,
1405 stg.ASSET_COST_ACCOUNT_CCID ,
1406 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1407 stg.CIP_COST_ACCOUNT_CCID ,
1408 stg.CIP_CLEARING_ACCOUNT_CCID ,
1409 stg.RESERVE_ACCOUNT_CCID ,
1410 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1411 stg.BONUS_RESERVE_ACCT_CCID ,
1412 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1413 stg.REVAL_AMORT_ACCOUNT_CCID ,
1414 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1415 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1416 stg.ALT_COST_ACCOUNT_CCID ,
1417 stg.WRITE_OFF_ACCOUNT_CCID ,
1418 stg.DEPRN_EXPENSE_ACCT ,
1419 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
1420 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
1421 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
1422 stg.GENERAL_FUND_ACCOUNT_CCID ,
1423 decode(adj.adjustment_type,
1424 'COST CLEARING',
1425 decode(debit_credit_flag,
1426 'CR', adjustment_amount,
1427 -1 * adjustment_amount),
1428 'RESERVE',
1429 decode(debit_credit_flag,
1430 'CR', adjustment_amount,
1431 -1 * adjustment_amount),
1432 'BONUS RESERVE',
1433 decode(debit_credit_flag,
1434 'CR', adjustment_amount,
1435 -1 * adjustment_amount),
1436 'REVAL RESERVE',
1437 decode(debit_credit_flag,
1438 'CR', adjustment_amount,
1439 -1 * adjustment_amount),
1440 'CIP COST',
1441 decode(stg.event_type_code,
1442 'CAPITALIZATION',
1443 decode(debit_credit_flag,
1444 'CR', adjustment_amount,
1445 -1 * adjustment_amount),
1446 'REVERSE_CAPITALIZATION',
1447 decode(debit_credit_flag,
1448 'CR', adjustment_amount,
1449 -1 * adjustment_amount),
1450 decode(debit_credit_flag,
1451 'DR', adjustment_amount,
1452 -1 * adjustment_amount)),
1453 'COST',
1454 decode(debit_credit_flag,
1455 'DR', adjustment_amount,
1456 -1 * adjustment_amount),
1457 'EXPENSE',
1458 decode(debit_credit_flag,
1459 'DR', adjustment_amount,
1460 -1 * adjustment_amount),
1461 'BONUS EXPENSE',
1462 decode(debit_credit_flag,
1463 'DR', adjustment_amount,
1464 -1 * adjustment_amount),
1465 'NBV RETIRED',
1466 decode(debit_credit_flag,
1467 'DR', adjustment_amount,
1468 -1 * adjustment_amount),
1469 'PROCEEDS CLR',
1470 decode(debit_credit_flag,
1471 'DR', adjustment_amount,
1472 -1 * adjustment_amount),
1473 'REMOVALCOST CLR',
1474 decode(debit_credit_flag,
1475 'CR', adjustment_amount,
1476 -1 * adjustment_amount),
1477 'IMPAIR EXPENSE',
1478 decode(debit_credit_flag,
1479 'DR',adjustment_amount,
1480 -1 * adjustment_amount),
1481 'IMPAIR RESERVE',
1482 decode(debit_credit_flag,
1483 'CR',adjustment_amount,
1484 -1 * adjustment_amount),
1485 'CAPITAL ADJ',
1486 decode(debit_credit_flag,
1487 'DR',adjustment_amount,
1488 -1 * adjustment_amount),
1489 'GENERAL FUND',
1490 decode(debit_credit_flag,
1491 'CR',adjustment_amount,
1492 -1 * adjustment_amount),
1493 'LINK IMPAIR EXP',
1494 decode(debit_credit_flag,
1495 'CR',adjustment_amount,
1496 -1 * adjustment_amount),
1497 'REVAL LOSS',
1498 decode(debit_credit_flag,
1499 'DR',adjustment_amount,
1500 -1 * adjustment_amount),
1501 'IMPAIR OFF EXP',
1502 decode(debit_credit_flag,
1503 'DR',adjustment_amount,
1504 -1 * adjustment_amount),
1505 'REVAL OFF EXP',
1506 decode(debit_credit_flag,
1507 'DR',adjustment_amount,
1508 -1 * adjustment_amount),
1509 decode(debit_credit_flag,
1510 'DR', adjustment_amount,
1511 -1 * adjustment_amount)) ,
1512 adj.ADJUSTMENT_LINE_ID,
1513 adj.ADJUSTMENT_TYPE,
1514 ai.PAYABLES_CODE_COMBINATION_ID,
1515 dh.CODE_COMBINATION_ID,
1516 stg.ASSET_CLEARING_ACCT,
1517 stg.ASSET_COST_ACCT,
1518 stg.BONUS_DEPRN_EXPENSE_ACCT,
1519 stg.BONUS_RESERVE_ACCT,
1520 stg.CIP_CLEARING_ACCT,
1521 stg.CIP_COST_ACCT,
1522 stg.DEPRN_RESERVE_ACCT,
1523 stg.REVAL_RESERVE_ACCT,
1524 stg.TRANSACTION_HEADER_ID
1525 from fa_xla_ext_lines_stg_gt stg,
1526 fa_mc_adjustments adj,
1527 fa_distribution_history dh,
1528 fa_locations loc,
1529 fa_lookups lu ,
1530 fa_mc_asset_invoices ai,
1531 fa_mc_book_controls bc ,
1532 gl_ledgers le
1533 WHERE adj.transaction_header_id = stg.transaction_header_id
1534 AND adj.book_type_code = stg.book_type_code
1535 AND adj.distribution_id = dh.distribution_id
1536 AND dh.location_id = loc.location_id
1537 -- AND dh.assigned_to = emp.employee_id(+)
1538 AND lu.lookup_type = 'JOURNAL ENTRIES'
1539 AND lu.lookup_code = adj.source_type_code || ' ' ||
1540 decode (adj.adjustment_type,
1541 'CIP COST', 'COST',
1542 adj.adjustment_type)
1543 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1544 AND nvl(adj.track_member_flag, 'N') = 'N'
1545 AND adj.adjustment_amount <> 0
1546 AND stg.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
1547 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
1548 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
1549 'REVALUATION', 'CIP_REVALUATION',
1550 'ROLLBACK_REVALUATION', 'CIP_ROLLBACK_REVALUATION',
1551 'DEPRECIATION_ADJUSTMENTS',
1552 'UNPLANNED_DEPRECIATION',
1553 'TERMINAL_GAIN_LOSS',
1554 'RETIREMENT_ADJUSTMENTS',
1555 'IMPAIRMENT', 'ROLLBACK_IMPAIRMENT')
1556 AND adj.source_line_id = ai.source_line_id(+)
1557 AND bc.book_type_code = stg.book_type_code
1558 AND bc.set_of_books_id = le.ledger_id
1559 AND le.ledger_category_code = decode(l_secondary,
1560 0, 'ALC',
1561 'SECONDARY')
1562 AND adj.set_of_books_id = bc.set_of_books_id
1563 AND adj.set_of_books_id = ai.set_of_books_id(+) ;
1564
1565 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1566 fnd_log.string(G_LEVEL_PROCEDURE,
1567 G_MODULE_NAME||l_procedure_name,
1568 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
1569 END IF;
1570
1571
1572
1573 end if;
1574
1575
1576 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1577 fnd_log.string(G_LEVEL_PROCEDURE,
1578 G_MODULE_NAME||l_procedure_name||'.end',
1579 'End of procedure');
1580 END IF;
1581
1582 EXCEPTION
1583 WHEN others THEN
1584 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1585 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1586 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1587 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1588 END IF;
1589 raise;
1590
1591 end load_line_data_fin1;
1592
1593
1594
1595 /*======================================================================+
1596 | |
1597 | Private Function |
1598 | load_line_data_fin2 |
1599 | |
1600 +======================================================================*/
1601
1602 PROCEDURE load_line_data_fin2 IS
1603
1604 l_procedure_name varchar2(80) := 'load_line_data_fin2';
1605 l_secondary number := 0;
1606
1607 BEGIN
1608
1609 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1610 fnd_log.string(G_LEVEL_PROCEDURE,
1611 G_MODULE_NAME||l_procedure_name||'.begin',
1612 'Beginning of procedure');
1613 END IF;
1614
1615 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
1616 l_secondary := 1;
1617 END IF;
1618
1619
1620 if (not fa_xla_extract_util_pkg.G_secondary_special) then
1621
1622
1623
1624 insert into fa_xla_ext_lines_b_gt (
1625 EVENT_ID ,
1626 LINE_NUMBER ,
1627 DISTRIBUTION_ID ,
1628 DISTRIBUTION_TYPE_CODE ,
1629 LEDGER_ID ,
1630 CURRENCY_CODE ,
1631 BOOK_TYPE_CODE ,
1632 GENERATED_CCID ,
1633 ASSET_ID ,
1634 CAT_ID ,
1635 ASSET_TYPE ,
1636 ASSET_COST_ACCOUNT_CCID ,
1637 ASSET_CLEARING_ACCOUNT_CCID ,
1638 CIP_COST_ACCOUNT_CCID ,
1639 CIP_CLEARING_ACCOUNT_CCID ,
1640 RESERVE_ACCOUNT_CCID ,
1641 DEPRN_EXPENSE_ACCOUNT_CCID ,
1642 BONUS_RESERVE_ACCT_CCID ,
1643 BONUS_EXPENSE_ACCOUNT_CCID ,
1644 REVAL_AMORT_ACCOUNT_CCID ,
1645 REVAL_RESERVE_ACCOUNT_CCID ,
1646 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1647 ALT_COST_ACCOUNT_CCID ,
1648 WRITE_OFF_ACCOUNT_CCID ,
1649 DEPRN_EXPENSE_ACCT ,
1650 IMPAIR_EXPENSE_ACCOUNT_CCID ,
1651 IMPAIR_RESERVE_ACCOUNT_CCID ,
1652 CAPITAL_ADJ_ACCOUNT_CCID ,
1653 GENERAL_FUND_ACCOUNT_CCID ,
1654 ENTERED_AMOUNT ,
1655 ADJUSTMENT_LINE_ID,
1656 ADJUSTMENT_TYPE,
1657 SOURCE_DEST_CODE,
1658 PAYABLES_CCID,
1659 EXPENSE_ACCOUNT_CCID,
1660 ASSET_CLEARING_ACCT,
1661 ASSET_COST_ACCT,
1662 BONUS_DEPRN_EXPENSE_ACCT,
1663 BONUS_RESERVE_ACCT,
1664 CIP_CLEARING_ACCT,
1665 CIP_COST_ACCT,
1666 DEPRN_RESERVE_ACCT,
1667 REVAL_RESERVE_ACCT,
1668 TRANSACTION_HEADER_ID )
1669 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1670 adj.adjustment_line_id ,
1671 adj.distribution_id ,
1672 stg.distribution_type_code ,
1673 stg.ledger_id ,
1674 stg.currency_code ,
1675 stg.book_type_code ,
1676 adj.code_combination_id ,
1677 adj.asset_id ,
1678 stg.cat_id ,
1679 stg.asset_type ,
1680 stg.ASSET_COST_ACCOUNT_CCID ,
1681 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1682 stg.CIP_COST_ACCOUNT_CCID ,
1683 stg.CIP_CLEARING_ACCOUNT_CCID ,
1684 stg.RESERVE_ACCOUNT_CCID ,
1685 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1686 stg.BONUS_RESERVE_ACCT_CCID ,
1687 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1688 stg.REVAL_AMORT_ACCOUNT_CCID ,
1689 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1690 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1691 stg.ALT_COST_ACCOUNT_CCID ,
1692 stg.WRITE_OFF_ACCOUNT_CCID ,
1693 stg.DEPRN_EXPENSE_ACCT ,
1694 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
1695 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
1696 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
1697 stg.GENERAL_FUND_ACCOUNT_CCID ,
1698 decode(adj.source_dest_code,
1699 'SOURCE',
1700 decode(adj.adjustment_type,
1701 'RESERVE',
1702 decode(debit_credit_flag,
1703 'DR', adjustment_amount,
1704 -1 * adjustment_amount),
1705 'BONUS RESERVE',
1706 decode(debit_credit_flag,
1707 'DR', adjustment_amount,
1708 -1 * adjustment_amount),
1709 'REVAL RESERVE',
1710 decode(debit_credit_flag,
1711 'DR', adjustment_amount,
1712 -1 * adjustment_amount),
1713 'IMPAIR RESERVE',
1714 decode(debit_credit_flag,
1715 'DR', adjustment_amount,
1716 -1 * adjustment_amount),
1717 'CAPITAL ADJ',
1718 decode(debit_credit_flag,
1719 'CR', adjustment_amount,
1720 -1 * adjustment_amount),
1721 'GENERAL FUND',
1722 decode(debit_credit_flag,
1723 'DR', adjustment_amount,
1724 -1 * adjustment_amount),
1725 decode(debit_credit_flag,
1726 'CR', adjustment_amount,
1727 -1 * adjustment_amount)),
1728 decode(adj.adjustment_type,
1729 'RESERVE',
1730 decode(debit_credit_flag,
1731 'CR', adjustment_amount,
1732 -1 * adjustment_amount),
1733 'BONUS RESERVE',
1734 decode(debit_credit_flag,
1735 'CR', adjustment_amount,
1736 -1 * adjustment_amount),
1737 'REVAL RESERVE',
1738 decode(debit_credit_flag,
1739 'CR', adjustment_amount,
1740 -1 * adjustment_amount),
1741 'IMPAIR RESERVE',
1742 decode(debit_credit_flag,
1743 'CR', adjustment_amount,
1744 -1 * adjustment_amount),
1745 'CAPITAL ADJ',
1746 decode(debit_credit_flag,
1747 'DR', adjustment_amount,
1748 -1 * adjustment_amount),
1749 'GENERAL FUND',
1750 decode(debit_credit_flag,
1751 'CR', adjustment_amount,
1752 -1 * adjustment_amount),
1753 decode(debit_credit_flag,
1754 'DR', adjustment_amount,
1755 -1 * adjustment_amount))) ,
1756 adj.ADJUSTMENT_LINE_ID,
1757 adj.ADJUSTMENT_TYPE,
1758 adj.SOURCE_DEST_CODE,
1759 ai.PAYABLES_CODE_COMBINATION_ID,
1760 dh.CODE_COMBINATION_ID,
1761 stg.ASSET_CLEARING_ACCT,
1762 stg.ASSET_COST_ACCT,
1763 stg.BONUS_DEPRN_EXPENSE_ACCT,
1764 stg.BONUS_RESERVE_ACCT,
1765 stg.CIP_CLEARING_ACCT,
1766 stg.CIP_COST_ACCT,
1767 stg.DEPRN_RESERVE_ACCT,
1768 stg.REVAL_RESERVE_ACCT,
1769 stg.TRANSACTION_HEADER_ID
1770 from fa_xla_ext_lines_stg_gt stg,
1771 fa_adjustments adj,
1772 fa_distribution_history dh,
1773 fa_locations loc,
1774 fa_lookups lu ,
1775 FA_ASSET_INVOICES ai
1776 WHERE adj.transaction_header_id = stg.transaction_header_id
1777 AND adj.book_type_code = stg.book_type_code
1778 AND adj.distribution_id = dh.distribution_id
1779 AND dh.location_id = loc.location_id
1780 -- AND dh.assigned_to = emp.employee_id(+)
1781 AND lu.lookup_type = 'JOURNAL ENTRIES'
1782 AND lu.lookup_code = adj.source_type_code || ' ' ||
1783 decode (adj.adjustment_type,
1784 'CIP COST', 'COST',
1785 adj.adjustment_type)
1786 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1787 AND nvl(adj.track_member_flag, 'N') = 'N'
1788 AND adj.adjustment_amount <> 0
1789 AND stg.event_type_code in ('SOURCE_LINE_TRANSFERS',
1790 'CIP_SOURCE_LINE_TRANSFERS',
1791 'RESERVE_TRANSFERS')
1792 AND adj.source_line_id = ai.source_line_id(+) ;
1793
1794 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1795 fnd_log.string(G_LEVEL_PROCEDURE,
1796 G_MODULE_NAME||l_procedure_name,
1797 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1798 END IF;
1799
1800
1801
1802 end if;
1803
1804 if (fa_xla_extract_util_pkg.G_alc_enabled or
1805 fa_xla_extract_util_pkg.G_secondary_special) then
1806
1807
1808
1809 insert into fa_xla_ext_lines_b_gt (
1810 EVENT_ID ,
1811 LINE_NUMBER ,
1812 DISTRIBUTION_ID ,
1813 DISTRIBUTION_TYPE_CODE ,
1814 LEDGER_ID ,
1815 CURRENCY_CODE ,
1816 BOOK_TYPE_CODE ,
1817 GENERATED_CCID ,
1818 ASSET_ID ,
1819 CAT_ID ,
1820 ASSET_TYPE ,
1821 ASSET_COST_ACCOUNT_CCID ,
1822 ASSET_CLEARING_ACCOUNT_CCID ,
1823 CIP_COST_ACCOUNT_CCID ,
1824 CIP_CLEARING_ACCOUNT_CCID ,
1825 RESERVE_ACCOUNT_CCID ,
1826 DEPRN_EXPENSE_ACCOUNT_CCID ,
1827 BONUS_RESERVE_ACCT_CCID ,
1828 BONUS_EXPENSE_ACCOUNT_CCID ,
1829 REVAL_AMORT_ACCOUNT_CCID ,
1830 REVAL_RESERVE_ACCOUNT_CCID ,
1831 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1832 ALT_COST_ACCOUNT_CCID ,
1833 WRITE_OFF_ACCOUNT_CCID ,
1834 DEPRN_EXPENSE_ACCT ,
1835 IMPAIR_EXPENSE_ACCOUNT_CCID ,
1836 IMPAIR_RESERVE_ACCOUNT_CCID ,
1837 CAPITAL_ADJ_ACCOUNT_CCID ,
1838 GENERAL_FUND_ACCOUNT_CCID ,
1839 ENTERED_AMOUNT ,
1840 ADJUSTMENT_LINE_ID,
1841 ADJUSTMENT_TYPE,
1842 SOURCE_DEST_CODE,
1843 PAYABLES_CCID,
1844 EXPENSE_ACCOUNT_CCID,
1845 ASSET_CLEARING_ACCT,
1846 ASSET_COST_ACCT,
1847 BONUS_DEPRN_EXPENSE_ACCT,
1848 BONUS_RESERVE_ACCT,
1849 CIP_CLEARING_ACCT,
1850 CIP_COST_ACCT,
1851 DEPRN_RESERVE_ACCT,
1852 REVAL_RESERVE_ACCT,
1853 TRANSACTION_HEADER_ID )
1854 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1855 adj.adjustment_line_id ,
1856 adj.distribution_id ,
1857 stg.distribution_type_code ,
1858 bc.set_of_books_id ,
1859 le.currency_code ,
1860 stg.book_type_code ,
1861 adj.code_combination_id ,
1862 adj.asset_id ,
1863 stg.cat_id ,
1864 stg.asset_type ,
1865 stg.ASSET_COST_ACCOUNT_CCID ,
1866 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1867 stg.CIP_COST_ACCOUNT_CCID ,
1868 stg.CIP_CLEARING_ACCOUNT_CCID ,
1869 stg.RESERVE_ACCOUNT_CCID ,
1870 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1871 stg.BONUS_RESERVE_ACCT_CCID ,
1872 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1873 stg.REVAL_AMORT_ACCOUNT_CCID ,
1874 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1875 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1876 stg.ALT_COST_ACCOUNT_CCID ,
1877 stg.WRITE_OFF_ACCOUNT_CCID ,
1878 stg.DEPRN_EXPENSE_ACCT ,
1879 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
1880 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
1881 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
1882 stg.GENERAL_FUND_ACCOUNT_CCID ,
1883 decode(adj.source_dest_code,
1884 'SOURCE',
1885 decode(adj.adjustment_type,
1886 'RESERVE',
1887 decode(debit_credit_flag,
1888 'DR', adjustment_amount,
1889 -1 * adjustment_amount),
1890 'BONUS RESERVE',
1891 decode(debit_credit_flag,
1892 'DR', adjustment_amount,
1893 -1 * adjustment_amount),
1894 'REVAL RESERVE',
1895 decode(debit_credit_flag,
1896 'DR', adjustment_amount,
1897 -1 * adjustment_amount),
1898 'IMPAIR RESERVE',
1899 decode(debit_credit_flag,
1900 'DR', adjustment_amount,
1901 -1 * adjustment_amount),
1902 'CAPITAL ADJ',
1903 decode(debit_credit_flag,
1904 'CR', adjustment_amount,
1905 -1 * adjustment_amount),
1906 'GENERAL FUND',
1907 decode(debit_credit_flag,
1908 'DR', adjustment_amount,
1909 -1 * adjustment_amount),
1910 decode(debit_credit_flag,
1911 'CR', adjustment_amount,
1912 -1 * adjustment_amount)),
1913 decode(adj.adjustment_type,
1914 'RESERVE',
1915 decode(debit_credit_flag,
1916 'CR', adjustment_amount,
1917 -1 * adjustment_amount),
1918 'BONUS RESERVE',
1919 decode(debit_credit_flag,
1920 'CR', adjustment_amount,
1921 -1 * adjustment_amount),
1922 'REVAL RESERVE',
1923 decode(debit_credit_flag,
1924 'CR', adjustment_amount,
1925 -1 * adjustment_amount),
1926 'IMPAIR RESERVE',
1927 decode(debit_credit_flag,
1928 'CR', adjustment_amount,
1929 -1 * adjustment_amount),
1930 'CAPITAL ADJ',
1931 decode(debit_credit_flag,
1932 'DR', adjustment_amount,
1933 -1 * adjustment_amount),
1934 'GENERAL FUND',
1935 decode(debit_credit_flag,
1936 'CR', adjustment_amount,
1937 -1 * adjustment_amount),
1938 decode(debit_credit_flag,
1939 'DR', adjustment_amount,
1940 -1 * adjustment_amount))) ,
1941 adj.ADJUSTMENT_LINE_ID,
1942 adj.ADJUSTMENT_TYPE,
1943 adj.SOURCE_DEST_CODE,
1944 ai.PAYABLES_CODE_COMBINATION_ID,
1945 dh.CODE_COMBINATION_ID,
1946 stg.ASSET_CLEARING_ACCT,
1947 stg.ASSET_COST_ACCT,
1948 stg.BONUS_DEPRN_EXPENSE_ACCT,
1949 stg.BONUS_RESERVE_ACCT,
1950 stg.CIP_CLEARING_ACCT,
1951 stg.CIP_COST_ACCT,
1952 stg.DEPRN_RESERVE_ACCT,
1953 stg.REVAL_RESERVE_ACCT,
1954 stg.TRANSACTION_HEADER_ID
1955 from fa_xla_ext_lines_stg_gt stg,
1956 fa_mc_adjustments adj,
1957 fa_distribution_history dh,
1958 fa_locations loc,
1959 fa_lookups lu ,
1960 fa_mc_asset_invoices ai,
1961 fa_mc_book_controls bc ,
1962 gl_ledgers le
1963 WHERE adj.transaction_header_id = stg.transaction_header_id
1964 AND adj.book_type_code = stg.book_type_code
1965 AND adj.distribution_id = dh.distribution_id
1966 AND dh.location_id = loc.location_id
1967 -- AND dh.assigned_to = emp.employee_id(+)
1968 AND lu.lookup_type = 'JOURNAL ENTRIES'
1969 AND lu.lookup_code = adj.source_type_code || ' ' ||
1970 decode (adj.adjustment_type,
1971 'CIP COST', 'COST',
1972 adj.adjustment_type)
1973 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1974 AND nvl(adj.track_member_flag, 'N') = 'N'
1975 AND adj.adjustment_amount <> 0
1976 AND stg.event_type_code in ('SOURCE_LINE_TRANSFERS',
1977 'CIP_SOURCE_LINE_TRANSFERS',
1978 'RESERVE_TRANSFERS')
1979 AND adj.source_line_id = ai.source_line_id(+)
1980 AND bc.book_type_code = stg.book_type_code
1981 AND bc.set_of_books_id = le.ledger_id
1982 AND le.ledger_category_code = decode(l_secondary,
1983 0, 'ALC',
1984 'SECONDARY')
1985 AND adj.set_of_books_id = bc.set_of_books_id
1986 AND adj.set_of_books_id = ai.set_of_books_id(+) ;
1987
1988 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1989 fnd_log.string(G_LEVEL_PROCEDURE,
1990 G_MODULE_NAME||l_procedure_name,
1991 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
1992 END IF;
1993
1994
1995
1996 end if;
1997
1998
1999 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2000 fnd_log.string(G_LEVEL_PROCEDURE,
2001 G_MODULE_NAME||l_procedure_name||'.end',
2002 'End of procedure');
2003 END IF;
2004
2005 EXCEPTION
2006 WHEN others THEN
2007 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2008 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2009 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2010 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2011 END IF;
2012 raise;
2013
2014 end load_line_data_fin2;
2015
2016
2017
2018 /*======================================================================+
2019 | |
2020 | Private Function |
2021 | load_line_data_xfr |
2022 | |
2023 +======================================================================*/
2024
2025 PROCEDURE load_line_data_xfr IS
2026
2027 l_procedure_name varchar2(80) := 'load_line_data_xfr';
2028 l_secondary number := 0;
2029
2030 BEGIN
2031
2032 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2033 fnd_log.string(G_LEVEL_PROCEDURE,
2034 G_MODULE_NAME||l_procedure_name||'.begin',
2035 'Beginning of procedure');
2036 END IF;
2037
2038 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
2039 l_secondary := 1;
2040 END IF;
2041
2042
2043 if (not fa_xla_extract_util_pkg.G_secondary_special) then
2044
2045
2046
2047 insert into fa_xla_ext_lines_b_gt (
2048 EVENT_ID ,
2049 LINE_NUMBER ,
2050 DISTRIBUTION_ID ,
2051 DISTRIBUTION_TYPE_CODE ,
2052 LEDGER_ID ,
2053 CURRENCY_CODE ,
2054 BOOK_TYPE_CODE ,
2055 GENERATED_CCID ,
2056 ASSET_ID ,
2057 CAT_ID ,
2058 ASSET_TYPE ,
2059 ASSET_COST_ACCOUNT_CCID ,
2060 ASSET_CLEARING_ACCOUNT_CCID ,
2061 CIP_COST_ACCOUNT_CCID ,
2062 CIP_CLEARING_ACCOUNT_CCID ,
2063 RESERVE_ACCOUNT_CCID ,
2064 DEPRN_EXPENSE_ACCOUNT_CCID ,
2065 BONUS_RESERVE_ACCT_CCID ,
2066 BONUS_EXPENSE_ACCOUNT_CCID ,
2067 REVAL_AMORT_ACCOUNT_CCID ,
2068 REVAL_RESERVE_ACCOUNT_CCID ,
2069 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2070 ALT_COST_ACCOUNT_CCID ,
2071 WRITE_OFF_ACCOUNT_CCID ,
2072 DEPRN_EXPENSE_ACCT ,
2073 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2074 IMPAIR_RESERVE_ACCOUNT_CCID ,
2075 CAPITAL_ADJ_ACCOUNT_CCID ,
2076 GENERAL_FUND_ACCOUNT_CCID ,
2077 ENTERED_AMOUNT ,
2078 ADJUSTMENT_LINE_ID,
2079 ADJUSTMENT_TYPE,
2080 SOURCE_DEST_CODE,
2081 EXPENSE_ACCOUNT_CCID,
2082 ASSET_COST_ACCT,
2083 BONUS_DEPRN_EXPENSE_ACCT,
2084 BONUS_RESERVE_ACCT,
2085 CIP_COST_ACCT,
2086 DEPRN_RESERVE_ACCT,
2087 REVAL_RESERVE_ACCT,
2088 TRANSACTION_HEADER_ID )
2089 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2090 adj.adjustment_line_id ,
2091 adj.distribution_id ,
2092 stg.distribution_type_code ,
2093 stg.ledger_id ,
2094 stg.currency_code ,
2095 stg.book_type_code ,
2096 adj.code_combination_id ,
2097 adj.asset_id ,
2098 stg.cat_id ,
2099 stg.asset_type ,
2100 stg.ASSET_COST_ACCOUNT_CCID ,
2101 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2102 stg.CIP_COST_ACCOUNT_CCID ,
2103 stg.CIP_CLEARING_ACCOUNT_CCID ,
2104 stg.RESERVE_ACCOUNT_CCID ,
2105 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2106 stg.BONUS_RESERVE_ACCT_CCID ,
2107 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2108 stg.REVAL_AMORT_ACCOUNT_CCID ,
2109 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2110 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2111 stg.ALT_COST_ACCOUNT_CCID ,
2112 stg.WRITE_OFF_ACCOUNT_CCID ,
2113 stg.DEPRN_EXPENSE_ACCT ,
2114 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2115 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
2116 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
2117 stg.GENERAL_FUND_ACCOUNT_CCID ,
2118 decode(adj.source_dest_code,
2119 'SOURCE',
2120 decode(adj.adjustment_type,
2121 'RESERVE',
2122 decode(debit_credit_flag,
2123 'DR', adjustment_amount,
2124 -1 * adjustment_amount),
2125 'BONUS RESERVE',
2126 decode(debit_credit_flag,
2127 'DR', adjustment_amount,
2128 -1 * adjustment_amount),
2129 'REVAL RESERVE',
2130 decode(debit_credit_flag,
2131 'DR', adjustment_amount,
2132 -1 * adjustment_amount),
2133 'IMPAIR RESERVE',
2134 decode(debit_credit_flag,
2135 'DR', adjustment_amount,
2136 -1 * adjustment_amount),
2137 'CAPITAL ADJ',
2138 decode(debit_credit_flag,
2139 'CR', adjustment_amount,
2140 -1 * adjustment_amount),
2141 'GENERAL FUND',
2142 decode(debit_credit_flag,
2143 'DR', adjustment_amount,
2144 -1 * adjustment_amount),
2145 decode(debit_credit_flag,
2146 'CR', adjustment_amount,
2147 -1 * adjustment_amount)),
2148 decode(adj.adjustment_type,
2149 'RESERVE',
2150 decode(debit_credit_flag,
2151 'CR', adjustment_amount,
2152 -1 * adjustment_amount),
2153 'BONUS RESERVE',
2154 decode(debit_credit_flag,
2155 'CR', adjustment_amount,
2156 -1 * adjustment_amount),
2157 'REVAL RESERVE',
2158 decode(debit_credit_flag,
2159 'CR', adjustment_amount,
2160 -1 * adjustment_amount),
2161 'IMPAIR RESERVE',
2162 decode(debit_credit_flag,
2163 'CR', adjustment_amount,
2164 -1 * adjustment_amount),
2165 'CAPITAL ADJ',
2166 decode(debit_credit_flag,
2167 'DR', adjustment_amount,
2168 -1 * adjustment_amount),
2169 'GENERAL FUND',
2170 decode(debit_credit_flag,
2171 'CR', adjustment_amount,
2172 -1 * adjustment_amount),
2173 decode(debit_credit_flag,
2174 'DR', adjustment_amount,
2175 -1 * adjustment_amount))) ,
2176 adj.ADJUSTMENT_LINE_ID,
2177 adj.ADJUSTMENT_TYPE,
2178 adj.SOURCE_DEST_CODE,
2179 dh.CODE_COMBINATION_ID,
2180 stg.ASSET_COST_ACCT,
2181 stg.BONUS_DEPRN_EXPENSE_ACCT,
2182 stg.BONUS_RESERVE_ACCT,
2183 stg.CIP_COST_ACCT,
2184 stg.DEPRN_RESERVE_ACCT,
2185 stg.REVAL_RESERVE_ACCT,
2186 stg.TRANSACTION_HEADER_ID
2187 from fa_xla_ext_lines_stg_gt stg,
2188 fa_adjustments adj,
2189 fa_distribution_history dh,
2190 fa_locations loc,
2191 fa_lookups lu
2192 WHERE adj.transaction_header_id = stg.transaction_header_id
2193 AND adj.book_type_code = stg.book_type_code
2194 AND adj.distribution_id = dh.distribution_id
2195 AND dh.location_id = loc.location_id
2196 -- AND dh.assigned_to = emp.employee_id(+)
2197 AND lu.lookup_type = 'JOURNAL ENTRIES'
2198 AND lu.lookup_code = adj.source_type_code || ' ' ||
2199 decode (adj.adjustment_type,
2200 'CIP COST', 'COST',
2201 adj.adjustment_type)
2202 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2203 AND nvl(adj.track_member_flag, 'N') = 'N'
2204 AND adj.adjustment_amount <> 0
2205 AND stg.event_type_code in ('TRANSFERS', 'CIP_TRANSFERS') ;
2206
2207 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2208 fnd_log.string(G_LEVEL_PROCEDURE,
2209 G_MODULE_NAME||l_procedure_name,
2210 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2211 END IF;
2212
2213
2214
2215 end if;
2216
2217 if (fa_xla_extract_util_pkg.G_alc_enabled or
2218 fa_xla_extract_util_pkg.G_secondary_special) then
2219
2220
2221
2222 insert into fa_xla_ext_lines_b_gt (
2223 EVENT_ID ,
2224 LINE_NUMBER ,
2225 DISTRIBUTION_ID ,
2226 DISTRIBUTION_TYPE_CODE ,
2227 LEDGER_ID ,
2228 CURRENCY_CODE ,
2229 BOOK_TYPE_CODE ,
2230 GENERATED_CCID ,
2231 ASSET_ID ,
2232 CAT_ID ,
2233 ASSET_TYPE ,
2234 ASSET_COST_ACCOUNT_CCID ,
2235 ASSET_CLEARING_ACCOUNT_CCID ,
2236 CIP_COST_ACCOUNT_CCID ,
2237 CIP_CLEARING_ACCOUNT_CCID ,
2238 RESERVE_ACCOUNT_CCID ,
2239 DEPRN_EXPENSE_ACCOUNT_CCID ,
2240 BONUS_RESERVE_ACCT_CCID ,
2241 BONUS_EXPENSE_ACCOUNT_CCID ,
2242 REVAL_AMORT_ACCOUNT_CCID ,
2243 REVAL_RESERVE_ACCOUNT_CCID ,
2244 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2245 ALT_COST_ACCOUNT_CCID ,
2246 WRITE_OFF_ACCOUNT_CCID ,
2247 DEPRN_EXPENSE_ACCT ,
2248 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2249 IMPAIR_RESERVE_ACCOUNT_CCID ,
2250 CAPITAL_ADJ_ACCOUNT_CCID ,
2251 GENERAL_FUND_ACCOUNT_CCID ,
2252 ENTERED_AMOUNT ,
2253 ADJUSTMENT_LINE_ID,
2254 ADJUSTMENT_TYPE,
2255 SOURCE_DEST_CODE,
2256 EXPENSE_ACCOUNT_CCID,
2257 ASSET_COST_ACCT,
2258 BONUS_DEPRN_EXPENSE_ACCT,
2259 BONUS_RESERVE_ACCT,
2260 CIP_COST_ACCT,
2261 DEPRN_RESERVE_ACCT,
2262 REVAL_RESERVE_ACCT,
2263 TRANSACTION_HEADER_ID )
2264 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2265 adj.adjustment_line_id ,
2266 adj.distribution_id ,
2267 stg.distribution_type_code ,
2268 bc.set_of_books_id ,
2269 le.currency_code ,
2270 stg.book_type_code ,
2271 adj.code_combination_id ,
2272 adj.asset_id ,
2273 stg.cat_id ,
2274 stg.asset_type ,
2275 stg.ASSET_COST_ACCOUNT_CCID ,
2276 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2277 stg.CIP_COST_ACCOUNT_CCID ,
2278 stg.CIP_CLEARING_ACCOUNT_CCID ,
2279 stg.RESERVE_ACCOUNT_CCID ,
2280 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2281 stg.BONUS_RESERVE_ACCT_CCID ,
2282 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2283 stg.REVAL_AMORT_ACCOUNT_CCID ,
2284 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2285 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2286 stg.ALT_COST_ACCOUNT_CCID ,
2287 stg.WRITE_OFF_ACCOUNT_CCID ,
2288 stg.DEPRN_EXPENSE_ACCT ,
2289 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2290 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
2291 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
2292 stg.GENERAL_FUND_ACCOUNT_CCID ,
2293 decode(adj.source_dest_code,
2294 'SOURCE',
2295 decode(adj.adjustment_type,
2296 'RESERVE',
2297 decode(debit_credit_flag,
2298 'DR', adjustment_amount,
2299 -1 * adjustment_amount),
2300 'BONUS RESERVE',
2301 decode(debit_credit_flag,
2302 'DR', adjustment_amount,
2303 -1 * adjustment_amount),
2304 'REVAL RESERVE',
2305 decode(debit_credit_flag,
2306 'DR', adjustment_amount,
2307 -1 * adjustment_amount),
2308 'IMPAIR RESERVE',
2309 decode(debit_credit_flag,
2310 'DR', adjustment_amount,
2311 -1 * adjustment_amount),
2312 'CAPITAL ADJ',
2313 decode(debit_credit_flag,
2314 'CR', adjustment_amount,
2315 -1 * adjustment_amount),
2316 'GENERAL FUND',
2317 decode(debit_credit_flag,
2318 'DR', adjustment_amount,
2319 -1 * adjustment_amount),
2320 decode(debit_credit_flag,
2321 'CR', adjustment_amount,
2322 -1 * adjustment_amount)),
2323 decode(adj.adjustment_type,
2324 'RESERVE',
2325 decode(debit_credit_flag,
2326 'CR', adjustment_amount,
2327 -1 * adjustment_amount),
2328 'BONUS RESERVE',
2329 decode(debit_credit_flag,
2330 'CR', adjustment_amount,
2331 -1 * adjustment_amount),
2332 'REVAL RESERVE',
2333 decode(debit_credit_flag,
2334 'CR', adjustment_amount,
2335 -1 * adjustment_amount),
2336 'IMPAIR RESERVE',
2337 decode(debit_credit_flag,
2338 'CR', adjustment_amount,
2339 -1 * adjustment_amount),
2340 'CAPITAL ADJ',
2341 decode(debit_credit_flag,
2342 'DR', adjustment_amount,
2343 -1 * adjustment_amount),
2344 'GENERAL FUND',
2345 decode(debit_credit_flag,
2346 'CR', adjustment_amount,
2347 -1 * adjustment_amount),
2348 decode(debit_credit_flag,
2349 'DR', adjustment_amount,
2350 -1 * adjustment_amount))) ,
2351 adj.ADJUSTMENT_LINE_ID,
2352 adj.ADJUSTMENT_TYPE,
2353 adj.SOURCE_DEST_CODE,
2354 dh.CODE_COMBINATION_ID,
2355 stg.ASSET_COST_ACCT,
2356 stg.BONUS_DEPRN_EXPENSE_ACCT,
2357 stg.BONUS_RESERVE_ACCT,
2358 stg.CIP_COST_ACCT,
2359 stg.DEPRN_RESERVE_ACCT,
2360 stg.REVAL_RESERVE_ACCT,
2361 stg.TRANSACTION_HEADER_ID
2362 from fa_xla_ext_lines_stg_gt stg,
2363 fa_mc_adjustments adj,
2364 fa_distribution_history dh,
2365 fa_locations loc,
2366 fa_lookups lu ,
2367 fa_mc_book_controls bc ,
2368 gl_ledgers le
2369 WHERE adj.transaction_header_id = stg.transaction_header_id
2370 AND adj.book_type_code = stg.book_type_code
2371 AND adj.distribution_id = dh.distribution_id
2372 AND dh.location_id = loc.location_id
2373 -- AND dh.assigned_to = emp.employee_id(+)
2374 AND lu.lookup_type = 'JOURNAL ENTRIES'
2375 AND lu.lookup_code = adj.source_type_code || ' ' ||
2376 decode (adj.adjustment_type,
2377 'CIP COST', 'COST',
2378 adj.adjustment_type)
2379 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2380 AND nvl(adj.track_member_flag, 'N') = 'N'
2381 AND adj.adjustment_amount <> 0
2382 AND stg.event_type_code in ('TRANSFERS', 'CIP_TRANSFERS')
2383 AND bc.book_type_code = stg.book_type_code
2384 AND bc.set_of_books_id = le.ledger_id
2385 AND le.ledger_category_code = decode(l_secondary,
2386 0, 'ALC',
2387 'SECONDARY')
2388 AND adj.set_of_books_id = bc.set_of_books_id ;
2389
2390 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2391 fnd_log.string(G_LEVEL_PROCEDURE,
2392 G_MODULE_NAME||l_procedure_name,
2393 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
2394 END IF;
2395
2396
2397
2398 end if;
2399
2400
2401 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2402 fnd_log.string(G_LEVEL_PROCEDURE,
2403 G_MODULE_NAME||l_procedure_name||'.end',
2404 'End of procedure');
2405 END IF;
2406
2407 EXCEPTION
2408 WHEN others THEN
2409 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2410 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2411 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2412 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2413 END IF;
2414 raise;
2415
2416 end load_line_data_xfr;
2417
2418
2419
2420 /*======================================================================+
2421 | |
2422 | Private Function |
2423 | load_line_data_dist |
2424 | |
2425 +======================================================================*/
2426
2427 PROCEDURE load_line_data_dist IS
2428
2429 l_procedure_name varchar2(80) := 'load_line_data_dist';
2430 l_secondary number := 0;
2431
2432 BEGIN
2433
2434 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2435 fnd_log.string(G_LEVEL_PROCEDURE,
2436 G_MODULE_NAME||l_procedure_name||'.begin',
2437 'Beginning of procedure');
2438 END IF;
2439
2440 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
2441 l_secondary := 1;
2442 END IF;
2443
2444
2445 if (not fa_xla_extract_util_pkg.G_secondary_special) then
2446
2447
2448
2449 insert into fa_xla_ext_lines_b_gt (
2450 EVENT_ID ,
2451 LINE_NUMBER ,
2452 DISTRIBUTION_ID ,
2453 DISTRIBUTION_TYPE_CODE ,
2454 LEDGER_ID ,
2455 CURRENCY_CODE ,
2456 BOOK_TYPE_CODE ,
2457 GENERATED_CCID ,
2458 ASSET_ID ,
2459 CAT_ID ,
2460 ASSET_TYPE ,
2461 ASSET_COST_ACCOUNT_CCID ,
2462 ASSET_CLEARING_ACCOUNT_CCID ,
2463 CIP_COST_ACCOUNT_CCID ,
2464 CIP_CLEARING_ACCOUNT_CCID ,
2465 RESERVE_ACCOUNT_CCID ,
2466 DEPRN_EXPENSE_ACCOUNT_CCID ,
2467 BONUS_RESERVE_ACCT_CCID ,
2468 BONUS_EXPENSE_ACCOUNT_CCID ,
2469 REVAL_AMORT_ACCOUNT_CCID ,
2470 REVAL_RESERVE_ACCOUNT_CCID ,
2471 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2472 ALT_COST_ACCOUNT_CCID ,
2473 WRITE_OFF_ACCOUNT_CCID ,
2474 DEPRN_EXPENSE_ACCT ,
2475 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2476 IMPAIR_RESERVE_ACCOUNT_CCID ,
2477 CAPITAL_ADJ_ACCOUNT_CCID ,
2478 GENERAL_FUND_ACCOUNT_CCID ,
2479 ENTERED_AMOUNT ,
2480 ADJUSTMENT_LINE_ID,
2481 ADJUSTMENT_TYPE,
2482 SOURCE_DEST_CODE,
2483 ASSET_COST_ACCT,
2484 BONUS_DEPRN_EXPENSE_ACCT,
2485 BONUS_RESERVE_ACCT,
2486 CIP_COST_ACCT,
2487 DEPRN_RESERVE_ACCT,
2488 REVAL_RESERVE_ACCT,
2489 EXPENSE_ACCOUNT_CCID,
2490 TRANSACTION_HEADER_ID )
2491 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2492 adj.adjustment_line_id ,
2493 adj.distribution_id ,
2494 stg.distribution_type_code ,
2495 stg.ledger_id ,
2496 stg.currency_code ,
2497 stg.book_type_code ,
2498 adj.code_combination_id ,
2499 adj.asset_id ,
2500 cb.category_id ,
2501 stg.asset_type ,
2502 cb.ASSET_COST_ACCOUNT_CCID ,
2503 cb.ASSET_CLEARING_ACCOUNT_CCID ,
2504 cb.WIP_COST_ACCOUNT_CCID ,
2505 cb.WIP_CLEARING_ACCOUNT_CCID ,
2506 cb.RESERVE_ACCOUNT_CCID ,
2507 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
2508 cb.BONUS_RESERVE_ACCT_CCID ,
2509 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
2510 cb.REVAL_AMORT_ACCOUNT_CCID ,
2511 cb.REVAL_RESERVE_ACCOUNT_CCID ,
2512 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2513 cb.ALT_COST_ACCOUNT_CCID ,
2514 cb.WRITE_OFF_ACCOUNT_CCID ,
2515 cb.DEPRN_EXPENSE_ACCT ,
2516 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2517 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
2518 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
2519 cb.GENERAL_FUND_ACCOUNT_CCID ,
2520 decode(adj.adjustment_type,
2521 'RESERVE',
2522 decode(debit_credit_flag,
2523 'DR', adjustment_amount,
2524 -1 * adjustment_amount),
2525 'BONUS RESERVE',
2526 decode(debit_credit_flag,
2527 'DR', adjustment_amount,
2528 -1 * adjustment_amount),
2529 'REVAL RESERVE',
2530 decode(debit_credit_flag,
2531 'DR', adjustment_amount,
2532 -1 * adjustment_amount),
2533 'IMPAIR RESERVE',
2534 decode(debit_credit_flag,
2535 'DR', adjustment_amount,
2536 -1 * adjustment_amount),
2537 'CAPITAL ADJ',
2538 decode(debit_credit_flag,
2539 'CR', adjustment_amount,
2540 -1 * adjustment_amount),
2541 'GENERAL FUND',
2542 decode(debit_credit_flag,
2543 'DR', adjustment_amount,
2544 -1 * adjustment_amount),
2545 decode(debit_credit_flag,
2546 'CR', adjustment_amount,
2547 -1 * adjustment_amount)) ,
2548 adj.ADJUSTMENT_LINE_ID,
2549 adj.ADJUSTMENT_TYPE,
2550 adj.SOURCE_DEST_CODE,
2551 cb.ASSET_COST_ACCT,
2552 cb.BONUS_DEPRN_EXPENSE_ACCT,
2553 cb.BONUS_DEPRN_RESERVE_ACCT,
2554 cb.CIP_COST_ACCT,
2555 cb.DEPRN_RESERVE_ACCT,
2556 cb.REVAL_RESERVE_ACCT,
2557 dh.CODE_COMBINATION_ID,
2558 stg.TRANSACTION_HEADER_ID
2559 from fa_xla_ext_lines_stg_gt stg,
2560 fa_adjustments adj,
2561 fa_distribution_history dh,
2562 fa_locations loc,
2563 fa_lookups lu ,
2564 fa_asset_history ah,
2565 fa_category_books cb
2566 WHERE adj.transaction_header_id = stg.transaction_header_id
2567 AND adj.book_type_code = stg.book_type_code
2568 AND adj.distribution_id = dh.distribution_id
2569 AND dh.location_id = loc.location_id
2570 -- AND dh.assigned_to = emp.employee_id(+)
2571 AND lu.lookup_type = 'JOURNAL ENTRIES'
2572 AND lu.lookup_code = adj.source_type_code || ' ' ||
2573 decode (adj.adjustment_type,
2574 'CIP COST', 'COST',
2575 adj.adjustment_type)
2576 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2577 AND nvl(adj.track_member_flag, 'N') = 'N'
2578 AND adj.adjustment_amount <> 0
2579 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
2580 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
2581 AND adj.asset_id = ah.asset_id
2582 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
2583 AND cb.category_id = ah.category_id
2584 AND cb.book_type_code = adj.book_type_code
2585 AND adj.source_dest_code = 'SOURCE' ;
2586
2587 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2588 fnd_log.string(G_LEVEL_PROCEDURE,
2589 G_MODULE_NAME||l_procedure_name,
2590 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2591 END IF;
2592
2593
2594
2595 end if;
2596
2597 if (fa_xla_extract_util_pkg.G_alc_enabled or
2598 fa_xla_extract_util_pkg.G_secondary_special) then
2599
2600
2601
2602 insert into fa_xla_ext_lines_b_gt (
2603 EVENT_ID ,
2604 LINE_NUMBER ,
2605 DISTRIBUTION_ID ,
2606 DISTRIBUTION_TYPE_CODE ,
2607 LEDGER_ID ,
2608 CURRENCY_CODE ,
2609 BOOK_TYPE_CODE ,
2610 GENERATED_CCID ,
2611 ASSET_ID ,
2612 CAT_ID ,
2613 ASSET_TYPE ,
2614 ASSET_COST_ACCOUNT_CCID ,
2615 ASSET_CLEARING_ACCOUNT_CCID ,
2616 CIP_COST_ACCOUNT_CCID ,
2617 CIP_CLEARING_ACCOUNT_CCID ,
2618 RESERVE_ACCOUNT_CCID ,
2619 DEPRN_EXPENSE_ACCOUNT_CCID ,
2620 BONUS_RESERVE_ACCT_CCID ,
2621 BONUS_EXPENSE_ACCOUNT_CCID ,
2622 REVAL_AMORT_ACCOUNT_CCID ,
2623 REVAL_RESERVE_ACCOUNT_CCID ,
2624 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2625 ALT_COST_ACCOUNT_CCID ,
2626 WRITE_OFF_ACCOUNT_CCID ,
2627 DEPRN_EXPENSE_ACCT ,
2628 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2629 IMPAIR_RESERVE_ACCOUNT_CCID ,
2630 CAPITAL_ADJ_ACCOUNT_CCID ,
2631 GENERAL_FUND_ACCOUNT_CCID ,
2632 ENTERED_AMOUNT ,
2633 ADJUSTMENT_LINE_ID,
2634 ADJUSTMENT_TYPE,
2635 SOURCE_DEST_CODE,
2636 ASSET_COST_ACCT,
2637 BONUS_DEPRN_EXPENSE_ACCT,
2638 BONUS_RESERVE_ACCT,
2639 CIP_COST_ACCT,
2640 DEPRN_RESERVE_ACCT,
2641 REVAL_RESERVE_ACCT,
2642 EXPENSE_ACCOUNT_CCID,
2643 TRANSACTION_HEADER_ID )
2644 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2645 adj.adjustment_line_id ,
2646 adj.distribution_id ,
2647 stg.distribution_type_code ,
2648 bc.set_of_books_id ,
2649 le.currency_code ,
2650 stg.book_type_code ,
2651 adj.code_combination_id ,
2652 adj.asset_id ,
2653 cb.category_id ,
2654 stg.asset_type ,
2655 cb.ASSET_COST_ACCOUNT_CCID ,
2656 cb.ASSET_CLEARING_ACCOUNT_CCID ,
2657 cb.WIP_COST_ACCOUNT_CCID ,
2658 cb.WIP_CLEARING_ACCOUNT_CCID ,
2659 cb.RESERVE_ACCOUNT_CCID ,
2660 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
2661 cb.BONUS_RESERVE_ACCT_CCID ,
2662 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
2663 cb.REVAL_AMORT_ACCOUNT_CCID ,
2664 cb.REVAL_RESERVE_ACCOUNT_CCID ,
2665 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2666 cb.ALT_COST_ACCOUNT_CCID ,
2667 cb.WRITE_OFF_ACCOUNT_CCID ,
2668 cb.DEPRN_EXPENSE_ACCT ,
2669 cb.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2670 cb.IMPAIR_RESERVE_ACCOUNT_CCID ,
2671 cb.CAPITAL_ADJ_ACCOUNT_CCID ,
2672 cb.GENERAL_FUND_ACCOUNT_CCID ,
2673 decode(adj.adjustment_type,
2674 'RESERVE',
2675 decode(debit_credit_flag,
2676 'DR', adjustment_amount,
2677 -1 * adjustment_amount),
2678 'BONUS RESERVE',
2679 decode(debit_credit_flag,
2680 'DR', adjustment_amount,
2681 -1 * adjustment_amount),
2682 'REVAL RESERVE',
2683 decode(debit_credit_flag,
2684 'DR', adjustment_amount,
2685 -1 * adjustment_amount),
2686 'IMPAIR RESERVE',
2687 decode(debit_credit_flag,
2688 'DR', adjustment_amount,
2689 -1 * adjustment_amount),
2690 'CAPITAL ADJ',
2691 decode(debit_credit_flag,
2692 'CR', adjustment_amount,
2693 -1 * adjustment_amount),
2694 'GENERAL FUND',
2695 decode(debit_credit_flag,
2696 'DR', adjustment_amount,
2697 -1 * adjustment_amount),
2698 decode(debit_credit_flag,
2699 'CR', adjustment_amount,
2700 -1 * adjustment_amount)) ,
2701 adj.ADJUSTMENT_LINE_ID,
2702 adj.ADJUSTMENT_TYPE,
2703 adj.SOURCE_DEST_CODE,
2704 cb.ASSET_COST_ACCT,
2705 cb.BONUS_DEPRN_EXPENSE_ACCT,
2706 cb.BONUS_DEPRN_RESERVE_ACCT,
2707 cb.CIP_COST_ACCT,
2708 cb.DEPRN_RESERVE_ACCT,
2709 cb.REVAL_RESERVE_ACCT,
2710 dh.CODE_COMBINATION_ID,
2711 stg.TRANSACTION_HEADER_ID
2712 from fa_xla_ext_lines_stg_gt stg,
2713 fa_mc_adjustments adj,
2714 fa_distribution_history dh,
2715 fa_locations loc,
2716 fa_lookups lu ,
2717 fa_asset_history ah,
2718 fa_category_books cb ,
2719 fa_mc_book_controls bc ,
2720 gl_ledgers le
2721 WHERE adj.transaction_header_id = stg.transaction_header_id
2722 AND adj.book_type_code = stg.book_type_code
2723 AND adj.distribution_id = dh.distribution_id
2724 AND dh.location_id = loc.location_id
2725 -- AND dh.assigned_to = emp.employee_id(+)
2726 AND lu.lookup_type = 'JOURNAL ENTRIES'
2727 AND lu.lookup_code = adj.source_type_code || ' ' ||
2728 decode (adj.adjustment_type,
2729 'CIP COST', 'COST',
2730 adj.adjustment_type)
2731 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2732 AND nvl(adj.track_member_flag, 'N') = 'N'
2733 AND adj.adjustment_amount <> 0
2734 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
2735 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
2736 AND adj.asset_id = ah.asset_id
2737 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
2738 AND cb.category_id = ah.category_id
2739 AND cb.book_type_code = adj.book_type_code
2740 AND adj.source_dest_code = 'SOURCE'
2741 AND bc.book_type_code = stg.book_type_code
2742 AND bc.set_of_books_id = le.ledger_id
2743 AND le.ledger_category_code = decode(l_secondary,
2744 0, 'ALC',
2745 'SECONDARY')
2746 AND adj.set_of_books_id = bc.set_of_books_id ;
2747
2748 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2749 fnd_log.string(G_LEVEL_PROCEDURE,
2750 G_MODULE_NAME||l_procedure_name,
2751 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
2752 END IF;
2753
2754
2755
2756 end if;
2757
2758 if (not fa_xla_extract_util_pkg.G_secondary_special) then
2759
2760
2761
2762 insert into fa_xla_ext_lines_b_gt (
2763 EVENT_ID ,
2764 LINE_NUMBER ,
2765 DISTRIBUTION_ID ,
2766 DISTRIBUTION_TYPE_CODE ,
2767 LEDGER_ID ,
2768 CURRENCY_CODE ,
2769 BOOK_TYPE_CODE ,
2770 GENERATED_CCID ,
2771 ASSET_ID ,
2772 CAT_ID ,
2773 ASSET_TYPE ,
2774 ASSET_COST_ACCOUNT_CCID ,
2775 ASSET_CLEARING_ACCOUNT_CCID ,
2776 CIP_COST_ACCOUNT_CCID ,
2777 CIP_CLEARING_ACCOUNT_CCID ,
2778 RESERVE_ACCOUNT_CCID ,
2779 DEPRN_EXPENSE_ACCOUNT_CCID ,
2780 BONUS_RESERVE_ACCT_CCID ,
2781 BONUS_EXPENSE_ACCOUNT_CCID ,
2782 REVAL_AMORT_ACCOUNT_CCID ,
2783 REVAL_RESERVE_ACCOUNT_CCID ,
2784 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2785 ALT_COST_ACCOUNT_CCID ,
2786 WRITE_OFF_ACCOUNT_CCID ,
2787 DEPRN_EXPENSE_ACCT ,
2788 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2789 IMPAIR_RESERVE_ACCOUNT_CCID ,
2790 CAPITAL_ADJ_ACCOUNT_CCID ,
2791 GENERAL_FUND_ACCOUNT_CCID ,
2792 ENTERED_AMOUNT ,
2793 ADJUSTMENT_LINE_ID,
2794 ADJUSTMENT_TYPE,
2795 SOURCE_DEST_CODE,
2796 EXPENSE_ACCOUNT_CCID,
2797 ASSET_COST_ACCT,
2798 BONUS_DEPRN_EXPENSE_ACCT,
2799 BONUS_RESERVE_ACCT,
2800 CIP_COST_ACCT,
2801 DEPRN_RESERVE_ACCT,
2802 REVAL_RESERVE_ACCT,
2803 TRANSACTION_HEADER_ID )
2804 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2805 adj.adjustment_line_id ,
2806 adj.distribution_id ,
2807 stg.distribution_type_code ,
2808 stg.ledger_id ,
2809 stg.currency_code ,
2810 stg.book_type_code ,
2811 adj.code_combination_id ,
2812 adj.asset_id ,
2813 stg.cat_id ,
2814 stg.asset_type ,
2815 stg.ASSET_COST_ACCOUNT_CCID ,
2816 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2817 stg.CIP_COST_ACCOUNT_CCID ,
2818 stg.CIP_CLEARING_ACCOUNT_CCID ,
2819 stg.RESERVE_ACCOUNT_CCID ,
2820 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2821 stg.BONUS_RESERVE_ACCT_CCID ,
2822 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2823 stg.REVAL_AMORT_ACCOUNT_CCID ,
2824 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2825 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2826 stg.ALT_COST_ACCOUNT_CCID ,
2827 stg.WRITE_OFF_ACCOUNT_CCID ,
2828 stg.DEPRN_EXPENSE_ACCT ,
2829 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2830 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
2831 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
2832 stg.GENERAL_FUND_ACCOUNT_CCID ,
2833 decode(adj.adjustment_type,
2834 'RESERVE',
2835 decode(debit_credit_flag,
2836 'CR', adjustment_amount,
2837 -1 * adjustment_amount),
2838 'BONUS RESERVE',
2839 decode(debit_credit_flag,
2840 'CR', adjustment_amount,
2841 -1 * adjustment_amount),
2842 'REVAL RESERVE',
2843 decode(debit_credit_flag,
2844 'CR', adjustment_amount,
2845 -1 * adjustment_amount),
2846 'IMPAIR RESERVE',
2847 decode(debit_credit_flag,
2848 'CR', adjustment_amount,
2849 -1 * adjustment_amount),
2850 'CAPITAL ADJ',
2851 decode(debit_credit_flag,
2852 'DR', adjustment_amount,
2853 -1 * adjustment_amount),
2854 'GENERAL FUND',
2855 decode(debit_credit_flag,
2856 'CR', adjustment_amount,
2857 -1 * adjustment_amount),
2858 decode(debit_credit_flag,
2859 'DR', adjustment_amount,
2860 -1 * adjustment_amount)) ,
2861 adj.ADJUSTMENT_LINE_ID,
2862 adj.ADJUSTMENT_TYPE,
2863 adj.SOURCE_DEST_CODE,
2864 dh.CODE_COMBINATION_ID,
2865 stg.ASSET_COST_ACCT,
2866 stg.BONUS_DEPRN_EXPENSE_ACCT,
2867 stg.BONUS_RESERVE_ACCT,
2868 stg.CIP_COST_ACCT,
2869 stg.DEPRN_RESERVE_ACCT,
2870 stg.REVAL_RESERVE_ACCT,
2871 stg.TRANSACTION_HEADER_ID
2872 from fa_xla_ext_lines_stg_gt stg,
2873 fa_adjustments adj,
2874 fa_distribution_history dh,
2875 fa_locations loc,
2876 fa_lookups lu
2877 WHERE adj.transaction_header_id = stg.transaction_header_id
2878 AND adj.book_type_code = stg.book_type_code
2879 AND adj.distribution_id = dh.distribution_id
2880 AND dh.location_id = loc.location_id
2881 -- AND dh.assigned_to = emp.employee_id(+)
2882 AND lu.lookup_type = 'JOURNAL ENTRIES'
2883 AND lu.lookup_code = adj.source_type_code || ' ' ||
2884 decode (adj.adjustment_type,
2885 'CIP COST', 'COST',
2886 adj.adjustment_type)
2887 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2888 AND nvl(adj.track_member_flag, 'N') = 'N'
2889 AND adj.adjustment_amount <> 0
2890 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
2891 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
2892 AND adj.source_dest_code = 'DEST' ;
2893
2894 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2895 fnd_log.string(G_LEVEL_PROCEDURE,
2896 G_MODULE_NAME||l_procedure_name,
2897 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2898 END IF;
2899
2900
2901
2902 end if;
2903
2904 if (fa_xla_extract_util_pkg.G_alc_enabled or
2905 fa_xla_extract_util_pkg.G_secondary_special) then
2906
2907
2908
2909 insert into fa_xla_ext_lines_b_gt (
2910 EVENT_ID ,
2911 LINE_NUMBER ,
2912 DISTRIBUTION_ID ,
2913 DISTRIBUTION_TYPE_CODE ,
2914 LEDGER_ID ,
2915 CURRENCY_CODE ,
2916 BOOK_TYPE_CODE ,
2917 GENERATED_CCID ,
2918 ASSET_ID ,
2919 CAT_ID ,
2920 ASSET_TYPE ,
2921 ASSET_COST_ACCOUNT_CCID ,
2922 ASSET_CLEARING_ACCOUNT_CCID ,
2923 CIP_COST_ACCOUNT_CCID ,
2924 CIP_CLEARING_ACCOUNT_CCID ,
2925 RESERVE_ACCOUNT_CCID ,
2926 DEPRN_EXPENSE_ACCOUNT_CCID ,
2927 BONUS_RESERVE_ACCT_CCID ,
2928 BONUS_EXPENSE_ACCOUNT_CCID ,
2929 REVAL_AMORT_ACCOUNT_CCID ,
2930 REVAL_RESERVE_ACCOUNT_CCID ,
2931 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2932 ALT_COST_ACCOUNT_CCID ,
2933 WRITE_OFF_ACCOUNT_CCID ,
2934 DEPRN_EXPENSE_ACCT ,
2935 IMPAIR_EXPENSE_ACCOUNT_CCID ,
2936 IMPAIR_RESERVE_ACCOUNT_CCID ,
2937 CAPITAL_ADJ_ACCOUNT_CCID ,
2938 GENERAL_FUND_ACCOUNT_CCID ,
2939 ENTERED_AMOUNT ,
2940 ADJUSTMENT_LINE_ID,
2941 ADJUSTMENT_TYPE,
2942 SOURCE_DEST_CODE,
2943 EXPENSE_ACCOUNT_CCID,
2944 ASSET_COST_ACCT,
2945 BONUS_DEPRN_EXPENSE_ACCT,
2946 BONUS_RESERVE_ACCT,
2947 CIP_COST_ACCT,
2948 DEPRN_RESERVE_ACCT,
2949 REVAL_RESERVE_ACCT,
2950 TRANSACTION_HEADER_ID )
2951 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2952 adj.adjustment_line_id ,
2953 adj.distribution_id ,
2954 stg.distribution_type_code ,
2955 bc.set_of_books_id ,
2956 le.currency_code ,
2957 stg.book_type_code ,
2958 adj.code_combination_id ,
2959 adj.asset_id ,
2960 stg.cat_id ,
2961 stg.asset_type ,
2962 stg.ASSET_COST_ACCOUNT_CCID ,
2963 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2964 stg.CIP_COST_ACCOUNT_CCID ,
2965 stg.CIP_CLEARING_ACCOUNT_CCID ,
2966 stg.RESERVE_ACCOUNT_CCID ,
2967 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2968 stg.BONUS_RESERVE_ACCT_CCID ,
2969 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2970 stg.REVAL_AMORT_ACCOUNT_CCID ,
2971 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2972 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2973 stg.ALT_COST_ACCOUNT_CCID ,
2974 stg.WRITE_OFF_ACCOUNT_CCID ,
2975 stg.DEPRN_EXPENSE_ACCT ,
2976 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
2977 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
2978 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
2979 stg.GENERAL_FUND_ACCOUNT_CCID ,
2980 decode(adj.adjustment_type,
2981 'RESERVE',
2982 decode(debit_credit_flag,
2983 'CR', adjustment_amount,
2984 -1 * adjustment_amount),
2985 'BONUS RESERVE',
2986 decode(debit_credit_flag,
2987 'CR', adjustment_amount,
2988 -1 * adjustment_amount),
2989 'REVAL RESERVE',
2990 decode(debit_credit_flag,
2991 'CR', adjustment_amount,
2992 -1 * adjustment_amount),
2993 'IMPAIR RESERVE',
2994 decode(debit_credit_flag,
2995 'CR', adjustment_amount,
2996 -1 * adjustment_amount),
2997 'CAPITAL ADJ',
2998 decode(debit_credit_flag,
2999 'DR', adjustment_amount,
3000 -1 * adjustment_amount),
3001 'GENERAL FUND',
3002 decode(debit_credit_flag,
3003 'CR', adjustment_amount,
3004 -1 * adjustment_amount),
3005 decode(debit_credit_flag,
3006 'DR', adjustment_amount,
3007 -1 * adjustment_amount)) ,
3008 adj.ADJUSTMENT_LINE_ID,
3009 adj.ADJUSTMENT_TYPE,
3010 adj.SOURCE_DEST_CODE,
3011 dh.CODE_COMBINATION_ID,
3012 stg.ASSET_COST_ACCT,
3013 stg.BONUS_DEPRN_EXPENSE_ACCT,
3014 stg.BONUS_RESERVE_ACCT,
3015 stg.CIP_COST_ACCT,
3016 stg.DEPRN_RESERVE_ACCT,
3017 stg.REVAL_RESERVE_ACCT,
3018 stg.TRANSACTION_HEADER_ID
3019 from fa_xla_ext_lines_stg_gt stg,
3020 fa_mc_adjustments adj,
3021 fa_distribution_history dh,
3022 fa_locations loc,
3023 fa_lookups lu ,
3024 fa_mc_book_controls bc ,
3025 gl_ledgers le
3026 WHERE adj.transaction_header_id = stg.transaction_header_id
3027 AND adj.book_type_code = stg.book_type_code
3028 AND adj.distribution_id = dh.distribution_id
3029 AND dh.location_id = loc.location_id
3030 -- AND dh.assigned_to = emp.employee_id(+)
3031 AND lu.lookup_type = 'JOURNAL ENTRIES'
3032 AND lu.lookup_code = adj.source_type_code || ' ' ||
3033 decode (adj.adjustment_type,
3034 'CIP COST', 'COST',
3035 adj.adjustment_type)
3036 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
3037 AND nvl(adj.track_member_flag, 'N') = 'N'
3038 AND adj.adjustment_amount <> 0
3039 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
3040 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
3041 AND adj.source_dest_code = 'DEST'
3042 AND bc.book_type_code = stg.book_type_code
3043 AND bc.set_of_books_id = le.ledger_id
3044 AND le.ledger_category_code = decode(l_secondary,
3045 0, 'ALC',
3046 'SECONDARY')
3047 AND adj.set_of_books_id = bc.set_of_books_id ;
3048
3049 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3050 fnd_log.string(G_LEVEL_PROCEDURE,
3051 G_MODULE_NAME||l_procedure_name,
3052 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
3053 END IF;
3054
3055
3056
3057 end if;
3058
3059
3060 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3061 fnd_log.string(G_LEVEL_PROCEDURE,
3062 G_MODULE_NAME||l_procedure_name||'.end',
3063 'End of procedure');
3064 END IF;
3065
3066 EXCEPTION
3067 WHEN others THEN
3068 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
3069 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3070 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3071 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3072 END IF;
3073 raise;
3074
3075 end load_line_data_dist;
3076
3077
3078
3079 /*======================================================================+
3080 | |
3081 | Private Function |
3082 | load_line_data_ret |
3083 | |
3084 +======================================================================*/
3085
3086 PROCEDURE load_line_data_ret IS
3087
3088 l_procedure_name varchar2(80) := 'load_line_data_ret';
3089 l_secondary number := 0;
3090
3091 BEGIN
3092
3093 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3094 fnd_log.string(G_LEVEL_PROCEDURE,
3095 G_MODULE_NAME||l_procedure_name||'.begin',
3096 'Beginning of procedure');
3097 END IF;
3098
3099 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
3100 l_secondary := 1;
3101 END IF;
3102
3103
3104 if (not fa_xla_extract_util_pkg.G_secondary_special) then
3105
3106
3107
3108 insert into fa_xla_ext_lines_b_gt (
3109 EVENT_ID ,
3110 LINE_NUMBER ,
3111 DISTRIBUTION_ID ,
3112 DISTRIBUTION_TYPE_CODE ,
3113 LEDGER_ID ,
3114 CURRENCY_CODE ,
3115 BOOK_TYPE_CODE ,
3116 GENERATED_CCID ,
3117 ASSET_ID ,
3118 CAT_ID ,
3119 ASSET_TYPE ,
3120 ASSET_COST_ACCOUNT_CCID ,
3121 ASSET_CLEARING_ACCOUNT_CCID ,
3122 CIP_COST_ACCOUNT_CCID ,
3123 CIP_CLEARING_ACCOUNT_CCID ,
3124 RESERVE_ACCOUNT_CCID ,
3125 DEPRN_EXPENSE_ACCOUNT_CCID ,
3126 BONUS_RESERVE_ACCT_CCID ,
3127 BONUS_EXPENSE_ACCOUNT_CCID ,
3128 REVAL_AMORT_ACCOUNT_CCID ,
3129 REVAL_RESERVE_ACCOUNT_CCID ,
3130 UNPLAN_EXPENSE_ACCOUNT_CCID ,
3131 ALT_COST_ACCOUNT_CCID ,
3132 WRITE_OFF_ACCOUNT_CCID ,
3133 DEPRN_EXPENSE_ACCT ,
3134 IMPAIR_EXPENSE_ACCOUNT_CCID ,
3135 IMPAIR_RESERVE_ACCOUNT_CCID ,
3136 CAPITAL_ADJ_ACCOUNT_CCID ,
3137 GENERAL_FUND_ACCOUNT_CCID ,
3138 ENTERED_AMOUNT ,
3139 ADJUSTMENT_LINE_ID,
3140 ADJUSTMENT_TYPE,
3141 EXPENSE_ACCOUNT_CCID,
3142 GAIN_LOSS_AMOUNT,
3143 ASSET_COST_ACCT,
3144 BONUS_DEPRN_EXPENSE_ACCT,
3145 BONUS_RESERVE_ACCT,
3146 CIP_COST_ACCT,
3147 DEPRN_RESERVE_ACCT,
3148 REVAL_RESERVE_ACCT,
3149 TRANSACTION_HEADER_ID )
3150 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
3151 adj.adjustment_line_id ,
3152 adj.distribution_id ,
3153 stg.distribution_type_code ,
3154 stg.ledger_id ,
3155 stg.currency_code ,
3156 stg.book_type_code ,
3157 adj.code_combination_id ,
3158 adj.asset_id ,
3159 stg.cat_id ,
3160 stg.asset_type ,
3161 stg.ASSET_COST_ACCOUNT_CCID ,
3162 stg.ASSET_CLEARING_ACCOUNT_CCID ,
3163 stg.CIP_COST_ACCOUNT_CCID ,
3164 stg.CIP_CLEARING_ACCOUNT_CCID ,
3165 stg.RESERVE_ACCOUNT_CCID ,
3166 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
3167 stg.BONUS_RESERVE_ACCT_CCID ,
3168 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
3169 stg.REVAL_AMORT_ACCOUNT_CCID ,
3170 stg.REVAL_RESERVE_ACCOUNT_CCID ,
3171 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
3172 stg.ALT_COST_ACCOUNT_CCID ,
3173 stg.WRITE_OFF_ACCOUNT_CCID ,
3174 stg.DEPRN_EXPENSE_ACCT ,
3175 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
3176 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
3177 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
3178 stg.GENERAL_FUND_ACCOUNT_CCID ,
3179 decode(adj.adjustment_type,
3180 'RESERVE',
3181 decode(debit_credit_flag,
3182 'DR', adjustment_amount,
3183 -1 * adjustment_amount),
3184 'BONUS RESERVE',
3185 decode(debit_credit_flag,
3186 'DR', adjustment_amount,
3187 -1 * adjustment_amount),
3188 'REVAL RESERVE',
3189 decode(debit_credit_flag,
3190 'DR', adjustment_amount,
3191 -1 * adjustment_amount),
3192 'NBV RETIRED',
3193 decode(debit_credit_flag,
3194 'DR', adjustment_amount,
3195 -1 * adjustment_amount),
3196 'PROCEEDS CLR',
3197 decode(debit_credit_flag,
3198 'DR', adjustment_amount,
3199 -1 * adjustment_amount),
3200 'REMOVALCOST',
3201 decode(debit_credit_flag,
3202 'DR', adjustment_amount,
3203 -1 * adjustment_amount),
3204 'IMPAIR RESERVE',
3205 decode(debit_credit_flag,
3206 'DR', adjustment_amount,
3207 -1 * adjustment_amount),
3208 'CAPITAL ADJ',
3209 decode(debit_credit_flag,
3210 'DR', adjustment_amount,
3211 -1 * adjustment_amount),
3212 'GENERAL FUND',
3213 decode(debit_credit_flag,
3214 'CR', adjustment_amount,
3215 -1 * adjustment_amount),
3216 decode(debit_credit_flag,
3217 'CR', adjustment_amount,
3218 -1 * adjustment_amount)) ,
3219 adj.ADJUSTMENT_LINE_ID,
3220 adj.ADJUSTMENT_TYPE,
3221 dh.CODE_COMBINATION_ID,
3222 ret.GAIN_LOSS_AMOUNT,
3223 stg.ASSET_COST_ACCT,
3224 stg.BONUS_DEPRN_EXPENSE_ACCT,
3225 stg.BONUS_RESERVE_ACCT,
3226 stg.CIP_COST_ACCT,
3227 stg.DEPRN_RESERVE_ACCT,
3228 stg.REVAL_RESERVE_ACCT,
3229 stg.TRANSACTION_HEADER_ID
3230 from fa_xla_ext_lines_stg_gt stg,
3231 fa_adjustments adj,
3232 fa_distribution_history dh,
3233 fa_locations loc,
3234 fa_lookups lu ,
3235 fa_retirements ret
3236 WHERE adj.transaction_header_id = stg.transaction_header_id
3237 AND adj.book_type_code = stg.book_type_code
3238 AND adj.distribution_id = dh.distribution_id
3239 AND dh.location_id = loc.location_id
3240 -- AND dh.assigned_to = emp.employee_id(+)
3241 AND lu.lookup_type = 'JOURNAL ENTRIES'
3242 AND lu.lookup_code = adj.source_type_code || ' ' ||
3243 decode (adj.adjustment_type,
3244 'CIP COST', 'COST',
3245 adj.adjustment_type)
3246 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
3247 AND nvl(adj.track_member_flag, 'N') = 'N'
3248 AND adj.adjustment_amount <> 0
3249 AND stg.event_type_code in ('RETIREMENTS', 'CIP_RETIREMENTS')
3250 AND ret.transaction_header_id_in = stg.member_transaction_header_id ;
3251
3252 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3253 fnd_log.string(G_LEVEL_PROCEDURE,
3254 G_MODULE_NAME||l_procedure_name,
3255 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
3256 END IF;
3257
3258
3259
3260 end if;
3261
3262 if (fa_xla_extract_util_pkg.G_alc_enabled or
3263 fa_xla_extract_util_pkg.G_secondary_special) then
3264
3265
3266
3267 insert into fa_xla_ext_lines_b_gt (
3268 EVENT_ID ,
3269 LINE_NUMBER ,
3270 DISTRIBUTION_ID ,
3271 DISTRIBUTION_TYPE_CODE ,
3272 LEDGER_ID ,
3273 CURRENCY_CODE ,
3274 BOOK_TYPE_CODE ,
3275 GENERATED_CCID ,
3276 ASSET_ID ,
3277 CAT_ID ,
3278 ASSET_TYPE ,
3279 ASSET_COST_ACCOUNT_CCID ,
3280 ASSET_CLEARING_ACCOUNT_CCID ,
3281 CIP_COST_ACCOUNT_CCID ,
3282 CIP_CLEARING_ACCOUNT_CCID ,
3283 RESERVE_ACCOUNT_CCID ,
3284 DEPRN_EXPENSE_ACCOUNT_CCID ,
3285 BONUS_RESERVE_ACCT_CCID ,
3286 BONUS_EXPENSE_ACCOUNT_CCID ,
3287 REVAL_AMORT_ACCOUNT_CCID ,
3288 REVAL_RESERVE_ACCOUNT_CCID ,
3289 UNPLAN_EXPENSE_ACCOUNT_CCID ,
3290 ALT_COST_ACCOUNT_CCID ,
3291 WRITE_OFF_ACCOUNT_CCID ,
3292 DEPRN_EXPENSE_ACCT ,
3293 IMPAIR_EXPENSE_ACCOUNT_CCID ,
3294 IMPAIR_RESERVE_ACCOUNT_CCID ,
3295 CAPITAL_ADJ_ACCOUNT_CCID ,
3296 GENERAL_FUND_ACCOUNT_CCID ,
3297 ENTERED_AMOUNT ,
3298 ADJUSTMENT_LINE_ID,
3299 ADJUSTMENT_TYPE,
3300 EXPENSE_ACCOUNT_CCID,
3301 GAIN_LOSS_AMOUNT,
3302 ASSET_COST_ACCT,
3303 BONUS_DEPRN_EXPENSE_ACCT,
3304 BONUS_RESERVE_ACCT,
3305 CIP_COST_ACCT,
3306 DEPRN_RESERVE_ACCT,
3307 REVAL_RESERVE_ACCT,
3308 TRANSACTION_HEADER_ID )
3309 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
3310 adj.adjustment_line_id ,
3311 adj.distribution_id ,
3312 stg.distribution_type_code ,
3313 bc.set_of_books_id ,
3314 le.currency_code ,
3315 stg.book_type_code ,
3316 adj.code_combination_id ,
3317 adj.asset_id ,
3318 stg.cat_id ,
3319 stg.asset_type ,
3320 stg.ASSET_COST_ACCOUNT_CCID ,
3321 stg.ASSET_CLEARING_ACCOUNT_CCID ,
3322 stg.CIP_COST_ACCOUNT_CCID ,
3323 stg.CIP_CLEARING_ACCOUNT_CCID ,
3324 stg.RESERVE_ACCOUNT_CCID ,
3325 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
3326 stg.BONUS_RESERVE_ACCT_CCID ,
3327 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
3328 stg.REVAL_AMORT_ACCOUNT_CCID ,
3329 stg.REVAL_RESERVE_ACCOUNT_CCID ,
3330 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
3331 stg.ALT_COST_ACCOUNT_CCID ,
3332 stg.WRITE_OFF_ACCOUNT_CCID ,
3333 stg.DEPRN_EXPENSE_ACCT ,
3334 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
3335 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
3336 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
3337 stg.GENERAL_FUND_ACCOUNT_CCID ,
3338 decode(adj.adjustment_type,
3339 'RESERVE',
3340 decode(debit_credit_flag,
3341 'DR', adjustment_amount,
3342 -1 * adjustment_amount),
3343 'BONUS RESERVE',
3344 decode(debit_credit_flag,
3345 'DR', adjustment_amount,
3346 -1 * adjustment_amount),
3347 'REVAL RESERVE',
3348 decode(debit_credit_flag,
3349 'DR', adjustment_amount,
3350 -1 * adjustment_amount),
3351 'NBV RETIRED',
3352 decode(debit_credit_flag,
3353 'DR', adjustment_amount,
3354 -1 * adjustment_amount),
3355 'PROCEEDS CLR',
3356 decode(debit_credit_flag,
3357 'DR', adjustment_amount,
3358 -1 * adjustment_amount),
3359 'REMOVALCOST',
3360 decode(debit_credit_flag,
3361 'DR', adjustment_amount,
3362 -1 * adjustment_amount),
3363 'IMPAIR RESERVE',
3364 decode(debit_credit_flag,
3365 'DR', adjustment_amount,
3366 -1 * adjustment_amount),
3367 'CAPITAL ADJ',
3368 decode(debit_credit_flag,
3369 'DR', adjustment_amount,
3370 -1 * adjustment_amount),
3371 'GENERAL FUND',
3372 decode(debit_credit_flag,
3373 'CR', adjustment_amount,
3374 -1 * adjustment_amount),
3375 decode(debit_credit_flag,
3376 'CR', adjustment_amount,
3377 -1 * adjustment_amount)) ,
3378 adj.ADJUSTMENT_LINE_ID,
3379 adj.ADJUSTMENT_TYPE,
3380 dh.CODE_COMBINATION_ID,
3381 ret.GAIN_LOSS_AMOUNT,
3382 stg.ASSET_COST_ACCT,
3383 stg.BONUS_DEPRN_EXPENSE_ACCT,
3384 stg.BONUS_RESERVE_ACCT,
3385 stg.CIP_COST_ACCT,
3386 stg.DEPRN_RESERVE_ACCT,
3387 stg.REVAL_RESERVE_ACCT,
3388 stg.TRANSACTION_HEADER_ID
3389 from fa_xla_ext_lines_stg_gt stg,
3390 fa_mc_adjustments adj,
3391 fa_distribution_history dh,
3392 fa_locations loc,
3393 fa_lookups lu ,
3394 fa_mc_retirements ret ,
3395 fa_mc_book_controls bc ,
3396 gl_ledgers le
3397 WHERE adj.transaction_header_id = stg.transaction_header_id
3398 AND adj.book_type_code = stg.book_type_code
3399 AND adj.distribution_id = dh.distribution_id
3400 AND dh.location_id = loc.location_id
3401 -- AND dh.assigned_to = emp.employee_id(+)
3402 AND lu.lookup_type = 'JOURNAL ENTRIES'
3403 AND lu.lookup_code = adj.source_type_code || ' ' ||
3404 decode (adj.adjustment_type,
3405 'CIP COST', 'COST',
3406 adj.adjustment_type)
3407 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
3408 AND nvl(adj.track_member_flag, 'N') = 'N'
3409 AND adj.adjustment_amount <> 0
3410 AND stg.event_type_code in ('RETIREMENTS', 'CIP_RETIREMENTS')
3411 AND ret.transaction_header_id_in = stg.member_transaction_header_id
3412 AND bc.book_type_code = stg.book_type_code
3413 AND bc.set_of_books_id = le.ledger_id
3414 AND le.ledger_category_code = decode(l_secondary,
3415 0, 'ALC',
3416 'SECONDARY')
3417 AND ret.set_of_books_id = bc.set_of_books_id
3418 AND adj.set_of_books_id = bc.set_of_books_id ;
3419
3420 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3421 fnd_log.string(G_LEVEL_PROCEDURE,
3422 G_MODULE_NAME||l_procedure_name,
3423 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
3424 END IF;
3425
3426
3427
3428 end if;
3429
3430
3431 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3432 fnd_log.string(G_LEVEL_PROCEDURE,
3433 G_MODULE_NAME||l_procedure_name||'.end',
3434 'End of procedure');
3435 END IF;
3436
3437 EXCEPTION
3438 WHEN others THEN
3439 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
3440 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3441 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3442 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3443 END IF;
3444 raise;
3445
3446 end load_line_data_ret;
3447
3448
3449
3450 /*======================================================================+
3451 | |
3452 | Private Function |
3453 | load_line_data_res |
3454 | |
3455 +======================================================================*/
3456
3457 PROCEDURE load_line_data_res IS
3458
3459 l_procedure_name varchar2(80) := 'load_line_data_res';
3460 l_secondary number := 0;
3461
3462 BEGIN
3463
3464 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3465 fnd_log.string(G_LEVEL_PROCEDURE,
3466 G_MODULE_NAME||l_procedure_name||'.begin',
3467 'Beginning of procedure');
3468 END IF;
3469
3470 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
3471 l_secondary := 1;
3472 END IF;
3473
3474
3475 if (not fa_xla_extract_util_pkg.G_secondary_special) then
3476
3477
3478
3479 insert into fa_xla_ext_lines_b_gt (
3480 EVENT_ID ,
3481 LINE_NUMBER ,
3482 DISTRIBUTION_ID ,
3483 DISTRIBUTION_TYPE_CODE ,
3484 LEDGER_ID ,
3485 CURRENCY_CODE ,
3486 BOOK_TYPE_CODE ,
3487 GENERATED_CCID ,
3488 ASSET_ID ,
3489 CAT_ID ,
3490 ASSET_TYPE ,
3491 ASSET_COST_ACCOUNT_CCID ,
3492 ASSET_CLEARING_ACCOUNT_CCID ,
3493 CIP_COST_ACCOUNT_CCID ,
3494 CIP_CLEARING_ACCOUNT_CCID ,
3495 RESERVE_ACCOUNT_CCID ,
3496 DEPRN_EXPENSE_ACCOUNT_CCID ,
3497 BONUS_RESERVE_ACCT_CCID ,
3498 BONUS_EXPENSE_ACCOUNT_CCID ,
3499 REVAL_AMORT_ACCOUNT_CCID ,
3500 REVAL_RESERVE_ACCOUNT_CCID ,
3501 UNPLAN_EXPENSE_ACCOUNT_CCID ,
3502 ALT_COST_ACCOUNT_CCID ,
3503 WRITE_OFF_ACCOUNT_CCID ,
3504 DEPRN_EXPENSE_ACCT ,
3505 IMPAIR_EXPENSE_ACCOUNT_CCID ,
3506 IMPAIR_RESERVE_ACCOUNT_CCID ,
3507 CAPITAL_ADJ_ACCOUNT_CCID ,
3508 GENERAL_FUND_ACCOUNT_CCID ,
3509 ENTERED_AMOUNT ,
3510 ADJUSTMENT_LINE_ID,
3511 ADJUSTMENT_TYPE,
3512 EXPENSE_ACCOUNT_CCID,
3513 GAIN_LOSS_AMOUNT,
3514 ASSET_COST_ACCT,
3515 BONUS_DEPRN_EXPENSE_ACCT,
3516 BONUS_RESERVE_ACCT,
3517 CIP_COST_ACCT,
3518 DEPRN_RESERVE_ACCT,
3519 REVAL_RESERVE_ACCT,
3520 TRANSACTION_HEADER_ID )
3521 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
3522 adj.adjustment_line_id ,
3523 adj.distribution_id ,
3524 stg.distribution_type_code ,
3525 stg.ledger_id ,
3526 stg.currency_code ,
3527 stg.book_type_code ,
3528 adj.code_combination_id ,
3529 adj.asset_id ,
3530 stg.cat_id ,
3531 stg.asset_type ,
3532 stg.ASSET_COST_ACCOUNT_CCID ,
3533 stg.ASSET_CLEARING_ACCOUNT_CCID ,
3534 stg.CIP_COST_ACCOUNT_CCID ,
3535 stg.CIP_CLEARING_ACCOUNT_CCID ,
3536 stg.RESERVE_ACCOUNT_CCID ,
3537 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
3538 stg.BONUS_RESERVE_ACCT_CCID ,
3539 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
3540 stg.REVAL_AMORT_ACCOUNT_CCID ,
3541 stg.REVAL_RESERVE_ACCOUNT_CCID ,
3542 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
3543 stg.ALT_COST_ACCOUNT_CCID ,
3544 stg.WRITE_OFF_ACCOUNT_CCID ,
3545 stg.DEPRN_EXPENSE_ACCT ,
3546 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
3547 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
3548 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
3549 stg.GENERAL_FUND_ACCOUNT_CCID ,
3550 decode(adj.adjustment_type,
3551 'RESERVE',
3552 decode(debit_credit_flag,
3553 'DR', adjustment_amount,
3554 -1 * adjustment_amount),
3555 'BONUS RESERVE',
3556 decode(debit_credit_flag,
3557 'DR', adjustment_amount,
3558 -1 * adjustment_amount),
3559 'REVAL RESERVE',
3560 decode(debit_credit_flag,
3561 'DR', adjustment_amount,
3562 -1 * adjustment_amount),
3563 'NBV RETIRED',
3564 decode(debit_credit_flag,
3565 'DR', adjustment_amount,
3566 -1 * adjustment_amount),
3567 'PROCEEDS CLR',
3568 decode(debit_credit_flag,
3569 'DR', adjustment_amount,
3570 -1 * adjustment_amount),
3571 'REMOVALCOST',
3572 decode(debit_credit_flag,
3573 'DR', adjustment_amount,
3574 -1 * adjustment_amount),
3575 'IMPAIR RESERVE',
3576 decode(debit_credit_flag,
3577 'DR', adjustment_amount,
3578 -1 * adjustment_amount),
3579 'CAPITAL ADJ',
3580 decode(debit_credit_flag,
3581 'DR', adjustment_amount,
3582 -1 * adjustment_amount),
3583 'GENERAL FUND',
3584 decode(debit_credit_flag,
3585 'CR', adjustment_amount,
3586 -1 * adjustment_amount),
3587 decode(debit_credit_flag,
3588 'CR', adjustment_amount,
3589 -1 * adjustment_amount)) ,
3590 adj.ADJUSTMENT_LINE_ID,
3591 adj.ADJUSTMENT_TYPE,
3592 dh.CODE_COMBINATION_ID,
3593 ret.GAIN_LOSS_AMOUNT,
3594 stg.ASSET_COST_ACCT,
3595 stg.BONUS_DEPRN_EXPENSE_ACCT,
3596 stg.BONUS_RESERVE_ACCT,
3597 stg.CIP_COST_ACCT,
3598 stg.DEPRN_RESERVE_ACCT,
3599 stg.REVAL_RESERVE_ACCT,
3600 stg.TRANSACTION_HEADER_ID
3601 from fa_xla_ext_lines_stg_gt stg,
3602 fa_adjustments adj,
3603 fa_distribution_history dh,
3604 fa_locations loc,
3605 fa_lookups lu ,
3606 fa_retirements ret
3607 WHERE adj.transaction_header_id = stg.transaction_header_id
3608 AND adj.book_type_code = stg.book_type_code
3609 AND adj.distribution_id = dh.distribution_id
3610 AND dh.location_id = loc.location_id
3611 -- AND dh.assigned_to = emp.employee_id(+)
3612 AND lu.lookup_type = 'JOURNAL ENTRIES'
3613 AND lu.lookup_code = adj.source_type_code || ' ' ||
3614 decode (adj.adjustment_type,
3615 'CIP COST', 'COST',
3616 adj.adjustment_type)
3617 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
3618 AND nvl(adj.track_member_flag, 'N') = 'N'
3619 AND adj.adjustment_amount <> 0
3620 AND stg.event_type_code in ('REINSTATEMENTS','CIP_REINSTATEMENTS')
3621 AND ret.transaction_header_id_out = stg.member_transaction_header_id ;
3622
3623 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3624 fnd_log.string(G_LEVEL_PROCEDURE,
3625 G_MODULE_NAME||l_procedure_name,
3626 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
3627 END IF;
3628
3629
3630
3631 end if;
3632
3633 if (fa_xla_extract_util_pkg.G_alc_enabled or
3634 fa_xla_extract_util_pkg.G_secondary_special) then
3635
3636
3637
3638 insert into fa_xla_ext_lines_b_gt (
3639 EVENT_ID ,
3640 LINE_NUMBER ,
3641 DISTRIBUTION_ID ,
3642 DISTRIBUTION_TYPE_CODE ,
3643 LEDGER_ID ,
3644 CURRENCY_CODE ,
3645 BOOK_TYPE_CODE ,
3646 GENERATED_CCID ,
3647 ASSET_ID ,
3648 CAT_ID ,
3649 ASSET_TYPE ,
3650 ASSET_COST_ACCOUNT_CCID ,
3651 ASSET_CLEARING_ACCOUNT_CCID ,
3652 CIP_COST_ACCOUNT_CCID ,
3653 CIP_CLEARING_ACCOUNT_CCID ,
3654 RESERVE_ACCOUNT_CCID ,
3655 DEPRN_EXPENSE_ACCOUNT_CCID ,
3656 BONUS_RESERVE_ACCT_CCID ,
3657 BONUS_EXPENSE_ACCOUNT_CCID ,
3658 REVAL_AMORT_ACCOUNT_CCID ,
3659 REVAL_RESERVE_ACCOUNT_CCID ,
3660 UNPLAN_EXPENSE_ACCOUNT_CCID ,
3661 ALT_COST_ACCOUNT_CCID ,
3662 WRITE_OFF_ACCOUNT_CCID ,
3663 DEPRN_EXPENSE_ACCT ,
3664 IMPAIR_EXPENSE_ACCOUNT_CCID ,
3665 IMPAIR_RESERVE_ACCOUNT_CCID ,
3666 CAPITAL_ADJ_ACCOUNT_CCID ,
3667 GENERAL_FUND_ACCOUNT_CCID ,
3668 ENTERED_AMOUNT ,
3669 ADJUSTMENT_LINE_ID,
3670 ADJUSTMENT_TYPE,
3671 EXPENSE_ACCOUNT_CCID,
3672 GAIN_LOSS_AMOUNT,
3673 ASSET_COST_ACCT,
3674 BONUS_DEPRN_EXPENSE_ACCT,
3675 BONUS_RESERVE_ACCT,
3676 CIP_COST_ACCT,
3677 DEPRN_RESERVE_ACCT,
3678 REVAL_RESERVE_ACCT,
3679 TRANSACTION_HEADER_ID )
3680 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
3681 adj.adjustment_line_id ,
3682 adj.distribution_id ,
3683 stg.distribution_type_code ,
3684 bc.set_of_books_id ,
3685 le.currency_code ,
3686 stg.book_type_code ,
3687 adj.code_combination_id ,
3688 adj.asset_id ,
3689 stg.cat_id ,
3690 stg.asset_type ,
3691 stg.ASSET_COST_ACCOUNT_CCID ,
3692 stg.ASSET_CLEARING_ACCOUNT_CCID ,
3693 stg.CIP_COST_ACCOUNT_CCID ,
3694 stg.CIP_CLEARING_ACCOUNT_CCID ,
3695 stg.RESERVE_ACCOUNT_CCID ,
3696 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
3697 stg.BONUS_RESERVE_ACCT_CCID ,
3698 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
3699 stg.REVAL_AMORT_ACCOUNT_CCID ,
3700 stg.REVAL_RESERVE_ACCOUNT_CCID ,
3701 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
3702 stg.ALT_COST_ACCOUNT_CCID ,
3703 stg.WRITE_OFF_ACCOUNT_CCID ,
3704 stg.DEPRN_EXPENSE_ACCT ,
3705 stg.IMPAIR_EXPENSE_ACCOUNT_CCID ,
3706 stg.IMPAIR_RESERVE_ACCOUNT_CCID ,
3707 stg.CAPITAL_ADJ_ACCOUNT_CCID ,
3708 stg.GENERAL_FUND_ACCOUNT_CCID ,
3709 decode(adj.adjustment_type,
3710 'RESERVE',
3711 decode(debit_credit_flag,
3712 'DR', adjustment_amount,
3713 -1 * adjustment_amount),
3714 'BONUS RESERVE',
3715 decode(debit_credit_flag,
3716 'DR', adjustment_amount,
3717 -1 * adjustment_amount),
3718 'REVAL RESERVE',
3719 decode(debit_credit_flag,
3720 'DR', adjustment_amount,
3721 -1 * adjustment_amount),
3722 'NBV RETIRED',
3723 decode(debit_credit_flag,
3724 'DR', adjustment_amount,
3725 -1 * adjustment_amount),
3726 'PROCEEDS CLR',
3727 decode(debit_credit_flag,
3728 'DR', adjustment_amount,
3729 -1 * adjustment_amount),
3730 'REMOVALCOST',
3731 decode(debit_credit_flag,
3732 'DR', adjustment_amount,
3733 -1 * adjustment_amount),
3734 'IMPAIR RESERVE',
3735 decode(debit_credit_flag,
3736 'DR', adjustment_amount,
3737 -1 * adjustment_amount),
3738 'CAPITAL ADJ',
3739 decode(debit_credit_flag,
3740 'DR', adjustment_amount,
3741 -1 * adjustment_amount),
3742 'GENERAL FUND',
3743 decode(debit_credit_flag,
3744 'CR', adjustment_amount,
3745 -1 * adjustment_amount),
3746 decode(debit_credit_flag,
3747 'CR', adjustment_amount,
3748 -1 * adjustment_amount)) ,
3749 adj.ADJUSTMENT_LINE_ID,
3750 adj.ADJUSTMENT_TYPE,
3751 dh.CODE_COMBINATION_ID,
3752 ret.GAIN_LOSS_AMOUNT,
3753 stg.ASSET_COST_ACCT,
3754 stg.BONUS_DEPRN_EXPENSE_ACCT,
3755 stg.BONUS_RESERVE_ACCT,
3756 stg.CIP_COST_ACCT,
3757 stg.DEPRN_RESERVE_ACCT,
3758 stg.REVAL_RESERVE_ACCT,
3759 stg.TRANSACTION_HEADER_ID
3760 from fa_xla_ext_lines_stg_gt stg,
3761 fa_mc_adjustments adj,
3762 fa_distribution_history dh,
3763 fa_locations loc,
3764 fa_lookups lu ,
3765 fa_mc_retirements ret ,
3766 fa_mc_book_controls bc ,
3767 gl_ledgers le
3768 WHERE adj.transaction_header_id = stg.transaction_header_id
3769 AND adj.book_type_code = stg.book_type_code
3770 AND adj.distribution_id = dh.distribution_id
3771 AND dh.location_id = loc.location_id
3772 -- AND dh.assigned_to = emp.employee_id(+)
3773 AND lu.lookup_type = 'JOURNAL ENTRIES'
3774 AND lu.lookup_code = adj.source_type_code || ' ' ||
3775 decode (adj.adjustment_type,
3776 'CIP COST', 'COST',
3777 adj.adjustment_type)
3778 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
3779 AND nvl(adj.track_member_flag, 'N') = 'N'
3780 AND adj.adjustment_amount <> 0
3781 AND stg.event_type_code in ('REINSTATEMENTS','CIP_REINSTATEMENTS')
3782 AND ret.transaction_header_id_out = stg.member_transaction_header_id
3783 AND bc.book_type_code = stg.book_type_code
3784 AND bc.set_of_books_id = le.ledger_id
3785 AND le.ledger_category_code = decode(l_secondary,
3786 0, 'ALC',
3787 'SECONDARY')
3788 AND ret.set_of_books_id = bc.set_of_books_id
3789 AND adj.set_of_books_id = bc.set_of_books_id ;
3790
3791 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3792 fnd_log.string(G_LEVEL_PROCEDURE,
3793 G_MODULE_NAME||l_procedure_name,
3794 'Rows inserted into alc lines: ' || to_char(SQL%ROWCOUNT));
3795 END IF;
3796
3797
3798
3799 end if;
3800
3801
3802 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3803 fnd_log.string(G_LEVEL_PROCEDURE,
3804 G_MODULE_NAME||l_procedure_name||'.end',
3805 'End of procedure');
3806 END IF;
3807
3808 EXCEPTION
3809 WHEN others THEN
3810 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
3811 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3812 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3813 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3814 END IF;
3815 raise;
3816
3817 end load_line_data_res;
3818
3819
3820
3821 /*======================================================================+
3822 | |
3823 | Private Function |
3824 | load_mls_data |
3825 | |
3826 +======================================================================*/
3827
3828 PROCEDURE load_mls_data IS
3829
3830 l_procedure_name varchar2(80) := 'load_mls_data';
3831
3832 BEGIN
3833
3834 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3835 fnd_log.string(G_LEVEL_PROCEDURE,
3836 G_MODULE_NAME||l_procedure_name||'.begin',
3837 'Beginning of procedure');
3838 END IF;
3839
3840 return;
3841
3842 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3843 fnd_log.string(G_LEVEL_PROCEDURE,
3844 G_MODULE_NAME||l_procedure_name,
3845 'Rows inserted into mls: ' || to_char(SQL%ROWCOUNT));
3846 END IF;
3847
3848
3849
3850 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3851 fnd_log.string(G_LEVEL_PROCEDURE,
3852 G_MODULE_NAME||l_procedure_name||'.end',
3853 'End of procedure');
3854 END IF;
3855
3856 EXCEPTION
3857 WHEN others THEN
3858 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
3859 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3860 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3861 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3862 END IF;
3863 raise;
3864
3865 end load_mls_data;
3866
3867
3868
3869 /*======================================================================+
3870 | |
3871 | Private Function |
3872 | Load_Generated_Ccids |
3873 | |
3874 +======================================================================*/
3875
3876 ----------------------------------------------------
3877 --
3878 -- Account Generator Hook
3879 --
3880 ----------------------------------------------------
3881 PROCEDURE Load_Generated_Ccids
3882 (p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
3883
3884 l_mesg_count number := 0;
3885 l_mesg_len number;
3886 l_mesg varchar2(4000);
3887
3888 l_procedure_name varchar2(80) := 'fa_xla_extract_def_pkg.load_generated_ccids'; -- BMR make this dynamic on type
3889
3890 type char_tab_type is table of varchar2(64) index by binary_integer;
3891 type num_tab_type is table of number index by binary_integer;
3892
3893
3894
3895
3896
3897 -- bug 5563601: Increased length of variable account_type to 50
3898 type adj_rec_type is record
3899 (rowid VARCHAR2(64),
3900 book_type_code VARCHAR2(30),
3901 distribution_id NUMBER(15),
3902 distribution_ccid NUMBER(15),
3903 entered_amount NUMBER,
3904 account_type VARCHAR2(50),
3905 generated_ccid NUMBER(15),
3906 account_ccid NUMBER(15),
3907 account_segment VARCHAR2(25),
3908 offset_account_type VARCHAR2(25),
3909 generated_offset_ccid NUMBER(15),
3910 offset_account_ccid NUMBER(15),
3911 offset_account_segment VARCHAR2(25),
3912 counter_account_type VARCHAR2(50), -- Bug 6962827:
3913 counter_generated_ccid NUMBER(15), -- Bug 6962827:
3914 counter_account_ccid NUMBER(15), -- Bug 6962827:
3915 counter_account_segment VARCHAR2(25), -- Bug 6962827:
3916 counter_generated_offset_ccid NUMBER(15), -- Bug 6962827:
3917 counter_offset_account_ccid NUMBER(15), -- Bug 6962827:
3918 counter_offset_account_segment VARCHAR2(25) -- Bug 6962827:
3919 );
3920
3921 type adj_tbl_type is table of adj_rec_type index by binary_integer;
3922
3923 l_adj_tbl adj_tbl_type;
3924
3925 l_generated_ccid num_tab_type;
3926 l_generated_offset_ccid num_tab_type;
3927 l_rowid char_tab_type;
3928 l_counter_generated_ccid num_tab_type; -- Bug 6962827:
3929 l_ctr_generated_off_ccid num_tab_type; -- Bug 6962827:
3930
3931 error_found exception;
3932
3933 l_last_book varchar2(30) := ' ';
3934
3935 cursor c_trx is
3936 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
3937 xl.rowid,
3938 xb.book_type_code,
3939 xl.distribution_id,
3940 xl.expense_account_ccid,
3941 xl.entered_amount,
3942 decode
3943 (adjustment_type,
3944 'COST', 'ASSET_COST_ACCT',
3945 'CIP COST', 'CIP_COST_ACCT',
3946 'COST CLEARING', decode(xl.asset_type,
3947 'CIP', 'CIP_CLEARING_ACCT',
3948 'ASSET_CLEARING_ACCT'),
3949 'EXPENSE', 'DEPRN_EXPENSE_ACCT',
3950 'RESERVE', 'DEPRN_RESERVE_ACCT',
3951 'BONUS EXPENSE', 'BONUS_DEPRN_EXPENSE_ACCT',
3952 'BONUS RESERVE', 'BONUS_DEPRN_RESERVE_ACCT',
3953 'REVAL RESERVE', 'REVAL_RESERVE_ACCT',
3954 'CAPITAL ADJ', 'CAPITAL_ADJ_ACCT',
3955 'GENERAL FUND', 'GENERAL_FUND_ACCT',
3956 'IMPAIR EXPENSE', 'IMPAIR_EXPENSE_ACCT',
3957 'IMPAIR RESERVE', 'IMPAIR_RESERVE_ACCT',
3958 'LINK IMPAIR EXP', 'IMPAIR_EXPENSE_ACCT',
3959 'IMPAIR OFF EXP', 'DEPRN_EXPENSE_ACCT',
3960 'DEPRN ADJUST', 'DEPRN_ADJUSTMENT_ACCT',
3961 'REVAL LOSS', 'REVAL_LOSS_ACCT',
3962 'REVAL OFF EXP', 'DEPRN_EXPENSE_ACCT',
3963 'PROCEEDS CLR', 'PROCEEDS_OF_SALE_CLEARING_ACCT',
3964 'REMOVALCOST CLR', 'COST_OF_REMOVAL_CLEARING_ACCT',
3965 'REMOVALCOST', decode(sign(gain_loss_amount),
3966 -1, 'COST_OF_REMOVAL_LOSS_ACCT',
3967 'COST_OF_REMOVAL_GAIN_ACCT'),
3968 'PROCEEDS', decode(sign(gain_loss_amount),
3969 -1, 'PROCEEDS_OF_SALE_LOSS_ACCT',
3970 'PROCEEDS_OF_SALE_GAIN_ACCT'),
3971 'REVAL RSV RET', decode(sign(gain_loss_amount),
3972 -1, 'REVAL_RSV_RETIRED_LOSS_ACCT',
3973 'REVAL_RSV_RETIRED_GAIN_ACCT'),
3974 'NBV RETIRED', decode(asset_type,
3975 'GROUP', decode(sign(gain_loss_amount),
3976 -1, 'NBV_RETIRED_LOSS_ACCT',
3977 'NBV_RETIRED_GAIN_ACCT'),
3978 decode(sign(gain_loss_amount),
3979 -1, 'NBV_RETIRED_LOSS_ACCT',
3980 'NBV_RETIRED_GAIN_ACCT')),
3981 NULL),
3982 decode(xl.adjustment_type,
3983 'COST', nvl(xl.generated_ccid, da.ASSET_COST_ACCOUNT_CCID),
3984 'CIP COST', nvl(xl.generated_ccid, da.CIP_COST_ACCOUNT_CCID),
3985 'COST CLEARING', decode(xl.asset_type,
3986 'CIP', nvl(xl.generated_ccid, da.CIP_CLEARING_ACCOUNT_CCID),
3987 nvl(xl.generated_ccid, da.ASSET_CLEARING_ACCOUNT_CCID)),
3988 'EXPENSE', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
3989 'RESERVE', nvl(xl.generated_ccid, da.DEPRN_RESERVE_ACCOUNT_CCID),
3990 'BONUS EXPENSE', nvl(xl.generated_ccid, da.BONUS_EXP_ACCOUNT_CCID),
3991 'BONUS RESERVE', nvl(xl.generated_ccid, da.BONUS_RSV_ACCOUNT_CCID),
3992 'REVAL RESERVE', nvl(xl.generated_ccid, da.REVAL_RSV_ACCOUNT_CCID),
3993 'CAPITAL ADJ', nvl(xl.generated_ccid, da.CAPITAL_ADJ_ACCOUNT_CCID),
3994 'GENERAL FUND', nvl(xl.generated_ccid, da.GENERAL_FUND_ACCOUNT_CCID),
3995 'IMPAIR EXPENSE', nvl(xl.generated_ccid, da.IMPAIR_EXPENSE_ACCOUNT_CCID),
3996 'IMPAIR RESERVE', nvl(xl.generated_ccid, da.IMPAIR_RESERVE_ACCOUNT_CCID),
3997 'LINK IMPAIR EXP', nvl(xl.generated_ccid, da.IMPAIR_EXPENSE_ACCOUNT_CCID),
3998 'IMPAIR OFF EXP', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
3999 'REVAL LOSS', nvl(xl.generated_ccid, da.REVAL_LOSS_ACCOUNT_CCID),
4000 'REVAL OFF EXP', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
4001 'DEPRN ADJUST', nvl(xl.generated_ccid, da.DEPRN_ADJ_ACCOUNT_CCID),
4002 'PROCEEDS CLR', nvl(xl.generated_ccid, da.PROCEEDS_SALE_CLEARING_CCID),
4003 'REMOVALCOST CLR', nvl(xl.generated_ccid, da.COST_REMOVAL_CLEARING_CCID),
4004 'PROCEEDS', decode(sign(xl.gain_loss_amount),
4005 -1, nvl(xl.generated_ccid, da.PROCEEDS_SALE_LOSS_CCID),
4006 nvl(xl.generated_ccid, da.PROCEEDS_SALE_GAIN_CCID)),
4007 'REMOVALCOST', decode(sign(xl.gain_loss_amount),
4008 -1, nvl(xl.generated_ccid, da.COST_REMOVAL_LOSS_CCID),
4009 nvl(xl.generated_ccid, da.COST_REMOVAL_GAIN_CCID)),
4010 'REVAL RSV RET', decode(sign(xl.gain_loss_amount),
4011 -1, nvl(xl.generated_ccid, da.REVAL_RSV_LOSS_ACCOUNT_CCID),
4012 nvl(xl.generated_ccid, da.REVAL_RSV_GAIN_ACCOUNT_CCID)),
4013 'NBV RETIRED', decode(sign(xl.gain_loss_amount),
4014 -1, nvl(xl.generated_ccid, da.NBV_RETIRED_LOSS_CCID),
4015 nvl(xl.generated_ccid, da.NBV_RETIRED_GAIN_CCID)),
4016 NULL),
4017 decode(xl.adjustment_type,
4018 'COST', xl.ASSET_COST_ACCOUNT_CCID,
4019 'CIP COST', xl.CIP_COST_ACCOUNT_CCID,
4020 'COST CLEARING', decode(xl.asset_type,
4021 'CIP', xl.CIP_CLEARING_ACCOUNT_CCID,
4022 xl.ASSET_CLEARING_ACCOUNT_CCID),
4023 'RESERVE', xl.RESERVE_ACCOUNT_CCID,
4024 'BONUS RESERVE', xl.BONUS_RESERVE_ACCT_CCID,
4025 'REVAL RESERVE', xl.REVAL_RESERVE_ACCOUNT_CCID,
4026 'CAPITAL ADJ', xl.CAPITAL_ADJ_ACCOUNT_CCID,
4027 'GENERAL FUND', xl.GENERAL_FUND_ACCOUNT_CCID,
4028 'IMPAIR EXPENSE', xl.IMPAIR_EXPENSE_ACCOUNT_CCID,
4029 'IMPAIR RESERVE', xl.IMPAIR_RESERVE_ACCOUNT_CCID,
4030 'LINK IMPAIR EXP', xl.IMPAIR_EXPENSE_ACCOUNT_CCID,
4031 'IMPAIR OFF EXP', xl.DEPRN_EXPENSE_ACCOUNT_CCID,
4032 'REVAL LOSS', xl.REVAL_LOSS_ACCOUNT_CCID,
4033 'REVAL OFF EXP', xl.DEPRN_EXPENSE_ACCOUNT_CCID,
4034 0),
4035 decode(xl.adjustment_type,
4036 'COST', xl.ASSET_COST_ACCT,
4037 'CIP COST', xl.CIP_COST_ACCT,
4038 'COST CLEARING', decode(xl.asset_type,
4039 'CIP', xl.CIP_CLEARING_ACCT,
4040 xl.ASSET_CLEARING_ACCT),
4041 'EXPENSE', xl.DEPRN_EXPENSE_ACCT,
4042 'RESERVE', xl.DEPRN_RESERVE_ACCT,
4043 'BONUS EXPENSE', xl.BONUS_DEPRN_EXPENSE_ACCT,
4044 'BONUS RESERVE', xl.BONUS_RESERVE_ACCT,
4045 'REVAL RESERVE', xl.REVAL_RESERVE_ACCT,
4046 'CAPITAL ADJ', xl.CAPITAL_ADJ_ACCT,
4047 'GENERAL FUND', xl.GENERAL_FUND_ACCT,
4048 'IMPAIR EXPENSE', xl.IMPAIR_EXPENSE_ACCT,
4049 'IMPAIR RESERVE', xl.IMPAIR_RESERVE_ACCT,
4050 'LINK IMPAIR EXP', xl.IMPAIR_EXPENSE_ACCT,
4051 'IMPAIR OFF EXP', xl.DEPRN_EXPENSE_ACCT,
4052 'REVAL LOSS', xl.REVAL_LOSS_ACCT,
4053 'REVAL OFF EXP', xl.DEPRN_EXPENSE_ACCT,
4054 'PROCEEDS CLR', xb.PROCEEDS_OF_SALE_CLEARING_ACCT,
4055 'REMOVALCOST CLR', xb.COST_OF_REMOVAL_CLEARING_ACCT,
4056 'NBV RETIRED', decode(sign(xl.gain_loss_amount),
4057 -1, xb.NBV_RETIRED_LOSS_ACCT,
4058 xb.NBV_RETIRED_GAIN_ACCT),
4059 'PROCEEDS', decode(sign(xl.gain_loss_amount),
4060 -1, xb.PROCEEDS_OF_SALE_LOSS_ACCT,
4061 xb.PROCEEDS_OF_SALE_GAIN_ACCT),
4062 'REMOVALCOST', decode(sign(xl.gain_loss_amount),
4063 -1, xb.COST_OF_REMOVAL_LOSS_ACCT,
4064 xb.COST_OF_REMOVAL_GAIN_ACCT),
4065 'REVAL RSV RET', decode(sign(xl.gain_loss_amount),
4066 -1, xb.REVAL_RSV_RETIRED_LOSS_ACCT,
4067 xb.REVAL_RSV_RETIRED_GAIN_ACCT),
4068 NULL),
4069 decode(xl.adjustment_type,
4070 'EXPENSE', 'DEPRN_RESERVE_ACCT',
4071 'BONUS EXPENSE', 'BONUS_DEPRN_RESERVE_ACCT',
4072 NULL),
4073 decode(xl.adjustment_type,
4074 'EXPENSE', da.DEPRN_RESERVE_ACCOUNT_CCID,
4075 'BONUS EXPENSE', da.BONUS_RSV_ACCOUNT_CCID,
4076 NULL),
4077 decode(xl.adjustment_type,
4078 'EXPENSE', xl.RESERVE_ACCOUNT_CCID,
4079 'BONUS EXPENSE', xl.BONUS_RESERVE_ACCT_CCID,
4080 NULL),
4081 decode(xl.adjustment_type,
4082 'EXPENSE', xl.DEPRN_RESERVE_ACCT,
4083 'BONUS EXPENSE', xl.BONUS_RESERVE_ACCT,
4084 NULL),
4085 -- Bug 6962827: counter_account_type
4086 decode(adjustment_type,
4087 'BONUS EXPENSE', 'DEPRN_EXPENSE_ACCT',
4088 'BONUS RESERVE', 'DEPRN_RESERVE_ACCT',
4089 NULL),
4090 -- Bug 6962827: counter_generated_ccid
4091 decode(xl.adjustment_type,
4092 'BONUS EXPENSE', da.DEPRN_EXPENSE_ACCOUNT_CCID,
4093 'BONUS RESERVE', da.DEPRN_RESERVE_ACCOUNT_CCID,
4094 NULL),
4095 -- Bug 6962827 : counter_account_ccid
4096 decode(xl.adjustment_type,
4097 'BONUS RESERVE', xl.RESERVE_ACCOUNT_CCID,
4098 0),
4099 -- Bug 6962827 : counter_account_segment
4100 decode(xl.adjustment_type,
4101 'BONUS EXPENSE', xl.DEPRN_EXPENSE_ACCT,
4102 'BONUS RESERVE', xl.DEPRN_RESERVE_ACCT,
4103 NULL),
4104 -- Bug 6962827 : counter_generated_offset_ccid
4105 decode(xl.adjustment_type,
4106 'BONUS EXPENSE', da.DEPRN_RESERVE_ACCOUNT_CCID,
4107 NULL),
4108 -- Bug 6962827 : counter_offset_account_ccid
4109 decode(xl.adjustment_type,
4110 'BONUS EXPENSE', xl.RESERVE_ACCOUNT_CCID,
4111 NULL),
4112 -- Bug 6962827 : counter_offset_account_segment
4113 decode(xl.adjustment_type,
4114 'BONUS EXPENSE', xl.DEPRN_RESERVE_ACCT,
4115 NULL)
4116 from xla_events_gt xg,
4117 fa_xla_ext_headers_b_gt xb,
4118 fa_xla_ext_lines_b_gt xl,
4119 fa_distribution_accounts da
4120 where xg.event_class_code not in ('DEPRECIATION', 'DEFERRED')
4121 and xb.event_id = xg.event_id
4122 and xl.event_id = xg.event_id
4123 and xl.distribution_id = da.distribution_id(+)
4124 and xl.book_type_code = da.book_type_code(+);
4125
4126
4127 BEGIN
4128
4129 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4130 fnd_log.string(G_LEVEL_PROCEDURE,
4131 G_MODULE_NAME||l_procedure_name||'.begin',
4132 'Beginning of procedure');
4133 END IF;
4134
4135
4136 open c_trx;
4137 fetch c_trx
4138 bulk collect into l_adj_tbl;
4139 close c_trx;
4140
4141 for i in 1..l_adj_tbl.count loop
4142
4143 if (l_last_book <> l_adj_tbl(i).book_type_code or
4144 i = 1) then
4145 if not (fa_cache_pkg.fazcbc
4146 (X_BOOK => l_adj_tbl(1).book_type_code,
4147 P_LOG_LEVEL_REC => p_log_level_rec)) then
4148 null;
4149 end if;
4150 l_last_book := l_adj_tbl(i).book_type_code;
4151 end if;
4152
4153 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
4154
4155 if (l_adj_tbl(i).generated_ccid is null and
4156 l_adj_tbl(i).entered_amount <> 0) then
4157
4158 if (not FA_GCCID_PKG.fafbgcc
4159 (X_book_type_code => l_adj_tbl(i).book_type_code,
4160 X_fn_trx_code => l_adj_tbl(i).account_type,
4161 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
4162 X_acct_segval => l_adj_tbl(i).account_segment,
4163 X_account_ccid => l_adj_tbl(i).account_ccid,
4164 X_distribution_id => l_adj_tbl(i).distribution_id,
4165 X_rtn_ccid => l_adj_tbl(i).generated_ccid,
4166 P_LOG_LEVEL_REC => p_log_level_rec)) then
4167 FA_SRVR_MSG.ADD_MESSAGE
4168 (NAME => 'FA_GET_ACCOUNT_CCID',
4169 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
4170 P_LOG_LEVEL_REC => p_log_level_rec);
4171 l_adj_tbl(i).generated_ccid := -1;
4172 end if;
4173 end if;
4174
4175 if (l_adj_tbl(i).account_type in
4176 ('DEPRN_EXPENSE_ACCT', 'BONUS_DEPRN_EXPENSE_ACCT') and
4177 l_adj_tbl(i).generated_offset_ccid is null and
4178 l_adj_tbl(i).entered_amount <> 0) then
4179
4180 if (not FA_GCCID_PKG.fafbgcc
4181 (X_book_type_code => l_adj_tbl(i).book_type_code,
4182 X_fn_trx_code => l_adj_tbl(i).offset_account_type,
4183 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
4184 X_acct_segval => l_adj_tbl(i).offset_account_segment,
4185 X_account_ccid => l_adj_tbl(i).offset_account_ccid,
4186 X_distribution_id => l_adj_tbl(i).distribution_id,
4187 X_rtn_ccid => l_adj_tbl(i).generated_offset_ccid,
4188 P_LOG_LEVEL_REC => p_log_level_rec)) then
4189 FA_SRVR_MSG.ADD_MESSAGE
4190 (NAME => 'FA_GET_ACCOUNT_CCID',
4191 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
4192 P_LOG_LEVEL_REC => p_log_level_rec);
4193 l_adj_tbl(i).generated_offset_ccid := -1;
4194 end if;
4195 end if;
4196
4197 -- Bug 6962827 start
4198 -- Populate counter_generated_ccid with the Expense acct for
4199 -- Bonus expense and with Reserve acct for Bonus Reserve lines.
4200 if (l_adj_tbl(i).account_type in ('BONUS_DEPRN_EXPENSE_ACCT','BONUS_DEPRN_RESERVE_ACCT') and
4201 l_adj_tbl(i).counter_generated_ccid is null and
4202 l_adj_tbl(i).entered_amount <> 0) then
4203
4204 if (not FA_GCCID_PKG.fafbgcc
4205 (X_book_type_code => l_adj_tbl(i).book_type_code,
4206 X_fn_trx_code => l_adj_tbl(i).counter_account_type,
4207 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
4208 X_acct_segval => l_adj_tbl(i).counter_account_segment,
4209 X_account_ccid => l_adj_tbl(i).counter_account_ccid,
4210 X_distribution_id => l_adj_tbl(i).distribution_id,
4211 X_rtn_ccid => l_adj_tbl(i).counter_generated_ccid,
4212 P_LOG_LEVEL_REC => p_log_level_rec)) then
4213 FA_SRVR_MSG.ADD_MESSAGE
4214 (NAME => 'FA_GET_ACCOUNT_CCID',
4215 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
4216 P_LOG_LEVEL_REC => p_log_level_rec);
4217 l_adj_tbl(i).counter_generated_ccid := -1;
4218 end if;
4219
4220 end if;
4221
4222 -- Populate counter_generated_offset_ccid with the Reserve acct
4223 -- for Bonus expense lines.
4224 if (l_adj_tbl(i).account_type = 'BONUS_DEPRN_EXPENSE_ACCT' and
4225 l_adj_tbl(i).counter_generated_offset_ccid is null and
4226 l_adj_tbl(i).entered_amount <> 0) then
4227
4228 if (not FA_GCCID_PKG.fafbgcc
4229 (X_book_type_code => l_adj_tbl(i).book_type_code,
4230 X_fn_trx_code => 'DEPRN_RESERVE_ACCT',
4231 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
4232 X_acct_segval => l_adj_tbl(i).counter_offset_account_segment,
4233 X_account_ccid => l_adj_tbl(i).counter_offset_account_ccid,
4234 X_distribution_id => l_adj_tbl(i).distribution_id,
4235 X_rtn_ccid => l_adj_tbl(i).counter_generated_offset_ccid,
4236 P_LOG_LEVEL_REC => p_log_level_rec)) then
4237 FA_SRVR_MSG.ADD_MESSAGE
4238 (NAME => 'FA_GET_ACCOUNT_CCID',
4239 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
4240 P_LOG_LEVEL_REC => p_log_level_rec);
4241 l_adj_tbl(i).counter_generated_offset_ccid := -1;
4242 end if;
4243
4244 end if;
4245 -- Bug 6962827 end
4246
4247 end loop;
4248
4249 for i in 1.. l_adj_tbl.count loop
4250
4251 l_generated_ccid(i) := l_adj_tbl(i).generated_ccid;
4252 l_generated_offset_ccid(i) := l_adj_tbl(i).generated_offset_ccid;
4253 l_rowid(i) := l_adj_tbl(i).rowid;
4254 -- Bug 6962827
4255 l_counter_generated_ccid(i) := l_adj_tbl(i).counter_generated_ccid;
4256 l_ctr_generated_off_ccid(i) := l_adj_tbl(i).counter_generated_offset_ccid;
4257 end loop;
4258
4259 forall i in 1..l_adj_tbl.count
4260 update fa_xla_ext_lines_b_gt
4261 set generated_ccid = l_generated_ccid(i),
4262 generated_offset_ccid = l_generated_offset_ccid(i),
4263 counter_generated_ccid = l_counter_generated_ccid(i), -- Bug 6962827
4264 counter_generated_offset_ccid = l_ctr_generated_off_ccid(i) -- Bug 6962827
4265 where rowid = l_rowid(i);
4266
4267
4268 --
4269
4270 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4271 fnd_log.string(G_LEVEL_PROCEDURE,
4272 G_MODULE_NAME||l_procedure_name||'.end',
4273 'End of procedure');
4274 END IF;
4275
4276 EXCEPTION
4277 WHEN others THEN
4278 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
4279 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
4280 fnd_message.set_token('ORACLE_ERR',SQLERRM);
4281 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
4282 END IF;
4283 raise;
4284
4285 END load_generated_ccids;
4286
4287
4288
4289 /*======================================================================+
4290 | |
4291 | Private Function |
4292 | Lock_Data |
4293 | |
4294 +======================================================================*/
4295
4296 --------------------------------------------------
4297 -- Locking Routine --
4298 --------------------------------------------------
4299
4300 PROCEDURE Lock_Data IS
4301
4302 TYPE number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
4303 l_lock number_tbl_type;
4304 l_procedure_name varchar2(80) := 'lock_data';
4305
4306 BEGIN
4307
4308 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4309 fnd_log.string(G_LEVEL_PROCEDURE,
4310 G_MODULE_NAME||l_procedure_name||'.begin',
4311 'Beginning of procedure');
4312 END IF;
4313
4314
4315 --
4316 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4317 fnd_log.string(G_LEVEL_PROCEDURE,
4318 G_MODULE_NAME||l_procedure_name||'.end',
4319 'End of procedure');
4320 END IF;
4321
4322 EXCEPTION
4323 WHEN others THEN
4324 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
4325 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
4326 fnd_message.set_token('ORACLE_ERR',SQLERRM);
4327 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
4328 END IF;
4329 raise;
4330
4331
4332 END Lock_Data;
4333
4334
4335
4336 /*======================================================================+
4337 | |
4338 | Public Function |
4339 | Lock_Data |
4340 | |
4341 +======================================================================*/
4342
4343 --------------------------------------------------
4344 -- Main Load Routine --
4345 --------------------------------------------------
4346 PROCEDURE load_data IS
4347
4348 l_log_level_rec FA_API_TYPES.log_level_rec_type;
4349 l_use_fafbgcc varchar2(25);
4350 l_procedure_name varchar2(80) := 'load_data'; -- BMR make this dynamic on type
4351 error_found EXCEPTION;
4352
4353 BEGIN
4354
4355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4356 fnd_log.string(G_LEVEL_PROCEDURE,
4357 G_MODULE_NAME||l_procedure_name||'.begin',
4358 'Beginning of procedure');
4359 END IF;
4360
4361
4362
4363 Lock_Data;
4364 if (fa_xla_extract_util_pkg.G_trx_exists) then
4365 load_header_data_stg1;
4366 Load_line_data_stg1;
4367 end if;
4368
4369 if (fa_xla_extract_util_pkg.G_inter_trx_exists) then
4370 load_header_data_stg2;
4371 Load_line_data_stg2;
4372 end if;
4373
4374 if (fa_xla_extract_util_pkg.G_fin_trx_exists) then
4375 Load_line_data_fin1;
4376 end if;
4377
4378 if (fa_xla_extract_util_pkg.G_inter_trx_exists) then
4379 Load_line_data_fin2;
4380 end if;
4381
4382 if (fa_xla_extract_util_pkg.G_xfr_trx_exists) then
4383 Load_line_data_xfr;
4384 end if;
4385
4386 if (fa_xla_extract_util_pkg.G_dist_trx_exists) then
4387 Load_line_data_dist;
4388 end if;
4389
4390 if (fa_xla_extract_util_pkg.G_ret_trx_exists) then
4391 Load_line_data_ret;
4392 end if;
4393
4394 if (fa_xla_extract_util_pkg.G_res_trx_exists) then
4395 Load_line_data_res;
4396 end if;
4397
4398 Load_mls_data;
4399
4400
4401
4402
4403 fnd_profile.get ('FA_WF_GENERATE_CCIDS', l_use_fafbgcc);
4404 if (nvl(l_use_fafbgcc, 'N') = 'Y') then
4405 if (NOT fa_util_pub.get_log_level_rec (
4406 x_log_level_rec => l_log_level_rec)) then raise error_found;
4407 end if;
4408
4409 Load_Generated_Ccids
4410 (p_log_level_rec => l_log_level_rec);
4411 end if;
4412
4413
4414
4415
4416
4417 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4418 fnd_log.string(G_LEVEL_PROCEDURE,
4419 G_MODULE_NAME||l_procedure_name||'.end',
4420 'End of procedure');
4421 END IF;
4422
4423 EXCEPTION
4424 WHEN error_found THEN
4425 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
4426 FND_LOG.string (G_LEVEL_ERROR,
4427 G_MODULE_NAME||l_procedure_name,
4428 'ended in error');
4429 END IF;
4430 raise;
4431
4432 WHEN others THEN
4433 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
4434 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
4435 fnd_message.set_token('ORACLE_ERR',SQLERRM);
4436 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
4437 END IF;
4438 raise;
4439
4440 END load_data;
4441
4442
4443
4444 END FA_XLA_EXTRACT_TRX_PKG;
4445