[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_ROLLBACK_DEPRN_PKG
Source
1 PACKAGE BODY IGI_IAC_ROLLBACK_DEPRN_PKG as
2 /* $Header: igiacrdb.pls 120.1 2007/10/29 15:30:01 vkilambi noship $ */
3 --===========================FND_LOG.START=====================================
4
5 g_state_level NUMBER ;
6 g_proc_level NUMBER ;
7 g_event_level NUMBER ;
8 g_excep_level NUMBER ;
9 g_error_level NUMBER ;
10 g_unexp_level NUMBER ;
11 g_path VARCHAR2(1000) ;
12
13 --===========================FND_LOG.END=====================================
14
15
16 FUNCTION Do_Rollback_Deprn(
17 p_asset_hdr_rec fa_api_types.asset_hdr_rec_type,
18 p_period_rec fa_api_types.period_rec_type,
19 p_deprn_run_id NUMBER,
20 p_reversal_event_id NUMBER,
21 p_reversal_date DATE,
22 p_deprn_exists_count NUMBER,
23 p_calling_function VARCHAR2
24 ) return BOOLEAN is
25 begin
26 --Need to Implement Event Reversal Login here
27 --Need to Implement data backup (trf to history )table here
28 --Need to Call Addition and Transfer Catchup rollback here
29 --Need to Make Addition and Transfer Catchup rollback at asset level
30 null;
31 end;
32
33 FUNCTION Do_Rollback_Addition(
34 p_book_type_code VARCHAR2,
35 p_period_counter NUMBER,
36 p_calling_function VARCHAR2
37 ) return BOOLEAN IS
38 CURSOR c_get_asset_add_info IS
39 SELECT asset_id,adjustment_id,transaction_sub_type
40 FROM igi_iac_transaction_headers
41 WHERE book_type_code = p_book_type_code
42 AND period_counter = p_period_counter
43 AND transaction_type_code = 'ADDITION';
44 CURSOR c_get_distributions(p_asset_id igi_iac_det_balances.asset_id%TYPE,
45 p_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
46 SELECT distribution_id
47 FROM igi_iac_det_balances
48 WHERE book_type_code = p_book_type_code
49 AND asset_id = p_asset_id;
50 /* Bug 2425914 vgadde 21/06/2002 */
51 /* Modified query to fecth records created by ADDITION only */
52 CURSOR c_get_revaluation_info(p_asset_id igi_iac_det_balances.asset_id%TYPE) IS
53 SELECT a.revaluation_id
54 FROM igi_iac_revaluations r,igi_iac_reval_asset_rules a
55 WHERE a.revaluation_id = r.revaluation_id
56 AND a.book_type_code = p_book_type_code
57 AND a.asset_id = p_asset_id
58 AND r.calling_program = 'ADDITION';
59 CURSOR c_get_adjustments(p_asset_id igi_iac_adjustments.asset_id%TYPE,
60 p_adjustment_id igi_iac_adjustments.adjustment_id%TYPE) IS
61 SELECT 'X'
62 FROM igi_iac_adjustments
63 WHERE adjustment_id = p_adjustment_id
64 AND book_type_code = p_book_type_code
65 AND asset_id = p_asset_id
66 AND rownum = 1;
67 CURSOR c_get_asset_balances(p_asset_id igi_iac_asset_balances.asset_id%TYPE,
68 cp_period_counter igi_iac_asset_balances.period_counter%TYPE) IS
69 SELECT 'X'
70 FROM igi_iac_asset_balances
71 WHERE book_type_code = p_book_type_code
72 AND asset_id = p_asset_id
73 AND period_counter = cp_period_counter;
74 CURSOR c_get_revaluation_rates(p_asset_id igi_iac_revaluation_rates.asset_id%TYPE,
75 p_revaluation_id igi_iac_revaluation_rates.revaluation_id%TYPE) IS
76 SELECT 'X'
77 FROM igi_iac_revaluation_rates
78 WHERE asset_id = p_asset_id
79 AND book_type_code = p_book_type_code
80 AND revaluation_id = p_revaluation_id;
81 CURSOR c_get_fa_distributions(cp_asset_id igi_iac_det_balances.asset_id%TYPE,
82 cp_adjustment_id igi_iac_det_balances.adjustment_id%TYPE) IS
83 SELECT distribution_id
84 FROM igi_iac_fa_deprn
85 WHERE book_type_code = p_book_type_code
86 AND asset_id = cp_asset_id
87 AND adjustment_id = cp_adjustment_id;
88 l_revaluation_id igi_iac_revaluations.revaluation_id%TYPE;
89 l_dummy VARCHAR2(1);
90 l_path_name VARCHAR2(150) := g_path||'do_rollback_addition';
91 BEGIN
92 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
93 p_full_path => l_path_name,
94 p_string => '********* Start of IAC Additions Rollback **********');
95 FOR l_asset_info IN c_get_asset_add_info LOOP
96 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
97 p_full_path => l_path_name,
98 p_string => ' Processing for Asset :'||to_char(l_asset_info.asset_id));
99 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
100 p_full_path => l_path_name,
101 p_string => ' Adjustment :'||to_char(l_asset_info.adjustment_id));
102 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
103 p_full_path => l_path_name,
104 p_string => ' Transaction :'||l_asset_info.transaction_sub_type);
105 /* Delete records from igi_iac_adjustments */
106 l_dummy := NULL;
107 OPEN c_get_adjustments(l_asset_info.asset_id,l_asset_info.adjustment_id);
108 FETCH c_get_adjustments INTO l_dummy;
109 IF c_get_adjustments%FOUND THEN
110 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
111 p_full_path => l_path_name,
112 p_string => ' Deleting records from igi_iac_adjustments');
113 igi_iac_adjustments_pkg.delete_row(
114 x_adjustment_id => l_asset_info.adjustment_id);
115 ELSIF c_get_adjustments%NOTFOUND THEN
116 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
117 p_full_path => l_path_name,
118 p_string => ' No records found in igi_iac_adjustments for delete');
119 END IF;
120 CLOSE c_get_adjustments;
121 /* Delete records from igi_iac_det_balances */
122 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
123 p_full_path => l_path_name,
124 p_string => ' Deleting records from igi_iac_det_balances');
125 FOR l_det_balance IN c_get_distributions(l_asset_info.asset_id,
126 l_asset_info.adjustment_id) LOOP
127 igi_iac_det_balances_pkg.delete_row(
128 x_adjustment_id => l_asset_info.adjustment_id,
129 x_asset_id => l_asset_info.asset_id,
130 x_distribution_id => l_det_balance.distribution_id,
131 x_book_type_code => p_book_type_code,
132 x_period_counter => p_period_counter);
133 END LOOP;
134 /* Delete records from igi_iac_fa_deprn */
135 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
136 p_full_path => l_path_name,
137 p_string => ' Deleting records from igi_iac_fa_deprn');
138 FOR l_iac_fa_det_balance IN c_get_fa_distributions(l_asset_info.asset_id,
139 l_asset_info.adjustment_id) LOOP
140 igi_iac_fa_deprn_pkg.delete_row(
141 x_book_type_code => p_book_type_code,
142 x_asset_id => l_asset_info.asset_id,
143 x_period_counter => p_period_counter,
144 x_adjustment_id => l_asset_info.adjustment_id,
145 x_distribution_id => l_iac_fa_det_balance.distribution_id);
146 END LOOP;
147 /* Delete records from igi_iac_asset_balances */
148 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
149 OPEN c_get_asset_balances(l_asset_info.asset_id,p_period_counter);
150 FETCH c_get_asset_balances INTO l_dummy;
151 IF c_get_asset_balances%FOUND THEN
152 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
153 p_full_path => l_path_name,
154 p_string => ' Deleting records from igi_iac_asset_balances for current period');
155 igi_iac_asset_balances_pkg.delete_row(
156 x_asset_id => l_asset_info.asset_id,
157 x_book_type_code => p_book_type_code,
158 x_period_counter => p_period_counter);
159 ELSIF c_get_asset_balances%NOTFOUND THEN
160 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
161 p_full_path => l_path_name,
162 p_string => ' No records found in igi_iac_asset_balances to delete');
163 END IF;
164 CLOSE c_get_asset_balances;
165 OPEN c_get_asset_balances(l_asset_info.asset_id, p_period_counter+1);
166 FETCH c_get_asset_balances INTO l_dummy;
167 IF c_get_asset_balances%FOUND THEN
168 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
169 p_full_path => l_path_name,
170 p_string => ' Deleting records from igi_iac_asset_balances for next period');
171 igi_iac_asset_balances_pkg.delete_row(
172 x_asset_id => l_asset_info.asset_id,
173 x_book_type_code => p_book_type_code,
174 x_period_counter => p_period_counter+1);
175 ELSE
176 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
177 p_full_path => l_path_name,
178 p_string => ' No records found in igi_iac_asset_balances to delete');
179 END IF;
180 CLOSE c_get_asset_balances; -- Bug 2417394 this cursor was not gettign closed previously
181 END IF;
182 /* Delete records from igi_iac_transaction_headers */
183 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
184 p_full_path => l_path_name,
185 p_string => ' Deleting records from igi_iac_transaction_headers');
186 igi_iac_trans_headers_pkg.delete_row(
187 x_adjustment_id => l_asset_info.adjustment_id);
188 IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
189 l_revaluation_id := NULL;
190 OPEN c_get_revaluation_info(l_asset_info.asset_id);
191 FETCH c_get_revaluation_info INTO l_revaluation_id;
192 CLOSE c_get_revaluation_info;
193 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
194 p_full_path => l_path_name,
195 p_string => ' Revaluation Id :'||to_char(l_revaluation_id));
196 /* Delete records from igi_iac_reval_asset_rules */
197 IF (l_revaluation_id IS NOT NULL) THEN
198 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
199 p_full_path => l_path_name,
200 p_string => ' Deleting records from igi_iac_reval_asset_rules');
201 igi_iac_reval_asset_rules_pkg.delete_row(
202 x_asset_id => l_asset_info.asset_id,
203 x_book_type_code => p_book_type_code,
204 x_revaluation_id => l_revaluation_id);
205 /* Delete records from igi_iac_revaluations */
206 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
207 p_full_path => l_path_name,
208 p_string => ' Deleting records from igi_iac_revaluations');
209 igi_iac_revaluations_pkg.delete_row(
210 x_revaluation_id => l_revaluation_id);
211 /* Delete records from igi_iac_revaluation_rates */
212 OPEN c_get_revaluation_rates(l_asset_info.asset_id,l_revaluation_id);
213 FETCH c_get_revaluation_rates INTO l_dummy;
214 IF c_get_revaluation_rates%FOUND THEN
215 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
216 p_full_path => l_path_name,
217 p_string => ' Deleting records from igi_iac_revaluation_rates');
218 DELETE FROM igi_iac_revaluation_rates
219 WHERE asset_id = l_asset_info.asset_id
220 AND book_type_code = p_book_type_code
221 AND revaluation_id = l_revaluation_id;
222 END IF;
223 CLOSE c_get_revaluation_rates;
224 END IF;
225 END IF;
226 END LOOP;
227 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
228 p_full_path => l_path_name,
229 p_string => '********* End of IAC Additions Rollback **********');
230 return TRUE;
231 EXCEPTION
232 WHEN OTHERS THEN
233 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
234 return FALSE;
235 END Do_Rollback_Addition;
236
237 FUNCTION Do_Rollback_Transfer(
238 p_book_type_code VARCHAR2,
239 p_period_counter NUMBER,
240 p_calling_function VARCHAR2
241 ) return BOOLEAN IS
242 /* Cursor to select data from transaction headers which need to be rolled back */
243
244 CURSOR c_trans_headers IS
245 SELECT *
246 FROM igi_iac_transaction_headers
247 WHERE book_type_code=p_book_type_code
248 AND period_counter=p_period_counter
249 AND transaction_type_code='TRANSFER';
250
251
252 CURSOR c_deprn_expense(cp_asset_id igi_iac_det_balances.asset_id%TYPE,
253 cp_adjustment_id IGI_IAC_DET_BALANCES.adjustment_id%type) IS
254 SELECT sum(deprn_period)
255 FROM igi_iac_det_balances
256 WHERE book_type_code = p_book_type_code
257 AND asset_id = cp_asset_id
258 AND adjustment_id = cp_adjustment_id;
259
260 /* Cursor to select the previous data */
261
262 CURSOR c_prev_data(c_adjustment_id igi_iac_transaction_headers.adjustment_id%type) IS
263 SELECT *
264 FROM igi_iac_transaction_headers
265 WHERE adjustment_id_out=c_adjustment_id;
266
267 /* Cursor to find the amounts that need to be transferred to the new dist
268 created by transfer */
269
270 CURSOR c_amounts(c_period_counter in IGI_IAC_ASSET_BALANCES.period_counter%type,
271 cp_asset_id IGI_IAC_ASSET_BALANCES.asset_id%TYPE) IS
272 SELECT *
273 FROM igi_iac_asset_balances
274 WHERE asset_id = cp_asset_id
275 AND book_type_code = p_book_type_code
276 AND period_counter = p_period_counter;
277
278 /* Cursor to select impacted distributions for roll back */
279
280 CURSOR c_dist(c_adjustment_id igi_iac_adjustments.adjustment_id%type) IS
281 SELECT distribution_id
282 FROM igi_iac_det_balances
283 WHERE adjustment_id=c_adjustment_id;
284
285 CURSOR c_fa_dist(cp_adjustment_id igi_iac_adjustments.adjustment_id%type,
286 cp_asset_id igi_iac_fa_deprn.asset_id%TYPE) IS
287 SELECT book_type_code,asset_id,period_counter,distribution_id,adjustment_id
288 FROM igi_iac_fa_deprn
289 WHERE asset_id = cp_asset_id
290 AND book_type_code = p_book_type_code
291 AND adjustment_id = cp_adjustment_id;
292
293
294
295 l_trans_headers c_trans_headers%rowtype;
296 l_amounts c_amounts%rowtype;
297 l_prd_rec_prior igi_iac_types.prd_rec;
298
299 l_deprn_expense FA_DEPRN_SUMMARY.deprn_amount%type;
300 l_Expense_diff IGI_IAC_DET_BALANCES.deprn_period%type;
301 l_prior_period_counter IGI_IAC_DET_BALANCES.period_counter%type;
302 l_current_period_counter IGI_IAC_DET_BALANCES.period_counter%type;
303 l_prev_adjustment igi_iac_transaction_headers%ROWTYPE;
304
305 l_path varchar2(150);
306 BEGIN
307
308 l_path := g_path||'Do_Rollback_Deprn';
309
310 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
311 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Rollback of Transactions ');
312 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
313
314 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Processing For book '||p_book_type_code);
315 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Current Period Counter :'||p_period_counter);
316
317
318 FOR l_trans_headers in c_trans_headers
319 LOOP
320 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction header Id :'||l_trans_headers.transaction_header_id);
321 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Date Entered :'||l_trans_headers.transaction_date_entered);
322 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment Id :'||l_trans_headers.adjustment_id);
323 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset Id :'||l_trans_headers.asset_id);
324
325
326 /*Check If it is Prior Period Transfer*/
327 IF (IGI_IAC_COMMON_UTILS.get_period_info_for_date(p_book_type_code,
328 l_trans_headers.transaction_date_entered,
329 l_prd_rec_prior)) THEN
330 l_prior_period_counter:=l_prd_rec_prior.period_counter;
331 END IF;
332
333
334 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Period counter :'||l_prior_period_counter);
335
336 IF l_prior_period_counter is not null AND (L_prior_period_counter< p_period_counter) THEN
337
338 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Processing prior period data on this asset');
339
340 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
341 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From detail balances...');
342 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
343
344 /*DELETE from igi_iac_det_balances
345 WHERE book_type_code=p_book_type_code
346 AND period_counter=p_period_counter
347 AND adjustment_id=l_trans_headers.adjustment_id;*/
348
349
350 FOR l_dist in c_dist(l_trans_headers.adjustment_id)
351 loop
352 IGI_IAC_DET_BALANCES_PKG.delete_row(
353 x_adjustment_id =>l_trans_headers.adjustment_id,
354 x_asset_id =>l_trans_headers.asset_id,
355 x_distribution_id =>l_dist.distribution_id,
356 x_book_type_code =>l_trans_headers.book_type_code,
357 x_period_counter =>p_period_counter
358 );
359 End loop;
360
361 FOR l_fa_dist IN c_fa_dist(l_trans_headers.adjustment_id,l_trans_headers.asset_id) LOOP
362 IGI_IAC_FA_DEPRN_PKG.delete_row(
363 x_adjustment_id =>l_fa_dist.adjustment_id,
364 x_asset_id =>l_fa_dist.asset_id,
365 x_distribution_id =>l_fa_dist.distribution_id,
366 x_book_type_code =>l_fa_dist.book_type_code,
367 x_period_counter =>l_fa_dist.period_counter
368 );
369 END LOOP;
370
371 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
372 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From adjustments...');
373 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
374
375 /* DELETE from igi_iac_adjustments
376 WHERE book_type_code=p_book_type_code
377 AND period_counter=p_period_counter
378 AND adjustment_id=l_trans_headers.adjustment_id; */
379
380 IGI_IAC_ADJUSTMENTS_PKG.delete_row(
381 x_adjustment_id =>l_trans_headers.adjustment_id
382 );
383
384 OPEN c_prev_data(l_trans_headers.adjustment_id);
385 FETCH c_prev_data INTO l_prev_adjustment;
386 CLOSE c_prev_data;
387
388 open c_deprn_expense(l_trans_headers.asset_id,l_prev_adjustment.adjustment_id);
389 fetch c_deprn_expense into l_deprn_expense;
390 close c_deprn_expense;
391
392 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Expense Amount to be adjusted :'||l_deprn_expense);
393
394 open c_amounts(p_period_counter,l_trans_headers.asset_id);
395 fetch c_amounts into l_amounts;
396 close c_amounts;
397
398 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
399 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updating Asset balances ...');
400 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
401
402 IGI_IAC_ASSET_BALANCES_PKG.update_row (
403 x_asset_id =>l_trans_headers.asset_id,
404 x_book_type_code =>p_book_type_code,
405 x_period_counter =>p_period_counter,
406 x_net_book_value =>l_amounts.net_book_value,
407 x_adjusted_cost =>l_amounts.adjusted_cost,
408 x_operating_acct =>l_amounts.operating_acct,
409 x_reval_reserve =>l_amounts.reval_reserve,
410 x_deprn_amount =>l_deprn_expense,
411 x_deprn_reserve =>l_amounts.deprn_reserve,
412 x_backlog_deprn_reserve =>l_amounts.backlog_deprn_reserve,
413 x_general_fund =>l_amounts.general_fund,
414 x_last_reval_date =>l_amounts.last_reval_date,
415 x_current_reval_factor =>l_amounts.current_reval_factor,
416 x_cumulative_reval_factor =>l_amounts.cumulative_reval_factor,
417 x_mode =>'R'
418 );
419
420
421 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
422 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updating transaction headers...');
423 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
424
425 FOR l_prev_data in c_prev_data(l_trans_headers.adjustment_id)
426 LOOP
427 IGI_IAC_TRANS_HEADERS_PKG.update_row (
428 x_prev_adjustment_id =>l_prev_data.adjustment_id,
429 x_adjustment_id =>null,
430 x_mode =>'R'
431 );
432 END LOOP;
433
434
435 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
436 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From transaction headers...');
437 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
438
439 IGI_IAC_TRANS_HEADERS_PKG.delete_row (
440 x_adjustment_id =>l_trans_headers.adjustment_id
441 );
442
443 END IF;
444
445
446 END LOOP; -- End of First for
447
448 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
449 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Rollback completed successfully ');
450 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
451
452 RETURN(TRUE);
453 END Do_rollback_Transfer;
454
455 BEGIN
456 --===========================FND_LOG.START=====================================
457
458 g_state_level := FND_LOG.LEVEL_STATEMENT;
459 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
460 g_event_level := FND_LOG.LEVEL_EVENT;
461 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
462 g_error_level := FND_LOG.LEVEL_ERROR;
463 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
464 g_path := 'IGI.PLSQL.igiacrbb.igi_iac_rollback_deprn_pkg.';
465
466 --===========================FND_LOG.END=====================================
467
468 END IGI_IAC_ROLLBACK_DEPRN_PKG;
469