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 |
第三步: 从自定义的接口表导入应付单据录入
这部分代码 ,有需要的观众留言获取。
优质生活从拆开始
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2022-07-25 Oracle存储过程中cursor + with用法