on 04-13-2009 12:52 PM
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.