I 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