I want to block inventory transfer document creation if the warehouse location name in the inventory transfer document is not as same as default warehouse location set in the OUDG table using SP_Transaction Notification (SP_TN) as follows:
IF @transaction_type in ('A', 'U') AND @object_type = '67'
if exists (SELECT T1.DocNum, T1.U_RefNo FROM [dbo].[OWHS]
T0 INNER JOIN OWTR T1 ON T0.WhsCode = T1.Filler
INNER JOIN OUDG T2 ON T0.WhsCode = T2.Warehouse
AND t1.Docentry = @list_of_cols_val_tab_del
WHERE T1.U_RefNo = t0.location)
SELECT @Error = 1, @error_message =
'Location name is not match with default'
in OWTR table I have created a UDF named U_RefNo and using a query FMS, it will display location code.
The purpose of the query is to block all warehouse users in creating a inventory transfer document if the owtr.filler they filled is not match with their default warehouse location. Since in our business run, there are 6 locations and 9 warehouses within each location, we must block warehouse users using other warehouses that are not in their location.
Please give advice. I appreciate it a lot