[Home] [Help]
PACKAGE BODY: APPS.AP_PPA_PKG
Source
1 PACKAGE BODY AP_PPA_PKG AS
2 /* $Header: aprddtsb.pls 120.9.12010000.5 2009/01/09 12:27:32 amitmukh ship $ */
3
4 --==========================================================================
5 ---------------------------------------------------------------------------
6 -- Private (Non Public) Procedure Specifications
7 ---------------------------------------------------------------------------
8 --==========================================================================
9
10 PROCEDURE Log(p_msg IN VARCHAR2,
11 p_loc IN VARCHAR2);
12
13 PROCEDURE Calc_Pay_Sched(p_invoice_id IN NUMBER,
14 p_curr_ps_pay_num IN NUMBER,
15 p_matched IN BOOLEAN,
16 p_start_date IN DATE,
17 p_term_id IN NUMBER,
18 p_term_name IN VARCHAR2,
19 p_system_user IN NUMBER,
20 p_payment_cross_rate IN NUMBER,
21 p_payment_priority IN NUMBER,
22 p_hold_flag IN VARCHAR2,
23 p_payment_status_flag IN VARCHAR2,
24 p_batch_id IN NUMBER,
25 p_creation_date IN DATE,
26 p_created_by IN NUMBER,
27 p_last_update_login IN NUMBER,
28 p_payment_method_code IN VARCHAR2, --4552701
29 p_external_bank_account_id IN NUMBER,
30 p_calling_sequence IN VARCHAR2,
31 p_sub_total IN OUT NOCOPY NUMBER,
32 p_sub_total_inv IN OUT NOCOPY NUMBER);
33
34 PROCEDURE Create_Pay_Scheds(p_invoice_id IN NUMBER,
35 p_curr_ps_pay_num IN NUMBER,
36 p_system_user IN NUMBER,
37 p_start_date IN DATE,
38 p_total_gross_amount IN NUMBER,
39 p_total_inv_curr_gross_amount
40 IN NUMBER, -- R11: Xcurr
41 p_amount_applicable_to_disc IN NUMBER,
42 p_payment_cross_rate IN NUMBER,
43 p_term_id IN NUMBER,
44 p_last_term_ps_pay_num IN NUMBER,
45 p_payment_priority IN NUMBER,
46 p_hold_flag IN VARCHAR2,
47 p_payment_status_flag IN VARCHAR2,
48 p_batch_id IN NUMBER,
49 p_creation_date IN DATE,
50 p_created_by IN NUMBER,
51 p_last_update_login IN NUMBER,
52 p_payment_method_code IN VARCHAR2, --4552701
53 p_external_bank_account_id IN NUMBER,
54 p_percent_remain_vs_gross IN NUMBER,
55 p_calling_sequence IN VARCHAR2);
56
57
58 PROCEDURE Insert_Pay_Sched(p_invoice_id IN NUMBER,
59 p_ps_pay_num IN NUMBER,
60 p_system_user IN NUMBER,
61 p_payment_cross_rate IN NUMBER,
62 p_due_date IN DATE,
63 p_1st_discount_date IN DATE,
64 p_2nd_discount_date IN DATE,
65 p_3rd_discount_date IN DATE,
66 p_gross_amount IN NUMBER,
67 p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
68 p_1st_disc_amt_available IN NUMBER,
69 p_2nd_disc_amt_available IN NUMBER,
70 p_3rd_disc_amt_available IN NUMBER,
71 p_payment_priority IN NUMBER,
72 p_hold_flag IN VARCHAR2,
73 p_payment_status_flag IN VARCHAR2,
74 p_batch_id IN NUMBER,
75 p_creation_date IN DATE,
76 p_created_by IN NUMBER,
77 p_last_update_login IN NUMBER,
78 p_payment_method_code IN VARCHAR2, --4552701
79 p_external_bank_account_id IN NUMBER,
80 p_percent_remain_vs_gross IN NUMBER,
81 p_calling_sequence IN VARCHAR2);
82
83 PROCEDURE Calc_PS_Dates_Percents_Amts(p_invoice_id IN NUMBER,
84 p_term_id IN NUMBER,
85 p_ps_pay_num IN NUMBER,
86 p_start_date IN DATE,
87 p_total_amount IN NUMBER,
88 p_total_pay_curr_amount IN NUMBER, -- R11: Xcurr
89 p_amount_applicable_to_disc IN NUMBER,
90 p_payment_cross_rate IN NUMBER,
91 p_ppa_due_date IN OUT NOCOPY DATE,
92 p_discount_date IN OUT NOCOPY DATE,
93 p_second_discount_date IN OUT NOCOPY DATE,
94 p_third_discount_date IN OUT NOCOPY DATE,
95 p_discount_amt_available IN OUT NOCOPY NUMBER,
96 p_secnd_disc_amt_available IN OUT NOCOPY NUMBER,
97 p_third_disc_amt_available IN OUT NOCOPY NUMBER,
98 p_gross_amount IN OUT NOCOPY NUMBER,
99 p_inv_curr_gross_amount IN OUT NOCOPY NUMBER, -- R11: Xcurr
100 p_discount_percent_1 IN OUT NOCOPY NUMBER,
101 p_discount_percent_2 IN OUT NOCOPY NUMBER,
102 p_discount_percent_3 IN OUT NOCOPY NUMBER,
103 p_due_amount IN OUT NOCOPY NUMBER,
104 p_due_percent IN OUT NOCOPY NUMBER,
105 p_calling_sequence IN VARCHAR2);
106
107 PROCEDURE Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id IN NUMBER,
108 p_payment_num IN NUMBER,
109 p_calling_sequence IN VARCHAR2);
110
111 PROCEDURE Update_Pay_Sched(p_invoice_id IN NUMBER,
112 p_payment_num IN NUMBER,
113 p_ppa_due_date IN DATE,
114 p_1st_disc_amt_available IN NUMBER,
115 p_2nd_disc_amt_available IN NUMBER,
116 p_3rd_disc_amt_available IN NUMBER,
117 p_1st_discount_date IN DATE,
118 p_2nd_discount_date IN DATE,
119 p_3rd_discount_date IN DATE,
120 p_system_user IN NUMBER,
121 p_gross_amount IN NUMBER,
122 p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
123 p_percent_remain_vs_gross IN NUMBER,
124 p_calling_sequence IN VARCHAR2);
125
126
127 PROCEDURE Get_PaySched_Info(p_invoice_id IN NUMBER,
128 p_term_id IN NUMBER,
129 p_ps_total_gross_amount IN OUT NOCOPY NUMBER,
130 p_ps_total_inv_curr_gross_amt IN OUT NOCOPY NUMBER, -- R11: Xcurr
131 p_last_inv_ps_pay_num IN OUT NOCOPY NUMBER,
132 p_last_term_ps_pay_num IN OUT NOCOPY NUMBER,
133 p_percent_remain_vs_gross IN OUT NOCOPY NUMBER,
134 p_calling_sequence IN VARCHAR2);
135
136 PROCEDURE Get_Invoice_Info(p_invoice_id IN NUMBER,
137 p_invoice_amount OUT NOCOPY NUMBER,
138 p_pay_curr_invoice_amount OUT NOCOPY NUMBER, -- R11: Xcurr
139 p_amount_applicable_to_disc OUT NOCOPY NUMBER,
140 p_calling_sequence IN VARCHAR2);
141
142 PROCEDURE Get_PO_Terms_Info(p_invoice_id IN NUMBER,
143 p_po_term_id IN OUT NOCOPY NUMBER,
144 p_po_rank IN OUT NOCOPY NUMBER,
145 p_po_terms_name IN OUT NOCOPY VARCHAR2,
146 p_calling_sequence IN VARCHAR2);
147
148 PROCEDURE Get_Inv_Start_Date(p_invoice_id IN NUMBER,
149 p_inv_date IN DATE,
150 p_receipt_acc_days IN NUMBER,
151 p_start_date IN OUT NOCOPY DATE,
152 p_calling_sequence IN VARCHAR2);
153
154 PROCEDURE Get_Matched_Start_Date(p_invoice_id IN NUMBER,
155 p_inv_date IN DATE,
156 p_receipt_acc_days IN NUMBER,
157 p_terms_date IN DATE,
158 p_goods_received_date IN DATE,
159 p_start_date IN OUT NOCOPY DATE,
160 p_calling_sequence IN VARCHAR2);
161
162 --2189242
163 Procedure Adj_Pay_Sched_For_Round (p_invoice_id IN NUMBER,
164 p_calling_sequence in VARCHAR2);
165
166
167
168 --==========================================================================
169 ---------------------------------------------------------------------------
170 -- Procedure Definitions
171 ---------------------------------------------------------------------------
172 --==========================================================================
173
174 --============================================================================
175 -- DUE_DATE_SWEEPER: Procedure that calculates the due_date of an
176 -- invoice to enforce the prompt payment act. For a
177 -- matched invoice, the po and invoice terms are compared bo determine which
178 -- term is better (rank is lower), and calculates the invoice payment schedule
179 -- with the better term.
180 --
181 -- Parameters:
182 --
183 -- p_invoice_id: Invoice Id
184 --
185 -- p_matched: Boolean indicating whether the invoice id matched.
186 --
187 -- p_system_user: Approval Program User Id
188 --
189 -- p_receipt_acc_days: System Recipt Acceptance Days
190 --
191 -- p_calling_sequence: Debugging string to indicate path of module calls to be
192 -- printed out NOCOPY upon error.
193 --
194 -- Procedure Flow:
195 -- ---------------
196 --
197 -- FOR each payment schedule line of the upaid invoice without any holds
198 -- IF (first_record) THEN
199 -- IF (matched) THEN
200 -- Get_Matched_Start_Date and Get_PO_Terms_Info
201 -- ELSE
202 -- Get_Invoice_Start_Date
203 -- END IF
204 -- END IF
205 -- IF (invoice not matched OR (po_rank < invoice_rank)) THEN
206 -- Calcuulate payment Schedules
207 -- END IF;
208 -- END FOR;
209 --============================================================================
210
211 PROCEDURE Due_Date_Sweeper(p_invoice_id IN NUMBER,
212 p_matched IN BOOLEAN,
213 p_system_user IN NUMBER,
214 p_receipt_acc_days IN NUMBER,
215 p_calling_sequence IN VARCHAR2) IS
216 --bug5058982
217 /*Made the correated subquery mergable by including the p_invoice_id in the
218 * sub-query*/
219 CURSOR Sweep_Cur IS
220 SELECT DISTINCT ps.invoice_id,
221 ps.payment_num,
222 i.invoice_date,
223 t.term_id,
224 t.name,
225 nvl(t.rank,999),
226 nvl(ps.payment_cross_rate,1),
227 i.invoice_num,
228 nvl(ps.payment_priority,0),
229 ps.hold_flag,
230 ps.payment_status_flag,
231 nvl(ps.batch_id,0),
232 ps.creation_date,
233 nvl(ps.created_by,0),
234 nvl(ps.last_update_login,0),
235 ps.payment_method_code, --4552701
236 ps.external_bank_account_id,
237 nvl(i.payment_currency_code,i.invoice_currency_code),
238 i.terms_date,
239 i.goods_received_date
240 FROM ap_terms t,
241 ap_invoice_distributions d,
242 ap_invoices i,
243 ap_payment_schedules ps
244 WHERE i.payment_status_flag = 'N'
245 AND ps.amount_remaining > 0
246 AND ps.invoice_id = i.invoice_id
247 AND i.invoice_id = d.invoice_id
248 AND i.terms_id = t.term_id
249 AND i.invoice_type_lookup_code <> 'INTEREST'
250 AND (i.invoice_id = p_invoice_id
251 AND d.invoice_id = p_invoice_id
252 AND NOT EXISTS
253 (SELECT h.invoice_id
254 FROM ap_holds h, ap_hold_codes c
255 WHERE h.hold_lookup_code = c.hold_lookup_code
256 AND h.release_lookup_code is null
257 AND c.user_releaseable_flag = 'N'
258 AND h.invoice_id=p_invoice_id))
259 ORDER BY ps.invoice_id, ps.payment_num;
260
261 l_invoice_id NUMBER(15);
262 l_invoice_num VARCHAR2(50);
263 l_term_id NUMBER(15);
264 l_term_name VARCHAR2(50);
265 -- bug 6722079
266 l_po_term_id NUMBER(15);
267 l_po_term_name VARCHAR2(50);
268 l_inv_term_id NUMBER(15);
269 l_inv_term_name VARCHAR2(50);
270 -- bug 6722079
271 l_inv_date DATE;
272 l_inv_rank NUMBER(15);
273 l_payment_cross_rate NUMBER;
274 l_payment_priority NUMBER(2);
275 l_hold_flag VARCHAR2(1);
276 l_payment_status_flag VARCHAR2(25);
277 l_batch_id NUMBER(15);
278 l_creation_date DATE;
279 l_created_by NUMBER(15);
280 l_last_update_login NUMBER(15);
281 l_payment_method_code VARCHAR2(30); --4552701
282 l_external_bank_account_id NUMBER(15);
283 l_payment_currency_code VARCHAR2(15);
284 l_start_date DATE;
285 l_po_rank NUMBER(15);
286 l_terms_name VARCHAR2(50);
287 l_payment_num NUMBER(15);
288 l_first_record BOOLEAN;
289 l_debug_loc VARCHAR2(30) := 'Due_Date_Sweeper';
290 l_curr_calling_sequence VARCHAR2(2000);
291 l_debug_info VARCHAR2(100);
292 l_sub_total NUMBER;
293 l_sub_total_inv NUMBER;
294 l_terms_date DATE;
295 l_goods_received_date DATE;
296 l_date_diff VARCHAR2(1); -- 4574614 (4271303)
297
298 BEGIN
299
300 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
301
302 -- Update the calling sequence --
303
304 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
305
306 l_first_record := TRUE;
307 l_sub_total := 0;
308 l_sub_total_inv :=0;
309 ---------------------------------
310 l_debug_info := 'Open Sweep_Cur';
311 ---------------------------------
312 --Log(l_debug_info, l_debug_loc);
313 -------------------------------
314
315 OPEN Sweep_Cur;
316
317 LOOP
318
319 ----------------------------------
320 l_debug_info := 'Fetch Sweep_Cur';
321 ---------------------------------
322 --Log(l_debug_info, l_debug_loc);
323 -------------------------------
324
325 FETCH Sweep_Cur INTO l_invoice_id,
326 l_payment_num,
327 l_inv_date,
328 l_inv_term_id, -- 6722079
329 l_inv_term_name, -- 6722079
330 l_inv_rank,
331 l_payment_cross_rate,
332 l_invoice_num,
333 l_payment_priority,
334 l_hold_flag,
335 l_payment_status_flag,
336 l_batch_id,
337 l_creation_date,
338 l_created_by,
339 l_last_update_login,
340 l_payment_method_code, --4552701
341 l_external_bank_account_id,
342 l_payment_currency_code,
343 l_terms_date,
344 l_goods_received_date;
345
346 EXIT WHEN Sweep_Cur%NOTFOUND;
347
348 IF (l_first_record) THEN
349
350 l_first_record := FALSE;
351
352 IF (p_matched) THEN -- Matched --
353
354 -----------------------------------------
355 l_debug_info := 'Get Matched Start Date';
356 -----------------------------------------
357 --Log(l_debug_info, l_debug_loc);
358 -------------------------------
359 -- Bug Fix: 1125440 - This call is moved after Get_PO_Terms_Info
360 /* Get_Matched_Start_Date(l_invoice_id,
361 l_inv_date,
362 p_receipt_acc_days,
363 l_terms_date,
364 l_goods_received_date,
365 l_start_date,
366 l_curr_calling_sequence); */
367
368 ------------------------------------
369 l_debug_info := 'Get PO Terms Info';
370 ------------------------------------
371 --Log(l_debug_info, l_debug_loc);
372 -------------------------------
373
374 Get_PO_Terms_Info(l_invoice_id,
375 l_po_term_id, -- 6722079
376 l_po_rank,
377 l_po_term_name, -- 6722079
378 l_curr_calling_sequence);
379
380 -- 1125440
381
382 -- bug 6722079 -- assigning the better ranked of the PO payment term
383 -- and Invoice payment term to l_term_id and l_term_name
384
385 IF (l_po_rank IS NULL OR
386 NVL(l_po_rank,999) >= l_inv_rank)
387 THEN
388 l_term_id := l_inv_term_id;
389 l_term_name := l_inv_term_name;
390 ELSE
391 l_term_id := l_po_term_id;
392 l_term_name := l_po_term_name;
393 END IF;
394
395 -- 6722079
396 /*
397 4574614 (4271303) fbreslin: Check if any of the relevent dates has different values
398 */
399 IF l_goods_received_date IS NULL
400 THEN IF l_inv_date <> l_terms_date
401 THEN l_date_diff := 'Y';
402 ELSE l_date_diff := 'N';
403 END IF;
404 ELSE IF l_inv_date <> l_terms_date OR
405 l_inv_date <> l_goods_received_date OR
406 l_terms_date <> l_goods_received_date
407 THEN l_date_diff := 'Y';
408 ELSE l_date_diff := 'N';
409 END IF;
410 END IF;
411 /*
412 4574614 (4271303) fbreslin: If any of the relevent dates has different values, recalculate due date.
413 */
414
415 -- 6792448 - Commented the following IF condition. Get_Matched_Start_Date
416 -- should always be called whenever invoice is PO matched.
417
418 -- IF l_po_rank < l_inv_rank OR l_date_diff = 'Y' THEN
419
420
421 Get_Matched_Start_Date(l_invoice_id,
422 l_inv_date,
423 p_receipt_acc_days,
424 l_terms_date,
425 l_goods_received_date,
426 l_start_date,
427 l_curr_calling_sequence);
428 -- END IF; -- 6792448
429 ---------------
430 ELSE -- unmatched --
431 ---------------
432
433 ------------------------------------
434 l_debug_info := 'Get_Inv_Start_Date';
435 ------------------------------------
436 --Log(l_debug_info, l_debug_loc);
437 -------------------------------
438
439 Get_Inv_Start_Date(l_invoice_id,
440 l_inv_date,
441 p_receipt_acc_days,
442 l_start_date,
443 l_curr_calling_sequence);
444
445 l_term_id := l_inv_term_id; --7699697
446 l_term_name := l_inv_term_name; --7699697
447
448 END IF; -- unmatched --
449
450 END IF; -- l_first_record --
451
452 -- 6792448 - Commented out this IF condition as this gives rise to
453 -- inconsistent behavior. Hence changed the logic so that recalculate
454 -- always happpens.
455
456 -- IF ((NOT p_matched) OR (l_po_rank < l_inv_rank)) THEN
457
458 ----------------------------------
459 l_debug_info := 'Calc_Pay_Scheds';
460 ----------------------------------
461 --Log(l_debug_info, l_debug_loc);
462 -------------------------------
463
464 Calc_Pay_Sched(l_invoice_id,
465 l_payment_num,
466 p_matched,
467 l_start_date,
468 l_term_id,
469 l_term_name,
470 p_system_user,
471 l_payment_cross_rate,
472 l_payment_priority,
473 l_hold_flag,
474 l_payment_status_flag,
475 l_batch_id,
476 l_creation_date,
477 l_created_by,
478 l_last_update_login,
479 l_payment_method_code, --4552701
480 l_external_bank_account_id,
481 l_curr_calling_sequence,
482 l_sub_total,
483 l_sub_total_inv);
484
485 -- END IF; -- 6792448
486
487 END LOOP;
488
489 ----------------------------------
490 l_debug_info := 'Close Sweep_Cur';
491 ---------------------------------
492 --Log(l_debug_info, l_debug_loc);
493 -------------------------------
494
495 CLOSE Sweep_Cur;
496
497 --2189242
498 Adj_Pay_Sched_For_Round (p_invoice_id ,
499 p_calling_sequence);
500
501
502 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 IF (SQLCODE <> -20001) THEN
507 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
508 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
509 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
510 FND_MESSAGE.SET_TOKEN('PARAMETERS',
511 'Invoice_id = '|| to_char(p_invoice_id)
512 || 'System User = '|| to_char(p_system_user)
513 || 'Receipt Acc Days = '|| to_char(p_receipt_acc_days));
514 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
515 END IF;
516 APP_EXCEPTION.RAISE_EXCEPTION;
517 END Due_Date_Sweeper;
518
519
520 --============================================================================
521 -- GET_MATCHED_START_DATE: Procedure to return the start_date for a
522 -- matched invoice.
523 --============================================================================
524
525 PROCEDURE Get_Matched_Start_Date(p_invoice_id IN NUMBER,
526 p_inv_date IN DATE,
527 p_receipt_acc_days IN NUMBER,
528 p_terms_Date IN DATE,
529 p_goods_received_date IN DATE,
530 p_start_date IN OUT NOCOPY DATE,
531 p_calling_sequence IN VARCHAR2) IS
532 l_debug_loc VARCHAR2(30) := 'Get_Matched_Start_Date';
533 l_curr_calling_sequence VARCHAR2(2000);
534 l_debug_info VARCHAR2(100);
535 l_transaction_date DATE;
536 l_goods_received_date DATE;
537 BEGIN
538
539 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
540
541 -- Update the calling sequence --
542
543 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
544
545 p_start_date := null;
546
547 -------------------------------------------------
548 -- SQL statement to retrieve the start date --
549 -- greatest[ invoice_date, max(accept_date) ] --
550 -- where max(accept_date) = --
551 -- goods_received_date + receipt_acc_days--
552 --------------------------------------------------
553 /*
554 SELECT MAX(greatest(to_date(p_inv_date),
555 i.terms_date,
556 least(to_date(nvl(i.goods_received_date +
557 nvl(p_receipt_acc_days,0),
558 nvl(rt.transaction_date,p_inv_date))),
559 to_date(nvl(rt.transaction_date,
560 nvl(i.goods_received_date +
561 nvl(p_receipt_acc_days,0),p_inv_date))))))
562 INTO p_start_date
563 FROM ap_invoices i, rcv_transactions rt, rcv_shipment_lines rsl,
564 ap_invoice_distributions ad, po_distributions_ap_v d
565 WHERE i.invoice_id = p_invoice_id
566 AND i.invoice_id = ad.invoice_id(+)
567 AND ad.po_distribution_id = d.po_distribution_id
568 AND d.po_header_id = rsl.po_header_id(+)
569 AND rsl.shipment_line_id = rt.shipment_line_id(+)
570 AND decode(rt.transaction_type(+), 'ACCEPT', '1',
571 'REJECT', '1', '0') = '1';
572 */
573
574 -- bug1655225. Added the condition 'RECEIVE, 'DELIVER'
575 -- because w/o this condition a 3 way match would fail.
576 -- Also, added if p_goods_received_date is NULL then
577 -- substitute it with l_transaction_date. Chenged the
578 -- least to greatest in the select that calculates p_start_date.
579
580 Begin
581 SELECT MIN(rt.transaction_date)
582 INTO l_transaction_date
583 FROM rcv_transactions rt, rcv_shipment_lines rsl,
584 ap_invoice_distributions ad, po_distributions d
585 WHERE ad.invoice_id = p_invoice_id
586 AND ad.po_distribution_id = d.po_distribution_id
587 AND d.po_header_id = rsl.po_header_id
588 AND rsl.shipment_line_id = rt.shipment_line_id
589 AND rt.transaction_type IN ('ACCEPT','REJECT','RECEIVE','DELIVER');
590
591 Exception
592 WHEN NO_DATA_FOUND THEN
593 null;
594 End;
595
596 l_goods_received_date := NVL( p_goods_received_date, l_transaction_date );
597
598 SELECT max(greatest(p_inv_date,
599 p_terms_date,
600 greatest(nvl(l_goods_received_date + nvl(p_receipt_acc_days,0),
601 nvl(l_transaction_date,p_inv_date)),
602 nvl(l_transaction_date,
603 nvl(l_goods_received_date + nvl(p_receipt_acc_days,0),p_inv_date)))))
604 INTO p_start_date
605 FROM dual;
606
607 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
608
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611 l_debug_info := 'Start date should not be null';
612 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
613 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
614 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
615 FND_MESSAGE.SET_TOKEN('PARAMETERS',
616 'Invoice_id = '|| to_char(p_invoice_id));
617 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
618 APP_EXCEPTION.RAISE_EXCEPTION;
619 WHEN OTHERS THEN
620 IF (SQLCODE <> -20001) THEN
621 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
622 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
623 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
624 FND_MESSAGE.SET_TOKEN('PARAMETERS',
625 'Invoice_id = '|| to_char(p_invoice_id));
626 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
627 END IF;
628 APP_EXCEPTION.RAISE_EXCEPTION;
629 END Get_Matched_Start_Date;
630
631 --============================================================================
632 -- GET_INV_START_DATE: Procedure to retrieve the start_date for an unmatched
633 -- invoice.
634 --============================================================================
635 PROCEDURE Get_Inv_Start_Date(p_invoice_id IN NUMBER,
636 p_inv_date IN DATE,
637 p_receipt_acc_days IN NUMBER,
638 p_start_date IN OUT NOCOPY DATE,
639 p_calling_sequence IN VARCHAR2) IS
640 l_debug_loc VARCHAR2(30) := 'Get_Inv_Start_Date';
641 l_curr_calling_sequence VARCHAR2(2000);
642 l_debug_info VARCHAR2(100);
643 BEGIN
644
645 -- Update the calling sequence --
646
647 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
648
649
650 SELECT
651 greatest(p_inv_date,
652 i.terms_date,
653 nvl(i.goods_received_date +
654 nvl(p_receipt_acc_days,0), p_inv_date))
655 INTO p_start_date
656 FROM ap_invoices i
657 WHERE i.invoice_id = p_invoice_id;
658
659 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF (SQLCODE <> -20001) THEN
664 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
665 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
666 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
667 FND_MESSAGE.SET_TOKEN('PARAMETERS',
668 'Invoice_id = '|| to_char(p_invoice_id));
669 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
670 END IF;
671 APP_EXCEPTION.RAISE_EXCEPTION;
672 END Get_Inv_Start_Date;
673
674
675 --============================================================================
676 -- GET_PO_TERMS_INFO: Procedure to retrieve the po_terms_id, po_rank,
677 -- po_terms_name, given a matched invoice_id.
678 --============================================================================
679 PROCEDURE Get_PO_Terms_Info(p_invoice_id IN NUMBER,
680 p_po_term_id IN OUT NOCOPY NUMBER,
681 p_po_rank IN OUT NOCOPY NUMBER,
682 p_po_terms_name IN OUT NOCOPY VARCHAR2,
683 p_calling_sequence IN VARCHAR2) IS
684 l_debug_loc VARCHAR2(30) := 'Get_PO_Terms_Info';
685 l_curr_calling_sequence VARCHAR2(2000);
686 l_debug_info VARCHAR2(100);
687 BEGIN
688
689 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
690
691 -- Update the calling sequence --
692
693 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
694
695
696 -- Retrieve po terms rank, and terms id and terms name
697
698 SELECT min(h.terms_id)
699 INTO p_po_term_id
700 FROM po_headers h,po_distributions_ap_v d,
701 ap_invoice_distributions id
702 WHERE id.invoice_id = p_invoice_id
703 AND id.po_distribution_id = d.po_distribution_id
704 AND d.po_header_id = h.po_header_id;
705
706
707 SELECT nvl(t.rank, 999), t.name
708 INTO p_po_rank, p_po_terms_name
709 FROM ap_terms t
710 WHERE t.term_id = p_po_term_id;
711
712 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
713
714 EXCEPTION
715 --Bug: 735019: PO header could have a null term id.
716 WHEN NO_DATA_FOUND THEN
717 NULL;
718 WHEN OTHERS THEN
719 IF (SQLCODE <> -20001) THEN
720 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
721 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
722 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
723 FND_MESSAGE.SET_TOKEN('PARAMETERS',
724 'Invoice_id = '|| to_char(p_invoice_id));
725 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
726 END IF;
727 APP_EXCEPTION.RAISE_EXCEPTION;
728 END Get_PO_Terms_Info;
729
730 --============================================================================
731 -- CALC_PAY_SCHED: Procedure to Recalcute a payment schecdule for an
732 -- invoice.
733 --============================================================================
734 PROCEDURE Calc_Pay_Sched(p_invoice_id IN NUMBER,
735 p_curr_ps_pay_num IN NUMBER,
736 p_matched IN BOOLEAN,
737 p_start_date IN DATE,
738 p_term_id IN NUMBER,
739 p_term_name IN VARCHAR2,
740 p_system_user IN NUMBER,
741 p_payment_cross_rate IN NUMBER,
742 p_payment_priority IN NUMBER,
743 p_hold_flag IN VARCHAR2,
744 p_payment_status_flag IN VARCHAR2,
745 p_batch_id IN NUMBER,
746 p_creation_date IN DATE,
747 p_created_by IN NUMBER,
748 p_last_update_login IN NUMBER,
749 p_payment_method_code IN VARCHAR2, --4552701
750 p_external_bank_account_id IN NUMBER,
751 p_calling_sequence IN VARCHAR2,
752 p_sub_total IN OUT NOCOPY NUMBER,
753 p_sub_total_inv IN OUT NOCOPY NUMBER) IS
754 l_total_gross_amount NUMBER;
755 l_total_inv_curr_gross_amount NUMBER; -- R11: Xcurr
756 l_invoice_amount NUMBER;
757 l_pay_curr_invoice_amount NUMBER; -- R11: Xcurr
758 l_amount_applicable_to_disc NUMBER;
759 l_last_inv_ps_pay_num NUMBER;
760 l_last_term_ps_pay_num NUMBER;
761 l_ppa_due_date DATE;
762 l_1st_discount_date DATE;
763 l_2nd_discount_date DATE;
764 l_3rd_discount_date DATE;
765 l_1st_disc_amt_available NUMBER;
766 l_2nd_disc_amt_available NUMBER;
767 l_3rd_disc_amt_available NUMBER;
768 l_gross_amount NUMBER;
769 l_inv_curr_gross_amount NUMBER; -- R11: Xcurr
770 l_disc_percent_1 NUMBER;
771 l_disc_percent_2 NUMBER;
772 l_disc_percent_3 NUMBER;
773 l_due_amount NUMBER;
774 l_due_percent NUMBER;
775 l_total NUMBER;
776 l_old_remaining_amount NUMBER;
777 l_inv_old_remaining_amount NUMBER;
778 l_percent_remain_vs_gross NUMBER;
779 l_debug_loc VARCHAR2(30) := 'Calc_Pay_Scheds';
780 l_curr_calling_sequence VARCHAR2(2000);
781 l_debug_info VARCHAR2(100);
782 BEGIN
783
784 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
785
786 -- Update the calling sequence --
787
788 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
789
790
791 --------------------------------------------
792 l_debug_info := 'Get Payment Schedule Info';
793 --------------------------------------------
794 --Log(l_debug_info, l_debug_loc);
795 -------------------------------
796
797 Get_PaySched_Info(p_invoice_id,
798 p_term_id,
799 l_total_gross_amount,
800 l_total_inv_curr_gross_Amount, -- R11: Xcurr
801 l_last_inv_ps_pay_num,
802 l_last_term_ps_pay_num,
803 l_percent_remain_vs_gross,
804 l_curr_calling_sequence);
805
806 --------------------------------------------
807 l_debug_info := 'Get Invoice Info';
808 --------------------------------------------
809 --Log(l_debug_info, l_debug_loc);
810 -------------------------------
811
812 Get_Invoice_Info(p_invoice_id,
813 l_invoice_amount,
814 l_pay_curr_invoice_amount,
815 l_amount_applicable_to_disc,
816 l_curr_calling_sequence);
817
818
819 --------------------------------------------------------------
820 l_debug_info := 'Calc Payment Schedule Dates, Percents, Amts';
821 -- given invoice_id, term_id and payment num --
822 --------------------------------------------------------------
823 --Log(l_debug_info, l_debug_loc);
824 -------------------------------
825
826 Calc_PS_Dates_Percents_Amts(p_invoice_id,
827 p_term_id,
828 p_curr_ps_pay_num,
829 p_start_date,
830 l_invoice_amount,
831 l_pay_curr_invoice_amount, -- R11: Xcurr
832 l_amount_applicable_to_disc,
833 p_payment_cross_rate,
834 l_ppa_due_date,
835 l_1st_discount_date,
836 l_2nd_discount_date,
837 l_3rd_discount_date,
838 l_1st_disc_amt_available,
839 l_2nd_disc_amt_available,
840 l_3rd_disc_amt_available,
841 l_gross_amount,
842 l_inv_curr_gross_amount, -- R11: Xcurr
843 l_disc_percent_1,
844 l_disc_percent_2,
845 l_disc_percent_3,
846 l_due_amount,
847 l_due_percent,
848 l_curr_calling_sequence);
849
850
851 l_old_remaining_amount := l_total_gross_amount - p_sub_total;
852 l_inv_old_remaining_amount := l_total_inv_curr_gross_Amount - p_sub_total_inv;
853 p_sub_total := p_sub_total + l_gross_amount;
854 p_sub_total_inv := p_sub_total_inv + l_inv_curr_gross_amount; -- R11: Xcurr
855
856 IF (((l_due_amount = 0) OR (p_sub_total >= l_total_gross_amount) OR
857 (p_sub_total_inv >= l_total_inv_curr_gross_Amount)) AND
858 (l_due_percent is NULL)) THEN
859 --------------------------------------------------------------------
860 -- Set gross_amount to the remainder of the invoice_amount and --
861 -- set the last_term_ps_payment_number to the current payment --
862 -- number as it is the last payment number that will be updated. --
863 -- Also calculate the discount amounts of this line. --
864 --------------------------------------------------------------------
865
866 -- Make sure we do not over-subtract
867 IF ((p_sub_total >= l_total_gross_amount) OR
868 (p_sub_total_inv >= l_total_inv_curr_gross_Amount)) THEN
869 l_gross_amount := l_old_remaining_amount;
870 l_inv_curr_gross_amount := l_inv_old_remaining_amount;
871 ELSE
872 l_gross_amount := l_total_gross_amount - p_sub_total;
873 l_inv_curr_gross_amount := l_total_inv_curr_gross_amount - p_sub_total_inv; -- R11: Xcurr
874 END IF;
875 l_1st_disc_amt_available := l_gross_amount * l_disc_percent_1;
876 l_2nd_disc_amt_available := l_gross_amount * l_disc_percent_2;
877 l_3rd_disc_amt_available := l_gross_amount * l_disc_percent_3;
878 l_last_term_ps_pay_num := p_curr_ps_pay_num;
879
880
881 END IF;
882
883 -------------------------------------------
884 l_debug_info := 'Update Payment Schedules';
885 -------------------------------------------
886 --Log(l_debug_info, l_debug_loc);
887 -------------------------------
888
889 Update_Pay_Sched(p_invoice_id,
890 p_curr_ps_pay_num,
891 l_ppa_due_date,
892 l_1st_disc_amt_available,
893 l_2nd_disc_amt_available,
894 l_3rd_disc_amt_available,
895 l_1st_discount_date,
896 l_2nd_discount_date,
897 l_3rd_discount_date,
898 p_system_user,
899 l_gross_amount,
900 l_inv_curr_gross_amount, -- R11: Xcurr
901 l_percent_remain_vs_gross,
902 l_curr_calling_sequence);
903
904 --
905 -- Check if we need to create/delete payment schedule line.
906 --
907 IF (l_last_term_ps_pay_num < l_last_inv_ps_pay_num) THEN
908
909 IF (p_curr_ps_pay_num = l_last_term_ps_pay_num) THEN
910
911 -----------------------------------------------------------------
912 -- If the term has less payment schedules than the invoice, --
913 -- and the current invoice payment schedule payment number --
914 -- is equal to the last term pay schedule payment number, then --
915 l_debug_info := 'Delete Pay Schedules form the invoice';
916 -- greater than the current payment number --
917 -----------------------------------------------------------------
918 --Log(l_debug_info, l_debug_loc);
919 -------------------------------
920
921 Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id, p_curr_ps_pay_num,
922 l_curr_calling_sequence);
923
924 END IF;
925
926 ELSIF (l_last_term_ps_pay_num > l_last_inv_ps_pay_num) THEN
927
928 IF (p_curr_ps_pay_num = l_last_inv_ps_pay_num) THEN
929
930 -------------------------------------------------------------------
931 -- If the term has more payment schedules than the invoice, --
932 -- and the current invoice payment schedule payment number --
933 -- is equal to the last invoice pay schedule payment number then --
934 l_debug_info := 'Create Payment Schedules for the invoice';
935 -- greater than the current payment number --
936 -- up until the last term pay sched pay num --
937 -------------------------------------------------------------------
938 --Log(l_debug_info, l_debug_loc);
939 -------------------------------
940
941 Create_Pay_Scheds(p_invoice_id,
942 (p_curr_ps_pay_num + 1),
943 p_system_user,
944 p_start_date,
945 l_pay_curr_invoice_amount, -- R11: Xcurr
946 l_invoice_amount,
947 l_amount_applicable_to_disc,
948 p_payment_cross_rate,
949 p_term_id,
950 l_last_term_ps_pay_num,
951 p_payment_priority,
952 p_hold_flag,
953 p_payment_status_flag,
954 p_batch_id,
955 p_creation_date,
956 p_created_by,
957 p_last_update_login,
958 p_payment_method_code, --4552701
959 p_external_bank_account_id,
960 l_percent_remain_vs_gross,
961 l_curr_calling_sequence);
962
963 END IF; -- p_curr_ps_pay_num = l_last_inv_ps_pay_num --
964
965 END IF; -- l_last_term_ps_pay_num > l_last_inv_ps_pay_num --
966
967
968 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
969
970 EXCEPTION
971 WHEN OTHERS THEN
972 IF (SQLCODE <> -20001) THEN
973 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
974 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
975 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
976 FND_MESSAGE.SET_TOKEN('PARAMETERS',
977 'Invoice_id = '|| to_char(p_invoice_id)
978 || 'Curr PS Pay Num = '|| to_char(p_curr_ps_pay_num)
979 || 'Term Id = '|| to_char(p_term_id)
980 || 'Term Name = '|| p_term_name
981 || 'System User = '|| to_char(p_system_user));
982 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
983 END IF;
984 APP_EXCEPTION.RAISE_EXCEPTION;
985 END Calc_Pay_Sched;
986
987
988 --============================================================================
989 -- GET_INVOICE_INFO: Procedure to retrieve the invoice_amount,
990 -- amount_applicable_to_disc,
991 --============================================================================
992 PROCEDURE Get_Invoice_Info(p_invoice_id IN NUMBER,
993 p_invoice_amount OUT NOCOPY NUMBER,
994 p_pay_curr_invoice_amount OUT NOCOPY NUMBER, -- R11: Xcurr
995 p_amount_applicable_to_disc OUT NOCOPY NUMBER,
996 p_calling_sequence IN VARCHAR2) IS
997 l_debug_loc VARCHAR2(30) := 'Get_Invoice_Info';
998 l_curr_calling_sequence VARCHAR2(2000);
999 l_debug_info VARCHAR2(100);
1000 BEGIN
1001
1002 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1003
1004 -- Update the calling sequence --
1005
1006 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1007
1008 ---------------------------------------------------------------------------
1009 l_debug_info := 'Retrieve invoice_amount and amount_applicable_to_disc';
1010 ---------------------------------------------------------------------------
1011 --Log(l_debug_info, l_debug_loc);
1012 -------------------------------
1013
1014 SELECT invoice_amount,
1015 nvl(pay_curr_invoice_amount,invoice_amount) pay_curr_invoice_amount, -- R11: Xcurr
1016 amount_applicable_to_discount
1017 INTO p_invoice_amount,
1018 p_pay_curr_invoice_amount, -- R11: Xcurr
1019 p_amount_applicable_to_disc
1020 FROM ap_invoices
1021 WHERE invoice_id = p_invoice_id;
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 IF (SQLCODE <> -20001) THEN
1026 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1027 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1028 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1029 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1030 'Invoice_id = '|| to_char(p_invoice_id));
1031 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1032 END IF;
1033 APP_EXCEPTION.RAISE_EXCEPTION;
1034
1035 END Get_Invoice_Info;
1036
1037
1038 --============================================================================
1039 -- GET_PAYSCHED_INFO: Procedure to retrieve the total_gross_amount,
1040 -- last_invoice_pay_sched_pay_num,
1041 -- last_term_pay_sched_pay_num given the invoice_id and new terms_id.
1042 --============================================================================
1043
1044 PROCEDURE Get_PaySched_Info(p_invoice_id IN NUMBER,
1045 p_term_id IN NUMBER,
1046 p_ps_total_gross_amount IN OUT NOCOPY NUMBER,
1047 p_ps_total_inv_curr_gross_amt IN OUT NOCOPY NUMBER, -- R11: Xcurr
1048 p_last_inv_ps_pay_num IN OUT NOCOPY NUMBER,
1049 p_last_term_ps_pay_num IN OUT NOCOPY NUMBER,
1050 p_percent_remain_vs_gross IN OUT NOCOPY NUMBER,
1051 p_calling_sequence IN VARCHAR2) IS
1052
1053 l_debug_loc VARCHAR2(30) := 'Get_PaySched_Info';
1054 l_curr_calling_sequence VARCHAR2(2000);
1055 l_debug_info VARCHAR2(100);
1056 l_amount_remaining NUMBER;
1057 BEGIN
1058
1059 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1060
1061 -- Update the calling sequence --
1062
1063 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1064
1065
1066 ----------------------------------------------------------------
1067 l_debug_info := 'Retrieve last_inv_ps_pay_num and gross_amount';
1068 ----------------------------------------------------------------
1069 --Log(l_debug_info, l_debug_loc);
1070 -------------------------------
1071
1072 SELECT count(*), sum(gross_amount),
1073 sum(nvl(inv_curr_gross_amount, gross_amount)), -- R11: Xcurr
1074 sum(nvl(amount_remaining, gross_amount))
1075 INTO p_last_inv_ps_pay_num, p_ps_total_gross_amount,
1076 p_ps_total_inv_curr_gross_amt, -- R11: Xcurr
1077 l_amount_remaining
1078 FROM ap_payment_schedules
1079 WHERE invoice_id = p_invoice_id;
1080
1081 ------------------------------------------------
1082 l_debug_info := 'Retrieve last_term_ps_pay_num';
1083 ------------------------------------------------
1084 --Log(l_debug_info, l_debug_loc);
1085 -------------------------------
1086
1087 SELECT count(*)
1088 INTO p_last_term_ps_pay_num
1089 FROM ap_terms_lines
1090 WHERE term_id = p_term_id;
1091
1092 ------------------------------------------------
1093 -- get percentage of amt_remaining vs gross_amt
1094 l_debug_info := 'Retrieve percent_remain_vs_gross';
1095 ------------------------------------------------
1096 if ( p_ps_total_gross_amount = 0 ) then
1097 p_percent_remain_vs_gross := 1;
1098 else
1099 p_percent_remain_vs_gross := l_amount_remaining/p_ps_total_gross_amount;
1100 end if;
1101
1102 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 IF (SQLCODE <> -20001) THEN
1107 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1108 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1109 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1110 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1111 'Invoice_id = '|| to_char(p_invoice_id));
1112 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1113 END IF;
1114 APP_EXCEPTION.RAISE_EXCEPTION;
1115 END Get_PaySched_Info;
1116
1117
1118 --============================================================================
1119 -- UPDATE_PAY_SCHED: Procedure to update a payment schedule line.
1120 --============================================================================
1121 PROCEDURE Update_Pay_Sched(p_invoice_id IN NUMBER,
1122 p_payment_num IN NUMBER,
1123 p_ppa_due_date IN DATE,
1124 p_1st_disc_amt_available IN NUMBER,
1125 p_2nd_disc_amt_available IN NUMBER,
1126 p_3rd_disc_amt_available IN NUMBER,
1127 p_1st_discount_date IN DATE,
1128 p_2nd_discount_date IN DATE,
1129 p_3rd_discount_date IN DATE,
1130 p_system_user IN NUMBER,
1131 p_gross_amount IN NUMBER,
1132 p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
1133 p_percent_remain_vs_gross IN NUMBER,
1134 p_calling_sequence IN VARCHAR2) IS
1135 l_debug_loc VARCHAR2(30) := 'Update_Pay_Sched';
1136 l_curr_calling_sequence VARCHAR2(2000);
1137 l_pay_sched_total NUMBER; /* Bug Fix:1237758 */
1138 l_invoice_sign NUMBER; /* Bug Fix:1237758 */
1139 l_pay_curr_invoice_amount NUMBER; /* Bug Fix:1237758 */
1140 l_pay_curr_code VARCHAR2(15);
1141
1142
1143 BEGIN
1144
1145 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1146
1147 -- Update the calling sequence --
1148
1149 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1150
1151 --
1152 -- Update the amount_remaining and not updat discount_amount_remaining
1153 -- per discussion with shira and nnakos, this program will populate
1154 -- discount_amount_remaining as the same as discount_amount_available
1155 --
1156 -- get payment currency code for rounding
1157 --
1158
1159 SELECT payment_currency_code
1160 INTO l_pay_curr_code
1161 FROM ap_invoices
1162 WHERE invoice_id = p_invoice_id;
1163
1164 UPDATE ap_payment_schedules
1165 SET due_date = p_ppa_due_date,
1166 discount_amount_available = p_1st_disc_amt_available,
1167 discount_amount_remaining = p_1st_disc_amt_available,
1168 second_disc_amt_available = p_2nd_disc_amt_available,
1169 third_disc_amt_available = p_3rd_disc_amt_available,
1170 discount_date = p_1st_discount_date,
1171 second_discount_date = p_2nd_discount_date,
1172 third_discount_date = p_3rd_discount_date,
1173 last_update_date = SYSDATE,
1174 last_updated_by = p_system_user,
1175 gross_amount = p_gross_amount,
1176 inv_curr_gross_amount = p_inv_curr_gross_amount, -- R11: Xcurr
1177 amount_remaining = ap_utilities_pkg.ap_round_currency(
1178 p_gross_amount * nvl(p_percent_remain_vs_gross,1),
1179 l_pay_curr_code)
1180 WHERE invoice_id = p_invoice_id
1181 AND payment_num = p_payment_num;
1182
1183 --Bug 4539462 DBI logging
1184 AP_DBI_PKG.Maintain_DBI_Summary
1185 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1186 p_operation => 'U',
1187 p_key_value1 => p_invoice_id,
1188 p_key_value2 => p_payment_num,
1189 p_calling_sequence => l_curr_calling_sequence);
1190
1191 --Bug Fix:1237758
1192 --The following code added to take care of rounding errors
1193 SELECT SUM(gross_amount)
1194 INTO l_pay_sched_total
1195 FROM ap_payment_schedules
1196 WHERE invoice_id = P_Invoice_Id;
1197
1198 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1199
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 IF (SQLCODE <> -20001) THEN
1203 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1204 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1205 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1206 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1207 'Invoice_id = '|| to_char(p_invoice_id)
1208 || 'Payment Num = '|| to_char(p_payment_num)
1209 || 'PPA Due Date = '|| to_char(p_ppa_due_date)
1210 || '1st Disc Amt Avail = '|| to_char(p_1st_disc_amt_available)
1211 || '2nd Disc Amt Avail = '|| to_char(p_1st_disc_amt_available)
1212 || '3rd Disc Amt Avail = '|| to_char(p_1st_disc_amt_available)
1213 || '1st_discount_date = '|| to_char(p_1st_discount_date)
1214 || '2nd_discount_date = '|| to_char(p_2nd_discount_date)
1215 || '3rd_discount_date = '|| to_char(p_3rd_discount_date)
1216 || 'System User = '|| to_char(p_system_user)
1217 || 'Gross Amount = '|| to_char(p_gross_amount));
1218 END IF;
1219 APP_EXCEPTION.RAISE_EXCEPTION;
1220 END Update_Pay_Sched;
1221
1222 --============================================================================
1223 -- DELETE_PAYSCHD_WTH_PAYNUM_GTR: Delete the payment schedules for a invoice
1224 -- with payment number greater than the
1225 -- one given.
1226 --============================================================================
1227 PROCEDURE Delete_PaySchd_Wth_PayNum_Gtr(p_invoice_id IN NUMBER,
1228 p_payment_num IN NUMBER,
1229 p_calling_sequence IN VARCHAR2) IS
1230 l_debug_loc VARCHAR2(30) := 'Delete_PaySchd_Wth_PayNum_Gtr';
1231 l_curr_calling_sequence VARCHAR2(2000);
1232 BEGIN
1233
1234 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1235
1236 -- Update the calling sequence --
1237
1238 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1239
1240
1241 DELETE FROM ap_payment_schedules
1242 WHERE invoice_id = p_invoice_id
1243 AND payment_num > p_payment_num;
1244
1245 --Bug 4539462 DBI logging
1246 AP_DBI_PKG.Maintain_DBI_Summary
1247 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1248 p_operation => 'D',
1249 p_key_value1 => p_invoice_id,
1250 p_calling_sequence => l_curr_calling_sequence);
1251
1252 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 IF (SQLCODE <> -20001) THEN
1257 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1258 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1259 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1260 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1261 'Invoice_id = '|| to_char(p_invoice_id)
1262 || 'Payment Num = '|| to_char(p_payment_num));
1263 END IF;
1264 APP_EXCEPTION.RAISE_EXCEPTION;
1265 END Delete_PaySchd_Wth_PayNum_Gtr;
1266
1267
1268 --============================================================================
1269 -- CALC_PS_DATES_PERCENTS_AMTS: Procedure to calculate and return the
1270 -- payment schedule, dates, percents and
1271 -- amounts.
1272 --============================================================================
1273 PROCEDURE Calc_PS_Dates_Percents_Amts(p_invoice_id IN NUMBER,
1274 p_term_id IN NUMBER,
1275 p_ps_pay_num IN NUMBER,
1276 p_start_date IN DATE,
1277 p_total_amount IN NUMBER,
1278 p_total_pay_curr_amount IN NUMBER, -- R11: Xcurr
1279 P_amount_applicable_to_disc IN NUMBER,
1280 p_payment_cross_rate IN NUMBER,
1281 p_ppa_due_date IN OUT NOCOPY DATE,
1282 p_discount_date IN OUT NOCOPY DATE,
1283 p_second_discount_date IN OUT NOCOPY DATE,
1284 p_third_discount_date IN OUT NOCOPY DATE,
1285 p_discount_amt_available IN OUT NOCOPY NUMBER,
1286 p_secnd_disc_amt_available IN OUT NOCOPY NUMBER,
1287 p_third_disc_amt_available IN OUT NOCOPY NUMBER,
1288 p_gross_amount IN OUT NOCOPY NUMBER,
1289 p_inv_curr_gross_amount IN OUT NOCOPY NUMBER, -- R11: Xcurr
1290 p_discount_percent_1 IN OUT NOCOPY NUMBER,
1291 p_discount_percent_2 IN OUT NOCOPY NUMBER,
1292 p_discount_percent_3 IN OUT NOCOPY NUMBER,
1293 p_due_amount IN OUT NOCOPY NUMBER,
1294 p_due_percent IN OUT NOCOPY NUMBER,
1295 p_calling_sequence IN VARCHAR2) IS
1296 l_min_unit NUMBER;
1297 l_precision NUMBER;
1298 l_debug_loc VARCHAR2(30) := 'Calc_PS_Dates_Percents_Amts';
1299 l_curr_calling_sequence VARCHAR2(2000);
1300 l_debug_info VARCHAR2(100);
1301 l_discount_date DATE;
1302 l_second_discount_date DATE;
1303 l_third_discount_date DATE;
1304 l_discount_amt_available NUMBER;
1305 l_secnd_disc_amt_available NUMBER;
1306 l_third_disc_amt_available NUMBER;
1307 l_terms_calendar VARCHAR2(30);
1308
1309 BEGIN
1310
1311 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1312
1313 -- Update the calling sequence --
1314
1315 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1316
1317 l_debug_info := 'Get minimum_accountable_unit';
1318 SELECT nvl(minimum_accountable_unit,0), precision
1319 INTO l_min_unit, l_precision
1320 FROM fnd_currencies
1321 WHERE currency_code = ( SELECT payment_currency_code -- R11: Xcurr
1322 FROM ap_invoices
1323 WHERE invoice_id = p_invoice_id);
1324
1325 l_debug_info := 'Get calendar for terms lines';
1326 --
1327 SELECT calendar
1328 INTO l_terms_calendar
1329 FROM ap_terms_lines
1330 WHERE term_id = p_term_id
1331 AND sequence_num = p_ps_pay_num;
1332
1333 l_debug_info := 'Get due date info';
1334
1335 p_ppa_due_date := AP_CREATE_PAY_SCHEDS_PKG.Calc_Due_Date (
1336 p_start_date,
1337 p_term_id,
1338 l_terms_calendar,
1339 p_ps_pay_num,
1340 p_calling_sequence);
1341
1342 l_debug_info := 'Get discount_amount.. etc';
1343 SELECT
1344 -- for first discount date
1345 decode(atl.fixed_date, NULL,
1346 decode(atl.discount_days, NULL,
1347 decode(atl.discount_day_of_month,
1348 null, null,
1349 to_date(to_char(
1350 least(nvl(atl.discount_day_of_month,32), --2936672
1351 to_number(to_char(
1352 last_day(
1353 add_months(p_start_date,
1354 nvl(atl.discount_months_forward ,0)+ --2936672
1355 decode(t.due_cutoff_day,NULL,0, --2936672
1356 decode(
1357 greatest(
1358 least(NVL(t.due_cutoff_day, 32),
1359 to_number(to_char(last_day(p_start_date),'DD'))
1360 ),
1361 to_number(to_char(p_start_date,'DD'))
1362 ),
1363 to_number(to_char(p_start_date,'DD')),
1364 1, 0)))
1365 ), 'DD')))) || '-'
1366 ||to_char(add_months(p_start_date,
1367 nvl(atl.discount_months_forward ,0)+ --2936672
1368 decode(t.due_cutoff_day,NULL,0, --2936672
1369 decode(
1370 greatest(
1371 least(NVL(t.due_cutoff_day, 32),
1372 to_number(to_char(last_day(p_start_date),'DD'))
1373 ),
1374 to_number(to_char(p_start_date, 'DD'))),
1375 to_number(to_char(p_start_date, 'DD')),
1376 1, 0))), 'MON-RR'),'DD/MM/RRRR') --Bug 7534693
1377 ),
1378 p_start_date + atl.discount_days),
1379 atl.fixed_date) DISCOUNT1,
1380 -- for second discount date
1381 decode(atl.fixed_date, NULL,
1382 decode(atl.discount_days_2, null,
1383 decode(atl.discount_day_of_month_2, null, null,
1384 to_date(to_char(
1385 least(nvl(atl.discount_day_of_month_2,32), --2936672
1386 to_number(to_char(
1387 last_day(
1388 add_months(p_start_date,
1389 nvl(atl.discount_months_forward_2 ,0)+ --2936672
1390 decode(t.due_cutoff_day,NULL,0, --2936672
1391 decode(
1392 greatest(
1393 least(NVL(t.due_cutoff_day, 32),
1394 to_number(to_char(last_day(p_start_date), 'DD'))),
1395 to_number(to_char(p_start_date, 'DD'))),
1396 to_number(to_char(p_start_date, 'DD')),
1397 1, 0)))), 'DD')))) || '-'
1398 || to_char(add_months(p_start_date,
1399 nvl(atl.discount_months_forward_2 ,0)+ --2936672
1400 decode(t.due_cutoff_day,NULL,0, --2936672
1401 decode(
1402 greatest(
1403 least(NVL(t.due_cutoff_day, 32),
1404 to_number(to_char(last_day(p_start_date),'DD'))),
1405 to_number(to_char(p_start_date, 'DD'))),
1406 to_number(to_char(p_start_date,'DD')), 1, 0))), 'MON-RR'),'DD/MM/RRRR')), --Bug 7534693
1407 p_start_date + atl.discount_days_2),
1408 atl.fixed_date) DISCOUNT2,
1409 -- for the third discount date
1410 decode(atl.fixed_date, NULL,
1411 decode(atl.discount_days_3, null,
1412 decode(atl.discount_day_of_month_3, null, null,
1413 to_date(to_char(
1414 least(nvl(atl.discount_day_of_month_3,32), --2936672
1415 to_number(to_char(
1416 last_day(
1417 add_months(p_start_date,
1418 NVL(atl.discount_months_forward_3,0) + --2936672
1419 decode(t.due_cutoff_day,NULL,0, --2936672
1420 decode(
1421 greatest(
1422 least(NVL(t.due_cutoff_day, 32),
1423 to_number(to_char(last_day(p_start_date), 'DD'))),
1424 to_number(to_char(p_start_date, 'DD'))),
1425 to_number(to_char(p_start_date, 'DD')),
1426 1, 0)))), 'DD')))) || '-'
1427 || to_char(add_months(p_start_date,
1428 nvl(atl.discount_months_forward_3 ,0)+ --2936672
1429 decode(t.due_cutoff_day,NULL,0, --2936672
1430 decode(
1431 greatest(
1432 least(NVL(t.due_cutoff_day, 32),
1433 to_number(to_char(last_day(p_start_date),'DD'))),
1434 to_number(to_char(p_start_date, 'DD'))),
1435 to_number(to_char(p_start_date,'DD')), 1, 0))), 'MON-RR'),'DD/MM/RRRR')), --Bug 7534693
1436 p_start_date + atl.discount_days_3),
1437 atl.fixed_date) DISCOUNT3,
1438 -- for discount_amt_available
1439 (DECODE(l_min_unit,0,
1440 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent/100,
1441 atl.due_amount) *
1442 atl.discount_percent/100, 0),l_precision),
1443 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
1444 atl.due_amount) *
1445 atl.discount_percent/100, 0) / l_min_unit) * l_min_unit)),
1446 -- for secnd_disc_amt_available
1447 (DECODE(l_min_unit,0,
1448 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
1449 atl.due_amount) *
1450 atl.discount_percent_2/100, 0),l_precision),
1451 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
1452 atl.due_amount) *
1453 atl.discount_percent_2/100, 0) / l_min_unit) * l_min_unit)),
1454 -- for third_disc_amt_available
1455 (DECODE(l_min_unit,0,
1456 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
1457 atl.due_amount) *
1458 atl.discount_percent_3/100, 0),l_precision),
1459 ROUND(NVL(nvl(P_amount_applicable_to_disc * p_payment_cross_rate * atl.due_percent / 100,
1460 atl.due_amount) *
1461 atl.discount_percent_3/100, 0) / l_min_unit) * l_min_unit)),
1462 /* Bug fix:1237758 */
1463 DECODE(l_min_unit,0,
1464 ROUND(NVL((p_total_pay_curr_amount * due_percent/100),
1465 due_amount),l_precision),
1466 ROUND(NVL((p_total_pay_curr_amount * due_percent/100),
1467 due_amount)/l_min_unit) * l_min_unit),
1468 /* Bug fix:1237758 */
1469 DECODE(l_min_unit,0,
1470 ROUND(NVL((p_total_amount * due_percent/100),
1471 due_amount / p_payment_cross_rate),l_precision),
1472 ROUND(NVL((p_total_amount * due_percent/100),
1473 due_amount / p_payment_cross_rate)/ l_min_unit) * l_min_unit),
1474
1475 /* nvl((p_total_pay_curr_amount * due_percent/100), due_amount),
1476 nvl((p_total_amount * due_percent/100), due_amount / p_payment_cross_rate),
1477 */
1478
1479 nvl(atl.discount_percent,0),
1480 nvl( atl.discount_percent_2,0),
1481 nvl( atl.discount_percent_3,0),
1482 nvl(due_amount,0),
1483 atl.due_percent
1484 INTO
1485 l_discount_date,
1486 l_second_discount_date,
1487 l_third_discount_date,
1488 l_discount_amt_available,
1489 l_secnd_disc_amt_available,
1490 l_third_disc_amt_available,
1491 p_gross_amount,
1492 p_inv_curr_gross_amount, -- R11: Xcurr
1493 p_discount_percent_1,
1494 p_discount_percent_2,
1495 p_discount_percent_3,
1496 p_due_amount,
1497 p_due_percent
1498 FROM ap_terms t, ap_terms_lines atl
1499 WHERE t.term_id = atl.term_id
1500 AND atl.term_id = p_term_id
1501 AND atl.sequence_num = p_ps_pay_num;
1502
1503 --
1504 -- Null out NOCOPY the discount infomation if discount amount = 0
1505 --
1506 if (l_discount_amt_available = 0) then
1507 p_discount_amt_available := '';
1508 p_discount_date := '';
1509 else
1510 p_discount_amt_available := l_discount_amt_available;
1511 p_discount_date :=l_discount_date;
1512 end if;
1513
1514 if (l_secnd_disc_amt_available = 0) then
1515 p_secnd_disc_amt_available := '';
1516 p_second_discount_date := '';
1517 else
1518 p_secnd_disc_amt_available := l_secnd_disc_amt_available;
1519 p_second_discount_date := l_second_discount_date;
1520 end if;
1521
1522 if (l_third_disc_amt_available = 0) then
1523 p_third_disc_amt_available := '';
1524 p_third_discount_date := '';
1525 else
1526 p_third_disc_amt_available := l_third_disc_amt_available;
1527 p_third_discount_date := l_third_discount_date;
1528 end if;
1529
1530 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1531
1532 EXCEPTION
1533 WHEN NO_DATA_FOUND THEN
1534 RETURN;
1535 WHEN OTHERS THEN
1536 IF (SQLCODE <> -20001) THEN
1537 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1538 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1539 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1540 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1541 ' Invoice_id = '|| to_char(p_invoice_id)
1542 || ' Term Id = '|| to_char(p_term_id)
1543 || ' PS Pay Num = '|| to_char(p_ps_pay_num)
1544 || ' p_start_date = '|| to_char(p_start_date)
1545 || ' p_total_amount = '|| to_char(p_total_amount)
1546 || ' P_amount_applicable_to_disc ='|| to_char(P_amount_applicable_to_disc)
1547 || ' p_payment_cross_rate = '|| to_char(p_payment_cross_rate)
1548 || ' l_min_unit = '|| to_char(l_min_unit)
1549 || ' l_precision = '|| to_char(l_precision));
1550
1551 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1552 END IF;
1553 APP_EXCEPTION.RAISE_EXCEPTION;
1554 END Calc_PS_Dates_Percents_Amts;
1555
1556 --============================================================================
1557 -- INSERT_PAY_SCHED: Procedure to insert a new payment schedule line
1558 --============================================================================
1559 PROCEDURE Insert_Pay_Sched(p_invoice_id IN NUMBER,
1560 p_ps_pay_num IN NUMBER,
1561 p_system_user IN NUMBER,
1562 p_payment_cross_rate IN NUMBER,
1563 p_due_date IN DATE,
1564 p_1st_discount_date IN DATE,
1565 p_2nd_discount_date IN DATE,
1566 p_3rd_discount_date IN DATE,
1567 p_gross_amount IN NUMBER,
1568 p_inv_curr_gross_amount IN NUMBER, -- R11: Xcurr
1569 p_1st_disc_amt_available IN NUMBER,
1570 p_2nd_disc_amt_available IN NUMBER,
1571 p_3rd_disc_amt_available IN NUMBER,
1572 p_payment_priority IN NUMBER,
1573 p_hold_flag IN VARCHAR2,
1574 p_payment_status_flag IN VARCHAR2,
1575 p_batch_id IN NUMBER,
1576 p_creation_date IN DATE,
1577 p_created_by IN NUMBER,
1578 p_last_update_login IN NUMBER,
1579 p_payment_method_code IN VARCHAR2, --4552701
1580 p_external_bank_account_id IN NUMBER,
1581 p_percent_remain_vs_gross IN NUMBER,
1582 p_calling_sequence IN VARCHAR2) IS
1583 l_debug_loc VARCHAR2(30) := 'Insert_Pay_Sched';
1584 l_curr_calling_sequence VARCHAR2(2000);
1585 l_debug_info VARCHAR2(100);
1586 l_pay_curr_code VARCHAR2(15);
1587 /* Bug 3700128. MOAC Project */
1588 l_org_id NUMBER (15);
1589
1590 BEGIN
1591
1592 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1593
1594 -- Update the calling sequence --
1595
1596 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1597
1598 -- get payment currency code for rounding
1599 SELECT payment_currency_code, org_id
1600 INTO l_pay_curr_code, l_org_id /* Bug 3700128. MOAC Project */
1601 FROM ap_invoices
1602 WHERE invoice_id = p_invoice_id;
1603
1604
1605 INSERT INTO ap_payment_schedules
1606 (invoice_id,
1607 payment_num,
1608 last_updated_by,
1609 last_update_date,
1610 payment_cross_rate,
1611 due_date,
1612 discount_date,
1613 gross_amount,
1614 inv_curr_gross_amount, -- R11: Xcurr
1615 discount_amount_available,
1616 amount_remaining,
1617 discount_amount_remaining,
1618 payment_priority,
1619 hold_flag,
1620 payment_status_flag,
1621 batch_id,
1622 creation_date,
1623 created_by,
1624 last_update_login,
1625 payment_method_code, --4552701
1626 external_bank_account_id,
1627 second_discount_date,
1628 third_discount_date,
1629 second_disc_amt_available,
1630 third_disc_amt_available,
1631 org_id ) /* Bug 3700128. MOAC Project */
1632 VALUES (p_invoice_id,
1633 p_ps_pay_num,
1634 p_system_user,
1635 sysdate,
1636 p_payment_cross_rate,
1637 p_due_date,
1638 p_1st_discount_date,
1639 p_gross_amount,
1640 p_inv_curr_gross_amount, -- R11: Xcurr
1641 p_1st_disc_amt_available,
1642 ap_utilities_pkg.ap_round_currency(
1643 p_gross_amount * nvl(p_percent_remain_vs_gross,1),
1644 l_pay_curr_code ),
1645 p_1st_disc_amt_available,
1646 p_payment_priority,
1647 p_hold_flag,
1648 p_payment_status_flag,
1649 p_batch_id,
1650 p_creation_date,
1651 p_created_by,
1652 p_last_update_login,
1653 p_payment_method_code, --4552701
1654 p_external_bank_account_id,
1655 p_2nd_discount_date,
1656 p_3rd_discount_date,
1657 p_2nd_disc_amt_available,
1658 p_3rd_disc_amt_available,
1659 l_org_id); /* Bug 3700128. MOAC Project */
1660
1661 --Bug 4539462 DBI logging
1662 AP_DBI_PKG.Maintain_DBI_Summary
1663 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1664 p_operation => 'I',
1665 p_key_value1 => p_invoice_id,
1666 p_key_value2 => p_ps_pay_num,
1667 p_calling_sequence => l_curr_calling_sequence);
1668
1669 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 IF (SQLCODE <> -20001) THEN
1674 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1675 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1676 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1677 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1678 'Invoice_id = '|| to_char(p_invoice_id)
1679 || 'Pay Sched Pay Num = '|| to_char(p_ps_pay_num)
1680 || 'System User = '|| to_char(p_system_user)
1681 || 'Pay Cross Rate = '|| to_char(p_payment_cross_rate)
1682 || 'Due Date = '|| to_char(p_due_date)
1683 || '1st Disc Date = '|| to_char(p_1st_discount_date)
1684 || '2nd Disc Date = '|| to_char(p_2nd_discount_date)
1685 || '3rd Disc Date = '|| to_char(p_3rd_discount_date)
1686 || 'Gross Amount = '|| to_char(p_gross_amount)
1687 || '1st Disc Amt Avail = '|| to_char(p_1st_disc_amt_available)
1688 || '2nd Disc Amt Avail = '|| to_char(p_2nd_disc_amt_available)
1689 || '3rd Disc Amt Avail = '|| to_char(p_3rd_disc_amt_available)
1690 || 'Payment Priority = '|| to_char(p_payment_priority)
1691 || 'Hold Flag = '|| p_hold_flag
1692 || 'Payment Status Flag = '|| p_payment_status_flag
1693 || 'Batch_id = '|| to_char(p_batch_id)
1694 || 'Creation Date = '|| to_char(p_creation_date)
1695 || 'Created By = '|| to_char(p_created_by)
1696 || 'Last Update Login = '|| to_char(p_last_update_login)
1697 || 'Payment Method = '|| p_payment_method_code);
1698 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1699 END IF;
1700 APP_EXCEPTION.RAISE_EXCEPTION;
1701 END Insert_Pay_Sched;
1702
1703 --============================================================================
1704 -- CREATE_PAY_SCHEDS: Procedure to create new payment schedules
1705 --============================================================================
1706
1707 PROCEDURE Create_Pay_Scheds(p_invoice_id IN NUMBER,
1708 p_curr_ps_pay_num IN NUMBER,
1709 p_system_user IN NUMBER,
1710 p_start_date IN DATE,
1711 p_total_gross_amount IN NUMBER,
1712 p_total_inv_curr_gross_amount
1713 IN NUMBER, -- R11: Xcurr
1714 p_amount_applicable_to_disc IN NUMBER,
1715 p_payment_cross_rate IN NUMBER,
1716 p_term_id IN NUMBER,
1717 p_last_term_ps_pay_num IN NUMBER,
1718 p_payment_priority IN NUMBER,
1719 p_hold_flag IN VARCHAR2,
1720 p_payment_status_flag IN VARCHAR2,
1721 p_batch_id IN NUMBER,
1722 p_creation_date IN DATE,
1723 p_created_by IN NUMBER,
1724 p_last_update_login IN NUMBER,
1725 p_payment_method_code IN VARCHAR2, --4552701
1726 p_external_bank_account_id IN NUMBER,
1727 p_percent_remain_vs_gross IN NUMBER,
1728 p_calling_sequence IN VARCHAR2) IS
1729 l_last_inv_ps_pay_num NUMBER;
1730 l_last_term_ps_pay_num NUMBER;
1731 l_ppa_due_date DATE;
1732 l_1st_discount_date DATE;
1733 l_2nd_discount_date DATE;
1734 l_3rd_discount_date DATE;
1735 l_1st_disc_amt_available NUMBER;
1736 l_2nd_disc_amt_available NUMBER;
1737 l_3rd_disc_amt_available NUMBER;
1738 l_gross_amount NUMBER;
1739 l_inv_curr_gross_amount NUMBER; -- R11: Xcurr
1740 l_disc_percent_1 NUMBER;
1741 l_disc_percent_2 NUMBER;
1742 l_disc_percent_3 NUMBER;
1743 l_due_amount NUMBER;
1744 l_due_percent NUMBER;
1745 l_sub_total NUMBER;
1746 l_sub_total_inv NUMBER; -- R11: Xcurr
1747 l_curr_ps_pay_num NUMBER;
1748 l_debug_loc VARCHAR2(30) := 'Create_Pay_Scheds';
1749 l_curr_calling_sequence VARCHAR2(2000);
1750 l_debug_info VARCHAR2(100);
1751 BEGIN
1752
1753 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
1754
1755 -- Update the calling sequence --
1756
1757 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1758
1759
1760 l_curr_ps_pay_num := p_curr_ps_pay_num;
1761
1762 WHILE (l_curr_ps_pay_num <= p_last_term_ps_pay_num) LOOP
1763
1764 --------------------------------------------------------------
1765 l_debug_info := 'Calc Payment Schedule Dates, Percents, Amts';
1766 -- given invoice_id, term_id and payment num --
1767 --------------------------------------------------------------
1768 --Log(l_debug_info, l_debug_loc);
1769 -------------------------------
1770
1771 Calc_PS_Dates_Percents_Amts(p_invoice_id,
1772 p_term_id,
1773 l_curr_ps_pay_num,
1774 p_start_date,
1775 p_total_gross_amount,
1776 p_total_inv_curr_gross_amount, -- R11: Xcurr
1777 p_amount_applicable_to_disc,
1778 p_payment_cross_rate,
1779 l_ppa_due_date,
1780 l_1st_discount_date,
1781 l_2nd_discount_date,
1782 l_3rd_discount_date,
1783 l_1st_disc_amt_available,
1784 l_2nd_disc_amt_available,
1785 l_3rd_disc_amt_available,
1786 l_gross_amount,
1787 l_inv_curr_gross_amount, -- R11: Xcurr
1788 l_disc_percent_1,
1789 l_disc_percent_2,
1790 l_disc_percent_3,
1791 l_due_amount,
1792 l_due_percent,
1793 l_curr_calling_sequence);
1794
1795 l_sub_total := l_sub_total + l_gross_amount;
1796 l_sub_total_inv := l_sub_total_inv + l_inv_curr_gross_amount; -- R11: Xcurr
1797
1798 IF ((l_due_amount = 0) AND (l_due_percent IS NULL)) THEN
1799
1800 --------------------------------------------------------------------
1801 -- Set gross_amount to the remainder of the invoice_amount and --
1802 -- set the last_term_ps_payment_number to the current payment --
1803 -- number as it is the last payment number that will be updated. --
1804 -- Also calculate the discount amounts of this line. --
1805 --------------------------------------------------------------------
1806
1807 l_gross_amount := p_total_gross_amount - l_sub_total;
1808 l_inv_curr_gross_amount := p_total_inv_curr_gross_amount - l_sub_total_inv; -- R11: Xcurr
1809 l_1st_disc_amt_available := l_gross_amount * l_disc_percent_1;
1810 l_2nd_disc_amt_available := l_gross_amount * l_disc_percent_2;
1811 l_3rd_disc_amt_available := l_gross_amount * l_disc_percent_3;
1812 l_last_term_ps_pay_num := l_curr_ps_pay_num;
1813
1814 END IF;
1815
1816 -------------------------------------------
1817 l_debug_info := 'Insert Payment Schedules';
1818 -------------------------------------------
1819 --Log(l_debug_info, l_debug_loc);
1820 -------------------------------
1821
1822 Insert_Pay_Sched(p_invoice_id,
1823 l_curr_ps_pay_num,
1824 p_system_user,
1825 p_payment_cross_rate,
1826 l_ppa_due_date,
1827 l_1st_discount_date,
1828 l_2nd_discount_date,
1829 l_3rd_discount_date,
1830 l_gross_amount,
1831 l_inv_curr_gross_amount, -- R11: Xcurr
1832 l_1st_disc_amt_available,
1833 l_2nd_disc_amt_available,
1834 l_3rd_disc_amt_available,
1835 p_payment_priority,
1836 p_hold_flag,
1837 p_payment_status_flag,
1838 p_batch_id,
1839 p_creation_date,
1840 p_created_by,
1841 p_last_update_login,
1842 p_payment_method_code, --4552701
1843 p_external_bank_account_id,
1844 p_percent_remain_vs_gross,
1845 l_curr_calling_sequence);
1846
1847 l_curr_ps_pay_num := l_curr_ps_pay_num + 1;
1848
1849 END LOOP;
1850
1851 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
1852
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 IF (SQLCODE <> -20001) THEN
1856 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1857 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1858 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1859 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1860 'Invoice_id = '|| to_char(p_invoice_id)
1861 || 'Cur PS Pay Num = '|| to_char(p_curr_ps_pay_num)
1862 || 'System User = '|| to_char(p_system_user)
1863 || 'Payment Cross Rate = '|| to_char(p_payment_cross_rate)
1864 || 'Term_id = '|| to_char(p_term_id)
1865 || 'Last Term PS Pay Num = '|| to_char(p_last_term_ps_pay_num)
1866 || 'Pay Priority = '|| to_char(p_payment_priority)
1867 || 'Hold Flag = '|| p_hold_flag
1868 || 'Payment Status Flag = '|| p_payment_status_flag
1869 || 'Batch Id = '|| to_char(p_batch_id)
1870 || 'Creation Date = '|| to_char(p_creation_date)
1871 || 'Created By = '|| to_char(p_created_by)
1872 || 'Last Update Login = '|| to_char(p_last_update_login)
1873 || 'Payment Method Lookup = '|| p_payment_method_code
1874 ||' p_external_bank_account_id = '||to_char(p_external_bank_account_id));
1875 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1876 END IF;
1877 APP_EXCEPTION.RAISE_EXCEPTION;
1878 END Create_Pay_Scheds;
1879
1880
1881 -- Short-named procedure for logging
1882
1883 PROCEDURE Log(p_msg IN VARCHAR2,
1884 p_loc IN VARCHAR2) IS
1885 BEGIN
1886 null;
1887 --AP_LOGGING_PKG.AP_Log(p_msg, p_loc);
1888 END Log;
1889
1890
1891 --2189242, added procedure below
1892
1893 Procedure Adj_Pay_Sched_For_Round (p_invoice_id in number,
1894 p_calling_sequence varchar2) IS
1895
1896 l_pay_sched_total NUMBER;
1897 l_pay_curr_invoice_amount NUMBER;
1898 l_debug_loc VARCHAR(200);
1899 l_curr_calling_sequence VARCHAR(2000);
1900 l_key_value NUMBER;
1901
1902 BEGIN
1903
1904 l_debug_loc := 'Update ap_payment_schedules - set gross_amount';
1905 l_curr_calling_sequence := 'AP_PPA_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
1906
1907 SELECT SUM(gross_amount)
1908 INTO l_pay_sched_total
1909 FROM ap_payment_schedules
1910 WHERE invoice_id = P_Invoice_Id;
1911
1912 SELECT nvl(pay_curr_invoice_amount,invoice_amount) pay_curr_invoice_amount
1913 INTO l_pay_curr_invoice_amount
1914 FROM ap_invoices
1915 WHERE invoice_id = p_invoice_id;
1916
1917 -- Adjust Payment Schedules for rounding errors
1918 IF (l_pay_sched_total <> l_Pay_Curr_Invoice_Amount) THEN
1919
1920
1921 UPDATE AP_PAYMENT_SCHEDULES
1922 SET gross_amount = gross_amount + TO_NUMBER(l_Pay_Curr_Invoice_Amount) -
1923 TO_NUMBER(l_pay_sched_total),
1924 amount_remaining=amount_remaining + TO_NUMBER(l_Pay_Curr_Invoice_Amount) -
1925 TO_NUMBER(l_pay_sched_total)
1926 WHERE invoice_id = P_Invoice_Id
1927 AND payment_num = (SELECT MAX(payment_num)
1928 FROM ap_payment_schedules
1929 WHERE invoice_id = P_Invoice_Id);
1930
1931 --Bug 4539462 DBI logging
1932 AP_DBI_PKG.Maintain_DBI_Summary
1933 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1934 p_operation => 'U',
1935 p_key_value1 => p_invoice_id,
1936 p_key_value2 => l_key_value,
1937 p_calling_sequence => l_curr_calling_sequence);
1938
1939 END IF;
1940
1941
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 IF (SQLCODE <> -20001) THEN
1946 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1947 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1948 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1949 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1950 'Invoice_id = '|| to_char(p_invoice_id));
1951 END IF;
1952 APP_EXCEPTION.RAISE_EXCEPTION;
1953
1954
1955 END Adj_Pay_Sched_For_Round;
1956
1957 END AP_PPA_PKG;