1 PACKAGE BODY FA_SHORT_TAX_YEARS_PKG AS
2 /* $Header: FAXSTYB.pls 120.7 2005/08/19 01:47:45 tkawamur ship $ */
3
4
5 --
6 -- PROCEDURE Calculate_Short_Tax_Vals
7 --
8
9 PROCEDURE Calculate_Short_Tax_Vals (
10 X_Asset_Id IN NUMBER,
11 X_Book_Type_Code IN VARCHAR2,
12 X_Short_Fiscal_Year_Flag IN VARCHAR2,
13 X_Date_Placed_In_Service IN DATE := NULL,
14 X_Deprn_Start_Date IN DATE := NULL,
15 X_Prorate_date IN DATE := NULL,
16 X_Conversion_Date IN DATE := NULL,
17 X_Orig_deprn_Start_Date IN DATE := NULL,
18 X_Curr_Fy_Start_Date IN DATE := NULL,
19 X_Curr_Fy_End_Date IN DATE := NULL,
20 C_Date_Placed_In_Service IN VARCHAR2 := NULL,
21 C_Deprn_Start_Date IN VARCHAR2 := NULL,
22 C_Prorate_date IN VARCHAR2 := NULL,
23 C_Conversion_Date IN VARCHAR2 := NULL,
24 C_Orig_Deprn_Start_Date IN VARCHAR2 := NULL,
25 C_Curr_Fy_Start_Date IN VARCHAR2 := NULL,
26 C_Curr_Fy_End_Date IN VARCHAR2 := NULL,
27 X_Life_In_Months IN NUMBER,
28 X_Rate_Source_Rule IN VARCHAR2,
29 X_Fiscal_Year IN NUMBER,
30 X_Method_Code IN VARCHAR2,
31 X_Current_Period IN NUMBER,
32 X_Remaining_Life1 OUT NOCOPY NUMBER,
33 X_Remaining_Life2 OUT NOCOPY NUMBER,
34 X_Success OUT NOCOPY VARCHAR2,
35 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
36
37 h_deprn_start_date DATE := X_Deprn_Start_Date;
38 h_prorate_date DATE := X_Prorate_Date;
39 h_conversion_date DATE := X_Conversion_Date;
40 h_orig_deprn_start_date DATE := X_Orig_deprn_Start_Date;
41 h_curr_fy_start_date DATE := X_Curr_Fy_Start_Date;
42 h_curr_fy_end_date DATE := X_Curr_Fy_End_Date;
43 h_dpis DATE := X_Date_Placed_In_Service;
44 h_num_days NUMBER;
45
46 -- added new variable to handle calculation of remaining life in non
47 -- short tax asset scenario at the end of fiscal year when
48 -- depreciation calls this from fadp5.lpc to calculate
49 -- remaining life for the next fiscal year
50 h_temp_fy_start_date DATE := X_Curr_Fy_Start_Date;
51
52 h_fy_name varchar2(30);
53 h_calendar_type varchar2(15);
54 h_cp_open_date date;
55 h_deprn_basis_rule_name varchar2(80);
56
57 cache_exception EXCEPTION;
58 l_period_rec FA_API_TYPES.period_rec_type;
59 l_amort_date date;
60
61 -- Bug4169773
62 Cursor c_get_trx_date is
63 select cp.start_date trx_date
64 from fa_transaction_headers th
65 , fa_calendar_periods cp
66 where th.asset_id = X_Asset_Id
67 and th.book_type_code = X_Book_Type_Code
68 and cp.calendar_type = h_calendar_type
69 and nvl(th.amortization_start_date, th.transaction_date_entered)
70 between cp.start_date and cp.end_date
71 and th.transaction_subtype = 'AMORTIZED'
72 and nvl(th.amortization_start_date, th.transaction_date_entered)
73 between h_curr_fy_start_date and l_period_rec.calendar_period_close_date
74 order by trx_date;
75
76 CURSOR check_date IS
77 SELECT decode(X_Short_Fiscal_Year_Flag,
78 'YES',decode(fy.fiscal_year - bc.current_fiscal_year,
79 0, greatest(h_conversion_date, h_deprn_start_date),
80 h_conversion_date),
81 h_conversion_date),
82 decode(fy.fiscal_year - bc.current_fiscal_year,
83 0, h_deprn_start_date,
84 decode(h_curr_fy_start_date,
85 NULL,to_date(C_Curr_Fy_Start_Date, 'DD/MM/YYYY'),
86 h_curr_fy_start_date))
87 FROM fa_fiscal_year fy,
88 fa_book_controls bc
89 WHERE h_dpis between fy.start_date
90 and fy.end_date
91 AND bc.book_type_code = X_Book_Type_Code
92 AND bc.fiscal_year_name = fy.fiscal_year_name;
93
94 CURSOR get_cp_date IS
95 select cp.start_date
96 from fa_calendar_periods cp,
97 fa_fiscal_year fy
98 where cp.calendar_type = h_calendar_type
99 and cp.period_num = X_Current_Period
100 and cp.start_date >= fy.start_date
101 and cp.end_date <= fy.end_date
102 and fy.fiscal_year_name = h_fy_name
103 and fy.fiscal_year = X_Fiscal_Year;
104
105
106 BEGIN
107
108 -- Check if the depreciation method is a FORMULA method. If not,
109 -- return NULL values for remaining lives,
110 -- as these values are not used for non-formula methods.
111 IF (X_Rate_Source_Rule <> 'FORMULA') THEN
112 X_Remaining_Life1 := NULL;
113 X_Remaining_Life2 := NULL;
114 X_Success := 'YES';
115 RETURN;
116 END IF;
117
118
119 IF (X_Deprn_Start_Date IS NULL) THEN
120 -- Pro*C function called this procedure.
121 h_deprn_start_date := to_date(C_Deprn_Start_Date, 'DD/MM/YYYY');
122 h_conversion_date := to_date(C_Conversion_Date, 'DD/MM/YYYY');
123 h_orig_deprn_start_date := to_date(C_Orig_Deprn_Start_Date,
124 'DD/MM/YYYY');
125 h_curr_fy_start_date := to_date(C_Curr_Fy_Start_Date,
126 'DD/MM/YYYY');
127 h_curr_fy_end_date := to_date(C_Curr_Fy_End_Date,
128 'DD/MM/YYYY');
129 h_dpis := to_date(C_Date_Placed_In_Service, 'DD/MM/YYYY');
130 h_prorate_date := to_date(C_Prorate_Date, 'DD/MM/YYYY');
131
132 -- when depreciation calls this from fadp5.lpc the value
133 -- for C_Curr_Fy_Start_Date is actually the next fiscal year
134 -- hold this in temp varibale and use this to calculate
135 -- remaining life when called for last period of fiscal year
136 -- since this will be greater than h_curr_fy_start_date
137 -- in this routine
138 h_temp_fy_start_date := to_date(C_Curr_Fy_Start_Date,
139 'DD/MM/YYYY');
140 END IF;
141
142 /*------------------------------------------------------------------+
143 | Set remaining lives. |
144 +------------------------------------------------------------------*/
145
146
147 -- Check if life_in_months is null. If so, remaining_life values
148 -- need not be calculated.
149 IF (nvl(X_Life_In_Months, 0) = 0) THEN
150 -- life_in_months is either a non-zero value or null.
151 X_Remaining_Life1 := NULL;
152 X_Remaining_Life2 := NULL;
153 X_Success := 'YES';
154 RETURN;
155 END IF;
156
157 /* Set remaining_life1. Should re-calculate every time as below
158 instead of reducing by one year, since deprn_start_date may have changed
159 if prorate_convention has changed.
160 remaining_life1 := deprn_start_date + life_in_months
161 - conversion_date */
162 OPEN check_date;
163 FETCH check_date into h_conversion_date,
164 h_curr_fy_start_date;
165 CLOSE check_date;
166
167 /* ****************************************************************
168 Fix for Bug 1095275. Set conversion date and fy_start_date
169 always to the first day of the month since months_between
170 will give +1 or -1 months depending on which day of the month
171 it is. This will result in different rate being returned
172 for an asset added in the same month but different dates for
173 example 01-JAN as opposed to 20-JAN
174 **************************************************************** */
175
176 h_num_days := to_number(to_char(h_conversion_date, 'DD'));
177 h_conversion_date := h_conversion_date - h_num_days + 1;
178
179 h_num_days := to_number(to_char(h_curr_fy_start_date, 'DD'));
180 h_curr_fy_start_date := h_curr_fy_start_date - h_num_days + 1;
181
182 h_num_days := to_number(to_char(h_temp_fy_start_date, 'DD'));
183 h_temp_fy_start_date := h_temp_fy_start_date - h_num_days + 1;
184
185
186 X_Remaining_Life1 :=
187 trunc(months_between(add_months(h_orig_deprn_start_date,
188 X_Life_In_Months),
189 (h_conversion_date)));
190 /* Set remaining_life2.
191 remaining_life2 := deprn_start_date + life_in_months
192 - (the first day of the following fiscal year of the
193 purchasing company). */
194
195 IF (h_conversion_date IS NULL) THEN
196 -- Not a short-tax year asset but uses FORMULA method.
197 -- Set remaining_life1..2 to be based from the current fiscal year
198 -- start date.
199
200 if (not fa_cache_pkg.fazccmt (
201 X_Method => X_Method_Code,
202 X_Life => X_Life_In_Months
203 ,p_log_level_rec => p_log_level_rec)) then
204 RAISE cache_exception;
205 end if;
206
207 h_deprn_basis_rule_name := fa_cache_pkg.fazcdbr_record.rule_name;
208
209 -- Bug4169773: Relocated to outside of following if because it
210 -- is used in else as well.
211 if (not fa_cache_pkg.fazcbc(X_Book_Type_Code ,p_log_level_rec => p_log_level_rec)) then
212 RAISE cache_exception;
213 end if;
214
215 h_calendar_type := fa_cache_pkg.fazcbc_record.deprn_calendar;
216
217 if (h_deprn_basis_rule_name in ('PERIOD END AVERAGE', 'BEGINNING PERIOD')) then
218
219 h_fy_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
220 open get_cp_date;
221 fetch get_cp_date into h_cp_open_date;
222 close get_cp_date;
223
224 X_Remaining_Life1 :=
225 trunc(months_between(add_months(h_prorate_date,
226 X_Life_In_Months),
227 (h_cp_open_date)));
228
229 else
230 -- Fix for bug 2005996 calculate X_Remaining_Life1 from
231 -- prorate date instead of deprn_start_date
232
233 -- Bug4169773: Remaining_Life1 cannot be the one as of
234 -- beginning of fy if there is an amortized adj during
235 -- current fy.
236 -- This fix is only for non-short tax year case.
237
238 if nvl(X_Short_Fiscal_Year_Flag, 'NO') = 'YES' then
239 X_Remaining_Life1 :=
240 trunc(months_between(add_months(h_prorate_date,
241 X_Life_In_Months),
242 (h_curr_fy_start_date)));
243
244 if (h_temp_fy_start_date > h_curr_fy_start_date) then
245 X_Remaining_Life1 :=
246 trunc(months_between(add_months(h_prorate_date,
247 X_Life_In_Months),
248 (h_temp_fy_start_date)));
249 end if;
250
251 else
252
253 if not FA_UTIL_PVT.get_period_rec
254 (p_book => X_Book_Type_Code,
255 p_effective_date => NULL,
256 x_period_rec => l_period_rec,
257 p_log_level_rec => p_log_level_rec) then
258
259 raise cache_exception;
260 end if;
261
262 open c_get_trx_date;
263 fetch c_get_trx_date into l_amort_date;
264
265 if c_get_trx_date%notfound then
266
267 X_Remaining_Life1 :=
268 trunc(months_between(add_months(h_prorate_date,
269 X_Life_In_Months),
270 (greatest(h_curr_fy_start_date, h_prorate_date))));
271
272 if (h_temp_fy_start_date > h_curr_fy_start_date) then
273 X_Remaining_Life1 :=
274 trunc(months_between(add_months(h_prorate_date,
275 X_Life_In_Months),
276 (h_temp_fy_start_date)));
277 end if;
278 else
279
280 X_Remaining_Life1 :=
281 trunc(months_between(add_months(h_prorate_date,
282 X_Life_In_Months),
283 (l_amort_date)));
284
285
286 end if;
287
288 close c_get_trx_date;
289
290 end if; -- nvl(X_Short_Fiscal_Year_Flag, 'NO') = 'YES'
291
292 end if;
293
294 X_Remaining_Life2 := X_Remaining_Life1;
295 ELSE
296 -- Remaining_life2 can be a negative value at the last year of asset life.
297 -- To get a correct depreciation amount, we should use FLOOR function
298 -- instead of TRUNC so that remainig_life is not inflated, when
299 -- remaining_life2 is a negative value(for positive value, FLOOR
300 -- functionality is exactly the same as TRUNC functionality.)
301 -- Also we do not want to take ABS value, since this generates
302 -- an incorrect depreciation rate.
303 X_Remaining_Life2 :=
304 floor(months_between(add_months(h_orig_deprn_start_date,
305 X_Life_In_Months),
306 (h_curr_fy_start_date)));
307 END IF;
308 X_Success := 'YES';
309
310
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 X_Remaining_Life1 := NULL;
315 X_Remaining_Life2 := NULL;
316 X_Success := 'NO';
317 END Calculate_Short_Tax_Vals;
318
319
320 END FA_SHORT_TAX_YEARS_PKG;
[Home] [Help]