Which items can be built (do we have enough product for)?

  • pietlinden

    SSC Guru

    Points: 62452

    I have a stupid question. I'm dealing with a Bill Of Materials problem... I can get the bill of materials, but the kicker is I have Invoices with (ProductID, Quantity) and I need to figure out which I have the "ingredients" to manufacture. I know I can get a "table" of current inventory and another of "recipe" X qty... but is there a non-cursor based way of figuring how which Inventory line items I can build?

    I know I can do compare the (ProductID, totalQuantity) in the "recipes" to inventory, but how to do process the next order? Use a windowing function and do running totals of inventory? Then update when I assign a BOM full of items to a work order?

    Is there a sane way to do this without using a cursor? (Yeah, I know that's a dirty word, but if I do that, I know that I'm allocating parts I still have! I just stop allocating when I don't have enough of an "ingredient" in inventory. I was thinking of writing a query that go the current inventory levels of all the ingredients requested, and then did a running total to allocate ingredient quantities to finished product requests...

    Anybody have any pointers on how to do this? The cursor-ish approach isn't my favorite - process one request at a time until there isn't enough product left... But what are my other options?

    Thanks!

    Pieter

  • scdecade

    Old Hand

    Points: 394

    One sane way to allocate inventory is to do it when the invoices are generated.  Is the purpose here to answer the question "how many of which products can I make?" or is it to explicitly allocate the inventory?  Or both?  Is the inventory tracked by unique item or sku?  Do other processes compete for inventory items?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • jcelko212 32090

    SSCrazy Eights

    Points: 8844

    Google around for the term "relational division" as a solution. Many years ago (well maybe decades now) I did this for a mail-order barbecue company (Corky's). Their products are gift baskets of assorted food items. I've covered this example in several of my books.

    Look at this old article of mine for an explanation of this programming technique.

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • pietlinden

    SSC Guru

    Points: 62452

    I think I remember your article about "Find all pilots that can fly all the planes" question. (Yep, that's the one!)

    Is there a way to process the "build orders" without using a cursor? The reason I ask is because allocating parts to one "build order" means it won't be available to the next build order.  I played around with a windowing function to generate the current inventory level on the fly, but I'm not sure how stable that will be.  Given that there would be a reasonably limited number of daily build orders, I could get away with doing using a cursor... it would at least give me accurate part counts.

    I see how the pilots and planes example would fit this, but I don't see how to process the orders without using a cursor. I don't like cursors, but I really don't see any other option here. How would I know/calculate the new running inventory levels AND at the same time figure out if I have enough parts for a given build order? I can do them separately, but I don't see how to do the two together in a query.

    Yeah yeah.. I guess that makes me a rookie. Oh well.

    Thanks!

    Pieter

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715810

    Do you have to look at your orders in some sequence? Meaning, I might need 5 Product3 and 2 Product4 for an order, but also 2 Product3 and 4 Product4 for separate one. I could build either with some level of inventory, but not necessarily both.

    Not compelely sure how to solve this, though I think relational division is the right track. Just not sure you can do this in one query unless you are sure you have a sequence for the divisor. Even then, subtracting out the used quantities for order 1 before looking at order 2 might be overly complex in the SQL.

    Maybe Drew will chime in. He comes up with some great creative SQL solutions.

  • drew.allen

    SSC Guru

    Points: 76580

    Steve Jones - SSC Editor wrote:

    Maybe Drew will chime in. He comes up with some great creative SQL solutions.

    I would need some data and expected results to work with.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • jcelko212 32090

    SSCrazy Eights

    Points: 8844

    http://tdan.com/data-mining-on-a-budget/5343

    Here's a link to an old article I wrote of my adventures in barbecue land. It's easy enough to find the various shades of relational division pick which one is appropriate for your particular problem.

    However, you have to present each basket's content, one at a time. Otherwise, you wind up with a combinatorial explosion and this becomes an NP-complete problem. It's a real bitch to optimize these bin packing problems. The first problem is just defining what optimized means. Do you construct the largest number of completed assemblies from the parts available? You look at a window of (n) desired complete assemblies, and trial possible arrangements of them to see who meets what criteria. It's not easy

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • pietlinden

    SSC Guru

    Points: 62452

    Okay, sorry everybody. I found out that the question was way easier than I thought. (That's what happens when you don't get scripts to recreate the tables and data and some expected results.) Turns out, they wanted a the standard recursive CTE to explode a bill of materials... That and a summary query to count up part quantities.

    I guess on the plus side, I got to play with CTEs for a while. (I can hear the "How's your tempdb feeling?" already!)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715810

    If you're interested, writing up your experiences and learning how to do this would make a great beginner article. You may have a perspective other authors don't.

  • pietlinden

    SSC Guru

    Points: 62452

    Steve.

    I have a question about the rCTE example. In the rCTE, the Product Assembly is "decomposed" into parts, which is then decomposed again (in the rCTE)… but how do you know when you've reached the "bottom", so you know which (and how many) Component part IDs you need to build an item?

    I'm using the expanded version of AdventureWorks2016. Here's my code:

    I'm using the expanded version of AdventureWorks2016. Here's my code:

    use adventureworks2016_ext;
    go
    CREATE TABLE #InvoiceList (InvoiceID INT);
    GO
    -- you might use INSERT INTO... SELECT... to get a list of invoices to order parts for.
    -- I'm just cheating a little.
    INSERT INTO #InvoiceList (InvoiceID) VALUES (43659), (43660), (43661), (43662);
    WITH RecurThroughBOM AS
    (
        --- this part returns all the lowest-level components and quantities of an assembly
        SELECT
            BOM.ProductAssemblyID,
            BOM.ComponentID,
            BOM.PerAssemblyQty,
            BOM.BOMLevel
        FROM
            Production.BillOfMaterials BOM
        WHERE
            BOM.ProductAssemblyID IS NULL
            AND
            SYSDATETIME() BETWEEN BOM.StartDate AND ISNULL(BOM.EndDate,'2099-12-31')
        UNION ALL
        SELECT
            BOM.ProductAssemblyID,
            BOM.ComponentID,
            BOM.PerAssemblyQty,
            BOM.BOMLevel
        FROM
            Production.BillOfMaterials BOM
            INNER JOIN RecurThroughBOM CTE
            ON BOM.ProductAssemblyID = CTE.ComponentID
        WHERE
            sysdatetime() BETWEEN BOM.StartDate AND ISNULL(BOM.EndDate,'2099-12-31')
    )
    /*
        you could join this back to a "table" of Products you want to build
        because a CTE is basically a funky view.
    */
    SELECT inv.ComponentID
            ,SUM(inv.QuantityNeeded) As TotalNeeded
    FROM
    (SELECT dtl.SalesOrderID,
        dtl.ProductID,
        BOM.ComponentID,
        QuantityNeeded = dtl.OrderQty * BOM.PerAssemblyQty
    FROM
        Sales.SalesOrderDetail dtl
            INNER JOIN
        RecurThroughBOM BOM
            ON dtl.productID = BOM.ProductAssemblyID
            --- join to your "orders" table or whatever right here
            -- filtering out the orders that have been completed
    INNER JOIN #InvoiceList il ON dtl.SalesOrderID = il.InvoiceID
    ) inv
    GROUP BY inv.ComponentID
    ORDER BY inv.ComponentID;

    All that to ask one simple question:

    Is this returning just the very lowest level components (components that are not sub-assemblies or assemblies)? What is the BOMLevel telling me? whether the item is part of a larger assembly?

    thanks!

    Pieter

    • This reply was modified 3 weeks ago by  pietlinden. Reason: Clarification
  • pietlinden

    SSC Guru

    Points: 62452

    This is a "note to self", I guess, so...

    Self,

    this is as far as I have gotten. it's not quite right maybe, because I have yet to figure out how to return just subassemblies, but this much seems to work (I can join to a table of Orders that should be processed, and expand those in one query.  Here's the query... The #ProcessOrders is just a sort of stub table with a couple of Orders in it so I could test with more than one Order.

    use AdventureWorks2017;
    GO
    -- this is setup for the rest of the procedure
    CREATE TABLE #ProcessOrders (OrderToProcess INT PRIMARY KEY);
    GO
    INSERT INTO #ProcessOrders(orderToProcess) VALUES (43659),(43660);


    --- now I can actually  answer the question
    WITH cte_BOM (ProductID, Name, Color, Quantity, ProductLevel, ProductAssemblyID, Sort)
    AS  (SELECT P.ProductID,
                CAST (P.Name AS VARCHAR (100)),
                P.Color,
                CAST (1 AS DECIMAL (8, 2)),
                1,
                NULL,
                CAST (P.Name AS VARCHAR (100))
         FROM   Production.Product AS P
                INNER JOIN
                Production.BillOfMaterials AS BOM
                ON BOM.ComponentID = P.ProductID
                AND BOM.ProductAssemblyID IS NULL
                AND (BOM.EndDate IS NULL
                    OR BOM.EndDate > GETDATE())
         UNION ALL
         SELECT P.ProductID,
                CAST (REPLICATE('|---', cte_BOM.ProductLevel) + P.Name AS VARCHAR (100)),
                P.Color,
                BOM.PerAssemblyQty,
                cte_BOM.ProductLevel + 1,
                cte_BOM.ProductID,
                CAST (cte_BOM.Sort + '\' + p.Name AS VARCHAR (100))
         FROM   cte_BOM
                INNER JOIN Production.BillOfMaterials AS BOM
                ON BOM.ProductAssemblyID = cte_BOM.ProductID
                INNER JOIN Production.Product AS P
                ON BOM.ComponentID = P.ProductID
                AND (BOM.EndDate IS NULL
                    OR BOM.EndDate > GETDATE())
        )
    SELECT   cte_BOM.ProductID,
             [Name],
             Color,
             Quantity * sod.OrderQty AS Total_Qty,
             ProductLevel,
             ProductAssemblyID,
             Sort
    FROM     cte_BOM
    -- inner join to a list of products & quantities
    INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID = cte_BOM.ProductAssemblyID
    WHERE sod.SalesOrderID IN (SELECT orderToProcess FROM #ProcessOrders)
    ORDER BY Sort;
     

    The part I haven't worked out yet is how to select one or more subassemblies and get the parts for those, but I think that's trivial now... just ID the ones I want, and dump into a temp table and join.

    Go to bed, Self. It's late.

  • pietlinden

    SSC Guru

    Points: 62452

    If I sequence the Work Orders somehow, I could use one of those dreaded cursor things... then I could do something like this:

    1. get complete list of components required to fulfill the work order.
    2.  check to see if there are enough parts in inventory, (and if not add the missing items to the "shopping list")
    3.  update inventory (because some parts are already allocated, do they're not available for other processes anymore.

    Messy, maybe, but are there other ways of doing it? I think that if I didn't use a cursor, I could end up allocating resources to something when the resources no longer were available.

    Am I at least close to right on this?

    Thanks!

    Pieter

  • drew.allen

    SSC Guru

    Points: 76580

    I believe that this is a variation on the bin packing problem.  I think it's possible to solve without using a cursor, but I would need sample data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply