Created by: Gunnar Steinn Magnússon
In this article, you will read about vendor bank account validation with exMon. Having accurate payment information for your vendors is essential for a smooth procurement process.
The dangers of bad or missing payment information are for example:
- Time-consuming manual intervention causes delayed payments to vendors
- Risk of payments to wrong accounts, by mistake or fraudulent behaviour
Bank Routing numbers are different between countries so separate rules are required for each country. More information about Bank Routing Numbers and Rules can be found here.
The dataset is a list of all active bank accounts in addition to entity name, vendor group, vendor name, and country.
Following is an SQL statement querying the implementation in Dynamics Axapta:
vend.dataareaid as entity,
VendBankAccount.NAME 'Bank Name' ,
VendBankAccount.ACCOUNTNUM 'Bank account number',
VendBankAccount.RegistrationNum 'Routing Number',
case when VendBankAccount.bankcodetype is null then null
when VendBankAccount.bankcodetype = 0 then 'None'
when VendBankAccount.bankcodetype = 1 then 'AT'
when VendBankAccount.bankcodetype = 2 then 'BL'
when VendBankAccount.bankcodetype = 3 then 'CC'
when VendBankAccount.bankcodetype = 4 then 'CP'
when VendBankAccount.bankcodetype = 5 then 'CH'
when VendBankAccount.bankcodetype = 6 then 'FW'
when VendBankAccount.bankcodetype = 7 then 'SC' end as [Routing Number Type]
FROM vendtable vend
LEFT OUTER JOIN dirpartytable d
ON d.RECID = vend.PARTY
LEFT OUTER JOIN logisticspostaladdress address
ON address.LOCATION = d.PRIMARYADDRESSLOCATION
LEFT OUTER JOIN VendBankAccount
ON VendBankAccount.VENDACCOUNT = vend.ACCOUNTNUM AND vend.dataareaid=vendbankaccount.dataareaid
--AND VendBankAccount.ACCOUNTID = vend.BANKACCOUNT
WHERE vend.vendgroup!='Cash Pool' AND address.validto > getdate()
Here are some ideas of controls to implement against vendor bank accounts
- US Bank Accounts validation
Make sure all USA vendors have bank accounts with FW type and 9 digits
WHERE vendaccount.countryregionid='USA' and ( -- USA requires Forwarding type isnull(vendaccount.[Routing Number type],'None')!='FW' or isnull(vendaccount.[Bank account number],'')='' -- 9 digits or (isnull(vendaccount.[Routing Number],'')='' or isnumeric(vendaccount.[Routing Number])!=1 or len(vendaccount.[Routing Number])!=9) )
- Australian Bank Account validation
Make sure None is selected in routing type and both the Routing number and Bank account numbers are non-empty
WHERE vendaccount.countryregionid='AUS' AND ( isnull(vendaccount.[Routing Number type],'None')!='None' or isnull(vendaccount.[Routing Number],'')!='' or isnull(vendaccount.[Bank account number],'')='' )
Gunnar is the author of this solution article.