SQL View to Return Sales Invoice Headers

Microsoft Dynamics GPI’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]