0
I have a bank where the stock(onhand) must be changed each time an order(docentry) is made, whether it is a confirmed order or not, for that I need to download the order quantity(Qty) with a simple subtraction operation(onhand - Qty). My question is the following, how do I carry out the stock replacement of each item every time an order is made? I can do this but only when I specify the order number but I would like to perform this operation. Follow code and result image.
Basically how it would work: the item’s Stock is at 3993, after the order "15335" the stock should upgrade to 3981 for all orders where this item exists(in case the 15681 should show in onhand 3981), then the cursor should update again when arriving at the item "AR000001" and decrease 4 of the updated stock 3981. Currently what happens: the stock drops from number 3993 for both where it is 3981 for one and 3989 for another.
--realizando inserção na tabela temporária
insert into #tmpPedidos(Docentry,qty,onhand,conta,itemcode)
(select a.docentry,b.Quantity,c.OnHand,(c.OnHand - b.Quantity),b.ItemCode
from ORDR a
inner join RDR1 b on a.DocEntry = b.DocEntry
inner join OITW c on b.ItemCode = c.ItemCode
where
a.DocStatus = 'o' and a.CANCELED = 'n' and c.whscode =
CASE when BPLId = 4 then '301'
ELSE
'401'
END) --documentos com status abertos não cancelados e com estoque de numero ou 301 ou 401
declare crs cursor
for
select * from #tmpPedidos order by itemcode
update #tmpPedidos set onhand = conta where itemcode = 'ar000001' and Docentry = 15335 --aqui realizo o update indicando um pedido(que funciona)
update #tmpPedidos set conta = onhand - qty where itemcode = 'ar000001' and Docentry = 15335 -- aqui atualizo a conta indicando o pedido(que também funciona)
open crs
fetch next from crs;
I thank all those who can help, I’m starting in this part of temporary tables and cursors so I don’t know much about the use of them.
Thanks a lot, Murilo! I will test everything right and return you, just one question: the updates I do inside the cursor to be able to replace the data of onhand as the requests are passing?
– Douglas Souza
@Douglassouza, in fact you would replace your entire cursor with the updates I went through, you fix your Sert by removing the
(c.OnHand - b.Quantity)
, so that he does not fill the fieldconta
and after you entered the data into the table#tmpPedidos
you use the updates I have informed you. The updates will go through all rows of the table calculating the column valueconta
as requested. As it is a temporary table it should not be keeping too much data, so there will be no performance problem.– Murilo Portugal
Good morning Murilo! I saw that the account works perfectly but I have 1 problem. onhand and account are based on the entire stock of all items, in case I would just like to download the stock of each item. For example: the item AR000001 has 3993 of stock, after the account would be 3981 the item AR000002 has 200 of stock, if within the order where the ar000001 was sold did not exist the 02, the stock of the 02 would remain the same, but when executing your code all items are being changed.
– Douglas Souza