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'

SELECT
Item."Item Description",
Bill."FINISHED GOOD ITEM NUMBER",
Bill."Component",
(Bill."QTY" * @needed) as "QTY",
Item."QTY Available",
Item."QTY On Order",
Item."QTY On Hand",
Item."QTY Allocated",
CASE
WHEN Sub.SubParts IS NOT NULL
THEN 1
ELSE 0
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'
LEFT JOIN
(
SELECT "Finished Good Item Number", COUNT(*) as SubParts FROM dbo.BillofMaterials
GROUP BY "Finished Good Item Number") as Sub
ON
Sub."Finished Good Item Number" = Bill."Component"

WHERE Bill."FINISHED GOOD ITEM NUMBER" = @item

  1. No comments yet.

  1. No trackbacks yet.