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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Stored Procedure For Bulk Update, Any Versions    
 
   
LilAnnCC1
post Aug 28 2019, 02:02 PM
Post#1



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


Hello all! I am using SQL Server 2017 with Access 2016 Front End

I wrote a stored procedure today that actually works! It is a complicated procedure for processing Work Orders. A work order can have many different fixtures on it with different quantities, and this procedure allows the user to process only those fixtures he/she wants to process.

In this procedure, I am running another procedure that will get a list of materials needed to produce the fixture which uses a recursive cte. I'm inserting those records into another table and allowing the user to proceed to another fixture or work order.

Now I need another procedure to process the entire work order instead of doing it fixture by fixture. But I'm stuck at how to set up this procedure to get the Materials for each fixture if the user wants to process the entire work order for the quantities in the work order detail table.

So, I'm asking if any of you know how I can turn this stored procedure into a bulk procedure.

Here is my procedure:

CODE
ALTER Procedure [dbo].[spWOProcess]
  @WOID int
, @QTY int
, @Date DateTime
, @BOM int
, @Fixture int
AS
SET NOCOUNT ON;
BEGIN
    Begin Try
        Begin Transaction
Declare @newID int

INSERT INTO tWOFixture (WOID, JobID, BOMID, Fixture, fQTY, DateCompleted)
SELECT d.WOID, w.JobID, d.BOMID, d.PartID, @QTY as CompleteQTY, @Date as DateCompleted
FROM tWODetail d
INNER JOIN tWO w on d.WOID=w.ID
WHERE d.WOID=@WOID and d.PartID=@Fixture

Set @newID=(SELECT ID FROM tWOFixture WHERE ID= SCOPE_IDENTITY());

--Update Status on detail table

IF (SELECT Remaining FROM vwWOFixtureBalance WHERE WOID=@WOID and PartID=@Fixture)=0
    BEGIN
        Update tWODetail
        Set StatusID=4
        FROM tWODetail
        WHERE WOID=@WOID and PartID=@Fixture
    END

--Get material list
--Call spWOMaterialList
    exec spWOMaterialList @WOID, @BOM, @QTY


--Insert above records into tWOMaterial
INSERT INTO tWOMaterialUsed (WOFID, WOID, BOMID, subBOMID, Material, Used)
SELECT @newID as WOFID, @WOID, n.BOMID, Null, n.Material, n.Needed
FROM tWOMaterialNeeded n
WHERE n.WOID=@WOID


--Close WO if possible

IF (SELECT Sum(Remaining) FROM vwWOFixtureBalance WHERE WOID=@WOID)=0
    BEGIN
        Update tWO
        Set StatusID=4,
        Closed=GETDATE(),
        ClosedBy=(substring(suser_sname(),(8),len(suser_sname())))
        FROM tWO
        WHERE ID=@WOID
    END

        Commit Transaction
    End Try

    Begin Catch
        Rollback Transaction            
    End Catch
End
GO



--------------------
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 Aug 28 2019, 03:29 PM
Post#2



Posts: 3,361
Joined: 27-February 09



How many tables are involved? I'm just wondering if there's an easy way to write a single query to get the complete Bill of Materials for all the Fixtures in a Work Order. Do you have some CREATE TABLE scripts?
Go to the top of the page
 
LilAnnCC1
post Aug 28 2019, 04:08 PM
Post#3



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


I can certainly create some scripts for you if you'd like.

The BOM table has the BOMID and FIXTURE that would match the Work Order detail table BOMID and FIXTURE, but they have subBOM's or assemblies, which is why I use a recursive CTE--to get the Bill of Materials. I wish I could just use a single query!

The current procedure is affecting these tables:
These 2 tables generate the shop work
tWO (Work Orders)
tWODetail (Work Order Detail)

