-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YPYZTQ -- Generated 29-九月-2019 15:52:46 from SD_HOSPITAL@TOSD_csclYY CREATE OR REPLACE PROCEDURE sp_bq_ypyztq --病人的药品医嘱提取成处方 -- 提取的内容根据以下情况组合： -- 提取对象：单个病人提取，全区病人提取； -- 医嘱长零：长期医嘱，零星医嘱；长零同时提取(PCLBZ00 IS NULL) -- 医嘱细类：药品类别中，西，草 -- 药品用法：煎药、注射、静滴、口服、其他 -- 截至日期：提取日期+1 -- 可以提取的长嘱时间规则判定: -- 起点:Greatest([拆分表的]起始日期,提取日期) -- 终点:Least(预出院日期,截至日期,预停止日期) -- 条件:起点<终点 -- 长嘱是否已经提取的判定规则： -- 未提取: (医嘱拆分表的)起始日期>=提取日期 -- 已提取: (医嘱拆分表的)起始日期<提取日期 -- 长嘱提取次数的规则: -- 已经提取医嘱: 次数=CEIL(周期数-1) -- 未提取医嘱 : 次数=CEIL(周期数) -- 草药必须每次提取一个周期 --药房无药时提示不足7.21 ( PCZY000 IN NUMBER, --操作员 PZYID00 IN NUMBER, --住院流水号 PBQH000 IN NUMBER, --病区号 PCLBZ00 IN CHAR , --长零标志 '0'长嘱，'1'短嘱，其他长短嘱 PYFLBID IN NUMBER, --用法提取类别ID PYPLBBH IN CHAR , --药品类别 PTQRQ00 IN DATE , --提取日期,时间无效 PBCTQID OUT NUMBER, --本次提取ID PTQBZ00 in number default 0 --提取标志 0 病区系统提取 1 手术麻醉系统提取 ) AS VKFJZRQ DATE; --本次KF医嘱提取截至日期(含时间) VZJJZRQ DATE; --本次ZJ医嘱提取截至日期(含时间) VCYRQSJ DATE; --病人预出院日期和时间 VKFJZSJ CHAR(16); --同上,char型 VZJJZSJ CHAR(16); --同上,char型 VKFSJFG XT_XTCS00.VALUE0%TYPE; --医嘱时间分割点 VZJSJFG XT_XTCS00.VALUE0%TYPE; --医嘱时间分割点 VQYRQXZ XT_XTCS00.VALUE0%TYPE; --20110707新增参数 by BQ-20110629-002 VYZID00 BQ_YPYZ00.YZID00%TYPE; VYPMC00 BQ_YPYZMX.YPMC00%TYPE; VYPLBBH BQ_YPYZ00.YPLBBH%TYPE; VBRID00 ZY_BRXXB0.BRID00%TYPE; VZYID00 VARCHAR2(10); VCLBZ00 VARCHAR2(1); VWHTQTS NUMBER(5); --医嘱允许往后提取的天数 VMYYZTQ CHAR(1); VSYSDATE DATE; COUNTER NUMBER(6); VPARAMS VARCHAR2(255); --错误时传递参数 Ecustom EXCEPTION; --错误 Verrmsg VARCHAR2(255); --错误提示 VSYTYM0 CHAR(1); --是否使用通用名 ls_BYJTQFS varchar2(5); --包药机提取方式 VKFQSRQSJ date; --add for BQ-20140519-002 VZJQSRQSJ date; --add for BQ-20140519-002 ls_TQYZKSRQ varchar2(20); ls_num number(5); ls_SSYZSFBYXBRTQ XT_XTCS00.VALUE0%type; -- ls_ErrorCode varchar2(10); -- ls_ErrorCode1 varchar2(10); --Modified By Lindj 2004.02.23 BM_YD0000.LBBH00->BQ_YPYZSQ.YPLBBH --病区当前在院病人 CURSOR C_ZYBR00 IS SELECT BRID00, ZYID00, XM0000, RYCWH0, ZYH000, TO_DATE(CYDJRQ||CYDJSJ,'YYYYMMDD HH24:MI:SS') "CYRQSJ" FROM ZY_BRXXB0 WHERE DQBQ00=PBQH000 AND BRZT00='20'; -- and ZYID00=443456 --BRZT00 LIKE '2%' ; --指定提取类别ID短嘱 CURSOR C_LSYZ00 IS SELECT Z.YZID00, M.YZMXID FROM BQ_YPYZ00 Z,BQ_YPYZMX M,VW_BQ_YFTQLB Y WHERE ZYID00=VZYID00 AND Z.YZID00=M.YZID00 AND Z.CLBZ00='1' AND ((YZZT00='1')OR (YZZT00='2'))AND--Z.DMBZ00='0' M.YPLBBH LIKE VYPLBBH AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND Z.QYRQ00'Y' AND Z.QYRQ00'Y' AND Z.QYRQ000 then ls_TQYZKSRQ:='N'; else begin SELECT trim(VALUE0) into ls_TQYZKSRQ FROM XT_XTCS00 WHERE NAME00='BQ_TQYZKSRQ' and substr(VALUE0,1,1)='Y'; ls_TQYZKSRQ:=substr(ls_TQYZKSRQ,3,10); ls_num:=to_number(ls_TQYZKSRQ); exception when others then ls_TQYZKSRQ:='N'; end; end if; VSYSDATE:=SYSDATE; VPARAMS :='SP_BQ_YPYZTQ('||NVL(TO_CHAR(PCZY000),'NULL')||','||NVL(TO_CHAR(PZYID00),'NULL')||','|| NVL(TO_CHAR(PBQH000),'NULL')||','||NVL(PCLBZ00,'NULL')||','||NVL(TO_CHAR(PYFLBID),'NULL')|| ','||NVL(PYPLBBH,'NULL')||','||NVL(TO_CHAR(PTQRQ00,'YYYYMMDD'),'NULL')||',PBCTQID)'; VYPLBBH :=NVL(PYPLBBH,'%'); VCLBZ00 :=NVL(PCLBZ00,'%'); if ls_SSYZSFBYXBRTQ = 'Y' then Verrmsg:='没有医嘱可以提取或该医嘱为手术医嘱！*'; else Verrmsg:='没有医嘱可以提取！*'; end if; IF PZYID00>0 THEN VZYID00:=TO_CHAR(PZYID00); --不限定往后提取的天数 SELECT to_number(nvl(max(VALUE0),'9999')) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BR'; ELSE SELECT to_number(nvl(max(VALUE0),'9999')) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BQ'; END IF; IF PTQRQ00 IS NULL THEN Verrmsg:='没有提取时间，无法提取'; RAISE Ecustom; ELSIF (TRUNC(PTQRQ00)-TRUNC(VSYSDATE)>VWHTQTS) THEN--提取时间限制 Verrmsg:='医嘱只能提前提取'||TO_CHAR(VWHTQTS)||'天'; RAISE Ecustom; END IF; BEGIN SELECT VALUE0 INTO VKFSJFG FROM XT_XTCS00 WHERE NAME00='BQ_KFYZSJFG'; SELECT VALUE0 INTO VZJSJFG FROM XT_XTCS00 WHERE NAME00='BQ_ZJYZSJFG'; --获得提取的截至日期时间(起始时间为当日) if ls_TQYZKSRQ='N' then VKFQSRQSJ:=to_date(to_char(sysdate-1000,'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS'); VZJQSRQSJ:=to_date(to_char(sysdate-1000,'YYYYMMDD')||VZJSJFG,'YYYYMMDDHH24:MI:SS'); else VKFQSRQSJ:=to_date(to_char(sysdate+ls_num,'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS'); VZJQSRQSJ:=to_date(to_char(sysdate+ls_num,'YYYYMMDD')||VZJSJFG,'YYYYMMDDHH24:MI:SS'); end if; VKFJZRQ:=TO_DATE(TO_CHAR(TRUNC(PTQRQ00)+1,'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS'); VKFJZSJ:=TO_CHAR(VKFJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 VZJJZRQ:=TO_DATE(TO_CHAR(TRUNC(PTQRQ00)+1,'YYYYMMDD')||VZJSJFG,'YYYYMMDDHH24:MI:SS'); VZJJZSJ:=TO_CHAR(VZJJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 EXCEPTION WHEN OTHERS THEN Verrmsg:='系统参数:医嘱时间分割点错误'; RAISE Ecustom; END ; if ls_TQYZKSRQ<>'N' and to_date(to_char(sysdate+ls_num,'YYYYMMDD'),'YYYYMMDD')>PTQRQ00 then Verrmsg:='截止日期不能改在参数（BQ_TQYZKSRQ）设定的开始日期之前'||PTQRQ00; raise Ecustom; end if; SELECT count(*) into COUNTER FROM XT_XTCS00 WHERE NAME00='BQ_YZTQSFYXQYRQDYTQRQ' and trim(value0)='Y'; IF COUNTER>0 THEN VQYRQXZ:='Y'; else VQYRQXZ:='N'; END IF; --20031007 --检验是否有未按规则拆分的长嘱 FOR BR IN C_ZYBR00 LOOP BEGIN IF (PYFLBID IS NOT NULL) THEN --指定用法类别 SELECT Z.YZID00, M.YPMC00 INTO VYZID00, VYPMC00 FROM BQ_YPYZ00 Z, BQ_YPYZMX M, VW_BQ_YFTQLB Y WHERE Z.ZYID00=BR.ZYID00 AND Z.YZID00=M.YZID00 AND Z.CLBZ00='0' AND M.YPLBBH LIKE VYPLBBH AND--Z.DMBZ00='0'AND (Z.YZZT00='1' OR Z.YZZT00='2') AND M.YZCLLB<>('嘱托') AND NOT EXISTS (SELECT 1 FROM BQ_YPYZCF C WHERE C.YZID00=Z.YZID00) AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND ROWNUM=1; ELSE--不指定用法类别 SELECT Z.YZID00, M.YPMC00 INTO VYZID00, VYPMC00 FROM BQ_YPYZ00 Z, BQ_YPYZMX M WHERE Z.ZYID00=BR.ZYID00 AND Z.YZID00=M.YZID00 AND Z.CLBZ00='0' AND M.YPLBBH LIKE VYPLBBH AND-- Z.DMBZ00='0' AND (Z.YZZT00='1' OR Z.YZZT00='2') AND M.YZCLLB<>('嘱托') AND NOT EXISTS (SELECT 1 FROM BQ_YPYZCF C WHERE C.YZID00=Z.YZID00) AND ROWNUM=1; END IF; Verrmsg:='"'||TRIM(BR.RYCWH0)||'"床病人--'||BR.XM0000||'的"'||VYPMC00||'"'||'药品医嘱(医嘱号='||TO_CHAR(VYZID00)||')核对时有误,请重开医嘱'; RAISE Ecustom;--医嘱拆分的触发器问题 EXCEPTION WHEN OTHERS THEN Verrmsg:=Verrmsg; END ; END LOOP; --是否使用通用名 SELECT COUNT(1) INTO COUNTER FROM XT_XTCS00 WHERE NAME00='BQ_YPMCLB' AND VALUE0='2'; IF COUNTER>0 THEN VSYTYM0:='Y'; else VSYTYM0:='N'; END IF; --获得本次提取ID SELECT SQ_BQ_ZBTQYZ_ID0000.NEXTVAL INTO PBCTQID FROM DUAL WHERE ROWNUM=1; --整理出准备提取医嘱(毒麻药品不提取) DELETE BQ_ZBTQYZ WHERE ID0000=PBCTQID; --********************************单病人提取***********************************----------- IF (PZYID00>0) THEN SELECT BRID00 INTO VBRID00 FROM ZY_BRXXB0 WHERE ZYID00=PZYID00; --皮试未执行或未通过的不能提取,(如果皮试药的用法是脱敏注射，不管皮试结果，都允许提取) IF (PYFLBID IS NOT NULL) THEN --指定用法类别 SELECT COUNT(*) INTO COUNTER FROM BQ_YPYZMX M,BQ_YPYZ00 Z ,VW_BQ_YFTQLB Y WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=PZYID00 AND (((YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM IN (SELECT J.YPNM00 FROM (SELECT YPNM00,MAX(JLRQ00||JLSJ00)JLRQSJ FROM YJ_BRGMJL WHERE BRID00=VBRID00 GROUP BY YPNM00) X,YJ_BRGMJL J WHERE BRID00=VBRID00 AND JLRQ00||JLSJ00=JLRQSJ AND J.YPNM00=X.YPNM00 AND ZCBZ00='N'))--有过敏史 AND YZZT00 IN ('2','1')) OR (PSID00 IS NOT NULL AND (PSTGBZ IS NULL OR PSTGBZ='E') AND YZZT00 IN ('0','1','2','3'))--未录入皮试结果 ) ) AND (M.PSID00 IS NULL OR (M.PSMC00 NOT LIKE '%脱敏%' and M.PSMC00 NOT LIKE '%免试%') ) AND M.YPLBBH LIKE VYPLBBH AND CLBZ00 LIKE VCLBZ00 AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND ROWNUM=1; ELSE SELECT COUNT(*) INTO COUNTER FROM BQ_YPYZMX M,BQ_YPYZ00 Z WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=PZYID00 AND ((( YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM IN (SELECT J.YPNM00 FROM YJ_BRGMJL J,(SELECT YPNM00,MAX(JLRQ00||JLSJ00)JLRQSJ FROM YJ_BRGMJL WHERE BRID00=VBRID00 GROUP BY YPNM00 ) X WHERE BRID00=VBRID00 AND JLRQ00||JLSJ00=JLRQSJ AND J.YPNM00=X.YPNM00 AND ZCBZ00='N') )--有过敏史 AND YZZT00 IN ('2','1') )OR (PSID00 IS NOT NULL AND (PSTGBZ IS NULL OR PSTGBZ='E') AND YZZT00 IN ('0','1','2','3'))--未录入皮试结果 ) ) AND (M.PSID00 IS NULL OR (M.PSMC00 NOT LIKE '%脱敏%' and M.PSMC00 NOT LIKE '%免试%') ) AND M.YPLBBH LIKE VYPLBBH AND CLBZ00 LIKE VCLBZ00 AND ROWNUM=1; END IF; IF COUNTER>0 THEN Verrmsg:='有未执行或未通过的皮试医嘱'; RAISE Ecustom; END IF; VZYID00:=TO_CHAR(PZYID00);--字符型 SELECT TO_DATE(CYDJRQ||CYDJSJ,'YYYYMMDDHH24:MI:SS') INTO VCYRQSJ FROM ZY_BRXXB0 WHERE ZYID00=PZYID00; IF VCYRQSJ IS NOT NULL THEN VKFJZRQ:=LEAST(VCYRQSJ,VKFJZRQ);--日期型 VZJJZRQ:=LEAST(VCYRQSJ,VZJJZRQ);--日期型 VKFJZSJ:=TO_CHAR(VKFJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 VZJJZSJ:=TO_CHAR(VZJJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 --预出院病人的出院时间小于提取的截至时间，取预出院时间 END IF; --提取短嘱所有药品医嘱 IF (PCLBZ00 IS NULL)OR (PCLBZ00='1')THEN --医嘱已经算好领药量(发药单位) --当QYRQSJ=JZRQSJ,或TQRQSJ=JZRQSJ时，医嘱不提取 --草药的领药量=每次用量*总贴数 --短嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN INSERT INTO BQ_ZBTQYZ ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, MCSL00, QYRQSJ, TQRQSJ, JZRQSJ, SJBZ00, YPSLSL, YFBMBH, YPLBBH, ZYID00, CS0000)--计算次数 SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, '1', X.YZCLLB, Z.PCID00, X.MCSL00, TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(Z.QYRQ00||Z.QYSJ00, 'YYYYMMDDHH24:MI:SS'), DECODE(Z.kfbz00,'Y', VKFJZRQ, VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24, '1', X.LYL000, Z.YFBMBH, X.YPLBBH, Z.ZYID00, DECODE(X.YPLBBH, '2', NVL(Z.ZQS000, 1)*NVL(Z.ZQCYTS, 1), NVL(Z.ZQS000, 1)*NVL(P.ZQCS00, 1))--中药时，为帖数 FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BM_PC0000 P, VW_BQ_YFTQLB Y WHERE ZYID00=PZYID00 AND Z.YZID00=X.YZID00 AND Z.PCID00=P.PCID00(+) AND Z.CLBZ00='1' AND ((YZZT00='1')OR (YZZT00='2'))AND-- Z.DMBZ00='0' X.YPLBBH LIKE VYPLBBH AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND Z.QYRQ00 < TO_CHAR(VKFJZRQ, 'YYYYMMDD') AND--利用索引 Z.QYRQ00||Z.QYSJ00 < DECODE(Z.KFBZ00, 'Y', VKFJZSJ, VZJJZSJ) and ((ls_SSYZSFBYXBRTQ<>'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --短嘱，只要医嘱提取截至日期>起用日期时间，非毒，即可提取 --草药必须每次提取一个周期 --短嘱，不指定用法类别 ELSE INSERT INTO BQ_ZBTQYZ ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, MCSL00, QYRQSJ, TQRQSJ, JZRQSJ, SJBZ00, YPSLSL, YFBMBH, YPLBBH, ZYID00, CS0000)--计算次数 SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, '1', X.YZCLLB, Z.PCID00, MCSL00, TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), DECODE(Z.KFBZ00, 'Y', VKFJZRQ, VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24 '1', X.LYL000, Z.YFBMBH, X.YPLBBH, Z.ZYID00, DECODE(X.YPLBBH,'2',NVL(Z.ZQS000,1)*NVL(Z.ZQCYTS,1),NVL(Z.ZQS000,1)*NVL(P.ZQCS00,1))--中药时，为帖数 FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BM_PC0000 P WHERE ZYID00=PZYID00 AND Z.YZID00=X.YZID00 AND Z.PCID00=P.PCID00(+) AND Z.CLBZ00='1' AND ((YZZT00='1')OR (YZZT00='2'))AND --Z.DMBZ00='0' Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; --提取长嘱所有药品医嘱 IF (PCLBZ00 IS NULL) OR (PCLBZ00='0') THEN --插入长嘱西药和成药、草药明细项(注意草药，每次按整个周期提取) --长嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH, YPLBBH, ZYID00,SCBZ00) SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, Z.CLBZ00, X.YZCLLB, Z.PCID00, TO_DATE(F.QSRQ00||F.QSSJ00, 'YYYYMMDDHH24:MI:SS'), NVL(TO_DATE(X.TQRQ00||X.TQSJ00, 'YYYYMMDDHH24:MI:SS'), TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ),NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ))), --SF_BQ_TQRQ00(Z.KFBZ00,VKFJZRQ,VZJJZRQ,to_date(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),nvl(to_date(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),to_date(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),Z.PCID00,X.YPNM00,null), --长期医嘱有预停止日期的,取较小的时间 X.MCSL00,'0', Z.YFBMBH, X.YPLBBH, Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BQ_YPYZCF F, VW_BQ_YFTQLB Y WHERE Z.YZID00=X.YZID00 AND Z.YZID00=F.YZID00 AND Y.TQLBID=PYFLBID AND ZYID00=PZYID00 AND Z.CLBZ00='0' AND YZZT00 IN ('1','2') AND--AND Z.DMBZ00='0' X.YPLBBH LIKE VYPLBBH AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --长期，非毒，核对和正在执行医嘱，MAX[提取日期，起始日期]'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; ELSE --************************************按病区提取*****************************-- FOR BR IN C_ZYBR00 LOOP --皮试未通过不能提取 BEGIN IF (PYFLBID IS NOT NULL) THEN --指定用法类别 SELECT Z.YZID00,M.YPMC00 INTO VYZID00,VYPMC00 FROM BQ_YPYZMX M,BQ_YPYZ00 Z ,VW_BQ_YFTQLB Y WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=BR.ZYID00 AND ( ( (YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM IN(SELECT J.YPNM00 FROM YJ_BRGMJL J,(SELECT YPNM00,MAX(JLRQ00||JLSJ00)JLRQSJ FROM YJ_BRGMJL WHERE BRID00=BR.BRID00 GROUP BY YPNM00) X WHERE BRID00=BR.BRID00 AND JLRQ00||JLSJ00=JLRQSJ AND J.YPNM00=X.YPNM00 AND ZCBZ00='N') )AND YZZT00 IN ('2','1'))--有过敏史 OR(PSID00 IS NOT NULL AND (PSTGBZ IS NULL OR PSTGBZ='E'))AND YZZT00 IN ('0','1','2','3')) ) AND YZZT00 IN ('3','2','1') AND (M.PSID00 IS NULL OR (M.PSMC00 NOT LIKE '%脱敏%' and M.PSMC00 NOT LIKE '%免试%') ) AND M.YPLBBH LIKE VYPLBBH AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND CLBZ00 LIKE VCLBZ00 AND ROWNUM=1; ELSE SELECT Z.YZID00,M.YPMC00 INTO VYZID00,VYPMC00 FROM BQ_YPYZMX M,BQ_YPYZ00 Z WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=BR.ZYID00 AND ( ( (YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM IN (SELECT J.YPNM00 FROM YJ_BRGMJL J,(SELECT YPNM00,MAX(JLRQ00||JLSJ00)JLRQSJ FROM YJ_BRGMJL WHERE BRID00=BR.BRID00 GROUP BY YPNM00)X WHERE BRID00=BR.BRID00 AND JLRQ00||JLSJ00=JLRQSJ AND J.YPNM00=X.YPNM00 AND ZCBZ00='N') )--有过敏史 AND YZZT00 IN ('2','1') ) OR(PSID00 IS NOT NULL AND (PSTGBZ IS NULL OR PSTGBZ='E'))AND YZZT00 IN ('0','1','2','3')) ) AND YZZT00 IN ('3','2','1') AND (M.PSID00 IS NULL OR (M.PSMC00 NOT LIKE '%脱敏%' and M.PSMC00 NOT LIKE '%免试%') ) AND M.YPLBBH LIKE VYPLBBH AND CLBZ00 LIKE VCLBZ00 AND ROWNUM=1; END IF; Verrmsg:='"'||TRIM(BR.RYCWH0)||'"床病人--'||BR.XM0000||'的药品"'||VYPMC00||'"'||'(医嘱号='||TO_CHAR(VYZID00)||')皮试未通过'; RAISE Ecustom; EXCEPTION WHEN OTHERS THEN if instr(Verrmsg,'皮试未通过')=0 then Verrmsg:=Verrmsg; else RAISE Ecustom; end if; END; --插入短嘱汇总，医嘱已经算好领药量(发药单位) IF (PCLBZ00 IS NULL) OR (PCLBZ00='1')THEN VZYID00:='%'; --短嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN INSERT INTO BQ_ZBTQYZ ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, MCSL00, QYRQSJ, TQRQSJ, JZRQSJ, SJBZ00, YPSLSL, YFBMBH, YPLBBH, ZYID00, CS0000)--计算次数 SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, '1', X.YZCLLB, Z.PCID00, X.MCSL00, TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(Z.QYRQ00||Z.QYSJ00, 'YYYYMMDDHH24:MI:SS'), DECODE(Z.KFBZ00,'Y', VKFJZRQ, VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24, '1', X.LYL000, Z.YFBMBH, X.YPLBBH, Z.ZYID00, DECODE(X.YPLBBH,'2',NVL(Z.ZQS000,1)*NVL(Z.ZQCYTS,1),NVL(Z.ZQS000,1)*NVL(P.ZQCS00,1))--中药时，为帖数 FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BM_PC0000 P, VW_BQ_YFTQLB Y WHERE Z.YZID00=X.YZID00 AND Z.PCID00=P.PCID00(+) AND Z.CLBZ00='1' AND YZZT00 in ('1','2')AND X.YPLBBH LIKE VYPLBBH AND-- Z.DMBZ00='0' NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND -- X.YPLBBH LIKE VYPLBBH AND YFID00 IN (SELECT YFID00 FROM BM_YPYF00 WHERE YFLBID=PYFLBID); Z.ZYID00=BR.ZYID00 AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --当QYRQSJ=JZRQSJ,或TQRQSJ=JZRQSJ时，医嘱不提取 --短嘱，不指定用法类别 ELSE INSERT INTO BQ_ZBTQYZ ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, MCSL00, QYRQSJ, TQRQSJ, JZRQSJ, SJBZ00, YPSLSL, YFBMBH, YPLBBH, ZYID00, CS0000)--计算次数 SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG,'1', X.YZCLLB, Z.PCID00, X.MCSL00, TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(Z.QYRQ00||Z.QYSJ00,'YYYYMMDDHH24:MI:SS'), DECODE(Z.KFBZ00,'Y', VKFJZRQ, VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24, '1', X.LYL000, Z.YFBMBH, X.YPLBBH, Z.ZYID00, DECODE(X.YPLBBH,'2',NVL(Z.ZQS000,1)*NVL(Z.ZQCYTS,1),NVL(Z.ZQS000,1)*NVL(P.ZQCS00,1))--中药时，为帖数 FROM BQ_YPYZ00 Z,BQ_YPYZMX X,BM_PC0000 P WHERE Z.YZID00=X.YZID00 AND Z.PCID00=P.PCID00(+) AND Z.CLBZ00='1' AND-- Z.DMBZ00='0' X.YPLBBH LIKE VYPLBBH AND YZZT00 in ('1','2') AND Z.ZYID00=BR.ZYID00 AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; --插入长嘱 IF (PCLBZ00 IS NULL) OR (PCLBZ00='0') THEN --插入长嘱西药和成药、草药明细项 --长嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN --提取长嘱所有药品医嘱 INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH, YPLBBH, ZYID00,SCBZ00) SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, Z.CLBZ00, X.YZCLLB, Z.PCID00, TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS'), --NVL(TO_DATE(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), GREATEST(NVL(TO_DATE(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),decode(Z.KFBZ00,'Y',VKFQSRQSJ,VZJQSRQSJ)), -- for BQ-20140519-002 LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ, VZJJZRQ),NVL(BR.CYRQSJ, DECODE(Z.KFBZ00,'Y', VKFJZRQ, VZJJZRQ)), NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)) ), --SF_BQ_TQRQ00(Z.KFBZ00,VKFJZRQ,VZJJZRQ,to_date(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),nvl(to_date(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),to_date(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),Z.PCID00,X.YPNM00,BR.CYRQSJ), X.MCSL00, '0', Z.YFBMBH, X.YPLBBH, Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BQ_YPYZCF F, VW_BQ_YFTQLB Y WHERE Z.YZID00=X.YZID00 AND Z.YZID00=F.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.CLBZ00='0' AND Z.YZZT00 IN ('1','2')AND-- Z.DMBZ00='0' X.YPLBBH LIKE VYPLBBH AND NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --长嘱，不指定用法类别 ELSE INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH, YPLBBH, ZYID00,SCBZ00) SELECT PBCTQID, X.YZID00, X.YZMXID, NVL(X.TQYPNM, X.YPNM00), X.SFDZGG, Z.CLBZ00, X.YZCLLB, Z.PCID00, TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS'), -- NVL(TO_DATE(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), GREATEST(NVL(TO_DATE(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),decode(Z.KFBZ00,'Y',VKFQSRQSJ,VZJQSRQSJ)), -- for BQ-20140519-002 LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ),NVL(BR.CYRQSJ,DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)), NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)) ), --SF_BQ_TQRQ00(Z.KFBZ00,VKFJZRQ,VZJJZRQ,to_date(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),nvl(to_date(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),to_date(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),Z.PCID00,X.YPNM00,BR.CYRQSJ), X.MCSL00, '0',YFBMBH,X.YPLBBH,Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z, BQ_YPYZMX X, BQ_YPYZCF F WHERE Z.YZID00=X.YZID00 AND Z.YZID00=F.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.CLBZ00='0'AND Z.YZZT00 IN ('1','2') AND-- Z.DMBZ00='0' X.YPLBBH LIKE VYPLBBH AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; END LOOP; END IF;--原始数据插入完毕 --把预停止撤销的医嘱从中间表删除BQ-20180509-001 delete BQ_ZBTQYZ a where ID0000=PBCTQID and exists(select 1 from BQ_YPYZ00 where YZID00=a.YZID00 and nvl(YCXBZ0,'0')='1'); --********************开始计算领药量*******************************--------------------- --填写换算周期长度,取整方式,天数,转换率 --- JZRQSJ by BQ-20111207-001 --update BQ_ZBTQYZ Y set JZRQSJ=least((select to_date(YTZRQ0||YTZSJ0,'YYYYMMDDHH24:MI:SS') from BQ_YPYZ00 where YZID00=Y.YZID00 and rownum=1),Y.JZRQSJ) update BQ_ZBTQYZ Y set JZRQSJ=(select least(to_date(YTZRQ0||YTZSJ0,'YYYYMMDDHH24:MI:SS'),Y.JZRQSJ) from BQ_YPYZ00 where YZID00=Y.YZID00 and rownum=1) where ID0000=PBCTQID and exists (select 1 from BQ_YPYZ00 where YZID00=Y.YZID00 and CLBZ00='0' and YTZRQ0 is not null); UPDATE BQ_ZBTQYZ Y SET HSZQCD=(SELECT HSZQCD FROM VW_BQ_PC0000 P WHERE Y.PCID00=P.PCID00) WHERE ID0000=PBCTQID ; --ls_ErrorCode:='a'; --包药机药品提取周期设置 if ls_BYJTQFS='1' then --insert into BQ_ZBTQYZ_TMP --select * from BQ_ZBTQYZ where ID0000=PBCTQID ; --decode(Y.SCBZ00,1,0,1),Y.JZRQSJ) 第1次提取如果启用日期和实际启用日期差1天时，实际截止日期不要加1天 update BQ_ZBTQYZ Y set JZRQSJ=(select least(TO_DATE(to_char(min(TQRQSJ),'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS')+decode(Y.SCBZ00,1,0,1),Y.JZRQSJ) from BQ_ZBTQYZ where YZID00=Y.YZID00 and ID0000=PBCTQID )--decode(Y.SCBZ00,1,0,1) where ID0000=PBCTQID and SJBZ00='0' --and exists (select 1 from BQ_YPYZMX a,BM_YD0000 b where YZID00=Y.YZID00 and CLBZ00='0' and a.YPNM00=b.YPNM00 and YPCZFL='3') and exists(select 1 from BQ_YPYZ00 where YZID00=Y.YZID00 and KFBZ00='Y' ); end if; --ls_ErrorCode:='b'; UPDATE BQ_ZBTQYZ Y SET --HSZQCD=(SELECT HSZQCD FROM VW_BQ_PC0000 P WHERE Y.PCID00=P.PCID00), QZFSID=(SELECT QZFS00 FROM BM_YD0000 X WHERE Y.YPNM00=X.YPNM00), TS0000=(TRUNC( JZRQSJ)-GREATEST(TRUNC(NVL(TQRQSJ,QYRQSJ)),TRUNC(QYRQSJ))),--天数 ZHL000=(SELECT DECODE(CLBZ00,'1',ZHL000,D.Z2J000) FROM BM_YD0000 D WHERE Y.YPNM00=D.YPNM00) --ZHL000=(SELECT DECODE(CLBZ00,'1',ZHL000,DECODE(B.BMXZ00,'3',D.Z2J000,D.M2J000)) -- FROM BM_BMBM00 B,BM_YD0000 D -- WHERE B.BMBH00=Y.YFBMBH AND Y.YPNM00=D.YPNM00) WHERE ID0000=PBCTQID ; IF SQL%NOTFOUND THEN --没有医嘱可以提取的处理 VMYYZTQ:='Y'; IF (VCYRQSJ IS NOT NULL) AND PTQRQ00>VCYRQSJ THEN if ls_SSYZSFBYXBRTQ = 'Y' then Verrmsg:='没有医嘱可以提取或该医嘱为手术医嘱，提取日期大于病人的预出院日期！*'; else Verrmsg:='没有医嘱可以提取，提取日期大于病人的预出院日期！*'; end if; END IF; GOTO LABEL1; END IF; --特殊处理,草药必须一次领一个周期的药品 --未起用的草药医嘱的计算办法 --起用的时间点< 分割时间点 起用日期+CEIL(周期数)*周期长度-1 --起用的时间点>=分割时间点 起用日期+CEIL(周期数)*周期长度 --计算已经启用长嘱的计算次数(启用时间点，上次提取截至时间点，本次截至提取时间点) UPDATE BQ_ZBTQYZ Y SET JZRQSJ=DECODE(SIGN(NVL(TQRQSJ,QYRQSJ)-QYRQSJ),1, TQRQSJ+CEIL((JZRQSJ-TQRQSJ)*24/HSZQCD)*HSZQCD/24, --8.03 TQRQSJ+1+CEIL((JZRQSJ-TQRQSJ-1)*24/HSZQCD),--已经起用的医嘱 --未起用的医嘱 TRUNC(QYRQSJ)+(JZRQSJ-TRUNC(JZRQSJ)) +DECODE(SIGN((QYRQSJ-TRUNC(QYRQSJ))-(JZRQSJ-TRUNC(JZRQSJ))), -1, CEIL((JZRQSJ-QYRQSJ)*24/HSZQCD)*HSZQCD/24-1, CEIL((JZRQSJ-QYRQSJ)*24/HSZQCD)*HSZQCD/24)) WHERE YPLBBH='2' AND CLBZ00='0'AND ID0000=PBCTQID; --计算执行次数 UPDATE BQ_ZBTQYZ Y SET CS0000=(SELECT SF_BQ_YZZXCS(Y.QYRQSJ,Y.TQRQSJ,Y.JZRQSJ,P.HSZQCD,P.ZQCS00,P.ZQDW00)*NVL(Z.ZQCYTS,1) FROM BQ_YPYZ00 Z,BM_PC0000 P WHERE Y.YZID00=Z.YZID00 AND Z.PCID00=P.PCID00 ) WHERE CLBZ00='0' AND ID0000=PBCTQID ; -- 删除计算次数等于小于0的记录 DELETE BQ_ZBTQYZ WHERE (CS0000<=0 OR CS0000 IS NULL ) AND ID0000=PBCTQID AND SJBZ00='0'; --校验数据 --SELECT COUNT(*) INTO COUNTER FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND --IF COUNTER=0 THEN -- RAISE E_SJCW;--数据错误 -- RETURN; --END IF; --取整后药品医嘱汇总(长)(根据医嘱明细汇总) INSERT INTO BQ_ZBTQYZ( ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID, ZYID00, SFKCBZ, YPLBBH,PCID00) SELECT ID0000, YZMXID, YZID00, VSYSDATE,VSYSDATE,JZRQSJ, 0, '0', YPNM00, '0', '1', ROUND(DECODE(QZFSID,0,SUM(CS0000*CEIL(MCSL00/ZHL000)),--每次取整 1,CEIL(SUM(CS0000*MCSL00/ZHL000)),--每天取整 2,CEIL(SUM(CS0000*MCSL00/ZHL000)),--每方取整 4,SUM(CS0000*MCSL00/ZHL000), --每方取整后顺延 3,SUM(CS0000*MCSL00/ZHL000))+0.0049,2),--不取整 YFBMBH,SUM(CS0000),MAX(TS0000),QZFSID,ZYID00,'0',YPLBBH,PCID00 FROM BQ_ZBTQYZ WHERE ID0000 = PBCTQID AND SJBZ00='0' AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,YZID00,QZFSID,JZRQSJ,ZYID00,YPLBBH,PCID00; /*--每天取整的药品医嘱(长)汇总(根据医嘱明细汇总)(未完成) INSERT INTO BQ_ZBTQYZ(ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000,TS0000, QZFSID ) SELECT ID0000, YZMXID, YZID00, VSYSDATE,VSYSDATE, VSYSDATE, 0 , ' ' , YPNM00, ' ' , '1' , CEIL(SUM(CS0000*MCSL00/ZHL000)),YFBMBH,SUM(CS0000),MAX(TS0000),QZFSID FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND QZFSID=1 AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,TS0000,YZID00,QZFSID; counter:=sql%rowcount; --每方取整的药品医嘱汇总(长)(根据医嘱明细汇总) INSERT INTO BQ_ZBTQYZ(ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID ) SELECT ID0000, YZMXID, YZID00, VSYSDATE,VSYSDATE, VSYSDATE, 0 , ' ' , YPNM00, ' ' , '1' , CEIL(SUM(CS0000*MCSL00/ZHL000)),YFBMBH,SUM(CS0000),MAX(TS0000),QZFSID FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND QZFSID=2 AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,TS0000,YZID00,QZFSID; --不取整和顺延药品处理 INSERT INTO BQ_ZBTQYZ(ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID ) SELECT ID0000, YZMXID, YZID00, VSYSDATE,VSYSDATE, VSYSDATE, 0 , ' ' , YPNM00, ' ' , '1' , SUM(CS0000*MCSL00/ZHL000),YFBMBH,SUM(CS0000),MAX(TS0000),QZFSID FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND QZFSID IN (3,4) AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,TS0000,YZID00,QZFSID;*/ --插入医嘱处理类别,更改医嘱的提取时间 UPDATE BQ_ZBTQYZ T SET (YZCLLB,SFKCBZ)= (SELECT YZCLLB,DECODE(YZCLLB,'自备药','0','门诊带药','0','嘱托','0','1') FROM BQ_YPYZMX X WHERE T.YZMXID=X.YZMXID), TQRQSJ= (SELECT DECODE(Z.CLBZ00,'1',TQRQSJ,NVL(TO_DATE(TQRQ00||TQSJ00,'YYYYMMDDHH24:MI:SS')+1,TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'))) FROM BQ_YPYZ00 Z WHERE T.YZID00=Z.YZID00) WHERE ID0000=PBCTQID AND SJBZ00='1'; --长期顺延药处理 UPDATE BQ_ZBTQYZ T SET JZRQSJ= (SELECT T.JZRQSJ+TRUNC((CEIL(T.YPSLSL)-T.YPSLSL)/(X.MCSL00*P.ZQCS00/Z2J000))*(P.HSZQCD/24) FROM BQ_YPYZMX X,BQ_YPYZ00 Z,BM_PC0000 P WHERE T.YZMXID=X.YZMXID AND X.YZID00=Z.YZID00 AND Z.PCID00=P.PCID00), YPSLSL=CEIL(T.YPSLSL) WHERE ID0000=PBCTQID AND SJBZ00='1' AND QZFSID=4 AND CLBZ00='0'; --农本方，修正默认发药药房 UPDATE BQ_ZBTQYZ T SET YFBMBH=(SELECT Z.YFBMBH FROM XT_YPFWFX Z,YF_YPKCXX X WHERE Z.SLBMBH=PBQH000 AND Z.YPLBBH='2' AND Z.MRBZ00='N' AND Z.YFBMBH=X.YFBMBH AND T.YPNM00=X.YPNM00 AND X.BQKDYF='Y'AND ROWNUM=1) WHERE ID0000=PBCTQID AND SJBZ00='1' AND YPLBBH='2'-- AND EXISTS (SELECT 1 from BM_YD0000 where T.YPNM00=YPNM00 AND LBBH00='2' AND jkbh00 is not null and nsbl00<>1) AND EXISTS (SELECT 1 FROM YF_YPKCXX B,XT_YPFWFX C WHERE T.YPNM00=B.YPNM00 AND C.SLBMBH=PBQH000 AND B.YFBMBH=C.YFBMBH AND B.BQKDYF='Y' AND C.MRBZ00='N' AND C.YPLBBH='2'); UPDATE BQ_ZBTQYZ T SET YFBMBH=(SELECT Z.YFBMBH FROM XT_YPFWFX Z,YF_YPKCXX X WHERE Z.SLBMBH=PBQH000 AND Z.YPLBBH=T.YPLBBH AND Z.MRBZ00='N' AND Z.YFBMBH=X.YFBMBH AND T.YPNM00=X.YPNM00 AND X.BQKDYF='Y'AND ROWNUM=1) WHERE ID0000=PBCTQID AND SJBZ00='1' --AND YPLBBH='2'-- AND EXISTS (SELECT 1 from BM_YD0000 where T.YPNM00=YPNM00 AND LBBH00='2' AND jkbh00 is not null and nsbl00<>1) AND EXISTS (SELECT 1 FROM YF_YPKCXX B,XT_YPFWFX C WHERE T.YPNM00=B.YPNM00 AND C.SLBMBH=PBQH000 AND B.YFBMBH=C.YFBMBH AND B.BQKDYF='Y' AND C.MRBZ00='N' AND C.YPLBBH=T.YPLBBH); --本次提取药品汇总(根据药品汇总) INSERT INTO BQ_YZYPHZ (ID0000, YPNM00, YPSLSL, YFBMBH, SFKCBZ) SELECT ID0000, YPNM00, SUM(YPSLSL),YFBMBH,'1'--不足 FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='1' AND YZCLLB IN ('正常','基数药','领药','出院带药') GROUP BY ID0000,YPNM00,YFBMBH; --农本方:草药YPSLSL=YPSLSL/NSBL00*2 --(2006-09-01,不乘2) UPDATE BQ_YZYPHZ Z SET YPSLSL=(SELECT ROUND(Z.YPSLSL/NSBL00,2) FROM BM_YD0000 WHERE YPNM00=Z.YPNM00) WHERE ID0000=PBCTQID AND EXISTS (SELECT 1 from BM_YD0000 where Z.YPNM00=YPNM00 AND LBBH00='2' ); --判定库存数量(毒麻处方参与判定，但不预扣) UPDATE BQ_YZYPHZ Z SET SFKCBZ='0' WHERE YPSLSL<=(SELECT YFKCSL FROM VW_BQ_YFYKKC K WHERE K.YFBMBH=Z.YFBMBH AND Z.YPNM00=K.YPNM00) AND ID0000=PBCTQID; --药房无药标志 UPDATE BQ_YZYPHZ Z SET SFKCBZ='2' WHERE ID0000=PBCTQID AND NOT EXISTS (SELECT 1 FROM VW_BQ_YFYKKC K WHERE K.YFBMBH=Z.YFBMBH AND Z.YPNM00=K.YPNM00); --修改汇总处方的库存量标志 UPDATE BQ_ZBTQYZ Z SET SFKCBZ=(SELECT SFKCBZ FROM BQ_YZYPHZ H WHERE H.YPNM00=Z.YPNM00 AND H.YFBMBH=Z.YFBMBH AND H.ID0000=PBCTQID) WHERE ID0000=PBCTQID AND SJBZ00='1' AND YZCLLB IN ('正常','基数药','领药','出院带药'); --库存不足的调整(未完成) --循环调整药品规格，以使库存满足 --插入到临时申领表 INSERT INTO BQ_YZYPSQ ( ID0000, YFBMBH, BQH000, ZYID00, ZYH000, BRXM00, YFLBID, ZYTS00, DMBZ00, YSGZH0, FJFBZ0, YPNM00, YPMC00, YZCLLB, SFKCBZ, YPGG00, QLDW00, ZHL000, CSL000, YPZSL0, LSDJ00, GJDJ00, PFDJ00, YPYFMC, PCMC00, YFSMMC, XMYFMC, SFXS00, SFZBY0, CZY000, YZID00, YZMXID, CH0000, YPLBBH , JLDW00, TS0000,ZYDJS0,BZ0000,YEXM00,DYCJSL,DYCJQL,DECJSL,DECJQL,YSZID0,TQQSSJ,TQJZSJ) SELECT B.ID0000,B.YFBMBH,X.DQBQ00,Z.ZYID00,X.ZYH000,X.XM0000,NVL(F.YFLBID,-1), NVL(B.CS0000,1),M.DMBZ00,Z.KZYS00,'1',B.YPNM00,DECODE(VSYTYM0,'Y',M.YPMC00,H.YPMC00),B.YZCLLB,--H.YPMC00 B.SFKCBZ, M.YPGG00, H.ZYFYDW, H.Z2J000, M.MCSL00, B.YPSLSL, ROUND(nvl(Y.LSDJ00, H.LSJ000)/H.K2J000*H.Z2j000,4), ROUND(nvl(H.GJPJDJ, H.GJPJDJ)/H.K2J000*H.Z2j000,4), ROUND(nvl(H.PFJ000, H.PFJ000)/H.K2J000*H.Z2j000,4), Z.YPYFMC, Z.PCMC00, NVL(SUBSTRB(Z.YYZF00,1,30),' '), M.XMYFMC,'Y','N', PCZY000, M.YZID00, B.YZMXID, X.RYCWH0, M.YPLBBH, H.JLDW00, B.TS0000, DECODE(M.YPLBBH,'2', DECODE(Z.CLBZ00, '0', NVL(B.CS0000,1)/Z.ZQCYTS*Z.JYCS00, Z.JYCS00),0), NVL(SUBSTRB(Z.YYZF00,1,20),''),Z.YEXM00,Z.DYCJSL,Z.DYCJQL,Z.DECJSL,Z.DECJQL,Z.YSZID0,to_char(nvl(B.tqrqsj,b.qyrqsj),'YYYYMMDDHH24:MI:SS') ,decode(substr(to_char(b.jzrqsj,'YYYYMMDDHH24:MI:SS'),-8),'00:00:00',to_char(b.jzrqsj-1/86400,'YYYYMMDDHH24:MI:SS'),to_char(b.jzrqsj,'YYYYMMDDHH24:MI:SS')) FROM BQ_ZBTQYZ B, BQ_YPYZ00 Z, BQ_YPYZMX M, BM_YD0000 H, YF_YPKCXX Y, ZY_BRXXB0 X, BM_YPYF00 F WHERE B.SJBZ00='1' AND ID0000=PBCTQID AND B.YZMXID=M.YZMXID AND B.YZID00=M.YZID00 AND B.YZID00=Z.YZID00 AND Z.ZYID00=X.ZYID00 AND B.YFBMBH=Y.YFBMBH(+) AND Z.YZID00=M.YZID00 AND B.YPNM00=Y.YPNM00(+) AND Z.YFID00=F.YFID00(+)and B.YPNM00=H.YPNM00; --AND B.YZCLLB IN ('正常','基数药','领药','出院带药'); --农本方:草药YPZSL0=YPZSL0/NSBL00*2,CSL=CSL/NSBL00,ZYTS00=ZYTS00*2 UPDATE BQ_YZYPSQ Z SET (ZYTS00,CSL000,YPZSL0)=(SELECT Z.ZYTS00,ROUND(Z.CSL000/NSBL00,4),ROUND(Z.YPZSL0/NSBL00,2) FROM BM_YD0000 WHERE YPNM00=Z.YPNM00) WHERE ID0000=PBCTQID AND EXISTS (SELECT 1 from BM_YD0000 where Z.YPNM00=YPNM00 AND LBBH00='2' ); --修改频次时间 UPDATE BQ_YZYPSQ B SET PCSJ00=(SELECT P.BZ0000 FROM BM_PC0000 P,BQ_YPYZ00 Z WHERE P.PCID00=Z.PCID00 AND B.YZID00=Z.YZID00) WHERE ID0000=PBCTQID; --第一次领药时，皮试药品续用的要减去一个发药单位的数量 UPDATE BQ_YZYPSQ Y SET YPZSL0=YPZSL0-1 WHERE EXISTS (SELECT 1 FROM VW_BQ_PSXYYZ Z WHERE Y.YZMXID=Z.YZMXID ) AND ID0000=PBCTQID; --插入提取异常原因 INSERT INTO BQ_TQYCJL( SBYYSM, SBLB00, YFBMBH, BQH000, ZYID00, YPNM00, TQRQ00, BGRQ00, BGSJ00, BGR000, YPZSL0, FY0000, QLDW00) SELECT DECODE(SFKCBZ, '1', '库存不足', '2', '无此药品'), DECODE(SFKCBZ, '1', '0', '2', '2'), YFBMBH, PBQH000, ZYID00, YPNM00, TO_CHAR(PTQRQ00,'YYYYMMDD'), TO_CHAR(VSYSDATE,'YYYYMMDD'), TO_CHAR(VSYSDATE,'HH24:MI:SS'), ZWXM00, DECODE(SFKCBZ,'1',YPZSL0,'2',0), 0, DECODE(SFKCBZ,'1',QLDW00,'2',NULL) FROM BQ_YZYPSQ A, BM_YGBM00 B WHERE ID0000=PBCTQID AND YZCLLB IN ('正常','基数药','领药','出院带药') AND SFKCBZ IN ('1','2') AND YGBH00=PCZY000; --************************************************************************************ --* * --* * --************************************************************************************ <> --生成医嘱执行记录,处理没有提取到药品的医嘱,包括予出院，周期顺延，定期应用，QOD等 IF (PZYID00>0) THEN --提取长嘱所有药品医嘱 IF (PCLBZ00 IS NULL) OR (PCLBZ00='0') THEN --插入长嘱西药和成药、草药明细项(注意草药，每次按整个周期提取) --长嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB,PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH, YPLBBH,ZYID00 ,SCBZ00 ) SELECT PBCTQID,Z.YZID00, 0, 0, '0', CLBZ00, '', PCID00, TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS'), NVL(TO_DATE(Z.TQRQ00||Z.TQSJ00,'YYYYMMDDHH24:MI:SS')+1,TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ),NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ))), --SF_BQ_TQRQ00(Z.KFBZ00,VKFJZRQ,VZJJZRQ,to_date(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),nvl(to_date(X.TQRQ00||X.TQSJ00,'YYYYMMDDHH24:MI:SS'),to_date(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')),Z.PCID00,X.YPNM00,null,Z.YZID00), 0,'2', 0, 0,Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z,BQ_YPYZCF F,VW_BQ_YFTQLB Y WHERE Z.YZID00=F.YZID00 AND ZYID00=PZYID00 AND Z.CLBZ00='0' AND YZZT00 IN ('1','2') AND YPLBBH LIKE VYPLBBH AND --DMBZ00='0' NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID --AND z.YZID00=e.YPID00 And F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --长期，非毒，核对和正在执行医嘱，MAX[提取日期，起始日期]'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; ELSE --************************************按病区提取*****************************-------------- FOR BR IN C_ZYBR00 LOOP --插入长嘱 IF (PCLBZ00 IS NULL) OR (PCLBZ00='0') THEN --插入长嘱西药和成药、草药明细项 --长嘱，指定用法类别 IF (PYFLBID IS NOT NULL) THEN --提取长嘱所有药品医嘱 INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB, PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH, YPLBBH, ZYID00 ,SCBZ00 ) SELECT PBCTQID,Z.YZID00, 0, 0, '0', CLBZ00, '', PCID00, TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS'), NVL(TO_DATE(Z.TQRQ00||Z.TQSJ00,'YYYYMMDDHH24:MI:SS')+1,TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ),NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ))), 0,'2', 0, 0,Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z,BQ_YPYZCF F,VW_BQ_YFTQLB Y WHERE Z.YZID00=F.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.CLBZ00='0' AND YZZT00 IN ('1','2') AND YPLBBH LIKE VYPLBBH AND-- Z.DMBZ00='0' NVL(Z.YFID00,'$*$*$*')=Y.YFID00 AND Y.TQLBID=PYFLBID AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --长嘱，不指定用法类别 ELSE INSERT INTO BQ_ZBTQYZ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00,YZCLLB,PCID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SJBZ00, YFBMBH,YPLBBH,ZYID00,SCBZ00 ) SELECT PBCTQID,Z.YZID00, 0, 0, '0', CLBZ00, '', PCID00, TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS'), NVL(TO_DATE(Z.TQRQ00||Z.TQSJ00,'YYYYMMDDHH24:MI:SS')+1,TO_DATE(F.QSRQ00||F.QSSJ00,'YYYYMMDDHH24:MI:SS')), LEAST(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ),NVL(TO_DATE(Z.TZRQ00||Z.TZSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ))), 0,'2', 0, 0,Z.ZYID00,decode(nvl(Z.TQRQ00,'A'),'A',decode(sign(Z.SJQSRQ-Z.QYRQ00),1,'1','0'),'0') FROM BQ_YPYZ00 Z,BQ_YPYZCF F WHERE Z.YZID00=F.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.CLBZ00='0' AND YZZT00 IN ('1','2')AND-- Z.DMBZ00='0' YPLBBH LIKE VYPLBBH AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 ((NVL(trim(VQYRQXZ),'N')<>'Y' AND Z.QYRQ00'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); END IF; END IF; END LOOP; END IF;--原始数据插入完毕 ---JZRQSJ by BQ-20111207-001 --update BQ_ZBTQYZ Y set JZRQSJ=least((select to_date(YTZRQ0||YTZSJ0,'YYYYMMDDHH24:MI:SS') from BQ_YPYZ00 where YZID00=Y.YZID00 and rownum=1),Y.JZRQSJ) update BQ_ZBTQYZ Y set JZRQSJ=(select least(to_date(YTZRQ0||YTZSJ0,'YYYYMMDDHH24:MI:SS'),Y.JZRQSJ) from BQ_YPYZ00 where YZID00=Y.YZID00 and rownum=1) where ID0000=PBCTQID and exists (select 1 from BQ_YPYZ00 where YZID00=Y.YZID00 and CLBZ00='0' and YTZRQ0 is not null); --开始计算次数，填写换算周期长度 UPDATE BQ_ZBTQYZ Y SET HSZQCD=(SELECT HSZQCD FROM BM_PC0000 P WHERE Y.PCID00=P.PCID00) WHERE ID0000=PBCTQID AND SJBZ00='2'; --ls_ErrorCode:='c'; --包药机药品提取周期设置 if ls_BYJTQFS='1' then update BQ_ZBTQYZ Y set JZRQSJ=(select least(TO_DATE(to_char(min(TQRQSJ),'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS')+decode(Y.SCBZ00,1,0,1),Y.JZRQSJ) from BQ_ZBTQYZ where YZID00=Y.YZID00) --decode(Y.SCBZ00,1,0,1) where ID0000=PBCTQID and SJBZ00='2' --and exists (select 1 from BQ_YPYZMX a,BM_YD0000 b where YZID00=Y.YZID00 and CLBZ00='0' and a.YPNM00=b.YPNM00 and YPCZFL='3') and exists(select 1 from BQ_YPYZ00 where YZID00=Y.YZID00 and KFBZ00='Y' ); end if; IF NOT SQL%NOTFOUND THEN --有需要处理的医嘱 UPDATE BQ_ZBTQYZ Y SET JZRQSJ=DECODE(SIGN(NVL(TQRQSJ,QYRQSJ)-QYRQSJ),1, TQRQSJ+CEIL((JZRQSJ-TQRQSJ)*24/HSZQCD)*HSZQCD/24, TRUNC(QYRQSJ)+(JZRQSJ-TRUNC(JZRQSJ)) +DECODE(SIGN((QYRQSJ-TRUNC(QYRQSJ))-(JZRQSJ-TRUNC(JZRQSJ))), -1,CEIL((JZRQSJ-QYRQSJ)*24/HSZQCD)*HSZQCD/24-1, CEIL((JZRQSJ-QYRQSJ)*24/HSZQCD)*HSZQCD/24)) WHERE YPLBBH='2' AND CLBZ00='0'AND ID0000=PBCTQID AND SJBZ00='2'; --已经启用的长期医嘱 UPDATE BQ_ZBTQYZ Y SET CS0000=(SELECT SF_BQ_YZZXCS(Y.QYRQSJ,Y.TQRQSJ,Y.JZRQSJ,P.HSZQCD,P.ZQCS00,P.ZQDW00)*NVL(ZQCYTS,1) FROM BQ_YPYZ00 Z,BM_PC0000 P WHERE Y.YZID00=Z.YZID00 AND Z.PCID00=P.PCID00 ) WHERE CLBZ00='0' AND ID0000=PBCTQID AND SJBZ00='2'; END IF; --ls_ErrorCode:='da'; --医嘱汇总(长)(根据医嘱明细汇总) INSERT INTO BQ_ZBTQYZ( ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID ,ZYID00,SFKCBZ,PCID00 ) --长期医嘱 SELECT ID0000, 0, YZID00, VSYSDATE,TQRQSJ,JZRQSJ, 0, '0' , 0, '0' , '3', 0, 0, SUM(CS0000), 0, 0, ZYID00, '0',PCID00 FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='2' AND CLBZ00='0' GROUP BY ID0000,YZID00,PCID00,JZRQSJ,ZYID00,TQRQSJ UNION ALL--短嘱 SELECT ID0000, 0, YZID00, VSYSDATE,TQRQSJ,JZRQSJ, 0 , '0' , 0, '0' , '3' ,0, 0,CS0000,0,0,ZYID00,'0',PCID00 FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='1' AND CLBZ00='1'; IF SQL%NOTFOUND THEN RAISE Ecustom; END IF; --ls_ErrorCode:='db'; DELETE FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='2';--删除临时记录 DELETE FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='3'AND CLBZ00='1';--删除短嘱 --判定是否存在能提取但无药的医嘱 --SELECT COUNT(*) INTO COUNTER FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND --NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00) AND ROWNUM=1; SELECT COUNT(*) INTO COUNTER FROM (SELECT YZID00 FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' MINUS SELECT YZID00 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' ); --ls_ErrorCode:='de'; IF COUNTER>0 THEN BEGIN INSERT INTO BQ_YZZXJL( ZYID00, YZMXID, TQDJH0, DJHLB0, FYDJH0, ZT0000, CZR000, YZID00, QSRQ00, QSSJ00, JSRQ00, JSSJ00, SJJSRQ, SJJSSJ, CZRQ00, CZSJ00, SCJSRQ, CS0000) SELECT M.ZYID00,Y.YZMXID, PBCTQID, '0' , 0 , '0', PCZY000,M.YZID00, TO_CHAR(TQRQSJ,'YYYYMMDD'), TO_CHAR(TQRQSJ,'HH24:MI:SS'), TO_CHAR(DECODE(M.KFBZ00,'Y', VKFJZRQ,VZJJZRQ),'YYYYMMDD'), TRIM(DECODE(M.KFBZ00,'Y', VKFSJFG,VZJSJFG)), TO_CHAR(JZRQSJ,'YYYYMMDD'), TO_CHAR(JZRQSJ,'HH24:MI:SS'), TO_CHAR(VSYSDATE,'YYYYMMDD'), TO_CHAR(VSYSDATE, 'HH24:MI:SS'), DECODE(M.TQRQ00, NULL, NULL, TO_CHAR(TO_DATE(M.TQRQ00, 'yyyymmdd') + 1,'yyyymmdd')||M.TQSJ00), Z.CS0000 FROM BQ_YPYZ00 M, BQ_YPYZMX Y, BQ_ZBTQYZ Z WHERE Z.YZID00=M.YZID00 AND M.YZID00=Y.YZID00 AND Z.ID0000=PBCTQID AND SJBZ00='3'AND Z.CLBZ00='0' AND Z.CS0000>0 AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ T WHERE ID0000=PBCTQID AND SJBZ00='1' AND M.YZID00=T.YZID00); --ls_ErrorCode:='df'; --整条医嘱没有提取到药品，但应该有执行记录的医嘱 --修改医嘱的提取时间,定期应用的医嘱要自动停止 --2019.09.06DECODE(Z.KFBZ00,VKFJZRQ,VZJJZRQ)-->DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ) UPDATE BQ_YPYZ00 Z SET (TQRQ00,TQSJ00,YZZT00)= (SELECT TO_CHAR(PTQRQ00,'YYYYMMDD'), TRIM(DECODE(Z.KFBZ00,'Y',VKFSJFG,VZJSJFG)), DECODE(CLBZ00,'1','3','0',DECODE(TZRQ00,NULL,'2', DECODE(SIGN(DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)-TO_DATE(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3'))) FROM BQ_ZBTQYZ M WHERE Z.YZID00=M.YZID00 AND ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' and rownum=1) WHERE BQH000=PBQH000 AND CLBZ00='0' AND YZID00 IN (SELECT YZID00 FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00)); --ls_ErrorCode:='dg'; --更改医嘱执行时间 UPDATE BQ_YPYZMX X SET (TQRQ00,TQSJ00)= (SELECT TO_CHAR(GREATEST(NVL(TO_DATE(TQRQ00||TQSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)),'YYYYMMDD'), TO_CHAR(GREATEST(NVL(TO_DATE(TQRQ00||TQSJ00,'YYYYMMDDHH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)),DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)),'HH24:MI:SS') FROM BQ_ZBTQYZ M,BQ_YPYZ00 Z WHERE M.YZID00=X.YZID00 AND X.YZID00=Z.YZID00 AND ID0000=PBCTQID AND SJBZ00='3' AND M.CLBZ00='0' and rownum=1) WHERE YZID00 IN (SELECT YZID00 FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00)); --ls_ErrorCode:='dh'; --修改包药机药品 --ls_ErrorCode:='e'; if ls_BYJTQFS='1' then UPDATE BQ_YPYZ00 Z SET (TQRQ00)=(select to_char(JZRQSJ-1,'YYYYMMDD') from BQ_ZBTQYZ where ID0000=PBCTQID AND SJBZ00='3'AND YZID00=Z.YZID00 and rownum=1) WHERE BQH000=PBQH000 AND CLBZ00='0' and KFBZ00='Y' AND YZID00 IN (SELECT YZID00 FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00)) ; update BQ_YPYZMX X SET (TQRQ00)= (SELECT TO_CHAR(JZRQSJ,'YYYYMMDD') FROM BQ_ZBTQYZ M WHERE M.YZID00=X.YZID00 AND ID0000=PBCTQID AND SJBZ00='3' AND M.CLBZ00='0' and rownum=1) WHERE YZID00 IN (SELECT YZID00 FROM BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00) and exists (select 1 from BQ_YPYZ00 WHERE YZID00=A.YZID00 AND KFBZ00='Y') ) --and exists(select 1 from BM_YD0000 where YPNM00=X.YPNM00 and YPCZFL='3') ; end if; --ls_ErrorCode:='f'; DELETE BQ_ZBTQYZ A WHERE ID0000=PBCTQID AND SJBZ00='3' AND CLBZ00='0' AND NOT EXISTS (SELECT 1 FROM BQ_ZBTQYZ B WHERE ID0000=PBCTQID AND SJBZ00='1' AND A.YZID00=B.YZID00); EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; --ls_ErrorCode1:=to_char(SQLCODE); IF SQLCODE=-2290 THEN --yzzxjl.qsrq00||qssj00-20050 for BQ-20131210-002 RAISE_APPLICATION_ERROR(-20050,substrb(SQLERRM,1,220)); END IF; END; END; END IF; --德化要求自备药可以显示出来，但金额必须是0 update BQ_YZYPSQ SET LSDJ00=0,GJDJ00=0,PFDJ00=0 WHERE ID0000=PBCTQID AND YZCLLB NOT IN ('正常','基数药','领药','出院带药'); --删除不需要的数据 DELETE BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0'; DELETE BQ_YZYPHZ WHERE ID0000=PBCTQID; COMMIT; --医嘱处理完毕，没有生成处方 SELECT COUNT(*) INTO COUNTER FROM BQ_YZYPSQ WHERE ID0000=PBCTQID; IF COUNTER=0 THEN Verrmsg:='医嘱处理完毕，没有生成处方'; RAISE Ecustom; END IF; EXCEPTION WHEN Ecustom THEN--没有医嘱可以提取; RAISE_APPLICATION_ERROR(-20030, substrb(Verrmsg||VPARAMS,1,100)); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20060, substrb('数据没有找到！*'||VPARAMS,1,100)); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20070, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,100)); END SP_BQ_YPYZTQ; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YPYZTQ