[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_ADDITIONS_PKG
Source
1 PACKAGE BODY IGI_IAC_ADDITIONS_PKG AS
2 -- $Header: igiiapab.pls 120.13.12000000.3 2007/11/02 15:13:05 sharoy ship $
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.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;
94 l_last_reval_period NUMBER DEFAULT NULL;
95 l_path_name VARCHAR2(150) := g_path||'is_default_index_found';
96 BEGIN
97
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
231 l_path_name VARCHAR2(150) := g_path||'Do_Addition_Wrapper';
232 BEGIN
233
234 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
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
320 CURSOR c_fa_books(p_asset_id fa_books.asset_id%TYPE) IS
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 */
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;
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;
348 l_reval_asset_out igi_iac_types.iac_reval_output_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 /* Bug 2961656 vgadde 08-jul-2003 Start(1) */
359 l_calling_function VARCHAR2(80);
360 l_fa_deprn_amount_py NUMBER;
361 l_fa_deprn_amount_cy NUMBER;
362 l_last_asset_period NUMBER;
363 l_salvage_value NUMBER;
364 l_cost NUMBER;
365 /* Bug 2961656 vgadde 08-jul-2003 End(1) */
366 l_path_name VARCHAR2(150) := g_path||'do_addition';
367
368 /* This function checks if the category is attched to the book in IAC setup */
369 FUNCTION is_iac_cat_book_defined(l_book_type_code VARCHAR2,
370 l_category_id NUMBER) return BOOLEAN IS
371 CURSOR c_cat_book_defined IS
372 SELECT 'X'
373 FROM IGI_IAC_CATEGORY_BOOKS
374 WHERE book_type_code = l_book_type_code
375 AND category_id = l_category_id
376 AND rownum = 1;
377
378 l_dummy VARCHAR2(1) DEFAULT NULL;
379 BEGIN
380 OPEN c_cat_book_defined;
381 FETCH c_cat_book_defined INTO l_dummy;
382
383 IF c_cat_book_defined%FOUND THEN
384 CLOSE c_cat_book_defined;
385 return TRUE;
386 ELSE
387 CLOSE c_cat_book_defined;
388 return FALSE;
389 END IF;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
394 return FALSE ;
395 END is_iac_cat_book_defined;
396
397 BEGIN
398 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
399 p_full_path => l_path_name,
400 p_string => '********************************************');
401 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
402 p_full_path => l_path_name,
403 p_string => ' Start of IAC Prior Additions Processing');
404 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
405 p_full_path => l_path_name,
406 p_string => '********************************************');
407 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
408 p_full_path => l_path_name,
409 p_string => ' Parameters from FA code hook');
410 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
411 p_full_path => l_path_name,
412 p_string => ' Book type code :'||p_book_type_code);
413 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
414 p_full_path => l_path_name,
415 p_string => ' Category Id :'||to_char(p_category_id));
416 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
417 p_full_path => l_path_name,
418 p_string => ' Asset Id :'||to_char(p_asset_id));
419
423 p_string => ' This book is not an IAC book');
420 IF NOT igi_iac_common_utils.is_iac_book(p_book_type_code) THEN
421 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
422 p_full_path => l_path_name,
424 return TRUE;
425 END IF;
426
427 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
428 p_full_path => l_path_name,
429 p_string => ' This book is an IAC book');
430
431 IF NOT is_iac_cat_book_defined(p_book_type_code,
432 p_category_id) THEN
433 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
434 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_additions_pkg');
435 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','The category is not set up for book in IAC Options', TRUE);
436 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
437 p_full_path => l_path_name,
438 p_remove_from_stack => FALSE);
439 fnd_file.put_line(fnd_file.log, fnd_message.get);
440 return FALSE;
441 END IF;
442
443 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
444 p_full_path => l_path_name,
445 p_string => ' This Category defined for book in IAC setup');
446
447 -- 30/07/2003, check if asset is a negative asset, if it is return TRUE
448 IF (p_cost < 0) THEN
449 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
450 p_full_path => l_path_name,
451 p_string => 'Asset '||to_char(p_asset_id)||' is a negative asset. Cost '||to_char(p_cost));
452 RETURN TRUE;
453 END IF;
454
455 OPEN c_allow_indexed_reval_flag;
456 FETCH c_allow_indexed_reval_flag INTO l_allow_indexed_reval_flag;
457 CLOSE c_allow_indexed_reval_flag;
458
459 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
460 p_full_path => l_path_name,
461 p_string => ' Allow Indexed reval flag :'||l_allow_indexed_reval_flag);
462 IF (nvl(l_allow_indexed_reval_flag,'Y') = 'N') THEN
463 return TRUE;
464 END IF;
465
466 IF NOT igi_iac_common_utils.get_dpis_period_counter(p_book_type_code,
467 p_asset_id,
468 l_dpis_period_counter) THEN
469 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
470 p_full_path => l_path_name,
471 p_string => '*** Error in Fetching DPIS period counter');
472 return FALSE;
473 END IF;
474
475 IF NOT igi_iac_common_utils.get_open_period_info(p_book_type_code,
476 l_open_period) THEN
477 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
478 p_full_path => l_path_name,
479 p_string => '*** Error in fetching Open period info for book');
480 return FALSE;
481 END IF;
482
483 /* Bug 2961656 vgadde 08-jul-2003 Start(2) */
484 IF p_calling_function = 'UPGRADE' THEN
485 l_calling_function := p_calling_function;
486 ELSE
487 l_calling_function := 'ADDITION';
488 END IF;
489 /* Bug 2961656 vgadde 08-jul-2003 End(2) */
490
491 /* Bug 2906034 vgadde 25/04/2002 Start(1) */
492 IF p_calling_function = 'DEPRECIATION' THEN
493 l_open_period.period_counter := l_open_period.period_counter - 1;
494 END IF;
495 /* Bug 2906034 vgadde 25/04/2002 End(1) */
496
497 /* Bug 2407352 vgadde 07/06/2002 Start */
498 IF (l_dpis_period_counter = l_open_period.period_counter) THEN
499 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
500 p_full_path => l_path_name,
501 p_string => ' The asset is added in the current period');
502 return TRUE;
503 END IF;
504 /* Bug 2407352 vgadde 07/06/2002 End */
505
506 /* Bugs 2411707 and 2411561 vgadde 12/06/2002 Start(1) */
507 /* Bug 2961656 vgadde 08-jul-2003 Start(3) commented */
508 /*OPEN c_get_deprn_acc;
509 FETCH c_get_deprn_acc INTO l_deprn_acc;
510 CLOSE c_get_deprn_acc;
511
512 Debug(' Catchup Depreciation reserve entered by user :' || to_char(l_deprn_acc));
513 IF (nvl(l_deprn_acc,0) <> 0) THEN
514 return TRUE;
515 END IF;*/
516 /* Bug 2961656 vgadde 08-jul-2003 End(3) commented */
517 /* Bugs 2411707 and 2411561 vgadde 12/06/2002 End(1) */
518
519 OPEN c_period_num_for_catchup;
520 FETCH c_period_num_for_catchup INTO l_period_num_for_catchup;
521 CLOSE c_period_num_for_catchup;
522
523 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
524 p_full_path => l_path_name,
525 p_string => ' Checking for default price index values in catchup periods');
526 IF NOT Is_Default_Index_Found(p_book_type_code,
527 p_asset_id,
528 l_dpis_period_counter,
529 l_open_period.period_counter,
530 l_period_num_for_catchup) THEN
531 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
532 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_additions_pkg');
533 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','The price indexes are not setup properly. Atleast one period in catchup has the index 9999.99', TRUE);
534 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
535 p_full_path => l_path_name,
536 p_remove_from_stack => FALSE);
540
537 fnd_file.put_line(fnd_file.log, fnd_message.get);
538 return FALSE;
539 END IF;
541 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
542 p_full_path => l_path_name,
543 p_string => ' Revaluation catchup period for the book :'||to_char(l_period_num_for_catchup));
544 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
545 p_full_path => l_path_name,
546 p_string => ' Revaluation catchup periods for the asset ');
547 FOR l_period_counter IN l_dpis_period_counter..(l_open_period.period_counter-1) LOOP
548
549 IF NOT igi_iac_common_utils.get_period_info_for_counter(p_book_type_code,
550 l_period_counter,
551 l_period_info) THEN
552 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
553 p_full_path => l_path_name,
554 p_string => '*** Error in fetching period information');
555 return FALSE;
556 END IF;
557
558 /* if the period in the loop is a catchup period for revaluation
559 then initialize revaluation structures */
560 IF (l_period_num_for_catchup = l_period_info.period_num) THEN
561 Debug_Period(l_period_info);
562 l_idx1 := l_idx1 + 1;
563 l_reval_control(l_idx1).revaluation_mode := 'L'; -- Live Mode
564 l_reval_asset_rules(l_idx1).revaluation_type := 'O'; -- Occasional
565 l_reval_asset_params(l_idx1).asset_id := p_asset_id;
566 l_reval_asset_params(l_idx1).category_id := p_category_id;
567 l_reval_asset_params(l_idx1).book_type_code := p_book_type_code;
568 l_reval_asset_params(l_idx1).period_counter := l_period_counter;
569
570 END IF;
571 END LOOP;
572
573
574 IF (l_idx1 = 0) THEN /* No catch-up required */
575 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
576 p_full_path => l_path_name,
577 p_string => ' No revaluation catchup periods found');
578 return TRUE;
579 END IF;
580
581 IF NOT igi_iac_catchup_pkg.get_FA_Deprn_Expense(p_asset_id,
582 p_book_type_code,
583 l_open_period.period_counter,
584 l_calling_function,
585 p_deprn_reserve,
586 p_deprn_ytd,
587 l_fa_deprn_amount_py,
588 l_fa_deprn_amount_cy,
589 l_last_asset_period) THEN
590 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
591 p_full_path => l_path_name,
592 p_string => '*** Error in get_FA_Deprn_Expense function');
593 return FALSE;
594 END IF;
595 /*Salavge value correction*/
596 -- resreve
597 IF (p_salvage_value Is Null) Or (P_cost is Null) THEN
598
599 OPEN c_fa_books(p_asset_id);
600 FETCH c_fa_books into l_salvage_value,
601 l_cost;
602 CLOSE c_fa_books;
603 ELSE
604 l_salvage_value := p_salvage_value;
605 l_cost := P_cost;
606 END IF;
607
608 IF l_salvage_value <> 0 Then
609 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
610 p_full_path => l_path_name,
611 p_string => '+Salavge Value Correction deprn_amount_py before :' ||l_fa_deprn_amount_py);
612 -- deprn amount l_fa_deprn_amount_py
613 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
614 P_book_type_code =>p_book_type_code,
615 P_value=>l_fa_deprn_amount_py,
616 P_cost=>l_cost,
617 P_salvage_value=>l_salvage_value,
618 P_calling_program=>'ADDITION') THEN
619 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
620 p_full_path => l_path_name,
621 p_string => '+Salavge Value Correction Failed : ');
622 return false;
623 END IF;
624 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
625 p_full_path => l_path_name,
626 p_string => '+Salavge Value Correction deprn_amount_py after :' ||l_fa_deprn_amount_py );
627 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
628 p_full_path => l_path_name,
629 p_string => '+Salavge Value Correction deprn_amount_cy before :' ||l_fa_deprn_amount_cy);
630 -- deprn l_fa_deprn_amount_cy
631 IF NOT igi_iac_salvage_pkg.correction(p_asset_id => p_asset_id,
632 P_book_type_code =>p_book_type_code,
633 P_value=>l_fa_deprn_amount_cy,
634 P_cost=>l_cost,
635 P_salvage_value=>l_salvage_value,
636 P_calling_program=>'ADDITION') THEN
637
641 return false;
638 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
639 p_full_path => l_path_name,
640 p_string => '+Salavge Value Correction Failed : ');
642 END IF;
643 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
644 p_full_path => l_path_name,
645 p_string => '+Salavge Value Correction deprn_amount_cy after :' ||l_fa_deprn_amount_cy);
646 END IF;
647 /*salvage value correction*/
648
649
650 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
651 p_full_path => l_path_name,
652 p_string => ' Calling Revaluation Initialization program ');
653 IF NOT igi_iac_catchup_pkg.do_reval_init_struct(l_open_period.period_counter,
654 l_reval_control,
655 l_reval_asset_params,
656 l_reval_input_asset,
657 l_reval_output_asset,
658 l_reval_output_asset_mvmt,
659 l_reval_asset_rules,
660 l_prev_rate_info,
661 l_curr_rate_info_first,
662 l_curr_rate_info_next,
663 l_curr_rate_info,
664 l_reval_exceptions,
665 l_fa_asset_info,
666 l_fa_deprn_amount_py,
667 l_fa_deprn_amount_cy,
668 l_last_asset_period,
669 l_calling_function) THEN
670 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
671 p_full_path => l_path_name,
672 p_string => '*** Error in catchup pkg for revaluation initialization');
673 return FALSE;
674 END IF;
675 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
676 p_full_path => l_path_name,
677 p_string => ' Back from Revaluation Initialization');
678
679 FOR l_idx2 IN 1..l_idx1 LOOP
680
681 IF (l_idx2 <> 1) THEN
682
683 l_reval_asset := l_reval_output_asset(l_idx2 - 1);
684
685 /* Added + 1 for the first 2 parameters for bug 2411478 vgadde */
686 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
687 p_full_path => l_path_name,
688 p_string => ' Doing depreciation catchup for the periods between revaluation');
689 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_reval_asset_params(l_idx2 - 1).period_counter + 1,
690 l_reval_asset_params(l_idx2).period_counter + 1,
691 l_open_period.period_counter,
692 FALSE,
693 l_calling_function,
694 l_fa_deprn_amount_py,
695 l_fa_deprn_amount_cy,
696 l_last_asset_period,
697 p_deprn_reserve,
698 p_deprn_ytd,
699 l_reval_asset,
700 p_event_id )THEN
701 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
702 p_full_path => l_path_name,
703 p_string => '*** Error in depreciation catchup');
704 return FALSE;
705 END IF;
706 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
707 p_full_path => l_path_name,
708 p_string => ' Back from depreciation catchup');
709
710 l_current_reval_factor := l_reval_input_asset(l_idx2).current_reval_factor;
711 l_cumulative_reval_factor := l_reval_input_asset(l_idx2).cumulative_reval_factor;
712 l_reval_input_asset(l_idx2) := l_reval_asset;
713 l_reval_input_asset(l_idx2).current_reval_factor := l_current_reval_factor;
714 l_reval_input_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
715 END IF;
716
717 IF (l_idx2 = l_idx1) THEN
718 /* Last revaluation - Insert records into revaluation tables*/
719 IF (p_calling_function <> 'UPGRADE') THEN
720 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
721 p_full_path => l_path_name,
722 p_string => ' Last Revaluation - Inserting into igi_iac_revaluations');
723
724 l_rowid := NULL;
725 l_revaluation_id := NULL;
726
727 igi_iac_revaluations_pkg.insert_row
728 (l_rowid,
729 l_revaluation_id,
730 p_book_type_code,
731 sysdate,
732 l_reval_asset_params(l_idx1).period_counter,
733 'NEW',
734 NULL,
738 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
735 NULL,
736 'ADDITION',
737 X_event_id => p_event_id);
739 p_full_path => l_path_name,
740 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
741
742 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
743 p_full_path => l_path_name,
744 p_string => ' Inserting into igi_iac_reval_asset_rules');
745 l_rowid := NULL;
746 igi_iac_reval_asset_rules_pkg.insert_row
747 (l_rowid,
748 l_revaluation_id,
749 l_reval_asset_params(l_idx1).book_type_code,
750 l_reval_asset_params(l_idx1).category_id,
751 l_reval_asset_params(l_idx1).asset_id,
752 l_reval_asset_rules(l_idx1).revaluation_factor,
753 l_reval_asset_rules(l_idx1).revaluation_type,
754 l_reval_asset_rules(l_idx1).new_cost,
755 l_reval_input_asset(l_idx2).adjusted_cost,
756 'Y',
757 'N',
758 NULL
759 );
760 END IF; /* End of checking for UPGRADE */
761
762 l_last_reval_period := l_reval_asset_params(l_idx2).period_counter;
763 l_reval_asset_params(l_idx2).period_counter := l_open_period.period_counter;
764 l_reval_input_asset(l_idx2).period_counter := l_open_period.period_counter;
765 l_reval_asset_params(l_idx2).revaluation_id := l_revaluation_id;
766 l_reval_asset_rules(l_idx2).revaluation_id := l_revaluation_id;
767
768 IF (p_calling_function = 'UPGRADE') THEN
769 l_reval_control(l_idx2).calling_program := 'UPGRADE';
770 END IF;
771 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
772 p_full_path => l_path_name,
773 p_string => ' Period counter passed to Reval CRUD :'||to_char(l_open_period.period_counter ));
774
775 END IF;
776
777 l_reval_params.reval_control := l_reval_control(l_idx2);
778 l_reval_params.reval_asset_params := l_reval_asset_params(l_idx2);
779 l_reval_params.reval_input_asset := l_reval_input_asset(l_idx2);
780 l_reval_params.reval_output_asset := l_reval_input_asset(l_idx2);
781 l_reval_params.reval_output_asset_mvmt := l_reval_output_asset_mvmt(l_idx2);
782 l_reval_params.reval_asset_rules := l_reval_asset_rules(l_idx2);
783 l_reval_params.reval_prev_rate_info := l_prev_rate_info(l_idx2);
784 l_reval_params.reval_curr_rate_info_first := l_curr_rate_info_first(l_idx2);
785 l_reval_params.reval_curr_rate_info_next := l_curr_rate_info_next(l_idx2);
786 l_reval_params.reval_asset_exceptions := l_reval_exceptions(l_idx2);
787 l_reval_params.fa_asset_info := l_fa_asset_info(l_idx2);
788
789 /* call revaluation processing function here */
790 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
791 p_full_path => l_path_name,
792 p_string => ' Input asset balances to revaluation program');
793 Debug_Asset(l_reval_input_asset(l_idx2));
794
795 IF NOT igi_iac_reval_wrapper.do_reval_calc_asset(l_reval_params,
796 l_reval_asset_out) THEN
797 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
798 p_full_path => l_path_name,
799 p_string => '*** Error in Revaluation Program');
800 return FALSE;
801 END IF;
802
803 l_current_reval_factor := l_reval_output_asset(l_idx2).current_reval_factor;
804 l_cumulative_reval_factor := l_reval_output_asset(l_idx2).cumulative_reval_factor;
805 l_reval_output_asset(l_idx2) := l_reval_asset_out;
806 l_reval_output_asset(l_idx2).current_reval_factor := l_current_reval_factor;
807 l_reval_output_asset(l_idx2).cumulative_reval_factor := l_cumulative_reval_factor;
808
809 /* Bug 2425856 vgadde 20/06/2002 Start(1) */
810 BEGIN
811 IF (l_idx2 = l_idx1 and p_calling_function <> 'UPGRADE') THEN /* Last Revaluation */
812 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
813 p_full_path => l_path_name,
814 p_string => ' Last revaluation period :'||to_char(l_last_reval_period));
815 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
816 p_full_path => l_path_name,
817 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
818 UPDATE igi_iac_revaluation_rates
819 SET period_counter = l_last_reval_period
820 WHERE revaluation_id = l_revaluation_id
821 AND asset_id = p_asset_id
822 AND book_type_code = p_book_type_code;
823
824 IF SQL%FOUND then
825 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
826 p_full_path => l_path_name,
827 p_string => ' Records in reval rates updated for correct period');
828 ELSE
829 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
830 p_full_path => l_path_name,
831 p_string => '*** No record found in reval rates table to update');
832 return FALSE;
836 /* Bug 2425856 vgadde 20/06/2002 End(1) */
833 END IF;
834 END IF;
835 END;
837
838 END LOOP;
839
840 IF (l_last_reval_period < l_open_period.period_counter) THEN
841
842 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
843 p_full_path => l_path_name,
844 p_string => ' Doing the final catchup for depreciation');
845
846 l_reval_asset := l_reval_output_asset(l_idx1);
847 /* Added + 1 for the first parameter for bug 2411478 vgadde 12/06/2002 */
848 IF NOT igi_iac_catchup_pkg.do_deprn_catchup(l_last_reval_period + 1,
849 l_open_period.period_counter,
850 l_open_period.period_counter,
851 TRUE,
852 l_calling_function,
853 l_fa_deprn_amount_py,
854 l_fa_deprn_amount_cy,
855 l_last_asset_period,
856 p_deprn_reserve,
857 p_deprn_ytd,
858 l_reval_asset,
859 p_event_id )THEN
860 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
861 p_full_path => l_path_name,
862 p_string => '*** Error in depreciation catchup for final run');
863 return FALSE;
864 END IF;
865 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
866 p_full_path => l_path_name,
867 p_string => ' Output from final catchup');
868 Debug_Asset(l_reval_asset);
869
870 END IF;
871
872 /* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
873 IF (p_calling_function <> 'UPGRADE') THEN
874 l_get_latest_adjustment_id :=0;
875 OPEN C_Reval_Rates;
876 FETCH C_Reval_Rates into l_get_latest_adjustment_id;
877 CLOSE C_Reval_Rates;
878 IF NOT IGI_IAC_REVAL_CRUD.update_reval_rates (fp_adjustment_id => l_get_latest_adjustment_id) THEN
879 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
880 p_full_path => l_path_name,
881 p_string => '*** Failed to Update REVAL RATES');
882 return FALSE;
883 END IF;
884 END IF;
885
886 -- Added by Venkat Gadde
887 IF p_calling_function <> 'UPGRADE' THEN
888 UPDATE igi_iac_transaction_headers
889 SET event_id = p_event_id
890 WHERE book_type_code = p_book_type_code
891 AND asset_id = p_asset_id;
892
893 UPDATE igi_iac_adjustments
894 SET event_id = p_event_id
895 WHERE book_type_code = p_book_type_code
896 AND asset_id = p_asset_id;
897 END IF;
898 -- End of code added by Venkat Gadde
899 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
900 p_full_path => l_path_name,
901 p_string => '******* End of IAC Prior addition processing for asset *****');
902 return TRUE;
903
904 EXCEPTION
905 WHEN OTHERS THEN
906 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
907 return FALSE;
908 END Do_Addition;
909
910
911 FUNCTION Do_Rollback_Addition(
912 p_book_type_code VARCHAR2,
913 p_period_counter NUMBER,
914 p_calling_function VARCHAR2
915 ) return BOOLEAN IS
916
917 CURSOR c_get_asset_add_info IS
918 SELECT asset_id,adjustment_id,transaction_sub_type
919 FROM igi_iac_transaction_headers
920 WHERE book_type_code = p_book_type_code
921 AND period_counter = p_period_counter
922 AND transaction_type_code = 'ADDITION';
923
924 CURSOR c_get_distributions(p_asset_id igi_iac_det_balances.asset_id%TYPE,
925 p_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
926 SELECT distribution_id
927 FROM igi_iac_det_balances
928 WHERE book_type_code = p_book_type_code
929 AND asset_id = p_asset_id;
930
931 /* Bug 2425914 vgadde 21/06/2002 */
932 /* Modified query to fecth records created by ADDITION only */
933 CURSOR c_get_revaluation_info(p_asset_id igi_iac_det_balances.asset_id%TYPE) IS
934 SELECT a.revaluation_id
935 FROM igi_iac_revaluations r,igi_iac_reval_asset_rules a
936 WHERE a.revaluation_id = r.revaluation_id
937 AND a.book_type_code = p_book_type_code
938 AND a.asset_id = p_asset_id
939 AND r.calling_program = 'ADDITION';
940
941 CURSOR c_get_adjustments(p_asset_id igi_iac_adjustments.asset_id%TYPE,
942 p_adjustment_id igi_iac_adjustments.adjustment_id%TYPE) IS
943 SELECT 'X'
944 FROM igi_iac_adjustments
945 WHERE adjustment_id = p_adjustment_id
946 AND book_type_code = p_book_type_code
947 AND asset_id = p_asset_id
948 AND rownum = 1;
949
953 FROM igi_iac_asset_balances
950 CURSOR c_get_asset_balances(p_asset_id igi_iac_asset_balances.asset_id%TYPE,
951 cp_period_counter igi_iac_asset_balances.period_counter%TYPE) IS
952 SELECT 'X'
954 WHERE book_type_code = p_book_type_code
955 AND asset_id = p_asset_id
956 AND period_counter = cp_period_counter;
957
958 CURSOR c_get_revaluation_rates(p_asset_id igi_iac_revaluation_rates.asset_id%TYPE,
959 p_revaluation_id igi_iac_revaluation_rates.revaluation_id%TYPE) IS
960 SELECT 'X'
961 FROM igi_iac_revaluation_rates
962 WHERE asset_id = p_asset_id
963 AND book_type_code = p_book_type_code
964 AND revaluation_id = p_revaluation_id;
965
966 CURSOR c_get_fa_distributions(cp_asset_id igi_iac_det_balances.asset_id%TYPE,
967 cp_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
968 SELECT distribution_id
969 FROM igi_iac_fa_deprn
970 WHERE book_type_code = p_book_type_code
971 AND asset_id = cp_asset_id
972 AND adjustment_id = cp_adjustment_id;
973
974 l_revaluation_id igi_iac_revaluations.revaluation_id%TYPE;
975 l_dummy VARCHAR2(1);
976 l_path_name VARCHAR2(150) := g_path||'do_rollback_addition';
977
978 BEGIN
979 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
980 p_full_path => l_path_name,
981 p_string => '********* Start of IAC Additions Rollback **********');
982 FOR l_asset_info IN c_get_asset_add_info LOOP
983 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
984 p_full_path => l_path_name,
985 p_string => ' Processing for Asset :'||to_char(l_asset_info.asset_id));
986 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
987 p_full_path => l_path_name,
988 p_string => ' Adjustment :'||to_char(l_asset_info.adjustment_id));
989 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
990 p_full_path => l_path_name,
991 p_string => ' Transaction :'||l_asset_info.transaction_sub_type);
992
993 /* Delete records from igi_iac_adjustments */
994 l_dummy := NULL;
995 OPEN c_get_adjustments(l_asset_info.asset_id,l_asset_info.adjustment_id);
996 FETCH c_get_adjustments INTO l_dummy;
997
998 IF c_get_adjustments%FOUND THEN
999 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1000 p_full_path => l_path_name,
1001 p_string => ' Deleting records from igi_iac_adjustments');
1002 igi_iac_adjustments_pkg.delete_row(
1003 x_adjustment_id => l_asset_info.adjustment_id);
1004 ELSIF c_get_adjustments%NOTFOUND THEN
1005 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1006 p_full_path => l_path_name,
1007 p_string => ' No records found in igi_iac_adjustments for delete');
1008 END IF;
1009
1010 CLOSE c_get_adjustments;
1011
1012 /* Delete records from igi_iac_det_balances */
1013 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1014 p_full_path => l_path_name,
1015 p_string => ' Deleting records from igi_iac_det_balances');
1016 FOR l_det_balance IN c_get_distributions(l_asset_info.asset_id,
1017 l_asset_info.adjustment_id) LOOP
1018 igi_iac_det_balances_pkg.delete_row(
1019 x_adjustment_id => l_asset_info.adjustment_id,
1020 x_asset_id => l_asset_info.asset_id,
1021 x_distribution_id => l_det_balance.distribution_id,
1022 x_book_type_code => p_book_type_code,
1023 x_period_counter => p_period_counter);
1024 END LOOP;
1025
1026 /* Delete records from igi_iac_fa_deprn */
1027 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1028 p_full_path => l_path_name,
1029 p_string => ' Deleting records from igi_iac_fa_deprn');
1030 FOR l_iac_fa_det_balance IN c_get_fa_distributions(l_asset_info.asset_id,
1031 l_asset_info.adjustment_id) LOOP
1032 igi_iac_fa_deprn_pkg.delete_row(
1033 x_book_type_code => p_book_type_code,
1034 x_asset_id => l_asset_info.asset_id,
1035 x_period_counter => p_period_counter,
1036 x_adjustment_id => l_asset_info.adjustment_id,
1037 x_distribution_id => l_iac_fa_det_balance.distribution_id);
1038 END LOOP;
1039
1040 /* Delete records from igi_iac_asset_balances */
1041 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
1042
1043 OPEN c_get_asset_balances(l_asset_info.asset_id,p_period_counter);
1044 FETCH c_get_asset_balances INTO l_dummy;
1045
1046 IF c_get_asset_balances%FOUND THEN
1047 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1048 p_full_path => l_path_name,
1049 p_string => ' Deleting records from igi_iac_asset_balances for current period');
1050 igi_iac_asset_balances_pkg.delete_row(
1051 x_asset_id => l_asset_info.asset_id,
1052 x_book_type_code => p_book_type_code,
1053 x_period_counter => p_period_counter);
1057 p_string => ' No records found in igi_iac_asset_balances to delete');
1054 ELSIF c_get_asset_balances%NOTFOUND THEN
1055 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1056 p_full_path => l_path_name,
1058 END IF;
1059
1060 CLOSE c_get_asset_balances;
1061
1062 OPEN c_get_asset_balances(l_asset_info.asset_id, p_period_counter+1);
1063 FETCH c_get_asset_balances INTO l_dummy;
1064 IF c_get_asset_balances%FOUND THEN
1065 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1066 p_full_path => l_path_name,
1067 p_string => ' Deleting records from igi_iac_asset_balances for next period');
1068 igi_iac_asset_balances_pkg.delete_row(
1069 x_asset_id => l_asset_info.asset_id,
1070 x_book_type_code => p_book_type_code,
1071 x_period_counter => p_period_counter+1);
1072 ELSE
1073 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1074 p_full_path => l_path_name,
1075 p_string => ' No records found in igi_iac_asset_balances to delete');
1076 END IF;
1077 CLOSE c_get_asset_balances; -- Bug 2417394 this cursor was not gettign closed previously
1078 END IF;
1079
1080 /* Delete records from igi_iac_transaction_headers */
1081 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1082 p_full_path => l_path_name,
1083 p_string => ' Deleting records from igi_iac_transaction_headers');
1084 igi_iac_trans_headers_pkg.delete_row(
1085 x_adjustment_id => l_asset_info.adjustment_id);
1086
1087 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
1088
1089 l_revaluation_id := NULL;
1090 OPEN c_get_revaluation_info(l_asset_info.asset_id);
1091 FETCH c_get_revaluation_info INTO l_revaluation_id;
1092 CLOSE c_get_revaluation_info;
1093 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1094 p_full_path => l_path_name,
1095 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
1096
1097 /* Delete records from igi_iac_reval_asset_rules */
1098 IF (l_revaluation_id IS NOT NULL) THEN
1099
1100 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1101 p_full_path => l_path_name,
1102 p_string => ' Deleting records from igi_iac_reval_asset_rules');
1103 igi_iac_reval_asset_rules_pkg.delete_row(
1104 x_asset_id => l_asset_info.asset_id,
1105 x_book_type_code => p_book_type_code,
1106 x_revaluation_id => l_revaluation_id);
1107
1108 /* Delete records from igi_iac_revaluations */
1109 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1110 p_full_path => l_path_name,
1111 p_string => ' Deleting records from igi_iac_revaluations');
1112 igi_iac_revaluations_pkg.delete_row(
1113 x_revaluation_id => l_revaluation_id);
1114
1115 /* Delete records from igi_iac_revaluation_rates */
1116 OPEN c_get_revaluation_rates(l_asset_info.asset_id,l_revaluation_id);
1117 FETCH c_get_revaluation_rates INTO l_dummy;
1118
1119 IF c_get_revaluation_rates%FOUND THEN
1120 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1121 p_full_path => l_path_name,
1122 p_string => ' Deleting records from igi_iac_revaluation_rates');
1123 DELETE FROM igi_iac_revaluation_rates
1124 WHERE asset_id = l_asset_info.asset_id
1125 AND book_type_code = p_book_type_code
1126 AND revaluation_id = l_revaluation_id;
1127 END IF;
1128
1129 CLOSE c_get_revaluation_rates;
1130
1131 END IF;
1132
1133 END IF;
1134
1135 END LOOP;
1136 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1137 p_full_path => l_path_name,
1138 p_string => '********* End of IAC Additions Rollback **********');
1139 return TRUE;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1144 return FALSE;
1145
1146 END Do_Rollback_Addition;
1147
1148 END igi_iac_additions_pkg; -- Package body