Ask the community about this page

Vendor Bank Account Validation¶

In this article, you will read about vendor bank account validation with Timextender Data Quality. 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 behavior

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],'')=''
  )

Discussed in the community

Looking for related questions…

Didn't find your answer? Ask the community about this page — questions are answered by Timextender experts, partners and other users.