Day: April 12, 2021

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 quote headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesQuoteHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesQuoteHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesQuoteHeaders] 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 'Quote Number'
	,['Sales Transaction Work'].DOCID AS 'Quote Type'
	,['Sales Transaction Work'].DOCDATE AS 'Document Date'
	,FORMAT(['Sales Transaction Work'].QUOTEDAT, 'dd-MM-yyyy') AS 'Quote Date'
	,FORMAT(['Sales Transaction Work'].QUOEXPDA, 'dd-MM-yyyy') AS 'Quote Expiry Date'
	,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date'
	,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date'
	,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number'
	,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name'
	,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'].CURNCYID) AS 'Currency ID'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORSUBTOT) AS 'Originating Sub Total'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORTAXAMT) AS 'Originating Tax Amount'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].ORDOCAMT) AS 'Originating Document Amount'
	,RTRIM(['Sales Transaction Work'].SALSTERR) AS 'Sales Territory ID'
	,RTRIM(['Sales Territory Master'].SALSTERR) AS 'Sales Territory'
	,RTRIM(['Sales Transaction Work'].SLPRSNID) AS 'Salesperson ID'
	,RTRIM(['Sales User-Defined Work History'].USERDEF1) AS 'Priority'
	,RTRIM(['Sales User-Defined Work History'].USERDEF2) AS 'Confirmation'
	,RTRIM(['Sales User-Defined Work History'].USRDEF03) AS 'Ordered By'
	,RTRIM(['Sales User-Defined Work History'].USRDEF04) AS 'Project Number'
	,RTRIM(['Sales User-Defined Work History'].USRDEF05) AS 'Project Name'
	,RTRIM(['RM Salesperson Master'].SLPRSNFN) + ' ' + RTRIM(['RM Salesperson Master'].SPRSNSLN) AS 'Salesperson'
FROM
	SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
LEFT JOIN
	SOP10106 AS ['Sales User-Defined Work History'] -- Sales User-Defined Work History (SOP10106)
		ON
			['Sales User-Defined Work History'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
			['Sales User-Defined Work History'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
LEFT JOIN
	RM00301 AS ['RM Salesperson Master'] -- RM Salesperson Master (RM00301)
		ON
			['RM Salesperson Master'].SLPRSNID = ['Sales Transaction Work'].SLPRSNID
LEFT JOIN
	RM00303 AS ['Sales Territory Master'] -- Sales Territory Master File (RM00303)
		ON
			['Sales Territory Master'].SALSTERR = ['Sales Transaction Work'].SALSTERR
WHERE
	['Sales Transaction Work'].SOPTYPE = 1
GO

GRANT SELECT ON uv_AZRCRV_SalesQuoteHeaders TO DYNGRP
GO