create or replace procedure SP_BQ_YPYZTQ_JP --病人的药品医嘱提取成处方 -- 提取的内容根据以下情况组合： -- 提取对象：单个病人提取，全区病人提取； -- 医嘱长零：长期医嘱，零星医嘱；长零同时提取(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 ) 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; --医嘱时间分割点 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); --是否使用通用名 VYPNM00 BQ_YPYZMX.YPNM00%TYPE; --药品内码 VYPNMNEW YF_YPKCXX.YPNM00%TYPE; --新药品内码 VYKKCSL YF_YPKCXX.YKKCSL%TYPE; --预扣库存数量 VYKKCSLNew YF_YPKCXX.YKKCSL%TYPE; --新药品预扣库存数量 VSFXY00 Boolean; --是否新药 VSFSC00 Boolean; --是否首次 VSYTYMYK CHAR(1); --BM_YD0000.YPMC00是否通用名:1是 VSFZDTH CHAR(1); --库存不足时，是否同类药品自动替换 VYPZJL0 NUMBER(31,3); --药品总剂量(发药单位*转换率) VYPZJLNew NUMBER(31,3); --新药品总剂量(发药单位*转换率) VQGBRID BM_BRXXB0.BRID00%type; --前个病人ID v_yznr01 varchar2(100); v_ybfzxlb BM_BRXXB0.YBLB00%type; --医保分中心类别 v_ybzxlb IC_YBBRLB.YBLB00%type; --医保中心类别 v_ybbrlb BM_BRXXB0.YBBRLB%type; --医保病人类别 v_fbbh00 BM_BRXXB0.FBBH00%type; --费别编号 v_sfxmid XT_ZLSFGX.SFXMID%type; --收费项目ID v_zfbl00 BM_YBSFDY.ZFBL00%type; --自付比率 v_yblb00 BM_YBSFDY.YBLB00%type; --医保类别 --v_ypmc00 BM_YD0000.YPMC00%type; --药品名称 --V_YPLBBH BM_YD0000.LBBH00%type; --药品类别编号 v_ypgg00 BM_YD0000.YPGG00%type; --药品规格 v_jldw00 BM_YD0000.JLDW00%type; --剂量单位 v_zyfydw BM_YD0000.ZYFYDW%type; --住院发药单位 v_z2j000 BM_YD0000.Z2J000%type; --Z/J转换率 v_qzfsid BM_YD0000.QZFS00%type; --取整方式 v_dmbz00 BQ_YPYZ00.DMBZ00%type; --毒麻标识 v_lyl000 BQ_YPYZMX.LYL000%type; --领药量 v_zqcs00 BM_PC0000.ZQCS00%type; --周期次数 p_zqs000 BQ_YPYZ00.zqs000%type; --周期数 v_xm0000 zy_brxxb0.xm0000%type; -- v_qqtqtg char(1); --某个病人医嘱皮试未通过时，是否允许全区提取 v_sfjzsj CHAR(1);--是否急诊时间 Y 是 N否 V_JZYFBM YF_YPKCXX.YFBMBH%type; --急诊药房部门编号 V_JPYFBH YF_YPKCXX.YFBMBH%type; --静配药房部门编号 BQ_CYDYYZSCZYLSCFFS varchar2(10);--出院带药医嘱生成住院零散处方方式 0在医嘱核对的触发器中生成 1医嘱提取过程生成 默认 为0 --病区当前在院病人 CURSOR C_ZYBR00 IS SELECT BRID00,ZYID00,XM0000,RYCWH0,ZYH000,TO_DATE(CYDJRQ||CYDJSJ,'YYYYMMDD HH24:MI:SS')"CYRQSJ" FROM ZY_BRXXB0 WHERE CYDJRQ='20991231' AND DQBQ00=PBQH000 AND 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.QYRQ000 THEN VZYID00:=TO_CHAR(PZYID00); BEGIN SELECT TO_NUMBER(VALUE0) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BR'; EXCEPTION WHEN OTHERS THEN VWHTQTS:=99999;--不限定往后提取的天数 END; ELSE BEGIN SELECT TO_NUMBER(VALUE0) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BQ'; EXCEPTION WHEN OTHERS THEN VWHTQTS:=99999;--不限定往后提取的天数 END; END IF; --出院带药医嘱生成病人自领的住院零散处方 select nvl(max(trim(value0)),'0') into BQ_CYDYYZSCZYLSCFFS from XT_XTCS00 where NAME00='BQ_CYDYYZSCZYLSCFFS'; IF PTQRQ00 IS NULL THEN Verrmsg:='没有提取时间，无法提取'; RAISE Ecustom; ELSIF (TRUNC(PTQRQ00)-TRUNC(VSYSDATE)>VWHTQTS) THEN--提取时间限制 Verrmsg:='医嘱只能提前提取'||TO_CHAR(VWHTQTS)||'天'; RAISE Ecustom; END IF; select nvl(max(trim(value0)),'N') into v_qqtqtg from xt_xtcs00 where name00='BQ_PSWTGQQTQ'; BEGIN --SELECT VALUE0 INTO VKFSJFG FROM XT_XTCS00 WHERE NAME00='BQ_KFYZSJFG'; --SELECT VALUE0 INTO VZJSJFG FROM XT_XTCS00 WHERE NAME00='BQ_ZJYZSJFG'; VKFSJFG:='00:00:00'; VZJSJFG:='00:00:00'; --获得提取的截至日期时间(起始时间为当日) 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 ; --判断是否急诊时间 begin select SF_XT_GETSFJZSJ('') into v_sfjzsj from dual; select BMBH00 into V_JZYFBM from BM_BMBM00 where BMXZ00 IN ('2','3') and BMMC00 like '%急诊%' and rownum=1; exception when others then v_sfjzsj:='N'; end; --静配药房 begin select BMBH00 into V_JPYFBH from BM_BMBM00 where BMXZ00 IN ('2','3') and BMBH00 in (select to_number(trim(Value0)) from XT_XTCS00 where Name00='BQ_ZDPSJPYF');--自动配送静配药房 exception when others then V_JPYFBH:=0; end; --20031007 --检验是否有未按规则拆分的长嘱 FOR BR IN C_ZYBR00 LOOP BEGIN IF (PYFLBID IS NOT NULL) THEN --指定用法类别 SELECT Z.YZID00,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,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,trim(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 (Z.YPYFMC IS NULL OR Z.YPYFMC 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,trim(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 (Z.YPYFMC IS NULL OR Z.YPYFMC 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,YYRQ00)--计算次数 SELECT PBCTQID, X.YZID00,YZMXID,NVL(TQYPNM,YPNM00), SFDZGG,'1',YZCLLB,Z.PCID00, MCSL00, TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), DECODE(Z.kfbz00,'Y',VKFJZRQ,VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24, '1',LYL000, decode(v_sfjzsj,'Y',v_JZYFBM,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)),--中药时，为帖数 Z.QYRQ00 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起用日期时间，非毒，即可提取 --草药必须每次提取一个周期 --短嘱，不指定用法类别 ELSE INSERT INTO BQ_ZBTQYZ ( ID0000, YZID00, YZMXID, YPNM00, SFDZGG, CLBZ00, YZCLLB,PCID00, MCSL00, QYRQSJ, TQRQSJ, JZRQSJ, SJBZ00, YPSLSL, YFBMBH, YPLBBH,ZYID00, CS0000,YYRQ00)--计算次数 SELECT PBCTQID, X.YZID00,YZMXID,NVL(TQYPNM,YPNM00), SFDZGG,'1',YZCLLB,Z.PCID00, MCSL00, TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24 '1', LYL000, decode(v_sfjzsj,'Y',v_JZYFBM,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)),--中药时，为帖数 Z.QYRQ00 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=NVL(X.TQRQ00,Z.QYRQ00) and GREATEST(F.ZXRQ00||F.ZXSJ00,NVL(X.TQRQ00||X.TQSJ00,F.ZXRQ00||F.ZXSJ00)) =NVL(X.TQRQ00,Z.QYRQ00) and GREATEST(F.ZXRQ00||F.ZXSJ00,NVL(X.TQRQ00||X.TQSJ00,F.ZXRQ00||F.ZXSJ00)) > Verrmsg := Verrmsg; end loop; FOR BR IN C_ZYBR00 LOOP if v_qqtqtg = 'Y' then IF (PYFLBID IS NOT NULL) THEN --指定用法类别 SELECT max(Z.YZID00),trim(max(M.YPMC00)),trim(max(k.xm0000)) INTO VYZID00,VYPMC00,v_xm0000 FROM BQ_YPYZMX M,BQ_YPYZ00 Z ,VW_BQ_YFTQLB Y, ZY_BRXXB0 K WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.ZYID00=K.ZYID00 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=BR.BRID00 GROUP BY YPNM00) X,YJ_BRGMJL J 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 (Z.YPYFMC IS NULL OR Z.YPYFMC 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 max(Z.YZID00),trim(max(M.YPMC00)),trim(max(k.xm0000)) INTO VYZID00,VYPMC00,v_xm0000 FROM BQ_YPYZMX M,BQ_YPYZ00 Z, ZY_BRXXB0 K WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=BR.ZYID00 AND Z.ZYID00=K.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 (Z.YPYFMC IS NULL OR Z.YPYFMC NOT LIKE '%脱敏%' ) AND M.YPLBBH LIKE VYPLBBH AND CLBZ00 LIKE VCLBZ00 AND ROWNUM=1; END IF; if VYZID00 is not null then goto CONTINUE_LOOP2; end if; end if; --插入短嘱汇总，医嘱已经算好领药量(发药单位) 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,YYRQ00 )--计算次数 SELECT PBCTQID, X.YZID00,YZMXID,NVL(TQYPNM,YPNM00), SFDZGG,'1',YZCLLB,Z.PCID00, MCSL00, TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'), DECODE(Z.KFBZ00,'Y',VKFJZRQ,VZJJZRQ)-1, --TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS')+Z.ZQS000*P.HSZQCD/24, '1', LYL000, decode(v_sfjzsj,'Y',v_JZYFBM,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)),--中药时，为帖数 Z.QYRQ00 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=NVL(X.TQRQ00,Z.QYRQ00) and GREATEST(F.ZXRQ00||F.ZXSJ00,NVL(X.TQRQ00||X.TQSJ00,F.ZXRQ00||F.ZXSJ00)) =NVL(X.TQRQ00,Z.QYRQ00) and GREATEST(F.ZXRQ00||F.ZXSJ00,NVL(X.TQRQ00||X.TQSJ00,F.ZXRQ00||F.ZXSJ00)) > Verrmsg := Verrmsg; END LOOP; END IF;--原始数据插入完毕 --********************开始计算领药量*******************************--------------------- --填写换算周期长度,取整方式,天数,转换率 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=1,--天数 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 -- Verrmsg:='没有医嘱可以提取，提取日期大于病人的预出院日期！*'; -- 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'; --取整后药品医嘱汇总(长)(根据医嘱明细汇总) INSERT INTO BQ_ZBTQYZ( ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID ,ZYID00,SFKCBZ,YPLBBH,YYRQ00) 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,YYRQ00 FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,YZID00,QZFSID,JZRQSJ,ZYID00,YPLBBH,YYRQ00; --插入医嘱处理类别,更改医嘱的提取时间 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'; --急诊时间，就按急诊药房处理 if v_sfjzsj='N' then 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); end if; --做静配药房处理BQ-20150505-001 if V_JPYFBH>0 and v_sfjzsj='N' then update BQ_ZBTQYZ T set YFBMBH=V_JPYFBH where ID0000=PBCTQID and SJBZ00='1' and exists(select 1 from BQ_YPYZ00 where YZID00=T.YZID00 and PSPC00 is not null) and (yyrq00>to_char(sysdate,'YYYYMMDD') or exists (select 1 from bq_ypyz00 a,bq_ypyzmx b,bm_yd0000 c where a.yzid00=b.yzid00 and a.yzid00=T.yzid00 and b.ypnm00=c.ypnm00 and (a.YPYFMC='静脉高营养' or exists (select 1 from BM_TYZD00 where NBBH00=C.ZSDYLB and MC0000='肿瘤药品'))) ); end if; --本次提取药品汇总(根据药品汇总) 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' AND jkbh00 is not null); --判定库存数量(毒麻处方参与判定，但不预扣) 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,YSZID0, YYRQ00,PSPC00,SHBZ00,SFR000,SFRQ00,SFSJ00,TSYS00) 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)/ZQCYTS*JYCS00,JYCS00),0), NVL(SUBSTRB(Z.YYZF00,1,20),''),Z.YEXM00,Z.YSZID0,B.YYRQ00,decode(v_sfjzsj,'Y','',Z.PSPC00), Z.SHBZ00,Z.SFR000,nvl(Z.SFRQ00,'20991231'),Z.SFSJ00,Z.TSYS00 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' AND jkbh00 is not null); --修改频次时间 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 z set sfkcbz='3' where ID0000=Pbctqid and pspc00 is not null and yyrq00=to_char(sysdate,'YYYYMMDD') --2014.12.19 去掉and zyid00=Pzyid00 for BQ-20141219-001 update BQ_YZYPSQ z set pspc00=null where ID0000=Pbctqid and pspc00 is not null and yyrq00<=to_char(sysdate,'YYYYMMDD') and not exists (select 1 from bq_ypyz00 a,bq_ypyzmx b,bm_yd0000 c where a.yzid00=b.yzid00 and a.yzid00=z.yzid00 and b.ypnm00=c.ypnm00 and (a.YPYFMC='静脉高营养' or exists (select 1 from BM_TYZD00 where NBBH00=C.ZSDYLB and MC0000='肿瘤药品'))); --第一次领药时，皮试药品续用的要减去一个发药单位的数量:移到自动替换后面 --插入提取异常原因 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 ,BM_YGBM00 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 ) 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 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--利用索引 GREATEST(F.QSRQ00||F.QSSJ00,DECODE(Z.TQRQ00,NULL,F.QSRQ00||F.QSSJ00,TO_CHAR(TO_DATE(Z.TQRQ00||Z.TQSJ00,'YYYYMMDDHH24:MI:SS')+1,'YYYYMMDDHH24:MI:SS'))) > Verrmsg := Verrmsg; END LOOP; END IF;--原始数据插入完毕 --开始计算次数，填写换算周期长度 UPDATE BQ_ZBTQYZ Y SET HSZQCD=(SELECT HSZQCD FROM BM_PC0000 P WHERE Y.PCID00=P.PCID00) WHERE ID0000=PBCTQID AND SJBZ00='2'; 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; --医嘱汇总(长)(根据医嘱明细汇总) INSERT INTO BQ_ZBTQYZ( ID0000, YZMXID, YZID00, QYRQSJ, TQRQSJ, JZRQSJ, MCSL00, SFDZGG, YPNM00, CLBZ00, SJBZ00, YPSLSL, YFBMBH, CS0000, TS0000, QZFSID ,ZYID00,SFKCBZ ) --长期医嘱 SELECT ID0000, 0, YZID00, VSYSDATE,TQRQSJ,JZRQSJ, 0 , '0' , 0, '0' , '3' ,0, 0,SUM(CS0000),0,0,ZYID00,'0' FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='2' AND CLBZ00='0' GROUP BY ID0000,YZID00,JZRQSJ,ZYID00,TQRQSJ UNION ALL--短嘱 SELECT ID0000, 0, YZID00, VSYSDATE,TQRQSJ,JZRQSJ, 0 , '0' , 0, '0' , '3' ,0, 0,CS0000,0,0,ZYID00,'0' FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='1' AND CLBZ00='1'; --IF SQL%NOTFOUND THEN -- RAISE Ecustom; --END IF; 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' ); 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'), TO_CHAR(DECODE(M.KFBZ00,'Y',VKFJZRQ, decode(Z.YYRQ00||'YYRQ','YYRQ',VZJJZRQ,TO_CHAR(to_date(Z.YYRQ00,'YYYYMMDD')+1,'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), 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); --整条医嘱没有提取到药品，但应该有执行记录的医嘱 --修改医嘱的提取时间,定期应用的医嘱要自动停止 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,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') 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)); --更改医嘱执行时间 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') 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)); 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; IF SQLCODE=-2290 THEN --yzzxjl.qsrq00||qssj00SQ.YPNM00 THEN VYPNM00:=SQ.YPNM00; VYPZJL0:=round(SQ.YPZSL0*SQ.ZHL000,3); SELECT NVL(MAX(YKKCSL),0) INTO VYKKCSL FROM YF_YPKCXX WHERE YFBMBH=SQ.YFBMBH AND YPNM00=SQ.YPNM00 AND PDXH00>'0'; VSFXY00:=True; VSFSC00:=True; ELSE VYPZJL0:=VYPZJL0+round(SQ.YPZSL0*SQ.ZHL000,3); VSFXY00:=False; END IF; --是否另外一个病人 IF VQGBRID<>SQ.BRID00 then --医保分中心类别和医保病人类别 select YBLB00,YBBRLB,FBBH00 into v_ybfzxlb,v_ybbrlb,v_fbbh00 from BM_BRXXB0 where BRID00 = SQ.BRID00; --医保中心类别 select YBZXLB into v_ybzxlb from IC_YBBRLB where YBLB00 = v_ybfzxlb and FBBH00=v_fbbh00; end if; --旧药库存不足或没药 IF VYPZJL0>VYKKCSL THEN VPARAMS:=Trim(SQ.RYCWH0)||'床病人:'||SQ.XM0000||'医嘱号为'||To_Char(SQ.YZID00)||',"'||SQ.YPMC00||'"药品自动替换时出错'; --查找同类的药品 --另外一种旧药开始 或者 同种药品是否首次 IF VSFXY00 or VSFSC00 then IF VSYTYMYK='0' THEN --BM_YD0000.YPMC00非通用名 SELECT NVL(MAX(Z.YPNM00),0),NVL(MAX(Z.YKKCSL),0) INTO VYPNMNEW,VYKKCSLNEW FROM YF_YPKCXX Z,BM_YD0000 K WHERE Z.YFBMBH=SQ.YFBMBH AND Z.YPNM00=K.YPNM00 AND Z.PDXH00>'0' AND Z.YPNM00<>VYPNM00 AND EXISTS (SELECT 1 FROM BM_YD0000 S WHERE YPNM00=VYPNM00 --AND DECODE(YPMCLB,'0',YPTYM0,YPMC00)=DECODE(K.YPMCLB,'0',K.YPTYM0,K.YPMC00) --索引没用到 AND YPTYM0=K.YPTYM0 AND JXBH00=K.JXBH00); else SELECT NVL(MAX(Z.YPNM00),0),NVL(MAX(Z.YKKCSL),0) INTO VYPNMNEW,VYKKCSLNEW FROM YF_YPKCXX Z,BM_YD0000 K WHERE Z.YFBMBH=SQ.YFBMBH AND Z.YPNM00=K.YPNM00 AND Z.PDXH00>'0' AND Z.YPNM00<>VYPNM00 AND EXISTS (SELECT 1 FROM BM_YD0000 S WHERE YPNM00=VYPNM00 AND YPMC00=K.YPMC00 AND JXBH00=K.JXBH00); end if; VYPZJLNew:=round(SQ.YPZSL0*SQ.ZHL000,3); if VYPNMNEW>0 then --找到替换药品 --自付比率，医保类别 begin select ZFBL00,YBLB00 into v_zfbl00,v_yblb00 from VW_BM_YBSFDY where SFYP00 = 'Y' and YBZXLB = v_ybzxlb and YBBRLB = v_ybbrlb and SFXMID = VYPNMNEW and FBBH00 = v_fbbh00 and ROWNUM = 1; exception when No_Data_Found then begin v_zfbl00 := 1; v_yblb00 := '7'; end; end; --药品规格，剂量单位，住院发药单位，Z/J转换率，毒麻标识,药平类别编号 select YPGG00,JLDW00,ZYFYDW,Z2J000,QZFS00,Decode(MZDJ00||DPDJ00||decode(SJDJ00,'0','0','1'),'000','0','1') into v_ypgg00,v_jldw00,v_zyfydw,v_z2j000,v_qzfsid,v_dmbz00 from BM_YD0000 where YPNM00 = VYPNMNEW; end if; VSFSC00:=False; --旧药继续 else VYPZJLNew:=VYPZJLNew+round(SQ.YPZSL0*SQ.ZHL000,3); end if; --另外一种旧药开始 或者 同种药品是否首次 结束 if VYPZJLNew<=VYKKCSLNEW then--新药品数量足够发 --计算新药品领药量 if SQ.yzcllb in ('嘱托','自备药','门诊带药') then v_lyl000 := 0; else --if BQ.yplbbh = '2' then--计算草药医嘱领药量 -- v_lyl000 := SF_BQ_LYLJS0(v_qzfsid,p_mcsl00,v_z2j000,1,p_zqcyts); if SQ.clbz00 = '0' then--计算西成药长期医嘱领药量 begin select ZQCS00 into v_zqcs00 from BM_PC0000 where PCID00 = (select pcid00 From bq_ypyz00 where yzid00=SQ.yzid00); exception when No_Data_Found then v_zqcs00 := 1; end; select zqs000 into p_zqs000 from bq_ypyz00 where yzid00=SQ.yzid00; v_lyl000 := SF_BQ_LYLJS0(v_qzfsid,SQ.mcsl00,v_z2j000,v_zqcs00,p_zqs000); elsif SQ.clbz00 = '1' then--西成药短期医嘱领药量 v_lyl000 := SQ.lyl000; end if; end if; --其他金额修改放在触发器控制 update BQ_YZYPSQ set YPNM00=VYPNMNEW, SFKCBZ= 0 , YPGG00=v_ypgg00, QLDW00=v_zyfydw, ZHL000=v_z2j000, ypzsl0=(select ROUND(DECODE(SQ.CLBZ00,'1',v_lyl000,DECODE(v_qzfsid,0,SUM(CS0000*CEIL(MCSL00/v_z2j000)),--每次取整 --ZHL000改为v_zyfydw 1,CEIL(SUM(CS0000*MCSL00/v_z2j000)),--每天取整 2,CEIL(SUM(CS0000*MCSL00/v_z2j000)),--每方取整 4,SUM(CS0000*MCSL00/v_z2j000), --每方取整后顺延 3,SUM(CS0000*MCSL00/v_z2j000))+0.0049),2)--不取整 FROM BQ_ZBTQYZ where ID0000=PBCTQID and SJBZ00='0' and zyid00=SQ.zyid00 and yzmxid=SQ.yzmxid) where id0000=PBCTQID and yzmxid=SQ.yzmxid; --医嘱药品自动替换 update BQ_YPYZMX set YPNM00 = VYPNMNEW, --药品内部编号 YPGG00 = v_ypgg00, --药品规格 QZFSID = v_qzfsid, --取整方式 --MCSL00 = p_mcsl00, --每次数量(剂量单位的每次剂量) ZYFYDW = v_zyfydw, --住院发药单位(Z)(住院药房维护)(冗余) Z2J000 = v_z2j000, --住院发药/基本单位转换率(默认药房) ZFBL00 = v_zfbl00, --自付比率 YBLB00 = v_yblb00, --医保类别 LYL000 = v_lyl000, --领药量(发药单位,临时、用药医嘱使用) DMBZ00 = v_dmbz00 --毒麻标识 where yzmxid=SQ.yzmxid; --生成医嘱内容 /*select decode(YEXM00,null,'','('||YEXM00||')')||decode(yplbbh,'2',to_char(ZQCYTS)||'贴','')||Trim(PCMC00)||' '||Trim(YPYFMC)||' ' ||Decode(CLBZ00,'0','',Decode(ZQS000,1,'',To_Char(ZQS000)||Trim(ZQDW00))) into v_yznr01 from BQ_YPYZ00 where YZID00 = SQ.yzid00; SP_BQ_YZNRSC(SQ.yzid00,v_yznr01); */ end if; --新药品数量足够发 结束 --旧药库存足够该条医嘱发药 else update BQ_YZYPSQ set SFKCBZ= 0 where id0000=PBCTQID and yzmxid=SQ.yzmxid; end if; --旧药库存不足或没药 结束 VQGBRID:=SQ.BRID00; END LOOP; end if; --第一次领药时，皮试药品续用的要减去一个发药单位的数量 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; --删除不需要的数据 DELETE BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0'; --出院带药医嘱按病人自领方式，生成YF_ZYCF00 if BQ_CYDYYZSCZYLSCFFS='1' then update BQ_YZYPSQ SET SFKSLY='N' WHERE YZCLLB='出院带药' and id0000=PBCTQID; end if; --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(-20031, substrb(Verrmsg||'！*'||VPARAMS,1,220)); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20061, substrb('数据没有找到！*'||VPARAMS,1,220)); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20071, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,220)); END SP_BQ_YPYZTQ_JP;