Resolving Stuck Selected For Payment Cheques

Problem: A number of cheques were printed and then spoilt as the wrong starting number was entered. AP tried to run the payment again, then realized that the invoices were not able to be pulled. The info on the payments showed that the cheques were still selected for payments.

Solution: Refer to R12 Generic Data Fix (GDF) Patch for Payment stuck when Invoices On “Spoiled Checks” remain in “Selected For Payment” Status (Doc ID 1367272.1). Apply patch 16216890, then run scripts $IBY_TOP/patch/115/sql/iby_splt_pmts_sel.sql and $IBY_TOP/patch/115/sql/iby_splt_pmts_fix.sql.

I ran the diagnostics report (via Application Diagnostics responsibility) for the Payment Process Request Code in question. However, the report did not show any errors.

Since the issue was also present on the TEST instance, I decided to troubleshoot on this instance using MOS Doc ID 1367272.1. Checking ad_bugs, I found that patch 16216890 was already applied:

SQL> select bug_number from ad_bugs where bug_number = '16216890';

BUG_NUMBER
------------------------------
16216890

SQL>

Next, as directed by MOS Doc ID 1367272.1, I had to run $IBY_TOP/patch/115/sql/iby_splt_pmts_sel.sql, supplying the payment_instruction_id as the parameter. To obtain the payment_instruction_id parameter for this script, I first ran the following SQL:

SQL> r
  1  SELECT distinct payment_instruction_id
  2  FROM iby_payments_all
  3  WHERE payment_service_request_id IN
  4  (SELECT payment_service_request_id
  5  FROM iby_pay_service_requests
  6  WHERE calling_app_id = 200
  7* AND call_app_pay_service_req_code = '&checkrun_name')
Enter value for checkrun_name: test_checkrun_name
old   7: AND call_app_pay_service_req_code = '&checkrun_name')
new   7: AND call_app_pay_service_req_code = 'test_checkrun_name')

PAYMENT_INSTRUCTION_ID
----------------------
                 12345

SQL>

Then I ran $IBY_TOP/patch/115/sql/iby_splt_pmts_sel.sql:

bash-3.00$ sqlplus apps/apps @ iby_splt_pmts_sel.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Dec 1 16:46:01 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter value for instruction_id: 15487
File location::/usr/tmp/11877372-Sel-16:46:12.html

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.00$

The script produced an output file /usr/tmp/11877372-Sel-16:46:12.html, which I copied to the user’s home directory:

bash-3.00$ cp /usr/tmp/11877372-Sel-16:46:12.html ~/

Then I used WinSCP to copy it to my machine. The output file showed the affected instructions, payments and documents.

When the iby_splt_pmts_sel.sql script is run, it populates the data for the checkrun into temporary IBY tables. When the fix script is run, it checks these tables and then fixes the data in them.

After running the iby_splt_pmts_sel.sql script, I then ran the fix script iby_splt_pmts_fix.sql to fix the data:

 bash-3.00$ sqlplus apps/apps @ iby_splt_pmts_fix.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Dec 1 16:57:28 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

File location::/usr/tmp/11877372-fix-16:57:28.html

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.00$

Again, I copied the output file /usr/tmp/11877372-fix-16:57:28.html to the home directory and copied it to my computer using WinSCP.

Check the output file to ensure that the datafix was correctly applied, without any errors.

 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


This site uses Akismet to reduce spam. Learn how your comment data is processed.