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

使用者反應資料做到一半系統當機，造成付款時發票還沒打就關閉

按下作業時出現錯誤APP-SQLAP-10062 The Payment Document Has No Associated Invoice Payments

查詢Oracle相關文件

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)

使用PL/SQL DEV執行@C:\ap_incorrect_checks_sel.sql，確認是否是該筆資料有問題或者使用以下SQL應該也是可以判斷

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_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 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');

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 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');

執行PL/SQL DEV 執行@C:\ap_incorrect_checks_fix.sql，進行資料修正

後記
某一天忽然以上方法不知道為什麼不能用，按照標準作業程式要開SR詢問，但因時間緊迫，開始翻閱ap_incorrect_checks_fix.sql程式，發現他執行以下動作

1. 查詢TEMP TABLE，這是ap_incorrect_checks_sel.sql所產生的資料
2. 備份相關資料表，AP_CHECKS_ALL、AP_PAYMENT_HISTORY_ALL、XLA_EVENTS等
3. 異動正式資料(參閱下方程式)
• 刪除AP_CHECKS_ALL該筆錯誤資料，利用TEMP紀錄的CHECK_ID
• 刪除AP_PAYMENT_HISTORY_ALL該筆錯誤資料，利用TEMP紀錄的CHECK_ID
• 更新XLA_EVENTS的EVENT_STATUS_CODE = 'N'、PROCESS_STATUS_CODE='P'

DELETE FROM AP_CHECKS_ALL
 WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID
                      FROM AP_TEMP_DRIVER_17927828
                     WHERE process_flag = 'Y');

DELETE FROM AP_PAYMENT_HISTORY_ALL
 WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID
                      FROM AP_TEMP_DRIVER_17927828
                     WHERE process_flag = 'Y');

UPDATE XLA_EVENTS
        SET EVENT_STATUS_CODE   = 'N',
            PROCESS_STATUS_CODE = 'P',
            last_updated_by     = 9123,
            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
                          WHERE process_flag = 'Y');