UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> For Each/loop?, Any Versions    
 
   
LilAnnCC1
post Sep 17 2019, 07:49 AM
Post#1



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA


Hello all! Using SQL SERVER 2017 as back-end with Access 2016 as front-end.

I use the following procedure to get a material list for 1 item (making a quantity of 1):

CODE
ALTER Procedure [dbo].[spBOMMaterialList]
@BOM int

AS
SET NOCOUNT ON;

With cte_BOMSubList
AS
(SELECT d.ID, d.BOMID, d.SubBOMID, d.PartID, d.bomQTY, 1 as [Level], @BOM as BOM
FROM tBOMDetail d
WHERE d.BOMID=@BOM

UNION ALL

SELECT d.ID, d.BOMID, d.SubBOMID, d.PartID, d.bomQTY*c.bomQTY, c.[Level]+1, c.BOM
FROM tBOMDetail d
INNER JOIN cte_BOMSubList c on d.BOMID=c.SubBOMID
WHERE d.BOMID=c.SubBOMID

)

SELECT * from cte_BOMSubList
WHERE SubBOMID is null
GO


I need to be able to get a material list for more than 1 item and need your help in directing me on how to set this up.

For example, I need a material list for every item on a work order.
Item 1 QTY 3
Item 2 QTY 1
Item 3 QTY 5

So I need the above procedure to run for each of these items X the quantity on the work order and sum any common materials for a complete list of materials needed to produce this work order. Note: Some work orders may have 1 item--others may have 30 items.

In other words, I'm trying to get a list of materials that need to be purchased in order to make these items on the work order.

Any advice is greatly appreciated.


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Sep 17 2019, 11:12 AM
Post#2



Posts: 3,330
Joined: 27-February 09



Okay, I think I figured it out. And no, you don't need a loop.

Here's what you have to do, though... Instead of using a stored procedure to explode your BOM, you need to use a table-valued function to do it. Because a table-valued function returns a table, you can join it to other tables (like OrderDetails or whatever yours is called). The big difference is that instead of using INNER or OUTER join, you use CROSS APPLY or OUTER APPLY.

So say you have your new table-valued function fnGetBOM(@ProductID) which returns a bunch of parts and quantities, you could then "join" that using CROSS APPLY to a real table. Then if you do the multiplication etc in your query, you'll get a full answer for all the orders in your WHERE clause.

CODE
SELECT od.*, bom.*
FROM OrderDetails od
CROSS APPLY fnGetBOM(od.ProductID) bom;


Here are some videos I found that were super handy. Start with the WiseOwl one:
table-valued functions: https://www.youtube.com/watch?v=nCAEgNxC7nU
joining TVFs to tables: https://www.youtube.com/watch?v=yqBl2as3ysw (bear with him, he's really smart, it's just the singsong delivery that's kinda annoying).



Go to the top of the page
 
MadPiet
post Sep 17 2019, 08:31 PM
Post#3



Posts: 3,330
Joined: 27-February 09



Couldn't find a good example, so I asked Chris Hyde. I think he gave me an answer to make me go away. LOL

This is his code:
CODE
WITH RecurThroughBOM AS
(
    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')
)
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
WHERE 1=1
-- AND    dtl.SalesOrderID IN (43659,47667,48373)
ORDER BY
    dtl.SalesOrderID,
    dtl.ProductID,
    BOM.ComponentID;


So you would join the CTE to the Orders you're trying to process (filtered set), and it should give you all of the parts for that.

What I learned (the hard way)… a CTE is just another kind of VIEW. So you can join it to other tables and views using normal INNER / OUTER join stuff just like you do with regular tables.

Yep, Bill Engvall came and I got my sign!!
Go to the top of the page
 
LilAnnCC1
post Sep 17 2019, 09:19 PM
Post#4



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA


LOL!

Thank you for the valuable information. Will be watching the videos shortly and looking more at the code you've posted. I really appreciate your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Sep 18 2019, 12:02 AM
Post#5



Posts: 3,330
Joined: 27-February 09



I hope this is a hard question, because the answers to this one aren't coming easy!

How do you block out what's inventory items have been allocated for a "recipe"? They're stamped with a "go" date or something? I'm trying to work out the running inventory, and this is what I was thinking:

