[Home] [Help]
PACKAGE BODY: APPS.FA_CAPITALIZE_CIP_PKG1
Source
1 PACKAGE BODY FA_CAPITALIZE_CIP_PKG1 as
2 /* $Header: faxccab1.pls 120.7 2005/06/24 19:16:39 bridgway ship $ */
3
4 PROCEDURE CALC_SUBCOMP_LIFE(X_book VARCHAR2,
5 X_cat_id NUMBER,
6 X_parent_asset_id NUMBER,
7 X_dpis DATE,
8 h_deprn_method VARCHAR2,
9 h_prorate_date DATE,
10 X_user_id NUMBER,
11 X_curr_date DATE,
12 h_life IN OUT NOCOPY number,
13 X_Calling_Fn VARCHAR2,
14 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
15 )IS
16 CURSOR RATE_DEF IS
17 SELECT DISTINCT RATE_SOURCE_RULE
18 FROM FA_METHODS
19 WHERE METHOD_CODE = h_deprn_method;
20
21 h_rate_source_rule varchar2(10);
22
23 CURSOR LIFE_DEF IS
24 select nvl(life_in_months,0), nvl(minimum_life_in_months,0),
25 subcomponent_life_rule
26 from fa_category_book_defaults
27 where book_type_code = X_book
28 and category_id = X_cat_id
29 and X_dpis
30 between start_dpis
31 and nvl(end_dpis,add_months(sysdate,1200));
32
33 h_cat_bk_lim number;
34 h_min_life_in_months number;
35 h_sub_life_rule varchar2(13);
36
37 CURSOR LIFE1_DEF IS
38 select nvl(life_in_months,0), nvl(life_in_months,0),
39 prorate_date
40 from fa_books
41 where book_type_code = X_book
42 and asset_id = X_parent_asset_id
43 and date_ineffective is null;
44
45 h_lim number;
46 h_parent_life number;
47 h_parent_prorate_date date;
48
49 CURSOR FY_DEF IS
50 select round
51 (nvl(sum
52 (decode (bc.deprn_allocation_code,'E',
53 1/ct.number_per_fiscal_year,
54 (cp.end_date + 1 - cp.start_date) /
55 (fy.end_date + 1 - fy.start_date))),0) * 12, 0)
56 from fa_calendar_periods cp,
57 fa_calendar_types ct,
58 fa_book_controls bc,
59 fa_fiscal_year fy
60 where bc.book_type_code = X_book and
61 bc.date_ineffective is null and
62 ct.calendar_type = bc.prorate_calendar and
63 ct.fiscal_year_name = bc.fiscal_year_name
64 and cp.calendar_type = ct.calendar_type and
65 ( (cp.start_date >= h_parent_prorate_date and
66 cp.end_date <= h_prorate_date) )
67 and fy.fiscal_year_name = bc.fiscal_year_name
68 and fy.start_date <= cp.start_date
69 and fy.end_date >= cp.end_date;
70
71
72 -- excluded due to bug 3872361
73 /*
74 or
75 (cp.start_date <= h_parent_prorate_date
76 and cp.end_date >= h_parent_prorate_date
77 and cp.start_date <= h_prorate_date
78 and cp.end_date <= h_prorate_date)
79 */
80
81 h_fy number;
82 h_new_life number;
83
84 BEGIN
85
86
87 OPEN RATE_DEF;
88 FETCH RATE_DEF INTO
89 h_rate_source_rule;
90
91 if (RATE_DEF%NOTFOUND) then
92 CLOSE RATE_DEF;
93
94 FA_STANDARD_PKG.RAISE_ERROR(
95 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
96 CALLING_FN => X_Calling_Fn,
97 NAME => 'FA_SHARED_OBJECT_NOT_DEF',
98 TOKEN1 => 'OBJECT',
99 VALUE1 => 'Method',
100 p_log_level_rec => p_log_level_rec);
101 end if;
102 CLOSE RATE_DEF;
103
104 if (h_rate_source_rule in ('FLAT', 'PRODUCTION')) then
105
106 FA_STANDARD_PKG.RAISE_ERROR(
107 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
108 CALLING_FN => X_Calling_Fn,
109 NAME => 'FA_MTH_LFR_INCOMPAT',
110 p_log_level_rec => p_log_level_rec);
111 end if;
112
113 -- For depreciation books that have future dated periods,
114 -- we use 100 years past the end_dpis if it's null for comparisions;
115
116 OPEN LIFE_DEF;
117 FETCH LIFE_DEF INTO
118 h_cat_bk_lim,
119 h_min_life_in_months,
120 h_sub_life_rule;
121
122 if (LIFE_DEF%NOTFOUND) then
123 CLOSE LIFE_DEF;
124
125 FA_STANDARD_PKG.RAISE_ERROR(
126 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
127 CALLING_FN => X_Calling_Fn,
128 NAME => 'FA_SHARED_OBJECT_NOT_DEF',
129 TOKEN1 => 'OBJECT',
130 VALUE1 => 'Subcomponent Life Rule',
131 p_log_level_rec => p_log_level_rec);
132 end if;
133 CLOSE LIFE_DEF;
134
135 OPEN LIFE1_DEF;
136 FETCH LIFE1_DEF INTO
137 h_lim,
138 h_parent_life,
139 h_parent_prorate_date;
140
141 if (LIFE1_DEF%NOTFOUND) then
142 CLOSE LIFE1_DEF;
143
144 FA_STANDARD_PKG.RAISE_ERROR(
145 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
146 CALLING_FN => X_Calling_Fn,
147 NAME => 'FA_PARENT_BKS_NOT_EXIST',
148 p_log_level_rec => p_log_level_rec);
149 end if;
150 CLOSE LIFE1_DEF;
151
152 if (h_sub_life_rule = 'SAME LIFE') then
153 if (h_lim = 0) then
154 h_lim := h_cat_bk_lim;
155 FA_STANDARD_PKG.RAISE_ERROR(
156 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
157 CALLING_FN => X_Calling_Fn,
158 NAME => 'FA_PARENT_LIFE_NOT_SETUP',
159 p_log_level_rec => p_log_level_rec);
160 end if;
161 elsif (h_sub_life_rule = 'SAME END DATE') then
162 if (h_parent_life = 0) then
163 h_lim := h_cat_bk_lim;
164 FA_STANDARD_PKG.RAISE_ERROR(
165 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
166 CALLING_FN => X_Calling_Fn,
167 NAME => 'FA_PARENT_LIFE_NOT_SETUP',
168 p_log_level_rec => p_log_level_rec);
169 else
170 h_fy := 0;
171
172 OPEN FY_DEF;
173 FETCH FY_DEF INTO
174 h_fy;
175
176 if (FY_DEF%NOTFOUND) then
177 CLOSE FY_DEF;
178 FA_STANDARD_PKG.RAISE_ERROR(
179 CALLED_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life',
180 CALLING_FN => X_Calling_Fn,
181 NAME => 'FA_SHARED_OBJECT_NOT_DEF',
182 TOKEN1 => 'OBJECT',
183 VALUE1 => 'Fiscal Year or Calendar Period',
184 p_log_level_rec => p_log_level_rec);
185 end if;
186 CLOSE FY_DEF;
187
188 -- If the parent asset is fully reserved i.e it's remaining life as
189 -- computed here is <= 0 then the life of the subcomponent asset
190 -- is one month.
191
192 if (h_fy >= h_parent_life) then
193 -- If the parent asset is fully rsvd
194 h_lim := 1;
195 else
196 -- The life is the lesser of the Category's life and parent's remaining life
197 -- BUG# 1898874 - correcting the check to use h_cat_bk_lim instead of h_lim
198 -- so that this is actually what happens. Previously, the same end date
199 -- was always implemented no matter how much the child's life was inflated.
200 -- bridgway 07/24/01
201
202 if ((h_parent_life - h_fy) < h_cat_bk_lim) then
203 h_lim := h_parent_life - h_fy;
204 else
205 h_lim := h_cat_bk_lim;
206 end if;
207
208 if (h_lim < h_min_life_in_months) then
209 if (h_cat_bk_lim < h_min_life_in_months) then
210 h_lim := h_min_life_in_months;
211 else
212 h_lim := h_cat_bk_lim;
213 end if;
214 end if;
215
216 end if; -- If the parent asset is fully reserved
217
218
219 end if; -- If parent's life is not setup
220
221 else
222
223 h_lim := h_cat_bk_lim;
224
225 end if;
226
227 -- h_new_life := 0; -- Change to h_lim to fix bug 737503
228 h_new_life := h_lim;
229
230 CHECK_LIFE(X_book,
231 X_cat_id,
232 X_dpis,
233 h_deprn_method,
234 h_rate_source_rule,
235 h_cat_bk_lim,
236 h_lim,
237 X_user_id,
238 X_curr_date,
239 h_new_life,
240 'fa_capitalize_cip_pkg1.calc_subcomp_life',
241 p_log_level_rec);
242
243 if (h_new_life <> 0) then
244 h_life := h_new_life;
245 end if;
246
247 exception
248 WHEN OTHERS THEN
249 FA_SRVR_MSG.Add_SQL_Error(
250 CALLING_FN => 'fa_capitalize_cip_pkg1.calc_subcomp_life');
251 raise;
252
253 END CALC_SUBCOMP_LIFE;
254
255
256 PROCEDURE CHECK_LIFE (X_book VARCHAR2,
257 X_cat_id NUMBER,
258 X_dpis DATE,
259 h_deprn_method VARCHAR2,
260 h_rate_source_rule VARCHAR2,
261 h_life_in_months NUMBER,
262 h_lim NUMBER,
263 X_user_id NUMBER,
264 X_curr_date DATE,
265 h_new_life IN OUT NOCOPY NUMBER,
266 X_Calling_Fn VARCHAR2,
267 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
268 )IS
269
270 CURSOR MD_DEF
271 (p_deprn_method varchar2,
272 p_lim number) IS
273 select method_id,
274 depreciate_lastyear_flag,
275 rate_source_rule,
276 deprn_basis_rule,
277 stl_method_flag,
278 exclude_salvage_value_flag
279 from fa_methods
280 where method_code = p_deprn_method
281 and nvl(life_in_months,0) = p_lim;
282
283 CURSOR CHECK_METHOD_EXISTS IS
284 select method_code, life_in_months
285 from fa_methods
286 where method_code = h_deprn_method
287 and nvl(life_in_months,0) = h_lim;
288
289 -- bug 1147151
290 CURSOR methodname IS
291 select name
292 from fa_methods
293 where method_code = h_deprn_method
294 order by created_by desc;
295
296 CURSOR C_FORMULA (p_method_id number) IS
297 SELECT formula_actual,
298 formula_displayed,
299 formula_parsed
300 FROM FA_FORMULAS
301 WHERE method_id = p_method_id;
302
303 h_method_id number;
304 h_deprn_last_year_flag varchar2(3);
305 h_rsr varchar2(10);
306 h_deprn_basis_rule varchar2(4);
307 h_rowid rowid;
308 h_dummy1 varchar2(12);
309 h_dummy2 number(4);
310 h_methodname fa_methods.name%type;
311
312 h_method_id_old number;
313 h_stl_method_flag varchar2(3);
314 h_exclude_sal_flag varchar2(3);
315
316 h_formula_actual varchar2(4000);
317 h_formula_displayed varchar2(4000);
318 h_formula_parsed varchar2(4000);
319
320 BEGIN
321
322 if not fa_cache_pkg.fazccmt
323 (X_method => h_deprn_method,
324 X_life => h_lim,
325 p_log_level_rec => p_log_level_rec) then -- method not found
326
327 if (h_rsr = 'TABLE') then
328
329 if (h_life_in_months <> 0) then
330 h_new_life := h_life_in_months;
331 else
332 FA_STANDARD_PKG.RAISE_ERROR(
333 CALLED_FN => 'fa_capitalize_cip_pkg1.check_life',
334 CALLING_FN => X_Calling_Fn,
335 NAME => 'FA_LIM_TDM_NOTDEF',
336 p_log_level_rec => p_log_level_rec);
337 end if;
338
339 else /* if not 'TABLE' */
340
341 select FA_METHODS_S.NEXTVAL
342 into h_method_id
343 from sys.dual;
344
345 -- need to derive more values to distinguish between
346 -- STL and Formula methods. Can't use cache as life
347 -- is unknown so like the function in calc engine,
348 -- we'll use cursor here, other option would be to
349 -- pass the values as parameter into this function
350 -- creating dependancies...
351
352 OPEN MD_DEF(h_deprn_method, h_life_in_months);
353 FETCH MD_DEF INTO
354 h_method_id_old,
355 h_deprn_last_year_flag,
356 h_rsr,
357 h_deprn_basis_rule,
358 h_stl_method_flag,
359 h_exclude_sal_flag;
360
361
362 if (MD_DEF%NOTFOUND) then
363 CLOSE MD_DEF;
364 FA_STANDARD_PKG.RAISE_ERROR(
365 CALLED_FN => 'fa_capitalize_cip_pkg1.check_life',
366 CALLING_FN => X_Calling_Fn,
367 NAME => 'FA_SHARED_OBJECT_NOT_DEF',
368 TOKEN1 => 'OBJECT',
369 VALUE1 => 'Method',
370 p_log_level_rec => p_log_level_rec);
371
372 else
373 CLOSE MD_DEF;
374 end if;
375
376 FA_METHODS_PKG.Insert_Row(
377 X_Rowid => h_rowid,
378 X_Method_Id => h_method_id,
379 X_Method_Code => h_deprn_method,
380 X_Life_In_Months => h_lim,
381 X_Depreciate_Lastyear_Flag => h_deprn_last_year_flag, -- 'YES',
382 X_STL_Method_Flag => h_stl_method_flag, -- 'YES',
383 X_Rate_Source_Rule => h_rsr, -- 'CALCULATED',
384 X_Deprn_Basis_Rule => h_deprn_basis_rule, -- 'COST',
388 X_Last_Updated_By => X_user_id,
385 X_Prorate_Periods_Per_Year => NULL,
386 X_Name => h_methodname,
387 X_Last_Update_Date => X_curr_date,
389 X_Created_By => X_user_id,
390 X_Creation_Date => X_curr_date,
391 X_Last_Update_Login => -1,
392 X_Attribute1 => null,
393 X_Attribute2 => null,
394 X_Attribute3 => null,
395 X_Attribute4 => null,
396 X_Attribute5 => null,
397 X_Attribute6 => null,
398 X_Attribute7 => null,
399 X_Attribute8 => null,
400 X_Attribute9 => null,
401 X_Attribute10 => null,
402 X_Attribute11 => null,
403 X_Attribute12 => null,
404 X_Attribute13 => null,
405 X_Attribute14 => null,
406 X_Attribute15 => null,
407 X_Attribute_Category_Code => null,
408 X_Exclude_Salvage_Value_Flag => h_exclude_sal_flag,
409 X_Calling_Fn => 'fa_capitalize_cip_pkg1.check_life',
410 p_log_level_rec => p_log_level_rec);
411
412 -- if formula based, we need to copy the formula too
413 if (h_rate_source_rule = 'FORMULA') then
414 OPEN C_FORMULA (p_method_id => h_method_id_old);
415 FETCH C_FORMULA
416 INTO h_formula_actual,
417 h_formula_displayed,
418 h_formula_parsed;
419
420 IF C_FORMULA%NOTFOUND then
421 CLOSE C_FORMULA;
422 FA_STANDARD_PKG.RAISE_ERROR(
423 CALLED_FN => 'fa_capitalize_cip_pkg1.check_life',
424 CALLING_FN => X_Calling_Fn,
425 NAME => 'FA_FORMULA_RATE_NO_DATA_FOUND',
426 p_log_level_rec => p_log_level_rec);
427 else
428 CLOSE C_FORMULA;
429 end if;
430
431 FA_FORMULAS_PKG.insert_row
432 (X_ROWID => h_rowid,
433 X_METHOD_ID => h_method_id,
434 X_FORMULA_ACTUAL => h_formula_actual,
435 X_FORMULA_DISPLAYED => h_formula_displayed,
436 X_FORMULA_PARSED => h_formula_parsed,
437 X_CREATION_DATE => X_curr_date,
438 X_CREATED_BY => X_user_id,
439 X_LAST_UPDATE_DATE => X_curr_date,
440 X_LAST_UPDATED_BY => X_user_id,
441 X_LAST_UPDATE_LOGIN => -1,
442 p_log_level_rec => p_log_level_rec);
443
444 end if; -- formula
445 end if; -- table based
446
447 -- Fix for bug 624113 -- default the new life in months to the remaining
448 -- life in months of parent.
449 if (h_lim <> 0) then
450 h_new_life := h_lim;
451 end if;
452
453 end if; -- method not found
454
455 exception
456 WHEN OTHERS THEN
457 FA_SRVR_MSG.Add_SQL_Error(
458 CALLING_FN => 'fa_capitalize_cip_pkg1.check_life');
459 raise;
460 END CHECK_LIFE;
461
462 END FA_CAPITALIZE_CIP_PKG1;