首页 > 其他分享 >U8:用友U8导入应付单录入、应收单录入存储过程

U8:用友U8导入应付单录入、应收单录入存储过程

时间：2023-08-03 22:48:10浏览次数：75  
标签：U8 dbo Ap -- nvarchar 用友 录入 id row

 

用友U8导入应收单录入、应付单录入的存储过程，以下SQL脚本整合前几天写的《u8:应付单据录入生成编号 》会更好。

--  应收单据\应付单据都可以用此程序,但需要注意一下两者编号长度不同. 
DECLARE 
   c_iface cursor  for 
    select row_id, cstatus,cBatchNum from u8api.dbo.ap_vouch_iface avi
    where avi.cstatus is null 
     order by row_id ; 
declare 
   @user_name nvarchar(30) = 'demo', -- 用户名,如: DEMO
   @cLink nvarchar(20), -- 应付票据主键(AP主键): P0 + 年月(4位)+ 3位流水号 
   @cVouchID nvarchar(20), 
   @auto_ID BIGINT,
   
   -- begin: voucherHistory表
   @AutoId int,  -- 自动生成 
   @CardNumber nvarchar(20),
   @iRDFlagSeed int,
   @cContent nvarchar(50),
   @cContentRule nvarchar(50),
   @cSeed nvarchar(120),
   @cNumber nvarchar(30),
   @bEmpty bit,
   -- end: voucherHistory表
   @cDeptCode nvarchar(50), -- 部门代码 
   @cDeptName nvarchar(150),  -- 部门名称 
   @row_id int,
   @cStatus nvarchar(20), -- 状态: 空,S,E,F.
   
   @fetch_status int;
declare 
   @cBatchNum  nvarchar(30),  -- 导入批次号
   @cPsn_Num   nvarchar(30),  -- 工号  
   @cPsn_Name  nvarchar(50),  -- 员工姓名 
   @cDwCode    nvarchar(30),  -- 供应商代码
   @cDwName    nvarchar(250), -- 供应商名称 
   @cPayCode   nvarchar(20), -- 付款条件代码 
   @cPayName   nvarchar(150); -- 付款条件名称
