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