CREATE OR REPLACE PROCEDURE SP_BQ_SZBR ( PZYH000 IN char , --住院号 PCH0000 IN char , --床号 PZZYS00 IN number , --主治医生 PZRYS00 IN number , --主任医生 PSXYS00 IN number , --实习医生 PCZY000 IN number , --收治操作护士 PHLJBID IN number , --护理级别ID PBQH000 IN number , --收治病区 PSZKS00 IN number , --所在科室 PRYZD00 In char , --入院诊断 PYSZID0 IN number default null, --医生组ID PZGYS00 IN number default null, --主管医生 PYKSH00 IN number default 0, --原科室号 PSFJC00 IN varchar2 default 'N', --是否借床 PYBQH00 IN number default 0 , --新入病人，可能直接入到ICU病区, PRKSJ00 IN char default '', --入科时间 PZRHS00 in number default 0, --责任护士 PALLDOC in number default 0 --是否显示所有医生 ) AS counter NUMBER(3); VCZYXM0 BM_YGBM00.ZWXM00%TYPE; Vzyid00 ZY_BRXXB0.ZYID00%TYPE; Vbrid00 ZY_BRXXB0.BRID00%TYPE; --病人ID Vrylb00 ZY_BRXXB0.RYLB00%TYPE; --入院类别 Vjflbid BM_BRJFLB.JFLBID%TYPE; --住院帐户类别ID Vksh000 BM_BMBM00.BMBH00%TYPE; --病人所在科室 Vyksh00 BM_BMBM00.BMBH00%TYPE; --病人原来所在科室 vybqh00 BM_BMBM00.BMBH00%TYPE; --病人原来所在病区 vsficu0 BM_BMBM00.SFICU0%TYPE; --是否icu标志 Vpcid00 BM_PC0000.PCID00%TYPE; --每天一次的频次ID Vpcmc00 BM_PC0000.PCMC00%TYPE; --每天一次的频次名称 Vbrlb00 CHAR(4); --病人状态类别，新入，转出，转入，出院等 Vfbbh00 BM_BRFBB0.FBBH00%TYPE; --病人的费别编号 V_YBBRSFKCZ XT_XTCS00.VALUE0%TYPE; --是否咳产生费用项目 V_JCYKSHSFQLDXX XT_XTCS00.VALUE0%TYPE; --参数by BQ-20120210-001 E_BRZTYJGB EXCEPTION ; --病人状态已经改变 E_MYQD EXCEPTION ; --没有每天一次的频次 E_BCZZSY EXCEPTION ; --病床正在使用中 E_BRMYZH EXCEPTION ; --病人可能没有帐户 E_BRYJBZ EXCEPTION ; --病人押金不足 E_ZHLBCSCW EXCEPTION ; --帐户类别参数错误 E_FBBQCW EXCEPTION ; --非本病区床位 E_RKSJ exception ; --入科时间 E_BRZRHS exception ; --责任护士 VPARAMS VARCHAR2(255); VSYSDATE DATE; vICD900 XT_ICD900.ICD900%Type; --入院诊断信息 Vyyszid zy_brxxb0.yyszid%type; --原医生组ID Vyrybq0 ZY_BRXXB0.RYBQ00%Type; --原入院病区 Vyrzks0 ZY_BRXXB0.RZKS00%Type; --原入院科室 v_jcbz00 bq_brldxx.jcbz00%type; --借床标志 v_ICUSZBQ varchar2(50); --ICU可以强制收治的病区号 bm_bmbm00.bmbh00 如果多个可以用逗号隔开 默认为-1，表示不能强制收治 LS_ERROR varchar2(200); E_CUSTOM exception; Cursor C_YZMXID is select YZMXID from BQ_YJYZ00 where ZYID00=Vzyid00 and yzzt00='0'; --2007-06-18 qks 漳州市漳浦县中医院:病人转病区收治时，[收治默认项目维护]中的零星收费项目不自动带出！ --2007-08-10 qks 取消南平邵武人民医院，碰到住院诊查费，日期往后一天 --2007-10-24 dsm 转入ICU的要记录原来病区和科室 --2007-12-16 YANGY 入院记录医生组 --2008.05.09 YANGY 增加主管医生 --2008.06.27 YANGY 病人从其他病区转入ICU病区，记录原科室原医生组 --2009.04.08 yangy 借床问题 --2008.04.30 YANGY 增加原病区号 --2009.07.18 yangy ICU病区可以强行收治别的科室的病人。 --2009.07.24 yangy 借床病人收治后，zkjcbz要改为N。 --2009.10.20 zhr 新入收治时如果有填写入院诊断，应该要写入BQ_BRZDXX,同时修改RYRQ00 --2009.10.25 yangh 借床病人JCYKSH0,JCYBQH0根据流动信息表获取 --2009.11.10 yangh 借床病人，可以被原病区收回 --2009.12.30 qks 新入收治时修改ZY_BRXXB0.RYSJ00 --2012.02.10 liuj 加新参数控制借床病人JCYKSH0,JCYBQH0是否根据流动信息表获取 by BQ-20120210-001 --2013.11.21 dsm 对BQ_ZDCSXM表数据的处理改成调用 SP_BQ_CLZDCSXM for BQ-20131120-003 --2014.04.29 dsm BQ_JZXM00,BQ_YJYZ00医生组写入 for BQ-20140505-002 --2015.01.26 dsm 增加参数PZRHS00,PRKSJ00 for BQ-20150113-001 --2017.07.04 zhengzk增加入参PALLDOC，当医生收治病人修改医生组及医生的状态为在用状态for BQ-20170619-001 --2019.07.23 dsm 增加判断病人新入状态一定要有一条有效的记录 BQ-20190723-001 --2019.09.21 dsm 判断BQ_BRLDXX状态不一致修正 for BQ-20190921-001 BEGIN VSYSDATE:=SYSDATE; Vyrybq0 := NULL; Vyrzks0 := NULL; --本过程暂时未考虑预约病人的情况1.18 --取住院帐户类别ID VPARAMS:='SP_BQ_SZBR('||PZYH000||','||PCH0000||','||TO_CHAR(PZZYS00)||','||TO_CHAR(PZRYS00) ||','||TO_CHAR(PSXYS00)||','||TO_CHAR(PCZY000)||','||TO_CHAR(PHLJBID)||','||TO_CHAR(PBQH000)||')'; SELECT COUNT(*) INTO COUNTER FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y'; IF COUNTER<>1 THEN RAISE E_ZHLBCSCW;--帐户类别参数错误 RETURN; ELSE SELECT JFLBID INTO VJFLBID FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y'; END IF; SELECT COUNT(*) INTO COUNTER FROM BM_CWK000 WHERE CH0000=PCH0000 AND BQH000<>PBQH000; IF COUNTER>0 THEN RAISE E_FBBQCW;--非本病区床位 END IF; select nvl(max(trim(value0)),'-1') into v_ICUSZBQ from xt_xtcs00 where name00='BQ_ICUKYQZSZBQ'; --获取病人的住院ID,病人ID,费别(医保等) SELECT MAX(ZYID00) INTO Vzyid00 FROM ZY_BRXXB0 WHERE ZYH000=PZYH000; SELECT BRID00,RYLB00 INTO Vbrid00,Vrylb00 FROM ZY_BRXXB0 WHERE ZYID00=Vzyid00; SELECT FBBH00 INTO Vfbbh00 FROM BM_BRXXB0 WHERE BRID00=Vbrid00; SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=PCZY000; BEGIN SELECT VALUE0 INTO V_JCYKSHSFQLDXX FROM XT_XTCS00 WHERE NAME00='BQ_JCBRYKSHSFQLDBRXXB'; EXCEPTION WHEN OTHERS THEN V_JCYKSHSFQLDXX:='Y'; END; IF PSFJC00='Y' then if V_JCYKSHSFQLDXX='N' then SELECT RYBQ00,RZKS00 INTO Vyrybq0,Vyrzks0 FROM ZY_BRXXB0 WHERE ZYID00 = Vzyid00; else SELECT BQH000,KSH000 INTO Vyrybq0,Vyrzks0 FROM BQ_BRLDXX D WHERE D.JSZT00='转出' AND ZYID00 = Vzyid00 AND D.QSRQ00||D.QSSJ00=(SELECT MAX(QSRQ00||QSSJ00) FROM BQ_BRLDXX X WHERE X.ZYID00=Vzyid00) AND ROWNUM=1; end if; END IF; select count(*) into counter from XT_YYXX00 where YYJC00 in ('南平市第一医院','福建省龙岩人民医院'); if counter>0 and nvl(PZRHS00,0)=0 then RAISE E_BRZRHS;--责任护士不能为空 RETURN; end if; --判定病人是否有帐户 SELECT COUNT(*) INTO counter FROM ZY_BRZHXX WHERE BRID00=Vbrid00 AND JFLBID=Vjflbid AND SFDJ00='0'; IF counter=0 THEN RAISE E_BRMYZH;--病人可能没有帐户 RETURN; END IF; if PSFJC00='N' then SELECT COUNT(*) INTO counter FROM VW_BQ_DDSZBR WHERE ZYH000=PZYH000 AND (BQH000=PBQH000 or (instrb(','||v_ICUSZBQ||',',','||to_char(BQH000)||',')>0 AND BRLB00='新入病人')); else SELECT COUNT(*) INTO counter FROM VW_BQ_DDSZBR WHERE ZYH000=PZYH000 AND (( brlb00='新入病人' and BQH000<>PBQH000) or (brlb00 in('转入病人','转出病人') and zkjcbz='Y' and BQH000=PBQH000)); end if; --2008.10.16 sechen 借床 加 and PSFJC00='N' 借床状态改变允许通过 IF counter=0 THEN RAISE E_BRZTYJGB;--病人状态已经改变 RETURN; ELSE --2008.10.16 sechen 病人借床待添加(借床病人，把'新入'换'借床') IF PSFJC00='Y' then SELECT SUBSTRB(BRLB00,1,4),KSH000 INTO Vbrlb00, Vksh000 FROM VW_BQ_DDSZBR WHERE ZYH000=PZYH000 AND (( brlb00='新入病人' and BQH000<>PBQH000) or (brlb00 in('转入病人','转出病人') and zkjcbz='Y' and BQH000=PBQH000)); v_jcbz00 := '1'; ElSE SELECT SUBSTRB(BRLB00,1,4),KSH000 INTO Vbrlb00, Vksh000 FROM VW_BQ_DDSZBR WHERE ZYH000=PZYH000 AND (BQH000=PBQH000 or (instrb(','||v_ICUSZBQ||',',','||to_char(BQH000)||',')>0 AND BRLB00='新入病人')); --UPDATE VIEW v_jcbz00 := null; END IF; END IF; if nvl(PRKSJ00,' ')<>' ' and Vbrlb00='新入' then if PRKSJ00>to_char(sysdate,'YYYYMMDDHH24:MI:SS') then --不能大于当前时间 raise E_RKSJ; end if; if Vbrlb00='新入' then --入科时间不能小于入院时间 select count(*) into counter from ZY_BRXXB0 where ZYH000=PZYH000 and RYRQ00||RYSJ00>PRKSJ00 ; if counter>0 then raise E_RKSJ; end if; end if; begin VSYSDATE:=to_date(PRKSJ00,'YYYYMMDDHH24:MI:SS'); exception when others then raise E_RKSJ; end; end if; --检查床位是否在使用 SELECT COUNT(*) INTO counter FROM BM_CWK000 WHERE CWZT00='0' AND CH0000=PCH0000; IF counter=0 THEN RAISE E_BCZZSY; --病床正在使用中 RETURN; END IF; --如果医保病人，且ybbrsfkcz='1'不产生费用项目 BEGIN SELECT VALUE0 INTO V_YBBRSFKCZ FROM XT_XTCS00 WHERE NAME00='BQ_YBBRSFKCZ'; EXCEPTION WHEN OTHERS THEN V_YBBRSFKCZ:='0'; END; if Vrylb00='生育' then V_YBBRSFKCZ:='0'; end if; IF NOT(V_YBBRSFKCZ='1' AND Vfbbh00=3) THEN --产生床位收费项目 SELECT COUNT(*) INTO counter FROM VW_BQ_CWJM00 WHERE CWDJ00=(SELECT CWDJ00 FROM BM_CWK000 WHERE CH0000=PCH0000); IF counter>0 THEN INSERT INTO BQ_JZXM00(ID0000,ZYH000,ZYID00,CLBZ00,XMLB00,JFLBID,SFXMID,CS0000,XMMC00, DW0000,SFJE00,LRRXM0,QSRQ00,FBBH00,CH0000,JJSFBZ,LYBZ00,YSZID0) SELECT SQ_BQ_JZXM00_ID0000.NEXTVAL,PZYH000,Vzyid00, 0, '1',Vjflbid,C.SFXMID,SFCS00,S.XMMC00, S.DW0000,NVL(S.SFJE00,0),VCZYXM0,TO_CHAR(VSYSDATE,'YYYYMMDD'),Vfbbh00,PCH0000,JJSFBZ,'1',PYSZID0 FROM VW_BQ_CWJM00 C,BM_YYSFXM S WHERE C.SFXMID=S.SFXMID AND C.CWDJ00=(SELECT CWDJ00 FROM BM_CWK000 WHERE CH0000=PCH0000); END IF; --修改病人押金 UPDATE ZY_BRZHXX SET TYJE00=(SELECT TYJE00+NVL(CWYJ00,0) FROM BM_CWK000 WHERE CH0000=PCH0000) WHERE BRID00=Vbrid00 AND JFLBID=Vjflbid AND SFDJ00='0'; IF SQL%NOTFOUND THEN RAISE E_BRMYZH;--病人可能没有帐户 RETURN; END IF; END IF; --添加病人流动信息 INSERT INTO BQ_BRLDXX (ZYID00,CH0000,BQH000,KSH000,QSZT00,QSCZY0,QSZZYS,QSZRYS,QSRQ00,QSSJ00,ZYYS00,YSZID0,ZGYS00,JCBZ00,JCYBQH,JCYKSH,ZRHS00) VALUES (Vzyid00,PCH0000,PBQH000,PSZKS00,DECODE(trim(Vbrlb00),'转出','转入','出院','入院',Vbrlb00), PCZY000,PZZYS00,PZRYS00,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'),PSXYS00, PYSZID0,PZGYS00,v_jcbz00,Vyrybq0,Vyrzks0,PZRHS00); if Vbrlb00='转入' or Vbrlb00='转出' then if Vbrlb00='转出' then--如果是本区转出又马上收回的，变更状态为"换出"和"换入",以免影响转入出人数统计 select count(*) into counter from BQ_BRLDXX where ZYID00=Vzyid00 and JSZT00='转出' and JSRQ00 is null ; if counter>0 then update BQ_BRLDXX set QSZT00='换入' where ZYID00=Vzyid00 and CH0000=PCH0000 and QSZT00='转入' AND JSZT00 IS NULL; end if; end if; Update BQ_BRLDXX set JSRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),JSSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS'), JSZT00=decode(QSZT00,'包床','退床',decode(BQH000,PBQH000,'换出','转出')) where ZYID00=Vzyid00 and JSZT00='转出' and JSRQ00 is null; end if; select count(*) into counter from BQ_BRLDXX where ZYID00=Vzyid00 and QSZT00='新入' and YXBZ00 in('1','2'); if counter<1 then LS_ERROR:='病人流动信息错误！*'; RAISE E_CUSTOM;--病人流动信息错误 RETURN; end if; --如果是转入ICU的修改原病区,原科室 --取icu标志 select SFICU0 into vsficu0 from BM_BMBM00 WHERE BMBH00=PBQH000; --取原病区,原科室 if NVL(PYBQH00,0) <> 0 and NVL(PYKSH00,0) <> 0 then vybqh00 := PYBQH00; Vyksh00 := PYKSH00; else begin select KSH000,BQH000,YSZID0 INTO Vyksh00,vybqh00,Vyyszid from BQ_BRLDXX A WHERE ZYID00=Vzyid00 AND QSRQ00||QSSJ00=(SELECT MAX(QSRQ00||QSSJ00) FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND JSZT00='转出' and (not JSZT00 is null) ); exception when no_data_found then Vyksh00:=0; vybqh00:=0; Vyyszid:=NULL; end; end if; if vsficu0='1' then update BQ_BRLDXX SET YBQH00=vybqh00,YKSH00=vyksh00 WHERE ZYID00=Vzyid00 and JSRQ00 is null; end if; --修改床位信息 UPDATE BM_CWK000 SET CWZT00='1',ZYID00=Vzyid00 WHERE CH0000=PCH0000; --修改病人信息表 UPDATE ZY_BRXXB0 SET DQBQ00=PBQH000, DQKS00=PSZKS00, ZZYS00=PZZYS00, ZRYS00=PZRYS00, SXYS00=PSXYS00, RYCWH0=PCH0000, CYDJRQ='20991231',--重复收治的病人，出院登记日期不能2004.09.16以第一次的为准 CYDJSJ=NULL, ZZYSXM=(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=PZZYS00), ZRYSXM=(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=PZRYS00), SXYSXM=(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=PSXYS00), BRZT00='20', --2009.10.20 zhr RYRQ00=decode(Vbrlb00,'新入',to_char(sysdate,'YYYYMMDD'),RYRQ00), --2009.12.30 qks RYSJ00=decode(Vbrlb00,'新入',to_char(VSYSDATE,'HH24:MI:SS'),RYSJ00), YSZID0=PYSZID0, ZGYS00=PZGYS00, JCYBQH=Vyrybq0, --原病区值，有借床的有值，否则为空值 JCYKSH=Vyrzks0, --原科室值，有借床的有值，否则为空值 zkjcbz='N', ZRHS00=PZRHS00 WHERE ZYID00=Vzyid00; IF SQL%NOTFOUND THEN RAISE NO_DATA_FOUND; END IF; IF Vbrlb00='转入' or Vbrlb00='转出' THEN UPDATE ZY_BRXXB0 SET YKSH00=DECODE(vsficu0,'1',Vyksh00,NULL), YBQH00=DECODE(vsficu0,'1',vybqh00,NULL), YYSZID=DECODE(vsficu0,'1',Vyyszid,NULL) WHERE ZYID00=Vzyid00; elsif (Vbrlb00='新入') and (vsficu0='1') and (Vyksh00 is not null) then UPDATE ZY_BRXXB0 SET YKSH00=Vyksh00, YBQH00=vybqh00 WHERE ZYID00=Vzyid00; END IF; IF NOT(V_YBBRSFKCZ='1' AND Vfbbh00=3) THEN IF Vbrlb00='转入' or Vbrlb00='转出' THEN SELECT COUNT(*) INTO counter FROM xt_yyxx00 where YYQC00='漳州市漳浦县中医院'; ELSE counter:=0; END IF; --2013.11.21 SP_BQ_CLZDCSXM(Vzyid00,PBQH000, PSXYS00, PSZKS00,PCZY000,counter); IF (PHLJBID IS NOT NULL) AND (PHLJBID>0 ) THEN --作为医技项目插入到医技项目表中 --INSERT INTO BQ_YJ()VALUES(); UPDATE ZY_BRXXB0 SET HLJBID=PHLJBID WHERE ZYID00=VZYID00; INSERT INTO BQ_YJYZ00 ( YZMXID, ZYID00, YZLB00, CLBZ00, ZLXMID, ZLXMJC, ZXKS00, LRRQ00, LRSJ00, LRRXM0, QYRQ00, QYSJ00, TS0000, BQH000, KSH000, PCID00, PCMC00, YZZT00, TQFS00, YZCLLB, KZYS00, PXSJ00,YSZID0) SELECT SQ_BQ_YZMX00_YZMXID.NEXTVAL,Vzyid00,1,0,PHLJBID,Z.ZLXMJC, PSZKS00,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), VCZYXM0,TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'), NULL, PBQH000, PSZKS00, 0 ,' ', '0','0','正常',PSXYS00,TO_CHAR(VSYSDATE,'HH24:MI:SS'),PYSZID0 FROM VW_BM_ZLZD00 Z WHERE Z.ZLXMID=PHLJBID; END IF; for yz in C_YZMXID loop SP_BQ_YJFJFY(yz.YZMXID,0); end loop; END IF; --收治病人的入院诊断 If PRYZD00 IS NOT NULL and Vbrlb00='新入' Then Begin Select ICD900 Into vICD900 From XT_ICD900 Where trim(JBMC00) like trim(PRYZD00) And ROWNUM=1; Exception WHEN OTHERS THEN vICD900 := '0'; End; insert into BQ_BRZDXX(ID0000, BRID00, ZYID00, ZDLB00, ZDM000,ZDMC00, ZDRQ00, ZDSJ00, SFYZ00, ZLXG00, LRR000) Values(SQ_BQ_BRZDXX_ID0000.NextVal, vBRID00, vZYID00, '2',vICD900,PRYZD00, TO_CHAR(Sysdate,'YYYYMMDD'),TO_CHAR(Sysdate,'HH24:MI:SS'),0, 6, PCZY000); End If; if PALLDOC = 0 then begin update ZS_YSZ000 set YSZZT0 = '2' where YSZID0 = PYSZID0; update ZS_YSZMX0 set YSZT00 = '2' where YSZID0 = PYSZID0 and ( YSBH00 = PSXYS00 or YSBH00 = PZZYS00 or YSBH00 = PZRYS00 or YSBH00 = PZGYS00 ); end; end if; COMMIT; EXCEPTION WHEN E_BRZTYJGB THEN RAISE_APPLICATION_ERROR(-20001,'病人状态已经改变！*'||VPARAMS); WHEN E_BRMYZH THEN RAISE_APPLICATION_ERROR(-20002,'病人没有帐户！*'||VPARAMS); WHEN E_BRYJBZ THEN RAISE_APPLICATION_ERROR(-20003,'病人押金不足！*'||VPARAMS); WHEN E_BCZZSY THEN RAISE_APPLICATION_ERROR(-20004,'病床正在使用中！*'||VPARAMS); WHEN E_MYQD THEN RAISE_APPLICATION_ERROR(-20005,'没有每天一次的频次！*'||VPARAMS); WHEN E_ZHLBCSCW THEN RAISE_APPLICATION_ERROR(-20006,'帐户类别参数错误！*'||VPARAMS); WHEN E_FBBQCW THEN RAISE_APPLICATION_ERROR(-20007,'非本病区床位，停止收治！*'||VPARAMS); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'||VPARAMS); when E_RKSJ then RAISE_APPLICATION_ERROR(-20011, '入科时间错误,不能小于入院登记时间,不能大于当前时间！*'); when E_BRZRHS then RAISE_APPLICATION_ERROR(-20011, '责任护士不能为空！*'); when E_CUSTOM then RAISE_APPLICATION_ERROR(-20012, LS_ERROR); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,220)); ROLLBACK; END SP_BQ_SZBR;