[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_EXTRACT_PKG
Source
1 PACKAGE BODY IGI_IAC_EXTRACT_PKG AS
2 /* $Header: igiacexb.pls 120.0.12000000.4 2007/11/03 13:56:02 vkilambi noship $ */
3 --===========================FND_LOG.START=====================================
4
5 g_state_level NUMBER ;
6 g_proc_level NUMBER ;
7 g_event_level NUMBER ;
8 g_excep_level NUMBER ;
9 g_error_level NUMBER ;
10 g_unexp_level NUMBER ;
11 g_path VARCHAR2(1000) ;
12
13 --===========================FND_LOG.END=====================================
14
15 PROCEDURE extract
16 (p_application_id IN number,
17 p_accounting_mode IN varchar2) IS
18
19 Begin
20 extract_revaluations(p_application_id,p_accounting_mode);
21 extract_transactions(p_application_id,p_accounting_mode);
22 --extract_deprn(p_application_id,p_accounting_mode);
23 End extract;
24
25 PROCEDURE extract_revaluations
26 (p_application_id IN number,
27 p_accounting_mode IN varchar2) IS
28
29 l_procedure_name varchar2(80) := 'extract_revaluations';
30 l_path_name varchar2(2000);
31
32 cursor all_events is
33 select * from xla_events_gt
34 where entity_code = 'TRANSACTIONS'
35 and event_type_code = 'INFLATION_REVALUATION';
36
37 cursor fa_header (p_event_id number,
38 p_reval_id number,
39 p_book_type_code varchar2) is
40 SELECT
41 th.EVENT_ID ,
42 bc.BOOK_TYPE_CODE ,
43 bc.BOOK_TYPE_NAME ,
44 bc.ORG_ID ,
45 th.revaluation_period ,
46 decode(bc.GL_POSTING_ALLOWED_FLAG,'YES', 'Y', 'N') gl_transfer_flag,
47 sysdate acc_date--Need to replace with period close date
48 FROM fa_book_controls bc,
49 igi_iac_revaluations th
50 WHERE th.book_type_code = bc.book_type_code
51 and th.book_type_code = p_book_type_code
52 and th.event_id = p_event_id
53 AND th.revaluation_id = p_reval_id;
54
55 cursor fa_igi_lines (p_event_id number,
56 p_book_type_code varchar2) is
57 select
58 adj.event_id,
59 adj.book_type_code,
60 th.category_id,
61 adj.asset_id,
62 adj.distribution_id,
63 adj.set_of_books_id,
64 adj.adjustment_id,
65 amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount) amount,
66 adj.dr_cr_flag,
67 adj.adjustment_type,
68 adj.transfer_to_gl_flag,
69 adj.units_assigned,
70 adj.period_counter,
71 adj.adjustment_offset_type,
72 adj.report_ccid,
73 th.transaction_header_id,
74 th.adjustment_id_out,
75 th.transaction_type_code,
76 th.transaction_sub_type,
77 th.transaction_date_entered,
78 th.mass_reference_id,
79 th.adj_deprn_start_date,
80 th.adjustment_status,
81 th.revaluation_type_flag,
82 lkp_adj.meaning adj_meaning,
83 lkp_trn.meaning trn_meaning,
84 decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null) BL_RESERVE,
85 decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null) OP_EXPENSE,
86 decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null) GENERAL_FUND,
87 decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null) REVAL_RESERVE,
88 decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null) REVAL_RSV_RET,
89 decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null) INTERCO_AP,
90 decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null) INTERCO_AR,
91 decode(adj.adjustment_type,'COST',code_combination_id,null) COST,
92 decode(adj.adjustment_type,'RESERVE',code_combination_id,null) RESERVE,
93 decode(adj.adjustment_type,'EXPENSE',code_combination_id,null) EXPENSE,
94 decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null) NBV_RETIRED,
95 sob.currency_code
96 from igi_iac_adjustments adj, igi_iac_transaction_headers th,
97 igi_lookups lkp_adj, igi_lookups lkp_trn, gl_sets_of_books sob
98 where adj.adjustment_id = th.adjustment_id
99 and adj.event_id = th.event_id
100 and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
101 and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
102 and adj.adjustment_type = lkp_adj.lookup_code
103 and th.transaction_type_code = lkp_trn.lookup_code
104 and th.book_type_code = p_book_type_code
105 and th.event_id = p_event_id
106 and adj.set_of_books_id = sob.set_of_books_id
107 and adj.transfer_to_gl_flag = 'Y';
108
109 v_counter number := 0;
110
111 BEGIN
112 l_path_name := g_path || l_procedure_name;
113 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
114 p_full_path => l_path_name,
115 p_string => 'extract_revaluation....Welcome... ');
116
117 for i in all_events loop
118 --Extract Fa transaction header object
119 for j in fa_header(i.event_id,i.source_id_int_1,i.valuation_method) loop
120 INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
121 event_id ,
122 BOOK_TYPE_CODE ,
123 BOOK_DESCRIPTION ,
124 ORG_ID ,
125 PERIOD_COUNTER ,
126 TRANSFER_TO_GL_FLAG ,
127 accounting_date )
128 values (
129 j.EVENT_ID ,
130 j.BOOK_TYPE_CODE ,
131 j.BOOK_TYPE_NAME ,
132 j.ORG_ID ,
133 j.revaluation_period ,
134 j.gl_transfer_flag,
135 j.acc_date );
136 end loop; --j
137
138 --Extract Fa transaction lines object
139 v_counter := 1;
140 for k in fa_igi_lines (i.event_id,i.valuation_method) loop
141 INSERT INTO FA_XLA_EXT_LINES_B_GT(
142 EVENT_ID ,
143 LINE_NUMBER ,
144 DISTRIBUTION_TYPE_CODE ,
145 transaction_header_id ,
146 adjustment_line_id ,
147 LEDGER_ID ,
148 BOOK_TYPE_CODE ,
149 ASSET_ID ,
150 CAT_ID ,
151 entered_amount ,
152 currency_code)
153 values (
154 k.event_id,
155 v_counter,
156 'TRX',
157 k.adjustment_id,
158 v_counter,
159 k.set_of_books_id,
160 k.book_type_code,
161 k.asset_id,
162 k.category_id,
163 k.amount,
164 k.currency_code);
165
166 --Extract IAC reference lines object
167 insert into igi_iac_xla_lines_gt (
168 IAC_EVENT_ID,
169 IAC_LINE_NUMBER,
170 IAC_BOOK_TYPE_CODE,
171 IAC_CATEGORY_ID,
172 IAC_ASSET_ID,
173 IAC_DISTRIBUTION_ID,
174 IAC_LEDGER_ID,
175 IAC_ADJUSTMENT_ID,
176 IAC_AMOUNT,
177 IAC_DR_CR_FLAG,
178 IAC_ADJUSTMENT_TYPE,
179 IAC_TRANSFER_TO_GL_FLAG,
180 IAC_UNITS_ASSIGNED,
181 IAC_PERIOD_COUNTER,
182 IAC_ADJUSTMENT_OFFSET_TYPE,
183 IAC_REPORT_CCID,
184 IAC_TRANSACTION_HEADER_ID,
185 IAC_ADJUSTMENT_ID_OUT,
186 IAC_TRANSACTION_TYPE_CODE,
187 IAC_TRANSACTION_SUB_TYPE,
188 IAC_TRANSACTION_DATE_ENTERED,
189 IAC_MASS_REFERENCE_ID,
190 IAC_ADJ_DEPRN_START_DATE,
191 IAC_ADJUSTMENT_STATUS,
192 IAC_REVALUATION_TYPE_FLAG,
193 IAC_ADJUSTMENT_TYPE_MEANING,
194 IAC_TRANSACTION_TYPE_MEANING,
195 IAC_BACKLOG_DEPRN_RSV_CCID,
196 IAC_OPERATING_EXPENSE_CCID,
197 IAC_GENERAL_FUND_CCID,
198 IAC_REVAL_RESERVE_CCID,
199 IAC_REVAL_RESERVE_RET_CCID,
200 IAC_INTERCO_AP_CCID,
201 IAC_INTERCO_AR_CCID,
202 IAC_ASSET_COST_CCID,
203 IAC_DEPRN_RESERVE_CCID,
204 IAC_DEPRN_EXPENSE_CCID,
205 IAC_NBV_RETIRED_GAIN_CCID,
206 IAC_CURRENCY_CODE)
207 values (
208 k.event_id,
209 v_counter,
210 k.book_type_code,
211 k.category_id,
212 k.asset_id,
213 k.distribution_id,
214 k.set_of_books_id,
215 k.adjustment_id,
216 k.amount,
217 k.dr_cr_flag,
218 k.adjustment_type,
219 k.transfer_to_gl_flag,
220 k.units_assigned,
221 k.period_counter,
222 k.adjustment_offset_type,
223 k.report_ccid,
224 k.transaction_header_id,
225 k.adjustment_id_out,
226 k.transaction_type_code,
227 k.transaction_sub_type,
228 k.transaction_date_entered,
229 k.mass_reference_id,
230 k.adj_deprn_start_date,
231 k.adjustment_status,
232 k.revaluation_type_flag,
233 k.adj_meaning,
234 k.trn_meaning,
235 k.BL_RESERVE,
236 k.OP_EXPENSE,
237 k.GENERAL_FUND,
238 k.REVAL_RESERVE,
239 k.REVAL_RSV_RET,
240 k.INTERCO_AP,
241 k.INTERCO_AR,
242 k.COST,
243 k.RESERVE,
244 k.EXPENSE,
245 k.NBV_RETIRED,
246 k.currency_code);
247 v_counter := v_counter + 1;
248 end loop; --k
249 end loop; --i
250
251 --Debug
252 /*delete from igi_iac_xla_lines_gt_tmp;
253 insert into igi_iac_xla_lines_gt_tmp
254 select * from igi_iac_xla_lines_gt;
255
256 delete from FA_XLA_EXT_HEADERS_B_GT_tmp;
257 insert into FA_XLA_EXT_HEADERS_B_GT_tmp
258 select * from FA_XLA_EXT_HEADERS_B_GT;
259
260 delete from FA_XLA_EXT_LINES_B_GT_tmp;
261 INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
262 select * from FA_XLA_EXT_LINES_B_GT;*/
263 --Debug
264
265
266 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
267 p_full_path => l_path_name,
268 p_string => 'extract_revaluations....Bye... ');
269
270
271 END extract_revaluations;
272
273 PROCEDURE extract_transactions
274 (p_application_id IN number,
275 p_accounting_mode IN varchar2) IS
276
277 l_procedure_name varchar2(80) := 'extract_transactions';
278 l_path_name varchar2(2000);
279
280 cursor all_events is
281 select * from xla_events_gt
282 where entity_code in ('TRANSACTIONS','DEPRECIATION')
283 and event_type_code in ('ADDITIONS','ADJUSTMENTS','TRANSFERS',
284 'CATEGORY_RECLASS', 'RETIREMENTS','REINSTATEMENTS','DEPRECIATION');
285 cursor fa_igi_lines (p_event_id number,p_book_type_code varchar2) is
286 select
287 adj.event_id,
288 adj.book_type_code,
289 th.category_id,
290 adj.asset_id,
291 adj.distribution_id,
292 adj.set_of_books_id,
293 adj.adjustment_id,
294 amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount) amount,
295 adj.dr_cr_flag,
296 adj.adjustment_type,
297 adj.transfer_to_gl_flag,
298 adj.units_assigned,
299 adj.period_counter,
300 adj.adjustment_offset_type,
301 adj.report_ccid,
302 th.transaction_header_id,
303 th.adjustment_id_out,
304 th.transaction_type_code,
305 th.transaction_sub_type,
306 th.transaction_date_entered,
307 th.mass_reference_id,
308 th.adj_deprn_start_date,
309 th.adjustment_status,
310 th.revaluation_type_flag,
311 lkp_adj.meaning adj_meaning,
312 lkp_trn.meaning trn_meaning,
313 decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null) BL_RESERVE,
314 decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null) OP_EXPENSE,
315 decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null) GENERAL_FUND,
316 decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null) REVAL_RESERVE,
317 decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null) REVAL_RSV_RET,
318 decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null) INTERCO_AP,
319 decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null) INTERCO_AR,
320 decode(adj.adjustment_type,'COST',code_combination_id,null) COST,
321 decode(adj.adjustment_type,'RESERVE',code_combination_id,null) RESERVE,
322 decode(adj.adjustment_type,'EXPENSE',code_combination_id,null) EXPENSE,
323 decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null) NBV_RETIRED,
324 sob.currency_code
325 from igi_iac_adjustments adj, igi_iac_transaction_headers th,
326 igi_lookups lkp_adj, igi_lookups lkp_trn, gl_sets_of_books sob
327 where adj.adjustment_id = th.adjustment_id
328 and adj.event_id = th.event_id
329 and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
330 and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
331 and adj.adjustment_type = lkp_adj.lookup_code
332 and th.transaction_type_code = lkp_trn.lookup_code
333 and th.book_type_code = p_book_type_code
334 and th.event_id = p_event_id
335 and adj.set_of_books_id = sob.set_of_books_id
336 and adj.transfer_to_gl_flag = 'Y';
337
338 v_counter number;
339
340
341 BEGIN
342 l_path_name := g_path || l_procedure_name;
343 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
344 p_full_path => l_path_name,
345 p_string => 'extract_transactions....Welcome... ');
346 --Extract IAC reference lines object
347 for i in all_events loop
348
349 select max(line_number) +1 into v_counter
350 from FA_XLA_EXT_LINES_B_GT where
351 event_id = i.event_id ;
352
353
354
355 for k in fa_igi_lines (i.event_id,i.valuation_method) loop
356 /*INSERT INTO FA_XLA_EXT_LINES_B_GT(
357 EVENT_ID ,
358 LINE_NUMBER ,
359 DISTRIBUTION_TYPE_CODE ,
360 ledger_id,
361 ASSET_ID ,
362 deprn_run_id ,
363 BOOK_TYPE_CODE ,
364 distribution_id ,
365 entered_amount ,
366 currency_code
367 )
368 values (
369 k.event_id,
370 v_counter,
371 'IAC',
372 k.set_of_books_id,
373 k.asset_id,
374 1,
375 k.book_type_code,
376 k.distribution_id,
377 1,'USD');*/
378
379 insert into fa_xla_ext_lines_b_gt (
380 EVENT_ID ,
381 LINE_NUMBER ,
382 DISTRIBUTION_TYPE_CODE ,
383 LEDGER_ID ,
384 CURRENCY_CODE ,
385 ENTERED_AMOUNT ,
386 BONUS_ENTERED_AMOUNT ,
387 REVAL_ENTERED_AMOUNT ,
388 GENERATED_CCID ,
389 GENERATED_OFFSET_CCID ,
390 BONUS_GENERATED_CCID ,
391 BONUS_GENERATED_OFFSET_CCID ,
392 REVAL_GENERATED_CCID ,
393 REVAL_GENERATED_OFFSET_CCID ,
394 BOOK_TYPE_CODE ,
395 ASSET_ID,
396 BONUS_DEPRN_EXPENSE_ACCT,
397 BONUS_RESERVE_ACCT,
398 DEPRN_RESERVE_ACCT,
399 REVAL_AMORT_ACCT,
400 REVAL_RESERVE_ACCT,
401 DEPRN_RUN_ID,
402 DISTRIBUTION_ID,
403 EXPENSE_ACCOUNT_CCID,
404 TRANSACTION_HEADER_ID,
405 ADJUSTMENT_LINE_ID)
406 select EVENT_ID ,
407 v_counter ,
408 'IAC' ,
409 LEDGER_ID ,
410 CURRENCY_CODE ,
411 ENTERED_AMOUNT ,
412 BONUS_ENTERED_AMOUNT ,
413 REVAL_ENTERED_AMOUNT ,
414 GENERATED_CCID ,
415 GENERATED_OFFSET_CCID ,
416 BONUS_GENERATED_CCID ,
417 BONUS_GENERATED_OFFSET_CCID ,
418 REVAL_GENERATED_CCID ,
419 REVAL_GENERATED_OFFSET_CCID ,
420 BOOK_TYPE_CODE ,
421 ASSET_ID,
422 BONUS_DEPRN_EXPENSE_ACCT,
423 BONUS_RESERVE_ACCT,
424 DEPRN_RESERVE_ACCT,
425 REVAL_AMORT_ACCT,
426 REVAL_RESERVE_ACCT,
427 DEPRN_RUN_ID,
428 DISTRIBUTION_ID,
429 EXPENSE_ACCOUNT_CCID,
430 TRANSACTION_HEADER_ID,
431 ADJUSTMENT_LINE_ID
432 from fa_xla_ext_lines_b_gt
433 where event_id = i.event_id
434 and rownum = 1;
435
436 insert into igi_iac_xla_lines_gt (
437 IAC_EVENT_ID,
438 IAC_LINE_NUMBER,
439 IAC_BOOK_TYPE_CODE,
440 IAC_CATEGORY_ID,
441 IAC_ASSET_ID,
442 IAC_DISTRIBUTION_ID,
443 IAC_LEDGER_ID,
444 IAC_ADJUSTMENT_ID,
445 IAC_AMOUNT,
446 IAC_DR_CR_FLAG,
447 IAC_ADJUSTMENT_TYPE,
448 IAC_TRANSFER_TO_GL_FLAG,
449 IAC_UNITS_ASSIGNED,
450 IAC_PERIOD_COUNTER,
451 IAC_ADJUSTMENT_OFFSET_TYPE,
452 IAC_REPORT_CCID,
453 IAC_TRANSACTION_HEADER_ID,
454 IAC_ADJUSTMENT_ID_OUT,
455 IAC_TRANSACTION_TYPE_CODE,
456 IAC_TRANSACTION_SUB_TYPE,
457 IAC_TRANSACTION_DATE_ENTERED,
458 IAC_MASS_REFERENCE_ID,
459 IAC_ADJ_DEPRN_START_DATE,
460 IAC_ADJUSTMENT_STATUS,
461 IAC_REVALUATION_TYPE_FLAG,
462 IAC_ADJUSTMENT_TYPE_MEANING,
463 IAC_TRANSACTION_TYPE_MEANING,
464 IAC_BACKLOG_DEPRN_RSV_CCID,
465 IAC_OPERATING_EXPENSE_CCID,
466 IAC_GENERAL_FUND_CCID,
467 IAC_REVAL_RESERVE_CCID,
468 IAC_REVAL_RESERVE_RET_CCID,
469 IAC_INTERCO_AP_CCID,
470 IAC_INTERCO_AR_CCID,
471 IAC_ASSET_COST_CCID,
472 IAC_DEPRN_RESERVE_CCID,
473 IAC_DEPRN_EXPENSE_CCID,
474 IAC_NBV_RETIRED_GAIN_CCID,
475 IAC_CURRENCY_CODE)
476 values (
477 k.event_id,
478 v_counter,
479 k.book_type_code,
480 k.category_id,
481 k.asset_id,
482 k.distribution_id,
483 k.set_of_books_id,
484 k.adjustment_id,
485 k.amount,
486 k.dr_cr_flag,
487 k.adjustment_type,
488 k.transfer_to_gl_flag,
489 k.units_assigned,
490 k.period_counter,
491 k.adjustment_offset_type,
492 k.report_ccid,
493 k.transaction_header_id,
494 k.adjustment_id_out,
495 k.transaction_type_code,
496 k.transaction_sub_type,
497 k.transaction_date_entered,
498 k.mass_reference_id,
499 k.adj_deprn_start_date,
500 k.adjustment_status,
501 k.revaluation_type_flag,
502 k.adj_meaning,
503 k.trn_meaning,
504 k.BL_RESERVE,
505 k.OP_EXPENSE,
506 k.GENERAL_FUND,
507 k.REVAL_RESERVE,
508 k.REVAL_RSV_RET,
509 k.INTERCO_AP,
510 k.INTERCO_AR,
511 k.COST,
512 k.RESERVE,
513 k.EXPENSE,
514 k.NBV_RETIRED,
515 k.currency_code);
516 v_counter := v_counter + 1;
517 end loop; --k
518 end loop; --i
519
520
521 --Debug
522 /* delete from igi_iac_xla_lines_gt_tmp;
523 insert into igi_iac_xla_lines_gt_tmp
524 select * from igi_iac_xla_lines_gt;
525
526 delete from FA_XLA_EXT_HEADERS_B_GT_tmp;
527 insert into FA_XLA_EXT_HEADERS_B_GT_tmp
528 select * from FA_XLA_EXT_HEADERS_B_GT;
529
530 delete from FA_XLA_EXT_LINES_B_GT_tmp;
531 INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
532 select * from FA_XLA_EXT_LINES_B_GT;*/
533 --Debug
534
535 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
536 p_full_path => l_path_name,
537 p_string => 'extract_transactions....Bye... ');
538
539 END extract_transactions;
540
541 PROCEDURE extract_deprn
542 (p_application_id IN number,
543 p_accounting_mode IN varchar2) IS
544
545 l_procedure_name varchar2(80) := 'extract_transactions';
546 l_path_name varchar2(2000);
547
548 cursor debug_ref_lines is
549 select * from igi_iac_xla_lines_gt;
550
551 BEGIN
552 l_path_name := g_path || l_procedure_name;
553 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
554 p_full_path => l_path_name,
555 p_string => 'extract_transactions....Welcome... ');
556 --Extract IAC reference lines object
557 insert into igi_iac_xla_lines_gt (
558 IAC_EVENT_ID,
559 IAC_LINE_NUMBER,
560 IAC_BOOK_TYPE_CODE,
561 IAC_CATEGORY_ID,
562 IAC_ASSET_ID,
563 IAC_DISTRIBUTION_ID,
564 IAC_LEDGER_ID,
565 IAC_ADJUSTMENT_ID,
566 IAC_AMOUNT,
567 IAC_DR_CR_FLAG,
568 IAC_ADJUSTMENT_TYPE,
569 IAC_TRANSFER_TO_GL_FLAG,
570 IAC_UNITS_ASSIGNED,
571 IAC_PERIOD_COUNTER,
572 IAC_ADJUSTMENT_OFFSET_TYPE,
573 IAC_REPORT_CCID,
574 IAC_TRANSACTION_HEADER_ID,
575 IAC_ADJUSTMENT_ID_OUT,
576 IAC_TRANSACTION_TYPE_CODE,
577 IAC_TRANSACTION_SUB_TYPE,
578 IAC_TRANSACTION_DATE_ENTERED,
579 IAC_MASS_REFERENCE_ID,
580 IAC_ADJ_DEPRN_START_DATE,
581 IAC_ADJUSTMENT_STATUS,
582 IAC_REVALUATION_TYPE_FLAG,
583 IAC_ADJUSTMENT_TYPE_MEANING,
584 IAC_TRANSACTION_TYPE_MEANING,
585 IAC_BACKLOG_DEPRN_RSV_CCID,
586 IAC_OPERATING_EXPENSE_CCID,
587 IAC_GENERAL_FUND_CCID,
588 IAC_REVAL_RESERVE_CCID,
589 IAC_REVAL_RESERVE_RET_CCID,
590 IAC_INTERCO_AP_CCID,
591 IAC_INTERCO_AR_CCID,
592 IAC_ASSET_COST_CCID,
593 IAC_DEPRN_RESERVE_CCID,
594 IAC_DEPRN_EXPENSE_CCID,
595 IAC_NBV_RETIRED_GAIN_CCID,
596 IAC_CURRENCY_CODE)
597 select
598 adj.event_id,
599 rownum,
600 -- adj.distribution_id,
601 adj.book_type_code,
602 th.category_id,
603 adj.asset_id,
604 adj.distribution_id,
605 adj.set_of_books_id,
606 adj.adjustment_id,
607 amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount),
608 adj.dr_cr_flag,
609 adj.adjustment_type,
610 adj.transfer_to_gl_flag,
611 adj.units_assigned,
612 adj.period_counter,
613 adj.adjustment_offset_type,
614 adj.report_ccid,
615 th.transaction_header_id,
616 th.adjustment_id_out,
617 th.transaction_type_code,
618 th.transaction_sub_type,
619 th.transaction_date_entered,
620 th.mass_reference_id,
621 th.adj_deprn_start_date,
622 th.adjustment_status,
623 th.revaluation_type_flag,
624 lkp_adj.meaning,
625 lkp_trn.meaning,
626 decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null),
627 decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null),
628 decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null),
629 decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null),
630 decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null),
631 decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null),
632 decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null),
633 decode(adj.adjustment_type,'COST',code_combination_id,null),
634 decode(adj.adjustment_type,'RESERVE',code_combination_id,null),
635 decode(adj.adjustment_type,'EXPENSE',code_combination_id,null),
636 decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null),
637 sob.currency_code
638 from igi_iac_adjustments adj, igi_iac_transaction_headers th,
639 igi_lookups lkp_adj, igi_lookups lkp_trn, xla_events_gt ctlgd,
640 gl_sets_of_books sob
641 where adj.adjustment_id = th.adjustment_id
642 and adj.event_id = th.event_id
643 and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
644 and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
645 and adj.adjustment_type = lkp_adj.lookup_code
646 and th.transaction_type_code = lkp_trn.lookup_code
647 and ctlgd.valuation_method = th.book_type_code
648 and ctlgd.event_id = th.event_id
649 and adj.set_of_books_id = sob.set_of_books_id
650 and ctlgd.entity_code ='DEPRECIATION'
651 and ctlgd.event_type_code ='DEPRECIATION'
652 and adj.transfer_to_gl_flag = 'Y';
653
654 --Debug
655 /* delete from igi_iac_xla_lines_gt_tmp;
656 insert into igi_iac_xla_lines_gt_tmp
657 select * from igi_iac_xla_lines_gt;
658
659 delete from FA_XLA_EXT_HEADERS_B_GT_tmp;
660 insert into FA_XLA_EXT_HEADERS_B_GT_tmp
661 select * from FA_XLA_EXT_HEADERS_B_GT;
662
663 delete from FA_XLA_EXT_LINES_B_GT_tmp;
664 INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
665 select * from FA_XLA_EXT_LINES_B_GT;*/
666 --Debug
667
668 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
669 p_full_path => l_path_name,
670 p_string => 'extract_transactions....Bye... ');
671
672 END extract_deprn;
673
674 function amount_switch(p_adj_type varchar2,
675 p_side_flag varchar2,
676 p_amount number)
677 return number is
678 begin
679 if p_adj_type in ('COST','EXPENSE','INTERCO AR','OP EXPENSE') then
680 if p_side_flag = 'DR' then
681 return p_amount;
682 else
683 return p_amount * -1;
684 end if;
685
686 elsif p_adj_type in ('BL RESERVE','RESERVE','GENERAL FUND','INTERCO AP','NBV RETIRED','REVAL RESERVE','REVAL RSV RET') then
687 if p_side_flag = 'CR' then
688 return p_amount;
689 else
690 return p_amount * -1;
691 end if;
692 end if;
693 end;
694
695 BEGIN
696 --===========================FND_LOG.START=====================================
697
698 g_state_level := FND_LOG.LEVEL_STATEMENT;
699 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
700 g_event_level := FND_LOG.LEVEL_EVENT;
701 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
702 g_error_level := FND_LOG.LEVEL_ERROR;
703 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
704 g_path := 'igi.plsql.igi_iac_extract_pkg.';
705
706 --===========================FND_LOG.END=====================================
707
708 END igi_iac_extract_pkg;