SQL View to Get Assembly BOM Items for Sales Transactions in Microsoft Dynamics GP

This script was written for a client who uses Inventory BOMs in Microsoft Dynamics GP and wanted to be able to see all of the component items from those BOMs when they were looking at Sales Order transactions; this would give them some visibility of which component items were going to be needed from quotes or orders on the system.

They only use BOMs in a light way so this script doesn’t cater for BOMs which are inactive or contains inactive items; it wouldn’t be too difficult to update to include these, but the particular question would edit a BOM adding or removing items, rather than creating a new version of the BOM.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GetBOMComponentsOnSalesTrx', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetBOMComponentsOnSalesTrx
GO
-- create view
CREATE VIEW uv_AZRCRV_GetBOMComponentsOnSalesTrx AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
SELECT
	['Sales Transaction Amounts'].SOPNUMBE
	,['Sales Transaction Amounts'].ITEMNMBR
	,['Sales Transaction Amounts'].ITEMDESC
	,['Bill of Materials Component'].CMPTITNM
	,['Item Master - Component'].ITEMDESC AS COMPDESC
	,['Bill of Materials Component'].Design_Qty * ['Sales Transaction Amounts'].QTYFULFI AS COMPQTY
FROM
	(
		SELECT
			SOPTYPE
			,SOPNUMBE
			,ITEMNMBR
			,ITEMDESC
			,QTYFULFI
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] WITH (NOLOCK) --Sales Transaction Amounts Work (SOP10200)
		UNION ALL
			SELECT
				SOPTYPE
				,SOPNUMBE
				,ITEMNMBR
				,ITEMDESC
				,QTYFULFI
			FROM
				SOP30300 AS ['Sales Transaction Amounts History'] WITH (NOLOCK) --Sales Transaction Amounts History (SOP30300)
	) AS ['Sales Transaction Amounts']
INNER JOIN
	(
		SELECT
			SOPTYPE
			,SOPNUMBE
			,DOCDATE
		FROM
			SOP10100 AS ['Sales Transaction Work'] WITH (NOLOCK) --Sales Transaction Work (SOP10100)
		UNION ALL
			SELECT
				SOPTYPE
				,SOPNUMBE
				,DOCDATE
			FROM
				SOP30200 AS ['Sales Transaction History'] WITH (NOLOCK) --Sales Transaction History (SOP30200)
	) AS ['Sales Transactions']
		ON
			['Sales Transactions'].SOPTYPE = ['Sales Transaction Amounts'].SOPTYPE
		AND
			['Sales Transactions'].SOPNUMBE = ['Sales Transaction Amounts'].SOPNUMBE
LEFT JOIN
	BM00111 AS ['Bill of Materials Component'] WITH (NOLOCK) --Bill of Materials Component (BM00111)
		ON
			['Bill of Materials Component'].ITEMNMBR = ['Sales Transaction Amounts'].ITEMNMBR
INNER JOIN
	IV00101 AS ['Item Master - Component'] WITH (NOLOCK) --Item Master (IV00101)
		ON
			['Item Master - Component'].ITEMNMBR = ['Bill of Materials Component'].CMPTITNM
GO

GRANT SELECT ON uv_AZRCRV_GetBOMComponentsOnSalesTrx TO DYNGRP
GO