KB 851279 - How Transactions are Matched in Electronic Reconcile for Microsoft Dynamics GP

Question:

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, andthen 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.
• It has not already been reconciled. (RECOND = 0 in CM20200 table.)
• It has not been voided. (VOIDED = 0 in CM20200 table.)
• 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 must be AFTER 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.) 

Deposits: To match, all these criteria must be true:
 

• The Deposit Amount matches.
• The Deposit Date in Microsoft Dynamics GP (TRXDATE in CM20200) must be ON or BEFORE the Bank Cleared Date in the bank import file. (The cash receipt/deposit is expected to be posted in Microsoft Dynamics GP first before it is sent to the bank for deposit.)  If the deposit date in GP is after the bank cleared date from the bank import file, then it will not be matched. The Unprocessed Downloaded Transactions/exception report says "Unable to match deposit." 
• The Deposit Date in the bank file must be within the grace period defined (default is 8 days, or whatever number of days you define for the grace period) from 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. The system is unable to determine 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.

Transfer Debit/Transfer Credit: 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.

Decrease Adjustments - will not be matched.

Withdrawals - will not be matched.





This article was TechKnowledge Document ID:20884