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
