CREATE OR REPLACE PROCEDURE SP_ZY_TKJZ00_YJREG0 ( as_XM0000 in char, --病人姓名 as_XB0000 in char, --病人性别 ad_CXDJH0 in number, --医技表中的被冲的单据号 ad_NEWDJH 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_JFLBID ZY_BRFY00.JFLBID%TYPE; --病人交费类别ID ls_BRID00 ZY_BRFY00.BRID00%TYPE; --病人ID ls_DJH000 ZY_BRFY00.DJH000%TYPE; --单据号 ls_ZYH000 ZY_BRFY00.ZYH000%TYPE; --住院号 ls_ZYID00 ZY_BRFY00.ZYID00%TYPE; --住院ID ls_BRFB00 ZY_BRFY00.BRFB00%TYPE; --病人费别 ls_HJJE00 ZY_BRFY00.HJJE00%TYPE; --合计金额 ls_ZFJE00 ZY_BRFY00.ZFJE00%TYPE; --自费金额 ls_JZJE00 ZY_BRFY00.JZJE00%TYPE; --记账金额 ls_GFJE00 ZY_BRFY00.GFJe00%TYPE; --其它金额 ls_BRZJE0 ZY_JZB000.BRZJE0%TYPE; --新结算的费用总额 ls_OLDZJE ZY_JZB000.BRZJE0%TYPE; --旧结算的费用总额 ls_PJH000 ZY_PJSYQK.PJH000%TYPE; --票据号 ls_KDYS00 YJ_YW0000.KDYS00%TYPE; --开单医生编号 ls_KDKSBH YJ_YW0000.KDKSBH%TYPE; --开单科室编号 ls_KDBQ00 YJ_YW0000.KDBQ00%TYPE; --开单病区编号 ls_KDRQ00 YJ_YW0000.KDRQ00%TYPE; --开单日期 ls_KDSJ00 YJ_YW0000.KDSJ00%TYPE; --开单时间 ls_YJKSBH YJ_YW0000.YJKSBH%TYPE; --医技科室编号 ls_YSKS00 ZY_FYMX00.YSKS00%TYPE; ls_ZXYSZ0 ZY_FYMX00.ZXYSZ0%TYPE; ls_SJZXYS ZY_FYMX00.SJZXYS%TYPE; LS_FSWBJKSJ varchar2(30); ls_COUNT0 number(5); -- V_YJYW00 YJ_YW0000%rowtype; BEGIN select KDYS00,KDKSBH,KDRQ00,KDSJ00,YJKSBH into ls_KDYS00,ls_KDKSBH,ls_KDRQ00,ls_KDSJ00,ls_YJKSBH from YJ_YW0000 where YJDJH0 = ad_CXDJH0; select BMBH00 into ls_YSKS00 from BM_YGBM00 where YGBH00 = ls_KDYS00; --1.先按将要冲销的记录生成一条新的冲销的费用信息 select A.DJH000, A.JFLBID, B.BRID00, A.ZYH000, A.ZYID00, A.BRFB00,NVL(B.KDBQ00,0) into ls_DJH000,ls_JFLBID,ls_BRID00,ls_ZYH000,ls_ZYID00,ls_BRFB00,ls_KDBQ00 from ZY_BRFY00 A,YJ_YW0000 B where A.DJH000 = B.SFDJH0 and B.YJDJH0 = ad_CXDJH0; select ZXYSZ0,SJZXYS into ls_ZXYSZ0,ls_SJZXYS from ZY_FYMX00 where DJH000=ls_DJH000 and rownum=1; --在ZY_FYMX00添加冲销的明细信息(注意相关的标志是否正确) --2017.12.06 dsm JJJE00*A.SFCS00*A.SL0000-->round(JJJE00*A.SFCS00*A.SL0000,2) Insert into ZY_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,CXBZ00,KDBQ00,JMSHR0,JMYY00,ZFBL00,GJBM00,SFLB00, flag00,djid00,ZFFSBZ,ZXYSZ0,SJZXYS,GZJLH0,OMXID0) select SQ_ZY_FYMX00_MXID00.nextval,ad_NEWDJH,A.SFXMID,XMMC00,'次',DECODE(B.DW0000,NULL,'次',B.DW0000),A.JJJE00,A.SFCS00*A.SL0000, round(JJJE00*A.SFCS00*A.SL0000,2),NVL(ZFJE00,0),NVL(GFJE00,0),NVL(JZJE00,0), to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ls_KDRQ00,ls_KDSJ00,NVL(ls_KDKSBH,0),NVL(ls_KDYS00,0),Decode(NVL(ls_YJKSBH,0),0, NVL(ls_KDKSBH,0),NVL(ls_YJKSBH,0)),NVL(ls_KDYS00,0),'0',ls_YSKS00,'*',ls_KDBQ00,as_SHRXM0,as_SHRYY0,A.ZFBL00,A.GJBM00,A.SFLB00,'1',A.ID0000 ,A.ZFFSBZ,ls_ZXYSZ0,ls_SJZXYS,A.GZJLH0,A.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 XMZT00 in ('1','8')); --XMZT00 新增加了'8'退费已审批状态 JLG --如果原来有优惠的也要冲回来2018.05.17 ZYSF-20171020-001 Insert into ZY_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,QZFJE0, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,CXBZ00,KDBQ00,JMSHR0,JMYY00,ZFBL00, GJBM00,SFLB00,flag00,djid00,YEXM00,ZFFSBZ,ZXYSZ0,SJZXYS,GZJLH0,OMXID0) select SQ_ZY_FYMX00_MXID00.NEXTVAL,ad_NEWDJH,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,-XMSL00,-HJJE00,-ZFJE00,GFJE00,JZJE00,QZFJE0, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,'-',KDBQ00,JMSHR0,JMYY00,ZFBL00,GJBM00,SFLB00,flag00,djid00,YEXM00 ,a.ZFFSBZ,ZXYSZ0,SJZXYS,a.GZJLH0,a.MXID00 from ZY_FYMX00 a where a.MXID00 in(select YHMXID from YJ_YWJJ00 b,ZY_FYYHMX c where YJDJH0=ad_CXDJH0 and b.MXID00=c.MXID00 and c.YXBZ00='1'); update ZY_FYYHMX a set YXBZ00='0',CXBZ00='-' where MXID00 in( select MXID00 from YJ_YWJJ00 where YJDJH0=ad_CXDJH0 and YXBZ00='1') ; --修改医技表中的相关信息 UPDATE YJ_YW0000 SET XMZT00 = '2', --已收费 SFDJH0 = ad_NEWDJH where YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 and XMZT00 in ('1','8')) and SFDJH0+0 = 0 ; -- if SQL%NOTFOUND then -- as_YHMSG0:='系统查找不到满足条件的医技信息,操作失败！'; -- as_SYSMSG:=SQLERRM; -- ROLLBACK; -- RETURN; -- end if; --Update 旧的费用记录成冲销记录 update ZY_FYMX00 Set CXBZ00 = '+',JMSHR0 = as_SHRXM0,JMYY00 = as_SHRYY0 where MXID00 in (select A.MXID00 from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0 ) and CXBZ00='Z'; --已冲销 select SF_SF_TYZD00('发送外部接口数据','Open') into LS_FSWBJKSJ from dual; if LS_FSWBJKSJ='Y' then for V_YJYW00 in (select * from YJ_YW0000 where CXDJH0=ad_CXDJH0) loop --select * into V_YJYW00 from YJ_YW0000 where CXDJH0=ad_YJDJH0; begin execute immediate 'begin SP_JK_INSJKB0('||'1'||','||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 loop; end if; --COMMIT,由于有医保接口,所以事务放在Delphi中控制 EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统没有找到可冲销的单据号,操作失败！'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'@出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_TKJZ00_YJREG0('||as_XM0000||','||as_XB0000||','||','||to_char(ad_CXDJH0)||','||to_char(ad_CZY000)||','|| as_CZYXM0||','||to_char(ad_CZYKS0)||','||'as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; END;