Vendor Bank Account Validation

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.

Datasets

The dataset is a list of all active bank accounts in addition to entity name, vendor group, vendor name, and country.

Dynamics Axapta

Following is an SQL statement querying the implementation in Dynamics Axapta:

SELECT
vend.dataareaid as entity,
vend.accountnum,
vend.vendgroup,
vend.currency,
vend.paymtermid,
d.name,
address.countryregionid,
VendBankAccount.ACCOUNTID BankAccount,
VendBankAccount.NAME 'Bank Name' ,
VendBankAccount.ACCOUNTNUM 'Bank account number',
VendBankAccount.RegistrationNum 'Routing Number',
VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN,
VendBankAccount.bankcodetype,

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()

Controls

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.