1 PACKAGE BODY pnt_payment_schedules_pkg AS
2 -- $Header: PNTPYSCB.pls 120.3 2005/12/01 09:56:48 appldev ship $
3
4 /*============================================================================+
5 -- NAME : check_payment_schedule_date
6 -- DESCRIPTION : Perform checks (mentioned below) on schedule_date of a
7 -- Payment Schedule record FOR a given lease:
8 -- o Disallow creation of a payment schedule record IF there
9 -- exists a record in pn_payment_schedules FOR the same
10 -- schedule_date
11 -- o Disallow creation of a "DRAFT" payment schedule record IF
12 -- a future payment schedule exists in status "APPROVED"
13 -- o Disallow creation of an "APPROVED" payment schedule record
14 -- IF a past payment schedule exists in status "DRAFT"
15 -- NOTES : Used by PNTAUPMT form (Authorize Payments)
16 -- SCOPE : PUBLIC
17 -- INVOKED FROM :
18 -- ARGUMENTS : IN : x_lease_id,x_schedule_date,x_rowid,
19 -- x_payment_status_lookup_code.
20 -- OUT : NONE
21 -- RETURNS :
22 -- REFERENCE :
23 -- HISTORY :
24 -- 14-JUL-98 ntandon o Created.
25 -- 21-AUG-03 ftanudja o Modified 'exists' logic to include day. 3089171.
26 -- 07-NOV-03 ftanudja o Bug #3240284 - Added 'day' constraint for csr
27 -- later_notin_draft.
28 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
29 -- with _ALL table.
30 +============================================================================*/
31 PROCEDURE check_payment_schedule_date (
32 x_lease_id IN NUMBER,
33 x_schedule_date IN DATE,
34 x_payment_status_lookup_code IN VARCHAR2,
35 x_rowid IN VARCHAR2
36 )
37 IS
38
39 CURSOR later_notin_draft IS
40 SELECT 'x'
41 FROM pn_payment_schedules_all
42 WHERE lease_id = x_lease_id
43 AND payment_status_lookup_code <> 'DRAFT'
44 AND schedule_date > x_schedule_date
45 AND to_char(schedule_date,'DD') = to_char(x_schedule_date,'DD');
46
47 CURSOR prior_in_draft IS
48 SELECT 'x'
49 FROM pn_payment_schedules_all
50 WHERE lease_id = x_lease_id
51 AND payment_status_lookup_code = 'DRAFT'
52 AND schedule_date < x_schedule_date;
53
54 dummy NUMBER;
55
56 BEGIN
57
58 -----------------------------------------------------------------------------
59 -- Check IF a payment schedule already exists FOR a lease on the same date
60 -----------------------------------------------------------------------------
61
62 -----------------------------------------------------------------------------
63 -- Bug Fix FOR the Bug ID#1210779.
64 -- WHEN creating a new schedule payment schedule the user should not be
65 -- allowed to create an additional payment schedule in the same GL period.
66 -----------------------------------------------------------------------------
67
68 BEGIN
69
70 SELECT 1
71 INTO dummy
72 FROM DUAL
73 WHERE NOT EXISTS (SELECT 1
74 FROM pn_payment_schedules_all
75 WHERE lease_id = x_lease_id
76 AND TO_CHAR(schedule_date,'YYYY-MON-DD') = TO_CHAR(x_schedule_date,'YYYY-MON-DD')
77 AND (( x_rowid IS NULL ) or (rowid <> x_rowid))
78 );
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 fnd_message.set_name ('PN', 'PN_PMT_SCHEDULE_ALREADY_EXISTS');
82 app_exception.Raise_Exception;
83
84 END;
85
86 IF x_payment_status_lookup_code = 'DRAFT' THEN
87
88 FOR i IN later_notin_draft
89 LOOP
90 fnd_message.set_name ('PN', 'PN_LATER_SCHED_DATE_NOT_DRAFT');
91 app_exception.Raise_Exception;
92 EXIT;
93 END LOOP;
94
95 ELSE
96
97 FOR i IN prior_in_draft
98 LOOP
99 fnd_message.set_name ('PN', 'PN_PRIOR_SCHED_DATE_IN_DRAFT');
100 app_exception.Raise_Exception;
101 EXIT;
102 END LOOP;
103
104 END IF;
105
106 END check_payment_schedule_date;
107
108 /*============================================================================+
109 -- NAME : check_payment_status
110 -- DESCRIPTION : Determine IF there exists a payment_item of type 'CASH' in
111 -- pn_payment_items with actual_amount = 0. IF 'YES' THEN the
112 -- payment schedule cannot be APPROVED
113 -- The PROCEDURE passes back the error_flag to the calling
114 -- routine to decide whether to RAISE a fatal error or not.
115 -- NOTES : Used by PNTAUPMT form (Authorize Payments)
116 -- SCOPE : PUBLIC
117 -- INVOKED FROM :
118 -- ARGUMENTS : IN : x_payment_schedule_id,x_payment_status_lookup_code,
119 -- x_error_flag.
120 -- OUT : x_error_flag
121 -- RETURNS :
122 -- REFERENCE :
123 -- HISTORY :
124 -- 14-JUL-98 Neeraj Tandon o Created
125 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
126 -- with _ALL table.
127 +===========================================================================*/
128 PROCEDURE check_payment_status (
129 x_payment_schedule_id IN NUMBER,
130 x_payment_status_lookup_code IN VARCHAR,
131 x_error_flag IN OUT NOCOPY VARCHAR2
132 ) IS
133
134 CURSOR is_actual_amount_NULL IS
135 SELECT 'x'
136 FROM pn_payment_items_all
137 WHERE payment_item_type_lookup_code = 'CASH'
138 AND payment_schedule_id = x_payment_schedule_id
139 AND NVL(actual_amount,0) = 0;
140
141 BEGIN
142
143 RETURN; /*-- Fixing bug 783419 by making this a do-nothing PROCEDURE --*/
144
145 FOR i IN is_actual_amount_NULL
146 LOOP
147 fnd_message.set_name ('PN', 'PN_ACTUAL_ITEM_AMOUNT_IS_NULL');
148 x_error_flag := 'Y';
149 EXIT;
150 END LOOP;
151
152 END check_payment_status;
153
154
155 /*=============================================================================+
156 -- NAME : get_next_payment_schedule
157 -- DESCRIPTION : FETCH the next 'DRAFT' payment schedule FOR a given lease to
158 -- allow DEFER payment item functionality. i.e. A payment item
159 -- can only be defered IF a future payment schedule exists in
160 -- status 'DRAFT'.
161 -- NOTES : Used by PNTAUPMT form (Authorize Payments)
162 -- SCOPE : PUBLIC
163 -- INVOKED FROM :
164 -- ARGUMENTS : IN : x_lease_id,x_schedule_date,
165 -- x_next_payment_schedule_id.
166 -- OUT : x_next_payment_schedule_id
167 -- RETURNS :
168 -- REFERENCE :
169 -- HISTORY :
170 -- 14-JUL-98 Neeraj Tandon o Created
171 -- 20-aug-01 achauhan o Added a check in the cursor to get the next
172 -- available schedule for the same schedule day.
173 -- 15-JUN-04 Kiran o Removed the previous check. We will now get
174 -- the next available schedule. bug # 3644937
175 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
176 -- with _ALL table.
177 +============================================================================*/
178 PROCEDURE get_next_payment_schedule (
179 x_lease_id IN NUMBER,
180 x_schedule_date IN DATE,
181 x_next_payment_schedule_id IN OUT NOCOPY NUMBER
182 ) IS
183
184 CURSOR next_schedule IS
185 SELECT payment_schedule_id
186 FROM pn_payment_schedules_all
187 WHERE lease_id = x_lease_id
188 AND schedule_date > x_schedule_date
189 AND payment_status_lookup_code = 'DRAFT'
190 ORDER BY schedule_date;
191
192 BEGIN
193
194 FOR i IN next_schedule
195 LOOP
196 x_next_payment_schedule_id := i.payment_schedule_id;
197 EXIT;
198 END LOOP;
199
200 END get_next_payment_schedule;
201
202
203 /*============================================================================+
204 -- NAME : mark_pmt_items_exportable
205 -- DESCRIPTION : UPDATE export_to_ap_flag in pn_payment_items(CASH items only)
206 -- with 'Y' WHEN a payment schedule IS authorized (APPROVED).
207 -- This will simplify the logic in Export to AP form which
208 -- currently navigates to every row FOR marking (updating) the
209 -- payment item FOR export.
210 -- NOTES : Used by PNTAUPMT form (Authorize Payments) AND the
211 -- Server table handler (insert/update)
212 -- SCOPE : PUBLIC
213 -- INVOKED FROM :
214 -- ARGUMENTS : IN : x_payment_schedule_id,x_payment_status_lookup_code,
215 -- x_export_flag.
216 -- OUT : NONE
217 -- RETURNS :
218 -- REFERENCE :
219 -- HISTORY :
220 -- 14-JUL-98 Neeraj Tandon o Created
221 -- 25-MAR-99 Neeraj Tandon o Added filter FOR 'CASH' items
222 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
223 -- with _ALL table.
224 +============================================================================*/
225 PROCEDURE MARK_PMT_ITEMS_EXPORTABLE (
226 x_payment_status_lookup_code IN VARCHAR2,
227 x_payment_schedule_id IN NUMBER,
228 x_export_flag IN VARCHAR2
229 )
230
231 IS
232
233 BEGIN
234
235 UPDATE pn_payment_items_all
236 SET export_to_ap_flag = x_export_flag
237 WHERE payment_schedule_id = x_payment_schedule_id
238 AND payment_item_type_lookup_code = 'CASH';
239
240 IF (SQL%NOTFOUND) THEN
241 NULL;
242 END IF;
243
244 END mark_pmt_items_exportable;
245
246
247 /*============================================================================+
248 -- NAME : mark_billing_items_exportable
249 -- DESCRIPTION : UPDATE export_to_ar_flag in pn_payment_items (CASH items only)
250 -- with 'Y' WHEN a payment schedule IS authorized (APPROVED).
251 -- This will simplify the logic in Export to AR form which currently
252 -- navigates to every row for marking (updating) the payment item
253 -- for export.
254 -- NOTES : Used by PNTAUPMT form (Authorize Payments) AND the
255 -- Server table handler (insert/update)
256 -- SCOPE : PUBLIC
257 -- INVOKED FROM :
258 -- ARGUMENTS : IN : x_payment_schedule_id,x_payment_status_lookup_code,
259 -- x_export_flag.
260 -- OUT : NONE
261 -- RETURNS :
262 -- REFERENCE :
263 -- HISTORY :
264 -- 14-JUL-98 Neeraj Tandon o Created
265 -- 25-MAR-99 Neeraj Tandon o Added filter FOR 'CASH' items
266 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
267 -- with _ALL table.
268 +=============================================================================*/
269 PROCEDURE MARK_BILLING_ITEMS_EXPORTABLE (
270 x_payment_status_lookup_code IN VARCHAR2,
271 x_payment_schedule_id IN NUMBER,
272 x_export_flag IN VARCHAR2
273 )
274
275 IS
276
277 BEGIN
278
279 UPDATE pn_payment_items_all
280 SET export_to_ar_flag = x_export_flag
281 WHERE payment_schedule_id = x_payment_schedule_id
282 AND payment_item_type_lookup_code = 'CASH';
283
284 IF (SQL%NOTFOUND) THEN
285 NULL;
286 END IF;
287
288 END mark_billing_items_exportable;
289
290 /*============================================================================+
291 -- NAME : check_on_hold
292 -- DESCRIPTION : Checks FOR any payment schedule On-Hold prior to the payment
293 -- schedule, in question. if there is any, then the payment,
294 -- schedule, in qustion, cannot be approved.
295 -- NOTES : Used by PNTAUPMT form (Authorize Payments).
296 -- SCOPE : PUBLIC
297 -- INVOKED FROM :
298 -- ARGUMENTS : IN : x_lease_id,x_schedule_date
299 -- OUT : NONE
300 -- RETURNS :
301 -- REFERENCE :
302 -- HISTORY :
303 -- 28-DEC-00 Mrinal Misra o Created
304 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
305 -- with _ALL table.
306 =============================================================================*/
307 PROCEDURE check_on_hold (
308 x_lease_id IN NUMBER,
309 x_schedule_date IN DATE
310 ) IS
311
312 l_check_cond VARCHAR2(1) := NULL;
313
314 CURSOR on_hold_cur IS
315 SELECT 'A'
316 FROM DUAL
317 WHERE EXISTS (SELECT NULL
318 FROM pn_payment_schedules_all
319 WHERE lease_id = x_lease_id
320 AND schedule_date < x_schedule_date
321 AND on_hold = 'Y');
322
323 BEGIN
324
325 OPEN on_hold_cur;
326 FETCH on_hold_cur INTO l_check_cond;
327 CLOSE on_hold_cur;
328
329 IF l_check_cond IS NOT NULL AND l_check_cond = 'A' THEN
330 fnd_message.set_name ('PN', 'PN_APPR_REJ_MSG');
331 app_exception.Raise_Exception;
332 END IF;
333
334 END check_on_hold;
335
336
337 /*============================================================================+
338 -- NAME : check_payment_items_acct_amt
339 -- DESCRIPTION : checks to see IF any of the payment item under schedule_id
340 -- has an accounted amount of NULL OR IF any of the payment item
341 -- which has conversion type 'User' has a rate of NULL
342 -- IF the former, THEN SET error flag to 'Y'
343 -- IF the latter, THEN SET error flag to 'U'
344 -- otherwise SET to 'N'.
345 -- NOTES : Used by PNTAUPMT form (Authorize Payments).
346 -- SCOPE : PUBLIC
347 -- INVOKED FROM :
348 -- ARGUMENTS : IN : l_payment_schedule_id,l_functional_currency
349 -- OUT : NOCOPY l_error_flag => look at description above
350 -- RETURNS :
351 -- REFERENCE :
352 -- HISTORY :
353 -- 25-APR-02 ftanudja o Created
354 -- 03-MAY-02 ftanudja o check IF 'to_currency' type IS user
355 -- (before it was checking 'from_currency')
356 -- 06-MAY-02 ftanudja o removed parameter p_def_conv_type
357 -- 11-MAY-02 ftanudja o cleaned up code
358 -- 24-JUL-02 ftanudja o added check for null conversion types
359 -- 20-DEC-02 psidhu o added check to set p_error_flag if conversion type is
360 -- null and there exists atleast one item for schedule
361 -- with currency_code <> functional currency. Added cursor
362 -- chk_item_exists. Fix for bug#'s 2707128 and 2714333.
363 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
364 -- with _ALL table.
365 -- 01-DEC-05 pikhar o passed org_id in pnp_util_func.check_conversion_type
366 =============================================================================*/
367
368 PROCEDURE check_payment_items_acct_amt (
369 p_payment_schedule_id IN NUMBER,
370 p_functional_currency IN VARCHAR2,
371 p_error_flag OUT NOCOPY VARCHAR2
372 )
373 IS
374 l_dummy VARCHAR2(240);
375 l_date DATE;
376 l_type VARCHAR2(10);
377 l_item_exists VARCHAR2(1):='N';
378
379 CURSOR payment_cursor IS
380 SELECT actual_amount, currency_code, rate, due_date
381 FROM pn_payment_items_all
382 WHERE payment_schedule_id = p_payment_schedule_id
383 AND payment_item_type_lookup_code = 'CASH';
384
385 CURSOR chk_item_exists IS
386 SELECT 'Y'
387 FROM dual
388 WHERE EXISTS (SELECT null
389 FROM pn_payment_items_all
390 WHERE payment_schedule_id = p_payment_schedule_id
391 AND payment_item_type_lookup_code = 'CASH'
392 AND currency_code <> p_functional_currency);
393
394 CURSOR org_cur IS
395 SELECT org_id
396 FROM pn_payment_schedules_all
397 WHERE payment_schedule_id = p_payment_schedule_id;
398
399 l_org_id NUMBER;
400
401 BEGIN
402
403 FOR rec IN org_cur LOOP
404 l_org_id := rec.org_id;
405 END LOOP;
406
407 l_type := pnp_util_func.check_conversion_type(p_functional_currency,l_org_id );
408 p_error_flag := 'N';
409
410 IF l_type IS NULL THEN
411
412 -- fix for bug# 2707128
413 OPEN chk_item_exists;
414 FETCH chk_item_exists into l_item_exists;
415 CLOSE chk_item_exists;
416
417 IF nvl(l_item_exists,'N') = 'Y' THEN
418 p_error_flag := 'Y';
419 RETURN;
420 END IF;
421
422 --
423 END IF;
424
425 FOR payment_record IN payment_cursor LOOP
426
427 IF payment_record.due_date > SYSDATE THEN
428 l_date := SYSDATE;
429 ELSE
430 l_date := payment_record.due_date;
431 END IF;
432
433 IF UPPER(l_type) = 'USER' AND payment_record.rate IS NULL AND
434 (payment_record.currency_code <> p_functional_currency) THEN
435 p_error_flag := 'Y';
436 EXIT;
437 ELSIF UPPER(l_type) <> 'USER' THEN
438
439 l_dummy := pnp_util_func.export_curr_amount(
440 currency_code => payment_record.currency_code,
441 export_currency_code => p_functional_currency,
442 export_date => l_date,
443 conversion_type => l_type,
444 actual_amount => payment_record.actual_amount,
445 p_called_from => 'PNTAUPMT'
446 );
447 IF l_dummy IS NULL THEN
448 p_error_flag := 'Y';
449 EXIT;
450 END IF;
451 END IF;
452 END LOOP;
453
454 END check_payment_items_acct_amt;
455
456 -------------------------------------------------------------------------------
457 -- PROCEDURE : Insert_Row
458 -- INVOKED FROM : insert_row procedure
459 -- PURPOSE : inserts the row
460 -- HISTORY :
461 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
462 -- with _ALL table.
463 -------------------------------------------------------------------------------
464 PROCEDURE Insert_Row (
465 X_CONTEXT IN VARCHAR2,
466 X_ROWID IN OUT NOCOPY VARCHAR2,
467 X_PAYMENT_SCHEDULE_ID IN OUT NOCOPY NUMBER,
468 X_SCHEDULE_DATE IN DATE,
469 X_LEASE_CHANGE_ID IN NUMBER,
470 X_LEASE_ID IN NUMBER,
471 X_APPROVED_BY_USER_ID IN NUMBER,
472 X_TRANSFERRED_BY_USER_ID IN NUMBER,
473 X_PAYMENT_STATUS_LOOKUP_CODE IN VARCHAR2,
474 X_APPROVAL_DATE IN DATE,
475 X_TRANSFER_DATE IN DATE,
476 X_PERIOD_NAME IN VARCHAR2,
477 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
478 X_ATTRIBUTE1 IN VARCHAR2,
479 X_ATTRIBUTE2 IN VARCHAR2,
480 X_ATTRIBUTE3 IN VARCHAR2,
481 X_ATTRIBUTE4 IN VARCHAR2,
482 X_ATTRIBUTE5 IN VARCHAR2,
483 X_ATTRIBUTE6 IN VARCHAR2,
484 X_ATTRIBUTE7 IN VARCHAR2,
485 X_ATTRIBUTE8 IN VARCHAR2,
486 X_ATTRIBUTE9 IN VARCHAR2,
487 X_ATTRIBUTE10 IN VARCHAR2,
488 X_ATTRIBUTE11 IN VARCHAR2,
489 X_ATTRIBUTE12 IN VARCHAR2,
490 X_ATTRIBUTE13 IN VARCHAR2,
491 X_ATTRIBUTE14 IN VARCHAR2,
492 X_ATTRIBUTE15 IN VARCHAR2,
493 X_CREATION_DATE IN DATE,
494 X_CREATED_BY IN NUMBER,
495 X_LAST_UPDATE_DATE IN DATE,
496 X_LAST_UPDATED_BY IN NUMBER,
497 X_LAST_UPDATE_LOGIN IN NUMBER,
498 x_org_id IN NUMBER
499 ) IS
500 CURSOR c IS
501 SELECT ROWID
502 FROM pn_payment_schedules_all
503 WHERE payment_schedule_id = x_payment_schedule_id;
504
505 CURSOR org_cur IS
506 SELECT org_id FROM pn_leases_all WHERE lease_id = x_lease_id;
507
508 l_org_id NUMBER;
509
510 BEGIN
511
512 IF x_payment_schedule_id IS NULL THEN
513
514 SELECT pn_payment_schedules_s.NEXTVAL
515 INTO x_payment_schedule_id
516 FROM DUAL;
517
518 END IF;
519
520 IF x_org_id IS NULL THEN
521 FOR rec IN org_cur LOOP
522 l_org_id := rec.org_id;
523 END LOOP;
524 ELSE
525 l_org_id := x_org_id;
526 END IF;
527
528 INSERT INTO pn_payment_schedules_all
529 (
530 PAYMENT_SCHEDULE_ID,
531 LAST_UPDATE_DATE,
532 LAST_UPDATED_BY,
533 CREATION_DATE,
534 CREATED_BY,
535 LAST_UPDATE_LOGIN,
536 SCHEDULE_DATE,
537 LEASE_CHANGE_ID,
538 LEASE_ID,
539 APPROVED_BY_USER_ID,
540 TRANSFERRED_BY_USER_ID,
541 PAYMENT_STATUS_LOOKUP_CODE,
542 APPROVAL_DATE,
543 TRANSFER_DATE,
544 PERIOD_NAME,
545 ATTRIBUTE_CATEGORY,
546 ATTRIBUTE1,
547 ATTRIBUTE2,
548 ATTRIBUTE3,
549 ATTRIBUTE4,
550 ATTRIBUTE5,
551 ATTRIBUTE6,
552 ATTRIBUTE7,
553 ATTRIBUTE8,
554 ATTRIBUTE9,
555 ATTRIBUTE10,
556 ATTRIBUTE11,
557 ATTRIBUTE12,
558 ATTRIBUTE13,
559 ATTRIBUTE14,
560 ATTRIBUTE15,
561 ON_HOLD,
562 org_id)
563 VALUES (
564 X_PAYMENT_SCHEDULE_ID,
565 X_LAST_UPDATE_DATE,
566 X_LAST_UPDATED_BY,
567 X_CREATION_DATE,
568 X_CREATED_BY,
569 X_LAST_UPDATE_LOGIN,
570 X_SCHEDULE_DATE,
571 X_LEASE_CHANGE_ID,
572 X_LEASE_ID,
573 X_APPROVED_BY_USER_ID,
574 X_TRANSFERRED_BY_USER_ID,
575 X_PAYMENT_STATUS_LOOKUP_CODE,
576 X_APPROVAL_DATE,
577 X_TRANSFER_DATE,
578 X_PERIOD_NAME,
579 X_ATTRIBUTE_CATEGORY,
580 X_ATTRIBUTE1,
581 X_ATTRIBUTE2,
582 X_ATTRIBUTE3,
583 X_ATTRIBUTE4,
584 X_ATTRIBUTE5,
585 X_ATTRIBUTE6,
586 X_ATTRIBUTE7,
587 X_ATTRIBUTE8,
588 X_ATTRIBUTE9,
589 X_ATTRIBUTE10,
590 X_ATTRIBUTE11,
591 X_ATTRIBUTE12,
592 X_ATTRIBUTE13,
593 X_ATTRIBUTE14,
594 X_ATTRIBUTE15,
595 NULL,
596 l_org_id
597 );
598
599 OPEN c;
600 FETCH c INTO X_ROWID;
601 IF (c%notfound) THEN
602 CLOSE c;
603 RAISE no_data_found;
604 END IF;
605 CLOSE c;
606
607 IF X_PAYMENT_STATUS_LOOKUP_CODE = 'APPROVED' THEN
608
609 IF (X_CONTEXT = 'PAY') THEN
610 mark_pmt_items_exportable ( x_payment_status_lookup_code,
611 x_payment_schedule_id,
612 'Y'
613 );
614 ELSE -- REC
615 mark_billing_items_exportable ( x_payment_status_lookup_code,
616 x_payment_schedule_id,
617 'Y'
618 );
619 END IF;
620
621 END IF;
622
623 END Insert_Row;
624
625
626 -------------------------------------------------------------------------------
627 -- PROCEDURE : Lock_Row
628 -- INVOKED FROM : lock_row procedure
629 -- PURPOSE : locks the row
630 -- HISTORY :
631 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
632 -- with _ALL table.
633 -------------------------------------------------------------------------------
634 PROCEDURE Lock_Row (
635 X_PAYMENT_SCHEDULE_ID IN NUMBER,
636 X_APPROVED_BY_USER_ID IN NUMBER,
637 X_TRANSFERRED_BY_USER_ID IN NUMBER,
638 X_PAYMENT_STATUS_LOOKUP_CODE IN VARCHAR2,
639 X_APPROVAL_DATE IN DATE,
640 X_TRANSFER_DATE IN DATE,
641 X_PERIOD_NAME IN VARCHAR2,
642 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
643 X_ATTRIBUTE1 IN VARCHAR2,
644 X_ATTRIBUTE2 IN VARCHAR2,
645 X_ATTRIBUTE3 IN VARCHAR2,
646 X_ATTRIBUTE4 IN VARCHAR2,
647 X_ATTRIBUTE5 IN VARCHAR2,
648 X_ATTRIBUTE6 IN VARCHAR2,
649 X_ATTRIBUTE7 IN VARCHAR2,
650 X_ATTRIBUTE8 IN VARCHAR2,
651 X_ATTRIBUTE9 IN VARCHAR2,
652 X_ATTRIBUTE10 IN VARCHAR2,
653 X_ATTRIBUTE11 IN VARCHAR2,
654 X_ATTRIBUTE12 IN VARCHAR2,
655 X_ATTRIBUTE13 IN VARCHAR2,
656 X_ATTRIBUTE14 IN VARCHAR2,
657 X_ATTRIBUTE15 IN VARCHAR2
658 )
659 IS
660
661 CURSOR c1 IS
662 SELECT *
663 FROM pn_payment_schedules_all
664 WHERE payment_schedule_id = x_payment_schedule_id
665 FOR UPDATE OF payment_schedule_id NOWAIT;
666
667 tlinfo c1%rowtype;
668
669 BEGIN
670
671 OPEN c1;
672 FETCH c1 INTO tlinfo;
673 IF (c1%notfound) THEN
674 CLOSE c1;
675 RETURN;
676 END IF;
677 CLOSE c1;
678
679 IF NOT (tlinfo.PAYMENT_SCHEDULE_ID = X_PAYMENT_SCHEDULE_ID) THEN
680 pn_var_rent_pkg.lock_row_exception('PAYMENT_SCHEDULE_ID',tlinfo.PAYMENT_SCHEDULE_ID);
681 END IF;
682
683 IF NOT ((tlinfo.APPROVED_BY_USER_ID = X_APPROVED_BY_USER_ID)
684 OR ((tlinfo.APPROVED_BY_USER_ID IS NULL) AND (X_APPROVED_BY_USER_ID IS NULL))) THEN
685 pn_var_rent_pkg.lock_row_exception('APPROVED_BY_USER_ID',tlinfo.APPROVED_BY_USER_ID);
686 END IF;
687
688 IF NOT ((tlinfo.TRANSFERRED_BY_USER_ID = X_TRANSFERRED_BY_USER_ID)
689 OR ((tlinfo.TRANSFERRED_BY_USER_ID IS NULL) AND (X_TRANSFERRED_BY_USER_ID IS NULL))) THEN
690 pn_var_rent_pkg.lock_row_exception('TRANSFERRED_BY_USER_ID',tlinfo.TRANSFERRED_BY_USER_ID);
691 END IF;
692
693 IF NOT ((tlinfo.PAYMENT_STATUS_LOOKUP_CODE = X_PAYMENT_STATUS_LOOKUP_CODE)
694 OR ((tlinfo.PAYMENT_STATUS_LOOKUP_CODE IS NULL) AND (X_PAYMENT_STATUS_LOOKUP_CODE IS NULL))) THEN
695 pn_var_rent_pkg.lock_row_exception('PAYMENT_STATUS_LOOKUP_CODE',tlinfo.PAYMENT_STATUS_LOOKUP_CODE);
696 END IF;
697
698 IF NOT ((trunc(tlinfo.APPROVAL_DATE) = trunc(X_APPROVAL_DATE))
699 OR ((trunc(tlinfo.APPROVAL_DATE) IS NULL) AND (trunc(X_APPROVAL_DATE) IS NULL))) THEN
700 pn_var_rent_pkg.lock_row_exception('APPROVAL_DATE',tlinfo.APPROVAL_DATE);
701 END IF;
702
703 IF NOT ((trunc(tlinfo.TRANSFER_DATE) = trunc(X_TRANSFER_DATE))
704 OR ((tlinfo.TRANSFER_DATE IS NULL) AND (X_TRANSFER_DATE IS NULL))) THEN
705 pn_var_rent_pkg.lock_row_exception('TRANSFER_DATE',tlinfo.TRANSFER_DATE);
706 END IF;
707
708 IF NOT ((tlinfo.PERIOD_NAME = X_PERIOD_NAME)
709 OR ((tlinfo.PERIOD_NAME IS NULL) AND (X_PERIOD_NAME IS NULL))) THEN
710 pn_var_rent_pkg.lock_row_exception('PERIOD_NAME',tlinfo.PERIOD_NAME);
711 END IF;
712
713 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
714 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
715 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
716 END IF;
717
718 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
719 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
720 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
721 END IF;
722
723 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
724 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
725 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
726 END IF;
727
728 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
729 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
730 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
731 END IF;
732
733 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
734 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
735 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
736 END IF;
737
738 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
739 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
740 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
741 END IF;
742
743 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
744 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
745 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
746 END IF;
747
748 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
749 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
750 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
751 END IF;
752
753 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
754 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
755 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
756 END IF;
757
758 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
759 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
760 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
761 END IF;
762
763 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
764 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
765 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
766 END IF;
767
768 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
769 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
770 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
771 END IF;
772
773 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
774 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
775 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
776 END IF;
777
778 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
779 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
780 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
781 END IF;
782
783 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
784 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
785 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
786 END IF;
787
788 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
789 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
790 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
791 END IF;
792
793 RETURN;
794 END Lock_Row;
795
796
797 -------------------------------------------------------------------------------
798 -- PROCEDURE : Update_Row
799 -- INVOKED FROM : update_row procedure
800 -- PURPOSE : Updates the row
801 -- HISTORY :
802 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
803 -- with _ALL table.
804 -------------------------------------------------------------------------------
805 PROCEDURE Update_Row (
806 X_CONTEXT IN VARCHAR2,
807 X_PAYMENT_SCHEDULE_ID IN NUMBER,
808 X_SCHEDULE_DATE IN DATE,
809 X_APPROVED_BY_USER_ID IN NUMBER,
810 X_TRANSFERRED_BY_USER_ID IN NUMBER,
811 X_PAYMENT_STATUS_LOOKUP_CODE IN VARCHAR2,
812 X_LEASE_FUNCTIONAL_CURRENCY IN VARCHAR2,
813 X_APPROVAL_DATE IN DATE,
814 X_TRANSFER_DATE IN DATE,
815 X_PERIOD_NAME IN VARCHAR2,
816 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
817 X_ATTRIBUTE1 IN VARCHAR2,
818 X_ATTRIBUTE2 IN VARCHAR2,
819 X_ATTRIBUTE3 IN VARCHAR2,
820 X_ATTRIBUTE4 IN VARCHAR2,
821 X_ATTRIBUTE5 IN VARCHAR2,
822 X_ATTRIBUTE6 IN VARCHAR2,
823 X_ATTRIBUTE7 IN VARCHAR2,
824 X_ATTRIBUTE8 IN VARCHAR2,
825 X_ATTRIBUTE9 IN VARCHAR2,
826 X_ATTRIBUTE10 IN VARCHAR2,
827 X_ATTRIBUTE11 IN VARCHAR2,
828 X_ATTRIBUTE12 IN VARCHAR2,
829 X_ATTRIBUTE13 IN VARCHAR2,
830 X_ATTRIBUTE14 IN VARCHAR2,
831 X_ATTRIBUTE15 IN VARCHAR2,
832 X_LAST_UPDATE_DATE IN DATE,
833 X_LAST_UPDATED_BY IN NUMBER,
834 X_LAST_UPDATE_LOGIN IN NUMBER
835 )
836 IS
837
838 BEGIN
839
840 update_rate(p_pnt_sched_id => x_payment_schedule_id,
841 p_payment_status_lookup_code => x_payment_status_lookup_code,
842 p_lease_functional_currency => x_lease_functional_currency,
843 p_last_updated_by => x_last_updated_by,
844 p_last_update_date => x_last_update_date,
845 p_last_update_login => x_last_update_login);
846
847 update_accounted_amount (X_PAYMENT_SCHEDULE_ID,
848 X_PAYMENT_STATUS_LOOKUP_CODE,
849 X_LEASE_FUNCTIONAL_CURRENCY,
850 X_LAST_UPDATED_BY,
851 X_LAST_UPDATE_DATE,
852 X_LAST_UPDATE_LOGIN );
853
854 UPDATE PN_PAYMENT_SCHEDULES_ALL
855 SET SCHEDULE_DATE = X_SCHEDULE_DATE,
856 APPROVED_BY_USER_ID = X_APPROVED_BY_USER_ID,
857 TRANSFERRED_BY_USER_ID = X_TRANSFERRED_BY_USER_ID,
858 PAYMENT_STATUS_LOOKUP_CODE = X_PAYMENT_STATUS_LOOKUP_CODE,
859 APPROVAL_DATE = X_APPROVAL_DATE,
860 TRANSFER_DATE = X_TRANSFER_DATE,
861 PERIOD_NAME = X_PERIOD_NAME,
862 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
863 ATTRIBUTE1 = X_ATTRIBUTE1,
864 ATTRIBUTE2 = X_ATTRIBUTE2,
865 ATTRIBUTE3 = X_ATTRIBUTE3,
866 ATTRIBUTE4 = X_ATTRIBUTE4,
867 ATTRIBUTE5 = X_ATTRIBUTE5,
868 ATTRIBUTE6 = X_ATTRIBUTE6,
869 ATTRIBUTE7 = X_ATTRIBUTE7,
870 ATTRIBUTE8 = X_ATTRIBUTE8,
871 ATTRIBUTE9 = X_ATTRIBUTE9,
872 ATTRIBUTE10 = X_ATTRIBUTE10,
873 ATTRIBUTE11 = X_ATTRIBUTE11,
874 ATTRIBUTE12 = X_ATTRIBUTE12,
875 ATTRIBUTE13 = X_ATTRIBUTE13,
876 ATTRIBUTE14 = X_ATTRIBUTE14,
877 ATTRIBUTE15 = X_ATTRIBUTE15,
878 ON_HOLD = NULL,
879 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
880 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
881 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
882 WHERE PAYMENT_SCHEDULE_ID = X_PAYMENT_SCHEDULE_ID;
883
884 IF (SQL%NOTFOUND) THEN
885 RAISE NO_DATA_FOUND;
886 END IF;
887
888 IF X_PAYMENT_STATUS_LOOKUP_CODE = 'APPROVED' THEN
889
890 IF (X_CONTEXT = 'PAY') THEN
891 mark_pmt_items_exportable ( x_payment_status_lookup_code,
892 x_payment_schedule_id,
893 'Y'
894 );
895 ELSE -- REC
896 mark_billing_items_exportable ( x_payment_status_lookup_code,
897 x_payment_schedule_id,
898 'Y'
899 );
900 END IF;
901
902 ELSIF X_PAYMENT_STATUS_LOOKUP_CODE = 'DRAFT' THEN
903
904 IF (X_CONTEXT = 'PAY') THEN
905 mark_pmt_items_exportable ( x_payment_status_lookup_code,
906 x_payment_schedule_id,
907 NULL
908 );
909 ELSE -- REC
910 mark_billing_items_exportable ( x_payment_status_lookup_code,
911 x_payment_schedule_id,
912 NULL
913 );
914 END IF;
915
916 END IF;
917
918 END Update_Row;
919
920
921 -------------------------------------------------------------------------------
922 -- PROCEDURE : Delete_Row
923 -- INVOKED FROM : delete_row procedure
924 -- PURPOSE : Deletes the row for a schedule id
925 -- HISTORY :
926 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_schedules
927 -- with _ALL table.
928 -------------------------------------------------------------------------------
929 PROCEDURE Delete_Row ( X_PAYMENT_SCHEDULE_ID IN NUMBER) IS
930 BEGIN
931
932 DELETE FROM pn_payment_schedules_all
933 WHERE payment_schedule_id = x_payment_schedule_id;
934
935 IF (SQL%NOTFOUND) THEN
936 RAISE NO_DATA_FOUND;
937 END IF;
938
939 END Delete_Row;
940
941
942 -------------------------------------------------------------------------------
943 -- PROCEDURE : update_accounted_amount
944 -- INVOKED FROM : update_row procedure
945 -- PURPOSE : UPDATE the accounted amount field
946 -- REFERENCE : BUG ID 2137179
947 -- HISTORY :
948 -- 11-MAR-02 ftanudja o Created
949 -- 25-APR-02 ftanudja o taken into account acct date.
950 -- 03-MAY-02 ftanudja o FOR conv. type 'User', use 'rate'
951 -- 11-MAY-02 ftanudja o cleaned up code
952 -- 07-OCT-02 Ashish o BUG#2590872 update the normalized items
953 -- along with the cash items.
954 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
955 -- with _ALL table.
956 -- 01-DEC-05 pikhar o passed org_id in pnp_util_func.check_conversion_type
957 -------------------------------------------------------------------------------
958
959 PROCEDURE update_accounted_amount (
960 p_pnt_sched_id IN NUMBER,
961 p_payment_status_lookup_code IN VARCHAR2,
962 p_lease_functional_currency IN VARCHAR2,
963 p_last_updated_by IN NUMBER,
964 p_last_update_date IN DATE,
965 p_last_update_login IN NUMBER
966 )
967 IS
968 l_temp NUMBER;
969 l_date DATE;
970 l_type VARCHAR2(10);
971 l_temp1 NUMBER; --BUG#2590872
972
973 V_PAYMENT_ITEM_ID NUMBER ;
974 V_ACCOUNTED_AMOUNT NUMBER ;
975 V_ACTUAL_AMOUNT NUMBER ;
976
977 CURSOR payment_cursor IS
978 SELECT payment_item_id
979 , accounted_amount
980 , actual_amount
981 , currency_code
982 , due_date
983 , rate
984 , payment_term_id
985 FROM pn_payment_items_all
986 WHERE payment_schedule_id = p_pnt_sched_id
987 AND payment_item_type_lookup_code = 'CASH';
988
989 --Added for BUG#2590872
990 CURSOR norm_cursor(l_term_id NUMBER, l_item_id number) IS
991 SELECT pi.payment_item_id
992 , pi.accounted_amount
993 , pi.actual_amount
994 FROM pn_payment_items_all pi,
995 pn_payment_items_all pi1
996 WHERE pi.payment_schedule_id = p_pnt_sched_id
997 AND pi.payment_TERM_id = l_term_id
998 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
999 AND pi1.payment_schedule_id = pi.payment_schedule_id
1000 AND pi1.payment_term_id = pi.payment_term_id
1001 AND pi1.payment_item_type_lookup_code = 'CASH'
1002 AND pi1.payment_item_id = l_item_id ;
1003
1004 CURSOR org_cur IS
1005 SELECT org_id
1006 FROM pn_payment_schedules_all
1007 WHERE payment_schedule_id = p_pnt_sched_id;
1008
1009 l_org_id NUMBER;
1010
1011
1012 BEGIN
1013
1014 FOR rec IN org_cur LOOP
1015 l_org_id := rec.org_id;
1016 END LOOP;
1017
1018 l_type := pnp_util_func.check_conversion_type(p_lease_functional_currency,l_org_id);
1019
1020 FOR payment_item_rec IN payment_cursor LOOP
1021
1022 /*---------ADDED FOR BUG#2590872---------*/
1023 OPEN NORM_cursor(payment_item_rec.payment_term_id,payment_item_rec.payment_item_id );
1024 FETCH NORM_CURSOR INTO V_PAYMENT_ITEM_ID,V_ACCOUNTED_AMOUNT,V_ACTUAL_AMOUNT;
1025 IF NORM_CURSOR%NOTFOUND THEN
1026 NULL;
1027 END IF;
1028 CLOSE NORM_CURSOR;
1029
1030
1031 IF payment_item_rec.due_date > SYSDATE THEN
1032 l_date := SYSDATE;
1033 ELSE
1034 l_date := payment_item_rec.due_date;
1035 END IF;
1036 IF p_payment_status_lookup_code = 'APPROVED' THEN
1037
1038 IF UPPER(l_type) = 'USER' THEN
1039
1040 l_temp := NVL(payment_item_rec.actual_amount,0) * NVL(payment_item_rec.rate,0);
1041 l_temp1 := NVL(V_actual_amount,0) * NVL(PAYMENT_item_rec.rate,0); --ADDED FOR BUG#2590872
1042
1043 ELSE
1044 l_temp := pnp_util_func.export_curr_amount(
1045 currency_code => payment_item_rec.currency_code,
1046 export_currency_code => p_lease_functional_currency,
1047 export_date => l_date,
1048 conversion_type => l_type,
1049 actual_amount => NVL(payment_item_rec.actual_amount,0),
1050 p_called_from => 'NOTPNTAUPMT'
1051 );
1052 --ADDED FOR BUG#2590872
1053 l_temp1 := pnp_util_func.export_curr_amount(
1054 currency_code => payment_item_rec.currency_code,
1055 export_currency_code => p_lease_functional_currency,
1056 export_date => l_date,
1057 conversion_type => l_type,
1058 actual_amount => NVL(V_actual_amount,0),
1059 p_called_from => 'NOTPNTAUPMT'
1060 );
1061
1062 END IF;
1063
1064 ELSIF p_payment_status_lookup_code = 'DRAFT' THEN
1065 l_temp := NULL;
1066 l_temp1 := null; --ADDED FOR BUG#2590872
1067 l_date := NULL;
1068 END IF;
1069
1070 --ADDED FOR BUG#2590872
1071 IF (NVL(v_accounted_amount,0) <> NVL(l_temp1,0)) THEN
1072 UPDATE pn_payment_items_all
1073 SET accounted_amount = l_temp1,
1074 accounted_date = l_date,
1075 RATE = payment_item_rec.RATE,
1076 CURRENCY_CODE = payment_item_rec.currency_code,
1077 last_updated_by = p_last_updated_by,
1078 last_update_date = p_last_update_date,
1079 last_update_login = p_last_update_login
1080 WHERE payment_item_id = v_PAYMENT_item_id;
1081
1082 IF SQL%NOTFOUND THEN
1083 NULL;
1084 END IF;
1085 END IF;
1086 IF (NVL(payment_item_rec.accounted_amount,0) <> NVL(l_temp,0)) THEN
1087 UPDATE pn_payment_items_all
1088 SET accounted_amount = l_temp,
1089 accounted_date = l_date,
1090 last_updated_by = p_last_updated_by,
1091 last_update_date = p_last_update_date,
1092 last_update_login = p_last_update_login
1093 WHERE payment_item_id = payment_item_rec.payment_item_id;
1094 END IF;
1095 END LOOP;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 RAISE;
1100 END update_accounted_amount;
1101
1102
1103 -------------------------------------------------------------------------------
1104 -- PROCEDURE : update_rate
1105 -- INVOKED FROM : update_row procedure
1106 -- PURPOSE : UPDATE the rate field
1107 -- REFERENCE : BUG ID 2730034
1108 -- HISTORY :
1109 -- 07-JAN-03 psidhu o Created
1110 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_payment_items
1111 -- with _ALL table.
1112 -- 01-DEC-05 pikhar o passed org_id in pnp_util_func.check_conversion_type
1113 -------------------------------------------------------------------------------
1114 PROCEDURE update_rate (
1115 p_pnt_sched_id IN NUMBER,
1116 p_payment_status_lookup_code IN VARCHAR2,
1117 p_lease_functional_currency IN VARCHAR2,
1118 p_last_updated_by IN NUMBER,
1119 p_last_update_date IN DATE,
1120 p_last_update_login IN NUMBER
1121 )
1122 IS
1123 l_rate NUMBER;
1124 l_date DATE;
1125 l_type VARCHAR2(30);
1126
1127 CURSOR payment_cursor IS
1128 SELECT payment_item_id, currency_code, due_date, rate
1129 FROM pn_payment_items_all
1130 WHERE payment_schedule_id = p_pnt_sched_id
1131 AND payment_item_type_lookup_code = 'CASH';
1132
1133 CURSOR org_cur IS
1134 SELECT org_id
1135 FROM pn_payment_schedules_all
1136 WHERE payment_schedule_id = p_pnt_sched_id;
1137
1138 l_org_id NUMBER;
1139
1140 BEGIN
1141
1142 FOR rec IN org_cur LOOP
1143 l_org_id := rec.org_id;
1144 END LOOP;
1145
1146 l_type := pnp_util_func.check_conversion_type(p_lease_functional_currency,l_org_id);
1147
1148 FOR payment_item_rec IN payment_cursor LOOP
1149
1150 IF payment_item_rec.due_date > SYSDATE THEN
1151 l_date := SYSDATE;
1152 ELSE
1153 l_date := payment_item_rec.due_date;
1154 END IF;
1155
1156 IF UPPER(l_type) = 'USER' AND
1157 payment_item_rec.currency_code <> p_lease_functional_currency THEN
1158
1159 l_rate := payment_item_rec.rate;
1160
1161 ELSIF p_payment_status_lookup_code = 'APPROVED' THEN
1162 l_rate := gl_currency_api.get_rate_sql(
1163 x_from_currency => payment_item_rec.currency_code,
1164 x_to_currency => p_lease_functional_currency,
1165 x_conversion_date => l_date,
1166 x_conversion_type => l_type);
1167
1168 /* gl_currency_api.get_rate_sql returns a -1 is no rate is found
1169 or -2 if the currency is invalid.*/
1170 if l_rate in (-1,-2) then
1171 l_rate := null;
1172 end if;
1173 ELSIF p_payment_status_lookup_code = 'DRAFT' THEN
1174 l_rate := NULL;
1175 END IF;
1176
1177 IF (NVL(payment_item_rec.rate,0) <> NVL(l_rate,0)) THEN
1178 UPDATE pn_payment_items_all
1179 SET rate = l_rate,
1180 last_updated_by = p_last_updated_by,
1181 last_update_date = p_last_update_date,
1182 last_update_login = p_last_update_login
1183 WHERE payment_item_id = payment_item_rec.payment_item_id;
1184 END IF;
1185 END LOOP;
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 RAISE;
1190 END update_rate;
1191
1192
1193 END pnt_payment_schedules_pkg;
[Home] [Help]