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

This 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