Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
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 (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).
CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN
CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN
'_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2))
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))
CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10))
END AS 'Quantity'
,SOP102.UOFM AS 'UofM'
,SOP101.BACHNUMB AS 'Sales Batch'
SOP10200 SOP102 WITH (NOLOCK)
SOP10100 SOP101 WITH (NOLOCK)
SOP101.SOPNUMBE = SOP102.SOPNUMBE
SOP101.SOPTYPE = SOP102.SOPTYPE
BM00101 BM101 WITH (NOLOCK)
BM101.ITEMNMBR = SOP102.ITEMNMBR
BM101.Bill_Status = 1
BM10200 BM102 WITH (NOLOCK)
ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
BM30200 BM302 WITH (NOLOCK)
ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
SOP102.SOPTYPE = 2
BM102.TRX_ID IS NULL
BM302.TRX_ID IS NULL
GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP
Read original post View To Return Sales Orders (Work Status) Requiring An Assembly at azurecurve|Ramblings of a Dynamics GP Consultant
Business Applications communities