CREATE OR REPLACE PROCEDURE SP_BQ_YZYPTY ( PTQDJH0 IN NUMBER , --提取ID PYZID00 IN NUMBER , --医嘱ID PCZY000 IN NUMBER , --操作员 PZYID00 IN NUMBER , --住院ID PYPQLPC IN NUMBER , --药品请领批次 PSFBFTY IN NUMBER , --是否部分退药 PCZLSH0 OUT number --操作流水号 ) AS COUNTER NUMBER(5); --计数器 VYZYPGS NUMBER(5); --医嘱药品个数计数器 VYPQLPC YF_YZYPSQ.YPQLPC%TYPE; --请领批次 VCZYXM0 ZY_BRFY00.CZYXM0%TYPE; VCZYKS0 ZY_BRFY00.CZYKS0%TYPE; VYZID00 BQ_YPYZ00.YZID00%TYPE; VTQDJH0 YF_YZYPSQ.TQDJH0%TYPE; VCXBZ00 CHAR(1); VFYBZ00 CHAR(1); VJSRQ00 CHAR(8); VJSSJ00 CHAR(8); VJSRQSJ CHAR(16); VSYSDATE DATE; VQLZT00 YF_YZYPSQ.QLZT00%TYPE; --处方状态 VYZMXID BQ_YPYZMX.YZMXID%TYPE; --医嘱明细ID VJFLBID BM_BRJFLB.JFLBID%TYPE; --计费类别(帐户类别) VZYID00 ZY_BRXXB0.ZYID00%TYPE; --住院流水号 VZYH000 ZY_BRXXB0.ZYH000%TYPE; --住院号 VBRXM00 ZY_BRXXB0.XM0000%TYPE; --病人姓名 VFBBH00 BM_BRFBB0.FBBH00%TYPE; --病人费别(自费，公费，医保等) VBRID00 ZY_BRXXB0.BRID00%TYPE; --病人ID V_YFBMBH YF_YZYPSQ.YFBMBH%TYPE; --药房部门编号 V_BQBMBH YF_YZYPSQ.BQBMBH%TYPE; --病区部门编号 V_OUT_CKDH VARCHAR2(50); --冲销的出库单号 VDJH000 NUMBER(10); --收费单据号 VJDJH00 NUMBER(10); --原收费单据号 VHGBZ00 CHAR(1); --回滚标志 V_SFHG00 XT_XTCS00.VALUE0%TYPE; --医嘱是否回滚 V_SFTY00 XT_XTCS00.VALUE0%TYPE; --医嘱退药是否直接进行退药申请确认 VBCCXSL YF_YZYPSQ.BCCXSL%TYPE; --本次冲销数量 VJZTYBZ CHAR(1); --已结帐是否允许退药标志 VJZDH00 ZY_BRFY00.JZDH00%TYPE; --结算单号 V_XMCXSQ XT_XTCS00.VALUE0%TYPE; --结算过的项目发票未回收不允许进行项目冲消或退费申请 VJZWFYBZ CHAR(1); --已结帐未发药是否允许直接退费标志 VSFYJS0 CHAR(1); --结算标志 Ecustom exception; --错误变量 VErrMsg varchar2(255); --变量 V_JZRQ00 CHAR(8); --记账日期 V_YBCXJZRQ XT_XTCS00.VALUE0%TYPE; --医保限制冲销日期(默认为20010101) V_YBKH00 BM_BRXXB0.YBKH00%TYPE; --医保卡号 V_YBID00 BM_BRXXB0.YBID00%TYPE; --医保ID V_ZFFSBZ YF_YZYPSQ.ZFFSBZ%type; counter1 NUMBER(5); --计数器 VYPNM00 BM_YD0000.YPNM00%type; VYFBMBH YF_YZYPSQ.YFBMBH%type; V_TZYPXZTY varchar2(10); V_YCXYZCKTF varchar2(30); VDKSL00 YF_YZYPSQ.DKSL00%type; BQ_SFQYYTY varchar2(20); V_YPTWHSFBGYZZT varchar2(10); --药品退完是否变更医嘱状态 --已收费但未冲，未发医嘱药品的单据号 CURSOR C_DJH000 IS SELECT DISTINCT DJH000 FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND QLZT00='1' AND FYBZ00='0' AND CXBZ00='Z'; CURSOR C_YZID00 IS SELECT DISTINCT YZID00 FROM YF_YZYPSQ Y WHERE TQDJH0=VTQDJH0 AND ZYID00=PZYID00 AND CXBZ00 ='Z' AND FYBZ00='0' AND QLZT00 IN ('0','1','2','4'); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2008.05.15;09.26 住院发票未回收的结算记录，不允许进行项目冲消或退费申请ZYSF-20080506-001;增加参数BQ_JSFYSQTFFPHSCZ控制,同时恢复BQ_YJZYPSFYXTY YJ-20080926-002 -- qks 2008.10.28 修改一些10g不支持的写法 BQ-20081024-002 1、 -- qks 2009.02.03 退所有药品时，如果存在已记帐跟已发药的，已发药对应的冲销记录存在:(YF_YZYPSQ表)DJH000不为空,JZRQ00为空。BQ-20090202-002 -- zhangwz 2011.06.17 相关业务表增加YEXM00数据的插入. -- laijg 2012.01.12 修正YF_YZYPSQ.bz0000被冲领药单为空的问题。 by BQ-20120111-003 -- liuj 2012.01.16 增加参数ZY_YBCXJZRQ处理限制不允许冲销某个时间段之前的费用 by BQ-20120113-001 -- liuj 2012.08.21 增加参数 BQ_YJSWFYBZJTF 已经结算过，又没有发药的数据，只能做退费申请，不能直接退回账户。for BQ-20120806-004 -- dsm 2014.05.09 相关业务表写入YSZID0 for BQ-20140509-001 -- dsm 2015.12.14 退药时处理YF_YZYPSQ.ZFFSBZ FOR BQ-20151130-001 -- daihq 2015.12.17 解决BQ_YZYPTYSQQR=Y时同组药不同药房退药，只确认一条单据的问题 for BQ-20151112-001 -- dsm 2016.03.14 BQ_TZYPXZTY=Y时不能退药 for BQ-20160318-001 -- zhuyr 2019.01.24 增加判断，如果冲销的费用已经发送过日清单，就不让冲销 by ZYSF-20190122-003 -- dsm 2019.02.20 ZYSF-20190122-003很慢 归档 for BQ-20190220-002 -- dsm 2019.12.02 退药处理，如果提取日期回滚了BQ_YZZXLS也要同时删除,否则再次提取时会违反约束 for BQ-20191202-001 -- dsm 2020.11.17 增加BQ_YCXYZCKTF控制是否撤销医嘱才能退费 for BQ-20201127-001 -- dsm 2020.08.16 判断重复退药申请 for 归档BQ-20210813-001 -- dsm 2021.09.02 增加PCZLSH0 出参 for BQ-20210824-001 -- dsm 2022.01.17 部分退药没有返回PCZLSH0 for BQ-20220104-001 -- dsm 2023.04.10 增加抵扣功能 for BQ-20230413-002 -- dsm 2024.01.31 YF_YZYPSQ.SFMFBZ写入 BQ-20240130-001 -- dsm 2024.09.29 增加限制必须先撤销未确认的退药申请 for BQ-20240929-001 -- dsm 2024.10.12 医嘱管理-医嘱处方退药BQ_YPTWHSFBGYZZT启用时，医嘱状态不要改4 for BQ-20241006-001 BEGIN VSYSDATE:=SYSDATE; Verrmsg:='OK'; --****基本参数准备****---- --取出住院默认帐户类别号 SELECT COUNT(*) INTO COUNTER FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y'; IF COUNTER<>1 THEN VErrMsg:='病人计费类别错误'; RAISE Ecustom; ELSE SELECT JFLBID INTO VJFLBID FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y'; END IF; Begin Select UPPER(TRIM(VALUE0)) Into V_YCXYZCKTF From XT_XTCS00 Where NAME00 = 'BQ_YCXYZCKTF'; Exception When others then V_YCXYZCKTF := 'N'; End; Begin Select UPPER(TRIM(VALUE0)) Into V_YPTWHSFBGYZZT From XT_XTCS00 Where NAME00 = 'BQ_YPTWHSFBGYZZT'; --药品退完后是否变更医嘱状态，0变更，1不变更，默认0 Exception When others then V_YPTWHSFBGYZZT := '0'; End; --发送过日清单的记录不允许退费 if PZYID00 is not null then select count(*) into COUNTER from ZY_BRFY00 a,YF_YZYPSQ b where a.DJH000=b.DJH000 and b.ZYID00=PZYID00 and b.YZID00=PYZID00 and a.YBFSDH <> 0; if COUNTER > 0 then VErrMsg:='处方（项目）已发送日清单，不允许退费，请冲销日清单之后再试！'; raise Ecustom; end if; elsif PYPQLPC is not null then select count(*) into COUNTER from ZY_BRFY00 a,YF_YZYPSQ b where a.DJH000=b.DJH000 and b.ZYID00=PZYID00 and b.YPQLPC=PYPQLPC and a.YBFSDH <> 0; if COUNTER > 0 then VErrMsg:='处方（项目）已发送日清单，不允许退费，请冲销日清单之后再试！'; raise Ecustom; end if; end if; Begin Select UPPER(TRIM(VALUE0)) Into V_XMCXSQ From XT_XTCS00 Where NAME00 = 'BQ_JSFYSQTFFPHSCZ'; Exception When others then V_XMCXSQ := 'N'; End; --医嘱退药是否直接进行退药申请确认 Begin Select Value0 Into V_SFTY00 From XT_XTCS00 Where Name00='BQ_YZYPTYSQQR'; Exception when others then V_SFTY00:='N'; End; --医嘱退药:停止药品是否限制退药 for BQ-20160318-001 Begin Select substr(Value0,1,1) Into V_TZYPXZTY From XT_XTCS00 Where Name00='BQ_TZYPXZTY' and VALUE0='Y'; Exception when others then V_TZYPXZTY:='N'; End; --已结帐药品是否允许退药 Begin Select trim(Value0) Into VJZTYBZ from XT_XTCS00 where Name00='BQ_YJZYPSFYXTY'; Exception when others then VJZTYBZ:='Y'; End; --已结账未发药是否不直接退费 begin select trim(Value0) into VJZWFYBZ from xt_xtcs00 where name00='BQ_YJSWFYBZJTF'; Exception when others then VJZWFYBZ:='N'; end; --取操作员信息 SELECT ZWXM00,BMBH00 INTO VCZYXM0,VCZYKS0 FROM BM_YGBM00 WHERE YGBH00=PCZY000; select SQ_YF_YZYPSQ_CZLSH0.nextval into PCZLSH0 from dual; IF PSFBFTY=1 THEN GOTO LABEL2; END IF; Begin --取回滚信息 Select Value0 Into V_SFHG00 From XT_XTCS00 Where Name00='BQ_YZYPTYYZSFHG'; Exception when others then V_SFHG00:='1'; End; begin select substr(trim(VALUE0),1,1) into BQ_SFQYYTY from XT_XTCS00 where name00='BQ_SFQYYTY'; exception when others then BQ_SFQYYTY:='0'; end; --医保限制冲销日期(默认为20010101) Select nvl(max(trim(Value0)),'20010101') Into V_YBCXJZRQ From XT_XTCS00 Where Name00='ZY_YBCXJZRQ'; --select SQ_YF_YZYPSQ_CZLSH0.nextval into PCZLSH0 from dual; BQ-20220104-001移到goto前面 IF PYPQLPC IS NOT NULL THEN --***************************** 退一个药品 ********************************* select count(*) into counter from BQ_YPYZ00 where YZID00 in( select YZID00 from YF_YZYPSQ where YPQLPC=PYPQLPC) and YZZT00 in('3','2') and nvl(YCXBZ0,'0')='0' and CLBZ00='1'; if V_YCXYZCKTF='Y' and counter>0 then Verrmsg:='医生预撤销或已撤销医嘱，才能做退费申请！'; Raise Ecustom; end if; BEGIN SELECT QLZT00 ,YZMXID ,YZID00 ,TQDJH0 ,ZYID00 ,DJH000, JZRQ00,ZFFSBZ,YPNM00,YFBMBH INTO VQLZT00,VYZMXID,VYZID00,VTQDJH0,VZYID00,VJDJH00, V_JZRQ00,V_ZFFSBZ,VYPNM00,VYFBMBH FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC AND CXBZ00='Z' AND QLZT00 IN ('0','1','2','4') AND FYBZ00='0'; Select count(*) into counter from zy_brxxb0 where zyid00=VZYID00 and Substr(brzt00,1,1) in ('1','2','3'); if counter=0 then Verrmsg:='非在院病人，不能提出退药申请！'; raise Ecustom; end if; if V_TZYPXZTY='Y' then --BQ-20160318-001 select count(*) into counter from BM_YD0000 where YPNM00=VYPNM00 and TZRQ00 is not null; select count(*) into counter1 from YF_YPKCXX where YPNM00=VYPNM00 and YFBMBH=VYFBMBH and ZTBZ00='1'; if counter>0 or counter1>0 then Verrmsg:='此药品已经停止,不能退药！'; raise Ecustom; end if; end if; EXCEPTION--错误 WHEN OTHERS THEN BEGIN SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC; IF COUNTER=0 THEN RAISE_APPLICATION_ERROR(-20001,'没有该药品的领药记录！*'); END IF; SELECT QLZT00,CXBZ00,FYBZ00 INTO VQLZT00,VCXBZ00,VFYBZ00 FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC; IF VCXBZ00<>'Z' THEN RAISE_APPLICATION_ERROR(-20002,'该药品已退！*'); ELSIF VFYBZ00='1' THEN RAISE_APPLICATION_ERROR(-20003,'药房正在发药，病区无法退药！*'); ELSIF VQLZT00='3' THEN RAISE_APPLICATION_ERROR(-20005,'该药品已作废！*'); ELSE RAISE_APPLICATION_ERROR(-20004,'无法退药！*'); END IF; END; END; --已结帐药品不允许退药时处理 added by jlg for BQ-20061205-001 if VJZTYBZ='N' then select count(*) into counter from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and YPQLPC=PYPQLPC; if counter>0 then Verrmsg:='该药品收费处已结帐,病区不能退药！*'; Raise Ecustom; end if; end if; IF (V_XMCXSQ='Y') and (nvl(VJDJH00,0)<>0) then select b.jzdh00 into VJZDH00 from ZY_BRFY00 b where b.djh000=VJDJH00; if VJZDH00<>0 then select count(1) into counter from ZY_JZB000 where jzdh00=VJZDH00 and fphsbz='0'; if counter>0 then Verrmsg:='病区不能退药,该药品收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; Raise Ecustom; end if; end if; end if; -----------------FOR BQ-20120806-004----------------------------- begin select count(*) into counter from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and YPQLPC=PYPQLPC; if counter>0 then VSFYJS0:='Y'; else VSFYJS0:='N'; end if; end; ---------------------------------------------------------------------- SELECT COUNT(*) INTO VYZYPGS FROM BQ_YPYZMX WHERE YZID00=VYZID00 AND YZCLLB IN ('正常','基数药','领药','出院带药'); IF VYZYPGS=1 THEN GOTO LABEL1;--一组医嘱中只有一个药品可以处理，按同组医嘱处理。 END IF; --取病人信息,费别 SELECT BRID00,ZYH000,XM0000 INTO VBRID00,VZYH000,VBRXM00 FROM ZY_BRXXB0 WHERE ZYID00=VZYID00; SELECT FBBH00,YBKH00,YBID00 INTO VFBBH00, V_YBKH00, V_YBID00 FROM BM_BRXXB0 WHERE BRID00=VBRID00; --ZY_YBCXJZRQ 根据不同的YBZXLB获取不同的 YBCXJZRQ by liuj BQ-20120113-001 on 20120116 begin select SF_ZY_GETXTCS(c.YBZXLB,V_YBCXJZRQ) into V_YBCXJZRQ from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c where a.ZYID00=VZYID00 and a.BRID00=b.BRID00 and b.FBBH00=3 and b.FBBH00=c.FBBH00 and b.YBLB00=c.YBLB00; exception when others then V_YBCXJZRQ:='20010101'; end; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (VFBBH00=3) and (V_YBCXJZRQ<>'20010101') and (V_JZRQ00<>'20991231') then if (V_YBCXJZRQ >= V_JZRQ00) and (V_YBKH00 <> V_YBID00) and (instr(V_YBKH00,'@') <= 0) then Verrmsg:='不能冲销'||substr(V_YBCXJZRQ,1,4)||'年'||substr(V_YBCXJZRQ,5,2)||'月'||substr(V_YBCXJZRQ,7,2)||'日之前的费用'; raise Ecustom; end if; end if; ----select count(*) into counter from YF_YZYPSQ a where CXPCID=PYPQLPC and ZYID00=VZYID00 and QLZT00 in('0'); ---- if counter>0 then --- Verrmsg:='此药品存在未审核的退药申请，请先撤销退药申请，或者让药房做退药审核后再做退药申请'; ---- raise Ecustom; ---- end if; if not ((VSFYJS0='Y') and (VJZWFYBZ='Y')) then --已收费但未发药的药品,退费 IF VQLZT00='1' THEN SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO VDJH000 FROM DUAL WHERE ROWNUM=1; /*SELECT COUNT(*) INTO COUNTER FROM ZY_BRFY00 WHERE DJH000=VJDJH00 AND JZDH00<>0; IF COUNTER>0 THEN Verrmsg:='费用已结，不能退药'; RAISE Ecustom; END IF;*/ --退费，触发器自动更改帐户总余额(不处理预扣) INSERT INTO ZY_FYMX00(MXID00, DJH000, XMBH00, XMMC00, XMDJ00,XMSL00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDKS00, KDYS00, KDBQ00, ZXKS00, ZXYS00, CXBZ00,YSKS00,SFLSCF,ZFBL00,YEXM00,YSZID0) --执行医生工号 SELECT SQ_ZY_FYMX00_MXID00.NEXTVAL, VDJH000,B.SFDM00,B.SFXMMC, -1*ROUND(LSDJ00*YPZSL0,2),1, -1*ROUND(LSDJ00*YPZSL0,2), -1*ZFJE00, -1*QZFJE0, -1*GFJE00, -1*(ROUND(LSDJ00*YPZSL0,2)-ZFJE00-GFJE00), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DQKS00,YSGZH0,BQBMBH ,YFBMBH,YSGZH0,'-',SSKS00,'2',ZFBL00,a.YEXM00,a.YSZID0 FROM YF_YZYPSQ A,BM_YPLB00 B WHERE YPQLPC=PYPQLPC AND A.YPDLBH=B.LBBH00; UPDATE ZY_FYMX00 SET CXBZ00='%' WHERE DJH000=VJDJH00; --多批次 Lindj 2004.04.26 先要处理ZY_BRZHXX.BRYKYE,以便SF_BQ_MXTOFY插入ZY_BRFY00.BRYKYE --1-------------------------------------------------------------------------------------- UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC ) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; --1-------------------------------------------------------------------------------------- COUNTER:=SF_BQ_MXTOFY(VDJH000, VZYID00, VZYH000, VBRID00, VBRXM00, VFBBH00, VJFLBID, PCZY000, VCZYKS0, VJDJH00,'3', VCZYXM0, VSYSDATE) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 END IF;--退费过程执行完毕 END IF; --病区直接处理的,要退预扣费用/预扣库存 IF VQLZT00 IN ('0') THEN --多批次 Lindj 2004.04.26 --1-------------------------------------------------------------------------------------- UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC ) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; --1-------------------------------------------------------------------------------------- --病区直接处理的,要退预扣药品库存数量 20031027由触发器完成予扣 --UPDATE YF_YPKCXX K SET YKKCSL=(SELECT YKKCSL+YPZSL0*ZHL000 FROM YF_YZYPSQ S -- WHERE S.YPNM00=K.YPNM00 AND S.YFBMBH=K.YFBMBH AND YPQLPC=PYPQLPC) --WHERE (YFBMBH,YPNM00)in (SELECT YFBMBH,YPNM00 FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC); --IF SQL%NOTFOUND THEN -- Verrmsg:='药房无此药品'; -- RAISE Ecustom; --END IF; END IF; --修改执行纪录状态 UPDATE BQ_YZZXJL SET ZT0000='2' WHERE YZID00=VYZID00 AND TQDJH0=VTQDJH0 AND YZMXID=VYZMXID AND ZT0000 IN ('0','1'); IF VQLZT00='0' THEN UPDATE YF_YZYPSQ SET QLZT00='3' WHERE YPQLPC=PYPQLPC AND QLZT00='0';--作废处理 ELSE --多批次 Lindj 2004.04.26 DECODE(QLZT00,'1','1','0') --申请单处理 INSERT INTO YF_YZYPSQ( YPQLPC ,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000 , BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,DJH000 , ZYTS00 ,YSGZH0 ,QLZT00 ,FJFDJH ,YPDLBH ,YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00 ,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,YPZSL0 ,YPYFMC ,PCMC00 , YFSMMC ,ZJYFMC ,XMYFMC ,CXBZ00 ,CXPCID ,SFXS00 , CZY000 ,SRRQ00 ,SRSJ00 ,JZRQ00 ,JZSJ00 ,TQDJH0 , DQKS00 ,SSKS00 ,BZ0000 ,ZFBL00 ,GFJE00 ,ZFJE00 , QZFJE0 ,YEXM00,YSZID0,ZFFSBZ,CZLSH0,DKSL00,SFMFBZ ) SELECT SQ_YF_YZYPSQ_YPQLPC.NEXTVAL,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000 , BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,VDJH000, ZYTS00 ,YSGZH0 ,DECODE(QLZT00,'1','0','0') ,NULL ,YPDLBH ,YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,-1*YPZSL0 ,YPYFMC,PCMC00, YFSMMC ,ZJYFMC ,XMYFMC ,'-' , PYPQLPC,SFXS00 , PCZY000 ,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DECODE(QLZT00,'0',NULL,TO_CHAR(VSYSDATE,'YYYYMMDD')), DECODE(QLZT00,'0',NULL,TO_CHAR(VSYSDATE,'HH24:MI:SS')),TQDJH0, DQKS00,SSKS00,DECODE(VHGBZ00,'Y','HG',CKDH00),ZFBL00,-1*GFJE00,-1*ZFJE00, -1*QZFJE0,YEXM00,YSZID0,ZFFSBZ,PCZLSH0,decode(QLZT00,'1',-DKSL00,0),SFMFBZ FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC; --多批次 Lindj 2004.04.26 UPDATE YF_YZYPSQ SET QLZT00='2' WHERE ZYID00=VZYID00 AND CXPCID=PYPQLPC AND CXBZ00='-' AND QLZT00='1'; SELECT sum(YPZSL0) into counter FROM YF_YZYPSQ WHERE ZYID00=VZYID00 and ( YPQLPC=PYPQLPC or CXPCID=PYPQLPC); if counter<0 then Verrmsg:='重复退药，不能提出退药申请！'; raise Ecustom; end if; UPDATE YF_YZYPSQ SET QLZT00='6' WHERE ZYID00=VZYID00 AND DJH000=VDJH000 AND CXBZ00='-' AND QLZT00='0'; UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00=DECODE(QLZT00,'1','2',QLZT00) WHERE YPQLPC=PYPQLPC; END IF; END IF; --******* end of 退一个药品*******---- <> IF (PYZID00 IS NOT NULL) OR (VYZYPGS=1) THEN --判断是否退过部分药品 Select Count(*) Into counter From YF_YZYPSQ A Where A.YPQLPC In (Select B.YPQLPC From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 And QLZT00='0' And CXBZ00='-' AND YZID00=PYZID00); IF counter>0 THEN Verrmsg:='已退部分药的，不能再退全部以及同组药'; raise Ecustom; END IF; IF PYZID00 IS NOT NULL THEN--退同组 VYZID00:=PYZID00; VTQDJH0:=PTQDJH0; END IF; --*****************************退整组医嘱的药品*************************------------- --参数处理 SELECT ZYID00 INTO VZYID00 FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ROWNUM=1; Select count(*) into counter from zy_brxxb0 where zyid00=VZYID00 and Substr(brzt00,1,1) in ('1','2','3'); if counter=0 then Verrmsg:='非在院病人，不能提出退药申请！'; raise Ecustom; end if; --同组医嘱的至少部分药品正在发药 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='1' AND ROWNUM=1; IF COUNTER>0 THEN Verrmsg:='药房正在发药，病区不能退药'; RAISE Ecustom; END IF; --判定是否存在未冲的药品 SELECT COUNT(*), trim(min(JZRQ00)) INTO COUNTER,V_JZRQ00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1; IF COUNTER=0 THEN Verrmsg:='没有可退药品'; RAISE Ecustom; END IF; --要退的一组医嘱如果退过一个药品，那么剩下的也只能一个一个地退 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00 IN ('%','+') AND ROWNUM=1 AND SRRQ00||SRSJ00=(SELECT SRRQ00||SRSJ00 FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1); IF COUNTER>0 THEN Verrmsg:='该组医嘱已经退过药品，只能一次退一个药品'; RAISE Ecustom; END IF; --如果一组药品的状态不相同，必须状态一致后才能退药 SELECT COUNT(1) INTO COUNTER FROM ( SELECT DISTINCT QLZT00,CXBZ00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND SRRQ00||SRSJ00= (SELECT SRRQ00||SRSJ00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1)); IF COUNTER>1 THEN Verrmsg:='该组医嘱对应的药品状态不一致，必须一致后才可退药'; RAISE Ecustom; END IF; --已结帐药品不允许退药时处理 added by jlg for BQ-20061205-001 if VJZTYBZ='N' then select count(*) into counter from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0; if counter>0 then Verrmsg:='该药品收费处已结帐,病区不能退药！*'; Raise Ecustom; end if; end if; if V_TZYPXZTY='Y' then --BQ-20160318-001 select count(*) into counter from YF_YZYPSQ a,BM_YD0000 b where a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0 and a.YPNM00=b.YPNM00 and b.TZRQ00 is not null; select count(*) into counter1 from YF_YZYPSQ a,YF_YPKCXX b where a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0 and a.YFBMBH=b.YFBMBH and a.YPNM00=b.YPNM00 and b.ZTBZ00='1'; if counter>0 or counter1>0 then Verrmsg:='存在已经停止药品,不能退药！'; raise Ecustom; end if; end if; if V_XMCXSQ='Y' then select NVL(MAX(JZDH00),0) into VJZDH00 from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0; IF VJZDH00<>0 then select count(1) into counter from ZY_JZB000 where jzdh00=VJZDH00 and fphsbz='0'; if counter>0 then Verrmsg:='病区不能退药,该药品收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; Raise Ecustom; end if; end if; end if; select count(*) into counter from BQ_YPYZ00 where YZID00 in( select YZID00 from YF_YZYPSQ where TQDJH0=VTQDJH0 and YZID00=VYZID00) and YZZT00 in('3','2') and nvl(YCXBZ0,'0')='0' and CLBZ00='1'; if V_YCXYZCKTF='Y' and counter>0 then Verrmsg:='医生预撤销或已撤销医嘱，才能做退费申请！'; Raise Ecustom; end if; --取病人信息 SELECT BRID00,XM0000,ZYH000 INTO VBRID00,VBRXM00,VZYH000 FROM ZY_BRXXB0 WHERE ZYID00=VZYID00; --取病人费别 SELECT FBBH00,YBKH00,YBID00 INTO VFBBH00, V_YBKH00, V_YBID00 FROM BM_BRXXB0 WHERE BRID00=VBRID00; --ZY_YBCXJZRQ 根据不同的YBZXLB获取不同的 YBCXJZRQ by liuj BQ-20120113-001 on 20120116 begin select SF_ZY_GETXTCS(c.YBZXLB,V_YBCXJZRQ) into V_YBCXJZRQ from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c where a.ZYID00=VZYID00 and a.BRID00=b.BRID00 and b.FBBH00=3 and b.FBBH00=c.FBBH00 and b.YBLB00=c.YBLB00; exception when others then V_YBCXJZRQ:='20010101'; end; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (VFBBH00=3) and (V_YBCXJZRQ<>'20010101') and (V_JZRQ00<>'20991231') then if (V_YBCXJZRQ >= V_JZRQ00) and (V_YBKH00 <> V_YBID00) and (instr(V_YBKH00,'@') <= 0) then Verrmsg:='不能冲销'||substr(V_YBCXJZRQ,1,4)||'年'||substr(V_YBCXJZRQ,5,2)||'月'||substr(V_YBCXJZRQ,7,2)||'日之前的费用'; raise Ecustom; end if; end if; --退费处理,限于收费但未发，未冲药品 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z'AND FYBZ00='0' AND QLZT00='1'; IF COUNTER>0 THEN--退费 OPEN C_DJH000; LOOP FETCH C_DJH000 INTO VJDJH00; EXIT WHEN C_DJH000%NOTFOUND; ------------for BQ-20120806-004------------------------------ BEGIN SELECT COUNT(1) INTO COUNTER FROM ZY_BRFY00 A,ZY_JZB000 B WHERE A.DJH000=VJDJH00 AND A.JZDH00<>0 and A.JZDH00=B.JZDH00; if counter>0 then VSFYJS0:='Y'; else VSFYJS0:='N'; end if; END; ------------------------------------------------------------- if not ((VSFYJS0='Y') and (VJZWFYBZ='Y')) then SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO VDJH000 FROM DUAL WHERE ROWNUM=1; /*SELECT COUNT(*) INTO COUNTER FROM ZY_BRFY00 WHERE DJH000=VJDJH00 AND JZDH00<>0; IF COUNTER>0 THEN Verrmsg:='费用已结，不能退药'; RAISE Ecustom; END IF;*/ --插入药品退费明细 INSERT INTO ZY_FYMX00( MXID00, DJH000, XMBH00, XMMC00, XMDJ00,XMSL00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDKS00, KDYS00, KDBQ00, ZXKS00, ZXYS00, CXBZ00,YSKS00,SFLSCF,ZFBL00,YEXM00,YSZID0) --执行医生工号 SELECT SQ_ZY_FYMX00_MXID00.NEXTVAL, VDJH000, SFDM00, SFXMMC, XMDJ00,1, HJJE00, ZFJE00, QZFJE0, GFJE00, HJJE00-ZFJE00-GFJE00, TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DQKS00,YSGZH0,BQBMBH ,YFBMBH,YSGZH0,'-',SSKS00,'2',ZFBL00,YEXM00,YSZID0 FROM (SELECT -1*SUM(ROUND(LSDJ00*YPZSL0,2)) XMDJ00, -1*SUM(ROUND(LSDJ00*YPZSL0,2)) HJJE00, -1*SUM(ZFJE00) ZFJE00, -1*SUM(QZFJE0) QZFJE0, -1*SUM(GFJE00) GFJE00, A.DQKS00,A.BQBMBH ,A.YFBMBH,A.YSGZH0,A.SSKS00,A.ZFBL00,B.SFDM00,B.SFXMMC,a.YEXM00,YSZID0 FROM YF_YZYPSQ A,BM_YPLB00 B WHERE A.DJH000=VJDJH00 AND A.TQDJH0=VTQDJH0 AND A.YPDLBH=B.LBBH00 AND A.YZID00=VYZID00 AND A.CXBZ00='Z' AND A.FYBZ00='0' AND A.QLZT00='1' GROUP BY A.DQKS00,A.BQBMBH ,A.YFBMBH,A.YSGZH0,A.SSKS00,A.ZFBL00,B.SFDM00,B.SFXMMC,a.YEXM00,a.YSZID0 ); --修改费用明细的冲销标志 UPDATE ZY_FYMX00 SET CXBZ00='%' WHERE DJH000=VJDJH00; --修改执行记录状态 2006-08-17 qks --UPDATE BQ_YZZXJL SET ZT0000='2' where TQDJH0=VTQDJH0 AND YZID00=VYZID00; --多批次 Lindj 2004.04.26 先要处理ZY_BRZHXX.BRYKYE,以便SF_BQ_MXTOFY插入ZY_BRFY00.BRYKYE --1-------------------------------------------------------------------------------------- --病区直接处理的,退预扣金额/预扣药品库存 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('1'); IF COUNTER>0 THEN UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('1')) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; END IF; --1-------------------------------------------------------------------------------------- --退费，触发器自动更改帐户总余额 COUNTER:=SF_BQ_MXTOFY(VDJH000, VZYID00, VZYH000, VBRID00, VBRXM00, VFBBH00, VJFLBID, PCZY000, VCZYKS0, VJDJH00,'3', VCZYXM0, VSYSDATE) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 end if; END LOOP; CLOSE C_DJH000; END IF;--退费结束(已预扣) --病区直接处理的,退预扣金额/预扣药品库存 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('0'); IF COUNTER>0 THEN UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('0')) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; END IF; VHGBZ00:='N'; --如果是最后一次提取的医嘱，还应进行医嘱还原处理 SELECT COUNT(*) INTO COUNTER FROM BQ_YZZXJL WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1') AND QSRQ00||QSSJ00=(SELECT MAX(QSRQ00||QSSJ00) FROM BQ_YZZXJL WHERE YZID00=VYZID00 AND ZT0000<>'5'); IF COUNTER>0 THEN--最后一次提取，医嘱还原 --如果药未发，回滚，否则，不回滚 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0','1'); IF COUNTER>0 THEN IF V_SFHG00='1' THEN BEGIN UPDATE BQ_YZZXJL SET ZT0000='5' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); --2008.10.28 qks 10g不支持 --SELECT MAX(JSRQ00),MAX(JSSJ00) INTO VJSRQ00,VJSSJ00 FROM BQ_YZZXJL --WHERE ZYID00=VZYID00 AND YZID00=VYZID00 AND ZT0000 IN ('0','1','2'); SELECT MAX(JSRQ00||JSSJ00) INTO VJSRQSJ FROM BQ_YZZXJL WHERE ZYID00=VZYID00 AND YZID00=VYZID00 AND ZT0000 IN ('0','1','2'); VJSRQ00:=substrb(VJSRQSJ,1,8); VJSSJ00:=substrb(VJSRQSJ,9,8); IF VJSRQ00 IS NULL THEN--第一次的提取 UPDATE BQ_YPYZ00 SET TQRQ00=NULL, TQSJ00=NULL, ZXRQ00=NULL, YZZT00=DECODE(YZZT00,'2','1','3',decode(V_YPTWHSFBGYZZT,'1','3','4'),YZZT00), TZRXM0=DECODE(YZZT00,'2',TZRXM0,'3',VCZYXM0,TZRXM0) WHERE YZID00=VYZID00; UPDATE BQ_YPYZMX X SET TQRQ00=NULL,TQSJ00=NULL WHERE YZID00=VYZID00; delete BQ_YZZXLS where TQDJH0=VTQDJH0 and YZID00=VYZID00; ELSE UPDATE BQ_YPYZ00 SET TQRQ00=TO_CHAR(TO_DATE(VJSRQ00,'YYYYMMDD')-1,'YYYYMMDD'), TQSJ00=VJSSJ00 WHERE YZID00=VYZID00; UPDATE BQ_YPYZMX X SET (TQRQ00,TQSJ00)= (SELECT SJJSRQ,SJJSSJ FROM BQ_YZZXJL J WHERE J.YZID00=VYZID00 AND J.YZMXID=X.YZMXID AND JSRQ00=VJSRQ00 AND JSSJ00=VJSSJ00 AND ZT0000<>'5') WHERE YZID00=VYZID00; delete BQ_YZZXLS where TQDJH0=VTQDJH0 and YZID00=VYZID00; END IF; VHGBZ00:='Y'; EXCEPTION WHEN OTHERS THEN COUNTER:=0; END; ELSE UPDATE BQ_YZZXJL SET ZT0000='2' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); END IF; ELSE UPDATE BQ_YZZXJL SET ZT0000='2' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); END IF; --如果有抵扣数量，YF_YTYKC0.YKKCSL要加回去 select sum(nvl(DKSL00,0)) into VDKSL00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0'); if VDKSL00>0 then update YF_YTYKC0 a set YKKCSL=YKKCSL+(select nvl(DKSL00,0)*ZHL000 from YF_YZYPSQ where ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0') and YPNM00=a.YPNM00 and YFBMBH=a.YFBMBH and BQBMBH=a.BQBMBH) where (BQBMBH,YFBMBH,YPNM00) in (select BQBMBH,YFBMBH,YPNM00 from YF_YZYPSQ where ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0')); end if; DELETE FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0'); ELSE--不是最后一次提取,医嘱不回滚 UPDATE BQ_YZZXJL SET ZT0000='2' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); UPDATE YF_YZYPSQ SET QLZT00='3' WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0'); END IF; --多批次 Lindj 2004.04.26 DECODE(QLZT00,'1','1','0') --插入药品更正信息 INSERT INTO YF_YZYPSQ ( YPQLPC ,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000, BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,DJH000 , ZYTS00 ,YSGZH0 ,QLZT00 ,FJFDJH ,YPDLBH ,YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00 ,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,YPZSL0 ,YPYFMC ,PCMC00 , YFSMMC ,ZJYFMC ,XMYFMC ,CXBZ00 ,CXPCID ,SFXS00 , CZY000 ,SRRQ00 ,SRSJ00 ,JZRQ00 ,JZSJ00 ,TQDJH0 , SSKS00, DQKS00 ,BZ0000 ,ZFBL00 ,GFJE00 ,ZFJE00 , QZFJE0 , YEXM00,YSZID0,ZFFSBZ,CZLSH0,DKSL00,SFMFBZ) SELECT SQ_YF_YZYPSQ_YPQLPC.NEXTVAL,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000 , BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,VDJH000, ZYTS00 ,YSGZH0 ,DECODE(QLZT00,'1','0','0'),NULL ,YPDLBH , YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00 ,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,-1*YPZSL0 ,YPYFMC ,PCMC00 , YFSMMC ,ZJYFMC ,XMYFMC ,'-' , YPQLPC ,SFXS00 , PCZY000 ,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL),DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL),TQDJH0, SSKS00,DQKS00,DECODE(VHGBZ00,'Y','HG',CKDH00),ZFBL00,-1*GFJE00,-1*ZFJE00, -1*QZFJE0,YEXM00,YSZID0,ZFFSBZ,PCZLSH0,decode(QLZT00,'1',-DKSL00,0),SFMFBZ FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('1','2','4'); --多批次 Lindj 2004.04.26 --判断是否存在重复退药 select count(*) into counter1 from ( select YZID00,YZMXID,YPNM00,sum(YPZSL0) YPZSL0 from YF_YZYPSQ where ZYID00=VZYID00 and YZID00=VYZID00 group by YZID00,YZMXID,YPNM00 ) where YPZSL0<0; if counter1>0 then Verrmsg:='病人存在重复退药，退药失败'; RAISE Ecustom; end if; UPDATE YF_YZYPSQ SET QLZT00='6' WHERE ZYID00=VZYID00 AND DJH000=VDJH000 AND YZID00=VYZID00 AND CXBZ00='-' AND FYBZ00='0'AND QLZT00='0'; --已记账->已退费 UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00='6',ZCXSL0=YPZSL0, BCCXSL=0 WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0','1'); --已发药/未审核-> UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00=DECODE(QLZT00,'1','2',QLZT00),BCCXSL=YPZSL0 WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('2','4'); --UPDATE YF_YZYPSQ SET QLZT00='2' --WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='-' AND FYBZ00='0'AND QLZT00='1'; --UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00=DECODE(QLZT00,'1','2',QLZT00) --WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('1','2','4'); ELSIF PZYID00 IS NOT NULL THEN --**************************退一个病人的所有药品**********************************------- VZYID00:=PZYID00; VTQDJH0:=PTQDJH0; Select count(*) into counter from zy_brxxb0 where zyid00=VZYID00 and Substr(brzt00,1,1) in ('1','2','3'); if counter=0 then Verrmsg:='非在院病人，不能提出退药申请！'; raise Ecustom; end if; --存在可冲项目 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND ZYID00=VZYID00 AND FYBZ00='1' and rownum=1; IF COUNTER>0 THEN--同组医嘱的至少部分药品正在发药 Verrmsg:='药房正在发药，病区不能退药'; RAISE Ecustom; END IF; --判定是否存在未冲的药品 SELECT COUNT(*),trim(min(JZRQ00)) INTO COUNTER,V_JZRQ00 FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND ZYID00=VZYID00 AND FYBZ00='0'AND CXBZ00='Z' and rownum=1; IF COUNTER=0 THEN Verrmsg:='没有可退药品'; RAISE Ecustom; END IF; --已结帐药品不允许退药时处理 added by jlg for BQ-20061205-001 if VJZTYBZ='N' then select count(*) into counter from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0; if counter>0 then Verrmsg:='该药品收费处已结帐,病区不能退药！*'; Raise Ecustom; end if; end if; if V_XMCXSQ='Y' then select NVL(MAX(JZDH00),0) into VJZDH00 from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0; IF VJZDH00<>0 then select count(1) into counter from ZY_JZB000 where jzdh00=VJZDH00 and fphsbz='0'; if counter>0 then Verrmsg:='病区不能退药,该药品收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; Raise Ecustom; end if; end if; end if; if V_TZYPXZTY='Y' then --BQ-20160318-001 select count(*) into counter from YF_YZYPSQ a,BM_YD0000 b where a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0 and a.YPNM00=b.YPNM00 and b.TZRQ00 is not null; select count(*) into counter1 from YF_YZYPSQ a,YF_YPKCXX b where a.ZYID00=VZYID00 and a.TQDJH0=VTQDJH0 and a.YFBMBH=b.YFBMBH and a.YPNM00=b.YPNM00 and b.ZTBZ00='1'; if counter>0 or counter1>0 then Verrmsg:='存在已经停止药品,不能退药！'; raise Ecustom; end if; end if; select count(*) into counter from BQ_YPYZ00 where YZID00 in( select YZID00 from YF_YZYPSQ where ZYID00=VZYID00 and TQDJH0=VTQDJH0 ) and YZZT00 in('3','2') and nvl(YCXBZ0,'0')='0' and CLBZ00='1'; if V_YCXYZCKTF='Y' and counter>0 then Verrmsg:='医生预撤销或已撤销医嘱，才能做退费申请！'; Raise Ecustom; end if; --取病人信息 SELECT BRID00,XM0000,ZYH000 INTO VBRID00,VBRXM00,VZYH000 FROM ZY_BRXXB0 WHERE ZYID00=VZYID00; --取病人费别 SELECT FBBH00,YBKH00,YBID00 INTO VFBBH00, V_YBKH00, V_YBID00 FROM BM_BRXXB0 WHERE BRID00=VBRID00; --ZY_YBCXJZRQ 根据不同的YBZXLB获取不同的 YBCXJZRQ by liuj BQ-20120113-001 on 20120116 begin select SF_ZY_GETXTCS(c.YBZXLB,V_YBCXJZRQ) into V_YBCXJZRQ from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c where a.ZYID00=VZYID00 and a.BRID00=b.BRID00 and b.FBBH00=3 and b.FBBH00=c.FBBH00 and b.YBLB00=c.YBLB00; exception when others then V_YBCXJZRQ:='20010101'; end; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (VFBBH00=3) and (V_YBCXJZRQ<>'20010101') and (V_JZRQ00<>'20991231') then if (V_YBCXJZRQ >= V_JZRQ00) and (V_YBKH00 <> V_YBID00) and (instr(V_YBKH00,'@') <= 0) then Verrmsg:='不能冲销'||substr(V_YBCXJZRQ,1,4)||'年'||substr(V_YBCXJZRQ,5,2)||'月'||substr(V_YBCXJZRQ,7,2)||'日之前的费用'; raise Ecustom; end if; end if; --循环处理每组医嘱 FOR YZ IN C_YZID00 LOOP VYZID00:=YZ.YZID00; --判定是否存在未冲的药品 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1; IF COUNTER=0 THEN Verrmsg:='退药不完全成功，医嘱号为"'||TO_CHAR(VYZID00)||'"没有可退药品'; GOTO BOTTOM; END IF; --要退的一组医嘱如果退过一个药品，那么剩下的也只能一个一个地退 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='+' AND ROWNUM=1 AND SRRQ00||SRSJ00=(SELECT SRRQ00||SRSJ00 FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1); IF COUNTER>0 THEN Verrmsg:='退药不完全成功，医嘱号为"'||TO_CHAR(VYZID00)||'"已经退过药品，只能一次退一个药品'; GOTO BOTTOM; END IF; --如果一组药品的状态不相同，必须状态一致后才能退药 SELECT COUNT(1) INTO COUNTER FROM ( SELECT DISTINCT QLZT00,CXBZ00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND SRRQ00||SRSJ00= (SELECT SRRQ00||SRSJ00 FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00='Z' AND ROWNUM=1)); IF COUNTER>1 THEN Verrmsg:='退药不完全成功，医嘱号为"'||TO_CHAR(VYZID00)||'"对应的药品状态不一致，必须一致后才可药品'; GOTO BOTTOM; END IF; --退费处理,限于收费但未发，未冲药品 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z'AND FYBZ00='0' AND QLZT00='1'; IF COUNTER>0 THEN--退费 OPEN C_DJH000; LOOP FETCH C_DJH000 INTO VJDJH00; EXIT WHEN C_DJH000%NOTFOUND; SELECT SQ_ZY_BRJFB0_DJH000.NEXTVAL INTO VDJH000 FROM DUAL WHERE ROWNUM=1; /*SELECT COUNT(*) INTO COUNTER FROM ZY_BRFY00 WHERE DJH000=VJDJH00 AND JZDH00<>0; IF COUNTER>0 THEN Verrmsg:='费用已结，不能退药'; RAISE Ecustom; END IF;*/ IF V_XMCXSQ='Y' then SELECT COUNT(1) INTO COUNTER FROM ZY_BRFY00 A,ZY_JZB000 B WHERE A.DJH000=VJDJH00 AND A.JZDH00<>0 and A.JZDH00=B.JZDH00 and B.FPHSBZ='0'; IF COUNTER>0 THEN Verrmsg:='病区不能退药,该药品收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; RAISE Ecustom; END IF; end if; ----------------- FRO BQ-20120806-004--------------------------- BEGIN SELECT COUNT(1) INTO COUNTER FROM ZY_BRFY00 A,ZY_JZB000 B WHERE A.DJH000=VJDJH00 AND A.JZDH00<>0 and A.JZDH00=B.JZDH00; if counter>0 then VSFYJS0:='Y'; else VSFYJS0:='N'; end if; END; ----------------------------------------------------------------- if not ((VSFYJS0='Y') and (VJZWFYBZ='Y')) then --插入药品退费明细 INSERT INTO ZY_FYMX00( MXID00, DJH000, XMBH00, XMMC00, XMDJ00,XMSL00, HJJE00, ZFJE00, QZFJE0, GFJE00, JZJE00, CZRQ00, CZSJ00, ZXRQ00, ZXSJ00, KDKS00, KDYS00, KDBQ00, ZXKS00, ZXYS00, CXBZ00,YSKS00,SFLSCF,ZFBL00,YEXM00,YSZID0) --执行医生工号 SELECT SQ_ZY_FYMX00_MXID00.NEXTVAL, VDJH000, SFDM00, SFXMMC, XMDJ00,1, HJJE00, ZFJE00, QZFJE0, GFJE00, HJJE00-ZFJE00-GFJE00, TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DQKS00,YSGZH0,BQBMBH ,YFBMBH,YSGZH0,'-',SSKS00,'2',ZFBL00,YEXM00,YSZID0 FROM (SELECT -1*SUM(ROUND(LSDJ00*YPZSL0,2)) XMDJ00, -1*SUM(ROUND(LSDJ00*YPZSL0,2)) HJJE00, -1*SUM(ZFJE00) ZFJE00, -1*SUM(QZFJE0) QZFJE0, -1*SUM(GFJE00) GFJE00, DQKS00,BQBMBH ,YFBMBH,YSGZH0,SSKS00,ZFBL00,B.SFDM00,B.SFXMMC,a.YEXM00,a.YSZID0 FROM YF_YZYPSQ A,BM_YPLB00 B WHERE A.DJH000=VJDJH00 AND A.TQDJH0=VTQDJH0 AND A.YPDLBH=B.LBBH00 AND A.YZID00=VYZID00 AND A.CXBZ00='Z' AND A.FYBZ00='0' AND A.QLZT00='1' GROUP BY A.DQKS00,A.BQBMBH ,A.YFBMBH,A.YSGZH0,A.SSKS00,A.ZFBL00,B.SFDM00,B.SFXMMC,a.YEXM00,a.YSZID0 ); --修改费用明细的冲销标志 UPDATE ZY_FYMX00 SET CXBZ00='%' WHERE DJH000=VJDJH00 ; --多批次 Lindj 2004.04.26 先要处理ZY_BRZHXX.BRYKYE,以便SF_BQ_MXTOFY插入ZY_BRFY00.BRYKYE --1-------------------------------------------------------------------------------------- --病区直接处理的,退预扣金额/预扣药品库存 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('1'); IF COUNTER>0 THEN UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('1')) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; END IF; --1-------------------------------------------------------------------------------------- --退费，触发器自动更改帐户总余额 COUNTER:=SF_BQ_MXTOFY(VDJH000, VZYID00, VZYH000, VBRID00, VBRXM00, VFBBH00, VJFLBID, PCZY000, VCZYKS0, VJDJH00,'3', VCZYXM0, VSYSDATE) ; SP_ZY_GJYEXMCFZYFY_NOCOMMIT(VDJH000); --婴儿费用拆分 --INSERT INTO ZY_BRFY00( -- DJH000, ZYID00, ZYH000, JFLBID, BRID00, XM0000, BRFB00 -- ,HJJE00 -- ,ZFJE00 -- ,QZFJE0 -- ,GFJE00 -- ,JZJE00 -- ,BRYE00 -- ,CZRQ00, CZSJ00 -- ,CZY000, CZYXM0, CZYKS0,CXDJH0,SFYKBZ ) --SELECT VDJH000,VZYID00, VZYH000,VJFLBID,VBRID00,VBRXM00,VFBBH00 -- ,-1*SUM( ROUND(LSDJ00*YPZSL0,2)) -- ,-1*SUM(ZFJE00) -- ,-1*SUM(QZFJE0) -- ,-1*SUM(GFJE00) -- ,-1*(SUM( ROUND(LSDJ00*YPZSL0,2))-SUM(ZFJE00)-SUM(GFJE00)) -- ,0.0--目前全部自费,病人余额由触发器维护 -- ,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS') -- ,PCZY000,VCZYXM0,VCZYKS0 ,VJDJH00,'3' --FROM YF_YZYPSQ --WHERE DJH000=VJDJH00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND -- CXBZ00='Z' AND FYBZ00='0' AND QLZT00='1' ; end if; END LOOP; CLOSE C_DJH000; END IF;--退费结束(已预扣) --病区直接处理的,退预扣金额/预扣药品库存 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('0'); IF COUNTER>0 THEN UPDATE ZY_BRZHXX SET YKZYE0=(SELECT YKZYE0+SUM(ZFJE00) FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN('0')) WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0'; IF SQL%NOTFOUND THEN Verrmsg:='病人帐户不正常'; RAISE Ecustom; END IF; --病区直接处理的,退预扣药房药品的预扣数量 20031027由触发器完成予扣 --UPDATE YF_YPKCXX K SET YKKCSL=(SELECT YKKCSL+YPZSL0 FROM -- (SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 FROM YF_YZYPSQ -- WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZYID00=VZYID00 AND -- CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN ('0','1') -- GROUP BY YFBMBH,YPNM00 -- ) S -- WHERE K.YFBMBH=S.YFBMBH AND K.YPNM00=S.YPNM00 ) --WHERE (YFBMBH,YPNM00)in (SELECT YFBMBH,YPNM00 FROM YF_YZYPSQ WHERE -- TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZYID00=VZYID00 AND -- CXBZ00='Z' AND FYBZ00='0' AND QLZT00 IN ('0','1')); --IF SQL%NOTFOUND THEN --Verrmsg:='药房无此药品'; -- RAISE Ecustom; --END IF; END IF; VHGBZ00:='N'; --如果是最后一次提取的医嘱，还应进行医嘱还原处理 SELECT COUNT(*) INTO COUNTER FROM BQ_YZZXJL WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1')AND QSRQ00||QSSJ00=(SELECT MAX(QSRQ00||QSSJ00) FROM BQ_YZZXJL WHERE YZID00=VYZID00 AND ZT0000<>'5'); IF COUNTER>0 THEN--最后一次提取，医嘱还原 --如果药未发，回滚，否则，不回滚 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0','1'); IF COUNTER>0 THEN IF V_SFHG00='1' THEN BEGIN UPDATE BQ_YZZXJL SET ZT0000='5' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); --2008.10.28 qks 10g不支持 --SELECT MAX(JSRQ00),MAX(JSSJ00) INTO VJSRQ00,VJSSJ00 FROM BQ_YZZXJL --WHERE ZYID00=VZYID00 AND YZID00=VYZID00 AND ZT0000 IN ('0','1','2'); SELECT MAX(JSRQ00||JSSJ00) INTO VJSRQSJ FROM BQ_YZZXJL WHERE ZYID00=VZYID00 AND YZID00=VYZID00 AND ZT0000 IN ('0','1','2'); VJSRQ00:=substrb(VJSRQSJ,1,8); VJSSJ00:=substrb(VJSRQSJ,9,8); IF VJSRQ00 IS NULL THEN--第一次的提取 UPDATE BQ_YPYZ00 SET TQRQ00=NULL, TQSJ00=NULL, ZXRQ00=NULL, YZZT00=DECODE(YZZT00,'2','1','3',decode(V_YPTWHSFBGYZZT,'1','3','4'),YZZT00), TZRXM0=DECODE(YZZT00,'2',TZRXM0,'3',VCZYXM0,TZRXM0) WHERE YZID00=VYZID00; UPDATE BQ_YPYZMX X SET TQRQ00=NULL,TQSJ00=NULL WHERE YZID00=VYZID00; delete BQ_YZZXLS where TQDJH0=VTQDJH0 and YZID00=VYZID00; ELSE UPDATE BQ_YPYZ00 SET TQRQ00=TO_CHAR(TO_DATE(VJSRQ00,'YYYYMMDD')-1,'YYYYMMDD'), TQSJ00=VJSSJ00 WHERE YZID00=VYZID00; UPDATE BQ_YPYZMX X SET (TQRQ00,TQSJ00)= (SELECT SJJSRQ,SJJSSJ FROM BQ_YZZXJL J WHERE J.YZID00=VYZID00 AND J.YZMXID=X.YZMXID AND JSRQ00=VJSRQ00 AND JSSJ00=VJSSJ00 AND ZT0000<>'5') WHERE YZID00=VYZID00; delete BQ_YZZXLS where TQDJH0=VTQDJH0 and YZID00=VYZID00; END IF; VHGBZ00:='Y'; EXCEPTION WHEN OTHERS THEN COUNTER:=0; END; END IF; ELSE UPDATE BQ_YZZXJL SET ZT0000='2' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); END IF; DELETE FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0'); ELSE--不是最后一次提取,医嘱不回滚 UPDATE BQ_YZZXJL SET ZT0000='2' WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND ZT0000 IN ('0','1'); UPDATE YF_YZYPSQ SET QLZT00='3' WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('0'); END IF; --多批次 Lindj 2004.04.26 DECODE(QLZT00,'1','1','0') --插入药品更正信息 INSERT INTO YF_YZYPSQ ( YPQLPC ,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000, BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,DJH000 , ZYTS00 ,YSGZH0 ,QLZT00 ,FJFDJH ,YPDLBH ,YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00 ,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,YPZSL0 ,YPYFMC ,PCMC00 , YFSMMC ,ZJYFMC ,XMYFMC ,CXBZ00 ,CXPCID ,SFXS00 , CZY000 ,SRRQ00 ,SRSJ00 ,JZRQ00 ,JZSJ00 ,TQDJH0 , SSKS00, DQKS00 ,BZ0000 ,ZFBL00 ,GFJE00 ,ZFJE00 , QZFJE0 ,YEXM00,YSZID0,ZFFSBZ,CZLSH0,DKSL00,SFMFBZ) SELECT SQ_YF_YZYPSQ_YPQLPC.NEXTVAL,YFBMBH ,BQBMBH ,ZYID00 ,CH0000 ,ZYH000 , BRXM00 ,YFLBID ,HZBZ00 ,YZID00 ,YZMXID ,DECODE(QLZT00,'1',VDJH000,NULL),--VDJH000, --modify by qks 2009.02.03 ZYTS00 ,YSGZH0 ,DECODE(QLZT00,'1','0','0'),NULL ,YPDLBH , YPNM00 , YPMC00 ,YPGG00 ,QLDW00 ,ZHL000 ,GJDJ00 ,LSDJ00 , PFDJ00 ,CSL000 ,JLDW00 ,-1*YPZSL0 ,YPYFMC ,PCMC00 , YFSMMC ,ZJYFMC ,XMYFMC ,'-' , YPQLPC ,SFXS00 , PCZY000 ,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL),DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL),TQDJH0, SSKS00,DQKS00,DECODE(VHGBZ00,'Y','HG',CKDH00),ZFBL00,-1*GFJE00,-1*ZFJE00, -1*QZFJE0,YEXM00,YSZID0,ZFFSBZ,PCZLSH0,decode(QLZT00,'1',-DKSL00,0),SFMFBZ FROM YF_YZYPSQ WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('1','2','4'); --多批次 Lindj 2004.04.26 UPDATE YF_YZYPSQ SET QLZT00='6' WHERE ZYID00=VZYID00 AND DJH000=VDJH000 AND YZID00=VYZID00 AND CXBZ00='-' AND FYBZ00='0'AND QLZT00='0'; --已记账->已退费 UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00='6',ZCXSL0=YPZSL0,BCCXSL=0 WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN ('0','1'); --已发药/未审核-> UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00=DECODE(QLZT00,'1','2',QLZT00),BCCXSL=YPZSL0 WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('2','4'); --UPDATE YF_YZYPSQ SET QLZT00='2' --WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='-' AND FYBZ00='0'AND QLZT00='1'; --UPDATE YF_YZYPSQ SET CXBZ00='+',QLZT00=DECODE(QLZT00,'1','2',QLZT00) --WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='Z' AND FYBZ00='0'AND QLZT00 IN('1','2','4'); <> COUNTER:=COUNTER; END LOOP; END IF; --***************************** 退部分药品 ********************************* <> IF PSFBFTY='1' THEN BEGIN SELECT QLZT00 ,YZMXID ,YZID00 ,TQDJH0 ,ZYID00 ,DJH000,JZRQ00 INTO VQLZT00,VYZMXID,VYZID00,VTQDJH0,VZYID00,VJDJH00,V_JZRQ00 FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC AND CXBZ00 In ('Z','+') AND QLZT00 IN ('0','1','2','4') AND FYBZ00='0'; Select count(*) into counter from zy_brxxb0 where zyid00=VZYID00 and Substr(brzt00,1,1) in ('1','2','3'); if counter=0 then Verrmsg:='非在院病人，不能提出退药申请！'; raise Ecustom; end if; EXCEPTION--错误 WHEN OTHERS THEN BEGIN SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC; IF COUNTER=0 THEN RAISE_APPLICATION_ERROR(-20001,'没有该药品的领药记录！*'); END IF; SELECT QLZT00,CXBZ00,FYBZ00 INTO VQLZT00,VCXBZ00,VFYBZ00 FROM YF_YZYPSQ WHERE YPQLPC=PYPQLPC; IF VCXBZ00 Not In ('Z','+') THEN RAISE_APPLICATION_ERROR(-20002,'该药品已退！*'); ELSIF VFYBZ00='1' THEN RAISE_APPLICATION_ERROR(-20003,'药房正在发药，病区无法退药！*'); ELSIF VQLZT00='3' THEN RAISE_APPLICATION_ERROR(-20005,'该药品已作废！*'); ELSE RAISE_APPLICATION_ERROR(-20004,'无法退药！*'); END IF; END; END; select count(*) into counter from BQ_YPYZ00 where YZID00 in( select YZID00 from YF_YZYPSQ where TQDJH0=PTQDJH0 And BCCXSL>0 ) and YZZT00 in('3','2') and nvl(YCXBZ0,'0')='0' and CLBZ00='1'; if V_YCXYZCKTF='Y' and counter>0 then Verrmsg:='医生预撤销或已撤销医嘱，才能做退费申请！'; Raise Ecustom; end if; -- select count(*) into counter from YF_YZYPSQ A where ZYID00=VZYID00 and A.YPQLPC In (Select B.YPQLPC From YF_YZYPSQ B -- Where B.TQDJH0=PTQDJH0 And ZYID00=Vzyid00 and QLZT00='0' And CXBZ00='-' ); -- if counter>0 then -- Verrmsg:='此药品存在未审核的退药申请，请先撤销退药申请，或者让药房做退药审核后再做退药申请'; -- Raise Ecustom; -- end if; --取病人信息 2012.01.16 SELECT BRID00,XM0000,ZYH000 INTO VBRID00,VBRXM00,VZYH000 FROM ZY_BRXXB0 WHERE ZYID00=VZYID00; --取病人费别 SELECT FBBH00,YBKH00,YBID00 INTO VFBBH00, V_YBKH00, V_YBID00 FROM BM_BRXXB0 WHERE BRID00=VBRID00; --ZY_YBCXJZRQ值根据不同的YBZXLB获取不同的 YBCXJZRQ begin select SF_ZY_GETXTCS(c.YBZXLB,V_YBCXJZRQ) into V_YBCXJZRQ from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c where a.ZYID00=VZYID00 and a.BRID00=b.BRID00 and b.FBBH00=3 and b.FBBH00=c.FBBH00 and b.YBLB00=c.YBLB00; exception when others then V_YBCXJZRQ:='20010101'; end; --除了新农合病人和异地医保病人，其他医保病人都要限制不能冲销某个时间段之前的费用 if (VFBBH00=3) and (V_YBCXJZRQ<>'20010101') and (V_JZRQ00<>'20991231') then if (V_YBCXJZRQ >= V_JZRQ00) and (V_YBKH00 <> V_YBID00) and (instr(V_YBKH00,'@') <= 0) then Verrmsg:='不能冲销'||substr(V_YBCXJZRQ,1,4)||'年'||substr(V_YBCXJZRQ,5,2)||'月'||substr(V_YBCXJZRQ,7,2)||'日之前的费用'; raise Ecustom; end if; end if; --已结帐药品不允许退药时处理 added by jlg for BQ-20061205-001 if VJZTYBZ='N' then select count(*) into counter from YF_YZYPSQ a,zy_brfy00 b where a.djh000=b.djh000 and b.jzdh00<>0 and YPQLPC=PYPQLPC; if counter>0 then Verrmsg:='该药品收费处已结帐,病区不能退药！*'; Raise Ecustom; end if; end if; IF (V_XMCXSQ='Y') and (nvl(VJDJH00,0)<>0) then select b.jzdh00 into VJZDH00 from ZY_BRFY00 b where b.djh000=VJDJH00; if VJZDH00<>0 then select count(1) into counter from ZY_JZB000 where jzdh00=VJZDH00 and fphsbz='0'; if counter>0 then Verrmsg:='病区不能退药,该药品收费处已结算,而且发票未回收,请先把发票交还到收费处！*'; Raise Ecustom; end if; end if; end if; --判断是否退过同组药品 --SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ -- WHERE ZYID00=VZYID00 AND TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND CXBZ00='+' AND ROWNUM=1 AND -- SRRQ00||SRSJ00=(SELECT SRRQ00||SRSJ00 FROM YF_YZYPSQ -- WHERE TQDJH0=VTQDJH0 AND YZID00=VYZID00 AND FYBZ00='0'AND CXBZ00 in ('Z','+') AND ROWNUM=1); --IF COUNTER>0 THEN -- Verrmsg:='同组药品已经退药的，不能部分退药！'; -- RAISE Ecustom; --END IF; --原先纪录冲销标志 改为'+' Update YF_YZYPSQ Set CXBZ00='+' Where TQDJH0=PTQDJH0 And BCCXSL>0; --删除原先的部分退药纪录，同一个领药单原先做全部退药药房没有确认的转为部分退药。 Delete From YF_YZYPSQ A Where A.YPQLPC In (Select B.YPQLPC From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 And ZYID00=Vzyid00 and QLZT00='0' And CXBZ00='-' ) AND A.CXPCID in (Select B.YPQLPC From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 And ZYID00=Vzyid00 AND BCCXSL>0); --原来有退药的，直接修改纪录中的药品总数量，操作员，输入日期/时间，GFJE00,ZFJE00,QZFJE0 --Update YF_YZYPSQ A Set( -- A.YPZSL0, -- A.CZY000, -- A.SRRQ00, -- A.SRSJ00, -- A.GFJE00, -- A.ZFJE00, -- A.QZFJE0 -- )= -- (Select -- -1*B.BCCXSL, -- PCZY000, -- TO_CHAR(VSYSDATE,'YYYYMMDD'), -- TO_CHAR(VSYSDATE,'HH24:MI:SS') , -- -1*B.GFJE00*B.BCCXSL/B.YPZSL0, -- -1*B.ZFJE00*B.BCCXSL/B.YPZSL0, -- -1*B.QZFJE0*B.BCCXSL/B.YPZSL0 -- From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 -- ) --Where A.YPQLPC In (Select YPQLPC From YF_YZYPSQ C Where C.TQDJH0=PTQDJH0 And C.CXBZ00='-'); --原来没有退药的 ，插入一条负纪录 INSERT INTO YF_YZYPSQ ( YPQLPC , YFBMBH , BQBMBH , ZYID00 , CH0000 , ZYH000, BRXM00 , YFLBID , HZBZ00 , YZID00 , YZMXID , DJH000 , ZYTS00 , YSGZH0 , QLZT00 , FJFDJH , YPDLBH , YPNM00 , YPMC00 , YPGG00 , QLDW00 , ZHL000 , GJDJ00 , LSDJ00 , PFDJ00 , CSL000 , JLDW00 , YPZSL0 , YPYFMC , PCMC00 , YFSMMC , ZJYFMC , XMYFMC , CXBZ00 , CXPCID , SFXS00 , CZY000 , SRRQ00 , SRSJ00 , --JZRQ00 , --JZSJ00 , TQDJH0 , SSKS00, DQKS00 , BZ0000 , ZFBL00 , GFJE00 , ZFJE00 , QZFJE0 , YEXM00, YSZID0, ZFFSBZ, CZLSH0, SFMFBZ ) SELECT SQ_YF_YZYPSQ_YPQLPC.NEXTVAL, YFBMBH , BQBMBH , ZYID00 , CH0000 , ZYH000 , BRXM00 , YFLBID , HZBZ00 , YZID00 , YZMXID , VDJH000, ZYTS00 , YSGZH0 , '0', NULL , YPDLBH , YPNM00 , YPMC00 , YPGG00 , QLDW00 , ZHL000 , GJDJ00 , LSDJ00 , PFDJ00 , CSL000 , JLDW00 , -1*BCCXSL , YPYFMC , PCMC00 , YFSMMC , ZJYFMC , XMYFMC , '-' , YPQLPC , SFXS00 , PCZY000 , TO_CHAR(VSYSDATE,'YYYYMMDD'), TO_CHAR(VSYSDATE,'HH24:MI:SS'), --DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'YYYYMMDD'),NULL), --DECODE(QLZT00,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS'),NULL), TQDJH0, SSKS00, DQKS00, DECODE(VHGBZ00,'Y','HG',CKDH00), ZFBL00, -1*GFJE00*BCCXSL/YPZSL0, -1*ZFJE00*BCCXSL/YPZSL0, -1*QZFJE0*BCCXSL/YPZSL0, YEXM00, YSZID0, ZFFSBZ, PCZLSH0, SFMFBZ FROM YF_YZYPSQ WHERE TQDJH0=PTQDJH0 and ZYID00=VZYID00 And BCCXSL>0 And YPQLPC Not In (Select YPQLPC From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 And B.CXBZ00='-' and ZYID00=VZYID00 ); --判断是否存在重复退药 select count(*) into counter1 from ( select YZID00,YZMXID,YPNM00,sum(YPZSL0) YPZSL0 from YF_YZYPSQ where ZYID00=VZYID00 and YZID00 in(select YZID00 from YF_YZYPSQ where TQDJH0=PTQDJH0 and ZYID00=VZYID00 and BCCXSL>0 and CXBZ00 in('Z','+')) group by YZID00,YZMXID,YPNM00 ) where YPZSL0<0; if counter1>0 then Verrmsg:='病人存在重复退药，退药失败'; RAISE Ecustom; end if; --过程里面把部分退药的本次冲销数量改为0 update YF_YZYPSQ A set BCCXSL=0 where A.YPQLPC In (Select B.CXPCID From YF_YZYPSQ B Where B.TQDJH0=PTQDJH0 And ZYID00=Vzyid00 and QLZT00='0' And CXBZ00='-' ); END IF; COMMIT; --已领药品退费申请时,如果是未结算过的，则直接调用过程SP_YF_BQTYSQQR进行退药申请确认 IF V_SFTY00='Y' then --##2015.12.17 modify by daihq for BQ-20151112-001 解决同组药不同药房退药，只确认一条单据的问题 --begin --SELECT YFBMBH,BQBMBH,ZYH000 INTO V_YFBMBH,V_BQBMBH,VZYH000 FROM YF_YZYPSQ L -- WHERE ZYID00=VZYID00 AND QLZT00='0' AND CXBZ00='-' AND ROWNUM=1 -- AND NOT EXISTS (select 1 from YF_YZYPSQ A,ZY_BRFY00 B where A.YPQLPC=L.CXPCID AND A.DJH000=B.DJH000 AND B.JZDH00<>0); --EXCEPTION -- WHEN OTHERS THEN -- V_YFBMBH:=0; --END; --IF V_YFBMBH<>0 then -- SP_YF_BQTYSQQR(TRIM(VZYH000),0,0,0,V_YFBMBH,V_BQBMBH,V_OUT_CKDH); --end if; for v_QRYZ in (select distinct YFBMBH,BQBMBH,ZYH000 from YF_YZYPSQ L where ZYID00=VZYID00 and QLZT00='0' and CXBZ00='-' and not exists (select 1 from YF_YZYPSQ A,ZY_BRFY00 B where A.YPQLPC=L.CXPCID and A.DJH000=B.DJH000 and B.JZDH00<>0)) loop if nvl(v_QRYZ.YFBMBH,0)<>0 then SP_YF_BQTYSQQR(TRIM(v_QRYZ.ZYH000),0,0,0,v_QRYZ.YFBMBH,v_QRYZ.BQBMBH,V_OUT_CKDH); end if; end loop; end if; IF Verrmsg<>'OK' then Verrmsg:='没有全部成功，'||Verrmsg; Raise Ecustom; end if; if BQ_SFQYYTY='3' then for c_dkyp in( select BQBMBH,DKSL00,YFBMBH,YPNM00,ZHL000 from YF_YZYPSQ where ZYID00=PZYID00 and SRRQ00=to_char(sysdate,'YYYYMMDD') and CZLSH0=PCZLSH0 and nvl(DKSL00,0)<0) loop update YF_YTYKC0 set YKKCSL=YKKCSL-c_dkyp.DKSL00*c_dkyp.ZHL000 where YFBMBH=c_dkyp.YFBMBH and BQBMBH=c_dkyp.BQBMBH and YPNM00=c_dkyp.YPNM00; end loop; end if; EXCEPTION WHEN Ecustom THEN RAISE_APPLICATION_ERROR(-20016,Verrmsg||'！*'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220)); ROLLBACK; END SP_BQ_YZYPTY;