Day: April 15, 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 invoice lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceLines] 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 Amounts Work'].LNITMSEQ AS 'Line Number'
	,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number'
	,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description'
	,RTRIM(['Item Master'].TCC) AS 'Tax Commodity Code'
	,'UK' AS 'Country of Origin'
	,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure'
	,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price'
	,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site'
FROM
	SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
INNER JOIN
	SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
		ON
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
			['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
INNER JOIN
	IV00101 AS ['Item Master'] -- Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
LEFT JOIN
	SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300)
		ON
			['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
		AND
			['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
WHERE
	['Sales Transaction Amounts Work'].SOPTYPE = 3
GO

GRANT SELECT ON uv_AZRCRV_SalesInvoiceLines TO DYNGRP
GO