CREATE OR REPLACE PROCEDURE SP_ZY_LJ_BRRJ_ZYDZBL( ad_ZYID00 in number, --病人住院ID ad_BRID00 in number, --病人ID ad_LJID00 in number, --路径ID ad_RCID00 in number, --开始日程ID ad_CJRBH0 in number, --创建人编号 ad_XGRBH0 in number, --修改人编号 ad_CZLX00 in number, -- 0 如已存在的路径则做退出 1 忽略已存在的路径 ad_JSYY00 in varchar2, -- 结束原因 ad_KSTS00 in number default 1, --路径中开始日 as_KSRQ00 in char, --开始日期，这个涉及是不是要已经发生的作自动匹配 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_ZLJBZ0 in varchar2 default '0' --分支路径标志 ) as -- Person Date Comments -- rbh 2014.12.10 CREATE DZBL-20141210-007 -- xuxm 2015.12.11 取消费用整理 DZBL-20151211-001 -- wuzy 2018.06.06 过程加上日志 SP_TransLog for ZYDZBL-20180531-002 -- ------ -------------- -------------------------------------------- ls_zyr000 LJ_MBZLJD.ZYR000%TYPE; ls_YPGG00 BM_YD0000.YPGG00%TYPE; ls_LJID00 LJ_MB0000.LJID00%type; ls_count0 number(3); ls_ZRZCID number; ls_ksbh00 number(5,0); ls_ypyzcou number(5); ls_yjyzcou number(5); err_Error exception; ls_YHMSG0 varchar(100); ls_SYSMSG varchar(100); ls_error varchar(200); ls_BRZXJD LJ_JDZXQK.BRZXJD%type; -- ls_ZRZCID LJ_ZRZC00.ZRZCID%type; -- ls_LJID00 LJ_ZRZC00.LJID00%type; ls_KSRQ00 LJ_ZRZC00.KSRQ00%type; ls_id0000 LJ_BRZLJ0.ID0000%type; i number(5); cursor c_ypyz is select YZID00 from BQ_YPYZ00 A where ZYID00=ad_ZYID00 and ( (QYRQ00>=as_KSRQ00 and CLBZ00='1') or (TZRQ00 is null and CLBZ00='0')) AND exists(select 1 from BQ_YPYZMX WHERE YZID00=A.YZID00 AND nvl(LJID00,0)=0); cursor c_yjyz is select YZMXID from BQ_YJYZ00 where ZYID00=ad_ZYID00 and ( (QYRQ00>=as_KSRQ00 and CLBZ00='1') or (TZRQ00 is null and CLBZ00='0')) AND NVL(ljid00,0)=0; begin SP_TRANSLOG(sysdate,'SP_ZY_LJ_BRRJ_ZYDZBL',ad_CJRBH0,0, 'SP_ZY_LJ_BRRJ_ZYDZBL:ad_ZYID00:'||To_Char(ad_ZYID00)||'ad_BRID00:'||ad_BRID00||'ad_LJID00:'||ad_LJID00||'ad_RCID00:'||ad_RCID00); select count(1) into ls_count0 from LJ_ZRZC00 where LJID00=ad_LJID00 and ZYID00=ad_ZYID00 and JSRQ00 is null; if ls_count0>0 and as_ZLJBZ0='2' then return; end if; if ls_count0>0 then --如已存在的路径 写入结束日期 if ad_CZLX00=0 then --取LJ_ZRZC00的ZRZCID begin select nvl(ZRZCID,0) into ls_ZRZCID from LJ_ZRZC00 where LJID00=ad_LJID00 and ZYID00=ad_ZYID00 and JSRQ00 is null; exception when others then ls_ZRZCID:=0; end; /* begin SP_ZY_LJYZFYMX_BR_ZYDZBL (ad_zyid00,ls_zrzcid); exception when others then as_YHMSG0:='费用整理失败！'; raise err_Error; end; */ update LJ_ZRZC00 set JSRQ00=to_char(sysdate,'YYYYMMDD'),JSSJ00=to_char(sysdate,'HH24:MI:SS'),JSYY00=ad_JSYY00,JSFS00='强制退出', ZJE000=(select sum(JE0000) from LJ_YZFYMX where ZRZCID=ls_zrzcid), SJTS00=(to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(KSRQ00,'YYYYMMDD'))+1, SFBY00='1' where LJID00=ad_LJID00 and ZYID00=ad_ZYID00 and JSRQ00 is null; update LJ_JDZXQK set SFJXZX='1',ZXJSRQ=to_char(sysdate,'YYYYMMDD'),XGRBH0=ad_XGRBH0 where ZRZCID=ls_ZRZCID and JDZT00='0' and ZYID00=ad_ZYID00 and ZXJSRQ is null and nvl(SFJXZX,'0')<>'1'; update LJ_BRZLJ0 set JSRQ00=to_char(sysdate,'YYYYMMDD'),JSSJ00=to_char(sysdate,'HH24:MI:SS'),JSYY00=ad_JSYY00,JSFS00='强制退出', ZJE000=(select sum(JE0000) from LJ_YZFYMX where ZRZCID=ls_zrzcid), SJTS00=(to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(KSRQ00,'YYYYMMDD'))+1 where ZRZCID=ls_ZRZCID and ZYID00=ad_ZYID00 and JSRQ00 is null; begin select nvl(LJID00,0) into ls_LJID00 from LJ_ZRZC00 where JSRQ00 is null and ZYID00=ad_ZYID00 and rownum=1; exception when others then ls_LJID00:=0; end; --病人是否存在其他路径 if ls_LJID00>0 then update ZY_BRXXB0 set DQLJID=ls_LJID00 where ZYID00=ad_ZYID00; else update ZY_BRXXB0 set DQLJID=null where ZYID00=ad_ZYID00; end if; --修改LJ_YZMX00 if ls_ZRZCID>0 then update LJ_YZMX00 set SFBY00='1',BYYY00=ad_JSYY00,XGRQ00=to_char(sysdate,'YYYYMMDD'),XGSJ00=to_char(sysdate,'HH24:MI:SS') where ZRZCID=ls_ZRZCID and ZYID00=ad_ZYID00 and LJID00=ad_LJID00; end if; end if; elsif ad_CZLX00=1 then --写入临床路径准入准出 select DQKS00 into ls_ksbh00 from zy_brxxb0 where ZYID00=ad_ZYID00; if as_ZLJBZ0='0' then select SQ_LJ_ZRZC00_ZRZCID.NextVal into ls_ZRZCID from dual; insert into LJ_ZRZC00(ZRZCID,ZYID00,BRID00,LJID00,KSRQ00,KSSJ00,KSRCJD,ZRRBH0,KSBH00,KSTS00 ,VER000,MINTS0,MAXTS0,SSZD00,SSZDMS,GJTS00,GJJE00,ZLJID0,LJMC00,ZDZXJD,ZYFYSX,SSLX00,LJLX00) select ls_ZRZCID,ad_ZYID00,ad_BRID00,ad_LJID00,as_KSRQ00,to_char(sysdate,'HH24:MI:SS'),ad_RCID00,ad_CJRBH0,ls_ksbh00,ad_KSTS00 ,VER000,MINTS0,MAXTS0,SSZD00,SSZDMS,ZYTS00,ZYFY00,ZLJID0,LJMC00,nvl(ZDZXJD,0),ZYFYSX,SSLX00,LJLX00 from LJ_MB0000 where LJID00=ad_LJID00 and exists(select count(1) from LJ_MBZLJD where LJID00=ad_LJID00 and JDZT00<>'1'); else select ZRZCID,LJID00,KSRQ00 into ls_ZRZCID,ls_LJID00,ls_KSRQ00 from LJ_ZRZC00 where ZYID00=ad_ZYID00 and JSRQ00 is null; update LJ_ZRZC00 set LJID00=ad_LJID00 where ZYID00=ad_ZYID00 and JSRQ00 is null; end if; --同时写入子路径表 select SQ_LJ_BRZLJ0_ID0000.nextval into ls_id0000 from dual; insert into LJ_BRZLJ0(ZRZCID,ZYID00,BRID00,LJID00,KSRQ00,KSSJ00,KSRCJD,ZRRBH0,KSBH00,KSTS00 ,VER000,MINTS0,MAXTS0,SSZD00,SSZDMS,GJTS00,GJJE00,ZRYY00,LJMC00,ID0000,ZLJBZ0,SSLX00,LJLX00) select ls_ZRZCID,ad_ZYID00,ad_BRID00,ad_LJID00,as_KSRQ00,to_char(sysdate,'HH24:MI:SS'),ad_RCID00,ad_CJRBH0,ls_ksbh00,ad_KSTS00 ,VER000,MINTS0,MAXTS0,SSZD00,SSZDMS,ZYTS00,ZYFY00,'',LJMC00,ls_id0000,as_ZLJBZ0,SSLX00,LJLX00 from LJ_MB0000 where LJID00=ad_LJID00 and exists(select count(1) from LJ_MBZLJD where LJID00=ad_LJID00 and JDZT00<>'1'); --更新ZY_BRZZB0 update ZY_BRXXB0 set DQLJID=ad_LJID00 where ZYID00=ad_ZYID00 and nvl(DQLJID,0)=0; ls_zyr000:=0; if ad_RCID00>0 then select ZYR000 into ls_zyr000 from LJ_MBZLJD where LJID00=ad_LJID00 and RCID00=ad_RCID00; end if; --如果是分支路径则把之前路径的没有执行的阶段设置成无效 if as_ZLJBZ0='2' then update LJ_JDZXQK set JDZT00='1' where ZRZCID=ls_ZRZCID and ZLJBZ0='0' and ZXKSRQ>to_char(sysdate,'YYYYMMDD'); update LJ_ZRZC00 set LJID00=ad_LJID00,(GJTS00,ZYFYSX,GJJE00,MINTS0,MAXTS0,SSLX00)=(select ZYTS00,ZYFYSX,ZYFY00,MINTS0,MAXTS0,SSLX00 from LJ_MB0000 where LJID00=ad_LJID00) where ZRZCID=ls_ZRZCID ; ---把原来的路径结束 update LJ_BRZLJ0 set JSRQ00=to_char(sysdate,'YYYYMMDD'),JSSJ00=to_char(sysdate,'HH24:MI:SS') where ZRZCID=ls_ZRZCID and LJID00=ls_LJID00; select BRZXJD into ls_BRZXJD from LJ_JDZXQK where ZRZCID=ls_ZRZCID and DQJDBZ='1' and ZLJBZ0='0'; update LJ_YZMX00 set YXBZ00='0',BYYY00='转入分支路径' where ZRZCID=ls_ZRZCID and LJID00=ls_LJID00 and KSRQ00>=to_char(sysdate,'YYYYMMDD') and nvl(WBYZID,0)=0 and BRZXJD<>ls_BRZXJD; update LJ_KJMX00 a set YXBZ00='0' where ZRZCID=ls_ZRZCID and LJID00=ls_LJID00 and KSRQ00>=to_char(sysdate,'YYYYMMDD') and BRZXJD<>ls_BRZXJD and not exists(select 1 from LJ_KJMXFJ where KJMXID=a.KJMXID and ZXBZ00='1' ); end if; --写入节点数据 if as_ZLJBZ0='0' then insert into LJ_JDZXQK(ZRZCID,BRZXJD,ZYID00,BRID00,LJID00,RCID00,JDLX00,RYTS00, JDMC00,JHRQ00,LCBGLX,CJRBH0 ,ZXKSRQ,JDZT00,ZLTS00,SFGJJD,ZXJSRQ,JHZLTS ,MINKSR ,MAXKSR ,GLRCID,KTZBZ0,ZDZXJD,XH0000,ZLJBZ0,ID0000,YCTCBZ) select ls_ZRZCID,SQ_LJ_JDZXQK_BRZXJD.nextval,ad_ZYID00,ad_BRID00,ad_LJID00,RCID00,JDLX00,ZYR000, JDMC00,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000 -1,'YYYYMMDD'),0,ad_CJRBH0, to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),JDZT00,ZLTS00,SFGJJD, decode(ZLTS00,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + (ZYR000 -1),'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + (ZYR000 -1)+(ZLTS00-1),'YYYYMMDD')),ZLTS00 ,decode(MINTS0,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MINTS0-1,'YYYYMMDD')) ,decode(MAXTS0,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MAXTS0-1,'YYYYMMDD')) ,GLRCID ,KTZBZ0,ZDZXJD,XH0000,'0',ls_id0000,YCTCBZ from (select * from LJ_MBZLJD where LJID00=ad_LJID00 and JDZT00<>'1' and JDZT00<>'2' and ZYR000>=ad_KSTS00 and exists(select 1 from LJ_ZRZC00 where LJID00=ad_LJID00 and ZYID00=ad_ZYID00 and JSRQ00 is null) order by xh0000); else--分支路径 select to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(ls_KSRQ00,'YYYYMMDD')+1 into ls_ZYR000 from dual; insert into LJ_JDZXQK(ZRZCID,BRZXJD,ZYID00,BRID00,LJID00,RCID00,JDLX00,RYTS00, JDMC00,JHRQ00,LCBGLX,CJRBH0 ,ZXKSRQ,JDZT00,ZLTS00,SFGJJD,ZXJSRQ,JHZLTS ,MINKSR ,MAXKSR ,GLRCID,KTZBZ0,ZDZXJD,XH0000,ZLJBZ0,ID0000,YCTCBZ) select ls_ZRZCID,SQ_LJ_JDZXQK_BRZXJD.nextval,ad_ZYID00,ad_BRID00,ad_LJID00,RCID00,JDLX00,ZYR000, JDMC00,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000 -1,'YYYYMMDD'),0,ad_CJRBH0, to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),JDZT00,ZLTS00,SFGJJD, decode(ZLTS00,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + (ZYR000 -1),'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + (ZYR000 -1)+(ZLTS00-1),'YYYYMMDD')),ZLTS00 ,decode(MINTS0,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MINTS0-1,'YYYYMMDD')) ,decode(MAXTS0,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + ZYR000-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MAXTS0-1,'YYYYMMDD')) ,GLRCID ,KTZBZ0,ZDZXJD,XH0000,'0',ls_id0000,YCTCBZ from (select * from LJ_MBZLJD where LJID00=ad_LJID00 and JDZT00<>'1' and JDZT00<>'2' and ZYR000+ZLTS00-1>=ad_KSTS00 and exists(select 1 from LJ_ZRZC00 where LJID00=ad_LJID00 and ZYID00=ad_ZYID00 and JSRQ00 is null) order by xh0000); i:=1; for cFZJD in (select BRZXJD,ZRZCID,ZLTS00,RYTS00 from LJ_JDZXQK where ZRZCID=ls_ZRZCID and LJID00=ad_LJID00 order by XH0000) loop if i=1 then ls_ZYR000:=ls_ZYR000; update LJ_JDZXQK set RYTS00=ls_ZYR000,ZLTS00=ZLTS00-(ad_KSTS00-cFZJD.RYTS00) where BRZXJD=cFZJD.BRZXJD; ls_ZYR000:=ls_ZYR000+cFZJD.ZLTS00-(ad_KSTS00-cFZJD.RYTS00); else update LJ_JDZXQK set RYTS00=ls_ZYR000 where BRZXJD=cFZJD.BRZXJD; ls_ZYR000:=ls_ZYR000+cFZJD.ZLTS00; end if; i:=i+1; end loop; update LJ_JDZXQK a set JHZLTS=ZLTS00 , JHRQ00=to_char(to_date(as_KSRQ00,'YYYYMMDD') + RYTS00 -1,'YYYYMMDD'), ZXKSRQ=to_char(to_date(as_KSRQ00,'YYYYMMDD') + RYTS00 -1,'YYYYMMDD'), ZXJSRQ=decode(ZLTS00,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + (RYTS00 -1),'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + (RYTS00 -1)+(ZLTS00-1),'YYYYMMDD')), MINKSR=(select decode(MINTS0,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + RYTS00-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MINTS0-1,'YYYYMMDD')) from LJ_MBZLJD where RCID00=a.RCID00), MAXKSR=(select decode(MAXKSR,0,to_char(to_date(as_KSRQ00,'YYYYMMDD') + RYTS00-1,'YYYYMMDD'),to_char(to_date(as_KSRQ00,'YYYYMMDD') + MAXTS0-1,'YYYYMMDD')) from LJ_MBZLJD where RCID00=a.RCID00) where ZRZCID=ls_ZRZCID and LJID00=ad_LJID00 ; end if; --更新节点状态 if as_ZLJBZ0='0' then update LJ_JDZXQK set JDZT00='1' where ZYID00=ad_ZYID00 and LJID00=ad_LJID00 and RYTS00=to_char(sysdate,'YYYYMMDD') and JDZT00='0' and nvl(SFJXZX,'0')='0'; end if; --生产医嘱明细计划 select count(*) into ls_ypyzcou from BQ_YPYZ00 where ZYID00=ad_ZYID00; select count(*) into ls_yjyzcou from BQ_YJYZ00 where ZYID00=ad_ZYID00; --写入当前阶段标志 if as_ZLJBZ0='2' then update LJ_JDZXQK set DQJDBZ='2',ZXJSRQ=to_char(sysdate,'YYYYMMDD'),ZLTS00=to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')-to_date(ZXKSRQ,'YYYYMMDD') where ZRZCID=ls_ZRZCID and ZLJBZ0='0' and DQJDBZ='1'; end if; update LJ_JDZXQK set DQJDBZ='1' where ZRZCID=ls_ZRZCID and ZLTS00>0 and nvl(DQJDBZ,'0')='0' and (to_char(sysdate,'YYYYMMDD') between ZXKSRQ and ZXJSRQ) and rownum=1; SP_ZY_LJ_INIBRJDYZ_ZYDZBL(ad_ZYID00,ad_LJID00,ad_CJRBH0,as_YHMSG0,as_SYSMSG); if (as_YHMSG0 is not null) then raise err_Error; end if; if as_ZLJBZ0='0' then if ls_ypyzcou>0 then --已经发生的作自动匹配 ls_error:='修改药品对应路径' ; for ypyzid in C_ypyz loop SP_ZY_LJ_YZRELATETOLJYZ_ZYDZBL(ad_ZYID00,ypyzid.yzid00,0,ls_YHMSG0,ls_SYSMSG); SP_ZY_LJ_YZADDTOLJYZ_DT_ZYDZBL(ad_ZYID00,ypyzid.yzid00,0,ls_YHMSG0,ls_SYSMSG); ls_YHMSG0:=''; ls_SYSMSG:=''; end loop; end if; if ls_yjyzcou>0 then --医技医嘱 ls_error:='修改医技医嘱对应' ; for yjyzid in C_yjyz loop SP_ZY_LJ_YZRELATETOLJYZ_ZYDZBL(ad_ZYID00,yjyzid.yzmxid,1,ls_YHMSG0,ls_SYSMSG); SP_ZY_LJ_YZADDTOLJYZ_DT_ZYDZBL(ad_ZYID00,yjyzid.yzmxid,1,ls_YHMSG0,ls_SYSMSG); end loop; end if; end if; end if; commit; exception when err_Error then rollback; when others then as_SYSMSG:=SQLERRM; as_YHMSG0:='过程SP_ZY_LJ_BRRJ_ZYDZBL出错！'; Rollback; end SP_ZY_LJ_BRRJ_ZYDZBL;