[Home] [Help]
PACKAGE BODY: APPS.FA_RETIREMENT_PVT
Source
1 PACKAGE BODY FA_RETIREMENT_PVT as
2 /* $Header: FAVRETB.pls 120.55.12010000.6 2008/12/04 10:01:07 dvjoshi ship $ */
3
4 -- +++++ Global Varialbes +++++
5 --Adding for 3440308
6 g_current_units number:=0;
7
8 -- +++++ Forward Declarations +++++
9 FUNCTION CALC_GAIN_LOSS_FOR_RET(
10 p_trans_rec IN FA_API_TYPES.trans_rec_type,
11 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
12 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
13 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
14 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
15 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
16 p_period_rec IN FA_API_TYPES.period_rec_type,
17 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
18 p_group_thid IN NUMBER,
19 p_salvage_value_retired IN NUMBER,
20 p_mrc_sob_type_code IN VARCHAR2,
21 p_mode IN VARCHAR2,
22 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
23 RETURN BOOLEAN;
24
25 FUNCTION CALC_GAIN_LOSS_FOR_REI(
26 p_trans_rec IN FA_API_TYPES.trans_rec_type,
27 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
28 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
29 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
30 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
31 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
32 p_period_rec IN FA_API_TYPES.period_rec_type,
33 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
34 p_group_thid IN NUMBER,
35 x_asset_fin_rec OUT NOCOPY FA_API_TYPES.asset_fin_rec_type,
36 p_mrc_sob_type_code IN VARCHAR2,
37 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
38 RETURN BOOLEAN;
39 -- +++++ End of Forward Declarations +++++
40
41 FUNCTION DO_RETIREMENT(p_trans_rec IN FA_API_TYPES.trans_rec_type,
42 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
43 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
44 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
45 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
46 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
47 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
48 p_period_rec IN FA_API_TYPES.period_rec_type,
49 p_mrc_sob_type_code IN VARCHAR2,
50 p_calling_fn IN VARCHAR2,
51 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
52 return boolean IS
53
54 l_trans_rec FA_API_TYPES.trans_rec_type;
55 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
56
57 CURSOR c_get_group_exp_row IS
58 select adjustment_type,
59 debit_credit_flag,
60 adjustment_amount,
61 period_counter_adjusted,
62 period_counter_created
63 from fa_adjustments
64 where asset_id = l_asset_hdr_rec.asset_id
65 and book_type_code = l_asset_hdr_rec.book_type_code
66 and transaction_header_id = l_trans_rec.transaction_header_id
67 and source_type_code = 'DEPRECIATION';
68
69 CURSOR c_member_exists IS
70 select 'Y'
71 from fa_books
72 where book_type_code = p_asset_hdr_rec.book_type_code
73 and group_asset_id = p_asset_fin_rec.group_asset_id
74 and period_counter_fully_retired is null
75 and transaction_header_id_out is null;
76
77 CURSOR c_get_thid is
78 select fa_transaction_headers_s.nextval
79 from dual;
80
81 CURSOR c_get_primary_thid is
82 select transaction_header_id
83 from fa_transaction_headers
84 where asset_id = p_asset_fin_rec.group_asset_id
85 and book_type_code = p_asset_hdr_rec.book_type_code
86 and member_transaction_header_id = p_trans_rec.transaction_header_id;
87
88 l_calling_fn VARCHAR2(100) := 'fa_retirement_pvt.do_retirement';
89
90 l_transaction_header_id FA_TRANSACTION_HEADERS.TRANSACTION_HEADER_ID%TYPE;
91
92 l_asset_desc_rec_m FA_API_TYPES.asset_desc_rec_type;
93 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
94 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
95 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
96 l_asset_fin_rec_new_m FA_API_TYPES.asset_fin_rec_type;
97 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
98 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
99 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
100 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
101 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
102 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
103 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
104
105 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
106
107 l_deprn_exp NUMBER;
108 l_bonus_deprn_exp NUMBER;
109
110 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
111
112 l_member_exists VARCHAR2(1) := 'N';
113 l_deprn_reserve NUMBER;
114 l_temp_num NUMBER;
115 l_temp_char VARCHAR2(30);
116 l_temp_bool BOOLEAN;
117
118 l_new_reserve NUMBER;
119 l_new_rec_cost NUMBER;
120 l_recapture_amount NUMBER;
121
122 l_alloc_amount NUMBER; -- Used to pass amount to allocate across
123 -- member assets
124
125 l_rowid ROWID; -- temp variable
126 l_cglfr_mode VARCHAR2(2); --For CALC_GAIN_LOSS_FOR_RET
127 ret_err EXCEPTION;
128
129 BEGIN
130 if (p_log_level_rec.statement_level) then
131 fa_debug_pkg.add(l_calling_fn, 'Begin',
132 p_asset_hdr_rec.asset_id||':'||p_asset_fin_rec.group_asset_id
133 ,p_log_level_rec => p_log_level_rec);
134 end if;
135
136 --Adding to bypass the issue in 3440308 (case where an equal # of units is
137 --retired as is left in the asset.
138 --This is required because we're resetting asset_desc_rec below
139 g_current_units := nvl(p_asset_desc_rec.current_units,0);
140
141 -- Get asset description for member asset
142 -- p_asset_desc may be null
143 if not FA_UTIL_PVT.get_asset_desc_rec (
144 p_asset_hdr_rec => p_asset_hdr_rec,
145 px_asset_desc_rec => l_asset_desc_rec_m,
146 p_log_level_rec => p_log_level_rec) then
147 raise ret_err;
148 end if;
149
150 if (p_mrc_sob_type_code <> 'R') then
151 OPEN c_get_thid;
152 FETCH c_get_thid INTO l_trans_rec.transaction_header_id;
153 CLOSE c_get_thid;
154 else
155 OPEN c_get_primary_thid;
156 FETCH c_get_primary_thid INTO l_trans_rec.transaction_header_id;
157 CLOSE c_get_primary_thid;
158 end if;
159
160 --
161 -- Prepare to call FA_ADJUSTMENT_PVT.do_adjustment to process group
162 -- asset after member asset retirement.
163 --
164
165 l_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
166 l_trans_rec.transaction_subtype := 'AMORTIZED';
167 l_trans_rec.transaction_date_entered := p_trans_rec.transaction_date_entered;
168 l_trans_rec.amortization_start_date := p_trans_rec.transaction_date_entered;
169 l_trans_rec.transaction_key := 'MR';
170 l_trans_rec.who_info.creation_date := p_trans_rec.who_info.creation_date;
171 l_trans_rec.who_info.created_by := p_trans_rec.who_info.created_by;
172 l_trans_rec.who_info.last_update_date := p_trans_rec.who_info.last_update_date;
173 l_trans_rec.who_info.last_updated_by := p_trans_rec.who_info.last_updated_by;
174 l_trans_rec.who_info.last_update_login := p_trans_rec.who_info.last_update_login;
175 l_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
176 l_trans_rec.mass_transaction_id := p_trans_rec.mass_transaction_id;
177 l_trans_rec.calling_interface := p_trans_rec.calling_interface;
178 l_trans_rec.mass_reference_id := p_trans_rec.mass_reference_id;
179 --Bug# 7198185 need to pass event_id to populate event_id for group in case of member retirement.
180 l_trans_rec.event_id := p_trans_rec.event_id;
181
182 l_asset_hdr_rec.asset_id := p_asset_fin_rec.group_asset_id;
183 l_asset_hdr_rec.book_type_code := p_asset_hdr_rec.book_type_code;
184 l_asset_hdr_rec.set_of_books_id := p_asset_hdr_rec.set_of_books_id;
185 --l_asset_hdr_rec.period_of_addition := 'N'; -- Guess not necessary
186
187 if not FA_UTIL_PVT.get_asset_type_rec (
188 p_asset_hdr_rec => l_asset_hdr_rec,
189 px_asset_type_rec => l_asset_type_rec
190 ,p_log_level_rec => p_log_level_rec) then
191 raise ret_err;
192 end if;
193
194 if not FA_UTIL_PVT.get_asset_desc_rec (
195 p_asset_hdr_rec => l_asset_hdr_rec,
196 px_asset_desc_rec => l_asset_desc_rec,
197 p_log_level_rec => p_log_level_rec) then
198 raise ret_err;
199 end if;
200
201 if not FA_UTIL_PVT.get_asset_cat_rec (
202 p_asset_hdr_rec => l_asset_hdr_rec,
203 px_asset_cat_rec => l_asset_cat_rec,
204 p_date_effective => null,
205 p_log_level_rec => p_log_level_rec) then
206 raise ret_err;
207 end if;
208
209 if not fa_util_pvt.get_asset_fin_rec (
210 p_asset_hdr_rec => l_asset_hdr_rec,
211 px_asset_fin_rec => l_asset_fin_rec_old,
212 p_mrc_sob_type_code => p_mrc_sob_type_code,
213 p_log_level_rec => p_log_level_rec) then
214 raise ret_err;
215 end if;
216
217 --tk_util.DumpFinRec(l_asset_fin_rec_old, 'OLD');
218
219 if not fa_util_pvt.get_asset_deprn_rec (
220 p_asset_hdr_rec => l_asset_hdr_rec,
221 px_asset_deprn_rec => l_asset_deprn_rec_old,
222 p_mrc_sob_type_code => p_mrc_sob_type_code,
223 p_log_level_rec => p_log_level_rec) then
224 raise ret_err;
225 end if;
226
227 if (p_asset_type_rec.asset_type = 'CIP') then
228 l_asset_fin_rec_adj.cip_cost := -1 * p_asset_retire_rec.cost_retired;
229 else
230 l_asset_fin_rec_adj.cost := -1 * p_asset_retire_rec.cost_retired;
231 end if;
232
233 l_asset_fin_rec_adj.unrevalued_cost := -1 * p_asset_retire_rec.cost_retired;
234 l_asset_fin_rec_adj.ytd_proceeds := p_asset_retire_rec.proceeds_of_sale;
235 l_asset_fin_rec_adj.ltd_proceeds := p_asset_retire_rec.proceeds_of_sale;
236 l_asset_fin_rec_adj.ltd_cost_of_removal := p_asset_retire_rec.cost_of_removal;
237
238 l_asset_deprn_rec_adj.deprn_reserve := -1 * p_asset_retire_rec.reserve_retired;
239
240 -- Get new member's fin_rec
241 if not fa_util_pvt.get_asset_fin_rec (
242 p_asset_hdr_rec => p_asset_hdr_rec,
243 px_asset_fin_rec => l_asset_fin_rec_new_m,
244 p_mrc_sob_type_code => p_mrc_sob_type_code,
245 p_log_level_rec => p_log_level_rec) then
246 raise ret_err;
247 end if;
248
249 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_new_m.salvage_value, 0) -
250 nvl(p_asset_fin_rec.salvage_value, 0);
251
252 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
253 nvl(l_asset_fin_rec_new_m.allowed_deprn_limit_amount, 0) -
254 nvl(p_asset_fin_rec.allowed_deprn_limit_amount, 0);
255 --Need this for CALC_GAIN_LOSS_FOR_RET.
256 if p_calling_fn <> 'DO_RETIREMENT.CGLFR_CR_ONLY' then
257 l_cglfr_mode := 'DR';
258 else
259 l_cglfr_mode := 'CR';
260 end if;
261 --
262 -- Insert COST, RESERVE, PROCEEDS CLR, and REMOVALCOST CLR
263 -- into FA_ADJUSTMENTS.
264 --
265 --tk_util.debug('Before calling CALC_GAIN_LOSS_FOR_RET');
266 if not CALC_GAIN_LOSS_FOR_RET(
267 p_trans_rec => p_trans_rec,
268 p_asset_hdr_rec => p_asset_hdr_rec,
269 p_asset_type_rec => p_asset_type_rec,
270 p_asset_desc_rec => l_asset_desc_rec_m,
271 p_asset_cat_rec => p_asset_cat_rec,
272 p_asset_fin_rec => p_asset_fin_rec,
273 p_period_rec => p_period_rec,
274 p_asset_retire_rec => p_asset_retire_rec,
275 p_group_thid => l_trans_rec.transaction_header_id,
276 p_salvage_value_retired => l_asset_fin_rec_adj.salvage_value,
277 p_mrc_sob_type_code => p_mrc_sob_type_code,
278 p_mode => l_cglfr_mode,
279 p_log_level_rec => p_log_level_rec) then
280 if (p_log_level_rec.statement_level) then
281 fa_debug_pkg.add(l_calling_fn, 'ERROR',
282 'returned from CALC_GAIN_LOSS_FOR_RET',p_log_level_rec);
283 end if;
284
285 raise ret_err;
286 end if;
287 --tk_util.debug('After calling CALC_GAIN_LOSS_FOR_RET');
288
289 -- Do the rest only if not called for credit row (fix for 3188851)
290 if p_calling_fn <> 'DO_RETIREMENT.CGLFR_CR_ONLY' then
291 --
292 -- Process Recapture Excess Reserve
293 --
294 if (nvl(p_asset_fin_rec.recapture_reserve_flag, 'N') = 'Y') then
295
296 if (nvl(p_asset_fin_rec.limit_proceeds_flag, 'N') = 'Y') and
297 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
298 nvl(p_asset_retire_rec.cost_of_removal, 0)) >
299 (p_asset_retire_rec.cost_retired -
300 l_asset_fin_rec_adj.salvage_value) then
301
302 l_new_reserve := l_asset_deprn_rec_old.deprn_reserve +
303 (p_asset_retire_rec.cost_retired -
304 l_asset_fin_rec_adj.salvage_value) -
305 p_asset_retire_rec.cost_retired;
306 else
307
308 l_new_reserve := l_asset_deprn_rec_old.deprn_reserve +
309 nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
310 p_asset_retire_rec.cost_retired -
311 nvl(p_asset_retire_rec.cost_of_removal, 0);
312
313 end if;
314
315 l_new_rec_cost := l_asset_fin_rec_old.recoverable_cost -
316 (p_asset_retire_rec.cost_retired -
317 l_asset_fin_rec_adj.salvage_value);
318
319 if (l_new_rec_cost < l_new_reserve) then
320 l_adj.adjustment_amount := l_new_reserve - l_new_rec_cost;
321
322 if (p_log_level_rec.statement_level) then
323 fa_debug_pkg.add(l_calling_fn, 'Recapture Excess Reserve',
324 l_adj.adjustment_amount,p_log_level_rec);
325 end if;
326
327 if p_asset_type_rec.asset_type = 'CIP' then
328 l_adj.source_type_code := 'CIP RETIREMENT';
329 else
330 l_adj.source_type_code := 'RETIREMENT';
331 end if;
332
333 l_adj.asset_id := l_asset_hdr_rec.asset_id;
334 l_adj.transaction_header_id := l_trans_rec.transaction_header_id;
335 l_adj.book_type_code := l_asset_hdr_rec.book_type_code;
336 l_adj.period_counter_created := p_period_rec.period_counter;
337 l_adj.period_counter_adjusted := p_period_rec.period_counter;
338 l_adj.current_units := l_asset_desc_rec.current_units;
339 l_adj.selection_retid := 0;
340 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
341 l_adj.leveling_flag := TRUE;
342 l_adj.flush_adj_flag := FALSE;
343 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
344 l_adj.gen_ccid_flag := TRUE;
345 l_adj.adjustment_type := 'RESERVE';
346 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
347 l_adj.debit_credit_flag := 'DR';
348
349 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
350 l_asset_cat_rec.category_id,
351 p_log_level_rec) then
352 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
353 p_log_level_rec => p_log_level_rec);
354 raise ret_err;
355 end if;
356
357 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
358
359 if not FA_INS_ADJUST_PKG.faxinaj
360 (l_adj,
361 p_trans_rec.who_info.last_update_date,
362 p_trans_rec.who_info.last_updated_by,
363 p_trans_rec.who_info.last_update_login,
364 p_log_level_rec) then
365 raise ret_err;
366 end if;
367
368 l_adj.adjustment_type := 'NBV RETIRED';
369 l_adj.adjustment_amount := l_new_reserve - l_new_rec_cost;
370 l_adj.flush_adj_flag := TRUE;
371
372 l_adj.debit_credit_flag := 'CR';
373 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
374 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
375
376 if not FA_INS_ADJUST_PKG.faxinaj
377 (l_adj,
378 p_trans_rec.who_info.last_update_date,
379 p_trans_rec.who_info.last_updated_by,
380 p_trans_rec.who_info.last_update_login,
381 p_log_level_rec) then
382 raise ret_err;
383 end if;
384
385 l_recapture_amount := l_new_reserve - l_new_rec_cost;
386 else
387 l_recapture_amount := 0;
388
389 end if; -- (l_new_rec_cost < l_new_reserve)
390
391 else
392
393 l_recapture_amount := 0;
394
395 end if; -- (nvl(p_asset_fin_rec.recapture_reserve_flag, 'N') = 'Y') and
396
397 if not FA_TRX_APPROVAL_PKG.faxcat
398 (X_book => l_asset_hdr_rec.book_type_code,
399 X_asset_id => l_asset_hdr_rec.asset_id,
400 X_trx_type => l_trans_rec.transaction_type_code,
401 X_trx_date => l_trans_rec.transaction_date_entered,
402 X_init_message_flag => 'NO',
403 p_log_level_rec => p_log_level_rec) then
404
405 if (p_log_level_rec.statement_level) then
406 fa_debug_pkg.add(l_calling_fn, 'Calling FA_TRX_APPROVAL_PKG.faxcat', 'Failed',p_log_level_rec);
407 end if;
408
409 raise ret_err;
410 end if;
411 /* Added for bug 7537762 */
412 if not fa_util_pvt.get_asset_deprn_rec (
413 p_asset_hdr_rec => l_asset_hdr_rec,
414 px_asset_deprn_rec => l_asset_deprn_rec_old,
415 p_mrc_sob_type_code => p_mrc_sob_type_code,
416 p_log_level_rec => p_log_level_rec) then
417 raise ret_err;
418 end if;
419
420 -- ENERGY
421 if (not fa_cache_pkg.fazccmt(l_asset_fin_rec_old.deprn_method_code,
422 l_asset_fin_rec_old.life_in_months,
423 p_log_level_rec)) then
424 if (p_log_level_rec.statement_level) then
425 fa_debug_pkg.add(l_calling_fn, 'Error calling', 'fa_cache_pkg.fazccmt',p_log_level_rec);
426 end if;
427
428 raise ret_err;
429 end if;
430
431 -- BUG# 6899255
432 -- handle all allocate cases the same way rather than just energy:
433 -- (l_group_db_rule_name = 'ENERGY PERIOD END BALANCE')
434
435 if (nvl(l_asset_fin_rec_old.tracking_method, 'NO TRACK') = 'ALLOCATE') then -- ENERGY
436 l_asset_deprn_rec_adj.deprn_reserve := - 1 * (p_asset_retire_rec.cost_retired -
437 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
438 nvl(p_asset_retire_rec.cost_of_removal, 0)));
439 end if;
440 -- ENERGY
441
442 if (p_log_level_rec.statement_level) then
443 fa_debug_pkg.add(l_calling_fn, 'Call FA_ADJUSTMENT_PVT.do_adjustment1',
444 'Begin',p_log_level_rec);
445 end if;
446
447 if not FA_ADJUSTMENT_PVT.do_adjustment
448 (px_trans_rec => l_trans_rec,
449 px_asset_hdr_rec => l_asset_hdr_rec,
450 p_asset_desc_rec => l_asset_desc_rec,
451 p_asset_type_rec => l_asset_type_rec,
452 p_asset_cat_rec => l_asset_cat_rec,
453 p_asset_fin_rec_old => l_asset_fin_rec_old,
454 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
455 x_asset_fin_rec_new => l_asset_fin_rec_new,
456 p_inv_trans_rec => l_inv_trans_rec,
457 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
458 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
459 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
460 p_period_rec => p_period_rec,
461 p_mrc_sob_type_code => p_mrc_sob_type_code,
462 p_group_reclass_options_rec => l_group_reclass_options_rec,
463 p_calling_fn => l_calling_fn,
464 p_log_level_rec => p_log_level_rec) then
465
466 if (p_log_level_rec.statement_level) then
467 fa_debug_pkg.add(l_calling_fn, 'Calling FA_ADJUSTMENT_PVT.do_adjustment', 'Failed',p_log_level_rec);
468 end if;
469
470 raise ret_err;
471 end if;
472
473 if (p_log_level_rec.statement_level) then
474 fa_debug_pkg.add(l_calling_fn, 'FA_ADJUSTMENT_PVT.do_adjustment', 'End',p_log_level_rec);
475 end if;
476
477 if (p_trans_rec.calling_interface <> 'FAXASSET') and
478 (nvl(l_asset_fin_rec_new.cost, 0) <> 0) and
479 (nvl(l_asset_fin_rec_new.tracking_method, 'NO TRACK') = 'ALLOCATE') and
480 (fa_cache_pkg.fazcdrd_record.rule_name = 'ENERGY PERIOD END BALANCE') then
481
482 l_alloc_amount := p_asset_retire_rec.cost_retired -
483 p_asset_retire_rec.reserve_retired -
484 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
485 nvl(p_asset_retire_rec.cost_of_removal, 0));
486
487 l_asset_deprn_rec_new.deprn_reserve := l_asset_deprn_rec_new.deprn_reserve -
488 (p_asset_retire_rec.cost_retired -
489 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
490 nvl(p_asset_retire_rec.cost_of_removal, 0)));
491
492 if (p_log_level_rec.statement_level) then
493 fa_debug_pkg.add(l_calling_fn, 'Calling Do_Allocation', l_alloc_amount,p_log_level_rec);
494 fa_debug_pkg.add(l_calling_fn, 'Calling Do_Allocation', p_asset_retire_rec.proceeds_of_sale,p_log_level_rec);
495 fa_debug_pkg.add(l_calling_fn, 'Calling Do_Allocation', p_asset_retire_rec.cost_of_removal,p_log_level_rec);
496 fa_debug_pkg.add(l_calling_fn, 'Calling Do_Allocation', p_asset_retire_rec.reserve_retired,p_log_level_rec);
497 end if;
498
499 if not FA_RETIREMENT_PVT.Do_Allocation(
500 p_trans_rec => l_trans_rec,
501 p_asset_hdr_rec => l_asset_hdr_rec,
502 p_asset_fin_rec => l_asset_fin_rec_new,
503 p_asset_deprn_rec_new => l_asset_deprn_rec_new, -- group new deprn rec
504 p_period_rec => p_period_rec,
505 p_reserve_amount => l_alloc_amount,
506 p_mrc_sob_type_code => p_mrc_sob_type_code,
507 p_calling_fn => l_calling_fn,
508 p_log_level_rec => p_log_level_rec) then
509 if (p_log_level_rec.statement_level) then
510 fa_debug_pkg.add(l_calling_fn, 'Calling FA_ADJUSTMENT_PVT.do_adjustment', 'Failed',p_log_level_rec);
511 end if;
512
513 raise ret_err;
514 end if;
515 end if; -- (nvl(l_asset_fin_rec_new.cost, 0) <> 0) and
516
517 --
518 -- Set status of retirement to PROCESSED so Gain Loss program won't pick this
519 -- retirement up later. This cannot be done at the time of insert since
520 -- Distribution API called after FA_RETIREMENT_PUB.do_sub_regular_retirement
521 -- is expecting the status to be PENDING.
522 --
523 if (p_log_level_rec.statement_level) then
524 fa_debug_pkg.add(l_calling_fn, 'Call FA_RETIREMENTS_PKG.Update_Row', 'Begin',p_log_level_rec);
525 end if;
526
527 FA_RETIREMENTS_PKG.Update_Row(
528 X_Rowid => l_rowid,
529 X_Retirement_Id => p_asset_retire_rec.retirement_id,
530 X_Status => 'PROCESSED',
531 X_Recapture_Amount => l_recapture_amount,
532 X_mrc_sob_type_code => p_mrc_sob_type_code,
533 X_Calling_Fn => l_calling_fn,
534 p_log_level_rec => p_log_level_rec);
535
536 if (p_log_level_rec.statement_level) then
537 fa_debug_pkg.add(l_calling_fn, 'Call FA_RETIREMENTS_PKG.Update_Row', 'End',p_log_level_rec);
538 end if;
539
540 -- SLA: update the event status in SLA as well to unprocessed
541 -- as original api inserted as incomplete
542
543 if (p_mrc_sob_type_code <> 'R') then
544
545 if p_log_level_rec.statement_level then
546 fa_debug_pkg.add
547 (fname => l_calling_fn,
548 element => 'calling fa_xla_events_pvt.update_transaction_event with thid: ',
549 value => p_trans_rec.transaction_header_id);
550 end if;
551
552 if not fa_xla_events_pvt.update_transaction_event
553 (p_ledger_id => p_asset_hdr_rec.set_of_books_id,
554 p_transaction_header_id => p_trans_rec.transaction_header_id,
555 p_book_type_code => p_asset_hdr_rec.book_type_code,
556 p_event_type_code => 'RETIREMENTS',
557 p_event_date => p_asset_retire_rec.date_retired, --?
558 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
559 p_calling_fn => l_calling_fn,
560 p_log_level_rec => p_log_level_rec) then
561 raise ret_err;
562 end if;
563 end if;
564
565 -- Processing Terminal Gain Loss
566 --
567 -- If this is full retirement, check to see there is at least
568 -- one asset remained in this group. If not, declare remaining
569 -- group reserve as terminal gain loss if group's terminal gain
570 -- loss is set to 'YES'.
571 --
572 if (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') <> 'NO') and
573 (nvl(l_asset_fin_rec_new.cost, 0) = 0) and
574 (p_trans_rec.transaction_type_code = 'FULL RETIREMENT') then
575
576 OPEN c_member_exists;
577 FETCH c_member_exists INTO l_member_exists;
578 CLOSE c_member_exists;
579
580 if (p_log_level_rec.statement_level) then
581 fa_debug_pkg.add(l_calling_fn, 'Is there a member asset',
582 l_member_exists,p_log_level_rec);
583 end if;
584
585 if (nvl(l_member_exists, 'N') <> 'Y') then
586
587 FA_BOOKS_PKG.Update_Row(
588 X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
589 X_Asset_Id => l_asset_hdr_rec.asset_id,
590 X_terminal_gain_loss_flag => 'Y',
591 X_mrc_sob_type_code => p_mrc_sob_type_code,
592 X_Calling_Fn => l_calling_fn,
593 p_log_level_rec => p_log_level_rec);
594
595 end if; -- (nvl(l_member_exists, 'N') <> 'Y')
596
597 end if; -- (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') = 'YES')
598
599 end if; -- p_calling_fn <> 'DO_RETIREMENT.CGLFR_CR_ONLY'
600 if (p_log_level_rec.statement_level) then
601 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
602 end if;
603
604 return true;
605 EXCEPTION
606 when ret_err then
607 if (p_log_level_rec.statement_level) then
608 fa_debug_pkg.add(l_calling_fn, 'End', 'Failed',p_log_level_rec);
609 end if;
610
611 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
612 p_log_level_rec => p_log_level_rec);
613 return false;
614
615 when OTHERS then
616 if (p_log_level_rec.statement_level) then
617 fa_debug_pkg.add(l_calling_fn, 'End', 'Failed'||':'||sqlerrm,p_log_level_rec);
618 end if;
619
620 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
621 ,p_log_level_rec => p_log_level_rec);
622 return false;
623
624 END DO_RETIREMENT;
625
626 FUNCTION UNDO_RETIREMENT_REINSTATEMENT(p_transaction_header_id IN NUMBER,
627 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
628 p_group_asset_id IN NUMBER,
629 p_set_of_books_id IN NUMBER,
630 p_mrc_sob_type_code IN VARCHAR2,
631 p_calling_fn IN VARCHAR2,
632 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
633 return boolean IS
634
635 CURSOR c_get_thid IS
636 select transaction_header_id
637 from fa_transaction_headers
638 where asset_id = p_group_asset_id
639 and book_type_code = p_asset_hdr_rec.book_type_code
640 and member_transaction_header_id = p_transaction_header_id;
641
642 l_transaction_header_id NUMBER(15);
643
644 CURSOR c_adj IS
645 select rowid
646 from fa_adjustments
647 where asset_id = p_group_asset_id
648 and book_type_code = p_asset_hdr_rec.book_type_code
649 and transaction_header_id = l_transaction_header_id;
650
651 CURSOR c_mc_adj IS
652 select rowid
653 from fa_adjustments_mrc_v
654 where transaction_header_id = l_transaction_header_id;
655
656 l_calling_fn VARCHAR2(100) := 'fa_group_retirement_pvt.undo_retirement_reinstatement';
657
658 BEGIN
659 --tk_util.debug(l_calling_fn||'()+');
660
661 OPEN c_get_thid;
662 FETCH c_get_thid INTO l_transaction_header_id;
663 CLOSE c_get_thid;
664
665 if (p_mrc_sob_type_code = 'R') then
666
667 FA_BOOKS_PKG.Delete_Row(
668 X_Transaction_Header_Id_in => l_transaction_header_id,
669 X_mrc_sob_type_code => p_mrc_sob_type_code,
670 X_Calling_Fn => l_calling_fn,
671 p_log_level_rec => p_log_level_rec);
672
673 FA_BOOKS_PKG.Reactivate_Row(
674 X_Transaction_Header_Id_Out => l_transaction_header_id,
675 X_mrc_sob_type_code => p_mrc_sob_type_code,
676 X_Calling_Fn => l_calling_fn,
677 p_log_level_rec => p_log_level_rec);
678
679 for l_adj in c_mc_adj loop
680
681 FA_ADJUSTMENTS_PKG.DELETE_ROW(
682 X_Rowid => l_adj.rowid,
683 X_mrc_sob_type_code => p_mrc_sob_type_code,
684 X_Calling_Fn => l_calling_fn,
685 p_log_level_rec => p_log_level_rec);
686 end loop;
687
688 else
689
690 FA_BOOKS_PKG.Delete_Row(
691 X_Transaction_Header_Id_in => l_transaction_header_id,
692 X_mrc_sob_type_code => p_mrc_sob_type_code,
693 X_Calling_Fn => l_calling_fn,
694 p_log_level_rec => p_log_level_rec);
695
696 FA_BOOKS_PKG.Reactivate_Row(
697 X_Transaction_Header_Id_Out => l_transaction_header_id,
698 X_mrc_sob_type_code => p_mrc_sob_type_code,
699 X_Calling_Fn => l_calling_fn,
700 p_log_level_rec => p_log_level_rec);
701
702 for l_adj in c_adj loop
703
704 FA_ADJUSTMENTS_PKG.DELETE_ROW(
705 X_Rowid => l_adj.rowid,
706 X_mrc_sob_type_code => p_mrc_sob_type_code,
707 X_Calling_Fn => l_calling_fn,
708 p_log_level_rec => p_log_level_rec);
709 end loop;
710
711 end if; -- (p_mrc_sob_type_code = 'R')
712
713 if (p_log_level_rec.statement_level) then
714 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
715 end if;
716
717 return true;
718
719 EXCEPTION
720 when OTHERS then
721 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
722 p_log_level_rec => p_log_level_rec);
723
724 if c_get_thid%ISOPEN then
725 CLOSE c_get_thid;
726 end if;
727
728 return false;
729
730 END UNDO_RETIREMENT_REINSTATEMENT;
731
732 FUNCTION DO_REINSTATEMENT(
733 p_trans_rec IN FA_API_TYPES.trans_rec_type,
734 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
735 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
736 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
737 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
738 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
739 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
740 p_period_rec IN FA_API_TYPES.period_rec_type,
741 p_mrc_sob_type_code IN VARCHAR2,
742 p_calling_fn IN VARCHAR2,
743 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
744 return boolean IS
745
746 l_calling_fn VARCHAR2(100) := 'fa_group_retirement_pvt.do_reinstatement';
747
748 l_out_trx_id NUMBER(15); -- Transfer Out thid due to retirement
749
750 CURSOR c_get_ret_dists IS
751 select FA_DISTRIBUTION_HISTORY_S.NEXTVAL
752 , -1 * transaction_units
753 , assigned_to
754 , code_combination_id
755 , location_id
756 from fa_distribution_history
757 where retirement_id = p_asset_retire_rec.retirement_id;
758
759 CURSOR c_get_cur_dists (c_assigned_to number,
760 c_expense_ccid number,
761 c_location_ccid number) IS
762 select distribution_id
763 , units_assigned + nvl(transaction_units, 0)
764 from fa_distribution_history
765 where asset_id = p_asset_hdr_rec.asset_id
766 and book_type_code = p_asset_hdr_rec.book_type_code
767 and transaction_header_id_out is null
768 and (assigned_to = c_assigned_to
769 or (assigned_to is null and
770 c_assigned_to is null))
771 and code_combination_id = c_expense_ccid
772 and location_id = c_location_ccid;
773
774
775 l_trans_rec FA_API_TYPES.trans_rec_type;
776 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
777
778
779 CURSOR c_get_thid is
780 select fa_transaction_headers_s.nextval
781 from dual;
782
783 CURSOR c_get_primary_thid is
784 select transaction_header_id
785 from fa_transaction_headers
786 where asset_id = p_asset_fin_rec.group_asset_id
787 and book_type_code = p_asset_hdr_rec.book_type_code
788 and member_transaction_header_id = p_trans_rec.transaction_header_id;
789
790 l_rowid ROWID;
791 l_status BOOLEAN := TRUE;
792
793 TYPE tab_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
794 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
795
796 t_distribution_id tab_num15_type;
797 t_units_assigned tab_num_type;
798 t_assigned_to tab_num15_type;
799 t_expense_ccid tab_num15_type;
800 t_location_ccid tab_num15_type;
801 l_row_fetched NUMBER;
802
803 l_distribution_id NUMBER(15);
804 l_units_assigned NUMBER;
805
806 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
807
808 l_deprn_exp NUMBER;
809 l_bonus_deprn_exp NUMBER;
810
811 -- +++++ Stores new fin_rec of member +++++
812 l_asset_fin_rec_mn FA_API_TYPES.asset_fin_rec_type;
813
814 l_period_rec FA_API_TYPES.period_rec_type;
815 l_asset_desc_rec_m FA_API_TYPES.asset_desc_rec_type;
816 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
817 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
818 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
819 l_asset_fin_rec_new_m FA_API_TYPES.asset_fin_rec_type;
820 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
821 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
822 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
823 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
824 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
825 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
826 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
827 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
828
829 l_salvage_value_retired NUMBER;
830 l_cip_cost_retired NUMBER;
831 l_adj_rec_cost_retired NUMBER;
832 l_rec_cost_retired NUMBER;
833 l_deprn_limit_retired NUMBER;
834
835 l_reserve_allocated NUMBER;
836
837 cursor c_dist is
838 select distribution_id
839 , transaction_header_id_in
840 , transaction_header_id_out
841 , units_assigned
842 , transaction_units
843 from fa_distribution_history
844 where asset_id = p_asset_hdr_rec.asset_id
845 order by distribution_id;
846
847
848 rein_err EXCEPTION;
849 BEGIN
850 if (p_log_level_rec.statement_level) then
851 fa_debug_pkg.add(l_calling_fn, 'Begin', p_asset_hdr_rec.asset_id||':'||p_asset_fin_rec.group_asset_id,p_log_level_rec);
852 end if;
853
854 -- +++++ populate current period_rec info +++++
855 if not FA_UTIL_PVT.get_period_rec
856 (p_book => p_asset_hdr_rec.book_type_code,
857 x_period_rec => l_period_rec,
858 p_log_level_rec => p_log_level_rec) then
859
860 if (p_log_level_rec.statement_level) then
861 fa_debug_pkg.add(l_calling_fn, 'Calling FA_UTIL_PVT.get_period_rec', 'Failed',p_log_level_rec);
862 end if;
863
864 raise rein_err;
865 end if;
866
867 -- Get asset description for member asset
868 -- p_asset_desc may be null
869 if not FA_UTIL_PVT.get_asset_desc_rec (
870 p_asset_hdr_rec => p_asset_hdr_rec,
871 px_asset_desc_rec => l_asset_desc_rec_m,
872 p_log_level_rec => p_log_level_rec) then
873
874 if (p_log_level_rec.statement_level) then
875 fa_debug_pkg.add(l_calling_fn, 'Calling FA_UTIL_PVT.get_asset_desc_rec', 'Failed',p_log_level_rec);
876 end if;
877
878 raise rein_err;
879 end if;
880
881 --
882 -- Recreate Distributions, Asset History, and Addition only if
883 -- retirement about to be reisntate is partial unit/full retirement and
884 -- book class is CORPORATE.
885 if nvl(p_asset_retire_rec.units_retired, 0) > 0 and
886 fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE' and
887 l_asset_desc_rec_m.current_units <> 0 then
888
889 -- +++++ Get retired distribution +++++
890 OPEN c_get_ret_dists;
891 FETCH c_get_ret_dists BULK COLLECT INTO t_distribution_id,
892 t_units_assigned,
893 t_assigned_to,
894 t_expense_ccid,
895 t_location_ccid;
896 CLOSE c_get_ret_dists;
897
898 l_row_fetched := t_distribution_id.COUNT;
899
900 if (t_distribution_id.COUNT = 0) then
901
902 if (p_log_level_rec.statement_level) then
903 fa_debug_pkg.add(l_calling_fn, 'Getting distribution info', 'Failed',p_log_level_rec);
904 end if;
905
906 raise rein_err;
907 end if;
908
909 --
910 -- For each distribution retired, if there is a existing active distribution
911 -- which shares the same exp_ccid, location, and assigned_to, deactivate it
912 -- and then added existing units to the retired units.
913 for i in 1..l_row_fetched loop
914
915 l_distribution_id := to_number(null);
916 l_units_assigned := to_number(null);
917
918 OPEN c_get_cur_dists(t_assigned_to(i), t_expense_ccid(i), t_location_ccid(i));
919 FETCH c_get_cur_dists into l_distribution_id, l_units_assigned;
920 CLOSE c_get_cur_dists;
921
922 if (l_distribution_id is not null) then
923
924 update FA_DISTRIBUTION_HISTORY
925 set transaction_units = nvl(transaction_units, t_units_assigned(i)),
926 transaction_header_id_out = p_trans_rec.transaction_header_id,
927 date_ineffective = p_trans_rec.who_info.last_update_date,
928 last_update_date = p_trans_rec.who_info.last_update_date,
929 last_updated_by = p_trans_rec.who_info.last_updated_by,
930 last_update_login = p_trans_rec.who_info.last_update_login
931 where distribution_id = l_distribution_id;
932
933 t_units_assigned(i) := t_units_assigned(i) + l_units_assigned;
934
935 end if;
936
937 end loop;
938
939 -- +++++ Create retired distributions +++++
940 FORALL i in 1..t_distribution_id.LAST
941 insert into FA_DISTRIBUTION_HISTORY(
942 DISTRIBUTION_ID,
943 BOOK_TYPE_CODE,
944 ASSET_ID,
945 UNITS_ASSIGNED,
946 DATE_EFFECTIVE,
947 CODE_COMBINATION_ID,
948 LOCATION_ID,
949 TRANSACTION_HEADER_ID_IN,
950 LAST_UPDATE_DATE,
951 LAST_UPDATED_BY,
952 ASSIGNED_TO)
953 VALUES(t_distribution_id(i),
954 p_asset_hdr_rec.book_type_code,
955 p_asset_hdr_rec.asset_id,
956 t_units_assigned(i),
957 p_trans_rec.who_info.creation_date,
958 t_expense_ccid(i),
959 t_location_ccid(i),
960 p_trans_rec.transaction_header_id,
961 p_trans_rec.who_info.last_update_date,
962 p_trans_rec.who_info.last_updated_by,
963 t_assigned_to(i));
964
965 --
966 -- Full retirement didn't update Asset History and Additions, so maintain
967 -- these only if it was partial unit retirement
968 --
969 if (l_asset_desc_rec_m.current_units > p_asset_retire_rec.units_retired) then
970 -- terminate old asset_history
971 FA_ASSET_HISTORY_PKG.Update_Row
972 (X_Asset_Id => p_asset_hdr_rec.asset_id,
973 X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
974 X_Transaction_Header_Id_Out => p_trans_rec.transaction_header_id,
975 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
976 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
977 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
978 X_Return_Status => l_status,
979 X_Calling_Fn => l_calling_fn,
980 p_log_level_rec => p_log_level_rec);
981 if not(l_status) then
982 raise rein_err;
983 end if;
984
985 -- insert new row with new units
986 FA_ASSET_HISTORY_PKG.Insert_Row
987 (X_Rowid => l_rowid,
988 X_Asset_Id => p_asset_hdr_rec.asset_id,
989 X_Category_Id => p_asset_cat_rec.category_id,
990 X_Asset_Type => p_asset_type_rec.asset_type,
991 X_Units => l_asset_desc_rec_m.current_units + p_asset_retire_rec.units_retired,
992 X_Date_Effective => p_trans_rec.who_info.last_update_date,
993 X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
994 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
995 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
996 X_Return_Status => l_status,
997 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history',
998 p_log_level_rec => p_log_level_rec);
999 if not(l_status) then
1000 raise rein_err;
1001 end if;
1002
1003 update fa_additions_b
1004 set current_units = current_units + p_asset_retire_rec.units_retired
1005 where asset_id = p_asset_hdr_rec.asset_id;
1006
1007 end if; -- (l_asset_desc_rec_m.current_units > p_asset_retire_rec.units_retired)
1008
1009 end if; --(nvl(p_asset_retire_rec.units_retired, 0) > 0)
1010
1011 if (p_mrc_sob_type_code <> 'R') then
1012 OPEN c_get_thid;
1013 FETCH c_get_thid INTO l_trans_rec.transaction_header_id;
1014 CLOSE c_get_thid;
1015 else
1016 OPEN c_get_primary_thid;
1017 FETCH c_get_primary_thid INTO l_trans_rec.transaction_header_id;
1018 CLOSE c_get_primary_thid;
1019 end if;
1020
1021
1022 if not CALC_GAIN_LOSS_FOR_REI(
1023 p_trans_rec => p_trans_rec,
1024 p_asset_hdr_rec => p_asset_hdr_rec,
1025 p_asset_type_rec => p_asset_type_rec,
1026 p_asset_desc_rec => l_asset_desc_rec_m,
1027 p_asset_cat_rec => p_asset_cat_rec,
1028 p_asset_fin_rec => p_asset_fin_rec,
1029 p_period_rec => l_period_rec,
1030 p_asset_retire_rec => p_asset_retire_rec,
1031 p_group_thid => l_trans_rec.transaction_header_id,
1032 x_asset_fin_rec => l_asset_fin_rec_mn,
1033 p_mrc_sob_type_code => p_mrc_sob_type_code,
1034 p_log_level_rec => p_log_level_rec) then
1035 raise rein_err;
1036 end if;
1037
1038 -- +++++ Process Group Asset +++++
1039
1040 l_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
1041 l_trans_rec.transaction_subtype := 'AMORTIZED';
1042
1043 l_trans_rec.transaction_key := 'MS';
1044 l_trans_rec.who_info.creation_date := p_trans_rec.who_info.creation_date;
1045 l_trans_rec.who_info.created_by := p_trans_rec.who_info.created_by;
1046 l_trans_rec.who_info.last_update_date := p_trans_rec.who_info.last_update_date;
1047 l_trans_rec.who_info.last_updated_by := p_trans_rec.who_info.last_updated_by;
1048 l_trans_rec.who_info.last_update_login := p_trans_rec.who_info.last_update_login;
1049 l_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
1050 l_trans_rec.mass_transaction_id := p_trans_rec.mass_transaction_id;
1051 l_trans_rec.calling_interface := p_trans_rec.calling_interface;
1052 l_trans_rec.mass_reference_id := p_trans_rec.mass_reference_id;
1053 l_trans_rec.event_id := p_trans_rec.event_id; --Bug#7374827 Need to pass event id.
1054
1055 -- Need deprn rec before reinstatement
1056 l_asset_hdr_rec.asset_id := p_asset_fin_rec.group_asset_id;
1057 l_asset_hdr_rec.book_type_code := p_asset_hdr_rec.book_type_code;
1058 l_asset_hdr_rec.set_of_books_id := p_asset_hdr_rec.set_of_books_id;
1059 --l_asset_hdr_rec.period_of_addition := 'N'; -- Guess not necessary
1060
1061 if not fa_util_pvt.get_asset_deprn_rec (
1062 p_asset_hdr_rec => l_asset_hdr_rec,
1063 px_asset_deprn_rec => l_asset_deprn_rec_old,
1064 p_mrc_sob_type_code => p_mrc_sob_type_code,
1065 p_log_level_rec => p_log_level_rec) then
1066 raise rein_err;
1067 end if;
1068
1069 if not FA_UTIL_PVT.get_asset_type_rec
1070 (p_asset_hdr_rec => l_asset_hdr_rec,
1071 px_asset_type_rec => l_asset_type_rec,
1072 p_log_level_rec => p_log_level_rec) then
1073 raise rein_err;
1074 end if;
1075
1076 if not FA_UTIL_PVT.get_asset_desc_rec
1077 (p_asset_hdr_rec => l_asset_hdr_rec,
1078 px_asset_desc_rec => l_asset_desc_rec,
1079 p_log_level_rec => p_log_level_rec) then
1080 raise rein_err;
1081 end if;
1082
1083 if not FA_UTIL_PVT.get_asset_cat_rec
1084 (p_asset_hdr_rec => l_asset_hdr_rec,
1085 px_asset_cat_rec => l_asset_cat_rec,
1086 p_date_effective => null,
1087 p_log_level_rec => p_log_level_rec) then
1088 raise rein_err;
1089 end if;
1090
1091 if not fa_util_pvt.get_asset_fin_rec (
1092 p_asset_hdr_rec => l_asset_hdr_rec,
1093 px_asset_fin_rec => l_asset_fin_rec_old,
1094 p_mrc_sob_type_code => p_mrc_sob_type_code,
1095 p_log_level_rec => p_log_level_rec) then
1096 raise rein_err;
1097 end if;
1098
1099 --HH Validate disabled_flag
1100 --We don't want to reinstate assets belonging to a disabled group.
1101 if not FA_ASSET_VAL_PVT.validate_disabled_flag
1102 (p_group_asset_id => l_asset_hdr_rec.asset_id,
1103 p_book_type_code => l_asset_hdr_rec.book_type_code,
1104 p_old_flag => l_asset_fin_rec_old.disabled_flag,
1105 p_new_flag => l_asset_fin_rec_old.disabled_flag,
1106 p_log_level_rec => p_log_level_rec) then
1107 raise rein_err;
1108 end if; --End HH
1109
1110 /*
1111 if not fa_util_pvt.get_asset_deprn_rec (
1112 p_asset_hdr_rec => l_asset_hdr_rec,
1113 px_asset_deprn_rec => l_asset_deprn_rec_old,
1114 p_mrc_sob_type_code => p_mrc_sob_type_code,
1115 p_log_level_rec => p_log_level_rec) then
1116 raise rein_err;
1117 end if;
1118 */
1119
1120 l_asset_fin_rec_adj.cost := p_asset_retire_rec.cost_retired;
1121 l_asset_fin_rec_adj.unrevalued_cost := p_asset_retire_rec.cost_retired;
1122
1123 -- Get new member's fin_rec
1124 if not fa_util_pvt.get_asset_fin_rec (
1125 p_asset_hdr_rec => p_asset_hdr_rec,
1126 px_asset_fin_rec => l_asset_fin_rec_new_m,
1127 p_mrc_sob_type_code => p_mrc_sob_type_code,
1128 p_log_level_rec => p_log_level_rec) then
1129 raise rein_err;
1130 end if;
1131
1132 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_new_m.salvage_value, 0) -
1133 nvl(p_asset_fin_rec.salvage_value, 0);
1134
1135 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
1136 nvl(l_asset_fin_rec_new_m.allowed_deprn_limit_amount, 0) -
1137 nvl(p_asset_fin_rec.allowed_deprn_limit_amount, 0);
1138
1139 -- l_asset_deprn_rec_adj.deprn_reserve := p_asset_retire_rec.reserve_retired;
1140
1141 --
1142 -- Process member asset reinstatement
1143 --
1144 -- Find Salvage value retired, recoverable cost, deprn limit, adjusted recoverable cost
1145 -- cip_cost_retired, new adjusted cost by calling deprn basis.
1146 -- Also find out whether there is other amount to reinstate. I guess not.
1147 FA_BOOKS_PKG.Deactivate_Row(
1148 X_Asset_Id => p_asset_hdr_rec.asset_id,
1149 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1150 X_Transaction_Header_Id_Out => p_trans_rec.transaction_header_id,
1151 X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
1152 X_mrc_sob_type_code => p_mrc_sob_type_code,
1153 X_Calling_Fn => l_calling_fn,
1154 p_log_level_rec => p_log_level_rec);
1155
1156 --tk_util.DumpFinRec(l_asset_fin_rec_mn, 'member');
1157
1158 FA_BOOKS_PKG.INSERT_ROW(
1159 X_Rowid => l_rowid,
1160 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1161 X_Asset_Id => p_asset_hdr_rec.asset_id,
1162 X_Date_Placed_In_Service => p_asset_fin_rec.date_placed_in_service,
1163 X_Date_Effective => p_trans_rec.who_info.creation_date,
1164 X_Deprn_Start_Date => p_asset_fin_rec.deprn_start_date,
1165 X_Deprn_Method_Code => p_asset_fin_rec.deprn_method_code,
1166 X_Life_In_Months => p_asset_fin_rec.life_in_months,
1167 X_Rate_Adjustment_Factor => l_asset_fin_rec_mn.rate_adjustment_factor,
1168 X_Adjusted_Cost => l_asset_fin_rec_mn.adjusted_cost,
1169 X_Cost => l_asset_fin_rec_mn.cost,
1170 X_Original_Cost => p_asset_fin_rec.original_cost,
1171 X_Salvage_Value => l_asset_fin_rec_mn.salvage_value,
1172 X_Prorate_Convention_Code => p_asset_fin_rec.prorate_convention_code,
1173 X_Prorate_Date => p_asset_fin_rec.prorate_date,
1174 X_Cost_Change_Flag => p_asset_fin_rec.cost_change_flag,
1175 X_Adjustment_Required_Status => p_asset_fin_rec.adjustment_required_status,
1176 X_Capitalize_Flag => p_asset_fin_rec.capitalize_flag,
1177 X_Retirement_Pending_Flag => 'NO',
1178 X_Depreciate_Flag => p_asset_fin_rec.depreciate_flag,
1179 X_Disabled_Flag => p_asset_fin_rec.disabled_flag, --HH
1180 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1181 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1182 X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
1183 X_Itc_Amount_Id => p_asset_fin_rec.itc_amount_id,
1184 X_Itc_Amount => p_asset_fin_rec.itc_amount,
1185 -- X_Retirement_Id => ,
1186 -- X_Tax_Request_Id => ,
1187 X_Itc_Basis => p_asset_fin_rec.itc_basis,
1188 X_Basic_Rate => p_asset_fin_rec.basic_rate,
1189 X_Adjusted_Rate => p_asset_fin_rec.adjusted_rate,
1190 X_Bonus_Rule => p_asset_fin_rec.bonus_rule,
1191 X_Ceiling_Name => p_asset_fin_rec.ceiling_name,
1192 X_Recoverable_Cost => l_asset_fin_rec_mn.recoverable_cost,
1193 X_Adjusted_Capacity => l_asset_fin_rec_mn.adjusted_capacity,
1194 X_Fully_Rsvd_Revals_Counter => p_asset_fin_rec.fully_rsvd_revals_counter,
1195 X_Idled_Flag => p_asset_fin_rec.idled_flag,
1196 X_Period_Counter_Capitalized => p_asset_fin_rec.period_counter_capitalized,
1197 X_PC_Fully_Reserved => null,
1198 X_Production_Capacity => p_asset_fin_rec.production_capacity,
1199 X_Reval_Amortization_Basis => l_asset_fin_rec_mn.reval_amortization_basis,
1200 X_Reval_Ceiling => l_asset_fin_rec_mn.reval_ceiling,
1201 X_Unit_Of_Measure => p_asset_fin_rec.unit_of_measure,
1202 X_Unrevalued_Cost => l_asset_fin_rec_mn.unrevalued_cost,
1203 X_Annual_Deprn_Rounding_Flag => l_asset_fin_rec_mn.annual_deprn_rounding_flag,
1204 X_Percent_Salvage_Value => p_asset_fin_rec.percent_salvage_value,
1205 X_Allowed_Deprn_Limit => p_asset_fin_rec.allowed_deprn_limit,
1206 X_Allowed_Deprn_Limit_Amount => l_asset_fin_rec_mn.allowed_deprn_limit_amount,
1207 X_Period_Counter_Life_Complete => p_asset_fin_rec.period_counter_life_complete,
1208 X_Adjusted_Recoverable_Cost => l_asset_fin_rec_mn.adjusted_recoverable_cost,
1209 X_Short_Fiscal_Year_Flag => p_asset_fin_rec.short_fiscal_year_flag,
1210 X_Conversion_Date => p_asset_fin_rec.conversion_date,
1211 X_Orig_Deprn_Start_Date => p_asset_fin_rec.orig_deprn_start_date,
1212 X_Remaining_Life1 => p_asset_fin_rec.remaining_life1,
1213 X_Remaining_Life2 => p_asset_fin_rec.remaining_life2,
1214 X_Old_Adj_Cost => p_asset_fin_rec.adjusted_cost, ---???
1215 X_Formula_Factor => l_asset_fin_rec_mn.formula_factor,
1216 X_gf_Attribute1 => p_asset_fin_rec.global_attribute1,
1217 X_gf_Attribute2 => p_asset_fin_rec.global_attribute2,
1218 X_gf_Attribute3 => p_asset_fin_rec.global_attribute3,
1219 X_gf_Attribute4 => p_asset_fin_rec.global_attribute4,
1220 X_gf_Attribute5 => p_asset_fin_rec.global_attribute5,
1221 X_gf_Attribute6 => p_asset_fin_rec.global_attribute6,
1222 X_gf_Attribute7 => p_asset_fin_rec.global_attribute7,
1223 X_gf_Attribute8 => p_asset_fin_rec.global_attribute8,
1224 X_gf_Attribute9 => p_asset_fin_rec.global_attribute9,
1225 X_gf_Attribute10 => p_asset_fin_rec.global_attribute10,
1226 X_gf_Attribute11 => p_asset_fin_rec.global_attribute11,
1227 X_gf_Attribute12 => p_asset_fin_rec.global_attribute12,
1228 X_gf_Attribute13 => p_asset_fin_rec.global_attribute13,
1229 X_gf_Attribute14 => p_asset_fin_rec.global_attribute14,
1230 X_gf_Attribute15 => p_asset_fin_rec.global_attribute15,
1231 X_gf_Attribute16 => p_asset_fin_rec.global_attribute16,
1232 X_gf_Attribute17 => p_asset_fin_rec.global_attribute17,
1233 X_gf_Attribute18 => p_asset_fin_rec.global_attribute18,
1234 X_gf_Attribute19 => p_asset_fin_rec.global_attribute19,
1235 X_gf_Attribute20 => p_asset_fin_rec.global_attribute20,
1236 X_global_attribute_category => p_asset_fin_rec.global_attribute_category,
1237 X_group_asset_id => p_asset_fin_rec.group_asset_id,
1238 X_salvage_type => p_asset_fin_rec.salvage_type,
1239 X_deprn_limit_type => p_asset_fin_rec.deprn_limit_type,
1240 X_over_depreciate_option => p_asset_fin_rec.over_depreciate_option,
1241 X_super_group_id => p_asset_fin_rec.super_group_id,
1242 X_reduction_rate => l_asset_fin_rec_mn.reduction_rate,
1243 X_reduce_addition_flag => p_asset_fin_rec.reduce_addition_flag,
1244 X_reduce_adjustment_flag => p_asset_fin_rec.reduce_adjustment_flag,
1245 X_reduce_retirement_flag => p_asset_fin_rec.reduce_retirement_flag,
1246 X_recognize_gain_loss => p_asset_fin_rec.recognize_gain_loss,
1247 X_recapture_reserve_flag => p_asset_fin_rec.recapture_reserve_flag,
1248 X_limit_proceeds_flag => p_asset_fin_rec.limit_proceeds_flag,
1249 X_terminal_gain_loss => p_asset_fin_rec.terminal_gain_loss,
1250 X_tracking_method => p_asset_fin_rec.tracking_method,
1251 X_allocate_to_fully_rsv_flag => p_asset_fin_rec.allocate_to_fully_rsv_flag,
1252 X_allocate_to_fully_ret_flag => p_asset_fin_rec.allocate_to_fully_ret_flag,
1253 X_exclude_fully_rsv_flag => p_asset_fin_rec.exclude_fully_rsv_flag,
1254 X_excess_allocation_option => p_asset_fin_rec.excess_allocation_option,
1255 X_depreciation_option => p_asset_fin_rec.depreciation_option,
1256 X_member_rollup_flag => p_asset_fin_rec.member_rollup_flag,
1257 X_ytd_proceeds => l_asset_fin_rec_mn.ytd_proceeds,
1258 X_ltd_proceeds => l_asset_fin_rec_mn.ltd_proceeds,
1259 X_eofy_reserve => l_asset_fin_rec_mn.eofy_reserve,
1260 X_cip_cost => l_asset_fin_rec_mn.cip_cost,
1261 X_terminal_gain_loss_amount => l_asset_fin_rec_mn.terminal_gain_loss_amount,
1262 X_ltd_cost_of_removal => l_asset_fin_rec_mn.ltd_cost_of_removal,
1263 X_Return_Status => l_status,
1264 X_mrc_sob_type_code => p_mrc_sob_type_code,
1265 X_Calling_Fn => l_calling_fn,
1266 p_log_level_rec => p_log_level_rec);
1267
1268 if (not(l_status)) then
1269 raise rein_err;
1270 end if;
1271
1272 if not FA_TRX_APPROVAL_PKG.faxcat
1273 (X_book => l_asset_hdr_rec.book_type_code,
1274 X_asset_id => l_asset_hdr_rec.asset_id,
1275 X_trx_type => l_trans_rec.transaction_type_code,
1276 X_trx_date => l_trans_rec.transaction_date_entered,
1277 X_init_message_flag => 'NO',
1278 p_log_level_rec => p_log_level_rec) then
1279 raise rein_err;
1280 end if;
1281
1282 if (p_log_level_rec.statement_level) then
1283 fa_debug_pkg.add(l_calling_fn, 'transaction_date_entered',
1284 p_trans_rec.transaction_date_entered,p_log_level_rec);
1285 fa_debug_pkg.add(l_calling_fn, 'amortization_start_date',
1286 p_trans_rec.amortization_start_date,p_log_level_rec);
1287 fa_debug_pkg.add(l_calling_fn, 'new.cost', l_asset_fin_rec_new.cost,p_log_level_rec);
1288 fa_debug_pkg.add(l_calling_fn, 'tracking_method',l_asset_fin_rec_old.tracking_method,p_log_level_rec);
1289 fa_debug_pkg.add(l_calling_fn, 'rule_name',fa_cache_pkg.fazcdrd_record.rule_name,p_log_level_rec);
1290 end if;
1291
1292
1293 if (nvl(l_asset_fin_rec_old.tracking_method, 'NO TRACK') = 'ALLOCATE') and
1294 (fa_cache_pkg.fazcdrd_record.rule_name = 'ENERGY PERIOD END BALANCE') then
1295 l_trans_rec.transaction_date_entered := p_trans_rec.transaction_date_entered;
1296 l_trans_rec.amortization_start_date := p_trans_rec.transaction_date_entered;
1297 else
1298 l_trans_rec.transaction_date_entered := p_asset_retire_rec.date_retired;
1299 l_trans_rec.amortization_start_date := p_asset_retire_rec.date_retired;
1300 end if;
1301
1302 if (p_log_level_rec.statement_level) then
1303 fa_debug_pkg.add(l_calling_fn, 'Call FA_ADJUSTMENT_PVT.do_adjustment2',
1304 'Begin',p_log_level_rec);
1305
1306 end if;
1307
1308
1309 if not FA_ADJUSTMENT_PVT.do_adjustment
1310 (px_trans_rec => l_trans_rec,
1311 px_asset_hdr_rec => l_asset_hdr_rec,
1312 p_asset_desc_rec => l_asset_desc_rec,
1313 p_asset_type_rec => l_asset_type_rec,
1314 p_asset_cat_rec => l_asset_cat_rec,
1315 p_asset_fin_rec_old => l_asset_fin_rec_old,
1316 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1317 x_asset_fin_rec_new => l_asset_fin_rec_new,
1318 p_inv_trans_rec => l_inv_trans_rec,
1319 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
1320 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1321 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1322 p_period_rec => l_period_rec,
1323 p_mrc_sob_type_code => p_mrc_sob_type_code,
1324 p_group_reclass_options_rec => l_group_reclass_options_rec,
1325 p_calling_fn => l_calling_fn,
1326 p_log_level_rec => p_log_level_rec)then
1327 raise rein_err;
1328 end if;
1329
1330 if (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'Y') then
1331 --
1332 -- This is Sum up member depreciation to group asset
1333 --
1334 if (p_log_level_rec.statement_level) then
1335 fa_debug_pkg.add(l_calling_fn, 'Process Member Rollup Group',
1336 l_asset_hdr_rec.asset_id,p_log_level_rec);
1337 end if;
1338 -- +++++ Remove group expense rows +++++
1339 DELETE FROM FA_ADJUSTMENTS
1340 WHERE ASSET_ID = l_asset_hdr_rec.asset_id
1341 AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code
1342 AND TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
1343 AND SOURCE_TYPE_CODE = 'DEPRECIATION'
1344 AND ADJUSTMENT_TYPE in ('EXPENSE', 'BONUS EXPENSE');
1345
1346 end if; -- (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'Y')
1347
1348 -- +++++ Clear Terminal Gain Loss Amount +++++
1349 FA_BOOKS_PKG.Update_Row(
1350 X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
1351 X_Asset_Id => l_asset_hdr_rec.asset_id,
1352 X_terminal_gain_loss_amount => FND_API.G_MISS_NUM,
1353 X_mrc_sob_type_code => p_mrc_sob_type_code,
1354 X_Calling_Fn => l_calling_fn,
1355 p_log_level_rec => p_log_level_rec);
1356
1357
1358 if (p_log_level_rec.statement_level) then
1359 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
1360 end if;
1361
1362 if (p_trans_rec.calling_interface <> 'FAXASSET') then
1363
1364 if (nvl(l_asset_fin_rec_old.tracking_method, 'NO TRACK') = 'ALLOCATE') and
1365 (fa_cache_pkg.fazcdrd_record.rule_name = 'ENERGY PERIOD END BALANCE') then
1366 if (p_log_level_rec.statement_level) then
1367 fa_debug_pkg.add(l_calling_fn, 'Calling Do_Allocation', ' ',p_log_level_rec);
1368 fa_debug_pkg.add(l_calling_fn, 'proceeds_of_sale', p_asset_retire_rec.proceeds_of_sale,p_log_level_rec);
1369 fa_debug_pkg.add(l_calling_fn, 'cost_of_removal', p_asset_retire_rec.cost_of_removal,p_log_level_rec);
1370 fa_debug_pkg.add(l_calling_fn, 'reserve_retired', p_asset_retire_rec.reserve_retired,p_log_level_rec);
1371 end if;
1372
1373 l_reserve_allocated := p_asset_retire_rec.cost_retired -
1374 nvl(p_asset_retire_rec.proceeds_of_sale, 0) +
1375 nvl(p_asset_retire_rec.cost_of_removal, 0) -
1376 nvl(p_asset_retire_rec.reserve_retired, 0);
1377
1378 if not FA_RETIREMENT_PVT.Do_Allocation(
1379 p_trans_rec => l_trans_rec,
1380 p_asset_hdr_rec => l_asset_hdr_rec,
1381 p_asset_fin_rec => l_asset_fin_rec_new,
1382 p_asset_deprn_rec_new => l_asset_deprn_rec_new,
1383 p_period_rec => l_period_rec,
1384 p_reserve_amount => l_reserve_allocated,
1385 p_mem_ret_thid => p_asset_retire_rec.detail_info.transaction_header_id_in,
1386 p_mrc_sob_type_code => p_mrc_sob_type_code,
1387 p_calling_fn => l_calling_fn,
1388 p_log_level_rec => p_log_level_rec) then
1389 if (p_log_level_rec.statement_level) then
1390 fa_debug_pkg.add(l_calling_fn, 'Calling FA_ADJUSTMENT_PVT.Do_Allocation', 'Failed',p_log_level_rec);
1391 end if;
1392
1393 raise rein_err;
1394 end if;
1395 end if; -- (nvl(l_asset_fin_rec_old.tracking_method, 'NO TRACK') = 'ALLOCATE')
1396
1397 end if; -- (p_trans_rec.calling_interface <> 'FAXASSET')
1398
1399 if (p_log_level_rec.statement_level) then
1400 for r_dist in c_dist loop
1401 fa_debug_pkg.add(l_calling_fn, 'distribution_id', r_dist.distribution_id,p_log_level_rec);
1402 fa_debug_pkg.add(l_calling_fn, 'transaction_header_id_in', r_dist.transaction_header_id_in,p_log_level_rec);
1403 fa_debug_pkg.add(l_calling_fn, 'transaction_header_id_out', r_dist.transaction_header_id_out,p_log_level_rec);
1404 fa_debug_pkg.add(l_calling_fn, 'units_assigned', r_dist.units_assigned,p_log_level_rec);
1405 fa_debug_pkg.add(l_calling_fn, 'transaction_units', r_dist.transaction_units,p_log_level_rec);
1406 end loop;
1407 end if;
1408
1409 return true;
1410
1411 EXCEPTION
1412 when rein_err then
1413 if (p_log_level_rec.statement_level) then
1414 fa_debug_pkg.add(l_calling_fn, '(rein_err)Processing reinstatement', 'Failed',p_log_level_rec);
1415 end if;
1416
1417 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
1418 p_log_level_rec => p_log_level_rec);
1419
1420 return false;
1421
1422 when OTHERS then
1423 if (p_log_level_rec.statement_level) then
1424 fa_debug_pkg.add(l_calling_fn, '(others)Processing reinstatement', 'Failed',p_log_level_rec);
1425 end if;
1426
1427 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
1428 p_log_level_rec => p_log_level_rec);
1429
1430 return false;
1431
1432 END DO_REINSTATEMENT;
1433
1434 /*====================================================================+
1435 | Function |
1436 | CALC_GAIN_LOSS_FOR_RET |
1437 | |
1438 | Description |
1439 | This function maintain FA_(MC_)ADJUSTMENTS table for group |
1440 | retirement. |
1441 | |
1442 +====================================================================*/
1443 FUNCTION CALC_GAIN_LOSS_FOR_RET(
1444 p_trans_rec IN FA_API_TYPES.trans_rec_type,
1445 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1446 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
1447 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
1448 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
1449 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
1450 p_period_rec IN FA_API_TYPES.period_rec_type,
1451 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
1452 p_group_thid IN NUMBER,
1453 p_salvage_value_retired IN NUMBER,
1454 p_mrc_sob_type_code IN VARCHAR2,
1455 p_mode IN VARCHAR2,
1456 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1457 return BOOLEAN is
1458
1459 /* --Commented out as part of fix for 3188851.
1460 -- Cursor to get transaction header id of transfer out in
1461 -- case of partial unit retirement
1462 CURSOR c_get_thid IS
1463 select distinct nvl(transaction_header_id_out,0)
1464 from fa_distribution_history
1465 where asset_id = p_asset_hdr_rec.asset_id
1466 and book_type_code = p_asset_hdr_rec.book_type_code
1467 and retirement_id = p_asset_retire_rec.retirement_id; */
1468
1469 CURSOR c_get_group_method_info is -- ENERGY
1470 select db.rule_name -- ENERGY
1471 from fa_deprn_basis_rules db -- ENERGY
1472 , fa_methods mt -- ENERGY
1473 , fa_books bk -- ENERGY
1474 where bk.asset_id = p_asset_fin_rec.group_asset_id -- ENERGY
1475 and bk.book_type_code = p_asset_hdr_rec.book_type_code -- ENERGY
1476 and bk.transaction_header_id_out is null -- ENERGY
1477 and bk.deprn_method_code = mt.method_code -- ENERGY
1478 and nvl(bk.life_in_months, -99) = nvl(mt.life_in_months, -99) -- ENERGY
1479 and mt.deprn_basis_rule_id = db.deprn_basis_rule_id; -- ENERGY
1480
1481 l_calling_fn VARCHAR2(100) := 'fa_retirement_pvt.calc_gain_loss_for_ret';
1482
1483 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1484 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
1485
1486 l_trans_rec FA_API_TYPES.trans_rec_type;
1487 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1488 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1489 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
1490
1491 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
1492
1493 l_deprn_exp NUMBER;
1494 l_bonus_deprn_exp NUMBER;
1495
1496 l_group_db_rule_name VARCHAR2(80);
1497
1498 calc_err EXCEPTION;
1499
1500
1501 BEGIN
1502 if (p_log_level_rec.statement_level) then
1503 fa_debug_pkg.add(l_calling_fn, 'Begin',
1504 p_asset_hdr_rec.asset_id||':'||p_asset_fin_rec.group_asset_id,p_log_level_rec);
1505 end if;
1506
1507 -- BUG# 3641747
1508 -- call cache as it was not yet initialized
1509 if not fa_cache_pkg.fazccb(p_asset_hdr_rec.book_type_code,
1510 p_asset_cat_rec.category_id,
1511 p_log_level_rec) then
1512 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
1513 p_log_level_rec => p_log_level_rec);
1514 raise calc_err;
1515 end if;
1516
1517 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
1518 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
1519 l_adj.period_counter_created := p_period_rec.period_counter;
1520 l_adj.period_counter_adjusted := p_period_rec.period_counter;
1521 l_adj.current_units := p_asset_desc_rec.current_units;
1522 l_adj.selection_retid := 0;
1523 l_adj.leveling_flag := TRUE;
1524 l_adj.flush_adj_flag := FALSE;
1525
1526 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
1527
1528 l_adj.gen_ccid_flag := TRUE;
1529
1530 l_adj.asset_id := p_asset_hdr_rec.asset_id;
1531
1532 l_adj.adjustment_type := 'COST';
1533 l_adj.debit_credit_flag := 'CR';
1534 l_adj.code_combination_id := 0;
1535 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
1536
1537 if p_asset_type_rec.asset_type = 'CIP' then
1538 l_adj.source_type_code := 'CIP RETIREMENT';
1539 l_adj.account := fa_cache_pkg.fazccb_record.cip_cost_acct;
1540 l_adj.account_type := 'CIP_COST_ACCT';
1541 else
1542 l_adj.source_type_code := 'RETIREMENT';
1543 l_adj.account := fa_cache_pkg.fazccb_record.asset_cost_acct;
1544 l_adj.account_type := 'ASSET_COST_ACCT';
1545 end if;
1546
1547 --fa_debug_pkg.add(l_calling_fn,'Before 1st check, book_class', fa_cache_pkg.fazcbc_record.book_class);
1548
1549 if ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
1550 (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') and
1551 (p_mode = 'CR')) then
1552 /* --Commented out as part of fix for 3188851.
1553 OPEN c_get_thid;
1554 FETCH c_get_thid INTO l_adj.selection_thid;
1555 CLOSE c_get_thid;
1556
1557 if (nvl(l_adj.selection_thid, 0) = 0) then
1558
1559 if (p_log_level_rec.statement_level) then
1560 fa_debug_pkg.add(l_calling_fn, 'Error', 'Getting selection_thid',p_log_level_rec);
1561 end if;
1562
1563 raise calc_err;
1564 end if; */
1565 l_adj.adjustment_amount := p_asset_fin_rec.cost;
1566 l_adj.selection_thid := p_trans_rec.transaction_header_id;
1567 l_adj.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
1568 --tk_util.DumpAdjRec(l_adj, 'URCR');
1569 if not FA_INS_ADJUST_PKG.faxinaj
1570 (l_adj,
1571 p_trans_rec.who_info.last_update_date,
1572 p_trans_rec.who_info.last_updated_by,
1573 p_trans_rec.who_info.last_update_login,
1574 p_log_level_rec) then
1575 raise calc_err;
1576 end if;
1577
1578 -- Not a partial unit ret.
1579 -- 3440308
1580 elsif ((p_asset_retire_rec.units_retired IS NULL) OR
1581 (NVL(p_asset_retire_rec.units_retired, 0) = g_current_units)) THEN
1582 --end 3440308
1583 l_adj.adjustment_amount := p_asset_retire_rec.cost_retired;
1584 l_adj.selection_thid := 0;
1585 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1586
1587 if not FA_INS_ADJUST_PKG.faxinaj
1588 (l_adj,
1589 p_trans_rec.who_info.last_update_date,
1590 p_trans_rec.who_info.last_updated_by,
1591 p_trans_rec.who_info.last_update_login,
1592 p_log_level_rec) then
1593 raise calc_err;
1594 end if;
1595 end if;
1596
1597 --fa_debug_pkg.add(l_calling_fn,'Before DR logic, book',p_asset_hdr_rec.book_type_code);
1598 -- Need to call cache again as this gets reset when the dist api gets called.
1599 if not fa_cache_pkg.fazcbc(x_book => p_asset_hdr_rec.book_type_code
1600 ,p_log_level_rec => p_log_level_rec) then
1601 raise calc_err;
1602 end if;
1603
1604 --Doing this check due to 3188851.
1605 if p_mode <> 'CR' then
1606
1607 --fa_debug_pkg.add(l_calling_fn,'In DR logic, units_retired',p_asset_retire_rec.units_retired);
1608 --fa_debug_pkg.add(l_calling_fn,'In DR logic, book_class', fa_cache_pkg.fazcbc_record.book_class);
1609
1610 if ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
1611 (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE')) then
1612 --
1613 -- With unkonwn reason l_adj.account is set to null so
1614 -- need to resert the same value.
1615 --
1616 if p_asset_type_rec.asset_type = 'CIP' then
1617 l_adj.account := fa_cache_pkg.fazccb_record.cip_cost_acct;
1618 else
1619 l_adj.account := fa_cache_pkg.fazccb_record.asset_cost_acct;
1620 end if;
1621
1622 l_adj.selection_thid := 0;
1623 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1624 l_adj.current_units := p_asset_desc_rec.current_units;
1625
1626 l_adj.debit_credit_flag := 'DR';
1627 l_adj.leveling_flag := FALSE;
1628 l_adj.adjustment_amount := p_asset_fin_rec.cost - p_asset_retire_rec.cost_retired;
1629 --tk_util.DumpAdjRec(l_adj, 'URDR');
1630 if not FA_INS_ADJUST_PKG.faxinaj
1631 (l_adj,
1632 p_trans_rec.who_info.last_update_date,
1633 p_trans_rec.who_info.last_updated_by,
1634 p_trans_rec.who_info.last_update_login,
1635 p_log_level_rec) then
1636 raise calc_err;
1637 end if;
1638 /* -- no need for this since it will use above condition in CR logic.
1639 else
1640 --tk_util.DumpAdjRec(l_adj, 'URCR');
1641 if not FA_INS_ADJUST_PKG.faxinaj
1642 (l_adj,
1643 p_trans_rec.who_info.last_update_date,
1644 p_trans_rec.who_info.last_updated_by,
1645 p_trans_rec.who_info.last_update_login
1646 ,p_log_level_rec => p_log_level_rec) then
1647 raise calc_err;
1648 end if; */
1649
1650 end if; -- ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
1651
1652 --
1653 -- Creating Member Entires for Tracking
1654 --
1655 if (p_asset_fin_rec.tracking_method = 'ALLOCATE') or
1656 (p_asset_fin_rec.tracking_method = 'CALCULATE') and
1657 (nvl(p_asset_fin_rec.member_rollup_flag, 'N') = 'N') then
1658 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1659 l_adj.distribution_id := 0;
1660
1661 l_adj.adjustment_type := 'RESERVE';
1662 l_adj.debit_credit_flag := 'DR';
1663 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1664 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1665
1666 OPEN c_get_group_method_info; -- ENERGY
1667 FETCH c_get_group_method_info INTO l_group_db_rule_name; -- ENERGY
1668 CLOSE c_get_group_method_info; -- ENERGY
1669
1670 -- BUG# 6899255
1671 -- handle all allocate cases the same way rather than just energy:
1672 -- (l_group_db_rule_name = 'ENERGY PERIOD END BALANCE')
1673 if (nvl(p_asset_fin_rec.tracking_method, 'NON TRACK') = 'ALLOCATE') then -- ENERGY
1674 -- ENERGY
1675 if not fa_util_pvt.get_asset_deprn_rec ( -- ENERGY
1676 p_asset_hdr_rec => p_asset_hdr_rec, -- ENERGY
1677 px_asset_deprn_rec => l_asset_deprn_rec, -- ENERGY
1678 p_mrc_sob_type_code => p_mrc_sob_type_code,
1679 p_log_level_rec => p_log_level_rec) then -- ENERGY
1680 raise calc_err; -- ENERGY
1681 end if; -- ENERGY
1682 -- ENERGY
1683 l_adj.adjustment_amount := l_asset_deprn_rec.deprn_reserve * -- ENERGY
1684 (p_asset_retire_rec.cost_retired/p_asset_fin_rec.cost); -- ENERGY
1685 -- ENERGY
1686 if not FA_UTILS_PKG.faxrnd(l_adj.adjustment_amount, -- ENERGY
1687 p_asset_hdr_rec.book_type_code,
1688 p_log_level_rec) then -- ENERGY
1689 raise calc_err; -- ENERGY
1690 end if; -- ENERGY
1691 else -- ENERGY
1692 l_adj.adjustment_amount := p_asset_retire_rec.cost_retired;
1693 end if; -- ENERGY
1694
1695 l_adj.track_member_flag := 'Y';
1696
1697 if (nvl(p_asset_retire_rec.cost_of_removal, 0) = 0) and
1698 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) = 0) then
1699 l_adj.flush_adj_flag := TRUE;
1700 end if;
1701
1702 --tk_util.DumpAdjRec(l_adj, 'RSVm');
1703 if (p_log_level_rec.statement_level) then
1704 fa_debug_pkg.add(l_calling_fn, 'Retired Reserve', l_adj.adjustment_amount,p_log_level_rec);
1705 end if;
1706
1707 if not FA_INS_ADJUST_PKG.faxinaj
1708 (l_adj,
1709 p_trans_rec.who_info.last_update_date,
1710 p_trans_rec.who_info.last_updated_by,
1711 p_trans_rec.who_info.last_update_login,
1712 p_log_level_rec) then
1713 raise calc_err;
1714 end if;
1715
1716 if (p_asset_fin_rec.tracking_method <> 'ALLOCATE') then
1717
1718 if (nvl(p_asset_retire_rec.proceeds_of_sale, 0) <> 0) then
1719 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1720 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1721 l_adj.adjustment_amount := p_asset_retire_rec.proceeds_of_sale;
1722 l_adj.track_member_flag := 'Y';
1723 l_adj.flush_adj_flag := FALSE;
1724
1725 --tk_util.DumpAdjRec(l_adj, 'RSVposm');
1726
1727 if (p_log_level_rec.statement_level) then
1728 fa_debug_pkg.add(l_calling_fn, 'Retired Reserve for Proceeds of Sale', l_adj.adjustment_amount,p_log_level_rec);
1729 end if;
1730
1731 if not FA_INS_ADJUST_PKG.faxinaj
1732 (l_adj,
1733 p_trans_rec.who_info.last_update_date,
1734 p_trans_rec.who_info.last_updated_by,
1735 p_trans_rec.who_info.last_update_login,
1736 p_log_level_rec) then
1737 raise calc_err;
1738 end if;
1739 end if; -- (nvl(p_asset_retire_rec.proceeds_of_sale, 0) <> 0)
1740
1741 if (nvl(p_asset_retire_rec.cost_of_removal, 0) <> 0) then
1742 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1743 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1744 l_adj.adjustment_amount := p_asset_retire_rec.cost_of_removal;
1745 l_adj.track_member_flag := 'Y';
1746 l_adj.flush_adj_flag := FALSE;
1747
1748 --tk_util.DumpAdjRec(l_adj, 'RSVcorm');
1749
1750 if (p_log_level_rec.statement_level) then
1751 fa_debug_pkg.add(l_calling_fn, 'Retired Reserve for Cost of Removal', l_adj.adjustment_amount,p_log_level_rec);
1752 end if;
1753
1754 if not FA_INS_ADJUST_PKG.faxinaj
1755 (l_adj,
1756 p_trans_rec.who_info.last_update_date,
1757 p_trans_rec.who_info.last_updated_by,
1758 p_trans_rec.who_info.last_update_login,
1759 p_log_level_rec) then
1760 raise calc_err;
1761 end if;
1762 end if;
1763
1764 if (nvl(p_asset_retire_rec.cost_of_removal, 0) <> 0) then
1765 l_adj.adjustment_type := 'REMOVALCOST CLR';
1766 l_adj.debit_credit_flag := 'CR';
1767 l_adj.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1768 l_adj.account := fa_cache_pkg.fazcbc_record.cost_of_removal_gain_acct;
1769 l_adj.adjustment_amount := p_asset_retire_rec.cost_of_removal;
1770 l_adj.flush_adj_flag := FALSE;
1771 l_adj.track_member_flag := 'Y';
1772
1773 --tk_util.DumpAdjRec(l_adj, 'Member COR');
1774
1775 if (p_log_level_rec.statement_level) then
1776 fa_debug_pkg.add(l_calling_fn, 'Cost of Removal for member asset', l_adj.adjustment_amount,
1777 p_log_level_rec);
1778 end if;
1779
1780 if not FA_INS_ADJUST_PKG.faxinaj
1781 (l_adj,
1782 p_trans_rec.who_info.last_update_date,
1783 p_trans_rec.who_info.last_updated_by,
1784 p_trans_rec.who_info.last_update_login,
1785 p_log_level_rec) then
1786 raise calc_err;
1787 end if;
1788 end if;
1789
1790 if (nvl(p_asset_retire_rec.proceeds_of_sale, 0) <> 0) then
1791 l_adj.adjustment_type := 'PROCEEDS CLR';
1792 l_adj.debit_credit_flag := 'DR';
1793 l_adj.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1794 l_adj.account := fa_cache_pkg.fazcbc_record.proceeds_of_sale_gain_acct;
1795 l_adj.flush_adj_flag := FALSE;
1796 l_adj.track_member_flag := 'Y';
1797
1798 --tk_util.DumpAdjRec(l_adj, 'MemberPOS');
1799
1800 if (p_log_level_rec.statement_level) then
1801 fa_debug_pkg.add(l_calling_fn, 'Proceeds of Sale for member asset', l_adj.adjustment_amount,p_log_level_rec);
1802 end if;
1803
1804 if not FA_INS_ADJUST_PKG.faxinaj
1805 (l_adj,
1806 p_trans_rec.who_info.last_update_date,
1807 p_trans_rec.who_info.last_updated_by,
1808 p_trans_rec.who_info.last_update_login,
1809 p_log_level_rec) then
1810 raise calc_err;
1811 end if;
1812 end if;
1813 end if; -- ((p_asset_fin_rec.tracking_method <> 'ALLOCATE')
1814
1815 end if; -- (p_asset_fin_rec.tracking_method = 'ALLOCATE') or
1816 -- End of Creating Member Entires for Tracking
1817
1818
1819 --***********************************************
1820 -- Prepare local variables with group information
1821 --
1822 l_asset_hdr_rec.asset_id := p_asset_fin_rec.group_asset_id;
1823 l_asset_hdr_rec.book_type_code := p_asset_hdr_rec.book_type_code;
1824 l_asset_hdr_rec.set_of_books_id := p_asset_hdr_rec.set_of_books_id;
1825 -- l_asset_hdr_rec.period_of_addition := 'N'; -- Set this if necessary
1826
1827 if not FA_UTIL_PVT.get_asset_cat_rec (
1828 p_asset_hdr_rec => l_asset_hdr_rec,
1829 px_asset_cat_rec => l_asset_cat_rec,
1830 p_date_effective => NULL,
1831 p_log_level_rec => p_log_level_rec) then
1832 raise calc_err;
1833 end if;
1834
1835 --
1836 -- Set category book cache w/ group information.
1837 --
1838 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
1839 l_asset_cat_rec.category_id,
1840 p_log_level_rec) then
1841
1842 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
1843 p_log_level_rec => p_log_level_rec);
1844 raise calc_err;
1845 end if;
1846
1847 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
1848 l_adj.transaction_header_id := p_group_thid;
1849 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1850 l_adj.distribution_id := 0;
1851
1852 l_adj.adjustment_type := 'RESERVE';
1853 l_adj.debit_credit_flag := 'DR';
1854 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1855 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1856 l_adj.adjustment_amount := p_asset_retire_rec.cost_retired;
1857 l_adj.track_member_flag := null;
1858
1859 if (nvl(p_asset_retire_rec.cost_of_removal, 0) = 0) and
1860 (nvl(p_asset_retire_rec.proceeds_of_sale, 0) = 0) then
1861 l_adj.flush_adj_flag := TRUE;
1862 end if;
1863
1864 --tk_util.DumpAdjRec(l_adj, 'RSV');
1865 if not FA_INS_ADJUST_PKG.faxinaj
1866 (l_adj,
1867 p_trans_rec.who_info.last_update_date,
1868 p_trans_rec.who_info.last_updated_by,
1869 p_trans_rec.who_info.last_update_login,
1870 p_log_level_rec) then
1871 raise calc_err;
1872 end if;
1873
1874 --
1875 -- NBV RETIRED/RESERVE entries as a result of limiting net proceeds to cost retired
1876 --
1877 if (p_asset_fin_rec.limit_proceeds_flag = 'Y') then
1878 if (nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
1879 nvl(p_asset_retire_rec.cost_of_removal, 0) > (p_asset_retire_rec.cost_retired -
1880 p_salvage_value_retired)) then
1881
1882 l_adj.adjustment_type := 'RESERVE';
1883 l_adj.debit_credit_flag := 'DR';
1884 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1885 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1886 l_adj.adjustment_amount := p_asset_retire_rec.detail_info.gain_loss_amount;
1887
1888 --tk_util.DumpAdjRec(l_adj, 'LNP RSV');
1889 if not FA_INS_ADJUST_PKG.faxinaj
1890 (l_adj,
1891 p_trans_rec.who_info.last_update_date,
1892 p_trans_rec.who_info.last_updated_by,
1893 p_trans_rec.who_info.last_update_login,
1894 p_log_level_rec) then
1895 raise calc_err;
1896 end if;
1897
1898
1899 l_adj.adjustment_type := 'NBV RETIRED';
1900 l_adj.debit_credit_flag := 'CR';
1901 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
1902 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
1903 l_adj.adjustment_amount := p_asset_retire_rec.detail_info.gain_loss_amount;
1904
1905 --tk_util.DumpAdjRec(l_adj, 'LNP NBVRET');
1906 if not FA_INS_ADJUST_PKG.faxinaj
1907 (l_adj,
1908 l_trans_rec.who_info.last_update_date,
1909 l_trans_rec.who_info.last_updated_by,
1910 l_trans_rec.who_info.last_update_login,
1911 p_log_level_rec) then
1912 raise calc_err;
1913 end if;
1914 end if; -- nvl(p_asset_retire_rec.proceeds_of_sale, 0) -
1915
1916
1917 end if; -- (p_asset_fin_rec.limit_proceeds_flag = 'Y')
1918
1919 --
1920 -- Create REMOVALCOST CLR and RESERVE entries
1921 --
1922 if (not(nvl(p_asset_retire_rec.cost_of_removal, 0) = 0)) then
1923 l_adj.adjustment_type := 'RESERVE';
1924 l_adj.debit_credit_flag := 'DR';
1925 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1926 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1927 l_adj.adjustment_amount := p_asset_retire_rec.cost_of_removal;
1928 --tk_util.DumpAdjRec(l_adj, 'COR');
1929 if not FA_INS_ADJUST_PKG.faxinaj
1930 (l_adj,
1931 p_trans_rec.who_info.last_update_date,
1932 p_trans_rec.who_info.last_updated_by,
1933 p_trans_rec.who_info.last_update_login,
1934 p_log_level_rec) then
1935 raise calc_err;
1936 end if;
1937
1938 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
1939 l_adj.adjustment_type := 'REMOVALCOST CLR';
1940 l_adj.debit_credit_flag := 'CR';
1941 l_adj.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1942 l_adj.account := fa_cache_pkg.fazcbc_record.cost_of_removal_gain_acct;
1943
1944 if (nvl(p_asset_retire_rec.proceeds_of_sale, 0) = 0) then
1945 l_adj.flush_adj_flag := TRUE;
1946 end if;
1947 --tk_util.DumpAdjRec(l_adj, 'COR');
1948 if not FA_INS_ADJUST_PKG.faxinaj
1949 (l_adj,
1950 p_trans_rec.who_info.last_update_date,
1951 p_trans_rec.who_info.last_updated_by,
1952 p_trans_rec.who_info.last_update_login,
1953 p_log_level_rec) then
1954 raise calc_err;
1955 end if;
1956
1957 end if;
1958
1959 --
1960 -- Create PROCEEDS CLR and RESERVE entries
1961 --
1962 if (not(nvl(p_asset_retire_rec.proceeds_of_sale, 0) = 0)) then
1963 l_adj.adjustment_type := 'RESERVE';
1964 l_adj.debit_credit_flag := 'CR';
1965 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
1966 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
1967 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
1968 l_adj.adjustment_amount := p_asset_retire_rec.proceeds_of_sale;
1969
1970 --tk_util.DumpAdjRec(l_adj, 'POS');
1971 if not FA_INS_ADJUST_PKG.faxinaj
1972 (l_adj,
1973 p_trans_rec.who_info.last_update_date,
1974 p_trans_rec.who_info.last_updated_by,
1975 p_trans_rec.who_info.last_update_login,
1976 p_log_level_rec) then
1977 raise calc_err;
1978 end if;
1979
1980 l_adj.adjustment_type := 'PROCEEDS CLR';
1981 l_adj.debit_credit_flag := 'DR';
1982 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
1983 l_adj.account_type := 'COST_OF_REMOVAL_GAIN_ACCT';
1984 l_adj.account := fa_cache_pkg.fazcbc_record.proceeds_of_sale_gain_acct;
1985 l_adj.flush_adj_flag := TRUE;
1986
1987 --tk_util.DumpAdjRec(l_adj, 'POS');
1988 if not FA_INS_ADJUST_PKG.faxinaj
1989 (l_adj,
1990 p_trans_rec.who_info.last_update_date,
1991 p_trans_rec.who_info.last_updated_by,
1992 p_trans_rec.who_info.last_update_login,
1993 p_log_level_rec) then
1994 raise calc_err;
1995 end if;
1996
1997 end if; -- (not(nvl(p_asset_retire_rec.proceeds_of_sale, 0) = 0))
1998
1999 --
2000 -- Calculating catchup only if tracking method is "CALCULATE".
2001 -- This is only necessary if this is prior period retirement.
2002 -- For now member asset prior period retirement is restricted.
2003 --
2004 if (p_asset_fin_rec.tracking_method = 'CALCULATE') and
2005 (p_period_rec.calendar_period_open_date >
2006 p_trans_rec.transaction_date_entered) then
2007
2008 l_trans_rec := p_trans_rec;
2009
2010 if not fa_util_pvt.get_asset_deprn_rec (
2011 p_asset_hdr_rec => p_asset_hdr_rec,
2012 px_asset_deprn_rec => l_asset_deprn_rec,
2013 p_mrc_sob_type_code => p_mrc_sob_type_code,
2014 p_log_level_rec => p_log_level_rec) then
2015 raise calc_err;
2016 end if;
2017
2018 l_asset_fin_rec_adj.cost := -1 * p_asset_retire_rec.cost_retired;
2019 l_asset_fin_rec_adj.unrevalued_cost := -1 * p_asset_retire_rec.cost_retired;
2020
2021 if not FA_AMORT_PVT.faxama(
2022 px_trans_rec => l_trans_rec,
2023 p_asset_hdr_rec => p_asset_hdr_rec,
2024 p_asset_desc_rec => p_asset_desc_rec,
2025 p_asset_cat_rec => p_asset_cat_rec,
2026 p_asset_type_rec => p_asset_type_rec,
2027 p_asset_fin_rec_old => p_asset_fin_rec,
2028 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
2029 px_asset_fin_rec_new => l_asset_fin_rec_new,
2030 p_asset_deprn_rec => l_asset_deprn_rec,
2031 p_period_rec => p_period_rec,
2032 p_mrc_sob_type_code => p_mrc_sob_type_code,
2033 p_running_mode => fa_std_types.FA_DPR_NORMAL,
2034 p_used_by_revaluation => null,
2035 x_deprn_exp => l_deprn_exp,
2036 x_bonus_deprn_exp => l_bonus_deprn_exp,
2037 p_log_level_rec => p_log_level_rec) then
2038 raise calc_err;
2039 end if;
2040
2041 if (nvl(l_deprn_exp, 0) <> 0) or
2042 (nvl(l_bonus_deprn_exp, 0) <> 0) then
2043
2044 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
2045 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
2046 l_adj.period_counter_created := p_period_rec.period_counter;
2047 l_adj.period_counter_adjusted := p_period_rec.period_counter;
2048 l_adj.current_units := p_asset_desc_rec.current_units;
2049 l_adj.selection_retid := 0;
2050 l_adj.leveling_flag := TRUE;
2051 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
2052 l_adj.gen_ccid_flag := TRUE;
2053 l_adj.flush_adj_flag := FALSE;
2054
2055 if (nvl(l_deprn_exp, 0) <> 0) then
2056 -- Catchup expense for Member
2057 -- Call category book cache for group to get expense account
2058 if not fa_cache_pkg.fazccb
2059 (X_book => p_asset_hdr_rec.book_type_code,
2060 X_cat_id => p_asset_cat_rec.category_id,
2061 p_log_level_rec => p_log_level_rec) then
2062 raise calc_err;
2063 end if;
2064
2065 l_adj.source_type_code := 'DEPRECIATION';
2066 l_adj.adjustment_type := 'EXPENSE';
2067 l_adj.account := fa_cache_pkg.fazccb_record.deprn_expense_acct;
2068 l_adj.account_type := 'DEPRN_EXPENSE_ACCT';
2069 l_adj.asset_id := p_asset_hdr_rec.asset_id;
2070 l_adj.adjustment_amount := abs(l_deprn_exp);
2071 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2072 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2073
2074 if (nvl(p_asset_fin_rec.member_rollup_flag, 'N') = 'N') then
2075 l_adj.track_member_flag := 'Y';
2076 else
2077 l_adj.track_member_flag := null;
2078 end if;
2079
2080 if (nvl(l_bonus_deprn_exp, 0) = 0) then
2081 l_adj.flush_adj_flag := TRUE;
2082 end if;
2083
2084 if (l_deprn_exp > 0) then
2085 l_adj.debit_credit_flag := 'CR';
2086 else
2087 l_adj.debit_credit_flag := 'DR';
2088 end if;
2089
2090 if not FA_INS_ADJUST_PKG.faxinaj
2091 (l_adj,
2092 p_trans_rec.who_info.last_update_date,
2093 p_trans_rec.who_info.last_updated_by,
2094 p_trans_rec.who_info.last_update_login,
2095 p_log_level_rec) then
2096 raise calc_err;
2097 end if;
2098 end if; --(nvl(l_deprn_exp, 0) <> 0)
2099
2100 if (nvl(l_bonus_deprn_exp, 0) <> 0) then
2101 -- Catchup bonus expense for Member
2102 -- Need to call cache function if it is not called for expense.
2103
2104 if (nvl(l_deprn_exp, 0) = 0) then
2105
2106 if not fa_cache_pkg.fazccb(
2107 X_book => p_asset_hdr_rec.book_type_code,
2108 X_cat_id => p_asset_cat_rec.category_id,
2109 p_log_level_rec => p_log_level_rec) then
2110 raise calc_err;
2111 end if;
2112 end if;
2113
2114 l_adj.source_type_code := 'DEPRECIATION';
2115 l_adj.adjustment_type := 'BONUS EXPENSE';
2116 l_adj.account := fa_cache_pkg.fazccb_record.bonus_deprn_expense_acct;
2117 l_adj.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
2118 l_adj.asset_id := p_asset_hdr_rec.asset_id;
2119 l_adj.adjustment_amount := abs(l_deprn_exp);
2120 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2121 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2122
2123 if (nvl(p_asset_fin_rec.member_rollup_flag, 'N') = 'N') then
2124 l_adj.track_member_flag := 'Y';
2125 else
2126 l_adj.track_member_flag := null;
2127 end if;
2128 l_adj.flush_adj_flag := TRUE;
2129
2130 if (l_deprn_exp > 0) then
2131 l_adj.debit_credit_flag := 'CR';
2132 else
2133 l_adj.debit_credit_flag := 'DR';
2134 end if;
2135
2136 if not FA_INS_ADJUST_PKG.faxinaj
2137 (l_adj,
2138 p_trans_rec.who_info.last_update_date,
2139 p_trans_rec.who_info.last_updated_by,
2140 p_trans_rec.who_info.last_update_login,
2141 p_log_level_rec) then
2142 raise calc_err;
2143 end if;
2144 end if; -- (nvl(l_bonus_deprn_exp, 0) <> 0)
2145
2146 end if; -- (nvl(l_deprn_exp, 0) <> 0) or
2147
2148 end if; -- (p_asset_fin_rec.tracking_method = 'CALCULATE')
2149
2150 end if; --p_mode <> 'CR'
2151
2152 if (p_log_level_rec.statement_level) then
2153 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
2154 end if;
2155
2156 return true;
2157
2158 EXCEPTION
2159 when calc_err then
2160 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2161 p_log_level_rec => p_log_level_rec);
2162
2163 return false;
2164
2165 when OTHERS then
2166 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
2167 ,p_log_level_rec => p_log_level_rec);
2168
2169 return false;
2170
2171 END CALC_GAIN_LOSS_FOR_RET;
2172
2173 /*====================================================================+
2174 | Function |
2175 | CALC_GAIN_LOSS_FOR_REI |
2176 | |
2177 | Description |
2178 | This function maintain FA_(MC_)ADJUSTMENTS table for group |
2179 | reinstatement. |
2180 | |
2181 +====================================================================*/
2182 FUNCTION CALC_GAIN_LOSS_FOR_REI(
2183 p_trans_rec IN FA_API_TYPES.trans_rec_type,
2184 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
2185 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
2186 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
2187 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
2188 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
2189 p_period_rec IN FA_API_TYPES.period_rec_type,
2190 p_asset_retire_rec IN FA_API_TYPES.asset_retire_rec_type,
2191 p_group_thid IN NUMBER,
2192 x_asset_fin_rec OUT NOCOPY FA_API_TYPES.asset_fin_rec_type,
2193 p_mrc_sob_type_code IN VARCHAR2,
2194 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2195 RETURN BOOLEAN IS
2196
2197 l_calling_fn VARCHAR2(100) := 'fa_group_retirement_pvt.calc_gain_loss_for_rei';
2198
2199 -- Cursor to get transaction header id of transfer out in
2200 -- case of partial unit retirement
2201 CURSOR c_get_thid_for_dist IS
2202 /* select transaction_header_id_out
2203 from fa_distribution_history
2204 where asset_id = p_asset_hdr_rec.asset_id
2205 and book_type_code = p_asset_hdr_rec.book_type_code
2206 and retirement_id = p_asset_retire_rec.retirement_id;
2207 bug 4411892*/
2208 select transaction_header_id_out
2209 from fa_retirements
2210 where asset_id = p_asset_hdr_rec.asset_id
2211 and retirement_id = p_asset_retire_rec.retirement_id
2212 and book_type_code = p_asset_hdr_rec.book_type_code;
2213
2214 CURSOR c_get_group_thid IS
2215 SELECT transaction_header_id,
2216 asset_id,
2217 date_effective
2218 FROM fa_transaction_headers
2219 WHERE member_transaction_header_id =
2220 p_asset_retire_rec.detail_info.transaction_header_id_in
2221 AND asset_id = p_asset_fin_rec.group_asset_id
2222 AND book_type_code = p_asset_hdr_rec.book_type_code;
2223
2224 l_group_thid NUMBER(15);
2225 l_group_asset_id NUMBER(15);
2226 l_date_effective DATE;
2227 l_period_rec FA_API_TYPES.period_rec_type;
2228
2229 -- BUG# 3031357
2230 -- adding additional columns to use N2 index for performance
2231
2232 CURSOR c_mc_pos_cor_adj IS
2233 SELECT TRANSACTION_HEADER_ID,
2234 SOURCE_TYPE_CODE,
2235 ADJUSTMENT_TYPE,
2236 DEBIT_CREDIT_FLAG,
2237 CODE_COMBINATION_ID,
2238 BOOK_TYPE_CODE,
2239 ASSET_ID,
2240 ADJUSTMENT_AMOUNT,
2241 DISTRIBUTION_ID,
2242 ANNUALIZED_ADJUSTMENT,
2243 PERIOD_COUNTER_ADJUSTED,
2244 PERIOD_COUNTER_CREATED,
2245 ASSET_INVOICE_ID
2246 FROM FA_ADJUSTMENTS_MRC_V
2247 WHERE TRANSACTION_HEADER_ID = l_group_thid
2248 AND ASSET_ID = l_group_asset_id
2249 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
2250 AND PERIOD_COUNTER_CREATED = l_period_rec.period_counter
2251 AND ADJUSTMENT_TYPE in ('PROCEEDS CLR', 'REMOVALCOST CLR', 'NBV RETIRED');
2252
2253 CURSOR c_pos_cor_adj IS
2254 SELECT TRANSACTION_HEADER_ID,
2255 SOURCE_TYPE_CODE,
2256 ADJUSTMENT_TYPE,
2257 DEBIT_CREDIT_FLAG,
2258 CODE_COMBINATION_ID,
2259 BOOK_TYPE_CODE,
2260 ASSET_ID,
2261 ADJUSTMENT_AMOUNT,
2262 DISTRIBUTION_ID,
2263 ANNUALIZED_ADJUSTMENT,
2264 PERIOD_COUNTER_ADJUSTED,
2265 PERIOD_COUNTER_CREATED,
2266 ASSET_INVOICE_ID
2267 FROM FA_ADJUSTMENTS
2268 WHERE TRANSACTION_HEADER_ID = l_group_thid
2269 AND ASSET_ID = l_group_asset_id
2270 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
2271 AND PERIOD_COUNTER_CREATED = l_period_rec.period_counter
2272 AND ADJUSTMENT_TYPE in ('PROCEEDS CLR', 'REMOVALCOST CLR', 'NBV RETIRED');
2273
2274 --kawa
2275 -- CURSOR c_get_ret_reserve
2276
2277 l_ret_period_counter NUMBER := to_number(null);
2278 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
2279
2280 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
2281 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
2282
2283 l_trans_rec FA_API_TYPES.trans_rec_type;
2284 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
2285 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
2286 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
2287
2288 l_deprn_exp NUMBER;
2289 l_bonus_deprn_exp NUMBER;
2290
2291 calc_err EXCEPTION;
2292
2293
2294 BEGIN
2295
2296 if (p_log_level_rec.statement_level) then
2297 fa_debug_pkg.add(l_calling_fn, 'Begin', p_asset_hdr_rec.asset_id||':'||p_asset_fin_rec.group_asset_id,p_log_level_rec);
2298 end if;
2299
2300 -- BUG# 3641747
2301 -- call cache as it was not yet initialized
2302 if not fa_cache_pkg.fazccb(p_asset_hdr_rec.book_type_code,
2303 p_asset_cat_rec.category_id,
2304 p_log_level_rec) then
2305 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2306 p_log_level_rec => p_log_level_rec);
2307 raise calc_err;
2308 end if;
2309
2310 --
2311 -- populate FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT(l_adj)to
2312 -- call FA_INS_ADJUST_PKG.faxinaj
2313 --
2314 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
2315 l_adj.source_type_code := 'RETIREMENT';
2316 l_adj.period_counter_created := p_period_rec.period_counter;
2317 l_adj.period_counter_adjusted := p_period_rec.period_counter;
2318 l_adj.current_units := p_asset_desc_rec.current_units;
2319 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
2320 l_adj.selection_thid := 0;
2321 l_adj.selection_retid := 0;
2322 l_adj.leveling_flag := TRUE;
2323 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
2324
2325 l_adj.flush_adj_flag := FALSE;
2326 l_adj.gen_ccid_flag := TRUE;
2327 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2328
2329 l_adj.asset_id := p_asset_hdr_rec.asset_id;
2330
2331 l_adj.adjustment_type := 'COST';
2332 l_adj.code_combination_id := 0;
2333 l_adj.adjustment_amount := p_asset_retire_rec.cost_retired;
2334 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2335
2336 -- +++++ Process Cost entries +++++
2337 if p_asset_type_rec.asset_type = 'CIP' then
2338 l_adj.source_type_code := 'CIP RETIREMENT';
2339 l_adj.account := fa_cache_pkg.fazccb_record.cip_cost_acct;
2340 l_adj.account_type := 'CIP_COST_ACCT';
2341 else
2342 l_adj.source_type_code := 'RETIREMENT';
2343 l_adj.account := fa_cache_pkg.fazccb_record.asset_cost_acct;
2344 l_adj.account_type := 'ASSET_COST_ACCT';
2345 end if;
2346
2347 if ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
2348 (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE')) and
2349 (p_asset_desc_rec.current_units > p_asset_retire_rec.units_retired) then
2350 OPEN c_get_thid_for_dist;
2351 FETCH c_get_thid_for_dist INTO l_adj.selection_thid;
2352 CLOSE c_get_thid_for_dist;
2353
2354 if (nvl(l_adj.selection_thid, 0) = 0) then
2355 raise calc_err;
2356 end if;
2357
2358 l_adj.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
2359 l_adj.debit_credit_flag := 'CR';
2360 else
2361 l_adj.debit_credit_flag := 'DR';
2362 l_adj.selection_thid := 0;
2363 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2364 end if;
2365
2366 --tk_util.DumpAdjRec(l_adj, l_adj.debit_credit_flag||':'||to_char(nvl(p_asset_retire_rec.units_retired, 0)));
2367 if not FA_INS_ADJUST_PKG.faxinaj
2368 (l_adj,
2369 p_trans_rec.who_info.last_update_date,
2370 p_trans_rec.who_info.last_updated_by,
2371 p_trans_rec.who_info.last_update_login,
2372 p_log_level_rec) then
2373 raise calc_err;
2374 end if;
2375
2376 if ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
2377 (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE')) and
2378 (p_asset_desc_rec.current_units > p_asset_retire_rec.units_retired) then
2379
2380 --
2381 -- With unkonwn reason l_adj.account is set to null so
2382 -- need to resert the same value.
2383 --
2384 if p_asset_type_rec.asset_type = 'CIP' then
2385 l_adj.account := fa_cache_pkg.fazccb_record.cip_cost_acct;
2386 else
2387 l_adj.account := fa_cache_pkg.fazccb_record.asset_cost_acct;
2388 end if;
2389
2390 l_adj.selection_thid := 0;
2391 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2392 l_adj.current_units := p_asset_desc_rec.current_units;
2393 l_adj.debit_credit_flag := 'DR';
2394 l_adj.leveling_flag := FALSE;
2395 l_adj.adjustment_amount := l_adj.amount_inserted +
2396 p_asset_retire_rec.cost_retired;
2397 --tk_util.DumpAdjRec(l_adj, 'URDR');
2398 if not FA_INS_ADJUST_PKG.faxinaj
2399 (l_adj,
2400 p_trans_rec.who_info.last_update_date,
2401 p_trans_rec.who_info.last_updated_by,
2402 p_trans_rec.who_info.last_update_login,
2403 p_log_level_rec) then
2404 raise calc_err;
2405 end if;
2406
2407 end if; -- ((nvl(p_asset_retire_rec.units_retired, 0) > 0) and
2408
2409 -- ENERGY
2410 if (not fa_cache_pkg.fazccmt(p_asset_fin_rec.deprn_method_code,
2411 p_asset_fin_rec.life_in_months
2412 ,p_log_level_rec => p_log_level_rec)) then
2413 if (p_log_level_rec.statement_level) then
2414 fa_debug_pkg.add(l_calling_fn, 'Error calling', 'fa_cache_pkg.fazccmt',p_log_level_rec);
2415 end if;
2416
2417 raise calc_err;
2418 end if;
2419
2420 if (p_log_level_rec.statement_level) then
2421 fa_debug_pkg.add(l_calling_fn, 'tracking_method',
2422 p_asset_fin_rec.tracking_method,p_log_level_rec);
2423 fa_debug_pkg.add(l_calling_fn, 'rule_name',
2424 fa_cache_pkg.fazcdrd_record.rule_name,p_log_level_rec);
2425 end if;
2426 -- toru
2427 -- Energy: Need to reinstate member reserve entry cre
2428 -- BUG# 6899255
2429 -- handle all allocate cases the same way rather than just energy
2430 -- (l_group_db_rule_name = 'ENERGY PERIOD END BALANCE')
2431 if (nvl(p_asset_fin_rec.tracking_method, 'NO TRACK') = 'ALLOCATE') then -- ENERGY
2432 if (p_log_level_rec.statement_level) then
2433 fa_debug_pkg.add(l_calling_fn, 'reserve retired',
2434 p_asset_retire_rec.reserve_retired,p_log_level_rec);
2435 end if;
2436
2437 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2438 l_adj.distribution_id := 0;
2439
2440 l_adj.adjustment_type := 'RESERVE';
2441 l_adj.debit_credit_flag := 'CR';
2442 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
2443 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
2444 l_adj.adjustment_amount := p_asset_retire_rec.reserve_retired;
2445 l_adj.track_member_flag := 'Y';
2446
2447 --tk_util.DumpAdjRec(l_adj, 'mRSV');
2448 if not FA_INS_ADJUST_PKG.faxinaj
2449 (l_adj,
2450 p_trans_rec.who_info.last_update_date,
2451 p_trans_rec.who_info.last_updated_by,
2452 p_trans_rec.who_info.last_update_login,
2453 p_log_level_rec) then
2454 raise calc_err;
2455 end if;
2456
2457 end if; -- ENERGY
2458
2459 --
2460 -- Process Reserve
2461 -- Prepare local variables with group information so category cache will
2462 -- hold group category. This is because Reserve, POS, and COR entries are
2463 -- created with group info.
2464 --
2465 l_asset_hdr_rec.asset_id := p_asset_fin_rec.group_asset_id;
2466 l_asset_hdr_rec.book_type_code := p_asset_hdr_rec.book_type_code;
2467 l_asset_hdr_rec.set_of_books_id := p_asset_hdr_rec.set_of_books_id;
2468 -- l_asset_hdr_rec.period_of_addition := 'N'; -- Set this if necessary
2469
2470 if not FA_UTIL_PVT.get_asset_cat_rec (
2471 p_asset_hdr_rec => l_asset_hdr_rec,
2472 px_asset_cat_rec => l_asset_cat_rec,
2473 p_date_effective => NULL,
2474 p_log_level_rec => p_log_level_rec) then
2475 raise calc_err;
2476 end if;
2477
2478 --
2479 -- Set category book cache w/ group information.
2480 --
2481 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
2482 l_asset_cat_rec.category_id,
2483 p_log_level_rec) then
2484 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2485 p_log_level_rec => p_log_level_rec);
2486 raise calc_err;
2487 end if;
2488
2489 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
2490 l_adj.transaction_header_id := p_group_thid;
2491 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2492 l_adj.distribution_id := 0;
2493
2494 l_adj.adjustment_type := 'RESERVE';
2495 l_adj.debit_credit_flag := 'CR';
2496 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
2497 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
2498 l_adj.adjustment_amount := p_asset_retire_rec.cost_retired;
2499 l_adj.track_member_flag := null;
2500
2501 --tk_util.DumpAdjRec(l_adj, 'RSV');
2502 if not FA_INS_ADJUST_PKG.faxinaj
2503 (l_adj,
2504 p_trans_rec.who_info.last_update_date,
2505 p_trans_rec.who_info.last_updated_by,
2506 p_trans_rec.who_info.last_update_login,
2507 p_log_level_rec) then
2508 raise calc_err;
2509 end if;
2510
2511 -- +++ Get Group's transaciton_header_id for retirement to be reinstated
2512 OPEN c_get_group_thid;
2513 FETCH c_get_group_thid INTO l_group_thid, l_group_asset_id, l_date_effective;
2514 CLOSE c_get_group_thid;
2515
2516 if not FA_UTIL_PVT.get_period_rec(
2517 p_book => l_asset_hdr_rec.book_type_code,
2518 p_effective_date => l_date_effective,
2519 x_period_rec => l_period_rec,
2520 p_log_level_rec => p_log_level_rec) then
2521 raise calc_err;
2522 end if;
2523
2524 --
2525 -- Reinstate Proceeds of Sale and Cost of Removal and reserve entries for
2526 -- each of these.
2527 --
2528 if (p_mrc_sob_type_code <> 'R') then
2529 for r_pos_cor_adj in c_pos_cor_adj loop
2530 if (r_pos_cor_adj.debit_credit_flag = 'CR') then
2531 l_adj.debit_credit_flag := 'DR';
2532 else
2533 l_adj.debit_credit_flag := 'CR';
2534 end if;
2535
2536 l_adj.adjustment_type := r_pos_cor_adj.adjustment_type;
2537 l_adj.code_combination_id := r_pos_cor_adj.code_combination_id;
2538 l_adj.asset_id := r_pos_cor_adj.asset_id;
2539 l_adj.adjustment_amount := r_pos_cor_adj.adjustment_amount;
2540 l_adj.distribution_id := r_pos_cor_adj.distribution_id;
2541 --tk_util.DumpAdjRec(l_adj, r_pos_cor_adj.adjustment_type);
2542 if not FA_INS_ADJUST_PKG.faxinaj
2543 (l_adj,
2544 p_trans_rec.who_info.last_update_date,
2545 p_trans_rec.who_info.last_updated_by,
2546 p_trans_rec.who_info.last_update_login,
2547 p_log_level_rec) then
2548 raise calc_err;
2549 end if;
2550
2551 --
2552 -- Set category book cache w/ group information.
2553 --
2554 if (l_asset_cat_rec.category_id is null) then
2555 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
2556 l_asset_cat_rec.category_id,
2557 p_log_level_rec) then
2558 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2559 p_log_level_rec => p_log_level_rec);
2560 raise calc_err;
2561 end if;
2562 end if;
2563
2564 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
2565 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2566 l_adj.distribution_id := 0;
2567 l_adj.adjustment_type := 'RESERVE';
2568
2569 if (l_adj.debit_credit_flag = 'CR') then
2570 l_adj.debit_credit_flag := 'DR';
2571 else
2572 l_adj.debit_credit_flag := 'CR';
2573 end if;
2574
2575 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
2576 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
2577
2578 --tk_util.DumpAdjRec(l_adj, 'RSV');
2579 if not FA_INS_ADJUST_PKG.faxinaj
2580 (l_adj,
2581 p_trans_rec.who_info.last_update_date,
2582 p_trans_rec.who_info.last_updated_by,
2583 p_trans_rec.who_info.last_update_login,
2584 p_log_level_rec) then
2585 raise calc_err;
2586 end if;
2587
2588 end loop;
2589 else -- Reporting Book
2590 for r_pos_cor_adj in c_mc_pos_cor_adj loop
2591 if (r_pos_cor_adj.debit_credit_flag = 'CR') then
2592 l_adj.debit_credit_flag := 'DR';
2593 else
2594 l_adj.debit_credit_flag := 'CR';
2595 end if;
2596
2597 l_adj.adjustment_type := r_pos_cor_adj.adjustment_type;
2598 l_adj.code_combination_id := r_pos_cor_adj.code_combination_id;
2599 l_adj.asset_id := r_pos_cor_adj.asset_id;
2600 l_adj.adjustment_amount := r_pos_cor_adj.adjustment_amount;
2601 l_adj.distribution_id := r_pos_cor_adj.distribution_id;
2602
2603 if not FA_INS_ADJUST_PKG.faxinaj
2604 (l_adj,
2605 p_trans_rec.who_info.last_update_date,
2606 p_trans_rec.who_info.last_updated_by,
2607 p_trans_rec.who_info.last_update_login,
2608 p_log_level_rec) then
2609 raise calc_err;
2610 end if;
2611
2612 --
2613 -- Set category book cache w/ group information.
2614 --
2615 if (l_asset_cat_rec.category_id is null) then
2616 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
2617 l_asset_cat_rec.category_id,
2618 p_log_level_rec) then
2619 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2620 p_log_level_rec => p_log_level_rec);
2621 raise calc_err;
2622 end if;
2623 end if;
2624
2625 l_adj.asset_id := p_asset_fin_rec.group_asset_id;
2626 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2627 l_adj.distribution_id := 0;
2628 l_adj.adjustment_type := 'RESERVE';
2629
2630 if (l_adj.debit_credit_flag = 'CR') then
2631 l_adj.debit_credit_flag := 'DR';
2632 else
2633 l_adj.debit_credit_flag := 'CR';
2634 end if;
2635
2636 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
2637 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
2638
2639 --tk_util.DumpAdjRec(l_adj, 'RSV');
2640 if not FA_INS_ADJUST_PKG.faxinaj
2641 (l_adj,
2642 p_trans_rec.who_info.last_update_date,
2643 p_trans_rec.who_info.last_updated_by,
2644 p_trans_rec.who_info.last_update_login,
2645 p_log_level_rec) then
2646 raise calc_err;
2647 end if;
2648
2649 end loop;
2650
2651 end if; --(p_mrc_sob_type_code <> 'R')
2652
2653 --
2654 -- Flash adjustments cache.
2655 --
2656 l_adj.flush_adj_flag := TRUE;
2657 l_adj.transaction_header_id := 0;
2658 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2659
2660
2661 if not FA_INS_ADJUST_PKG.faxinaj
2662 (l_adj,
2663 p_trans_rec.who_info.last_update_date,
2664 p_trans_rec.who_info.last_updated_by,
2665 p_trans_rec.who_info.last_update_login,
2666 p_log_level_rec) then
2667 raise calc_err;
2668 end if;
2669
2670 l_trans_rec := p_trans_rec;
2671
2672 if not fa_util_pvt.get_asset_deprn_rec (
2673 p_asset_hdr_rec => p_asset_hdr_rec,
2674 px_asset_deprn_rec => l_asset_deprn_rec,
2675 p_mrc_sob_type_code => p_mrc_sob_type_code,
2676 p_log_level_rec => p_log_level_rec) then
2677 raise calc_err;
2678 end if;
2679
2680 l_asset_fin_rec_adj.cost := p_asset_retire_rec.cost_retired;
2681 l_asset_fin_rec_adj.unrevalued_cost := p_asset_retire_rec.cost_retired;
2682 --tk_util.debug('recovering cost : '||to_char(l_asset_fin_rec_adj.cost));
2683
2684 if not FA_AMORT_PVT.faxama
2685 (px_trans_rec => l_trans_rec,
2686 p_asset_hdr_rec => p_asset_hdr_rec,
2687 p_asset_desc_rec => p_asset_desc_rec,
2688 p_asset_cat_rec => p_asset_cat_rec,
2689 p_asset_type_rec => p_asset_type_rec,
2690 p_asset_fin_rec_old => p_asset_fin_rec,
2691 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
2692 px_asset_fin_rec_new => l_asset_fin_rec_new,
2693 p_asset_deprn_rec => l_asset_deprn_rec,
2694 p_period_rec => p_period_rec,
2695 p_mrc_sob_type_code => p_mrc_sob_type_code,
2696 p_running_mode => fa_std_types.FA_DPR_NORMAL,
2697 p_used_by_revaluation => null,
2698 x_deprn_exp => l_deprn_exp,
2699 x_bonus_deprn_exp => l_bonus_deprn_exp,
2700 p_log_level_rec => p_log_level_rec) then
2701 raise calc_err;
2702 end if;
2703
2704 x_asset_fin_rec := l_asset_fin_rec_new;
2705 --tk_util.debug('new cost : '||to_char(x_asset_fin_rec.cost));
2706 --tk_util.debug('new adj cost : '||to_char(x_asset_fin_rec.adjusted_cost));
2707
2708 --
2709 -- Calculating catchup only if tracking method is "CALCULATE".
2710 -- This is only necessary if this is prior period retirement.
2711 -- For now member asset prior period retirement is restricted.
2712 --
2713 if (p_asset_fin_rec.tracking_method = 'CALCULATE') and
2714 (p_period_rec.calendar_period_open_date >
2715 p_trans_rec.transaction_date_entered) then
2716
2717
2718 if (nvl(l_deprn_exp, 0) <> 0) or
2719 (nvl(l_bonus_deprn_exp, 0) <> 0) then
2720
2721 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
2722 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
2723 l_adj.period_counter_created := p_period_rec.period_counter;
2724 l_adj.period_counter_adjusted := p_period_rec.period_counter;
2725 l_adj.current_units := p_asset_desc_rec.current_units;
2726 l_adj.selection_retid := 0;
2727 l_adj.leveling_flag := TRUE;
2728 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
2729 l_adj.gen_ccid_flag := TRUE;
2730 l_adj.flush_adj_flag := FALSE;
2731
2732 if (nvl(l_deprn_exp, 0) <> 0) then
2733 -- Catchup expense for Member
2734 -- Call category book cache for group to get expense account
2735
2736 if not fa_cache_pkg.fazccb
2737 (X_book => p_asset_hdr_rec.book_type_code,
2738 X_cat_id => p_asset_cat_rec.category_id,
2739 p_log_level_rec => p_log_level_rec) then
2740 raise calc_err;
2741 end if;
2742
2743 l_adj.source_type_code := 'DEPRECIATION';
2744 l_adj.adjustment_type := 'EXPENSE';
2745 l_adj.account := fa_cache_pkg.fazccb_record.deprn_expense_acct;
2746 l_adj.account_type := 'DEPRN_EXPENSE_ACCT';
2747 l_adj.asset_id := p_asset_hdr_rec.asset_id;
2748 l_adj.adjustment_amount := abs(l_deprn_exp);
2749 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2750 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2751
2752 if (nvl(p_asset_fin_rec.member_rollup_flag, 'N') = 'N') then
2753 l_adj.track_member_flag := 'Y';
2754 else
2755 l_adj.track_member_flag := null;
2756 end if;
2757
2758 if (nvl(l_bonus_deprn_exp, 0) = 0) then
2759 l_adj.flush_adj_flag := TRUE;
2760 end if;
2761
2762 if (l_deprn_exp > 0) then
2763 l_adj.debit_credit_flag := 'CR';
2764 else
2765 l_adj.debit_credit_flag := 'DR';
2766 end if;
2767
2768 if not FA_INS_ADJUST_PKG.faxinaj
2769 (l_adj,
2770 p_trans_rec.who_info.last_update_date,
2771 p_trans_rec.who_info.last_updated_by,
2772 p_trans_rec.who_info.last_update_login,
2773 p_log_level_rec) then
2774 raise calc_err;
2775 end if;
2776 end if; --(nvl(l_deprn_exp, 0) <> 0)
2777
2778 if (nvl(l_bonus_deprn_exp, 0) <> 0) then
2779 -- Catchup bonus expense for Member
2780 -- Need to call cache function if it is not called for expense.
2781
2782 if (nvl(l_deprn_exp, 0) = 0) then
2783 if not fa_cache_pkg.fazccb
2784 (X_book => p_asset_hdr_rec.book_type_code,
2785 X_cat_id => p_asset_cat_rec.category_id,
2786 p_log_level_rec => p_log_level_rec) then
2787 raise calc_err;
2788 end if;
2789 end if;
2790
2791 l_adj.source_type_code := 'DEPRECIATION';
2792 l_adj.adjustment_type := 'BONUS EXPENSE';
2793 l_adj.account := fa_cache_pkg.fazccb_record.bonus_deprn_expense_acct;
2794 l_adj.account_type := 'BONUS_DEPRN_EXPENSE_ACCT';
2795 l_adj.asset_id := p_asset_hdr_rec.asset_id;
2796 l_adj.adjustment_amount := abs(l_deprn_exp);
2797 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
2798 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
2799
2800 if (nvl(p_asset_fin_rec.member_rollup_flag, 'N') = 'N') then
2801 l_adj.track_member_flag := 'Y';
2802 else
2803 l_adj.track_member_flag := null;
2804 end if;
2805 l_adj.flush_adj_flag := TRUE;
2806
2807 if (l_deprn_exp > 0) then
2808 l_adj.debit_credit_flag := 'CR';
2809 else
2810 l_adj.debit_credit_flag := 'DR';
2811 end if;
2812
2813 if not FA_INS_ADJUST_PKG.faxinaj
2814 (l_adj,
2815 p_trans_rec.who_info.last_update_date,
2816 p_trans_rec.who_info.last_updated_by,
2817 p_trans_rec.who_info.last_update_login,
2818 p_log_level_rec) then
2819 raise calc_err;
2820 end if;
2821 end if; -- (nvl(l_bonus_deprn_exp, 0) <> 0)
2822
2823 end if; -- (nvl(l_deprn_exp, 0) <> 0) or
2824
2825 end if; -- (p_asset_fin_rec.tracking_method = 'CALCULATE')
2826
2827 if (p_log_level_rec.statement_level) then
2828 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
2829 end if;
2830
2831 return true;
2832
2833 EXCEPTION
2834 when calc_err then
2835 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
2836 p_log_level_rec => p_log_level_rec);
2837
2838 return false;
2839
2840 when OTHERS then
2841 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
2842 ,p_log_level_rec => p_log_level_rec);
2843
2844 return false;
2845
2846 END CALC_GAIN_LOSS_FOR_REI;
2847
2848 FUNCTION Do_Retirement_in_CGL(
2849 p_ret IN fa_ret_types.ret_struct,
2850 p_bk IN fa_ret_types.book_struct,
2851 p_dpr IN fa_STD_TYPES.dpr_struct,
2852 p_asset_deprn_rec_old IN FA_API_TYPES.asset_deprn_rec_type,
2853 p_mrc_sob_type_code IN VARCHAR2,
2854 p_calling_fn IN VARCHAR2,
2855 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2856 return boolean IS
2857
2858 CURSOR c_get_thid is
2859 select fa_transaction_headers_s.nextval
2860 from dual;
2861
2862 /* Bug - 7011548 Cursor Added to fetch Current value of the Sequence for MRC book.
2863 Earlier the Transaction_Header_id was missing for MRC Book. For MRC it
2864 is the Current Value of the Sequence.
2865 */
2866 CURSOR c_get_thid_mrc is
2867 select fa_transaction_headers_s.currval
2868 from dual;
2869
2870 CURSOR c_get_thx is
2871 select trx.transaction_date_entered
2872 , trx.last_update_date
2873 , trx.last_updated_by
2874 , trx.last_update_login
2875 , trx.mass_transaction_id
2876 , trx.calling_interface
2877 , trx.mass_reference_id
2878 , nvl(trx.amortization_start_date, trx.transaction_date_entered)
2879 from fa_transaction_headers trx
2880 where trx.transaction_header_id = p_ret.th_id_in;
2881
2882 CURSOR c_member_exists IS
2883 select 'Y'
2884 from fa_books
2885 where book_type_code = p_ret.book
2886 and group_asset_id = p_bk.group_asset_id
2887 and ((period_counter_fully_retired is null)
2888 or (period_counter_fully_retired is not null
2889 and retirement_pending_flag = 'YES'))
2890 and transaction_header_id_out is null;
2891
2892 CURSOR c_get_ret_info IS
2893 select -1 * eofy_reserve
2894 from fa_retirements
2895 where retirement_id = p_ret.retirement_id;
2896
2897 CURSOR c_get_mc_ret_info IS
2898 select -1 * eofy_reserve
2899 from fa_retirements_mrc_v
2900 where retirement_id = p_ret.retirement_id;
2901
2902 l_calling_fn VARCHAR2(100) := 'fa_retirement_pvt.Do_Retirement_in_CGL';
2903
2904 l_trans_rec FA_API_TYPES.trans_rec_type;
2905 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
2906 l_asset_hdr_rec_m FA_API_TYPES.asset_hdr_rec_type;
2907 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
2908 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
2909 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
2910 l_asset_fin_rec_new_m FA_API_TYPES.asset_fin_rec_type;
2911 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
2912 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
2913 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
2914 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
2915 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
2916 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
2917 l_period_rec FA_API_TYPES.period_rec_type;
2918 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
2919
2920 l_member_exists VARCHAR2(1) := 'N';
2921 l_deprn_reserve NUMBER;
2922 l_temp_num NUMBER;
2923 l_temp_char VARCHAR2(30);
2924 l_temp_bool BOOLEAN;
2925
2926 l_new_reserve NUMBER;
2927 l_new_rec_cost NUMBER;
2928 l_recapture_amount NUMBER;
2929
2930 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
2931
2932 l_rowid ROWID; -- temp variable
2933
2934 calc_err EXCEPTION;
2935
2936 BEGIN
2937 if (p_log_level_rec.statement_level) then
2938 fa_debug_pkg.add(l_calling_fn, 'Begin',
2939 p_ret.asset_id||':'||p_bk.group_asset_id,p_log_level_rec);
2940 end if;
2941
2942 --
2943 -- Prepare to call FA_ADJUSTMENT_PVT.do_adjustment to process group
2944 -- asset after member asset retirement.
2945 --
2946 if (p_mrc_sob_type_code <> 'R') then
2947 OPEN c_get_thid;
2948 FETCH c_get_thid INTO l_trans_rec.transaction_header_id;
2949 CLOSE c_get_thid;
2950 else /*Bug - 7011548 For MRC book the Transaction_Hear_Id is the current value of the sequence*/
2951 OPEN c_get_thid_mrc;
2952 FETCH c_get_thid_mrc INTO l_trans_rec.transaction_header_id;
2953 CLOSE c_get_thid_mrc;
2954 end if;
2955
2956 --tk_util.debug('l_trans_rec.transaction_header_id: '||to_char(l_trans_rec.transaction_header_id));
2957
2958 l_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
2959 l_trans_rec.transaction_subtype := 'AMORTIZED';
2960 l_trans_rec.member_transaction_header_id := p_ret.th_id_in;
2961 l_trans_rec.transaction_key := 'MR';
2962
2963 OPEN c_get_thx;
2964 FETCH c_get_thx INTO l_trans_rec.transaction_date_entered
2965 , l_trans_rec.who_info.last_update_date
2966 , l_trans_rec.who_info.last_updated_by
2967 , l_trans_rec.who_info.last_update_login
2968 , l_trans_rec.mass_transaction_id
2969 , l_trans_rec.calling_interface
2970 , l_trans_rec.mass_reference_id
2971 , l_trans_rec.amortization_start_date;
2972 CLOSE c_get_thx;
2973
2974 if (l_trans_rec.transaction_date_entered is null) then
2975 if (p_log_level_rec.statement_level) then
2976 fa_debug_pkg.add(l_calling_fn, 'Looking for retirement transaction', 'FAILED',p_log_level_rec);
2977
2978 end if;
2979
2980 raise calc_err;
2981 end if;
2982 --tk_util.DumpTrxRec(l_trans_rec, 'trx');
2983
2984 l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
2985 l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
2986
2987 l_asset_hdr_rec.asset_id := p_bk.group_asset_id;
2988 l_asset_hdr_rec.book_type_code := p_ret.book;
2989 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
2990
2991 if not FA_UTIL_PVT.get_asset_type_rec (
2992 p_asset_hdr_rec => l_asset_hdr_rec,
2993 px_asset_type_rec => l_asset_type_rec,
2994 p_log_level_rec => p_log_level_rec) then
2995 raise calc_err;
2996 end if;
2997
2998 if not FA_UTIL_PVT.get_asset_desc_rec (
2999 p_asset_hdr_rec => l_asset_hdr_rec,
3000 px_asset_desc_rec => l_asset_desc_rec,
3001 p_log_level_rec => p_log_level_rec) then
3002 raise calc_err;
3003 end if;
3004
3005 if not FA_UTIL_PVT.get_asset_cat_rec (
3006 p_asset_hdr_rec => l_asset_hdr_rec,
3007 px_asset_cat_rec => l_asset_cat_rec,
3008 p_date_effective => null,
3009 p_log_level_rec => p_log_level_rec) then
3010 raise calc_err;
3011 end if;
3012
3013 if not fa_util_pvt.get_asset_fin_rec (
3014 p_asset_hdr_rec => l_asset_hdr_rec,
3015 px_asset_fin_rec => l_asset_fin_rec_old,
3016 p_mrc_sob_type_code => p_mrc_sob_type_code,
3017 p_log_level_rec => p_log_level_rec) then
3018 raise calc_err;
3019 end if;
3020
3021 if not FA_UTIL_PVT.get_period_rec(
3022 p_book => l_asset_hdr_rec.book_type_code,
3023 x_period_rec => l_period_rec,
3024 p_log_level_rec => p_log_level_rec) then
3025 raise calc_err;
3026 end if;
3027
3028 --tk_util.DumpFinRec(l_asset_fin_rec_old, 'OLD');
3029
3030 if (l_asset_type_rec.asset_type = 'CIP') then
3031 l_asset_fin_rec_adj.cip_cost := -1 * p_ret.cost_retired;
3032 else
3033 l_asset_fin_rec_adj.cost := -1 * p_ret.cost_retired;
3034 end if;
3035
3036 l_asset_fin_rec_adj.unrevalued_cost := -1 * p_ret.cost_retired;
3037 l_asset_fin_rec_adj.ytd_proceeds := p_ret.proceeds_of_sale;
3038 l_asset_fin_rec_adj.ltd_proceeds := p_ret.proceeds_of_sale;
3039 l_asset_fin_rec_adj.ltd_cost_of_removal := p_ret.cost_of_removal;
3040
3041 -- This seems not necessary since the amount is in old_deprn_rec
3042 l_asset_deprn_rec_adj.deprn_reserve := -1 * p_ret.reserve_retired;
3043
3044 if (p_mrc_sob_type_code = 'R') then
3045 OPEN c_get_mc_ret_info;
3046 FETCH c_get_mc_ret_info INTO l_asset_fin_rec_adj.eofy_reserve;
3047 CLOSE c_get_mc_ret_info;
3048 else
3049 OPEN c_get_ret_info;
3050 FETCH c_get_ret_info INTO l_asset_fin_rec_adj.eofy_reserve;
3051 CLOSE c_get_ret_info;
3052 end if;
3053
3054 l_asset_hdr_rec_m.asset_id := p_ret.asset_id;
3055 l_asset_hdr_rec_m.book_type_code := p_ret.book;
3056 l_asset_hdr_rec_m.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
3057
3058 -- Get new member's fin_rec
3059 if not fa_util_pvt.get_asset_fin_rec (
3060 p_asset_hdr_rec => l_asset_hdr_rec_m,
3061 px_asset_fin_rec => l_asset_fin_rec_new_m,
3062 p_mrc_sob_type_code => p_mrc_sob_type_code,
3063 p_log_level_rec => p_log_level_rec) then
3064 raise calc_err;
3065 end if;
3066
3067 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_new_m.salvage_value, 0) -
3068 nvl(p_bk.salvage_value, 0);
3069
3070 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
3071 nvl(l_asset_fin_rec_new_m.allowed_deprn_limit_amount, 0) -
3072 nvl(p_bk.allowed_deprn_limit_amount, 0);
3073
3074 --
3075 -- Process Recapture Excess Reserve
3076 --
3077 if (nvl(p_bk.recapture_reserve_flag, 'N') = 'Y') then
3078
3079 l_new_reserve := p_asset_deprn_rec_old.deprn_reserve -
3080 p_ret.reserve_retired;
3081
3082 l_new_rec_cost := l_asset_fin_rec_old.recoverable_cost -
3083 (p_ret.cost_retired -
3084 l_asset_fin_rec_adj.salvage_value);
3085
3086 if (l_new_rec_cost < l_new_reserve) then
3087 l_adj.adjustment_amount := l_new_reserve - l_new_rec_cost;
3088
3089 if (p_log_level_rec.statement_level) then
3090 fa_debug_pkg.add(l_calling_fn, 'Recapture Excess Reserve',
3091 l_adj.adjustment_amount,p_log_level_rec);
3092 end if;
3093
3094 if l_asset_type_rec.asset_type = 'CIP' then
3095 l_adj.source_type_code := 'CIP RETIREMENT';
3096 else
3097 l_adj.source_type_code := 'RETIREMENT';
3098 end if;
3099
3100 l_adj.asset_id := l_asset_hdr_rec.asset_id;
3101 l_adj.transaction_header_id := l_trans_rec.transaction_header_id;
3102 l_adj.book_type_code := l_asset_hdr_rec.book_type_code;
3103 l_adj.period_counter_created := l_period_rec.period_counter;
3104 l_adj.period_counter_adjusted := l_period_rec.period_counter;
3105 l_adj.current_units := l_asset_desc_rec.current_units;
3106 l_adj.selection_retid := 0;
3107 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
3108 l_adj.leveling_flag := TRUE;
3109 l_adj.flush_adj_flag := FALSE;
3110 l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
3111 l_adj.gen_ccid_flag := TRUE;
3112 l_adj.adjustment_type := 'RESERVE';
3113 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
3114 l_adj.debit_credit_flag := 'DR';
3115
3116 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
3117 l_asset_cat_rec.category_id,
3118 p_log_level_rec) then
3119 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3120 p_log_level_rec => p_log_level_rec);
3121 raise calc_err;
3122 end if;
3123
3124 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
3125
3126 if not FA_INS_ADJUST_PKG.faxinaj
3127 (l_adj,
3128 l_trans_rec.who_info.last_update_date,
3129 l_trans_rec.who_info.last_updated_by,
3130 l_trans_rec.who_info.last_update_login,
3131 p_log_level_rec) then
3132 raise calc_err;
3133 end if;
3134
3135 l_adj.adjustment_type := 'NBV RETIRED';
3136 l_adj.adjustment_amount := l_new_reserve - l_new_rec_cost;
3137 l_adj.flush_adj_flag := TRUE;
3138
3139 l_adj.debit_credit_flag := 'CR';
3140 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
3141 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
3142
3143 if not FA_INS_ADJUST_PKG.faxinaj
3144 (l_adj,
3145 l_trans_rec.who_info.last_update_date,
3146 l_trans_rec.who_info.last_updated_by,
3147 l_trans_rec.who_info.last_update_login,
3148 p_log_level_rec) then
3149 raise calc_err;
3150 end if;
3151
3152 l_recapture_amount := l_new_reserve - l_new_rec_cost;
3153
3154 else
3155 l_recapture_amount := 0;
3156
3157 end if; -- (l_new_rec_cost < l_new_reserve)
3158
3159 else
3160
3161 l_recapture_amount := 0;
3162
3163 end if; -- (nvl(p_bk.recapture_reserve_flag, 'N') = 'Y') and
3164
3165 FA_RETIREMENTS_PKG.Update_Row(
3166 X_Rowid => l_rowid,
3167 X_Retirement_Id => p_ret.retirement_id,
3168 X_Recapture_Amount => l_recapture_amount,
3169 X_mrc_sob_type_code => p_mrc_sob_type_code,
3170 X_Calling_Fn => l_calling_fn,
3171 p_log_level_rec => p_log_level_rec);
3172
3173
3174 if (p_log_level_rec.statement_level) then
3175 fa_debug_pkg.add(l_calling_fn, 'Process Non Member Rollup Group',
3176 l_asset_hdr_rec.asset_id,p_log_level_rec);
3177 end if;
3178
3179
3180 if (p_mrc_sob_type_code = 'R') then
3181 UPDATE FA_ADJUSTMENTS_MRC_V
3182 SET TRANSACTION_HEADER_ID = l_trans_rec.transaction_header_id
3183 WHERE TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
3184 AND ASSET_ID = l_asset_hdr_rec.asset_id
3185 AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code;
3186 else
3187 UPDATE FA_ADJUSTMENTS
3188 SET TRANSACTION_HEADER_ID = l_trans_rec.transaction_header_id
3189 WHERE TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
3190 AND ASSET_ID = l_asset_hdr_rec.asset_id
3191 AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code;
3192 end if;
3193
3194 if (p_log_level_rec.statement_level) then
3195 fa_debug_pkg.add(l_calling_fn, 'Call FA_ADJUSTMENT_PVT.do_adjustment3',
3196 'Begin',p_log_level_rec);
3197 end if;
3198
3199 if not FA_ADJUSTMENT_PVT.do_adjustment
3200 (px_trans_rec => l_trans_rec,
3201 px_asset_hdr_rec => l_asset_hdr_rec,
3202 p_asset_desc_rec => l_asset_desc_rec,
3203 p_asset_type_rec => l_asset_type_rec,
3204 p_asset_cat_rec => l_asset_cat_rec,
3205 p_asset_fin_rec_old => l_asset_fin_rec_old,
3206 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
3207 x_asset_fin_rec_new => l_asset_fin_rec_new,
3208 p_inv_trans_rec => l_inv_trans_rec,
3209 p_asset_deprn_rec_old => p_asset_deprn_rec_old,
3210 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
3211 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
3212 p_period_rec => l_period_rec,
3213 p_mrc_sob_type_code => p_mrc_sob_type_code,
3214 p_group_reclass_options_rec => l_group_reclass_options_rec,
3215 p_calling_fn => l_calling_fn,
3216 p_log_level_rec => p_log_level_rec)then
3217
3218 if (p_log_level_rec.statement_level) then
3219 fa_debug_pkg.add(l_calling_fn, 'Call FA_ADJUSTMENT_PVT.do_adjustment',
3220 'Failed',p_log_level_rec);
3221 end if;
3222
3223 raise calc_err;
3224
3225 end if;
3226
3227 if (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'Y') then
3228 --
3229 -- This is Sum up member depreciation to group asset
3230 --
3231 if (p_log_level_rec.statement_level) then
3232 fa_debug_pkg.add(l_calling_fn, 'Process Member Rollup Group',
3233 l_asset_hdr_rec.asset_id,p_log_level_rec);
3234 end if;
3235 end if; -- (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'N')
3236
3237 if (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') <> 'NO' ) and
3238 (nvl(l_asset_fin_rec_new.cost, 0) = 0) then
3239
3240 OPEN c_member_exists;
3241 FETCH c_member_exists INTO l_member_exists;
3242 CLOSE c_member_exists;
3243
3244 if (p_log_level_rec.statement_level) then
3245 fa_debug_pkg.add(l_calling_fn, 'Is there a member asset',
3246 l_member_exists,p_log_level_rec);
3247 end if;
3248
3249 if (nvl(l_member_exists, 'N') <> 'Y') then
3250
3251 if (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') = 'END_OF_YEAR') then
3252
3253 FA_BOOKS_PKG.Update_Row(
3254 X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
3255 X_Asset_Id => l_asset_hdr_rec.asset_id,
3256 X_terminal_gain_loss_flag => 'Y',
3257 X_mrc_sob_type_code => p_mrc_sob_type_code,
3258 X_Calling_Fn => l_calling_fn,
3259 p_log_level_rec => p_log_level_rec);
3260
3261 else
3262
3263 if (p_log_level_rec.statement_level) then
3264 fa_debug_pkg.add(l_calling_fn, 'Call',
3265 'fa_query_balances_pkg.query_balances',p_log_level_rec);
3266 end if;
3267
3268 fa_query_balances_pkg.query_balances(
3269 X_asset_id => l_asset_hdr_rec.asset_id,
3270 X_book => l_asset_hdr_rec.book_type_code,
3271 X_period_ctr => 0,
3272 X_dist_id => 0,
3273 X_run_mode => 'STANDARD',
3274 X_cost => l_temp_num,
3275 X_deprn_rsv => l_deprn_reserve,
3276 X_reval_rsv => l_temp_num,
3277 X_ytd_deprn => l_temp_num,
3278 X_ytd_reval_exp => l_temp_num,
3279 X_reval_deprn_exp => l_temp_num,
3280 X_deprn_exp => l_temp_num,
3281 X_reval_amo => l_temp_num,
3282 X_prod => l_temp_num,
3283 X_ytd_prod => l_temp_num,
3284 X_ltd_prod => l_temp_num,
3285 X_adj_cost => l_temp_num,
3286 X_reval_amo_basis => l_temp_num,
3287 X_bonus_rate => l_temp_num,
3288 X_deprn_source_code => l_temp_char,
3289 X_adjusted_flag => l_temp_bool,
3290 X_transaction_header_id => -1,
3291 X_bonus_deprn_rsv => l_temp_num,
3292 X_bonus_ytd_deprn => l_temp_num,
3293 X_bonus_deprn_amount => l_temp_num,
3294 p_log_level_rec => p_log_level_rec);
3295
3296 if (p_log_level_rec.statement_level) then
3297 fa_debug_pkg.add(l_calling_fn, 'l_deprn_reserve',
3298 l_deprn_reserve,p_log_level_rec);
3299 end if;
3300
3301 -- If remaining resrve is not 0, insert NBV RETIRED GAIN/LOSS,
3302 -- and RESERVE to fa_adjustments table.
3303 -- update terminal gain loss amount in fa_books
3304 if (nvl(l_deprn_reserve, 0) <> 0) then
3305
3306 if (p_log_level_rec.statement_level) then
3307 fa_debug_pkg.add(l_calling_fn, 'Create Terminal Gain/Loss entries',
3308 'Begin',p_log_level_rec);
3309 end if;
3310
3311 l_adj.asset_id := l_asset_hdr_rec.asset_id;
3312 l_adj.transaction_header_id := l_trans_rec.transaction_header_id;
3313 l_adj.book_type_code := l_asset_hdr_rec.book_type_code;
3314 l_adj.period_counter_created := l_period_rec.period_counter;
3315 l_adj.period_counter_adjusted := l_period_rec.period_counter;
3316 l_adj.current_units := l_asset_desc_rec.current_units;
3317 l_adj.selection_retid := 0;
3318 l_adj.leveling_flag := TRUE;
3319 l_adj.flush_adj_flag := FALSE;
3320 l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
3321 l_adj.gen_ccid_flag := TRUE;
3322 l_adj.adjustment_type := 'RESERVE';
3323 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
3324 l_adj.adjustment_amount := l_deprn_reserve;
3325
3326 if (l_deprn_reserve > 0) then
3327 l_adj.debit_credit_flag := 'DR';
3328 else
3329 l_adj.debit_credit_flag := 'CR';
3330 end if;
3331
3332 if not fa_cache_pkg.fazccb(l_asset_hdr_rec.book_type_code,
3333 l_asset_cat_rec.category_id,
3334 p_log_level_rec) then
3335 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3336 p_log_level_rec => p_log_level_rec);
3337 raise calc_err;
3338 end if;
3339
3340 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
3341
3342 if not FA_INS_ADJUST_PKG.faxinaj
3343 (l_adj,
3344 l_trans_rec.who_info.last_update_date,
3345 l_trans_rec.who_info.last_updated_by,
3346 l_trans_rec.who_info.last_update_login,
3347 p_log_level_rec) then
3348 raise calc_err;
3349 end if;
3350
3351 l_adj.adjustment_type := 'NBV RETIRED';
3352 l_adj.adjustment_amount := l_deprn_reserve;
3353 l_adj.flush_adj_flag := TRUE;
3354
3355 if (l_deprn_reserve > 0) then
3356 l_adj.debit_credit_flag := 'CR';
3357 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
3358 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
3359 else
3360 l_adj.debit_credit_flag := 'DR';
3361 l_adj.debit_credit_flag := 'CR';
3362 l_adj.account_type := 'NBV_RETIRED_LOSS_ACCT';
3363 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_loss_acct;
3364 end if; -- (nvl(l_deprn_reserve, 0) <> 0)
3365
3366 if not FA_INS_ADJUST_PKG.faxinaj
3367 (l_adj,
3368 l_trans_rec.who_info.last_update_date,
3369 l_trans_rec.who_info.last_updated_by,
3370 l_trans_rec.who_info.last_update_login,
3371 p_log_level_rec) then
3372 raise calc_err;
3373 end if;
3374
3375 if (p_log_level_rec.statement_level) then
3376 fa_debug_pkg.add(l_calling_fn, 'Create Terminal Gain/Loss entries',
3377 'End',p_log_level_rec);
3378 end if;
3379
3380 FA_BOOKS_PKG.Update_Row(
3381 X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
3382 X_Asset_Id => l_asset_hdr_rec.asset_id,
3383 X_terminal_gain_loss_amount => l_deprn_reserve,
3384 X_mrc_sob_type_code => p_mrc_sob_type_code,
3385 X_Calling_Fn => l_calling_fn,
3386 p_log_level_rec => p_log_level_rec);
3387
3388 end if;
3389
3390 end if; -- (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') = 'END_OF_YEAR')
3391
3392 end if; -- (nvl(l_member_exists, 'N') <> 'Y')
3393
3394 end if; -- (nvl(l_asset_fin_rec_old.terminal_gain_loss, 'NO') = 'YES') and
3395
3396 if (p_log_level_rec.statement_level) then
3397 fa_debug_pkg.add(l_calling_fn, 'End', 'Success',p_log_level_rec);
3398 end if;
3399
3400 return TRUE;
3401
3402 EXCEPTION
3403 WHEN calc_err THEN
3404 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3405 p_log_level_rec => p_log_level_rec);
3406
3407 return false;
3408
3409 WHEN OTHERS THEN
3410 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
3411 ,p_log_level_rec => p_log_level_rec);
3412
3413 return false;
3414
3415
3416 END Do_Retirement_in_CGL;
3417
3418 FUNCTION Do_Reinstatement_in_CGL(
3419 p_ret IN fa_ret_types.ret_struct,
3420 p_bk IN fa_ret_types.book_struct,
3421 p_dpr IN fa_STD_TYPES.dpr_struct,
3422 p_mrc_sob_type_code IN VARCHAR2,
3423 p_calling_fn IN VARCHAR2,
3424 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
3425 return boolean IS
3426
3427 CURSOR c_get_thid is
3428 select fa_transaction_headers_s.nextval
3429 from dual;
3430
3431 CURSOR c_get_thx is
3432 select trx.transaction_header_id
3433 , trx.transaction_date_entered
3434 , trx.last_update_date
3435 , trx.last_updated_by
3436 , trx.last_update_login
3437 , trx.mass_transaction_id
3438 , trx.calling_interface
3439 , trx.mass_reference_id
3440 , nvl(trx.amortization_start_date, trx.transaction_date_entered)
3441 from fa_retirements ret
3442 , fa_transaction_headers trx
3443 where ret.retirement_id = p_ret.retirement_id
3444 and ret.transaction_header_id_out = trx.transaction_header_id;
3445
3446 l_calling_fn VARCHAR2(100) := 'fa_retirement_pvt.Do_Reinstatement_in_CGL';
3447
3448 l_trans_rec FA_API_TYPES.trans_rec_type;
3449 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
3450 l_asset_hdr_rec_m FA_API_TYPES.asset_hdr_rec_type;
3451 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
3452 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
3453 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
3454 l_asset_fin_rec_new_m FA_API_TYPES.asset_fin_rec_type;
3455 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
3456 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
3457 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
3458 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
3459 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
3460 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
3461 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
3462 l_period_rec FA_API_TYPES.period_rec_type;
3463 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
3464
3465 calc_err EXCEPTION;
3466
3467 BEGIN
3468 if (p_log_level_rec.statement_level) then
3469 fa_debug_pkg.add(l_calling_fn, 'Begin',
3470 p_ret.asset_id||':'||p_bk.group_asset_id,p_log_level_rec);
3471 end if;
3472
3473 --
3474 -- Prepare to call FA_ADJUSTMENT_PVT.do_adjustment to process group
3475 -- asset after member asset retirement.
3476 --
3477 if (p_mrc_sob_type_code <> 'R') then
3478 OPEN c_get_thid;
3479 FETCH c_get_thid INTO l_trans_rec.transaction_header_id;
3480 CLOSE c_get_thid;
3481 end if;
3482
3483 l_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
3484 l_trans_rec.transaction_subtype := 'AMORTIZED';
3485 l_trans_rec.transaction_key := 'MS';
3486
3487 OPEN c_get_thx;
3488 FETCH c_get_thx INTO l_trans_rec.member_transaction_header_id
3489 , l_trans_rec.transaction_date_entered
3490 , l_trans_rec.who_info.last_update_date
3491 , l_trans_rec.who_info.last_updated_by
3492 , l_trans_rec.who_info.last_update_login
3493 , l_trans_rec.mass_transaction_id
3494 , l_trans_rec.calling_interface
3495 , l_trans_rec.mass_reference_id
3496 , l_trans_rec.amortization_start_date;
3497 CLOSE c_get_thx;
3498
3499 if (l_trans_rec.transaction_date_entered is null) then
3500 if (p_log_level_rec.statement_level) then
3501 fa_debug_pkg.add(l_calling_fn, 'Looking for retirement transaction', 'FAILED',p_log_level_rec);
3502
3503 end if;
3504
3505 raise calc_err;
3506 end if;
3507
3508 l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
3509 l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
3510
3511 l_asset_hdr_rec.asset_id := p_bk.group_asset_id;
3512 l_asset_hdr_rec.book_type_code := p_ret.book;
3513 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
3514
3515 if not FA_UTIL_PVT.get_asset_type_rec (
3516 p_asset_hdr_rec => l_asset_hdr_rec,
3517 px_asset_type_rec => l_asset_type_rec,
3518 p_log_level_rec => p_log_level_rec) then
3519 raise calc_err;
3520 end if;
3521
3522 if not FA_UTIL_PVT.get_asset_desc_rec (
3523 p_asset_hdr_rec => l_asset_hdr_rec,
3524 px_asset_desc_rec => l_asset_desc_rec,
3525 p_log_level_rec => p_log_level_rec) then
3526 raise calc_err;
3527 end if;
3528
3529 if not FA_UTIL_PVT.get_asset_cat_rec (
3530 p_asset_hdr_rec => l_asset_hdr_rec,
3531 px_asset_cat_rec => l_asset_cat_rec,
3532 p_date_effective => null,
3533 p_log_level_rec => p_log_level_rec) then
3534 raise calc_err;
3535 end if;
3536
3537 if not fa_util_pvt.get_asset_fin_rec (
3538 p_asset_hdr_rec => l_asset_hdr_rec,
3539 px_asset_fin_rec => l_asset_fin_rec_old,
3540 p_mrc_sob_type_code => p_mrc_sob_type_code,
3541 p_log_level_rec => p_log_level_rec) then
3542 raise calc_err;
3543 end if;
3544
3545 if not FA_UTIL_PVT.get_period_rec(
3546 p_book => l_asset_hdr_rec.book_type_code,
3547 x_period_rec => l_period_rec,
3548 p_log_level_rec => p_log_level_rec) then
3549 raise calc_err;
3550 end if;
3551
3552 --tk_util.DumpFinRec(l_asset_fin_rec_old, 'OLD');
3553
3554 if not fa_util_pvt.get_asset_deprn_rec (
3555 p_asset_hdr_rec => l_asset_hdr_rec,
3556 px_asset_deprn_rec => l_asset_deprn_rec_old,
3557 p_mrc_sob_type_code => p_mrc_sob_type_code,
3558 p_log_level_rec => p_log_level_rec) then
3559 raise calc_err;
3560 end if;
3561
3562 if (l_asset_type_rec.asset_type = 'CIP') then
3563 l_asset_fin_rec_adj.cip_cost := p_ret.cost_retired;
3564 else
3565 l_asset_fin_rec_adj.cost := p_ret.cost_retired;
3566 end if;
3567
3568 l_asset_fin_rec_adj.unrevalued_cost := p_ret.cost_retired;
3569 l_asset_fin_rec_adj.ytd_proceeds := p_ret.proceeds_of_sale;
3570 l_asset_fin_rec_adj.ltd_proceeds := p_ret.proceeds_of_sale;
3571 l_asset_fin_rec_adj.ltd_cost_of_removal := p_ret.cost_of_removal;
3572
3573 -- l_asset_deprn_rec_adj.deprn_reserve := p_ret.reserve_retired;
3574
3575 l_asset_hdr_rec_m.asset_id := p_ret.asset_id;
3576 l_asset_hdr_rec_m.book_type_code := p_ret.book;
3577 l_asset_hdr_rec_m.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
3578
3579 -- Get new member's fin_rec
3580 if not fa_util_pvt.get_asset_fin_rec (
3581 p_asset_hdr_rec => l_asset_hdr_rec_m,
3582 px_asset_fin_rec => l_asset_fin_rec_new_m,
3583 p_mrc_sob_type_code => p_mrc_sob_type_code,
3584 p_log_level_rec => p_log_level_rec) then
3585 raise calc_err;
3586 end if;
3587
3588 l_asset_fin_rec_adj.salvage_value := nvl(l_asset_fin_rec_new_m.salvage_value, 0) -
3589 nvl(p_bk.salvage_value, 0);
3590
3591 l_asset_fin_rec_adj.allowed_deprn_limit_amount :=
3592 nvl(l_asset_fin_rec_new_m.allowed_deprn_limit_amount, 0) -
3593 nvl(p_bk.allowed_deprn_limit_amount, 0);
3594
3595 UPDATE FA_ADJUSTMENTS
3596 SET TRANSACTION_HEADER_ID = l_trans_rec.transaction_header_id
3597 WHERE TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
3598 AND ASSET_ID = l_asset_hdr_rec.asset_id
3599 AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code;
3600
3601 if (p_log_level_rec.statement_level) then
3602 fa_debug_pkg.add(l_calling_fn, 'Call FA_ADJUSTMENT_PVT.do_adjustment4',
3603 'Begin',p_log_level_rec);
3604
3605 end if;
3606
3607 if not FA_ADJUSTMENT_PVT.do_adjustment
3608 (px_trans_rec => l_trans_rec,
3609 px_asset_hdr_rec => l_asset_hdr_rec,
3610 p_asset_desc_rec => l_asset_desc_rec,
3611 p_asset_type_rec => l_asset_type_rec,
3612 p_asset_cat_rec => l_asset_cat_rec,
3613 p_asset_fin_rec_old => l_asset_fin_rec_old,
3614 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
3615 x_asset_fin_rec_new => l_asset_fin_rec_new,
3616 p_inv_trans_rec => l_inv_trans_rec,
3617 p_asset_deprn_rec_old => l_asset_deprn_rec_old,
3618 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
3619 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
3620 p_period_rec => l_period_rec,
3621 p_mrc_sob_type_code => p_mrc_sob_type_code,
3622 p_group_reclass_options_rec => l_group_reclass_options_rec,
3623 p_calling_fn => l_calling_fn,
3624 p_log_level_rec => p_log_level_rec)then
3625 raise calc_err;
3626 end if;
3627
3628 if (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'Y') then
3629 --
3630 -- This is Sum up member depreciation to group asset
3631 --
3632 if (p_log_level_rec.statement_level) then
3633 fa_debug_pkg.add(l_calling_fn, 'Process Member Rollup Group',
3634 l_asset_hdr_rec.asset_id,p_log_level_rec);
3635 end if;
3636
3637 -- +++++ Remove group expense rows +++++
3638 DELETE FROM FA_ADJUSTMENTS
3639 WHERE ASSET_ID = l_asset_hdr_rec.asset_id
3640 AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code
3641 AND TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
3642 AND SOURCE_TYPE_CODE = 'DEPRECIATION'
3643 AND ADJUSTMENT_TYPE in ('EXPENSE', 'BONUS EXPENSE');
3644
3645 end if; -- (nvl(l_asset_fin_rec_old.member_rollup_flag, 'N') = 'N')
3646
3647 -- +++++ Clear Terminal Gain Loss Amount +++++
3648 FA_BOOKS_PKG.Update_Row(
3649 X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
3650 X_Asset_Id => l_asset_hdr_rec.asset_id,
3651 X_terminal_gain_loss_amount => FND_API.G_MISS_NUM,
3652 X_mrc_sob_type_code => p_mrc_sob_type_code,
3653 X_Calling_Fn => l_calling_fn,
3654 p_log_level_rec => p_log_level_rec);
3655
3656 return true;
3657
3658 EXCEPTION
3659 WHEN calc_err THEN
3660 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3661 p_log_level_rec => p_log_level_rec);
3662
3663 return false;
3664
3665 WHEN OTHERS THEN
3666 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
3667 ,p_log_level_rec => p_log_level_rec);
3668
3669 return false;
3670
3671 END Do_Reinstatement_in_CGL;
3672
3673 FUNCTION Do_Terminal_Gain_Loss_All_Bk (
3674 p_book_type_code VARCHAR2,
3675 p_asset_id NUMBER,
3676 p_period_rec FA_API_TYPES.period_rec_type,
3677 p_mrc_sob_type_code VARCHAR2,
3678 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)RETURN BOOLEAN IS
3679
3680 l_calling_fn VARCHAR2(50) := 'FA_RETIREMENT_PVT.Do_Terminal_Gain_Loss_All_Bk';
3681
3682 cursor c_member_exists IS
3683 select 'Y'
3684 from fa_books
3685 where book_type_code = p_book_type_code
3686 and group_asset_id = p_asset_id
3687 and period_counter_fully_retired is null
3688 and transaction_header_id_out is null;
3689
3690 cursor c_max_ret_thid is
3691 select max(gth.transaction_header_id)
3692 from fa_transaction_headers gth,
3693 fa_transaction_headers mth,
3694 fa_retirements ret,
3695 fa_books bk
3696 where bk.group_asset_id = p_asset_id
3697 and bk.book_type_code = p_book_type_code
3698 and ret.asset_id = bk.asset_id
3699 and ret.book_type_code = p_book_type_code
3700 and mth.asset_id = bk.asset_id
3701 and mth.book_type_code = p_book_type_code
3702 and gth.asset_id = p_asset_id
3703 and gth.book_type_code = p_book_type_code
3704 and mth.transaction_type_code = 'FULL RETIREMENT'
3705 and mth.transaction_header_id = gth.member_transaction_header_id;
3706
3707 cursor c_mc_member_exists IS
3708 select 'Y'
3709 from fa_books_mrc_v
3710 where book_type_code = p_book_type_code
3711 and group_asset_id = p_asset_id
3712 and period_counter_fully_retired is null
3713 and transaction_header_id_out is null;
3714
3715 cursor c_max_mc_ret_thid is
3716 select max(gth.transaction_header_id)
3717 from fa_transaction_headers gth,
3718 fa_transaction_headers mth,
3719 fa_retirements_mrc_v ret,
3720 fa_books_mrc_v bk
3721 where bk.group_asset_id = p_asset_id
3722 and bk.book_type_code = p_book_type_code
3723 and ret.asset_id = bk.asset_id
3724 and ret.book_type_code = p_book_type_code
3725 and mth.asset_id = bk.asset_id
3726 and mth.book_type_code = p_book_type_code
3727 and gth.asset_id = p_asset_id
3728 and gth.book_type_code = p_book_type_code
3729 and mth.transaction_type_code = 'FULL RETIREMENT'
3730 and mth.transaction_header_id = gth.member_transaction_header_id;
3731
3732 -- +++++ Get Current Unit of Group Asset +++++
3733 cursor c_get_unit is
3734 select units,
3735 category_id
3736 from fa_asset_history
3737 where asset_id = p_asset_id
3738 and transaction_header_id_out is null;
3739
3740 l_adj fa_adjust_type_pkg.fa_adj_row_struct;
3741 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
3742
3743 l_member_exists VARCHAR2(1) := 'N';
3744 l_deprn_reserve NUMBER;
3745 l_last_update_date DATE := sysdate;
3746 l_last_updated_by NUMBER(15) := fnd_global.user_id;
3747 l_last_update_login NUMBER(15) := fnd_global.user_id;
3748
3749 l_temp_num NUMBER;
3750 l_temp_char VARCHAR2(30);
3751 l_temp_bool BOOLEAN;
3752
3753 gl_err EXCEPTION;
3754
3755 BEGIN
3756
3757 if (p_mrc_sob_type_code = 'R') then
3758 OPEN c_mc_member_exists;
3759 FETCH c_mc_member_exists INTO l_member_exists;
3760 CLOSE c_mc_member_exists;
3761 else
3762 OPEN c_member_exists;
3763 FETCH c_member_exists INTO l_member_exists;
3764 CLOSE c_member_exists;
3765 end if;
3766
3767 if (nvl(l_member_exists, 'N') = 'N') then
3768
3769 if (p_log_level_rec.statement_level) then
3770 fa_debug_pkg.add(l_calling_fn, 'Call',
3771 'fa_query_balances_pkg.query_balances',p_log_level_rec);
3772 end if;
3773
3774 fa_query_balances_pkg.query_balances(
3775 X_asset_id => p_asset_id,
3776 X_book => p_book_type_code,
3777 X_period_ctr => 0,
3778 X_dist_id => 0,
3779 X_run_mode => 'STANDARD',
3780 X_cost => l_temp_num,
3781 X_deprn_rsv => l_deprn_reserve,
3782 X_reval_rsv => l_temp_num,
3783 X_ytd_deprn => l_temp_num,
3784 X_ytd_reval_exp => l_temp_num,
3785 X_reval_deprn_exp => l_temp_num,
3786 X_deprn_exp => l_temp_num,
3787 X_reval_amo => l_temp_num,
3788 X_prod => l_temp_num,
3789 X_ytd_prod => l_temp_num,
3790 X_ltd_prod => l_temp_num,
3791 X_adj_cost => l_temp_num,
3792 X_reval_amo_basis => l_temp_num,
3793 X_bonus_rate => l_temp_num,
3794 X_deprn_source_code => l_temp_char,
3795 X_adjusted_flag => l_temp_bool,
3796 X_transaction_header_id => -1,
3797 X_bonus_deprn_rsv => l_temp_num,
3798 X_bonus_ytd_deprn => l_temp_num,
3799 X_bonus_deprn_amount => l_temp_num,
3800 p_log_level_rec => p_log_level_rec);
3801
3802 if (p_log_level_rec.statement_level) then
3803 fa_debug_pkg.add(l_calling_fn, 'Coming back from Query Balance',
3804 l_deprn_reserve,p_log_level_rec);
3805 end if;
3806
3807 l_adj.asset_id := p_asset_id;
3808
3809 if (p_mrc_sob_type_code = 'R') then
3810 OPEN c_max_mc_ret_thid;
3811 FETCH c_max_mc_ret_thid INTO l_adj.transaction_header_id;
3812 CLOSE c_max_mc_ret_thid;
3813 else
3814 OPEN c_max_ret_thid;
3815 FETCH c_max_ret_thid INTO l_adj.transaction_header_id;
3816 CLOSE c_max_ret_thid;
3817 end if;
3818
3819 if (p_log_level_rec.statement_level) then
3820 fa_debug_pkg.add(l_calling_fn, 'Transaction_Header_Id for Terminal GL',
3821 l_adj.transaction_header_id,p_log_level_rec);
3822 end if;
3823
3824 l_adj.book_type_code := p_book_type_code;
3825 l_adj.period_counter_created := p_period_rec.period_counter;
3826 l_adj.period_counter_adjusted := p_period_rec.period_counter;
3827
3828 OPEN c_get_unit;
3829 FETCH c_get_unit INTO l_adj.current_units , l_asset_cat_rec.category_id;
3830 CLOSE c_get_unit;
3831
3832 l_adj.selection_retid := 0;
3833 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
3834 l_adj.leveling_flag := TRUE;
3835 l_adj.flush_adj_flag := FALSE;
3836 l_adj.last_update_date := sysdate;
3837 l_adj.gen_ccid_flag := TRUE;
3838 l_adj.adjustment_type := 'RESERVE';
3839 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
3840 l_adj.adjustment_amount := l_deprn_reserve;
3841
3842 if (l_deprn_reserve > 0) then
3843 l_adj.debit_credit_flag := 'DR';
3844 else
3845 l_adj.debit_credit_flag := 'CR';
3846 end if;
3847
3848 if not fa_cache_pkg.fazccb(p_book_type_code,
3849 l_asset_cat_rec.category_id,
3850 p_log_level_rec) then
3851 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3852 p_log_level_rec => p_log_level_rec);
3853 raise gl_err;
3854 end if;
3855
3856 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
3857
3858 if not FA_INS_ADJUST_PKG.faxinaj
3859 (l_adj,
3860 l_last_update_date,
3861 l_last_updated_by,
3862 l_last_update_login,
3863 p_log_level_rec) then
3864 raise gl_err;
3865 end if;
3866
3867 l_adj.adjustment_type := 'NBV RETIRED';
3868 l_adj.adjustment_amount := l_deprn_reserve;
3869 l_adj.flush_adj_flag := TRUE;
3870
3871 if (l_deprn_reserve > 0) then
3872 l_adj.debit_credit_flag := 'CR';
3873 l_adj.account_type := 'NBV_RETIRED_GAIN_ACCT';
3874 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_gain_acct;
3875 else
3876 l_adj.debit_credit_flag := 'CR';
3877 l_adj.account_type := 'NBV_RETIRED_LOSS_ACCT';
3878 l_adj.account := fa_cache_pkg.fazcbc_record.nbv_retired_loss_acct;
3879 end if;
3880
3881 if not FA_INS_ADJUST_PKG.faxinaj
3882 (l_adj,
3883 l_last_update_date,
3884 l_last_updated_by,
3885 l_last_update_login,
3886 p_log_level_rec) then
3887
3888 raise gl_err;
3889 end if;
3890
3891 FA_BOOKS_PKG.Update_Row(
3892 X_Book_Type_Code => p_book_type_code,
3893 X_Asset_Id => p_asset_id,
3894 X_terminal_gain_loss_amount => l_deprn_reserve,
3895 X_terminal_gain_loss_flag => 'N',
3896 X_mrc_sob_type_code => p_mrc_sob_type_code,
3897 X_Calling_Fn => l_calling_fn,
3898 p_log_level_rec => p_log_level_rec);
3899
3900 else
3901 if (p_log_level_rec.statement_level) then
3902 fa_debug_pkg.add(l_calling_fn, 'There is at least non retired member exists',
3903 p_asset_id,p_log_level_rec);
3904 end if;
3905 --
3906 -- Must not calculate terminal gain loss as long as some
3907 -- non-retired member exists
3908 --
3909 FA_BOOKS_PKG.Update_Row(
3910 X_Book_Type_Code => p_book_type_code,
3911 X_Asset_Id => p_asset_id,
3912 X_terminal_gain_loss_flag => FND_API.G_MISS_CHAR,
3913 X_mrc_sob_type_code => p_mrc_sob_type_code,
3914 X_Calling_Fn => l_calling_fn,
3915 p_log_level_rec => p_log_level_rec);
3916
3917 end if; -- (nvl(l_member_exists, 'N') = 'N' )
3918
3919 return true;
3920
3921 EXCEPTION
3922 WHEN gl_err THEN
3923 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3924 p_log_level_rec => p_log_level_rec);
3925
3926 return false;
3927
3928 WHEN OTHERS THEN
3929 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
3930 ,p_log_level_rec => p_log_level_rec);
3931
3932 return false;
3933
3934 END Do_Terminal_Gain_Loss_All_Bk;
3935
3936 FUNCTION Do_Terminal_Gain_Loss (
3937 p_book_type_code VARCHAR2,
3938 p_total_requests NUMBER,
3939 p_request_number NUMBER,
3940 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)RETURN BOOLEAN IS
3941
3942 l_calling_fn VARCHAR2(50) := 'FA_RETIREMENT_PVT.Do_Terminal_Gain_Loss';
3943
3944 l_period_rec FA_API_TYPES.period_rec_type;
3945
3946 cursor c_get_groups is
3947 select bk.asset_id asset_id
3948 from fa_books bk,
3949 fa_additions ad
3950 where bk.book_type_code = p_book_type_code
3951 and bk.transaction_header_id_out is null
3952 and bk.asset_id = ad.asset_id
3953 and ad.asset_type = 'GROUP'
3954 and bk.terminal_gain_loss = 'YES'
3955 and bk.terminal_gain_loss_flag = 'Y'
3956 and MOD(bk.asset_id, p_total_requests) = (p_request_number - 1);
3957
3958 cursor c_get_eofy_groups is
3959 select bk.asset_id asset_id
3960 from fa_books bk,
3961 fa_additions ad
3962 where bk.book_type_code = p_book_type_code
3963 and bk.transaction_header_id_out is null
3964 and bk.asset_id = ad.asset_id
3965 and ad.asset_type = 'GROUP'
3966 and bk.terminal_gain_loss_flag = 'Y'
3967 and MOD(bk.asset_id, p_total_requests) = (p_request_number - 1);
3968
3969 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
3970
3971 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3972 t_asset_id tab_num15_type;
3973
3974 gl_err EXCEPTION;
3975
3976 BEGIN
3977
3978 if (p_log_level_rec.statement_level) then
3979 fa_debug_pkg.add(l_calling_fn, 'Begin', p_book_type_code,p_log_level_rec);
3980 end if;
3981
3982 --+++++ Get Current Period Info +++++
3983 if not FA_UTIL_PVT.get_period_rec(
3984 p_book => p_book_type_code,
3985 x_period_rec => l_period_rec,
3986 p_log_level_rec => p_log_level_rec) then
3987 raise gl_err;
3988 end if;
3989
3990 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar,
3991 p_log_level_rec) then
3992 raise gl_err;
3993 end if;
3994
3995 -- Process Terminal Gain Loss only if this is last period of
3996 -- the fiscal year.
3997
3998 --+++++ Check to see if this is ast period of the fiscal year.
3999 if (fa_cache_pkg.fazcct_record.number_per_fiscal_year =
4000 l_period_rec.period_num) then
4001
4002 OPEN c_get_eofy_groups;
4003 else
4004 OPEN c_get_groups;
4005 end if;
4006
4007 LOOP -- loop for bulk fetch with limit
4008
4009 if (fa_cache_pkg.fazcct_record.number_per_fiscal_year =
4010 l_period_rec.period_num) then
4011
4012 FETCH c_get_eofy_groups BULK COLLECT INTO t_asset_id;
4013 else
4014 FETCH c_get_groups BULK COLLECT INTO t_asset_id;
4015 end if;
4016
4017 if (p_log_level_rec.statement_level) then
4018 fa_debug_pkg.add(l_calling_fn, 'Number of rows fetched',
4019 to_char(t_asset_id.count),p_log_level_rec);
4020 end if;
4021
4022 EXIT WHEN t_asset_id.count = 0;
4023
4024 for i IN 1..t_asset_id.count loop
4025
4026 if not Do_Terminal_Gain_Loss_All_Bk (
4027 p_book_type_code => p_book_type_code,
4028 p_asset_id => t_asset_id(i),
4029 p_period_rec => l_period_rec,
4030 p_mrc_sob_type_code => 'P',
4031 p_log_level_rec => p_log_level_rec) then
4032 raise gl_err;
4033 end if;
4034
4035 if fa_cache_pkg.fazcbc_record.mc_source_flag = 'Y' then
4036
4037 -- call the sob cache to get the table of sob_ids
4038 if not FA_CACHE_PKG.fazcrsob
4039 (x_book_type_code => p_book_type_code,
4040 x_sob_tbl => l_sob_tbl,
4041 p_log_level_rec => p_log_level_rec) then
4042 raise gl_err;
4043 end if;
4044
4045 for l_sob_index in 1..l_sob_tbl.count loop
4046
4047 if p_log_level_rec.statement_level then
4048 fa_debug_pkg.add (l_calling_fn, 'Reporting book loop: Set_of_books_id',
4049 l_sob_tbl(l_sob_index),p_log_level_rec);
4050 end if;
4051
4052 -- set the sob_id and currency context for reporting book(sob)
4053 fnd_profile.put('GL_SET_OF_BKS_ID', l_sob_tbl(l_sob_index));
4054 fnd_client_info.set_currency_context(to_char(l_sob_tbl(l_sob_index)));
4055
4056 if not fa_cache_pkg.fazcbcs(x_book => p_book_type_code,
4057 p_log_level_rec => p_log_level_rec) then
4058 raise gl_err;
4059 end if;
4060
4061 if not Do_Terminal_Gain_Loss_All_Bk (
4062 p_book_type_code => p_book_type_code,
4063 p_asset_id => t_asset_id(i),
4064 p_period_rec => l_period_rec,
4065 p_mrc_sob_type_code => 'P',
4066 p_log_level_rec => p_log_level_rec) then
4067 raise gl_err;
4068 end if;
4069
4070 end loop;
4071 end if; -- fa_cache_pkg.fazcbc_record.mc_source_flag = 'Y'
4072
4073 end loop; -- for i IN 1..t_asset_id.count
4074
4075 end loop; -- loop for bulk fetch with limit
4076
4077 if (fa_cache_pkg.fazcct_record.number_per_fiscal_year =
4078 l_period_rec.period_num) then
4079
4080 CLOSE c_get_eofy_groups;
4081 else
4082 CLOSE c_get_groups;
4083 end if;
4084
4085 if (p_log_level_rec.statement_level) then
4086 fa_debug_pkg.add(l_calling_fn, 'End', p_book_type_code,p_log_level_rec);
4087 end if;
4088
4089 return true;
4090
4091 EXCEPTION
4092 WHEN gl_err THEN
4093 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
4094 p_log_level_rec => p_log_level_rec);
4095
4096 return false;
4097
4098 WHEN OTHERS THEN
4099 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
4100 ,p_log_level_rec => p_log_level_rec);
4101
4102 return false;
4103
4104 END Do_Terminal_Gain_Loss;
4105
4106 FUNCTION Check_Terminal_Gain_Loss(
4107 p_trans_rec IN FA_API_TYPES.trans_rec_type,
4108 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
4109 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
4110 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
4111 p_period_rec IN FA_API_TYPES.period_rec_type,
4112 p_mrc_sob_type_code IN VARCHAR2,
4113 p_calling_fn IN VARCHAR2,
4114 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
4115
4116 l_calling_fn VARCHAR2(50) := 'FA_RETIREMENT_PVT.Check_Terminal_Gain_Loss';
4117
4118 --
4119 -- This cursor tries to find not full retired member of given group asset
4120 -- if there is any.
4121 --
4122 CURSOR c_find_member IS
4123 select asset_id
4124 from fa_books
4125 where group_asset_id = p_asset_hdr_rec.asset_id
4126 and book_type_code = p_asset_hdr_rec.book_type_code
4127 and period_counter_fully_retired is null
4128 and transaction_header_id_out is null;
4129
4130 CURSOR c_find_mc_member IS
4131 select asset_id
4132 from fa_books_mrc_v
4133 where group_asset_id = p_asset_hdr_rec.asset_id
4134 and book_type_code = p_asset_hdr_rec.book_type_code
4135 and period_counter_fully_retired is null
4136 and transaction_header_id_out is null;
4137
4138
4139 l_asset_id NUMBER;
4140 l_is_tgl_due BOOLEAN := FALSE;
4141
4142 -- query balance variables
4143 l_deprn_reserve NUMBER;
4144 l_temp_num NUMBER;
4145 l_temp_char VARCHAR2(30);
4146 l_temp_bool BOOLEAN;
4147
4148 tgl_err EXCEPTION;
4149 BEGIN
4150 if (p_log_level_rec.statement_level) then
4151 fa_debug_pkg.add(l_calling_fn, 'Begin', to_char(p_asset_hdr_rec.asset_id)||':'||
4152 p_asset_hdr_rec.book_type_code,p_log_level_rec);
4153 end if;
4154
4155 --
4156 -- Check to see the group asset is eligible for terminal gain loss
4157 --
4158 if (p_asset_fin_rec.cost = 0) and
4159 (p_asset_fin_rec.cip_cost = 0) and
4160 (p_asset_fin_rec.terminal_gain_loss in ('YES', 'END_OF_YEAR')) then
4161
4162 if (p_mrc_sob_type_code = 'R') then
4163
4164 OPEN c_find_mc_member;
4165 FETCH c_find_mc_member INTO l_asset_id;
4166 CLOSE c_find_mc_member;
4167
4168 else
4169
4170 OPEN c_find_member;
4171 FETCH c_find_member INTO l_asset_id;
4172 CLOSE c_find_member;
4173
4174 end if;
4175
4176 if (l_asset_id is not null) then
4177
4178 if (p_log_level_rec.statement_level) then
4179 fa_debug_pkg.add(l_calling_fn, 'Member exists', to_char(l_asset_id),p_log_level_rec);
4180 fa_debug_pkg.add(l_calling_fn, 'End', 'No Terminal Gain Loss Calculated',p_log_level_rec);
4181 end if;
4182
4183 return TRUE;
4184
4185 end if;
4186
4187 if (p_log_level_rec.statement_level) then
4188 fa_debug_pkg.add(l_calling_fn, 'Continue', 'Checking remaining reserve ',p_log_level_rec);
4189 end if;
4190
4191 if (p_log_level_rec.statement_level) then
4192 fa_debug_pkg.add(l_calling_fn, 'Call',
4193 'fa_query_balances_pkg.query_balances',p_log_level_rec);
4194 end if;
4195
4196 fa_query_balances_pkg.query_balances(
4197 X_asset_id => p_asset_hdr_rec.asset_id,
4198 X_book => p_asset_hdr_rec.book_type_code,
4199 X_period_ctr => 0,
4200 X_dist_id => 0,
4201 X_run_mode => 'STANDARD',
4202 X_cost => l_temp_num,
4203 X_deprn_rsv => l_deprn_reserve,
4204 X_reval_rsv => l_temp_num,
4205 X_ytd_deprn => l_temp_num,
4206 X_ytd_reval_exp => l_temp_num,
4207 X_reval_deprn_exp => l_temp_num,
4208 X_deprn_exp => l_temp_num,
4209 X_reval_amo => l_temp_num,
4210 X_prod => l_temp_num,
4211 X_ytd_prod => l_temp_num,
4212 X_ltd_prod => l_temp_num,
4213 X_adj_cost => l_temp_num,
4214 X_reval_amo_basis => l_temp_num,
4215 X_bonus_rate => l_temp_num,
4216 X_deprn_source_code => l_temp_char,
4217 X_adjusted_flag => l_temp_bool,
4218 X_transaction_header_id => -1,
4219 X_bonus_deprn_rsv => l_temp_num,
4220 X_bonus_ytd_deprn => l_temp_num,
4221 X_bonus_deprn_amount => l_temp_num,
4222 p_log_level_rec => p_log_level_rec);
4223
4224 if (p_log_level_rec.statement_level) then
4225 fa_debug_pkg.add(l_calling_fn, 'l_deprn_reserve',
4226 l_deprn_reserve,p_log_level_rec);
4227 end if;
4228
4229 if (nvl(l_deprn_reserve, 0) = 0) then
4230
4231 if (p_log_level_rec.statement_level) then
4232 fa_debug_pkg.add(l_calling_fn, 'Reserve is 0', 'No Terminal Gain Loss Calculated',p_log_level_rec);
4233 end if;
4234
4235 return TRUE;
4236
4237 end if;
4238
4239 if (p_log_level_rec.statement_level) then
4240 fa_debug_pkg.add(l_calling_fn, 'Continue', 'Eligible for Terminal Gain Loss',p_log_level_rec);
4241 end if;
4242
4243 --
4244 -- Setting TERMINAL_GAIN_LOSS_FLAG so terminal gain loss will be
4245 -- processed during depreciation
4246 --
4247 if (p_mrc_sob_type_code = 'R') then
4248
4249 UPDATE FA_BOOKS_MRC_V
4250 SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
4251 WHERE ASSET_ID = p_asset_hdr_rec.asset_id
4252 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4253 AND TRANSACTION_HEADER_ID_OUT is null;
4254
4255 UPDATE FA_BOOKS_SUMMARY_MRC_V
4256 SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
4257 WHERE ASSET_ID = p_asset_hdr_rec.asset_id
4258 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4259 AND PERIOD_COUNTER = p_period_rec.period_counter;
4260
4261 else
4262
4263 UPDATE FA_BOOKS
4264 SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
4265 WHERE ASSET_ID = p_asset_hdr_rec.asset_id
4266 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4267 AND TRANSACTION_HEADER_ID_OUT is null;
4268
4269 UPDATE FA_BOOKS_SUMMARY
4270 SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
4271 WHERE ASSET_ID = p_asset_hdr_rec.asset_id
4272 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4273 AND PERIOD_COUNTER = p_period_rec.period_counter;
4274
4275 end if;
4276
4277 end if; -- (p_asset_fin_rec.cost = 0) and
4278
4279
4280 if (p_log_level_rec.statement_level) then
4281 fa_debug_pkg.add(l_calling_fn, 'l_is_tgl_due', l_is_tgl_due
4282 ,p_log_level_rec => p_log_level_rec);
4283 fa_debug_pkg.add(l_calling_fn, 'End', 'SUCCESS',p_log_level_rec);
4284 end if;
4285
4286 return TRUE;
4287
4288 EXCEPTION
4289 WHEN tgl_err THEN
4290 if (p_log_level_rec.statement_level) then
4291 fa_debug_pkg.add(l_calling_fn, 'End', FALSE,p_log_level_rec);
4292 end if;
4293
4294 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
4295 p_log_level_rec => p_log_level_rec);
4296 return FALSE;
4297
4298 WHEN OTHERS THEN
4299 if (p_log_level_rec.statement_level) then
4300 fa_debug_pkg.add(l_calling_fn, 'End', FALSE,p_log_level_rec);
4301 end if;
4302
4303 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
4304 p_log_level_rec => p_log_level_rec);
4305 return FALSE;
4306
4307 END Check_Terminal_Gain_Loss;
4308
4309 FUNCTION Do_Allocation(
4310 p_trans_rec IN FA_API_TYPES.trans_rec_type,
4311 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
4312 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
4313 p_asset_deprn_rec_new IN FA_API_TYPES.asset_deprn_rec_type,
4314 p_period_rec IN FA_API_TYPES.period_rec_type,
4315 p_reserve_amount IN NUMBER,
4316 p_mem_ret_thid IN NUMBER DEFAULT NULL,
4317 p_mode IN VARCHAR2 DEFAULT 'NORMAL',
4318 p_mrc_sob_type_code IN VARCHAR2,
4319 p_calling_fn IN VARCHAR2,
4320 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
4321 RETURN BOOLEAN IS
4322
4323 l_calling_fn varchar2(40) := 'FA_RETIREMENT_PVT.Do_Allocation';
4324
4325
4326 CURSOR C_GET_SUM_REC_COST IS
4327 SELECT SUM(RECOVERABLE_COST)
4328 FROM FA_BOOKS
4329 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4330 AND TRANSACTION_HEADER_ID_OUT is null
4331 AND PERIOD_COUNTER_FULLY_RESERVED is null
4332 AND PERIOD_COUNTER_FULLY_RETIRED is null
4333 AND nvl(CIP_COST, 0) = 0
4334 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id;
4335
4336
4337 CURSOR C_MC_GET_SUM_REC_COST IS
4338 SELECT SUM(RECOVERABLE_COST)
4339 FROM FA_BOOKS_MRC_V
4340 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4341 AND TRANSACTION_HEADER_ID_OUT is null
4342 AND PERIOD_COUNTER_FULLY_RESERVED is null
4343 AND PERIOD_COUNTER_FULLY_RETIRED is null
4344 AND nvl(CIP_COST, 0) = 0
4345 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id;
4346
4347 CURSOR C_GET_BS_SUM_ADJ_COST IS
4348 SELECT SUM(ADJUSTED_COST)
4349 FROM FA_BOOKS_SUMMARY
4350 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4351 AND TRANSACTION_HEADER_ID_OUT is null
4352 AND nvl(FULLY_RESERVED_FLAG, 'N') = 'N'
4353 AND nvl(FULLY_RETIRED_FLAG, 'N') = 'N'
4354 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4355 AND nvl(CIP_COST, 0) = 0
4356 AND PERIOD_COUNTER = p_period_rec.period_counter;
4357
4358 CURSOR C_MC_GET_BS_SUM_ADJ_COST IS
4359 SELECT SUM(ADJUSTED_COST)
4360 FROM FA_BOOKS_SUMMARY_MRC_V
4361 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4362 AND TRANSACTION_HEADER_ID_OUT is null
4363 AND nvl(FULLY_RESERVED_FLAG, 'N') = 'N'
4364 AND nvl(FULLY_RETIRED_FLAG, 'N') = 'N'
4365 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4366 AND nvl(CIP_COST, 0) = 0
4367 AND PERIOD_COUNTER = p_period_rec.period_counter;
4368
4369 CURSOR C_GET_GROUP_RSV_RET IS
4370 SELECT sum(decode(AJ.DEBIT_CREDIT_FLAG, 'CR', -1, 1)*AJ.ADJUSTMENT_AMOUNT)
4371 FROM FA_ADJUSTMENTS AJ
4372 WHERE AJ.ASSET_ID = p_asset_hdr_rec.asset_id
4373 AND AJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4374 AND AJ.TRANSACTION_HEADER_ID = p_trans_rec.transaction_header_id;
4375
4376 CURSOR C_MC_GET_GROUP_RSV_RET IS
4377 SELECT sum(decode(AJ.DEBIT_CREDIT_FLAG, 'CR', -1, 1)*AJ.ADJUSTMENT_AMOUNT)
4378 FROM FA_ADJUSTMENTS AJ
4379 WHERE AJ.ASSET_ID = p_asset_hdr_rec.asset_id
4380 AND AJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4381 AND AJ.TRANSACTION_HEADER_ID = p_trans_rec.transaction_header_id;
4382
4383
4384 CURSOR C_GET_MEMBER_ASSETS IS
4385 SELECT BK.ASSET_ID ASSET_ID
4386 , BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
4387 , BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
4388 , BK.ADJUSTED_COST ADJUSTED_COST
4389 , BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
4390 , BK.RECOVERABLE_COST RECOVERABLE_COST
4391 , BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
4392 , AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
4393 , AD.CURRENT_UNITS CURRENT_UNITS
4394 FROM FA_BOOKS BK
4395 , FA_ADDITIONS_B AD
4396 WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4397 AND BK.TRANSACTION_HEADER_ID_OUT is null
4398 AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4399 AND nvl(BK.CIP_COST, 0) = 0
4400 AND BK.PERIOD_COUNTER_FULLY_RESERVED is null
4401 AND BK.PERIOD_COUNTER_FULLY_RETIRED is null
4402 AND BK.ASSET_ID = AD.ASSET_ID
4403 AND AD.ASSET_TYPE = 'CAPITALIZED';
4404
4405 CURSOR C_MC_GET_MEMBER_ASSETS IS
4406 SELECT BK.ASSET_ID ASSET_ID
4407 , BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
4408 , BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
4409 , BK.ADJUSTED_COST ADJUSTED_COST
4410 , BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
4411 , BK.RECOVERABLE_COST RECOVERABLE_COST
4412 , BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
4413 , AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
4414 , AD.CURRENT_UNITS CURRENT_UNITS
4415 FROM FA_BOOKS_MRC_V BK
4416 , FA_ADDITIONS_B AD
4417 WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4418 AND BK.TRANSACTION_HEADER_ID_OUT is null
4419 AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4420 AND nvl(BK.CIP_COST, 0) = 0
4421 AND BK.PERIOD_COUNTER_FULLY_RESERVED is null
4422 AND BK.PERIOD_COUNTER_FULLY_RETIRED is null
4423 AND BK.ASSET_ID = AD.ASSET_ID
4424 AND AD.ASSET_TYPE = 'CAPITALIZED';
4425
4426 CURSOR C_DIST_EXCESS IS
4427 SELECT ASSET_ID
4428 , TRANSACTION_HEADER_ID_IN
4429 , ADJUSTED_COST
4430 FROM FA_BOOKS
4431 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4432 AND TRANSACTION_HEADER_ID_OUT is null
4433 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4434 AND PERIOD_COUNTER_FULLY_RESERVED is null
4435 AND PERIOD_COUNTER_FULLY_RETIRED is null
4436 AND nvl(CIP_COST, 0) = 0
4437 AND ADJUSTED_COST <> 0;
4438
4439 CURSOR C_MC_DIST_EXCESS IS
4440 SELECT ASSET_ID
4441 , TRANSACTION_HEADER_ID_IN
4442 , ADJUSTED_COST
4443 FROM FA_BOOKS_MRC_V
4444 WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4445 AND TRANSACTION_HEADER_ID_OUT is null
4446 AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
4447 AND PERIOD_COUNTER_FULLY_RESERVED is null
4448 AND PERIOD_COUNTER_FULLY_RETIRED is null
4449 AND nvl(CIP_COST, 0) = 0
4450 AND ADJUSTED_COST <> 0;
4451
4452
4453 -- Get old trx info for member and group
4454 CURSOR c_get_ret_member IS
4455 select th.asset_id
4456 , th.transaction_header_id
4457 , aj.debit_credit_flag
4458 , ad.current_units
4459 , ad.asset_category_id
4460 , bk.adjusted_recoverable_cost
4461 , sum(aj.adjustment_amount)
4462 from fa_transaction_headers th -- member
4463 , fa_transaction_headers gth -- group
4464 , fa_adjustments aj
4465 , fa_deprn_periods dp
4466 , fa_additions_b ad
4467 , fa_books bk
4468 where th.book_type_code = p_asset_hdr_rec.book_type_code
4469 -- and th.date_effective = gth.date_effective
4470 and th.source_transaction_header_id = gth.transaction_header_id
4471 and gth.book_type_code = p_asset_hdr_rec.book_type_code
4472 and gth.member_transaction_header_id = p_mem_ret_thid
4473 and aj.asset_id = th.asset_id
4474 and aj.book_type_code = p_asset_hdr_rec.book_type_code
4475 and aj.transaction_header_id = th.transaction_header_id
4476 and dp.book_type_code = p_asset_hdr_rec.book_type_code
4477 and gth.date_effective between dp.period_open_date and nvl(dp.period_close_date, sysdate)
4478 and dp.period_counter = aj.period_counter_created
4479 and th.asset_id = ad.asset_id
4480 and bk.asset_id = th.asset_id
4481 and bk.book_type_code = p_asset_hdr_rec.book_type_code
4482 and bk.group_asset_id = gth.asset_id
4483 and bk.transaction_header_id_out is null
4484 group by th.asset_id
4485 , th.transaction_header_id
4486 , aj.debit_credit_flag
4487 , ad.current_units
4488 , ad.asset_category_id
4489 , bk.adjusted_recoverable_cost;
4490
4491 CURSOR c_get_thid (c_asset_id number) IS
4492 select transaction_header_id_in
4493 from fa_books
4494 where book_type_code = p_asset_hdr_rec.book_type_code
4495 and asset_id = c_asset_id
4496 and transaction_header_id_out is null;
4497
4498 CURSOR c_mc_get_thid (c_asset_id number) IS
4499 select transaction_header_id_in
4500 from fa_books_mrc_v
4501 where book_type_code = p_asset_hdr_rec.book_type_code
4502 and asset_id = c_asset_id
4503 and transaction_header_id_out is null;
4504
4505 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
4506 TYPE tab_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4507 TYPE tab_char3_type IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
4508
4509 l_adj FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT;
4510 t_asset_id tab_num15_type;
4511 t_transaction_header_id_in tab_num15_type;
4512 t_rate_adjustment_factor tab_num_type;
4513 t_recoverable_cost tab_num_type;
4514 t_adjusted_cost tab_num_type;
4515 t_capitalize_flag tab_char3_type;
4516 t_adjusted_recoverable_cost tab_num_type;
4517 t_asset_category_id tab_num15_type;
4518 t_current_units tab_num_type;
4519 t_thid tab_num15_type;
4520 t_new_adj_cost tab_num_type;
4521 t_new_deprn_reserve tab_num_type;
4522 t_adjustment_amount tab_num_type;
4523 l_actual_rsv_total number := 0;
4524
4525 t_debit_credit_flag tab_char3_type; -- reinstatement only
4526
4527
4528 l_last_asset_id number(15);
4529 l_last_thid number(15);
4530
4531 l_sum_rec_cost number;
4532 l_sum_adj_cost number;
4533 l_group_rsv_ret number;
4534
4535 l_reallocate_amount number;
4536
4537 -- Query balance parameters
4538 l_deprn_reserve NUMBER;
4539 l_temp_num NUMBER;
4540 l_temp_char VARCHAR2(30);
4541 l_temp_bool BOOLEAN;
4542
4543 l_limit BINARY_INTEGER := 500;
4544 allocate_err EXCEPTION;
4545
4546 l_loc varchar2(245);
4547
4548 BEGIN
4549
4550 if (p_log_level_rec.statement_level) then
4551 fa_debug_pkg.add(l_calling_fn, 'Begin', p_asset_hdr_rec.asset_id,p_log_level_rec);
4552 fa_debug_pkg.add(l_calling_fn, 'Transaction_key', p_trans_rec.transaction_key,p_log_level_rec);
4553 end if;
4554
4555 -- Prepare common FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT parameters
4556 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
4557 l_adj.period_counter_created := p_period_rec.period_counter;
4558 l_adj.period_counter_adjusted := p_period_rec.period_counter;
4559 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
4560 l_adj.selection_retid := 0;
4561 l_adj.leveling_flag := TRUE;
4562 l_adj.flush_adj_flag := TRUE; -- FALSE;
4563 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
4564 l_adj.gen_ccid_flag := TRUE;
4565 l_adj.adjustment_type := 'RESERVE';
4566 l_adj.account_type := 'DEPRN_RESERVE_ACCT';
4567 l_adj.track_member_flag := 'Y';
4568 l_adj.source_type_code := 'ADJUSTMENT';
4569
4570 --
4571 -- If this is called from retirement, then we know
4572 -- debit/credit flag from p_reserve_amount
4573 --
4574 if (p_trans_rec.transaction_key = 'MR') or
4575 (p_mode = 'RECURR') then
4576
4577 --
4578 -- If this is called from retirement, then we know
4579 -- debit/credit flag from p_reserve_amount
4580 --
4581 if (p_reserve_amount > 0) then
4582 l_adj.debit_credit_flag := 'DR';
4583 else
4584 l_adj.debit_credit_flag := 'CR';
4585 end if;
4586
4587 /*
4588 if (p_mrc_sob_type_code = 'R') then
4589 OPEN C_MC_GET_SUM_REC_COST; --or C_MC_GET_BS_SUM_ADJ_COST;
4590 FETCH C_MC_GET_SUM_REC_COST INTO l_sum_rec_cost;
4591 CLOSE C_MC_GET_SUM_REC_COST;
4592 else
4593 OPEN C_GET_SUM_REC_COST; --or C_GET_BS_SUM_ADJ_COST;
4594 FETCH C_GET_SUM_REC_COST INTO l_sum_rec_cost;
4595 CLOSE C_GET_SUM_REC_COST;
4596 end if;
4597
4598
4599 if (p_calling_fn = 'FAPGADJ') then
4600
4601 if (p_mrc_sob_type_code = 'R') then
4602 OPEN C_MC_GET_GROUP_RSV_RET;
4603 FETCH C_MC_GET_GROUP_RSV_RET INTO l_group_rsv_ret;
4604 CLOSE C_MC_GET_GROUP_RSV_RET;
4605 else
4606 OPEN C_GET_GROUP_RSV_RET;
4607 FETCH C_GET_GROUP_RSV_RET INTO l_group_rsv_ret;
4608 CLOSE C_GET_GROUP_RSV_RET;
4609 end if;
4610
4611 l_sum_adj_cost := l_sum_rec_cost - p_asset_deprn_rec_new.deprn_reserve + l_group_rsv_ret;
4612
4613 else
4614 l_sum_adj_cost := l_sum_rec_cost - p_asset_deprn_rec_new.deprn_reserve;
4615 end if;
4616 */
4617
4618 --toru
4619 l_sum_adj_cost := p_asset_fin_rec.recoverable_cost - p_asset_deprn_rec_new.deprn_reserve -
4620 p_reserve_amount;
4621
4622 if (p_log_level_rec.statement_level) then
4623 fa_debug_pkg.add(l_calling_fn, 'group reserve', p_asset_deprn_rec_new.deprn_reserve,p_log_level_rec);
4624 fa_debug_pkg.add(l_calling_fn, 'sum rec_cost', p_asset_fin_rec.recoverable_cost,p_log_level_rec);
4625 fa_debug_pkg.add(l_calling_fn, 'p_reserve_amount', p_reserve_amount,p_log_level_rec);
4626 fa_debug_pkg.add(l_calling_fn, 'l_sum_adj_cost', l_sum_adj_cost,p_log_level_rec);
4627 end if;
4628
4629
4630 if (p_mrc_sob_type_code = 'R') then
4631 OPEN C_MC_GET_MEMBER_ASSETS;
4632 else
4633 OPEN C_GET_MEMBER_ASSETS;
4634 end if;
4635
4636 elsif (p_trans_rec.transaction_key = 'MS') then
4637 OPEN c_get_ret_member;
4638 end if;
4639
4640 l_loc := 'Before Main Loop';
4641 LOOP -- MAIN OUTER LOOP
4642
4643 t_thid.delete;
4644 t_asset_id.delete;
4645 t_transaction_header_id_in.delete;
4646 t_rate_adjustment_factor.delete;
4647 t_adjusted_cost.delete;
4648 t_capitalize_flag.delete;
4649 t_recoverable_cost.delete;
4650 t_adjusted_recoverable_cost.delete;
4651 t_asset_category_id.delete;
4652 t_current_units.delete;
4653 t_new_adj_cost.delete;
4654 t_adjustment_amount.delete;
4655
4656 if (p_trans_rec.transaction_key = 'MR') or
4657 (p_mode = 'RECURR') then
4658 -- can this be update statement?
4659 if (p_mrc_sob_type_code = 'R') then
4660 FETCH C_MC_GET_MEMBER_ASSETS BULK COLLECT INTO t_asset_id
4661 , t_transaction_header_id_in
4662 , t_rate_adjustment_factor
4663 , t_adjusted_cost
4664 , t_capitalize_flag
4665 , t_recoverable_cost
4666 , t_adjusted_recoverable_cost
4667 , t_asset_category_id
4668 , t_current_units LIMIT l_limit;
4669 else
4670 FETCH C_GET_MEMBER_ASSETS BULK COLLECT INTO t_asset_id
4671 , t_transaction_header_id_in
4672 , t_rate_adjustment_factor
4673 , t_adjusted_cost
4674 , t_capitalize_flag
4675 , t_recoverable_cost
4676 , t_adjusted_recoverable_cost
4677 , t_asset_category_id
4678 , t_current_units LIMIT l_limit;
4679 end if;
4680
4681 if (p_log_level_rec.statement_level) then
4682 fa_debug_pkg.add(l_calling_fn, 'In Main Cursor', t_asset_id.count,p_log_level_rec);
4683 end if;
4684
4685 if t_asset_id.count = 0 then
4686
4687 if (p_mrc_sob_type_code = 'R') then
4688 CLOSE C_MC_GET_MEMBER_ASSETS;
4689 else
4690 CLOSE C_GET_MEMBER_ASSETS;
4691 end if;
4692 EXIT;
4693 end if;
4694
4695 elsif (p_trans_rec.transaction_key = 'MS') then
4696
4697 FETCH c_get_ret_member BULK COLLECT INTO t_asset_id
4698 , t_transaction_header_id_in
4699 , t_debit_credit_flag
4700 , t_current_units
4701 , t_asset_category_id
4702 , t_adjusted_recoverable_cost
4703 , t_adjustment_amount
4704 LIMIT l_limit;
4705
4706 if t_asset_id.count = 0 then
4707
4708 CLOSE c_get_ret_member;
4709 EXIT;
4710
4711 end if;
4712
4713 end if; -- (p_trans_rec.transaction_key = 'MR')
4714
4715 l_loc := 'Before Insert TH';
4716
4717 FORALL i in t_asset_id.first..t_asset_id.last
4718 INSERT INTO FA_TRANSACTION_HEADERS(
4719 TRANSACTION_HEADER_ID
4720 , BOOK_TYPE_CODE
4721 , ASSET_ID
4722 , TRANSACTION_TYPE_CODE
4723 , TRANSACTION_DATE_ENTERED
4724 , DATE_EFFECTIVE
4725 , LAST_UPDATE_DATE
4726 , LAST_UPDATED_BY
4727 , SOURCE_TRANSACTION_HEADER_ID
4728 , MASS_REFERENCE_ID
4729 , LAST_UPDATE_LOGIN
4730 , TRANSACTION_SUBTYPE
4731 , TRANSACTION_KEY
4732 , AMORTIZATION_START_DATE
4733 , CALLING_INTERFACE
4734 , MASS_TRANSACTION_ID
4735 ) VALUES (
4736 FA_TRANSACTION_HEADERS_S.NEXTVAL
4737 , p_asset_hdr_rec.book_type_code
4738 , t_asset_id(i)
4739 , 'ADJUSTMENT'
4740 , p_trans_rec.transaction_date_entered
4741 , p_trans_rec.who_info.last_update_date
4742 , p_trans_rec.who_info.last_update_date
4743 , p_trans_rec.who_info.last_updated_by
4744 , p_trans_rec.transaction_header_id
4745 , p_trans_rec.mass_reference_id
4746 , p_trans_rec.who_info.last_update_login
4747 , 'AMORTIZED'
4748 , 'RA'
4749 , p_trans_rec.amortization_start_date
4750 , p_trans_rec.calling_interface
4751 , p_trans_rec.mass_transaction_id
4752 ) RETURNING transaction_header_id BULK COLLECT INTO t_thid;
4753
4754
4755 l_loc := 'Before ADJ LOOP';
4756 -- Prepare non-common FA_ADJUST_TYPE_PKG.FA_ADJ_ROW_STRUCT parameters and
4757 -- call faxinaj to create records in FA_ADJUSTMENTS table
4758 if (p_log_level_rec.statement_level) then
4759 fa_debug_pkg.add(l_calling_fn, 'Before ADJ LOOP', t_asset_id(1),p_log_level_rec);
4760 fa_debug_pkg.add(l_calling_fn, 't_asset_id.last', t_asset_id.last,p_log_level_rec);
4761 end if;
4762
4763
4764 FOR i in 1..t_asset_id.last LOOP
4765
4766 if (p_log_level_rec.statement_level) then
4767 fa_debug_pkg.add(l_calling_fn, 'p_reserve_amount', p_reserve_amount,p_log_level_rec);
4768 fa_debug_pkg.add(l_calling_fn, 'l_sum_adj_cost', l_sum_adj_cost,p_log_level_rec);
4769 end if;
4770
4771 if (p_trans_rec.transaction_key = 'MR') or
4772 (p_mode = 'RECURR') then
4773
4774 if (p_log_level_rec.statement_level) then
4775 fa_debug_pkg.add(l_calling_fn, 'Call',
4776 'fa_query_balances_pkg.query_balances',p_log_level_rec);
4777 end if;
4778
4779 fa_query_balances_pkg.query_balances(
4780 X_asset_id => t_asset_id(i),
4781 X_book => p_asset_hdr_rec.book_type_code,
4782 X_period_ctr => 0,
4783 X_dist_id => 0,
4784 X_run_mode => 'STANDARD',
4785 X_cost => l_temp_num,
4786 X_deprn_rsv => l_deprn_reserve,
4787 X_reval_rsv => l_temp_num,
4788 X_ytd_deprn => l_temp_num,
4789 X_ytd_reval_exp => l_temp_num,
4790 X_reval_deprn_exp => l_temp_num,
4791 X_deprn_exp => l_temp_num,
4792 X_reval_amo => l_temp_num,
4793 X_prod => l_temp_num,
4794 X_ytd_prod => l_temp_num,
4795 X_ltd_prod => l_temp_num,
4796 X_adj_cost => l_temp_num,
4797 X_reval_amo_basis => l_temp_num,
4798 X_bonus_rate => l_temp_num,
4799 X_deprn_source_code => l_temp_char,
4800 X_adjusted_flag => l_temp_bool,
4801 X_transaction_header_id => -1,
4802 X_bonus_deprn_rsv => l_temp_num,
4803 X_bonus_ytd_deprn => l_temp_num,
4804 X_bonus_deprn_amount => l_temp_num,
4805 p_log_level_rec => p_log_level_rec);
4806
4807 if (p_log_level_rec.statement_level) then
4808 fa_debug_pkg.add(l_calling_fn, 'l_deprn_reserve',
4809 l_deprn_reserve,p_log_level_rec);
4810 fa_debug_pkg.add(l_calling_fn, 't_adjusted_cost('||to_char(i)||')',
4811 t_adjusted_cost(i),p_log_level_rec);
4812 fa_debug_pkg.add(l_calling_fn, 't_recoverable_cost('||to_char(i)||')',
4813 t_recoverable_cost(i),p_log_level_rec);
4814 end if;
4815
4816 t_adjusted_cost(i) := t_recoverable_cost(i) - l_deprn_reserve;
4817
4818 if (p_log_level_rec.statement_level) then
4819 fa_debug_pkg.add(l_calling_fn, 't_adjusted_cost('||to_char(i)||')', t_adjusted_cost(i),p_log_level_rec);
4820 end if;
4821
4822
4823 l_adj.adjustment_amount := abs(p_reserve_amount) * (t_adjusted_cost(i)/l_sum_adj_cost);
4824
4825 if not FA_UTILS_PKG.faxrnd(l_adj.adjustment_amount,
4826 p_asset_hdr_rec.book_type_code,
4827 p_log_level_rec) then
4828 raise allocate_err;
4829 end if;
4830
4831 l_actual_rsv_total := l_actual_rsv_total + sign(p_reserve_amount) * l_adj.adjustment_amount;
4832
4833 t_adjustment_amount(i) := sign(p_reserve_amount) * -1 * l_adj.adjustment_amount;
4834
4835 elsif (p_trans_rec.transaction_key = 'MS') then
4836
4837 if (p_mrc_sob_type_code = 'R') then
4838 --toru
4839 OPEN c_mc_get_thid(t_asset_id(i));
4840 FETCH c_mc_get_thid INTO t_transaction_header_id_in(i);
4841 CLOSE c_mc_get_thid;
4842
4843 else
4844
4845 OPEN c_get_thid(t_asset_id(i));
4846 FETCH c_get_thid INTO t_transaction_header_id_in(i);
4847 CLOSE c_get_thid;
4848
4849 end if;
4850
4851 fa_query_balances_pkg.query_balances(
4852 X_asset_id => t_asset_id(i),
4853 X_book => p_asset_hdr_rec.book_type_code,
4854 X_period_ctr => 0,
4855 X_dist_id => 0,
4856 X_run_mode => 'STANDARD',
4857 X_cost => l_temp_num,
4858 X_deprn_rsv => l_deprn_reserve,
4859 X_reval_rsv => l_temp_num,
4860 X_ytd_deprn => l_temp_num,
4861 X_ytd_reval_exp => l_temp_num,
4862 X_reval_deprn_exp => l_temp_num,
4863 X_deprn_exp => l_temp_num,
4864 X_reval_amo => l_temp_num,
4865 X_prod => l_temp_num,
4866 X_ytd_prod => l_temp_num,
4867 X_ltd_prod => l_temp_num,
4868 X_adj_cost => l_temp_num,
4869 X_reval_amo_basis => l_temp_num,
4870 X_bonus_rate => l_temp_num,
4871 X_deprn_source_code => l_temp_char,
4872 X_adjusted_flag => l_temp_bool,
4873 X_transaction_header_id => -1,
4874 X_bonus_deprn_rsv => l_temp_num,
4875 X_bonus_ytd_deprn => l_temp_num,
4876 X_bonus_deprn_amount => l_temp_num,
4877 p_log_level_rec => p_log_level_rec);
4878
4879 if (p_log_level_rec.statement_level) then
4880 fa_debug_pkg.add(l_calling_fn, 'l_deprn_reserve',
4881 l_deprn_reserve,p_log_level_rec);
4882 fa_debug_pkg.add(l_calling_fn, 't_adjusted_recoverable_cost('||to_char(i)||')',
4883 t_adjusted_recoverable_cost(i),p_log_level_rec);
4884 end if;
4885
4886 -- Check to see if the asset can take entire reserve reinstated or not.
4887 -- If not, it takes as much as it can.
4888 if (t_adjusted_recoverable_cost(i) - l_deprn_reserve - t_adjustment_amount(i) < 0) then
4889 t_adjustment_amount(i) := t_adjusted_recoverable_cost(i) - l_deprn_reserve;
4890 end if;
4891
4892 if (t_debit_credit_flag(i) = 'DR') then
4893 l_adj.adjustment_amount := t_adjustment_amount(i);
4894 l_adj.debit_credit_flag := 'CR';
4895 else
4896 l_adj.adjustment_amount := t_adjustment_amount(i);
4897 t_adjustment_amount(i) := -1 * t_adjustment_amount(i);
4898 l_adj.debit_credit_flag := 'DR';
4899 end if;
4900
4901 l_actual_rsv_total := l_actual_rsv_total + l_adj.adjustment_amount;
4902
4903
4904 end if;
4905
4906 if (p_log_level_rec.statement_level) then
4907 fa_debug_pkg.add(l_calling_fn, 'Recapture Excess Reserve',
4908 l_adj.adjustment_amount,p_log_level_rec);
4909 end if;
4910
4911 l_adj.asset_id := t_asset_id(i);
4912 l_adj.transaction_header_id := t_thid(i);
4913 l_adj.current_units := t_current_units(i);
4914
4915 if not fa_cache_pkg.fazccb(p_asset_hdr_rec.book_type_code,
4916 t_asset_category_id(i),
4917 p_log_level_rec) then
4918 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
4919 p_log_level_rec => p_log_level_rec);
4920 raise allocate_err;
4921 end if;
4922
4923 l_adj.account := fa_cache_pkg.fazccb_record.DEPRN_RESERVE_ACCT;
4924
4925 if not FA_INS_ADJUST_PKG.faxinaj
4926 (l_adj,
4927 p_trans_rec.who_info.last_update_date,
4928 p_trans_rec.who_info.last_updated_by,
4929 p_trans_rec.who_info.last_update_login,
4930 p_log_level_rec) then
4931 raise allocate_err;
4932 end if;
4933 --tk_util.debug('finish adj');
4934
4935 l_last_asset_id := t_asset_id(i);
4936 l_last_thid := t_thid(i);
4937 END LOOP;
4938
4939 if (p_mrc_sob_type_code = 'R') then
4940 l_loc := 'Before BS';
4941 -- Update FA_BOOKS_SUMAMRY_MRC_V table with new reserve adjustment amount and
4942 -- related columns. Return new adjusted_cost and deprn reserve for later use
4943 FORALL i in t_asset_id.first..t_asset_id.last
4944 UPDATE FA_BOOKS_SUMMARY_MRC_V
4945 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjustment_amount(i)
4946 , DEPRN_RESERVE = DEPRN_RESERVE + t_adjustment_amount(i)
4947 , ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE - t_adjustment_amount(i)
4948 WHERE ASSET_ID = t_asset_id(i)
4949 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
4950 AND PERIOD_COUNTER = p_period_rec.period_counter
4951 AND TRANSACTION_HEADER_ID_OUT IS NULL
4952 RETURNING ADJUSTED_COST, DEPRN_RESERVE
4953 BULK COLLECT INTO t_new_adj_cost, t_new_deprn_reserve;
4954
4955
4956 l_loc := 'Before Books';
4957 -- Deactivate FA_BOOKS row for processed assets
4958 FORALL i in t_asset_id.first..t_asset_id.last
4959 UPDATE FA_BOOKS_MRC_V
4960 SET DATE_INEFFECTIVE = p_trans_rec.who_info.last_update_date
4961 , TRANSACTION_HEADER_ID_OUT = t_thid(i)
4962 WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
4963
4964
4965 -- Insert new FA_BOOKS records for processed assets
4966 FORALL i in t_asset_id.first..t_asset_id.last
4967 INSERT INTO FA_BOOKS_MRC_V( BOOK_TYPE_CODE
4968 , ASSET_ID
4969 , DATE_PLACED_IN_SERVICE
4970 , DATE_EFFECTIVE
4971 , DEPRN_START_DATE
4972 , DEPRN_METHOD_CODE
4973 , LIFE_IN_MONTHS
4974 , RATE_ADJUSTMENT_FACTOR
4975 , ADJUSTED_COST
4976 , COST
4977 , ORIGINAL_COST
4978 , SALVAGE_VALUE
4979 , PRORATE_CONVENTION_CODE
4980 , PRORATE_DATE
4981 , COST_CHANGE_FLAG
4982 , ADJUSTMENT_REQUIRED_STATUS
4983 , CAPITALIZE_FLAG
4984 , RETIREMENT_PENDING_FLAG
4985 , DEPRECIATE_FLAG
4986 , LAST_UPDATE_DATE
4987 , LAST_UPDATED_BY
4988 , TRANSACTION_HEADER_ID_IN
4989 , ITC_AMOUNT_ID
4990 , ITC_AMOUNT
4991 , RETIREMENT_ID
4992 , TAX_REQUEST_ID
4993 , ITC_BASIS
4994 , BASIC_RATE
4995 , ADJUSTED_RATE
4996 , BONUS_RULE
4997 , CEILING_NAME
4998 , RECOVERABLE_COST
4999 , LAST_UPDATE_LOGIN
5000 , ADJUSTED_CAPACITY
5001 , FULLY_RSVD_REVALS_COUNTER
5002 , IDLED_FLAG
5003 , PERIOD_COUNTER_CAPITALIZED
5004 , PERIOD_COUNTER_FULLY_RESERVED
5005 , PERIOD_COUNTER_FULLY_RETIRED
5006 , PRODUCTION_CAPACITY
5007 , REVAL_AMORTIZATION_BASIS
5008 , REVAL_CEILING
5009 , UNIT_OF_MEASURE
5010 , UNREVALUED_COST
5011 , ANNUAL_DEPRN_ROUNDING_FLAG
5012 , PERCENT_SALVAGE_VALUE
5013 , ALLOWED_DEPRN_LIMIT
5014 , ALLOWED_DEPRN_LIMIT_AMOUNT
5015 , PERIOD_COUNTER_LIFE_COMPLETE
5016 , ADJUSTED_RECOVERABLE_COST
5017 , ANNUAL_ROUNDING_FLAG
5018 , GLOBAL_ATTRIBUTE1
5019 , GLOBAL_ATTRIBUTE2
5020 , GLOBAL_ATTRIBUTE3
5021 , GLOBAL_ATTRIBUTE4
5022 , GLOBAL_ATTRIBUTE5
5023 , GLOBAL_ATTRIBUTE6
5024 , GLOBAL_ATTRIBUTE7
5025 , GLOBAL_ATTRIBUTE8
5026 , GLOBAL_ATTRIBUTE9
5027 , GLOBAL_ATTRIBUTE10
5028 , GLOBAL_ATTRIBUTE11
5029 , GLOBAL_ATTRIBUTE12
5030 , GLOBAL_ATTRIBUTE13
5031 , GLOBAL_ATTRIBUTE14
5032 , GLOBAL_ATTRIBUTE15
5033 , GLOBAL_ATTRIBUTE16
5034 , GLOBAL_ATTRIBUTE17
5035 , GLOBAL_ATTRIBUTE18
5036 , GLOBAL_ATTRIBUTE19
5037 , GLOBAL_ATTRIBUTE20
5038 , GLOBAL_ATTRIBUTE_CATEGORY
5039 , EOFY_ADJ_COST
5040 , EOFY_FORMULA_FACTOR
5041 , SHORT_FISCAL_YEAR_FLAG
5042 , CONVERSION_DATE
5043 , ORIGINAL_DEPRN_START_DATE
5044 , REMAINING_LIFE1
5045 , REMAINING_LIFE2
5046 , OLD_ADJUSTED_COST
5047 , FORMULA_FACTOR
5048 , GROUP_ASSET_ID
5049 , SALVAGE_TYPE
5050 , DEPRN_LIMIT_TYPE
5051 , REDUCTION_RATE
5052 , REDUCE_ADDITION_FLAG
5053 , REDUCE_ADJUSTMENT_FLAG
5054 , REDUCE_RETIREMENT_FLAG
5055 , RECOGNIZE_GAIN_LOSS
5056 , RECAPTURE_RESERVE_FLAG
5057 , LIMIT_PROCEEDS_FLAG
5058 , TERMINAL_GAIN_LOSS
5059 , TRACKING_METHOD
5060 , EXCLUDE_FULLY_RSV_FLAG
5061 , EXCESS_ALLOCATION_OPTION
5062 , DEPRECIATION_OPTION
5063 , MEMBER_ROLLUP_FLAG
5064 , ALLOCATE_TO_FULLY_RSV_FLAG
5065 , ALLOCATE_TO_FULLY_RET_FLAG
5066 , TERMINAL_GAIN_LOSS_AMOUNT
5067 , CIP_COST
5068 , YTD_PROCEEDS
5069 , LTD_PROCEEDS
5070 , LTD_COST_OF_REMOVAL
5071 , EOFY_RESERVE
5072 , PRIOR_EOFY_RESERVE
5073 , EOP_ADJ_COST
5074 , EOP_FORMULA_FACTOR
5075 , EXCLUDE_PROCEEDS_FROM_BASIS
5076 , RETIREMENT_DEPRN_OPTION
5077 , TERMINAL_GAIN_LOSS_FLAG
5078 , SUPER_GROUP_ID
5079 , OVER_DEPRECIATE_OPTION
5080 , DISABLED_FLAG
5081 ) SELECT BOOK_TYPE_CODE
5082 , ASSET_ID
5083 , DATE_PLACED_IN_SERVICE
5084 , p_trans_rec.who_info.last_update_date -- DATE_EFFECTIVE
5085 , DEPRN_START_DATE
5086 , DEPRN_METHOD_CODE
5087 , LIFE_IN_MONTHS
5088 , RATE_ADJUSTMENT_FACTOR
5089 , t_new_adj_cost(i) -- ADJUSTED_COST
5090 , COST
5091 , ORIGINAL_COST
5092 , SALVAGE_VALUE
5093 , PRORATE_CONVENTION_CODE
5094 , PRORATE_DATE
5095 , COST_CHANGE_FLAG
5096 , ADJUSTMENT_REQUIRED_STATUS
5097 , CAPITALIZE_FLAG
5098 , RETIREMENT_PENDING_FLAG
5099 , DEPRECIATE_FLAG
5100 , p_trans_rec.who_info.last_update_date -- LAST_UPDATE_DATE
5101 , p_trans_rec.who_info.last_updated_by -- LAST_UPDATED_BY
5102 , t_thid(i) -- TRANSACTION_HEADER_ID_IN
5103 , ITC_AMOUNT_ID
5104 , ITC_AMOUNT
5105 , RETIREMENT_ID
5106 , TAX_REQUEST_ID
5107 , ITC_BASIS
5108 , BASIC_RATE
5109 , ADJUSTED_RATE
5110 , BONUS_RULE
5111 , CEILING_NAME
5112 , RECOVERABLE_COST
5113 , p_trans_rec.who_info.last_update_login -- LAST_UPDATE_LOGIN
5114 , ADJUSTED_CAPACITY
5115 , FULLY_RSVD_REVALS_COUNTER
5116 , IDLED_FLAG
5117 , PERIOD_COUNTER_CAPITALIZED
5118 , PERIOD_COUNTER_FULLY_RESERVED
5119 , PERIOD_COUNTER_FULLY_RETIRED
5120 , PRODUCTION_CAPACITY
5121 , REVAL_AMORTIZATION_BASIS
5122 , REVAL_CEILING
5123 , UNIT_OF_MEASURE
5124 , UNREVALUED_COST
5125 , ANNUAL_DEPRN_ROUNDING_FLAG
5126 , PERCENT_SALVAGE_VALUE
5127 , ALLOWED_DEPRN_LIMIT
5128 , ALLOWED_DEPRN_LIMIT_AMOUNT
5129 , PERIOD_COUNTER_LIFE_COMPLETE
5130 , ADJUSTED_RECOVERABLE_COST
5131 , ANNUAL_ROUNDING_FLAG
5132 , GLOBAL_ATTRIBUTE1
5133 , GLOBAL_ATTRIBUTE2
5134 , GLOBAL_ATTRIBUTE3
5135 , GLOBAL_ATTRIBUTE4
5136 , GLOBAL_ATTRIBUTE5
5137 , GLOBAL_ATTRIBUTE6
5138 , GLOBAL_ATTRIBUTE7
5139 , GLOBAL_ATTRIBUTE8
5140 , GLOBAL_ATTRIBUTE9
5141 , GLOBAL_ATTRIBUTE10
5142 , GLOBAL_ATTRIBUTE11
5143 , GLOBAL_ATTRIBUTE12
5144 , GLOBAL_ATTRIBUTE13
5145 , GLOBAL_ATTRIBUTE14
5146 , GLOBAL_ATTRIBUTE15
5147 , GLOBAL_ATTRIBUTE16
5148 , GLOBAL_ATTRIBUTE17
5149 , GLOBAL_ATTRIBUTE18
5150 , GLOBAL_ATTRIBUTE19
5151 , GLOBAL_ATTRIBUTE20
5152 , GLOBAL_ATTRIBUTE_CATEGORY
5153 , EOFY_ADJ_COST
5154 , EOFY_FORMULA_FACTOR
5155 , SHORT_FISCAL_YEAR_FLAG
5156 , CONVERSION_DATE
5157 , ORIGINAL_DEPRN_START_DATE
5158 , REMAINING_LIFE1
5159 , REMAINING_LIFE2
5160 , OLD_ADJUSTED_COST
5161 , FORMULA_FACTOR
5162 , GROUP_ASSET_ID
5163 , SALVAGE_TYPE
5164 , DEPRN_LIMIT_TYPE
5165 , REDUCTION_RATE
5166 , REDUCE_ADDITION_FLAG
5167 , REDUCE_ADJUSTMENT_FLAG
5168 , REDUCE_RETIREMENT_FLAG
5169 , RECOGNIZE_GAIN_LOSS
5170 , RECAPTURE_RESERVE_FLAG
5171 , LIMIT_PROCEEDS_FLAG
5172 , TERMINAL_GAIN_LOSS
5173 , TRACKING_METHOD
5174 , EXCLUDE_FULLY_RSV_FLAG
5175 , EXCESS_ALLOCATION_OPTION
5176 , DEPRECIATION_OPTION
5177 , MEMBER_ROLLUP_FLAG
5178 , ALLOCATE_TO_FULLY_RSV_FLAG
5179 , ALLOCATE_TO_FULLY_RET_FLAG
5180 , TERMINAL_GAIN_LOSS_AMOUNT
5181 , CIP_COST
5182 , YTD_PROCEEDS
5183 , LTD_PROCEEDS
5184 , LTD_COST_OF_REMOVAL
5185 , EOFY_RESERVE
5186 , PRIOR_EOFY_RESERVE
5187 , EOP_ADJ_COST
5188 , EOP_FORMULA_FACTOR
5189 , EXCLUDE_PROCEEDS_FROM_BASIS
5190 , RETIREMENT_DEPRN_OPTION
5191 , TERMINAL_GAIN_LOSS_FLAG
5192 , SUPER_GROUP_ID
5193 , OVER_DEPRECIATE_OPTION
5194 , DISABLED_FLAG
5195 FROM FA_BOOKS_MRC_V
5196 WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
5197
5198 else
5199 l_loc := 'Before BS';
5200 -- Update FA_BOOKS_SUMAMRY table with new reserve adjustment amount and
5201 -- related columns. Return new adjusted_cost and deprn reserve for later use
5202 FORALL i in t_asset_id.first..t_asset_id.last
5203 UPDATE FA_BOOKS_SUMMARY
5204 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjustment_amount(i)
5205 , DEPRN_RESERVE = DEPRN_RESERVE + t_adjustment_amount(i)
5206 , ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE - t_adjustment_amount(i)
5207 WHERE ASSET_ID = t_asset_id(i)
5208 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5209 AND PERIOD_COUNTER = p_period_rec.period_counter
5210 AND TRANSACTION_HEADER_ID_OUT IS NULL
5211 RETURNING ADJUSTED_COST, DEPRN_RESERVE
5212 BULK COLLECT INTO t_new_adj_cost, t_new_deprn_reserve;
5213
5214 if (p_log_level_rec.statement_level) then
5215 for i in t_asset_id.first..t_asset_id.last loop
5216 fa_debug_pkg.add(l_calling_fn, 'i', i,p_log_level_rec);
5217 fa_debug_pkg.add(l_calling_fn, 't_new_adj_cost', t_new_adj_cost(i),p_log_level_rec);
5218 fa_debug_pkg.add(l_calling_fn, 't_new_deprn_reserve', t_new_deprn_reserve(i),p_log_level_rec);
5219 fa_debug_pkg.add(l_calling_fn, 't_transaction_header_id_in', t_transaction_header_id_in(i),p_log_level_rec);
5220 fa_debug_pkg.add(l_calling_fn, 't_asset_id', t_asset_id(i),p_log_level_rec);
5221 fa_debug_pkg.add(l_calling_fn, 't_thid', t_thid(i),p_log_level_rec);
5222 end loop;
5223 end if;
5224
5225 l_loc := 'Before Books';
5226 -- Deactivate FA_BOOKS row for processed assets
5227 FORALL i in t_asset_id.first..t_asset_id.last
5228 UPDATE FA_BOOKS
5229 SET DATE_INEFFECTIVE = p_trans_rec.who_info.last_update_date
5230 , TRANSACTION_HEADER_ID_OUT = t_thid(i)
5231 WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
5232
5233 l_loc := 'Before Insert Books';
5234 -- Insert new FA_BOOKS records for processed assets
5235 FORALL i in t_asset_id.first..t_asset_id.last
5236 INSERT INTO FA_BOOKS( BOOK_TYPE_CODE
5237 , ASSET_ID
5238 , DATE_PLACED_IN_SERVICE
5239 , DATE_EFFECTIVE
5240 , DEPRN_START_DATE
5241 , DEPRN_METHOD_CODE
5242 , LIFE_IN_MONTHS
5243 , RATE_ADJUSTMENT_FACTOR
5244 , ADJUSTED_COST
5245 , COST
5246 , ORIGINAL_COST
5247 , SALVAGE_VALUE
5248 , PRORATE_CONVENTION_CODE
5249 , PRORATE_DATE
5250 , COST_CHANGE_FLAG
5251 , ADJUSTMENT_REQUIRED_STATUS
5252 , CAPITALIZE_FLAG
5253 , RETIREMENT_PENDING_FLAG
5254 , DEPRECIATE_FLAG
5255 , LAST_UPDATE_DATE
5256 , LAST_UPDATED_BY
5257 , TRANSACTION_HEADER_ID_IN
5258 , ITC_AMOUNT_ID
5259 , ITC_AMOUNT
5260 , RETIREMENT_ID
5261 , TAX_REQUEST_ID
5262 , ITC_BASIS
5263 , BASIC_RATE
5264 , ADJUSTED_RATE
5265 , BONUS_RULE
5266 , CEILING_NAME
5267 , RECOVERABLE_COST
5268 , LAST_UPDATE_LOGIN
5269 , ADJUSTED_CAPACITY
5270 , FULLY_RSVD_REVALS_COUNTER
5271 , IDLED_FLAG
5272 , PERIOD_COUNTER_CAPITALIZED
5273 , PERIOD_COUNTER_FULLY_RESERVED
5274 , PERIOD_COUNTER_FULLY_RETIRED
5275 , PRODUCTION_CAPACITY
5276 , REVAL_AMORTIZATION_BASIS
5277 , REVAL_CEILING
5278 , UNIT_OF_MEASURE
5279 , UNREVALUED_COST
5280 , ANNUAL_DEPRN_ROUNDING_FLAG
5281 , PERCENT_SALVAGE_VALUE
5282 , ALLOWED_DEPRN_LIMIT
5283 , ALLOWED_DEPRN_LIMIT_AMOUNT
5284 , PERIOD_COUNTER_LIFE_COMPLETE
5285 , ADJUSTED_RECOVERABLE_COST
5286 , ANNUAL_ROUNDING_FLAG
5287 , GLOBAL_ATTRIBUTE1
5288 , GLOBAL_ATTRIBUTE2
5289 , GLOBAL_ATTRIBUTE3
5290 , GLOBAL_ATTRIBUTE4
5291 , GLOBAL_ATTRIBUTE5
5292 , GLOBAL_ATTRIBUTE6
5293 , GLOBAL_ATTRIBUTE7
5294 , GLOBAL_ATTRIBUTE8
5295 , GLOBAL_ATTRIBUTE9
5296 , GLOBAL_ATTRIBUTE10
5297 , GLOBAL_ATTRIBUTE11
5298 , GLOBAL_ATTRIBUTE12
5299 , GLOBAL_ATTRIBUTE13
5300 , GLOBAL_ATTRIBUTE14
5301 , GLOBAL_ATTRIBUTE15
5302 , GLOBAL_ATTRIBUTE16
5303 , GLOBAL_ATTRIBUTE17
5304 , GLOBAL_ATTRIBUTE18
5305 , GLOBAL_ATTRIBUTE19
5306 , GLOBAL_ATTRIBUTE20
5307 , GLOBAL_ATTRIBUTE_CATEGORY
5308 , EOFY_ADJ_COST
5309 , EOFY_FORMULA_FACTOR
5310 , SHORT_FISCAL_YEAR_FLAG
5311 , CONVERSION_DATE
5312 , ORIGINAL_DEPRN_START_DATE
5313 , REMAINING_LIFE1
5314 , REMAINING_LIFE2
5315 , OLD_ADJUSTED_COST
5316 , FORMULA_FACTOR
5317 , GROUP_ASSET_ID
5318 , SALVAGE_TYPE
5319 , DEPRN_LIMIT_TYPE
5320 , REDUCTION_RATE
5321 , REDUCE_ADDITION_FLAG
5322 , REDUCE_ADJUSTMENT_FLAG
5323 , REDUCE_RETIREMENT_FLAG
5324 , RECOGNIZE_GAIN_LOSS
5325 , RECAPTURE_RESERVE_FLAG
5326 , LIMIT_PROCEEDS_FLAG
5327 , TERMINAL_GAIN_LOSS
5328 , TRACKING_METHOD
5329 , EXCLUDE_FULLY_RSV_FLAG
5330 , EXCESS_ALLOCATION_OPTION
5331 , DEPRECIATION_OPTION
5332 , MEMBER_ROLLUP_FLAG
5333 , ALLOCATE_TO_FULLY_RSV_FLAG
5334 , ALLOCATE_TO_FULLY_RET_FLAG
5335 , TERMINAL_GAIN_LOSS_AMOUNT
5336 , CIP_COST
5337 , YTD_PROCEEDS
5338 , LTD_PROCEEDS
5339 , LTD_COST_OF_REMOVAL
5340 , EOFY_RESERVE
5341 , PRIOR_EOFY_RESERVE
5342 , EOP_ADJ_COST
5343 , EOP_FORMULA_FACTOR
5344 , EXCLUDE_PROCEEDS_FROM_BASIS
5345 , RETIREMENT_DEPRN_OPTION
5346 , TERMINAL_GAIN_LOSS_FLAG
5347 , SUPER_GROUP_ID
5348 , OVER_DEPRECIATE_OPTION
5349 , DISABLED_FLAG
5350 ) SELECT BOOK_TYPE_CODE
5351 , ASSET_ID
5352 , DATE_PLACED_IN_SERVICE
5353 , p_trans_rec.who_info.last_update_date -- DATE_EFFECTIVE
5354 , DEPRN_START_DATE
5355 , DEPRN_METHOD_CODE
5356 , LIFE_IN_MONTHS
5357 , RATE_ADJUSTMENT_FACTOR
5358 , t_new_adj_cost(i) -- ADJUSTED_COST
5359 , COST
5360 , ORIGINAL_COST
5361 , SALVAGE_VALUE
5362 , PRORATE_CONVENTION_CODE
5363 , PRORATE_DATE
5364 , COST_CHANGE_FLAG
5365 , ADJUSTMENT_REQUIRED_STATUS
5366 , CAPITALIZE_FLAG
5367 , RETIREMENT_PENDING_FLAG
5368 , DEPRECIATE_FLAG
5369 , p_trans_rec.who_info.last_update_date -- LAST_UPDATE_DATE
5370 , p_trans_rec.who_info.last_updated_by -- LAST_UPDATED_BY
5371 , t_thid(i) -- TRANSACTION_HEADER_ID_IN
5372 , ITC_AMOUNT_ID
5373 , ITC_AMOUNT
5374 , RETIREMENT_ID
5375 , TAX_REQUEST_ID
5376 , ITC_BASIS
5377 , BASIC_RATE
5378 , ADJUSTED_RATE
5379 , BONUS_RULE
5380 , CEILING_NAME
5381 , RECOVERABLE_COST
5382 , p_trans_rec.who_info.last_update_login -- LAST_UPDATE_LOGIN
5383 , ADJUSTED_CAPACITY
5384 , FULLY_RSVD_REVALS_COUNTER
5385 , IDLED_FLAG
5386 , PERIOD_COUNTER_CAPITALIZED
5387 , PERIOD_COUNTER_FULLY_RESERVED
5388 , PERIOD_COUNTER_FULLY_RETIRED
5389 , PRODUCTION_CAPACITY
5390 , REVAL_AMORTIZATION_BASIS
5391 , REVAL_CEILING
5392 , UNIT_OF_MEASURE
5393 , UNREVALUED_COST
5394 , ANNUAL_DEPRN_ROUNDING_FLAG
5395 , PERCENT_SALVAGE_VALUE
5396 , ALLOWED_DEPRN_LIMIT
5397 , ALLOWED_DEPRN_LIMIT_AMOUNT
5398 , PERIOD_COUNTER_LIFE_COMPLETE
5399 , ADJUSTED_RECOVERABLE_COST
5400 , ANNUAL_ROUNDING_FLAG
5401 , GLOBAL_ATTRIBUTE1
5402 , GLOBAL_ATTRIBUTE2
5403 , GLOBAL_ATTRIBUTE3
5404 , GLOBAL_ATTRIBUTE4
5405 , GLOBAL_ATTRIBUTE5
5406 , GLOBAL_ATTRIBUTE6
5407 , GLOBAL_ATTRIBUTE7
5408 , GLOBAL_ATTRIBUTE8
5409 , GLOBAL_ATTRIBUTE9
5410 , GLOBAL_ATTRIBUTE10
5411 , GLOBAL_ATTRIBUTE11
5412 , GLOBAL_ATTRIBUTE12
5413 , GLOBAL_ATTRIBUTE13
5414 , GLOBAL_ATTRIBUTE14
5415 , GLOBAL_ATTRIBUTE15
5416 , GLOBAL_ATTRIBUTE16
5417 , GLOBAL_ATTRIBUTE17
5418 , GLOBAL_ATTRIBUTE18
5419 , GLOBAL_ATTRIBUTE19
5420 , GLOBAL_ATTRIBUTE20
5421 , GLOBAL_ATTRIBUTE_CATEGORY
5422 , EOFY_ADJ_COST
5423 , EOFY_FORMULA_FACTOR
5424 , SHORT_FISCAL_YEAR_FLAG
5425 , CONVERSION_DATE
5426 , ORIGINAL_DEPRN_START_DATE
5427 , REMAINING_LIFE1
5428 , REMAINING_LIFE2
5429 , OLD_ADJUSTED_COST
5430 , FORMULA_FACTOR
5431 , GROUP_ASSET_ID
5432 , SALVAGE_TYPE
5433 , DEPRN_LIMIT_TYPE
5434 , REDUCTION_RATE
5435 , REDUCE_ADDITION_FLAG
5436 , REDUCE_ADJUSTMENT_FLAG
5437 , REDUCE_RETIREMENT_FLAG
5438 , RECOGNIZE_GAIN_LOSS
5439 , RECAPTURE_RESERVE_FLAG
5440 , LIMIT_PROCEEDS_FLAG
5441 , TERMINAL_GAIN_LOSS
5442 , TRACKING_METHOD
5443 , EXCLUDE_FULLY_RSV_FLAG
5444 , EXCESS_ALLOCATION_OPTION
5445 , DEPRECIATION_OPTION
5446 , MEMBER_ROLLUP_FLAG
5447 , ALLOCATE_TO_FULLY_RSV_FLAG
5448 , ALLOCATE_TO_FULLY_RET_FLAG
5449 , TERMINAL_GAIN_LOSS_AMOUNT
5450 , CIP_COST
5451 , YTD_PROCEEDS
5452 , LTD_PROCEEDS
5453 , LTD_COST_OF_REMOVAL
5454 , EOFY_RESERVE
5455 , PRIOR_EOFY_RESERVE
5456 , EOP_ADJ_COST
5457 , EOP_FORMULA_FACTOR
5458 , EXCLUDE_PROCEEDS_FROM_BASIS
5459 , RETIREMENT_DEPRN_OPTION
5460 , TERMINAL_GAIN_LOSS_FLAG
5461 , SUPER_GROUP_ID
5462 , OVER_DEPRECIATE_OPTION
5463 , DISABLED_FLAG
5464 FROM FA_BOOKS
5465 WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
5466
5467 end if; -- (p_mrc_sob_type_code = 'R')
5468
5469 -- flush the rows to the db
5470 l_adj.transaction_header_id := 0;
5471 l_adj.flush_adj_flag := TRUE;
5472 l_adj.leveling_flag := TRUE;
5473 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
5474
5475 if not FA_INS_ADJUST_PKG.faxinaj
5476 (l_adj,
5477 p_trans_rec.who_info.last_update_date,
5478 p_trans_rec.who_info.last_updated_by,
5479 p_trans_rec.who_info.last_update_login,
5480 p_log_level_rec) then
5481 raise allocate_err;
5482 end if;
5483
5484 -- As of now, following commit is commented out.
5485 -- It may be necessary in future if we see pl/sql table used in this function
5486 -- caused memory problem.
5487 -- COMMIT;
5488 END LOOP; -- MAIN OUTER LOOP
5489
5490 if (p_log_level_rec.statement_level) then
5491 fa_debug_pkg.add(l_calling_fn, 'End of Main Loop', p_reserve_amount - l_actual_rsv_total,p_log_level_rec);
5492 end if;
5493
5494 if (l_actual_rsv_total = p_reserve_amount) then
5495 if (p_log_level_rec.statement_level) then
5496 fa_debug_pkg.add(l_calling_fn, 'End of allocation', ' ',p_log_level_rec);
5497 end if;
5498
5499 return TRUE;
5500
5501 end if;
5502
5503
5504
5505 --
5506 -- If process reaches to this point, it means that reinstatement could not
5507 -- reinstate all amounts because member at the time of retirement does not exist
5508 -- or could not reinstate the reserve because some member become fully reserved.
5509 if (p_trans_rec.transaction_key = 'MS') and
5510 (p_mode = 'NORMAL') then
5511 -- Call Do_Allocation again to distribute remaining amounts
5512 if (p_log_level_rec.statement_level) then
5513 fa_debug_pkg.add(l_calling_fn, 'Recurrsive Call Do_Allocation', 'Calling...',p_log_level_rec);
5514 end if;
5515 --toru
5516 if not Do_Allocation(
5517 p_trans_rec => p_trans_rec,
5518 p_asset_hdr_rec => p_asset_hdr_rec,
5519 p_asset_fin_rec => p_asset_fin_rec,
5520 p_asset_deprn_rec_new => p_asset_deprn_rec_new, -- group new deprn rec
5521 p_period_rec => p_period_rec,
5522 p_reserve_amount => -1 *(p_reserve_amount - l_actual_rsv_total),
5523 p_mode => 'RECURR',
5524 p_mrc_sob_type_code => p_mrc_sob_type_code,
5525 p_calling_fn => l_calling_fn,
5526 p_log_level_rec => p_log_level_rec) then
5527 if (p_log_level_rec.statement_level) then
5528 fa_debug_pkg.add(l_calling_fn, 'Recurrsive Call to Do_Allocation', 'Failed',p_log_level_rec);
5529 end if;
5530
5531 raise allocate_err;
5532 end if;
5533
5534 return TRUE;
5535 end if;
5536
5537 -- This portion of code is necessary to distribute fraction
5538 -- produced during allocation process above.
5539 -- For performance reason (to avoid issuing sql) reuse what's in pl/sql table now
5540 -- If there is a difference between
5541 if (p_reserve_amount <> l_actual_rsv_total) then
5542
5543 l_reallocate_amount := p_reserve_amount - l_actual_rsv_total;
5544
5545 -- First, if it needs to take out some reserve, then do that against
5546 -- last processed asset. If not, then first go through the assets in
5547 -- pl/sql table to check for the same. if not done yet, hit db to do the same.
5548 if (p_reserve_amount < 0 or
5549 p_reserve_amount < l_actual_rsv_total) then
5550
5551 -- Need to backout some reserve so do that from last processed asset
5552
5553 if (p_mrc_sob_type_code = 'R') then
5554 UPDATE FA_ADJUSTMENTS_MRC_V
5555 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5556 WHERE ASSET_ID = l_last_asset_id
5557 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5558 AND TRANSACTION_HEADER_ID = l_last_thid
5559 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5560 FROM FA_ADJUSTMENTS_MRC_V
5561 WHERE ASSET_ID = l_last_asset_id
5562 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5563 AND TRANSACTION_HEADER_ID = l_last_thid);
5564
5565 UPDATE FA_BOOKS_SUMMARY_MRC_V
5566 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5567 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5568 , ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE
5569 WHERE ASSET_ID = l_last_asset_id
5570 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5571 AND PERIOD_COUNTER = p_period_rec.period_counter
5572 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5573
5574
5575 UPDATE FA_BOOKS_MRC_V
5576 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5577 WHERE TRANSACTION_HEADER_ID_IN = l_last_thid;
5578 else
5579 UPDATE FA_ADJUSTMENTS
5580 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5581 WHERE ASSET_ID = l_last_asset_id
5582 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5583 AND TRANSACTION_HEADER_ID = l_last_thid
5584 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5585 FROM FA_ADJUSTMENTS
5586 WHERE ASSET_ID = l_last_asset_id
5587 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5588 AND TRANSACTION_HEADER_ID = l_last_thid);
5589
5590 UPDATE FA_BOOKS_SUMMARY
5591 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5592 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5593 , ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE
5594 WHERE ASSET_ID = l_last_asset_id
5595 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5596 AND PERIOD_COUNTER = p_period_rec.period_counter
5597 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5598
5599
5600 UPDATE FA_BOOKS
5601 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5602 WHERE TRANSACTION_HEADER_ID_IN = l_last_thid;
5603 end if; -- (p_mrc_sob_type_code = 'R')
5604 else
5605
5606 -- Need to add some reserve to non fully reserved asset(s).
5607 -- Check to see if such assets exists in data still exists in pl/sql table
5608 FOR i in t_asset_id.last..t_asset_id.first LOOP
5609
5610 if (t_adjusted_recoverable_cost(i) > t_new_deprn_reserve(i)) then
5611
5612 if (t_adjusted_recoverable_cost(i) - t_new_deprn_reserve(i) >= l_reallocate_amount) then
5613
5614 -- This asset can take all hit
5615
5616 if (p_mrc_sob_type_code = 'R') then
5617 UPDATE FA_ADJUSTMENTS_MRC_V
5618 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5619 WHERE ASSET_ID = t_asset_id(i)
5620 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5621 AND TRANSACTION_HEADER_ID = t_thid(i)
5622 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5623 FROM FA_ADJUSTMENTS_MRC_V
5624 WHERE ASSET_ID = t_asset_id(i)
5625 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5626 AND TRANSACTION_HEADER_ID = t_thid(i));
5627
5628 UPDATE FA_BOOKS_SUMMARY_MRC_V
5629 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5630 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5631 , ADJUSTED_COST = ADJUSTED_RECOVERABLE_COST - DEPRN_RESERVE
5632 WHERE ASSET_ID = t_asset_id(i)
5633 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5634 AND PERIOD_COUNTER = p_period_rec.period_counter
5635 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5636
5637
5638 UPDATE FA_BOOKS_MRC_V
5639 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5640 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5641 else
5642 UPDATE FA_ADJUSTMENTS
5643 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5644 WHERE ASSET_ID = t_asset_id(i)
5645 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5646 AND TRANSACTION_HEADER_ID = t_thid(i)
5647 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5648 FROM FA_ADJUSTMENTS
5649 WHERE ASSET_ID = t_asset_id(i)
5650 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5651 AND TRANSACTION_HEADER_ID = t_thid(i));
5652
5653 UPDATE FA_BOOKS_SUMMARY
5654 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5655 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5656 , ADJUSTED_COST = ADJUSTED_RECOVERABLE_COST - DEPRN_RESERVE
5657 WHERE ASSET_ID = t_asset_id(i)
5658 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5659 AND PERIOD_COUNTER = p_period_rec.period_counter
5660 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5661
5662
5663 UPDATE FA_BOOKS
5664 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5665 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5666
5667 end if; -- (p_mrc_sob_type_code = 'R')
5668
5669 -- Should be no more reserve to allocate
5670 l_reallocate_amount := 0;
5671
5672 else
5673
5674 -- This asset cannot take all hit but take some until the asset become
5675 -- fully reserved. After this, still need to look for another asset to allocate
5676 -- remaining excess amount.
5677
5678 if (p_mrc_sob_type_code = 'R') then
5679 UPDATE FA_ADJUSTMENTS_MRC_V
5680 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_recoverable_cost(i) - t_new_deprn_reserve(i)
5681 WHERE ASSET_ID = t_asset_id(i)
5682 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5683 AND TRANSACTION_HEADER_ID = t_thid(i)
5684 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5685 FROM FA_ADJUSTMENTS_MRC_V
5686 WHERE ASSET_ID = t_asset_id(i)
5687 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5688 AND TRANSACTION_HEADER_ID = t_thid(i));
5689
5690 UPDATE FA_BOOKS_SUMMARY_MRC_V
5691 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_recoverable_cost(i) -
5692 t_new_deprn_reserve(i)
5693 , DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
5694 , ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5695 WHERE ASSET_ID = t_asset_id(i)
5696 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5697 AND PERIOD_COUNTER = p_period_rec.period_counter
5698 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5699
5700
5701 UPDATE FA_BOOKS_MRC_V
5702 SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5703 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5704 else
5705 UPDATE FA_ADJUSTMENTS
5706 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_recoverable_cost(i) - t_new_deprn_reserve(i)
5707 WHERE ASSET_ID = t_asset_id(i)
5708 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5709 AND TRANSACTION_HEADER_ID = t_thid(i)
5710 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5711 FROM FA_ADJUSTMENTS
5712 WHERE ASSET_ID = t_asset_id(i)
5713 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5714 AND TRANSACTION_HEADER_ID = t_thid(i));
5715
5716 UPDATE FA_BOOKS_SUMMARY
5717 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_recoverable_cost(i) -
5718 t_new_deprn_reserve(i)
5719 , DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
5720 , ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5721 WHERE ASSET_ID = t_asset_id(i)
5722 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5723 AND PERIOD_COUNTER = p_period_rec.period_counter
5724 AND TRANSACTION_HEADER_ID_OUT IS NULL;
5725
5726
5727 UPDATE FA_BOOKS
5728 SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5729 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5730
5731 end if; -- (p_mrc_sob_type_code = 'R')
5732
5733 l_reallocate_amount := l_reallocate_amount - (t_adjusted_recoverable_cost(i) -
5734 t_new_deprn_reserve(i));
5735 end if; -- (t_adjusted_recoverable_cost(i) - t_new_deprn_reserve(i) >= l_reallocate_amount)
5736
5737 if l_reallocate_amount = 0 then
5738 EXIT;
5739 end if;
5740
5741 end if; -- if (t_adjusted_recoverable_cost(i) > t_new_deprn_reserve(i)) then
5742
5743 END LOOP; -- FOR i in t_asset_id.last..t_asset_id.first
5744
5745
5746 -- Need to hit db to allocate excess amount if any at this point.
5747 -- Basically, do the same thing done in above loop but this time against
5748 -- data in db.
5749 if (l_reallocate_amount <> 0) then
5750
5751 t_thid.delete;
5752 t_asset_id.delete;
5753 t_adjusted_cost.delete;
5754
5755 if (p_mrc_sob_type_code = 'R') then
5756 OPEN C_MC_DIST_EXCESS;
5757 else
5758 OPEN C_DIST_EXCESS;
5759 end if;
5760
5761 LOOP -- *** DIST EXCESS OUTER LOOP **
5762
5763 if (l_reallocate_amount = 0) then
5764 if (p_mrc_sob_type_code = 'R') then
5765 CLOSE C_MC_DIST_EXCESS;
5766 else
5767 CLOSE C_DIST_EXCESS;
5768 end if;
5769 EXIT;
5770 end if;
5771
5772 if (p_mrc_sob_type_code = 'R') then
5773 FETCH C_MC_DIST_EXCESS BULK COLLECT INTO t_asset_id
5774 , t_thid
5775 , t_adjusted_cost LIMIT l_limit;
5776 else
5777 FETCH C_DIST_EXCESS BULK COLLECT INTO t_asset_id
5778 , t_thid
5779 , t_adjusted_cost LIMIT l_limit;
5780 end if;
5781
5782 if (t_asset_id.count = 0) then
5783 if (p_mrc_sob_type_code = 'R') then
5784 CLOSE C_MC_DIST_EXCESS;
5785 else
5786 CLOSE C_DIST_EXCESS;
5787 end if;
5788 EXIT;
5789 end if;
5790
5791 FOR i in t_asset_id.first..t_asset_id.last LOOP
5792
5793 if (t_adjusted_cost(i) >= l_reallocate_amount) then
5794
5795 if (p_mrc_sob_type_code = 'R') then
5796 UPDATE FA_ADJUSTMENTS_MRC_V
5797 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5798 WHERE ASSET_ID = t_asset_id(i)
5799 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5800 AND TRANSACTION_HEADER_ID = t_thid(i)
5801 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5802 FROM FA_ADJUSTMENTS_MRC_V
5803 WHERE ASSET_ID = t_asset_id(i)
5804 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5805 AND TRANSACTION_HEADER_ID = t_thid(i));
5806
5807 UPDATE FA_BOOKS_SUMMARY_MRC_V
5808 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5809 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5810 , ADJUSTED_COST = ADJUSTED_RECOVERABLE_COST - DEPRN_RESERVE
5811 WHERE ASSET_ID = t_asset_id(i)
5812 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5813 AND PERIOD_COUNTER = p_period_rec.period_counter
5814 AND TRANSACTION_HEADER_ID_OUT is null;
5815
5816
5817 UPDATE FA_BOOKS_MRC_V
5818 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5819 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5820 else
5821 UPDATE FA_ADJUSTMENTS
5822 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
5823 WHERE ASSET_ID = t_asset_id(i)
5824 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5825 AND TRANSACTION_HEADER_ID = t_thid(i)
5826 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5827 FROM FA_ADJUSTMENTS
5828 WHERE ASSET_ID = t_asset_id(i)
5829 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5830 AND TRANSACTION_HEADER_ID = t_thid(i));
5831
5832 UPDATE FA_BOOKS_SUMMARY
5833 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reallocate_amount
5834 , DEPRN_RESERVE = DEPRN_RESERVE + l_reallocate_amount
5835 , ADJUSTED_COST = ADJUSTED_RECOVERABLE_COST - DEPRN_RESERVE
5836 WHERE ASSET_ID = t_asset_id(i)
5837 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5838 AND PERIOD_COUNTER = p_period_rec.period_counter
5839 AND TRANSACTION_HEADER_ID_OUT is null;
5840
5841
5842 UPDATE FA_BOOKS
5843 SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
5844 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5845 end if;
5846
5847 l_reallocate_amount := 0;
5848
5849 else
5850
5851 if (p_mrc_sob_type_code = 'R') then
5852 UPDATE FA_ADJUSTMENTS_MRC_V
5853 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
5854 WHERE ASSET_ID = t_asset_id(i)
5855 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5856 AND TRANSACTION_HEADER_ID = t_thid(i)
5857 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5858 FROM FA_ADJUSTMENTS_MRC_V
5859 WHERE ASSET_ID = t_asset_id(i)
5860 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5861 AND TRANSACTION_HEADER_ID = t_thid(i));
5862
5863 UPDATE FA_BOOKS_SUMMARY_MRC_V
5864 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
5865 , DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
5866 , ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5867 WHERE ASSET_ID = t_asset_id(i)
5868 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5869 AND PERIOD_COUNTER = p_period_rec.period_counter;
5870
5871
5872 UPDATE FA_BOOKS_MRC_V
5873 SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5874 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5875 else
5876 UPDATE FA_ADJUSTMENTS
5877 SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
5878 WHERE ASSET_ID = t_asset_id(i)
5879 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5880 AND TRANSACTION_HEADER_ID = t_thid(i)
5881 AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
5882 FROM FA_ADJUSTMENTS
5883 WHERE ASSET_ID = t_asset_id(i)
5884 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5885 AND TRANSACTION_HEADER_ID = t_thid(i));
5886
5887 UPDATE FA_BOOKS_SUMMARY
5888 SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
5889 , DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
5890 , ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5891 WHERE ASSET_ID = t_asset_id(i)
5892 AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
5893 AND PERIOD_COUNTER = p_period_rec.period_counter;
5894
5895
5896 UPDATE FA_BOOKS
5897 SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
5898 WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
5899 end if;
5900
5901 l_reallocate_amount := l_reallocate_amount - t_adjusted_cost(i);
5902 end if; -- (t_adjusted_recoverable_cost(i) - t_new_deprn_reserve(i) >= l_reallocate_amount)
5903
5904 if (l_reallocate_amount = 0) then
5905 EXIT;
5906 end if;
5907
5908
5909 END LOOP; -- FOR i in t_asset_id.first..t_asset_id.last
5910
5911 END LOOP; -- *** DIST EXCESS OUTER LOOP ***
5912
5913 end if;
5914
5915 end if; -- (p_reserve_amount < 0 or
5916
5917 end if; -- (p_reserve_amount <> l_actual_rsv_total)
5918
5919
5920 if (p_log_level_rec.statement_level) then
5921 fa_debug_pkg.add(l_calling_fn, 'End', p_reserve_amount,p_log_level_rec);
5922 end if;
5923
5924 return true;
5925
5926 EXCEPTION
5927 WHEN OTHERS THEN
5928 if (p_log_level_rec.statement_level) then
5929 fa_debug_pkg.add(l_calling_fn, 'l_loc', l_loc,p_log_level_rec);
5930 fa_debug_pkg.add(l_calling_fn, 'End', 'Failed'||':'||sqlerrm,p_log_level_rec);
5931 end if;
5932
5933 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
5934 ,p_log_level_rec => p_log_level_rec);
5935 return false;
5936
5937 END Do_Allocation;
5938
5939
5940 END FA_RETIREMENT_PVT;