-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YPYZTQQR_BR -- Generated 20-3月-2024 10:46:21 from SD_HOSPITAL@NJXZYY CREATE OR REPLACE PROCEDURE sp_bq_ypyztqqr_br ( PTQID00 IN NUMBER , --提取ID PZYID00 IN NUMBER , --住院ID PCZY000 IN NUMBER , --操作员 PBQH000 IN NUMBER , --病区号 PTQRQ00 IN DATE , --提取日期 PCZYXM0 IN CHAR , --操作员中文姓名 PJFLBID IN NUMBER , --计费类别(帐户类别) PCZRQSJ IN DATE , --操作日期时间,同一批提取的药品，操作日期时间应该相等 PHLFFBR IN VARCHAR2 , --忽略非法病人标志 'Y'忽略,'N'不能忽略 PKFYXQF IN CHAR , --口服药品允许欠费 PYPTQSF IN CHAR , --医嘱处方提取是否收费 PFJFTQSF IN CHAR DEFAULT NULL, --附加费提取是否收费 PSFSSMZ in char default 'N', --是否手术麻醉系统进行提取 即有手术麻醉系统里面的药品提取. PSSKSBH in number default 0 --手术科室编号 ) AS -- MODIFICATION HISTORY -- Person Date Comments -- qks 2008.12.10 天湖山能源医院:新增参数BQ_TQYZSFBPDYKYE提取医嘱是否不判断预扣余额 BQ-20081205-001; -- qks 2010.02.03 插入表YF_YZYPSQ时，增加order by S.YPNM00排序：解决 触发器修改药房的预扣库存数量时出现死锁情况 -- qks 2010.02.05 福州肺科医院:新增参数BQ_TQYZSFBPDFY -- csf 2010.06.29 省二人民医院要求，中药按口服单来提取 -- csf 2010.11.18 漳浦县医院要求，神经二类医嘱不转零散处方。 -- csf 2011.06.14 扩张参数BQ_SJELYPSFZLSCF实现毒麻药品不转入零散处方 -- zhangwz 2011.06.17 相关业务表增加YEXM00数据的插入. -- zhangwz 2011.07.28 增加传入参数PSFSSS0控制有手术麻醉系统进入的药品提取要使用零散处方科室领药的模式进行,欲提取后已经BQ_YZYPSQ已经置成非科室领药。 by SSMZ-20110729-001 -- zhangwz 2011.08.25 增加传入参数PSSKSBH用于生成零散处方时的DQKS00数据的存入以及ZY_YFCF00.CFSRBZ='3'(毒麻非毒麻都算3,保持和手术系统处方估价状态一致). by SSMZ-20110822-001. -- CSF 2012.05.16 将医嘱合理用药的接口警示灯改到YF_YZYPSQ表及YF_ZYCFMX 表 YF-20120523-002 -- dsm 2012.07.04 包药机的药品设置TQRQ00提取日期 for BQ-20120629-001 -- dsm 2012.07.04 BQ-20120629-001加参数控制 for BQ-20120711-001 -- laijg 2012.08.31 往BQ_YZYPSQ、YF_YZYPSQ、YF_ZYCF00写入 DYCJSL,DYCJQL,DECJSL,DECJQL字段值 by ZYYS-20120620-001 -- laijg 2012.09.04 扩张参数BQ_SJELYPSFZLSCF=J实现神经二类药品转入零散非毒麻处方 by BQ-20120816-001 -- liuj 2012.10.18 自动带出织袋时先判断隶属跟是否库存不足，以免写入yf_yzypsq不成功导致bq_yzypsq与YF_YZYPSQ数据不一致引起后面扣费费用不一致 for BQ-20121017-001 -- liuj 2012.11.20 由于没有维护好对应的织带参数，提取药品后，点击确认时报错。 改成如没维护织带则不执行自动带出织带代码不抛出异常 for BQ-20121120-001 -- dsm 2013.04.28 药品医嘱首次提取，记录到bq_yzzxjl的时间点有误 for BQ-20130416-002 -- dsm 2013.12.06 医嘱提取时候，如果医嘱是第1次提取，把医嘱的"首日次数"写到申领的药品记录上 for BQ-20131206-002 -- dsm 2014.01.16 修改BQ_YPYZ00.TQRQ00不要条件YPCZFL='3' 只要用KFBZ00='Y' for BQ-20140116-002 -- dsm 2014.01.20 BQ_YZZXJL.qsrq00，第1次提取要用BQ_YPYZ00.SJQSRQ,否则提供给包药机接口的时间有可能不对 for BQ-20140117-002 -- dsm 2014.02.19 增加BQ_XCYAZJHZFY控制 西成药 按针剂会总发药 for BQ-20140208-002 -- dsm 2014.02.20 YF_YZYPSQ.ZFFSBZ 写入 for BQ-20131018-001 -- dsm 2014.04.04 预判余额时考虑YF_BQKFFYDCYXQFJE参数（单次口服欠费金额） for BQ-20140329-002 -- dsm 2014.05.09 相关业务表写入YSZID0 for BQ-20140509-001 -- dsm 2014.05.16 增加BQ_YPYZSFXYZX参数控制BQ_YZZXJL.SFXYZX是否都改成Y for BQ-20140425-002 -- dsm 2014.08.22 增加参数控制BQ_XYCYLSYZAZJHZFY：临时医嘱西药或成药是否按针剂发药 for BQ-20140813-001 -- daihq 2014.09.05 增加修改BM_BQFYZT的KFFYZT(病区口服发药状态)、ZJFYZT(病区针剂发药状态)值 for YF-20140830-001 -- daihq 2014.09.17 修改没有生成处方的异常(E_MYCFSC)的提示信息 for SSMZ-20140828-001 -- dsm 2014.10.27 BQ_XYCYLSYZAZJHZF该参数写的语句(instr(':'||substr(vXYCYLSYZAZJHZFY,3,50)||':',':'||YYID00||':' )>0 修改 YYID00改为TRIM(YYID00) for BQ-20141017-002 -- dsm 2016.12.23 其中有插入yf_zycfmx的时候yysj00字段取值为空值（null）导致处方没有天数　for BQ-20161223-001 -- dsm 2016.12.28 专人专用药品审批数量限制 for BQ-20161215-001 -- dsm 2017.04.17 BQ_TQYZSFBPDYKYE=Y时ZY_BRFY00.SFYKBZ设置成3 for BQ-20170417-002 -- dsm 2017.06.06 增加专科用药的限制 for BQ-20170330-002 -- dsm 2018.01.06 老年按天提取处理 for BQ-20180106-001 -- huangjy 2018.12.03 增加异常处理。修正220035医院byfl00没维护直接跳出 for BQ-20181115-003 -- dsm 2019.05.11 医生操作预停止时写入停止医生等信息，在护士提取/执行操作时被清空，导致后期护士不可核对该条预停止医嘱 for BQ-20190510-002 -- dsm 2019.09.05 提取确认重复问题处理 for BQ-20190829-001 -- dsm 2019.10.24 多人提取时会提示违反约束BQ_YZZXLS for BQ-20191024-003 -- dsm 2019.11.19 a.ZXRQ00||a.ZXSJ00<=b.SJJSRQ||b.SJJSSJ-->a.ZXRQ00||a.ZXSJ00decode(PSFSSMZ,'Y','3',decode(s.SFJSY0,'M','5','4')) -- dsm 2023.03.24 涵江会诊医嘱开单科室按BQ_YPYZ00.KSH000来写入 for BQ-20230317-001 -- dsm 2023.04.10 抵扣修改 for BQ-20230413-002 -- dsn 2023.06.12 修改预扣的时候增加sum 归档 MZYS-20230612-001 --------- ----------- --------------------------------------------------------------------------------------------- COUNTER NUMBER(10,2); --计数器 VCFLSH0 YF_ZYCF00.CFLSH0%TYPE; VKFSJFG CHAR(8); --医嘱时间分割点 VZJSJFG CHAR(8); --医嘱时间分割点 VJZRQ00 DATE; --截至日期 VSYSDATE DATE; VZYH000 ZY_BRXXB0.ZYH000%TYPE; --住院号 VBRXM00 ZY_BRXXB0.XM0000%TYPE; --病人姓名 VXB0000 ZY_BRXXB0.XB0000%TYPE; --性别 VCSRQ00 ZY_BRXXB0.CSRQ00%TYPE; --出生日期 VFBBH00 BM_BRFBB0.FBBH00%TYPE; --病人费别(自费，公费，医保等) VKSH000 BM_BMBM00.BMBH00%TYPE; --病人所属科室号 VCH0000 BM_CWK000.CH0000%TYPE; --当前床号 VBRID00 ZY_BRXXB0.BRID00%TYPE; --病人ID VPARAMS VARCHAR2(255); VSFKFYP CHAR(1);--是否纯粹处理口服药品(允许欠费) VDJH000 NUMBER(10); --收费单据号 VYF0000 NUMBER(12,2); --药费 VXYLYGS NUMBER(5);--需要领药的医嘱数 VXYYKGS NUMBER(5);--需要预扣费用的医嘱数 VWJSJE0 ZY_BRZHXX.WJSJE0%TYPE; --未结算金额 VYBQFJE ZY_BRZHXX.ZYE000%TYPE; --医保允许欠费 VYZCLLB BQ_YPYZMX.YZCLLB%TYPE; --医嘱处理类别 VYPTGBZ CHAR(1); --缺药时,其他药品允许通过标志 VMYYFHZ NUMBER(1); --没有用法的药品医嘱发药是否汇总 -- VYPZSL0 BQ_YZYPSQ.YPZSL0%TYPE; -- VCSL000 BQ_YZYPSQ.CSL000%TYPE;-- VLSJ000 NUMBER(10,2); --零售价 VZDNM00 NUMBER(10); --织袋内码 VZDMC00 CHAR(50); --织袋名称 VZDSL00 NUMBER(2); --织袋数量 VJLDW00 CHAR(6); VSFBPDYK XT_XTCS00.VALUE0%TYPE; --是否不判断预扣余额 VSFBPDFY XT_XTCS00.VALUE0%TYPE; vZYHZBZSFDAKFCL XT_XTCS00.VALUE0%TYPE;--中药汇总标志是否都按口服来处理。 -- E_BRYJGB EXCEPTION ; --病人已经改变，不属于本病区 E_BRJFLBCW EXCEPTION ; --病人计费类别错误 E_YFWCYP EXCEPTION ;--药房无此药品 E_YFYPQY EXCEPTION ;--药房药品缺药 E_YFWCZD EXCEPTION ;--药房无纸袋 E_YFZDBZ EXCEPTION ;--药房纸袋库存不足 E_BRMYZH EXCEPTION ;--病人没有帐户 E_BRZHBZ EXCEPTION ;--病人押金不足 E_MYCFSC EXCEPTION ;--没有生成处方 E_FJFWK EXCEPTION ;--附加费未扣 E_SFPDFY EXCEPTION ; E_SCZDJL EXCEPTION ; --生成纸袋错误 Verrmsg varchar2(100); v_SJELYPSFZLSCF XT_XTCS00.VALUE0%TYPE;--神经二类药品是否转零散处方 Y转 N不转 默认为Y v_LSCFDJH0 ZY_BRFY00.DJH000%TYPE; --住院零散处方收费单据号 v_LSCFJZRQ ZY_BRFY00.CZRQ00%TYPE; --住院零散处方收费日期 v_LSCFJZSJ ZY_BRFY00.CZSJ00%TYPE; --住院零散处方收费时间 v_LSCFLYKS YF_ZYCF00.LYKS00%TYPE; --住院零散处方来源科室 ls_BYJTQFS varchar2(5); --包药机提取方式 ls_SFZDDCZD char(1); vXCYAZJFY varchar2(2); v_DCYXQFJE number(14,4); v_KFZFJE number(14,4); V_QTZFJE number(14,4); VYPSFDZX Varchar2(10); vXYCYLSYZAZJHZFY varchar2(60); LS_Error varchar2(400); E_YBSPerror EXCEPTION ; --目录审批数量限制 V_YPZSL0 BQ_YZYPSQ.YPZSL0%type; V_BZ0000 number; V_ERROR varchar2(120) ; LS_SFQYZKYYQXKZ varchar2(10); BQ_YPYZSFATTQ varchar2(10); LS_FKJE00 ZY_BRFY00.HJJE00%type; LS_HJJE00 ZY_BRFY00.HJJE00%type; BQ_ZDJKYPSFSCCF varchar2(10); BQ_TZYZSFXYHD number(5); BQ_CYDYYZSCZYLSCFFS varchar2(10); ls_YXQFXE ZY_BRFY00.HJJE00%type; LS_KYE000 ZY_BRFY00.HJJE00%type; Ecustom EXCEPTION ; --库存足够的毒麻零散处方 CURSOR C_DMYZID IS SELECT YFBMBH, YFLBID, ZYTS00, DMBZ00, YSGZH0, FJFBZ0, YPNM00, YPMC00, YZCLLB, YPGG00, QLDW00, ZHL000, CSL000, YPZSL0, LSDJ00, GJDJ00, PFDJ00, YPYFMC, PCMC00, YFSMMC, XMYFMC, SFXS00, YZID00, YZMXID, YPLBBH, JLDW00, ZYDJS0, SFKSLY ,YEXM00,YSZID0,ZXYSZ0,ZDJKDJ,decode(YZLX00,'3',KDKS00,VKSH000) KDKS00 FROM BQ_YZYPSQ A WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZCLLB IN ('正常','基数药','领药','出院带药','应急领药')AND DMBZ00='1' AND YPZSL0>0 AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); --库存足够的病人自领的非毒零散处方 CURSOR C_FDLSCF IS SELECT YFBMBH,DECODE(YPLBBH,'2',ZYTS00,1) ZYTS00,YSGZH0,YPLBBH,ZYDJS0,SFKSLY,SUM(LSDJ00*YPZSL0)CFJE00,DECODE(YZCLLB,'基数药','Y','出院带药','M','N')SFJSY0,YEXM00, nvl(max(DYCJSL),0) DYCJSL,nvl(max(DYCJQL),0) DYCJQL,nvl(max(DECJSL),0) DECJSL,nvl(max(DECJQL),0) DECJQL,YSZID0,ZXYSZ0 ,decode(YZLX00,'3',KDKS00,VKSH000) KDKS00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZCLLB IN ('正常','基数药','领药','出院带药','应急领药') AND DMBZ00='0' AND SFKSLY='N' AND YPZSL0>0 AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) GROUP BY YFBMBH,DECODE(YPLBBH,'2',ZYTS00,1),YSGZH0,YPLBBH,ZYDJS0,SFKSLY,DECODE(YZCLLB,'基数药','Y','出院带药','M','N'),YEXM00,YSZID0,ZXYSZ0,decode(YZLX00,'3',KDKS00,VKSH000); --药品提取分以下几种可能 --基数药 毒麻 科室领药/病人自领 医嘱处方/零散处方 予扣 收费 -- 基 非毒 科室 医嘱 Y Y -- 基 毒 科室 零散 Y Y -- 非基 非毒 科室 医嘱 Y N -- 非基 毒 科室 零散 N N -- 非基 非毒 病人 零散 N N -- 非基 毒 病人 零散 N N -- 基数药必须是科室领药(触发器保证)，零散处方不预扣，医嘱处方要预扣库存数量 -- 毒麻的零散处方，一个药品一张处方，普通零散处方应该有合并功能 BEGIN --2004.03.04 Modified By Lindj 插入费用明细时条件多了：--YPLBBH='0'AND --先插入YF_YZYPSQ表,算出药品金额,然后回写到BQ_YZYPSQ --附加费提取是否收费20051109-- --神经二类药品是否转零散处方 --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; select nvl(max(trim(VALUE0)),'Y') INTO v_SJELYPSFZLSCF from XT_XTCS00 where NAME00='BQ_SJELYPSFZLSCF'; VSYSDATE:=SYSDATE; VPARAMS:='SP_BQ_YPYZTQQR_BR('||TO_CHAR(PTQID00)||','||TO_CHAR(PZYID00)||','||TO_CHAR(PCZY000)||','|| TO_CHAR(PBQH000)||','||TO_CHAR(PTQRQ00,'YYYYMMDD HH24:MI:SS')||',' ||PCZYXM0||','||TO_CHAR(PJFLBID)||','||TO_CHAR(PCZRQSJ,'YYYYMMDD HH24:MI:SS')||','||PHLFFBR||')'; VYPTGBZ:='Y';--缺药时,其他药品允许通过标志 VSFKFYP:='N';--是否是允许欠费的口服药品 --中药是否都提成口服领药单 select nvl(max(trim(VALUE0)),'N') into vZYHZBZSFDAKFCL from XT_XTCS00 where NAME00='BQ_ZYSFDTCKFLYD'; VJZRQ00:=TRUNC(PTQRQ00)+1; Verrmsg:='SELECT * FROM XT_XTCS00 WHERE NAME00=BQ_KFYZSJFG'; SELECT SUBSTRB(VALUE0,1,8) INTO VKFSJFG FROM XT_XTCS00 WHERE NAME00='BQ_KFYZSJFG'; Verrmsg:='SELECT * FROM XT_XTCS00 WHERE NAME00=BQ_ZJYZSJFG'; SELECT SUBSTRB(VALUE0,1,8) INTO VZJSJFG FROM XT_XTCS00 WHERE NAME00='BQ_ZJYZSJFG'; IF PYPTQSF='Y' THEN--医嘱药品提取时全部收费 VYZCLLB:='%'; ELSE--否则只收基数药的钱 VYZCLLB:='基数药'; END IF; BEGIN SELECT DECODE(UPPER(TRIM(VALUE0)),'Y',0,1) INTO VMYYFHZ FROM XT_XTCS00 WHERE NAME00='BQ_MYYFYPYZFYSFHZ'; EXCEPTION WHEN OTHERS THEN VMYYFHZ:=0; END; BEGIN SELECT VALUE0 INTO VSFBPDYK FROM XT_XTCS00 WHERE NAME00='BQ_TQYZSFBPDYKYE'; EXCEPTION WHEN OTHERS THEN VSFBPDYK:='N'; END; BEGIN SELECT VALUE0 INTO VSFBPDFY FROM XT_XTCS00 WHERE NAME00='BQ_TQYZSFBPDFY'; EXCEPTION WHEN OTHERS THEN VSFBPDFY:='N'; END; BEGIN SELECT '1' INTO BQ_ZDJKYPSFSCCF FROM XT_XTCS00 WHERE NAME00='BQ_ZDJKYPSFSCCF' and VALUE0='1'; EXCEPTION WHEN OTHERS THEN BQ_ZDJKYPSFSCCF:='0'; END; IF VSFBPDFY='Y' then select count(1) into COUNTER from BM_BQFYZT where (FYZT00='2' or KFFYZT='2' or ZJFYZT='2') AND BQBMBH=PBQH000; IF COUNTER>0 THEN ROLLBACK; RAISE E_SFPDFY; END IF; end if; --重点监控药品是否生成零散处方 if BQ_ZDJKYPSFSCCF='1' then update BQ_YZYPSQ a set ZDJKDJ=nvl((select ZDJKDJ from BM_YD0000 where YPNM00=a.YPNM00),0) where a.ID0000=PTQID00 and A.ZYID00=PZYID00; update BQ_YZYPSQ a set DMBZ00='1' where a.ID0000=PTQID00 and A.ZYID00=PZYID00 and ZDJKDJ>0; end if; --专人专用药品审批 begin LS_Error:=' '; for c_zrzyyp in(select a.YPNM00,a.YPMC00,nvl(SUM(ROUND(a.YPZSL0*a.ZHL000/C.K2J000,3)),0) YPZSL0 FROM BQ_YZYPSQ A,BM_YD0000 C WHERE A.ID0000=PTQID00 AND A.ZYID00=PZYID00 and A.YPNM00=C.YPNM00 and a.YPNM00 in (select DJ0000 from BM_TYZD00 where ZDMC00='专人专用审批的药品目录') group by A.YPNM00,A.YPMC00 ) loop begin SP_BQ_YPSPSLXZ(PZYID00,c_zrzyyp.YPNM00,c_zrzyyp.YPZSL0,V_BZ0000); if V_BZ0000=1 then if LS_Error=' ' then LS_Error:=c_zrzyyp.YPMC00||'药品的使用量已经超过了审批数量！*'; else LS_Error:=LS_Error||chr(13)||'药品的使用量已经超过了审批数量！*'; end if; -- raise E_YBSPerror; end if; exception when others then V_BZ0000:=0; -- LS_Error:=substr(SQLERRM,1,120); end; end loop; if LS_Error<>' ' then raise E_YBSPerror; end if; --专人专用药品审批 end --专科用药限制begin-----------------------BM_ZKYYKZ begin select Trim(Value0) into LS_SFQYZKYYQXKZ from XT_XTCS00 where NAME00='YS_SFQYZKYYQXKZ' ; exception when others then ROLLBACK; LS_SFQYZKYYQXKZ:='N'; end; if LS_SFQYZKYYQXKZ='Y' then LS_Error:=' '; for c_erzyyp in ( select distinct a.YPMC00 from BQ_YZYPSQ a,BM_YD0000 d,BM_ZKYYKZ e where A.ID0000=PTQID00 AND A.ZYID00=PZYID00 and a.YPNM00=d.YPNM00 and d.YPNM00=e.YPNM00 and e.SFQY00='1' and e.MZZYBZ in('1','2') and not exists (select 1 from BM_ZKYYQX f where YPNM00=e.YPNM00 and to_char(sysdate,'YYYYMMDDHH24:MI:SS')>=f.KSRQ00||f.KSSJ00 and to_char(sysdate,'YYYYMMDDHH24:MI:SS')<=f.JSRQ00||f.JSSJ00 and YSGZH0=a.YSGZH0 and f.MZZYBZ in('1','2') ) ) loop if LS_Error=' ' then LS_Error:=c_erzyyp.YPMC00; else LS_Error:=LS_Error||'、'||c_erzyyp.YPMC00; end if; if LS_Error<>' ' then LS_Error:='药品('||LS_Error||')是专科用药控制品种,属于特定医生使用!'; end if; end loop; end if; if LS_Error<>' ' then ROLLBACK; raise E_YBSPerror; end if; --专科用药限制end----------------------- --可能病人已经不属于本病区,要进行判定 SELECT COUNT(*) INTO COUNTER FROM ZY_BRXXB0 WHERE ZYID00=PZYID00 AND DQBQ00+0=PBQH000 AND SUBSTR(BRZT00,1,1)='2'; IF COUNTER<>1 THEN DELETE BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00; COMMIT; RAISE E_BRYJGB;--病人已经改变，不属于本病区 END IF; --出院带药医嘱生成病人自领的住院零散处方 select nvl(max(trim(value0)),'0') into BQ_CYDYYZSCZYLSCFFS from XT_XTCS00 where NAME00='BQ_CYDYYZSCZYLSCFFS'; if BQ_CYDYYZSCZYLSCFFS='1' then --BQ-20220901-001 update BQ_YZYPSQ SET SFKSLY='N' WHERE YZCLLB='出院带药' and ID0000=PTQID00 and ZYID00=PZYID00; end if; --取病人信息,费别 Verrmsg:='SELECT * FROM ZY_BRXXB0 WHERE ZYID00='||PZYID00; SELECT RYCWH0,XB0000,CSRQ00,BRID00,DQKS00,XM0000,ZYH000 INTO VCH0000,VXB0000,VCSRQ00,VBRID00,VKSH000,VBRXM00,VZYH000 FROM ZY_BRXXB0 WHERE ZYID00=PZYID00; Verrmsg:='SELECT * FROM BM_BRXXB0 WHERE BRID00='||VBRID00; SELECT FBBH00 INTO VFBBH00 FROM BM_BRXXB0 WHERE BRID00=VBRID00; select SF_BQ_KYJE00(PZYID00,VBRID00) into LS_KYE000 from dual; begin select to_number(MC0000) into ls_YXQFXE from BM_TYZD00 where ZDMC00='医嘱提取限制欠费金额' and BH0000=VFBBH00 ; exception when others then ls_YXQFXE:=0; end; if ls_YXQFXE>0 and LS_KYE000+ls_YXQFXE<0 then Verrmsg:='['||VBRXM00||']病人住院欠费总金额已经超过 '||ls_YXQFXE||'元,'||chr(13)||'纠纷或需开通绿色通道需向医务部申请'; raise Ecustom; end if; if VFBBH00=3 then begin select to_number(SF_ZY_BRFYSX(PZYID00,PBQH000)) into LS_FKJE00 from dual; exception when others then LS_FKJE00:=0; end; if LS_FKJE00>0 then select sum(HJJE00) into LS_HJJE00 from ZY_BRFY00 where ZYID00=PZYID00; if LS_HJJE00>LS_FKJE00 then Verrmsg:='['||VBRXM00||']病人住院总金额已经超过 '||LS_FKJE00||'元，请走审批流程'; raise Ecustom; end if; end if; end if; --药品库存数量判定(所有需要领药的处方都参与判定)-- --是否该药房没有此药 UPDATE BQ_YZYPSQ Z SET SFKCBZ='2' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') AND NOT EXISTS ( SELECT 1 FROM YF_YPKCXX K WHERE Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND PDXH00>'0'); IF (NOT SQL%NOTFOUND)AND (VYPTGBZ<>'Y') THEN --无此药品,处理下一个病人 ROLLBACK; RAISE E_YFWCYP;--药房无此药品 END IF; --是否库存不足 UPDATE BQ_YZYPSQ Z SET SFKCBZ='1' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') AND EXISTS (SELECT 1 FROM YF_YPKCXX K,(SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 FROM BQ_YZYPSQ WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') --AND DMBZ00='0' GROUP BY YFBMBH,YPNM00)S WHERE K.YFBMBH=S.YFBMBH AND K.YPNM00=S.YPNM00 AND S.YPZSL0>K.YKKCSL AND Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND K.PDXH00>'0'); IF (NOT SQL%NOTFOUND) AND (VYPTGBZ<>'Y') THEN--缺药其他药品不允许通过 ROLLBACK; RAISE E_YFYPQY;--药房库存数量不足 END IF; --判定是否有需要领药的药品(不包括库存不足处方) SELECT COUNT(*)INTO VXYLYGS FROM BQ_YZYPSQ S WHERE S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND ROWNUM=1 AND S.YPZSL0>0 AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); --药品医嘱是否都需要执行 begin select trim(VALUE0) into VYPSFDZX from XT_XTCS00 where name00='BQ_YPYZSFXYZX'; exception when others then VYPSFDZX:='N'; end; --医保目录审批begin select trim(SF_BQ_CheckYBSPSL(PTQID00,PZYID00)) into LS_Error from dual; if LS_Error<>' ' then ROLLBACK; raise E_YBSPerror; end if; --医保目录审批end; IF VXYLYGS >0 THEN -- if v_SJELYPSFZLSCF='N' then update BQ_YZYPSQ S set DMBZ00='0' where S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND S.YPZSL0>0 and SFKSLY='Y' and S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) and DMBZ00='1' and exists (select 1 from BM_YD0000 where YPNM00=S.YPNM00 and SJDJ00='2'); elsif v_SJELYPSFZLSCF='M' then update BQ_YZYPSQ S set DMBZ00='0' where S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND S.YPZSL0>0 AND SFKSLY='Y' and S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) and DMBZ00='1' ; elsif v_SJELYPSFZLSCF='J' then update BQ_YZYPSQ S set DMBZ00='0',SFKSLY='N' where S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND S.YPZSL0>0 AND SFKSLY='Y' and S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) and DMBZ00='1' and exists (select 1 from BM_YD0000 where YPNM00=S.YPNM00 and SJDJ00='2'); end if; SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO VDJH000 FROM DUAL WHERE ROWNUM=1; --1------------------------------------------------------------------------------------------- --插入申请表：得到正确的零售单价 Lindj 2004.04.30 --插入到药房的申请表(库存足够且非毒且科室领药) --多批次 Lindj 2004.04.26 DECODE(TRIM(PYPTQSF),'Y','1' ,DECODE(YZCLLB,'基数药','1','0')) --SELECT SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, ID0000, YFBMBH, BQH000, ZYID00,NVL(CH0000,0) -- ,ZYH000, BRXM00,S.YFLBID, NVL(L.HZBZ00,DECODE(S.YPLBBH,'2',1,VMYYFHZ)), YZID00, YZMXID -- ,DECODE(S.YPLBBH,'2',ZYTS00,1), YSGZH0,DECODE(TRIM(PYPTQSF),'Y','8' ,DECODE(YZCLLB,'基数药','8','0')) -- ,NULL, YPNM00,YPMC00,YPGG00,DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)) -- ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'YYYYMMDD'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL)) -- ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'HH24:MI:SS'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL)) -- ,QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC -- ,PCMC00, YFSMMC, '' , XMYFMC, 'Z' , SFXS00 -- ,PCZY000,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS') -- ,GJDJ00 ,PFDJ00,S.YPLBBH, VKSH000,Y.BMBH00,PCSJ00,ZYDJS0 --FROM BQ_YZYPSQ S,BQ_YFLB00 L,BM_YGBM00 Y --WHERE S.YFLBID=L.YFLBID(+) AND S.ID0000=PTQID00 AND S.YSGZH0=Y.YGBH00 AND -- S.SFKSLY='Y' AND S.DMBZ00='0' AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND -- S.ZYID00=PZYID00 AND S.YPZSL0>0 AND -- S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ -- WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00); if BQ_YPYZSFATTQ='Y' then INSERT INTO YF_YZYPSQ( YPQLPC, TQDJH0, YFBMBH, BQBMBH, ZYID00, CH0000, ZYH000, BRXM00, YFLBID, HZBZ00, YZID00, YZMXID, ZYTS00, YSGZH0, QLZT00, FJFDJH, YPNM00, YPMC00, YPGG00, DJH000, JZRQ00, JZSJ00, QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC, PCMC00, YFSMMC, ZJYFMC, XMYFMC, CXBZ00, SFXS00, CZY000, SRRQ00, SRSJ00, GJDJ00, PFDJ00, YPDLBH, DQKS00, SSKS00, PCSJ00, ZYDJS0, YEXM00,DYCJSL,DYCJQL, DECJSL,DECJQL,SRCS00,ZFFSBZ,YSZID0,TQQSSJ,TQJZSJ,CS0000,YYRQ00,ZXYSZ0,DKSL00,SFZSL0) --零售单价对发药单位而言 select SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, A.* from (SELECT ID0000, YFBMBH, BQH000, ZYID00,NVL(CH0000,0) ,ZYH000, BRXM00,S.YFLBID, NVL(L.HZBZ00,DECODE(S.YPLBBH,'2',1,VMYYFHZ)), YZID00, YZMXID ,DECODE(S.YPLBBH,'2',ZYTS00,1), YSGZH0,DECODE(TRIM(PYPTQSF),'Y','8' ,DECODE(YZCLLB,'基数药','8','0')) ,NULL, YPNM00,YPMC00,YPGG00,DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'YYYYMMDD'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'HH24:MI:SS'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL)) ,QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC ,PCMC00, YFSMMC, '' , XMYFMC, 'Z' , SFXS00 ,PCZY000,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS') ,GJDJ00 ,PFDJ00,S.YPLBBH, decode(S.YZLX00,'3',S.KDKS00,VKSH000),Y.BMBH00,PCSJ00,ZYDJS0,S.YEXM00,S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL ,(select SRCS00 from BQ_YPYZ00 where YZID00=S.YZID00 and TQRQ00 is null) ,(select ZFFSBZ from BQ_YPYZMX where YZID00=S.YZID00 and YZMXID=S.YZMXID) ,S.YSZID0 ,S.TQQSSJ ,S.TQJZSJ ,S.ZYTS00 ,null YYRQ00 ,S.ZXYSZ0 ,S.DKSL00 ,S.YPZSL0-S.DKSL00 FROM BQ_YZYPSQ S,BQ_YFLB00 L,BM_YGBM00 Y WHERE S.YFLBID=L.YFLBID(+) AND S.ID0000=PTQID00 AND S.YSGZH0=Y.YGBH00 AND S.SFKSLY='Y' AND S.DMBZ00='0' AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.ZYID00=PZYID00 AND S.YPZSL0>0 AND S.YPLBBH='2' and S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00) order by S.YPNM00) A; INSERT INTO YF_YZYPSQ( YPQLPC, TQDJH0, YFBMBH, BQBMBH, ZYID00, CH0000, ZYH000, BRXM00, YFLBID, HZBZ00, YZID00, YZMXID, ZYTS00, YSGZH0, QLZT00, FJFDJH, YPNM00, YPMC00, YPGG00, DJH000, JZRQ00, JZSJ00, QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC, PCMC00, YFSMMC, ZJYFMC, XMYFMC, CXBZ00, SFXS00, CZY000, SRRQ00, SRSJ00, GJDJ00, PFDJ00, YPDLBH, DQKS00, SSKS00, PCSJ00, ZYDJS0, YEXM00,DYCJSL,DYCJQL, DECJSL,DECJQL,SRCS00,ZFFSBZ,YSZID0,TQQSSJ,TQJZSJ,CS0000,YYRQ00,ZXYSZ0,DKSL00,SFZSL0) --零售单价对发药单位而言 select SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, A.* from ( SELECT ID0000, YFBMBH, BQH000, ZYID00,NVL(CH0000,0) ,ZYH000, BRXM00,S.YFLBID, NVL(L.HZBZ00,DECODE(S.YPLBBH,'2',1,VMYYFHZ)), S.YZID00, YZMXID ,DECODE(S.YPLBBH,'2',ZYTS00,1), YSGZH0,DECODE(TRIM(PYPTQSF),'Y','8' ,DECODE(YZCLLB,'基数药','8','0')) ,NULL, YPNM00,YPMC00,YPGG00,DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'YYYYMMDD'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'HH24:MI:SS'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL)) ,QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, (YPZSL0/S.ZYTS00*SF_BQ_CALYPZSL(S.YZID00,s.TQQSSJ,s.TQJZSJ,K.ZXRQ00)) YPZSL0, YPYFMC ,PCMC00, YFSMMC, '' , XMYFMC, 'Z' , SFXS00 ,PCZY000,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS') ,GJDJ00 ,PFDJ00,S.YPLBBH, decode(S.YZLX00,'3',S.KDKS00,VKSH000),Y.BMBH00,PCSJ00,ZYDJS0,S.YEXM00,S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL ,(select SRCS00 from BQ_YPYZ00 where YZID00=S.YZID00 and TQRQ00 is null) ,(select ZFFSBZ from BQ_YPYZMX where YZID00=S.YZID00 and YZMXID=S.YZMXID) ,S.YSZID0 ,S.TQQSSJ ,S.TQJZSJ ,S.ZYTS00 ,K.ZXRQ00 ,S.ZXYSZ0 ,S.DKSL00 ,S.YPZSL0-S.DKSL00 FROM BQ_YZYPSQ S,BQ_YFLB00 L,BM_YGBM00 Y,VW_BQ_YZCFMX K WHERE S.YFLBID=L.YFLBID(+) AND S.ID0000=PTQID00 AND S.YSGZH0=Y.YGBH00 AND S.SFKSLY='Y' AND S.DMBZ00='0' AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.ZYID00=PZYID00 AND S.YPZSL0>0 AND S.YZID00=K.YZID00 and K.ZXRQ00 between substr(S.TQQSSJ,1,8) and substr(S.TQJZSJ,1,8) and S.YPLBBH<>'2' and S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00) order by S.YPNM00 ) A; else INSERT INTO YF_YZYPSQ( YPQLPC, TQDJH0, YFBMBH, BQBMBH, ZYID00, CH0000, ZYH000, BRXM00, YFLBID, HZBZ00, YZID00, YZMXID, ZYTS00, YSGZH0, QLZT00, FJFDJH, YPNM00, YPMC00, YPGG00, DJH000, JZRQ00, JZSJ00, QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC, PCMC00, YFSMMC, ZJYFMC, XMYFMC, CXBZ00, SFXS00, CZY000, SRRQ00, SRSJ00, GJDJ00, PFDJ00, YPDLBH, DQKS00, SSKS00, PCSJ00, ZYDJS0, YEXM00,DYCJSL,DYCJQL, DECJSL,DECJQL,SRCS00,ZFFSBZ,YSZID0,TQQSSJ,TQJZSJ,CS0000,YYRQ00,ZXYSZ0,DKSL00,SFZSL0) --零售单价对发药单位而言 select SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, A.* from (SELECT ID0000, YFBMBH, BQH000, ZYID00,NVL(CH0000,0) ,ZYH000, BRXM00,S.YFLBID, NVL(L.HZBZ00,DECODE(S.YPLBBH,'2',1,VMYYFHZ)), YZID00, YZMXID ,DECODE(S.YPLBBH,'2',ZYTS00,1), YSGZH0,DECODE(TRIM(PYPTQSF),'Y','8' ,DECODE(YZCLLB,'基数药','8','0')) ,NULL, YPNM00,YPMC00,YPGG00,DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'YYYYMMDD'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL)) ,DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'HH24:MI:SS'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL)) ,QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC ,PCMC00, YFSMMC, '' , XMYFMC, 'Z' , SFXS00 ,PCZY000,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS') ,GJDJ00 ,PFDJ00,S.YPLBBH, decode(S.YZLX00,'3',S.KDKS00,VKSH000),Y.BMBH00,PCSJ00,ZYDJS0,S.YEXM00,S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL ,(select SRCS00 from BQ_YPYZ00 where YZID00=S.YZID00 and TQRQ00 is null) ,(select ZFFSBZ from BQ_YPYZMX where YZID00=S.YZID00 and YZMXID=S.YZMXID) ,S.YSZID0 ,S.TQQSSJ ,S.TQJZSJ ,S.ZYTS00 ,substr(s.TQJZSJ,1,8) ,S.ZXYSZ0 ,S.DKSL00 ,S.YPZSL0-S.DKSL00 FROM BQ_YZYPSQ S,BQ_YFLB00 L,BM_YGBM00 Y WHERE S.YFLBID=L.YFLBID(+) AND S.ID0000=PTQID00 AND S.YSGZH0=Y.YGBH00 AND S.SFKSLY='Y' AND S.DMBZ00='0' AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND S.ZYID00=PZYID00 AND S.YPZSL0>0 AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00) order by S.YPNM00) A; end if; --多批次 Lindj 2004.04.26 --多批次 Lindj 2004.04.26 多批次库存提取时收费，需要把QLZT00:'8'-->'1';基数药时也需要把QLZT00:'8'-->'1'(状态'8'为临时使用) UPDATE YF_YZYPSQ SET QLZT00='1' WHERE TQDJH0=PTQID00 AND QLZT00='8'; --多批次 Lindj 2004.04.26 更新BQ_YZYPSQ的单价、以便ZY_FYMX00中的价格正确 UPDATE BQ_YZYPSQ A SET (LSDJ00,GJDJ00,PFDJ00,GFJE00,ZFJE00,QZFJE0)=(SELECT LSDJ00,GJDJ00,PFDJ00,GFJE00,sum(ZFJE00),sum(QZFJE0) FROM YF_YZYPSQ B WHERE A.ID0000=B.TQDJH0 AND A.YFBMBH=B.YFBMBH AND A.YPNM00=B.YPNM00 AND A.YZID00=B.YZID00 group by LSDJ00,GJDJ00,PFDJ00,GFJE00) WHERE EXISTS (SELECT 1 FROM YF_YZYPSQ C WHERE A.ID0000=C.TQDJH0 AND A.YFBMBH=C.YFBMBH AND A.YPNM00=C.YPNM00 AND A.YZID00=C.YZID00 AND A.ID0000=PTQID00) AND A.ID0000=PTQID00; --抵扣begin,此处做预扣，药房做发药确认时扣SJKCSL update YF_YTYKC0 a set YKKCSL=YKKCSL-nvl((select sum(nvl(DKSL00,0)*ZHL000) from YF_YZYPSQ where TQDJH0=PTQID00 and ZYID00=PZYID00 and BQBMBH=a.BQBMBH and YFBMBH=a.YFBMBH and YPNM00=a.YPNM00),0) where BQBMBH=PBQH000 and exists(select 1 from YF_YZYPSQ where TQDJH0=PTQID00 and ZYID00=PZYID00 and BQBMBH=a.BQBMBH and YFBMBH=a.YFBMBH and YPNM00+0=a.YPNM00); --抵扣 end --中草药自动带出纸袋 --查找当前提取处方的中草药的贴数 begin ls_SFZDDCZD:='Y'; begin SELECT NVL(LSJ000,0) ,NVL(value0,'9999') , NVL(ypmc00,'织袋'),NVL(jldw00,'个') INTO VLSJ000, VZDNM00, VZDMC00, VJLDW00 From xt_xtcs00 a, bm_yd0000 b Where a.name00='bm_zdnm00' and a.value0=b.YPNM00; EXCEPTION WHEN OTHERS THEN ls_SFZDDCZD:='N'; end; SELECT COUNT(*) INTO VZDSL00 FROM YF_YZYPSQ S,XT_ZDSZ00 B where s.ypdlbh='2' AND S.YPNM00=B.YPNM00 AND S.ZYID00=PZYID00 AND S.TQDJH0=PTQID00; IF (VZDSL00>0) and (ls_SFZDDCZD='Y') THEN 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) SELECT TQDJH0, YFBMBH, BQBMBH, ZYID00, ZYH000, BRXM00, YFLBID, ZYTS00, '0', YSGZH0, 0, VZDNM00, VZDMC00, 'N', '0', YPGG00, '个', 1, 1, YPZSL0*ZHL000/CSL000, VLSJ000, VLSJ000, VLSJ000, YPYFMC, PCMC00, YFSMMC, XMYFMC, SFXS00, 'N', CZY000, YZID00, 0, CH0000, YPDLBH , '个', YPZSL0*ZHL000/CSL000,ZYDJS0,BZ0000,S.YEXM00,S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL,S.YSZID0 FROM YF_YZYPSQ S, XT_ZDSZ00 B where s.ypdlbh='2' AND S.YPNM00=B.YPNM00 AND S.ZYID00=PZYID00 AND S.TQDJH0=PTQID00; --判断 药房存在纸袋 UPDATE BQ_YZYPSQ Z SET SFKCBZ='2' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND Z.YPNM00=VZDNM00 AND NOT EXISTS ( SELECT 1 FROM YF_YPKCXX K WHERE Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND PDXH00>'0'); IF NOT SQL%NOTFOUND THEN ROLLBACK; RAISE E_YFWCZD;--药房无此药品 END IF; --是否库存不足 UPDATE BQ_YZYPSQ Z SET SFKCBZ='1' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND Z.YPNM00=VZDNM00 AND EXISTS (SELECT 1 FROM YF_YPKCXX K,(SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 FROM BQ_YZYPSQ WHERE ZYID00=PZYID00 AND ID0000=PTQID00 GROUP BY YFBMBH,YPNM00)S WHERE K.YFBMBH=S.YFBMBH AND K.YPNM00=S.YPNM00 AND S.YPZSL0>K.YKKCSL AND Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND K.PDXH00>'0'); IF NOT SQL%NOTFOUND THEN ROLLBACK; RAISE E_YFZDBZ;--库存数量不足 END IF; Insert Into YF_YZYPSQ( YPQLPC, TQDJH0, YFBMBH, BQBMBH, ZYID00, CH0000, ZYH000, BRXM00, YFLBID, HZBZ00, YZID00, YZMXID, ZYTS00, YSGZH0, QLZT00, FJFDJH, YPNM00, YPMC00, YPGG00, DJH000, JZRQ00, JZSJ00, QLDW00, ZHL000, LSDJ00, CSL000, JLDW00, YPZSL0, YPYFMC, PCMC00, YFSMMC, ZJYFMC, XMYFMC, CXBZ00, SFXS00, CZY000, SRRQ00, SRSJ00, GJDJ00, PFDJ00, YPDLBH, DQKS00, SSKS00, PCSJ00, ZYDJS0,YEXM00,DYCJSL,DYCJQL,DECJSL,DECJQL,SRCS00,YSZID0) --VLSJ000,VZDNM00 ,VZDMC00,VJLDW00 Select SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, TQDJH0, YFBMBH, BQBMBH, ZYID00, CH0000, ZYH000, BRXM00, YFLBID, HZBZ00, YZID00, 0, ZYTS00, YSGZH0, QLZT00, FJFDJH, VZDNM00, VZDMC00, YPGG00, DJH000, JZRQ00, JZSJ00, '个', 1, VLSJ000, 1, '个', YPZSL0*ZHL000/CSL000, YPYFMC, PCMC00, YFSMMC, ZJYFMC, XMYFMC, CXBZ00, SFXS00, CZY000, SRRQ00, SRSJ00, VLSJ000, VLSJ000, YPDLBH, DQKS00,SSKS00,PCSJ00,ZYDJS0,S.YEXM00,S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL ,(select SRCS00 from BQ_YPYZ00 where YZID00=S.YZID00 and TQRQ00 is null) ,S.YSZID0 FROM YF_YZYPSQ S, XT_ZDSZ00 B where s.ypdlbh='2' AND S.YPNM00=B.YPNM00 AND S.ZYID00=PZYID00 AND S.TQDJH0=PTQID00; --修改费用 END IF; exception when others then -- VZDSL00:=0; rollback ; raise E_SCZDJL ; end; --end中草药自动带出纸袋 --1------------------------------------------------------------------------------------------- --判定是否有需要预扣费用的医嘱(所有库存足，所有基数药处方或医嘱处方) --医嘱处方全部予扣,所有基数药处方预扣 SELECT COUNT(*)INTO VXYYKGS FROM BQ_YZYPSQ S WHERE S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND ROWNUM=1 AND S.YPZSL0>0 AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND ((YZCLLB='基数药')OR(SFKSLY='Y'AND DMBZ00='0'))AND -- ((YZCLLB='基数药')OR(SFKSLY='Y'AND DMBZ00='0')OR(PYPTQSF='Y'AND SFKSLY='Y'AND DMBZ00='1'))AND -- ((YZCLLB LIKE VYZCLLB AND DMBZ00='1')OR(DMBZ00='0')) AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); IF VXYYKGS>0 THEN--有需要预扣费用的医嘱 SELECT SUM(ZFJE00) INTO COUNTER FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZCLLB IN ('正常','基数药','领药','出院带药')AND ((YZCLLB='基数药')OR(SFKSLY='Y'AND DMBZ00='0')) AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); UPDATE ZY_BRZHXX SET YKZYE0=YKZYE0-COUNTER WHERE BRID00=Vbrid00 AND JFLBID=PJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN --病人没有帐户,下一个 ROLLBACK; RAISE E_BRMYZH;--病人没有帐户 END IF; --判定预扣费用是否足够 V_KFZFJE:=0; begin select to_number(nvl(trim(value0),'0')) into v_DCYXQFJE from xt_xtcs00 where name00='YF_BQKFFYDCYXQFJE'; exception when others then v_DCYXQFJE:=0; end; if (v_DCYXQFJE>0) then --单次允许欠费金额BQ-20140329-002 SELECT SUM(ROUND(ZFJE00,2))ZFJE00 into V_KFZFJE --口服自付金额 FROM BQ_YZYPSQ A WHERE ID0000=PTQID00 AND ZYID00=PZYID00 and YZCLLB NOT IN ('嘱托','自备药','门诊处理') and YFLBID=0; SELECT SUM(ROUND(ZFJE00,2)) ZFJE00 into V_QTZFJE --非口服自付金额 FROM BQ_YZYPSQ A WHERE ID0000=PTQID00 AND ZYID00=PZYID00 and YZCLLB NOT IN ('嘱托','自备药','门诊处理') and YFLBID>0; V_KFZFJE:=nvl(V_KFZFJE,0); V_QTZFJE:=nvl(V_QTZFJE,0); end if; IF COUNTER>0 THEN COUNTER:=SF_BQ_YKYEPD(NULL,VBRID00); ELSE COUNTER:=1; END IF; if V_QTZFJE=0 and V_KFZFJE>0 and v_DCYXQFJE>0 and V_KFZFJE<=v_DCYXQFJE then COUNTER:=1; end if; --不判断预扣药品费用 2008.12.10 IF VSFBPDYK='Y' then COUNTER:=1; end if; IF COUNTER<>1 THEN --病人押金不足 ROLLBACK; IF (PKFYXQF='Y') and (1=2) THEN--设置了口服药品允许欠费,暂时不能使用此功能2005.11.17 VSFKFYP:='Y';--纯粹口服药品 --删除非口服药品 DELETE FROM BQ_ZBTQYZ Z WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND NOT EXISTS (SELECT 1 FROM BQ_YZYPSQ S WHERE S.YFLBID=0 AND S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND Z.YZID00=S.YZID00); DELETE FROM BQ_YZYPSQ WHERE YFLBID<>0 AND ID0000=PTQID00 AND ZYID00=PZYID00; SELECT COUNT(*) INTO COUNTER FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND ROWNUM=1; IF COUNTER=0 THEN--没有口服药 ROLLBACK; RAISE E_BRZHBZ; END IF; --药品库存数量重新判定(所有需领药品都参与判定)-- --是否该药房没有此药 UPDATE BQ_YZYPSQ Z SET SFKCBZ='2' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') AND NOT EXISTS ( SELECT 1 FROM YF_YPKCXX K WHERE Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND PDXH00>'0'); IF (NOT SQL%NOTFOUND)AND (VYPTGBZ<>'Y') THEN --无此药品,处理下一个病人 ROLLBACK; RAISE E_YFWCYP;--药房无此药品 END IF; --是否库存不足 UPDATE BQ_YZYPSQ Z SET SFKCBZ='1' WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') AND EXISTS (SELECT 1 FROM YF_YPKCXX K,(SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 FROM BQ_YZYPSQ WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') --AND DMBZ00='0' GROUP BY YFBMBH,YPNM00)S WHERE K.YFBMBH=S.YFBMBH AND K.YPNM00=S.YPNM00 AND S.YPZSL0>K.YKKCSL AND Z.YFBMBH=K.YFBMBH AND Z.YPNM00=K.YPNM00 AND K.PDXH00>'0'); IF (NOT SQL%NOTFOUND) AND (VYPTGBZ<>'Y') THEN--缺药其他药品不允许通过 ROLLBACK; RAISE E_YFYPQY;--药房库存数量不足 END IF; --重新予扣,是否有需预扣的药品(所有库存足够,基数药处方或医嘱处方或参数指定要收费的科室领药零散处方) SELECT COUNT(*)INTO VXYYKGS FROM BQ_YZYPSQ S WHERE S.ID0000=PTQID00 AND S.ZYID00=PZYID00 AND ROWNUM=1 AND S.YPZSL0>0 AND S.YZCLLB IN ('正常','基数药','领药','出院带药') AND ((YZCLLB='基数药')OR(SFKSLY='Y'AND DMBZ00='0')OR(PYPTQSF='Y'AND SFKSLY='Y'AND DMBZ00='1'))AND -- ((YZCLLB LIKE VYZCLLB AND DMBZ00='1')OR(DMBZ00='0')) AND S.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); IF VXYYKGS>0 THEN --重新预扣药费(允许欠费,不需要重新判押金) UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0-SUM(ZFJE00) FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZCLLB IN ('正常','基数药','领药','出院带药')AND ((YZCLLB='基数药')OR(SFKSLY='Y'AND DMBZ00='0'))AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00) ) WHERE BRID00=Vbrid00 AND JFLBID=PJFLBID AND SFDJ00='0' ; END IF; ELSE--病人押金不足又没有设置口服允许欠费 ROLLBACK; RAISE E_BRZHBZ;--病人押金不足 END IF; END IF; END IF; --修改库存数量足够的药品的预扣数量(库存足够，领药的医嘱处方)，/无法优化 20031027由触发器完成予扣 --BEGIN -- UPDATE YF_YPKCXX K SET YKKCSL=(SELECT YKKCSL-YPZSL0 -- FROM (SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 -- FROM BQ_YZYPSQ -- WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND SFKCBZ='0'AND -- YZCLLB IN ('正常','基数药','领药','出院带药') AND -- DMBZ00='0' AND SFKSLY='Y' -- GROUP BY YFBMBH,YPNM00)S -- WHERE K.YFBMBH=S.YFBMBH AND K.YPNM00=S.YPNM00 ) -- WHERE (YFBMBH,YPNM00)in (SELECT YFBMBH,YPNM00 FROM BQ_YZYPSQ -- WHERE ZYID00=PZYID00 AND ID0000=PTQID00 AND -- YZCLLB IN ('正常','基数药','领药','出院带药') AND DMBZ00='0' AND SFKCBZ='0' AND SFKSLY='Y'); --EXCEPTION -- WHEN OTHERS THEN -- BEGIN -- ROLLBACK; -- RAISE E_YFWCYP;--药房无此药品 -- END; --END; --IF COUNTER>0 THEN --无此药品或库存不足,循环修改药品规格(未完成) -- UPDATE BQ_YZYPSQ Q SET YPNM00=(SELECT YPNM00 FROM BM_YD0000 WHERE YZYYP0=(SELECT YZYYP0 FROM BM_YD0000 WHERE YPNM00=Q.YPNM00) -- AND YPNM00<>Q.YPNM00 ); --修改其他药品信息 --END IF; --收药品费用(包括库存足，基数药处方、参数指定需收费的科室处方(包括医嘱和零散处方)) SELECT COUNT(*) INTO COUNTER FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZCLLB IN ('正常','基数药','领药','出院带药') AND --( YZCLLB='基数药' OR (PYPTQSF='Y' AND SFKSLY='Y' AND DMBZ00='0')) AND ( PYPTQSF='Y' AND SFKSLY='Y' AND DMBZ00='0') AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ --库存不足的不算 WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00); --1------------------------------------------------------------------------------------------- --1------------------------------------------------------------------------------------------- IF COUNTER>0 THEN --取收费单据号 --分为西药，成药和草药费分别插入收费明细表 --基数药处方收费 --根据不同的药房药房和不同的开单医生分别向ZY_FYMX00插入药费 INSERT INTO ZY_FYMX00( MXID00, DJH000, XMBH00, XMMC00, XMDJ00, XMSL00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDYS00, KDKS00, KDBQ00, ZXKS00, ZXYS00, YSKS00, SFLSCF, ZFBL00, YEXM00,YSZID0,ZXYSZ0,HJJE01) --执行医生工号 SELECT SQ_ZY_FYMX00_MXID00.NEXTVAL,VDJH000, SFDM00, SFXMMC,XMDJ00, 1, HJJE00, ZFJE00, QZFJE0, GFJE00, HJJE00-ZFJE00-GFJE00, TO_CHAR(PCZRQSJ, 'YYYYMMDD'), TO_CHAR(PCZRQSJ, 'HH24:MI:SS'), TO_CHAR(PCZRQSJ,'YYYYMMDD'), TO_CHAR(PCZRQSJ, 'HH24:MI:SS'), YSGZH0,KDKS00,PBQH000,YFBMBH,YSGZH0,NVL(Y.BMBH00,0),'2', ZFBL00,YEXM00,YSZID0,ZXYSZ0,HJJE01 FROM (SELECT SUM(ROUND(LSDJ00*YPZSL0,2))"XMDJ00",SUM(ROUND(LSDJ00*YPZSL0,2))"HJJE00", SUM(ROUND(ZFJE00,2))"ZFJE00",SUM(ROUND(QZFJE0,2))"QZFJE0", SUM(ROUND(GFJE00,2))"GFJE00", YSGZH0, YFBMBH, ZFBL00, B.SFDM00, B.SFXMMC,A.YEXM00,A.YSZID0 ,A.ZXYSZ0,SUM(ROUND(LSDJ00*YPZSL0,4)) HJJE01 ,decode(A.YZLX00,'3',A.KDKS00,VKSH000) KDKS00 FROM BQ_YZYPSQ A, BM_YPLB00 B WHERE ID0000=PTQID00 AND ZYID00=PZYID00 --AND YPLBBH='0' AND A.YPLBBH=B.LBBH00 AND (YZCLLB='基数药' OR (PYPTQSF='Y' AND SFKSLY='Y' AND DMBZ00='0')) AND YZCLLB NOT IN ('嘱托','自备药','门诊处理') AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00) GROUP BY YFBMBH,YSGZH0,ZFBL00,B.SFDM00,B.SFXMMC,A.YEXM00,A.YSZID0,A.ZXYSZ0,decode(A.YZLX00,'3',A.KDKS00,VKSH000) )S,BM_YGBM00 Y WHERE S.YSGZH0=Y.YGBH00(+); if SQL%NOTFOUND then Verrmsg:='生成费用明细表时发生错误'; RAISE NO_DATA_FOUND; end if; --住院病人费用表 if VSFKFYP='Y' or VSFBPDYK='Y' then COUNTER:=SF_BQ_MXTOFY(VDJH000, PZYID00, VZYH000, VBRID00, VBRXM00, VFBBH00, PJFLBID, PCZY000, PBQH000, 0,'3', PCZYXM0, PCZRQSJ); else COUNTER:=SF_BQ_MXTOFY(VDJH000, PZYID00, VZYH000, VBRID00, VBRXM00, VFBBH00, PJFLBID, PCZY000, PBQH000, 0,'1', PCZYXM0, PCZRQSJ); end if; --INSERT INTO ZY_BRFY00( -- DJH000, ZYID00, ZYH000, JFLBID, BRID00, XM0000, BRFB00 -- ,HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, BRYE00 -- ,CZRQ00, CZSJ00 -- ,CZY000, CZYXM0, CZYKS0, SFYKBZ ) --SELECT VDJH000, PZYID00, VZYH000, PJFLBID, VBRID00, VBRXM00, VFBBH00 -- ,SUM( ROUND(LSDJ00*YPZSL0,2)),SUM( ZFJE00),SUM(QZFJE0),SUM(GFJE00) -- ,SUM( ROUND(LSDJ00*YPZSL0,2))-SUM(ZFJE00)-SUM(GFJE00),0.0,--病人余额由触发器维护 -- TO_CHAR(PCZRQSJ,'YYYYMMDD'),TO_CHAR(PCZRQSJ,'HH24:MI:SS') -- ,PCZY000,PCZYXM0,PBQH000,DECODE(VSFKFYP,'Y','3','1')--已预扣,口服允许欠费，其他不允许 --FROM BQ_YZYPSQ --WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND -- (YZCLLB='基数药' OR (PYPTQSF='Y' AND SFKSLY='Y' AND DMBZ00='0'))AND -- YZCLLB NOT IN ('嘱托','自备药','门诊处理')AND -- YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ -- WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); --IF SQL%NOTFOUND THEN --没有收费滚回,继续下一个 -- BEGIN -- ROLLBACK; -- RAISE E_MYCFSC ; -- END; --END IF; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 END IF;--需要收费的处方收费完毕 --库存足够的病人自领的非毒零散处方(根据不同的药房和不同的开单医生汇总)-- FOR S IN C_FDLSCF LOOP SELECT SF_YK_GETLSH('ZYYF_ZYCFH') INTO VCFLSH0 FROM DUAL WHERE ROWNUM=1; INSERT INTO YF_ZYCF00 (CFLSH0, YFBMBH , ZYID00 , BRID00, ZYH000, BRXM00, CH0000 , XB0000 , CSRQ00 , BRDZ00 , YSGZH0 , DJH000 , YSXM00 , SSKS00 , DQBQ00 , DQKS00 , YPDLBH , CFXZ00 , BZID00 , BZMC00 , ZYTS00 , FJFDJH , CZY000 , CFZT00 , SRRQ00 , SRSJ00 , JZRQ00 , JZSJ00 , CFZJE0 , BZ0000 , SFKSLY , PSZT00 , CFSRBZ, TQDJH0 , ZYDJS0 , LYKS00,YEXM00,DYCJSL,DYCJQL,DECJSL,DECJQL,YSZID0,ZXYSZ0) SELECT VCFLSH0,S.YFBMBH, PZYID00, VBRID00, VZYH000, VBRXM00, VCH0000, VXB0000, VCSRQ00, NULL,S.YSGZH0, NULL,--DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)), Y.ZWXM00,Y.BMBH00, PBQH000, decode(PSFSSMZ,'Y',PSSKSBH,s.KDKS00), S.YPLBBH ,'0', NULL,NULL,DECODE(S.YPLBBH,'2',S.ZYTS00,1), NULL , PCZY000,'0',-- DECODE(TRIM(PYPTQSF),'Y','1',DECODE(S.YZCLLB,'基数药','1','0')), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), NULL,NULL, S.CFJE00,decode(s.SFJSY0,'M','出院带药',''),S.SFKSLY,'0',decode(PSFSSMZ,'Y','3',decode(s.SFJSY0,'M','5','4')),PTQID00, S.ZYDJS0,DECODE(S.SFKSLY,'Y',PBQH000,NULL),S.YEXM00, S.DYCJSL,S.DYCJQL,S.DECJSL,S.DECJQL,S.YSZID0,S.ZXYSZ0 FROM BM_YGBM00 Y WHERE Y.YGBH00=S.YSGZH0; IF NOT SQL%NOTFOUND THEN INSERT INTO YF_ZYCFMX( CFID00 , CFLSH0 , YPNM00 , YPMC00 , YPGG00 , GJDJ00 , LSDJ00 , PFDJ00 , YPYFMC , PCID00 , PCMC00 , CSL000 , JLDW00 , YYSJ00 , SFZBY0 , YYSJDW , ZQCD00 , YFSMMC , ZJYFMC , XMYFMC , YPZSL0 , CFDW00 , ZHL000 , CXBZ00 , CXCFID , PSLBID , SFXS00 , YPSCCJ , YPSCPH , KPHSS0 , HSKPPH , ZB0000 , BZ0000 , YZID00 , YZMXID , TSYS00 ,ZFFSBZ) SELECT SQ_YF_ZYCFMX_CFID00.NextVal ,VCFLSH0 ,Y.YPNM00 ,Y.YPMC00 , Y.YPGG00 ,Y.GJDJ00 , Y.LSDJ00,Y.PFDJ00 ,Y.YPYFMC ,Z.PCID00 ,Y.PCMC00 ,Y.CSL000 , Y.JLDW00, Z.ZQS000, 'N' ,Z.ZQDW00 ,Z.ZQCD00 ,Y.YFSMMC , NULL ,Y.XMYFMC ,Y.YPZSL0 , Y.QLDW00,Y.ZHL000 , 'Z' , NULL , NULL, 'Y' , NULL , NULL , NULL , NULL , NULL , M.BZ0000, Y.YZID00 ,Y.YZMXID,Z.TSYS00 ,M.ZFFSBZ FROM BQ_YPYZ00 Z,BQ_YPYZMX M,BQ_YZYPSQ Y WHERE Y.YZID00=Z.YZID00 AND Y.YZMXID=M.YZMXID AND Y.YFBMBH=S.YFBMBH AND DECODE(Y.YPLBBH,'2',Y.ZYTS00,1)=S.ZYTS00 AND Y.YSGZH0=S.YSGZH0 AND Y.YPLBBH=S.YPLBBH AND Y.ZYDJS0=S.ZYDJS0 AND Y.SFKSLY=S.SFKSLY AND Y.ID0000=PTQID00 AND Y.ZYID00=PZYID00 AND DECODE(Y.YZCLLB,'基数药','Y','出院带药','M','N')=S.SFJSY0 AND Y.ID0000=PTQID00 AND Y.ZYID00=PZYID00 AND Y.YZCLLB IN ('正常','基数药','领药','出院带药','应急领药')AND Y.DMBZ00='0' AND Y.SFKSLY='N' AND Y.YPZSL0>0 AND Y.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) and ((Y.YEXM00=S.YEXM00)or (Y.YEXM00 is null and S.YEXM00 is null)); --母亲医嘱YEXM00为空值 IF SQL%NOTFOUND THEN ROLLBACK; Verrmsg:='向YF_ZYCFMX插入非毒零散处方发生错误'; RAISE NO_DATA_FOUND; END IF; if PSFSSMZ='Y' or (PYPTQSF='Y' and BQ_CYDYYZSCZYLSCFFS='1') then begin SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO v_LSCFDJH0 FROM DUAL; -- select nvl(max(a.KSH000),0) into v_LSCFLYKS from BQ_YPYZ00 a,BQ_YZYPSQ b where b.ID0000=PTQID00 -- and b.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) and a.YZID00=b.YZID00; select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') into v_LSCFJZRQ,v_LSCFJZSJ from dual; UPDATE YF_ZYCF00 SET CFZT00='1',DJH000=v_LSCFDJH0,JZRQ00=v_LSCFJZRQ,JZSJ00=v_LSCFJZSJ,SFKSLY=decode(PSFSSMZ,'Y','Y','N'),LYKS00=PSSKSBH WHERE CFLSH0=VCFLSH0 AND CFZT00='0'; SP_YF_ZYCFJZ(VCFLSH0,v_LSCFJZRQ,v_LSCFJZSJ,PCZY000,v_LSCFDJH0); end; end if; END IF; END LOOP; --毒麻零散处方插入 FOR S IN C_DMYZID LOOP SELECT SF_YK_GETLSH('ZYYF_ZYCFH') INTO VCFLSH0 FROM DUAL WHERE ROWNUM=1; --零散处方插入 INSERT INTO YF_ZYCF00 ( CFLSH0, YFBMBH , ZYID00 , BRID00, ZYH000, BRXM00 , CH0000 , XB0000 , CSRQ00 , BRDZ00 , YSGZH0 , DJH000 , YSXM00 , SSKS00 , DQBQ00 , DQKS00 , YPDLBH , CFXZ00 , BZID00 , BZMC00 , ZYTS00 , FJFDJH , CZY000 , CFZT00 , SRRQ00 , SRSJ00 , JZRQ00 , JZSJ00 , CFZJE0 , BZ0000 , SFKSLY , PSZT00 , CFSRBZ, TQDJH0 , ZYDJS0 , LYKS00 , YEXM00 ,DYCJSL , DYCJQL, DECJSL , DECJQL,YSZID0,ZXYSZ0) SELECT VCFLSH0,S.YFBMBH, PZYID00, VBRID00, VZYH000, VBRXM00, VCH0000, VXB0000, VCSRQ00,Z.BRDZ00,S.YSGZH0, NULL,--DECODE(TRIM(PYPTQSF),'Y',VDJH000,DECODE(S.YZCLLB,'基数药',VDJH000,NULL)), Y.ZWXM00,Y.BMBH00, PBQH000, decode(PSFSSMZ,'Y',PSSKSBH,S.KDKS00), Z.YPLBBH ,Z.DMBZ00, Z.BZID00,Z.BZMC00,DECODE(S.YPLBBH,'2',S.ZYTS00,1), NULL , PCZY000,'0',-- DECODE(TRIM(PYPTQSF),'Y','1',DECODE(S.YZCLLB,'基数药','1','0')), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), NULL,--DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'YYYYMMDD'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL)), NULL,--DECODE(TRIM(PYPTQSF),'Y',TO_CHAR(VSYSDATE,'HH24:MI:SS'),DECODE(S.YZCLLB,'基数药',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL)), S.YPZSL0*S.LSDJ00,decode(nvl(S.ZDJKDJ,0),'0',Z.BZ0000,'重点监控') ,'N' ,'0',decode(S.YZCLLB,'出院带药','5',decode(PSFSSMZ,'Y','3','6')),PTQID00,S.ZYDJS0,DECODE(S.SFKSLY,'Y',PBQH000,NULL),Z.YEXM00, Z.DYCJSL,Z.DYCJQL,Z.DECJSL,Z.DECJQL,Z.YSZID0,Z.ZXYSZ0 FROM BM_YGBM00 Y,VW_BQ_YD0000 D,BQ_YPYZ00 Z WHERE Y.YGBH00=S.YSGZH0 AND D.YPNM00=S.YPNM00 AND D.YFBMBH=S.YFBMBH AND Z.YZID00=S.YZID00 ; IF NOT SQL%NOTFOUND THEN INSERT INTO YF_ZYCFMX( CFID00 , CFLSH0 , YPNM00 , YPMC00 , YPGG00 , GJDJ00 , LSDJ00 , PFDJ00 , YPYFMC , PCID00 , PCMC00 , CSL000 , JLDW00 , YYSJ00 , SFZBY0 , YYSJDW , ZQCD00 , YFSMMC , ZJYFMC , XMYFMC , YPZSL0 , CFDW00 , ZHL000 , CXBZ00 , CXCFID , PSLBID , SFXS00 , YPSCCJ , YPSCPH , KPHSS0 , HSKPPH , ZB0000 , BZ0000 , YZID00 , YZMXID ,ZFFSBZ ) SELECT SQ_YF_ZYCFMX_CFID00.NextVal ,VCFLSH0 ,S.YPNM00 ,S.YPMC00 , S.YPGG00 ,S.GJDJ00 , S.LSDJ00,S.PFDJ00 ,S.YPYFMC ,Z.PCID00 ,S.PCMC00 ,S.CSL000 , S.JLDW00,Z.ZQS000, 'N' ,Z.ZQDW00 ,Z.ZQCD00 ,S.YFSMMC , NULL ,S.XMYFMC ,S.YPZSL0 , S.QLDW00,S.ZHL000 , 'Z' , NULL , M.PSID00, 'Y' , NULL , NULL , NULL , NULL , NULL , M.BZ0000, S.YZID00 , S.YZMXID,M.ZFFSBZ FROM BQ_YPYZ00 Z,BQ_YPYZMX M WHERE Z.YZID00=S.YZID00 AND M.YZMXID=S.YZMXID; IF SQL%NOTFOUND THEN ROLLBACK; Verrmsg:='向YF_ZYCFMX插入毒麻零散处方发生错误'; RAISE NO_DATA_FOUND; END IF; END IF; --毒麻基数药零散处方要收费 UPDATE YF_ZYCF00 SET DJH000=VDJH000, CFZT00='1', JZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), JZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS') WHERE CFLSH0=VCFLSH0 AND S.YZCLLB='基数药' AND CFSRBZ IN ('4','5','6','7'); begin SP_BQ_XGDMCFZD(VCFLSH0,S.YZID00); exception when others then COUNTER:=0; end; if PSFSSMZ='Y' or (PYPTQSF='Y' and BQ_CYDYYZSCZYLSCFFS='1') then begin select count(*) into COUNTER from YF_ZYCF00 where CFLSH0=VCFLSH0 and CFZT00='0'; if COUNTER>0 then begin SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO v_LSCFDJH0 FROM DUAL; select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') into v_LSCFJZRQ,v_LSCFJZSJ from dual; UPDATE YF_ZYCF00 SET CFZT00='1',DJH000=v_LSCFDJH0,JZRQ00=v_LSCFJZRQ,JZSJ00=v_LSCFJZSJ,SFKSLY=decode(CFSRBZ,'5',SFKSLY,'Y'),LYKS00=PSSKSBH WHERE CFLSH0=VCFLSH0 AND CFZT00='0'; SP_YF_ZYCFJZ(VCFLSH0,v_LSCFJZRQ,v_LSCFJZSJ,PCZY000,v_LSCFDJH0); end; end if; end; end if; END LOOP; END IF; --插入医嘱时间提取记录(同组的要一起插入) --删除垃圾数据，否则会违反唯一约束 delete BQ_YZZXJL a where ZYID00=PZYID00 and (YZID00,YZMXID,JSRQ00,JSSJ00) in(SELECT B.YZID00,B.YZMXID, DECODE(B.CLBZ00,'1',DECODE(Z.ZQDW00,'小时',TO_CHAR(B.QYRQSJ+1,'YYYYMMDD'),TO_CHAR(B.QYRQSJ+Z.ZQS000,'YYYYMMDD')), decode(Z.ZQDW00,'小时',TO_CHAR(B.JZRQSJ,'YYYYMMDD'),TO_CHAR(VJZRQ00,'YYYYMMDD'))), decode(Z.ZQDW00,'小时',TO_CHAR(B.JZRQSJ,'HH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFSJFG,VZJSJFG)) FROM BQ_ZBTQYZ B,BQ_YPYZ00 Z WHERE ID0000=PTQID00 AND B.ZYID00=PZYID00 AND SJBZ00='1' AND B.YZID00=Z.YZID00 AND B.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) ) and not exists(select 1 from YF_YZYPSQ where TQDJH0=a.TQDJH0 and ZYID00=a.ZYID00 and YZID00=a.YZID00); BEGIN INSERT INTO BQ_YZZXJL ( ZYID00,YZMXID, TQDJH0, DJHLB0, FYDJH0, ZT0000, CZR000, YZID00, QSRQ00, QSSJ00, JSRQ00, JSSJ00, SJJSRQ, SJJSSJ, CZRQ00, CZSJ00, SCJSRQ, CS0000) SELECT PZYID00,YZMXID, PTQID00, '0' ,NVL(VDJH000,0),'0', PCZY000,B.YZID00, --TO_CHAR(TQRQSJ,'YYYYMMDD'), DECODE(Z.TQRQ00,NULL,Z.SJQSRQ,TO_CHAR(TQRQSJ,'YYYYMMDD')), DECODE(Z.TQSJ00,NULL,Z.SJQSSJ,DECODE(Z.KFBZ00,'Y',VKFSJFG,VZJSJFG)),-- --TO_CHAR(TQRQSJ,'HH24:MI:SS'), --2003.02.27一次开多天静滴的短嘱,静脉滴注费要收多天的 -- DECODE(B.CLBZ00,'1',DECODE(ZQDW00,'小时',TO_CHAR(QYRQSJ+1,'YYYYMMDD'),TO_CHAR(QYRQSJ+Z.ZQS000,'YYYYMMDD')),TO_CHAR(VJZRQ00,'YYYYMMDD')), DECODE(B.CLBZ00,'1',DECODE(ZQDW00,'小时',TO_CHAR(QYRQSJ+1,'YYYYMMDD'),TO_CHAR(QYRQSJ+Z.ZQS000,'YYYYMMDD')), decode(ZQDW00,'小时',TO_CHAR(JZRQSJ,'YYYYMMDD'),TO_CHAR(VJZRQ00,'YYYYMMDD'))), decode(ZQDW00,'小时',TO_CHAR(JZRQSJ,'HH24:MI:SS'),DECODE(Z.KFBZ00,'Y',VKFSJFG,VZJSJFG)), TO_CHAR(JZRQSJ,'YYYYMMDD'), TO_CHAR(JZRQSJ,'HH24:MI:SS'), TO_CHAR(PCZRQSJ,'YYYYMMDD'), TO_CHAR(PCZRQSJ,'HH24:MI:SS'), decode(Z.TQRQ00,null,null,TO_CHAR(TO_DATE(Z.TQRQ00,'yyyymmdd')+decode(Z.ZQDW00,'小时',0,1),'yyyymmdd')||Z.TQSJ00), CS0000 FROM BQ_ZBTQYZ B,BQ_YPYZ00 Z WHERE ID0000=PTQID00 AND B.ZYID00=PZYID00 AND SJBZ00='1' AND B.YZID00=Z.YZID00 AND B.YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); --插入如下表限制重复提取 insert into BQ_YZZXLS(TQDJH0,YZID00,YZMXID,ZXRQ00,ZXSJ00,ID0000,CZR000,ZYID00,DJHLB0,ZT0000) select PTQID00,b.YZID00,b.YZMXID,a.ZXRQ00,a.ZXSJ00,a.ID0000,PCZY000 ,(select ZYID00 from BQ_YPYZ00 where YZID00=a.YZID00),'0','0' from BQ_YZCFMX a,BQ_YZZXJL b where b.TQDJH0=PTQID00 and DJHLB0='0' and a.YZID00=b.YZID00 and a.ZXRQ00||a.ZXSJ00>=b.QSRQ00||b.QSSJ00 and a.ZXRQ00||a.ZXSJ00=b.QSRQ00 and ZXRQ00<=b.SJJSRQ) and exists(select 1 from BQ_YPYZ00 e,BQ_YPYZMX f where e.YZID00=b.YZID00 and e.ZYID00=PZYID00 and e.YZID00=f.YZID00 and f.YZCLLB in ('正常','基数药','领药','出院带药','应急领药') ); EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; IF SQLCODE='-02290' THEN --yzzxjl.qsrq00||qssj00'0'AND ZYID00=PZYID00); IF SQL%NOTFOUND THEN ROLLBACK; RAISE E_MYCFSC; END IF; --包药机药品设置 if ls_BYJTQFS='1' then UPDATE BQ_YPYZ00 A SET TQRQ00=(select to_char(JZRQSJ-1,'YYYYMMDD') from BQ_ZBTQYZ where ID0000=PTQID00 AND SJBZ00='1'AND ZYID00=PZYID00 and YZID00=A.YZID00 and rownum=1) WHERE ZYID00=PZYID00 AND CLBZ00='0' and KFBZ00='Y' and YZID00 IN (SELECT YZID00 FROM BQ_ZBTQYZ WHERE ID0000=PTQID00 AND SJBZ00='1'AND ZYID00=PZYID00) AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ B WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00 ); --YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ B,BM_YD0000 C -- WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00 and b.YPNM00=C.YPNM00 and C.YPCZFL='3' ); end if; --更改医嘱执行时间 UPDATE BQ_YPYZMX X SET (TQRQ00,TQSJ00)=(SELECT TO_CHAR(JZRQSJ,'YYYYMMDD'),TO_CHAR(JZRQSJ,'HH24:MI:SS') FROM BQ_ZBTQYZ T WHERE ID0000=PTQID00 AND SJBZ00='1' AND X.YZMXID=T.YZMXID AND ZYID00=PZYID00) WHERE YZMXID IN (SELECT YZMXID FROM BQ_ZBTQYZ WHERE ID0000=PTQID00 AND SJBZ00='1' AND ZYID00=PZYID00)AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00); IF SQL%NOTFOUND THEN ROLLBACK; RAISE E_MYCFSC ; END IF; --修改病区有药标志 UPDATE BM_BQFYZT B SET FYZT00='1' WHERE FYZT00 IN ('0','3') AND BQBMBH=PBQH000 AND YFBMBH IN (SELECT DISTINCT YFBMBH FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND DMBZ00='0'AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00)); --删除临时表纪录 DELETE FROM BQ_ZBTQYZ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZID00 IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0'AND ZYID00=PZYID00) ); IF SQL%NOTFOUND THEN ROLLBACK; RAISE E_MYCFSC ; END IF; DELETE FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND ZYID00=PZYID00 AND YZID00 NOT IN (SELECT YZID00 FROM BQ_YZYPSQ WHERE ID0000=PTQID00 AND SFKCBZ <>'0' AND ZYID00=PZYID00); IF SQL%NOTFOUND THEN ROLLBACK; RAISE E_MYCFSC ; END IF; if VYPSFDZX='Y' then UPDATE BQ_YZZXJL J SET SFXYZX='Y' WHERE ZYID00=PZYID00 AND ZT0000='0' AND DJHLB0='0'; else UPDATE BQ_YZZXJL J SET SFXYZX='Y' WHERE ZYID00=PZYID00 AND TQDJH0=PTQID00 AND DJHLB0='0' AND EXISTS (SELECT 1 FROM BQ_FJZXXM F WHERE J.ZYID00=F.ZYID00 AND J.YZID00=F.YZID00); end if; begin update yf_yzypsq a set hzbz00=(select decode(byfl00,'0','0','1','1','2','1') from bm_yd0000 where ypnm00=a.ypnm00 ) where ZYID00=PZYID00 AND TQDJH0=PTQID00 and exists ( select 1 from xt_yyxx00 where YYID00='220035'); exception when others then begin Verrmsg:='莆田专属医院字典包药分类没有维护，无法保存'; raise Ecustom; end; end; if vZYHZBZSFDAKFCL='Y' then update YF_YZYPSQ a set HZBZ00=decode(YPDLBH,'2','1',HZBZ00) where ZYID00=PZYID00 AND TQDJH0=PTQID00; end if; select nvl(max(trim(value0)),'N') into vXCYAZJFY from XT_XTCS00 where name00='BQ_XCYAZJHZFY'; if vXCYAZJFY='Y' then --西成药按针剂发药(长嘱，临嘱) update YF_YZYPSQ a set HZBZ00=decode(YPDLBH,'1','0',HZBZ00) where ZYID00=PZYID00 AND TQDJH0=PTQID00; end if; select nvl(max(trim(value0)),'N') into vXYCYLSYZAZJHZFY from XT_XTCS00 where name00='BQ_XYCYLSYZAZJHZFY'; if substr(vXYCYLSYZAZJHZFY,1,1)='Y' then --不论医生在录入西药、中成药临时医嘱时不管选择的是什么用法，都要发到针剂窗口发药 BQ_XYCYLSYZAZJHZFY格式 (Y/N:YYID01:YYID02:YYID03) update YF_YZYPSQ a set HZBZ00=decode(YPDLBH,'1','0','0','0',HZBZ00) where ZYID00=PZYID00 AND TQDJH0=PTQID00 and CLBZ00='1' and exists(select 1 from BM_BMBM00 where BMBH00=a.BQBMBH and (instr(':'||substr(vXYCYLSYZAZJHZFY,3,50)||':',':'||trim(YYID00)||':' )>0 or nvl(substr(vXYCYLSYZAZJHZFY,3,50),' ')=' ' )) ; end if; --修改警示颜色 update YF_YZYPSQ a SET TSYS00=(select TSYS00 from BQ_YPYZ00 where YZID00=a.YZID00) where ZYID00=PZYID00 AND TQDJH0=PTQID00; --修改病区口服有药标志 UPDATE BM_BQFYZT B SET KFFYZT='1' WHERE KFFYZT IN ('0','3') AND BQBMBH=PBQH000 AND YFBMBH IN (SELECT DISTINCT YFBMBH FROM YF_YZYPSQ WHERE ZYID00=PZYID00 AND TQDJH0=PTQID00 AND HZBZ00='1'); --修改病区针剂有药标志 UPDATE BM_BQFYZT B SET ZJFYZT='1' WHERE ZJFYZT IN ('0','3') AND BQBMBH=PBQH000 AND YFBMBH IN (SELECT DISTINCT YFBMBH FROM YF_YZYPSQ WHERE ZYID00=PZYID00 AND TQDJH0=PTQID00 AND HZBZ00='0'); COMMIT;--一个病人处方申请完毕 IF (PFJFTQSF='Y') or (PSFSSMZ='Y') THEN BEGIN SP_BQ_YPYZZX_BR(PCZY000,PZYID00,PBQH000,NULL,NULL,NULL,TO_CHAR(VJZRQ00,'YYYYMMDD'),PCZYXM0,PSFSSMZ,PSSKSBH); EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; -- RAISE E_FJFWK; END; END; END IF; EXCEPTION WHEN E_YFWCYP THEN RAISE_APPLICATION_ERROR(-20005,'药房无此药品，终止提取！*'); WHEN E_YFYPQY THEN RAISE_APPLICATION_ERROR(-20006,'药品库存数量不足，终止提取！*'); WHEN E_YFWCZD THEN RAISE_APPLICATION_ERROR(-20005,'该药房无织袋或纸袋没有隶属，终止提取！*'); WHEN E_YFZDBZ THEN RAISE_APPLICATION_ERROR(-20006,'该药房织袋库存数量不足，终止提取！*'); WHEN E_BRMYZH THEN RAISE_APPLICATION_ERROR(-20007,'病人没有帐户，终止提取！*'); WHEN E_BRZHBZ THEN RAISE_APPLICATION_ERROR(-20008,'病人押金不足，终止提取！*'); WHEN E_BRYJGB THEN RAISE_APPLICATION_ERROR(-20001,'有病人已经不在本病区，终止提取！*'); WHEN E_MYCFSC THEN RAISE_APPLICATION_ERROR(-20004, '没有生成申领药品，可能是库存不足，终止提取！*'); WHEN E_SFPDFY THEN RAISE_APPLICATION_ERROR(-20003, '药房正在对本病区进行发药，请稍后提取！*'); WHEN E_FJFWK THEN RAISE_APPLICATION_ERROR(-20004, '病人押金不足，附加费终止执行！*'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, '数据没有找到，终止提取！*'||Verrmsg); WHEN E_SCZDJL THEN RAISE_APPLICATION_ERROR(-20020, '自动带出织袋错误'||substrb(SQLERRM,1,220)); when E_YBSPerror then RAISE_APPLICATION_ERROR(-20021, substrb(LS_Error||'！*',1,220)); WHEN Ecustom THEN RAISE_APPLICATION_ERROR(-20010, Verrmsg); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20022, substrb('未知错误！*'||SQLERRM,1,220)); ROLLBACK; END SP_BQ_YPYZTQQR_BR; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YPYZTQQR_BR