H漫国产剧情在线,久久久久影院美女国产主播,亚洲人成777在线播放网站,久久性爱影院五月天,亚洲精品乱码久久久久久,国产白丝JK制服在线视频,老司机精品视频在线,亚洲精品嫩草研究院久,MIYA午夜色大片在线观看免费,国产精华AV午夜在线观看

ERP系統(tǒng)各種單據(jù)流水號的產(chǎn)生方案

Ccoffee

2011年6月10日 15:43 本文熱度 2901

ERP系統(tǒng)單據(jù)流水號是整個業(yè)務(wù)的一個重要憑證，并且都是唯一的。這些單據(jù)流水號可能包含的信息有日期信息、操作員信息等，而且每個客戶都有自己的單據(jù)編碼規(guī)范。本篇就介紹如何來產(chǎn)生這類單據(jù)流水號。主要應(yīng)用的還是字符串的拼接技術(shù)。

1、簡單的單據(jù)號產(chǎn)生辦法：
     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、具有商業(yè)價值的的解決方案：
例如：
create proc [dbo].CreateSequenceNumber
@custId   varchar(20) ,                  --客戶編號
@typeFlag char(3),                        --業(yè)務(wù)類型
@outputId       varchar(30) output  --輸出單據(jù)號
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' --發(fā)貨單
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' --調(diào)貨單
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

該文章在 2011/6/10 15:43:30 編輯過

關(guān)鍵字查詢

相關(guān)文章

正在查詢...

點晴ERP是一款針對中小制造業(yè)的專業(yè)生產(chǎn)管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內(nèi)大量中小企業(yè)的青睞。

點晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運作、調(diào)度、堆場、車隊、財務(wù)費用、相關(guān)報表等業(yè)務(wù)管理，結(jié)合碼頭的業(yè)務(wù)特點，圍繞調(diào)度、堆場作業(yè)而開發(fā)的。集技術(shù)的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。

點晴WMS倉儲管理系統(tǒng)提供了貨物產(chǎn)品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質(zhì)期管理,貨位管理,庫位管理,生產(chǎn)管理,WMS管理系統(tǒng),標簽打印,條形碼,二維碼管理,批號管理軟件。

點晴免費OA是一款軟件和通用服務(wù)都免費，不限功能、不限時間、不限用戶的免費OA協(xié)同辦公管理系統(tǒng)。