I'm looking to build a query that is able to go through a BOM recursively with inventory and tell me what pieces I need to purchase in order to create the product.
For example: I want a query to list of all the bicycle parts I need to order to satisfy the orders for 15 bikes, 3 wheels, 2 tyre valves, 10 nuts. It should query through the current inventory and take account of that.
https://www.db-fiddle.com/f/r1hhkcWkZAHYjs6LKGPpTK/0
create table item (
item_sku varchar(10),
item_description nvarchar(255)
);
create table bom (
item_sku varchar(10),
parent_item_sku varchar(10),
quantity float
);
create table inventory (
item_sku varchar(10),
quantity float
);
-- ITEM DATA
insert into item (item_sku, item_description)
values ('10', 'Bicycle'),
('101', 'Wheels'),('102', 'Frame'),
('201','Spokes'),('202', 'Tyre'), ('203', 'Rim'), ('204', 'Hub'), ('205', 'Seat'), ('206', 'Handle'), ('207', 'Main Frame'),
('301', 'Trye Valve'),
('401', 'Bolt'),('402','Nut'),('403','Cap')
-- BILL OF MATERIALS DATA
insert into bom (item_sku, parent_item_sku, quantity)
values ('101', '10', 2),('102', '10', 1),
('201', '101', 48),('202', '101', 1),('203', '101', 1),('204', '101', 1),
('205', '102', 48),('206', '102', 1),('207', '102', 1),
('301', '202', 1),
('401', '301', 48),('402', '301', 1),('403', '301', 1)
-- INVENTORY DATA
insert into inventory (item_sku, quantity) values ('10', 3), ('101', 5), ('205', 10), ('301', 2), ('402', 15), ('401', 20)
-- Produce 15 bikes
-- Produce 3 wheels
-- Produce 2 Tyre Valves
-- Produce 10 Nuts
If this was my task, I would buy an ERP rather than reinvent the wheel.
If you need to do this strictly in SQL, you would need to know the "max BOM level" to dig down to. Dynamics GP (for example) if your BOM depth gets too deep, things can break or be incredibly slow.
With your sample data, your max depth appears to be 5 (10->101->202->301->402); is this the case each and every time OR is there no known "max depth" and you need to handle potentially 100 deep or more?
If the depth is unknown, I would look at handling this on the application side and do multiple calls to the database. If doing it on the database side is required, I think you are going to need to do a bunch of loops. One approach, have a temp table that stores the Item Sku, Item quantity, the BOM Item SKU, and the BOM Item Quantity along with a "processed" bit (0) and a uniquifier of some sort. Before you loop through, you insert your main items (mikes, wheels, tyre valves, and nuts) along with the first level child items and quantities and set bit processed to 0. Then loop while there exists a bit processed value of 0 in the table. First time through the loop you declare a cursor (local, fast forward) to pull all BOM Item SKU's and quantities from that table where bit processed is 0, then into loop number 2 that first updates the table to set the processed bit to 1 for the row you selected then inserts into the same table the current Item Sku you are looking at, the quantity, and the BOM items and quantities for that child item along with the processed bit (value is 0) and a uniquifier. Then onto the next item in the cursor and repeat. Once the cursor is complete, you will have your first 4 items, and all of their child items in the table. The parent items will have a processed value of 1 while the child ones will have processed value of 0. That is where the first loop comes in as some items will have a bit processed of 0 and thus the loop continues.
Then at the end, you just need to select from that temp table the Item Sku and the SUM of the Item Quantities, bring that back as a distinct list and you are good to go... sort of.
The problem that comes in is you have no indicator of which parts are "make" and which parts are "buy". What I mean is lets say your inventory was empty and you wanted to produce 1 bike. The final result would say to make 1 bike you need to buy 1 bike, 2 wheels, 1 frame, 96 spokes, 2 tyre, etc. We know ourselves that a "bike" is a make part, so we could exclude that pretty easily by putting a where clause on the final result set with something like "WHERE LEN(ITEM_SKU) > 2", but that feels messy and unreliable. What if in the future a bike can be a make OR buy part? So you can make them in house if the demand is low, or you can buy them from a supplier when the demand gets high for example.
And you run into the problem is a Wheel being a "make" or a "buy" part? If it is a buy part, then there is no problem. If it is a make part, then you probably want it excluded from your purchase list.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 25, 2021 at 5:26 pm
Thank you Brian. This is a great answer
I was hoping I didn't have to use a cursor... but had to for now. I will move the logic over the application rather on the the DB.
February 25, 2021 at 5:43 pm
Please see the folllowing 2 articles... there's no need for such things to be slow.
If you keep the "Adjacency List" going, you'll be able to easily maintain things and, in the latest tests on a modern piece of equipment, both methods take only 19 seconds to rebuild a million node hierarchy.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2021 at 8:15 pm
I strongly recommend that you give up the adjacency list model for an inventory. Use the nested set model instead. You can Google it, or read chapters in my book on Trees & Hierarchies and SQL. Your mindset is still locked into pointer chains and not yet into sets.
Thank you for the DDL even though it's wrong. By definition, every table should have a key; your non-table have none. While not wrong, SKU, and similar commercial codes are almost always fixed length and use the required subset of digits and letters that are part of the Unicode standard for such things. They are also often an industry-standard like UPC, GTIN, etc. using floating-point for quantity as an invitation for a disaster. I used to be a Fortran programmer decades ago and we had to learn to write very tricky code to avoid rounding errors with floating-point numbers. This was a two-week course! I'm going to guess you have not had such a course to avoid getting rounding errors. Quantity should be an integer or decimal. But more than that, quantity is an attribute of the inventory units and should be a column in that table. This looks like what is called an attribute splitting error.
I'm not going to retype the bill of materials, parts, explosion. You can do that easily yourself. Once you learn how to use the nested set model.
CREATE TABLE Inventory
(item_sku CHAR(10) NOT NULL PRIMARY KEY,
item_description NVARCHAR(255) NOT NULL,
onhand_quantity INTEGER :NOT NULL CHECK(onhand_quantity >= 0)
);
Please post DDL and follow ANSI/ISO standards when asking for help.
February 25, 2021 at 8:35 pm
Heh... and I strongly recommend that you don't give up the Adjacency List model because of how incredibly easy it is to maintain. Use it to drive the creation of brand new Nested Sets (or the alternative in the second article I posted the link for) when you need to. As I've stated, that creation is now lightning fast compared to the old push-stack method.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2021 at 7:51 am
But does the Adjacency model actual satisfy BOM requirements?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply