Day: July 19, 2023

SQL Scripts for Microsoft Dynamics GP: Sales by Customer By Year

Ian Grieve4 minute read0 comment

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by customer:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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	
	['RM Customer MSTR'].CUSTNMBR AS CustomerNumber
	,['RM Customer MSTR'].CUSTNAME AS CustomerName
	,['RM Customer MSTR'].CUSTCLAS AS CustomerClass
	,['RM_Class_MSTR'].CLASDSCR AS CustomerClassDescription
	,ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson
	,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4]
FROM
	(
		SELECT
			CUSTNMBR
			,DOCNUMBR
			,RMDTYPAL
			,DOCDATE
			,GLPOSTDT
			,CASE WHEN RMDTYPAL < 7 THEN
				SLSAMNT
			ELSE
				SLSAMNT * -1
			END AS SLSAMNT
		FROM
			RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen]
		WHERE
			RMDTYPAL <> 9
		AND
			VOIDSTTS = 0
		UNION ALL
			SELECT
				CUSTNMBR
				, DOCNUMBR
				, RMDTYPAL
				, DOCDATE
				, GLPOSTDT
				,CASE WHEN RMDTYPAL < 7 THEN
					SLSAMNT
				ELSE
					SLSAMNT * -1
				END AS SLSAMNT
			FROM
				RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen]
			WHERE
				RMDTYPAL <> 9
			AND
				VOIDSTTS = 0
	) AS ['Sales Data']
	INNER JOIN
		RM00101 ['RM Customer MSTR'] --RM Customer MSTR (RM00101)
			ON
				['Sales Data'].CUSTNMBR = ['RM Customer MSTR'].CUSTNMBR
	LEFT OUTER JOIN
		RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301)
			ON
				['RM Customer MSTR'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID
	LEFT OUTER JOIN
		RM00201 AS ['RM_Class_MSTR'] --RM_Class_MSTR (RM00201)
			ON
				['RM Customer MSTR'].CUSTCLAS = ['RM_Class_MSTR'].CLASSID
GROUP BY
	['RM Customer MSTR'].CUSTNMBR
	,['RM Customer MSTR'].CUSTNAME
	,['RM Customer MSTR'].CUSTCLAS
	,['RM Salesperson Master'].SLPRSNID
	,['RM Salesperson Master'].SLPRSNFN
	,['RM Salesperson Master'].SPRSNSLN
	,['RM_Class_MSTR'].CLASDSCR
GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I… Setup Inventory for Locations

Ian Grieve3 minute read1 Comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

There is only one setting required for the use of locations in Business Central. That is to make the locations mandatory; this is a very important step to do if you have even a single location defined in Business Central. If locations are not mandatory, then users can process stock related transactions without selecting a location for the goods, which can result in unexpected behaviour. Goods processed in this way will show as being at an unspecified location

If you have been processing in Business central without locations mandatory, you will need to do some transfers of goods from the unspecified location to an actual location before you make locations mandatory.

To make locations mandatory, do a search in the Tell me what you want to do for Inventory Setup; toggle the Location Mandatory option on:

Inventory Setup

With this option enabled, all stock processing will now require a site to be selected.

In Microsoft Dynamics 365 Business Central, how do I…

In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I…