Skip to Content
0
Former Member
Nov 16, 2008 at 02:07 PM

SBO_SP_TransactionNotification an ATP (Available To Promise)

144 Views

Dear all,

I would like a sales order to block when the available to promise is less or equal to the row quantity requested, per warehouse. (where RDR1.Quantity <= OITW.Onhand-OITW.IsCommited).

The below query returns a correct result (1 or 0) when executed in MSSQL 2005 but not in SAP B1 2007A PL 42. The vATP table is a view I created calculating ATP <OITW.Onhand-OITW.IsCommited> as I first thought that the calculation inside the procedure was the problem. You are welcome to link it back to OITW if possible.

Note that the database is set to block negative stock and manager stock per WHS.

Please somebody can tell me how stupid I am 😉 and show me the correct code?

Kind regards,

Frederic

<IF @transaction_type IN ('A', 'U') AND @Object_type = '17'

BEGIN

IF EXISTS (SELECT T0.ItemCode FROM RDR1 T0

INNER JOIN vATP T1 ON T0.ItemCode = T1.ItemCode

AND T0.WhsCode = T1.WhsCode

AND CAST(T0.DocEntry AS NVARCHAR(255)) = @list_of_cols_val_tab_del

WHERE T0.Quantity > T1.ATP)

BEGIN

set @error = 85001

set @error_message = 'There is not enough stock in the specified warehouse'

END

END>

I used the query below to double check the result in MSSQL 2005 directly and make sure my result shows 1 or 0.

<

DECLARE @ERROR int

SET @ERROR = 0

IF EXISTS

(SELECT T0.ItemCode FROM RDR1 T0

INNER JOIN vATP T1 ON T0.ItemCode = T1.ItemCode

AND T0.WhsCode = T1.WhsCode

AND CAST(T0.DocEntry AS NVARCHAR(255)) = 12

WHERE T0.Quantity > T1.ATP)

BEGIN SET @ERROR=1

END

SELECT @ERROR

SELECT T0.ItemCode,T0.WhsCode, T0.Quantity, T1.ItemCode,T1.WhsCode, T1.ATP FROM RDR1 T0

INNER JOIN vATP T1 ON T0.ItemCode = T1.ItemCode

AND T0.WhsCode = T1.WhsCode

AND CAST(T0.DocEntry AS NVARCHAR(255)) = 12>