ERP系统各种单据流水号的产生方案

        ERP系统单据流水号是整个业务的一个重要凭证，并且都是唯一的。这些单据流水号可能包含的信息有日期信息、操作员信息等，而且每个客户都有自己的单据编码规范。本篇就介绍如何来产生这类单据流水号。主要应用的还是字符串的拼接技术。

1、简单的单据号产生办法：  
     declare @ord_id varchar(20)
       select  @ord_id=max(ord_id) from Toc_AdviseOrder
       if @ord_id is null
         set @ord_id='DJH10000001'
       else
          set @ord_id='DJH'+convert(varchar(8),right(10000000+convert(int,substring(@ord_id,5,11))+1,8))

2、具有商业价值的的解决方案：
例如：
create proc [dbo].CreateSequenceNumber
@custId   varchar(20) ,                  --客户编号
@typeFlag char(3),                        --业务类型
@outputId       varchar(30) output  --输出单据号
as
declare @l int,
        @Y char(2),
        @OutPutId varchar(20), --输出客户编号
        @CustIdFormat varchar(20)
select top 1 @l=len(custId) from customers where custUp='0'
select @Y=Y from Years
if @typeFlag='1'  --订货单
begin
     if @custId='0'
        begin
          select @OutPutId=max(ord_id) from [order] where len(cst_id)=@l and substring(ord_id,1,6)='DHDA'+@Y and len(ord_id)=15
          if @OutPutId is null
             set @OutPutId='DHDA'+@Y+'000000001'
          else
             set @OutPutId='DHDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(ord_id) from [order] inner join Customers on [order].cst_id=Customers.custId
     where [email=[ft=,2,]custup=@custId]custup=@custId[/email] and len(ord_id)=15 and substring(ord_id,1,3) = 'DHD'
   if @OutPutId is null
      set @OutPutId='DHD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='DHD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end      
end
if @typeFlag='2' --发货单
begin
     if @custId='0'
        begin
          select @OutPutId=max(sal_id) from sales where len(cust_id)=@l and substring(sal_id,1,6)='FHDA'+@Y and len(sal_id)=15
          if @OutPutId is null
             set @OutPutId='FHDA'+@Y+'000000001'
          else
             set @OutPutId='FHDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(sal_id) from sales inner join Customers on sales.cust_id=Customers.custId
     where [email=[ft=,2,]custup=@custId]custup=@custId[/email] and len(sal_id)=15 and substring(sal_id,1,3) = 'FHD'
   if @OutPutId is null
      set @OutPutId='FHD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='FHD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end
if @typeFlag='3' --退货单
begin
     if @custId='0'
        begin
          select @OutPutId=max(back_id) from Backs where len(cust_id)=@l and substring(back_id,1,6)='THDA'+@Y and len(back_id)=15
          if @OutPutId is null
             set @OutPutId='THDA'+@Y+'000000001'
          else
             set @OutPutId='THDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(back_id) from Backs inner join Customers on Backs.cust_id=Customers.custId
     where [email=[ft=,2,]custup=@custId]custup=@custId[/email] and len(back_id)=15 and substring(back_id,1,3) = 'THD'
   if @OutPutId is null
      set @OutPutId='THD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='THD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end
if @typeFlag='4' --入库单
begin
     if @custId='0'
        begin
          select @OutPutId=max(rkd_id) from Rukudan inner join StorageType
             on Rukudan.Storagetype=StorageType.id and custId='0'
               where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and substring(rkd_id,1,6)='RKDA'+@Y and len(rkd_id)=15
          if @OutPutId is null
             set @OutPutId='RKDA'+@Y+'000000001'
          else
             set @OutPutId='RKDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(rkd_id) from Rukudan
                inner join StorageType on StorageType.id=Rukudan.StorageType
        where [email=[ft=,2,]CustId=@custId]CustId=@custId[/email] and len(rkd_id)=15 and substring(rkd_id,1,3) = 'RKD'
   if @OutPutId is null
      set @OutPutId='RKD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='RKD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end

if @typeFlag='6' --移库单
begin
     if @custId='0'
        begin
          select @OutPutId=max(StockMoveID) from StockMove inner join StorageType
             on StockMove.StockOut=StorageType.id and custId='0'
               where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and substring(StockMoveID,1,6)='ZKDA'+@Y  
                and len(StockMoveID)=15
          if @OutPutId is null
             set @OutPutId='ZKDA'+@Y+'000000001'
          else
             set @OutPutId='ZKDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(StockMoveID) from StockMove inner join StorageType
                  on StockMove.StockOut=StorageType.id or stockMove.StockIn=StorageType.id
     where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and len(StockMoveID)=15 and substring(StockMoveID,1,3) = 'ZKD'
   if @OutPutId is null
      set @OutPutId='ZKD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='ZKD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end
if @typeFlag='7' --调货单
begin

     set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
      select @OutPutId=max(ChId) from ChProduct inner join Customers
         on ChProduct.OutCustId=Customers.custId  
           where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and substring(ChId,1,3) = 'CHD' and len(ChId)=15
      if @OutPutId is null
         set @OutPutId='CHD'+@Y+@CustIdFormat+'00001'
      else
         set @OutPutId='CHD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))    
end
if @typeFlag='8' --盘点单
begin
     if @custId='0'
        begin
          select @OutPutId=max(pandianId) from  PanDian inner join StorageType
             on PanDian.StorageId=StorageType.id and custId='0'
               where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and substring(pandianId,1,6)='PDDA'+@Y and len(pandianId)=15
          if @OutPutId is null
             set @OutPutId='PDDA'+@Y+'000000001'
          else
             set @OutPutId='PDDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(pandianId) from PanDian
                inner join StorageType on StorageType.id=PanDian.StorageId
        where [email=[ft=,2,]CustId=@custId]CustId=@custId[/email] and len(pandianId)=15 and substring(pandianId,1,3) = 'PDD'
   if @OutPutId is null
      set @OutPutId='PDD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='PDD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end
if @typeFlag='9' --盈亏单
begin
     if @custId='0'
        begin
          select @OutPutId=max(yk_id) from  YingKui inner join StorageType
             on YingKui.storageid=StorageType.id and custId='0'
               where [email=[ft=,2,]custId=@custId]custId=@custId[/email] and substring(yk_id,1,6)='YKDA'+@Y and len(yk_id)=15
          if @OutPutId is null
             set @OutPutId='YKDA'+@Y+'000000001'
          else
             set @OutPutId='YKDA'+@Y+convert(varchar(9),right(1000000000+convert(int,substring(@OutputId,7,9))+1,9))
        end
     else  --分销
       begin
          set @CustIdFormat = (select StringFormat from PosFormat where CustId = @custId)
   select @OutPutId=max(yk_id) from YingKui
                inner join StorageType on StorageType.id=YingKui.storageid
        where [email=[ft=,2,]CustId=@custId]CustId=@custId[/email] and len(yk_id)=15 and substring(yk_id,1,3) = 'YKD'
   if @OutPutId is null
      set @OutPutId='YKD'+@Y+@CustIdFormat+'00001'
   else
    set @OutPutId='YKD'+@Y+@CustIdFormat+convert(varchar(5),right(100000+convert(int,substring(@OutPutId,11,5))+1,5))
       end  
end
SET @outputId= @OutPutId