U8:用友U8导入应付单录入、应收单录入存储过程
用友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)
*/
优质生活从拆开始
