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

[Kiransonawane]

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

[narayanis]

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

[BattleshipCobra]

Hello,

I'm not sure what OBTW does but the joins and data aren't there.  I use this page for reference when working with batches and serials, it explains things very well:

https://www.linkedin.com/pulse/batch-numbers-writing-recall-report-sap-business-one-steven-lipton/

I rewrote your code to be more robust, although more complex:

-- BSC: Block transfers if originating warehouse batch expired

IF ( @object_type = '67' ) AND ( @transaction_type IN ('A', 'U') )
BEGIN

	-- Add all batch details from xfer to temp table
	SELECT
		 ROW_NUMBER() OVER(ORDER BY T2.[DistNumber]) AS 'RowNo'
		,T2.[ItemCode]
		,T2.[DistNumber] AS 'BatchNo'
		,T0.[LocCode] AS 'WhsCode'
		,ISNULL(T2.[ExpDate],GETDATE()) AS 'ExpDate'

	INTO #BatchTemp

	FROM 
		OITL T0 
		INNER JOIN ITL1 T1 ON T0.[LogEntry] = T1.[LogEntry] 
		INNER JOIN OBTN T2 ON T1.[ItemCode] = T2.[Itemcode] AND T1.[SysNumber] = T2.[SysNumber]

	WHERE 
		T0.[ManagedBy] = 10000044 
		AND T0.[ApplyType] = 67
		AND T0.[DocQty] < 0
		AND DATEDIFF(d,ISNULL(T2.[ExpDate],GETDATE()),GETDATE()) > 0
		AND T0.[DocEntry] = @List_of_cols_val_tab_del

	-- Create variables for evaluation and for message output
	DECLARE @ItemCode NVARCHAR(20)
	DECLARE @BatchNo NVARCHAR(36)
	DECLARE @WhsCode NVARCHAR(8)
	DECLARE @ExpDate DATETIME

	SELECT
		 @ItemCode = X.[ItemCode]
		,@BatchNo = X.[BatchNo]
		,@WhsCode = X.[WhsCode]
		,@ExpDate = X.[ExpDate]

	FROM #BatchTemp X

	WHERE X.[RowNo] = 1

	-- Clean up temp table not technically required in a storproc
	DROP TABLE #BatchTemp

	-- Evaluate if there is an expired batch and warn user
	IF ( DATEDIFF(d,ISNULL(@ExpDate,GETDATE()),GETDATE()) > 0 )
	BEGIN
		SET @error = -1
		SET @error_message = N'Error - Batch ' + @BatchNo + ' for item ' + @ItemCode + ' in warehouse ' + @WhsCode + ' has expired. Please choose a different batch.'
	END

END

This worked in my system, give it a try!

Mike