Transaction Notification to Prevent Duplicate Barcodes

[Former Member]

Hi,

I please need a transaction notification to prevent users from using the same bar code on more than one item i.e. barcode xyz may only appear once in the codebars field in oitm.

Thanks

Jacques

[former_member204969 · Accepted solution]

I assume you want to allow the empty bar code field.

Then nsert this in your SBO_SP_TransactionNotification stored procedure after the line

-- ADD YOUR CODE HERE

If @object_type = '4' and @transaction_type in ( 'A','U')
BEGIN
declare @B nvarchar(16)
set @B=(Select isnull(I.CodeBars,'') From OITM I
    Where I.ItemCode=@list_of_cols_val_tab_del)
If @B!='' and exists
 (Select I.CodeBars From OITM I
    Where I.CodeBars=@B and I.ItemCode!=@list_of_cols_val_tab_del)
Select @error =12, @error_message = 'Duplicate bar code !!'
END

[Former Member · Accepted solution]

Thanks for reply.

Unfortunately it did not function as expected as I am still able to add and update Items in the system with duplicate barcodes (CodeBars).

Thanks

Jacques

[former_member204969 · Accepted solution]

It works well for me.May be you inserted the code in an unexecuted place in the procedure. Show here the full transaction notification procedure we can examine.

[Former Member · Accepted solution]

Hi,

Ended up using this below as I got a (220001) error with your code.

Thanks for you assistance, though.

-- Prevent Duplicate Bar Codes

If @object_type = '4' and @transaction_type in ( 'A','U')

begin

if exists ( select a.ItemCode from oitm a inner join OITM b on a.CODEBARS

= b.CODEBARS

and a.ItemCode <> b.ItemCode

where @object_type = '4' and

a.ItemCode = @list_of_cols_val_tab_del AND a.CodeBars

= b.CodeBars and a.ItemCode!=b.ItemCode)

begin

set @error = 9

set @error_message = 'BK-Duplicate Barcode.'

end

end

[former_member983106]

This post is prior to Sap Business One introducing Barcode per UoM enhancement, when Barcodes where stored at the OITM Table, now there is a OBCD Table where Barcodes are stored per Item and unit o measure, this query modification worked for me, hope anyone also finds it useful

If @object_type = '1470000062' and @transaction_type in ( 'A','U')

BEGIN

declare @B nvarchar(16) set @B=(Select isnull(T1.BcdCode,'') From OBCD T1

Where T1.BcdEntry=@list_of_cols_val_tab_del)

If @B!='' and exists (Select T1.BcdCode From OBCD T1 Where T1.BcdCode=@B

and T1.BcdEntry!=@list_of_cols_val_tab_del)

Select @error =12, @error_message = 'Barcode already exists!'

END