[Oracle]APP-SQLAP-10062 The Payment Document Has No Associated Invoice Payments

Users reported that the system crashed halfway through processing data, causing the payment to be closed before the invoice was printed.

An error occurred while pressing the button: APP-SQLAP-10062 The Payment Document Has No Associated Invoice Payments.

Search Oracle related documents

R12: AP: APP-SQLAP-10062 Error Trying to Void a Manual Type Payment (Doc ID 1546742.1)

R12: AP/IBY: Generic Data Fix (GDF) Patch To Correct Checks with Missing Invoice Reference (Doc ID 2115968.1)

Use PL/SQL Developer to execute `@C:\ap_incorrect_checks_sel.sql` to confirm if there is a problem with the data. Alternatively, the following SQL should also be able to determine the issue.

Query1:

SELECT ac.check_id
, ac.amount
, ac.payment_id
, ac.status_lookup_code
, ac.currency_code
, ac.payment_type_flag
, ac.org_id
, ac.vendor_id
, ac.vendor_name
, ac.vendor_site_id
, aph.transaction_type
, aph.posted_flag
, aph.trx_pmt_amount
, aph.accounting_event_id
FROM ap_payment_history_all aph
, ap_checks_all ac
WHERE ac.check_id = aph.check_id(+)
AND ac.status_lookup_code NOT IN ('OVERFLOW','SET UP','SPOILED')
AND NVL(aph.historical_flag,'N') <> 'Y'
AND AC.CHECK_NUMBER=11802347
AND NOT EXISTS (SELECT '1' FROM ap_invoice_payments_all aip WHERE ac.check_id = aip.check_id)
AND ac.creation_date > (SELECT MIN(creation_date) FROM ad_bugs WHERE aru_release_name = 'R12')

Query2:

Select distinct ac.check_id,ac.checkrun_name,ac.checkrun_id,ac.payment_id
FROM ap_payment_history_all aph
, ap_checks_all ac
WHERE ac.check_id = aph.check_id(+)
AND ac.status_lookup_code NOT IN ('OVERFLOW','SET UP','SPOILED')
AND NVL(aph.historical_flag,'N') <> 'Y'
AND AC.CHECK_NUMBER=11802347
AND NOT EXISTS (SELECT '1' FROM ap_invoice_payments_all aip WHERE ac.check_id = aip.check_id)
AND ac.creation_date > (SELECT MIN(creation_date) FROM ad_bugs WHERE aru_release_name = 'R12')

Execute PL/SQL Developer to execute @C:\ap_incorrect_checks_fix.sql to correct the data.

Postscript:

One day, for some reason, the above methods suddenly stopped working. According to the standard operating procedure, an SR query should be initiated, but due to time constraints, I started reviewing the ap_incorrect_checks_fix.sql program and found that it performs the following actions.

1.  Querying the TEMP TABLE provides data generated by ap_incorrect_checks_sel.sql.

2.  Backup related tables, such as AP_CHECKS_ALL, AP_PAYMENT_HISTORY_ALL, and XLA_EVENTS.

3.  Formal information on the anomaly (see the equation below)

-Delete the erroneous record AP_CHECKS_ALL and use the CHECK_ID recorded in the TEMP file.

-Delete the erroneous record AP_PAYMENT_HISTORY_ALL and use the CHECK_ID recorded in the TEMP file.

-Update XLA_EVENTS to EVENT_STATUS_CODE = 'N' and PROCESS_STATUS_CODE = 'P'.

select * from AP_CHECKS_ALL where CHECK_NUMBER=11802347
select * from AP_CHECKS_ALL where CHECK_ID=2417002
select * from AP_PAYMENT_HISTORY_ALL where CHECK_ID=2417002
select * from XLA_EVENTS where EVENT_ID=3613439

create table AP_TEMP_DRIVER_17927828_AC as select * from AP_CHECKS_ALL where CHECK_ID=2417002;

create table AP_TEMP_DRIVER_17927828_APH as select * from AP_PAYMENT_HISTORY_ALL where CHECK_ID=2417002;

create table AP_TEMP_DRIVER_17927828_XE as select * from XLA_EVENTS where EVENT_ID=3613439;

select * from AP_TEMP_DRIVER_17927828_AC
select * from AP_TEMP_DRIVER_17927828_APH
select * from AP_TEMP_DRIVER_17927828_XE

DELETE FROM AP_CHECKS_ALL WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID FROM AP_TEMP_DRIVER_17927828_AC);

DELETE FROM AP_PAYMENT_HISTORY_ALL WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID FROM AP_TEMP_DRIVER_17927828_APH);

select * from fnd_user where user_id=2809 lud---2312

UPDATE XLA_EVENTS
        SET EVENT_STATUS_CODE = 'N',
            PROCESS_STATUS_CODE = 'P',
            last_updated_by = 2312,
            last_update_date = SYSDATE,
            last_update_login = (-1)
      WHERE APPLICATION_ID = 200
        AND EVENT_STATUS_CODE <> 'P'
        AND EVENT_ID IN (SELECT DISTINCT ACCOUNTING_EVENT_ID FROM AP_TEMP_DRIVER_17927828_APH);