A recent project required a report of serial numbers received into Microsoft Dynamics GP on purchase orders and to which sales transaction they’d been allocated. I had a hunt around in my scripts folder and found an old script I’d written which only required some small changes to add the required fields.
The script uses INNER JOIN
clauses as only assigned serial numbers were wanted, but this could easily be changed to LEFT JOIN
to return serial numbered items which had been received but not yet allocated.
-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POPSOPSerialNumbers', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_POPSOPSerialNumbers
GO
-- create view
CREATE VIEW uv_AZRCRV_POPSOPSerialNumbers AS
SELECT
['Purchasing Receipt Line Quantities'].PONUMBER
,['Purchasing Receipt Line Quantities'].POPRCTNM
,['Purchasing Receipt Line Quantities'].VENDORID
,['Purchasing Receipt Line Quantities'].TRXLOCTN
,['Sales Serial/Lot Work AND History'].ITEMNMBR
,[Purchasing Serial Lot History'].SERLTNUM
,['Sales Serial/Lot Work AND History'].SOPTYPE
,CASE ['Sales Serial/Lot Work AND History'].SOPTYPE
WHEN 1 THEN 'Quote'
WHEN 2 THEN 'Order'
WHEN 3 THEN 'Invoice'
WHEN 5 THEN 'Back Order'
ELSE ''
END AS 'Type'
,['Sales Serial/Lot Work AND History'].SOPNUMBE
,['Sales Transactions'].DOCDATE
,['Sales Transactions'].CUSTNMBR
FROM
POP30330 AS [Purchasing Serial Lot History'] -- Purchasing Serial Lot History (POP30330)
INNER JOIN
POP10500 AS ['Purchasing Receipt Line Quantities'] -- Purchasing Receipt Line Quantities (POP10500)
ON
['Purchasing Receipt Line Quantities'].POPRCTNM = [Purchasing Serial Lot History'].POPRCTNM
AND
['Purchasing Receipt Line Quantities'].RCPTLNNM = [Purchasing Serial Lot History'].RCPTLNNM
INNER JOIN
SOP10201 AS ['Sales Serial/Lot Work AND History'] -- Sales Serial/Lot Work and History (SOP10201)
ON
['Sales Serial/Lot Work AND History'].SERLTNUM = [Purchasing Serial Lot History'].SERLTNUM
INNER JOIN
(
SELECT
SOPNUMBE
,SOPTYPE
,DOCID
,DOCDATE
,CUSTNMBR
,CUSTNAME
FROM
SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100)
UNION ALL
SELECT
SOPNUMBE
,SOPTYPE
,DOCID
,DOCDATE
,CUSTNMBR
,CUSTNAME
FROM
SOP30200 AS ['Sales Transaction History'] -- Sales Transaction History (SOP30200)
) AS ['Sales Transactions']
ON
['Sales Transactions'].SOPNUMBE = ['Sales Serial/Lot Work AND History'].SOPNUMBE
AND
['Sales Transactions'].SOPTYPE = ['Sales Serial/Lot Work AND History'].SOPTYPE
GO
GRANT SELECT ON uv_AZRCRV_POPSOPSerialNumbers TO DYNGRP
GO
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.
This is super helpful thanks