CODE
use tempdb;
GO
CREATE TABLE #OrderDetails (
    OrderID INT NOT NULL,
    IngredientID CHAR(2) NOT NULL,
    Qty TINYINT NOT NULL);
GO
INSERT INTO #OrderDetails(OrderID, IngredientID, Qty)
VALUES (1, 'aa',5),(1,'bb',3),(1,'c',3),
(2,'aa',3),(2,'c',10),(3,'aa',2),(3,'bb',6),(3,'c',12);

CREATE TABLE Inventory (
        IngredientID CHAR(2) PRIMARY KEY,
    Qty TINYINT NOT NULL);
GO
INSERT INTO Inventory (ingredientID, qty)
VALUES ('aa',12),('bb',10),('c',22);


Get the running total of items required... You could join this to "ingredient" table and get the current quantity and then compare to the rtQty to see where you have to stop?

CODE
SELECT od.OrderID
    , od.IngredientID
    , od.Qty
    , i.Qty - SUM(od.Qty) OVER (PARTITION BY od.IngredientID
                     ORDER BY od.OrderID
                     ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW) AS rtQty
FROM #OrderDetails od
    INNER JOIN Inventory i ON od.IngredientID = i.IngredientID
ORDER BY IngredientID, OrderID;


then as long as none of the RunningTotalQuantity (rtQty) is not negative, you can build the line items up to that point.
This post has been edited by MadPiet: Sep 18 2019, 12:21 AM
Go to the top of the page
 
MadPiet
post Sep 18 2019, 08:37 PM
Post#6



Posts: 3,330
Joined: 27-February 09



Just picked someone's brain about this problem, because it's not easy. He confirmed my suspicion. what if you used a cursor (yeah, I know it's a dirty word, but in this case, I think it's an appropriate thing to do.) Cursors in T-SQL are similar to recordsets in Access VBA. The reason I would go the recordset route (and I only use them if I have to) is that after each "assignment" of resources to the manufacture of X quantity of some item, inventory has just been adjusted. And I have no clue how to decrement the inventory values without screwing something up. So it would be something like:

1. user selects what work order / invoice / request to work on.
2. explode the BOM using your query, and dump the result to a (temp ?) table.
3. Compare that to what's in stock. If everything is in stock, basically commit the resources to this batch, and maybe update the WO to "in progress".
4. If not everything is in stock, skip the work order and go to the next one?

You could also explode the BOM to the lowest level parts and insert that into a table... depends on how often your parts change. Maybe it's not feasible/practical. It would just save you a lot of tedious processing.

If you could order the "work orders/manufacture orders/invoices" in priority order, you could process them one set at a time. As I said before, the big thing is going from one "committed" state to another - the resources are allocated or they're not - it's a package deal (stored procedure time... with commits and rollbacks maybe).

maybe less than perfect, but completely workable that way.
Go to the top of the page
 
MadPiet
post Sep 19 2019, 12:39 AM
Post#7



Posts: 3,330
Joined: 27-February 09



Some daze, I can just stare at the same question for hours and still not understand what it's asking. It's asking for a shopping list. Okay...

You have your recursive CTE that explodes your Bill of Materials... there's nothing at all stopping you from joining your cteBOM to another table, like a list of (ProductID, Qty) that needs to be built. So you'd have your normal recursive CTE at the top, and then at the bottom of the query, where you use the rCTE, you could do something along the lines of

CODE
SELECT <fields>, rCTE.TotalQty * pdb.Qty
FROM rCTE
INNER JOIN ProductsToBuild pdb
ON pdb.ProductID = rCTE.ProductID


because a recursive CTE is basically a glorified view (returns a bunch of columns!)… and the join should cause more items to be "exploded", and then you can just sum up the Component quantities... you'd just have a totals query at the end with a SUM(rCTE.ComponentQty). Right? (or am I completely off my rocker again?)

In the final query, there's nothing keeping you from joining that result to another table, or inserting the result into a temporary or permanent table... In your case, you could compare the results from the CTE to what's currently in inventory, and adjust the "shopping list" accordingly.
This post has been edited by MadPiet: Sep 19 2019, 12:40 AM
Go to the top of the page
 
LilAnnCC1
post Sep 19 2019, 08:20 AM
Post#8



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA


Thank you again for your help! I haven't even thought of some of this--so you've given me plenty to think about!

Right now, I do not want to affect inventory with this process, so I just want a list of all the materials needed to manufacture the items on the work order. In Access, I would insert the BOMID and Quantity into a temp table and use a recordset and run the procedure I listed above for each record, putting the returned values into another temp table and running a query to sum the common materials together for the report. In other words, if 3 items use the same screw, those screws would be totaled together.

So, what you are saying is that a CURSOR is like a recordset? I have read pros and cons of CURSORS, but so new to SQL Server, that I don't know where I fall. For the most part, work orders will have between 1 and 45 items that I would need a material list for. The actual average per work order is 3 items, so I'm thinking that the CURSOR wouldn't be a bad thing, but I've been wrong before!

I'm thinking that I need 2 temp tables--1 table to hold the BOMID and Quantity and another to insert all the materials per BOMID. Then sum those results to get my list. What I'm unclear on is how to get the materials for each BOMID in the 1st temp table.

Again, I appreciate your time and help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Sep 19 2019, 09:47 AM
Post#9



Posts: 3,330
Joined: 27-February 09



QUOTE
so I just want a list of all the materials needed to manufacture the items on the work order
<-- have to do that, or I go and answer the wrong question! LOL

You have your recursive CTE at the top of your query, and then after that, you can join that table result to whatever you want. So you'd join that to a Work Order, and that would return a list of parts you need, right? It would be something like

CODE
WITH cteBOM (<field list>)
AS
( SELECT...

)

SELECT <field list>
FROM cteBOM
INNER JOIN WorkOrderParts [censored] ON [censored].PartID = cteBOM.PartID
INNER JOIN WorkOrder wo ON [censored].WorkOrderID = [censored].WorkOrderID
WHERE wo.WorkOrderID = @WorkOrderID;


It's a lot easier to get your head around if you just remember that a CTE is just a funky kind of view. For the moment, forget all about what's going on inside the SELECT union SELECT stuff. Once you realize that, then you just join your "view" to other objects in your database - like the WorkOrderParts that lists the quantities of each completed product you want to assemble.
Go to the top of the page
 
LilAnnCC1
post Sep 19 2019, 11:43 AM
Post#10



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA


The CTE is per BOMID and I need to add more than 1 BOMID to it.

For each item in the work order, there is a corresponding BOM ID. So if there are 3 items on the work order, there will be 3 BOMIDs.

I need to run the CTE procedure 3 time--1 for each BOMID. This is where I don't know how to accomplish this in SQL. I know how to get this to work in Access, but I need to learn how to make it work in SQL server.




--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Sep 19 2019, 11:52 AM
Post#11



Posts: 3,330
Joined: 27-February 09



If your CTE is per bom_ID, then can't you just join to whatever contains the products you want built?

Isn't this conceptually the same as joining InvoiceHeader, InvoiceLineItems and then joining that to your CTE to get all the parts required to fill that invoice?

Unless the components of your assemblies change all the time, I would consider writing the exploded BOM to a persisted table, because it makes this query trivial after that. The only reason to keep it as code is because the contents of the BOM change all the time.
Go to the top of the page
 
MadPiet
post Sep 19 2019, 11:58 AM
Post#12



Posts: 3,330
Joined: 27-February 09



LOL... the "cleanliness" engine censored [censored] (WorkOrderParts), because it thought I meant "WithOut Papers"? Wow. Just wow.
Go to the top of the page
 
MadPiet
post Sep 19 2019, 12:04 PM
Post#13



Posts: 3,330
Joined: 27-February 09



Are the Bom ID's you need in a table or something? You can join other tables to the recursive CTE in the final query. That's what I was trying to get at. Ignore for a moment that your query is recursive. I don't think it matters here. How would you specify which 3 BOMs you need? From a WorkOrder? If you get the items to be assembled from the WorkOrder, can't you just join the "WorkOrderDetails" table to the BOM and get all the parts? Wouldn't that cause each BOM_id to be passed into the cte and return all the subassembly parts for each? Then you'd just have to write a totals query to get the count of each subassembly part in your final query, right?

This is using an expanded version of AdventureWorks2016...

CODE
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 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
WHERE dtl.SalesOrderID IN (43659, 43660, 43661, 43662)
ORDER BY
    dtl.SalesOrderID,
    dtl.ProductID,
    BOM.ComponentID;


I just used this
CODE
WHERE dtl.SalesOrderID IN (43659, 43660, 43661, 43662)


to get more than one bill of materials returned, but there's no reason you couldn't join to a table of Workorders instead. Right? The important part to remember is that the rCTE is a fancy view. So you can treat it like one - but only immediately after the declaration. I'd say you could write a bunch of workOrders IDs or EndProductIDs to a temporary table and then join that to your CTE, and it would expand those.

The big problem with using recursive CTEs is performance. They're slower than Christmas. It might be worthwhile to insert the CTE results into a permanent table - because then you can index it and all that. But that assumes that you're not switching out parts in your builds all the time.
This post has been edited by MadPiet: Sep 19 2019, 12:36 PM
Go to the top of the page
 
LilAnnCC1
post Sep 19 2019, 02:13 PM
Post#14



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA




CODE
    Are the Bom ID's you need in a table or something? You can join other tables to the recursive CTE in the final query. That's what I was trying to get at. Ignore for a moment that your query is recursive. I don't think it matters here. How would you specify which 3 BOMs you need? From a WorkOrder? If you get the items to be assembled from the WorkOrder, can't you just join the "WorkOrderDetails" table to the BOM and get all the parts? Wouldn't that cause each BOM_id to be passed into the cte and return all the subassembly parts for each? Then you'd just have to write a totals query to get the count of each subassembly part in your final query, right?


The 3 boms would come from the work order. So that is correct.

I can join the work order detail to the BOM table and from there to the BOM detail table--however, I have sub assemblies for some of the BOMs, which is what the recursive CTE is used for.

If I just join the work order detail to the BOM/Bom Detail tables and get the level 1 materials and then try to run the CTE for the subassembly parts, it would duplicate the level 1 parts, wouldn't it? I think it would.

Don't I still have the run the CTE procedure per each BOMID? (Which is what I don't know how to do in SQL Server).

I think we're almost on the same page.

1. Work order provides BOMID that are needed to get materials (ingredients) for my item. Create a temp table to hold BOMID and QTY?
2. Some loop or something needed here to run each BOMID in #1 to run the BOM (CTE) procedure.
3. Send results of CTE procedure to temp table.
4. Use temp table to calculate materials times work order QTY and sum common parts for a complete list of materials needed for all the items on the work order.


#2 is where I am lost.


CODE
The big problem with using recursive CTEs is performance. They're slower than Christmas. It might be worthwhile to insert the CTE results into a permanent table - because then you can index it and all that. But that assumes that you're not switching out parts in your builds all the time.


Would love to just create a view/list of every BOM, but there are only a few items that we produce on a regular basis. The majority of items are completely custom and maybe only produced during a certain time frame.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Sep 19 2019, 02:46 PM
Post#15



Posts: 3,330
Joined: 27-February 09



Do you have a copy of either the original AdventureWorks2016 or the extended version "AdventureWorks2016_EXT"? That's what I was using to answer your question. If your structure is similar to that, it should work. But I don't know what your structure looks like.

Wouldn't the recursive CTE basically "reduce" all the parts - subassemblies too - to their lowest level parts?

