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