CREATE OR REPLACE PROCEDURE SP_SF_TKJZ00_REG000 --本存储过程按照结帐单号+MXID00来冲销 ( as_XM0000 in char, --病人姓名 as_XB0000 in char, --病人性别 ad_JZDH00 in number, --病人结账单号 ad_NEWDJH in number, --病人新冲销的费用单据号 NEW!!! ad_CZY000 in number, --操作员 as_CZYXM0 in char, --操作员姓名 ad_CZYKS0 in number, --操作员所在科室名称 as_YJJXH0 in char, --发票上的序号 as_SFYB00 in char, --是否医保病人,'Y':医保 as_ZPZH00 in char, --病人所属医保中心的编号 ad_FBBH00 in number default -1 , --结算时费别编号 as_YBLB00 in char default '0', --结算时医保类别 as_YBBRLB in char default '0', --结算时医保病人类别 as_SFDYFP in char default 'Y', --是否打印发票，N为否，Y为是 ad_NEWJZH out number, --退款后的新的结账单号 ad_ZFDJH0 out number, --病人退费记录对应的单据号 ad_GRDJH0 out number, --医保个人账户对应的单据号 ad_TJDJH0 out number, --医保统筹基金对应的单据号 ad_SBZHDH out number, --商保个人帐户单据流水号 ad_SBTJDH out number, --商保统筹基金单据流水号 ad_JJDJH0 out number, --保健基金单据号 ad_SYDJH0 out number, --商业保险支付单据号 ad_SBDJH0 out number, --商保基金支付单据号(省属三家市属九家医疗机构) ad_BJDJH0 out number, --保健基金支付单据号(省属三家市属九家医疗机构) ad_GWYDJH out number, --公务员单据号 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.15 传递参数增加保健基金单据号; -- jlg 2007.07.27 增加公务员补助GWYGZ0,JJZFE0的处理 -- jlg 2007.08.03 增加商业保险支付SYBXZF的处理 MZSF-20070801-001 -- jlg 2007.09.11 增加非医保金额FYBJE0的处理 MZSF-20070906-002 -- jlg 2008.01.03 增加公费转自费后退款的处理 MZSF-20071219-002 (广东医院) -- jlg 2008.01.15 增加乙类自付,超封顶线金额的处理 MZSF-20080115-002 -- xzw 2008.04.28 增加商保基金支付金额和保健基金支付金额的处理MZSF-20080401-004 -- jlg 2008.05.26 增加传入参数FBBH00,YBLB00,YBBRLB -- xzw 2008.07.30 10G修改，使程序能够在10G库上正常操作 -- qks 2009.04.20 新增参数SF_SFYXKYTKTH，是否允许跨院退款以及退号等 -- xzw 2009.10.30 10G修改，使程序能够在10G库上正常操作 -- qks 2009.11.30 当ls_BRZJE0 <> 0时，修正写入sf_jzb000.JMJE00。 -- qks 2010.01.25 bug:广州医保退费时as_SFYB00='Y',但是没有再进行补充登记,引起重新收取费用没写入到缴费表。 -- xzw 2010.03.03 增加传入参数ad_GWYDJH -- qks 2010.03.27 表sf_jzb000新增加YBYL01--YBYL13共13个字段 -- qks 2010.07.27 广东省中医院 当TRIM(LS_SFCFFP)='N'时SF_JZMX00.ZFBL00以及QZFJE0取值更改 -- qks 2010.08.25 广州医保通过接口退费,允许部分退费 -- qks 2010.10.15 广东省中医院：1、医保病人使用自费结算LS_OLDYBDJH='0' 2、医保结算全部退款ls_BRZJE0=0 不跟医保交换数据 -- qks 2010.10.24 广东省中医院：处理预交金方式支付金额退款 -- zhangyc 2010.11.05 修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) -- zhangyc 2010.11.19 增加ls_FPNum条件判断 -- qks 2010.11.26 广东省中医院: 修改“医保病人使用自费结算”以及“医保结算全部退款”条件; 除了广州医保之外的其他医保要求全部退费 -- qks 2010.12.20 广东省中医院: 判断LS_GDYB00值增加条件 and (as_SFYB00 = 'Y') -- qks 2010.12.21 处理医保电子钱包现金结算退款时,电子钱包也要退款(自费病人现金结算目前都当现金来退) MZSF-20101221-002 -- jinfl 2011.12.29 当只打印结算单的时候，sf_pjsyqk这张表的SFDY设为N MZSF-20111115-001 -- jinfl 2011.12.29 结账表生成负数记录的时候，身份信息用原来被冲的信息 MZSF-20111215-005 --zhangyc 2012.11.20 扩展字段YBYL14--YBYL30 BY MZSF-20121120-004 -- jinfl 2013.12.13 将SP_SF_TJTKDZ_TKREG0过程放到本过程中执行。原来是放在程序中 MZSF-20131212-002 --zhangyc 2014.08.04 取消结算时FBBH00,YBLB00,YBZXLB,YBBRLB 按原来的值插入 by MZSF-20140513-001 --zhangyc 2015.07.30 修改账户信息表SF_BRZHXX.TCJE00 by MZSF-20150708-002 ls_FBBH00 SF_BRXXB0.FBBH00%TYPE; --费别编号 ls_JFLBID SF_BRFY00.JFLBID%TYPE; --病人交费类别ID ls_BRID00 SF_BRFY00.BRID00%TYPE; --病人ID ls_DJH000 SF_BRFY00.DJH000%TYPE; --单据号 ls_BRFB00 SF_BRFY00.BRFB00%TYPE; ls_XM0000 SF_BRFY00.XM0000%TYPE; ls_MZID00 SF_BRFY00.MZID00%TYPE; ls_MZH000 SF_BRFY00.MZH000%TYPE; ls_ZFJFJE SF_BRFY00.ZFJE00%TYPE; --交费表对应的自费金额 ls_JZJEJE SF_BRFY00.JZJE00%TYPE; --交费表对应的记账金额 ls_BRZJE0 SF_JZB000.BRZJE0%TYPE; --新结算的费用总额 ls_OLDZJE SF_JZB000.BRZJE0%TYPE; --旧结算的费用总额 ls_HJJE00 SF_FYMX00.HJJE00%TYPE; --合计金额 ls_ZFJE00 SF_FYMX00.ZFJE00%TYPE; --现金支付 ls_JZJE00 SF_FYMX00.JZJE00%TYPE; --记账金额 ls_GFJE00 SF_FYMX00.GFJE00%TYPE; --其它支付 ls_tczfje SF_FYMX00.GFJE00%TYPE; ls_QZFJE0 SF_FYMX00.QZFJE0%TYPE; ls_PJH000 SF_PJSYQK.PJH000%TYPE; --票据号 ls_TMPPJH SF_JZB000.PJH000%TYPE; --临时的票据号 ls_OLDPJH SF_JZB000.PJH000%TYPE; --旧的票据号 ls_PJH001 SF_JZB000.PJH000%TYPE; --临时票据号 ls_JFBZ00 SF_BRJFB0.JFBZ00%TYPE; --病人交费标志 ls_OLDXH0 SF_PJSYQK.PJXH00%TYPE; --旧的票据对应的序号 ls_TMPJE0 SF_BRFY00.HJJE00%TYPE; --合计金额 ls_TMPPJH01 SF_JZB000.PJH000%TYPE; --临时的票据号 ls_ZFFSBH_2 BM_BRZFFS.ZFFSBH%TYPE; ls_ZFFSMC_2 BM_BRZFFS.ZFFSMC%TYPE; oldzxj sf_brjfb0.jfje00%type; oldjz0 sf_brjfb0.jfje00%type; ls_sfcffp_temp bm_brfbb0.sfcffp%type; ls_sfcffp bm_brfbb0.sfcffp%type; ls_ZFBL01 IC_YBBRLB.MRZFBL%TYPE; --自付比例 ls_yblb00 bm_brxxb0.yblb00%type; ls_SSWUTSCL CHAR(1); --四舍五入部分的金额是否要特殊处理 ls_COUNT0 NUMBER(5); ls_FPNum NUMBER(5); ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_ZFBL00 SF_JZMX00.ZFBL00%TYPE; --自付比例 ls_JZMX00 SF_JZMX00%ROWTYPE; ls_MRZFBL IC_YBBRLB.MRZFBL%TYPE; --自付比例 LS_SF_ZY_FFJJ CHAR(3); --逢分进角参数 SF_SFCYSSWR char(2); SF_TKAZFFS char(2); LS_SFYXKYTK XT_XTCS00.value0%type; LS_OLDCZYKS SF_JZB000.CZYKS0%TYPE; --收费科室 ls_yyid00 xt_yyxx00.yyid00%type; ls_xgjzje sf_brfy00.jzje00%type; --修改记账金额 added for MZSF-20071219-002 ls_jfzfje sf_brjfb0.jfje00%type; LS_OLDYBDJH SF_JZB000.YBDJH0%TYPE; LS_GDYB00 CHAR(1); --'A' 广州医保,'Q' 广州其他医保 LS_OLDFBBH SF_JZB000.FBBH00%type; --结算时费别编号 LS_OLDFBMC BM_BRFBB0.FBMC00%type; --结算时费别名称 ls_OLDYBLB00 SF_JZB000.YBLB00%TYPE;--结算时医保类别 ls_OLDYBBRLB SF_JZB000.YBBRLB%TYPE; --结算时病人类别 ls_TJ_ZEJMXMID varchar2(40); --总额减免项目ID LS_YBZFJE number(12,2); LS_TCJE00 number(12,2); cursor CUR_SF_PJSYQK_PJH000 is select PJH000 from SF_PJSYQK where PJZT00='0' and FZPJH0 = ls_OLDPJH order by PJH000; cursor CUR_SF_JZMX00_ZFBL00 is select * from SF_JZMX00 where JZDH00 = ad_NEWJZH and instr(XMMC00,'诊察') <= 0 order by ZFBL00; cursor CUR_SF_JZMX00_ZFBL01 is select * from SF_JZMX00 where JZDH00 = ad_NEWJZH and instr(XMMC00,'诊察') > 0 order by ZFBL00; begin ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_JZJEJE :=0; ls_ZFJFJE :=0; ls_TMPPJH01:=0; ls_tczfje :=0; ls_BRZJE0 :=0; ls_JZJE00 :=0; ad_jjdjh0 := 0; ad_SYDJH0 := 0; ad_SBDJH0 := 0; ad_BJDJH0 := 0; ad_GWYDJH := 0; LS_GDYB00 := 'N'; --select yyid00 into ls_yyid00 from xt_yyxx00 where rownum=1; select SF_SF_GETYYID00() YYID00 into ls_yyid00 from dual; begin select PJH000,BRZJE0,BRID00,CZYKS0,YBDJH0,FBBH00,YBLB00,YBBRLB into ls_OLDPJH ,ls_OLDZJE,ls_BRID00,LS_OLDCZYKS,LS_OLDYBDJH,LS_OLDFBBH,ls_OLDYBLB00,ls_OLDYBBRLB from SF_JZB000 where JZDH00 = ad_JZDH00; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:='系统没有找到可冲销的单据号,操作失败!'; as_SYSMSG:=SQLERRM||'select * from sf_jzb000 where jzdh00='||to_char(ad_JZDH00); rollback; return; end; begin select count(1) into ls_FPNum from SF_PJSYQK where PJZT00='0' and FZPJH0 = ls_OLDPJH; EXCEPTION when OTHERS then ls_FPNum:=1; end; begin select trim(value0) into LS_SFYXKYTK from xt_xtcs00 where name00 = 'SF_SFYXKYTKTH'; EXCEPTION when OTHERS then LS_SFYXKYTK:='Y'; end; if LS_SFYXKYTK='N' then --add by qks 2009.04.20 select count(1) into ls_COUNT0 from BM_BMBM00 a,BM_BMBM00 b where a.BMBH00=LS_OLDCZYKS and b.BMBH00=ad_CZYKS0 and a.YYID00=b.YYID00; if ls_COUNT0=0 then as_YHMSG0:='不能进行跨院退款操作!'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; begin select trim(value0) into SF_TKAZFFS from xt_xtcs00 where name00 = 'SF_TKAZFFS'; EXCEPTION when OTHERS then SF_TKAZFFS:='N'; end; begin select trim(value0) into LS_SF_ZY_FFJJ from xt_xtcs00 where name00 = 'SF_ZY_FFJJ'; EXCEPTION when OTHERS then LS_SF_ZY_FFJJ:='N'; END; select FBBH00,yblb00 into ls_FBBH00,ls_yblb00 from BM_BRXXB0 where BRID00 = ls_BRID00; begin select mrzfbl into ls_mrzfbl from ic_ybbrlb where fbbh00=ls_fbbh00 and yblb00=ls_yblb00; exception when others then ls_mrzfbl:=1; end; begin select sfcffp into ls_sfcffp_temp from bm_brfbb0 where fbbh00=ls_fbbh00 and rownum=1; exception when others then ls_sfcffp_temp:='N'; end; begin select trim(value0) into LS_SFCFFP from xt_xtcs00 where name00='SF_SFCFFP'; EXCEPTION when OTHERS then LS_SFCFFP:='N'; end; if (LS_SFCFFP='Y')and(LS_SFCFFP_temp='Y') then LS_SFCFFP:='Y'; else LS_SFCFFP:='N'; end if; begin select trim(value0) into ls_SSWUTSCL from xt_xtcs00 where name00 = 'SF_SSWUTSCL'; EXCEPTION when OTHERS then ls_SSWUTSCL:='N'; end; begin select trim(VALUE0) into ls_TJ_ZEJMXMID from xt_xtcs00 where name00='TJ_ZEJMXMID'; --总额减免收费项目ID exception when others then ls_TJ_ZEJMXMID:=''; end; select FBMC00 into LS_OLDFBMC from BM_BRFBB0 where FBBH00 = LS_OLDFBBH; if (trim(ls_yyid00)>='A') and (as_SFYB00 = 'Y') then if LS_OLDFBBH = 3 then LS_GDYB00 := 'A'; elsif Instr(LS_OLDFBMC,'医保')>0 then LS_GDYB00 := 'Q'; end if; end if; ad_SBZHDH := 0; --商保个人帐户单据流水号 ad_SBTJDH := 0; --商保统筹基金单据流水号 ad_NEWJZH := 0; ad_ZFDJH0 := 0; ad_GRDJH0 := 0; ad_TJDJH0 := 0; ls_JFLBID := 0; --select FBBH00 into ls_FBBH00 from BM_BRXXB0 where BRID00 = ls_BRID00; --先按将要冲销的记录生成一条新的冲销的费用信息 begin select nvl(sum(HJJE00),0),nvl(sum(ZFJE00),0),nvl(sum(JZJE00),0),nvl(sum(GFJE00),0),nvl(sum(QZFJE0),0) into ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00,ls_QZFJE0 from SF_FYMX00 where DJH000 = ad_NEWDJH; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:=' ,操作失败!'; as_SYSMSG:=SQLERRM||'select * from sf_fymx00 where djh000='||to_char(ad_NEWDJH); rollback; return; end; begin select max(DJH000),NVL(max(JFLBID),0),min(trim(BRFB00)),min(trim(XM0000)), max(trim(mzid00)),max(trim(MZH000)) into ls_DJH000,ls_JFLBID,ls_BRFB00,ls_XM0000,ls_MZID00,ls_MZH000 from SF_BRFY00 where JZDH00 = ad_JZDH00; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:='系统没有找到可冲销的单据号,操作失败!'; as_SYSMSG:=SQLERRM||'select * from sf_brfy00 where jzdh00='||to_char(ad_JZDH00); rollback; return; end; --在SF_BRFY00添加一条冲销记录,注意该处的结帐单号与旧的记录的结帐单号相同 Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,QZFJE0,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00) select ad_NEWDJH,MZH000,MZID00,JFLBID,ls_BRID00,XM0000,BRFB00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00,ls_QZFJE0,BRYE00, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKS0,ad_JZDH00,ls_DJH000,as_CZYXM0,'费用退款冲销' from SF_BRFY00 where DJH000 = ls_DJH000; if (ls_TJ_ZEJMXMID is not null) and (ls_TJ_ZEJMXMID<>'0') then SP_SF_TJTKDZ_TKREG0(ad_JZDH00,ls_HJJE00,ad_NEWDJH,ad_CZY000,as_CZYXM0,ad_CZYKS0,ls_BRID00,as_YHMSG0,as_SYSMSG); if (as_YHMSG0 is not null) or (as_SYSMSG is not null) then rollback; return; end if; end if; --select count(*) into ls_COUNT0 from SF_FYMX00 where DJH000 = ls_DJH000 and CXBZ00='Z'; --判断冲销后的该结算单号的总金额,若总金额为零,说明该结算单号全部冲销了,主要是给医保使用的! --select sum(HJJE00) into ls_TMPJE0 from SF_BRFY00 where JZDH00 = ad_JZDH00; --2、按结帐单号,将这些记录做个统一的结帐处理,并打印出新的发票 --2.1.将旧的结算记录也做个冲销 --Added on 2001-12-12,对冲销的进行处理,否则,缴款单打印的不正确 select SQ_SF_JZB000_JZDH00.nextval into ad_NEWJZH from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; Insert into SF_JZB000(JZDH00,YBDJH0,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF, TCJJZF,ZFJE00,JZRQ00,JZSJ00,JZY000,PJH000,XJFKJE,ZZFKJE,CXJZDH,SBGRZH, SBTCJJ,YBXJZF,TSBZBH,GWYBZ0,JJZFE0,SYBXZF,FYBJE0,YLZFJE,CFDXJE,SBZFE0, BJZFE0,ybyl01,ybyl02,ybyl03,ybyl04,ybyl05,ybyl06,ybyl07,ybyl08,ybyl09, ybyl10,ybyl11,ybyl12,ybyl13,ybyl14,ybyl15,ybyl16,ybyl17,ybyl18,ybyl19, ybyl20,ybyl21,ybyl22,ybyl23,ybyl24,ybyl25,ybyl26,ybyl27,ybyl28,ybyl29, ybyl30,YBSM01,YBSM02,YBSM03,YBSM04,YBSM05,YBSM06,YBSM07,YBSM08, FBBH00,YBLB00,YBZXLB,YBBRLB) select ad_NEWJZH,YBDJH0,BRID00,MZID00,-BRZJE0,-JZJE00,-GFJE00,-HJJE00,-JMJE00,-GRZHZF, -TCJJZF,-ZFJE00,ls_CZRQ00,ls_CZSJ00,ad_CZY000,ls_PJH000,-XJFKJE,-ZZFKJE,ad_jzdh00,-SBGRZH, -SBTCJJ,-YBXJZF,TSBZBH,-GWYBZ0,-JJZFE0,-SYBXZF,-FYBJE0,-YLZFJE,-CFDXJE,-SBZFE0, -BJZFE0,-ybyl01,-ybyl02,-ybyl03,-ybyl04,-ybyl05,-ybyl06,-ybyl07,-ybyl08,-ybyl09, -ybyl10,-ybyl11,-ybyl12,-ybyl13,-ybyl14,-ybyl15,-ybyl16,-ybyl17,-ybyl18,-ybyl19, -ybyl20,-ybyl21,-ybyl22,-ybyl23,-ybyl24,-ybyl25,-ybyl26,-ybyl27,-ybyl28,-ybyl29, -ybyl30,YBSM01,YBSM02,YBSM03,YBSM04,YBSM05,YBSM06,YBSM07,YBSM08, FBBH00,YBLB00,YBZXLB,YBBRLB from SF_JZB000 where JZDH00 = ad_JZDH00; Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,QZFJE0,JMJE00,JMSHR0,JMYY00,ZFBL00) select ad_NEWJZH,XMBH00,XMMC00,-XMJE00,-QZFJE0,-JMJE00,JMSHR0,JMYY00,ZFBL00 from SF_JZMX00 where JZDH00 = ad_JZDH00; --修改病人的结帐时的类别(fbbh00,yblb00,ybbrlb),原来在插入的时候根据病人的性质触发器自动生成 --added by jlg 2008.05.26 for MZSF-20080526-001 ,ad_FBBH00 = -1表示未修改费别，则不修改 update SF_JZB000 set FBBH00 = ls_OLDFBBH,YBLB00 = ls_OLDYBLB00,YBBRLB = ls_OLDYBBRLB where JZDH00 = ad_NEWJZH; -- by MZSF-20150708-002 -- 2015.10.14 闽清县医院修改 对TCJE00求sum select nvl(sum(TCJE00),0) into LS_TCJE00 from SF_BRZHXX Where brid00=ls_brid00; Select abs(nvl(TCJJZF,0)) into LS_YBZFJE from SF_JZB000 where JZDH00=ad_JZDH00 and brid00=ls_brid00; if nvl(LS_YBZFJE,0)>=nvl(LS_TCJE00,0) then LS_TCJE00:=0; else LS_TCJE00:=nvl(LS_TCJE00,0)-nvl(LS_YBZFJE,0); end if; update SF_BRZHXX set TCJE00=nvl(LS_TCJE00,0) Where brid00=ls_BRID00; --modify by qks 2010.10.24 zffs00增加19:预交金支付(广东专用) --modify by qks 2010.12.21 zffs00增加18:电子钱包支付 begin select nvl(sum(JFJE00),0) into oldzxj from SF_BRJFB0 a where a.brid00=ls_brid00 and a.zffs00 in(1,8,2,5,19,18) and a.JZDH00 in (select b.jzdh00 from sf_jzb000 b where b.brid00=ls_brid00 start with b.jzdh00=ad_JZDH00 connect by prior b.cxjzdh=b.jzdh00); exception when others then oldzxj:=0; end; begin select nvl(sum(JFJE00),0) into oldjz0 from SF_BRJFB0 where brid00=ls_brid00 and zffs00 =7 and JZDH00 in(select jzdh00 from sf_jzb000 where brid00=ls_brid00 start with jzdh00=ad_JZDH00 connect by prior cxjzdh=jzdh00); exception when others then oldjz0:=0; end; --Update by GZ,由于退款的发票有多张(如公医病人时) Delete from SF_PJSYMX where PJH000 = ls_PJH000; --票据明细的数据要重新生成 -- open CUR_SF_PJSYQK_PJH000; -- LOOP -- fetch CUR_SF_PJSYQK_PJH000 INTO ls_TMPPJH; -- exit when CUR_SF_PJSYQK_PJH000%NOTFOUND; -- select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH001 from dual; -- Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) -- select ls_PJH001,PLBH00,PLMC00,'-'||trim(PJXH00),-XJJE00,-ZZJE00,ad_CZY000,as_CZYXM0, -- ls_CZRQ00,ls_CZSJ00,'N','2',as_XM0000,as_XB0000,ls_PJH000,ls_BRID00 from SF_PJSYQK where PJH000 = ls_TMPPJH; -- Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,ZFBL00,XMBZ00,QZFJE0) -- select ls_PJH001,XMBH00,XMMC00,-XMJE00,ZFBL00,XMBZ00,-QZFJE0 from SF_PJSYMX where PJH000 = ls_TMPPJH; -- end loop; -- close CUR_SF_PJSYQK_PJH000; if ls_FPNum>1 then ---多张票据 begin open CUR_SF_PJSYQK_PJH000; LOOP fetch CUR_SF_PJSYQK_PJH000 INTO ls_TMPPJH; exit when CUR_SF_PJSYQK_PJH000%NOTFOUND; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH001 from dual; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) select ls_PJH001,PLBH00,PLMC00,'-'||trim(PJXH00),-XJJE00,-ZZJE00,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'N','2',as_XM0000,as_XB0000,ls_PJH000,ls_BRID00 from SF_PJSYQK where PJH000 = ls_TMPPJH; Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,ZFBL00,XMBZ00,QZFJE0) select ls_PJH001,XMBH00,XMMC00,-XMJE00,ZFBL00,XMBZ00,-QZFJE0 from SF_PJSYMX where PJH000 = ls_TMPPJH; end loop; close CUR_SF_PJSYQK_PJH000; end; end if; if ls_FPNum=1 then begin select PJH000 into ls_TMPPJH from SF_PJSYQK where PJZT00='0' and FZPJH0 = ls_OLDPJH; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) select ls_PJH000,PLBH00,PLMC00,'-'||trim(PJXH00),-XJJE00,-ZZJE00,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'N','2',as_XM0000,as_XB0000,ls_PJH000,ls_BRID00 from SF_PJSYQK where PJH000 = ls_TMPPJH; Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,ZFBL00,XMBZ00,QZFJE0) select ls_PJH000,XMBH00,XMMC00,-XMJE00,ZFBL00,XMBZ00,-QZFJE0 from SF_PJSYMX where PJH000 = ls_TMPPJH; end; end if; --2.2.冲销记录做结算处理, 添加结算的明细记录 select nvl(sum(A.HJJE00),0),nvl(sum(A.JZJE00),0) into ls_BRZJE0,LS_JZJE00 from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00; --注意:该处与住院有分别,住院结算都有预交金单(或有交费记录),但门诊预交金病人却可能没有对应的交费记录! --modify by qks 2010.10.15 广东省中医院 1、医保病人使用自费结算LS_OLDYBDJH='0' 2、医保结算全部退款ls_BRZJE0=0 不跟医保交换数据 --广州医保在缴费表中是直接产生退费记录,跟福建的模式（先全退，再产生新收费记录）不一样； --广东除了广州医保之外的其他医保,目前都是全部退费处理; -- if trim(ls_yyid00)>='A' then -- if ((LS_OLDYBDJH='0' or ls_BRZJE0=0) and ls_FBBH00=3) or (ls_FBBH00<>3) then --modify by qks 2010.11.26 修改条件 if LS_GDYB00 = 'A' then if ls_BRZJE0=0 then --全退情况下 --多次退费时，产生的记录太多,注释掉 -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00 -- from SF_BRJFB0 where zffs00 in(4,3,10,11,16) and brid00=ls_brid00 --JZDH00 = ad_JZDH00 and -- and JZDH00 in (select b.jzdh00 from sf_jzb000 b where b.brid00=ls_brid00 start with b.jzdh00=ad_JZDH00 -- connect by prior b.cxjzdh=b.jzdh00); Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE from (select BRID00,JFLBID,-sum(JFJE00) JFJE00,ZFFS00,ZFFSMC,NULL ZPZH00,ls_PJH000 PJH000,ls_CZRQ00 JFRQ00,ls_CZSJ00 JFSJ00, ad_CZY000 JFCZY0,as_CZYXM0 CZYXM0,0 BRYE00,'1' JFBZ00,0 JKBID0,ad_NEWJZH JZDH00,1 WBHLID,0 WBHLJE from SF_BRJFB0 where zffs00 in(4,3,10,11,16) and brid00=ls_brid00 --JZDH00 = ad_JZDH00 and and JZDH00 in (select b.jzdh00 from sf_jzb000 b where b.brid00=ls_brid00 start with b.jzdh00=ad_JZDH00 connect by prior b.cxjzdh=b.jzdh00) group by BRID00,JFLBID,ZFFS00,ZFFSMC) a; end if; else if (as_SFYB00 = 'Y') and (ls_JFLBID=0) then Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00 from SF_BRJFB0 where JZDH00 = ad_JZDH00 and zffs00 in(1,2,8) and brid00=ls_brid00; --处理电子钱包：如果是全退，只产生一条要上传的负记录;如果是部分退费,先产生一负一正都不上传的记录，再产生一负的退款记录，SP_SF_YBJSDJ再修改退款金额(优先退还现金) add by qks 2010.12.21 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00,decode(sign(ls_BRZJE0),0,NULL,'不上传') from SF_BRJFB0 where JZDH00 = ad_JZDH00 and zffs00 in(18) and brid00=ls_brid00; end if; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00 from SF_BRJFB0 where JZDH00 = ad_JZDH00 and zffs00 in(4,3,10,11,16) and brid00=ls_brid00; end if; IF trim(ls_yyid00)>='A' and ls_FBBH00 = 1 and ls_BRFB00 <> 1 AND ls_BRFB00 <> 3 then --广州中医院公费转自费后退款处理 ls_xgjzje :=LS_JZJE00; else ls_xgjzje :=0; end if; if ls_BRZJE0 <> 0 then select SQ_SF_JZB000_JZDH00.nextval into ad_NEWJZH from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --处理电子钱包：部分退费 add by qks 2010.12.21 if (as_SFYB00 = 'Y') and (ls_JFLBID=0) and (trim(ls_yyid00)<'A') then Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00,'不上传' from SF_BRJFB0 where JZDH00 = ad_JZDH00 and zffs00 in(18) and brid00=ls_brid00; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,DECODE(sign(-JFJE00),-1,'1','0'),0,ad_NEWJZH,1,-JFJE00,NULL from SF_BRJFB0 where JZDH00 = ad_JZDH00 and zffs00 in(18) and brid00=ls_brid00; end if; Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,JZRQ00,JZSJ00,JZY000,PJH000,XJFKJE,ZZFKJE,cxjzdh) select ad_NEWJZH,max(BRID00),max(MZID00),sum(HJJE00),sum(JZJE00),sum(GFJE00),sum(HJJE00),0,0,0,SF_SF_SF_SFCYSSWR(sum(ZFJE00)),ls_CZRQ00,ls_CZSJ00,ad_CZY000,ls_PJH000,sum(ZFJE00),sum(JZJE00),ad_jzdh00 from SF_BRFY00 where JZDH00 = ad_JZDH00; --add by qks 2009.11.30 update SF_JZB000 a set JMJE00=(select nvl(sum(JMJE00),0) from sf_fymx00 b,sf_brfy00 c where b.djh000=c.djh000 and c.jzdh00=ad_JZDH00) where a.JZDH00=ad_NEWJZH; --修改病人的结帐时的类别(fbbh00,yblb00,ybbrlb),原来在插入的时候根据病人的性质触发器自动生成 --added by jlg 2008.05.26 for MZSF-20080526-001 ,ad_FBBH00 = -1表示未修改费别，则不修改 --另注：因为前台已经限制，有修改费别的要全部费用一起退掉,所以本来本次不需要，但为了日后扩展需要，也加了 if ad_FBBH00<>-1 then update SF_JZB000 set FBBH00 = ad_FBBH00,YBLB00 = as_YBLB00,YBBRLB = as_YBBRLB where JZDH00 = ad_NEWJZH; end if; if TRIM(LS_SFCFFP)='N' then if trim(ls_yyid00)>='A' then --2010.07.27 广东省中医院 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_NEWJZH,B.FPXMID,B.XMMC00,sum(A.HJJE00),0,null,null,A.ZFBL00,sum(A.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 group by B.FPXMID,B.XMMC00,A.ZFBL00; else Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_NEWJZH,B.FPXMID,B.XMMC00,sum(A.HJJE00),0,null,null,0,sum(A.QZFJE0+(A.HJJE00-A.QZFJE0)*A.ZFBL00) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 group by B.FPXMID,B.XMMC00; end if; --填写病人发票信息表的内容 if as_SFDYFP='Y' then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ls_BRZJE0,0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_BRID00 from BM_PJLXB0 where PLBH00 = 1;--PLBH00=1是门诊发票 else Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ls_BRZJE0,0,0,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ls_BRID00 from BM_PJLXB0 where PLBH00 = 1;--PLBH00=1是门诊发票 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 1)'; rollback; return; end if; else --2、对病人结账明细表的填写 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,ZFBL00,QZFJE0) select ad_NEWJZH,B.FPXMID,B.XMMC00,sum(A.HJJE00),A.ZFBL00,sum(A.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 --可以使用DJH000或结账单号,但由于前台有额外的其它单据号的处理 group by B.FPXMID,B.XMMC00,A.ZFBL00; --3、填写病人票据信息表的内容 --Modify on 2002-12-04,判断是否要拆分发票(条件:若SF_JZMX00的自付比率不同,则要拆分) Delete from SF_PJSYMX where PJH000 = ls_PJH000; --旧票据明细删除 Update SF_PJSYQK set XJJE00 = 0,ZZJE00 = 0,YHKJE0 = 0 where FZPJH0 = ls_PJH000; --原值去掉 select NVL(min(ZFBL00),1) into ls_ZFBL00 from SF_JZMX00 where JZDH00 = ad_NEWJZH and instr(XMMC00,'诊察') <= 0;-- and (ls_FBBH00 <> 5 or (ls_FBBH00 = 5 and instr(XMMC00,'诊察') <= 0)); select nvl(count(ZFBL00),0) into ls_ZFBL01 from SF_JZMX00 where JZDH00 = ad_NEWJZH and instr(XMMC00,'诊察') <= 0 and zfbl00=ls_mrzfbl ; ls_TMPPJH:=ls_PJH000; --开始的票据号取同组的组号 for JZMX00 in CUR_SF_JZMX00_ZFBL00 loop if ls_zfbl00=ls_mrzfbl then ls_TMPPJH01:=ls_TMPPJH; end if; if JZMX00.ZFBL00 <> 1 then if (JZMX00.ZFBL00 <> ls_ZFBL00) and (ls_ZFBL00 <> 1) then --若NOT (自付比例为零同时该项目又是含有诊金的项目) if JZMX00.ZFBL00 > ls_ZFBL00 then select SQ_SF_PJSYQK_PJH000.nextval into ls_TMPPJH from dual; end if; end if; elsif (ls_ZFBL01=0 or (ls_mrzfbl=1)) and (JZMX00.ZFBL00 <> ls_ZFBL00) and (JZMX00.ZFBL00=1) then select SQ_SF_PJSYQK_PJH000.nextval into ls_TMPPJH from dual; ls_TMPPJH01:=ls_TMPPJH; end if; if JZMX00.ZFBL00 > ls_ZFBL00 then ls_ZFBL00:= JZMX00.ZFBL00; end if; if JZMX00.ZFBL00<>1 then Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) values(ls_TMPPJH,JZMX00.XMBH00,JZMX00.XMMC00,JZMX00.XMJE00,0,JZMX00.ZFBL00,JZMX00.QZFJE0); Update SF_PJSYQK set XJJE00 = NVL(XJJE00,0)+(JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0, ZZJE00 = NVL(ZZJE00,0)+JZMX00.XMJE00 - ((JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0) where PJH000 = ls_TMPPJH; else if (ls_TMPPJH01<>ls_TMPPJH)and(ls_TMPPJH01=0) then ls_TMPPJH01:=ls_TMPPJH; end if; Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) values(ls_TMPPJH01,JZMX00.XMBH00,JZMX00.XMMC00,JZMX00.XMJE00,0,JZMX00.ZFBL00,JZMX00.QZFJE0); Update SF_PJSYQK set XJJE00 = NVL(XJJE00,0)+(JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0, ZZJE00 = NVL(ZZJE00,0)+JZMX00.XMJE00 - ((JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0) where PJH000 = ls_TMPPJH01 ; end if; if SQL%NOTFOUND then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) values(ls_TMPPJH,1,'门诊发票',SF_SF_GETPJXH(ad_CZY000,1),(JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0,JZMX00.XMJE00-((JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0), ad_CZY000,as_CZYXM0,ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,as_XB0000,ls_PJH000,ls_BRID00); end if; ls_zfbl00:=jzmx00.zfbl00; end loop; if ls_TMPPJH01<>0 then ls_TMPPJH:=ls_TMPPJH01; else select SQ_SF_PJSYQK_PJH000.nextval into ls_TMPPJH from dual; end if; for JZMX00 IN CUR_SF_JZMX00_ZFBL01 loop Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) values(ls_TMPPJH,JZMX00.XMBH00,JZMX00.XMMC00,JZMX00.XMJE00,0,JZMX00.ZFBL00,JZMX00.QZFJE0); Update SF_PJSYQK set XJJE00 = NVL(XJJE00,0)+(JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0, ZZJE00 = NVL(ZZJE00,0)+JZMX00.XMJE00 - ((JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0) where PJH000 = ls_TMPPJH ; if SQL%NOTFOUND then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) values(ls_TMPPJH,1,'门诊发票',SF_SF_GETPJXH(ad_CZY000,1),(JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0,JZMX00.XMJE00-((JZMX00.XMJE00-JZMX00.QZFJE0)*JZMX00.ZFBL00+JZMX00.QZFJE0), ad_CZY000,as_CZYXM0,ls_CZRQ00,ls_CZSJ00,'Y','0',ls_XM0000,as_XB0000,ls_PJH000,ls_BRID00); end if; end loop; end if; end if; --修改:2001-07-09,退款操作时,产生的差额=(原来发票金额-新发票金额),由于退款时,预交金不发生变化,只是现金变化 --补充说明:由于ls_JFLBID=0,否则医保病人的交费或退款金额都应该为0,否则会与触发器产生重复结果! if ls_JFLBID = 0 then select SQ_SF_BRJFB0_DJH000.nextval into ad_ZFDJH0 from dual; select nvl(sum(ZFJE00),0),nvl(sum(JZJE00),0) into ls_ZFJFJE,ls_JZJEJE from SF_BRFY00 where JZDH00 = ad_JZDH00; begin select ZFFSBH ,ZFFSMC into ls_ZFFSBH_2,ls_ZFFSMC_2 from BM_BRZFFS where ZFFSBH = 1; exception when NO_DATA_FOUND then as_YHMSG0:='系统没有找到支付方式为记账的字典数据，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end; if (ls_ZFJFJE-oldzxj) > 0 then ls_JFBZ00:='0'; elsif (ls_ZFJFJE-oldzxj) < 0 then ls_JFBZ00 := '1'; else ls_JFBZ00 := '1'; end if; IF (trim(SF_TKAZFFS)='N') and (trim(as_SFYB00) = 'N') and (trim(LS_SF_ZY_FFJJ)='N') AND (trim(ls_SSWUTSCL) = 'Y') and (round((ls_ZFJFJE-oldzxj)*10)/10 <> (ls_ZFJFJE-oldzxj)) THEN --On 20030305,按广东省中医院的需求进行补充:对于四舍五入部分金额特殊处理 Insert into sf_brjfb0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000,JFRQ00,JFSJ00, JFCZY0,CZYXM0,BRYE00,JFBZ00,JZDH00,JKBID0,WBHLID,WBHLJE) values(SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_JFLBID,(ls_ZFJFJE-oldzxj)-ROUND((ls_ZFJFJE-oldzxj)*10)/10,5,'四舍五入金额',ls_PJH000,ls_CZRQ00,ls_CZSJ00, ad_CZY000,as_CZYXM0,0, '1',ad_JZDH00,0,1,(ls_ZFJFJE-oldzxj)-ROUND((ls_ZFJFJE-oldzxj)*10)/10); --现金病人的账户余额为0 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_ZFDJH0,BRID00,ls_JFLBID,ROUND((ls_ZFJFJE-oldzxj)*10)/10,ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 from SF_BRFY00 where DJH000 = ls_DJH000; ls_tczfje:=ROUND((ls_ZFJFJE-oldzxj)*10)/10; ELSIF (trim(SF_TKAZFFS)='N') and (trim(as_SFYB00) = 'N') AND (trim(LS_SF_ZY_FFJJ)='Y') AND (trim(ls_SSWUTSCL) = 'Y') and (round(ls_ZFJFJE-oldzxj+0.04,1) <> (ls_ZFJFJE-oldzxj)) THEN --20030916宁德医院逢分进角 Insert into sf_brjfb0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000,JFRQ00,JFSJ00, JFCZY0,CZYXM0,BRYE00,JFBZ00,JZDH00,JKBID0,WBHLID,WBHLJE) values(SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_JFLBID,(ls_ZFJFJE-oldzxj)-ROUND((ls_ZFJFJE-oldzxj)+0.04,1),5,'四舍五入金额',ls_PJH000,ls_CZRQ00,ls_CZSJ00, ad_CZY000,as_CZYXM0,0, '1',ad_JZDH00,0,1,(ls_ZFJFJE-oldzxj)-ROUND(ls_ZFJFJE-oldzxj+0.04,1)); --现金病人的账户余额为0 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_ZFDJH0,BRID00,ls_JFLBID,ROUND(ls_ZFJFJE-oldzxj+0.04,1),ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 from SF_BRFY00 where DJH000 = ls_DJH000; ls_tczfje:=ROUND(ls_ZFJFJE-oldzxj+0.04,1); else --广州医保退费时as_SFYB00='Y',但是没有再进行补充登记,引起重新收取费用没写入到缴费表 add by qks 2010.01.25 -- if trim(ls_yyid00)>='A' then -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- select ad_ZFDJH0,BRID00,ls_JFLBID,DECODE(as_SFYB00,'Y',ls_BRZJE0,(ls_ZFJFJE-oldzxj)),ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, -- ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 -- from SF_BRFY00 where DJH000 = ls_DJH000; -- if trim(ls_yyid00)>='A' and ad_FBBH00=3 and as_SFYB00='Y' then --modify by qks 2010.08.25 -- select nvl(max(JFJE00),0) into ls_jfzfje from SF_BRJFB0 where JZDH00 = ad_JZDH00 and brid00=ls_brid00 and ZFFS00 in (1,2,8); -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- select ad_ZFDJH0,BRID00,ls_JFLBID,ls_jfzfje,ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, -- ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 -- from SF_BRFY00 where DJH000 = ls_DJH000; -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,JFBZ00,0,ad_NEWJZH,1,0 -- from SF_BRJFB0 where JZDH00 = ad_JZDH00 and brid00=ls_brid00 and ZFFS00 not in (1,2,8); --modify by qks 2010.10.15 广东省中医院 1、医保病人使用自费结算LS_OLDYBDJH='0' 2、医保结算全部退款ls_BRZJE0=0 不跟医保交换数据 -- if trim(ls_yyid00)>='A' and ls_FBBH00=3 and as_SFYB00='Y' then -- if LS_OLDYBDJH='0' or ls_BRZJE0=0 then -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- select ad_ZFDJH0,BRID00,ls_JFLBID,(ls_ZFJFJE-oldzxj),ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, -- ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 -- from SF_BRFY00 where DJH000 = ls_DJH000; --modify by qks 2010.11.26 只处理广州医保,广州其他医保(全部退款)上面已处理 if LS_GDYB00 = 'A' or LS_GDYB00 = 'Q' then --if LS_GDYB00 = 'A' then if (ls_BRZJE0 = 0) or (LS_GDYB00 = 'Q') then Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_ZFDJH0,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE from (select BRID00,0 JFLBID,-sum(JFJE00) JFJE00,ls_ZFFSBH_2 ZFFS00,ls_ZFFSMC_2 ZFFSMC,NULL ZPZH00,ls_PJH000 PJH000,ls_CZRQ00 JFRQ00,ls_CZSJ00 JFSJ00, ad_CZY000 JFCZY0,as_CZYXM0 CZYXM0,0 BRYE00,'1' JFBZ00,0 JKBID0,ad_NEWJZH JZDH00,1 WBHLID,0 WBHLJE from SF_BRJFB0 where zffs00 in(1,2,8,5,19) and brid00=ls_brid00 and JZDH00 in (select b.jzdh00 from sf_jzb000 b where b.brid00=ls_brid00 start with b.jzdh00=ad_JZDH00 connect by prior b.cxjzdh=b.jzdh00) group by BRID00) a; --JFLBID else --modify by qks 2010.10.15 待跟医保接口交换数据后根据过程SP_SF_GDYBSF00_BCREG0修正 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_ZFDJH0,BRID00,JFLBID,0,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from SF_BRJFB0 where JZDH00 = ad_JZDH00 and brid00=ls_brid00 and zffs00=1; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,JFLBID,0,ZFFS00,ZFFSMC,ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from SF_BRJFB0 where JZDH00 = ad_JZDH00 and brid00=ls_brid00 and zffs00<>1 ; end if; --end if; else Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_ZFDJH0,BRID00,ls_JFLBID,DECODE(as_SFYB00,'Y',0,(ls_ZFJFJE-oldzxj)),ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 from SF_BRFY00 where DJH000 = ls_DJH000; end if; ls_tczfje:=(ls_ZFJFJE-oldzxj); end if; if (ls_JZJEJE-oldjz0) > 0 then ls_JFBZ00:='0'; elsif (ls_JZJEJE-oldjz0) < 0 then ls_JFBZ00 := '1'; else ls_JFBZ00 := '1'; end if; begin select ZFFSBH ,ZFFSMC into ls_ZFFSBH_2,ls_ZFFSMC_2 from BM_BRZFFS where ZFFSBH = 7 and ZFFSMC like '%记账%'; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:='系统没有找到支付方式为记账的字典数据，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM; rollback; return; end; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,BRID00,ls_JFLBID,ls_JZJEJE-oldjz0,ls_ZFFSBH_2,ls_ZFFSMC_2,'',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,ls_JFBZ00,0,ad_NEWJZH,1,ls_BRZJE0 from SF_BRFY00 where DJH000 = ls_DJH000; end if; IF ls_xgjzje <> 0 then --广东公费转自费退款处理 update SF_BRJFB0 SET JFJE00 = JFJE00 + decode(ZFFS00,'1',ls_xgjzje,'7',-ls_xgjzje,0) WHERE JZDH00 = ad_NEWJZH; update SF_JZB000 SET JZJE00 = JZJE00-ls_xgjzje,ZFJE00 = ZFJE00+ls_xgjzje,XJFKJE = XJFKJE +ls_xgjzje,ZZFKJE = ZZFKJE -ls_xgjzje WHERE JZDH00 = ad_NEWJZH; update SF_JZMX00 SET ZFBL00 = 1,QZFJE0 = XMJE00 where JZDH00 = ad_NEWJZH; update SF_PJSYMX SET ZFBL00 = 1,QZFJE0 = XMJE00 where PJH000 = ls_PJH000; end if; --Update交费表的票据号(Added on 2001-05-17) --Update SF_BRJFB0 set PJH000 = ls_PJH000,JZDH00 = ad_NEWJZH where PJH000 = ls_OLDPJH; --对应的票据号的更改 --Update旧的票据信息,将旧的票据信息设置成作废状态 Update SF_PJSYQK set PJZT00 = '1', HSCZY0 = ad_CZY000, HSCZXM = as_CZYXM0, HSRQ00 = ls_CZRQ00, HSSJ00 = ls_CZSJ00, OLDPJH = ad_JZDH00, NEWPJH = ls_PJH000 where FZPJH0 = ls_OLDPJH; Update SF_PJSYQK set TCZFJE = decode(sign(ls_tczfje),1,0,ls_tczfje),--ls_tczfje,--退款时有可能再收钱 TCJZJE = decode(sign(ls_JZJEJE-oldjz0),1,0,ls_JZJEJE-oldjz0)--ls_JZJEJE-oldjz0 where FZPJH0 = ls_OLDPJH and rownum=1; Update SF_BRFY00 set JZDH00 = ad_NEWJZH where JZDH00 = ad_JZDH00; --触发器TR_SF_SF_JZB000_INSERT没起到作用 insert into sf_jzmxxm (JZDH00,MXID00,BZ0000) select ad_NEWJZH,mxid00,1 from sf_brfy00 a,sf_fymx00 b where a.brid00=ls_brid00 and a.jzdh00=ad_NEWJZH and a.djh000=b.djh000; --若是医保病人，还应该添加病人的个人账户,统筹基金额的交费信息 --若是医保病人,同时又是部分冲销的(若是全部冲销,就没有必要进行本操作了!) IF ((as_SFYB00 = 'Y') and (ls_BRZJE0 <> 0) and (trim(ls_yyid00)<'A')) THEN --2010.08.25 qks 增加条件and (trim(ls_yyid00)<'A') --生成新结算记录对应的医保接口文件返回的相关金额 select SQ_SF_BRJFB0_DJH000.nextval into ad_GRDJH0 from dual ; --个人账户对应的单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_GRDJH0,ls_BRID00,ls_JFLBID,0,3,'医保个人账户',as_ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0); select SQ_SF_BRJFB0_DJH000.nextval into ad_TJDJH0 from dual ; --统筹基金对应的单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_TJDJH0,ls_BRID00,ls_JFLBID,0,4,'医保统筹基金',as_ZPZH00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0); select SQ_SF_BRJFB0_DJH000.nextval into ad_SBZHDH from dual; --商保个人帐户单据流水号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBZHDH,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 10; select SQ_SF_BRJFB0_DJH000.nextval into ad_SBTJDH from dual; --商保统筹基金单据流水号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBTJDH,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 11; select SQ_SF_BRJFB0_DJH000.nextval into ad_jjdjh0 from dual; --保健基金单据流水号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_jjdjh0,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 6; select SQ_SF_BRJFB0_DJH000.nextval into ad_SYDJH0 from dual; --保健基金单据流水号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SYDJH0,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 13; select SQ_SF_BRJFB0_DJH000.nextval into ad_SBDJH0 from dual; --商保基金支付单据号(省属三家市属九家医疗机构) Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBDJH0,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 14; select SQ_SF_BRJFB0_DJH000.nextval into ad_BJDJH0 from dual; --保健基金单据流水号(省属三家市属九家医疗机构) Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_BJDJH0,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 15; select SQ_SF_BRJFB0_DJH000.nextval into ad_GWYDJH from dual; --公务员单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_GWYDJH,ls_BRID00,ls_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ad_NEWJZH,1,0 from BM_BRZFFS where ZFFSBH = 16; end if; --COMMIT,由于有医保接口,所以事务放在Delphi中控制 EXCEPTION when NO_DATA_FOUND then as_YHMSG0:='系统没有找到可冲销的单据号1,操作失败!'; as_SYSMSG:=SQLERRM; rollback; when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_TKJZ00_REG000('||as_XM0000||','||as_XB0000||','|| to_char(ad_JZDH00)||','||to_char(ad_NEWDJH)||','||to_char(ad_CZY000)||','||as_CZYXM0||','|| to_char(ad_CZYKS0)||','||as_YJJXH0||','||as_SFYB00||','||as_ZPZH00||',ad_NEWJZH,ad_ZFDJH0,ad_GRDJH0,ad_TJDJH0,as_YHMSG0,as_SYSMSG)',1,150); rollback; end; /