[Home] [Help]
PACKAGE BODY: APPS.FA_JP_TAX_EXTN_PVT
Source
1 PACKAGE BODY fa_jp_tax_extn_pvt
2 /* $Header: FAVJPEXTB.pls 120.9 2011/01/05 06:00:19 mswetha noship $ */
3 AS
4 g_print_debug BOOLEAN := fa_cache_pkg.fa_print_debug;
5
6 TYPE tp_nbv IS RECORD (
7 period VARCHAR2 (30)
8 , nbv_value NUMBER
9 , asset_id NUMBER
10 , deprn_value NUMBER
11 , new_depreciation NUMBER
12 , acc_deprn NUMBER
13 , period_cntr NUMBER
14 );
15
16 TYPE tp_nbv_typ IS TABLE OF tp_nbv
17 INDEX BY BINARY_INTEGER;
18
19 -- Declaration of global constants
20 gn_user_id NUMBER := fnd_global.user_id;
21 gn_login_id NUMBER := fnd_global.login_id;
22 gn_deprn NUMBER := 0;
23 gn_acc_deprn NUMBER := 0;
24 gn_normal_status CONSTANT NUMBER := 0;
25 gn_warning_status CONSTANT NUMBER := 1;
26 gn_error_status CONSTANT NUMBER := 2;
27 gn_test NUMBER;
28
29 PROCEDURE insert_fa_whatif_itf (
30 x_errbuf OUT NOCOPY VARCHAR2
31 , x_retcode OUT NOCOPY NUMBER
32 , p_request_id IN NUMBER
33 , p_checkbox_check IN VARCHAR2
34 , p_book_type_code IN fa_books.book_type_code%TYPE
35 , p_number_of_periods IN NUMBER
36 , p_asset_id IN fa_books.asset_id%TYPE
37 , p_period_name IN fa_deprn_periods.period_name%TYPE
38 , p_first_begin_prd IN fa_deprn_periods.period_name%TYPE
39 , p_dep_amt IN NUMBER
40 , p_dep_amt_annual IN NUMBER
41 , p_date_placed_in_service IN fa_books.date_placed_in_service%TYPE
42 , p_life_in_months IN fa_books.life_in_months%TYPE
43 , p_original_cost IN fa_books.original_cost%TYPE
44 , p_asset_number IN fa_additions_b.asset_number%TYPE
45 , p_description IN fa_additions.description%TYPE
46 , p_tag_number IN fa_additions_b.tag_number%TYPE
47 , p_serial_number IN fa_additions_b.serial_number%TYPE
48 , p_location IN fa_locations_kfv.concatenated_segments%TYPE
49 , p_expense_account IN gl_code_combinations_kfv.concatenated_segments%TYPE
50 , p_round_value IN NUMBER
51 , p_deprn_value IN NUMBER
52 , p_flag IN VARCHAR2
53 )
54 IS
55 CURSOR lcr_deprn_periods (
56 p_book_type_code IN fa_books.book_type_code%TYPE
57 , p_period_name IN fa_deprn_periods.period_name%TYPE
58 )
59 /***********************************************************************
60 *
61 * CURSOR
62 * lcr_deprn_periods
63 *
64 * DESCRIPTION
65 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
66 * Cursor will return the end_date
67 *
68 * PARAMETERS
69 * ==========
70 * NAME TYPE DESCRIPTION
71 * ----------------- -------- ------------------------------------------
72 * p_book_type_code
73 * p_period_name
74 * *
75 * None
76 *
77 * PREREQUISITES
78 * None
79 *
80 * CALLED BY
81 * load
82 *
83 ***********************************************************************/
84 IS
85 SELECT fcp.end_date
86 FROM fa_calendar_periods fcp
87 , fa_calendar_types fct
88 , fa_book_controls fbc
89 WHERE fbc.book_type_code = p_book_type_code
90 AND fcp.calendar_type = fct.calendar_type
91 AND fcp.calendar_type = fbc.deprn_calendar
92 AND fcp.period_name = p_period_name;
93
94 CURSOR lcr_end_date (
95 p_book_type_code IN fa_books.book_type_code%TYPE
96 , p_period_name IN fa_deprn_periods.period_name%TYPE
97 , p_counter IN NUMBER
98 )
99 /***********************************************************************
100 *
101 * CURSOR
102 * lcr_end_date
103 *
104 * DESCRIPTION
105 * Cursor lcr_end_date is a private cursor of procedure whatif_main.
106 * Cursor will return the end_date
107 *
108 * PARAMETERS
109 * ==========
110 * NAME TYPE DESCRIPTION
111 * ----------------- -------- ------------------------------------------
112 * p_book_type_code
113 * p_period_name
114 *
115 *
116 * None
117 *
118 * PREREQUISITES
119 * None
120 *
121 * CALLED BY
122 * load
123 *
124 ***********************************************************************/
125 IS
126 SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
127 FROM fa_calendar_periods fcp
128 , fa_calendar_types fct
129 , fa_book_controls fbc
130 WHERE fbc.book_type_code = p_book_type_code
131 AND fcp.calendar_type = fct.calendar_type
132 AND fcp.calendar_type = fbc.deprn_calendar
133 AND fcp.period_name = p_period_name;
134
135 CURSOR lcr_distribution (
136 p_book_type_code IN fa_books.book_type_code%TYPE
137 , p_asset_id IN fa_books.asset_id%TYPE
138 )
139 /***********************************************************************
140 *
141 * CURSOR
142 * lcr_distribution
143 *
144 * DESCRIPTION
145 * Cursor lcr_distribution is a private cursor of procedure insert_fa_whatif_itf.
146 * Cursor will return distribution records
147 *
148 * PARAMETERS
149 * ==========
150 * NAME TYPE DESCRIPTION
151 * ----------------- -------- ------------------------------------------
152 * p_book_type_code
153 * p_asset_id
154 *
155 *
156 * None
157 *
158 * PREREQUISITES
159 * None
160 *
161 * CALLED BY
162 * insert_fa_whatif_itf
163 *
164 ***********************************************************************/
165 IS
166 SELECT fdh.units_assigned
167 , papf.full_name
168 , papf.employee_number
169 FROM fa_distribution_history fdh
170 , per_all_people_f papf
171 , fa_book_controls fbc
172 WHERE fbc.book_type_code = p_book_type_code
173 AND fdh.book_type_code = fbc.distribution_source_book
174 AND fdh.asset_id = p_asset_id
175 AND fdh.assigned_to = papf.person_id(+);
176
177 CURSOR lcr_first_periods (
178 p_book_type IN fa_books.book_type_code%TYPE
179 , p_start_period IN fa_deprn_periods.period_name%TYPE
180 , p_number_of_periods IN NUMBER
181 )
182 /***********************************************************************
183 *
184 * CURSOR
185 * lcr_first_periods
186 *
187 * DESCRIPTION
188 * Cursor lcr_first_periods is a private cursor of procedure whatif_main.
189 * Cursor will return the first eligible period for a particular Book Type and Period
190 *
191 * PARAMETERS
192 * ==========
193 * NAME TYPE DESCRIPTION
194 * ----------------- -------- ------------------------------------------
195 * p_book_type_code
196 * p_period_name
197 * p_number_of_periods
198 *
199 *
200 * None
201 *
202 * PREREQUISITES
203 * None
204 *
205 * CALLED BY
206 * insert_fa_whatif_itf
207 *
208 ***********************************************************************/
209 IS
210 SELECT cp1.period_name
211 FROM fa_calendar_periods cp
212 , fa_fiscal_year fy
213 , fa_book_controls fb
214 , fa_calendar_types fc
215 , fa_calendar_periods cp1
216 WHERE cp.period_name = p_start_period
217 AND cp.calendar_type = fb.deprn_calendar
218 AND fb.book_type_code = p_book_type
219 AND cp.calendar_type = fc.calendar_type
220 AND cp.start_date >= fy.start_date
221 AND cp.end_date <= fy.end_date
222 AND fy.fiscal_year_name = fb.fiscal_year_name
223 AND cp1.period_num = 1
224 AND fb.deprn_calendar = cp1.calendar_type
225 AND cp1.start_date >= cp.start_date
226 AND ROWNUM = 1;
227
228 CURSOR lcr_periods (
229 p_book_type_code IN fa_books.book_type_code%TYPE
230 , p_end_date IN DATE
231 )
232 /***********************************************************************
233 *
234 * CURSOR
235 * lcr_periods
236 *
237 * DESCRIPTION
238 * Cursor lcr_periods is a private cursor of procedure insert_fa_whatif_itf.
239 * Cursor will return the Peiord
240 *
241 * PARAMETERS
242 * ==========
243 * NAME TYPE DESCRIPTION
244 * ----------------- -------- ------------------------------------------
245 * p_book_type_code
246 * p_end_date
247 *
248 *
249 * None
250 *
251 * PREREQUISITES
252 * None
253 *
254 * CALLED BY
255 * insert_fa_whatif_itf
256 *
257 ***********************************************************************/
258 IS
259 SELECT fcp.period_name
260 FROM fa_calendar_periods fcp
261 , fa_calendar_types fct
262 , fa_book_controls fbc
263 WHERE fbc.book_type_code = p_book_type_code
264 AND fcp.calendar_type = fct.calendar_type
265 AND fcp.calendar_type = fbc.deprn_calendar
266 AND fcp.end_date = p_end_date;
267
268 --
269 -- Record type for whatif
270 --
271 TYPE t_whatif_period_rec IS RECORD (
272 period_name fa_deprn_periods.period_name%TYPE
273 );
274
275 --
276 -- Table type for for whatif
277 --
278 TYPE t_whatif_period_tbl IS TABLE OF t_whatif_period_rec
279 INDEX BY BINARY_INTEGER;
280
281 l_start_period_date DATE;
282 l_first_period_date DATE;
283 l_date DATE;
284 l_start_date DATE;
285 l_last_date DATE;
286 l_counter NUMBER;
287 l_count NUMBER;
288 l_counter2 NUMBER;
289 l_deprn_value NUMBER;
290 l_round_value NUMBER;
291 l_sql_query VARCHAR2 (1000);
292 l_sql_counter_query VARCHAR2 (1000);
293 l_sql_query2 VARCHAR2 (1000);
294 l_first_eligible_period VARCHAR2 (15);
295 l_start_period_name VARCHAR2 (15);
296 l_period NUMBER;
297 l_twelve_count NUMBER;
298 l_greater_period fa_calendar_periods.period_name%TYPE;
299 -- As per Bug 712836
300 l_whatif_period_tbl t_whatif_period_tbl;
301 l_whatif_period_tbl2 t_whatif_period_tbl;
302 l_end_date fa_calendar_periods.end_date%TYPE;
303 h_asset_id NUMBER;
304 lr_distribution lcr_distribution%ROWTYPE;
305 BEGIN
306 l_whatif_period_tbl2.DELETE;
307 l_period := NULL;
308 l_sql_query := NULL;
309 l_sql_query2 := NULL;
310 l_counter2 := 0;
311 l_count := 0;
312 l_deprn_value := 0;
313 l_round_value := 0;
314 l_twelve_count := 1;
315
316 --**************************************
317 -- Derive First Eligible Period Name
318 --**************************************
319 OPEN lcr_first_periods (p_book_type_code
320 , p_first_begin_prd -- Period When fully reserved
321 , p_number_of_periods
322 );
323
324 FETCH lcr_first_periods
325 INTO l_first_eligible_period;
326
327 CLOSE lcr_first_periods;
328
329
330 --*****************************
331 -- Derive First eligible Date
332 --*****************************
333 OPEN lcr_deprn_periods (p_book_type_code, l_first_eligible_period);
334
335 FETCH lcr_deprn_periods
336 INTO l_date;
337
338 CLOSE lcr_deprn_periods;
339
340 --*****************************
341 -- Derive Date
342 --*****************************
343 OPEN lcr_deprn_periods (p_book_type_code, p_period_name);
344
345 FETCH lcr_deprn_periods
346 INTO l_start_period_date;
347
348 CLOSE lcr_deprn_periods;
349
350 --*****************************
351 -- Derive First Begin Period
352 --*****************************
353 OPEN lcr_deprn_periods (p_book_type_code, p_first_begin_prd);
354
355 FETCH lcr_deprn_periods
356 INTO l_first_period_date;
357
358 CLOSE lcr_deprn_periods;
359
360 --Fetch the Counter for duration Start Period to First Eligible period
361 --Starts As per Bug No. 7289893
362
363 l_sql_counter_query :=
364 'SELECT months_between(''' || l_first_period_date
365 || ''',''' || l_start_period_date
366 || ''') counter
367 FROM DUAL';
368
369 EXECUTE IMMEDIATE l_sql_counter_query
370 INTO l_count;
371
372 EXECUTE IMMEDIATE l_sql_counter_query
373 INTO l_count;
374
375 --*****************************
376 -- Derive the end date
377 --*****************************
378 l_counter2 := p_number_of_periods - l_count;
379
380 -- As per Bug 7128376
381 IF l_first_period_date > l_start_period_date
382 THEN
383 -- l_greater_period := p_period_name;
384 l_greater_period := p_first_begin_prd;
385 -- As per Bug No .7183390 (For Tax Books)
386 -- As per Bug 7128376
387 OPEN lcr_end_date
388 (p_book_type_code
389 , l_greater_period -- p_first_begin_prd -- As per Bug 712836
390 , l_counter2
391 );
392
393 FETCH lcr_end_date
394 INTO l_end_date;
395
396 CLOSE lcr_end_date;
397 ELSIF l_first_period_date <= l_start_period_date
398 THEN
399 -- l_greater_period := p_first_begin_prd;
400 l_greater_period := p_period_name;
401 -- As per Bug 7128376
402 OPEN lcr_end_date
403 (p_book_type_code
404 , l_greater_period -- p_first_begin_prd -- As per Bug 712836
405 , p_number_of_periods
406 );
407
408 FETCH lcr_end_date
409 INTO l_end_date;
410
411 CLOSE lcr_end_date;
412 -- As per Bug No .7183390 (For Tax Books)
413 END IF;
414
415
416 l_deprn_value := p_deprn_value;
417
418 --Starts As per Bug No. 7289893
419
420 l_last_date := l_first_period_date;
421
422 -- END As per Bug No. 7289893
423
424 SELECT DECODE (SIGN (l_first_period_date - l_start_period_date)
425 , -1, l_start_period_date
426 , l_first_period_date
427 )
428 INTO l_last_date
429 FROM DUAL;
430
431 SELECT DECODE (SIGN (l_first_period_date - l_start_period_date)
432 , -1, l_first_period_date
433 , l_start_period_date
434 )
435 INTO l_start_date
436 FROM DUAL;
437
438 IF p_flag = 'Y'
439 THEN
440 l_last_date := l_date;
441 END IF;
442 --l_last_date := l_date;
443
444
445 IF p_period_name <> p_first_begin_prd
446 AND p_flag = 'N'
447 THEN
448 l_sql_query :=
449 'SELECT FCP.period_name
450 FROM fa_calendar_periods FCP
451 , fa_calendar_types FCT
452 , fa_book_controls FBC
453 WHERE FBC.book_type_code = '''
454 || p_book_type_code
455 || '''
456 AND FCP.calendar_type = FCT.calendar_type
457 AND FCP.calendar_type = FBC.deprn_calendar
458 AND FCP.end_date BETWEEN '''
459 || l_start_date || ''' AND ''' || l_last_date || '''';
460
461 EXECUTE IMMEDIATE l_sql_query
462 BULK COLLECT INTO l_whatif_period_tbl;
463
464 IF l_whatif_period_tbl.COUNT = 0
465 THEN
466 fnd_message.set_name ('OFA', 'FA_NO_DATA_FOUND_ERROR');
467 x_errbuf := fnd_message.get;
468 x_retcode := gn_error_status;
469 END IF;
470
471 IF p_checkbox_check = 'N'
472 THEN
473 OPEN lcr_distribution (p_book_type_code, p_asset_id);
474
475 LOOP
476 FETCH lcr_distribution
477 INTO lr_distribution;
478
479 EXIT WHEN lcr_distribution%NOTFOUND;
480
481 -- For loop to insert 0 in the periods between the start and first eligible period.
482 FOR i IN 1 .. l_count
483 LOOP
484 INSERT INTO fa_whatif_itf
485 (request_id
486 , book_type_code
487 , asset_id
488 , period_name
489 , depreciation
490 , new_depreciation
491 , current_method
492 , current_cost
493 , current_life
494 , units
495 , employee_name
496 , employee_number
497 , created_by
498 , creation_date
499 , last_update_date
500 , last_updated_by
501 , last_update_login
502 , date_placed_in_service
503 , asset_number
504 , description
505 , tag_number
506 , serial_number
507 , LOCATION
508 , expense_acct
509 )
510 VALUES (p_request_id
511 , p_book_type_code
512 , p_asset_id
513 , l_whatif_period_tbl (i).period_name
514 , 0
515 , 0
516 , 'STL'
517 , p_original_cost
518 , p_life_in_months
519 , lr_distribution.units_assigned
520 , lr_distribution.full_name
521 , lr_distribution.employee_number
522 , gn_user_id
523 , SYSDATE
524 , SYSDATE
525 , gn_user_id
526 , gn_login_id
527 , p_date_placed_in_service
528 , p_asset_number
529 , p_description
530 , p_tag_number
531 , p_serial_number
532 , p_location
533 , p_expense_account
534 );
535
536 l_deprn_value := l_deprn_value + 0;
537
538 UPDATE fa_whatif_itf
539 SET accumulated_deprn = l_deprn_value
540 WHERE period_name = l_whatif_period_tbl (i).period_name
541 AND book_type_code = p_book_type_code
542 AND request_id = p_request_id;
543 END LOOP; -- FOR i IN 1..p_number_of_periods LOOP
544 END LOOP; -- OPEN lcr_distribution(p_book_type_code
545 ELSE -- IF p_checkbox_check IS 'N' THEN
546 FOR i IN 1 .. l_count
547 LOOP
548 l_deprn_value := l_deprn_value + 0;
549
550 UPDATE fa_whatif_itf
551 SET depreciation = 0
552 , new_depreciation = 0
553 , units = lr_distribution.units_assigned
554 , employee_name = lr_distribution.full_name
555 , employee_number = lr_distribution.employee_number
556 , accumulated_deprn = l_deprn_value --p_deprn_value
557 WHERE period_name = l_whatif_period_tbl (i).period_name
558 AND book_type_code = p_book_type_code
559 AND request_id = p_request_id
560 AND asset_id = p_asset_id;
561 /*
562
563 IF i = 61 THEN
564 l_round_value := p_round_value*60;
565
566 UPDATE fa_whatif_itf
567 SET depreciation = p_round_value*5
568 , new_depreciation = p_round_value*5
569 , units = lr_distribution.units_assigned
570 , employee_name = lr_distribution.full_name
571 , employee_number = lr_distribution.employee_number
572 , accumulated_deprn = l_deprn_value - l_round_value
573 WHERE period_name = l_whatif_period_tbl2(i).period_name
574 AND book_type_code = p_book_type_code
575 AND request_id = p_request_id;
576 END IF;
577 */
578 END LOOP;
579 END IF; -- IF p_checkbox_check IS 'N' THEN
580 END IF; -- IF p_period_name <> p_first_begin_prd THEN
581
582 l_sql_query2 :=
583 'SELECT FCP.period_name
584 FROM fa_calendar_periods FCP
585 , fa_calendar_types FCT
586 , fa_book_controls FBC
587 WHERE FBC.book_type_code = '''
588 || p_book_type_code
589 || '''
590 AND FCP.calendar_type = FCT.calendar_type
591 AND FCP.calendar_type = FBC.deprn_calendar
592 AND FCP.end_date BETWEEN '''
593 || l_date || ''' AND ''' || l_end_date || '''';
594 --l_first_period_date
595
596 EXECUTE IMMEDIATE l_sql_query2
597 BULK COLLECT INTO l_whatif_period_tbl2;
598
599 IF l_start_period_date <= l_first_period_date THEN
600 l_count := 0;
601 ELSE
602 select round(months_between(l_start_period_date, l_first_period_date),0)
603 INTO l_count
604 FROM DUAL;
605 END IF;
606
607 IF l_whatif_period_tbl2.COUNT = 0
608 THEN
609 fnd_message.set_name ('OFA', 'FA_NO_DATA_FOUND_ERROR');
610 x_errbuf := fnd_message.get;
611 x_retcode := gn_error_status;
612 END IF;
613
614 -- Get period NAme
615 OPEN lcr_periods (p_book_type_code, l_last_date);
616
617 FETCH lcr_periods
618 INTO l_start_period_name;
619
620 CLOSE lcr_periods;
621
622 l_period :=
623 chk_period (p_first_begin_prd, p_book_type_code);
624
625 IF p_checkbox_check = 'N'
626 THEN
627 OPEN lcr_distribution (p_book_type_code, p_asset_id);
628
629 LOOP
630 FETCH lcr_distribution
631 INTO lr_distribution;
632
633 EXIT WHEN lcr_distribution%NOTFOUND;
634
635 -- For loop to insert 0 in the periods between the first eligible period till the period counter ends
636 FOR i IN 1 .. l_counter2
637 LOOP
638 INSERT INTO fa_whatif_itf
639 (request_id
640 , book_type_code
641 , asset_id
642 , period_name
643 , depreciation
644 , new_depreciation
645 , current_method
646 , current_cost
647 , current_life
648 , units
649 , employee_name
650 , employee_number
651 , created_by
652 , creation_date
653 , last_update_date
654 , last_updated_by
655 , last_update_login
656 , date_placed_in_service
657 , asset_number
658 , description
659 , tag_number
660 , serial_number
661 , LOCATION
662 , expense_acct
663 )
664 VALUES (p_request_id
665 , p_book_type_code
666 , p_asset_id
667 , l_whatif_period_tbl2 (i).period_name
668 , p_dep_amt
669 , p_dep_amt
670 , 'STL'
671 , p_original_cost
672 , p_life_in_months
673 , lr_distribution.units_assigned
674 , lr_distribution.full_name
675 , lr_distribution.employee_number
676 , gn_user_id
677 , SYSDATE
678 , SYSDATE
679 , gn_user_id
680 , gn_login_id
681 , p_date_placed_in_service
682 , p_asset_number
683 , p_description
684 , p_tag_number
685 , p_serial_number
686 , p_location
687 , p_expense_account
688 );
689
690 l_deprn_value := l_deprn_value + p_dep_amt;
691
692 UPDATE fa_whatif_itf
693 SET accumulated_deprn = l_deprn_value
694 WHERE period_name = l_whatif_period_tbl2 (i).period_name
695 AND book_type_code = p_book_type_code
696 AND request_id = p_request_id;
697 END LOOP; -- FOR i IN 1..p_number_of_periods LOOP
698 END LOOP; --OPEN lcr_distribution(p_book_type_code
699 ELSE --IF p_checkbox_check IS 'N' THEN -- p_checkbox_check = 'Y'
700 FOR i IN 1+l_count .. (l_counter2+l_count)
701 LOOP
702 IF i > (61+l_count)
703 THEN --(l_count + i) has been changed to i
704 UPDATE fa_whatif_itf
705 SET depreciation = 0
706 , new_depreciation = 0
707 , units = lr_distribution.units_assigned
708 , employee_name = lr_distribution.full_name
709 , employee_number = lr_distribution.employee_number
710 , accumulated_deprn = l_deprn_value
711 WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
712 AND book_type_code = p_book_type_code
713 AND request_id = p_request_id
714 AND asset_id = p_asset_id;
715 ELSIF i = (61 + l_count)
716 AND l_period <> 12 * l_twelve_count
717 THEN --(l_count + i) has been changed to i
718 -- l_round_value := p_round_value*60;
719 UPDATE fa_whatif_itf
720 SET depreciation = p_round_value * 5 --l_round_value*5
721 , new_depreciation = p_round_value * 5
722 , units = lr_distribution.units_assigned
723 , employee_name = lr_distribution.full_name
724 , employee_number = lr_distribution.employee_number
725 , accumulated_deprn = l_deprn_value - l_round_value
726 WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
727 AND book_type_code = p_book_type_code
728 AND request_id = p_request_id
729 AND asset_id = p_asset_id;
730 ELSIF i <= (60+l_count)
731 AND l_period <> 12 * l_twelve_count
732 THEN ----(l_count + i) has been changed to i
733 UPDATE fa_whatif_itf
734 SET depreciation = p_dep_amt
735 , new_depreciation = p_dep_amt
736 , units = lr_distribution.units_assigned
737 , employee_name = lr_distribution.full_name
738 , employee_number = lr_distribution.employee_number
739 , accumulated_deprn = l_deprn_value
740 WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
741 AND book_type_code = p_book_type_code
742 AND request_id = p_request_id
743 AND asset_id = p_asset_id;
744 ELSIF l_period = 12 * l_twelve_count
745 THEN
746 UPDATE fa_whatif_itf
747 SET depreciation = (FLOOR (p_dep_amt_annual) - p_dep_amt * 11)
748 , new_depreciation =
749 (FLOOR (p_dep_amt_annual) - p_dep_amt * 11
750 )
751 , units = lr_distribution.units_assigned
752 , employee_name = lr_distribution.full_name
753 , employee_number = lr_distribution.employee_number
754 , accumulated_deprn = l_deprn_value - l_round_value
755 WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
756 AND book_type_code = p_book_type_code
757 AND request_id = p_request_id
758 AND asset_id = p_asset_id;
759
760 l_twelve_count := l_twelve_count + 1;
761 ELSE
762 NULL;
763 END IF;
764
765 l_period := l_period + 1;
766 END LOOP;
767 END IF; --IF p_checkbox_check IS 'N' THEN
768
769 COMMIT;
770 EXCEPTION
771 WHEN OTHERS
772 THEN
773 NULL;
774 END insert_fa_whatif_itf;
775
776 PROCEDURE whatif_main (
777 x_errbuf OUT NOCOPY VARCHAR2
778 , x_retcode OUT NOCOPY NUMBER
779 , p_request_id IN NUMBER
780 , p_book_type_code IN fa_books.book_type_code%TYPE
781 , p_first_begin_period IN VARCHAR2
782 , p_number_of_periods IN NUMBER
783 , p_start_period IN VARCHAR2
784 , p_checkbox_check IN VARCHAR2
785 , p_full_rsrv_checkbox IN VARCHAR2
786 , p_asset_id IN NUMBER
787 )
788 IS
789 CURSOR lcr_profile_option (
790 p_option_name IN VARCHAR2
791 )
792 /***********************************************************************
793 *
794 * CURSOR
795 * lcr_receipts_details
796 *
797 * DESCRIPTION
798 * Cursor lcr_receipts_details is a private cursor of procedure whatif_main.
799 * Cursor will return the Profile option associated with the profile option name
800 *
801 * PARAMETERS
802 * ==========
803 * NAME TYPE DESCRIPTION
804 * ----------------- -------- ------------------------------------------
805 * p_option_name
806 *
807 *
808 * None
809 *
810 * PREREQUISITES
811 * None
812 *
813 * CALLED BY
814 * whatif_main
815 *
816 ***********************************************************************/
817 IS
818 SELECT user_profile_option_name
819 FROM fnd_profile_options_vl
820 WHERE profile_option_name = p_option_name;
821
822 CURSOR lcr_books (
823 p_book_type_code IN fa_books.book_type_code%TYPE
824 , p_asset_id IN fa_books.asset_id%TYPE
825 )
826 /***********************************************************************
827 *
828 * CURSOR
829 lcr_books
830 *
831 * DESCRIPTION
832 * Cursor lcr_books is a private cursor of procedure whatif_main.
833 * Cursor will return only the Book Details which are Active for a particular Book Type
834 *
835 * PARAMETERS
836 * ==========
837 * NAME TYPE DESCRIPTION
838 * ----------------- -------- ------------------------------------------
839 * p_book_type_code
840 *
841 *
842 * None
843 *
844 * PREREQUISITES
845 * None
846 *
847 * CALLED BY
848 * whatif_main
849 *
850 ***********************************************************************/
851 IS
852
853 --BUG# 7331249 The Below Query has been changed as, this query is not returning data ,
854 --when Period_counter_fully_reserved is not in FA_deprn_periods table.
855 SELECT fb.book_type_code
856 , cp.period_name
857 , fy.fiscal_year
858 , fb.asset_id
859 , fb.last_update_date
860 , fb.last_updated_by
861 , fb.last_update_login
862 , fb.original_cost
863 , fb.life_in_months
864 , fb.date_placed_in_service
865 , fb.period_counter_fully_reserved
866 , fa.asset_number
867 , fa.description
868 , fa.tag_number
869 , fa.serial_number
870 , flk.concatenated_segments LOCATION
871 , gcck.concatenated_segments expense_account
872 , (fb.allowed_deprn_limit_amount - 1) / 5
873 - FLOOR ((fb.allowed_deprn_limit_amount - 1) / 5) round_value
874 -- , CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Deprn_Value
875 , ROUND ((fb.allowed_deprn_limit_amount - 1) / 60) deprn_value
876 , (fb.allowed_deprn_limit_amount - 1) / 5 annual_deprn_value
877 FROM fa_books fb
878 , fa_book_controls fbc
879 ,fa_calendar_periods cp
880 , fa_fiscal_year fy
881 , fa_calendar_types fct
882 , fa_additions fa
883 , fa_distribution_history fdh
884 , fa_locations_kfv flk
885 , gl_code_combinations_kfv gcck
886 WHERE fa.asset_id = fb.asset_id
887 AND fb.asset_id = p_asset_id
888 AND fdh.asset_id = fa.asset_id
889 -- AND FDH.book_type_code = FB.book_type_code -- As per Bug No .7183390 (For Tax Books)
890 AND fbc.book_type_code = fb.book_type_code
891 -- As per Bug No .7183390 (For Tax Books)
892 AND fbc.distribution_source_book = fdh.book_type_code
893 -- As per Bug No .7183390 (For Tax Books)
894 AND fdh.location_id = flk.location_id
895 AND gcck.code_combination_id = fdh.code_combination_id
896 AND fb.book_type_code = p_book_type_code
897 AND cp.calendar_type = fbc.deprn_calendar
898 AND fy.fiscal_year_name = fbc.fiscal_year_name
899 AND cp.calendar_type = fct.calendar_type
900 AND cp.start_date >= fy.start_date
901 and cp.end_date <= fy.end_date
902 AND fb.period_counter_fully_reserved = (fy.fiscal_year * fct.number_per_fiscal_year + cp.period_num)
903 AND fb.date_ineffective IS NULL
904 AND fb.transaction_header_id_out IS NULL
905 AND fdh.transaction_header_id_out IS NULL
906 -- As per Bug No .7183390 (For Tax Books)
907 AND fb.period_counter_fully_reserved IS NOT NULL
908 AND fb.deprn_method_code <> 'JP-STL-EXTND'
909 AND fb.allowed_deprn_limit_amount > 1;
910
911 CURSOR lcr_deprn_periods (
912 p_book_type_code IN fa_books.book_type_code%TYPE
913 , p_period_name IN fa_deprn_periods.period_name%TYPE
914 )
915 /***********************************************************************
916 *
917 * CURSOR
918 * lcr_deprn_periods
919 *
920 * DESCRIPTION
921 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
922 * Cursor will return the period for a particular Book Type and Period
923 *
924 * PARAMETERS
925 * ==========
926 * NAME TYPE DESCRIPTION
927 * ----------------- -------- ------------------------------------------
928 * p_book_type_code
929 * p_period_name
930 *
931 *
932 * None
933 *
934 * PREREQUISITES
935 * None
936 *
937 * CALLED BY
938 * whatif_main
939 *
940 ***********************************************************************/
941 IS
942 SELECT fdp.period_counter
943 FROM fa_deprn_periods fdp
944 WHERE fdp.book_type_code = p_book_type_code
945 AND fdp.period_name = p_period_name;
946
947 CURSOR lcr_first_periods (
948 p_book_type IN fa_books.book_type_code%TYPE
949 , p_start_period IN fa_deprn_periods.period_name%TYPE
950 , p_number_of_periods IN NUMBER
951 )
952 /***********************************************************************
953 *
954 * CURSOR
955 * lcr_first_periods
956 *
957 * DESCRIPTION
958 * Cursor lcr_first_periods is a private cursor of procedure whatif_main.
959 * Cursor will return the first eligible period for a particular Book Type and Period
960 *
961 * PARAMETERS
962 * ==========
963 * NAME TYPE DESCRIPTION
964 * ----------------- -------- ------------------------------------------
965 * p_book_type_code
966 * p_period_name
967 * p_number_of_periods
968 *
969 *
970 * None
971 *
972 * PREREQUISITES
973 * None
974 *
975 * CALLED BY
976 * whatif_main
977 *
978 ***********************************************************************/
979 IS
980 --As per Bug No. 7289893 . The SQL statement was failing if p_number_of_periods = 1
981
982 SELECT cp1.period_name
983 FROM fa_calendar_periods cp
984 , fa_fiscal_year fy
985 , fa_book_controls fb
986 , fa_calendar_types fc
987 , fa_calendar_periods cp1
988 WHERE cp.period_name = p_start_period
989 AND cp.calendar_type = fb.deprn_calendar
990 AND fb.book_type_code = p_book_type
991 AND cp.calendar_type = fc.calendar_type
992 AND cp.start_date >= fy.start_date
993 AND cp.end_date <= fy.end_date
994 AND fy.fiscal_year_name = fb.fiscal_year_name
995 AND cp1.period_num = 1
996 AND fb.deprn_calendar = cp1.calendar_type
997 AND cp1.start_date > cp.start_date
998 AND ROWNUM = 1;
999
1000 CURSOR lcr_deprn_amount (
1001 p_book_type_code IN fa_books.book_type_code%TYPE
1002 , p_asset_id IN fa_books.asset_id%TYPE
1003 , p_period_counter IN NUMBER
1004 )
1005 /***********************************************************************
1006 *
1007 * CURSOR
1008 * lcr_deprn_amount
1009 *
1010 * DESCRIPTION
1011 * Cursor lcr_deprn_amount is a private cursor of procedure whatif_main.
1012 * Cursor will return the Depreciation Amount for a particular Book Type and Period
1013 *
1014 * PARAMETERS
1015 * ==========
1016 * NAME TYPE DESCRIPTION
1017 * ----------------- -------- ------------------------------------------
1018 * p_book_type_code
1019 * p_period_name
1020 *
1021 *
1022 * None
1023 *
1024 * PREREQUISITES
1025 * None
1026 *
1027 * CALLED BY
1028 * whatif_main
1029 *
1030 ***********************************************************************/
1031 IS
1032 SELECT CEIL ((fb.COST - fds.deprn_reserve) / 60)
1033 FROM fa_books fb
1034 , fa_deprn_summary fds
1035 WHERE fds.book_type_code = fb.book_type_code
1036 AND fb.asset_id = fds.asset_id
1037 AND fds.period_counter = p_period_counter
1038 AND fb.asset_id = p_asset_id
1039 AND fb.book_type_code = p_book_type_code
1040 AND fb.transaction_header_id_out IS NULL;
1041
1042 -- condition modified from date_ineffective
1043 CURSOR lcr_end_date (
1044 p_book_type_code IN fa_books.book_type_code%TYPE
1045 , p_period_name IN fa_deprn_periods.period_name%TYPE
1046 , p_counter IN NUMBER
1047 )
1048 /***********************************************************************
1049 *
1050 * CURSOR
1051 * lcr_end_date
1052 *
1053 * DESCRIPTION
1054 * Cursor lcr_end_date is a private cursor of procedure whatif_main.
1055 * Cursor will return the end_date
1056 *
1057 * PARAMETERS
1058 * ==========
1059 * NAME TYPE DESCRIPTION
1060 * ----------------- -------- ------------------------------------------
1061 * p_book_type_code
1062 * p_period_name
1063 *
1064 *
1065 * None
1066 *
1067 * PREREQUISITES
1068 * None
1069 *
1070 * CALLED BY
1071 * whatif_main
1072 *
1073 ***********************************************************************/
1074 IS
1075 SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
1076 FROM fa_calendar_periods fcp
1077 , fa_calendar_types fct
1078 , fa_book_controls fbc
1079 WHERE fbc.book_type_code = p_book_type_code
1080 AND fcp.calendar_type = fct.calendar_type
1081 AND fcp.calendar_type = fbc.deprn_calendar
1082 AND fcp.period_name = p_period_name;
1083
1084 CURSOR lcr_periods (
1085 p_book_type_code IN fa_books.book_type_code%TYPE
1086 , p_end_date IN DATE
1087 )
1088 /***********************************************************************
1089 *
1090 * CURSOR
1091 * lcr_periods
1092 *
1093 * DESCRIPTION
1094 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
1095 * Cursor will return the Peiord
1096 *
1097 * PARAMETERS
1098 * ==========
1099 * NAME TYPE DESCRIPTION
1100 * ----------------- -------- ------------------------------------------
1101 * p_book_type_code
1102 * p_end_date
1103 *
1104 *
1105 * None
1106 *
1107 * PREREQUISITES
1108 * None
1109 *
1110 * CALLED BY
1111 * whatif_main
1112 *
1113 ***********************************************************************/
1114 IS
1115 SELECT fcp.period_name
1116 FROM fa_calendar_periods fcp
1117 , fa_calendar_types fct
1118 , fa_book_controls fbc
1119 WHERE fbc.book_type_code = p_book_type_code
1120 AND fcp.calendar_type = fct.calendar_type
1121 AND fcp.calendar_type = fbc.deprn_calendar
1122 AND fcp.end_date = p_end_date;
1123
1124 l_req_id fa_whatif_itf.request_id%TYPE;
1125 l_sob fa_book_controls.set_of_books_id%TYPE;
1126 l_fully_reserved_flag VARCHAR2 (1);
1127 lc_return BOOLEAN;
1128 l_profile_tax_reform VARCHAR2 (100);
1129 ln_setof_bk_id NUMBER;
1130 lc_errmsg VARCHAR2 (4000);
1131 ln_retcode NUMBER;
1132 l_end_date DATE;
1133 ln_acc_deprn NUMBER;
1134 ln_first_elg_period NUMBER;
1135 ln_first_beg_period NUMBER;
1136 l_period fa_deprn_periods.period_name%TYPE;
1137 l_profile_option fnd_profile_options_vl.user_profile_option_name%TYPE;
1138 l_period_counter fa_deprn_periods.period_counter%TYPE;
1139 l_period_full_counter fa_deprn_periods.period_counter%TYPE;
1140 l_period_apr07_counter fa_deprn_periods.period_counter%TYPE;
1141 l_period_name fa_deprn_periods.period_name%TYPE;
1142 l_first_eligible_period fa_deprn_periods.period_name%TYPE
1143 := NULL;
1144 l_deprn_amount NUMBER;
1145 lr_books lcr_books%ROWTYPE;
1146 ex_user_exception EXCEPTION;
1147 p_debug_flag VARCHAR2 (1) DEFAULT 'N';
1148 BEGIN
1149 --*****Check Profile Option*****************
1150 -- Get the Profile Option
1151 l_profile_tax_reform :=
1152 fnd_profile.VALUE ('FA_JAPAN_TAX_REFORMS');
1153
1154 --Accumulated Depreciation
1155 BEGIN
1156 /* bug 8991192
1157 SELECT deprn_reserve
1158 INTO ln_acc_deprn
1159 FROM (SELECT ROWNUM a
1160 , deprn_reserve
1161 FROM fa_deprn_summary
1162 WHERE book_type_code = p_book_type_code
1163 AND asset_id = p_asset_id
1164 ORDER BY deprn_reserve DESC)
1165 WHERE ROWNUM = 1;
1166 */
1167 SELECT max(nvl(deprn_reserve,0))
1168 INTO ln_acc_deprn
1169 FROM fa_deprn_summary
1170 WHERE book_type_code = p_book_type_code
1171 AND asset_id = p_asset_id;
1172 EXCEPTION
1173 WHEN OTHERS
1174 THEN
1175 x_retcode := gn_error_status;
1176 x_errbuf := lc_errmsg;
1177 END;
1178
1179 --*****************************
1180 -- Derive Period Counter
1181 --*****************************
1182 OPEN lcr_deprn_periods (p_book_type_code, p_first_begin_period);
1183
1184 FETCH lcr_deprn_periods
1185 INTO l_period_counter;
1186
1187 CLOSE lcr_deprn_periods;
1188
1189 -- *****Check whether the asset is Fully Reserved*****
1190 IF l_profile_tax_reform = 'Y'
1191 THEN --Step 1
1192 IF p_checkbox_check IN (('YES'), ('Y'))
1193 THEN -- Step 2
1194 l_period_full_counter := NULL;
1195
1196 OPEN lcr_books (p_book_type_code, p_asset_id);
1197
1198 LOOP
1199 FETCH lcr_books
1200 INTO lr_books;
1201
1202 EXIT WHEN lcr_books%NOTFOUND;
1203
1204 --**************************************
1205 -- Derive First Eligible Period Name
1206 --**************************************
1207 OPEN lcr_first_periods
1208 (p_book_type_code
1209 , lr_books.period_name -- Period When fully reserved
1210 , p_number_of_periods
1211 );
1212
1213 FETCH lcr_first_periods
1214 INTO l_first_eligible_period;
1215
1216 CLOSE lcr_first_periods;
1217
1218
1219 --*****************************
1220 -- Derive Depreciation Amount
1221 --*****************************
1222 OPEN lcr_deprn_amount (p_book_type_code
1223 , lr_books.asset_id
1224 , lr_books.period_counter_fully_reserved
1225 );
1226
1227 FETCH lcr_deprn_amount
1228 INTO l_deprn_amount;
1229
1230 CLOSE lcr_deprn_amount;
1231
1232
1233 ln_first_elg_period :=
1234 ret_counter (p_book_type_code, l_first_eligible_period);
1235 ln_first_beg_period :=
1236 ret_counter (p_book_type_code, p_first_begin_period);
1237
1238 --Step 3 Validation for Date Placed in Service
1239 IF lr_books.date_placed_in_service <
1240 TO_DATE ('01-04-2007', 'DD-MM-RRRR')
1241 THEN
1242 IF p_first_begin_period IS NOT NULL
1243 THEN
1244 -- STEP 5 VALIDATION using the concept for Period Counter
1245 -- IF (TO_DATE(l_first_eligible_period,'MM-RRRR') > TO_DATE(p_first_begin_period,'MM-RRRR') ) THEN
1246 IF ln_first_elg_period > ln_first_beg_period
1247 THEN
1248 --Step 8 Insert data into FA_WHATIF_ITF
1249 insert_fa_whatif_itf
1250 (x_errbuf => lc_errmsg
1251 , x_retcode => ln_retcode
1252 , p_request_id => p_request_id
1253 , p_checkbox_check => p_full_rsrv_checkbox
1254 , p_book_type_code => p_book_type_code
1255 , p_number_of_periods => p_number_of_periods
1256 , p_asset_id => lr_books.asset_id
1257 , p_period_name => p_start_period
1258 , p_first_begin_prd => l_first_eligible_period
1259 --lr_books.period_name
1260 -- ,p_dep_amt => l_deprn_amount
1261 , p_dep_amt => lr_books.deprn_value
1262 , p_dep_amt_annual => lr_books.annual_deprn_value
1263 , p_date_placed_in_service => lr_books.date_placed_in_service
1264 , p_life_in_months => lr_books.life_in_months
1265 , p_original_cost => lr_books.original_cost
1266 , p_asset_number => lr_books.asset_number
1267 , p_description => lr_books.description
1268 , p_tag_number => lr_books.tag_number
1269 , p_serial_number => lr_books.serial_number
1270 , p_location => lr_books.LOCATION
1271 , p_expense_account => lr_books.expense_account
1272 , p_round_value => lr_books.round_value
1273 , p_deprn_value => ln_acc_deprn
1274 --ln_acc_deprnlr_books.Deprn_Value
1275 , p_flag => 'N'
1276 );
1277
1278 IF ln_retcode <> 1
1279 THEN
1280 RAISE ex_user_exception;
1281 END IF;
1282 ELSE
1283 --Calculating extended depreciation starting from the first period provided in the form
1284 --Step 8 Insert data into FA_WHATIF_ITF
1285 insert_fa_whatif_itf
1286 (x_errbuf => lc_errmsg
1287 , x_retcode => ln_retcode
1288 , p_request_id => p_request_id
1289 , p_checkbox_check => p_full_rsrv_checkbox
1290 , p_book_type_code => p_book_type_code
1291 , p_number_of_periods => p_number_of_periods
1292 , p_asset_id => lr_books.asset_id
1293 , p_period_name => p_start_period
1294 --lr_books.period_name
1295 , p_first_begin_prd => p_first_begin_period
1296 -- ,p_dep_amt => l_deprn_amount
1297 , p_dep_amt => lr_books.deprn_value
1298 , p_dep_amt_annual => lr_books.annual_deprn_value
1299 , p_date_placed_in_service => lr_books.date_placed_in_service
1300 , p_life_in_months => lr_books.life_in_months
1301 , p_original_cost => lr_books.original_cost
1302 , p_asset_number => lr_books.asset_number
1303 , p_description => lr_books.description
1304 , p_tag_number => lr_books.tag_number
1305 , p_serial_number => lr_books.serial_number
1306 , p_location => lr_books.LOCATION
1307 , p_expense_account => lr_books.expense_account
1308 , p_round_value => lr_books.round_value
1309 , p_deprn_value => ln_acc_deprn
1310 --lr_books.Deprn_Value
1311 , p_flag => 'N'
1312 );
1313
1314 IF ln_retcode <> 1
1315 THEN
1316 RAISE ex_user_exception;
1317 END IF;
1318 END IF;
1319 --IF (p_first_begin_period > p_end_asset_date) THEN
1320 --Step 6 calculate depreciation projections based on Period When Fully Reserved
1321 ELSE -- IF p_first_begin_period IS NOT NULL THEN
1322 --Step 8 Insert data into FA_WHATIF_ITF
1323
1324 insert_fa_whatif_itf
1325 (x_errbuf => lc_errmsg
1326 , x_retcode => ln_retcode
1327 , p_request_id => p_request_id
1328 , p_checkbox_check => p_full_rsrv_checkbox
1329 , p_book_type_code => p_book_type_code
1330 , p_number_of_periods => p_number_of_periods
1331 , p_asset_id => lr_books.asset_id
1332 , p_period_name => p_start_period
1333 , p_first_begin_prd => l_first_eligible_period
1334 --lr_books.period_name
1335 -- ,p_dep_amt => l_deprn_amount
1336 , p_dep_amt => lr_books.deprn_value
1337 , p_dep_amt_annual => lr_books.annual_deprn_value
1338 , p_date_placed_in_service => lr_books.date_placed_in_service
1339 , p_life_in_months => lr_books.life_in_months
1340 , p_original_cost => lr_books.original_cost
1341 , p_asset_number => lr_books.asset_number
1342 , p_description => lr_books.description
1343 , p_tag_number => lr_books.tag_number
1344 , p_serial_number => lr_books.serial_number
1345 , p_location => lr_books.LOCATION
1346 , p_expense_account => lr_books.expense_account
1347 , p_round_value => lr_books.round_value
1348 , p_deprn_value => ln_acc_deprn
1349 --lr_books.Deprn_Value
1350 , p_flag => 'N'
1351 );
1352
1353 IF ln_retcode <> 1
1354 THEN
1355 RAISE ex_user_exception;
1356 END IF;
1357 END IF; -- IF p_first_begin_period IS NOT NULL THEN
1358 ELSE
1359 fnd_message.set_name ('OFA', 'FA_SERVICE_DATE_INVALID');
1360 lc_errmsg := fnd_message.get;
1361 RAISE ex_user_exception;
1362 END IF;
1363 --fnd_date.canonical_to_date(lr_books.date_placed_in_service) < fnd_date.canonical_to_date('01-APR-2007')
1364 END LOOP;
1365 END IF; --IF p_checkbox_check IN (('YES'),('Y'))) THEN
1366 END IF; --IF l_profile_tax_reform = 'Y' THEN
1367 EXCEPTION
1368 WHEN ex_user_exception
1369 THEN
1370 x_retcode := gn_error_status;
1371 x_errbuf := lc_errmsg;
1372 fnd_file.put_line (fnd_file.output, x_errbuf);
1373 fnd_file.put_line (fnd_file.LOG, x_errbuf);
1374 WHEN OTHERS
1375 THEN
1376 x_retcode := gn_error_status;
1377 x_errbuf := lc_errmsg;
1378 ROLLBACK;
1379 END whatif_main;
1380
1381 PROCEDURE extd_deprn_main (
1382 x_errbuf OUT NOCOPY VARCHAR2
1383 , x_retcode OUT NOCOPY NUMBER
1384 , p_request_id IN NUMBER
1385 , p_book_type_code IN fa_books.book_type_code%TYPE
1386 , p_first_begin_period IN VARCHAR2
1387 , p_number_of_periods IN NUMBER
1388 , p_start_period IN VARCHAR2
1389 , p_checkbox_check IN VARCHAR2
1390 , p_full_rsrv_checkbox IN VARCHAR2
1391 , p_asset_id IN NUMBER
1392 )
1393 IS
1394 CURSOR lcr_profile_option (
1395 p_option_name IN VARCHAR2
1396 )
1397 /***********************************************************************
1398 *
1399 * CURSOR
1400 * lcr_receipts_details
1401 *
1402 * DESCRIPTION
1403 * Cursor lcr_receipts_details is a private cursor of procedure whatif_main.
1404 * Cursor will return the Profile option associated with the profile option name
1405 *
1406 * PARAMETERS
1407 * ==========
1408 * NAME TYPE DESCRIPTION
1409 * ----------------- -------- ------------------------------------------
1410 * p_option_name
1411 *
1412 *
1413 * None
1414 *
1415 * PREREQUISITES
1416 * None
1417 *
1418 * CALLED BY
1419 * extd_deprn_main
1420 *
1421 ***********************************************************************/
1422 IS
1423 SELECT user_profile_option_name
1424 FROM fnd_profile_options_vl
1425 WHERE profile_option_name = p_option_name;
1426
1427 CURSOR lcr_books (
1428 p_book_type_code IN fa_books.book_type_code%TYPE
1429 , p_asset_id IN fa_books.asset_id%TYPE
1430 )
1431 /***********************************************************************
1432 *
1433 * CURSOR
1434 lcr_books
1435 *
1436 * DESCRIPTION
1437 * Cursor lcr_books is a private cursor of procedure whatif_main.
1438 * Cursor will return only the Book Details which are Active for a particular Book Type
1439 *
1440 * PARAMETERS
1441 * ==========
1442 * NAME TYPE DESCRIPTION
1443 * ----------------- -------- ------------------------------------------
1444 * p_book_type_code
1445 *
1446 *
1447 * None
1448 *
1449 * PREREQUISITES
1450 * None
1451 *
1452 * CALLED BY
1453 * extd_deprn_main
1454 *
1455 ***********************************************************************/
1456 IS
1457 SELECT fb.book_type_code
1458 , fb.COST -- change
1459 , fb.basic_rate -- change
1460 , fb.asset_id
1461 , fb.last_update_date
1462 , fb.last_updated_by
1463 , fb.last_update_login
1464 , fb.original_cost
1465 , fb.life_in_months
1466 , fb.date_placed_in_service
1467 , fb.period_counter_fully_reserved
1468 , fa.asset_number
1469 , fa.description
1470 , fa.tag_number
1471 , fa.serial_number
1472 , flk.concatenated_segments LOCATION
1473 , gcck.concatenated_segments expense_account
1474 -- ,(CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12)-((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Round_Value
1475 -- , CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Acc_Deprn_Value
1476 -- ,CEIL((FB.allowed_deprn_limit_amount -1)/60) Deprn_value
1477 , (fb.allowed_deprn_limit_amount - 1) / 5
1478 - FLOOR ((fb.allowed_deprn_limit_amount - 1) / 5) round_value
1479 , ROUND ((fb.allowed_deprn_limit_amount - 1) / 60) deprn_value
1480 , (fb.allowed_deprn_limit_amount - 1) / 5 annual_deprn_value
1481 , fb.adjusted_cost
1482 , fb.allowed_deprn_limit_amount
1483 FROM fa_books fb
1484 , fa_book_controls fbc -- As per Bug No .7183390 (For Tax Books)
1485 , fa_additions fa
1486 , fa_distribution_history fdh
1487 , fa_locations_kfv flk
1488 , gl_code_combinations_kfv gcck
1489 WHERE fa.asset_id = fb.asset_id
1490 AND fb.asset_id = p_asset_id
1491 AND fdh.asset_id = fa.asset_id
1492 -- AND FDH.book_type_code = FB.book_type_code -- As per Bug No .7183390 (For Tax Books)
1493 AND fbc.book_type_code = fb.book_type_code
1494 -- As per Bug No .7183390 (For Tax Books)
1495 AND fbc.distribution_source_book = fdh.book_type_code
1496 -- As per Bug No .7183390 (For Tax Books)
1497 AND fdh.location_id = flk.location_id
1498 AND gcck.code_combination_id = fdh.code_combination_id
1499 AND fb.book_type_code = p_book_type_code
1500 AND fb.date_ineffective IS NULL
1501 AND fb.transaction_header_id_out IS NULL
1502 AND fb.period_counter_fully_reserved IS NULL
1503 AND fdh.transaction_header_id_out IS NULL
1504 -- As per Bug No .7183390 (For Tax Books)
1505 AND fb.deprn_method_code <> 'JP-STL-EXTND'
1506 AND fb.allowed_deprn_limit_amount > 1;
1507
1508 CURSOR lcr_deprn_periods (
1509 p_book_type_code IN fa_books.book_type_code%TYPE
1510 , p_period_name IN fa_deprn_periods.period_name%TYPE
1511 )
1512 /***********************************************************************
1513 *
1514 * CURSOR
1515 * lcr_deprn_periods
1516 *
1517 * DESCRIPTION
1518 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
1519 * Cursor will return the period for a particular Book Type and Period
1520 *
1521 * PARAMETERS
1522 * ==========
1523 * NAME TYPE DESCRIPTION
1524 * ----------------- -------- ------------------------------------------
1525 * p_book_type_code
1526 * p_period_name
1527 *
1528 *
1529 * None
1530 *
1531 * PREREQUISITES
1532 * None
1533 *
1534 * CALLED BY
1535 * extd_deprn_main
1536 *
1537 ***********************************************************************/
1538 IS
1539 SELECT fdp.period_counter
1540 FROM fa_deprn_periods fdp
1541 WHERE fdp.book_type_code = p_book_type_code
1542 AND fdp.period_name = p_period_name;
1543
1544 CURSOR lcr_first_periods (
1545 p_book_type IN fa_books.book_type_code%TYPE
1546 , p_start_period IN fa_deprn_periods.period_name%TYPE
1547 , p_number_of_periods IN NUMBER
1548 )
1549 /***********************************************************************
1550 *
1551 * CURSOR
1552 * lcr_first_periods
1553 *
1554 * DESCRIPTION
1555 * Cursor lcr_first_periods is a private cursor of procedure whatif_main.
1556 * Cursor will return the first eligible period for a particular Book Type and Period
1557 *
1558 * PARAMETERS
1559 * ==========
1560 * NAME TYPE DESCRIPTION
1561 * ----------------- -------- ------------------------------------------
1562 * p_book_type_code
1563 * p_period_name
1564 * p_number_of_periods
1565 *
1566 *
1567 * None
1568 *
1569 * PREREQUISITES
1570 * None
1571 *
1572 * CALLED BY
1573 * extd_deprn_main
1574 *
1575 ***********************************************************************/
1576 IS
1577 SELECT cp1.period_name
1578 FROM fa_calendar_periods cp
1579 , fa_fiscal_year fy
1580 , fa_book_controls fb
1581 , fa_calendar_types fc
1582 , fa_calendar_periods cp1
1583 WHERE cp.period_name = p_start_period
1584 AND cp.calendar_type = fb.deprn_calendar
1585 AND fb.book_type_code = p_book_type
1586 AND cp.calendar_type = fc.calendar_type
1587 AND cp.start_date >= fy.start_date
1588 AND cp.end_date <= fy.end_date
1589 AND fy.fiscal_year_name = fb.fiscal_year_name
1590 AND cp1.period_num = 1
1591 AND fb.deprn_calendar = cp1.calendar_type
1592 AND cp1.start_date >= cp.start_date -- BUG# 7264516: Added = condition
1593 AND ROWNUM = 1;
1594
1595 CURSOR lcr_deprn_amount (
1596 p_book_type_code IN fa_books.book_type_code%TYPE
1597 , p_asset_id IN fa_books.asset_id%TYPE
1598 , p_period_counter IN NUMBER
1599 )
1600 /***********************************************************************
1601 *
1602 * CURSOR
1603 * lcr_deprn_amount
1604 *
1605 * DESCRIPTION
1606 * Cursor lcr_deprn_amount is a private cursor of procedure whatif_main.
1607 * Cursor will return the Depreciation Amount for a particular Book Type and Period
1608 *
1609 * PARAMETERS
1610 * ==========
1611 * NAME TYPE DESCRIPTION
1612 * ----------------- -------- ------------------------------------------
1613 * p_book_type_code
1614 * p_period_name
1615 *
1616 *
1617 * None
1618 *
1619 * PREREQUISITES
1620 * None
1621 *
1622 * CALLED BY
1623 * extd_deprn_main
1624 *
1625 ***********************************************************************/
1626 IS
1627 SELECT CEIL ((fb.COST - fds.deprn_reserve) / 60)
1628 -- need to change for variable
1629 FROM fa_books fb
1630 , fa_deprn_summary fds
1631 WHERE fds.book_type_code = fb.book_type_code
1632 AND fb.asset_id = fds.asset_id
1633 AND fds.period_counter = p_period_counter
1634 AND fb.asset_id = p_asset_id
1635 AND fb.book_type_code = p_book_type_code
1636 AND fb.transaction_header_id_out IS NULL;
1637
1638 -- condition modified from date_ineffective
1639 CURSOR lcr_end_date_check (
1640 p_book_type_code IN fa_books.book_type_code%TYPE
1641 , p_period_name IN fa_deprn_periods.period_name%TYPE
1642 , p_counter IN NUMBER
1643 )
1644 /***********************************************************************
1645 *
1646 * CURSOR
1647 * lcr_end_date_check
1648 *
1649 * DESCRIPTION
1650 * Cursor lcr_end_date_check is a private cursor of procedure whatif_main.
1651 * Cursor will return the end_date
1652 *
1653 * PARAMETERS
1654 * ==========
1655 * NAME TYPE DESCRIPTION
1656 * ----------------- -------- ------------------------------------------
1657 * p_book_type_code
1658 * p_period_name
1659 * p_counter
1660 *
1661 *
1662 * None
1663 *
1664 * PREREQUISITES
1665 * None
1666 *
1667 * CALLED BY
1668 * extd_deprn_main
1669 *
1670 ***********************************************************************/
1671 IS
1672 SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
1673 FROM fa_calendar_periods fcp
1674 , fa_calendar_types fct
1675 , fa_book_controls fbc
1676 WHERE fbc.book_type_code = p_book_type_code
1677 AND fcp.calendar_type = fct.calendar_type
1678 AND fcp.calendar_type = fbc.deprn_calendar
1679 AND fcp.period_name = p_period_name;
1680
1681 CURSOR lcr_end_date_validate (
1682 p_book_type_code IN fa_books.book_type_code%TYPE
1683 , p_end_date IN fa_calendar_periods.end_date%TYPE
1684 , p_counter IN NUMBER
1685 )
1686 /***********************************************************************
1687 *
1688 * CURSOR
1689 * lcr_end_date_check
1690 *
1691 * DESCRIPTION
1692 * Cursor lcr_end_date_check is a private cursor of procedure whatif_main.
1693 * Cursor will return the end_date
1694 *
1695 * PARAMETERS
1696 * ==========
1697 * NAME TYPE DESCRIPTION
1698 * ----------------- -------- ------------------------------------------
1699 * p_book_type_code
1700 * p_end_date
1701 * p_counter
1702 *
1703 *
1704 * None
1705 *
1706 * PREREQUISITES
1707 * None
1708 *
1709 * CALLED BY
1710 * extd_deprn_main
1711 *
1712 ***********************************************************************/
1713 IS
1714 SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
1715 FROM fa_calendar_periods fcp
1716 , fa_calendar_types fct
1717 , fa_book_controls fbc
1718 WHERE fbc.book_type_code = p_book_type_code
1719 AND fcp.calendar_type = fct.calendar_type
1720 AND fcp.calendar_type = fbc.deprn_calendar
1721 AND p_end_date BETWEEN fcp.start_date AND fcp.end_date;
1722
1723 CURSOR lcr_periods (
1724 p_book_type_code IN fa_books.book_type_code%TYPE
1725 , p_end_date IN DATE
1726 )
1727 /***********************************************************************
1728 *
1729 * CURSOR
1730 * lcr_periods
1731 *
1732 * DESCRIPTION
1733 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
1734 * Cursor will return the Peiord
1735 *
1736 * PARAMETERS
1737 * ==========
1738 * NAME TYPE DESCRIPTION
1739 * ----------------- -------- ------------------------------------------
1740 * p_book_type_code
1741 * p_end_date
1742 *
1743 *
1744 * None
1745 *
1746 * PREREQUISITES
1747 * None
1748 *
1749 * CALLED BY
1750 * extd_deprn_main
1751 *
1752 ***********************************************************************/
1753 IS
1754 SELECT fcp.period_name
1755 FROM fa_calendar_periods fcp
1756 , fa_calendar_types fct
1757 , fa_book_controls fbc
1758 WHERE fbc.book_type_code = p_book_type_code
1759 AND fcp.calendar_type = fct.calendar_type
1760 AND fcp.calendar_type = fbc.deprn_calendar
1761 AND fcp.end_date = p_end_date;
1762
1763 CURSOR lcr_extd_deprns (
1764 p_book_type_code IN fa_books.book_type_code%TYPE
1765 , p_period_name IN fa_deprn_periods.period_name%TYPE
1766 )
1767 /***********************************************************************
1768 *
1769 * CURSOR
1770 * lcr_extd_deprns
1771 *
1772 * DESCRIPTION
1773 * Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
1774 * Cursor will return the end_date
1775 *
1776 * PARAMETERS
1777 * ==========
1778 * NAME TYPE DESCRIPTION
1779 * ----------------- -------- ------------------------------------------
1780 * p_book_type_code
1781 * p_period_name
1782 * *
1783 * None
1784 *
1785 * PREREQUISITES
1786 * None
1787 *
1788 * CALLED BY
1789 * load
1790 *
1791 ***********************************************************************/
1792 IS
1793 SELECT fcp.end_date
1794 FROM fa_calendar_periods fcp
1795 , fa_calendar_types fct
1796 , fa_book_controls fbc
1797 WHERE fbc.book_type_code = p_book_type_code
1798 AND fcp.calendar_type = fct.calendar_type
1799 AND fcp.calendar_type = fbc.deprn_calendar
1800 AND fcp.period_name = p_period_name;
1801
1802 CURSOR lcr_dern_method (
1803 p_book_type_code IN fa_books.book_type_code%TYPE
1804 , p_asset_id IN fa_books.asset_id%TYPE
1805 )
1806 /***********************************************************************
1807 *
1808 * CURSOR
1809 lcr_dern_method
1810 *
1811 * DESCRIPTION
1812 * Cursor lcr_dern_method is a private cursor of procedure whatif_main.
1813 * Cursor will return the deprn method code which are Active for a particular Book Type
1814 *
1815 * PARAMETERS
1816 * ==========
1817 * NAME TYPE DESCRIPTION
1818 * ----------------- -------- ------------------------------------------
1819 * p_book_type_code
1820 * p_asset_id
1821 *
1822 *
1823 * None
1824 *
1825 * PREREQUISITES
1826 * None
1827 *
1828 * CALLED BY
1829 * extd_deprn_main
1830 *
1831 ***********************************************************************/
1832 IS
1833 SELECT fb.deprn_method_code
1834 FROM fa_books fb
1835 WHERE fb.book_type_code = p_book_type_code
1836 AND fb.asset_id = p_asset_id
1837 AND fb.date_ineffective IS NULL
1838 AND fb.transaction_header_id_out IS NULL
1839 AND fb.deprn_method_code LIKE '%STL%';
1840
1841 l_req_id fa_whatif_itf.request_id%TYPE;
1842 l_sob fa_book_controls.set_of_books_id%TYPE;
1843 l_fully_reserved_flag VARCHAR2 (1);
1844 lc_return BOOLEAN;
1845 l_profile_tax_reform VARCHAR2 (100);
1846 ln_setof_bk_id NUMBER;
1847 lc_errmsg VARCHAR2 (4000);
1848 ln_retcode NUMBER;
1849 l_end_date DATE;
1850 l_end_date_validate DATE;
1851 l_end_date_check DATE;
1852 l_extd_period_name VARCHAR2 (10);
1853 l_extended_date DATE;
1854 l_period fa_deprn_periods.period_name%TYPE;
1855 l_profile_option fnd_profile_options_vl.user_profile_option_name%TYPE;
1856 l_period_counter fa_deprn_periods.period_counter%TYPE;
1857 l_period_full_counter fa_deprn_periods.period_counter%TYPE;
1858 l_period_apr07_counter fa_deprn_periods.period_counter%TYPE;
1859 l_period_name fa_deprn_periods.period_name%TYPE;
1860 l_first_eligible_period fa_deprn_periods.period_name%TYPE;
1861 l_deprn_amount NUMBER;
1862 l_per NUMBER;
1863 l_count NUMBER := 1;
1864 ln_acc_deprn NUMBER;
1865 ln_acc_deprn1 NUMBER;
1866 lc_period VARCHAR2 (10);
1867 l_last_deprn_run_pc VARCHAR2 (10); -- Bug#10311019
1868 l_date_extn_end DATE;
1869 l_exit NUMBER := 0;
1870 ln_first_elg_period NUMBER;
1871 ln_first_beg_period NUMBER;
1872 ln_int_deprn fa_deprn_summary.deprn_reserve%TYPE;
1873 l_deprn_method fa_books.deprn_method_code%TYPE;
1874 l_amt NUMBER;
1875 l_temp NUMBER;
1876 lr_books lcr_books%ROWTYPE;
1877 ex_user_exception EXCEPTION;
1878 p_debug_flag VARCHAR2 (1) DEFAULT 'N';
1879 ln_start_period_count NUMBER;
1880 ln_period_count NUMBER;
1881 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1882 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
1883 ln_ytd NUMBER; -- Bug#10209489
1884 BEGIN
1885 l_date_extn_end := NULL;
1886 ln_acc_deprn := NULL;
1887 l_extended_date := NULL;
1888 gn_acc_deprn := 0;
1889 ln_ytd := 0; -- Bug#10209489
1890 --*****Check Profile Option*****************
1891
1892 -- Get the Profile Option
1893 l_profile_tax_reform :=
1894 fnd_profile.VALUE ('FA_JAPAN_TAX_REFORMS');
1895
1896 SELECT period_name
1897 INTO lc_period
1898 FROM fa_deprn_periods
1899 WHERE book_type_code = p_book_type_code
1900 AND period_close_date IS NULL;
1901
1902 /* Bug#10311019: Last deprn run period counter */
1903 /* Bug#10432890: Modified query to execute order by as expected */
1904 select period_name
1905 into l_last_deprn_run_pc
1906 FROM (
1907 select period_name
1908 from fa_deprn_periods
1909 where book_type_code = p_book_type_code
1910 and deprn_run = 'Y'
1911 order by period_counter desc)
1912 where rownum = 1;
1913
1914 --As per the Bug 7210341
1915 ln_period_count :=
1916 ret_counter (p_book_type_code, lc_period);
1917 ln_start_period_count :=
1918 ret_counter (p_book_type_code, p_start_period); -- aug
1919
1920 l_temp := ln_period_count - ln_start_period_count;
1921
1922 --As per the Bug 7210341
1923 /* SELECT MONTHS_BETWEEN (TO_DATE (lc_period, 'MM-RRRR')
1924 , TO_DATE (p_start_period, 'MM-RRRR'))
1925 INTO l_temp
1926 FROM DUAL;
1927 */
1928 --Bug#9595652:Changed to < 0 as what_if cannot be run for
1929 --already closed periods.
1930 IF l_temp < 0
1931 THEN
1932 l_temp := p_number_of_periods - l_temp;
1933 lc_period := lc_period;
1934 ELSE
1935 l_temp := p_number_of_periods;
1936 lc_period := p_start_period;
1937 END IF;
1938
1939 --*****************************
1940 -- Derive Period Counter
1941 --*****************************
1942 /* Bug#10311019: Passing last deprn run period counter */
1943 OPEN lcr_extd_deprns (p_book_type_code, l_last_deprn_run_pc);
1944
1945 FETCH lcr_extd_deprns
1946 INTO l_date_extn_end;
1947
1948 CLOSE lcr_extd_deprns;
1949
1950 --*****************************
1951 -- Derive Method Code
1952 --*****************************
1953 OPEN lcr_dern_method (p_book_type_code, p_asset_id);
1954
1955 FETCH lcr_dern_method
1956 INTO l_deprn_method;
1957
1958 CLOSE lcr_dern_method;
1959
1960 OPEN lcr_books (p_book_type_code, p_asset_id);
1961
1962 LOOP
1963 FETCH lcr_books
1964 INTO lr_books;
1965
1966 EXIT WHEN lcr_books%NOTFOUND;
1967
1968 OPEN lcr_end_date_check (p_book_type_code
1969 , lc_period
1970 , p_number_of_periods
1971 );
1972
1973 FETCH lcr_end_date_check
1974 INTO l_end_date_check;
1975
1976 CLOSE lcr_end_date_check;
1977 /*Bug 10155740*/
1978 l_asset_hdr_rec.asset_id := lr_books.asset_id;
1979 l_asset_hdr_rec.book_type_code := p_book_type_code;
1980
1981 IF not FA_UTIL_PVT.get_asset_deprn_rec
1982 (p_asset_hdr_rec => l_asset_hdr_rec ,
1983 px_asset_deprn_rec => l_asset_deprn_rec,
1984 p_period_counter => NULL,
1985 p_mrc_sob_type_code => NULL
1986 ) then raise ex_user_exception;
1987 END IF;
1988
1989 ln_acc_deprn1 := l_asset_deprn_rec.deprn_reserve;
1990 ln_ytd := l_asset_deprn_rec.ytd_deprn; -- Bug#10209489
1991
1992 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn1;
1993 l_per := chk_period (lc_period, p_book_type_code);
1994
1995 IF l_exit = 0
1996 THEN
1997 FOR i IN 1 .. l_temp
1998 LOOP
1999 IF l_deprn_method IS NOT NULL
2000 THEN -- ( For STL Method)
2001 IF gn_acc_deprn <=
2002 (lr_books.COST - lr_books.allowed_deprn_limit_amount
2003 )
2004 THEN
2005 ln_acc_deprn := (lr_books.adjusted_cost * lr_books.basic_rate)/12; -- Bug#8628718
2006 -- ln_acc_deprn :=
2007 -- (lr_books.COST * lr_books.basic_rate
2008 -- ) / 12;
2009 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn;
2010 ELSIF gn_acc_deprn >
2011 (lr_books.COST - lr_books.allowed_deprn_limit_amount
2012 )
2013 THEN
2014 BEGIN
2015 SELECT ADD_MONTHS (l_date_extn_end, i-1)
2016 -- SELECT ADD_MONTHS(l_date_extn_end,i-1) (For Bug : 6971130)
2017 INTO l_extended_date
2018 FROM DUAL;
2019
2020 IF l_extended_date IS NOT NULL
2021 THEN
2022 l_exit := l_exit + 1;
2023 EXIT;
2024 END IF;
2025 EXCEPTION
2026 WHEN OTHERS
2027 THEN
2028 x_retcode := gn_error_status;
2029 x_errbuf := lc_errmsg;
2030 END;
2031 END IF; --IF gn_acc_deprn <> lr_books.recoverable_cost THEN
2032 ELSE -- IF l_deprn_method IS NOT NULL THEN ( For DB Method)
2033 IF gn_acc_deprn <=
2034 (lr_books.COST - lr_books.allowed_deprn_limit_amount
2035 )
2036 THEN
2037 IF l_per = (12 * l_count + 1)
2038 THEN
2039 ln_int_deprn :=
2040 ((lr_books.COST - gn_acc_deprn)
2041 * lr_books.basic_rate
2042 )
2043 / 12;
2044 l_count := l_count + 1;
2045
2046 IF ln_int_deprn IS NULL
2047 THEN
2048 -- ln_acc_deprn := (lr_books.adjusted_cost * lr_books.basic_rate)/12;-- (Bug No.6968798)
2049 ln_acc_deprn :=
2050 ((lr_books.COST - ln_acc_deprn1)
2051 * lr_books.basic_rate
2052 )
2053 / 12;
2054 ELSE
2055 ln_acc_deprn := ln_int_deprn;
2056 END IF;
2057
2058 gn_acc_deprn :=
2059 gn_acc_deprn + ln_acc_deprn;
2060 ELSIF l_per <> (12 * l_count + 1)
2061 THEN
2062 IF ln_int_deprn IS NULL
2063 THEN
2064 -- ln_acc_deprn := (lr_books.adjusted_cost * lr_books.basic_rate)/12;-- (Bug No.6968798)
2065 -- Bug#10209489: Deducting ytd for calculating correct deprn in first year
2066 ln_acc_deprn :=
2067 ((lr_books.COST - (ln_acc_deprn1 - ln_ytd ))
2068 * lr_books.basic_rate
2069 )
2070 / 12;
2071 ELSIF ln_int_deprn IS NOT NULL
2072 THEN
2073 ln_acc_deprn := ln_int_deprn;
2074 END IF;
2075
2076 gn_acc_deprn :=
2077 gn_acc_deprn + ln_acc_deprn;
2078 END IF; --IF l_per = 12 THEN
2079 ELSIF gn_acc_deprn >
2080 (lr_books.COST - lr_books.allowed_deprn_limit_amount
2081 )
2082 THEN
2083 BEGIN
2084 SELECT ADD_MONTHS (l_date_extn_end, i-1)
2085 --SELECT ADD_MONTHS(l_date_extn_end,i-1) (For Bug : 6971130)
2086 INTO l_extended_date
2087 FROM DUAL;
2088
2089 IF l_extended_date IS NOT NULL
2090 THEN
2091 l_exit := l_exit + 1;
2092 EXIT;
2093 END IF;
2094 EXCEPTION
2095 WHEN OTHERS
2096 THEN
2097 x_retcode := gn_error_status;
2098 x_errbuf := lc_errmsg;
2099 END;
2100 END IF; --IF gn_acc_deprn <> lr_books.recoverable_cost THEN
2101
2102 l_per := l_per + 1;
2103 END IF; -- IF l_deprn_method IS NOT NULL THEN
2104 END LOOP;
2105
2106 IF l_extended_date IS NOT NULL
2107 THEN
2108 EXIT;
2109 END IF;
2110 END IF; --IF l_exit = 0 THEN
2111 END LOOP;
2112
2113 --*****************************
2114 -- Derive Period end date
2115 --*****************************
2116 OPEN lcr_deprn_periods (p_book_type_code, p_first_begin_period);
2117
2118 FETCH lcr_deprn_periods
2119 INTO l_period_counter;
2120
2121 CLOSE lcr_deprn_periods;
2122
2123 IF lcr_books%ISOPEN
2124 THEN
2125 CLOSE lcr_books;
2126 END IF;
2127
2128 -- *****Check whether the asset is Fully Reserved*****
2129 IF l_profile_tax_reform = 'Y'
2130 THEN --Step 1
2131 IF p_checkbox_check IN (('YES'), ('Y'))
2132 THEN -- Step 2
2133 l_period_full_counter := NULL;
2134
2135 OPEN lcr_books (p_book_type_code, p_asset_id);
2136
2137 LOOP
2138 FETCH lcr_books
2139 INTO lr_books;
2140
2141 EXIT WHEN lcr_books%NOTFOUND;
2142
2143 --*****************************
2144 -- Derive Depreciation Amount
2145 --*****************************
2146 OPEN lcr_deprn_amount (p_book_type_code
2147 , lr_books.asset_id
2148 , lr_books.period_counter_fully_reserved
2149 );
2150
2151 FETCH lcr_deprn_amount
2152 INTO l_deprn_amount;
2153
2154 CLOSE lcr_deprn_amount;
2155
2156 -- Step 4: Derive End Date for the period when the Extended depreciation is scheduled to start
2157 OPEN lcr_end_date_validate (p_book_type_code
2158 , l_extended_date
2159 , 1 -- Bug#8626718:Passing pc next to fully reserved pc.
2160 );
2161
2162 FETCH lcr_end_date_validate
2163 INTO l_end_date_validate;
2164
2165 CLOSE lcr_end_date_validate;
2166
2167 -- Step 4: Derive End Date for the period when p_number_of_periods parameter ends starting from start period
2168 OPEN lcr_end_date_check (p_book_type_code
2169 , lc_period
2170 , p_number_of_periods
2171 );
2172
2173 FETCH lcr_end_date_check
2174 INTO l_end_date_check;
2175
2176 CLOSE lcr_end_date_check;
2177
2178 OPEN lcr_periods (p_book_type_code, l_end_date_validate);
2179
2180 FETCH lcr_periods
2181 INTO l_extd_period_name;
2182
2183 CLOSE lcr_periods;
2184
2185 --**************************************p
2186 -- Derive First Eligible Period Name
2187 --**************************************
2188 OPEN lcr_first_periods (p_book_type_code
2189 , l_extd_period_name
2190 --p_start_period -- Period When fully reserved
2191 , p_number_of_periods
2192 );
2193
2194 FETCH lcr_first_periods
2195 INTO l_first_eligible_period;
2196
2197 CLOSE lcr_first_periods;
2198
2199 ln_first_elg_period :=
2200 ret_counter (p_book_type_code, l_first_eligible_period);
2201 ln_first_beg_period :=
2202 ret_counter (p_book_type_code, p_first_begin_period);
2203
2204 --Step 3 Validation for Date Placed in Service
2205 IF lr_books.date_placed_in_service <
2206 TO_DATE ('01-04-2007', 'DD-MM-RRRR')
2207 THEN
2208 IF l_end_date_check > l_end_date_validate
2209 THEN -- Step 4
2210 IF p_first_begin_period IS NOT NULL
2211 THEN
2212 -- STEP 5 VALIDATION using the concept for Period Counter
2213 --IF (TO_DATE(l_first_eligible_period,'MM-RRRR') > TO_DATE(p_first_begin_period,'MM-RRRR') ) THEN
2214 IF ln_first_elg_period > ln_first_beg_period
2215 THEN
2216 --Step 8 Insert data into FA_WHATIF_ITF
2217 insert_fa_whatif_itf
2218 (x_errbuf => lc_errmsg
2219 , x_retcode => ln_retcode
2220 , p_request_id => p_request_id
2221 , p_checkbox_check => p_full_rsrv_checkbox
2222 , p_book_type_code => p_book_type_code
2223 , p_number_of_periods => p_number_of_periods
2224 , p_asset_id => lr_books.asset_id
2225 , p_period_name => lc_period
2226 , p_first_begin_prd => l_first_eligible_period
2227 --lr_books.period_name
2228 , p_dep_amt => lr_books.deprn_value
2229 --l_deprn_amount
2230 , p_dep_amt_annual => lr_books.annual_deprn_value
2231 , p_date_placed_in_service => lr_books.date_placed_in_service
2232 , p_life_in_months => lr_books.life_in_months
2233 , p_original_cost => lr_books.original_cost
2234 , p_asset_number => lr_books.asset_number
2235 , p_description => lr_books.description
2236 , p_tag_number => lr_books.tag_number
2237 , p_serial_number => lr_books.serial_number
2238 , p_location => lr_books.LOCATION
2239 , p_expense_account => lr_books.expense_account
2240 , p_round_value => lr_books.round_value
2241 , p_deprn_value => ln_acc_deprn
2242 --lr_books.Acc_Deprn_Value
2243 , p_flag => 'Y'
2244 );
2245
2246 IF ln_retcode <> 1
2247 THEN
2248 RAISE ex_user_exception;
2249 END IF;
2250 ELSE
2251 --Calculating extended depreciation starting from the first period provided in the form
2252 --Step 8 Insert data into FA_WHATIF_ITF
2253 insert_fa_whatif_itf
2254 (x_errbuf => lc_errmsg
2255 , x_retcode => ln_retcode
2256 , p_request_id => p_request_id
2257 , p_checkbox_check => p_full_rsrv_checkbox
2258 , p_book_type_code => p_book_type_code
2259 , p_number_of_periods => p_number_of_periods
2260 , p_asset_id => lr_books.asset_id
2261 , p_period_name => lc_period
2262 --lr_books.period_name
2263 , p_first_begin_prd => p_first_begin_period
2264 , p_dep_amt => lr_books.deprn_value
2265 --l_deprn_amount
2266 , p_dep_amt_annual => lr_books.annual_deprn_value
2267 , p_date_placed_in_service => lr_books.date_placed_in_service
2268 , p_life_in_months => lr_books.life_in_months
2269 , p_original_cost => lr_books.original_cost
2270 , p_asset_number => lr_books.asset_number
2271 , p_description => lr_books.description
2272 , p_tag_number => lr_books.tag_number
2273 , p_serial_number => lr_books.serial_number
2274 , p_location => lr_books.LOCATION
2275 , p_expense_account => lr_books.expense_account
2276 , p_round_value => lr_books.round_value
2277 , p_deprn_value => ln_acc_deprn
2278 --lr_books.Acc_Deprn_Value
2279 , p_flag => 'Y'
2280 );
2281
2282 IF ln_retcode <> 1
2283 THEN
2284 RAISE ex_user_exception;
2285 END IF;
2286 END IF;
2287 --IF (p_first_begin_period > p_end_asset_date) THEN
2288 --Step 6 calculate depreciation projections based on Period When Fully Reserved
2289 ELSE -- IF p_first_begin_period IS NOT NULL THEN
2290 --Step 8 Insert data into FA_WHATIF_ITF
2291 insert_fa_whatif_itf
2292 (x_errbuf => lc_errmsg
2293 , x_retcode => ln_retcode
2294 , p_request_id => p_request_id
2295 , p_checkbox_check => p_full_rsrv_checkbox
2296 , p_book_type_code => p_book_type_code
2297 , p_number_of_periods => p_number_of_periods
2298 , p_asset_id => lr_books.asset_id
2299 , p_period_name => lc_period
2300 --p_start_period
2301 , p_first_begin_prd => l_first_eligible_period
2302 --lr_books.period_name
2303 , p_dep_amt => lr_books.deprn_value
2304 --l_deprn_amount
2305 , p_dep_amt_annual => lr_books.annual_deprn_value
2306 , p_date_placed_in_service => lr_books.date_placed_in_service
2307 , p_life_in_months => lr_books.life_in_months
2308 , p_original_cost => lr_books.original_cost
2309 , p_asset_number => lr_books.asset_number
2310 , p_description => lr_books.description
2311 , p_tag_number => lr_books.tag_number
2312 , p_serial_number => lr_books.serial_number
2313 , p_location => lr_books.LOCATION
2314 , p_expense_account => lr_books.expense_account
2315 , p_round_value => lr_books.round_value
2316 , p_deprn_value => ln_acc_deprn
2317 --lr_books.Acc_Deprn_Value
2318 , p_flag => 'Y'
2319 );
2320
2321 IF ln_retcode <> 1
2322 THEN
2323 RAISE ex_user_exception;
2324 END IF;
2325 END IF; -- IF p_first_begin_period IS NOT NULL THEN
2326 END IF; --IF l_end_date_check > l_end_date_check THEN
2327 END IF;
2328 --IF lr_books.date_placed_in_service < TO_DATE('01-04-2007','DD-MM-RRRR')
2329 END LOOP;
2330 END IF; --IF p_checkbox_check IN (('YES'),('Y'))) THEN
2331 END IF; --IF l_profile_tax_reform = 'Y' THEN
2332 EXCEPTION
2333 WHEN ex_user_exception
2334 THEN
2335 x_retcode := gn_error_status;
2336 x_errbuf := lc_errmsg;
2337 fnd_file.put_line (fnd_file.output, x_errbuf);
2338 fnd_file.put_line (fnd_file.LOG, x_errbuf);
2339 WHEN OTHERS
2340 THEN
2341 x_retcode := gn_error_status;
2342 x_errbuf := lc_errmsg;
2343 ROLLBACK;
2344 END extd_deprn_main;
2345
2346 PROCEDURE deprn_main (
2347 x_errbuf OUT NOCOPY VARCHAR2
2348 , x_retcode OUT NOCOPY NUMBER
2349 , p_request_id IN NUMBER
2350 , p_book_type_code IN fa_books.book_type_code%TYPE
2351 , p_first_begin_period IN VARCHAR2
2352 , p_number_of_periods IN NUMBER
2353 , p_start_period IN VARCHAR2
2354 , p_checkbox_check IN VARCHAR2
2355 , p_full_rsrv_checkbox IN VARCHAR2
2356 , p_asset_id IN NUMBER
2357 )
2358 IS
2359 CURSOR lcr_books (
2360 p_book_type_code IN fa_books.book_type_code%TYPE
2361 , p_asset_id IN fa_books.asset_id%TYPE
2362 )
2363 /***********************************************************************
2364 *
2365 * CURSOR
2366 lcr_books
2367 *
2368 * DESCRIPTION
2369 * Cursor lcr_books is a private cursor of procedure whatif_main.
2370 * Cursor will return only the Book Details which are Active for a particular Book Type
2371 *
2372 * PARAMETERS
2373 * ==========
2374 * NAME TYPE DESCRIPTION
2375 * ----------------- -------- ------------------------------------------
2376 * p_book_type_code
2377 *
2378 *
2379 * None
2380 *
2381 * PREREQUISITES
2382 * None
2383 *
2384 * CALLED BY
2385 * deprn_main
2386 *
2387 ***********************************************************************/
2388 IS
2389 SELECT fb.period_counter_fully_reserved
2390 , fb.asset_id
2391 FROM fa_books fb
2392 WHERE fb.book_type_code = p_book_type_code
2393 AND fb.date_ineffective IS NULL
2394 AND fb.transaction_header_id_out IS NULL
2395 AND fb.asset_id = p_asset_id
2396 AND fb.deprn_method_code <> 'JP-STL-EXTND'
2397 AND fb.allowed_deprn_limit_amount > 1;
2398
2399 lr_books lcr_books%ROWTYPE;
2400 lc_errmsg VARCHAR2 (4000);
2401 ln_retcode NUMBER;
2402 ex_user_exception EXCEPTION;
2403 BEGIN
2404 OPEN lcr_books (p_book_type_code, p_asset_id);
2405
2406 LOOP
2407 FETCH lcr_books
2408 INTO lr_books;
2409
2410 EXIT WHEN lcr_books%NOTFOUND;
2411
2412 IF lr_books.period_counter_fully_reserved IS NOT NULL
2413 THEN
2414 whatif_main (x_errbuf => lc_errmsg
2415 , x_retcode => ln_retcode
2416 , p_request_id => p_request_id
2417 , p_book_type_code => p_book_type_code
2418 , p_first_begin_period => p_first_begin_period
2419 , p_number_of_periods => p_number_of_periods
2420 , p_start_period => p_start_period
2421 , p_checkbox_check => p_checkbox_check
2422 , p_full_rsrv_checkbox => p_full_rsrv_checkbox
2423 , p_asset_id => lr_books.asset_id
2424 );
2425
2426 IF ln_retcode <> 1
2427 THEN
2428 RAISE ex_user_exception;
2429 END IF;
2430 ELSE
2431 extd_deprn_main (x_errbuf => lc_errmsg
2432 , x_retcode => ln_retcode
2433 , p_request_id => p_request_id
2434 , p_book_type_code => p_book_type_code
2435 , p_first_begin_period => p_first_begin_period
2436 , p_number_of_periods => p_number_of_periods
2437 , p_start_period => p_start_period
2438 , p_checkbox_check => p_checkbox_check
2439 , p_full_rsrv_checkbox => p_full_rsrv_checkbox
2440 , p_asset_id => lr_books.asset_id
2441 );
2442
2443 IF ln_retcode <> 1
2444 THEN
2445 RAISE ex_user_exception;
2446 END IF;
2447 END IF; -- IF lr_books.period_counter_fully_reserved IS NOT NULL THEN
2448 END LOOP;
2449 EXCEPTION
2450 WHEN ex_user_exception
2451 THEN
2452 x_retcode := gn_error_status;
2453 x_errbuf := lc_errmsg;
2454 fnd_file.put_line (fnd_file.output, x_errbuf);
2455 fnd_file.put_line (fnd_file.LOG, x_errbuf);
2456 END deprn_main;
2457
2458 PROCEDURE calc_jp250db (
2459 x_request_id NUMBER
2460 , x_asset_id NUMBER
2461 , x_book VARCHAR2
2462 , x_method VARCHAR2
2463 , x_cost NUMBER
2464 , x_cur_cost NUMBER
2465 , x_life NUMBER
2466 , x_rate_in_use NUMBER
2467 , x_deprn_lmt NUMBER
2468 , x_start_prd VARCHAR2
2469 , x_dtin_serv VARCHAR2
2470 , x_num_per NUMBER
2471 )
2472 IS
2473 CURSOR lcu_assets (
2474 x_reuest_id NUMBER
2475 , x_book VARCHAR2
2476 , x_asset_id NUMBER
2477 , x_dep NUMBER
2478 )
2479 IS
2480 SELECT request_id
2481 , book_type_code
2482 , asset_id
2483 , asset_number
2484 , description
2485 , tag_number
2486 , serial_number
2487 , period_name
2488 , fiscal_year
2489 , expense_acct
2490 , LOCATION
2491 , units
2492 , employee_name
2493 , employee_number
2494 , asset_key
2495 , current_cost
2496 , current_prorate_conv
2497 , current_method
2498 , current_life
2499 , current_basic_rate
2500 , current_adjusted_rate
2501 , current_salvage_value
2502 , depreciation
2503 , new_depreciation
2504 , created_by
2505 , creation_date
2506 , last_update_date
2507 , last_updated_by
2508 , last_update_login
2509 , date_placed_in_service
2510 , CATEGORY
2511 , accumulated_deprn
2512 , bonus_depreciation
2513 , new_bonus_depreciation
2514 , current_bonus_rule
2515 , period_num
2516 , currency_code
2517 FROM fa_whatif_itf
2518 WHERE request_id = x_request_id
2519 AND book_type_code = x_book
2520 AND asset_id = x_asset_id
2521 AND EXISTS (
2522 SELECT fiscal_year
2523 FROM fa_whatif_itf
2524 WHERE request_id = x_request_id
2525 AND book_type_code = x_book
2526 AND asset_id = x_asset_id)
2527 ORDER BY fiscal_year ASC;
2528
2529 CURSOR lcu_periods (
2530 x_book_type VARCHAR2
2531 , x_start_period VARCHAR2
2532 , x_num_periods NUMBER
2533 )
2534 IS
2535 SELECT cp1.period_name period_name
2536 , fa_jp_tax_extn_pvt.ret_counter (x_book_type, cp1.period_name)
2537 counter
2538 , cp1.period_num
2539 , bc.fiscal_year_name
2540 FROM fa_calendar_periods cp
2541 , fa_book_controls bc
2542 , fa_deprn_periods dp
2543 , fa_calendar_periods cp1
2544 , (SELECT MAX (cp.start_date) max_start_date
2545 FROM fa_calendar_periods cp
2546 , fa_calendar_periods cp1
2547 , fa_book_controls bc
2548 , fa_deprn_periods dp
2549 WHERE dp.book_type_code = x_book_type
2550 AND dp.period_close_date IS NULL
2551 AND dp.calendar_period_open_date <= cp.start_date
2552 AND cp.calendar_type = bc.deprn_calendar
2553 AND bc.book_type_code = x_book_type
2554 AND bc.deprn_calendar = cp1.calendar_type
2555 AND cp.start_date >= cp1.start_date
2556 AND cp1.period_name = x_start_period
2557 AND ROWNUM <= x_num_periods) x
2558 WHERE dp.book_type_code = x_book_type
2559 AND dp.period_close_date IS NULL
2560 AND dp.calendar_period_open_date <= cp.start_date
2561 AND cp.calendar_type = bc.deprn_calendar
2562 AND bc.book_type_code = x_book_type
2563 AND bc.deprn_calendar = cp1.calendar_type
2564 AND cp1.start_date >= cp.start_date
2565 AND cp.period_name = x_start_period
2566 AND cp1.start_date <= x.max_start_date;
2567
2568 CURSOR lcu_backdt_ast (
2569 x_book_type VARCHAR2
2570 )
2571 IS
2572 SELECT th.asset_id
2573 FROM fa_calendar_periods cp
2574 , fa_deprn_periods dp
2575 , fa_transaction_headers th
2576 , fa_book_controls bc
2577 WHERE dp.book_type_code = bc.book_type_code
2578 AND cp.calendar_type = bc.deprn_calendar
2579 AND th.book_type_code = dp.book_type_code
2580 AND th.transaction_date_entered BETWEEN cp.start_date AND cp.end_date
2581 AND th.date_effective BETWEEN dp.period_open_date
2582 AND NVL (dp.period_close_date
2583 , th.date_effective)
2584 AND bc.book_type_code = x_book_type
2585 AND cp.period_name <> dp.period_name
2586 AND th.transaction_type_code = 'ADDITION';
2587
2588 lt_nbv_typ tp_nbv_typ;
2589 l_nop NUMBER;
2590 h_nop NUMBER;
2591 h_cntr1 BINARY_INTEGER := 1;
2592 h_cntr2 BINARY_INTEGER := 1;
2593 h_per NUMBER;
2594 h_orig_rate NUMBER;
2595 h_rev_rate NUMBER;
2596 h_guarant_rate NUMBER;
2597 ln_nbv NUMBER;
2598 ln_nbv1 NUMBER;
2599 ln_temp3 NUMBER;
2600 lc_period VARCHAR2 (30);
2601 h_period VARCHAR2 (30);
2602 l_dep NUMBER;
2603 g_dep NUMBER;
2604 ln_acc_deprn NUMBER := 0;
2605 ln_int_deprn NUMBER := 0;
2606 gn_acc_deprn NUMBER := 0;
2607 l_gnacc_deprn NUMBER := 0; -- added to chk life end depreciation #7174535
2608 l_temp NUMBER;
2609 gn_deprn NUMBER;
2610 h_rate NUMBER;
2611 h_cost NUMBER;
2612 ln_temp1 NUMBER;
2613 l_ct NUMBER;
2614 h_rate_in_use NUMBER;
2615 l_cnt NUMBER := 1;
2616 l_cnt1 NUMBER := 1;
2617 l_tmp NUMBER := 1;
2618 l_tmp4 NUMBER := 1;
2619 ln_dep_dumy NUMBER := 0;
2620 l_cnt2 NUMBER := 0;
2621 l_dtin_serv VARCHAR2 (15);
2622 l_dtcntr NUMBER;
2623 l_strt_cntr NUMBER;
2624 l_opn_cntr NUMBER;
2625 h_period_cnt NUMBER;
2626 l_end_percnt NUMBER;
2627 ln_stcnt NUMBER;
2628 y_dep NUMBER;
2629 l_per_dum NUMBER;
2630 l_count_dum NUMBER := 0;
2631 h_cache BOOLEAN;
2632 l_bdast_flg NUMBER := 0;
2633 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
2634 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
2635 adj_err EXCEPTION;
2636 BEGIN
2637 IF x_book IS NOT NULL
2638 AND fnd_profile.VALUE ('FA_JAPAN_TAX_REFORMS') = 'Y'
2639 THEN
2640 h_cache :=
2641 fa_cache_pkg.fazccmt (x_method, x_life);
2642 lt_whatitf.DELETE;
2643 lt_nbv_typ.DELETE;
2644 h_rate_in_use := x_rate_in_use;
2645 h_orig_rate :=
2646 fa_cache_pkg.fazcfor_record.original_rate;
2647 h_rev_rate :=
2648 fa_cache_pkg.fazcfor_record.revised_rate;
2649 h_guarant_rate :=
2650 fa_cache_pkg.fazcfor_record.guarantee_rate;
2651
2652 BEGIN
2653 SELECT number_per_fiscal_year
2654 INTO h_nop
2655 FROM fa_calendar_types fc
2656 , fa_book_controls fb
2657 WHERE fc.calendar_type = fb.deprn_calendar
2658 AND fb.book_type_code = x_book;
2659 END;
2660
2661 h_cost :=
2662 ROUND ((x_cost * h_guarant_rate) / h_nop, 0);
2663
2664 -- load all periods into temp plsql table
2665 FOR lc_asset IN lcu_assets (x_request_id
2666 , x_book
2667 , x_asset_id
2668 , h_cost
2669 )
2670 LOOP
2671 EXIT WHEN lcu_assets%NOTFOUND;
2672 lt_whatitf (h_cntr2).request_id := lc_asset.request_id;
2673 lt_whatitf (h_cntr2).book_type_code := lc_asset.book_type_code;
2674 lt_whatitf (h_cntr2).asset_id := lc_asset.asset_id;
2675 lt_whatitf (h_cntr2).asset_number := lc_asset.asset_number;
2676 lt_whatitf (h_cntr2).period_name := lc_asset.period_name;
2677 lt_whatitf (h_cntr2).period_counter :=
2678 ret_counter (x_book, lc_asset.period_name);
2679 lt_whatitf (h_cntr2).fiscal_year := lc_asset.fiscal_year;
2680 lt_whatitf (h_cntr2).units := lc_asset.units;
2681 lt_whatitf (h_cntr2).current_method := lc_asset.current_method;
2682 lt_whatitf (h_cntr2).current_life := lc_asset.current_life;
2683 lt_whatitf (h_cntr2).depreciation := lc_asset.depreciation;
2684 lt_whatitf (h_cntr2).new_depreciation :=
2685 lc_asset.new_depreciation;
2686 lt_whatitf (h_cntr2).date_placed_in_service :=
2687 lc_asset.date_placed_in_service;
2688 lt_whatitf (h_cntr2).accumulated_deprn :=
2689 lc_asset.accumulated_deprn;
2690 lt_whatitf (h_cntr2).period_num := lc_asset.period_num;
2691 h_cntr2 := h_cntr2 + 1;
2692 END LOOP;
2693
2694 BEGIN
2695 SELECT period_name
2696 INTO lc_period
2697 FROM fa_deprn_periods
2698 WHERE book_type_code = x_book
2699 AND period_close_date IS NULL;
2700 EXCEPTION
2701 WHEN NO_DATA_FOUND
2702 THEN
2703 lc_period := NULL;
2704 END;
2705
2706 BEGIN
2707 SELECT fc.period_name
2708 INTO l_dtin_serv
2709 FROM fa_calendar_periods fc
2710 , fa_book_controls fb
2711 WHERE fc.calendar_type = fb.deprn_calendar
2712 AND fb.book_type_code = x_book
2713 AND TRUNC (TO_DATE (x_dtin_serv, 'dd/mm/rrrr'))
2714 BETWEEN fc.start_date
2715 AND fc.end_date;
2716 EXCEPTION
2717 WHEN OTHERS
2718 THEN
2719 l_dtin_serv := NULL;
2720 END;
2721
2722 l_dtcntr := ret_counter (x_book, l_dtin_serv);
2723 l_strt_cntr := ret_counter (x_book, x_start_prd);
2724 l_opn_cntr := ret_counter (x_book, lc_period);
2725
2726 -- calc nop for accdeprn
2727 IF l_strt_cntr = l_dtcntr
2728 OR l_strt_cntr = l_opn_cntr
2729 OR l_dtcntr = l_opn_cntr
2730 THEN
2731 l_nop := x_num_per;
2732 h_period := x_start_prd;
2733 -- modified for #7174543
2734 FOR lc_backdt_ast IN lcu_backdt_ast (x_book)
2735 LOOP
2736 IF lc_backdt_ast.asset_id = x_asset_id
2737 THEN
2738 BEGIN
2739 SELECT COUNT (1)
2740 INTO ln_temp1
2741 FROM fa_deprn_summary
2742 WHERE asset_id = x_asset_id
2743 AND deprn_source_code = 'DEPRN';
2744 END;
2745 IF ln_temp1 = 0
2746 THEN
2747 l_bdast_flg := 1;
2748 END IF;
2749 END IF;
2750 END LOOP;
2751 ELSE
2752 l_temp := ABS (l_opn_cntr - l_strt_cntr);
2753
2754 IF l_temp > 0
2755 THEN
2756 l_nop := x_num_per - l_temp;
2757 h_period := lc_period;
2758 ELSIF l_temp < 0
2759 THEN
2760 l_nop := ABS (l_temp) + x_num_per;
2761 h_period := lc_period;
2762 END IF;
2763
2764 FOR lc_backdt_ast IN lcu_backdt_ast (x_book)
2765 LOOP
2766 IF lc_backdt_ast.asset_id = x_asset_id
2767 THEN
2768 BEGIN
2769 SELECT COUNT (1)
2770 INTO ln_temp1
2771 FROM fa_deprn_summary
2772 WHERE asset_id = x_asset_id
2773 AND deprn_source_code = 'DEPRN';
2774 END;
2775
2776 IF ln_temp1 = 0
2777 THEN
2778 l_temp := ABS (l_opn_cntr - l_strt_cntr);
2779 l_nop := x_num_per + l_temp;
2780 h_period := lc_period;
2781 l_bdast_flg := 1; -- modified for #7174543
2782 END IF;
2783 END IF;
2784 END LOOP;
2785 END IF;
2786
2787 /*Bug 10155740*/
2788 l_asset_hdr_rec.asset_id := x_asset_id;
2789 l_asset_hdr_rec.book_type_code := x_book;
2790
2791 if not FA_UTIL_PVT.get_asset_deprn_rec
2792 (p_asset_hdr_rec => l_asset_hdr_rec ,
2793 px_asset_deprn_rec => l_asset_deprn_rec,
2794 p_period_counter => NULL,
2795 p_mrc_sob_type_code => NULL
2796 ) then raise adj_err;
2797 end if;
2798
2799 ln_int_deprn := l_asset_deprn_rec.deprn_reserve;
2800
2801 gn_acc_deprn := gn_acc_deprn + ln_int_deprn;
2802 l_gnacc_deprn := l_gnacc_deprn + ln_int_deprn; -- added to chk life end depreciation #7174535
2803
2804 IF ((x_cur_cost * h_guarant_rate) <
2805 (x_cur_cost - gn_acc_deprn) * h_orig_rate
2806 )
2807 THEN
2808 h_rate := h_orig_rate;
2809 ELSE
2810 h_rate := h_rev_rate;
2811 h_rate_in_use := h_rev_rate;
2812 END IF;
2813
2814 BEGIN
2815 SELECT DECODE (adjusted_cost
2816 , COST, 1
2817 , 0
2818 )
2819 INTO l_tmp
2820 FROM fa_books
2821 WHERE book_type_code = x_book
2822 AND date_ineffective IS NULL
2823 AND period_counter_fully_reserved IS NULL
2824 AND asset_id = x_asset_id;
2825 END;
2826
2827 FOR lc_periods IN lcu_periods (x_book
2828 , h_period
2829 , l_nop
2830 )
2831 LOOP -- each period
2832 -- findout the rate to calculate deprn
2833 h_per := chk_period (lc_periods.period_name, x_book);
2834
2835 IF h_per = 1
2836 THEN
2837 IF ((x_cur_cost * h_guarant_rate) <
2838 (x_cur_cost - gn_acc_deprn
2839 ) * h_orig_rate
2840 )
2841 THEN
2842 h_rate := h_orig_rate;
2843 ELSE
2844 h_rate := h_rev_rate;
2845 h_rate_in_use := h_rev_rate;
2846 END IF;
2847
2848 BEGIN
2849 SELECT deprn_amount
2850 , ROUND (deprn_reserve, 0)
2851 INTO l_dep
2852 , g_dep
2853 FROM fa_deprn_summary fds
2854 , fa_deprn_periods fdp
2855 WHERE fds.period_counter = fdp.period_counter
2856 AND fds.book_type_code = fdp.book_type_code
2857 AND fds.asset_id = x_asset_id
2858 AND fdp.book_type_code = x_book
2859 AND fdp.period_name =
2860 (SELECT cp.period_name
2861 FROM fa_calendar_periods cp
2862 , fa_fiscal_year fy
2863 , fa_book_controls fb
2864 WHERE cp.calendar_type = fb.deprn_calendar
2865 AND fb.book_type_code = x_book
2866 AND fb.fiscal_year_name = fy.fiscal_year_name
2867 AND cp.period_num = 1
2868 AND fy.fiscal_year =
2869 (SELECT fy.fiscal_year
2870 FROM fa_calendar_periods cp
2871 , fa_fiscal_year fy
2872 , fa_book_controls fb
2873 WHERE cp.period_name = lc_period
2874 AND cp.calendar_type =
2875 fb.deprn_calendar
2876 AND fb.book_type_code = x_book
2877 AND fb.fiscal_year_name =
2878 fy.fiscal_year_name
2879 AND cp.start_date
2880 BETWEEN fy.start_date
2881 AND fy.end_date)
2882 AND cp.start_date BETWEEN fy.start_date
2883 AND fy.end_date);
2884 EXCEPTION
2885 WHEN OTHERS
2886 THEN
2887 l_dep := ln_dep_dumy;
2888 g_dep := 0;
2889 END;
2890
2891 -- commented for #7161938 and 7161740
2892 /* IF h_rate = h_rev_rate
2893 THEN
2894 IF l_cnt2 = 0
2895 AND l_tmp <> 1
2896 THEN
2897 gn_acc_deprn := gn_acc_deprn + NVL(l_ct,0);
2898 l_cnt2 := l_cnt2 + 1;
2899 END IF;
2900 END IF; */
2901 IF l_tmp = 1
2902 THEN
2903 IF h_rate <> h_rev_rate
2904 THEN
2905 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn;
2906 l_gnacc_deprn := l_gnacc_deprn + ln_acc_deprn; -- added to chk life end depreciation #7174535
2907 END IF;
2908
2909 ln_nbv := x_cur_cost - gn_acc_deprn;
2910 ln_acc_deprn := TRUNC (ln_nbv * h_rate, 0); -- round changed to trunc 7174535
2911 ELSIF l_tmp = 0
2912 THEN
2913 IF l_cnt1 = 1
2914 THEN
2915 IF chk_period (x_start_prd, x_book) = 1
2916 THEN
2917 IF g_dep > 0
2918 AND l_dep > 0
2919 THEN
2920 gn_acc_deprn := g_dep - l_dep;
2921 l_gnacc_deprn := g_dep - l_dep; -- added to chk life end depreciation #7174535
2922 END IF;
2923
2924 ln_nbv := x_cost;
2925 ELSE
2926 IF h_rate <> h_rev_rate
2927 THEN
2928 gn_acc_deprn := gn_acc_deprn + (ln_dep_dumy * h_nop);
2929 l_gnacc_deprn := l_gnacc_deprn + (ln_dep_dumy * h_nop); -- added to chk life end depreciation #7174535
2930 ln_nbv := x_cur_cost - gn_acc_deprn;
2931 ELSE
2932 ln_nbv := ln_nbv1;
2933 END IF;
2934 END IF;
2935
2936 ln_acc_deprn := TRUNC (ln_nbv * h_rate, 0); -- round changed to trunc 7174535
2937 l_cnt1 := l_cnt1 + 1;
2938 ELSE
2939 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn;
2940 l_gnacc_deprn := l_gnacc_deprn + ln_acc_deprn; -- added to chk life end depreciation #7174535
2941 ln_nbv := x_cur_cost - gn_acc_deprn;
2942 ln_acc_deprn := TRUNC (ln_nbv * h_rate, 0); -- round changed to trunc 7174535
2943 END IF;
2944 END IF;
2945
2946 IF h_orig_rate = h_rate_in_use
2947 THEN
2948 lt_nbv_typ (h_cntr1).period := lc_periods.period_name;
2949 lt_nbv_typ (h_cntr1).period_cntr := lc_periods.counter;
2950 lt_nbv_typ (h_cntr1).nbv_value := ln_nbv;
2951 lt_nbv_typ (h_cntr1).asset_id := x_asset_id;
2952 lt_nbv_typ (h_cntr1).deprn_value :=
2953 ROUND (TRUNC ((lt_nbv_typ (h_cntr1).nbv_value * h_rate
2954 )
2955 , 0)
2956 * (1 / h_nop)
2957 , 0);
2958 lt_nbv_typ (h_cntr1).new_depreciation :=
2959 ROUND (TRUNC ((lt_nbv_typ (h_cntr1).nbv_value * h_rate))
2960 * (1 / h_nop)
2961 , 0);
2962 gn_deprn := gn_deprn + lt_nbv_typ (h_cntr1).deprn_value;
2963 ELSE
2964 -- STL method
2965 h_rate := h_rev_rate;
2966 lt_nbv_typ (h_cntr1).period := lc_periods.period_name;
2967 lt_nbv_typ (h_cntr1).period_cntr := lc_periods.counter;
2968 lt_nbv_typ (h_cntr1).asset_id := x_asset_id;
2969 lt_nbv_typ (h_cntr1).deprn_value :=
2970 ROUND (TRUNC (ln_nbv * h_rate) * (1 / h_nop), 0);
2971 lt_nbv_typ (h_cntr1).new_depreciation :=
2972 ROUND (TRUNC (ln_nbv * h_rate) * (1 / h_nop), 0);
2973 gn_deprn := gn_deprn + lt_nbv_typ (h_cntr1).deprn_value;
2974 END IF;
2975
2976 IF h_rate <> h_rev_rate
2977 THEN
2978 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn;
2979 END IF;
2980 l_gnacc_deprn := l_gnacc_deprn + ln_acc_deprn; -- added to chk life end depreciation #7174535
2981 h_cntr1 := h_cntr1 + 1;
2982 ELSIF h_per = h_nop
2983 THEN
2984 IF gn_acc_deprn = 0
2985 AND (chk_period (x_start_prd, x_book) <> 1)
2986 AND l_tmp4 = 1
2987 THEN
2988 -- modified against 7174535
2989 IF y_dep <> 0 THEN
2990 gn_acc_deprn := x_cost * h_rate;
2991 l_gnacc_deprn := x_cost * h_rate; -- added to chk life end depreciation #7174535
2992 ELSE
2993 h_per := chk_period (x_start_prd, x_book);
2994 gn_acc_deprn := gn_acc_deprn + (nvl(ln_dep_dumy,0) * (h_nop - (h_per-1)));
2995 l_gnacc_deprn := l_gnacc_deprn + (nvl(ln_dep_dumy,0) * (h_nop - (h_per-1))); -- added to chk life end depreciation #7174535
2996 END IF;
2997
2998 IF ln_nbv1 IS NULL
2999 THEN
3000 ln_nbv1 := x_cur_cost - gn_acc_deprn;
3001 END IF;
3002 l_tmp4 := l_tmp4 + 1;
3003 END IF;
3004 -- added to calculate year end period depreciation #7174535
3005 IF l_count_dum = 0
3006 THEN
3007 BEGIN
3008 SELECT period_num
3009 INTO l_per_dum
3010 FROM fa_deprn_summary fds
3011 , fa_deprn_periods fdp
3012 WHERE fdp.book_type_code = fds.book_type_code
3013 AND fdp.period_counter = fds.period_counter
3014 AND fds.book_type_code = x_book
3015 AND fds.asset_id = x_asset_id
3016 AND fds.deprn_source_code = 'BOOKS';
3017 EXCEPTION
3018 WHEN OTHERS
3019 THEN
3020 l_per_dum := 1;
3021 END;
3022 l_count_dum := l_count_dum + 1;
3023 ELSE
3024 l_per_dum := 1;
3025 END IF;
3026
3027 IF l_per_dum = 1
3028 OR l_per_dum = h_nop
3029 THEN
3030 ln_temp3 :=
3031 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop), 0)
3032 * (h_per - 1);
3033 ELSE
3034 ln_temp3 := TRUNC ((ln_nbv1 * h_rate)
3035 * ((h_nop - l_per_dum + 1) / h_nop));
3036 END IF;
3037
3038 -- fa_round_pkg.fa_round (ln_temp3, x_book);
3039 gn_deprn := gn_deprn + ln_temp3;
3040 lt_nbv_typ (h_cntr1).period := lc_periods.period_name;
3041 lt_nbv_typ (h_cntr1).period_cntr := lc_periods.counter;
3042 lt_nbv_typ (h_cntr1).nbv_value := x_cur_cost - gn_acc_deprn;
3043 lt_nbv_typ (h_cntr1).asset_id := x_asset_id;
3044 -- added for year end rounding #7171947
3045 IF l_per_dum = 1
3046 OR l_per_dum = h_nop
3047 THEN
3048 lt_nbv_typ (h_cntr1).deprn_value :=
3049 TRUNC ((ln_nbv1 * h_rate), 0) - ln_temp3;
3050 lt_nbv_typ (h_cntr1).new_depreciation :=
3051 TRUNC ((ln_nbv1 * h_rate), 0) - ln_temp3;
3052 ELSE
3053 lt_nbv_typ (h_cntr1).deprn_value :=
3054 ln_temp3
3055 - ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop), 0)
3056 * (h_nop - l_per_dum);
3057 lt_nbv_typ (h_cntr1).new_depreciation :=
3058 ln_temp3
3059 - ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop), 0)
3060 * (h_nop - l_per_dum);
3061 END IF;
3062
3063 -- added against #7136958
3064 l_end_percnt := l_dtcntr + (x_life - 1);
3065
3066 -- modified for #7174543
3067 IF l_bdast_flg <> 1
3068 THEN
3069 IF lt_nbv_typ (h_cntr1).period_cntr = l_end_percnt
3070 AND l_gnacc_deprn <> (x_cur_cost - x_deprn_lmt)
3071 THEN
3072 gn_acc_deprn := gn_acc_deprn - x_deprn_lmt;
3073 lt_nbv_typ (h_cntr1).deprn_value :=
3074 lt_nbv_typ (h_cntr1).deprn_value - x_deprn_lmt;
3075 lt_nbv_typ (h_cntr1).new_depreciation :=
3076 lt_nbv_typ (h_cntr1).new_depreciation - x_deprn_lmt;
3077 END IF;
3078 END IF;
3079
3080 gn_deprn :=
3081 gn_deprn + lt_nbv_typ (h_cntr1).deprn_value;
3082 l_ct := ln_acc_deprn;
3083 gn_deprn := 0;
3084 ln_acc_deprn := 0;
3085 h_cntr1 := h_cntr1 + 1;
3086 ELSE
3087 IF ln_nbv > 0
3088 THEN
3089 ln_nbv1 := ln_nbv;
3090 ELSE
3091 h_per := chk_period (l_dtin_serv, x_book);
3092
3093 IF h_per = 1
3094 THEN
3095 BEGIN
3096 SELECT deprn_amount
3097 , ROUND (deprn_reserve, 0)
3098 INTO l_dep
3099 , g_dep
3100 FROM fa_deprn_summary fds
3101 , fa_deprn_periods fdp
3102 WHERE fds.period_counter = fdp.period_counter
3103 AND fds.book_type_code = fdp.book_type_code
3104 AND fds.asset_id = x_asset_id
3105 AND fdp.book_type_code = x_book
3106 AND fdp.period_name =
3107 (SELECT cp.period_name
3108 FROM fa_calendar_periods cp
3109 , fa_fiscal_year fy
3110 , fa_book_controls fb
3111 WHERE cp.calendar_type = fb.deprn_calendar
3112 AND fb.book_type_code = x_book
3113 AND fb.fiscal_year_name =
3114 fy.fiscal_year_name
3115 AND cp.period_num = 1
3116 AND fy.fiscal_year =
3117 (SELECT fy.fiscal_year
3118 FROM fa_calendar_periods cp
3119 , fa_fiscal_year fy
3120 , fa_book_controls fb
3121 WHERE cp.period_name =
3122 lc_period
3123 AND cp.calendar_type =
3124 fb.deprn_calendar
3125 AND fb.book_type_code =
3126 x_book
3127 AND fb.fiscal_year_name =
3128 fy.fiscal_year_name
3129 AND cp.start_date
3130 BETWEEN fy.start_date
3131 AND fy.end_date)
3132 AND cp.start_date BETWEEN fy.start_date
3133 AND fy.end_date);
3134 EXCEPTION
3135 WHEN OTHERS
3136 THEN
3137 l_dep := 0;
3138 g_dep := 0;
3139 END;
3140
3141 IF l_tmp = 0
3142 THEN
3143 gn_acc_deprn := x_cur_cost - x_cost;
3144 l_gnacc_deprn := x_cur_cost - x_cost; -- added to chk life end depreciation #7174535
3145 ELSE
3146 BEGIN
3147 SELECT fdp.period_counter
3148 INTO ln_stcnt
3149 FROM fa_deprn_periods fdp
3150 WHERE fdp.period_name =
3151 (SELECT cp.period_name
3152 FROM fa_calendar_periods cp
3153 , fa_fiscal_year fy
3154 , fa_book_controls fb
3155 WHERE cp.calendar_type =
3156 fb.deprn_calendar
3157 AND fb.book_type_code = x_book
3158 AND fb.fiscal_year_name =
3159 fy.fiscal_year_name
3160 AND cp.period_num = 1
3161 AND fy.fiscal_year =
3162 (SELECT fy.fiscal_year
3163 FROM fa_calendar_periods cp
3164 , fa_fiscal_year fy
3165 , fa_book_controls fb
3166 WHERE cp.period_name =
3167 lc_period
3168 AND cp.calendar_type =
3169 fb.deprn_calendar
3170 AND fb.book_type_code =
3171 x_book
3172 AND fb.fiscal_year_name =
3173 fy.fiscal_year_name
3174 AND cp.start_date
3175 BETWEEN fy.start_date
3176 AND fy.end_date)
3177 AND cp.start_date BETWEEN fy.start_date
3178 AND fy.end_date)
3179 AND fdp.book_type_code = x_book;
3180 END;
3181
3182 -- added against #7133749
3183 BEGIN
3184 SELECT deprn_amount
3185 , ytd_deprn
3186 , deprn_reserve
3187 INTO l_dep
3188 , y_dep
3189 , g_dep
3190 FROM fa_deprn_summary fds
3191 , fa_deprn_periods fdp
3192 WHERE fds.period_counter = fdp.period_counter
3193 AND fds.book_type_code = fdp.book_type_code
3194 AND fds.asset_id = x_asset_id
3195 AND fdp.book_type_code = x_book
3196 AND fdp.period_counter = l_opn_cntr - 1;
3197 EXCEPTION
3198 WHEN OTHERS
3199 THEN
3200 l_dep := 0;
3201 g_dep := 0;
3202 y_dep := 0;
3203 END;
3204 -- modified against 7174535
3205 --Manual entry assets
3206 gn_acc_deprn := g_dep - y_dep;
3207 l_gnacc_deprn := g_dep - y_dep; -- added to chk life end depreciation #7174535
3208 -- gn_acc_deprn := g_dep - (l_dep * ABS(l_opn_cntr - ln_stcnt)); #7171957
3209 END IF;
3210
3211 -- commented against #7133749
3212 -- IF h_rate <> h_rev_rate
3213 -- THEN
3214 ln_nbv1 := x_cur_cost - gn_acc_deprn;
3215 gn_acc_deprn := gn_acc_deprn + ln_acc_deprn;
3216 l_gnacc_deprn := l_gnacc_deprn + ln_acc_deprn; -- added to chk life end depreciation #7174535
3217 -- END IF;
3218 ELSE
3219 IF ((x_cur_cost * h_guarant_rate) <
3220 (x_cur_cost - gn_acc_deprn
3221 ) * h_orig_rate
3222 )
3223 THEN
3224 h_rate := h_orig_rate;
3225 ELSE
3226 h_rate := h_rev_rate;
3227 h_rate_in_use := h_rev_rate;
3228 END IF;
3229
3230 BEGIN
3231 SELECT deprn_amount
3232 , deprn_reserve
3233 INTO l_dep
3234 , g_dep
3235 FROM fa_deprn_summary fds
3236 , fa_deprn_periods fdp
3237 WHERE fds.period_counter = fdp.period_counter
3238 AND fds.book_type_code = fdp.book_type_code
3239 AND fds.asset_id = x_asset_id
3240 AND fdp.book_type_code = x_book
3241 AND fdp.period_name =
3242 (SELECT cp.period_name
3243 FROM fa_calendar_periods cp
3244 , fa_fiscal_year fy
3245 , fa_book_controls fb
3246 WHERE cp.calendar_type = fb.deprn_calendar
3247 AND fb.book_type_code = x_book
3248 AND fb.fiscal_year_name =
3249 fy.fiscal_year_name
3250 AND cp.period_num = 1
3251 AND fy.fiscal_year =
3252 (SELECT fy.fiscal_year
3253 FROM fa_calendar_periods cp
3254 , fa_fiscal_year fy
3255 , fa_book_controls fb
3256 WHERE cp.period_name =
3257 lc_period
3258 AND cp.calendar_type =
3259 fb.deprn_calendar
3260 AND fb.book_type_code =
3261 x_book
3262 AND fb.fiscal_year_name =
3263 fy.fiscal_year_name
3264 AND cp.start_date
3265 BETWEEN fy.start_date
3266 AND fy.end_date)
3267 AND cp.start_date BETWEEN fy.start_date
3268 AND fy.end_date);
3269 EXCEPTION
3270 WHEN OTHERS
3271 THEN
3272 l_dep := 0;
3273 g_dep := 0;
3274 END;
3275
3276 BEGIN
3277 SELECT DECODE (adjusted_cost
3278 , COST, 1
3279 , 0
3280 )
3281 INTO l_tmp
3282 FROM fa_books
3283 WHERE book_type_code = x_book
3284 AND date_ineffective IS NULL
3285 AND period_counter_fully_reserved IS NULL
3286 AND asset_id = x_asset_id;
3287 END;
3288
3289 IF l_tmp = 0
3290 THEN
3291 IF l_dep <> 0
3292 THEN
3293 gn_acc_deprn := g_dep - l_dep;
3294 l_gnacc_deprn := g_dep - l_dep; -- added to chk life end depreciation #7174535
3295 ELSE
3296 gn_acc_deprn := x_cur_cost - x_cost;
3297 l_gnacc_deprn := x_cur_cost - x_cost; -- added to chk life end depreciation #7174535
3298 END IF;
3299
3300 ln_nbv1 := x_cost;
3301
3302 ELSIF l_tmp = 1
3303 THEN
3304 IF h_rate <> h_rev_rate
3305 THEN
3306 gn_acc_deprn := ln_acc_deprn;
3307 l_gnacc_deprn := ln_acc_deprn; -- added to chk life end depreciation #7174535
3308 ln_nbv1 := x_cur_cost - gn_acc_deprn;
3309 END IF;
3310
3311 ln_temp3 :=
3312 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop), 0)
3313 * (h_nop - h_per + 1);
3314 -- fa_round_pkg.fa_round(ln_temp3,X_book);
3315 END IF;
3316 END IF;
3317 END IF;
3318
3319 IF h_orig_rate = h_rate_in_use
3320 THEN
3321 lt_nbv_typ (h_cntr1).period := lc_periods.period_name;
3322 lt_nbv_typ (h_cntr1).period_cntr := lc_periods.counter;
3323 lt_nbv_typ (h_cntr1).nbv_value := ln_nbv1;
3324 lt_nbv_typ (h_cntr1).asset_id := x_asset_id;
3325 lt_nbv_typ (h_cntr1).deprn_value :=
3326 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop)
3327 , 0);
3328 lt_nbv_typ (h_cntr1).new_depreciation :=
3329 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop)
3330 , 0);
3331 gn_deprn :=
3332 gn_deprn + lt_nbv_typ (h_cntr1).deprn_value;
3333 ln_dep_dumy :=
3334 (ln_nbv1 * h_rate)
3335 * (1 / h_nop);
3336 ELSE
3337 -- STL method
3338 h_rate := h_rev_rate;
3339 lt_nbv_typ (h_cntr1).period := lc_periods.period_name;
3340 lt_nbv_typ (h_cntr1).period_cntr := lc_periods.counter;
3341 lt_nbv_typ (h_cntr1).asset_id := x_asset_id;
3342 lt_nbv_typ (h_cntr1).deprn_value :=
3343 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop)
3344 , 0);
3345 lt_nbv_typ (h_cntr1).new_depreciation :=
3346 ROUND (TRUNC (ln_nbv1 * h_rate) * (1 / h_nop)
3347 , 0);
3348 gn_deprn :=
3349 gn_deprn + lt_nbv_typ (h_cntr1).deprn_value;
3350 ln_dep_dumy :=
3351 lt_nbv_typ (h_cntr1).deprn_value;
3352 END IF;
3353
3354 h_cntr1 := h_cntr1 + 1;
3355 END IF;
3356 END LOOP;
3357
3358 h_period := l_strt_cntr;
3359
3360 FOR k IN 1 .. lt_nbv_typ.COUNT
3361 LOOP
3362 IF lt_nbv_typ (k).period_cntr = l_strt_cntr
3363 THEN
3364 lt_nbv_typ (k).acc_deprn := lt_nbv_typ (k).deprn_value;
3365 h_period_cnt := l_strt_cntr + 1;
3366 ln_temp1 := lt_nbv_typ (k).acc_deprn;
3367 ELSIF lt_nbv_typ (k).period_cntr = h_period_cnt
3368 THEN
3369 lt_nbv_typ (k).acc_deprn :=
3370 ln_temp1 + lt_nbv_typ (k).deprn_value;
3371 h_period_cnt := h_period_cnt + 1;
3372 ln_temp1 := lt_nbv_typ (k).acc_deprn;
3373 END IF;
3374 --fa_round_pkg.fa_round (lt_nbv_typ (k).acc_deprn, x_book);
3375 END LOOP;
3376
3377 l_end_percnt := l_dtcntr + (x_life - 1);
3378 h_period_cnt := l_end_percnt + 1;
3379
3380 FOR j IN 1 .. lt_nbv_typ.COUNT
3381 LOOP
3382 IF lt_nbv_typ (j).asset_id = x_asset_id
3383 THEN
3384 FOR k IN 1 .. lt_whatitf.COUNT
3385 LOOP
3386 IF lt_whatitf (k).period_counter =
3387 lt_nbv_typ (j).period_cntr
3388 AND lt_whatitf (k).asset_id = lt_nbv_typ (j).asset_id
3389 THEN
3390 IF lt_whatitf (k).period_counter = l_end_percnt
3391 THEN
3392 -- Modified for #7174543
3393 IF l_bdast_flg <> 1
3394 THEN
3395 lt_whatitf (k).depreciation := lt_nbv_typ (j).deprn_value;
3396 lt_whatitf (k).new_depreciation := lt_nbv_typ (j).new_depreciation;
3397 lt_whatitf (k).accumulated_deprn := lt_nbv_typ (j).acc_deprn;
3398 ELSIF l_bdast_flg = 1
3399 THEN
3400 lt_whatitf (k).depreciation := lt_nbv_typ (j).deprn_value;
3401 lt_whatitf (k).new_depreciation := lt_nbv_typ (j).new_depreciation;
3402 lt_whatitf (k).accumulated_deprn := lt_nbv_typ (j).acc_deprn;
3403 l_end_percnt := l_opn_cntr + (x_life - 1);
3404 h_period_cnt := l_end_percnt + 1;
3405 END IF;
3406
3407 -- EXIT;
3408 ELSIF lt_whatitf (k).period_counter = h_period_cnt
3409 THEN
3410 lt_whatitf (k).depreciation := 0;
3411 lt_whatitf (k).new_depreciation := 0;
3412 lt_whatitf (k).accumulated_deprn := 0;
3413 h_period_cnt := h_period_cnt + 1;
3414 EXIT;
3415 ELSE
3416 IF lt_nbv_typ (j).deprn_value <= 1
3417 THEN
3418 lt_whatitf (k).depreciation := 0;
3419 lt_whatitf (k).new_depreciation := 0;
3420 ELSE
3421 lt_whatitf (k).depreciation :=
3422 lt_nbv_typ (j).deprn_value;
3423 lt_whatitf (k).new_depreciation :=
3424 lt_nbv_typ (j).new_depreciation;
3425 lt_whatitf (k).accumulated_deprn :=
3426 lt_nbv_typ (j).acc_deprn;
3427 END IF;
3428 END IF;
3429 END IF;
3430 END LOOP;
3431 END IF;
3432 END LOOP;
3433
3434 -- added against #7136958
3435 -- modified #7161938 and 7161740
3436 l_cnt1 := 1;
3437
3438 FOR j IN 1 .. lt_nbv_typ.COUNT
3439 LOOP
3440 FOR k IN 1 .. lt_whatitf.COUNT
3441 LOOP
3442 IF lt_nbv_typ (j).asset_id = x_asset_id
3443 AND ( lt_nbv_typ (j).acc_deprn >= x_cost
3444 OR lt_nbv_typ (j).acc_deprn >= x_cur_cost
3445 )
3446 AND lt_whatitf (k).period_counter =
3447 lt_nbv_typ (j).period_cntr
3448 AND lt_whatitf (k).asset_id = lt_nbv_typ (j).asset_id
3449 THEN
3450 IF l_cnt1 = 1
3451 AND lt_whatitf (k).accumulated_deprn = x_cost
3452 THEN
3453 lt_whatitf (k).depreciation :=
3454 lt_whatitf (k).depreciation - x_deprn_lmt;
3455 lt_whatitf (k).new_depreciation :=
3456 lt_whatitf (k).new_depreciation - x_deprn_lmt;
3457 l_cnt1 := l_cnt1 + 1;
3458 ELSE
3459 lt_whatitf (k).depreciation := 0;
3460 lt_whatitf (k).new_depreciation := 0;
3461 END IF;
3462 END IF;
3463 END LOOP;
3464 END LOOP;
3465
3466 l_cnt1 := 1;
3467
3468 FOR j IN 1 .. lt_whatitf.COUNT
3469 LOOP
3470 BEGIN
3471 --fa_round_pkg.fa_round (lt_whatitf (j).depreciation, x_book);
3472 --fa_round_pkg.fa_round (lt_whatitf (j).new_depreciation, x_book);
3473 --fa_round_pkg.fa_round (lt_whatitf (j).accumulated_deprn,x_book);
3474 UPDATE fa_whatif_itf
3475 SET depreciation = lt_whatitf (j).depreciation
3476 , new_depreciation = lt_whatitf (j).new_depreciation
3477 , accumulated_deprn = lt_whatitf (j).accumulated_deprn
3478 WHERE period_name = lt_whatitf (j).period_name
3479 AND asset_id = x_asset_id
3480 AND book_type_code = x_book
3481 AND request_id = lt_whatitf (j).request_id;
3482 EXCEPTION
3483 WHEN OTHERS
3484 THEN
3485 NULL;
3486 END;
3487 END LOOP;
3488
3489 COMMIT;
3490 END IF;
3491
3492 EXCEPTION
3493 WHEN ADJ_ERR THEN
3494 fa_srvr_msg.add_message(calling_fn => 'calc_jp250db', p_log_level_rec => null);
3495 END calc_jp250db;
3496
3497 FUNCTION chk_period (
3498 x_period VARCHAR2
3499 , x_book_type VARCHAR2
3500 )
3501 RETURN NUMBER
3502 IS
3503 h_periodnum NUMBER;
3504 BEGIN
3505 SELECT period_num
3506 INTO h_periodnum
3507 FROM fa_calendar_periods fc
3508 , fa_book_controls fb
3509 WHERE fc.calendar_type = fb.deprn_calendar
3510 AND fb.book_type_code = x_book_type
3511 AND period_name = x_period;
3512
3513 RETURN h_periodnum;
3514 EXCEPTION
3515 WHEN OTHERS
3516 THEN
3517 h_periodnum := NULL;
3518 RETURN h_periodnum;
3519 END chk_period;
3520
3521 FUNCTION ret_counter (
3522 x_book_typ VARCHAR2
3523 , x_periodname VARCHAR2
3524 )
3525 RETURN VARCHAR2
3526 IS
3527 l_dtcntr1 NUMBER;
3528 BEGIN
3529 SELECT (ffy.fiscal_year * fct.number_per_fiscal_year + fc.period_num)
3530 period_counter
3531 INTO l_dtcntr1
3532 FROM fa_calendar_periods fc
3533 , fa_book_controls fb
3534 , fa_fiscal_year ffy
3535 , fa_calendar_types fct
3536 WHERE fc.calendar_type = fb.deprn_calendar
3537 AND fb.book_type_code = x_book_typ
3538 AND ffy.fiscal_year_name = fb.fiscal_year_name
3539 AND ffy.fiscal_year_name = fct.fiscal_year_name
3540 AND fc.calendar_type = fct.calendar_type
3541 AND fct.calendar_type = fb.deprn_calendar
3542 AND fc.start_date >= ffy.start_date
3543 AND fc.end_date <= ffy.end_date
3544 AND fc.period_name = x_periodname;
3545
3546 RETURN l_dtcntr1;
3547 EXCEPTION
3548 WHEN OTHERS
3549 THEN
3550 l_dtcntr1 := NULL;
3551 RETURN l_dtcntr1;
3552 END;
3553 END fa_jp_tax_extn_pvt;