Processing Build Item Orders -- do I need a cursor?

  • pietlinden

    SSC Guru

    Points: 62816

    say I'm working on a database like AdventureWorks2017 - the standard SalesOrderHeader, SalesOrderDetail (ProductID, QtyOrdered).  Then Parts and PartInventory (PartID, LocationID, QtyOnHand)...

    and I want to know if I have all the "pieces" to build all the items in a SalesOrder. Writing a query to get the quantity of each Part that's required is trivial. To determine if a quantity of ProductX could be built, I'd just check that the "box of parts/part quantities" is less than or equal to what's on hand. But given that each thing I build will decrease inventory of available parts to build the next item, I would have to use a cursor for that, right? The closest I could come without it is to use a windowing function that does a running total of Qty on Hand of each "ingredient".

    Right? I should go build one and try it out. Here's an analogous query using AdventureWorks2017...

    USE AdventureWorks2017;
    GO

    DECLARE @SalesOrderID INT = 43659;

    IF EXISTS (
    SELECT soh.CustomerID
    , soh.SalesOrderID
    , soh.OrderDate
    , soh.[Status] OrderStatus
    , sod.ProductID
    , sod.OrderQty
    FROM sales.SalesOrderHeader soh
    INNER JOIN sales.SalesOrderDetail sod
    ON soh.SalesOrderID = sod.SalesOrderID
    INNER JOIN Production.ProductInventory ppi
    ON sod.ProductID = ppi.ProductID
    WHERE soh.SalesOrderID = @SalesOrderID
    AND sod.OrderQty > ppi.Quantity
    )
    BEGIN
    PRINT 'Somethings not in stock.';
    /* mark the work order as "waiting" */
    END
    ELSE
    BEGIN
    PRINT 'All systems go! Order can be fulfilled.';
    /* update the Sales Order to "ship" */
    END;

    I don't see an alternative to using a cursor here, because as I set one order to "ship", it decreases my available quantity on hand, which affects what orders I can fill from existing inventory after that. My cursor would open on the SalesOrderHeader / Details, and process from there. is there another way to do this that would be better? If so, please point it out.

    Thanks!

    Pieter

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    If you're wanting to know for an ongoing stream of orders whether you have enough parts to fulfill at any point in time, then you'd have to either use your cursor to step through or queue the parts somehow to reserve whatever volume you have for orders on hand, and see what's left as remaining inventory. I don't know AdventureWorks well enough, are there dates on when the parts are received or something, so you could use a windowing function for "orders received between X and Y" compared to "parts on hand as of Y"?

    If you're just checking right now if you have enough, then just sum(count()) of parts for each order, total it up and see if that's what you have.

    Suppose that's the difference between real life and the sample dataset "academic" exercise.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • pietlinden

    SSC Guru

    Points: 62816

    I could do it like this, I suppose...

    use AdventureWorks2017;
    go

    -- what parts are in stock
    SELECT currInv.ProductId
    , currInv.TotalQOH
    , sod.OrderQty
    , rt_Sold = SUM(sod.OrderQty) OVER (PARTITION BY currInv.ProductID
    ORDER BY soh.OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    , RemainingQOH = TotalQOH - SUM(sod.OrderQty) OVER (PARTITION BY currInv.ProductID
    ORDER BY soh.OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    FROM
    (SELECT ProductID
    , TotalQOH = SUM(Quantity)
    FROM Production.ProductInventory
    GROUP BY ProductID) currInv
    INNER JOIN Sales.SalesOrderDetail sod
    ON currInv.ProductID = sod.ProductID
    INNER JOIN Sales.SalesOrderHeader soh
    ON sod.SalesOrderID = soh.SalesOrderID

    That would tell me how much inventory I had left to fulfill new orders. I'd just have to do the same running total against queued orders to get and subtract those too. So the set would be something like

    [Current Inventory] - [Queued Orders] = [Total Available Inventory]

    Either that or create a stored procedure / trigger that decrements Quantity on Hand when an order is "processed" (so that the parts for it are reserved).  but that seems really ugly.

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    Either that or create a stored procedure / trigger that decrements Quantity on Hand when an order is "processed" (so that the parts for it are reserved).  but that seems really ugly.

    And that's inventory management for you lol. You have to make sure you're differentiating between "quantity on hand" and "reserved for existing order fulfillment". If you don't, things shake out, but finding the difference between the two is much more effective

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Mike01

    SSChampion

    Points: 11275

    I don't have the db installed, but wondering if you could use lag function to keep track of quantity on hand as you mark things ready to ship

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • pietlinden

    SSC Guru

    Points: 62816

    progress! I created a Table-valued function to do the BOM stuff, then used CROSS APPLY to join it to OrderDetails, and it worked. (It's an approximation of a Work Order to build some quantities of products) FWIW, here it is:

    CREATE FUNCTION udfProductBOM (
    @FinalProductID INT
    )
    RETURNS TABLE
    AS
    RETURN
    WITH cteAssembly(AssemblyID,ComponentID,PerAssemblyQty,ComponentLevel)
    -- Root (anchor) part
    AS (SELECT b.ProductAssemblyID,
    b.ComponentID,
    b.PerAssemblyQty,
    0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = @FinalProductID
    -- Recursive part - recursive CTE
    UNION ALL
    SELECT bom.ProductAssemblyID,
    bom.ComponentID,
    p.PerAssemblyQty,
    ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
    INNER JOIN cteAssembly AS p
    ON bom.ProductAssemblyID = p.ComponentID)
    SELECT AssemblyName = p1.Name,
    a.ComponentID,
    Component = p2.Name,
    a.PerAssemblyQty,
    ComponentLevel
    FROM Production.Product p1
    INNER JOIN cteAssembly AS a
    ON a.AssemblyID = p1.ProductID
    INNER JOIN Production.Product AS p2
    ON a.ComponentID = p2.ProductID;

    (The goal was to return the component materials for a single item.)

    Then use that in the SalesOrder/SalesOrderDetail as a substitute for WorkOrder/WorkOrderDetail like this:

    CREATE PROCEDURE GetSalesOrderPartsList
    @SalesOrderID INT
    AS
    SELECT sod.SalesOrderID
    -- , sod.SalesOrderDetailID
    -- , sod.OrderQty
    -- , sod.ProductID
    -- , bom.AssemblyName
    , bom.ComponentID
    , bom.Component
    -- , bom.PerAssemblyQty
    -- , bom.ComponentLevel
    , PartQty = SUM(bom.PerAssemblyQty * sod.OrderQty)
    FROM Sales.SalesOrderDetail sod
    CROSS APPLY udfProductBOM(sod.ProductID) bom
    WHERE sod.SalesOrderID = @SalesOrderID
    GROUP BY sod.SalesOrderID
    , bom.ComponentID
    , bom.Component
    ORDER BY sod.SalesOrderID
    , bom.ComponentID;

    For the original question... "Do I need a cursor?" If I'm just seeing if there are sufficient quantities of parts for a series of orders (assuming nothing goes wrong), I think I could use a windowing function with a running total of remaining parts. (I'd have to check for negative stock levels though).  This one may be one of those "it depends" ones - on what other processes have to be checked. It might be easier to do it that way, so I may try it that way first, and then with just a running total.

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

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