CREATE OR REPLACE PROCEDURE SP_SST_GETYYGRJBXX_YKT(
  P_ZDBH00     in varchar2,  --终端编号
  P_YYJGDM     in varchar2,  --医院机构代码
  P_CARDTYPE   in varchar2,  --卡类型
  P_CARDNO     in varchar2,  --卡号
  P_BYRC00     in varchar2,  --备用入参
  P_XMING0     out varchar2, --姓名
  P_YJJYE0     out number,   --预交金余额
  P_XBIE00     out varchar2, --性别(0男1女)-->(1:男；2：女)
  P_CSRQ00     out varchar2, --出生日期
  P_FZXBH0     out varchar2, --分中心编号
  P_LXDH00     out varchar2, --联系电话
  P_BRBLH0     out varchar2, --病例号
  P_BYCC00     out varchar2, --备用出参
  P_BRID00     out Varchar2, --出参：病人ID
  P_SFZHAO     out Varchar2, --身份证号码
  P_SFXNH0     out Varchar2, --是否新农合病人
  P_ISEXITS    out Varchar2, --出参:是否存在病人基本信息 1 是 0 否
  P_ERRMSG     out varchar2, --错误信息，空表示正确
  P_CARDN1     out varchar2  --kmh
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2014.01.01  重新整理脚本
-- zhangyc     2014.01.16  去掉参数P_OTHER_CARDNO
-- zhangyc     2014.02.18  P_FZXBH0返回(0:市医保 1:省医保 2：保健,3:两费 4:自费 ''：其它)
-- zhangyc     2014.02.27  增加返回P_FZXBH0（5:异地医保 6:新农合）
-- zhangyc     2014.03.11  增加参数SST_ZYBRSFYXMZCZ控制，如果有住院病人P_BYCC00增加“住院”
-- zhangyc     2014.04.11  增加当P_BYRC00='1'时P_BYCC00输出欠费金额
-- zhangyc     2014.04.14  当P_BYRC00='1'时(医保病人例子)P_BYCC00 输出格式为:医保,住院,欠费金额（医保,,） P_BYRC00<>'1'否则格式为:医保
-- zhangyc     2014.04.24  增加出参P_BRBLH0
-- zhangyc     2015.12.07  增加判断如果费别=3并且P_FZXBH0=null 时 全部默认为市医保P_FZXBH0:='0'
-- zhangyc     2015.12.18  P_FZXBH0改为SF_SST_FZXBH0_YKT取值
-- zhangyc     2015.12.28  增加出参P_BRID00,P_ISEXITS
-- zhangyc     2016.05.06  性别(0男1女)-->(1:男；2：女)
-- zhangyc     2017.06.09  修改联系电话取值
-- zhangyc     2018.01.31  增加出参P_SFZHAO V2.04
-- ---------   ----------  -------

ls_brid00  bm_brxxb0.brid00%type; --病人ID
ls_BRXB00  bm_brxxb0.BRXB00%type;
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(400);
LS_CARDNO  varchar2(220);   --卡号
LS_CARDN1  IC_YHXX00.kmh000%type;    --卡面号
LS_ICKQZMC VARCHAR2(50);
LS_CarNum  number(5); --卡号位数
LS_SFXNH0  IC_YBBRLB.SFXNH0%TYPE;
LS_ZYBRSFYXMZCZ  varchar2(100);
LS_SFZYBR varchar(10);--是否在院病人
LS_MZYJJJKJMHQDJZJETS  varchar2(100);--是否启用门诊预缴金缴退款界面获取待记账金额的天数,>0为具体天数,N:否默认使用GHYXTS, 默认值:N
LS_YJJCLCESFAZFJS  varchar2(100);--预交金处理差额是否按自费金额来计算默认为Y
LV_YXTS00 number(12,4); --参数有效天数
lv_ZYDJFY number(12,4); --中药代煎费
lv_CFJE00 number(12,4); --处方金额
lv_YJJE00 number(12,4); --医技金额
lv_CFZFJE number(12,4); --处方自费金额
lv_YJZFJE number(12,4); --医技自费金额
lv_QFJE00 number(12,4); --欠费金额
lv_ZYE000 number(12,4); --账户总余额
begin
 P_ISEXITS:='0';--是否存在病人基本信息 1 是 0 否
 select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual;

 begin
     select BRID00,ZT0000,kmh000 into ls_brid00,ls_zt0000,LS_CARDN1 from IC_YHXX00 where  ICKH00 =LS_CARDNO and ZT0000=1;
          --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表中未找到!';
       P_ISEXITS:='0';--是否存在病人基本信息 1 是 0 否
    return;
    end;
    select nvl(TYJE00,0),nvl(ZYE000,0),nvl(SFDJ00,0) into ls_TYJE00,lv_ZYE000,ls_SFDJ00 from SF_BRZHXX where BRID00=ls_brid00 and JFLBID=1;
  if ls_SFDJ00<>0 then
      P_ERRMSG:='该病人在本院的账户已被冻结或停用!';
      P_ISEXITS:='1';--是否存在病人基本信息 1 是 0 否
      return;
   end if;
  select A.BRXM00,B.ZYE000-ls_TYJE00,A.BRXB00,A.BRCSRQ,C.YBMC00,
  --(select i.YBMC00 from ic_ybbrlb i where i.ybzxlb=c.ybzxlb and yblb00=ybzxlb and rownum=1)YBMC00,
  nvl(A.BRDH00,A.BRLXDH),A.FBBH00,C.YBZXLB,C.YBLB00,C.SFXNH0,trim(A.BRBLH0)BRBLH0,trim(A.BRZJBH)BRZJBH,a.yblb00
  into P_XMING0,P_YJJYE0,ls_BRXB00,P_CSRQ00,ls_YBMC00,P_LXDH00,LS_FBBH00,LS_YBZXLB,LS_YBLB00,LS_SFXNH0,P_BRBLH0,P_SFZHAO,P_SFXNH0
     from BM_BRXXB0 A,SF_BRZHXX B,IC_YBBRLB C WHERE A.BRID00=B.BRID00 and
      A.FBBH00 =C.FBBH00 and A.YBLB00=C.YBLB00 and B.JFLBID=1 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;

 --SST_ZYBRSFYXMZCZ :住院在院病人是否允许在门诊自助机现金充值 Y:允许 N:不允许 默认:Y
  begin
      select trim(VALUE0) into LS_ZYBRSFYXMZCZ from XT_XTCS00  where  name00 in ('SST_ZYBRSFYXMZCZ');
    exception
      when others then
       LS_ZYBRSFYXMZCZ:='Y';
    end;

 --参数SST_ZYBRSFYXMZCZ=N 在去判断是否在院病人，如果是P_BYCC00返回“医保,住院” 自助机在充值前会去判断是否保护“住院”
 --有包含自助机直接提示“该病人在住院状态不允许在门诊自助机充值！请到收费窗口缴费”
  LS_SFZYBR:='N';
  if LS_ZYBRSFYXMZCZ='N' then
   select count(1) into  LS_CarNum  from ZY_BRXXB0 where BRID00=ls_brid00 and CYRQ00 is null and substr(brzt00,1,1) in ('1','2','3','8');
   if  LS_CarNum>0 then
     LS_SFZYBR:='Y';
   end if;
  end if;

 P_BRID00:=LS_BRID00;
 P_CARDN1:=LS_CARDN1;
 --2015.12.17 分中心编码改为函数(SF_SST_FZXBH0_YKT)返回
 select SF_SST_FZXBH0_YKT(LS_BRID00) into P_FZXBH0  from dual;

 /*
 --两费 350191  保健350192  省医保350000  市医保350100  龙岩市医保:350800
 --(0:市医保 1:省医保 2：保健,3:两费 4:自费 5:异地医保 6:新农合 ''：其它)
  if instrb(LS_YBMC00,'省医保')>0 then
    P_FZXBH0:='1';--'350000';
  elsif instrb(LS_YBMC00,'市医保')>0 then
    P_FZXBH0:='0';--'350100';
  elsif instrb(LS_YBMC00,'保健')>0 then
    P_FZXBH0:='2';--'350192';
  elsif instrb(LS_YBMC00,'两费')>0 then
    P_FZXBH0:='3';--'350191';
  elsif instrb(LS_YBMC00,'自费')>0 then
    P_FZXBH0:='4';
  elsif instrb(LS_YBMC00,'非医保')>0 then
    P_FZXBH0:='4';
  else
    P_FZXBH0:='';
  end if;

 if (P_FZXBH0 is null) and  (trim(LS_SFXNH0)='1') then P_FZXBH0:='6'; end if; --新农合
 if (P_FZXBH0 is null) and  (trim(LS_SFXNH0)='3') then P_FZXBH0:='5'; end if; --异地医保
 --2015.12.07 增加判断如果费别=3并且P_FZXBH0=null 时 全部默认为市医保P_FZXBH0:='0'
 if (P_FZXBH0 is null) and  (LS_FBBH00=3) then P_FZXBH0:='0'; 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;

 --P_OTHER_CARDNO:=P_CARDNO;

 if P_BYRC00='1' then
     if  LS_SFZYBR='Y' then
       P_BYCC00:=P_BYCC00||',住院';
   else
       P_BYCC00:=P_BYCC00||',';
   end if;

    begin
       select upper(trim(value0)) into LS_MZYJJJKJMHQDJZJETS from  XT_XTCS00 where name00='SF_MZYJJJKJMHQDJZJETS';
        exception
       when others  then
        LS_MZYJJJKJMHQDJZJETS:='N';
  end;

  begin
       select upper(trim(value0)) into LS_YJJCLCESFAZFJS from  XT_XTCS00 where name00='SF_YJJCLCESFAZFJS';
        exception
       when others  then
        LS_YJJCLCESFAZFJS:='Y';
  end;

     if LS_MZYJJJKJMHQDJZJETS='N' then
        --处方金额和中药待煎金额
      select  sum(CFZJE0)+sum(NVL(ZYDJFY,0)) JE0000,sum(NVL(ZYDJFY,0)) ZYDJFY  into lv_CFJE00,lv_ZYDJFY from  VW_SF_YFMZCF A
        where (A.BCCFH0  is null) and A.CFZT00 = '0' and A.BRID00 = LS_BRID00
        and ghid00 in(select ghid00 from sf_brxxb0 where  to_date(GHRQ00,'YYYYMMDD')+GHYXTS>=sysdate and brid00=LS_BRID00);

      --医技未记账金额
     select  sum(ZJE000) JE0000  into lv_YJJE00 from  VW_SF_YJYW00 where   (CXDJH0  is  null)
       and  SFDJH0+0 = 0 and XMZT00 = '1'and  BRID00 =LS_BRID00
     and  ghid00 in(select ghid00 from sf_brxxb0 where to_date(GHRQ00,'YYYYMMDD')+GHYXTS>=sysdate and  brid00= LS_BRID00);

   -- 未记帐的处方自费金额
  select  SUM(ROUND(B.YPZSL0*B.LSDJ00,2)*B.ZFBL00) JE0000 into lv_CFZFJE from  VW_SF_YFMZCF A,YF_MZCFMX B
    where A.CFLSH0=B.CFLSH0 AND (A.BCCFH0  is null) and A.CFZT00 = '0' and A.BRID00 = LS_BRID00
    and ghid00 in(select ghid00 from sf_brxxb0  where  to_date(GHRQ00,'YYYYMMDD')+GHYXTS>=sysdate and brid00= LS_BRID00);

  --未记帐的医技自费金额
     select  SUM(ZFJE00) JE0000 Into lv_YJZFJE from  VW_SF_YJYW00 A,YJ_YWJJ00 B where  A.YJDJH0=B.YJDJH0 AND  (CXDJH0  is  null)
    and  SFDJH0+0 = 0 and XMZT00 = '1'and  BRID00 = LS_BRID00
    and  ghid00 in(select ghid00 from sf_brxxb0 where to_date(GHRQ00,'YYYYMMDD')+GHYXTS>=sysdate and  brid00=LS_BRID00);

    else
        LV_YXTS00:=to_number(nvl(LS_MZYJJJKJMHQDJZJETS,'0'));
        --处方金额和中药待煎金额
        select  sum(CFZJE0)+sum(NVL(ZYDJFY,0)) JE0000,sum(NVL(ZYDJFY,0)) ZYDJFY into lv_CFJE00,lv_ZYDJFY from  VW_SF_YFMZCF A
        where (A.BCCFH0  is null) and A.CFZT00 = '0' and A.BRID00 = LS_BRID00
        and ghid00 in(select ghid00 from sf_brxxb0 where  to_date(GHRQ00,'YYYYMMDD')>=sysdate-LV_YXTS00 and brid00=LS_BRID00);
     --医技未记账金额
     select  sum(ZJE000) JE0000  into lv_YJJE00 from  VW_SF_YJYW00 where   (CXDJH0  is  null)
      and  SFDJH0+0 = 0 and XMZT00 = '1' and  BRID00 =LS_BRID00
    and  ghid00 in(select ghid00 from sf_brxxb0 where to_date(GHRQ00,'YYYYMMDD')>=sysdate-LV_YXTS00 and  brid00= LS_BRID00);
  -- 未记帐的处方自费金额
   select  SUM(ROUND(B.YPZSL0*B.LSDJ00,2)*B.ZFBL00) JE0000 into lv_CFZFJE from  VW_SF_YFMZCF A,YF_MZCFMX B
    where A.CFLSH0=B.CFLSH0 AND (A.BCCFH0  is null) and A.CFZT00 = '0' and A.BRID00 = LS_BRID00
    and ghid00 in(select ghid00 from sf_brxxb0  where  to_date(GHRQ00,'YYYYMMDD')>=sysdate-LV_YXTS00 and brid00= LS_BRID00);
  --未记帐的医技自费金额
     select  SUM(ZFJE00) JE0000 Into lv_YJZFJE from  VW_SF_YJYW00 A,YJ_YWJJ00 B where  A.YJDJH0=B.YJDJH0 AND  (CXDJH0  is  null)
     and  SFDJH0+0 = 0 and XMZT00 = '1' and  BRID00 = LS_BRID00
     and  ghid00 in(select ghid00 from sf_brxxb0 where to_date(GHRQ00,'YYYYMMDD')>=sysdate-LV_YXTS00 and  brid00=LS_BRID00);
  end if;
   if LS_YJJCLCESFAZFJS='N' then  --预交金处理差额是否按自费金额来计算默认为Y
     lv_QFJE00:=(nvl(lv_ZYE000,0)-nvl(ls_TYJE00,0))-(nvl(lv_CFJE00,0)+nvl(lv_ZYDJFY,0)+nvl(lv_YJJE00,0));
   else
     lv_QFJE00:=(nvl(lv_ZYE000,0)-nvl(ls_TYJE00,0))-(nvl(lv_CFZFJE,0)+nvl(lv_ZYDJFY,0)+nvl(lv_YJZFJE,0));
   end if;

   if lv_QFJE00<0 then
     P_BYCC00:=P_BYCC00||','||trim(to_char(lv_QFJE00,'999999990.99'));
   else
       P_BYCC00:=P_BYCC00||',';
   end if;

 end if;
 select  SF_SST_XBIE00_YKT(ls_BRXB00,'1') into P_XBIE00   from dual;
 P_ISEXITS:='1';--是否存在病人基本信息 1 是 0 否

exception
    when no_data_found then
      P_ERRMSG:='@'||dbms_utility.format_error_backtrace||'未找到数据！'||SQLERRM;
    when others then
      P_ERRMSG:='发生未知错误'||SQLERRM;
end;