cancel
Showing results for 
Search instead for 
Did you mean: 

Prevent an inventory transfer from expired batch in sap business One Transaction notification

Kiransonawane
Explorer
0 Kudos

I want to Prevent an Inventory Transfer from expired batch in sap business One Transaction notification. What am I doing wrong?

IF LTRIM(RTRIM(@object_type)) = '67' AND (@transaction_type in( 'A','U'))

BEGIN

declare @item as nvarchar(20)

declare @batch as nvarchar(32)

declare @whs as nvarchar(8)

declare @expDate as datetime

SELECT @item=OBTN.ItemCode, @batch=OBTN.DistNumber, @whs=OBTW.WhsCode, @expDate=OBTN.ExpDate

FROM WTR1 INNER JOIN OBTW ON OBTW.WhsCode = WTR1.WhsCode and WTR1.ItemCode = OBTW.ItemCode and

WTR1.DocEntry = CAST(@list_of_cols_val_tab_del AS INT)

INNER JOIN OBTN ON OBTW.ItemCode = OBTN.ItemCode

 

WHERE OBTN.Status = '0' AND WTR1.LineStatus='O'

IF (LTRIM(RTRIM(@item)) <> '' AND LTRIM(RTRIM(@batch)) <> '' AND @expDate < GetDate())

BEGIN

SELECT @error = -1

SELECT @error_message = N'Error - Batch ' + @batch + ' for item ' + @item + ' in warehouse ' + @whs + ' has expired. Please choose a different batch.'

END

END

View Entire Topic
narayanis
Active Contributor
0 Kudos

Hi,

If you are entering the expiry date in batch management window then this can be achieved. Try below query in transaction notification

 

SELECT T1."BaseNum", T1."BaseType", T1."DocDate", T0."ItemCode", T1."ItemName", T0."DistNumber", T0."ExpDate" FROM OBTN T0 , IBT1 T1 WHERE T0."ExpDate" < Current_Date And T1."BaseType" = 67 And T1."BaseEntry" = :list_of_cols_val_tab_del;

Hope this helps