Bill of Materials and Quantities in Dynamics GP 10
Here’s a SQL query that will allow you to see what components go into a given finished part number along with the available, on hand, allocated, and back ordered quantities.
This is useful if you have a rush job with not enough quantity available in GP and want to see what’s actually in house.
Just define @needed as how many items you’re trying to manufacture and set @item to the finished part number. You’ll also need to enter in your own location code in the left join for ItemQuantities. The “Assembly” column will let you know if the component is itself an assembly. When I make a report out of this in Business Intelligence Development Studio, I use this column to show or hide a symbol to navigate to this report with the part number of the component (pretty much “scoping in” to that part) to see if the material is available to make that.
DECLARE @needed as INT
DECLARE @item as char(20)
SET @needed = 1
SET @item = '115-0001'
Bill."FINISHED GOOD ITEM NUMBER",
(Bill."QTY" * @needed) as "QTY",
Item."QTY On Order",
Item."QTY On Hand",
WHEN Sub.SubParts IS NOT NULL
END as "Assembly"
FROM dbo.BillofMaterials as Bill
LEFT JOIN dbo.ItemQuantities as Item ON Bill."Component" = Item."Item Number" AND Item."Location Code" = 'YOUR LOCATION CODE'
SELECT "Finished Good Item Number", COUNT(*) as SubParts FROM dbo.BillofMaterials
GROUP BY "Finished Good Item Number") as Sub
Sub."Finished Good Item Number" = Bill."Component"
WHERE Bill."FINISHED GOOD ITEM NUMBER" = @item