[Home] [Help]
PACKAGE BODY: APPS.AP_PAY_IN_FULL_PKG
Source
1 PACKAGE BODY AP_PAY_IN_FULL_PKG AS
2 /* $Header: apayfulb.pls 120.19.12010000.7 2009/02/04 09:08:30 ssontine ship $ */
3
4 ---------------------------------------------------------------------
5 -- Procedure AP_Lock_Invoices parses the P_invoice_id_list and locks all
6 -- the invoices in the list. This procedure also returns information
7 -- needed by the Single Payment workbench to pay these invoices in full.
8 --
9
10 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
13 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
14 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
15 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
17 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_ACCOUNTING_EVENT_PKG.';
18
19 --Modified below procedure parameter types for bug #7721348/7758448
20 PROCEDURE AP_Lock_Invoices(
21 P_invoice_id_list IN VARCHAR2,
22 P_payment_num_list IN VARCHAR2,
23 P_currency_code OUT NOCOPY VARCHAR2,
24 P_payment_method OUT NOCOPY AP_PAYMENT_SCHEDULES.PAYMENT_METHOD_CODE%TYPE, --VARCHAR2,
25 P_vendor_id OUT NOCOPY AP_SUPPLIERS.VENDOR_ID%TYPE, --NUMBER,
26 P_vendor_site_id OUT NOCOPY AP_SUPPLIER_SITES.VENDOR_SITE_ID%TYPE, --NUMBER,
27 P_party_id OUT NOCOPY AP_SUPPLIERS.PARTY_ID%TYPE, --NUMBER,
28 P_party_site_id OUT NOCOPY AP_SUPPLIER_SITES.PARTY_SITE_ID%TYPE, --NUMBER,
29 P_org_id OUT NOCOPY NUMBER,
30 P_payment_function OUT NOCOPY AP_INVOICES.PAYMENT_FUNCTION%TYPE, --VARCHAR2, -- 4965233
31 P_proc_trxn_type OUT NOCOPY AP_INVOICES.PAY_PROC_TRXN_TYPE_CODE%TYPE, --VARCHAR2, -- 4965233
32 P_num_payments OUT NOCOPY NUMBER,
33 P_le_id OUT NOCOPY NUMBER, -- 5617689
34 --Added below variables for the bug 7662240
35 P_remit_vendor_id OUT NOCOPY AP_SUPPLIERS.VENDOR_ID%TYPE, --NUMBER,
36 P_remit_vendor_site_id OUT NOCOPY AP_SUPPLIER_SITES.VENDOR_SITE_ID%TYPE, --NUMBER,
37 P_remit_party_id OUT NOCOPY AP_SUPPLIERS.PARTY_ID%TYPE, --NUMBER,
38 P_remit_party_site_id OUT NOCOPY AP_SUPPLIER_SITES.PARTY_SITE_ID%TYPE, --NUMBER,
39 P_remit_vendor_name OUT NOCOPY AP_SUPPLIERS.VENDOR_NAME%TYPE, --VARCHAR2,
40 P_remit_vendor_site_name OUT NOCOPY AP_SUPPLIER_SITES.VENDOR_SITE_CODE%TYPE, --VARCHAR2,
41 P_calling_sequence IN VARCHAR2,
42 --Added below parameter for 7688200
43 p_relationship_id OUT NOCOPY NUMBER)
44 IS
45 l_invoice_id NUMBER;
46 l_inv_pos NUMBER;
47 l_inv_next NUMBER;
48 l_pay_pos NUMBER;
49 l_pay_next NUMBER;
50 l_num_payments NUMBER := 0;
51 l_payment_num NUMBER;
52 l_log_msg VARCHAR2(240);
53 l_curr_calling_sequence VARCHAR2(2000);
54 BEGIN
55 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.AP_LOCK_INVOICES<-' ||
56 P_calling_sequence;
57
58 -- Parse P_invoice_id_list and lock invoices
59 --
60 l_inv_pos := 1;
61
62 LOOP
63 l_inv_next := INSTR(P_invoice_id_list, ' ', l_inv_pos);
64 IF (l_inv_next = 0) THEN
65 l_inv_next := LENGTH(P_invoice_id_list) + 1;
66 END IF;
67
68 l_invoice_id := TO_NUMBER(SUBSTR(P_invoice_id_list,
69 l_inv_pos,
70 l_inv_next - l_inv_pos));
71
72 l_log_msg := 'Locking invoice_id:' || to_char(l_invoice_id);
73 AP_INVOICES_PKG.LOCK_ROW(l_invoice_id,
74 l_curr_calling_sequence);
75
76 -- Determine the number of payments for this invoice
77 --
78 IF (P_payment_num_list IS NULL) THEN
79
80 l_log_msg := 'Get number of payments for invoice_id:' ||
81 to_char(l_invoice_id);
82
83 SELECT count(*) + l_num_payments
84 INTO l_num_payments
85 FROM ap_invoices_ready_to_pay_v
86 WHERE invoice_id = l_invoice_id;
87
88 ELSE
89 -- Parse and count P_payment_num_list
90 --
91 l_pay_pos := 1;
92
93 LOOP
94 l_pay_next := INSTR(P_payment_num_list, ' ', l_pay_pos);
95 IF (l_pay_next = 0) THEN
96 l_pay_next := LENGTH(P_payment_num_list) + 1;
97 END IF;
98
99 l_num_payments := l_num_payments + 1;
100
101 EXIT WHEN (l_pay_next = LENGTH(P_payment_num_list) + 1);
102 l_pay_pos := l_pay_next + 1;
103
104 END LOOP;
105
106 END IF;
107
108 EXIT WHEN (l_inv_next > LENGTH(P_invoice_id_list));
109 l_inv_pos := l_inv_next + 1;
110
111 END LOOP;
112
113 l_log_msg := 'Get vendor and currency info for invoice_id:' ||
114 to_char(l_invoice_id);
115
116
117
118 -- Perf bugfix 5052493
119 -- Go to base table AP_INVOICES_ALL to reduce shared memory usage
120 SELECT a.payment_currency_code,
121 b.payment_method_code, --4552701
122 a.vendor_id,
123 a.vendor_site_id,
124 a.party_id,
125 a.party_site_id,
126 a.org_id,
127 l_num_payments,
128 a.payment_function,
129 a.pay_proc_trxn_type_code,
130 a.legal_entity_id,
131 /* commented as part of bug 7688200
132 , -- Bug 5617689 */--Bug 7860631 Uncommented the commeted code.
133 b.remit_to_supplier_id, --Bug 7662240
134 b.remit_to_supplier_site_id,
135 b.remit_to_supplier_name,
136 b.remit_to_supplier_site,
137 b.relationship_id
138 INTO P_currency_code,
139 P_payment_method,
140 P_vendor_id,
141 P_vendor_site_id,
142 P_party_id,
143 P_party_site_id,
144 P_org_id,
145 P_num_payments,
146 P_payment_function,
147 P_proc_trxn_type,
148 p_le_id,
149 /* commented as part of bug 7688200
150 , -- Bug 5617689 */--Bug 7860631 Uncommented the commented code.
151 P_remit_vendor_id, -- Bug 7662240
152 P_remit_vendor_site_id,
153 p_remit_vendor_name,
154 p_remit_vendor_site_name,
155 p_relationship_id
156 FROM ap_invoices_all a, ap_payment_schedules_all b --Bug 7662240
157 WHERE a.invoice_id = l_invoice_id
158 and a.invoice_id = b.invoice_id
159 and rownum<2;
160
161 /* Need to get payment method if paying from the payment schedule level */
162 If (P_Payment_num_list IS NOT NULL) then
163 -- get the first payment num.
164 l_pay_next := INSTR(P_payment_num_list,' ',1);
165 If l_pay_next = 0 then
166 l_pay_next := length(p_payment_num_list) +1;
167 End if;
168 l_payment_num := to_number(substr(p_payment_num_list,1,l_pay_next));
169
170 --7662240 Added all remit related variables in below select statement
171 SELECT payment_method_code,
172 /* commented as part of bug 7688200
173 , --4552701 */--Bug 7860631 Uncommented the commeted code.
174 remit_to_supplier_id, --7662240
175 remit_to_supplier_site_id,
176 remit_to_supplier_name,
177 remit_to_supplier_site,
178 relationship_id
179 INTO p_payment_method,
180 /* commented as part of bug 7688200
181 ,*/--Bug 7860631 Uncommented the commented code.
182 P_remit_vendor_id, -- Bug 7662240
183 P_remit_vendor_site_id,
184 p_remit_vendor_name,
185 p_remit_vendor_site_name,
186 p_relationship_id
187 FROM ap_payment_schedules
188 WHERE invoice_id = l_invoice_id
189 and payment_num = l_payment_num;
190 End if;
191
192 /* Bug 7860631 Added If condition to avoid the call to IBY and the query Payment request type invoices */
193 If (p_remit_vendor_id > 0 and p_remit_vendor_site_id > 0) then
194 --Begin 7662240
195 -- modified as part of bug 7688200. start
196 IBY_EXT_PAYEE_RELSHIPS_PKG.default_Ext_Payee_Relationship(
197 p_party_id => p_party_id,
198 p_supplier_site_id => P_vendor_site_id,
199 p_date => sysdate,
200 x_remit_party_id => p_remit_party_id,
201 x_remit_supplier_site_id => P_remit_vendor_site_id,
202 x_relationship_id => p_relationship_id
203 );
204
205 SELECT APS.vendor_id, APS.vendor_name, APSS.party_site_id, APSS.vendor_site_code
206 INTO p_remit_vendor_id, p_remit_vendor_name, p_remit_party_site_id, p_remit_vendor_site_name
207 FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES APSS
208 WHERE APS.party_id = p_remit_party_id
209 AND APS.vendor_id =APSS.vendor_id
210 AND APSS.vendor_site_id = p_remit_vendor_site_id;
211
212 -- modified as part of bug 7688200. end
213 --End 7662240
214 -- 7860631. To handle payment request type invoices
215 ELSIF (p_remit_party_id is null AND
216 p_remit_party_site_id is null AND
217 (p_remit_vendor_id = p_vendor_id) AND
218 (p_remit_vendor_site_id = p_vendor_site_id) ) THEN
219
220 SELECT party_id, party_site_id
221 INTO p_remit_party_id, p_remit_party_site_id
222 FROM ap_invoices_all
223 WHERE invoice_id = l_invoice_id;
224
225 end if; --7860631
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 IF (SQLCODE <> -20001) THEN
230 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
231 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
232 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
233 FND_MESSAGE.SET_TOKEN('PARAMETERS',
234 ' P_invoice_id_list = ' || P_invoice_id_list);
235 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
236 END IF;
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 END AP_Lock_Invoices;
239
240
241 ---------------------------------------------------------------------
242 -- Function AP_Discount_Available determines whether or not discounts
243 -- are available for the invoices in P_invoice_id_list
244 --
245 FUNCTION AP_Discount_Available(P_invoice_id_list IN VARCHAR2,
246 P_payment_num_list IN VARCHAR2,
247 P_check_date IN DATE,
248 P_currency_code IN VARCHAR2,
249 P_calling_sequence IN VARCHAR2)
250 RETURN BOOLEAN
251 IS
252 l_invoice_id NUMBER;
253 l_payment_num NUMBER;
254 l_pos NUMBER;
255 l_next NUMBER;
256 l_discount_available NUMBER := 0;
257 l_log_msg VARCHAR2(240);
258 l_curr_calling_sequence VARCHAR2(2000);
259 BEGIN
260 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.AP_DISCOUNT_AVAILABLE<-' ||
261 P_calling_sequence;
262 l_pos := 1;
263
264 IF (P_payment_num_list IS NOT NULL) THEN
265
266 l_invoice_id := TO_NUMBER(P_invoice_id_list);
267 --
268 -- Parse P_payment_num_list
269 --
270 LOOP
271 l_next := INSTR(P_payment_num_list, ' ', l_pos);
272 IF (l_next = 0) THEN
273 l_next := LENGTH(P_payment_num_list) + 1;
274 END IF;
275
276 l_payment_num := TO_NUMBER(SUBSTR(P_payment_num_list,
277 l_pos,
278 l_next - l_pos));
279
280 l_log_msg := 'Get discount available for invoice_id:' ||
281 to_char(l_invoice_id) || ' payment_num:' ||
282 to_char(l_payment_num);
283
284 SELECT ap_payment_schedules_pkg.get_discount_available(
285 invoice_id,
286 payment_num,
287 P_check_date,
288 P_currency_code)
289 INTO l_discount_available
290 FROM ap_invoices_ready_to_pay_v
291 WHERE invoice_id = l_invoice_id
292 AND payment_num = l_payment_num;
293
294 EXIT WHEN (l_discount_available > 0 OR
295 l_next > LENGTH(P_payment_num_list));
296 l_pos := l_next + 1;
297
298 END LOOP;
299
300 ELSIF (P_invoice_id_list IS NOT NULL) THEN
301 --
302 -- Parse P_invoice_id_list
303 --
304 LOOP
305 l_next := INSTR(P_invoice_id_list, ' ', l_pos);
306 IF (l_next = 0) THEN
307 l_next := LENGTH(P_invoice_id_list) + 1;
308 END IF;
309
310 l_invoice_id := TO_NUMBER(SUBSTR(P_invoice_id_list,
311 l_pos,
312 l_next - l_pos));
313
314 l_log_msg := 'Get discount available for invoice_id:' ||
315 to_char(l_invoice_id);
316
317 SELECT SUM(ap_payment_schedules_pkg.get_discount_available(
318 invoice_id,
319 payment_num,
320 P_check_date,
321 P_currency_code))
322 INTO l_discount_available
323 FROM ap_invoices_ready_to_pay_v
324 WHERE invoice_id = l_invoice_id;
325
326 EXIT WHEN (l_discount_available > 0 OR
327 l_next > LENGTH(P_invoice_id_list));
328 l_pos := l_next + 1;
329
330 END LOOP;
331
332 END IF;
333
334 -- Fix for 962271. For refunds the l_discount_available would be negative,
335 -- so the following if condition should be: IF(l_discount_available <> 0) (instead of > 0)
336 -- since it can be positive or negative depending upon the payment.
337 --
338 IF (l_discount_available <> 0) THEN
339 RETURN TRUE;
340 ELSE
341 RETURN FALSE;
342 END IF;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 IF (SQLCODE <> -20001) THEN
347 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
348 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
349 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
350 FND_MESSAGE.SET_TOKEN('PARAMETERS',
351 ' P_invoice_id_list = ' ||P_invoice_id_list ||
352 ' P_payment_num_list = ' ||P_payment_num_list ||
353 ' P_check_date = ' ||P_check_date ||
354 ' P_currency_code = ' ||P_currency_code);
355 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
356 END IF;
357 APP_EXCEPTION.RAISE_EXCEPTION;
358 END AP_Discount_Available;
359
360 ---------------------------------------------------------------------
361 -- Function Get_Single_Payment_Amount is called by Get_Check_Amount
362 -- to compute the amount of a single payment
363 --
364 FUNCTION Get_Single_Payment_Amount(P_invoice_id IN NUMBER,
365 P_payment_num IN NUMBER,
366 P_payment_type_flag IN VARCHAR2,
367 P_check_date IN DATE,
368 P_currency_code IN VARCHAR2,
369 P_take_discount IN VARCHAR2,
370 P_sys_auto_calc_int_flag IN VARCHAR2,
371 P_auto_calc_int_flag IN VARCHAR2,
372 P_calling_sequence IN VARCHAR2)
373 RETURN NUMBER
374 IS
375 l_payment_num NUMBER;
376 l_total_amount NUMBER := 0;
377 l_amount_remaining NUMBER;
378 l_discount_available NUMBER;
379 l_discount_taken NUMBER;
380 l_interest_amount NUMBER;
381 l_payment_amount NUMBER;
382 l_due_date DATE;
383 l_interest_invoice_num VARCHAR2(50);
384 l_log_msg VARCHAR2(240);
385 l_curr_calling_sequence VARCHAR2(2000);
386
387 -------------------------------------------------------------------
388 -- Declare cursor to compute single payment amount
389 --
390 CURSOR payments_cursor IS
391 SELECT payment_num,
392 amount_remaining,
393 ap_payment_schedules_pkg.get_discount_available(
394 invoice_id,
395 payment_num,
396 P_check_date,
397 P_currency_code)
398 FROM ap_invoices_ready_to_pay_v
399 WHERE invoice_id = P_invoice_id
400 AND payment_num = nvl(P_payment_num, payment_num);
401
402 BEGIN
403 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.GET_SINGLE_PAYMENT_AMOUNT<-' ||
404 P_calling_sequence;
405
406 l_log_msg := 'Open payments_cursor';
407 OPEN payments_cursor;
408
409 LOOP
410 l_log_msg := 'Fetch payments_cursor';
411 FETCH payments_cursor
412 INTO l_payment_num,
413 l_amount_remaining,
414 l_discount_available;
415
416 EXIT WHEN payments_cursor%NOTFOUND;
417
418 -- For pay-in-full payment amount = amount remaining
419
420 l_payment_amount := l_amount_remaining;
421 --
422 -- Calculate discount taken
423 --
424 IF (P_take_discount = 'Y') THEN
425 l_discount_taken := l_discount_available;
426 ELSE
427 l_discount_taken := 0;
428 END IF;
429
430 --
431 -- Calculate interest invoice amount
432 --
433 IF ((P_payment_type_flag = 'Q')
434 AND (P_auto_calc_int_flag = 'Y')) THEN --Bug 2119368: AND condition added
435 l_log_msg := 'Calulate interest invoice amount for invoice_id:' ||
436 to_char(P_invoice_id) || ' payment_num:' ||
437 to_char(l_payment_num);
438 AP_INTEREST_INVOICE_PKG.AP_CALCULATE_INTEREST(
439 P_invoice_id,
440 P_sys_auto_calc_int_flag,
441 P_auto_calc_int_flag,
442 P_check_date,
443 l_payment_num,
444 l_amount_remaining,
445 l_discount_taken,
446 l_discount_available,
447 P_currency_code,
448 l_interest_amount,
449 l_due_date,
450 l_interest_invoice_num,
451 l_payment_amount,
452 l_curr_calling_sequence);
453 ELSE
454 l_interest_amount := 0;
455 END IF;
456
457 --
458 -- Calculate total amount
459 --
460 l_total_amount := l_total_amount + l_amount_remaining
461 + l_interest_amount
462 - l_discount_taken;
463 END LOOP;
464
465 l_log_msg := 'Close payments_cursor';
466 CLOSE payments_cursor;
467
468 RETURN l_total_amount;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 IF (SQLCODE <> -20001) THEN
473 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
474 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
475 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
476 FND_MESSAGE.SET_TOKEN('PARAMETERS',
477 ' P_invoice_id = ' || P_invoice_id ||
478 ' P_payment_num = ' || P_payment_num ||
479 ' P_payment_type_flag = ' || P_payment_type_flag ||
480 ' P_check_date = ' || P_check_date ||
481 ' P_currency_code = ' || P_currency_code ||
482 ' P_take_discount = ' || P_take_discount ||
483 ' P_sys_auto_calc_int_flag = '|| P_sys_auto_calc_int_flag ||
484 ' P_auto_calc_int_flag = ' || P_auto_calc_int_flag);
485 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
486 END IF;
487 APP_EXCEPTION.RAISE_EXCEPTION;
488 END Get_Single_Payment_Amount;
489
490
491 ---------------------------------------------------------------------
492 -- Function AP_Get_Check_Amount computes the total check amount including
493 -- discount and interest amounts (if applicable) for the invoices in
494 -- P_invoice_id_list
495 --
496 FUNCTION AP_Get_Check_Amount(P_invoice_id_list IN VARCHAR2,
497 P_payment_num_list IN VARCHAR2,
498 P_payment_type_flag IN VARCHAR2,
499 P_check_date IN DATE,
500 P_currency_code IN VARCHAR2,
501 P_take_discount IN VARCHAR2,
502 P_sys_auto_calc_int_flag IN VARCHAR2,
503 P_auto_calc_int_flag IN VARCHAR2,
504 P_calling_sequence IN VARCHAR2)
505 RETURN NUMBER
506 IS
507 l_invoice_id NUMBER;
508 l_payment_num NUMBER;
509 l_pos NUMBER;
510 l_next NUMBER;
511 l_total_check_amount NUMBER := 0;
512 l_log_msg VARCHAR2(240);
513 l_curr_calling_sequence VARCHAR2(2000);
514
515 BEGIN
516 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.AP_GET_CHECK_AMOUNT<-' ||
517 P_calling_sequence;
518 l_pos := 1;
519
520 IF (P_payment_num_list IS NOT NULL) THEN
521
522 l_invoice_id := TO_NUMBER(P_invoice_id_list);
523 --
524 -- Parse P_payment_num_list
525 --
526 LOOP
527 l_next := INSTR(P_payment_num_list, ' ', l_pos);
528 IF (l_next = 0) THEN
529 l_next := LENGTH(P_payment_num_list) + 1;
530 END IF;
531
532 l_payment_num := TO_NUMBER(SUBSTR(P_payment_num_list,
533 l_pos,
534 l_next - l_pos));
535
536 l_total_check_amount := l_total_check_amount +
537 Get_Single_Payment_Amount(
538 l_invoice_id,
539 l_payment_num,
540 P_payment_type_flag,
541 P_check_date,
542 P_currency_code,
543 P_take_discount,
544 P_sys_auto_calc_int_flag,
545 P_auto_calc_int_flag,
546 l_curr_calling_sequence);
547
548 EXIT WHEN (l_next > LENGTH(P_payment_num_list));
549 l_pos := l_next + 1;
550
551 END LOOP;
552
553 ELSIF (P_invoice_id_list IS NOT NULL) THEN
554 --
555 -- Parse P_invoice_id_list
556 --
557 LOOP
558 l_next := INSTR(P_invoice_id_list, ' ', l_pos);
559 IF (l_next = 0) THEN
560 l_next := LENGTH(P_invoice_id_list) + 1;
561 END IF;
562
563 l_invoice_id := TO_NUMBER(SUBSTR(P_invoice_id_list,
564 l_pos,
565 l_next - l_pos));
566
567 l_log_msg := 'Get discount available for invoice_id:' ||
568 to_char(l_invoice_id);
569
570 l_total_check_amount := l_total_check_amount +
571 Get_Single_Payment_Amount(
572 l_invoice_id,
573 NULL,
574 P_payment_type_flag,
575 P_check_date,
576 P_currency_code,
577 P_take_discount,
578 P_sys_auto_calc_int_flag,
579 P_auto_calc_int_flag,
580 l_curr_calling_sequence);
581
582 EXIT WHEN (l_next > LENGTH(P_invoice_id_list));
583 l_pos := l_next + 1;
584
585 END LOOP;
586
587 END IF;
588
589 RETURN l_total_check_amount;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF (SQLCODE <> -20001) THEN
594 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
595 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
596 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
597 FND_MESSAGE.SET_TOKEN('PARAMETERS',
598 ' P_invoice_id_list = ' || P_invoice_id_list ||
599 ' P_payment_num_list = ' || P_payment_num_list ||
600 ' P_payment_type_flag = ' || P_payment_type_flag ||
601 ' P_check_date = ' || P_check_date ||
602 ' P_currency_code = ' || P_currency_code ||
603 ' P_take_discount = ' || P_take_discount ||
604 ' P_sys_auto_calc_int_flag = '|| P_sys_auto_calc_int_flag ||
605 ' P_auto_calc_int_flag = ' || P_auto_calc_int_flag);
606 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
607 END IF;
608 APP_EXCEPTION.RAISE_EXCEPTION;
609 END AP_Get_Check_Amount;
610
611
612 ---------------------------------------------------------------------
613 -- Procedure Create_Single_Payment is called by Create_Payments to
614 -- create the payment(s) for a single invoice
615 --
616 PROCEDURE Create_Single_Payment(P_invoice_id IN NUMBER,
617 P_payment_num IN NUMBER,
618 P_check_id IN NUMBER,
619 P_payment_type_flag IN VARCHAR2,
620 P_payment_method IN VARCHAR2,
621 P_ce_bank_acct_use_id IN NUMBER,
622 P_bank_account_num IN VARCHAR2,
623 P_bank_account_type IN VARCHAR2,
624 P_bank_num IN VARCHAR2,
625 P_check_date IN DATE,
626 P_period_name IN VARCHAR2,
627 P_currency_code IN VARCHAR2,
628 P_base_currency_code IN VARCHAR2,
629 P_checkrun_name IN VARCHAR2,
630 P_doc_sequence_value IN NUMBER,
631 P_doc_sequence_id IN NUMBER,
632 P_exchange_rate IN NUMBER,
633 P_exchange_rate_type IN VARCHAR2,
634 P_exchange_date IN DATE,
635 P_take_discount IN VARCHAR2,
636 P_sys_auto_calc_int_flag IN VARCHAR2,
637 P_auto_calc_int_flag IN VARCHAR2,
638 P_set_of_books_id IN NUMBER,
639 P_future_pay_ccid IN NUMBER,
640 P_last_updated_by IN NUMBER,
641 P_last_update_login IN NUMBER,
642 P_calling_sequence IN VARCHAR2,
643 P_sequential_numbering IN VARCHAR2,
644 P_accounting_event_id IN NUMBER, --Events
645 P_org_id IN NUMBER)
646 IS
647 l_invoice_payment_id NUMBER;
648 l_payment_num NUMBER;
649 l_invoice_type VARCHAR2(25);
650 l_invoice_num VARCHAR2(50);
651 l_vendor_id NUMBER;
652 l_vendor_site_id NUMBER;
653 l_exclusive_payment_flag VARCHAR2(1);
654 l_future_pay_posted_flag VARCHAR2(1);
655 l_accts_pay_ccid NUMBER;
656 l_amount NUMBER;
657 l_amount_remaining NUMBER;
658 l_discount_available NUMBER;
659 l_discount_taken NUMBER;
660 l_interest_invoice_id NUMBER;
661 l_interest_invoice_pay_id NUMBER;
662 l_interest_amount NUMBER;
663 l_payment_amount NUMBER;
664 l_due_date DATE;
665 l_interest_invoice_num VARCHAR2(50);
666 l_invoice_description VARCHAR2(240);
667 l_attribute1 VARCHAR2(150);
668 l_attribute2 VARCHAR2(150);
669 l_attribute3 VARCHAR2(150);
670 l_attribute4 VARCHAR2(150);
671 l_attribute5 VARCHAR2(150);
672 l_attribute6 VARCHAR2(150);
673 l_attribute7 VARCHAR2(150);
674 l_attribute8 VARCHAR2(150);
675 l_attribute9 VARCHAR2(150);
676 l_attribute10 VARCHAR2(150);
677 l_attribute11 VARCHAR2(150);
678 l_attribute12 VARCHAR2(150);
679 l_attribute13 VARCHAR2(150);
680 l_attribute14 VARCHAR2(150);
681 l_attribute15 VARCHAR2(150);
682 l_attribute_category VARCHAR2(150);
683 l_log_msg VARCHAR2(240);
684 l_curr_calling_sequence VARCHAR2(2000);
685 l_int_inv_doc_seq_v NUMBER; --1724353
686 l_int_inv_doc_seq_id NUMBER;
687 l_int_inv_doc_seq_nm FND_DOCUMENT_SEQUENCES.NAME%TYPE;
688
689 -------------------------------------------------------------------
690 -- Declare cursor to pay single invoice
691 --
692 CURSOR payments_cursor IS
693 SELECT AIRP.payment_num,
694 AIRP.invoice_type,
695 AIRP.invoice_num,
696 AIRP.vendor_id,
697 AIRP.vendor_site_id,
698 AIRP.exclusive_payment_flag,
699 AIRP.accts_pay_code_combi_id,
700 AIRP.amount_remaining,
701 ap_payment_schedules_pkg.get_discount_available(
702 AIRP.invoice_id,
703 AIRP.payment_num,
704 P_check_date,
705 P_currency_code),
706 APS.attribute1,
707 APS.attribute2,
708 APS.attribute3,
709 APS.attribute4,
710 APS.attribute5,
711 APS.attribute6,
712 APS.attribute7,
713 APS.attribute8,
714 APS.attribute9,
715 APS.attribute10,
716 APS.attribute11,
717 APS.attribute12,
718 APS.attribute13,
719 APS.attribute14,
720 APS.attribute15,
721 APS.attribute_category
722 FROM ap_invoices_ready_to_pay_v AIRP,
723 ap_payment_schedules APS
724 WHERE AIRP.invoice_id = P_invoice_id
725 AND AIRP.payment_num = nvl(P_payment_num, AIRP.payment_num)
726 AND APS.invoice_id = AIRP.invoice_id
727 AND APS.payment_num = AIRP.payment_num;
728
729 BEGIN
730 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.CREATE_SINGLE_PAYMENT<-' ||
731 P_calling_sequence;
732
733
734 l_log_msg := 'Open payments_cursor';
735 OPEN payments_cursor;
736
737 LOOP
738 l_log_msg := 'Fetch payments_cursor';
739 FETCH payments_cursor
740 INTO l_payment_num,
741 l_invoice_type,
742 l_invoice_num,
743 l_vendor_id,
744 l_vendor_site_id,
745 l_exclusive_payment_flag,
746 l_accts_pay_ccid,
747 l_amount_remaining,
748 l_discount_available,
749 l_attribute1,
750 l_attribute2,
751 l_attribute3,
752 l_attribute4,
753 l_attribute5,
754 l_attribute6,
755 l_attribute7,
756 l_attribute8,
757 l_attribute9,
758 l_attribute10,
759 l_attribute11,
760 l_attribute12,
761 l_attribute13,
762 l_attribute14,
763 l_attribute15,
764 l_attribute_category;
765
766 EXIT WHEN payments_cursor%NOTFOUND;
767
768 --
769 -- Calculate discount taken and amount
770 --
771 IF (P_take_discount = 'Y') THEN
772 l_discount_taken := l_discount_available;
773 ELSE
774 l_discount_taken := 0;
775 END IF;
776
777 l_amount := l_amount_remaining - l_discount_taken;
778
779 -- For pay-in-full payment_amount = amount_remaining
780
781 l_payment_amount := l_amount_remaining;
782
783 --
784 -- Get next invoice_payment_id
785 --
786 l_log_msg := 'Get next invoice_payment_id';
787 SELECT ap_invoice_payments_s.nextval
788 INTO l_invoice_payment_id
789 FROM sys.dual;
790
791 --
792 -- Bug: 661558
793 -- DO AUTOMATIC WITHHOLDING
794 --
795 declare
796 l_subject_amount NUMBER;
797 l_withholding_amount NUMBER;
798 l_awt_success VARCHAR2(2000);
799 l_include_discount VARCHAR2(1);
800 l_awt_flag VARCHAR2(1);
801 l_awt_invoices_exists VARCHAR2(1);
802 l_before_invoice_amount NUMBER;
803 l_inv_exchange_rate NUMBER;
804 l_pay_cross_rate NUMBER;
805 --5145239
806 l_awt_applied VARCHAR2(1);
807 l_create_awt_dists_type VARCHAR2(25);
808 l_create_awt_invoices_type VARCHAR2(25);
809 --Bug6660355 AWT PROJ
810 l_amount_payable NUMBER;
811 l_total_inv_amount NUMBER;
812 l_total_awt_amount NUMBER;
813 begin
814 IF (P_payment_type_flag = 'Q') THEN
815
816 l_log_msg := 'Get system parameter for tax withholding';
817
818 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
819 FND_LOG.STRING(G_LEVEL_PROCEDURE,
820 G_MODULE_NAME||'AP_PAY_IN_FULL_PKG.DO_AUTOMATIC_WITHHOLDING.begin',
821 l_log_msg);
822 END IF;
823
824 SELECT nvl(awt_include_discount_amt, 'N'),
825 nvl(allow_awt_flag, 'N'),
826 create_awt_dists_type,
827 create_awt_invoices_type
828
829 INTO l_include_discount,
830 l_awt_flag,
831 l_create_awt_dists_type, --5745239
832 l_create_awt_invoices_type
833 FROM ap_system_parameters;
834
835 begin
836 l_log_msg := 'Check if tax should be withheld from invoice:'||
837 to_char(P_invoice_id);
838 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
839 FND_LOG.STRING(G_LEVEL_PROCEDURE,
840 G_MODULE_NAME||'AP_PAY_IN_FULL_PKG.DO_AUTOMATIC_WITHHOLDING.begin',
841 l_log_msg);
842 END IF;
843 SELECT 'Y',awt_flag
844 INTO l_awt_invoices_exists,l_awt_applied --5745239
845 FROM ap_invoices AI
846 WHERE AI.invoice_id = p_invoice_id
847 AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
848 FROM ap_invoice_distributions AID1
849 WHERE AID1.invoice_id = AI.invoice_id
850 AND AID1.pay_awt_group_id is not null --Bug6660355
851 OR AID1.awt_group_id is not null) --Bug7685907
852 AND NOT EXISTS (SELECT 'Manual AWT lines exist'
853 FROM ap_invoice_distributions AID
854 WHERE AID.invoice_id = AI.invoice_id
855 AND AID.line_type_lookup_code = 'AWT'
856 AND AID.awt_flag in ('M', 'O'));
857
858 l_log_msg := 'Distributions Exists -- l_awt_invoices_exists -- '||l_awt_invoices_exists
859 ||' l_awt_applied -- '||l_awt_applied;
860 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
861 FND_LOG.STRING(G_LEVEL_PROCEDURE,
862 G_MODULE_NAME||'AP_PAY_IN_FULL_PKG.DO_AUTOMATIC_WITHHOLDING.begin',
863 l_log_msg);
864 END IF;
865 exception
866 when no_data_found then
867 l_awt_invoices_exists := 'N';
868 end;
869 --Bug6660355
870 SELECT sum(nvl(base_amount,amount))
871 INTO l_total_inv_amount
872 FROM ap_invoice_distributions
873 WHERE invoice_id =p_invoice_id
874 AND line_type_lookup_code not in('PREPAYMENT','AWT');
875
876 SELECT sum(nvl(aid.base_amount,aid.amount))
877 INTO l_total_awt_amount
878 FROM ap_invoice_distributions aid,ap_invoices ai
879 WHERE aid.invoice_id =p_invoice_id
880 AND aid.invoice_id = ai.invoice_id
881 AND aid.line_type_lookup_code in ('AWT')
882 AND aid.awt_origin_group_id = ai.awt_group_id;
883
884 l_amount_payable := l_total_inv_amount + nvl(l_total_awt_amount,0); --7022001
885
886 if l_awt_flag = 'Y' and
887 l_awt_invoices_exists = 'Y' then
888
889 --get invoice amount before withholding
890 l_log_msg := 'Get the invoice amount for awt, invoice_id:'||
891 to_char(P_invoice_id);
892
893 -- Bug 906732
894 -- Multiply the witholding subject amount by the exchange rate as the
895 -- witholding procedure expects the amount in base currency.
896
897 SELECT invoice_amount,exchange_rate,nvl(payment_cross_rate,1)
898 INTO l_before_invoice_amount,l_inv_exchange_rate,l_pay_cross_rate
899 FROM ap_invoices
900 WHERE invoice_id = p_invoice_id;
901
902 if (l_include_discount = 'Y') then
903 l_subject_amount := (((l_amount + l_discount_taken))
904 / l_pay_cross_rate) * nvl(l_inv_exchange_rate,1);
905 else
906 l_subject_amount := l_amount / l_pay_cross_rate
907 * nvl(l_inv_exchange_rate,1);
908 end if;
909 l_subject_amount := l_subject_amount * l_total_inv_amount/l_amount_payable; --Bug6660355
910 l_log_msg := 'Call the AP_DO_WITHHOLD procedure, invoice_id:'||
911 to_char(P_invoice_id);
912 --Bug5745239
913
914 l_log_msg := 'l_awt_applied -- '||nvl(l_awt_applied,'NULL')||' l_create_awt_dists_type -- '
915 ||l_create_awt_dists_type||' l_create_awt_invoices_type -- '||l_create_awt_invoices_type;
916 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
917 FND_LOG.STRING(G_LEVEL_PROCEDURE,
918 G_MODULE_NAME||'AP_PAY_IN_FULL_PKG.DO_AUTOMATIC_WITHHOLDING.begin',
919 l_log_msg);
920 END IF;
921
922 IF (nvl(l_awt_applied,'N') <> 'Y')
923 OR (l_create_awt_dists_type='APPROVAL' and l_create_awt_invoices_type ='PAYMENT')
924 OR (l_create_awt_dists_type ='BOTH') Then --Bug6660355
925
926 l_log_msg := 'call to AP_DO_WITHHOLDING with parameters -- p_invoice_id -- '||p_invoice_id
927 ||' p_calling_module -- QUICKCHECK '||'p_amount -- '||l_subject_amount;
928 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
929 FND_LOG.STRING(G_LEVEL_PROCEDURE,
930 G_MODULE_NAME||'AP_PAY_IN_FULL_PKG.DO_AUTOMATIC_WITHHOLDING.begin',
931 l_log_msg);
932 END IF;
933
934 AP_WITHHOLDING_PKG.AP_Do_Withholding
935 (P_Invoice_Id => p_invoice_id
936 ,P_Awt_Date => P_check_date
937 ,P_Calling_Module => 'QUICKCHECK'
938 ,P_Amount => l_subject_amount
939 ,P_Payment_Num => l_payment_num
940 ,P_Checkrun_Name => null
941 ,P_Last_Updated_By => p_last_updated_by
942 ,P_Last_Update_Login => p_last_update_login
943 ,P_Program_Application_Id => null
944 ,P_Program_Id => null
945 ,P_Request_Id => null
946 ,P_Awt_Success => l_awt_success
947 ,P_Invoice_Payment_ID => l_invoice_payment_id
948 );
949 End if;
950 if ((l_awt_success = 'SUCCESS') OR
951 (l_awt_success IS NULL)) then
952
953 -- get amount withheld for this particular invoice payment.
954 -- (this will be a negative number for STD inv and
955 -- positive for CM
956 SELECT nvl(sum(ap_utilities_pkg.ap_round_currency(
957 AID.amount * AI.payment_cross_rate,
958 AI.payment_currency_code)),0)
959 INTO l_withholding_amount
960 FROM ap_invoice_distributions AID,
961 ap_invoices AI
962 WHERE AID.awt_invoice_payment_id = l_invoice_payment_id
963 AND AID.invoice_id = AI.invoice_id;
964
965 l_amount := l_amount + l_withholding_amount;
966 l_amount_remaining := l_amount_remaining +
967 l_withholding_amount;
968 else
969 FND_MESSAGE.SET_NAME('SQLAP', 'AP_AWT_PROB_PLSQL');
970 FND_MESSAGE.SET_TOKEN('INVOICE', to_char(P_invoice_id));
971 FND_MESSAGE.SET_TOKEN('PROBLEM', l_awt_success );
972 APP_EXCEPTION.RAISE_EXCEPTION;
973 end if;
974
975 end if;
976
977 --END WITHHOLDING HANDLING
978 end if; -- quick check
979 end;
980
981 --
982 -- Calculate interest invoice amount
983 -- added below 'and' condition for 7612309/7668747
984 IF ((P_payment_type_flag = 'Q') AND (P_auto_calc_int_flag = 'Y')) THEN
985 l_log_msg := 'Calulate interest invoice amount for invoice_id:' ||
986 to_char(P_invoice_id) || ' payment_num:' ||
987 to_char(l_payment_num);
988 AP_INTEREST_INVOICE_PKG.AP_CALCULATE_INTEREST(
989 P_invoice_id,
990 P_sys_auto_calc_int_flag,
991 P_auto_calc_int_flag,
992 P_check_date,
993 l_payment_num,
994 --bug1905384 Interest should be calculated for payment amount
995 --including withholding amount.
996 l_payment_amount,
997 --l_amount_remaining,
998 l_discount_taken,
999 l_discount_available,
1000 P_currency_code,
1001 l_interest_amount,
1002 l_due_date,
1003 l_interest_invoice_num,
1004 l_payment_amount,
1005 l_curr_calling_sequence);
1006 ELSE
1007 l_interest_amount := 0;
1008 END IF;
1009
1010
1011 l_log_msg := 'Create invoice payment for invoice_id:' ||
1012 to_char(P_invoice_id) || ' payment_num:' ||
1013 to_char(l_payment_num);
1014
1015 AP_PAY_INVOICE_PKG.AP_PAY_INVOICE(
1016 P_invoice_id => P_invoice_id,
1017 P_check_id => P_check_id,
1018 P_payment_num => l_payment_num,
1019 P_invoice_payment_id => l_invoice_payment_id,
1020 P_old_invoice_payment_id => NULL,
1021 P_period_name => P_period_name,
1022 P_invoice_type => l_invoice_type,
1023 P_accounting_date => P_check_date,
1024 P_amount => l_amount,
1025 P_discount_taken => l_discount_taken,
1026 P_discount_lost => '',
1027 P_invoice_base_amount => '',
1028 P_payment_base_amount => '',
1029 P_accrual_posted_flag => 'N',
1030 P_cash_posted_flag => 'N',
1031 P_posted_flag => 'N',
1032 P_set_of_books_id => P_set_of_books_id,
1033 P_last_updated_by => P_last_updated_by,
1034 P_last_update_login => P_last_update_login,
1035 P_currency_code => P_currency_code,
1036 P_base_currency_code => P_base_currency_code,
1037 P_exchange_rate => P_exchange_rate,
1038 P_exchange_rate_type => P_exchange_rate_type,
1039 P_exchange_date => P_exchange_date,
1040 P_ce_bank_acct_use_id => P_ce_bank_acct_use_id,
1041 P_bank_account_num => P_bank_account_num,
1042 P_bank_account_type => P_bank_account_type,
1043 P_bank_num => P_bank_num,
1044 P_future_pay_posted_flag => l_future_pay_posted_flag,
1045 P_exclusive_payment_flag => l_exclusive_payment_flag,
1046 P_accts_pay_ccid => l_accts_pay_ccid,
1047 P_gain_ccid => '',
1048 P_loss_ccid => '',
1049 P_future_pay_ccid => P_future_pay_ccid,
1050 P_asset_ccid => NULL,
1051 P_payment_dists_flag => 'N',
1052 P_payment_mode => 'PAY',
1053 P_replace_flag => 'N',
1054 P_attribute1 => l_attribute1,
1055 P_attribute2 => l_attribute2,
1056 P_attribute3 => l_attribute3,
1057 P_attribute4 => l_attribute4,
1058 P_attribute5 => l_attribute5,
1059 P_attribute6 => l_attribute6,
1060 P_attribute7 => l_attribute7,
1061 P_attribute8 => l_attribute8,
1062 P_attribute9 => l_attribute9,
1063 P_attribute10 => l_attribute10,
1064 P_attribute11 => l_attribute11,
1065 P_attribute12 => l_attribute12,
1066 P_attribute13 => l_attribute13,
1067 P_attribute14 => l_attribute14,
1068 P_attribute15 => l_attribute15,
1069 P_attribute_category => l_attribute_category,
1070 P_calling_sequence => l_curr_calling_sequence,
1071 -- Events Project - 4 - Added following parameter
1072 P_accounting_event_id => P_accounting_event_id,
1073 P_org_id => P_org_id);
1074
1075 --Bug2993905 We will call events package to update the accounting
1076 --event id on awt distributions created during Payment time with
1077 --the payment time account event id after invoice payments have been
1078 --created.
1079 IF (p_payment_type_flag = 'Q') THEN
1080
1081 -- Events Project
1082 -- Bug 2751466
1083 -- This call is happening for the Pay-In-Full case,
1084 -- (also a type of Quick Payment)
1085 --
1086 -- This code will work ONLY when the the wiholding options
1087 -- are set to:-
1088 --
1089 -- o Apply Witholding Tax ==> At Payment Time
1090 -- o Create Witholding Invoice ==> At Payment Time
1091 --
1092 -- We want to stamp the Accounting_Event_ID for the Payment
1093 -- Event on all the AWT distributions that have been created
1094 -- as a result of this check.
1095
1096 AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
1097 (
1098 p_event_type => 'PAYMENT CREATED',
1099 p_check_id => p_check_id,
1100 p_event_id => p_accounting_event_id,
1101 p_calling_sequence => l_curr_calling_sequence
1102 );
1103
1104 END IF;
1105 --Bug2993905 End.
1106
1107 --
1108 -- Create interest invoice if QuickCheck
1109 -- Added below 'AND' condition for 7612309/7668747
1110 IF ((P_payment_type_flag = 'Q') AND (P_auto_calc_int_flag = 'Y'))THEN
1111
1112 --
1113 -- Get next interest invoice_id
1114 --
1115 l_log_msg := 'Get next interest invoice_id';
1116 SELECT ap_invoices_s.nextval
1117 INTO l_interest_invoice_id
1118 FROM sys.dual;
1119
1120 --
1121 -- Get next interest invoice_payment_id
1122 --
1123 l_log_msg := 'Get next interest invoice_payment_id';
1124 SELECT ap_invoice_payments_s.nextval
1125 INTO l_interest_invoice_pay_id
1126 FROM sys.dual;
1127
1128 --
1129 -- Create interest invoice
1130 --
1131 l_log_msg := 'Create interest invoice for invoice_id:' ||
1132 to_char(P_invoice_id) || ' payment_num:' ||
1133 to_char(l_payment_num);
1134
1135 --
1136 -- Bug: 622377
1137 -- This block will compose the Invoice Description field by retrieving the
1138 -- Due Date and Annual interest rate and the filling words...
1139 --
1140 DECLARE
1141 l_rate NUMBER;
1142 l_due_date DATE;
1143 l_int_invoice_days NUMBER;
1144 /* Datatype for following variables changed for MLS */
1145 l_nls_interest ap_lookup_codes.displayed_field%TYPE; -- **1**
1146 l_nls_days ap_lookup_codes.displayed_field%TYPE; -- **1**
1147 l_nls_percent ap_lookup_codes.displayed_field%TYPE; -- **1**
1148
1149 BEGIN
1150 -- Get the Translatable Words for filling the description
1151 SELECT l1.displayed_field,
1152 l2.displayed_field,
1153 l3.displayed_field
1154 INTO l_nls_interest,
1155 l_nls_days,
1156 l_nls_percent
1157 FROM ap_lookup_codes l1,
1158 ap_lookup_codes l2,
1159 ap_lookup_codes l3
1160 WHERE l1.lookup_type = 'NLS TRANSLATION'
1161 AND l1.lookup_code = 'INTEREST'
1162 AND l2.lookup_type = 'NLS TRANSLATION'
1163 AND l2.lookup_code = 'DAYS'
1164 AND l3.lookup_type = 'NLS TRANSLATION'
1165 AND l3.lookup_code = 'PERCENT';
1166
1167 IF l_interest_amount = 0 THEN
1168 l_invoice_description := '0 ' || l_nls_interest;
1169 ELSE
1170 BEGIN
1171 SELECT annual_interest_rate , due_date
1172 INTO l_rate, l_due_date
1173 FROM ap_payment_schedules, ap_interest_periods
1174 WHERE payment_num = l_payment_num
1175 AND invoice_id = P_invoice_id
1176 AND trunc(due_date+1) BETWEEN trunc(start_date) AND trunc(end_date);
1177
1178 l_int_invoice_days:=
1179 LEAST(TRUNC(P_check_date),ADD_MONTHS(TRUNC(l_due_date),12))
1180 - TRUNC(l_due_date);
1181
1182 l_invoice_description :=
1183 l_nls_interest|| ' ' || to_char(l_int_invoice_days) || ' ' || l_nls_days ||
1184 to_char(l_rate) || l_nls_percent;
1185
1186 EXCEPTION
1187 WHEN NO_DATA_FOUND then
1188 -- If no interest found, treat as ZERO interest
1189 l_invoice_description := '0 ' || l_nls_interest;
1190 END;
1191 END IF;
1192 END;
1193
1194
1195 --1724353, START OF CODE
1196
1197 BEGIN
1198
1199 IF P_sequential_numbering = 'A' and l_interest_amount > 0 THEN
1200
1201
1202 l_int_inv_doc_seq_v := FND_SEQNUM.GET_NEXT_SEQUENCE(
1203 APPID =>'200',
1204 CAT_CODE => 'INT INV',
1205 SOBID => P_set_of_books_id,
1206 MET_CODE => 'A',
1207 TRX_DATE => SYSDATE,
1208 DBSEQNM => l_int_inv_doc_seq_nm,
1209 DBSEQID => l_int_inv_doc_seq_id );
1210 END IF;
1211
1212 EXCEPTION
1213
1214 WHEN OTHERS THEN
1215
1216 IF (SQLCODE <> -20001) THEN
1217 FND_MESSAGE.SET_NAME('FND','UNIQUE-ALWAYS USED');
1218 END IF;
1219
1220 APP_EXCEPTION.RAISE_EXCEPTION;
1221 END;
1222
1223
1224 BEGIN
1225
1226 IF P_sequential_numbering = 'P' and l_interest_amount > 0 THEN
1227
1228 l_int_inv_doc_seq_v := FND_SEQNUM.GET_NEXT_SEQUENCE(
1229 APPID =>'200',
1230 CAT_CODE => 'INT INV',
1231 SOBID => P_set_of_books_id,
1232 MET_CODE => 'A',
1233 TRX_DATE => SYSDATE,
1234 DBSEQNM => l_int_inv_doc_seq_nm,
1235 DBSEQID => l_int_inv_doc_seq_id );
1236 END IF;
1237
1238 EXCEPTION
1239 when others then
1240 NULL;
1241
1242 END;
1243
1244 --1724353, END OF CODE
1245
1246
1247
1248
1249 AP_INTEREST_INVOICE_PKG.AP_CREATE_INTEREST_INVOICE(
1250 P_invoice_id => P_invoice_id,
1251 P_int_invoice_id => l_interest_invoice_id,
1252 P_check_id => P_check_id,
1253 P_payment_num => l_payment_num,
1254 P_int_invoice_payment_id => l_interest_invoice_pay_id,
1255 P_old_invoice_payment_id => NULL,
1256 P_period_name => P_period_name,
1257 P_invoice_type => l_invoice_type,
1258 P_accounting_date => P_check_date,
1259 P_amount => l_amount,
1260 P_discount_taken => l_discount_taken,
1261 P_discount_lost => '',
1262 P_invoice_base_amount => '',
1263 P_payment_base_amount => '',
1264 P_vendor_id => l_vendor_id,
1265 P_vendor_site_id => l_vendor_site_id,
1266 P_int_invoice_num => l_interest_invoice_num,
1267 P_old_invoice_num => l_invoice_num,
1268 P_interest_amount => l_interest_amount,
1269 P_payment_method_code => P_payment_method, --4552701
1270 P_doc_sequence_value => l_int_inv_doc_seq_v, --1724353
1271 P_doc_sequence_id => l_int_inv_doc_seq_id,--1724353
1272 P_checkrun_name => P_checkrun_name,
1273 P_payment_priority => '',
1274 P_accrual_posted_flag => 'N',
1275 P_cash_posted_flag => 'N',
1276 P_posted_flag => 'N',
1277 P_set_of_books_id => P_set_of_books_id,
1278 P_last_updated_by => P_last_updated_by,
1279 P_last_update_login => P_last_update_login,
1280 P_currency_code => P_currency_code,
1281 P_base_currency_code => P_base_currency_code,
1282 P_exchange_rate => P_exchange_rate,
1283 P_exchange_rate_type => P_exchange_rate_type,
1284 P_exchange_date => P_exchange_date,
1285 P_bank_account_id => P_ce_bank_acct_use_id,
1286 P_bank_account_num => P_bank_account_num,
1287 P_bank_account_type => P_bank_account_type,
1288 P_bank_num => P_bank_num,
1289 P_exclusive_payment_flag => l_exclusive_payment_flag,
1290 P_accts_pay_ccid => l_accts_pay_ccid,
1291 P_gain_ccid => '',
1292 P_loss_ccid => '',
1293 P_future_pay_ccid => P_future_pay_ccid,
1294 P_asset_ccid => NULL,
1295 P_payment_dists_flag => 'N',
1296 P_payment_mode => 'PAY',
1297 P_replace_flag => 'N',
1298 P_invoice_description => l_invoice_description,
1299 P_attribute1 => l_attribute1,
1300 P_attribute2 => l_attribute2,
1301 P_attribute3 => l_attribute3,
1302 P_attribute4 => l_attribute4,
1303 P_attribute5 => l_attribute5,
1304 P_attribute6 => l_attribute6,
1305 P_attribute7 => l_attribute7,
1306 P_attribute8 => l_attribute8,
1307 P_attribute9 => l_attribute9,
1308 P_attribute10 => l_attribute10,
1309 P_attribute11 => l_attribute11,
1310 P_attribute12 => l_attribute12,
1311 P_attribute13 => l_attribute13,
1312 P_attribute14 => l_attribute14,
1313 P_attribute15 => l_attribute15,
1314 P_attribute_category => l_attribute_category,
1315 P_calling_sequence => l_curr_calling_sequence,
1316 P_org_id => P_org_id, /* Bug 4742671 */
1317 P_accounting_event_id => P_accounting_event_id); --Events
1318
1319 END IF;
1320
1321 END LOOP;
1322
1323 l_log_msg := 'Close payments_cursor';
1324 CLOSE payments_cursor;
1325
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 IF (SQLCODE <> -20001) THEN
1330 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1331 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1332 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1333 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1334 ' P_invoice_id = ' || P_invoice_id ||
1335 ' P_payment_num = ' || P_payment_num ||
1336 ' P_check_id = ' || P_check_id ||
1337 ' P_payment_type_flag = ' || P_payment_type_flag ||
1338 ' P_payment_method = ' || P_payment_method ||
1339 ' P_bank_account_id = ' || P_ce_bank_acct_use_id ||
1340 ' P_bank_account_num = ' || P_bank_account_num ||
1341 ' P_bank_account_type = ' || P_bank_account_type ||
1342 ' P_bank_num = ' || P_bank_num ||
1343 ' P_check_date = ' || P_check_date ||
1344 ' P_period_name = ' || P_period_name ||
1345 ' P_currency_code = ' || P_currency_code ||
1346 ' P_base_currency_code = ' || P_base_currency_code ||
1347 ' P_checkrun_name = ' || P_checkrun_name ||
1348 ' P_doc_sequence_value = ' || P_doc_sequence_value ||
1349 ' P_doc_sequence_id = ' || P_doc_sequence_id ||
1350 ' P_exchange_rate = ' || P_exchange_rate ||
1351 ' P_exchange_rate_type = ' || P_exchange_rate_type ||
1352 ' P_exchange_date = ' || P_exchange_date ||
1353 ' P_take_discount = ' || P_take_discount ||
1354 ' P_sys_auto_calc_int_flag = ' || P_sys_auto_calc_int_flag||
1355 ' P_auto_calc_int_flag = ' || P_auto_calc_int_flag ||
1356 ' P_set_of_books_id = ' || P_set_of_books_id ||
1357 ' P_future_pay_ccid = ' || P_future_pay_ccid ||
1358 ' P_last_updated_by = ' || P_last_updated_by ||
1359 ' P_last_update_login = ' || P_last_update_login
1360 );
1361 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
1362 END IF;
1363 APP_EXCEPTION.RAISE_EXCEPTION;
1364 END Create_Single_Payment;
1365
1366
1367 ---------------------------------------------------------------------
1368 -- Procedure AP_Create_Payments create an invoice payment for all payable
1369 -- payment schedules belonging to invoices in P_invoice_id_list and
1370 -- related interest invoices if Quickcheck.
1371 --
1372 PROCEDURE AP_Create_Payments(P_invoice_id_list IN VARCHAR2,
1373 P_payment_num_list IN VARCHAR2,
1374 P_check_id IN NUMBER,
1375 P_payment_type_flag IN VARCHAR2,
1376 P_payment_method IN VARCHAR2,
1377 P_ce_bank_acct_use_id IN NUMBER,
1378 P_bank_account_num IN VARCHAR2,
1379 P_bank_account_type IN VARCHAR2,
1380 P_bank_num IN VARCHAR2,
1381 P_check_date IN DATE,
1382 P_period_name IN VARCHAR2,
1383 P_currency_code IN VARCHAR2,
1384 P_base_currency_code IN VARCHAR2,
1385 P_checkrun_name IN VARCHAR2,
1386 P_doc_sequence_value IN NUMBER,
1387 P_doc_sequence_id IN NUMBER,
1388 P_exchange_rate IN NUMBER,
1389 P_exchange_rate_type IN VARCHAR2,
1390 P_exchange_date IN DATE,
1391 P_take_discount IN VARCHAR2,
1392 P_sys_auto_calc_int_flag IN VARCHAR2,
1393 P_auto_calc_int_flag IN VARCHAR2,
1394 P_set_of_books_id IN NUMBER,
1395 P_future_pay_ccid IN NUMBER,
1396 P_last_updated_by IN NUMBER,
1397 P_last_update_login IN NUMBER,
1398 P_calling_sequence IN VARCHAR2,
1399 P_sequential_numbering IN VARCHAR2 DEFAULT 'N', -- 1724353
1400 P_accounting_event_id IN NUMBER, -- Events
1401 P_org_id IN NUMBER)
1402 IS
1403 l_invoice_id NUMBER;
1404 l_payment_num NUMBER;
1405 l_pos NUMBER;
1406 l_next NUMBER;
1407 l_log_msg VARCHAR2(240);
1408 l_curr_calling_sequence VARCHAR2(2000);
1409
1410 BEGIN
1411 l_curr_calling_sequence := 'AP_PAY_IN_FULL_PKG.AP_CREATE_PAYMENTS<-' ||
1412 P_calling_sequence;
1413 l_pos := 1;
1414
1415 IF (P_payment_num_list IS NOT NULL) THEN
1416
1417 l_invoice_id := TO_NUMBER(P_invoice_id_list);
1418 --
1419 -- Parse P_payment_num_list
1420 --
1421 LOOP
1422 l_next := INSTR(P_payment_num_list, ' ', l_pos);
1423 IF (l_next = 0) THEN
1424 l_next := LENGTH(P_payment_num_list) + 1;
1425 END IF;
1426
1427 l_payment_num := TO_NUMBER(SUBSTR(P_payment_num_list,
1428 l_pos,
1429 l_next - l_pos));
1430
1431 Create_Single_Payment(l_invoice_id,
1432 l_payment_num,
1433 P_check_id,
1434 P_payment_type_flag,
1435 P_payment_method,
1436 P_ce_bank_acct_use_id,
1437 P_bank_account_num,
1438 P_bank_account_type,
1439 P_bank_num,
1440 P_check_date,
1441 P_period_name,
1442 P_currency_code,
1443 P_base_currency_code,
1444 P_checkrun_name,
1445 P_doc_sequence_value,
1446 P_doc_sequence_id,
1447 P_exchange_rate,
1448 P_exchange_rate_type,
1449 P_exchange_date,
1450 P_take_discount,
1451 P_sys_auto_calc_int_flag,
1452 P_auto_calc_int_flag,
1453 P_set_of_books_id,
1454 P_future_pay_ccid,
1455 P_last_updated_by,
1456 P_last_update_login,
1457 l_curr_calling_sequence,
1458 P_sequential_numbering, -- 1724353
1459 P_accounting_event_id, -- Events Project
1460 P_org_id);
1461
1462 EXIT WHEN (l_next > LENGTH(P_payment_num_list));
1463 l_pos := l_next + 1;
1464
1465 END LOOP;
1466
1467 ELSIF (P_invoice_id_list IS NOT NULL) THEN
1468 --
1469 -- Parse P_invoice_id_list
1470 --
1471 LOOP
1472 l_next := INSTR(P_invoice_id_list, ' ', l_pos);
1473 IF (l_next = 0) THEN
1474 l_next := LENGTH(P_invoice_id_list) + 1;
1475 END IF;
1476
1477 l_invoice_id := TO_NUMBER(SUBSTR(P_invoice_id_list,
1478 l_pos,
1479 l_next - l_pos));
1480
1481 l_log_msg := 'Get discount available for invoice_id:' ||
1482 to_char(l_invoice_id);
1483
1484 Create_Single_Payment(l_invoice_id,
1485 NULL,
1486 P_check_id,
1487 P_payment_type_flag,
1488 P_payment_method,
1489 P_ce_bank_acct_use_id,
1490 P_bank_account_num,
1491 P_bank_account_type,
1492 P_bank_num,
1493 P_check_date,
1494 P_period_name,
1495 P_currency_code,
1496 P_base_currency_code,
1497 P_checkrun_name,
1498 P_doc_sequence_value,
1499 P_doc_sequence_id,
1500 P_exchange_rate,
1501 P_exchange_rate_type,
1502 P_exchange_date,
1503 P_take_discount,
1504 P_sys_auto_calc_int_flag,
1505 P_auto_calc_int_flag,
1506 P_set_of_books_id,
1507 P_future_pay_ccid,
1508 P_last_updated_by,
1509 P_last_update_login,
1510 l_curr_calling_sequence,
1511 P_sequential_numbering,
1512 P_accounting_event_id, -- Events Project
1513 P_org_id);
1514
1515 EXIT WHEN (l_next > LENGTH(P_invoice_id_list));
1516 l_pos := l_next + 1;
1517
1518 END LOOP;
1519
1520 END IF;
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 IF (SQLCODE <> -20001) THEN
1525 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1526 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1527 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1528 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1529 ' P_invoice_id_list = ' || P_invoice_id_list ||
1530 ' P_payment_num_list = ' || P_payment_num_list ||
1531 ' P_check_id = ' || P_check_id ||
1532 ' P_payment_type_flag = ' || P_payment_type_flag ||
1533 ' P_payment_method = ' || P_payment_method ||
1534 ' P_bank_account_id = ' || P_ce_bank_acct_use_id ||
1535 ' P_bank_account_num = ' || P_bank_account_num ||
1536 ' P_bank_account_type = ' || P_bank_account_type ||
1537 ' P_bank_num = ' || P_bank_num ||
1538 ' P_check_date = ' || P_check_date ||
1539 ' P_period_name = ' || P_period_name ||
1540 ' P_currency_code = ' || P_currency_code ||
1541 ' P_base_currency_code = ' || P_base_currency_code ||
1542 ' P_checkrun_name = ' || P_checkrun_name ||
1543 ' P_doc_sequence_value = ' || P_doc_sequence_value ||
1544 ' P_doc_sequence_id = ' || P_doc_sequence_id ||
1545 ' P_exchange_rate = ' || P_exchange_rate ||
1546 ' P_exchange_rate_type = ' || P_exchange_rate_type ||
1547 ' P_exchange_date = ' || P_exchange_date ||
1548 ' P_take_discount = ' || P_take_discount ||
1549 ' P_sys_auto_calc_int_flag = ' || P_sys_auto_calc_int_flag||
1550 ' P_auto_calc_int_flag = ' || P_auto_calc_int_flag ||
1551 ' P_set_of_books_id = ' || P_set_of_books_id ||
1552 ' P_future_pay_ccid = ' || P_future_pay_ccid ||
1553 ' P_last_updated_by = ' || P_last_updated_by ||
1554 ' P_last_update_login = ' || P_last_update_login
1555 );
1556 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
1557 END IF;
1558 APP_EXCEPTION.RAISE_EXCEPTION;
1559 END AP_Create_Payments;
1560
1561 END AP_PAY_IN_FULL_PKG;