SQL View to Return Sales Invoice Headers

I’ve been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceHeaders] 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
	RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number'
	,['Sales Transaction Work'].DOCID AS 'Invoice Type'
	,FORMAT(['Sales Transaction Work'].DOCDATE, 'dd-MM-yyyy') AS 'Document Date'
	,FORMAT(['Sales Transaction Work'].INVODATE, 'dd-MM-yyyy') AS 'Invoice Date'
	,FORMAT(['Sales Transaction Work'].INVODATE, 'yyyy-MM-dd') AS 'Invoice Date Sortable'
	,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date'
	,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date'
	,RTRIM(['Company Master'].ADRCNTCT) AS 'Shipper Contact Person'
	,RTRIM(['Company Master'].ADDRESS1) AS 'Shipper Address 1'
	,RTRIM(['Company Master'].ADDRESS2) AS 'Shipper Address 2'
	,RTRIM(['Company Master'].ADDRESS3) AS 'Shipper Address 3'
	,RTRIM(['Company Master'].CITY) AS 'Shipper City'
	,RTRIM(['Company Master'].STATE) AS 'Shipper State'
	,RTRIM(['Company Master'].ZIPCODE) AS 'Shipper Zip Code'
	,RTRIM(['Company Master'].COUNTY) AS 'Shipper Country'
	,RTRIM(['Company Master'].PHONE1) AS 'Shipper Phone 1'
	,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number'
	,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name'
	,RTRIM(['RM Customer Master'].TXRGNNUM) AS 'Tax Registration Number'
	,RTRIM(['Sales Transaction Work'].PRSTADCD) AS 'Ship To Address Code'
	,RTRIM(['Sales Transaction Work'].CNTCPRSN) AS 'Contact Person'
	,RTRIM(['Sales Transaction Work'].ShipToName) AS 'Ship To Name'
	,RTRIM(['Sales Transaction Work'].ADDRESS1) AS 'Address 1'
	,RTRIM(['Sales Transaction Work'].ADDRESS2) AS 'Address 2'
	,RTRIM(['Sales Transaction Work'].ADDRESS3) AS 'Address 3'
	,RTRIM(['Sales Transaction Work'].CITY) AS 'City'
	,RTRIM(['Sales Transaction Work'].STATE) AS 'State'
	,RTRIM(['Sales Transaction Work'].ZIPCODE) AS 'Zip Code'
	,RTRIM(['Sales Transaction Work'].COUNTRY) AS 'Country'
	,RTRIM(['Sales Transaction Work'].PHNUMBR1) AS 'Phone 1'
	,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID'
	,(
		SELECT
			CONVERT(DECIMAL(10,0), SUM(QUANTITY))
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
		['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
	) AS 'Number Of Pieces'
	,(
		SELECT
			CONVERT(DECIMAL(10,2), SUM(QUANTITY) * SUM(['Item Master'].ITEMSHWT))
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
		INNER JOIN
			IV00101 AS ['Item Master'] -- Item Master (IV00101)
				ON
					['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
			['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
	) AS 'Total Weight'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].SUBTOTAL) AS 'Sub Total'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].TAXAMNT) AS 'Tax Amount'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].FRTAMNT) AS 'Freight Amount'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].DOCAMNT) AS 'Document Amount'
FROM
	SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
LEFT JOIN
	RM00101 AS ['RM Customer Master'] -- RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
LEFT JOIN
	DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Sales Transaction Work'].SOPTYPE = 3
GO

GRANT SELECT ON uv_AZRCRV_SalesInvoiceHeaders TO DYNGRP
GO

[/postcode]