CREATE OR REPLACE PROCEDURE SP_BQ_YPYZTQ_XZ ( PCZY000 IN NUMBER, --操作员 PZYID00 IN NUMBER, --住院流水号 PBQH000 IN NUMBER, --病区号 PTQRQ00 IN DATE , --提取日期,时间无效 PYZID00 IN char , --医嘱ID串 PBCTQID OUT NUMBER, --本次提取ID PTQBZ00 in number default 0 --提取标志 0 病区系统提取 1 手术麻醉系统提取 ) AS I NUMBER(3); J NUMBER(3); INIT NUMBER(3); COUNTER NUMBER(10); STRYZID VARCHAR2(2550); VKFJZRQ DATE; --本次医嘱提取截至日期(含时间) VZJJZRQ DATE; --本次医嘱提取截至日期(含时间) VKFJZSJ CHAR(16); --同上,char型 VZJJZSJ CHAR(16); --同上,char型 VKFSJFG XT_XTCS00.VALUE0%TYPE; --医嘱时间分割点 VZJSJFG XT_XTCS00.VALUE0%TYPE; --医嘱时间分割点 E_MYTQSJ EXCEPTION; --没有提取日期，无法提取 E_YZSJFGCSCW EXCEPTION; --系统参数:医嘱时间分割点错误 E_MYYZTQ EXCEPTION; --没有医嘱可以提取; E_YZCLWB EXCEPTION; --医嘱处理完毕，没有生成处方; E_CFQWT EXCEPTION; --医嘱拆分的触发器问题 E_YZZNDTTQ EXCEPTION; --医嘱只能当天提取 E_PSWTG EXCEPTION; --皮试未通过 E_CSCW EXCEPTION; --参数错误 E_YCXXZ EXCEPTION; --预撤销限制 VCYRQSJ DATE; --病人预出院日期和时间 VSYSDATE DATE; VBRID00 ZY_BRXXB0.BRID00%TYPE; VYBZXLB IC_YBBRLB.YBLB00%TYPE; VYBBRLB BM_BRXXB0.YBBRLB%TYPE; VFBBH00 BM_BRXXB0.YBBRLB%TYPE; VPARAMS VARCHAR2(2550); --错误时传递参数 VWHTQTS NUMBER(5); --医嘱允许往后提取的天数 VMYYZTQ CHAR(1); VERRTIP VARCHAR2(255); --错误提示 VSYTYM0 CHAR(1); --是否使用通用名 ls_BYJTQFS varchar2(5); --包药机提取方式 ls_SSYZSFBYXBRTQ XT_XTCS00.VALUE0%type; -- MODIFICATION HISTORY -- Person Date Comments -- qks 2007.09.05 BQ_YZYPSQ.YPMC00是否使用通用名 -- yangy 2007.10.27 西成药的药品提取时，发药药房可以自动改变 -- zhangwz 2011.06.17 相关业务表增加YEXM00数据的插入. -- zhangwz 2011.12.20 增加医嘱预停止时间处理 by BQ-20111207-001. -- dsm 2012.07.04 包药机的药品特殊设置提取日期 for BQ-20120629-001 -- dsm 2012.07.04 BQ-20120629-001加参数控制 for BQ-20120711-001 -- liuj 2012.07.31 当药品皮试用法选择了免试时，提取药品还会提示有药品皮试未执行或未通过，不能提取药品。修正该问题 for BQ-20120725-001 -- laijg 2012.08.31 往BQ_YZYPSQ写入 DYCJSL,DYCJQL,DECJSL,DECJQL字段值 by ZYYS-20120620-001 -- liuj 2013.11.29 去掉条件jkbh00 is not null for BQ-20131111-002 -- dsm 2013.12.13 BQ_BQJTQFS参数，在首日次数启用下，提取的药品数据量有问题 for BQ-20131213-002 -- dsm 2014.01.14 口服提取有时BQ_YZZXJL的QSRQ00不对 for BQ-20140116-002 -- dsm 2014.05.09 相关业务表写入YSZID0 for BQ-20140509-001 -- daihq 2014.09.17 增加参数SS_SSYZSFBYXBRTQ控制手术医嘱是否不允许病区提取 for SSMZ-20140828-001 -- dsm 2018.01.06 老年按天提取处理 for BQ-20180106-001 -- dsm 2018.05.22 预撤销医嘱限制 for BQ-20180509-001 -- dsm 2019.07.25 if ls_BYJTQFS='1' 增加 ID0000=PBCTQID 否则JZRQSJ会更新错误 BQ-20190726-001 BEGIN VSYSDATE:=SYSDATE; --BQ_BQJTQFS 1在过程中限制 包药机药品只提取1天 begin SELECT trim(VALUE0) into ls_BYJTQFS FROM XT_XTCS00 WHERE NAME00='BQ_BQJTQFS'; exception when others then ls_BYJTQFS:='0' ; end; --手术医嘱是否不允许病区提取 begin SELECT nvl(max(trim(VALUE0)),'N') into ls_SSYZSFBYXBRTQ FROM XT_XTCS00 WHERE NAME00='SS_SSYZSFBYXBRTQ'; exception when others then ls_SSYZSFBYXBRTQ:='N'; end; IF (NVL(PZYID00,0)<=0) OR (NVL(PBQH000,0)<=0) OR (PTQRQ00 IS NULL) OR (NVL(PCZY000,0)<=0) OR (PYZID00 IS NULL) THEN RAISE E_CSCW;--参数错误 END IF; if ls_SSYZSFBYXBRTQ = 'Y' then VERRTIP:='没有医嘱可以提取或该医嘱为手术医嘱！*'; else VERRTIP:='没有医嘱可以提取！*'; end if; VPARAMS:='SP_BQ_YPYZTQ('||NVL(TO_CHAR(PCZY000),'NULL')||','||NVL(TO_CHAR(PZYID00),'NULL')||','|| NVL(TO_CHAR(PBQH000),'NULL')||','||TRIM(PYZID00)||','||NVL(TO_CHAR(PTQRQ00,'YYYYMMDD'),'NULL')||',PBCTQID)'; BEGIN SELECT TO_NUMBER(VALUE0) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BR'; EXCEPTION WHEN OTHERS THEN VWHTQTS:=99999;--不限定往后提取的天数 END; IF PTQRQ00 IS NULL THEN RAISE E_MYTQSJ;--没有提取时间，无法提取 ELSIF TRUNC(PTQRQ00)-TRUNC(VSYSDATE)>VWHTQTS THEN--提取时间限制 RAISE E_YZZNDTTQ; 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'; --获得提取的截至日期时间(起始时间为当日) VKFJZRQ:=TO_DATE(TO_CHAR(TRUNC(PTQRQ00)+1,'YYYYMMDD')||VKFSJFG,'YYYYMMDDHH24:MI:SS'); VZJJZRQ:=TO_DATE(TO_CHAR(TRUNC(PTQRQ00)+1,'YYYYMMDD')||VZJSJFG,'YYYYMMDDHH24:MI:SS'); VKFJZSJ:=TO_CHAR(VKFJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 VZJJZSJ:=TO_CHAR(VZJJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 EXCEPTION WHEN OTHERS THEN RAISE E_YZSJFGCSCW;--系统参数:医嘱时间分割点错误 END ; DELETE FROM BM_YPJJB0_TMP; --将药品医嘱ID插入到临时表 STRYZID:=TRIM(PYZID00); IF LENGTH(STRYZID)>0 THEN J:=1;--都号基数起 INIT:=1;--起始位 LOOP I:=INSTR(PYZID00,',',1,J); IF I=0 THEN--结束 IF J=1 THEN --都没有找到逗号 INSERT INTO BM_YPJJB0_TMP(YZYYP0)VALUES(TO_NUMBER(STRYZID)); ELSE --最后一次J>1 INSERT INTO BM_YPJJB0_TMP(YZYYP0)VALUES(TO_NUMBER(SUBSTR(STRYZID,INIT,LENGTH(STRYZID)-INIT+1))); END IF; EXIT;--退出 END IF; counter:=TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT)); INSERT INTO BM_YPJJB0_TMP (YZYYP0) VALUES (TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT))); J:=J+1; INIT:=I+1; --变更起始位置 END LOOP; END IF; --删除预撤销的医嘱 for BQ-20180509-001 select count(*) into COUNTER from BM_YPJJB0_TMP a where exists(select 1 from BQ_YPYZ00 where YZID00=a.YZYYP0 and nvl(YCXBZ0,'0')='1'); if COUNTER>0 then raise E_YCXXZ; end if; --检验是否有未按规则拆分的长嘱 SELECT COUNT(*) INTO COUNTER FROM BQ_YPYZ00 Z,BQ_YPYZMX M WHERE Z.YZZT00 IN ('1','2') AND CLBZ00='0' AND--Z.DMBZ00='0'AND NOT EXISTS (SELECT 1 FROM BQ_YPYZCF C WHERE C.YZID00=Z.YZID00) AND Z.YZID00=M.YZID00 AND M.YZCLLB<>('嘱托') AND Z.YZID00 IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP); IF COUNTER>0 THEN RAISE E_CFQWT;--医嘱拆分的触发器问题 END IF; --是否使用通用名 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; --********************************单病人提取***********************************----------- SELECT BRID00 INTO VBRID00 FROM ZY_BRXXB0 WHERE ZYID00=PZYID00; --皮试未执行或未通过的不能提取,(如果皮试药的用法是脱敏注射，不管皮试结果，都允许提取) 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 Z.YZID00 IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP)AND ROWNUM=1; IF COUNTER>0 THEN RAISE E_PSWTG; END IF; 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);--日期型 VKFJZSJ:=TO_CHAR(VKFJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 VZJJZRQ:=LEAST(VCYRQSJ,VZJJZRQ);--日期型 VZJJZSJ:=TO_CHAR(VZJJZRQ,'YYYYMMDDHH24:MI:SS');--字符型 --预出院病人的出院时间小于提取的截至时间，取预出院时间 END IF; --提取短嘱所有药品医嘱 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,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, 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')))); --提取长嘱所有药品医嘱 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,YZMXID, NVL(TQYPNM,YPNM00), SFDZGG, CLBZ00,YZCLLB,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), 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 ZYID00=PZYID00 AND Z.CLBZ00='0' AND--AND Z.DMBZ00='0' YZZT00 IN ('1','2') AND Z.YZID00 IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP)AND F.QSRQ00<=TO_CHAR(VKFJZRQ,'YYYYMMDD') AND--利用索引 GREATEST(F.QSRQ00||F.QSSJ00,NVL(X.TQRQ00||X.TQSJ00,F.QSRQ00||F.QSSJ00)) 'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --********************开始计算领药量*******************************--------------------- --- 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 ; --包药机药品提取周期设置 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 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; --填写换算周期长度,取整方式,天数,转换率 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 VERRTIP:='没有医嘱可以提取或该医嘱为手术医嘱，提取日期大于病人的预出院日期！*'; else VERRTIP:='没有医嘱可以提取，提取日期大于病人的预出院日期！*'; 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(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) 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 FROM BQ_ZBTQYZ WHERE ID0000=PBCTQID AND SJBZ00='0' AND CLBZ00='0' GROUP BY YZMXID,YPNM00,ID0000,YFBMBH,YZID00,QZFSID,JZRQSJ,ZYID00,YPLBBH; --插入医嘱处理类别,更改医嘱的提取时间 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)/ZQCYTS*JYCS00,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, BM_YGBM00 WHERE ID0000=PBCTQID AND YZCLLB IN ('正常','基数药','领药','出院带药') AND SFKCBZ IN ('2','1') AND YGBH00=PCZY000; --************************************************************************************ --* * --* * --************************************************************************************ <> --生成医嘱执行记录,处理没有提取到药品的医嘱,包括予出院，周期顺延，定期应用，QOD等 --提取长嘱所有药品医嘱 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), 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 ZYID00=PZYID00 AND Z.CLBZ00='0' AND-- Z.DMBZ00='0' YZZT00 IN ('1','2') AND Z.YZID00 IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP) 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'))) 'Y') or ((ls_SSYZSFBYXBRTQ='Y') and ((PTQBZ00='1' and z.YZLY00='2') or (PTQBZ00='0' and z.YZLY00<>'2')))); --- 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'; --包药机药品提取周期设置 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'; 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; --计算长期医嘱的执行次数 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 E_MYYZTQ; 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'), 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); --整条医嘱没有提取到药品，但应该有执行记录的医嘱 --修改医嘱的提取时间,定期应用的医嘱要自动停止 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') 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 Z.YZID00=X.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)); 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; EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; IF SQLCODE=-2290 THEN --yzzxjl.qsrq00||qssj00