SQL View to Return Sales Quote Lines

Microsoft Dynamics GPI'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

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *