CREATE OR REPLACE PROCEDURE SP_BQ_YJYZZX_XZ ( Pczy000 in number, --操作员 Pzyid00 in number, --住院流水号 Pbqh000 in number, --病区号 Pyzmxid in char , --医嘱明细串 Pzxrq00 in date , --提取日期 Pbctqid out number, --本次提取ID Pcjksbh in char default '', --采集科室编号 默认 '' Pystjdz in char default 'N' --是否住院医生提交医嘱操作动作 Y 是 ) AS -- MODIFICATION HISTORY -- Person Date Comments -- chenqw 20100113 描述性的医嘱也要填上ZXRQSC，ZXSJSC -- liuj 20100716 限制描述医嘱执行时插入BQ_YJYZLS -- chenyw 20100902 增加过程一个入参数来判断是否是病区在执行医嘱 -- chenyw 2010.11.08 增加参数Pcjksbh采集科室 默认'' -- zhangwz 2011.12.20 增加医嘱预停止时间 YTZSJ1 处理 by BQ-20111207-001. -- zhangwz 2012.05.10 增加参数 Pystjdz 用于处理住院医生医嘱提交非药短嘱的时候直接生成YJ_YW0000表XMZT00=6的数据 by BQ-20120503-002 -- yangy 2013.05.12 描述性医嘱执行选择 -- qks 2014.02.07 Stryzid,Vparams:VARCHAR2(500)-->VARCHAR2(4000) -- zhaoxz 2016.02.19 程瑾反馈，非药医嘱核对报错，未找到数据归档 For BQHS9.0-20160219-002 -- linyx 2018.01.04 省人民模式的停止医嘱（参数BQ_SFKQSRMYZXG=Y），tzys00、tzrxm0不清空 for BQHS9.0-20180102-001 -- linyx 2018.07.24 预停止医嘱模式（参数BQ_SFQYYTZ=Y），tzys00、tzrxm0，tzczrq、tzczsj不清空 for BQHS9.0-20180712-001 -- xuxm 2019.06.26 修改描述性医嘱可以执行 for BQHS9.0-20190625-014 -- ruanbh 2019.06.27 修改描述性医嘱可以扣费 BQHS9.0-20190627-004 Counter NUMBER; I NUMBER(3); J NUMBER(3); Init NUMBER(3); Stryzid VARCHAR2(4000); Vparams VARCHAR2(4000); Vczyxm0 BM_YGBM00.ZWXM00%TYPE; Vsl0000 YJ_YW0000.SL0000%TYPE; Vyjdjh0 YJ_YW0000.YJDJH0%TYPE; Vyjfzh0 YJ_YW0000.YJFZH0%TYPE; Vyzmxid BQ_YJYZLS.YZMXID%TYPE; Vzlxmid BM_ZLZD00.ZLXMID%TYPE; Vmcsl00 BQ_YJYZLS.MCSL00%TYPE; Vcs0000 BQ_YJYZLS.CS0000%TYPE; Vpcid00 BM_PC0000.PCID00%TYPE; Vhszqcd BM_PC0000.HSZQCD%TYPE; Vqyrqsj BQ_YJYZLS.QYRQSJ%TYPE; Vtqrqsj BQ_YJYZLS.TQRQSJ%TYPE; Vjzsj00 CHAR(16); Vqsrqsj CHAR(16); Vzxks00 BM_BMBM00.BMBH00%TYPE; Vyzcllb BQ_YJYZLS.YZCLLB%TYPE; Vksh000 BM_BMBM00.BMBH00%TYPE; Vjflbid BM_BRJFLB.JFLBID%TYPE; --计费类别(帐户类别) Vreturn NUMBER(10) ; --函数的返回值 Vjzrqsj DATE; --本次医嘱提取截至日期(含时间) Vsysdate DATE; Vyzsjfg CHAR(8) ; --医嘱时间分割点 E_Mytqsj exception; --没有提取日期，无法提取 E_Yzsjfgcscw exception; --系统参数:医嘱时间分割点错误 E_Myyzzx exception; --没有医嘱可以提取; E_Mycfjl exception; --数据不完整，部分医嘱没有拆分记录; E_Hlxmcw exception; --护理项目错误 E_Zhlbcscw exception; --帐户类别参数错误 E_Yzzndttq exception; --医嘱只能当天提取 E_Cfqwt exception; --医嘱拆分的触发器问题 E_Cscw exception; --参数错误 Vcyrqsj DATE; --病人预出院日期和时间 Vmyyzzx CHAR(1); --没有医嘱执行 Vwhtqts NUMBER(5); --医嘱允许往后提取的天数 Vminirq BQ_YJYZ00.QYRQ00%TYPE; Vmaxmrq BQ_YJYZ00.QYRQ00%TYPE; Vbrxm00 ZY_BRXXB0.XM0000%TYPE; --病人姓名 Vbrch00 ZY_BRXXB0.RYCWH0%TYPE; --床位号 Vyzly00 BQ_YJYZ00.YZLY00%TYPE; --医嘱来源 Vsrmyztz XT_XTCS00.VALUE0%TYPE;--省人民医嘱停止模式 Vsfqyytz XT_XTCS00.VALUE0%TYPE;--是否启用预停止 BQ_MSXYZKYKF XT_XTCS00.VALUE0%TYPE;--描述性医嘱可以扣费，Y是，N否，默认N CURSOR C_YEYZ00 IS SELECT YEXM00,COUNT(*) YZSL00 FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00 IS NOT NULL and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) GROUP BY YEXM00 HAVING COUNT(*)>1 ; BEGIN VSYSDATE:=SYSDATE; IF (NVL(Pzyid00,0)<=0) OR (NVL(Pbqh000,0)<=0) OR (Pzxrq00 IS NULL) OR (NVL(Pczy000,0)<=0) OR (Pyzmxid IS NULL) THEN RAISE E_CSCW;--参数错误 END IF; VPARAMS:=substrb('SP_BQ_YJYZZX_XZ('||NVL(TO_CHAR(Pczy000),'NULL')||','||NVL(TO_CHAR(Pzyid00),'NULL')||','|| NVL(TO_CHAR(Pbqh000),'NULL')||','||Pyzmxid||','||NVL(TO_CHAR(Pzxrq00,'YYYYMMDD'),'NULL')||',Pbctqid)',1,4000); select XM0000,RYCWH0 into VBRXM00,VBRCH00 from ZY_BRXXB0 where zyid00 = Pzyid00; --限定往后提取的天数 select to_number(nvl(max(VALUE0),9999)) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BR'; --查询yzly00='3'入院中心的医嘱 --select yzly00 into Vyzly00 from bq_yjyz00 where yzmxid in (substrb(Pyzmxid,1,decode(instrb(',',Pyzmxid),0,lengthb(Pyzmxid)))) and yzly00='3' and rownum=1; --省人民医嘱停止模式 select nvl(max(trim(VALUE0)),'N') INTO Vsrmyztz from XT_XTCS00 where NAME00 = 'BQ_SFKQSRMYZXG'; select nvl(max(trim(VALUE0)),'N') into Vsfqyytz from XT_XTCS00 where NAME00 = 'BQ_SFQYYTZ'; -- ruanbh 2019.06.27 修改描述性医嘱可以扣费 BQHS9.0-20190627-004 select nvl(max(trim(VALUE0)),'N') into BQ_MSXYZKYKF from XT_XTCS00 where name00='BQ_MSXYZKYKF'; if Pzxrq00 is null then raise E_MYTQSJ;--没有提取时间，无法提取 elsif trunc(Pzxrq00)-trunc(VSYSDATE)>Vwhtqts THEN--提取时间限制 RAISE E_YZZNDTTQ; END IF; SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=Pczy000; SELECT COUNT(VALUE0) INTO COUNTER FROM XT_XTCS00 WHERE NAME00='BQ_FYYZSJFG'; IF COUNTER<>1 THEN RAISE E_YZSJFGCSCW;--系统参数:医嘱时间分割点错误 ELSE SELECT SUBSTR(VALUE0,1,8) INTO VYZSJFG FROM XT_XTCS00 WHERE NAME00='BQ_FYYZSJFG'; END IF; --获得提取的截至日期时间(起始时间为当日) IF Pzxrq00 IS NOT NULL THEN VJZRQSJ:=TO_DATE(TO_CHAR(Pzxrq00,'YYYYMMDD')||VYZSJFG,'YYYYMMDDHH24:MI:SS'); VJZSJ00:=TO_CHAR(VJZRQSJ,'YYYYMMDDHH24:MI:SS');--字符型 END IF; --获得本次提取ID SELECT SQ_BQ_ZBTQYZ_ID0000.NEXTVAL INTO Pbctqid FROM DUAL WHERE ROWNUM=1; delete from BQ_YJYZLS where ID0000=Pbctqid; delete from BM_YPJJB0_TMP where ID0000=Pbctqid; COMMIT; --将药品医嘱ID插入到临时表 STRYZID:=TRIM(Pyzmxid); IF LENGTH(STRYZID)>0 THEN J:=1;--都号基数起 INIT:=1;--起始位 LOOP I:=INSTR(Pyzmxid,',',1,J); IF I=0 THEN--结束 IF J=1 THEN --都没有找到逗号 INSERT INTO BM_YPJJB0_TMP(YZYYP0,ID0000)VALUES(TO_NUMBER(STRYZID),Pbctqid); ELSE --最后一次J>1 INSERT INTO BM_YPJJB0_TMP(YZYYP0,ID0000)VALUES(TO_NUMBER(SUBSTR(STRYZID,INIT,LENGTH(STRYZID)-INIT+1)),Pbctqid); END IF; EXIT;--退出 END IF; counter:=TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT)); INSERT INTO BM_YPJJB0_TMP (YZYYP0,ID0000) VALUES (TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT)),Pbctqid); J:=J+1; INIT:=I+1; --变更起始位置 END LOOP; END IF; --查询yzly00='3'入院中心的医嘱 select yzly00 into Vyzly00 from bq_yjyz00 where YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP where ID0000=Pbctqid) and rownum=1; --检验是否有未按规则拆分的医技医嘱 SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00 Z WHERE Z.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP where ID0000=Pbctqid) and Z.YZZT00 IN ('1','2') and NOT EXISTS (SELECT 1 FROM BQ_YJYZCF C WHERE C.YZMXID=Z.YZMXID); IF COUNTER>0 THEN RAISE E_CFQWT;--医嘱拆分的触发器问题 END IF; --开始计算执行的次数 --预出院病人的出院时间小于提取的截至时间，取预出院时间 SELECT TO_DATE(CYDJRQ||CYDJSJ,'YYYYMMDDHH24:MI:SS') INTO VCYRQSJ FROM ZY_BRXXB0 WHERE ZYID00=Pzyid00; IF VCYRQSJ IS NOT NULL THEN VJZRQSJ:=LEAST(VCYRQSJ,VJZRQSJ); VJZSJ00:=TO_CHAR(VJZRQSJ,'YYYYMMDDHH24:MI:SS');--字符型 END IF; --处理成人的护理医嘱 SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00 IS NULL and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)and ROWNUM=1; IF COUNTER>0 THEN SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00 IS NULL and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)and ROWNUM<4; IF COUNTER>1 THEN RAISE E_HLXMCW;--护理项目错误 ELSIF COUNTER=2 THEN SELECT MIN(QYRQ00),MAX(QYRQ00) INTO VMINIRQ,VMAXMRQ FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00 IS NULL and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM); IF VMINIRQ=VMAXMRQ THEN RAISE E_HLXMCW; ELSE --设置停止日期 SELECT MIN(QSRQ00||QSSJ00)INTO VQSRQSJ FROM BQ_YJYZCF WHERE YZMXID=(SELECT YZMXID FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00 IS NULL and YZZT00 IN ('1','2') and QYRQ00=VMAXMRQ and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)); UPDATE BQ_YJYZ00 SET TZRQ00=SUBSTR(VQSRQSJ,1,8), TZSJ00=SUBSTR(VQSRQSJ,9,8) WHERE ZYID00=Pzyid00 and YEXM00 IS NULL and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) and QYRQ00=VMINIRQ; --改变病人的护理状态 UPDATE ZY_BRXXB0 SET HLJBID=(SELECT ZLXMID FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and YEXM00 IS NULL and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) and QYRQ00=VMAXMRQ) WHERE ZYID00=Pzyid00; END IF; ELSIF COUNTER=1 THEN--更改病人的护理级别 UPDATE ZY_BRXXB0 SET HLJBID=(SELECT ZLXMID FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and YEXM00 IS NULL and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)) WHERE ZYID00=Pzyid00; END IF; --处理婴儿的护理医嘱 FOR YEYZ IN C_YEYZ00 LOOP IF YEYZ.YZSL00>2 THEN RAISE E_HLXMCW;--护理项目错误 ELSIF YEYZ.YZSL00=2 THEN SELECT MIN(QYRQ00),MAX(QYRQ00) INTO VMINIRQ,VMAXMRQ FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00=YEYZ.YEXM00 and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM); IF VMINIRQ=VMAXMRQ THEN RAISE E_HLXMCW; ELSE SELECT MIN(QSRQ00||QSSJ00) INTO VQSRQSJ FROM BQ_YJYZCF WHERE YZMXID=(SELECT YZMXID FROM BQ_YJYZ00 WHERE ZYID00=Pzyid00 and YEXM00=YEYZ.YEXM00 and YZZT00 IN ('1','2') and QYRQ00=VMAXMRQ and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)); --设置停止日期 UPDATE BQ_YJYZ00 SET TZRQ00=SUBSTR(VQSRQSJ,1,8), TZSJ00=SUBSTR(VQSRQSJ,9,8) WHERE ZYID00=Pzyid00 and YEXM00=YEYZ.YEXM00 and YZZT00 IN ('1','2') and ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) and QYRQ00=VMINIRQ; END IF; END IF; END LOOP; END IF; --插入单病人(非本病区执行)的短嘱医技项目 INSERT INTO BQ_YJYZLS ( ID0000, YZMXID, ZYID00, ZLXMID, MCSL00, LBBH00, CLBZ00, PCID00, HSZQCD, ZXKS00, YZCLLB, CS0000, QYRQSJ, TQRQSJ, JZRQSJ ) SELECT Pbctqid,Y.YZMXID, ZYID00, ZLXMID, MCSL00, YZLB00, CLBZ00, PCID00, Y.HSZQCD, ZXKS00, YZCLLB, TS0000,--周期数 F.QSRQSJ, --有效起始日期 F.QSRQSJ, --VJZRQSJ , VJZRQSJ FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F WHERE ZYID00=Pzyid00 and BQH000=Pbqh000 and CLBZ00='1' and (Y.ZLXMID>0 or BQ_MSXYZKYKF='Y') and YZZT00 IN ('1','2') and Y.YZMXID=F.YZMXID and GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ)0 or BQ_MSXYZKYKF='Y') and YZZT00 IN ('1','2') and Y.YZMXID=F.YZMXID and GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ) '3' then --处理没有执行到的医嘱,包括予出院，定期应用，QOD等 --判定是否存在没有执行到的医嘱 SELECT COUNT(*) INTO COUNTER FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F,VW_BQ_ZYBRXX B WHERE Y.ZYID00=B.ZYID00 and Y.YZMXID=F.YZMXID and B.DQBQ00=Pbqh000 and SUBSTR(BRZT00,1,1)='2' and B.ZYID00=Pzyid00 and Y.BQH000=Pbqh000 and Y.ZYID00=Pzyid00 and Y.YZZT00 IN ('1','2') and NVL(Y.CLRQSJ,Y.QYRQSJ)0 THEN --修改医嘱的提取时间,定期应用的医嘱要自动停止 UPDATE BQ_YJYZ00 Z SET TQRQ00=(SELECT DECODE(P.ZQDW00,'小时',TQRQ00,TO_CHAR(Pzxrq00,'YYYYMMDD')) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), TQSJ00=(SELECT DECODE(P.ZQDW00,'小时',TQSJ00,TRIM(VYZSJFG)) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), YZZT00=DECODE(CLBZ00,'1','3','0',DECODE(TZRQ00,NULL,'2',DECODE(SIGN(VJZRQSJ-TO_DATE(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3'))) WHERE ZYID00=Pzyid00 and BQH000=Pbqh000 and CLBZ00='0' and YZMXID IN (SELECT Y.YZMXID FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F,VW_BQ_ZYBRXX B WHERE Y.ZYID00=B.ZYID00 and Y.YZMXID=F.YZMXID and B.DQBQ00=Pbqh000 and SUBSTR(BRZT00,1,1)='2' and B.ZYID00=Pzyid00 and Y.BQH000=Pbqh000 and Y.ZYID00=Pzyid00 and Y.YZZT00 IN ('1','2') and NVL(Y.CLRQSJ,Y.QYRQSJ)'3'; --yangy 2013.05.12 描述性的医嘱 select count(1) into COUNTER from BQ_YJYZ00 A WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00<=TO_CHAR(VSYSDATE,'YYYYMMDD') --and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=A.YZMXID)); if (COUNTER > 0) then if Vsrmyztz='Y' or Vsfqyytz='Y' then UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',TZYS00,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',TZRXM0,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=PZYID00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00<=TO_CHAR(VSYSDATE,'YYYYMMDD') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID)); else UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',NULL,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',NULL,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=PZYID00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00<=TO_CHAR(VSYSDATE,'YYYYMMDD') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID)); end if; end if; DELETE FROM BM_YPJJB0_TMP where ID0000=Pbctqid; COMMIT; RETURN;--结束 ELSE RAISE E_MYYZZX;--没有医嘱执行 END IF; ELSIF Vyzly00 = '3' then --入院中心医嘱 --处理没有执行到的医嘱,包括予出院，定期应用，QOD等 --判定是否存在没有执行到的医嘱 SELECT COUNT(*) INTO COUNTER FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F WHERE Y.YZMXID=F.YZMXID and --B.DQBQ00=Pbqh000 and SUBSTR(BRZT00,1,1)='2' and B.ZYID00=Pzyid00 and Y.ZYID00=Pzyid00 and Y.YZZT00 IN ('1','2') and NVL(Y.CLRQSJ,Y.QYRQSJ)0 THEN --修改医嘱的提取时间,定期应用的医嘱要自动停止 UPDATE BQ_YJYZ00 Z SET TQRQ00=(SELECT DECODE(P.ZQDW00,'小时',TQRQ00,TO_CHAR(Pzxrq00,'YYYYMMDD')) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), TQSJ00=(SELECT DECODE(P.ZQDW00,'小时',TQSJ00,TRIM(VYZSJFG)) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), YZZT00=DECODE(CLBZ00,'1','3','0',DECODE(TZRQ00,NULL,'2',DECODE(SIGN(VJZRQSJ-TO_DATE(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3'))) WHERE ZYID00=Pzyid00 and BQH000=Pbqh000 and CLBZ00='0' and YZMXID IN (SELECT Y.YZMXID FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F WHERE Y.YZMXID=F.YZMXID and -- B.DQBQ00=Pbqh000 and SUBSTR(BRZT00,1,1)='2' and B.ZYID00=Pzyid00 and Y.ZYID00=Pzyid00 and Y.YZZT00 IN ('1','2') and NVL(Y.CLRQSJ,Y.QYRQSJ)'3'; --yangy 2013.05.12 描述性的医嘱 select count(1) into COUNTER from BQ_YJYZ00 A WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=A.YZMXID)); if (COUNTER > 0) then if Vsrmyztz='Y' or Vsfqyytz='Y' then UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',TZYS00,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',TZRXM0,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=PZYID00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID and sfxmid>0)); else UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',NULL,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',NULL,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=PZYID00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID and sfxmid>0)); end if; end if; DELETE FROM BM_YPJJB0_TMP where ID0000=Pbctqid; COMMIT; RETURN;--结束 ELSE RAISE E_MYYZZX;--没有医嘱执行 END IF; END IF; END IF; --yangy 2013.05.12 描述性的医嘱 select count(1) into COUNTER from BQ_YJYZ00 A WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=A.YZMXID)); if (COUNTER > 0) then if Vsrmyztz='Y' or Vsfqyytz='Y' then UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',TZYS00,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',TZRXM0,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID and sfxmid>0)); else UPDATE BQ_YJYZ00 Y SET TQRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TQSJ00=VYZSJFG, YZZT00=DECODE(CLBZ00,'0','2','1','3'), TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'YYYYMMDD')), TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')), TZYS00=DECODE(CLBZ00,'0',NULL,'1',KZYS00), TZRXM0=DECODE(CLBZ00,'0',NULL,'1',VCZYXM0), ZXRQSC=decode(CLBZ00,'0',decode(ZXRQSC,null,TO_CHAR(VSYSDATE,'YYYYMMDD'),ZXRQSC)), ZXSJSC=decode(CLBZ00,'0',decode(ZXSJSC,null,TO_CHAR(VSYSDATE,'HH24:MI:SS'),ZXSJSC)) WHERE ZYID00=Pzyid00 and YZZT00 IN ('1','2') and ZLXMID=0 and QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') and NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD') and (BQ_MSXYZKYKF='N' or not exists(select 1 from BQ_YJYZJJ where YZMXID=Y.YZMXID and sfxmid>0)); end if; end if; --计算已经启用长嘱的计算次数(启用时间点，上次提取截至时间点，本次截至提取时间点) UPDATE BQ_YJYZLS Y SET CS0000=(SELECT SF_BQ_YZZXCS(Y.QYRQSJ,Y.TQRQSJ,Y.JZRQSJ,P.HSZQCD,P.ZQCS00,P.ZQDW00) FROM VW_BQ_PC0000 P WHERE Y.PCID00=P.PCID00 ) WHERE ID0000=Pbctqid and CLBZ00='0'; --已经启用的长期医嘱 --短嘱次数(小时的医嘱要*周期次数) UPDATE BQ_YJYZLS Y SET CS0000=(SELECT DECODE(P.ZQDW00,'小时',CS0000*ZQCS00,CS0000) FROM VW_BQ_PC0000 P WHERE Y.PCID00=P.PCID00 ) WHERE ID0000=Pbctqid and CLBZ00='1'; --处理隔天执行的单据 UPDATE BQ_YJYZ00 Z SET TQRQ00=(SELECT DECODE(P.ZQDW00,'小时',TQRQ00,TO_CHAR(Pzxrq00,'YYYYMMDD')) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), TQSJ00=(SELECT DECODE(P.ZQDW00,'小时',TQSJ00,TRIM(VYZSJFG)) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00), YZZT00=DECODE(CLBZ00,'1','3','0',DECODE(TZRQ00,NULL,'2',DECODE(SIGN(VJZRQSJ-TO_DATE(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3'))) WHERE ZYID00=Pzyid00 and BQH000=Pbqh000 and CLBZ00='0' and YZZT00 in ('1','2') and YZMXID in (select L.YZMXID from BQ_YJYZLS L where ID0000=Pbctqid and L.ZYID00=Pzyid00 and nvl(CS0000,0)=0); --删除计算次数等于小于0的记录 DELETE BQ_YJYZLS WHERE (CS0000<=0 OR CS0000 IS NULL ) and ID0000=Pbctqid; --对于小时频次的医嘱，将执行次数合并到每次数量 UPDATE BQ_YJYZLS Y SET (MCSL00,CS0000)=(SELECT DECODE(P.ZQDW00,'小时',MCSL00*CS0000,MCSL00), DECODE(P.ZQDW00,'小时',1,CS0000) FROM VW_BQ_PC0000 P WHERE Y.PCID00=P.PCID00) WHERE ID0000=Pbctqid; DELETE FROM BM_YPJJB0_TMP where ID0000=Pbctqid; --END OF 整理临时表 --COMMIT; --处理有医嘱但未生成医技单 WAITING COMPLETION --按病人逐个生成执行单 SP_BQ_YJYZZX_YGBR00(Pczy000,Pzyid00,Pbqh000,Pzxrq00,Pbctqid,VCZYXM0,VJFLBID,'N',Pcjksbh,Pystjdz); DELETE FROM BQ_YJYZLS WHERE ID0000=Pbctqid ; COMMIT; EXCEPTION WHEN E_MYTQSJ THEN RAISE_APPLICATION_ERROR(-20001, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'没有执行时间，不能执行非药医嘱！*'||VPARAMS,1,180)); WHEN E_MYCFJL THEN RAISE_APPLICATION_ERROR(-20002, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'数据不完整！*部分医嘱没有拆分记录'||VPARAMS,1,180)); WHEN E_YZSJFGCSCW THEN RAISE_APPLICATION_ERROR(-20003, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'系统参数:医嘱时间分割点错误！*'||VPARAMS,1,180)); WHEN E_MYYZZX THEN --没有医嘱可以提取; RAISE_APPLICATION_ERROR(-20004, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'没有非药医嘱可以执行！*'||VPARAMS,1,180)); WHEN E_HLXMCW THEN --护理项目错误 RAISE_APPLICATION_ERROR(-20005, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'有多个护理级别，请纠正后后再执行！*'||VPARAMS,1,180)); WHEN E_YZZNDTTQ THEN RAISE_APPLICATION_ERROR(-20080, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'医嘱只能提前执行'||TO_CHAR(VWHTQTS)||'天！*'||VPARAMS,1,180)); WHEN E_ZHLBCSCW THEN RAISE_APPLICATION_ERROR(-20006,substrb('病人'||VBRXM00||'('||VBRCH00||')'||'帐户类别参数错误，终止执行非药医嘱！*'||VPARAMS,1,180)); WHEN E_CFQWT THEN RAISE_APPLICATION_ERROR(-20007, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'BQ_YJYZCF数据未找到！*'||VPARAMS,1,180)); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, substrb('病人'||VBRXM00||'('||VBRCH00||')'||'数据没有找到，终止执行非药医嘱！*'||VPARAMS,1,180)); when others then RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,180)); -- RAISE_APPLICATION_ERROR(-20020, NVL(SUBSTRB(SQLERRM,180),'原因不明出错')||'！*'||VPARAMS); rollback; END;