These 2 tables are done when the shop work is completed
tWOFixture (This table is basically the finished goods table. When work order is completed, these fixtures go into inventory to sell.)
tWOMaterialUsed (inserting the tWOMaterialNeeded into this table. Can't go directly into this table because the Quantity of the work order detail has to be multiplied by the BOM quantity)

Used only for getting the Bill of Materials for each fixture
tBOMDetail (Bill of Material Details (Parts, quantities, etc.)


Extra unwanted but needed table (because of stored procedure):
tWOMaterialNeeded (only needed for the actual list of materials results from CTE in my stored procedure above to insert into the tWOMaterialUsed table).

Let me know if you want me to generate the scripts for these tables.

--------------------
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 Aug 28 2019, 04:26 PM
Post#4



Posts: 3,361
Joined: 27-February 09



Sure, post the scripts... Might help to get toward the bottom of this. You might want to post this on SQL Server Central too.
Go to the top of the page
 
LilAnnCC1
post Aug 28 2019, 04:34 PM
Post#5



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


I think I might have figure it out.

This is what I have so far and it appears to have worked for 9 fixtures as far as getting the BOM for all of them and putting them into the tWOMaterialNeeded table.

CODE
--Close Work Order in full

--Get the work order details


DECLARE @IDWO int
DECLARE @WO int=4263

--Create a temp table to hold the work order details

SELECT d.ID, d.WOID, d.BOMID, d.PartID, r.Remaining    
INTO #TEMP
FROM tWODetail d
INNER JOIN vwWOFixtureBalance r on d.WOID=r.WOID and d.PartID=r.PartID and r.Remaining >0
WHERE d.StatusID Not IN(4,34) and d.WOID=@WO
ORDER BY ID

While (SELECT Count(*) from #TEMP)>0
BEGIN
    SELECT TOP 1 @IDWO=ID FROM #TEMP
    --Get the Material List for all fixtures on the work order
        --CTE for Material List
        ;WITH cteMaterial
                AS
                (
                SELECT @WO as WOID, d.BOMID, d.SubBOMID, d.PartID, d.bomQTY*#TEMP.Remaining as NEEDED
                FROM tBOMDetail d
                INNER JOIN #TEMP on d.BOMID=#TEMP.BOMID
                WHERE d.BOMID=#TEMP.BOMID

                UNION ALL

                SELECT @WO, d.BOMID, d.SubBOMID, d.PartID, c.NEEDED*d.bomQTY
                FROM tBOMDetail d
                INNER JOIN cteMaterial c on d.BOMID=c.SubBOMID
                WHERE d.BOMID=c.SubBOMID
                )
                            
                INSERT INTO tWOMaterialNeeded (WOID, BOMID, Material, Needed)
                SELECT WOID, BOMID, PartID, NEEDED
                FROM cteMaterial c
                WHERE c.SubBOMID is null



                DELETE #TEMP WHERE ID=@IDWO
        END


Can you see any glaring errors with 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
 
LilAnnCC1
post Aug 28 2019, 04:49 PM
Post#6



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


Okay, I don't have it yet. I had 9 fixtures left on a work order to close. So it inserted each of the materials needed into the tWOMaterialsNeeded table 9 times. Can you see why it is doing 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
 
MadPiet
post Aug 28 2019, 04:52 PM
Post#7



Posts: 3,361
Joined: 27-February 09



Sounds like a missing join, because that's acting exactly like a cartesian product...

SELECT a.Column1, b.Column2
FROM a CROSS JOIN b

will return the count of records in A times the count of records in B.
Go to the top of the page
 
LilAnnCC1
post Aug 28 2019, 05:06 PM
Post#8



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


I believe you're right.

I'm not sure how to join it exactly.

Where I am creating the Temp table:
CODE
DECLARE @IDWO int
DECLARE @WO int=4263

--Create a temp table to hold the work order details
SELECT d.ID, d.WOID, d.BOMID, d.PartID, r.Remaining    
INTO #TEMP
FROM tWODetail d
INNER JOIN vwWOFixtureBalance r on d.WOID=r.WOID and d.PartID=r.PartID and r.Remaining >0
WHERE d.StatusID Not IN(4,34) and d.WOID=@WO
ORDER BY ID


The PartID is the actual Fixture on the work order that needs to be produced.

But in the rest of the code, the PartID is the actual Material used to produce the fixture.

CODE
While (SELECT Count(*) from #TEMP)>0
BEGIN
    SELECT TOP 1 @IDWO=ID FROM #TEMP
    --Get the Material List for all fixtures on the work order
        --CTE for Material List
        ;WITH cteMaterial
                AS
                (
                SELECT @WO as WOID, d.BOMID, d.SubBOMID, d.PartID, d.bomQTY * #TEMP.Remaining as NEEDED
                FROM tBOMDetail d
                INNER JOIN #TEMP on d.BOMID=#TEMP.BOMID
                WHERE d.BOMID=#TEMP.BOMID

                UNION ALL

                SELECT @WO, d.BOMID, d.SubBOMID, d.PartID, c.NEEDED * d.bomQTY
                FROM tBOMDetail d
                INNER JOIN cteMaterial c on d.BOMID=c.SubBOMID
                WHERE d.BOMID=c.SubBOMID
                )
                            
                INSERT INTO tWOMaterialNeeded (WOID, BOMID, Material, Needed)
                SELECT WOID, BOMID, PartID, NEEDED
                FROM cteMaterial c
                WHERE c.SubBOMID is null



I think I need to add another table to get the correct join. Then I think this will work. Thank you for sticking with me!

--------------------
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 Aug 28 2019, 05:08 PM
Post#9



Posts: 3,361
Joined: 27-February 09



So you're basically getting the components for each part/subassembly one at a time? yeah, ouch.

maybe check this conversation out on SQL Server Central:
https://www.sqlservercentral.com/forums/top...otal-inside-cte

It's Jeff Moden stuff, and he knows what he's talking about.
Go to the top of the page
 
LilAnnCC1
post Aug 28 2019, 05:13 PM
Post#10



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


So, I am added the tWODetail ID into the temp table and tried changing the cte to include that as a join, but the insert didn't work.

CODE
;WITH cteMaterial
                AS
                (
                SELECT @WO as WOID, d.BOMID, d.SubBOMID, d.PartID, d.bomQTY * #TEMP.Remaining as NEEDED
                FROM tBOMDetail d
                INNER JOIN #TEMP on d.BOMID=#TEMP.BOMID
                WHERE d.BOMID=#TEMP.BOMID and d.ID=#TEMP.ID    --------------------------------------------------------------------------------------Added ID, but insert below doesn't insert any records.

                UNION ALL

                SELECT @WO, d.BOMID, d.SubBOMID, d.PartID, c.NEEDED * d.bomQTY
                FROM tBOMDetail d
                INNER JOIN cteMaterial c on d.BOMID=c.SubBOMID
                WHERE d.BOMID=c.SubBOMID
                )
                            
                INSERT INTO tWOMaterialNeeded (WOID, BOMID, Material, Needed)
                SELECT WOID, BOMID, PartID, NEEDED
                FROM cteMaterial c

--------------------
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 Aug 29 2019, 08:20 PM
Post#11



Posts: 3,361
Joined: 27-February 09



You might find this article helpful:
https://www.essentialsql.com/recursive-ctes-explained/

You can use CROSS APPLY to expand/explode your assemblies into a list of parts and quantities.
CODE
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 *
FROM Sales.SalesOrderDetail sod
CROSS APPLY (
SELECT   ProductID,
         Name,
         Color,
         Quantity,
         ProductLevel,
         ProductAssemblyID,
         Sort
FROM     cte_BOM) ca;


if you insert all of those parts, you only run the CTE stuff once per a set of invoices instead of once per assembly. You could still insert the results into a temporary or permanent table... you'd just change the SELECT *… CROSS APPLY to INSERT INTO...SELECT.. CROSS APPLY.
Go to the top of the page
 
MadPiet
post Sep 20 2019, 10:18 PM
Post#12



Posts: 3,361
Joined: 27-February 09



I think I answered this elsewhere, so I'll make this one real short.

if you think of the CTE like a view (just returns a bunch of records), then in the last query in the CTE, you can do whatever you want with the results, including inserting them into another table. The top/recursive part of the query builds the result, and then under that (where I have a big ugly select query), you could basically wrap that in something like

CODE
INSERT INTO #MyTempTable (fieldlist)
SELECT fieldList FROM RecurThroughBOM; <-- the CTE "table/view"


This is the original code:

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 also use an INSERT statement that dumps the result of
    your CTE into another table if you wanted...
    just
    INSERT INTO DestinationTable(field1, field2...)
    SELECT field1, field2...
    FROM cteName
*/
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;
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 12:42 AM