[Home] [Help]
PACKAGE BODY: APPS.AP_AUTO_PAYMENT_PKG
Source
1 PACKAGE BODY AP_AUTO_PAYMENT_PKG AS
2 /* $Header: apautopb.pls 120.19.12020000.4 2013/01/31 00:36:24 tjbhatt ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'AP.PLSQL.AP_AUTO_PAYMENT_PKG.';
12
13 --Bugfix 2124107 - Add one more parameter p_last_update_login
14
15 PROCEDURE Replace_Check
16 (P_Old_Check_Id IN NUMBER
17 ,P_Replace_Check_Id IN OUT NOCOPY NUMBER
18 ,P_Replace_Check_Date IN DATE
19 ,P_Replace_Period_Name IN VARCHAR2
20 ,P_Replace_Check_Num IN NUMBER
21 ,P_Replace_Voucher_Num IN NUMBER
22 ,P_Orig_Amount IN NUMBER
23 ,P_Orig_payment_Date IN DATE
24 ,P_Last_Updated_By IN NUMBER
25 ,P_Future_Pay_Ccid IN NUMBER
26 ,P_Quickcheck_Id IN VARCHAR2
27 ,P_Calling_Sequence IN VARCHAR2
28 ,P_Last_Update_Login IN NUMBER DEFAULT NULL
29 ,P_Remit_to_supplier_name IN VARCHAR2 DEFAULT NULL -- Added for bug 8218410
30 ,P_Remit_to_supplier_id IN Number DEFAULT NULL
31 ,P_Remit_To_Supplier_Site IN VARCHAR2 DEFAULT NULL
32 ,P_Remit_To_Supplier_Site_Id IN NUMBER DEFAULT NULL
33 ,P_Relationship_Id IN NUMBER DEFAULT NULL -- Bug 8218410 ends
34 )
35
36 IS
37 -------------------------------------------------------------------
38 -- Cursor to insert new invoice payments for replacement check
39 --
40
41 -- Bug#590200: The invoice and payment base amounts should get
42 -- populated if either invoice or payment currency is different
43 -- than the base currency. Since this has been implemented for
44 -- creating the invoice payments, we can assume that the original
45 -- check's invoice payments are correct. Therefore, all we need to do
46 -- here is:
47 -- If payment currency = base currency then
48 -- copy from old invoice payment (will be NULL or populated
49 -- based on invoice currency)
50 -- else calculate using exchange rate for new check.
51
52 CURSOR c_new_payments IS
53 SELECT ap_invoice_payments_s.nextval new_invoice_payment_id
54 , AIP.invoice_id invoice_id
55 , AIP.payment_num payment_num
56 , NVL(AIP.amount,0) amount
57 , AIP.set_of_books_id set_of_books_id
58 , AIP.accts_pay_code_combination_id accts_pay_code_combination_id
59 , NVL(AIP.discount_taken,0) discount_taken
60 , NVL(AIP.discount_lost,0) discount_lost
61 , AC.exchange_rate_type exchange_rate_type
62 , AC.exchange_rate exchange_rate
63 , AIP.invoice_base_amount invoice_base_amount
64 , AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
65 decode(AC.currency_code, ASP.base_currency_code,
66 AIP.payment_base_amount,
67 (AIP.amount * AC.exchange_rate)),
68 ASP.base_currency_code) payment_base_amount
69 , AIP.gain_code_combination_id gain_code_combination_id
70 , AIP.loss_code_combination_id loss_code_combination_id
71 --Bug 2631799 Added Attributes for Payments Information and Invoices DFF
72 , ASP.awt_include_discount_amt awt_include_discount_amt --bug 3309344
73 , AC.attribute1
74 , AC.attribute2
75 , AC.attribute3
76 , AC.attribute4
77 , AC.attribute5
78 , AC.attribute6
79 , AC.attribute7
80 , AC.attribute8
81 , AC.attribute9
82 , AC.attribute10
83 , AC.attribute11
84 , AC.attribute12
85 , AC.attribute13
86 , AC.attribute14
87 , AC.attribute15
88 , AC.attribute_category
89 , AC.global_attribute1
90 , AC.global_attribute2
91 , AC.global_attribute3
92 , AC.global_attribute4
93 , AC.global_attribute5
94 , AC.global_attribute6
95 , AC.global_attribute7
96 , AC.global_attribute8
97 , AC.global_attribute9
98 , AC.global_attribute10
99 , AC.global_attribute11
100 , AC.global_attribute12
101 , AC.global_attribute13
102 , AC.global_attribute14
103 , AC.global_attribute15
104 , AC.global_attribute16
105 , AC.global_attribute17
106 , AC.global_attribute18
107 , AC.global_attribute19
108 , AC.global_attribute20
109 , AC.global_attribute_category
110 , AC.org_id /* Bug 4759178. Added org_id */
111 FROM ap_checks AC
112 , ap_invoice_payments AIP
113 , ap_payment_schedules APS
114 , ap_system_parameters ASP
115 WHERE AC.check_id = P_Old_Check_Id
116 AND AIP.check_id = AC.check_id
117 AND AIP.invoice_id = APS.invoice_id
118 AND AIP.payment_num = APS.payment_num
119 AND AIP.reversal_inv_pmt_id is NULL;
120
121 rec_new_payments c_new_payments%ROWTYPE;
122 l_debug_info VARCHAR2(240);
123 l_curr_calling_sequence VARCHAR2(2000);
124 l_doc_sequence_name fnd_document_sequences.name%TYPE;
125 l_doc_sequence_id ap_checks.doc_sequence_id%TYPE;
126 l_doc_sequence_value ap_checks.doc_sequence_value%TYPE;
127 l_doc_category_code ap_checks.doc_category_code%TYPE;
128 l_set_of_books_id ap_system_parameters.set_of_books_id%TYPE;
129 l_awt_success VARCHAR2(2000);
130 l_awt_gross_amount NUMBER;
131 l_accounting_event_id NUMBER; --Events Project 1
132 l_prev_withheld_amt NUMBER; --bug3309344
133 l_prev_amt_paid NUMBER; --bug3309344
134 l_payment_type_flag AP_CHECKS.payment_type_flag%TYPE; -- Bug3343314
135 l_amount AP_CHECKS.amount%TYPE; -- Bug3343314
136 l_currency_code AP_CHECKS.currency_code%TYPE; -- Bug3343314
137 l_exchange_rate_type AP_CHECKS.exchange_rate_type%TYPE; -- Bug3343314
138 l_exchange_date AP_CHECKS.exchange_date%TYPE; -- Bug3343314
139 l_exchange_rate AP_CHECKS.exchange_rate%TYPE; -- Bug3343314
140 l_base_amount AP_CHECKS.exchange_rate%TYPE; -- Bug3343314
141 l_creation_date AP_CHECKS.creation_date%TYPE; -- Bug3343314
142 l_created_by AP_CHECKS.created_by%TYPE; -- Bug3343314
143 l_last_update_date AP_CHECKS.last_update_date%TYPE; -- Bug3343314
144 l_last_updated_by AP_CHECKS.last_updated_by%TYPE; -- Bug3343314
145 l_last_update_login AP_CHECKS.last_update_login%TYPE; -- Bug3343314
146 l_org_id NUMBER;
147 l_transaction_type AP_PAYMENT_HISTORY_ALL.transaction_type%TYPE;
148
149 --bug14308871, added below three variables
150 l_invoice_amount number;
151 l_validation_awt_amount number;
152 l_amount_payable number;
153
154 BEGIN
155
156 l_curr_calling_sequence := 'AP_AUTO_PAYMENT_PKG.REPLACE_CHECK<-'||
157 P_Calling_Sequence;
158
159 -- Added call to get new voucher number Bug #510855
160
161 l_debug_info := 'Selecting Category Code and SOB';
162 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
164 END IF;
165
166
167 -- Fix for bug 547662
168 -- We need to handle exception when no_data_found
169 BEGIN
170
171
172 SELECT ac.doc_category_code, aip.set_of_books_id
173 INTO l_doc_category_code, l_set_of_books_id
174 FROM ap_checks ac, ap_invoice_payments aip
175 WHERE AC.check_id = P_old_check_id
176 AND AC.check_id = AIP.check_id
177 AND AC.doc_sequence_value IS NOT NULL
178 AND rownum = 1;
179
180 EXCEPTION WHEN NO_DATA_FOUND Then
181 -- If doc_sequence_value is null
182 -- we should handle the exception
183 l_doc_category_code := null;
184 END;
185
186
187
188 IF l_doc_category_code IS NOT NULL THEN
189 l_doc_sequence_value := FND_SEQNUM.GET_NEXT_SEQUENCE(
190 APPID =>'200',
191 CAT_CODE => l_doc_category_code,
192 SOBID => l_set_of_books_id,
193 MET_CODE => 'M',
194 TRX_DATE => SYSDATE,
195 DBSEQNM => l_doc_sequence_name,
196 DBSEQID => l_doc_sequence_id );
197 END IF;
198 --
199 -------------------------------------------------------------------
200 l_debug_info := 'Get replace_check_id';
201 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
202 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
203 END IF;
204
205
206 SELECT ap_checks_s.nextval
207 INTO P_Replace_Check_Id
208 FROM dual;
209
210 -------------------------------------------------------------------
211 l_debug_info := 'Insert into ap_checks for replace_check_id';
212 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
213 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
214 END IF;
215
216 /* Bug 4759178. Added Org_id */
217 INSERT INTO AP_CHECKS
218 (CHECK_ID, CE_BANK_ACCT_USE_ID, BANK_ACCOUNT_NAME,
219 AMOUNT, CHECK_NUMBER, CHECK_DATE, CURRENCY_CODE,
220 LAST_UPDATE_DATE, LAST_UPDATED_BY, VENDOR_ID, VENDOR_NAME,
221 VENDOR_SITE_ID, VENDOR_SITE_CODE, EXCHANGE_RATE, EXCHANGE_DATE,
222 EXCHANGE_RATE_TYPE, BASE_AMOUNT, CHECK_FORMAT_ID, CLEARED_DATE,
223 CLEARED_AMOUNT, VOID_DATE, STATUS_LOOKUP_CODE, CHECK_STOCK_ID,
224 CHECKRUN_NAME, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3,
225 ADDRESS_LINE4, COUNTY, CITY, STATE, ZIP, PROVINCE, COUNTRY,
226 WITHHOLDING_STATUS_LOOKUP_CODE, PAYMENT_TYPE_FLAG,
227 CHECK_VOUCHER_NUM, PAYMENT_METHOD_CODE, --4552701
228 DOC_SEQUENCE_VALUE,DOC_CATEGORY_CODE,DOC_SEQUENCE_ID,
229 CREATION_DATE, CREATED_BY,
230 --Bug2631799 Added Attributes
231 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
232 ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
233 ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
234 ATTRIBUTE_CATEGORY,
235 GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,
236 GLOBAL_ATTRIBUTE4,GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,
237 GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,GLOBAL_ATTRIBUTE9,
238 GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,
239 GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,
240 GLOBAL_ATTRIBUTE16,GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,
241 GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,GLOBAL_ATTRIBUTE_CATEGORY, ORG_ID,
242 BANK_CHARGE_BEARER, SETTLEMENT_PRIORITY, PAYMENT_PROFILE_ID, /* Bug 4759178 */
243 PAYMENT_DOCUMENT_ID, PARTY_ID, PARTY_SITE_ID, LEGAL_ENTITY_ID,
244 REMIT_TO_SUPPLIER_NAME, --Added for bug 8218410
245 REMIT_TO_SUPPLIER_ID,
246 REMIT_TO_SUPPLIER_SITE,
247 REMIT_TO_SUPPLIER_SITE_ID,
248 RELATIONSHIP_ID
249 /* Added for bug 12773903 */
250 ,EXTERNAL_BANK_ACCOUNT_ID) -- Bug 8218410 ends
251 SELECT P_Replace_Check_Id, AC.ce_bank_acct_use_id, AC.bank_account_name,
252 AC.amount, P_Replace_Check_Num, P_Replace_Check_Date,
253 AC.currency_code, sysdate, P_Last_Updated_By, AC.vendor_id,
254 AC.vendor_name, AC.vendor_site_id, AC.vendor_site_code,
255 AC.exchange_rate, AC.exchange_date, AC.exchange_rate_type,
256 AC.base_amount, AC.check_format_id, NULL, NULL, NULL,
257 AC.status_lookup_code, AC.check_stock_id,
258 substr(P_Quickcheck_Id,1,30-length(to_char(P_Replace_Check_Id)))||
259 to_char(P_Replace_Check_Id),
260 AC.address_line1, AC.address_line2, AC.address_line3,
261 AC.address_line4, AC.county, AC.city, AC.state, AC.zip,
262 AC.province, AC.country, AC.withholding_status_lookup_code, 'Q',
263 P_Replace_Voucher_Num, AC.payment_method_code,
264 l_doc_sequence_value, AC.doc_category_code, AC.doc_sequence_id,
265 sysdate, P_Last_Updated_By,
266 --Bug 2631799 Added attributes
267 AC.ATTRIBUTE1,AC.ATTRIBUTE2,AC.ATTRIBUTE3,AC.ATTRIBUTE4,
268 AC.ATTRIBUTE5,AC.ATTRIBUTE6,AC.ATTRIBUTE7,AC.ATTRIBUTE8,
269 AC.ATTRIBUTE9,AC.ATTRIBUTE10,AC.ATTRIBUTE11,AC.ATTRIBUTE12,
270 AC.ATTRIBUTE13,AC.ATTRIBUTE14,AC.ATTRIBUTE15,
271 AC.ATTRIBUTE_CATEGORY,
272 AC.GLOBAL_ATTRIBUTE1,AC.GLOBAL_ATTRIBUTE2,AC.GLOBAL_ATTRIBUTE3,
273 AC.GLOBAL_ATTRIBUTE4,AC.GLOBAL_ATTRIBUTE5,AC.GLOBAL_ATTRIBUTE6,
274 AC.GLOBAL_ATTRIBUTE7,AC.GLOBAL_ATTRIBUTE8,AC.GLOBAL_ATTRIBUTE9,
275 AC.GLOBAL_ATTRIBUTE10,AC.GLOBAL_ATTRIBUTE11,AC.GLOBAL_ATTRIBUTE12,
276 AC.GLOBAL_ATTRIBUTE13,AC.GLOBAL_ATTRIBUTE14,AC.GLOBAL_ATTRIBUTE15,
277 AC.GLOBAL_ATTRIBUTE16,AC.GLOBAL_ATTRIBUTE17,AC.GLOBAL_ATTRIBUTE18,
278 AC.GLOBAL_ATTRIBUTE19,AC.GLOBAL_ATTRIBUTE20,
279 AC.GLOBAL_ATTRIBUTE_CATEGORY, AC.ORG_ID,
280 AC.bank_charge_bearer, AC.settlement_priority, AC.payment_profile_id,
281 AC.payment_document_id, AC.party_id, AC.party_site_id, AC.legal_entity_id,
282 AC.REMIT_TO_SUPPLIER_NAME,AC.REMIT_TO_SUPPLIER_ID,AC.REMIT_TO_SUPPLIER_SITE, --Added for bug 8218410
283 AC.REMIT_TO_SUPPLIER_SITE_ID,AC.RELATIONSHIP_ID -- bug 8218410 ends
284 ,AC.EXTERNAL_BANK_ACCOUNT_ID /* Added for bug 12773903 */
285 FROM ap_checks AC
286 WHERE AC.check_id = P_old_check_id;
287
288 -- Bug3343314
289 SELECT payment_type_flag,
290 amount,
291 currency_code,
292 exchange_rate_type,
293 exchange_date,
294 exchange_rate,
295 base_amount,
296 creation_date,
297 created_by,
298 last_update_date,
299 last_updated_by,
300 last_update_login,
301 org_id
302 INTO l_payment_type_flag,
303 l_amount,
304 l_currency_code,
305 l_exchange_rate_type,
306 l_exchange_date,
307 l_exchange_rate,
308 l_base_amount,
309 l_creation_date,
310 l_created_by,
311 l_last_update_date,
312 l_last_updated_by,
313 l_last_update_login,
314 l_org_id
315 FROM ap_checks
316 WHERE check_id = p_replace_check_id;
317
318 AP_ACCOUNTING_EVENTS_PKG.create_events
319 (
320 p_event_type => 'PAYMENT',
321 p_doc_type => l_payment_type_flag, -- Bug3343314
322 p_doc_id => p_replace_check_id,
323 p_accounting_date => p_replace_check_date,
324 p_accounting_event_id => l_accounting_event_id, -- OUT
325 p_checkrun_name => NULL,
326 p_calling_sequence => l_curr_calling_sequence
327 );
328
329 IF ( l_payment_type_flag = 'R' ) THEN
330 l_transaction_type := 'REFUND RECORDED';
331 ELSE
332 l_transaction_type := 'PAYMENT CREATED';
333 END IF;
334
335 -- Bug3343314
336 AP_RECONCILIATION_PKG.insert_payment_history
337 (
338 x_check_id => p_replace_check_id,
339 x_transaction_type => l_transaction_type,
340 x_accounting_date => p_replace_check_date,
341 x_trx_bank_amount => NULL,
342 x_errors_bank_amount => NULL,
343 x_charges_bank_amount => NULL,
344 x_bank_currency_code => NULL,
345 x_bank_to_base_xrate_type => NULL,
346 x_bank_to_base_xrate_date => NULL,
347 x_bank_to_base_xrate => NULL,
348 x_trx_pmt_amount => l_amount,
349 x_errors_pmt_amount => NULL,
350 x_charges_pmt_amount => NULL,
351 x_pmt_currency_code => l_currency_code,
352 x_pmt_to_base_xrate_type => l_exchange_rate_type,
353 x_pmt_to_base_xrate_date => l_exchange_date,
354 x_pmt_to_base_xrate => l_exchange_rate,
355 x_trx_base_amount => l_base_amount,
356 x_errors_base_amount => NULL,
357 x_charges_base_amount => NULL,
358 x_matched_flag => NULL,
359 x_rev_pmt_hist_id => NULL,
360 x_org_id => l_org_id,
361 x_creation_date => l_creation_date,
362 x_created_by => l_created_by,
363 x_last_update_date => l_last_update_date,
364 x_last_updated_by => l_last_updated_by,
365 x_last_update_login => l_last_update_login,
366 x_program_update_date => NULL,
367 x_program_application_id => NULL,
368 x_program_id => NULL,
369 x_request_id => NULL,
370 x_calling_sequence => l_curr_calling_sequence,
371 x_accounting_event_id => l_accounting_event_id
372 );
373
374 -------------------------------------------------------------------
375 l_debug_info := 'Update ap_check_stocks';
376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
377 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
378 END IF;
379
380 UPDATE ap_check_stocks
381 SET last_document_num = P_Replace_Check_Num,
382 last_update_date = sysdate,
383 last_updated_by = P_Last_Updated_By
384 WHERE check_stock_id =
385 (SELECT check_stock_id
386 FROM ap_checks
387 WHERE check_id = P_Replace_Check_Id);
388
389 -------------------------------------------------------------------
390 l_debug_info := 'Open c_new_payments cursor';
391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
392 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
393 END IF;
394
395 OPEN c_new_payments;
396
397 LOOP
398 ---------------------------------------------------------------
399 l_debug_info := 'Fetch from c_new_payments cursor';
400 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
402 END IF;
403
404 FETCH c_new_payments INTO rec_new_payments;
405 EXIT WHEN c_new_payments%NOTFOUND;
406
407 ---------------------------------------------------------------
408 -- Bug 1492588 : Process Withholding
409 --
410 IF OK_To_Call_Withholding (rec_new_payments.invoice_id)
411 THEN
412
413 -- bug3309344 added the following 2 selects
414
415 -- BUG 4121323 : selecting payment_base_amount
416 select sum(nvl(payment_base_amount,amount)+decode(rec_new_payments.awt_include_discount_amt,
417 'Y',nvl(discount_taken,0),0))
418 into l_prev_amt_paid
419 from ap_invoice_payments aip
420 where aip.reversal_inv_pmt_id is null
421 and aip.invoice_id = rec_new_payments.invoice_id
422 and aip.check_id= p_old_check_id;
423
424 l_debug_info := 'l_prev_amt_paid -- '||to_char(l_prev_amt_paid);
425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
426 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
427 END IF;
428
429
430 -- BUG 4121323 : selecting base_amount
431 select sum(nvl(aid.base_amount,aid.amount))
432 into l_prev_withheld_amt
433 from ap_invoice_distributions aid
434 where aid.invoice_id=rec_new_payments.invoice_id
435 and aid.awt_invoice_payment_id
436 in (select invoice_payment_id
437 from ap_invoice_payments aip
438 where aip.check_id=p_old_check_id
439 and aip.reversal_inv_pmt_id is null
440 and aip.invoice_id=rec_new_payments.invoice_id);
441
442 l_debug_info := 'l_prev_withheld_amt -- '||to_char(l_prev_withheld_amt);
443 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
445 END IF;
446
447
448 l_awt_gross_amount:=l_prev_amt_paid-l_prev_withheld_amt;
449
450 --begin bug14308871
451
452 select
453 sum(nvl(aid.base_amount,aid.amount))
454 into l_invoice_amount
455 from ap_invoice_distributions_all aid
456 where invoice_id = rec_new_payments.invoice_id
457 and line_type_lookup_code not in ('AWT');
458
459 select sum(nvl(aid.base_amount,aid.amount))
460 into l_validation_awt_amount
461 from ap_invoice_distributions_all aid
462 where invoice_id = rec_new_payments.invoice_id
463 and line_type_lookup_code = 'AWT'
464 and awt_invoice_payment_id is null;
465
466 l_amount_payable := l_invoice_amount + nvl(l_validation_awt_amount,0);
467
468 if(nvl(l_amount_payable,0)<>0) then
469 l_awt_gross_amount := l_awt_gross_amount * l_invoice_amount / l_amount_payable;
470 end if;
471
472 --end bug14308871
473
474 l_debug_info := 'l_awt_gross_amount -- '||to_char(l_awt_gross_amount);
475 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
476 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
477 END IF;
478
479
480 /* Bug 3309344 commenting this select statement
481 SELECT MAX(AID.awt_gross_amount)
482 INTO l_awt_gross_amount
483 FROM AP_INVOICE_PAYMENTS AIP,
484 AP_INVOICE_DISTRIBUTIONS AID
485 WHERE AIP.invoice_id = rec_new_payments.invoice_id
486 AND AIP.check_id = p_old_check_id
487 AND AIP.reversal_inv_pmt_id IS NULL
488 AND AID.awt_invoice_payment_id = AIP.invoice_payment_id;
489 */
490 -- Bugfix 2124107 - Pass p_last_update_login instead of null to procedure
491
492 l_debug_info := 'calling AP_DO_WITHHOLDING';
493 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
494 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
495 END IF;
496
497
498 AP_WITHHOLDING_PKG.AP_Do_Withholding(
499 P_Invoice_Id => rec_new_payments.invoice_id ,
500 P_AWT_Date => P_Replace_Check_Date ,
501 P_Calling_Module => 'QUICKCHECK' ,
502 P_Amount => l_awt_gross_amount ,
503 P_Payment_Num => rec_new_payments.payment_num ,
504 P_Checkrun_Name => null ,
505 P_Last_Updated_By => p_last_updated_by ,
506 P_Last_Update_Login => p_last_update_login ,
507 -- P_Last_Update_Login => null ,
508 P_Program_Application_id => null ,
509 P_Program_Id => null ,
510 P_Request_Id => null ,
511 P_Awt_Success => l_awt_success ,
512 P_Invoice_Payment_Id => rec_new_payments.new_invoice_payment_id,
513 P_Check_Id => P_Replace_Check_Id); --bug 8735998
514 END IF;
515
516 ---------------------------------------------------------------
517 -- Create new invoice payment for replacement check
518 --
519 AP_PAY_INVOICE_PKG.AP_PAY_INVOICE
520 (rec_new_payments.invoice_id
521 ,P_Replace_Check_Id
522 ,rec_new_payments.payment_num
523 ,rec_new_payments.new_invoice_payment_id
524 ,NULL
525 ,P_Replace_Period_Name
526 ,NULL
527 ,P_Replace_Check_Date
528 ,rec_new_payments.amount
529 ,rec_new_payments.discount_taken
530 ,rec_new_payments.discount_lost
531 ,rec_new_payments.invoice_base_amount
532 ,rec_new_payments.payment_base_amount
533 ,'N'
534 ,'N'
535 ,'N'
536 ,rec_new_payments.set_of_books_id
537 ,P_Last_Updated_By
538 ,NULL
539 ,NULL
540 ,NULL
541 ,rec_new_payments.exchange_rate
542 ,rec_new_payments.exchange_rate_type
543 ,P_Replace_Check_Date
544 ,NULL
545 ,NULL
546 ,NULL
547 ,NULL
548 ,'N'
549 ,NULL
550 ,rec_new_payments.accts_pay_code_combination_id
551 ,rec_new_payments.gain_code_combination_id
552 ,rec_new_payments.loss_code_combination_id
553 ,P_Future_Pay_Ccid
554 ,NULL
555 ,'N'
556 ,'PAY'
557 ,'Y'
558 --Bug 2631799 Added attributes for payment information and invoices DFF
559 ,rec_new_payments.attribute1
560 ,rec_new_payments.attribute2
561 ,rec_new_payments.attribute3
562 ,rec_new_payments.attribute4
563 ,rec_new_payments.attribute5
564 ,rec_new_payments.attribute6
565 ,rec_new_payments.attribute7
566 ,rec_new_payments.attribute8
567 ,rec_new_payments.attribute9
568 ,rec_new_payments.attribute10
569 ,rec_new_payments.attribute11
570 ,rec_new_payments.attribute12
571 ,rec_new_payments.attribute13
572 ,rec_new_payments.attribute14
573 ,rec_new_payments.attribute15
574 ,rec_new_payments.attribute_category
575 ,rec_new_payments.global_attribute1
576 ,rec_new_payments.global_attribute2
577 ,rec_new_payments.global_attribute3
578 ,rec_new_payments.global_attribute4
579 ,rec_new_payments.global_attribute5
580 ,rec_new_payments.global_attribute6
581 ,rec_new_payments.global_attribute7
582 ,rec_new_payments.global_attribute8
583 ,rec_new_payments.global_attribute9
584 ,rec_new_payments.global_attribute10
585 ,rec_new_payments.global_attribute11
586 ,rec_new_payments.global_attribute12
587 ,rec_new_payments.global_attribute13
588 ,rec_new_payments.global_attribute14
589 ,rec_new_payments.global_attribute15
590 ,rec_new_payments.global_attribute16
591 ,rec_new_payments.global_attribute17
592 ,rec_new_payments.global_attribute18
593 ,rec_new_payments.global_attribute19
594 ,rec_new_payments.global_attribute20
595 ,rec_new_payments.global_attribute_category
596 ,l_curr_calling_sequence
597 ,l_accounting_event_id -- Events Project - 4
598 ,rec_new_payments.org_id); /* Bug 4759178. Added org_id */
599 END LOOP;
600
601 -------------------------------------------------------------------
602 l_debug_info := 'Close c_new_payments cursor';
603 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
604 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||'AP_AUTO_PAYMENT_PKG',l_debug_info);
605 END IF;
606
607 CLOSE c_new_payments;
608
609 -------------------------------------------------------------------
610
611 -- Events Project 5 -----------------------------------------------
612 --
613 -- For the Payment Issue case, we will create new invoice payments
614 -- for the new check (based upon existing invoice payments for the
615 -- check that we are replacing).
616 --
617 -- The Interest Invoice Distributions are not re-created as we are not
618 -- changing the invoices that the check pays. The Witholding
619 -- distributions are recreated.
620 --
621 -- The parameter replace_check_flag in the call
622 -- to AP_ACCOUNTING_EVENTS_PKG.Update_AWT_Int_Dists has been removed
623 -- in the changes for the Events Project in Family Pack D.
624 -- ----------------------------------------------------------------
625
626 AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
627 (
628 p_event_type => 'PAYMENT CREATED',
629 p_check_id => p_replace_check_id,
630 p_event_id => l_accounting_event_id,
631 p_calling_sequence => l_curr_calling_sequence
632 );
633
634 -------------------------------------------------------------------
635
636 -- Update old check amount if date changed
637 --
638 IF (P_Replace_Check_Date <> P_Orig_Payment_Date) THEN
639 l_debug_info := 'Update check amount';
640
641 UPDATE ap_checks
642 SET amount = P_orig_amount
643 WHERE check_id = P_Old_Check_Id;
644 END IF;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 IF (SQLCODE <> -20001) THEN
649 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
650 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
651 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
652 FND_MESSAGE.SET_TOKEN('PARAMETERS',
653 ' OLD_CHECK_ID = ' ||TO_CHAR(P_Old_Check_Id)
654 ||', REPLACE_CHECK_DATE = ' ||TO_CHAR(P_Replace_Check_Date)
655 ||', REPLACE_PERIOD_NAME = ' ||P_Replace_Period_Name
656 ||', REPLACE_CHECK_NUM = ' ||TO_CHAR(P_Replace_Check_Num)
657 ||', REPLACE_VOUCHER_NUM = ' ||TO_CHAR(P_Replace_Voucher_Num)
658 ||', ORIG_AMOUNT = ' ||TO_CHAR(P_Orig_Amount)
659 ||', ORIG_PAYMENT_DATE = ' ||TO_CHAR(P_Orig_Payment_Date)
660 ||', LAST_UPDATED_BY = ' ||TO_CHAR(P_Last_Updated_By)
661 ||', FUTURE_PAY_CCID = ' ||TO_CHAR(P_Future_Pay_Ccid));
662 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
663 END IF;
664 APP_EXCEPTION.RAISE_EXCEPTION;
665
666 END Replace_Check;
667
668 -- Bug 5061811 - removed obsoleted procedure INSERT_TEMP_RECORDS
669 --------------------------------------------------------------------------
670 -- Insert the records needed for the FORMAT program to work based on
671 -- P_check_id in the following tables:
672 --
673 -- AP_INVOICE_SELECTION_CRITERIA AISC
674 -- AP_SELECTED_INVOICE_CHECKS ASIC
675 -- AP_SELECTED_INVOICES ASI
676 --
677 -- NOTE: Records in ASIC and ASI will be deleted by the FORMAT program
678 --
679 --PROCEDURE Insert_Temp_Records(P_check_id IN NUMBER,
680 -- P_calling_sequence IN VARCHAR2)
681 --IS
682 -- l_debug_info VARCHAR2(240);
683 -- l_curr_calling_sequence VARCHAR2(2000);
684 -- BEGIN
685 -- bug 5061811 - removed all code in procedure
686 -- END Insert_Temp_Records;
687
688
689 --------------------------------------------------------------------------
690 -- Return 'Y' if record exists in AP_INVOICE_SELECTION_CRITERIA
691 --
692 FUNCTION Selection_Criteria_Exists(P_check_id IN NUMBER)
693 RETURN VARCHAR2
694 IS
695 l_num_records NUMBER;
696 l_exists_flag VARCHAR2(1);
697 BEGIN
698
699 SELECT count(*)
700 INTO l_num_records
701 FROM ap_inv_selection_criteria_all AISC,
702 ap_checks_all AC
703 WHERE AC.check_id = P_check_id
704 AND AC.checkrun_name = AISC.checkrun_name;
705
706 IF (l_num_records > 0) THEN
707 l_exists_flag := 'Y';
708 ELSE
709 l_exists_flag := 'N';
710 END IF;
711
712 RETURN l_exists_flag;
713
714 END Selection_Criteria_Exists;
715
716
717 -----------------------------------------------------------------------
718 -- Function get_check_stock_in_use_by returns the name of a payment batch
719 -- that uses the check_stock and do not have a status of
720 -- 'CONFIRMED', 'CANCELED', or 'QUICKCHECK'.
721 --
722 FUNCTION Get_Check_Stock_In_Use_By(p_check_stock_id IN NUMBER)
723 RETURN VARCHAR2 IS
724 l_checkrun_name ap_invoice_selection_criteria.checkrun_name%TYPE;
725 BEGIN
726
727 SELECT checkrun_name
728 INTO l_checkrun_name
729 FROM ap_invoice_selection_criteria
730 WHERE check_stock_id = p_check_stock_id
731 AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK');
732
733 return(l_checkrun_name);
734
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN return(NULL);
737
738 END Get_Check_Stock_In_Use_By;
739
740
741 -----------------------------------------------------------------------
742 -- Bug 1492588 :
743 -- Function Ok_To_Call_Withholding returns True if there is withholding
744 -- to recreate for an invoice, during check reissual
745 --
746 FUNCTION OK_To_Call_Withholding ( P_Invoice_Id IN NUMBER)
747 RETURN BOOLEAN IS
748 l_call_withholding VARCHAR2(1);
749
750 BEGIN
751
752 SELECT 'Y'
753 INTO l_call_withholding
754 FROM ap_invoices AI
755 WHERE AI.invoice_id = p_invoice_id
756 AND EXISTS ( SELECT 'At least 1 AWT line created automatically at payment time'
757 FROM ap_invoice_distributions AID
758 WHERE aid.invoice_id = ai.invoice_id
759 AND aid.awt_invoice_payment_id is not null);
760
761 RETURN(TRUE);
762
763 EXCEPTION
764 WHEN NO_DATA_FOUND THEN RETURN(FALSE);
765
766 END Ok_To_Call_Withholding;
767
768 END AP_AUTO_PAYMENT_PKG;