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