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