Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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

No comments:

Post a Comment