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

Question

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

pietlinden

SSC Guru

Points: 63428
More actions
June 1, 2020 at 11:17 pm

#3759236
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

Viewing 9 posts - 1 through 8 (of 8 total)

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

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 1

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: 63428 More actions · Answer 2

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: 6611 More actions · Answer 3

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: 11393 More actions · Answer 4

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: 63428 More actions · Answer 5

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.

pietlinden SSC Guru Points: 63428 More actions · Answer 6

Correct me if I'm wrong, but if I'm processing a series of work orders for products I need to build, then the only way to do it seems to be with a cursor. I think so because if I start with some level of inventory, each work order/request will change the quantities of parts on hand... which will affect what items can be built after the current one. the basic flow is something like

for each work order...

check there are parts in stock to complete the order, if yes, then reserve the parts. If not, order the necessary parts and leave the work order in the queue.
once the parts arrive, add the work order to the "build" queue.

(Or did I miss something obvious?)

Thanks,

Pieter

frederico_fonseca SSCoach Points: 16017 More actions · Answer 7

its more than that - and it can be done with set based instead of a cursor assuming doing it in T-SQL alone.

but take in consideration this aspect you have 100 ITEMX

which one does the business prefer

fulfil 10 small orders that require 10 ITEMX each - profit = 20
fulfil 2 small orders that require 50 ITEMX each - profit = 5

or should orders always be fulfilled based on order placement order e.g. FIFO style.

there are other aspects that affect sequence of making items - not going to talk about them as it has been tooo many years since I did that type of process. but there are lots of info on the net about this.

pietlinden SSC Guru Points: 63428 More actions · Answer 8

As it wasn't my database, I would assume FIFO, but then I could be wrong. <g>

Frederico, any pointers on how to do it without a cursor? The only way I could think of would be to do running totals and then subtract from inventory. If there's a site that explains that, I'd be happy to read it.

Thanks!

Pieter