CREATE OR REPLACE PROCEDURE SP_YJ_YWZX00( PYJDJH0 IN NUMBER ,--单据号 PZLXMID IN NUMBER ,--子项目ID PZXSL00 IN NUMBER ,--执行数量 PZXMBZ IN NUMBER ,--子项目标志,1:执行子项目,2:大项目 PXKH000 IN BM_YGBM00.XKH000%TYPE ,--执行人胸卡号 PZXKS00 IN NUMBER , --执行科室 PCOMMIT IN varchar2 default 'Y' , --提交标志 PSFXKCS IN NUMBER DEFAULT 0 --标识是否输血系统执行过程 ) AS VID0000 NUMBER ; --ID号 VYJDJH0 NUMBER ; --医技单据流水号 VYJFZH0 NUMBER ; --分组号(一个标本多个检查项目用，不同的单据号，相同的分组号) VSFXMID NUMBER ; --收费项目ID VSFXX00 NUMBER ; --收费金额下限 VJJJE00 NUMBER ; --业务科室自定收费金额 VSFSX00 NUMBER ; --收费金额上限 LS_YBBZ00 CHAR ; --医保标志 'Y'医保,'N'非医保,显示用 VDJH000 NUMBER ; --医技收费单据号 Vcounter NUMBER ; --临时变量 VTEMP NUMBER ; --临时变量 VZLXMID NUMBER ; --诊疗项目ID VSL0000 NUMBER ; --数量 VZXSL00 NUMBER ; --执行数量 VCXSL00 NUMBER ; --冲消数量 Verrmsg CHAR(255) ; --错误描述 VZXLMID NUMBER ; --诊疗项目ID VBRID00 NUMBER ; --病人ID VFBBH00 NUMBER ; --费别号码 VGFBL00 NUMBER ; --公费报销比例 0-100 LS_YBLB00 CHAR ; --病人医保类别,'0':非医保'1':省直医保,'2':市医保(对应病人所属分中心名称) LS_XMZT00 CHAR ; --项目状态 LS_FBZXBZ CHAR ; --分步执行标志 VZXR000 NUMBER ; --执行人编号 LS_YZCFID CHAR(16) ; --相关处方号 LS_PSFJGSM CHAR(255) ; --收费结果说明 VPSFSFCG NUMBER ; --收费是否成功,1:成功,0:失败 Vparams VARCHAR2(200) ; LS_MZZYBZ CHAR ; --门诊住院标志 Vpsxzbz char(1) ; --皮试处方是否限制 LS_ZXRXM0 YJ_YWMX00.ZXRXM0%TYPE; --执行人姓名 LS_ZLXMJC YJ_YW0000.ZLXMJC%TYPE; --诊疗项目简称 LS_DCBZ00 YJ_YW0000.DCBZ00%TYPE; --带出标志 LS_ZLDCCF YJ_YW0000.ZLDCCF%TYPE; --诊疗带出处方 Vzje000 YJ_YW0000.ZJE000%TYPE; --总金额 L_YJYW_ROW YJ_YW0000%ROWTYPE ; --返回医技业务表中的行 R_YF_MZCF00 YF_MZCF00%ROWTYPE ; --门诊处方行变量 R_YF_ZYCF00 YF_ZYCF00%ROWTYPE ; --住院处方行变量 LS_CFZT00 YF_MZCF00.CFZT00%TYPE; --处方状态 LS_SFKSLY YF_MZCF00.CFZT00%TYPE; --是否科室领药 E_NOTONE EXCEPTION ; --记录数不唯一 E_NOT_JJ EXCEPTION ; --没定价 E_CANNOT_ZX EXCEPTION ; --不能执行 Ecustom EXCEPTION ; --自定义错误 Vcount NUmber(2); VJLRQ00 char(8); VJLSJ00 char(8); Vboolean char ;--是否允许金额为0的项目执行 VYJKSBH YJ_YW0000.YJKSBH%TYPE; VSFYXZX XT_XTCS00.VALUE0%TYPE; E_YEWFS EXCEPTION; --余额为负数 CURSOR YWMX00 IS select ZLXMID from YJ_YWMX00 where YJDJH0=PYJDJH0; -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2008.11.30 已经收费的项目，在另一个科室执行时，要把项目拆分成一部分给另外一个科室。 -- yangy 2009.04.29 限制医保病人先到收费处记帐，在这个过程中限制。 -- yangy 2009.08.02 住院的不用限制到收费处结算。 -- yangh 2009.08.19 住院处方未发药在参数YJ_WFYCFXMSFYXZX控制下允许执行。 -- zhr 2010.08.03 执行科室合法性判断，非本科室的不能执行 -- liuj 2010.08.31 增加默认参数PSFXKCS判断是否血库系统项目执行，医保病人绕过提示到收费处结算 -- lgh 2010.10.27 执行科室合法性判断，非本科室的不能执行(做了修正，允许抢执行的医院不做此判断) -- chenyw 2011.06.03 检验同时执行多条同YJDJH0、同项目会报错 -- jinfl 2011.11.25 病人账户余额为负数，不允许执行 BEGIN Vparams:='参数:PYJDJH0='||to_char(PYJDJH0)||',PZLXMID='||to_char(PZLXMID)||',PZXSL00='||to_char(PZXSL00)||',PZXMBZ='||to_char(PZXMBZ)||',PXKH000='||PXKH000||',PZXKS00='||to_char(PZXKS00); begin select trim(value0) into VSFYXZX from xt_xtcs00 where name00='YJ_WFYCFXMSFYXZX'; exception when others then VSFYXZX := 'N'; end; --单据号是否存在 select count(*) INTO Vcounter from YJ_YW0000 where YJDJH0=PYJDJH0; IF Vcounter<1 THEN RAISE NO_DATA_FOUND; END IF; select count(*) INTO Vcounter from YJ_YW0000 where CXDJH0=PYJDJH0; --IF Vcounter>0 THEN -- Verrmsg:='该项目已经被冲销'; -- RAISE Ecustom; --END IF; --修正冲消数量 UPDATE YJ_YW0000 a SET CXSL00=(select -1*nvl(sum(SL0000),0) from YJ_YW0000 where BRID00=a.BRID00 and CXDJH0=a.YJDJH0 and XMZT00<>'6') where YJDJH0=PYJDJH0; --诊疗项目ID,分组号,病人ID,项目状态,相关处方号,分步执行标志,门诊住院标志 select ZLXMID,YJFZH0,BRID00,SL0000,XMZT00,YZCFID,FBZXBZ,MZZYBZ,DCBZ00,ZLXMJC,SFDJH0,ZJE000,ZLDCCF,ZXSL00,CXSL00,YJKSBH INTO VZLXMID,VYJFZH0,VBRID00,VSL0000,LS_XMZT00,LS_YZCFID,LS_FBZXBZ,LS_MZZYBZ, LS_DCBZ00,LS_ZLXMJC,VDJH000,Vzje000,LS_ZLDCCF,VZXSL00,VCXSL00,VYJKSBH from YJ_YW0000 where YJDJH0=PYJDJH0; if Vzje000=0 then select count(1) into Vcounter from vw_BM_ZLZD00 a where exists (select 1 from xt_xtcs00 where Instr(','||trim(Value0)||',',','||trim(a.zlxmid)||',')>0 AND trim(NAME00)='YJ_ZLLSFXMZX'); if Vcounter=0 then select 1 into Vboolean from xt_xtcs00 where name00='YJ_LSFXMZX'; if Vboolean='1' then select count(1) into Vcounter from vw_BM_ZLZD00 a,vw_BM_YYSFXM b,XT_ZLSFGX c where c.ZLXMID = a.ZLXMID and b.SFXMID = c.SFXMID and a.zlxmid=VZLXMID and ((b.SFXX00=0 and b.SFSX00=0) or exists (select 1 from xt_xtcs00 where Instr(','||trim(Value0)||',',','||trim(b.bh0000)||',')>0 AND trim(NAME00)='YJ_LSFXMZX')); else select count(1) into Vcounter from vw_BM_ZLZD00 a,vw_BM_YYSFXM b,XT_ZLSFGX c where c.ZLXMID = a.ZLXMID and b.SFXMID = c.SFXMID and a.zlxmid=VZLXMID and b.SFXX00=0 and b.SFSX00=0; end if; if Vcounter=0 then Verrmsg:='该诊疗单的收费金额为0,不能执行,请先估价。'; RAISE Ecustom; end if; end if; end if; --参数设定有的病人必须结算后才能执行 if ls_mzzybz='0' then select count(*) INTO Vcounter from BM_BRXXB0 a,XT_XTCS00 b where BRID00=VBRID00 AND NAME00='SF_QZJSFB' and Instrb(','||trim(Value0)||',',','||to_char(FBBH00)||',')>0 and (Vdjh000=0 or exists (select 1 from sf_brfy00 where djh000=Vdjh000 and jzdh00=0)); IF Vcounter>0 and PSFXKCS=0 THEN Verrmsg:='该类费别的病人必须到收费处先记账并结算，才能执行诊疗单'; RAISE Ecustom; END IF; end if; --限制余额为负数 不允许执行 select count(*) into Vcounter from XT_XTCS00 where NAME00='YF_ZHWFSBYXFYZX' and TRIM(VALUE0)='Y'; if (Vcounter>0) and (LS_MZZYBZ='0') then select count(*) into Vcounter from SF_BRZHXX where BRID00=VBRID00 and JFLBID in (0,1) and ZYE000<0; if Vcounter>0 then raise E_YEWFS; end if; end if; --冲消数量判定 if Vsl0000-nvl(Vcxsl00,0)pZXKS00 then--2010.10.27 lgh 修改 --允许抢执行 select count(*) into Vcounter from XT_XTCS00 where name00='YJ_RHXMLYKSZX' and Value0='Y'; if Vcounter=0 then Verrmsg:='非本科室的项目，不能执行!'; RAISE Ecustom; end if; end if; --执行人 select YGBH00,ZWXM00 INTO VZXR000,LS_ZXRXM0 from BM_YGBM00 where UPPER(XKH000)=UPPER(PXKH000) AND ROWNUM=1; --相关的处方判定和处理 IF ls_YZCFID > '0' AND LS_DCBZ00 IN ('1','2') THEN IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from YF_MZCFMX where CFLSH0=LS_YZCFID AND THBZ00='0'; ELSE select count(*) INTO Vcounter from YF_ZYCFMX where CFLSH0=LS_YZCFID AND THBZ00='0'; END IF; IF Vcounter=0 THEN Verrmsg:='处方已经被药房退回，项目不能执行'; RAISE Ecustom; END IF; IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from (select count(*) from YF_MZCFMX a,YF_MZCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_YZCFID or b.BCCFH0=LS_YZCFID) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); ELSE select count(*) INTO Vcounter from (select count(*) from YF_ZYCFMX a,YF_ZYCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_YZCFID or b.BCCFH0=LS_YZCFID) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); END IF; IF Vcounter=0 THEN Verrmsg:='处方已经被退药，项目不能执行'; RAISE Ecustom; END IF; --发药判定 --相关处方的所有药品都自备,则不判定处方是否已发药，可直接执行项目；否则 --如果相关处方的药品医技科室都有备药，则进行处方收费并继续执行项目, --如果科室都不备药,那么判定处方是否发药，如果已发，项目可执行，否则停止执行项目 IF LS_MZZYBZ='0' THEN select count(*) INTO VTEMP from YF_MZCFMX where CFLSH0=LS_YZCFID AND NVL(SFZBY0,'N')='N'; ELSE select count(*) INTO VTEMP from YF_ZYCFMX where CFLSH0=LS_YZCFID AND NVL(SFZBY0,'N')='N'; END IF; IF VTEMP>0 THEN --需要领药的处方 IF LS_MZZYBZ='0' THEN select * INTO R_YF_MZCF00 from YF_MZCF00 where CFLSH0=LS_YZCFID; IF R_YF_MZCF00.CFZT00='3' THEN--处方被作废 Verrmsg:='处方已被作废，项目不能执行'; RAISE Ecustom; ELSIF R_YF_MZCF00.CFZT00 IN ('0','1','4') THEN--处方未发药 IF LS_DCBZ00<>'2' THEN--非皮试项目 Verrmsg:='处方未发药,项目不能执行'; RAISE Ecustom; END IF; --处理皮试 select count(*) INTO VTEMP from YF_MZCFMX C where CFLSH0=LS_YZCFID AND SFZBY0<>'Y' AND NOT EXISTS ( select 1 from YJ_KSBYYP B where C.YPNM00=B.YPNM00 AND BMBH00=PZXKS00); IF VTEMP=0 THEN--判定是否科室备药 --所有的药品，医技科室都有备药，(收取处方标志，并把处方改为科室领药)------->项目冲销时，药费要退回 --收费 UPDATE YF_MZCF00 SET SFKSLY='Y',LYKS00=PZXKS00 where CFLSH0=LS_YZCFID; IF R_YF_MZCF00.CFZT00 IN ('0','4') THEN SP_YJ_CFJZ00('0',LS_YZCFID,VZXR000,PZXKS00,VZXR000); END IF; --ELSE--不能科室备药 -- Verrmsg:='处方未发药,项目不能执行'; -- RAISE Ecustom; END IF; END IF; ELSE--住院病人需要领药的处方 select * INTO R_YF_ZYCF00 from YF_ZYCF00 where CFLSH0=LS_YZCFID; IF R_YF_ZYCF00.CFZT00='3' THEN--处方被作废 Verrmsg:='处方已被作废，项目不能执行'; RAISE Ecustom; ELSIF R_YF_ZYCF00.CFZT00 IN ('0','1','4') THEN--处方未发药 IF LS_DCBZ00<>'2' THEN--非皮试项目 IF (VSFYXZX = 'N') THEN Verrmsg:='处方未发药,项目不能执行'; RAISE Ecustom; ELSE IF (R_YF_ZYCF00.CFZT00='0') AND (VSFYXZX = 'Y') THEN Verrmsg:='处方未记账,项目不能执行'; RAISE Ecustom; END IF; END IF; ELSE select count(*) INTO VTEMP from YF_ZYCFMX C where CFLSH0=LS_YZCFID AND SFZBY0<>'Y' AND NOT EXISTS ( select 1 from YJ_KSBYYP B where C.YPNM00=B.YPNM00 AND BMBH00=PZXKS00); IF VTEMP=0 THEN--判定是否科室备药 --所有的药品，医技科室都有备药，(收取处方标志，并把处方改为科室领药)------->项目冲销时，药费要退回 UPDATE YF_ZYCF00 SET SFKSLY='Y',LYKS00=PZXKS00 where CFLSH0=LS_YZCFID; --收费 IF R_YF_MZCF00.CFZT00 IN ('0','4') THEN SP_YJ_CFJZ00('1',LS_YZCFID,VZXR000,PZXKS00,VZXR000); END IF; END IF; END IF; END IF; END IF; END IF; ELSIF ls_YZCFID > '0' AND LS_DCBZ00='0' and LS_MZZYBZ='0' THEN--诊疗带出的处方判定和处理 IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from YF_MZCFMX where CFLSH0=LS_YZCFID AND THBZ00='0'; ELSE select count(*) INTO Vcounter from YF_ZYCFMX where CFLSH0=LS_YZCFID AND THBZ00='0'; END IF; IF Vcounter=0 THEN Verrmsg:='处方已经被药房退回，项目不能执行'; RAISE Ecustom; END IF; IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from (select count(*) from YF_MZCFMX a,YF_MZCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_YZCFID or b.BCCFH0=LS_YZCFID) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); ELSE select count(*) INTO Vcounter from (select count(*) from YF_ZYCFMX a,YF_ZYCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_YZCFID or b.BCCFH0=LS_YZCFID) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); END IF; IF Vcounter=0 THEN --部分冲消,不能再这样判定 2006.04.26 zhr Verrmsg:='处方已经被退药，项目不能执行'; RAISE Ecustom; END IF; --发药判定 --相关处方的所有药品都自备,则不判定处方是否已发药，可直接执行项目；否则 --如果相关处方的药品医技科室都有备药，则进行处方收费并继续执行项目,如果科室都不备药 --那么判定处方是否发药，如果已发，项目可执行，否则停止执行项目 IF LS_MZZYBZ='0' THEN select count(*) INTO VTEMP from YF_MZCFMX where CFLSH0=LS_YZCFID AND NVL(SFZBY0,'N')='N'; ELSE select count(*) INTO VTEMP from YF_ZYCFMX where CFLSH0=LS_YZCFID AND NVL(SFZBY0,'N')='N'; END IF; IF VTEMP>0 THEN --需要领药的处方 IF LS_MZZYBZ='0' THEN select * INTO R_YF_MZCF00 from YF_MZCF00 where CFLSH0=LS_YZCFID; IF R_YF_MZCF00.CFZT00='3' THEN--处方被作废 Verrmsg:='处方已被作废，项目不能执行'; RAISE Ecustom; ELSIF R_YF_MZCF00.CFZT00 IN ('0','1','4') AND R_YF_MZCF00.SFKSLY='N' THEN--非基数药，处方必须发药 Verrmsg:='处方未发药,项目不能执行'; RAISE Ecustom; ELSIF R_YF_MZCF00.CFZT00 IN ('0','4') AND R_YF_MZCF00.SFKSLY='Y' THEN--基数药，处方必须记账 --如果是预交金病人病人，自动记账，否则报告错误 select count(*) INTO VcountER from SF_BRZHXX where BRID00=VBRID00 AND SFDJ00='0' AND ZYE000>0; IF VcountER>0 THEN SP_YJ_CFJZ00('0',LS_YZCFID,VZXR000,PZXKS00,VZXR000); ELSE Verrmsg:='处方未收费,项目不能执行'; RAISE Ecustom; END IF; END IF; ELSE--住院病人需要领药的处方 select * INTO R_YF_ZYCF00 from YF_ZYCF00 where CFLSH0=LS_YZCFID; IF R_YF_ZYCF00.CFZT00='3' THEN--处方被作废 Verrmsg:='处方已被作废，项目不能执行'; RAISE Ecustom; ELSIF R_YF_ZYCF00.CFZT00 IN ('0','1','4') AND R_YF_ZYCF00.SFKSLY='N' THEN--非基数药，处方必须发药 Verrmsg:='处方未发药,项目不能执行'; RAISE Ecustom; ELSIF R_YF_ZYCF00.CFZT00 IN ('0','4') AND R_YF_ZYCF00.SFKSLY='Y' THEN--基数药，处方必须记账 --如果是预交金病人病人，自动记账，否侧报告错误 select count(*) INTO VcountER from ZY_BRZHXX where BRID00=VBRID00 AND SFDJ00='0' AND ZYE000>0; IF Vcounter>0 THEN SP_YJ_CFJZ00('0',LS_YZCFID,VZXR000,PZXKS00,VZXR000); ELSE Verrmsg:='处方未收费,项目不能执行'; RAISE Ecustom; END IF; END IF; END IF; END IF; ELSIF LS_ZLDCCF>'0' AND LS_DCBZ00='0' THEN--诊疗带出的处方判定和处理_ IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from YF_MZCFMX where CFLSH0=LS_ZLDCCF AND THBZ00='0'; ELSE select count(*) INTO Vcounter from YF_ZYCFMX where CFLSH0=LS_ZLDCCF AND THBZ00='0'; END IF; IF Vcounter=0 THEN Verrmsg:='处方已经被药房退回，项目不能执行'; RAISE Ecustom; END IF; IF LS_MZZYBZ='0' THEN--退回判定,相关处方的所有药品都退回,则项目不能执行 select count(*) INTO Vcounter from (select count(*) from YF_MZCFMX a,YF_MZCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_ZLDCCF or b.BCCFH0=LS_ZLDCCF) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); ELSE select count(*) INTO Vcounter from (select count(*) from YF_ZYCFMX a,YF_ZYCF00 b where b.BRID00=Vbrid00 and a.CFLSH0=b.CFLSH0 and (b.CFLSH0=LS_ZLDCCF or b.BCCFH0=LS_ZLDCCF) and b.CFZT00<>'3' group by a.YPNM00 having sum(a.YPZSL0)>0); END IF; IF Vcounter=0 THEN Verrmsg:='处方已经被退药，项目不能执行'; RAISE Ecustom; END IF; --记帐判定 --相关处方是科室领药的,尝试记帐，成功后方可执行项目 --病人自领处方，发药后才能执行诊疗项目 IF LS_MZZYBZ='0' THEN select CFZT00,SFKSLY INTO LS_CFZT00,LS_SFKSLY from YF_MZCF00 where CFLSH0=LS_ZLDCCF; ELSE select CFZT00,SFKSLY INTO LS_CFZT00,LS_SFKSLY from YF_ZYCF00 where CFLSH0=LS_ZLDCCF; END IF; IF LS_SFKSLY='Y' THEN --处方科室领药 IF LS_CFZT00='3' THEN--处方被作废 Verrmsg:='处方已被作废，项目不能执行'; RAISE Ecustom; ELSIF LS_CFZT00='4' THEN--处方需要审核 Verrmsg:='处方需要审核,项目不能执行'; RAISE Ecustom; ELSIF LS_CFZT00='5' THEN--处方需要审核 Verrmsg:='处方已退药,项目不能执行'; RAISE Ecustom; ELSIF LS_CFZT00='6' THEN--处方需要审核 Verrmsg:='处方已退费,项目不能执行'; RAISE Ecustom; END IF; IF LS_CFZT00='0' THEN IF LS_MZZYBZ='0' THEN --0未记账,1已记账,2已发药,3作废'4'需要审核'5'退药 '6'退费 SP_YJ_CFJZ00('0',LS_ZLDCCF,VZXR000,PZXKS00,VZXR000); ELSE--住院病人需要领药的处方 SP_YJ_CFJZ00('1',LS_ZLDCCF,VZXR000,PZXKS00,VZXR000); END IF; END IF; ELSE --病人自领药处方 IF LS_CFZT00 <>'2' THEN Verrmsg:='诊疗项目自动带出的处方(处方号:'||LS_ZLDCCF||')未领药,项目不能执行'; RAISE Ecustom; END IF; END IF; END IF; --是否收费 IF LS_XMZT00='0' THEN--0申请 --Verrmsg:='该项目还没有定价,请先定价'; --RAISE Ecustom; SP_YJ_YWJJ00(PYJDJH0,0); IF VBRID00=0 THEN--非门诊住院病人，一定要到收费处交费 Verrmsg:='没有收费，请先到收费处收费'; RAISE Ecustom; END IF; sp_yj_ywsf00(PYJDJH0,PZXKS00,VZXR000,LS_PSFJGSM,VPSFSFCG); IF VPSFSFCG<>1 THEN Verrmsg:=LS_PSFJGSM; RAISE Ecustom; END IF; ELSIF LS_XMZT00='1' THEN --1定价 --先收费 IF VBRID00>0 THEN if LS_MZZYBZ = '0' then --是否医保病人一定要先到收费处记帐并结算，不允许在其他科室直接记帐 select count(*) INTO Vcounter from BM_BRXXB0 where BRID00=VBRID00 AND YBID00<>'0' and exists (select 1 from XT_XTCS00 where NAME00='XT_YBYXJZ' and trim(Value0)='N') ; IF Vcounter>0 and PSFXKCS=0 THEN Verrmsg:='门诊医保病人请到收费处结算'; RAISE Ecustom; RETURN; END IF; --发药或诊疗前必须先到收费处记帐强制结算的费别 select count(*) INTO Vcounter from BM_BRXXB0 a,XT_XTCS00 b where BRID00=Vbrid00 AND NAME00='SF_QZJSFB' and Instrb(','||trim(Value0)||',',','||to_char(FBBH00)||',')>0; IF Vcounter>0 AND PSFXKCS=0 THEN Verrmsg:='这类门诊医保病人请到收费处结算'; RAISE Ecustom; RETURN; END IF; end if; ELSE Verrmsg:='没有收费，请先到收费处收费'; RAISE Ecustom; RETURN; END IF; sp_yj_ywsf00(PYJDJH0,PZXKS00,VZXR000,LS_PSFJGSM,VPSFSFCG); IF VPSFSFCG<>1 THEN Verrmsg:=LS_PSFJGSM; RAISE Ecustom; END IF; ELSIF LS_XMZT00='2' THEN if (LS_MZZYBZ='0') and (VYJKSBH<>PZXKS00) AND (LS_DCBZ00='1') THEN SP_YJ_XMZXCF(PYJDJH0,PZLXMID,PZXSL00,PXKH000,PZXKS00); RETURN; END IF; ELSIF LS_XMZT00<>'2' THEN --不能执行 Verrmsg:='该项目不能执行'; RAISE Ecustom; END IF; --‘2’已收费,可以执行 --执行人 --select YGBH00,ZWXM00 INTO VZXR000,LS_ZXRXM0 from BM_YGBM00 where UPPER(XKH000)=PXKH000 AND ROWNUM=1; --分步执行标志 IF LS_FBZXBZ='Y' AND PZXMBZ=1 THEN --执行子项目 --是否 已经执行 select count(*) INTO Vcounter from YJ_YWMX00 where YJDJH0=PYJDJH0 AND ZLXMID=PZLXMID AND ZXRQ00 IS NULL AND ZXSJ00 IS NULL ; IF Vcounter<1 THEN Verrmsg:='该项目已经执行'; RAISE Ecustom; END IF; --修改YJ_YWMX00的执行人和执行时间 UPDATE YJ_YWMX00 SET ZXRXM0=LS_ZXRXM0,ZXRQ00=TO_CHAR(SYSDATE,'YYYYMMDD'),ZXSJ00=TO_CHAR(SYSDATE,'HH24:MI:SS') where YJDJH0=PYJDJH0 AND ZLXMID=PZLXMID; select count(*) INTO Vcounter from YJ_YWMX00 where YJDJH0=PYJDJH0 AND --ZXRXM0 IS NOT NULL AND ZXRQ00 IS NULL AND ZXSJ00 IS NULL; IF Vcounter<1 THEN --所有子项目都已经执行,修改YJ_YW0000的执行人,执行日期时间,状态 UPDATE YJ_YW0000 SET ZXR000=VZXR000,YJKSBH=PZXKS00,ZXRQ00=TO_CHAR(SYSDATE,'YYYYMMDD'), ZXSJ00=TO_CHAR(SYSDATE,'HH24:MI:SS'),XMZT00='3' where YJDJH0=PYJDJH0 ; END IF; ELSE --执行大项目 --修改YJ_YW0000的执行人,执行日期时间,状态 UPDATE YJ_YW0000 SET ZXR000=VZXR000,YJKSBH=PZXKS00,ZXRXM0=LS_ZXRXM0,ZXRQ00=TO_CHAR(SYSDATE,'YYYYMMDD'), ZXSJ00=TO_CHAR(SYSDATE,'HH24:MI:SS'), ZXSL00=NVL(ZXSL00,0)+PZXSL00 where YJDJH0=PYJDJH0; --IF PZXSL00 < VSL0000 THEN VJLRQ00:=TO_CHAR(SYSDATE,'YYYYMMDD'); VJLSJ00:=TO_CHAR(SYSDATE,'HH24:MI:SS'); begin select count(1) into Vcount from YJ_YWZXJL where yjdjh0=PYJDJH0 and zxrq00=VJLRQ00 and zxsj00=VJLSJ00; exception when others then Vcount:=0; end; if Vcount<>0 then update YJ_YWZXJL set zxcs00=NVL(zxcs00,0)+PZXSL00 where yjdjh0=PYJDJH0 and zxrq00=VJLRQ00 and zxsj00=VJLSJ00; else INSERT INTO YJ_YWZXJL(yjdjh0,zxrq00,zxsj00,zxcs00,zxr000) VALUES (PYJDJH0,TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),PZXSL00,VZXR000); end if; --END IF; select zxsl00 INTO VZXSL00 from YJ_YW0000 where YJDJH0=PYJDJH0; IF VZXSL00>=VSL0000 THEN UPDATE YJ_YW0000 SET XMZT00='3' where YJDJH0=PYJDJH0; UPDATE YJ_YWMX00 SET ZXRXM0=LS_ZXRXM0,ZXRQ00=TO_CHAR(SYSDATE,'YYYYMMDD'),ZXSJ00=TO_CHAR(SYSDATE,'HH24:MI:SS') where YJDJH0=PYJDJH0; END IF; IF NVL(VDJH000,0)>0 THEN--如果费用已经收过的话，得考虑费用明细表得执行医生是否正确 IF LS_MZZYBZ='1' THEN UPDATE ZY_FYMX00 SET ZXYS00=VZXR000,ZXKS00=PZXKS00,ZXRQ00=to_char(sysdate,'YYYYMMDD'),ZXSJ00=to_char(sysdate,'HH24:mi:ss') where DJH000=VDJH000 AND --NVL(ZXYS00,0)<=0 AND XMBH00 IN (select SFXMID from YJ_YWJJ00 where YJDJH0=PYJDJH0); ELSE UPDATE SF_FYMX00 SET ZXYS00=VZXR000,ZXKS00=PZXKS00,ZXRQ00=to_char(sysdate,'YYYYMMDD'),ZXSJ00=to_char(sysdate,'HH24:mi:ss') where DJH000=VDJH000 AND --NVL(ZXYS00,0)<=0 AND XMBH00 IN (select SFXMID from YJ_YWJJ00 where YJDJH0=PYJDJH0); END IF; END IF; END IF; if Upper(PCOMMIT)='Y' then commit; end if; commit; --if Upper(PCOMMIT)<>'M' then -- commit; --end if; EXCEPTION WHEN Ecustom THEN RAISE_APPLICATION_ERROR(-20011,substrb(Verrmsg||'！*'||Verrmsg,1,180)); WHEN E_YEWFS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20010,substrb('病人账户余额为负数，请先到收费处交钱后,再来执行!'||Verrmsg,1,120)); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, substrb('数据没有找到!*'||Verrmsg,1,180)); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错！*'||Verrmsg),1,120)); ROLLBACK; END SP_YJ_YWZX00; /