A bit of background
We are a global brand and have multiple Shopify stores, we switched to Acumatica from a basic accounting system when we outgrew the old systems multi-currency features.
The implementation wasn’t what you’d call the greatest. To connect to our Shopify stores of which we have 6 production stores serving different regions we were using a very simple prebuilt ISV connector which fell short of our requirements.
As part of our clean up of our systems we replaced the old Shopify connector with Celigo so that we could have complete control of the integration and in a separate group of flows we handled orders and payments synchronizations.
The Problem:
We allow our Shopify customers to check out in their local currency and the old connector would import the records in the currency of the Shopify store but in our new integrations we import the transactions in the currency that the transaction occurred in, which solved a lot of problems for us, but it created another.
We now had transactions sitting in numerous undeposited accounts unique for each currency we are accepting (currently there are about 40 active currencies) however this money when paid out by Shopify is deposited in a single currency. This made reconciliation and processing deposits a nightmare for our accounts team.
For example, a payout might have 3 EUR, 6 GBP and 1 CHF payments and be deposited as a single EUR payout.
The Solution:
For each payout, group the transactions by currency then for each currency create a bank deposit record moving the value from the undeposited account into a separate clearing account of the same currency.
Then for each currency create a Funds Transfer record to move the value of each created deposit from the clearing account of the FX Currency into the clearing account of the deposit currency.
Finally create a funds transfer from the deposit currency clearing account to the deposit account, including any merchant fees on this transfer.
The result is that when reconciling, the deposit account has a single transfer that matches the bank statement, any exchange variances build up in the clearing accounts and the payment records that are included on each deposit are the correct ones.
The Flow:
First we get the payouts then for each store we perform a look up to get related transactions, we do some processing of the data in a hook before we send the data to either the flow that handles the logic we outlined above or to a separate flow that handles the edge cases of when we have chargebacks included in the payout, in cases of the chargebacks once these are handled the data is then sent to the same batch deposit flow.
The batch deposit flow is separated from the Shopify flow to allow this flow to be re-usable with other payment providers.
The batch deposit flow starts with a deduplication check of the final funds transfer into the deposit account if this has already been done then we can skip it. We then lookup all the payment records in Acumatica that are included in the deposit from there we move through to creating the Bank Deposit records and FX clearing funds transfers deduplication at each step which allows us to rerun the flow from the original export if an error occurs during the process without conflicting with any already processed data.
