Skip to Content
0
Former Member
Nov 24, 2011 at 04:09 PM

Block Duplicate Serial Number on a Stock Goods Receipt

645 Views

Hi,

Can anyone please help me with my stored procedure I created. Basically I want the system to block the user from capturing a serial no. that has all ready exists in the database regardless if it is unavailable or available. Please see below what I have got so far but the system is not blocking me :(. Am I even using the correct object id?

IF @transaction_type IN ('A','U') AND (@object_type = '94' ) -- For Goods Receipts

BEGIN

set @item = LEFT( @list_of_cols_val_tab_del, CHARINDEX(CHAR(9), @list_of_cols_val_tab_del,1) - 1)

set @SysSer = ltrim(rtrim(substring(@list_of_cols_val_tab_del, len(@item)+2, 30)))

select @IntSer = intrserial from osri where itemcode = @item and convert(varchar,sysserial) = @SysSer

SELECT @count = COUNT() FROM OSRI WHERE INTRSERIAL = @IntSer*

IF @count > 1

BEGIN

set @error = 1

set @error_message = N'Duplicate S/N was found in the system for Item No.' +@item +' S/N : ' + @IntSer

END

END

Any help will be greatly appreciated

Regards,

Ryan

Edited by: Ryan Todd on Nov 24, 2011 5:10 PM