[Home] [Help]
PACKAGE BODY: APPS.IGI_IMP_IAC_EXCEP_INER_PKG
Source
1 PACKAGE BODY IGI_IMP_IAC_EXCEP_INER_PKG AS
2 -- $Header: igiiaerb.pls 120.13.12000000.1 2007/08/01 16:15:16 npandya noship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiiaerb.IGI_IMP_IAC_EXCEP_INER_PKG.';
13
14 --===========================FND_LOG.END=====================================
15
16 l_message varchar2(1000);
17 l_concat_asset_key VARCHAR2(200);
18 l_period_name VARCHAR2(15);
19 l_company_name VARCHAR2(30);
20 l_fiscal_year_name VARCHAR2(30);
21 l_currency_code VARCHAR2(15);
22 l_g_loc_struct NUMBER;
23 l_g_asset_key_struct NUMBER;
24 l_g_cat_struct NUMBER;
25 l_concat_loc VARCHAR2(200);
26 l_loc_segs fa_rx_shared_pkg.Seg_Array;
27 l_asset_segs fa_rx_shared_pkg.Seg_Array;
28 l_sob_name VARCHAR2(30);
29 l_asset_cat_id NUMBER(15);
30 l_concat_cat VARCHAR2(500);
31 l_cat_segs fa_rx_shared_pkg.Seg_Array;
32 p_asset_id number;
33
34 FUNCTION get_period_name (p_book IN VARCHAR2
35 ,p_period IN VARCHAR2)
36 RETURN BOOLEAN IS
37
38 CURSOR c_period (cp_bookType VARCHAR2, cp_period VARCHAR2) IS
39 SELECT period_name
40 FROM fa_deprn_periods
41 WHERE Book_Type_Code = cp_bookType
42 AND period_counter = TO_NUMBER(cp_period);
43
44 l_selOk BOOLEAN := FALSE;
45 l_path varchar2(100) := g_path||'get_period_name';
46 BEGIN
47 FOR l_period in c_period (p_book, p_period) LOOP
48 l_period_name := l_period.period_name;
49 l_selOk := TRUE;
50 END LOOP;
51
52 IF NOT l_selOk THEN
53 RETURN FALSE;
54 END IF;
55
56 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_period_name ' || l_period_name);
57 RETURN TRUE;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Exception within "get_period_name" : '|| sqlerrm);
62 RETURN FALSE;
63 END get_period_name;
64
65 FUNCTION get_flex_segments (p_book IN VARCHAR2 )
66 RETURN BOOLEAN IS
67
68 CURSOR c_flex(cp_bookType VARCHAR2) IS
69 SELECT
70 sob.name,
71 sc.Company_Name,
72 sc.Category_Flex_Structure,
73 sc.Location_Flex_Structure,
74 sc.asset_key_flex_structure,
75 bc.Accounting_Flex_Structure,
76 ct.fiscal_year_name,
77 sob.Currency_Code
78 FROM
79 fa_system_controls sc,
80 fa_book_controls bc,
81 gl_sets_of_books sob,
82 fa_calendar_types ct
83 WHERE
84 bc.Book_Type_Code = cp_bookType
85 AND sob.Set_Of_Books_ID = BC.Set_Of_Books_ID
86 AND bc.deprn_calendar = ct.calendar_type;
87
88 l_cat_struct NUMBER;
89 l_loc_struct NUMBER;
90 l_asset_key_struct NUMBER;
91 l_acct_struct NUMBER;
92 l_selOk BOOLEAN := FALSE;
93 l_path varchar2(100) := g_path||'get_flex_segments';
94 BEGIN
95 FOR l_flex in c_flex (p_book) LOOP
96 l_sob_name := l_flex.name;
97 l_company_name := l_flex.Company_Name;
98 l_cat_struct := l_flex.Category_Flex_Structure;
99 l_loc_struct := l_flex.Location_Flex_Structure;
100 l_asset_key_struct := l_flex.asset_key_flex_structure;
101 l_acct_struct := l_flex.Accounting_Flex_Structure;
102 l_fiscal_year_name := l_flex.fiscal_year_name;
103 l_currency_code := l_flex.Currency_Code;
104 l_selOk := TRUE;
105 END LOOP;
106
107 IF NOT l_selOk THEN
108 RETURN FALSE;
109 END IF;
110
111 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_company_name ' || l_company_name);
112 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_cat_struct ' || l_cat_struct);
113 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_loc_struct ' || l_loc_struct);
114 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_asset_key_struct ' || l_asset_key_struct);
115 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_acct_struct ' || l_acct_struct);
116 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_fiscal_year_name ' || l_fiscal_year_name);
117 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'l_currency_code ' || l_currency_code);
118
119 l_g_loc_struct := l_loc_struct;
120 l_g_asset_key_struct := l_asset_key_struct;
121 l_g_cat_struct := l_cat_struct;
122 RETURN TRUE;
123 EXCEPTION
124 WHEN OTHERS THEN
125 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within "get_flex_segments" : '|| sqlerrm);
126 RETURN FALSE;
127
128 END get_flex_segments;
129
130 PROCEDURE run_report (p_book IN VARCHAR2
131 ,p_period IN VARCHAR2
132 ,p_request_id IN NUMBER
133 ,p_retcode OUT NOCOPY NUMBER
134 ,p_errbuf OUT NOCOPY VARCHAR2) IS
135
136 -- bug 3464589, change cursor to obtain depreciation information
137 -- for corporate book
138 CURSOR C_Corp_Book_Info(p_book in varchar2, p_period in varchar2) IS
139 SELECT DISTINCT a.asset_id,
140 a.book_type_code,
141 a.date_placed_in_service,
142 a.life_in_months,
143 nvl(a.cost,0) cost,
144 nvl(a.adjusted_cost,0) adjusted_cost,
145 nvl(a.original_cost,0) original_cost,
146 nvl(a.salvage_value,0) salvage_value,
147 nvl(a.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
148 nvl(a.recoverable_cost,0) recoverable_cost,
149 a.deprn_start_date,
150 a.cost_change_flag,
151 a.rate_adjustment_factor,
152 a.depreciate_flag,
153 a.fully_rsvd_revals_counter,
154 a.period_counter_fully_reserved,
155 a.period_counter_fully_retired ,
156 ad.asset_number,
157 ad.description,
158 b.deprn_reserve,
159 b.ytd_deprn
160 FROM fa_books a,
161 fa_additions ad,
162 fa_deprn_summary b
163 WHERE a.book_type_code = p_book
164 AND ad.asset_id = a.asset_id
165 AND a.date_ineffective IS NULL
166 AND b.asset_id = a.asset_id
167 AND b.book_type_code = a.book_type_code
168 AND b.period_counter = (SELECT MAX(period_counter)
169 FROM fa_deprn_summary
170 WHERE book_type_code = a.book_type_code
171 AND asset_id = a.asset_id);
172
173 Cursor C_Mhca_Book_Info(p_book in varchar2, p_asset_id in number) Is
174 select a.asset_id ,
175 a.book_type_code,
176 a.date_placed_in_service,
177 a.life_in_months,
178 nvl(a.cost,0) cost,
179 nvl(a.adjusted_cost,0) adjusted_cost,
180 nvl(a.original_cost,0) original_cost,
181 nvl(a.salvage_value,0) salvage_value,
182 nvl(a.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
183 nvl(a.recoverable_cost,0) recoverable_cost,
184 a.deprn_start_date,
185 a.cost_change_flag,
186 a.rate_adjustment_factor,
187 a.depreciate_flag,
188 a.fully_rsvd_revals_counter,
189 a.period_counter_fully_reserved,
190 a.period_counter_fully_retired,
191 ad.asset_number,
192 ad.description,
193 ad.asset_category_id,
194 b.period_counter,
195 b.deprn_reserve,
196 b.ytd_deprn
197 from fa_books a ,
198 fa_deprn_summary b,
199 fa_additions ad
200 Where a.book_type_code = p_book
201 and a.asset_id = p_asset_id
202 and ad.asset_id = a.asset_id
203 and a.book_type_code = b.book_type_code
204 and b.asset_id = a.asset_id
205 and b.period_counter = (select max(period_counter)
206 from fa_deprn_summary
207 where book_type_code =p_book
208 and asset_id =p_asset_id)
209 and a.date_ineffective is null;
210
211 Cursor C_Tax_Book_Info (p_book in varchar2) Is
212 Select book_type_code
213 from igi_mhc_book_controls
214 where book_type_code in (Select book_type_code
215 from fa_book_controls
216 where book_class ='TAX'
217 and distribution_source_book =p_book)
218 Order by book_type_code;
219
220 Cursor C_Mhca_Reval_Summary_Info (cp_tax_book in varchar2, p_asset_id in number) Is
221 Select distinct asset_id,
222 nvl(original_cost,0) original_cost,
223 nvl(new_asset_cost,0) new_asset_cost,
224 nvl(new_salvage_value,0) new_salvage_value,
225 original_life,
226 current_life,
227 nvl(old_accum_deprn,0) old_accum_deprn,
228 nvl(new_accum_deprn,0) new_accum_deprn,
229 nvl(old_reval_reserve,0) old_reval_reserve,
230 nvl(new_reval_reserve,0) new_reval_reserve,
231 nvl(new_curr_yr_expense,0) new_curr_yr_expense,
232 nvl(new_backlog_deprn,0) new_backlog_deprn
233 From igi_mhc_reval_summary_pl_v
234 Where book_type_code = cp_tax_book
235 and asset_id = p_asset_id
236 and period_counter = (select Max(period_counter) from igi_mhc_reval_summary_pl_v
237 Where book_type_code = cp_tax_book
238 and asset_id = p_asset_id)
239 and reval_mode = 'INDEXED'
240 and active_flag= 'Y'
241 and Run_mode = 'L';
242
243 Cursor C_Concat_Category_Info(p_book in varchar) Is
244 Select category_id
245 From fa_category_books
246 Where book_type_code = p_book;
247
248 -- bug 3442275, start 1
249 -- get the depreciation aclendar
250 CURSOR c_get_deprn_calendar(cp_book_type_code fa_book_controls.book_type_code%TYPE)
251 IS
252 SELECT deprn_calendar
253 FROM fa_book_controls
254 WHERE book_type_code = cp_book_type_code;
255
256 CURSOR c_get_periods_in_year(cp_calendar_type fa_calendar_types.calendar_type%TYPE)
257 IS
258 SELECT number_per_fiscal_year
259 FROM fa_calendar_types
260 WHERE calendar_type = cp_calendar_type;
261
262 CURSOR c_get_reval_period(cp_book_type_code igi_iac_book_controls.book_type_code%TYPE)
263 IS
264 SELECT period_num_for_catchup
265 FROM igi_iac_book_controls
266 WHERE book_type_code = cp_book_type_code;
267
268 l_deprn_calendar fa_book_controls.deprn_calendar%TYPE;
269 l_num_per_fiscal_year fa_calendar_types.number_per_fiscal_year%TYPE;
270 l_iac_reval_period_num igi_iac_book_controls.period_num_for_catchup%TYPE;
271
272 l_curr_period igi_iac_types.prd_rec;
273
274 l_curr_period_num igi_iac_book_controls.period_num_for_catchup%TYPE;
275 l_curr_fiscal_year fa_deprn_periods.fiscal_year%TYPE;
276 l_curr_prd_counter fa_deprn_periods.period_counter%TYPE;
277 l_reval_prd_counter fa_deprn_periods.period_counter%TYPE;
278
279 l_dpis_period igi_iac_types.prd_rec;
280 l_dpis_prd_counter fa_deprn_periods.period_counter%TYPE;
281 l_ret BOOLEAN;
282 -- bug 3442275, end 1
283
284 l_initial_flag varchar2(1) :='N';
285 l_path varchar2(100) := g_path||'run_report';
286
287 BEGIN
288
289 delete from IGI_IMP_IAC_EXCEP_REP_ITF ;
290
291 -- mh, If get_flex_segments (p_book)THEN
292 IF NOT get_flex_segments (p_book) THEN
293 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Failed to get the Flex Segments period name - will continue.... ');
294 End If;
295
296 -- mh, If get_period_name ( p_book, p_period)THEN
297 IF NOT get_period_name ( p_book, p_period) THEN
298 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Failed to get the period name - will continue.... ');
299 End If;
300
301
302 -- bug 3442275, start 2
303 -- get the depreciation calendar
304 OPEN c_get_deprn_calendar(p_book);
305 FETCH c_get_deprn_calendar INTO l_deprn_calendar;
306 IF c_get_deprn_calendar%NOTFOUND THEN
307 RAISE NO_DATA_FOUND;
308 END IF;
309 CLOSE c_get_deprn_calendar;
310
311 -- get the period numbers per fiscal year for the depreciation calendar
312 OPEN c_get_periods_in_year(l_deprn_calendar);
313 FETCH c_get_periods_in_year INTO l_num_per_fiscal_year;
314 IF c_get_periods_in_year%NOTFOUND THEN
315 RAISE NO_DATA_FOUND;
316 END IF;
317 CLOSE c_get_periods_in_year;
318
319 -- get the iac revlaution period number
320 OPEN c_get_reval_period(p_book);
321 FETCH c_get_reval_period INTO l_iac_reval_period_num;
322 IF c_get_reval_period%NOTFOUND THEN
323 l_iac_reval_period_num := 0;
324 END IF;
325 CLOSE c_get_reval_period;
326
327 -- process only if asset has been registered as IAC Book
328 IF (l_iac_reval_period_num > 0) THEN
329 -- get period information for the current open period
330 l_ret := igi_iac_common_utils.get_period_info_for_name(p_book,
331 l_period_name,
332 l_curr_period);
333
334 l_curr_period_num := l_curr_period.period_num;
335 l_curr_fiscal_year := l_curr_period.fiscal_year;
336 l_curr_prd_counter := l_curr_period.period_counter;
337
338 -- calculate the period counter for the reval period of the
339 -- current fiscal year
340 l_reval_prd_counter := l_curr_period.fiscal_year*l_num_per_fiscal_year
341 + l_iac_reval_period_num;
342
343 -- if l_reval_prd_counter is greater than current period counter
344 -- get the previous reval period counter
345 IF (l_reval_prd_counter > l_curr_prd_counter) THEN
346 l_reval_prd_counter := l_reval_prd_counter - l_num_per_fiscal_year;
347 END IF;
348 END IF;
349 -- bug 3442275, end 2
350
351 -- For C_Corp_Book_Info_Rec in C_Corp_Book_Info(p_book, p_period) Loop
352 For C_Corp_Book_Info_Rec in C_Corp_Book_Info(p_book, p_period) Loop
353 l_initial_flag := 'N';
354
355 For C_Tax_Book_Info_Rec in C_Tax_Book_Info(C_Corp_Book_Info_Rec.book_type_code) Loop
356
357 For C_Mhca_Book_Info_Rec In C_Mhca_Book_Info(C_Tax_Book_Info_Rec.book_type_code, C_Corp_Book_Info_Rec.asset_id) Loop
358
359 l_asset_cat_id := C_Mhca_Book_Info_Rec.asset_category_id;
360
361 -- for you new cu
362 -- This will get the CONCATANATED LOCATION
363 /* mh, commentiong out as conactenated location not in ITF table
364 fa_rx_shared_pkg.concat_location (
365 struct_id => l_g_loc_struct
366 ,ccid => C_Mhca_Book_Info_Rec.location_id
367 ,concat_string => l_concat_loc
368 ,segarray => l_loc_segs); */
369
370 -- This gets the CONCATENATED CATEGORY NAME
371 fa_rx_shared_pkg.concat_category (
372 struct_id => l_g_cat_struct,
373 ccid => l_asset_cat_id,
374 concat_string => l_concat_cat,
375 segarray => l_cat_segs);
376
377
378 -- This will get the CONCATANATED ASSETKEY
379 /* mh, commenting out as conactenated asset key not in ITF table
380 fa_rx_shared_pkg.concat_asset_key (
381 struct_id => l_g_asset_key_struct
382 ,ccid => l_asset_cat_id
383 ,concat_string => l_concat_asset_key
384 ,segarray => l_asset_segs); */
385
386 -- bug 3442275, start 3
387 -- process only if asset has been registered as IAC Book
388 IF (l_iac_reval_period_num > 0) THEN
389
390 -- get the period information for the asset DPIS
391 l_ret:= igi_iac_common_utils.get_period_info_for_date(C_Mhca_Book_Info_Rec.book_type_code,
392 C_Mhca_Book_Info_Rec.date_placed_in_service,
393 l_dpis_period);
394
395 -- get the DPIS period counter
396 l_dpis_prd_counter := l_dpis_period.period_counter;
397
398 IF (l_dpis_prd_counter > l_reval_prd_counter AND
399 l_dpis_prd_counter <= l_curr_prd_counter) THEN
400
401 -- list the asset as an exception
402 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_DPIS_REVAL_EXCEPTION');
403 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
404 l_message := Fnd_message.get;
405
406 INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
407 request_id,
408 sob_book,
409 organisation_name,
410 corp_book,
411 tax_book,
412 PERIOD ,
413 FISCAL_YEAR_NAME ,
414 warning_message ,
415 warning_message_code ,
416 ASSET_NUMBER,
417 ASSET_DESCRIPTION ,
418 FUNCTIONAL_CURRENCY_CODE,
419 DPIS_CORP,
420 DPIS_TAX ,
421 ASSET_LIFE_CORP ,
422 ASSET_LIFE_TAX ,
423 CONCAT_CATEGORY ,
424 HIST_COST_CORP ,
425 HIST_COST_TAX ,
426 SALVAGE_VALUE_CORP ,
427 SALVAGE_VALUE_TAX )
428 VALUES (
429 p_request_id,
430 l_sob_name,
431 l_company_name,
432 C_Corp_Book_Info_Rec.book_type_code,
433 C_Tax_Book_Info_Rec.book_type_code,
434 l_period_name,
435 l_fiscal_year_name,
436 l_message,
437 'P',
438 C_Mhca_Book_Info_Rec.asset_number,
439 C_Mhca_Book_Info_Rec.description,
440 l_currency_code,
441 C_Corp_Book_Info_Rec.date_placed_in_service,
442 C_Mhca_Book_Info_Rec.date_placed_in_service,
443 C_Corp_Book_Info_Rec.life_in_months,
444 C_Mhca_Book_Info_Rec.life_in_months ,
445 l_concat_cat,
446 C_Corp_Book_Info_Rec.original_cost,
447 C_Mhca_Book_Info_Rec.original_cost ,
448 C_Corp_Book_Info_Rec.salvage_value,
449 C_Mhca_Book_Info_Rec.salvage_value );
450
451 l_initial_flag := 'Y';
452 END IF;
453 END IF; -- registered as IAC book
454 -- bug 3442275, 3nd 3
455
456 IF l_initial_flag ='N' then
457 If C_Mhca_Book_Info_Rec.period_counter_fully_retired is not null then
458 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_FULLY_RETRIED');
459 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
460 l_message := Fnd_message.get;
461
462 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
463 request_id,
464 sob_book,
465 organisation_name,
466 corp_book,
467 tax_book,
468 PERIOD ,
469 FISCAL_YEAR_NAME ,
470 warning_message ,
471 warning_message_code ,
472 ASSET_NUMBER,
473 ASSET_DESCRIPTION ,
474 FUNCTIONAL_CURRENCY_CODE,
475 DPIS_CORP,
476 DPIS_TAX ,
477 ASSET_LIFE_CORP ,
478 ASSET_LIFE_TAX ,
479 CONCAT_CATEGORY ,
480 HIST_COST_CORP ,
481 HIST_COST_TAX ,
482 SALVAGE_VALUE_CORP ,
483 SALVAGE_VALUE_TAX )
484 values (
485 p_request_id,
486 l_sob_name,
487 l_company_name,
488 C_Corp_Book_Info_Rec.book_type_code,
489 C_Tax_Book_Info_Rec.book_type_code,
490 l_period_name,
491 l_fiscal_year_name,
492 l_message,
493 'K',
494 C_Mhca_Book_Info_Rec.asset_number,
495 C_Mhca_Book_Info_Rec.description,
496 l_currency_code,
497 C_Corp_Book_Info_Rec.date_placed_in_service,
498 C_Mhca_Book_Info_Rec.date_placed_in_service,
499 C_Corp_Book_Info_Rec.life_in_months,
500 C_Mhca_Book_Info_Rec.life_in_months ,
501 l_concat_cat,
502 C_Corp_Book_Info_Rec.original_cost,
503 C_Mhca_Book_Info_Rec.original_cost ,
504 C_Corp_Book_Info_Rec.salvage_value,
505 C_Mhca_Book_Info_Rec.salvage_value );
506
507 l_initial_flag := 'Y';
508 End if;
509 END IF;
510
511 IF l_initial_flag ='N' then
512 If sign(C_Mhca_Book_Info_Rec.cost) = -1 then
513 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_NEGATIVE_COST');
514 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
515 l_message := Fnd_message.get;
516
517 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
518 request_id,
519 sob_book,
520 organisation_name,
521 corp_book,
522 tax_book,
523 PERIOD,
524 FISCAL_YEAR_NAME,
525 warning_message ,
526 warning_message_code ,
527 ASSET_NUMBER,
528 ASSET_DESCRIPTION ,
529 FUNCTIONAL_CURRENCY_CODE,
530 DPIS_CORP ,
531 DPIS_TAX ,
532 ASSET_LIFE_CORP ,
533 ASSET_LIFE_TAX ,
534 CONCAT_CATEGORY ,
535 HIST_COST_CORP ,
536 HIST_COST_TAX ,
537 SALVAGE_VALUE_CORP ,
538 SALVAGE_VALUE_TAX)
539 values
540 (p_request_id,
541 l_sob_name,
542 l_company_name,
543 C_Corp_Book_Info_Rec.book_type_code,
544 C_Tax_Book_Info_Rec.book_type_code,
545 l_period_name,
546 l_fiscal_year_name,
547 l_message,
548 'M',
549 C_Mhca_Book_Info_Rec.asset_number,
550 C_Mhca_Book_Info_Rec.description,
551 l_currency_code,
552 C_Corp_Book_Info_Rec.date_placed_in_service,
553 C_Mhca_Book_Info_Rec.date_placed_in_service,
554 C_Corp_Book_Info_Rec.life_in_months,
555 C_Mhca_Book_Info_Rec.life_in_months,
556 l_concat_cat,
557 C_Corp_Book_Info_Rec.original_cost,
558 C_Mhca_Book_Info_Rec.original_cost,
559 C_Corp_Book_Info_Rec.salvage_value,
560 C_Mhca_Book_Info_Rec.salvage_value );
561 l_initial_flag := 'Y';
562
563 End if;
564 End if;
565
566 IF l_initial_flag ='N' then
567 If (C_Corp_Book_Info_Rec.life_in_months - C_Mhca_Book_Info_Rec.life_in_months )<> 0 then
568 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_ASSET_LIFE');
569 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
570 l_message := Fnd_message.get;
571
572 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
573 request_id,
574 sob_book,
575 organisation_name,
576 corp_book,
577 tax_book,
578 PERIOD,
579 FISCAL_YEAR_NAME,
580 warning_message ,
581 warning_message_code ,
582 ASSET_NUMBER,
583 ASSET_DESCRIPTION ,
584 FUNCTIONAL_CURRENCY_CODE,
585 DPIS_CORP ,
586 DPIS_TAX ,
587 ASSET_LIFE_CORP ,
588 ASSET_LIFE_TAX ,
589 CONCAT_CATEGORY ,
590 HIST_COST_CORP ,
591 HIST_COST_TAX ,
592 SALVAGE_VALUE_CORP ,
593 SALVAGE_VALUE_TAX)
594 values
595 (p_request_id,
596 l_sob_name,
597 l_company_name,
598 C_Corp_Book_Info_Rec.book_type_code,
599 C_Tax_Book_Info_Rec.book_type_code,
600 l_period_name,
601 l_fiscal_year_name,
602 l_message,
603 'L',
604 C_Mhca_Book_Info_Rec.asset_number,
605 C_Mhca_Book_Info_Rec.description,
606 l_currency_code, -- mh, l_concat_cat,
607 C_Corp_Book_Info_Rec.date_placed_in_service,
608 C_Mhca_Book_Info_Rec.date_placed_in_service,
609 C_Corp_Book_Info_Rec.life_in_months,
610 C_Mhca_Book_Info_Rec.life_in_months,
611 l_concat_cat, -- mh, l_concat_loc,
612 C_Corp_Book_Info_Rec.original_cost,
613 C_Mhca_Book_Info_Rec.original_cost,
614 C_Corp_Book_Info_Rec.salvage_value,
615 C_Mhca_Book_Info_Rec.salvage_value );
616 l_initial_flag := 'Y';
617
618 End if;
619 End if;
620
621 IF l_initial_flag ='N' then
622 If trunc(C_Mhca_Book_Info_Rec.date_placed_in_service) <> trunc(C_Corp_Book_Info_Rec.date_placed_in_service)then
623 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_ASSET_DPIS');
624 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
625 l_message := Fnd_message.get;
626
627 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
628 request_id,
629 sob_book,
630 organisation_name,
631 corp_book,
632 tax_book,
633 PERIOD,
634 FISCAL_YEAR_NAME,
635 warning_message ,
636 warning_message_code ,
637 ASSET_NUMBER,
638 ASSET_DESCRIPTION ,
639 FUNCTIONAL_CURRENCY_CODE,
640 DPIS_CORP ,
641 DPIS_TAX ,
642 ASSET_LIFE_CORP ,
643 ASSET_LIFE_TAX ,
644 CONCAT_CATEGORY ,
645 HIST_COST_CORP ,
646 HIST_COST_TAX ,
647 SALVAGE_VALUE_CORP ,
648 SALVAGE_VALUE_TAX)
649 values
650 (p_request_id,
651 l_sob_name,
652 l_company_name,
653 C_Corp_Book_Info_Rec.book_type_code,
654 C_Tax_Book_Info_Rec.book_type_code,
655 l_period_name,
656 l_fiscal_year_name,
657 l_message,
658 'D',
659 C_Mhca_Book_Info_Rec.asset_number,
660 C_Mhca_Book_Info_Rec.description,
661 l_currency_code,
662 C_Corp_Book_Info_Rec.date_placed_in_service,
663 C_Mhca_Book_Info_Rec.date_placed_in_service,
664 C_Corp_Book_Info_Rec.life_in_months,
665 C_Mhca_Book_Info_Rec.life_in_months,
666 l_concat_cat,
667 C_Corp_Book_Info_Rec.original_cost,
668 C_Mhca_Book_Info_Rec.original_cost,
669 C_Corp_Book_Info_Rec.salvage_value,
670 C_Mhca_Book_Info_Rec.salvage_value);
671 l_initial_flag := 'Y';
672
673 End if;
674 End if;
675
676 IF l_initial_flag ='N' then
677 If (C_Mhca_Book_Info_Rec.salvage_value - C_Corp_Book_Info_Rec.salvage_value) <> 0 then
678 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_ASSET_SALVAGE');
679 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
680 l_message := Fnd_message.get;
681
682 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
683 request_id,
684 sob_book,
685 organisation_name,
686 corp_book,
687 tax_book,
688 PERIOD,
689 FISCAL_YEAR_NAME,
690 warning_message ,
691 warning_message_code ,
692 ASSET_NUMBER,
693 ASSET_DESCRIPTION ,
694 FUNCTIONAL_CURRENCY_CODE,
695 DPIS_CORP ,
696 DPIS_TAX ,
697 ASSET_LIFE_CORP ,
698 ASSET_LIFE_TAX ,
699 CONCAT_CATEGORY ,
700 HIST_COST_CORP ,
701 HIST_COST_TAX ,
702 SALVAGE_VALUE_CORP ,
703 SALVAGE_VALUE_TAX)
704 values
705 (p_request_id,
706 l_sob_name,
707 l_company_name,
708 C_Corp_Book_Info_Rec.book_type_code,
709 C_Tax_Book_Info_Rec.book_type_code,
710 l_period_name,
711 l_fiscal_year_name,
712 l_message,
713 'S',
714 C_Mhca_Book_Info_Rec.asset_number,
715 C_Mhca_Book_Info_Rec.description,
716 l_currency_code,
717 C_Corp_Book_Info_Rec.date_placed_in_service,
718 C_Mhca_Book_Info_Rec.date_placed_in_service,
719 C_Corp_Book_Info_Rec.life_in_months,
720 C_Mhca_Book_Info_Rec.life_in_months,
721 l_concat_cat,
722 C_Corp_Book_Info_Rec.original_cost,
723 C_Mhca_Book_Info_Rec.original_cost,
724 C_Corp_Book_Info_Rec.salvage_value,
725 C_Mhca_Book_Info_Rec.salvage_value );
726 l_initial_flag := 'Y';
727 End if;
728 End if;
729
730 IF l_initial_flag ='N' then
731 -- bug 3464589, start 1
732 -- bug 3451572, start 1
733 -- If (C_Mhca_Book_Info_Rec.depreciate_flag ='N'
734 IF (C_Corp_Book_Info_Rec.depreciate_flag ='NO' OR
735 C_Mhca_Book_Info_Rec.depreciate_flag = 'NO') THEN
736 -- bug 3451572, end 1
737 IF (nvl(C_Mhca_Book_Info_Rec.deprn_reserve,0) <> 0
738 OR nvl(C_Mhca_Book_Info_Rec.ytd_deprn,0) <> 0
739 OR nvl(c_corp_book_info_rec.deprn_reserve,0) <> 0
740 OR nvl(c_corp_book_info_rec.ytd_deprn,0) <> 0) THEN
741 -- bug 3464589, end 1
742
743 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_NONDEPRN_ASSET');
744 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
745 l_message := Fnd_message.get;
746
747 INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
748 request_id,
749 sob_book,
750 organisation_name,
751 corp_book,
752 tax_book,
753 PERIOD,
754 FISCAL_YEAR_NAME,
755 warning_message ,
756 warning_message_code ,
757 ASSET_NUMBER,
758 ASSET_DESCRIPTION ,
759 FUNCTIONAL_CURRENCY_CODE,
760 DPIS_CORP ,
761 DPIS_TAX ,
762 ASSET_LIFE_CORP ,
763 ASSET_LIFE_TAX ,
764 CONCAT_CATEGORY ,
765 HIST_COST_CORP ,
766 HIST_COST_TAX ,
767 SALVAGE_VALUE_CORP ,
768 SALVAGE_VALUE_TAX)
769 VALUES
770 (p_request_id,
771 l_sob_name,
772 l_company_name,
773 C_Corp_Book_Info_Rec.book_type_code,
774 C_Tax_Book_Info_Rec.book_type_code,
775 l_period_name,
776 l_fiscal_year_name,
777 l_message,
778 'L',
779 C_Mhca_Book_Info_Rec.asset_number,
780 C_Mhca_Book_Info_Rec.description,
781 l_currency_code,
782 C_Corp_Book_Info_Rec.date_placed_in_service,
783 C_Mhca_Book_Info_Rec.date_placed_in_service,
784 C_Corp_Book_Info_Rec.life_in_months,
785 C_Mhca_Book_Info_Rec.life_in_months,
786 l_concat_cat,
787 C_Corp_Book_Info_Rec.original_cost,
788 C_Mhca_Book_Info_Rec.original_cost,
789 C_Corp_Book_Info_Rec.salvage_value,
790 C_Mhca_Book_Info_Rec.salvage_value);
791 l_initial_flag := 'Y';
792
793 END IF; -- check depreciation amounts
794 -- bug 3464589, start 2
795 END IF; -- check depreciate flag
796 -- bug 3464589, end 2
797 END IF;
798
799 For C_Mhca_Reval_Summary_Info_Rec In C_Mhca_Reval_Summary_Info(C_Tax_Book_Info_Rec.book_type_code, C_Corp_Book_Info_Rec.asset_id ) Loop
800
801 IF l_initial_flag ='N' then
802 If (abs((nvl(C_Mhca_Reval_Summary_Info_Rec.new_asset_cost,0) - nvl(C_Mhca_Reval_Summary_Info_Rec.new_reval_reserve,0))
803 - C_Corp_Book_Info_Rec.cost ) > 0.05 ) then
804
805 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_ASSET_COST');
806 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
807 l_message := Fnd_message.get;
808
809 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
810 request_id,
811 sob_book,
812 organisation_name,
813 corp_book,
814 tax_book,
815 PERIOD,
816 FISCAL_YEAR_NAME,
817 warning_message ,
818 warning_message_code ,
819 ASSET_NUMBER,
820 ASSET_DESCRIPTION ,
821 FUNCTIONAL_CURRENCY_CODE,
822 DPIS_CORP ,
823 DPIS_TAX ,
824 ASSET_LIFE_CORP ,
825 ASSET_LIFE_TAX ,
826 CONCAT_CATEGORY ,
827 HIST_COST_CORP ,
828 HIST_COST_TAX ,
829 SALVAGE_VALUE_CORP ,
830 SALVAGE_VALUE_TAX)
831 values
832 (p_request_id,
833 l_sob_name,
834 l_company_name,
835 C_Corp_Book_Info_Rec.book_type_code,
836 C_Tax_Book_Info_Rec.book_type_code,
837 l_period_name,
838 l_fiscal_year_name,
839 l_message,
840 'L',
841 C_Mhca_Book_Info_Rec.asset_number,
842 C_Mhca_Book_Info_Rec.description,
843 l_currency_code,
844 C_Corp_Book_Info_Rec.date_placed_in_service,
845 C_Mhca_Book_Info_Rec.date_placed_in_service,
846 C_Corp_Book_Info_Rec.life_in_months,
847 C_Mhca_Book_Info_Rec.life_in_months,
848 l_concat_cat,
849 C_Corp_Book_Info_Rec.original_cost,
850 C_Mhca_Book_Info_Rec.original_cost,
851 C_Corp_Book_Info_Rec.salvage_value,
852 C_Mhca_Book_Info_Rec.salvage_value);
853 l_initial_flag := 'Y';
854
855 End if;
856 End if;
857 End Loop; -- end Mhca Reval Summary loop
858 End Loop; -- MHca Book Info loop end
859 End Loop; -- Tax Book Info loop end
860
861 -- bug 3442275, start 4
862 -- process only if asset has been registered as IAC Book
863 IF l_initial_flag ='N' THEN
864 IF (l_iac_reval_period_num > 0) THEN
865
866 -- get the period information for the asset DPIS
867 l_ret := igi_iac_common_utils.get_period_info_for_date(C_Corp_Book_Info_Rec.book_type_code,
868 C_Corp_Book_Info_Rec.date_placed_in_service,
869 l_dpis_period);
870
871 -- get the DPIS period counter
872 l_dpis_prd_counter := l_dpis_period.period_counter;
873
874 IF (l_dpis_prd_counter > l_reval_prd_counter AND
875 l_dpis_prd_counter <= l_curr_prd_counter) THEN
876
877 -- list the asset as an exception
878 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_DPIS_REVAL_EXCEPTION');
879 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,TRUE);
880 l_message := Fnd_message.get;
881
882 INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
883 request_id,
884 sob_book,
885 organisation_name,
886 corp_book,
887 tax_book,
888 PERIOD ,
889 FISCAL_YEAR_NAME ,
890 warning_message ,
891 warning_message_code ,
892 ASSET_NUMBER,
893 ASSET_DESCRIPTION ,
894 FUNCTIONAL_CURRENCY_CODE,
895 DPIS_CORP,
896 DPIS_TAX ,
897 ASSET_LIFE_CORP ,
898 ASSET_LIFE_TAX ,
899 CONCAT_CATEGORY ,
900 HIST_COST_CORP ,
901 HIST_COST_TAX ,
902 SALVAGE_VALUE_CORP ,
903 SALVAGE_VALUE_TAX )
904 VALUES (
905 p_request_id,
906 l_sob_name,
907 l_company_name,
908 C_Corp_Book_Info_Rec.book_type_code,
909 NULL,
910 l_period_name,
911 l_fiscal_year_name,
912 l_message,
913 'P',
914 C_Corp_Book_Info_Rec.asset_number,
915 C_Corp_Book_Info_Rec.description,
916 l_currency_code,
917 C_Corp_Book_Info_Rec.date_placed_in_service,
918 NULL,
919 C_Corp_Book_Info_Rec.life_in_months,
920 NULL,
921 l_concat_cat,
922 C_Corp_Book_Info_Rec.original_cost,
923 NULL,
924 C_Corp_Book_Info_Rec.salvage_value,
925 NULL);
926
927 l_initial_flag := 'Y';
928 END IF;
929 END IF; -- process if IAC Book only
930 END IF; -- l_initial_flag
931 -- bug 3442275, end 4
932
933
934 -- bug 3476361, start 1
935 IF l_initial_flag ='N' THEN
936 -- bug 3476361, end 1
937 If C_Corp_Book_Info_Rec.period_counter_fully_retired is not null then
938 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_FULLY_RETRIED');
939 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
940 l_message := Fnd_message.get;
941
942 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
943 request_id,
944 sob_book,
945 organisation_name,
946 corp_book,
947 tax_book,
948 PERIOD ,
949 FISCAL_YEAR_NAME ,
950 warning_message ,
951 warning_message_code ,
952 ASSET_NUMBER,
953 ASSET_DESCRIPTION ,
954 FUNCTIONAL_CURRENCY_CODE,
955 DPIS_CORP,
956 DPIS_TAX ,
957 ASSET_LIFE_CORP ,
958 ASSET_LIFE_TAX ,
959 CONCAT_CATEGORY ,
960 HIST_COST_CORP ,
961 HIST_COST_TAX ,
962 SALVAGE_VALUE_CORP ,
963 SALVAGE_VALUE_TAX )
964 values
965 (p_request_id,
966 l_sob_name,
967 l_company_name,
968 C_Corp_Book_Info_Rec.book_type_code,
969 NULL,
970 l_period_name,
971 l_fiscal_year_name,
972 l_message,
973 'K',
974 C_Corp_Book_Info_Rec.asset_number,
975 C_Corp_Book_Info_Rec.description,
976 l_currency_code,
977 C_Corp_Book_Info_Rec.date_placed_in_service,
978 NULL,
979 C_Corp_Book_Info_Rec.life_in_months ,
980 NULL ,
981 l_concat_cat, -- l_concat_loc,
982 C_Corp_Book_Info_Rec.original_cost,
983 NULL ,
984 C_Corp_Book_Info_Rec.salvage_value,
985 NULL);
986 l_initial_flag := 'Y';
987
988 End if;
989 -- bug 3476361, start 2
990 END IF;
991 -- bug 3476361, end 2
992
993 IF l_initial_flag ='N' then
994 If sign(C_Corp_Book_Info_Rec.cost) = -1 then
995 FND_MESSAGE.SET_Name ('IGI', 'IGI_IMP_NEGATIVE_COST');
996 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
997 l_message := Fnd_message.get;
998
999
1000 insert into IGI_IMP_IAC_EXCEP_REP_ITF (
1001 request_id,
1002 sob_book,
1003 organisation_name,
1004 corp_book,
1005 tax_book,
1006 PERIOD,
1007 FISCAL_YEAR_NAME,
1008 warning_message ,
1009 warning_message_code ,
1010 ASSET_NUMBER,
1011 ASSET_DESCRIPTION ,
1012 FUNCTIONAL_CURRENCY_CODE,
1013 DPIS_CORP ,
1014 DPIS_TAX ,
1015 ASSET_LIFE_CORP ,
1016 ASSET_LIFE_TAX ,
1017 CONCAT_CATEGORY ,
1018 HIST_COST_CORP ,
1019 HIST_COST_TAX ,
1020 SALVAGE_VALUE_CORP ,
1021 SALVAGE_VALUE_TAX)
1022 values
1023 (p_request_id,
1024 l_sob_name,
1025 l_company_name,
1026 C_Corp_Book_Info_Rec.book_type_code,
1027 null,
1028 l_period_name,
1029 l_fiscal_year_name,
1030 l_message,
1031 'M',
1032 C_Corp_Book_Info_Rec.asset_number,
1033 C_Corp_Book_Info_Rec.description,
1034 l_currency_code,
1035 C_Corp_Book_Info_Rec.date_placed_in_service,
1036 NULL,
1037 C_Corp_Book_Info_Rec.life_in_months,
1038 NULL,
1039 l_concat_cat,
1040 C_Corp_Book_Info_Rec.original_cost,
1041 NULL,
1042 C_Corp_Book_Info_Rec.salvage_value,
1043 NULL);
1044 l_initial_flag := 'Y';
1045
1046 End if;
1047 End if;
1048
1049 -- bug 3476361, start 3
1050 -- for non depreciating assets
1051 IF l_initial_flag ='N' THEN
1052
1053 IF (C_Corp_Book_Info_Rec.depreciate_flag ='NO') THEN
1054 IF (nvl(c_corp_book_info_rec.deprn_reserve,0) <> 0
1055 OR nvl(c_corp_book_info_rec.ytd_deprn,0) <> 0) THEN
1056
1057
1058 FND_MESSAGE.SET_NAME ('IGI', 'IGI_IMP_NONDEPRN_ASSET');
1059 igi_iac_debug_pkg.debug_other_msg(g_state_level,l_path,FALSE);
1060 l_message := Fnd_message.get;
1061
1062
1063 INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
1064 request_id,
1065 sob_book,
1066 organisation_name,
1067 corp_book,
1068 tax_book,
1069 PERIOD,
1070 FISCAL_YEAR_NAME,
1071 warning_message ,
1072 warning_message_code ,
1073 ASSET_NUMBER,
1074 ASSET_DESCRIPTION ,
1075 FUNCTIONAL_CURRENCY_CODE,
1076 DPIS_CORP ,
1077 DPIS_TAX ,
1078 ASSET_LIFE_CORP ,
1079 ASSET_LIFE_TAX ,
1080 CONCAT_CATEGORY ,
1081 HIST_COST_CORP ,
1082 HIST_COST_TAX ,
1083 SALVAGE_VALUE_CORP ,
1084 SALVAGE_VALUE_TAX)
1085 VALUES
1086 (p_request_id,
1087 l_sob_name,
1088 l_company_name,
1089 C_Corp_Book_Info_Rec.book_type_code,
1090 null,
1091 l_period_name,
1092 l_fiscal_year_name,
1093 l_message,
1094 'L',
1095 C_Corp_Book_Info_Rec.asset_number,
1096 C_Corp_Book_Info_Rec.description,
1097 l_currency_code,
1098 C_Corp_Book_Info_Rec.date_placed_in_service,
1099 null,
1100 C_Corp_Book_Info_Rec.life_in_months,
1101 null,
1102 l_concat_cat,
1103 C_Corp_Book_Info_Rec.original_cost,
1104 null,
1105 C_Corp_Book_Info_Rec.salvage_value,
1106 null);
1107 l_initial_flag := 'Y';
1108
1109 END IF; -- check depreciation amounts
1110 END IF; -- check depreciate flag
1111 END IF;
1112
1113 -- bug 3476361, end 3
1114 End Loop; -- end Corp Book loop
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 p_errbuf := sqlerrm;
1118 p_retcode := 2;
1119 END run_report;
1120
1121 END IGI_IMP_IAC_EXCEP_INER_PKG ; -- Package Body