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]
This post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.
