Get allocated serial number only

[Former Member]

I have a UDF serialno in Sales Order and a Stored Procedure that will  save selected serials. But It still saves not selected serial.How to know if serial is alread allocated in Sales Order? Also anyone where is SBDR table? When I look at the system information of the rows from selection its from SBDR, but there is no table when I look at the SQL.

This is my Stored Procedure.

declare @rowcount int

declare @rowcount1 int

declare @mycount as int

declare @mystring nvarchar(255)

declare @myitem  nvarchar(10)

declare @myserial nvarchar(100)

declare @mybaseentry as nvarchar(10)

declare @mylinenum as int

declare @gdocentry as nvarchar(255)

declare @gitemcode as nvarchar(255)

declare @glinenum as int

IF @object_type='17' AND (@transaction_type='A' or @transaction_type='U')

BEGIN

--for U_serial==============================================================================================================================================

  set @rowcount=(select count(*) from syscolumns a,sysobjects b where a.name='u_serialno' and b.name='rdr1' and a.id=b.id)

  if @rowcount>0 and (@transaction_type='A' or @transaction_type='U')

  begin

  set @gdocentry=''

  set @gitemcode=''

  set @glinenum=''

  --update RDR1 set U_serialno='' where docentry=@gdocentry and itemcode=@gitemcode and linenum=@glinenum

  declare serial_no cursor for SELECT A.DOCENTRY, A.ITEMCODE, A.LINENUM FROM RDR1 A, OITM B WHERE A.DOCENTRY=@list_of_cols_val_tab_del AND A.ITEMCODE=B.ITEMCODE AND B.MANSERNUM='Y'

  open serial_no

  fetch next from serial_no into @gdocentry,@gitemcode,@glinenum

  while @@fetch_status=0 begin

  set @myserial=''

  select @myserial=@myserial + cast(intrserial as varchar(255)) + ', ' from sri1 a, osri b where a.itemcode=b.itemcode and a.itemcode=@gitemcode and a.baseentry=@gdocentry and a.baselinnum=@glinenum and a.sysserial=b.sysserial  Group by cast(intrserial as varchar(255))

  set @myserial=case when @myserial='' then '' else left(@myserial,len(@myserial)-1) end

  update rdr1 set u_serialno=@myserial where docentry=@gdocentry and itemcode=@gitemcode and linenum=@glinenum

  fetch next from serial_no into @gdocentry,@gitemcode,@glinenum

  end

  Close serial_no

  deallocate serial_no

  end

END

[Former Member]

Hi Raphael,

You can click CTRL+TAB on quantity field in Sales Order to allocate serial/batch numbers.

Have you tried this option?

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi,

1. How to know if serial is already allocated in Sales Order?

Right click in sales order and then choose Batch number transaction report-->check direction field

2. Also anyone where is SBDR table?

RSRN, RBTN, RITL or SBDR are virtual tables that do not store any information. They are used to connect the GUI (Graphical User Interface) with other tables so that the report columns on the GUI display the correct information.

Thanks & Regards,

Nagarajan

[Former Member]

Hi, thanks for the reply. How can I update the allocated serials for Sales Order using stored procedure?For example I add two serials for item one:serial1 and serial2 and saved it on my RDR1 U_serialno.The output data on my UDF U_serialno would be serial1,serial2.  Next I want to update the sales order and remove serial2 when I update my Sales Order the U_serialno should be serial1 only but It still saves serial1,serial2 . This is my stored procedure:

declare @rowcount int

declare @rowcount1 int

declare @mycount as int

declare @mystring nvarchar(255)

declare @myitem  nvarchar(10)

declare @myserial nvarchar(100)

declare @mybaseentry as nvarchar(10)

declare @mylinenum as int

declare @gdocentry as nvarchar(255)

declare @gitemcode as nvarchar(255)

declare @glinenum as int

IF @object_type='17' AND (@transaction_type='A' or @transaction_type='U')

BEGIN

--for U_serial==============================================================================================================================================

  set @rowcount=(select count(*) from syscolumns a,sysobjects b where a.name='u_serialno' and b.name='rdr1' and a.id=b.id)

  if @rowcount>0 and (@transaction_type='A' or @transaction_type='U')

  begin

  set @gdocentry=''

  set @gitemcode=''

  set @glinenum=''

  update RDR1 set u_serialno='' where docentry=@gdocentry and itemcode=@gitemcode and linenum=@glinenum

  declare serial_no cursor for SELECT A.DOCENTRY, A.ITEMCODE, A.LINENUM FROM RDR1 A, OITM B WHERE A.DOCENTRY=@list_of_cols_val_tab_del AND A.ITEMCODE=B.ITEMCODE AND B.MANSERNUM='Y'

  open serial_no

  fetch next from serial_no into @gdocentry,@gitemcode,@glinenum

  while @@fetch_status=0 begin

  set @myserial=''

  select @myserial=@myserial + cast(intrserial as varchar(255)) + ', ' from sri1 a, osri b where a.itemcode=b.itemcode and a.itemcode=@gitemcode and a.baseentry=@gdocentry and a.baselinnum=@glinenum and a.sysserial=b.sysserial Group by cast(intrserial as varchar(255))

  set @myserial=case when @myserial='' then '' else left(@myserial,len(@myserial)-1) end

  --Select @Error=55,@error_message=@myserial

  update rdr1 set u_serialno=@myserial where docentry=@gdocentry and itemcode=@gitemcode and linenum=@glinenum

  fetch next from serial_no into @gdocentry,@gitemcode,@glinenum

  end

  Close serial_no

  deallocate serial_no

  end

END

Thanks,

Raphael

[kothandaraman_nagarajan]

Hi,

Your main intention is to capture allocated serial no?

Thanks & Regards,

Nagarajan

[Former Member]

Yes, How to capture it?

Thanks,

Raphael