[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_RETIREMENT
Source
1 PACKAGE BODY IGI_IAC_RETIREMENT AS
2 -- $Header: igiiartb.pls 120.28.12000000.3 2007/11/16 13:45:29 pakumare ship $
3
4 --global variables
5 g_calling_fn VARCHAR2(200);
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13 g_latest_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
14 g_latest_trx_type igi_iac_transaction_headers.transaction_type_code%TYPE DEFAULT NULL;
15 g_mass_reference_id igi_iac_transaction_headers.mass_reference_id%TYPE ;
16 g_latest_trx_id igi_iac_transaction_headers.transaction_header_id%TYPE ;
17 g_latest_adj_status igi_iac_transaction_headers.adjustment_status%TYPE ;
18 g_prd_rec igi_iac_types.prd_rec ;
19 g_retire_prd_rec igi_iac_types.prd_rec ;
20 g_retire_rec fa_api_types.asset_retire_rec_type ;
21 g_retirement_type VARCHAR2(4) ;
22 g_retirement_period_type VARCHAR2(10) ;
23 g_ret_type_long VARCHAR2(20) ;
24 g_retirement_factor NUMBER ;
25 g_err_msg VARCHAR2(250) ;
26 g_retirement_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
27 g_asset_category_id fa_additions_b.asset_category_id%TYPE ;
28 g_rowid ROWID ;
29 g_sob_id NUMBER ;
30 g_coa_id NUMBER ;
31 g_currency VARCHAR2(10);
32 g_precision NUMBER ;
33 g_is_first_period BOOLEAN;
34 g_period_rec igi_iac_types.prd_rec ;
35 g_prior_prd_count NUMBER ;
36 g_retirement_prior_period NUMBER;
37 g_total_asset_units Number;
38
39 CURSOR c_fa_trx (p_trx_id NUMBER) IS
40 SELECT transaction_date_entered,
41 mass_reference_id
42 FROM fa_transaction_headers
43 WHERE transaction_header_id = p_trx_id ;
44
45 g_fa_trx c_fa_trx%ROWTYPE;
46
47 CURSOR c_fa_adds (p_c_asset_id NUMBER) IS
48 SELECT asset_category_id
49 FROM fa_additions_b
50 WHERE asset_id = p_c_asset_id ;
51
52
53 CURSOR c_detail_balances (p_current_adjustment NUMBER) IS
54 SELECT *
55 FROM igi_iac_det_balances
56 WHERE adjustment_id = p_current_adjustment ;
57
58 CURSOR c_units_per_dist (p_c_asset_id NUMBER, p_c_book_type_code VARCHAR2, p_dist_id NUMBER) IS
59 SELECT units_assigned, transaction_units
60 FROM fa_distribution_history
61 WHERE asset_id = p_c_asset_id
62 AND book_type_code = p_c_book_type_code
63 AND distribution_id = p_dist_id ;
64
65 g_units_per_dist c_units_per_dist%ROWTYPE ;
66
67 CURSOR c_asset_balances (p_c_asset_id NUMBER, p_c_book_type_code VARCHAR2, p_period_counter NUMBER) IS
68 SELECT *
69 FROM igi_iac_asset_balances
70 WHERE asset_id = p_c_asset_id
71 AND book_type_code = p_c_book_type_code
72 AND period_counter = p_period_counter ;
73
74 g_asset_balances_rec igi_iac_asset_balances%ROWTYPE ;
75 CURSOR c_new_distribution (p_c_retirement_id NUMBER, p_old_distribution NUMBER ) IS
76 SELECT new.distribution_id
77 FROM fa_distribution_history NEW, fa_distribution_history old
78 WHERE old.retirement_id = p_c_retirement_id
79 AND old.location_id = new.location_id
80 AND old.code_combination_id = new.code_combination_id
81 AND NVL(old.assigned_to,-99) = NVL(new.assigned_to,-99)
82 AND old.transaction_header_id_out = new.transaction_header_id_in
83 AND old.distribution_id = p_old_distribution ;
84
85 CURSOR c_total_units (p_c_asset_id NUMBER) IS
86 SELECT units
87 FROM fa_asset_history
88 WHERE asset_id = p_c_asset_id
89 AND transaction_header_id_out IS NULL ;
90
91 CURSOR c_previous_per (p_adjustment_id NUMBER) IS
92 SELECT period_counter
93 FROM igi_iac_transaction_headers
94 WHERE adjustment_id = p_adjustment_id ;
95
96
97 CURSOR c_fa_deprn(n_adjust_id NUMBER, n_dist_id NUMBER,n_prd_cnt NUMBER)
98 IS
99 SELECT *
100 FROM igi_iac_fa_deprn ifd
101 WHERE ifd.adjustment_id = n_adjust_id
102 AND ifd.distribution_id = n_dist_id
103 AND ifd.period_counter = n_prd_cnt;
104
105
106 CURSOR c_get_impacted_dist(p_asset_id Number,
107 p_retirement_id Number,
108 p_distribution_id Number )
109 IS
110 SELECT *
111 FROM fa_distribution_history fad
112 WHERE fad.asset_id = p_asset_id
113 AND fad.retirement_id = p_retirement_id
114 AND fad.distribution_id = p_distribution_id;
115
116 CURSOR c_get_new_dist(p_asset_id Number,
117 p_retirement_id Number,
118 p_code_combination_id Number,
119 p_transaction_units Number,
120 p_location_id Number,
121 P_assigned_to Number)
122 IS
123 SELECT *
124 FROM fa_distribution_history fad
125 WHERE fad.asset_id = p_asset_id
126 AND fad.code_combination_id = p_code_combination_id
127 AND fad.units_assigned = p_transaction_units
128 AND fad.location_id = p_location_id
129 AND NVL(fad.assigned_to,-1) = p_assigned_to
130 AND fad.transaction_header_id_out IS NULL;
131
132
133 CURSOR c_check_revaluations (p_asset_id fa_books.asset_id%TYPE,
134 p_book_type_code fa_books.book_type_code%TYPE,
135 p_retire_period Number,
136 p_current_period NUmber) IS
137 SELECT *
138 FROM igi_iac_transaction_headers
139 WHERE book_type_code = p_book_type_code
140 AND asset_id = p_asset_id
141 AND period_counter >= p_retire_period
142 AND period_counter <= p_current_period
143 AND transaction_type_code='REVALUATION'
144 AND adjustment_status='RUN';
145
146
147 CURSOR c_get_all_occ_reval(p_asset_id fa_books.asset_id%TYPE,
148 p_book_type_code fa_books.book_type_code%TYPE,
149 p_retire_period Number,
150 p_current_period NUmber,
151 p_distribution_id Number ) IS
152 SELECT asset_id,distribution_id,adjustment_type,SUM(DECODE(dr_cr_flag,'CR',1,-1)*Amount) amount,
153 adjustment_offset_type,report_ccid,code_combination_id,units_assigned
154 FROM igi_iac_adjustments
155 WHERE book_type_code = p_book_type_code
156 AND asset_id = p_asset_id
157 AND distribution_id = p_distribution_id
158 AND adjustment_id IN (SELECT adjustment_id
159 FROM igi_iac_transaction_headers
160 WHERE book_type_code = p_book_type_code
161 AND asset_id = p_asset_id
162 AND period_counter >= p_retire_period
163 AND period_counter <= p_current_period
164 AND transaction_type_code ='REVALUATION'
165 AND adjustment_status ='RUN')
166 GROUP BY asset_id,distribution_id,adjustment_type,adjustment_offset_type,report_ccid,code_combination_id,units_assigned;
167
168 CURSOR c_check_depreciations (p_asset_id fa_books.asset_id%TYPE,
169 p_book_type_code fa_books.book_type_code%TYPE,
170 p_retire_period Number,
171 p_current_period NUmber) IS
172 SELECT *
173 FROM igi_iac_transaction_headers
174 WHERE book_type_code = p_book_type_code
175 AND asset_id = p_asset_id
176 AND period_counter >= p_retire_period
177 AND period_counter <= p_current_period
178 AND transaction_type_code='DEPRECIATION'
179 AND adjustment_status='COMPLETE';
180
181
182 CURSOR c_get_all_prd_reval(p_asset_id fa_books.asset_id%TYPE,
183 p_book_type_code fa_books.book_type_code%TYPE,
184 p_retire_period Number,
185 p_current_period NUmber,
186 p_distribution_id Number ) IS
187 SELECT asset_id,distribution_id,adjustment_type,SUM(DECODE(dr_cr_flag,'CR',1,-1)*Amount) amount,
188 adjustment_offset_type,report_ccid,code_combination_id,units_assigned
189 FROM igi_iac_adjustments
190 WHERE book_type_code = p_book_type_code
191 AND asset_id = p_asset_id
192 AND distribution_id = p_distribution_id
193 AND adjustment_id IN (SELECT adjustment_id
194 FROM igi_iac_transaction_headers
195 WHERE book_type_code = p_book_type_code
196 AND asset_id = p_asset_id
197 AND period_counter >= p_retire_period
198 AND period_counter <= p_current_period
199 AND transaction_type_code ='DEPRECIATION'
200 AND adjustment_status ='COMPLETE')
201 GROUP BY asset_id,distribution_id,adjustment_type,adjustment_offset_type,report_ccid,code_combination_id,units_assigned;
202
203
204 CURSOR C_get_prior_dist ( p_asset_id fa_books.asset_id%TYPE,
205 p_book_type_code fa_books.book_type_code%TYPE,
206 p_distribution_id Number,
207 P_period_counter Number) IS
208 SELECT *
209 FROM igi_iac_det_balances
210 WHERE book_type_code = p_book_type_code
211 AND asset_id = p_asset_id
212 AND distribution_id = p_distribution_id
213 AND adjustment_id = (SELECT MAX(adjustment_id)
214 FROM igi_iac_transaction_headers
215 WHERE asset_id = p_asset_id
216 AND book_type_code = p_book_type_code
217 AND period_counter = ( SELECT max(period_counter)
218 FROM igi_iac_det_balances
219 WHERE book_type_code = p_book_type_code
220 AND asset_id = p_asset_id
221 AND distribution_id = p_distribution_id
222 AND period_counter < p_period_counter )
223 AND adjustment_status NOT IN ('PREVIEW','OBSOLETE'));
224
225
226
227 -- get the asset number
228 Cursor C_get_asset_number(cp_asset_id Number) iS
229 Select asset_number
230 From fa_additions
231 Where asset_id = cp_asset_id;
232
233 e_is_asset_proc EXCEPTION;
234 e_no_latest_trans EXCEPTION;
235 e_reval_preview EXCEPTION;
236 e_no_open_prd_info EXCEPTION;
237 e_no_retire_rec EXCEPTION;
238 e_no_retire_type EXCEPTION;
239 e_no_book_gl EXCEPTION;
240 e_no_cost_retire EXCEPTION;
241 e_no_account_ccid EXCEPTION;
242 e_no_units_info EXCEPTION;
243 e_asset_balance_verify EXCEPTION;
244 e_no_new_distribution EXCEPTION;
245 e_no_asset_bals EXCEPTION;
246 e_no_retire_period EXCEPTION;
247 e_iac_fa_deprn EXCEPTION;
248 g_path_name VARCHAR2(150);
249
250
251
252 PROCEDURE debug (p_level IN NUMBER,
253 p_full_path IN VARCHAR2,
254 p_string IN VARCHAR2) IS
255 BEGIN
256 igi_iac_debug_pkg.debug_other_string( p_level =>p_level,
257 p_Full_path =>P_full_path,
258 p_string =>p_string);
259
260 END;
261
262 FUNCTION Cost_Retirement ( P_Asset_Id IN NUMBER ,
263 P_Book_Type_Code IN VARCHAR2 ,
264 P_Retirement_Id IN NUMBER ,
265 P_retirement_type IN VARCHAR2,
266 p_retirement_factor IN NUMBER ,
267 p_retirement_period_type IN VARCHAR2,
268 P_prior_period IN NUMBER,
269 P_Current_period IN NUMBER,
270 P_Event_Id IN NUMBER) --R12 uptake
271 RETURN BOOLEAN IS
272
273 l_rowid ROWID;
274 l_asset_balances igi_iac_asset_balances%ROWTYPE;
275 l_asset_balances_rec igi_iac_asset_balances%ROWTYPE;
276 l_detail_balances igi_iac_det_balances%ROWTYPE;
277 l_detail_balances_new igi_iac_det_balances%ROWTYPE;
278 l_detail_balances_total_old igi_iac_det_balances%ROWTYPE;
279 l_detail_balances_retire igi_iac_det_balances%ROWTYPE;
280 l_detail_balances_retire_unrnd igi_iac_det_balances%ROWTYPE;
281 l_detail_balances_rnd_tot igi_iac_det_balances%ROWTYPE;
282 l_fa_deprn igi_iac_fa_deprn%ROWTYPE;
283 l_units_per_dist c_units_per_dist%ROWTYPE;
284 l_cost_account_ccid NUMBER ;
285 l_acc_deprn_account_ccid NUMBER ;
286 l_reval_rsv_account_ccid NUMBER ;
287 l_backlog_account_ccid NUMBER ;
288 l_nbv_retired_account_ccid NUMBER ;
289 l_reval_rsv_ret_acct_ccid NUMBER ;
290 l_deprn_exp_account_ccid NUMBER ;
291 l_account_gen_fund_ccid NUMBER;
292 l_new_units NUMBER ;
293 l_new_distribution NUMBER ;
294 l_units_before NUMBER ;
295 l_units_after NUMBER ;
296 l_ret BOOLEAN ;
297 l_total_asset_units NUMBER ;
298 l_asset_units_count NUMBER ;
299 l_previous_per NUMBER ;
300 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
301 l_last_active_adj_id igi_iac_transaction_headers.adjustment_id%TYPE ;
302 l_db_op_acct_ytd igi_iac_det_balances.operating_acct_ytd%TYPE;
303 l_db_deprn_ytd igi_iac_det_balances.deprn_ytd%TYPE;
304 l_fa_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
305 l_fa_tot_round_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
306 l_fa_unround_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
307 l_fa_total_old_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
308 l_fa_retire_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
309 l_fa_tot_round_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
310 l_fa_unround_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
311 l_fa_total_new_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
312 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
313 l_fa_tot_round_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
314 l_fa_unround_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
315 l_fa_total_new_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
316 l_fa_total_new_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
317 l_fa_total_old_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
318 l_fa_total_old_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
319 l_fa_retire_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
320 l_fa_retire_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
321 l_op_exp_ccid NUMBER;
322 l_Transaction_Type_Code igi_iac_transaction_headers.transaction_type_code%TYPE;
323 l_Transaction_Id igi_iac_transaction_headers.transaction_header_id%TYPE;
324 l_Mass_Reference_ID igi_iac_transaction_headers.mass_reference_id%TYPE;
325 l_Adjustment_Status igi_iac_transaction_headers.adjustment_status%TYPE;
326 l_adjustment_id_out igi_iac_adjustments.adjustment_id%TYPE;
327 l_retirement_adjustment_id NUmber;
328 l_path_name varchar2(200);
329 l_detail_balances_prior C_get_prior_dist%ROWTYPE;
330 l_max_period_counter NUMBER;
331
332 BEGIN
333
334 l_path_name:=g_path_name ||'.Cost_Retiremet';
335
336 /* Initialize Asset total balance variables */
337 ------------------------
338 l_asset_balances.Asset_id :=P_Asset_Id ;
339 l_asset_balances.book_type_code :=P_Book_Type_Code;
340 l_asset_balances.period_counter :=P_Current_period;
341 l_asset_balances.net_book_value :=0;
342 l_asset_balances.adjusted_cost :=0;
343 l_asset_balances.operating_acct :=0;
344 l_asset_balances.reval_reserve :=0;
345 l_asset_balances.deprn_amount :=0;
346 l_asset_balances.deprn_reserve :=0;
347 l_asset_balances.backlog_deprn_reserve:=0;
348 l_asset_balances.general_fund :=0;
349
350
351 l_Transaction_Type_Code := NULL;
352 l_Transaction_Id := NULL;
353 l_Mass_Reference_ID := NULL;
354 l_adjustment_id_out := NULL;
355 l_prev_adjustment_id := NULL;
356 l_Adjustment_Status := NULL;
357 l_retirement_adjustment_id :=NULL;
358
359 debug(g_state_level,l_path_name,'Asset ID '||P_Asset_Id);
360 -- get the latest tranaction for the asset id
361
362 IF NOT (igi_iac_common_utils.get_latest_transaction(P_Book_Type_Code,
363 P_Asset_Id,
364 l_Transaction_Type_Code,
365 l_Transaction_Id,
366 l_Mass_Reference_ID ,
367 l_adjustment_id_out,
368 l_prev_adjustment_id,
369 l_Adjustment_Status )
370 ) THEN
371 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path_name,'*** Error in fetching the latest transaction');
372 RETURN FALSE;
373 END IF;
374
375 debug(g_state_level,l_path_name,'got latest transaction');
376 l_last_active_adj_id := l_prev_adjustment_id ;
377 debug( g_state_level,l_path_name,'not reval in preview');
378
379 l_rowid:=NULL;
380 igi_iac_trans_headers_pkg.insert_row(
381 X_rowid => l_rowid ,
382 X_adjustment_id => l_retirement_adjustment_id ,
383 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
384 X_adjustment_id_out => NULL ,
385 X_transaction_type_code => g_ret_type_long,
386 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
387 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
388 X_transaction_sub_type => SUBSTR(g_retirement_type,1,1),
389 X_book_type_code => P_Book_Type_Code,
390 X_asset_id => p_asset_id ,
391 X_category_id => g_asset_category_id,
392 X_adj_deprn_start_date => NULL,
393 X_revaluation_type_flag => NULL,
394 X_adjustment_status => 'COMPLETE' ,
395 X_period_counter => P_Current_period,
396 X_mode =>'R',
397 X_event_id => P_Event_Id) ;
398 debug( g_state_level, l_path_name,'inserted trans_headers record');
399
400 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
401 l_retirement_adjustment_id,
402 'R') ;
403
404 debug( g_state_level,l_path_name,'updated old trans_headers record');
405 debug( g_state_level,l_path_name,'Start loop');
406
407
408 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
409
410 -- since the equivalent row in igi_iac_fa_deprn has to handled in
411 -- the same manner, retrieving the row for simultaneous processing
412 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
413 l_detail_balances.distribution_id,
414 l_detail_balances.period_counter);
415 FETCH c_fa_deprn INTO l_fa_deprn;
416 IF c_fa_deprn%NOTFOUND THEN
417 CLOSE c_fa_deprn;
418 RETURN FALSE;
419 END IF;
420 CLOSE c_fa_deprn;
421
422 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
423
424 debug( g_state_level,l_path_name,'inside loop');
425
426
427 l_detail_balances_rnd_tot.adjustment_cost :=0 ;
428 l_detail_balances_rnd_tot.reval_reserve_cost :=0 ;
429 l_detail_balances_rnd_tot.deprn_reserve :=0 ;
430 l_detail_balances_rnd_tot.deprn_reserve_backlog :=0 ;
431 l_detail_balances_rnd_tot.reval_reserve_net :=0 ;
432 l_detail_balances_rnd_tot.deprn_period :=0 ;
433 l_detail_balances_rnd_tot.general_fund_acc :=0 ;
434 l_detail_balances_rnd_tot.general_fund_per :=0 ;
435 l_detail_balances_rnd_tot.reval_reserve_gen_fund :=0 ;
436 l_detail_balances_rnd_tot.operating_acct_backlog :=0;
437 l_detail_balances_rnd_tot.operating_acct_cost :=0;
438 l_detail_balances_rnd_tot.operating_acct_net :=0;
439 l_detail_balances_rnd_tot.reval_reserve_backlog :=0;
440 l_detail_balances_rnd_tot.deprn_ytd :=0;
441
442
443 IF (l_detail_balances.active_flag IS NULL) THEN
444
445 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
446
447 OPEN c_units_per_dist(P_Asset_Id, P_Book_Type_Code, l_detail_balances.distribution_id) ;
448 debug( g_state_level, l_path_name,'opened c_units_per_dist');
449 FETCH c_units_per_dist INTO l_units_per_dist ;
450 IF c_units_per_dist%NOTFOUND THEN
451 CLOSE c_units_per_dist;
452 debug( g_state_level,l_path_name,'units per dist not found');
453 RAISE NO_DATA_FOUND;
454 END IF ;
455 CLOSE c_units_per_dist ;
456
457 debug( g_state_level,l_path_name,'got units per dist');
458 l_detail_balances_total_old := l_detail_balances;
459
460 l_fa_total_old_acc_deprn := l_fa_deprn.deprn_reserve;
461
462 /* Calculate retirement amounts */
463 debug( g_state_level,l_path_name,'adjustment_cost ' || l_detail_balances.adjustment_cost);
464 debug( g_state_level,l_path_name,'reval_reserve_cost ' || l_detail_balances.reval_reserve_cost);
465 debug( g_state_level,l_path_name,'deprn_reserve ' ||l_detail_balances.deprn_reserve);
466 debug( g_state_level,l_path_name,'deprn_reserve_backlog ' ||l_detail_balances.deprn_reserve_backlog);
467 debug( g_state_level,l_path_name,'reval_reserve_nett ' ||l_detail_balances.reval_reserve_net);
468 debug( g_state_level,l_path_name,'operating_acct_backlog' ||l_detail_balances.operating_acct_backlog);
469 debug( g_state_level,l_path_name,'operating_acct_net ' ||l_detail_balances.operating_acct_net);
470 debug( g_state_level,l_path_name,'reval_reserve_backlog ' ||l_detail_balances.reval_reserve_backlog);
471
472 IF p_prior_period IS NULL THEN
473
474 l_detail_balances_retire.adjustment_cost := l_detail_balances.adjustment_cost* p_retirement_factor ;
475 l_detail_balances_retire.reval_reserve_cost := l_detail_balances.reval_reserve_cost* p_retirement_factor ;
476 l_detail_balances_retire.deprn_reserve := l_detail_balances.deprn_reserve * p_retirement_factor ;
477 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog * p_retirement_factor ;
478 l_detail_balances_retire.reval_reserve_net := l_detail_balances.reval_reserve_net * p_retirement_factor ;
479 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period * (1 - p_retirement_factor) ;
480 l_detail_balances_retire.general_fund_acc := l_detail_balances.general_fund_acc * p_retirement_factor;
481 l_detail_balances_retire.general_fund_per := l_detail_balances.general_fund_per * p_retirement_factor;
482 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund * p_retirement_factor;
483 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances.operating_acct_backlog * p_retirement_factor;
484 l_detail_balances_retire.operating_acct_cost :=l_detail_balances.operating_acct_cost * p_retirement_factor;
485 l_detail_balances_retire.operating_acct_net :=l_detail_balances.operating_acct_net * p_retirement_factor;
486 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog * p_retirement_factor ;
487 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd * p_retirement_factor;
488
489 ELSE
490 -- get the prior record for the distribution
491 OPEN C_get_prior_dist(p_asset_id,p_book_type_code,l_detail_balances.distribution_id,p_prior_period);
492 FETCH c_get_prior_dist INTO l_detail_balances_prior;
493 CLOSE c_get_prior_dist;
494
495 l_detail_balances_retire.adjustment_cost := l_detail_balances_prior.adjustment_cost* p_retirement_factor ;
496 l_detail_balances_retire.reval_reserve_cost := l_detail_balances_prior.reval_reserve_cost* p_retirement_factor ;
497 l_detail_balances_retire.deprn_reserve := l_detail_balances_prior.deprn_reserve * p_retirement_factor ;
498 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_prior.deprn_reserve_backlog * p_retirement_factor ;
499 l_detail_balances_retire.reval_reserve_net := l_detail_balances_prior.reval_reserve_net * p_retirement_factor ;
500 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period * (1 - p_retirement_factor) ;
501 l_detail_balances_retire.general_fund_acc := l_detail_balances_prior.general_fund_acc * p_retirement_factor;
502 l_detail_balances_retire.general_fund_per := l_detail_balances_prior.general_fund_per * p_retirement_factor;
503 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_prior.reval_reserve_gen_fund * p_retirement_factor;
504 l_detail_balances_retire.operating_acct_backlog := l_detail_balances_prior.operating_acct_backlog * p_retirement_factor;
505 l_detail_balances_retire.operating_acct_cost := l_detail_balances_prior.operating_acct_cost * p_retirement_factor;
506 l_detail_balances_retire.operating_acct_net := l_detail_balances_prior.operating_acct_net * p_retirement_factor;
507 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_prior.reval_reserve_backlog * p_retirement_factor ;
508 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd * p_retirement_factor;
509 END IF;
510
511
512
513 l_fa_retire_acc_deprn := l_fa_total_old_acc_deprn * p_retirement_factor;
514 l_fa_retire_deprn_prd := l_fa_deprn.deprn_period * (1 - p_retirement_factor);
515 l_fa_retire_deprn_ytd := l_fa_deprn.deprn_ytd * p_retirement_factor;
516
517 /* Do roundings */
518
519 l_asset_units_count := l_asset_units_count + l_units_per_dist.units_assigned ;
520
521 IF l_asset_units_count = g_total_asset_units THEN
522
523 l_detail_balances_retire.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_rnd_tot.adjustment_cost;
524 l_detail_balances_retire.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_rnd_tot.reval_reserve_cost;
525 l_detail_balances_retire.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_rnd_tot.deprn_reserve ;
526 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_rnd_tot.deprn_reserve_backlog ;
527 l_detail_balances_retire.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_rnd_tot.reval_reserve_net ;
528 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period + l_detail_balances_rnd_tot.deprn_period;
529 l_detail_balances_retire.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_rnd_tot.general_fund_acc;
530 l_detail_balances_retire.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_rnd_tot.general_fund_per;
531 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_rnd_tot.reval_reserve_gen_fund;
532 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances.operating_acct_backlog + l_detail_balances_rnd_tot.operating_acct_backlog ;
533 l_detail_balances_retire.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_rnd_tot.operating_acct_cost;
534 l_detail_balances_retire.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_rnd_tot.operating_acct_net ;
535 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_rnd_tot.reval_reserve_backlog;
536 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_rnd_tot.deprn_ytd;
537
538
539 l_fa_retire_acc_deprn := l_fa_retire_acc_deprn + l_fa_tot_round_acc_deprn;
540 l_fa_retire_deprn_prd := l_fa_retire_deprn_prd + l_fa_tot_round_deprn_prd;
541 l_fa_retire_deprn_ytd := l_fa_retire_deprn_ytd + l_fa_tot_round_deprn_ytd;
542
543 END IF ;
544
545 l_detail_balances_retire_unrnd.adjustment_cost :=l_detail_balances_retire.adjustment_cost ;
546 l_detail_balances_retire_unrnd.reval_reserve_cost :=l_detail_balances_retire.reval_reserve_cost ;
547 l_detail_balances_retire_unrnd.deprn_reserve := l_detail_balances_retire.deprn_reserve;
548 l_detail_balances_retire_unrnd.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog;
549 l_detail_balances_retire_unrnd.reval_reserve_net := l_detail_balances_retire.reval_reserve_net ;
550 l_detail_balances_retire_unrnd.deprn_period := l_detail_balances_retire.deprn_period;
551 l_detail_balances_retire_unrnd.general_fund_acc := l_detail_balances_retire.general_fund_acc;
552 l_detail_balances_retire_unrnd.general_fund_per := l_detail_balances_retire.general_fund_per;
553 l_detail_balances_retire_unrnd.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund;
554 l_detail_balances_retire_unrnd.operating_acct_backlog := l_detail_balances_retire.operating_acct_backlog;
555 l_detail_balances_retire_unrnd.operating_acct_cost := l_detail_balances_retire.operating_acct_cost;
556 l_detail_balances_retire_unrnd.operating_acct_net := l_detail_balances_retire.operating_acct_net;
557 l_detail_balances_retire_unrnd.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog;
558 l_detail_balances_retire_unrnd.deprn_ytd := l_detail_balances_retire.deprn_ytd;
559
560
561
562 l_fa_unround_acc_deprn := l_fa_retire_acc_deprn;
563 l_fa_unround_deprn_prd := l_fa_retire_deprn_prd;
564 l_fa_unround_deprn_ytd := l_fa_retire_deprn_ytd;
565
566
567 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.adjustment_cost,P_Book_Type_Code) ;
568 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_cost,P_Book_Type_Code) ;
569 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_reserve,P_Book_Type_Code) ;
570 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_reserve_backlog,P_Book_Type_Code) ;
571 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_net,P_Book_Type_Code) ;
572 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_period,P_Book_Type_Code) ;
573 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.general_fund_acc,P_Book_Type_Code) ;
574 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.general_fund_per,P_Book_Type_Code) ;
575 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_gen_fund,P_Book_Type_Code) ;
576 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_backlog,P_Book_Type_Code) ;
577 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_cost,P_Book_Type_Code) ;
578 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_net,P_Book_Type_Code) ;
579 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_backlog,P_Book_Type_Code) ;
580 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_ytd,P_Book_Type_Code) ;
581
582 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_acc_deprn,P_Book_Type_Code) ;
583 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_deprn_prd,P_Book_Type_Code) ;
584 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_deprn_ytd,P_Book_Type_Code) ;
585
586 l_detail_balances_rnd_tot.adjustment_cost:= l_detail_balances_rnd_tot.adjustment_cost;
587
588 l_detail_balances_rnd_tot.adjustment_cost := l_detail_balances_rnd_tot.adjustment_cost +
589 (l_detail_balances_retire_unrnd.adjustment_cost - l_detail_balances_retire.adjustment_cost);
590 l_detail_balances_rnd_tot.reval_reserve_cost := l_detail_balances_rnd_tot.reval_reserve_cost +
591 (l_detail_balances_retire_unrnd.reval_reserve_cost - l_detail_balances_retire.reval_reserve_cost);
592 l_detail_balances_rnd_tot.deprn_reserve := l_detail_balances_rnd_tot.deprn_reserve +
593 (l_detail_balances_retire_unrnd.deprn_reserve - l_detail_balances_retire.deprn_reserve) ;
594 l_detail_balances_rnd_tot.deprn_reserve_backlog := l_detail_balances_rnd_tot.deprn_reserve_backlog +
595 (l_detail_balances_retire_unrnd.deprn_reserve_backlog - l_detail_balances_retire.deprn_reserve_backlog) ;
596 l_detail_balances_rnd_tot.reval_reserve_net := l_detail_balances_rnd_tot.reval_reserve_net +
597 (l_detail_balances_retire_unrnd.reval_reserve_net - l_detail_balances_retire.reval_reserve_net);
598 l_detail_balances_rnd_tot.deprn_period := l_detail_balances_rnd_tot.deprn_period +
599 (l_detail_balances_retire_unrnd.deprn_period - l_detail_balances_retire.deprn_period);
600 l_detail_balances_rnd_tot.general_fund_acc := l_detail_balances_rnd_tot.general_fund_acc +
601 (l_detail_balances_retire_unrnd.general_fund_acc - l_detail_balances_retire.general_fund_acc);
602 l_detail_balances_rnd_tot.general_fund_per := l_detail_balances_rnd_tot.general_fund_per +
603 (l_detail_balances_retire_unrnd.general_fund_per- l_detail_balances_retire.general_fund_per);
604 l_detail_balances_rnd_tot.reval_reserve_gen_fund := l_detail_balances_rnd_tot.reval_reserve_gen_fund +
605 (l_detail_balances_retire_unrnd.reval_reserve_gen_fund - l_detail_balances_retire.reval_reserve_gen_fund);
606 l_detail_balances_rnd_tot.operating_acct_backlog :=l_detail_balances_rnd_tot.operating_acct_backlog +
607 (l_detail_balances_retire_unrnd.operating_acct_backlog - l_detail_balances_retire.operating_acct_backlog) ;
608 l_detail_balances_rnd_tot.operating_acct_cost :=l_detail_balances_rnd_tot.operating_acct_cost +
609 (l_detail_balances_retire_unrnd.operating_acct_cost - l_detail_balances_retire.operating_acct_cost);
610 l_detail_balances_rnd_tot.operating_acct_net :=l_detail_balances_rnd_tot.operating_acct_net+
611 (l_detail_balances_retire_unrnd.operating_acct_net - l_detail_balances_retire.operating_acct_net) ;
612 l_detail_balances_rnd_tot.reval_reserve_backlog := l_detail_balances_rnd_tot.reval_reserve_backlog +
613 (l_detail_balances_retire_unrnd.reval_reserve_backlog - l_detail_balances_retire.reval_reserve_backlog);
614 l_detail_balances_rnd_tot.deprn_ytd := l_detail_balances_rnd_tot.deprn_ytd +
615 (l_detail_balances_retire_unrnd.deprn_ytd - l_detail_balances_retire.deprn_ytd);
616
617
618 l_fa_tot_round_acc_deprn := l_fa_tot_round_acc_deprn + l_fa_unround_acc_deprn - l_fa_retire_acc_deprn ;
619 l_fa_tot_round_deprn_prd := l_fa_tot_round_deprn_prd + l_fa_unround_deprn_prd - l_fa_retire_deprn_prd ;
620 l_fa_tot_round_deprn_ytd := l_fa_tot_round_deprn_ytd + l_fa_unround_deprn_ytd - l_fa_retire_deprn_ytd ;
621
622
623 debug( g_state_level,l_path_name,'done roundings');
624
625
626 debug( g_state_level,l_path_name,'adjustment_cost retire ' || l_detail_balances_retire.adjustment_cost);
627 debug( g_state_level,l_path_name,'reval_reserve_cost retire ' || l_detail_balances_retire.reval_reserve_cost);
628 debug( g_state_level,l_path_name,'deprn_reserve retire ' ||l_detail_balances_retire.deprn_reserve);
629 debug( g_state_level,l_path_name,'deprn_reserve_backlog retire' ||l_detail_balances_retire.deprn_reserve_backlog);
630 debug( g_state_level,l_path_name,'reval_reserve_nett retire ' ||l_detail_balances_retire.reval_reserve_net);
631 debug( g_state_level,l_path_name,'operating_acct_backlog retire' ||l_detail_balances_retire.operating_acct_backlog);
632 debug( g_state_level,l_path_name,'operating_acct_net retire ' ||l_detail_balances_retire.operating_acct_net);
633 debug( g_state_level,l_path_name,'reval_reserve_backlog retire ' ||l_detail_balances_retire.reval_reserve_backlog);
634
635 /* Calculate new totals */
636 l_detail_balances_new.adjustment_cost := l_detail_balances.adjustment_cost - l_detail_balances_retire.adjustment_cost;
637 l_detail_balances_new.reval_reserve_cost := l_detail_balances.reval_reserve_cost - l_detail_balances_retire.reval_reserve_cost;
638 l_detail_balances_new.deprn_reserve := l_detail_balances.deprn_reserve - l_detail_balances_retire.deprn_reserve ;
639 l_detail_balances_new.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog - l_detail_balances_retire.deprn_reserve_backlog ;
640 l_detail_balances_new.reval_reserve_net := l_detail_balances.reval_reserve_net - l_detail_balances_retire.reval_reserve_net ;
641 l_detail_balances_new.deprn_period := l_detail_balances.deprn_period - l_detail_balances_retire.deprn_period;
642 l_detail_balances_new.general_fund_acc := l_detail_balances.general_fund_acc - l_detail_balances_retire.general_fund_acc;
643 l_detail_balances_new.general_fund_per := l_detail_balances.general_fund_per - l_detail_balances_retire.general_fund_per;
644 l_detail_balances_new.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund - l_detail_balances_retire.reval_reserve_gen_fund;
645 l_detail_balances_new.operating_acct_backlog :=l_detail_balances.operating_acct_backlog - l_detail_balances_retire.operating_acct_backlog;
646 l_detail_balances_new.operating_acct_cost :=l_detail_balances.operating_acct_cost - l_detail_balances_retire.operating_acct_cost;
647 l_detail_balances_new.operating_acct_net :=l_detail_balances.operating_acct_net - l_detail_balances_retire.operating_acct_net ;
648 l_detail_balances_new.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog - l_detail_balances_retire.reval_reserve_backlog;
649 l_detail_balances_new.deprn_ytd := l_detail_balances.deprn_ytd - l_detail_balances_retire.deprn_ytd;
650
651 l_detail_balances_new.net_book_value := l_detail_balances_new.adjustment_cost - l_detail_balances_new.deprn_reserve -l_detail_balances_new.deprn_reserve_backlog ;
652
653 debug( g_state_level,l_path_name,'adjustment_cost new ' || l_detail_balances_new.adjustment_cost);
654 debug( g_state_level,l_path_name,'reval_reserve_cost new ' || l_detail_balances_new.reval_reserve_cost);
655 debug( g_state_level,l_path_name,'deprn_reserve new ' ||l_detail_balances_new.deprn_reserve);
656 debug( g_state_level,l_path_name,'deprn_reserve_backlog new' ||l_detail_balances_new.deprn_reserve_backlog);
657 debug( g_state_level,l_path_name,'reval_reserve_nett new ' ||l_detail_balances_new.reval_reserve_net);
658 debug( g_state_level,l_path_name,'operating_acct_backlog new' ||l_detail_balances_new.operating_acct_backlog);
659 debug( g_state_level,l_path_name,'operating_acct_net new ' ||l_detail_balances_new.operating_acct_net);
660 debug( g_state_level,l_path_name,'reval_reserve_backlog new ' ||l_detail_balances_new.reval_reserve_backlog);
661
662
663 l_fa_total_new_acc_deprn := l_fa_total_old_acc_deprn - l_fa_retire_acc_deprn ;
664 l_fa_total_new_deprn_prd := l_fa_deprn.deprn_period - l_fa_retire_deprn_prd ;
665 l_fa_total_new_deprn_ytd := l_fa_deprn.deprn_ytd - l_fa_retire_deprn_ytd ;
666
667 IF P_retirement_type='FULL' THEN
668 l_detail_balances_new.deprn_period :=0;
669 l_detail_balances_new.deprn_ytd :=0;
670 l_fa_total_new_deprn_prd :=0;
671 l_fa_total_new_deprn_ytd :=0;
672 END IF;
673
674 --asset total;
675 l_asset_balances.net_book_value :=l_asset_balances.net_book_value+l_detail_balances_new.net_book_value;
676 l_asset_balances.adjusted_cost :=l_asset_balances.adjusted_cost +l_detail_balances_new.adjustment_cost;
677 l_asset_balances.operating_acct :=l_asset_balances.operating_acct+ l_detail_balances_new.operating_acct_net;
678 l_asset_balances.reval_reserve :=l_asset_balances.reval_reserve +l_detail_balances_new.reval_reserve_net;
679 l_asset_balances.deprn_amount :=l_asset_balances.deprn_amount +l_detail_balances_new.deprn_period;
680 l_asset_balances.deprn_reserve :=l_asset_balances.deprn_reserve +l_detail_balances_new.deprn_reserve;
681 l_asset_balances.backlog_deprn_reserve:=l_asset_balances.backlog_deprn_reserve+l_detail_balances_new.deprn_reserve_backlog;
682 l_asset_balances.general_fund :=l_asset_balances.general_fund +l_detail_balances_new.general_fund_acc;
683
684
685
686
687 /* Create adjustment to reverse out NOCOPY old balances */
688 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
689 'ASSET_COST_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
690 'RETIREMENT',l_cost_account_ccid
691 )) THEN
692 RAISE e_no_account_ccid ;
693 END IF ;
694
695 debug( g_state_level,l_path_name,'done cost get acct ccid');
696 l_rowid := NULL ;
697 debug( g_state_level, l_path_name,'done cost adjustment');
698 debug( g_state_level, l_path_name,'dist id: '||l_detail_balances.distribution_id);
699 l_acc_deprn_account_ccid := NULL;
700 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
701 'DEPRN_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
702 'RETIREMENT',l_acc_deprn_account_ccid
703 )) THEN
704 RAISE e_no_account_ccid ;
705 END IF ;
706 debug( g_state_level,l_path_name,'done deprn rsv ccid');
707 debug( g_state_level,l_path_name, '*' ||l_acc_deprn_account_ccid || '*');
708
709 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
710 'REVAL_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
711 'RETIREMENT',l_reval_rsv_account_ccid
712 )) THEN
713 RAISE e_no_account_ccid ;
714 END IF ;
715 debug( g_state_level,l_path_name,'done reval rsv ccid');
716 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
717 'BACKLOG_DEPRN_RSV_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
718 'RETIREMENT',l_backlog_account_ccid
719 )) THEN
720 RAISE e_no_account_ccid ;
721 END IF ;
722 debug( g_state_level,l_path_name,'done backlog ccid');
723
724 /* Create new adjustments for retirement part */
725
726 IF ((l_detail_balances_retire.adjustment_cost-l_detail_balances_retire.deprn_reserve)<> 0) OR
727 (l_detail_balances_retire.deprn_reserve_backlog <> 0) THEN
728
729 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
730 'NBV_RETIRED_GAIN_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
731 'RETIREMENT',l_nbv_retired_account_ccid
732 )) THEN
733 RAISE e_no_account_ccid ;
734 END IF ;
735
736 debug( g_state_level,l_path_name,'done nbv ret ccid');
737
738 l_rowid := NULL ;
739
740 igi_iac_adjustments_pkg.insert_row(
741 X_rowid => l_rowid ,
742 X_adjustment_id => l_retirement_adjustment_id ,
743 X_book_type_code => P_Book_Type_Code ,
744 X_code_combination_id => l_nbv_retired_account_ccid,
745 X_set_of_books_id => g_sob_id ,
746 X_dr_cr_flag => 'DR' ,
747 X_amount => l_detail_balances_retire.adjustment_cost-l_detail_balances_retire.deprn_reserve,
748 X_adjustment_type => 'NBV RETIRED',
749 X_transfer_to_gl_flag => 'Y' ,
750 X_units_assigned => l_units_per_dist.units_assigned ,
751 X_asset_id => P_Asset_Id ,
752 X_distribution_id => l_detail_balances.distribution_id ,
753 X_period_counter => g_prd_rec.period_counter,
754 X_adjustment_offset_type => NULL,
755 X_report_ccid => NULL,
756 X_mode => 'R',
757 X_event_id => P_Event_Id ) ;
758
759 debug( g_state_level,l_path_name,'NBV RETIRED...');
760
761 l_rowid := NULL ;
762
763 igi_iac_adjustments_pkg.insert_row(
764 X_rowid => l_rowid ,
765 X_adjustment_id => l_retirement_adjustment_id ,
766 X_book_type_code => P_Book_Type_Code ,
767 X_code_combination_id => l_nbv_retired_account_ccid,
768 X_set_of_books_id => g_sob_id ,
769 X_dr_cr_flag => 'CR' ,
770 X_amount => l_detail_balances_retire.deprn_reserve_backlog,
771 X_adjustment_type => 'NBV RETIRED',
772 X_transfer_to_gl_flag => 'Y' ,
773 X_units_assigned => l_units_per_dist.units_assigned ,
774 X_asset_id => P_Asset_Id ,
775 X_distribution_id => l_detail_balances.distribution_id ,
776 X_period_counter => g_prd_rec.period_counter,
777 X_adjustment_offset_type => NULL,
778 X_report_ccid => NULL,
779 X_mode => 'R',
780 X_event_id => P_Event_Id ) ;
781
782 debug( g_state_level,l_path_name,'NBV RETIRED...'|| l_detail_balances_retire.deprn_reserve_backlog);
783
784
785 debug( g_state_level,l_path_name, 'done 2nd nbv ret insert');
786 END IF;
787
788 IF l_detail_balances_retire.reval_reserve_net <> 0 THEN
789
790 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
791 'REVAL_RESERVE_RETIRED_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
792 'RETIREMENT',l_reval_rsv_ret_acct_ccid
793 )) THEN
794 RAISE e_no_account_ccid ;
795 END IF ;
796 debug( g_state_level,l_path_name,'done reval rsv ret ccid');
797 l_rowid := NULL ;
798 igi_iac_adjustments_pkg.insert_row(
799 X_rowid => l_rowid ,
800 X_adjustment_id => l_retirement_adjustment_id ,
801 X_book_type_code => P_Book_Type_Code ,
802 X_code_combination_id => l_reval_rsv_ret_acct_ccid,
803 X_set_of_books_id => g_sob_id ,
804 X_dr_cr_flag => 'CR' ,
805 X_amount => l_detail_balances_retire.reval_reserve_net,
806 X_adjustment_type => 'REVAL RSV RET',
807 X_transfer_to_gl_flag => 'Y' ,
808 X_units_assigned => l_units_per_dist.units_assigned ,
809 X_asset_id => P_Asset_Id ,
810 X_distribution_id => l_detail_balances.distribution_id ,
811 X_period_counter => g_prd_rec.period_counter,
812 X_adjustment_offset_type => NULL,
813 X_report_ccid => NULL,
814 X_mode => 'R',
815 X_event_id => P_Event_Id ) ;
816
817 debug( g_state_level,l_path_name,'REVAL RSV RETD...'|| l_detail_balances_retire.reval_reserve_net);
818 END IF;
819
820 /* Create adjustment for new balances */
821 debug( g_state_level,l_path_name,'start new balances');
822 l_rowid := NULL ;
823 igi_iac_adjustments_pkg.insert_row(
824 X_rowid => l_rowid ,
825 X_adjustment_id => l_retirement_adjustment_id ,
826 X_book_type_code => P_Book_Type_Code ,
827 X_code_combination_id => l_cost_account_ccid,
828 X_set_of_books_id => g_sob_id ,
829 X_dr_cr_flag => 'CR' ,
830 X_amount => l_detail_balances_retire.adjustment_cost,
831 X_adjustment_type => 'COST',
832 X_transfer_to_gl_flag => 'Y' ,
833 X_units_assigned => l_units_per_dist.units_assigned ,
834 X_asset_id => P_Asset_Id ,
835 X_distribution_id => l_detail_balances.distribution_id ,
836 X_period_counter => g_prd_rec.period_counter,
837 X_adjustment_offset_type => NULL,
838 X_report_ccid => NULL,
839 X_mode => 'R',
840 X_event_id => P_Event_Id ) ;
841
842
843
844 debug( g_state_level,l_path_name,'COST...'|| l_detail_balances_retire.adjustment_cost);
845 IF l_detail_balances_retire.deprn_reserve <> 0 THEN
846
847 l_rowid := NULL ;
848 igi_iac_adjustments_pkg.insert_row(
849 X_rowid => l_rowid ,
850 X_adjustment_id => l_retirement_adjustment_id ,
851 X_book_type_code => P_Book_Type_Code ,
852 X_code_combination_id => l_acc_deprn_account_ccid,
853 X_set_of_books_id => g_sob_id ,
854 X_dr_cr_flag => 'DR' ,
855 X_amount => l_detail_balances_retire.deprn_reserve,
856 X_adjustment_type => 'RESERVE',
857 X_transfer_to_gl_flag => 'Y' ,
858 X_units_assigned => l_units_per_dist.units_assigned ,
859 X_asset_id => P_Asset_Id ,
860 X_distribution_id => l_detail_balances.distribution_id ,
861 X_period_counter => g_prd_rec.period_counter,
862 X_adjustment_offset_type => NULL,
863 X_report_ccid => NULL,
864 X_mode => 'R',
865 X_event_id => P_Event_Id ) ;
866
867
868 END IF;
869
870 IF l_detail_balances_retire.operating_acct_backlog <> 0 THEN
871
872 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
873 'OPERATING_EXPENSE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
874 'RETIREMENT',l_op_exp_ccid
875 )) THEN
876 RAISE e_no_account_ccid ;
877 END IF ;
878
879 l_rowid := NULL ;
880 igi_iac_adjustments_pkg.insert_row(
881 X_rowid => l_rowid ,
882 X_adjustment_id => l_retirement_adjustment_id ,
883 X_book_type_code => P_Book_Type_Code ,
884 X_code_combination_id => l_backlog_account_ccid,
885 X_set_of_books_id => g_sob_id ,
886 X_dr_cr_flag => 'DR' ,
887 X_amount => l_detail_balances_retire.operating_acct_backlog,
888 X_adjustment_type => 'BL RESERVE',
889 X_transfer_to_gl_flag => 'Y' ,
890 X_units_assigned => l_units_per_dist.units_assigned ,
891 X_asset_id => P_Asset_Id ,
892 X_distribution_id => l_detail_balances.distribution_id ,
893 X_period_counter => g_prd_rec.period_counter,
894 X_adjustment_offset_type => 'OP EXPENSE',
895 X_report_ccid => l_op_exp_ccid,
896 X_mode => 'R',
897 X_event_id => P_Event_Id ) ;
898
899
900
901 debug( g_state_level,l_path_name,' BL reserve for OP '||l_detail_balances_retire.operating_acct_backlog );
902 END IF;
903
904 IF l_detail_balances_retire.reval_reserve_backlog <> 0 THEN
905
906 l_rowid := NULL ;
907 igi_iac_adjustments_pkg.insert_row(
908 X_rowid => l_rowid ,
909 X_adjustment_id => l_retirement_adjustment_id ,
910 X_book_type_code => P_Book_Type_Code ,
911 X_code_combination_id => l_backlog_account_ccid,
912 X_set_of_books_id => g_sob_id ,
913 X_dr_cr_flag => 'DR' ,
914 X_amount => l_detail_balances_retire.reval_reserve_backlog,
915 X_adjustment_type => 'BL RESERVE',
916 X_transfer_to_gl_flag => 'Y' ,
917 X_units_assigned => l_units_per_dist.units_assigned ,
918 X_asset_id => P_Asset_Id ,
919 X_distribution_id => l_detail_balances.distribution_id ,
920 X_period_counter => g_prd_rec.period_counter,
921 X_adjustment_offset_type => 'REVAL RESERVE',
922 X_report_ccid => l_reval_rsv_account_ccid,
923 X_mode => 'R',
924 X_event_id => P_Event_Id ) ;
925
926
927
928 debug( g_state_level,l_path_name,'done BL reserve for RR '||l_detail_balances_retire.reval_reserve_backlog );
929 END IF;
930
931
932
933 IF l_detail_balances_retire.reval_reserve_net <> 0 THEN
934
935 l_rowid := NULL ;
936 igi_iac_adjustments_pkg.insert_row(
937 X_rowid => l_rowid ,
938 X_adjustment_id => l_retirement_adjustment_id ,
939 X_book_type_code => P_Book_Type_Code ,
940 X_code_combination_id => l_reval_rsv_account_ccid,
941 X_set_of_books_id => g_sob_id ,
942 X_dr_cr_flag => 'DR' ,
943 X_amount => l_detail_balances_retire.reval_reserve_net,
944 X_adjustment_type => 'REVAL RESERVE',
945 X_transfer_to_gl_flag => 'Y' ,
946 X_units_assigned => l_units_per_dist.units_assigned ,
947 X_asset_id => P_Asset_Id ,
948 X_distribution_id => l_detail_balances.distribution_id ,
949 X_period_counter => g_prd_rec.period_counter,
950 X_adjustment_offset_type => NULL,
951 X_report_ccid => NULL,
952 X_mode => 'R',
953 X_event_id => P_Event_Id ) ;
954
955 debug( g_state_level,l_path_name,'done reval rsv' || l_detail_balances_retire.reval_reserve_net);
956
957 END IF;
958
959 debug( g_state_level,l_path_name,'end new balances');
960
961 /* Insert new detail balance record for this distribution */
962 IF (g_is_first_period) THEN
963 l_db_op_acct_ytd := 0;
964 l_db_deprn_ytd := 0;
965 ELSE
966 l_db_op_acct_ytd := l_detail_balances.operating_acct_ytd;
967 l_db_deprn_ytd := l_detail_balances.deprn_ytd;
968 END IF;
969
970 debug( g_state_level,l_path_name,'start insert det bal');
971 l_rowid := NULL ;
972
973 igi_iac_det_balances_pkg.insert_row(
974 X_rowid => l_rowid ,
975 X_adjustment_id => l_retirement_adjustment_id ,
976 X_asset_id => P_Asset_Id ,
977 X_distribution_id => l_detail_balances.distribution_id ,
978 X_book_type_code => P_Book_Type_Code ,
979 X_period_counter => g_prd_rec.period_counter,
980 X_adjustment_cost => l_detail_balances_new.adjustment_cost ,
981 X_net_book_value => l_detail_balances_new.net_book_value,
982 X_reval_reserve_cost => l_detail_balances_new.reval_reserve_cost,
983 X_reval_reserve_backlog => l_detail_balances_new.reval_reserve_backlog,
984 X_reval_reserve_gen_fund => l_detail_balances_new.reval_reserve_gen_fund,
985 X_reval_reserve_net => l_detail_balances_new.reval_reserve_net,
986 X_operating_acct_cost => l_detail_balances_new.operating_acct_cost,
987 X_operating_acct_backlog => l_detail_balances_new.operating_acct_backlog,
988 X_operating_acct_net => l_detail_balances_new.operating_acct_net,
989 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
990 X_deprn_period => l_detail_balances_new.deprn_period,
991 X_deprn_ytd => l_detail_balances_new.deprn_ytd,
992 X_deprn_reserve => l_detail_balances_new.deprn_reserve,
993 X_deprn_reserve_backlog => l_detail_balances_new.deprn_reserve_backlog,
994 X_general_fund_per => l_detail_balances_new.general_fund_per,
995 X_general_fund_acc => l_detail_balances_new.general_fund_acc,
996 X_last_reval_date => l_detail_balances.last_reval_date ,
997 X_current_reval_factor => l_detail_balances.current_reval_factor ,
998 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
999 X_active_flag => l_detail_balances.active_flag,
1000 X_mode => 'R') ;
1001
1002 debug( g_state_level,l_path_name,'CURRENT PERIOD');
1003 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
1004 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
1005 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
1006 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
1007 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
1008 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances_new.adjustment_cost );
1009 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances_new.net_book_value);
1010 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances_new.reval_reserve_cost);
1011 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances_new.reval_reserve_backlog);
1012 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances_new.reval_reserve_gen_fund);
1013 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances_new.reval_reserve_net);
1014 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances_new.operating_acct_cost);
1015 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances_new.operating_acct_backlog);
1016 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances_new.operating_acct_net);
1017 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
1018 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances_new.deprn_period);
1019 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances_new.deprn_ytd);
1020 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances_new.deprn_reserve);
1021 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances_new.deprn_reserve_backlog);
1022 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances_new.general_fund_per);
1023 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances_new.general_fund_acc);
1024 debug( g_state_level,l_path_name,'X_last_reval_date =>'|| l_detail_balances.last_reval_date );
1025 debug( g_state_level,l_path_name,'X_current_reval_factor =>'|| l_detail_balances.current_reval_factor );
1026 debug( g_state_level,l_path_name,'X_cumulative_reval_factor=>'|| l_detail_balances.cumulative_reval_factor );
1027 debug( g_state_level,l_path_name,'X_active_flag =>'|| l_detail_balances.active_flag);
1028
1029
1030
1031 debug( g_state_level,l_path_name,'end insert det bals');
1032
1033 IF (g_is_first_period) THEN
1034 l_fa_deprn.deprn_ytd := 0;
1035 ELSE
1036 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
1037 END IF;
1038 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
1039 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1040 x_rowid => g_rowid,
1041 x_book_type_code => p_book_type_code,
1042 x_asset_id => p_asset_id,
1043 x_period_counter => g_prd_rec.period_counter,
1044 x_adjustment_id => l_retirement_adjustment_id,
1045 x_distribution_id => l_fa_deprn.distribution_id,
1046 x_deprn_period => l_fa_total_new_deprn_prd,
1047 x_deprn_ytd => l_fa_total_new_deprn_ytd,
1048 x_deprn_reserve => l_fa_total_new_acc_deprn,
1049 x_active_flag => NULL,
1050 x_mode => 'R'
1051 );
1052
1053
1054 END IF ;
1055
1056
1057 ELSE -- Inactive distributions IF active_flag is NULL. i.e. following code for active_flag = 'N'
1058
1059 debug( g_state_level,l_path_name,'YTD record insert');
1060 /* Roll forward YTD records */
1061 IF (g_is_first_period) THEN
1062 l_fa_deprn_ytd := 0;
1063 ELSE
1064 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
1065 END IF;
1066
1067 IF p_retirement_type = 'FULL' THEN
1068 l_detail_balances.deprn_ytd := 0;
1069 l_db_deprn_ytd := 0;
1070 l_fa_deprn_ytd := 0;
1071 ELSE
1072 l_detail_balances.deprn_ytd:= l_detail_balances.deprn_ytd * p_retirement_factor;
1073 l_fa_deprn_ytd := l_fa_deprn_ytd *p_retirement_factor;
1074 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances.deprn_ytd,P_Book_Type_Code);
1075 l_ret:= igi_iac_common_utils.iac_round(l_fa_deprn_ytd,P_Book_Type_Code);
1076 END IF;
1077
1078 igi_iac_det_balances_pkg.insert_row(
1079 X_rowid => l_rowid ,
1080 X_adjustment_id => l_retirement_adjustment_id ,
1081 X_asset_id => P_Asset_Id,
1082 X_distribution_id => l_detail_balances.distribution_id ,
1083 X_book_type_code => P_Book_Type_Code ,
1084 X_period_counter => g_prd_rec.period_counter,
1085 X_adjustment_cost => l_detail_balances.adjustment_cost ,
1086 X_net_book_value => l_detail_balances.net_book_value,
1087 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
1088 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
1089 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
1090 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
1091 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
1092 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
1093 X_operating_acct_net => l_detail_balances.operating_acct_net,
1094 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
1095 X_deprn_period => l_detail_balances.deprn_period,
1096 X_deprn_ytd => l_detail_balances.deprn_ytd,
1097 X_deprn_reserve => l_detail_balances.deprn_reserve,
1098 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
1099 X_general_fund_per => l_detail_balances.general_fund_per,
1100 X_general_fund_acc => l_detail_balances.general_fund_acc,
1101 X_last_reval_date => l_detail_balances.last_reval_date ,
1102 X_current_reval_factor => l_detail_balances.current_reval_factor ,
1103 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
1104 X_active_flag => l_detail_balances.active_flag ,
1105 X_mode => 'R') ;
1106
1107
1108 g_rowid := NULL ;
1109
1110
1111
1112 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1113 x_rowid => g_rowid,
1114 x_book_type_code => p_book_type_code,
1115 x_asset_id => p_asset_id,
1116 x_period_counter => g_prd_rec.period_counter,
1117 x_adjustment_id => l_retirement_adjustment_id,
1118 x_distribution_id => l_fa_deprn.distribution_id,
1119 x_deprn_period => l_fa_deprn.deprn_period,
1120 x_deprn_ytd => l_fa_deprn_ytd,
1121 x_deprn_reserve => l_fa_deprn.deprn_reserve,
1122 x_active_flag => l_fa_deprn.active_flag,
1123 x_mode => 'R'
1124 );
1125 END IF ; -- if active_flag is Null
1126
1127 END LOOP ; -- g_detail_balances
1128 debug( g_state_level, l_path_name, 'end loop');
1129
1130 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, g_prd_rec.period_counter);
1131
1132 FETCH c_asset_balances INTO l_asset_balances_rec ;
1133
1134 IF c_asset_balances%NOTFOUND THEN
1135 CLOSE c_asset_balances ;
1136
1137 OPEN c_previous_per(l_last_active_adj_id) ;
1138 FETCH c_previous_per INTO l_previous_per ;
1139 IF c_previous_per%NOTFOUND THEN
1140 CLOSE c_previous_per;
1141 RAISE NO_DATA_FOUND ;
1142 END IF ;
1143 CLOSE c_previous_per ;
1144
1145 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, l_previous_per ) ;
1146 FETCH c_asset_balances INTO l_asset_balances_rec ;
1147 IF c_asset_balances%NOTFOUND THEN
1148 CLOSE c_asset_balances;
1149
1150 -- Begin Fix for Bug 5049536
1151 SELECT max(period_counter)
1152 INTO l_max_period_counter
1153 FROM igi_iac_asset_balances
1154 WHERE asset_id = P_Asset_Id
1155 AND book_type_code = P_Book_Type_Code;
1156
1157 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, l_max_period_counter) ;
1158 FETCH c_asset_balances INTO l_asset_balances_rec ;
1159 -- RAISE e_no_asset_bals ;
1160
1161 --End Fix for Bug 5049536
1162
1163 END IF ;
1164
1165 igi_iac_asset_balances_pkg.insert_row(
1166 X_rowid => l_rowid ,
1167 X_asset_id => p_asset_id,
1168 X_book_type_code => p_book_type_code ,
1169 X_period_counter => g_prd_rec.period_counter ,
1170 X_net_book_value => l_asset_balances.net_book_value ,
1171 X_adjusted_cost => l_asset_balances.adjusted_cost ,
1172 X_operating_acct => l_asset_balances.operating_acct ,
1173 X_reval_reserve => l_asset_balances.reval_reserve ,
1174 X_deprn_amount => l_asset_balances.deprn_amount,
1175 X_deprn_reserve => l_asset_balances.deprn_reserve ,
1176 X_backlog_deprn_reserve => l_asset_balances.backlog_deprn_reserve ,
1177 X_general_fund => l_asset_balances.general_fund ,
1178 X_last_reval_date => l_asset_balances_rec.last_reval_date ,
1179 X_current_reval_factor => l_asset_balances_rec.current_reval_factor,
1180 X_cumulative_reval_factor => l_asset_balances_rec.cumulative_reval_factor,
1181 X_mode => 'R') ;
1182
1183
1184
1185 ELSE
1186 igi_iac_asset_balances_pkg.update_row(
1187 X_asset_id => p_asset_id,
1188 X_book_type_code => p_book_type_code ,
1189 X_period_counter => g_prd_rec.period_counter ,
1190 X_net_book_value => l_asset_balances.net_book_value ,
1191 X_adjusted_cost => l_asset_balances.adjusted_cost ,
1192 X_operating_acct => l_asset_balances.operating_acct ,
1193 X_reval_reserve => l_asset_balances.reval_reserve ,
1194 X_deprn_amount => l_asset_balances.deprn_amount,
1195 X_deprn_reserve => l_asset_balances.deprn_reserve ,
1196 X_backlog_deprn_reserve => l_asset_balances.backlog_deprn_reserve ,
1197 X_general_fund => l_asset_balances.general_fund ,
1198 X_last_reval_date => l_asset_balances_rec.last_reval_date ,
1199 X_current_reval_factor => l_asset_balances_rec.current_reval_factor,
1200 X_cumulative_reval_factor => l_asset_balances_rec.cumulative_reval_factor,
1201 X_mode => 'R') ;
1202
1203 END IF;
1204 CLOSE c_asset_balances ;
1205
1206 RETURN TRUE;
1207
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 debug( g_state_level,l_path_name,'Error in Processing Cost Retirement');
1211 FA_SRVR_MSG.add_sql_error(Calling_Fn => g_calling_fn);
1212 RETURN FALSE ;
1213 END cost_retirement;
1214
1215 FUNCTION Prior_Cost_Retirement ( P_Asset_Id IN NUMBER ,
1216 P_Book_Type_Code IN VARCHAR2 ,
1217 P_Retirement_Id IN NUMBER ,
1218 P_retirement_type IN VARCHAR2,
1219 p_retirement_factor IN NUMBER ,
1220 p_retirement_period_type IN VARCHAR2,
1221 P_prior_period IN NUMBER,
1222 P_Current_period IN NUMBER,
1223 P_Event_Id IN NUMBER) --R12 uptake
1224
1225 RETURN BOOLEAN IS
1226
1227 l_rowid ROWID;
1228 l_asset_balances igi_iac_asset_balances%ROWTYPE;
1229 l_asset_balances_rec igi_iac_asset_balances%ROWTYPE;
1230 l_detail_balances igi_iac_det_balances%ROWTYPE;
1231 l_detail_balances_new igi_iac_det_balances%ROWTYPE;
1232 l_detail_balances_total_old igi_iac_det_balances%ROWTYPE;
1233 l_detail_balances_retire igi_iac_det_balances%ROWTYPE;
1234 l_detail_balances_retire_unrnd igi_iac_det_balances%ROWTYPE;
1235 l_detail_balances_rnd_tot igi_iac_det_balances%ROWTYPE;
1236 l_fa_deprn igi_iac_fa_deprn%ROWTYPE;
1237 l_units_per_dist c_units_per_dist%ROWTYPE;
1238 l_cost_account_ccid NUMBER ;
1239 l_acc_deprn_account_ccid NUMBER ;
1240 l_reval_rsv_account_ccid NUMBER ;
1241 l_backlog_account_ccid NUMBER ;
1242 l_nbv_retired_account_ccid NUMBER ;
1243 l_reval_rsv_ret_acct_ccid NUMBER ;
1244 l_deprn_exp_account_ccid NUMBER ;
1245 l_account_gen_fund_ccid NUMBER;
1246 l_new_units NUMBER ;
1247 l_new_distribution NUMBER ;
1248 l_units_before NUMBER ;
1249 l_units_after NUMBER ;
1250 l_ret BOOLEAN ;
1251 l_total_asset_units NUMBER ;
1252 l_asset_units_count NUMBER ;
1253 l_previous_per NUMBER ;
1254 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
1255 l_last_active_adj_id igi_iac_transaction_headers.adjustment_id%TYPE ;
1256 l_db_op_acct_ytd igi_iac_det_balances.operating_acct_ytd%TYPE;
1257 l_db_deprn_ytd igi_iac_det_balances.deprn_ytd%TYPE;
1258 l_fa_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1259 l_fa_tot_round_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1260 l_fa_unround_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1261 l_fa_total_old_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1262 l_fa_retire_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1263 l_fa_tot_round_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1264 l_fa_unround_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1265 l_fa_total_new_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1266 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1267 l_fa_tot_round_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1268 l_fa_unround_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1269 l_fa_total_new_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1270 l_fa_total_new_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1271 l_fa_total_old_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1272 l_fa_total_old_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1273 l_fa_retire_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1274 l_fa_retire_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1275 l_op_exp_ccid NUMBER;
1276 l_Transaction_Type_Code igi_iac_transaction_headers.transaction_type_code%TYPE;
1277 l_Transaction_Id igi_iac_transaction_headers.transaction_header_id%TYPE;
1278 l_Mass_Reference_ID igi_iac_transaction_headers.mass_reference_id%TYPE;
1279 l_Adjustment_Status igi_iac_transaction_headers.adjustment_status%TYPE;
1280 l_adjustment_id_out igi_iac_adjustments.adjustment_id%TYPE;
1281 l_retirement_adjustment_id NUmber;
1282 l_path_name varchar2(200);
1283 l_check_revaluations c_check_revaluations%ROWTYPE;
1284 l_all_occ_reval c_get_all_occ_reval%ROWTYPE;
1285 l_retire_amount Number;
1286 l_check_depreciations c_check_depreciations%ROWTYPE;
1287 l_all_prd_reval c_get_all_prd_reval%ROWTYPE;
1288 l_op_exp_ccid NUMBER;
1289
1290 BEGIN
1291
1292 l_path_name:=g_path_name ||'.Prior_Period_Cost_Retirement';
1293
1294
1295 /* Initialize Asset total balance variables */
1296 ------------------------
1297 l_asset_balances.Asset_id :=P_Asset_Id ;
1298 l_asset_balances.book_type_code :=P_Book_Type_Code;
1299 l_asset_balances.period_counter :=P_Current_period;
1300 l_asset_balances.net_book_value :=0;
1301 l_asset_balances.adjusted_cost :=0;
1302 l_asset_balances.operating_acct :=0;
1303 l_asset_balances.reval_reserve :=0;
1304 l_asset_balances.deprn_amount :=0;
1305 l_asset_balances.deprn_reserve :=0;
1306 l_asset_balances.backlog_deprn_reserve:=0;
1307 l_asset_balances.general_fund :=0;
1308
1309
1310 l_Transaction_Type_Code := NULL;
1311 l_Transaction_Id := NULL;
1312 l_Mass_Reference_ID := NULL;
1313 l_adjustment_id_out := NULL;
1314 l_prev_adjustment_id := NULL;
1315 l_Adjustment_Status := NULL;
1316 l_retirement_adjustment_id :=NULL;
1317
1318 debug(g_state_level,l_path_name,'Asset ID '||P_Asset_Id);
1319 -- get the latest tranaction for the asset id
1320
1321 IF NOT (igi_iac_common_utils.get_latest_transaction(P_Book_Type_Code,
1322 P_Asset_Id,
1323 l_Transaction_Type_Code,
1324 l_Transaction_Id,
1325 l_Mass_Reference_ID ,
1326 l_adjustment_id_out,
1327 l_prev_adjustment_id,
1328 l_Adjustment_Status )
1329 ) THEN
1330 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path_name,'*** Error in fetching the latest transaction');
1331 RETURN FALSE;
1332 END IF;
1333
1334 debug(g_state_level,l_path_name,'got latest transaction');
1335 l_last_active_adj_id := l_prev_adjustment_id ;
1336 debug( g_state_level,l_path_name,'prior_period'|| p_prior_period);
1337 debug( g_state_level,l_path_name,'current_period'||p_current_period);
1338
1339 ---check for revauations if exits between the prior perod and current period
1340 -- Start revaluation
1341 OPEN c_check_revaluations(P_asset_id,p_book_type_code,
1342 p_prior_period,p_current_period);
1343 FETCH c_check_revaluations INTO l_check_revaluations;
1344 IF c_check_revaluations%FOUND THEN -- revlautions found in betweem
1345 l_path_name :=l_path_name ||'.Reval' ;
1346
1347 --- create a new transaction header for Revlaution retirement
1348 l_rowid:=NULL;
1349 l_retirement_adjustment_id :=NULL;
1350 igi_iac_trans_headers_pkg.insert_row(
1351 X_rowid => l_rowid ,
1352 X_adjustment_id => l_retirement_adjustment_id ,
1353 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
1354 X_adjustment_id_out => NULL ,
1355 X_transaction_type_code => 'REVALUATION',
1356 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
1357 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
1358 X_transaction_sub_type => 'RETIREMENT',
1359 X_book_type_code => P_Book_Type_Code,
1360 X_asset_id => p_asset_id ,
1361 X_category_id => g_asset_category_id,
1362 X_adj_deprn_start_date => NULL,
1363 X_revaluation_type_flag => NULL,
1364 X_adjustment_status => 'COMPLETE' ,
1365 X_period_counter => P_Current_period,
1366 X_mode =>'R',
1367 X_event_id => P_Event_Id) ;
1368
1369 debug( g_state_level, l_path_name,'inserted trans_headers record');
1370 debug( g_state_level, l_path_name, 'new transaction Revalaution...'||l_retirement_adjustment_id );
1371 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
1372 l_retirement_adjustment_id,
1373 'R') ;
1374
1375 debug( g_state_level,l_path_name,'updated old trans_headers record');
1376 debug( g_state_level,l_path_name,'Start of Revaluations');
1377 --get all adjustments for occasional revaluation between current period and
1378 -- retire period
1379
1380 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
1381
1382 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
1383 l_detail_balances.distribution_id,
1384 l_detail_balances.period_counter);
1385 FETCH c_fa_deprn INTO l_fa_deprn;
1386 IF c_fa_deprn%NOTFOUND THEN
1387 CLOSE c_fa_deprn;
1388 RETURN FALSE;
1389 END IF;
1390 CLOSE c_fa_deprn;
1391
1392 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
1393 debug( g_state_level,l_path_name,'inside loop');
1394
1395 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
1396
1397 /* Create adjustment to reverse out retirement factor */
1398
1399 l_detail_balances_retire.adjustment_cost :=0 ;
1400 l_detail_balances_retire.reval_reserve_cost :=0 ;
1401 l_detail_balances_retire.deprn_reserve :=0 ;
1402 l_detail_balances_retire.deprn_reserve_backlog :=0 ;
1403 l_detail_balances_retire.reval_reserve_net :=0 ;
1404 l_detail_balances_retire.deprn_period :=0 ;
1405 l_detail_balances_retire.general_fund_acc :=0 ;
1406 l_detail_balances_retire.general_fund_per :=0 ;
1407 l_detail_balances_retire.reval_reserve_gen_fund :=0 ;
1408 l_detail_balances_retire.operating_acct_backlog :=0;
1409 l_detail_balances_retire.operating_acct_cost :=0;
1410 l_detail_balances_retire.operating_acct_net :=0;
1411 l_detail_balances_retire.reval_reserve_backlog :=0;
1412 l_detail_balances_retire.deprn_ytd :=0;
1413
1414 FOR l_all_occ_reval IN c_get_all_occ_reval(p_asset_id,p_book_type_code,
1415 P_prior_period,
1416 p_current_period,
1417 l_detail_balances.distribution_id ) LOOP
1418
1419 l_retire_amount := l_all_occ_reval.amount * P_retirement_factor;
1420 l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code) ;
1421 l_rowid := NULL ;
1422 igi_iac_adjustments_pkg.insert_row(
1423 X_rowid => l_rowid ,
1424 X_adjustment_id => l_retirement_adjustment_id ,
1425 X_book_type_code => P_Book_Type_Code ,
1426 X_code_combination_id => l_all_occ_reval.code_combination_id,
1427 X_set_of_books_id => g_sob_id ,
1428 X_dr_cr_flag => 'DR',
1429 X_amount => l_retire_amount,
1430 X_adjustment_type => l_all_occ_reval.adjustment_type,
1431 X_transfer_to_gl_flag => 'Y',
1432 X_units_assigned => l_all_occ_reval.units_assigned ,
1433 X_asset_id => p_Asset_Id ,
1434 X_distribution_id => l_detail_balances.distribution_id ,
1435 X_period_counter => g_prd_rec.period_counter,
1436 X_adjustment_offset_type =>l_all_occ_reval.adjustment_offset_type,
1437 X_report_ccid => l_all_occ_reval.report_ccid,
1438 X_mode => 'R',
1439 X_event_id => P_Event_Id) ;
1440
1441 debug( g_state_level,l_path_name,'adjustment type '|| l_all_occ_reval.adjustment_type);
1442 debug( g_state_level,l_path_name,'amount ' || l_all_occ_reval.amount * P_retirement_factor);
1443 debug( g_state_level,l_path_name,'distribution ' || l_detail_balances.distribution_id);
1444
1445 -- cost entries
1446 IF l_all_occ_reval.adjustment_type = 'COST' THEN
1447 l_detail_balances_retire.adjustment_cost:=l_detail_balances_retire.adjustment_cost + l_retire_amount;
1448 END IF;
1449 IF l_all_occ_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
1450 l_detail_balances_retire.reval_reserve_cost:= l_detail_balances_retire.reval_reserve_cost + l_retire_amount;
1451 END IF;
1452 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
1453 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog - l_retire_amount;
1454 END IF;
1455 IF l_all_occ_reval.adjustment_type = 'GENERAL FUND' THEN
1456 l_detail_balances_retire.general_fund_acc := l_detail_balances_retire.general_fund_acc - l_retire_amount;
1457 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund - l_retire_amount;
1458 END IF;
1459 IF l_all_occ_reval.adjustment_type = 'REVAL RESERVE' THEN
1460 l_detail_balances_retire.reval_reserve_net := l_detail_balances_retire.reval_reserve_net - l_retire_amount;
1461 END IF;
1462 IF l_all_occ_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
1463 l_detail_balances_retire.operating_acct_cost:=l_detail_balances_retire.operating_acct_cost + l_retire_amount;
1464 END IF;
1465 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
1466 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances_retire.operating_acct_backlog - l_retire_amount;
1467 END IF;
1468 IF l_all_occ_reval.adjustment_type = 'OP EXPENSE' THEN
1469 l_detail_balances_retire.operating_acct_net :=l_detail_balances_retire.operating_acct_net - l_retire_amount;
1470 END IF;
1471 IF l_all_occ_reval.adjustment_type = 'RESERVE' THEN
1472 l_detail_balances_retire.deprn_reserve := l_detail_balances_retire.deprn_reserve - l_retire_amount;
1473 END IF;
1474 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' THEN
1475 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog - l_retire_amount;
1476 END IF;
1477 END LOOP; -- adjustment reversal
1478
1479 /* Calculate new totals */
1480 l_detail_balances.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_retire.adjustment_cost;
1481 l_detail_balances.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_retire.reval_reserve_cost;
1482 l_detail_balances.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_retire.deprn_reserve ;
1483 l_detail_balances.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_retire.deprn_reserve_backlog ;
1484 l_detail_balances.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_retire.reval_reserve_net ;
1485 l_detail_balances.deprn_period := l_detail_balances.deprn_period + l_detail_balances_retire.deprn_period;
1486 l_detail_balances.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_retire.general_fund_acc;
1487 l_detail_balances.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_retire.general_fund_per;
1488 l_detail_balances.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_retire.reval_reserve_gen_fund;
1489 l_detail_balances.operating_acct_backlog := l_detail_balances.operating_acct_backlog + l_detail_balances_retire.operating_acct_backlog;
1490 l_detail_balances.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_retire.operating_acct_cost;
1491 l_detail_balances.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_retire.operating_acct_net ;
1492 l_detail_balances.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_retire.reval_reserve_backlog;
1493 l_detail_balances.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_retire.deprn_ytd;
1494
1495 l_detail_balances.net_book_value := l_detail_balances.adjustment_cost - l_detail_balances.deprn_reserve -l_detail_balances.deprn_reserve_backlog ;
1496
1497 l_rowid := NULL ;
1498
1499
1500 igi_iac_det_balances_pkg.insert_row(
1501 X_rowid => l_rowid ,
1502 X_adjustment_id => l_retirement_adjustment_id ,
1503 X_asset_id => P_Asset_Id ,
1504 X_distribution_id => l_detail_balances.distribution_id ,
1505 X_book_type_code => P_Book_Type_Code ,
1506 X_period_counter => g_prd_rec.period_counter,
1507 X_adjustment_cost => l_detail_balances.adjustment_cost ,
1508 X_net_book_value => l_detail_balances.net_book_value,
1509 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
1510 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
1511 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
1512 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
1513 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
1514 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
1515 X_operating_acct_net => l_detail_balances.operating_acct_net,
1516 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
1517 X_deprn_period => l_detail_balances.deprn_period,
1518 X_deprn_ytd => l_detail_balances.deprn_ytd,
1519 X_deprn_reserve => l_detail_balances.deprn_reserve,
1520 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
1521 X_general_fund_per => l_detail_balances.general_fund_per,
1522 X_general_fund_acc => l_detail_balances.general_fund_acc,
1523 X_last_reval_date => l_detail_balances.last_reval_date ,
1524 X_current_reval_factor => l_detail_balances.current_reval_factor ,
1525 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
1526 X_active_flag => l_detail_balances.active_flag,
1527 X_mode => 'R') ;
1528
1529 debug( g_state_level,l_path_name,'REVALUATION REVERSAL');
1530 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
1531 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
1532 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
1533 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
1534 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
1535 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances.adjustment_cost);
1536 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances.net_book_value);
1537 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances.reval_reserve_cost);
1538 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances.reval_reserve_backlog);
1539 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances.reval_reserve_gen_fund);
1540 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances.reval_reserve_net);
1541 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances.operating_acct_cost);
1542 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances.operating_acct_backlog);
1543 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances.operating_acct_net);
1544 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
1545 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances.deprn_period);
1546 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances.deprn_ytd);
1547 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances.deprn_reserve);
1548 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances.deprn_reserve_backlog);
1549 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances.general_fund_per);
1550 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances.general_fund_acc);
1551 debug( g_state_level,l_path_name,'X_last_reval_date =>'|| l_detail_balances.last_reval_date );
1552 debug( g_state_level,l_path_name,'X_current_reval_factor =>'|| l_detail_balances.current_reval_factor );
1553 debug( g_state_level,l_path_name,'X_cumulative_reval_factor=>'|| l_detail_balances.cumulative_reval_factor );
1554 debug( g_state_level,l_path_name,'X_active_flag =>'|| l_detail_balances.active_flag);
1555
1556 debug( g_state_level,l_path_name,'end insert det bals');
1557 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
1558 l_rowid:=NULL;
1559 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1560 x_rowid => l_rowid,
1561 x_book_type_code => p_book_type_code,
1562 x_asset_id => p_asset_id,
1563 x_period_counter => g_prd_rec.period_counter,
1564 x_adjustment_id => l_retirement_adjustment_id,
1565 x_distribution_id => l_fa_deprn.distribution_id,
1566 x_deprn_period => l_fa_deprn.deprn_period,
1567 x_deprn_ytd => l_fa_deprn.deprn_ytd,
1568 x_deprn_reserve => l_fa_deprn.deprn_reserve,
1569 x_active_flag => NULL,
1570 x_mode => 'R');
1571
1572 ELSE -- Inactive distributions IF active_flag is not NULL. i.e. following code for active_flag = 'N'
1573
1574 debug( g_state_level,l_path_name,'Inactive distributions');
1575 l_rowid := NULL ;
1576 igi_iac_det_balances_pkg.insert_row(
1577 X_rowid => l_rowid ,
1578 X_adjustment_id => l_retirement_adjustment_id ,
1579 X_asset_id => P_Asset_Id,
1580 X_distribution_id => l_detail_balances.distribution_id ,
1581 X_book_type_code => P_Book_Type_Code ,
1582 X_period_counter => g_prd_rec.period_counter,
1583 X_adjustment_cost => l_detail_balances.adjustment_cost ,
1584 X_net_book_value => l_detail_balances.net_book_value,
1585 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
1586 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
1587 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
1588 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
1589 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
1590 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
1591 X_operating_acct_net => l_detail_balances.operating_acct_net,
1592 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
1593 X_deprn_period => l_detail_balances.deprn_period,
1594 X_deprn_ytd => l_detail_balances.deprn_ytd,
1595 X_deprn_reserve => l_detail_balances.deprn_reserve,
1596 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
1597 X_general_fund_per => l_detail_balances.general_fund_per,
1598 X_general_fund_acc => l_detail_balances.general_fund_acc,
1599 X_last_reval_date => l_detail_balances.last_reval_date ,
1600 X_current_reval_factor => l_detail_balances.current_reval_factor ,
1601 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
1602 X_active_flag => l_detail_balances.active_flag ,
1603 X_mode => 'R') ;
1604
1605 l_rowid := NULL ;
1606 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1607 x_rowid => g_rowid,
1608 x_book_type_code => p_book_type_code,
1609 x_asset_id => p_asset_id,
1610 x_period_counter => g_prd_rec.period_counter,
1611 x_adjustment_id => l_retirement_adjustment_id,
1612 x_distribution_id => l_fa_deprn.distribution_id,
1613 x_deprn_period => l_fa_deprn.deprn_period,
1614 x_deprn_ytd => l_fa_deprn.deprn_ytd,
1615 x_deprn_reserve => l_fa_deprn.deprn_reserve,
1616 x_active_flag => NULL,
1617 x_mode => 'R'
1618 );
1619 END IF ; -- if active_flag is not Null
1620
1621 END LOOP ; -- det balances
1622 debug( g_state_level,l_path_name,'End of Revaluations');
1623 l_adjustment_id_out :=l_retirement_adjustment_id;
1624 l_last_active_adj_id :=l_retirement_adjustment_id;
1625
1626 ELSE -- no revalautions
1627 debug( g_state_level,l_path_name,'No Revaluations');
1628 NULL;
1629 END IF; --end revaluaionts
1630 CLOSE C_check_revaluations;
1631 -- End revaluation
1632
1633 -- start deprecaition reversal
1634 debug( g_state_level,l_path_name,'Start of Depreciation');
1635 OPEN c_check_depreciations(P_asset_id,p_book_type_code,
1636 p_prior_period,p_current_period);
1637 FETCH c_check_depreciations INTO l_check_depreciations;
1638 IF c_check_depreciations%FOUND THEN -- revlautions found in betweem
1639 l_path_name :=l_path_name ||'.Deprn' ;
1640 --- create a new transaction header for Revlaution retirement
1641 l_rowid:=NULL;
1642 l_retirement_adjustment_id :=NULL;
1643 igi_iac_trans_headers_pkg.insert_row(
1644 X_rowid => l_rowid ,
1645 X_adjustment_id => l_retirement_adjustment_id ,
1646 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
1647 X_adjustment_id_out => NULL ,
1648 X_transaction_type_code => 'DEPRECIATION',
1649 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
1650 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
1651 X_transaction_sub_type => 'RETIREMENT',
1652 X_book_type_code => P_Book_Type_Code,
1653 X_asset_id => p_asset_id ,
1654 X_category_id => g_asset_category_id,
1655 X_adj_deprn_start_date => NULL,
1656 X_revaluation_type_flag => NULL,
1657 X_adjustment_status => 'COMPLETE' ,
1658 X_period_counter => P_Current_period,
1659 X_mode =>'R',
1660 X_event_id => P_Event_Id) ;
1661
1662 debug( g_state_level, l_path_name,'inserted trans_headers record');
1663 debug( g_state_level, l_path_name, 'new transaction Depreciation...'||l_retirement_adjustment_id );
1664 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
1665 l_retirement_adjustment_id,
1666 'R') ;
1667 --l_adjustment_id_out :=l_retirement_adjustment_id;
1668 ---l_last_active_adj_id :=l_retirement_adjustment_id;
1669
1670 debug( g_state_level,l_path_name,'updated old trans_headers record');
1671
1672 --get all adjustments for occasional revaluation between current period and
1673 -- retire period
1674 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
1675
1676 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
1677 l_detail_balances.distribution_id,
1678 l_detail_balances.period_counter);
1679 FETCH c_fa_deprn INTO l_fa_deprn;
1680 IF c_fa_deprn%NOTFOUND THEN
1681 CLOSE c_fa_deprn;
1682 RETURN FALSE;
1683 END IF;
1684 CLOSE c_fa_deprn;
1685
1686 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
1687 debug( g_state_level,l_path_name,'inside loop');
1688
1689 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
1690
1691 /* Create adjustment to reverse out retirement factor */
1692
1693 l_detail_balances_retire.adjustment_cost :=0 ;
1694 l_detail_balances_retire.reval_reserve_cost :=0 ;
1695 l_detail_balances_retire.deprn_reserve :=0 ;
1696 l_detail_balances_retire.deprn_reserve_backlog :=0 ;
1697 l_detail_balances_retire.reval_reserve_net :=0 ;
1698 l_detail_balances_retire.deprn_period :=0 ;
1699 l_detail_balances_retire.general_fund_acc :=0 ;
1700 l_detail_balances_retire.general_fund_per :=0 ;
1701 l_detail_balances_retire.reval_reserve_gen_fund :=0 ;
1702 l_detail_balances_retire.operating_acct_backlog :=0;
1703 l_detail_balances_retire.operating_acct_cost :=0;
1704 l_detail_balances_retire.operating_acct_net :=0;
1705 l_detail_balances_retire.reval_reserve_backlog :=0;
1706 l_detail_balances_retire.deprn_ytd :=0;
1707
1708 FOR l_all_prd_reval IN c_get_all_prd_reval(p_asset_id,p_book_type_code,
1709 P_prior_period,
1710 p_current_period,
1711 l_detail_balances.distribution_id ) LOOP
1712
1713 l_retire_amount := l_all_prd_reval.amount * P_retirement_factor;
1714 l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code) ;
1715 l_rowid := NULL ;
1716 igi_iac_adjustments_pkg.insert_row(
1717 X_rowid => l_rowid ,
1718 X_adjustment_id => l_retirement_adjustment_id ,
1719 X_book_type_code => P_Book_Type_Code ,
1720 X_code_combination_id => l_all_prd_reval.code_combination_id,
1721 X_set_of_books_id => g_sob_id ,
1722 X_dr_cr_flag => 'DR',
1723 X_amount => l_retire_amount,
1724 X_adjustment_type => l_all_prd_reval.adjustment_type,
1725 X_transfer_to_gl_flag => 'Y',
1726 X_units_assigned => l_all_prd_reval.units_assigned ,
1727 X_asset_id => p_Asset_Id ,
1728 X_distribution_id => l_detail_balances.distribution_id ,
1729 X_period_counter => g_prd_rec.period_counter,
1730 X_adjustment_offset_type =>l_all_prd_reval.adjustment_offset_type,
1731 X_report_ccid => l_all_prd_reval.report_ccid,
1732 X_mode => 'R',
1733 X_event_id => P_Event_Id ) ;
1734
1735 debug( g_state_level,l_path_name,'adjustment type '|| l_all_prd_reval.adjustment_type);
1736 debug( g_state_level,l_path_name,'amount ' || l_retire_amount );
1737 debug( g_state_level,l_path_name,'distribution ' || l_detail_balances.distribution_id);
1738
1739
1740 -- cost entries
1741 IF l_all_prd_reval.adjustment_type = 'COST' THEN
1742 l_detail_balances_retire.adjustment_cost:=l_detail_balances_retire.adjustment_cost + l_retire_amount;
1743 END IF;
1744 IF l_all_prd_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
1745 l_detail_balances_retire.reval_reserve_cost:= l_detail_balances_retire.reval_reserve_cost+ l_retire_amount;
1746 END IF;
1747 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
1748 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog - l_retire_amount;
1749 END IF;
1750 IF l_all_prd_reval.adjustment_type = 'GENERAL FUND' THEN
1751 l_detail_balances_retire.general_fund_acc := l_detail_balances_retire.general_fund_acc - l_retire_amount;
1752 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund - l_retire_amount;
1753 END IF;
1754 IF l_all_prd_reval.adjustment_type = 'REVAL RESERVE' THEN
1755 l_detail_balances_retire.reval_reserve_net := l_detail_balances_retire.reval_reserve_net - l_retire_amount;
1756 END IF;
1757 IF l_all_prd_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
1758 l_detail_balances_retire.operating_acct_cost:=l_detail_balances_retire.operating_acct_cost + l_retire_amount;
1759 END IF;
1760 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
1761 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances_retire.operating_acct_backlog - l_retire_amount;
1762 END IF;
1763 IF l_all_prd_reval.adjustment_type = 'OP EXPENSE' THEN
1764 l_detail_balances_retire.operating_acct_net :=l_detail_balances_retire.operating_acct_net - l_retire_amount;
1765 END IF;
1766 IF l_all_prd_reval.adjustment_type = 'RESERVE' THEN
1767 l_detail_balances_retire.deprn_reserve := l_detail_balances_retire.deprn_reserve - l_retire_amount;
1768 END IF;
1769 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' THEN
1770 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog - l_retire_amount;
1771 END IF;
1772 END LOOP; -- adjustment reversal
1773
1774
1775
1776 /* Calculate new totals */
1777 l_detail_balances.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_retire.adjustment_cost;
1778 l_detail_balances.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_retire.reval_reserve_cost;
1779 l_detail_balances.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_retire.deprn_reserve ;
1780 l_detail_balances.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_retire.deprn_reserve_backlog ;
1781 l_detail_balances.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_retire.reval_reserve_net ;
1782 l_detail_balances.deprn_period := l_detail_balances.deprn_period + l_detail_balances_retire.deprn_period;
1783 l_detail_balances.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_retire.general_fund_acc;
1784 l_detail_balances.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_retire.general_fund_per;
1785 l_detail_balances.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_retire.reval_reserve_gen_fund;
1786 l_detail_balances.operating_acct_backlog := l_detail_balances.operating_acct_backlog + l_detail_balances_retire.operating_acct_backlog;
1787 l_detail_balances.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_retire.operating_acct_cost;
1788 l_detail_balances.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_retire.operating_acct_net ;
1789 l_detail_balances.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_retire.reval_reserve_backlog;
1790 l_detail_balances.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_retire.deprn_ytd;
1791
1792 l_detail_balances.net_book_value := l_detail_balances.adjustment_cost - l_detail_balances.deprn_reserve -l_detail_balances.deprn_reserve_backlog ;
1793
1794 l_rowid := NULL ;
1795
1796
1797 igi_iac_det_balances_pkg.insert_row(
1798 X_rowid => l_rowid ,
1799 X_adjustment_id => l_retirement_adjustment_id ,
1800 X_asset_id => P_Asset_Id ,
1801 X_distribution_id => l_detail_balances.distribution_id ,
1802 X_book_type_code => P_Book_Type_Code ,
1803 X_period_counter => g_prd_rec.period_counter,
1804 X_adjustment_cost => l_detail_balances.adjustment_cost ,
1805 X_net_book_value => l_detail_balances.net_book_value,
1806 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
1807 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
1808 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
1809 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
1810 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
1811 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
1812 X_operating_acct_net => l_detail_balances.operating_acct_net,
1813 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
1814 X_deprn_period => l_detail_balances.deprn_period,
1815 X_deprn_ytd => l_detail_balances.deprn_ytd,
1816 X_deprn_reserve => l_detail_balances.deprn_reserve,
1817 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
1818 X_general_fund_per => l_detail_balances.general_fund_per,
1819 X_general_fund_acc => l_detail_balances.general_fund_acc,
1820 X_last_reval_date => l_detail_balances.last_reval_date ,
1821 X_current_reval_factor => l_detail_balances.current_reval_factor ,
1822 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
1823 X_active_flag => l_detail_balances.active_flag,
1824 X_mode => 'R') ;
1825
1826 debug( g_state_level,l_path_name,'DEPRECIATION REVERSAL');
1827 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
1828 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
1829 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
1830 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
1831 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
1832 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances.adjustment_cost);
1833 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances.net_book_value);
1834 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances.reval_reserve_cost);
1835 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances.reval_reserve_backlog);
1836 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances.reval_reserve_gen_fund);
1837 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances.reval_reserve_net);
1838 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances.operating_acct_cost);
1839 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances.operating_acct_backlog);
1840 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances.operating_acct_net);
1841 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
1842 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances.deprn_period);
1843 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances.deprn_ytd);
1844 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances.deprn_reserve);
1845 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances.deprn_reserve_backlog);
1846 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances.general_fund_per);
1847 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances.general_fund_acc);
1848 debug( g_state_level,l_path_name,'X_last_reval_date =>'|| l_detail_balances.last_reval_date );
1849 debug( g_state_level,l_path_name,'X_current_reval_factor =>'|| l_detail_balances.current_reval_factor );
1850 debug( g_state_level,l_path_name,'X_cumulative_reval_factor=>'|| l_detail_balances.cumulative_reval_factor );
1851 debug( g_state_level,l_path_name,'X_active_flag =>'|| l_detail_balances.active_flag);
1852
1853 debug( g_state_level,l_path_name,'end insert det bals');
1854 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
1855 l_rowid:=NULL;
1856 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1857 x_rowid => l_rowid,
1858 x_book_type_code => p_book_type_code,
1859 x_asset_id => p_asset_id,
1860 x_period_counter => g_prd_rec.period_counter,
1861 x_adjustment_id => l_retirement_adjustment_id,
1862 x_distribution_id => l_fa_deprn.distribution_id,
1863 x_deprn_period => l_fa_deprn.deprn_period,
1864 x_deprn_ytd => l_fa_deprn.deprn_ytd,
1865 x_deprn_reserve => l_fa_deprn.deprn_reserve,
1866 x_active_flag => NULL,
1867 x_mode => 'R'
1868 );
1869
1870 ELSE -- Inactive distributions IF active_flag is not NULL. i.e. following code for active_flag = 'N'
1871
1872 debug( g_state_level,l_path_name,'Inactive distributions');
1873 l_rowid := NULL ;
1874 igi_iac_det_balances_pkg.insert_row(
1875 X_rowid => l_rowid ,
1876 X_adjustment_id => l_retirement_adjustment_id ,
1877 X_asset_id => P_Asset_Id,
1878 X_distribution_id => l_detail_balances.distribution_id ,
1879 X_book_type_code => P_Book_Type_Code ,
1880 X_period_counter => g_prd_rec.period_counter,
1881 X_adjustment_cost => l_detail_balances.adjustment_cost ,
1882 X_net_book_value => l_detail_balances.net_book_value,
1883 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
1884 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
1885 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
1886 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
1887 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
1888 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
1889 X_operating_acct_net => l_detail_balances.operating_acct_net,
1890 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
1891 X_deprn_period => l_detail_balances.deprn_period,
1892 X_deprn_ytd => l_detail_balances.deprn_ytd,
1893 X_deprn_reserve => l_detail_balances.deprn_reserve,
1894 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
1895 X_general_fund_per => l_detail_balances.general_fund_per,
1896 X_general_fund_acc => l_detail_balances.general_fund_acc,
1897 X_last_reval_date => l_detail_balances.last_reval_date ,
1898 X_current_reval_factor => l_detail_balances.current_reval_factor ,
1899 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
1900 X_active_flag => l_detail_balances.active_flag ,
1901 X_mode => 'R') ;
1902
1903
1904 l_rowid := NULL ;
1905 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
1906 x_rowid => g_rowid,
1907 x_book_type_code => p_book_type_code,
1908 x_asset_id => p_asset_id,
1909 x_period_counter => g_prd_rec.period_counter,
1910 x_adjustment_id => l_retirement_adjustment_id,
1911 x_distribution_id => l_fa_deprn.distribution_id,
1912 x_deprn_period => l_fa_deprn.deprn_period,
1913 x_deprn_ytd => l_fa_deprn.deprn_ytd,
1914 x_deprn_reserve => l_fa_deprn.deprn_reserve,
1915 x_active_flag => NULL,
1916 x_mode => 'R'
1917 );
1918 END IF ; -- if active_flag is not Null
1919
1920 END LOOP ; -- det balances
1921 debug( g_state_level,l_path_name,'End of Depreciation');
1922 ELSE -- no deprecaitions
1923 debug( g_state_level,l_path_name,'No Depreciations');
1924 NULL;
1925 END IF; --end deprecaitions
1926 CLOSE C_check_depreciations;
1927 -- end reversal
1928 RETURN TRUE;
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 debug( g_state_level,l_path_name,'Error in Processing Prior Cost Retirement');
1932 FA_SRVR_MSG.add_sql_error(Calling_Fn => g_calling_fn);
1933 RETURN FALSE ;
1934
1935 END prior_cost_retirement;
1936
1937 FUNCTION Unit_Retirement ( P_Asset_Id IN NUMBER ,
1938 P_Book_Type_Code IN VARCHAR2 ,
1939 P_Retirement_Id IN NUMBER ,
1940 P_retirement_type IN VARCHAR2,
1941 p_retirement_period_type IN VARCHAR2,
1942 P_prior_period IN NUMBER,
1943 P_Current_period IN NUMBER,
1944 P_Event_Id IN NUMBER) --R12 uptake
1945 RETURN BOOLEAN IS
1946
1947
1948 l_rowid ROWID;
1949 l_asset_balances igi_iac_asset_balances%ROWTYPE;
1950 l_asset_balances_rec igi_iac_asset_balances%ROWTYPE;
1951 l_detail_balances igi_iac_det_balances%ROWTYPE;
1952 l_detail_balances_new igi_iac_det_balances%ROWTYPE;
1953 l_detail_balances_total_old igi_iac_det_balances%ROWTYPE;
1954 l_detail_balances_retire igi_iac_det_balances%ROWTYPE;
1955 l_detail_balances_retire_unrnd igi_iac_det_balances%ROWTYPE;
1956 l_detail_balances_rnd_tot igi_iac_det_balances%ROWTYPE;
1957 l_fa_deprn igi_iac_fa_deprn%ROWTYPE;
1958 l_units_per_dist c_units_per_dist%ROWTYPE;
1959 l_cost_account_ccid NUMBER ;
1960 l_acc_deprn_account_ccid NUMBER ;
1961 l_reval_rsv_account_ccid NUMBER ;
1962 l_backlog_account_ccid NUMBER ;
1963 l_nbv_retired_account_ccid NUMBER ;
1964 l_reval_rsv_ret_acct_ccid NUMBER ;
1965 l_deprn_exp_account_ccid NUMBER ;
1966 l_account_gen_fund_ccid NUMBER;
1967 l_new_units NUMBER ;
1968 l_new_distribution NUMBER ;
1969 l_units_before NUMBER ;
1970 l_units_after NUMBER ;
1971 l_ret BOOLEAN ;
1972 l_total_asset_units NUMBER ;
1973 l_asset_units_count NUMBER ;
1974 l_previous_per NUMBER ;
1975 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
1976 l_last_active_adj_id igi_iac_transaction_headers.adjustment_id%TYPE ;
1977 l_db_op_acct_ytd igi_iac_det_balances.operating_acct_ytd%TYPE;
1978 l_db_deprn_ytd igi_iac_det_balances.deprn_ytd%TYPE;
1979 l_fa_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1980 l_fa_tot_round_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1981 l_fa_unround_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
1982 l_fa_total_old_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1983 l_fa_retire_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1984 l_fa_tot_round_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1985 l_fa_unround_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1986 l_fa_total_new_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
1987 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1988 l_fa_tot_round_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1989 l_fa_unround_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1990 l_fa_total_new_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1991 l_fa_total_new_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1992 l_fa_total_old_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1993 l_fa_total_old_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1994 l_fa_retire_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
1995 l_fa_retire_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1996 l_impact_fa_dist c_get_impacted_dist%ROWTYPE;
1997 l_new_fa_dist c_get_new_dist%ROWTYPE;
1998 l_retirement_factor Number;
1999 l_op_exp_ccid Number;
2000 l_Transaction_Type_Code igi_iac_transaction_headers.transaction_type_code%TYPE;
2001 l_Transaction_Id igi_iac_transaction_headers.transaction_header_id%TYPE;
2002 l_Mass_Reference_ID igi_iac_transaction_headers.mass_reference_id%TYPE;
2003 l_Adjustment_Status igi_iac_transaction_headers.adjustment_status%TYPE;
2004 l_adjustment_id_out igi_iac_adjustments.adjustment_id%TYPE;
2005 l_retirement_adjustment_id Number;
2006 l_path_name varchar2(200);
2007 l_detail_balances_prior C_get_prior_dist%rowtype;
2008 l_detail_balances_latest C_get_prior_dist%rowtype;
2009 l_max_period_counter NUMBER;
2010 BEGIN
2011
2012 l_path_name:=g_path_name ||'.Unit_Retiremet';
2013
2014 /* Initialize Asset total balance variables */
2015 ------------------------
2016 l_asset_balances.Asset_id :=P_Asset_Id ;
2017 l_asset_balances.book_type_code :=P_Book_Type_Code;
2018 l_asset_balances.period_counter :=P_Current_period;
2019 l_asset_balances.net_book_value :=0;
2020 l_asset_balances.adjusted_cost :=0;
2021 l_asset_balances.operating_acct :=0;
2022 l_asset_balances.reval_reserve :=0;
2023 l_asset_balances.deprn_amount :=0;
2024 l_asset_balances.deprn_reserve :=0;
2025 l_asset_balances.backlog_deprn_reserve:=0;
2026 l_asset_balances.general_fund :=0;
2027 l_retirement_factor :=0;
2028
2029
2030
2031 l_Transaction_Type_Code := NULL;
2032 l_Transaction_Id := NULL;
2033 l_Mass_Reference_ID := NULL;
2034 l_adjustment_id_out := NULL;
2035 l_prev_adjustment_id := NULL;
2036 l_Adjustment_Status := NULL;
2037 l_retirement_adjustment_id := NULL;
2038
2039 debug(g_state_level,l_path_name,'Asset ID '||P_Asset_Id);
2040 -- get the latest tranaction for the asset id
2041
2042 IF NOT (igi_iac_common_utils.get_latest_transaction(P_Book_Type_Code,
2043 P_Asset_Id,
2044 l_Transaction_Type_Code,
2045 l_Transaction_Id,
2046 l_Mass_Reference_ID ,
2047 l_adjustment_id_out,
2048 l_prev_adjustment_id,
2049 l_Adjustment_Status )
2050 ) THEN
2051 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path_name,'*** Error in fetching the latest transaction');
2052 RETURN FALSE;
2053 END IF;
2054
2055
2056 debug(g_state_level,g_path_name,'got latest transaction');
2057 l_last_active_adj_id := l_prev_adjustment_id ;
2058 debug( g_state_level,g_path_name,'not reval in preview');
2059
2060 l_rowid:=NULL;
2061 l_retirement_adjustment_id := NULL;
2062 igi_iac_trans_headers_pkg.insert_row(
2063 X_rowid => l_rowid ,
2064 X_adjustment_id => l_retirement_adjustment_id ,
2065 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
2066 X_adjustment_id_out => NULL ,
2067 X_transaction_type_code => g_ret_type_long,
2068 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
2069 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
2070 X_transaction_sub_type => SUBSTR(g_retirement_type,1,1),
2071 X_book_type_code => P_Book_Type_Code,
2072 X_asset_id => p_asset_id ,
2073 X_category_id => g_asset_category_id,
2074 X_adj_deprn_start_date => NULL,
2075 X_revaluation_type_flag => NULL,
2076 X_adjustment_status => 'COMPLETE' ,
2077 X_period_counter => P_Current_period,
2078 X_mode =>'R',
2079 X_event_id => P_Event_Id) ;
2080 debug( g_state_level, l_path_name,'inserted trans_headers record');
2081
2082 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
2083 l_retirement_adjustment_id,
2084 'R') ;
2085
2086 debug( g_state_level,l_path_name,'updated old trans_headers record');
2087 debug( g_state_level,l_path_name,'Start loop');
2088
2089
2090 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
2091
2092 -- since the equivalent row in igi_iac_fa_deprn has to handled in
2093 -- the same manner, retrieving the row for simultaneous processing
2094 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
2095 l_detail_balances.distribution_id,
2096 l_detail_balances.period_counter);
2097 FETCH c_fa_deprn INTO l_fa_deprn;
2098 IF c_fa_deprn%NOTFOUND THEN
2099 CLOSE c_fa_deprn;
2100 RETURN FALSE;
2101 END IF;
2102 CLOSE c_fa_deprn;
2103
2104 OPEN c_units_per_dist(P_Asset_Id, P_Book_Type_Code, l_detail_balances.distribution_id) ;
2105 debug( g_state_level, l_path_name,'opened c_units_per_dist');
2106 FETCH c_units_per_dist INTO l_units_per_dist ;
2107 IF c_units_per_dist%NOTFOUND THEN
2108 CLOSE c_units_per_dist;
2109 debug( g_state_level,l_path_name,'units per dist not found');
2110 RAISE NO_DATA_FOUND;
2111 END IF ;
2112 CLOSE c_units_per_dist ;
2113
2114
2115
2116 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
2117
2118 -- find the impacted distribution because of the partial unit retirement
2119 OPEN c_get_impacted_dist(P_Asset_Id,P_Retirement_Id,l_detail_balances.distribution_id);
2120 FETCH c_get_impacted_dist INTO l_impact_fa_dist;
2121
2122 IF c_get_impacted_dist%FOUND THEN -- impacted by partial nit retirement
2123 CLOSE c_get_impacted_dist;
2124 -- distribtion impacted by partial unkit retirement
2125
2126
2127
2128 debug( g_state_level,l_path_name,'inside loop');
2129
2130 l_detail_balances_rnd_tot.adjustment_cost :=0 ;
2131 l_detail_balances_rnd_tot.reval_reserve_cost :=0 ;
2132 l_detail_balances_rnd_tot.deprn_reserve :=0 ;
2133 l_detail_balances_rnd_tot.deprn_reserve_backlog :=0 ;
2134 l_detail_balances_rnd_tot.reval_reserve_net :=0 ;
2135 l_detail_balances_rnd_tot.deprn_period :=0 ;
2136 l_detail_balances_rnd_tot.general_fund_acc :=0 ;
2137 l_detail_balances_rnd_tot.general_fund_per :=0 ;
2138 l_detail_balances_rnd_tot.reval_reserve_gen_fund :=0 ;
2139 l_detail_balances_rnd_tot.operating_acct_backlog :=0;
2140 l_detail_balances_rnd_tot.operating_acct_cost :=0;
2141 l_detail_balances_rnd_tot.operating_acct_net :=0;
2142 l_detail_balances_rnd_tot.reval_reserve_backlog :=0;
2143 l_detail_balances_rnd_tot.deprn_ytd :=0;
2144
2145
2146 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
2147
2148
2149 -- get the impacted new distribution create for the retire distribution
2150 l_retirement_factor := 1;
2151 IF l_impact_fa_dist.transaction_units <> l_impact_fa_dist.units_assigned THEN
2152
2153 OPEN c_get_new_dist(P_Asset_Id,P_Retirement_Id,
2154 l_impact_fa_dist.code_combination_id,
2155 ABS(l_impact_fa_dist.units_assigned + l_impact_fa_dist.transaction_units),
2156 l_impact_fa_dist.location_id,
2157 NVL(l_impact_fa_dist.assigned_to,-1));
2158 FETCH C_get_new_dist INTO l_new_fa_dist;
2159 IF C_get_new_dist%FOUND THEN
2160 l_retirement_factor := - NVL(l_impact_fa_dist.transaction_units,0)/ l_impact_fa_dist.units_assigned;
2161 l_units_per_dist.units_assigned :=l_new_fa_dist.units_assigned;
2162 END IF;
2163 CLOSE C_get_new_dist;
2164 END IF;
2165
2166
2167 IF l_retirement_factor <> 1 THEN
2168
2169 debug( g_state_level,l_path_name,'got units per dist');
2170 l_detail_balances_total_old := l_detail_balances;
2171
2172 l_fa_total_old_acc_deprn := l_fa_deprn.deprn_reserve;
2173
2174 /* Calculate retirement amounts */
2175
2176 IF p_prior_period IS NULL THEN
2177
2178 l_detail_balances_retire.adjustment_cost := l_detail_balances.adjustment_cost* l_retirement_factor ;
2179 l_detail_balances_retire.reval_reserve_cost := l_detail_balances.reval_reserve_cost* l_retirement_factor ;
2180 l_detail_balances_retire.deprn_reserve := l_detail_balances.deprn_reserve * l_retirement_factor ;
2181 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog * l_retirement_factor ;
2182 l_detail_balances_retire.reval_reserve_net := l_detail_balances.reval_reserve_net * l_retirement_factor ;
2183 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period * (1 - l_retirement_factor) ;
2184 l_detail_balances_retire.general_fund_acc := l_detail_balances.general_fund_acc * l_retirement_factor;
2185 l_detail_balances_retire.general_fund_per := l_detail_balances.general_fund_per * l_retirement_factor;
2186 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund * l_retirement_factor;
2187 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances.operating_acct_backlog * l_retirement_factor;
2188 l_detail_balances_retire.operating_acct_cost :=l_detail_balances.operating_acct_cost * l_retirement_factor;
2189 l_detail_balances_retire.operating_acct_net :=l_detail_balances.operating_acct_net * l_retirement_factor;
2190 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog * l_retirement_factor ;
2191 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd * l_retirement_factor;
2192
2193 ELSE
2194 -- get the prior record for the distribution
2195 debug( g_state_level,l_path_name,'Prior period unit processing');
2196 OPEN C_get_prior_dist(p_asset_id,p_book_type_code,l_detail_balances.distribution_id,p_prior_period);
2197 FETCH c_get_prior_dist INTO l_detail_balances_prior;
2198 CLOSE c_get_prior_dist;
2199
2200
2201
2202 l_detail_balances_retire.adjustment_cost := l_detail_balances_prior.adjustment_cost* l_retirement_factor ;
2203 l_detail_balances_retire.reval_reserve_cost := l_detail_balances_prior.reval_reserve_cost* l_retirement_factor ;
2204 l_detail_balances_retire.deprn_reserve := l_detail_balances_prior.deprn_reserve * l_retirement_factor ;
2205 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_prior.deprn_reserve_backlog * l_retirement_factor ;
2206 l_detail_balances_retire.reval_reserve_net := l_detail_balances_prior.reval_reserve_net * l_retirement_factor ;
2207 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period * (1 - l_retirement_factor) ;
2208 l_detail_balances_retire.general_fund_acc := l_detail_balances_prior.general_fund_acc * l_retirement_factor;
2209 l_detail_balances_retire.general_fund_per := l_detail_balances_prior.general_fund_per * l_retirement_factor;
2210 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_prior.reval_reserve_gen_fund * l_retirement_factor;
2211 l_detail_balances_retire.operating_acct_backlog := l_detail_balances_prior.operating_acct_backlog * l_retirement_factor;
2212 l_detail_balances_retire.operating_acct_cost := l_detail_balances_prior.operating_acct_cost * l_retirement_factor;
2213 l_detail_balances_retire.operating_acct_net := l_detail_balances_prior.operating_acct_net * l_retirement_factor;
2214 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_prior.reval_reserve_backlog * l_retirement_factor ;
2215 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd * l_retirement_factor;
2216 debug( g_state_level,l_path_name,'End Prior period unit processing');
2217 END IF;
2218
2219 l_fa_retire_acc_deprn := l_fa_total_old_acc_deprn * l_retirement_factor;
2220 l_fa_retire_deprn_prd := l_fa_deprn.deprn_period * (1 - l_retirement_factor);
2221 l_fa_retire_deprn_ytd := l_fa_deprn.deprn_ytd * l_retirement_factor;
2222
2223 /* Do roundings */
2224
2225 l_asset_units_count := l_asset_units_count + l_units_per_dist.units_assigned ;
2226
2227 IF (l_asset_units_count = g_total_asset_units) and (p_prior_period is null) THEN
2228
2229 l_detail_balances_retire.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_rnd_tot.adjustment_cost;
2230 l_detail_balances_retire.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_rnd_tot.reval_reserve_cost;
2231 l_detail_balances_retire.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_rnd_tot.deprn_reserve ;
2232 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_rnd_tot.deprn_reserve_backlog ;
2233 l_detail_balances_retire.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_rnd_tot.reval_reserve_net ;
2234 l_detail_balances_retire.deprn_period := l_detail_balances.deprn_period + l_detail_balances_rnd_tot.deprn_period;
2235 l_detail_balances_retire.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_rnd_tot.general_fund_acc;
2236 l_detail_balances_retire.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_rnd_tot.general_fund_per;
2237 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_rnd_tot.reval_reserve_gen_fund;
2238 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances.operating_acct_backlog + l_detail_balances_rnd_tot.operating_acct_backlog ;
2239 l_detail_balances_retire.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_rnd_tot.operating_acct_cost;
2240 l_detail_balances_retire.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_rnd_tot.operating_acct_net ;
2241 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_rnd_tot.reval_reserve_backlog;
2242 l_detail_balances_retire.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_rnd_tot.deprn_ytd;
2243
2244
2245 l_fa_retire_acc_deprn := l_fa_retire_acc_deprn + l_fa_tot_round_acc_deprn;
2246 l_fa_retire_deprn_prd := l_fa_retire_deprn_prd + l_fa_tot_round_deprn_prd;
2247 l_fa_retire_deprn_ytd := l_fa_retire_deprn_ytd + l_fa_tot_round_deprn_ytd;
2248
2249 END IF ;
2250
2251
2252 l_detail_balances_retire_unrnd.adjustment_cost :=l_detail_balances_retire.adjustment_cost ;
2253 l_detail_balances_retire_unrnd.reval_reserve_cost :=l_detail_balances_retire.reval_reserve_cost ;
2254 l_detail_balances_retire_unrnd.deprn_reserve := l_detail_balances_retire.deprn_reserve;
2255 l_detail_balances_retire_unrnd.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog;
2256 l_detail_balances_retire_unrnd.reval_reserve_net := l_detail_balances_retire.reval_reserve_net ;
2257 l_detail_balances_retire_unrnd.deprn_period := l_detail_balances_retire.deprn_period;
2258 l_detail_balances_retire_unrnd.general_fund_acc := l_detail_balances_retire.general_fund_acc;
2259 l_detail_balances_retire_unrnd.general_fund_per := l_detail_balances_retire.general_fund_per;
2260 l_detail_balances_retire_unrnd.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund;
2261 l_detail_balances_retire_unrnd.operating_acct_backlog := l_detail_balances_retire.operating_acct_backlog;
2262 l_detail_balances_retire_unrnd.operating_acct_cost := l_detail_balances_retire.operating_acct_cost;
2263 l_detail_balances_retire_unrnd.operating_acct_net := l_detail_balances_retire.operating_acct_net;
2264 l_detail_balances_retire_unrnd.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog;
2265 l_detail_balances_retire_unrnd.deprn_ytd := l_detail_balances_retire.deprn_ytd;
2266
2267
2268
2269 l_fa_unround_acc_deprn := l_fa_retire_acc_deprn;
2270 l_fa_unround_deprn_prd := l_fa_retire_deprn_prd;
2271 l_fa_unround_deprn_ytd := l_fa_retire_deprn_ytd;
2272
2273
2274 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.adjustment_cost,P_Book_Type_Code) ;
2275 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_cost,P_Book_Type_Code) ;
2276 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_reserve,P_Book_Type_Code) ;
2277 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_reserve_backlog,P_Book_Type_Code) ;
2278 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_net,P_Book_Type_Code) ;
2279 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_period,P_Book_Type_Code) ;
2280 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.general_fund_acc,P_Book_Type_Code) ;
2281 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.general_fund_per,P_Book_Type_Code) ;
2282 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_gen_fund,P_Book_Type_Code) ;
2283 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_backlog,P_Book_Type_Code) ;
2284 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_cost,P_Book_Type_Code) ;
2285 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.operating_acct_net,P_Book_Type_Code) ;
2286 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.reval_reserve_backlog,P_Book_Type_Code) ;
2287 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances_retire.deprn_ytd,P_Book_Type_Code) ;
2288
2289 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_acc_deprn,P_Book_Type_Code) ;
2290 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_deprn_prd,P_Book_Type_Code) ;
2291 l_ret:= igi_iac_common_utils.iac_round(l_fa_retire_deprn_ytd,P_Book_Type_Code) ;
2292
2293 l_detail_balances_rnd_tot.adjustment_cost:= l_detail_balances_rnd_tot.adjustment_cost;
2294
2295 l_detail_balances_rnd_tot.adjustment_cost := l_detail_balances_rnd_tot.adjustment_cost +
2296 (l_detail_balances_retire_unrnd.adjustment_cost - l_detail_balances_retire.adjustment_cost);
2297 l_detail_balances_rnd_tot.reval_reserve_cost := l_detail_balances_rnd_tot.reval_reserve_cost +
2298 (l_detail_balances_retire_unrnd.reval_reserve_cost - l_detail_balances_retire.reval_reserve_cost);
2299 l_detail_balances_rnd_tot.deprn_reserve := l_detail_balances_rnd_tot.deprn_reserve +
2300 (l_detail_balances_retire_unrnd.deprn_reserve - l_detail_balances_retire.deprn_reserve) ;
2301 l_detail_balances_rnd_tot.deprn_reserve_backlog := l_detail_balances_rnd_tot.deprn_reserve_backlog +
2302 (l_detail_balances_retire_unrnd.deprn_reserve_backlog - l_detail_balances_retire.deprn_reserve_backlog) ;
2303 l_detail_balances_rnd_tot.reval_reserve_net := l_detail_balances_rnd_tot.reval_reserve_net +
2304 (l_detail_balances_retire_unrnd.reval_reserve_net - l_detail_balances_retire.reval_reserve_net);
2305 l_detail_balances_rnd_tot.deprn_period := l_detail_balances_rnd_tot.deprn_period +
2306 (l_detail_balances_retire_unrnd.deprn_period - l_detail_balances_retire.deprn_period);
2307 l_detail_balances_rnd_tot.general_fund_acc := l_detail_balances_rnd_tot.general_fund_acc +
2308 (l_detail_balances_retire_unrnd.general_fund_acc - l_detail_balances_retire.general_fund_acc);
2309 l_detail_balances_rnd_tot.general_fund_per := l_detail_balances_rnd_tot.general_fund_per +
2310 (l_detail_balances_retire_unrnd.general_fund_per- l_detail_balances_retire.general_fund_per);
2311 l_detail_balances_rnd_tot.reval_reserve_gen_fund := l_detail_balances_rnd_tot.reval_reserve_gen_fund +
2312 (l_detail_balances_retire_unrnd.reval_reserve_gen_fund - l_detail_balances_retire.reval_reserve_gen_fund);
2313 l_detail_balances_rnd_tot.operating_acct_backlog :=l_detail_balances_rnd_tot.operating_acct_backlog +
2314 (l_detail_balances_retire_unrnd.operating_acct_backlog - l_detail_balances_retire.operating_acct_backlog) ;
2315 l_detail_balances_rnd_tot.operating_acct_cost :=l_detail_balances_rnd_tot.operating_acct_cost +
2316 (l_detail_balances_retire_unrnd.operating_acct_cost - l_detail_balances_retire.operating_acct_cost);
2317 l_detail_balances_rnd_tot.operating_acct_net :=l_detail_balances_rnd_tot.operating_acct_net+
2318 (l_detail_balances_retire_unrnd.operating_acct_net - l_detail_balances_retire.operating_acct_net) ;
2319 l_detail_balances_rnd_tot.reval_reserve_backlog := l_detail_balances_rnd_tot.reval_reserve_backlog +
2320 (l_detail_balances_retire_unrnd.reval_reserve_backlog - l_detail_balances_retire.reval_reserve_backlog);
2321 l_detail_balances_rnd_tot.deprn_ytd := l_detail_balances_rnd_tot.deprn_ytd +
2322 (l_detail_balances_retire_unrnd.deprn_ytd - l_detail_balances_retire.deprn_ytd);
2323
2324
2325 l_fa_tot_round_acc_deprn := l_fa_tot_round_acc_deprn + l_fa_unround_acc_deprn - l_fa_retire_acc_deprn ;
2326 l_fa_tot_round_deprn_prd := l_fa_tot_round_deprn_prd + l_fa_unround_deprn_prd - l_fa_retire_deprn_prd ;
2327 l_fa_tot_round_deprn_ytd := l_fa_tot_round_deprn_ytd + l_fa_unround_deprn_ytd - l_fa_retire_deprn_ytd ;
2328
2329
2330 debug( g_state_level,l_path_name,'done roundings');
2331
2332 /* Calculate new totals */
2333 l_detail_balances_new.adjustment_cost := l_detail_balances.adjustment_cost - l_detail_balances_retire.adjustment_cost;
2334 l_detail_balances_new.reval_reserve_cost := l_detail_balances.reval_reserve_cost - l_detail_balances_retire.reval_reserve_cost;
2335 l_detail_balances_new.deprn_reserve := l_detail_balances.deprn_reserve - l_detail_balances_retire.deprn_reserve ;
2336 l_detail_balances_new.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog - l_detail_balances_retire.deprn_reserve_backlog ;
2337 l_detail_balances_new.reval_reserve_net := l_detail_balances.reval_reserve_net - l_detail_balances_retire.reval_reserve_net ;
2338 l_detail_balances_new.deprn_period := l_detail_balances.deprn_period - l_detail_balances_retire.deprn_period;
2339 l_detail_balances_new.general_fund_acc := l_detail_balances.general_fund_acc - l_detail_balances_retire.general_fund_acc;
2340 l_detail_balances_new.general_fund_per := l_detail_balances.general_fund_per - l_detail_balances_retire.general_fund_per;
2341 l_detail_balances_new.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund - l_detail_balances_retire.reval_reserve_gen_fund;
2342 l_detail_balances_new.operating_acct_backlog :=l_detail_balances.operating_acct_backlog - l_detail_balances_retire.operating_acct_backlog;
2343 l_detail_balances_new.operating_acct_cost :=l_detail_balances.operating_acct_cost - l_detail_balances_retire.operating_acct_cost;
2344 l_detail_balances_new.operating_acct_net :=l_detail_balances.operating_acct_net - l_detail_balances_retire.operating_acct_net ;
2345 l_detail_balances_new.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog - l_detail_balances_retire.reval_reserve_backlog;
2346 l_detail_balances_new.deprn_ytd := l_detail_balances.deprn_ytd - l_detail_balances_retire.deprn_ytd;
2347
2348 l_detail_balances_new.net_book_value := l_detail_balances_new.adjustment_cost - l_detail_balances_new.deprn_reserve -l_detail_balances_new.deprn_reserve_backlog ;
2349
2350
2351 l_fa_total_new_acc_deprn := l_fa_total_old_acc_deprn - l_fa_retire_acc_deprn ;
2352 l_fa_total_new_deprn_prd := l_fa_deprn.deprn_period - l_fa_retire_deprn_prd ;
2353 l_fa_total_new_deprn_ytd := l_fa_deprn.deprn_ytd - l_fa_retire_deprn_ytd ;
2354
2355 --asset total;
2356 l_asset_balances.net_book_value :=l_asset_balances.net_book_value+l_detail_balances_new.net_book_value;
2357 l_asset_balances.adjusted_cost :=l_asset_balances.adjusted_cost +l_detail_balances_new.adjustment_cost;
2358 l_asset_balances.operating_acct :=l_asset_balances.operating_acct+ l_detail_balances_new.operating_acct_net;
2359 l_asset_balances.reval_reserve :=l_asset_balances.reval_reserve +l_detail_balances_new.reval_reserve_net;
2360 l_asset_balances.deprn_amount :=l_asset_balances.deprn_amount +l_detail_balances_new.deprn_period;
2361 l_asset_balances.deprn_reserve :=l_asset_balances.deprn_reserve +l_detail_balances_new.deprn_reserve;
2362 l_asset_balances.backlog_deprn_reserve:=l_asset_balances.backlog_deprn_reserve+l_detail_balances_new.deprn_reserve_backlog;
2363 l_asset_balances.general_fund :=l_asset_balances.general_fund +l_detail_balances_new.general_fund_acc;
2364
2365 END IF; -- Retirement factor <> 1 new distributions created in partial unit retirement
2366
2367 IF p_prior_period IS NOT NULL AND l_retirement_factor <> 1 THEN
2368
2369 l_detail_balances_latest:=l_detail_balances ;
2370 l_detail_balances := l_detail_balances_prior;
2371
2372 END IF;
2373
2374 /* Create adjustment to reverse out NOCOPY old balances */
2375 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2376 'ASSET_COST_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2377 'RETIREMENT',l_cost_account_ccid
2378 )) THEN
2379 RAISE e_no_account_ccid ;
2380 END IF ;
2381
2382 debug( g_state_level,l_path_name,'done cost get acct ccid');
2383 l_rowid := NULL ;
2384 debug( g_state_level, l_path_name,'done cost adjustment');
2385 debug( g_state_level, l_path_name,'dist id: '||l_detail_balances.distribution_id);
2386 l_acc_deprn_account_ccid := NULL;
2387 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2388 'DEPRN_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2389 'RETIREMENT',l_acc_deprn_account_ccid
2390 )) THEN
2391 RAISE e_no_account_ccid ;
2392 END IF ;
2393 debug( g_state_level,l_path_name,'done deprn rsv ccid');
2394 debug( g_state_level,l_path_name, '*' ||l_acc_deprn_account_ccid || '*');
2395 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2396 'REVAL_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2397 'RETIREMENT',l_reval_rsv_account_ccid
2398 )) THEN
2399 RAISE e_no_account_ccid ;
2400 END IF ;
2401 debug( g_state_level,l_path_name,'done reval rsv ccid');
2402
2403 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2404 'BACKLOG_DEPRN_RSV_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2405 'RETIREMENT',l_backlog_account_ccid
2406 )) THEN
2407 RAISE e_no_account_ccid ;
2408 END IF ;
2409 debug( g_state_level,l_path_name,'done backlog ccid');
2410
2411 /* Create new adjustments for retirement part */
2412
2413 IF ((l_detail_balances.adjustment_cost-l_detail_balances.deprn_reserve<>0) OR (l_detail_balances.deprn_reserve_backlog <>0)) THEN
2414 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2415 'NBV_RETIRED_GAIN_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2416 'RETIREMENT',l_nbv_retired_account_ccid
2417 )) THEN
2418 RAISE e_no_account_ccid ;
2419 END IF ;
2420 debug( g_state_level,l_path_name,'done nbv ret ccid');
2421 l_rowid := NULL ;
2422
2423 igi_iac_adjustments_pkg.insert_row(
2424 X_rowid => l_rowid ,
2425 X_adjustment_id => l_retirement_adjustment_id ,
2426 X_book_type_code => P_Book_Type_Code ,
2427 X_code_combination_id => l_nbv_retired_account_ccid,
2428 X_set_of_books_id => g_sob_id ,
2429 X_dr_cr_flag => 'DR' ,
2430 X_amount => l_detail_balances.adjustment_cost-l_detail_balances.deprn_reserve,
2431 X_adjustment_type => 'NBV RETIRED',
2432 X_transfer_to_gl_flag => 'Y' ,
2433 X_units_assigned => l_units_per_dist.units_assigned ,
2434 X_asset_id => P_Asset_Id ,
2435 X_distribution_id => l_detail_balances.distribution_id ,
2436 X_period_counter => g_prd_rec.period_counter,
2437 X_adjustment_offset_type => NULL,
2438 X_report_ccid => NULL,
2439 X_mode => 'R',
2440 X_event_id => P_Event_Id ) ;
2441
2442 debug( g_state_level,l_path_name,'done nbv ret insert');
2443
2444 l_rowid := NULL ;
2445
2446 igi_iac_adjustments_pkg.insert_row(
2447 X_rowid => l_rowid ,
2448 X_adjustment_id => l_retirement_adjustment_id ,
2449 X_book_type_code => P_Book_Type_Code ,
2450 X_code_combination_id => l_nbv_retired_account_ccid,
2451 X_set_of_books_id => g_sob_id ,
2452 X_dr_cr_flag => 'CR' ,
2453 X_amount => l_detail_balances.deprn_reserve_backlog,
2454 X_adjustment_type => 'NBV RETIRED',
2455 X_transfer_to_gl_flag => 'Y' ,
2456 X_units_assigned => l_units_per_dist.units_assigned ,
2457 X_asset_id => P_Asset_Id ,
2458 X_distribution_id => l_detail_balances.distribution_id ,
2459 X_period_counter => g_prd_rec.period_counter,
2460 X_adjustment_offset_type => NULL,
2461 X_report_ccid => NULL,
2462 X_mode => 'R',
2463 X_event_id => P_Event_Id ) ;
2464
2465 debug( g_state_level,l_path_name, 'done 2nd nbv ret insert');
2466 END IF;
2467
2468
2469 IF l_detail_balances.reval_reserve_net <> 0 THEN
2470 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2471 'REVAL_RESERVE_RETIRED_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2472 'RETIREMENT',l_reval_rsv_ret_acct_ccid
2473 )) THEN
2474 RAISE e_no_account_ccid ;
2475 END IF ;
2476 debug( g_state_level,l_path_name,'done reval rsv ret ccid');
2477 l_rowid := NULL ;
2478 igi_iac_adjustments_pkg.insert_row(
2479 X_rowid => l_rowid ,
2480 X_adjustment_id => l_retirement_adjustment_id ,
2481 X_book_type_code => P_Book_Type_Code ,
2482 X_code_combination_id => l_reval_rsv_ret_acct_ccid,
2483 X_set_of_books_id => g_sob_id ,
2484 X_dr_cr_flag => 'CR' ,
2485 X_amount => l_detail_balances.reval_reserve_net,
2486 X_adjustment_type => 'REVAL RSV RET',
2487 X_transfer_to_gl_flag => 'Y' ,
2488 X_units_assigned => l_units_per_dist.units_assigned ,
2489 X_asset_id => P_Asset_Id ,
2490 X_distribution_id => l_detail_balances.distribution_id ,
2491 X_period_counter => g_prd_rec.period_counter,
2492 X_adjustment_offset_type => NULL,
2493 X_report_ccid => NULL,
2494 X_mode => 'R',
2495 X_event_id => P_Event_Id ) ;
2496
2497 debug( g_state_level,l_path_name,'done reval rsv ret insert');
2498 END IF;
2499
2500 /* Create adjustment for new balances */
2501 debug( g_state_level,l_path_name,'start new balances');
2502 l_rowid := NULL ;
2503 igi_iac_adjustments_pkg.insert_row(
2504 X_rowid => l_rowid ,
2505 X_adjustment_id => l_retirement_adjustment_id ,
2506 X_book_type_code => P_Book_Type_Code ,
2507 X_code_combination_id => l_cost_account_ccid,
2508 X_set_of_books_id => g_sob_id ,
2509 X_dr_cr_flag => 'CR' ,
2510 X_amount => l_detail_balances.adjustment_cost,
2511 X_adjustment_type => 'COST',
2512 X_transfer_to_gl_flag => 'Y' ,
2513 X_units_assigned => l_units_per_dist.units_assigned ,
2514 X_asset_id => P_Asset_Id ,
2515 X_distribution_id => l_detail_balances.distribution_id ,
2516 X_period_counter => g_prd_rec.period_counter,
2517 X_adjustment_offset_type => NULL,
2518 X_report_ccid => NULL,
2519 X_mode => 'R',
2520 X_event_id => P_Event_Id ) ;
2521
2522
2523
2524 debug( g_state_level,l_path_name,'done new cost');
2525 IF l_detail_balances.deprn_reserve <> 0 THEN
2526
2527 l_rowid := NULL ;
2528 igi_iac_adjustments_pkg.insert_row(
2529 X_rowid => l_rowid ,
2530 X_adjustment_id => l_retirement_adjustment_id ,
2531 X_book_type_code => P_Book_Type_Code ,
2532 X_code_combination_id => l_acc_deprn_account_ccid,
2533 X_set_of_books_id => g_sob_id ,
2534 X_dr_cr_flag => 'DR' ,
2535 X_amount => l_detail_balances.deprn_reserve,
2536 X_adjustment_type => 'RESERVE',
2537 X_transfer_to_gl_flag => 'Y' ,
2538 X_units_assigned => l_units_per_dist.units_assigned ,
2539 X_asset_id => P_Asset_Id ,
2540 X_distribution_id => l_detail_balances.distribution_id ,
2541 X_period_counter => g_prd_rec.period_counter,
2542 X_adjustment_offset_type => NULL,
2543 X_report_ccid => NULL,
2544 X_mode => 'R',
2545 X_event_id => P_Event_Id ) ;
2546
2547 END IF;
2548
2549 IF l_detail_balances.operating_acct_backlog <> 0 THEN
2550
2551 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_detail_balances.distribution_id,
2552 'OPERATING_EXPENSE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2553 'RETIREMENT',l_op_exp_ccid
2554 )) THEN
2555 RAISE e_no_account_ccid ;
2556 END IF ;
2557
2558 l_rowid := NULL ;
2559 igi_iac_adjustments_pkg.insert_row(
2560 X_rowid => l_rowid ,
2561 X_adjustment_id => l_retirement_adjustment_id ,
2562 X_book_type_code => P_Book_Type_Code ,
2563 X_code_combination_id => l_backlog_account_ccid,
2564 X_set_of_books_id => g_sob_id ,
2565 X_dr_cr_flag => 'DR' ,
2566 X_amount => l_detail_balances.operating_acct_backlog,
2567 X_adjustment_type => 'BL RESERVE',
2568 X_transfer_to_gl_flag => 'Y' ,
2569 X_units_assigned => l_units_per_dist.units_assigned ,
2570 X_asset_id => P_Asset_Id ,
2571 X_distribution_id => l_detail_balances.distribution_id ,
2572 X_period_counter => g_prd_rec.period_counter,
2573 X_adjustment_offset_type => 'OP EXPENSE',
2574 X_report_ccid => l_op_exp_ccid,
2575 X_mode => 'R',
2576 X_event_id => P_Event_Id ) ;
2577
2578
2579
2580 debug( g_state_level,l_path_name,'done BL reserve for OP ');
2581 END IF;
2582
2583 IF l_detail_balances.reval_reserve_backlog <> 0 THEN
2584
2585 l_rowid := NULL ;
2586 igi_iac_adjustments_pkg.insert_row(
2587 X_rowid => l_rowid ,
2588 X_adjustment_id => l_retirement_adjustment_id ,
2589 X_book_type_code => P_Book_Type_Code ,
2590 X_code_combination_id => l_backlog_account_ccid,
2591 X_set_of_books_id => g_sob_id ,
2592 X_dr_cr_flag => 'DR' ,
2593 X_amount => l_detail_balances.reval_reserve_backlog,
2594 X_adjustment_type => 'BL RESERVE',
2595 X_transfer_to_gl_flag => 'Y' ,
2596 X_units_assigned => l_units_per_dist.units_assigned ,
2597 X_asset_id => P_Asset_Id ,
2598 X_distribution_id => l_detail_balances.distribution_id ,
2599 X_period_counter => g_prd_rec.period_counter,
2600 X_adjustment_offset_type => 'REVAL RESERVE',
2601 X_report_ccid => l_reval_rsv_account_ccid,
2602 X_mode => 'R',
2603 X_event_id => P_Event_Id ) ;
2604
2605
2606
2607 debug( g_state_level,l_path_name,'done BL reserve for OP ');
2608 END IF;
2609
2610 IF l_detail_balances.reval_reserve_net<> 0 THEN
2611
2612 l_rowid := NULL ;
2613 igi_iac_adjustments_pkg.insert_row(
2614 X_rowid => l_rowid ,
2615 X_adjustment_id => l_retirement_adjustment_id ,
2616 X_book_type_code => P_Book_Type_Code ,
2617 X_code_combination_id => l_reval_rsv_account_ccid,
2618 X_set_of_books_id => g_sob_id ,
2619 X_dr_cr_flag => 'DR' ,
2620 X_amount => l_detail_balances.reval_reserve_net,
2621 X_adjustment_type => 'REVAL RESERVE',
2622 X_transfer_to_gl_flag => 'Y' ,
2623 X_units_assigned => l_units_per_dist.units_assigned ,
2624 X_asset_id => P_Asset_Id ,
2625 X_distribution_id => l_detail_balances.distribution_id ,
2626 X_period_counter => g_prd_rec.period_counter,
2627 X_adjustment_offset_type => NULL,
2628 X_report_ccid => NULL,
2629 X_mode => 'R',
2630 X_event_id => P_Event_Id ) ;
2631
2632 debug( g_state_level,l_path_name,'done reval rsv');
2633
2634 END IF;
2635 debug( g_state_level,l_path_name,'end new balances');
2636
2637
2638 IF p_prior_period IS NOT NULL AND l_retirement_factor <> 1 THEN
2639 l_detail_balances:=l_detail_balances_latest;
2640 END IF;
2641
2642 /* Insert new detail balance record for this distribution */
2643 IF (g_is_first_period) THEN
2644 l_db_op_acct_ytd := 0;
2645 l_db_deprn_ytd := 0;
2646 ELSE
2647 l_db_op_acct_ytd := l_detail_balances.operating_acct_ytd;
2648 l_db_deprn_ytd := l_detail_balances.deprn_ytd;
2649 END IF;
2650
2651 debug( g_state_level,l_path_name,'start insert det bal');
2652
2653 IF l_retirement_factor = 1 THEN
2654 l_detail_balances.deprn_ytd:=0;
2655 l_fa_deprn.deprn_ytd :=0;
2656 ELSE
2657 l_detail_balances.deprn_ytd:=l_detail_balances.deprn_ytd *(1-l_retirement_factor);
2658 l_ret:= igi_iac_common_utils.iac_round(l_detail_balances.deprn_ytd,P_Book_Type_Code);
2659 l_fa_deprn.deprn_ytd:=l_fa_deprn.deprn_ytd *(1-l_retirement_factor);
2660 l_ret:= igi_iac_common_utils.iac_round(l_fa_deprn.deprn_ytd,P_Book_Type_Code);
2661 END IF;
2662
2663 l_rowid := NULL ;
2664
2665 igi_iac_det_balances_pkg.insert_row(
2666 X_rowid => l_rowid ,
2667 X_adjustment_id => l_retirement_adjustment_id ,
2668 X_asset_id => P_Asset_Id ,
2669 X_distribution_id => l_detail_balances.distribution_id ,
2670 X_book_type_code => P_Book_Type_Code ,
2671 X_period_counter => g_prd_rec.period_counter,
2672 X_adjustment_cost => 0,
2673 X_net_book_value => 0,
2674 X_reval_reserve_cost => 0,
2675 X_reval_reserve_backlog => 0,
2676 X_reval_reserve_gen_fund => 0,
2677 X_reval_reserve_net => 0,
2678 X_operating_acct_cost => 0,
2679 X_operating_acct_backlog => 0,
2680 X_operating_acct_net => 0,
2681 X_operating_acct_ytd => 0,
2682 X_deprn_period => 0,
2683 X_deprn_ytd => l_detail_balances.deprn_ytd,
2684 X_deprn_reserve => 0,
2685 X_deprn_reserve_backlog => 0,
2686 X_general_fund_per => 0,
2687 X_general_fund_acc => 0,
2688 X_last_reval_date => l_detail_balances.last_reval_date ,
2689 X_current_reval_factor => l_detail_balances.current_reval_factor ,
2690 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
2691 X_active_flag => 'N',
2692 X_mode => 'R') ;
2693
2694
2695
2696 debug( g_state_level,l_path_name,'end insert det bals');
2697 debug( g_state_level,l_path_name,'CURRENT PERIOD -- inactive');
2698 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
2699 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
2700 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
2701 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
2702 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
2703 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances_new.deprn_ytd);
2704 debug( g_state_level,l_path_name,'X_active_flag =>'|| 'N');
2705
2706
2707 IF (g_is_first_period) THEN
2708 l_fa_deprn.deprn_ytd := 0;
2709 ELSE
2710 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
2711 END IF;
2712 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
2713
2714 l_rowid := NULL ;
2715 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
2716 x_rowid => g_rowid,
2717 x_book_type_code => p_book_type_code,
2718 x_asset_id => p_asset_id,
2719 x_period_counter => g_prd_rec.period_counter,
2720 x_adjustment_id => l_retirement_adjustment_id,
2721 x_distribution_id => l_fa_deprn.distribution_id,
2722 x_deprn_period => 0,
2723 x_deprn_ytd => l_fa_deprn.deprn_ytd,
2724 x_deprn_reserve => 0,
2725 x_active_flag => 'N',
2726 x_mode => 'R');
2727
2728 IF l_retirement_factor <> 1 THEN -- for new distribution created
2729
2730 --Create balances and adjustments for new distriution create in partial retirement
2731 l_cost_account_ccid:=NULL;
2732 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_new_fa_dist.distribution_id,
2733 'ASSET_COST_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2734 'RETIREMENT',l_cost_account_ccid
2735 )) THEN
2736 RAISE e_no_account_ccid ;
2737 END IF ;
2738
2739 debug( g_state_level,l_path_name,'done cost get acct ccid for new dist');
2740 l_rowid := NULL ;
2741 debug( g_state_level, l_path_name,'done cost adjustment for new dist');
2742
2743 l_acc_deprn_account_ccid := NULL;
2744
2745 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_new_fa_dist.distribution_id,
2746 'DEPRN_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2747 'RETIREMENT',l_acc_deprn_account_ccid
2748 )) THEN
2749 RAISE e_no_account_ccid ;
2750 END IF ;
2751 debug( g_state_level,l_path_name,'done deprn rsv ccid for new dist');
2752 debug( g_state_level,l_path_name, '*' ||l_acc_deprn_account_ccid || '*');
2753 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_new_fa_dist.distribution_id,
2754 'REVAL_RESERVE_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2755 'RETIREMENT',l_reval_rsv_account_ccid
2756 )) THEN
2757 RAISE e_no_account_ccid ;
2758 END IF ;
2759 debug( g_state_level,l_path_name,'done reval rsv ccid for new dist');
2760 IF NOT (igi_iac_common_utils.get_account_ccid(P_Book_Type_Code,P_Asset_Id,l_new_fa_dist.distribution_id,
2761 'BACKLOG_DEPRN_RSV_ACCT',g_retire_rec.detail_info.transaction_header_id_in,
2762 'RETIREMENT',l_backlog_account_ccid
2763 )) THEN
2764 RAISE e_no_account_ccid ;
2765 END IF ;
2766 debug( g_state_level,l_path_name,'done backlog ccid for new dist');
2767 debug( g_state_level,l_path_name,'start new balances for new dist');
2768
2769 IF ((l_detail_balances_new.adjustment_cost-l_detail_balances_new.deprn_reserve<>0) OR (l_detail_balances_new.deprn_reserve_backlog <>0)) THEN -- Kaps
2770
2771 l_rowid := NULL ;
2772 igi_iac_adjustments_pkg.insert_row(
2773 X_rowid => l_rowid ,
2774 X_adjustment_id => l_retirement_adjustment_id ,
2775 X_book_type_code => P_Book_Type_Code ,
2776 X_code_combination_id => l_nbv_retired_account_ccid,
2777 X_set_of_books_id => g_sob_id ,
2778 X_dr_cr_flag => 'CR' ,
2779 X_amount => l_detail_balances_new.adjustment_cost-l_detail_balances_new.deprn_reserve,
2780 X_adjustment_type => 'NBV RETIRED',
2781 X_transfer_to_gl_flag => 'Y' ,
2782 X_units_assigned => l_units_per_dist.units_assigned ,
2783 X_asset_id => P_Asset_Id ,
2784 X_distribution_id => l_new_fa_dist.distribution_id ,
2785 X_period_counter => g_prd_rec.period_counter,
2786 X_adjustment_offset_type => NULL,
2787 X_report_ccid => NULL,
2788 X_mode => 'R',
2789 X_event_id => P_Event_Id ) ;
2790
2791 debug( g_state_level,l_path_name,'done nbv ret insert');
2792
2793 l_rowid := NULL ;
2794
2795 igi_iac_adjustments_pkg.insert_row(
2796 X_rowid => l_rowid ,
2797 X_adjustment_id => l_retirement_adjustment_id ,
2798 X_book_type_code => P_Book_Type_Code ,
2799 X_code_combination_id => l_nbv_retired_account_ccid,
2800 X_set_of_books_id => g_sob_id ,
2801 X_dr_cr_flag => 'DR' ,
2802 X_amount => l_detail_balances_new.deprn_reserve_backlog,
2803 X_adjustment_type => 'NBV RETIRED',
2804 X_transfer_to_gl_flag => 'Y' ,
2805 X_units_assigned => l_units_per_dist.units_assigned ,
2806 X_asset_id => P_Asset_Id ,
2807 X_distribution_id => l_new_fa_dist.distribution_id ,
2808 X_period_counter => g_prd_rec.period_counter,
2809 X_adjustment_offset_type => NULL,
2810 X_report_ccid => NULL,
2811 X_mode => 'R',
2812 X_event_id => P_Event_Id ) ;
2813
2814 debug( g_state_level,l_path_name, 'done 2nd nbv ret insert');
2815 END IF;
2816
2817
2818 IF l_detail_balances_new.reval_reserve_net <> 0 THEN
2819
2820 debug( g_state_level,l_path_name,'done reval rsv ret ccid');
2821 l_rowid := NULL ;
2822 igi_iac_adjustments_pkg.insert_row(
2823 X_rowid => l_rowid ,
2824 X_adjustment_id => l_retirement_adjustment_id ,
2825 X_book_type_code => P_Book_Type_Code ,
2826 X_code_combination_id => l_reval_rsv_ret_acct_ccid,
2827 X_set_of_books_id => g_sob_id ,
2828 X_dr_cr_flag => 'DR' ,
2829 X_amount => l_detail_balances_new.reval_reserve_net,
2830 X_adjustment_type => 'REVAL RSV RET',
2831 X_transfer_to_gl_flag => 'Y' ,
2832 X_units_assigned => l_units_per_dist.units_assigned ,
2833 X_asset_id => P_Asset_Id ,
2834 X_distribution_id => l_new_fa_dist.distribution_id ,
2835 X_period_counter => g_prd_rec.period_counter,
2836 X_adjustment_offset_type => NULL,
2837 X_report_ccid => NULL,
2838 X_mode => 'R',
2839 X_event_id => P_Event_Id ) ;
2840
2841 debug( g_state_level,l_path_name,'done reval rsv ret insert');
2842 END IF;
2843
2844 l_rowid := NULL ;
2845 igi_iac_adjustments_pkg.insert_row(
2846 X_rowid => l_rowid ,
2847 X_adjustment_id => l_retirement_adjustment_id ,
2848 X_book_type_code => P_Book_Type_Code ,
2849 X_code_combination_id => l_cost_account_ccid,
2850 X_set_of_books_id => g_sob_id ,
2851 X_dr_cr_flag => 'DR' ,
2852 X_amount => l_detail_balances_new.adjustment_cost,
2853 X_adjustment_type => 'COST',
2854 X_transfer_to_gl_flag => 'Y' ,
2855 X_units_assigned => l_units_per_dist.units_assigned ,
2856 X_asset_id => P_Asset_Id ,
2857 X_distribution_id => l_new_fa_dist.distribution_id,
2858 X_period_counter => g_prd_rec.period_counter,
2859 X_adjustment_offset_type => NULL,
2860 X_report_ccid => NULL,
2861 X_mode => 'R',
2862 X_event_id => P_Event_Id ) ;
2863
2864 debug( g_state_level,l_path_name,'done new cost for new dist');
2865 l_rowid := NULL ;
2866 igi_iac_adjustments_pkg.insert_row(
2867 X_rowid => l_rowid ,
2868 X_adjustment_id => l_retirement_adjustment_id ,
2869 X_book_type_code => P_Book_Type_Code ,
2870 X_code_combination_id => l_acc_deprn_account_ccid,
2871 X_set_of_books_id => g_sob_id ,
2872 X_dr_cr_flag => 'CR' ,
2873 X_amount => l_detail_balances_new.deprn_reserve,
2874 X_adjustment_type => 'RESERVE',
2875 X_transfer_to_gl_flag => 'Y' ,
2876 X_units_assigned => l_units_per_dist.units_assigned ,
2877 X_asset_id => P_Asset_Id ,
2878 X_distribution_id => l_new_fa_dist.distribution_id,
2879 X_period_counter => g_prd_rec.period_counter,
2880 X_adjustment_offset_type => NULL,
2881 X_report_ccid => NULL,
2882 X_mode => 'R',
2883 X_event_id => P_Event_Id ) ;
2884 l_rowid := NULL ;
2885 igi_iac_adjustments_pkg.insert_row(
2886 X_rowid => l_rowid ,
2887 X_adjustment_id => l_retirement_adjustment_id ,
2888 X_book_type_code => P_Book_Type_Code ,
2889 X_code_combination_id => l_backlog_account_ccid,
2890 X_set_of_books_id => g_sob_id ,
2891 X_dr_cr_flag => 'CR' ,
2892 X_amount => l_detail_balances_new.deprn_reserve_backlog,
2893 X_adjustment_type => 'BL RESERVE',
2894 X_transfer_to_gl_flag => 'Y' ,
2895 X_units_assigned => l_units_per_dist.units_assigned ,
2896 X_asset_id => P_Asset_Id ,
2897 X_distribution_id => l_new_fa_dist.distribution_id,
2898 X_period_counter => g_prd_rec.period_counter,
2899 X_adjustment_offset_type => NULL,
2900 X_report_ccid => NULL,
2901 X_mode => 'R',
2902 X_event_id => P_Event_Id ) ;
2903
2904
2905 debug( g_state_level,l_path_name,'done BL reserve for new dist');
2906 l_rowid := NULL ;
2907 igi_iac_adjustments_pkg.insert_row(
2908 X_rowid => l_rowid ,
2909 X_adjustment_id => l_retirement_adjustment_id ,
2910 X_book_type_code => P_Book_Type_Code ,
2911 X_code_combination_id => l_reval_rsv_account_ccid,
2912 X_set_of_books_id => g_sob_id ,
2913 X_dr_cr_flag => 'CR' ,
2914 X_amount => l_detail_balances_new.reval_reserve_net,
2915 X_adjustment_type => 'REVAL RESERVE',
2916 X_transfer_to_gl_flag => 'Y' ,
2917 X_units_assigned => l_units_per_dist.units_assigned ,
2918 X_asset_id => P_Asset_Id ,
2919 X_distribution_id => l_new_fa_dist.distribution_id,
2920 X_period_counter => g_prd_rec.period_counter,
2921 X_adjustment_offset_type => NULL,
2922 X_report_ccid => NULL,
2923 X_mode => 'R',
2924 X_event_id => P_Event_Id ) ;
2925
2926 debug( g_state_level,l_path_name,'done reval rsv for new dist');
2927
2928
2929
2930
2931
2932
2933
2934 l_rowid := NULL ;
2935 igi_iac_det_balances_pkg.insert_row(
2936 X_rowid => l_rowid ,
2937 X_adjustment_id => l_retirement_adjustment_id ,
2938 X_asset_id => P_Asset_Id ,
2939 X_distribution_id => l_new_fa_dist.distribution_id,
2940 X_book_type_code => P_Book_Type_Code ,
2941 X_period_counter => g_prd_rec.period_counter,
2942 X_adjustment_cost => l_detail_balances_new.adjustment_cost ,
2943 X_net_book_value => l_detail_balances_new.net_book_value,
2944 X_reval_reserve_cost => l_detail_balances_new.reval_reserve_cost,
2945 X_reval_reserve_backlog => l_detail_balances_new.reval_reserve_backlog,
2946 X_reval_reserve_gen_fund => l_detail_balances_new.reval_reserve_gen_fund,
2947 X_reval_reserve_net => l_detail_balances_new.reval_reserve_net,
2948 X_operating_acct_cost => l_detail_balances_new.operating_acct_cost,
2949 X_operating_acct_backlog => l_detail_balances_new.operating_acct_backlog,
2950 X_operating_acct_net => l_detail_balances_new.operating_acct_net,
2951 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
2952 X_deprn_period => l_detail_balances_new.deprn_period,
2953 X_deprn_ytd => 0,
2954 X_deprn_reserve => l_detail_balances_new.deprn_reserve,
2955 X_deprn_reserve_backlog => l_detail_balances_new.deprn_reserve_backlog,
2956 X_general_fund_per => l_detail_balances_new.general_fund_per,
2957 X_general_fund_acc => l_detail_balances_new.general_fund_acc,
2958 X_last_reval_date => l_detail_balances.last_reval_date ,
2959 X_current_reval_factor => l_detail_balances.current_reval_factor ,
2960 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
2961 X_active_flag => l_detail_balances.active_flag,
2962 X_mode => 'R') ;
2963
2964 debug( g_state_level,l_path_name,'CURRENT PERIOD');
2965 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
2966 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
2967 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_new_fa_dist.distribution_id );
2968 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
2969 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
2970 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances_new.adjustment_cost );
2971 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances_new.net_book_value);
2972 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances_new.reval_reserve_cost);
2973 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances_new.reval_reserve_backlog);
2974 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances_new.reval_reserve_gen_fund);
2975 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances_new.reval_reserve_net);
2976 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances_new.operating_acct_cost);
2977 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances_new.operating_acct_backlog);
2978 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances_new.operating_acct_net);
2979 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
2980 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances_new.deprn_period);
2981 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances_new.deprn_ytd);
2982 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances_new.deprn_reserve);
2983 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances_new.deprn_reserve_backlog);
2984 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances_new.general_fund_per);
2985 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances_new.general_fund_acc);
2986 debug( g_state_level,l_path_name,'X_last_reval_date =>'|| l_detail_balances.last_reval_date );
2987 debug( g_state_level,l_path_name,'X_current_reval_factor =>'|| l_detail_balances.current_reval_factor );
2988 debug( g_state_level,l_path_name,'X_cumulative_reval_factor=>'|| l_detail_balances.cumulative_reval_factor );
2989 debug( g_state_level,l_path_name,'X_active_flag =>'|| l_detail_balances.active_flag);
2990
2991
2992
2993 debug( g_state_level,l_path_name,'end insert det bals for new dist');
2994
2995 IF (g_is_first_period) THEN
2996 l_fa_deprn.deprn_ytd := 0;
2997 ELSE
2998 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
2999 END IF;
3000
3001 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
3002 l_rowid:=NULL;
3003 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3004 x_rowid => l_rowid,
3005 x_book_type_code => p_book_type_code,
3006 x_asset_id => p_asset_id,
3007 x_period_counter => g_prd_rec.period_counter,
3008 x_adjustment_id => l_retirement_adjustment_id,
3009 x_distribution_id => l_new_fa_dist.distribution_id,
3010 x_deprn_period => l_fa_deprn.deprn_period,
3011 x_deprn_ytd => 0,
3012 x_deprn_reserve => l_fa_total_new_acc_deprn,
3013 x_active_flag => NULL,
3014 x_mode => 'R');
3015
3016 END IF ; -- for new distributon cerated
3017
3018
3019 ELSE -- active distribution not impacted by partial unit retirement
3020 debug( g_state_level,l_path_name,'Non Imapcted Active distributions insertion');
3021 CLOSE c_get_impacted_dist;
3022 /* Roll forward YTD records */
3023 IF (g_is_first_period) THEN
3024 l_fa_deprn_ytd := 0;
3025 ELSE
3026 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
3027 END IF;
3028
3029 l_asset_units_count := l_asset_units_count + l_units_per_dist.units_assigned ;
3030
3031 l_rowid := NULL ;
3032 igi_iac_det_balances_pkg.insert_row(
3033 X_rowid => l_rowid ,
3034 X_adjustment_id => l_retirement_adjustment_id ,
3035 X_asset_id => P_Asset_Id,
3036 X_distribution_id => l_detail_balances.distribution_id ,
3037 X_book_type_code => P_Book_Type_Code ,
3038 X_period_counter => g_prd_rec.period_counter,
3039 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3040 X_net_book_value => l_detail_balances.net_book_value,
3041 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3042 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3043 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3044 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3045 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3046 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3047 X_operating_acct_net => l_detail_balances.operating_acct_net,
3048 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3049 X_deprn_period => l_detail_balances.deprn_period,
3050 X_deprn_ytd => l_detail_balances.deprn_ytd,
3051 X_deprn_reserve => l_detail_balances.deprn_reserve,
3052 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3053 X_general_fund_per => l_detail_balances.general_fund_per,
3054 X_general_fund_acc => l_detail_balances.general_fund_acc,
3055 X_last_reval_date => l_detail_balances.last_reval_date ,
3056 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3057 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3058 X_active_flag => l_detail_balances.active_flag ,
3059 X_mode => 'R') ;
3060
3061
3062 l_rowid := NULL ;
3063 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3064 x_rowid => g_rowid,
3065 x_book_type_code => p_book_type_code,
3066 x_asset_id => p_asset_id,
3067 x_period_counter => g_prd_rec.period_counter,
3068 x_adjustment_id => l_retirement_adjustment_id,
3069 x_distribution_id => l_fa_deprn.distribution_id,
3070 x_deprn_period => l_fa_deprn.deprn_period,
3071 x_deprn_ytd => l_fa_deprn_ytd,
3072 x_deprn_reserve => l_fa_deprn.deprn_reserve,
3073 x_active_flag => l_fa_deprn.active_flag,
3074 x_mode => 'R');
3075
3076 --asset total;
3077 l_asset_balances.net_book_value :=l_asset_balances.net_book_value+l_detail_balances.net_book_value;
3078 l_asset_balances.adjusted_cost :=l_asset_balances.adjusted_cost +l_detail_balances.adjustment_cost;
3079 l_asset_balances.operating_acct :=l_asset_balances.operating_acct+ l_detail_balances.operating_acct_net;
3080 l_asset_balances.reval_reserve :=l_asset_balances.reval_reserve +l_detail_balances.reval_reserve_net;
3081 l_asset_balances.deprn_amount :=l_asset_balances.deprn_amount +l_detail_balances.deprn_period;
3082 l_asset_balances.deprn_reserve :=l_asset_balances.deprn_reserve +l_detail_balances.deprn_reserve;
3083 l_asset_balances.backlog_deprn_reserve:=l_asset_balances.backlog_deprn_reserve+l_detail_balances.deprn_reserve_backlog;
3084 l_asset_balances.general_fund :=l_asset_balances.general_fund +l_detail_balances.general_fund_acc;
3085
3086
3087 END IF ; -- if active_flag is Null and not impacted partial unot retirement
3088
3089 ELSE -- Inactive distributions IF active_flag is NULL. i.e. following code for active_flag = 'N'
3090
3091 debug( g_state_level,l_path_name,'Non Imapcted InActive distributions insertion');
3092 /* Roll forward YTD records */
3093 IF (g_is_first_period) THEN
3094 l_fa_deprn_ytd := 0;
3095 ELSE
3096 l_fa_deprn_ytd := l_fa_deprn.deprn_ytd;
3097 END IF;
3098
3099 l_rowid:=NULL ;
3100 igi_iac_det_balances_pkg.insert_row(
3101 X_rowid => l_rowid ,
3102 X_adjustment_id => l_retirement_adjustment_id ,
3103 X_asset_id => P_Asset_Id,
3104 X_distribution_id => l_detail_balances.distribution_id ,
3105 X_book_type_code => P_Book_Type_Code ,
3106 X_period_counter => g_prd_rec.period_counter,
3107 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3108 X_net_book_value => l_detail_balances.net_book_value,
3109 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3110 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3111 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3112 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3113 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3114 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3115 X_operating_acct_net => l_detail_balances.operating_acct_net,
3116 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3117 X_deprn_period => l_detail_balances.deprn_period,
3118 X_deprn_ytd => l_detail_balances.deprn_ytd,
3119 X_deprn_reserve => l_detail_balances.deprn_reserve,
3120 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3121 X_general_fund_per => l_detail_balances.general_fund_per,
3122 X_general_fund_acc => l_detail_balances.general_fund_acc,
3123 X_last_reval_date => l_detail_balances.last_reval_date ,
3124 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3125 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3126 X_active_flag => l_detail_balances.active_flag ,
3127 X_mode => 'R') ;
3128
3129
3130 l_rowid:=NULL;
3131 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3132 x_rowid => l_rowid,
3133 x_book_type_code => p_book_type_code,
3134 x_asset_id => p_asset_id,
3135 x_period_counter => g_prd_rec.period_counter,
3136 x_adjustment_id => l_retirement_adjustment_id,
3137 x_distribution_id => l_fa_deprn.distribution_id,
3138 x_deprn_period => l_fa_deprn.deprn_period,
3139 x_deprn_ytd => l_fa_deprn_ytd,
3140 x_deprn_reserve => l_fa_deprn.deprn_reserve,
3141 x_active_flag => l_fa_deprn.active_flag,
3142 x_mode => 'R'
3143 );
3144 END IF ; -- if active_flag is Null
3145
3146 END LOOP ; -- g_detail_balances
3147 debug( g_state_level, l_path_name, 'end loop');
3148
3149 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, g_prd_rec.period_counter);
3150
3151 FETCH c_asset_balances INTO l_asset_balances_rec ;
3152
3153 IF c_asset_balances%NOTFOUND THEN
3154 CLOSE c_asset_balances ;
3155
3156 OPEN c_previous_per(l_last_active_adj_id) ;
3157 FETCH c_previous_per INTO l_previous_per ;
3158 IF c_previous_per%NOTFOUND THEN
3159 CLOSE c_previous_per;
3160 RAISE NO_DATA_FOUND ;
3161 END IF ;
3162 CLOSE c_previous_per ;
3163
3164 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, l_previous_per ) ;
3165 FETCH c_asset_balances INTO l_asset_balances_rec ;
3166 IF c_asset_balances%NOTFOUND THEN
3167 CLOSE c_asset_balances;
3168
3169 --Begin Fix for Bug 5049536
3170 SELECT max(period_counter)
3171 INTO l_max_period_counter
3172 FROM igi_iac_asset_balances
3173 WHERE asset_id = P_Asset_Id
3174 AND book_type_code = P_Book_Type_Code;
3175
3176 OPEN c_asset_balances(P_Asset_Id, P_Book_Type_Code, l_max_period_counter) ;
3177 FETCH c_asset_balances INTO l_asset_balances_rec ;
3178 --RAISE e_no_asset_bals ;
3179 --End fix for Bug 5049536
3180 END IF ;
3181
3182 igi_iac_asset_balances_pkg.insert_row(
3183 X_rowid => l_rowid ,
3184 X_asset_id => p_asset_id,
3185 X_book_type_code => p_book_type_code ,
3186 X_period_counter => g_prd_rec.period_counter ,
3187 X_net_book_value => l_asset_balances.net_book_value ,
3188 X_adjusted_cost => l_asset_balances.adjusted_cost ,
3189 X_operating_acct => l_asset_balances.operating_acct ,
3190 X_reval_reserve => l_asset_balances.reval_reserve ,
3191 X_deprn_amount => l_asset_balances.deprn_amount,
3192 X_deprn_reserve => l_asset_balances.deprn_reserve ,
3193 X_backlog_deprn_reserve => l_asset_balances.backlog_deprn_reserve ,
3194 X_general_fund => l_asset_balances.general_fund ,
3195 X_last_reval_date => l_asset_balances_rec.last_reval_date ,
3196 X_current_reval_factor => l_asset_balances_rec.current_reval_factor,
3197 X_cumulative_reval_factor => l_asset_balances_rec.cumulative_reval_factor,
3198 X_mode => 'R') ;
3199
3200
3201
3202 ELSE
3203 igi_iac_asset_balances_pkg.update_row(
3204 X_asset_id => p_asset_id,
3205 X_book_type_code => p_book_type_code ,
3206 X_period_counter => g_prd_rec.period_counter ,
3207 X_net_book_value => l_asset_balances.net_book_value ,
3208 X_adjusted_cost => l_asset_balances.adjusted_cost ,
3209 X_operating_acct => l_asset_balances.operating_acct ,
3210 X_reval_reserve => l_asset_balances.reval_reserve ,
3211 X_deprn_amount => l_asset_balances.deprn_amount,
3212 X_deprn_reserve => l_asset_balances.deprn_reserve ,
3213 X_backlog_deprn_reserve => l_asset_balances.backlog_deprn_reserve ,
3214 X_general_fund => l_asset_balances.general_fund ,
3215 X_last_reval_date => l_asset_balances_rec.last_reval_date ,
3216 X_current_reval_factor => l_asset_balances_rec.current_reval_factor,
3217 X_cumulative_reval_factor => l_asset_balances_rec.cumulative_reval_factor,
3218 X_mode => 'R') ;
3219
3220 END IF;
3221 CLOSE c_asset_balances ;
3222
3223 RETURN TRUE;
3224
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 debug( g_state_level,l_path_name,'Error in Processing Unit Retirement');
3228 FA_SRVR_MSG.add_sql_error(Calling_Fn => g_calling_fn);
3229 RETURN FALSE ;
3230
3231 END unit_retirement;
3232
3233 FUNCTION Prior_Unit_Retirement (P_Asset_Id IN NUMBER ,
3234 P_Book_Type_Code IN VARCHAR2 ,
3235 P_Retirement_Id IN NUMBER ,
3236 P_retirement_type IN VARCHAR2,
3237 p_retirement_period_type IN VARCHAR2,
3238 P_prior_period IN NUMBER,
3239 P_Current_period IN NUMBER,
3240 P_Event_Id IN NUMBER ) --R12 uptake
3241 RETURN BOOLEAN IS
3242 l_rowid ROWID;
3243 l_asset_balances igi_iac_asset_balances%ROWTYPE;
3244 l_asset_balances_rec igi_iac_asset_balances%ROWTYPE;
3245 l_detail_balances igi_iac_det_balances%ROWTYPE;
3246 l_detail_balances_new igi_iac_det_balances%ROWTYPE;
3247 l_detail_balances_total_old igi_iac_det_balances%ROWTYPE;
3248 l_detail_balances_retire igi_iac_det_balances%ROWTYPE;
3249 l_detail_balances_retire_unrnd igi_iac_det_balances%ROWTYPE;
3250 l_detail_balances_rnd_tot igi_iac_det_balances%ROWTYPE;
3251 l_fa_deprn igi_iac_fa_deprn%ROWTYPE;
3252 l_units_per_dist c_units_per_dist%ROWTYPE;
3253 l_cost_account_ccid NUMBER ;
3254 l_acc_deprn_account_ccid NUMBER ;
3255 l_reval_rsv_account_ccid NUMBER ;
3256 l_backlog_account_ccid NUMBER ;
3257 l_nbv_retired_account_ccid NUMBER ;
3258 l_reval_rsv_ret_acct_ccid NUMBER ;
3259 l_deprn_exp_account_ccid NUMBER ;
3260 l_account_gen_fund_ccid NUMBER;
3261 l_new_units NUMBER ;
3262 l_new_distribution NUMBER ;
3263 l_units_before NUMBER ;
3264 l_units_after NUMBER ;
3265 l_ret BOOLEAN ;
3266 l_total_asset_units NUMBER ;
3267 l_asset_units_count NUMBER ;
3268 l_previous_per NUMBER ;
3269 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE ;
3270 l_last_active_adj_id igi_iac_transaction_headers.adjustment_id%TYPE ;
3271 l_db_op_acct_ytd igi_iac_det_balances.operating_acct_ytd%TYPE;
3272 l_db_deprn_ytd igi_iac_det_balances.deprn_ytd%TYPE;
3273 l_fa_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
3274 l_fa_tot_round_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
3275 l_fa_unround_deprn_prd igi_iac_fa_deprn.deprn_period%TYPE;
3276 l_fa_total_old_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
3277 l_fa_retire_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
3278 l_fa_tot_round_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
3279 l_fa_unround_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
3280 l_fa_total_new_acc_deprn igi_iac_fa_deprn.deprn_reserve%TYPE;
3281 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3282 l_fa_tot_round_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3283 l_fa_unround_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3284 l_fa_total_new_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
3285 l_fa_total_new_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3286 l_fa_total_old_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
3287 l_fa_total_old_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3288 l_fa_retire_deprn_prd igi_iac_fa_deprn.deprn_ytd%TYPE;
3289 l_fa_retire_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
3290 l_op_exp_ccid NUMBER;
3291 l_Transaction_Type_Code igi_iac_transaction_headers.transaction_type_code%TYPE;
3292 l_Transaction_Id igi_iac_transaction_headers.transaction_header_id%TYPE;
3293 l_Mass_Reference_ID igi_iac_transaction_headers.mass_reference_id%TYPE;
3294 l_Adjustment_Status igi_iac_transaction_headers.adjustment_status%TYPE;
3295 l_adjustment_id_out igi_iac_adjustments.adjustment_id%TYPE;
3296 l_retirement_adjustment_id NUMBER;
3297 l_path_name varchar2(200);
3298 l_check_revaluations c_check_revaluations%ROWTYPE;
3299 l_all_occ_reval c_get_all_occ_reval%ROWTYPE;
3300 l_retire_amount Number;
3301 l_check_depreciations c_check_depreciations%ROWTYPE;
3302 l_all_prd_reval c_get_all_prd_reval%ROWTYPE;
3303 l_retirement_factor number;
3304 l_impact_fa_dist c_get_impacted_dist%ROWTYPE;
3305 l_new_fa_dist c_get_new_dist%ROWTYPE;
3306 BEGIN
3307
3308 l_path_name:=g_path_name ||'.Prior_Period_Unit_Retirement';
3309 /* Initialize Asset total balance variables */
3310 ------------------------
3311 l_asset_balances.Asset_id :=P_Asset_Id ;
3312 l_asset_balances.book_type_code :=P_Book_Type_Code;
3313 l_asset_balances.period_counter :=P_Current_period;
3314 l_asset_balances.net_book_value :=0;
3315 l_asset_balances.adjusted_cost :=0;
3316 l_asset_balances.operating_acct :=0;
3317 l_asset_balances.reval_reserve :=0;
3318 l_asset_balances.deprn_amount :=0;
3319 l_asset_balances.deprn_reserve :=0;
3320 l_asset_balances.backlog_deprn_reserve:=0;
3321 l_asset_balances.general_fund :=0;
3322
3323
3324 l_Transaction_Type_Code := NULL;
3325 l_Transaction_Id := NULL;
3326 l_Mass_Reference_ID := NULL;
3327 l_adjustment_id_out := NULL;
3328 l_prev_adjustment_id := NULL;
3329 l_Adjustment_Status := NULL;
3330 l_retirement_adjustment_id :=NULL;
3331
3332 debug(g_state_level,l_path_name,'Asset ID '||P_Asset_Id);
3333 -- get the latest tranaction for the asset id
3334
3335 IF NOT (igi_iac_common_utils.get_latest_transaction(P_Book_Type_Code,
3336 P_Asset_Id,
3337 l_Transaction_Type_Code,
3338 l_Transaction_Id,
3339 l_Mass_Reference_ID ,
3340 l_adjustment_id_out,
3341 l_prev_adjustment_id,
3342 l_Adjustment_Status )) THEN
3343 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path_name,'*** Error in fetching the latest transaction');
3344 RETURN FALSE;
3345 END IF;
3346
3347 debug(g_state_level,l_path_name,'got latest transaction');
3348 l_last_active_adj_id := l_prev_adjustment_id ;
3349 debug( g_state_level,l_path_name,'not reval in preview');
3350
3351 ---check for revauations if exits between the prior perod and current period
3352 -- Start revaluation
3353 OPEN c_check_revaluations(P_asset_id,p_book_type_code,
3354 p_prior_period,p_current_period);
3355 FETCH c_check_revaluations INTO l_check_revaluations;
3356 IF c_check_revaluations%FOUND THEN -- revlautions found in betweem
3357 l_path_name :=l_path_name ||'.Reval Reversal' ;
3358
3359 --- create a new transaction header for Revlaution retirement
3360 l_rowid:=NULL;
3361 l_retirement_adjustment_id :=NULL;
3362 igi_iac_trans_headers_pkg.insert_row(
3363 X_rowid => l_rowid ,
3364 X_adjustment_id => l_retirement_adjustment_id ,
3365 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
3366 X_adjustment_id_out => NULL ,
3367 X_transaction_type_code => 'REVALUATION',
3368 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
3369 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
3370 X_transaction_sub_type => 'RETIREMENT',
3371 X_book_type_code => P_Book_Type_Code,
3372 X_asset_id => p_asset_id ,
3373 X_category_id => g_asset_category_id,
3374 X_adj_deprn_start_date => NULL,
3375 X_revaluation_type_flag => NULL,
3376 X_adjustment_status => 'COMPLETE' ,
3377 X_period_counter => P_Current_period,
3378 X_mode =>'R',
3379 X_event_id => P_Event_Id) ;
3380
3381 debug( g_state_level, l_path_name,'inserted trans_headers record');
3382
3383 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
3384 l_retirement_adjustment_id,
3385 'R') ;
3386
3387 debug( g_state_level,l_path_name,'updated old trans_headers record');
3388
3389 --get all adjustments for occasional revaluation between current period and
3390 -- retire period
3391 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
3392
3393 debug( g_state_level,l_path_name,'Inside loop ');
3394 debug( g_state_level,l_path_name,'Detail balances loop: '|| l_detail_balances.distribution_id);
3395
3396 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
3397 l_detail_balances.distribution_id,
3398 l_detail_balances.period_counter);
3399 FETCH c_fa_deprn INTO l_fa_deprn;
3400 IF c_fa_deprn%NOTFOUND THEN
3401 CLOSE c_fa_deprn;
3402 RETURN FALSE;
3403 END IF;
3404 CLOSE c_fa_deprn;
3405
3406 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
3407
3408 debug( g_state_level,l_path_name,'Active dist ');
3409 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
3410 OPEN c_units_per_dist(P_Asset_Id, P_Book_Type_Code, l_detail_balances.distribution_id) ;
3411 debug( g_state_level, l_path_name,'opened c_units_per_dist');
3412 FETCH c_units_per_dist INTO l_units_per_dist ;
3413 IF c_units_per_dist%NOTFOUND THEN
3414 CLOSE c_units_per_dist;
3415 debug( g_state_level,l_path_name,'units per dist not found');
3416 RAISE NO_DATA_FOUND;
3417 END IF ;
3418 CLOSE c_units_per_dist ;
3419
3420
3421 -- find the impacted distribution because of the partial unit retirement
3422 OPEN c_get_impacted_dist(P_Asset_Id,P_Retirement_Id,l_detail_balances.distribution_id);
3423 FETCH c_get_impacted_dist INTO l_impact_fa_dist;
3424 IF c_get_impacted_dist%FOUND THEN -- impacted by partial nit retirement
3425 --CLOSE c_get_impacted_dist;
3426 --- distribtion impacted by partial unkit retirement
3427 l_detail_balances_retire.adjustment_cost :=0 ;
3428 l_detail_balances_retire.reval_reserve_cost :=0 ;
3429 l_detail_balances_retire.deprn_reserve :=0 ;
3430 l_detail_balances_retire.deprn_reserve_backlog :=0 ;
3431 l_detail_balances_retire.reval_reserve_net :=0 ;
3432 l_detail_balances_retire.deprn_period :=0 ;
3433 l_detail_balances_retire.general_fund_acc :=0 ;
3434 l_detail_balances_retire.general_fund_per :=0 ;
3435 l_detail_balances_retire.reval_reserve_gen_fund :=0 ;
3436 l_detail_balances_retire.operating_acct_backlog :=0;
3437 l_detail_balances_retire.operating_acct_cost :=0;
3438 l_detail_balances_retire.operating_acct_net :=0;
3439 l_detail_balances_retire.reval_reserve_backlog :=0;
3440 l_detail_balances_retire.deprn_ytd :=0;
3441
3442 -- get the impacted new distribution create for the retire distribution
3443 l_retirement_factor := 1;
3444 IF l_impact_fa_dist.transaction_units <> l_impact_fa_dist.units_assigned THEN
3445
3446 OPEN c_get_new_dist(P_Asset_Id,P_Retirement_Id,l_impact_fa_dist.code_combination_id,
3447 ABS(l_impact_fa_dist.units_assigned + l_impact_fa_dist.transaction_units),l_impact_fa_dist.location_id,
3448 NVL(l_impact_fa_dist.assigned_to,-1));
3449 FETCH C_get_new_dist INTO l_new_fa_dist;
3450 IF C_get_new_dist%FOUND THEN
3451 l_retirement_factor := - NVL(l_impact_fa_dist.transaction_units,0)/ l_impact_fa_dist.units_assigned;
3452 l_units_per_dist.units_assigned :=l_new_fa_dist.units_assigned;
3453 END IF;
3454 CLOSE C_get_new_dist;
3455 END IF;
3456
3457
3458 FOR l_all_occ_reval IN c_get_all_occ_reval(p_asset_id,p_book_type_code,
3459 P_prior_period,
3460 p_current_period,
3461 l_detail_balances.distribution_id ) LOOP
3462
3463
3464 l_retire_amount:=l_all_occ_reval.amount;
3465 --l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code);
3466 l_rowid := NULL ;
3467 igi_iac_adjustments_pkg.insert_row(
3468 X_rowid => l_rowid ,
3469 X_adjustment_id => l_retirement_adjustment_id ,
3470 X_book_type_code => P_Book_Type_Code ,
3471 X_code_combination_id => l_all_occ_reval.code_combination_id,
3472 X_set_of_books_id => g_sob_id ,
3473 X_dr_cr_flag => 'DR',
3474 X_amount => l_all_occ_reval.amount,
3475 X_adjustment_type => l_all_occ_reval.adjustment_type,
3476 X_transfer_to_gl_flag => 'Y',
3477 X_units_assigned => l_all_occ_reval.units_assigned ,
3478 X_asset_id => p_Asset_Id ,
3479 X_distribution_id => l_detail_balances.distribution_id ,
3480 X_period_counter => g_prd_rec.period_counter,
3481 X_adjustment_offset_type =>l_all_occ_reval.adjustment_offset_type,
3482 X_report_ccid => l_all_occ_reval.report_ccid,
3483 X_mode => 'R',
3484 X_event_id => P_Event_Id ) ;
3485
3486 debug( g_state_level,l_path_name,'adjustment type '|| l_all_occ_reval.adjustment_type);
3487 debug( g_state_level,l_path_name,'amount ' || l_all_occ_reval.amount);
3488 debug( g_state_level,l_path_name,'distribution ' || l_detail_balances.distribution_id);
3489
3490
3491 IF l_retirement_factor <> 1 THEN
3492
3493 l_retire_amount:=l_all_occ_reval.amount * l_retirement_factor ;
3494 l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code);
3495
3496 l_rowid := NULL ;
3497 igi_iac_adjustments_pkg.insert_row(
3498 X_rowid => l_rowid ,
3499 X_adjustment_id => l_retirement_adjustment_id ,
3500 X_book_type_code => P_Book_Type_Code ,
3501 X_code_combination_id => l_all_occ_reval.code_combination_id,
3502 X_set_of_books_id => g_sob_id ,
3503 X_dr_cr_flag => 'CR',
3504 X_amount => l_retire_amount,
3505 X_adjustment_type => l_all_occ_reval.adjustment_type,
3506 X_transfer_to_gl_flag => 'Y',
3507 X_units_assigned => l_new_fa_dist.units_assigned ,
3508 X_asset_id => p_Asset_Id ,
3509 X_distribution_id => l_new_fa_dist.distribution_id ,
3510 X_period_counter => g_prd_rec.period_counter,
3511 X_adjustment_offset_type =>l_all_occ_reval.adjustment_offset_type,
3512 X_report_ccid => l_all_occ_reval.report_ccid,
3513 X_mode => 'R',
3514 X_event_id => P_Event_Id ) ;
3515 debug( g_state_level,l_path_name,'adjustment type '|| l_all_occ_reval.adjustment_type);
3516 debug( g_state_level,l_path_name,'amount ' || l_retire_amount);
3517 debug( g_state_level,l_path_name,'distribution ' || l_new_fa_dist.distribution_id);
3518
3519 END IF;
3520
3521 IF l_all_occ_reval.adjustment_type = 'COST' THEN
3522 l_detail_balances_retire.adjustment_cost:=l_detail_balances_retire.adjustment_cost + l_retire_amount;
3523 END IF;
3524 IF l_all_occ_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
3525 l_detail_balances_retire.reval_reserve_cost:= l_detail_balances_retire.reval_reserve_cost + l_retire_amount;
3526 END IF;
3527 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
3528 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog - l_retire_amount;
3529 END IF;
3530 IF l_all_occ_reval.adjustment_type = 'GENERAL FUND' THEN
3531 l_detail_balances_retire.general_fund_acc := l_detail_balances_retire.general_fund_acc - l_retire_amount;
3532 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund - l_retire_amount;
3533 END IF;
3534 IF l_all_occ_reval.adjustment_type = 'REVAL RESERVE' THEN
3535 l_detail_balances_retire.reval_reserve_net := l_detail_balances_retire.reval_reserve_net - l_retire_amount;
3536 END IF;
3537 IF l_all_occ_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
3538 l_detail_balances_retire.operating_acct_cost:=l_detail_balances_retire.operating_acct_cost + l_retire_amount;
3539 END IF;
3540 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
3541 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances_retire.operating_acct_backlog - l_retire_amount;
3542 END IF;
3543 IF l_all_occ_reval.adjustment_type = 'OP EXPENSE' THEN
3544 l_detail_balances_retire.operating_acct_net :=l_detail_balances_retire.operating_acct_net - l_retire_amount;
3545 END IF;
3546 IF l_all_occ_reval.adjustment_type = 'RESERVE' THEN
3547 l_detail_balances_retire.deprn_reserve := l_detail_balances_retire.deprn_reserve - l_retire_amount;
3548 END IF;
3549 IF l_all_occ_reval.adjustment_type = 'BL RESERVE' THEN
3550 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog - l_retire_amount;
3551 END IF;
3552
3553 END LOOP; -- adjustment reversal
3554
3555 /* Calculate new totals */
3556 l_detail_balances.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_retire.adjustment_cost;
3557 l_detail_balances.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_retire.reval_reserve_cost;
3558 l_detail_balances.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_retire.deprn_reserve ;
3559 l_detail_balances.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_retire.deprn_reserve_backlog ;
3560 l_detail_balances.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_retire.reval_reserve_net ;
3561 l_detail_balances.deprn_period := l_detail_balances.deprn_period + l_detail_balances_retire.deprn_period;
3562 l_detail_balances.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_retire.general_fund_acc;
3563 l_detail_balances.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_retire.general_fund_per;
3564 l_detail_balances.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_retire.reval_reserve_gen_fund;
3565 l_detail_balances.operating_acct_backlog := l_detail_balances.operating_acct_backlog + l_detail_balances_retire.operating_acct_backlog;
3566 l_detail_balances.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_retire.operating_acct_cost;
3567 l_detail_balances.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_retire.operating_acct_net ;
3568 l_detail_balances.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_retire.reval_reserve_backlog;
3569 l_detail_balances.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_retire.deprn_ytd;
3570
3571 l_detail_balances.net_book_value := l_detail_balances.adjustment_cost - l_detail_balances.deprn_reserve -l_detail_balances.deprn_reserve_backlog ;
3572
3573 l_rowid := NULL ;
3574 igi_iac_det_balances_pkg.insert_row(
3575 X_rowid => l_rowid ,
3576 X_adjustment_id => l_retirement_adjustment_id ,
3577 X_asset_id => P_Asset_Id ,
3578 X_distribution_id => l_detail_balances.distribution_id,
3579 X_book_type_code => P_Book_Type_Code ,
3580 X_period_counter => g_prd_rec.period_counter,
3581 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3582 X_net_book_value => l_detail_balances.net_book_value,
3583 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3584 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3585 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3586 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3587 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3588 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3589 X_operating_acct_net => l_detail_balances.operating_acct_net,
3590 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3591 X_deprn_period => l_detail_balances.deprn_period,
3592 X_deprn_ytd => l_detail_balances.DEPRN_YTD,
3593 X_deprn_reserve => l_detail_balances.deprn_reserve,
3594 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3595 X_general_fund_per => l_detail_balances.general_fund_per,
3596 X_general_fund_acc => l_detail_balances.general_fund_acc,
3597 X_last_reval_date => l_detail_balances.last_reval_date ,
3598 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3599 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3600 X_active_flag => l_detail_balances.active_flag,
3601 X_mode => 'R') ;
3602 debug( g_state_level,l_path_name,'end insert det bals for new dist');
3603 debug( g_state_level,l_path_name,'REVALUATION REVERSAL');
3604 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
3605 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
3606 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
3607 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
3608 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
3609 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances.adjustment_cost);
3610 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances.net_book_value);
3611 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances.reval_reserve_cost);
3612 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances.reval_reserve_backlog);
3613 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances.reval_reserve_gen_fund);
3614 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances.reval_reserve_net);
3615 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances.operating_acct_cost);
3616 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances.operating_acct_backlog);
3617 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances.operating_acct_net);
3618 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
3619 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances.deprn_period);
3620 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances.deprn_ytd);
3621 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances.deprn_reserve);
3622 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances.deprn_reserve_backlog);
3623 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances.general_fund_per);
3624 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances.general_fund_acc);
3625
3626 -- insert into igi_iac_fa_deprn with the new adjustment_id
3627 l_rowid:=NULL;
3628 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3629 x_rowid => l_rowid,
3630 x_book_type_code => p_book_type_code,
3631 x_asset_id => p_asset_id,
3632 x_period_counter => g_prd_rec.period_counter,
3633 x_adjustment_id => l_retirement_adjustment_id,
3634 x_distribution_id => l_detail_balances.distribution_id,
3635 x_deprn_period => l_fa_deprn.deprn_period,
3636 x_deprn_ytd => l_fa_deprn.deprn_ytd,
3637 x_deprn_reserve => l_fa_deprn.deprn_reserve,
3638 x_active_flag => NULL,
3639 x_mode => 'R');
3640
3641 debug(g_state_level,l_path_name,'Inserted FA deprn record for dist'||l_detail_balances.distribution_id );
3642
3643 ELSE -- not impacted by retiretment -- active distribution
3644
3645 l_rowid := NULL ;
3646 igi_iac_det_balances_pkg.insert_row(
3647 X_rowid => l_rowid ,
3648 X_adjustment_id => l_retirement_adjustment_id ,
3649 X_asset_id => P_Asset_Id ,
3650 X_distribution_id => l_detail_balances.distribution_id ,
3651 X_book_type_code => P_Book_Type_Code ,
3652 X_period_counter => g_prd_rec.period_counter,
3653 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3654 X_net_book_value => l_detail_balances.net_book_value,
3655 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3656 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3657 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3658 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3659 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3660 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3661 X_operating_acct_net => l_detail_balances.operating_acct_net,
3662 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3663 X_deprn_period => l_detail_balances.deprn_period,
3664 X_deprn_ytd => l_detail_balances.deprn_ytd,
3665 X_deprn_reserve => l_detail_balances.deprn_reserve,
3666 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3667 X_general_fund_per => l_detail_balances.general_fund_per,
3668 X_general_fund_acc => l_detail_balances.general_fund_acc,
3669 X_last_reval_date => l_detail_balances.last_reval_date ,
3670 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3671 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3672 X_active_flag => l_detail_balances.active_flag,
3673 X_mode => 'R') ;
3674
3675
3676
3677 debug( g_state_level,l_path_name,'end insert det bals..not impacted distribution');
3678 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
3679 l_rowid:=NULL;
3680 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3681 x_rowid => l_rowid,
3682 x_book_type_code => p_book_type_code,
3683 x_asset_id => p_asset_id,
3684 x_period_counter => g_prd_rec.period_counter,
3685 x_adjustment_id => l_retirement_adjustment_id,
3686 x_distribution_id => l_fa_deprn.distribution_id,
3687 x_deprn_period => l_fa_deprn.deprn_period,
3688 x_deprn_ytd => l_fa_deprn.deprn_ytd,
3689 x_deprn_reserve => l_fa_deprn.deprn_reserve,
3690 x_active_flag => NULL,
3691 x_mode => 'R' );
3692
3693 END IF; -- impacted distribution
3694 CLOSE c_get_impacted_dist;
3695 ELSE -- Inactive distributions IF active_flag is not NULL. i.e. following code for active_flag = 'N'
3696
3697 debug( g_state_level,l_path_name,'Inactive distributions');
3698 l_rowid := NULL ;
3699 igi_iac_det_balances_pkg.insert_row(
3700 X_rowid => l_rowid ,
3701 X_adjustment_id => l_retirement_adjustment_id ,
3702 X_asset_id => P_Asset_Id,
3703 X_distribution_id => l_detail_balances.distribution_id ,
3704 X_book_type_code => P_Book_Type_Code ,
3705 X_period_counter => g_prd_rec.period_counter,
3706 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3707 X_net_book_value => l_detail_balances.net_book_value,
3708 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3709 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3710 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3711 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3712 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3713 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3714 X_operating_acct_net => l_detail_balances.operating_acct_net,
3715 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3716 X_deprn_period => l_detail_balances.deprn_period,
3717 X_deprn_ytd => l_detail_balances.deprn_ytd,
3718 X_deprn_reserve => l_detail_balances.deprn_reserve,
3719 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3720 X_general_fund_per => l_detail_balances.general_fund_per,
3721 X_general_fund_acc => l_detail_balances.general_fund_acc,
3722 X_last_reval_date => l_detail_balances.last_reval_date ,
3723 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3724 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3725 X_active_flag => l_detail_balances.active_flag ,
3726 X_mode => 'R') ;
3727
3728 l_rowid := NULL ;
3729 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
3730 x_rowid => g_rowid,
3731 x_book_type_code => p_book_type_code,
3732 x_asset_id => p_asset_id,
3733 x_period_counter => g_prd_rec.period_counter,
3734 x_adjustment_id => l_retirement_adjustment_id,
3735 x_distribution_id => l_fa_deprn.distribution_id,
3736 x_deprn_period => l_fa_deprn.deprn_period,
3737 x_deprn_ytd => l_fa_deprn.deprn_ytd,
3738 x_deprn_reserve => l_fa_deprn.deprn_reserve,
3739 x_active_flag => l_fa_deprn.active_flag,
3740 x_mode => 'R');
3741 END IF ; -- if active_flag is not Null
3742 END LOOP ; -- det balances
3743 debug( g_state_level,l_path_name,'End of Revaluations');
3744 l_adjustment_id_out :=l_retirement_adjustment_id;
3745 l_last_active_adj_id :=l_retirement_adjustment_id;
3746 ELSE -- no revalautions
3747 debug( g_state_level,l_path_name,'No Revaluations');
3748 NULL;
3749 END IF; --end revaluaionts
3750 CLOSE C_check_revaluations;
3751 -- End revaluation
3752
3753 -- start deprecaition reversal
3754 debug( g_state_level,l_path_name,'Start of Depreciation');
3755 OPEN c_check_depreciations(P_asset_id,p_book_type_code,
3756 p_prior_period,p_current_period);
3757 FETCH c_check_depreciations INTO l_check_depreciations;
3758 IF c_check_depreciations%FOUND THEN -- revlautions found in betweem
3759 l_path_name :=l_path_name ||'.Deprn' ;
3760 --- create a new transaction header for Revlaution retirement
3761 l_rowid:=NULL;
3762 l_retirement_adjustment_id :=NULL;
3763 igi_iac_trans_headers_pkg.insert_row(
3764 X_rowid => l_rowid ,
3765 X_adjustment_id => l_retirement_adjustment_id ,
3766 X_transaction_header_id => g_retire_rec.detail_info.transaction_header_id_in,
3767 X_adjustment_id_out => NULL ,
3768 X_transaction_type_code => 'DEPRECIATION',
3769 X_transaction_date_entered => g_fa_trx.transaction_date_entered,
3770 X_mass_refrence_id => g_fa_trx.mass_reference_id ,
3771 X_transaction_sub_type => 'RETIREMENT',
3772 X_book_type_code => P_Book_Type_Code,
3773 X_asset_id => p_asset_id ,
3774 X_category_id => g_asset_category_id,
3775 X_adj_deprn_start_date => NULL,
3776 X_revaluation_type_flag => NULL,
3777 X_adjustment_status => 'COMPLETE' ,
3778 X_period_counter => P_Current_period,
3779 X_mode =>'R',
3780 X_event_id => P_Event_Id) ;
3781
3782 debug( g_state_level, l_path_name,'inserted trans_headers record');
3783
3784 igi_iac_trans_headers_pkg.update_row(l_adjustment_id_out,
3785 l_retirement_adjustment_id,
3786 'R') ;
3787 --l_adjustment_id_out :=l_retirement_adjustment_id;
3788 ---l_last_active_adj_id :=l_retirement_adjustment_id;
3789
3790 debug( g_state_level,l_path_name,'updated old trans_headers record');
3791
3792 --get all adjustments for occasional revaluation between current period and
3793 -- retire period
3794 FOR l_detail_balances IN c_detail_balances(l_last_active_adj_id) LOOP
3795
3796 OPEN c_fa_deprn( l_detail_balances.adjustment_id,
3797 l_detail_balances.distribution_id,
3798 l_detail_balances.period_counter);
3799 FETCH c_fa_deprn INTO l_fa_deprn;
3800 IF c_fa_deprn%NOTFOUND THEN
3801 CLOSE c_fa_deprn;
3802 RETURN FALSE;
3803 END IF;
3804 CLOSE c_fa_deprn;
3805
3806 IF l_detail_balances.active_flag IS NULL THEN -- Active distributions
3807 debug( g_state_level,l_path_name,'inside loop');
3808
3809 debug( g_state_level,l_path_name,'Detail balances loop: active record dist id '|| l_detail_balances.distribution_id);
3810
3811 -- find the impacted distribution because of the partial unit retirement
3812 OPEN c_get_impacted_dist(P_Asset_Id,P_Retirement_Id,l_detail_balances.distribution_id);
3813 FETCH c_get_impacted_dist INTO l_impact_fa_dist;
3814 IF c_get_impacted_dist%FOUND THEN -- impacted by partial nit retirement
3815 --CLOSE c_get_impacted_dist;
3816 --- distribtion impacted by partial unkit retirement
3817 l_detail_balances_retire.adjustment_cost :=0 ;
3818 l_detail_balances_retire.reval_reserve_cost :=0 ;
3819 l_detail_balances_retire.deprn_reserve :=0 ;
3820 l_detail_balances_retire.deprn_reserve_backlog :=0 ;
3821 l_detail_balances_retire.reval_reserve_net :=0 ;
3822 l_detail_balances_retire.deprn_period :=0 ;
3823 l_detail_balances_retire.general_fund_acc :=0 ;
3824 l_detail_balances_retire.general_fund_per :=0 ;
3825 l_detail_balances_retire.reval_reserve_gen_fund :=0 ;
3826 l_detail_balances_retire.operating_acct_backlog :=0;
3827 l_detail_balances_retire.operating_acct_cost :=0;
3828 l_detail_balances_retire.operating_acct_net :=0;
3829 l_detail_balances_retire.reval_reserve_backlog :=0;
3830 l_detail_balances_retire.deprn_ytd :=0;
3831
3832 -- get the impacted new distribution create for the retire distribution
3833 l_retirement_factor := 1;
3834 IF l_impact_fa_dist.transaction_units <> l_impact_fa_dist.units_assigned THEN
3835
3836 OPEN c_get_new_dist(P_Asset_Id,P_Retirement_Id,l_impact_fa_dist.code_combination_id,
3837 ABS(l_impact_fa_dist.units_assigned + l_impact_fa_dist.transaction_units),l_impact_fa_dist.location_id,
3838 NVL(l_impact_fa_dist.assigned_to,-1));
3839 FETCH C_get_new_dist INTO l_new_fa_dist;
3840 IF C_get_new_dist%FOUND THEN
3841 l_retirement_factor := - NVL(l_impact_fa_dist.transaction_units,0)/ l_impact_fa_dist.units_assigned;
3842 l_units_per_dist.units_assigned :=l_new_fa_dist.units_assigned;
3843 END IF;
3844 CLOSE C_get_new_dist;
3845 END IF;
3846
3847
3848
3849 -- get the impact of the distribution -- new distribution created
3850
3851
3852
3853 FOR l_all_prd_reval IN c_get_all_prd_reval(p_asset_id,p_book_type_code,
3854 P_prior_period,
3855 p_current_period,
3856 l_detail_balances.distribution_id ) LOOP
3857
3858 l_retire_amount:=l_all_prd_reval.amount;
3859 --l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code);
3860 l_rowid := NULL ;
3861 igi_iac_adjustments_pkg.insert_row(
3862 X_rowid => l_rowid ,
3863 X_adjustment_id => l_retirement_adjustment_id ,
3864 X_book_type_code => P_Book_Type_Code ,
3865 X_code_combination_id => l_all_prd_reval.code_combination_id,
3866 X_set_of_books_id => g_sob_id ,
3867 X_dr_cr_flag => 'DR',
3868 X_amount => l_all_prd_reval.amount,
3869 X_adjustment_type => l_all_prd_reval.adjustment_type,
3870 X_transfer_to_gl_flag => 'Y',
3871 X_units_assigned => l_all_prd_reval.units_assigned ,
3872 X_asset_id => p_Asset_Id ,
3873 X_distribution_id => l_detail_balances.distribution_id ,
3874 X_period_counter => g_prd_rec.period_counter,
3875 X_adjustment_offset_type =>l_all_prd_reval.adjustment_offset_type,
3876 X_report_ccid => l_all_prd_reval.report_ccid,
3877 X_mode => 'R',
3878 X_event_id => P_Event_Id ) ;
3879
3880 debug( g_state_level,l_path_name,'adjustment type '|| l_all_prd_reval.adjustment_type);
3881 debug( g_state_level,l_path_name,'amount ' || l_all_prd_reval.amount);
3882 debug( g_state_level,l_path_name,'distribution ' || l_all_prd_reval.distribution_id);
3883
3884
3885 IF l_retirement_factor <> 1 THEN
3886
3887 l_retire_amount:=l_all_prd_reval.amount * l_retirement_factor ;
3888 l_ret:= igi_iac_common_utils.iac_round(l_retire_amount,P_Book_Type_Code);
3889
3890 l_rowid := NULL ;
3891 igi_iac_adjustments_pkg.insert_row(
3892 X_rowid => l_rowid ,
3893 X_adjustment_id => l_retirement_adjustment_id ,
3894 X_book_type_code => P_Book_Type_Code ,
3895 X_code_combination_id => l_all_prd_reval.code_combination_id,
3896 X_set_of_books_id => g_sob_id ,
3897 X_dr_cr_flag => 'CR',
3898 X_amount => l_retire_amount,
3899 X_adjustment_type => l_all_prd_reval.adjustment_type,
3900 X_transfer_to_gl_flag => 'Y',
3901 X_units_assigned => l_new_fa_dist.units_assigned ,
3902 X_asset_id => p_Asset_Id ,
3903 X_distribution_id => l_new_fa_dist.distribution_id ,
3904 X_period_counter => g_prd_rec.period_counter,
3905 X_adjustment_offset_type =>l_all_prd_reval.adjustment_offset_type,
3906 X_report_ccid => l_all_prd_reval.report_ccid,
3907 X_mode => 'R',
3908 X_event_id => P_Event_Id ) ;
3909 debug( g_state_level,l_path_name,'adjustment type '|| l_all_prd_reval.adjustment_type);
3910 debug( g_state_level,l_path_name,'amount ' || l_retire_amount);
3911 debug( g_state_level,l_path_name,'distribution ' || l_new_fa_dist.distribution_id);
3912
3913 END IF;
3914
3915 IF l_all_prd_reval.adjustment_type = 'COST' THEN
3916 l_detail_balances_retire.adjustment_cost:=l_detail_balances_retire.adjustment_cost + l_retire_amount;
3917 END IF;
3918 IF l_all_prd_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
3919 l_detail_balances_retire.reval_reserve_cost:= l_detail_balances_retire.reval_reserve_cost + l_retire_amount;
3920 END IF;
3921 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='REVAL RESERVE' THEN
3922 l_detail_balances_retire.reval_reserve_backlog := l_detail_balances_retire.reval_reserve_backlog - l_retire_amount;
3923 END IF;
3924 IF l_all_prd_reval.adjustment_type = 'GENERAL FUND' THEN
3925 l_detail_balances_retire.general_fund_acc := l_detail_balances_retire.general_fund_acc - l_retire_amount;
3926 l_detail_balances_retire.reval_reserve_gen_fund := l_detail_balances_retire.reval_reserve_gen_fund - l_retire_amount;
3927 END IF;
3928 IF l_all_prd_reval.adjustment_type = 'REVAL RESERVE' THEN
3929 l_detail_balances_retire.reval_reserve_net := l_detail_balances_retire.reval_reserve_net - l_retire_amount;
3930 END IF;
3931 IF l_all_prd_reval.adjustment_type = 'COST' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
3932 l_detail_balances_retire.operating_acct_cost:=l_detail_balances_retire.operating_acct_cost + l_retire_amount;
3933 END IF;
3934 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' AND l_all_occ_reval.adjustment_offset_type='OP EXPENSE' THEN
3935 l_detail_balances_retire.operating_acct_backlog :=l_detail_balances_retire.operating_acct_backlog - l_retire_amount;
3936 END IF;
3937 IF l_all_prd_reval.adjustment_type = 'OP EXPENSE' THEN
3938 l_detail_balances_retire.operating_acct_net :=l_detail_balances_retire.operating_acct_net - l_retire_amount;
3939 END IF;
3940 IF l_all_prd_reval.adjustment_type = 'RESERVE' THEN
3941 l_detail_balances_retire.deprn_reserve := l_detail_balances_retire.deprn_reserve - l_retire_amount;
3942 END IF;
3943 IF l_all_prd_reval.adjustment_type = 'BL RESERVE' THEN
3944 l_detail_balances_retire.deprn_reserve_backlog := l_detail_balances_retire.deprn_reserve_backlog - l_retire_amount;
3945 END IF;
3946
3947 END LOOP; -- adjustment reversal
3948 /* Calculate new totals */
3949 l_detail_balances.adjustment_cost := l_detail_balances.adjustment_cost + l_detail_balances_retire.adjustment_cost;
3950 l_detail_balances.reval_reserve_cost := l_detail_balances.reval_reserve_cost + l_detail_balances_retire.reval_reserve_cost;
3951 l_detail_balances.deprn_reserve := l_detail_balances.deprn_reserve + l_detail_balances_retire.deprn_reserve ;
3952 l_detail_balances.deprn_reserve_backlog := l_detail_balances.deprn_reserve_backlog + l_detail_balances_retire.deprn_reserve_backlog ;
3953 l_detail_balances.reval_reserve_net := l_detail_balances.reval_reserve_net + l_detail_balances_retire.reval_reserve_net ;
3954 l_detail_balances.deprn_period := l_detail_balances.deprn_period + l_detail_balances_retire.deprn_period;
3955 l_detail_balances.general_fund_acc := l_detail_balances.general_fund_acc + l_detail_balances_retire.general_fund_acc;
3956 l_detail_balances.general_fund_per := l_detail_balances.general_fund_per + l_detail_balances_retire.general_fund_per;
3957 l_detail_balances.reval_reserve_gen_fund := l_detail_balances.reval_reserve_gen_fund + l_detail_balances_retire.reval_reserve_gen_fund;
3958 l_detail_balances.operating_acct_backlog := l_detail_balances.operating_acct_backlog + l_detail_balances_retire.operating_acct_backlog;
3959 l_detail_balances.operating_acct_cost :=l_detail_balances.operating_acct_cost + l_detail_balances_retire.operating_acct_cost;
3960 l_detail_balances.operating_acct_net :=l_detail_balances.operating_acct_net + l_detail_balances_retire.operating_acct_net ;
3961 l_detail_balances.reval_reserve_backlog := l_detail_balances.reval_reserve_backlog + l_detail_balances_retire.reval_reserve_backlog;
3962 l_detail_balances.deprn_ytd := l_detail_balances.deprn_ytd + l_detail_balances_retire.deprn_ytd;
3963
3964 l_detail_balances.net_book_value := l_detail_balances.adjustment_cost - l_detail_balances.deprn_reserve -l_detail_balances.deprn_reserve_backlog ;
3965
3966 l_rowid := NULL ;
3967 igi_iac_det_balances_pkg.insert_row(
3968 X_rowid => l_rowid ,
3969 X_adjustment_id => l_retirement_adjustment_id ,
3970 X_asset_id => P_Asset_Id ,
3971 X_distribution_id => l_detail_balances.distribution_id,
3972 X_book_type_code => P_Book_Type_Code ,
3973 X_period_counter => g_prd_rec.period_counter,
3974 X_adjustment_cost => l_detail_balances.adjustment_cost ,
3975 X_net_book_value => l_detail_balances.net_book_value,
3976 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
3977 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
3978 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
3979 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
3980 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
3981 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
3982 X_operating_acct_net => l_detail_balances.operating_acct_net,
3983 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
3984 X_deprn_period => l_detail_balances.deprn_period,
3985 X_deprn_ytd => l_detail_balances.DEPRN_YTD,
3986 X_deprn_reserve => l_detail_balances.deprn_reserve,
3987 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
3988 X_general_fund_per => l_detail_balances.general_fund_per,
3989 X_general_fund_acc => l_detail_balances.general_fund_acc,
3990 X_last_reval_date => l_detail_balances.last_reval_date ,
3991 X_current_reval_factor => l_detail_balances.current_reval_factor ,
3992 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
3993 X_active_flag => l_detail_balances.active_flag,
3994 X_mode => 'R') ;
3995 debug( g_state_level,l_path_name,'end insert det bals for new dist');
3996 debug( g_state_level,l_path_name,'DEPREVALUATION REVERSAL');
3997 debug( g_state_level,l_path_name,'X_adjustment_id => '||l_retirement_adjustment_id );
3998 debug( g_state_level,l_path_name,'X_asset_id =>'|| P_Asset_Id );
3999 debug( g_state_level,l_path_name,'X_distribution_id =>'|| l_detail_balances.distribution_id );
4000 debug( g_state_level,l_path_name,'X_book_type_code =>'|| P_Book_Type_Code );
4001 debug( g_state_level,l_path_name,'X_period_counter =>'|| g_prd_rec.period_counter);
4002 debug( g_state_level,l_path_name,'X_adjustment_cost =>'|| l_detail_balances.adjustment_cost);
4003 debug( g_state_level,l_path_name,'X_net_book_value =>'|| l_detail_balances.net_book_value);
4004 debug( g_state_level,l_path_name,'X_reval_reserve_cost =>'|| l_detail_balances.reval_reserve_cost);
4005 debug( g_state_level,l_path_name,'X_reval_reserve_backlog =>'|| l_detail_balances.reval_reserve_backlog);
4006 debug( g_state_level,l_path_name,'X_reval_reserve_gen_fund =>'|| l_detail_balances.reval_reserve_gen_fund);
4007 debug( g_state_level,l_path_name,'X_reval_reserve_net =>'|| l_detail_balances.reval_reserve_net);
4008 debug( g_state_level,l_path_name,'X_operating_acct_cost =>'|| l_detail_balances.operating_acct_cost);
4009 debug( g_state_level,l_path_name,'X_operating_acct_backlog =>'|| l_detail_balances.operating_acct_backlog);
4010 debug( g_state_level,l_path_name,'X_operating_acct_net =>'|| l_detail_balances.operating_acct_net);
4011 debug( g_state_level,l_path_name,'X_operating_acct_ytd =>'|| l_detail_balances.operating_acct_ytd);
4012 debug( g_state_level,l_path_name,'X_deprn_period =>'|| l_detail_balances.deprn_period);
4013 debug( g_state_level,l_path_name,'X_deprn_ytd =>'|| l_detail_balances.deprn_ytd);
4014 debug( g_state_level,l_path_name,'X_deprn_reserve =>'|| l_detail_balances.deprn_reserve);
4015 debug( g_state_level,l_path_name,'X_deprn_reserve_backlog =>'|| l_detail_balances.deprn_reserve_backlog);
4016 debug( g_state_level,l_path_name,'X_general_fund_per =>'|| l_detail_balances.general_fund_per);
4017 debug( g_state_level,l_path_name,'X_general_fund_acc =>'|| l_detail_balances.general_fund_acc);
4018 debug( g_state_level,l_path_name,'X_last_reval_date =>'|| l_detail_balances.last_reval_date );
4019 debug( g_state_level,l_path_name,'X_current_reval_factor =>'|| l_detail_balances.current_reval_factor );
4020 debug( g_state_level,l_path_name,'X_cumulative_reval_factor=>'|| l_detail_balances.cumulative_reval_factor );
4021 debug( g_state_level,l_path_name,'X_active_flag =>'|| l_detail_balances.active_flag);
4022
4023 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
4024 l_rowid:=NULL;
4025 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
4026 x_rowid => l_rowid,
4027 x_book_type_code => p_book_type_code,
4028 x_asset_id => p_asset_id,
4029 x_period_counter => g_prd_rec.period_counter,
4030 x_adjustment_id => l_retirement_adjustment_id,
4031 x_distribution_id => l_detail_balances.distribution_id,
4032 x_deprn_period => l_fa_deprn.deprn_period,
4033 x_deprn_ytd => l_fa_deprn.deprn_ytd,
4034 x_deprn_reserve => l_fa_deprn.deprn_reserve,
4035 x_active_flag => NULL,
4036 x_mode => 'R');
4037 ELSE -- not impacted by retiretment -- active distribution
4038
4039 l_rowid := NULL ;
4040 igi_iac_det_balances_pkg.insert_row(
4041 X_rowid => l_rowid ,
4042 X_adjustment_id => l_retirement_adjustment_id ,
4043 X_asset_id => P_Asset_Id ,
4044 X_distribution_id => l_detail_balances.distribution_id ,
4045 X_book_type_code => P_Book_Type_Code ,
4046 X_period_counter => g_prd_rec.period_counter,
4047 X_adjustment_cost => l_detail_balances.adjustment_cost ,
4048 X_net_book_value => l_detail_balances.net_book_value,
4049 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
4050 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
4051 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
4052 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
4053 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
4054 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
4055 X_operating_acct_net => l_detail_balances.operating_acct_net,
4056 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
4057 X_deprn_period => l_detail_balances.deprn_period,
4058 X_deprn_ytd => l_detail_balances.deprn_ytd,
4059 X_deprn_reserve => l_detail_balances.deprn_reserve,
4060 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
4061 X_general_fund_per => l_detail_balances.general_fund_per,
4062 X_general_fund_acc => l_detail_balances.general_fund_acc,
4063 X_last_reval_date => l_detail_balances.last_reval_date ,
4064 X_current_reval_factor => l_detail_balances.current_reval_factor ,
4065 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
4066 X_active_flag => l_detail_balances.active_flag,
4067 X_mode => 'R') ;
4068
4069
4070
4071 debug( g_state_level,l_path_name,'end insert det bals');
4072 -- insert into igi_iac_fa_deprn with the reinstatement adjustment_id
4073 l_rowid:=NULL;
4074 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
4075 x_rowid => l_rowid,
4076 x_book_type_code => p_book_type_code,
4077 x_asset_id => p_asset_id,
4078 x_period_counter => g_prd_rec.period_counter,
4079 x_adjustment_id => l_retirement_adjustment_id,
4080 x_distribution_id => l_fa_deprn.distribution_id,
4081 x_deprn_period => l_fa_deprn.deprn_period,
4082 x_deprn_ytd => l_fa_deprn.deprn_ytd,
4083 x_deprn_reserve => l_fa_deprn.deprn_reserve,
4084 x_active_flag => NULL,
4085 x_mode => 'R' );
4086
4087 END IF; -- impacted distribution
4088 CLOSE c_get_impacted_dist;
4089 ELSE -- Inactive distributions IF active_flag is not NULL. i.e. following code for active_flag = 'N'
4090
4091 debug( g_state_level,l_path_name,'Inactive distributions');
4092 l_rowid := NULL ;
4093 igi_iac_det_balances_pkg.insert_row(
4094 X_rowid => l_rowid ,
4095 X_adjustment_id => l_retirement_adjustment_id ,
4096 X_asset_id => P_Asset_Id,
4097 X_distribution_id => l_detail_balances.distribution_id ,
4098 X_book_type_code => P_Book_Type_Code ,
4099 X_period_counter => g_prd_rec.period_counter,
4100 X_adjustment_cost => l_detail_balances.adjustment_cost ,
4101 X_net_book_value => l_detail_balances.net_book_value,
4102 X_reval_reserve_cost => l_detail_balances.reval_reserve_cost,
4103 X_reval_reserve_backlog => l_detail_balances.reval_reserve_backlog,
4104 X_reval_reserve_gen_fund => l_detail_balances.reval_reserve_gen_fund,
4105 X_reval_reserve_net => l_detail_balances.reval_reserve_net,
4106 X_operating_acct_cost => l_detail_balances.operating_acct_cost,
4107 X_operating_acct_backlog => l_detail_balances.operating_acct_backlog,
4108 X_operating_acct_net => l_detail_balances.operating_acct_net,
4109 X_operating_acct_ytd => l_detail_balances.operating_acct_ytd,
4110 X_deprn_period => l_detail_balances.deprn_period,
4111 X_deprn_ytd => l_detail_balances.deprn_ytd,
4112 X_deprn_reserve => l_detail_balances.deprn_reserve,
4113 X_deprn_reserve_backlog => l_detail_balances.deprn_reserve_backlog,
4114 X_general_fund_per => l_detail_balances.general_fund_per,
4115 X_general_fund_acc => l_detail_balances.general_fund_acc,
4116 X_last_reval_date => l_detail_balances.last_reval_date ,
4117 X_current_reval_factor => l_detail_balances.current_reval_factor ,
4118 X_cumulative_reval_factor => l_detail_balances.cumulative_reval_factor ,
4119 X_active_flag => l_detail_balances.active_flag ,
4120 X_mode => 'R') ;
4121
4122 l_rowid := NULL ;
4123 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
4124 x_rowid => g_rowid,
4125 x_book_type_code => p_book_type_code,
4126 x_asset_id => p_asset_id,
4127 x_period_counter => g_prd_rec.period_counter,
4128 x_adjustment_id => l_retirement_adjustment_id,
4129 x_distribution_id => l_fa_deprn.distribution_id,
4130 x_deprn_period => l_fa_deprn.deprn_period,
4131 x_deprn_ytd => l_fa_deprn.deprn_ytd,
4132 x_deprn_reserve => l_fa_deprn.deprn_reserve,
4133 x_active_flag => l_fa_deprn.active_flag,
4134 x_mode => 'R'
4135 );
4136 END IF ; -- if active_flag is not Null
4137
4138 END LOOP ; -- det balances
4139 debug( g_state_level,l_path_name,'End of Depreciation');
4140 ELSE -- no deprecaitions
4141 debug( g_state_level,l_path_name,'No Depreciations');
4142 NULL;
4143 END IF; --end deprecaitions
4144 CLOSE C_check_depreciations;
4145 -- end reversal
4146 debug( g_state_level,l_path_name,'Prior unit sucess');
4147 RETURN TRUE;
4148
4149 EXCEPTION
4150 WHEN OTHERS THEN
4151 debug( g_state_level,l_path_name,'Error in Processing Prior Unit Retirement');
4152 FA_SRVR_MSG.add_sql_error(Calling_Fn => g_calling_fn);
4153 RETURN FALSE ;
4154
4155 END Prior_Unit_Retirement;
4156 --functions
4157
4158 FUNCTION Do_IAC_Retirement (
4159 P_Asset_Id IN NUMBER ,
4160 P_Book_Type_Code IN VARCHAR2 ,
4161 P_Retirement_Id IN NUMBER ,
4162 P_Calling_Function IN VARCHAR2,
4163 P_Event_Id IN NUMBER ) --R12 uptake
4164 RETURN BOOLEAN IS
4165
4166 -- Sekhar
4167 -- Status for adjustments in a period
4168 l_asset_number C_get_asset_number%rowtype;
4169 l_allowed_date Date;
4170 FUNCTION Get_Adjustment_Status(
4171 X_book_type_code IN VARCHAR2,
4172 X_asset_id IN NUMBER,
4173 X_Period_Counter IN NUMBER,
4174 X_allowed_date OUT NOCOPY DATE )
4175 RETURN BOOLEAN IS
4176
4177 Cursor c_get_prior_transactions(p_book_type_code varchar2, p_asset_id number,p_period_counter number)
4178 is select MAX(period_counter) period_counter
4179 from igi_iac_transaction_headers
4180 where asset_id = p_asset_id
4181 and book_type_code = p_book_type_code
4182 and period_counter >= p_period_counter
4183 and not (transaction_type_code='DEPRECIATION' and transaction_sub_type is null)
4184 and not (transaction_type_code='REVALUATION' and transaction_sub_type in ('OCCASSIONAL','PRFOESSIONAL'));
4185
4186
4187 l_asset_id number;
4188 l_prd_rec igi_iac_types.prd_rec;
4189 l_iac_transaction c_get_prior_transactions%rowtype;
4190
4191
4192 BEGIN
4193 x_allowed_date :=Null;
4194 open c_get_prior_transactions( X_book_type_code,X_asset_id ,X_Period_Counter);
4195 fetch c_get_prior_transactions into l_iac_transaction;
4196 If l_iac_transaction.period_counter is not null Then
4197 close c_get_prior_transactions;
4198 l_iac_transaction.period_counter := l_iac_transaction.period_counter + 1;
4199 IF not igi_iac_common_utils.get_period_info_for_counter(X_book_type_code,l_iac_transaction.period_counter
4200 ,l_prd_rec) THEN
4201 Null;
4202
4203 END iF;
4204 x_allowed_date := l_prd_rec.period_start_date;
4205 RETURN FALSE;
4206
4207 else
4208
4209 close c_get_prior_transactions;
4210 RETURN TRUE;
4211 end if;
4212
4213 END Get_Adjustment_Status;
4214
4215
4216 BEGIN
4217
4218 g_path_name := g_path||'.Do_IAC_Retirement';
4219
4220 IF NOT (igi_iac_common_utils.is_asset_proc(P_Book_Type_Code,P_Asset_Id)) THEN
4221 RAISE e_is_asset_proc ;
4222 END IF ;
4223 debug( g_state_level,g_path_name,'asset processed by IAC');
4224
4225 -- if no entries exist for the book in the table
4226 IF NOT igi_iac_common_utils.populate_iac_fa_deprn_data(p_book_type_code,'RETIREMENT') THEN
4227 debug( g_error_level,g_path_name,'Problems creating rows in igi_iac_fa_deprn');
4228 RAISE e_iac_fa_deprn;
4229 END IF;
4230
4231 IF NOT (igi_iac_common_utils.get_open_period_info(P_Book_Type_Code,g_prd_rec)) THEN
4232 RAISE e_no_open_prd_info ;
4233 END IF ;
4234 debug( g_state_level,g_path_name,'got open period info');
4235
4236 IF NOT igi_iac_common_utils.get_period_info_for_counter(p_book_type_code,g_prd_rec.period_counter,
4237 g_period_rec) THEN
4238 debug( g_state_level,g_path_name,
4239 'Could not retreive period information for the current period '||g_prd_rec.period_counter);
4240 RETURN FALSE;
4241 END IF;
4242
4243
4244
4245 g_retire_rec.retirement_id := p_retirement_id ;
4246
4247 IF NOT (fa_util_pvt.get_asset_retire_rec(px_asset_retire_rec => g_retire_rec,
4248 p_mrc_sob_type_code => NULL )) THEN
4249 RAISE e_no_retire_rec ;
4250 END IF ;
4251 debug( g_state_level,g_path_name,'got retirement record');
4252
4253 IF NOT (igi_iac_common_utils.get_period_info_for_date(P_Book_Type_Code,
4254 g_retire_rec.date_retired,
4255 g_retire_prd_rec )) THEN
4256 RAISE e_no_retire_period ;
4257 END IF ;
4258 debug( g_state_level, g_path_name,'got retirement period info');
4259
4260 If g_prd_rec.period_counter <> g_retire_prd_rec.period_counter THEN
4261 --- Check if adjustment has been processed in the prior period
4262 IF NOT Get_Adjustment_Status(p_book_type_code,P_Asset_Id,g_retire_prd_rec.period_counter,l_allowed_date) THEN
4263
4264
4265 -- gett the asset number for asset and call the error message
4266 open C_get_asset_number( P_Asset_Id);
4267 fetch c_get_asset_number into l_asset_number;
4268 Close C_get_asset_number;
4269
4270
4271 FA_SRVR_MSG.Add_Message(
4272 Calling_FN => p_calling_function ,
4273 Name => 'IGI_IAC_OVERLAP_RETIRE',
4274 TOKEN1 => 'NUMBER',
4275 VALUE1 => l_asset_number.asset_number,
4276 TOKEN2 => 'DATE',
4277 VALUE2 => l_allowed_date,
4278 TRANSLATE => TRUE,
4279 APPLICATION => 'IGI');
4280 RETURN FALSE;
4281
4282 END IF;
4283 END IF;
4284
4285 g_prior_prd_count := g_prd_rec.period_counter - g_retire_prd_rec.period_counter ;
4286
4287 IF NOT (igi_iac_common_utils.get_retirement_type(P_Book_Type_Code,
4288 P_Asset_id,
4289 P_Retirement_Id,
4290 g_retirement_type)) THEN
4291 RAISE e_no_retire_type ;
4292 END IF ;
4293 debug( g_state_level,g_path_name,'got retirement type');
4294
4295 IF g_prior_prd_count > 0 THEN
4296 g_retirement_period_type:='PRIOR';
4297 g_retirement_prior_period := g_retire_prd_rec.period_counter;
4298 ELSE
4299 g_retirement_period_type:='CURRENT';
4300 g_retirement_prior_period:= NULL;
4301 END IF;
4302
4303 IF g_retirement_type = 'FULL' THEN
4304 g_ret_type_long := 'FULL RETIREMENT';
4305 ELSE
4306 g_ret_type_long := 'PARTIAL RETIRE';
4307 END IF ;
4308
4309 g_retire_rec.retirement_id := p_retirement_id ;
4310
4311 OPEN c_fa_trx(g_retire_rec.detail_info.transaction_header_id_in) ;
4312 FETCH c_fa_trx INTO g_fa_trx ;
4313 IF c_fa_trx%NOTFOUND THEN
4314 CLOSE c_fa_trx ;
4315 RAISE NO_DATA_FOUND ;
4316 END IF ;
4317 CLOSE c_fa_trx ;
4318
4319 debug( g_state_level,g_path_name,'c_fa_trx OK');
4320
4321 OPEN c_fa_adds(P_Asset_Id) ;
4322 FETCH c_fa_adds INTO g_asset_category_id ;
4323 IF c_fa_adds%NOTFOUND THEN
4324 CLOSE c_fa_adds ;
4325 RAISE NO_DATA_FOUND ;
4326 END IF ;
4327 CLOSE c_fa_adds ;
4328
4329 debug( g_state_level,g_path_name,'c_fa_adds OK');
4330
4331 g_rowid := NULL ;
4332
4333 -- now find out NOCOPY if the period is the first period for the fiscal year
4334 IF (g_period_rec.period_num = 1) THEN
4335 g_is_first_period := TRUE;
4336 ELSE
4337 g_is_first_period := FALSE;
4338 END IF;
4339
4340
4341 IF NOT (igi_iac_common_utils.get_book_gl_info(P_Book_Type_Code,
4342 g_sob_id,
4343 g_coa_id,
4344 g_currency,
4345 g_precision
4346 )) THEN
4347 RAISE e_no_book_gl ;
4348 END IF ;
4349 debug( g_state_level,g_path_name,'got gl book info');
4350
4351
4352
4353 /* Get total number of units for the asset */
4354 OPEN c_total_units(P_Asset_Id);
4355 FETCH c_total_units INTO g_total_asset_units ;
4356 IF c_total_units%NOTFOUND THEN
4357 CLOSE c_total_units;
4358 RAISE e_no_units_info ;
4359 END IF ;
4360 CLOSE c_total_units ;
4361
4362
4363 IF g_retirement_type IN('FULL','COST') THEN
4364
4365 -- get the reitement factor partial cost or retirement
4366 IF g_retirement_type <> 'FULL' THEN
4367 IF NOT (igi_iac_common_utils.get_cost_retirement_factor(P_Book_Type_Code,
4368 P_Asset_Id,
4369 P_Retirement_Id,
4370 g_retirement_factor)) THEN
4371 RAISE e_no_cost_retire ;
4372 END IF ;
4373 ELSE
4374 g_retirement_factor:=1;
4375 END IF;
4376
4377 debug( g_state_level,g_path_name,'got cost retirement factor'||g_retirement_factor);
4378 IF g_prior_prd_count > 0 THEN
4379 -- call prior period processing for retirement
4380 IF NOT Prior_cost_retirement( P_Asset_Id => p_asset_id ,
4381 P_Book_Type_Code => p_book_type_code ,
4382 P_Retirement_Id => p_retirement_id ,
4383 P_retirement_type => g_retirement_type ,
4384 p_retirement_factor => g_retirement_factor,
4385 p_retirement_period_type => g_retirement_period_type,
4386 P_prior_period => g_retirement_prior_period,
4387 P_Current_period => g_prd_rec.period_counter,
4388 P_Event_Id => P_Event_Id ) THEN
4389
4390
4391 debug( g_state_level,g_path_name,'Failed in prior period cost retirement ');
4392 RETURN FALSE;
4393 END IF;
4394
4395 END IF;
4396 -- call current period processing for retirement
4397 IF NOT cost_retirement( P_Asset_Id => p_asset_id ,
4398 P_Book_Type_Code => p_book_type_code ,
4399 P_Retirement_Id => p_retirement_id ,
4400 P_retirement_type => g_retirement_type ,
4401 p_retirement_factor => g_retirement_factor,
4402 p_retirement_period_type => g_retirement_period_type,
4403 P_prior_period => g_retirement_prior_period,
4404 P_Current_period => g_prd_rec.period_counter,
4405 P_Event_Id => P_Event_Id) THEN
4406
4407
4408 debug( g_state_level,g_path_name,'Failed in Current period cost retirement ');
4409 RETURN FALSE;
4410 END IF;
4411
4412
4413 ELSE -- Partial Unit retirement
4414 -- call prior period processing for retirement
4415 IF g_prior_prd_count > 0 THEN
4416 -- call prior period processing for retirement
4417 IF NOT Prior_unit_retirement( P_Asset_Id => p_asset_id ,
4418 P_Book_Type_Code => p_book_type_code ,
4419 P_Retirement_Id => p_retirement_id ,
4420 P_retirement_type => g_retirement_type ,
4421 p_retirement_period_type => g_retirement_period_type,
4422 P_prior_period => g_retirement_prior_period,
4423 P_Current_period => g_prd_rec.period_counter,
4424 P_Event_Id => P_Event_Id) THEN
4425
4426
4427 debug( g_state_level,g_path_name,'Failed in prior period cost retirement ');
4428 RETURN FALSE;
4429 END IF;
4430
4431 END IF;
4432 -- call current period processing for retirement
4433 IF NOT Unit_retirement( P_Asset_Id => p_asset_id ,
4434 P_Book_Type_Code => p_book_type_code ,
4435 P_Retirement_Id => p_retirement_id ,
4436 P_retirement_type => g_retirement_type ,
4437 p_retirement_period_type => g_retirement_period_type,
4438 P_prior_period => g_retirement_prior_period,
4439 P_Current_period => g_prd_rec.period_counter,
4440 P_Event_Id => P_Event_Id ) THEN
4441
4442
4443 debug( g_state_level,g_path_name,'Failed in prior period cost retirement ');
4444 RETURN FALSE;
4445 END IF;
4446
4447 END IF;
4448
4449 debug( g_state_level,g_path_name,'IAC Retirement sucess ');
4450 -- RETURN FALSE ;
4451 RETURN TRUE ;
4452 EXCEPTION
4453 WHEN e_is_asset_proc THEN
4454 debug( g_state_level,g_path_name,'IGI_IAC_NO_IAC_EFFECT');
4455 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn, Name=>'IGI_IAC_NO_IAC_EFFECT');
4456 RETURN TRUE ;
4457
4458 WHEN e_iac_fa_deprn THEN
4459 debug( g_state_level,g_path_name,'problems inserting rows into igi_iac_fa_deprn');
4460 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,
4461 Name => 'IGI_IAC_FA_DEPR_CREATE_PROB',
4462 Token1 => 'BOOK',
4463 Value1 => p_book_type_code);
4464 RETURN TRUE;
4465
4466 WHEN e_no_open_prd_info THEN
4467 debug( g_state_level,g_path_name,'cannot get open period info');
4468 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=>'IGI_IAC_NO_PERIOD_INFO');
4469 RETURN FALSE ;
4470
4471 WHEN e_no_retire_rec THEN
4472 debug( g_state_level,g_path_name,'cannot get retirement record');
4473 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=> 'IGI_IAC_NO_RETIRE_EFFECT');
4474 RETURN FALSE ;
4475
4476 WHEN e_no_retire_type THEN
4477 debug( g_state_level,g_path_name,'cannot get retirement type');
4478 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=> 'IGI_IAC_INDEF_RETIRE_TYPE');
4479 RETURN FALSE ;
4480
4481 WHEN e_no_book_gl THEN
4482 debug( g_state_level,g_path_name,'cannot get gl book info');
4483 RETURN FALSE ;
4484
4485 WHEN e_no_cost_retire THEN
4486 debug( g_state_level,g_path_name,'no cost retirement ingo');
4487 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=> 'IGI_IAC_NO_COST_RETIRE_FACTOR');
4488 RETURN FALSE ;
4489
4490 WHEN e_no_account_ccid THEN
4491 debug( g_state_level,g_path_name,'get_account_ccid failure');
4492 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=>'IGI_IAC_NO_WF_FAILED_CCID');
4493 RETURN FALSE ;
4494
4495 WHEN e_no_units_info THEN
4496 debug( g_state_level,g_path_name,'no units info');
4497 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=> 'IGI_IAC_NO_UNITS_INFO');
4498 RETURN FALSE ;
4499
4500 WHEN e_no_asset_bals THEN
4501 debug( g_state_level,g_path_name,'no asset balances');
4502 IF c_asset_balances%ISOPEN THEN
4503 CLOSE c_asset_balances ;
4504 END IF ;
4505 FA_SRVR_MSG.add_message(Calling_Fn => g_calling_fn,Name=> 'IGI_IAC_NO_ASSET_BALS');
4506 RETURN FALSE ;
4507
4508 WHEN OTHERS THEN
4509 igi_iac_debug_pkg.debug_unexpected_msg( g_path_name);
4510 -- close any open cursors
4511 IF c_asset_balances%ISOPEN THEN
4512 CLOSE c_asset_balances ;
4513 END IF ;
4514 IF c_fa_trx%ISOPEN THEN
4515 CLOSE c_fa_trx ;
4516 END IF ;
4517 IF c_fa_adds%ISOPEN THEN
4518 CLOSE c_fa_adds ;
4519 END IF ;
4520 IF c_units_per_dist%ISOPEN THEN
4521 CLOSE c_units_per_dist ;
4522 END IF ;
4523 FA_SRVR_MSG.add_SQL_error(Calling_Fn => g_calling_fn);
4524 RETURN FALSE ;
4525
4526 END Do_IAC_Retirement ;
4527
4528
4529
4530 BEGIN
4531
4532 g_calling_fn := 'IGI_IAC_RETIREMENT.Do_IAC_Retirement';
4533 g_state_level := FND_LOG.LEVEL_STATEMENT;
4534 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
4535 g_event_level := FND_LOG.LEVEL_EVENT;
4536 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
4537 g_error_level := FND_LOG.LEVEL_ERROR;
4538 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
4539 g_path := 'IGI.PLSQL.igiiartb.igi_iac_retirement';
4540
4541
4542 END igi_iac_retirement; --package spec