Receipt Reconciliation Fails With APP-AR-999967188 The Receipt is in the Process Of Funds Capture Settlement

 

1. One possible cause for this error is an incomplete transaction on the Funds Capture side.

To verify if the transaction has been completed in Funds Capture, please run the following query (using the receipt number of the failing receipt as parameter):

SELECT status
  FROM iby_trxn_summaries_all
WHERE transactionid IN (
          SELECT transactionid
            FROM iby_fndcpt_tx_operations
           WHERE trxn_extension_id IN (
                                      SELECT payment_trxn_extension_id
                                        FROM ar_cash_receipts_all
                                       WHERE receipt_number =
                                                             '&receipt_number'))
   AND trxntypeid = 100;

If the above query returns a value of 100, please execute the Create Settlement Batches request, as explained in  Note  550146.1 How To Generate Remittance Format Report In Release 12.

2. If the value returned by the query is 0, this means that the transaction is completed on the Funds Capture side and the issue might be caused by a code bug.
get_settlement_status cursor in package ARP_REVERSE_RECEIPT did not include check for reqtype 'ORAPMTEFTCLOSEBATCH' . As result AR is unable to validate that transaction was settled in  Payments.

Version of ARREREVB.pls contains

Cursor get_settlement_status IS
SELECT summ.status
FROM iby_fndcpt_tx_operations op,iby_trxn_summaries_all summ
WHERE op.trxn_extension_id = p_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTCAPTURE', 'ORAPMTRETURN',
'ORAPMTCREDIT', 'ORAPMTVOID', 'ORAPMTBATCHREQ')
ORDER BY summ.trxnmid desc;

as we can see 'ORAPMTEFTCLOSEBATCH' is not included.

3. In case the value retrieved by the above query is different than 100 and 0, please log a service request against Oracle Payments/Funds Capture product for further analysis.

Solution:

To resolve the issue, apply Patch 10389524:R12.AR.B