1 PACKAGE BODY FA_POLISH_PVT AS
2 /* $Header: FAVPOLB.pls 120.15 2009/04/15 15:06:30 bridgway 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
211
212 -- PRIVATE ROUTINE - forward declaration
213
214 FUNCTION Calc_Basis_Neg_Adj_Flag_Yes (
215 p_book_type_code IN VARCHAR2,
216 p_old_cost IN NUMBER,
217 p_old_reserve IN NUMBER,
218 p_adjustment_amount IN NUMBER,
219 p_set_of_books_id IN NUMBER,
220 x_new_deprn_basis OUT NOCOPY NUMBER,
221 p_log_level_rec IN
222 FA_API_TYPES.log_level_rec_type default null)
223 RETURN BOOLEAN;
224
225 FUNCTION Calc_Basis_Neg_Adj_Flag_No (
226 p_book_type_code IN VARCHAR2,
227 p_old_cost IN NUMBER,
228 p_old_deprn_basis IN NUMBER,
229 p_adjustment_amount IN NUMBER,
230 p_set_of_books_id IN NUMBER,
231 x_new_deprn_basis OUT NOCOPY NUMBER,
232 p_log_level_rec IN
233 FA_API_TYPES.log_level_rec_type default null)
234 RETURN BOOLEAN;
235
236 FUNCTION Calc_Basis_Partial_Ret (
237 p_book_type_code IN VARCHAR2,
238 p_old_cost IN NUMBER,
239 p_old_deprn_basis IN NUMBER,
240 p_retirement_amount IN NUMBER,
241 p_set_of_books_id IN NUMBER,
242 x_new_deprn_basis OUT NOCOPY NUMBER,
243 p_log_level_rec IN
244 FA_API_TYPES.log_level_rec_type default null)
245 RETURN BOOLEAN;
246
247 FUNCTION Calc_Basis_Trxn (
248 p_book_type_code IN VARCHAR2,
249 p_transaction_type_code IN VARCHAR2,
250 p_pos_neg_adjustment IN VARCHAR2,
251 p_polish_adj_calc_basis_flag
252 IN VARCHAR2,
253 p_adjusted_cost IN NUMBER,
254 p_adjusted_recoverable_cost
255 IN NUMBER,
256 p_prev_adj_rec_cost IN NUMBER,
257 p_prev_basis IN NUMBER,
258 p_prev_reserve IN NUMBER,
259 p_adjustment_amount IN NUMBER,
260 p_retirement_amount IN NUMBER,
261 p_switch_code IN VARCHAR2,
262 p_set_of_books_id IN NUMBER,
263 x_adjusted_cost OUT NOCOPY NUMBER,
264 x_adjusted_recoverable_cost OUT NOCOPY NUMBER,
265 p_log_level_rec IN
266 FA_API_TYPES.log_level_rec_type default null)
267 RETURN BOOLEAN;
268
269 FUNCTION Calc_Trxn_Values (
270 p_book_type_code IN VARCHAR2,
271 p_asset_id IN NUMBER,
272 p_transaction_type_code IN VARCHAR2,
273 p_transaction_header_id IN NUMBER,
274 p_first_period_counter IN NUMBER,
275 p_mrc_sob_type_code IN VARCHAR2,
276 p_set_of_books_id IN NUMBER,
277 p_calling_mode IN VARCHAR2,
278 x_adjustment_amount OUT NOCOPY NUMBER,
279 x_retirement_amount OUT NOCOPY NUMBER,
280 x_bef_trxn_period_counter OUT NOCOPY NUMBER,
281 x_pos_neg_adjustment OUT NOCOPY VARCHAR2,
282 x_prev_basis OUT NOCOPY NUMBER,
283 x_prev_adj_rec_cost OUT NOCOPY NUMBER,
284 x_prev_reserve OUT NOCOPY NUMBER,
285 p_log_level_rec IN
286 FA_API_TYPES.log_level_rec_type default null)
287 RETURN BOOLEAN;
288
289 FUNCTION Calc_Rate (
290 p_book_type_code IN VARCHAR2,
291 p_asset_id IN NUMBER,
292 p_polish_rule IN NUMBER,
293 p_year_of_life IN NUMBER,
294 p_year_retired IN VARCHAR2,
295 p_period_counter IN NUMBER,
296 p_first_period_counter IN NUMBER,
297 p_open_period_counter IN NUMBER,
298 p_period_num IN NUMBER,
299 p_periods_per_year IN NUMBER,
300 p_adjusted_rate IN NUMBER,
301 p_deprn_factor IN NUMBER,
302 p_alternate_deprn_factor IN NUMBER,
303 p_depreciate_flag IN VARCHAR2,
304 p_first_year_reserve IN NUMBER,
305 p_prev_year_reserve IN NUMBER,
306 p_prev_year_adjusted_cost
307 IN NUMBER,
308 p_prev_year_adj_rec_cost IN NUMBER,
309 p_mrc_sob_type_code IN VARCHAR2,
310 p_set_of_books_id IN NUMBER,
311 x_adjusted_rate OUT NOCOPY NUMBER,
312 x_depreciate_flag OUT NOCOPY VARCHAR2,
313 x_switch_code OUT NOCOPY VARCHAR2,
314 p_log_level_rec IN
315 FA_API_TYPES.log_level_rec_type default null)
316 RETURN BOOLEAN;
317
318
319
320 -- PUBLIC ROUTINE
321
322 PROCEDURE Calc_Polish_Rate_Cost (
323 p_Book_Type_Code IN VARCHAR2,
324 p_Asset_Id IN NUMBER,
325 p_Polish_Rule IN NUMBER,
326 p_Deprn_Factor IN NUMBER,
327 p_Alternate_Deprn_Factor IN NUMBER,
328 p_Polish_Adj_Calc_Basis_Flag
329 IN VARCHAR2,
330 p_Rate IN NUMBER,
331 p_Depreciate_Flag IN VARCHAR2,
332 p_Adjusted_Cost IN NUMBER,
333 p_Recoverable_Cost IN NUMBER,
334 p_Adjusted_Recoverable_Cost
335 IN NUMBER,
336 p_Fiscal_Year IN NUMBER,
337 p_Period_Num IN NUMBER,
338 p_Periods_Per_Year IN NUMBER,
339 p_Year_Retired IN VARCHAR2,
340 p_MRC_Sob_Type_Code IN VARCHAR2,
341 p_set_of_books_id IN NUMBER,
342 x_Rate OUT NOCOPY NUMBER,
343 x_Depreciate_Flag OUT NOCOPY VARCHAR2,
344 x_Adjusted_Cost OUT NOCOPY NUMBER,
345 x_Adjusted_Recoverable_Cost
346 OUT NOCOPY NUMBER,
347 x_Success OUT NOCOPY INTEGER,
348 p_Calling_Fn IN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
349 IS
350
351 l_period_counter number(15);
352 l_first_period_counter number(15);
353 l_first_fiscal_year number(4);
354 l_year_of_life number(15);
355 l_old_adjusted_cost number;
356 l_adjusted_cost number;
357 l_adjusted_recoverable_cost number;
358 l_adjusted_rate number;
359 l_depreciate_flag varchar2(3);
360 l_switch_code varchar2(2);
361
362 l_adjusted_cost_old number;
363 l_adj_rec_cost_old number;
364
365 l_transaction_type_code varchar2(20);
366 l_transaction_header_id number(15);
367
368 l_adjustment_amount number;
369 l_retirement_amount number;
370 l_bef_trxn_period_counter number(15);
371 l_pos_neg_adjustment varchar2(3);
372 l_prev_adj_rec_cost number;
373 l_prev_basis number;
374 l_prev_reserve number;
375
376 l_first_year_period_counter number(15);
377 l_prev_year_period_counter number(15);
378 l_first_year_reserve number;
379 l_prev_year_reserve number;
380 l_prev_year_adjusted_cost number;
381 l_prev_year_adj_rec_cost number;
382 l_min_period_counter number;
383
384 l_previous_year_adjustment boolean;
385 l_previous_year_retirement boolean;
386
387 l_open_period_counter number(15);
388 l_bs_row number(15);
389
390 polish_err exception;
391
392 BEGIN
393
394 -- === Initializations ================================================== --
395
396 l_transaction_type_code := 'NONE';
397 l_previous_year_adjustment := FALSE;
398 l_previous_year_retirement := FALSE;
399
400 l_first_year_reserve := 0;
401 l_prev_year_reserve := 0;
402
403 -- Fix for Bug #3649102. Need to initialize this in case we're in 1st yr
404 l_prev_year_adj_rec_cost := p_Adjusted_Recoverable_Cost;
405
406 if (p_Calling_Fn = 'fadpdp.faxgpolr') then
407 -- We are calling this from the deprn engine. Set the mode.
408 FA_POLISH_PVT.calling_mode := 'DEPRN';
409 elsif (p_Calling_Fn = 'faproj.faxgpolr') then
410 FA_POLISH_PVT.calling_mode := 'PROJECT';
411 elsif (p_Calling_Fn = 'fa_cde_pkg.whatif.faxgpolr') then
412 FA_POLISH_PVT.calling_mode := 'WHATIF';
413 end if;
414
415 -- Derive current period_counter. Note that this may be different from
416 -- the current open period if we are looping through backdated periods.
417 l_period_counter := (p_Fiscal_Year * p_Periods_Per_Year) + p_Period_Num;
418
419 -- Determine the year_of_life. For a Polish asset, the first year is
420 -- always the year of the dpis regardless of the prorate convention.
421 begin
422
423 if (p_MRC_SOB_Type_Code = 'R') then
424
425 select fy.fiscal_year
426 into l_first_fiscal_year
427 from fa_fiscal_year fy,
428 fa_mc_book_controls mbc,
429 fa_book_controls bc,
430 fa_mc_books bks
431 where bc.book_type_code = p_Book_Type_Code
432 and mbc.book_type_code = p_Book_Type_Code
433 and bc.fiscal_year_name = fy.fiscal_year_name
434 and mbc.set_of_books_id = p_set_of_books_id
435 and bks.book_type_code = p_Book_Type_Code
436 and bks.asset_id = p_Asset_Id
437 and bks.transaction_header_id_out is null
438 and bks.set_of_books_id = p_set_of_books_id
439 and bks.date_placed_in_service between
440 fy.start_date and fy.end_date;
441
442 select dp.period_counter
443 into l_first_period_counter
444 from fa_mc_deprn_periods dp,
445 fa_mc_books bks
446 where dp.book_type_code = p_Book_Type_Code
447 and dp.set_of_books_id = p_set_of_books_id
448 and bks.book_type_code = p_Book_Type_Code
449 and bks.asset_id = p_Asset_Id
450 and bks.transaction_header_id_out is null
451 and bks.set_of_books_id = p_set_of_books_id
452 and bks.date_placed_in_service between
453 dp.calendar_period_open_date and dp.calendar_period_close_date;
454
455 select period_counter
456 into l_open_period_counter
457 from fa_mc_deprn_periods
458 where book_type_code = p_Book_Type_Code
459 and period_close_date is null
460 and set_of_books_id = p_set_of_books_id;
461
462 else
463
464 select fy.fiscal_year
465 into l_first_fiscal_year
466 from fa_fiscal_year fy,
467 fa_book_controls bc,
468 fa_books bks
469 where bc.book_type_code = p_Book_Type_Code
470 and bc.fiscal_year_name = fy.fiscal_year_name
471 and bks.book_type_code = p_Book_Type_Code
472 and bks.asset_id = p_Asset_Id
473 and bks.transaction_header_id_out is null
474 and bks.date_placed_in_service between
475 fy.start_date and fy.end_date;
476
477 select dp.period_counter
478 into l_first_period_counter
479 from fa_deprn_periods dp,
480 fa_books bks
481 where dp.book_type_code = p_Book_Type_Code
482 and bks.book_type_code = p_Book_Type_Code
483 and bks.asset_id = p_Asset_Id
484 and bks.transaction_header_id_out is null
485 and bks.date_placed_in_service between
486 dp.calendar_period_open_date and dp.calendar_period_close_date;
487
488 select period_counter
489 into l_open_period_counter
490 from fa_deprn_periods
491 where book_type_code = p_Book_Type_Code
492 and period_close_date is null;
493
494 end if;
495 exception
496 when others then
497 -- We're going to assume that if there are errors, the asset is
498 -- too old for the calendar, so we'll just set the first year to 0
499 l_first_fiscal_year := 0;
500 l_first_period_counter := 0;
501 end;
502
503 l_year_of_life := p_Fiscal_Year - l_first_fiscal_year + 1;
504
505 -- For an adjustment, the p_Adjusted_Cost passed in is not necessarily the
506 -- new cost. We will need to derive this amount.
507 if (FA_POLISH_PVT.calling_mode = 'ADJUSTMENT') then
508
509 if (p_MRC_SOB_Type_Code = 'R') then
510
511 select adjusted_cost
512 into l_old_adjusted_cost
513 from fa_mc_books
514 where book_type_code = p_Book_Type_Code
515 and asset_id = p_Asset_Id
516 and transaction_header_id_out is null
517 and set_of_books_id = p_set_of_books_id;
518
519 else
520
521 select adjusted_cost
522 into l_old_adjusted_cost
523 from fa_books
524 where book_type_code = p_Book_Type_Code
525 and asset_id = p_Asset_Id
526 and transaction_header_id_out is null;
527 end if;
528
529 l_adjusted_cost := FA_POLISH_PVT.adjustment_amount + l_old_adjusted_cost;
530 else
531 l_adjusted_cost := p_Adjusted_Cost;
532 end if;
533
534 -- Save parameters into local variables if we may be modifying them
535 l_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
536 l_adjusted_rate := p_Rate;
537 l_depreciate_flag := p_Depreciate_Flag;
538
539 -- ============================================== End Initializations === --
540 -- --
541 -- === Initial Checks =================================================== --
542
543 -- If before first period of life, then just return
544 if (l_period_counter < l_first_period_counter) then
545 x_Rate := 0;
546 x_Depreciate_Flag := l_depreciate_flag;
547 x_Adjusted_Cost := l_adjusted_cost;
548 x_Adjusted_Recoverable_Cost := l_adjusted_recoverable_cost;
549 X_Success := 1;
550 return;
551 end if;
552
553 -- If we are not calling this from a recognized program, then error.
554 if (FA_POLISH_PVT.calling_mode not in ('DEPRN', 'ADJUSTMENT',
555 'PROJECT', 'WHATIF')) then
556
557 fa_srvr_msg.add_message(
558 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
559 name => '***FA_POLISH_INVALID_MODE***',
560 p_log_level_rec => p_log_level_rec);
561 raise polish_err;
562 end if;
563
564 -- =============================================== End Initial Checks === --
565 -- --
566 -- === Check Transactions =============================================== --
567
568 if (FA_POLISH_PVT.calling_mode = 'ADJUSTMENT') then
569 -- If we are currently doing an adjustment, no rows for the adjustment
570 -- will be been added yet, so there will not exist rows for the
571 -- adjustment in fa_transaction_headers or fa_adjustments.
572 l_transaction_type_code := 'ADJUSTMENT';
573
574 else
575
576 -- Check to see if any transactions have been performed on this asset,
577 -- specifically, adjustments and partial retirements, which would affect
578 -- the rate and deprn basis. We want to get the most recent transaction.
579
580 -- Fix for Bug #3629991. Added transaction_header_id to select b/c
581 -- we may have multiple transactions to select from fa_adjustments
582 -- later.
583 begin
584 select transaction_type_code,
585 transaction_header_id
586 into l_transaction_type_code,
587 l_transaction_header_id
588 from fa_transaction_headers
589 where book_type_code = p_Book_Type_Code
590 and asset_id = p_Asset_ID
591 and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
592 and transaction_header_id =
593 (
594 select max(transaction_header_id)
595 from fa_transaction_headers
596 where book_type_code = p_Book_Type_Code
597 and asset_id = p_Asset_ID
598 and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
599 );
600
601 exception
602 when no_data_found then
603 -- No adjustments or partial retirements found
604 l_transaction_type_code := 'NONE';
605 end;
606 end if;
607
608 -- =========================================== End Check Transactions === --
609 -- --
610 -- === Calculate Transaction Values ===================================== --
611 -- If there are transactions that were done, let's calculate some
612 -- intermediate values since nearly every scenario will require them.
613
614 if (l_transaction_type_code <> 'NONE') then
615
616 if (not FA_POLISH_PVT.Calc_Trxn_Values (
617 p_book_type_code => p_book_type_code,
618 p_asset_id => p_asset_id,
619 p_transaction_type_code => l_transaction_type_code,
620 p_transaction_header_id => l_transaction_header_id,
621 p_first_period_counter => l_first_period_counter,
622 p_mrc_sob_type_code => p_mrc_sob_type_code,
623 p_set_of_books_id => p_set_of_books_id,
624 p_calling_mode => fa_polish_pvt.calling_mode,
625 x_adjustment_amount => l_adjustment_amount,
626 x_retirement_amount => l_retirement_amount,
627 x_bef_trxn_period_counter => l_bef_trxn_period_counter,
628 x_pos_neg_adjustment => l_pos_neg_adjustment,
629 x_prev_basis => l_prev_basis,
630 x_prev_adj_rec_cost => l_prev_adj_rec_cost,
631 x_prev_reserve => l_prev_reserve
632 , p_log_level_rec => p_log_level_rec)) then
633 raise polish_err;
634 end if;
635 end if;
636
637 -- ================================= End Calculate Transaction Values === --
638 -- --
639 -- === Calculate Transaction Switch Values ============================== --
640
641 -- After the first year, we need to calculate some new values because a
642 -- transaction in the middle of a year can cause a switch test to be
643 -- re-done, especially if it is a backdated adjustment. Therefore the
644 -- new deprn basis may be derived differently based on these values.
645
646 if ((l_year_of_life > 1) and (p_Year_Retired = 'N')) then
647
648 -- The previous year last period counter is period_ctr - per
649 l_prev_year_period_counter := l_period_counter - p_Period_Num;
650
651 -- The first year last period counter is (begin_fy + 1) * 12)
652 l_first_year_period_counter := (l_first_fiscal_year + 1) * 12;
653
654 if (p_MRC_SOB_Type_Code = 'R') then
655 Begin /* Bug 6450906 Added Exception handling*/
656 select deprn_reserve
657 into l_first_year_reserve
658 from fa_mc_deprn_summary
659 where book_type_code = p_Book_Type_Code
660 and asset_id = p_Asset_Id
661 and period_counter = l_first_year_period_counter
662 and set_of_books_id = p_set_of_books_id;
663 Exception
664 when no_data_found then
665 l_first_year_reserve := 0;
666 End;
667
668 Begin /* Bug 6450906 Added Exception handling*/
669 select deprn_reserve
670 into l_prev_year_reserve
671 from fa_mc_deprn_summary
672 where book_type_code = p_Book_Type_Code
673 and asset_id = p_Asset_Id
674 and period_counter = l_prev_year_period_counter
675 and set_of_books_id = p_set_of_books_id;
676 Exception
677 when no_data_found then
678 l_prev_year_reserve := 0;
679 End;
680 -- Fix for Bug #5550557. Need to catch if books summary row
681 -- does not exist.
682 begin
683
684 select polish_deprn_basis,
685 polish_adj_rec_cost
686 into l_prev_year_adjusted_cost,
687 l_prev_year_adj_rec_cost
688 from fa_mc_books_summary
689 where book_type_code = p_Book_Type_Code
690 and asset_id = p_Asset_Id
691 and period_counter = l_prev_year_period_counter
692 and set_of_books_id = p_set_of_books_id;
693
694 -- Fix for Bug #5907258. If these values are null, can cause
695 -- data corruption. Needs to be fixed first.
696 if (l_prev_year_adjusted_cost is null) or
697 (l_prev_year_adj_rec_cost is null) then
698
699 fa_srvr_msg.add_message(
700 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
701 name => '***FA_POLISH_NULL_COST***',
702 p_log_level_rec => p_log_level_rec);
703 raise polish_err;
704 end if;
705 exception
706
707 when no_data_found then
708
709 select min(period_counter)
710 into l_min_period_counter
711 from fa_mc_deprn_periods
712 where book_type_code = p_Book_Type_Code
713 and set_of_books_id = p_set_of_books_id;
714
715 if (l_prev_year_period_counter >= l_min_period_counter) then
716
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_mc_books bks
724 where bks.book_type_code = p_Book_Type_Code
725 and bks.asset_id = p_Asset_Id
726 and bks.set_of_books_id = p_set_of_books_id
727 and bks.rowid =
728 (
729 select max(bks1.rowid)
730 from fa_mc_books bks1,
731 fa_mc_deprn_periods dp
732 where bks1.book_type_code = p_Book_Type_Code
733 and bks1.asset_id = p_Asset_Id
734 and bks1.book_type_code = dp.book_type_code
735 and bks1.set_of_books_id = p_set_of_books_id
736 and dp.period_counter = l_prev_year_period_counter
737 and dp.set_of_books_id = p_set_of_books_id
738 and bks1.date_effective <=
739 nvl(dp.period_close_date, sysdate)
740 );
741 exception
742 when others then
743 l_prev_year_adjusted_cost := l_Adjusted_Cost;
744 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
745 end;
746 else
747 begin
748
749 select bks.adjusted_cost,
750 bks.adjusted_recoverable_cost
751 into l_prev_year_adjusted_cost,
752 l_prev_year_adj_rec_cost
753 from fa_mc_books bks
754 where bks.book_type_code = p_Book_Type_Code
755 and bks.asset_id = p_Asset_Id
756 and bks.set_of_books_id = p_set_of_books_id
757 and bks.rowid =
758 (
759 select min(bks1.rowid)
760 from fa_mc_books bks1
761 where bks1.book_type_code = p_Book_Type_Code
762 and bks1.asset_id = p_Asset_Id
763 and bks1.set_of_books_id = p_set_of_books_id
764 );
765 exception
766 when others then
767 l_prev_year_adjusted_cost := l_Adjusted_Cost;
768 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
769 end;
770 end if;
771 end;
772
773 else
774 Begin /* Bug 6450906 Added Exception handling*/
775 select deprn_reserve
776 into l_first_year_reserve
777 from fa_deprn_summary
778 where book_type_code = p_Book_Type_Code
779 and asset_id = p_Asset_Id
780 and period_counter = l_first_year_period_counter;
781 Exception
782 when no_data_found then
783 l_first_year_reserve := 0;
784 End;
785
786 Begin /* Bug 6450906 Added Exception handling*/
787 select deprn_reserve
788 into l_prev_year_reserve
789 from fa_deprn_summary
790 where book_type_code = p_Book_Type_Code
791 and asset_id = p_Asset_Id
792 and period_counter = l_prev_year_period_counter;
793 Exception
794 when no_data_found then
795 l_prev_year_reserve := 0;
796 End;
797
798 -- Fix for Bug #5550557. Need to catch if books summary row
799 -- does not exist.
800 begin
801
802 select polish_deprn_basis,
803 polish_adj_rec_cost
804 into l_prev_year_adjusted_cost,
805 l_prev_year_adj_rec_cost
806 from fa_books_summary
807 where book_type_code = p_Book_Type_Code
808 and asset_id = p_Asset_Id
809 and period_counter = l_prev_year_period_counter;
810
811 -- Fix for Bug #5907258. If these values are null, can cause
812 -- data corruption. Needs to be fixed first.
813 if (l_prev_year_adjusted_cost is null) or
814 (l_prev_year_adj_rec_cost is null) then
815
816 fa_srvr_msg.add_message(
817 calling_fn => 'fa_polish_pvt.calc_polish_rate_cost',
818 name => '***FA_POLISH_NULL_COST***',
819 p_log_level_rec => p_log_level_rec);
820 raise polish_err;
821 end if;
822
823 exception
824
825 when no_data_found then
826
827 select min(period_counter)
828 into l_min_period_counter
829 from fa_deprn_periods
830 where book_type_code = p_Book_Type_Code;
831
832 if (l_prev_year_period_counter >= l_min_period_counter) then
833
834 begin
835
836 select bks.adjusted_cost,
837 bks.adjusted_recoverable_cost
838 into l_prev_year_adjusted_cost,
839 l_prev_year_adj_rec_cost
840 from fa_books bks
841 where bks.book_type_code = p_Book_Type_Code
842 and bks.asset_id = p_Asset_Id
843 and bks.rowid =
844 (
845 select max(bks1.rowid)
846 from fa_books bks1,
847 fa_deprn_periods dp
848 where bks1.book_type_code = p_Book_Type_Code
849 and bks1.asset_id = p_Asset_Id
850 and bks1.book_type_code = dp.book_type_code
851 and dp.period_counter = l_prev_year_period_counter
852 and bks1.date_effective <=
853 nvl(dp.period_close_date, sysdate)
854 );
855 exception
856 when others then
857 l_prev_year_adjusted_cost := l_Adjusted_Cost;
858 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
859 end;
860 else
861 begin
862
863 select bks.adjusted_cost,
864 bks.adjusted_recoverable_cost
865 into l_prev_year_adjusted_cost,
866 l_prev_year_adj_rec_cost
867 from fa_books bks
868 where bks.book_type_code = p_Book_Type_Code
869 and bks.asset_id = p_Asset_Id
870 and bks.rowid =
871 (
872 select min(bks1.rowid)
873 from fa_books bks1
874 where bks1.book_type_code = p_Book_Type_Code
875 and bks1.asset_id = p_Asset_Id
876 );
877 exception
878 when others then
879 l_prev_year_adjusted_cost := l_Adjusted_Cost;
880 l_prev_year_adj_rec_cost := l_Adjusted_Recoverable_Cost;
881 end;
882 end if;
883 end;
884 end if;
885
886 if (l_transaction_type_code = 'ADJUSTMENT') then
887
888 -- Check to see if the ADJUSTMENT occurred in a past fiscal year
889 -- from this current period. If it did, we will need to do the
890 -- switch test again even though we may be in the middle of the
891 -- year. If the transaction occurred in the same fiscal year, we
892 -- may change the basis, but we would still stay with the same
893 -- switch rate since we're not doing the re-test again.
894 -- If the current period counter - period adjustment occurred >=
895 -- period num then the adjustment occurred in a previous year.
896 if (l_period_counter - (l_bef_trxn_period_counter + 1) >=
897 p_Period_Num) then
898
899 -- Adjustment occurred in previous year
900 l_previous_year_adjustment := TRUE;
901
902 -- Fix for Bug #5710413. Need to use correct adjusted cost
903 -- for the previous year.
904 if (p_MRC_SOB_Type_Code = 'R') then
905
906 select adjusted_cost
907 into l_prev_year_adjusted_cost
908 from fa_mc_books
909 where asset_id = p_asset_id
910 and book_type_code = p_book_type_code
911 and transaction_header_id_out is null
912 and set_of_books_id = p_set_of_books_id;
913
914 else
915 select adjusted_cost
916 into l_prev_year_adjusted_cost
917 from fa_books
918 where asset_id = p_asset_id
919 and book_type_code = p_book_type_code
920 and transaction_header_id_out is null;
921 end if;
922
923 else
924 -- Adjustment occurred in current year
925 -- ??? Do we need logic for if adjustment in a future year?
926 l_previous_year_adjustment := FALSE;
927 end if;
928
929 elsif (l_transaction_type_code = 'PARTIAL RETIREMENT') then
930 -- Same logic as above. Partial retirements cannot cross fiscal
931 -- years. However, in the year after the retirement, we still
932 -- need to subtract the previous reserve when doing the switch test.
933 if (l_period_counter - (l_bef_trxn_period_counter + 1) >=
934 p_Period_Num) then
935
936 -- Retirement occurred in previous year
937 l_previous_year_retirement := TRUE;
938 else
939
940 -- Retirement occurred in current year
941 -- ??? Do we need logic for if retirement in a future year?
942 l_previous_year_retirement := FALSE;
943 end if;
944 end if;
945 end if;
946
947 -- ========================== End Calculate Transaction Switch Values === --
948 -- --
949 -- === Derive Rate ====================================================== --
950
951 -- Calculate the rate and switch code for this period.
952
953 if (not FA_POLISH_PVT.Calc_Rate (
954 p_book_type_code => p_book_type_code,
955 p_asset_id => p_asset_id,
956 p_polish_rule => p_polish_rule,
957 p_year_of_life => l_year_of_life,
958 p_year_retired => p_year_retired,
959 p_period_counter => l_period_counter,
960 p_first_period_counter => l_first_period_counter,
961 p_open_period_counter => l_open_period_counter,
962 p_period_num => p_period_num,
963 p_periods_per_year => p_periods_per_year,
964 p_adjusted_rate => p_rate,
965 p_deprn_factor => p_deprn_factor,
966 p_alternate_deprn_factor => p_alternate_deprn_factor,
967 p_depreciate_flag => p_depreciate_flag,
968 p_first_year_reserve => l_first_year_reserve,
969 p_prev_year_reserve => l_prev_year_reserve,
970 p_prev_year_adjusted_cost
971 => l_prev_year_adjusted_cost,
972 p_prev_year_adj_rec_cost => l_prev_year_adj_rec_cost,
973 p_mrc_sob_type_code => p_mrc_sob_type_code,
974 p_set_of_books_id => p_set_of_books_id,
975 x_adjusted_rate => l_adjusted_rate,
976 x_depreciate_flag => l_depreciate_flag,
977 x_switch_code => l_switch_code
978 , p_log_level_rec => p_log_level_rec)) then
979 raise polish_err;
980 end if;
981
982 -- ================================ End Derive Rate ===================== --
983 -- --
984 -- === Calculate New Basis ============================================== --
985
986 if ((l_transaction_type_code = 'NONE') or
987 ((l_transaction_type_code = 'ADJUSTMENT') and
988 (l_previous_year_adjustment)) or
989 ((l_transaction_type_code = 'ADJUSTMENT') and (l_pos_neg_adjustment is null)) or /* Brahma 6989831. This perticular statement is added for non cost adjustments like Dereciation checkbox unselected and selected again */
990 ((l_transaction_type_code = 'PARTIAL RETIREMENT') and
991 (l_previous_year_retirement))) then
992
993 -- Determine the basis depending on the switch
994 if (l_switch_code in ('30', 'DM')) then
995
996 -- Set the basis to be the fully reservable cost
997 l_adjusted_cost := nvl(l_adjusted_recoverable_cost, 0);
998
999 elsif (l_switch_code = 'D2') then
1000
1001 -- Set the basis to be cost - previous year rsv + 1st year rsv
1002 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
1003 l_adjusted_recoverable_cost) -
1004 nvl(l_prev_year_reserve,0) +
1005 nvl(l_first_year_reserve,0);
1006 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
1007 l_adjusted_recoverable_cost);
1008
1009 elsif (l_switch_code = 'SD') then
1010
1011 -- Set the basis to be the cost - previous year rsv
1012 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
1013 l_adjusted_recoverable_cost) -
1014 nvl(l_prev_year_reserve,0);
1015 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
1016 l_adjusted_recoverable_cost);
1017
1018 elsif (l_switch_code = 'FR') then
1019
1020 -- Set the basis to be the fully reservable cost
1021 l_adjusted_cost := nvl(l_prev_year_adj_rec_cost,
1022 l_adjusted_recoverable_cost);
1023 l_adjusted_recoverable_cost := nvl(l_prev_year_adj_rec_cost,
1024 l_adjusted_recoverable_cost);
1025
1026 end if;
1027
1028 else
1029 -- Need to copy into separate variables since can't have same
1030 -- as IN and OUT parameters when using NOCOPY
1031 l_adjusted_cost_old := l_adjusted_cost;
1032 l_adj_rec_cost_old := l_adjusted_recoverable_cost;
1033
1034 -- Calculate the new basis depending on the transaction
1035 if (l_transaction_type_code = 'ADJUSTMENT') then
1036
1037 if not (Calc_Basis_Trxn (
1038 p_book_type_code => p_Book_Type_Code,
1039 p_transaction_type_code => l_transaction_type_code,
1040 p_pos_neg_adjustment => l_pos_neg_adjustment,
1041 p_polish_adj_calc_basis_flag => p_polish_adj_calc_basis_flag,
1042 p_adjusted_cost => l_adjusted_cost_old,
1043 p_adjusted_recoverable_cost => l_adj_rec_cost_old,
1044 p_prev_adj_rec_cost => l_prev_adj_rec_cost,
1045 p_prev_reserve => l_prev_reserve,
1046 p_prev_basis => l_prev_basis,
1047 p_adjustment_amount => l_adjustment_amount,
1048 p_retirement_amount => l_retirement_amount,
1049 p_switch_code => l_switch_code,
1050 p_set_of_books_id => p_set_of_books_id,
1051 x_adjusted_cost => l_adjusted_cost,
1052 x_adjusted_recoverable_cost => l_adjusted_recoverable_cost,
1053 p_log_level_rec => p_log_level_rec
1054 )) then
1055 raise polish_err;
1056 end if;
1057 end if;
1058 end if;
1059
1060 -- ================================================== End Audit Trail === --
1061 -- --
1062 -- === Set OUT Parameters =============================================== --
1063
1064 x_Adjusted_Cost := l_adjusted_cost;
1065 x_Adjusted_Recoverable_Cost := l_adjusted_recoverable_cost;
1066 x_Depreciate_Flag := l_depreciate_flag;
1067 x_Rate := l_adjusted_rate;
1068
1069 -- =========================================== End Set OUT Parameters === --
1070 X_Success := 1;
1071
1072
1073 EXCEPTION
1074 WHEN polish_err THEN
1075
1076 if (sqlcode <> 0) then
1077 fa_rx_conc_mesg_pkg.log(sqlerrm);
1078 end if;
1079
1080 fa_srvr_msg.add_sql_error (
1081 calling_fn => 'fa_polish_pvt.Calc_Polish_Rate_Cost', p_log_level_rec => p_log_level_rec);
1082 x_Rate := 0;
1083 X_Success := 0;
1084
1085 WHEN OTHERS THEN
1086
1087 if (sqlcode <> 0) then
1088 fa_rx_conc_mesg_pkg.log(sqlerrm);
1089 end if;
1090
1091 fa_srvr_msg.add_sql_error (
1092 calling_fn => 'fa_polish_pvt.Calc_Polish_Rate_Cost', p_log_level_rec => p_log_level_rec);
1093 x_Rate := 0;
1094 X_Success := 0;
1095
1096 END Calc_Polish_Rate_Cost;
1097
1098 FUNCTION Calc_Basis_Neg_Adj_Flag_Yes (
1099 p_book_type_code IN VARCHAR2,
1100 p_old_cost IN NUMBER,
1101 p_old_reserve IN NUMBER,
1102 p_adjustment_amount IN NUMBER,
1103 p_set_of_books_id IN NUMBER,
1104 x_new_deprn_basis OUT NOCOPY NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1105 RETURN BOOLEAN IS
1106
1107 l_adjustment_reserve number;
1108 l_adjustment_nbv number;
1109
1110 l_dummy_bool boolean;
1111
1112 BEGIN
1113
1114 l_adjustment_reserve := p_old_reserve * p_adjustment_amount / p_old_cost;
1115
1116 l_adjustment_nbv := p_adjustment_amount - l_adjustment_reserve;
1117
1118 x_new_deprn_basis := p_old_cost - l_adjustment_nbv;
1119
1120 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_set_of_books_id, p_log_level_rec => p_log_level_rec);
1121
1122 return (TRUE);
1123
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126
1127 if (sqlcode <> 0) then
1128 fa_rx_conc_mesg_pkg.log(sqlerrm);
1129 end if;
1130
1131 fa_srvr_msg.add_sql_error (
1132 calling_fn => 'fa_polish_pvt.Calc_Basis_Neg_Adj_Flag_Yes', p_log_level_rec => p_log_level_rec);
1133 return (FALSE);
1134 END Calc_Basis_Neg_Adj_Flag_Yes;
1135
1136 FUNCTION Calc_Basis_Neg_Adj_Flag_No (
1137 p_book_type_code IN VARCHAR2,
1138 p_old_cost IN NUMBER,
1139 p_old_deprn_basis IN NUMBER,
1140 p_adjustment_amount IN NUMBER,
1141 p_set_of_books_id IN NUMBER,
1142 x_new_deprn_basis OUT NOCOPY NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1143 RETURN BOOLEAN IS
1144
1145 l_dummy_bool boolean;
1146
1147 BEGIN
1148
1149 x_new_deprn_basis := p_old_deprn_basis -
1150 (p_old_deprn_basis * p_adjustment_amount /
1151 p_old_cost);
1152
1153 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_set_of_books_id, p_log_level_rec => p_log_level_rec);
1154
1155 return (TRUE);
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159
1160 if (sqlcode <> 0) then
1161 fa_rx_conc_mesg_pkg.log(sqlerrm);
1162 end if;
1163
1164 fa_srvr_msg.add_sql_error (
1165 calling_fn => 'fa_polish_pvt.Calc_Basis_Neg_Adj_Flag_No', p_log_level_rec => p_log_level_rec);
1166 return (FALSE);
1167
1168 END Calc_Basis_Neg_Adj_Flag_No;
1169
1170 FUNCTION Calc_Basis_Partial_Ret (
1171 p_book_type_code IN VARCHAR2,
1172 p_old_cost IN NUMBER,
1173 p_old_deprn_basis IN NUMBER,
1174 p_retirement_amount IN NUMBER,
1175 p_set_of_books_id IN NUMBER,
1176 x_new_deprn_basis OUT NOCOPY NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1177 RETURN BOOLEAN IS
1178
1179 l_dummy_bool boolean;
1180
1181 BEGIN
1182
1183 x_new_deprn_basis := p_old_deprn_basis -
1184 (p_old_deprn_basis * p_retirement_amount /
1185 p_old_cost);
1186
1187 l_dummy_bool := fa_utils_pkg.faxrnd (x_new_deprn_basis, p_Book_Type_Code, p_set_of_books_id, p_log_level_rec => p_log_level_rec);
1188
1189 return (TRUE);
1190
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193
1194 if (sqlcode <> 0) then
1195 fa_rx_conc_mesg_pkg.log(sqlerrm);
1196 end if;
1197
1198 fa_srvr_msg.add_sql_error (
1199 calling_fn => 'fa_polish_pvt.Calc_Basis_Partial_Ret', p_log_level_rec => p_log_level_rec);
1200 return (FALSE);
1201
1202 END Calc_Basis_Partial_Ret;
1203
1204 FUNCTION Calc_Basis_Trxn (
1205 p_book_type_code IN VARCHAR2,
1206 p_transaction_type_code IN VARCHAR2,
1207 p_pos_neg_adjustment IN VARCHAR2,
1208 p_polish_adj_calc_basis_flag
1209 IN VARCHAR2,
1210 p_adjusted_cost IN NUMBER,
1211 p_adjusted_recoverable_cost
1212 IN NUMBER,
1213 p_prev_adj_rec_cost IN NUMBER,
1214 p_prev_basis IN NUMBER,
1215 p_prev_reserve IN NUMBER,
1216 p_adjustment_amount IN NUMBER,
1217 p_retirement_amount IN NUMBER,
1218 p_switch_code IN VARCHAR2,
1219 p_set_of_books_id IN NUMBER,
1220 x_adjusted_cost OUT NOCOPY NUMBER,
1221 x_adjusted_recoverable_cost OUT NOCOPY NUMBER,
1222 p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1223 RETURN BOOLEAN IS
1224
1225 basis_err exception;
1226 Begin
1227
1228 if ((p_transaction_type_code = 'ADJUSTMENT') and
1229 (p_pos_neg_adjustment = 'NEG')) then
1230
1231 -- Calculate new basis
1232 if (p_Polish_Adj_Calc_Basis_Flag = 'Y') then
1233 if not (Calc_Basis_Neg_Adj_Flag_Yes (
1234 p_book_type_code => p_book_type_code,
1235 p_old_cost => p_prev_adj_rec_cost,
1236 p_old_reserve => p_prev_reserve,
1237 p_adjustment_amount => p_adjustment_amount,
1238 p_set_of_books_id => p_set_of_books_id,
1239 x_new_deprn_basis => x_adjusted_cost,
1240 p_log_level_rec => p_log_level_rec
1241 )) then
1242 raise basis_err;
1243 end if;
1244
1245 -- For a negative adjustment with the polish_adj_calc_basis_flag
1246 -- checked, we over-depreciate the asset, setting the cost at
1247 -- which it becomes fully reserved, i.e., the
1248 -- adjusted_recoverable_cost to the new basis.
1249 x_adjusted_recoverable_cost := x_adjusted_cost;
1250
1251 else
1252 if not (Calc_Basis_Neg_Adj_Flag_No (
1253 p_book_type_code => p_book_type_code,
1254 p_old_cost => p_prev_adj_rec_cost,
1255 p_old_deprn_basis => p_prev_basis,
1256 p_adjustment_amount => p_adjustment_amount,
1257 p_set_of_books_id => p_set_of_books_id,
1258 x_new_deprn_basis => x_adjusted_cost,
1259 p_log_level_rec => p_log_level_rec
1260 )) then
1261 raise basis_err;
1262 end if;
1263
1264 -- These values don't change from the defaults
1265 x_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
1266 end if;
1267
1268 elsif ((p_transaction_type_code = 'ADJUSTMENT') and
1269 (p_pos_neg_adjustment = 'POS')) then
1270
1271 -- For a positive adjustment, the new basis is just the new
1272 -- cost if the switch is currently FR. However, for anything else,
1273 -- the basis stays the same as it was before the adjustment.
1274 if (p_switch_code = 'FR') then
1275 x_adjusted_cost := p_adjusted_cost;
1276 x_adjusted_recoverable_cost := p_adjusted_cost;
1277
1278 else
1279 -- Need to go w/ the value before the adjustment occurred.
1280 x_adjusted_cost := p_prev_basis;
1281
1282 -- We will change the fully reservable cost to reflect the new cost
1283 -- however.
1284 x_adjusted_recoverable_cost := p_adjusted_recoverable_cost;
1285 end if;
1286 elsif (p_transaction_type_code = 'PARTIAL RETIREMENT') then
1287
1288 if not (Calc_Basis_Partial_Ret (
1289 p_book_type_code => p_book_type_code,
1290 p_old_cost => p_prev_adj_rec_cost,
1291 p_old_deprn_basis => p_prev_basis,
1292 p_retirement_amount => p_retirement_amount,
1293 p_set_of_books_id => p_set_of_books_id,
1294 x_new_deprn_basis => x_adjusted_cost,
1295 p_log_level_rec => p_log_level_rec
1296 )) then
1297 raise basis_err;
1298 end if;
1299
1300 -- These values don't change from the defaults
1301 x_adjusted_recoverable_cost := p_Adjusted_Recoverable_Cost;
1302 end if;
1303
1304 return (TRUE);
1305
1306 EXCEPTION
1307 WHEN basis_err THEN
1308
1309 if (sqlcode <> 0) then
1310 fa_rx_conc_mesg_pkg.log(sqlerrm);
1311 end if;
1312
1313 fa_srvr_msg.add_sql_error (
1314 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn', p_log_level_rec => p_log_level_rec);
1315 return (FALSE);
1316
1317 WHEN OTHERS THEN
1318
1319 if (sqlcode <> 0) then
1320 fa_rx_conc_mesg_pkg.log(sqlerrm);
1321 end if;
1322
1323 fa_srvr_msg.add_sql_error (
1324 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn', p_log_level_rec => p_log_level_rec);
1325 return (FALSE);
1326 END Calc_Basis_Trxn;
1327
1328 FUNCTION Calc_Trxn_Values (
1329 p_book_type_code IN VARCHAR2,
1330 p_asset_id IN NUMBER,
1331 p_transaction_type_code IN VARCHAR2,
1332 p_transaction_header_id IN NUMBER,
1333 p_first_period_counter IN NUMBER,
1334 p_mrc_sob_type_code IN VARCHAR2,
1335 p_set_of_books_id IN NUMBER,
1336 p_calling_mode IN VARCHAR2,
1337 x_adjustment_amount OUT NOCOPY NUMBER,
1338 x_retirement_amount OUT NOCOPY NUMBER,
1339 x_bef_trxn_period_counter OUT NOCOPY NUMBER,
1340 x_pos_neg_adjustment OUT NOCOPY VARCHAR2,
1341 x_prev_basis OUT NOCOPY NUMBER,
1342 x_prev_adj_rec_cost OUT NOCOPY NUMBER,
1343 x_prev_reserve OUT NOCOPY NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1344 RETURN BOOLEAN IS
1345
1346 l_amortization_start_date date;
1347 l_debit_credit_flag varchar2(2);
1348 l_min_period_counter number;
1349
1350 polish_err exception;
1351
1352 BEGIN
1353
1354 -- ??? Probably need to still account for multiple adjustments or
1355 -- partial retirements in a single period. If we get the max id, we'd
1356 -- be missing some transactions. Maybe need to do a sum or something.
1357 if (p_transaction_type_code = 'ADJUSTMENT') then
1358
1359 if (p_calling_mode = 'ADJUSTMENT') then
1360
1361 l_amortization_start_date := FA_POLISH_PVT.amortization_start_date;
1362
1363 if (p_mrc_sob_type_code = 'R') then
1364
1365 select period_counter - 1
1366 into x_bef_trxn_period_counter
1367 from fa_mc_deprn_periods
1368 where book_type_code = p_Book_Type_Code
1369 and set_of_books_id = p_set_of_books_id
1370 and l_amortization_start_date between
1371 calendar_period_open_date and calendar_period_close_date;
1372 else
1373 select period_counter - 1
1374 into x_bef_trxn_period_counter
1375 from fa_deprn_periods
1376 where book_type_code = p_Book_Type_Code
1377 and l_amortization_start_date between
1378 calendar_period_open_date and calendar_period_close_date;
1379 end if;
1380
1381 -- Can't go before the first period of the asset's life
1382 if (x_bef_trxn_period_counter < p_first_period_counter) then
1383 x_bef_trxn_period_counter := p_first_period_counter;
1384 end if;
1385
1386 x_adjustment_amount := FA_POLISH_PVT.adjustment_amount;
1387
1388 if (x_adjustment_amount > 0) then
1389 -- Positive adjustment
1390 x_pos_neg_adjustment := 'POS';
1391 else
1392 x_pos_neg_adjustment := 'NEG';
1393
1394 -- Always need to have this positive.
1395 x_adjustment_amount := x_adjustment_amount * -1;
1396 end if;
1397
1398 else /* Brahma 6989831. If the transaction is not cost based then the following query returns no rows. so handle that exception */
1399
1400 if (p_MRC_SOB_Type_Code = 'R') then
1401 -- Determine if it's a positive or negative adj, amount and period
1402 -- immediately before the period of adjustment
1403 Begin
1404 select adjustment_amount,
1405 debit_credit_flag
1406 into x_adjustment_amount,
1407 l_debit_credit_flag
1408 from fa_mc_adjustments
1409 where book_type_code = p_Book_Type_Code
1410 and asset_id = p_Asset_Id
1411 and transaction_header_id = p_transaction_header_id
1412 and set_of_books_id = p_set_of_books_id
1413 and source_type_code = 'ADJUSTMENT'
1414 and adjustment_type = 'COST';
1415 Exception
1416 when no_data_found then
1417 x_adjustment_amount := 0;
1418 l_debit_credit_flag := null;
1419 End;
1420 else
1421 Begin
1422 select adjustment_amount,
1423 debit_credit_flag
1424 into x_adjustment_amount,
1425 l_debit_credit_flag
1426 from fa_adjustments
1427 where book_type_code = p_Book_Type_Code
1428 and asset_id = p_Asset_Id
1429 and transaction_header_id = p_transaction_header_id
1430 and source_type_code = 'ADJUSTMENT'
1431 and adjustment_type = 'COST';
1432 Exception
1433 when no_data_found then
1434 x_adjustment_amount := 0;
1435 l_debit_credit_flag := null;
1436 End;
1437 end if;
1438
1439 if ((l_debit_credit_flag = 'CR') and (x_adjustment_amount >= 0)) then
1440
1441 -- Negative adjustment
1442 x_pos_neg_adjustment := 'NEG';
1443
1444 elsif ((l_debit_credit_flag = 'DR') and (x_adjustment_amount < 0)) then
1445 -- Negative adjustment
1446 x_pos_neg_adjustment := 'NEG';
1447
1448 -- Flip the adjustment amount
1449 x_adjustment_amount := x_adjustment_amount * -1;
1450
1451 elsif ((l_debit_credit_flag = 'CR') and (x_adjustment_amount < 0)) then
1452 -- Positive adjustment
1453 x_pos_neg_adjustment := 'POS';
1454
1455 elsif ((l_debit_credit_flag = 'DR') and (x_adjustment_amount >= 0)) then
1456
1457 -- Positive adjustment
1458 x_pos_neg_adjustment := 'POS';
1459
1460 -- Flip the adjustment amount
1461 x_adjustment_amount := x_adjustment_amount * -1;
1462 end if;
1463 end if; /* End of if (p_calling_mode = 'ADJUSTMENT') then */
1464
1465 if (p_calling_mode = 'DEPRN') then
1466
1467 if (x_pos_neg_adjustment = 'POS') then
1468
1469 if (p_MRC_SOB_Type_Code = 'R') then
1470
1471 select dp.period_counter - 1
1472 into x_bef_trxn_period_counter
1473 from fa_mc_deprn_periods dp,
1474 fa_transaction_headers th
1475 where th.transaction_header_id = p_transaction_header_id
1476 and dp.book_type_code = p_Book_Type_Code
1477 and dp.set_of_books_id = p_set_of_books_id
1478 and th.date_effective between
1479 dp.period_open_date and
1480 nvl(dp.period_close_date, sysdate);
1481 else
1482
1483 select dp.period_counter - 1
1484 into x_bef_trxn_period_counter
1485 from fa_deprn_periods dp,
1486 fa_transaction_headers th
1487 where th.transaction_header_id = p_transaction_header_id
1488 and dp.book_type_code = p_Book_Type_Code
1489 and th.date_effective between
1490 dp.period_open_date and
1491 nvl(dp.period_close_date, sysdate);
1492 end if;
1493
1494 -- Can't go before the first period of the asset's life
1495 if (x_bef_trxn_period_counter < p_first_period_counter) then
1496 x_bef_trxn_period_counter := p_first_period_counter;
1497 end if;
1498
1499 elsif (x_pos_neg_adjustment = 'NEG') then
1500
1501 if (p_MRC_SOB_Type_Code = 'R') then
1502
1503 select dp.period_counter - 1
1504 into x_bef_trxn_period_counter
1505 from fa_mc_deprn_periods dp,
1506 fa_transaction_headers th
1507 where th.transaction_header_id = p_transaction_header_id
1508 and dp.book_type_code = p_Book_Type_Code
1509 and dp.set_of_books_id = p_set_of_books_id
1510 and th.transaction_date_entered between
1511 dp.calendar_period_open_date and
1512 dp.calendar_period_close_date;
1513 else
1514
1515 select dp.period_counter - 1
1516 into x_bef_trxn_period_counter
1517 from fa_deprn_periods dp,
1518 fa_transaction_headers th
1519 where th.transaction_header_id = p_transaction_header_id
1520 and dp.book_type_code = p_Book_Type_Code
1521 and th.transaction_date_entered between
1522 dp.calendar_period_open_date and
1523 dp.calendar_period_close_date;
1524 end if;
1525
1526 -- Can't go before the first period of the asset's life
1527 if (x_bef_trxn_period_counter < p_first_period_counter) then
1528 x_bef_trxn_period_counter := p_first_period_counter;
1529 end if;
1530 end if;
1531 end if; /* End if of if (p_calling_mode = 'DEPRN') then */
1532
1533 -- Determine the cost and the reserve in the period before the adj
1534 if (p_MRC_SOB_Type_Code = 'R') then
1535
1536 -- Fix for Bug #5550557. Need to catch if books summary row
1537 -- does not exist.
1538 begin
1539 select polish_deprn_basis,
1540 polish_adj_rec_cost
1541 into x_prev_basis,
1542 x_prev_adj_rec_cost
1543 from fa_mc_books_summary
1544 where book_type_code = p_Book_Type_Code
1545 and asset_id = p_Asset_Id
1546 and period_counter = x_bef_trxn_period_counter
1547 and set_of_books_id = p_set_of_books_id;
1548
1549 -- Fix for Bug #5907258. If these values are null, can cause
1550 -- data corruption. Needs to be fixed first.
1551 if (x_prev_basis is null) or
1552 (x_prev_adj_rec_cost is null) then
1553
1554 fa_srvr_msg.add_message(
1555 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1556 name => '***FA_POLISH_NULL_COST***',
1557 p_log_level_rec => p_log_level_rec);
1558
1559 raise polish_err;
1560 end if;
1561
1562 exception
1563
1564 when no_data_found then
1565
1566 select min(period_counter)
1567 into l_min_period_counter
1568 from fa_mc_deprn_periods
1569 where book_type_code = p_Book_Type_Code
1570 and set_of_books_id = p_set_of_books_id;
1571
1572 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1573
1574 begin
1575
1576 select bks.adjusted_cost,
1577 bks.adjusted_recoverable_cost
1578 into x_prev_basis,
1579 x_prev_adj_rec_cost
1580 from fa_mc_books bks
1581 where bks.book_type_code = p_Book_Type_Code
1582 and bks.asset_id = p_Asset_Id
1583 and bks.set_of_books_id = p_set_of_books_id
1584 and bks.rowid =
1585 (
1586 select max(bks1.rowid)
1587 from fa_mc_books bks1,
1588 fa_mc_deprn_periods dp
1589 where bks1.book_type_code = p_Book_Type_Code
1590 and bks1.asset_id = p_Asset_Id
1591 and bks1.book_type_code = dp.book_type_code
1592 and bks1.set_of_books_id = p_set_of_books_id
1593 and dp.period_counter = x_bef_trxn_period_counter
1594 and dp.set_of_books_id = p_set_of_books_id
1595 and bks1.date_effective <=
1596 nvl(dp.period_close_date, sysdate)
1597 );
1598 end;
1599 elsif (x_bef_trxn_period_counter < l_min_period_counter) then /* Brahma 6989831. when x_bef_trxn_period_counter is null then else part was getting executed. but it should not that case so added lessthan condition */
1600 begin
1601
1602 select bks.adjusted_cost,
1603 bks.adjusted_recoverable_cost
1604 into x_prev_basis,
1605 x_prev_adj_rec_cost
1606 from fa_mc_books bks
1607 where bks.book_type_code = p_Book_Type_Code
1608 and bks.asset_id = p_Asset_Id
1609 and bks.set_of_books_id = p_set_of_books_id
1610 and bks.rowid =
1611 (
1612 select min(bks1.rowid)
1613 from fa_mc_books bks1
1614 where bks1.book_type_code = p_Book_Type_Code
1615 and bks1.asset_id = p_Asset_Id
1616 and bks.set_of_books_id = p_set_of_books_id
1617 );
1618 end;
1619 end if;
1620 end;
1621
1622 Begin /* Brahma 6989831. when x_bef_trxn_period_counter is null the following query returns no rows. so adding exception handling */
1623 select deprn_reserve
1624 into x_prev_reserve
1625 from fa_mc_deprn_summary
1626 where book_type_code = p_Book_Type_Code
1627 and asset_id = p_Asset_Id
1628 and period_counter = x_bef_trxn_period_counter
1629 and set_of_books_id = p_set_of_books_id;
1630
1631 Exception
1632 when no_data_found then
1633 x_prev_reserve := 0;
1634 End;
1635 else
1636
1637
1638 -- Fix for Bug #5550557. Need to catch if books summary row
1639 -- does not exist.
1640 begin
1641
1642 select polish_deprn_basis,
1643 polish_adj_rec_cost
1644 into x_prev_basis,
1645 x_prev_adj_rec_cost
1646 from fa_books_summary
1647 where book_type_code = p_Book_Type_Code
1648 and asset_id = p_Asset_Id
1649 and period_counter = x_bef_trxn_period_counter;
1650
1651 -- Fix for Bug #5907258. If these values are null, can cause
1652 -- data corruption. Needs to be fixed first.
1653 if (x_prev_basis is null) or
1654 (x_prev_adj_rec_cost is null) then
1655
1656 fa_srvr_msg.add_message(
1657 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1658 name => '***FA_POLISH_NULL_COST***',
1659 p_log_level_rec => p_log_level_rec);
1660
1661 raise polish_err;
1662 end if;
1663
1664 exception
1665
1666 when no_data_found then
1667
1668 select min(period_counter)
1669 into l_min_period_counter
1670 from fa_deprn_periods
1671 where book_type_code = p_Book_Type_Code;
1672
1673 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1674
1675 begin
1676
1677 select bks.adjusted_cost,
1678 bks.adjusted_recoverable_cost
1679 into x_prev_basis,
1680 x_prev_adj_rec_cost
1681 from fa_books bks
1682 where bks.book_type_code = p_Book_Type_Code
1683 and bks.asset_id = p_Asset_Id
1684 and bks.rowid =
1685 (
1686 select max(bks1.rowid)
1687 from fa_books bks1,
1688 fa_deprn_periods dp
1689 where bks1.book_type_code = p_Book_Type_Code
1690 and bks1.asset_id = p_Asset_Id
1691 and bks1.book_type_code = dp.book_type_code
1692 and dp.period_counter = x_bef_trxn_period_counter
1693 and bks1.date_effective <=
1694 nvl(dp.period_close_date, sysdate)
1695 );
1696 end;
1697 elsif (x_bef_trxn_period_counter < l_min_period_counter) then /* Brahma 6989831. when x_bef_trxn_period_counter is null then else part was getting executed. but it should not that case so added lessthan condition */
1698 begin
1699
1700 select bks.adjusted_cost,
1701 bks.adjusted_recoverable_cost
1702 into x_prev_basis,
1703 x_prev_adj_rec_cost
1704 from fa_books bks
1705 where bks.book_type_code = p_Book_Type_Code
1706 and bks.asset_id = p_Asset_Id
1707 and bks.rowid =
1708 (
1709 select min(bks1.rowid)
1710 from fa_books bks1
1711 where bks1.book_type_code = p_Book_Type_Code
1712 and bks1.asset_id = p_Asset_Id
1713 );
1714 end;
1715 end if;
1716 end;
1717
1718 Begin /* Brahma 6989831. when x_bef_trxn_period_counter is null the following query returns no rows. so adding exception handling */
1719 select deprn_reserve
1720 into x_prev_reserve
1721 from fa_deprn_summary
1722 where book_type_code = p_Book_Type_Code
1723 and asset_id = p_Asset_Id
1724 and period_counter = x_bef_trxn_period_counter;
1725 Exception
1726 when no_data_found then
1727 x_prev_reserve := 0;
1728 End;
1729 end if;
1730
1731 -- Fix for Bug #3629784. If you have an asset with a following month
1732 -- prorate convention, the basis will be 0 in the first period of its
1733 -- life since you don't want to depreciate until the next period.
1734 -- However, if you do an adjustment that is effective in the second
1735 -- period, the previous basis will get a value of 0, which you don't
1736 -- want. You need the actual cost here since you do want to
1737 -- depreciate in the 2nd period.
1738 if (x_bef_trxn_period_counter = p_first_period_counter) then
1739 x_prev_basis := x_prev_adj_rec_cost;
1740 end if;
1741 elsif (p_transaction_type_code = 'PARTIAL RETIREMENT') then
1742
1743 -- Get the retirement values
1744 if (p_MRC_SOB_Type_Code = 'R') then
1745
1746 select adjustment_amount,
1747 debit_credit_flag
1748 into x_retirement_amount,
1749 l_debit_credit_flag
1750 from fa_mc_adjustments
1751 where book_type_code = p_Book_Type_Code
1752 and asset_id = p_Asset_Id
1753 and transaction_header_id = p_transaction_header_id
1754 and source_type_code = 'RETIREMENT'
1755 and adjustment_type = 'COST'
1756 and set_of_books_id = p_set_of_books_id;
1757
1758 select dp.period_counter - 1
1759 into x_bef_trxn_period_counter
1760 from fa_mc_deprn_periods dp,
1761 fa_transaction_headers th
1762 where th.transaction_header_id = p_transaction_header_id
1763 and dp.book_type_code = p_Book_Type_Code
1764 and dp.set_of_books_id = p_set_of_books_id
1765 and th.transaction_date_entered between
1766 dp.calendar_period_open_date and
1767 dp.calendar_period_close_date;
1768
1769 else
1770
1771 select adjustment_amount,
1772 debit_credit_flag
1773 into x_retirement_amount,
1774 l_debit_credit_flag
1775 from fa_adjustments
1776 where book_type_code = p_Book_Type_Code
1777 and asset_id = p_Asset_Id
1778 and transaction_header_id = p_transaction_header_id
1779 and source_type_code = 'RETIREMENT'
1780 and adjustment_type = 'COST';
1781
1782 select dp.period_counter - 1
1783 into x_bef_trxn_period_counter
1784 from fa_deprn_periods dp,
1785 fa_transaction_headers th
1786 where th.transaction_header_id = p_transaction_header_id
1787 and dp.book_type_code = p_Book_Type_Code
1788 and th.transaction_date_entered between
1789 dp.calendar_period_open_date and
1790 dp.calendar_period_close_date;
1791 end if;
1792
1793 -- Can't go before the first period of the asset's life
1794 if (x_bef_trxn_period_counter < p_first_period_counter) then
1795 x_bef_trxn_period_counter := p_first_period_counter;
1796 end if;
1797
1798 if (l_debit_credit_flag = 'DR') then
1799 -- Flip adjustment_amount
1800 x_retirement_amount := x_retirement_amount * -1;
1801 end if;
1802
1803 -- Determine the cost in the period before the retirement
1804 if (p_MRC_SOB_Type_Code = 'R') then
1805
1806 -- Fix for Bug #5550557. Need to catch if books summary row
1807 -- does not exist.
1808 begin
1809
1810 select polish_deprn_basis,
1811 polish_adj_rec_cost
1812 into x_prev_basis,
1813 x_prev_adj_rec_cost
1814 from fa_mc_books_summary
1815 where book_type_code = p_Book_Type_Code
1816 and asset_id = p_Asset_Id
1817 and period_counter = x_bef_trxn_period_counter
1818 and set_of_books_id = p_set_of_books_id;
1819
1820 -- Fix for Bug #5907258. If these values are null, can cause
1821 -- data corruption. Needs to be fixed first.
1822 if (x_prev_basis is null) or
1823 (x_prev_adj_rec_cost is null) then
1824
1825 fa_srvr_msg.add_message(
1826 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1827 name => '***FA_POLISH_NULL_COST***',
1828 p_log_level_rec => p_log_level_rec);
1829
1830 raise polish_err;
1831 end if;
1832
1833 exception
1834
1835 when no_data_found then
1836
1837 select min(period_counter)
1838 into l_min_period_counter
1839 from fa_mc_deprn_periods
1840 where book_type_code = p_Book_Type_Code
1841 and set_of_books_id = p_set_of_books_id;
1842
1843 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1844
1845 begin
1846
1847 select bks.adjusted_cost,
1848 bks.adjusted_recoverable_cost
1849 into x_prev_basis,
1850 x_prev_adj_rec_cost
1851 from fa_mc_books bks
1852 where bks.book_type_code = p_Book_Type_Code
1853 and bks.asset_id = p_Asset_Id
1854 and bks.set_of_books_id = p_set_of_books_id
1855 and bks.rowid =
1856 (
1857 select max(bks1.rowid)
1858 from fa_mc_books bks1,
1859 fa_mc_deprn_periods dp
1860 where bks1.book_type_code = p_Book_Type_Code
1861 and bks1.asset_id = p_Asset_Id
1862 and bks1.set_of_books_id = p_set_of_books_id
1863 and bks1.book_type_code = dp.book_type_code
1864 and dp.period_counter = x_bef_trxn_period_counter
1865 and dp.set_of_books_id = p_set_of_books_id
1866 and bks1.date_effective <=
1867 nvl(dp.period_close_date, sysdate)
1868 );
1869 end;
1870 else
1871 begin
1872
1873 select bks.adjusted_cost,
1874 bks.adjusted_recoverable_cost
1875 into x_prev_basis,
1876 x_prev_adj_rec_cost
1877 from fa_mc_books bks
1878 where bks.book_type_code = p_Book_Type_Code
1879 and bks.asset_id = p_Asset_Id
1880 and bks.set_of_books_id = p_set_of_books_id
1881 and bks.rowid =
1882 (
1883 select min(bks1.rowid)
1884 from fa_mc_books bks1
1885 where bks1.book_type_code = p_Book_Type_Code
1886 and bks1.asset_id = p_Asset_Id
1887 and bks1.set_of_books_id = p_set_of_books_id
1888 );
1889 end;
1890 end if;
1891 end;
1892
1893 else
1894
1895 -- Fix for Bug #5550557. Need to catch if books summary row
1896 -- does not exist.
1897 begin
1898
1899 select polish_deprn_basis,
1900 polish_adj_rec_cost
1901 into x_prev_basis,
1902 x_prev_adj_rec_cost
1903 from fa_books_summary
1904 where book_type_code = p_Book_Type_Code
1905 and asset_id = p_Asset_Id
1906 and period_counter = x_bef_trxn_period_counter;
1907
1908 -- Fix for Bug #5907258. If these values are null, can cause
1909 -- data corruption. Needs to be fixed first.
1910 if (x_prev_basis is null) or
1911 (x_prev_adj_rec_cost is null) then
1912
1913 fa_srvr_msg.add_message(
1914 calling_fn => 'fa_polish_pvt.calc_trxn_values',
1915 name => '***FA_POLISH_NULL_COST***',
1916 p_log_level_rec => p_log_level_rec);
1917
1918 raise polish_err;
1919 end if;
1920
1921 exception
1922
1923 when no_data_found then
1924
1925 select min(period_counter)
1926 into l_min_period_counter
1927 from fa_deprn_periods
1928 where book_type_code = p_Book_Type_Code;
1929
1930 if (x_bef_trxn_period_counter >= l_min_period_counter) then
1931
1932 begin
1933
1934 select bks.adjusted_cost,
1935 bks.adjusted_recoverable_cost
1936 into x_prev_basis,
1937 x_prev_adj_rec_cost
1938 from fa_books bks
1939 where bks.book_type_code = p_Book_Type_Code
1940 and bks.asset_id = p_Asset_Id
1941 and bks.rowid =
1942 (
1943 select max(bks1.rowid)
1944 from fa_books bks1,
1945 fa_deprn_periods dp
1946 where bks1.book_type_code = p_Book_Type_Code
1947 and bks1.asset_id = p_Asset_Id
1948 and bks1.book_type_code = dp.book_type_code
1949 and dp.period_counter = x_bef_trxn_period_counter
1950 and bks1.date_effective <=
1951 nvl(dp.period_close_date, sysdate)
1952 );
1953 end;
1954 else
1955 begin
1956
1957 select bks.adjusted_cost,
1958 bks.adjusted_recoverable_cost
1959 into x_prev_basis,
1960 x_prev_adj_rec_cost
1961 from fa_books bks
1962 where bks.book_type_code = p_Book_Type_Code
1963 and bks.asset_id = p_Asset_Id
1964 and bks.rowid =
1965 (
1966 select min(bks1.rowid)
1967 from fa_books bks1
1968 where bks1.book_type_code = p_Book_Type_Code
1969 and bks1.asset_id = p_Asset_Id
1970 );
1971 end;
1972 end if;
1973 end;
1974 end if;
1975
1976 -- Fix for Bug #3629784. If you have an asset with a following month
1977 -- prorate convention, the basis will be 0 in the first period of its
1978 -- life since you don't want to depreciate until the next period.
1979 -- However, if you do a retirement that is effective in the second
1980 -- period, the previous basis will get a value of 0, which you don't
1981 -- want. You need the actual cost here since you do want to
1982 -- depreciate in the 2nd period.
1983 if (x_bef_trxn_period_counter = p_first_period_counter) then
1984 x_prev_basis := x_prev_adj_rec_cost;
1985 end if;
1986 end if;
1987
1988 return (TRUE);
1989
1990 EXCEPTION
1991
1992 WHEN polish_err THEN
1993
1994 if (sqlcode <> 0) then
1995 fa_rx_conc_mesg_pkg.log(sqlerrm);
1996 end if;
1997
1998 fa_srvr_msg.add_sql_error (
1999 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn', p_log_level_rec => p_log_level_rec);
2000 return (FALSE);
2001
2002 WHEN OTHERS THEN
2003
2004 if (sqlcode <> 0) then
2005 fa_rx_conc_mesg_pkg.log(sqlerrm);
2006 end if;
2007
2008 fa_srvr_msg.add_sql_error (
2009 calling_fn => 'fa_polish_pvt.Calc_Basis_Trxn', p_log_level_rec => p_log_level_rec);
2010 return (FALSE);
2011 END Calc_Trxn_Values;
2012
2013 FUNCTION Calc_Rate (
2014 p_book_type_code IN VARCHAR2,
2015 p_asset_id IN NUMBER,
2016 p_polish_rule IN NUMBER,
2017 p_year_of_life IN NUMBER,
2018 p_year_retired IN VARCHAR2,
2019 p_period_counter IN NUMBER,
2020 p_first_period_counter IN NUMBER,
2021 p_open_period_counter IN NUMBER,
2022 p_period_num IN NUMBER,
2023 p_periods_per_year IN NUMBER,
2024 p_adjusted_rate IN NUMBER,
2025 p_deprn_factor IN NUMBER,
2026 p_alternate_deprn_factor IN NUMBER,
2027 p_depreciate_flag IN VARCHAR2,
2028 p_first_year_reserve IN NUMBER,
2029 p_prev_year_reserve IN NUMBER,
2030 p_prev_year_adjusted_cost
2031 IN NUMBER,
2032 p_prev_year_adj_rec_cost IN NUMBER,
2033 p_mrc_sob_type_code IN VARCHAR2,
2034 p_set_of_books_id IN NUMBER,
2035 x_adjusted_rate OUT NOCOPY NUMBER,
2036 x_depreciate_flag OUT NOCOPY VARCHAR2,
2037 x_switch_code OUT NOCOPY VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
2038 RETURN BOOLEAN IS
2039
2040 FA_POLISH_30_RULE constant number := .3;
2041
2042 l_prev_switch_code varchar2(2);
2043 l_new_basis number;
2044 l_switch_value number;
2045
2046 calc_rate_err exception;
2047
2048 BEGIN
2049
2050 -- Default values
2051 x_adjusted_rate := p_adjusted_rate;
2052 x_depreciate_flag := p_depreciate_flag;
2053 x_switch_code := 'XX';
2054
2055 -- In an asset's first year of life, it has a specific rate and switch
2056 -- code. In addition, we do not do the switch test in the first year.
2057 if ((p_year_of_life = 1) and (p_Year_Retired = 'N')) then
2058
2059 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
2060 FA_STD_TYPES.FAD_DBR_POLISH_2)) then
2061
2062 x_switch_code := '30';
2063
2064 elsif (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_3,
2065 FA_STD_TYPES.FAD_DBR_POLISH_4)) then
2066
2067 x_switch_code := 'DM';
2068
2069 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
2070
2071 x_switch_code := 'SD';
2072
2073 else
2074 -- Not one of the 5 Polish Mechanisms.
2075
2076 fa_srvr_msg.add_message(
2077 calling_fn => 'fa_polish_pvt.calc_rate',
2078 name => '***FA_POLISH_INVALID_MECH***',
2079 p_log_level_rec => p_log_level_rec);
2080
2081 raise calc_rate_err;
2082 end if;
2083 end if;
2084
2085 -- After the first year, some mechanisms automatically move to a new switch
2086 if ((p_year_of_life > 1) and (p_Year_Retired = 'N')) then
2087 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
2088 FA_STD_TYPES.FAD_DBR_POLISH_3)) then
2089 if (p_Year_Of_Life = 2) then
2090
2091 -- For 2nd year, rate is adj_rate * deprn_factor
2092 -- No switch test is done in the second year
2093 x_switch_code := 'D2';
2094 x_adjusted_rate := p_adjusted_rate * p_Deprn_Factor;
2095
2096 else
2097 -- After year 2, we will always need to do the switch test.
2098 -- We're going to use XX to signify that we'll figure out the
2099 -- switch and new basis later.
2100 x_switch_code := 'XX';
2101
2102 end if;
2103 elsif (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_2,
2104 FA_STD_TYPES.FAD_DBR_POLISH_4)) then
2105 -- After the first year, rate is adj_rate
2106 -- No switch test is done
2107 x_switch_code := 'FR';
2108 x_adjusted_rate := p_adjusted_rate;
2109
2110 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
2111 -- For Mech 5, we do the switch test immediately after year 1.
2112 -- We're going to use XX to signify that we'll figure out the
2113 -- switch and new basis later.
2114 x_switch_code := 'XX';
2115
2116 end if;
2117 end if;
2118
2119 -- Check if old period
2120 if ((x_switch_code = 'XX') and
2121 (p_period_counter < p_open_period_counter)) then
2122
2123 -- We never modify a switch_code once we set it, so if we are doing a
2124 -- backdated transaction or something else where we're looking at old
2125 -- periods, just take the switch_code that was there originally. The
2126 -- nvl is to handle perhaps a method change where the asset wasn't
2127 -- always a Polish one.
2128 if (p_mrc_sob_type_code = 'R') then
2129
2130 begin
2131
2132 select nvl(switch_code, 'XX')
2133 into x_switch_code
2134 from fa_mc_books_summary
2135 where book_type_code = p_book_type_code
2136 and asset_id = p_asset_id
2137 and period_counter = p_period_counter
2138 and set_of_books_id = p_set_of_books_id;
2139 exception
2140 when no_data_found then
2141 null;
2142 end;
2143 else
2144
2145 begin
2146 select nvl(switch_code, 'XX')
2147 into x_switch_code
2148 from fa_books_summary
2149 where book_type_code = p_book_type_code
2150 and asset_id = p_asset_id
2151 and period_counter = p_period_counter;
2152 exception
2153 when no_data_found then
2154 null;
2155 end;
2156 end if;
2157 end if;
2158
2159 -- Check previous switch code rules
2160 if (x_switch_code = 'XX') then
2161 if (p_mrc_sob_type_code = 'R') then
2162
2163 begin
2164 select nvl(switch_code, 'XX')
2165 into l_prev_switch_code
2166 from fa_mc_books_summary
2167 where book_type_code = p_book_type_code
2168 and asset_id = p_asset_id
2169 and period_counter = p_period_counter - 1
2170 and set_of_books_id = p_set_of_books_id;
2171 exception
2172 when no_data_found then
2173 l_prev_switch_code := 'XX';
2174 end;
2175 else
2176
2177 begin
2178 select nvl(switch_code, 'XX')
2179 into l_prev_switch_code
2180 from fa_books_summary
2181 where book_type_code = p_book_type_code
2182 and asset_id = p_asset_id
2183 and period_counter = p_period_counter - 1;
2184 exception
2185 when no_data_found then
2186 l_prev_switch_code := 'XX';
2187 end;
2188 end if;
2189
2190 -- You never change the switch with a fiscal year, so after the first
2191 -- period.
2192 if (p_period_num > 1) then
2193 x_switch_code := l_prev_switch_code;
2194
2195 -- You can never move backwards in the switch chain, so once the switch
2196 -- is at FR, it stays there.
2197 elsif (p_period_num = 1) then
2198 if (l_prev_switch_code = 'FR') then
2199 x_switch_code := 'FR';
2200 end if;
2201 end if;
2202 end if;
2203
2204 -- Do the switch test if necessary. The switch test is always based on
2205 -- old data, and specifically, what happened after the last period of the
2206 -- previous fiscal year close. Even if a transaction occurred in the
2207 -- current period, even the first period, it does not affect the switch
2208 -- code. Transactions such as this would only affect the basis. It would
2209 -- not affect the switch until the nexte year. Even backdated transactions
2210 -- have no effect since you cannot change the switch code within the
2211 -- fiscal year. Once the year end for a fiscal year has closed, the
2212 -- switch code for the next year is set.
2213 --
2214 -- Here is how the switch test is derived.
2215 --
2216 -- The switch formula is as follows:
2217 -- (basis for D2) * (rate for D2) / pers_per_year <=
2218 -- (basis for FR) * (rate for FR) / pers_per_year.
2219 --
2220 -- This derives into: (when cancelling common variables)
2221 --
2222 -- (adjusted_cost for D2) * (adj_rate * deprn_factor) <=
2223 -- (adjusted_cost) * (adj_rate)
2224 --
2225 -- Further deriving into:
2226 --
2227 -- (adjusted_cost - prev_yr_rsv + first_yr_rsv) * deprn_factor
2228 -- <= (adjusted_cost)
2229 --
2230 -- This simiplied switch rule is common to Polish mechanisms
2231 -- 1 and 3. For mechanism 5, we just want the nbv and don't
2232 -- want to add back the first year reserve for the formula is:
2233 --
2234 -- (adjusted_cost - prev_yr_rsv) * deprn_factor <= (adjusted_cost)
2235 --
2236 if (x_switch_code = 'XX') then
2237 if (p_Polish_Rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
2238 FA_STD_TYPES.FAD_DBR_POLISH_3)) then
2239
2240 -- Switch between D2 and FR
2241
2242 l_new_basis := p_prev_year_adjusted_cost - p_prev_year_reserve +
2243 p_first_year_reserve;
2244
2245 l_switch_value := l_new_basis * p_deprn_factor;
2246
2247 -- Now do switch test
2248 if (l_switch_value > p_prev_year_adjusted_cost) then
2249
2250 -- Stay with current D2
2251 x_switch_code := 'D2';
2252 else
2253 -- Switch to FR
2254 x_switch_code := 'FR';
2255 end if;
2256
2257 elsif (p_Polish_Rule = FA_STD_TYPES.FAD_DBR_POLISH_5) then
2258
2259 -- Switch between SD and FR
2260
2261 l_new_basis := p_prev_year_adj_rec_cost - p_prev_year_reserve;
2262
2263 l_switch_value := l_new_basis * p_deprn_factor;
2264
2265 -- Now do switch test
2266 if (l_switch_value > p_prev_year_adj_rec_cost) then
2267
2268 -- Stay with current SD
2269 x_switch_code := 'SD';
2270
2271 else
2272 -- Switch to FR
2273 x_switch_code := 'FR';
2274
2275 end if;
2276 end if;
2277 end if;
2278
2279 -- Set the rate based on the switch code derived.
2280 if (x_switch_code = '30') then
2281
2282 if (p_period_counter = p_first_period_counter) then
2283
2284 -- We're multiplying the rate by the periods in the year because
2285 -- for the first year, we want to take the entire year's worth of
2286 -- depreciation in a single period when the switch is 30. For the
2287 -- rest of the year, we'll turn the depreciate flag off.
2288 x_adjusted_rate := FA_POLISH_30_RULE * p_Periods_Per_Year;
2289
2290 -- Note that an adjustment does not affect the rate or basis
2291 -- here since an adjustment in the period of addition creates
2292 -- an ADDITION/ADDITION VOID row rather than an ADJUSTMENT row
2293 -- Similarly, you can't retirement an asset in the period of add
2294 -- ??? How about an asset with a FOL MONTH convention and an
2295 -- ??? ADJUSTMENT that occurs in the period after addition?
2296 -- After discussion w/ Som, we decided to use our judgment and
2297 -- take the new cost after the adjustment as the new basis
2298 -- since depreciation had not been taken yet. For example, if
2299 -- user added an asset w/ cost 10K in March w/ FOL MON convention,
2300 -- and in April, adjusted the cost to 20K, we would use 20K as the
2301 -- the basis in April when we apply the 30% rule.
2302
2303 else
2304 -- When switch is 30, don't depreciate the asset in the first year
2305 -- after the first period
2306 x_adjusted_rate := 0;
2307 x_depreciate_flag := 'NO';
2308
2309 -- Note that transactions such as adjustments do not affect
2310 -- the rate here.
2311 end if;
2312 elsif (x_switch_code = 'DM') then
2313 -- When switch is DM, rate is adj_rate * alt_deprn_factor
2314 x_adjusted_rate := p_adjusted_rate * p_Alternate_Deprn_Factor;
2315
2316 elsif (x_switch_code in ('D2','SD')) then
2317 -- When switch is D2 or SD, rate is adj_rate * deprn_factor
2318 x_adjusted_rate := p_adjusted_rate * p_Deprn_Factor;
2319 elsif (x_switch_code = 'FR') then
2320 -- When switch is FR, rate is adj_rate
2321 x_adjusted_rate := p_adjusted_rate;
2322 else
2323 -- Unknown switch_code.
2324 fa_srvr_msg.add_message(
2325 calling_fn => 'fa_polish_pvt.calc_rate',
2326 name => '***FA_POLISH_INVALID_SWITCH_CODE***',
2327 p_log_level_rec => p_log_level_rec);
2328
2329 raise calc_rate_err;
2330 end if;
2331
2332 return (TRUE);
2333
2334 EXCEPTION
2335 WHEN calc_rate_err THEN
2336
2337 if (sqlcode <> 0) then
2338 fa_rx_conc_mesg_pkg.log(sqlerrm);
2339 end if;
2340
2341 fa_srvr_msg.add_sql_error (
2342 calling_fn => 'fa_polish_pvt.Calc_Rate', p_log_level_rec => p_log_level_rec);
2343 return (FALSE);
2344 WHEN OTHERS THEN
2345
2346 if (sqlcode <> 0) then
2347 fa_rx_conc_mesg_pkg.log(sqlerrm);
2348 end if;
2349
2350 fa_srvr_msg.add_sql_error (
2351 calling_fn => 'fa_polish_pvt.Calc_Rate', p_log_level_rec => p_log_level_rec);
2352 return (FALSE);
2353 END Calc_Rate;
2354
2355 END FA_POLISH_PVT;
[Home] [Help]