Day: January 3, 2023

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a user to check information; it returns basic information on the vendor along with bank details and email addresses for the addresses linked to the vendor as primary, remit to and purchases fields.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['PM Creditor Master'].VENDORID
	,['PM Creditor Master'].VENDNAME
	,CASE ['PM Creditor Master'].HOLD
		WHEN 0 THEN ''
		ELSE 'On Hold'
	END AS HOLD
	,['PM Creditor Master'].TXRGNNUM AS VatNumber
	,['PM Creditor Address Master'].PHNUMBR1 AS PhoneNumber
	,['PM Creditor Address Master'].ADDRESS1 AS AddressLineOne
	,['PM Creditor Address Master'].ADDRESS2 AS AddressLineTwo
	,['PM Creditor Address Master'].ADDRESS3 AS AddressLineThree
	,['PM Creditor Address Master'].CITY AS City
	,['PM Creditor Address Master'].STATE AS County
	,['PM Creditor Address Master'].ZIPCODE AS PostCode
	,ISNULL(['Internet Addresses'].EmailToAddress,'') AS EmailAddress
	,['Address Electronic Funds Transfer Master'].BANKNAME AS BankName
	,['Address Electronic Funds Transfer Master'].EFTBankCode AS SortCode
	,['Address Electronic Funds Transfer Master'].EFTBankAcct AS AccountNumber
	,['Internet Addresses - Remittance'].EmailToAddress AS RemittanceEmailAddress
	,['Internet Addresses - Purchase Order'].EmailToAddress AS PurchaseOrderEmailAddress
	,['PM Creditor Master'].TXRGNNUM AS CompanyRegistrationNumber
FROM
	PM00200 AS ['PM Creditor Master']
INNER JOIN
	PM00300 AS ['PM Creditor Address Master']
		ON
			['PM Creditor Address Master'].VENDORID = ['PM Creditor Master'].VENDORID
		AND
			['PM Creditor Address Master'].ADRSCODE = ['PM Creditor Master'].VADCDPAD
LEFT JOIN
	SY01200 AS ['Internet Addresses']
		ON
			['Internet Addresses'].Master_Type = 'VEN'
		AND
			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDPAD
LEFT JOIN
	SY01200 AS ['Internet Addresses - Remittance']
		ON
			['Internet Addresses - Remittance'].Master_Type = 'VEN'
		AND
			['Internet Addresses - Remittance'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses - Remittance'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
LEFT JOIN
	SY01200 AS ['Internet Addresses - Purchase Order']
		ON
			['Internet Addresses - Purchase Order'].Master_Type = 'VEN'
		AND
			['Internet Addresses - Purchase Order'].Master_ID = ['PM Creditor Master'].VENDORID
		AND
			['Internet Addresses - Purchase Order'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
LEFT JOIN
	SY06000 AS ['Address Electronic Funds Transfer Master']
		ON
			['Address Electronic Funds Transfer Master'].SERIES = 4
		AND
			['Address Electronic Funds Transfer Master'].VENDORID = ['PM Creditor Master'].VENDORID
		AND
			['Address Electronic Funds Transfer Master'].ADRSCODE = ['PM Creditor Master'].VADCDTRO
WHERE
	['PM Creditor Master'].VENDSTTS = 1