港美圈

用友 userdef(用友erp管理系统)

lisa • • 美股资讯 • 阅读 0

0) THENselect bill_no INTO billNo from cmp_paybilldetail where pk_paybill_detail = json_ext.get_stri

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉

应用场景

用友NC5.6与宁波银行财资大管家异构，用友NC的付款结算单按笔、批量进行银企付款，财资大管家已支付成功后，会按笔单证，批量单证返回到用友NC付款结算单成功状态及结算完成。

1）单笔转账

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


2）批量转账

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


3）单笔查证

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


4）批量查证

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


5）交易明细

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


配置数据源

1）数据库配置

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


2）SDK配置

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


配置连接流

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


管理连接流

1）单笔转账

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


2）批量转账

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


3）单笔查证

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


4）批量查证

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉


5）交易明细

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉

重磅！《2022低代码数据连接流Dslink开发流程手册》新鲜出炉



示例

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;

延伸阅读:
关键词: 付款结算 pk json
本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 931614094@qq.com 举报，一经查实，本站将立刻删除。
用友 userdef(用友erp管理系统)文档下载： PDF DOC TXT
上一篇 2022年11月11日 pm 3:40:37
下一篇 2022年11月11日 pm 3:40:37

猜你喜欢