azurecurve

GeneralUser Error When Migrating Management Reporter to a New Server

Friday 7th May 2021Wednesday 28th Apr 2021 Ian Grieve2 minute read0 comment

Across the years since it was launched I have done a lot of work with Management Reporter, including many migrations to new servers and upgrades which necessitated a migration to a new server or instance of SQL Server, but have never seen this particular error message before.

In this case, I migrated the Management Reporter database to a new server and installed the very latest version of the server software. When I started the database configuration, I received this error message:

Validation Messages
Database configuration: The connection to the database was successful, but the connection to database 'Management Reporter' failed. Verify that the Management Reporter service account has been added to the General User role in the database.

Continue reading "GeneralUser Error When Migrating Management Reporter to a New Server"

PrintFriendly Email Linkedin Twitter Facebook

Update Microsoft Dynamics GP Customer Emails on Test Including Sent Emails

Thursday 6th May 2021Wednesday 28th Apr 2021 Ian Grieve4 minute read0 comment

After creating a new script for updating vendor emails on test which includes the emails of sent items, I figured that a similar script will be needed to update the email address on sent emails in the Sales Series.

The script below, includes the tables holding sent email information for the Sales Series emails (both Receivables Management and Sales Order Processing) as well as the usual Internet Addresses table; the highlighted email address can be changed to whatever email address you're using for testing your Sales emails:

/*
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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

-- UPDATE existing email addresses
UPDATE
	SY01200 --Internet Addresses (SY01200)
SET
	INET1 = @TestEmail
	,EmailToAddress = @TestEmail
	,EmailCcAddress = ''
	,EmailBccAddress = ''
WHERE
	Master_Type = 'CUS'
				
-- UPDATE Email Details of previously sent emails
UPDATE
	SY04910 --Email Details (SY04910)
SET
	EmailToAddress = @TestEmail
	,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END
WHERE
	MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing
			
-- UPDATE Email Details of previously sent emails
UPDATE
	SY04915 --Email History (SY04915)
SET
	EmailToAddress = @TestEmail
	,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END
WHERE
	MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing
GO

PrintFriendly Email Linkedin Twitter Facebook

Update Microsoft Dynamics GP Vendor Emails on Test Including Sent Emails

Wednesday 5th May 2021Wednesday 28th Apr 2021 Ian Grieve5 minute read0 comment

About 10 years ago I did a post on changing emails on the test system so they didn't go outside the organisation. This has worked well for a long time and no-one has mentioned any issues, until this week when I was working with a client and we made some changes to a remittance format and tested by emailing.

The remittance itself emailed to the test address fine, but the reprint remittance went to the original email address of the vendor used on the live system.

After doing a little exploring I found there were two additional tables which held this data for sent emails and which were used by the reprint remittance function.

The script below, includes the Internet Addresses table as well as these two tables for the Purchasing Series emails (both Purchase Order Processing and Payables Management); the highlighted email address can be changed to whatever email address you're using for testing your Purchasing emails:

/*
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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

-- UPDATE existing email addresses
UPDATE
	SY01200 --Internet Addresses (SY01200)
SET
	INET1 = @TestEmail
	,EmailToAddress = @TestEmail
	,EmailCcAddress = ''
	,EmailBccAddress = ''
WHERE
	Master_Type = 'VEN'
				
-- UPDATE Email Details of previously sent emails
UPDATE
	SY04910 --Email Details (SY04910)
SET
	EmailToAddress = @TestEmail
	,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END
WHERE
	MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management
			
-- UPDATE Email Details of previously sent emails
UPDATE
	SY04915 --Email History (SY04915)
SET
	EmailToAddress = @TestEmail
	,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END
WHERE
	MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management
GO

PrintFriendly Email Linkedin Twitter Facebook

Recent ISC Software Webinar: Powerful document generation for Dynamics GP

Tuesday 4th May 2021Tuesday 4th May 2021 Ian Grieve3 minute read0 comment

In our most recent webinar, we took a look at Powerful document generation for Dynamics GP. In this webinar, we covered how dox42 can be used to design attractive document templates in Microsoft Office and integrate data from Microsoft Dynamics GP and other systems such as SharePoint or Microsoft 365. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and show the benefits and some example uses of dox42 for generating documents with data from Dynamics GP and other systems:

Introducing dox42
Examples of how dox42 can be used
Template Design
Integrate data from various sources
Automate your output
Key benefits
Examples from Dynamics GP
- Quotation
- Commercial Invoice
Conclusion

Introducing dox42 ^

dox42 are headquartered in Vienna Austria and sell dox42 through a partner channel using companies such as ISC Software. It allows you to generate individualised documents from all your existing systems, including Microsoft Dynamics GP, automatically. Offers, quotes, contracts, inspections or server-reports, presentations, Excel-charts and insurance policies; dox42 is a flexible, powerful and intuitive application which allows you to use the common interface of Microsoft Word, Excel or PowerPoint to design document templates.

Continue reading "Recent ISC Software Webinar: Powerful document generation for Dynamics GP"

PrintFriendly Email Linkedin Twitter Facebook

Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items

Friday 30th Apr 2021Tuesday 13th Apr 2021 Ian Grieve5 minute read0 comment

This post as been added as part of the series on creating a simple audit for Microsoft Dynamics GP, but wsn;t part of the original series.

I recently used the simple audit to add an audit to the Sales Customer Item Cross Reference (SOP60300) table to allow a client to keep an audit of changes to customer items. They wanted to keep track of all changes so this means three triggers are required on:

INSERT
UPDATE
DELETE

These triggers will record all customer items which are added, amended or removed. The Customer Items window contains a few fields, but the only ones with sensitive dta which needs to be audited are:

Customer Item Number
Customer Item Description

The first trigger creates the trigger which runs when data is inserted:

/*
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).
*/
CREATE TRIGGER utr_AZRCRV_SOP60300_AuditUpdate ON SOP60300 AFTER UPDATE AS
	INSERT INTO ut_AZRCRV_Audit
	SELECT
		'Sales Customer Item Cross Reference'
		,CAST(RTRIM(d.ITEMNMBR) AS VARCHAR(30)) + '|' + CAST(RTRIM(d.CUSTNMBR) AS VARCHAR(15))
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'Customer Item Number = ' + CAST(RTRIM(d.CUSTITEMNMBR) AS VARCHAR(30)) + ' | ' + 'Customer Item Description = ' + CAST(RTRIM(d.CUSTITEMDESC) AS VARCHAR(30))
		,'Customer Item Number = ' + CAST(RTRIM(i.CUSTITEMNMBR) AS VARCHAR(30)) + ' | ' + 'Customer Item Description = ' + CAST(RTRIM(i.CUSTITEMDESC) AS VARCHAR(30))
	FROM
		deleted AS d
	LEFT JOIN
		inserted AS i
			ON
				i.ITEMNMBR = d.ITEMNMBR
			AND
				i.CUSTNMBR = d.CUSTNMBR
