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