CREATE OR REPLACE PROCEDURE SP_YF_BQYZFY( P_YPNM00 IN VARCHAR2 DEFAULT NULL, --输入：药品内码组合字符窜(格式 'xxxx*xxxx*xxxx...*') P_YFBMBH IN NUMBER, --输入：药房部门编号 P_BQBMBH IN NUMBER, --输入：病区部门编号 P_ZYID00 IN NUMBER DEFAULT NULL, --输入：病人住院ID P_CLBZ00 IN CHAR, --输入：长临标志:0,长嘱;1,短嘱,2急诊 P_HZBZ00 IN CHAR, --输入：汇总标志:0,针剂;1,口服 P_CZYBH0 IN NUMBER, --输入：操作员编码 P_LYRBH0 IN NUMBER, --输入：领药人编码 P_FHRBH0 IN NUMBER DEFAULT NULL, --输入：复核人编码 P_BYFHR0 IN NUMBER DEFAULT NULL, --输入：摆药复核人编码 P_CKDH00 OUT VARCHAR2, --输出：出库单号 P_Message OUT VARCHAR2, --输出：退回信息如病人缺钱等 P_SFTJ00 in varchar2 default 'Y', --输入：是否提交数据 P_PYR000 IN NUMBER DEFAULT NULL , --输入：配药人编码 P_SYXT00 in varchar2 default '1', --输入：使用系统 1,药房系统，2，新住院配药系统 P_SHBZ00 IN VARCHAR2 DEFAULT NULL --输入：审核结果 1，合格， 2，不合格， 默认全部 )AS V_COUNTER NUMBER; --记数器 V_COUNTER1 NUMBER; --记数器 V_FYZT00 BM_BQFYZT.FYZT00%TYPE; V_DQRQ00 YF_YZYPSQ.FYRQ00%TYPE; V_DQSJ00 YF_YZYPSQ.FYSJ00%TYPE; V_FYBZ00 YF_YZYPSQ.FYBZ00%TYPE; --发药标志:1,发药状态;2,单选择发药状态 V_ZYID00 YF_YZYPSQ.ZYID00%TYPE; V_ZYIDST YF_YZYPSQ.ZYID00%TYPE; V_ZYIDEN YF_YZYPSQ.ZYID00%TYPE; V_YPNM00 BM_YD0000.YPNM00%TYPE; V_CKDH00 YF_YPCKD0.CKDH00%TYPE; V_CH0000 ZY_BRXXB0.RYCWH0%TYPE; V_XM0000 ZY_BRXXB0.XM0000%TYPE; V_DLGS00 XT_XTCS00.VALUE0%TYPE; V_BQLYYZ XT_XTCS00.VALUE0%TYPE; V_YZID00 YF_YZYPSQ.YZID00%TYPE; V_TQDJH0 YF_YZYPSQ.TQDJH0%TYPE; ls_SFQYBJJJK NUMBER(1); --是否启用包药机接口 0 不启用 1启用 V_SFQYDJGLMK xt_xtcs00.value0%type; ls_YHMSG0 varchar2(400); ls_SYSMSG varchar2(400); V_DJDH00 number;--返回代煎单号 VYF_YZYPSQ yf_yzypsq%rowtype; V_error varchar2(255); L NUMBER; I NUMBER; J NUMBER; V_Message varchar2(2000); V_params varchar2(1500); --参数变量 V_errmsg varchar2(255); --错误提示变量 E_custom exception; --错误变量 E_TriStrop EXCEPTION; --触发器停止异常变量 E_TriStrop1 EXCEPTION; --触发器停止异常变量 V_BQFYXXTSSFYXMS XT_XTCS00.VALUE0%type; V_KFFYZT BM_BQFYZT.KFFYZT%type; V_ZJFYZT BM_BQFYZT.ZJFYZT%type; BQ_SFQYYTY XT_XTCS00.VALUE0%type; -- MODIFICATION HISTORY -- Person Date Comments -- JETHUA 2004.04.08 CREATE:将病区医嘱发药整合成一个过程,并增加批次预扣功能 -- JETHUA 2004.05.21 增加计算预扣总余额 -- JETHUA 2004.05.28 出库单增加备注：长临标志 -- JETHUA 2004.07.08 规范错误提示 -- JETHUA 2004.12.16 铁路医院无需领药确认 -- JETHUA 2005.01.06 修改医嘱状态,增加判断保护 -- JETHUA 2005.08.09 购进价为空 则付值 -- JETHUA 2005.09.07 优化语句，提高发药速度 -- JETHUA 2005.10.24 增加参数判断，是否需要护士确认 -- JETHUA 2006.02.28 药房单价格管理，在出库确认前要更新最新零售价 -- CSF 2009.12.25 判断触发器TR_YF_YZYPSQ_BFINTUPDDET/TR_YF_YZYPSQ_BINS_UPD是否停用，停用不允许操作该过程 -- chenyw 2010.04.12 把V_Message 从varchar2(255)扩到varchar2(2000); -- csf 2010.08.12 全区发药记账的游标将条QLZT00=0 改成qlzt00 in ('0','1'),否则会漏收煎药费 --laijg 2011.08.29 加大返回字节数P_Message:=substrb(V_Message,1,255); -- CSF 2011.11.18 农本方接口新格式处理。 --laijg 2012.06.14 病区口服发药、病区针剂发药写入复核人 by YF-20120604-002 --laijg 2012.06.15 病区口服发药写入摆药复核人 by YF-20120604-003 --laijg 2012.07.12 病区医嘱发药参数YF_SFQYDJGLGN启用后写入煎药信息 by YF-20120711-001 --csf 2014.02.11 包药机接口数据改用表(调用过程写入表YF_FYXX00表的数据) YF_SYBYJJKYF=Y在过程SP_YF_CKDDYFYXX中控制 YF-20140207-001 --daihq 2014.09.05 增加参数YF_BQFYXXTSSFYXMS控制病区发药信息提示是否用新模式 for YF-20140830-001 --daihq 2014.10.22 参数YF_BQFYXXTSSFYXMS增加M模式 for YF-20141017-001 --daihq 2016.05.09 增加是否提交数据，解决拆分失败药品已发药的问题 for ZYPY-20160425-001 --chenhn 2017.03.09 增加配药人 for YF-20170306-002 --huangjy 2018.01.29 增加新系统插入yf_ypckmx表 for YF-20180129-001 --hetf 2018.03.22 增加审核结果SHBZ00 for YF-20180126-002 -- dsm 2019.06.09 YF_YPCKMC.KSBH00 for YF-20190507-001 -- dsm 2020.04.09 增加急诊发药 for YF-20200324-001 --huangjy 2020.04.24 增加住院配药系统不通过此过程发送摆药机因为住院配药流程和药房系统流程不一样，住院配药是打印就发药了，不是再到发药时发药 ZYPY-20200423-001 --dsm 2022.12.14 增加参数YF_ZY_ZYJYF_SFDM异常判断防止漏收费 for ZYSF-20221213-001 --huangjy 2020.04.24 增加参数BQ_SFQYYTY启用病区预退药功能 for YF-20230417-001 --huangjy 2023.12.28 扩展V_params字段大小 for BQ-20231130-001 -- --------- ---------- ------------------------------------------- BEGIN V_errmsg:=''; V_params:='调用过程: SP_YF_BQYZFY('||P_YPNM00||','||TO_CHAR(P_YFBMBH)|| ','||TO_CHAR(P_BQBMBH)||','||TO_CHAR(P_ZYID00)||','||P_CLBZ00||','||P_HZBZ00|| ','||TO_CHAR(P_CZYBH0)||','||TO_CHAR(P_LYRBH0)||') ; '; P_CKDH00:=''; V_FYBZ00:='1'; V_ZYIDST:=0; V_ZYIDEN:=9999999999; select count(*) into V_COUNTER from XT_XTCS00 where NAME00='YF_ZYJYF_SFDM'; select count(*) into V_COUNTER1 from XT_XTCS00 where NAME00='YF_ZY_ZYJYF_SFDM'; if V_COUNTER>0 and V_COUNTER1=0 then --一般不可能 V_errmsg:='YF_ZY_ZYJYF_SFDM参数设置异常!'; RAISE E_custom; end if; --是否生成包药机接口要用的服药信息。 select NVL(MAX(INSTR(','||trim(VALUE0)||',',','||TO_CHAR(P_YFBMBH)||',',1)),0) into ls_SFQYBJJJK from XT_XTCS00 where NAME00='YF_SYBYJJKYF'; select count(*) into V_COUNTER from ALL_TRIGGERS where TRIGGER_NAME='TR_YF_YZYPSQ_BFINTUPDDET' and STATUS='ENABLED'; if V_COUNTER=0 then raise E_TriStrop; end if; select count(*) into V_COUNTER from ALL_TRIGGERS where TRIGGER_NAME='TR_YF_YZYPSQ_BINS_UPD' and STATUS='ENABLED'; if V_COUNTER=0 then raise E_TriStrop1; end if; --取系统参数：独立格式 0默认;1莆二;2泉二;3省二;4南安;5七院;6铁路;7建阳 BEGIN SELECT VALUE0 INTO V_DLGS00 FROM XT_XTCS00 WHERE NAME00='YF_ZJKFDDDYGS'; EXCEPTION WHEN OTHERS THEN V_DLGS00:='0'; END; --预退药抵扣 BEGIN SELECT VALUE0 INTO BQ_SFQYYTY FROM XT_XTCS00 WHERE NAME00='BQ_SFQYYTY'; EXCEPTION WHEN OTHERS THEN BQ_SFQYYTY:='0'; END; --领药是否需要护士确认 BEGIN SELECT Trim(VALUE0) INTO V_BQLYYZ FROM XT_XTCS00 WHERE NAME00='YF_BQLYYZ'; EXCEPTION WHEN OTHERS THEN V_BQLYYZ:='N'; END; --是否启用代煎管理系统 BEGIN SELECT Trim(VALUE0) INTO V_SFQYDJGLMK FROM XT_XTCS00 WHERE NAME00='YF_SFQYDJGLGN'; EXCEPTION WHEN OTHERS THEN V_SFQYDJGLMK:='N'; END; --病区发药信息提示是否用新模式，新模式把病区口服发药、病区针剂发药、住院处方发药和住院毒麻处方发药分开单独控制和提示，Y是N否，默认为N BEGIN SELECT nvl(max(Trim(VALUE0)),'N') INTO V_BQFYXXTSSFYXMS FROM XT_XTCS00 WHERE NAME00='YF_BQFYXXTSSFYXMS'; EXCEPTION WHEN OTHERS THEN V_BQFYXXTSSFYXMS:='N'; END; if V_BQFYXXTSSFYXMS='Y' then select KFFYZT,ZJFYZT into V_KFFYZT,V_ZJFYZT from BM_BQFYZT where BQBMBH=P_BQBMBH and YFBMBH=P_YFBMBH; if (P_HZBZ00='1') and (V_KFFYZT<>'2') then V_errmsg:='该病区口服发药状态被强行复位了,不能再进行发药操作'; RAISE E_custom; elsif (P_HZBZ00='0') and (V_ZJFYZT<>'2') then V_errmsg:='该病区针剂发药状态被强行复位了,不能再进行发药操作'; RAISE E_custom; end if; elsif V_BQFYXXTSSFYXMS='M' then select FYZT00,KFFYZT,ZJFYZT into V_FYZT00,V_KFFYZT,V_ZJFYZT from BM_BQFYZT where BQBMBH=P_BQBMBH and YFBMBH=P_YFBMBH; if (P_HZBZ00='1') and (V_KFFYZT<>'2') and (V_FYZT00<>'2') then V_errmsg:='该病区口服发药状态被强行复位了,不能再进行发药操作'; RAISE E_custom; elsif (P_HZBZ00='0') and (V_ZJFYZT<>'2') and (V_FYZT00<>'2') then V_errmsg:='该病区针剂发药状态被强行复位了,不能再进行发药操作'; RAISE E_custom; end if; else --判断病区发药状态 SELECT FYZT00 INTO V_FYZT00 FROM BM_BQFYZT WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH; IF V_FYZT00<>'2' THEN --不是发药状态，不能进行发药操作 V_errmsg:='该病区发药状态被强行复位了,不能再进行发药操作'; RAISE E_custom; END IF; end if; SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; --将药品选择发药置'2' IF LENGTH(P_YPNM00)>0 THEN V_FYBZ00:='2'; I:=0; J:=1; SELECT INSTR(P_YPNM00,'*',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 WHILE L>0 LOOP --取出药品批次组合字符窜中的药品内码 V_YPNM00:=TO_NUMBER(SUBSTR(P_YPNM00,I+1,L-I-1)); UPDATE YF_YZYPSQ SET FYBZ00='2' WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND YPNM00=V_YPNM00 AND CXBZ00='Z' AND FYBZ00='1' AND THBZ00='0' AND QLZT00 IN ('0','1') AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) and nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; I:=L; J:=J+1; SELECT INSTR(P_YPNM00,'*',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 END LOOP; ---FOR WHILE ... LOOP OUTER END IF; --******************记帐**************** IF P_ZYID00>0 THEN --单病人发药 SP_YF_ZYYZJZ(P_CZYBH0,P_YFBMBH,P_BQBMBH,P_ZYID00,V_FYBZ00,P_HZBZ00,P_CLBZ00,P_SHBZ00); --计算预扣总余额 SP_YF_BQYKYE(P_ZYID00); V_ZYIDST:=P_ZYID00; V_ZYIDEN:=P_ZYID00; ELSE --全区发药 --定义一个病区需要记账病人的游标逐个分别进行记账 DECLARE CURSOR C_YZYPSQ_BR IS SELECT DISTINCT ZYID00 FROM YF_YZYPSQ WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00 in ('0','1') AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) and nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; BEGIN ---AAA OPEN C_YZYPSQ_BR; --打开游标 FETCH C_YZYPSQ_BR INTO V_ZYID00; WHILE C_YZYPSQ_BR%FOUND LOOP --L --记帐 BEGIN SP_YF_ZYYZJZ(P_CZYBH0,P_YFBMBH,P_BQBMBH,V_ZYID00,V_FYBZ00,P_HZBZ00,P_CLBZ00,P_SHBZ00); --计算预扣总余额 SP_YF_BQYKYE(V_ZYID00); EXCEPTION WHEN OTHERS THEN IF SQLCODE=-20901 THEN V_error:='查找不到病人的账户信息'; ELSIF SQLCODE=-20902 THEN V_error:='病人的账户状态不是有效的状态!'; ELSIF SQLCODE=-20903 THEN V_error:='账户金额不够'; elsif SQLCODE=-20274 THEN V_error:='煎药费参数代码配置错误'; ELSE V_error:='记账出错,原因不详,可能是药品库存不足'; END IF; SELECT RYCWH0,XM0000 INTO V_CH0000,V_XM0000 FROM ZY_BRXXB0 WHERE ZYID00=V_ZYID00; V_error:=Trim(V_XM0000)||'('||Trim(V_CH0000)||')'||V_error; V_Message:=V_Message||V_error||','; END; FETCH C_YZYPSQ_BR INTO V_ZYID00; END LOOP; --L CLOSE C_YZYPSQ_BR; END; END IF; --2、发药操作(通过触发器修改药品库存) --对已经记账(非发药时)修改医嘱药品申请表YF_YZYPSQ状态 SELECT COUNT(*) INTO V_COUNTER FROM YF_YZYPSQ WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00='1' AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; IF V_COUNTER>0 THEN ---XXX V_CKDH00:=SF_YK_GETLSH('YF_CKDH00'); --向药房药品出库单表YF_YPCKD0插入出库数据(待确认) INSERT INTO YF_YPCKD0( CKDH00, --出库单号 SQ_YF_YPCKD0_CKDH00 YFBMBH, --药房部门编号 CKBMBH, --出库部门编号 CKLXBH, --出库类型编码 -->BM_YFCKLX CKZT00, --出库状态,'0'未确认，'1'已确认 CZY000, --操作员 SRRQ00, --输入日期 SRSJ00, --输入时间 BZ0000) VALUES( V_CKDH00, P_YFBMBH, P_BQBMBH, '03', '0', P_CZYBH0, V_DQRQ00, V_DQSJ00, DECODE(P_CLBZ00,'0','长嘱','1','短嘱','2','急诊','全部')); --向药品出库明细中插入病区针剂汇总领药信息 if P_SYXT00='2' then INSERT INTO YF_YPCKMX( YPCKPC, --药品出库批次 SQ_YF_YPCKMX_YPCKPC CKDH00, --出库单号 YF_YPCKD0_CKDH00 YPNM00, --药品内码 对应BM_YD0000表 YPMC00, --药品名称 YPGG00, --规格 CKDW00, --出库单位 GJDJ00, --购进单价(出库单位) LSDJ00, --零售单价(出库单位) PFDJ00, --批发单价(出库单位) CKSL00, --出库数量(出库单位) CKJE00, --出库金额 ZHL000, --转换率(出库单位/剂量单位) KSBH00 --科室编号 ) SELECT SQ_YF_YPCKMX_YPCKPC.NEXTVAL, V_CKDH00, YPNM00, YPMC00, YPGG00, QLDW00, GJDJ00, LSDJ00, PFDJ00, YPZSL0, CKJE00, ZHL000, DQKS00 FROM (SELECT a.YPNM00,a.YPMC00,a.YPGG00,a.QLDW00,a.GJDJ00,a.LSDJ00,a.PFDJ00, SUM(a.YPZSL0) AS YPZSL0,SUM(ROUND(a.LSDJ00*a.YPZSL0,2)) AS CKJE00,a.ZHL000,a.DQKS00 FROM YF_YZYPSQ a,BM_YD0000 b,YF_YPKCXX c WHERE a.YPNM00=b.YPNM00 AND a.YPNM00=c.YPNM00 AND a.YFBMBH=c.YFBMBH AND a.BQBMBH=P_BQBMBH AND a.YFBMBH=P_YFBMBH AND a.CXBZ00='Z' AND a.ZYID00>=V_ZYIDST AND a.ZYID00<=V_ZYIDEN AND a.FYBZ00=V_FYBZ00 AND a.THBZ00='0' AND a.QLZT00='1' AND a.HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(a.SHBZ00,'0') LIKE P_SHBZ00||'%' GROUP BY a.DQKS00,a.YPNM00,a.YPMC00,a.YPGG00,a.QLDW00,a.GJDJ00,a.LSDJ00,a.PFDJ00,a.ZHL000 ); else INSERT INTO YF_YPCKMX( YPCKPC, --药品出库批次 SQ_YF_YPCKMX_YPCKPC CKDH00, --出库单号 YF_YPCKD0_CKDH00 YPNM00, --药品内码 对应BM_YD0000表 YPMC00, --药品名称 YPGG00, --规格 CKDW00, --出库单位 GJDJ00, --购进单价(出库单位) LSDJ00, --零售单价(出库单位) PFDJ00, --批发单价(出库单位) CKSL00, --出库数量(出库单位) CKJE00, --出库金额 ZHL000, --转换率(出库单位/剂量单位) KSBH00) SELECT SQ_YF_YPCKMX_YPCKPC.NEXTVAL, V_CKDH00, YPNM00, YPMC00, YPGG00, QLDW00, GJDJ00, LSDJ00, PFDJ00, YPZSL0, CKJE00, ZHL000, DQKS00 FROM (SELECT YPNM00,YPMC00,YPGG00,QLDW00,GJDJ00,LSDJ00,PFDJ00, SUM(YPZSL0) AS YPZSL0,SUM(ROUND(LSDJ00*YPZSL0,2)) AS CKJE00,ZHL000,DQKS00 FROM YF_YZYPSQ WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00='1' AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%' GROUP BY DQKS00,YPNM00,YPMC00,YPGG00,QLDW00,GJDJ00,LSDJ00,PFDJ00,ZHL000 ); end if; --购进价为空 则付值 JETHUA 2005.08.09 UPDATE YF_YPCKMX X SET X.GJDJ00=(SELECT ROUND(D.GJJ000/D.K2J000*X.ZHL000,4) FROM BM_YD0000 D WHERE X.YPNM00=D.YPNM00) WHERE X.CKDH00=V_CKDH00 AND X.GJDJ00 IS NULL; --确认出库单（触发器TR_YF_YPCKD0_AFUPD修改库存） UPDATE YF_YPCKD0 SET CKZT00='1',QRR000=P_CZYBH0, QRRQ00=V_DQRQ00,QRSJ00=V_DQSJ00 WHERE CKDH00=V_CKDH00; IF P_LYRBH0=-1 AND V_BQLYYZ='N' THEN --发药时未进行领药人确认 IF (V_DLGS00='6') or (V_DLGS00='18') THEN --铁路医院无需领药确认 UPDATE YF_YZYPSQ Q SET CKDH00=V_CKDH00,QLZT00='4', FYBZ00='0',FYR000=P_CZYBH0, FYRQ00=V_DQRQ00,FYSJ00=V_DQSJ00, LYRQ00=V_DQRQ00,LYSJ00=V_DQSJ00,FHR000=P_FHRBH0,BYFHR0=P_BYFHR0,PYR000=P_PYR000 WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00='1' AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; IF SQL%NOTFOUND = TRUE THEN V_errmsg:='医嘱状态修改失败,请重试!'; RAISE E_custom; END IF; ELSE UPDATE YF_YZYPSQ Q SET CKDH00=V_CKDH00,QLZT00='2', FYBZ00='0',FYR000=P_CZYBH0, FYRQ00=V_DQRQ00,FYSJ00=V_DQSJ00,FHR000=P_FHRBH0,BYFHR0=P_BYFHR0,PYR000=P_PYR000 WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00='1' AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; IF SQL%NOTFOUND = TRUE THEN V_errmsg:='医嘱状态修改失败,请重试!'; RAISE E_custom; END IF; END IF; ELSE --领药发药合并为一个过程 UPDATE YF_YZYPSQ Q SET CKDH00=V_CKDH00,QLZT00='4', FYBZ00='0',FYR000=P_CZYBH0,LYR000=P_LYRBH0,LYCZY0=P_CZYBH0, FYRQ00=V_DQRQ00,FYSJ00=V_DQSJ00, LYRQ00=V_DQRQ00,LYSJ00=V_DQSJ00,FHR000=P_FHRBH0,BYFHR0=P_BYFHR0,PYR000=P_PYR000 WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND FYBZ00=V_FYBZ00 AND THBZ00='0' AND QLZT00='1' AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; IF SQL%NOTFOUND = TRUE THEN V_errmsg:='医嘱状态修改失败,请重试!'; RAISE E_custom; END IF; END IF; --3、修改退回药品的发药状态THBZ00<>'0' UPDATE YF_YZYPSQ SET FYBZ00='0' WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND ZYID00>=V_ZYIDST AND ZYID00<=V_ZYIDEN AND (FYRQ00=V_DQRQ00 OR QLZT00 IN ('0','1')) AND FYBZ00=V_FYBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND HZBZ00=P_HZBZ00 AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; END IF; ---XXX P_CKDH00:=Trim(V_CKDH00); P_Message:=substrb(V_Message,1,255); --将药品选择发药置'1' IF LENGTH(P_YPNM00)>0 THEN UPDATE YF_YZYPSQ SET FYBZ00='1' WHERE BQBMBH=P_BQBMBH AND YFBMBH=P_YFBMBH AND CXBZ00='Z' AND FYBZ00='2' AND THBZ00='0' AND QLZT00 IN ('0','1') AND HZBZ00=P_HZBZ00 AND (CLBZ00 LIKE P_CLBZ00||'%' or (P_CLBZ00='2' and PCMC00 in('st','ST'))) AND nvl(SHBZ00,'0') LIKE P_SHBZ00||'%'; END IF; --处理农本方接口 select count(*) into V_COUNTER from XT_XTCS00 where NAME00='YF_NBFJKXGS' and VALUE0='Y'; if V_COUNTER>0 then execute immediate 'begin SP_YF_GETNBFJKXX(''2'','''||P_CKDH00||'''); end;'; end if; --遍历该出库单下的提取单据号和医嘱ID，写入病区中药煎药信息 DECLARE CURSOR C_YZYPSQ_JYXX IS SELECT DISTINCT TQDJH0,YZID00 FROM YF_YZYPSQ WHERE CKDH00=V_CKDH00; BEGIN OPEN C_YZYPSQ_JYXX; --打开游标 FETCH C_YZYPSQ_JYXX INTO V_TQDJH0,V_YZID00; WHILE C_YZYPSQ_JYXX%FOUND LOOP --插入病区煎药信息 BEGIN select a.* into VYF_YZYPSQ from YF_YZYPSQ a where TQDJH0=V_TQDJH0 AND YZID00=V_YZID00 AND ROWNUM=1; IF ((V_SFQYDJGLMK='Y') and (VYF_YZYPSQ.YPDLBH='2') AND (VYF_YZYPSQ.ZYDJS0>0) ) THEN --by YF-20120711-001 SP_PY_INSERTZYJYXX('0',V_TQDJH0,V_YZID00,'1',ls_YHMSG0,ls_SYSMSG,V_DJDH00); end if; EXCEPTION WHEN OTHERS THEN V_params:=V_params||'插入煎药信息有误！'; END; FETCH C_YZYPSQ_JYXX INTO V_TQDJH0,V_YZID00; END LOOP; CLOSE C_YZYPSQ_JYXX; --关闭游标 END; if BQ_SFQYYTY='3' then --长乐区医院病区预退药抵扣 SP_BQ_YTYKCCL(0,V_CKDH00,P_BQBMBH,'1'); end if; --写入包药机信息 if (P_HZBZ00='1') and (ls_SFQYBJJJK>0) then --口服 全区发药 if trim(P_SYXT00) = '1' then SP_YF_CKDDYFYXX(V_CKDH00,P_CZYBH0); end if; end if; if trim(P_SFTJ00) = 'Y' then COMMIT; end if; EXCEPTION WHEN no_data_found THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001,substrb('数据没有找到！*'||SQLERRM||','||V_params,1,120)); WHEN E_custom THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20010,substrb(V_errmsg||'！*'||V_params,1,120)); WHEN E_TriStrop1 THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20010,substrb('触发器TR_YF_YZYPSQ_BINS_UPD被停止，不能继续本发药过程，请找管理员处理！*'||V_params,1,120)); WHEN E_TriStrop THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20010,substrb('触发器TR_YF_YZYPSQ_BFINTUPDDET被停止，不能继续本发药过程，请找管理员处理！*'||V_params,1,120)); WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*'||V_params,1,120)); END;