Skip to Content
avatar image
Former Member

While Loop SAP B1 SQL Store Procedure for blocking over transfer

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 29, 2016 at 08:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded