[Home] [Help]
PACKAGE BODY: APPS.FA_LEASE_SCHEDULE_PVT
Source
1 PACKAGE BODY FA_LEASE_SCHEDULE_PVT AS
2 /* $Header: FAVLSCB.pls 120.3 2005/07/25 10:06:07 yyoon noship $ */
3
4 -------------------------------------------
5 -- CHECK FOR PAYMENT SCHEDULE NAME
6 -------------------------------------------
7 FUNCTION CHECK_PAYMENT_SCHEDULE (
8 P_PAYMENT_SCHEDULE_NAME IN FA_LEASE_SCHEDULES.PAYMENT_SCHEDULE_NAME%TYPE,
9 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
10
11 CURSOR C_PAYMENT_SCHEDULE_ID IS
12 SELECT PAYMENT_SCHEDULE_ID
13 FROM FA_LEASE_SCHEDULES
14 WHERE PAYMENT_SCHEDULE_NAME = P_PAYMENT_SCHEDULE_NAME;
15
16 L_PAYMENT_SCHEDULE_ID NUMBER;
17 PAYMENT_SCHEDULE_FOUND EXCEPTION;
18
19 BEGIN
20 OPEN C_PAYMENT_SCHEDULE_ID ;
21
22 FETCH C_PAYMENT_SCHEDULE_ID
23 INTO L_PAYMENT_SCHEDULE_ID;
24
25 -------------------------------------------
26 -- IF PAYMENT SCHEDULE IS FOUND RAISE ERROR
27 -------------------------------------------
28 IF C_PAYMENT_SCHEDULE_ID%FOUND THEN
29 RAISE PAYMENT_SCHEDULE_FOUND;
30 END IF;
31
32 CLOSE C_PAYMENT_SCHEDULE_ID ;
33 RETURN (TRUE);
34
35 EXCEPTION
36 WHEN PAYMENT_SCHEDULE_FOUND THEN
37 FND_MESSAGE.SET_NAME ('OFA','FA_DUPLICATE_PAYMENT_SCH');
38 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_PAYMENT_SCHEDULE');
39 FND_MSG_PUB.ADD;
40 RETURN (FALSE);
41 WHEN OTHERS THEN
42 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_PAYMENT_SCHEDULE'
43 ,p_log_level_rec => p_log_level_rec);
44 RETURN (FALSE);
45 END CHECK_PAYMENT_SCHEDULE;
46
47 -------------------------------------------
48 -- CHECK FOR CURRENCY CODE
49 -------------------------------------------
50 FUNCTION CHECK_CURRENCY_CODE(
51 P_CURRENCY_CODE IN FA_LEASE_SCHEDULES.CURRENCY_CODE%TYPE,
52 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
53
54 L_CURRENCY_CODE FA_LEASE_SCHEDULES.CURRENCY_CODE%TYPE;
55
56 BEGIN
57 -------------------------------------------
58 -- CHECK IF CURRENCY CODE IS VALID
59 -------------------------------------------
60 SELECT CURRENCY_CODE INTO L_CURRENCY_CODE
61 FROM
62 FND_CURRENCIES_VL
63 WHERE CURRENCY_CODE = P_CURRENCY_CODE
64 AND ENABLED_FLAG = 'Y';
65
66 RETURN (TRUE);
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 FND_MESSAGE.SET_NAME ('OFA','FA_DYN_CURRENCY');
71 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_CURRENCY_CODE');
72 FND_MSG_PUB.ADD;
73 RETURN (FALSE);
74 WHEN OTHERS THEN
75 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_CURRENCY_CODE'
76 ,p_log_level_rec => p_log_level_rec);
77 RETURN (FALSE);
78 END CHECK_CURRENCY_CODE;
79
80 -------------------------------------------
81 -- CHECK FOR LEASE FREQUENCY
82 -------------------------------------------
83 FUNCTION CHECK_LEASE_FREQUENCY(
84 P_FREQUENCY IN FA_LEASE_SCHEDULES.FREQUENCY%TYPE,
85 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
86
87 L_FRQUENCY VARCHAR2(15);
88
89 BEGIN
90
91 -------------------------------------------
92 -- CHECK IF FREQUENCY IS VALID
93 -------------------------------------------
94 SELECT LOOKUP_CODE
95 INTO L_FRQUENCY
96 FROM FA_LOOKUPS
97 WHERE LOOKUP_TYPE ='LEASE FREQUENCY'
98 AND LOOKUP_CODE=UPPER(P_FREQUENCY);
99
100 RETURN (TRUE);
101
102 EXCEPTION
103 WHEN NO_DATA_FOUND THEN
104 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_LEASE_FREQUENCY');
105 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_LEASE_FREQUENCY');
106 FND_MSG_PUB.ADD;
107 RETURN (FALSE);
108 WHEN OTHERS THEN
109 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_LEASE_FREQUENCY'
110 ,p_log_level_rec => p_log_level_rec);
111 RETURN (FALSE);
112 END CHECK_LEASE_FREQUENCY;
113
114 -------------------------------------------
115 -- CHECK FOR START DATE
116 -------------------------------------------
117 FUNCTION CHECK_START_DATE (
118 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE,
119 P_LEASE_INCEPTION_DATE IN DATE,
120 P_MONTHS_PER_PERIOD IN NUMBER,
121 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
122 L_RETURN_FLAG BOOLEAN:=TRUE;
123 BEGIN
124
125 ------------------------------------------------------------
126 -- VALIDATE START DATE 1. NEEDS TO BE >= THAN INCEPTION DATE
127 -- 2. FIRST DAY OF THE MONTH
128 -- 3. SHOULD FALL ON A DATE BASED ON FREQUENCY
129 ------------------------------------------------------------
130 FOR TBL_INDEX IN 1..P_LEASE_PAYMENTS_TBL.COUNT LOOP
131
132 IF P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE IS NULL THEN
133 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_ST_DT_NULL');
134 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_START_DATE');
135 FND_MSG_PUB.ADD;
136 L_RETURN_FLAG:=FALSE;
137 GOTO END_OF_LOOP;
138 END IF;
139
140 IF P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE >= P_LEASE_INCEPTION_DATE THEN
141 IF TO_CHAR(P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE,'DD') = 1 THEN
142 IF (MOD (MONTHS_BETWEEN(P_LEASE_INCEPTION_DATE,P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE),P_MONTHS_PER_PERIOD) <> 0) THEN
143 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_ST_DT_BEGIN_OF_PERIOD');
144 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_START_DATE');
145 FND_MSG_PUB.ADD;
146 L_RETURN_FLAG:=FALSE;
147 END IF;
148 ELSE
149 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_DATE_FIRST_OF_MONTH');
150 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_START_DATE');
151 FND_MSG_PUB.ADD;
152 L_RETURN_FLAG:=FALSE;
153 END IF;
154 ELSE
155 FND_MESSAGE.SET_NAME('OFA','FA_LEASE_ST_LEASE_DATE_OVERLAP');
156 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_START_DATE');
157 FND_MSG_PUB.ADD;
158 L_RETURN_FLAG:=FALSE;
159 END IF;
160
161 END LOOP;
162
163 <<END_OF_LOOP>>
164
165 RETURN(L_RETURN_FLAG);
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_START_DATE'
170 ,p_log_level_rec => p_log_level_rec);
171 RETURN (FALSE);
172 END CHECK_START_DATE;
173
174 -------------------------------------------
175 -- CHECK FOR PAYMENT AMOUNT
176 -------------------------------------------
177 FUNCTION CHECK_PAYMENT_AMOUNT(
178 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE,
179 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
180 L_RETURN_FLAG BOOLEAN:=TRUE;
181 BEGIN
182 ------------------------------------------------------------
183 -- PAYMENT AMOUNT CAN NOT BE NULL
184 ------------------------------------------------------------
185 FOR TBL_INDEX IN 1..P_LEASE_PAYMENTS_TBL.COUNT LOOP
186 IF P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_AMOUNT IS NULL OR
187 P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_AMOUNT < 0 THEN
188 FND_MESSAGE.SET_NAME('OFA','FA_INCORRECT_PAYMENT_AMOUNT');
189 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_PAYMENT_AMOUNT');
190 FND_MSG_PUB.ADD;
191 L_RETURN_FLAG:=FALSE;
192 END IF;
193 END LOOP;
194
195 RETURN(L_RETURN_FLAG);
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_PAYMENT_AMOUNT'
200 ,p_log_level_rec => p_log_level_rec);
201 RETURN (FALSE);
202 END CHECK_PAYMENT_AMOUNT;
203
204 -------------------------------------------
205 -- CHECK FOR NUMBER OF PAYMENTS
206 -------------------------------------------
207 FUNCTION CHECK_NO_OF_PAYMENTS (
208 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE,
209 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
210
211 L_RETURN_FLAG BOOLEAN:=TRUE;
212
213 BEGIN
214
215 ------------------------------------------------------------
216 -- NUMBER OF PAYMENTS SHOULD BE > 0
217 ------------------------------------------------------------
218
219 FOR TBL_INDEX IN 1..P_LEASE_PAYMENTS_TBL.COUNT LOOP
220
221 IF P_LEASE_PAYMENTS_TBL(TBL_INDEX).NUMBER_OF_PAYMENTS IS NULL THEN
222 FND_MESSAGE.SET_NAME('OFA','FA_INCORRECT_NO_OF_PAYMENTS');
223 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_NO_OF_PAYMENTS');
224 FND_MSG_PUB.ADD;
225 L_RETURN_FLAG:=FALSE;
226 ELSIF P_LEASE_PAYMENTS_TBL(TBL_INDEX).NUMBER_OF_PAYMENTS <= 0 THEN
227 FND_MESSAGE.SET_NAME('OFA','FA_INCORRECT_NO_OF_PAYMENTS');
228 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CHECK_NO_OF_PAYMENTS');
229 FND_MSG_PUB.ADD;
230 L_RETURN_FLAG:=FALSE;
231 END IF;
232 END LOOP;
233
234 RETURN(L_RETURN_FLAG);
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_NO_OF_PAYMENTS'
239 ,p_log_level_rec => p_log_level_rec);
240 RETURN (FALSE);
241 END CHECK_NO_OF_PAYMENTS ;
242
243 -------------------------------------------
244 -- CHECK FOR LEASE PAYMENT TYPE
245 -------------------------------------------
246 FUNCTION CHECK_PERIODS (
247 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE,
248 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
249
250 L_RETURN_FLAG BOOLEAN:=TRUE;
251 L_PERIOD VARCHAR2(1);
252
253 CURSOR C1 IS
254 SELECT LOOKUP_CODE
255 FROM FA_LOOKUPS
256 WHERE LOOKUP_CODE = L_PERIOD AND
257 LOOKUP_TYPE = 'LEASE PAYMENT TYPE';
258
259 BEGIN
260 ------------------------------------------------------------
261 -- IF PROVIDED, PERIOD SHOULD BE A VALID ONE
262 ------------------------------------------------------------
263 FOR TBL_INDEX IN 1..P_LEASE_PAYMENTS_TBL.COUNT LOOP
264 IF P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_TYPE IS NULL THEN
265 NULL;
266 ELSE
267 L_PERIOD:=P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_TYPE;
268 OPEN C1;
269 FETCH C1 INTO L_PERIOD;
270 IF C1%NOTFOUND THEN
271 FND_MESSAGE.SET_NAME('OFA','FA_INCORRECT_PAYMENT_TYPE');
272 FND_MESSAGE.SET_TOKEN ('CALLING_FN',' CHECK_PERIODS');
273 FND_MSG_PUB.ADD;
274 L_RETURN_FLAG:=FALSE;
275 END IF;
276 CLOSE C1;
277 END IF;
278 END LOOP;
279
280 RETURN(L_RETURN_FLAG);
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 FA_SRVR_MSG.ADD_SQL_ERROR('CHECK_PERIODS'
285 ,p_log_level_rec => p_log_level_rec);
286 RETURN (FALSE);
287 END CHECK_PERIODS;
288
289 -------------------------------------------
290 -- CALCULATE END DATE
291 -------------------------------------------
292 FUNCTION CALC_END_DATE (
293 P_NUMBER_OF_PAYMENTS IN NUMBER,
294 P_MONTHS_PER_PERIOD IN NUMBER,
295 P_START_DATE IN DATE,
296 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN DATE AS
297 L_MONTHS_IN_LEASE INTEGER;
298 L_END_DATE DATE;
299 BEGIN
300 ------------------------------------------------------------
301 -- CALCULATE END DATE
302 ------------------------------------------------------------
303
304 L_MONTHS_IN_LEASE := P_NUMBER_OF_PAYMENTS * P_MONTHS_PER_PERIOD;
305 L_END_DATE := (ADD_MONTHS(P_START_DATE, (L_MONTHS_IN_LEASE - P_MONTHS_PER_PERIOD)));
306
307 RETURN L_END_DATE;
308 EXCEPTION
309 WHEN OTHERS THEN
310 FA_SRVR_MSG.ADD_SQL_ERROR('CALC_END_DATE'
311 ,p_log_level_rec => p_log_level_rec);
312 RETURN (NULL);
313 END CALC_END_DATE;
314
315 -------------------------------------------
316 -- VALIDATE SCHEDULE ID
317 -------------------------------------------
318 FUNCTION VALIDATE_SCHEDULE_ID
319 (P_PAYMENT_SCHEDULE_ID IN NUMBER,
320 X_MONTHS_PER_PERIOD OUT NOCOPY NUMBER,
321 X_CURRENCY_PRECISION OUT NOCOPY NUMBER,
322 X_PERIODS_PER_YEAR OUT NOCOPY NUMBER ,
323 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN AS
324
325 CURSOR C1 IS
326 SELECT FREQUENCY,CURRENCY_CODE
327 FROM FA_LEASE_SCHEDULES
328 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID;
329
330 C1_NOT_FOUND EXCEPTION;
331 PAYMENTS_NOT_FOUND EXCEPTION;
332
333 L_CURRENCY_CODE VARCHAR2(5);
334 L_FREQUENCY VARCHAR2(15);
335 L_EXT_PRECISION NUMBER;
336 L_MIN_ACCT_UNIT NUMBER;
337 BEGIN
338
339 --------------------------------------------------------------
340 -- CHECK IF SCHDULE ID IS VALID, ALSO RETURN MONTHS PER PERIOD
341 -- PERIODS PER YEAR AND CURRENCY PRECISION
342 --------------------------------------------------------------
343 OPEN C1;
344
345 FETCH C1 INTO L_FREQUENCY,L_CURRENCY_CODE;
346
347 IF C1%NOTFOUND THEN
348 RAISE C1_NOT_FOUND;
349 END IF;
350
351 IF (L_FREQUENCY = 'MONTHLY') THEN
352 X_MONTHS_PER_PERIOD := 1;
353 X_PERIODS_PER_YEAR := 12;
354 ELSIF (L_FREQUENCY = 'QUARTERLY') THEN
355 X_MONTHS_PER_PERIOD := 3;
356 X_PERIODS_PER_YEAR := 4;
357 ELSIF (L_FREQUENCY = 'SEMI-ANNUALLY') THEN
358 X_MONTHS_PER_PERIOD := 6;
359 X_PERIODS_PER_YEAR := 2;
360 ELSIF (L_FREQUENCY = 'ANNUALLY') THEN
361 X_MONTHS_PER_PERIOD := 12;
362 X_PERIODS_PER_YEAR := 1;
363 END IF;
364
365 FND_CURRENCY.GET_INFO(L_CURRENCY_CODE, X_CURRENCY_PRECISION,L_EXT_PRECISION,L_MIN_ACCT_UNIT);
366
367 RETURN(TRUE);
368
369 EXCEPTION
370 WHEN C1_NOT_FOUND THEN
371 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_SCHEDULE_NOT_FOUND');
372 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_SCHEDULE_ID');
373 FND_MSG_PUB.ADD;
374 RETURN (FALSE);
375 WHEN OTHERS THEN
376 FA_SRVR_MSG.ADD_SQL_ERROR('VALIDATE_SCHEDULE_ID'
377 ,p_log_level_rec => p_log_level_rec);
378 RETURN (FALSE);
379 END VALIDATE_SCHEDULE_ID;
380
381 -------------------------------------------
382 -- CALCULATE INTEREST
383 -------------------------------------------
384 FUNCTION CALCULATE_INTEREST
385 (P_PRINCIPAL IN NUMBER,
386 P_RATE_PER_PERIOD IN NUMBER,
387 P_NUM_PERIODS IN NUMBER,
388 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
389 RETURN NUMBER IS
390 INTEREST_AMOUNT NUMBER := 0;
391 RATE_FACTOR NUMBER := 0;
392 BEGIN
393 --------------------------------------------------------------
394 -- INTEREST_AMOUNT IS:
395 --
396 -- I = (P * ((1 + I)**N)) - P
397 -- CALCULATE (1 + I)**N
398 --------------------------------------------------------------
399 SELECT (POWER((1 + P_RATE_PER_PERIOD), P_NUM_PERIODS))
400 INTO RATE_FACTOR
401 FROM DUAL;
402
403 INTEREST_AMOUNT := (P_PRINCIPAL * (RATE_FACTOR)) - P_PRINCIPAL;
404 INTEREST_AMOUNT := INTEREST_AMOUNT;
405
406 RETURN(INTEREST_AMOUNT);
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 FA_SRVR_MSG.ADD_SQL_ERROR('CALCULATE_INTEREST'
411 ,p_log_level_rec => p_log_level_rec);
412 RETURN (NULL);
413 END CALCULATE_INTEREST;
414
415 -------------------------------------------
416 -- DO MINIMUM OF TWO NUMBERS
417 -------------------------------------------
418 FUNCTION AMINIMUM2
419 (P_X NUMBER,
420 P_Y NUMBER,
421 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
422 RETURN NUMBER IS
423 MIN_NUMBER NUMBER;
424 BEGIN
425
426 --------------------------------------------------------------
427 -- CALCULATE MIN OF TWO
428 --------------------------------------------------------------
429
430 IF (P_X <= P_Y) THEN
431 MIN_NUMBER := P_X;
432 ELSE
433 MIN_NUMBER := P_Y;
434 END IF;
435
436 RETURN (MIN_NUMBER);
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 FA_SRVR_MSG.ADD_SQL_ERROR('AMINIMUM2'
441 ,p_log_level_rec => p_log_level_rec);
442 RETURN (NULL);
443 END AMINIMUM2;
444
445 -------------------------------------------
446 -- DO MINIMUM OF THREE NUMBERS
447 -------------------------------------------
448 FUNCTION AMINIMUM3
449 (P_X NUMBER,
450 P_Y NUMBER,
451 P_Z NUMBER,
452 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
453 RETURN NUMBER IS
454 MIN_NUMBER NUMBER;
455 BEGIN
456 --------------------------------------------------------------
457 -- CALCULATE MIN OF THREE
458 --------------------------------------------------------------
459
460 IF (P_X <= P_Y) AND (P_X <= P_Z) THEN
461 MIN_NUMBER := P_X;
462 ELSE
463 IF (P_Y <= P_X) AND (P_Y <= P_Z) THEN
464 MIN_NUMBER := P_Y;
465 ELSE
466 IF (P_Z <= P_X) AND (P_Z <= P_Y) THEN
467 MIN_NUMBER := P_Z;
468 END IF;
469 END IF;
470 END IF;
471
472 RETURN (MIN_NUMBER);
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 FA_SRVR_MSG.ADD_SQL_ERROR('AMINIMUM3'
477 ,p_log_level_rec => p_log_level_rec);
478 RETURN (NULL);
479 END AMINIMUM3;
480
481 -------------------------------------------
482 -- DO LUMP SUM CALCULATIONS
483 -------------------------------------------
484 FUNCTION LUMP_SUM
485 (P_PAYMENT_AMOUNT IN NUMBER,
486 P_NUMBER_PAYMENTS IN NUMBER,
487 P_INTEREST_RATE IN NUMBER,
488 P_CURRENCY_PRECISION IN NUMBER,
489 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
490 RETURN NUMBER IS
491 H_ROW_PV NUMBER := 0;
492 H_DENOMINATOR NUMBER := 0;
493 BEGIN
494
495 -------------------------------------------------------------
496 -- CALCULATE THE DENOMINATOR FIRST "(1 + I) EXP N"
497 --------------------------------------------------------------
498 IF (P_NUMBER_PAYMENTS = 0) THEN
499 H_DENOMINATOR := 1;
500 ELSE
501 SELECT (POWER((1 + P_INTEREST_RATE), P_NUMBER_PAYMENTS)) INTO H_DENOMINATOR FROM DUAL;
502 END IF;
503
504 H_ROW_PV := (1/H_DENOMINATOR) * P_PAYMENT_AMOUNT;
505 H_ROW_PV := ROUND(H_ROW_PV, P_CURRENCY_PRECISION);
506 RETURN(H_ROW_PV);
507
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 FA_SRVR_MSG.ADD_SQL_ERROR('LUMP_SUM'
512 ,p_log_level_rec => p_log_level_rec);
513 RETURN (NULL);
514 END LUMP_SUM;
515
516 -------------------------------------------
517 -- DO ORDINARY ANNUITY CALCULATIONS
518 -------------------------------------------
519 FUNCTION ORDINARY_ANNUITY
520 (P_PAYMENT_AMOUNT IN NUMBER,
521 P_NUMBER_PAYMENTS IN NUMBER,
522 P_INTEREST_RATE IN NUMBER,
523 P_CURRENCY_PRECISION IN NUMBER,
524 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN NUMBER IS
525 H_ROW_PV NUMBER := 0 ;
526 H_NUMERATOR NUMBER := 0 ;
527 H_DENOMINATOR NUMBER := 0 ;
528 H_SUBDENOMINATOR NUMBER := 0 ;
529 BEGIN
530 -------------------------------------------------------------
531 -- CALCULATE ORDINARY ANNUITY
532 --------------------------------------------------------------
533 H_DENOMINATOR := P_INTEREST_RATE;
534 --------------------------------------------------------------
535 -- H_SUBDENOMINATOR := 1;
536 -- CALCULATE THE SUBDENOMINATOR "(1 + I) EXP N"
537 --------------------------------------------------------------
538
539 IF (P_NUMBER_PAYMENTS = 0) THEN
540 H_SUBDENOMINATOR := 1;
541 ELSE
542 --H_SUBDENOMINATOR := ((1 + P_INTEREST_RATE) ** P_NUMBER_PAYMENTS);
543 SELECT (POWER((1 + P_INTEREST_RATE), P_NUMBER_PAYMENTS)) INTO H_SUBDENOMINATOR FROM DUAL;
544 END IF;
545 --
546 H_NUMERATOR := 1 - (1 / H_SUBDENOMINATOR);
547 H_ROW_PV := ((H_NUMERATOR / H_DENOMINATOR) * P_PAYMENT_AMOUNT);
548 H_ROW_PV := ROUND(H_ROW_PV, P_CURRENCY_PRECISION);
549
550 RETURN(H_ROW_PV);
551 EXCEPTION
552 WHEN OTHERS THEN
553 FA_SRVR_MSG.ADD_SQL_ERROR('ORDINARY_ANNUITY'
554 ,p_log_level_rec => p_log_level_rec);
555 RETURN (NULL);
556 --
557 END ORDINARY_ANNUITY;
558
559 -------------------------------------------
560 -- DO ANNUITY DUE CALCULATIONS
561 -------------------------------------------
562 FUNCTION ANNUITY_DUE (P_PAYMENT_AMOUNT IN NUMBER,
563 P_NUMBER_PAYMENTS IN NUMBER,
564 P_INTEREST_RATE IN NUMBER,
565 P_CURRENCY_PRECISION IN NUMBER,
566 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN NUMBER IS
567 H_ROW_PV NUMBER := 0 ;
568 H_NUMERATOR NUMBER := 0 ;
569 H_DENOMINATOR NUMBER := 0 ;
570 H_SUBDENOMINATOR NUMBER := 0 ;
571 BEGIN
572 -------------------------------------------------------------
573 -- CALCULATE ANNUITY DUE
574 --------------------------------------------------------------
575 H_DENOMINATOR := P_INTEREST_RATE;
576 H_SUBDENOMINATOR := 1;
577
578 -- CALCULATE THE SUBDENOMINATOR "(1 + I) EXP N"
579 --
580 IF (P_NUMBER_PAYMENTS = 0) THEN
581 H_SUBDENOMINATOR := 1;
582 ELSIF (P_NUMBER_PAYMENTS = 1) THEN
583 H_SUBDENOMINATOR := (1 + P_INTEREST_RATE);
584 ELSIF (P_NUMBER_PAYMENTS > 1) THEN
585 --THE FOLLOWING STATEMENT DOES NOT WORK. SO THE SELECT ST. IS NECESSARY
586 -- H_SUBDENOMINATOR := ((1 + P_INTEREST_RATE) ** (P_NUMBER_PAYMENTS - 1));
587 SELECT (POWER((1 + P_INTEREST_RATE), (P_NUMBER_PAYMENTS - 1)))
588 INTO H_SUBDENOMINATOR FROM DUAL;
589 END IF;
590
591 H_NUMERATOR := 1 - (1 / H_SUBDENOMINATOR);
592 H_ROW_PV := (1 + (H_NUMERATOR/H_DENOMINATOR)) * P_PAYMENT_AMOUNT;
593 H_ROW_PV := ROUND(H_ROW_PV, P_CURRENCY_PRECISION);
594 RETURN(H_ROW_PV);
595 EXCEPTION
596 WHEN OTHERS THEN
597 FA_SRVR_MSG.ADD_SQL_ERROR('ANNUITY_DUE'
598 ,p_log_level_rec => p_log_level_rec);
599 RETURN (NULL);
600 END ANNUITY_DUE;
601
602 -------------------------------------------
603 -- DO PRESENT VALUE CALCULATIONS
604 -------------------------------------------
605 PROCEDURE PRESENT_VALUE_CALC
606 (P_PAYMENT_SCHEDULE_ID IN NUMBER
607 ,P_MONTHS_PER_PERIOD IN NUMBER
608 ,P_INTEREST_PER_PERIOD IN NUMBER
609 ,P_CURRENCY_PRECISION IN NUMBER,
610 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
611 IS
612 CURSOR C_SCHEDULE IS
613 SELECT LEASE_INCEPTION_DATE,PRESENT_VALUE
614 FROM FA_LEASE_SCHEDULES
615 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID
616 FOR UPDATE OF PRESENT_VALUE;
617
618 CURSOR C_PAYMENT IS
619 SELECT START_DATE,PAYMENT_AMOUNT,NUMBER_OF_PAYMENTS
620 FROM FA_LEASE_PAYMENTS
621 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID
622 ORDER BY PAYMENT_LINE_NUMBER
623 FOR UPDATE OF ROW_PRESENT_VALUE;
624
625 H_PV_OF_ANNUITY_DUE NUMBER := 0 ;
626 L_DEFERRED_PERIOD NUMBER:=0;
627 L_ROW_PRESENT_VALUE NUMBER:=0;
628 L_TOTAL_ROW_PRESENT_VALUE NUMBER:=0;
629
630 BEGIN
631
632 --------------------------------------------------------------------
633 -- CALCULATE ROW_PRESENT VLAUE FOR EACH ROW IN FA_LEASE_PAYMENTS AND
634 -- SUM ALL THE ROW_PRESENT VALUE FOR FA_LEASE_SHCEDULE.PRESENT_VALUE
635 --------------------------------------------------------------------
636
637 FOR SCHEDULE_REC IN C_SCHEDULE LOOP
638 FOR PAYMENT_REC IN C_PAYMENT LOOP
639 -- CALCULATE DEFERRED PERIOD FOR EACH PAYMENT.IT WILL BE PASSED INTO PRESENT VALUE FUNCTIONS
640 L_DEFERRED_PERIOD := ((MONTHS_BETWEEN(PAYMENT_REC.START_DATE ,SCHEDULE_REC.LEASE_INCEPTION_DATE))/ P_MONTHS_PER_PERIOD);
641 -- DEPENDING ON LEASING STRUCTURE CHOSEN BY THE USER, CALL THE
642 -- APPROPRIATE FUNCTIONS TO CALCULATE PRESENT VALUE
643 -----------------------------
644 -- FIRST PAYMENT IN SCHEDULE
645 -----------------------------
646 IF ((PAYMENT_REC.START_DATE = SCHEDULE_REC.LEASE_INCEPTION_DATE) AND
647 (PAYMENT_REC.NUMBER_OF_PAYMENTS = 1)) THEN
648 L_ROW_PRESENT_VALUE := PAYMENT_REC.PAYMENT_AMOUNT;
649
650 -----------------------------
651 -- LUMP SUM PAYMENT
652 -----------------------------
653 ELSIF (PAYMENT_REC.NUMBER_OF_PAYMENTS = 1) THEN
654 L_ROW_PRESENT_VALUE := LUMP_SUM(PAYMENT_REC.PAYMENT_AMOUNT,
655 L_DEFERRED_PERIOD,
656 P_INTEREST_PER_PERIOD,
657 P_CURRENCY_PRECISION
658 );
659 -----------------------------
660 -- ANNUITY DUE
661 -----------------------------
662 ELSIF (PAYMENT_REC.START_DATE = SCHEDULE_REC.LEASE_INCEPTION_DATE) AND
663 (PAYMENT_REC.NUMBER_OF_PAYMENTS > 1) THEN
664 IF P_INTEREST_PER_PERIOD <> 0 THEN
665 L_ROW_PRESENT_VALUE := ANNUITY_DUE(PAYMENT_REC.PAYMENT_AMOUNT,
666 PAYMENT_REC.NUMBER_OF_PAYMENTS,
667 P_INTEREST_PER_PERIOD,P_CURRENCY_PRECISION
668 );
669 ELSE
670 L_ROW_PRESENT_VALUE := PAYMENT_REC.PAYMENT_AMOUNT * PAYMENT_REC.NUMBER_OF_PAYMENTS;
671 END IF;
672 -----------------------------
673 -- ORDINARY ANNUITY
674 -----------------------------
675 ELSIF (PAYMENT_REC.START_DATE = ADD_MONTHS(SCHEDULE_REC.LEASE_INCEPTION_DATE,
676 P_MONTHS_PER_PERIOD)) AND
677 (PAYMENT_REC.NUMBER_OF_PAYMENTS > 1) THEN
678 IF P_INTEREST_PER_PERIOD <> 0 THEN
679 L_ROW_PRESENT_VALUE := ORDINARY_ANNUITY(PAYMENT_REC.PAYMENT_AMOUNT,
680 PAYMENT_REC.NUMBER_OF_PAYMENTS,
681 P_INTEREST_PER_PERIOD,P_CURRENCY_PRECISION
682 );
683 ELSE
684 L_ROW_PRESENT_VALUE := PAYMENT_REC.PAYMENT_AMOUNT * PAYMENT_REC.NUMBER_OF_PAYMENTS;
685 END IF;
686 -----------------------------------------------------------------------------------
687 -- CALCULATE THE PRESENT VALUE OF THE ANNUITY IGNORING THE DEFERRED PERIOD
688 -- THEN CALCULATE THE PRESENT VALUE OF THIS LUMP SUM AMOUNT WITH THE
689 -- NUMBER_OF_PAYMENTS = THE DEFERRED PERIOD
690 ----------------------------------------------------------------------------------
691 ELSIF ( (PAYMENT_REC.START_DATE >
692 ADD_MONTHS(SCHEDULE_REC.LEASE_INCEPTION_DATE, P_MONTHS_PER_PERIOD)) AND
693 (PAYMENT_REC.NUMBER_OF_PAYMENTS > 1) ) OR
694 ( (PAYMENT_REC.START_DATE <> SCHEDULE_REC.LEASE_INCEPTION_DATE) AND
695 (PAYMENT_REC.NUMBER_OF_PAYMENTS > 1)) THEN
696 IF P_INTEREST_PER_PERIOD <> 0 THEN
697 H_PV_OF_ANNUITY_DUE := ANNUITY_DUE(PAYMENT_REC.PAYMENT_AMOUNT,
698 PAYMENT_REC.NUMBER_OF_PAYMENTS,
699 P_INTEREST_PER_PERIOD,P_CURRENCY_PRECISION
700 );
701 L_ROW_PRESENT_VALUE := LUMP_SUM(H_PV_OF_ANNUITY_DUE,
702 L_DEFERRED_PERIOD,
703 P_INTEREST_PER_PERIOD,P_CURRENCY_PRECISION
704 );
705 ELSE
706 L_ROW_PRESENT_VALUE := PAYMENT_REC.PAYMENT_AMOUNT * PAYMENT_REC.NUMBER_OF_PAYMENTS;
707 END IF;
708 END IF;
709
710 L_ROW_PRESENT_VALUE := ROUND(L_ROW_PRESENT_VALUE,
711 P_CURRENCY_PRECISION);
712 UPDATE FA_LEASE_PAYMENTS
713 SET ROW_PRESENT_VALUE = L_ROW_PRESENT_VALUE
714 WHERE CURRENT OF C_PAYMENT;
715 L_TOTAL_ROW_PRESENT_VALUE := L_TOTAL_ROW_PRESENT_VALUE + L_ROW_PRESENT_VALUE;
716
717 END LOOP;
718 UPDATE FA_LEASE_SCHEDULES
719 SET PRESENT_VALUE = L_TOTAL_ROW_PRESENT_VALUE
720 WHERE CURRENT OF C_SCHEDULE;
721 END LOOP;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 FA_SRVR_MSG.ADD_SQL_ERROR('PRESENT_VALUE_CALC'
726 ,p_log_level_rec => p_log_level_rec);
727 END PRESENT_VALUE_CALC;
728
729 ------------------------------------------------------------
730 -- VALIDATE ALL THE SUPPLIED DATA
731 ------------------------------------------------------------
732
733 FUNCTION VALIDATE_PAYMENTS (
734 PX_LEASE_SCHEDULES_REC IN OUT NOCOPY FA_API_TYPES.LEASE_SCHEDULES_REC_TYPE,
735 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE,
736 P_MONTHS_PER_PERIOD OUT NOCOPY NUMBER,
737 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
738 RETURN BOOLEAN IS
739 VALUE_ERROR_EXCEPTION EXCEPTION;
740 L_MONTHS_PER_PERIOD NUMBER;
741 L_PERIODS_PER_YEAR NUMBER;
742
743 BEGIN
744
745
746 ------------------------------------------------------------
747 -- VALIDATE PAYMENT SCHEDULE NAME
748 ------------------------------------------------------------
749
750 IF PX_LEASE_SCHEDULES_REC.PAYMENT_SCHEDULE_NAME IS NULL THEN
751 FND_MESSAGE.SET_NAME ('OFA','FA_NULL_PAYMENT_SCH');
752 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
753 FND_MSG_PUB.ADD;
754 RAISE VALUE_ERROR_EXCEPTION;
755
756 ELSE
757 IF NOT CHECK_PAYMENT_SCHEDULE(PX_LEASE_SCHEDULES_REC.PAYMENT_SCHEDULE_NAME) THEN
758 RAISE VALUE_ERROR_EXCEPTION;
759 END IF;
760 END IF;
761
762 ------------------------------------------------------------
763 -- VALIDATE PRESENT VALUE
764 ------------------------------------------------------------
765
766 IF PX_LEASE_SCHEDULES_REC.PRESENT_VALUE IS NULL THEN
767 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_PRESENT_VALUE');
768 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
769 FND_MSG_PUB.ADD;
770 RAISE VALUE_ERROR_EXCEPTION;
771 ELSIF PX_LEASE_SCHEDULES_REC.PRESENT_VALUE < 0 THEN
772 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_PRESENT_VALUE');
773 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
774 FND_MSG_PUB.ADD;
775 RAISE VALUE_ERROR_EXCEPTION;
776 END IF;
777
778 ------------------------------------------------------------
779 -- VALIDATE INTEREST RATE
780 ------------------------------------------------------------
781
782 IF PX_LEASE_SCHEDULES_REC.INTEREST_RATE IS NULL THEN
783 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_INT_RATE');
784 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
785 FND_MSG_PUB.ADD;
786 RAISE VALUE_ERROR_EXCEPTION;
787 ELSIF ( PX_LEASE_SCHEDULES_REC.INTEREST_RATE < 0
788 OR PX_LEASE_SCHEDULES_REC.INTEREST_RATE > 100) THEN
789 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_INT_RATE');
790 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
791 FND_MSG_PUB.ADD;
792 RAISE VALUE_ERROR_EXCEPTION;
793 END IF;
794
795 ------------------------------------------------------------
796 -- VALIDATE LEASE INCEPTION DATE
797 ------------------------------------------------------------
798
799 IF PX_LEASE_SCHEDULES_REC.LEASE_INCEPTION_DATE IS NULL THEN
800 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_INCEPTION_DATE');
801 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
802 FND_MSG_PUB.ADD;
803 RAISE VALUE_ERROR_EXCEPTION;
804 ELSIF TO_CHAR(PX_LEASE_SCHEDULES_REC.LEASE_INCEPTION_DATE ,'DD') <> 1 THEN
805 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_DATE_FIRST_OF_MONTH');
806 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
807 FND_MSG_PUB.ADD;
808 RAISE VALUE_ERROR_EXCEPTION;
809 END IF;
810
811 ------------------------------------------------------------
812 -- VALIDATE CURRENCY CODE
813 ------------------------------------------------------------
814
815 IF PX_LEASE_SCHEDULES_REC.CURRENCY_CODE IS NULL THEN
816 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_CURRENCY_CODE');
817 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
818 FND_MSG_PUB.ADD;
819 RAISE VALUE_ERROR_EXCEPTION;
820 ELSE
821 IF NOT CHECK_CURRENCY_CODE(PX_LEASE_SCHEDULES_REC.CURRENCY_CODE) THEN
822 RAISE VALUE_ERROR_EXCEPTION;
823 END IF;
824 END IF;
825
826 ------------------------------------------------------------
827 -- VALIDATE FREQUENCY
828 ------------------------------------------------------------
829
830 IF PX_LEASE_SCHEDULES_REC.FREQUENCY IS NULL THEN
831 FND_MESSAGE.SET_NAME ('OFA','FA_INCORRECT_LEASE_FREQUENCY');
832 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
833 FND_MSG_PUB.ADD;
834 RAISE VALUE_ERROR_EXCEPTION;
835 ELSE
836 IF NOT CHECK_LEASE_FREQUENCY(PX_LEASE_SCHEDULES_REC.FREQUENCY) THEN
837 RAISE VALUE_ERROR_EXCEPTION;
838 END IF;
839 END IF;
840
841 --------------------------------------------------------------------
842 -- BASED ON FREQUENCY DETERMINE MONTHS PER PERIOD , PERIODS PER YEAR
843 --------------------------------------------------------------------
844
845 IF (UPPER(PX_LEASE_SCHEDULES_REC.FREQUENCY) = 'MONTHLY') THEN
846 L_MONTHS_PER_PERIOD := 1;
847 L_PERIODS_PER_YEAR := 12;
848 P_MONTHS_PER_PERIOD:=L_MONTHS_PER_PERIOD;
849 ELSIF (UPPER(PX_LEASE_SCHEDULES_REC.FREQUENCY) = 'QUARTERLY') THEN
850 L_MONTHS_PER_PERIOD := 3;
851 L_PERIODS_PER_YEAR := 4;
852 P_MONTHS_PER_PERIOD:=L_MONTHS_PER_PERIOD;
853 ELSIF (UPPER(PX_LEASE_SCHEDULES_REC.FREQUENCY) = 'SEMI-ANNUALLY') THEN
854 L_MONTHS_PER_PERIOD := 6;
855 L_PERIODS_PER_YEAR := 2;
856 P_MONTHS_PER_PERIOD:=L_MONTHS_PER_PERIOD;
857 ELSIF (UPPER(PX_LEASE_SCHEDULES_REC.FREQUENCY) = 'ANNUALLY') THEN
858 L_MONTHS_PER_PERIOD := 12;
859 L_PERIODS_PER_YEAR := 1;
860 P_MONTHS_PER_PERIOD:=L_MONTHS_PER_PERIOD;
861 END IF;
862
863 ------------------------------------------------------------
864 -- ATLEAST ONE PAYMENT ROW MUST EXIST
865 ------------------------------------------------------------
866
867 IF P_LEASE_PAYMENTS_TBL.COUNT < 1 THEN
868 FND_MESSAGE.SET_NAME ('OFA','FA_ATLEAST_ONE_PAYMENT');
869 FND_MESSAGE.SET_TOKEN ('CALLING_FN','VALIDATE_PAYMENTS');
870 FND_MSG_PUB.ADD;
871 RAISE VALUE_ERROR_EXCEPTION;
872 END IF;
873
874 ------------------------------------------------------------
875 -- CHECK ALL START DATE'S
876 ------------------------------------------------------------
877
878 IF NOT CHECK_START_DATE (P_LEASE_PAYMENTS_TBL,PX_LEASE_SCHEDULES_REC.LEASE_INCEPTION_DATE,L_MONTHS_PER_PERIOD) THEN
879 RAISE VALUE_ERROR_EXCEPTION;
880 END IF;
881
882 ------------------------------------------------------------
883 -- CHECK ALL PAYMENT AMOUNT'S
884 ------------------------------------------------------------
885 IF NOT CHECK_PAYMENT_AMOUNT(P_LEASE_PAYMENTS_TBL) THEN
886 RAISE VALUE_ERROR_EXCEPTION;
887 END IF;
888
889 ------------------------------------------------------------
890 -- CHECK ALL NUMBER OF PAYMENTS
891 ------------------------------------------------------------
892
896
893 IF NOT CHECK_NO_OF_PAYMENTS(P_LEASE_PAYMENTS_TBL) THEN
894 RAISE VALUE_ERROR_EXCEPTION;
895 END IF;
897
898 ------------------------------------------------------------
899 -- CHECK ALL LEASE PERIODS
900 ------------------------------------------------------------
901 IF NOT CHECK_PERIODS(P_LEASE_PAYMENTS_TBL) THEN
902 RAISE VALUE_ERROR_EXCEPTION;
903 END IF;
904
905 RETURN (TRUE);
906
907 EXCEPTION
908 WHEN VALUE_ERROR_EXCEPTION THEN
909 RETURN (FALSE);
910 WHEN OTHERS THEN
911 FA_SRVR_MSG.ADD_SQL_ERROR('VALIDATE_PAYMENTS'
912 ,p_log_level_rec => p_log_level_rec);
913 RETURN (FALSE);
914 END VALIDATE_PAYMENTS;
915
916 FUNCTION AMORTIZE(
917 P_PAYMENT_SCHEDULE_ID IN NUMBER,
918 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
919 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
920 RETURN BOOLEAN IS
921
922 VALUE_ERROR_EXCEPTION EXCEPTION;
923 L_MONTHS_PER_PERIOD NUMBER:=NULL;
924 L_CURRENCY_PRECISION NUMBER:=NULL;
925 L_CURRENT_PAYMENT_DATE DATE;
926 L_NUM_OF_PAYMENTS NUMBER:=NULL;
927 L_LINE_NUMBER NUMBER:=1;
928 L_PAYMENT_AMOUNT NUMBER:=NULL;
929 L_PERIODS_PER_YEAR NUMBER:=NULL;
930 L_INTEREST_RATE NUMBER:=NULL;
931 L_PREVIOUS_PAYMENT_DATE DATE;
932 L_LEASE_OBLIGATION NUMBER := 0;
933 L_CURRENT_INTEREST NUMBER := 0;
934 L_TOT_INT_MAX NUMBER := 0;
935 L_NUMBER_OF_PERIODS NUMBER;
936 L_TOT_PAY_CALC NUMBER:=0;
937 L_TOT_PAY_MAX NUMBER := 0;
938 L_INTEREST_PER_PERIOD NUMBER;
939 L_LINE_NUM NUMBER := 0;
940 L_PRINCIPAL_PAID NUMBER := 0;
941 L_INTEREST_PAID NUMBER := 0;
942 L_TOT_PRIN_MAX NUMBER := 0;
943 L_LEASE_INCEPTION_DATE DATE;
944 L_PAY_AMOUNT NUMBER := 0;
945 L_PRESENT_VALUE NUMBER:=NULL;
946 L_TOTAL_PAYMENT NUMBER := 0;
947 L_TOTAL_INTEREST NUMBER := 0;
948 L_TOTAL_PRINCIPAL NUMBER := 0;
949 L_AMORT_COUNT NUMBER := 0;
950
951 CURSOR C1 IS
952 SELECT
953 START_DATE,
954 NUMBER_OF_PAYMENTS,
955 PAYMENT_AMOUNT
956 FROM FA_LEASE_PAYMENTS
957 WHERE PAYMENT_SCHEDULE_ID=P_PAYMENT_SCHEDULE_ID
958 ORDER BY PAYMENT_LINE_NUMBER;
959
960 CURSOR C IS
961 SELECT AMORTIZATION_LINE_NUM, PAYMENT_DATE, PAYMENT_AMOUNT,
962 INTEREST, PRINCIPAL, LEASE_OBLIGATION ,
963 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
964 CREATED_BY, LAST_UPDATE_LOGIN
965 FROM FA_AMORT_SCHEDULES
966 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID
967 ORDER BY PAYMENT_DATE
968 FOR UPDATE OF AMORTIZATION_LINE_NUM, PAYMENT_DATE, PAYMENT_AMOUNT,
969 INTEREST, PRINCIPAL, LEASE_OBLIGATION ,
970 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
971 CREATED_BY, LAST_UPDATE_LOGIN;
972
973 BEGIN
974
975 -------------------------------------------------------------------------------
976 -- MAKE SURE THAT NO AMORTIZATION CALCULATION EXISTS FOR THIS PAYMENT SCHEDULE
977 -------------------------------------------------------------------------------
978
979 SELECT COUNT(*)
980 INTO L_AMORT_COUNT
981 FROM FA_AMORT_SCHEDULES
982 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID;
983
984 IF L_AMORT_COUNT > 0 THEN
985 FND_MESSAGE.SET_NAME ('OFA','FA_AMORT_EXISTS');
986 FND_MESSAGE.SET_TOKEN ('CALLING_FN','AMORTIZE');
987 FND_MSG_PUB.ADD;
988 RAISE VALUE_ERROR_EXCEPTION;
989 END IF;
990
991
992 -------------------------------------------------------------------------------
993 -- MAKE SURE THAT PAYMENT SCHEDULE ID IS VALID
994 -------------------------------------------------------------------------------
995
996 IF P_PAYMENT_SCHEDULE_ID IS NULL THEN
997 FND_MESSAGE.SET_NAME ('OFA','FA_NULL_PAYMENT_SCH');
998 FND_MESSAGE.SET_TOKEN ('CALLING_FN','AMORTIZE');
999 FND_MSG_PUB.ADD;
1000 RAISE VALUE_ERROR_EXCEPTION;
1001 END IF;
1002
1003 IF NOT VALIDATE_SCHEDULE_ID(P_PAYMENT_SCHEDULE_ID,L_MONTHS_PER_PERIOD,L_CURRENCY_PRECISION,L_PERIODS_PER_YEAR) THEN
1004 RAISE VALUE_ERROR_EXCEPTION;
1005 END IF;
1006
1007 FOR C1_REC IN C1 LOOP
1008
1009 L_CURRENT_PAYMENT_DATE := C1_REC.START_DATE;
1010 L_NUM_OF_PAYMENTS := C1_REC.NUMBER_OF_PAYMENTS;
1011
1012
1013 FOR J IN 1..L_NUM_OF_PAYMENTS LOOP
1014
1015 L_PAYMENT_AMOUNT:=ROUND(C1_REC.PAYMENT_AMOUNT,L_CURRENCY_PRECISION);
1016 L_TOT_PAY_CALC := L_TOT_PAY_CALC + L_PAYMENT_AMOUNT;
1017
1018 INSERT INTO FA_AMORT_SCHEDULES
1019 (PAYMENT_SCHEDULE_ID
1020 , AMORTIZATION_LINE_NUM
1021 , PAYMENT_DATE
1022 , PAYMENT_AMOUNT
1023 , INTEREST
1024 , PRINCIPAL
1025 , LEASE_OBLIGATION
1026 , CREATED_BY
1027 ,CREATION_DATE
1028 ,LAST_UPDATE_LOGIN
1029 ,LAST_UPDATE_DATE
1030 ,LAST_UPDATED_BY )
1031 VALUES
1032 (P_PAYMENT_SCHEDULE_ID
1033 ,L_LINE_NUMBER
1034 ,L_CURRENT_PAYMENT_DATE
1035 ,L_PAYMENT_AMOUNT
1036 ,0
1037 ,0
1038 ,0
1039 ,P_TRANS_REC.WHO_INFO.CREATED_BY
1040 ,P_TRANS_REC.WHO_INFO.CREATION_DATE
1041 ,P_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN
1042 ,P_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE
1043 ,P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY );
1044
1045 L_CURRENT_PAYMENT_DATE := ADD_MONTHS(L_CURRENT_PAYMENT_DATE, L_MONTHS_PER_PERIOD);
1046
1047 L_LINE_NUMBER := L_LINE_NUMBER + 1;
1048
1049 END LOOP;
1050
1051 END LOOP ;
1052
1053
1054 L_TOT_PAY_MAX := ROUND(L_TOT_PAY_CALC,L_CURRENCY_PRECISION);
1055
1056 SELECT INTEREST_RATE
1057 ,LEASE_INCEPTION_DATE
1058 ,PRESENT_VALUE
1059 INTO
1060 L_INTEREST_RATE
1061 ,L_LEASE_INCEPTION_DATE
1062 ,L_PRESENT_VALUE
1063 FROM FA_LEASE_SCHEDULES
1064 WHERE PAYMENT_SCHEDULE_ID=P_PAYMENT_SCHEDULE_ID;
1065
1066 L_INTEREST_PER_PERIOD := (L_INTEREST_RATE/100) / L_PERIODS_PER_YEAR;
1067
1068 UPDATE FA_AMORT_SCHEDULES
1069 SET AMORTIZATION_LINE_NUM = AMORTIZATION_LINE_NUM + 100000
1070 WHERE PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID;
1071
1072 L_LINE_NUM := 0;
1073 L_PRINCIPAL_PAID := 0;
1074 L_INTEREST_PAID := 0;
1075 L_PREVIOUS_PAYMENT_DATE := L_LEASE_INCEPTION_DATE;
1076 L_LEASE_OBLIGATION := ROUND(L_PRESENT_VALUE, L_CURRENCY_PRECISION);
1077 L_CURRENT_INTEREST := 0;
1078 L_TOT_INT_MAX := ROUND(L_TOT_PAY_MAX - L_LEASE_OBLIGATION, L_CURRENCY_PRECISION);
1079 L_TOT_PRIN_MAX := ROUND(L_PRESENT_VALUE, L_CURRENCY_PRECISION);
1080
1081 FOR REC IN C LOOP
1082
1083 L_LINE_NUM := L_LINE_NUM + 1;
1084
1085 -- ONLY CALCULATE INTEREST FOR PERIOD WHEN DATE HAS CHANGED
1086 IF (REC.PAYMENT_DATE <> L_PREVIOUS_PAYMENT_DATE) THEN
1087
1088 L_NUMBER_OF_PERIODS := (MONTHS_BETWEEN(REC.PAYMENT_DATE, L_PREVIOUS_PAYMENT_DATE)/L_MONTHS_PER_PERIOD);
1089 L_CURRENT_INTEREST := CALCULATE_INTEREST(L_LEASE_OBLIGATION + L_CURRENT_INTEREST,
1090 L_INTEREST_PER_PERIOD, L_NUMBER_OF_PERIODS
1091 ) + L_CURRENT_INTEREST;
1092
1093 L_CURRENT_INTEREST := ROUND(L_CURRENT_INTEREST, L_CURRENCY_PRECISION);
1094
1095 END IF;
1096
1097 L_PAY_AMOUNT := ROUND(REC.PAYMENT_AMOUNT, L_CURRENCY_PRECISION);
1098 L_INTEREST_PAID := AMINIMUM3(L_PAY_AMOUNT, L_CURRENT_INTEREST, L_TOT_INT_MAX - L_TOTAL_INTEREST);
1099 L_PRINCIPAL_PAID := AMINIMUM2(L_PAY_AMOUNT - L_INTEREST_PAID, L_TOT_PRIN_MAX - L_TOTAL_PRINCIPAL);
1100 L_INTEREST_PAID := L_PAY_AMOUNT - L_PRINCIPAL_PAID;
1101 L_CURRENT_INTEREST := L_CURRENT_INTEREST - L_INTEREST_PAID;
1102 L_LEASE_OBLIGATION := L_LEASE_OBLIGATION - L_PRINCIPAL_PAID;
1103
1104 -- NOW UPDATE COMPUTED VALUES INTO CURSOR
1105 UPDATE FA_AMORT_SCHEDULES
1106 SET AMORTIZATION_LINE_NUM = L_LINE_NUM,
1107 PAYMENT_DATE = REC.PAYMENT_DATE,
1108 PAYMENT_AMOUNT = REC.PAYMENT_AMOUNT,
1109 INTEREST = L_INTEREST_PAID,
1110 PRINCIPAL = L_PRINCIPAL_PAID,
1111 LEASE_OBLIGATION = L_LEASE_OBLIGATION,
1112 LAST_UPDATE_DATE = SYSDATE,
1113 LAST_UPDATED_BY = P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY,
1114 CREATION_DATE = SYSDATE,
1115 CREATED_BY = P_TRANS_REC.WHO_INFO.CREATED_BY,
1116 LAST_UPDATE_LOGIN = P_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN
1117 WHERE CURRENT OF C;
1118
1119 L_PREVIOUS_PAYMENT_DATE := REC.PAYMENT_DATE;
1120 ---- UPDATE TOTALS
1121 L_TOTAL_PAYMENT := L_TOTAL_PAYMENT + REC.PAYMENT_AMOUNT;
1122 L_TOTAL_INTEREST := L_TOTAL_INTEREST + L_INTEREST_PAID;
1123 L_TOTAL_PRINCIPAL := L_TOTAL_PRINCIPAL + L_PRINCIPAL_PAID;
1124
1125 END LOOP;
1126
1127
1128 PRESENT_VALUE_CALC
1129 (P_PAYMENT_SCHEDULE_ID,L_MONTHS_PER_PERIOD,L_INTEREST_PER_PERIOD,L_CURRENCY_PRECISION
1130 ,p_log_level_rec => p_log_level_rec);
1131
1132 RETURN (TRUE);
1133
1134 EXCEPTION
1135 WHEN VALUE_ERROR_EXCEPTION THEN
1136 RETURN (FALSE);
1137 WHEN OTHERS THEN
1138 FA_SRVR_MSG.ADD_SQL_ERROR('AMORTIZE'
1139 ,p_log_level_rec => p_log_level_rec);
1140 RETURN (FALSE);
1141 END AMORTIZE;
1142
1143 END FA_LEASE_SCHEDULE_PVT;