u8:应付单据录入生成编号

用友U8在后台用存储过程导入应付单据录入。

创建几个存储过程进行处理单据编号的生成。

第一步：取出单据编号

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

use UFDATA_012_2014
go
if OBJECT_ID('p_get_number','P') >0 
   DROP PROCEDURE [dbo].[P_Get_Number]
GO
 
declare
@cardNum1 nvarchar(60) ,
@dDate1 datetime ,
@create_user_id1 nvarchar(40),
@number1 nvarchar(100),
@SQL NVARCHAR(2000),
@PARA NVARCHAR(500);
begin
  set @dDate1 = GETDATE();
  SET @SQL =N'[dbo].[P_Get_Number] ';
  --set @PARA = N'[dbo].[P_Get_Number]  @CardNum  Nvarchar(60), @ddate datetime, @create_user_id nvarchar(40), @number nvarchar(100) output';
  --exec SP_EXECUTESQL   @para, @CardNum1 =@CardNum, @ddate=@DDATE1 ,@create_user_id='demo',@number= @number1 output;
  EXEC [dbo].[P_Get_Number] 'P0',@dDate1,'dmeo',@number1 output;
  select @number1; 
end;
go
 
create PROCEDURE [dbo].[P_Get_Number]
@cardNum nvarchar(60) ,
@dDate datetime ,
@create_user_id nvarchar(40),
@number nvarchar(100)  output
as
begin
/*
* Author: samrv,
* Create Date: 2023-07-25
* 生成单据编号
*/
declare @user_id nvarchar(40)
declare @number1 nvarchar(100)
declare @cContent nvarchar(50);
set @user_id = (select cCode from VoucherContrapose a  left join   UserHrPersonContro b  on cPsn_Num = @create_user_id where a.cSeed = b.cUser_Id)
 
SELECT  @number1 =
    case
     
     
    when  Prefix1Len=4 AND Prefix1Rule ='年'  then  CONVERT(varchar(4), @dDate, 112)
     
    when  Prefix1Len=4 AND Prefix1Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
     
    when   Prefix1Len=6 AND Prefix1Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
     
    when   Prefix1Len=6 AND Prefix1Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
     
    when   Prefix1Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)
         
    else ''
    end,
    @cContent= Glide 
from  VoucherNumber where CardNumber = @cardNum;
 
declare @number2 nvarchar(100)
 
SELECT  @number2 =
    case
     
     
    when  Prefix2Len=4 AND Prefix2Rule ='年' then  CONVERT(varchar(4), @dDate, 112)
     
    when Prefix2Len=4 AND Prefix2Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
     
    when  Prefix2Len=6 AND Prefix2Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
     
    when  Prefix2Len=6 AND Prefix2Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
     
    when  Prefix2Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)
 
    else ''
    end,
    @cContent= Glide 
     
from  VoucherNumber where CardNumber = @cardNum
 
declare @number3 nvarchar(100)
 
SELECT  @number3 =
    case
     
     
    when  Prefix3Len=4 AND Prefix3Rule ='年' then  CONVERT(varchar(4), @dDate, 112)
     
    when  Prefix3Len=4 AND Prefix3Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
     
    when  Prefix3Len=6 AND Prefix3Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
     
    when  Prefix3Len=6 AND Prefix3Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
     
    when  Prefix3Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)
     
 
    else ''
    end ,
    @cContent= Glide 
      
from  VoucherNumber where CardNumber = @cardNum
 
    declare @num int
    declare @len int
    declare @glide nvarchar(100)
    declare @cSeed nvarchar(100)
    declare @maxn int
 
    set @glide= (select GlideRule from VoucherNumber where CardNumber = @cardNum) 
    set @len = (Select GlideLen From VoucherNumber Where CardNumber=@cardNum ) 
    if(@glide is not null and @glide <>'')
    begin
        if(@glide = '年')
            set @cSeed = (CONVERT(varchar(4), @dDate, 112))
        if(@glide = '月')    
            set @cSeed = (CONVERT(varchar(6), @dDate, 112))
        if(@glide = '日' and @len <=6)
            set @cSeed = substring(CONVERT(varchar(8), @dDate, 112),3,6)  
        if(@glide = '日' and @len >6)
            set @cSeed = CONVERT(varchar(8), @dDate, 112) 
        select @maxn=max(cNumber) from VoucherHistory with (NOLOCK) Where  CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide and cSeed=@cSeed
        if(@maxn is not null)
            set @num= 1+ @maxn
        else
        begin
            set @num=1
        end
        exec P_InsetVoucheHistory @cardNum,@glide,@cSeed,@cContent
    end
    else
        set @num = 1+ (select cNumber as Maxnumber From VoucherHistory  with (NOLOCK) Where  CardNumber=@cardNum and cContent is NULL)
 
    SET @number = @number1+@number2+@number3+dbo.fn_FillNumberWithZero(@num,@len)
 
end

第二步：取出单据编号后，回写单据编号的最大值。

-- 生成录入日期 的单据编号 
create  PROCEDURE [dbo].[P_InsetVoucheHistory]    
@cardNum nvarchar(60),        
@glide nvarchar(100), 
@cSeed nvarchar(100),
@cContent nvarchar(100)         
AS 
/*
* Author: samrv,
* Create Date: 2023-07-25
* 生成单据编号
*/
if not exists (select * from VoucherHistory where CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide)
begin  
   insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)
   values (@cardNum,@cContent,@glide,@cSeed,0,0)
end
else
BEGIN
    update VoucherHistory set cNumber=cNumber+1 where CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide and cSeed=@cSeed
END