SP_Transaction_Notification to block sales orders from being added or updated if they don't have a batch allocated to an item

[Former Member]

Hi,

I need help with a SP_Transaction_Notification to block sales orders from being added or updated if they don't have a batch allocated to an item on the sales order. Below is what I have so far:

/* Error 1600001: Block Sales Orders that do not have Inventory Allocation */ 
If @object_type = '17' AND @transaction_type IN ('A', 'U')
Begin    
If (SELECT
T2.DistNumber
FROM  dbo.OBTN T2            
INNER JOIN dbo.ITL1 T1 ON T2.SysNumber = T1.SysNumber AND T2.Itemcode = T2.ItemCode           
INNER JOIN dbo.OITL T3 ON T1.LogEntry = T3.LogEntry AND T2.ItemCode = T3.ItemCode           
RIGHT JOIN dbo.RDR1 T4 ON T2.ItemCode = T4.ItemCode AND T3.ApplyEntry = T4.DocEntry           
RIGHT JOIN dbo.ORDR T5 ON T4.DocEntry = T5.DocEntry
WHERE    T4.DocEntry = @list_of_cols_val_tab_del and T5.Series = 502)  IS NULL   
Begin        
SET @error = 1600001        
SET @error_message = '****You have to Select a Batch Num for every Item****'    
End  End  /* Fin Error 1600001 */ 

[Former Member · Accepted solution]

If @object_type = '17' AND @transaction_type IN ('A', 'U') 
Begin     
DECLARE @L AS INT
DECLARE @B AS INT
DECLARE @D as Int
set @D = (SELECT Docentry from ordr where Series = 502 and DocEntry = @list_of_cols_val_tab_del)
SET @L = (select Count(*)  FROM RDR1 T1 INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode AND ManBtchNum = 'Y' and t1.DocEntry = @D)
set @B = (SELECT COUNT(*) FROM IBT1 where baseentry = @D and BaseType = '17')

IF @B < @L
Begin         
SET @error = 1600001         
SET @error_message = '****You have to Select a Batch Num for every Item****'     
End  End  /* Fin Error 1600001 */ 

[Former Member · Accepted solution]

HI;

I crotch the same thing, but i want specify which item has a probleme in the error message, how can we proceed?

Thanks.

[Former Member · Accepted solution]

Hi Ali,

This works for all items in the order. If one or all don't have a batch allocated it will not add or update.

[Former Member]

I found how to attribute the request result to a variable, but the variable don't reset for a new process, it get the first result and it was repeated  in the next operations.

Now, my first question is how can i reset the variable for a new execution of the script.

The second is how can i integrate the variable into the output message to the user, with another texte.

This is my new request:

LANGUAGE SQLSCRIPT

AS

-- Return values

---------------------------------------------------------------------------------

error  int;                       -- Result (0 for no error)

error_message nvarchar (200);           -- Error string to be displayed

cnt int;

cnt2 int;

article nvarchar (200);

begin

error := 0;

error_message := N'Ok';

--------------------------------------------------------------------------------------------------------------------------------

if :object_type='15' and (:transaction_type='A' or :transaction_type='U') then

--Scenario#1: Case whene Quantity value = 0 --CardName is blank

    SELECT TOP 1 "Dscription" INTO article from (SELECT T0."DocEntry",T0."Dscription"

  FROM DLN1 T0 

  WHERE T0."Quantity"=0 or T0."Quantity" is null )

  WHERE "DocEntry" = :list_of_cols_val_tab_del;

  if article is not null then

            error := -111;

            error_message := 'Message to the user'+article;

    end if;

end if;

   -- Select the return values

select :error, :error_message FROM dummy;

end;

Thank you for your answers.

[Former Member]

Hi michael;

Thank you for your anwer, but i need to specify the first bloked item which is selected in the request.

This is my request:

begin

error := 0;

error_message := N'Ok';

--------------------------------------------------------------------------------------------------------------------------------

if :object_type='15' and (:transaction_type='A' or :transaction_type='U') then

--Scenario#1: Case whene Quantity value = 0 --CardName is blank

    select count(*) into cnt from ( SELECT T0."DocEntry", T0."LineNum"

  FROM DLN1 T0 

  WHERE T0."Quantity"=0 or T0."Quantity" is null)

  WHERE "DocEntry" = :list_of_cols_val_tab_del;

      if :cnt>0 then

            error := -111;

            error_message := 'Veuillez saisir une quantité supérieure à 0';

      end if ;

end;

Now, i need to defined an ather variable which will got the first item which it selected in the request, but i don't know how i do it.

Thank you.

[former_member370721]

What exactly is the question ?

[Former Member]

Hi Franz Leu,

The above SP is incorrect if you test it in SP_Transaction_Notification . I need a SP to block sales orders from being created if an item does not have a batch (Not allocated to inventory).

[former_member370721]

Of course, there must be more in your SP.

Please post the complete SP.

[Former Member]

Hi Franz Leu,

Please see  http://scn.sap.com/people/lisa.mulchinock/blog/2009/05/22/the-sptransactionnotification-stored-proce....

That is all the rest is standard SAP SP

[former_member370721]

OK. If you do return with @error <> 0, then the sales order should not be added/created and instead the message (as in your screen-shot) is posted.

Are you saying that in your case it is still posted/added?

[former_member370721]

Try to set a smaller value instead of 1600001.

@error is declared as int, which is max 65535 - (edit: forget that, in SQL int is 4byte).

Message was edited by: Franz Leu

[Former Member]

Yes, it is still posted/added without any batch allocated to the item.