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>