[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_ADDITIONS_PKG
Source
4 --===========================FND_LOG.START=====================================
1 PACKAGE BODY IGI_IAC_ADDITIONS_PKG AS
2 -- $Header: igiiapab.pls 120.16 2011/04/19 09:42:18 schakkin ship $
3
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.igiiapab.igi_iac_additions_pkg.';
13
14 --===========================FND_LOG.END=====================================
15
16 PROCEDURE Debug_Period(p_period igi_iac_types.prd_rec) IS
17 l_path_name VARCHAR2(150) := g_path||'debug_period';
18 BEGIN
19 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
20 p_full_path => l_path_name,
21 p_string => ' Period counter :'||to_char(p_period.period_counter));
22 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
23 p_full_path => l_path_name,
24 p_string => ' Period Num :'||to_char(p_period.period_num));
25 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
26 p_full_path => l_path_name,
27 p_string => ' Fiscal Year :'||to_char(p_period.fiscal_year));
28 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
29 p_full_path => l_path_name,
30 p_string => ' Period Name :'||p_period.period_name);
31 END Debug_Period;
32
33 PROCEDURE Debug_Asset(p_asset igi_iac_types.iac_reval_input_asset) IS
34 l_path_name VARCHAR2(150) := g_path||'debug_asset';
35 BEGIN
36 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
37 p_full_path => l_path_name,
38 p_string => '==============================================================');
39 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
40 p_full_path => l_path_name,
41 p_string => ' Net book value :'||to_char(p_asset.net_book_value));
42 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
43 p_full_path => l_path_name,
44 p_string => ' Adjusted Cost :'||to_char(p_asset.adjusted_cost));
45 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
46 p_full_path => l_path_name,
47 p_string => ' Operating Account :'||to_char(p_asset.operating_acct));
48 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
49 p_full_path => l_path_name,
50 p_string => ' Reval Reserve :'||to_char(p_asset.reval_reserve));
51 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
52 p_full_path => l_path_name,
53 p_string => ' Deprn Amount :'||to_char(p_asset.deprn_amount));
54 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
55 p_full_path => l_path_name,
56 p_string => ' Deprn Reserve :'||to_char(p_asset.deprn_reserve));
57 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
58 p_full_path => l_path_name,
59 p_string => ' Backlog Deprn Reserve :'||to_char(p_asset.backlog_deprn_reserve));
60 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
61 p_full_path => l_path_name,
62 p_string => ' General Fund :'||to_char(p_asset.general_fund));
63 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
64 p_full_path => l_path_name,
65 p_string => ' Current Reval Factor :'||to_char(p_asset.current_reval_factor));
66 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
67 p_full_path => l_path_name,
68 p_string => ' Cumulative Reval Factor :'||to_char(p_asset.Cumulative_reval_factor));
69 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
70 p_full_path => l_path_name,
71 p_string => '==============================================================');
72 END Debug_Asset;
73
74 /*=========================================================================+
75 | Function Name: |
76 | Is_Default_Index_Found |
77 | |
78 | Description: |
79 | This function finds if the price indexes are defined for all the |
80 | revaluation catchup periods and all periods after final revaluation |
81 | until the current open period. If any of them still has the default |
82 | value 9999.99, then the function returns FALSE. |
83 +=========================================================================*/
84 FUNCTION Is_Default_Index_Found(
85 p_book_type_code VARCHAR2,
86 p_asset_id NUMBER,
87 p_dpis_period_counter NUMBER,
88 p_open_period_counter NUMBER,
89 p_reval_catchup_period NUMBER
90 ) return BOOLEAN IS
91
92 l_period_info igi_iac_types.prd_rec;
93 l_price_index_value NUMBER;
97
94 l_last_reval_period NUMBER DEFAULT NULL;
95 l_path_name VARCHAR2(150) := g_path||'is_default_index_found';
96 BEGIN
98 FOR l_period_counter IN p_dpis_period_counter..(p_open_period_counter-1) LOOP
99
100 l_price_index_value := 0;
101 IF NOT igi_iac_common_utils.get_period_info_for_counter(
102 p_book_type_code,
103 l_period_counter,
104 l_period_info) THEN
105 return FALSE;
106 END IF;
107
108 IF (p_dpis_period_counter = l_period_counter) OR (l_period_info.period_num = p_reval_catchup_period) THEN
109 IF NOT igi_iac_common_utils.get_price_index_value(
110 p_book_type_code,
111 p_asset_id,
112 l_period_info.period_name,
113 l_price_index_value) THEN
114 return FALSE;
115 END IF;
116
117 IF l_price_index_value = 9999.99 THEN
118 return FALSE;
119 END IF;
120
121 IF (l_period_info.period_num = p_reval_catchup_period) THEN
122 l_last_reval_period := l_period_counter;
123 END IF;
124 END IF;
125
126 END LOOP;
127
128 IF l_last_reval_period IS NOT NULL THEN
129 FOR l_period_counter IN (l_last_reval_period+1)..p_open_period_counter LOOP
130
131 l_price_index_value := 0;
132 IF NOT igi_iac_common_utils.get_period_info_for_counter(
133 p_book_type_code,
134 l_period_counter,
135 l_period_info) THEN
136 return FALSE;
137 END IF;
138
139 IF NOT igi_iac_common_utils.get_price_index_value(
140 p_book_type_code,
141 p_asset_id,
142 l_period_info.period_name,
143 l_price_index_value) THEN
144 return FALSE;
145 END IF;
146
147 IF l_price_index_value = 9999.99 THEN
148 return FALSE;
149 END IF;
150 END LOOP;
151 END IF;
152
153 return TRUE;
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
158 return FALSE;
159
160 END Is_Default_Index_Found;
161
162 /*=========================================================================+
163 | Function Name: |
164 | Do_Prior_Addition |
165 | |
166 | Description: |
167 | This function is called from codehook provided from FA Prior |
168 | Additions program. This function calls do_addition which does the |
169 | IAC Prior Addition processing. |
170 | R12 |
171 +=========================================================================*/
172 FUNCTION Do_Prior_Addition(
173 p_book_type_code VARCHAR2,
174 p_asset_id NUMBER,
175 p_category_id NUMBER,
176 p_deprn_method_code VARCHAR2,
177 p_cost NUMBER,
178 p_adjusted_cost NUMBER,
179 p_salvage_value NUMBER,
180 p_current_unit NUMBER,
181 p_life_in_months NUMBER,
182 p_event_id NUMBER, --R12 uptake
183 p_calling_function VARCHAR2
184 ) return BOOLEAN IS
185 l_path_name VARCHAR2(150) := g_path||'do_prior_addition';
186 BEGIN
187 IF NOT Do_Addition(
188 p_book_type_code,
189 p_asset_id,
190 p_category_id,
191 p_deprn_method_code,
192 p_cost,
193 p_adjusted_cost,
194 p_salvage_value,
195 p_current_unit,
196 p_life_in_months,
197 NULL, -- p_deprn_reserve
198 NULL, -- p_deprn_ytd
199 p_calling_function,
200 p_event_id) THEN
201 return FALSE;
202 END IF;
203 return TRUE;
204 EXCEPTION
205 WHEN OTHERS THEN
206 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
207 return FALSE;
208 END Do_Prior_Addition;
209
210 /*=========================================================================+
211 | Function Name: |
212 | Do_Addition_Wrapper |
213 | |
214 | Description: |
215 | This IAC function is for wrapping up the the Do_Prior_Addition() |
216 | procedure. |
217 +=========================================================================*/
218 FUNCTION Do_Addition_Wrapper(
219 p_book_type_code VARCHAR2,
220 p_asset_id NUMBER,
221 p_category_id NUMBER,
222 p_deprn_method_code VARCHAR2,
223 p_cost NUMBER,
224 p_adjusted_cost NUMBER,
225 p_salvage_value NUMBER,
226 p_current_unit NUMBER,
227 p_life_in_months NUMBER,
228 p_event_id NUMBER,
229 p_calling_function VARCHAR2
230 ) return BOOLEAN IS
234 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
231 l_path_name VARCHAR2(150) := g_path||'Do_Addition_Wrapper';
232 BEGIN
233
235 p_full_path => l_path_name,
236 p_string => '********************************************');
237 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
238 p_full_path => l_path_name,
239 p_string => ' Start of Do_Addition_Wrapper ');
240 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
241 p_full_path => l_path_name,
242 p_string => '********************************************');
243
244 IF NOT Do_Prior_Addition(
245 p_book_type_code,
246 p_asset_id,
247 p_category_id,
248 p_deprn_method_code,
249 p_cost,
250 p_adjusted_cost,
251 p_salvage_value,
252 p_current_unit,
253 p_life_in_months,
254 p_event_id,
255 p_calling_function) THEN
256 return FALSE;
257 END IF;
258 return TRUE;
259 EXCEPTION
260 WHEN OTHERS THEN
261 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
262 return FALSE;
263 END;
264
265 /*=========================================================================+
266 | Function Name: |
267 | Do_Addition |
268 | |
269 | Description: |
270 | This function calculates IAC catchup amounts for the asset added in |
271 | prior period. This function is called from do_prior_addition and |
272 | IAC Implementation Data Preparation program. This function call |
273 | functions in igi_iac_catchup_pkg and igi_iac_reval_wrapper packages. |
274 +=========================================================================*/
275 FUNCTION Do_Addition(
276 p_book_type_code VARCHAR2,
277 p_asset_id NUMBER,
278 p_category_id NUMBER,
279 p_deprn_method_code VARCHAR2,
280 p_cost NUMBER,
281 p_adjusted_cost NUMBER,
282 p_salvage_value NUMBER,
283 p_current_unit NUMBER,
284 p_life_in_months NUMBER,
285 p_deprn_reserve NUMBER,
286 p_deprn_ytd NUMBER,
287 p_calling_function VARCHAR2,
288 p_event_id NUMBER --R12 uptake
289 ) return BOOLEAN IS
290
291 CURSOR c_allow_indexed_reval_flag IS
292 SELECT allow_indexed_reval_flag
293 FROM igi_iac_category_books
294 WHERE book_type_code = p_book_type_code
295 AND category_id = p_category_id;
296
297 CURSOR c_period_num_for_catchup IS
298 SELECT period_num_for_catchup
299 FROM igi_iac_book_controls
300 WHERE book_type_code = p_book_type_code;
301
302 /* Added for bug 2411707 vgadde 12/06/2002 */
303 /* This cursor is used for fetching deprn reserve amount entered at the time of adding asset */
304 CURSOR c_get_deprn_acc IS
305 SELECT deprn_reserve
306 FROM fa_deprn_summary
307 WHERE book_type_code = p_book_type_code
308 AND asset_id = p_asset_id
309 AND deprn_source_code = 'BOOKS';
310
311 /* bug 2450796 sekhar need to update the reval rates ..only one record should have staus = 'Y' for an asset */
312 Cursor C_Reval_Rates is
313 SELECT max(adjustment_id)
314 FROM igi_iac_transaction_headers ith
315 WHERE ith.book_type_code =p_book_type_code
316 AND ith.asset_id = p_asset_id
317 AND (ith.transaction_type_code = 'ADDITION' AND ith.Transaction_sub_type ='REVALUATION');
318
319 /* Cursor to get cost and salvage value for the asset from FA */
320 CURSOR c_fa_books(p_asset_id fa_books.asset_id%TYPE) IS
321 SELECT salvage_value,cost
322 FROM fa_books
323 WHERE book_type_code = p_book_type_code
324 AND asset_id = p_asset_id
325 AND transactioN_header_id_out is NULL ;
326
327 l_dpis_period_counter NUMBER;
328 l_open_period igi_iac_types.prd_rec;
329 l_period_info igi_iac_types.prd_rec;
330 l_allow_indexed_reval_flag igi_iac_category_books.allow_indexed_reval_flag%TYPE;
331 l_period_num_for_catchup igi_iac_book_controls.period_num_for_catchup%TYPE;
332 l_idx1 BINARY_INTEGER DEFAULT 0;
333 l_idx2 BINARY_INTEGER DEFAULT 0;
334 l_reval_control igi_iac_types.iac_reval_control_tab;
335 l_reval_asset_params igi_iac_types.iac_reval_asset_params_tab;
336 l_reval_input_asset igi_iac_types.iac_reval_asset_tab;
337 l_reval_output_asset igi_iac_types.iac_reval_asset_tab;
338 l_reval_output_asset_mvmt igi_iac_types.iac_reval_asset_tab;
339 l_reval_asset_rules igi_iac_types.iac_reval_asset_rules_tab;
340 l_prev_rate_info igi_iac_types.iac_reval_rates_tab;
341 l_curr_rate_info_first igi_iac_types.iac_reval_rates_tab;
342 l_curr_rate_info_next igi_iac_types.iac_reval_rates_tab;
343 l_curr_rate_info igi_iac_types.iac_reval_rates_tab;
344 l_reval_exceptions igi_iac_types.iac_reval_exceptions_tab;
348 l_reval_asset_out igi_iac_types.iac_reval_output_asset;
345 l_fa_asset_info igi_iac_types.iac_reval_fa_asset_info_tab;
346 l_reval_params igi_iac_types.iac_reval_params;
347 l_reval_asset igi_iac_types.iac_reval_input_asset;
349 l_revaluation_id igi_iac_revaluations.revaluation_id%TYPE;
350 l_user_id NUMBER DEFAULT fnd_global.user_id;
351 l_login_id NUMBER DEFAULT fnd_global.login_id;
352 l_current_reval_factor igi_iac_asset_balances.current_reval_factor%TYPE;
353 l_cumulative_reval_factor igi_iac_asset_balances.cumulative_reval_factor%TYPE;
354 l_last_reval_period igi_iac_asset_balances.period_counter%TYPE;
355 l_rowid VARCHAR2(25);
356 l_deprn_acc fa_deprn_summary.deprn_reserve%TYPE;
357 l_get_latest_adjustment_id number;
358 l_asset_type fa_additions.asset_type%TYPE; -- bug 11852403
359 /* Bug 2961656 vgadde 08-jul-2003 Start(1) */
360 l_calling_function VARCHAR2(80);
361 l_fa_deprn_amount_py NUMBER;
362 l_fa_deprn_amount_cy NUMBER;
363 l_last_asset_period NUMBER;
364 l_salvage_value NUMBER;
365 l_cost NUMBER;
366 /* Bug 2961656 vgadde 08-jul-2003 End(1) */
367 l_path_name VARCHAR2(150) := g_path||'do_addition';
368
369 /* This function checks if the category is attched to the book in IAC setup */
370 FUNCTION is_iac_cat_book_defined(l_book_type_code VARCHAR2,
371 l_category_id NUMBER) return BOOLEAN IS
372 CURSOR c_cat_book_defined IS
373 SELECT 'X'
374 FROM IGI_IAC_CATEGORY_BOOKS
375 WHERE book_type_code = l_book_type_code
376 AND category_id = l_category_id
377 AND rownum = 1;
378
379 l_dummy VARCHAR2(1) DEFAULT NULL;
380 BEGIN
381 OPEN c_cat_book_defined;
382 FETCH c_cat_book_defined INTO l_dummy;
383
384 IF c_cat_book_defined%FOUND THEN
385 CLOSE c_cat_book_defined;
386 return TRUE;
387 ELSE
388 CLOSE c_cat_book_defined;
389 return FALSE;
390 END IF;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
395 return FALSE ;
396 END is_iac_cat_book_defined;
397
398 BEGIN
399 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
400 p_full_path => l_path_name,
401 p_string => '********************************************');
402 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
403 p_full_path => l_path_name,
404 p_string => ' Start of IAC Prior Additions Processing');
405 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
406 p_full_path => l_path_name,
407 p_string => '********************************************');
408 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
409 p_full_path => l_path_name,
410 p_string => ' Parameters from FA code hook');
411 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
412 p_full_path => l_path_name,
413 p_string => ' Book type code :'||p_book_type_code);
414 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
415 p_full_path => l_path_name,
416 p_string => ' Category Id :'||to_char(p_category_id));
417 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
418 p_full_path => l_path_name,
419 p_string => ' Asset Id :'||to_char(p_asset_id));
420
421 IF NOT igi_iac_common_utils.is_iac_book(p_book_type_code) THEN
422 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
423 p_full_path => l_path_name,
424 p_string => ' This book is not an IAC book');
425 return TRUE;
426 END IF;
427
428 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
429 p_full_path => l_path_name,
430 p_string => ' This book is an IAC book');
431
432 --bug 11852403 begin
433 SELECT asset_type into l_asset_type from fa_additions where
434 asset_id= p_asset_id;
435
436 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
437 p_full_path => l_path_name,
438 p_string => to_char(p_asset_id)|| ' : Asset type :'|| l_asset_type);
439
440 IF UPPER(l_asset_type)= 'CIP' THEN
441 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
442 p_full_path => l_path_name,
443 p_string => to_char(p_asset_id)|| ': This is a CIP asset. Should not be processed.');
444 RETURN TRUE;
445 END IF;
446
447 --bug 11852403 end
448 IF NOT is_iac_cat_book_defined(p_book_type_code,
449 p_category_id) THEN
450 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
451 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_additions_pkg');
452 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','The category is not set up for book in IAC Options', TRUE);
453 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
454 p_full_path => l_path_name,
455 p_remove_from_stack => FALSE);
456 fnd_file.put_line(fnd_file.log, fnd_message.get);
457 return FALSE;
458 END IF;
459
460 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
461 p_full_path => l_path_name,
462 p_string => ' This Category defined for book in IAC setup');
463
467 p_full_path => l_path_name,
464 -- 30/07/2003, check if asset is a negative asset, if it is return TRUE
465 IF (p_cost < 0) THEN
466 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
468 p_string => 'Asset '||to_char(p_asset_id)||' is a negative asset. Cost '||to_char(p_cost));
469 RETURN TRUE;
470 END IF;
471
472 OPEN c_allow_indexed_reval_flag;
473 FETCH c_allow_indexed_reval_flag INTO l_allow_indexed_reval_flag;
474 CLOSE c_allow_indexed_reval_flag;
475
476 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
477 p_full_path => l_path_name,
478 p_string => ' Allow Indexed reval flag :'||l_allow_indexed_reval_flag);
479 IF (nvl(l_allow_indexed_reval_flag,'Y') = 'N') THEN
480 return TRUE;
481 END IF;
482
483 IF NOT igi_iac_common_utils.get_dpis_period_counter(p_book_type_code,
484 p_asset_id,
485 l_dpis_period_counter) THEN
486 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
487 p_full_path => l_path_name,
488 p_string => '*** Error in Fetching DPIS period counter');
489 return FALSE;
490 END IF;
491
492 IF NOT igi_iac_common_utils.get_open_period_info(p_book_type_code,
493 l_open_period) THEN
494 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
495 p_full_path => l_path_name,
496 p_string => '*** Error in fetching Open period info for book');
497 return FALSE;
498 END IF;
499
500 /* Bug 2961656 vgadde 08-jul-2003 Start(2) */
501 IF p_calling_function = 'UPGRADE' THEN
502 l_calling_function := p_calling_function;
503 ELSE
504 l_calling_function := 'ADDITION';
505 END IF;
506 /* Bug 2961656 vgadde 08-jul-2003 End(2) */
507
508 /* Bug 2906034 vgadde 25/04/2002 Start(1) */
509 IF p_calling_function = 'DEPRECIATION' THEN
510 l_open_period.period_counter := l_open_period.period_counter - 1;
511 END IF;
512 /* Bug 2906034 vgadde 25/04/2002 End(1) */
513
514 /* Bug 2407352 vgadde 07/06/2002 Start */
515 IF (l_dpis_period_counter = l_open_period.period_counter) THEN
516 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
517 p_full_path => l_path_name,
518 p_string => ' The asset is added in the current period');
519 return TRUE;
520 END IF;
521 /* Bug 2407352 vgadde 07/06/2002 End */
522
523 /* Bugs 2411707 and 2411561 vgadde 12/06/2002 Start(1) */
524 /* Bug 2961656 vgadde 08-jul-2003 Start(3) commented */
525 /*OPEN c_get_deprn_acc;
526 FETCH c_get_deprn_acc INTO l_deprn_acc;
527 CLOSE c_get_deprn_acc;
528
529 Debug(' Catchup Depreciation reserve entered by user :' || to_char(l_deprn_acc));
530 IF (nvl(l_deprn_acc,0) <> 0) THEN
531 return TRUE;
532 END IF;*/
533 /* Bug 2961656 vgadde 08-jul-2003 End(3) commented */
534 /* Bugs 2411707 and 2411561 vgadde 12/06/2002 End(1) */
535
536 OPEN c_period_num_for_catchup;
537 FETCH c_period_num_for_catchup INTO l_period_num_for_catchup;
538 CLOSE c_period_num_for_catchup;
539
540 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
541 p_full_path => l_path_name,
542 p_string => ' Checking for default price index values in catchup periods');
543 IF NOT Is_Default_Index_Found(p_book_type_code,
544 p_asset_id,
545 l_dpis_period_counter,
546 l_open_period.period_counter,
547 l_period_num_for_catchup) THEN
548 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
549 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_additions_pkg');
550 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','The price indexes are not setup properly. Atleast one period in catchup has the index 9999.99', TRUE);
551 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
552 p_full_path => l_path_name,
553 p_remove_from_stack => FALSE);
554 fnd_file.put_line(fnd_file.log, fnd_message.get);
555 return FALSE;
556 END IF;
557
558 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
559 p_full_path => l_path_name,
560 p_string => ' Revaluation catchup period for the book :'||to_char(l_period_num_for_catchup));
561 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
562 p_full_path => l_path_name,
563 p_string => ' Revaluation catchup periods for the asset ');
564 FOR l_period_counter IN l_dpis_period_counter..(l_open_period.period_counter-1) LOOP
565
566 IF NOT igi_iac_common_utils.get_period_info_for_counter(p_book_type_code,
567 l_period_counter,
568 l_period_info) THEN
569 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
570 p_full_path => l_path_name,
571 p_string => '*** Error in fetching period information');
572 return FALSE;
573 END IF;
574
575 /* if the period in the loop is a catchup period for revaluation
576 then initialize revaluation structures */
577 IF (l_period_num_for_catchup = l_period_info.period_num) THEN
578 Debug_Period(l_period_info);
579 l_idx1 := l_idx1 + 1;
583 l_reval_asset_params(l_idx1).category_id := p_category_id;
580 l_reval_control(l_idx1).revaluation_mode := 'L'; -- Live Mode
581 l_reval_asset_rules(l_idx1).revaluation_type := 'O'; -- Occasional
582 l_reval_asset_params(l_idx1).asset_id := p_asset_id;
584 l_reval_asset_params(l_idx1).book_type_code := p_book_type_code;
585 l_reval_asset_params(l_idx1).period_counter := l_period_counter;
586
587 END IF;
588 END LOOP;
589
590
591 IF (l_idx1 = 0) THEN /* No catch-up required */
592 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
593 p_full_path => l_path_name,
594 p_string => ' No revaluation catchup periods found');
595 return TRUE;
596 END IF;
597
598 IF NOT igi_iac_catchup_pkg.get_FA_Deprn_Expense(p_asset_id,
599 p_book_type_code,
600 l_open_period.period_counter,
601 l_calling_function,
602 p_deprn_reserve,
603 p_deprn_ytd,
604 l_fa_deprn_amount_py,
605 l_fa_deprn_amount_cy,
606 l_last_asset_period) THEN
607 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
608 p_full_path => l_path_name,
609 p_string => '*** Error in get_FA_Deprn_Expense function');
610 return FALSE;
611 END IF;
612 /*Salavge value correction*/
613 -- resreve
614 IF (p_salvage_value Is Null) Or (P_cost is Null) THEN
615
616 OPEN c_fa_books(p_asset_id);
617 FETCH c_fa_books into l_salvage_value,
618 l_cost;
619 CLOSE c_fa_books;
620 ELSE
621 l_salvage_value := p_salvage_value;
622 l_cost := P_cost;
623 END IF;
624
625 IF l_salvage_value <> 0 Then
626 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
627 p_full_path => l_path_name,
628 p_string => '+Salavge Value Correction deprn_amount_py before :' ||l_fa_deprn_amount_py);
629 -- deprn amount l_fa_deprn_amount_py
630 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
631 P_book_type_code =>p_book_type_code,
632 P_value=>l_fa_deprn_amount_py,
633 P_cost=>l_cost,
634 P_salvage_value=>l_salvage_value,
635 P_calling_program=>'ADDITION') THEN
636 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
637 p_full_path => l_path_name,
638 p_string => '+Salavge Value Correction Failed : ');
639 return false;
640 END IF;
641 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
642 p_full_path => l_path_name,
643 p_string => '+Salavge Value Correction deprn_amount_py after :' ||l_fa_deprn_amount_py );
644 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
645 p_full_path => l_path_name,
646 p_string => '+Salavge Value Correction deprn_amount_cy before :' ||l_fa_deprn_amount_cy);
647 -- deprn l_fa_deprn_amount_cy
648 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
649 P_book_type_code =>p_book_type_code,
650 P_value=>l_fa_deprn_amount_cy,
651 P_cost=>l_cost,
652 P_salvage_value=>l_salvage_value,
653 P_calling_program=>'ADDITION') THEN
654
655 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
656 p_full_path => l_path_name,
657 p_string => '+Salavge Value Correction Failed : ');
658 return false;
659 END IF;
660 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
661 p_full_path => l_path_name,
662 p_string => '+Salavge Value Correction deprn_amount_cy after :' ||l_fa_deprn_amount_cy);
663 END IF;
664 /*salvage value correction*/
665
666
667 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
668 p_full_path => l_path_name,
669 p_string => ' Calling Revaluation Initialization program ');
670 IF NOT igi_iac_catchup_pkg.do_reval_init_struct(l_open_period.period_counter,
671 l_reval_control,
672 l_reval_asset_params,
673 l_reval_input_asset,
674 l_reval_output_asset,
675 l_reval_output_asset_mvmt,
676 l_reval_asset_rules,
677 l_prev_rate_info,
678 l_curr_rate_info_first,
679 l_curr_rate_info_next,
680 l_curr_rate_info,
684 l_fa_deprn_amount_cy,
681 l_reval_exceptions,
682 l_fa_asset_info,
683 l_fa_deprn_amount_py,
685 l_last_asset_period,
686 l_calling_function) THEN
687 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
688 p_full_path => l_path_name,
689 p_string => '*** Error in catchup pkg for revaluation initialization');
690 return FALSE;
691 END IF;
692 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
693 p_full_path => l_path_name,
694 p_string => ' Back from Revaluation Initialization');
695
696 FOR l_idx2 IN 1..l_idx1 LOOP
697
698 IF (l_idx2 <> 1) THEN
699
700 l_reval_asset := l_reval_output_asset(l_idx2 - 1);
701
702 /* Added + 1 for the first 2 parameters for bug 2411478 vgadde */
703 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
704 p_full_path => l_path_name,
705 p_string => ' Doing depreciation catchup for the periods between revaluation');
706 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_reval_asset_params(l_idx2 - 1).period_counter + 1,
707 l_reval_asset_params(l_idx2).period_counter + 1,
708 l_open_period.period_counter,
709 FALSE,
710 l_calling_function,
711 l_fa_deprn_amount_py,
712 l_fa_deprn_amount_cy,
713 l_last_asset_period,
714 p_deprn_reserve,
715 p_deprn_ytd,
716 l_reval_asset,
717 p_event_id )THEN
718 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
719 p_full_path => l_path_name,
720 p_string => '*** Error in depreciation catchup');
721 return FALSE;
722 END IF;
723 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
724 p_full_path => l_path_name,
725 p_string => ' Back from depreciation catchup');
726
727 l_current_reval_factor := l_reval_input_asset(l_idx2).current_reval_factor;
728 l_cumulative_reval_factor := l_reval_input_asset(l_idx2).cumulative_reval_factor;
729 l_reval_input_asset(l_idx2) := l_reval_asset;
730 l_reval_input_asset(l_idx2).current_reval_factor := l_current_reval_factor;
731 l_reval_input_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
732 END IF;
733
734 IF (l_idx2 = l_idx1) THEN
735 /* Last revaluation - Insert records into revaluation tables*/
736 IF (p_calling_function <> 'UPGRADE') THEN
737 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
738 p_full_path => l_path_name,
739 p_string => ' Last Revaluation - Inserting into igi_iac_revaluations');
740
741 l_rowid := NULL;
742 l_revaluation_id := NULL;
743
744 igi_iac_revaluations_pkg.insert_row
745 (l_rowid,
746 l_revaluation_id,
747 p_book_type_code,
748 sysdate,
749 l_reval_asset_params(l_idx1).period_counter,
750 'NEW',
751 NULL,
752 NULL,
753 'ADDITION',
754 X_event_id => p_event_id);
755 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
756 p_full_path => l_path_name,
757 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
758
759 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
760 p_full_path => l_path_name,
761 p_string => ' Inserting into igi_iac_reval_asset_rules');
762 l_rowid := NULL;
763 igi_iac_reval_asset_rules_pkg.insert_row
764 (l_rowid,
765 l_revaluation_id,
766 l_reval_asset_params(l_idx1).book_type_code,
767 l_reval_asset_params(l_idx1).category_id,
768 l_reval_asset_params(l_idx1).asset_id,
769 l_reval_asset_rules(l_idx1).revaluation_factor,
770 l_reval_asset_rules(l_idx1).revaluation_type,
771 l_reval_asset_rules(l_idx1).new_cost,
772 l_reval_input_asset(l_idx2).adjusted_cost,
773 'Y',
774 'N',
775 NULL
776 );
777 END IF; /* End of checking for UPGRADE */
778
779 l_last_reval_period := l_reval_asset_params(l_idx2).period_counter;
780 l_reval_asset_params(l_idx2).period_counter := l_open_period.period_counter;
781 l_reval_input_asset(l_idx2).period_counter := l_open_period.period_counter;
782 l_reval_asset_params(l_idx2).revaluation_id := l_revaluation_id;
783 l_reval_asset_rules(l_idx2).revaluation_id := l_revaluation_id;
784
788 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
785 IF (p_calling_function = 'UPGRADE') THEN
786 l_reval_control(l_idx2).calling_program := 'UPGRADE';
787 END IF;
789 p_full_path => l_path_name,
790 p_string => ' Period counter passed to Reval CRUD :'||to_char(l_open_period.period_counter ));
791
792 END IF;
793
794 l_reval_params.reval_control := l_reval_control(l_idx2);
795 l_reval_params.reval_asset_params := l_reval_asset_params(l_idx2);
796 l_reval_params.reval_input_asset := l_reval_input_asset(l_idx2);
797 l_reval_params.reval_output_asset := l_reval_input_asset(l_idx2);
798 l_reval_params.reval_output_asset_mvmt := l_reval_output_asset_mvmt(l_idx2);
799 l_reval_params.reval_asset_rules := l_reval_asset_rules(l_idx2);
800 l_reval_params.reval_prev_rate_info := l_prev_rate_info(l_idx2);
801 l_reval_params.reval_curr_rate_info_first := l_curr_rate_info_first(l_idx2);
802 l_reval_params.reval_curr_rate_info_next := l_curr_rate_info_next(l_idx2);
803 l_reval_params.reval_asset_exceptions := l_reval_exceptions(l_idx2);
804 l_reval_params.fa_asset_info := l_fa_asset_info(l_idx2);
805
806 /* call revaluation processing function here */
807 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
808 p_full_path => l_path_name,
809 p_string => ' Input asset balances to revaluation program');
810 Debug_Asset(l_reval_input_asset(l_idx2));
811
812 IF NOT igi_iac_reval_wrapper.do_reval_calc_asset(l_reval_params,
813 l_reval_asset_out) THEN
814 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
815 p_full_path => l_path_name,
816 p_string => '*** Error in Revaluation Program');
817 return FALSE;
818 END IF;
819
820 l_current_reval_factor := l_reval_output_asset(l_idx2).current_reval_factor;
821 l_cumulative_reval_factor := l_reval_output_asset(l_idx2).cumulative_reval_factor;
822 l_reval_output_asset(l_idx2) := l_reval_asset_out;
823 l_reval_output_asset(l_idx2).current_reval_factor := l_current_reval_factor;
824 l_reval_output_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
825
826 /* Bug 2425856 vgadde 20/06/2002 Start(1) */
827 BEGIN
828 IF (l_idx2 = l_idx1 and p_calling_function <> 'UPGRADE') THEN /* Last Revaluation */
829 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
830 p_full_path => l_path_name,
831 p_string => ' Last revaluation period :'||to_char(l_last_reval_period));
832 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
833 p_full_path => l_path_name,
834 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
835 UPDATE igi_iac_revaluation_rates
836 SET period_counter = l_last_reval_period
837 WHERE revaluation_id = l_revaluation_id
838 AND asset_id = p_asset_id
839 AND book_type_code = p_book_type_code;
840
841 IF SQL%FOUND then
842 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
843 p_full_path => l_path_name,
844 p_string => ' Records in reval rates updated for correct period');
845 ELSE
846 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
847 p_full_path => l_path_name,
848 p_string => '*** No record found in reval rates table to update');
849 return FALSE;
850 END IF;
851 END IF;
852 END;
853 /* Bug 2425856 vgadde 20/06/2002 End(1) */
854
855 END LOOP;
856
857 IF (l_last_reval_period < l_open_period.period_counter) THEN
858
859 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
860 p_full_path => l_path_name,
861 p_string => ' Doing the final catchup for depreciation');
862
863 l_reval_asset := l_reval_output_asset(l_idx1);
864 /* Added + 1 for the first parameter for bug 2411478 vgadde 12/06/2002 */
865 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_last_reval_period + 1,
866 l_open_period.period_counter,
867 l_open_period.period_counter,
868 TRUE,
869 l_calling_function,
870 l_fa_deprn_amount_py,
871 l_fa_deprn_amount_cy,
872 l_last_asset_period,
873 p_deprn_reserve,
874 p_deprn_ytd,
875 l_reval_asset,
876 p_event_id )THEN
877 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
878 p_full_path => l_path_name,
879 p_string => '*** Error in depreciation catchup for final run');
880 return FALSE;
881 END IF;
882 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
883 p_full_path => l_path_name,
884 p_string => ' Output from final catchup');
885 Debug_Asset(l_reval_asset);
886
887 END IF;
888
889 /* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
890 IF (p_calling_function <> 'UPGRADE') THEN
891 l_get_latest_adjustment_id :=0;
895 IF NOT IGI_IAC_REVAL_CRUD.update_reval_rates (fp_adjustment_id => l_get_latest_adjustment_id) THEN
892 OPEN C_Reval_Rates;
893 FETCH C_Reval_Rates into l_get_latest_adjustment_id;
894 CLOSE C_Reval_Rates;
896 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
897 p_full_path => l_path_name,
898 p_string => '*** Failed to Update REVAL RATES');
899 return FALSE;
900 END IF;
901 END IF;
902
903 -- Added by Venkat Gadde
904 IF p_calling_function <> 'UPGRADE' THEN
905 UPDATE igi_iac_transaction_headers
906 SET event_id = p_event_id
907 WHERE book_type_code = p_book_type_code
908 AND asset_id = p_asset_id;
909
910 UPDATE igi_iac_adjustments
911 SET event_id = p_event_id
912 WHERE book_type_code = p_book_type_code
913 AND asset_id = p_asset_id;
914 END IF;
915 -- End of code added by Venkat Gadde
916 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
917 p_full_path => l_path_name,
918 p_string => '******* End of IAC Prior addition processing for asset *****');
919 return TRUE;
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
924 return FALSE;
925 END Do_Addition;
926
927
928 FUNCTION Do_Rollback_Addition(
929 p_book_type_code VARCHAR2,
930 p_period_counter NUMBER,
931 p_calling_function VARCHAR2
932 ) return BOOLEAN IS
933
934 CURSOR c_get_asset_add_info IS
935 SELECT asset_id,adjustment_id,transaction_sub_type
936 FROM igi_iac_transaction_headers
937 WHERE book_type_code = p_book_type_code
938 AND period_counter = p_period_counter
939 AND transaction_type_code = 'ADDITION';
940
941 CURSOR c_get_distributions(p_asset_id igi_iac_det_balances.asset_id%TYPE,
942 p_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
943 SELECT distribution_id
944 FROM igi_iac_det_balances
945 WHERE book_type_code = p_book_type_code
946 AND asset_id = p_asset_id;
947
948 /* Bug 2425914 vgadde 21/06/2002 */
949 /* Modified query to fecth records created by ADDITION only */
950 CURSOR c_get_revaluation_info(p_asset_id igi_iac_det_balances.asset_id%TYPE) IS
951 SELECT a.revaluation_id
952 FROM igi_iac_revaluations r,igi_iac_reval_asset_rules a
953 WHERE a.revaluation_id = r.revaluation_id
954 AND a.book_type_code = p_book_type_code
955 AND a.asset_id = p_asset_id
956 AND r.calling_program = 'ADDITION';
957
958 CURSOR c_get_adjustments(p_asset_id igi_iac_adjustments.asset_id%TYPE,
959 p_adjustment_id igi_iac_adjustments.adjustment_id%TYPE) IS
960 SELECT 'X'
961 FROM igi_iac_adjustments
962 WHERE adjustment_id = p_adjustment_id
963 AND book_type_code = p_book_type_code
964 AND asset_id = p_asset_id
965 AND rownum = 1;
966
967 CURSOR c_get_asset_balances(p_asset_id igi_iac_asset_balances.asset_id%TYPE,
968 cp_period_counter igi_iac_asset_balances.period_counter%TYPE) IS
969 SELECT 'X'
970 FROM igi_iac_asset_balances
971 WHERE book_type_code = p_book_type_code
972 AND asset_id = p_asset_id
973 AND period_counter = cp_period_counter;
974
975 CURSOR c_get_revaluation_rates(p_asset_id igi_iac_revaluation_rates.asset_id%TYPE,
976 p_revaluation_id igi_iac_revaluation_rates.revaluation_id%TYPE) IS
977 SELECT 'X'
978 FROM igi_iac_revaluation_rates
979 WHERE asset_id = p_asset_id
980 AND book_type_code = p_book_type_code
981 AND revaluation_id = p_revaluation_id;
982
983 CURSOR c_get_fa_distributions(cp_asset_id igi_iac_det_balances.asset_id%TYPE,
984 cp_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
985 SELECT distribution_id
986 FROM igi_iac_fa_deprn
987 WHERE book_type_code = p_book_type_code
988 AND asset_id = cp_asset_id
989 AND adjustment_id = cp_adjustment_id;
990
991 l_revaluation_id igi_iac_revaluations.revaluation_id%TYPE;
992 l_dummy VARCHAR2(1);
993 l_path_name VARCHAR2(150) := g_path||'do_rollback_addition';
994
995 BEGIN
996 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
997 p_full_path => l_path_name,
998 p_string => '********* Start of IAC Additions Rollback **********');
999 FOR l_asset_info IN c_get_asset_add_info LOOP
1000 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1001 p_full_path => l_path_name,
1002 p_string => ' Processing for Asset :'||to_char(l_asset_info.asset_id));
1003 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1004 p_full_path => l_path_name,
1005 p_string => ' Adjustment :'||to_char(l_asset_info.adjustment_id));
1006 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1007 p_full_path => l_path_name,
1008 p_string => ' Transaction :'||l_asset_info.transaction_sub_type);
1009
1010 /* Delete records from igi_iac_adjustments */
1011 l_dummy := NULL;
1012 OPEN c_get_adjustments(l_asset_info.asset_id,l_asset_info.adjustment_id);
1013 FETCH c_get_adjustments INTO l_dummy;
1014
1015 IF c_get_adjustments%FOUND THEN
1016 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1017 p_full_path => l_path_name,
1018 p_string => ' Deleting records from igi_iac_adjustments');
1022 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1019 igi_iac_adjustments_pkg.delete_row(
1020 x_adjustment_id => l_asset_info.adjustment_id);
1021 ELSIF c_get_adjustments%NOTFOUND THEN
1023 p_full_path => l_path_name,
1024 p_string => ' No records found in igi_iac_adjustments for delete');
1025 END IF;
1026
1027 CLOSE c_get_adjustments;
1028
1029 /* Delete records from igi_iac_det_balances */
1030 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1031 p_full_path => l_path_name,
1032 p_string => ' Deleting records from igi_iac_det_balances');
1033 FOR l_det_balance IN c_get_distributions(l_asset_info.asset_id,
1034 l_asset_info.adjustment_id) LOOP
1035 igi_iac_det_balances_pkg.delete_row(
1036 x_adjustment_id => l_asset_info.adjustment_id,
1037 x_asset_id => l_asset_info.asset_id,
1038 x_distribution_id => l_det_balance.distribution_id,
1039 x_book_type_code => p_book_type_code,
1040 x_period_counter => p_period_counter);
1041 END LOOP;
1042
1043 /* Delete records from igi_iac_fa_deprn */
1044 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1045 p_full_path => l_path_name,
1046 p_string => ' Deleting records from igi_iac_fa_deprn');
1047 FOR l_iac_fa_det_balance IN c_get_fa_distributions(l_asset_info.asset_id,
1048 l_asset_info.adjustment_id) LOOP
1049 igi_iac_fa_deprn_pkg.delete_row(
1050 x_book_type_code => p_book_type_code,
1051 x_asset_id => l_asset_info.asset_id,
1052 x_period_counter => p_period_counter,
1053 x_adjustment_id => l_asset_info.adjustment_id,
1054 x_distribution_id => l_iac_fa_det_balance.distribution_id);
1055 END LOOP;
1056
1057 /* Delete records from igi_iac_asset_balances */
1058 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
1059
1060 OPEN c_get_asset_balances(l_asset_info.asset_id,p_period_counter);
1061 FETCH c_get_asset_balances INTO l_dummy;
1062
1063 IF c_get_asset_balances%FOUND THEN
1064 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1065 p_full_path => l_path_name,
1066 p_string => ' Deleting records from igi_iac_asset_balances for current period');
1067 igi_iac_asset_balances_pkg.delete_row(
1068 x_asset_id => l_asset_info.asset_id,
1069 x_book_type_code => p_book_type_code,
1070 x_period_counter => p_period_counter);
1071 ELSIF c_get_asset_balances%NOTFOUND THEN
1072 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1073 p_full_path => l_path_name,
1074 p_string => ' No records found in igi_iac_asset_balances to delete');
1075 END IF;
1076
1077 CLOSE c_get_asset_balances;
1078
1079 OPEN c_get_asset_balances(l_asset_info.asset_id, p_period_counter+1);
1080 FETCH c_get_asset_balances INTO l_dummy;
1081 IF c_get_asset_balances%FOUND THEN
1082 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1083 p_full_path => l_path_name,
1084 p_string => ' Deleting records from igi_iac_asset_balances for next period');
1085 igi_iac_asset_balances_pkg.delete_row(
1086 x_asset_id => l_asset_info.asset_id,
1087 x_book_type_code => p_book_type_code,
1088 x_period_counter => p_period_counter+1);
1089 ELSE
1090 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1091 p_full_path => l_path_name,
1092 p_string => ' No records found in igi_iac_asset_balances to delete');
1093 END IF;
1094 CLOSE c_get_asset_balances; -- Bug 2417394 this cursor was not gettign closed previously
1095 END IF;
1096
1097 /* Delete records from igi_iac_transaction_headers */
1098 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1099 p_full_path => l_path_name,
1100 p_string => ' Deleting records from igi_iac_transaction_headers');
1101 igi_iac_trans_headers_pkg.delete_row(
1102 x_adjustment_id => l_asset_info.adjustment_id);
1103
1104 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
1105
1106 l_revaluation_id := NULL;
1107 OPEN c_get_revaluation_info(l_asset_info.asset_id);
1108 FETCH c_get_revaluation_info INTO l_revaluation_id;
1109 CLOSE c_get_revaluation_info;
1110 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1111 p_full_path => l_path_name,
1112 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
1113
1114 /* Delete records from igi_iac_reval_asset_rules */
1115 IF (l_revaluation_id IS NOT NULL) THEN
1116
1117 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1118 p_full_path => l_path_name,
1119 p_string => ' Deleting records from igi_iac_reval_asset_rules');
1120 igi_iac_reval_asset_rules_pkg.delete_row(
1121 x_asset_id => l_asset_info.asset_id,
1122 x_book_type_code => p_book_type_code,
1123 x_revaluation_id => l_revaluation_id);
1124
1125 /* Delete records from igi_iac_revaluations */
1126 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1127 p_full_path => l_path_name,
1128 p_string => ' Deleting records from igi_iac_revaluations');
1129 igi_iac_revaluations_pkg.delete_row(
1130 x_revaluation_id => l_revaluation_id);
1131
1132 /* Delete records from igi_iac_revaluation_rates */
1133 OPEN c_get_revaluation_rates(l_asset_info.asset_id,l_revaluation_id);
1134 FETCH c_get_revaluation_rates INTO l_dummy;
1135
1136 IF c_get_revaluation_rates%FOUND THEN
1137 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1138 p_full_path => l_path_name,
1139 p_string => ' Deleting records from igi_iac_revaluation_rates');
1140 DELETE FROM igi_iac_revaluation_rates
1141 WHERE asset_id = l_asset_info.asset_id
1142 AND book_type_code = p_book_type_code
1143 AND revaluation_id = l_revaluation_id;
1144 END IF;
1145
1146 CLOSE c_get_revaluation_rates;
1147
1148 END IF;
1149
1150 END IF;
1151
1152 END LOOP;
1153 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1154 p_full_path => l_path_name,
1155 p_string => '********* End of IAC Additions Rollback **********');
1156 return TRUE;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1161 return FALSE;
1162
1163 END Do_Rollback_Addition;
1164
1165 END igi_iac_additions_pkg; -- Package body