Skip to Content
0
Former Member
Nov 29, 2016 at 07:01 AM

While Loop SAP B1 SQL Store Procedure for blocking over transfer

321 Views

I have an issue with my stored procedure SAP B1

What I'm trying to do here is storing the sum of the quantity, group by the manufacturing order and insert it into the temp table. Then use a while loop to go thru each ID to compare with the user table [@FGTRACKING] and block if temp.quantity > sum(quantity) in [@FGTracking].

However this is not working, the transaction still passed the stored procedure block. Need help please, i suspect there is something wrong with my syntax.

IF @transaction_type IN ('A') AND @object_type = '67'
BEGIN
declare @top as int
declare @temp table (id int,quantity int NOT NULL, monum int NOT NULL)
insert into @temp (id, quantity,monum)
select row_number() over (order by (select NULL)), sum(quantity) as quantity, u_shipment_line as monum
from wtr1 t1
where t1.docentry = @list_of_cols_val_tab_del
group by u_shipment_line
set @top = 1
WHILE @top <= (select count(monum) from @temp)
BEGIN
IF EXISTS (select t100.monum from @temp t100 
where t100.quantity > (select sum(t111.u_transfer) 
from [@FGTRACKING] t111 where t111.u_mo_num = t100.monum 
group by t111.u_mo_num) and t100.id = @top)
BEGIN
SELECT @Error = 666, @error_message = 'Over-transfer'
END
ELSE
set @top = @top + 1
END
END