我们通过自定义存储过程从业务系统引入凭证
我们需要以下适配
1、设置业务系统数据库链接
2、在自定义模板中设置存储过程名称及入参，这里的功能键值必须为_InterfaceExec，保留字段作为存储过程名称
3、第二步适配完成后，在引入凭证界面可以选择存储过程，并可以生成入参的输入框
4、在业务系统中适配第二步中的存储过程，可以参考脚本中的示例，要求所有字段名匹配正确，入参与第二步中适配的对应
if (exists (select * from sys.objects where name = 'sp_collect_for_voucher'))
drop proc sp_collect_for_voucher
go
--create proc sp_collect_for_voucher
--(
-- @yearBegin int,
-- @periodBegin int,
-- @yearEnd int,
-- @periodEnd int
--)
--as
---- header
--select '关联单号' as _linkNo,'记' as _word, '备注（页面未体现，忽略）' as _note, '参考信息' as _reference, 2019 as [_year], 4 as _period, getdate() as _businessDate,
--getdate() as [_date], getdate() as _creatTime, 13594 as _creater, '出纳' as _cashier, '经办人' as _agent;
--
---- entry
--select '关联单号' as _linkNo,1 as [_index],/*主营业务收入*/'6001.1001' as _accountSubjectNo,'销售产品A' as _explanation, 1000 as _amount, -1 as _direction, '5C46D0E37EA749CBB0C28C7C65DFD857' as _uniqueKey
--union all
--select '关联单号' as _linkNo,2 as [_index],/*应收账款*/'1122.3001' as _accountSubjectNo,'应收客户甲' as _explanation, 1000 as _amount, 1 as _direction, 'B50E86494313480AA66849A0D091E46E' as _uniqueKey
--
---- udefenties
--
--select '关联单号' as _linkNo, '5C46D0E37EA749CBB0C28C7C65DFD857' as _uniqueKey, '0' as _price_gold, '0' as _qty_gold, '1' as _price_stone, '5' as _qty_stone ,'' as _remark
--
--2.0.0.8变更如下
create proc sp_collect_for_voucher
(
@yearBegin int,
@periodBegin int,
@yearEnd int,
@periodEnd int
)
as
-- header
select '关联单号' as _linkNo,'记' as _word, '备注（页面未体现，忽略）' as _note, '参考信息' as _reference, 2019 as [_year], 4 as _period, getdate() as _businessDate,
getdate() as [_date], getdate() as _creatTime, 13594 as _creater, '出纳' as _cashier, '经办人' as _agent;
-- entry
select '关联单号' as _linkNo,1 as [_index],/*主营业务收入*/'6001.1001' as _accountSubjectNo,'销售产品A' as _explanation, 1000 as _amount, -1 as _direction,
'5C46D0E37EA749CBB0C28C7C65DFD857' as _uniqueKey, '0' as _price_gold, '0' as _qty_gold, '1' as _price_stone, '5' as _qty_stone ,'' as _remark
union all
select '关联单号' as _linkNo,2 as [_index],/*应收账款*/'1122.1001' as _accountSubjectNo,'应收客户甲' as _explanation, 1000 as _amount, 1 as _direction,
'B50E86494313480AA66849A0D091E46E' as _uniqueKey, '0' as _price_gold, '0' as _qty_gold, '1' as _price_stone, '5' as _qty_stone ,'' as _remark
go
5、在业务系统中适配存储过程sp_getaccountsubjectname，用于在第三步中的科目不存在时，从业务系统获取相应的项目名称作为自动生成科目的名称
-- 自动生成科目时通过科目代码获取明细科目名称
if (exists (select * from sys.objects where name = 'sp_getaccountsubjectname'))
drop proc sp_getaccountsubjectname
go
create proc sp_getaccountsubjectname
(
@accountSubjectNo nvarchar(50)
)
as
declare @sales int
select @sales = PATINDEX ( '6001.%' , @accountSubjectNo)
if (@sales = 1)
select '销售产品A'
declare @receivables int
select @receivables = PATINDEX ( '1122.%' , @accountSubjectNo)
if (@receivables = 1)
select '应收客户甲'
go
6、根据业务单号引入凭证sp_generatevoucherbybillno
if (exists (select * from sys.objects where name = 'sp_generatevoucherbybillno'))
drop proc sp_generatevoucherbybillno
go
create proc sp_generatevoucherbybillno
(
@billNo nvarchar(50)
)
as
-- header
select @billNo as _linkNo,'记' as _word, '备注（页面未体现，忽略）' as _note, '参考信息' as _reference, YEAR(GETDATE()) as [_year], MONTH(getdate()) as _period, GETDATE() as _businessDate,
getdate() as [_date], getdate() as _creatTime, 13594 as _creater, '出纳' as _cashier, '经办人' as _agent;
-- entry
select @billNo as _linkNo,1 as [_index],/*主营业务收入*/'6001.1001' as _accountSubjectNo,'销售产品A' as _explanation, 1000 as _amount, -1 as _direction,
'5C46D0E37EA749CBB0C28C7C65DFD857' as _uniqueKey, '0' as _price_gold, '0' as _qty_gold, '1' as _price_stone, '5' as _qty_stone ,'' as _remark
union all
select @billNo as _linkNo,2 as [_index],/*应收账款*/'1122.1001' as _accountSubjectNo,'应收客户甲' as _explanation, 1000 as _amount, 1 as _direction,
'B50E86494313480AA66849A0D091E46E' as _uniqueKey, '0' as _price_gold, '0' as _qty_gold, '1' as _price_stone, '5' as _qty_stone ,'' as _remark
上述适配完成后，可以到引入凭证界面点一下执行来试试。enjoy!