-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YJ_GETBRXX -- Generated 14-十二月-2015 13:50:52 from SD_HOSPITAL@clyy CREATE OR REPLACE PROCEDURE sp_yj_getbrxx --过程,获取病人信息 --现金病人,有BRID,但BM_BRXXB0表中无记录. --不论什么情况,正在住院的病人,用住院处方,扣住院帐户, --其余的(包括住院出院的病人),用门诊处方,扣门诊帐户. --BM_BRXXB0记录了病人最后一次是门诊0还是住院1。0,从门诊表中查找病人信息,1从住院表中查找信息 -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.09.10 预挂号的病人不显示 -- CSF 2010.11.09 select YYID00 from XT_YYXX00 where rownum=1写法改成select SF_SF_GETYYID00 from dual -- liuj 2010.12.24 增加医技平台门诊病人住院病人操作权限控制 -- liuj 2010.12.28 增加体检病人在医技平台开单执行限制 -- liuj 2011.07.19 增加参数控制读卡时只显示门诊记录不要显示住院 by YJ-20110708-001 -- liuj 2012.03.19 增加参数控制住院病人余额是否取sf_bq_kyje00返回值 for YJ-20120307-001 -- liuj 2012.07.17 增加前台传入门诊/住院，来判断是要获取门诊还是要住院信息 for YJ-20120716-001 -- liuj 2012.12.18 bug住院病人读卡后病人信息没传出值 for YJ-20121217-002 -- csf 2013.01.17 bug:病人出院了默认的类别还是住院，病人出院了应该根据门诊的挂号情况默认门诊 for YJ-20130104-001 -- liuj 2103.12.17 bug:如果病人建卡后未有任何挂号记录，那么在有些读卡界面会变成住院病人，造成操作人员误解 for YJ-20131211-001 ( PBRID00 IN OUT NUMBER , --病人ID PMZZYBZ IN OUT varCHAR2 , --门诊住院标志'0'门诊'1'住院,说明了PZYGHID是住院ID或门诊ID PZYHGHH IN OUT varCHAR2 , --住院号或门诊号 PZYGHID OUT NUMBER , --住院ID或门诊ID PXM0000 OUT VARCHAR2 , --姓名 PXB0000 OUT VARCHAR2 , --性别 PCH0000 OUT VARCHAR2 , --床号 PYBLB00 OUT VARCHAR2 , --医保类别 PFBBH00 OUT VARCHAR2 , --费别编号 PFBMC00 OUT VARCHAR2 , --费别名称 PYBBRLB OUT VARCHAR2 , --医保病人类别 PYBMC00 OUT VARCHAR2 , --医保公费名称 PKSMC00 OUT VARCHAR2 , --最后挂(住)科室名称 PCSRQ00 OUT VARCHAR2 , --出生日期 PMZYE00 OUT NUMBER , --门诊帐户余额 PZYYE00 OUT NUMBER , --住院帐户余额 PBRNL00 OUT VARCHAR2 --年龄 ) AS Vzyzt BM_BRXXB0.ZYZT00%TYPE; Vyyid00 xt_yyxx00.yyid00%TYPE; E_ghhbcz EXCEPTION; --挂号号不存在 E_ghhbwy EXCEPTION; --挂号号不唯一 E_zyhbcz EXCEPTION; --住院号不存在 E_zyhbwy EXCEPTION; --住院号不唯一 E_myfhrhxx EXCEPTION; --没有返回任何信息 E_ghjlbcz EXCEPTION; --挂号记录不存在 E_zyjlbcz EXCEPTION;--住院记录不存在 E_mzczqx EXCEPTION;--无门诊操作权限 E_zyczqx EXCEPTION;--无住院操作权限 E_tjbrgh EXCEPTION; --体检病人挂号 LS_ERR CHAR(200); V_MAXGHID SF_BRXXB0.GHID00%TYPE;--最大挂号ID Vcounter NUMBER; Vcounter1 NUMBER; Vcounter2 NUMBER; Vcounter3 NUMBER; VGHH000 SF_BRXXB0.GHH000%TYPE; VZYH000 ZY_BRXXB0.ZYH000%TYPE; VYGBH00 BM_YGBM00.ygbh00%TYPE; v_ghlb sf_brxxb0.ghlb00%type; v_sfqyqxkz XT_XTCS00.VALUE0%TYPE; v_sfxztjbr XT_XTCS00.VALUE0%TYPE; v_sftjgh XT_XTCS00.VALUE0%TYPE; v_processid sys.V_$session.process%TYPE; v_programname SYS.V_$SESSION.program%TYPE; v_ZYBRYESFKYYE XT_XTCS00.VALUE0%TYPE; BEGIN --取参数 begin select VALUE0 into v_sfqyqxkz from xt_xtcs00 where name00='YJ_SFQYMZZYFKQXKZ'; EXCEPTION WHEN OTHERS THEN v_sfqyqxkz:='N'; end; begin select VALUE0 into v_sfxztjbr from xt_xtcs00 where name00='YJ_SFXZTJBRKDZX'; EXCEPTION WHEN OTHERS THEN v_sfxztjbr:='N'; end; begin select VALUE0 into v_sftjgh from xt_xtcs00 where name00='XT_TJGHLB'; EXCEPTION WHEN OTHERS THEN v_sftjgh:='N'; end; begin select VALUE0 into v_ZYBRYESFKYYE from xt_xtcs00 where name00='YJ_ZYBRYESFXSKYYE'; EXCEPTION WHEN OTHERS THEN v_ZYBRYESFKYYE:='N'; end; --进行员工操作权限判断 select sf_xt_hqygbh into VYGBH00 from dual; Select to_number(userenv('SESSIONID')) PROCESS into v_processid from dual where rownum=1; select program into v_programname from sys.V_$session where process=v_processid or AUDSID= v_processid; if v_programname='YJsystem.exe' and v_sfqyqxkz='Y' then select count(MKNBMC) into Vcounter1 from XT_XTMK00 where XTDM00='7' and MKDM00='805' and ( (BZ0000<>'0' and MKDM00 in (select MKDM00 from XT_ZBQX00 where XTDM00='7' and ZBDM00 in ( select ZBDM00 from VW_XT_YHZB00 where YGBH00=VYGBH00)))); if Vcounter1=0 and PMZZYBZ='0' then RAISE E_mzczqx; end if; select count(MKNBMC) into Vcounter2 from XT_XTMK00 where XTDM00='7' and MKDM00='806' and ( (BZ0000<>'0' and MKDM00 in (select MKDM00 from XT_ZBQX00 where XTDM00='7' and ZBDM00 in ( select ZBDM00 from VW_XT_YHZB00 where YGBH00=VYGBH00)))); if Vcounter2=0 and PMZZYBZ='1' then RAISE E_zyczqx; end if; end if; --输入的是挂号号或住院号，查找病人信息 IF (PZYHGHH IS NOT NULL) AND (PMZZYBZ='0' OR PMZZYBZ='1') THEN IF PMZZYBZ='0' THEN --门诊 VGHH000:=PZYHGHH; select count(BRID00) INTO Vcounter from SF_BRXXB0 where GHH000=VGHH000 and ghlb00<>-99999 AND ROWNUM=1; IF Vcounter=1 THEN LS_ERR:='8000:select * INTO * from SF_BRXXB0 S,BM_BMBM00 B,BM_BRFBB0 F'|| 'where trim(S.GHH000)='||PZYHGHH||' AND S.GHKS00=B.BMBH00(+) AND S.FBBH00=F.FBBH00(+)'; select S.BRID00,S.GHID00,S.ghlb00,S.XM0000,B.BMMC00,TO_CHAR(TO_DATE(S.CSRQ00,'YYYYMMDD'),'YYYY.MM.DD'),S.XB0000,NVL(F.FBMC00,'未知'),SF_BQ_CSRQTONL(S.CSRQ00) INTO PBRID00, PZYGHID, v_ghlb,PXM0000, PKSMC00, PCSRQ00, PXB0000, PFBMC00,PBRNL00 from SF_BRXXB0 S,BM_BMBM00 B,BM_BRFBB0 F where S.GHH000=VGHH000 AND S.GHKS00=B.BMBH00(+) AND S.FBBH00=F.FBBH00(+) and s.ghlb00<>-99999; if v_sfxztjbr='Y' and v_programname='YJsystem.exe' and v_ghlb=to_number(trim(v_sftjgh)) then raise E_tjbrgh; end if; select count(*) INTO Vcounter from BM_BRXXB0 where BRID00=PBRID00; IF Vcounter=0 THEN PYBLB00:='0'; PYBBRLB:='0'; PFBBH00:=1; ELSE LS_ERR:='8001:select YBZXLB INTO PYBLB00 from IC_YBBRLB where YBLB00 IN'|| '(select YBLB00 from BM_BRXXB0 where BRID00='||PBRID00||')'; select B.YBZXLB,A.YBBRLB, A.FBBH00,B.YBMC00 INTO PYBLB00,PYBBRLB,PFBBH00,PYBMC00 from BM_BRXXB0 a,IC_YBBRLB b where A.BRID00=PBRID00 AND B.YBLB00=a.YBLB00 and B.FBBH00=a.FBBH00; END IF; ELSIF Vcounter=0 THEN RAISE E_ghhbcz;--挂号号不存在 ELSE RAISE E_ghhbwy;--住院号挂号号不唯一 END IF; ELSIF PMZZYBZ='1' THEN --住院 VZYH000:=PZYHGHH; select count(BRID00) INTO Vcounter from ZY_BRXXB0 where ZYH000=VZYH000 AND ROWNUM=1; IF Vcounter=1 THEN LS_ERR:='8002:select * INTO * from ZY_BRXXB0 Z,BM_BMBM00 B,BM_BRFBB0 F'|| 'where ZYID00=(select MAX(ZYID00) from ZY_BRXXB0 where trim(ZYH000)='||PZYHGHH||' )'|| ' AND Z.DQBQ00=B.BMBH00(+) '|| ' AND Z.FBBH00=F.FBBH00(+)'; select Z.BRID00,Z.ZYID00,Z.XM0000,B.BMMC00,TO_CHAR(TO_DATE(CSRQ00,'YYYYMMDD'),'YYYY.MM.DD'),Z.XB0000,NVL(F.FBMC00,'未知'),RYCWH0,SF_BQ_CSRQTONL(CSRQ00) INTO PBRID00, PZYGHID,PXM0000, PKSMC00, PCSRQ00, PXB0000, PFBMC00,PCH0000,PBRNL00 from ZY_BRXXB0 Z,BM_BMBM00 B,BM_BRFBB0 F where ZYID00=(select MAX(ZYID00) from ZY_BRXXB0 where ZYH000=VZYH000 ) AND Z.DQBQ00=B.BMBH00(+) AND Z.FBBH00=F.FBBH00(+); select count(*) INTO Vcounter from BM_BRXXB0 where BRID00=PBRID00; IF Vcounter=0 THEN PYBLB00:='0'; PYBBRLB:='0'; PFBBH00:=1; ELSE LS_ERR:='8003 select YBZXLB INTO PYBLB00 from IC_YBBRLB where YBLB00 IN'|| '(select YBLB00 from BM_BRXXB0 where BRID00='||PBRID00||')'; select B.YBZXLB,A.YBBRLB, A.FBBH00,B.YBMC00 INTO PYBLB00,PYBBRLB,PFBBH00,PYBMC00 from BM_BRXXB0 a,IC_YBBRLB b where A.BRID00=PBRID00 AND B.YBLB00=a.YBLB00 and B.FBBH00=a.FBBH00; END IF; ELSIF Vcounter=0 THEN RAISE E_zyhbcz;--住院号不存在 ELSE RAISE E_zyhbwy;--住院号不唯一 END IF; END IF; ELSIF PBRID00>0 THEN --*********通过BRID00找最后一次的活动信息,BRID00为0的病人，没有任何信息********-------------------- if ((PMZZYBZ<>'0') and (PMZZYBZ<>'1')) or (PMZZYBZ is null) then PMZZYBZ:='1'; end if; select count(*) into Vcounter3 from xt_xtcs00 where name00='YJ_DKCZSFMRXSMZJL' and value0='Y'; select count(ZYZT00) INTO Vcounter from BM_BRXXB0 where BRID00=PBRID00 AND ROWNUM=1; LS_ERR:='8004 select YBZXLB INTO PYBLB00 from IC_YBBRLB where YBLB00 IN'|| '(select YBLB00 from BM_BRXXB0 where BRID00='||PBRID00||')'; select B.YBZXLB,A.YBBRLB, A.FBBH00,B.YBMC00 INTO PYBLB00,PYBBRLB,PFBBH00,PYBMC00 from BM_BRXXB0 a,IC_YBBRLB b where A.BRID00=PBRID00 AND B.YBLB00=a.YBLB00 and B.FBBH00=a.FBBH00; IF Vcounter=0 THEN --BM_BRXXB0中无记录,作门诊现金病人处理 Vzyzt:='0' ; --最后一次作门诊处理 PYBLB00:='0'; PYBBRLB:='0'; PFBBH00:=1; ELSE LS_ERR:='8005 select ZYZT00 INTO Vzyzt from BM_BRXXB0 where BRID00='||PBRID00; select ZYZT00 INTO Vzyzt from BM_BRXXB0 where BRID00=PBRID00; END IF; IF (Vzyzt='0') or (Vcounter3>0) or (PMZZYBZ='0') or (Vzyzt='3') THEN --查找最后一次挂号病人信息 select count(GHID00) INTO Vcounter from SF_BRXXB0 where BRID00=PBRID00 and ghlb00<>-99999 AND ROWNUM=1; IF Vcounter>=1 THEN--有记录 LS_ERR:='8006 select * INTO * '|| 'from SF_BRXXB0 S,BM_BMBM00 B,BM_BRFBB0 F '|| 'where S.GHID00=(select MAX(GHID00) from SF_BRXXB0 where BRID00='||PBRID00||')'|| ' AND S.GHKS00=B.BMBH00(+) '|| ' AND S.FBBH00=F.FBBH00(+)'; select count(*) INTO Vcounter from VW_YS_YXGH00 where BRID00=PBRID00 and ghlb00<>-99999; IF Vcounter>0 THEN select MAX(GHID00) INTO V_MAXGHID from VW_YS_YXGH00 where BRID00=PBRID00 and ghlb00<>-99999 and ghlb00<>'5'; ELSE select MAX(GHID00) INTO V_MAXGHID from SF_BRXXB0 where BRID00=PBRID00 and ghlb00<>-99999 and ghlb00<>'5'; END IF; --BEGIN --select MAX(GHID00) INTO V_MAXGHID from vw_ys_yxgh00 where brid00=PBRID00; --EXCEPTION --WHEN NO_DATA_FOUND THEN --select MAX(GHID00) INTO V_MAXGHID from SF_BRXXB0 where BRID00=PBRID00; --END; select S.GHH000,S.GHID00,S.ghlb00,S.XM0000,B.BMMC00,TO_CHAR(TO_DATE(S.CSRQ00,'YYYYMMDD'),'YYYY.MM.DD'),S.XB0000,NVL(F.FBMC00,'未知'),SF_BQ_CSRQTONL(S.CSRQ00) INTO PZYHGHH, PZYGHID, v_ghlb,PXM0000, PKSMC00, PCSRQ00, PXB0000, PFBMC00,PBRNL00 from SF_BRXXB0 S,BM_BMBM00 B,BM_BRFBB0 F where S.GHID00=V_MAXGHID --(select MAX(GHID00) from SF_BRXXB0 where BRID00=PBRID00) AND S.GHKS00=B.BMBH00(+) AND S.FBBH00=F.FBBH00(+) and s.ghlb00<>-99999; PMZZYBZ:='0'; if Vcounter1=0 and PMZZYBZ='0' and v_sfqyqxkz='Y' then RAISE E_mzczqx; end if; if v_sfxztjbr='Y' and v_programname='YJsystem.exe' and v_ghlb=to_number(trim(v_sftjgh)) then raise E_tjbrgh; end if; ELSE--挂号表没有记录(可能被删除) select count(ZYZT00) INTO Vcounter from BM_BRXXB0 where BRID00=PBRID00 AND ROWNUM=1; IF Vcounter>0 THEN --挂号表无记录，病人基本信息表有记录 LS_ERR:='8006 select * INTO *'|| 'from BM_BRXXB0 X,BM_BRFBB0 F'|| ' where X.BRID00='||PBRID00|| ' AND X.FBBH00=F.FBBH00(+)'; select X.BRXB00,X.BRXM00,TO_CHAR(TO_DATE(X.BRCSRQ,'YYYYMMDD'),'YYYY.MM.DD'),F.FBMC00,SF_BQ_CSRQTONL(X.BRCSRQ) INTO PXB0000,PXM0000,PCSRQ00,PFBMC00,PBRNL00 from BM_BRXXB0 X,BM_BRFBB0 F where X.BRID00=PBRID00 AND X.FBBH00=F.FBBH00(+); PMZZYBZ:='0'; ELSE RAISE E_ghjlbcz;--挂号记录不存在 END IF; END IF; ELSIF (Vzyzt='1') and (Vcounter3=0) and (PMZZYBZ='1') THEN--查找最后一次住院病人信息 select count(*) INTO Vcounter from ZY_BRXXB0 where BRID00=PBRID00 AND ROWNUM=1; IF Vcounter=1 THEN--有记录 LS_ERR:='8007 select '||'from ZY_BRXXB0 Z,BM_BMBM00 B,BM_BRFBB0 F '|| 'where Z.ZYID00=(select MAX(ZYID00) from ZY_BRXXB0 where BRID00='||PBRID00||')'|| ' AND Z.DQBQ00=B.BMBH00(+) '|| ' AND Z.FBBH00=F.FBBH00(+)'; select Z.ZYH000,Z.ZYID00,Z.XM0000,B.BMMC00,TO_CHAR(TO_DATE(Z.CSRQ00,'YYYYMMDD'),'YYYY.MM.DD'),Z.XB0000,NVL(F.FBMC00,'未知'),RYCWH0,SF_BQ_CSRQTONL(Z.CSRQ00) INTO PZYHGHH,PZYGHID,PXM0000, PKSMC00, PCSRQ00, PXB0000, PFBMC00,PCH0000,PBRNL00 from ZY_BRXXB0 Z,BM_BMBM00 B,BM_BRFBB0 F where Z.ZYID00=(select MAX(ZYID00) from ZY_BRXXB0 where BRID00=PBRID00) and Z.DQBQ00=B.BMBH00(+) AND Z.FBBH00=F.FBBH00(+); PMZZYBZ:='1'; if Vcounter2=0 and PMZZYBZ='1' and v_sfqyqxkz='Y' then RAISE E_zyczqx; end if; ELSE select count(BRID00) INTO Vcounter from BM_BRXXB0 where BRID00=PBRID00 AND ROWNUM=1; IF Vcounter>0 THEN LS_ERR:='8008 from BM_BRXXB0 X,BM_BRFBB0 F'||' where X.BRID00='||PBRID00||' AND X.FBBH00=F.FBBH00(+)'; select X.BRXB00,X.BRXM00,TO_CHAR(TO_DATE(X.BRCSRQ,'YYYYMMDD'),'YYYY.MM.DD'),F.FBMC00,SF_BQ_CSRQTONL(X.BRCSRQ) INTO PXB0000,PXM0000,PCSRQ00,PFBMC00,PBRNL00 from BM_BRXXB0 X,BM_BRFBB0 F where X.BRID00=PBRID00 AND X.FBBH00=F.FBBH00(+); ELSE RAISE E_zyjlbcz;--住院记录不存在 END IF; END IF; ELSE--查找最后一次非住院非门诊病人信息 ls_err:='8009 from bm_brxxb0 x,bm_brfbb0 f'||' where x.brid00=pbrid00'||' and x.fbbh00=f.fbbh00(+)'; select X.BRXB00,X.BRXM00,TO_CHAR(TO_DATE(X.BRCSRQ,'YYYYMMDD'),'YYYY.MM.DD'),F.FBMC00,SF_BQ_CSRQTONL(X.BRCSRQ) INTO PXB0000,PXM0000,PCSRQ00,PFBMC00,PBRNL00 from BM_BRXXB0 X,BM_BRFBB0 F where X.BRID00=PBRID00 AND X.FBBH00=F.FBBH00(+); END IF; ELSE RAISE E_myfhrhxx;--没有返回任何信息 END IF; IF PBRID00<>0 THEN--找出病人的帐户 --取医院ID --select yyid00 into Vyyid00 from xt_yyxx00 where ROWNUM=1; select SF_SF_GETYYID00 into Vyyid00 from dual; --查找门诊账户 select count(YKZYE0) INTO Vcounter from SF_BRZHXX where ROWNUM=1 AND BRID00=PBRID00 AND JFLBID IN (select JFLBID from BM_BRJFLB where SYBZ00='0')AND SFDJ00='0'; IF Vcounter=0 THEN PMZYE00:=0; ELSE select YKZYE0 INTO PMZYE00 from SF_BRZHXX where BRID00=PBRID00 AND JFLBID IN (select JFLBID from BM_BRJFLB where SYBZ00='0')AND SFDJ00='0'; END IF; --查找住院病人信息 select count(YKZYE0) INTO Vcounter from ZY_BRZHXX where ROWNUM=1 AND BRID00=PBRID00 AND JFLBID IN (select JFLBID from BM_BRJFLB where SYBZ00='1')AND SFDJ00='0' ; IF Vcounter=0 THEN PZYYE00:=0; ELSE IF (Vyyid00='221678') or (v_ZYBRYESFKYYE='Y') then --福州闽侯县二院 or (v_ZYBRYESFKYYE='Y') PZYYE00:=sf_bq_kyje00(0,PBRID00); ELSE select YKZYE0 INTO PZYYE00 from ZY_BRZHXX where BRID00=PBRID00 AND JFLBID IN (select JFLBID from BM_BRJFLB where SYBZ00='1')AND SFDJ00='0' ; END IF; END IF; ELSE PMZYE00:=0; PZYYE00:=0; END IF; EXCEPTION WHEN E_zyjlbcz THEN RAISE_APPLICATION_ERROR(-20001,'住院记录不存在！*'); WHEN E_ghjlbcz THEN RAISE_APPLICATION_ERROR(-20002,'挂号记录不存在！*'); WHEN E_myfhrhxx THEN RAISE_APPLICATION_ERROR(-20003,'没有返回任何信息！*'); WHEN E_zyhbcz THEN RAISE_APPLICATION_ERROR(-20004,'住院号不存在！*'); WHEN E_zyhbwy THEN RAISE_APPLICATION_ERROR(-20005,'住院号不唯一！*'); WHEN E_ghhbcz THEN RAISE_APPLICATION_ERROR(-20004,'挂号号不存在！*'); WHEN E_ghhbwy THEN RAISE_APPLICATION_ERROR(-20005,'挂号号不唯一！*'); WHEN E_mzczqx THEN RAISE_APPLICATION_ERROR(-20006,'无门诊操作权限！*'); WHEN E_zyczqx THEN RAISE_APPLICATION_ERROR(-20006,'无住院操作权限！*'); WHEN E_tjbrgh THEN RAISE_APPLICATION_ERROR(-20007,'体检病人不能再医技系统开单及执行！*'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'||LS_ERR); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*')); END SP_YJ_GETBRXX; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_YJ_GETBRXX