[Home] [Help]
PACKAGE BODY: APPS.PN_MODIFY_PMT_SCHED
Source
1 PACKAGE BODY PN_MODIFY_PMT_SCHED AS
2 -- $Header: PNMPMTSB.pls 120.1 2005/07/25 06:47:10 appldev ship $
3
4 --
5 --
6
7 -------------------------------------------------------------------------------
8 -- PROCDURE : modify_sched_and_items
9 -- INVOKED FROM :
10 -- PURPOSE :
11 -- HISTORY :
12 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
13 -------------------------------------------------------------------------------
14 procedure modify_sched_and_items (
15 error_buf OUT NOCOPY VARCHAR2,
16 ret_code OUT NOCOPY VARCHAR2,
17 pn_lease_id IN NUMBER,
18 pn_user_id IN NUMBER
19 ) IS
20
21
22 CURSOR pt_cur (p_lease_id NUMBER) IS
23 SELECT payment_term_id,
24 start_date,
25 end_date,
26 actual_amount,
27 estimated_amount,
28 payment_term_type_code,
29 frequency_code,
30 lease_id,
31 set_of_books_id,
32 currency_code
33 FROM pn_payment_terms_all /*sdm14jul*/
34 WHERE lease_id = p_lease_id
35 FOR UPDATE OF end_date;
36
37 pt_rec pt_cur%ROWTYPE;
38
39 /* Early Termination Variables */
40
41 first_payment_date DATE;
42 item_to_change_date DATE;
43 items_paid NUMBER;
44 lease_start_date DATE;
45 lease_end_date DATE;
46 pi_id NUMBER;
47 pro_start_date DATE;
48 pt_months NUMBER;
49 sched_status VARCHAR2(30);
50 min_sched_date DATE;
51 l_org_id NUMBER; /*sdm14jul*/
52
53 /* Normalization Variables */
54
55 actual_items NUMBER;
56 amount NUMBER;
57 day_to_pay NUMBER := 1;
58 months NUMBER;
59 next_payment_date DATE;
60 partial NUMBER;
61 payment_date DATE;
62 proration_rule NUMBER;
63 ps_id NUMBER;
64 rent_sched_date DATE;
65 term_on_start_date DATE;
66 term_on_end_date DATE;
67 total_cash NUMBER;
68 total_partial NUMBER;
69 pn_lease_change_id NUMBER;
70
71 BEGIN
72
73 OPEN pt_cur (pn_lease_id);
74
75 LOOP
76
77 FETCH pt_cur INTO pt_rec;
78 EXIT WHEN pt_cur%NOTFOUND;
79
80 SELECT pn_leases.lease_commencement_date,
81 pn_leases.lease_termination_date,
82 nvl(pn_leases.payment_term_rule,365),
83 pn_leases.lease_change_id
84 INTO lease_start_date, lease_end_date, proration_rule, pn_lease_change_id
85 FROM pn_leases_v pn_leases /*sdm??should form view be replaced*/
86 WHERE pn_leases.lease_id = pt_rec.lease_id;
87
88
89 IF (pt_rec.end_date > lease_end_date) THEN
90
91 -------------------------------------------------------------------------
92 -- Hardcoded Frequency Code
93 -------------------------------------------------------------------------
94 IF pt_rec.frequency_code = 'OT' THEN
95 pt_months := 0;
96 ELSIF pt_rec.frequency_code = 'MON' THEN
97 pt_months := 1;
98 ELSIF pt_rec.frequency_code = 'QTR' THEN
99 pt_months := 3;
100 ELSIF pt_rec.frequency_code = 'YR' THEN
101 pt_months := 12;
102 ELSIF pt_rec.frequency_code = 'SA' THEN
103 pt_months := 6;
104 END IF;
105
106 SELECT count(*)
107 INTO items_paid
108 FROM pn_payment_schedules_all ps, /*sdm14jul*/
109 pn_payment_items_all pi /*sdm14jul*/
110 WHERE ps.payment_schedule_id = pi.payment_schedule_id
111 AND pi.payment_term_id = pt_rec.payment_term_id
112 AND ps.payment_status_lookup_code <> 'DRAFT'
113 AND pi.payment_item_type_lookup_code = 'CASH';
114
115 IF ((pt_rec.start_date > lease_end_date) and (items_paid = 0)) THEN
116
117 delete from pn_payment_items_all /*sdm14jul*/
118 where payment_term_id = pt_rec.payment_term_id;
119
120 /* Not Required in PN
121 delete from pn_lease_milestones
122 where payment_term_id = pt_rec.payment_term_id
123 and RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM';*/
124
125 delete from pn_payment_terms_all /*sdm14jul*/
126 where current of pt_cur;
127
128 ELSE IF ((pt_rec.end_date > lease_end_date) or (items_paid <> 0)) THEN
129
130 SELECT min(due_date)
131 INTO first_payment_date
132 FROM pn_payment_items_all /*sdm14jul*/
133 WHERE payment_term_id = pt_rec.payment_term_id
134 AND payment_item_type_lookup_code = 'CASH';
135
136 SELECT nvl(max(due_date),first_payment_date)
137 INTO item_to_change_date
138 FROM pn_payment_items_all /*sdm14jul*/
139 WHERE payment_term_id = pt_rec.payment_term_id
140 AND due_date <= greatest(lease_end_date, pt_rec.start_date);
141
142 -----------------------------------------------------------------------
143 -- No Cash Item exists for Pmt Terms of frequency other than Monthly
144 -----------------------------------------------------------------------
145 BEGIN
146
147 SELECT ps.payment_status_lookup_code, pi.payment_item_id
148 INTO sched_status, pi_id
149 FROM pn_payment_schedules_all ps, /*sdm14jul*/
150 pn_payment_items_all pi /*sdm14jul*/
151 WHERE pi.payment_schedule_id = ps.payment_schedule_id
152 AND pi.due_date = item_to_change_date
153 AND pi.payment_term_id = pt_rec.payment_term_id
154 AND pi.payment_item_type_lookup_code = 'CASH';
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 NULL;
159
160 END;
161
162 IF (sched_status = 'DRAFT') THEN
163
164 IF (first_payment_date = item_to_change_date) THEN
165 pro_start_date := pt_rec.start_date;
166 ELSE
167 pro_start_date := item_to_change_date;
168 END IF;
169
170 /* Prorate partial period from pro_start to termination date */
171
172 /* PL SQL bug sometimes returns a negative value for the months
173 between function */
174
175 months := months_between (lease_end_date + 1, pro_start_date);
176
177 IF (months < 0) then
178 months := -months;
179 END IF;
180
181 months := Trunc(months);
182
183 partial := (lease_end_date + 1 - add_months(pro_start_date,months)) *
184 12 / proration_rule;
185
186 IF partial > 1 then
190 months := months + partial;
187 partial := 1;
188 END IF;
189
191 amount := nvl(pt_rec.actual_amount, pt_rec.estimated_amount) *
192 months / pt_months;
193
194 UPDATE pn_payment_items_all /*sdm14jul*/
195 SET actual_amount = decode(pt_rec.actual_amount,
196 null,null, round(amount,2)),
197 estimated_amount = decode(pt_rec.actual_amount,
198 null,round(amount,2),null),
199 period_fraction = amount / nvl(pt_rec.actual_amount,
200 pt_rec.estimated_amount),
201 last_update_date = sysdate,
202 last_updated_by = pn_user_id
203 WHERE payment_item_id = pi_id
204 AND payment_item_type_lookup_code = 'CASH';
205
206 END IF;
207
208 DELETE from pn_payment_items_all /*sdm14jul*/
209 WHERE payment_term_id = pt_rec.payment_term_id
210 AND due_date > item_to_change_date
211 AND payment_item_type_lookup_code = 'CASH'
212 AND payment_schedule_id in (SELECT payment_schedule_id
213 FROM pn_payment_schedules_all /*sdm14jul*/
214 WHERE payment_status_lookup_code = 'DRAFT'
215 AND lease_id = pt_rec.lease_id
216 );
217
218 IF (lease_end_date) > (pt_rec.start_date) THEN
219
220 update pn_payment_terms_all /*sdm14jul*/
221 set end_date = lease_end_date,
222 last_update_date = sysdate,
223 last_updated_by = pn_user_id
224 where payment_term_id = pt_rec.payment_term_id;
225
226 /* Not Required in PN...
227 update pn_lease_milestones
228 set milestone_date = lease_end_date,
229 last_update_date = sysdate,
230 last_updated_by = pn_user_id
231 where payment_term_id = pt_rec.payment_term_id
232 and RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM'
233 and (description like 'End%'
234 or description like 'Security Deposit refund due');*/
235
236 ELSE
237
238 update pn_payment_terms_all /*sdm14jul*/
239 set end_date = pt_rec.start_date,
240 last_update_date = sysdate,
241 last_updated_by = pn_user_id
242 where payment_term_id = pt_rec.payment_term_id;
243
244 /* Not required in PN....
245 update pn_lease_milestones
246 set milestone_date = pt_rec.start_date,
247 last_update_date = sysdate,
248 last_updated_by = pn_user_id
249 where payment_term_id = pt_rec.payment_term_id
250 and responsibility_lookup_code = 'PAYMENT_TERM'
251 and (description like 'End%'
252 or description like 'Security Deposit refund due');*/
253
254 END IF;
255
256 END IF;
257
258 END IF;
259
260 END IF;
261
262 /* ------------------ Renormalize ------------------------- */
263
264 SELECT max(schedule_date)
265 INTO min_sched_date
266 FROM pn_payment_schedules_all /*sdm14jul*/
267 WHERE lease_id = pt_rec.lease_id
268 AND payment_status_lookup_code <> 'DRAFT';
269
270 IF min_sched_date is not null THEN
271 min_sched_date := add_months(min_sched_date,1);
272 END IF;
273
274 DELETE from pn_payment_items_all /*sdm14jul*/
275 WHERE payment_item_type_lookup_code = 'NORMALIZED'
276 AND payment_term_id = pt_rec.payment_term_id;
277
278 SELECT count(*)
279 INTO actual_items
280 FROM pn_payment_items_all /*sdm14jul*/
281 WHERE payment_term_id = pt_rec.payment_term_id
282 AND actual_amount is not null
283 AND payment_item_type_lookup_code = 'CASH';
284
285 IF ((pt_rec.actual_amount is not null) and
286 (pt_rec.payment_term_type_code in ('BASE','ABATE')))
287 or (actual_items <> 0) THEN /* IF E */
288 /* Normalize actuals base and abates */
289
290 /* Loop and figure out NOCOPY sum of partial months */
291
292 total_partial := 0;
293
294 IF TO_NUMBER(TO_CHAR(lease_start_date,'DD')) >= day_to_pay THEN
295 payment_date := trunc(lease_start_date,'MM') + day_to_pay - 1;
296 IF trunc(payment_date,'MM') <> trunc(lease_start_date,'MM') THEN
297 payment_date := LAST_DAY(lease_start_date);
298 END IF;
299
300 ELSE
301
302 payment_date := add_months(trunc(lease_start_date,'MM'),
303 -1) + day_to_pay - 1;
304 IF trunc(payment_date,'MM') <> add_months(trunc( lease_start_date,'MM'),1) THEN
305 payment_date := LAST_DAY(add_months(lease_start_date,-1));
306 END IF;
307
308 END IF;
309
310 LOOP /* Loop D */
311 next_payment_date := add_months(trunc(payment_date,'MM'),1) + day_to_pay -1;
312
313 IF trunc(next_payment_date,'MM') <> add_months(trunc(payment_date, 'MM'),1) THEN
314 next_payment_date := LAST_DAY(add_months(trunc(payment_date, 'MM'),1));
315 END IF;
316
317 IF (lease_start_date < payment_date) THEN
318 term_on_start_date := payment_date;
319 ELSE
320 term_on_start_date := lease_start_date;
321 END IF;
322
323 IF (next_payment_date - 1 > lease_end_date) THEN
324 term_on_end_date := lease_end_date;
325 ELSE
326 term_on_end_date := next_payment_date - 1;
327 END IF;
328
329 /* PL SQL bug sometimes returns a negative value
330 for the months between function */
331
332 months := months_between(term_on_start_date, term_on_end_date + 1);
333
334 IF (months < 0) then
335 months := -months;
336 END IF;
337
338 months := Trunc(months);
339
340 /* Calculate partial months that are in range */
341
342 partial := (term_on_end_date + 1 - add_months(term_on_start_date, months)) *
343 12 / proration_rule;
344
345 IF partial > 1 THEN
346 partial := 1;
347 END IF;
348
349 months := months + partial;
350
351 total_partial := total_partial + months;
352
353 EXIT WHEN next_payment_date > lease_end_date;
354
355 payment_date := next_payment_date;
356
357 END LOOP; /* Loop D */
358
359 /* Find total of all cash items */
360
361 SELECT nvl(sum(actual_amount),0)
362 INTO total_cash
363 FROM pn_payment_items_all /*sdm14jul*/
364 WHERE payment_term_id = pt_rec.payment_term_id
365 AND payment_item_type_lookup_code = 'CASH';
366
367 /* Set amount to this the normalized per period */
368
369 IF (total_cash <> 0) THEN
370 amount := total_cash / total_partial;
371 ELSE
372 amount := 0;
373 END IF;
374
375 /* If amount is null, we are using the estimated
376 amount, otherwise this is a known escalation. */
377
378 IF TO_NUMBER(TO_CHAR(lease_start_date,'DD')) >= day_to_pay
379 THEN
380 payment_date := trunc(lease_start_date,'MM') + day_to_pay - 1;
381 IF trunc(payment_date,'MM') <> trunc(lease_start_date,'MM') THEN
382 payment_date := LAST_DAY(lease_start_date);
383 END IF;
384
385 ELSE
386
387 payment_date := add_months(trunc(lease_start_date, 'MM'), - 1) + day_to_pay - 1;
388 IF trunc(payment_date,'MM') <> add_months(trunc( lease_start_date, 'MM'),1) THEN
389 payment_date := LAST_DAY(add_months(lease_start_date, -1));
390 END IF;
391
392 END IF;
393
394 /* Process each payment item */
395
396 LOOP /* Loop E */
397
398 /* Determine the next payment date to define the
399 date range of the current payment */
400
401 next_payment_date := add_months(trunc(payment_date, 'MM'),1) + day_to_pay -1;
402
403 IF trunc(next_payment_date,'MM') <> add_months(trunc(payment_date, 'MM'),1) THEN
404 next_payment_date := LAST_DAY(add_months(trunc(payment_date, 'MM'),1));
405 END IF;
406
407 /* Tie to rent_schedule of payment_item_due_date or sysdate
408 whichever is greater. Payment schedules are always
409 assigned to the first of the month. */
410
411 rent_sched_date := Trunc(payment_date,'MM');
412
413 /* See if a payment schedule exists for this lease and gl period */
414
415 SELECT max(payment_schedule_id)
416 INTO ps_id
417 FROM pn_payment_schedules_all /*sdm14jul*/
418 WHERE lease_id = pt_rec.lease_id
419 AND schedule_date = rent_sched_date;
420
421 IF ps_id is null THEN
422
423 SELECT pn_payment_schedules_s.nextval
424 INTO ps_id
425 FROM dual;
426
427 IF (rent_sched_date < min_sched_date) THEN
428
429 /*sdm14jul*/
430 SELECT org_id INTO l_org_id
431 FROM pn_payment_schedules_all
432 WHERE payment_schedule_id = ps_id;
433
434
435 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
436 (
437 payment_schedule_id,
438 last_update_date,
439 last_updated_by,
440 creation_date,
441 created_by,
442 schedule_date,
443 lease_id,
444 lease_change_id,
445 payment_status_lookup_code,
446 org_id /*sdm14jul*/
447 )
448 VALUES
449 ( ps_id,
450 sysdate,
451 pn_user_id,
452 sysdate,
453 pn_user_id,
454 rent_sched_date,
455 pt_rec.lease_id,
456 pn_lease_change_id,
457 'APPROVED',
458 l_org_id /*sdm14jul*/
459 );
460
461 ELSE
462
463 INSERT INTO pn_payment_schedules_all /*sdm14jul*/
464 (
465 payment_schedule_id,
466 last_update_date,
467 last_updated_by,
468 creation_date,
469 created_by,
470 schedule_date,
471 lease_id,
472 lease_change_id,
473 payment_status_lookup_code,
474 org_id /*sdm14jul*/
475 )
476 VALUES
477 (
478 ps_id,
479 sysdate,
480 pn_user_id,
481 sysdate,
482 pn_user_id,
483 rent_sched_date,
484 pt_rec.lease_id,
485 pn_lease_change_id,
486 'DRAFT',
487 l_org_id /*sdm14jul*/
488 );
489 END IF;
490 END IF;
491
492 IF (lease_start_date < payment_date) THEN
493 term_on_start_date := payment_date;
494 ELSE
495 term_on_start_date := lease_start_date;
496 END IF;
497
498 IF (next_payment_date - 1 > lease_end_date) THEN
499 term_on_end_date := lease_end_date;
500 ELSE
501 term_on_end_date := next_payment_date - 1;
502 END IF;
503
504 /* PL SQL bug sometimes returns a negative value
505 for the months between function */
506
507 months := months_between(term_on_start_date, term_on_end_date + 1);
508
509 IF (months < 0) then
510 months := -months;
511 END IF;
512
513 months := Trunc(months);
514
515 /* Calculate partial months that are in range */
516
517 partial := (term_on_end_date + 1 - add_months(term_on_start_date, months)) *
518 12 / proration_rule;
519
520 IF partial > 1 THEN
521 partial := 1;
522 END IF;
523
524 months := months + partial;
525
526 amount := amount * months ;
527
528 ---------------------------------------------------------------
529 -- No Need to create NORMALIZED items when amount = 0
530 ---------------------------------------------------------------
531 if nvl(amount,0) <> 0 then
532
533 /*sdm14jul*/
534 SELECT org_id INTO l_org_id
535 FROM pn_payment_terms_all
536 WHERE payment_term_id = pt_rec.payment_term_id;
537
538 INSERT INTO pn_payment_items_all /*sdm14jul*/
539 (
540 payment_item_id,
541 last_update_date,
542 last_updated_by,
543 creation_date,
544 created_by,
545 actual_amount,
546 estimated_amount,
547 due_date,
548 payment_item_type_lookup_code,
549 payment_term_id,
550 payment_schedule_id,
551 period_fraction,
552 vendor_id,
553 customer_id,
554 vendor_site_id,
555 customer_site_use_id,
556 set_of_books_id,
557 currency_code,
558 rate,
559 org_id /*sdm14jul*/
560 )
561 VALUES
562 (
563 pn_payment_items_s.nextval,
564 sysdate,
565 pn_user_id,
566 sysdate,
567 pn_user_id,
568 amount,
569 null,
570 payment_date,
571 'NORMALIZED',
572 pt_rec.payment_term_id,
573 ps_id,
574 amount/decode(amount,0,1,months),
575 null,
576 null,
577 null,
578 null,
579 pt_rec.set_of_books_id,
580 pt_rec.currency_code,
581 1,
582 l_org_id /*sdm14jul*/
583 );
584 end if;
585
586 EXIT WHEN next_payment_date > lease_end_date;
587
588 payment_date := next_payment_date;
589
590 END LOOP; /* Loop E */
591
592 END IF; /* IF E */
593
594 INSERT INTO pn_payment_items_all /*sdm14jul*/
595 (
596 payment_item_id,
597 last_update_date,
598 last_updated_by,
599 creation_date,
600 created_by,
601 estimated_amount,
602 due_date,
603 payment_item_type_lookup_code,
604 payment_term_id,
605 payment_schedule_id,
606 period_fraction,
607 set_of_books_id,
608 currency_code,
609 rate,
610 org_id
611 )
612 SELECT
613 pn_payment_items_s.nextval,
614 sysdate,
615 pn_user_id,
616 sysdate,
617 pn_user_id,
618 nvl(actual_amount, estimated_amount),
619 due_date,
620 'NORMALIZED',
621 payment_term_id,
622 payment_schedule_id,
623 1,
624 set_of_books_id,
625 currency_code,
626 rate,
627 l_org_id /*sdm14jul*/
628 FROM pn_payment_items_all /*sdm14jul*/
629 WHERE payment_term_id = pt_rec.payment_term_id
630 AND actual_amount is null;
631
632 END LOOP;
633
634 CLOSE pt_cur;
635
636 DELETE FROM pn_payment_schedules_all ps /*sdm14jul*/
637 WHERE ps.lease_id = pn_lease_id
638 AND NOT exists (SELECT 'x'
639 FROM pn_payment_items_all pi /*sdm14jul*/
640 WHERE pi.payment_schedule_id = ps.payment_schedule_id);
641
642 commit;
643 END;
644
645 END PN_MODIFY_PMT_SCHED;