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 a Dynamics GP Consultant (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]

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *