1 PACKAGE BODY FA_GROUP_RECLASS2_PVT AS
2 /* $Header: FAVGRECB.pls 120.51.12010000.4 2009/02/21 00:38:11 bmaddine ship $ */
3
4 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
6 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
7
8 FUNCTION do_group_reclass
9 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
10 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
11 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
12 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
13 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
14 px_src_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
15 px_src_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
16 p_src_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
17 p_src_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
18 p_src_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
19 px_dest_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
20 px_dest_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
21 p_dest_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
22 p_dest_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
23 p_dest_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
24 p_asset_fin_rec_old IN FA_API_TYPES.asset_fin_rec_type,
25 p_asset_fin_rec_adj IN FA_API_TYPES.asset_fin_rec_type,
26 p_asset_fin_rec_new IN FA_API_TYPES.asset_fin_rec_type,
27 p_asset_deprn_rec_old IN FA_API_TYPES.asset_deprn_rec_type,
28 p_asset_deprn_rec_adj IN FA_API_TYPES.asset_deprn_rec_type,
29 p_asset_deprn_rec_new IN FA_API_TYPES.asset_deprn_rec_type,
30 px_group_reclass_options_rec IN OUT NOCOPY FA_API_TYPES.group_reclass_options_rec_type,
31 p_period_rec IN FA_API_TYPES.period_rec_type,
32 p_mrc_sob_type_code IN VARCHAR2,
33 p_calling_fn IN VARCHAR2,
34 p_log_level_rec IN fa_api_types.log_level_rec_type default null
35 ) RETURN BOOLEAN IS
36
37
38 l_src_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
39 l_src_asset_desc_rec fa_api_types.asset_desc_rec_type;
40 l_src_asset_type_rec fa_api_types.asset_type_rec_type;
41 l_src_asset_cat_rec fa_api_types.asset_cat_rec_type;
42 l_src_asset_fin_rec_old fa_api_types.asset_fin_rec_type;
43 l_src_asset_fin_rec_adj fa_api_types.asset_fin_rec_type;
44 l_src_asset_fin_rec_new fa_api_types.asset_fin_rec_type;
45 l_src_asset_deprn_rec_old fa_api_types.asset_deprn_rec_type;
46 l_src_asset_deprn_rec_adj fa_api_types.asset_deprn_rec_type;
47 l_src_asset_deprn_rec_new fa_api_types.asset_deprn_rec_type;
48
49 l_dest_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
50 l_dest_asset_desc_rec fa_api_types.asset_desc_rec_type;
51 l_dest_asset_type_rec fa_api_types.asset_type_rec_type;
52 l_dest_asset_cat_rec fa_api_types.asset_cat_rec_type;
53 l_dest_asset_fin_rec_old fa_api_types.asset_fin_rec_type;
54 l_dest_asset_fin_rec_adj fa_api_types.asset_fin_rec_type;
55 l_dest_asset_fin_rec_new fa_api_types.asset_fin_rec_type;
56 l_dest_asset_deprn_rec_old fa_api_types.asset_deprn_rec_type;
57 l_dest_asset_deprn_rec_adj fa_api_types.asset_deprn_rec_type;
58 l_dest_asset_deprn_rec_new fa_api_types.asset_deprn_rec_type;
59
60 l_inv_trans_rec fa_api_types.inv_trans_rec_type;
61 l_null_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
62
63 l_transaction_date date;
64 l_group_reclass_code varchar2(20);
65
66 l_trx_reference_id number;
67 l_return_status boolean;
68 l_rowid varchar2(200);
69
70 l_calling_fn VARCHAR2(35) := 'fa_group_reclass_pvt.do_grp_reclass';
71
72 -- BUG# 6936546
73 l_api_version NUMBER := 1.0;
74 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
75 l_commit VARCHAR2(1) := FND_API.G_FALSE;
76 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
77 l_return_status2 VARCHAR2(1);
78 l_msg_count NUMBER;
79 l_msg_data VARCHAR2(2000);
80 l_trans_rec FA_API_TYPES.trans_rec_type;
81 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type :=
82 p_asset_hdr_rec;
83 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
84
85 l_distribution_id num_tbl;
86 l_units_assigned num_tbl;
87 l_code_combination_id num_tbl;
88 l_location_id num_tbl;
89 l_assigned_to num_tbl;
90
91 l_transfer_amount NUMBER; --Bug6987743
92
93 cursor c_member_dists (p_asset_id number) is
94 select distribution_id,
95 units_assigned,
96 code_combination_id,
97 location_id,
98 assigned_to
99 from fa_distribution_history
100 where asset_id = p_asset_id
101 and transaction_header_id_out is null;
102
103 --Bug6987743: Getting previously transfered reserve
104 cursor c_get_reserve is
105 select nvl(reserve_transfer_amount, 0)
106 from fa_trx_references
107 where dest_asset_id = l_src_asset_hdr_rec.asset_id
108 and member_asset_id = p_asset_hdr_rec.asset_id
109 and book_type_code = p_asset_hdr_rec.book_type_code
110 order by trx_reference_id desc;
111
112 -- used for faxinaj calls
113 l_rsv_adj FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
114
115 grp_rec_err exception;
116
117 BEGIN
118
119 if (p_log_level_rec.statement_level) then
120 fa_debug_pkg.add(l_calling_fn, 'src exp amount', px_group_reclass_options_rec.source_exp_amount, p_log_level_rec);
121 fa_debug_pkg.add(l_calling_fn, 'dest exp amount', px_group_reclass_options_rec.destination_exp_amount, p_log_level_rec);
122 fa_debug_pkg.add(l_calling_fn, 'reserve amount', px_group_reclass_options_rec.reserve_amount, p_log_level_rec);
123 fa_debug_pkg.add(l_calling_fn, 'p_trans_rec.amort_start_date', p_trans_rec.amortization_start_date, p_log_level_rec);
124 end if;
125
126 -- note that adjustments to both expense and reserve will be handled
127 -- here in and not in the adjustment api/calc engine (via faadjust)
128 -- note this code will be bypassed by a cominbation of the following:
129 --
130 -- trx_type = 'GROUP RECLASS'
131 -- trx_subtype = ''
132 --
133
134 -- note that we need to investigate / determine transaction subtype and track member impacts!!!!
135 -- including merging this into the adjustment API
136 --
137 -- how to handle amort start / trx_date , etc on the member in all three scenarios
138 -- - the transaction date currently must be equal among all three
139 -- - validate / prevent it exceeding the dpis of member and or group
140 -- - should be able to "expense" the adjustment on the member when
141 -- pulling it out of a group (per last discussion on UI)
142 -- thus when amort comes in as expense, we need to
143 -- set to amort for group leave as expensed for member
144
145
146 -- use this
147 -- *** VERIFY THE TWO GROUP TRX APP CALLS - they may too expensive
148 -- though some form of locking is required ***
149 -- now doen in the calling public api
150
151 -- verify a change in group is occurring / otherwise error out
152 -- store the "reclass code" for easy reference at various
153 -- points in the api
154 if (p_log_level_rec.statement_level) then
155 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_old.group_asset_id', p_asset_fin_rec_old.group_asset_id, p_log_level_rec);
156 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.group_asset_id', p_asset_fin_rec_adj.group_asset_id, p_log_level_rec);
157 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_new.group_asset_id', p_asset_fin_rec_new.group_asset_id, p_log_level_rec);
158 end if;
159
160
161 if (nvl(p_asset_fin_rec_old.group_asset_id, -99) = nvl(p_asset_fin_rec_new.group_asset_id, -99)) then
162
163 raise grp_rec_err;
164 elsif (p_asset_fin_rec_old.group_asset_id is not null and
165 p_asset_fin_rec_new.group_asset_id is not null) then
166 l_group_reclass_code := 'GRP-GRP';
167 elsif (p_asset_fin_rec_old.group_asset_id is not null) then
168 l_group_reclass_code := 'GRP-NONE';
169 else
170 l_group_reclass_code := 'NONE-GRP';
171 end if;
172
173 if (p_log_level_rec.statement_level) then
174 fa_debug_pkg.add(l_calling_fn, 'reclass code', l_group_reclass_code, p_log_level_rec);
175 end if;
176
177 -- validate intercompany impacts before doing anything else
178 if (l_group_reclass_code <> 'GRP-NONE' and
179 nvl(fa_cache_pkg.fazcbc_record.allow_interco_group_flag, 'N') <> 'Y') then
180
181 if not fa_interco_pvt.validate_grp_interco
182 (p_asset_hdr_rec => p_asset_hdr_rec,
183 p_trans_rec => p_trans_rec,
184 p_asset_type_rec => p_asset_type_rec,
185 p_group_asset_id => px_dest_asset_hdr_rec.asset_id,
186 p_asset_dist_tbl => l_null_dist_tbl,
187 p_calling_fn => l_calling_fn,
188 p_log_level_rec => p_log_level_rec ) then
189 raise grp_rec_err;
190 end if;
191 end if;
192
193 -- do not allow reclasses from or to none in conjunction with manual amounts
194 -- this is still questionable - refernece open issues as we could
195 -- do this by performing two calculations/insert branches:
196 -- 1) inserting the manual amount
197 -- 2) taking the difference between oracle's calc and the entered amount
198 --
199 -- this would allow the effective "backdating" of a group reclass
200 -- where the amount entered is < reserve (NONE->GRP)
201
202
203 -- if ((l_group_reclass_code = 'GRP-NONE' or l_group_reclass_code = 'NONE-GRP') and
204 -- (p_group_reclass_options_rec.group_reclass_type = 'MANUAL-EXP' or
205 -- p_group_reclass_options_rec.group_reclass_type = 'MANUAL-RES')) then
206 -- raise grp_rec_err;
207 -- end if;
208
209 -- Do not allow amort date is not either DPIS of member asset to be reclassified
210 -- or current date in case of the group asset id defined as Year End Balance
211 -- Method.
212
213 -- For Source Group
214 -- Populate Subtract Ytd Flag
215 if not fa_cache_pkg.fazccmt(X_method => p_asset_fin_rec_old.deprn_method_code,
216 X_life => p_asset_fin_rec_old.life_in_months,
217 p_log_level_rec => p_log_level_rec) then
218 raise grp_rec_err;
219 end if;
220
221 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' and
222 p_trans_rec.amortization_start_date <> p_asset_fin_rec_old.date_placed_in_service and
223 p_trans_rec.amortization_start_date < p_period_rec.calendar_period_open_date then
224 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
225 name => '***FA_AMORT_DATE_SUB***');
226 return false;
227 raise grp_rec_err;
228 end if;
229
230 -- For Target Group
231 -- Populate Subtract Ytd Flag
232 if not fa_cache_pkg.fazccmt(X_method => p_asset_fin_rec_new.deprn_method_code,
233 X_life => p_asset_fin_rec_new.life_in_months) then
234 raise grp_rec_err;
235 end if;
236
237 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' and
238 p_trans_rec.amortization_start_date <> p_asset_fin_rec_old.date_placed_in_service and
239 p_trans_rec.amortization_start_date < p_period_rec.calendar_period_open_date then
240 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
241 name => '***FA_AMORT_DATE_SUB***');
242 raise grp_rec_err;
243 end if;
244
245 -- now process the group(s) and or deprn / reserve impacts
246 --
247 -- open issues in terms of sequence when you are combining
248 -- a cost change with a change in group in the same transaction
249 --
250 -- option1: backout old.cost only
251 -- option2: perform adj for cost change, then backout the total new cost
252
253 -- Bug#3737670
254 -- get the old fin and deprn information of destination group
255
256 if (p_log_level_rec.statement_level) then
257 fa_debug_pkg.add(l_calling_fn, 'px_dest_asset_hdr_rec.asset_id:book_type_code',
258 px_dest_asset_hdr_rec.asset_id||':'|| px_dest_asset_hdr_rec.book_type_code, p_log_level_rec);
259 end if;
260
261 if l_group_reclass_code <> 'GRP-NONE' then
262 if not FA_UTIL_PVT.get_asset_fin_rec
263 (p_asset_hdr_rec => px_dest_asset_hdr_rec,
264 px_asset_fin_rec => l_dest_asset_fin_rec_old,
265 p_transaction_header_id => NULL,
266 p_mrc_sob_type_code => p_mrc_sob_type_code,
267 p_log_level_rec => p_log_level_rec
268 ) then raise grp_rec_err;
269 end if;
270 end if;
271
272
273 ------------
274 -- SOURCE --
275 ------------
276
277 if (p_log_level_rec.statement_level) then
278 fa_debug_pkg.add(l_calling_fn, 'processing','source asset', p_log_level_rec);
279 end if;
280
281 -- For Source Group
282 -- Populate Subtract Ytd Flag
283 if not fa_cache_pkg.fazccmt(X_method => p_asset_fin_rec_old.deprn_method_code,
284 X_life => p_asset_fin_rec_old.life_in_months,
285 p_log_level_rec => p_log_level_rec) then
286 raise grp_rec_err;
287 end if;
288
289 -- If the asset was moved out of a group, deduct the asset's cost from the old group
290 if p_asset_fin_rec_old.group_asset_id is not null then
291
292 if (p_log_level_rec.statement_level) then
293 fa_debug_pkg.add(l_calling_fn, 'source is','group', p_log_level_rec);
294 end if;
295
296 l_src_asset_hdr_rec := px_src_asset_hdr_rec;
297 l_src_asset_desc_rec := p_src_asset_desc_rec;
298 l_src_asset_type_rec := p_src_asset_type_rec;
299 l_src_asset_cat_rec := p_src_asset_cat_rec;
300
301 -- load the delta cost for the group with the -ve cost of the member
302 -- NOTE: iniital test we'll use the od cost and not increment the
303 -- source group's cost first in the case of a combo cost/reclass
304
305 if (p_asset_type_rec.asset_type = 'CIP') then
306 l_src_asset_fin_rec_adj.cost := -(p_asset_fin_rec_old.cost - p_asset_fin_rec_old.cip_cost);
307 l_src_asset_fin_rec_adj.cip_cost := -p_asset_fin_rec_old.cip_cost;
308 else
309 l_src_asset_fin_rec_adj.cost := -p_asset_fin_rec_old.cost;
310 end if;
311
312 if (p_mrc_sob_type_code <> 'R') then
313
314 -- set up the group recs
315 px_src_trans_rec := p_trans_rec;
316 px_src_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
317 px_src_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
318 px_src_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
319 px_src_trans_rec.transaction_subtype := 'AMORTIZED';
320 px_src_trans_rec.transaction_key := 'GC';
321 px_src_trans_rec.transaction_date_entered := p_trans_rec.amortization_start_date;
322
323 select fa_transaction_headers_s.nextval
324 into px_src_trans_rec.transaction_header_id
325 from dual;
326
327 end if;
328
329 -- get the old fin and deprn information
330 if not FA_UTIL_PVT.get_asset_fin_rec
331 (p_asset_hdr_rec => px_src_asset_hdr_rec,
332 px_asset_fin_rec => l_src_asset_fin_rec_old,
333 p_transaction_header_id => NULL,
334 p_mrc_sob_type_code => p_mrc_sob_type_code,
335 p_log_level_rec => p_log_level_rec
336 ) then raise grp_rec_err;
337 end if;
338
339 --HH Validate disabled_flag
340 --Reject transaction if src group is disabled
341 if not FA_ASSET_VAL_PVT.validate_disabled_flag
342 (p_group_asset_id => l_src_asset_fin_rec_old.group_asset_id,
343 p_book_type_code => px_src_asset_hdr_rec.book_type_code,
344 p_old_flag => l_src_asset_fin_rec_old.disabled_flag,
345 p_new_flag => l_src_asset_fin_rec_old.disabled_flag,
346 p_log_level_rec => p_log_level_rec
347 ) then
348 raise grp_rec_err;
349 end if; --End HH
350
351 if not FA_UTIL_PVT.get_asset_deprn_rec
352 (p_asset_hdr_rec => px_src_asset_hdr_rec,
353 px_asset_deprn_rec => l_src_asset_deprn_rec_old,
354 p_period_counter => NULL,
355 p_mrc_sob_type_code => p_mrc_sob_type_code,
356 p_log_level_rec => p_log_level_rec
357 ) then raise grp_rec_err;
358 end if;
359
360 -- If the deprn basis rule is defined as Year End Balance type,
361 -- new adjusted cost must be calculated based on the entered eofy_reserve
362 -- Need to set adj.eofy_reserve
363 -- if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
364 l_src_asset_fin_rec_adj.eofy_reserve := (-1)*nvl(px_group_reclass_options_rec.source_eofy_reserve,0);
365 -- end if;
366
367 if (p_log_level_rec.statement_level) then
368 fa_debug_pkg.add(l_calling_fn, 'l_src_asset_fin_rec_old.eofy_reserve',
369 l_src_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
370 fa_debug_pkg.add(l_calling_fn, 'l_src_asset_fin_rec_adj.eofy_reserve',
371 l_src_asset_fin_rec_adj.eofy_reserve, p_log_level_rec);
372 end if;
373
374 -- now process the adjustment on the group
375 -- raf / adjusted_cost to be updated later
376
377 if (p_log_level_rec.statement_level) then
378 fa_debug_pkg.add(l_calling_fn, 'source is','group', p_log_level_rec);
379 end if;
380
381
382 if (p_log_level_rec.statement_level) then
383 fa_debug_pkg.add(l_calling_fn, 'amort start date2',p_trans_rec.amortization_start_date , p_log_level_rec);
384 fa_debug_pkg.add(l_calling_fn, 'amort start date2a',px_src_trans_rec.amortization_start_date , p_log_level_rec);
385 end if;
386
387 --
388 -- Initialize Member Tables
389 --
390 FA_AMORT_PVT.initMemberTable;
391
392 --
393 -- Bug3537474: pass reserve entered by user to pass it to faxama.
394 --
395 if px_group_reclass_options_rec.group_reclass_type = 'MANUAL' then
396 l_src_asset_deprn_rec_adj.deprn_reserve := -1 * px_group_reclass_options_rec.reserve_amount;
397
398 --Bug6987743:Following make sure to pass reserve transfered to faxama so
399 --that previously transferred amount will be taken out form Books Summary table.
400 elsif px_group_reclass_options_rec.group_reclass_type = 'CALC' and
401 (l_src_asset_fin_rec_old.tracking_method = 'ALLOCATE' or
402 (l_src_asset_fin_rec_old.tracking_method = 'CALCULATE' and
403 nvl(l_src_asset_fin_rec_old.member_rollup_flag, 'N') = 'N')) then
404
405 OPEN c_get_reserve;
406 FETCH c_get_reserve INTO l_transfer_amount;
407 CLOSE c_get_reserve;
408
409 l_src_asset_deprn_rec_adj.deprn_reserve := -1 * nvl(l_transfer_amount, 0);
410
411 if (p_log_level_rec.statement_level) then
412 fa_debug_pkg.add(l_calling_fn, 'Getting previously transferred rsv', l_transfer_amount, p_log_level_rec);
413 fa_debug_pkg.add(l_calling_fn, 'l_src_asset_deprn_rec_adj.deprn_reserve',
414 l_src_asset_deprn_rec_adj.deprn_reserve, p_log_level_rec);
415 end if;
416 end if;
417
418 if not FA_ADJUSTMENT_PVT.do_adjustment
419 (px_trans_rec => px_src_trans_rec,
420 px_asset_hdr_rec => px_src_asset_hdr_rec,
421 p_asset_desc_rec => p_src_asset_desc_rec,
422 p_asset_type_rec => p_src_asset_type_rec,
423 p_asset_cat_rec => p_src_asset_cat_rec,
424 p_asset_fin_rec_old => l_src_asset_fin_rec_old,
425 p_asset_fin_rec_adj => l_src_asset_fin_rec_adj,
426 x_asset_fin_rec_new => l_src_asset_fin_rec_new,
427 p_inv_trans_rec => l_inv_trans_rec,
428 p_asset_deprn_rec_old => l_src_asset_deprn_rec_old,
429 p_asset_deprn_rec_adj => l_src_asset_deprn_rec_adj,
430 x_asset_deprn_rec_new => l_src_asset_deprn_rec_new,
431 p_period_rec => p_period_rec,
432 p_reclassed_asset_id => p_asset_hdr_rec.asset_id,
433 p_reclass_src_dest => 'SOURCE',
434 p_reclassed_asset_dpis => p_asset_fin_rec_old.date_placed_in_service,
435 p_mrc_sob_type_code => p_mrc_sob_type_code,
436 p_group_reclass_options_rec => px_group_reclass_options_rec,
437 p_calling_fn => l_calling_fn,
438 p_log_level_rec => p_log_level_rec
439 ) then
440 raise grp_rec_err;
441 end if; -- do_adjustment
442
443
444 if px_group_reclass_options_rec.group_reclass_type = 'MANUAL' then
445
446 -- BUG# 2683922, issue #2
447 -- delete any expense rows taken in the above call from
448 -- calc_fin_info. currently, there's no easy way to
449 -- distinguish whether the group adjustment was from
450 -- a group reclass / member adjustment, etc inside
451 -- calc_fin_info when calling faxama/faxiat
452
453 if (p_mrc_sob_type_code = 'R') then
454 delete from fa_adjustments_mrc_v
455 where asset_id = px_src_asset_hdr_rec.asset_id
456 and book_type_code = px_src_asset_hdr_rec.book_type_code
457 and transaction_header_id = px_src_trans_rec.transaction_header_id
458 and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
459 and source_type_code = 'DEPRECIATION';
460 else
461 delete from fa_adjustments
462 where asset_id = px_src_asset_hdr_rec.asset_id
463 and book_type_code = px_src_asset_hdr_rec.book_type_code
464 and transaction_header_id = px_src_trans_rec.transaction_header_id
465 and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
466 and source_type_code = 'DEPRECIATION';
467 end if;
468 end if;
469
470 if (p_log_level_rec.statement_level) then
471 fa_debug_pkg.add(l_calling_fn, 'amort start date2.5',p_trans_rec.amortization_start_date , p_log_level_rec);
472 fa_debug_pkg.add(l_calling_fn, 'amort start date2.5a',px_src_trans_rec.amortization_start_date , p_log_level_rec);
473 fa_debug_pkg.add(l_calling_fn, 'l_src_asset_fin_rec_new.eofy_reserve',l_src_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
474 end if;
475
476 l_src_asset_deprn_rec_new := l_src_asset_deprn_rec_old;
477
478 if px_group_reclass_options_rec.group_reclass_type = 'CALC' and
479 p_trans_rec.calling_interface <> 'FAXASSET' then
480
481 -- call the category books cache for the accounts
482 if not fa_cache_pkg.fazccb
483 (X_book => p_asset_hdr_rec.book_type_code,
484 X_cat_id => p_src_asset_cat_rec.category_id) then
485 raise grp_rec_err;
486 end if;
487
488 -- set up the structs to be passed to faxinaj
489 l_rsv_adj.transaction_header_id := px_src_trans_rec.transaction_header_id;
490 l_rsv_adj.asset_id := px_src_asset_hdr_rec.asset_id; -- p_asset_fin_rec_new.group_asset_id;
491 l_rsv_adj.book_type_code := px_src_asset_hdr_rec.book_type_code;
492 l_rsv_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
493 l_rsv_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
494 l_rsv_adj.current_units := p_src_asset_desc_rec.current_units;
495 l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
496 l_rsv_adj.selection_thid := 0;
497 l_rsv_adj.selection_retid := 0;
498 l_rsv_adj.leveling_flag := TRUE;
499 l_rsv_adj.last_update_date := px_src_trans_rec.transaction_date_entered;
500 l_rsv_adj.flush_adj_flag := TRUE;
501 l_rsv_adj.gen_ccid_flag := TRUE;
502 l_rsv_adj.annualized_adjustment := 0;
503 l_rsv_adj.asset_invoice_id := 0;
504 l_rsv_adj.distribution_id := 0;
505 l_rsv_adj.mrc_sob_type_code := p_mrc_sob_type_code;
506
507
508 -- Expense accounts have to be CR for the old acct
509 -- Reserve accounts have to be DR for the old acct
510
511 l_rsv_adj.source_type_code := 'ADJUSTMENT';
512 l_rsv_adj.adjustment_type := 'RESERVE';
513 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
514 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
515 l_rsv_adj.account_type := 'DEPRN_RESERVE_ACCT';
516 l_rsv_adj.debit_credit_flag := 'DR';
517
518 if (nvl(px_group_reclass_options_rec.reserve_amount, 0) <> 0) then
519
520 l_rsv_adj.adjustment_amount := px_group_reclass_options_rec.reserve_amount;
521
522 if (p_log_level_rec.statement_level) then
523 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for rsv source', p_log_level_rec);
524 end if;
525
526 if not FA_INS_ADJUST_PKG.faxinaj
527 (l_rsv_adj,
528 px_src_trans_rec.who_info.last_update_date,
529 px_src_trans_rec.who_info.last_updated_by,
530 px_src_trans_rec.who_info.last_update_login) then
531 raise grp_rec_err;
532 end if;
533 end if;
534
535 if (l_src_asset_fin_rec_old.tracking_method = 'ALLOCATE') then
536 -- call the category books cache for the accounts
537 if not fa_cache_pkg.fazccb
538 (X_book => p_asset_hdr_rec.book_type_code,
539 X_cat_id => p_asset_cat_rec.category_id) then
540 raise grp_rec_err;
541 end if;
542
543 -- set up the structs to be passed to faxinaj
544 l_rsv_adj.transaction_header_id := p_trans_rec.transaction_header_id;
545 l_rsv_adj.asset_id := p_asset_hdr_rec.asset_id; -- p_asset_fin_rec_new.group_asset_id;
546 l_rsv_adj.current_units := p_asset_desc_rec.current_units;
547
548 -- Expense accounts have to be CR for the old acct
549 -- Reserve accounts have to be DR for the old acct
550
551 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
552 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
553 l_rsv_adj.track_member_flag := 'Y';
554
555
556 if (nvl(px_group_reclass_options_rec.reserve_amount, 0) <> 0) then
557
558 if (p_log_level_rec.statement_level) then
559 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for rsv source track', p_log_level_rec);
560 end if;
561
562 if not FA_INS_ADJUST_PKG.faxinaj
563 (l_rsv_adj,
564 px_src_trans_rec.who_info.last_update_date,
565 px_src_trans_rec.who_info.last_updated_by,
566 px_src_trans_rec.who_info.last_update_login) then
567 raise grp_rec_err;
568 end if;
569 end if;
570 end if; -- (l_src_asset_fin_rec_new.tracking_method = 'ALLOCATE')
571 end if;
572
573 -- BMR
574
575
576 else -- asset was originally standalone
577
578 if (p_log_level_rec.statement_level) then
579 fa_debug_pkg.add(l_calling_fn, 'source is','standalone', p_log_level_rec);
580 end if;
581
582
583 -- whether expense or reserve transfer, amount must be set to
584 -- the current reserve balance in order to remove all balances
585 -- from the memeber asset (i.e. ignore calc / manual) this is
586 -- done internally inside the private api
587
588
589 -- set the main structs equal to member if asset was
590 -- originally standalone
591
592 px_src_trans_rec := p_trans_rec;
593 l_src_asset_hdr_rec := p_asset_hdr_rec;
594 l_src_asset_desc_rec := p_asset_desc_rec;
595 l_src_asset_type_rec := p_asset_type_rec;
596 l_src_asset_cat_rec := p_asset_cat_rec;
597 l_src_asset_fin_rec_new := p_asset_fin_rec_old; -- NOTE: using old here
598 l_src_asset_deprn_rec_new := p_asset_deprn_rec_old;
599
600 end if;
601
602 px_src_trans_rec.amortization_start_date := p_trans_rec.amortization_start_date;
603
604 if (p_log_level_rec.statement_level) then
605 fa_debug_pkg.add(l_calling_fn, 'fin_rec_new.deprn_method',l_src_asset_fin_rec_new.deprn_method_code, p_log_level_rec);
606 fa_debug_pkg.add(l_calling_fn, 'fin_rec_new.eofy_reserve',l_src_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
607 fa_debug_pkg.add(l_calling_fn, 'fin_rec_old.eofy_reserve',l_src_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
608 fa_debug_pkg.add(l_calling_fn, 'amort start date3',p_trans_rec.amortization_start_date , p_log_level_rec);
609 fa_debug_pkg.add(l_calling_fn, 'amort start date3a',px_src_trans_rec.amortization_start_date , p_log_level_rec);
610 fa_debug_pkg.add(l_calling_fn, 'destination tracking method',l_dest_asset_fin_rec_old.tracking_method, p_log_level_rec);
611 end if;
612
613 -- If the asset was moved out of a group, deduct the asset's cost from the old group
614 if (l_src_asset_type_rec.asset_type <> 'GROUP' and
615 nvl(l_dest_asset_fin_rec_old.tracking_method,'NONE') <> 'CALCULATE') or
616 px_group_reclass_options_rec.group_reclass_type = 'MANUAL' then
617 if not FA_GROUP_RECLASS2_PVT.do_adjustment
618 (px_trans_rec => px_src_trans_rec,
619 p_asset_hdr_rec => l_src_asset_hdr_rec,
620 p_asset_desc_rec => l_src_asset_desc_rec,
621 p_asset_type_rec => l_src_asset_type_rec,
622 p_asset_cat_rec => l_src_asset_cat_rec,
623 p_asset_fin_rec_old => l_src_asset_fin_rec_old,
624 p_asset_fin_rec_new => l_src_asset_fin_rec_new,
625 p_asset_deprn_rec_old => l_src_asset_deprn_rec_new,
626 p_mem_asset_hdr_rec => p_asset_hdr_rec,
627 p_mem_asset_desc_rec => p_asset_desc_rec,
628 p_mem_asset_type_rec => p_asset_type_rec,
629 p_mem_asset_cat_rec => p_asset_cat_rec,
630 p_mem_asset_fin_rec_new => p_asset_fin_rec_old,
631 p_mem_asset_deprn_rec_new => p_asset_deprn_rec_old,
632 px_group_reclass_options_rec => px_group_reclass_options_rec,
633 p_period_rec => p_period_rec,
634 p_mrc_sob_type_code => p_mrc_sob_type_code,
635 p_src_dest => 'SOURCE'
636 ) then
637 raise grp_rec_err;
638 end if;
639
640 if (p_log_level_rec.statement_level) then
641 fa_debug_pkg.add(l_calling_fn, 'amort start date4',p_trans_rec.amortization_start_date , p_log_level_rec);
642 fa_debug_pkg.add(l_calling_fn, 'amort start date4a',px_src_trans_rec.amortization_start_date , p_log_level_rec);
643 end if;
644 end if;
645
646 --------------------------
647 -- CONDITIONAL TRANSFER --
648 --------------------------
649
650 -- BUG# 6936546
651 -- in allocation cases it is possible in cases where asset is moving into or out of
652 -- a standalone status for true expense to be combined with tracked expense in the
653 -- same period. In order to seperate these for correct reserve processing from DD
654 -- for journaling purposes, we will force a transfer so we can seperate the two
655 -- into different distributions when the reclass is backdated creating expense
656 --
657 -- UPDATE: not only backdated, but current period too
658 -- as expense could have hit the dist from other trxs
659 --
660 -- thus this transfer effectively is a complete wash, although only cost
661 -- will be moved in the transaction as the first portion of reclass out
662 -- must clear all expense and reserve as part of the premise for fix.
663 --
664 -- NOTE: this would spawn a transfer when occuring in tax books as well
665 -- since we share distirubtions, there is really no way around this...
666
667 if ((l_group_reclass_code = 'GRP-NONE' or
668 l_group_reclass_code = 'NONE-GRP') and
669 ((l_src_asset_fin_rec_old.tracking_method = 'ALLOCATE' or
670 l_dest_asset_fin_rec_old.tracking_method = 'ALLOCATE') OR
671 ((l_src_asset_fin_rec_old.tracking_method = 'CALCULATE' and
672 nvl(l_src_asset_fin_rec_old.member_rollup_flag, 'N') = 'N') OR
673 (l_dest_asset_fin_rec_old.tracking_method = 'CALCULATE' and
674 nvl(l_dest_asset_fin_rec_old.member_rollup_flag, 'N') = 'N'))) and
675 p_trans_rec.calling_interface <> 'FAXASSET') then
676
677 if (p_mrc_sob_type_code <> 'R') then
678
679 l_asset_hdr_rec.book_type_code :=
680 fa_cache_pkg.fazcbc_record.distribution_source_book;
681
682 open c_member_dists (p_asset_id => p_asset_hdr_rec.asset_id);
683 fetch c_member_dists bulk collect
684 into l_distribution_id,
685 l_units_assigned,
686 l_code_combination_id,
687 l_location_id,
688 l_assigned_to;
689 close c_member_dists;
690
691 -- load current dists into array
692 for i in 1..l_distribution_id.count loop
693 l_asset_dist_tbl(i).distribution_id := l_distribution_id(i);
694 l_asset_dist_tbl(i).transaction_units := -l_units_assigned(i);
695 end loop;
696
697 -- load the new dists into array
698 for i in 1..l_distribution_id.count loop
699 l_asset_dist_tbl(i+l_distribution_id.count).expense_ccid := l_code_combination_id(i);
700 l_asset_dist_tbl(i+l_distribution_id.count).location_ccid := l_location_id(i);
701 l_asset_dist_tbl(i+l_distribution_id.count).assigned_to := l_assigned_to(i);
702 l_asset_dist_tbl(i+l_distribution_id.count).transaction_units := l_units_assigned(i);
703 end loop;
704
705 if (p_log_level_rec.statement_level) then
706 fa_debug_pkg.add(l_calling_fn, 'calling' ,'transfer api for standalone asset', p_log_level_rec);
707 end if;
708
709 FA_TRANSFER_PUB.do_transfer
710 (p_api_version => l_api_version,
711 p_init_msg_list => l_init_msg_list,
712 p_commit => l_commit,
713 p_validation_level => l_validation_level,
714 p_calling_fn => l_calling_fn,
715 x_return_status => l_return_status2,
716 x_msg_count => l_msg_count,
717 x_msg_data => l_msg_data,
718 px_trans_rec => l_trans_rec,
719 px_asset_hdr_rec => l_asset_hdr_rec,
720 px_asset_dist_tbl => l_asset_dist_tbl);
721
722 if (l_return_status2 <> FND_API.G_RET_STS_SUCCESS) then
723 raise grp_rec_err;
724 end if;
725
726 if (l_group_reclass_code = 'NONE-GRP') then
727
728 update fa_adjustments
729 set track_member_flag = null
730 where transaction_header_id = l_trans_rec.transaction_header_id
731 and book_type_code = p_asset_hdr_rec.book_type_code;
732
733 end if;
734
735 elsif (l_group_reclass_code = 'NONE-GRP') then -- reporting is implied in else
736
737 select transaction_header_id
738 into l_trans_rec.transaction_header_id
739 from fa_transaction_headers
740 where asset_id = p_asset_hdr_rec.asset_id
741 and book_type_code = p_asset_hdr_rec.book_type_code
742 and transaction_type_code = 'TRANSFER'
743 and transaction_header_id > p_trans_rec.transaction_header_id;
744
745 update fa_adjustments_mrc_v
746 set track_member_flag = null
747 where transaction_header_id = l_trans_rec.transaction_header_id
748 and book_type_code = p_asset_hdr_rec.book_type_code;
749
750 end if;
751
752 end if;
753
754 -----------------
755 -- DESTINATION --
756 -----------------
757
758 if (p_log_level_rec.statement_level) then
759 fa_debug_pkg.add(l_calling_fn, 'processing','detination asset', p_log_level_rec);
760 end if;
761
762 -- For Source Group
763 -- Populate Subtract Ytd Flag
764 if not fa_cache_pkg.fazccmt(X_method => p_asset_fin_rec_new.deprn_method_code,
765 X_life => p_asset_fin_rec_new.life_in_months) then
766 raise grp_rec_err;
767 end if;
768
769 -- If the asset was moved into a group, add the asset's cost to the new group
770 if p_asset_fin_rec_new.group_asset_id is not null then
771
772 if (p_log_level_rec.statement_level) then
773 fa_debug_pkg.add(l_calling_fn, 'processing dest as ','group', p_log_level_rec);
774 end if;
775
776 l_dest_asset_hdr_rec := px_dest_asset_hdr_rec;
777 l_dest_asset_desc_rec := p_dest_asset_desc_rec;
778 l_dest_asset_type_rec := p_dest_asset_type_rec;
779 l_dest_asset_cat_rec := p_dest_asset_cat_rec;
780
781 -- load the delta cost for the group with the cost of the member
782
783 if (p_asset_type_rec.asset_type = 'CIP') then
784 l_dest_asset_fin_rec_adj.cost := p_asset_fin_rec_new.cost - p_asset_fin_rec_new.cip_cost;
785 l_dest_asset_fin_rec_adj.cip_cost := p_asset_fin_rec_new.cip_cost;
786 else
787 l_dest_asset_fin_rec_adj.cost := p_asset_fin_rec_new.cost;
788 end if;
789
790 if (p_mrc_sob_type_code <> 'R') then
791
792 -- set up the group recs
793 px_dest_trans_rec := p_trans_rec;
794 px_dest_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
795
796 px_dest_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
797 px_dest_trans_rec.member_transaction_header_id := p_trans_rec.transaction_header_id;
798 px_dest_trans_rec.transaction_subtype := 'AMORTIZED';
799 px_dest_trans_rec.transaction_key := 'GC';
800 px_dest_trans_rec.transaction_date_entered := p_trans_rec.amortization_start_date;
801
802
803 select fa_transaction_headers_s.nextval
804 into px_dest_trans_rec.transaction_header_id
805 from dual;
806
807 end if;
808
809 -- get the old fin and deprn information
810 if not FA_UTIL_PVT.get_asset_fin_rec
811 (p_asset_hdr_rec => px_dest_asset_hdr_rec,
812 px_asset_fin_rec => l_dest_asset_fin_rec_old,
813 p_transaction_header_id => NULL,
814 p_mrc_sob_type_code => p_mrc_sob_type_code
815 ) then raise grp_rec_err;
816 end if;
817
818 --HH Validate disabled_flag
819 --Reject transaction if dest group is disabled
820 if not FA_ASSET_VAL_PVT.validate_disabled_flag
821 (p_group_asset_id => l_dest_asset_fin_rec_old.group_asset_id,
822 p_book_type_code => px_dest_asset_hdr_rec.book_type_code,
823 p_old_flag => l_dest_asset_fin_rec_old.disabled_flag,
824 p_new_flag => l_dest_asset_fin_rec_old.disabled_flag
825 ) then
826 raise grp_rec_err;
827 end if; --End HH
828
829 if not FA_UTIL_PVT.get_asset_deprn_rec
830 (p_asset_hdr_rec => px_dest_asset_hdr_rec,
831 px_asset_deprn_rec => l_dest_asset_deprn_rec_old,
832 p_period_counter => NULL,
833 p_mrc_sob_type_code => p_mrc_sob_type_code
834 ) then raise grp_rec_err;
835 end if;
836
837 if (p_log_level_rec.statement_level) then
838 fa_debug_pkg.add(l_calling_fn, 'amort start date6', p_trans_rec.amortization_start_date , p_log_level_rec);
839 fa_debug_pkg.add(l_calling_fn, 'amort start date6a',px_dest_trans_rec.amortization_start_date , p_log_level_rec);
840 end if;
841
842 -- If the deprn basis rule is defined as Year End Balance type,
843 -- new adjusted cost must be calculated based on the entered eofy_reserve
844 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
845 l_dest_asset_fin_rec_adj.eofy_reserve := nvl(px_group_reclass_options_rec.destination_eofy_reserve,0);
846 end if;
847
848 --
849 -- Populating eofy_reserve is necessary because fin_rec_adj is not populated in
850 -- faxama if this is not called from FAXASSET
851 --
852 if (p_asset_fin_rec_old.group_asset_id is null) and
853 (p_trans_rec.calling_interface <> 'FAXASSET') then
854 l_dest_asset_fin_rec_adj.eofy_reserve := nvl(p_asset_fin_rec_new.eofy_reserve,0);
855 end if;
856
857 if px_group_reclass_options_rec.group_reclass_type = 'MANUAL' then
858 l_dest_asset_deprn_rec_adj.deprn_reserve := px_group_reclass_options_rec.reserve_amount;
859 end if;
860
861 if (p_log_level_rec.statement_level) then
862 fa_debug_pkg.add(l_calling_fn, 'l_dest_asset_fin_rec_old.eofy_reserve',
863 l_dest_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
864 fa_debug_pkg.add(l_calling_fn, 'l_dest_asset_fin_rec_adj.eofy_reserve',
865 l_dest_asset_fin_rec_adj.eofy_reserve, p_log_level_rec);
866 fa_debug_pkg.add(l_calling_fn, 'l_dest_asset_fin_rec_new.eofy_reserve',
867 l_dest_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
868 fa_debug_pkg.add(l_calling_fn, 'amort start date befor calling faxama',
869 px_dest_trans_rec.amortization_start_date , p_log_level_rec);
870 end if;
871
872 -- now process the adjustment on the group
873 if not FA_ADJUSTMENT_PVT.do_adjustment
874 (px_trans_rec => px_dest_trans_rec,
875 px_asset_hdr_rec => px_dest_asset_hdr_rec,
876 p_asset_desc_rec => p_dest_asset_desc_rec,
877 p_asset_type_rec => p_dest_asset_type_rec,
878 p_asset_cat_rec => p_dest_asset_cat_rec,
879 p_asset_fin_rec_old => l_dest_asset_fin_rec_old,
880 p_asset_fin_rec_adj => l_dest_asset_fin_rec_adj,
881 x_asset_fin_rec_new => l_dest_asset_fin_rec_new,
882 p_inv_trans_rec => l_inv_trans_rec,
883 p_asset_deprn_rec_old => l_dest_asset_deprn_rec_old,
884 p_asset_deprn_rec_adj => l_dest_asset_deprn_rec_adj,
885 x_asset_deprn_rec_new => l_dest_asset_deprn_rec_new,
886 p_period_rec => p_period_rec,
887 p_reclassed_asset_id => p_asset_hdr_rec.asset_id,
888 p_reclass_src_dest => 'DESTINATION',
889 p_reclassed_asset_dpis => p_asset_fin_rec_old.date_placed_in_service,
890 p_mrc_sob_type_code => p_mrc_sob_type_code,
891 p_group_reclass_options_rec => px_group_reclass_options_rec,
892 p_calling_fn => l_calling_fn
893 )then
894 raise grp_rec_err;
895 end if; -- do_adjustment
896
897 if px_group_reclass_options_rec.group_reclass_type = 'MANUAL' then
898 -- BUG# 2683922, issue #2
899 -- delete any expense rows taken in the above call from
900 -- calc_fin_info. currently, there's no easy way to
901 -- distinguish whether the group adjustment was from
902 -- a group reclass / member adjustment, etc inside
903 -- calc_fin_info when calling faxama/faxiat
904
905 if (p_mrc_sob_type_code = 'R') then
906 delete from fa_adjustments_mrc_v
907 where asset_id = px_dest_asset_hdr_rec.asset_id
908 and book_type_code = px_dest_asset_hdr_rec.book_type_code
909 and transaction_header_id = px_dest_trans_rec.transaction_header_id
910 and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
911 and source_type_code = 'DEPRECIATION';
912 else
913 delete from fa_adjustments
914 where asset_id = px_dest_asset_hdr_rec.asset_id
915 and book_type_code = px_dest_asset_hdr_rec.book_type_code
916 and transaction_header_id = px_dest_trans_rec.transaction_header_id
917 and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
918 and source_type_code = 'DEPRECIATION';
919 end if;
920 end if;
921
922 if (p_log_level_rec.statement_level) then
923 fa_debug_pkg.add(l_calling_fn, 'amort start date7', p_trans_rec.amortization_start_date , p_log_level_rec);
924 fa_debug_pkg.add(l_calling_fn, 'amort start date7a',px_dest_trans_rec.amortization_start_date , p_log_level_rec);
925 fa_debug_pkg.add(l_calling_fn, 'l_dest_asset_fin_rec_new.eofy_reserve',
926 l_dest_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
927 end if;
928
929 l_dest_asset_deprn_rec_new := l_dest_asset_deprn_rec_old;
930
931 if px_group_reclass_options_rec.group_reclass_type = 'CALC' and
932 p_trans_rec.calling_interface <> 'FAXASSET' then
933
934 -- call the category books cache for the accounts
935 if not fa_cache_pkg.fazccb
936 (X_book => p_asset_hdr_rec.book_type_code,
937 X_cat_id => p_dest_asset_cat_rec.category_id) then
938 raise grp_rec_err;
939 end if;
940
941 -- set up the structs to be passed to faxinaj
942 l_rsv_adj.transaction_header_id := px_dest_trans_rec.transaction_header_id;
943 l_rsv_adj.asset_id := px_dest_asset_hdr_rec.asset_id; -- p_asset_fin_rec_new.group_asset_id;
944 l_rsv_adj.book_type_code := px_dest_asset_hdr_rec.book_type_code;
945 l_rsv_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
946 l_rsv_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
947 l_rsv_adj.current_units := p_dest_asset_desc_rec.current_units;
948 l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
949 l_rsv_adj.selection_thid := 0;
950 l_rsv_adj.selection_retid := 0;
951 l_rsv_adj.leveling_flag := TRUE;
952 l_rsv_adj.last_update_date := px_dest_trans_rec.transaction_date_entered;
953 l_rsv_adj.flush_adj_flag := TRUE;
954 l_rsv_adj.gen_ccid_flag := TRUE;
955 l_rsv_adj.annualized_adjustment := 0;
956 l_rsv_adj.asset_invoice_id := 0;
957 l_rsv_adj.distribution_id := 0;
958 l_rsv_adj.mrc_sob_type_code := p_mrc_sob_type_code;
959 l_rsv_adj.track_member_flag := null;
960
961 -- Expense accounts have to be DR for the new acct
962 -- Reserve accounts have to be CR for the new acct
963
964 l_rsv_adj.source_type_code := 'ADJUSTMENT';
965 l_rsv_adj.adjustment_type := 'RESERVE';
966 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
967 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
968 l_rsv_adj.account_type := 'DEPRN_RESERVE_ACCT';
969 l_rsv_adj.debit_credit_flag := 'CR';
970
971 if (nvl(px_group_reclass_options_rec.reserve_amount, 0) <> 0) then
972
973 l_rsv_adj.adjustment_amount := px_group_reclass_options_rec.reserve_amount;
974
975 if (p_log_level_rec.statement_level) then
976 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for rsv dest', p_log_level_rec);
977 end if;
978
979 if not FA_INS_ADJUST_PKG.faxinaj
980 (l_rsv_adj,
981 px_dest_trans_rec.who_info.last_update_date,
982 px_dest_trans_rec.who_info.last_updated_by,
983 px_dest_trans_rec.who_info.last_update_login) then
984 raise grp_rec_err;
985 end if;
986 end if;
987
988 if (l_dest_asset_fin_rec_new.tracking_method = 'ALLOCATE') then -- ENERGY
989
990 -- call the category books cache for the accounts
991 if not fa_cache_pkg.fazccb
992 (X_book => p_asset_hdr_rec.book_type_code,
993 X_cat_id => p_asset_cat_rec.category_id) then
994 raise grp_rec_err;
995 end if;
996
997 -- set up the structs to be passed to faxinaj
998 l_rsv_adj.transaction_header_id := p_trans_rec.transaction_header_id;
999 l_rsv_adj.asset_id := p_asset_hdr_rec.asset_id;
1000 l_rsv_adj.current_units := p_asset_desc_rec.current_units;
1001 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
1002 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
1003 l_rsv_adj.debit_credit_flag := 'CR';
1004 l_rsv_adj.track_member_flag := 'Y'; --bug6951739
1005
1006 if (nvl(px_group_reclass_options_rec.reserve_amount, 0) <> 0) then
1007
1008 if (p_log_level_rec.statement_level) then
1009 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for rsv dest track', p_log_level_rec);
1010 end if;
1011
1012 if not FA_INS_ADJUST_PKG.faxinaj
1013 (l_rsv_adj,
1014 px_dest_trans_rec.who_info.last_update_date,
1015 px_dest_trans_rec.who_info.last_updated_by,
1016 px_dest_trans_rec.who_info.last_update_login) then
1017 raise grp_rec_err;
1018 end if;
1019 end if;
1020
1021 end if; -- (l_dest_asset_fin_rec_new.tracking_method = 'ALLOCATE')
1022 end if;
1023
1024 else -- asset is now standalone
1025
1026 if (p_log_level_rec.statement_level) then
1027 fa_debug_pkg.add(l_calling_fn, 'processing dest as','standalone', p_log_level_rec);
1028 end if;
1029
1030 -- set the main structs equal to member if asset is
1031 -- now standalone
1032
1033 px_dest_trans_rec := p_trans_rec;
1034 l_dest_asset_hdr_rec := p_asset_hdr_rec;
1035 l_dest_asset_desc_rec := p_asset_desc_rec;
1036 l_dest_asset_type_rec := p_asset_type_rec;
1037 l_dest_asset_cat_rec := p_asset_cat_rec;
1038 l_dest_asset_fin_rec_old := p_asset_fin_rec_old;
1039 l_dest_asset_fin_rec_new := p_asset_fin_rec_new;
1040 l_dest_asset_deprn_rec_old := p_asset_deprn_rec_old;
1041 l_dest_asset_deprn_rec_new := p_asset_deprn_rec_new;
1042
1043 end if;
1044
1045 px_dest_trans_rec.amortization_start_date := p_trans_rec.amortization_start_date;
1046
1047 if (p_log_level_rec.statement_level) then
1048 fa_debug_pkg.add(l_calling_fn, 'amort start date8',p_trans_rec.amortization_start_date , p_log_level_rec);
1049 fa_debug_pkg.add(l_calling_fn, 'amort start date8a',px_dest_trans_rec.amortization_start_date , p_log_level_rec);
1050 end if;
1051
1052 -- If the asset was moved into a group, add the asset's cost to the new group
1053 if (l_dest_asset_type_rec.asset_type <> 'GROUP' and
1054 nvl(l_src_asset_fin_rec_old.tracking_method,'NONE') <> 'CALCULATE' ) or
1055 (px_group_reclass_options_rec.group_reclass_type = 'MANUAL') then
1056 -- l_dest_asset_type_rec.asset_type = 'GROUP') then
1057 if not FA_GROUP_RECLASS2_PVT.do_adjustment
1058 (px_trans_rec => px_dest_trans_rec,
1059 p_asset_hdr_rec => l_dest_asset_hdr_rec,
1060 p_asset_desc_rec => l_dest_asset_desc_rec,
1061 p_asset_type_rec => l_dest_asset_type_rec,
1062 p_asset_cat_rec => l_dest_asset_cat_rec,
1063 p_asset_fin_rec_old => l_dest_asset_fin_rec_old,
1064 p_asset_fin_rec_new => l_dest_asset_fin_rec_new,
1065 p_asset_deprn_rec_old => l_dest_asset_deprn_rec_new,
1066 p_mem_asset_hdr_rec => p_asset_hdr_rec,
1067 p_mem_asset_desc_rec => p_asset_desc_rec,
1068 p_mem_asset_type_rec => p_asset_type_rec,
1069 p_mem_asset_cat_rec => p_asset_cat_rec,
1070 p_mem_asset_fin_rec_new => p_asset_fin_rec_new,
1071 p_mem_asset_deprn_rec_new => p_asset_deprn_rec_new,
1072 px_group_reclass_options_rec => px_group_reclass_options_rec,
1073 p_period_rec => p_period_rec,
1074 p_mrc_sob_type_code => p_mrc_sob_type_code,
1075 p_src_dest => 'DESTINATION'
1076 ) then
1077 raise grp_rec_err;
1078 end if;
1079 end if;
1080
1081 if (p_log_level_rec.statement_level) then
1082 fa_debug_pkg.add(l_calling_fn, 'amort start date9',p_trans_rec.amortization_start_date, p_log_level_rec);
1083 fa_debug_pkg.add(l_calling_fn, 'amort start date9a',px_dest_trans_rec.amortization_start_date, p_log_level_rec);
1084 end if;
1085
1086 --
1087 -- Initialize Member Tables
1088 --
1089 FA_AMORT_PVT.initMemberTable;
1090
1091 -- insert the transaction link record
1092 -- note that the trx_ref_id was loaded in FAPADJB.pls
1093
1094 if (p_mrc_sob_type_code <> 'R') then
1095
1096 l_trx_reference_id := p_trans_rec.trx_reference_id;
1097
1098 fa_trx_references_pkg.insert_row
1099 (X_Rowid => l_rowid,
1100 X_Trx_Reference_Id => l_trx_reference_id,
1101 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1102 X_Src_Asset_Id => l_src_asset_hdr_rec.asset_id,
1103 X_Src_Transaction_Header_Id => px_src_trans_rec.transaction_header_id,
1104 X_Dest_Asset_Id => l_dest_asset_hdr_rec.asset_id,
1105 X_Dest_Transaction_Header_Id => px_dest_trans_rec.transaction_header_id,
1106 X_Member_Asset_Id => p_asset_hdr_rec.asset_id,
1107 X_Member_Transaction_Header_Id => p_trans_rec.transaction_header_id,
1108 X_Transaction_Type => 'GROUP CHANGE',
1109 X_Src_Transaction_Subtype => px_src_trans_rec.transaction_subtype || ' ' || px_group_reclass_options_rec.group_reclass_type,
1110 X_Dest_Transaction_Subtype => px_dest_trans_rec.transaction_subtype || ' ' || px_group_reclass_options_rec.group_reclass_type,
1111 X_Src_Amortization_Start_Date => px_src_trans_rec.amortization_start_date,
1112 X_Dest_Amortization_Start_Date => px_dest_trans_rec.amortization_start_date,
1113 X_Reserve_Transfer_Amount => px_group_reclass_options_rec.reserve_amount,
1114 X_Src_Expense_Amount => px_group_reclass_options_rec.source_exp_amount,
1115 X_Dest_Expense_Amount => px_group_reclass_options_rec.destination_exp_amount,
1116 X_Src_Eofy_Reserve => px_group_reclass_options_rec.source_eofy_reserve,
1117 X_Dest_Eofy_Reserve => px_group_reclass_options_rec.destination_eofy_reserve,
1118 X_Creation_Date => p_trans_rec.who_info.creation_date,
1119 X_Created_By => p_trans_rec.who_info.created_by,
1120 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1121 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1122 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1123 X_Return_Status => l_return_status,
1124 X_Calling_Fn => l_calling_fn
1125 );
1126
1127 end if;
1128
1129 -- no go back and update all transaction header rows with the link_id
1130
1131 --
1132 -- Source group is the only potential group requires terminal gain loss
1133 -- calculation.
1134 --
1135 if (nvl(l_src_asset_fin_rec_new.adjustment_required_status, 'NONE') <> 'GADJ') then
1136 if not FA_RETIREMENT_PVT.Check_Terminal_Gain_Loss(
1137 p_trans_rec => px_src_trans_rec,
1138 p_asset_hdr_rec => px_src_asset_hdr_rec,
1139 p_asset_type_rec => p_src_asset_type_rec,
1140 p_asset_fin_rec => l_src_asset_fin_rec_new,
1141 p_period_rec => p_period_rec,
1142 p_mrc_sob_type_code => p_mrc_sob_type_code,
1143 p_calling_fn => l_calling_fn) then
1144 if (p_log_level_rec.statement_level) then
1145 fa_debug_pkg.add(l_calling_fn, 'Failed Calling',
1146 ' FA_RETIREMENT_PVT.Check_Terminal_Gain_Loss', p_log_level_rec);
1147 end if;
1148 raise grp_rec_err;
1149
1150 end if;
1151 end if; -- (nvl(l_src_asset_fin_rec_new.adjustment_required_status, 'NONE') <> 'GADJ')
1152
1153
1154
1155 return true;
1156
1157 EXCEPTION
1158
1159 WHEN GRP_REC_ERR THEN
1160 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
1161 return FALSE;
1162
1163 WHEN OTHERS THEN
1164 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
1165 return FALSE;
1166
1167
1168 end do_group_reclass;
1169
1170 ---------------------------------------------------------------------------------------
1171
1172 FUNCTION do_adjustment
1173 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
1174 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1175 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
1176 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
1177 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
1178 p_asset_fin_rec_old IN FA_API_TYPES.asset_fin_rec_type,
1179 p_asset_fin_rec_new IN FA_API_TYPES.asset_fin_rec_type,
1180 p_asset_deprn_rec_old IN FA_API_TYPES.asset_deprn_rec_type,
1181 p_mem_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1182 p_mem_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
1183 p_mem_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
1184 p_mem_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
1185 p_mem_asset_fin_rec_new IN FA_API_TYPES.asset_fin_rec_type,
1186 p_mem_asset_deprn_rec_new IN FA_API_TYPES.asset_deprn_rec_type,
1187 px_group_reclass_options_rec IN OUT NOCOPY FA_API_TYPES.group_reclass_options_rec_type,
1188 p_period_rec IN fa_api_types.period_rec_type,
1189 p_mrc_sob_type_code IN VARCHAR2,
1190 p_src_dest IN VARCHAR2,
1191 p_log_level_rec IN fa_api_types.log_level_rec_type default null
1192 ) RETURN BOOLEAN IS
1193
1194 -- used for faxinaj calls
1195 l_exp_adj FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
1196 l_rsv_adj FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
1197
1198 -- used for temporary holding
1199 l_exp_amount number := 0;
1200 l_rsv_amount number := 0;
1201 l_total_rsv_amount number := 0;
1202 l_bonus_exp_amount number := 0;
1203
1204
1205 -- used for temporary storage for calls to faxama
1206 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1207 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1208 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type; --bug6983091
1209 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
1210 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
1211 l_trans_rec FA_API_TYPES.trans_rec_type;
1212 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
1213 l_deprn_exp number;
1214 l_bonus_deprn_exp number;
1215
1216 l_difference_eofy_reserve number;
1217 l_exp_adjust_for_ye number;
1218 l_exp_amount_by_system number;
1219 l_eofy_reserve number;
1220
1221 l_backed_out_exp number; --bug6983091
1222
1223 --bug6983091: getting expense just backed out
1224 CURSOR c_get_exp_amount IS
1225 select NVL(SUM(DECODE(adj.debit_credit_flag,
1226 'DR', adj.adjustment_amount,
1227 'CR', -1 * adj.adjustment_amount)), 0)
1228 from fa_adjustments adj
1229 where adj.asset_id = p_asset_hdr_rec.asset_id
1230 and adj.book_type_code = p_asset_hdr_rec.book_type_code
1231 and adj.adjustment_type = 'EXPENSE'
1232 and adj.period_counter_created = p_period_rec.period_counter
1233 and adj.track_member_flag = 'Y'
1234 and adj.transaction_header_id = px_trans_rec.transaction_header_id;
1235 /* Bug 8237945 added above transaction_header_id condition. To get the backed out expense
1236 we need to consider the expense just inserted into fa_adjustments by this transaction*/
1237
1238 --bug6983091: MRC: getting expense just backed out
1239 CURSOR c_get_mc_exp_amount IS
1240 select NVL(SUM(DECODE(adj.debit_credit_flag,
1241 'DR', adj.adjustment_amount,
1242 'CR', -1 * adj.adjustment_amount)), 0)
1243 from fa_adjustments_mrc_v adj
1244 where adj.asset_id = p_asset_hdr_rec.asset_id
1245 and adj.book_type_code = p_asset_hdr_rec.book_type_code
1246 and adj.adjustment_type = 'EXPENSE'
1247 and adj.period_counter_created = p_period_rec.period_counter
1248 and adj.track_member_flag = 'Y'
1249 and adj.transaction_header_id = px_trans_rec.transaction_header_id;
1250 /* Bug 8237945 added above transaction_header_id condition. To get the backed out expense
1251 we need to consider the expense just inserted into fa_adjustments by this transaction*/
1252
1253 l_calling_fn VARCHAR2(35) := 'fa_group_reclass_pvt.do_adjustment';
1254 grp_rec_err EXCEPTION;
1255
1256
1257 BEGIN
1258
1259 if (p_log_level_rec.statement_level) then
1260 fa_debug_pkg.add(l_calling_fn, 'amort start date inside 1',px_trans_rec.amortization_start_date, p_log_level_rec);
1261 end if;
1262
1263
1264 l_asset_fin_rec_new := p_asset_fin_rec_new;
1265
1266 if (p_src_dest = 'SOURCE') then
1267 l_asset_fin_rec_adj.cost := -1 * p_mem_asset_fin_rec_new.cost;
1268 l_asset_fin_rec_adj.salvage_value := -1 * p_mem_asset_fin_rec_new.salvage_value;
1269 l_asset_fin_rec_adj.allowed_deprn_limit_amount
1270 := -1 * p_mem_asset_fin_rec_new.allowed_deprn_limit_amount;
1271 else
1272 l_asset_fin_rec_adj.cost := p_mem_asset_fin_rec_new.cost;
1273 l_asset_fin_rec_adj.salvage_value := p_mem_asset_fin_rec_new.salvage_value;
1274 l_asset_fin_rec_adj.allowed_deprn_limit_amount
1275 := p_mem_asset_fin_rec_new.allowed_deprn_limit_amount;
1276 end if;
1277
1278 -- calculate / insert the expense and reserve accordingly
1279 --
1280 -- notes:
1281 -- 1) we do not allow NONE in conjunction with manual (***OPEN ISSUE***)
1282 -- UPDATE: yes we will as the form will default the amount and not allow
1283 -- it to change. validation below will validate that for
1284 -- NONE-GRP, that the amount is equal to the member's reserve
1285 --
1286 -- 2) precedence is as follows:
1287 -- - reserve portion (calc only)
1288 -- - NONE-GRP (the asset's reserve is always used)
1289 -- - GRP-NONE (the asset's dpis is used to derive
1290 -- the amount of grp reserve to extract)
1291 -- - GRP-GRP (the asset's dpis is used to derive
1292 -- the amount of source group's reserve)
1293 -- - expense
1294 -- - (both CALC and MANUAL):
1295 -- each side of the expense part of transaction is treated
1296 -- totally independantly.
1297 -- - (CALC only): each side uses same amortization start date.
1298 -- if there is any difference between the amort start date and
1299 -- current period open date, we will charge expense for the periods
1300 -- in between. In the event the amort start date = members dpis,
1301 -- the axpense will equal reserve portion leaving no reserve to
1302 -- transfer.
1303 --
1304 -- 3) for mrc scenarios, the incoming values will already be
1305 -- set to the reporting values we will always convert the amounts
1306 -- so that the ratio of new reserve / new cost is the same
1307 -- as in primary *** as in CRL, this poses a problem when
1308 -- using transfer reserve because there is the likely
1309 -- potential that when this ratio is applied to both the
1310 -- and destination, the amounts will not be equal, but
1311 -- from an accounting standpoint - they must be
1312 --
1313
1314
1315 -- call the category books cache for the accounts
1316 if not fa_cache_pkg.fazccb
1317 (X_book => p_asset_hdr_rec.book_type_code,
1318 X_cat_id => p_asset_cat_rec.category_id) then
1319 raise grp_rec_err;
1320 end if;
1321
1322 -- set up the structs to be passed to faxinaj
1323 l_rsv_adj.transaction_header_id := px_trans_rec.transaction_header_id;
1324 l_rsv_adj.asset_id := p_asset_hdr_rec.asset_id; -- p_asset_fin_rec_new.group_asset_id;
1325 l_rsv_adj.book_type_code := p_asset_hdr_rec.book_type_code;
1326 l_rsv_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1327 l_rsv_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1328 l_rsv_adj.current_units := p_asset_desc_rec.current_units;
1329 l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1330 l_rsv_adj.selection_thid := 0;
1331 l_rsv_adj.selection_retid := 0;
1332 l_rsv_adj.leveling_flag := TRUE;
1333 l_rsv_adj.last_update_date := px_trans_rec.transaction_date_entered;
1334 l_rsv_adj.flush_adj_flag := TRUE;
1335 l_rsv_adj.gen_ccid_flag := TRUE;
1336 l_rsv_adj.annualized_adjustment := 0;
1337 l_rsv_adj.asset_invoice_id := 0;
1338 l_rsv_adj.distribution_id := 0;
1339 l_rsv_adj.mrc_sob_type_code := p_mrc_sob_type_code;
1340
1341 l_exp_adj.transaction_header_id := px_trans_rec.transaction_header_id;
1342 l_exp_adj.asset_id := p_asset_hdr_rec.asset_id; -- p_asset_fin_rec_new.group_asset_id;
1343 l_exp_adj.book_type_code := p_asset_hdr_rec.book_type_code;
1344 l_exp_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1345 l_exp_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1346 l_exp_adj.current_units := p_asset_desc_rec.current_units;
1347 l_exp_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1348 l_exp_adj.selection_thid := 0;
1349 l_exp_adj.selection_retid := 0;
1350 l_exp_adj.leveling_flag := TRUE;
1351 l_exp_adj.last_update_date := px_trans_rec.transaction_date_entered;
1352 l_exp_adj.flush_adj_flag := TRUE;
1353 l_exp_adj.gen_ccid_flag := TRUE;
1354 l_exp_adj.annualized_adjustment := 0;
1355 l_exp_adj.asset_invoice_id := 0;
1356 l_exp_adj.distribution_id := 0;
1357 l_exp_adj.mrc_sob_type_code := p_mrc_sob_type_code;
1358
1359 -- most of the following logic was stolen from FAVGRCLB.pls (CRL)
1360 -- basically, derive the info for each insert into fa_adjustments
1361 -- based on the source / destination / asset type, etc
1362
1363 -- set up the accounting related values in the adj_ptr
1364 if (p_src_dest = 'SOURCE') then
1365
1366 -- Expense accounts have to be CR for the old acct
1367 -- Reserve accounts have to be DR for the old acct
1368
1369 l_rsv_adj.source_type_code := 'ADJUSTMENT';
1370 l_rsv_adj.adjustment_type := 'RESERVE';
1371 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
1372 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
1373 l_rsv_adj.account_type := 'DEPRN_RESERVE_ACCT';
1374 l_rsv_adj.debit_credit_flag := 'DR';
1375
1376 -- Bug4335926:
1377 -- Set l_rsv_adj.track_member_flag accordingly.
1378 --
1379 if (p_log_level_rec.statement_level) then
1380 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_new.tracking_method',p_asset_fin_rec_new.tracking_method
1381 , p_log_level_rec => p_log_level_rec);
1382 end if;
1383
1384 -- Bug5736734: Modified so that it only set flag for member tracking
1385 -- Bug 6792185: Set track_member_flag for assets having tracking_method
1386
1387 if (p_mem_asset_fin_rec_new.group_asset_id is not null) and -- GRP-NONE
1388 (p_mem_asset_fin_rec_new.tracking_method is not null) and
1389 (p_asset_type_rec.asset_type <> 'GROUP') then
1390 l_rsv_adj.track_member_flag := 'Y';
1391 l_exp_adj.track_member_flag := 'Y';
1392 else
1393 l_rsv_adj.track_member_flag := null;
1394 l_exp_adj.track_member_flag := null;
1395 end if;
1396
1397 l_exp_adj.source_type_code := 'DEPRECIATION';
1398 l_exp_adj.adjustment_type := 'EXPENSE';
1399 l_exp_adj.code_combination_id := 0;
1400 l_exp_adj.account := fa_cache_pkg.fazccb_record.deprn_expense_acct;
1401 l_exp_adj.account_type := 'DEPRN_EXPENSE_ACCT';
1402 l_exp_adj.debit_credit_flag := 'CR';
1403
1404 else -- (p_src_dest = 'DESTINATION') then
1405
1406 -- Expense accounts have to be DR for the new acct
1407 -- Reserve accounts have to be CR for the new acct
1408
1409 l_rsv_adj.source_type_code := 'ADJUSTMENT';
1410 l_rsv_adj.adjustment_type := 'RESERVE';
1411 l_rsv_adj.code_combination_id := fa_cache_pkg.fazccb_record.reserve_account_ccid;
1412 l_rsv_adj.account := fa_cache_pkg.fazccb_record.deprn_reserve_acct;
1413 l_rsv_adj.account_type := 'DEPRN_RESERVE_ACCT';
1414 l_rsv_adj.debit_credit_flag := 'CR';
1415
1416 l_exp_adj.source_type_code := 'DEPRECIATION';
1417 l_exp_adj.adjustment_type := 'EXPENSE';
1418 l_exp_adj.code_combination_id := 0;
1419 l_exp_adj.account := fa_cache_pkg.fazccb_record.deprn_expense_acct;
1420 l_exp_adj.account_type := 'DEPRN_EXPENSE_ACCT';
1421 l_exp_adj.debit_credit_flag := 'DR';
1422
1423 end if;
1424
1425 -- here are how the the various options are calculated / handled:
1426 --
1427 -- MANUAL : use the entered amounts for expense and reserve for both assets
1428 --
1429 -- CALC : use the member's dpis and derive the amount of reserve
1430 -- which it contributed (for standalone source, use query balances)
1431 -- use the amort start date and derive the expense for all periods
1432 -- since that date. transfer the difference between the two as reserve
1433 -- use the difference and the respective expense amounts for source and dest
1434 --
1435 -- (In Case the Tracking Method is enabled)
1436 -- When the Tracking Method is enabled, reclass option is passed as 'CALC',
1437 -- But the amounts for reserve and expense will be populated from stored amounts
1438 -- instead of calculating the amounts.
1439 -- This behavior is similar to MANUAL type.
1440 --
1441 -- implications: we can/must always use the same reserve for source and dest
1442 -- regardless of the reclass type
1443 --
1444
1445 if (p_log_level_rec.statement_level) then
1446 fa_debug_pkg.add(l_calling_fn, 'debug message','just before if-clause to switch logic by reclass type', p_log_level_rec);
1447 fa_debug_pkg.add(l_calling_fn, 'group_reclass_type',px_group_reclass_options_rec.group_reclass_type, p_log_level_rec);
1448 fa_debug_pkg.add(l_calling_fn, 'asset_type',p_asset_type_rec.asset_type, p_log_level_rec);
1449 fa_debug_pkg.add(l_calling_fn, 'tracking_method',p_asset_fin_rec_new.tracking_method, p_log_level_rec);
1450 fa_debug_pkg.add(l_calling_fn, 'Source or Dest',p_src_dest, p_log_level_rec);
1451 fa_debug_pkg.add(l_calling_fn, 'src exp amount', px_group_reclass_options_rec.source_exp_amount, p_log_level_rec);
1452 fa_debug_pkg.add(l_calling_fn, 'dest exp amount', px_group_reclass_options_rec.destination_exp_amount, p_log_level_rec);
1453 fa_debug_pkg.add(l_calling_fn, 'reserve amount', px_group_reclass_options_rec.reserve_amount, p_log_level_rec);
1454 fa_debug_pkg.add(l_calling_fn, 'src eofy reserve amt', px_group_reclass_options_rec.source_eofy_reserve, p_log_level_rec);
1455 fa_debug_pkg.add(l_calling_fn, 'dest eofy_reserve amt', px_group_reclass_options_rec.destination_eofy_reserve, p_log_level_rec);
1456 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_new.eofy_reserve', l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1457 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_new.group_asset_id', l_asset_fin_rec_new.group_asset_id, p_log_level_rec);
1458 fa_debug_pkg.add(l_calling_fn, 'l_asset_fin_rec_new.tracking_method', l_asset_fin_rec_new.tracking_method, p_log_level_rec);
1459 end if;
1460
1461 if (px_group_reclass_options_rec.group_reclass_type = 'MANUAL') then
1462
1463 -- note: this section is interdependent on the section following calc logic
1464 -- where the rsv is rederived. For destination we are intentionally
1465 -- not adding the two together here, for source we are.
1466 if (p_src_dest = 'SOURCE') then
1467 l_exp_amount := nvl(px_group_reclass_options_rec.source_exp_amount, 0);
1468 l_total_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0) +
1469 nvl(px_group_reclass_options_rec.source_exp_amount, 0);
1470 l_eofy_reserve := nvl(px_group_reclass_options_rec.source_eofy_reserve, 0);
1471 else
1472 l_exp_amount := nvl(px_group_reclass_options_rec.destination_exp_amount, 0);
1473 l_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1474 l_total_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1475 l_eofy_reserve := nvl(px_group_reclass_options_rec.destination_eofy_reserve,l_eofy_reserve);
1476 end if;
1477
1478 elsif (px_group_reclass_options_rec.group_reclass_type = 'CALC') and
1479 (p_asset_type_rec.asset_type = 'GROUP') and
1480 (p_asset_fin_rec_new.tracking_method is not null) then
1481
1482 if (p_log_level_rec.statement_level) then
1483 fa_debug_pkg.add(l_calling_fn, 'entering','populate member amounts logic', p_log_level_rec);
1484 end if;
1485
1486 if not fa_group_reclass2_pvt.populate_member_amounts
1487 (p_trans_rec => px_trans_rec,
1488 p_asset_hdr_rec => p_asset_hdr_rec,
1489 p_asset_fin_rec_new => p_asset_fin_rec_new,
1490 p_mem_asset_hdr_rec => p_mem_asset_hdr_rec,
1491 p_mem_asset_fin_rec_new => p_mem_asset_fin_rec_new,
1492 px_group_reclass_options_rec => px_group_reclass_options_rec,
1493 p_period_rec => p_period_rec,
1494 p_mrc_sob_type_code => p_mrc_sob_type_code,
1495 p_src_dest => p_src_dest
1496 ) then
1497 raise grp_rec_err;
1498 end if;
1499
1500 l_eofy_reserve := nvl(px_group_reclass_options_rec.source_eofy_reserve,0);
1501
1502 if (p_src_dest = 'SOURCE') then
1503 l_exp_amount := nvl(px_group_reclass_options_rec.source_exp_amount, 0);
1504 l_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1505 l_total_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1506
1507 else -- for destination group, needs to calculate expense amount.
1508 if (px_trans_rec.amortization_start_date <
1509 p_period_rec.calendar_period_open_date
1510 and px_trans_rec.calling_interface <> 'FAXASSET' -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1511 ) then
1512
1513 if not FA_AMORT_PVT.faxama
1514 (px_trans_rec => px_trans_rec, -- use amort start
1515 p_asset_hdr_rec => p_asset_hdr_rec,
1516 p_asset_desc_rec => p_asset_desc_rec,
1517 p_asset_cat_rec => p_asset_cat_rec,
1518 p_asset_type_rec => p_asset_type_rec,
1519 p_asset_fin_rec_old => p_asset_fin_rec_new,
1520 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1521 px_asset_fin_rec_new => l_asset_fin_rec_new,
1522 p_asset_deprn_rec => p_asset_deprn_rec_old,
1523 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1524 p_period_rec => p_period_rec,
1525 p_mrc_sob_type_code => p_mrc_sob_type_code,
1526 p_running_mode => fa_std_types.FA_DPR_NORMAL,
1527 p_used_by_revaluation => null,
1528 p_reclassed_asset_id => p_mem_asset_hdr_rec.asset_id,
1529 p_reclass_src_dest => p_src_dest,
1530 p_reclassed_asset_dpis => p_mem_asset_fin_rec_new.date_placed_in_service,
1531 x_deprn_exp => l_deprn_exp,
1532 x_bonus_deprn_exp => l_bonus_deprn_exp
1533 ) then
1534 raise grp_rec_err;
1535 end if;
1536
1537 if (p_log_level_rec.statement_level) then
1538 fa_debug_pkg.add(l_calling_fn, 'deprn expense from faxama - in tracking logic',l_deprn_exp, p_log_level_rec);
1539 end if;
1540 l_total_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1541 l_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1542 l_exp_amount := l_deprn_exp; -- l_rsv_amount;
1543 else
1544 l_exp_amount := 0;
1545 end if;
1546 end if;
1547
1548 elsif (px_group_reclass_options_rec.group_reclass_type = 'CALC') then
1549
1550 -- get the portion of reserve which the member contributed
1551 -- (starting from dpis) and store temporarily - needed only
1552 -- for the source asset (same amount used for destination)
1553
1554 if (p_src_dest = 'SOURCE') then
1555
1556 -- Populate Subtract Ytd Flag
1557 if not fa_cache_pkg.fazccmt(X_method => l_asset_fin_rec_new.deprn_method_code,
1558 X_life => l_asset_fin_rec_new.life_in_months) then
1559 raise grp_rec_err;
1560 end if;
1561
1562 if (p_asset_type_rec.asset_type = 'GROUP'
1563 and px_trans_rec.calling_interface <> 'FAXASSET' -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1564 ) then
1565
1566 l_trans_rec := px_trans_rec;
1567 l_trans_rec.amortization_start_date := p_mem_asset_fin_rec_new.date_placed_in_service;
1568
1569 if not FA_AMORT_PVT.faxama
1570 (px_trans_rec => l_trans_rec, -- uses DPIS for amort start
1571 p_asset_hdr_rec => p_asset_hdr_rec,
1572 p_asset_desc_rec => p_asset_desc_rec,
1573 p_asset_cat_rec => p_asset_cat_rec,
1574 p_asset_type_rec => p_asset_type_rec,
1575 p_asset_fin_rec_old => p_asset_fin_rec_new,
1576 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1577 px_asset_fin_rec_new => l_asset_fin_rec_new,
1578 p_asset_deprn_rec => p_asset_deprn_rec_old,
1579 p_period_rec => p_period_rec,
1580 p_mrc_sob_type_code => p_mrc_sob_type_code,
1581 p_running_mode => fa_std_types.FA_DPR_NORMAL,
1582 p_used_by_revaluation => null,
1583 p_reclassed_asset_id => p_mem_asset_hdr_rec.asset_id,
1584 p_reclass_src_dest => p_src_dest,
1585 p_reclassed_asset_dpis => p_mem_asset_fin_rec_new.date_placed_in_service,
1586 x_deprn_exp => l_deprn_exp,
1587 x_bonus_deprn_exp => l_bonus_deprn_exp
1588 ) then
1589 raise grp_rec_err;
1590 end if;
1591
1592 if (p_log_level_rec.statement_level) then
1593 fa_debug_pkg.add(l_calling_fn, 'faxama from DPIS',l_deprn_exp, p_log_level_rec);
1594 fa_debug_pkg.add(l_calling_fn, 'New eofy_reserve', l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1595 fa_debug_pkg.add(l_calling_fn, 'Old eofy_reserve', p_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
1596 end if;
1597
1598 l_eofy_reserve := nvl(p_asset_fin_rec_old.eofy_reserve,0) - nvl(l_asset_fin_rec_new.eofy_reserve,0);
1599 px_group_reclass_options_rec.source_eofy_reserve := l_eofy_reserve;
1600 px_group_reclass_options_rec.destination_eofy_reserve := l_eofy_reserve;
1601
1602 -- flip the sign as the amount will be negative since this is a negative cost adj
1603 l_total_rsv_amount := -l_deprn_exp;
1604
1605 -- In case the Source Method is Year End Balance,
1606 -- Necessary Reserve Amount is NEW Eofy Reserve Amount.
1607 -- Here, the total rsv amount is calculated as New.Eofy - Old.Eofy
1608 -- This may be negative amount.
1609 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
1610 if (p_log_level_rec.statement_level) then
1611 fa_debug_pkg.add(l_calling_fn, 'Source group method type', 'Year End Balance', p_log_level_rec);
1612 fa_debug_pkg.add(l_calling_fn, 'New eofy_reserve', l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1613 fa_debug_pkg.add(l_calling_fn, 'Old eofy_reserve', p_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
1614 end if;
1615 l_total_rsv_amount := nvl(p_asset_fin_rec_old.eofy_reserve,0) - nvl(l_asset_fin_rec_new.eofy_reserve,0);
1616 l_difference_eofy_reserve := l_total_rsv_amount;
1617 end if;
1618 else -- asset was originally standalone, use current reserve balance
1619 -- Bug# 3737670
1620 -- If the tracking method is 'CALCULATE' then doesn't need to reverse calculated reserve amount
1621 -- at member level. So set 0 to l_total_rsv_amount
1622 if nvl(l_asset_fin_rec_new.tracking_method,'NONE') = 'CALCULATE' then
1623 l_total_rsv_amount := 0;
1624 else
1625 l_total_rsv_amount := p_asset_deprn_rec_old.deprn_reserve;
1626 end if;
1627
1628 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
1629 l_total_rsv_amount := nvl(l_eofy_reserve,0);
1630 end if;
1631 end if;
1632
1633 else
1634
1635 l_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1636 l_total_rsv_amount := nvl(px_group_reclass_options_rec.reserve_amount, 0);
1637 if nvl(px_group_reclass_options_rec.source_eofy_reserve,0) = 0 then
1638 l_group_reclass_options_rec := px_group_reclass_options_rec;
1639 -- Call populate member amounts to get eofy_reserve;
1640 if not fa_group_reclass2_pvt.populate_member_amounts
1641 (p_trans_rec => px_trans_rec,
1642 p_asset_hdr_rec => p_asset_hdr_rec,
1643 p_asset_fin_rec_new => p_asset_fin_rec_new,
1644 p_mem_asset_hdr_rec => p_mem_asset_hdr_rec,
1645 p_mem_asset_fin_rec_new => p_mem_asset_fin_rec_new,
1646 px_group_reclass_options_rec => px_group_reclass_options_rec,
1647 p_period_rec => p_period_rec,
1648 p_mrc_sob_type_code => p_mrc_sob_type_code,
1649 p_src_dest => p_src_dest
1650 ) then
1651 raise grp_rec_err;
1652 end if;
1653
1654 l_eofy_reserve := nvl(px_group_reclass_options_rec.source_eofy_reserve,0);
1655 px_group_reclass_options_rec := l_group_reclass_options_rec;
1656
1657 else
1658 l_eofy_reserve := nvl(px_group_reclass_options_rec.destination_eofy_reserve,
1659 px_group_reclass_options_rec.source_eofy_reserve);
1660 end if;
1661 end if;
1662
1663 if (p_log_level_rec.statement_level) then
1664 fa_debug_pkg.add(l_calling_fn, 'l_exp_amount(1)', l_exp_amount, p_log_level_rec);
1665 fa_debug_pkg.add(l_calling_fn, 'l_rsv_amount(1)', l_rsv_amount, p_log_level_rec);
1666 fa_debug_pkg.add(l_calling_fn, 'l_total_rsv_amount(1)', l_total_rsv_amount, p_log_level_rec);
1667 fa_debug_pkg.add(l_calling_fn, 'l_eofy_reserve(1)', l_eofy_reserve, p_log_level_rec);
1668 end if;
1669
1670 -- get the portion of expense which the member contributed
1671 -- (starting from amort_start) for both source and desitnation
1672 --
1673 -- only needed when the transaction is backdated
1674 -- for the source asset, if the amort start = dpis,
1675 -- we can use the reserve amount in full
1676 -- but for destination, we always need the amount
1677
1678 if (p_log_level_rec.statement_level) then
1679 fa_debug_pkg.add(l_calling_fn, 'amort start date',px_trans_rec.amortization_start_date, p_log_level_rec);
1680 end if;
1681
1682 if (px_trans_rec.amortization_start_date < p_period_rec.calendar_period_open_date) then
1683
1684 if (p_log_level_rec.statement_level) then
1685 fa_debug_pkg.add(l_calling_fn, 'entering','catchup expense logic', p_log_level_rec);
1686 fa_debug_pkg.add(l_calling_fn, 'subtract_ytd_flag', fa_cache_pkg.fazcdrd_record.subtract_ytd_flag, p_log_level_rec);
1687 end if;
1688
1689 if ((px_trans_rec.amortization_start_date <>
1690 p_mem_asset_fin_rec_new.date_placed_in_service or
1691 p_src_dest = 'DESTINATION')
1692 and px_trans_rec.calling_interface <> 'FAXASSET' -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1693 ) then
1694
1695 --bug6983091: getting expense just backed out
1696 -- Set up-to-date deprn info when it becomes standalone
1697 -- necessry because when reclassed out, there may be
1698 -- expense row created while processing source
1699 l_backed_out_exp := 0;
1700 if (p_src_dest = 'DESTINATION') and
1701 (l_asset_fin_rec_new.group_asset_id is null) and
1702 (p_asset_fin_rec_old.tracking_method = 'ALLOCATE' or
1703 (p_asset_fin_rec_old.tracking_method = 'CALCULATE' and
1704 nvl(p_asset_fin_rec_old.member_rollup_flag, 'N') = 'N')) then
1705
1706 if p_mrc_sob_type_code <> 'R' then
1707 OPEN c_get_exp_amount;
1708 FETCH c_get_exp_amount INTO l_backed_out_exp;
1709 CLOSE c_get_exp_amount;
1710 else
1711 OPEN c_get_mc_exp_amount;
1712 FETCH c_get_mc_exp_amount INTO l_backed_out_exp;
1713 CLOSE c_get_mc_exp_amount;
1714 end if;
1715 end if;
1716
1717 if (p_src_dest = 'DESTINATION') and
1718 (l_asset_fin_rec_new.group_asset_id is null) then
1719 l_asset_deprn_rec_adj.deprn_reserve := l_rsv_amount;
1720 end if;
1721
1722 if (p_src_dest = 'SOURCE') then
1723 l_asset_fin_rec_adj.eofy_reserve := (-1)*l_eofy_reserve;
1724 else
1725 l_asset_fin_rec_adj.eofy_reserve := l_eofy_reserve;
1726 end if;
1727
1728 if (p_log_level_rec.statement_level) then
1729 fa_debug_pkg.add(l_calling_fn, 'faxama from amort date','before calling', p_log_level_rec);
1730 fa_debug_pkg.add(l_calling_fn, 'Adj eofy_reserve', l_asset_fin_rec_adj.eofy_reserve, p_log_level_rec);
1731 fa_debug_pkg.add(l_calling_fn, 'p_asset_deprn_rec_old.deprn_reserve', p_asset_deprn_rec_old.deprn_reserve);
1732 fa_debug_pkg.add(l_calling_fn, 'l_backed_out_exp', l_backed_out_exp);
1733 end if;
1734
1735 --bug6983091: setting up-to-date reserve
1736 l_asset_deprn_rec_old := p_asset_deprn_rec_old;
1737 l_asset_deprn_rec_old.deprn_reserve := l_asset_deprn_rec_old.deprn_reserve + nvl(l_backed_out_exp, 0);
1738
1739 if not FA_AMORT_PVT.faxama
1740 (px_trans_rec => px_trans_rec, -- use amort start
1741 p_asset_hdr_rec => p_asset_hdr_rec,
1742 p_asset_desc_rec => p_asset_desc_rec,
1743 p_asset_cat_rec => p_asset_cat_rec,
1744 p_asset_type_rec => p_asset_type_rec,
1745 p_asset_fin_rec_old => p_asset_fin_rec_new,
1746 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1747 px_asset_fin_rec_new => l_asset_fin_rec_new,
1748 p_asset_deprn_rec => l_asset_deprn_rec_old,
1749 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1750 p_period_rec => p_period_rec,
1751 p_mrc_sob_type_code => p_mrc_sob_type_code,
1752 p_running_mode => fa_std_types.FA_DPR_NORMAL,
1753 p_used_by_revaluation => null,
1754 p_reclassed_asset_id => p_mem_asset_hdr_rec.asset_id,
1755 p_reclass_src_dest => p_src_dest,
1756 p_reclassed_asset_dpis => p_mem_asset_fin_rec_new.date_placed_in_service,
1757 x_deprn_exp => l_deprn_exp,
1758 x_bonus_deprn_exp => l_bonus_deprn_exp
1759 ) then
1760 raise grp_rec_err;
1761 end if;
1762
1763 if (p_log_level_rec.statement_level) then
1764 fa_debug_pkg.add(l_calling_fn, 'deprn expense from faxama',l_deprn_exp, p_log_level_rec);
1765 fa_debug_pkg.add(l_calling_fn, 'Target group method type(expense)', 'Year End Balance', p_log_level_rec);
1766 fa_debug_pkg.add(l_calling_fn, 'New eofy_reserve', l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1767 fa_debug_pkg.add(l_calling_fn, 'Old eofy_reserve', p_asset_fin_rec_old.eofy_reserve, p_log_level_rec);
1768 end if;
1769
1770 -- l_eofy_reserve := nvl(l_asset_fin_rec_new.eofy_reserve,0);
1771
1772 -- flip the sign as the amount will be negative since this is a negative cost adj
1773 if (p_src_dest = 'SOURCE') then
1774 l_exp_amount := -l_deprn_exp;
1775 else
1776 l_exp_amount := l_deprn_exp;
1777 end if;
1778
1779 -- In case of Year End Balance, amort date can be set only DPIS of reclassified member or
1780 -- Current Date.
1781 -- If the amort date is set as DPIS of member, all amount is treated as expense.
1782 -- on the other hand, it is treated as reserve.
1783 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' and
1784 px_trans_rec.amortization_start_date = p_mem_asset_fin_rec_new.date_placed_in_service then
1785 if (p_src_dest = 'SOURCE') then
1786 l_exp_amount := l_total_rsv_amount;
1787 else
1788 l_exp_amount := nvl(p_asset_fin_rec_new.eofy_reserve,0) - nvl(p_asset_fin_rec_old.eofy_reserve,0);
1789 end if;
1790 -- BUG# 2774165: removing this code as it overwrights value from faxama
1791 --else
1792 -- l_exp_amount := 0;
1793 end if;
1794
1795 else
1796 l_exp_amount := l_total_rsv_amount;
1797 l_eofy_reserve := 0;
1798 end if;
1799 else
1800 l_exp_amount := 0;
1801 end if;
1802
1803 if (p_src_dest = 'SOURCE') then
1804 l_eofy_reserve := p_asset_fin_rec_new.eofy_reserve - l_asset_fin_rec_new.eofy_reserve;
1805 end if;
1806
1807 end if;
1808
1809 if (p_log_level_rec.statement_level) then
1810 fa_debug_pkg.add(l_calling_fn, 'l_exp_amount(2)', l_exp_amount, p_log_level_rec);
1811 fa_debug_pkg.add(l_calling_fn, 'l_rsv_amount(2)', l_rsv_amount, p_log_level_rec);
1812 fa_debug_pkg.add(l_calling_fn, 'l_total_rsv_amount(2)', l_total_rsv_amount, p_log_level_rec);
1813 fa_debug_pkg.add(l_calling_fn, 'l_eofy_reserve(2)', l_eofy_reserve, p_log_level_rec);
1814 end if;
1815
1816 -- load the amounts regardless of the type of reclass
1817 -- signs are assumed to all be the same (i.e. generally +ve)
1818 -- with reliance on the debit_credit_flags to due the rest
1819 --
1820 -- need to insure the total reserve transfered from source
1821 -- is used here as well
1822 --
1823 -- reverting initial change for BUG# 2780960 as this section
1824 -- applies to calc and manual and is dependant on code above
1825 -- note that values for total_rsv are different in both scenarios
1826 -- for src and dest assets. for destination total only stores the
1827 -- amount of reserve transfered from source at this point, so
1828 -- we need to add in the expense, not subtract it
1829
1830 if (p_src_dest = 'DESTINATION') then
1831 l_rsv_amount := l_total_rsv_amount;
1832 l_total_rsv_amount := l_total_rsv_amount + l_exp_amount;
1833 else
1834 l_rsv_amount := l_total_rsv_amount - l_exp_amount;
1835 end if;
1836
1837 if (p_log_level_rec.statement_level) then
1838 fa_debug_pkg.add(l_calling_fn, 'l_exp_amount(3)', l_exp_amount, p_log_level_rec);
1839 fa_debug_pkg.add(l_calling_fn, 'l_rsv_amount(3)', l_rsv_amount, p_log_level_rec);
1840 end if;
1841
1842 l_rsv_adj.adjustment_amount := l_rsv_amount;
1843 l_exp_adj.adjustment_amount := l_exp_amount; -- l_deprn_exp; (need to use one with same sign as tot_rsv)
1844
1845 -- In case of Year End Balance, back the values into original amount
1846 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
1847 l_exp_amount := l_exp_amount_by_system;
1848 end if;
1849 -- if this is the source asset, reset the amount in the
1850 -- reclass structure to be the delta amount as dest will use this
1851 -- (i.e. the reserve which was transfered)
1852 -- if it's the destination, we need to update the total amount
1853 -- to be the transfered value + expense
1854
1855 if (p_src_dest = 'SOURCE') then
1856 px_group_reclass_options_rec.reserve_amount := l_rsv_amount;
1857 else
1858 l_total_rsv_amount := l_rsv_amount + l_exp_amount;
1859 end if;
1860
1861 -- flush all the reserve and expense rows to the db
1862
1863 -- Fix for bug 3062207. Quick fix to not call faxinaj for CALC
1864 -- and FAXASSET combination since no reserve or exp rows must be inserted
1865 -- if called from faxasset.
1866
1867 if (l_rsv_adj.adjustment_amount <> 0
1868 and not (px_group_reclass_options_rec.group_reclass_type = 'CALC' -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1869 and px_trans_rec.calling_interface = 'FAXASSET') -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1870 ) then
1871
1872 if (p_log_level_rec.statement_level) then
1873 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for rsv', p_log_level_rec);
1874 end if;
1875
1876 if not FA_INS_ADJUST_PKG.faxinaj
1877 (l_rsv_adj,
1878 px_trans_rec.who_info.last_update_date,
1879 px_trans_rec.who_info.last_updated_by,
1880 px_trans_rec.who_info.last_update_login) then
1881 raise grp_rec_err;
1882 end if;
1883 end if;
1884
1885 if (l_exp_adj.adjustment_amount <> 0
1886 and not (px_group_reclass_options_rec.group_reclass_type = 'CALC' -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1887 and px_trans_rec.calling_interface = 'FAXASSET') -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
1888 ) then
1889
1890 if (p_log_level_rec.statement_level) then
1891 fa_debug_pkg.add(l_calling_fn, 'calling faxinaj','for exp', p_log_level_rec);
1892 end if;
1893
1894 if not FA_INS_ADJUST_PKG.faxinaj
1895 (l_exp_adj,
1896 px_trans_rec.who_info.last_update_date,
1897 px_trans_rec.who_info.last_updated_by,
1898 px_trans_rec.who_info.last_update_login) then
1899 raise grp_rec_err;
1900 end if;
1901 end if;
1902
1903
1904 -- now that rows are flushed, call the amort package again
1905 -- this time just to amortize the nbv over remaining life
1906 -- first we need to add the deprn effects to the deprn_old values
1907
1908 l_asset_deprn_rec_new := p_asset_deprn_rec_old;
1909
1910 if (p_src_dest = 'SOURCE') then
1911 l_asset_deprn_rec_new.deprn_reserve :=
1912 l_asset_deprn_rec_new.deprn_reserve - l_total_rsv_amount;
1913 else
1914 l_asset_deprn_rec_new.deprn_reserve :=
1915 l_asset_deprn_rec_new.deprn_reserve + l_total_rsv_amount;
1916 end if;
1917
1918 if (p_src_dest = 'SOURCE') then
1919 l_asset_deprn_rec_adj.deprn_reserve := -1 * l_rsv_amount;
1920 l_asset_fin_rec_adj.eofy_reserve := (-1)*nvl(l_eofy_reserve,0);
1921 if (px_group_reclass_options_rec.group_reclass_type = 'CALC') and
1922 (p_asset_type_rec.asset_type = 'GROUP') and
1923 (p_asset_fin_rec_new.tracking_method is not null) then
1924 l_asset_fin_rec_new.eofy_reserve := nvl(l_asset_fin_rec_new.eofy_reserve,0) - nvl(l_eofy_reserve,0);
1925 elsif (px_group_reclass_options_rec.group_reclass_type = 'MANUAL') and
1926 (p_asset_type_rec.asset_type = 'GROUP') then
1927 l_asset_fin_rec_new.eofy_reserve := nvl(p_asset_fin_rec_old.eofy_reserve,0) - nvl(l_eofy_reserve,0);
1928 end if;
1929 else
1930 l_asset_fin_rec_adj.eofy_reserve := nvl(l_eofy_reserve,0);
1931 if (px_group_reclass_options_rec.group_reclass_type = 'MANUAL') and
1932 (p_asset_type_rec.asset_type = 'GROUP') then
1933 l_asset_fin_rec_new.eofy_reserve := nvl(p_asset_fin_rec_old.eofy_reserve,0) + nvl(l_eofy_reserve,0);
1934 else
1935 l_asset_fin_rec_new.eofy_reserve := nvl(l_asset_fin_rec_new.eofy_reserve,0) + nvl(l_eofy_reserve,0);
1936 end if;
1937 end if;
1938
1939 if (p_log_level_rec.statement_level) then
1940 fa_debug_pkg.add(l_calling_fn, 'just before last call to faxama-p_src_dest',p_src_dest, p_log_level_rec);
1941 fa_debug_pkg.add(l_calling_fn, 'existing rsv before last call to faxama',l_asset_deprn_rec_new.deprn_reserve, p_log_level_rec);
1942 fa_debug_pkg.add(l_calling_fn, 'new expense before last call to faxama',l_exp_amount, p_log_level_rec);
1943 fa_debug_pkg.add(l_calling_fn, 'new reserve before last call to faxama',l_rsv_amount, p_log_level_rec);
1944 fa_debug_pkg.add(l_calling_fn, 'new total rsv before last call to faxama',l_total_rsv_amount, p_log_level_rec);
1945 fa_debug_pkg.add(l_calling_fn, 'total reserv before last call to faxama',l_asset_deprn_rec_new.deprn_reserve, p_log_level_rec);
1946 fa_debug_pkg.add(l_calling_fn, 'old eofy rsv before last call to faxama',p_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1947 fa_debug_pkg.add(l_calling_fn, 'new eofy rsv before last call to faxama',l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
1948 fa_debug_pkg.add(l_calling_fn, 'adj eofy rsv before last call to faxama',l_asset_fin_rec_adj.eofy_reserve, p_log_level_rec);
1949 fa_debug_pkg.add(l_calling_fn, 'amort start date',px_trans_rec.amortization_start_date, p_log_level_rec);
1950 end if;
1951
1952 -- +++ This won't be necessary
1953 l_trans_rec := px_trans_rec;
1954 l_trans_rec.amortization_start_date := null; -- use current period
1955
1956
1957 -- no need to do the amort and update when an asset is
1958 -- reclassed into a group
1959
1960 /*
1961 if (((p_asset_type_rec.asset_type = 'GROUP') or
1962 (p_src_dest = 'DESTINATION' and
1963 p_asset_type_rec.asset_type = 'CAPITALIZED')) and
1964 (px_group_reclass_options_rec.group_reclass_type = 'MANUAL' OR
1965 (px_trans_rec.calling_interface <> 'FAXASSET' and
1966 px_group_reclass_options_rec.group_reclass_type = 'CALC'))) then
1967 */
1968
1969 if ((p_asset_type_rec.asset_type = 'GROUP') or
1970 (p_src_dest = 'DESTINATION' and
1971 p_asset_type_rec.asset_type = 'CAPITALIZED')) then
1972
1973
1974 if (p_log_level_rec.statement_level) then
1975 fa_debug_pkg.add(l_calling_fn, 'LAST CALL TO FAXAMA', 'CALLING FAXAMA', p_log_level_rec);
1976 end if;
1977
1978
1979 -- use calc_raf_adj_cost rather than faxama if trx is in current
1980 -- period or it's a manual transaction for performance
1981
1982 if (px_group_reclass_options_rec.group_reclass_type = 'MANUAL' or
1983 px_trans_rec.amortization_start_date >= p_period_rec.calendar_period_open_date) then
1984
1985 if (p_log_level_rec.statement_level) then
1986 fa_debug_pkg.add(l_calling_fn, 'calling','calc_raf_adj_cost', p_log_level_rec);
1987 fa_debug_pkg.add(l_calling_fn, 'p_asset_deprn_rec_old.rsv', p_asset_deprn_rec_old.deprn_reserve, p_log_level_rec);
1988 fa_debug_pkg.add(l_calling_fn, 'p_asset_deprn_rec_old.bonus_rsv', p_asset_deprn_rec_old.bonus_deprn_reserve, p_log_level_rec);
1989 fa_debug_pkg.add(l_calling_fn, 'l_asset_deprn_rec_adj.rsv', l_asset_deprn_rec_adj.deprn_reserve, p_log_level_rec);
1990 fa_debug_pkg.add(l_calling_fn, 'l_asset_deprn_rec_adj.bonus_rsv', l_asset_deprn_rec_adj.bonus_deprn_reserve, p_log_level_rec);
1991 fa_debug_pkg.add(l_calling_fn, 'l_asset_deprn_rec_new.rsv', l_asset_deprn_rec_new.deprn_reserve, p_log_level_rec);
1992 fa_debug_pkg.add(l_calling_fn, 'l_asset_deprn_rec_new.bonus_rsv', l_asset_deprn_rec_new.bonus_deprn_reserve, p_log_level_rec);
1993 end if;
1994
1995 if not FA_AMORT_PVT.calc_raf_adj_cost
1996 (p_trans_rec => px_trans_rec,
1997 p_asset_hdr_rec => p_asset_hdr_rec,
1998 p_asset_desc_rec => p_asset_desc_rec,
1999 p_asset_type_rec => p_asset_type_rec,
2000 p_asset_fin_rec_old => p_asset_fin_rec_old,
2001 px_asset_fin_rec_new => l_asset_fin_rec_new,
2002 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj, -- should contain the total delta (set to l_rsv)
2003 p_asset_deprn_rec_new => l_asset_deprn_rec_new, -- total new amount old + adj (set above)
2004 p_period_rec => p_period_rec,
2005 p_mrc_sob_type_code => p_mrc_sob_type_code
2006 ) then raise grp_rec_err;
2007 end if;
2008
2009 elsif (px_trans_rec.calling_interface <> 'FAXASSET') then -- COMMENT THIS LINE OUT FOR NON-CONC PROGRAM TESTING
2010
2011 if not FA_AMORT_PVT.faxama
2012 (px_trans_rec => px_trans_rec, -- use amort start date
2013 p_asset_hdr_rec => p_asset_hdr_rec,
2014 p_asset_desc_rec => p_asset_desc_rec,
2015 p_asset_cat_rec => p_asset_cat_rec,
2016 p_asset_type_rec => p_asset_type_rec,
2017 p_asset_fin_rec_old => p_asset_fin_rec_new,
2018 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
2019 px_asset_fin_rec_new => l_asset_fin_rec_new,
2020 p_asset_deprn_rec => p_asset_deprn_rec_old,
2021 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
2022 p_period_rec => p_period_rec,
2023 p_mrc_sob_type_code => p_mrc_sob_type_code,
2024 p_running_mode => fa_std_types.FA_DPR_NORMAL,
2025 p_used_by_revaluation => null,
2026 p_reclassed_asset_id => p_mem_asset_hdr_rec.asset_id,
2027 p_reclass_src_dest => p_src_dest,
2028 p_reclassed_asset_dpis => p_mem_asset_fin_rec_new.date_placed_in_service,
2029 x_deprn_exp => l_deprn_exp,
2030 x_bonus_deprn_exp => l_bonus_deprn_exp
2031 ) then
2032 raise grp_rec_err;
2033 end if;
2034 end if;
2035
2036 if (p_log_level_rec.statement_level) then
2037 fa_debug_pkg.add(l_calling_fn, 'new adjusted_cost after last call to faxama',l_asset_fin_rec_new.adjusted_cost, p_log_level_rec);
2038 fa_debug_pkg.add(l_calling_fn, 'new eofy_reserve after last call to faxama',l_asset_fin_rec_new.eofy_reserve, p_log_level_rec);
2039 end if;
2040
2041 -- if reclassing out of the group to standalone, then
2042 -- insure the amount doesn't exceed the recoverable_cost
2043 -- of the standalone asset (open issue - see japan / track member)
2044
2045 -- if (l_temp_rsv + l_exp_amount > l_asset_fin_rec_new.recoverable_cost) then
2046 -- l_src_amount := l_src_asset_fin_rec_new.recoverable_cost;
2047 -- end if;
2048
2049 -- now update fa_books with new RAF / adjusted_cost
2050
2051 fa_books_pkg.update_row
2052 (X_asset_id => p_asset_hdr_rec.asset_id,
2053 X_book_type_code => p_asset_hdr_rec.book_type_code,
2054 X_rate_adjustment_factor => l_asset_fin_rec_new.rate_adjustment_factor,
2055 X_reval_amortization_basis => l_asset_fin_rec_new.reval_amortization_basis,
2056 X_adjusted_cost => l_asset_fin_rec_new.adjusted_cost,
2057 X_adjusted_capacity => l_asset_fin_rec_new.adjusted_capacity,
2058 X_eofy_reserve => l_asset_fin_rec_new.eofy_reserve,
2059 X_mrc_sob_type_code => p_mrc_sob_type_code,
2060 X_calling_fn => l_calling_fn);
2061
2062 end if;
2063
2064 return true;
2065
2066 EXCEPTION
2067 when grp_rec_err then
2068 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
2069 return false;
2070
2071 when others then
2072 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
2073 return false;
2074
2075 END do_adjustment;
2076
2077 ----------------------------------------------
2078 -- Function: populate_member_amounts
2079 -- Description:
2080 -- In case tracking is enabled, transferred amount will be populated from
2081 -- tables.
2082 ----------------------------------------------
2083
2084 FUNCTION populate_member_amounts
2085 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
2086 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
2087 p_asset_fin_rec_new IN FA_API_TYPES.asset_fin_rec_type,
2088 p_mem_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
2089 p_mem_asset_fin_rec_new IN FA_API_TYPES.asset_fin_rec_type,
2090 px_group_reclass_options_rec IN OUT NOCOPY FA_API_TYPES.group_reclass_options_rec_type,
2091 p_period_rec IN fa_api_types.period_rec_type,
2092 p_mrc_sob_type_code IN VARCHAR2,
2093 p_src_dest IN VARCHAR2,
2094 p_log_level_rec IN fa_api_types.log_level_rec_type default null
2095 ) RETURN BOOLEAN IS
2096
2097 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
2098
2099 h_book_type_code varchar2(15);
2100 h_member_asset_id number(15);
2101 h_group_asset_id number(15);
2102 h_period_counter number;
2103 h_cur_fiscal_year number;
2104 h_amort_fiscal_year number;
2105
2106 h_reserve_dpis_current number;
2107 h_reserve_dpis_amort number;
2108 h_eofy_reserve number;
2109 h_set_of_books_id number;
2110
2111 h_amort_start_date date;
2112
2113 h_ytd_deprn number;
2114 h_ds_fiscal_year number;
2115
2116 h_adj_eofy_reserve number;
2117 h_adj_reserve number;
2118
2119 --* Cursor to populate the member level amounts
2120 cursor MEM_EXP_RESERVE is
2121 select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
2122 from fa_deprn_summary ds1,
2123 fa_deprn_periods dp1
2124 where ds1.book_type_code = h_book_type_code
2125 and ds1.asset_id = h_member_asset_id
2126 and dp1.book_type_code = ds1.book_type_Code
2127 and dp1.period_counter = ds1.period_counter
2128 and ds1.period_counter =
2129 (select max(period_counter)
2130 from fa_deprn_summary ds2
2131 where ds2.book_type_code = h_book_type_code
2132 and ds2.asset_id = h_member_asset_id
2133 and period_counter <= h_period_counter);
2134
2135 cursor MEM_EXP_RESERVE_MRC is
2136 select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
2137 from fa_mc_deprn_summary ds1,
2138 fa_mc_deprn_periods dp1
2139 where ds1.book_type_code = h_book_type_code
2140 and ds1.asset_id = h_member_asset_id
2141 and dp1.book_type_code = ds1.book_type_Code
2142 and dp1.period_counter = ds1.period_counter
2143 and dp1.set_of_books_id = h_set_of_books_id
2144 and ds1.period_counter =
2145 (select max(period_counter)
2146 from fa_deprn_summary ds2
2147 where ds2.book_type_code = h_book_type_code
2148 and ds2.asset_id = h_member_asset_id
2149 and period_counter <= h_period_counter)
2150 and ds1.set_of_books_id = h_set_of_books_id;
2151
2152 --* Cursor for EOFY_RESERVE adjustment
2153 cursor FA_RET_RSV is
2154 select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
2155 from fa_retirements ret
2156 where ret.book_type_code = h_book_type_code
2157 and ret.asset_id = h_member_asset_id
2158 and ret.transaction_header_id_in in
2159 (select th1.transaction_header_id
2160 from fa_transaction_headers th1,
2161 fa_deprn_periods dp1,
2162 fa_deprn_periods dp3
2163 where th1.asset_id = ret.asset_id
2164 and dp1.book_type_code = h_book_type_code
2165 and dp1.fiscal_year =
2166 (select dp2.fiscal_year
2167 from fa_deprn_periods dp2
2168 where dp2.book_type_code = dp1.book_type_code
2169 and dp2.period_Counter = h_period_counter - 1)
2170 and dp1.period_num = 1
2171 and dp3.book_type_code = dp1.book_type_code
2172 and dp3.period_counter = h_period_counter - 1
2173 and th1.transaction_date_entered >= dp1.calendar_period_open_date
2174 and th1.transaction_date_entered <= dp3.calendar_period_close_date
2175 and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT'));
2176
2177 cursor FA_RET_RSV_MRC is
2178 select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
2179 from fa_mc_retirements ret
2180 where ret.book_type_code = h_book_type_code
2181 and ret.asset_id = h_member_asset_id
2182 and ret.set_of_books_id = h_set_of_books_id
2183 and ret.transaction_header_id_in in
2184 (select th1.transaction_header_id
2185 from fa_transaction_headers th1,
2186 fa_deprn_periods dp1,
2187 fa_deprn_periods dp3
2188 where th1.asset_id = ret.asset_id
2189 and dp1.book_type_code = h_book_type_code
2190 and dp1.fiscal_year =
2191 (select dp2.fiscal_year
2192 from fa_deprn_periods dp2
2193 where dp2.book_type_code = dp1.book_type_code
2194 and dp2.period_Counter = h_period_counter - 1)
2195 and dp1.period_num = 1
2196 and dp3.book_type_code = dp1.book_type_code
2197 and dp3.period_counter = h_period_counter - 1
2198 and th1.transaction_date_entered >= dp1.calendar_period_open_date
2199 and th1.transaction_date_entered <= dp3.calendar_period_close_date
2200 and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT'));
2201
2202 cursor FA_ADJ_RESERVE is
2203 select sum(decode(adj.debit_credit_flag,
2204 'DR',adj.adjustment_amount,
2205 'CR', -1 * adj.adjustment_amount))
2206 from fa_adjustments adj
2207 where adj.book_type_code = h_book_type_code
2208 and adj.asset_id = h_member_asset_id
2209 and adj.adjustment_type = 'RESERVE'
2210 and adj.source_type_code = 'ADJUSTMENT'
2211 and adj.period_counter_adjusted in
2212 (select dp2.period_counter
2213 from fa_deprn_periods dp1,
2214 fa_deprn_periods dp2
2215 where dp1.book_type_code = adj.book_type_code
2216 and dp1.period_counter = h_period_counter - 1
2217 and dp2.book_type_code = dp1.book_type_code
2218 and dp2.fiscal_year = dp1.fiscal_year
2219 and dp2.period_counter <= dp1.period_counter);
2220
2221 cursor FA_ADJ_RESERVE_MRC is
2222 select sum(decode(adj.debit_credit_flag,
2223 'DR',adj.adjustment_amount,
2224 'CR', -1 * adj.adjustment_amount))
2225 from fa_mc_adjustments adj
2226 where adj.book_type_code = h_book_type_code
2227 and adj.asset_id = h_member_asset_id
2228 and adj.adjustment_type = 'RESERVE'
2229 and adj.source_type_code = 'ADJUSTMENT'
2230 and adj.set_of_books_id = h_set_of_books_id
2231 and adj.period_counter_adjusted in
2232 (select dp2.period_counter
2233 from fa_mc_deprn_periods dp1,
2234 fa_mc_deprn_periods dp2
2235 where dp1.book_type_code = adj.book_type_code
2236 and dp1.period_counter = h_period_counter - 1
2237 and dp1.set_of_books_id = h_set_of_books_id
2238 and dp2.book_type_code = dp1.book_type_code
2239 and dp2.fiscal_year = dp1.fiscal_year
2240 and dp2.set_of_books_id = h_set_of_books_id
2241 and dp2.period_counter <= dp1.period_counter);
2242
2243
2244 l_calling_fn VARCHAR2(50) := 'fa_group_reclass_pvt.populate_member_amounts';
2245 pop_mem_amt_err EXCEPTION;
2246
2247
2248 BEGIN
2249
2250 if (p_log_level_rec.statement_level) then
2251 fa_debug_pkg.add(l_calling_fn, 'populate member amt',p_trans_rec.amortization_start_date, p_log_level_rec);
2252 end if;
2253
2254 l_asset_fin_rec_new := p_asset_fin_rec_new;
2255 h_book_type_code := p_asset_hdr_rec.book_type_code;
2256 h_group_asset_id := p_asset_hdr_rec.asset_id;
2257 h_member_asset_id := p_mem_asset_hdr_rec.asset_id;
2258 h_amort_start_date := nvl(p_trans_rec.amortization_start_date,sysdate);
2259 h_set_of_books_id := p_asset_hdr_rec.set_of_books_id;
2260 h_eofy_reserve := p_asset_fin_rec_new.eofy_reserve;
2261
2262 -- Populate Subtract Ytd Flag
2263 if not fa_cache_pkg.fazccmt(X_method => l_asset_fin_rec_new.deprn_method_code,
2264 X_life => l_asset_fin_rec_new.life_in_months) then
2265 raise pop_mem_amt_err;
2266 end if;
2267
2268 -- Get Period Counter for current period
2269 h_period_counter := fa_cache_pkg.fazcbc_record.last_period_counter;
2270
2271 -- Get Reserve Amount between member's DPIS and current period -- For Source Group Reserve
2272 if p_mrc_sob_type_code <> 'R' then
2273
2274 select fiscal_year into h_cur_fiscal_year
2275 from fa_deprn_periods
2276 where book_type_code = h_book_type_code
2277 and period_counter = h_period_counter;
2278
2279 open MEM_EXP_RESERVE;
2280 fetch MEM_EXP_RESERVE into h_reserve_dpis_current,h_ytd_deprn,h_ds_fiscal_year;
2281 if MEM_EXP_RESERVE%NOTFOUND then
2282 h_reserve_dpis_current := 0;
2283 end if;
2284 close MEM_EXP_RESERVE;
2285
2286 open FA_RET_RSV;
2287 fetch FA_RET_RSV into h_adj_eofy_reserve;
2288 close FA_RET_RSV;
2289
2290 open FA_ADJ_RESERVE;
2291 fetch FA_ADJ_RESERVE into h_adj_reserve;
2292 close FA_ADJ_RESERVE;
2293
2294 else
2295
2296 select fiscal_year into h_cur_fiscal_year
2297 from fa_mc_deprn_periods
2298 where book_type_code = h_book_type_code
2299 and period_counter = h_period_counter
2300 and set_of_books_id = h_set_of_books_id;
2301
2302 open MEM_EXP_RESERVE_MRC;
2303 fetch MEM_EXP_RESERVE_MRC into h_reserve_dpis_current,h_ytd_deprn,h_ds_fiscal_year;
2304 if MEM_EXP_RESERVE_MRC%NOTFOUND then
2305 h_reserve_dpis_current := 0;
2306 end if;
2307 close MEM_EXP_RESERVE_MRC;
2308
2309 open FA_RET_RSV_MRC;
2310 fetch FA_RET_RSV_MRC into h_adj_eofy_reserve;
2311 close FA_RET_RSV_MRC;
2312
2313 open FA_ADJ_RESERVE_MRC;
2314 fetch FA_ADJ_RESERVE_MRC into h_adj_reserve;
2315 close FA_ADJ_RESERVE_MRC;
2316
2317 end if;
2318
2319 -- calculate current eofy_reserve using amounts above
2320 h_eofy_reserve := p_mem_asset_fin_rec_new.eofy_reserve;
2321
2322 if (p_log_level_rec.statement_level) then
2323 fa_debug_pkg.add(l_calling_fn,'h_eofy_reserve',h_eofy_reserve, p_log_level_rec);
2324 fa_debug_pkg.add(l_calling_fn,'h_cur_fiscal_yesr',h_cur_fiscal_year, p_log_level_rec);
2325 fa_debug_pkg.add(l_calling_fn,'h_ds_fiscal_year',h_ds_fiscal_year, p_log_level_rec);
2326 fa_debug_pkg.add(l_calling_fn,'h_reserve_dpis_current',h_reserve_dpis_current, p_log_level_rec);
2327 fa_debug_pkg.add(l_calling_fn,'h_ytd_deprn',h_ytd_deprn, p_log_level_rec);
2328 fa_debug_pkg.add(l_calling_fn,'h_adj_eofy_reserve',h_adj_eofy_reserve, p_log_level_rec);
2329 fa_debug_pkg.add(l_calling_fn,'h_adj_reserve',h_adj_reserve, p_log_level_rec);
2330 end if;
2331
2332 if nvl(h_eofy_reserve,0) = 0 then
2333 if h_cur_fiscal_year = h_ds_fiscal_year then
2334 h_eofy_reserve := h_reserve_dpis_current - h_ytd_deprn + nvl(h_adj_eofy_reserve,0) + nvl(h_adj_reserve,0);
2335 else
2336 h_eofy_reserve := h_reserve_dpis_current;
2337 end if;
2338 end if;
2339
2340 if nvl(fa_cache_pkg.fazcdrd_record.subtract_ytd_flag,'N') = 'Y' then
2341 h_reserve_dpis_current := nvl(h_eofy_reserve,0);
2342 end if; -- Subtarct Flag
2343
2344 if (p_src_dest = 'SOURCE') then
2345 px_group_reclass_options_rec.reserve_amount := h_reserve_dpis_current;
2346 end if;
2347
2348 if (p_log_level_rec.statement_level) then
2349 fa_debug_pkg.add(l_calling_fn,'reserve_amount',px_group_reclass_options_rec.reserve_amount, p_log_level_rec);
2350 end if;
2351
2352 -- Get Period Counter of amortization start date
2353 if (p_trans_rec.amortization_start_date <> p_mem_asset_fin_rec_new.date_placed_in_service) then
2354
2355 -- Get Reserve Amount between amortization start date and current date -- For destination
2356 if (p_mrc_sob_type_code <> 'R') then
2357
2358 select period_Counter,fiscal_year
2359 into h_period_counter,h_amort_fiscal_year
2360 from fa_deprn_periods
2361 where book_type_Code= h_book_type_code
2362 and nvl(calendar_period_open_date,sysdate) <= h_amort_start_date
2363 and nvl(calendar_period_close_date,sysdate) >= h_amort_start_date;
2364
2365 h_period_counter := h_period_counter - 1;
2366
2367 open MEM_EXP_RESERVE;
2368 fetch MEM_EXP_RESERVE into h_reserve_dpis_amort,h_ytd_deprn,h_ds_fiscal_year;
2369 if MEM_EXP_RESERVE%NOTFOUND then
2370 h_reserve_dpis_amort := 0;
2371 end if;
2372 close MEM_EXP_RESERVE;
2373
2374 open FA_RET_RSV;
2375 fetch FA_RET_RSV into h_adj_eofy_reserve;
2376 close FA_RET_RSV;
2377
2378 open FA_ADJ_RESERVE;
2379 fetch FA_ADJ_RESERVE into h_adj_reserve;
2380 close FA_ADJ_RESERVE;
2381
2382 else
2383
2384 select period_Counter,fiscal_year
2385 into h_period_counter,h_amort_fiscal_year
2386 from fa_mc_deprn_periods
2387 where book_type_Code= h_book_type_code
2388 and nvl(calendar_period_open_date,sysdate) <= h_amort_start_date
2389 and nvl(calendar_period_close_date,sysdate) >= h_amort_start_date
2390 and set_of_books_id = h_set_of_books_id;
2391
2392 h_period_counter := h_period_counter - 1;
2393
2394 open MEM_EXP_RESERVE_MRC;
2395 fetch MEM_EXP_RESERVE_MRC into h_reserve_dpis_amort,h_ytd_deprn,h_ds_fiscal_year;
2396 if MEM_EXP_RESERVE_MRC%NOTFOUND then
2397 h_reserve_dpis_amort := 0;
2398 end if;
2399 close MEM_EXP_RESERVE_MRC;
2400
2401 open FA_RET_RSV_MRC;
2402 fetch FA_RET_RSV_MRC into h_adj_eofy_reserve;
2403 close FA_RET_RSV_MRC;
2404
2405 open FA_ADJ_RESERVE_MRC;
2406 fetch FA_ADJ_RESERVE_MRC into h_adj_reserve;
2407 close FA_ADJ_RESERVE_MRC;
2408
2409 end if;
2410
2411 if (p_log_level_rec.statement_level) then
2412 fa_debug_pkg.add(l_calling_fn,'h_cur_fiscal_yesr',h_cur_fiscal_year, p_log_level_rec);
2413 fa_debug_pkg.add(l_calling_fn,'h_amort_fiscal_year',h_amort_fiscal_year, p_log_level_rec);
2414 fa_debug_pkg.add(l_calling_fn,'h_ds_fiscal_year',h_ds_fiscal_year, p_log_level_rec);
2415 fa_debug_pkg.add(l_calling_fn,'h_reserve_dpis_amort',h_reserve_dpis_amort, p_log_level_rec);
2416 fa_debug_pkg.add(l_calling_fn,'h_ytd_deprn',h_ytd_deprn, p_log_level_rec);
2417 fa_debug_pkg.add(l_calling_fn,'h_adj_eofy_reserve',h_adj_eofy_reserve, p_log_level_rec);
2418 fa_debug_pkg.add(l_calling_fn,'h_adj_reserve',h_adj_reserve, p_log_level_rec);
2419 end if;
2420
2421 -- calculate current eofy_reserve using amounts above
2422 if h_cur_fiscal_year <> h_amort_fiscal_year then
2423 if h_amort_fiscal_year = h_ds_fiscal_year then
2424 h_eofy_reserve := h_reserve_dpis_amort - h_ytd_deprn + nvl(h_adj_eofy_reserve,0) + nvl(h_adj_reserve,0);
2425 else
2426 h_eofy_reserve := h_reserve_dpis_amort;
2427 end if;
2428 end if;
2429
2430 px_group_reclass_options_rec.source_exp_amount := h_reserve_dpis_current - h_reserve_dpis_amort;
2431 -- px_group_reclass_options_rec.reserve_amount := h_reserve_dpis_amort;
2432
2433 else -- amortization start date is equal to the DPIS
2434
2435 h_eofy_reserve := 0;
2436 px_group_reclass_options_rec.source_exp_amount := h_reserve_dpis_current;
2437
2438 end if;
2439
2440 px_group_reclass_options_rec.source_eofy_reserve := h_eofy_reserve;
2441
2442 if (p_log_level_rec.statement_level) then
2443 fa_debug_pkg.add(l_calling_fn,'reserve_dpis_current',h_reserve_dpis_current, p_log_level_rec);
2444 fa_debug_pkg.add(l_calling_fn,'reserve_dpis_amort',h_reserve_dpis_amort, p_log_level_rec);
2445 fa_debug_pkg.add(l_calling_fn,'source_exp_amount',px_group_reclass_options_rec.source_exp_amount, p_log_level_rec);
2446 fa_debug_pkg.add(l_calling_fn,'eofy_reserve',px_group_reclass_options_rec.source_eofy_reserve, p_log_level_rec);
2447 end if;
2448
2449 -- x_eofy_reserve := h_eofy_reserve;
2450
2451 return true;
2452
2453 EXCEPTION
2454 when pop_mem_amt_err then
2455 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
2456 return false;
2457
2458 when others then
2459 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
2460 return false;
2461
2462 end populate_member_amounts;
2463
2464
2465 END FA_GROUP_RECLASS2_PVT;