Transactional Data Migration Pt. 1 : Payables, Receivables & Bank
Hi Readers,
In this 3-part series, we will look at how to complete a transactional data migration for:
Subledgers : Receivables, Payables, Banks, Fixed Assets, Items
General Ledger : Trial Balance.
In Part 1, we focus on Receivables, Payables & Banks.
I will be using a blank company with no transactions for this series and working with an assumption that data was requested as at 30th June 2024.
Receivables
When migrating sub-ledger transactional data, the total balances should reflect on the sub-ledger accounts but should not populate on the general ledger.
This is because all general ledger balances will be populated using the trial balance.
So how do we ensure our general ledger balance remains zero on posting?
We create a scenario where the debit and credit in the general ledger will be in the same account. This will generate a net effect of zero on the general ledger.
I have my customer master and posting groups as below.
I have received the below receivable transactional data.
| Posting Date | Document Number | Customer No. | Description | Amount |
| 26.06.2024 | SV026571 | C00001 | Inv 26571 | 20,000 |
| 15.05.2024 | SV022571 | C00002 | Inv 22571 | 35,000 |
| 19.06.2024 | SV023472 | C00003 | Inv 23472 | 120,000 |
| 01.04.2024 | SV021472 | C00004 | Inv 21472 | 50,000 |
Method 1
In this method, we will debit our customer account and credit the receivable account that has been placed in the customer posting group of the customer.
I will load my journal as below as per the transactional data.
From above you can notice that my balancing account is either 2310 or 2320 depending on the customer posting group of the customer.
On preview posting, it will look like below.
The net effect on the G/L Entries will be zero. However, we will have 4 customer ledger entries for our subledgers.
Method 2
We will create special migration accounts that will sit at the bottom of our chart of accounts.
They will be grouped into different categories of sub-ledger accounts.
In each of our posting groups, we will add a MIGRATION posting group that links to the respective subledger migration account.
Export the customer table with the current customer posting groups and make a copy of the file. Change all the customer posting groups to migration.
In the journal, the balancing account will be the receivables account of the migration customer posting group as below.
On preview posting , we will get a similar effect as Method 1.
So basically , when we debit our Customer account - the account in the customer posting group is also debited.
The same account is also credited in our journal thus creating an 'in and out' effect and leaving the general ledger without any balance.
The customer posting group can be restored to their original values.
I prefer Method 2 and will use it going forward. Both methods can be used across all the sub ledger types.
Payables
My vendors list and vendor posting groups are shown below
My migration vendor posting group is mapped to the payables migration G/L account.
I have received the payables transactional data as below.
| Posting Date | Document Number | Vendor No. | Description | Amount |
| 26.06.2024 | PV026571 | V00001 | Inv 26571 | 120,000 |
| 15.05.2024 | PV022571 | V00002 | Inv 22571 | 25,000 |
| 19.06.2024 | PV023472 | V00003 | Inv 23472 | 20,000 |
| 01.04.2024 | PV021472 | V00004 | Inv 21472 | 150,000 |
The vendor migration groups changed to MIGRATION.
Create my journal - debit the migration payable account and credit the vendor sub-ledger account.
Check the preview post to ensure no effect on the chart of accounts.
On posting the vendor balances should be updated and general ledger should have no transactions.
The vendor posting group can be restored to their original values.
Bank Balances
The bank accounts and bank account balances are as below.
My migration bank account posting group is mapped to the bank migration G/L account.
I have received the bank balances transactional data as at 30.06.2024.
| Posting Date | Bank No | Description | Amount |
| 30.06.24 | EQUITY-KES | Bank Balance - 30.06.2024 | 200,000 |
| 30.06.24 | EQUITY-USD | Bank Balance - 30.06.2024 | 5,000 |
| 30.06.24 | MPESA-708917 | Bank Balance - 30.06.2024 | 40,000 |
The bank account migration groups changed to MIGRATION.
Create my journal - debit the bank account subledger account and credit the bank migration migration g/l account.
Remember to put the correct exchange rate for any bank balances which are nit in the local currency e.g EQUITY-USD above.
Check the preview post to ensure no effect on the chart of accounts.
On posting the bank account balances should be updated and general ledger should have no transactions.
The bank account posting group can be restored to their original values.
In Pt 2 ( Transactional Data Migration Pt.2 ) , we will cover fixed assets & items.