While Loop SAP B1 SQL Store Procedure for blocking over transfer

[Former Member]

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

[Johan_H]

Hi David,

I can'tsee much wrong with your query. All I saw was maybe a missing BEGIN END, and I moved the variable declaration:

declare @top as int = 1
declare @temp table (id int,quantity int NOT NULL, monum int NOT NULL)
IF @transaction_type IN ('A') AND @object_type = '67'
 BEGIN
  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
  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
     BEGIN
      set @top = @top + 1
     END
    END
 END

Regards,

Johan