1 PACKAGE BODY FA_FIN_ADJ1_PKG as
2 /* $Header: faxfa1b.pls 120.5 2006/05/12 18:10:46 vtandon ship $ */
3
4 PROCEDURE get_deprn_info(bks_asset_id in number,
5 bks_book_type_code in varchar2,
6 bks_depreciation_check in out nocopy varchar2,
7 bks_current_period_flag in out nocopy varchar2,
8 bks_calling_fn varchar2,
9 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
10 IS
11
12 cursor deprn_check is
13 select 'Y'
14 from fa_deprn_summary ds
15 where ds.book_type_code = bks_book_type_code and
16 ds.asset_id = bks_asset_id and
17 ds.deprn_source_code = 'DEPRN' and
18 ds.deprn_amount <> 0 and
19 rownum <2;
20
21 -- Bug:5172007
22 cursor check_current_period is
23 select 'Y'
24 from fa_deprn_summary ds,
25 fa_deprn_periods dp
26 where dp.book_type_code= bks_book_type_code and
27 ds.asset_id = bks_asset_id and
28 ds.book_type_code= bks_book_type_code and
29 ds.deprn_source_code = 'BOOKS' and
30 dp.period_close_date is null and
31 ds.period_counter = dp.period_counter - 1;
32
33 BEGIN
34
35 bks_depreciation_check := 'N';
36 bks_current_period_flag := 'N';
37
38 open deprn_check;
39 fetch deprn_check into bks_depreciation_check;
40 close deprn_check;
41
42 open check_current_period;
43 fetch check_current_period into bks_current_period_flag;
44 close check_current_period;
45
46 /* exception
47 when others then
48 FA_STANDARD_PKG.RAISE_ERROR(
49 CALLED_FN => 'fa_fin_adj1_pkg.get_deprn_info',
50 CALLING_FN => bks_Calling_Fn,
51 p_log_level_rec => p_log_level_rec); */
52
53 END get_deprn_info;
54
55 -- syoung: added x_return_status.
56 procedure cal_rec_cost(
57 bks_itc_amount_id in number,
58 bks_ceiling_type in varchar2,
59 bks_ceiling_name in varchar2,
60 bks_itc_basis in number,
61 bks_cost in number,
62 bks_salvage_value in number,
63 bks_recoverable_cost in out nocopy number,
64 bks_date_placed_in_service in date,
65 x_return_status out nocopy boolean,
66 bks_calling_fn varchar2,
67 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
68 is
69 cursor no_itc_yes_ceiling is
70 select least(bks_cost - bks_salvage_value,
71 nvl(ce.limit, bks_cost - bks_salvage_value))
72 from fa_ceilings ce
73 where ce.ceiling_name = bks_ceiling_name
74 and bks_date_placed_in_service
75 between ce.start_date
76 and nvl(ce.end_date, bks_date_placed_in_service);
77
78 cursor yes_itc_no_ceiling is
79 select bks_cost - bks_salvage_value -
80 bks_itc_basis * ir.basis_reduction_rate
81 from fa_itc_rates ir
82 where ir.itc_amount_id = bks_itc_amount_id;
83
84 cursor yes_itc_yes_ceiling is
85 select least(bks_cost - bks_salvage_value -
86 bks_itc_basis * ir.basis_reduction_rate,
87 nvl(ce.limit, bks_cost - bks_salvage_value -
88 bks_itc_basis * ir.basis_reduction_rate))
89 from fa_ceilings ce, fa_itc_rates ir
90 where ir.itc_amount_id = bks_itc_amount_id
91 and ce.ceiling_name = bks_ceiling_name
92 and bks_date_placed_in_service
93 between ce.start_date
94 and nvl(ce.end_date, bks_date_placed_in_service);
95
96 calc_error exception; -- syoung: added this exception.
97
98 begin
99 if bks_itc_amount_id is null then
100 if bks_ceiling_type = 'RECOVERABLE COST CEILING' then
101 open no_itc_yes_ceiling;
102 fetch no_itc_yes_ceiling into
103 bks_recoverable_cost;
104
105 if (no_itc_yes_ceiling%notfound) then
106 close no_itc_yes_ceiling;
107 -- syoung: conditional messaging.
108 if (bks_calling_fn = 'FA_BOOKS_ADD.Default_Assets' or
109 bks_calling_fn = 'FA_BOOKS_VAL.COST' or
110 bks_calling_fn = 'FA_BOOKS_VAL.SAL_VALUE_VAL' or
111 bks_calling_fn = 'FA_BOOKS_VAL.DPIS_VAL' or
112 bks_calling_fn = 'FA_BOOKS_VAL3.CONV_CODE_VAL' or
113 bks_calling_fn = 'FA_BOOKS_VAL4.CEILING_NAME') then
114 fnd_message.set_name('OFA', 'FA_SHARED_REC_COST');
115 app_exception.raise_exception;
116 else
117 raise calc_error;
118 end if;
119 end if;
120
121 close no_itc_yes_ceiling;
122 else
123 bks_recoverable_cost := bks_cost - bks_salvage_value;
124 end if;
125 else
126 if bks_ceiling_type = 'RECOVERABLE COST CEILING' then
127 open yes_itc_yes_ceiling;
128 fetch yes_itc_yes_ceiling into
129 bks_recoverable_cost;
130
131 if (yes_itc_yes_ceiling%notfound) then
132 close yes_itc_yes_ceiling;
133 -- syoung: conditional messaging.
134 if (bks_calling_fn = 'FA_BOOKS_ADD.Default_Assets' or
135 bks_calling_fn = 'FA_BOOKS_VAL.COST' or
136 bks_calling_fn = 'FA_BOOKS_VAL.SAL_VALUE_VAL' or
137 bks_calling_fn = 'FA_BOOKS_VAL.DPIS_VAL' or
138 bks_calling_fn = 'FA_BOOKS_VAL3.CONV_CODE_VAL' or
139 bks_calling_fn = 'FA_BOOKS_VAL4.CEILING_NAME') then
140 fnd_message.set_name('OFA', 'FA_SHARED_REC_COST');
141 app_exception.raise_exception;
142 else
143 raise calc_error;
144 end if;
145 end if;
146
147 close yes_itc_yes_ceiling;
148 else
149 open yes_itc_no_ceiling;
150 fetch yes_itc_no_ceiling into
151 bks_recoverable_cost;
152
153 if (yes_itc_no_ceiling%notfound) then
154 close yes_itc_no_ceiling;
155 -- syoung: conditional messaging.
156 if (bks_calling_fn = 'FA_BOOKS_ADD.Default_Assets' or
157 bks_calling_fn = 'FA_BOOKS_VAL.COST' or
158 bks_calling_fn = 'FA_BOOKS_VAL.SAL_VALUE_VAL' or
159 bks_calling_fn = 'FA_BOOKS_VAL.DPIS_VAL' or
160 bks_calling_fn = 'FA_BOOKS_VAL3.CONV_CODE_VAL' or
161 bks_calling_fn = 'FA_BOOKS_VAL4.CEILING_NAME') then
162 fnd_message.set_name('OFA', 'FA_SHARED_REC_COST');
163 app_exception.raise_exception;
164 else
165 raise calc_error;
166 end if;
167 end if;
168
169 close yes_itc_no_ceiling;
170 end if;
171 end if;
172
173 x_return_status := true;
174 /* exception
175 when others then
176 FA_STANDARD_PKG.RAISE_ERROR(
177 CALLED_FN => 'fa_fin_adj1_pkg.cal_rec_cost',
178 CALLING_FN => bks_Calling_Fn,
179 p_log_level_rec => p_log_level_rec); */
180 --syoung: added exceptions
181 exception
182 when calc_error then
183 FA_SRVR_MSG.Add_Message(
184 CALLING_FN => 'FA_FIN_ADJ1_PKG.Cal_Rec_Cost',
185 NAME => 'FA_SHARED_REC_COST',
186 p_log_level_rec => p_log_level_rec);
187 x_return_status := false;
188 when others then
189 if not (bks_calling_fn = 'FA_BOOKS_ADD.Default_Assets' or
190 bks_calling_fn = 'FA_BOOKS_VAL.COST' or
191 bks_calling_fn = 'FA_BOOKS_VAL.SAL_VALUE_VAL' or
192 bks_calling_fn = 'FA_BOOKS_VAL.DPIS_VAL' or
193 bks_calling_fn = 'FA_BOOKS_VAL3.CONV_CODE_VAL' or
194 bks_calling_fn = 'FA_BOOKS_VAL4.CEILING_NAME') then
195 FA_SRVR_MSG.Add_SQL_Error(
196 CALLING_FN => 'FA_FIN_ADJ1_PKG.Cal_Rec_Cost'
197 ,p_log_level_rec => p_log_level_rec);
198 x_return_status := false;
199 end if;
200
201 end cal_rec_cost;
202
203 -- syoung: included x_return_status.
204 procedure update_and_check_amts(
205 bks_depreciation_check in varchar2,
206 bks_current_period_flag in varchar2,
207 bks_recoverable_cost in number,
208 bks_deprn_reserve in number,
209 bks_ytd_deprn in number,
210 bks_cost in number,
211 bks_salvage_value in number,
212 x_return_status out nocopy boolean,
213 bks_calling_fn varchar2,
214 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
215 is
216 amt_error exception; -- syoung.
217 begin
218 if bks_depreciation_check <> 'Y' then
219 if (bks_recoverable_cost >= 0) or
220 (bks_recoverable_cost < 0 and
221 bks_deprn_reserve <= 0 and
222 bks_ytd_deprn <= 0) then
223 if (bks_recoverable_cost <= 0) or
224 (bks_recoverable_cost > 0 and
225 bks_deprn_reserve >= 0 and
226 bks_ytd_deprn >= 0) then
227 if (abs(bks_recoverable_cost) >= abs(bks_deprn_reserve)) or
228 (bks_current_period_flag = 'N') then
229
230 /* mwoodwar 01/18/00. CRL stub call - don't do the check for CRL. */
231 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
232 null;
233 else
234
235 if (bks_cost < 0 and bks_salvage_value <> 0) then
236 -- syoung: conditional error messaging.
237 -- add more conditions later when using mass reclass preview
238 -- or for other trx engine modules.
239 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
240 FA_SRVR_MSG.Add_Message(
241 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts',
242 NAME => 'FA_BOOK_NEG_SALVAGE_VALUE',
243 p_log_level_rec => p_log_level_rec);
244 raise amt_error;
245 else
246 fnd_message.set_name('OFA', 'FA_BOOK_NEG_SALVAGE_VALUE');
247 app_exception.raise_exception;
248 end if;
249 else
250 if bks_cost > 0 and bks_salvage_value < 0 then
251 -- syoung: conditional error messaging.
252 -- add more conditions later when using mass reclass preview
253 -- or for other trx engine modules.
254 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
255 FA_SRVR_MSG.Add_Message(
256 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts',
257 NAME => 'FA_BOOK_POS_SALVAGE_VALUE',
258 p_log_level_rec => p_log_level_rec);
259 raise amt_error;
260 else
261 fnd_message.set_name('OFA', 'FA_BOOK_POS_SALVAGE_VALUE');
262 app_exception.raise_exception;
263 end if;
264 end if;
265 end if;
266
267 /* End of CRL condition. */
268 end if;
269
270 else
271 -- syoung: conditional error messaging.
272 -- add more conditions later when using mass reclass preview
273 -- or for other trx engine modules.
274 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
275 FA_SRVR_MSG.Add_Message(
276 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts',
277 NAME => 'FA_BOOK_INVALID_RESERVE',
278 p_log_level_rec => p_log_level_rec);
279 raise amt_error;
280 else
281 fnd_message.set_name('OFA', 'FA_BOOK_INVALID_RESERVE');
282 app_exception.raise_exception;
283 end if;
284 end if;
285 else
286 -- syoung: conditional messaging.
287 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
288 FA_SRVR_MSG.Add_Message(
289 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts',
290 NAME => 'FA_BOOK_ALL_POSITIVE',
291 p_log_level_rec => p_log_level_rec);
292 raise amt_error;
293 else
294 fnd_message.set_name('OFA', 'FA_BOOK_ALL_POSITIVE');
295 app_exception.raise_exception;
296 end if;
297 end if;
298 else
299 -- syoung: conditional messaging.
300 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
301 FA_SRVR_MSG.Add_Message(
302 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts',
303 NAME => 'FA_BOOK_ALL_NEGATIVE',
304 p_log_level_rec => p_log_level_rec);
305 raise amt_error;
306 else
307 fnd_message.set_name('OFA', 'FA_BOOK_ALL_NEGATIVE');
308 app_exception.raise_exception;
309 end if;
310 end if;
311 end if;
312
313 x_return_status := true;
314 /* exception
315 when others then
316 FA_STANDARD_PKG.RAISE_ERROR(
317 CALLED_FN => 'fa_fin_adj1_pkg.update_and_check_amts',
318 CALLING_FN => bks_Calling_Fn,
319 p_log_level_rec => p_log_level_rec); */
320 exception
321 when amt_error then
322 x_return_status := false;
323 when others then
324 if (bks_calling_fn = 'FA_REC_PVT_PKG5.Set_Redef_Transaction') then
325 FA_SRVR_MSG.Add_SQL_Error(
326 CALLING_FN => 'FA_FIN_ADJ1.Update_And_Check_Amts'
327 ,p_log_level_rec => p_log_level_rec);
328 x_return_status := false;
329 end if;
330
331 end update_and_check_amts;
332
333 procedure chk_val_before_commit(
334 bks_cost in number,
335 bks_pc_fully_retired in number,
336 bks_pc_fully_reserved in out nocopy number,
337 bks_depreciation_check in varchar2,
338 bks_current_period_flag in varchar2,
339 bks_recoverable_cost in number,
340 bks_deprn_reserve in number,
341 bks_ytd_deprn in number,
342 bks_salvage_value in number,
343 bks_book_type_code in varchar2,
344 bks_date_placed_in_service in date,
345 bks_rate_source_rule in varchar2,
346 bks_deprn_method_code in varchar2,
347 bks_life_years in number,
348 bks_life_months in number,
349 bks_basic_rate in number,
350 bks_adjusted_rate in number,
351 bks_itc_amount_id in number,
352 bks_ceiling_type in varchar2,
353 bks_depreciate_flag in varchar2,
354 bks_calling_fn varchar2,
355 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
356 is
357 /* BUG# 1487644
358 this doesn't work when the entire fiscal year is not defined
359 removing joins to deprn periods and going strictly against FY
360 -- bridgway 11/14/00
361 */
362 cursor same_fiscal_year is
363 select 'Y'
364 from fa_book_controls bc,
365 fa_fiscal_year fy
366 where bc.book_type_code = bks_book_type_code
367 and bc.fiscal_year_name = fy.fiscal_year_name
368 and bks_date_placed_in_service between
369 fy.start_date and fy.end_date
370 and fy.fiscal_year = bc.current_fiscal_year;
371
372 CURSOR cbachand_part_ret IS
373 SELECT 'Y'
374 FROM fa_transaction_headers th,
375 fa_book_controls bc,
376 fa_deprn_periods dp
377 WHERE th.book_type_code = bks_book_type_code
378 AND th.book_type_code = bc.book_type_code
379 AND bc.book_type_code = dp.book_type_code
380 AND th.transaction_type_code = 'PARTIAL RETIREMENT'
381 AND dp.period_counter = bc.last_period_counter +1
382 AND th.transaction_date_entered between
383 dp.calendar_period_open_date and nvl(dp.calendar_period_close_date,
384 sysdate);
385
386 cursor check_rate is
387 select 'Y'
388 from fa_flat_rates fr, fa_methods mth
389 where mth.method_code = bks_deprn_method_code
390 and mth.life_in_months is null
391 and mth.method_id = fr.method_id
392 and fr.basic_rate = bks_basic_rate
393 and fr.adjusted_rate = bks_adjusted_rate;
394
395 cursor check_life is
396 select 'Y'
397 from fa_methods mth
398 where mth.method_code = bks_deprn_method_code
399 and bks_life_years * 12 + bks_life_months = mth.life_in_months;
400
401 cursor check_deprn_flag is
402 select 'Y'
403 from fa_formulas f, fa_methods mth
404 where mth.method_code = bks_deprn_method_code
405 and mth.method_id = f.method_id (+)
406 and ((mth.rate_source_rule = 'PRODUCTION') or
407 ((mth.rate_source_rule = 'FORMULA') and
408 (instr (f.formula_actual, 'CAPACITY') <> 0)))
409 and bks_depreciate_flag = 'NO';
410
411 check_flag varchar2(3);
412 cbachand_flag varchar2(3) := 'N';
413
414 h_status boolean; -- syoung: dummy boolean.
415
416 h_formula_actual varchar2(4000);
417
418 message_name VARCHAR2(50);
419 expected_exc EXCEPTION;
420
421 begin
422
423 /* 1. Modified to call another error message routine
424 2. Added the code to check the return status of update_and_check_amts(
425 ,p_log_level_rec => p_log_level_rec)
426 3. Added the exception block which was commented out. - aling
427 */
428
429 if bks_cost is null then
430 message_name := 'FA_BOOK_NO_FINANCIAL_INFO';
431 raise expected_exc;
432 --fnd_message.set_name('OFA', 'FA_BOOK_NO_FINANCIAL_INFO');
433 --app_exception.raise_exception;
434 end if;
435
436 if bks_pc_fully_retired is not null then
437 message_name := 'FA_SHARED_RETIRED_ASSET';
438 raise expected_exc;
439 --fnd_message.set_name('OFA', 'FA_SHARED_RETIRED_ASSET');
440 --app_exception.raise_exception;
441 end if;
442
443 if bks_pc_fully_reserved is not null then
444 bks_pc_fully_reserved := null;
445 end if;
446
447 update_and_check_amts(
448 bks_depreciation_check,
449 bks_current_period_flag,
450 bks_recoverable_cost,
451 bks_deprn_reserve,
452 bks_ytd_deprn,
453 bks_cost,
454 bks_salvage_value,
455 h_status, -- syoung: added this local.
456 'fa_fin_adj1_pkg.chk_val_before_commit',
457 p_log_level_rec);
458
459 if not h_status then
460 message_name := '';
461 raise expected_exc;
462 end if;
463
464 if bks_depreciation_check <> 'Y' then
465 open same_fiscal_year;
466 fetch same_fiscal_year into check_flag;
467
468 if (same_fiscal_year%notfound) then
469 if abs(bks_ytd_deprn) > abs(bks_deprn_reserve) then
470 message_name := 'FA_BOOK_YTD_EXCEED_RSV';
471 raise expected_exc;
472 --fnd_message.set_name('OFA', 'FA_BOOK_YTD_EXCEED_RSV');
473 --app_exception.raise_exception;
474 end if;
475 else
476 if bks_ytd_deprn <> bks_deprn_reserve then
477
478 /* For Bug 839397. YTD deprn and deprn reserve may legitimately be different if
479 a partial retirement has occured before depreciation runs. This can occur when
480 the method is Following Month and the partial retirement occurs in the period
481 when depreciation is set to begin. cbachand 8/12/99 */
482
483 OPEN cbachand_part_ret;
484 FETCH cbachand_part_ret INTO cbachand_flag;
485 IF cbachand_flag <> 'Y' THEN
486 message_name := 'FA_BOOK_RSV_EQL_YTD';
487 raise expected_exc;
488 END IF;
489 CLOSE cbachand_part_ret;
490 --fnd_message.set_name('OFA', 'FA_BOOK_RSV_EQL_YTD');
491 --app_exception.raise_exception;
492 end if;
493 end if;
494
495 close same_fiscal_year;
496 end if;
497
498 if bks_rate_source_rule = 'FLAT' then
499 open check_rate;
500 fetch check_rate into check_flag;
501
502 if (check_rate%notfound) then
503 close check_rate;
504 message_name := 'FA_SHARED_INVALID_METHOD_RATE';
505 raise expected_exc;
506 --fnd_message.set_name('OFA', 'FA_SHARED_INVALID_METHOD_RATE');
507 --app_exception.raise_exception;
508 end if;
509
510 close check_rate;
511 elsif bks_rate_source_rule = 'FORMULA' then
512
513 SELECT f.formula_actual
514 INTO h_formula_actual
515 FROM fa_formulas f, fa_methods m
516 WHERE m.method_id = f.method_id
517 AND m.method_code = bks_deprn_method_code
518 AND rownum = 1
519 ORDER BY m.method_id;
520
521 if instr (h_formula_actual, 'CAPACITY') = 0 then
522 open check_life;
523 fetch check_life into check_flag;
524
525 if (check_life%notfound) then
526 close check_life;
527 message_name := 'FA_SHARED_INVALID_METHOD_LIFE';
528 raise expected_exc;
529 --fnd_message.set_name('OFA', 'FA_SHARED_INVALID_METHOD_LIFE');
530 --app_exception.raise_exception;
531 end if;
532 end if;
533
534 else
535 if bks_rate_source_rule <> 'PRODUCTION' then
536 open check_life;
537 fetch check_life into check_flag;
538
539 if (check_life%notfound) then
540 close check_life;
541 message_name := 'FA_SHARED_INVALID_METHOD_LIFE';
542 raise expected_exc;
543 --fnd_message.set_name('OFA', 'FA_SHARED_INVALID_METHOD_LIFE');
544 --app_exception.raise_exception;
545 end if;
546
547 close check_life;
548 end if;
549 end if;
550
551 if bks_itc_amount_id is not null and
552 bks_ceiling_type = 'RECOVERABLE COST CEILING' then
553 message_name := 'FA_BOOK_CANT_ITC_AND_COST_CEIL';
554 raise expected_exc;
555 --fnd_message.set_name('OFA', 'FA_BOOK_CANT_ITC_AND_COST_CEIL');
556 --app_exception.raise_exception;
557 end if;
558
559 check_flag := 'N';
560
561 open check_deprn_flag;
562 fetch check_deprn_flag into check_flag;
563
564 if check_flag = 'Y' then
565 close check_deprn_flag;
566 message_name := 'FA_BOOK_INVALID_DEPRN_FLAG';
567 raise expected_exc;
568 --fnd_message.set_name('OFA', 'FA_BOOK_INVALID_DEPRN_FLAG');
569 --app_exception.raise_exception;
570 end if;
571
572 close check_deprn_flag;
573
574 EXCEPTION
575 when expected_exc then
576 FA_SRVR_MSG.Add_Message(
577 CALLING_FN => 'FA_FIN_ADJ1_PKG.chk_val_before_commit',
578 NAME => message_name,
579 p_log_level_rec => p_log_level_rec);
580 raise;
581 when others then
582 FA_SRVR_MSG.Add_Message(
583 CALLING_FN => 'FA_FIN_ADJ1_PKG.chk_val_before_commit',
584 NAME => 'FA_SHARED_INVALID_METHOD_LIFE' ,
585 p_log_level_rec => p_log_level_rec);
586 raise;
587
588 /* exception
589 when others then
590 FA_STANDARD_PKG.RAISE_ERROR(
591 CALLED_FN => 'fa_fin_adj1_pkg.chk_val_before_commit',
592 CALLING_FN => bks_Calling_Fn,
593 p_log_level_rec => p_log_level_rec); */
594
595 end chk_val_before_commit;
596
597
598 procedure check_changes_before_commit(
599 bks_row_id in varchar2,
600 bks_amortize_flag in varchar2,
601 bks_prorate_convention_code in varchar2,
602 bks_orig_deprn_reserve in number,
603 bks_orig_reval_reserve in number,
604 bks_orig_ytd_deprn in number,
605 bks_cost in number,
606 bks_recoverable_cost in number,
607 bks_adjusted_rec_cost in number,
608 bks_date_placed_in_service in date,
609 bks_deprn_method_code in varchar2,
610 bks_life_years in number,
611 bks_life_months in number,
612 bks_salvage_value in number,
613 bks_basic_rate_dsp in number,
614 bks_adjusted_rate_dsp in number,
615 bks_bonus_rule in varchar2,
616 bks_ceiling_name in varchar2,
617 bks_production_capacity in number,
618 bks_deprn_reserve in number,
619 bks_ytd_deprn in number,
620 bks_reval_reserve in number,
621 bks_adjusted_cost in number,
622 bks_orig_adjusted_cost in number,
623 bks_reval_ceiling in number,
624 bks_depreciate_flag in varchar2,
625 bks_unit_of_measure in varchar2,
626 bks_global_attribute1 in varchar2,
627 bks_global_attribute2 in varchar2,
628 bks_global_attribute3 in varchar2,
629 bks_global_attribute4 in varchar2,
630 bks_global_attribute5 in varchar2,
631 bks_global_attribute6 in varchar2,
632 bks_global_attribute7 in varchar2,
633 bks_global_attribute8 in varchar2,
634 bks_global_attribute9 in varchar2,
635 bks_global_attribute10 in varchar2,
636 bks_global_attribute11 in varchar2,
637 bks_global_attribute12 in varchar2,
638 bks_global_attribute13 in varchar2,
639 bks_global_attribute14 in varchar2,
640 bks_global_attribute15 in varchar2,
641 bks_global_attribute16 in varchar2,
642 bks_global_attribute17 in varchar2,
643 bks_global_attribute18 in varchar2,
644 bks_global_attribute19 in varchar2,
645 bks_global_attribute20 in varchar2,
646 bks_global_attribute_category in varchar2,
647 bks_adjustment_required_status in out nocopy varchar2,
648 bks_calling_fn varchar2,
649 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
650 is
651 cursor exist_to_book_cur is
652 select 'Y'
653 from fa_books bk
654 where bk.rowid = bks_row_id;
655
656 cursor pro_conv_cur is
657 select 'Y'
658 from fa_books bk
659 where bk.prorate_convention_code = bks_prorate_convention_code
660 and bk.rowid = bks_row_id;
661
662 cursor no_changes_made is
663 select 'Y'
664 from fa_books bk
665 where bk.rowid = bks_row_id
666 and bk.cost = bks_cost
667 and bk.recoverable_cost = bks_recoverable_cost
668 and bk.date_placed_in_service = bks_date_placed_in_service
669 and bk.deprn_method_code = bks_deprn_method_code
670 and nvl(bk.life_in_months, 99999) =
671 nvl(bks_life_years * 12 + bks_life_months, 99999)
672 and bk.prorate_convention_code = bks_prorate_convention_code
673 and bk.salvage_value = bks_salvage_value
674 /* BUG# 1514366
675 the rates are already passes in decimal format - no need to convert
676 also removing the global attributes from this statement as they have
677 not financial impact.
678 -- bridgway 11/26/00
679
680 and nvl(bk.basic_rate, 99999) = nvl(bks_basic_rate_dsp/100, 99999);
681 and nvl(bk.adjusted_rate, 99999) = nvl(bks_adjusted_rate_dsp/100, 99999);
682 */
683 and nvl(bk.basic_rate, 99999) = nvl(bks_basic_rate_dsp, 99999)
684 and nvl(bk.adjusted_rate, 99999) = nvl(bks_adjusted_rate_dsp, 99999)
685 and nvl(bk.bonus_rule, 'NULL') = nvl(bks_bonus_rule, 'NULL')
686 and nvl(bk.ceiling_name, 'NULL') = nvl(bks_ceiling_name, 'NULL')
687 and nvl(bk.production_capacity, 99999) =
688 nvl(bks_production_capacity, 99999)
689 and bks_deprn_reserve = bks_orig_deprn_reserve
690 and bks_ytd_deprn = bks_orig_ytd_deprn
691 and nvl(bks_reval_reserve, 0) = nvl(bks_orig_reval_reserve, 0)
692 and bks_adjusted_cost = bks_orig_adjusted_cost
693 and nvl(bks_adjusted_rec_cost,0) = nvl(bk.adjusted_recoverable_cost,0)
694 and nvl(bk.reval_ceiling, 0) = nvl(bks_reval_ceiling, 0)
695 and nvl(bk.global_attribute1,'NULL') = nvl(bks_global_attribute1,'NULL')
696 and nvl(bk.global_attribute2,'NULL') = nvl(bks_global_attribute2,'NULL')
697 and nvl(bk.global_attribute3,'NULL') = nvl(bks_global_attribute3,'NULL')
698 and nvl(bk.global_attribute4,'NULL') = nvl(bks_global_attribute4,'NULL')
699 and nvl(bk.global_attribute5,'NULL') = nvl(bks_global_attribute5,'NULL')
700 and nvl(bk.global_attribute6,'NULL') = nvl(bks_global_attribute6,'NULL')
701 and nvl(bk.global_attribute7,'NULL') = nvl(bks_global_attribute7,'NULL')
702 and nvl(bk.global_attribute8,'NULL') = nvl(bks_global_attribute8,'NULL')
703 and nvl(bk.global_attribute9,'NULL') = nvl(bks_global_attribute9,'NULL')
704 and nvl(bk.global_attribute10,'NULL') = nvl(bks_global_attribute10,'NULL')
705 and nvl(bk.global_attribute11,'NULL') = nvl(bks_global_attribute11,'NULL')
706 and nvl(bk.global_attribute12,'NULL') = nvl(bks_global_attribute12,'NULL')
707 and nvl(bk.global_attribute13,'NULL') = nvl(bks_global_attribute13,'NULL')
708 and nvl(bk.global_attribute14,'NULL') = nvl(bks_global_attribute14,'NULL')
709 and nvl(bk.global_attribute15,'NULL') = nvl(bks_global_attribute15,'NULL')
710 and nvl(bk.global_attribute16,'NULL') = nvl(bks_global_attribute16,'NULL')
711 and nvl(bk.global_attribute17,'NULL') = nvl(bks_global_attribute17,'NULL')
712 and nvl(bk.global_attribute18,'NULL') = nvl(bks_global_attribute18,'NULL')
713 and nvl(bk.global_attribute19,'NULL') = nvl(bks_global_attribute19,'NULL')
714 and nvl(bk.global_attribute20,'NULL') = nvl(bks_global_attribute20,'NULL');
715
716 cursor no_changes_to_dep_flag_uom is
717 select 'Y'
718 from fa_books bk
719 where bk.rowid = bks_row_id
720 and nvl(bk.unit_of_measure, 99999) = nvl(bks_unit_of_measure, 99999);
721
722 cursor no_changes_to_dep_flag is
723 select 'Y'
724 from fa_books bk
725 where bk.rowid = bks_row_id
726 and bk.depreciate_flag = bks_depreciate_flag;
727
728 cursor adjustment_reqd_flag is
729 select 'Y'
730 from fa_books bk,
731 fa_transaction_headers th,
732 fa_deprn_summary ds
733 where bk.rowid = bks_row_id
734 and bks_depreciate_flag = 'YES'
735 and bk.depreciate_flag = 'NO'
736 and bk.transaction_header_id_in = th.transaction_header_id
737 and th.transaction_type_code = 'ADDITION'
738 and th.book_type_code = bk.book_type_code
739 and ds.asset_id = bk.asset_id
740 and ds.deprn_reserve = 0
741 and ds.book_type_code = bk.book_type_code
742 and ds.deprn_source_code = 'BOOKS';
743
744
745 exist_to_book_flag varchar(2) := 'N';
746 check_flag varchar(2);
747 no_changes_made_flag varchar(2) := 'N';
748
749 begin
750 open exist_to_book_cur;
751 fetch exist_to_book_cur into exist_to_book_flag;
752 close exist_to_book_cur;
753
754
755 if exist_to_book_flag = 'Y' then
756 /* if bks_amortize_flag = 'YES' then
757 open pro_conv_cur;
758 fetch pro_conv_cur into check_flag;
759
760 if (pro_conv_cur%notfound) then
761 close pro_conv_cur;
762 fnd_message.set_name('OFA', 'FA_CANNOT_AMORTIZE_PRORATE_CHE');
763 app_exception.raise_exception;
764 end if;
765
766 close pro_conv_cur;
767 end if; */
768
769 open no_changes_made;
770 fetch no_changes_made into no_changes_made_flag;
771 close no_changes_made;
772
773 if no_changes_made_flag = 'Y' then
774 open no_changes_to_dep_flag_uom;
775 fetch no_changes_to_dep_flag_uom into check_flag;
776
777 if (no_changes_to_dep_flag_uom%found) then
778 close no_changes_to_dep_flag_uom;
779 open no_changes_to_dep_flag; -- fix for bug 563327
780 fetch no_changes_to_dep_flag into check_flag;
781 if (no_changes_to_dep_flag%found) then
782 close no_changes_to_dep_flag;
783 fnd_message.set_name('OFA', 'FA_SHARED_NO_CHANGES_TO_COMMIT');
784 app_exception.raise_exception;
785 else
786 open adjustment_reqd_flag;
787 fetch adjustment_reqd_flag into check_flag;
788 if (adjustment_reqd_flag%found) then
789 bks_adjustment_required_status := 'ADD';
790 end if;
791 close adjustment_reqd_flag;
792 end if;
793
794 close no_changes_to_dep_flag;
795 else
796 close no_changes_to_dep_flag_uom;
797 end if; -- fix for bug 563327
798 else
799 open no_changes_to_dep_flag;
800 fetch no_changes_to_dep_flag into check_flag;
801
802 if (no_changes_to_dep_flag%notfound) then
803 close no_changes_to_dep_flag;
804 fnd_message.set_name('OFA', 'FA_BK_NO_MULTIPLE_CHANGES');
805 app_exception.raise_exception;
806 end if;
807
808 close no_changes_to_dep_flag;
809 end if;
810
811 end if;
812
813 /* exception
814 when others then
815 FA_STANDARD_PKG.RAISE_ERROR(
816 CALLED_FN => 'fa_fin_adj1_pkg.check_changes_before_commit',
817 CALLING_FN => bks_Calling_Fn,
818 p_log_level_rec => p_log_level_rec); */
819 end check_changes_before_commit;
820
821 END FA_FIN_ADJ1_PKG;
[Home] [Help]