[Home] [Help]
PACKAGE BODY: APPS.FA_CALC_DEPRN_BASIS2_PKG
Source
1 PACKAGE BODY FA_CALC_DEPRN_BASIS2_PKG as
2 /* $Header: faxcdb2b.pls 120.52.12010000.3 2008/10/24 08:07:31 gigupta ship $ */
3
4
5 --------------------------------------------------------------------------------
6 -- Procedure NON_STRICT_FLAT:
7 -- This procedure is the additional functionality for depreciable basis rule
8 -- 'Use Transaction Period Basis'.
9 --------------------------------------------------------------------------------
10
11 PROCEDURE NON_STRICT_FLAT (
12 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
13 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
14 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
15 is
16 l_calling_fn varchar2(50) := 'fa_calc_deprn_basis2_pkg.non_strict_flat';
17 l_rate_in_use NUMBER; -- Bug 6665510: FP: Japan Tax Reform Project
18 calc_basis_err exception;
19
20 begin
21 if p_log_level_rec.statement_level then
22 fa_debug_pkg.add('NSF', 'BEGIN', px_rule_in.calc_basis, p_log_level_rec);
23 end if;
24
25 ------------------------------------------------------
26 -- Event Type: EXPENSED_ADJ,AMORT_ADJ and AMORT_ADJ2
27 -----------------------------------------------------
28
29 if (px_rule_in.calc_basis = 'NBV' and
30 px_rule_in.method_type = 'FLAT') then
31 if (px_rule_in.event_type ='EXPENSED_ADJ') then
32 if Upper(px_rule_in.depreciate_flag) like 'N%' then
33 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost -
34 px_rule_in.current_total_rsv;
35 else
36 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost -
37 px_rule_in.hyp_total_rsv;
38 end if;
39
40 elsif (px_rule_in.event_type ='AMORT_ADJ' or
41 (px_rule_in.event_type ='AMORT_ADJ' and
42 (px_rule_in.asset_type ='GROUP' or
43 (px_rule_in.asset_type <> 'GROUP' and
44 px_rule_in.tracking_method='ALLOCATE')) and
45 nvl(px_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
46 and px_rule_in.calc_basis = 'NBV')) then -- Retirement for Group
47
48 --
49 -- Bug3463933: Added condition to set adjusted_cost to 0 if cost is 0.
50 --
51 if px_rule_in.cost = 0 then
52 px_rule_out.new_adjusted_cost := 0;
53 else
54 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost -
55 px_rule_in.current_total_rsv;
56 end if;
57
58 end if;
59 elsif (px_rule_in.method_type = 'PRODUCTION' and
60 fa_cache_pkg.fazcdrd_record.period_update_flag = 'Y') and -- ENERGY
61 (px_rule_in.event_type <> 'AMORT_ADJ3') then -- ENERGY
62 -- -- ENERGY
63 -- There should be no EXPENSED ADJUSTMENT for now and CURRRENT -- ENERGY
64 -- period amortized adjustment is only allowed. -- ENERGY
65 -- -- ENERGY
66 if px_rule_in.recoverable_cost = 0 then
67 px_rule_out.new_adjusted_cost := 0;
68 else
69 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost - -- ENERGY
70 px_rule_in.current_total_rsv - -- ENERGY
71 nvl(px_rule_in.unplanned_amount, 0) - -- ENERGY
72 nvl(px_rule_in.reserve_retired,0); -- ENERGY
73 end if;
74
75 elsif (px_rule_in.method_type = 'CALCULATED') and
76 (fa_cache_pkg.fazcdrd_record.rule_name = 'ENERGY PERIOD END BALANCE') and -- ENERGY
77 (px_rule_in.event_type <> 'AMORT_ADJ3') then
78
79 if px_rule_in.recoverable_cost = 0 then
80 px_rule_out.new_adjusted_cost := 0;
81 px_rule_out.new_raf := nvl(px_rule_in.old_raf,1);
82 else
83 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost - -- ENERGY
84 px_rule_in.current_total_rsv - -- ENERGY
85 nvl(px_rule_in.unplanned_amount, 0) - -- ENERGY
86 nvl(px_rule_in.reserve_retired,0); -- ENERGY
87 px_rule_out.new_raf := (px_rule_in.recoverable_cost -
88 px_rule_in.hyp_total_rsv)/px_rule_in.recoverable_cost;
89 end if;
90
91 end if;
92
93 if p_log_level_rec.statement_level then
94 fa_debug_pkg.add('NSF', 'px_rule_in.method_type', px_rule_in.method_type, p_log_level_rec);
95 fa_debug_pkg.add('NSF', 'fa_cache_pkg.fazcdrd_record.period_update_flag',
96 fa_cache_pkg.fazcdrd_record.period_update_flag, p_log_level_rec);
97 fa_debug_pkg.add('NSF', 'px_rule_in.recoverable_cost', px_rule_in.recoverable_cost, p_log_level_rec);
98 fa_debug_pkg.add('NSF', 'px_rule_in.current_total_rsv', px_rule_in.current_total_rsv, p_log_level_rec);
99 fa_debug_pkg.add('NSF', 'px_rule_in.unplanned_amount', px_rule_in.unplanned_amount, p_log_level_rec);
100 fa_debug_pkg.add('NSF', 'new_adjusted_cost', px_rule_out.new_adjusted_cost, p_log_level_rec);
101 fa_debug_pkg.add('NSF', 'reserve_retired', px_rule_in.reserve_retired, p_log_level_rec);
102 end if;
103
104 ------------------------------------------------------------
105 -- Event Type: RETIREMENT (Retirements)
106 ------------------------------------------------------------
107
108 if (px_rule_in.event_type ='RETIREMENT') then
109 if px_rule_in.recognize_gain_loss is not null
110 then -- Do not Recognize :Group and member
111
112 if (px_rule_in.calc_basis = 'NBV') then
113 px_rule_out.new_adjusted_cost :=
114 px_rule_in.recoverable_cost
115 - px_rule_in.current_total_rsv;
116 end if;
117
118 end if; -- End of Group and member assets' retirement
119
120 end if; -- End Retirement
121
122 -------------------------------------------------------------
123 -- Event Type: DEPRECIATE_FLAG_ADJ (IDLE Asset Control)
124 -------------------------------------------------------------
125 if (px_rule_in.event_type ='DEPRECIATE_FLAG_ADJ') then
126 -- Bug 6665510: FP: Japan Tax Reform Project (Start)
127 if nvl(fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag,'NO') = 'YES' then
128
129 if p_log_level_rec.statement_level then
130 fa_debug_pkg.add('faxcd2b', '+++ Inside Guarantee Logic', 'YES', p_log_level_rec);
131 end if;
132
133 SELECT rate_in_use
134 INTO l_rate_in_use
135 FROM fa_books
136 WHERE asset_id = px_rule_in.asset_id
137 AND book_type_code = px_rule_in.book_type_code
138 AND date_ineffective is null;
139
140 if p_log_level_rec.statement_level then
141 fa_debug_pkg.add('faxcdb2b', '+++ Revised Rate : ', fa_cache_pkg.fazcfor_record.revised_rate,
142 p_log_level_rec);
143 fa_debug_pkg.add('faxcdb2b', '+++ FA_Books.Rate : ', l_rate_in_use, p_log_level_rec);
144 end if;
145
146 if fa_cache_pkg.fazcfor_record.revised_rate = l_rate_in_use then
147 Null; -- Dont change adjusted cost.
148 else
149
150 if p_log_level_rec.statement_level then
151 fa_debug_pkg.add('faxcd2b', '+++ ORIGINAL RATE', 'YES', p_log_level_rec);
152 end if;
153
154 -- Bug# 7044005
155 px_rule_out.new_adjusted_cost :=
156 px_rule_in.recoverable_cost
157 - px_rule_in.eofy_reserve
158 - px_rule_in.impairment_reserve;
159
160 end if; -- revised_rate = l_rate_in_use
161 -- Bug 6665510: FP: Japan Tax Reform Project (End)
162
163 elsif (px_rule_in.calc_basis = 'NBV') then
164 px_rule_out.new_adjusted_cost :=
165 px_rule_in.recoverable_cost
166 - px_rule_in.current_total_rsv;
167 end if;
168 end if; -- End DEPRECIATE_FLAG_ADJ
169
170 if p_log_level_rec.statement_level then
171 fa_debug_pkg.add(fname=>'non_strict_flat',
172 element=>'new_adjusted_cost',
173 value=> px_rule_out.new_adjusted_cost,
174 p_log_level_rec => p_log_level_rec);
175 end if;
176
177 exception
178 when calc_basis_err then
179 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
180 ,p_log_level_rec => p_log_level_rec);
181
182 when others then
183 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
184 ,p_log_level_rec => p_log_level_rec);
185 raise;
186
187 end NON_STRICT_FLAT;
188
189 --------------------------------------------------------------------------------
190 -- Procedure FLAT_EXTENSION:
191 -- This procedure is the additional functionality for depreciable basis rule
192 -- 'Flat Rate Extension'.
193 --------------------------------------------------------------------------------
194
195 PROCEDURE FLAT_EXTENSION (
196 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
197 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
198 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
199 is
200
201 h_old_recoverable_cost NUMBER;
202 h_old_adjusted_cost NUMBER;
203 h_old_method_code VARCHAR2(12);
204 h_old_calc_basis VARCHAR2(4);
205 h_old_method_type VARCHAR2(10);
206 h_old_salvage_value NUMBER;
207
208 -- added new variables to fix bug 2303276.
209 h_old_adj_recoverable_cost NUMBER;
210 h_old_cost NUMBER;
211
212 l_temp_limit_remain NUMBER;
213 l_method_change_date DATE;
214 l_cost_at_method_change NUMBER;
215 l_reserve_at_method_change NUMBER;
216
217 l_nbv_at_method_change NUMBER;
218 l_adjustment_amount NUMBER;
219
220 l_amort_period number;
221 l_deprn_reserve number;
222 l_ytd_deprn number;
223
224 h_set_of_books_id number; /*Bug# 6933726 to hold value of set_of_books_id for MRC */
225
226 cursor GET_RESERVE is
227 select nvl(deprn_reserve,0), nvl(ytd_deprn,0)
228 from fa_deprn_summary
229 where asset_id = px_rule_in.asset_id
230 and period_counter = l_amort_period;
231
232 cursor GET_RESERVE_M is
233 select nvl(deprn_reserve,0), nvl(ytd_deprn,0)
234 from fa_deprn_summary_mrc_v
235 where asset_id = px_rule_in.asset_id
236 and period_counter = l_amort_period;
237
238 -- Replace original queries to cursors
239 cursor C_OLD_INFO is
240 select nvl(bk.recoverable_cost,0),
241 nvl(bk.adjusted_cost,0),
242 nvl(bk.salvage_value,0),
243 bk.deprn_method_code,
244 dm.deprn_basis_rule,
245 dm.rate_source_rule,
246 -----------------------------------------
247 -- Added following two columns
248 -- to fix bug 2303276
249 -----------------------------------------
250 nvl(bk.adjusted_recoverable_cost,0),
251 nvl(bk.cost,0)
252 from FA_BOOKS bk, FA_METHODS dm
253 where bk.deprn_method_code = dm.method_code
254 and nvl(bk.life_in_months, 0) = nvl(dm.life_in_months,0)
255 and bk.asset_id = px_rule_in.asset_id
256 and bk.book_type_code = px_rule_in.book_type_code
257 and bk.transaction_header_id_out is null;
258 -- MRC
259 cursor C_OLD_INFO_M
260 is
261 select nvl(bk.recoverable_cost,0),
262 nvl(bk.adjusted_cost,0),
263 nvl(bk.salvage_value,0),
264 bk.deprn_method_code,
265 dm.deprn_basis_rule,
266 dm.rate_source_rule,
267 nvl(bk.adjusted_recoverable_cost,0),
268 nvl(bk.cost,0)
269 from FA_BOOKS_MRC_V bk, FA_METHODS dm
270 where bk.deprn_method_code = dm.method_code
271 and nvl(bk.life_in_months, 0) = nvl(dm.life_in_months,0)
272 and bk.asset_id = px_rule_in.asset_id
273 and bk.book_type_code = px_rule_in.book_type_code
274 and bk.transaction_header_id_out is null;
275
276 cursor C_AMORT_PERIOD
277 is
278 select ap.period_counter
279 from fa_deprn_periods ap
280 where ap.book_type_code = px_rule_in.book_type_code
281 and ap.calendar_period_open_date
282 <= trunc(px_rule_in.amortization_start_date)
283 and nvl(ap.calendar_period_close_date,sysdate)
284 >= trunc(px_rule_in.amortization_start_date);
285
286 -- Select Method Change Date and Cost at Method Change
287 cursor C_MTC_BOOK
288 is
289 select nvl(h.amortization_start_date,h.transaction_date_entered),
290 nvl(bk.cost,0)
291 from fa_transaction_headers h,
292 fa_books bk
293 where h.transaction_header_id =
294 (select max(transaction_header_id_out)
295 from fa_books b,
296 fa_methods m
297 where b.book_type_code = px_rule_in.book_type_code
298 and b.asset_id = px_rule_in.asset_id
299 and b.deprn_method_code = m.method_code
300 and m.deprn_basis_rule = 'NBV')
301 and bk.book_type_code = px_rule_in.book_type_code
302 and bk.asset_id = px_rule_in.asset_id
303 and bk.transaction_header_id_in = h.transaction_header_id;
304
305 cursor C_MTC_BOOK_M
306 is
307 select nvl(h.amortization_start_date,h.transaction_date_entered),
308 nvl(bk.cost,0)
309 from fa_transaction_headers h,
310 fa_books bk
311 where h.transaction_header_id =
312 (select max(transaction_header_id_out)
313 from fa_books_mrc_v b,
314 fa_methods m
315 where b.book_type_code = px_rule_in.book_type_code
316 and b.asset_id = px_rule_in.asset_id
317 and b.deprn_method_code = m.method_code
318 and m.deprn_basis_rule = 'NBV')
319 and bk.book_type_code = px_rule_in.book_type_code
320 and bk.asset_id = px_rule_in.asset_id
321 and bk.transaction_header_id_in = h.transaction_header_id;
322
323 -- Select reserve at Method Change (Reserve at the beginning of fy of Method Change)
324 cursor C_MTC_SUM (p_method_change_date date)
325 is
326 select nvl(deprn_reserve,0) - nvl(ytd_deprn,0)
327 from fa_deprn_summary
328 where book_type_code = px_rule_in.book_type_code
329 and asset_id = px_rule_in.asset_id
330 and period_counter =
331 (select period_counter
332 from fa_deprn_periods
333 where book_type_code = px_rule_in.book_type_code
334 and calendar_period_open_date <= p_method_change_date
335 and nvl(calendar_period_close_date,sysdate) >= p_method_change_date);
336
337 cursor C_MTC_SUM_M (p_method_change_date date)
338 is
339 select nvl(deprn_reserve,0) - nvl(ytd_deprn,0)
340 from fa_deprn_summary_mrc_v
341 where book_type_code = px_rule_in.book_type_code
342 and asset_id = px_rule_in.asset_id
343 and period_counter =
344 (select period_counter
345 from fa_deprn_periods
346 where book_type_code = px_rule_in.book_type_code
347 and calendar_period_open_date <= p_method_change_date
348 and nvl(calendar_period_close_date,sysdate) >= p_method_change_date);
349
350 -- Added for group depreciation
351
352 -- Check exclude_fully_rsv_flag
353 CURSOR C_EXC_FULLY_RSV_FLAG
354 is
355 select exclude_fully_rsv_flag
356 from FA_BOOKS BK
357 where BK.ASSET_ID = px_rule_in.asset_id
358 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
359 and BK.TRANSACTION_HEADER_ID_OUT is null;
360
361 CURSOR C_EXC_FULLY_RSV_FLAG_M
362 is
363 select exclude_fully_rsv_flag
364 from FA_BOOKS_MRC_V BK
365 where BK.ASSET_ID = px_rule_in.asset_id
366 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
367 and BK.TRANSACTION_HEADER_ID_OUT is null;
368
369 cursor FULL_RSV_MEMBER_TRC (p_period_counter number)
370 is
371 select nvl(sum(TRC.adjusted_cost),0) fully_rsv_adjusted_cost,
372 nvl(sum(TRC.salvage_value),0) fully_rsv_salvage_value,
373 nvl(sum(TRC.recoverable_cost),0) fully_rsv_recoverable_cost,
374 nvl(sum(TRC.deprn_reserve),0) fully_rsv_deprn_reserve
375 from FA_TRACK_MEMBERS TRC
376 where TRC.GROUP_ASSET_ID = px_rule_in.asset_id
377 and TRC.PERIOD_COUNTER <= p_period_counter
378 and TRC.FULLY_RESERVED_FLAG='Y'
379 and nvl(TRC.SET_OF_BOOKS_ID,-99) = nvl(h_set_of_books_id,-99) /*Bug# 6933726 Added filter conition for MRC */
380 and TRC.MEMBER_ASSET_ID is not null;
381
382 cursor CUR_FULL_RSV_MEMBER_TRC (p_period_counter number)
383 is
384 select nvl(sum(TRC.adjusted_cost),0) fully_rsv_adjusted_cost,
385 nvl(sum(TRC.salvage_value),0) fully_rsv_salvage_value,
386 nvl(sum(TRC.recoverable_cost),0) fully_rsv_recoverable_cost,
387 nvl(sum(TRC.deprn_reserve),0) fully_rsv_deprn_reserve
388 from FA_TRACK_MEMBERS TRC
389 where TRC.GROUP_ASSET_ID = px_rule_in.asset_id
390 and TRC.PERIOD_COUNTER = p_period_counter
391 and TRC.FULLY_RESERVED_FLAG='Y'
392 and nvl(TRC.SET_OF_BOOKS_ID,-99) = nvl(h_set_of_books_id,-99) /*Bug# 6933726 Added filter conition for MRC */
393 and TRC.MEMBER_ASSET_ID is not null;
394
395 cursor MIN_TRC_PERIOD
396 is
397 select min(TRC.period_counter)
398 from FA_TRACK_MEMBERS TRC
399 where TRC.GROUP_ASSET_ID = px_rule_in.asset_id
400 and TRC.MEMBER_ASSET_ID is not null
401 and nvl(TRC.SET_OF_BOOKS_ID,-99) = nvl(h_set_of_books_id,-99); /*Bug# 6933726 Added filter conition for MRC */
402
403 cursor FULL_RSV_MEMBER_BK (p_period_counter number)
404 is
405 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
406 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
407 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost,
408 nvl(sum(DS.deprn_reserve),0) fully_rsv_deprn_reserve
409 from FA_BOOKS BK,
410 FA_DEPRN_SUMMARY DS
411 where BK.ASSET_ID = DS.ASSET_ID
412 and BK.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE
413 and BK.PERIOD_COUNTER_FULLY_RESERVED = DS.PERIOD_COUNTER
414 and BK.GROUP_ASSET_ID = px_rule_in.asset_id
415 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
416 and nvl(BK.PERIOD_COUNTER_FULLY_RESERVED,p_period_counter)
417 < p_period_counter
418 and BK.PERIOD_COUNTER_FULLY_RETIRED is null
419 and BK.DATE_INEFFECTIVE is null;
420
421 cursor FULL_RSV_MEMBER_BK_M (p_period_counter number)
422 is
423 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
424 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
425 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost,
426 nvl(sum(DS.deprn_reserve),0) fully_rsv_deprn_reserve
427 from FA_BOOKS_MRC_V BK,
428 FA_DEPRN_SUMMARY_MRC_V DS
429 where BK.ASSET_ID = DS.ASSET_ID
430 and BK.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE
431 and BK.PERIOD_COUNTER_FULLY_RESERVED = DS.PERIOD_COUNTER
432 and BK.GROUP_ASSET_ID = px_rule_in.asset_id
433 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
434 and nvl(BK.PERIOD_COUNTER_FULLY_RESERVED,p_period_counter)
435 < p_period_counter
436 and BK.PERIOD_COUNTER_FULLY_RETIRED is null
437 and BK.DATE_INEFFECTIVE is null;
438
439 cursor CUR_FULL_RSV_MEMBER_BK (p_period_counter number)
440 is
441 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
442 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
443 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost
444 from FA_BOOKS BK
445 where BK.GROUP_ASSET_ID = px_rule_in.asset_id
446 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
447 and BK.PERIOD_COUNTER_FULLY_RESERVED = p_period_counter
448 and BK.DATE_INEFFECTIVE is null;
449
450 cursor CUR_FULL_RSV_MEMBER_BK_M (p_period_counter number)
451 is
452 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
453 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
454 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost
455 from FA_BOOKS_MRC_V BK
456 where BK.GROUP_ASSET_ID = px_rule_in.asset_id
457 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
458 and BK.PERIOD_COUNTER_FULLY_RESERVED = p_period_counter
459 and BK.DATE_INEFFECTIVE is null;
460
461 cursor ALL_FULL_RSV_MEMBER_BK (p_period_counter number)
462 is
463 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
464 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
465 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost
466 from FA_BOOKS BK
467 where BK.GROUP_ASSET_ID = px_rule_in.asset_id
468 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
469 and BK.PERIOD_COUNTER_FULLY_RESERVED <= p_period_counter
470 and BK.DATE_INEFFECTIVE is null;
471
472 cursor ALL_FULL_RSV_MEMBER_BK_M (p_period_counter number)
473 is
474 select nvl(sum(BK.adjusted_cost),0) fully_rsv_adjusted_cost,
475 nvl(sum(BK.salvage_value),0) fully_rsv_salvage_value,
476 nvl(sum(BK.recoverable_cost),0) fully_rsv_recoverable_cost
477 from FA_BOOKS_MRC_V BK
478 where BK.GROUP_ASSET_ID = px_rule_in.asset_id
479 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
480 and BK.PERIOD_COUNTER_FULLY_RESERVED <= p_period_counter
481 and BK.DATE_INEFFECTIVE is null;
482
483 -- Get member's retired adjusted_cost
484 cursor C_GET_RET_ADJ_COST
485 is
486 select nvl(BK1.ADJUSTED_COST,0) - nvl(BK2.ADJUSTED_COST,0)
487 from FA_BOOKS BK1,
488 FA_BOOKS BK2
489 where BK1.TRANSACTION_HEADER_ID_OUT = px_rule_in.member_transaction_header_id
490 and BK1.GROUP_ASSET_ID is not null
491 and BK2.TRANSACTION_HEADER_ID_IN = px_rule_in.member_transaction_header_id
492 and BK2.GROUP_ASSET_ID is not null;
493
494 -- MRC
495 cursor C_GET_RET_ADJ_COST_M
496 is
497 select nvl(BK1.ADJUSTED_COST,0) - nvl(BK2.ADJUSTED_COST,0)
498 from FA_BOOKS_MRC_V BK1,
499 FA_BOOKS_MRC_V BK2
500 where BK1.TRANSACTION_HEADER_ID_OUT = px_rule_in.member_transaction_header_id
501 and BK1.GROUP_ASSET_ID is not null
502 and BK2.TRANSACTION_HEADER_ID_IN = px_rule_in.member_transaction_header_id
503 and BK2.GROUP_ASSET_ID is not null;
504
505 l_fully_rsv_adjusted_cost NUMBER :=0; -- Summary of Fully reserved member's adjusted cost
506 l_fully_rsv_salvage_value NUMBER :=0; -- Summary of Fully reserved member's salvage value
507 l_fully_rsv_recoverable_cost NUMBER :=0; -- Summary of Fully reserved member's recoverable cost
508 l_fully_rsv_deprn_reserve NUMBER :=0; -- Summary of Fully reserved member's deprn reserve
509
510 trc_fully_rsv_adjusted_cost NUMBER :=0; -- Summary of Fully reserved member's adjusted cost from FA_TRACK_MEMBERS
511 trc_fully_rsv_salvage_value NUMBER :=0; -- Summary of Fully reserved member's salvage value from FA_TRACK_MEMBERS
512 trc_fully_rsv_recoverable_cost NUMBER :=0; -- Summary of Fully reserved member's recoverable cost from FA_TRACK_MEMBERS
513 trc_fully_rsv_deprn_reserve NUMBER :=0; -- Summary of Fully reserved member's deprn reserve from FA_TRACK_MEMBERS
514
515 bk_fully_rsv_adjusted_cost NUMBER :=0; -- Summary of Fully reserved member's adjusted cost from FA_BOOKS
516 bk_fully_rsv_salvage_value NUMBER :=0; -- Summary of Fully reserved member's salvage value from FA_BOOKS
517 bk_fully_rsv_recoverable_cost NUMBER :=0; -- Summary of Fully reserved member's recoverable cost from FA_BOOKS
518 bk_fully_rsv_deprn_reserve NUMBER :=0; -- Summary of Fully reserved member's deprn reserve from FA_BOOKS
519
520 l_exclude_fully_rsv_flag VARCHAR2(1); -- Exclude fully Reserved flag
521 l_exclude_salvage_value_flag VARCHAR2(3); -- Exclude salvage value flag
522 l_trc_min_period_counter NUMBER :=null; -- Minimum period counter on FA_TRACK_MEMBERS
523 l_mem_ret_adj_cost NUMBER :=0; -- Member retirement adjusted cost
524
525 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.flat_extension';
526 l_function VARCHAR2(20) := 'flat_extension';
527
528 calc_basis_err exception;
529
530 begin
531
532 if (px_rule_in.method_type = 'FLAT') then
533 if px_rule_in.event_type ='AMORT_ADJ' and
534 not nvl(px_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT' then
535
536 --
537 -- Normal Adjustment
538 --
539 if (px_rule_in.calc_basis = 'COST') then
540
541 -----------------------------------------
542 -- Query old book and method information
543 ----------------------------------------
544 if px_rule_in.mrc_sob_type_code <>'R' then
545
546 OPEN C_OLD_INFO;
547 FETCH C_OLD_INFO into h_old_recoverable_cost,
548 h_old_adjusted_cost,
549 h_old_salvage_value,
550 h_old_method_code,
551 h_old_calc_basis,
552 h_old_method_type,
553 h_old_adj_recoverable_cost,
554 h_old_cost;
555 CLOSE C_OLD_INFO;
556
557 else -- MRC
558 OPEN C_OLD_INFO_M;
559 FETCH C_OLD_INFO_M into h_old_recoverable_cost,
560 h_old_adjusted_cost,
561 h_old_salvage_value,
562 h_old_method_code,
563 h_old_calc_basis,
564 h_old_method_type,
565 h_old_adj_recoverable_cost,
566 h_old_cost;
567 CLOSE C_OLD_INFO_M;
568
569 end if; -- End of MRC
570
571 if p_log_level_rec.statement_level then
572 fa_debug_pkg.add('deprn_basis2:FLAT_EXTENSION logic:',
573 'After the main SQL Statement', 'queried values', p_log_level_rec);
574 fa_debug_pkg.add(l_function, 'h_old_recoverable_cost', h_old_recoverable_cost, p_log_level_rec);
575 fa_debug_pkg.add(l_function, 'h_old_adjusted_cost', h_old_adjusted_cost, p_log_level_rec);
576 fa_debug_pkg.add(l_function, 'h_old_salvage_value', h_old_salvage_value, p_log_level_rec);
577 fa_debug_pkg.add(l_function, 'h_old_method_code', h_old_method_code, p_log_level_rec);
578 fa_debug_pkg.add(l_function, 'h_old_calc_basis', h_old_calc_basis, p_log_level_rec);
579 fa_debug_pkg.add(l_function, 'h_old_method_type', h_old_method_type, p_log_level_rec);
580 fa_debug_pkg.add(l_function, 'h_old_adj_recoverable_cost', h_old_adj_recoverable_cost, p_log_level_rec);
581 fa_debug_pkg.add(l_function, 'h_old_cost', h_old_cost, p_log_level_rec);
582 fa_debug_pkg.add(l_function, 'amortization_start_date',
583 px_rule_in.amortization_start_date, p_log_level_rec);
584 end if;
585
586 if (h_old_method_code <> px_rule_in.method_code and
587 h_old_calc_basis <> px_rule_in.calc_basis) then
588 -------------------------------------------------------------
589 -- If old and new method codes are not same
590 -- and old and new calculation basis are not same,
591 -- Depreciable basis is set the NBV at the fiscal year begin.
592 -------------------------------------------------------------
593
594 -------------------------------------------------------------
595 -- Bug2303276: following current total rsv and current total ytd
596 -- is not correct when the amortization start date is different
597 -- period from current.
598 -- Check it and if the period is different, query the correct
599 -- reserve and ytd with amortization start date
600 ------------------------------------------------------------
601 if px_rule_in.amortization_start_date is not null then
602
603 OPEN C_AMORT_PERIOD;
604 FETCH C_AMORT_PERIOD into l_amort_period;
605 CLOSE C_AMORT_PERIOD;
606
607 if p_log_level_rec.statement_level then
608 fa_debug_pkg.add(l_function, 'Amortization Start Date Period', l_amort_period, p_log_level_rec);
609 end if;
610
611 if px_rule_in.mrc_sob_type_code <>'R' then
612 open GET_RESERVE;
613 fetch GET_RESERVE into l_deprn_reserve,l_ytd_deprn;
614
615 if GET_RESERVE%FOUND then
616
617 if l_deprn_reserve is not null then
618 px_rule_in.current_total_rsv := nvl(l_deprn_reserve,0);
619 end if;
620
621 if l_ytd_deprn is not null then
622 px_rule_in.current_total_ytd := nvl(l_ytd_deprn,0);
623 end if;
624
625 end if;
626
627 close GET_RESERVE;
628 else -- MRC
629 open GET_RESERVE_M;
630 fetch GET_RESERVE_M into l_deprn_reserve,l_ytd_deprn;
631
632 if GET_RESERVE_M%FOUND then
633
634 if l_deprn_reserve is not null then
635 px_rule_in.current_total_rsv := nvl(l_deprn_reserve,0);
636 end if;
637
638 if l_ytd_deprn is not null then
639 px_rule_in.current_total_ytd := nvl(l_ytd_deprn,0);
640 end if;
641
642 end if;
643
644 close GET_RESERVE_M;
645
646 end if; -- End of MRC
647
648 if p_log_level_rec.statement_level then
649 fa_debug_pkg.add(l_function, 'Back Dated Amortization Logic',
650 'Replace Reserve and YTD', p_log_level_rec);
651 fa_debug_pkg.add(l_function, 'Deprn Reserve at Amortization Date',
652 px_rule_in.current_total_rsv, p_log_level_rec);
653 fa_debug_pkg.add(l_function, 'YTD at Amortization Date',
654 px_rule_in.current_total_ytd, p_log_level_rec);
655 end if;
656
657 end if; -- px_rule_in.amortization_start_date is not null
658
659 if p_log_level_rec.statement_level then
660 fa_debug_pkg.add(l_function, 'Method Change logic', 'NBV calculation', p_log_level_rec);
661 fa_debug_pkg.add(l_function, 'Deprn Reserve at Amortization Date',
662 px_rule_in.current_total_rsv, p_log_level_rec);
663 fa_debug_pkg.add(l_function, 'YTD at Amortization Date',
664 px_rule_in.current_total_ytd, p_log_level_rec);
665 end if;
666
667 -- Bug3481425:
668 -- Adjusted cost cannot be derived using rec cost, ytd and reserve in case
669 -- there was a retirement in this fiscal year
670 -- so use eofy reserve instead. The passed eofy reserve should include
671 -- retirement effect.
672 --
673 -- px_rule_out.new_adjusted_cost := nvl(px_rule_in.recoverable_cost,0) -
674 -- nvl(px_rule_in.current_total_rsv,0) +
675 -- nvl(px_rule_in.current_total_ytd,0);
676 --
677
678 px_rule_out.new_adjusted_cost := nvl(px_rule_in.recoverable_cost,0) -
679 nvl(px_rule_in.eofy_reserve, 0);
680
681 -------------------------------------------------------------
682 -- If the new adjusted_cost above is smaller than zero,
683 -- New Adjusted Cost must be calculated as follows;
684 -- New Adjusted Cost = NBV at Method Change - Deprn Limit Remaining.
685 -------------------------------------------------------------
686
687 if px_rule_out.new_adjusted_cost <= 0 then
688
689 l_temp_limit_remain := nvl(h_old_cost,0) - nvl(h_old_adj_recoverable_cost,0);
690
691 if p_log_level_rec.statement_level then
692 fa_debug_pkg.add(l_function, 'Method Change logic', 'Negative NBV calculation', p_log_level_rec);
693 fa_debug_pkg.add(l_function, 'new_adjusted_cost reducing by salvage value',
694 px_rule_out.new_adjusted_cost, p_log_level_rec);
695 fa_debug_pkg.add(l_function, 'h_old_salvage_value', h_old_salvage_value, p_log_level_rec);
696 fa_debug_pkg.add(l_function, 'l_temp_limit_remain', l_temp_limit_remain, p_log_level_rec);
697 end if;
698
699 px_rule_out.new_adjusted_cost := nvl(px_rule_out.new_adjusted_cost,0) +
700 nvl(h_old_salvage_value,0) -
701 nvl(l_temp_limit_remain,0);
702
703 end if;
704
705 -------------------------------------------------------------
706 -- If old and new calculation basis are same
707 -- and old and new method types are same
708 -- and old adjusted cost is not same as old recoverable cost,
709 -- Depreciable basis is set
710 -- 'old adjusted cost + adjustment amount
711 -- (new salvage value - old salvage value)
712 -------------------------------------------------------------
713
714 elsif (h_old_calc_basis = px_rule_in.calc_basis and
715 h_old_method_type = px_rule_in.method_type and
716 h_old_adjusted_cost <> h_old_recoverable_cost) then
717
718 -------------------------------------------------------------
719 -- If old and new calculation basis are same
720 -- and old and new method types are same
721 -- and old adjusted cost is not same as old recoverable cost,
722 -- Depreciable basis is set
723 -- 'old adjusted cost + adjustment amount
724 -- (new salvage value - old salvage value)
725 -------------------------------------------------------------
726
727 if p_log_level_rec.statement_level then
728 fa_debug_pkg.add(l_function, 'Cost Change logic', 'before SQL statement', p_log_level_rec);
729 end if;
730
731 ---------------------------------------------------------------
732 -- Cost Adjustment Case Preparation
733 -- Select Reserve-YTD reserve at Method Change
734 ---------------------------------------------------------------
735 if px_rule_in.mrc_sob_type_code <>'R' then
736
737 -- Select Method Change Date and Cost at Method Change
738 OPEN C_MTC_BOOK;
739 FETCH C_MTC_BOOK into l_method_change_date, l_cost_at_method_change;
740 CLOSE C_MTC_BOOK;
741
742 -- Select reserve at Method Change (Reserve at the beginning of fy of Method Change)
743 OPEN C_MTC_SUM (l_method_change_date);
744 FETCH C_MTC_SUM into l_reserve_at_method_change;
745 CLOSE C_MTC_SUM;
746
747 else -- MRC
748 -- Select Method Change Date and Cost at Method Change
749 OPEN C_MTC_BOOK_M;
750 FETCH C_MTC_BOOK_M into l_method_change_date, l_cost_at_method_change;
751 CLOSE C_MTC_BOOK_M;
752
753 -- Select reserve at Method Change (Reserve at the beginning of fy of Method Change)
754 OPEN C_MTC_SUM_M (l_method_change_date);
755 FETCH C_MTC_SUM_M into l_reserve_at_method_change;
756 CLOSE C_MTC_SUM_M;
757
758 end if; -- End of MRC
759
760 --
761 -- NBV at Method Change --
762 --
763 l_nbv_at_method_change := nvl(l_cost_at_method_change,0) -
764 nvl(l_reserve_at_method_change,0);
765
766 --
767 -- Difference between Cost at Method Change and New Cost --
768 --
769 l_adjustment_amount := nvl(px_rule_in.cost,0) - nvl(l_cost_at_method_change,0);
770
771 -- Check if the NBV at Method Change + difference > Salvage Value --
772
773 if p_log_level_rec.statement_level then
774 fa_debug_pkg.add(l_function, 'Check Salvage Value logic', 'before IF-Clause', p_log_level_rec);
775 fa_debug_pkg.add(l_function, 'l_nbv_at_method_change', l_nbv_at_method_change, p_log_level_rec);
776 fa_debug_pkg.add(l_function, 'l_adjustment_amount', l_adjustment_amount, p_log_level_rec);
777 fa_debug_pkg.add(l_function, 'salvage_value', px_rule_in.salvage_value, p_log_level_rec);
778 end if;
779
780 -- Check if the NBV at Method Change + difference > Salvage Value --
781 -- Because user cannot make the Cost Change with small adjustment amount
782 -- (Salvage Value > NBV). So the following formula is not considered
783 -- such situation.
784 px_rule_out.new_adjusted_cost := nvl(l_nbv_at_method_change,0) +
785 nvl(l_adjustment_amount,0) -
786 nvl(px_rule_in.salvage_value,0);
787
788 if nvl(l_nbv_at_method_change,0) <> 0 then
789 px_rule_in.use_old_adj_cost_flag :='Y';
790 end if;
791
792 end if; -- End if Method Change or Cost Adjustment
793
794 end if; -- End if Deprn Basis = COST
795
796 elsif px_rule_in.event_type ='AMORT_ADJ' and
797 px_rule_in.asset_type ='GROUP' and
798 nvl(px_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT' then
799 --
800 -- Retirement for group asset
801 --
802 if px_rule_in.calc_basis = 'COST' and
803 px_rule_in.tracking_method is not null then
804 --
805 -- When group asset has tracking method and cost base,
806 -- reduce member's retired adjuted cost from group's adjusted_cost.
807 --
808 if px_rule_in.mrc_sob_type_code <>'R' then
809 OPEN C_GET_RET_ADJ_COST;
810 FETCH C_GET_RET_ADJ_COST into l_mem_ret_adj_cost;
811 CLOSE C_GET_RET_ADJ_COST;
812 else
813 OPEN C_GET_RET_ADJ_COST_M;
814 FETCH C_GET_RET_ADJ_COST_M into l_mem_ret_adj_cost;
815 CLOSE C_GET_RET_ADJ_COST_M;
816 end if;
817
818 if p_log_level_rec.statement_level then
819 fa_debug_pkg.add(l_function, 'l_mem_ret_adj_cost', l_mem_ret_adj_cost, p_log_level_rec);
820 end if;
821
822 px_rule_out.new_adjusted_cost := nvl(px_rule_in.old_adjusted_cost,0) -
823 nvl(l_mem_ret_adj_cost,0);
824 px_rule_in.use_old_adj_cost_flag :='Y';
825
826 end if; -- End of Cost and tracking method is null
827 end if; -- End if Event Type = AMORT_ADJ
828
829 -------------------------------------------------------------
830 -- Event Type: AFTER_DEPRN
831 --
832 -- Only at fiscal year end, update adjusted_cost of NBV base
833 -------------------------------------------------------------
834 if (px_rule_in.event_type ='AFTER_DEPRN') then
835
836 -- Reset old adjusted cost flag
837 px_rule_in.use_old_adj_cost_flag :=null;
838
839 if px_rule_in.eofy_flag ='Y' and
840 px_rule_in.calc_basis ='NBV' then
841 --
842 -- fiscal year end update
843 --
844 px_rule_out.new_adjusted_cost := px_rule_in.recoverable_cost -
845 px_rule_in.current_total_rsv;
846
847 else -- period update
848
849 px_rule_out.new_adjusted_cost :=px_rule_in.old_adjusted_cost;
850 px_rule_in.use_old_adj_cost_flag :='Y';
851
852 end if; -- End of eofy flag
853
854 px_rule_out.new_formula_factor := px_rule_in.old_formula_factor;
855 px_rule_out.new_raf := px_rule_in.old_raf;
856 end if; -- End event type: AFTER_DEPRN
857
858
859 if p_log_level_rec.statement_level then
860 fa_debug_pkg.add(l_function,
861 'new_adjusted_cost before calculating fully reserved member',
862 px_rule_out.new_adjusted_cost, p_log_level_rec);
863 end if;
864
865 -------------------------------------------------------------
866 -- Reduce depreciable basis of fully reserved member assets
867 -- from depreciable basis of group asset
868 -------------------------------------------------------------
869 if px_rule_in.asset_type = 'GROUP' and
870 px_rule_in.tracking_method is not null and
871 not (px_rule_in.calc_basis='NBV' and
872 px_rule_in.tracking_method='CALCULATE') then
873
874 if px_rule_in.mrc_sob_type_code <>'R' then
875
876 OPEN C_EXC_FULLY_RSV_FLAG;
877 FETCH C_EXC_FULLY_RSV_FLAG into l_exclude_fully_rsv_flag;
878 CLOSE C_EXC_FULLY_RSV_FLAG;
879 /*Bug# 6933726 Added to fetch set_of_books_id from cache*/
880 h_set_of_books_id:= FA_CACHE_PKG.fazcbc_record.set_of_books_id;
881
882 else -- MRC
883
884 OPEN C_EXC_FULLY_RSV_FLAG_M;
885 FETCH C_EXC_FULLY_RSV_FLAG_M into l_exclude_fully_rsv_flag;
886 CLOSE C_EXC_FULLY_RSV_FLAG_M;
887 /*Bug# 6933726 Added to fetch set_of_books_id from cache*/
888 h_set_of_books_id:= nvl(FA_CACHE_PKG.fazcbcs_record.set_of_books_id,
889 FA_CACHE_PKG.fazcbc_record.set_of_books_id);
890
891 end if;
892
893 if p_log_level_rec.statement_level then
894 fa_debug_pkg.add(l_function, 'l_exclude_fully_rsv_flag', l_exclude_fully_rsv_flag, p_log_level_rec);
895 fa_debug_pkg.add(l_function, 'use_old_adj_cost_flag', px_rule_in.use_old_adj_cost_flag, p_log_level_rec);
896 end if;
897
898 if l_exclude_fully_rsv_flag ='Y' then
899
900 -- Initialize before starting loop
901 trc_fully_rsv_adjusted_cost := 0;
902 trc_fully_rsv_salvage_value := 0;
903 trc_fully_rsv_recoverable_cost := 0;
904 trc_fully_rsv_deprn_reserve := 0;
905
906
907 if px_rule_in.event_type not in ('AFTER_DEPRN2','AMOR_ADJ2',
908 'AMORT_ADJ3','UNPLANNED_ADJ') then
909 if px_rule_in.tracking_method='ALLOCATE' then
910
911 if nvl(px_rule_in.use_old_adj_cost_flag,'N')='Y' then
912
913 if nvl(px_rule_in.used_by_adjustment,'NULL') <> 'ADJUSTMENT' then
914
915 open CUR_FULL_RSV_MEMBER_TRC (px_rule_in.period_counter);
916 fetch CUR_FULL_RSV_MEMBER_TRC into trc_fully_rsv_adjusted_cost,
917 trc_fully_rsv_salvage_value,
918 trc_fully_rsv_recoverable_cost,
919 trc_fully_rsv_deprn_reserve;
920 close CUR_FULL_RSV_MEMBER_TRC;
921
922 else -- used_by_adjsutment ='ADJUSTMENT'
923
924 FOR i IN 1 .. fa_track_member_pvt.p_track_member_table.COUNT LOOP
925 if fa_track_member_pvt.p_track_member_table(i).group_asset_id
926 = px_rule_in.asset_id and
927 fa_track_member_pvt.p_track_member_table(i).period_counter
928 = px_rule_in.period_counter and
929 nvl(fa_track_member_pvt.p_track_member_table(i).fully_reserved_flag,'N') = 'Y' then
930
931 trc_fully_rsv_adjusted_cost := trc_fully_rsv_adjusted_cost +
932 fa_track_member_pvt.p_track_member_table(i).adjusted_cost;
933 trc_fully_rsv_salvage_value := trc_fully_rsv_salvage_value +
934 fa_track_member_pvt.p_track_member_table(i).salvage_value;
935 trc_fully_rsv_recoverable_cost := trc_fully_rsv_recoverable_cost +
936 fa_track_member_pvt.p_track_member_table(i).recoverable_cost;
937 trc_fully_rsv_deprn_reserve := trc_fully_rsv_deprn_reserve +
938 fa_track_member_pvt.p_track_member_table(i).deprn_reserve;
939
940 end if;
941
942 END LOOP;
943 end if; -- nvl(px_rule_in.used_by_adjustment,'NULL') <> 'ADJUSTMENT'
944
945 else -- Not use old_adjusted_cost
946
947 if nvl(px_rule_in.used_by_adjustment,'NULL') <> 'ADJUSTMENT' then
948 --
949 -- Query fully Reserved member assets info from FA_TRACK_MEMBERS
950 --
951 open FULL_RSV_MEMBER_TRC (px_rule_in.period_counter);
952 fetch FULL_RSV_MEMBER_TRC into trc_fully_rsv_adjusted_cost,
953 trc_fully_rsv_salvage_value,
954 trc_fully_rsv_recoverable_cost,
955 trc_fully_rsv_deprn_reserve;
956 close FULL_RSV_MEMBER_TRC;
957
958 --
959 -- Query Minimum period counter on FA_TRACK_MEMBERS
960 --
961 open MIN_TRC_PERIOD;
962 fetch MIN_TRC_PERIOD into l_trc_min_period_counter;
963
964 if MIN_TRC_PERIOD%NOTFOUND then
965 l_trc_min_period_counter := px_rule_in.period_counter + 1;
966 end if;
967
968 close MIN_TRC_PERIOD;
969
970 else -- used_by_adjustment 'ADJUSTMENT'
971
972 FOR i IN 1 .. fa_track_member_pvt.p_track_member_table.COUNT LOOP
973 if fa_track_member_pvt.p_track_member_table(i).group_asset_id
974 = px_rule_in.asset_id and
975 fa_track_member_pvt.p_track_member_table(i).period_counter
976 <= px_rule_in.period_counter and
977 nvl(fa_track_member_pvt.p_track_member_table(i).fully_reserved_flag,'N') = 'Y' then
978
979 trc_fully_rsv_adjusted_cost := trc_fully_rsv_adjusted_cost +
980 fa_track_member_pvt.p_track_member_table(i).adjusted_cost;
981 trc_fully_rsv_salvage_value := trc_fully_rsv_salvage_value +
982 fa_track_member_pvt.p_track_member_table(i).salvage_value;
983 trc_fully_rsv_recoverable_cost := trc_fully_rsv_recoverable_cost +
984 fa_track_member_pvt.p_track_member_table(i).recoverable_cost;
985 trc_fully_rsv_deprn_reserve := trc_fully_rsv_deprn_reserve +
986 fa_track_member_pvt.p_track_member_table(i).deprn_reserve;
987
988 end if;
989
990 END LOOP;
991
992 FOR i IN 1 .. fa_track_member_pvt.p_track_member_table.COUNT LOOP
993 if fa_track_member_pvt.p_track_member_table(i).period_counter <
994 nvl(l_trc_min_period_counter,
995 fa_track_member_pvt.p_track_member_table(i).period_counter+1) and
996 fa_track_member_pvt.p_track_member_table(i).member_asset_id is not null then
997
998 l_trc_min_period_counter := fa_track_member_pvt.p_track_member_table(i).period_counter;
999 end if;
1000 END LOOP;
1001
1002 if l_trc_min_period_counter is null then
1003 l_trc_min_period_counter := px_rule_in.period_counter + 1;
1004 end if;
1005
1006 end if; -- nvl(px_rule_in.used_by_adjustment,'NULL') <> 'ADJUSTMENT'
1007
1008 if px_rule_in.mrc_sob_type_code <>'R' then
1009 open FULL_RSV_MEMBER_BK (l_trc_min_period_counter);
1010 fetch FULL_RSV_MEMBER_BK into bk_fully_rsv_adjusted_cost,
1011 bk_fully_rsv_salvage_value,
1012 bk_fully_rsv_recoverable_cost,
1013 bk_fully_rsv_deprn_reserve;
1014 close FULL_RSV_MEMBER_BK;
1015 else -- MRC
1016 open FULL_RSV_MEMBER_BK_M (l_trc_min_period_counter);
1017 fetch FULL_RSV_MEMBER_BK_M into bk_fully_rsv_adjusted_cost,
1018 bk_fully_rsv_salvage_value,
1019 bk_fully_rsv_recoverable_cost,
1020 bk_fully_rsv_deprn_reserve;
1021 close FULL_RSV_MEMBER_BK_M;
1022 end if; -- End of MRC
1023
1024 end if; -- End of use_old_adj_cost_flag
1025
1026 else -- tracking method is 'CALCULATE'
1027
1028 if px_rule_in.use_old_adj_cost_flag='Y' then
1029 if px_rule_in.mrc_sob_type_code <>'R' then
1030 open CUR_FULL_RSV_MEMBER_BK (px_rule_in.period_counter);
1031 fetch CUR_FULL_RSV_MEMBER_BK into bk_fully_rsv_adjusted_cost,
1032 bk_fully_rsv_salvage_value,
1033 bk_fully_rsv_recoverable_cost;
1034 close CUR_FULL_RSV_MEMBER_BK;
1035 else -- MRC
1036 open CUR_FULL_RSV_MEMBER_BK_M (px_rule_in.period_counter);
1037 fetch CUR_FULL_RSV_MEMBER_BK_M into bk_fully_rsv_adjusted_cost,
1038 bk_fully_rsv_salvage_value,
1039 bk_fully_rsv_recoverable_cost;
1040 close CUR_FULL_RSV_MEMBER_BK_M;
1041 end if; -- End of MRC
1042
1043 else -- use_old_adj_cost_flag is 'N'
1044 if px_rule_in.mrc_sob_type_code <>'R' then
1045 open ALL_FULL_RSV_MEMBER_BK (px_rule_in.period_counter);
1046 fetch ALL_FULL_RSV_MEMBER_BK into bk_fully_rsv_adjusted_cost,
1047 bk_fully_rsv_salvage_value,
1048 bk_fully_rsv_recoverable_cost;
1049 close ALL_FULL_RSV_MEMBER_BK;
1050 else -- MRC
1051 open ALL_FULL_RSV_MEMBER_BK_M (px_rule_in.period_counter);
1052 fetch ALL_FULL_RSV_MEMBER_BK_M into bk_fully_rsv_adjusted_cost,
1053 bk_fully_rsv_salvage_value,
1054 bk_fully_rsv_recoverable_cost;
1055 close ALL_FULL_RSV_MEMBER_BK_M;
1056 end if; -- End of MRC
1057
1058 end if; -- px_rule_in.use_old_adj_cost_flag='Y'
1059
1060 end if; -- px_rule_in.tracking_method='ALLOCATE'
1061
1062 l_fully_rsv_adjusted_cost := nvl(trc_fully_rsv_adjusted_cost,0) +
1063 nvl(bk_fully_rsv_adjusted_cost,0);
1064 l_fully_rsv_salvage_value := nvl(trc_fully_rsv_salvage_value,0) +
1065 nvl(bk_fully_rsv_salvage_value,0);
1066 l_fully_rsv_recoverable_cost := nvl(trc_fully_rsv_recoverable_cost,0) +
1067 nvl(bk_fully_rsv_recoverable_cost,0);
1068 l_fully_rsv_deprn_reserve := nvl(trc_fully_rsv_deprn_reserve,0) +
1069 nvl(bk_fully_rsv_deprn_reserve,0);
1070
1071 end if; -- End of Event type conditions
1072
1073 if p_log_level_rec.statement_level then
1074 fa_debug_pkg.add(l_function, 'l_fully_rsv_adjusted_cost', l_fully_rsv_adjusted_cost, p_log_level_rec);
1075 fa_debug_pkg.add(l_function, 'l_fully_rsv_salvage_value', l_fully_rsv_salvage_value, p_log_level_rec);
1076 fa_debug_pkg.add(l_function, 'l_fully_rsv_recoverable_cost', l_fully_rsv_recoverable_cost, p_log_level_rec);
1077 fa_debug_pkg.add(l_function, 'l_fully_rsv_deprn_reserve', l_fully_rsv_deprn_reserve, p_log_level_rec);
1078 end if;
1079
1080 if px_rule_in.eofy_flag='Y' and
1081 px_rule_in.calc_basis ='NBV' then
1082 l_fully_rsv_adjusted_cost := nvl(l_fully_rsv_recoverable_cost,0) -
1083 nvl(l_fully_rsv_deprn_reserve,0);
1084 end if;
1085
1086 -------------------------------------------------------------------------
1087 -- Check Exclude salvage value flag and calculate new adjusted cost
1088 --
1089 -- If group asset's methods has the exclude salvage value flag:YES,
1090 -- Reduce fully reserved adjusted cost and salvage value
1091 -- new adjusted cost.
1092 -- If not, Reduce fully reserved adjusted cost only
1093 -------------------------------------------------------------------------
1094
1095 if fa_cache_pkg.fazccmt(px_rule_in.method_code,
1096 px_rule_in.life_in_months
1097 ,p_log_level_rec => p_log_level_rec) then
1098
1099 if p_log_level_rec.statement_level then
1100 fa_debug_pkg.add(l_function, 'fazccmt', 'Called', p_log_level_rec);
1101 end if;
1102
1103 l_exclude_salvage_value_flag := fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag;
1104 end if;
1105
1106 if Upper(l_exclude_salvage_value_flag) like 'Y%' and
1107 px_rule_in.calc_basis= 'NBV' then
1108
1109 px_rule_out.new_adjusted_cost := nvl(px_rule_out.new_adjusted_cost,
1110 px_rule_in.old_adjusted_cost) -
1111 nvl(l_fully_rsv_adjusted_cost,0) -
1112 nvl(l_fully_rsv_salvage_value,0);
1113
1114 else -- Exclude salvage value flag is Off or Calc basis is COST
1115
1116 px_rule_out.new_adjusted_cost := nvl(px_rule_out.new_adjusted_cost,
1117 px_rule_in.old_adjusted_cost) -
1118 nvl(l_fully_rsv_adjusted_cost,0);
1119
1120 end if; -- End Exclude salvage value flag check
1121
1122 end if; -- Exclude fully reserved flag
1123 end if; -- asset type is GROUP
1124
1125 end if; -- (px_rule_in.method_type = 'FLAT')
1126
1127 if p_log_level_rec.statement_level then
1128 fa_debug_pkg.add(l_function, 'new_adjusted_cost', px_rule_out.new_adjusted_cost, p_log_level_rec);
1129 end if;
1130
1131 if nvl(l_fully_rsv_adjusted_cost,0) <> 0 then
1132 px_rule_out.new_deprn_rounding_flag := 'ADJ';
1133 end if;
1134
1135 exception
1136 when calc_basis_err then
1137 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1138 ,p_log_level_rec => p_log_level_rec);
1139
1140 when no_data_found then
1141 if p_log_level_rec.statement_level then
1142 fa_debug_pkg.add(fname=>'flat_extension',
1143 element=>'Warning',
1144 value=> SQLERRM,
1145 p_log_level_rec => p_log_level_rec);
1146 end if;
1147
1148 when others then
1149 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1150 ,p_log_level_rec => p_log_level_rec);
1151 raise;
1152
1153 end FLAT_EXTENSION;
1154
1155 --------------------------------------------------------------------------------
1156 -- Procedure PERIOD_AVERAGE:
1157 -- This procedure is the additional functionality for depreciable basis rule
1158 -- 'Period End Average'.
1159 --------------------------------------------------------------------------------
1160
1161 PROCEDURE PERIOD_AVERAGE (
1162 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
1163 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
1164 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1165 is
1166
1167 -- Get current period reseve adjustments for member and standalone
1168 cursor GET_DEPRN_RSV is
1169 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1170 'RESERVE',
1171 decode(adj.debit_credit_flag,
1172 'DR',adj.adjustment_amount,
1173 'CR',-adj.adjustment_amount))),0) current_period_reserve
1174 from fa_adjustments adj
1175 where adj.asset_id = px_rule_in.asset_id
1176 and adj.book_type_code = px_rule_in.book_type_code
1177 and adj.period_counter_created = px_rule_in.period_counter
1178 ;
1179
1180 cursor GET_DEPRN_RSV_M is
1181 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1182 'RESERVE',
1183 decode(adj.debit_credit_flag,
1184 'DR',adj.adjustment_amount,
1185 'CR',-adj.adjustment_amount))),0) current_period_reserve
1186 from fa_adjustments_mrc_v adj
1187 where adj.asset_id = px_rule_in.asset_id
1188 and adj.book_type_code = px_rule_in.book_type_code
1189 and adj.period_counter_created = px_rule_in.period_counter
1190 ;
1191
1192 -- Get current period reseve adjustments for group
1193 cursor GP_GET_DEPRN_RSV is
1194 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1195 'RESERVE',
1196 decode(adj.debit_credit_flag,
1197 'DR',adj.adjustment_amount,
1198 'CR',-adj.adjustment_amount))),0) current_period_reserve
1199 from fa_adjustments adj,
1200 fa_transaction_headers th1,
1201 fa_transaction_headers th2
1202 where adj.asset_id = px_rule_in.asset_id
1203 and adj.book_type_code = px_rule_in.book_type_code
1204 and adj.period_counter_created = px_rule_in.period_counter
1205 and adj.transaction_header_id = th1.transaction_header_id
1206 and th1.member_transaction_header_id (+) = th2.transaction_header_id
1207 and (th1.transaction_type_code <>'GROUP ADJUSTMENT'
1208 and th1.trx_reference_id is null)
1209 and exists (select th2.asset_id
1210 from FA_BOOKS bk
1211 where th2.asset_id = bk.asset_id
1212 and bk.book_type_code = px_rule_in.book_type_code
1213 and bk.group_asset_id= px_rule_in.asset_id
1214 and bk.date_ineffective is null);
1215
1216 cursor GP_GET_DEPRN_RSV_M is
1217 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1218 'RESERVE',
1219 decode(adj.debit_credit_flag,
1220 'DR',adj.adjustment_amount,
1221 'CR',-adj.adjustment_amount))),0) current_period_reserve
1222 from fa_adjustments_mrc_v adj,
1223 fa_transaction_headers th1,
1224 fa_transaction_headers th2
1225 where adj.asset_id = px_rule_in.asset_id
1226 and adj.book_type_code = px_rule_in.book_type_code
1227 and adj.period_counter_created = px_rule_in.period_counter
1228 and adj.transaction_header_id = th1.transaction_header_id
1229 and th1.member_transaction_header_id (+) = th2.transaction_header_id
1230 and (th1.transaction_type_code <>'GROUP ADJUSTMENT'
1231 and th1.trx_reference_id is null)
1232 and exists (select th2.asset_id
1233 from FA_BOOKS_MRC_V bk
1234 where th2.asset_id = bk.asset_id
1235 and bk.book_type_code = px_rule_in.book_type_code
1236 and bk.group_asset_id= px_rule_in.asset_id
1237 and bk.date_ineffective is null);
1238
1239 -- Get current deprn expense
1240 cursor GET_DEPRN_EXP is
1241 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1242 'EXPENSE',
1243 decode(adj.debit_credit_flag,
1244 'DR',adj.adjustment_amount,
1245 'CR',-adj.adjustment_amount))),0) current_period_expense
1246 from fa_adjustments adj,
1247 fa_transaction_headers th,
1248 fa_deprn_periods dp
1249 where adj.asset_id = px_rule_in.asset_id
1250 and adj.book_type_code = px_rule_in.book_type_code
1251 and dp.book_type_code = px_rule_in.book_type_code
1252 and adj.transaction_header_id = th.transaction_header_id
1253 and th.transaction_date_entered
1254 between dp.calendar_period_open_date and dp.calendar_period_close_date
1255 and dp.period_counter = px_rule_in.period_counter
1256 ;
1257
1258 cursor GET_DEPRN_EXP_M is
1259 select nvl(sum(DECODE(ADJ.ADJUSTMENT_TYPE,
1260 'EXPENSE',
1261 decode(adj.debit_credit_flag,
1262 'DR',adj.adjustment_amount,
1263 'CR',-adj.adjustment_amount))),0) current_period_expense
1264 from fa_adjustments_mrc_v adj,
1265 fa_transaction_headers th,
1266 fa_deprn_periods_mrc_v dp
1267 where adj.asset_id = px_rule_in.asset_id
1268 and adj.book_type_code = px_rule_in.book_type_code
1269 and dp.book_type_code = px_rule_in.book_type_code
1270 and adj.transaction_header_id = th.transaction_header_id
1271 and th.transaction_date_entered
1272 between dp.calendar_period_open_date and dp.calendar_period_close_date
1273 and dp.period_counter = px_rule_in.period_counter
1274 ;
1275
1276 l_current_period_expense NUMBER :=0; -- Depreciation Expense at this period
1277 l_current_period_reserve NUMBER :=0; -- Reserve at this period
1278 l_eop_reserve NUMBER :=0; -- End of prior period reserve
1279 l_exclude_salvage_value_flag VARCHAR2(3) := null; -- Exclude salvage value flag
1280 l_retirement_reserve NUMBER :=0; -- Retirement Reserve
1281
1282 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.period_average';
1283
1284 calc_basis_err exception;
1285
1286 begin
1287
1288 -------------------------------------------------------
1289 -- This rule is FLAT method type
1290 -- and Formula NBV base only
1291 -------------------------------------------------------
1292 if (px_rule_in.method_type = 'FLAT' OR
1293 (px_rule_in.method_type = 'FORMULA' AND
1294 px_rule_in.calc_basis = 'NBV')) then
1295
1296 if px_rule_in.calc_basis = 'NBV' then
1297
1298
1299 ---------------------------------------------
1300 -- All Event types:
1301 -- Get the current period adjusted reserve
1302 -- and the current period adjusted expense
1303 -- And calculate Prior Period Reserve
1304 ---------------------------------------------
1305 if px_rule_in.mrc_sob_type_code <>'R' then
1306
1307 if px_rule_in.asset_type ='GROUP'
1308 then
1309 open GP_GET_DEPRN_RSV;
1310 fetch GP_GET_DEPRN_RSV into l_current_period_reserve;
1311 close GP_GET_DEPRN_RSV;
1312 else -- member and standalone
1313 open GET_DEPRN_RSV;
1314 fetch GET_DEPRN_RSV into l_current_period_reserve;
1315 close GET_DEPRN_RSV;
1316 end if;
1317
1318 open GET_DEPRN_EXP;
1319 fetch GET_DEPRN_EXP into l_current_period_expense;
1320 close GET_DEPRN_EXP;
1321
1322 else -- MRC
1323
1324 if px_rule_in.asset_type ='GROUP'
1325 then
1326 open GP_GET_DEPRN_RSV_M;
1327 fetch GP_GET_DEPRN_RSV_M into l_current_period_reserve;
1328 close GP_GET_DEPRN_RSV_M;
1329 else -- member and standalone
1330 open GET_DEPRN_RSV_M;
1331 fetch GET_DEPRN_RSV_M into l_current_period_reserve;
1332 close GET_DEPRN_RSV_M;
1333 end if;
1334
1335 open GET_DEPRN_EXP_M;
1336 fetch GET_DEPRN_EXP_M into l_current_period_expense;
1337 close GET_DEPRN_EXP_M;
1338
1339 end if;
1340
1341 l_eop_reserve := nvl(px_rule_in.current_total_rsv,0)
1342 + nvl(px_rule_in.reserve_retired,0)
1343 - nvl(l_current_period_expense,0)
1344 + nvl(l_current_period_reserve,0);
1345
1346 if p_log_level_rec.statement_level then
1347 fa_debug_pkg.add(fname=>'period_average',
1348 element=>'l_current_period_expense',
1349 value=> l_current_period_expense,
1350 p_log_level_rec => p_log_level_rec);
1351 fa_debug_pkg.add(fname=>'period_average',
1352 element=>'l_current_period_reserve',
1353 value=> l_current_period_reserve,
1354 p_log_level_rec => p_log_level_rec);
1355 fa_debug_pkg.add(fname=>'period_average',
1356 element=>'l_eop_reserve',
1357 value=> l_eop_reserve,
1358 p_log_level_rec => p_log_level_rec);
1359 end if;
1360
1361 end if; -- px_rule_in.calc_basis = 'NBV'
1362
1363 -------------------------------------------------------------
1364 -- All event type:
1365 -- Set raf and formula factor
1366 -------------------------------------------------------------
1367 px_rule_out.new_raf :=1;
1368 px_rule_out.new_formula_factor :=1;
1369
1370 -------------------------------------------------------------
1371 -- Event types: ADDITION, AMORT_ADJ
1372 --
1373 -- current_total_rsv includes retired_reserve at group asset
1374 -------------------------------------------------------------
1375
1376 if px_rule_in.event_type in ('ADDITION','AMORT_ADJ')
1377 then
1378 if px_rule_in.calc_basis='COST' then
1379
1380 px_rule_out.new_adjusted_cost :=
1381 (nvl(px_rule_in.eop_recoverable_cost,0)
1382 + nvl(px_rule_in.recoverable_cost,0))/2;
1383
1384 elsif px_rule_in.calc_basis = 'NBV' then
1385
1386 px_rule_out.new_adjusted_cost :=
1387 ((nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0))
1388 +(nvl(px_rule_in.recoverable_cost,0)
1389 - nvl(px_rule_in.current_total_rsv,0)
1390 ))/2;
1391
1392 else -- unexpected calc_basis
1393 raise calc_basis_err;
1394 end if; -- End calc_basis
1395
1396 end if; -- End Event type:ADDITION, AMORT_ADJ
1397
1398 -------------------------------------------------------------
1399 -- Event types: AMORT_ADJ3
1400 -------------------------------------------------------------
1401
1402 if px_rule_in.event_type = 'AMORT_ADJ3'
1403 then
1404 px_rule_out.new_adjusted_cost
1405 := px_rule_in.adjusted_cost;
1406 px_rule_in.use_old_adj_cost_flag :='Y';
1407 end if;
1408
1409 -------------------------------
1410 -- Event type: EXPENSED_ADJ
1411 -------------------------------
1412
1413 if px_rule_in.event_type='EXPENSED_ADJ' then
1414
1415 if px_rule_in.calc_basis='COST' then
1416 if Upper(px_rule_in.depreciate_flag) like 'N%' then
1417 px_rule_out.new_adjusted_cost :=
1418 (nvl(px_rule_in.eop_recoverable_cost,0)
1419 + nvl(px_rule_in.recoverable_cost,0))/2;
1420 else
1421
1422 px_rule_out.new_adjusted_cost :=
1423 nvl(px_rule_in.recoverable_cost,0);
1424 end if;
1425
1426 elsif px_rule_in.calc_basis = 'NBV' then
1427
1428 if Upper(px_rule_in.depreciate_flag) like 'N%' then
1429 px_rule_out.new_adjusted_cost :=
1430 ((nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0))
1431 +(nvl(px_rule_in.recoverable_cost,0)
1432 - nvl(px_rule_in.current_total_rsv,0)
1433 ))/2;
1434 else
1435 px_rule_out.new_adjusted_cost :=
1436 nvl(px_rule_in.recoverable_cost,0)
1437 - nvl(px_rule_in.hyp_total_rsv,0);
1438 end if;
1439 else -- unexpected calc_basis
1440 raise calc_basis_err;
1441 end if; -- End calc_basis
1442
1443 end if; -- End event type: EXPENSED_ADJ
1444
1445 ----------------------------
1446 -- Event type: RETIREMENT
1447 ----------------------------
1448 if px_rule_in.event_type='RETIREMENT' then
1449
1450 -- Retirement with Recognized Gain and Loss Immediately When Retired
1451 if px_rule_in.recognize_gain_loss <>'NO' then
1452 if px_rule_in.cost = 0 then
1453 l_retirement_reserve := 0;
1454 else
1455 l_retirement_reserve
1456 := - nvl(px_rule_in.adjustment_amount,0)
1457 / px_rule_in.cost
1458 * nvl(px_rule_in.current_total_rsv,0);
1459 end if;
1460
1461 else -- Do Not Recogized Gain and loss
1462 l_retirement_reserve
1463 := - nvl(px_rule_in.adjustment_amount,0)
1464 + nvl(px_rule_in.nbv_retired,0);
1465
1466 end if; -- End recognize_gain_loss
1467
1468 if px_rule_in.calc_basis='COST' then
1469
1470 px_rule_out.new_adjusted_cost :=
1471 (nvl(px_rule_in.eop_recoverable_cost,0)
1472 + nvl(px_rule_in.recoverable_cost,0))/2;
1473
1474 elsif px_rule_in.calc_basis = 'NBV' then
1475 px_rule_out.new_adjusted_cost :=
1476 ((nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0))
1477 +(nvl(px_rule_in.recoverable_cost,0)
1478 - nvl(px_rule_in.current_total_rsv,0)
1479 - nvl(l_retirement_reserve,0)))/2;
1480 else -- unexpected calc_basis
1481 raise calc_basis_err;
1482 end if; -- End calc_basis
1483
1484 end if; -- Event type: Retirement
1485
1486 ----------------------------------------------------------------
1487 -- Event Type: INITIAL_ADDITION
1488 ----------------------------------------------------------------
1489
1490 if (px_rule_in.event_type ='INITIAL_ADDITION') THEN
1491
1492 px_rule_out.new_adjusted_cost :=
1493 nvl(px_rule_in.recoverable_cost,0)/2;
1494
1495 px_rule_in.eop_salvage_value := 0;
1496
1497 if p_log_level_rec.statement_level then
1498 fa_debug_pkg.add(fname=>'period_average',
1499 element=>'updated eop_salvage_value',
1500 value=> px_rule_in.eop_salvage_value,
1501 p_log_level_rec => p_log_level_rec);
1502 end if;
1503
1504 end if; -- End of INITIAL_ADDITION
1505
1506 -------------------------------------------------------------
1507 -- Event Type: DEPRECIATE_FLAG_ADJ (IDLE Asset Control)
1508 -------------------------------------------------------------
1509 if (px_rule_in.event_type ='DEPRECIATE_FLAG_ADJ') then
1510 if (px_rule_in.calc_basis = 'NBV') then
1511
1512 px_rule_out.new_adjusted_cost :=
1513 ((nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0))
1514 +(nvl(px_rule_in.recoverable_cost,0)
1515 - nvl(px_rule_in.current_total_rsv,0)
1516 ))/2;
1517
1518 end if;
1519 end if; -- End DEPRECIATE_FLAG_ADJ
1520
1521 ------------------------------------------------------------
1522 -- Event Type: AFTER_DEPRN (After Depreciation)
1523 ------------------------------------------------------------
1524 if (px_rule_in.event_type ='AFTER_DEPRN') THEN
1525 if px_rule_in.calc_basis='COST' then
1526
1527 px_rule_out.new_adjusted_cost :=
1528 nvl(px_rule_in.recoverable_cost,0);
1529
1530 px_rule_in.use_old_adj_cost_flag :='N';
1531 end if;
1532 end if;
1533
1534 ---------------------------------------------------------------------------
1535 -- All Event types except of 'AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ',
1536 -- 'AMORT_ADJ2','AMORT_ADJ3':
1537 -- Check exclude salvage value flag.
1538 -- And if exclude salvage value flag is Yes,
1539 -- reduce salvage value from new adjusted cost
1540 ---------------------------------------------------------------------------
1541 if fa_cache_pkg.fazccmt(px_rule_in.method_code,
1542 px_rule_in.life_in_months,
1543 p_log_level_rec) then
1544
1545 if p_log_level_rec.statement_level then
1546 fa_debug_pkg.add(fname=>'period_average',
1547 element=>'fazccmt',
1548 value=> 'Called',
1549 p_log_level_rec => p_log_level_rec);
1550 end if;
1551
1552 l_exclude_salvage_value_flag := fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag;
1553
1554 end if;
1555
1556
1557 if px_rule_in.event_type
1558 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3')
1559 and Upper(l_exclude_salvage_value_flag) like 'Y%'
1560 and px_rule_in.calc_basis= 'NBV'
1561 then
1562
1563 px_rule_out.new_adjusted_cost :=
1564 px_rule_out.new_adjusted_cost
1565 - (nvl(px_rule_in.salvage_value,0)
1566 - nvl(px_rule_in.eop_salvage_value,0))/2;
1567
1568 end if;
1569
1570 end if; -- Flat and Formula NBV
1571
1572 if p_log_level_rec.statement_level then
1573 fa_debug_pkg.add(fname=>'period_average',
1574 element=>'new_adjusted_cost',
1575 value=> px_rule_out.new_adjusted_cost,
1576 p_log_level_rec => p_log_level_rec);
1577 end if;
1578
1579
1580 exception
1581 when calc_basis_err then
1582 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1583 ,p_log_level_rec => p_log_level_rec);
1584
1585 when others then
1586 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1587 ,p_log_level_rec => p_log_level_rec);
1588 raise;
1589 end PERIOD_AVERAGE;
1590
1591 --------------------------------------------------------------------------------
1592 -- Procedure YTD_AVERAGE:
1593 -- This procedure is the additional functionality for depreciable basis rule
1594 -- 'Year to Date Average'.
1595 --------------------------------------------------------------------------------
1596
1597 PROCEDURE YTD_AVERAGE (
1598 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
1599 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
1600 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1601 is
1602
1603 -- Get Eofy reserve retired on current fiscal year
1604 Cursor C_EOFY_RESERVE_RETIRED is
1605 select nvl(sum(RET.EOFY_RESERVE),0)
1606 from FA_RETIREMENTS RET,
1607 FA_TRANSACTION_HEADERS TH,
1608 FA_DEPRN_PERIODS DP,
1609 FA_FISCAL_YEAR FY,
1610 FA_BOOK_CONTROLS BC
1611 where RET.ASSET_ID= px_rule_in.asset_id
1612 and RET.BOOK_TYPE_CODE = px_rule_in.book_type_code
1613 and DP.BOOK_TYPE_CODE = px_rule_in.book_type_code
1614 and BC.BOOK_TYPE_CODE = px_rule_in.book_type_code
1615 and RET.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
1616 and BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
1617 and FY.FISCAL_YEAR = px_rule_in.fiscal_year
1618 and DP.PERIOD_COUNTER = px_rule_in.period_counter
1619 and TH.TRANSACTION_DATE_ENTERED between
1620 FY.START_DATE and DP.CALENDAR_PERIOD_CLOSE_DATE
1621 and RET.STATUS in ('PROCESSED','PENDING')
1622 ;
1623
1624 Cursor C_EOFY_RESERVE_RETIRED_M is
1625 select nvl(sum(RET.EOFY_RESERVE),0)
1626 from FA_RETIREMENTS_MRC_V RET,
1627 FA_TRANSACTION_HEADERS TH,
1628 FA_DEPRN_PERIODS_MRC_V DP,
1629 FA_FISCAL_YEAR FY,
1630 FA_BOOK_CONTROLS_MRC_V BC
1631 where RET.ASSET_ID= px_rule_in.asset_id
1632 and RET.BOOK_TYPE_CODE = px_rule_in.book_type_code
1633 and DP.BOOK_TYPE_CODE = px_rule_in.book_type_code
1634 and BC.BOOK_TYPE_CODE = px_rule_in.book_type_code
1635 and RET.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
1636 and BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
1637 and FY.FISCAL_YEAR = px_rule_in.fiscal_year
1638 and DP.PERIOD_COUNTER = px_rule_in.period_counter
1639 and TH.TRANSACTION_DATE_ENTERED between
1640 FY.START_DATE and DP.CALENDAR_PERIOD_CLOSE_DATE
1641 and RET.STATUS in ('PROCESSED','PENDING')
1642 ;
1643
1644 Cursor GP_EOFY_RESERVE_RETIRED is
1645 select nvl(sum(RET.EOFY_RESERVE),0)
1646 from FA_RETIREMENTS RET,
1647 FA_TRANSACTION_HEADERS TH,
1648 FA_DEPRN_PERIODS DP,
1649 FA_FISCAL_YEAR FY,
1650 FA_BOOK_CONTROLS BC
1651 where TH.ASSET_ID= px_rule_in.asset_id
1652 and TH.BOOK_TYPE_CODE = px_rule_in.book_type_code
1653 and DP.BOOK_TYPE_CODE = px_rule_in.book_type_code
1654 and BC.BOOK_TYPE_CODE = px_rule_in.book_type_code
1655 and RET.TRANSACTION_HEADER_ID_IN = TH.MEMBER_TRANSACTION_HEADER_ID (+)
1656 and BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
1657 and FY.FISCAL_YEAR = px_rule_in.fiscal_year
1658 and DP.PERIOD_COUNTER = px_rule_in.period_counter
1659 and TH.TRANSACTION_DATE_ENTERED between
1660 FY.START_DATE and DP.CALENDAR_PERIOD_CLOSE_DATE
1661 and RET.STATUS in ('PROCESSED','PENDING')
1662 and exists (select RET.ASSET_ID
1663 from FA_BOOKS BK
1664 where RET.ASSET_ID = BK.ASSET_ID
1665 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
1666 and BK.GROUP_ASSET_ID = px_rule_in.asset_id
1667 and BK.DATE_INEFFECTIVE is null)
1668 ;
1669
1670 Cursor GP_EOFY_RESERVE_RETIRED_M is
1671 select nvl(sum(RET.EOFY_RESERVE),0)
1672 from FA_RETIREMENTS_MRC_V RET,
1673 FA_TRANSACTION_HEADERS TH,
1674 FA_DEPRN_PERIODS_MRC_V DP,
1675 FA_FISCAL_YEAR FY,
1676 FA_BOOK_CONTROLS_MRC_V BC
1677 where TH.ASSET_ID= px_rule_in.asset_id
1678 and TH.BOOK_TYPE_CODE = px_rule_in.book_type_code
1679 and DP.BOOK_TYPE_CODE = px_rule_in.book_type_code
1680 and BC.BOOK_TYPE_CODE = px_rule_in.book_type_code
1681 and RET.TRANSACTION_HEADER_ID_IN = TH.MEMBER_TRANSACTION_HEADER_ID (+)
1682 and BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
1683 and FY.FISCAL_YEAR = px_rule_in.fiscal_year
1684 and DP.PERIOD_COUNTER = px_rule_in.period_counter
1685 and TH.TRANSACTION_DATE_ENTERED between
1686 FY.START_DATE and DP.CALENDAR_PERIOD_CLOSE_DATE
1687 and RET.STATUS in ('PROCESSED','PENDING')
1688 and exists (select RET.ASSET_ID
1689 from FA_BOOKS_MRC_V BK
1690 where RET.ASSET_ID = BK.ASSET_ID
1691 and BK.BOOK_TYPE_CODE = px_rule_in.book_type_code
1692 and BK.GROUP_ASSET_ID = px_rule_in.asset_id
1693 and BK.DATE_INEFFECTIVE is null)
1694 ;
1695
1696 -- current transaction's member eofy reserve retired
1697 Cursor CUR_EOFY_RESERVE_RETIRED is
1698 select nvl(sum(RET.EOFY_RESERVE),0)
1699 from FA_RETIREMENTS RET
1700 where RET.TRANSACTION_HEADER_ID_IN = px_rule_in.adj_mem_transaction_header_id
1701 ;
1702
1703 Cursor CUR_EOFY_RESERVE_RETIRED_M is
1704 select nvl(sum(RET.EOFY_RESERVE),0)
1705 from FA_RETIREMENTS_MRC_V RET
1706 where RET.TRANSACTION_HEADER_ID_IN = px_rule_in.adj_mem_transaction_header_id
1707 ;
1708
1709 l_retired_eofy_reserve NUMBER := 0;
1710 l_cur_retired_eofy_reserve NUMBER := 0;
1711 l_old_eofy_reserve NUMBER := 0;
1712 l_exclude_salvage_value_flag VARCHAR2(3) :=null;
1713 l_retirement_reserve NUMBER :=0; -- Retirement Reserve
1714
1715 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.ytd_average';
1716
1717 calc_basis_err exception;
1718
1719 begin
1720
1721 -------------------------------------------------------
1722 -- This rule is FLAT method type only
1723 -------------------------------------------------------
1724 if (px_rule_in.method_type = 'FLAT') then
1725
1726 -------------------------------------
1727 -- Get old eofy_reserve
1728 -------------------------------------
1729 if px_rule_in.mrc_sob_type_code <>'R' then
1730 if px_rule_in.asset_type = 'GROUP' then
1731 OPEN GP_EOFY_RESERVE_RETIRED;
1732 FETCH GP_EOFY_RESERVE_RETIRED into l_retired_eofy_reserve;
1733 CLOSE GP_EOFY_RESERVE_RETIRED;
1734
1735 OPEN CUR_EOFY_RESERVE_RETIRED;
1736 FETCH CUR_EOFY_RESERVE_RETIRED into l_cur_retired_eofy_reserve;
1737 CLOSE CUR_EOFY_RESERVE_RETIRED;
1738
1739 else -- member and standalone
1740 OPEN C_EOFY_RESERVE_RETIRED;
1741 FETCH C_EOFY_RESERVE_RETIRED into l_retired_eofy_reserve;
1742 CLOSE C_EOFY_RESERVE_RETIRED;
1743 end if;
1744 else -- MRC
1745 if px_rule_in.asset_type = 'GROUP' then
1746 OPEN GP_EOFY_RESERVE_RETIRED_M;
1747 FETCH GP_EOFY_RESERVE_RETIRED_M into l_retired_eofy_reserve;
1748 CLOSE GP_EOFY_RESERVE_RETIRED_M;
1749
1750 OPEN CUR_EOFY_RESERVE_RETIRED_M;
1751 FETCH CUR_EOFY_RESERVE_RETIRED_M into l_cur_retired_eofy_reserve;
1752 CLOSE CUR_EOFY_RESERVE_RETIRED_M;
1753
1754 else -- member and standalone
1755 OPEN C_EOFY_RESERVE_RETIRED_M;
1756 FETCH C_EOFY_RESERVE_RETIRED_M into l_retired_eofy_reserve;
1757 CLOSE C_EOFY_RESERVE_RETIRED_M;
1758 end if;
1759 end if;
1760
1761 l_old_eofy_reserve := nvl(px_rule_in.eofy_reserve,0)
1762 + nvl(l_retired_eofy_reserve,0)
1763 + nvl(l_cur_retired_eofy_reserve,0);
1764
1765 if p_log_level_rec.statement_level then
1766 fa_debug_pkg.add(fname=>'ytd_average',
1767 element=>'l_retired_eofy_reserve',
1768 value=> l_retired_eofy_reserve,
1769 p_log_level_rec => p_log_level_rec);
1770 fa_debug_pkg.add(fname=>'ytd_average',
1771 element=>'l_cur_retired_eofy_reserve',
1772 value=> l_cur_retired_eofy_reserve,
1773 p_log_level_rec => p_log_level_rec);
1774 fa_debug_pkg.add(fname=>'ytd_average',
1775 element=>'l_old_eofy_reserve',
1776 value=> l_old_eofy_reserve,
1777 p_log_level_rec => p_log_level_rec);
1778 end if;
1779
1780 -- End of getting old eofy_reserve
1781
1782 --------------------------------------------------------------
1783 -- Event types: ADDITION, AMORT_ADJ
1784 --
1785 -- current_total_rsv includes retired_reserve at group asset
1786 --------------------------------------------------------------
1787
1788 if px_rule_in.event_type in ('ADDITION','AMORT_ADJ')
1789 then
1790 if px_rule_in.calc_basis='COST' then
1791
1792 px_rule_out.new_adjusted_cost :=
1793 (nvl(px_rule_in.eofy_recoverable_cost,0)
1794 + nvl(px_rule_in.recoverable_cost,0))/2;
1795
1796 elsif px_rule_in.calc_basis = 'NBV' then
1797
1798 px_rule_out.new_adjusted_cost :=
1799 ((nvl(px_rule_in.eofy_recoverable_cost,0)
1800 - nvl(l_old_eofy_reserve,0))
1801 +(nvl(px_rule_in.recoverable_cost,0)
1802 - nvl(px_rule_in.current_total_rsv,0)
1803 ))/2;
1804
1805 else -- unexpected calc_basis
1806 raise calc_basis_err;
1807
1808 end if; -- End calc_basis
1809
1810 end if; -- End Event type:ADDITION, AMORT_ADJ
1811
1812 ------------------------------
1813 -- Event type: EXPENSED_ADJ
1814 ------------------------------
1815
1816 if px_rule_in.event_type='EXPENSED_ADJ' then
1817
1818 if px_rule_in.calc_basis='COST' then
1819 if Upper(px_rule_in.depreciate_flag) like 'N%' then
1820 px_rule_out.new_adjusted_cost :=
1821 (nvl(px_rule_in.eofy_recoverable_cost,0)
1822 + nvl(px_rule_in.recoverable_cost,0))/2;
1823 else
1824 px_rule_out.new_adjusted_cost :=
1825 nvl(px_rule_in.recoverable_cost,0);
1826 end if;
1827 elsif px_rule_in.calc_basis = 'NBV' then
1828 if Upper(px_rule_in.depreciate_flag) like 'N%' then
1829 px_rule_out.new_adjusted_cost :=
1830 ((nvl(px_rule_in.eofy_recoverable_cost,0)
1831 - nvl(l_old_eofy_reserve,0))
1832 +(nvl(px_rule_in.recoverable_cost,0)
1833 - nvl(px_rule_in.current_total_rsv,0)
1834 ))/2;
1835 else
1836 px_rule_out.new_adjusted_cost :=
1837 nvl(px_rule_in.recoverable_cost,0)
1838 - nvl(px_rule_in.hyp_total_rsv,0)
1839 + nvl(px_rule_in.hyp_total_ytd,0);
1840 end if;
1841 else -- unexpected calc_basis
1842 raise calc_basis_err;
1843
1844 end if; -- End calc_basis
1845
1846 end if; -- End event type: EXPENSED_ADJ
1847
1848 ---------------------------
1849 -- Event type: RETIREMENT
1850 ---------------------------
1851 if px_rule_in.event_type='RETIREMENT' then
1852
1853 -- Retirement with Recognized Gain and Loss Immediately When Retired
1854 if px_rule_in.recognize_gain_loss<>'NO' then
1855 if px_rule_in.cost = 0 then
1856 l_retirement_reserve := 0;
1857 else
1858 l_retirement_reserve
1859 := - nvl(px_rule_in.adjustment_amount,0)
1860 / px_rule_in.cost
1861 * nvl(px_rule_in.current_total_rsv,0);
1862 end if;
1863
1864 else -- Do Not Recogized Gain and loss
1865 l_retirement_reserve
1866 := - nvl(px_rule_in.adjustment_amount,0)
1867 + nvl(px_rule_in.nbv_retired,0);
1868
1869 end if; -- End recognize_gain_loss
1870
1871 if px_rule_in.calc_basis='COST' then
1872
1873 px_rule_out.new_adjusted_cost :=
1874 (nvl(px_rule_in.eofy_recoverable_cost,0)
1875 + nvl(px_rule_in.recoverable_cost,0))/2;
1876
1877 elsif px_rule_in.calc_basis = 'NBV' then
1878 px_rule_out.new_adjusted_cost :=
1879 ((nvl(px_rule_in.eofy_recoverable_cost,0)
1880 - nvl(l_old_eofy_reserve,0))
1881 +(nvl(px_rule_in.recoverable_cost,0)
1882 - nvl(px_rule_in.current_total_rsv,0)
1883 - nvl(l_retirement_reserve,0)))/2;
1884
1885 else -- unexpected calc_basis
1886 raise calc_basis_err;
1887
1888 end if; -- End calc_basis
1889
1890 end if; -- Event type: Retirement
1891
1892 -------------------------------------------------
1893 -- Event types: AFTER_DEPRN
1894 -------------------------------------------------
1895
1896 if px_rule_in.event_type ='AFTER_DEPRN'
1897 then
1898 if px_rule_in.calc_basis='NBV' then
1899
1900 if px_rule_in.eofy_flag ='Y' then
1901
1902 px_rule_out.new_adjusted_cost
1903 := px_rule_in.recoverable_cost
1904 - px_rule_in.current_total_rsv;
1905 else
1906
1907 px_rule_out.new_adjusted_cost :=
1908 ((nvl(px_rule_in.eofy_recoverable_cost,0)
1909 - nvl(px_rule_in.eofy_reserve,0))
1910 +(nvl(px_rule_in.recoverable_cost,0)
1911 - nvl(px_rule_in.current_total_rsv,0)))/2;
1912
1913 end if; -- eofy flag
1914
1915 elsif px_rule_in.calc_basis = 'COST' then
1916
1917 if px_rule_in.eofy_flag ='Y' then
1918 px_rule_out.new_adjusted_cost :=
1919 nvl(px_rule_in.recoverable_cost,0);
1920 px_rule_in.use_old_adj_cost_flag :='N';
1921 end if;
1922
1923 else -- unexpected calc_basis
1924 raise calc_basis_err;
1925 end if; -- End calc_basis
1926
1927 end if; -- End Event type: AFTER_DEPRN
1928
1929 ----------------------------------------------------------------
1930 -- Event Type: INITIAL_ADDITION
1931 ----------------------------------------------------------------
1932
1933 if (px_rule_in.event_type ='INITIAL_ADDITION') THEN
1934
1935 px_rule_out.new_adjusted_cost :=
1936 nvl(px_rule_in.recoverable_cost,0)/2;
1937
1938 px_rule_in.eofy_salvage_value := 0;
1939
1940 if p_log_level_rec.statement_level then
1941 fa_debug_pkg.add(fname=>'ytd_average',
1942 element=>'updated eop_salvage_value',
1943 value=> px_rule_in.eofy_salvage_value,
1944 p_log_level_rec => p_log_level_rec);
1945 end if;
1946
1947 end if; -- End of INITIAL_ADDITION
1948
1949 -------------------------------------------------------------
1950 -- Event Type: DEPRECIATE_FLAG_ADJ (IDLE Asset Control)
1951 -------------------------------------------------------------
1952 if (px_rule_in.event_type ='DEPRECIATE_FLAG_ADJ') then
1953 if (px_rule_in.calc_basis = 'NBV') then
1954 px_rule_out.new_adjusted_cost :=
1955 ((nvl(px_rule_in.eofy_recoverable_cost,0)
1956 - nvl(l_old_eofy_reserve,0))
1957 +(nvl(px_rule_in.recoverable_cost,0)
1958 - nvl(px_rule_in.current_total_rsv,0)
1959 ))/2;
1960 end if;
1961 end if; -- End DEPRECIATE_FLAG_ADJ
1962
1963 ------------------------------------------------------------
1964 -- All Event types except of 'AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ',
1965 -- 'AMORT_ADJ2','AMORT_ADJ3':
1966 -- Check exclude salvage value flag.
1967 -- And if exclude salvage value flag is Yes,
1968 -- reduce salvage value from new adjusted cost
1969 ------------------------------------------------------------
1970
1971 if fa_cache_pkg.fazccmt(px_rule_in.method_code,
1972 px_rule_in.life_in_months,
1973 p_log_level_rec) then
1974
1975 if p_log_level_rec.statement_level then
1976 fa_debug_pkg.add(fname=>'ytd_average',
1977 element=>'fazccmt',
1978 value=> 'Called',
1979 p_log_level_rec => p_log_level_rec);
1980 end if;
1981
1982 l_exclude_salvage_value_flag := nvl(fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag,0);
1983
1984 end if;
1985
1986
1987 if (px_rule_in.event_type
1988 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3')
1989 or (px_rule_in.event_type = 'AFTER_DEPRN'
1990 and px_rule_in.eofy_flag <>'Y'))
1991 and Upper(l_exclude_salvage_value_flag) like 'Y%'
1992 and px_rule_in.calc_basis= 'NBV'
1993 then
1994
1995 px_rule_out.new_adjusted_cost :=
1996 nvl(px_rule_out.new_adjusted_cost,0)
1997 - (nvl(px_rule_in.salvage_value,0)
1998 - nvl(px_rule_in.eofy_salvage_value,0))/2;
1999
2000 end if;
2001
2002 end if; -- End Flat method type
2003
2004 if p_log_level_rec.statement_level then
2005 fa_debug_pkg.add(fname=>'ytd_average',
2006 element=>'new_adjusted_cost',
2007 value=> px_rule_out.new_adjusted_cost,
2008 p_log_level_rec => p_log_level_rec);
2009 end if;
2010
2011 exception
2012 when calc_basis_err then
2013 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2014 ,p_log_level_rec => p_log_level_rec);
2015
2016 when others then
2017 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2018 ,p_log_level_rec => p_log_level_rec);
2019 raise;
2020 end YTD_AVERAGE;
2021
2022 --------------------------------------------------------------------------------
2023 -- Procedure POSITIVE_REDUCTION:
2024 -- This procedure is the additional functionality for depreciable basis rule
2025 -- 'Year End Balance with Positive Reduction'.
2026 --------------------------------------------------------------------------------
2027
2028 PROCEDURE POSITIVE_REDUCTION (
2029 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
2030 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
2031 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2032 is
2033
2034 -- Cursor to check exclude_proceeds_from_basis
2035 cursor C_CHECK_FLAGS (p_asset_id number)
2036 is
2037 select EXCLUDE_PROCEEDS_FROM_BASIS,
2038 MEMBER_ROLLUP_FLAG
2039 from FA_BOOKS
2040 where ASSET_ID = p_asset_id
2041 and BOOK_TYPE_CODE = px_rule_in.book_type_code
2042 and date_ineffective is null;
2043
2044 -- MRC
2045 cursor C_CHECK_FLAGS_M (p_asset_id number)
2046 is
2047 select EXCLUDE_PROCEEDS_FROM_BASIS,
2048 MEMBER_ROLLUP_FLAG
2049 from FA_BOOKS_MRC_V
2050 where ASSET_ID = p_asset_id
2051 and BOOK_TYPE_CODE = px_rule_in.book_type_code
2052 and date_ineffective is null;
2053
2054 -- Cursor to calculate sum of member assets' eofy_reserve
2055 cursor C_SUMUP_EOFY_RESERVE
2056 is
2057 select sum(nvl(EOFY_RESERVE,0))
2058 from FA_BOOKS
2059 where GROUP_ASSET_ID = px_rule_in.asset_id
2060 and BOOK_TYPE_CODE = px_rule_in.book_type_code
2061 and DATE_INEFFECTIVE is null;
2062
2063 -- MRC
2064 cursor C_SUMUP_EOFY_RESERVE_M
2065 is
2066 select sum(nvl(EOFY_RESERVE,0))
2067 from FA_BOOKS_MRC_V
2068 where GROUP_ASSET_ID = px_rule_in.asset_id
2069 and BOOK_TYPE_CODE = px_rule_in.book_type_code
2070 and DATE_INEFFECTIVE is null;
2071
2072 l_change_in_cost NUMBER :=0;
2073 l_change_in_cost_to_reduce NUMBER :=0;
2074 l_total_change_in_cost NUMBER :=0;
2075 l_net_proceeds NUMBER :=0;
2076 l_net_proceeds_to_reduce NUMBER :=0;
2077 l_total_net_proceeds NUMBER :=0;
2078 l_first_half_cost NUMBER :=0;
2079 l_first_half_cost_to_reduce NUMBER :=0;
2080 l_second_half_cost NUMBER :=0;
2081 l_second_half_cost_to_reduce NUMBER :=0;
2082
2083 l_retired_cost NUMBER :=0;
2084 l_ltd_proceeds NUMBER :=0;
2085 l_ytd_proceeds NUMBER :=0;
2086
2087 l_exclude_proceeds_from_basis VARCHAR2(1) :=NULL;
2088 l_tmp_asset_id NUMBER(15) := NULL;
2089
2090 l_member_eofy_reserve NUMBER :=0;
2091
2092 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.positive_reduction';
2093
2094 call_reduction_amount_err exception;
2095 positive_reduction_err exception;
2096 calc_basis_err exception;
2097
2098 begin
2099
2100
2101 -------------------------------------------------------
2102 -- This rule is FLAT method type only
2103 -------------------------------------------------------
2104 if (px_rule_in.method_type = 'FLAT') then
2105
2106 -- Check exclude_proceeds_from_basis flag
2107
2108 if px_rule_in.group_asset_id is not null then
2109 l_tmp_asset_id := px_rule_in.group_asset_id;
2110 else
2111 l_tmp_asset_id := px_rule_in.asset_id;
2112 end if;
2113
2114 if px_rule_in.mrc_sob_type_code <>'R' then
2115 OPEN C_CHECK_FLAGS(l_tmp_asset_id);
2116 FETCH C_CHECK_FLAGS into l_exclude_proceeds_from_basis,
2117 px_rule_in.member_rollup_flag;
2118 CLOSE C_CHECK_FLAGS;
2119 else --MRC
2120 OPEN C_CHECK_FLAGS_M(l_tmp_asset_id);
2121 FETCH C_CHECK_FLAGS_M into l_exclude_proceeds_from_basis,
2122 px_rule_in.member_rollup_flag;
2123 CLOSE C_CHECK_FLAGS_M;
2124 end if;
2125
2126 if p_log_level_rec.statement_level then
2127 fa_debug_pkg.add(fname=>'positive_reduction',
2128 element=>'l_exclude_proceeds_from_basis',
2129 value=> l_exclude_proceeds_from_basis,
2130 p_log_level_rec => p_log_level_rec);
2131 fa_debug_pkg.add(fname=>'positive_reduction',
2132 element=>'member_rollup_flag',
2133 value=> px_rule_in.member_rollup_flag,
2134 p_log_level_rec => p_log_level_rec);
2135 end if;
2136
2137 ------------------------------------------------------
2138 -- Event TYPE: AMORT_ADJ
2139 ------------------------------------------------------
2140 if px_rule_in.event_type ='AMORT_ADJ'
2141 then
2142 ---------------------------------------------------
2143 -- This is to calculate group asset's adjusted_cost
2144 -- when group assest is CALCULATE/SUMUP
2145 ---------------------------------------------------
2146
2147 if px_rule_in.asset_type='GROUP'
2148 and nvl(px_rule_in.tracking_method,'NULL')='CALCULATE'
2149 and nvl(px_rule_in.member_rollup_flag,'NULL')='Y'
2150 then
2151 -- Calculate sum of member's eofy_reserve
2152 if px_rule_in.mrc_sob_type_code <>'R' then
2153 OPEN C_SUMUP_EOFY_RESERVE;
2154 FETCH C_SUMUP_EOFY_RESERVE into l_member_eofy_reserve;
2155 CLOSE C_SUMUP_EOFY_RESERVE;
2156 else --MRC
2157 OPEN C_SUMUP_EOFY_RESERVE_M;
2158 FETCH C_SUMUP_EOFY_RESERVE_M into l_member_eofy_reserve;
2159 CLOSE C_SUMUP_EOFY_RESERVE_M;
2160 end if;
2161
2162 if p_log_level_rec.statement_level then
2163 fa_debug_pkg.add(fname=>'positive_reduction',
2164 element=>'l_member_eofy_reserve',
2165 value=> l_member_eofy_reserve,
2166 p_log_level_rec => p_log_level_rec);
2167 end if;
2168
2169 -- Recalculate new_adjusted_cost using member's eofy_reserve
2170 if nvl(px_rule_in.member_transaction_type_code,'NULL')
2171 like '%RETIREMENT'
2172 then -- This is always Do Not Gain/Loss
2173
2174 if px_rule_in.calc_basis = 'NBV' then
2175 px_rule_out.new_adjusted_cost :=
2176 px_rule_in.recoverable_cost
2177 - px_rule_in.adjustment_amount
2178 - nvl(l_member_eofy_reserve,0)
2179 - nvl(px_rule_in.member_proceeds,0);
2180 end if; -- End of calc_basis
2181
2182 else -- Normal Adjustment
2183 if px_rule_in.calc_basis = 'NBV' then
2184 px_rule_out.new_adjusted_cost
2185 := px_rule_in.recoverable_cost
2186 - l_member_eofy_reserve;
2187 end if;
2188 end if; -- End of Group Retirement or Normal Adjustment
2189 end if; -- End of Group asset and calculate/sumup
2190
2191 end if; -- End of AMORT_ADJ
2192
2193 ------------------------------------------------------
2194 -- Event TYPE: RETIREMENT
2195 ------------------------------------------------------
2196 if px_rule_in.event_type ='RETIREMENT' then
2197
2198 -- Adjustment amount is cost retired.
2199 -- Changed positive amount to nevative amount.
2200
2201 px_rule_in.adjustment_amount
2202 := - px_rule_in.adjustment_amount;
2203
2204 end if; -- event type:RETIREMENT
2205
2206 ------------------------------------------------------
2207 -- Event TYPE: AFTER_DEPRN
2208 ------------------------------------------------------
2209 if px_rule_in.event_type ='AFTER_DEPRN' then
2210 if px_rule_in.calc_basis ='COST'
2211 and px_rule_in.eofy_flag ='Y' then -- End of Fiscal Year
2212
2213 -- For Class 13
2214 if px_rule_in.asset_type='GROUP'
2215 and px_rule_in.tracking_method ='CALCULATE'
2216 and px_rule_in.member_rollup_flag ='Y'
2217 then
2218 px_rule_out.new_adjusted_cost
2219 := px_rule_in.recoverable_cost
2220 - px_rule_in.current_total_rsv;
2221 else
2222 px_rule_out.new_adjusted_cost
2223 := px_rule_in.recoverable_cost;
2224 end if;
2225
2226 end if;
2227 end if;
2228
2229 --------------------------------------------------------
2230 -- Treated Class 13 (Cost Base) :
2231 -- When asset is retired, don't subtruct retired cost
2232 -- from depreciable basis.
2233 -- Because of this, retire costs of all retirements
2234 -- are added.
2235 ---------------------------------------------------------
2236 if px_rule_in.calc_basis ='COST'
2237 and px_rule_in.event_type
2238 not in ('AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3','DEPRECIATE_FLAG_ADJ')
2239 then
2240
2241 if px_rule_in.asset_type='GROUP'
2242 and px_rule_in.tracking_method ='CALCULATE'
2243 and px_rule_in.member_rollup_flag ='Y'
2244 and px_rule_in.event_type <>'AFTER_DEPRN'
2245 then
2246 -- Class 13 Report Purpose
2247 -- Only this condition, the adjusted cost is NBV.
2248 -- This adjusted cost is not used by the calculation of depreciation engine.
2249
2250 -- Calcluation of proceeds
2251 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_PROCEEDS (
2252 p_asset_id => px_rule_in.asset_id,
2253 p_asset_type => px_rule_in.asset_type,
2254 p_book_type_code => px_rule_in.book_type_code,
2255 p_period_counter => px_rule_in.period_counter,
2256 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2257 x_ltd_proceeds => l_ltd_proceeds,
2258 x_ytd_proceeds => l_ytd_proceeds,
2259 p_log_level_rec => p_log_level_rec)
2260 then
2261 raise positive_reduction_err;
2262 end if; -- End of call CALC_PROCEEDS
2263
2264 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_RETIRED_COST (
2265 p_event_type => px_rule_in.event_type,
2266 p_asset_id => px_rule_in.asset_id,
2267 p_asset_type => px_rule_in.asset_type,
2268 p_book_type_code => px_rule_in.book_type_code,
2269 p_fiscal_year => px_rule_in.fiscal_year,
2270 p_period_num => px_rule_in.period_num,
2271 p_adjustment_amount => px_rule_in.adjustment_amount,
2272 p_ltd_ytd_flag => 'YTD',
2273 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2274 x_retired_cost => l_retired_cost,
2275 p_log_level_rec => p_log_level_rec)
2276 then
2277 raise call_reduction_amount_err;
2278 end if; -- End of call CALC_RETIRED_COST
2279
2280 if nvl(px_rule_in.member_transaction_type_code,'NULL')
2281 like '%RETIREMENT'
2282 then
2283 px_rule_out.new_adjusted_cost
2284 := px_rule_in.recoverable_cost
2285 + nvl(l_retired_cost,0)
2286 - nvl(px_rule_in.eofy_reserve,0)
2287 - nvl(l_ytd_proceeds,0)
2288 - nvl(px_rule_in.adjustment_amount,0)
2289 - nvl(px_rule_in.member_proceeds,0);
2290 else -- Except of RETIREMENT
2291 px_rule_out.new_adjusted_cost
2292 := px_rule_in.recoverable_cost
2293 + nvl(l_retired_cost,0)
2294 - nvl(px_rule_in.eofy_reserve,0)
2295 - nvl(l_ytd_proceeds,0);
2296
2297 end if;
2298
2299 else
2300 if px_rule_in.asset_type<>'GROUP'
2301 and px_rule_in.tracking_method ='CALCULATE'
2302 and not (px_rule_in.event_type = 'AFTER_DEPRN'
2303 and px_rule_in.eofy_flag ='N')
2304 then
2305
2306 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_RETIRED_COST (
2307 p_event_type => px_rule_in.event_type,
2308 p_asset_id => px_rule_in.asset_id,
2309 p_asset_type => px_rule_in.asset_type,
2310 p_book_type_code => px_rule_in.book_type_code,
2311 p_fiscal_year => px_rule_in.fiscal_year,
2312 p_period_num => px_rule_in.period_num,
2313 p_adjustment_amount => px_rule_in.adjustment_amount,
2314 p_ltd_ytd_flag => 'LTD',
2315 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2316 x_retired_cost => l_retired_cost,
2317 p_log_level_rec => p_log_level_rec)
2318 then
2319 raise call_reduction_amount_err;
2320 end if;
2321
2322 px_rule_out.new_adjusted_cost
2323 := nvl(px_rule_out.new_adjusted_cost,0) + nvl(l_retired_cost,0);
2324 end if;
2325
2326 end if; -- End of Class 13 Report Purpose
2327
2328 end if; -- Cost Base
2329
2330 -------------------------------------------------------
2331 -- Treated Class 10.1 (NBV Base) :
2332 -- When asset is retired, don't subtract proceeds
2333 -- from deprn basis.
2334 -- Because of this, proceeds are added.
2335 -------------------------------------------------------
2336 if px_rule_in.calc_basis ='NBV'
2337 and px_rule_in.tracking_method='CALCULATE'
2338 and px_rule_in.member_rollup_flag='Y'
2339 and l_exclude_proceeds_from_basis='Y'
2340 and px_rule_in.event_type
2341 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3')
2342 then
2343
2344 -- Calcluation of proceeds
2345 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_PROCEEDS (
2346 p_asset_id => px_rule_in.asset_id,
2347 p_asset_type => px_rule_in.asset_type,
2348 p_book_type_code => px_rule_in.book_type_code,
2349 p_period_counter => px_rule_in.period_counter,
2350 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2351 x_ltd_proceeds => l_ltd_proceeds,
2352 x_ytd_proceeds => l_ytd_proceeds,
2353 p_log_level_rec => p_log_level_rec)
2354 then
2355 raise positive_reduction_err;
2356 end if; -- End of call CALC_PROCEEDS
2357
2358 if px_rule_in.asset_type='GROUP' then
2359 px_rule_out.new_adjusted_cost
2360 := nvl(px_rule_out.new_adjusted_cost,0) + nvl(l_ytd_proceeds,0)
2361 + nvl(px_rule_in.member_proceeds,0);
2362 else
2363 px_rule_out.new_adjusted_cost
2364 := nvl(px_rule_out.new_adjusted_cost,0) + nvl(l_ytd_proceeds,0)
2365 + nvl(px_rule_in.nbv_retired,0);
2366 end if;
2367 end if; -- End of Class 10.1
2368
2369 ----------------------------------------
2370 -- Call CALC_REDUCTION_AMOUNT function
2371 ----------------------------------------
2372
2373 if px_rule_in.event_type
2374 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3')
2375 then
2376
2377 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_REDUCTION_AMOUNT
2378 (
2379 p_asset_id => px_rule_in.asset_id,
2380 p_group_asset_id => px_rule_in.group_asset_id,
2381 p_asset_type => px_rule_in.asset_type,
2382 p_book_type_code => px_rule_in.book_type_code,
2383 p_period_counter => px_rule_in.period_counter,
2384 p_transaction_date => px_rule_in.adj_transaction_date_entered,
2385 p_half_year_rule_flag => 'N',
2386 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2387 x_change_in_cost => l_change_in_cost,
2388 x_change_in_cost_to_reduce => l_change_in_cost_to_reduce,
2389 x_total_change_in_cost => l_total_change_in_cost,
2390 x_net_proceeds => l_net_proceeds,
2391 x_net_proceeds_to_reduce => l_net_proceeds_to_reduce,
2392 x_total_net_proceeds => l_total_net_proceeds,
2393 x_first_half_cost => l_first_half_cost,
2394 x_first_half_cost_to_reduce => l_first_half_cost_to_reduce,
2395 x_second_half_cost => l_second_half_cost,
2396 x_second_half_cost_to_reduce => l_second_half_cost_to_reduce,
2397 p_log_level_rec => p_log_level_rec)
2398 then
2399
2400 raise call_reduction_amount_err;
2401
2402 end if; -- Call CALC_REDUCTION_AMOUNT
2403 end if; -- Condition of CALC_REDUCTION_AMOUNT call
2404
2405 if p_log_level_rec.statement_level then
2406 fa_debug_pkg.add(fname=>'positive_reduction',
2407 element=>'l_change_in_cost',
2408 value=> l_change_in_cost,
2409 p_log_level_rec => p_log_level_rec);
2410 fa_debug_pkg.add(fname=>'positive_reduction',
2411 element=>'l_change_in_cost_to_reduce',
2412 value=> l_change_in_cost_to_reduce,
2413 p_log_level_rec => p_log_level_rec);
2414 fa_debug_pkg.add(fname=>'positive_reduction',
2415 element=>'l_total_change_in_cost',
2416 value=> l_total_change_in_cost,
2417 p_log_level_rec => p_log_level_rec);
2418 fa_debug_pkg.add(fname=>'positive_reduction',
2419 element=>'l_net_proceeds',
2420 value=> l_net_proceeds,
2421 p_log_level_rec => p_log_level_rec);
2422 fa_debug_pkg.add(fname=>'positive_reduction',
2423 element=>'l_net_proceeds_to_reduce',
2424 value=> l_net_proceeds_to_reduce,
2425 p_log_level_rec => p_log_level_rec);
2426 fa_debug_pkg.add(fname=>'positive_reduction',
2427 element=>'l_total_net_proceeds',
2428 value=> l_total_net_proceeds,
2429 p_log_level_rec => p_log_level_rec);
2430 end if;
2431
2432 -- Check to apply reduction amount and calculate reduction amount
2433
2434 If (l_change_in_cost - l_net_proceeds >0
2435 and px_rule_in.apply_reduction_flag is null)
2436 or nvl(px_rule_in.apply_reduction_flag,'N') ='Y'
2437 then
2438 if px_rule_in.calc_basis ='COST'
2439 and not (px_rule_in.asset_type='GROUP'
2440 and px_rule_in.tracking_method ='CALCULATE'
2441 and px_rule_in.member_rollup_flag ='Y')
2442 then
2443 px_rule_in.reduction_amount
2444 := nvl(l_change_in_cost_to_reduce,0);
2445 else -- NBV Base and Class 13's Group Asset
2446 px_rule_in.reduction_amount
2447 := nvl(l_change_in_cost_to_reduce,0)
2448 - nvl(l_net_proceeds_to_reduce,0);
2449 end if;
2450 else
2451 px_rule_in.reduction_amount := 0;
2452
2453 end if; -- Reduction amount condition
2454
2455 if px_rule_in.asset_type='GROUP'
2456 and px_rule_in.reduction_amount<>0 then
2457 -- Apply reduction amount to group asset
2458 px_rule_in.apply_reduction_flag :='Y';
2459 end if;
2460
2461 end if; -- End Flat method type
2462
2463 if p_log_level_rec.statement_level then
2464 fa_debug_pkg.add(fname=>'positive_reduction',
2465 element=>'new_adjusted_cost',
2466 value=> px_rule_out.new_adjusted_cost,
2467 p_log_level_rec => p_log_level_rec);
2468 fa_debug_pkg.add(fname=>'positive_reduction',
2469 element=>'reduction_amount',
2470 value=> px_rule_in.reduction_amount,
2471 p_log_level_rec => p_log_level_rec);
2472 end if;
2473
2474 exception
2475 when calc_basis_err then
2476 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2477 ,p_log_level_rec => p_log_level_rec);
2478
2479 when call_reduction_amount_err then
2480 fa_srvr_msg.add_message (
2481 calling_fn => l_calling_fn,
2482 name => 'FA_SHARED_UNKNOWN_ERROR',
2483 translate => TRUE
2484 ,p_log_level_rec => p_log_level_rec);
2485 raise;
2486
2487 when positive_reduction_err then
2488 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2489 ,p_log_level_rec => p_log_level_rec);
2490 raise;
2491
2492 when others then
2493 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2494 ,p_log_level_rec => p_log_level_rec);
2495 raise;
2496 end POSITIVE_REDUCTION;
2497
2498 --------------------------------------------------------------------------------
2499 -- Procedure HALF_YEAR:
2500 -- This procedure is the additional functionality for depreciable basis rule
2501 -- 'Year End Balance with Half Year Rule'.
2502 --------------------------------------------------------------------------------
2503
2504 PROCEDURE HALF_YEAR (
2505 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
2506 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
2507 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2508 is
2509
2510 l_change_in_cost NUMBER :=0;
2511 l_change_in_cost_to_reduce NUMBER :=0;
2512 l_total_change_in_cost NUMBER :=0;
2513 l_net_proceeds NUMBER :=0;
2514 l_net_proceeds_to_reduce NUMBER :=0;
2515 l_total_net_proceeds NUMBER :=0;
2516 l_first_half_cost NUMBER :=0;
2517 l_first_half_cost_to_reduce NUMBER :=0;
2518 l_second_half_cost NUMBER :=0;
2519 l_second_half_cost_to_reduce NUMBER :=0;
2520
2521 l_first_reduction_amount NUMBER :=0;
2522 l_fy_begin_nbv NUMBER :=0;
2523 l_check_amount NUMBER :=0;
2524
2525 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.half_year';
2526
2527 calc_basis_err exception;
2528
2529 begin
2530
2531 ------------------------------------------------------
2532 -- Event TYPE: RETIREMENT
2533 ------------------------------------------------------
2534 if px_rule_in.event_type ='RETIREMENT' then
2535
2536 -- Adjustment amount is cost retired.
2537 -- Changed positive amount to negative amount.
2538
2539 px_rule_in.adjustment_amount
2540 := - px_rule_in.adjustment_amount;
2541 end if;
2542
2543 -------------------------------------------------------
2544 -- This rule is FLAT method type only
2545 -------------------------------------------------------
2546 if (px_rule_in.method_type = 'FLAT') then
2547
2548 ----------------------------------------
2549 -- Call CALC_REDUCTION_AMOUNT function
2550 ----------------------------------------
2551
2552 if px_rule_in.event_type
2553 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ','AMORT_ADJ2','AMORT_ADJ3')
2554 then
2555
2556 if not FA_CALC_DEPRN_BASIS1_PKG.CALC_REDUCTION_AMOUNT
2557 (
2558 p_asset_id => px_rule_in.asset_id,
2559 p_group_asset_id => px_rule_in.group_asset_id,
2560 p_asset_type => px_rule_in.asset_type,
2561 p_book_type_code => px_rule_in.book_type_code,
2562 p_period_counter => px_rule_in.period_counter,
2563 p_transaction_date => px_rule_in.adj_transaction_date_entered,
2564 p_half_year_rule_flag => 'Y',
2565 p_mrc_sob_type_code => px_rule_in.mrc_sob_type_code,
2566 x_change_in_cost => l_change_in_cost,
2567 x_change_in_cost_to_reduce => l_change_in_cost_to_reduce,
2568 x_total_change_in_cost => l_total_change_in_cost,
2569 x_net_proceeds => l_net_proceeds,
2570 x_net_proceeds_to_reduce => l_net_proceeds_to_reduce,
2571 x_total_net_proceeds => l_total_net_proceeds,
2572 x_first_half_cost => l_first_half_cost,
2573 x_first_half_cost_to_reduce => l_first_half_cost_to_reduce,
2574 x_second_half_cost => l_second_half_cost,
2575 x_second_half_cost_to_reduce => l_second_half_cost_to_reduce,
2576 p_log_level_rec => p_log_level_rec)
2577 then
2578
2579 fa_srvr_msg.add_message (
2580 calling_fn => l_calling_fn,
2581 name => 'FA_SHARED_UNKNOWN_ERROR',
2582 translate => FALSE,
2583 p_log_level_rec => p_log_level_rec);
2584
2585 end if; -- Call CALC_REDUCTION_AMOUNT
2586 end if; -- Condition of CALC_REDUCTION_AMOUNT call
2587
2588 if p_log_level_rec.statement_level then
2589 fa_debug_pkg.add(fname=>'half_year',
2590 element=>'l_change_in_cost',
2591 value=> l_change_in_cost, p_log_level_rec => p_log_level_rec);
2592 fa_debug_pkg.add(fname=>'half_year',
2593 element=>'l_change_in_cost_to_reduce',
2594 value=> l_change_in_cost_to_reduce, p_log_level_rec => p_log_level_rec);
2595 fa_debug_pkg.add(fname=>'half_year',
2596 element=>'l_total_change_in_cost',
2597 value=> l_total_change_in_cost, p_log_level_rec => p_log_level_rec);
2598 fa_debug_pkg.add(fname=>'half_year',
2599 element=>'l_net_proceeds',
2600 value=> l_net_proceeds, p_log_level_rec => p_log_level_rec);
2601 fa_debug_pkg.add(fname=>'half_year',
2602 element=>'l_net_proceeds_to_reduce',
2603 value=> l_net_proceeds_to_reduce, p_log_level_rec => p_log_level_rec);
2604 fa_debug_pkg.add(fname=>'half_year',
2605 element=>'l_total_net_proceeds',
2606 value=> l_total_net_proceeds, p_log_level_rec => p_log_level_rec);
2607 fa_debug_pkg.add(fname=>'half_year',
2608 element=>'l_first_half_cost',
2609 value=> l_first_half_cost, p_log_level_rec => p_log_level_rec);
2610 fa_debug_pkg.add(fname=>'half_year',
2611 element=>'l_first_half_cost_to_reduce',
2612 value=> l_first_half_cost_to_reduce, p_log_level_rec => p_log_level_rec);
2613 fa_debug_pkg.add(fname=>'half_year',
2614 element=>'l_second_half_cost',
2615 value=> l_second_half_cost, p_log_level_rec => p_log_level_rec);
2616 fa_debug_pkg.add(fname=>'half_year',
2617 element=>'l_second_half_cost_to_reduce',
2618 value=> l_second_half_cost_to_reduce, p_log_level_rec => p_log_level_rec);
2619 end if;
2620
2621 -- Check whether 1st half year's reduction amount
2622 l_fy_begin_nbv
2623 := nvl(px_rule_in.eofy_recoverable_cost,0)
2624 + nvl(px_rule_in.eofy_salvage_value,0)
2625 - nvl(px_rule_in.eofy_reserve,0);
2626
2627 l_check_amount
2628 := nvl(l_fy_begin_nbv,0)
2629 + nvl(l_first_half_cost,0) - nvl(l_net_proceeds,0);
2630
2631 -- Calculate first reduction amount
2632 if (l_check_amount < 0
2633 and px_rule_in.apply_reduction_flag is null)
2634 or nvl(px_rule_in.apply_reduction_flag,'N') ='Y'
2635 then
2636
2637 l_first_reduction_amount
2638 := l_check_amount
2639 *nvl(px_rule_in.reduction_rate,0);
2640
2641 if px_rule_in.asset_type='GROUP'
2642 and l_first_reduction_amount<>0 then
2643 -- Apply reduction amount to group asset
2644 px_rule_in.apply_reduction_flag :='Y';
2645 end if;
2646
2647 else
2648
2649 l_first_reduction_amount :=0;
2650
2651 end if; -- End calculate first reduction amount
2652
2653 -- Cacluate reduction_amount
2654 if p_log_level_rec.statement_level then
2655 fa_debug_pkg.add(fname=>'half_year',
2656 element=>'l_first_reduction_amount',
2657 value=> l_first_reduction_amount, p_log_level_rec => p_log_level_rec);
2658 fa_debug_pkg.add(fname=>'half_year',
2659 element=>'l_second_half_cost_to_reduce',
2660 value=> l_second_half_cost_to_reduce, p_log_level_rec => p_log_level_rec);
2661 end if;
2662
2663 px_rule_in.reduction_amount
2664 := nvl(l_first_reduction_amount,0)
2665 + nvl(l_second_half_cost_to_reduce,0);
2666
2667 end if; -- End FLAT method type
2668
2669 if p_log_level_rec.statement_level then
2670 fa_debug_pkg.add(fname=>'half_year',
2671 element=>'reduction_amount',
2672 value=> px_rule_in.reduction_amount, p_log_level_rec => p_log_level_rec);
2673 end if;
2674
2675 exception
2676 when calc_basis_err then
2677 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2678 ,p_log_level_rec => p_log_level_rec);
2679
2680 when others then
2681 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2682 ,p_log_level_rec => p_log_level_rec);
2683 raise;
2684 end HALF_YEAR;
2685
2686 --------------------------------------------------------------------------------
2687 -- Procedure BEGINNING_PERIOD:
2688 -- This procedure is the additional functionality for depreciable basis rule
2689 -- 'Beginning Period'.
2690 --------------------------------------------------------------------------------
2691
2692 PROCEDURE BEGINNING_PERIOD (
2693 px_rule_in IN OUT NOCOPY fa_std_types.fa_deprn_rule_in_struct,
2694 px_rule_out IN OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
2695 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2696 is
2697
2698 cursor GET_DEPRN_EXP is
2699 select nvl(sum(decode(adj.debit_credit_flag,
2700 'DR',adj.adjustment_amount,
2701 'CR',-adj.adjustment_amount)),0) beginning_period_expense
2702 from fa_adjustments adj
2703 where adj.asset_id = px_rule_in.asset_id
2704 and adj.book_type_code = px_rule_in.book_type_code
2705 and adj.period_counter_created = px_rule_in.period_counter
2706 and adj.source_type_code ='DEPRECIATION'
2707 and adj.adjustment_type ='EXPENSE';
2708
2709 cursor GET_DEPRN_EXP_M is
2710 select nvl(sum(decode(adj.debit_credit_flag,
2711 'DR',adj.adjustment_amount,
2712 'CR',-adj.adjustment_amount)),0) beginning_period_expense
2713 from fa_adjustments_mrc_v adj
2714 where adj.asset_id = px_rule_in.asset_id
2715 and adj.book_type_code = px_rule_in.book_type_code
2716 and adj.period_counter_created = px_rule_in.period_counter
2717 and adj.source_type_code ='DEPRECIATION'
2718 and adj.adjustment_type ='EXPENSE';
2719
2720
2721 -- create new cursor which look for retirement catchup.
2722 -- dr retirement expense with positive amount if cost is positive
2723 -- dr retirement expense with negative amount if cost is negative
2724
2725 l_current_period_expense NUMBER :=0; -- Depreciation Expense at this period
2726 l_eop_reserve NUMBER :=0; -- End of prior period reserve
2727 l_exclude_salvage_value_flag VARCHAR2(3); --Exclude salvage value flag
2728 l_retirement_reserve NUMBER :=0; -- Retirement Reserve new line added
2729
2730 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis2_pkg.beginning_period';
2731
2732 calc_basis_err exception;
2733
2734 begin
2735
2736 -------------------------------------------------------
2737 -- This rule is FLAT method type only
2738 -------------------------------------------------------
2739 if (px_rule_in.method_type in ('FLAT', 'FORMULA')) then
2740
2741 ---------------------------------------------
2742 -- All Event types:
2743 -- Get Depreciation Expenses at this period
2744 -- and calculate Prior Period Reserve
2745 ---------------------------------------------
2746
2747 if (px_rule_in.method_type = 'FLAT') then
2748 if px_rule_in.mrc_sob_type_code <>'R' then
2749 open GET_DEPRN_EXP;
2750 fetch GET_DEPRN_EXP into l_current_period_expense;
2751 close GET_DEPRN_EXP;
2752 else -- MRC
2753 open GET_DEPRN_EXP_M;
2754 fetch GET_DEPRN_EXP_M into l_current_period_expense;
2755 close GET_DEPRN_EXP_M;
2756 end if;
2757
2758 l_eop_reserve := nvl(px_rule_in.current_total_rsv,0)
2759 - nvl(px_rule_in.adj_reserve,0)
2760 - nvl(l_current_period_expense,0);
2761 elsif (px_rule_in.method_type = 'FORMULA') then
2762
2763 l_eop_reserve := nvl(px_rule_in.current_total_rsv,0) -
2764 nvl(px_rule_in.adj_reserve,0);
2765 end if;
2766
2767 ----------------------------------------------------------------
2768 -- Event Type: INITIAL_ADDITION
2769 ----------------------------------------------------------------
2770 if (px_rule_in.event_type in ('ADDITION', 'INITIAL_ADDITION')) THEN
2771
2772 if not fa_cache_pkg.fazcdp
2773 (x_book_type_code => px_rule_in.book_type_code,
2774 x_period_counter => px_rule_in.period_counter,
2775 x_effective_date => null,
2776 p_log_level_rec => p_log_level_rec) then
2777 raise calc_basis_err;
2778 end if;
2779
2780 --
2781 -- Bug4115689: Added check against reserve and set adj_cost
2782 -- to rec cost ONLY if this addition is with reserve
2783 --
2784 if (px_rule_in.transaction_date_entered <
2785 fa_cache_pkg.fazcdp_record.calendar_period_open_date) and
2786 ((nvl(px_rule_in.adj_reserve, 0) <> 0) or
2787 (nvl(px_rule_in.current_rsv, 0) <> 0)) then
2788
2789 if px_rule_in.calc_basis='COST' then
2790
2791 px_rule_out.new_adjusted_cost :=
2792 nvl(px_rule_in.recoverable_cost,0);
2793
2794 elsif px_rule_in.calc_basis = 'NBV' then
2795 px_rule_out.new_adjusted_cost :=
2796 nvl(px_rule_in.recoverable_cost,0) - nvl(l_eop_reserve,0);
2797
2798 else -- unexpected calc_basis
2799 raise calc_basis_err;
2800 end if; -- End calc_basis
2801
2802 else
2803 px_rule_out.new_adjusted_cost := 0;
2804 px_rule_in.eop_salvage_value := 0;
2805
2806 if p_log_level_rec.statement_level then
2807 fa_debug_pkg.add(fname=>'beginning_period',
2808 element=>'updated eop_salvage_value',
2809 value=> px_rule_in.eop_salvage_value,
2810 p_log_level_rec => p_log_level_rec);
2811 end if;
2812 end if;
2813
2814 end if; -- End of ADDITION / INITIAL ADDITION
2815
2816
2817
2818 ------------------------------------------------
2819 -- Event types: AMORT_ADJ, AMORT_ADJ2, AMORT_ADJ3
2820 ------------------------------------------------
2821
2822 if px_rule_in.event_type in ('AMORT_ADJ','AMORT_ADJ2', 'AMORT_ADJ3')
2823 then
2824 if px_rule_in.calc_basis='COST' then
2825
2826 px_rule_out.new_adjusted_cost :=
2827 nvl(px_rule_in.eop_recoverable_cost,0);
2828
2829 elsif px_rule_in.calc_basis = 'NBV' then
2830 px_rule_out.new_adjusted_cost :=
2831 nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0);
2832
2833 else -- unexpected calc_basis
2834 raise calc_basis_err;
2835 end if; -- End calc_basis
2836
2837 end if; -- End Event type: AMORT_ADJ, AMORT_ADJ2 , 'AMORT_ADJ3'
2838
2839 -------------------------------
2840 -- Event type: EXPENSED_ADJ
2841 -------------------------------
2842
2843 if px_rule_in.event_type='EXPENSED_ADJ' then
2844
2845 if px_rule_in.calc_basis='COST' then
2846
2847 px_rule_out.new_adjusted_cost :=
2848 nvl(px_rule_in.recoverable_cost,0);
2849
2850 elsif px_rule_in.calc_basis = 'NBV' then
2851 px_rule_out.new_adjusted_cost :=
2852 (nvl(px_rule_in.eop_recoverable_cost,0)
2853 - nvl(px_rule_in.hyp_total_rsv,0));
2854
2855 -- core development removed these lines
2856 -- +(nvl(px_rule_in.recoverable_cost,0)
2857 -- - nvl(px_rule_in.hyp_total_rsv,0)))/2;
2858
2859 else -- unexpected calc_basis
2860 raise calc_basis_err;
2861 end if; -- End calc_basis
2862
2863 end if; -- End event type: EXPENSED_ADJ
2864
2865 ----------------------------
2866 -- Event type: RETIREMENT
2867 ----------------------------
2868 if px_rule_in.event_type='RETIREMENT' then
2869
2870 -- Retirement with Recognized Gain and Loss Immediately When Retired
2871 if px_rule_in.recognize_gain_loss<>'NO' then
2872 if px_rule_in.cost = 0 then
2873 l_retirement_reserve := 0;
2874 else
2875 l_retirement_reserve
2876 := - nvl(px_rule_in.adjustment_amount,0)
2877 / px_rule_in.cost
2878 * nvl(px_rule_in.current_total_rsv,0);
2879 end if;
2880
2881 else -- Do Not Recogized Gain and loss
2882 l_retirement_reserve
2883 := - nvl(px_rule_in.adjustment_amount,0)
2884 + nvl(px_rule_in.nbv_retired,0);
2885 -- + nvl(px_rule_in.proceeds_of_sale,0)
2886 -- - nvl(px_rule_in.cost_of_removal,0);
2887
2888 end if; -- End recognize_gain_loss
2889
2890 if px_rule_in.calc_basis='COST' then
2891
2892 px_rule_out.new_adjusted_cost :=
2893 nvl(px_rule_in.eop_recoverable_cost,0);
2894
2895 elsif px_rule_in.calc_basis = 'NBV' then
2896 px_rule_out.new_adjusted_cost :=
2897 nvl(px_rule_in.eop_recoverable_cost,0) - nvl(l_eop_reserve,0);
2898
2899 else -- unexpected calc_basis
2900 raise calc_basis_err;
2901 end if; -- End calc_basis
2902
2903 end if; -- Event type: Retirement
2904
2905 --end new version
2906
2907 ------------------------------------------------------------
2908 -- Event Type: AFTER_DEPRN (After Depreciation)
2909 ------------------------------------------------------------
2910 if (px_rule_in.event_type ='AFTER_DEPRN') THEN
2911 if px_rule_in.calc_basis='COST' then
2912
2913 px_rule_out.new_adjusted_cost :=
2914 nvl(px_rule_in.recoverable_cost,0);
2915
2916 px_rule_in.use_old_adj_cost_flag :='N';
2917 end if;
2918 end if;
2919
2920 ---------------------------------------------------------------------------
2921 -- All Event types except of 'AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ',:
2922 -- Check exclude salvage value flag.
2923 -- And if exclude salvage value flag is Yes,
2924 -- reduce salvage value from new adjusted cost
2925 ---------------------------------------------------------------------------
2926 if fa_cache_pkg.fazccmt(px_rule_in.method_code,
2927 px_rule_in.life_in_months,
2928 p_log_level_rec) then
2929
2930 if p_log_level_rec.statement_level then
2931 fa_debug_pkg.add(fname=>'beginning_period',
2932 element=>'fazccmt',
2933 value=> 'Called',
2934 p_log_level_rec => p_log_level_rec);
2935 end if;
2936
2937 l_exclude_salvage_value_flag := fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag;
2938
2939 end if;
2940
2941
2942 if px_rule_in.event_type
2943 not in ('AFTER_DEPRN','AFTER_DEPRN2','UNPLANNED_ADJ')
2944 and Upper(l_exclude_salvage_value_flag) like 'Y%'
2945 and px_rule_in.calc_basis= 'NBV'
2946 then
2947
2948 px_rule_out.new_adjusted_cost :=
2949 px_rule_out.new_adjusted_cost
2950 -nvl(px_rule_in.salvage_value,0);
2951
2952 -- Period Average code used the following:
2953 -- - nvl(px_rule_in.eop_salvage_value,0))/2;
2954
2955 end if;
2956
2957 end if; -- Flat or formula method type
2958
2959 if p_log_level_rec.statement_level then
2960 fa_debug_pkg.add(fname=>'beginning_period',
2961 element=>'new_adjusted_cost',
2962 value=> px_rule_out.new_adjusted_cost,
2963 p_log_level_rec => p_log_level_rec);
2964 end if;
2965
2966
2967 exception
2968 when calc_basis_err then
2969 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2970 ,p_log_level_rec => p_log_level_rec);
2971
2972 when others then
2973 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2974 ,p_log_level_rec => p_log_level_rec);
2975 end BEGINNING_PERIOD; --BEGINNING_PERIOD;
2976
2977 end FA_CALC_DEPRN_BASIS2_PKG;