[Home] [Help]
PACKAGE BODY: APPS.AP_CHECKS_PKG
Source
1 PACKAGE BODY AP_CHECKS_PKG as
2 /* $Header: apichecb.pls 120.25.12020000.5 2012/07/26 15:37:31 shubhgup ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Amount NUMBER,
6 X_Ce_Bank_Acct_Use_Id NUMBER,
7 X_Bank_Account_Name VARCHAR2,
8 X_Check_Date DATE,
9 X_Check_Id NUMBER,
10 X_Check_Number NUMBER,
11 X_Currency_Code VARCHAR2,
12 X_Last_Updated_By NUMBER,
13 X_Last_Update_Date DATE,
14 --IBY:SP X_Payment_Method_Lookup_Code VARCHAR2,
15 X_Payment_Type_Flag VARCHAR2,
16 X_Address_Line1 VARCHAR2 DEFAULT NULL,
17 X_Address_Line2 VARCHAR2 DEFAULT NULL,
18 X_Address_Line3 VARCHAR2 DEFAULT NULL,
19 X_Checkrun_Name VARCHAR2 DEFAULT NULL,
20 X_Check_Format_Id NUMBER DEFAULT NULL,
21 X_Check_Stock_Id NUMBER DEFAULT NULL,
22 X_City VARCHAR2 DEFAULT NULL,
23 X_Country VARCHAR2 DEFAULT NULL,
24 X_Created_By NUMBER DEFAULT NULL,
25 X_Creation_Date DATE DEFAULT NULL,
26 X_Last_Update_Login NUMBER DEFAULT NULL,
27 X_Status_Lookup_Code VARCHAR2 DEFAULT NULL,
28 X_Vendor_Name VARCHAR2 DEFAULT NULL,
29 X_Vendor_Site_Code VARCHAR2 DEFAULT NULL,
30 X_External_Bank_Account_Id NUMBER,
31 X_Zip VARCHAR2 DEFAULT NULL,
32 X_Bank_Account_Num VARCHAR2 DEFAULT NULL,
33 X_Bank_Account_Type VARCHAR2 DEFAULT NULL,
34 X_Bank_Num VARCHAR2 DEFAULT NULL,
35 X_Check_Voucher_Num NUMBER DEFAULT NULL,
36 X_Cleared_Amount NUMBER DEFAULT NULL,
37 X_Cleared_Date DATE DEFAULT NULL,
38 X_Doc_Category_Code VARCHAR2 DEFAULT NULL,
39 X_Doc_Sequence_Id NUMBER DEFAULT NULL,
40 X_Doc_Sequence_Value NUMBER DEFAULT NULL,
41 X_Province VARCHAR2 DEFAULT NULL,
42 X_Released_Date DATE DEFAULT NULL,
43 X_Released_By NUMBER DEFAULT NULL,
44 X_State VARCHAR2 DEFAULT NULL,
45 X_Stopped_Date DATE DEFAULT NULL,
46 X_Stopped_By NUMBER DEFAULT NULL,
47 X_Void_Date DATE DEFAULT NULL,
48 X_Attribute1 VARCHAR2 DEFAULT NULL,
49 X_Attribute10 VARCHAR2 DEFAULT NULL,
50 X_Attribute11 VARCHAR2 DEFAULT NULL,
51 X_Attribute12 VARCHAR2 DEFAULT NULL,
52 X_Attribute13 VARCHAR2 DEFAULT NULL,
53 X_Attribute14 VARCHAR2 DEFAULT NULL,
54 X_Attribute15 VARCHAR2 DEFAULT NULL,
55 X_Attribute2 VARCHAR2 DEFAULT NULL,
56 X_Attribute3 VARCHAR2 DEFAULT NULL,
57 X_Attribute4 VARCHAR2 DEFAULT NULL,
58 X_Attribute5 VARCHAR2 DEFAULT NULL,
59 X_Attribute6 VARCHAR2 DEFAULT NULL,
60 X_Attribute7 VARCHAR2 DEFAULT NULL,
61 X_Attribute8 VARCHAR2 DEFAULT NULL,
62 X_Attribute9 VARCHAR2 DEFAULT NULL,
63 X_Attribute_Category VARCHAR2 DEFAULT NULL,
64 X_Future_Pay_Due_Date DATE DEFAULT NULL,
65 X_Treasury_Pay_Date DATE DEFAULT NULL,
66 X_Treasury_Pay_Number NUMBER DEFAULT NULL,
67 -- Removed for bug 4277744
68 -- X_Ussgl_Transaction_Code VARCHAR2 DEFAULT NULL,
69 -- X_Ussgl_Trx_Code_Context VARCHAR2 DEFAULT NULL,
70 X_Withholding_Status_Lkup_Code VARCHAR2 DEFAULT NULL,
71 X_Reconciliation_Batch_Id NUMBER DEFAULT NULL,
72 X_Cleared_Base_Amount NUMBER DEFAULT NULL,
73 X_Cleared_Exchange_Rate NUMBER DEFAULT NULL,
74 X_Cleared_Exchange_Date DATE DEFAULT NULL,
75 X_Cleared_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
76 X_Address_Line4 VARCHAR2 DEFAULT NULL,
77 X_County VARCHAR2 DEFAULT NULL,
78 X_Address_Style VARCHAR2 DEFAULT NULL,
79 X_Org_Id NUMBER DEFAULT NULL,
80 X_Vendor_Id NUMBER,
81 X_Vendor_Site_Id NUMBER,
82 X_Exchange_Rate NUMBER DEFAULT NULL,
83 X_Exchange_Date DATE DEFAULT NULL,
84 X_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
85 X_Base_Amount NUMBER DEFAULT NULL,
86 X_Checkrun_Id NUMBER DEFAULT NULL,
87 X_global_attribute_category VARCHAR2 DEFAULT NULL,
88 X_global_attribute1 VARCHAR2 DEFAULT NULL,
89 X_global_attribute2 VARCHAR2 DEFAULT NULL,
90 X_global_attribute3 VARCHAR2 DEFAULT NULL,
91 X_global_attribute4 VARCHAR2 DEFAULT NULL,
92 X_global_attribute5 VARCHAR2 DEFAULT NULL,
93 X_global_attribute6 VARCHAR2 DEFAULT NULL,
94 X_global_attribute7 VARCHAR2 DEFAULT NULL,
95 X_global_attribute8 VARCHAR2 DEFAULT NULL,
96 X_global_attribute9 VARCHAR2 DEFAULT NULL,
97 X_global_attribute10 VARCHAR2 DEFAULT NULL,
98 X_global_attribute11 VARCHAR2 DEFAULT NULL,
99 X_global_attribute12 VARCHAR2 DEFAULT NULL,
100 X_global_attribute13 VARCHAR2 DEFAULT NULL,
101 X_global_attribute14 VARCHAR2 DEFAULT NULL,
102 X_global_attribute15 VARCHAR2 DEFAULT NULL,
103 X_global_attribute16 VARCHAR2 DEFAULT NULL,
104 X_global_attribute17 VARCHAR2 DEFAULT NULL,
105 X_global_attribute18 VARCHAR2 DEFAULT NULL,
106 X_global_attribute19 VARCHAR2 DEFAULT NULL,
107 X_global_attribute20 VARCHAR2 DEFAULT NULL,
108 X_transfer_priority VARCHAR2 DEFAULT NULL,
109 X_maturity_exchange_rate_type VARCHAR2 DEFAULT NULL,
110 X_maturity_exchange_date DATE DEFAULT NULL,
111 X_maturity_exchange_rate NUMBER DEFAULT NULL,
112 X_description VARCHAR2 DEFAULT NULL,
113 X_anticipated_value_date DATE DEFAULT NULL,
114 X_actual_value_date DATE DEFAULT NULL,
115 x_payment_method_code VARCHAR2 DEFAULT NULL,
116 x_payment_profile_id NUMBER DEFAULT NULL,
117 x_bank_charge_bearer VARCHAR2 DEFAULT NULL,
118 x_settlement_priority VARCHAR2 DEFAULT NULL,
119 x_payment_document_id NUMBER DEFAULT NULL,
120 x_party_id NUMBER DEFAULT NULL,
121 x_party_site_id NUMBER DEFAULT NULL,
122 x_legal_entity_id NUMBER DEFAULT NULL,
123 x_payment_id NUMBER DEFAULT NULL,
124 X_calling_sequence IN VARCHAR2,
125 X_Remit_To_Supplier_Name VARCHAR2 DEFAULT NULL,
126 X_Remit_To_Supplier_Id Number DEFAULT NULL,
127 X_Remit_To_Supplier_Site VARCHAR2 DEFAULT NULL,
128 X_Remit_To_Supplier_Site_Id NUMBER DEFAULT NULL,
129 X_Relationship_Id NUMBER DEFAULT NULL,
130 X_paycard_authorization_number VARCHAR2 DEFAULT NULL,
131 X_paycard_reference_id NUMBER DEFAULT NULL
132 ) IS
133 current_calling_sequence VARCHAR2(2000);
134 debug_info VARCHAR2(100);
135
136 /* Added for bug#12393780 Start */
137 l_Address_Line1 hz_locations.address1%type;
138 l_Address_Line2 hz_locations.address2%type;
139 l_Address_Line3 hz_locations.address3%type;
140 l_Address_Line4 hz_locations.address4%type;
141 l_Address_Style fnd_territories.address_style%type;
142 l_City hz_locations.city%type;
143 l_County hz_locations.county%type;
144 l_State hz_locations.state%type;
145 l_Province hz_locations.province%type;
146 l_Zip hz_locations.postal_code%type;
147 l_Country hz_locations.country%type;
148 /* Added for bug#12393780 End */
149
150 BEGIN
151
152 -- Update the calling sequence
153 --
154 current_calling_sequence := 'AP_CHECKS_PKG.INSERT_ROW<-' ||
155 X_calling_sequence;
156
157 /* Added for bug#12393780 Start */
158 debug_info := 'Deriving the remit to Address';
159
160 BEGIN
161 IF X_Remit_To_Supplier_Site_Id IS NOT NULL
162 THEN
163 SELECT hzl.address1 address_line1,
164 hzl.address2 address_line2,
165 hzl.address3 address_line3,
166 hzl.address4 address_line4,
167 nvl(B.address_style, 'DEFAULT') address_style,
168 hzl.city,
169 hzl.county,
170 hzl.state,
171 hzl.province,
172 hzl.postal_code zip,
173 hzl.country
174 INTO l_Address_Line1
175 , l_Address_Line2
176 , l_Address_Line3
177 , l_Address_Line4
178 , l_Address_Style
179 , l_City
180 , l_County
181 , l_State
182 , l_Province
183 , l_Zip
184 , l_Country
185 FROM ap_supplier_sites a,
186 hz_locations hzl ,
187 fnd_territories_tl ft,
188 FND_TERRITORIES B
189 WHERE a.pay_site_flag = 'Y'
190 AND a.vendor_id = X_Remit_To_Supplier_Id
191 AND a.vendor_site_id = X_Remit_To_Supplier_Site_Id
192 AND a.location_id = hzl.location_id
193 AND hzl.country = FT.territory_code (+)
194 AND B.TERRITORY_CODE = FT.TERRITORY_CODE
195 AND FT.LANGUAGE = USERENV('LANG')
196 AND B.TERRITORY_CODE = hzl.country;
197
198 /* Bug 13421900 starts */
199 ELSE
200 l_Address_Line1 := X_Address_Line1;
201 l_Address_Line2 := X_Address_Line2;
202 l_Address_Line3 := X_Address_Line3;
203 l_Address_Line4 := X_Address_Line4;
204 l_Address_Style := X_Address_Style;
205 l_City := X_City;
206 l_County := X_County;
207 l_State := X_State;
208 l_Province := X_Province;
209 l_Zip := X_Zip;
210 l_Country := X_Country;
211 /*End bug 13421900 */
212 END IF;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 l_Address_Line1 := X_Address_Line1;
217 l_Address_Line2 := X_Address_Line2;
218 l_Address_Line3 := X_Address_Line3;
219 l_Address_Line4 := X_Address_Line4;
220 l_Address_Style := X_Address_Style;
221 l_City := X_City;
222 l_County := X_County;
223 l_State := X_State;
224 l_Province := X_Province;
225 l_Zip := X_Zip;
226 l_Country := X_Country;
227 END;
228 /* Added for bug#12393780 End */
229
230 debug_info := 'Insert into ap_checks';
231 AP_AC_TABLE_HANDLER_PKG.Insert_Row(
232 X_Rowid,
233 X_Amount,
234 X_Ce_Bank_Acct_Use_Id,
235 X_Bank_Account_Name,
236 X_Check_Date,
237 X_Check_Id,
238 X_Check_Number,
239 X_Currency_Code,
240 X_Last_Updated_By,
241 X_Last_Update_Date,
242 --IBY:SP X_Payment_Method_Lookup_Code,
243 X_Payment_Type_Flag,
244 L_Address_Line1,
245 L_Address_Line2,
246 L_Address_Line3,
247 X_Checkrun_Name,
248 X_Check_Format_Id,
249 X_Check_Stock_Id,
250 L_City,
251 L_Country,
252 X_Created_By,
253 X_Creation_Date,
254 X_Last_Update_Login,
255 X_Status_Lookup_Code,
256 X_Vendor_Name,
257 X_Vendor_Site_Code,
258 X_External_Bank_Account_Id,
259 L_Zip,
260 X_Bank_Account_Num,
261 X_Bank_Account_Type,
262 X_Bank_Num,
263 X_Check_Voucher_Num,
264 X_Cleared_Amount,
265 X_Cleared_Date,
266 X_Doc_Category_Code,
267 X_Doc_Sequence_Id,
268 X_Doc_Sequence_Value,
269 L_Province,
270 X_Released_Date,
271 X_Released_By,
272 L_State,
273 X_Stopped_Date,
274 X_Stopped_By,
275 X_Void_Date,
276 X_Attribute1,
277 X_Attribute10,
278 X_Attribute11,
279 X_Attribute12,
280 X_Attribute13,
281 X_Attribute14,
282 X_Attribute15,
283 X_Attribute2,
284 X_Attribute3,
285 X_Attribute4,
286 X_Attribute5,
287 X_Attribute6,
288 X_Attribute7,
289 X_Attribute8,
290 X_Attribute9,
291 X_Attribute_Category,
292 X_Future_Pay_Due_Date,
293 X_Treasury_Pay_Date,
294 X_Treasury_Pay_Number,
295 -- Removed for bug 4277744
296 -- X_Ussgl_Transaction_Code,
297 -- X_Ussgl_Trx_Code_Context,
298 X_Withholding_Status_Lkup_Code,
299 X_Reconciliation_Batch_Id,
300 X_Cleared_Base_Amount,
301 X_Cleared_Exchange_Rate,
302 X_Cleared_Exchange_Date,
303 X_Cleared_Exchange_Rate_Type,
304 L_Address_Line4,
305 L_County,
306 L_Address_Style,
307 X_Org_Id,
308 X_Vendor_Id,
309 X_Vendor_Site_Id,
310 X_Exchange_Rate,
311 X_Exchange_Date,
312 X_Exchange_Rate_Type,
313 X_Base_Amount,
314 X_Checkrun_Id,
315 X_global_attribute_category,
316 X_global_attribute1,
317 X_global_attribute2,
318 X_global_attribute3,
319 X_global_attribute4,
320 X_global_attribute5,
321 X_global_attribute6,
322 X_global_attribute7,
323 X_global_attribute8,
324 X_global_attribute9,
325 X_global_attribute10,
326 X_global_attribute11,
327 X_global_attribute12,
328 X_global_attribute13,
329 X_global_attribute14,
330 X_global_attribute15,
331 X_global_attribute16,
332 X_global_attribute17,
333 X_global_attribute18,
334 X_global_attribute19,
335 X_global_attribute20,
336 X_transfer_priority,
337 X_maturity_exchange_rate_type,
338 X_maturity_exchange_date,
339 X_maturity_exchange_rate,
340 X_description,
341 X_anticipated_value_date,
342 X_actual_value_date,
343 x_payment_method_code,
344 x_payment_profile_id,
345 x_bank_charge_bearer,
346 x_settlement_priority,
347 x_payment_document_id,
348 x_party_id,
349 x_party_site_id,
350 x_legal_entity_id,
351 x_payment_id,
352 current_calling_sequence,
353 X_Remit_To_Supplier_Name,
354 X_Remit_To_Supplier_Id,
355 X_Remit_To_Supplier_Site,
356 X_Remit_To_Supplier_Site_Id,
357 X_Relationship_ID ,
358 x_paycard_authorization_number,
359 X_paycard_reference_id
360 );
361
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 IF (SQLCODE <> -20001) THEN
366 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
367 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
368 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
369 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
370 ', CHECK_ID = ' || TO_CHAR(X_Check_Id));
371 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
372 END IF;
373 APP_EXCEPTION.RAISE_EXCEPTION;
374
375 END Insert_Row;
376
377
378 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
379 X_Amount NUMBER,
380 X_Ce_Bank_Acct_Use_Id NUMBER,
381 X_Bank_Account_Name VARCHAR2,
382 X_Check_Date DATE,
383 X_Check_Id NUMBER,
384 X_Check_Number NUMBER,
385 X_Currency_Code VARCHAR2,
386 --IBY:SP X_Payment_Method_Lookup_Code VARCHAR2,
387 X_Payment_Type_Flag VARCHAR2,
388 X_Address_Line1 VARCHAR2 DEFAULT NULL,
389 X_Address_Line2 VARCHAR2 DEFAULT NULL,
390 X_Address_Line3 VARCHAR2 DEFAULT NULL,
391 X_Checkrun_Name VARCHAR2 DEFAULT NULL,
392 X_Check_Format_Id NUMBER DEFAULT NULL,
393 X_Check_Stock_Id NUMBER DEFAULT NULL,
394 X_City VARCHAR2 DEFAULT NULL,
395 X_Country VARCHAR2 DEFAULT NULL,
396 X_Status_Lookup_Code VARCHAR2 DEFAULT NULL,
397 X_Vendor_Name VARCHAR2 DEFAULT NULL,
398 X_Vendor_Site_Code VARCHAR2 DEFAULT NULL,
399 X_External_Bank_Account_Id NUMBER,
400 X_Zip VARCHAR2 DEFAULT NULL,
401 X_Bank_Account_Num VARCHAR2 DEFAULT NULL,
402 X_Bank_Account_Type VARCHAR2 DEFAULT NULL,
403 X_Bank_Num VARCHAR2 DEFAULT NULL,
404 X_Check_Voucher_Num NUMBER DEFAULT NULL,
405 X_Cleared_Amount NUMBER DEFAULT NULL,
406 X_Cleared_Date DATE DEFAULT NULL,
407 X_Doc_Category_Code VARCHAR2 DEFAULT NULL,
408 X_Doc_Sequence_Id NUMBER DEFAULT NULL,
409 X_Doc_Sequence_Value NUMBER DEFAULT NULL,
410 X_Province VARCHAR2 DEFAULT NULL,
411 X_Released_Date DATE DEFAULT NULL,
412 X_Released_By NUMBER DEFAULT NULL,
413 X_State VARCHAR2 DEFAULT NULL,
414 X_Stopped_Date DATE DEFAULT NULL,
415 X_Stopped_By NUMBER DEFAULT NULL,
416 X_Void_Date DATE DEFAULT NULL,
417 X_Attribute1 VARCHAR2 DEFAULT NULL,
418 X_Attribute10 VARCHAR2 DEFAULT NULL,
419 X_Attribute11 VARCHAR2 DEFAULT NULL,
420 X_Attribute12 VARCHAR2 DEFAULT NULL,
421 X_Attribute13 VARCHAR2 DEFAULT NULL,
422 X_Attribute14 VARCHAR2 DEFAULT NULL,
423 X_Attribute15 VARCHAR2 DEFAULT NULL,
424 X_Attribute2 VARCHAR2 DEFAULT NULL,
425 X_Attribute3 VARCHAR2 DEFAULT NULL,
426 X_Attribute4 VARCHAR2 DEFAULT NULL,
427 X_Attribute5 VARCHAR2 DEFAULT NULL,
428 X_Attribute6 VARCHAR2 DEFAULT NULL,
429 X_Attribute7 VARCHAR2 DEFAULT NULL,
430 X_Attribute8 VARCHAR2 DEFAULT NULL,
431 X_Attribute9 VARCHAR2 DEFAULT NULL,
432 X_Attribute_Category VARCHAR2 DEFAULT NULL,
433 X_Future_Pay_Due_Date DATE DEFAULT NULL,
434 X_Treasury_Pay_Date DATE DEFAULT NULL,
435 X_Treasury_Pay_Number NUMBER DEFAULT NULL,
436 -- Removed for bug 4277744
437 -- X_Ussgl_Transaction_Code VARCHAR2 DEFAULT NULL,
438 -- X_Ussgl_Trx_Code_Context VARCHAR2 DEFAULT NULL,
439 X_Withholding_Status_Lkup_Code VARCHAR2 DEFAULT NULL,
440 X_Reconciliation_Batch_Id NUMBER DEFAULT NULL,
441 X_Cleared_Base_Amount NUMBER DEFAULT NULL,
442 X_Cleared_Exchange_Rate NUMBER DEFAULT NULL,
443 X_Cleared_Exchange_Date DATE DEFAULT NULL,
444 X_Cleared_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
445 X_Address_Line4 VARCHAR2 DEFAULT NULL,
446 X_County VARCHAR2 DEFAULT NULL,
447 X_Address_Style VARCHAR2 DEFAULT NULL,
448 X_Org_Id NUMBER DEFAULT NULL,
449 X_Vendor_Id NUMBER,
450 X_Vendor_Site_Id NUMBER,
451 X_Exchange_Rate NUMBER DEFAULT NULL,
452 X_Exchange_Date DATE DEFAULT NULL,
453 X_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
454 X_Base_Amount NUMBER DEFAULT NULL,
455 X_Checkrun_Id NUMBER DEFAULT NULL,
456 X_global_attribute_category VARCHAR2 DEFAULT NULL,
457 X_global_attribute1 VARCHAR2 DEFAULT NULL,
458 X_global_attribute2 VARCHAR2 DEFAULT NULL,
459 X_global_attribute3 VARCHAR2 DEFAULT NULL,
460 X_global_attribute4 VARCHAR2 DEFAULT NULL,
461 X_global_attribute5 VARCHAR2 DEFAULT NULL,
462 X_global_attribute6 VARCHAR2 DEFAULT NULL,
463 X_global_attribute7 VARCHAR2 DEFAULT NULL,
464 X_global_attribute8 VARCHAR2 DEFAULT NULL,
465 X_global_attribute9 VARCHAR2 DEFAULT NULL,
466 X_global_attribute10 VARCHAR2 DEFAULT NULL,
467 X_global_attribute11 VARCHAR2 DEFAULT NULL,
468 X_global_attribute12 VARCHAR2 DEFAULT NULL,
469 X_global_attribute13 VARCHAR2 DEFAULT NULL,
470 X_global_attribute14 VARCHAR2 DEFAULT NULL,
471 X_global_attribute15 VARCHAR2 DEFAULT NULL,
472 X_global_attribute16 VARCHAR2 DEFAULT NULL,
473 X_global_attribute17 VARCHAR2 DEFAULT NULL,
474 X_global_attribute18 VARCHAR2 DEFAULT NULL,
475 X_global_attribute19 VARCHAR2 DEFAULT NULL,
476 X_global_attribute20 VARCHAR2 DEFAULT NULL,
477 X_transfer_priority VARCHAR2 DEFAULT NULL,
478 X_maturity_exchange_rate_type VARCHAR2 DEFAULT NULL,
479 X_maturity_exchange_date DATE DEFAULT NULL,
480 X_maturity_exchange_rate NUMBER DEFAULT NULL,
481 X_description VARCHAR2 DEFAULT NULL,
482 X_anticipated_value_date DATE DEFAULT NULL,
483 X_actual_value_date DATE DEFAULT NULL,
484 x_payment_method_code VARCHAR2 DEFAULT NULL,
485 x_payment_profile_id NUMBER DEFAULT NULL,
486 x_bank_charge_bearer VARCHAR2 DEFAULT NULL,
487 x_settlement_priority VARCHAR2 DEFAULT NULL,
488 x_payment_document_id NUMBER DEFAULT NULL,
489 x_party_id NUMBER DEFAULT NULL,
490 x_party_site_id NUMBER DEFAULT NULL,
491 x_legal_entity_id NUMBER DEFAULT NULL,
492 x_payment_id NUMBER DEFAULT NULL,
493 X_calling_sequence IN VARCHAR2,
494 X_Remit_To_Supplier_Name VARCHAR2 DEFAULT NULL,
495 X_Remit_To_Supplier_Id Number DEFAULT NULL,
496 X_Remit_To_Supplier_Site VARCHAR2 DEFAULT NULL,
497 X_Remit_To_Supplier_Site_Id NUMBER DEFAULT NULL,
498 X_Relationship_Id NUMBER DEFAULT NULL,
499 X_paycard_authorization_number VARCHAR2 DEFAULT NULL,
500 X_paycard_reference_id NUMBER DEFAULT NULL,
501 X_acknowledged_flag VARCHAR2 DEFAULT NULL /* Added for NOEX */
502 ) IS
503
504
505 --Modified below cursor for bug #8236815/8348653
506 --Added rtrim for all varchar2 fields.
507
508 CURSOR C IS
509 SELECT
510 ACTUAL_VALUE_DATE,
511 ANTICIPATED_VALUE_DATE,
512 AMOUNT,
513 CE_BANK_ACCT_USE_ID,
514 rtrim(BANK_ACCOUNT_NAME) BANK_ACCOUNT_NAME,
515 CHECK_DATE,
516 CHECK_ID,
517 CHECK_NUMBER,
518 rtrim(CURRENCY_CODE) CURRENCY_CODE,
519 LAST_UPDATED_BY,
520 LAST_UPDATE_DATE,
521 --IBY:SP
522 --Bug5949912, bug6312110
523 rtrim(PAYMENT_METHOD_LOOKUP_CODE) PAYMENT_METHOD_LOOKUP_CODE,
524 rtrim(PAYMENT_TYPE_FLAG) payment_type_flag,
525 -- Bug 6620381. trimming trailing space for the address lines
526 rtrim(ADDRESS_LINE1) ADDRESS_LINE1,
527 rtrim(ADDRESS_LINE2) ADDRESS_LINE2,
528 rtrim(ADDRESS_LINE3) ADDRESS_LINE3,
529 rtrim(CHECKRUN_NAME) checkrun_name,
530 -- CHECK_FORMAT_ID, Bug 5460922. Removing these fields from cursor
531 -- CHECK_STOCK_ID, as these fields are no longer used in R12.
532 rtrim(CITY) CITY,
533 rtrim(COUNTRY) country,
534 CREATED_BY,
535 CREATION_DATE,
536 LAST_UPDATE_LOGIN,
537 rtrim(STATUS_LOOKUP_CODE) STATUS_LOOKUP_CODE,
538 --bug7670430 trimming trailing spaces for vendor_name
539 rtrim(VENDOR_NAME) VENDOR_NAME ,
540 rtrim(VENDOR_SITE_CODE) VENDOR_SITE_CODE,
541 rtrim(ZIP) ZIP,
542 BANK_ACCOUNT_NUM,
543 BANK_ACCOUNT_TYPE,
544 BANK_NUM,
545 CHECK_VOUCHER_NUM,
546 CLEARED_AMOUNT,
547 CLEARED_DATE,
548 DOC_CATEGORY_CODE,
549 DOC_SEQUENCE_ID,
550 DOC_SEQUENCE_VALUE,
551 rtrim(PROVINCE) PROVINCE,
552 RELEASED_AT,
553 RELEASED_BY,
554 rtrim(STATE) STATE,
555 STOPPED_AT,
556 STOPPED_BY,
557 VOID_DATE,
558 ATTRIBUTE1,
559 ATTRIBUTE10,
560 ATTRIBUTE11,
561 ATTRIBUTE12,
562 ATTRIBUTE13,
563 ATTRIBUTE14,
564 ATTRIBUTE15,
565 ATTRIBUTE2,
566 ATTRIBUTE3,
567 ATTRIBUTE4,
568 ATTRIBUTE5,
569 ATTRIBUTE6,
570 ATTRIBUTE7,
571 ATTRIBUTE8,
572 ATTRIBUTE9,
573 rtrim(ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY,
574 FUTURE_PAY_DUE_DATE,
575 TREASURY_PAY_DATE,
576 TREASURY_PAY_NUMBER,
577 -- Removed for bug 4277744
578 -- USSGL_TRANSACTION_CODE,
579 -- USSGL_TRX_CODE_CONTEXT,
580 rtrim(WITHHOLDING_STATUS_LOOKUP_CODE) WITHHOLDING_STATUS_LOOKUP_CODE,
581 RECONCILIATION_BATCH_ID,
582 CLEARED_BASE_AMOUNT,
583 CLEARED_EXCHANGE_RATE,
584 CLEARED_EXCHANGE_DATE,
585 CLEARED_EXCHANGE_RATE_TYPE,
586 rtrim(ADDRESS_LINE4) ADDRESS_LINE4,
587 rtrim(COUNTY) COUNTY,
588 rtrim(ADDRESS_STYLE) ADDRESS_STYLE,
589 ORG_ID,
590 VENDOR_ID,
591 VENDOR_SITE_ID,
592 EXCHANGE_RATE,
593 EXCHANGE_DATE,
594 EXCHANGE_RATE_TYPE,
595 BASE_AMOUNT,
596 CHECKRUN_ID,
597 REQUEST_ID,
598 CLEARED_ERROR_AMOUNT,
599 CLEARED_CHARGES_AMOUNT,
600 CLEARED_ERROR_BASE_AMOUNT,
601 CLEARED_CHARGES_BASE_AMOUNT,
602 rtrim(POSITIVE_PAY_STATUS_CODE),
603 GLOBAL_ATTRIBUTE_CATEGORY,
604 GLOBAL_ATTRIBUTE1,
605 GLOBAL_ATTRIBUTE2,
606 GLOBAL_ATTRIBUTE3,
607 GLOBAL_ATTRIBUTE4,
608 GLOBAL_ATTRIBUTE5,
609 GLOBAL_ATTRIBUTE6,
610 GLOBAL_ATTRIBUTE7,
611 GLOBAL_ATTRIBUTE8,
612 GLOBAL_ATTRIBUTE9,
613 GLOBAL_ATTRIBUTE10,
614 GLOBAL_ATTRIBUTE11,
615 GLOBAL_ATTRIBUTE12,
616 GLOBAL_ATTRIBUTE13,
617 GLOBAL_ATTRIBUTE14,
618 GLOBAL_ATTRIBUTE15,
619 GLOBAL_ATTRIBUTE16,
620 GLOBAL_ATTRIBUTE17,
621 GLOBAL_ATTRIBUTE18,
622 GLOBAL_ATTRIBUTE19,
623 GLOBAL_ATTRIBUTE20,
624 TRANSFER_PRIORITY,
625 EXTERNAL_BANK_ACCOUNT_ID,
626 STAMP_DUTY_AMT,
627 STAMP_DUTY_BASE_AMT,
628 -- MRC_CLEARED_BASE_AMOUNT, Bug 5460922
629 -- MRC_CLEARED_EXCHANGE_RATE,
630 -- MRC_CLEARED_EXCHANGE_DATE,
631 -- MRC_CLEARED_EXCHANGE_RATE_TYPE,
632 -- MRC_EXCHANGE_RATE,
633 -- MRC_EXCHANGE_DATE,
634 -- MRC_EXCHANGE_RATE_TYPE,
635 -- MRC_BASE_AMOUNT,
636 -- MRC_CLEARED_ERROR_BASE_AMOUNT,
637 -- MRC_CLEARED_CHARGES_BASE_AMT,
638 -- MRC_STAMP_DUTY_BASE_AMT,
639 MATURITY_EXCHANGE_DATE,
640 MATURITY_EXCHANGE_RATE_TYPE,
641 MATURITY_EXCHANGE_RATE,
642 rtrim(DESCRIPTION) DESCRIPTION,
643 RELEASED_DATE,
644 STOPPED_DATE,
645 -- MRC_MATURITY_EXG_DATE, Bug 5460922
646 -- MRC_MATURITY_EXG_RATE,
647 -- MRC_MATURITY_EXG_RATE_TYPE,
648 rtrim(PAYMENT_METHOD_CODE) PAYMENT_METHOD_CODE,
649 PAYMENT_PROFILE_ID,
650 rtrim(BANK_CHARGE_BEARER) BANK_CHARGE_BEARER,
651 SETTLEMENT_PRIORITY,
652 PAYMENT_DOCUMENT_ID,
653 PARTY_ID,
654 PARTY_SITE_ID,
655 legal_entity_id,
656 payment_id,
657 rtrim(Remit_To_Supplier_Name) Remit_To_Supplier_Name,
658 Remit_To_Supplier_Id,
659 rtrim(Remit_To_Supplier_Site) Remit_To_Supplier_Site,
660 Remit_To_Supplier_Site_Id,
661 Relationship_Id,
662 Paycard_authorization_number,
663 Paycard_reference_id,
664 acknowledged_flag /* Added for NOEX */
665 FROM ap_checks_all
666 WHERE rowid = X_Rowid
667 FOR UPDATE of Check_Id NOWAIT;
668 Recinfo C%ROWTYPE;
669 current_calling_sequence VARCHAR2(2000);
670 debug_info VARCHAR2(100);
671
672
673 BEGIN
674 -- Update the calling sequence
675 --
676 current_calling_sequence := 'AP_CHECKS_PKG.LOCK_ROW<-' ||
677 X_calling_sequence;
678
679 debug_info := 'Open cursor C';
680 OPEN C;
681 debug_info := 'Fetch cursor C';
682 FETCH C INTO Recinfo;
683
684 if (C%NOTFOUND) then
685 debug_info := 'Close cursor C - ROW NOTFOUND';
686 CLOSE C;
687 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
688 APP_EXCEPTION.Raise_Exception;
689 end if;
690 debug_info := 'Close cursor C';
691 CLOSE C;
692 if (
693
694 (Recinfo.amount = X_Amount)
695 AND ((Recinfo.ce_bank_acct_use_id = X_Ce_Bank_Acct_Use_Id)
696 OR ( (Recinfo.ce_bank_acct_use_id IS NULL)
697 AND (X_Ce_Bank_Acct_Use_Id IS NULL)))
698 AND (Recinfo.bank_account_name = X_Bank_Account_Name)
699 AND (Recinfo.check_date = X_Check_Date)
700 AND (Recinfo.check_id = X_Check_Id)
701 AND (Recinfo.check_number = X_Check_Number)
702 AND (Recinfo.currency_code = X_Currency_Code)
703 --IBY:SP AND (Recinfo.payment_method_lookup_code = X_Payment_Method_Lookup_Code)
704 AND (Recinfo.payment_type_flag = X_Payment_Type_Flag)
705 AND (Recinfo.org_id = X_Org_Id)
706 AND ( (Recinfo.address_line1 = X_Address_Line1)
707 OR ( (Recinfo.address_line1 IS NULL)
708 AND (X_Address_Line1 IS NULL)))
709 AND ( (Recinfo.address_line2 = X_Address_Line2)
710 OR ( (Recinfo.address_line2 IS NULL)
711 AND (X_Address_Line2 IS NULL)))
712 AND ( (Recinfo.address_line3 = X_Address_Line3)
713 OR ( (Recinfo.address_line3 IS NULL)
714 AND (X_Address_Line3 IS NULL)))
715 AND ( (Recinfo.checkrun_name = X_Checkrun_Name)
716 OR ( (Recinfo.checkrun_name IS NULL)
717 AND (X_Checkrun_Name IS NULL)))
718 /* AND ( (Recinfo.check_format_id = X_Check_Format_Id)
719 OR ( (Recinfo.check_format_id IS NULL)
720 AND (X_Check_Format_Id IS NULL)))
721 AND ( (Recinfo.check_stock_id = X_Check_Stock_Id)
722 OR ( (Recinfo.check_stock_id IS NULL)
723 AND (X_Check_Stock_Id IS NULL)))*/ -- Bug 5460922
724 AND ( (Recinfo.city = X_City)
725 OR ( (Recinfo.city IS NULL)
726 AND (X_City IS NULL)))
727 AND ( (Recinfo.country = X_Country)
728 OR ( (Recinfo.country IS NULL)
729 AND (X_Country IS NULL)))
730 AND ( (Recinfo.status_lookup_code = X_Status_Lookup_Code)
731 OR ( (Recinfo.status_lookup_code IS NULL)
732 AND (X_Status_Lookup_Code IS NULL)))
733 AND ( (Recinfo.vendor_name = X_Vendor_Name)
734 OR ( (Recinfo.vendor_name IS NULL)
735 AND (X_Vendor_Name IS NULL)))
736 AND ( (Recinfo.vendor_site_code = X_Vendor_Site_Code)
737 OR ( (Recinfo.vendor_site_code IS NULL)
738 AND (X_Vendor_Site_Code IS NULL)))
739 AND ( (Recinfo.external_bank_Account_id = X_external_bank_Account_id)
740 OR ( (Recinfo.external_bank_Account_id IS NULL)
741 AND (X_external_bank_Account_id IS NULL)))
742 AND ( (Recinfo.zip = X_Zip)
743 OR ( (Recinfo.zip IS NULL)
744 AND (X_Zip IS NULL)))
745 AND ( (Recinfo.bank_account_num = X_Bank_Account_Num)
746 OR ( (Recinfo.bank_account_num IS NULL)
747 AND (X_Bank_Account_Num IS NULL)))
748 AND ( (Recinfo.bank_account_type = X_Bank_Account_Type)
749 OR ( (Recinfo.bank_account_type IS NULL)
750 AND (X_Bank_Account_Type IS NULL)))
751 AND ( (Recinfo.bank_num = X_Bank_Num)
752 OR ( (Recinfo.bank_num IS NULL)
753 AND (X_Bank_Num IS NULL)))
754 AND ( (Recinfo.check_voucher_num = X_Check_Voucher_Num)
755 OR ( (Recinfo.check_voucher_num IS NULL)
756 AND (X_Check_Voucher_Num IS NULL)))
757 AND ( (Recinfo.cleared_amount = X_Cleared_Amount)
758 OR ( (Recinfo.cleared_amount IS NULL)
759 AND (X_Cleared_Amount IS NULL)))
760 AND ( (Recinfo.cleared_date = X_Cleared_Date)
761 OR ( (Recinfo.cleared_date IS NULL)
762 AND (X_Cleared_Date IS NULL)))
763 AND ( (Recinfo.doc_category_code = X_Doc_Category_Code)
764 OR ( (Recinfo.doc_category_code IS NULL)
765 AND (X_Doc_Category_Code IS NULL)))
766 AND ( (Recinfo.doc_sequence_id = X_Doc_Sequence_Id)
767 OR ( (Recinfo.doc_sequence_id IS NULL)
768 AND (X_Doc_Sequence_Id IS NULL)))
769 AND ( (Recinfo.doc_sequence_value = X_Doc_Sequence_Value)
770 OR ( (Recinfo.doc_sequence_value IS NULL)
771 AND (X_Doc_Sequence_Value IS NULL)))
772 AND ( (Recinfo.province = X_Province)
773 OR ( (Recinfo.province IS NULL)
774 AND (X_Province IS NULL)))
775 AND ( (Recinfo.released_date = X_Released_Date)
776 OR ( (Recinfo.released_date IS NULL)
777 AND (X_Released_Date IS NULL)))
778 AND ( (Recinfo.released_by = X_Released_By)
779 OR ( (Recinfo.released_by IS NULL)
780 AND (X_Released_By IS NULL)))
781 AND ( (Recinfo.state = X_State)
782 OR ( (Recinfo.state IS NULL)
783 AND (X_State IS NULL)))
784 AND ( (Recinfo.stopped_date = X_Stopped_Date)
785 OR ( (Recinfo.stopped_date IS NULL)
786 AND (X_Stopped_Date IS NULL)))
787 AND ( (Recinfo.stopped_by = X_Stopped_By)
788 OR ( (Recinfo.stopped_by IS NULL)
789 AND (X_Stopped_By IS NULL)))
790 AND ( (Recinfo.void_date = X_Void_Date)
791 OR ( (Recinfo.void_date IS NULL)
792 AND (X_Void_Date IS NULL)))
793 AND ( (Recinfo.attribute1 = X_Attribute1)
794 OR ( (Recinfo.attribute1 IS NULL)
795 AND (X_Attribute1 IS NULL)))
796 AND ( (Recinfo.attribute10 = X_Attribute10)
797 OR ( (Recinfo.attribute10 IS NULL)
798 AND (X_Attribute10 IS NULL)))
799 )then
800
801 if( ( (Recinfo.attribute11 = X_Attribute11)
802 OR ( (Recinfo.attribute11 IS NULL)
803 AND (X_Attribute11 IS NULL)))
804 AND ( (Recinfo.attribute12 = X_Attribute12)
805 OR ( (Recinfo.attribute12 IS NULL)
806 AND (X_Attribute12 IS NULL)))
807 AND ( (Recinfo.attribute13 = X_Attribute13)
808 OR ( (Recinfo.attribute13 IS NULL)
809 AND (X_Attribute13 IS NULL)))
810 AND ( (Recinfo.attribute14 = X_Attribute14)
811 OR ( (Recinfo.attribute14 IS NULL)
812 AND (X_Attribute14 IS NULL)))
813 AND ( (Recinfo.attribute15 = X_Attribute15)
814 OR ( (Recinfo.attribute15 IS NULL)
815 AND (X_Attribute15 IS NULL)))
816 AND ( (Recinfo.attribute2 = X_Attribute2)
817 OR ( (Recinfo.attribute2 IS NULL)
818 AND (X_Attribute2 IS NULL)))
819 AND ( (Recinfo.attribute3 = X_Attribute3)
820 OR ( (Recinfo.attribute3 IS NULL)
821 AND (X_Attribute3 IS NULL)))
822 AND ( (Recinfo.attribute4 = X_Attribute4)
823 OR ( (Recinfo.attribute4 IS NULL)
824 AND (X_Attribute4 IS NULL)))
825 AND ( (Recinfo.attribute5 = X_Attribute5)
826 OR ( (Recinfo.attribute5 IS NULL)
827 AND (X_Attribute5 IS NULL)))
828 AND ( (Recinfo.attribute6 = X_Attribute6)
829 OR ( (Recinfo.attribute6 IS NULL)
830 AND (X_Attribute6 IS NULL)))
831 AND ( (Recinfo.attribute7 = X_Attribute7)
832 OR ( (Recinfo.attribute7 IS NULL)
833 AND (X_Attribute7 IS NULL)))
834 AND ( (Recinfo.attribute8 = X_Attribute8)
835 OR ( (Recinfo.attribute8 IS NULL)
836 AND (X_Attribute8 IS NULL)))
837 AND ( (Recinfo.attribute9 = X_Attribute9)
838 OR ( (Recinfo.attribute9 IS NULL)
839 AND (X_Attribute9 IS NULL)))
840 /* Commented for bug#6976792 Start
841 AND ( (Recinfo.attribute_category = X_Attribute_Category)
842 OR ( (Recinfo.attribute_category IS NULL)
843 AND (X_Attribute_Category IS NULL)))
844 Commented for bug#6976792 End */
845 AND ( (Recinfo.future_pay_due_date = X_Future_Pay_Due_Date)
846 OR ( (Recinfo.future_pay_due_date IS NULL)
847 AND (X_Future_Pay_Due_Date IS NULL)))
848 AND ( (Recinfo.treasury_pay_date = X_Treasury_Pay_Date)
849 OR ( (Recinfo.treasury_pay_date IS NULL)
850 AND (X_Treasury_Pay_Date IS NULL)))
851 AND ( (Recinfo.treasury_pay_number = X_Treasury_Pay_Number)
852 OR ( (Recinfo.treasury_pay_number IS NULL)
853 AND (X_Treasury_Pay_Number IS NULL)))
854 -- Removed for bug 4277744
855 -- AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
856 -- OR ( (Recinfo.ussgl_transaction_code IS NULL)
857 -- AND (X_Ussgl_Transaction_Code IS NULL)))
858 -- AND ( (Recinfo.ussgl_trx_code_context = X_Ussgl_Trx_Code_Context)
859 -- OR ( (Recinfo.ussgl_trx_code_context IS NULL)
860 -- AND (X_Ussgl_Trx_Code_Context IS NULL)))
861 AND ( (Recinfo.withholding_status_lookup_code = X_Withholding_Status_Lkup_Code)
862 OR ( (Recinfo.withholding_status_lookup_code IS NULL)
863 AND (X_Withholding_Status_Lkup_Code IS NULL)))
864 AND ( (Recinfo.reconciliation_batch_id = X_Reconciliation_Batch_Id)
865 OR ( (Recinfo.reconciliation_batch_id IS NULL)
866 AND (X_Reconciliation_Batch_Id IS NULL)))
867 AND ( (Recinfo.cleared_base_amount = X_Cleared_Base_Amount)
868 OR ( (Recinfo.cleared_base_amount IS NULL)
869 AND (X_Cleared_Base_Amount IS NULL)))
870 AND ( (Recinfo.cleared_exchange_rate = X_Cleared_Exchange_Rate)
871 OR ( (Recinfo.cleared_exchange_rate IS NULL)
872 AND (X_Cleared_Exchange_Rate IS NULL)))
873 AND ( (Recinfo.cleared_exchange_date = X_Cleared_Exchange_Date)
874 OR ( (Recinfo.cleared_exchange_date IS NULL)
875 AND (X_Cleared_Exchange_Date IS NULL)))
876 AND ( (Recinfo.cleared_exchange_rate_type = X_Cleared_Exchange_Rate_Type)
877 OR ( (Recinfo.cleared_exchange_rate_type IS NULL)
878 AND (X_Cleared_Exchange_Rate_Type IS NULL)))
879 AND ( (Recinfo.address_line4 = X_Address_Line4)
880 OR ( (Recinfo.address_line4 IS NULL)
881 AND (X_Address_Line4 IS NULL)))
882 AND ( (Recinfo.county = X_County)
883 OR ( (Recinfo.county IS NULL)
884 AND (X_County IS NULL)))
885 AND ( (NVL(Recinfo.address_style,'DEFAULT') = X_Address_Style)
886 OR ( (Recinfo.address_style IS NULL)
887 AND (X_Address_Style IS NULL)))
888 /* Bug 6628204. Vnedor_Id and Vendor_site_Id is not there for
889 paying Payment Request type invoice */
890 AND ( (Recinfo.vendor_id = x_vendor_id)
891 OR ( (Recinfo.vendor_id IS NULL)
892 AND (x_vendor_id IS NULL)))
893 AND ( (Recinfo.vendor_site_id = x_vendor_site_id)
894 OR ( (Recinfo.vendor_site_id IS NULL)
895 AND (x_vendor_site_id IS NULL)))
896 AND ( (Recinfo.exchange_rate = X_Exchange_Rate)
897 OR ( (Recinfo.exchange_rate IS NULL)
898 AND (X_Exchange_Rate IS NULL)))
899 AND ( (Recinfo.exchange_date = X_Exchange_Date)
900 OR ( (Recinfo.exchange_date IS NULL)
901 AND (X_Exchange_Date IS NULL)))
902 AND ( (Recinfo.exchange_rate_type = X_Exchange_Rate_Type)
903 OR ( (Recinfo.exchange_rate_type IS NULL)
904 AND (X_Exchange_Rate_Type IS NULL)))
905 AND ( (Recinfo.base_amount = X_Base_Amount)
906 OR ( (Recinfo.base_amount IS NULL)
907 AND (X_Base_Amount IS NULL)))
908 AND ( (Recinfo.checkrun_id = X_Checkrun_Id)
909 OR ( (Recinfo.checkrun_id IS NULL)
910 AND (X_Checkrun_Id IS NULL)))
911 AND ( (Recinfo.global_attribute_category = X_global_attribute_category)
912 OR ( (Recinfo.global_attribute_category IS NULL)
913 AND (X_global_attribute_category IS NULL)))
914 AND ( (Recinfo.global_attribute1 = X_global_attribute1)
915 OR ( (Recinfo.global_attribute1 IS NULL)
916 AND (X_global_attribute1 IS NULL)))
917 AND ( (Recinfo.global_attribute2 = X_global_attribute2)
918 OR ( (Recinfo.global_attribute2 IS NULL)
919 AND (X_global_attribute2 IS NULL)))
920 AND ( (Recinfo.global_attribute3 = X_global_attribute3)
921 OR ( (Recinfo.global_attribute3 IS NULL)
922 AND (X_global_attribute3 IS NULL)))
923 AND ( (Recinfo.global_attribute4 = X_global_attribute4)
924 OR ( (Recinfo.global_attribute4 IS NULL)
925 AND (X_global_attribute4 IS NULL)))
926 AND ( (Recinfo.global_attribute5 = X_global_attribute5)
927 OR ( (Recinfo.global_attribute5 IS NULL)
928 AND (X_global_attribute5 IS NULL)))
929 AND ( (Recinfo.global_attribute6 = X_global_attribute6)
930 OR ( (Recinfo.global_attribute6 IS NULL)
931 AND (X_global_attribute6 IS NULL)))
932 AND ( (Recinfo.global_attribute7 = X_global_attribute7)
933 OR ( (Recinfo.global_attribute7 IS NULL)
934 AND (X_global_attribute7 IS NULL)))
935 AND ( (Recinfo.global_attribute8 = X_global_attribute8)
936 OR ( (Recinfo.global_attribute8 IS NULL)
937 AND (X_global_attribute8 IS NULL)))
938 AND ( (Recinfo.global_attribute9 = X_global_attribute9)
939 OR ( (Recinfo.global_attribute9 IS NULL)
940 AND (X_global_attribute9 IS NULL)))
941 AND ( (Recinfo.global_attribute10 = X_global_attribute10)
942 OR ( (Recinfo.global_attribute10 IS NULL)
943 AND (X_global_attribute10 IS NULL)))
944 AND ( (Recinfo.global_attribute11 = X_global_attribute11)
945 OR ( (Recinfo.global_attribute11 IS NULL)
946 AND (X_global_attribute11 IS NULL)))
947 AND ( (Recinfo.global_attribute12 = X_global_attribute12)
948 OR ( (Recinfo.global_attribute12 IS NULL)
949 AND (X_global_attribute12 IS NULL)))
950 AND ( (Recinfo.global_attribute13 = X_global_attribute13)
951 OR ( (Recinfo.global_attribute13 IS NULL)
952 AND (X_global_attribute13 IS NULL)))
953 AND ( (Recinfo.global_attribute14 = X_global_attribute14)
954 OR ( (Recinfo.global_attribute14 IS NULL)
955 AND (X_global_attribute14 IS NULL)))
956 AND ( (Recinfo.global_attribute15 = X_global_attribute15)
957 OR ( (Recinfo.global_attribute15 IS NULL)
958 AND (X_global_attribute15 IS NULL)))
959 AND ( (Recinfo.global_attribute16 = X_global_attribute16)
960 OR ( (Recinfo.global_attribute16 IS NULL)
961 AND (X_global_attribute16 IS NULL)))
962 AND ( (Recinfo.global_attribute17 = X_global_attribute17)
963 OR ( (Recinfo.global_attribute17 IS NULL)
964 AND (X_global_attribute17 IS NULL)))
965 AND ( (Recinfo.global_attribute18 = X_global_attribute18)
966 OR ( (Recinfo.global_attribute18 IS NULL)
967 AND (X_global_attribute18 IS NULL)))
968 AND ( (Recinfo.global_attribute19 = X_global_attribute19)
969 OR ( (Recinfo.global_attribute19 IS NULL)
970 AND (X_global_attribute19 IS NULL)))
971 AND ( (Recinfo.global_attribute20 = X_global_attribute20)
972 OR ( (Recinfo.global_attribute20 IS NULL)
973 AND (X_global_attribute20 IS NULL)))
974 AND ( (Recinfo.transfer_priority = X_transfer_priority)
975 OR ( (Recinfo.transfer_priority IS NULL)
976 AND (X_transfer_priority IS NULL)))
977 AND ( (Recinfo.maturity_exchange_rate_type = X_maturity_exchange_rate_type)
978 OR ( (Recinfo.maturity_exchange_rate_type IS NULL)
979 AND (X_maturity_exchange_rate_type IS NULL)))
980 AND ( (Recinfo.maturity_exchange_date = X_maturity_exchange_date)
981 OR ( (Recinfo.maturity_exchange_date IS NULL)
982 AND (X_maturity_exchange_date IS NULL)))
983 AND ( (Recinfo.maturity_exchange_rate = X_maturity_exchange_rate)
984 OR ( (Recinfo.maturity_exchange_rate IS NULL)
985 AND (X_maturity_exchange_rate IS NULL)))
986 AND ( (Recinfo.description = X_description)
987 OR ( (Recinfo.description IS NULL)
988 AND (X_description IS NULL)))
989 AND ( (Recinfo.anticipated_value_date = X_anticipated_value_date)
990 OR ( (Recinfo.anticipated_value_date IS NULL)
991 AND (X_anticipated_value_date IS NULL)))
992
993 AND ( (Recinfo.actual_value_date = X_actual_value_date)
994 OR ( (Recinfo.actual_value_date IS NULL)
995 AND (X_actual_value_date IS NULL)))
996
997 AND ( (Recinfo.payment_method_code = x_payment_method_code)
998 OR ( (Recinfo.payment_method_code IS NULL)
999 AND (x_payment_method_code IS NULL)))
1000
1001 AND ( (Recinfo.payment_profile_id = x_payment_profile_id)
1002 OR ( (Recinfo.payment_profile_id IS NULL)
1003 AND (x_payment_profile_id IS NULL)))
1004 AND ( (Recinfo.bank_charge_bearer = x_bank_charge_bearer)
1005 OR ( (Recinfo.bank_charge_bearer IS NULL)
1006 AND (x_bank_charge_bearer IS NULL)))
1007 AND ( (Recinfo.settlement_priority = x_settlement_priority)
1008 OR ( (Recinfo.settlement_priority IS NULL)
1009 AND (x_settlement_priority IS NULL)))
1010 --Bug5949912, bug6312110
1011 AND ( (Recinfo.payment_document_id = x_payment_document_id)
1012 OR( (Recinfo.payment_document_id IS NULL)
1013 AND (x_payment_document_id IS NULL))
1014 OR( (Recinfo.payment_method_lookup_code is NOT NULL)
1015 AND (x_payment_document_id is NULL)))
1016 AND ( (Recinfo.party_id = x_party_id)
1017 OR ( (Recinfo.party_id IS NULL)
1018 AND (x_party_id IS NULL)))
1019 AND ( (Recinfo.party_site_id = x_party_site_id)
1020 OR ( (Recinfo.party_site_id IS NULL)
1021 AND (x_party_site_id IS NULL)))
1022 AND ( (Recinfo.legal_entity_id = x_legal_entity_id)
1023 OR ( (Recinfo.legal_entity_id IS NULL)
1024 AND (x_legal_entity_id IS NULL)))
1025 AND ( (Recinfo.payment_id = x_payment_id)
1026 OR ( (Recinfo.payment_id IS NULL)
1027 AND (x_payment_id IS NULL)))
1028 AND ( (Recinfo.remit_to_supplier_id = x_remit_to_supplier_id)
1029 OR ( (Recinfo.remit_to_supplier_id IS NULL)
1030 AND (x_remit_to_supplier_id IS NULL)))
1031 AND ( (Recinfo.remit_to_supplier_site_id = x_remit_to_supplier_site_id)
1032 OR ( (Recinfo.remit_to_supplier_site_id IS NULL)
1033 AND (x_remit_to_supplier_site_id IS NULL)))
1034 AND ( (Recinfo.relationship_id = x_relationship_id)
1035 OR ( (Recinfo.relationship_id IS NULL)
1036 AND (x_relationship_id IS NULL)))
1037 AND ( (Recinfo.paycard_reference_id = x_paycard_reference_id)
1038 OR ( (Recinfo.paycard_reference_id IS NULL)
1039 AND (x_paycard_reference_id IS NULL)))
1040 /* Added for NOEX */
1041 AND ( (Recinfo.acknowledged_flag = x_acknowledged_flag)
1042 OR ( (Recinfo.acknowledged_flag IS NULL)
1043 AND (x_acknowledged_flag IS NULL)))
1044 ) then
1045
1046 /* Added for bug#6976792 Start */
1047 if ( Recinfo.attribute_category is null
1048 AND Recinfo.payment_type_flag = 'A'
1049 )
1050 OR
1051 ( (Recinfo.attribute_category = X_Attribute_Category)
1052 OR ( (Recinfo.attribute_category IS NULL)
1053 AND (X_Attribute_Category IS NULL))
1054 )
1055 then
1056 return;
1057 else
1058 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1059 APP_EXCEPTION.Raise_Exception;
1060 end if;
1061 /* Added for bug#6976792 End */
1062 /* return; commented for bug#6976792 */
1063 else
1064 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1065 APP_EXCEPTION.Raise_Exception;
1066 end if;
1067 else
1068 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1069 APP_EXCEPTION.Raise_Exception;
1070 end if;
1071
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 IF (SQLCODE <> -20001) THEN
1075 IF (SQLCODE = -54) THEN
1076 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1077 ELSE
1078 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1079 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1080 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1081 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1082 ', CHECK_ID = ' || TO_CHAR(X_Check_Id));
1083 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1084 END IF;
1085 END IF;
1086 APP_EXCEPTION.RAISE_EXCEPTION;
1087
1088 END Lock_Row;
1089
1090
1091
1092 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1093 X_Amount NUMBER,
1094 X_Ce_Bank_Acct_Use_Id NUMBER,
1095 X_Bank_Account_Name VARCHAR2,
1096 X_Check_Date DATE,
1097 X_Check_Id NUMBER,
1098 X_Check_Number NUMBER,
1099 X_Currency_Code VARCHAR2,
1100 X_Last_Updated_By NUMBER,
1101 X_Last_Update_Date DATE,
1102 --IBY:SP X_Payment_Method_Lookup_Code VARCHAR2,
1103 X_Payment_Type_Flag VARCHAR2,
1104 X_Address_Line1 VARCHAR2 DEFAULT NULL,
1105 X_Address_Line2 VARCHAR2 DEFAULT NULL,
1106 X_Address_Line3 VARCHAR2 DEFAULT NULL,
1107 X_Checkrun_Name VARCHAR2 DEFAULT NULL,
1108 X_Check_Format_Id NUMBER DEFAULT NULL,
1109 X_Check_Stock_Id NUMBER DEFAULT NULL,
1110 X_City VARCHAR2 DEFAULT NULL,
1111 X_Country VARCHAR2 DEFAULT NULL,
1112 X_Last_Update_Login NUMBER DEFAULT NULL,
1113 X_Status_Lookup_Code VARCHAR2 DEFAULT NULL,
1114 X_Vendor_Name VARCHAR2 DEFAULT NULL,
1115 X_Vendor_Site_Code VARCHAR2 DEFAULT NULL,
1116 X_External_Bank_Account_Id NUMBER,
1117 X_Zip VARCHAR2 DEFAULT NULL,
1118 X_Bank_Account_Num VARCHAR2 DEFAULT NULL,
1119 X_Bank_Account_Type VARCHAR2 DEFAULT NULL,
1120 X_Bank_Num VARCHAR2 DEFAULT NULL,
1121 X_Check_Voucher_Num NUMBER DEFAULT NULL,
1122 X_Cleared_Amount NUMBER DEFAULT NULL,
1123 X_Cleared_Date DATE DEFAULT NULL,
1124 X_Doc_Category_Code VARCHAR2 DEFAULT NULL,
1125 X_Doc_Sequence_Id NUMBER DEFAULT NULL,
1126 X_Doc_Sequence_Value NUMBER DEFAULT NULL,
1127 X_Province VARCHAR2 DEFAULT NULL,
1128 X_Released_Date DATE DEFAULT NULL,
1129 X_Released_By NUMBER DEFAULT NULL,
1130 X_State VARCHAR2 DEFAULT NULL,
1131 X_Stopped_Date DATE DEFAULT NULL,
1132 X_Stopped_By NUMBER DEFAULT NULL,
1133 X_Void_Date DATE DEFAULT NULL,
1134 X_Attribute1 VARCHAR2 DEFAULT NULL,
1135 X_Attribute10 VARCHAR2 DEFAULT NULL,
1136 X_Attribute11 VARCHAR2 DEFAULT NULL,
1137 X_Attribute12 VARCHAR2 DEFAULT NULL,
1138 X_Attribute13 VARCHAR2 DEFAULT NULL,
1139 X_Attribute14 VARCHAR2 DEFAULT NULL,
1140 X_Attribute15 VARCHAR2 DEFAULT NULL,
1141 X_Attribute2 VARCHAR2 DEFAULT NULL,
1142 X_Attribute3 VARCHAR2 DEFAULT NULL,
1143 X_Attribute4 VARCHAR2 DEFAULT NULL,
1144 X_Attribute5 VARCHAR2 DEFAULT NULL,
1145 X_Attribute6 VARCHAR2 DEFAULT NULL,
1146 X_Attribute7 VARCHAR2 DEFAULT NULL,
1147 X_Attribute8 VARCHAR2 DEFAULT NULL,
1148 X_Attribute9 VARCHAR2 DEFAULT NULL,
1149 X_Attribute_Category VARCHAR2 DEFAULT NULL,
1150 X_Future_Pay_Due_Date DATE DEFAULT NULL,
1151 X_Treasury_Pay_Date DATE DEFAULT NULL,
1152 X_Treasury_Pay_Number NUMBER DEFAULT NULL,
1153 -- Removed for bug 4277744
1154 -- X_Ussgl_Transaction_Code VARCHAR2 DEFAULT NULL,
1155 -- X_Ussgl_Trx_Code_Context VARCHAR2 DEFAULT NULL,
1156 X_Withholding_Status_Lkup_Code VARCHAR2 DEFAULT NULL,
1157 X_Reconciliation_Batch_Id NUMBER DEFAULT NULL,
1158 X_Cleared_Base_Amount NUMBER DEFAULT NULL,
1159 X_Cleared_Exchange_Rate NUMBER DEFAULT NULL,
1160 X_Cleared_Exchange_Date DATE DEFAULT NULL,
1161 X_Cleared_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
1162 X_Address_Line4 VARCHAR2 DEFAULT NULL,
1163 X_County VARCHAR2 DEFAULT NULL,
1164 X_Address_Style VARCHAR2 DEFAULT NULL,
1165 X_Org_Id NUMBER DEFAULT NULL,
1166 X_Vendor_Id NUMBER,
1167 X_Vendor_Site_Id NUMBER,
1168 X_Exchange_Rate NUMBER DEFAULT NULL,
1169 X_Exchange_Date DATE DEFAULT NULL,
1170 X_Exchange_Rate_Type VARCHAR2 DEFAULT NULL,
1171 X_Base_Amount NUMBER DEFAULT NULL,
1172 X_Checkrun_Id NUMBER DEFAULT NULL,
1173 X_global_attribute_category VARCHAR2 DEFAULT NULL,
1174 X_global_attribute1 VARCHAR2 DEFAULT NULL,
1175 X_global_attribute2 VARCHAR2 DEFAULT NULL,
1176 X_global_attribute3 VARCHAR2 DEFAULT NULL,
1177 X_global_attribute4 VARCHAR2 DEFAULT NULL,
1178 X_global_attribute5 VARCHAR2 DEFAULT NULL,
1179 X_global_attribute6 VARCHAR2 DEFAULT NULL,
1180 X_global_attribute7 VARCHAR2 DEFAULT NULL,
1181 X_global_attribute8 VARCHAR2 DEFAULT NULL,
1182 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1183 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1184 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1185 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1186 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1187 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1188 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1189 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1190 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1191 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1192 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1193 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1194 X_transfer_priority VARCHAR2 DEFAULT NULL,
1195 X_maturity_exchange_rate_type VARCHAR2 DEFAULT NULL,
1196 X_maturity_exchange_date DATE DEFAULT NULL,
1197 X_maturity_exchange_rate NUMBER DEFAULT NULL,
1198 X_description VARCHAR2 DEFAULT NULL,
1199 X_anticipated_value_date DATE DEFAULT NULL,
1200 X_actual_value_date DATE DEFAULT NULL,
1201 x_payment_method_code VARCHAR2 DEFAULT NULL,
1202 x_payment_profile_id NUMBER DEFAULT NULL,
1203 x_bank_charge_bearer VARCHAR2 DEFAULT NULL,
1204 x_settlement_priority VARCHAR2 DEFAULT NULL,
1205 x_payment_document_id NUMBER DEFAULT NULL,
1206 x_party_id NUMBER DEFAULT NULL,
1207 x_party_site_id NUMBER DEFAULT NULL,
1208 x_legal_entity_id NUMBER DEFAULT NULL,
1209 x_payment_id NUMBER DEFAULT NULL,
1210 X_calling_sequence IN VARCHAR2,
1211 X_Remit_To_Supplier_Name VARCHAR2 DEFAULT NULL,
1212 X_Remit_To_Supplier_Id Number DEFAULT NULL,
1213 X_Remit_To_Supplier_Site VARCHAR2 DEFAULT NULL,
1214 X_Remit_To_Supplier_Site_Id NUMBER DEFAULT NULL,
1215 X_Relationship_Id NUMBER DEFAULT NULL,
1216 X_paycard_authorization_number VARCHAR2 DEFAULT NULL,
1217 X_paycard_reference_id NUMBER DEFAULT NULL
1218 ) IS
1219 current_calling_sequence VARCHAR2(2000);
1220 debug_info VARCHAR2(100);
1221 l_old_status_lookup_code AP_CHECKS.status_lookup_code%TYPE;
1222 BEGIN
1223 -- Update the calling sequence
1224 --
1225 current_calling_sequence := 'AP_CHECKS_PKG.UPDATE_ROW<-' ||
1226 X_calling_sequence;
1227
1228 -- We need to check if the payment is being matured/unmatured during this
1229 -- updated, i.e. if the status_lookup_code is going from ISSUED to MATURED
1230 -- or vice-versa. If yes, then we want to insert/delete a row in the
1231 -- ap_payment_history table.
1232 -- I am putting the logic to figure out NOCOPY if the status has changed in this
1233 -- procedure because it is too messy and bug prone to put it in the form.
1234 -- Some of the issues are - we will need to track the previous and new value
1235 -- of the displayed field and then retrieve the lookup_codes to figure out NOCOPY
1236 -- whether this has happened or not. This might be done in the WVI. However,
1237 -- the actual insert/delete should be in the pre-update (maybe). What if the
1238 -- user flips it back and forth a few times and we try to delete when no
1239 -- row exists ......
1240 -- There is a bit of performance hit here 'cos we are checking for this
1241 -- even if the user did not touch this field, but that seems to be a fair
1242 -- trade off. Of course, we should do this only if the payment is future
1243 -- dated and X_status_lookup_code is either 'ISSUED' or 'NEGOTIABLE'.
1244
1245 If ((X_future_pay_due_date is NOT NULL) AND
1246 (X_status_lookup_code in ('ISSUED', 'NEGOTIABLE')))
1247 Then
1248
1249 debug_info := 'Retrieve existing status_lookup_code in the DB';
1250 SELECT status_lookup_code
1251 INTO l_old_status_lookup_code
1252 FROM ap_checks_all
1253 WHERE rowid = X_RowID;
1254
1255 If (l_old_status_lookup_code = 'ISSUED' AND
1256 X_status_lookup_code = 'NEGOTIABLE')
1257 Then
1258 debug_info := 'Insert row in Ap_Payment_History for payment maturity';
1259 ap_reconciliation_pkg.recon_payment_history
1260 (X_CHECKRUN_ID => NULL,
1261 X_CHECK_ID => X_check_id,
1262 X_TRANSACTION_TYPE => 'PAYMENT MATURITY',
1263 X_ACCOUNTING_DATE => X_future_pay_due_date,
1264 X_CLEARED_DATE => NULL,
1265 X_TRANSACTION_AMOUNT => X_amount,
1266 X_ERROR_AMOUNT => NULL,
1267 X_CHARGE_AMOUNT => NULL,
1268 X_CURRENCY_CODE => X_currency_code,
1269 X_EXCHANGE_RATE_TYPE => X_maturity_exchange_rate_type,
1270 X_EXCHANGE_RATE_DATE => X_maturity_exchange_date,
1271 X_EXCHANGE_RATE => X_maturity_exchange_rate,
1272 X_MATCHED_FLAG => NULL,
1273 X_ACTUAL_VALUE_DATE => NULL,
1274 X_CREATION_DATE => sysdate,
1275 X_CREATED_BY => X_last_updated_by,
1276 X_LAST_UPDATE_DATE => sysdate,
1277 X_LAST_UPDATED_BY => X_last_updated_by,
1278 X_LAST_UPDATE_LOGIN => X_last_update_login,
1279 X_PROGRAM_UPDATE_DATE => NULL,
1280 X_PROGRAM_APPLICATION_ID => NULL,
1281 X_PROGRAM_ID => NULL,
1282 X_REQUEST_ID => NULL,
1283 X_CALLING_SEQUENCE => current_calling_sequence);
1284 Elsif (l_old_status_lookup_code = 'NEGOTIABLE' AND
1285 X_status_lookup_code = 'ISSUED')
1286 Then
1287 debug_info := 'Delete maturity row from ap_payment_history';
1288 ap_reconciliation_pkg.recon_payment_history
1289 (X_CHECKRUN_ID => NULL,
1290 X_CHECK_ID => X_check_id,
1291 X_TRANSACTION_TYPE => 'PAYMENT MATURITY REVERSAL',
1292 X_ACCOUNTING_DATE => X_future_pay_due_date,
1293 X_CLEARED_DATE => NULL,
1294 X_TRANSACTION_AMOUNT => X_amount,
1295 X_ERROR_AMOUNT => NULL,
1296 X_CHARGE_AMOUNT => NULL,
1297 X_CURRENCY_CODE => X_currency_code,
1298 X_EXCHANGE_RATE_TYPE => X_maturity_exchange_rate_type,
1299 X_EXCHANGE_RATE_DATE => X_maturity_exchange_date,
1300 X_EXCHANGE_RATE => X_maturity_exchange_rate,
1301 X_MATCHED_FLAG => NULL,
1302 X_ACTUAL_VALUE_DATE => NULL,
1303 X_CREATION_DATE => sysdate,
1304 X_CREATED_BY => X_last_updated_by,
1305 X_LAST_UPDATE_DATE => sysdate,
1306 X_LAST_UPDATED_BY => X_last_updated_by,
1307 X_LAST_UPDATE_LOGIN => X_last_update_login,
1308 X_PROGRAM_UPDATE_DATE => NULL,
1309 X_PROGRAM_APPLICATION_ID => NULL,
1310 X_PROGRAM_ID => NULL,
1311 X_REQUEST_ID => NULL,
1312 X_CALLING_SEQUENCE => current_calling_sequence);
1313 End If;
1314
1315 End IF;
1316
1317 debug_info := 'Update ap_checks';
1318 AP_AC_TABLE_HANDLER_PKG.Update_Row(
1319 X_Rowid,
1320 X_Amount,
1321 X_Ce_Bank_Acct_Use_Id,
1322 X_Bank_Account_Name,
1323 X_Check_Date,
1324 X_Check_Id,
1325 X_Check_Number,
1326 X_Currency_Code,
1327 X_Last_Updated_By,
1328 X_Last_Update_Date,
1329 --IBY:SP X_Payment_Method_Lookup_Code,
1330 X_Payment_Type_Flag,
1331 X_Address_Line1,
1332 X_Address_Line2,
1333 X_Address_Line3,
1334 X_Checkrun_Name,
1335 X_Check_Format_Id,
1336 X_Check_Stock_Id,
1337 X_City,
1338 X_Country,
1339 X_Last_Update_Login,
1340 X_Status_Lookup_Code,
1341 X_Vendor_Name,
1342 X_Vendor_Site_Code,
1343 X_External_Bank_Account_Id,
1344 X_Zip,
1345 X_Bank_Account_Num,
1346 X_Bank_Account_Type,
1347 X_Bank_Num,
1348 X_Check_Voucher_Num,
1349 X_Cleared_Amount,
1350 X_Cleared_Date,
1351 X_Doc_Category_Code,
1352 X_Doc_Sequence_Id,
1353 X_Doc_Sequence_Value,
1354 X_Province,
1355 X_Released_Date,
1356 X_Released_By,
1357 X_State,
1358 X_Stopped_Date,
1359 X_Stopped_By,
1360 X_Void_Date,
1361 X_Attribute1,
1362 X_Attribute10,
1363 X_Attribute11,
1364 X_Attribute12,
1365 X_Attribute13,
1366 X_Attribute14,
1367 X_Attribute15,
1368 X_Attribute2,
1369 X_Attribute3,
1370 X_Attribute4,
1371 X_Attribute5,
1372 X_Attribute6,
1373 X_Attribute7,
1374 X_Attribute8,
1375 X_Attribute9,
1376 X_Attribute_Category,
1377 X_Future_Pay_Due_Date,
1378 X_Treasury_Pay_Date,
1379 X_Treasury_Pay_Number,
1380 -- Removed for bug 4277744
1381 -- X_Ussgl_Transaction_Code,
1382 -- X_Ussgl_Trx_Code_Context,
1383 X_Withholding_Status_Lkup_Code,
1384 X_Reconciliation_Batch_Id,
1385 X_Cleared_Base_Amount,
1386 X_Cleared_Exchange_Rate,
1387 X_Cleared_Exchange_Date,
1388 X_Cleared_Exchange_Rate_Type,
1389 X_Address_Line4,
1390 X_County,
1391 X_Address_Style,
1392 X_Org_Id,
1393 X_Vendor_Id,
1394 X_Vendor_Site_Id,
1395 X_Exchange_Rate,
1396 X_Exchange_Date,
1397 X_Exchange_Rate_Type,
1398 X_Base_Amount,
1399 X_Checkrun_Id,
1400 X_global_attribute_category,
1401 X_global_attribute1,
1402 X_global_attribute2,
1403 X_global_attribute3,
1404 X_global_attribute4,
1405 X_global_attribute5,
1406 X_global_attribute6,
1407 X_global_attribute7,
1408 X_global_attribute8,
1409 X_global_attribute9,
1410 X_global_attribute10,
1411 X_global_attribute11,
1412 X_global_attribute12,
1413 X_global_attribute13,
1414 X_global_attribute14,
1415 X_global_attribute15,
1416 X_global_attribute16,
1417 X_global_attribute17,
1418 X_global_attribute18,
1419 X_global_attribute19,
1420 X_global_attribute20,
1421 X_transfer_priority,
1422 X_maturity_exchange_rate_type,
1423 X_maturity_exchange_date,
1424 X_maturity_exchange_rate,
1425 X_description,
1426 X_anticipated_value_date,
1427 X_actual_value_date,
1428 x_payment_method_code,
1429 x_payment_profile_id,
1430 x_bank_charge_bearer,
1431 x_settlement_priority,
1432 x_payment_document_id,
1433 x_party_id,
1434 x_party_site_id,
1435 x_legal_entity_id,
1436 x_payment_id,
1437 current_calling_sequence,
1438 X_Remit_To_Supplier_Name,
1439 X_Remit_To_Supplier_Id,
1440 X_Remit_To_Supplier_Site,
1441 X_Remit_To_Supplier_Site_Id,
1442 X_Relationship_Id,
1443 x_paycard_authorization_number,
1444 x_paycard_reference_id
1445 );
1446
1447
1448 if (SQL%NOTFOUND) then
1449 Raise NO_DATA_FOUND;
1450 end if;
1451
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 IF (SQLCODE <> -20001) THEN
1455 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1456 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1457 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1458 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1459 ', CHECK_ID = ' || TO_CHAR(X_Check_Id));
1460 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1461 END IF;
1462 APP_EXCEPTION.RAISE_EXCEPTION;
1463
1464 END Update_Row;
1465
1466 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1467 X_calling_sequence IN VARCHAR2) IS
1468 current_calling_sequence VARCHAR2(2000);
1469 debug_info VARCHAR2(100);
1470
1471 BEGIN
1472 -- Update the calling sequence
1473 --
1474 current_calling_sequence := 'AP_CHECKS_PKG.DELETE_ROW<-' ||
1475 X_calling_sequence;
1476 debug_info := 'Delete from ap_checks';
1477 AP_AC_TABLE_HANDLER_PKG.Delete_Row(
1478 X_Rowid,
1479 current_calling_sequence);
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 IF (SQLCODE <> -20001) THEN
1484 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1485 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1486 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1487 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
1488 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1489 END IF;
1490 APP_EXCEPTION.RAISE_EXCEPTION;
1491
1492 END Delete_Row;
1493
1494 -----------------------------------------------------------------------
1495 -- Function get_invoices_paid returns a comma delimited list of
1496 -- invoices paid by this check.
1497 --
1498 FUNCTION get_invoices_paid (l_check_id IN NUMBER)
1499 RETURN VARCHAR2
1500 IS
1501 l_inv_num AP_INVOICES.INVOICE_NUM%TYPE;
1502 l_inv_num_list VARCHAR2(2000) := NULL;
1503
1504 -------------------------------------------------------------------
1505 -- Declare cursor to return the Invoice number
1506 --
1507 CURSOR inv_num_cursor IS
1508 SELECT ai.invoice_num
1509 FROM ap_invoices ai,
1510 ap_invoice_payments aip
1511 WHERE aip.check_id = l_check_id
1512 AND aip.invoice_id = ai.invoice_id;
1513
1514 BEGIN
1515
1516 OPEN inv_num_cursor;
1517
1518 LOOP
1519 FETCH inv_num_cursor INTO l_inv_num;
1520 EXIT WHEN inv_num_cursor%NOTFOUND;
1521
1522 IF (l_inv_num_list IS NOT NULL) THEN
1523 l_inv_num_list := l_inv_num_list || ', ';
1524 END IF;
1525
1526 l_inv_num_list := l_inv_num_list || l_inv_num;
1527
1528 END LOOP;
1529
1530 CLOSE inv_num_cursor;
1531
1532 RETURN(l_inv_num_list);
1533
1534 END get_invoices_paid;
1535
1536 -----------------------------------------------------------------------
1537 -- Function is_maturity_accounted returns TRUE if the maturity event
1538 -- has been accounted for, i.e. a PAYMENT MATURITY row exists in
1539 -- ap_payment_history for this payment with posted_flag = 'Y'.
1540 -- Otherwise it returns a FALSE.
1541 --
1542 FUNCTION is_maturity_accounted(X_check_id IN NUMBER,
1543 X_calling_sequence IN VARCHAR2)
1544 return BOOLEAN IS
1545 current_calling_sequence VARCHAR2(2000);
1546 debug_info VARCHAR2(100);
1547 l_num_accounted_pay_hist NUMBER;
1548 BEGIN
1549 -- Update the calling sequence
1550 --
1551 current_calling_sequence := 'AP_CHECKS_PKG.is_maturity_accounted<-' ||
1552 X_calling_sequence;
1553 debug_info := 'Selecting from ap_payment_history';
1554 SELECT count(*)
1555 INTO l_num_accounted_pay_hist
1556 FROM ap_payment_history
1557 WHERE check_id = X_check_id
1558 AND transaction_type = 'PAYMENT MATURITY'
1559 AND posted_flag = 'Y';
1560
1561 If (l_num_accounted_pay_hist = 0)
1562 then
1563 return(FALSE);
1564 else
1565 return(TRUE);
1566 end if;
1567
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 IF (SQLCODE <> -20001) THEN
1571 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1572 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1573 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1574 FND_MESSAGE.SET_TOKEN('PARAMETERS','check_id = ' || to_char(X_check_id));
1575 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1576 END IF;
1577 APP_EXCEPTION.RAISE_EXCEPTION;
1578 END is_maturity_accounted;
1579
1580 -----------------------------------------------------------------------
1581 -- Function is_payment_matured returns TRUE if the maturity event
1582 -- has been created, i.e. a PAYMENT MATURITY row exists in
1583 -- ap_payment_history for this payment.
1584 -- Otherwise it returns a FALSE.
1585 --
1586 FUNCTION is_payment_matured(X_check_id IN NUMBER,
1587 X_calling_sequence IN VARCHAR2)
1588 return BOOLEAN IS
1589 current_calling_sequence VARCHAR2(2000);
1590 debug_info VARCHAR2(100);
1591 l_num_pay_hist NUMBER;
1592 BEGIN
1593 -- Update the calling sequence
1594 --
1595 current_calling_sequence := 'AP_CHECKS_PKG.is_payment_matured<-' ||
1596 X_calling_sequence;
1597 debug_info := 'Selecting from ap_payment_history';
1598 SELECT count(*)
1599 INTO l_num_pay_hist
1600 FROM ap_payment_history
1601 WHERE check_id = X_check_id
1602 AND transaction_type = 'PAYMENT MATURITY';
1603
1604 If (l_num_pay_hist = 0)
1605 then
1606 return(FALSE);
1607 else
1608 return(TRUE);
1609 end if;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 IF (SQLCODE <> -20001) THEN
1614 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1615 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1616 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1617 FND_MESSAGE.SET_TOKEN('PARAMETERS','check_id = ' || to_char(X_check_id));
1618 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1619 END IF;
1620 APP_EXCEPTION.RAISE_EXCEPTION;
1621 END is_payment_matured;
1622
1623
1624 -----------------------------------------------------------------------
1625 -- Function get_posting_status returns the payment posting status flag.
1626 --
1627 -- 'Y' - Posted
1628 -- 'S' - Selected
1629 -- 'P' - Partial
1630 -- 'N' - Unposted
1631 --
1632 FUNCTION get_posting_status(l_check_id IN NUMBER)
1633 RETURN VARCHAR2
1634 IS
1635 payment_posting_flag VARCHAR2(1);
1636 posting_flag VARCHAR2(1);
1637
1638 ---------------------------------------------------------------------
1639 -- Declare cursor to establish the payment-level posting flag
1640 --
1641 -- The first two selects simply look at the posting flags (cash and/or
1642 -- accrual) for the distributions. The rest is to cover one specific
1643 -- case when some of the distributions are fully posted (Y) and some
1644 -- are unposted (N). The status should be partial (P).
1645 --
1646
1647 -- Fix for 1375672. Modified the cursor to select the posted_flag from
1648 -- ap_invoice_payments and ap_payment_history. Previously, the cursor
1649 -- selected the cash_posted_flag and accrual_posted_flag (depending upon
1650 -- the accounting option) from ap_invoice_payments only, ignoring
1651 -- the ap_payment_history table.
1652
1653 -- bug 3676049 Posting status of check is irrespective of Payment Maturity
1654 -- when Payment Accounting = 'CLEARING ONLY'
1655 CURSOR posting_cursor IS
1656 SELECT posted_flag
1657 FROM ap_invoice_payments_all
1658 WHERE check_id = l_check_id
1659 UNION ALL
1660 SELECT posted_flag
1661 FROM ap_payment_history aph, ap_system_parameters asp
1662 WHERE check_id = l_check_id
1663 AND nvl(aph.org_id, -99) = nvl(asp.org_id, -99)
1664 AND (nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' or
1665 (nvl(asp.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY' and
1666 aph.transaction_type in
1667 ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
1668 'PAYMENT CLEARING ADJUSTED'))); --Bug 9789574
1669
1670 BEGIN
1671
1672 ---------------------------------------------------------------------
1673 -- Establish the payment-level posting flag
1674 --
1675 -- Use the following ordering sequence to determine the payment-level
1676 -- posting flag:
1677 -- 'S' - Selected
1678 -- 'N' - Unposted
1679 -- 'Y' - Posted
1680 -- 'P' - Partial
1681 --
1682 -- Initialize payment-level posting flag
1683 --
1684 -- Fix for 1375672. Modified the logic of this IF block to get the
1685 -- correct payment posting status. The logic works as follows: If all
1686 -- values, that are returned by the cursor, are 'Y' then status is
1687 -- 'Y', if all are 'N' then the status is 'N', otherwise the status
1688 -- is 'P' (Partial)
1689
1690 payment_posting_flag := 'X';
1691
1692 OPEN posting_cursor;
1693
1694 LOOP
1695 FETCH posting_cursor INTO posting_flag;
1696 EXIT WHEN posting_cursor%NOTFOUND;
1697
1698 IF (posting_flag = 'S') THEN
1699 payment_posting_flag := 'S';
1700 ELSIF (posting_flag = 'N' AND
1701 payment_posting_flag NOT IN ('S','Y','P')) THEN
1702 payment_posting_flag := 'N';
1703 ELSIF (posting_flag = 'Y' AND
1704 payment_posting_flag NOT IN ('S','N','P')) THEN
1705 payment_posting_flag := 'Y';
1706 ELSIF (payment_posting_flag <> 'S') THEN
1707 payment_posting_flag := 'P';
1708 END IF;
1709
1710 END LOOP;
1711
1712 CLOSE posting_cursor;
1713
1714 if (payment_posting_flag = 'X') then
1715 -- No distributions belong to this invoice; therefore,
1716 -- the payment-level posting status should be 'N'
1717 payment_posting_flag := 'N';
1718 end if;
1719
1720 RETURN(payment_posting_flag);
1721
1722 END get_posting_status;
1723
1724
1725 --Added for Payment Request
1726 -----------------------------------------------------------------------
1727 -- Procedure to subscribe to the payment event by other products
1728 -- This procedure checks the product registry table for all the
1729 -- products that have subscribed to a particular event and inturn
1730 -- calls the products API
1731 --
1732 -----------------------------------------------------------------------
1733 PROCEDURE Subscribe_To_Payment_Event
1734 (P_Event_Type IN VARCHAR2,
1735 P_Check_ID IN NUMBER,
1736 P_Application_ID IN NUMBER,
1737 P_Return_Status OUT NOCOPY VARCHAR2,
1738 P_Msg_Count OUT NOCOPY NUMBER,
1739 P_Msg_Data OUT NOCOPY VARCHAR2,
1740 P_Calling_Sequence IN VARCHAR2) IS
1741
1742
1743 current_calling_sequence VARCHAR2(2000);
1744 debug_info VARCHAR2(100);
1745
1746 l_stmt VARCHAR2(1000);
1747 l_return_status VARCHAR2(1);
1748 l_msg_count NUMBER;
1749 l_msg_data VARCHAR2(2000);
1750
1751
1752 CURSOR c_products_registered IS
1753 SELECT Reg_Application_ID,
1754 Registration_API
1755 FROM AP_Product_Registrations
1756 WHERE Reg_Application_ID = P_Application_ID
1757 AND Registration_Event_Type = P_Event_Type;
1758
1759
1760 BEGIN
1761
1762 -- Update the calling sequence
1763 --
1764 current_calling_sequence := 'AP_CHECKS_PKG.Subscribe_To_Payment_Event<-' ||
1765 P_calling_sequence;
1766
1767 debug_info := 'Calling the subscribe payment event API';
1768
1769 FOR c_product_rec IN c_products_registered
1770 LOOP
1771
1772 l_stmt := 'Begin ' ||
1773 c_product_rec.Registration_API ||
1774 '(:P_Event_Type,' ||
1775 ':P_Check_ID,' ||
1776 ':l_return_Status,' ||
1777 ':l_msg_count,' ||
1778 ':l_msg_data);' ||
1779 'End;';
1780
1781 EXECUTE IMMEDIATE l_stmt
1782 USING IN P_Event_Type,
1783 IN P_Check_ID,
1784 OUT l_return_status,
1785 OUT l_msg_count,
1786 OUT l_msg_data;
1787
1788 P_Return_Status := l_return_status;
1789 P_Msg_Count := l_msg_count;
1790 P_Msg_Data := l_msg_data;
1791
1792
1793 END LOOP;
1794
1795 EXCEPTION
1796
1797 WHEN OTHERS THEN
1798 IF (SQLCODE <> -20001) THEN
1799 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1800 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1801 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1802 FND_MESSAGE.SET_TOKEN('PARAMETERS','check_id = ' || to_char(P_Check_ID));
1803 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1804 END IF;
1805
1806 /* Start - BUG 14364091 - Logging */
1807 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
1808 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
1809 FND_FILE.PUT_LINE(FND_FILE.LOG, debug_info||' - '||systimestamp);
1810 /* End - BUG 14364091 - Logging */
1811
1812 APP_EXCEPTION.RAISE_EXCEPTION;
1813
1814 END Subscribe_To_Payment_Event;
1815
1816
1817 END AP_CHECKS_PKG;