[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 30-03-2009 at 05:03:21 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 insert into fa_xla_ext_headers_b_gt (
63 event_id ,
64 DEFAULT_CCID ,
65 BOOK_TYPE_CODE ,
66 PERIOD_NAME ,
67 PERIOD_CLOSE_DATE ,
68 PERIOD_COUNTER ,
69 ACCOUNTING_DATE ,
70 TRANSFER_TO_GL_FLAG ,
71 AP_INTERCOMPANY_ACCT,
72 AR_INTERCOMPANY_ACCT,
73 COST_OF_REMOVAL_CLEARING_ACCT,
74 COST_OF_REMOVAL_GAIN_ACCT,
75 COST_OF_REMOVAL_LOSS_ACCT,
76 DEPRN_ADJUSTMENT_ACCT,
77 NBV_RETIRED_GAIN_ACCT,
78 NBV_RETIRED_LOSS_ACCT,
79 PROCEEDS_OF_SALE_CLEARING_ACCT,
80 PROCEEDS_OF_SALE_GAIN_ACCT,
81 PROCEEDS_OF_SALE_LOSS_ACCT,
82 REVAL_RSV_RETIRED_GAIN_ACCT,
83 REVAL_RSV_RETIRED_LOSS_ACCT )
84 select ctlgd.event_id,
85 bc.FLEXBUILDER_DEFAULTS_CCID ,
86 bc.book_type_code ,
87 dp.PERIOD_NAME ,
88 dp.CALENDAR_PERIOD_CLOSE_DATE ,
89 dp.PERIOD_COUNTER ,
90 ctlgd.event_date ,
91 decode(bc.GL_POSTING_ALLOWED_FLAG ,
92 'YES', 'Y','N') ,
93 bc.AP_INTERCOMPANY_ACCT,
94 bc.AR_INTERCOMPANY_ACCT,
95 bc.COST_OF_REMOVAL_CLEARING_ACCT,
96 bc.COST_OF_REMOVAL_GAIN_ACCT,
97 bc.COST_OF_REMOVAL_LOSS_ACCT,
98 bc.DEPRN_ADJUSTMENT_ACCT,
99 bc.NBV_RETIRED_GAIN_ACCT,
100 bc.NBV_RETIRED_LOSS_ACCT,
101 bc.PROCEEDS_OF_SALE_CLEARING_ACCT,
102 bc.PROCEEDS_OF_SALE_GAIN_ACCT,
103 bc.PROCEEDS_OF_SALE_LOSS_ACCT,
104 bc.REVAL_RSV_RETIRED_GAIN_ACCT,
105 bc.REVAL_RSV_RETIRED_LOSS_ACCT
106 FROM xla_events_gt ctlgd,
107 fa_deprn_periods dp,
108 fa_book_controls bc ,
109 FA_TRANSACTION_HEADERS th
110 WHERE ctlgd.entity_code = 'TRANSACTIONS'
111 AND ctlgd.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
112 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
113 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
114 'REVALUATION', 'CIP_REVALUATION',
115 'TRANSFERS', 'CIP_TRANSFERS',
116 'CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
117 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS',
118 'RETIREMENTS', 'CIP_RETIREMENTS',
119 'REINSTATEMENTS', 'CIP_REINSTATEMENTS',
120 'DEPRECIATION_ADJUSTMENTS',
121 'UNPLANNED_DEPRECIATION',
122 'TERMINAL_GAIN_LOSS',
123 'RETIREMENT_ADJUSTMENT')
124 AND th.transaction_header_id = ctlgd.source_id_int_1
125 AND ctlgd.valuation_method = dp.book_type_code
126 AND ctlgd.valuation_method = bc.book_type_code
127 AND th.date_effective between dp.period_open_date and
128 nvl(dp.period_close_date, sysdate) ;
129
130
131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
132 fnd_log.string(G_LEVEL_PROCEDURE,
133 G_MODULE_NAME||l_procedure_name,
134 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
135 END IF;
136
137
138
139 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
140 fnd_log.string(G_LEVEL_PROCEDURE,
141 G_MODULE_NAME||l_procedure_name||'.end',
142 'End of procedure');
143 END IF;
144
145 EXCEPTION
146 WHEN others THEN
147 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
148 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
149 fnd_message.set_token('ORACLE_ERR',SQLERRM);
150 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
151 END IF;
152 raise;
153
154 end load_header_data_stg1;
155
156
157
158 /*======================================================================+
159 | |
160 | Private Function |
161 | load_header_data_stg2 |
162 | |
163 +======================================================================*/
164
165 PROCEDURE load_header_data_stg2 IS
166
167 l_procedure_name varchar2(80) := 'load_header_data_stg2';
168
169 BEGIN
170
171 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
172 fnd_log.string(G_LEVEL_PROCEDURE,
173 G_MODULE_NAME||l_procedure_name||'.begin',
174 'Beginning of procedure');
175 END IF;
176
177
178 insert into fa_xla_ext_headers_b_gt (
179 event_id ,
180 DEFAULT_CCID ,
181 BOOK_TYPE_CODE ,
182 PERIOD_NAME ,
183 PERIOD_CLOSE_DATE ,
184 PERIOD_COUNTER ,
185 ACCOUNTING_DATE ,
186 TRANSFER_TO_GL_FLAG ,
187 AP_INTERCOMPANY_ACCT,
188 AR_INTERCOMPANY_ACCT )
189 select ctlgd.event_id,
190 bc.FLEXBUILDER_DEFAULTS_CCID ,
191 bc.book_type_code ,
192 dp.PERIOD_NAME ,
193 dp.CALENDAR_PERIOD_CLOSE_DATE ,
194 dp.PERIOD_COUNTER ,
195 ctlgd.event_date ,
196 decode(bc.GL_POSTING_ALLOWED_FLAG ,
197 'YES', 'Y','N') ,
198 bc.AP_INTERCOMPANY_ACCT,
199 bc.AR_INTERCOMPANY_ACCT
200 FROM xla_events_gt ctlgd,
201 fa_deprn_periods dp,
202 fa_book_controls bc ,
203 FA_TRX_REFERENCES trx
204 WHERE ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
205 AND ctlgd.event_type_code in ('SOURCE_LINE_TRANSFERS',
206 'CIP_SOURCE_LINE_TRANSFERS',
207 'RESERVE_TRANSFERS')
208 AND trx.trx_reference_id = ctlgd.source_id_int_1
209 AND trx.event_id = ctlgd.event_id
210 AND trx.book_type_code = dp.book_type_code
211 AND trx.book_type_code = bc.book_type_code
212 AND dp.book_type_code = trx.book_type_code
213 AND trx.creation_date between dp.period_open_date and
214 nvl(dp.period_close_date, sysdate) ;
215
216
217 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
218 fnd_log.string(G_LEVEL_PROCEDURE,
219 G_MODULE_NAME||l_procedure_name,
220 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
221 END IF;
222
223
224
225 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
226 fnd_log.string(G_LEVEL_PROCEDURE,
227 G_MODULE_NAME||l_procedure_name||'.end',
228 'End of procedure');
229 END IF;
230
231 EXCEPTION
232 WHEN others THEN
233 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
234 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
235 fnd_message.set_token('ORACLE_ERR',SQLERRM);
236 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
237 END IF;
238 raise;
239
240 end load_header_data_stg2;
241
242
243
244 /*======================================================================+
245 | |
246 | Private Function |
247 | load_line_data_stg1 |
248 | |
249 +======================================================================*/
250
251 PROCEDURE load_line_data_stg1 IS
252
253 l_procedure_name varchar2(80) := 'load_line_data_stg1';
254
255 BEGIN
256
257 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
258 fnd_log.string(G_LEVEL_PROCEDURE,
259 G_MODULE_NAME||l_procedure_name||'.begin',
260 'Beginning of procedure');
261 END IF;
262
263
264 insert into fa_xla_ext_lines_stg_gt (
265 EVENT_ID ,
266 EVENT_TYPE_CODE ,
267 TRANSACTION_HEADER_ID ,
268 MEMBER_TRANSACTION_HEADER_ID ,
269 DISTRIBUTION_TYPE_CODE ,
270 BOOK_TYPE_CODE ,
271 LEDGER_ID ,
272 CURRENCY_CODE ,
273 ASSET_TYPE ,
274 ASSET_COST_ACCOUNT_CCID ,
275 ASSET_CLEARING_ACCOUNT_CCID ,
276 CIP_COST_ACCOUNT_CCID ,
277 CIP_CLEARING_ACCOUNT_CCID ,
278 RESERVE_ACCOUNT_CCID ,
279 DEPRN_EXPENSE_ACCOUNT_CCID ,
280 BONUS_RESERVE_ACCT_CCID ,
281 BONUS_EXPENSE_ACCOUNT_CCID ,
282 REVAL_AMORT_ACCOUNT_CCID ,
283 REVAL_RESERVE_ACCOUNT_CCID ,
284 UNPLAN_EXPENSE_ACCOUNT_CCID ,
285 ALT_COST_ACCOUNT_CCID ,
286 WRITE_OFF_ACCOUNT_CCID ,
287 ASSET_CLEARING_ACCT,
288 ASSET_COST_ACCT,
289 BONUS_DEPRN_EXPENSE_ACCT,
290 BONUS_RESERVE_ACCT,
291 CIP_CLEARING_ACCT,
292 CIP_COST_ACCT,
293 DEPRN_RESERVE_ACCT,
294 REVAL_RESERVE_ACCT )
295 select ctlgd.EVENT_ID ,
296 ctlgd.event_type_code ,
297 th.transaction_header_id ,
298 nvl(th.member_transaction_header_id,
299 th.transaction_header_id) ,
300 'TRX' ,
301 bc.book_type_code , -- Bug:6272229
302 bc.set_of_books_id ,
303 le.currency_code ,
304 ah.asset_type ,
305 cb.ASSET_COST_ACCOUNT_CCID ,
306 cb.ASSET_CLEARING_ACCOUNT_CCID ,
307 cb.WIP_COST_ACCOUNT_CCID ,
308 cb.WIP_CLEARING_ACCOUNT_CCID ,
309 cb.RESERVE_ACCOUNT_CCID ,
310 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
311 cb.BONUS_RESERVE_ACCT_CCID ,
312 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
313 cb.REVAL_AMORT_ACCOUNT_CCID ,
314 cb.REVAL_RESERVE_ACCOUNT_CCID ,
315 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
316 cb.ALT_COST_ACCOUNT_CCID ,
317 cb.WRITE_OFF_ACCOUNT_CCID ,
318 cb.ASSET_CLEARING_ACCT,
319 cb.ASSET_COST_ACCT,
320 cb.BONUS_DEPRN_EXPENSE_ACCT,
321 cb.BONUS_DEPRN_RESERVE_ACCT,
322 cb.CIP_CLEARING_ACCT,
323 cb.CIP_COST_ACCT,
324 cb.DEPRN_RESERVE_ACCT,
325 cb.REVAL_RESERVE_ACCT
326 from fa_additions_b ad,
327 fa_asset_history ah,
328 fa_book_controls bc,
329 fa_category_books cb,
330 gl_ledgers le,
331 fa_transaction_headers th,
332 xla_events_gt ctlgd
333 where ctlgd.entity_code = 'TRANSACTIONS'
334 AND ctlgd.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
335 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
336 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
337 'REVALUATION', 'CIP_REVALUATION',
338 'TRANSFERS', 'CIP_TRANSFERS',
339 'CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
340 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS',
341 'RETIREMENTS', 'CIP_RETIREMENTS',
342 'REINSTATEMENTS', 'CIP_REINSTATEMENTS',
343 'DEPRECIATION_ADJUSTMENTS',
344 'UNPLANNED_DEPRECIATION',
345 'TERMINAL_GAIN_LOSS',
346 'RETIREMENT_ADJUSTMENT')
347 AND (th.transaction_header_id = ctlgd.source_id_int_1 or
348 th.member_transaction_header_id = ctlgd.source_id_int_1) -- this is what grabs the groups
349 -- AND th.book_type_code = ctlgd.valuation_method -- Bug:6272229
350 AND bc.book_type_code = ctlgd.valuation_method
351 AND le.ledger_id = bc.set_of_books_id
352 AND ad.asset_id = th.asset_id
353 AND ah.asset_id = th.asset_id
354 AND th.transaction_header_id between ah.transaction_header_id_in and
355 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
356 AND cb.category_id = ah.category_id
357 AND cb.book_type_code = ctlgd.valuation_method
358 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
359 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP') ;
360
361
362 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
363 fnd_log.string(G_LEVEL_PROCEDURE,
364 G_MODULE_NAME||l_procedure_name,
365 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
366 END IF;
367
368
369
370 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
371 fnd_log.string(G_LEVEL_PROCEDURE,
372 G_MODULE_NAME||l_procedure_name||'.end',
373 'End of procedure');
374 END IF;
375
376 EXCEPTION
377 WHEN others THEN
378 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
379 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
380 fnd_message.set_token('ORACLE_ERR',SQLERRM);
381 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
382 END IF;
383 raise;
384
385 end load_line_data_stg1;
386
387
388
389 /*======================================================================+
390 | |
391 | Private Function |
392 | load_line_data_stg2 |
393 | |
394 +======================================================================*/
395
396 PROCEDURE load_line_data_stg2 IS
397
398 l_procedure_name varchar2(80) := 'load_line_data_stg2';
399
400 BEGIN
401
402 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
403 fnd_log.string(G_LEVEL_PROCEDURE,
404 G_MODULE_NAME||l_procedure_name||'.begin',
405 'Beginning of procedure');
406 END IF;
407
408
409 insert into fa_xla_ext_lines_stg_gt (
410 EVENT_ID ,
411 EVENT_TYPE_CODE ,
412 TRANSACTION_HEADER_ID ,
413 MEMBER_TRANSACTION_HEADER_ID ,
414 DISTRIBUTION_TYPE_CODE ,
415 BOOK_TYPE_CODE ,
416 LEDGER_ID ,
417 CURRENCY_CODE ,
418 ASSET_TYPE ,
419 ASSET_COST_ACCOUNT_CCID ,
420 ASSET_CLEARING_ACCOUNT_CCID ,
421 CIP_COST_ACCOUNT_CCID ,
422 CIP_CLEARING_ACCOUNT_CCID ,
423 RESERVE_ACCOUNT_CCID ,
424 DEPRN_EXPENSE_ACCOUNT_CCID ,
425 BONUS_RESERVE_ACCT_CCID ,
426 BONUS_EXPENSE_ACCOUNT_CCID ,
427 REVAL_AMORT_ACCOUNT_CCID ,
428 REVAL_RESERVE_ACCOUNT_CCID ,
429 UNPLAN_EXPENSE_ACCOUNT_CCID ,
430 ALT_COST_ACCOUNT_CCID ,
431 WRITE_OFF_ACCOUNT_CCID ,
432 ASSET_CLEARING_ACCT,
433 ASSET_COST_ACCT,
434 BONUS_DEPRN_EXPENSE_ACCT,
435 BONUS_RESERVE_ACCT,
436 CIP_CLEARING_ACCT,
437 CIP_COST_ACCT,
438 DEPRN_RESERVE_ACCT,
439 REVAL_RESERVE_ACCT )
440 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ctlgd.EVENT_ID ,
441 ctlgd.event_type_code ,
442 th.transaction_header_id ,
443 nvl(th.member_transaction_header_id,
444 th.transaction_header_id) ,
445 'TRX' ,
446 bc.book_type_code , -- Bug:6272229
447 bc.set_of_books_id ,
448 le.currency_code ,
449 ah.asset_type ,
450 cb.ASSET_COST_ACCOUNT_CCID ,
451 cb.ASSET_CLEARING_ACCOUNT_CCID ,
452 cb.WIP_COST_ACCOUNT_CCID ,
453 cb.WIP_CLEARING_ACCOUNT_CCID ,
454 cb.RESERVE_ACCOUNT_CCID ,
455 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
456 cb.BONUS_RESERVE_ACCT_CCID ,
457 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
458 cb.REVAL_AMORT_ACCOUNT_CCID ,
459 cb.REVAL_RESERVE_ACCOUNT_CCID ,
460 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
461 cb.ALT_COST_ACCOUNT_CCID ,
462 cb.WRITE_OFF_ACCOUNT_CCID ,
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 fa_trx_references trx,
478 xla_events_gt ctlgd
479 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
480 AND ctlgd.event_type_code in ('SOURCE_LINE_TRANSFERS',
481 'CIP_SOURCE_LINE_TRANSFERS',
482 'RESERVE_TRANSFERS')
483 AND trx.trx_reference_id = ctlgd.source_id_int_1
484 AND (th.transaction_header_id = trx.src_transaction_header_id or
485 th.transaction_header_id = trx.dest_transaction_header_id )
486 AND bc.book_type_code = ctlgd.valuation_method
487 AND le.ledger_id = bc.set_of_books_id
488 AND ad.asset_id = th.asset_id
489 AND ah.asset_id = th.asset_id
490 AND th.transaction_header_id between ah.transaction_header_id_in and
491 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
492 AND cb.category_id = ah.category_id
493 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
494 AND ah.asset_type in ('CAPITALIZED', 'CIP', 'GROUP')
495 AND ad.asset_type in ('CAPITALIZED', 'CIP', 'GROUP') ;
496
497
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
499 fnd_log.string(G_LEVEL_PROCEDURE,
500 G_MODULE_NAME||l_procedure_name,
501 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
502 END IF;
503
504
505 insert into fa_xla_ext_lines_stg_gt (
506 EVENT_ID ,
507 EVENT_TYPE_CODE ,
508 TRANSACTION_HEADER_ID ,
509 MEMBER_TRANSACTION_HEADER_ID ,
510 DISTRIBUTION_TYPE_CODE ,
511 BOOK_TYPE_CODE ,
512 LEDGER_ID ,
513 CURRENCY_CODE ,
514 ASSET_TYPE ,
515 ASSET_COST_ACCOUNT_CCID ,
516 ASSET_CLEARING_ACCOUNT_CCID ,
517 CIP_COST_ACCOUNT_CCID ,
518 CIP_CLEARING_ACCOUNT_CCID ,
519 RESERVE_ACCOUNT_CCID ,
520 DEPRN_EXPENSE_ACCOUNT_CCID ,
521 BONUS_RESERVE_ACCT_CCID ,
522 BONUS_EXPENSE_ACCOUNT_CCID ,
523 REVAL_AMORT_ACCOUNT_CCID ,
524 REVAL_RESERVE_ACCOUNT_CCID ,
525 UNPLAN_EXPENSE_ACCOUNT_CCID ,
526 ALT_COST_ACCOUNT_CCID ,
527 WRITE_OFF_ACCOUNT_CCID ,
528 ASSET_CLEARING_ACCT,
529 ASSET_COST_ACCT,
530 BONUS_DEPRN_EXPENSE_ACCT,
531 BONUS_RESERVE_ACCT,
532 CIP_CLEARING_ACCT,
533 CIP_COST_ACCT,
534 DEPRN_RESERVE_ACCT,
535 REVAL_RESERVE_ACCT )
536 select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ctlgd.EVENT_ID ,
537 ctlgd.event_type_code ,
538 th.transaction_header_id ,
539 nvl(th.member_transaction_header_id,
540 th.transaction_header_id) ,
541 'TRX' ,
542 bc.book_type_code , -- Bug:6272229
543 bc.set_of_books_id ,
544 le.currency_code ,
545 ah.asset_type ,
546 cb.ASSET_COST_ACCOUNT_CCID ,
547 cb.ASSET_CLEARING_ACCOUNT_CCID ,
548 cb.WIP_COST_ACCOUNT_CCID ,
549 cb.WIP_CLEARING_ACCOUNT_CCID ,
550 cb.RESERVE_ACCOUNT_CCID ,
551 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
552 cb.BONUS_RESERVE_ACCT_CCID ,
553 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
554 cb.REVAL_AMORT_ACCOUNT_CCID ,
555 cb.REVAL_RESERVE_ACCOUNT_CCID ,
556 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
557 cb.ALT_COST_ACCOUNT_CCID ,
558 cb.WRITE_OFF_ACCOUNT_CCID ,
559 cb.ASSET_CLEARING_ACCT,
560 cb.ASSET_COST_ACCT,
561 cb.BONUS_DEPRN_EXPENSE_ACCT,
562 cb.BONUS_DEPRN_RESERVE_ACCT,
563 cb.CIP_CLEARING_ACCT,
564 cb.CIP_COST_ACCT,
565 cb.DEPRN_RESERVE_ACCT,
566 cb.REVAL_RESERVE_ACCT
567 from fa_additions_b ad,
568 fa_asset_history ah,
569 fa_book_controls bc,
570 fa_category_books cb,
571 gl_ledgers le,
572 fa_transaction_headers th,
573 fa_trx_references trx,
574 xla_events_gt ctlgd
575 where ctlgd.entity_code = 'INTER_ASSET_TRANSACTIONS'
576 AND ctlgd.event_type_code in ('SOURCE_LINE_TRANSFERS',
577 'CIP_SOURCE_LINE_TRANSFERS',
578 'RESERVE_TRANSFERS')
579 AND trx.trx_reference_id = ctlgd.source_id_int_1
580 AND (th.member_transaction_header_id = trx.src_transaction_header_id or
581 th.member_transaction_header_id = trx.dest_transaction_header_id )
582 AND bc.book_type_code = ctlgd.valuation_method
583 AND le.ledger_id = bc.set_of_books_id
584 AND ad.asset_id = th.asset_id
585 AND ah.asset_id = th.asset_id
586 AND th.transaction_header_id between ah.transaction_header_id_in and
587 nvl(ah.transaction_header_id_out - 1, th.transaction_header_id)
588 AND cb.category_id = ah.category_id
589 AND cb.book_type_code = ctlgd.valuation_method --th.book_type_code
590 AND ah.asset_type in ('GROUP')
591 AND ad.asset_type in ('GROUP') ;
592
593
594 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
595 fnd_log.string(G_LEVEL_PROCEDURE,
596 G_MODULE_NAME||l_procedure_name,
597 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
598 END IF;
599
600
601
602 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
603 fnd_log.string(G_LEVEL_PROCEDURE,
604 G_MODULE_NAME||l_procedure_name||'.end',
605 'End of procedure');
606 END IF;
607
608 EXCEPTION
609 WHEN others THEN
610 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
611 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
612 fnd_message.set_token('ORACLE_ERR',SQLERRM);
613 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
614 END IF;
615 raise;
616
617 end load_line_data_stg2;
618
619
620
621 /*======================================================================+
622 | |
623 | Private Function |
624 | load_line_data_fin1 |
625 | |
626 +======================================================================*/
627
628 PROCEDURE load_line_data_fin1 IS
629
630 l_procedure_name varchar2(80) := 'load_line_data_fin1';
631
632 BEGIN
633
634 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
635 fnd_log.string(G_LEVEL_PROCEDURE,
636 G_MODULE_NAME||l_procedure_name||'.begin',
637 'Beginning of procedure');
638 END IF;
639
640
641 insert into fa_xla_ext_lines_b_gt (
642 EVENT_ID ,
643 LINE_NUMBER ,
644 DISTRIBUTION_ID ,
645 DISTRIBUTION_TYPE_CODE ,
646 LEDGER_ID ,
647 CURRENCY_CODE ,
648 BOOK_TYPE_CODE ,
649 GENERATED_CCID ,
650 ASSET_ID ,
651 ASSET_TYPE ,
652 ASSET_COST_ACCOUNT_CCID ,
653 ASSET_CLEARING_ACCOUNT_CCID ,
654 CIP_COST_ACCOUNT_CCID ,
655 CIP_CLEARING_ACCOUNT_CCID ,
656 RESERVE_ACCOUNT_CCID ,
657 DEPRN_EXPENSE_ACCOUNT_CCID ,
658 BONUS_RESERVE_ACCT_CCID ,
659 BONUS_EXPENSE_ACCOUNT_CCID ,
660 REVAL_AMORT_ACCOUNT_CCID ,
661 REVAL_RESERVE_ACCOUNT_CCID ,
662 UNPLAN_EXPENSE_ACCOUNT_CCID ,
663 ALT_COST_ACCOUNT_CCID ,
664 WRITE_OFF_ACCOUNT_CCID ,
665 ENTERED_AMOUNT ,
666 ADJUSTMENT_LINE_ID,
667 ADJUSTMENT_TYPE,
668 PAYABLES_CCID,
669 EXPENSE_ACCOUNT_CCID,
670 ASSET_CLEARING_ACCT,
671 ASSET_COST_ACCT,
672 BONUS_DEPRN_EXPENSE_ACCT,
673 BONUS_RESERVE_ACCT,
674 CIP_CLEARING_ACCT,
675 CIP_COST_ACCT,
676 DEPRN_RESERVE_ACCT,
677 REVAL_RESERVE_ACCT,
678 TRANSACTION_HEADER_ID )
679 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
680 adj.adjustment_line_id ,
681 adj.distribution_id ,
682 stg.distribution_type_code ,
683 stg.ledger_id ,
684 stg.currency_code ,
685 stg.book_type_code ,
686 adj.code_combination_id ,
687 adj.asset_id ,
688 stg.asset_type ,
689 stg.ASSET_COST_ACCOUNT_CCID ,
690 stg.ASSET_CLEARING_ACCOUNT_CCID ,
691 stg.CIP_COST_ACCOUNT_CCID ,
692 stg.CIP_CLEARING_ACCOUNT_CCID ,
693 stg.RESERVE_ACCOUNT_CCID ,
694 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
695 stg.BONUS_RESERVE_ACCT_CCID ,
696 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
697 stg.REVAL_AMORT_ACCOUNT_CCID ,
698 stg.REVAL_RESERVE_ACCOUNT_CCID ,
699 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
700 stg.ALT_COST_ACCOUNT_CCID ,
701 stg.WRITE_OFF_ACCOUNT_CCID ,
702 decode(adj.adjustment_type,
703 'COST CLEARING',
704 decode(debit_credit_flag,
705 'CR', adjustment_amount,
706 -1 * adjustment_amount),
707 'RESERVE',
708 decode(debit_credit_flag,
709 'CR', adjustment_amount,
710 -1 * adjustment_amount),
711 'BONUS RESERVE',
712 decode(debit_credit_flag,
713 'CR', adjustment_amount,
714 -1 * adjustment_amount),
715 'REVAL RESERVE',
716 decode(debit_credit_flag,
717 'CR', adjustment_amount,
718 -1 * adjustment_amount),
719 'CIP COST',
720 decode(stg.event_type_code,
721 'CAPITALIZATION',
722 decode(debit_credit_flag,
723 'CR', adjustment_amount,
724 -1 * adjustment_amount),
725 'REVERSE_CAPITALIZATION',
726 decode(debit_credit_flag,
727 'CR', adjustment_amount,
728 -1 * adjustment_amount),
729 decode(debit_credit_flag,
730 'DR', adjustment_amount,
731 -1 * adjustment_amount)),
732 'COST',
733 decode(debit_credit_flag,
734 'DR', adjustment_amount,
735 -1 * adjustment_amount),
736 'EXPENSE',
737 decode(debit_credit_flag,
738 'DR', adjustment_amount,
739 -1 * adjustment_amount),
740 'BONUS EXPENSE',
741 decode(debit_credit_flag,
742 'DR', adjustment_amount,
743 -1 * adjustment_amount),
744 'NBV RETIRED',
745 decode(debit_credit_flag,
746 'DR', adjustment_amount,
747 -1 * adjustment_amount),
748 decode(debit_credit_flag,
749 'DR', adjustment_amount,
750 -1 * adjustment_amount)) ,
751 adj.ADJUSTMENT_LINE_ID,
752 adj.ADJUSTMENT_TYPE,
753 ai.PAYABLES_CODE_COMBINATION_ID,
754 dh.CODE_COMBINATION_ID,
755 stg.ASSET_CLEARING_ACCT,
756 stg.ASSET_COST_ACCT,
757 stg.BONUS_DEPRN_EXPENSE_ACCT,
758 stg.BONUS_RESERVE_ACCT,
759 stg.CIP_CLEARING_ACCT,
760 stg.CIP_COST_ACCT,
761 stg.DEPRN_RESERVE_ACCT,
762 stg.REVAL_RESERVE_ACCT,
763 stg.TRANSACTION_HEADER_ID
764 from fa_xla_ext_lines_stg_gt stg,
765 fa_adjustments adj,
766 fa_distribution_history dh,
767 fa_locations loc,
768 fa_lookups lu ,
769 FA_ASSET_INVOICES ai
770 WHERE adj.transaction_header_id = stg.transaction_header_id
771 AND adj.book_type_code = stg.book_type_code
772 AND adj.distribution_id = dh.distribution_id
773 AND dh.location_id = loc.location_id
774 -- AND dh.assigned_to = emp.employee_id(+)
775 AND lu.lookup_type = 'JOURNAL ENTRIES'
776 AND lu.lookup_code = adj.source_type_code || ' ' ||
777 decode (adj.adjustment_type,
778 'CIP COST', 'COST',
779 adj.adjustment_type)
780 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
781 AND nvl(adj.track_member_flag, 'N') = 'N'
782 AND adj.adjustment_amount <> 0
783 AND stg.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
784 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
785 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
786 'REVALUATION', 'CIP_REVALUATION',
787 'DEPRECIATION_ADJUSTMENTS',
788 'UNPLANNED_DEPRECIATION',
789 'TERMINAL_GAIN_LOSS',
790 'RETIREMENT_ADJUSTMENT')
791 AND adj.source_line_id = ai.source_line_id(+) ;
792
793 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
794 fnd_log.string(G_LEVEL_PROCEDURE,
795 G_MODULE_NAME||l_procedure_name,
796 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
797 END IF;
798
799
800
801 if (fa_xla_extract_util_pkg.G_alc_enabled) then
802
803
804
805 insert into fa_xla_ext_lines_b_gt (
806 EVENT_ID ,
807 LINE_NUMBER ,
808 DISTRIBUTION_ID ,
809 DISTRIBUTION_TYPE_CODE ,
810 LEDGER_ID ,
811 CURRENCY_CODE ,
812 BOOK_TYPE_CODE ,
813 GENERATED_CCID ,
814 ASSET_ID ,
815 ASSET_TYPE ,
816 ASSET_COST_ACCOUNT_CCID ,
817 ASSET_CLEARING_ACCOUNT_CCID ,
818 CIP_COST_ACCOUNT_CCID ,
819 CIP_CLEARING_ACCOUNT_CCID ,
820 RESERVE_ACCOUNT_CCID ,
821 DEPRN_EXPENSE_ACCOUNT_CCID ,
822 BONUS_RESERVE_ACCT_CCID ,
823 BONUS_EXPENSE_ACCOUNT_CCID ,
824 REVAL_AMORT_ACCOUNT_CCID ,
825 REVAL_RESERVE_ACCOUNT_CCID ,
826 UNPLAN_EXPENSE_ACCOUNT_CCID ,
827 ALT_COST_ACCOUNT_CCID ,
828 WRITE_OFF_ACCOUNT_CCID ,
829 ENTERED_AMOUNT ,
830 ADJUSTMENT_LINE_ID,
831 ADJUSTMENT_TYPE,
832 PAYABLES_CCID,
833 EXPENSE_ACCOUNT_CCID,
834 ASSET_CLEARING_ACCT,
835 ASSET_COST_ACCT,
836 BONUS_DEPRN_EXPENSE_ACCT,
837 BONUS_RESERVE_ACCT,
838 CIP_CLEARING_ACCT,
839 CIP_COST_ACCT,
840 DEPRN_RESERVE_ACCT,
841 REVAL_RESERVE_ACCT,
842 TRANSACTION_HEADER_ID )
843 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
844 adj.adjustment_line_id ,
845 adj.distribution_id ,
846 stg.distribution_type_code ,
847 bc.set_of_books_id ,
848 le.currency_code ,
849 stg.book_type_code ,
850 adj.code_combination_id ,
851 adj.asset_id ,
852 stg.asset_type ,
853 stg.ASSET_COST_ACCOUNT_CCID ,
854 stg.ASSET_CLEARING_ACCOUNT_CCID ,
855 stg.CIP_COST_ACCOUNT_CCID ,
856 stg.CIP_CLEARING_ACCOUNT_CCID ,
857 stg.RESERVE_ACCOUNT_CCID ,
858 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
859 stg.BONUS_RESERVE_ACCT_CCID ,
860 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
861 stg.REVAL_AMORT_ACCOUNT_CCID ,
862 stg.REVAL_RESERVE_ACCOUNT_CCID ,
863 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
864 stg.ALT_COST_ACCOUNT_CCID ,
865 stg.WRITE_OFF_ACCOUNT_CCID ,
866 decode(adj.adjustment_type,
867 'COST CLEARING',
868 decode(debit_credit_flag,
869 'CR', adjustment_amount,
870 -1 * adjustment_amount),
871 'RESERVE',
872 decode(debit_credit_flag,
873 'CR', adjustment_amount,
874 -1 * adjustment_amount),
875 'BONUS RESERVE',
876 decode(debit_credit_flag,
877 'CR', adjustment_amount,
878 -1 * adjustment_amount),
879 'REVAL RESERVE',
880 decode(debit_credit_flag,
881 'CR', adjustment_amount,
882 -1 * adjustment_amount),
883 'CIP COST',
884 decode(stg.event_type_code,
885 'CAPITALIZATION',
886 decode(debit_credit_flag,
887 'CR', adjustment_amount,
888 -1 * adjustment_amount),
889 'REVERSE_CAPITALIZATION',
890 decode(debit_credit_flag,
891 'CR', adjustment_amount,
892 -1 * adjustment_amount),
893 decode(debit_credit_flag,
894 'DR', adjustment_amount,
895 -1 * adjustment_amount)),
896 'COST',
897 decode(debit_credit_flag,
898 'DR', adjustment_amount,
899 -1 * adjustment_amount),
900 'EXPENSE',
901 decode(debit_credit_flag,
902 'DR', adjustment_amount,
903 -1 * adjustment_amount),
904 'BONUS EXPENSE',
905 decode(debit_credit_flag,
906 'DR', adjustment_amount,
907 -1 * adjustment_amount),
908 'NBV RETIRED',
909 decode(debit_credit_flag,
910 'DR', adjustment_amount,
911 -1 * adjustment_amount),
912 decode(debit_credit_flag,
913 'DR', adjustment_amount,
914 -1 * adjustment_amount)) ,
915 adj.ADJUSTMENT_LINE_ID,
916 adj.ADJUSTMENT_TYPE,
917 ai.PAYABLES_CODE_COMBINATION_ID,
918 dh.CODE_COMBINATION_ID,
919 stg.ASSET_CLEARING_ACCT,
920 stg.ASSET_COST_ACCT,
921 stg.BONUS_DEPRN_EXPENSE_ACCT,
922 stg.BONUS_RESERVE_ACCT,
923 stg.CIP_CLEARING_ACCT,
924 stg.CIP_COST_ACCT,
925 stg.DEPRN_RESERVE_ACCT,
926 stg.REVAL_RESERVE_ACCT,
927 stg.TRANSACTION_HEADER_ID
928 from fa_xla_ext_lines_stg_gt stg,
929 fa_mc_adjustments adj,
930 fa_distribution_history dh,
931 fa_locations loc,
932 fa_lookups lu ,
933 fa_mc_asset_invoices ai,
934 fa_mc_book_controls bc ,
935 gl_ledgers le
936 WHERE adj.transaction_header_id = stg.transaction_header_id
937 AND adj.book_type_code = stg.book_type_code
938 AND adj.distribution_id = dh.distribution_id
939 AND dh.location_id = loc.location_id
940 -- AND dh.assigned_to = emp.employee_id(+)
941 AND lu.lookup_type = 'JOURNAL ENTRIES'
942 AND lu.lookup_code = adj.source_type_code || ' ' ||
943 decode (adj.adjustment_type,
944 'CIP COST', 'COST',
945 adj.adjustment_type)
946 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
947 AND nvl(adj.track_member_flag, 'N') = 'N'
948 AND adj.adjustment_amount <> 0
949 AND stg.event_type_code in ('ADDITIONS', 'CIP_ADDITIONS',
950 'ADJUSTMENTS', 'CIP_ADJUSTMENTS',
951 'CAPITALIZATION', 'REVERSE_CAPITALIZATION',
952 'REVALUATION', 'CIP_REVALUATION',
953 'DEPRECIATION_ADJUSTMENTS',
954 'UNPLANNED_DEPRECIATION',
955 'TERMINAL_GAIN_LOSS',
956 'RETIREMENT_ADJUSTMENT')
957 AND adj.source_line_id = ai.source_line_id(+)
958 AND bc.book_type_code = stg.book_type_code
959 AND bc.set_of_books_id = le.ledger_id
960 AND adj.set_of_books_id = bc.set_of_books_id
961 AND adj.set_of_books_id = ai.set_of_books_id(+) ;
962
963 end if;
964
965
966 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
967 fnd_log.string(G_LEVEL_PROCEDURE,
968 G_MODULE_NAME||l_procedure_name,
969 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
970 END IF;
971
972
973
974 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
975 fnd_log.string(G_LEVEL_PROCEDURE,
976 G_MODULE_NAME||l_procedure_name||'.end',
977 'End of procedure');
978 END IF;
979
980 EXCEPTION
981 WHEN others THEN
982 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
983 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
984 fnd_message.set_token('ORACLE_ERR',SQLERRM);
985 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
986 END IF;
987 raise;
988
989 end load_line_data_fin1;
990
991
992
993 /*======================================================================+
994 | |
995 | Private Function |
996 | load_line_data_fin2 |
997 | |
998 +======================================================================*/
999
1000 PROCEDURE load_line_data_fin2 IS
1001
1002 l_procedure_name varchar2(80) := 'load_line_data_fin2';
1003
1004 BEGIN
1005
1006 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1007 fnd_log.string(G_LEVEL_PROCEDURE,
1008 G_MODULE_NAME||l_procedure_name||'.begin',
1009 'Beginning of procedure');
1010 END IF;
1011
1012
1013 insert into fa_xla_ext_lines_b_gt (
1014 EVENT_ID ,
1015 LINE_NUMBER ,
1016 DISTRIBUTION_ID ,
1017 DISTRIBUTION_TYPE_CODE ,
1018 LEDGER_ID ,
1019 CURRENCY_CODE ,
1020 BOOK_TYPE_CODE ,
1021 GENERATED_CCID ,
1022 ASSET_ID ,
1023 ASSET_TYPE ,
1024 ASSET_COST_ACCOUNT_CCID ,
1025 ASSET_CLEARING_ACCOUNT_CCID ,
1026 CIP_COST_ACCOUNT_CCID ,
1027 CIP_CLEARING_ACCOUNT_CCID ,
1028 RESERVE_ACCOUNT_CCID ,
1029 DEPRN_EXPENSE_ACCOUNT_CCID ,
1030 BONUS_RESERVE_ACCT_CCID ,
1031 BONUS_EXPENSE_ACCOUNT_CCID ,
1032 REVAL_AMORT_ACCOUNT_CCID ,
1033 REVAL_RESERVE_ACCOUNT_CCID ,
1034 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1035 ALT_COST_ACCOUNT_CCID ,
1036 WRITE_OFF_ACCOUNT_CCID ,
1037 ENTERED_AMOUNT ,
1038 ADJUSTMENT_LINE_ID,
1039 ADJUSTMENT_TYPE,
1040 SOURCE_DEST_CODE,
1041 PAYABLES_CCID,
1042 EXPENSE_ACCOUNT_CCID,
1043 ASSET_CLEARING_ACCT,
1044 ASSET_COST_ACCT,
1045 BONUS_DEPRN_EXPENSE_ACCT,
1046 BONUS_RESERVE_ACCT,
1047 CIP_CLEARING_ACCT,
1048 CIP_COST_ACCT,
1049 DEPRN_RESERVE_ACCT,
1050 REVAL_RESERVE_ACCT,
1051 TRANSACTION_HEADER_ID )
1052 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1053 adj.adjustment_line_id ,
1054 adj.distribution_id ,
1055 stg.distribution_type_code ,
1056 stg.ledger_id ,
1057 stg.currency_code ,
1058 stg.book_type_code ,
1059 adj.code_combination_id ,
1060 adj.asset_id ,
1061 stg.asset_type ,
1062 stg.ASSET_COST_ACCOUNT_CCID ,
1063 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1064 stg.CIP_COST_ACCOUNT_CCID ,
1065 stg.CIP_CLEARING_ACCOUNT_CCID ,
1066 stg.RESERVE_ACCOUNT_CCID ,
1067 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1068 stg.BONUS_RESERVE_ACCT_CCID ,
1069 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1070 stg.REVAL_AMORT_ACCOUNT_CCID ,
1071 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1072 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1073 stg.ALT_COST_ACCOUNT_CCID ,
1074 stg.WRITE_OFF_ACCOUNT_CCID ,
1075 decode(adj.source_dest_code,
1076 'SOURCE',
1077 decode(adj.adjustment_type,
1078 'RESERVE',
1079 decode(debit_credit_flag,
1080 'DR', adjustment_amount,
1081 -1 * adjustment_amount),
1082 'BONUS RESERVE',
1083 decode(debit_credit_flag,
1084 'DR', adjustment_amount,
1085 -1 * adjustment_amount),
1086 'REVAL RESERVE',
1087 decode(debit_credit_flag,
1088 'DR', adjustment_amount,
1089 -1 * adjustment_amount),
1090 decode(debit_credit_flag,
1091 'CR', adjustment_amount,
1092 -1 * adjustment_amount)),
1093 decode(adj.adjustment_type,
1094 'RESERVE',
1095 decode(debit_credit_flag,
1096 'CR', adjustment_amount,
1097 -1 * adjustment_amount),
1098 'BONUS RESERVE',
1099 decode(debit_credit_flag,
1100 'CR', adjustment_amount,
1101 -1 * adjustment_amount),
1102 'REVAL RESERVE',
1103 decode(debit_credit_flag,
1104 'CR', adjustment_amount,
1105 -1 * adjustment_amount),
1106 decode(debit_credit_flag,
1107 'DR', adjustment_amount,
1108 -1 * adjustment_amount))) ,
1109 adj.ADJUSTMENT_LINE_ID,
1110 adj.ADJUSTMENT_TYPE,
1111 adj.SOURCE_DEST_CODE,
1112 ai.PAYABLES_CODE_COMBINATION_ID,
1113 dh.CODE_COMBINATION_ID,
1114 stg.ASSET_CLEARING_ACCT,
1115 stg.ASSET_COST_ACCT,
1116 stg.BONUS_DEPRN_EXPENSE_ACCT,
1117 stg.BONUS_RESERVE_ACCT,
1118 stg.CIP_CLEARING_ACCT,
1119 stg.CIP_COST_ACCT,
1120 stg.DEPRN_RESERVE_ACCT,
1121 stg.REVAL_RESERVE_ACCT,
1122 stg.TRANSACTION_HEADER_ID
1123 from fa_xla_ext_lines_stg_gt stg,
1124 fa_adjustments adj,
1125 fa_distribution_history dh,
1126 fa_locations loc,
1127 fa_lookups lu ,
1128 FA_ASSET_INVOICES ai
1129 WHERE adj.transaction_header_id = stg.transaction_header_id
1130 AND adj.book_type_code = stg.book_type_code
1131 AND adj.distribution_id = dh.distribution_id
1132 AND dh.location_id = loc.location_id
1133 -- AND dh.assigned_to = emp.employee_id(+)
1134 AND lu.lookup_type = 'JOURNAL ENTRIES'
1135 AND lu.lookup_code = adj.source_type_code || ' ' ||
1136 decode (adj.adjustment_type,
1137 'CIP COST', 'COST',
1138 adj.adjustment_type)
1139 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1140 AND nvl(adj.track_member_flag, 'N') = 'N'
1141 AND adj.adjustment_amount <> 0
1142 AND stg.event_type_code in ('SOURCE_LINE_TRANSFERS',
1143 'CIP_SOURCE_LINE_TRANSFERS',
1144 'RESERVE_TRANSFERS')
1145 AND adj.source_line_id = ai.source_line_id(+) ;
1146
1147 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1148 fnd_log.string(G_LEVEL_PROCEDURE,
1149 G_MODULE_NAME||l_procedure_name,
1150 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1151 END IF;
1152
1153
1154
1155 if (fa_xla_extract_util_pkg.G_alc_enabled) then
1156
1157
1158
1159 insert into fa_xla_ext_lines_b_gt (
1160 EVENT_ID ,
1161 LINE_NUMBER ,
1162 DISTRIBUTION_ID ,
1163 DISTRIBUTION_TYPE_CODE ,
1164 LEDGER_ID ,
1165 CURRENCY_CODE ,
1166 BOOK_TYPE_CODE ,
1167 GENERATED_CCID ,
1168 ASSET_ID ,
1169 ASSET_TYPE ,
1170 ASSET_COST_ACCOUNT_CCID ,
1171 ASSET_CLEARING_ACCOUNT_CCID ,
1172 CIP_COST_ACCOUNT_CCID ,
1173 CIP_CLEARING_ACCOUNT_CCID ,
1174 RESERVE_ACCOUNT_CCID ,
1175 DEPRN_EXPENSE_ACCOUNT_CCID ,
1176 BONUS_RESERVE_ACCT_CCID ,
1177 BONUS_EXPENSE_ACCOUNT_CCID ,
1178 REVAL_AMORT_ACCOUNT_CCID ,
1179 REVAL_RESERVE_ACCOUNT_CCID ,
1180 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1181 ALT_COST_ACCOUNT_CCID ,
1182 WRITE_OFF_ACCOUNT_CCID ,
1183 ENTERED_AMOUNT ,
1184 ADJUSTMENT_LINE_ID,
1185 ADJUSTMENT_TYPE,
1186 SOURCE_DEST_CODE,
1187 PAYABLES_CCID,
1188 EXPENSE_ACCOUNT_CCID,
1189 ASSET_CLEARING_ACCT,
1190 ASSET_COST_ACCT,
1191 BONUS_DEPRN_EXPENSE_ACCT,
1192 BONUS_RESERVE_ACCT,
1193 CIP_CLEARING_ACCT,
1194 CIP_COST_ACCT,
1195 DEPRN_RESERVE_ACCT,
1196 REVAL_RESERVE_ACCT,
1197 TRANSACTION_HEADER_ID )
1198 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1199 adj.adjustment_line_id ,
1200 adj.distribution_id ,
1201 stg.distribution_type_code ,
1202 bc.set_of_books_id ,
1203 le.currency_code ,
1204 stg.book_type_code ,
1205 adj.code_combination_id ,
1206 adj.asset_id ,
1207 stg.asset_type ,
1208 stg.ASSET_COST_ACCOUNT_CCID ,
1209 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1210 stg.CIP_COST_ACCOUNT_CCID ,
1211 stg.CIP_CLEARING_ACCOUNT_CCID ,
1212 stg.RESERVE_ACCOUNT_CCID ,
1213 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1214 stg.BONUS_RESERVE_ACCT_CCID ,
1215 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1216 stg.REVAL_AMORT_ACCOUNT_CCID ,
1217 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1218 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1219 stg.ALT_COST_ACCOUNT_CCID ,
1220 stg.WRITE_OFF_ACCOUNT_CCID ,
1221 decode(adj.source_dest_code,
1222 'SOURCE',
1223 decode(adj.adjustment_type,
1224 'RESERVE',
1225 decode(debit_credit_flag,
1226 'DR', adjustment_amount,
1227 -1 * adjustment_amount),
1228 'BONUS RESERVE',
1229 decode(debit_credit_flag,
1230 'DR', adjustment_amount,
1231 -1 * adjustment_amount),
1232 'REVAL RESERVE',
1233 decode(debit_credit_flag,
1234 'DR', adjustment_amount,
1235 -1 * adjustment_amount),
1236 decode(debit_credit_flag,
1237 'CR', adjustment_amount,
1238 -1 * adjustment_amount)),
1239 decode(adj.adjustment_type,
1240 'RESERVE',
1241 decode(debit_credit_flag,
1242 'CR', adjustment_amount,
1243 -1 * adjustment_amount),
1244 'BONUS RESERVE',
1245 decode(debit_credit_flag,
1246 'CR', adjustment_amount,
1247 -1 * adjustment_amount),
1248 'REVAL RESERVE',
1249 decode(debit_credit_flag,
1250 'CR', adjustment_amount,
1251 -1 * adjustment_amount),
1252 decode(debit_credit_flag,
1253 'DR', adjustment_amount,
1254 -1 * adjustment_amount))) ,
1255 adj.ADJUSTMENT_LINE_ID,
1256 adj.ADJUSTMENT_TYPE,
1257 adj.SOURCE_DEST_CODE,
1258 ai.PAYABLES_CODE_COMBINATION_ID,
1259 dh.CODE_COMBINATION_ID,
1260 stg.ASSET_CLEARING_ACCT,
1261 stg.ASSET_COST_ACCT,
1262 stg.BONUS_DEPRN_EXPENSE_ACCT,
1263 stg.BONUS_RESERVE_ACCT,
1264 stg.CIP_CLEARING_ACCT,
1265 stg.CIP_COST_ACCT,
1266 stg.DEPRN_RESERVE_ACCT,
1267 stg.REVAL_RESERVE_ACCT,
1268 stg.TRANSACTION_HEADER_ID
1269 from fa_xla_ext_lines_stg_gt stg,
1270 fa_mc_adjustments adj,
1271 fa_distribution_history dh,
1272 fa_locations loc,
1273 fa_lookups lu ,
1274 fa_mc_asset_invoices ai,
1275 fa_mc_book_controls bc ,
1276 gl_ledgers le
1277 WHERE adj.transaction_header_id = stg.transaction_header_id
1278 AND adj.book_type_code = stg.book_type_code
1279 AND adj.distribution_id = dh.distribution_id
1280 AND dh.location_id = loc.location_id
1281 -- AND dh.assigned_to = emp.employee_id(+)
1282 AND lu.lookup_type = 'JOURNAL ENTRIES'
1283 AND lu.lookup_code = adj.source_type_code || ' ' ||
1284 decode (adj.adjustment_type,
1285 'CIP COST', 'COST',
1286 adj.adjustment_type)
1287 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1288 AND nvl(adj.track_member_flag, 'N') = 'N'
1289 AND adj.adjustment_amount <> 0
1290 AND stg.event_type_code in ('SOURCE_LINE_TRANSFERS',
1291 'CIP_SOURCE_LINE_TRANSFERS',
1292 'RESERVE_TRANSFERS')
1293 AND adj.source_line_id = ai.source_line_id(+)
1294 AND bc.book_type_code = stg.book_type_code
1295 AND bc.set_of_books_id = le.ledger_id
1296 AND adj.set_of_books_id = bc.set_of_books_id
1297 AND adj.set_of_books_id = ai.set_of_books_id(+) ;
1298
1299 end if;
1300
1301
1302 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1303 fnd_log.string(G_LEVEL_PROCEDURE,
1304 G_MODULE_NAME||l_procedure_name,
1305 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1306 END IF;
1307
1308
1309
1310 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1311 fnd_log.string(G_LEVEL_PROCEDURE,
1312 G_MODULE_NAME||l_procedure_name||'.end',
1313 'End of procedure');
1314 END IF;
1315
1316 EXCEPTION
1317 WHEN others THEN
1318 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1319 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1320 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1321 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1322 END IF;
1323 raise;
1324
1325 end load_line_data_fin2;
1326
1327
1328
1329 /*======================================================================+
1330 | |
1331 | Private Function |
1332 | load_line_data_xfr |
1333 | |
1334 +======================================================================*/
1335
1336 PROCEDURE load_line_data_xfr IS
1337
1338 l_procedure_name varchar2(80) := 'load_line_data_xfr';
1339
1340 BEGIN
1341
1342 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1343 fnd_log.string(G_LEVEL_PROCEDURE,
1344 G_MODULE_NAME||l_procedure_name||'.begin',
1345 'Beginning of procedure');
1346 END IF;
1347
1348
1349 insert into fa_xla_ext_lines_b_gt (
1350 EVENT_ID ,
1351 LINE_NUMBER ,
1352 DISTRIBUTION_ID ,
1353 DISTRIBUTION_TYPE_CODE ,
1354 LEDGER_ID ,
1355 CURRENCY_CODE ,
1356 BOOK_TYPE_CODE ,
1357 GENERATED_CCID ,
1358 ASSET_ID ,
1359 ASSET_TYPE ,
1360 ASSET_COST_ACCOUNT_CCID ,
1361 ASSET_CLEARING_ACCOUNT_CCID ,
1362 CIP_COST_ACCOUNT_CCID ,
1363 CIP_CLEARING_ACCOUNT_CCID ,
1364 RESERVE_ACCOUNT_CCID ,
1365 DEPRN_EXPENSE_ACCOUNT_CCID ,
1366 BONUS_RESERVE_ACCT_CCID ,
1367 BONUS_EXPENSE_ACCOUNT_CCID ,
1368 REVAL_AMORT_ACCOUNT_CCID ,
1369 REVAL_RESERVE_ACCOUNT_CCID ,
1370 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1371 ALT_COST_ACCOUNT_CCID ,
1372 WRITE_OFF_ACCOUNT_CCID ,
1373 ENTERED_AMOUNT ,
1374 ADJUSTMENT_LINE_ID,
1375 ADJUSTMENT_TYPE,
1376 SOURCE_DEST_CODE,
1377 EXPENSE_ACCOUNT_CCID,
1378 ASSET_COST_ACCT,
1379 BONUS_DEPRN_EXPENSE_ACCT,
1380 BONUS_RESERVE_ACCT,
1381 CIP_COST_ACCT,
1382 DEPRN_RESERVE_ACCT,
1383 REVAL_RESERVE_ACCT,
1384 TRANSACTION_HEADER_ID )
1385 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1386 adj.adjustment_line_id ,
1387 adj.distribution_id ,
1388 stg.distribution_type_code ,
1389 stg.ledger_id ,
1390 stg.currency_code ,
1391 stg.book_type_code ,
1392 adj.code_combination_id ,
1393 adj.asset_id ,
1394 stg.asset_type ,
1395 stg.ASSET_COST_ACCOUNT_CCID ,
1396 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1397 stg.CIP_COST_ACCOUNT_CCID ,
1398 stg.CIP_CLEARING_ACCOUNT_CCID ,
1399 stg.RESERVE_ACCOUNT_CCID ,
1400 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1401 stg.BONUS_RESERVE_ACCT_CCID ,
1402 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1403 stg.REVAL_AMORT_ACCOUNT_CCID ,
1404 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1405 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1406 stg.ALT_COST_ACCOUNT_CCID ,
1407 stg.WRITE_OFF_ACCOUNT_CCID ,
1408 decode(adj.source_dest_code,
1409 'SOURCE',
1410 decode(adj.adjustment_type,
1411 'RESERVE',
1412 decode(debit_credit_flag,
1413 'DR', adjustment_amount,
1414 -1 * adjustment_amount),
1415 'BONUS RESERVE',
1416 decode(debit_credit_flag,
1417 'DR', adjustment_amount,
1418 -1 * adjustment_amount),
1419 'REVAL RESERVE',
1420 decode(debit_credit_flag,
1421 'DR', adjustment_amount,
1422 -1 * adjustment_amount),
1423 decode(debit_credit_flag,
1424 'CR', adjustment_amount,
1425 -1 * adjustment_amount)),
1426 decode(adj.adjustment_type,
1427 'RESERVE',
1428 decode(debit_credit_flag,
1429 'CR', adjustment_amount,
1430 -1 * adjustment_amount),
1431 'BONUS RESERVE',
1432 decode(debit_credit_flag,
1433 'CR', adjustment_amount,
1434 -1 * adjustment_amount),
1435 'REVAL RESERVE',
1436 decode(debit_credit_flag,
1437 'CR', adjustment_amount,
1438 -1 * adjustment_amount),
1439 decode(debit_credit_flag,
1440 'DR', adjustment_amount,
1441 -1 * adjustment_amount))) ,
1442 adj.ADJUSTMENT_LINE_ID,
1443 adj.ADJUSTMENT_TYPE,
1444 adj.SOURCE_DEST_CODE,
1445 dh.CODE_COMBINATION_ID,
1446 stg.ASSET_COST_ACCT,
1447 stg.BONUS_DEPRN_EXPENSE_ACCT,
1448 stg.BONUS_RESERVE_ACCT,
1449 stg.CIP_COST_ACCT,
1450 stg.DEPRN_RESERVE_ACCT,
1451 stg.REVAL_RESERVE_ACCT,
1452 stg.TRANSACTION_HEADER_ID
1453 from fa_xla_ext_lines_stg_gt stg,
1454 fa_adjustments adj,
1455 fa_distribution_history dh,
1456 fa_locations loc,
1457 fa_lookups lu
1458 WHERE adj.transaction_header_id = stg.transaction_header_id
1459 AND adj.book_type_code = stg.book_type_code
1460 AND adj.distribution_id = dh.distribution_id
1461 AND dh.location_id = loc.location_id
1462 -- AND dh.assigned_to = emp.employee_id(+)
1463 AND lu.lookup_type = 'JOURNAL ENTRIES'
1464 AND lu.lookup_code = adj.source_type_code || ' ' ||
1465 decode (adj.adjustment_type,
1466 'CIP COST', 'COST',
1467 adj.adjustment_type)
1468 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1469 AND nvl(adj.track_member_flag, 'N') = 'N'
1470 AND adj.adjustment_amount <> 0
1471 AND stg.event_type_code in ('TRANSFERS', 'CIP_TRANSFERS') ;
1472
1473 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1474 fnd_log.string(G_LEVEL_PROCEDURE,
1475 G_MODULE_NAME||l_procedure_name,
1476 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1477 END IF;
1478
1479
1480
1481 if (fa_xla_extract_util_pkg.G_alc_enabled) then
1482
1483
1484
1485 insert into fa_xla_ext_lines_b_gt (
1486 EVENT_ID ,
1487 LINE_NUMBER ,
1488 DISTRIBUTION_ID ,
1489 DISTRIBUTION_TYPE_CODE ,
1490 LEDGER_ID ,
1491 CURRENCY_CODE ,
1492 BOOK_TYPE_CODE ,
1493 GENERATED_CCID ,
1494 ASSET_ID ,
1495 ASSET_TYPE ,
1496 ASSET_COST_ACCOUNT_CCID ,
1497 ASSET_CLEARING_ACCOUNT_CCID ,
1498 CIP_COST_ACCOUNT_CCID ,
1499 CIP_CLEARING_ACCOUNT_CCID ,
1500 RESERVE_ACCOUNT_CCID ,
1501 DEPRN_EXPENSE_ACCOUNT_CCID ,
1502 BONUS_RESERVE_ACCT_CCID ,
1503 BONUS_EXPENSE_ACCOUNT_CCID ,
1504 REVAL_AMORT_ACCOUNT_CCID ,
1505 REVAL_RESERVE_ACCOUNT_CCID ,
1506 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1507 ALT_COST_ACCOUNT_CCID ,
1508 WRITE_OFF_ACCOUNT_CCID ,
1509 ENTERED_AMOUNT ,
1510 ADJUSTMENT_LINE_ID,
1511 ADJUSTMENT_TYPE,
1512 SOURCE_DEST_CODE,
1513 EXPENSE_ACCOUNT_CCID,
1514 ASSET_COST_ACCT,
1515 BONUS_DEPRN_EXPENSE_ACCT,
1516 BONUS_RESERVE_ACCT,
1517 CIP_COST_ACCT,
1518 DEPRN_RESERVE_ACCT,
1519 REVAL_RESERVE_ACCT,
1520 TRANSACTION_HEADER_ID )
1521 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1522 adj.adjustment_line_id ,
1523 adj.distribution_id ,
1524 stg.distribution_type_code ,
1525 bc.set_of_books_id ,
1526 le.currency_code ,
1527 stg.book_type_code ,
1528 adj.code_combination_id ,
1529 adj.asset_id ,
1530 stg.asset_type ,
1531 stg.ASSET_COST_ACCOUNT_CCID ,
1532 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1533 stg.CIP_COST_ACCOUNT_CCID ,
1534 stg.CIP_CLEARING_ACCOUNT_CCID ,
1535 stg.RESERVE_ACCOUNT_CCID ,
1536 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1537 stg.BONUS_RESERVE_ACCT_CCID ,
1538 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1539 stg.REVAL_AMORT_ACCOUNT_CCID ,
1540 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1541 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1542 stg.ALT_COST_ACCOUNT_CCID ,
1543 stg.WRITE_OFF_ACCOUNT_CCID ,
1544 decode(adj.source_dest_code,
1545 'SOURCE',
1546 decode(adj.adjustment_type,
1547 'RESERVE',
1548 decode(debit_credit_flag,
1549 'DR', adjustment_amount,
1550 -1 * adjustment_amount),
1551 'BONUS RESERVE',
1552 decode(debit_credit_flag,
1553 'DR', adjustment_amount,
1554 -1 * adjustment_amount),
1555 'REVAL RESERVE',
1556 decode(debit_credit_flag,
1557 'DR', adjustment_amount,
1558 -1 * adjustment_amount),
1559 decode(debit_credit_flag,
1560 'CR', adjustment_amount,
1561 -1 * adjustment_amount)),
1562 decode(adj.adjustment_type,
1563 'RESERVE',
1564 decode(debit_credit_flag,
1565 'CR', adjustment_amount,
1566 -1 * adjustment_amount),
1567 'BONUS RESERVE',
1568 decode(debit_credit_flag,
1569 'CR', adjustment_amount,
1570 -1 * adjustment_amount),
1571 'REVAL RESERVE',
1572 decode(debit_credit_flag,
1573 'CR', adjustment_amount,
1574 -1 * adjustment_amount),
1575 decode(debit_credit_flag,
1576 'DR', adjustment_amount,
1577 -1 * adjustment_amount))) ,
1578 adj.ADJUSTMENT_LINE_ID,
1579 adj.ADJUSTMENT_TYPE,
1580 adj.SOURCE_DEST_CODE,
1581 dh.CODE_COMBINATION_ID,
1582 stg.ASSET_COST_ACCT,
1583 stg.BONUS_DEPRN_EXPENSE_ACCT,
1584 stg.BONUS_RESERVE_ACCT,
1585 stg.CIP_COST_ACCT,
1586 stg.DEPRN_RESERVE_ACCT,
1587 stg.REVAL_RESERVE_ACCT,
1588 stg.TRANSACTION_HEADER_ID
1589 from fa_xla_ext_lines_stg_gt stg,
1590 fa_mc_adjustments adj,
1591 fa_distribution_history dh,
1592 fa_locations loc,
1593 fa_lookups lu ,
1594 fa_mc_book_controls bc ,
1595 gl_ledgers le
1596 WHERE adj.transaction_header_id = stg.transaction_header_id
1597 AND adj.book_type_code = stg.book_type_code
1598 AND adj.distribution_id = dh.distribution_id
1599 AND dh.location_id = loc.location_id
1600 -- AND dh.assigned_to = emp.employee_id(+)
1601 AND lu.lookup_type = 'JOURNAL ENTRIES'
1602 AND lu.lookup_code = adj.source_type_code || ' ' ||
1603 decode (adj.adjustment_type,
1604 'CIP COST', 'COST',
1605 adj.adjustment_type)
1606 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1607 AND nvl(adj.track_member_flag, 'N') = 'N'
1608 AND adj.adjustment_amount <> 0
1609 AND stg.event_type_code in ('TRANSFERS', 'CIP_TRANSFERS')
1610 AND bc.book_type_code = stg.book_type_code
1611 AND bc.set_of_books_id = le.ledger_id
1612 AND adj.set_of_books_id = bc.set_of_books_id ;
1613
1614 end if;
1615
1616
1617 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1618 fnd_log.string(G_LEVEL_PROCEDURE,
1619 G_MODULE_NAME||l_procedure_name,
1620 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1621 END IF;
1622
1623
1624
1625 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1626 fnd_log.string(G_LEVEL_PROCEDURE,
1627 G_MODULE_NAME||l_procedure_name||'.end',
1628 'End of procedure');
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN others THEN
1633 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1634 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1635 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1636 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1637 END IF;
1638 raise;
1639
1640 end load_line_data_xfr;
1641
1642
1643
1644 /*======================================================================+
1645 | |
1646 | Private Function |
1647 | load_line_data_dist |
1648 | |
1649 +======================================================================*/
1650
1651 PROCEDURE load_line_data_dist IS
1652
1653 l_procedure_name varchar2(80) := 'load_line_data_dist';
1654
1655 BEGIN
1656
1657 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1658 fnd_log.string(G_LEVEL_PROCEDURE,
1659 G_MODULE_NAME||l_procedure_name||'.begin',
1660 'Beginning of procedure');
1661 END IF;
1662
1663
1664 insert into fa_xla_ext_lines_b_gt (
1665 EVENT_ID ,
1666 LINE_NUMBER ,
1667 DISTRIBUTION_ID ,
1668 DISTRIBUTION_TYPE_CODE ,
1669 LEDGER_ID ,
1670 CURRENCY_CODE ,
1671 BOOK_TYPE_CODE ,
1672 GENERATED_CCID ,
1673 ASSET_ID ,
1674 ASSET_TYPE ,
1675 ASSET_COST_ACCOUNT_CCID ,
1676 ASSET_CLEARING_ACCOUNT_CCID ,
1677 CIP_COST_ACCOUNT_CCID ,
1678 CIP_CLEARING_ACCOUNT_CCID ,
1679 RESERVE_ACCOUNT_CCID ,
1680 DEPRN_EXPENSE_ACCOUNT_CCID ,
1681 BONUS_RESERVE_ACCT_CCID ,
1682 BONUS_EXPENSE_ACCOUNT_CCID ,
1683 REVAL_AMORT_ACCOUNT_CCID ,
1684 REVAL_RESERVE_ACCOUNT_CCID ,
1685 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1686 ALT_COST_ACCOUNT_CCID ,
1687 WRITE_OFF_ACCOUNT_CCID ,
1688 ENTERED_AMOUNT ,
1689 ADJUSTMENT_LINE_ID,
1690 ADJUSTMENT_TYPE,
1691 SOURCE_DEST_CODE,
1692 ASSET_COST_ACCT,
1693 BONUS_DEPRN_EXPENSE_ACCT,
1694 BONUS_RESERVE_ACCT,
1695 CIP_COST_ACCT,
1696 DEPRN_RESERVE_ACCT,
1697 REVAL_RESERVE_ACCT,
1698 EXPENSE_ACCOUNT_CCID,
1699 TRANSACTION_HEADER_ID )
1700 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1701 adj.adjustment_line_id ,
1702 adj.distribution_id ,
1703 stg.distribution_type_code ,
1704 stg.ledger_id ,
1705 stg.currency_code ,
1706 stg.book_type_code ,
1707 adj.code_combination_id ,
1708 adj.asset_id ,
1709 stg.asset_type ,
1710 cb.ASSET_COST_ACCOUNT_CCID ,
1711 cb.ASSET_CLEARING_ACCOUNT_CCID ,
1712 cb.WIP_COST_ACCOUNT_CCID ,
1713 cb.WIP_CLEARING_ACCOUNT_CCID ,
1714 cb.RESERVE_ACCOUNT_CCID ,
1715 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
1716 cb.BONUS_RESERVE_ACCT_CCID ,
1717 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
1718 cb.REVAL_AMORT_ACCOUNT_CCID ,
1719 cb.REVAL_RESERVE_ACCOUNT_CCID ,
1720 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1721 cb.ALT_COST_ACCOUNT_CCID ,
1722 cb.WRITE_OFF_ACCOUNT_CCID ,
1723 decode(adj.adjustment_type,
1724 'RESERVE',
1725 decode(debit_credit_flag,
1726 'DR', adjustment_amount,
1727 -1 * adjustment_amount),
1728 'BONUS RESERVE',
1729 decode(debit_credit_flag,
1730 'DR', adjustment_amount,
1731 -1 * adjustment_amount),
1732 'REVAL RESERVE',
1733 decode(debit_credit_flag,
1734 'DR', adjustment_amount,
1735 -1 * adjustment_amount),
1736 decode(debit_credit_flag,
1737 'CR', adjustment_amount,
1738 -1 * adjustment_amount)) ,
1739 adj.ADJUSTMENT_LINE_ID,
1740 adj.ADJUSTMENT_TYPE,
1741 adj.SOURCE_DEST_CODE,
1742 cb.ASSET_COST_ACCT,
1743 cb.BONUS_DEPRN_EXPENSE_ACCT,
1744 cb.BONUS_DEPRN_RESERVE_ACCT,
1745 cb.CIP_COST_ACCT,
1746 cb.DEPRN_RESERVE_ACCT,
1747 cb.REVAL_RESERVE_ACCT,
1748 dh.CODE_COMBINATION_ID,
1749 stg.TRANSACTION_HEADER_ID
1750 from fa_xla_ext_lines_stg_gt stg,
1751 fa_adjustments adj,
1752 fa_distribution_history dh,
1753 fa_locations loc,
1754 fa_lookups lu ,
1755 fa_asset_history ah,
1756 fa_category_books cb
1757 WHERE adj.transaction_header_id = stg.transaction_header_id
1758 AND adj.book_type_code = stg.book_type_code
1759 AND adj.distribution_id = dh.distribution_id
1760 AND dh.location_id = loc.location_id
1761 -- AND dh.assigned_to = emp.employee_id(+)
1762 AND lu.lookup_type = 'JOURNAL ENTRIES'
1763 AND lu.lookup_code = adj.source_type_code || ' ' ||
1764 decode (adj.adjustment_type,
1765 'CIP COST', 'COST',
1766 adj.adjustment_type)
1767 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1768 AND nvl(adj.track_member_flag, 'N') = 'N'
1769 AND adj.adjustment_amount <> 0
1770 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
1771 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
1772 AND adj.asset_id = ah.asset_id
1773 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
1774 AND cb.category_id = ah.category_id
1775 AND cb.book_type_code = adj.book_type_code
1776 AND adj.source_dest_code = 'SOURCE' ;
1777
1778 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1779 fnd_log.string(G_LEVEL_PROCEDURE,
1780 G_MODULE_NAME||l_procedure_name,
1781 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1782 END IF;
1783
1784
1785
1786 if (fa_xla_extract_util_pkg.G_alc_enabled) then
1787
1788
1789
1790 insert into fa_xla_ext_lines_b_gt (
1791 EVENT_ID ,
1792 LINE_NUMBER ,
1793 DISTRIBUTION_ID ,
1794 DISTRIBUTION_TYPE_CODE ,
1795 LEDGER_ID ,
1796 CURRENCY_CODE ,
1797 BOOK_TYPE_CODE ,
1798 GENERATED_CCID ,
1799 ASSET_ID ,
1800 ASSET_TYPE ,
1801 ASSET_COST_ACCOUNT_CCID ,
1802 ASSET_CLEARING_ACCOUNT_CCID ,
1803 CIP_COST_ACCOUNT_CCID ,
1804 CIP_CLEARING_ACCOUNT_CCID ,
1805 RESERVE_ACCOUNT_CCID ,
1806 DEPRN_EXPENSE_ACCOUNT_CCID ,
1807 BONUS_RESERVE_ACCT_CCID ,
1808 BONUS_EXPENSE_ACCOUNT_CCID ,
1809 REVAL_AMORT_ACCOUNT_CCID ,
1810 REVAL_RESERVE_ACCOUNT_CCID ,
1811 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1812 ALT_COST_ACCOUNT_CCID ,
1813 WRITE_OFF_ACCOUNT_CCID ,
1814 ENTERED_AMOUNT ,
1815 ADJUSTMENT_LINE_ID,
1816 ADJUSTMENT_TYPE,
1817 SOURCE_DEST_CODE,
1818 ASSET_COST_ACCT,
1819 BONUS_DEPRN_EXPENSE_ACCT,
1820 BONUS_RESERVE_ACCT,
1821 CIP_COST_ACCT,
1822 DEPRN_RESERVE_ACCT,
1823 REVAL_RESERVE_ACCT,
1824 EXPENSE_ACCOUNT_CCID,
1825 TRANSACTION_HEADER_ID )
1826 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1827 adj.adjustment_line_id ,
1828 adj.distribution_id ,
1829 stg.distribution_type_code ,
1830 bc.set_of_books_id ,
1831 le.currency_code ,
1832 stg.book_type_code ,
1833 adj.code_combination_id ,
1834 adj.asset_id ,
1835 stg.asset_type ,
1836 cb.ASSET_COST_ACCOUNT_CCID ,
1837 cb.ASSET_CLEARING_ACCOUNT_CCID ,
1838 cb.WIP_COST_ACCOUNT_CCID ,
1839 cb.WIP_CLEARING_ACCOUNT_CCID ,
1840 cb.RESERVE_ACCOUNT_CCID ,
1841 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
1842 cb.BONUS_RESERVE_ACCT_CCID ,
1843 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
1844 cb.REVAL_AMORT_ACCOUNT_CCID ,
1845 cb.REVAL_RESERVE_ACCOUNT_CCID ,
1846 cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1847 cb.ALT_COST_ACCOUNT_CCID ,
1848 cb.WRITE_OFF_ACCOUNT_CCID ,
1849 decode(adj.adjustment_type,
1850 'RESERVE',
1851 decode(debit_credit_flag,
1852 'DR', adjustment_amount,
1853 -1 * adjustment_amount),
1854 'BONUS RESERVE',
1855 decode(debit_credit_flag,
1856 'DR', adjustment_amount,
1857 -1 * adjustment_amount),
1858 'REVAL RESERVE',
1859 decode(debit_credit_flag,
1860 'DR', adjustment_amount,
1861 -1 * adjustment_amount),
1862 decode(debit_credit_flag,
1863 'CR', adjustment_amount,
1864 -1 * adjustment_amount)) ,
1865 adj.ADJUSTMENT_LINE_ID,
1866 adj.ADJUSTMENT_TYPE,
1867 adj.SOURCE_DEST_CODE,
1868 cb.ASSET_COST_ACCT,
1869 cb.BONUS_DEPRN_EXPENSE_ACCT,
1870 cb.BONUS_DEPRN_RESERVE_ACCT,
1871 cb.CIP_COST_ACCT,
1872 cb.DEPRN_RESERVE_ACCT,
1873 cb.REVAL_RESERVE_ACCT,
1874 dh.CODE_COMBINATION_ID,
1875 stg.TRANSACTION_HEADER_ID
1876 from fa_xla_ext_lines_stg_gt stg,
1877 fa_mc_adjustments adj,
1878 fa_distribution_history dh,
1879 fa_locations loc,
1880 fa_lookups lu ,
1881 fa_asset_history ah,
1882 fa_category_books cb ,
1883 fa_mc_book_controls bc ,
1884 gl_ledgers le
1885 WHERE adj.transaction_header_id = stg.transaction_header_id
1886 AND adj.book_type_code = stg.book_type_code
1887 AND adj.distribution_id = dh.distribution_id
1888 AND dh.location_id = loc.location_id
1889 -- AND dh.assigned_to = emp.employee_id(+)
1890 AND lu.lookup_type = 'JOURNAL ENTRIES'
1891 AND lu.lookup_code = adj.source_type_code || ' ' ||
1892 decode (adj.adjustment_type,
1893 'CIP COST', 'COST',
1894 adj.adjustment_type)
1895 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
1896 AND nvl(adj.track_member_flag, 'N') = 'N'
1897 AND adj.adjustment_amount <> 0
1898 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
1899 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
1900 AND adj.asset_id = ah.asset_id
1901 AND adj.transaction_header_id = ah.transaction_header_id_out -- terminated row
1902 AND cb.category_id = ah.category_id
1903 AND cb.book_type_code = adj.book_type_code
1904 AND adj.source_dest_code = 'SOURCE'
1905 AND bc.book_type_code = stg.book_type_code
1906 AND bc.set_of_books_id = le.ledger_id
1907 AND adj.set_of_books_id = bc.set_of_books_id ;
1908
1909 end if;
1910
1911
1912 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1913 fnd_log.string(G_LEVEL_PROCEDURE,
1914 G_MODULE_NAME||l_procedure_name,
1915 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
1916 END IF;
1917
1918
1919 insert into fa_xla_ext_lines_b_gt (
1920 EVENT_ID ,
1921 LINE_NUMBER ,
1922 DISTRIBUTION_ID ,
1923 DISTRIBUTION_TYPE_CODE ,
1924 LEDGER_ID ,
1925 CURRENCY_CODE ,
1926 BOOK_TYPE_CODE ,
1927 GENERATED_CCID ,
1928 ASSET_ID ,
1929 ASSET_TYPE ,
1930 ASSET_COST_ACCOUNT_CCID ,
1931 ASSET_CLEARING_ACCOUNT_CCID ,
1932 CIP_COST_ACCOUNT_CCID ,
1933 CIP_CLEARING_ACCOUNT_CCID ,
1934 RESERVE_ACCOUNT_CCID ,
1935 DEPRN_EXPENSE_ACCOUNT_CCID ,
1936 BONUS_RESERVE_ACCT_CCID ,
1937 BONUS_EXPENSE_ACCOUNT_CCID ,
1938 REVAL_AMORT_ACCOUNT_CCID ,
1939 REVAL_RESERVE_ACCOUNT_CCID ,
1940 UNPLAN_EXPENSE_ACCOUNT_CCID ,
1941 ALT_COST_ACCOUNT_CCID ,
1942 WRITE_OFF_ACCOUNT_CCID ,
1943 ENTERED_AMOUNT ,
1944 ADJUSTMENT_LINE_ID,
1945 ADJUSTMENT_TYPE,
1946 SOURCE_DEST_CODE,
1947 EXPENSE_ACCOUNT_CCID,
1948 ASSET_COST_ACCT,
1949 BONUS_DEPRN_EXPENSE_ACCT,
1950 BONUS_RESERVE_ACCT,
1951 CIP_COST_ACCT,
1952 DEPRN_RESERVE_ACCT,
1953 REVAL_RESERVE_ACCT,
1954 TRANSACTION_HEADER_ID )
1955 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
1956 adj.adjustment_line_id ,
1957 adj.distribution_id ,
1958 stg.distribution_type_code ,
1959 stg.ledger_id ,
1960 stg.currency_code ,
1961 stg.book_type_code ,
1962 adj.code_combination_id ,
1963 adj.asset_id ,
1964 stg.asset_type ,
1965 stg.ASSET_COST_ACCOUNT_CCID ,
1966 stg.ASSET_CLEARING_ACCOUNT_CCID ,
1967 stg.CIP_COST_ACCOUNT_CCID ,
1968 stg.CIP_CLEARING_ACCOUNT_CCID ,
1969 stg.RESERVE_ACCOUNT_CCID ,
1970 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
1971 stg.BONUS_RESERVE_ACCT_CCID ,
1972 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
1973 stg.REVAL_AMORT_ACCOUNT_CCID ,
1974 stg.REVAL_RESERVE_ACCOUNT_CCID ,
1975 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
1976 stg.ALT_COST_ACCOUNT_CCID ,
1977 stg.WRITE_OFF_ACCOUNT_CCID ,
1978 decode(adj.adjustment_type,
1979 'RESERVE',
1980 decode(debit_credit_flag,
1981 'CR', adjustment_amount,
1982 -1 * adjustment_amount),
1983 'BONUS RESERVE',
1984 decode(debit_credit_flag,
1985 'CR', adjustment_amount,
1986 -1 * adjustment_amount),
1987 'REVAL RESERVE',
1988 decode(debit_credit_flag,
1989 'CR', adjustment_amount,
1990 -1 * adjustment_amount),
1991 decode(debit_credit_flag,
1992 'DR', adjustment_amount,
1993 -1 * adjustment_amount)) ,
1994 adj.ADJUSTMENT_LINE_ID,
1995 adj.ADJUSTMENT_TYPE,
1996 adj.SOURCE_DEST_CODE,
1997 dh.CODE_COMBINATION_ID,
1998 stg.ASSET_COST_ACCT,
1999 stg.BONUS_DEPRN_EXPENSE_ACCT,
2000 stg.BONUS_RESERVE_ACCT,
2001 stg.CIP_COST_ACCT,
2002 stg.DEPRN_RESERVE_ACCT,
2003 stg.REVAL_RESERVE_ACCT,
2004 stg.TRANSACTION_HEADER_ID
2005 from fa_xla_ext_lines_stg_gt stg,
2006 fa_adjustments adj,
2007 fa_distribution_history dh,
2008 fa_locations loc,
2009 fa_lookups lu
2010 WHERE adj.transaction_header_id = stg.transaction_header_id
2011 AND adj.book_type_code = stg.book_type_code
2012 AND adj.distribution_id = dh.distribution_id
2013 AND dh.location_id = loc.location_id
2014 -- AND dh.assigned_to = emp.employee_id(+)
2015 AND lu.lookup_type = 'JOURNAL ENTRIES'
2016 AND lu.lookup_code = adj.source_type_code || ' ' ||
2017 decode (adj.adjustment_type,
2018 'CIP COST', 'COST',
2019 adj.adjustment_type)
2020 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2021 AND nvl(adj.track_member_flag, 'N') = 'N'
2022 AND adj.adjustment_amount <> 0
2023 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
2024 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
2025 AND adj.source_dest_code = 'DEST' ;
2026
2027 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2028 fnd_log.string(G_LEVEL_PROCEDURE,
2029 G_MODULE_NAME||l_procedure_name,
2030 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2031 END IF;
2032
2033
2034
2035 if (fa_xla_extract_util_pkg.G_alc_enabled) then
2036
2037
2038
2039 insert into fa_xla_ext_lines_b_gt (
2040 EVENT_ID ,
2041 LINE_NUMBER ,
2042 DISTRIBUTION_ID ,
2043 DISTRIBUTION_TYPE_CODE ,
2044 LEDGER_ID ,
2045 CURRENCY_CODE ,
2046 BOOK_TYPE_CODE ,
2047 GENERATED_CCID ,
2048 ASSET_ID ,
2049 ASSET_TYPE ,
2050 ASSET_COST_ACCOUNT_CCID ,
2051 ASSET_CLEARING_ACCOUNT_CCID ,
2052 CIP_COST_ACCOUNT_CCID ,
2053 CIP_CLEARING_ACCOUNT_CCID ,
2054 RESERVE_ACCOUNT_CCID ,
2055 DEPRN_EXPENSE_ACCOUNT_CCID ,
2056 BONUS_RESERVE_ACCT_CCID ,
2057 BONUS_EXPENSE_ACCOUNT_CCID ,
2058 REVAL_AMORT_ACCOUNT_CCID ,
2059 REVAL_RESERVE_ACCOUNT_CCID ,
2060 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2061 ALT_COST_ACCOUNT_CCID ,
2062 WRITE_OFF_ACCOUNT_CCID ,
2063 ENTERED_AMOUNT ,
2064 ADJUSTMENT_LINE_ID,
2065 ADJUSTMENT_TYPE,
2066 SOURCE_DEST_CODE,
2067 EXPENSE_ACCOUNT_CCID,
2068 ASSET_COST_ACCT,
2069 BONUS_DEPRN_EXPENSE_ACCT,
2070 BONUS_RESERVE_ACCT,
2071 CIP_COST_ACCT,
2072 DEPRN_RESERVE_ACCT,
2073 REVAL_RESERVE_ACCT,
2074 TRANSACTION_HEADER_ID )
2075 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2076 adj.adjustment_line_id ,
2077 adj.distribution_id ,
2078 stg.distribution_type_code ,
2079 bc.set_of_books_id ,
2080 le.currency_code ,
2081 stg.book_type_code ,
2082 adj.code_combination_id ,
2083 adj.asset_id ,
2084 stg.asset_type ,
2085 stg.ASSET_COST_ACCOUNT_CCID ,
2086 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2087 stg.CIP_COST_ACCOUNT_CCID ,
2088 stg.CIP_CLEARING_ACCOUNT_CCID ,
2089 stg.RESERVE_ACCOUNT_CCID ,
2090 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2091 stg.BONUS_RESERVE_ACCT_CCID ,
2092 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2093 stg.REVAL_AMORT_ACCOUNT_CCID ,
2094 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2095 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2096 stg.ALT_COST_ACCOUNT_CCID ,
2097 stg.WRITE_OFF_ACCOUNT_CCID ,
2098 decode(adj.adjustment_type,
2099 'RESERVE',
2100 decode(debit_credit_flag,
2101 'CR', adjustment_amount,
2102 -1 * adjustment_amount),
2103 'BONUS RESERVE',
2104 decode(debit_credit_flag,
2105 'CR', adjustment_amount,
2106 -1 * adjustment_amount),
2107 'REVAL RESERVE',
2108 decode(debit_credit_flag,
2109 'CR', adjustment_amount,
2110 -1 * adjustment_amount),
2111 decode(debit_credit_flag,
2112 'DR', adjustment_amount,
2113 -1 * adjustment_amount)) ,
2114 adj.ADJUSTMENT_LINE_ID,
2115 adj.ADJUSTMENT_TYPE,
2116 adj.SOURCE_DEST_CODE,
2117 dh.CODE_COMBINATION_ID,
2118 stg.ASSET_COST_ACCT,
2119 stg.BONUS_DEPRN_EXPENSE_ACCT,
2120 stg.BONUS_RESERVE_ACCT,
2121 stg.CIP_COST_ACCT,
2122 stg.DEPRN_RESERVE_ACCT,
2123 stg.REVAL_RESERVE_ACCT,
2124 stg.TRANSACTION_HEADER_ID
2125 from fa_xla_ext_lines_stg_gt stg,
2126 fa_mc_adjustments adj,
2127 fa_distribution_history dh,
2128 fa_locations loc,
2129 fa_lookups lu ,
2130 fa_mc_book_controls bc ,
2131 gl_ledgers le
2132 WHERE adj.transaction_header_id = stg.transaction_header_id
2133 AND adj.book_type_code = stg.book_type_code
2134 AND adj.distribution_id = dh.distribution_id
2135 AND dh.location_id = loc.location_id
2136 -- AND dh.assigned_to = emp.employee_id(+)
2137 AND lu.lookup_type = 'JOURNAL ENTRIES'
2138 AND lu.lookup_code = adj.source_type_code || ' ' ||
2139 decode (adj.adjustment_type,
2140 'CIP COST', 'COST',
2141 adj.adjustment_type)
2142 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2143 AND nvl(adj.track_member_flag, 'N') = 'N'
2144 AND adj.adjustment_amount <> 0
2145 AND stg.event_type_code in ('CATEGORY_RECLASS', 'CIP_CATEGORY_RECLASS',
2146 'UNIT_ADJUSTMENTS', 'CIP_UNIT_ADJUSTMENTS')
2147 AND adj.source_dest_code = 'DEST'
2148 AND bc.book_type_code = stg.book_type_code
2149 AND bc.set_of_books_id = le.ledger_id
2150 AND adj.set_of_books_id = bc.set_of_books_id ;
2151
2152 end if;
2153
2154
2155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2156 fnd_log.string(G_LEVEL_PROCEDURE,
2157 G_MODULE_NAME||l_procedure_name,
2158 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2159 END IF;
2160
2161
2162
2163 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2164 fnd_log.string(G_LEVEL_PROCEDURE,
2165 G_MODULE_NAME||l_procedure_name||'.end',
2166 'End of procedure');
2167 END IF;
2168
2169 EXCEPTION
2170 WHEN others THEN
2171 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2172 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2173 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2174 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2175 END IF;
2176 raise;
2177
2178 end load_line_data_dist;
2179
2180
2181
2182 /*======================================================================+
2183 | |
2184 | Private Function |
2185 | load_line_data_ret |
2186 | |
2187 +======================================================================*/
2188
2189 PROCEDURE load_line_data_ret IS
2190
2191 l_procedure_name varchar2(80) := 'load_line_data_ret';
2192
2193 BEGIN
2194
2195 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2196 fnd_log.string(G_LEVEL_PROCEDURE,
2197 G_MODULE_NAME||l_procedure_name||'.begin',
2198 'Beginning of procedure');
2199 END IF;
2200
2201
2202 insert into fa_xla_ext_lines_b_gt (
2203 EVENT_ID ,
2204 LINE_NUMBER ,
2205 DISTRIBUTION_ID ,
2206 DISTRIBUTION_TYPE_CODE ,
2207 LEDGER_ID ,
2208 CURRENCY_CODE ,
2209 BOOK_TYPE_CODE ,
2210 GENERATED_CCID ,
2211 ASSET_ID ,
2212 ASSET_TYPE ,
2213 ASSET_COST_ACCOUNT_CCID ,
2214 ASSET_CLEARING_ACCOUNT_CCID ,
2215 CIP_COST_ACCOUNT_CCID ,
2216 CIP_CLEARING_ACCOUNT_CCID ,
2217 RESERVE_ACCOUNT_CCID ,
2218 DEPRN_EXPENSE_ACCOUNT_CCID ,
2219 BONUS_RESERVE_ACCT_CCID ,
2220 BONUS_EXPENSE_ACCOUNT_CCID ,
2221 REVAL_AMORT_ACCOUNT_CCID ,
2222 REVAL_RESERVE_ACCOUNT_CCID ,
2223 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2224 ALT_COST_ACCOUNT_CCID ,
2225 WRITE_OFF_ACCOUNT_CCID ,
2226 ENTERED_AMOUNT ,
2227 ADJUSTMENT_LINE_ID,
2228 ADJUSTMENT_TYPE,
2229 EXPENSE_ACCOUNT_CCID,
2230 GAIN_LOSS_AMOUNT,
2231 ASSET_COST_ACCT,
2232 BONUS_DEPRN_EXPENSE_ACCT,
2233 BONUS_RESERVE_ACCT,
2234 CIP_COST_ACCT,
2235 DEPRN_RESERVE_ACCT,
2236 REVAL_RESERVE_ACCT,
2237 TRANSACTION_HEADER_ID )
2238 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2239 adj.adjustment_line_id ,
2240 adj.distribution_id ,
2241 stg.distribution_type_code ,
2242 stg.ledger_id ,
2243 stg.currency_code ,
2244 stg.book_type_code ,
2245 adj.code_combination_id ,
2246 adj.asset_id ,
2247 stg.asset_type ,
2248 stg.ASSET_COST_ACCOUNT_CCID ,
2249 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2250 stg.CIP_COST_ACCOUNT_CCID ,
2251 stg.CIP_CLEARING_ACCOUNT_CCID ,
2252 stg.RESERVE_ACCOUNT_CCID ,
2253 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2254 stg.BONUS_RESERVE_ACCT_CCID ,
2255 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2256 stg.REVAL_AMORT_ACCOUNT_CCID ,
2257 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2258 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2259 stg.ALT_COST_ACCOUNT_CCID ,
2260 stg.WRITE_OFF_ACCOUNT_CCID ,
2261 decode(adj.adjustment_type,
2262 'RESERVE',
2263 decode(debit_credit_flag,
2264 'DR', adjustment_amount,
2265 -1 * adjustment_amount),
2266 'BONUS RESERVE',
2267 decode(debit_credit_flag,
2268 'DR', adjustment_amount,
2269 -1 * adjustment_amount),
2270 'REVAL RESERVE',
2271 decode(debit_credit_flag,
2272 'DR', adjustment_amount,
2273 -1 * adjustment_amount),
2274 'NBV RETIRED',
2275 decode(debit_credit_flag,
2276 'DR', adjustment_amount,
2277 -1 * adjustment_amount),
2278 'PROCEEDS CLR',
2279 decode(debit_credit_flag,
2280 'DR', adjustment_amount,
2281 -1 * adjustment_amount),
2282 'REMOVALCOST',
2283 decode(debit_credit_flag,
2284 'DR', adjustment_amount,
2285 -1 * adjustment_amount),
2286 decode(debit_credit_flag,
2287 'CR', adjustment_amount,
2288 -1 * adjustment_amount)) ,
2289 adj.ADJUSTMENT_LINE_ID,
2290 adj.ADJUSTMENT_TYPE,
2291 dh.CODE_COMBINATION_ID,
2292 ret.GAIN_LOSS_AMOUNT,
2293 stg.ASSET_COST_ACCT,
2294 stg.BONUS_DEPRN_EXPENSE_ACCT,
2295 stg.BONUS_RESERVE_ACCT,
2296 stg.CIP_COST_ACCT,
2297 stg.DEPRN_RESERVE_ACCT,
2298 stg.REVAL_RESERVE_ACCT,
2299 stg.TRANSACTION_HEADER_ID
2300 from fa_xla_ext_lines_stg_gt stg,
2301 fa_adjustments adj,
2302 fa_distribution_history dh,
2303 fa_locations loc,
2304 fa_lookups lu ,
2305 fa_retirements ret
2306 WHERE adj.transaction_header_id = stg.transaction_header_id
2307 AND adj.book_type_code = stg.book_type_code
2308 AND adj.distribution_id = dh.distribution_id
2309 AND dh.location_id = loc.location_id
2310 -- AND dh.assigned_to = emp.employee_id(+)
2311 AND lu.lookup_type = 'JOURNAL ENTRIES'
2312 AND lu.lookup_code = adj.source_type_code || ' ' ||
2313 decode (adj.adjustment_type,
2314 'CIP COST', 'COST',
2315 adj.adjustment_type)
2316 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2317 AND nvl(adj.track_member_flag, 'N') = 'N'
2318 AND adj.adjustment_amount <> 0
2319 AND stg.event_type_code in ('RETIREMENTS', 'CIP_RETIREMENTS')
2320 AND ret.transaction_header_id_in = stg.member_transaction_header_id ;
2321
2322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2323 fnd_log.string(G_LEVEL_PROCEDURE,
2324 G_MODULE_NAME||l_procedure_name,
2325 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2326 END IF;
2327
2328
2329
2330 if (fa_xla_extract_util_pkg.G_alc_enabled) then
2331
2332
2333
2334 insert into fa_xla_ext_lines_b_gt (
2335 EVENT_ID ,
2336 LINE_NUMBER ,
2337 DISTRIBUTION_ID ,
2338 DISTRIBUTION_TYPE_CODE ,
2339 LEDGER_ID ,
2340 CURRENCY_CODE ,
2341 BOOK_TYPE_CODE ,
2342 GENERATED_CCID ,
2343 ASSET_ID ,
2344 ASSET_TYPE ,
2345 ASSET_COST_ACCOUNT_CCID ,
2346 ASSET_CLEARING_ACCOUNT_CCID ,
2347 CIP_COST_ACCOUNT_CCID ,
2348 CIP_CLEARING_ACCOUNT_CCID ,
2349 RESERVE_ACCOUNT_CCID ,
2350 DEPRN_EXPENSE_ACCOUNT_CCID ,
2351 BONUS_RESERVE_ACCT_CCID ,
2352 BONUS_EXPENSE_ACCOUNT_CCID ,
2353 REVAL_AMORT_ACCOUNT_CCID ,
2354 REVAL_RESERVE_ACCOUNT_CCID ,
2355 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2356 ALT_COST_ACCOUNT_CCID ,
2357 WRITE_OFF_ACCOUNT_CCID ,
2358 ENTERED_AMOUNT ,
2359 ADJUSTMENT_LINE_ID,
2360 ADJUSTMENT_TYPE,
2361 EXPENSE_ACCOUNT_CCID,
2362 GAIN_LOSS_AMOUNT,
2363 ASSET_COST_ACCT,
2364 BONUS_DEPRN_EXPENSE_ACCT,
2365 BONUS_RESERVE_ACCT,
2366 CIP_COST_ACCT,
2367 DEPRN_RESERVE_ACCT,
2368 REVAL_RESERVE_ACCT,
2369 TRANSACTION_HEADER_ID )
2370 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2371 adj.adjustment_line_id ,
2372 adj.distribution_id ,
2373 stg.distribution_type_code ,
2374 bc.set_of_books_id ,
2375 le.currency_code ,
2376 stg.book_type_code ,
2377 adj.code_combination_id ,
2378 adj.asset_id ,
2379 stg.asset_type ,
2380 stg.ASSET_COST_ACCOUNT_CCID ,
2381 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2382 stg.CIP_COST_ACCOUNT_CCID ,
2383 stg.CIP_CLEARING_ACCOUNT_CCID ,
2384 stg.RESERVE_ACCOUNT_CCID ,
2385 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2386 stg.BONUS_RESERVE_ACCT_CCID ,
2387 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2388 stg.REVAL_AMORT_ACCOUNT_CCID ,
2389 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2390 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2391 stg.ALT_COST_ACCOUNT_CCID ,
2392 stg.WRITE_OFF_ACCOUNT_CCID ,
2393 decode(adj.adjustment_type,
2394 'RESERVE',
2395 decode(debit_credit_flag,
2396 'DR', adjustment_amount,
2397 -1 * adjustment_amount),
2398 'BONUS RESERVE',
2399 decode(debit_credit_flag,
2400 'DR', adjustment_amount,
2401 -1 * adjustment_amount),
2402 'REVAL RESERVE',
2403 decode(debit_credit_flag,
2404 'DR', adjustment_amount,
2405 -1 * adjustment_amount),
2406 'NBV RETIRED',
2407 decode(debit_credit_flag,
2408 'DR', adjustment_amount,
2409 -1 * adjustment_amount),
2410 'PROCEEDS CLR',
2411 decode(debit_credit_flag,
2412 'DR', adjustment_amount,
2413 -1 * adjustment_amount),
2414 'REMOVALCOST',
2415 decode(debit_credit_flag,
2416 'DR', adjustment_amount,
2417 -1 * adjustment_amount),
2418 decode(debit_credit_flag,
2419 'CR', adjustment_amount,
2420 -1 * adjustment_amount)) ,
2421 adj.ADJUSTMENT_LINE_ID,
2422 adj.ADJUSTMENT_TYPE,
2423 dh.CODE_COMBINATION_ID,
2424 ret.GAIN_LOSS_AMOUNT,
2425 stg.ASSET_COST_ACCT,
2426 stg.BONUS_DEPRN_EXPENSE_ACCT,
2427 stg.BONUS_RESERVE_ACCT,
2428 stg.CIP_COST_ACCT,
2429 stg.DEPRN_RESERVE_ACCT,
2430 stg.REVAL_RESERVE_ACCT,
2431 stg.TRANSACTION_HEADER_ID
2432 from fa_xla_ext_lines_stg_gt stg,
2433 fa_mc_adjustments adj,
2434 fa_distribution_history dh,
2435 fa_locations loc,
2436 fa_lookups lu ,
2437 fa_retirements ret ,
2438 fa_mc_book_controls bc ,
2439 gl_ledgers le
2440 WHERE adj.transaction_header_id = stg.transaction_header_id
2441 AND adj.book_type_code = stg.book_type_code
2442 AND adj.distribution_id = dh.distribution_id
2443 AND dh.location_id = loc.location_id
2444 -- AND dh.assigned_to = emp.employee_id(+)
2445 AND lu.lookup_type = 'JOURNAL ENTRIES'
2446 AND lu.lookup_code = adj.source_type_code || ' ' ||
2447 decode (adj.adjustment_type,
2448 'CIP COST', 'COST',
2449 adj.adjustment_type)
2450 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2451 AND nvl(adj.track_member_flag, 'N') = 'N'
2452 AND adj.adjustment_amount <> 0
2453 AND stg.event_type_code in ('RETIREMENTS', 'CIP_RETIREMENTS')
2454 AND ret.transaction_header_id_in = stg.member_transaction_header_id
2455 AND bc.book_type_code = stg.book_type_code
2456 AND bc.set_of_books_id = le.ledger_id
2457 AND adj.set_of_books_id = bc.set_of_books_id ;
2458
2459 end if;
2460
2461
2462 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2463 fnd_log.string(G_LEVEL_PROCEDURE,
2464 G_MODULE_NAME||l_procedure_name,
2465 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2466 END IF;
2467
2468
2469
2470 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2471 fnd_log.string(G_LEVEL_PROCEDURE,
2472 G_MODULE_NAME||l_procedure_name||'.end',
2473 'End of procedure');
2474 END IF;
2475
2476 EXCEPTION
2477 WHEN others THEN
2478 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2479 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2480 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2481 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2482 END IF;
2483 raise;
2484
2485 end load_line_data_ret;
2486
2487
2488
2489 /*======================================================================+
2490 | |
2491 | Private Function |
2492 | load_line_data_res |
2493 | |
2494 +======================================================================*/
2495
2496 PROCEDURE load_line_data_res IS
2497
2498 l_procedure_name varchar2(80) := 'load_line_data_res';
2499
2500 BEGIN
2501
2502 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2503 fnd_log.string(G_LEVEL_PROCEDURE,
2504 G_MODULE_NAME||l_procedure_name||'.begin',
2505 'Beginning of procedure');
2506 END IF;
2507
2508
2509 insert into fa_xla_ext_lines_b_gt (
2510 EVENT_ID ,
2511 LINE_NUMBER ,
2512 DISTRIBUTION_ID ,
2513 DISTRIBUTION_TYPE_CODE ,
2514 LEDGER_ID ,
2515 CURRENCY_CODE ,
2516 BOOK_TYPE_CODE ,
2517 GENERATED_CCID ,
2518 ASSET_ID ,
2519 ASSET_TYPE ,
2520 ASSET_COST_ACCOUNT_CCID ,
2521 ASSET_CLEARING_ACCOUNT_CCID ,
2522 CIP_COST_ACCOUNT_CCID ,
2523 CIP_CLEARING_ACCOUNT_CCID ,
2524 RESERVE_ACCOUNT_CCID ,
2525 DEPRN_EXPENSE_ACCOUNT_CCID ,
2526 BONUS_RESERVE_ACCT_CCID ,
2527 BONUS_EXPENSE_ACCOUNT_CCID ,
2528 REVAL_AMORT_ACCOUNT_CCID ,
2529 REVAL_RESERVE_ACCOUNT_CCID ,
2530 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2531 ALT_COST_ACCOUNT_CCID ,
2532 WRITE_OFF_ACCOUNT_CCID ,
2533 ENTERED_AMOUNT ,
2534 ADJUSTMENT_LINE_ID,
2535 ADJUSTMENT_TYPE,
2536 EXPENSE_ACCOUNT_CCID,
2537 GAIN_LOSS_AMOUNT,
2538 ASSET_COST_ACCT,
2539 BONUS_DEPRN_EXPENSE_ACCT,
2540 BONUS_RESERVE_ACCT,
2541 CIP_COST_ACCT,
2542 DEPRN_RESERVE_ACCT,
2543 REVAL_RESERVE_ACCT,
2544 TRANSACTION_HEADER_ID )
2545 select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2546 adj.adjustment_line_id ,
2547 adj.distribution_id ,
2548 stg.distribution_type_code ,
2549 stg.ledger_id ,
2550 stg.currency_code ,
2551 stg.book_type_code ,
2552 adj.code_combination_id ,
2553 adj.asset_id ,
2554 stg.asset_type ,
2555 stg.ASSET_COST_ACCOUNT_CCID ,
2556 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2557 stg.CIP_COST_ACCOUNT_CCID ,
2558 stg.CIP_CLEARING_ACCOUNT_CCID ,
2559 stg.RESERVE_ACCOUNT_CCID ,
2560 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2561 stg.BONUS_RESERVE_ACCT_CCID ,
2562 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2563 stg.REVAL_AMORT_ACCOUNT_CCID ,
2564 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2565 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2566 stg.ALT_COST_ACCOUNT_CCID ,
2567 stg.WRITE_OFF_ACCOUNT_CCID ,
2568 decode(adj.adjustment_type,
2569 'RESERVE',
2570 decode(debit_credit_flag,
2571 'DR', adjustment_amount,
2572 -1 * adjustment_amount),
2573 'BONUS RESERVE',
2574 decode(debit_credit_flag,
2575 'DR', adjustment_amount,
2576 -1 * adjustment_amount),
2577 'REVAL RESERVE',
2578 decode(debit_credit_flag,
2579 'DR', adjustment_amount,
2580 -1 * adjustment_amount),
2581 'NBV RETIRED',
2582 decode(debit_credit_flag,
2583 'DR', adjustment_amount,
2584 -1 * adjustment_amount),
2585 'PROCEEDS CLR',
2586 decode(debit_credit_flag,
2587 'DR', adjustment_amount,
2588 -1 * adjustment_amount),
2589 'REMOVALCOST',
2590 decode(debit_credit_flag,
2591 'DR', adjustment_amount,
2592 -1 * adjustment_amount),
2593 decode(debit_credit_flag,
2594 'CR', adjustment_amount,
2595 -1 * adjustment_amount)) ,
2596 adj.ADJUSTMENT_LINE_ID,
2597 adj.ADJUSTMENT_TYPE,
2598 dh.CODE_COMBINATION_ID,
2599 ret.GAIN_LOSS_AMOUNT,
2600 stg.ASSET_COST_ACCT,
2601 stg.BONUS_DEPRN_EXPENSE_ACCT,
2602 stg.BONUS_RESERVE_ACCT,
2603 stg.CIP_COST_ACCT,
2604 stg.DEPRN_RESERVE_ACCT,
2605 stg.REVAL_RESERVE_ACCT,
2606 stg.TRANSACTION_HEADER_ID
2607 from fa_xla_ext_lines_stg_gt stg,
2608 fa_adjustments adj,
2609 fa_distribution_history dh,
2610 fa_locations loc,
2611 fa_lookups lu ,
2612 fa_retirements ret
2613 WHERE adj.transaction_header_id = stg.transaction_header_id
2614 AND adj.book_type_code = stg.book_type_code
2615 AND adj.distribution_id = dh.distribution_id
2616 AND dh.location_id = loc.location_id
2617 -- AND dh.assigned_to = emp.employee_id(+)
2618 AND lu.lookup_type = 'JOURNAL ENTRIES'
2619 AND lu.lookup_code = adj.source_type_code || ' ' ||
2620 decode (adj.adjustment_type,
2621 'CIP COST', 'COST',
2622 adj.adjustment_type)
2623 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2624 AND nvl(adj.track_member_flag, 'N') = 'N'
2625 AND adj.adjustment_amount <> 0
2626 AND stg.event_type_code in ('REINSTATEMENTS','CIP_REINSTATEMENTS')
2627 AND ret.transaction_header_id_out = stg.member_transaction_header_id ;
2628
2629 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2630 fnd_log.string(G_LEVEL_PROCEDURE,
2631 G_MODULE_NAME||l_procedure_name,
2632 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2633 END IF;
2634
2635
2636
2637 if (fa_xla_extract_util_pkg.G_alc_enabled) then
2638
2639
2640
2641 insert into fa_xla_ext_lines_b_gt (
2642 EVENT_ID ,
2643 LINE_NUMBER ,
2644 DISTRIBUTION_ID ,
2645 DISTRIBUTION_TYPE_CODE ,
2646 LEDGER_ID ,
2647 CURRENCY_CODE ,
2648 BOOK_TYPE_CODE ,
2649 GENERATED_CCID ,
2650 ASSET_ID ,
2651 ASSET_TYPE ,
2652 ASSET_COST_ACCOUNT_CCID ,
2653 ASSET_CLEARING_ACCOUNT_CCID ,
2654 CIP_COST_ACCOUNT_CCID ,
2655 CIP_CLEARING_ACCOUNT_CCID ,
2656 RESERVE_ACCOUNT_CCID ,
2657 DEPRN_EXPENSE_ACCOUNT_CCID ,
2658 BONUS_RESERVE_ACCT_CCID ,
2659 BONUS_EXPENSE_ACCOUNT_CCID ,
2660 REVAL_AMORT_ACCOUNT_CCID ,
2661 REVAL_RESERVE_ACCOUNT_CCID ,
2662 UNPLAN_EXPENSE_ACCOUNT_CCID ,
2663 ALT_COST_ACCOUNT_CCID ,
2664 WRITE_OFF_ACCOUNT_CCID ,
2665 ENTERED_AMOUNT ,
2666 ADJUSTMENT_LINE_ID,
2667 ADJUSTMENT_TYPE,
2668 EXPENSE_ACCOUNT_CCID,
2669 GAIN_LOSS_AMOUNT,
2670 ASSET_COST_ACCT,
2671 BONUS_DEPRN_EXPENSE_ACCT,
2672 BONUS_RESERVE_ACCT,
2673 CIP_COST_ACCT,
2674 DEPRN_RESERVE_ACCT,
2675 REVAL_RESERVE_ACCT,
2676 TRANSACTION_HEADER_ID )
2677 select /*+ leading(stg) index(adj, FA_MC_ADJUSTMENTS_U1) */ stg.EVENT_ID ,
2678 adj.adjustment_line_id ,
2679 adj.distribution_id ,
2680 stg.distribution_type_code ,
2681 bc.set_of_books_id ,
2682 le.currency_code ,
2683 stg.book_type_code ,
2684 adj.code_combination_id ,
2685 adj.asset_id ,
2686 stg.asset_type ,
2687 stg.ASSET_COST_ACCOUNT_CCID ,
2688 stg.ASSET_CLEARING_ACCOUNT_CCID ,
2689 stg.CIP_COST_ACCOUNT_CCID ,
2690 stg.CIP_CLEARING_ACCOUNT_CCID ,
2691 stg.RESERVE_ACCOUNT_CCID ,
2692 stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
2693 stg.BONUS_RESERVE_ACCT_CCID ,
2694 stg.BONUS_EXPENSE_ACCOUNT_CCID ,
2695 stg.REVAL_AMORT_ACCOUNT_CCID ,
2696 stg.REVAL_RESERVE_ACCOUNT_CCID ,
2697 stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
2698 stg.ALT_COST_ACCOUNT_CCID ,
2699 stg.WRITE_OFF_ACCOUNT_CCID ,
2700 decode(adj.adjustment_type,
2701 'RESERVE',
2702 decode(debit_credit_flag,
2703 'DR', adjustment_amount,
2704 -1 * adjustment_amount),
2705 'BONUS RESERVE',
2706 decode(debit_credit_flag,
2707 'DR', adjustment_amount,
2708 -1 * adjustment_amount),
2709 'REVAL RESERVE',
2710 decode(debit_credit_flag,
2711 'DR', adjustment_amount,
2712 -1 * adjustment_amount),
2713 'NBV RETIRED',
2714 decode(debit_credit_flag,
2715 'DR', adjustment_amount,
2716 -1 * adjustment_amount),
2717 'PROCEEDS CLR',
2718 decode(debit_credit_flag,
2719 'DR', adjustment_amount,
2720 -1 * adjustment_amount),
2721 'REMOVALCOST',
2722 decode(debit_credit_flag,
2723 'DR', adjustment_amount,
2724 -1 * adjustment_amount),
2725 decode(debit_credit_flag,
2726 'CR', adjustment_amount,
2727 -1 * adjustment_amount)) ,
2728 adj.ADJUSTMENT_LINE_ID,
2729 adj.ADJUSTMENT_TYPE,
2730 dh.CODE_COMBINATION_ID,
2731 ret.GAIN_LOSS_AMOUNT,
2732 stg.ASSET_COST_ACCT,
2733 stg.BONUS_DEPRN_EXPENSE_ACCT,
2734 stg.BONUS_RESERVE_ACCT,
2735 stg.CIP_COST_ACCT,
2736 stg.DEPRN_RESERVE_ACCT,
2737 stg.REVAL_RESERVE_ACCT,
2738 stg.TRANSACTION_HEADER_ID
2739 from fa_xla_ext_lines_stg_gt stg,
2740 fa_mc_adjustments adj,
2741 fa_distribution_history dh,
2742 fa_locations loc,
2743 fa_lookups lu ,
2744 fa_retirements ret ,
2745 fa_mc_book_controls bc ,
2746 gl_ledgers le
2747 WHERE adj.transaction_header_id = stg.transaction_header_id
2748 AND adj.book_type_code = stg.book_type_code
2749 AND adj.distribution_id = dh.distribution_id
2750 AND dh.location_id = loc.location_id
2751 -- AND dh.assigned_to = emp.employee_id(+)
2752 AND lu.lookup_type = 'JOURNAL ENTRIES'
2753 AND lu.lookup_code = adj.source_type_code || ' ' ||
2754 decode (adj.adjustment_type,
2755 'CIP COST', 'COST',
2756 adj.adjustment_type)
2757 AND adj.adjustment_type not in ('REVAL EXPENSE', 'REVAL AMORT')
2758 AND nvl(adj.track_member_flag, 'N') = 'N'
2759 AND adj.adjustment_amount <> 0
2760 AND stg.event_type_code in ('REINSTATEMENTS','CIP_REINSTATEMENTS')
2761 AND ret.transaction_header_id_out = stg.member_transaction_header_id
2762 AND bc.book_type_code = stg.book_type_code
2763 AND bc.set_of_books_id = le.ledger_id
2764 AND adj.set_of_books_id = bc.set_of_books_id ;
2765
2766 end if;
2767
2768
2769 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2770 fnd_log.string(G_LEVEL_PROCEDURE,
2771 G_MODULE_NAME||l_procedure_name,
2772 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
2773 END IF;
2774
2775
2776
2777 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2778 fnd_log.string(G_LEVEL_PROCEDURE,
2779 G_MODULE_NAME||l_procedure_name||'.end',
2780 'End of procedure');
2781 END IF;
2782
2783 EXCEPTION
2784 WHEN others THEN
2785 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2786 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2787 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2788 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2789 END IF;
2790 raise;
2791
2792 end load_line_data_res;
2793
2794
2795
2796 /*======================================================================+
2797 | |
2798 | Private Function |
2799 | load_mls_data |
2800 | |
2801 +======================================================================*/
2802
2803 PROCEDURE load_mls_data IS
2804
2805 l_procedure_name varchar2(80) := 'load_mls_data';
2806
2807 BEGIN
2808
2809 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2810 fnd_log.string(G_LEVEL_PROCEDURE,
2811 G_MODULE_NAME||l_procedure_name||'.begin',
2812 'Beginning of procedure');
2813 END IF;
2814
2815 return;
2816
2817 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2818 fnd_log.string(G_LEVEL_PROCEDURE,
2819 G_MODULE_NAME||l_procedure_name,
2820 'Rows inserted into mls: ' || to_char(SQL%ROWCOUNT));
2821 END IF;
2822
2823
2824
2825 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2826 fnd_log.string(G_LEVEL_PROCEDURE,
2827 G_MODULE_NAME||l_procedure_name||'.end',
2828 'End of procedure');
2829 END IF;
2830
2831 EXCEPTION
2832 WHEN others THEN
2833 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
2834 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
2835 fnd_message.set_token('ORACLE_ERR',SQLERRM);
2836 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
2837 END IF;
2838 raise;
2839
2840 end load_mls_data;
2841
2842
2843
2844 /*======================================================================+
2845 | |
2846 | Private Function |
2847 | Load_Generated_Ccids |
2848 | |
2849 +======================================================================*/
2850
2851 ----------------------------------------------------
2852 --
2853 -- Account Generator Hook
2854 --
2855 ----------------------------------------------------
2856 PROCEDURE Load_Generated_Ccids
2857 (p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
2858
2859 l_mesg_count number := 0;
2860 l_mesg_len number;
2861 l_mesg varchar2(4000);
2862
2863 l_procedure_name varchar2(80) := 'fa_xla_extract_def_pkg.load_generated_ccids'; -- BMR make this dynamic on type
2864
2865 type char_tab_type is table of varchar2(64) index by binary_integer;
2866 type num_tab_type is table of number index by binary_integer;
2867
2868
2869
2870
2871
2872 -- bug 5563601: Increased length of variable account_type to 50
2873 type adj_rec_type is record
2874 (rowid VARCHAR2(64),
2875 book_type_code VARCHAR2(15),
2876 distribution_id NUMBER(15),
2877 distribution_ccid NUMBER(15),
2878 entered_amount NUMBER,
2879 account_type VARCHAR2(50),
2880 generated_ccid NUMBER(15),
2881 account_ccid NUMBER(15),
2882 account_segment VARCHAR2(25),
2883 offset_account_type VARCHAR2(25),
2884 generated_offset_ccid NUMBER(15),
2885 offset_account_ccid NUMBER(15),
2886 offset_account_segment VARCHAR2(25)
2887 );
2888
2889 type adj_tbl_type is table of adj_rec_type index by binary_integer;
2890
2891 l_adj_tbl adj_tbl_type;
2892
2893 l_generated_ccid num_tab_type;
2894 l_generated_offset_ccid num_tab_type;
2895 l_rowid char_tab_type;
2896
2897 error_found exception;
2898
2899 l_last_book varchar2(15) := ' ';
2900
2901 cursor c_trx is
2902 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
2903 xl.rowid,
2904 xb.book_type_code,
2905 xl.distribution_id,
2906 xl.expense_account_ccid,
2907 xl.entered_amount,
2908 decode
2909 (adjustment_type,
2910 'COST', 'ASSET_COST_ACCT',
2911 'CIP COST', 'CIP_COST_ACCT',
2912 'COST CLEARING', decode(xl.asset_type,
2913 'CIP', 'CIP_CLEARING_ACCT',
2914 'ASSET_CLEARING_ACCT'),
2915 'EXPENSE', 'DEPRN_EXPENSE_ACCT',
2916 'RESERVE', 'DEPRN_RESERVE_ACCT',
2917 'BONUS EXPENSE', 'BONUS_DEPRN_EXPENSE_ACCT',
2918 'BONUS RESERVE', 'BONUS_DEPRN_RESERVE_ACCT',
2919 'REVAL RESERVE', 'REVAL_RESERVE_ACCT',
2920 'DEPRN ADJUST', 'DEPRN_ADJUSTMENT_ACCT',
2921 'PROCEEDS CLR', 'PROCEEDS_OF_SALE_CLEARING_ACCT',
2922 'REMOVALCOST CLR', 'COST_OF_REMOVAL_CLEARING_ACCT',
2923 'REMOVALCOST', decode(sign(gain_loss_amount),
2924 -1, 'COST_OF_REMOVAL_LOSS_ACCT',
2925 'COST_OF_REMOVAL_GAIN_ACCT'),
2926 'PROCEEDS', decode(sign(gain_loss_amount),
2927 -1, 'PROCEEDS_OF_SALE_LOSS_ACCT',
2928 'PROCEEDS_OF_SALE_GAIN_ACCT'),
2929 'REVAL RSV RET', decode(sign(gain_loss_amount),
2930 -1, 'REVAL_RSV_RETIRED_LOSS_ACCT',
2931 'REVAL_RSV_RETIRED_GAIN_ACCT'),
2932 'NBV RETIRED', decode(asset_type,
2933 'GROUP', decode(sign(gain_loss_amount),
2934 -1, 'NBV_RETIRED_LOSS_ACCT',
2935 'NBV_RETIRED_GAIN_ACCT'),
2936 decode(sign(gain_loss_amount),
2937 -1, 'NBV_RETIRED_LOSS_ACCT',
2938 'NBV_RETIRED_GAIN_ACCT')),
2939 NULL),
2940 decode(xl.adjustment_type,
2941 'COST', nvl(xl.generated_ccid, da.ASSET_COST_ACCOUNT_CCID),
2942 'CIP COST', nvl(xl.generated_ccid, da.CIP_COST_ACCOUNT_CCID),
2943 'COST CLEARING', decode(xl.asset_type,
2944 'CIP', nvl(xl.generated_ccid, da.CIP_CLEARING_ACCOUNT_CCID),
2945 nvl(xl.generated_ccid, da.ASSET_CLEARING_ACCOUNT_CCID)),
2946 'EXPENSE', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
2947 'RESERVE', nvl(xl.generated_ccid, da.DEPRN_RESERVE_ACCOUNT_CCID),
2948 'BONUS EXPENSE', nvl(xl.generated_ccid, da.BONUS_EXP_ACCOUNT_CCID),
2949 'BONUS RESERVE', nvl(xl.generated_ccid, da.BONUS_RSV_ACCOUNT_CCID),
2950 'REVAL RESERVE', nvl(xl.generated_ccid, da.REVAL_RSV_ACCOUNT_CCID),
2951 'DEPRN ADJUST', nvl(xl.generated_ccid, da.DEPRN_ADJ_ACCOUNT_CCID),
2952 'PROCEEDS CLR', nvl(xl.generated_ccid, da.PROCEEDS_SALE_CLEARING_CCID),
2953 'REMOVALCOST CLR', nvl(xl.generated_ccid, da.COST_REMOVAL_CLEARING_CCID),
2954 'PROCEEDS', decode(sign(xl.gain_loss_amount),
2955 -1, nvl(xl.generated_ccid, da.PROCEEDS_SALE_LOSS_CCID),
2956 nvl(xl.generated_ccid, da.PROCEEDS_SALE_GAIN_CCID)),
2957 'REMOVALCOST', decode(sign(xl.gain_loss_amount),
2958 -1, nvl(xl.generated_ccid, da.COST_REMOVAL_LOSS_CCID),
2959 nvl(xl.generated_ccid, da.COST_REMOVAL_GAIN_CCID)),
2960 'REVAL RSV RET', decode(sign(xl.gain_loss_amount),
2961 -1, nvl(xl.generated_ccid, da.REVAL_RSV_LOSS_ACCOUNT_CCID),
2962 nvl(xl.generated_ccid, da.REVAL_RSV_GAIN_ACCOUNT_CCID)),
2963 'NBV RETIRED', decode(sign(xl.gain_loss_amount),
2964 -1, nvl(xl.generated_ccid, da.NBV_RETIRED_LOSS_CCID),
2965 nvl(xl.generated_ccid, da.NBV_RETIRED_GAIN_CCID)),
2966 NULL),
2967 decode(xl.adjustment_type,
2968 'COST', xl.ASSET_COST_ACCOUNT_CCID,
2969 'CIP COST', xl.CIP_COST_ACCOUNT_CCID,
2970 'COST CLEARING', decode(xl.asset_type,
2971 'CIP', xl.CIP_CLEARING_ACCOUNT_CCID,
2972 xl.ASSET_CLEARING_ACCOUNT_CCID),
2973 'RESERVE', xl.RESERVE_ACCOUNT_CCID,
2974 'BONUS RESERVE', xl.BONUS_RESERVE_ACCT_CCID,
2975 'REVAL RESERVE', xl.REVAL_RESERVE_ACCOUNT_CCID,
2976 0),
2977 decode(xl.adjustment_type,
2978 'COST', xl.ASSET_COST_ACCT,
2979 'CIP COST', xl.CIP_COST_ACCT,
2980 'COST CLEARING', decode(xl.asset_type,
2981 'CIP', xl.CIP_CLEARING_ACCT,
2982 xl.ASSET_CLEARING_ACCT),
2983 'EXPENSE', xl.DEPRN_EXPENSE_ACCT,
2984 'RESERVE', xl.DEPRN_RESERVE_ACCT,
2985 'BONUS EXPENSE', xl.BONUS_DEPRN_EXPENSE_ACCT,
2986 'BONUS RESERVE', xl.BONUS_RESERVE_ACCT,
2987 'REVAL RESERVE', xl.REVAL_RESERVE_ACCT,
2988 'PROCEEDS CLR', xb.PROCEEDS_OF_SALE_CLEARING_ACCT,
2989 'REMOVALCOST CLR', xb.COST_OF_REMOVAL_CLEARING_ACCT,
2990 'NBV RETIRED', decode(sign(xl.gain_loss_amount),
2991 -1, xb.NBV_RETIRED_LOSS_ACCT,
2992 xb.NBV_RETIRED_GAIN_ACCT),
2993 'PROCEEDS', decode(sign(xl.gain_loss_amount),
2994 -1, xb.PROCEEDS_OF_SALE_LOSS_ACCT,
2995 xb.PROCEEDS_OF_SALE_GAIN_ACCT),
2996 'REMOVALCOST', decode(sign(xl.gain_loss_amount),
2997 -1, xb.COST_OF_REMOVAL_LOSS_ACCT,
2998 xb.COST_OF_REMOVAL_GAIN_ACCT),
2999 'REVAL RSV RET', decode(sign(xl.gain_loss_amount),
3000 -1, xb.REVAL_RSV_RETIRED_LOSS_ACCT,
3001 xb.REVAL_RSV_RETIRED_GAIN_ACCT),
3002 NULL),
3003 decode(xl.adjustment_type,
3004 'EXPENSE', 'DEPRN_RESERVE_ACCT',
3005 'BONUS EXPENSE', 'BONUS_DEPRN_RESERVE_ACCT',
3006 NULL),
3007 decode(xl.adjustment_type,
3008 'EXPENSE', da.DEPRN_RESERVE_ACCOUNT_CCID,
3009 'BONUS EXPENSE', da.BONUS_RSV_ACCOUNT_CCID,
3010 NULL),
3011 decode(xl.adjustment_type,
3012 'EXPENSE', xl.RESERVE_ACCOUNT_CCID,
3013 'BONUS EXPENSE', xl.BONUS_RESERVE_ACCT_CCID,
3014 NULL),
3015 decode(xl.adjustment_type,
3016 'EXPENSE', xl.DEPRN_RESERVE_ACCT,
3017 'BONUS EXPENSE', xl.BONUS_RESERVE_ACCT,
3018 NULL)
3019 from xla_events_gt xg,
3020 fa_xla_ext_headers_b_gt xb,
3021 fa_xla_ext_lines_b_gt xl,
3022 fa_distribution_accounts da
3023 where xg.event_class_code not in ('DEPRECIATION', 'DEFERRED')
3024 and xb.event_id = xg.event_id
3025 and xl.event_id = xg.event_id
3026 and xl.distribution_id = da.distribution_id(+)
3027 and xl.book_type_code = da.book_type_code(+);
3028
3029
3030 BEGIN
3031
3032 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3033 fnd_log.string(G_LEVEL_PROCEDURE,
3034 G_MODULE_NAME||l_procedure_name||'.begin',
3035 'Beginning of procedure');
3036 END IF;
3037
3038
3039 open c_trx;
3040 fetch c_trx
3041 bulk collect into l_adj_tbl;
3042 close c_trx;
3043
3044 for i in 1..l_adj_tbl.count loop
3045
3046 if (l_last_book <> l_adj_tbl(i).book_type_code or
3047 i = 1) then
3048 if not (fa_cache_pkg.fazcbc
3049 (X_BOOK => l_adj_tbl(1).book_type_code,
3050 P_LOG_LEVEL_REC => p_log_level_rec)) then
3051 null;
3052 end if;
3053 l_last_book := l_adj_tbl(i).book_type_code;
3054 end if;
3055
3056 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
3057
3058 if (l_adj_tbl(i).generated_ccid is null and
3059 l_adj_tbl(i).entered_amount <> 0) then
3060
3061 if (not FA_GCCID_PKG.fafbgcc
3062 (X_book_type_code => l_adj_tbl(i).book_type_code,
3063 X_fn_trx_code => l_adj_tbl(i).account_type,
3064 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
3065 X_acct_segval => l_adj_tbl(i).account_segment,
3066 X_account_ccid => l_adj_tbl(i).account_ccid,
3067 X_distribution_id => l_adj_tbl(i).distribution_id,
3068 X_rtn_ccid => l_adj_tbl(i).generated_ccid,
3069 P_LOG_LEVEL_REC => p_log_level_rec)) then
3070 FA_SRVR_MSG.ADD_MESSAGE
3071 (NAME => 'FA_GET_ACCOUNT_CCID',
3072 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
3073 P_LOG_LEVEL_REC => p_log_level_rec);
3074 l_adj_tbl(i).generated_ccid := -1;
3075 end if;
3076 end if;
3077
3078 if (l_adj_tbl(i).account_type in
3079 ('DEPRN_EXPENSE_ACCT', 'BONUS_DEPRN_EXPENSE_ACCT') and
3080 l_adj_tbl(i).generated_offset_ccid is null and
3081 l_adj_tbl(i).entered_amount <> 0) then
3082
3083 if (not FA_GCCID_PKG.fafbgcc
3084 (X_book_type_code => l_adj_tbl(i).book_type_code,
3085 X_fn_trx_code => l_adj_tbl(i).offset_account_type,
3086 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
3087 X_acct_segval => l_adj_tbl(i).offset_account_segment,
3088 X_account_ccid => l_adj_tbl(i).offset_account_ccid,
3089 X_distribution_id => l_adj_tbl(i).distribution_id,
3090 X_rtn_ccid => l_adj_tbl(i).generated_offset_ccid,
3091 P_LOG_LEVEL_REC => p_log_level_rec)) then
3092 FA_SRVR_MSG.ADD_MESSAGE
3093 (NAME => 'FA_GET_ACCOUNT_CCID',
3094 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
3095 P_LOG_LEVEL_REC => p_log_level_rec);
3096 l_adj_tbl(i).generated_offset_ccid := -1;
3097 end if;
3098 end if;
3099
3100 end loop;
3101
3102 for i in 1.. l_adj_tbl.count loop
3103
3104 l_generated_ccid(i) := l_adj_tbl(i).generated_ccid;
3105 l_generated_offset_ccid(i) := l_adj_tbl(i).generated_offset_ccid;
3106 l_rowid(i) := l_adj_tbl(i).rowid;
3107
3108 end loop;
3109
3110 forall i in 1..l_adj_tbl.count
3111 update fa_xla_ext_lines_b_gt
3112 set generated_ccid = l_generated_ccid(i),
3113 generated_offset_ccid = l_generated_offset_ccid(i)
3114 where rowid = l_rowid(i);
3115
3116
3117 --
3118
3119 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3120 fnd_log.string(G_LEVEL_PROCEDURE,
3121 G_MODULE_NAME||l_procedure_name||'.end',
3122 'End of procedure');
3123 END IF;
3124
3125 EXCEPTION
3126 WHEN others THEN
3127 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3128 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3129 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3130 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3131 END IF;
3132 raise;
3133
3134 END load_generated_ccids;
3135
3136
3137
3138 /*======================================================================+
3139 | |
3140 | Private Function |
3141 | Lock_Data |
3142 | |
3143 +======================================================================*/
3144
3145 --------------------------------------------------
3146 -- Locking Routine --
3147 --------------------------------------------------
3148
3149 PROCEDURE Lock_Data IS
3150
3151 TYPE number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
3152 l_lock number_tbl_type;
3153 l_procedure_name varchar2(80) := 'lock_data';
3154
3155 BEGIN
3156
3157 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3158 fnd_log.string(G_LEVEL_PROCEDURE,
3159 G_MODULE_NAME||l_procedure_name||'.begin',
3160 'Beginning of procedure');
3161 END IF;
3162
3163
3164 --
3165 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3166 fnd_log.string(G_LEVEL_PROCEDURE,
3167 G_MODULE_NAME||l_procedure_name||'.end',
3168 'End of procedure');
3169 END IF;
3170
3171 EXCEPTION
3172 WHEN others THEN
3173 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
3174 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3175 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3176 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3177 END IF;
3178 raise;
3179
3180
3181 END Lock_Data;
3182
3183
3184
3185 /*======================================================================+
3186 | |
3187 | Public Function |
3188 | Lock_Data |
3189 | |
3190 +======================================================================*/
3191
3192 --------------------------------------------------
3193 -- Main Load Routine --
3194 --------------------------------------------------
3195 PROCEDURE load_data IS
3196
3197 l_log_level_rec FA_API_TYPES.log_level_rec_type;
3198 l_use_fafbgcc varchar2(25);
3199 l_procedure_name varchar2(80) := 'load_data'; -- BMR make this dynamic on type
3200 error_found EXCEPTION;
3201
3202 BEGIN
3203
3204 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3205 fnd_log.string(G_LEVEL_PROCEDURE,
3206 G_MODULE_NAME||l_procedure_name||'.begin',
3207 'Beginning of procedure');
3208 END IF;
3209
3210
3211
3212 Lock_Data;
3213 if (fa_xla_extract_util_pkg.G_trx_exists) then
3214 load_header_data_stg1;
3215 Load_line_data_stg1;
3216 end if;
3217
3218 if (fa_xla_extract_util_pkg.G_inter_trx_exists) then
3219 load_header_data_stg2;
3220 Load_line_data_stg2;
3221 end if;
3222
3223 if (fa_xla_extract_util_pkg.G_fin_trx_exists) then
3224 Load_line_data_fin1;
3225 end if;
3226
3227 if (fa_xla_extract_util_pkg.G_inter_trx_exists) then
3228 Load_line_data_fin2;
3229 end if;
3230
3231 if (fa_xla_extract_util_pkg.G_xfr_trx_exists) then
3232 Load_line_data_xfr;
3233 end if;
3234
3235 if (fa_xla_extract_util_pkg.G_dist_trx_exists) then
3236 Load_line_data_dist;
3237 end if;
3238
3239 if (fa_xla_extract_util_pkg.G_ret_trx_exists) then
3240 Load_line_data_ret;
3241 end if;
3242
3243 if (fa_xla_extract_util_pkg.G_res_trx_exists) then
3244 Load_line_data_res;
3245 end if;
3246
3247 Load_mls_data;
3248
3249
3250
3251
3252 fnd_profile.get ('FA_WF_GENERATE_CCIDS', l_use_fafbgcc);
3253 if (nvl(l_use_fafbgcc, 'N') = 'Y') then
3254 if (NOT fa_util_pub.get_log_level_rec (
3255 x_log_level_rec => l_log_level_rec)) then raise error_found;
3256 end if;
3257
3258 Load_Generated_Ccids
3259 (p_log_level_rec => l_log_level_rec);
3260 end if;
3261
3262
3263
3264
3265
3266 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3267 fnd_log.string(G_LEVEL_PROCEDURE,
3268 G_MODULE_NAME||l_procedure_name||'.end',
3269 'End of procedure');
3270 END IF;
3271
3272 EXCEPTION
3273 WHEN error_found THEN
3274 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
3275 FND_LOG.string (G_LEVEL_ERROR,
3276 G_MODULE_NAME||l_procedure_name,
3277 'ended in error');
3278 END IF;
3279 raise;
3280
3281 WHEN others THEN
3282 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3283 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
3284 fnd_message.set_token('ORACLE_ERR',SQLERRM);
3285 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
3286 END IF;
3287 raise;
3288
3289 END load_data;
3290
3291
3292
3293 END FA_XLA_EXTRACT_TRX_PKG;
3294