As for "running the CTE for each BOM ID" - no... if you join your list of BOM_IDs to the CTE in the final SQL statement (the one using the CTE, you can return the exploded parts lists for several BOMs at once. That's what my last query was doing.

I don't think you need to send anything to a temporary table... You could just output the list of parts and quantities (with the component qty multiplied by the "unit quantity" to get a final number). Then you could do whatever you wanted with that - like append it to some permanent table. Seems like you're misunderstanding what a recursive CTE is - it's more like a view than a stored procedure. So you can use it in joins etc, but only immediately after the CTE is defined. It's like a wonky dataset that only exists for the duration of the execution of the query.

Oh, right #2... getting the CTE to execute for each BOM at the same time. Maybe this will help ? I created a temporary table just to tweak the CTE so that it doesn't use a canned value list for a filter... so you could populate the temporary table with all the job orders you want to process, and then the code would spit out a final parts list (as yet incomplete... would need to create a totals query at the bottom).

So the cte joined to the InvoiceDetails table "expands" the BOMs for all products in the Invoice Details list. Then all you have to do as a last step in your query is to group by PartNumber and get the total quantity for each Part#. (so you need the assembly qty X component part qty to determine the total number of parts to order). … yeah, this is kinda mind-boggling!

Does that make sense?

CODE
use adventureworks2016_ext;
go
CREATE TABLE #InvoiceList (InvoiceID INT);
GO
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 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
ORDER BY
    dtl.SalesOrderID,
    dtl.ProductID,
    BOM.ComponentID;


You could wrap the last part of the CTE in a totals query...
CODE
SELECT ComponentID
            , SUM(QuantityNeeded) AS TotalToOrder
FROM (  SELECT dtl.SalesOrderID,
         dtl.ProductID,
         BOM.ComponentID,
         QuantityNeeded = dtl.OrderQty * BOM.PerAssemblyQty )
GROUP BY ComponentID;


Is that clearer now? Think of a CTE is a query with a bunch of intermediate steps that you can manipulate - like create several "views" inside the CTE and join them in the final statement.
This post has been edited by MadPiet: Sep 19 2019, 03:01 PM
Go to the top of the page
 
MadPiet
post Sep 19 2019, 04:52 PM
Post#16



Posts: 3,330
Joined: 27-February 09



Okay, I think this works... The table #InvoiceList is for the Invoices you need to generate the "shopping list" for.

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;


Basically, I'm generating bills of materials for all the items in the SalesOrderDetail table that are related to the invoices in the #InvoiceList table, and then I'm summarizing that at the very end of the CTE.
Go to the top of the page
 
MadPiet
post Sep 30 2019, 10:18 AM
Post#17



Posts: 3,330
Joined: 27-February 09



I found most of this on the MSFT website somewhere and tweaked it to work for me. Basically, it joins to the SalesOrderDetails (yours would be WorkOrderDetails) to get the parts list multiplied by the number of completed units to get the full shopping list (see the code below the CTE... the CTE just returns the parts for a single item).

CODE
ALTER PROCEDURE [dbo].[uspGetBillOfMaterialsForSalesOrder]
    @SalesOrderID INT,
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc]
                , [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel]
                , [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name]
            , b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE /* b.[ProductAssemblyID] = @StartProductID
            AND */ @CheckDate >= b.[StartDate]  
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID]
        , b.[ComponentDesc], SUM(b.[PerAssemblyQty] * sod.OrderQty) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    INNER JOIN Sales.SalesOrderDetail sod ON b.ProductAssemblyID = sod.ProductID
    WHERE sod.SalesOrderID = @SalesOrderID
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)
END;
Go to the top of the page
 
MadPiet
post Oct 3 2019, 01:46 PM
Post#18



Posts: 3,330
Joined: 27-February 09



If you wanted to append the results of the query to a permanent table, you would just change the last query that's referencing the CTE.

INSERT INTO ShoppingList (WorkOrderNo, PartNumber, Qty)
SELECT … FROM cteBOM INNER JOIN WorkOrderDetail wod on wod.ProductID = cteBOM.ProductID…
WHERE WorkOrderID = @WorkOrderID;

... and then you'd just reference the @WorkOrderNo that you're "expanding" and the (PartNumber, and SUM(Qty)) from your CTE result, and it would "generate" your Shopping List. Then you could just print that thing or do whatever - run a report in SSRS and e-mail it somewhere, for example.
Go to the top of the page
 
MadPiet
post Oct 14 2019, 10:06 PM
Post#19



Posts: 3,330
Joined: 27-February 09



Pretty sure this conversation is dead, but...

Any chance you have a couple of BOMs? The fun part is that the whole Assembly, Component, ProductID stuff is really confusing unless you're super clear on what it all means. (I think you need the BOM table that relates to that to make sense of it.)
Go to the top of the page
 
LilAnnCC1
post Oct 14 2019, 10:11 PM
Post#20



Posts: 832
Joined: 31-May 04
From: Wisconsin, USA


Not dead, MadPiet. Still working at it, but other events unrelated to database have been consuming my time. I will try to send you a couple of BOM's tomorrow.

Again, thank you for your time on this!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th October 2019 - 05:05 AM