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 lines and related fields such as the sales user defined.
-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesQuoteLines', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_SalesQuoteLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesQuoteLines] 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 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(['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
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 = 1
GO
GRANT SELECT ON uv_AZRCRV_SalesQuoteLines TO DYNGRP
GO
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.