Check the opening and closing balances in the control totals

Issue: 

Check the opening and closing balances in the control totals.

Solution:


The Bank Statement Import program checks if the Control end balance matches:
(Control begin balance) + (Control cr amount) - (Control dr amount)

If not, it shows the warning, "Check the opening and closing balances in the control totals."

Here's the steps to address the warning:

Case 1:
Bank statement file does not provide the opening and closing balances and these optional fields are not populated.
So CONTROL_TOTAL_DR and CONTROL_TOTAL_CR are populated but not the begin and end balance.

Workaround:
Either null out the CONTROL TOTAL DR and CONTROL TOTAL CR, or populate begin and end balance for the Bank Statement Import Parameters.

Case 2:
CONTROL_BEGIN_BALANCE and CONTROL_END_BALANCE is populated but not CONTROL_TOTAL_DR and CONTROL_TOTAL_CR

The issue can occurr when there is a missing transaction code setup.

1 - With the Bank Statement in the Interface tables , navigate to the Bank Statement Interface form.

2 - Check the Bank Transaction Codes assigned to the Bank Statement Lines.

3 - Ensure Bank Transaction Codes identified on the Bank Statement are correctly setup in the system prior to importing the bank statement.

Please check the information from bank statement loader TDD:
-----------------------------------------------------------
After you load SWIFT940 bank statement files into the open interface tables,
you may need to define new bank transaction codes in Cash Management.
SWIFT940 transaction codes represent the type of transaction. For example,
TRF represents transfers. SWIFT940 transaction codes do not, however, contain
information about the debit or credit nature of the transaction. Instead,
the Debit/Credit Mark field is used to differentiate debit and credit
entries, where D means debit and C means credit. When the Bank Statement
Loader program populates the TRX_CODE column in the Bank Statement Lines
Interface table, it appends the Debit/Credit Mark to the transaction code to
form a new code. For example, debit transfers are identified as TRFD and
credit transfers as TRFC. You must set up these new transaction codes before
you can import the bank statement information.


4 - Once this is checked and corrected ( if required ), make a change to the Bank Statement in the Interface form and save.

5 - Now, try to import the Bank Statement.

Unable to get The Matching Criteria In Reconciliation Open Interface For R12

Issue:

Unable to see the Open Interface tab in the Cash Management in available Form to match the Open interface transactions for reconciliation.




Fix:

Enable the "Use Reconciliation Open Interfaces" check box under "Open Interface Controls" in Bank Account Control screen.


1. Navigate to Bank account Enquiry screen : Setup -> Bank -> Bank Account

2. Find your bank and select for Update option.

3. Click on "Account Control"

4. Enable the "Use Reconciliation Open Interfaces" check box

5. Click on Finish Button

Now we can able to get the Open Interface Option enabled in available window.

The Reconciled and Reconciled but Unaccounted Status Mean For Payments

Account for Payment When Payment Clears Payables option is enabled.

Created invoice and accounted.
Created payment and accounted (status of payment is Negotiable).
Cleared payment - Status on payments form is Cleared but Unaccounted.
Accounted - status went to Cleared.
Reconciled - status went to Reconciled.



Cleared:- The bank has disbursed funds for the payment, and the payment has been cleared, but not matched to a bank statement within Oracle Cash Management. Payables uses this status when the Account for Payment When Payment Clears Payables option
is enabled.

Cleared But Unaccounted:- The bank has disbursed funds for the payment and the payment has been cleared but not matched to a bank statement within Oracle Cash Management. Payables uses this status when the Account for Payment When Payment Clears Payables option is disabled.

Reconciled:- The bank has disbursed funds for the payment, and the payment has been reconciled and matched to a bank statement in Oracle Cash Management. Payables uses this status when the Account for Payment When Payment Clears Payables option is enabled.

Reconciled But Unaccounted:- The bank has disbursed funds for the payment, and the payment has been reconciled and matched to a bank statement in Oracle Cash Management. Payables uses this status when the Account for Payment When Payment Clears Payables option is disabled.

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