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