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 causing 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 is required for each country. An example of the rules can be found here: http://dynamicbusinesssolutions.ru/axsasetup.en/html/57d8b0d0-e631-40ac-b860-a281233edc8f.htm


Datasets 

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


Dynamics Axapta

Following is an 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 Routing number and Bank account number is 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],'')=''
      )