GO

Continue reading "Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items"

PrintFriendly Email Linkedin Twitter Facebook

SQL Stored Procedure to Get the Next Microsoft Dynamics GP Sales Document Number

Thursday 29th Apr 2021Monday 3rd May 2021 Ian Grieve5 minute read0 comment

This stored procedure can be executed to generate the next sequential sales document number; this script was created to get the next sales invoice number for a transaction to be inserted into Microsoft Dynamics GP through eConnect. I write stored procedures as a wrapper around the eConnect stored procedure as we are often working with the clients IT department or a third party and this abstracts the call way from the other application so any changes by Microsoft can be managed within the wrapper stored procedure rather than the application.

This particular example is for generating a sales order number, but can be used to generate a document number for any of the sales document transactions. To do this, change the first highlighted parameter to one of the following numbers:

Quote
Order
Invoice
Return
Back Order
Fulfillment Order

The second parameter should be set to the Doc ID which will vary depending on how you have configured Sales Order Processing.

The stored procedure to get the next sales document number is:

-- drop stored proc if it exists[/sqlgrey]
IF OBJECT_ID (N'usp_AZRCRV_GetNextSOPDocumentNumber', N'P') IS NOT NULL
	DROP PROCEDURE usp_AZRCRV_GetNextSOPDocumentNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextSOPDocumentNumber 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).
