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
BattleshipCobra
Contributor
0 Kudos

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