SQL Scripts for Microsoft Dynamics GP: Select a List of Vendor Addresses

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 as a quick way of extracting a list of vendor addresses from Dynamics GP.

CREATE VIEW [dbo].[uv_AZRCRV_ExtractVendorAddresses] AS
/*
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 Address MSTR'].VENDORID AS 'Vendor ID'
	,['PM Address MSTR'].ADRSCODE AS 'Address Code'
	,['PM Address MSTR'].VNDCNTCT AS 'Contact'
	,['PM Address MSTR'].ADDRESS1 AS 'Address 1'
	,['PM Address MSTR'].ADDRESS2 AS 'Address 2'
	,['PM Address MSTR'].ADDRESS3 AS 'Address 3'
	,['PM Address MSTR'].CITY AS 'City'
	,['PM Address MSTR'].STATE AS 'County'
	,['PM Address MSTR'].ZIPCODE AS 'Post Code'
	,['PM Address MSTR'].COUNTRY AS 'Country'
	,['PM Address MSTR'].PHNUMBR1 AS 'Phone 1'
	,['PM Address MSTR'].PHNUMBR2 AS 'Phone 2'
	,['PM Address MSTR'].PHONE3 AS 'Phone 3'
	,['PM Address MSTR'].FAXNUMBR AS 'Fax'
	,['PM Address MSTR'].TAXSCHID AS 'Tax Schedule'
	,['Internet Addresses'].EmailToAddress AS 'Email To'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc'
	,ISNULL(['Address Electronic Funds Transfer Master'].BANKNAME, '') AS 'Bank Name'
	,ISNULL(['Address Electronic Funds Transfer Master'].EFTBankCode, '') AS 'Bank Sort Code'
	,ISNULL(['Address Electronic Funds Transfer Master'].EFTBankAcct, '') AS 'Bank Account Number'
FROM
	PM00300 AS ['PM Address MSTR'] WITH (NOLOCK) --PM Address MSTR (PM00300)
INNER JOIN
	PM00200 AS ['PM Vendor Master File'] WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON ['PM Vendor Master File'].VENDORID = ['PM Address MSTR'].VENDORID
LEFT JOIN
	SY06000 AS ['Address Electronic Funds Transfer Master'] WITH (NOLOCK) --Address Electronic Funds Transfer Master (SY06000)
		ON ['Address Electronic Funds Transfer Master'].VENDORID = ['PM Address MSTR'].VENDORID AND ['Address Electronic Funds Transfer Master'].ADRSCODE = ['PM Address MSTR'].ADRSCODE
LEFT JOIN
	SY01200 AS ['Internet Addresses'] WITH (NOLOCK) --Internet Addresses (SY01200)
		ON ['Internet Addresses'].Master_Type = 'VEN' AND ['Internet Addresses'].Master_ID = ['PM Address MSTR'].VENDORID AND ['Internet Addresses'].ADRSCODE = ['PM Address MSTR'].ADRSCODE
WHERE
	['PM Vendor Master File'].MODIFDT >= CAST('2015-12-12' AS DATE)
OR
	['PM Vendor Master File'].CREATDDT >= CAST('2015-12-12' AS DATE)
GO