-- Start of DDL script for SP_BQ_BRCYDJ -- Generated 31-三月-15 4:48:23 pm -- from fw2-SD_HOSPITAL:2 -- Procedure SP_BQ_BRCYDJ CREATE OR REPLACE PROCEDURE SP_BQ_BRCYDJ--病人出院登记 ( PZYID00 IN NUMBER , --病人ID PBQH000 IN NUMBER , --操作病区号 PCYYS00 IN NUMBER , --出院医生 PCYRQSJ IN CHAR , --出院日期 PQXZYBZ IN CHAR , --取消出院 PCZY000 IN NUMBER , --操作员 PZLXG00 In Number --诊疗效果 ) AS Vparams VARCHAR2(255); COUNTER NUMBER(5) ; Vczyxm0 BM_YGBM00.ZWXM00%TYPE; --操作员姓名 Verrmsg varchar2(255); Ecustom EXCEPTION; -- Vsfxmid BM_YYSFXM.SFXMID%TYPE; -- Vvalue0 XT_XTCS00.VALUE0%TYPE; Vcfscbz XT_XTCS00.VALUE0%TYPE; --病人出院登记时删除处方标志 Vyjscbz XT_XTCS00.VALUE0%TYPE; --病人出院登记时删除医技项目标志 Vcfyxcy XT_XTCS00.VALUE0%TYPE; --存在未发药品允许出院 Vlscfcy XT_XTCS00.VALUE0%TYPE; --存在已记帐未发药零散处方不允许出院 Vlscfxz XT_XTCS00.VALUE0%TYPE; --存在未记账零散处方不行出院 Vyjyxcy XT_XTCS00.VALUE0%TYPE; --存在未执行医技允许出院 Vtyyxcy XT_XTCS00.VALUE0%TYPE; --退药未处理允许允许出院 Vyjqxsf XT_XTCS00.VALUE0%TYPE; --医技单未收费的出院时强行收费 Vjflbid BM_BRJFLB.JFLBID%TYPE; --住院帐户类别ID Vch0000 BM_CWK000.CH0000%TYPE; --病人当前的床号 Vbrid00 ZY_BRXXB0.BRID00%TYPE; --病人ID号 Vsysdate DATE; VCYXJXZ varchar2(2);--出院小结限制 Vfbbh00 zy_brxxb0.fbbh00%type;--费别编号 vXLXGMC XT_ZLXG00.MC0000%Type;--诊疗效果名称 Vyjdwgz XT_XTCS00.VALUE0%TYPE; --医技单未更正不允许出院登记 vhjje00 sf_brfy00.hjje00%type; Vztsxd0 XT_XTCS00.VALUE0%TYPE; --总天数相等才允许出院登记 Vtatjpd XT_XTCS00.VALUE0%TYPE; Vryrq00 ZY_BRXXB0.RYRQ00%TYPE; --入院日期 VDQKS00 ZY_BRXXB0.DQKS00%TYPE; --当前科室 Vyyqc00 xt_yyxx00.yyqc00%type;--所属医院 Vtsjsfs xt_xtcs00.value0%type;--床位诊疗护理费用天数计算方式 COUNTER1 NUMBER(5) ; --临时变量 VZRZCID LJ_ZRZC00.ZRZCID%TYPE; VODQKS0 ZY_BRXXB0.DQKS00%TYPE; COUNTER2 NUMBER(5) ; --临时变量 VCYDJSFYXBTZYZ XT_XTCS00.VALUE0%TYPE; --病人出院登记时如果医嘱都按时提取和执行好后是否允许不停止医嘱就出院 Y 是 N否 默认值为N VRYCS00 ZY_BRXXB0.BRRYCS%type;--住院次数 LS_CWS000 number(10);--床位数 LS_QTF000 number(10);--其他数 VSQL000 varchar2(100); VCYRQQFWQSJ varchar2(10); VCYDJRQSJ varchar2(20); BQ_SFZCYBZYZPD VARCHAR2(10); --是否做出院标志医嘱判断 CURSOR C_YW0000 IS SELECT YJDJH0,YJKSBH FROM YJ_YW0000 WHERE ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') AND NVL(CXDJH0,0)=0;--AND YJLRBZ='N' CURSOR C_BMMC00_WJZCF IS SELECT DISTINCT L.BMMC00 FROM YF_ZYCF00 C,YF_ZYCFMX M,BM_BMBM00 L WHERE C.CFLSH0=M.CFLSH0 AND C.DQKS00=L.BMBH00 AND ZYID00=PZYID00 AND CFZT00='0' AND CXBZ00 IN ('Z');--AND SFKSLY='Y' and CFSRBZ IN ('4','6'); CURSOR C_BMMC00_WJZYJ IS SELECT DISTINCT B.BMMC00 FROM YJ_YW0000 A,BM_BMBM00 B WHERE A.YJKSBH=B.BMBH00 AND ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') AND NVL(CXDJH0,0)=0 --AND YJLRBZ='N'; and (VYJYXCY='N' or (VYJYXCY='M' and exists (select 1 from YJ_YWJJ00 where YJDJH0=a.YJDJH0))); CURSOR C_BMMC00_YJZCF IS SELECT DISTINCT L.BMMC00 FROM YF_ZYCF00 C,YF_ZYCFMX M,BM_BMBM00 L WHERE C.CFLSH0=M.CFLSH0 AND C.DQKS00=L.BMBH00 AND ZYID00=PZYID00 AND CFZT00='1' AND CXBZ00 IN ('Z') ;--AND SFKSLY='Y' and CFSRBZ IN ('4','6'); CURSOR C_BMMC00_YJWGZ IS SELECT DISTINCT A.YJKSMC FROM VW_ZY_YJYW00 A where A.ZYID00 = PZYID00 and A.CXDJH0 in (select YJDJH0 from YJ_YW0000 where ZYGHID = A.ZYID00 and SFDJH0 in (select DJH000 from ZY_BRFY00 where JZDH00 = 0 and ZYID00=A.ZYID00)) and A.SFDJH0 = 0; CURSOR C_ZYZTS0 IS SELECT c.zyfpid,E.xmmc00,SUM(A.xmsl00) xmsl00 FROM ZY_FYMX00 A,ZY_BRFY00 B,BM_YYSFXM C,BM_TYZD00 D,BM_FPXM00 E WHERE A.djh000=B.djh000 AND A.xmbh00=C.sfxmid AND B.zyid00=PZYID00 and c.bmgzid = 1 and d.ZDMC00='床位/护理/诊察费维护' and c.bh0000=d.bh0000 and c.zyfpid=e.fpxmid group by c.zyfpid,e.xmmc00; CURSOR C_FYMX00 IS SELECT SUM(DECODE(C.BH0000,'011020000502',A.xmsl00,0)) ZYZCF0,--住院诊察费,58元/天 SUM(DECODE(C.BH0000,'011090000101',A.xmsl00,0)) CWF180,--床位费,18元/天 SUM(DECODE(C.BH0000,'011090000120',A.xmsl00,0)) CWF250,--床位费,25元/天 SUM(DECODE(C.BH0000,'011090000130',A.xmsl00,0)) CWF200,--床位费,20元/天 SUM(DECODE(C.BH0000,'011090000110',A.xmsl00,0)) CWF330,--单人间,33元/天 SUM(DECODE(C.BH0000,'012010000300',A.xmsl00,0)) YJHL00,--I级护理 SUM(DECODE(C.BH0000,'012010000400',A.xmsl00,0)) EJHL00,--II级护理 SUM(DECODE(C.BH0000,'012010000500',A.xmsl00,0)) SJHL00,--III级护理 SUM(DECODE(C.BH0000,'011090000103',A.xmsl00,0)) PBF000,--陪伴费,2元/天 SUM(DECODE(C.BH0000,'011090000104',A.xmsl00,0)) WJ0000,--卧具,2元/天 SUM(DECODE(C.BH0000,'011090000105',A.xmsl00,0)) DDFB00,--电动防褥,10元/天 SUM(DECODE(C.BH0000,'011090000106',A.xmsl00,0)) ZC2000,--租床,20元/天 SUM(DECODE(C.BH0000,'011090000107',A.xmsl00,0)) ZC2500,--租床,25元/天 SUM(DECODE(C.BH0000,'011090000112',A.xmsl00,0)) ZFDRJ0,--自费单人间,8元/天 SUM(DECODE(C.BH0000,'011090000122',A.xmsl00,0)) ZFYZBF --自费优质病房,75元/天 FROM ZY_FYMX00 A,ZY_BRFY00 B,BM_YYSFXM C WHERE A.djh000=B.djh000 AND A.xmbh00=C.sfxmid AND B.zyid00=PZYID00; -- MODIFICATION HISTORY -- Person Date Comments -- qks 2007.06.18 1、病人出院登记时出院小结不允许为空；2、当BQ_CYDJSFYXCYXJWK=‘M’时，医保病人出院诊断不能为空。 -- qks 2008.08.13 增加参数BQ_CYDJTXBRBASY判断出院登记是否要求填写病人病案首页,默认N。 -- qks 2008.09.19 新增参数BQ_YJDWGZBNCY，有未更正的医技单项目不允许出院登记。 -- qks 2008.10.07 参数BQ_WZXYJSFYXCY功能扩展,值为M时:如果存在未记帐的医技单,当医技业务计价表(YJ_YWJJ00)无记录,那么允许病人转区或者出院登记,反之则不允许。 -- XZW 2008.10.23 住院病人消费费用为10块以下的不需要填写病案首页也能出科 -- qks 2008.11.06 新增参数BQ_CYDJ_CWHLZCZTS:床位/护理/诊察费各自总数量是否都要等于住院总天数,才允许出院登记 BQ-20081024-003 -- qks 2008.11.13 挂床请假后直接办理出院登记,病人流动信息表CH0000和KSH000不能为0 BQ-20081107-001 2\; 11.18 VCH0000取值改为nvl(RYCWH0,'0') -- zhr 2009.03.10 增加实际出院登记日期字段 -- chenqw 2009.10.20 福州肺科医院要求病人出院登记时,将zy_brxxb0.zzys00及zzysxm改为出院登记时输入的出院医生。 -- zhr 2009.10.20 取消收治，要删除诊断信息 -- zhangwz 2010.09.06 增加参数 BQ_SFXZCYZDWCRBSTXCRBBGK 控制病区做出院登记的时候是否限制如果出院诊断为传染病诊断时必须填写传染病报告卡. by BQ-20100906-002. -- qks 2010.11.22 修改“病人病案首页未填写，不让出科”判断条件A.RYRQ00=B.RYRQ00为A.ZYCS00=B.BRRYCS -- liuj 2011.04.06 修改:BQ-20081024-003中对床位/诊察/护理费的限制使用zyfpid条件改为用发票名称匹配,因有些医院fpxmid与别不同--BQ-20110401-002 -- liuj 2011.04.15 新增参数BQ_CWZCHLFTSJSFS控制床位/诊察/护理费的限制计算天数方法。by BQ-20110414-002 -- dsm 2011.05.02 病人没有出径的不能出病区 -- liuj 2011.07.29 修改同安医院要求出院登记数据条件判断 -- zhangwz 2011.08.03 增加参数控制BQ_CYDJSFYXBTZYZ出院登记时是否允许不停止医嘱 by BQ-20110705-001. -- liuj 2011.08.25 修改bq_brldxx转区病人等的qszt00为新入或入院的bqjsrq字段为出院登记日期 by BQ-20110822-002 -- liuj 2011.09.02 对病人有未记账医技单不允许出院的限制进行条件修正 by BQ-20110831-002 -- zhangwz 2011.09.05 对于参数BQ_CYDJTXBRBASY的select下移到正确的位置. -- liuj 2011.12.16 增加参数BQ_CZWSFSSSFTSXZCY控制已申请或已安排手术未收费进行提示不能出院.by BQ-20111207-002 -- liuj 2012.01.10 原ZY_WJZBNCY参数不论医嘱还是零散处方都一起控制.现改为增加参数BQ_YLSCFWJZSFXZCY 以单独控制出院登记时存在未记账零散处方不允许办出院.by BQ-20120109-001 -- liuj 2012.01.11 增加出院操作日期字段写入zy_brxxb0 by BQ-20120111-002 -- liuj 2012.02.28 增加写入zy_brxxb0.sjcysj字段 for BQ-20120222-002 -- dsm 2012.03.09 护理路径未处理的不能出院登记 for BQ-20120309-001 -- dsm 2012.03.27 护理路径未处理的不能出院登记 for BQ-20120309-001 -- liuj 2012.04.23 对病人有未记账医技单不允许出院的限制条件加参数进行控制修正 for BQ-20120413-001 -- dsm 2012.05.03 修改提示内容 BQ-20120503-001 -- dsm 2012.06.01 判断是否有护理未执行,改用视图VW_BQ_LJHLZXMX判断 for BQ-20120521-001 -- liuj 2012.07.18 出院登记过程，bq_yzzxjl被删除，由于包药机接口要用到该表记录，所以在删除记录前，将记录移到bq_yzzxjl_bk表。for BQ-20120717-001 -- liuj 2012.12.28 加参数是否判断有未填切口，愈合等级的收不允许出院 for ZYYS-20121121-004 -- dsm 2013.05.06 省二出院登记对床位/诊察/空调费的限制延用泉州附二的参数BQ_CYDJ_CWHLZCZTS,但是增加空调费（维护在通用字典） for BQ-20130416-001 -- dsm 2013.05.23 莆田没有出院小结的不能出院当参数BQ_CYDJSFYXCYXJWK='NP' for BQ-20130416-001 -- liuj 2103.12.13 病人出院登记时，将ZKJCBZ=Y的置成N for BQ-20131212-001 -- liuj 2013.12.20 SELECT后面的zyfpid，需改为c.zyfpid for BQ-20131218-004 -- dsm 2015.01.07 泉二出院登记取服务器时间,参数BQ_CYRQQFWQSJ for BQ-20150106-001 -- dsm 2015.01.28 是否做出院标志医嘱判断,增加参数BQ_SFZCYBZYZPD for BQ-20150122-001 BEGIN Vsysdate:=SYSDATE; Vparams :='SP_BQ_BRCYDJ('||TO_CHAR(PZYID00)||', '||TO_CHAR(PBQH000)||','||TO_CHAR(PCYYS00) ||','''||PCYRQSJ||''','||TO_CHAR(PCZY000)||')'; --select count(1) into counter from xt_xtcs00 where name00='BQ_CYDJTXBRBASY' and value0='Y'; select count(*) into counter from LJ_ZRZC00 where ZYID00=PZYID00 and nvl(JSRQ00,' ')=' '; if counter>0 then Verrmsg:='病人还未出路径，不让出科'; RAISE Ecustom; end if; VCYDJRQSJ:=PCYRQSJ; select nvl(max(trim(value0)),'N') into VCYRQQFWQSJ from XT_XTCS00 where name00='BQ_CYRQQFWQSJ' and value0='Y'; if VCYRQQFWQSJ='Y' then VCYDJRQSJ:=to_char(sysdate,'YYYYMMDDHH24:MI:SS'); end if; select nvl(max(trim(value0)),'N') into BQ_SFZCYBZYZPD from XT_XTCS00 where name00='BQ_SFZCYBZYZPD' and Value0='Y'; if BQ_SFZCYBZYZPD='Y' and PQXZYBZ IS NULL then select count(*) into COUNTER from BQ_YJYZ00 a where ZYID00=PZYID00 and YZZT00 in ('3','2') and exists(select 1 from BM_TYZD00 where ZDMC00='出院标志性医嘱' and instr(ZLXMJC,MC0000)>0 ) and exists (select 1 from zy_brxxb0 b where b.zyid00=a.zyid00 and b.sjcyrq<>'20991231'); --and (instr(ZLXMJC,'死亡')>0 or instr(ZLXMJC,'今日出院')>0 or instr(ZLXMJC,'明日出院')>0 or instr(ZLXMJC,'自动出院')>0) ; if COUNTER=0 then Verrmsg:='病人没有出院标志性医嘱，不能出院！'; Raise Ecustom; end if; end if; SELECT DQKS00 INTO VODQKS0 FROM ZY_BRXXB0 WHERE DQBQ00=PBQH000 AND ZYID00=PZYID00; select count(1) into counter from xt_xtcs00 where name00='BQ_SFQYHLLJ' and value0='Y'; --是否启用护理路径 --判断病人是否还在路径护理工作未处理 if counter>0 then begin select ZRZCID into VZRZCID from ( select ZRZCID from LJ_ZRZC00 where ZYID00=PZYID00 and JSFS00='完成' and KSBH00=VODQKS0 order by KSRQ00 desc) where rownum=1 ; exception when others then VZRZCID:=0; end; if VZRZCID>0 then --是否启用护理路径 select count(*) into COUNTER from VW_BQ_LJHLZXMX where ZRZCID=VZRZCID and SFZX00='0'; if (COUNTER>0) then Verrmsg:='病人还有未处理的路径护理工作，请处理后后再出院登记'; Raise Ecustom; end if; end if; end if; begin--病人出院登记时如果医嘱都按时提取和执行好后是否允许不停止医嘱就出院 Y 是 N否 默认值为N 至于是否已经按时从出院登记的前台已经限制好了. select UPPER(TRIM(VALUE0)) inTO VCYDJSFYXBTZYZ from XT_XTCS00 where NAME00='BQ_CYDJSFYXBTZYZ'; exception when others THEN VCYDJSFYXBTZYZ:='N'; end; select count(1) into counter from xt_xtcs00 where name00='BQ_CYDJTXBRBASY' and value0='Y'; IF COUNTER>0 THEN SELECT COUNT(1) INTO COUNTER FROM BA_BRZYXX A,ZY_BRXXB0 B WHERE A.BAZT00 ='2' AND B.ZYID00=PZYID00 AND A.ZYH000=TRIM(B.ZYH000) AND A.ZYCS00=B.BRRYCS; IF COUNTER=0 THEN select sum(hjje00) into vhjje00 from zy_brfy00 where zyid00=PZYID00 and jzdh00+0=0; if vhjje00>10 then Verrmsg:='病人病案首页未填写，不让出科'; RAISE Ecustom; end if; END IF; END IF; select count(1) into counter from xt_xtcs00 where name00='BQ_CYDJTXBRBASY' and value0='M'; IF COUNTER>0 THEN SELECT COUNT(1) INTO COUNTER FROM BA_BRZYXX A where LSH000=PZYID00 and RYQK00 is not null and CYRQ00 is not null; IF COUNTER=0 THEN Verrmsg:='病案首页的入院情况或出院日期未填写，无法办理出院'; RAISE Ecustom; END IF; end if ; IF nvl(PCYYS00,0)=0 THEN Verrmsg:='未指定出院医生'; RAISE Ecustom; END IF; SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=PCZY000; --判定病人状态是否正确 SELECT COUNT(*) INTO COUNTER FROM ZY_BRXXB0 WHERE DQBQ00=PBQH000 AND ZYID00=PZYID00 AND BRZT00 IN ('20','23'); IF COUNTER=0 THEN Verrmsg:='病人状态已经改变，请刷新后重试'; RAISE Ecustom; ELSE --取病人的床号和ID号 SELECT nvl(RYCWH0,'0'),BRID00,FBBH00,RYRQ00,DQKS00,BRRYCS INTO VCH0000,VBRID00,Vfbbh00,Vryrq00,VDQKS00,VRYCS00 FROM ZY_BRXXB0 WHERE ZYID00=PZYID00; END IF; select count(*) into counter from xt_xtcs00 where name00='BQ_CYDJSFYXCYXJWK'; if COUNTER>0 then select trim(value0) into VCYXJXZ from xt_xtcs00 where name00='BQ_CYDJSFYXCYXJWK'; if VCYXJXZ='N' then select count(*) into COUNTER from zy_brxxb0 where zyid00=PZYID00 and cyxj00 is null; if COUNTER >0 then Verrmsg:='出院小结不能为空'; RAISE Ecustom; end if; end if; if VCYXJXZ='M' and Vfbbh00='3' then select count(*) into COUNTER from zy_brxxb0 where zyid00=PZYID00 and cyxj00 is null; if COUNTER >0 then Verrmsg:='医保病人出院小结不能为空'; RAISE Ecustom; end if; select count(*) into COUNTER from bq_brzdxx where zyid00=PZYID00 and ZDLB00='3'; if COUNTER =0 then Verrmsg:='医保病人出院诊断不能为空'; RAISE Ecustom; end if; end if; if VCYXJXZ='NP'then --莆田出院小结的限制 VSQL000:='select count(*) from br_cyxj@HISTOEMR where patient_id='''||VBRID00 ||''' and visit_id='||VRYCS00; execute immediate VSQL000 into COUNTER; if COUNTER =0 then Verrmsg:='出院小结不能为空'; RAISE Ecustom; end if; end if; end if; --限制出院诊断为传染病诊断时必须填写传染病报告卡. by zhangwz select count(*) into counter from xt_xtcs00 where name00='BQ_SFXZCYZDWCRBSTXCRBBGK' and VALUE0='Y'; if counter>0 then select count(a.ZYID00) into COUNTER from BQ_BRZDXX a,XT_ICD900 b where a.zyid00=PZYID00 and a.ZDLB00='3' and a.ZDM000=b.ICD900 and b.SFCRB0='1' and not exists (select 1 from CR_CRBDJ0 where ZYID00=a.ZYID00 and (JBMC00=a.ZDMC00 OR DJID00=a.CRDJID)); if COUNTER>0 then Verrmsg:='系统限制出院主要诊断填写为传染病诊断的必须填写传染病报告卡，请先通知医生填写后再来操作！'; RAISE Ecustom; end if; end if; --判断是否存在未填切口愈合等级手术，跳出提示。 for ZYYS-20121121-004 select count(*) into counter from xt_xtcs00 where name00='BQ_CZQKYHDJWTSSSFXZCY' and VALUE0='Y'; if COUNTER>0 then select count(*) into counter from ss_yw0000 a,ss_ywmx00 b where a.ZYGHID=PZYID00 and a.ssdh00=b.ssdh00 and ((b.qkdjmc is null) or (b.yhdjmc is null)) and (a.SSFYBZ='2' and a.MZFYBZ='2'); if COUNTER>0 then Verrmsg:='存在未填切口等级或愈合等级的手术，不能出院，请先处理后再来操作！'; RAISE Ecustom; end if; end if; --判断是否存在已申请或安排手术未收费，跳出提示。 by BQ-20111207-002 select count(*) into counter from xt_xtcs00 where name00='BQ_CZWSFSSSFTSXZCY' and VALUE0='Y'; if COUNTER>0 then select count(*) into counter from ss_yw0000 where ZYGHID=PZYID00 and SSFYBZ in ('0','1') and MZFYBZ in ('0','1') and sszt00 in ('0','2','4') ; if COUNTER>0 then Verrmsg:='存在未收费手术不能出院，请先处理后再来操作！'; RAISE Ecustom; end if; end if; --判定系统帐户参数是否正常，以便准备返还押金的预扣的费用 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; --判定病人是否有未停止的药品医嘱 SELECT COUNT(*) INTO COUNTER FROM BQ_YPYZ00 WHERE ZYID00=PZYID00 AND YZZT00 IN ('1','2','5') AND ROWNUM=1; --SELECT COUNT(*) INTO COUNTER FROM VW_BQ_YPYZMX_WTZ --WHERE ZYID00=PZYID00 AND((YZZT00='1' AND TRUNC(VSYSDATE)>TRUNC(NVL(QYRQSJ,VSYSDATE))) -- OR (YZZT00='2' AND TRUNC(VSYSDATE)>TRUNC(NVL(TQRQSJ,VSYSDATE)))) AND ROWNUM=1; IF (COUNTER>0) and (trim(VCYDJSFYXBTZYZ)='N') THEN Verrmsg:='病人有未停止的药品医嘱，请执行后再出院'; RAISE Ecustom; END IF; SELECT COUNT(*) INTO COUNTER FROM BQ_YZZXJL WHERE ZYID00=PZYID00 AND YZID00>0 AND DJHLB0='0' AND ZT0000 IN ('0')AND ROWNUM=1 AND SFXYZX='Y'; --SELECT COUNT(*) INTO COUNTER FROM VW_BQ_YPYZMX_WTZ --WHERE ZYID00=PZYID00 AND((YZZT00='1' AND TRUNC(VSYSDATE)>TRUNC(NVL(QYRQSJ,VSYSDATE))) -- OR (YZZT00='2' AND TRUNC(VSYSDATE)>TRUNC(NVL(TQRQSJ,VSYSDATE)))) AND ROWNUM=1; IF (COUNTER>0) THEN Verrmsg:='病人有未执行的药品医嘱，请执行后再出院'; RAISE Ecustom; END IF; SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00 WHERE ZYID00=PZYID00 AND YZZT00 IN ('1','2','5','6') AND ROWNUM=1; --WHERE ZYID00=PZYID00 AND ((YZZT00='2' AND TO_CHAR(VSYSDATE,'YYYYMMDD')>TQRQ00) -- OR (YZZT00='1' AND TO_CHAR(VSYSDATE,'YYYYMMDD')>QYRQ00 )) AND ROWNUM=1; IF (COUNTER>0) and (trim(VCYDJSFYXBTZYZ)='N') THEN Verrmsg:='病人有未停止的非药医嘱，请停止后再出院'; RAISE Ecustom; END IF; SELECT COUNT(*) INTO COUNTER FROM BQ_JZXM00 WHERE ZYID00=PZYID00 and TZRQ00 IS NULL AND ROWNUM=1; -- AND((XMLB00 IN ('0','2') AND TO_CHAR(VSYSDATE-1,'YYYYMMDD')>NVL(GZRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))) -- OR (XMLB00 IN ('1') AND TO_CHAR(VSYSDATE-1,'YYYYMMDD')>NVL(GZRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD')))) IF COUNTER>0 THEN Verrmsg:='病人有未停止的收费项目，请停止后再出院'; RAISE Ecustom; END IF; --停止所有的药品医嘱 -- UPDATE BQ_YPYZ00 SET TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), -- TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), -- YZZT00='3', -- TZYS00=KZYS00, -- TZRXM0=VCZYXM0 -- WHERE ZYID00=PZYID00 AND YZZT00 IN ('1','2'); --停止所有的医技医嘱 -- UPDATE BQ_YJYZ00 SET TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), -- TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), -- YZZT00='3', -- TZYS00=KZYS00, -- TZRXM0=VCZYXM0 -- WHERE ZYID00=PZYID00 AND YZZT00 IN ('1','2'); --删除所有新开的医嘱 -- DELETE FROM BQ_YPYZMX WHERE YZID00 IN (SELECT ZYID00 FROM BQ_YPYZ00 WHERE YZZT00='0' AND ZYID00=PZYID00); -- DELETE FROM BQ_YPYZ00 WHERE YZZT00='0' AND YZID00 IN (SELECT ZYID00 FROM BQ_YPYZ00 WHERE YZZT00='0') AND ZYID00=PZYID00; -- DELETE FROM BQ_YJYZ00 WHERE YZZT00='0' AND ZYID00=PZYID00; --停止所有的收费项目 --UPDATE BQ_JZXM00 SET TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), -- TZRXM0=VCZYXM0 WHERE ZYID00=PZYID00 AND TZRQ00 IS NULL; BEGIN--总天数相等才允许出院登记 SELECT UPPER(TRIM(VALUE0)) INTO Vztsxd0 FROM XT_XTCS00 WHERE NAME00='BQ_CYDJ_CWHLZCZTS'; EXCEPTION WHEN OTHERS THEN Vztsxd0:='N'; END; BEGIN SELECT UPPER(TRIM(VALUE0)) INTO Vtatjpd FROM XT_XTCS00 WHERE NAME00='BQ_SFTAYYCYDJTJPD'; EXCEPTION WHEN OTHERS THEN Vtatjpd:='N'; END; BEGIN--医技单未更正不能出院判定 SELECT UPPER(TRIM(VALUE0)) INTO Vyjdwgz FROM XT_XTCS00 WHERE NAME00='BQ_YJDWGZBNCY'; EXCEPTION WHEN OTHERS THEN Vyjdwgz:='N'; END; BEGIN--医技单强行收费判定 SELECT UPPER(TRIM(VALUE0)) INTO VYJQXSF FROM XT_XTCS00 WHERE NAME00='BQ_CYSYJDQXSF'; EXCEPTION WHEN OTHERS THEN VYJQXSF:='N'; END; BEGIN--出院时未收费处方是否强制作废 SELECT UPPER(TRIM(VALUE0)) INTO VCFSCBZ FROM XT_XTCS00 WHERE NAME00='BQ_CYDJCFSCBZ'; EXCEPTION WHEN OTHERS THEN VCFSCBZ:='N'; END; BEGIN--出院时未收费医技是否强制作废 SELECT UPPER(TRIM(VALUE0)) INTO VYJSCBZ FROM XT_XTCS00 WHERE NAME00='BQ_CYDJYJXMSCBZ'; EXCEPTION WHEN OTHERS THEN VYJSCBZ:='N'; END; BEGIN--处方未记账不能出院判定 SELECT UPPER(TRIM(VALUE0)) INTO VCFYXCY FROM XT_XTCS00 WHERE NAME00='ZY_WJZBNCY'; IF VCFYXCY='Y' THEN VCFYXCY:='N'; ELSE VCFYXCY:='Y'; END IF; EXCEPTION WHEN OTHERS THEN VCFYXCY:='Y'; END; BEGIN--存在未记账零散处方不允许出院 SELECT UPPER(TRIM(VALUE0)) INTO Vlscfxz FROM XT_XTCS00 WHERE NAME00='BQ_YLSCFWJZSFXZCY'; EXCEPTION WHEN OTHERS THEN Vlscfxz:='N'; END; BEGIN--存在已记帐未发药零散处方不允许出院 SELECT UPPER(TRIM(VALUE0)) INTO Vlscfcy FROM XT_XTCS00 WHERE NAME00='BQ_CYDJLSCFFY'; EXCEPTION WHEN OTHERS THEN Vlscfcy:='N'; END; BEGIN--医技未记账出院判定 SELECT UPPER(TRIM(VALUE0)) INTO VYJYXCY FROM XT_XTCS00 WHERE NAME00='BQ_WZXYJSFYXCY'; EXCEPTION WHEN OTHERS THEN VYJYXCY:='Y'; END; BEGIN--退药未处理出院判定 SELECT UPPER(TRIM(VALUE0)) INTO VTYYXCY FROM XT_XTCS00 WHERE NAME00='ZY_YTFSFYXCY'; IF VTYYXCY='Y' THEN VTYYXCY:='N'; ELSE VTYYXCY:='Y'; END IF; EXCEPTION WHEN OTHERS THEN VTYYXCY:='Y'; END; IF Vtatjpd='Y' THEN FOR FYMX IN C_FYMX00 LOOP IF FYMX.ZYZCF0<>FYMX.CWF180+FYMX.CWF200+FYMX.CWF250+FYMX.CWF330 then Verrmsg:='住院诊察费次数不等于床位费:其中[诊察费]'||to_char(FYMX.ZYZCF0)||'次,[床位费]18元的'||to_char(FYMX.CWF180)||'次、20元的'||to_char(FYMX.CWF200)||'次、25元的'||to_char(FYMX.CWF250)||'次、单人间33元的'||to_char(FYMX.CWF330)||'次'; RAISE Ecustom; end if; IF FYMX.ZYZCF0<>FYMX.YJHL00+FYMX.EJHL00+FYMX.SJHL00 then Verrmsg:='住院诊察费次数不等于护理费:其中[诊察费]'||to_char(FYMX.ZYZCF0)||'次,[I级护理]'||to_char(FYMX.YJHL00)||'次、[II级护理]'||to_char(FYMX.EJHL00)||'次、[III级护理]'||to_char(FYMX.SJHL00)||'次'; RAISE Ecustom; end if; IF FYMX.ZYZCF0'Y') then --是否属于取消出院记录后再收治 select count(1) into counter from zy_brxxb0 where zyid00=PZYID00 and cyrq00 is null; select trim(value0) into Vtsjsfs from xt_xtcs00 where name00='BQ_CWZCHLFTSJSFS'; if counter>0 then COUNTER1:=0; select TRUNC(SYSDATE)-TO_DATE(Vryrq00,'YYYYMMDD') INTO COUNTER from dual; --住院总天数 LS_CWS000:=0; LS_QTF000:=0; FOR ZYZTS0 IN C_ZYZTS0 LOOP if ZYZTS0.xmmc00='床位费' then --床位费 LS_CWS000:=ZYZTS0.xmsl00; if ZYZTS0.xmsl00<>COUNTER then Verrmsg:=Verrmsg||'应退还床位费'||TO_CHAR(ZYZTS0.xmsl00-COUNTER)||'次,'; end if; COUNTER1:=COUNTER1+1; ELSIF ZYZTS0.xmmc00='诊察费' then --诊察费 if Vtsjsfs='1' then if ZYZTS0.xmsl00<>COUNTER then Verrmsg:=Verrmsg||'应退还诊察费'||TO_CHAR(ZYZTS0.xmsl00-COUNTER)||'次,'; end if; ELSIF ZYZTS0.xmsl00<>COUNTER+1 then Verrmsg:=Verrmsg||'应退还诊察费'||TO_CHAR(ZYZTS0.xmsl00-COUNTER-1)||'次,'; end if; COUNTER1:=COUNTER1+10; ELSIF ZYZTS0.xmmc00='护理费' then --护理费 if Vtsjsfs='1' then if ZYZTS0.xmsl00<>COUNTER then Verrmsg:=Verrmsg||'应退还护理费'||TO_CHAR(ZYZTS0.xmsl00-COUNTER)||'次,'; end if; ELSIF ZYZTS0.xmsl00<>COUNTER+1 then Verrmsg:=Verrmsg||'应退还护理费'||TO_CHAR(ZYZTS0.xmsl00-COUNTER-1)||'次,'; end if; COUNTER1:=COUNTER1+100; ELSIF ZYZTS0.xmmc00='其他费' then --空调费 BQ-20130416-001 if Vtsjsfs='0' then if ZYZTS0.xmsl00>LS_CWS000 then Verrmsg:=Verrmsg||'空调降温费的天数'||ZYZTS0.xmsl00||'不能大于床位天数'||LS_CWS000; COUNTER1:=COUNTER1+1000; end if; end if; END IF; END LOOP; if COUNTER>0 then --有住院天数 if COUNTER1=0 then --全部没收费 Verrmsg:=Verrmsg||'床位、诊察、护理费全部没收费,应各补收'||TO_CHAR(COUNTER)||'次,'; elsif COUNTER1<1111 then --有某类发票全部没收费 select lpad(To_char(COUNTER1),4,'0') into Vztsxd0 from dual; if substrb(Vztsxd0,4,1)='0' then Verrmsg:=Verrmsg||'应退还床位费-'||TO_CHAR(COUNTER)||'次,'; end if; if substrb(Vztsxd0,3,1)='0' then if Vtsjsfs='1' then Verrmsg:=Verrmsg||'应退还诊察费-'||TO_CHAR(COUNTER)||'次,'; else Verrmsg:=Verrmsg||'应退还诊察费-'||TO_CHAR(COUNTER+1)||'次,'; end if; end if; if substrb(Vztsxd0,2,1)='0' then if Vtsjsfs='1' then Verrmsg:=Verrmsg||'应退还护理费-'||TO_CHAR(COUNTER)||'次,'; else Verrmsg:=Verrmsg||'应退还护理费-'||TO_CHAR(COUNTER+1)||'次,'; end if; end if; end if; end if; if Verrmsg is not null then Verrmsg:='住院总天数'||TO_CHAR(COUNTER+1)||','||Verrmsg; RAISE Ecustom; end if; end if; END IF; --医技单未更正不能出院判定(莆田人民医院) IF Vyjdwgz='Y' THEN select COUNT(*) INTO COUNTER from VW_ZY_YJYW00 A where A.ZYID00 = PZYID00 and A.CXDJH0 in (select YJDJH0 from YJ_YW0000 where ZYGHID = A.ZYID00 and SFDJH0 in (select DJH000 from ZY_BRFY00 where JZDH00 = 0 and ZYID00=A.ZYID00)) and A.SFDJH0 = 0 AND ROWNUM=1; IF COUNTER>0 THEN FOR YJWGZ IN C_BMMC00_YJWGZ LOOP Verrmsg:=Verrmsg||YJWGZ.YJKSMC||','; END LOOP; Verrmsg:=Verrmsg||'病人有医技单未更正，不能出院'; RAISE Ecustom; END IF; END IF; --医技单未收费的强行收费(泉州三院) IF VYJQXSF='Y' THEN SELECT COUNT(*) INTO COUNTER FROM YJ_YW0000 WHERE ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') AND ROWNUM=1 AND NVL(CXDJH0,0)=0;--AND YJLRBZ='N' IF COUNTER>0 THEN FOR YJ IN C_YW0000 LOOP SF_YJ_YWSF_NOCOMMIT(YJ.YJDJH0,YJ.YJKSBH,PCZY000,Verrmsg,Verrmsg); END LOOP; END IF; END IF; IF VCFSCBZ='Y' THEN--出院时强行作废未收费的医嘱处方(不包括退药申请和零散处方) --医嘱药品处理，有预扣药品库存和帐户金额 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=PZYID00 AND QLZT00='0' AND CXBZ00='Z' AND ROWNUM=1; IF COUNTER>0 THEN --返还预扣的药品库存数量 20031027由触发器完成予扣 --UPDATE YF_YPKCXX K SET YKKCSL=(SELECT YKKCSL+YPZSL0 -- FROM (SELECT SUM(YPZSL0*ZHL000)"YPZSL0",YFBMBH,YPNM00 -- FROM YF_YZYPSQ -- WHERE ZYID00=PZYID00 AND QLZT00='0'AND CXBZ00='Z' -- 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 ZYID00=PZYID00 AND -- QLZT00='0'AND CXBZ00='Z'); UPDATE YF_YZYPSQ SET QLZT00='3' WHERE ZYID00=PZYID00 AND QLZT00='0' AND CXBZ00='Z'; END IF; END IF; IF VYJSCBZ='Y' THEN--医技单未收费的强行作废(不包括冲销申请的医技单) UPDATE YJ_YW0000 SET XMZT00='6', ZXRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), ZXSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), ZXR000=PCZY000 WHERE ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') AND YJLRBZ='N' AND NVL(CXDJH0,0)=0; END IF; --处理处方 IF VCFYXCY='N' THEN--未收费处方不允许出院 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=PZYID00 AND QLZT00='0'AND CXBZ00 IN ('Z') ; IF COUNTER>0 THEN Verrmsg:='病人有医嘱药品处方未发，不能出院'; RAISE Ecustom; END IF; END IF; IF Vlscfxz='Y' THEN--未记账零散处方不允许出院 SELECT COUNT(*) INTO COUNTER FROM YF_ZYCF00 C,YF_ZYCFMX M WHERE C.CFLSH0=M.CFLSH0 AND ZYID00=PZYID00 AND CFZT00='0' AND CXBZ00 IN ('Z') ;--AND SFKSLY='Y' and CFSRBZ IN ('4','6'); IF COUNTER>0 THEN FOR WJZCF IN C_BMMC00_WJZCF LOOP Verrmsg:=Verrmsg||WJZCF.BMMC00||','; END LOOP; Verrmsg:=Verrmsg||'病人有零散处方未记账，不能出院'; RAISE Ecustom; END IF; END IF; --处理处方2 IF Vlscfcy='Y' THEN--已记帐未发药零散处方不允许出院 SELECT COUNT(*) INTO COUNTER FROM YF_ZYCF00 C,YF_ZYCFMX M WHERE C.CFLSH0=M.CFLSH0 AND ZYID00=PZYID00 AND CFZT00='1' AND CXBZ00 IN ('Z') AND SFKSLY<>'Y';-- and CFSRBZ IN ('4','6'); IF COUNTER>0 THEN FOR YJZCF IN C_BMMC00_YJZCF LOOP Verrmsg:=Verrmsg||YJZCF.BMMC00||','; END LOOP; Verrmsg:=Verrmsg||'病人有零散处方已记帐未发药，不能出院'; RAISE Ecustom; END IF; END IF; --处理医技单 IF (VYJYXCY='N') or (VYJYXCY='M') THEN--医技单未收费的不允许出院 select count(*) into COUNTER2 from xt_xtcs00 where name00='BQ_CYDJYJDFYW0SFXZ' and value0='Y' ; if COUNTER2>0 then SELECT COUNT(*) INTO COUNTER FROM YJ_YW0000 a WHERE ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') AND ROWNUM=1 AND NVL(CXDJH0,0)=0 --AND YJLRBZ='N' and (VYJYXCY='N' or (VYJYXCY='M' and exists (select 1 from YJ_YWJJ00 where YJDJH0=a.YJDJH0))); else SELECT COUNT(*) INTO COUNTER FROM YJ_YW0000 a WHERE ZYGHID=PZYID00 AND MZZYBZ='1' AND XMZT00 IN ('0','1') and zje000<>0 AND ROWNUM=1 AND NVL(CXDJH0,0)=0 --AND YJLRBZ='N' and (VYJYXCY='N' or (VYJYXCY='M' and exists (select 1 from YJ_YWJJ00 where YJDJH0=a.YJDJH0))); end if ; IF COUNTER>0 THEN FOR WJZYJ IN C_BMMC00_WJZYJ LOOP Verrmsg:=Verrmsg||WJZYJ.BMMC00||','; END LOOP; Verrmsg:=Verrmsg||'病人有未记账的医技单，不能出院'; RAISE Ecustom; END IF; END IF; IF VTYYXCY='N' THEN--退药未处理不允许出院 SELECT COUNT(*) INTO COUNTER FROM YF_YZYPSQ WHERE ZYID00=PZYID00 AND QLZT00='0'AND CXBZ00='-'; IF COUNTER>0 THEN Verrmsg:='病人有医嘱处方退药未处理，不能出院'; RAISE Ecustom; END IF; SELECT COUNT(*) INTO COUNTER FROM YF_ZYCF00 C,YF_ZYCFMX M WHERE C.CFLSH0=M.CFLSH0 AND ZYID00=PZYID00 AND CFZT00='0'AND CXBZ00 <>'Z';-- AND SFKSLY='Y';--CFSRBZ IN ('4','6'); IF COUNTER>0 THEN Verrmsg:='病人有零散处方退药未处理，不能出院'; RAISE Ecustom; END IF; END IF; --BEGIN -- SP_BQ_CYZXHL(PZYID00,PBQH000,PCYRQSJ,PCZY000); --EXCEPTION -- WHEN OTHERS THEN -- RAISE_APPLICATION_ERROR(-20015,'出院时执行护理医嘱出错！*'); --END; --出院登记时自动收费的项目 SELECT COUNT(*) into COUNTER FROM XT_XTCS00 WHERE NAME00='BQ_CYDJZDSFXM' AND VALUE0<>'0'; if COUNTER>0 then SELECT COUNT(*) INTO COUNTER FROM BQ_BRLDXX WHERE ZYID00=PZYID00 AND JSZT00='出院'; IF COUNTER=0 THEN--未出过院 SELECT value0 into Vvalue0 FROM XT_XTCS00 WHERE NAME00='BQ_CYDJZDSFXM' ; LOOP if instr(Vvalue0,',')>0 then Vsfxmid:=to_number(substrb(Vvalue0,1,instr(Vvalue0,',')-1)); Vvalue0:=substrb(Vvalue0,instr(Vvalue0,',')+1,length(Vvalue0)); else Vsfxmid:=to_number(trim(Vvalue0)); Vvalue0:='0'; end if; --作为床位费处理 INSERT INTO BQ_JZXM00 ( ID0000, ZYH000, ZYID00,CLBZ00,XMLB00,JFLBID ,SFXMID,CS0000,XMMC00, DW0000,SFJE00,LRRXM0,QSRQ00,FBBH00,LYBZ00,JJSFBZ,CH0000) SELECT SQ_BQ_JZXM00_ID0000.NEXTVAL,B.ZYH000,PZYID00,'1',1,Vjflbid,Vsfxmid, 1,S.XMMC00, S.DW0000,NVL(S.SFJE00,0),VCZYXM0,TO_CHAR(VSYSDATE,'YYYYMMDD'),B.fbbh00,'1','N',RYCWH0 FROM BM_YYSFXM S,ZY_BRXXB0 B WHERE Vsfxmid=S.SFXMID AND B.ZYID00=PZYID00; EXIT WHEN Vvalue0='0'; END LOOP; --收费 SP_BQ_JZXMGZ_BR(PZYID00,PBQH000,PCZY000,'1','1',COUNTER,VSYSDATE); END IF; end if; --删除拆份表纪录 DELETE FROM BQ_YPYZCF C WHERE YZID00 IN (SELECT YZID00 FROM BQ_YPYZ00 WHERE ZYID00=PZYID00 AND BQH000=PBQH000 ); --修改病人的流动信息 update bq_brldxx set bqjsrq=TO_CHAR(VSYSDATE,'YYYYMMDD') where zyid00=pzyid00 and bqjsrq='99991231' and qszt00 in ('新入','入院'); UPDATE BQ_BRLDXX SET JSCZY0=PCZY000, JSZT00=decode(QSZT00,'包床','退床','出院'), JSRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), JSSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), ZRYS00=ZRYS00, ZZYS00=PCYYS00 WHERE ZYID00=PZYID00 AND JSZT00 IS NULL; IF SQL%NOTFOUND THEN--没有发现信息 SELECT COUNT(1) INTO COUNTER FROM ZY_BRXXB0 WHERE ZYID00=PZYID00 AND BRZT00='23'; --请假 2008.11.13 qks INSERT INTO BQ_BRLDXX( ZYID00, CH0000, BQH000, KSH000, QSZT00, QSCZY0, QSZZYS, QSZRYS, QSRQ00, QSSJ00, JSCZY0, JSZT00, ZRYS00, ZZYS00, JSRQ00, JSSJ00) --VALUES (PZYID00, 0 , PBQH000, 0 , '入院',PCZY000, 0 , 0 , VALUES (PZYID00, VCH0000, PBQH000, VDQKS00, decode(sign(COUNTER),1,'续住','入院'),PCZY000, PCYYS00, PCYYS00, TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), PCZY000, '出院',PCYYS00,PCYYS00, TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS')); END IF; IF PQXZYBZ='Y' THEN DELETE FROM BQ_BRLDXX WHERE ZYID00=PZYID00; DELETE FROM BQ_BRZDXX WHERE ZYID00=PZYID00; END IF; --修改床位信息 UPDATE BM_CWK000 SET CWZT00='0',ZYID00=NULL,ZCH000=NULL WHERE ZYID00=PZYID00; --退还病人的押金 IF VCH0000 IS NOT NULL THEN UPDATE ZY_BRZHXX SET TYJE00=0 WHERE BRID00=Vbrid00 AND JFLBID=Vjflbid AND SFDJ00='0'; -- IF SQL%NOTFOUND THEN -- Verrmsg:='病人没有帐户'; -- RAISE Ecustom; -- END IF; END IF; --改医嘱的附加费用为无效 UPDATE BQ_FJZXXM F SET TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), TZR000=PCZY000 WHERE EXISTS (SELECT 1 FROM BQ_YPYZ00 Z WHERE F.YZID00=Z.YZID00 AND CSLY00 IN ('0','1')) AND F.BQSFBZ='Y' AND ZYID00=PZYID00; UPDATE BQ_FJZXXM F SET TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'), TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), TZR000=PCZY000 WHERE EXISTS (SELECT 1 FROM BQ_YJYZ00 Z WHERE F.YZID00=Z.YZMXID AND CSLY00 ='2') AND F.BQSFBZ='Y' AND ZYID00=PZYID00; DELETE XT_DZGG00 WHERE ZYID00=PZYID00; --把BQ_YZZXJL记录转移到备份表 insert into bq_yzzxjl_bk (yzmxid,yzid00,tqdjh0,djhlb0,qsrq00,qssj00,sjjsrq,sjjssj,jsrq00, jssj00,cs0000,fydjh0,zt0000,czrq00,czsj00,czr000,zxrq00,zxsj00,dybz00, zyid00,zxrxm0,scjsrq) select yzmxid,yzid00,tqdjh0,djhlb0,qsrq00,qssj00,sjjsrq,sjjssj,jsrq00, jssj00,cs0000,fydjh0,zt0000,czrq00,czsj00,czr000,zxrq00,zxsj00,dybz00, zyid00,zxrxm0,scjsrq from bq_yzzxjl where zyid00=PZYID00; --保留饮食医嘱 DELETE BQ_YZZXJL Z WHERE ZYID00=PZYID00 AND NOT EXISTS (SELECT 1 FROM BQ_YJYZ00 where YZMXID=Z.YZMXID and YZLB00=8); DELETE BQ_TQYCJL WHERE ZYID00=PZYID00; --修改住院病人信息表 SELECT YYQC00 INTO Vyyqc00 FROM XT_YYXX00 where rownum=1; IF Vyyqc00='福州肺科医院' THEN UPDATE ZY_BRXXB0 SET ZZYS00=PCYYS00, ZZYSXM=(select ZWXM00 from bm_ygbm00 where ygbh00=PCYYS00) WHERE ZYID00=PZYID00 AND SUBSTR(BRZT00,1,1)='2'; END IF; vXLXGMC:=''; If PZLXG00 Is Not Null Then Begin Select MC0000 Into vXLXGMC From XT_ZLXG00 Where DM0000=PZLXG00; Exception When Others Then vXLXGMC:=''; End; End If; UPDATE ZY_BRXXB0 SET BRZT00=DECODE(PQXZYBZ,'Y','1','3'), CYDJRQ=decode(CYDJRQ,null,TO_CHAR(TO_DATE(VCYDJRQSJ,'YYYYMMDDHH24:MI:SS'),'YYYYMMDD'),CYDJRQ), CYDJSJ=decode(CYDJRQ,null,TO_CHAR(TO_DATE(VCYDJRQSJ,'YYYYMMDDHH24:MI:SS'),'HH24:MI:SS'),CYDJSJ), SJCYRQ=decode(SJCYRQ,'20991231',substrb(VCYDJRQSJ,1,8),SJCYRQ), SJCYSJ=decode(SJCYSJ,NULL,TO_CHAR(sysdate,'HH24:MI:SS'),SJCYSJ), CYCZRQ=(select to_char(sysdate,'YYYYMMDD') from dual), --RCYFH0=(SELECT X.MC0000 FROM BQ_BRZDXX Z,XT_ZLXG00 X -- WHERE Z.ZLXG00=X.DM0000 AND ZYID00=PZYID00 AND Z.ZDLB00=3), RCYFH0=vXLXGMC, HLJBID=NULL WHERE ZYID00=PZYID00 AND SUBSTR(BRZT00,1,1)='2'; IF SQL%NOTFOUND THEN Verrmsg:='病人信息错误，请刷新后重试'; RAISE Ecustom; END IF; UPDATE ZY_BRXXB0 SET RCYFH0='好转' WHERE ZYID00=PZYID00 AND RCYFH0 IS NULL; update ZY_BRXXB0 SET ZKJCBZ='N' WHERE ZYID00=PZYID00 AND ZKJCBZ='Y'; DELETE BQ_YZCFMX where YZID00 in (select YZID00 from BQ_YPYZ00 where ZYID00=PZYID00) and YZLB00='0' and ZXRQ00<>to_char(sysdate,'YYYYMMDD'); DELETE BQ_YZCFMX where YZID00 in (select YZMXID from BQ_YJYZ00 where ZYID00=PZYID00) and YZLB00='1' and ZXRQ00<>to_char(sysdate,'YYYYMMDD'); COMMIT; EXCEPTION when Ecustom then raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,220)); when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||Verrmsg||Vparams,1,220)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220)); END SP_BQ_BRCYDJ; / -- End of DDL script for SP_BQ_BRCYDJ