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

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

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

第一步:取出单据编号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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

  

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 生成录入日期 的单据编号
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

 第三步: 从自定义的接口表导入应付单据录入

这部分代码 ,有需要的观众留言获取。

posted @   samrv  阅读(322)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
历史上的今天:
2022-07-25 Oracle存储过程中cursor + with用法
点击右上角即可分享
微信分享提示