Category: Dynamics

The last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.

IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,['Receivables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Sales Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,YEAR1 AS DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	INNER JOIN
		SY40101
			ON
				DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,YEAR1 AS DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		INNER JOIN
			SY40101
				ON
					DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR = ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,['Receivables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

DocAttach was introduced in Microsoft Dynamics GP 2013 and replaced the ole OLE Notes functionality in all areas of Dynamics GP 2013 R2.

You can attach all types of files to DocAttach including web links, except you cannot include shortcuts to files or folders on your network. While the intention of DocAttach is to be able to attach files and have them stored in the database.

However, if you deal with a substantial number of fixed assets and need images of the condition, or deal with technical diagrams, these files are often between quite and very large. As files are stored in the database as BLOBs (Binary Large OBjects) they are the same size in the database as they were originally; this can mean the database size will grow very quickly as files are added.

I have submitted a suggestion on Microsoft Connect asking that file and folder shortcuts be an allowed file type for Doc Attach.

You can vote for this suggestion here.

I did a post a few weeks ago which included a SQL view which could be used to return a list of purchases by creditors/vendors by year. I wrote the view for a client who operates a financial year which is the same as the calendar year.

However, most of my clients use different financial years, so I have also created a script which returns the same information, but links to the Financial Calendar to determine which financial, rather than calendar, year a transaction is within:

IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,['Payables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
		'Active'
	WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
		'Inctive'
	ELSE
		'Temporary'
	END AS 'Status'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		['PM Transaction OPEN File'].VENDORID
		,['Period Header'].YEAR1 AS DOCDATE
		,['PM Transaction OPEN File'].PSTGDATE
		,['PM Transaction OPEN File'].DOCNUMBR
		,['PM Transaction OPEN File'].DOCTYPE
		,['PM Transaction OPEN File'].PRCHAMNT
		,['PM Transaction OPEN File'].TRDISAMT
		,['PM Transaction OPEN File'].FRTAMNT
		,['PM Transaction OPEN File'].MSCCHAMT
		,['PM Transaction OPEN File'].TAXAMNT
		,['PM Transaction OPEN File'].DOCAMNT
	FROM
		PM20000 AS ['PM Transaction OPEN File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Transaction OPEN File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			['PM Paid Transaction History File'].VENDORID
			,['Period Header'].YEAR1 AS DOCDATE
			,['PM Paid Transaction History File'].PSTGDATE
			,['PM Paid Transaction History File'].DOCNUMBR
			,['PM Paid Transaction History File'].DOCTYPE
			,['PM Paid Transaction History File'].PRCHAMNT
			,['PM Paid Transaction History File'].TRDISAMT
			,['PM Paid Transaction History File'].FRTAMNT
			,['PM Paid Transaction History File'].MSCCHAMT
			,['PM Paid Transaction History File'].TAXAMNT
			,['PM Paid Transaction History File'].DOCAMNT
		FROM
			PM30200 AS ['PM Paid Transaction History File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Paid Transaction History File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
		WHERE
			VOIDED >= 0
		AND
			DOCTYPE <= 5
	) AS ['Payables Transactions']
LEFT JOIN
	PM00200 AS ['PM Vendor Master']
		ON
			['Payables Transactions'].VENDORID = ['PM Vendor Master'].VENDORID
GROUP BY
	['Payables Transactions'].VENDORID
	,['PM Vendor Master'].VENDNAME
	,['PM Vendor Master'].VNDCLSID
	,['PM Vendor Master'].VENDSTTS
	,['Payables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

UPDATE: Removed Format on DOCDATE. Thanks to Tim Wappat for pointing out the error.

This script is the result of a support call logged by a client where the incorrect value on sales invoices was being invoiced to customers. There was a small number of invoices being created which were showing the incorrect value; there was concern that the issue might be wider than thought, so I wrote this script to verify the sum of the Extended Cost of the lines on an invoice against the Subtotal.

This script is configured to check invoices, but could be used against other transaction types if the highlighted section is changed.

IF OBJECT_ID (N'uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal
GO
CREATE VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Sales Transaction Work'].SOPNUMBE
	,FORMAT(['Sales Transaction Work'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
	,['Sales Transaction Amounts Work'].XTNDPRCE
	,['Sales Transaction Work'].SUBTOTAL
	,'OPEN' AS TRXSTATUS
FROM
	SOP10100 AS ['Sales Transaction Work']
INNER JOIN 
	(SELECT
		SOPNUMBE
		,SOPTYPE
		,SUM(XTNDPRCE) AS XTNDPRCE
	FROM
		SOP10200
	WHERE
		SOPTYPE = 3 --invoice
	GROUP BY
		SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts Work']
			ON
				['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
			AND
				['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
WHERE
	['Sales Transaction Amounts Work'].XTNDPRCE <> ['Sales Transaction Work'].SUBTOTAL
UNION ALL
	SELECT
		['Sales Transaction History'].SOPNUMBE
		,FORMAT(['Sales Transaction History'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
		,['Sales Transaction Amounts History'].XTNDPRCE
		,['Sales Transaction History'].SUBTOTAL
		,'HIST' AS TRXSTATUS
	FROM
		SOP30200 AS ['Sales Transaction History']
	INNER JOIN 
		(SELECT
			SOPNUMBE
			,SOPTYPE
			,SUM(XTNDPRCE) AS XTNDPRCE
		FROM
			SOP30300
		WHERE
			SOPTYPE = 3 --invoice
		GROUP BY
			SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].SOPNUMBE = ['Sales Transaction History'].SOPNUMBE
				AND
					['Sales Transaction Amounts History'].SOPTYPE = ['Sales Transaction History'].SOPTYPE
	WHERE
		['Sales Transaction Amounts History'].XTNDPRCE <> ['Sales Transaction History'].SUBTOTAL
GO
GRANT SELECT ON uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

As mentioned in the last post I’ve had a couple of scripts recently which I needed to create in order to facilitate clients easily making live to test copies without issues arising.

The script, below, can be used to set the EFT file locations in Checkbook Master. For the client I was working with, we only needed to set the file location of the Domestic Payments File Location field, but I am including all of the file location fields in the script.

Simply change the highlighted path locations to the required paths and set the Checkbook ID at the bottom:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
UPDATE
	CM00101
SET
	DomPmtsFile = '{path}'
	,ForeignPmtsFile = '{path}'
	,DomDirDbtCollectFile = '{path}'
	,DomDirDbtRefundFile = '{path}'
	,EFTPMPrenoteFile = '{path}'
	,EFTRMPrenoteFile = '{path}'
	,EFTCommunicationFile = '{path}'
WHERE
	CHEKBKID = '{chequebook id}'

As always with scripts, please make sure you have a good backup before using the script.