prompt 32、住院病人医技项目更正处理存储过程 SP_ZY_YJGZ00_YJD000 create or replace procedure SP_ZY_YJGZ00_YJD000 ( ad_YJDJH0 IN NUMBER, --冲销单的单据号 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 ZY_BRFY00.DJH000%TYPE; ls_JZDH00 ZY_BRFY00.JZDH00%TYPE; ls_ZYH000 YJ_YW0000.ZYHGHH%TYPE; ls_ZYID00 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_TMPDJH YJ_YW0000.SFDJH0%TYPE; ls_YJKSBH YJ_YW0000.YJKSBH%TYPE; --医技科室,执行科室 ls_KDYS00 YJ_YW0000.KDYS00%TYPE; --开单医生编号 ls_ZXYS00 yj_yw0000.ZXYS00%TYPE;--执行医生编号 ls_KDKSBH YJ_YW0000.KDKSBH%TYPE; --开单科室编号 ls_KDBQ00 YJ_YW0000.KDBQ00%TYPE; --开单病区编号 ls_YSSZKS YJ_YW0000.YSSZKS%TYPE; --医生所在科室 ls_ZFBL00 YJ_YWJJ00.ZFBL00%TYPE; --自付比例 ls_YEXM00 YJ_YW0000.YEXM00%TYPE; --婴儿姓名 ls_ZLDCCF YJ_YW0000.ZLDCCF%TYPE; --诊疗带出处方号 ls_cflsh0 yf_zycf00.cflsh0%TYPE; --处方号 ls_cfNum number(2) ; --重新处方数量 ls_FBBH00 ZY_BRXXB0.FBBH00%TYPE; ls_LBBH00 ZY_BRXXB0.LBBH00%TYPE; ls_SSDH00 SS_SSYJD0.SSDH00%type; ls_YHMSG0 varchar2(200); ls_SYSMSG varchar2(200); lsSSHJSFQYGZHCJK XT_XTCS00.VALUE0%type; --lvMXID00 SF_FYMX00.MXID00%type; Ecustom exception; lvSFSFCG number; lsSFJGSM varchar2(250); ls_COUNT0 number; LS_BRDQKS ZY_FYMX00.BRDQKS%type; -- Person Date Comments -- jlg 2007.07.16 增加传入参数ad_YJDJH0 ; -- jlg 2007.10.24 修改四舍五入的问题ZYSF-20071023-002 -- qks 2008.11.24 恢复Update ZY_FYMX00 set CXBZ00 = '+'原先的写法,由于YJ_YWJJ00.MXID00值为空引起 -- csf 2009.07.17 yj_yw0000有值的时候，冲销时应优先取该值，然后才取zxr000 -- qks 2009.10.23 插入zy_fymx00表时增加(flag00,djid00)字段,值为(1,YJ_YWJJ00.ID0000) -- chenqw 2010.01.18 恢复Update ZY_FYMX00 set CXBZ00 = '+'原先的写法,因为可能同一个单据号会有多条相同的XMBH00的记录 -- CSF 2010.03.12 包床记录(zy_fymx00.xmmc00含"(包)"字样),冲销时候将正的记录中的“(包)”冲销 -- zhangyc 2011.03.15 更正时往ZY_BRFY00,ZY_FYMX00插入QZFJE0 -- zhangwz 2011.06.17 相关业务表增加YEXM00数据的插入. -- zhangyc 2012.03.31 by ZYSF-20120322-001 修正科室领药处方(诊疗项目带出处方),更正该项目时同时更正处方 -- liuj 2012.11.28 被冲销医技单状态为2已收费未执行时冲销单状态不能设置成3已执行 for YJ-20121119-002 -- dsm 2015.09.02 增加调用SP_SS_CreateSSYWMX for SSMZ-20150827-001 -- daihq 2016.04.11 增加参数SS_SSHJSFQYGZHCJK控制，是否启用智业高值耗材接口 for SSMZ-20160406-001 -- daihq 2016.05.12 修正SSMZ-20160406-001改后出现的问题 for MZSF-20160512-003 --zhangyc 2017.03.23 增加执行过程 SP_ZY_UPDATE_DBZFY0 by ZYSF-20170324-002 -- dsm 2018.05.18 如果有优惠的更正时也要处理 for ZYSF-20171020-001 -- zhuyr 2019.01.24 增加判断，如果冲销的费用已经发送过日清单，就不让冲销 by ZYSF-20190122-003 -- dsm 2019.06.15 ZY_FYMX00.BRDQKS写入住院科室 for YJ-20190615-001 -- dsm 2019.10.17 ZFFSBZ也要写入 for ZYSF-20190919-001 BEGIN ls_cfNum:=0; select SFDJH0, XMZT00, YJKSBH, KDYS00, KDKSBH, YSSZKS, ZYHGHH, ZYGHID, BRID00, BRXM00,NVL(KDBQ00,0), nvl(ZXYS00,KDYS00) into ls_SFDJH0,ls_XMZT00,ls_YJKSBH,ls_KDYS00,ls_KDKSBH,ls_YSSZKS,ls_ZYH000,ls_ZYID00,ls_BRID00,ls_XM0000,ls_KDBQ00,ls_ZXYS00 from YJ_YW0000 where YJDJH0 = ad_CXDJH0 and MZZYBZ = '1'; --住院 if ls_SFDJH0 = 0 then as_YHMSG0:='病人被冲的医技单据尚未记账,请先记账后再进行本操作!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; --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 ZLDCCF into ls_ZLDCCF from YJ_YW0000 where YJDJH0 = ad_YJDJH0 and MZZYBZ = '1'; --住院 --先加个判断,若病人被冲的单据号已结算过,暂时不做处理! select JZDH00 into ls_JZDH00 from ZY_BRFY00 where DJH000 = ls_SFDJH0; if ls_JZDH00 <> 0 then as_YHMSG0:='病人被冲的处方号已结算,本过程尚未处理!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; --加个判断,若病人被冲的单据号已发送过日清单,暂时不做处理! --select count(*) into ls_COUNT0 from ZY_BRFY00 where DJH000 = ls_SFDJH0 and YBFSDH <> 0;--修改前20200529 select count(*) into ls_COUNT0 from ZY_BRFY00 where DJH000 = ls_SFDJH0 and YBFSDH <> '0'; if ls_COUNT0 > 0 then as_YHMSG0:='病人被冲的处方号已发送过日清单,请冲销日清单之后再进行退费操作!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; BEGIN select FBBH00,LBBH00 into ls_FBBH00,ls_LBBH00 from ZY_BRXXB0 where ZYID00 = ls_ZYID00; EXCEPTION WHEN NO_DATA_FOUND THEN ls_FBBH00 := 1; --当作自费处理 ls_LBBH00 := 2; --住院预交金 END; select NVL(max(SFDJH0),0) into ls_TMPDJH from YJ_YW0000 where YJDJH0=ad_YJDJH0 AND CXDJH0 = ad_CXDJH0 ; if ls_TMPDJH <> 0 then as_YHMSG0:='病人被冲的医技单号已冲销!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; select SQ_ZY_BRJFB0_DJH000.nextval into ls_DJH000 from dual; select YEXM00 into ls_YEXM00 from YJ_YW0000 where YJDJH0=ad_YJDJH0; --修改医记表中的相关信息 if ls_XMZT00='3' then UPDATE YJ_YW0000 SET XMZT00 = '3', --已执行 SFDJH0 = ls_DJH000 -- WHERE YJDJH0 in ( select YJDJH0 from YJ_YW0000 where CXDJH0 = ad_CXDJH0 ); where YJDJH0 = ad_YJDJH0; else UPDATE YJ_YW0000 SET XMZT00 = '2', --已收费 SFDJH0 = ls_DJH000 where YJDJH0 = ad_YJDJH0; end if; select BRDQKS into LS_BRDQKS from ZY_FYMX00 where DJH000=ls_SFDJH0 and rownum=1; --添加新的费用明细 --select SQ_ZY_FYMX00_MXID00.NEXTVAL into lvMXID00 from dual; 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,BRDQKS,ZFFSBZ) select SQ_ZY_FYMX00_MXID00.NEXTVAL,ls_DJH000,B.SFXMID,B.XMMC00,'次',DECODE(B.DW0000,NULL,'次',B.DW0000),JJJE00,A.SFCS00*A.SL0000, Round(NVL(JJJE00*A.SFCS00*A.SL0000,0),2),NVL(ZFJE00,0),NVL(GFJE00,0),NVL(JZJE00,0),NVL(QZFJE0,0), to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), ls_KDKSBH,ls_KDYS00,ls_YJKSBH,ls_ZXYS00,'0',ls_YSSZKS,'-',ls_KDBQ00,as_SHRXM0,as_SHRYY0,A.ZFBL00,A.GJBM00,A.SFLB00,'1',A.ID0000,ls_YEXM00,LS_BRDQKS ,A.ZFFSBZ from YJ_YWJJ00 A,BM_YYSFXM B where A.SFXMID = B.SFXMID --and A.YJDJH0 in ( select YJDJH0 from YJ_YW0000 C where C.CXDJH0 = ad_CXDJH0 ); and A.YJDJH0 = ad_YJDJH0; --如果原来有优惠的也要冲回来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,BRDQKS,ZFFSBZ) select SQ_ZY_FYMX00_MXID00.NEXTVAL,ls_DJH000,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,BRDQKS ,a.ZFFSBZ from ZY_FYMX00 a where a.MXID00 in(select YHMXID from YJ_YWJJ00 b,ZY_FYYHMX c where YJDJH0=ad_YJDJH0 and b.MXID00=c.MXID00 and c.YXBZ00='1'); update ZY_FYYHMX set YXBZ00='0',CXBZ00='-' where MXID00 in(select MXID00 from YJ_YWJJ00 where YJDJH0=ad_YJDJH0 and YXBZ00='1') ; --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 ZY_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 = ad_YJDJH0 and A.WZDWM0 is not null ) loop SP_SS_GZHCJK(VR.SFXMID,1,VR.WZDWM0,VR.GZJLH0,'1',ls_ZYID00,ls_ZYH000,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; --添加费用主表的信息 Insert into ZY_BRFY00(DJH000,YBDJH0,ZYH000,ZYID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00,QZFJE0, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00,SFYKBZ,YEXM00,DBZCWF,DBZHCF) select ls_DJH000,'0',ls_ZYH000,ls_ZYID00,ls_LBBH00,ls_BRID00,ls_XM0000,ls_FBBH00,NVL(sum(HJJE00),0),NVL(sum(ZFJE00),0),NVL(sum(JZJE00),0),NVL(sum(GFJE00),0),0,NVL(sum(QZFJE0),0), to_char(SYSDATE,'YYYYMMDD'),to_char(SYSDATE,'HH24:MI:SS'),ad_CZY000,as_CZYXM0,ad_CZYKS0,0,ls_SFDJH0,as_CZYXM0,'医技单冲销','2',ls_YEXM00,sum(nvl(DBZCWF,0)),sum(nvl(DBZHCF,0)) from ZY_FYMX00 where DJH000 = ls_DJH000; --Update ZY_FYMX00 --Update ZY_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 ZY_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); -- Update ZY_FYMX00 set CXBZ00 = '+' where MXID00 in ( select A.MXID00 from YJ_YWJJ00 A where YJDJH0 = ad_YJDJH0 ); --Update ZY_FYMX00 set CXBZ00 = '+' where DJH000 = ls_SFDJH0 --2008.11.24 -- and XMBH00 in ( select A.SFXMID from YJ_YWJJ00 A where A.YJDJH0=ad_YJDJH0); Update ZY_FYMX00 set CXBZ00 = '+',XMMC00=replace(XMMC00,'(包)','') where MXID00 in ( select A.MXID00 from YJ_YWJJ00 A,YJ_YW0000 B where A.YJDJH0=B.YJDJH0 and B.CXDJH0=ad_CXDJH0); ----更正科室领药药品冲销---- select count(1)Num into ls_cfNum from yf_zycf00 where brid00=ls_BRID00 and INSTR(','||ls_ZLDCCF||',',','||TRIM(cflsh0)||',')>0 and SFKSLY='Y' AND DJH000+0=0; if ls_cfNum>=1 then Declare Cursor C_CXKSCF is select DISTINCT BCCFH0 from yf_zycf00 where brid00=ls_BRID00 and SFKSLY='Y' AND DJH000+0=0 and INSTR(','||ls_ZLDCCF||',',','||TRIM(cflsh0)||',')>0 ; begin FOR cx IN C_CXKSCF LOOP ls_cflsh0:=cx.BCCFH0; SP_ZY_BRTF00_CFD000(ls_cflsh0,ad_CZY000,as_CZYXM0,ad_CZYKS0,as_SHRXM0,as_SHRYY0,ls_YHMSG0,ls_SYSMSG); end loop; end; end if; select nvl(max(SSDH00),0) into ls_SSDH00 from SS_SSYJD0 where YJDJH0 in(select CXDJH0 from YJ_YW0000 where YJDJH0=ad_YJDJH0) and SSDH00 in (select SSDH00 from SS_YW0000 where ZYGHID=ls_ZYID00); if ls_SSDH00>0 then begin SP_SS_CreateSSYWMX(ls_SSDH00); exception when others then ls_SSDH00:=-1; end; 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_ZY_YJGZ00_YJD000('||to_char(ad_CXDJH0)||','||to_char(ad_CZY000)||','||as_CZYXM0||','||to_char(ad_CZYKS0)||',as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; END; / show error;