on 10-16-2015 4:13 PM
Upon Goods Receipt - I need to restrict the Batch # field to XXXX-XXXX
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this SP
IF (@OBJECT_TYPE ='20' AND (@TRANSACTION_TYPE IN('A','U')))
BEGIN
IF EXISTS(select * from pdn1 p1 inner join opdn p on p.DocEntry=p1.DocEntry inner join oibt b on b.BaseEntry=p1.DocEntry and b.BaseType=20
where (len(b.BatchNum)<>9 or right(LEFT(b.BatchNum ,5),1)<>'-')
and p1.DocEntry= @LIST_OF_COLS_VAL_TAB_DEL )
BEGIN
SELECT @error=100001, @error_message='Batch # field should be XXXX-XXXX'
END
END
Hi Daniel,
I believe the only way to restrict batch number format is through SP_TransactionNotification as mentioned by Nagarajan.
You can make it like this:
IF @transaction_type IN ('A') AND (@object_type = '59' )
BEGIN
IF exists (select 1 from OIBT where LEN(BatchNum) != '9' or LEN(BatchNum) - LEN(REPLACE(BatchNum, '-', '')) != 1 or CHARINDEX('-', BatchNum) != 5 and BaseType = '59' and BaseEntry = @list_of_cols_val_tab_del)
BEGIN
set @error = 1
set @error_message = 'Batch Format Must Be ####-####'
select @error, @error_message
END
END
What it does is every time you add a Goods Receipt (ObjType 59), it will look through Batch(OIBT) table where the records are created by your Goods Receipt. It will trigger error if:
Give it a try, I hope it's correct
Regards,
Wongso
Hi Daniel,
As mentioned by Bharathiraja, you should modify SP_TransactionNotification. Here are the steps:
I hope that's clear enough.
Regards,
Wongso
Hi Wongso,
Still does not work properly - If I put the correct format in the Batch Number(Create)
XXXX-XXXX I still get the error message - in other words I get the error message no matter what data is entered - correct or not
Also the batch selection is not OBTN?
THANKS SO MUCH FOR HELP ON THIS
Hi,
Try this SP
IF (@OBJECT_TYPE ='20' AND (@TRANSACTION_TYPE IN('A','U')))
BEGIN
IF EXISTS(select * from pdn1 p1 inner join opdn p on p.DocEntry=p1.DocEntry inner join oibt b on b.BaseEntry=p1.DocEntry and b.BaseType=20
where (len(b.BatchNum)<>9 or right(LEFT(b.BatchNum ,5),1)<>'-')
and p1.DocEntry= @LIST_OF_COLS_VAL_TAB_DEL )
BEGIN
SELECT @error=100001, @error_message='Batch # field should be XXXX-XXXX'
END
END
Hi Daniel,
I'm really sorry, there is a mistake on the query(on the if exists section), please use this one
IF @transaction_type IN ('A') AND (@object_type = '59' )
BEGIN
IF exists (select 1 from OIBT where (LEN(BatchNum) != '9' or LEN(BatchNum) - LEN(REPLACE(BatchNum, '-', '')) != 1 or CHARINDEX('-', BatchNum) != 5) and BaseType = '59' and BaseEntry = @list_of_cols_val_tab_del)
BEGIN
set @error = 1
set @error_message = 'Batch Format Must Be ####-####'
select @error, @error_message
END
END
I have tested it here and it works just fine
Regards,
Wongso
Wongso - THANK YOU SO MUCH!
However still the same problem - error even if it is entered correctly?
Here is the entire Transaction Notification:
USE [UDC_TEST]
GO
/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 11/04/2015 06:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SBO_SP_TransactionNotification]
@object_type nvarchar(20), -- SBO Object Type
@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)
AS
begin
-- Return values
declare @error int -- Result (0 for no error)
declare @error_message nvarchar (200) -- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
--------------------------------------------------------------------------------------------------------------------------------
IF @transaction_type IN ('A') AND (@object_type = '59' )
BEGIN
IF exists (select 1 from OIBT where LEN(BatchNum) != '9' or LEN(BatchNum) - LEN(REPLACE(BatchNum, '-', '')) != 1 or CHARINDEX('-', BatchNum) != 5 and BaseType = '59' and BaseEntry = @list_of_cols_val_tab_del)
BEGIN
set @error = 1
set @error_message = 'Batch Format Must Be ####-####'
select @error, @error_message
END
END
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message
end
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.