SQL View to Return Sales Quote Lines

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 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 ‘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

Read original post SQL View to Return Sales Quote Lines at azurecurve|Ramblings of a Dynamics GP Consultant