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