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
Can ISC Software help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
5 thoughts on “View To Return Sales Orders (Work Status) Requiring An Assembly”