cancel
Showing results for 
Search instead for 
Did you mean: 

Need to Restrict Batch # field

Former Member
0 Kudos

Upon Goods Receipt - I need to restrict the Batch # field to XXXX-XXXX

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please check this thread

Thanks.

Former Member
0 Kudos

Thanks for the reply!

That does not help though.

I simply need to restrict the Batch Number entry(for new batches) to the following format:

####-####

Former Member
0 Kudos

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

Former Member
0 Kudos

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:

  1. There is a BatchNumber record which length is not 9
  2. There is a BatchNumber record which doesn't contains '-' or contains more than one '-'
  3. There is a BatchNumber record which '-' isn't positioned in the middle

Give it a try, I hope it's correct

Regards,

Wongso

Former Member
0 Kudos
Former Member
0 Kudos

Can someone point me in the RIGHT direction here??

Former Member
0 Kudos

Hi Daniel,

You marked my response as correct answer so I assume it worked, it doesn't? May you explain what is wrong with the answer?

Regards,

Wongso

Former Member
0 Kudos

Hi Wongso

Can you reference my screen shot above - I am having issues with the transaction fields...

Former Member
0 Kudos

Hi Daniel,

You should not create new stored procedure.

Find the SP name called SBO_SP_TransactionNotification and right click the SP then select modify, then paste your query there and press F5 button.

Former Member
0 Kudos

Hi Daniel,

As mentioned by Bharathiraja, you should modify SP_TransactionNotification. Here are the steps:

  1. Log in to your SQL Server Management Studio
  2. Expand your database - Programmability - Stored Procedure
  3. Find SP_TransactionNotification - right click - modify
  4. Paste the script right below "--ADD YOUR CODE HERE" section, and then press F5 on your keyboard. You should get "Command(s) completed successfully" message.

I hope that's clear enough.

Regards,

Wongso

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Wongso - SORRY SO - Works PERFECTLY!!

Thank you SO much

Former Member
0 Kudos

Hi Daniel,

You are welcome, finally it is done haha

Regards,

Wongso

Answers (0)