-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SST_GETZYBRJBXX
-- Generated 8-四月-2016 11:00:24 from SD_HOSPITAL@clyy

CREATE OR REPLACE 
PROCEDURE sp_sst_getzybrjbxx(
  P_ZDBH00     in varchar2,  --终端编号
  P_YYJGDM     in varchar2,  --医院机构代码
  P_CARDTYPE   in varchar2,  --卡类型
  P_CARDNO     in varchar2,  --卡号(输入住院号卡号为0)
  P_INZYH0     in varchar2,  --住院号(读卡时住院号为0)
  P_XMING0     out varchar2, --出参姓名
  P_XBIE00     out varchar2, --性别
  P_CSRQ00     out varchar2, --出生日期
  P_LXDH00     out varchar2, --联系电话
  P_DQBQMC     out varchar2, --所在病区
  P_DQKSMC     out varchar2, --所在科室
  P_RYCWH0     out varchar2, --所在床号
  P_ZYH000     out varchar2, --住院号
  P_YJJYE0     out number,   --预交金余额
  P_FZXBH0     out varchar2, --分中心编号
  P_BYCC00     out varchar2,  --备用出参
  P_ERRMSG     out varchar2 --错误信息，空表示正确
  
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2012.10.25  create
-- zhangyc     2014.01.01  重新整理过程
-- ---------   ----------  -------  
LS_BRID00  BM_BRXXB0.BRID00%type; --病人ID
LS_CARDNO  IC_YHXX00.ICKH00%type; --IC卡号
LS_ZT0000  IC_YHXX00.ZT0000%type; --IC卡状态
ls_YBMC00  IC_YBBRLB.YBMC00%type; --医保名称
ls_YBZXLB  IC_YBBRLB.ybzxlb%type;
ls_YBLB00  IC_YBBRLB.yblb00%type;
ls_TYJE00  SF_BRZHXX.TYJE00%TYPE; --账户停用金额
ls_SFDJ00  SF_BRZHXX.SFDJ00%TYPE; --账户冻结标志
LS_FBBH00  BM_BRXXB0.FBBH00%TYPE;
ls_YHMSG0  varchar2(200);
LS_ICKQZMC VARCHAR2(50);
LS_CarNum number(5); --卡号位数
Lv_ZYE000  number(12,4);
begin
 if nvl(P_INZYH0,'0')='0' then	--读卡操作
 select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual; 
	 begin
	    select BRID00,ZT0000 into LS_BRID00,LS_ZT0000 from IC_YHXX00 where  ICKH00 =LS_CARDNO and ZT0000=1;
	    exception
		  when no_data_found then
        P_ERRMSG:='IC卡号:'||LS_CARDNO||'在本院IC_YHXX00表中未找到,请联系管理员!';
        return;
	  end;
 else  --按住院号查询
 	 begin
 	   select BRID00 into LS_BRID00  from  ZY_BRXXB0 a where ZYH000=rpad(P_INZYH0,12,' ')
 	       and CYRQ00 is null and substr(brzt00,1,1) in ('1','2','3','8');   
	 exception
		  WHEN NO_DATA_FOUND THEN
        P_ERRMSG:='住院号:'||P_INZYH0||'在本院未找到相应信息，请确认住院号是否正确或该病人是否在院!';
        return;
	  end; 
 end if;
  
  select nvl(TYJE00,0),nvl(SFDJ00,0),nvl(ZYE000,0) into LS_TYJE00,LS_SFDJ00,Lv_ZYE000 from ZY_BRZHXX where BRID00=LS_BRID00 and JFLBID=2;
  if LS_SFDJ00<>0 then
  	  P_ERRMSG:='该病人在本院的账户已被冻结或停用,请联系管理员!';
  	  return;
   end if;
   P_YJJYE0:=Lv_ZYE000-LS_TYJE00;
  select A.BRXM00,A.BRXB00,A.BRCSRQ,C.YBMC00,A.BRDH00,A.FBBH00,C.YBZXLB,C.YBLB00,
  B.ZYH000,B.RYCWH0,
  (Select aa.BMMC00 from bm_bmbm00 aa where aa.bmbh00=B.DQBQ00 and rownum=1)DQBQMC,
  (Select bb.BMMC00 from bm_bmbm00 bb where bb.bmbh00=B.DQKS00 and rownum=1)DQKSMC
      into P_XMING0,P_XBIE00,P_CSRQ00,LS_YBMC00,P_LXDH00,LS_FBBH00,LS_YBZXLB,LS_YBLB00,
      P_ZYH000,P_RYCWH0,P_DQBQMC,P_DQKSMC
  from BM_BRXXB0 A,IC_YBBRLB C,ZY_BRXXB0 B WHERE A.FBBH00 =C.FBBH00 and A.YBLB00=C.YBLB00
  and A.BRID00=B.BRID00 and A.BRID00=LS_BRID00;
 
  if LS_FBBH00=3 then
    select YBMC00 into ls_YBMC00 from ic_ybbrlb  where ybzxlb=ls_ybzxlb and yblb00=ybzxlb and rownum=1;
  end if;
  
   --两费 350191  保健350192  省医保350000  市医保350100
    if instrb(ls_YBMC00,'省医保')>0 then
    P_FZXBH0:='350000';
  elsif instrb(ls_YBMC00,'市医保')>0 then
    P_FZXBH0:='350100';
  elsif instrb(ls_YBMC00,'保健')>0 then
    P_FZXBH0:='350192';
  elsif instrb(ls_YBMC00,'两费')>0 then
    P_FZXBH0:='350191';
  else
    P_FZXBH0:='';
  end if; 
 if LS_FBBH00=3 then 
   P_BYCC00:='医保';
 else
   P_BYCC00:='自费';
 end if;   

exception
    when NO_DATA_FOUND then
      P_ERRMSG:='未找到数据！'||SQLERRM;
    when others then
      P_ERRMSG:='发生未知错误'||SQLERRM;
END;
/

-- End of DDL Script for Procedure SD_HOSPITAL.SP_SST_GETZYBRJBXX