This article will outline a quick start guide on finding discrepancies using customer deposit reports between the amount of direct debit deposits in JLP and Eazy Collect payment files.
Note:
- It is presumed total amounts have been checked in EazyCollect files against JLP and a discrepancy is evident.
- Discrepancies could be caused by instances of ‘Imdenity Claimed’ payments which are easily found in the EazyCollect file under the Paid/Unpaid column.
Discrepancy Causes Could Be As Follows:
- Duplicate deposits in Eazy Collect
- This could be a result of duplicate API requests where there are two payments in Eazy Collect but only one in JLP
- Direct debits can be made monthly, quarterly, biannually and yearly
- There should only be one direct debit payment per direct debit reference per period
- Inconsistencies with the status of payment between JLP and Eazy Collect:
- This is usually caused by a direct debit deposit being listed as unpaid in EazyCollect and paid in JLP. This may result in either JLP or Eazy Collect having a higher amount of deposits.
- Deposit status in the Eazy Collect payment file
- Paid
- Unpaid
- Sometimes both circumstances can be causing the discrepancy, so if either one doesn’t explain why the deposit amounts are inconsistent you may need to look for deposits that fit into both categories
Prerequisites
- Access to Powered By Jumbo admin platform
- Payment file in question from EazyCollect
- Microsoft Excel
1. Begin With EazyCollect Payment File.
For the purpose of this guide we will use an example from a 1st May payment file.
In this scenario the sum of ‘Paid’ Amounts from the Paid/Unpaid column have been totalled. This has been compared to ‘Successful’ payments in JLP, the results are £8.68 more in JLP which suggests a prospective ‘Successful payment’ in JLP, showing as unpaid in EazyCollect.
2. Download The ‘Customer Deposit’ Reports
The data will be split across two files containing data for both nodes. For ease paste the contents of one node into another to work with x1 file.
3. Preparing The Customer Deposit Report
Start by filtering the ‘Settlement Date’ column to yield results from the settlement date in question, in this scenario we only want to look at data from the 1st May.
Next, filter the ‘Deposit Status Name’ to show results for ‘Successful’ (If the discrepancies should less in JLP reverse this step and select the Cancelled and Failed)
4. Preparing The EazyCollect File
Filter the EazyCollect file to show Unpaid payments only. (Reverse this process if payments are more in EazyCollect). In addition, the ‘Amount’ column can be filtered if a specific discrepancy amount was in question.
Select all of the references under the column ‘Bank Ref’s.
5. Ensure The Customer Deposit Report Is Correct
The report should now contain filtered results for Successful state and Settlement dates, if necessary the amount can also be filtered.
6. Create A New Sheet In The Customer Deposits File
In order to compare data we need to use a common denominator between the two data sets in EazyCollect and JLP. This would be the Direct Debit References column in the Customer Deposit file and the Customer refs column in the EazyCollect file.
Next, create a new sheet within the Customer Deposit file and paste the filtered Direct Debit References.
Copy and paste the filtered Bank Refs data from the EazyCollect file into this new Sheet next to the Direct Debit References.
7. Finding The Discrepancy
Filter the sheet, and condition format the columns to find duplicate values.
Filter cells by colour – this will yield duplicate data across both columns:
This will instantly highlight the discrepancy
8. Double Check The Findings
Check the results against the Eazy Collect file to ensure the status of the payment is different to PBJ Admin to ensure the results are correct. Proceed to cancel the payment/tickets purchased if necessary.