Dynamics GP
Dynamics GP
Organizer:
- JOSE Organized by
-
-
Vendor EFT Information
-
Vendor EFT Information
Is there any way to run a report of vendors with the EFT information on their account? I am working on setting this up and I am so close. I would like to see what vendors have this information on their vendor file.
-
2 Replies
-
I had this older SQL Script in my toolbox from many years ago. Will this do what your need?
P.S. I always try to leave the original creator’s info in the script.
/*———————————————————————————-
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 11/23/2015 Original Version
2 04/12/2024 DPM-Joined the Vendor Master for changed data
———————————————————————————- */
— Tables Included:
— SY06000 EFT Bank Setup Information
/*- Purchasing Series*/
SELECT S6.SERIES AS Series,
S6.ADRSCODE AS ‘Address Code’,
S6.VENDORID AS ‘Vendor ID’,
PM2.VENDNAME,
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,
S6.BANKNAME AS ‘Bank Name’,
S6.EFTBankAcct AS ‘EFT Bank Account’,
S6.EFTBankBranch AS ‘EFT Bank Branch’,
S6.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’,
S6.SWIFTADDR AS ‘Swift Address’,
S6.CustVendCountryCode AS ‘Vendor Country Code’,
S6.DeliveryCountryCode AS ‘Delivery Country Code’,
S6.BNKCTRCD AS ‘Bank Country Code’,
S6.CBANKCD AS ‘Central Bank Code’,
S6.ADDRESS1 AS AddressLine_1,
S6.ADDRESS2 AS AddressLine_2,
S6.ADDRESS3 AS AddressLine_3,
S6.ADDRESS4 AS AddressLine_4,
S6.RegCode1 AS RegulatoryCode_1,
S6.RegCode2 AS RegulatoryCode_2,
S6.EFTTransitRoutingNo,
S6.CURNCYID AS CurrencyID,
S6.EFTPrenoteDate AS ‘EFT Pre Note Date’,
S6.EFTTerminationDate AS ‘EFT Termination Date’
FROM SY06000 S6
INNER JOIN PM00200 PM2 ON S6.VENDORID = PM2.VENDORID
WHERE S6.EFTBankType IS NOT NULL
–WHERE (S6.SERIES = 4) AND MODIFDT >= GETDATE() -7
Log in to reply.
