General Discussion Topics

[tmcclure]

Hello!

From time to time our warehouse has the wrong quantity pending showing for an item. This is caused by shipments being generated that are discarded without being used, and without being deleted. Items still attached to these non-committed shipments can mess up the pending count, obviously.

To determine which shipments these items are "hung up" in, we run this:

select l.*, td.*

from timInvtTranLog l  (nolock)

join timInvtTranDist td (nolock) on td.InvtTranKey = l.InvtTranKey

where l.TranStatus = 2   -- pending

and td.WhseKey = 22     -- replace <enter> with the appropriate Warehouse Key

and td.ItemKey = (SELECT itemkey from timItem where itemID ='8510-12')

which gives a list of shipments. We take each shipment individually and run this:

Begin Tran

Update timInvtTranLog 

Set TranStatus = 5

      Where InvtTranKey = 4127077

 with each InvtTranKey.

Then obviously we

Commit Tran

 for each.

Then we run recalculate qty on SO script

INSERT timQtyOnOrderVariance (CalcType, ItemKey, WhseKey)
SELECT 2, Item.ItemKey, Inven.WhseKey
FROM timItem Item (NOLOCK)
JOIN timInventory Inven (NOLOCK)
ON Item.ItemKey = Inven.ItemKey
WHERE CompanyID = 'company name whatever'

 to recalculate, then

DECLARE @RetVal INTEGER
EXEC spimRecalcInvenQtyOn @RetVal OUTPUT, 1
IF @RetVal <> 1
PRINT 'spimRecalcInvenQtyOn failed during QtyOn execution.'

 to verify.



My question is this: Can a query be written that combines these separate processes into one script where I can plug in the InvtTranKeys (or even just the item #s?) and have the script change TranStatus on each, commit each, recalculate and verify without the user having to execute each individual process manually?

Doing it this way works fine, obv, but I'm always looking for ways to boost efficiency.

[LouDavis]

You could try combining them into a stored procedure and execute the stored procedure through a script and pass it the data you want to process.

[tmcclure]

Honestly it's the

Begin Tran

Update timInvtTranLog 

Set TranStatus = 5

      Where InvtTranKey = 4127077

 that takes forever. Having to do each InvtTranKey one at a time (or BETWEEN if they are in series) then Commit Tran on each.

I was just looking for a way to input all the InvtTranKey items in a big list, have it run and Commit each without me having to do it all manually each time.

[LouDavis · Accepted solution]

Within your stored procedure, use a table variable to hold all your information and use this to update the timInvtTranLog

For example

Update timInvtTranLog

Set TranStatus = 5

FROM timInvtTranLog a

INNER JOIN @MyTableVariable b

     ON a.InvtTranKey = b.InvTranKey