BEGIN

   open  c_iface  ;
   fetch next from c_iface into @row_id,@cStatus,@cBatchNum ;
   set @fetch_status = @@FETCH_STATUS 
   while @fetch_status =0 
   begin  
   
  -- 第一步: 1.获取 AP主键
  /*
    declare csr_voucherHistory cursor for      
   SELECT TOP 9 autoId,CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty
    FROM UFDATA_012_2014.dbo.[VoucherHistory] h --记录数: 13605
    where 1=1  --and cSeed like '2023%'
     --and cNumber = '1' 
    and CardNumber like 'P0'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112) ;
      
   open csr_voucherHistory 
   fetch next from csr_voucherHistory into @autoId,@CardNumber,@iRdFlagSeed,@cContent,
          @cContentRule, @cSeed,@cNumber,@bEmpty
   while @@fetch_status  = 0 
   begin
      set @cLink = @CardNumber+ @cSeed +  right('00000000'+@cNumber ,3);
     fetch next from csr_voucherHistory into @autoId,@CardNumber,@iRdFlagSeed,@cContent,
          @cContentRule, @cSeed,@cNumber,@bEmpty
   end; 
   close  csr_voucherHistory;
   deallocate csr_voucherHistory     
   PRINT @cLink; 
   */
   -- 单据编号: AP: P02307029 ; AR: R023070001
   if  exists(SELECT autoId,CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty
    FROM UFDATA_012_2014.dbo.[VoucherHistory] h --记录数: 13605
    where 1=1  --and cSeed like '2023%'
     --and cNumber = '1' 
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112) )
    BEGIN
       update h
        set cNumber = CAST(cNumber AS INT) +1
      from UFDATA_012_2014.dbo.[VoucherHistory] h   
    where 1=1  
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112);
    
    select @cLink = CardNumber+ RIGHT(cSeed,4) +  right('0000000'+ cNumber ,3)  ,
          @cVouchID = RIGHT(cSeed,4) +  right('0000000'+ cNumber ,3)       
      FROM UFDATA_012_2014.dbo.[VoucherHistory] h 
    where 1=1  
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112);
    END  
    else
    BEGIN
       insert into  UFDATA_012_2014.dbo.[VoucherHistory](CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty) 
        values(N'P0',NULL,N'单据日期',N'月',CONVERT(nvarchar(6), getdate(), 112),1,0 )  ;
        
        SET @cLink = N'P0' + CONVERT(nvarchar(4), getdate(), 12) + right('0000001' ,3);
        set @cVouchID = CONVERT(nvarchar(4), getdate(), 12) + right('0000001' ,3) ;
    END;
    PRINT @cLink;  
    
   -- 第一步: 2. 获取自动流水号 AUTO_ID
    SELECT  @auto_ID = max(AUTO_ID)+1       
      FROM UFDATA_012_2014.DBO.Ap_Vouch 
      where cLink like 'P0'+ CONVERT(nvarchar(4),GETDATE(), 12)+'%'
      and cPZNum is null;
      
   
   -- 第二步: 验证数据
   
	   -- 1.部门代码验证
	  --	set @cDeptName = '电线一部';
	  select @cDeptName = cDeptName from U8API.dbo.Ap_Vouch_Iface avi
	  where row_id = @row_id; 
	  
		if exists (SELECT TOP 1 cDepCode,cDepName
		 FROM ufData_012_2014.dbo.[Department] dp
		where dp.cDepName = @cDeptName ) 
		begin 
		SELECT TOP 1 @cDeptCode= cDepCode
		 FROM ufData_012_2014.dbo.[Department] dp
		where dp.cDepName = @cDeptName ;
		end
	   else
	   begin
		  set @cDeptCode = null; 
	   end;
	   
	   if @cDeptCode is not null
	   begin  
       update avi
         set  cDeptCode= @cDeptCode ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
       end   
       else
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20001;' , 
              cMsg = coalesce(cMsg,'') +'部门名:'+ @cDeptName+ ',无法匹配部门代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
       end;
      -- 2.1  业务员验证
      -- 人员表
      if exists ( select cPersonCode from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id and cPersonCode is Not null ) 
        begin
      SELECT top 1 @cPsn_Num =
          cPsn_num 
       FROM ufData_012_2014.dbo.[hr_hi_person] emp
       where emp.cPsn_Num = ( select cPersonCode from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id and cPersonCode is Not null ) ;
     if @cPsn_Num is null 
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20002;' , 
              cMsg = coalesce(cMsg,'') + '工号:'+ @cPsn_Num+ ',无法匹配工号.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
       end;
      end ; 
       
     -- 2.2  业务员验证
      -- 人员表
      if exists (select cPersonName from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id  and cPersonName is Not null ) 
        begin
     SELECT top 1 @cPsn_Name =
          cPsn_Name , @cPsn_Num = cPsn_Num
       FROM ufData_012_2014.dbo.[hr_hi_person] emp
       where emp.cPsn_Name = (select cPersonName from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id  and cPersonName is Not null ) ;
     if @cPsn_Name is null 
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20003;' , 
              cMsg = coalesce(cMsg,'') + '姓名:'+ @cPsn_Name+ ',无法匹配工号.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
       end;  
     else
     begin
        update avi
          set avi.cPersonCode = @cPsn_Num,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ;
        
     end;
     end;
     -- 3. 供应商名称验证
      select @cDwName = v.cDwName  from U8API.dbo.Ap_Vouch_Iface v 
      where row_id = @row_id
      ;
      if isnull(@cDwName,'')!='' 
      select top 1 @cDwCode = pv.cVenCode from UFDATA_012_2014.dbo.Vendor  pv
      where pv.cVenName = @cDwName;
      
      if ISNULL(@cDwCode ,'') != ''
      begin
         update avi
          set avi.cDwCode = @cDwCode,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ;        
       end;  
     else
     begin      
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20004;' , 
              cMsg = coalesce(cMsg,'') + '供应商名称:'+ @cDwName+ ',无法匹配供应商代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
     end;
       
     --4. 付款条件 验证
     select @cPayName = v.cPayName  from U8API.dbo.Ap_Vouch_Iface v 
      where row_id = @row_id ;
      
     select top 1 @cPayCode  = cPayCode 
     from UFDATA_012_2014.dbo.PayCondition 
     where cPayName = @cPayName;
      
     if isnull(@cPayCode ,'') != '' 
     begin
		update avi
          set avi.cPayCode = @cPayCode,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ; 
     end
     else
     begin
		update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20005;' , 
              cMsg = coalesce(cMsg,'') + '付款条件名称:'+ @cDwName+ ',无法匹配付款条件代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi 
         where avi.row_id = @row_id; 
     end;
     
       
    
  -- SELECT @cBatchNum = BatchNum from u8api.dbo.Ap_Vouch_Iface where row_id= @row_id; 
   
   -- 第三步: 1.导入应付录入表头
   insert into UFDATA_012_2014.dbo.ap_vouch(cLink,
				cVouchType,
				cVouchID,
				cVouchID1,
				dVouchDate,
				cDwCode,
				cDeptCode,
				cPerson,
				cItem_Class,
				cItemCode,
				cDigest,
				cCode,
				cexch_name,
				iExchRate,
				bd_c,
				iAmount,
				iAmount_f,
				iRAmount,
				iRAmount_f,
				cPayCode,
				cOperator,
				cCheckMan,
				cCoVouchType,
				cDestNo,
				cSrcNo,
				bStartFlag,
				cPZid,
				cFlag,
				cDefine1,
				cDefine2,
				cDefine3,
				cDefine4,
				cDefine5,
				cDefine6,
				cDefine7,
				cDefine8,
				cDefine9,
				cDefine10,
				iAmount_s,
				iRAmount_s,
				VT_ID,
				--Ufts,
				iClosesID,
				iCoClosesID,
				cDefine11,
				cDefine12,
				cDefine13,
				cDefine14,
				cDefine15,
				cDefine16,
				cItemName,
				cGatheringPlan,
				dCreditStart,
				iCreditPeriod,
				dGatheringDate,
				dcreatesystime,
				dverifysystime,
				dmodifysystime,
				cmodifier,
				dmoddate,
				dverifydate,
				Auto_ID,
				cPZNum,
				doutbilldate,
				iPrintCount,
				cPluginsourcetype,
				iPluginsourceautoid,
				cPluginsourceautoid,
				iBusType,
				cagentcuscode,
				cOrderNo,
				cContractType,
				iSource,
				cContractID,
				csysbarcode,
				iDiscountTaxType,
				iTaxRate)
     SELECT @cLink,
			cVouchType,
			@cVouchID,
			cVouchID1,
			convert(date,getdate(),112) as dVouchDate,
			cDwCode,
			cDeptCode,
			cPerson,
			cItem_Class,
			cItemCode,
			cDigest,
			cCode,
			cexch_name,
			iExchRate,
			bd_c,
			iAmount,
			iAmount_f,
			iRAmount,
			iRAmount_f,
			cPayCode,
			@user_name as cOperator,
			null as cCheckMan,
			cCoVouchType,
			cDestNo,
			cSrcNo,
			bStartFlag,
			cPZid,
			cFlag,
			cDefine1,
			cDefine2,
			cDefine3,
			cDefine4,
			cDefine5,
			cDefine6,
			cDefine7,
			cDefine8,
			cDefine9,
			cDefine10,
			iAmount_s,
			iRAmount_s,
			VT_ID,
			--Ufts,
			iClosesID,
			iCoClosesID,
			cDefine11,
			cDefine12,
			cDefine13,
			cDefine14,
			cDefine15,
			cDefine16,
			cItemName,
			cGatheringPlan,
			dCreditStart,
			iCreditPeriod,
			dGatheringDate,
			getdate() as dcreatesystime,
			null as dverifysystime,
			getdate() dmodifysystime,
			@user_name as cmodifier,
			null as dmoddate,
			null dverifydate,
			@Auto_ID,
			cPZNum,
			doutbilldate,
			iPrintCount,
			cPluginsourcetype,
			iPluginsourceautoid,
			cPluginsourceautoid,
			iBusType,
			cagentcuscode,
			cOrderNo,
			cContractType,
			iSource,
			cContractID,
			'||app0|'+@cVouchID csysbarcode,
			iDiscountTaxType,
			iTaxRate
			 FROM U8API.DBO.Ap_Vouch_Iface  
			where  -- cLink = 'P02306002' 
			  cBatchNum = @cBatchNum 
			 and row_id = @row_id 
			;
   -- 第三步: 2.导入应付录入明细
    INSERT INTO UFDATA_012_2014.DBO.Ap_Vouchs (
			--Auto_ID,
		cLink,
		cDwCode,
		cDeptCode,
		cPerson,
		cItem_Class,
		cItemCode,
		cDigest,
		cCode,
		cexch_name,
		iExchRate,
		bd_c,
		iAmount,
		iAmount_f,
		cItemName,
		iAmt_s,
		cExpCode,
		iTaxRate,
		iTax,
		iNatTax,
		cDefine22,
		cDefine23,
		cDefine24,
		cDefine25,
		cDefine26,
		cDefine27,
		cDefine28,
		cDefine29,
		cDefine30,
		cDefine31,
		cDefine32,
		cDefine33,
		cDefine34,
		cDefine35,
		cDefine36,
		cDefine37,
		iNoTaxAmount_f,
		iNoTaxAmount)
	select --Auto_ID,
		@cLink,
		cDwCode,
		cDeptCode,
		cPerson,
		cItem_Class,
		cItemCode,
		cDigest,
		cCode,
		cexch_name,
		iExchRate,
		bd_c,
		iAmount,
		iAmount_f,
		cItemName,
		iAmt_s,
		cExpCode,
		iTaxRate,
		iTax,
		iNatTax,
		cDefine22,
		cDefine23,
		cDefine24,
		cDefine25,
		cDefine26,
		cDefine27,
		cDefine28,
		cDefine29,
		cDefine30,
		cDefine31,
		cDefine32,
		cDefine33,
		cDefine34,
		cDefine35,
		cDefine36,
		cDefine37,
		iNoTaxAmount_f,
		iNoTaxAmount
		 from U8API.dbo.Ap_Vouchs_Iface avi
		 where --avi.cLink ='P02306002'
		 -- row_id = @row_id 	
		 cBatchNum = @cBatchNum	  
		 ; 	
	      
     fetch next from c_iface into @row_id,@cStatus, @cBatchNum;
     set @fetch_status = @@FETCH_STATUS 
   end;
   close  c_iface;
   deallocate c_iface;
   	 	
   /*待解决问题:2023-07-08
     1、接口表 AP_Vouch_iface增加字段： 部门名称、业务员名称(工号)、供应商名称、付款条件名称、项目名称 ,以供用户在EXCEL表上填写名称.        
     2、对增加字段转化代码，写入对应字段：cDeptCode、cPerson、cDwCode、cPayCode、未知
     3、接口表 AP_Vouch_iface增加字段：状态:(初始状态为空, S:表示导入成功, E:有错误, F:导入失败)
   */		 	
