CREATE OR REPLACE PROCEDURE SP_SF_YJGZ00_YJD000 ( ad_CXDJH0 IN NUMBER, --被冲销的医技单据号 ad_CZY000 IN NUMBER, --操作员 as_CZYXM0 IN CHAR, --操作员姓名 ad_CZYKS0 IN NUMBER, --操作员所在科室名称 as_SHRXM0 IN CHAR, --审核人 as_SHRYY0 IN CHAR, --审核原因 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as ls_DJH000 SF_BRFY00.DJH000%TYPE; ls_JZDH00 SF_BRFY00.JZDH00%TYPE; ls_GHH000 YJ_YW0000.ZYHGHH%TYPE; ls_GHID00 YJ_YW0000.ZYGHID%TYPE; ls_BRID00 YJ_YW0000.BRID00%TYPE; ls_XM0000 YJ_YW0000.BRXM00%TYPE; ls_XMZT00 YJ_YW0000.XMZT00%TYPE; ls_SFDJH0 YJ_YW0000.YJDJH0%TYPE; ls_YJKSBH YJ_YW0000.YJKSBH%TYPE; --医技科室,执行科室 ls_KDYS00 YJ_YW0000.KDYS00%TYPE; --开单医生编号 ls_KDKSBH YJ_YW0000.KDKSBH%TYPE; --开单科室编号 ls_YSSZKS YJ_YW0000.YSSZKS%TYPE; --医生所在科室 ls_FBBH00 SF_BRXXB0.FBBH00%TYPE; ls_LBBH00 SF_BRXXB0.LBBH00%TYPE; ls_ZXR000 YJ_YW0000.ZXR000%TYPE; ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_COUNT0 NUMBER(5); ls_FSJMJE CHAR(1); --是否向医保发送减免金额 LS_CXYY00 SF_BRFY00.CXYY00%TYPE; LS_FYZZJE NUMBER(12,2); --费用转账支付金额 LS_TKZZJE NUMBER(12,2); --退款转账支付金额 ls_ZZZFJE NUMBER(12,2); --转账支付金额 ls_HJJE00 SF_FYMX00.HJJE00%TYPE; LS_ZRZYLS number(10); lsSSHJSFQYGZHCJK XT_XTCS00.VALUE0%type; --lvMXID00 SF_FYMX00.MXID00%type; Ecustom exception; lvSFSFCG number; lsSFJGSM varchar2(250); V_YJYW00 YJ_YW0000%rowtype; LS_FSWBJKSJ varchar2(30); ls_DJID00 YJ_YW0000.DJID00%type; ls_DJMXID YJ_YWJJ00.DJMXID%type; ls_SL0000 YJ_YW0000.SL0000%type; ls_ZXYSZ0 SF_FYMX00.ZXYSZ0%type; ls_SJZXYS SF_FYMX00.SJZXYS%type; ls_MXID00 YJ_YWJJ00.MXID00%type; begin -- MODIFICATION HISTORY -- Person Date Comments -- qks 2008.08.29 新增参数SF_SFXYBFSJMJE MZSF-20080814-001 ; -- xzw 2008.09.28 修正同一个项目分批冲销多退给病人的问题 -- xzw 2008.11.14 发现有的医院在YJ_YW0000中ZXR000字段为空，更正出错，修改; -- qks 2008.11.24 恢复Update SF_FYMX00 set CXBZ00 = '+'原先的写法,由于YJ_YWJJ00.MXID00值为空引起 MZSF-20081122-001 -- qks 2009.11.30 写入sf_fymx00表增加JMBZ00，同时JMJE00取值有改变 --zhangyc 2012.03.15 费用更正时如有转账金额支付 更正后退回到院内转账金额(SF_BRZHXX.ZZYE00) BY MZSF-20120210-001 -- liuj 2012.04.11 写入SF_FYMX00表时HJJE00,ZFJE00,GFJE00,JZJE00等字段四舍五入两位 forMZSF-20120329-001 -- daihq 2014.04.30 修改冲销单据的XMZT00时，同时修改被冲销单据的XMZT00 for MZSF-20140429-002 --zhangyc 2014.09.19 择日住院挂号时记录择日流水号 by MZSF-20140917-001 --zhangyc 2015.12.08 修改门诊择日费用转住院去掉插入ZRZYLS值 by ZYSF-20151109-001 -- daihq 2016.04.11 增加参数SS_SSHJSFQYGZHCJK控制，是否启用智业高值耗材接口 for SSMZ-20160406-001 -- daihq 2016.05.12 修正SSMZ-20160406-001改后出现的问题 for MZSF-20160512-003 --yanghq 2018.05.08 增加调用过程SP_JK_INSJKB0 for MZSF-20180509-003 -- dsm 2019.05.11 退费申请YJ_YW0000.DJID00,DJMXID写入数据 YJ-20190511-001 -- dsm 2019.08.17 调用接口过程增加异常处理 for MZSF-20190817-001 -- dsm 2020.11.26 SF_FYMX00.ZXYSZ0,SF_FYMX00.SJZXYS for YJ-20201126-001 --linbin 2021.01.15 如YJ_YWJJ00中已冲销记录对应的SF_FYMX00资料打了“+”标志 for MZSF-20201228-004 ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); --是否向医保发送减免金额 begin select upper(trim(VALUE0)) into ls_FSJMJE from XT_XTCS00 where NAME00 = 'SF_SFXYBFSJMJE'; EXCEPTION WHEN OTHERS THEN ls_FSJMJE:='Y'; END; --2016.04.13 daihq 是否启用智业高值耗材接口 begin select nvl(max(trim(VALUE0)),'N') into lsSSHJSFQYGZHCJK from XT_XTCS00 where NAME00='SS_SSHJSFQYGZHCJK'; exception when others then lsSSHJSFQYGZHCJK:='N'; end; select SFDJH0,XMZT00,YJKSBH,KDYS00,KDKSBH,YSSZKS,ZYHGHH,ZYGHID,BRID00,BRXM00, ZXR000,nvl(ZRZYLS,0)ZRZYLS,nvl(DJID00,0) DJID00,nvl(DJMXID,0) DJMXID,SL0000 into ls_SFDJH0,ls_XMZT00,ls_YJKSBH,ls_KDYS00,ls_KDKSBH,ls_YSSZKS,ls_GHH000,ls_GHID00,ls_BRID00,ls_XM0000,ls_ZXR000,LS_ZRZYLS,ls_DJID00,ls_DJMXID,ls_SL0000 from YJ_YW0000 where YJDJH0 = ad_CXDJH0; if ls_SFDJH0 = 0 then as_YHMSG0:='病人被冲的医技单据尚未记账,请先记账后再进行本操作！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; --先加个判断,若病人被冲的单据号已结算过,暂时不做处理！ select JZDH00 into ls_JZDH00 from SF_BRFY00 where DJH000 = ls_SFDJH0; if ls_JZDH00 <> 0 then as_YHMSG0:='病人被冲的处方号已结算,本过程尚未处理！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; begin select FBBH00,LBBH00 into ls_FBBH00,ls_LBBH00 from SF_BRXXB0 where GHID00 = ls_GHID00; EXCEPTION WHEN NO_DATA_FOUND THEN ls_FBBH00 := 1; --当作自费处理 ls_LBBH00 := 1; --门诊预交金 END; if ls_LBBH00 = 0 then as_YHMSG0:='该医技单对应的挂号信息是现金的信息,系统更正失败！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; --修改医记表中的相关信息 UPDATE YJ_YW0000 SET XMZT00 = DECODE(sign(SL0000),-1,'3','2'), --已执行,区别与处方 SFDJH0 = ls_DJH000 WHERE YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 ) and SFDJH0 +0= 0; --同时修改被冲销单据的XMZT00 update YJ_YW0000 a set XMZT00=(select XMZT00 from YJ_YW0000 where YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 ) and rownum=1) where YJDJH0=ad_CXDJH0; select SF_SF_TYZD00('发送外部接口数据','Open') into LS_FSWBJKSJ from dual; if LS_FSWBJKSJ='Y' then select * into V_YJYW00 from YJ_YW0000 where YJDJH0=ad_CXDJH0; --SP_JK_INSJKB0('0',null,V_YJYW00.BRID00,null,null,V_YJYW00.SFDJH0,V_YJYW00.YJDJH0,'1',V_YJYW00.XMZT00,ad_CZY000,V_YJYW00.YJKSBH,0,null); begin execute immediate 'begin SP_JK_INSJKB0('||'0'||','||V_YJYW00.ZYGHID||','||V_YJYW00.BRID00||',null,null,'||V_YJYW00.SFDJH0||','||V_YJYW00.YJDJH0||',''1'','||V_YJYW00.XMZT00||','||nvl(ad_CZY000,'0')||','||V_YJYW00.YJKSBH||',0,null); end; '; exception when others then ls_COUNT0:=0; end; end if; select count(*) into ls_COUNT0 from yj_yw0000 A,bm_zlzd00 B where A.ZLXMID = B.ZLXMID and B.LBBH00 ='4' and YJDJH0 = ad_CXDJH0; if ls_COUNT0 > 0 THEN --若是检验项目,则将其从接口的临时表删除 delete from BM_YJDJH0 where YJDJH0=ad_CXDJH0; END if; if ls_DJID00>0 then ----套餐数量加回去 update SF_TCDJMX set YKSL00=YKSL00-ls_SL0000,SYSL00=SYSL00+ls_SL0000 where DJID00=ls_DJID00 and DJMXID=ls_DJMXID; end if; select ZXYSZ0,SJZXYS into ls_ZXYSZ0,ls_SJZXYS from SF_FYMX00 where DJH000=ls_SFDJH0 and rownum=1; --添加新的费用明细 --select SQ_SF_FYMX00_MXID00.NEXTVAL into lvMXID00 from dual; Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,CXBZ00,JMSHR0,JMYY00,GJBM00, SFLB00,ZFBL00,JMJE00,JMBZ00,ZRZYLS,WZDWM0,GZJLH0,DJID00,DJMXID,ZXYSZ0,SJZXYS,ZFFSBZ) select SQ_SF_FYMX00_MXID00.NEXTVAL,ls_DJH000,B.SFXMID,B.XMMC00,'',DECODE(B.DW0000,NULL,'次',B.DW0000),A.JJJE00,A.SFCS00*A.SL0000,round(A.JJJE00*A.SFCS00*A.SL0000,2),round(A.ZFJE00,2),round(A.GFJE00,2),round(A.JZJE00,2), ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00, ls_KDKSBH,ls_KDYS00,ls_YJKSBH,nvl(ls_ZXR000,0),'0',ls_YSSZKS,'-',as_SHRXM0,as_SHRYY0,A.GJBM00, A.SFLB00,A.ZFBL00,nvl(a.JMJE00,0),DECODE(NVL(a.JMJE00,0),0,'0','4'), --decode(ls_FSJMJE,'Y',0,NVL(A.JMJE00,0)) LS_ZRZYLS,A.WZDWM0,A.GZJLH0,a.DJID00,a.DJMXID,ls_ZXYSZ0,ls_SJZXYS,a.ZFFSBZ from YJ_YWJJ00 A,BM_YYSFXM B where A.SFXMID = B.SFXMID and A.YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 and SFDJH0=ls_DJH000 ); --2016.04.13 daihq add 调用智业高值耗材接口 莆田附属医院 if trim(lsSSHJSFQYGZHCJK)='Y' then for VR in (select A.SFXMID,A.SL0000,A.WZDWM0,A.GZJLH0, (select MXID00 from SF_FYMX00 C where C.DJH000=ls_DJH000 and C.XMBH00=B.SFXMID and C.GZJLH0=A.GZJLH0 and rownum=1) MXID00 from YJ_YWJJ00 A,BM_YYSFXM B where A.SFXMID = B.SFXMID and A.YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 and SFDJH0=ls_DJH000 ) and A.WZDWM0 is not null ) loop SP_SS_GZHCJK(VR.SFXMID,1,VR.WZDWM0,VR.GZJLH0,'0',ls_GHID00,ls_GHH000,as_CZYXM0,ad_CZYKS0,ls_DJH000,VR.MXID00,lsSFJGSM,lvSFSFCG); if lvSFSFCG=-1 then as_YHMSG0:=substrb(lsSFJGSM,1,240); raise Ecustom; end if; end loop; end if; --添加费用主表的信息 ls_ZZZFJE:=0; LS_CXYY00:='SP_SF_YJGZ00_YJD000'; select nvl(sum(ZZZHZF),0) into LS_FYZZJE from SF_BRFY00 Where DJH000=ls_SFDJH0 and BRID00=LS_BRID00; select nvl(sum(ZZZHZF),0) into LS_TKZZJE from SF_BRFY00 Where CXDJH0=ls_SFDJH0 and BRID00=LS_BRID00; select nvl(sum(HJJE00),0) into ls_HJJE00 from SF_FYMX00 where DJH000 = ls_DJH000; if LS_FYZZJE>0 then if LS_FYZZJE>=abs(LS_TKZZJE)+abs(ls_HJJE00) then --冲销的原单号转账金额>=已冲销的转账金额+本次冲销金额 ls_ZZZFJE:=ls_HJJE00; else if LS_FYZZJE>=abs(LS_TKZZJE) then --冲销的原单号转账金额>=已冲销的转账金额 ls_ZZZFJE:=-(LS_FYZZJE-abs(LS_TKZZJE)); else ls_ZZZFJE:=0; end if; end if; end if; Insert into SF_BRFY00(DJH000,YBDJH0,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00,ZZZHZF) select ls_DJH000,'0',ls_GHH000,ls_GHID00,ls_LBBH00,ls_BRID00,ls_XM0000,ls_FBBH00,sum(HJJE00),sum(ZFJE00),sum(JZJE00),sum(GFJE00),0, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKS0,0,ls_SFDJH0,as_CZYXM0,LS_CXYY00,ls_ZZZFJE from SF_FYMX00 where DJH000 = ls_DJH000; update SF_BRFY00 set CXYY00='医技单冲销' where DJH000=ls_DJH000 and BRID00=LS_BRID00;--把原因修改为原来默认方式(医技单冲销) --Update SF_FYMX00 --Update SF_FYMX00 set CXBZ00 = '+' where DJH000 = ls_SFDJH0 -- and XMBH00 in ( select A.SFXMID from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0) --Update SF_FYMX00 set CXBZ00 = '+' where MXID00 in ( select A.MXID00 from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0); begin select max(nvl(A.MXID00,0)) into ls_MXID00 from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0 ; exception when others then ls_MXID00:=0; end; if ls_MXID00 = 0 then Update SF_FYMX00 set CXBZ00 = '+' where DJH000 = ls_SFDJH0 --2008.11.24 and XMBH00 in ( select A.SFXMID from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0); else Update SF_FYMX00 set CXBZ00 = '+' where DJH000 = ls_SFDJH0 --2021.01.15 and MXID00 in ( select A.MXID00 from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and A.CXBZ00='+' and B.CXDJH0=ad_CXDJH0); end if; COMMIT; EXCEPTION when Ecustom then as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YJGZ00_YJD000('||to_char(ad_CXDJH0)||','||to_char(ad_CZY000)||','|| as_CZYXM0||','||to_char(ad_CZYKS0)||',as_YHMSG0,as_SYSMSG)',1,150); rollback; WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YJGZ00_YJD000('||to_char(ad_CXDJH0)||','||to_char(ad_CZY000)||','|| as_CZYXM0||','||to_char(ad_CZYKS0)||',as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; END SP_SF_YJGZ00_YJD000;