[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_EXTRACT_DEPRN_PKG
Source
1 PACKAGE BODY FA_XLA_EXTRACT_DEPRN_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_DEPRN_PKG |
10 | |
11 | DESCRIPTION |
12 | Package generated From FA AAD setups |
13 | |
14 | HISTORY |
15 | Generated at 30-03-2009 at 05:03:16 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 |
46 | |
47 +======================================================================*/
48
49 PROCEDURE load_header_data IS
50
51 l_procedure_name varchar2(80) := 'load_header_data';
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 select ctlgd.event_id,
72 bc.FLEXBUILDER_DEFAULTS_CCID ,
73 bc.book_type_code ,
74 dp.PERIOD_NAME ,
75 dp.CALENDAR_PERIOD_CLOSE_DATE ,
76 dp.PERIOD_COUNTER ,
77 ctlgd.event_date ,
78 decode(bc.GL_POSTING_ALLOWED_FLAG ,
79 'YES', 'Y','N')
80 FROM xla_events_gt ctlgd,
81 fa_deprn_periods dp,
82 fa_book_controls bc
83 WHERE ctlgd.entity_code = 'DEPRECIATION'
84 AND ctlgd.event_type_code = 'DEPRECIATION'
85 AND dp.book_type_code = ctlgd.source_id_char_1
86 AND dp.period_counter = ctlgd.source_id_int_2
87 AND bc.book_type_code = ctlgd.source_id_char_1;
88
89
90 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
91 fnd_log.string(G_LEVEL_PROCEDURE,
92 G_MODULE_NAME||l_procedure_name,
93 'Rows inserted into headers: ' || to_char(SQL%ROWCOUNT));
94 END IF;
95
96
97
98 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
99 fnd_log.string(G_LEVEL_PROCEDURE,
100 G_MODULE_NAME||l_procedure_name||'.end',
101 'End of procedure');
102 END IF;
103
104 EXCEPTION
105 WHEN others THEN
106 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
107 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
108 fnd_message.set_token('ORACLE_ERR',SQLERRM);
109 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
110 END IF;
111 raise;
112
113 end load_header_data;
114
115
116
117 PROCEDURE Load_header_data_rb IS
118
119 l_procedure_name varchar2(80) := 'load_header_data_rb';
120
121 BEGIN
122
123 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
124 fnd_log.string(G_LEVEL_PROCEDURE,
125 G_MODULE_NAME||l_procedure_name||'.begin',
126 'Beginning of procedure');
127 END IF;
128
129 INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
130 event_id ,
131 period_close_date ,
132 reversal_flag ,
133 transfer_to_gl_flag ,
134 accounting_date )
135 SELECT ctlgd.event_id ,
136 dp.CALENDAR_PERIOD_CLOSE_DATE ,
137 'Y' ,
138 decode(bc.GL_POSTING_ALLOWED_FLAG ,
139 'YES', 'Y',
140 'N'),
141 dp.CALENDAR_PERIOD_CLOSE_DATE
142 FROM xla_events_gt ctlgd,
143 fa_book_controls bc,
144 fa_deprn_periods dp,
145 fa_deprn_events ds
146 WHERE ctlgd.entity_code = 'DEPRECIATION'
147 AND ctlgd.event_type_code = 'ROLLBACK_DEPRECIATION'
148 AND ds.asset_id = ctlgd.source_id_int_1
149 AND ds.book_type_code = ctlgd.source_id_char_1
150 AND ds.period_counter = ctlgd.source_id_int_2
151 AND ds.deprn_run_id = ctlgd.source_id_int_3
152 AND bc.book_type_code = ctlgd.source_id_char_1
153 -- AND ds.book_type_code = ctlgd.valuation_method
154 AND ds.reversal_event_id = ctlgd.event_id
155 AND dp.book_type_code = ds.book_type_code
156 AND dp.period_counter = ds.period_counter;
157
158
159 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
160 fnd_log.string(G_LEVEL_PROCEDURE,
161 G_MODULE_NAME||l_procedure_name||'.end',
162 'End of procedure');
163 END IF;
164
165 EXCEPTION
166 WHEN others THEN
167 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
168 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
169 fnd_message.set_token('ORACLE_ERR',SQLERRM);
170 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
171 END IF;
172 raise;
173
174 END Load_header_data_rb ;
175
176
177
178 /*======================================================================+
179 | |
180 | Private Function |
181 | load_line_data |
182 | |
183 +======================================================================*/
184
185 PROCEDURE load_line_data IS
186
187 l_procedure_name varchar2(80) := 'load_line_data';
188
189 BEGIN
190
191 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
192 fnd_log.string(G_LEVEL_PROCEDURE,
193 G_MODULE_NAME||l_procedure_name||'.begin',
194 'Beginning of procedure');
195 END IF;
196
197
198 insert into fa_xla_ext_lines_b_gt (
199 EVENT_ID ,
200 LINE_NUMBER ,
201 DISTRIBUTION_ID ,
202 DISTRIBUTION_TYPE_CODE ,
203 LEDGER_ID ,
204 CURRENCY_CODE ,
205 ENTERED_AMOUNT ,
206 BONUS_ENTERED_AMOUNT ,
207 REVAL_ENTERED_AMOUNT ,
208 GENERATED_CCID ,
209 GENERATED_OFFSET_CCID ,
210 BONUS_GENERATED_CCID ,
211 BONUS_GENERATED_OFFSET_CCID ,
212 REVAL_GENERATED_CCID ,
213 REVAL_GENERATED_OFFSET_CCID ,
214 RESERVE_ACCOUNT_CCID ,
215 DEPRN_EXPENSE_ACCOUNT_CCID ,
216 BONUS_RESERVE_ACCT_CCID ,
217 BONUS_EXPENSE_ACCOUNT_CCID ,
218 REVAL_AMORT_ACCOUNT_CCID ,
219 REVAL_RESERVE_ACCOUNT_CCID ,
220 BOOK_TYPE_CODE ,
221 PERIOD_COUNTER ,
222 ASSET_ID,
223 BONUS_DEPRN_EXPENSE_ACCT,
224 BONUS_RESERVE_ACCT,
225 DEPRN_RESERVE_ACCT,
226 REVAL_AMORT_ACCT,
227 REVAL_RESERVE_ACCT,
228 DEPRN_RUN_ID,
229 EXPENSE_ACCOUNT_CCID )
230 select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ctlgd.EVENT_ID ,
231 dd.distribution_id as distribution_id,
232 dd.distribution_id as dist_id,
233 'DEPRN' ,
234 bc.set_of_books_id ,
235 le.currency_code ,
236 dd.deprn_amount
237 - dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
238 dd.bonus_deprn_amount
239 - dd.bonus_deprn_adjustment_amount ,
240 dd.reval_amortization ,
241 dd.deprn_expense_ccid ,
242 dd.deprn_reserve_ccid ,
243 dd.bonus_deprn_expense_ccid ,
244 dd.bonus_deprn_reserve_ccid ,
245 dd.reval_amort_ccid ,
246 dd.reval_reserve_ccid ,
247 cb.RESERVE_ACCOUNT_CCID ,
248 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
249 cb.BONUS_RESERVE_ACCT_CCID ,
250 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
251 cb.REVAL_AMORT_ACCOUNT_CCID ,
252 cb.REVAL_RESERVE_ACCOUNT_CCID ,
253 ctlgd.source_id_char_1 ,
254 dd.period_counter ,
255 ad.ASSET_ID,
256 cb.BONUS_DEPRN_EXPENSE_ACCT,
257 cb.BONUS_DEPRN_RESERVE_ACCT,
258 cb.DEPRN_RESERVE_ACCT,
259 cb.REVAL_AMORTIZATION_ACCT,
260 cb.REVAL_RESERVE_ACCT,
261 dd.DEPRN_RUN_ID,
262 dh.CODE_COMBINATION_ID
263 from xla_events_gt ctlgd,
264 fa_deprn_detail dd,
265 fa_distribution_history dh,
266 fa_additions_b ad,
267 fa_asset_history ah,
268 fa_category_books cb,
269 fa_book_controls bc,
270 gl_ledgers le
271 where ctlgd.entity_code = 'DEPRECIATION'
272 AND ctlgd.event_type_code = 'DEPRECIATION'
273 AND dd.asset_id = ctlgd.source_id_int_1
274 AND dd.book_type_code = ctlgd.source_id_char_1
275 AND dd.period_counter = ctlgd.source_id_int_2
276 AND dd.deprn_run_id = ctlgd.source_id_int_3
277 AND ad.asset_id = ctlgd.source_id_int_1
278 AND dd.distribution_id = dh.distribution_id
279 AND ah.asset_id = ctlgd.source_id_int_1
280 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
281 AND nvl(DH.Date_ineffective, SYSDATE) <=
282 nvl(AH.Date_ineffective, SYSDATE)
283 AND cb.category_id = ah.category_id
284 AND cb.book_type_code = ctlgd.source_id_char_1
285 AND ah.asset_type in ('CAPITALIZED', 'GROUP')
286 AND ad.asset_type in ('CAPITALIZED', 'GROUP')
287 AND bc.book_type_code = ctlgd.source_id_char_1
288 AND le.ledger_id = bc.set_of_books_id ;
289
290 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
291 fnd_log.string(G_LEVEL_PROCEDURE,
292 G_MODULE_NAME||l_procedure_name,
293 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
294 END IF;
295
296
297
298 if (fa_xla_extract_util_pkg.G_alc_enabled) then
299
300
301
302 insert into fa_xla_ext_lines_b_gt (
303 EVENT_ID ,
304 LINE_NUMBER ,
305 DISTRIBUTION_ID ,
306 DISTRIBUTION_TYPE_CODE ,
307 LEDGER_ID ,
308 CURRENCY_CODE ,
309 ENTERED_AMOUNT ,
310 BONUS_ENTERED_AMOUNT ,
311 REVAL_ENTERED_AMOUNT ,
312 GENERATED_CCID ,
313 GENERATED_OFFSET_CCID ,
314 BONUS_GENERATED_CCID ,
315 BONUS_GENERATED_OFFSET_CCID ,
316 REVAL_GENERATED_CCID ,
317 REVAL_GENERATED_OFFSET_CCID ,
318 RESERVE_ACCOUNT_CCID ,
319 DEPRN_EXPENSE_ACCOUNT_CCID ,
320 BONUS_RESERVE_ACCT_CCID ,
321 BONUS_EXPENSE_ACCOUNT_CCID ,
322 REVAL_AMORT_ACCOUNT_CCID ,
323 REVAL_RESERVE_ACCOUNT_CCID ,
324 BOOK_TYPE_CODE ,
325 PERIOD_COUNTER ,
326 ASSET_ID,
327 BONUS_DEPRN_EXPENSE_ACCT,
328 BONUS_RESERVE_ACCT,
329 DEPRN_RESERVE_ACCT,
330 REVAL_AMORT_ACCT,
331 REVAL_RESERVE_ACCT,
332 DEPRN_RUN_ID,
333 EXPENSE_ACCOUNT_CCID )
334 select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ctlgd.EVENT_ID ,
335 dd.distribution_id as distribution_id,
336 dd.distribution_id as dist_id,
337 'DEPRN' ,
338 bc.set_of_books_id ,
339 le.currency_code ,
340 dd.deprn_amount
341 - dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
342 dd.bonus_deprn_amount
343 - dd.bonus_deprn_adjustment_amount ,
344 dd.reval_amortization ,
345 dd.deprn_expense_ccid ,
346 dd.deprn_reserve_ccid ,
347 dd.bonus_deprn_expense_ccid ,
348 dd.bonus_deprn_reserve_ccid ,
349 dd.reval_amort_ccid ,
350 dd.reval_reserve_ccid ,
351 cb.RESERVE_ACCOUNT_CCID ,
352 cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
353 cb.BONUS_RESERVE_ACCT_CCID ,
354 cb.BONUS_EXPENSE_ACCOUNT_CCID ,
355 cb.REVAL_AMORT_ACCOUNT_CCID ,
356 cb.REVAL_RESERVE_ACCOUNT_CCID ,
357 ctlgd.source_id_char_1 ,
358 dd.period_counter ,
359 ad.ASSET_ID,
360 cb.BONUS_DEPRN_EXPENSE_ACCT,
361 cb.BONUS_DEPRN_RESERVE_ACCT,
362 cb.DEPRN_RESERVE_ACCT,
363 cb.REVAL_AMORTIZATION_ACCT,
364 cb.REVAL_RESERVE_ACCT,
365 dd.DEPRN_RUN_ID,
366 dh.CODE_COMBINATION_ID
367 from xla_events_gt ctlgd,
368 fa_mc_deprn_detail dd,
369 fa_distribution_history dh,
370 fa_additions_b ad,
371 fa_asset_history ah,
372 fa_category_books cb,
373 fa_mc_book_controls bc,
374 gl_ledgers le
375 where ctlgd.entity_code = 'DEPRECIATION'
376 AND ctlgd.event_type_code = 'DEPRECIATION'
377 AND dd.asset_id = ctlgd.source_id_int_1
378 AND dd.book_type_code = ctlgd.source_id_char_1
379 AND dd.period_counter = ctlgd.source_id_int_2
380 AND dd.deprn_run_id = ctlgd.source_id_int_3
381 AND ad.asset_id = ctlgd.source_id_int_1
382 AND dd.distribution_id = dh.distribution_id
383 AND ah.asset_id = ctlgd.source_id_int_1
384 AND AH.Date_Effective < nvl(DH.Date_ineffective, SYSDATE)
385 AND nvl(DH.Date_ineffective, SYSDATE) <=
386 nvl(AH.Date_ineffective, SYSDATE)
387 AND cb.category_id = ah.category_id
388 AND cb.book_type_code = ctlgd.source_id_char_1
389 AND ah.asset_type in ('CAPITALIZED', 'GROUP')
390 AND ad.asset_type in ('CAPITALIZED', 'GROUP')
391 AND bc.book_type_code = ctlgd.source_id_char_1
392 AND le.ledger_id = bc.set_of_books_id
393 AND dd.set_of_books_id = bc.set_of_books_id;
394
395 end if;
396
397
398 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
399 fnd_log.string(G_LEVEL_PROCEDURE,
400 G_MODULE_NAME||l_procedure_name,
401 'Rows inserted into lines: ' || to_char(SQL%ROWCOUNT));
402 END IF;
403
404
405
406 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
407 fnd_log.string(G_LEVEL_PROCEDURE,
408 G_MODULE_NAME||l_procedure_name||'.end',
409 'End of procedure');
410 END IF;
411
412 EXCEPTION
413 WHEN others THEN
414 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
415 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
416 fnd_message.set_token('ORACLE_ERR',SQLERRM);
417 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
418 END IF;
419 raise;
420
421 end load_line_data;
422
423
424
425 /*======================================================================+
426 | |
427 | Private Function |
428 | load_mls_data |
429 | |
430 +======================================================================*/
431
432 PROCEDURE load_mls_data IS
433
434 l_procedure_name varchar2(80) := 'load_mls_data';
435
436 BEGIN
437
438 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
439 fnd_log.string(G_LEVEL_PROCEDURE,
440 G_MODULE_NAME||l_procedure_name||'.begin',
441 'Beginning of procedure');
442 END IF;
443
444 return;
445
446 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
447 fnd_log.string(G_LEVEL_PROCEDURE,
448 G_MODULE_NAME||l_procedure_name,
449 'Rows inserted into mls: ' || to_char(SQL%ROWCOUNT));
450 END IF;
451
452
453
454 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
455 fnd_log.string(G_LEVEL_PROCEDURE,
456 G_MODULE_NAME||l_procedure_name||'.end',
457 'End of procedure');
458 END IF;
459
460 EXCEPTION
461 WHEN others THEN
462 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
463 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
464 fnd_message.set_token('ORACLE_ERR',SQLERRM);
465 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
466 END IF;
467 raise;
468
469 end load_mls_data;
470
471
472
473 /*======================================================================+
474 | |
475 | Private Function |
476 | Load_Generated_Ccids |
477 | |
478 +======================================================================*/
479
480 ----------------------------------------------------
481 --
482 -- Account Generator Hook
483 --
484 ----------------------------------------------------
485 PROCEDURE Load_Generated_Ccids
486 (p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
487
488 l_mesg_count number := 0;
489 l_mesg_len number;
490 l_mesg varchar2(4000);
491
492 l_procedure_name varchar2(80) := 'fa_xla_extract_def_pkg.load_generated_ccids'; -- BMR make this dynamic on type
493
494 type char_tab_type is table of varchar2(64) index by binary_integer;
495 type num_tab_type is table of number index by binary_integer;
496
497
498
499
500 type deprn_rec_type is record
501 (rowid VARCHAR2(64),
502 book_type_code VARCHAR2(15),
503 distribution_id NUMBER(15),
504 distribution_ccid NUMBER(15),
505 deprn_entered_amount NUMBER,
506 bonus_entered_amount NUMBER,
507 reval_entered_amount NUMBER,
508 generated_ccid NUMBER(15),
509 generated_offset_ccid NUMBER(15),
510 bonus_generated_ccid NUMBER(15),
511 bonus_generated_offset_ccid NUMBER(15),
512 reval_generated_ccid NUMBER(15),
513 reval_generated_offset_ccid NUMBER(15),
514 -- DEPRN_EXPENSE_ACCOUNT_CCID NUMBER(15),
515 DEPRN_RESERVE_ACCOUNT_CCID NUMBER(15),
516 --BONUS_EXP_ACCOUNT_CCID NUMBER(15),
517 BONUS_RSV_ACCOUNT_CCID NUMBER(15),
518 REVAL_AMORT_ACCOUNT_CCID NUMBER(15),
519 REVAL_RSV_ACCOUNT_CCID NUMBER(15),
520 DEPRN_EXPENSE_ACCT VARCHAR2(25),
521 DEPRN_RESERVE_ACCT VARCHAR2(25),
522 BONUS_DEPRN_EXPENSE_ACCT VARCHAR2(25),
523 BONUS_RESERVE_ACCT VARCHAR2(25),
524 REVAL_AMORT_ACCT VARCHAR2(25),
525 REVAL_RESERVE_ACCT VARCHAR2(25)
526 );
527
528 type deprn_tbl_type is table of deprn_rec_type index by binary_integer;
529
530 l_deprn_tbl deprn_tbl_type;
531
532 l_generated_ccid num_tab_type;
533 l_generated_offset_ccid num_tab_type;
534 l_bonus_generated_ccid num_tab_type;
535 l_bonus_generated_offset_ccid num_tab_type;
536 l_reval_generated_ccid num_tab_type;
537 l_reval_generated_offset_ccid num_tab_type;
538 l_rowid char_tab_type;
539
540 l_last_book varchar2(15) := ' ';
541
542 cursor c_deprn is
543 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
544 xl.rowid,
545 xb.book_type_code,
546 xl.distribution_id,
547 xl.EXPENSE_ACCOUNT_CCID,
548 xl.entered_amount,
549 xl.bonus_entered_amount,
550 xl.reval_entered_amount,
551 nvl(xl.GENERATED_CCID, da.DEPRN_EXPENSE_ACCOUNT_CCID),
552 nvl(xl.GENERATED_OFFSET_CCID, da.DEPRN_RESERVE_ACCOUNT_CCID),
553 nvl(xl.BONUS_GENERATED_CCID, da.BONUS_EXP_ACCOUNT_CCID),
554 nvl(xl.BONUS_GENERATED_OFFSET_CCID, da.BONUS_RSV_ACCOUNT_CCID),
555 nvl(xl.REVAL_GENERATED_CCID, da.REVAL_AMORT_ACCOUNT_CCID),
556 nvl(xl.REVAL_GENERATED_OFFSET_CCID, da.REVAL_RSV_ACCOUNT_CCID),
557 -- xl.DEPRN_EXPENSE_ACCOUNT_CCID,
558 xl.RESERVE_ACCOUNT_CCID,
559 -- xl.BONUS_EXP_ACCOUNT_CCID,
560 xl.BONUS_RESERVE_ACCT_CCID,
561 xl.REVAL_AMORT_ACCOUNT_CCID,
562 xl.REVAL_RESERVE_ACCOUNT_CCID,
563 xl.deprn_expense_acct,
564 xl.DEPRN_RESERVE_ACCT,
565 xl.bonus_deprn_expense_acct,
566 xl.BONUS_RESERVE_ACCT,
567 xl.REVAL_AMORT_ACCT,
568 xl.REVAL_RESERVE_ACCT
569 from xla_events_gt xg,
570 fa_xla_ext_headers_b_gt xb,
571 fa_xla_ext_lines_b_gt xl,
572 fa_distribution_accounts da
573 where xg.event_class_code = 'DEPRECIATION'
574 and xb.event_id = xg.event_id
575 and xl.event_id = xg.event_id
576 and xl.distribution_id = da.distribution_id(+)
577 and xl.book_type_code = da.book_type_code(+);
578
579
580 BEGIN
581
582 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
583 fnd_log.string(G_LEVEL_PROCEDURE,
584 G_MODULE_NAME||l_procedure_name||'.begin',
585 'Beginning of procedure');
586 END IF;
587
588 open c_deprn;
589 fetch c_deprn bulk collect into l_deprn_tbl;
590 close c_deprn;
591
592 for i in 1..l_deprn_tbl.count loop
593
594 if (l_last_book <> l_deprn_tbl(i).book_type_code or
595 i = 1) then
596
597 if not (fa_cache_pkg.fazcbc
598 (X_BOOK => l_deprn_tbl(i).book_type_code,
599 P_LOG_LEVEL_REC => p_log_level_rec)) then
600 null;
601
602 end if;
603 l_last_book := l_deprn_tbl(i).book_type_code;
604 end if;
605
606
607 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
608
609 if (l_deprn_tbl(i).generated_ccid is null and
610 l_deprn_tbl(i).deprn_entered_amount <> 0) then
611
612 if (not FA_GCCID_PKG.fafbgcc
613 (X_book_type_code => l_deprn_tbl(i).book_type_code,
614 X_fn_trx_code => 'DEPRN_EXPENSE_ACCT',
615 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
616 X_acct_segval => l_deprn_tbl(i).deprn_expense_acct,
617 X_account_ccid => 0,
618 X_distribution_id => l_deprn_tbl(i).distribution_id,
619 X_rtn_ccid => l_deprn_tbl(i).generated_ccid,
620 P_LOG_LEVEL_REC => p_log_level_rec)) then
621 FA_SRVR_MSG.ADD_MESSAGE
622 (NAME => 'FA_GET_ACCOUNT_CCID',
623 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
624 P_LOG_LEVEL_REC => p_log_level_rec);
625 l_deprn_tbl(i).generated_ccid := -1;
626 end if;
627 end if;
628
629 if (l_deprn_tbl(i).generated_offset_ccid is null and
630 l_deprn_tbl(i).deprn_entered_amount <> 0) then
631
632
633 if (not FA_GCCID_PKG.fafbgcc
634 (X_book_type_code => l_deprn_tbl(i).book_type_code,
635 X_fn_trx_code => 'DEPRN_RESERVE_ACCT',
636 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
637 X_acct_segval => l_deprn_tbl(i).deprn_reserve_acct,
638 X_account_ccid => l_deprn_tbl(i).deprn_reserve_account_ccid,
639 X_distribution_id => l_deprn_tbl(i).distribution_id,
640 X_rtn_ccid => l_deprn_tbl(i).generated_offset_ccid,
641 P_LOG_LEVEL_REC => p_log_level_rec)) then
642
643 FA_SRVR_MSG.ADD_MESSAGE
644 (NAME => 'FA_GET_ACCOUNT_CCID',
645 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
646 P_LOG_LEVEL_REC => p_log_level_rec);
647 l_deprn_tbl(i).generated_offset_ccid := -1;
648 end if;
649 end if;
650
651 if (l_deprn_tbl(i).bonus_generated_ccid is null and
652 l_deprn_tbl(i).bonus_entered_amount <> 0) then
653
654 if (not FA_GCCID_PKG.fafbgcc
655 (X_book_type_code => l_deprn_tbl(i).book_type_code,
656 X_fn_trx_code => 'BONUS_DEPRN_EXPENSE_ACCT',
657 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
658 X_acct_segval => l_deprn_tbl(i).bonus_deprn_expense_acct,
659 X_account_ccid => 0,
660 X_distribution_id => l_deprn_tbl(i).distribution_id,
661 X_rtn_ccid => l_deprn_tbl(i).bonus_generated_ccid,
662 P_LOG_LEVEL_REC => p_log_level_rec)) then
663 FA_SRVR_MSG.ADD_MESSAGE
664 (NAME => 'FA_GET_ACCOUNT_CCID',
665 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
666 P_LOG_LEVEL_REC => p_log_level_rec);
667 l_deprn_tbl(i).bonus_generated_ccid := -1;
668
669 end if;
670 end if;
671
672 if (l_deprn_tbl(i).bonus_generated_offset_ccid is null and
673 l_deprn_tbl(i).bonus_entered_amount <> 0) then
674
675 if (not FA_GCCID_PKG.fafbgcc
676 (X_book_type_code => l_deprn_tbl(i).book_type_code,
677 X_fn_trx_code => 'BONUS_DEPRN_RESERVE_ACCT',
678 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
679 X_acct_segval => l_deprn_tbl(i).bonus_reserve_acct,
680 X_account_ccid => l_deprn_tbl(i).bonus_rsv_account_ccid,
681 X_distribution_id => l_deprn_tbl(i).distribution_id,
682 X_rtn_ccid => l_deprn_tbl(i).bonus_generated_offset_ccid,
683 P_LOG_LEVEL_REC => p_log_level_rec)) then
684 FA_SRVR_MSG.ADD_MESSAGE
685 (NAME => 'FA_GET_ACCOUNT_CCID',
686 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
687 P_LOG_LEVEL_REC => p_log_level_rec);
688 l_deprn_tbl(i).bonus_generated_offset_ccid := -1;
689
690 end if;
691 end if;
692
693
694 if (l_deprn_tbl(i).reval_generated_ccid is null and
695 l_deprn_tbl(i).reval_entered_amount <> 0) then
696
697 if (not FA_GCCID_PKG.fafbgcc
698 (X_book_type_code => l_deprn_tbl(i).book_type_code,
699 X_fn_trx_code => 'REVAL_AMORTIZATION_ACCT',
700 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
701 X_acct_segval => l_deprn_tbl(i).reval_amort_acct,
702 X_account_ccid => l_deprn_tbl(i).reval_amort_account_ccid,
703 X_distribution_id => l_deprn_tbl(i).distribution_id,
704 X_rtn_ccid => l_deprn_tbl(i).reval_generated_ccid,
705 P_LOG_LEVEL_REC => p_log_level_rec)) then
706 FA_SRVR_MSG.ADD_MESSAGE
707 (NAME => 'FA_GET_ACCOUNT_CCID',
708 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
709 P_LOG_LEVEL_REC => p_log_level_rec);
710 l_deprn_tbl(i).reval_generated_ccid := -1;
711 end if;
712 end if;
713
714 if (l_deprn_tbl(i).reval_generated_offset_ccid is null and
715 l_deprn_tbl(i).reval_entered_amount <> 0) then
716
717 if (not FA_GCCID_PKG.fafbgcc
718 (X_book_type_code => l_deprn_tbl(i).book_type_code,
719 X_fn_trx_code => 'REVAL_RESERVE_ACCT',
720 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
721 X_acct_segval => l_deprn_tbl(i).reval_reserve_acct,
722 X_account_ccid => l_deprn_tbl(i).reval_rsv_account_ccid,
723 X_distribution_id => l_deprn_tbl(i).distribution_id,
724 X_rtn_ccid => l_deprn_tbl(i).reval_generated_offset_ccid,
725 P_LOG_LEVEL_REC => p_log_level_rec)) then
726 FA_SRVR_MSG.ADD_MESSAGE
727 (NAME => 'FA_GET_ACCOUNT_CCID',
728 CALLING_FN => 'FA_INS_ADJUST_PKG.fadoflx',
729 P_LOG_LEVEL_REC => p_log_level_rec);
730 l_deprn_tbl(i).reval_generated_offset_ccid := -1;
731
732 end if;
733 end if;
734
735 end loop;
736
737 for i in 1.. l_deprn_tbl.count loop
738
739 l_generated_ccid(i) := l_deprn_tbl(i).generated_ccid;
740 l_generated_offset_ccid(i) := l_deprn_tbl(i).generated_offset_ccid;
741 l_bonus_generated_ccid(i) := l_deprn_tbl(i).bonus_generated_ccid;
742 l_bonus_generated_offset_ccid(i) := l_deprn_tbl(i).bonus_generated_offset_ccid;
743 l_reval_generated_ccid(i) := l_deprn_tbl(i).reval_generated_ccid;
744 l_reval_generated_offset_ccid(i) := l_deprn_tbl(i).reval_generated_offset_ccid;
745 l_rowid(i) := l_deprn_tbl(i).rowid;
746
747 end loop;
748
749 forall i in 1..l_deprn_tbl.count
750 update fa_xla_ext_lines_b_gt
751 set generated_ccid = l_generated_ccid(i),
752 generated_offset_ccid = l_generated_offset_ccid(i),
753 bonus_generated_ccid = l_bonus_generated_ccid(i),
754 bonus_generated_offset_ccid = l_bonus_generated_offset_ccid(i),
755 reval_generated_ccid = l_reval_generated_ccid(i),
756 reval_generated_offset_ccid = l_reval_generated_offset_ccid(i)
757 where rowid = l_rowid(i);
758
759
760 --
761
762 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
763 fnd_log.string(G_LEVEL_PROCEDURE,
764 G_MODULE_NAME||l_procedure_name||'.end',
765 'End of procedure');
766 END IF;
767
768 EXCEPTION
769 WHEN others THEN
770 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
771 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
772 fnd_message.set_token('ORACLE_ERR',SQLERRM);
773 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
774 END IF;
775 raise;
776
777 END load_generated_ccids;
778
779
780
781 /*======================================================================+
782 | |
783 | Private Function |
784 | Lock_Data |
785 | |
786 +======================================================================*/
787
788 --------------------------------------------------
789 -- Locking Routine --
790 --------------------------------------------------
791
792 PROCEDURE Lock_Data IS
793
794 TYPE number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
795 l_lock number_tbl_type;
796 l_procedure_name varchar2(80) := 'lock_data';
797
798 BEGIN
799
800 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
801 fnd_log.string(G_LEVEL_PROCEDURE,
802 G_MODULE_NAME||l_procedure_name||'.begin',
803 'Beginning of procedure');
804 END IF;
805
806
807 --
808 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
809 fnd_log.string(G_LEVEL_PROCEDURE,
810 G_MODULE_NAME||l_procedure_name||'.end',
811 'End of procedure');
812 END IF;
813
814 EXCEPTION
815 WHEN others THEN
816 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
817 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
818 fnd_message.set_token('ORACLE_ERR',SQLERRM);
819 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
820 END IF;
821 raise;
822
823
824 END Lock_Data;
825
826
827
828 /*======================================================================+
829 | |
830 | Public Function |
831 | Lock_Data |
832 | |
833 +======================================================================*/
834
835 --------------------------------------------------
836 -- Main Load Routine --
837 --------------------------------------------------
838 PROCEDURE load_data IS
839
840 l_log_level_rec FA_API_TYPES.log_level_rec_type;
841 l_use_fafbgcc varchar2(25);
842 l_procedure_name varchar2(80) := 'load_data'; -- BMR make this dynamic on type
843 error_found EXCEPTION;
844
845 BEGIN
846
847 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
848 fnd_log.string(G_LEVEL_PROCEDURE,
849 G_MODULE_NAME||l_procedure_name||'.begin',
850 'Beginning of procedure');
851 END IF;
852
853
854
855 if (fa_xla_extract_util_pkg.G_deprn_exists) then
856 Lock_Data;
857 Load_header_data;
858 Load_line_data;
859 Load_mls_data;
860
861
862
863
864 fnd_profile.get ('FA_WF_GENERATE_CCIDS', l_use_fafbgcc);
865 if (nvl(l_use_fafbgcc, 'N') = 'Y') then
866 if (NOT fa_util_pub.get_log_level_rec (
867 x_log_level_rec => l_log_level_rec)) then raise error_found;
868 end if;
869
870 Load_Generated_Ccids
871 (p_log_level_rec => l_log_level_rec);
872 end if;
873
874
875
876
877 end if;
878
879 if (fa_xla_extract_util_pkg.G_rollback_deprn_exists) then
880 Load_header_data_rb;
881 end if;
882
883
884
885
886 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
887 fnd_log.string(G_LEVEL_PROCEDURE,
888 G_MODULE_NAME||l_procedure_name||'.end',
889 'End of procedure');
890 END IF;
891
892 EXCEPTION
893 WHEN error_found THEN
894 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
895 FND_LOG.string (G_LEVEL_ERROR,
896 G_MODULE_NAME||l_procedure_name,
897 'ended in error');
898 END IF;
899 raise;
900
901 WHEN others THEN
902 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
903 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
904 fnd_message.set_token('ORACLE_ERR',SQLERRM);
905 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
906 END IF;
907 raise;
908
909 END load_data;
910
911
912
913 END FA_XLA_EXTRACT_DEPRN_PKG;
914