[Home] [Help]
PACKAGE BODY: APPS.PN_GENERATE_PMT_SCHED
Source
1 PACKAGE BODY PN_GENERATE_PMT_SCHED AS
2 -- $Header: PNCPMTSB.pls 120.1 2005/07/25 06:13:25 appldev ship $
3
4 --
5 --
6
7 -------------------------------------------------------------------------------
8 -- PROCDURE : create_sched_and_items
9 -- INVOKED FROM :
10 -- PURPOSE :
11 -- HISTORY :
12 -- 14-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms,pn_payment_items
13 -- with _ALL table.
14 -------------------------------------------------------------------------------
15 procedure create_sched_and_items (
16 error_buf OUT NOCOPY VARCHAR2,
17 ret_code OUT NOCOPY VARCHAR2,
18 pn_lease_id IN NUMBER,
19 normalize_only IN VARCHAR2,
20 pn_user_id IN NUMBER
21 ) IS
22
23 next_payment_date DATE; /* Date of next payment to be made */
24 payment_date DATE; /* Date that the current payment item must be paid */
25 day_to_pay NUMBER := 1; /* Day of month that all payment terms are paid */
26 months NUMBER;
27 partial NUMBER;
28 amount NUMBER;
29 round_amount NUMBER;
30 proration_rule NUMBER;
31 term_on_start_date DATE;
32 term_on_end_date DATE;
33 items_paid NUMBER;
34 actual_items NUMBER;
35 sched_status VARCHAR2(30);
36 pt_start_date DATE;
37 pt_end_date DATE;
38 pt_target_date DATE;
39 pt_amount NUMBER;
40 pt_estimated_amount NUMBER;
41 pt_type_code VARCHAR2(30);
42 pt_frequency_code VARCHAR2(30);
43 pt_lease_id NUMBER;
44 pt_lease_change_id NUMBER;
45 pt_lease_num NUMBER;
46 ps_id NUMBER; /* Payment Schedule ID number */
47 min_sched_date DATE;
48 rent_sched_date DATE; /* Date of rent schedule */
49 lease_start_date DATE;
50 lease_end_date DATE;
51 iteration NUMBER;
52 iteration_start NUMBER;
53
54 cur_start_date DATE;
55 cur_end_date DATE;
56 cur_months NUMBER;
57 cur_pit_lookup_code VARCHAR2(30);
58 cur_amount NUMBER;
59 cur_estimated_amount NUMBER;
60 total_cash NUMBER;
61 total_partial NUMBER;
62 l_org_id NUMBER; /*sdm14jul*/
63
64
65 cursor pt_cur (p_lease_id NUMBER) IS
66 SELECT ppta.start_date,
67 ppta.end_date,
68 ppta.target_date,
69 ppta.actual_amount,
70 ppta.estimated_amount,
71 ppta.payment_term_type_code,
72 ppta.frequency_code,
73 ppta.payment_term_id,
74 pn_leases.lease_commencement_date,
75 pn_leases.lease_termination_date,
76 pn_leases.lease_id,
77 pn_leases.lease_change_id,
78 nvl(pn_leases.payment_term_rule,365) proration_rule,
79 ppta.vendor_id,
80 ppta.customer_id,
81 ppta.vendor_site_id,
82 ppta.customer_site_use_id,
83 ppta.set_of_books_id,
84 ppta.currency_code
85 FROM pn_payment_terms_all ppta, /*sdm14jul*/
86 pn_leases_v pn_leases /*sdm? shud form view be replaced*/
87 WHERE ppta.lease_id = pn_leases.lease_id /*sdm14jul*/
88 AND ppta.lease_id = p_lease_id /*sdm14jul*/
89 AND not exists
90 ( SELECT 'x'
91 FROM pn_payment_items_all a /*sdm14jul*/
92 WHERE a.payment_term_id = ppta.payment_term_id
93 );
94
95 pt_rec pt_cur%ROWTYPE;
96
97
98 -------------------------------------------------------------------------------
99 -- PROCDURE : CREATE_OT_PAYMENT_ITEMS
100 -- INVOKED FROM :
101 -- PURPOSE :
102 -- HISTORY :
103 -- 14-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms,pn_payment_items with _ALL table.
104 -------------------------------------------------------------------------------
105 PROCEDURE CREATE_OT_PAYMENT_ITEMS (p_paymentTermId IN NUMBER) AS
106 cursor ot_cur is
107 SELECT ppt.start_date,
108 ppt.end_date,
109 ppt.target_date,
110 ppt.actual_amount,
111 ppt.estimated_amount,
112 ppt.payment_term_type_code,
113 ppt.frequency_code,
114 ppt.payment_term_id,
115 pls.lease_commencement_date,
116 pls.lease_termination_date,
117 pls.lease_id,
118 pls.lease_change_id,
119 nvl(pls.payment_term_rule,365) proration_rule,
120 ppt.vendor_id,
121 ppt.customer_id,
122 ppt.vendor_site_id,
123 ppt.customer_site_use_id,
124 ppt.set_of_books_id,
125 ppt.currency_code
126 FROM pn_leases_v pls, /*sdm should form view be replaced?*/
127 pn_payment_terms_all ppt /*sdm14jul*/
128 WHERE ppt.lease_id = pls.lease_id
129 AND ppt.lease_id = pn_lease_id
130 AND ppt.payment_term_id = p_paymentTermId
131 AND ppt.frequency_code = 'OT'
132 AND not exists
133 ( SELECT 'x'
134 FROM pn_payment_items_all a /*sdm14jul*/
135 WHERE a.payment_term_id = ppt.payment_term_id
136 );
137 l_paymentScheduleDate DATE := NULL;
138 l_paymentScheduleId NUMBER := NULL;
139 l_paymentStatusLookupCode VARCHAR2(30) := 'DRAFT';
140 l_org_id NUMBER;
141 BEGIN
142
143 FOR i in ot_cur LOOP
144
145 l_paymentStatusLookupCode := 'DRAFT';
146
147 -- get the first of the month
148 select trunc (i.start_date, 'MM')
149 into l_paymentScheduleDate
150 from dual;
151
152 select max (payment_schedule_id)
153 into l_paymentScheduleId
154 from pn_payment_schedules_all /*sdm14jul*/
155 where lease_id = i.lease_id
156 and schedule_date = l_paymentScheduleDate;
157
158 -- we need to create the schedule record
159 IF (l_paymentScheduleId IS NULL) THEN
160
161 SELECT pn_payment_schedules_s.nextval
162 INTO l_paymentScheduleId
163 FROM dual;
164
165 /*sdm14jul*/
166 SELECT org_id INTO l_org_id
167 FROM pn_leases_all
168 WHERE lease_id = i.lease_id;
169
170 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
171 (
172 payment_schedule_id,
173 last_update_date,
174 last_updated_by,
175 creation_date,
176 created_by,
177 schedule_date,
178 lease_id,
179 lease_change_id,
180 payment_status_lookup_code,
181 org_id /*sdm14jul*/
182 )
183 VALUES
184 (
185 l_paymentScheduleId,
186 sysdate,
187 pn_user_id,
188 sysdate,
189 pn_user_id,
190 l_paymentScheduleDate,
191 i.lease_id,
192 i.lease_change_id,
193 'DRAFT',
194 l_org_id /*sdm14jul*/
195 );
196 ELSE
197 -- we trying to find if the schedule is approved or not
198 select payment_status_lookup_code
199 into l_paymentStatusLookupCode
200 from pn_payment_schedules_all /*sdm14jul*/
201 where payment_schedule_id = l_paymentScheduleId;
202 END IF;
203
204
205 INSERT INTO pn_payment_items_all /*sdm14jul*/
206 (
207 payment_item_id,
208 last_update_date,
209 last_updated_by,
210 creation_date,
211 created_by,
212 actual_amount,
213 estimated_amount,
214 due_date,
215 payment_item_type_lookup_code,
216 payment_term_id,
217 payment_schedule_id,
218 period_fraction,
219 vendor_id,
220 customer_id,
221 vendor_site_id,
222 customer_site_use_id,
223 set_of_books_id,
224 currency_code,
225 export_currency_code,
226 export_currency_amount,
227 rate,
228 export_to_ap_flag,
229 org_id /*sdm14jul*/
230 )
231 SELECT
232 pn_payment_items_s.nextval,
233 sysdate,
234 pn_user_id,
235 sysdate,
236 pn_user_id,
237 i.actual_amount,
238 decode(i.actual_amount, null, i.estimated_amount,
239 null),
240 i.start_date,
241 'CASH',
242 i.payment_term_id,
243 l_paymentScheduleId,
244 1,
245 i.vendor_id,
246 i.customer_id,
247 i.vendor_site_id,
248 i.customer_site_use_id,
249 i.set_of_books_id,
250 i.currency_code,
251 i.currency_code,
252 i.actual_amount,
253 1,
254 decode (l_paymentStatusLookupCode, 'APPROVED', 'Y',NULL),
255 l_org_id /*sdm14jul*/
256 from dual;
257
258 END LOOP;
259 END CREATE_OT_PAYMENT_ITEMS;
260
261 BEGIN
262
263 OPEN pt_cur (pn_lease_id);
264
265 LOOP
266 FETCH pt_cur INTO pt_rec;
267 EXIT WHEN pt_cur%NOTFOUND;
268
269 -------------------------------------------------------------------------
270 -- Hardcoded Frequency Code
271 -------------------------------------------------------------------------
272 IF pt_rec.frequency_code = 'OT' THEN
273 cur_months := 0;
274 ELSIF pt_rec.frequency_code = 'MON' THEN
275 cur_months := 1;
276 ELSIF pt_rec.frequency_code = 'QTR' THEN
277 cur_months := 3;
278 ELSIF pt_rec.frequency_code = 'YR' THEN
279 cur_months := 12;
280 ELSIF pt_rec.frequency_code = 'SA' THEN
281 cur_months := 6;
282 END IF;
283
284 /* Loop - 1st time - insert CASH items and delete normalized items */
285 /* 2nd time - insert NORMALIZED items */
286
287
288 IF (normalize_only = 'Y') THEN
289
290 iteration_start := 2;
291
292 DELETE from pn_payment_items_all /*sdm14jul*/
293 WHERE payment_item_type_lookup_code = 'NORMALIZED'
294 AND payment_term_id = pt_rec.payment_term_id;
295
296 ELSE
297
298 iteration_start := 1;
299
300 END IF;
301
302 SELECT max(schedule_date)
303 INTO min_sched_date
304 FROM pn_payment_schedules_all /*sdm14jul*/
305 WHERE lease_id = pt_rec.lease_id
306 AND payment_status_lookup_code <> 'DRAFT';
307
308 IF min_sched_date is not null THEN
309 min_sched_date := add_months(min_sched_date,1);
310 END IF;
311
312 FOR iteration IN iteration_start..2
313 LOOP /* Loop A */
314
315 IF (iteration = 1) THEN
316
317 cur_start_date := pt_rec.start_date;
318 cur_end_date := pt_rec.end_date;
319 cur_pit_lookup_code := 'CASH';
320 cur_amount := pt_rec.actual_amount;
321 cur_estimated_amount := pt_rec.estimated_amount;
322
323 ELSE
324
325 cur_start_date := pt_rec.lease_commencement_date;
326 cur_end_date := pt_rec.lease_termination_date;
327 cur_months := 1;
328 cur_pit_lookup_code := 'NORMALIZED';
329 cur_estimated_amount := ''; /* cur_amount to be set later */
330
331 END IF;
332
333 IF (pt_rec.payment_term_type_code = 'PRE') and (iteration = 1) THEN /* IF A */
334
335 /* Prepayments */
336
337 DELETE FROM pn_payment_items_all /*sdm14jul*/
338 WHERE payment_term_id = pt_rec.payment_term_id;
339
340 rent_sched_date := Trunc(pt_rec.start_date,'MM');
341
342 IF (rent_sched_date < min_sched_date) THEN
343 rent_sched_date := min_sched_date;
344 END IF;
345
346 /* See if a payment schedule exists for this lease and gl period */
347
348 SELECT max(payment_schedule_id)
349 INTO ps_id
350 FROM pn_payment_schedules_all /*sdm14jul*/
351 WHERE lease_id = pt_rec.lease_id
352 AND schedule_date = rent_sched_date;
353
354 IF ps_id is null THEN
355
356 SELECT pn_payment_schedules_s.nextval
357 INTO ps_id
358 FROM dual;
359
360 /*sdm14jul*/
361 SELECT org_id INTO l_org_id
362 FROM pn_leases_all
363 WHERE lease_id = pt_rec.lease_id;
364
365 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
366 ( payment_schedule_id,
367 last_update_date,
368 last_updated_by,
369 creation_date,
370 created_by,
371 schedule_date,
372 lease_id,
373 lease_change_id,
374 payment_status_lookup_code,
375 org_id /*sdm14jul*/
376 )
377 values
378 ( ps_id,sysdate,
379 pn_user_id,
380 sysdate,
381 pn_user_id,
382 rent_sched_date,
383 pt_rec.lease_id,
384 pt_rec.lease_change_id,
385 'DRAFT',
386 l_org_id /*sdm14jul*/
387 );
388
389 END IF;
390
391 INSERT INTO pn_payment_items_all /*sdm14jul*/
392 ( payment_item_id,
393 last_update_date,
394 last_updated_by,
395 creation_date,
396 created_by,
397 actual_amount,
398 estimated_amount,
399 due_date,
400 payment_item_type_lookup_code,
401 payment_term_id,
402 payment_schedule_id,
403 period_fraction,
404 vendor_id,
405 customer_id,
406 vendor_site_id,
407 customer_site_use_id,
408 set_of_books_id,
409 currency_code,
410 export_currency_code,
411 export_currency_amount,
412 rate,
413 org_id /*sdm14jul*/
414 )
415 VALUES
416 ( pn_payment_items_s.nextval,
417 sysdate,
418 pn_user_id,
419 sysdate,
420 pn_user_id,
421 pt_rec.actual_amount,
422 decode(pt_rec.actual_amount,null,pt_rec.estimated_amount,null),
423 pt_rec.start_date,
424 'CASH',
425 pt_rec.payment_term_id,
426 ps_id,
427 1,
428 pt_rec.vendor_id,
429 pt_rec.customer_id,
430 pt_rec.vendor_site_id,
431 pt_rec.customer_site_use_id,
432 pt_rec.set_of_books_id,
433 pt_rec.currency_code,
434 pt_rec.currency_code,
435 pt_rec.actual_amount,
436 1,
437 l_org_id /*sdm14jul*/
438 );
439
440 /* Prepayment offseting payment */
441
442 rent_sched_date := Trunc(pt_rec.target_date,'MM');
443
444 IF (rent_sched_date < min_sched_date) THEN
445 rent_sched_date := min_sched_date;
446 END IF;
447
448 /* See if a payment schedule exists for this lease and gl period */
449
450 SELECT max(payment_schedule_id)
451 INTO ps_id
452 FROM pn_payment_schedules_all /*sdm14jul*/
453 WHERE lease_id = pt_rec.lease_id
454 AND schedule_date = rent_sched_date;
455
456 IF ps_id is null THEN
457
458 SELECT pn_payment_schedules_s.nextval
459 INTO ps_id
460 FROM dual;
461
462 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
463 ( payment_schedule_id,
464 last_update_date,
465 last_updated_by,
466 creation_date,
467 created_by,
468 schedule_date,
469 lease_id,
470 lease_change_id,
471 payment_status_lookup_code,
472 org_id /*sdm14jul*/
473 )
474 values
475 ( ps_id,sysdate,
476 pn_user_id,
477 sysdate,
478 pn_user_id,
479 rent_sched_date,
480 pt_rec.lease_id,
481 pt_rec.lease_change_id,
482 'DRAFT',
483 l_org_id /*sdm14jul*/
484 );
485
486 END IF;
487
488 INSERT INTO pn_payment_items_all /*sdm14jul*/
489 ( payment_item_id,
490 last_update_date,
491 last_updated_by,
492 creation_date,
493 created_by,
494 actual_amount,
495 estimated_amount,
496 due_date,
497 payment_item_type_lookup_code,
498 payment_term_id,
499 payment_schedule_id,
500 period_fraction,
501 vendor_id,
502 customer_id,
503 vendor_site_id,
504 customer_site_use_id,
505 set_of_books_id,
506 currency_code,
507 export_currency_code,
508 export_currency_amount,
509 rate,
510 org_id /*sdm14jul*/
511 )
512 VALUES
513 ( pn_payment_items_s.nextval,
514 sysdate,
515 pn_user_id,
516 sysdate,
517 pn_user_id,
518 -pt_rec.actual_amount,
519 decode(pt_rec.actual_amount,null,-pt_rec.estimated_amount,null),
520 pt_rec.target_date,
521 'CASH',
522 pt_rec.payment_term_id,
523 ps_id,
524 1,
525 pt_rec.vendor_id,
526 pt_rec.customer_id,
527 pt_rec.vendor_site_id,
528 pt_rec.customer_site_use_id,
529 pt_rec.set_of_books_id,
530 pt_rec.currency_code,
531 pt_rec.currency_code,
532 pt_rec.actual_amount,
533 1,
534 l_org_id /*sdm14jul*/
535 );
536
537 /* End of CASH Pre-payments */
538
539 ELSE IF ((pt_rec.frequency_code = 'OT') and (iteration = 1)) THEN /* IF D */
540
541 DELETE FROM pn_payment_items_all /*sdm14jul*/
542 WHERE payment_term_id = pt_rec.payment_term_id
546 -- create OT 's
543 AND payment_item_type_lookup_code = 'NORMALIZED';
544
545 ------------------------------------------------
547 ------------------------------------------------
548 CREATE_OT_PAYMENT_ITEMS (pt_rec.payment_term_id);
549
550 ELSE IF (iteration = 2) THEN
551
552 SELECT count(*)
553 INTO actual_items
554 FROM pn_payment_items_all /*sdm14jul*/
555 WHERE payment_term_id = pt_rec.payment_term_id
556 AND actual_amount is NOT NULL
557 AND payment_item_type_lookup_code = 'CASH';
558
559 ELSE
560
561 actual_items := 0;
562
563 END IF;
564
565 IF (iteration = 1) or
566 ((pt_rec.actual_amount is not null) and (pt_rec.payment_term_type_code in ('BASE','ABATE'))) or /* IF E */
567 (actual_items <> 0) THEN /* Normalize actual base and abates if iteration = 2 or
568 Cash base and abates if iteration = 1 */
569
570 SELECT count(*)
571 INTO items_paid
572 FROM pn_payment_items_all pi, /*sdm14jul*/
573 pn_payment_schedules_all ps /*sdm14jul*/
574 WHERE pi.payment_schedule_id = ps.payment_schedule_id
575 AND pi.payment_term_id = pt_rec.payment_term_id
576 AND pi.payment_item_type_lookup_code = 'CASH'
577 AND ps.payment_status_lookup_code <> 'DRAFT';
578
579 IF (items_paid = 0) or (iteration = 2) THEN /* IF F */
580
581 IF (iteration = 1) THEN /* IF G */
582
583 DELETE FROM pn_payment_items_all /*sdm14jul*/
584 WHERE payment_term_id = pt_rec.payment_term_id;
585
586 ELSE
587
588 DELETE FROM pn_payment_items_all /*sdm14jul*/
589 WHERE payment_term_id = pt_rec.payment_term_id
590 AND payment_item_type_lookup_code = 'NORMALIZED';
591
592 /* Loop and figure out NOCOPY sum of partial months */
593
594 total_partial := 0;
595
596 IF TO_NUMBER(TO_CHAR(cur_start_date,'DD')) >= day_to_pay THEN
597
598 payment_date := trunc(cur_start_date,'MM') + day_to_pay - 1;
599
600 IF trunc(payment_date,'MM') <> trunc(cur_start_date,'MM') THEN
601 payment_date := LAST_DAY(cur_start_date);
602 END IF;
603
604 ELSE
605
606 payment_date := add_months(trunc( cur_start_date,'MM'), - 1) + day_to_pay - 1;
607
608 IF trunc(payment_date,'MM') <> add_months(trunc(cur_start_date,'MM'),1) THEN
609
610 payment_date := LAST_DAY(add_months( cur_start_date,-1));
611
612 END IF;
613
614 END IF;
615
616 LOOP /* Loop D */
617
618 next_payment_date := add_months(trunc( payment_date,'MM'),cur_months) +
619 day_to_pay -1;
620
621
622 IF trunc(next_payment_date,'MM') <> add_months(
623 trunc(payment_date,'MM'),cur_months) THEN
624 next_payment_date := LAST_DAY(add_months( trunc(payment_date,'MM'),cur_months));
625
626 END IF;
627
628 IF (cur_start_date < payment_date) THEN
629 term_on_start_date := payment_date;
630 ELSE
631 term_on_start_date := cur_start_date;
632 END IF;
633
634 IF (next_payment_date - 1 > cur_end_date) THEN
635 term_on_end_date := cur_end_date;
636 ELSE
637 term_on_end_date := next_payment_date - 1;
638 END IF;
639
640 /* PL SQL bug sometimes returns a negative value for the months between function */
641
642 months := months_between(term_on_start_date, term_on_end_date + 1);
643
644 IF (months < 0) then
645 months := -months;
646 END IF;
647
648 months := Trunc(months);
649
650 /* Calculate partial months that are in range */
651
652 partial := (term_on_end_date + 1 - add_months(term_on_start_date, months))
653 * 12 / pt_rec.proration_rule;
654
655 IF partial > 1 THEN
656 partial := 1;
657 END IF;
658
659 months := months + partial;
660 total_partial := total_partial + months;
661
662 EXIT WHEN next_payment_date > cur_end_date;
663
664 payment_date := next_payment_date;
665
666 END LOOP; /* Loop D */
667
668 /* Find total of all cash items */
669
670 SELECT nvl(sum(actual_amount),0)
671 INTO total_cash
672 FROM pn_payment_items_all /*sdm14jul*/
673 WHERE payment_term_id = pt_rec.payment_term_id
674 AND payment_item_type_lookup_code = 'CASH';
675
676 /* Set cur_amount to this the normalized per period */
677
678 IF (total_cash <> 0) THEN
679 cur_amount := total_cash / total_partial;
680 ELSE
681 cur_amount := 0;
682 END IF;
683
684 END IF; /* IF G */
685
686 /* If amount is null, we are using the estimated amount,
687 otherwise this is a known escalation. */
688
689 IF TO_NUMBER(TO_CHAR(cur_start_date,'DD')) >= day_to_pay THEN
690
691 payment_date := trunc(cur_start_date,'MM') + day_to_pay - 1;
692
693 IF trunc(payment_date,'MM') <> trunc(cur_start_date,'MM') THEN
694 payment_date := LAST_DAY(cur_start_date);
695 END IF;
696
697 ELSE
698
699 payment_date := add_months(trunc(cur_start_date, 'MM'), - 1) + day_to_pay - 1;
700
701 IF trunc(payment_date,'MM') <> add_months(trunc(cur_start_date,'MM'),1) THEN
702 payment_date := LAST_DAY(add_months( cur_start_date,-1));
703 END IF;
704
705 END IF;
706
707 /* Process each payment item */
708
709 LOOP /* Loop E */
710
711 /* Determine the next payment date to define the date range of the current payment */
712
713 next_payment_date := add_months(trunc(payment_date, 'MM'),cur_months) + day_to_pay -1;
714
715
716 IF trunc(next_payment_date,'MM') <> add_months( trunc(payment_date,'MM'),cur_months)
717 THEN
718 next_payment_date := LAST_DAY(add_months(trunc (payment_date,'MM'),cur_months));
719 END IF;
720
721 rent_sched_date := Trunc(payment_date,'MM');
722
723 IF (rent_sched_date < min_sched_date) and (iteration = 1) THEN
724 rent_sched_date := min_sched_date;
725 END IF;
726
727 /* See if a payment schedule exists for this lease and gl period */
728
729 SELECT max(payment_schedule_id)
730 INTO ps_id
731 FROM pn_payment_schedules_all /*sdm14jul*/
732 WHERE lease_id = pt_rec.lease_id
733 AND schedule_date = rent_sched_date;
734
735 IF ps_id is null THEN
736
737 SELECT pn_payment_schedules_s.nextval
738 INTO ps_id
739 FROM dual;
740
741 IF (rent_sched_date < min_sched_date) THEN
742
743 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
744 (
745 payment_schedule_id,
746 last_update_date,
747 last_updated_by,creation_date,created_by,
748 schedule_date, lease_id,
749 lease_change_id,
750 payment_status_lookup_code,
751 org_id
752 )
753 VALUES
754 (
755 ps_id,
756 sysdate,
757 pn_user_id,
758 sysdate,
759 pn_user_id,
760 rent_sched_date,
761 pt_rec.lease_id,
762 pt_rec.lease_change_id,
763 'APPROVED',
764 l_org_id
765 );
766 ELSE
767 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
768 (
769 payment_schedule_id,
770 last_update_date,
771 last_updated_by,
772 creation_date,
773 created_by,
774 schedule_date,
775 lease_id,
776 lease_change_id,
777 payment_status_lookup_code,
778 org_id
779 )
780 VALUES
781 (
782 ps_id,
783 sysdate,
784 pn_user_id,
785 sysdate,
786 pn_user_id,
787 rent_sched_date,
788 pt_rec.lease_id,
789 pt_rec.lease_change_id,
790 'DRAFT',
791 l_org_id
792 );
793
794 END IF;
798 IF (cur_start_date < payment_date) THEN
795
796 END IF;
797
799 term_on_start_date := payment_date;
800 ELSE
801 term_on_start_date := cur_start_date;
802 END IF;
803
804 IF (next_payment_date - 1 > cur_end_date) THEN
805 term_on_end_date := cur_end_date;
806 ELSE
807 term_on_end_date := next_payment_date - 1;
808 END IF;
809
810 /* PL SQL bug sometimes returns a negative value for the months between function */
811
812 months := months_between(term_on_start_date, term_on_end_date + 1);
813
814 IF (months < 0) then
815 months := -months;
816 END IF;
817
818 months := Trunc(months);
819
820 /* Calculate partial months that are in range */
821
822 partial := (term_on_end_date + 1 - add_months(term_on_start_date, months))
823 * 12 / pt_rec.proration_rule;
824
825 IF partial > 1 THEN
826 partial := 1;
827 END IF;
828
829 months := months + partial;
830
831 IF ((pt_rec.actual_amount is null) and (iteration = 1)) THEN
832 amount := cur_estimated_amount * months / cur_months;
833 ELSE
834 amount := cur_amount * months / cur_months;
835 END IF;
836
837 IF (iteration = 1) THEN
838 round_amount := round(amount,2);
839 ELSE
840 round_amount := amount;
841 END IF;
842
843 INSERT INTO pn_payment_items_all /*sdm14jul*/
844 (
845 payment_item_id,
846 last_update_date,
847 last_updated_by,
848 creation_date,
849 created_by,
850 actual_amount,
851 estimated_amount,
852 due_date,
853 payment_item_type_lookup_code,
854 payment_term_id,
855 payment_schedule_id,
856 period_fraction,
857 vendor_id,
858 customer_id,
859 vendor_site_id,
860 customer_site_use_id,
861 set_of_books_id,
862 currency_code,
863 export_currency_code,
864 export_currency_amount,
865 rate,
866 org_id /*sdm14jul*/
867 )
868 values
869 ( pn_payment_items_s.nextval,
870 sysdate,
871 pn_user_id,
872 sysdate,
873 pn_user_id,
874 decode (iteration,2,
875 round_amount,
876 decode (pt_rec.actual_amount,null,
877 null,round_amount
878 )
879 ),
880 decode(iteration,2,
881 null,
882 decode(pt_rec.actual_amount,null,
883 round_amount,null
884 )
885 ),
886 payment_date,
887 cur_pit_lookup_code,
888 pt_rec.payment_term_id,
889 ps_id,
890 amount/decode(iteration,2,
891 decode(cur_amount,0,
892 1, cur_amount
893 ),
894 decode(pt_rec.actual_amount,null,
895 cur_estimated_amount, cur_amount
896 )
897 ),
898 decode(iteration,2,
899 null,pt_rec.vendor_id
900 ),
901 decode(iteration,2,
902 null,pt_rec.customer_id
903 ),
904 decode(iteration,2,
905 null,pt_rec.vendor_site_id
906 ),
907 decode(iteration,2,
908 null,pt_rec.customer_site_use_id
909 ),
910 pt_rec.set_of_books_id,
911 pt_rec.currency_code,
912 pt_rec.currency_code,
913 decode (iteration,2,
914 round_amount,
915 decode (pt_rec.actual_amount,null,
916 null,round_amount
917 )
918 ),
919 1,
920 l_org_id /*sdm14jul*/
921 );
922
923 EXIT WHEN next_payment_date > cur_end_date;
924
925 payment_date := next_payment_date;
926
927 END LOOP; /* Loop E */
928
929 ELSE /* Some items have been paid */
930
931 DELETE from pn_payment_items_all /*sdm14jul*/
932 WHERE payment_term_id = pt_rec.payment_term_id
933 and payment_item_type_lookup_code = 'NORMALIZED';
934
935 UPDATE pn_payment_items_all /*sdm14jul*/
936 SET actual_amount = cur_amount * period_fraction,
937 estimated_amount = decode(pt_rec.actual_amount,null,
938 cur_estimated_amount * period_fraction,
939 null
940 ),
941 last_update_date = sysdate,
942 last_updated_by = pn_user_id
943 WHERE payment_term_id = pt_rec.payment_term_id
944 AND payment_item_type_lookup_code = 'CASH'
945 AND payment_schedule_id in (
946 SELECT payment_schedule_id
947 FROM pn_payment_schedules_all /*sdm14jul*/
948 WHERE payment_status_lookup_code = 'DRAFT'
949 );
950
951 END IF; /* IF F */
952
953 END IF; /* IF E */
954
955 /* Normalize for estimates and non-BASE or ABATEs */
956 IF (iteration = 2) THEN
957
958 INSERT INTO pn_payment_items_all /*sdm14jul*/
959 (
960 payment_item_id,
961 last_update_date,
962 last_updated_by,
963 creation_date,
964 created_by,
965 estimated_amount,
966 due_date,
967 payment_item_type_lookup_code,
968 payment_term_id,
969 payment_schedule_id,
970 period_fraction,
971 set_of_books_id,
972 currency_code,
973 rate,
974 org_id /*sdm14jul*/
975 )
976 SELECT pn_payment_items_s.nextval,
977 sysdate,
978 pn_user_id,
979 sysdate,
980 pn_user_id,
981 nvl(actual_amount, estimated_amount),
982 due_date,
983 'NORMALIZED',
984 payment_term_id,
985 payment_schedule_id,
986 1,
987 pt_rec.set_of_books_id,
988 pt_rec.currency_code,
989 1,
990 l_org_id /*sdm14jul*/
991 FROM pn_payment_items_all /*sdm14jul*/
992 WHERE payment_term_id = pt_rec.payment_term_id
993 AND actual_amount is null;
994 END IF;
995
996 END IF; /* IF D */
997
998 END IF; /* IF A */
999
1000 END LOOP; /* Loop A */
1001
1002 END LOOP;
1003
1004 CLOSE pt_cur;
1005 commit;
1006
1007 END;
1008
1009 END PN_GENERATE_PMT_SCHED;