View To Return Sales Orders (Work Status) Requiring An Assembly

Microsoft Dynamics GPI have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).

I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.


CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN
		SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3)
	ELSE
		RTRIM(SOP102.SOPNUMBE)
	END +
	CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN
		'_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2))
	ELSE
		''
	END AS 'Assembly'
	,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number'
	,RTRIM(SOP102.ITEMNMBR) AS 'Item Number'
	,CASE WHEN SOP102.UOFM = 'EACH' THEN
		CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10))
	ELSE
		CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10))
	END AS 'Quantity'
	,SOP102.UOFM AS 'UofM'
	,SOP101.BACHNUMB AS 'Sales Batch'
FROM
	SOP10200 SOP102 WITH (NOLOCK)
INNER JOIN
	SOP10100 SOP101 WITH (NOLOCK)
		ON
			SOP101.SOPNUMBE = SOP102.SOPNUMBE
		AND
			SOP101.SOPTYPE = SOP102.SOPTYPE
INNER JOIN
	BM00101 BM101 WITH (NOLOCK)
		ON
			BM101.ITEMNMBR = SOP102.ITEMNMBR
		AND
			BM101.Bill_Status = 1
LEFT JOIN
	BM10200 BM102 WITH (NOLOCK)
		ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
LEFT JOIN
	BM30200 BM302 WITH (NOLOCK)
		ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
WHERE
	SOP102.SOPTYPE = 2
AND
	BM102.TRX_ID IS NULL
AND
	BM302.TRX_ID IS NULL
GO

GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP