CREATE OR REPLACE
PROCEDURE P_SyncVoucharToOA AS
v_updatetime date;
v_quantity integer;
v_init_time date;
BEGIN
v_init_time := to_date('2021-10-01', 'YYYY-MM-DD');
SELECT SYSDATE INTO v_updatetime
FROM DUAL;
SELECT COUNT(1) INTO v_quantity
--FROM z_voucher_test t1 inner join z_period t2 on t1.fperiodid = t2.id
FROM t_gl_voucher t1 inner join T_BD_Period t2 on t1.fperiodid = t2.fid
WHERE (t1.FLASTUPDATETIME BETWEEN (select nvl(max(FACTIONTIME), v_init_time) from Z_SYNC_LOG where FACTIONFLAG = 'sync_fybx') AND v_updatetime)
and (substr(t1.FDESCRIPTION, 1, 4) = 'FYBX' OR substr(t1.FDESCRIPTION, 1, 5) = 'NFYBX');
merge into uf_fybxalxx@ecology dt1
using (select cast(t2.fperiodnumber as varchar2(100)) || '#' || cast(t1.FNUMBER as varchar2(100)) as pzbh ,
case t1.FBIZSTATUS when 0 then '暂存' when 1 then '已提交' when 2 then '已作废' when 3 then '已审核' when 5 then '已过账' end as pzzt,
t1.FLOCALDEBITAMOUNT as pzje, t1.FDESCRIPTION
from t_gl_voucher t1 inner join T_BD_Period t2 on t1.fperiodid = t2.fid
where (t1.FLASTUPDATETIME BETWEEN (select nvl(max(FACTIONTIME), v_init_time) from Z_SYNC_LOG where FACTIONFLAG = 'sync_fybx') AND v_updatetime)
and (substr(t1.FDESCRIPTION, 1, 4) = 'FYBX' OR substr(t1.FDESCRIPTION, 1, 5) = 'NFYBX')
And t1.FDESCRIPTION Not In ('FYBX20220228003','NFYBX20240319034','NFYBX20240321019')) dt2
on (dt1.djbh = dt2.FDESCRIPTION)
when matched then
update set dt1.pzbh = dt2.pzbh,
dt1.pzje = dt2.pzje,
dt1.pzzt = dt2.pzzt,
dt1.gxsj = to_char(sysdate,'YYYY-MM-DD HH24:MI:SS');
COMMIT;
INSERT INTO Z_SYNC_LOG (UUID, FSOURCE, FTARGET, FDESCRIPTION, FQUANTITY, FACTIONTIME, FACTIONFLAG)
VALUES ((select sys_guid() from dual), 'EAS.T_GL_VOUCHER', 'OA.UF_FYBXALXX', '向费用报销台账更新凭证编码,会计期间,凭证总金额,凭证状态。', v_quantity, v_updatetime, 'sync_fybx');
COMMIT;
SELECT COUNT(1) INTO v_quantity
FROM t_gl_voucher t1 inner join T_BD_Period t2 on t1.fperiodid = t2.fid
WHERE (t1.FLASTUPDATETIME BETWEEN (select nvl(max(FACTIONTIME), v_init_time) from Z_SYNC_LOG where FACTIONFLAG = 'sync_clfbx') AND v_updatetime)
and (substr(t1.FDESCRIPTION, 1, 4) = 'CLBX' OR substr(t1.FDESCRIPTION, 1, 5) = 'NCLBX');
merge into uf_clfbx@ecology dt1
using (select cast(t2.fperiodnumber as varchar2(100)) || '#' || cast(t1.FNUMBER as varchar2(100)) as pzbh ,
case t1.FBIZSTATUS when 0 then '暂存' when 1 then '已提交' when 2 then '已作废' when 3 then '已审核' when 5 then '已过账' end as pzzt,
t1.FLOCALDEBITAMOUNT as pzje, t1.FDESCRIPTION
from t_gl_voucher t1 inner join T_BD_Period t2 on t1.fperiodid = t2.fid
where (t1.FLASTUPDATETIME BETWEEN (select nvl(max(FACTIONTIME), v_init_time) from Z_SYNC_LOG where FACTIONFLAG = 'sync_clfbx') AND v_updatetime)
and (substr(t1.FDESCRIPTION, 1, 4) = 'CLBX' OR substr(t1.FDESCRIPTION, 1, 5) = 'NCLBX')) dt2
on (dt1.djbh = dt2.FDESCRIPTION)
when matched then
update set dt1.pzbh = dt2.pzbh,
dt1.pzje = dt2.pzje,
dt1.pzzt = dt2.pzzt,
dt1.gxsj = to_char(sysdate,'YYYY-MM-DD HH24:MI:SS');
COMMIT;
INSERT INTO Z_SYNC_LOG (UUID, FSOURCE, FTARGET, FDESCRIPTION, FQUANTITY, FACTIONTIME, FACTIONFLAG)
VALUES ((select sys_guid() from dual), 'EAS.T_GL_VOUCHER', 'OA.UF_CLFBX', '向差旅费报销台账更新凭证编码,会计期间,凭证总金额,凭证状态。', v_quantity, v_updatetime, 'sync_clfbx');
COMMIT;
END;帮我解析下,这块的含义,以及优化方案
最新发布