*/
BEGIN
	DECLARE @return_value INT
	DECLARE @I_tSOPType TINYINT = 2
	DECLARE @I_cDOCID CHAR(15) = 'STDORD'
	DECLARE @I_tInc_Dec TINYINT = 1
	DECLARE @O_vSopNumber AS VARCHAR(21)
	DECLARE @O_iErrorState INT
	
	EXEC @return_value = taGetSopNumber  @I_tSOPType = @I_tSOPType, @I_cDOCID = @I_cDOCID, @I_tInc_Dec = @I_tInc_Dec, @O_vSopNumber = @O_vSopNumber  OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
	
	SELECT @O_vSopNumber 
END
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_GetNextSOPDocumentNumber TO DYNGRP
GO

You can execute the stored procedure using the below:

-- execute stored proc
EXEC usp_AZRCRV_GetNextSOPDocumentNumber
GO

I've written similar stored procedures in the past in other next numbers for other parts of Dynamics GP:

I have also posted a custom solution which can be used to generate a next number when there isn't a method available in eConnect.

PrintFriendly Email Linkedin Twitter Facebook

SQL View to Return Sales Invoice Lines

Thursday 15th Apr 2021Sunday 11th Apr 2021 Ian Grieve7 minute read0 comment

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

[/postcode]

PrintFriendly Email Linkedin Twitter Facebook

SQL View to Return Sales Invoice Headers

Wednesday 14th Apr 2021Sunday 11th Apr 2021 Ian Grieve14 minute read0 comment

This view returns sales invoice headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceHeaders] 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 'Invoice Number'
	,['Sales Transaction Work'].DOCID AS 'Invoice Type'
	,FORMAT(['Sales Transaction Work'].DOCDATE, 'dd-MM-yyyy') AS 'Document Date'
	,FORMAT(['Sales Transaction Work'].INVODATE, 'dd-MM-yyyy') AS 'Invoice Date'
	,FORMAT(['Sales Transaction Work'].INVODATE, 'yyyy-MM-dd') AS 'Invoice Date Sortable'
	,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date'
	,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date'
	,RTRIM(['Company Master'].ADRCNTCT) AS 'Shipper Contact Person'
	,RTRIM(['Company Master'].ADDRESS1) AS 'Shipper Address 1'
	,RTRIM(['Company Master'].ADDRESS2) AS 'Shipper Address 2'
	,RTRIM(['Company Master'].ADDRESS3) AS 'Shipper Address 3'
	,RTRIM(['Company Master'].CITY) AS 'Shipper City'
	,RTRIM(['Company Master'].STATE) AS 'Shipper State'
	,RTRIM(['Company Master'].ZIPCODE) AS 'Shipper Zip Code'
	,RTRIM(['Company Master'].COUNTY) AS 'Shipper Country'
	,RTRIM(['Company Master'].PHONE1) AS 'Shipper Phone 1'
	,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number'
	,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name'
	,RTRIM(['RM Customer Master'].TXRGNNUM) AS 'Tax Registration Number'
	,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'].PHNUMBR1) AS 'Phone 1'
	,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID'
	,(
		SELECT
			CONVERT(DECIMAL(10,0), SUM(QUANTITY))
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
		['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
	) AS 'Number Of Pieces'
	,(
		SELECT
			CONVERT(DECIMAL(10,2), SUM(QUANTITY) * SUM(['Item Master'].ITEMSHWT))
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
		INNER JOIN
			IV00101 AS ['Item Master'] -- Item Master (IV00101)
				ON
					['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
			['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
	) AS 'Total Weight'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].SUBTOTAL) AS 'Sub Total'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].TAXAMNT) AS 'Tax Amount'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].FRTAMNT) AS 'Freight Amount'
	,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].DOCAMNT) AS 'Document Amount'
FROM
	SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
LEFT JOIN
	RM00101 AS ['RM Customer Master'] -- RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
LEFT JOIN
	DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Sales Transaction Work'].SOPTYPE = 3
GO

GRANT SELECT ON uv_AZRCRV_SalesInvoiceHeaders TO DYNGRP
GO

[/postcode]

PrintFriendly Email Linkedin Twitter Facebook

SQL View to Return Sales Quote Lines

Tuesday 13th Apr 2021Tuesday 13th Apr 2021 Ian Grieve6 minute read0 comment

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

PrintFriendly Email Linkedin Twitter Facebook

SQL View to Return Sales Quote Headers

Monday 12th Apr 2021Monday 12th Apr 2021 Ian Grieve11 minute read0 comment

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

PrintFriendly Email Linkedin Twitter Facebook