END; 
   
  -- select CONVERT(nvarchar(6), getdate(), 112)
/*  
-- 前置环境：先创建数据库U8API,再在U8API库中创建两个接口表Ap_Vouch_Iface、Ap_Vouchs_Iface.
-- 应付录入头表接口表
alter table U8API.dbo.Ap_Vouch_Iface add cDeptName nvarchar(150); -- 部门名称
alter table U8API.dbo.Ap_Vouch_Iface add cPersonCode nvarchar(150); -- 业务员名称(工号)
alter table U8API.dbo.Ap_Vouch_Iface add cPersonName nvarchar(150); --业务员名称(姓名)
alter table U8API.dbo.Ap_Vouch_Iface add cDwName nvarchar(150); -- 供应商名称
alter table U8API.dbo.Ap_Vouch_Iface add cPayName nvarchar(150); -- 付款条件名称
alter table U8API.dbo.Ap_Vouch_Iface add cStatus nvarchar(20); -- 状态:(初始状态为空, S:表示导入成功, E:有错误, F:导入失败)
alter table U8API.dbo.Ap_Vouch_Iface add cMsgCode nvarchar(150); -- 错误代码
alter table U8API.dbo.Ap_Vouch_Iface add cMsg nvarchar(250); -- 错误信息
alter table U8API.dbo.Ap_Vouch_Iface add row_id int identity(1,1) not null; -- 自动流水号 
alter table U8API.dbo.Ap_Vouch_Iface add cBatchNum  nvarchar(30);  -- 导入的批次号,建议一张AP,一个号,
alter table U8API.dbo.Ap_Vouch_Iface add cTrxNum nvarchar(50);  -- 发票编号,用户可以要求指定生成的应付发票编号.
alter table U8API.dbo.Ap_Vouch_Iface add debitCredit nvarchar(20); -- 借贷方向(值范围:借:1,贷:0)
*/

