Day: December 30, 2022

SQL Scripts for Microsoft Dynamics GP: Status of Items Based on Serial Number

Ian Grieve3 minute read0 comment

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

This script will return a list of items calculating the status of serial number tracked items based on the posting status and the sales transaction type.

/*
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
	['Purchasing Serial Lot History'].ITEMNMBR
	,['Item Master'].ITEMDESC
	,['Purchasing Serial Lot History'].SERLTNUM
	,['Purchasing Serial Lot History'].UNITCOST
	,['Purchasing Serial Lot History'].DATERECD
	,['Sales Serial/Lot Work and History'].SOPNUMBE
	,CASE WHEN ['Sales Serial/Lot Work and History'].POSTED = 1 THEN
		'Invoiced'
	WHEN ['Sales Serial/Lot Work and History'].SOPTYPE = 3 THEN 
		'Despatched'
	WHEN ['Sales Serial/Lot Work and History'].POSTED IS NULL THEN 
		'Available'
	ELSE
		'Allocated'
	END AS SHIPDSTS
FROM
	POP30330 AS ['Purchasing Serial Lot History'] --Purchasing Serial Lot History (POP30330)
INNER JOIN
	IV00101 AS ['Item Master'] --Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Purchasing Serial Lot History'].ITEMNMBR
LEFT JOIN
	SOP10201 AS ['Sales Serial/Lot Work and History'] --Sales Serial/Lot Work and History (SOP10201)
		ON
			['Sales Serial/Lot Work and History'].ITEMNMBR = ['Purchasing Serial Lot History'].ITEMNMBR
		AND
			['Sales Serial/Lot Work and History'].SERLTNUM = ['Purchasing Serial Lot History'].SERLTNUM
GO

Reflections on Microsoft Dynamics ERP After Four Months with Microsoft Dynamics 365 Business Central

Ian Grieve4 minute read0 comment

Microsoft Dynamics 365 Business CentralI started this blog back in June 2011 to contribute back to the Dynamics GP Community from which I’d been drawing information since I started working with Dynamics GP in the July of 2003. There were quite a few contributors around at the time including David Musgrave, Mark Polino, Mariano Gomez, Belinda Allen, Victoria Yudin, Leslie Vail and Mohammad Daoud amongst quite a few others. Other contributors have come along since and quite a few have disappeared through time as their careers took other paths.

In September of this year my career also took a change which means I no longer work with or blog about Dynamics GP (other than a few posts from by backlog and some SQL scripts), but am now working and blogging about Dynamics 365 Business Central. After just over 19 years of working with Dynamics GP I decided that it was time for a new challenge and I changed role to work for 4PS UK, a long time Dynamics NAV and latterly Dynamics BC partner specialising in software for the construction and civil engineering sectors in the UK (sectors in which I do have some previous exposure).

Why did I make the change from Dynamics GP to Dynamics BC? Well, while Dynamics GP has a roadmap which runs through 2028, it is no longer getting the investment it once did and the messaging around its future from Microsoft isn’t great. This investment pattern is in part because it is a mature system which has all the basic functions one would expect of an ERP system and also has a thriving ecosystem of third party add-ons available which extend that functionality further.

The enhancements made to it are guided by suggestions from the community as to which improvements it wants to see.

The other part is that Microsoft’s attention is on other products, like Dynamics BC. Dynamics GP gets updates based on client requests, regulatory and security updates and updates to keep it compatible with other Microsoft products (like Office).

Continue reading “Reflections on Microsoft Dynamics ERP After Four Months with Microsoft Dynamics 365 Business Central”