SQL View to Return Sales Quote Headers

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

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 *