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