/*
方向,对应科目,币种,汇率,原币金额,本币金额,部门,业务员,项目,摘要
借 
-- 应付录入明细表接口表
--alter table U8API.dbo.Ap_Vouchs_Iface add row_id int identity(1,1) not null; -- 自动流水号 (已存在 AUTO_ID字段)
alter table U8API.dbo.Ap_Vouchs_Iface add cBatchNum  nvarchar(30);  -- 导入的批次号,建议一张AP,一个号,
alter table U8API.dbo.Ap_Vouchs_Iface add debitCredit nvarchar(20); -- 借贷方向(值范围:借:1,贷:0)
*/

　　

标签：U8,dbo,Ap,--,nvarchar,用友,录入,id,row
From： https://www.cnblogs.com/samrv/p/17604661.html

相关文章

  • 用友二面
    忘记录音的，残存记忆。1.进程和线程的区别。2.window的调用算法。3.正在进行的一个视频会议网络执行的全过程（从输入URL到页面展示到底发生了什么？）4.留言板模块，评论区怎么提高效率。（或者说前端页面如何加载快一点）5.设计mysql优化器。......
  • 用友助医院推动数电票试点 构建数智化财税系统
    近年来，国税局等部门颁布了密集政策，以推广全面数字化的电子发票(简称：数电票)，数电票开票试点地区逐步扩展到全国所有省份和地区，预计到今年年底前，数电票数量占比将大于90%。对于医院而言，这既是加速财税数字化、改善智慧就医服务的良好契机，也意味着系统接入、流程改造等方面的挑战。数......
  • 用友助力交易控股集团打造财务共享服务中心
    交易控股集团成功打造了财务共享服务中心，通过数字化技术和智能化手段提高了财务工作效率和准确性。该中心成立后，财务工作审批效率提高50%，发票查验时间由月均10个工作日锐减为2个工作日，单家企业平均降本增效39%以上。抓住试点契机，交易控股集团在省属一级企业中率先实现财务共享，成立......
  • 更高效、更精确、更智能的企业收入管理新范式——用友BIP收入云
    企业收入管理现状及痛点随着各大品牌商在线电商平台建设的不断完善，在线支付场景下的收单业务持续增长，在线支付已成为主流支付方式，这也给企业收入管理带来诸多挑战。俯瞰订单的全生命周期，从客户下单商品、生成订单、渠道收款、资金到账，再到财务端交易对账、收款认领、清分计量到最终......
  • m3u8 流视频数据文件。
    #EXT-X-KEY:METHOD=AES-128,URI="https://edu.aliyun.com/hls/1109/clef/YnBGq7zAJf1Is7xIB5v8vI7AIORwwG9W",IV=0x0fe82567a6be41afda68d82d3724976a有URI中的信息为key，访问后得到有IV时使用IV，没有IV时，通常会在m3u8地址中提供，比如下面的最后一部分即iv:eb7ab5bb3cb1ae35f6d5......
  • 转载用友面经
    用友Java一面（已约二面）自我介绍集合类有哪些这些集合是线程安全吗HashMap底层原理为什么不用头插法用了尾插法就线程安全了吗保证线程安全几种方式Synchronized锁升级过程ThreadLocal原理ThreadLocal需要注意的地方volatile作用volatile原理缓存和主存在操作系统\CPU......
  • 用友U8凭证打印“使用标准版”模版调整方法
    用友U8凭证打印“使用标准版”模版调整方法1.调整的目标：“使用标准版”2.找到文件（先装glrep文件夹复到一份到另外一个盘备份，改错了可以恢复原表。）3.打开文件的程序UFO（不能用U8中的UFO打开，版本不一样）4.点击成“格式”状态就可以调整了5.具体参数说明 5.1：直接在模板文件......
  • 关于U8非标生产流程整理
    应用场景：我们对于生产订单无需走LRP或者MRP生成，而是直接进行订单生产，同时无需BOM，也就是自由订单模式：1.在生产订单录入时注意选择：非标生产 2.生产订单增加行后，我们需要保存后选择子件，这里子件可以和母件相同，也可以不同，若和母件相同时则会弹出提醒子母件相同，忽略即可。同时这里......
  • u8:应付单据录入生成编号
    用友U8在后台用存储过程导入 应付单据录入。创建 几个存储过程进行处理单据编号的生成。第一步：取出单据编号useUFDATA_012_2014goifOBJECT_ID('p_get_number','P')>0DROPPROCEDURE[dbo].[P_Get_Number]GOdeclare@cardNum1nvarchar(60),@dDate1datetime......
  • 用友BIP全面预算
    全面预算是企业在经营过程中制定并实施的一种财务管理工具，它考虑了企业的各个方面，包括销售、采购、生产、财务、人力资源等，以全面的视角规划和控制企业的财务活动。用友BIP全面预算数智化解决方案利用了"事项中台+智能中台+多维内存计算"的组合方式，以使预算更加详细、更快、更智......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99