Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Monday, November 23, 2015

Vendor EFT Bank Details - SQL Script

A client of us requested a consolidated report for all the vendor EFT Bank details, I was quite sure that I have come across an SQL Script on that matter, It was Ian's script | SQL view on Vendor EFT from Azurecurve. While it is just perfect, I still wanted further definitive details including most of the fields in the screen.  


The Vendor EFT Bank details can be found on:
  • Cards > Purchasing > Vendor > Address Button > EFT Bank ButtonOr
  • Cards > Purchasing > Address > EFT BanK

Vendor EFT Bank Maintenance Window 

The script can be downloaded from >> Download Link

/*--------------------------------------------------------------------------------
Creation Date: 23, November, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to retrieve vendor EFT Bank details. 

Revision History:
Revision No.            Revision Date    Description
1                       23/11/2015      Original Version
-------------------------------------------------------------------------------- */

-- Tables Included:
-- SY06000 EFT Bank Setup Information
  
SELECT  SERIES Series ,
        ADRSCODE AS 'Address Code' ,
        VENDORID AS 'Vendor ID' ,
        CASE FRGNBANK
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No'
          ELSE ''
        END AS 'Additional Information' ,
        CASE INACTIVE
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No'
          ELSE ''
        END AS Inactive ,
        BANKNAME AS 'Bank Name' ,
        EFTBankAcct AS 'EFT Bank Account' ,
        EFTBankBranch AS 'EFT Bank Branch' ,
        IntlBankAcctNum AS IBAN ,
        CASE EFTAccountType
          WHEN 1 THEN 'Checking'
          WHEN 2 THEN 'Savings'
          WHEN 3 THEN 'General Ledger'
          WHEN 4 THEN 'Loan'
          ELSE ''
        END AS 'EFT Account Type' ,
        CASE EFTTransferMethod
          WHEN 1 THEN 'Not specified'
          WHEN 2 THEN 'Business Account'
          WHEN 3 THEN 'Corporate Account'
          WHEN 4 THEN 'Personal Account'
          WHEN 5 THEN 'Foreign Account'
          ELSE ''
        END AS 'EFT Transfer Method' ,
        CASE GIROPostType
          WHEN 1 THEN 'Pure'
          WHEN 2 THEN 'Impure'
          ELSE ''
        END AS 'GIRO Post Type' ,
        SWIFTADDR AS 'Swift Address' ,
        CustVendCountryCode AS 'Vendor Country Code' ,
        DeliveryCountryCode AS 'Delivery Country Code' ,
        BNKCTRCD AS 'Bank Country Code' ,
        CBANKCD AS 'Central Bank Code' ,
        ADDRESS1 AS AddressLine_1 ,
        ADDRESS2 AS AddressLine_2 ,
        ADDRESS3 AS AddressLine_3 ,
        ADDRESS4 AS AddressLine_4 ,
        RegCode1 AS RegulatoryCode_1 ,
        RegCode2 AS RegulatoryCode_2 ,
        EFTTransitRoutingNo ,
        CURNCYID AS CurrencyID ,
        EFTPrenoteDate AS 'EFT Pre Note Date' ,
        EFTTerminationDate AS 'EFT Termination Date'
FROM    SY06000
WHERE   SERIES =--- Purchasing Series

Best Regards,
Mahmoud M. AlSaadi

1 comment:

  1. This came in real handy recently
    Please see EFTBankType case statement below

    CASE EFTBankType
    WHEN 0 THEN 'None'
    WHEN 2 THEN 'Ireland'
    WHEN 3 THEN 'United Kingdom'
    WHEN 4 THEN 'NL Bank'
    WHEN 5 THEN 'NL Giro'
    WHEN 6 THEN 'Belgium'
    WHEN 7 THEN 'Luxembourg'
    WHEN 8 THEN 'France'
    WHEN 9 THEN 'Spain'
    WHEN 10 THEN 'Portugal'
    WHEN 11 THEN 'Norway Bank'
    WHEN 12 THEN 'Norway Giro'
    WHEN 13 THEN 'Sweden Bank'
    WHEN 14 THEN 'Sweden Giro'
    WHEN 15 THEN 'Denmark Bank'
    WHEN 16 THEN 'Denmark Giro'
    WHEN 17 THEN 'Finland'
    WHEN 18 THEN 'Germany'
    WHEN 19 THEN 'Austria'
    WHEN 20 THEN 'Switzerland'
    WHEN 21 THEN 'Italy'
    WHEN 22 THEN 'Poland'
    WHEN 23 THEN 'Czech Republic'
    WHEN 24 THEN 'Slovak Republic'
    WHEN 25 THEN 'Hungary'
    WHEN 26 THEN 'Other 1'
    WHEN 27 THEN 'Other 2'
    WHEN 28 THEN 'Other 3'
    WHEN 29 THEN 'Other 4'
    WHEN 30 THEN 'Canada'
    WHEN 31 THEN 'United States'
    END AS EFTBankType

    ReplyDelete