SQL Scripts for Microsoft Dynamics GP: Sales Invoice Query

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 returns work status sales invoices which are linked to a history status sales order and along with data for the work transaction returns the originating transactions order number and document date.

/*
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
	['Sales Transaction Work'].SOPNUMBE
	,['Sales Transaction Work'].SOPTYPE
	,['Company Master'].CMPNYNAM AS COCMPNYNAM
	,['Company Master'].ADDRESS1 AS COADDRESS1
	,['Company Master'].ADDRESS2 AS COADDRESS2
	,['Company Master'].ADDRESS3 AS COADDRESS3
	,['Company Master'].CITY AS COCITY
	,['Company Master'].STATE AS COSTATE
	,['Company Master'].ZIPCODE AS COZIPCODE
	,['Company Master'].COUNTY AS COCOUNTRY
	,['Company Master'].TAXREGTN AS COTAXREGNO
	,['Company Master'].UDCOSTR1 AS COREGNO
	,['Company Master'].PHONE1 AS COPHONE1
	,['Internet Addresses - Company'].INET1 AS COEMAIL
	,['Internet Addresses - Company'].INET2 AS COWEBSITE
	,['Sales Transaction Work'].CUSTNMBR
	,['Sales Transaction Work'].CUSTNAME
	,['Sales Transaction Work'].CSTPONBR
	,['Sales Transaction Work'].SOPNUMBE AS INVNO
	,['Sales Transaction History'].SOPNUMBE AS ORDNO
	,['Sales Transaction History'].DOCDATE
	,['Currency Setup'].CRNCYSYM
	,['RM Customer Master'].TXRGNNUM AS CUSTTAXREGNO
	,['Customer Master Address'].ADDRESS1 AS BTADDRESS1
	,['Customer Master Address'].ADDRESS2 AS BTADDRESS2
	,['Customer Master Address'].ADDRESS3 AS BTADDRESS3
	,['Customer Master Address'].CITY AS BTCITY
	,['Customer Master Address'].STATE AS BTSTATE
	,['Customer Master Address'].ZIP AS BTZIP
	,['Customer Master Address'].COUNTRY AS BTCOUNTRY
	,['Sales Transaction Work'].CUSTNMBR AS CUSTCODE
	,['Sales Transaction Work'].ADDRESS1 AS STADRESS1
	,['Sales Transaction Work'].ADDRESS2 AS STADDRESS2
	,['Sales Transaction Work'].ADDRESS3 AS STADDRESS3
	,['Sales Transaction Work'].CITY AS STCITY
	,['Sales Transaction Work'].STATE AS STSTATE
	,['Sales Transaction Work'].ZIPCODE AS STZIP
	,['Sales Transaction Work'].COUNTRY AS STCOUNTRY
	,['Sales Transaction Work'].ORSUBTOT
	,['Sales Transaction Work'].ORTAXAMT
	,['Sales Transaction Work'].ORSUBTOT + ['Sales Transaction Work'].ORTAXAMT AS ORTOTAL
	,['Sales Transaction Work'].DUEDATE
FROM
	SOP10100 AS ['Sales Transaction Work'] WITH (NOLOCK) --Sales Transaction Work (SOP10100)
INNER JOIN
	RM00101 AS ['RM Customer Master'] WITH (NOLOCK) --RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
INNER JOIN
	RM00102 AS ['Customer Master Address'] WITH (NOLOCK) --Customer Master Address File (RM00102)
		ON
			['Customer Master Address'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
		AND
			['Customer Master Address'].ADRSCODE = ['Sales Transaction Work'].PRBTADCD
INNER JOIN
	SOP30200 AS ['Sales Transaction History'] WITH (NOLOCK) --Sales Transaction History (SOP30200)
		ON
			['Sales Transaction History'].SOPTYPE = ['Sales Transaction Work'].ORIGTYPE
		AND
			['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Work'].ORIGNUMB
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master'] WITH (NOLOCK) --Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME()
LEFT JOIN
	DYNAMICS..MC40200 AS ['Currency Setup'] WITH (NOLOCK) --Currency Setup (MC40200)
		ON
			['Currency Setup'].CURNCYID = ['Sales Transaction Work'].CURNCYID
LEFT JOIN
	SY01200 AS ['Internet Addresses - Company'] WITH (NOLOCK) --Assembly Component (BM10300)
		ON
			['Internet Addresses - Company'].Master_Type = 'CMP'
		AND
			['Internet Addresses - Company'].ADRSCODE = 'PRIMARY'
WHERE
	['Sales Transaction Work'].SOPTYPE = 3