cancel
Showing results for 
Search instead for 
Did you mean: 

sp_transactionnotification for serial number duplication

Former Member
0 Kudos

Dear All,

I am using following procedure for fetching the current Internal serial number (Intrserial field in OSRI) table while doing any serial number transaction. it returns empty string. I want to block any duplication in Internal serial number column.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(25), 				-- 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'
declare @val nvarchar (1200) 		-- Error string to be displayed
declare @chrin nvarchar(30)
declare @item nvarchar(455)
declare @SysSer nvarchar(455)
declare @qry nvarchar(255)
declare @IntSer nvarchar(32)
declare @whs nvarchar(8)
declare @count int
declare @base nvarchar(6)
set @count = 0
--------------------------------------------------------------------------------------------------------------------------------

--	ADD	YOUR	CODE	HERE



IF @transaction_type IN ('A','U') AND (@object_type = '94' )
BEGIN


set @item = LEFT( @list_of_cols_val_tab_del, CHARINDEX(CHAR(9),  @list_of_cols_val_tab_del,1) - 1)
set @SysSer = substring(@list_of_cols_val_tab_del, len(@item)+1, 30)

--set @qry = 'select intrserial from osri where itemcode =' + @item + ' and convert(varchar,sysserial) = convert(varchar,' + @SysSer + ')'

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

set @error = 1
select @error, @error_message


END
--------------------------------------------------------------------------------------------------------------------------------
select @error, @error_message


END

it gives me correct current itemcode(@item) and sysserial(@SysSer) from this query but not the internal serial number. please guide me in doing this.

Thanks,

Binita

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

Do you have Serial Number on Every Transaction / Release Only?

Have you tried manually checked what values your system serial number and itemcode variables are getting when the SP executes

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

Former Member
0 Kudos

thanks for replying Suda,

it got resolved by truncating one character from the serial number that I was getting. actually a tab was included in the key which was not getting removed by trimming in SQL.

regards,

Binita

Former Member
0 Kudos

Hi Binita

Please assist me with , I need the sp you used to solve this issue.

Regards

Bongani Dlamini

Answers (0)