1 PACKAGE BODY FA_POLISH_PVT AS
2 /* $Header: FAVPOLB.pls 120.11.12010000.1 2008/07/28 13:18:47 appldev ship $ */
3
4 -- === Overview ============================================================ --
5 --
6 -- The Polish Tax Enhancement feature is fairly complicated with a number of
7 -- business rules that do not follow normal Fixed Assets behaviour. Therefore,
8 -- I am going to summarize a few points here. For a more in-depth look at
9 -- this feature, refer to the design document or the official published
10 -- documentation. For precise formulas such as the formula for calculating
11 -- the new basis after a negative adjustment, refer to the code.
12 --
13 -- Definitions:
14 -- -------------
15 -- adjusted_cost: depreciation basis for an asset to derive deprn amount
16 -- adjusted_recoverable_cost: cost that determines when an asset becomes
17 -- fully reserved.
18 --
19 -- Polish Mechanisms or Rules:
20 -- ---> : change happens automatically when moving from year 1 to year 2
21 -- -?-> : switch test is performed to check if method moves to next code
22 --
23 -- 1: 30 ---> D2 -?-> FR
24 -- 2: 30 ---> FR
25 -- 3: DM ---> D2 -?-> FR
26 -- 4: DM ---> FR
27 -- 5: SD -?-> FR
28 --
29 -- Switch Codes:
30 -- 30: adjusted rate = 30% in period asset placed in service
31 -- : ignore prorate convention
32 -- : no deprn in rest of first year after first periods
33 -- : basis = adjusted_cost
34 -- D2: adjusted rate = flat rate * deprn factor
35 -- : basis = adjusted_cost - previous year reserve + first year reserve
36 -- SD: adjusted rate = flat rate * deprn factor
37 -- : basis = adjusted_cost - previous year reserve
38 -- DM: adjusted rate = flat rate * alternate deprn factor
39 -- : basis = adjusted_cost
40 -- FR: adjusted rate = flat rate
41 -- : basis = adjusted_cost
42 --
43 -- Basic Features:
44 -- ----------------
45 -- Polish assets are assets that are assigned to a flat-rate method using
46 -- one of the five Polish depreciation basis rules in addition to a bonus rule.
47 -- The bonus rule determines the deprn factor and alternate deprn factor.
48 --
49 -- Throughout the asset's life, it moves from one rate, which is determined
50 -- by the switch code, to another. Sometimes the switch happens automatically,
51 -- such as for some mechanisms from year 1 to year 2. Other times, you need
52 -- to run a switch test to see if the asset moves to the next method in the
53 -- chain. The switch test happens only at the close of the fiscal and
54 -- determines the rate for all of the periods in that year. In general, the
55 -- switch test checks to see which code will generate the greater monthly
56 -- depreciation in the year and returns that code. Once an asset has moved
57 -- to the next part of the chain, it cannot revert back to a previous one.
58 --
59 -- In addition to the rate, the switch code also determines the depreciation
60 -- basis for an asset. The formulas for the rate and the depreciation basis
61 -- for each switch code is in the definitions section.
62 --
63 -- When you perform a transaction on an asset, it may affect the basis for
64 -- that asset immediately or not take affect until the next fiscal year
65 -- depending on the transaction and the switch code the asset currently has.
66 -- For a negative cost adjustment, the formula to determine the new basis is
67 -- also predicated on the value of the polish adjustment calculate basis flag
68 -- which is tied to the method.
69 --
70 -- Partial retirement:
71 -- basis: for formula see Calc_Basis_Partial_Ret procedure
72 --
73 -- Negative adjustment w/ polish_adj_calc_basis_flag set to Yes:
74 -- basis: for formula see Calc_Basis_Neg_Adj_Flag_Yes procedure
75 --
76 -- Negative adjustment w/ polish_adj_calc_basis_flag set to No:
77 -- basis: for formula see Calc_Basis_Neg_Adj_Flag_No
78 --
79 -- Positive adjustment
80 -- basis: if switch is FR, take new adjusted_cost as basis immediately
81 -- : otherwise, do not take it until next fiscal year, running new
82 -- : new adjusted_cost through the switch test if necessary.
83 --
84 -- In addition, if you do a negative adjustment, the asset can over-depreciate.
85 -- That is, when a new basis is calculated for the asset, the asset's
86 -- adjusted_recoverable_cost is also set to this amount so that the asset can
87 -- depreciate to this new basis amount even if it is greater than the asset's
88 -- cost. This brings up an important point with regard to the code. The
89 -- Polish asset's adjusted_cost and adjusted_recoverable cost that it uses
90 -- to determine the depreciation basis are the ones in fa_books. Instead, I
91 -- currently use polish_deprn_basis in fa_books_summary for adjusted_cost and
92 -- polish_adj_rec_cost in fa_books_summary for adjusted_recoverable_cost.
93 -- The reason for this is that I treat the values in fa_books as the baseline.
94 -- For example, when the user changes the cost from 20,000 to 12,000, the
95 -- value of adjusted_cost and adjusted_recoverable_cost are both 12,000 in
96 -- fa_books. However, since we need to run negative adjustments through a
97 -- formula to determine the basis, etc, these numbers will be different from
98 -- the 12,000. However, I still want the user to see the 12,000, so if they
99 -- perform an subsequent transactions on the asset, they are using the 12,000
100 -- figure and not the new figure calculated by the program. Therefore, for
101 -- Polish assets, the depreciation basis and the adjusted_recoverable_cost
102 -- used to determine when the asset becomes fully reserved are essentially
103 -- internal figures and not ones that the user sees. Of course, we could
104 -- display these figures in fields separately that the user can see such as
105 -- in the new new book value of the asset.
106 --
107 -- This Polish procedure is currently called from the depreciation engine
108 -- and the adjustment API (when catchup is needed). For this, we have two
109 -- modes, DEPRN and ADJUSTMENT. We have separate code for adjustments
110 -- because none of the rows have been inserted into the tables when
111 -- catchup is called. Therefore, we can't derive from the tables when
112 -- the adjustment occurred, how much was adjusted, and so forth. During
113 -- depreciation, all of this is stored in the tables, so we can drill down
114 -- and obtain the information we need that way. For adjustments, we are
115 -- currently reading some global variables that the adjustment API provides,
116 -- and this is enough to calculate the appropriate rate and cost. Note
117 -- that using global variables is not an ideal solution as they do not work
118 -- if called from OA Framework since the variables are reset constantly and
119 -- not when you expect.
120 --
121 -- Business Rules:
122 -- ----------------
123 -- This is a summary of different business rules that Polish assets need to
124 -- follow. Some of them have been mentioned previously.
125 --
126 -- * Assets with a switch code of 30, take a flat rate of 30% in the period
127 -- they are placed in service regardless of prorate convention. On a
128 -- related note, please remember if you are using a following month prorate
129 -- convention for a Polish asset using one of the other rules, if you have
130 -- the Depreciate When Placed in Service Flag checked for your following
131 -- month prorate convention in the Setup Prorate Conventions form, the asset
132 -- will still take depreciation in the period it was added. For example, if
133 -- you add such an asset in October 2003 with a following month prorate
134 -- convention and the Depreciate When Placed In Service flag checked, the
135 -- asset will take two months depreciation (November and December) for the
136 -- rest of 2003 because it is a following month convention. However, it will
137 -- spread these two months of depreciation across three months since it will
138 -- begin depreciating in October. If the Depreciate When Placed In Service
139 -- flag is not checked, the asset would begin depreciating in November 2003
140 -- and spread two months of depreciation across two months, November and
141 -- December as you would expect.
142 --
143 -- * You can never move backwards in the switch chain. For example, after
144 -- an asset moves to FR, it can never move back to SD, even if you made a
145 -- cost adjustment to the asset where if you performed the switch test,
146 -- it would result in SD. It still must stay FR.
147 --
148 -- * The switch test for the next year is determined after the close of the
149 -- last period in the fiscal year. Therefore even if you do an adjustment
150 -- in the first period of the next year before depreciation is charged,
151 -- the switch test is still performed based upon the values for the last
152 -- year. All subsequent rules for that switch code also apply for the first
153 -- period of that year. Therefore, if the first period is supposed to be
154 -- SD, all rules regarding SD apply such as a positive cost adjustment
155 -- does not affect the basis for SD in the year of adjustment.
156 --
157 -- * For a positive cost adjustment, the basis only changes in the current
158 -- year if the switch is FR. For any other code, the basis remains the
159 -- same until the next year when the new cost takes affect.
160 --
161 -- * For a negative cost adjustment, the basis changes immediately for any
162 -- switch value except 30 since all periods after 30 don't depreciate at all.
163 --
164 -- * For partial retirement, the basis changes immediately for any switch
165 -- value except 30.
166 --
167 -- * Backdated adjustments across fiscal years do not affect the switch in
168 -- those years. Therefore, when you are calculating the catchup in those
169 -- previous years, the rates stay the same as they were. In addition, the
170 -- basis may not change either, depending on whether it is allowed. For
171 -- example, if you do a backdated positive adjustment on year, and the
172 -- previous year were SD, the basis would not change in that previous year
173 -- either. The basis would change if it were FR or a negative adjustment
174 -- instead of a positive one. Note that since partial retirements cannot
175 -- cross fiscal years, this is not relevant for this type of transaction.
176 --
177 -- * If you have multiple adjustments in the same period, you take the
178 -- summation of the adjustments to determine the net adjustment for the
179 -- period and treat this as a single cost change rather than taking the
180 -- adjustments individually. For example, if you have a positive
181 -- adjustments of 100 and a negative adjustment of 25, you treat this as
182 -- a positive 75 adjustment.
183 --
184 -- Possible Improvements:
185 -- -----------------------
186 -- One thing mentioned is the use of global variables and the drawbacks.
187 -- These should be removed ideally.
188 --
189 -- One thing that I do not do here that could be improved is the
190 -- calculate of the switch code. That is, typically, the switch
191 -- only need to be calculated at the end of the year, and at times
192 -- when a transaction occurs. However, I calculate the switch
193 -- during every period regardless. Theoretically, in the middle of a
194 -- year w/ no transactions, I could take a look at the switch_code in
195 -- fa_books_summary, and just use that for the current period. If
196 -- performance improvements need to be made, that can be one of the
197 -- first places to look. One of the reasons that I didn't do this
198 -- was that I wanted to make the code as generic as possible. That is,
199 -- when transactions occur, you do need to do the switch mid-year on
200 -- occasion. When backdated adjustments happen, you might need to do
201 -- this over a range of periods, and here the switch_code in
202 -- fa_books_summary may not give you the value that you are looking
203 -- for anymore since that was the switch value at the time of that
204 -- period, but now that the backdated adjustment has occurred, it's
205 -- obsolete. To try to avoid these complexities, I just do the
206 -- switch all the time even though there are performance drawbacks.
207 --
208 -- ============================================================ Overview === --
209
210 PROCEDURE Calc_Polish_Rate_Cost (
211 p_Book_Type_Code IN VARCHAR2,
212 p_Asset_Id IN NUMBER,
213 p_Polish_Rule IN NUMBER,
214 p_Deprn_Factor IN NUMBER,
215 p_Alternate_Deprn_Factor IN NUMBER,
216 p_Polish_Adj_Calc_Basis_Flag
217 IN VARCHAR2,
218 p_Rate IN NUMBER,
219 p_Depreciate_Flag IN VARCHAR2,
220 p_Adjusted_Cost IN NUMBER,
221 p_Recoverable_Cost IN NUMBER,
222 p_Adjusted_Recoverable_Cost
223 IN NUMBER,
224 p_Fiscal_Year IN NUMBER,
225 p_Period_Num IN NUMBER,
226 p_Periods_Per_Year IN NUMBER,
227 p_Year_Retired IN VARCHAR2,
228 p_MRC_Sob_Type_Code IN VARCHAR2,
229 x_Rate OUT NOCOPY NUMBER,
230 x_Depreciate_Flag OUT NOCOPY VARCHAR2,
231 x_Adjusted_Cost OUT NOCOPY NUMBER,
232 x_Adjusted_Recoverable_Cost
233 OUT NOCOPY NUMBER,
234 x_Success OUT NOCOPY INTEGER,
235 p_Calling_Fn IN VARCHAR2,
236
237 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
238 IS
239
240 l_period_counter number(15);
241 l_first_period_counter number(15);
242 l_first_fiscal_year number(4);
243 l_year_of_life number(15);
244 l_old_adjusted_cost number;
245 l_adjusted_cost number;
246 l_adjusted_recoverable_cost number;
247 l_adjusted_rate number;
248 l_depreciate_flag varchar2(3);
249 l_switch_code varchar2(2);
250
251 l_adjusted_cost_old number;
252 l_adj_rec_cost_old number;
253
254 l_transaction_type_code varchar2(20);
255 l_transaction_header_id number(15);
256
257 l_adjustment_amount number;
258 l_retirement_amount number;
259 l_bef_trxn_period_counter number(15);
260 l_pos_neg_adjustment varchar2(3);
261 l_prev_adj_rec_cost number;
262 l_prev_basis number;
263 l_prev_reserve number;
264
265 l_first_year_period_counter number(15);
266 l_prev_year_period_counter number(15);
267 l_first_year_reserve number;
268 l_prev_year_reserve number;
269 l_prev_year_adjusted_cost number;
270 l_prev_year_adj_rec_cost number;
271 l_min_period_counter number;
272
273 l_previous_year_adjustment boolean;
274 l_previous_year_retirement boolean;
275
276 l_open_period_counter number(15);
277 l_bs_row number(15);
278
279 polish_err exception;
280
281 BEGIN
282
286 l_previous_year_adjustment := FALSE;
283 -- === Initializations ================================================== --
284
285 l_transaction_type_code := 'NONE';
287 l_previous_year_retirement := FALSE;
288
289 l_first_year_reserve := 0;
290 l_prev_year_reserve := 0;
291
292 -- Fix for Bug #3649102. Need to initialize this in case we're in 1st yr
293 l_prev_year_adj_rec_cost := p_Adjusted_Recoverable_Cost;
294
295 if (p_Calling_Fn = 'fadpdp.faxgpolr') then
296 -- We are calling this from the deprn engine. Set the mode.
297 FA_POLISH_PVT.calling_mode := 'DEPRN';
298 elsif (p_Calling_Fn = 'faproj.faxgpolr') then
299 FA_POLISH_PVT.calling_mode := 'PROJECT';
300 elsif (p_Calling_Fn = 'fa_cde_pkg.whatif.faxgpolr') then
301 FA_POLISH_PVT.calling_mode := 'WHATIF';
302 end if;
303
304 -- Derive current period_counter. Note that this may be different from
305 -- the current open period if we are looping through backdated periods.
306 l_period_counter := (p_Fiscal_Year * p_Periods_Per_Year) + p_Period_Num;
307
308 -- Determine the year_of_life. For a Polish asset, the first year is
309 -- always the year of the dpis regardless of the prorate convention.
310 begin
311
312 if (p_MRC_SOB_Type_Code = 'R') then
313
314 select fy.fiscal_year
315 into l_first_fiscal_year
316 from fa_fiscal_year fy,
317 fa_book_controls_mrc_v bc,
318 fa_books_mrc_v bks
319 where bc.book_type_code = p_Book_Type_Code
320 and bc.fiscal_year_name = fy.fiscal_year_name
321 and bks.book_type_code = p_Book_Type_Code
322 and bks.asset_id = p_Asset_Id
323 and bks.transaction_header_id_out is null
324 and bks.date_placed_in_service between
325 fy.start_date and fy.end_date;
326
327 select dp.period_counter
328 into l_first_period_counter
329 from fa_deprn_periods_mrc_v dp,
330 fa_books_mrc_v bks
331 where dp.book_type_code = p_Book_Type_Code
332 and bks.book_type_code = p_Book_Type_Code
333 and bks.asset_id = p_Asset_Id
334 and bks.transaction_header_id_out is null
335 and bks.date_placed_in_service between
336 dp.calendar_period_open_date and dp.calendar_period_close_date;
337
338 select period_counter
339 into l_open_period_counter
340 from fa_deprn_periods_mrc_v
341 where book_type_code = p_Book_Type_Code
342 and period_close_date is null;
343
344 else
345
346 select fy.fiscal_year
347 into l_first_fiscal_year
348 from fa_fiscal_year fy,
349 fa_book_controls bc,
350 fa_books bks
351 where bc.book_type_code = p_Book_Type_Code
352 and bc.fiscal_year_name = fy.fiscal_year_name
353 and bks.book_type_code = p_Book_Type_Code
354 and bks.asset_id = p_Asset_Id
355 and bks.transaction_header_id_out is null
356 and bks.date_placed_in_service between
357 fy.start_date and fy.end_date;
358
359 select dp.period_counter
360 into l_first_period_counter
361 from fa_deprn_periods dp,
362 fa_books bks
363 where dp.book_type_code = p_Book_Type_Code
364 and bks.book_type_code = p_Book_Type_Code
365 and bks.asset_id = p_Asset_Id
366 and bks.transaction_header_id_out is null
367 and bks.date_placed_in_service between
368 dp.calendar_period_open_date and dp.calendar_period_close_date;
369
370 select period_counter
371 into l_open_period_counter
372 from fa_deprn_periods
373 where book_type_code = p_Book_Type_Code
374 and period_close_date is null;
375
376 end if;
377 exception
378 when others then
379 -- We're going to assume that if there are errors, the asset is
380 -- too old for the calendar, so we'll just set the first year to 0
381 l_first_fiscal_year := 0;
382 l_first_period_counter := 0;
383 end;
384
385 l_year_of_life := p_Fiscal_Year - l_first_fiscal_year + 1;
386
387 -- For an adjustment, the p_Adjusted_Cost passed in is not necessarily the
388 -- new cost. We will need to derive this amount.
389 if (FA_POLISH_PVT.calling_mode = 'ADJUSTMENT') then
390
391 if (p_MRC_SOB_Type_Code = 'R') then
392
393 select adjusted_cost
394 into l_old_adjusted_cost
395 from fa_books_mrc_v
396 where book_type_code = p_Book_Type_Code
397 and asset_id = p_Asset_Id
398 and transaction_header_id_out is null;
399
400 else
401
402 select adjusted_cost
403 into l_old_adjusted_cost
404 from fa_books
405 where book_type_code = p_Book_Type_Code
406 and asset_id = p_Asset_Id
407 and transaction_header_id_out is null;
408 end if;
409
410 l_adjusted_cost := FA_POLISH_PVT.adjustment_amount + l_old_adjusted_cost;
414
411 else
412 l_adjusted_cost := p_Adjusted_Cost;
413 end if;
415 -- Save parameters into local variables if we may be modifying them
416 l_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
417 l_adjusted_rate := p_Rate;
418 l_depreciate_flag := p_Depreciate_Flag;
419
420 -- ============================================== End Initializations === --
421 -- --
422 -- === Initial Checks =================================================== --
423
424 -- If before first period of life, then just return
425 if (l_period_counter < l_first_period_counter) then
426 x_Rate := 0;
427 x_Depreciate_Flag := l_depreciate_flag;
428 x_Adjusted_Cost := l_adjusted_cost;
429 x_Adjusted_Recoverable_Cost := l_adjusted_recoverable_cost;
430 X_Success := 1;
431 return;
432 end if;
433
434 -- If we are not calling this from a recognized program, then error.
435 if (FA_POLISH_PVT.calling_mode not in ('DEPRN', 'ADJUSTMENT',
436 'PROJECT', 'WHATIF')) then
437
438 fa_srvr_msg.add_message(
439 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
440 name => '***FA_POLISH_INVALID_MODE***',
441 p_log_level_rec => p_log_level_rec);
442
443 raise polish_err;
444 end if;
445
446 -- =============================================== End Initial Checks === --
447 -- --
448 -- === Check Transactions =============================================== --
449
450 if (FA_POLISH_PVT.calling_mode = 'ADJUSTMENT') then
451 -- If we are currently doing an adjustment, no rows for the adjustment
452 -- will be been added yet, so there will not exist rows for the
453 -- adjustment in fa_transaction_headers or fa_adjustments.
454 l_transaction_type_code := 'ADJUSTMENT';
455
456 else
457
458 -- Check to see if any transactions have been performed on this asset,
459 -- specifically, adjustments and partial retirements, which would affect
460 -- the rate and deprn basis. We want to get the most recent transaction.
461
462 -- Fix for Bug #3629991. Added transaction_header_id to select b/c
463 -- we may have multiple transactions to select from fa_adjustments
464 -- later.
465 begin
466 select transaction_type_code,
467 transaction_header_id
468 into l_transaction_type_code,
469 l_transaction_header_id
470 from fa_transaction_headers
471 where book_type_code = p_Book_Type_Code
472 and asset_id = p_Asset_ID
473 and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
474 and transaction_header_id =
475 (
476 select max(transaction_header_id)
477 from fa_transaction_headers
478 where book_type_code = p_Book_Type_Code
479 and asset_id = p_Asset_ID
480 and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
481 );
482
483 exception
484 when no_data_found then
485 -- No adjustments or partial retirements found
486 l_transaction_type_code := 'NONE';
487 end;
488 end if;
489
490 -- =========================================== End Check Transactions === --
491 -- --
492 -- === Calculate Transaction Values ===================================== --
493 -- If there are transactions that were done, let's calculate some
494 -- intermediate values since nearly every scenario will require them.
495
496 if (l_transaction_type_code <> 'NONE') then
497
498 if (not FA_POLISH_PVT.Calc_Trxn_Values (
499 p_book_type_code => p_book_type_code,
500 p_asset_id => p_asset_id,
501 p_transaction_type_code => l_transaction_type_code,
502 p_transaction_header_id => l_transaction_header_id,
503 p_first_period_counter => l_first_period_counter,
504 p_mrc_sob_type_code => p_mrc_sob_type_code,
505 p_calling_mode => fa_polish_pvt.calling_mode,
506 x_adjustment_amount => l_adjustment_amount,
507 x_retirement_amount => l_retirement_amount,
508 x_bef_trxn_period_counter => l_bef_trxn_period_counter,
509 x_pos_neg_adjustment => l_pos_neg_adjustment,
510 x_prev_basis => l_prev_basis,
511 x_prev_adj_rec_cost => l_prev_adj_rec_cost,
512 x_prev_reserve => l_prev_reserve,
513 p_log_level_rec => p_log_level_rec
514 )) then
515 raise polish_err;
516 end if;
517 end if;
518
519 -- ================================= End Calculate Transaction Values === --
520 -- --
521 -- === Calculate Transaction Switch Values ============================== --
522
526 -- new deprn basis may be derived differently based on these values.
523 -- After the first year, we need to calculate some new values because a
524 -- transaction in the middle of a year can cause a switch test to be
525 -- re-done, especially if it is a backdated adjustment. Therefore the
527
528 if ((l_year_of_life > 1) and (p_Year_Retired = 'N')) then
529
530 -- The previous year last period counter is period_ctr - per
531 l_prev_year_period_counter := l_period_counter - p_Period_Num;
532
533 -- The first year last period counter is (begin_fy + 1) * 12)
534 l_first_year_period_counter := (l_first_fiscal_year + 1) * 12;
535
536 if (p_MRC_SOB_Type_Code = 'R') then
537 select deprn_reserve
538 into l_first_year_reserve
539 from fa_deprn_summary_mrc_v
540 where book_type_code = p_Book_Type_Code
541 and asset_id = p_Asset_Id
542 and period_counter = l_first_year_period_counter;
543
544 select deprn_reserve
545 into l_prev_year_reserve
546 from fa_deprn_summary_mrc_v
547 where book_type_code = p_Book_Type_Code
548 and asset_id = p_Asset_Id
549 and period_counter = l_prev_year_period_counter;
550
551 -- Fix for Bug #5550557. Need to catch if books summary row
552 -- does not exist.
553 begin
554
555 select polish_deprn_basis,
556 polish_adj_rec_cost
557 into l_prev_year_adjusted_cost,
558 l_prev_year_adj_rec_cost
559 from fa_books_summary_mrc_v
560 where book_type_code = p_Book_Type_Code
561 and asset_id = p_Asset_Id
562 and period_counter = l_prev_year_period_counter;
563
564 -- Fix for Bug #5907258. If these values are null, can cause
565 -- data corruption. Needs to be fixed first.
566 if (l_prev_year_adjusted_cost is null) or
567 (l_prev_year_adj_rec_cost is null) then
568
569 fa_srvr_msg.add_message(
570 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
571 name => '***FA_POLISH_NULL_COST***',
572 p_log_level_rec => p_log_level_rec);
573
574 raise polish_err;
575 end if;
576 exception
577
578 when no_data_found then
579
580 select min(period_counter)
581 into l_min_period_counter
582 from fa_deprn_periods_mrc_v
583 where book_type_code = p_Book_Type_Code;
584
585 if (l_prev_year_period_counter >= l_min_period_counter) then
586
587 begin
588
589 select bks.adjusted_cost,
590 bks.adjusted_recoverable_cost
591 into l_prev_year_adjusted_cost,
592 l_prev_year_adj_rec_cost
593 from fa_books_mrc_v bks
594 where bks.book_type_code = p_Book_Type_Code
595 and bks.asset_id = p_Asset_Id
596 and bks.rowid =
597 (
598 select max(bks1.rowid)
599 from fa_books_mrc_v bks1,
600 fa_deprn_periods_mrc_v dp
601 where bks1.book_type_code = p_Book_Type_Code
602 and bks1.asset_id = p_Asset_Id
603 and bks1.book_type_code = dp.book_type_code
604 and dp.period_counter = l_prev_year_period_counter
605 and bks1.date_effective <=
606 nvl(dp.period_close_date, sysdate)
607 );
608 exception
609 when others then
610 l_prev_year_adjusted_cost := l_Adjusted_Cost;
611 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
612 end;
613 else
614 begin
615
616 select bks.adjusted_cost,
617 bks.adjusted_recoverable_cost
618 into l_prev_year_adjusted_cost,
619 l_prev_year_adj_rec_cost
620 from fa_books_mrc_v bks
621 where bks.book_type_code = p_Book_Type_Code
622 and bks.asset_id = p_Asset_Id
623 and bks.rowid =
624 (
625 select min(bks1.rowid)
626 from fa_books_mrc_v bks1
627 where bks1.book_type_code = p_Book_Type_Code
628 and bks1.asset_id = p_Asset_Id
629 );
630 exception
631 when others then
632 l_prev_year_adjusted_cost := l_Adjusted_Cost;
633 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
634 end;
635 end if;
636 end;
637
638 else
639 select deprn_reserve
640 into l_first_year_reserve
644 and period_counter = l_first_year_period_counter;
641 from fa_deprn_summary
642 where book_type_code = p_Book_Type_Code
643 and asset_id = p_Asset_Id
645
646 select deprn_reserve
647 into l_prev_year_reserve
648 from fa_deprn_summary
649 where book_type_code = p_Book_Type_Code
650 and asset_id = p_Asset_Id
651 and period_counter = l_prev_year_period_counter;
652
653 -- Fix for Bug #5550557. Need to catch if books summary row
654 -- does not exist.
655 begin
656
657 select polish_deprn_basis,
658 polish_adj_rec_cost
659 into l_prev_year_adjusted_cost,
660 l_prev_year_adj_rec_cost
661 from fa_books_summary
662 where book_type_code = p_Book_Type_Code
663 and asset_id = p_Asset_Id
664 and period_counter = l_prev_year_period_counter;
665
666 -- Fix for Bug #5907258. If these values are null, can cause
667 -- data corruption. Needs to be fixed first.
668 if (l_prev_year_adjusted_cost is null) or
669 (l_prev_year_adj_rec_cost is null) then
670
671 fa_srvr_msg.add_message(
672 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
673 name => '***FA_POLISH_NULL_COST***',
674 p_log_level_rec => p_log_level_rec);
675
676 raise polish_err;
677 end if;
678
679 exception
680
681 when no_data_found then
682
683 select min(period_counter)
684 into l_min_period_counter
685 from fa_deprn_periods
686 where book_type_code = p_Book_Type_Code;
687
688 if (l_prev_year_period_counter >= l_min_period_counter) then
689
690 begin
691
692 select bks.adjusted_cost,
693 bks.adjusted_recoverable_cost
694 into l_prev_year_adjusted_cost,
695 l_prev_year_adj_rec_cost
696 from fa_books bks
697 where bks.book_type_code = p_Book_Type_Code
698 and bks.asset_id = p_Asset_Id
699 and bks.rowid =
700 (
701 select max(bks1.rowid)
702 from fa_books bks1,
703 fa_deprn_periods dp
704 where bks1.book_type_code = p_Book_Type_Code
705 and bks1.asset_id = p_Asset_Id
706 and bks1.book_type_code = dp.book_type_code
707 and dp.period_counter = l_prev_year_period_counter
708 and bks1.date_effective <=
709 nvl(dp.period_close_date, sysdate)
710 );
711 exception
712 when others then
713 l_prev_year_adjusted_cost := l_Adjusted_Cost;
714 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
715 end;
716 else
717 begin
718
719 select bks.adjusted_cost,
720 bks.adjusted_recoverable_cost
721 into l_prev_year_adjusted_cost,
722 l_prev_year_adj_rec_cost
723 from fa_books bks
724 where bks.book_type_code = p_Book_Type_Code
725 and bks.asset_id = p_Asset_Id
726 and bks.rowid =
727 (
728 select min(bks1.rowid)
729 from fa_books bks1
730 where bks1.book_type_code = p_Book_Type_Code
731 and bks1.asset_id = p_Asset_Id
732 );
733 exception
734 when others then
735 l_prev_year_adjusted_cost := l_Adjusted_Cost;
736 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
737 end;
738 end if;
739 end;
740 end if;
741
742 if (l_transaction_type_code = 'ADJUSTMENT') then
743
744 -- Check to see if the ADJUSTMENT occurred in a past fiscal year
745 -- from this current period. If it did, we will need to do the
746 -- switch test again even though we may be in the middle of the
747 -- year. If the transaction occurred in the same fiscal year, we
748 -- may change the basis, but we would still stay with the same
749 -- switch rate since we're not doing the re-test again.
750 -- If the current period counter - period adjustment occurred >=
751 -- period num then the adjustment occurred in a previous year.
752 if (l_period_counter - (l_bef_trxn_period_counter + 1) >=
753 p_Period_Num) then
754
755 -- Adjustment occurred in previous year
756 l_previous_year_adjustment := TRUE;
757
758 -- Fix for Bug #5710413. Need to use correct adjusted cost
762 select adjusted_cost
759 -- for the previous year.
760 if (p_MRC_SOB_Type_Code = 'R') then
761
763 into l_prev_year_adjusted_cost
764 from fa_books_mrc_v
765 where asset_id = p_asset_id
766 and book_type_code = p_book_type_code
767 and transaction_header_id_out is null;
768
769 else
770 select adjusted_cost
771 into l_prev_year_adjusted_cost
772 from fa_books
773 where asset_id = p_asset_id
774 and book_type_code = p_book_type_code
775 and transaction_header_id_out is null;
776 end if;
777
778 else
779 -- Adjustment occurred in current year
780 -- ??? Do we need logic for if adjustment in a future year?
781 l_previous_year_adjustment := FALSE;
782 end if;
783
784 elsif (l_transaction_type_code = 'PARTIAL RETIREMENT') then
785 -- Same logic as above. Partial retirements cannot cross fiscal
786 -- years. However, in the year after the retirement, we still
787 -- need to subtract the previous reserve when doing the switch test.
788 if (l_period_counter - (l_bef_trxn_period_counter + 1) >=
789 p_Period_Num) then
790
791 -- Retirement occurred in previous year
792 l_previous_year_retirement := TRUE;
793 else
794
795 -- Retirement occurred in current year
796 -- ??? Do we need logic for if retirement in a future year?
797 l_previous_year_retirement := FALSE;
798 end if;
799 end if;
800 end if;
801
802 -- ========================== End Calculate Transaction Switch Values === --
803 -- --
804 -- === Derive Rate ====================================================== --
805
806 -- Calculate the rate and switch code for this period.
807 if (not FA_POLISH_PVT.Calc_Rate (
808 p_book_type_code => p_book_type_code,
809 p_asset_id => p_asset_id,
810 p_polish_rule => p_polish_rule,
811 p_year_of_life => l_year_of_life,
812 p_year_retired => p_year_retired,
813 p_period_counter => l_period_counter,
814 p_first_period_counter => l_first_period_counter,
815 p_open_period_counter => l_open_period_counter,
816 p_period_num => p_period_num,
817 p_periods_per_year => p_periods_per_year,
818 p_adjusted_rate => p_rate,
819 p_deprn_factor => p_deprn_factor,
820 p_alternate_deprn_factor => p_alternate_deprn_factor,
821 p_depreciate_flag => p_depreciate_flag,
822 p_first_year_reserve => l_first_year_reserve,
823 p_prev_year_reserve => l_prev_year_reserve,
824 p_prev_year_adjusted_cost
825 => l_prev_year_adjusted_cost,
826 p_prev_year_adj_rec_cost => l_prev_year_adj_rec_cost,
827 p_mrc_sob_type_code => p_mrc_sob_type_code,
828 x_adjusted_rate => l_adjusted_rate,
829 x_depreciate_flag => l_depreciate_flag,
830 x_switch_code => l_switch_code,
831 p_log_level_rec => p_log_level_rec
832 )) then
833 raise polish_err;
834 end if;
835
836 -- ================================ End Derive Rate ===================== --
837 -- --
838 -- === Calculate New Basis ============================================== --
839
840 if ((l_transaction_type_code = 'NONE') or
841 ((l_transaction_type_code = 'ADJUSTMENT') and
842 (l_previous_year_adjustment)) or
843 ((l_transaction_type_code = 'PARTIAL RETIREMENT') and
844 (l_previous_year_retirement))) then
845
846 -- Determine the basis depending on the switch
847 if (l_switch_code in ('30', 'DM')) then
848
849 -- Set the basis to be the fully reservable cost
850 l_adjusted_cost := nvl(l_adjusted_recoverable_cost, 0);
851
852 elsif (l_switch_code = 'D2') then
853
854 -- Set the basis to be cost - previous year rsv + 1st year rsv
855 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
856 l_adjusted_recoverable_cost) -
857 nvl(l_prev_year_reserve,0) +
858 nvl(l_first_year_reserve,0);
859 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
860 l_adjusted_recoverable_cost);
861
862 elsif (l_switch_code = 'SD') then
863
864 -- Set the basis to be the cost - previous year rsv
865 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
866 l_adjusted_recoverable_cost) -
870
867 nvl(l_prev_year_reserve,0);
868 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
869 l_adjusted_recoverable_cost);
871 elsif (l_switch_code = 'FR') then
872
873 -- Set the basis to be the fully reservable cost
874 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
875 l_adjusted_recoverable_cost);
876 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
877 l_adjusted_recoverable_cost);
878
879 end if;
880
881 else
882
883 -- Need to copy into separate variables since can't have same
884 -- as IN and OUT parameters when using NOCOPY
885 l_adjusted_cost_old := l_adjusted_cost;
886 l_adj_rec_cost_old := l_adjusted_recoverable_cost;
887
888 -- Calculate the new basis depending on the transaction
889 if not (Calc_Basis_Trxn (
890 p_book_type_code => p_Book_Type_Code,
891 p_transaction_type_code => l_transaction_type_code,
892 p_pos_neg_adjustment => l_pos_neg_adjustment,
893 p_polish_adj_calc_basis_flag => p_polish_adj_calc_basis_flag,
894 p_adjusted_cost => l_adjusted_cost_old,
895 p_adjusted_recoverable_cost => l_adj_rec_cost_old,
896 p_prev_adj_rec_cost => l_prev_adj_rec_cost,
897 p_prev_reserve => l_prev_reserve,
898 p_prev_basis => l_prev_basis,
899 p_adjustment_amount => l_adjustment_amount,
900 p_retirement_amount => l_retirement_amount,
901 p_switch_code => l_switch_code,
902 x_adjusted_cost => l_adjusted_cost,
903 x_adjusted_recoverable_cost => l_adjusted_recoverable_cost,
904 p_log_level_rec => p_log_level_rec
905 )) then
906 raise polish_err;
907 end if;
908 end if;
909
910 -- ================================================== End Audit Trail === --
911 -- --
912 -- === Set OUT Parameters =============================================== --
913
914 x_Adjusted_Cost := l_adjusted_cost;
915 x_Adjusted_Recoverable_Cost := l_adjusted_recoverable_cost;
916 x_Depreciate_Flag := l_depreciate_flag;
917 x_Rate := l_adjusted_rate;
918
919 -- =========================================== End Set OUT Parameters === --
920
921 X_Success := 1;
922
923 EXCEPTION
924 WHEN polish_err THEN
925
926 if (sqlcode <> 0) then
927 fa_rx_conc_mesg_pkg.log(sqlerrm);
928 end if;
929
930 fa_srvr_msg.add_sql_error (
931 calling_fn => 'fa_polish_pvt.Calc_Polish_Rate_Cost',
932 p_log_level_rec => p_log_level_rec);
933 x_Rate := 0;
934 X_Success := 0;
935
936 WHEN OTHERS THEN
937
938 if (sqlcode <> 0) then
939 fa_rx_conc_mesg_pkg.log(sqlerrm);
940 end if;
941
942 fa_srvr_msg.add_sql_error (
943 calling_fn => 'fa_polish_pvt.Calc_Polish_Rate_Cost',
944 p_log_level_rec => p_log_level_rec);
945
946 x_Rate := 0;
947 X_Success := 0;
948
949 END Calc_Polish_Rate_Cost;
950
951 FUNCTION Calc_Basis_Neg_Adj_Flag_Yes (
952 p_book_type_code IN VARCHAR2,
953 p_old_cost IN NUMBER,
954 p_old_reserve IN NUMBER,
955 p_adjustment_amount IN NUMBER,
956 x_new_deprn_basis OUT NOCOPY NUMBER,
957 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
958 RETURN BOOLEAN IS
959
960 l_adjustment_reserve number;
961 l_adjustment_nbv number;
962
963 l_dummy_bool boolean;
964
965 BEGIN
966
967 l_adjustment_reserve := p_old_reserve * p_adjustment_amount / p_old_cost;
968
969 l_adjustment_nbv := p_adjustment_amount - l_adjustment_reserve;
970
971 x_new_deprn_basis := p_old_cost - l_adjustment_nbv;
972
973 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_log_level_rec);
974
975 return (TRUE);
976
977 EXCEPTION
978 WHEN OTHERS THEN
979
980 if (sqlcode <> 0) then
981 fa_rx_conc_mesg_pkg.log(sqlerrm);
982 end if;
983
984 fa_srvr_msg.add_sql_error (
985 calling_fn => 'fa_polish_pvt.Calc_Basis_Neg_Adj_Flag_Yes',
986 p_log_level_rec => p_log_level_rec);
987 return (FALSE);
988 END Calc_Basis_Neg_Adj_Flag_Yes;
989
990 FUNCTION Calc_Basis_Neg_Adj_Flag_No (
991 p_book_type_code IN VARCHAR2,
992 p_old_cost IN NUMBER,
993 p_old_deprn_basis IN NUMBER,
994 p_adjustment_amount IN NUMBER,
995 x_new_deprn_basis OUT NOCOPY NUMBER,
996 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
997 RETURN BOOLEAN IS
998
1002
999 l_dummy_bool boolean;
1000
1001 BEGIN
1003 x_new_deprn_basis := p_old_deprn_basis -
1004 (p_old_deprn_basis * p_adjustment_amount /
1005 p_old_cost);
1006
1007 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_log_level_rec);
1008
1009 return (TRUE);
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013
1014 if (sqlcode <> 0) then
1015 fa_rx_conc_mesg_pkg.log(sqlerrm);
1016 end if;
1017
1018 fa_srvr_msg.add_sql_error (
1019 calling_fn => 'fa_polish_pvt.Calc_Basis_Neg_Adj_Flag_No',
1020 p_log_level_rec => p_log_level_rec);
1021 return (FALSE);
1022
1023 END Calc_Basis_Neg_Adj_Flag_No;
1024
1025 FUNCTION Calc_Basis_Partial_Ret (
1026 p_book_type_code IN VARCHAR2,
1027 p_old_cost IN NUMBER,
1028 p_old_deprn_basis IN NUMBER,
1029 p_retirement_amount IN NUMBER,
1030 x_new_deprn_basis OUT NOCOPY NUMBER,
1031 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1032 RETURN BOOLEAN IS
1033
1034 l_dummy_bool boolean;
1035
1036 BEGIN
1037
1038 x_new_deprn_basis := p_old_deprn_basis -
1039 (p_old_deprn_basis * p_retirement_amount /
1040 p_old_cost);
1041
1042 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_log_level_rec);
1043
1044 return (TRUE);
1045
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048
1049 if (sqlcode <> 0) then
1050 fa_rx_conc_mesg_pkg.log(sqlerrm);
1051 end if;
1052
1053 fa_srvr_msg.add_sql_error (
1054 calling_fn => 'fa_polish_pvt.Calc_Basis_Partial_Ret',
1055 p_log_level_rec => p_log_level_rec);
1056 return (FALSE);
1057
1058 END Calc_Basis_Partial_Ret;
1059
1060 FUNCTION Calc_Basis_Trxn (
1061 p_book_type_code IN VARCHAR2,
1062 p_transaction_type_code IN VARCHAR2,
1063 p_pos_neg_adjustment IN VARCHAR2,
1064 p_polish_adj_calc_basis_flag
1065 IN VARCHAR2,
1066 p_adjusted_cost IN NUMBER,
1067 p_adjusted_recoverable_cost
1068 IN NUMBER,
1069 p_prev_adj_rec_cost IN NUMBER,
1070 p_prev_basis IN NUMBER,
1071 p_prev_reserve IN NUMBER,
1072 p_adjustment_amount IN NUMBER,
1073 p_retirement_amount IN NUMBER,
1074 p_switch_code IN VARCHAR2,
1075 x_adjusted_cost OUT NOCOPY NUMBER,
1076 x_adjusted_recoverable_cost OUT NOCOPY NUMBER,
1077 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1078 RETURN BOOLEAN IS
1079
1080 basis_err exception;
1081
1082 BEGIN
1083
1084 if ((p_transaction_type_code = 'ADJUSTMENT') and
1085 (p_pos_neg_adjustment = 'NEG')) then
1086
1087 -- Calculate new basis
1088 if (p_Polish_Adj_Calc_Basis_Flag = 'Y') then
1089 if not (Calc_Basis_Neg_Adj_Flag_Yes (
1090 p_book_type_code => p_book_type_code,
1091 p_old_cost => p_prev_adj_rec_cost,
1092 p_old_reserve => p_prev_reserve,
1093 p_adjustment_amount => p_adjustment_amount,
1094 x_new_deprn_basis => x_adjusted_cost,
1095 p_log_level_rec => p_log_level_rec
1096 )) then
1097 raise basis_err;
1098 end if;
1099
1100 -- For a negative adjustment with the polish_adj_calc_basis_flag
1101 -- checked, we over-depreciate the asset, setting the cost at
1102 -- which it becomes fully reserved, i.e., the
1103 -- adjusted_recoverable_cost to the new basis.
1104 x_adjusted_recoverable_cost := x_adjusted_cost;
1105
1106 else
1107 if not (Calc_Basis_Neg_Adj_Flag_No (
1108 p_book_type_code => p_book_type_code,
1109 p_old_cost => p_prev_adj_rec_cost,
1110 p_old_deprn_basis => p_prev_basis,
1111 p_adjustment_amount => p_adjustment_amount,
1112 x_new_deprn_basis => x_adjusted_cost,
1113 p_log_level_rec => p_log_level_rec
1114 )) then
1115 raise basis_err;
1116 end if;
1117
1118 -- These values don't change from the defaults
1119 x_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
1120 end if;
1121
1122 elsif ((p_transaction_type_code = 'ADJUSTMENT') and
1123 (p_pos_neg_adjustment = 'POS')) then
1124
1125 -- For a positive adjustment, the new basis is just the new
1126 -- cost if the switch is currently FR. However, for anything else,
1130 x_adjusted_recoverable_cost := p_adjusted_cost;
1127 -- the basis stays the same as it was before the adjustment.
1128 if (p_switch_code = 'FR') then
1129 x_adjusted_cost := p_adjusted_cost;
1131
1132 else
1133 -- Need to go w/ the value before the adjustment occurred.
1134 x_adjusted_cost := p_prev_basis;
1135
1136 -- We will change the fully reservable cost to reflect the new cost
1137 -- however.
1138 x_adjusted_recoverable_cost := p_adjusted_recoverable_cost;
1139 end if;
1140 elsif (p_transaction_type_code = 'PARTIAL RETIREMENT') then
1141
1142 if not (Calc_Basis_Partial_Ret (
1143 p_book_type_code => p_book_type_code,
1144 p_old_cost => p_prev_adj_rec_cost,
1145 p_old_deprn_basis => p_prev_basis,
1146 p_retirement_amount => p_retirement_amount,
1147 x_new_deprn_basis => x_adjusted_cost,
1148 p_log_level_rec => p_log_level_rec
1149 )) then
1150 raise basis_err;
1151 end if;
1152
1153 -- These values don't change from the defaults
1154 x_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
1155 end if;
1156
1157 return (TRUE);
1158
1159 EXCEPTION
1160 WHEN basis_err THEN
1161
1162 if (sqlcode <> 0) then
1163 fa_rx_conc_mesg_pkg.log(sqlerrm);
1164 end if;
1165
1166 fa_srvr_msg.add_sql_error (
1167 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn',
1168 p_log_level_rec => p_log_level_rec);
1169 return (FALSE);
1170
1171 WHEN OTHERS THEN
1172
1173 if (sqlcode <> 0) then
1174 fa_rx_conc_mesg_pkg.log(sqlerrm);
1175 end if;
1176
1177 fa_srvr_msg.add_sql_error (
1178 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn',
1179 p_log_level_rec => p_log_level_rec);
1180 return (FALSE);
1181 END Calc_Basis_Trxn;
1182
1183 FUNCTION Calc_Trxn_Values (
1184 p_book_type_code IN VARCHAR2,
1185 p_asset_id IN NUMBER,
1186 p_transaction_type_code IN VARCHAR2,
1187 p_transaction_header_id IN NUMBER,
1188 p_first_period_counter IN NUMBER,
1189 p_mrc_sob_type_code IN VARCHAR2,
1190 p_calling_mode IN VARCHAR2,
1191 x_adjustment_amount OUT NOCOPY NUMBER,
1192 x_retirement_amount OUT NOCOPY NUMBER,
1193 x_bef_trxn_period_counter OUT NOCOPY NUMBER,
1194 x_pos_neg_adjustment OUT NOCOPY VARCHAR2,
1195 x_prev_basis OUT NOCOPY NUMBER,
1196 x_prev_adj_rec_cost OUT NOCOPY NUMBER,
1197 x_prev_reserve OUT NOCOPY NUMBER,
1198 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1199 RETURN BOOLEAN IS
1200
1201 l_amortization_start_date date;
1202 l_debit_credit_flag varchar2(2);
1203 l_min_period_counter number;
1204
1205 polish_err exception;
1206
1207 BEGIN
1208
1209 -- ??? Probably need to still account for multiple adjustments or
1210 -- partial retirements in a single period. If we get the max id, we'd
1211 -- be missing some transactions. Maybe need to do a sum or something.
1212 if (p_transaction_type_code = 'ADJUSTMENT') then
1213
1214 if (p_calling_mode = 'ADJUSTMENT') then
1215
1216 l_amortization_start_date := FA_POLISH_PVT.amortization_start_date;
1217
1218 if (p_mrc_sob_type_code = 'R') then
1219
1220 select period_counter - 1
1221 into x_bef_trxn_period_counter
1222 from fa_deprn_periods_mrc_v
1223 where book_type_code = p_Book_Type_Code
1224 and l_amortization_start_date between
1225 calendar_period_open_date and calendar_period_close_date;
1226 else
1227 select period_counter - 1
1228 into x_bef_trxn_period_counter
1229 from fa_deprn_periods
1230 where book_type_code = p_Book_Type_Code
1231 and l_amortization_start_date between
1232 calendar_period_open_date and calendar_period_close_date;
1233 end if;
1234
1235 -- Can't go before the first period of the asset's life
1236 if (x_bef_trxn_period_counter < p_first_period_counter) then
1237 x_bef_trxn_period_counter := p_first_period_counter;
1238 end if;
1239
1240 x_adjustment_amount := FA_POLISH_PVT.adjustment_amount;
1241
1242 if (x_adjustment_amount > 0) then
1243 -- Positive adjustment
1244 x_pos_neg_adjustment := 'POS';
1245 else
1246 x_pos_neg_adjustment := 'NEG';
1247
1248 -- Always need to have this positive.
1249 x_adjustment_amount := x_adjustment_amount * -1;
1250 end if;
1251
1252 else
1253
1254 if (p_MRC_SOB_Type_Code = 'R') then
1255
1256 -- Determine if it's a positive or negative adj, amount and period
1257 -- immediately before the period of adjustment
1258 select adjustment_amount,
1262 from fa_adjustments_mrc_v
1259 debit_credit_flag
1260 into x_adjustment_amount,
1261 l_debit_credit_flag
1263 where book_type_code = p_Book_Type_Code
1264 and asset_id = p_Asset_Id
1265 and transaction_header_id = p_transaction_header_id
1266 and source_type_code = 'ADJUSTMENT'
1267 and adjustment_type = 'COST';
1268
1269 else
1270 select adjustment_amount,
1271 debit_credit_flag
1272 into x_adjustment_amount,
1273 l_debit_credit_flag
1274 from fa_adjustments
1275 where book_type_code = p_Book_Type_Code
1276 and asset_id = p_Asset_Id
1277 and transaction_header_id = p_transaction_header_id
1278 and source_type_code = 'ADJUSTMENT'
1279 and adjustment_type = 'COST';
1280
1281 end if;
1282
1283 if ((l_debit_credit_flag = 'CR') and (x_adjustment_amount >= 0)) then
1284
1285 -- Negative adjustment
1286 x_pos_neg_adjustment := 'NEG';
1287
1288 elsif ((l_debit_credit_flag = 'DR') and (x_adjustment_amount < 0)) then
1289 -- Negative adjustment
1290 x_pos_neg_adjustment := 'NEG';
1291
1292 -- Flip the adjustment amount
1293 x_adjustment_amount := x_adjustment_amount * -1;
1294
1295 elsif ((l_debit_credit_flag = 'CR') and (x_adjustment_amount < 0)) then
1296 -- Positive adjustment
1297 x_pos_neg_adjustment := 'POS';
1298
1299 elsif ((l_debit_credit_flag = 'DR') and
1300 (x_adjustment_amount >= 0)) then
1301
1302 -- Positive adjustment
1303 x_pos_neg_adjustment := 'POS';
1304
1305 -- Flip the adjustment amount
1306 x_adjustment_amount := x_adjustment_amount * -1;
1307 end if;
1308 end if;
1309
1310 if (p_calling_mode = 'DEPRN') then
1311
1312 if (x_pos_neg_adjustment = 'POS') then
1313
1314 if (p_MRC_SOB_Type_Code = 'R') then
1315
1316 select dp.period_counter - 1
1317 into x_bef_trxn_period_counter
1318 from fa_deprn_periods_mrc_v dp,
1319 fa_transaction_headers th
1320 where th.transaction_header_id = p_transaction_header_id
1321 and dp.book_type_code = p_Book_Type_Code
1322 and th.date_effective between
1323 dp.period_open_date and
1324 nvl(dp.period_close_date, sysdate);
1325 else
1326
1327 select dp.period_counter - 1
1328 into x_bef_trxn_period_counter
1329 from fa_deprn_periods dp,
1330 fa_transaction_headers th
1331 where th.transaction_header_id = p_transaction_header_id
1332 and dp.book_type_code = p_Book_Type_Code
1333 and th.date_effective between
1334 dp.period_open_date and
1335 nvl(dp.period_close_date, sysdate);
1336 end if;
1337
1338 -- Can't go before the first period of the asset's life
1339 if (x_bef_trxn_period_counter < p_first_period_counter) then
1340 x_bef_trxn_period_counter := p_first_period_counter;
1341 end if;
1342
1343 elsif (x_pos_neg_adjustment = 'NEG') then
1344
1345 if (p_MRC_SOB_Type_Code = 'R') then
1346
1347 select dp.period_counter - 1
1348 into x_bef_trxn_period_counter
1349 from fa_deprn_periods_mrc_v dp,
1350 fa_transaction_headers th
1351 where th.transaction_header_id = p_transaction_header_id
1352 and dp.book_type_code = p_Book_Type_Code
1353 and th.transaction_date_entered between
1354 dp.calendar_period_open_date and
1355 dp.calendar_period_close_date;
1356 else
1357
1358 select dp.period_counter - 1
1359 into x_bef_trxn_period_counter
1360 from fa_deprn_periods dp,
1361 fa_transaction_headers th
1362 where th.transaction_header_id = p_transaction_header_id
1363 and dp.book_type_code = p_Book_Type_Code
1364 and th.transaction_date_entered between
1365 dp.calendar_period_open_date and
1366 dp.calendar_period_close_date;
1367 end if;
1368
1369 -- Can't go before the first period of the asset's life
1370 if (x_bef_trxn_period_counter < p_first_period_counter) then
1371 x_bef_trxn_period_counter := p_first_period_counter;
1372 end if;
1373 end if;
1374 end if;
1375
1376 -- Determine the cost and the reserve in the period before the adj
1377 if (p_MRC_SOB_Type_Code = 'R') then
1378
1379 -- Fix for Bug #5550557. Need to catch if books summary row
1380 -- does not exist.
1381 begin
1382
1383 select polish_deprn_basis,
1384 polish_adj_rec_cost
1385 into x_prev_basis,
1389 and asset_id = p_Asset_Id
1386 x_prev_adj_rec_cost
1387 from fa_books_summary_mrc_v
1388 where book_type_code = p_Book_Type_Code
1390 and period_counter = x_bef_trxn_period_counter;
1391
1392 -- Fix for Bug #5907258. If these values are null, can cause
1393 -- data corruption. Needs to be fixed first.
1394 if (x_prev_basis is null) or
1395 (x_prev_adj_rec_cost is null) then
1396
1397 fa_srvr_msg.add_message(
1398 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1399 name => '***FA_POLISH_NULL_COST***',
1400 p_log_level_rec => p_log_level_rec);
1401
1402 raise polish_err;
1403 end if;
1404
1405 exception
1406
1407 when no_data_found then
1408
1409 select min(period_counter)
1410 into l_min_period_counter
1411 from fa_deprn_periods_mrc_v
1412 where book_type_code = p_Book_Type_Code;
1413
1414 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1415
1416 begin
1417
1418 select bks.adjusted_cost,
1419 bks.adjusted_recoverable_cost
1420 into x_prev_basis,
1421 x_prev_adj_rec_cost
1422 from fa_books_mrc_v bks
1423 where bks.book_type_code = p_Book_Type_Code
1424 and bks.asset_id = p_Asset_Id
1425 and bks.rowid =
1426 (
1427 select max(bks1.rowid)
1428 from fa_books_mrc_v bks1,
1429 fa_deprn_periods_mrc_v dp
1430 where bks1.book_type_code = p_Book_Type_Code
1431 and bks1.asset_id = p_Asset_Id
1432 and bks1.book_type_code = dp.book_type_code
1433 and dp.period_counter = x_bef_trxn_period_counter
1434 and bks1.date_effective <=
1435 nvl(dp.period_close_date, sysdate)
1436 );
1437 end;
1438 else
1439 begin
1440
1441 select bks.adjusted_cost,
1442 bks.adjusted_recoverable_cost
1443 into x_prev_basis,
1444 x_prev_adj_rec_cost
1445 from fa_books_mrc_v bks
1446 where bks.book_type_code = p_Book_Type_Code
1447 and bks.asset_id = p_Asset_Id
1448 and bks.rowid =
1449 (
1450 select min(bks1.rowid)
1451 from fa_books_mrc_v bks1
1452 where bks1.book_type_code = p_Book_Type_Code
1453 and bks1.asset_id = p_Asset_Id
1454 );
1455 end;
1456 end if;
1457 end;
1458
1459 select deprn_reserve
1460 into x_prev_reserve
1461 from fa_deprn_summary_mrc_v
1462 where book_type_code = p_Book_Type_Code
1463 and asset_id = p_Asset_Id
1464 and period_counter = x_bef_trxn_period_counter;
1465 else
1466
1467 -- Fix for Bug #5550557. Need to catch if books summary row
1468 -- does not exist.
1469 begin
1470
1471 select polish_deprn_basis,
1472 polish_adj_rec_cost
1473 into x_prev_basis,
1474 x_prev_adj_rec_cost
1475 from fa_books_summary
1476 where book_type_code = p_Book_Type_Code
1477 and asset_id = p_Asset_Id
1478 and period_counter = x_bef_trxn_period_counter;
1479
1480 -- Fix for Bug #5907258. If these values are null, can cause
1481 -- data corruption. Needs to be fixed first.
1482 if (x_prev_basis is null) or
1483 (x_prev_adj_rec_cost is null) then
1484
1485 fa_srvr_msg.add_message(
1486 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1487 name => '***FA_POLISH_NULL_COST***',
1488 p_log_level_rec => p_log_level_rec);
1489
1490 raise polish_err;
1491 end if;
1492
1493 exception
1494
1495 when no_data_found then
1496
1497 select min(period_counter)
1498 into l_min_period_counter
1499 from fa_deprn_periods
1500 where book_type_code = p_Book_Type_Code;
1501
1502 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1503
1504 begin
1505
1506 select bks.adjusted_cost,
1507 bks.adjusted_recoverable_cost
1508 into x_prev_basis,
1509 x_prev_adj_rec_cost
1510 from fa_books bks
1511 where bks.book_type_code = p_Book_Type_Code
1512 and bks.asset_id = p_Asset_Id
1513 and bks.rowid =
1517 fa_deprn_periods dp
1514 (
1515 select max(bks1.rowid)
1516 from fa_books bks1,
1518 where bks1.book_type_code = p_Book_Type_Code
1519 and bks1.asset_id = p_Asset_Id
1520 and bks1.book_type_code = dp.book_type_code
1521 and dp.period_counter = x_bef_trxn_period_counter
1522 and bks1.date_effective <=
1523 nvl(dp.period_close_date, sysdate)
1524 );
1525 end;
1526 else
1527 begin
1528
1529 select bks.adjusted_cost,
1530 bks.adjusted_recoverable_cost
1531 into x_prev_basis,
1532 x_prev_adj_rec_cost
1533 from fa_books bks
1534 where bks.book_type_code = p_Book_Type_Code
1535 and bks.asset_id = p_Asset_Id
1536 and bks.rowid =
1537 (
1538 select min(bks1.rowid)
1539 from fa_books bks1
1540 where bks1.book_type_code = p_Book_Type_Code
1541 and bks1.asset_id = p_Asset_Id
1542 );
1543 end;
1544 end if;
1545 end;
1546
1547 select deprn_reserve
1548 into x_prev_reserve
1549 from fa_deprn_summary
1550 where book_type_code = p_Book_Type_Code
1551 and asset_id = p_Asset_Id
1552 and period_counter = x_bef_trxn_period_counter;
1553 end if;
1554
1555 -- Fix for Bug #3629784. If you have an asset with a following month
1556 -- prorate convention, the basis will be 0 in the first period of its
1557 -- life since you don't want to depreciate until the next period.
1558 -- However, if you do an adjustment that is effective in the second
1559 -- period, the previous basis will get a value of 0, which you don't
1560 -- want. You need the actual cost here since you do want to
1561 -- depreciate in the 2nd period.
1562 if (x_bef_trxn_period_counter = p_first_period_counter) then
1563 x_prev_basis := x_prev_adj_rec_cost;
1564 end if;
1565 elsif (p_transaction_type_code = 'PARTIAL RETIREMENT') then
1566
1567 -- Get the retirement values
1568 if (p_MRC_SOB_Type_Code = 'R') then
1569
1570 select adjustment_amount,
1571 debit_credit_flag
1572 into x_retirement_amount,
1573 l_debit_credit_flag
1574 from fa_adjustments_mrc_v
1575 where book_type_code = p_Book_Type_Code
1576 and asset_id = p_Asset_Id
1577 and transaction_header_id = p_transaction_header_id
1578 and source_type_code = 'RETIREMENT'
1579 and adjustment_type = 'COST';
1580
1581 select dp.period_counter - 1
1582 into x_bef_trxn_period_counter
1583 from fa_deprn_periods_mrc_v dp,
1584 fa_transaction_headers th
1585 where th.transaction_header_id = p_transaction_header_id
1586 and dp.book_type_code = p_Book_Type_Code
1587 and th.transaction_date_entered between
1588 dp.calendar_period_open_date and
1589 dp.calendar_period_close_date;
1590
1591 else
1592 select adjustment_amount,
1593 debit_credit_flag
1594 into x_retirement_amount,
1595 l_debit_credit_flag
1596 from fa_adjustments
1597 where book_type_code = p_Book_Type_Code
1598 and asset_id = p_Asset_Id
1599 and transaction_header_id = p_transaction_header_id
1600 and source_type_code = 'RETIREMENT'
1601 and adjustment_type = 'COST';
1602
1603 select dp.period_counter - 1
1604 into x_bef_trxn_period_counter
1605 from fa_deprn_periods dp,
1606 fa_transaction_headers th
1607 where th.transaction_header_id = p_transaction_header_id
1608 and dp.book_type_code = p_Book_Type_Code
1609 and th.transaction_date_entered between
1610 dp.calendar_period_open_date and
1611 dp.calendar_period_close_date;
1612 end if;
1613
1614 -- Can't go before the first period of the asset's life
1615 if (x_bef_trxn_period_counter < p_first_period_counter) then
1616 x_bef_trxn_period_counter := p_first_period_counter;
1617 end if;
1618
1619 if (l_debit_credit_flag = 'DR') then
1620 -- Flip adjustment_amount
1621 x_retirement_amount := x_retirement_amount * -1;
1622 end if;
1623
1624 -- Determine the cost in the period before the retirement
1625 if (p_MRC_SOB_Type_Code = 'R') then
1626
1627 -- Fix for Bug #5550557. Need to catch if books summary row
1628 -- does not exist.
1629 begin
1630
1631 select polish_deprn_basis,
1632 polish_adj_rec_cost
1633 into x_prev_basis,
1634 x_prev_adj_rec_cost
1635 from fa_books_summary_mrc_v
1636 where book_type_code = p_Book_Type_Code
1640 -- Fix for Bug #5907258. If these values are null, can cause
1637 and asset_id = p_Asset_Id
1638 and period_counter = x_bef_trxn_period_counter;
1639
1641 -- data corruption. Needs to be fixed first.
1642 if (x_prev_basis is null) or
1643 (x_prev_adj_rec_cost is null) then
1644
1645 fa_srvr_msg.add_message(
1646 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1647 name => '***FA_POLISH_NULL_COST***',
1648 p_log_level_rec => p_log_level_rec);
1649
1650 raise polish_err;
1651 end if;
1652
1653 exception
1654
1655 when no_data_found then
1656
1657 select min(period_counter)
1658 into l_min_period_counter
1659 from fa_deprn_periods_mrc_v
1660 where book_type_code = p_Book_Type_Code;
1661
1662 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1663
1664 begin
1665
1666 select bks.adjusted_cost,
1667 bks.adjusted_recoverable_cost
1668 into x_prev_basis,
1669 x_prev_adj_rec_cost
1670 from fa_books_mrc_v bks
1671 where bks.book_type_code = p_Book_Type_Code
1672 and bks.asset_id = p_Asset_Id
1673 and bks.rowid =
1674 (
1675 select max(bks1.rowid)
1676 from fa_books_mrc_v bks1,
1677 fa_deprn_periods_mrc_v dp
1678 where bks1.book_type_code = p_Book_Type_Code
1679 and bks1.asset_id = p_Asset_Id
1680 and bks1.book_type_code = dp.book_type_code
1681 and dp.period_counter = x_bef_trxn_period_counter
1682 and bks1.date_effective <=
1683 nvl(dp.period_close_date, sysdate)
1684 );
1685 end;
1686 else
1687 begin
1688
1689 select bks.adjusted_cost,
1690 bks.adjusted_recoverable_cost
1691 into x_prev_basis,
1692 x_prev_adj_rec_cost
1693 from fa_books_mrc_v bks
1694 where bks.book_type_code = p_Book_Type_Code
1695 and bks.asset_id = p_Asset_Id
1696 and bks.rowid =
1697 (
1698 select min(bks1.rowid)
1699 from fa_books_mrc_v bks1
1700 where bks1.book_type_code = p_Book_Type_Code
1701 and bks1.asset_id = p_Asset_Id
1702 );
1703 end;
1704 end if;
1705 end;
1706
1707 else
1708
1709 -- Fix for Bug #5550557. Need to catch if books summary row
1710 -- does not exist.
1711 begin
1712
1713 select polish_deprn_basis,
1714 polish_adj_rec_cost
1715 into x_prev_basis,
1716 x_prev_adj_rec_cost
1717 from fa_books_summary
1718 where book_type_code = p_Book_Type_Code
1719 and asset_id = p_Asset_Id
1720 and period_counter = x_bef_trxn_period_counter;
1721
1722 -- Fix for Bug #5907258. If these values are null, can cause
1723 -- data corruption. Needs to be fixed first.
1724 if (x_prev_basis is null) or
1725 (x_prev_adj_rec_cost is null) then
1726
1727 fa_srvr_msg.add_message(
1728 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1729 name => '***FA_POLISH_NULL_COST***',
1730 p_log_level_rec => p_log_level_rec);
1731
1732 raise polish_err;
1733 end if;
1734
1735 exception
1736
1737 when no_data_found then
1738
1739 select min(period_counter)
1740 into l_min_period_counter
1741 from fa_deprn_periods
1742 where book_type_code = p_Book_Type_Code;
1743
1744 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1745
1746 begin
1747
1748 select bks.adjusted_cost,
1749 bks.adjusted_recoverable_cost
1750 into x_prev_basis,
1751 x_prev_adj_rec_cost
1752 from fa_books bks
1753 where bks.book_type_code = p_Book_Type_Code
1754 and bks.asset_id = p_Asset_Id
1755 and bks.rowid =
1756 (
1757 select max(bks1.rowid)
1758 from fa_books bks1,
1759 fa_deprn_periods dp
1760 where bks1.book_type_code = p_Book_Type_Code
1761 and bks1.asset_id = p_Asset_Id
1762 and bks1.book_type_code = dp.book_type_code
1766 );
1763 and dp.period_counter = x_bef_trxn_period_counter
1764 and bks1.date_effective <=
1765 nvl(dp.period_close_date, sysdate)
1767 end;
1768 else
1769 begin
1770
1771 select bks.adjusted_cost,
1772 bks.adjusted_recoverable_cost
1773 into x_prev_basis,
1774 x_prev_adj_rec_cost
1775 from fa_books bks
1776 where bks.book_type_code = p_Book_Type_Code
1777 and bks.asset_id = p_Asset_Id
1778 and bks.rowid =
1779 (
1780 select min(bks1.rowid)
1781 from fa_books bks1
1782 where bks1.book_type_code = p_Book_Type_Code
1783 and bks1.asset_id = p_Asset_Id
1784 );
1785 end;
1786 end if;
1787 end;
1788 end if;
1789
1790 -- Fix for Bug #3629784. If you have an asset with a following month
1791 -- prorate convention, the basis will be 0 in the first period of its
1792 -- life since you don't want to depreciate until the next period.
1793 -- However, if you do a retirement that is effective in the second
1794 -- period, the previous basis will get a value of 0, which you don't
1795 -- want. You need the actual cost here since you do want to
1796 -- depreciate in the 2nd period.
1797 if (x_bef_trxn_period_counter = p_first_period_counter) then
1798 x_prev_basis := x_prev_adj_rec_cost;
1799 end if;
1800 end if;
1801
1802 return (TRUE);
1803
1804 EXCEPTION
1805
1806 WHEN polish_err THEN
1807
1808 if (sqlcode <> 0) then
1809 fa_rx_conc_mesg_pkg.log(sqlerrm);
1810 end if;
1811
1812 fa_srvr_msg.add_sql_error (
1813 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn',
1814 p_log_level_rec => p_log_level_rec);
1815 return (FALSE);
1816
1817 WHEN OTHERS THEN
1818
1819 if (sqlcode <> 0) then
1820 fa_rx_conc_mesg_pkg.log(sqlerrm);
1821 end if;
1822
1823 fa_srvr_msg.add_sql_error (
1824 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn',
1825 p_log_level_rec => p_log_level_rec);
1826 return (FALSE);
1827 END Calc_Trxn_Values;
1828
1829 FUNCTION Calc_Rate (
1830 p_book_type_code IN VARCHAR2,
1831 p_asset_id IN NUMBER,
1832 p_polish_rule IN NUMBER,
1833 p_year_of_life IN NUMBER,
1834 p_year_retired IN VARCHAR2,
1835 p_period_counter IN NUMBER,
1836 p_first_period_counter IN NUMBER,
1837 p_open_period_counter IN NUMBER,
1838 p_period_num IN NUMBER,
1839 p_periods_per_year IN NUMBER,
1840 p_adjusted_rate IN NUMBER,
1841 p_deprn_factor IN NUMBER,
1842 p_alternate_deprn_factor IN NUMBER,
1843 p_depreciate_flag IN VARCHAR2,
1844 p_first_year_reserve IN NUMBER,
1845 p_prev_year_reserve IN NUMBER,
1846 p_prev_year_adjusted_cost
1847 IN NUMBER,
1848 p_prev_year_adj_rec_cost IN NUMBER,
1849 p_mrc_sob_type_code IN VARCHAR2,
1850 x_adjusted_rate OUT NOCOPY NUMBER,
1851 x_depreciate_flag OUT NOCOPY VARCHAR2,
1852 x_switch_code OUT NOCOPY VARCHAR2,
1853 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1854 RETURN BOOLEAN IS
1855
1856 FA_POLISH_30_RULE constant number := .3;
1857
1858 l_prev_switch_code varchar2(2);
1859 l_new_basis number;
1860 l_switch_value number;
1861
1862 calc_rate_err exception;
1863
1864 BEGIN
1865
1866 -- Default values
1867 x_adjusted_rate := p_adjusted_rate;
1868 x_depreciate_flag := p_depreciate_flag;
1869 x_switch_code := 'XX';
1870
1871 -- In an asset's first year of life, it has a specific rate and switch
1872 -- code. In addition, we do not do the switch test in the first year.
1873 if ((p_year_of_life = 1) and (p_Year_Retired = 'N')) then
1874
1875 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
1876 FA_STD_TYPES.FAD_DBR_POLISH_2)) then
1877
1878 x_switch_code := '30';
1879
1880 elsif (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_3,
1881 FA_STD_TYPES.FAD_DBR_POLISH_4)) then
1882
1883 x_switch_code := 'DM';
1884
1885 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
1886
1887 x_switch_code := 'SD';
1888
1889 else
1893 calling_fn => 'fa_polish_pvt.calc_rate',
1890 -- Not one of the 5 Polish Mechanisms.
1891
1892 fa_srvr_msg.add_message(
1894 name => '***FA_POLISH_INVALID_MECH***',
1895 p_log_level_rec => p_log_level_rec);
1896
1897 raise calc_rate_err;
1898 end if;
1899 end if;
1900
1901 -- After the first year, some mechanisms automatically move to a new switch
1902 if ((p_year_of_life > 1) and (p_Year_Retired = 'N')) then
1903 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
1904 FA_STD_TYPES.FAD_DBR_POLISH_3)) then
1905 if (p_Year_Of_Life = 2) then
1906
1907 -- For 2nd year, rate is adj_rate * deprn_factor
1908 -- No switch test is done in the second year
1909 x_switch_code := 'D2';
1910 x_adjusted_rate := p_adjusted_rate * p_Deprn_Factor;
1911
1912 else
1913 -- After year 2, we will always need to do the switch test.
1914 -- We're going to use XX to signify that we'll figure out the
1915 -- switch and new basis later.
1916 x_switch_code := 'XX';
1917
1918 end if;
1919 elsif (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_2,
1920 FA_STD_TYPES.FAD_DBR_POLISH_4)) then
1921 -- After the first year, rate is adj_rate
1922 -- No switch test is done
1923 x_switch_code := 'FR';
1924 x_adjusted_rate := p_adjusted_rate;
1925
1926 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
1927 -- For Mech 5, we do the switch test immediately after year 1.
1928 -- We're going to use XX to signify that we'll figure out the
1929 -- switch and new basis later.
1930 x_switch_code := 'XX';
1931
1932 end if;
1933 end if;
1934
1935 -- Check if old period
1936 if ((x_switch_code = 'XX') and
1937 (p_period_counter < p_open_period_counter)) then
1938
1939 -- We never modify a switch_code once we set it, so if we are doing a
1940 -- backdated transaction or something else where we're looking at old
1941 -- periods, just take the switch_code that was there originally. The
1942 -- nvl is to handle perhaps a method change where the asset wasn't
1943 -- always a Polish one.
1944 if (p_mrc_sob_type_code = 'R') then
1945
1946 begin
1947
1948 select nvl(switch_code, 'XX')
1949 into x_switch_code
1950 from fa_books_summary_mrc_v
1951 where book_type_code = p_book_type_code
1952 and asset_id = p_asset_id
1953 and period_counter = p_period_counter;
1954 exception
1955 when no_data_found then
1956 null;
1957 end;
1958 else
1959
1960 begin
1961 select nvl(switch_code, 'XX')
1962 into x_switch_code
1963 from fa_books_summary
1964 where book_type_code = p_book_type_code
1965 and asset_id = p_asset_id
1966 and period_counter = p_period_counter;
1967 exception
1968 when no_data_found then
1969 null;
1970 end;
1971 end if;
1972 end if;
1973
1974 -- Check previous switch code rules
1975 if (x_switch_code = 'XX') then
1976 if (p_mrc_sob_type_code = 'R') then
1977
1978 begin
1979 select nvl(switch_code, 'XX')
1980 into l_prev_switch_code
1981 from fa_books_summary_mrc_v
1982 where book_type_code = p_book_type_code
1983 and asset_id = p_asset_id
1984 and period_counter = p_period_counter - 1;
1985 exception
1986 when no_data_found then
1987 l_prev_switch_code := 'XX';
1988 end;
1989 else
1990
1991 begin
1992 select nvl(switch_code, 'XX')
1993 into l_prev_switch_code
1994 from fa_books_summary
1995 where book_type_code = p_book_type_code
1996 and asset_id = p_asset_id
1997 and period_counter = p_period_counter - 1;
1998 exception
1999 when no_data_found then
2000 l_prev_switch_code := 'XX';
2001 end;
2002 end if;
2003
2004 -- You never change the switch with a fiscal year, so after the first
2005 -- period.
2006 if (p_period_num > 1) then
2007 x_switch_code := l_prev_switch_code;
2008
2009 -- You can never move backwards in the switch chain, so once the switch
2010 -- is at FR, it stays there.
2011 elsif (p_period_num = 1) then
2012 if (l_prev_switch_code = 'FR') then
2013 x_switch_code := 'FR';
2014 end if;
2015 end if;
2016 end if;
2017
2018 -- Do the switch test if necessary. The switch test is always based on
2019 -- old data, and specifically, what happened after the last period of the
2020 -- previous fiscal year close. Even if a transaction occurred in the
2021 -- current period, even the first period, it does not affect the switch
2022 -- code. Transactions such as this would only affect the basis. It would
2023 -- not affect the switch until the nexte year. Even backdated transactions
2027 --
2024 -- have no effect since you cannot change the switch code within the
2025 -- fiscal year. Once the year end for a fiscal year has closed, the
2026 -- switch code for the next year is set.
2028 -- Here is how the switch test is derived.
2029 --
2030 -- The switch formula is as follows:
2031 -- (basis for D2) * (rate for D2) / pers_per_year <=
2032 -- (basis for FR) * (rate for FR) / pers_per_year.
2033 --
2034 -- This derives into: (when cancelling common variables)
2035 --
2036 -- (adjusted_cost for D2) * (adj_rate * deprn_factor) <=
2037 -- (adjusted_cost) * (adj_rate)
2038 --
2039 -- Further deriving into:
2040 --
2041 -- (adjusted_cost - prev_yr_rsv + first_yr_rsv) * deprn_factor
2042 -- <= (adjusted_cost)
2043 --
2044 -- This simiplied switch rule is common to Polish mechanisms
2045 -- 1 and 3. For mechanism 5, we just want the nbv and don't
2046 -- want to add back the first year reserve for the formula is:
2047 --
2048 -- (adjusted_cost - prev_yr_rsv) * deprn_factor <= (adjusted_cost)
2049 --
2050 if (x_switch_code = 'XX') then
2051 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
2052 FA_STD_TYPES.FAD_DBR_POLISH_3)) then
2053
2054 -- Switch between D2 and FR
2055
2056 l_new_basis := p_prev_year_adjusted_cost - p_prev_year_reserve +
2057 p_first_year_reserve;
2058
2059 l_switch_value := l_new_basis * p_deprn_factor;
2060
2061 -- Now do switch test
2062 if (l_switch_value > p_prev_year_adjusted_cost) then
2063
2064 -- Stay with current D2
2065 x_switch_code := 'D2';
2066 else
2067 -- Switch to FR
2068 x_switch_code := 'FR';
2069 end if;
2070
2071 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
2072
2073 -- Switch between SD and FR
2074
2075 l_new_basis := p_prev_year_adj_rec_cost - p_prev_year_reserve;
2076
2077 l_switch_value := l_new_basis * p_deprn_factor;
2078
2079 -- Now do switch test
2080 if (l_switch_value > p_prev_year_adj_rec_cost) then
2081
2082 -- Stay with current SD
2083 x_switch_code := 'SD';
2084
2085 else
2086 -- Switch to FR
2087 x_switch_code := 'FR';
2088
2089 end if;
2090 end if;
2091 end if;
2092
2093 -- Set the rate based on the switch code derived.
2094 if (x_switch_code = '30') then
2095
2096 if (p_period_counter = p_first_period_counter) then
2097
2098 -- We're multiplying the rate by the periods in the year because
2099 -- for the first year, we want to take the entire year's worth of
2100 -- depreciation in a single period when the switch is 30. For the
2101 -- rest of the year, we'll turn the depreciate flag off.
2102 x_adjusted_rate := FA_POLISH_30_RULE * p_Periods_Per_Year;
2103
2104 -- Note that an adjustment does not affect the rate or basis
2105 -- here since an adjustment in the period of addition creates
2106 -- an ADDITION/ADDITION VOID row rather than an ADJUSTMENT row
2107 -- Similarly, you can't retirement an asset in the period of add
2108 -- ??? How about an asset with a FOL MONTH convention and an
2109 -- ??? ADJUSTMENT that occurs in the period after addition?
2110 -- After discussion w/ Som, we decided to use our judgment and
2111 -- take the new cost after the adjustment as the new basis
2112 -- since depreciation had not been taken yet. For example, if
2113 -- user added an asset w/ cost 10K in March w/ FOL MON convention,
2114 -- and in April, adjusted the cost to 20K, we would use 20K as the
2115 -- the basis in April when we apply the 30% rule.
2116
2117 else
2118 -- When switch is 30, don't depreciate the asset in the first year
2119 -- after the first period
2120 x_adjusted_rate := 0;
2121 x_depreciate_flag := 'NO';
2122
2123 -- Note that transactions such as adjustments do not affect
2124 -- the rate here.
2125 end if;
2126 elsif (x_switch_code = 'DM') then
2127 -- When switch is DM, rate is adj_rate * alt_deprn_factor
2128 x_adjusted_rate := p_adjusted_rate * p_Alternate_Deprn_Factor;
2129
2130 elsif (x_switch_code in ('D2','SD')) then
2131 -- When switch is D2 or SD, rate is adj_rate * deprn_factor
2132 x_adjusted_rate := p_adjusted_rate * p_Deprn_Factor;
2133 elsif (x_switch_code = 'FR') then
2134 -- When switch is FR, rate is adj_rate
2135 x_adjusted_rate := p_adjusted_rate;
2136 else
2137 -- Unknown switch_code.
2138 fa_srvr_msg.add_message(
2139 calling_fn => 'fa_polish_pvt.calc_rate',
2140 name => '***FA_POLISH_INVALID_SWITCH_CODE***',
2141 p_log_level_rec => p_log_level_rec);
2142
2143 raise calc_rate_err;
2144 end if;
2145
2146 return (TRUE);
2147
2148 EXCEPTION
2149 WHEN calc_rate_err THEN
2150
2151 if (sqlcode <> 0) then
2152 fa_rx_conc_mesg_pkg.log(sqlerrm);
2153 end if;
2154
2155 fa_srvr_msg.add_sql_error (
2156 calling_fn => 'fa_polish_pvt.Calc_Rate',
2157 p_log_level_rec => p_log_level_rec);
2158 return (FALSE);
2159 WHEN OTHERS THEN
2160
2161 if (sqlcode <> 0) then
2162 fa_rx_conc_mesg_pkg.log(sqlerrm);
2163 end if;
2164
2165 fa_srvr_msg.add_sql_error (
2166 calling_fn => 'fa_polish_pvt.Calc_Rate',
2167 p_log_level_rec => p_log_level_rec);
2168 return (FALSE);
2169 END Calc_Rate;
2170
2171 END FA_POLISH_PVT;
[Home] [Help]