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