Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

2 comments:

  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
  2. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete