Can you tell me the steps that Electronic Reconcile goes through to match transactions from the bank's download file and Microsoft Dynamics GP? Answer:
1. Electronic Reconcile reviews the CM Transaction table (CM20200), and copies the entire table into a temp table.
2. It then sets a date range in this temp table based on the Cutoff Date
used in the Reconcile Bank Statements window.
3. Once it has the range of downloaded transactions, it then sets another range in the temp table based on Transaction Amount, and then on Transaction Type (check, deposit, adjustment, etc.).
4. It then reviews the RECOND
column (CM20200) to see if the transaction has already been reconciled or not. If not, then Electronic Reconcile reviews what type (CMTRXTYPE
- CM20200) it is and tries to match the transaction based on the following criteria for each type:
MATCHING PROCESS FOR EACH TYPE:Checks/Credit Cards/EFT Transactions:
To match, all these criteria must be true:
- The Amount matches.
- The Check Number matches. (NOTE: The leading zeroes on the front of a check do not matter. GP strips them off and the matching process ignores any leading zeroes.)
- The transaction is a check type in Microsoft Dynamics GP. (ie. The record has a CMTRXYPE = '3' in the CM20200 SQL table.)
- The CHECKBOOK ID on the check in the CM20200 must be the checkbook ID you are working with in the Reconcile window.
- The Transaction Code on the bank source file is mapped in the CODES ENTRY as a Check Paid in the configurator file. (Usually checks have code 475, which should already be mapped by default in Microsoft Dynamics GP 2013, but you may have to double-check any other codes used as not all codes are mapped by default for you.) Several codes are defaulted in for you. Verify the code you need is listed and press Save in this window to activate/cache them.
- The Bank Cleared Date in the bank file is BEFORE the Check Issue Date in Microsoft Dynamics GP. The system will not clear a check on date that is before it was actually issued in Microsoft Dynamics GP. (Example: If the Bank Cleared date comes in as 8/20/2016, and the Check Date in Microsoft Dynamics GP is 8/22/2016, the system will not match this check for you. It doesn't make sense that the check was cashed on a date prior to when it was actually issued in GP. The check must be manually cleared in the reconciliation.)
: To match, all these criteria must be true:
Transfer Debit/Transfer Credit
- The Deposit Amount matches.
- The Deposit Date on the bank source file is within the grace period defined (default is 8 days) of the Deposit Date in Microsoft Dynamics GP. (Note, if there is another deposit in the same amount within the grace period, it will fall out on the Exception report. It doesn't know which one to mark, so it denotes that you must clear it manually against the one you choose.)
- The transaction is a deposit type in Microsoft Dynamics GP. (ie. CMTRXTYPE = '1' in the CM20200 SQL table).
- The CHECKBOOK ID on the deposit in the CM20200 must be the checkbook ID you are working with in the Reconcile window.
- The Transaction code on the source file is mapped in the CODES ENTRY as a Deposit Cleared on the configurator file in Microsoft Dynamics GP. Several codes are defaulted in for you. Verify that the code you need is listed, and press SAVE in this window to activate them.
: Bank Transfers will automatically match in Microsoft Dynamics GP 2013 and higher versions. To match, all these criteria must be true:
- The transfer amount must match.
- The transfer date must match.
- The transfer must have been keyed into Microsoft Dynamics GP as a Bank Transfer in Bank Reconciliation.
- The transfer must be a transfer type in Microsoft Dynamics GP. (ie. CMTRXTYPE = '7' in the CM20200 SQL table).
- The Transaction code on the source file must be mapped in the CODES ENTRY as a Transfer Debit or Transfer Credit as needed in the configurator file in Microsoft Dynamics GP. These codes are not defaulted in for you and must be added. Typically, the bank uses codes 277 for a Credit Transfer and 577 for a Debit Transfer.
Electronic Reconcile does this for each Transaction Type that is included in the download file from the bank. MORE INFORMATION
The following types are for adjustments in Microsoft Dynamics GP only to affect the checkbook balance and would typically not be transactions found on the bank statement. Therefore, these types of transactions keyed in Microsoft Dynamics GP are excluded from the matching process in Electronic reconcile: Increase Adjustments
- will not be matched.
- will not be matched.
- will not be matched.
This article was TechKnowledge Document ID: 20884