CREATE PROCEDURE dbo.st_intelink_temp_getinvno20070809 /*得到帐单号码*/
@Clno varchar(20),
@Way bit,
@Mani_id varchar(30) output,
@Comnetwork varchar(5)=''
as
declare @i int
declare @IndexLength int /*流水号长度*/
declare @Index int /*序号*/
declare @Month int
declare @Mani_ID_Temp varchar(30)
set @IndexLength=5
if @Comnetwork=''
begin
set @Comnetwork='hh'
end
/*中间增加一个特殊苻号'Z'*/
/*帐单号码组成=方向(1位)+网络代号(2位)+'Z'+客户代号(4-6位)+流水号(5位)*/
set @Mani_ID=''
set @Mani_ID_Temp=''
if @way=1
begin
set @Mani_ID=@Mani_ID+'1'
end else begin
set @Mani_ID=@Mani_ID+'0'
end
set @Mani_ID=@Mani_ID+@Comnetwork+'z'+@Clno
set @Month=month(getdate())
if @Month<10
begin
set @Mani_ID=@Mani_ID+'0'+convert(varchar(2),@Month)
end else begin
set @Mani_ID=@Mani_ID+convert(varchar(2),@Month)
end
if upper(@Comnetwork)='CD'
begin
set @Mani_ID_Temp=(select max(mani_id) from ar_ap1 where mani_id like @Mani_ID+'%')
end else begin
set @Mani_ID_Temp=(select max(mani_id) from ar_ap where mani_id like @Mani_ID+'%')
end
/*得到序号*/
if @Mani_ID_Temp is null
begin
set @Index=1
end else begin
set @Index=convert(int,right(@Mani_ID_Temp,@IndexLength))+1
end
set @i=len(convert(varchar(10),@Index))
while @i<@IndexLength
begin
set @Mani_ID=@Mani_ID+'0'
set @i=@i+1
end
set @Mani_ID=@Mani_ID+convert(varchar(10),@Index)
GO