Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

sp_transactionnotification for serial number duplication

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

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question