[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_ADJ_AMORT_PKG
Source
1 PACKAGE BODY IGI_IAC_ADJ_AMORT_PKG AS
2 -- $Header: igiiadab.pls 120.19.12020000.1 2012/06/27 10:33:38 appldev ship $
3
4 -- ===================================================================
5 -- Global Variables
6 -- ===================================================================
7
8 l_calling_function varchar2(255);
9
10 l_rowid ROWID;
11 g_calling_fn VARCHAR2(200);
12 g_asset_num fa_additions.asset_number%TYPE;
13
14 -- ===================================================================
15 -- Local functions and procedures
16 -- ===================================================================
17
18 --===========================FND_LOG.START=====================================
19
20 g_state_level NUMBER;
21 g_proc_level NUMBER;
22 g_event_level NUMBER;
23 g_excep_level NUMBER;
24 g_error_level NUMBER;
25 g_unexp_level NUMBER;
26 g_path VARCHAR2(100);
27
28 --===========================FND_LOG.END=====================================
29
30 PROCEDURE do_round ( p_amount in out NOCOPY number, p_book_type_code in varchar2) is
31 l_path varchar2(150) := g_path||'do_round(p_amount,p_book_type_code)';
32 l_amount number := p_amount;
33 l_amount_old number := p_amount;
34 --l_path varchar2(150) := g_path||'do_round';
35 begin
36 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'--- Inside Round() ---');
37 IF IGI_IAC_COMMON_UTILS.Iac_Round(X_Amount => l_amount, X_Book => p_book_type_code)
38 THEN
39 p_amount := l_amount;
40 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is TRUE');
41 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
42 ELSE
43 p_amount := round( l_amount, 2);
44 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'IGI_IAC_COMMON_UTILS.Iac_Round is FALSE');
45 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'p_amount = '||p_amount);
46 END IF;
47 exception when others then
48 p_amount := l_amount_old;
49 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
50 Raise;
51 END;
52
53 -- -------------------------------------------------------------------
54 -- PROCEDURE Debug_Adj_Asset : Procedure that will print the historic
55 -- asset information
56 -- -------------------------------------------------------------------
57
58 PROCEDURE debug_adj_asset(p_asset igi_iac_types.iac_adj_hist_asset_info) IS
59 l_path_name VARCHAR2(150);
60 BEGIN
61 l_path_name := g_path||'debug_adj_asset';
62 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
63 p_full_path => l_path_name,
64 p_string => 'asset_id...............'|| p_asset.asset_id);
65 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
66 p_full_path => l_path_name,
67 p_string => 'book_type_code.........'|| p_asset.book_type_code);
68 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
69 p_full_path => l_path_name,
70 p_string => 'cost...................'|| p_asset.cost );
71 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
72 p_full_path => l_path_name,
73 p_string => 'original_Expensed..........'|| p_asset.original_cost);
74 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
75 p_full_path => l_path_name,
76 p_string => 'adjusted_Expensed..........'|| p_asset.adjusted_cost );
80 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
77 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
78 p_full_path => l_path_name,
79 p_string => 'salvage_value..........'|| p_asset.salvage_value );
81 p_full_path => l_path_name,
82 p_string => 'life_in_months.........'|| p_asset.life_in_months);
83 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
84 p_full_path => l_path_name,
85 p_string => 'rate_adjustment_factor..'||p_asset.rate_adjustment_factor);
86 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
87 p_full_path => l_path_name,
88 p_string => 'period_counter_fully_reserved '|| p_asset.period_counter_fully_reserved);
89 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
90 p_full_path => l_path_name,
91 p_string => 'recoverable_Expensed.......'|| p_asset.recoverable_cost);
92 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
93 p_full_path => l_path_name,
94 p_string => 'date_placed_in_service..'||p_asset.date_placed_in_service);
95 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
96 p_full_path => l_path_name,
97 p_string => 'deprn_start_date........'||p_asset.deprn_start_date);
98 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
99 p_full_path => l_path_name,
100 p_string => 'deprn_periods_elapsed...'||p_asset.deprn_periods_elapsed);
101 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
102 p_full_path => l_path_name,
103 p_string => 'deprn_periods_current_year..'||p_asset.deprn_periods_current_year);
104 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
105 p_full_path => l_path_name,
106 p_string => 'prior year periods..'|| p_asset.deprn_periods_prior_year);
107 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
108 p_full_path => l_path_name,
109 p_string => 'last_period_counter.........'|| p_asset.last_period_counter);
110 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
111 p_full_path => l_path_name,
112 p_string => 'ytd_deprn...................'|| p_asset.ytd_deprn);
113 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
114 p_full_path => l_path_name,
115 p_string => 'deprn_reserve................'|| p_asset.deprn_reserve);
116 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
117 p_full_path => l_path_name,
118 p_string => 'pys_deprn_reserve............'|| p_asset.pys_deprn_reserve);
119 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
120 p_full_path => l_path_name,
121 p_string => 'deprn_amount................'|| p_asset.deprn_amount);
122 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
123 p_full_path => l_path_name,
124 p_string => 'depreciate_flag................'|| p_asset.depreciate_flag);
125 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
126 p_full_path => l_path_name,
127 p_string => 'deprn_adjustment_amount................'|| p_asset.deprn_adjustment_amount);
128
129 END debug_adj_asset;
130
131 -- ===================================================================
132 -- Main public functions and procedures
133 -- ===================================================================
134
135 -- -------------------------------------------------------------------
136 -- PROCEDURE Do_Amort_Deprn_Reval: This is the main function that will
137 -- do the Amortization Depreciation revaluation for the asset
138 -- -------------------------------------------------------------------
139 FUNCTION Do_Amort_Deprn_Reval(p_asset_iac_adj_info igi_iac_types.iac_adj_hist_asset_info,
140 p_asset_iac_dist_info igi_iac_types.iac_adj_dist_info_tab,
141 p_adj_hist igi_iac_adjustments_history%ROWTYPE,
142 p_event_id number) --R12 uptake
143 RETURN BOOLEAN
144 IS
145 -- cursors
146 /*-- cursor to check if asset is non depreciating
147 CURSOR c_asset_non_depr(cp_asset_id fa_books.asset_id%TYPE,
148 cp_book_type_code fa_books.book_type_code%TYPE)
149 IS
150 SELECT depreciate_flag
151 FROM fa_books
152 WHERE book_type_code = cp_book_type_code
153 AND asset_id = cp_asset_id
154 AND date_ineffective IS NULL;
155 */
156
157 -- cursor to get iac asset balance information
158 CURSOR c_iac_asset_bal(cp_asset_id igi_iac_asset_balances.asset_id%TYPE,
159 cp_book_type_code igi_iac_asset_balances.book_type_code%TYPE,
160 cp_period_counter igi_iac_asset_balances.period_counter%TYPE)
161 IS
162 SELECT period_counter,
163 net_book_value,
164 adjusted_cost,
165 operating_acct,
166 reval_reserve,
167 deprn_amount,
168 deprn_reserve,
169 backlog_deprn_reserve,
170 general_fund,
171 last_reval_date,
172 current_reval_factor,
173 cumulative_reval_factor
174 FROM igi_iac_asset_balances
175 WHERE asset_id = cp_asset_id
176 AND book_type_code = cp_book_type_code
177 AND period_counter = cp_period_counter;
178
179 -- cursor to get the detail balances for a distribution
183 cp_dist_id igi_iac_det_balances.distribution_id%TYPE)
180 CURSOR c_det_bal(cp_adjust_id igi_iac_det_balances.adjustment_id%TYPE,
181 cp_asset_id fa_books.asset_id%TYPE,
182 cp_book_type_code fa_books.book_type_code%TYPE,
184 IS
185 SELECT iidb.adjustment_id,
186 iidb.distribution_id,
187 iidb.period_counter,
188 iidb.adjustment_cost,
189 iidb.net_book_value,
190 iidb.reval_reserve_cost,
191 iidb.reval_reserve_backlog,
192 iidb.reval_reserve_gen_fund,
193 iidb.reval_reserve_net,
194 iidb.operating_acct_cost,
195 iidb.operating_acct_backlog,
196 iidb.operating_acct_ytd,
197 iidb.operating_acct_net,
198 iidb.deprn_period,
199 iidb.deprn_ytd,
200 iidb.deprn_reserve,
201 iidb.deprn_reserve_backlog,
202 iidb.general_fund_per,
203 iidb.general_fund_acc,
204 iidb.last_reval_date,
205 iidb.current_reval_factor,
206 iidb.cumulative_reval_factor,
207 iidb.active_flag
208 FROM igi_iac_det_balances iidb
209 WHERE iidb.adjustment_id = cp_adjust_id
210 AND iidb.book_type_code = cp_book_type_code
211 AND iidb.asset_id = cp_asset_id
212 AND iidb.distribution_id = cp_dist_id
213 AND iidb.active_flag IS NULL
214 ORDER BY iidb.distribution_id;
215
216 -- cursor to get the latest fa figures from
217 -- igi_iac_fa_deprn
218 CURSOR c_get_fa_iac_deprn(cp_distribution_id igi_iac_fa_deprn.distribution_id%TYPE,
219 cp_adjustment_id igi_iac_fa_deprn.adjustment_id%TYPE)
220 IS
221 SELECT iifd.deprn_ytd,
222 iifd.deprn_period,
223 iifd.deprn_reserve
224 FROM igi_iac_fa_deprn iifd
225 WHERE iifd.adjustment_id = cp_adjustment_id
226 AND iifd.distribution_id = cp_distribution_id
227 AND iifd.active_flag IS NULL;
228
229 -- cursor to get the fa_depreciation adjustment amount
230 CURSOR c_fa_deprn_adjust_amt(cp_asset_id fa_deprn_detail.book_type_code%TYPE,
231 cp_book_type_code fa_deprn_detail.book_type_code%TYPE,
232 cp_period_counter fa_deprn_detail.period_counter%TYPE)
233 IS
234 SELECT SUM(NVL(deprn_amount,0)) deprn_amount,
235 SUM(NVL(deprn_adjustment_amount,0)) deprn_adjustment_amount,
236 SUM(NVL(deprn_reserve,0)) deprn_reserve
237 FROM fa_deprn_detail
238 WHERE book_type_code = cp_book_type_code
239 AND asset_id = cp_asset_id
240 AND period_counter = cp_period_counter;
241
242 CURSOR c_get_period_first_adj(cp_asset_id igi_iac_adjustments_history.asset_id%TYPE,
243 cp_book_type_code igi_iac_adjustments_history.book_type_code%TYPE,
244 cp_period_counter igi_iac_adjustments_history.period_counter%TYPE)
245 IS
246 SELECT ah.pre_adjusted_cost, ah.pre_salvage_value
247 FROM igi_iac_adjustments_history ah
248 WHERE ah.book_type_code = cp_book_type_code
249 AND ah.asset_id = cp_asset_id
250 AND ah.transaction_header_id_in = (SELECT min(iah.transaction_header_id_in)
251 FROM igi_iac_adjustments_history iah
252 WHERE iah.book_type_code = cp_book_type_code
253 AND iah.asset_id = cp_asset_id
254 AND iah.period_counter = cp_period_counter);
255
256
257 -- local variables
258 l_asset_id fa_books.asset_id%TYPE;
259 l_book_type_code fa_books.book_type_code%TYPE;
260 l_last_period_counter igi_iac_transaction_headers.period_counter%TYPE;
261 l_sob_id NUMBER;
262 l_coa_id NUMBER;
263 l_currency VARCHAR2(15);
264 l_precision NUMBER;
265
266 l_latest_trx_type igi_iac_transaction_headers.transaction_type_code%TYPE;
267 l_latest_trx_id igi_iac_transaction_headers.transaction_header_id%TYPE;
268 l_latest_mref_id igi_iac_transaction_headers.mass_reference_id%TYPE;
269 l_latest_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
270 l_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
271 l_latest_adj_status igi_iac_transaction_headers.adjustment_status%TYPE;
272 l_adj_id_out igi_iac_transaction_headers.adjustment_id_out%TYPE;
273
274 l_fully_rsvd_pc fa_books.period_counter_fully_reserved%TYPE;
275 -- l_deprn_flag fa_books.depreciate_flag%TYPE;
276
277 l_new_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
278 l_rsvd_pc igi_iac_transaction_headers.period_counter%TYPE;
279 l_iac_asset_bal c_iac_asset_bal%ROWTYPE;
280
281 l_fa_deprn_adj_amount fa_deprn_detail.deprn_adjustment_amount%TYPE;
282 l_iac_new_deprn_adj_amt igi_iac_asset_balances.deprn_amount%TYPE;
283 l_iac_new_deprn_period igi_iac_asset_balances.deprn_amount%TYPE;
284 l_iac_new_deprn_reserve igi_iac_asset_balances.deprn_reserve%TYPE;
285
286 l_iac_new_nbv igi_iac_asset_balances.net_book_value%TYPE;
287 l_iac_new_rr igi_iac_asset_balances.reval_reserve%TYPE;
288 l_iac_new_gf igi_iac_asset_balances.general_fund%TYPE;
289
290 l_fa_idx NUMBER;
291 l_det_bal c_det_bal%ROWTYPE;
295 l_dep_adj_amount igi_iac_det_balances.deprn_period%TYPE;
292 l_det_table IGI_IAC_TYPES.dist_amt_tab;
293 l_dist_idx NUMBER;
294 l_dist_id igi_iac_det_balances.distribution_id%TYPE;
296 l_units_assigned igi_iac_adjustments.units_assigned%TYPE;
297
298 l_new_dist_dep_prd igi_iac_det_balances.deprn_period%TYPE;
299 l_new_dist_dep_rsv igi_iac_det_balances.deprn_reserve%TYPE;
300 l_new_dist_dep_ytd igi_iac_det_balances.deprn_ytd%TYPE;
301
302 l_new_dist_nbv igi_iac_det_balances.net_book_value%TYPE;
303 l_new_dist_rr_gf igi_iac_det_balances.reval_reserve_gen_fund%TYPE;
304 l_new_dist_rr_net igi_iac_det_balances.reval_reserve_net%TYPE;
305 l_new_dist_gf_per igi_iac_det_balances.general_fund_per%TYPE;
306 l_new_dist_gf_acc igi_iac_det_balances.general_fund_acc%TYPE;
307
308 l_round_diff NUMBER ;
309 l_fa_deprn_period igi_iac_fa_deprn.deprn_period%TYPE;
310 l_fa_deprn_reserve igi_iac_fa_deprn.deprn_reserve%TYPE;
311 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
312
313 l_ccid igi_iac_adjustments.code_combination_id%TYPE;
314 l_reval_rsv_ccid igi_iac_adjustments.code_combination_id%TYPE;
315 l_exists NUMBER;
316 l_ret BOOLEAN;
317
318 l_deprn_amount fa_deprn_detail.deprn_amount%TYPE;
319 l_deprn_reserve fa_deprn_detail.deprn_reserve%TYPE;
320 l_prev_cost fa_books.cost%TYPE;
321 l_prev_salvage fa_books.salvage_value%TYPE;
322 l_curr_cost fa_books.cost%TYPE;
323 l_curr_Salvage fa_books.salvage_value%TYPE;
324 l_prev_sv_correction NUMBER;
325 l_curr_sv_correction NUMBER;
326 l_diff_sv_correction NUMBER;
327 l_iac_deprn_period_amount NUMBER;
328 l_fa_deprn_period_amount NUMBER;
329
330 -- exceptions
331 e_latest_trx_not_avail EXCEPTION;
332 e_no_period_info_avail EXCEPTION;
333 e_no_ccid_found EXCEPTION;
334 e_no_proration EXCEPTION;
335 e_asset_life_err EXCEPTION;
336 e_no_gl_info EXCEPTION;
337 e_non_dep_asset EXCEPTION;
338 e_curr_period_amort EXCEPTION;
339
340 l_path_name VARCHAR2(150);
341
342 BEGIN -- do amort deprn reval
343 l_path_name := g_path||'do_amort_deprn_reval';
344 l_round_diff := 0;
345 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
346 p_full_path => l_path_name,
347 p_string => 'In Amortized Depreciation Revaluation');
348 debug_adj_asset( p_asset_iac_adj_info);
349
350 -- set local variables
351 l_book_type_code := p_asset_iac_adj_info.book_type_code;
352 l_asset_id := p_asset_iac_adj_info.asset_id;
353 l_last_period_counter := p_asset_iac_adj_info.last_period_counter;
354
355 -- get the asset number for debugging purpose
356 SELECT asset_number
357 INTO g_asset_num
358 FROM fa_additions
359 WHERE asset_id = l_asset_id;
360
361 -- get the GL set of books id
362 IF NOT igi_iac_common_utils.get_book_GL_info(l_book_type_code,
363 l_sob_id,
364 l_coa_id,
365 l_currency,
366 l_precision)
367 THEN
368 RAISE e_no_gl_info;
369 END IF;
370
371 -- Get the latest transaction or adjustment from igi_iac_common_utils.get_latest_transaction
372 -- for the asset and book
373 IF NOT igi_iac_common_utils.get_latest_transaction(l_book_type_code,
374 l_asset_id,
375 l_latest_trx_type,
376 l_latest_trx_id,
377 l_latest_mref_id,
378 l_latest_adj_id,
379 l_prev_adj_id,
380 l_latest_adj_status)
381 THEN
382 RAISE e_latest_trx_not_avail;
383 END IF;
384
385 -- set the adjustment_id_out
386 l_adj_id_out := l_latest_adj_id;
387
388 -- check if latest adjustment is a REVALUATION in PREVIEW status
389 IF (l_latest_trx_type = 'REVALUATION' AND l_latest_adj_status IN ('PREVIEW', 'OBSOLETE')) THEN
390 l_latest_adj_id := l_prev_adj_id;
391 END IF;
392
393 -- check if the asset is fully reserved
394 IF NOT igi_iac_adj_cost_reval_pkg.chk_asset_life(p_book_code => l_book_type_code,
395 p_period_counter => l_last_period_counter,
396 p_asset_id => l_asset_id,
397 l_last_period_counter => l_fully_rsvd_pc)
398 THEN
399 RAISE e_asset_life_err;
400 END IF;
401 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
402 p_full_path => l_path_name,
403 p_string => 'Fully reserved period counter: '||l_fully_rsvd_pc);
404
405 -- check if asset is non depreciating asset
409 IF (c_asset_non_depr%NOTFOUND) THEN
406 /* OPEN c_asset_non_depr(l_asset_id,
407 l_book_type_code);
408 FETCH c_asset_non_depr INTO l_deprn_flag;
410 RAISE NO_DATA_FOUND;
411 END IF;
412 CLOSE c_asset_non_depr;
413
414 Debug('Asset depreciation flag: '||l_deprn_flag);
415 */
416 -- if asset is non depreciating do not process any further
417 IF (p_asset_iac_adj_info.depreciate_flag = 'NO') THEN
418 RAISE e_non_dep_asset;
419 END IF;
420
421 OPEN c_get_period_first_adj(l_asset_id,
422 l_book_type_code,
423 l_last_period_counter);
424 FETCH c_get_period_first_adj INTO l_prev_cost, l_prev_salvage;
425 CLOSE c_get_period_first_adj;
426
427 l_curr_cost := p_adj_hist.adjusted_cost;
428 l_curr_salvage := p_adj_hist.salvage_value;
429
430 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
431 p_full_path => l_path_name,
432 p_string => 'Before adjustment Cost: '||l_prev_cost);
433 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
434 p_full_path => l_path_name,
435 p_string => 'Before adjustment Salvage Value: '||l_prev_salvage);
436 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
437 p_full_path => l_path_name,
438 p_string => 'After adjustment Cost: '||l_curr_cost);
439 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
440 p_full_path => l_path_name,
441 p_string => 'After adjustment Salvage Value: '||l_curr_salvage);
442
443 -- check if asset is amortized in current period, if yes then
444 -- do not process any further
445 IF (p_adj_hist.current_period_amortization = 'Y' AND
446 (l_prev_cost = l_curr_cost OR
447 (l_prev_cost <> l_curr_cost AND l_prev_salvage = 0 AND l_curr_salvage = 0)) AND
448 l_prev_salvage = l_curr_salvage) THEN
449 RAISE e_curr_period_amort;
450 END IF;
451
452 IF (l_last_period_counter >= l_fully_rsvd_pc) THEN
453 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
454 p_full_path => l_path_name,
455 p_string => 'Asset is fully reserved or non depreciable');
456 -- get the period counter associated with the latest adjustment
457 -- for the asset
458 SELECT period_counter
459 INTO l_rsvd_pc
460 FROM igi_iac_transaction_headers
461 WHERE adjustment_id = l_latest_adj_id;
462
463 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
464 p_full_path => l_path_name,
465 p_string => 'Last period counter for fully reserved or non deprn asset: '||l_rsvd_pc);
466 ELSE
467 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
468 p_full_path => l_path_name,
469 p_string => 'asset is depreciable with life');
470 l_rsvd_pc := l_last_period_counter;
471 END IF;
472
473 -- Fetch the latest IGI_IAC_ASSET_BALANCES for the asset regardless of type
474 -- bug 3391000, start 1
475 /* OPEN c_iac_asset_bal(l_asset_id,
476 l_book_type_code,
477 l_rsvd_pc);
478 FETCH c_iac_asset_bal INTO l_iac_asset_bal;
479 IF c_iac_asset_bal%NOTFOUND THEN
480 RAISE NO_DATA_FOUND ;
481 END IF ;
482 CLOSE c_iac_asset_bal; */
483
484 -- Fetch the latest IGI_IAC_ASSET_BALANCES for the asset regardless of type
485 -- if a balance row exists for the latest open period counter then retrieve
486 -- that else, retrieve the last active row
487 OPEN c_iac_asset_bal(l_asset_id,
488 l_book_type_code,
489 l_last_period_counter);
490 FETCH c_iac_asset_bal INTO l_iac_asset_bal;
491 IF c_iac_asset_bal%NOTFOUND THEN
492 CLOSE c_iac_asset_bal;
493 OPEN c_iac_asset_bal(l_asset_id,
494 l_book_type_code,
495 l_rsvd_pc);
496 FETCH c_iac_asset_bal INTO l_iac_asset_bal;
497 IF c_iac_asset_bal%NOTFOUND THEN
498 RAISE NO_DATA_FOUND ;
499 END IF ;
500 END IF;
501 CLOSE c_iac_asset_bal;
502
503 -- bug 3391000, end 1
504
505 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
506 p_full_path => l_path_name,
507 p_string => 'Latest period counter: '||l_last_period_counter);
508
509 -- Calculate the new revalued FA depreciation amount
510 IF (p_asset_iac_adj_info.deprn_adjustment_amount = 0) THEN
511 -- fetch from fa_deprn_detail
512 OPEN c_fa_deprn_adjust_amt(l_asset_id,
513 l_book_type_code,
514 l_last_period_counter);
515 FETCH c_fa_deprn_adjust_amt INTO l_deprn_amount,
516 l_fa_deprn_adj_amount,
517 l_deprn_reserve;
518 IF c_fa_deprn_adjust_amt%NOTFOUND THEN
519 RAISE NO_DATA_FOUND;
520 END IF;
521 CLOSE c_fa_deprn_adjust_amt;
522 ELSE
523 l_deprn_amount := p_asset_iac_adj_info.deprn_amount;
527
524 l_fa_deprn_adj_amount := p_asset_iac_adj_info.deprn_adjustment_amount;
525 l_deprn_reserve := p_asset_iac_adj_info.deprn_reserve;
526 END IF;
528 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
529 p_full_path => l_path_name,
530 p_string => 'FA depreciation adjustment amount: '||l_fa_deprn_adj_amount);
531
532 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
533 p_full_path => l_path_name,
534 p_string => 'FA depreciation amount: '||l_deprn_amount);
535 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
536 p_full_path => l_path_name,
537 p_string => 'FA depreciation reserve: '||l_deprn_reserve);
538
539 IF ((l_prev_cost <> l_curr_cost OR l_prev_salvage <> l_curr_salvage) AND
540 (l_prev_salvage <> 0 OR l_curr_salvage <> 0)) THEN
541 l_prev_sv_correction := (l_deprn_reserve - l_deprn_amount) * (l_prev_salvage/(l_prev_cost-l_prev_salvage));
542 do_round(l_prev_sv_correction,l_book_type_code);
543 l_curr_sv_correction := (l_deprn_reserve - l_deprn_amount) * (l_curr_salvage/(l_curr_cost-l_curr_salvage));
544 do_round(l_curr_sv_correction,l_book_type_code);
545 l_diff_sv_correction := l_curr_sv_correction - l_prev_sv_correction;
546 ELSE
547 l_diff_sv_correction := 0;
548 END IF;
549 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
550 p_full_path => l_path_name,
551 p_string => 'Prev Salvage Value Correction: '||l_prev_sv_correction);
552 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
553 p_full_path => l_path_name,
554 p_string => 'Curr Salvage Value Correction: '||l_curr_sv_correction);
555
556 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
557 p_full_path => l_path_name,
558 p_string => 'Salvage Value Correction: '||l_diff_sv_correction);
559
560 l_fa_deprn_adj_amount := l_fa_deprn_adj_amount + l_diff_sv_correction;
561
562 IF (l_fa_deprn_adj_amount = 0) THEN
563 RAISE e_curr_period_amort;
564 END IF;
565
566 -- create a new row in igi_iac_transaction_headers with transaction type code
567 -- ADJUSTMENT and transaction sub type as COST
568 l_new_adj_id := null;
569
570 IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
571 x_rowid => l_rowid,
572 x_adjustment_id => l_new_adj_id, -- out NOCOPY parameter
573 x_transaction_header_id => p_adj_hist.transaction_header_id_in, -- bug 3391000 null,
574 x_adjustment_id_out => null,
575 x_transaction_type_code => 'DEPRECIATION',
576 x_transaction_date_entered => sysdate,
577 x_mass_refrence_id => null,
578 x_transaction_sub_type => 'ADJUSTMENT',
579 x_book_type_code => l_book_type_code,
580 x_asset_id => l_asset_id,
581 x_category_id => p_adj_hist.category_id,
582 x_adj_deprn_start_date => null,
583 x_revaluation_type_flag => null,
584 x_adjustment_status => 'COMPLETE',
585 x_period_counter => l_last_period_counter,
586 x_event_id => p_event_id
587 );
588
589 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
590 p_full_path => l_path_name,
591 p_string => 'New adjustment id: '||l_new_adj_id);
592
593 -- update the previous active row for the asset in igi_iac_transaction_headers
594 -- in order to make it inactive by setting adjustment_id_out= adjustment_id of
595 -- the active row in igi_iac_transaction_headers
596 IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
597 x_prev_adjustment_id => l_adj_id_out,
598 x_adjustment_id => l_new_adj_id
599 );
600
601 -- calculate the new revlaued deprn adjustment amount
602 l_iac_new_deprn_adj_amt := l_fa_deprn_adj_amount*(l_iac_asset_bal.cumulative_reval_factor - 1);
603
604 -- round the depreciation adjustment amount
605 l_ret := igi_iac_common_utils.iac_round(l_iac_new_deprn_adj_amt,
606 l_book_type_code) ;
607
608 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
609 p_full_path => l_path_name,
610 p_string => 'New IAC revalued depreciation adjustment amount: '||l_iac_new_deprn_adj_amt);
611
612 -- calculate the new deprn_amount, deprn_ytd and deprn_reserve
613 l_iac_new_deprn_period := l_iac_new_deprn_adj_amt;
614 l_iac_new_deprn_reserve:= l_iac_asset_bal.deprn_reserve + l_iac_new_deprn_adj_amt;
615
616 -- calculate net book value
617 -- l_iac_new_nbv := l_iac_asset_bal.net_book_value - l_iac_new_deprn_adj_amt;
618 l_iac_new_nbv := l_iac_asset_bal.adjusted_cost -
619 (l_iac_new_deprn_reserve + l_iac_asset_bal.backlog_deprn_reserve);
620 -- added 2: 16/12/2003
621 -- add gen fun and change IF condition
622 -- IF (l_iac_asset_bal.adjusted_cost > 0) THEN
623 IF (l_iac_asset_bal.cumulative_reval_factor >= 1) THEN
624 l_iac_new_gf := l_iac_asset_bal.general_fund + l_iac_new_deprn_adj_amt;
625 l_iac_new_rr := l_iac_asset_bal.reval_reserve -l_iac_new_deprn_adj_amt;
626 ELSE
627 l_iac_new_gf := l_iac_asset_bal.general_fund;
628 l_iac_new_rr := l_iac_asset_bal.reval_reserve;
629 END IF;
633 p_full_path => l_path_name,
630 -- end 1: 12/12/2003
631
632 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
634 p_string => 'new deprn period: '||l_iac_new_deprn_period);
635 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
636 p_full_path => l_path_name,
637 p_string => 'new deprn reserve: '||l_iac_new_deprn_reserve);
638
639 -- Prorate th depreciation ajustment amount for the active distributions in the
640 -- ratio of the units assigned to them
641 IF NOT igi_iac_common_utils.prorate_amt_to_active_dists(l_book_type_code,
642 l_asset_id,
643 l_iac_new_deprn_adj_amt,
644 l_det_table)
645 THEN
646 RAISE e_no_proration;
647 END IF;
648
649 l_round_diff := 0;
650
651 -- create a new row in igi_iac_det_balances for each active distribution
652 FOR l_dist_idx IN l_det_table.FIRST..l_det_table.LAST LOOP
653 l_dist_id := l_det_table(l_dist_idx).distribution_id;
654 l_dep_adj_amount := l_det_table(l_dist_idx).amount;
655 l_units_assigned := l_det_table(l_dist_idx).units;
656
657 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
658 p_full_path => l_path_name,
659 p_string => ' Dist ID: '||l_dist_id||' Units: '||l_units_assigned
660 ||' adjusted amount: '||l_dep_adj_amount);
661
662 -- get the detail balances for the distribution for l_latest_adj_id
663 OPEN c_det_bal(l_latest_adj_id,
664 l_asset_id,
665 l_book_type_code,
666 l_dist_id);
667 FETCH c_det_bal INTO l_det_bal;
668 IF c_det_bal%NOTFOUND THEN
669 RAISE NO_DATA_FOUND ;
670 END IF ;
671 CLOSE c_det_bal;
672 -- round dep_adj_amount
673 l_ret := igi_iac_common_utils.iac_round(l_dep_adj_amount,
674 l_book_type_code);
675 -- maintain the diff due to rounding
676 l_round_diff := l_round_diff + l_dep_adj_amount;
677
678 IF (l_dist_idx = l_det_table.LAST) THEN
679 -- add rounding diff to the last distribution
680 l_dep_adj_amount := l_dep_adj_amount + (l_iac_new_deprn_adj_amt - l_round_diff);
681 END IF;
682
683 -- calcluate the new depreciation figures
684 l_new_dist_dep_prd := l_dep_adj_amount;
685 l_new_dist_dep_rsv := l_det_bal.deprn_reserve + l_dep_adj_amount;
686 l_new_dist_dep_ytd := l_det_bal.deprn_ytd + l_dep_adj_amount;
687
688 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
689 p_full_path => l_path_name,
690 p_string => 'new dist deprn period: '||l_new_dist_dep_prd);
691 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
692 p_full_path => l_path_name,
693 p_string => 'new dist deprn reserve: '||l_new_dist_dep_rsv);
694 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
695 p_full_path => l_path_name,
696 p_string => 'new dist deprn ytd: '||l_new_dist_dep_ytd);
697
698 -- added 2, 16/12/2003
699 -- added gen fund, and change IF condition
700 -- IF (l_det_bal.adjustment_cost > 0) THEN
701 IF (l_iac_asset_bal.cumulative_reval_factor >= 1) THEN
702 l_new_dist_rr_gf := l_det_bal.reval_reserve_gen_fund + l_dep_adj_amount;
703 -- l_new_dist_rr_net := l_det_bal.reval_reserve_net + l_dep_adj_amount;
704 l_new_dist_rr_net := l_det_bal.reval_reserve_cost -
705 ( l_det_bal.reval_reserve_backlog + l_new_dist_rr_gf);
706
707 l_new_dist_gf_per := l_new_dist_dep_prd;
708 l_new_dist_gf_acc := l_det_bal.general_fund_acc + l_dep_adj_amount;
709 ELSE
710 l_new_dist_rr_gf := l_det_bal.reval_reserve_gen_fund;
711 l_new_dist_rr_net := l_det_bal.reval_reserve_net;
712 l_new_dist_gf_per := l_det_bal.general_fund_per;
713 l_new_dist_gf_acc := l_det_bal.general_fund_acc;
714 END IF;
715 -- calculate the nbv
716 -- l_new_dist_nbv := l_det_bal.net_book_value - l_dep_adj_amount;
717 l_new_dist_nbv := l_det_bal.adjustment_cost -
718 (l_new_dist_dep_rsv + l_det_bal.deprn_reserve_backlog);
719 -- insert the row into igi_iac_det_balances
720 IF p_asset_iac_adj_info.period_counter_fully_reserved IS NOT NULL THEN
721 l_iac_deprn_period_amount := 0;
722 ELSE
723 l_iac_deprn_period_amount := l_new_dist_dep_prd;
724 END IF;
725
726 IGI_IAC_DET_BALANCES_PKG.Insert_Row(
727 x_rowid => l_rowid,
728 x_adjustment_id => l_new_adj_id,
729 x_asset_id => l_asset_id,
730 x_book_type_code => l_book_type_code,
731 x_distribution_id => l_dist_id,
732 x_period_counter => l_last_period_counter,
733 x_adjustment_cost => l_det_bal.adjustment_cost,
734 x_net_book_value => l_new_dist_nbv, -- l_det_bal.net_book_value,
735 x_reval_reserve_cost => l_det_bal.reval_reserve_cost,
739 x_operating_acct_cost => l_det_bal.operating_acct_cost,
736 x_reval_reserve_backlog => l_det_bal.reval_reserve_backlog,
737 x_reval_reserve_gen_fund => l_new_dist_rr_gf, -- l_det_bal.reval_reserve_gen_fund,
738 x_reval_reserve_net => l_new_dist_rr_net, -- l_det_bal.reval_reserve_net,
740 x_operating_acct_backlog => l_det_bal.operating_acct_backlog,
741 x_operating_acct_net => l_det_bal.operating_acct_net,
742 x_operating_acct_ytd => l_det_bal.operating_acct_ytd,
743 x_deprn_period => l_iac_deprn_period_amount,
744 x_deprn_ytd => l_new_dist_dep_ytd,
745 x_deprn_reserve => l_new_dist_dep_rsv,
746 x_deprn_reserve_backlog => l_det_bal.deprn_reserve_backlog,
747 x_general_fund_per => l_new_dist_gf_per, -- l_det_bal.general_fund_per,
748 x_general_fund_acc => l_new_dist_gf_acc, -- l_det_bal.general_fund_acc,
749 x_last_reval_date => l_det_bal.last_reval_date,
750 x_current_reval_factor => l_det_bal.current_reval_factor,
751 x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
752 x_active_flag => null,
753 x_mode => 'R'
754 );
755
756 -- create accounting entries only if iac adjustment is not equal to zero
757 IF (l_dep_adj_amount <> 0) THEN
758 -- find the ccid for EXPENSE
759 IF NOT igi_iac_common_utils.get_account_ccid(l_book_type_code,
760 l_asset_id,
761 l_det_bal.distribution_id,
762 'DEPRN_EXPENSE_ACCT',
763 l_ccid)
764 THEN
765 RAISE e_no_ccid_found;
766 END IF;
767
768 -- insert into igi_iac_adjustments for EXPENSE
769 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
770 x_rowid => l_rowid,
771 x_adjustment_id => l_new_adj_id,
772 x_book_type_code => l_book_type_code,
773 x_code_combination_id => l_ccid,
774 x_set_of_books_id => l_sob_id,
775 x_dr_cr_flag => 'DR',
776 x_amount => l_new_dist_dep_prd,
777 x_adjustment_type => 'EXPENSE',
778 x_transfer_to_gl_flag => 'Y',
779 x_units_assigned => l_units_assigned,
780 x_asset_id => l_asset_id,
781 x_distribution_id => l_det_bal.distribution_id,
782 x_period_counter => l_last_period_counter,
783 x_adjustment_offset_type => 'RESERVE',
784 x_report_ccid => Null,
785 x_mode => 'R',
786 x_event_id => p_event_id
787 );
788
789 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
790 p_full_path => l_path_name,
791 p_string => 'EXPENSE ccid: '||l_ccid);
792
793 -- find the ccid for RESERVE
794 IF NOT igi_iac_common_utils.get_account_ccid(l_book_type_code,
795 l_asset_id,
796 l_det_bal.distribution_id,
797 'DEPRN_RESERVE_ACCT',
798 l_ccid)
799 THEN
800 RAISE e_no_ccid_found;
801 END IF;
802
803 -- insert into igi_iac_adjustments for RESERVE
804 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
805 x_rowid => l_rowid,
806 x_adjustment_id => l_new_adj_id,
807 x_book_type_code => l_book_type_code,
808 x_code_combination_id => l_ccid,
809 x_set_of_books_id => l_sob_id,
810 x_dr_cr_flag => 'CR',
811 x_amount => l_new_dist_dep_prd,
812 x_adjustment_type => 'RESERVE',
813 x_transfer_to_gl_flag => 'Y',
814 x_units_assigned => l_units_assigned,
815 x_asset_id => l_asset_id,
816 x_distribution_id => l_det_bal.distribution_id,
817 x_period_counter => l_last_period_counter,
818 x_adjustment_offset_type => 'EXPENSE',
819 x_report_ccid => Null,
820 x_mode => 'R',
821 x_event_id => p_event_id
822 );
823
824 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
825 p_full_path => l_path_name,
826 p_string => 'RESERVE ccid: '||l_ccid);
827
828 IF (l_iac_asset_bal.cumulative_reval_factor >= 1) THEN
829 -- find the ccid for REVAL RESERVE
830 IF NOT igi_iac_common_utils.get_account_ccid(l_book_type_code,
834 l_reval_rsv_ccid)
831 l_asset_id,
832 l_det_bal.distribution_id,
833 'REVAL_RESERVE_ACCT',
835 THEN
836 RAISE e_no_ccid_found;
837 END IF;
838
839 -- insert into igi_iac_adjustments for EXPENSE
840 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
841 x_rowid => l_rowid,
842 x_adjustment_id => l_new_adj_id,
843 x_book_type_code => l_book_type_code,
844 x_code_combination_id => l_reval_rsv_ccid,
845 x_set_of_books_id => l_sob_id,
846 x_dr_cr_flag => 'DR',
847 x_amount => l_new_dist_dep_prd,
848 x_adjustment_type => 'REVAL RESERVE',
849 x_transfer_to_gl_flag => 'Y',
850 x_units_assigned => l_units_assigned,
851 x_asset_id => l_asset_id,
852 x_distribution_id => l_det_bal.distribution_id,
853 x_period_counter => l_last_period_counter,
854 x_adjustment_offset_type => 'GENERAL FUND',
855 x_report_ccid => Null ,
856 x_mode => 'R',
857 x_event_id => p_event_id
858 );
859
860 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
861 p_full_path => l_path_name,
862 p_string => 'EXPENSE ccid: '||l_ccid);
863
864 -- find the ccid for GENERAL FUND
865 IF NOT igi_iac_common_utils.get_account_ccid(l_book_type_code,
866 l_asset_id,
867 l_det_bal.distribution_id,
868 'GENERAL_FUND_ACCT',
869 l_ccid)
870 THEN
871 RAISE e_no_ccid_found;
872 END IF;
873
874 -- insert into igi_iac_adjustments for EXPENSE
875 IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
876 x_rowid => l_rowid,
877 x_adjustment_id => l_new_adj_id,
878 x_book_type_code => l_book_type_code,
879 x_code_combination_id => l_ccid,
880 x_set_of_books_id => l_sob_id,
881 x_dr_cr_flag => 'CR',
882 x_amount => l_new_dist_dep_prd,
883 x_adjustment_type => 'GENERAL FUND',
884 x_transfer_to_gl_flag => 'Y',
885 x_units_assigned => l_units_assigned,
886 x_asset_id => l_asset_id,
887 x_distribution_id => l_det_bal.distribution_id,
888 x_period_counter => l_last_period_counter,
889 x_adjustment_offset_type => 'REVAL RESERVE',
890 x_report_ccid => l_reval_rsv_ccid ,
891 x_mode => 'R',
892 x_event_id => p_event_id
893 );
894
895 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
896 p_full_path => l_path_name,
897 p_string => 'RESERVE ccid: '||l_ccid);
898
899 END IF; -- reval factor >= 1
900 END IF; -- if iac deprn adjustment amount <> 0
901 END LOOP;
902
903 -- do the igi_iac_fa_deprn active distributions
904 FOR l_fa_idx IN p_asset_iac_dist_info.FIRST.. p_asset_iac_dist_info.LAST LOOP
905 l_fa_deprn_adj_amount := p_asset_iac_dist_info(l_fa_idx).deprn_adjustment_amount;
906
907 -- calculate the IAC maintained YTD figures
908 OPEN c_get_fa_iac_deprn(p_asset_iac_dist_info(l_fa_idx).distribution_id,
909 l_latest_adj_id);
910
911 FETCH c_get_fa_iac_deprn INTO l_fa_deprn_ytd, l_fa_deprn_period, l_fa_deprn_reserve;
912 IF c_get_fa_iac_deprn%NOTFOUND THEN
913 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
914 p_full_path => l_path_name,
915 p_string => 'Fetching FA deprn detail values for IGI_IAC_FA_DEPRN');
916 l_fa_deprn_period := p_asset_iac_dist_info(l_fa_idx).deprn_adjustment_amount;
917 l_fa_deprn_ytd := p_asset_iac_dist_info(l_fa_idx).ytd_deprn;
918 l_fa_deprn_reserve := p_asset_iac_dist_info(l_fa_idx).deprn_reserve;
919 ELSE
920 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
921 p_full_path => l_path_name,
922 p_string => 'Fetching IAC FA deprn detail values for IGI_IAC_FA_DEPRN');
923 l_fa_deprn_period := l_fa_deprn_adj_amount;
924 l_fa_deprn_ytd := l_fa_deprn_ytd + l_fa_deprn_adj_amount;
925 l_fa_deprn_reserve := l_fa_deprn_reserve + l_fa_deprn_adj_amount;
926 END IF;
927 CLOSE c_get_fa_iac_deprn;
928
929 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
930 p_full_path => l_path_name,
934 p_string => 'new FA dist deprn reserve: '||l_fa_deprn_reserve);
931 p_string => 'new FA dist deprn period: '||l_fa_deprn_period);
932 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
933 p_full_path => l_path_name,
935 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
936 p_full_path => l_path_name,
937 p_string => 'new FA dist deprn ytd: '||l_fa_deprn_ytd);
938
939 IF p_asset_iac_adj_info.period_counter_fully_reserved IS NOT NULL THEN
940 l_fa_deprn_period_amount := 0;
941 ELSE
942 l_fa_deprn_period_amount := l_fa_deprn_period;
943 END IF;
944 -- insert into igi_iac_fa_deprn
945 IGI_IAC_FA_DEPRN_PKG.Insert_Row(
946 x_rowid => l_rowid,
947 x_book_type_code => l_book_type_code,
948 x_asset_id => l_asset_id,
949 x_period_counter => l_last_period_counter,
950 x_adjustment_id => l_new_adj_id,
951 x_distribution_id => p_asset_iac_dist_info(l_fa_idx).distribution_id,
952 x_deprn_period => l_fa_deprn_period_amount,
953 x_deprn_ytd => l_fa_deprn_ytd,
954 x_deprn_reserve => l_fa_deprn_reserve,
955 x_active_flag => null,
956 x_mode => 'R'
957 );
958
959 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
960 p_full_path => l_path_name,
961 p_string => 'IAC FA Deprn row inserted: '||l_new_adj_id);
962
963 END LOOP;
964
965 -- Roll YTD rows forward
966 -- Carry forward any inactive distributions from the previous adjustment to the new adjustment
967 igi_iac_adj_cost_reval_pkg.Roll_YTD_Forward(l_asset_id,
968 l_book_type_code,
969 l_latest_adj_id,
970 l_new_adj_id,
971 l_last_period_counter);
972
973 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
974 p_full_path => l_path_name,
975 p_string => 'YTD rows rolled forward');
976
977 -- check if a row exists in igi_iac_asset_balances for l_last_period_counter
978 -- if it does then update it else insert a new row
979 SELECT count(*)
980 INTO l_exists
981 FROM igi_iac_asset_balances
982 WHERE asset_id = l_asset_id
983 AND book_type_code = l_book_type_code
984 AND period_counter = l_last_period_counter;
985
986 -- Update row in IGI_IAC_ASSET_BALANCES for the asset for the new revalued iac cost using
987 -- IGI_IAC_ASSET_BALANCES.Update_Row
988
989 IF p_asset_iac_adj_info.period_counter_fully_reserved IS NOT NULL THEN
990 l_iac_deprn_period_amount := 0;
991 ELSE
992 l_iac_deprn_period_amount := l_iac_new_deprn_period;
993 END IF;
994
995 IF (l_exists > 0) THEN
996 IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
997 X_asset_id => l_asset_id,
998 X_book_type_code => l_book_type_code,
999 X_period_counter => l_iac_asset_bal.period_counter,
1000 X_net_book_value => l_iac_new_nbv, -- l_iac_asset_bal.net_book_value,
1001 X_adjusted_cost => l_iac_asset_bal.adjusted_cost,
1002 X_operating_acct => l_iac_asset_bal.operating_acct,
1003 X_reval_reserve => l_iac_new_rr, -- l_iac_asset_bal.reval_reserve,
1004 X_deprn_amount => l_iac_deprn_period_amount,
1005 X_deprn_reserve => l_iac_new_deprn_reserve,
1006 X_backlog_deprn_reserve => l_iac_asset_bal.backlog_deprn_reserve,
1007 X_general_fund => l_iac_new_gf, -- l_iac_asset_bal.general_fund,
1008 X_last_reval_date => l_iac_asset_bal.last_reval_date,
1009 X_current_reval_factor => l_iac_asset_bal.current_reval_factor,
1010 X_cumulative_reval_factor => l_iac_asset_bal.cumulative_reval_factor
1011 ) ;
1012 ELSE
1013 -- insert a row for the last period counter
1014 IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
1015 X_rowid => l_rowid,
1016 X_asset_id => l_asset_id,
1017 X_book_type_code => l_book_type_code,
1018 X_period_counter => l_last_period_counter,
1019 X_net_book_value => l_iac_new_nbv, -- l_iac_asset_bal.net_book_value,
1020 X_adjusted_cost => l_iac_asset_bal.adjusted_cost,
1021 X_operating_acct => l_iac_asset_bal.operating_acct,
1022 X_reval_reserve => l_iac_new_rr, -- l_iac_asset_bal.reval_reserve,
1023 X_deprn_amount => l_iac_deprn_period_amount,
1024 X_deprn_reserve => l_iac_new_deprn_reserve,
1025 X_backlog_deprn_reserve => l_iac_asset_bal.backlog_deprn_reserve,
1026 X_general_fund => l_iac_new_gf, -- l_iac_asset_bal.general_fund,
1027 X_last_reval_date => l_iac_asset_bal.last_reval_date,
1028 X_current_reval_factor => l_iac_asset_bal.current_reval_factor,
1029 X_cumulative_reval_factor => l_iac_asset_bal.cumulative_reval_factor
1030 ) ;
1031 END IF;
1032
1033 -- successful completion
1034 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1035 p_full_path => l_path_name,
1036 p_string => 'Adjustment Amortized Depreciation Reval completed successfully!!!');
1037 -- rollback;
1038 RETURN TRUE;
1039
1040 EXCEPTION
1041 WHEN e_latest_trx_not_avail THEN
1042 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1043 p_full_path => l_path_name,
1044 p_string => 'Latest transaction for the asset could not be retrieved');
1045 FA_SRVR_MSG.add_message(
1046 Calling_Fn => g_calling_fn,
1047 Name => 'IGI_IAC_NO_LATEST_TRX');
1048 RETURN FALSE;
1049
1050 WHEN e_no_period_info_avail THEN
1051 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1052 p_full_path => l_path_name,
1053 p_string => 'No open period information available for the book');
1054 FA_SRVR_MSG.add_message(
1055 Calling_Fn => g_calling_fn,
1056 Name => 'IGI_IAC_NO_PERIOD_INFO');
1057 RETURN FALSE;
1058
1059 WHEN e_no_gl_info THEN
1060 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1061 p_full_path => l_path_name,
1062 p_string => 'Could not retrive GL information for Book');
1063 FA_SRVR_MSG.add_message(
1064 Calling_Fn => g_calling_fn,
1065 Name => 'IGI_IAC_NO_GL_INFO');
1066 RETURN FALSE;
1067
1068 WHEN e_no_ccid_found THEN
1069 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1070 p_full_path => l_path_name,
1071 p_string => 'CCID could not be found');
1072 FA_SRVR_MSG.add_message(
1073 Calling_Fn => g_calling_fn,
1074 Name => 'IGI_IAC_WF_FAILED_CCID');
1075 RETURN FALSE;
1076
1077 WHEN e_no_proration THEN
1078 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1079 p_full_path => l_path_name,
1080 p_string => 'Amount could not be prorated among the distributions');
1081 FA_SRVR_MSG.add_message(
1082 Calling_Fn => g_calling_fn,
1083 Name => 'IGI_IAC_NO_PRORATION');
1084 RETURN FALSE;
1085
1086 WHEN e_asset_life_err THEN
1087 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1088 p_full_path => l_path_name,
1089 p_string => 'Asset life could not be checked');
1090 FA_SRVR_MSG.add_message(
1091 Calling_Fn => g_calling_fn,
1092 Name => 'IGI_IAC_ASSET_LIFE_ERR');
1093 RETURN FALSE;
1094
1095 WHEN e_non_dep_asset THEN
1096 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1097 p_full_path => l_path_name,
1098 p_string => 'No further processing for non-depreciating asset');
1099 FA_SRVR_MSG.add_message(
1100 Calling_Fn => g_calling_fn,
1101 Name => 'IGI_IAC_NON_DEP_ASSET',
1102 Token1 => 'ASSET_NUM',
1103 Value1 => g_asset_num);
1104 RETURN TRUE;
1105
1106 WHEN e_curr_period_amort THEN
1107 igi_iac_debug_pkg.debug_other_string(p_level => g_excep_level,
1108 p_full_path => l_path_name,
1109 p_string => 'No further processing for current period amortized asset');
1110 FA_SRVR_MSG.add_message(
1111 Calling_Fn => g_calling_fn,
1112 Name => 'IGI_IAC_CURR_PRD_AMORT',
1113 Token1 => 'ASSET_NUM',
1114 Value1 => g_asset_num);
1115 RETURN TRUE;
1116
1117 WHEN others THEN
1118 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1119 FA_SRVR_MSG.add_sql_error(Calling_Fn => g_calling_fn);
1120 RETURN FALSE;
1121
1122 END Do_Amort_Deprn_Reval;
1123
1124 BEGIN
1125 l_calling_function := '***Amort***';
1126 g_calling_fn := 'IGI_IAC_ADJ_AMORT_PKG.Do_Amort_Deprn_Reval';
1127
1128 --===========================FND_LOG.START=====================================
1129 g_state_level := FND_LOG.LEVEL_STATEMENT;
1130 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
1131 g_event_level := FND_LOG.LEVEL_EVENT;
1132 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
1133 g_error_level := FND_LOG.LEVEL_ERROR;
1134 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
1135 g_path := 'IGI.PLSQL.igiiadab.igi_iac_adj_amort_pkg.';
1136 --===========================FND_LOG.END=====================================
1137
1138 END IGI_IAC_ADJ_AMORT_PKG;