应用场景
用友NC5.6与宁波银行财资大管家异构，用友NC的付款结算单按笔、批量进行银企付款，财资大管家已支付成功后，会按笔单证，批量单证返回到用友NC付款结算单成功状态及结算完成。
1）单笔转账
2）批量转账
3）单笔查证
4）批量查证
5）交易明细
配置数据源
1）数据库配置
2）SDK配置
配置连接流
管理连接流
1）单笔转账
2）批量转账
3）单笔查证
4）批量查证
5）交易明细
示例
1）查询结算单视图（SQL to API）
SELECT
c.BILLCODE,
bdt.PK_BILLTYPEID,
c.busi_billdate,
c.def1,
c.def2,
NVL2(pd.pk_paybill_detail ,pd.pk_paybill_detail , ap.pk_payitem ) as billno,
NVL2(bd.accnum ,bd.accnum , d.accnum ) as pk_oppaccount ,--付款账号
NVL2(bdk.accnum ,bdk.accnum , dk.accnum ) as pk_account,--收款账号
NVL2(pd.accountname,pd.accountname,dk.accname) as accountname,--收款账户户名
NVL2(pd.accountopenbank,pd.accountopenbank,doc.name) as accountopenbank,--收款方行名
NVL2(pd.memo,pd.memo,ap.scomment) as memo,--用途
c.orglocal as pay_local,--金额
NVL2(pd.accounttype,pd.accounttype,1) as accounttype --对公对私()
FROM
CMP_SETTLEMENT c
LEFT JOIN bd_billtype bdt on c.pk_tradetype = bdt.PK_BILLTYPECODE
LEFT JOIN BD_BILLTYPE b ON c.pk_tradetypeid = b.pk_billtypeid
LEFT JOIN CMP_PAYBILLDETAIL pd on pd.bill_no = c.billcode and pd.dr='0'
LEFT JOIN bd_bankaccsub bd on pd.pk_oppaccount = bd.PK_BANKACCSUB
LEFT JOIN bd_bankaccsub bdk on pd.pk_account = bdk.PK_BANKACCSUB
left join ap_payitem ap on ap.billno = c.billcode and AP.DR = '0' and ap.billclass='fk'
LEFT JOIN bd_bankaccsub d on ap.payaccount = d.PK_BANKACCSUB
LEFT JOIN bd_bankaccsub dk on ap.recaccount = dk.PK_BANKACCSUB
LEFT JOIN bd_bankaccbas bas on bas.pk_bankaccbas = dk.pk_bankaccbas
LEFT JOIN bd_bankdoc doc on doc.pk_bankdoc = bas.pk_bankdoc
WHERE
bdt.PK_BILLTYPEID in( '0001A810000000002PO8','0001A810000000002PMF')
and
c.busistatus = '8' and c.settlestatus = '0'
2）更改结算单状态存储过程（SQL to API）
CREATE OR REPLACE
PROCEDURE UPDATEPAYBILLDETAILFORSTATUS (json6 in varchar) AS
json3 json;
data1 json_list;
oneobj json;
countNum number;
zcountNum number;
wccountNum number;
wwccountNum number;
status varchar(20);
billNo varchar(50);
BEGIN
json3 := json(json6);
data1 := json_ext.get_json_list(json3,'list');
FOR i IN 1 .. data1.count LOOP
oneobj := json(data1.get(i));
select count(bill_no) INTO wccountNum from cmp_paybilldetail where pk_paybill_detail = json_ext.get_string(oneobj, 'detailId') ;
status := json_ext.get_string(oneobj,'status');
IF(wccountNum>0) THEN
select bill_no INTO billNo from cmp_paybilldetail where pk_paybill_detail = json_ext.get_string(oneobj, 'detailId') ;
if (status='0') then
update cmp_paybilldetail set paystatus = '2' where pk_paybill_detail= json_ext.get_string(oneobj, 'detailId');
update cmp_paybill set paystatus = '2' where BILL_NO = billNo;
update cmp_settlement set settlestatus = '5' where billcode = billNo;
ELSIF (status='-3') then
update cmp_paybilldetail set paystatus = '3' where pk_paybill_detail= json_ext.get_string(oneobj, 'detailId');
update cmp_paybill set paystatus = '3' where BILL_NO = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='-2') then
update cmp_paybilldetail set paystatus = '3' where pk_paybill_detail= json_ext.get_string(oneobj, 'detailId');
update cmp_paybill set paystatus = '3' where BILL_NO = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='-1') then
update cmp_paybilldetail set paystatus = '3' where pk_paybill_detail= json_ext.get_string(oneobj, 'detailId');
update cmp_paybill set paystatus = '3' where BILL_NO = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='2') then
update cmp_paybilldetail set paystatus = '3' where pk_paybill_detail= json_ext.get_string(oneobj, 'detailId');
update cmp_paybill set paystatus = '3' where BILL_NO = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
END if;
else
select billno INTO billNo from ap_payitem where pk_payitem = json_ext.get_string(oneobj, 'detailId') ;
dbms_output.put_line(wccountNum);
if (status='0') then
update ap_payitem set payflag = '3' where pk_payitem= json_ext.get_string(oneobj, 'detailId');
update ap_paybill set settleflag = '1' where billno = billNo;
update cmp_settlement set settlestatus = '5' where billcode = billNo;
ELSIF (status='-3') then
update ap_payitem set payflag = '4' where pk_payitem= json_ext.get_string(oneobj, 'detailId');
update ap_paybill set settleflag = '-1' where billno = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='-2') then
update ap_payitem set payflag = '4' where pk_payitem= json_ext.get_string(oneobj, 'detailId');
update ap_paybill set settleflag = '-1' where billno = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='-1') then
update ap_payitem set payflag = '4' where pk_payitem= json_ext.get_string(oneobj, 'detailId');
update ap_paybill set settleflag = '-1' where billno = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
ELSIF (status='2') then
update ap_payitem set payflag = '4' where pk_payitem= json_ext.get_string(oneobj, 'detailId');
update ap_paybill set settleflag = '-1' where billno = billNo;
update cmp_settlement set settlestatus = '4' where billcode = billNo;
END if;
END IF;
END LOOP;
end;
3收款通知存储过程（SQL to API）
CREATE OR REPLACE
PROCEDURE insertList (json6 IN VARCHAR2)
AS
status NVARCHAR2(5000);
json2 NVARCHAR2(5000);
json3 json;
data1 json_list;
onejson json;
oneobj json;
countNum number;
BEGIN
json3 := json(json6);
status := json_ext.get_string(json3, 'Data.retCode');
if status = '0000' THEN
data1 := json_ext.get_json_list(json3,'Data.list');
FOR i IN 1 .. data1.count LOOP
oneobj := json(data1.get(i));
select count(*) INTO countNum from CMP_INFORMER where vbillno = json_ext.get_string(oneobj, 'serialId');
IF(countNum = 0) THEN
insert into CMP_INFORMER(PK_INFORMER,bankaccdate,bankaccount,bankname,creationtime,infodate,modifiedtime,moneyy,
note_no,direction,oppbankaccount,oppunitname,payreason,pk_bank,pk_bankacc,vbillno)
values(trunc(dbms_random.value(1,10000000000000000000)),json_ext.get_string(oneobj,'transTime'),json_ext.get_string(oneobj,'bankAcc'),
json_ext.get_string(oneobj,'bankName'),json_ext.get_string(oneobj,'transTime'),
json_ext.get_string(oneobj,'transTime'),json_ext.get_string(oneobj,'transTime'),
json_ext.get_string(oneobj,'amt'),json_ext.get_string(oneobj,'serialId'),'receivemoney',json_ext.get_string(oneobj,'oppAccNo'),
json_ext.get_string(oneobj,'oppAccName'),json_ext.get_string(oneobj,'abs'),json_ext.get_string(oneobj,'bankName'),
json_ext.get_string(oneobj,'bankAcc'),json_ext.get_string(oneobj,'serialId'));
END IF;
END LOOP;
end IF;
END;