[Home] [Help]
PACKAGE BODY: APPS.IGIRX_IMP_IAC_REP
Source
1 PACKAGE BODY IGIRX_IMP_IAC_REP AS
2 -- $Header: igiimrxb.pls 120.10.12000000.1 2007/08/01 16:22:03 npandya noship $
3
4 -- global variables
5
6 --===========================FND_LOG.START=====================================
7
8 g_state_level NUMBER;
9 g_proc_level NUMBER;
10 g_event_level NUMBER;
11 g_excep_level NUMBER;
12 g_error_level NUMBER;
13 g_unexp_level NUMBER;
14 g_path VARCHAR2(100);
15
16 PROCEDURE Debug_Initialize IS
17 BEGIN
18 g_state_level := FND_LOG.LEVEL_STATEMENT;
19 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
20 g_event_level := FND_LOG.LEVEL_EVENT;
21 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
22 g_error_level := FND_LOG.LEVEL_ERROR;
23 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
24 g_path := 'IGI.PLSQL.igiimrxb.igirx_imp_iac_rep.';
25 END Debug_Initialize;
26 --===========================FND_LOG.END=====================================
27
28 -- ====================================================================
29 -- PROCEDURE Recreate_Intf_Data: Procedure will recreate the interface
30 -- data in category -> cost center -> asset grouping
31 -- procedure introduced as a fix for bug 3439808
32 -- ====================================================================
33 PROCEDURE Recreate_Intf_Data(p_book_type_code VARCHAR2,
34 p_request_id NUMBER)
35 IS
36 CURSOR c_get_intf_data(cp_request_id igi_imp_iac_itf.request_id%TYPE,
37 cp_book_type_code igi_imp_iac_itf.book_type_code%TYPE)
38 IS
39 SELECT book_type_code,
40 request_id,
41 functional_currency_code,
42 set_of_books_id,
43 fiscal_year_name,
44 period,
45 organization_name,
46 major_category,
47 minor_category,
48 concat_category,
49 category_id,
50 balancing_segment,
51 cost_center,
52 asset_id,
53 asset_number,
54 asset_description,
55 parent_no,
56 curr_reval_factor,
57 cumm_reval_factor,
58 asset_tag,
59 serial_no,
60 dpis ,
61 life_months,
62 stl_rate,
63 depreciation_method,
64 conc_asset_key,
65 conc_location,
66 sum(REVAL_COST),
67 sum(NET_REVAL_RESERVE),
68 sum(REVAL_RES_BLOG),
69 sum(REVAL_YTD_DEPRN),
70 sum(REVAL_DEPRN_RESERVE),
71 sum(OPER_EXP_BACKLOG),
72 sum(GENERAL_FUND),
73 sum(HIST_COST),
74 sum(HIST_YTD_DEPRN),
75 sum(HIST_ACC_DEPRN),
76 sum(OPER_ACCT)
77 FROM igi_imp_iac_itf
78 WHERE request_id = cp_request_id
79 AND book_type_code = cp_book_type_code
80 GROUP BY book_type_code,
81 request_id,
82 functional_currency_code,
83 set_of_books_id,
84 fiscal_year_name,
85 period,
86 organization_name,
87 major_category,
88 minor_category,
89 concat_category,
90 category_id,
91 balancing_segment,
92 cost_center,
93 asset_id,
94 asset_number,
95 asset_description,
96 parent_no,
97 curr_reval_factor,
98 cumm_reval_factor,
99 asset_tag,
100 serial_no,
101 dpis ,
102 life_months,
103 stl_rate,
104 depreciation_method,
105 conc_asset_key,
106 conc_location;
107
108
109 TYPE type_book_type_code IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
110 TYPE type_curr_code IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
111 TYPE type_fiscal_year IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
112 TYPE type_period IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
113 TYPE type_asset_num IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
114 TYPE type_asset_tag IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
115
116 TYPE type_maj_cat IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
117 TYPE type_min_cat IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
118 TYPE type_bal_seg IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
119 TYPE type_cost_center IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
120 TYPE type_dep_method IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
121
122 TYPE type_serial_no IS TABLE OF VARCHAR2(35) INDEX BY BINARY_INTEGER;
123
124 TYPE type_org_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
125 TYPE type_conc_cat IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
126 TYPE type_asset_desc IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
127 TYPE type_conc_asset_key IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
128 TYPE type_conc_location IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
129
130 TYPE type_sob_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
131 TYPE type_curr_reval IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
132 TYPE type_cumm_reval IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
133 TYPE type_life_months IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
134 TYPE type_stl_rate IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
135 TYPE type_reval_cost IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
136 TYPE type_net_rr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
137 TYPE type_rr_blog IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
138 TYPE type_reval_ytd_dep IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
139 TYPE type_reval_dep_rsv IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
140 TYPE type_op_exp_blog IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
141 TYPE type_gen_fund IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
142 TYPE type_hist_cost IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
143 TYPE type_hist_ytd_dep IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
144 TYPE type_hist_acc_dep IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
145 TYPE type_op_acct IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
146
147 TYPE type_request_id IS TABLE OF NUMBER(15,0) INDEX BY BINARY_INTEGER;
148 TYPE type_cat_id IS TABLE OF NUMBER(15,0) INDEX BY BINARY_INTEGER;
149 TYPE type_asset_id IS TABLE OF NUMBER(15,0) INDEX BY BINARY_INTEGER;
150 TYPE type_parent_no IS TABLE OF NUMBER(15,0) INDEX BY BINARY_INTEGER;
151
152 TYPE type_dpis IS TABLE OF DATE INDEX BY BINARY_INTEGER;
153
154
155 l_book_type_code type_book_type_code;
156 l_request_id type_request_id;
157 l_functional_currency_code type_curr_code;
158 l_set_of_books_id type_sob_id;
159 l_fiscal_year_name type_fiscal_year;
160 l_period type_period;
161 l_organization_name type_org_name;
162 l_major_category type_maj_cat;
163 l_minor_category type_min_cat;
164 l_concat_category type_conc_cat;
165 l_category_id type_cat_id;
166 l_balancing_segment type_bal_seg;
167 l_cost_center type_cost_center;
168 l_asset_id type_asset_id;
169 l_asset_number type_asset_num;
170 l_asset_description type_asset_desc;
171 l_parent_no type_parent_no;
172 l_curr_reval_factor type_curr_reval;
173 l_cumm_reval_factor type_cumm_reval;
174 l_asset_tag type_asset_tag;
175 l_serial_no type_serial_no;
176 l_dpis type_dpis;
177 l_life_months type_life_months;
178 l_stl_rate type_stl_rate;
179 l_depreciation_method type_dep_method;
180 l_conc_asset_key type_conc_asset_key;
181 l_conc_location type_conc_location;
182 l_reval_cost type_reval_cost;
183 l_net_reval_reserve type_net_rr;
184 l_reval_res_blog type_rr_blog;
185 l_reval_ytd_deprn type_reval_ytd_dep;
186 l_reval_deprn_reserve type_reval_dep_rsv;
187 l_oper_exp_backlog type_op_exp_blog;
188 l_general_fund type_gen_fund;
189 l_hist_cost type_hist_cost;
190 l_hist_ytd_deprn type_hist_ytd_dep;
191 l_hist_acc_deprn type_hist_acc_dep;
192 l_oper_acct type_op_acct;
193
194 l_login_id NUMBER;
195 l_user_id NUMBER;
196
197 BEGIN
198
199 l_login_id := fnd_profile.value('LOGIN_ID');
200 l_user_id := fnd_profile.value('USER_ID');
201
202 -- fetch data
203
204 OPEN c_get_intf_data(p_request_id,p_book_type_code);
205 FETCH c_get_intf_data BULK COLLECT INTO l_book_type_code,
206 l_request_id,
207 l_functional_currency_code,
208 l_set_of_books_id,
209 l_fiscal_year_name,
210 l_period,
211 l_organization_name,
212 l_major_category,
213 l_minor_category,
214 l_concat_category,
215 l_category_id,
216 l_balancing_segment,
217 l_cost_center,
218 l_asset_id,
219 l_asset_number,
220 l_asset_description,
221 l_parent_no,
222 l_curr_reval_factor,
223 l_cumm_reval_factor,
224 l_asset_tag,
225 l_serial_no,
226 l_dpis,
227 l_life_months,
228 l_stl_rate,
229 l_depreciation_method,
230 l_conc_asset_key,
231 l_conc_location,
232 l_reval_cost,
233 l_net_reval_reserve,
234 l_reval_res_blog,
235 l_reval_ytd_deprn,
236 l_reval_deprn_reserve,
237 l_oper_exp_backlog,
238 l_general_fund,
239 l_hist_cost,
240 l_hist_ytd_deprn,
241 l_hist_acc_deprn,
242 l_oper_acct;
243
244 -- delete existing data in igi_imp_iac_itf
245 -- for request_id and book_type_code
246 DELETE FROM igi_imp_iac_itf
247 WHERE book_type_code = p_book_type_code
248 AND request_id = p_request_id;
249
250 -- insert the data back into the interface table
251
252 FORALL j IN l_book_type_code.FIRST..l_book_type_code.LAST
253 INSERT INTO IGI_IMP_IAC_ITF( distribution_id ,
254 request_id ,
255 set_of_books_id ,
256 asset_id,
257 category_id,
258 functional_currency_code ,
259 book_type_code ,
260 fiscal_year_name ,
261 period ,
262 cost_center ,
263 asset_number ,
264 asset_description ,
265 major_category ,
266 minor_category ,
267 concat_category ,
268 reval_cost ,
269 net_reval_reserve ,
270 reval_res_blog ,
271 reval_ytd_deprn ,
272 reval_deprn_reserve ,
273 oper_acct ,
274 oper_exp_backlog ,
275 general_fund ,
276 parent_no ,
277 curr_reval_factor ,
278 cumm_reval_factor,
279 asset_tag ,
280 balancing_segment ,
281 serial_no ,
282 dpis ,
283 life_months ,
284 stl_rate ,
285 depreciation_method ,
286 conc_asset_key ,
287 conc_location ,
288 cost_acct ,
289 iac_reval_resv_acct ,
290 deprn_res_acct ,
291 deprn_backlog_acct ,
292 gen_fund_acct ,
293 deprn_exp_acct ,
294 oper_exp_acct ,
295 hist_cost ,
296 hist_ytd_deprn ,
297 hist_acc_deprn,
298 organization_name,
299 created_by,
300 creation_date,
301 last_update_login,
302 last_updated_by,
303 last_update_date)
304 VALUES
305 ( null ,
306 l_request_id(j),
307 l_set_of_books_id(j) ,
308 l_asset_id(j),
309 l_category_id(j),
310 l_functional_currency_code(j) ,
311 l_book_type_code(j) ,
312 l_fiscal_year_name(j),
313 l_period(j) ,
314 l_cost_center(j) ,
315 l_asset_number(j),
316 l_asset_description(j) ,
317 l_major_category(j),
318 l_minor_category(j),
319 l_concat_category(j),
320 l_reval_cost(j),
321 l_net_reval_reserve(j) ,
322 l_reval_res_blog(j),
323 l_reval_ytd_deprn(j) ,
324 l_reval_deprn_reserve(j) ,
325 l_oper_acct(j),
326 l_oper_exp_backlog(j),
327 l_general_fund(j),
328 l_parent_no(j),
329 l_curr_reval_factor(j) ,
330 l_cumm_reval_factor(j) ,
331 l_asset_tag(j) ,
332 l_balancing_segment(j) ,
333 l_serial_no(j) ,
334 l_dpis(j),
335 l_life_months(j) ,
336 NULL ,
337 l_depreciation_method(j) ,
338 l_conc_asset_key(j) ,
339 l_conc_location(j) ,
340 null,
341 null,
342 null,
343 null,
344 null,
345 null ,
346 null,
347 l_hist_cost(j),
348 l_hist_ytd_deprn(j) ,
349 l_hist_acc_deprn(j),
350 l_organization_name(j),
351 l_user_id,
352 sysdate,
353 l_login_id,
354 l_user_id,
355 sysdate) ;
356
357
358 END Recreate_Intf_Data;
359
360 -- ====================================================================
361 -- PROCEDURE Imp: Main procedure that will be called by the RXi
362 -- outer wrapper process for IAC Implementation Reconciliation
363 -- ====================================================================
364 PROCEDURE imp(p_book_type_code VARCHAR2,
365 p_category_struct_id NUMBER,
366 p_category_id NUMBER,
367 p_request_id NUMBER,
368 retcode OUT NOCOPY NUMBER,
369 errbuf OUT NOCOPY VARCHAR2)
370 IS
371
372 -- cursors
373 --get the implementation data.
374
375 CURSOR C_source_book IS
376 SELECT distribution_source_book
377 FROM fa_book_controls
378 WHERE book_type_code=p_book_type_code;
379
380 CURSOR C_YTD (p_asset_id IN NUMBER,
381 p_category_id IN NUMBER)
382 IS
383 SELECT YTD_HIST,YTD_MHCA,asset_number,
384 cost_hist,cost_mhca
385 FROM igi_imp_iac_interface ii
386 WHERE
387 ii.asset_id=p_asset_id AND
388 ii.book_type_code=p_book_type_code AND
389 ii.category_id=p_category_id ;
390
391 CURSOR C_main ( p_category_id in NUMBER,
392 p_distribution_id in NUMBER)
393 IS
394 SELECT nvl(ii.Hist_Salvage_Value,0) Hist_Salvage_Value,
395 nvl(ii.Life_in_Months,0) Life_in_Months,
396 nvl(ct.Corp_Book,' ') Corp_Book,
397 nvl(ii.Cost_Hist * (dh.units_Assigned/ad.current_units),0) cost_hist,
398 nvl(ii.Cost_MHCA * (dh.units_Assigned/ad.current_units) ,0) cost_mhca,
399 nvl(ii.Deprn_Exp_Hist * (dh.units_Assigned/ad.current_units) ,0) deprn_exp_hist,
400 nvl(ii.Deprn_Exp_MHCA * (dh.units_Assigned/ad.current_units) ,0 ) deprn_exp_mhca,
401 nvl(ii.Accum_Deprn_Hist * (dh.units_Assigned/ad.current_units) ,0) Accum_Deprn_Hist,
402 nvl(ii.Accum_Deprn_MHCA * (dh.units_Assigned/ad.current_units) ,0) Accum_Deprn_MHCA,
403 nvl(ii.Reval_Reserve_Hist * (dh.units_Assigned/ad.current_units) ,0) Reval_Reserve_Hist,
404 nvl(ii.Reval_Reserve_MHCA * (dh.units_Assigned/ad.current_units) ,0) Reval_Reserve_MHCA,
405 nvl(ii.Backlog_Hist *(dh.units_Assigned/ad.current_units) ,0) Backlog_Hist,
406 nvl(ii.Backlog_MHCA *(dh.units_Assigned/ad.current_units) ,0) Backlog_MHCA,
407 nvl(ii.General_Fund_HIST * (dh.units_Assigned/ad.current_units) ,0) General_Fund_HIST,
408 nvl(ii.General_Fund_MHCA * (dh.units_Assigned/ad.current_units) ,0) General_Fund_MHCA,
409 nvl(ii.General_Fund_Per_Hist * (dh.units_Assigned/ad.current_units) ,0) General_Fund_Per_Hist,
410 nvl(ii.General_Fund_Per_Mhca * (dh.units_Assigned/ad.current_units) ,0) General_Fund_Per_Mhca,
411 nvl(ii.Operating_Account_Hist * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Hist,
412 nvl(ii.Operating_Account_MHCA * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_MHCA,
413 nvl(ii.Operating_Account_YTD_Hist * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_YTD_Hist,
414 nvl(ii.Operating_Account_YTD_MHCA * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_YTD_MHCA,
415 nvl(ii.Operating_Account_Cost * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Cost,
416 nvl(ii.Operating_Account_Backlog * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Backlog,
417 nvl(ii.NBV_Hist * (dh.units_Assigned/ad.current_units) ,0) NBV_Hist,
418 nvl(ii.NBV_MHCA * (dh.units_Assigned/ad.current_units) ,0) NBV_MHCA
419 FROM Igi_Imp_Iac_Interface ii,
420 Fa_Distribution_History dh,
421 Fa_Additions ad,
422 igi_imp_iac_controls ct
423 WHERE
424 ii.book_type_code=p_book_type_code AND
425 ii.CATEGORY_ID=NVL(P_CATEGORY_ID,ii.CATEGORY_ID) AND
426 ct.book_type_code = ii.book_type_code AND
427 dh.book_type_Code = ct.corp_book AND dh.asset_id = ii.asset_id AND
428 ad.asset_id = ii.asset_id AND
429 dh.distribution_id=p_distribution_id;
430
431 CURSOR Cur_dets(p_max_period_counter in NUMBER)
432 IS
433 SELECT sc.Location_Flex_Structure,
434 bc.Accounting_Flex_Structure,
435 sc.asset_key_flex_structure,
436 sc.company_name,
437 sob.set_of_books_id,
438 sob.name,
439 dp.fiscal_year,
440 sc.Category_Flex_Structure,
441 sob.Currency_Code
442 FROM fa_system_controls sc,
443 gl_sets_of_books sob,
444 fa_book_controls bc,
445 fa_deprn_periods dp
446 WHERE
447 bc.Book_Type_Code = p_book_type_code AND
448 sob.Set_Of_Books_ID = bc.Set_Of_Books_ID AND
449 dp.book_type_code =bc.Book_Type_Code AND
450 dp.period_counter= p_max_period_counter-1;
451
452 CURSOR C_period_counter
453 IS
454 SELECT max(period_counter)
455 FROM igi_imp_iac_controls
456 WHERE Book_Type_Code = p_book_type_code;
457
458 CURSOR C_period (p_max_period_counter in VARCHAR2)
459 IS
460 SELECT period_name
461 FROM fa_deprn_periods
462 WHERE Book_Type_Code = p_book_type_code AND
463 period_counter = p_max_period_counter-1;
464
465 CURSOR C_counter(p_distribution_id in NUMBER
466 ,p_fiscal_year in NUMBER
467 ,p_dist_source_book in VARCHAR2)
468 IS
469 SELECT dp.period_counter
470 FROM fa_deprn_periods dp,
471 fa_distribution_history dh
472 WHERE dh.date_ineffective between dp.period_open_date and dp.period_close_date AND
473 dp.book_type_code=p_book_type_code AND
474 dp.fiscal_year=p_fiscal_year AND
475 dh.book_type_code=p_dist_source_book AND
476 dh.distribution_id=p_distribution_id;
477
478 CURSOR Cur_acct2(p_category_id in NUMBER,
479 p_dist_source_book in VARCHAR2)
480 IS
481 SELECT backlog_deprn_rsv_ccid ,
482 general_fund_ccid ,
483 operating_expense_ccid,
484 reval_rsv_ccid
485 FROM igi_iac_category_books
486 WHERE book_type_code=p_dist_source_book AND
487 category_id=p_category_id;
488
489 CURSOR cur_acct( p_asset_id in NUMBER)
490 IS
491 SELECT ad.asset_key_ccid,
492 ad.description,
493 ad.parent_asset_id,
494 ad.Tag_number,
495 ad.serial_number ,
496 bk.date_placed_in_service ,
497 bk.deprn_method_code
498 FROM fa_books bk,
499 fa_additions ad
500 WHERE ad.asset_id=p_asset_id AND
501 ad.asset_id = bk.asset_id AND
502 bk.transaction_header_id_out is NULL AND
503 bk.book_type_code = p_book_type_code ;
504
505 CURSOR C_dist_acct( p_distribution_id in number)
506 IS
507 SELECT asset_cost_account_ccid ,
508 deprn_expense_account_ccid ,
509 deprn_reserve_account_ccid
510 FROM fa_distribution_accounts
511 WHERE distribution_id=p_distribution_id AND
512 book_type_code=p_book_type_code;
513
514 CURSOR C_deprn( p_fiscal_year in NUMBER,
515 p_asset_id in NUMBER,
516 p_dist_source_book in VARCHAR2)
517 IS
518 SELECT dp.calendar_period_open_date,
519 dp.period_counter,
520 dh.distribution_id,
521 dh.code_combination_id,
522 dh.date_ineffective,
523 dh.location_id
524 FROM fa_distribution_history dh,
525 fa_deprn_periods dp
526 WHERE dh.asset_id= p_asset_id AND
527 dh.book_type_code=p_dist_source_book AND
528 (nvl(dh.date_ineffective,dp.period_open_date)>=dp.period_open_date) AND
529 dp.Book_type_code=p_book_type_code AND
530 dp.fiscal_year=p_fiscal_year AND
531 dp.period_num=(SELECT min(period_num)
532 FROM fa_deprn_periods
533 WHERE fiscal_year=p_fiscal_year AND
534 book_type_code=p_book_type_code);
535
536 CURSOR C_asset(p_category_id in number)
537 IS
538 SELECT asset_id
539 FROM igi_imp_iac_interface
540 WHERE
541 book_type_code=p_book_type_code AND
542 category_id=p_category_id;
543
544 CURSOR C_category(p_category_id in number)
545 IS
546 SELECT distinct category_id
547 FROM
548 igi_imp_iac_interface_ctrl
549 WHERE book_type_code=p_book_type_code AND
550 category_id=nvl(p_category_id,category_id);
551
552
553 CURSOR C_ytd_dist(p_distribution_id in number,
554 p_asset_id in number,
555 p_period_counter in number,
556 p_book_type_code in varchar2)
557 IS
558 SELECT ytd_deprn
559 FROM fa_deprn_detail
560 WHERE distribution_id=p_distribution_id AND
561 book_type_code =p_book_type_code AND
562 asset_id =p_asset_id AND
563 period_counter=p_period_counter ;
564
565 CURSOR C_ytd_dist_non_deprn(p_distribution_id in number,
566 p_asset_id in number,
567 p_book_type_code in varchar2)
568 IS
569 SELECT ytd_deprn
570 FROM fa_deprn_detail
571 WHERE distribution_id=p_distribution_id AND
572 book_type_code =p_book_type_code AND
573 asset_id =p_asset_id AND
574 period_counter=(select max(period_counter)
575 from fa_deprn_detail
576 where asset_id=p_asset_id
577 and distribution_id=p_distribution_id
578 and book_type_code =p_book_type_code);
579
580
581 CURSOR C_ytd_asset(p_asset_id in number,p_max_period_counter in number,p_book_type_code in varchar2)
582 IS
583 SELECT ytd_deprn
584 FROM fa_deprn_summary
585 WHERE asset_id=p_asset_id AND
586 book_type_code=p_book_type_code AND
587 period_counter=p_max_period_counter-1;
588
589 CURSOR C_ytd_asset_non_dep(p_asset_id in number, p_book_type_code in varchar2)
590 IS
591 SELECT ytd_deprn,fiscal_year
592 FROM fa_deprn_summary ds, fa_deprn_periods dp
593 WHERE ds.asset_id=p_asset_id AND
594 ds.book_type_code=p_book_type_code AND
595 ds.book_type_code = dp.book_type_code and
596 ds.period_counter= (select max(period_counter)
597 from fa_deprn_summary
598 where asset_id=p_asset_id
599 and book_type_code=p_book_type_code )
600 and ds.period_counter=dp.period_counter;
601
602
603 CURSOR c_inactive_dist_cat( cp_book_type_code varchar2,
604 cp_asset_id number,
605 cp_distribution_id number) IS
606 SELECT ah.category_id
607 FROM fa_asset_history ah,
608 fa_distribution_history dh
609 WHERE dh.book_type_code = cp_book_type_code AND
610 dh.asset_id = cp_asset_id AND
611 dh.distribution_id = cp_distribution_id AND
612 dh.asset_id = ah.asset_id AND
613 dh.date_ineffective BETWEEN
614 ah.date_effective AND nvl(ah.date_ineffective,sysdate);
615
616
617 Cursor C_asset_derpn_Info(cp_book_type_code varchar2,
618 cp_asset_id number) Is
619 Select depreciate_flag
620 From fa_books bk
621 Where bk.book_type_code = cp_book_type_code
622 and bk.asset_id =cp_asset_id
623 and bk.transaction_header_id_out is null;
624
625
626 --variables
627 l_dist_source_book varchar2(15);
628 l_cat_segs fa_rx_shared_pkg.seg_array;
629 l_loc_segs fa_rx_shared_pkg.seg_array;
630 l_asset_segs fa_rx_shared_pkg.seg_array;
631 l_asset_key_flex_struct fa_system_controls.asset_key_flex_structure%type;
632 l_cat_flex_struct fa_system_controls.category_flex_structure%type;
633 l_loc_flex_struct fa_system_controls.location_flex_structure%type;
634 l_accounting_flex_struct fa_book_controls.accounting_flex_structure%type ;
635 l_set_of_books_id fa_book_controls.set_of_books_id%type;
636 l_currency_code gl_sets_of_books.currency_code%type;
637 l_organization_name gl_sets_of_books.name%type;
638 l_ytd_hist number;
639 l_asset_ytd_hist number;
640 l_max_period_counter number;
641 l_inactive_counter number;
642 l_fiscal_year number(4);
643 l_category_id number(15);
644 l_asset_id number(15);
645 l_cost_hist number(15);
646 l_cost_mhca number(15);
647 l_current_reval_factor number;
648 l_cumulative_reval_factor number;
649 l_deprn_expense_acct varchar2(30);
650 l_asset_cost_acct varchar2(30);
651 l_deprn_reserve_acct varchar2(30);
652 l_reval_rsv_acct varchar2(30);
653 l_blog_deprn_rsv_acct varchar2(30);
654 l_general_fund_acct varchar2(30);
655 l_oper_expense_acct varchar2(30);
656 l_cost_center varchar2(25);
657 l_company_name varchar2(30);
658 l_asset_number varchar2(15);
659 l_balancing_seg varchar2(15);
660 l_concat_asset_key varchar2(500);
661 l_major_category varchar2(50);
662 l_minor_category varchar2(50);
663 l_concat_category varchar2(500);
664 l_concat_location varchar2(500);
665 l_period_name varchar2(15);
666 l_ytd_mhca number;
667 l_asset_ytd_mhca number;
668 l_location_id number;
669 l_dist_ytd_deprn number;
670 l_asset_ytd_deprn number;
671 l_category c_category%rowtype;
672 l_asset c_asset%rowtype;
673 l_dist_acct c_dist_acct%rowtype;
674 l_acct2 cur_acct2%rowtype;
675 l_acct cur_acct%rowtype;
676 l_main c_main%rowtype;
677 l_deprn c_deprn%rowtype;
678 l_login_id NUMBER;
679 l_user_id NUMBER;
680 l_path_name VARCHAR2(150);
681 l_deprn_flag fa_books.depreciate_flag%type;
682 l_curr_fiscal_year fa_deprn_periods.fiscal_year%type;
683 l_asset_fiscal_year fa_deprn_periods.fiscal_year%type;
684 l_YTD_prorate_dists_tab igi_iac_types.prorate_dists_tab;
685 l_YTD_prorate_dists_idx binary_integer;
686 idx_YTD binary_integer;
687 l_ytd_prorate_factor NUMBER;
688
689 BEGIN
690
691 Debug_Initialize;
692
693 l_ytd_hist :=0;
694 l_asset_ytd_hist := 0;
695 l_login_id := fnd_profile.value('LOGIN_ID');
696 l_user_id := fnd_profile.value('USER_ID');
697 l_path_name := g_path||'imp';
698
699 OPEN C_source_book;
700 FETCH C_source_book INTO l_dist_source_book;
701 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
702 p_full_path => l_path_name,
703 p_string => 'after fetch c_dist_source_book ');
704 CLOSE C_source_book;
705
706 OPEN c_period_counter;
707 FETCH c_period_counter into l_max_period_counter;
708 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
709 p_full_path => l_path_name,
710 p_string => 'after fetch c_period_counter ');
711 CLOSE c_period_counter;
712
713 OPEN C_period(l_max_period_counter);
714 FETCH C_period INTO l_period_name;
715 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
716 p_full_path => l_path_name,
717 p_string => 'after fetch c_period ');
718 CLOSE C_period;
719
720 OPEN Cur_dets(l_max_period_counter);
721 FETCH Cur_dets INTO
722 l_loc_flex_struct,
723 l_Accounting_Flex_Struct,
724 l_asset_key_flex_struct,
725 l_company_name,
726 l_set_of_books_id,
727 l_organization_name,
728 l_fiscal_year,
729 l_cat_flex_struct,
730 l_Currency_Code;
731 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
732 p_full_path => l_path_name,
733 p_string => 'after fetch cur_dets ');
734 CLOSE cur_dets;
735
736 l_category_id:=p_category_id;
737
738 FOR l_category IN C_category(l_category_id)
739 LOOP --LOOP 1
740 l_category_id:=l_category.category_id;
741
742 FOR l_asset IN C_Asset(l_category_id )
743 LOOP --LOOP 2
744 l_asset_id:=l_asset.asset_id;
745
746 l_cost_hist:=0;
747 l_cost_mhca:=0;
748 OPEN C_ytd (l_asset_id,
749 l_category_id);
750 FETCH C_ytd
751 INTO l_asset_ytd_hist, l_asset_ytd_mhca, l_asset_number,l_cost_hist,l_cost_mhca;
752 CLOSE C_ytd;
753
754 IF ( nvl( l_cost_hist,0) = 0 ) THEN
755 l_current_reval_factor := 1 ;
756 l_cumulative_reval_factor := 1 ;
757 ELSE
758 l_current_reval_factor := l_cost_mhca/l_cost_hist ;
759 l_cumulative_reval_factor := l_cost_mhca/l_cost_hist ;
760 END IF;
761
762 -- get depreciate flag;
763 l_deprn_flag :='YES';
764 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
765 p_full_path => l_path_name,
766 p_string => 'Get depreciate flag');
767
768 open C_asset_derpn_Info (P_book_type_code,l_asset_id);
769 Fetch C_asset_derpn_Info into l_deprn_flag;
770 Close C_asset_derpn_Info;
771
772 l_asset_ytd_deprn :=0;
773 l_asset_fiscal_year:=Null;
774 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
775 p_full_path => l_path_name,
776 p_string => 'before fetch C_YTD_ASSET');
777
778 OPEN C_ytd_asset ( l_asset_id ,
779 l_max_period_counter,
780 l_dist_source_book);
781 FETCH C_ytd_asset INTO l_asset_ytd_deprn;
782 IF c_ytd_asset%NotFound Then
783 OPEN C_ytd_asset_non_dep ( l_asset_id,l_dist_source_book);
784 FETCH C_ytd_asset_non_dep INTO l_asset_ytd_deprn,l_asset_fiscal_year;
785 If C_ytd_asset_non_dep%Notfound Then
786 l_asset_ytd_deprn:=0;
787 Else
788 if Not l_asset_fiscal_year = l_fiscal_year THen
789 l_asset_ytd_deprn:=0;
790 End If;
791 END IF;
792 close C_ytd_asset_non_dep;
793 END IF;
794 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
795 p_full_path => l_path_name,
796 p_string => 'C_YTD_ASSET'|| l_asset_ytd_deprn);
797 CLOSE C_ytd_asset;
798
799 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
800 p_full_path => l_path_name,
801 p_string => 'after fetch C_YTD_ASSET');
802
803 OPEN cur_acct(l_asset_id);
804 FETCH cur_acct
805 INTO l_acct;
806 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
807 p_full_path => l_path_name,
808 p_string => 'after fetch CUR ACCT');
809 CLOSE cur_acct;
810
811 If l_asset_fiscal_year is not Null Then
812 l_curr_fiscal_year:= l_asset_fiscal_year;
813 Else
814 l_curr_fiscal_year:= l_fiscal_year;
815 End if;
816
817 IF NOT IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD ( fp_asset_id => l_asset_id
818 , fp_book_type_code => l_dist_source_book
819 , fp_current_period_counter => l_max_period_counter - 1
820 , fp_prorate_dists_tab => l_YTD_prorate_dists_tab
821 , fp_prorate_dists_idx => l_YTD_prorate_dists_idx
822 )
823 THEN
824 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path,'+error IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD');
825 END IF;
826
827 FOR l_deprn IN C_Deprn(l_curr_fiscal_year , l_asset_id , l_dist_source_book )
828 LOOP --LOOP 3
829
830 OPEN C_MAIN(l_category_id,l_deprn.distribution_id);
831 FETCH C_Main INTO l_main;
832 CLOSE C_main;
833
834 IF nvl(l_asset_fiscal_year,l_fiscal_year) = l_fiscal_year THEN
835
836 IF(l_deprn.date_ineffective IS NULL) --Active distribution
837 THEN
838 /* Fetching accounts for active distribution */
839 OPEN Cur_acct2(l_category_id,l_dist_source_book);
840 FETCH Cur_Acct2
841 INTO l_acct2;
842 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
843 p_full_path => l_path_name,
844 p_string => 'after fetch CUR ACCT2');
845 CLOSE Cur_Acct2;
846
847 l_dist_ytd_deprn:=0;
848 OPEN C_ytd_dist (l_deprn.distribution_id ,
849 l_asset_id ,
850 l_max_period_counter-1 ,
851 l_dist_source_book);
852 FETCH C_Ytd_Dist
853 INTO l_dist_ytd_deprn;
854 If C_ytd_dist%NotFound THen
855
856 Open C_Ytd_Dist_non_deprn(l_deprn.distribution_id,l_asset_id, l_dist_source_book);
857 Fetch C_Ytd_Dist_non_deprn into l_dist_ytd_deprn;
858 IF C_Ytd_Dist_non_deprn%notfound Then
859 l_dist_ytd_deprn:=0;
860 end if;
861 close C_Ytd_Dist_non_deprn;
862 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
863 p_full_path => l_path_name,
864 p_string => 'after fetch C_YTD_DIST');
865 END IF;
866 CLOSE C_Ytd_Dist;
867
868 ELSE --Inactive Distribution
869 /* Fetching category for inactive dist Bug 3430707 */
870 OPEN c_inactive_dist_cat(l_dist_source_book,l_asset_id,l_deprn.distribution_id);
871 FETCH c_inactive_dist_cat INTO l_category_id;
872 CLOSE c_inactive_dist_cat;
873
874 /* Fetching accounts for inactive distribution */
875 OPEN Cur_acct2(l_category_id,l_dist_source_book);
876 FETCH Cur_Acct2
877 INTO l_acct2;
878 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
879 p_full_path => l_path_name,
880 p_string => 'after fetch CUR ACCT2');
881 CLOSE Cur_Acct2;
882
883 --Assign all currency fields of C_main 0
884 l_main.cost_mhca :=0;
885 l_main.reval_reserve_mhca:=0;
886 l_main.backlog_mhca :=0;
887 l_main.Accum_Deprn_MHCA :=0;
888 l_main.operating_account_cost:=0;
889 l_main.operating_account_backlog :=0;
890 l_main.general_fund_mhca:=0;
891 l_main.cost_hist:=0;
892 l_main.accum_deprn_hist:=0;
893
894 OPEN C_counter( l_deprn.distribution_id,
895 l_fiscal_year,
896 l_dist_source_book);
897 FETCH C_Counter Into l_Inactive_Counter;
898
899 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
900 p_full_path => l_path_name,
901 p_string => 'after fetch C_COUNTER');
902 CLOSE C_counter;
903
904 l_dist_ytd_deprn:=0;
905
906 OPEN C_ytd_dist ( l_deprn.distribution_id ,
907 l_asset_id ,
908 l_inactive_counter,
909 l_dist_source_book);
910 FETCH C_ytd_dist INTO l_dist_ytd_deprn;
911 If C_ytd_dist%NotFound THen
912 Open C_Ytd_Dist_non_deprn(l_deprn.distribution_id,l_asset_id,l_dist_source_book);
913 Fetch C_Ytd_Dist_non_deprn into l_dist_ytd_deprn;
914 IF C_Ytd_Dist_non_deprn%notfound Then
915 l_dist_ytd_deprn:=0;
916 end if;
917 close C_Ytd_Dist_non_deprn;
918 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
919 p_full_path => l_path_name,
920 p_string => 'after fetch C_Ytd_Dist');
921
922 END IF;
923 CLOSE C_Ytd_Dist;
924 END IF;
925 ELSE
926 OPEN Cur_acct2(l_category_id,l_dist_source_book);
927 FETCH Cur_Acct2
928 INTO l_acct2;
929 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
930 p_full_path => l_path_name,
931 p_string => 'after fetch CUR ACCT2');
932 CLOSE Cur_Acct2;
933
934 l_dist_ytd_deprn:=0;
935 END IF;
936
937 if l_deprn_flag = 'YES' THEN
938 IF (nvl(l_asset_ytd_deprn,0)=0)
939 THEN
940 l_ytd_hist:=0;
941 l_ytd_mhca:=0;
942 ELSE
943 l_ytd_hist:=l_asset_ytd_hist*(l_dist_ytd_deprn/l_asset_ytd_deprn);
944
945 l_ytd_prorate_factor := 0;
946 idx_YTD := l_YTD_prorate_dists_tab.FIRST;
947 WHILE idx_YTD <= l_YTD_prorate_dists_tab.LAST LOOP
948 IF l_deprn.distribution_id = l_YTD_prorate_dists_tab(idx_YTD).distribution_id THEN
949 l_ytd_prorate_factor := l_YTD_prorate_dists_tab(idx_YTD).ytd_prorate_factor;
950 EXIT;
951 END IF;
952 idx_ytd := l_YTD_prorate_dists_tab.Next(idx_ytd);
953 END LOOP;
954 l_ytd_mhca:=l_asset_ytd_mhca*l_ytd_prorate_factor;
955
956 END IF;
957 Else
958 l_ytd_hist:=l_dist_ytd_deprn;
959 l_ytd_mhca:=l_dist_ytd_deprn;
960 ENd If;
961
962
963 OPEN C_dist_acct(l_deprn.distribution_id);
964 FETCH C_dist_acct
965 INTO l_dist_acct;
966 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
967 p_full_path => l_path_name,
968 p_string => 'after fetch dist acct');
969 CLOSE C_Dist_Acct;
970
971 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
972 l_dist_acct.deprn_expense_account_ccid,
973 'GL_ACCOUNT',
974 l_deprn_expense_acct) --the expense acct for distribution
975 THEN
976 l_deprn_expense_acct:= 'not_found';
977 END IF;
978
979 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
980 l_acct2.backlog_deprn_rsv_ccid,
981 'GL_ACCOUNT',
982 l_blog_deprn_rsv_acct)
983 THEN
984 l_blog_deprn_rsv_acct:= 'not_found';
985 END IF;
986
987 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
988 l_acct2.general_fund_ccid,
989 'GL_ACCOUNT',
990 l_general_fund_acct)
991 THEN
992 l_general_fund_acct:= 'not_found';
993 END IF;
994
995 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
996 l_acct2.operating_expense_ccid,
997 'GL_ACCOUNT',
998 l_oper_expense_acct)
999 THEN
1000 l_oper_expense_acct:= 'not_found';
1001 END IF;
1002
1003 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
1004 l_dist_acct.deprn_reserve_account_ccid,
1005 'GL_ACCOUNT',
1006 l_deprn_reserve_acct)
1007 THEN
1008 l_deprn_reserve_acct:= 'not_found';
1009 END IF;
1010
1011 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
1012 l_acct2.reval_rsv_ccid,
1013 'GL_ACCOUNT',
1014 l_reval_rsv_acct)
1015 THEN
1016 l_reval_rsv_acct:= 'not_found';
1017 END IF;
1018
1019 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
1020 l_dist_acct.asset_cost_account_ccid,
1021 'GL_ACCOUNT',
1022 l_asset_cost_acct)
1023 THEN
1024 l_asset_cost_acct:= 'not_found';
1025 END IF;
1026
1027 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
1028 l_deprn.code_combination_id,
1029 'FA_COST_CTR',
1030 l_cost_center)
1031 THEN
1032 l_cost_center:= 'not_found';
1033 END IF;
1034
1035 IF NOT igi_iac_common_utils.get_account_segment_value(l_set_of_books_id,
1036 l_deprn.code_combination_id,
1037 'GL_BALANCING',
1038 l_balancing_seg)
1039 THEN
1040 l_balancing_seg := 'not_found';
1041 END IF;
1042
1043 BEGIN
1044 l_major_category := fa_rx_flex_pkg.get_value(
1045 p_application_id => 140,
1046 p_id_flex_code => 'CAT#',
1047 p_id_flex_num => l_asset_key_flex_struct,
1048 p_qualifier => 'BASED_CATEGORY',
1049 p_ccid => l_category_id);
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1053 END;
1054
1055 BEGIN
1056 l_minor_category:= fa_rx_flex_pkg.get_value(
1057 p_application_id => 140,
1058 p_id_flex_code => 'CAT#',
1059 p_id_flex_num => l_asset_key_flex_struct,
1060 p_qualifier => 'MINOR_CATEGORY',
1061 p_ccid => l_category_id);
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1065 END;
1066
1067 --This will get the concatenated category
1068 fa_rx_shared_pkg.concat_category (struct_id => l_cat_flex_struct,
1069 ccid => l_category_id,
1070 concat_string => l_concat_category,
1071 segarray => l_cat_segs);
1072
1073 --This will get the concatenated location
1074 fa_rx_shared_pkg.concat_location (struct_id => l_loc_flex_struct
1075 ,ccid => l_deprn.location_id
1076 ,concat_string => l_concat_location
1077 ,segarray => l_loc_segs);
1078
1079 --This will get the concatenated asset key
1080 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1081 p_full_path => l_path_name,
1082 p_string => 'ccid'||l_deprn.code_combination_id);
1083
1084 fa_rx_shared_pkg.concat_asset_key (struct_id => l_asset_key_flex_struct,
1085 ccid => l_acct.asset_key_ccid,
1086 concat_string => l_concat_asset_key,
1087 segarray => l_asset_segs);
1088
1089 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1090 p_full_path => l_path_name,
1091 p_string => 'l_concat_location ' || l_concat_location);
1092 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1093 p_full_path => l_path_name,
1094 p_string => 'l_concat_asset_key ' || l_concat_asset_key);
1095 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1096 p_full_path => l_path_name,
1097 p_string => 'l_category_id ' || l_category_id);
1098 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1099 p_full_path => l_path_name,
1100 p_string => 'l_concat_category ' || l_concat_category);
1101 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1102 p_full_path => l_path_name,
1103 p_string => 'l_currency_code ' || l_currency_code);
1104 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1105 p_full_path => l_path_name,
1106 p_string => 'l_concat_location ' ||l_concat_location );
1107
1108 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.cost_mhca ,
1109 l_dist_source_book )) THEN
1110 null;
1111 END IF;
1112
1113 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.reval_reserve_mhca ,
1114 l_dist_source_book )) THEN
1115 null;
1116 END IF;
1117
1118 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.backlog_mhca ,
1119 l_dist_source_book )) THEN
1120 null;
1121 END IF;
1122
1123 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_ytd_mhca ,
1124 l_dist_source_book )) THEN
1125 null;
1126 END IF;
1127
1128 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.Accum_Deprn_MHCA ,
1129 l_dist_source_book )) THEN
1130 null;
1131 END IF;
1132
1133 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.operating_account_cost ,
1134 l_dist_source_book )) THEN
1135 null;
1136 END IF;
1137
1138 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.operating_account_backlog ,
1139 l_dist_source_book )) THEN
1140 null;
1141 END IF;
1142
1143 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.general_fund_mhca ,
1144 l_dist_source_book )) THEN
1145 null;
1146 END IF;
1147
1148 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.cost_hist ,
1149 l_dist_source_book )) THEN
1150 null;
1151 END IF;
1152
1153 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_ytd_hist ,
1154 l_dist_source_book )) THEN
1155 null;
1156 END IF;
1157
1158 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_main.accum_deprn_hist ,
1159 l_dist_source_book )) THEN
1160 null;
1161 END IF;
1162
1163 INSERT INTO IGI_IMP_IAC_ITF( distribution_id ,
1164 request_id ,
1165 set_of_books_id ,
1166 asset_id,
1167 category_id,
1168 functional_currency_code ,
1169 book_type_code ,
1170 fiscal_year_name ,
1171 period ,
1172 cost_center ,
1173 asset_number ,
1174 asset_description ,
1175 major_category ,
1176 minor_category ,
1177 concat_category ,
1178 reval_cost ,
1179 net_reval_reserve ,
1180 reval_res_blog ,
1181 reval_ytd_deprn ,
1182 reval_deprn_reserve ,
1183 oper_acct ,
1184 oper_exp_backlog ,
1185 general_fund ,
1186 parent_no ,
1187 curr_reval_factor ,
1188 cumm_reval_factor,
1189 asset_tag ,
1190 balancing_segment ,
1191 serial_no ,
1192 dpis ,
1193 life_months ,
1194 stl_rate ,
1195 depreciation_method ,
1196 conc_asset_key ,
1197 conc_location ,
1198 cost_acct ,
1199 iac_reval_resv_acct ,
1200 deprn_res_acct ,
1201 deprn_backlog_acct ,
1202 gen_fund_acct ,
1203 deprn_exp_acct ,
1204 oper_exp_acct ,
1205 hist_cost ,
1206 hist_ytd_deprn ,
1207 hist_acc_deprn,
1208 organization_name,
1209 created_by,
1210 creation_date,
1211 last_update_login,
1212 last_updated_by,
1213 last_update_date)
1214 VALUES
1215 ( l_deprn.distribution_id ,
1216 p_request_id,
1217 l_set_of_books_id ,
1218 l_asset_id,
1219 l_category_id,
1220 l_currency_code ,
1221 p_book_type_code ,
1222 l_fiscal_year,
1223 l_period_name ,
1224 l_cost_center ,
1225 l_asset_number ,
1226 l_acct.description ,
1227 l_major_category ,
1228 l_minor_category ,
1229 l_concat_category ,
1230 l_main.cost_mhca ,
1231 l_main.reval_reserve_mhca ,
1232 l_main.backlog_mhca ,
1233 l_ytd_mhca ,
1234 l_main.Accum_Deprn_MHCA ,
1235 l_main.operating_account_cost * -1, --Bug 3277826
1236 l_main.operating_account_backlog * -1, --Bug 3277826
1237 l_main.general_fund_mhca,
1238 l_acct.parent_asset_id ,
1239 l_current_reval_factor ,
1240 l_cumulative_reval_factor ,
1241 l_acct.tag_number ,
1242 l_balancing_seg ,
1243 l_acct.serial_number ,
1244 l_acct.date_placed_in_service ,
1245 l_main.life_in_months ,
1246 NULL ,
1247 l_acct.deprn_method_code ,
1248 l_concat_asset_key ,
1249 l_concat_location ,
1250 l_asset_cost_acct,
1251 l_reval_rsv_acct ,
1252 l_deprn_reserve_acct ,
1253 l_blog_deprn_rsv_acct,
1254 l_general_fund_acct,
1255 l_deprn_expense_acct ,
1256 l_oper_expense_acct,
1257 l_main.cost_hist,
1258 l_ytd_hist ,
1259 l_main.accum_deprn_hist,
1260 l_organization_name,
1261 l_user_id,
1262 sysdate,
1263 l_login_id,
1264 l_user_id,
1265 sysdate) ;
1266
1267 /* Resetting category for next active dist Bug 3430707 */
1268 l_category_id:=l_category.category_id;
1269 END LOOP; --END LOOP 3;
1270 END LOOP; --END LOOP 2;
1271 END LOOP; --END LOOP 1;
1272
1273 -- Bug 3439808, start 1
1274 Recreate_Intf_Data(p_book_type_code,
1275 p_request_id);
1276 -- Bug 3439808, end 1
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1281 retcode:=2;
1282 errbuf:='Exception within igirx_imp_iac_rep';
1283
1284 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1285 p_full_path => l_path_name,
1286 p_string => 'l_set_of_books_id ' || l_set_of_books_id);
1287 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1288 p_full_path => l_path_name,
1289 p_string => 'l_period_name ' || l_period_name);
1290 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1291 p_full_path => l_path_name,
1292 p_string => 'l_general_fund_acct ' || l_general_fund_acct);
1293 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1294 p_full_path => l_path_name,
1295 p_string => 'l_operating_exp_acct ' || l_oper_expense_acct);
1296 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1297 p_full_path => l_path_name,
1298 p_string => 'l_balancing_seg ' || l_balancing_seg);
1299 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
1300 p_full_path => l_path_name,
1301 p_string => 'l_book_type_code ' || p_book_type_code);
1302
1303 END IMP;
1304
1305 END igirx_imp_iac_rep;