CREATE OR REPLACE FUNCTION SF_SF_YKTHKPD
(
    as_YBLB00 in varchar2 ,  --医保名称
    as_BRZTQK in varchar2 , --职退情况
    as_brid00 in number Default 0    --BRID00
)
    --函数返回VARCHAR2   0:无效的病人  1:一卡通用户   2: 医保换卡病人(不包括离休病人)   3：离休病人  4：非医保的病人
    Return varchar2
as
    --函数声明
    ls_FBBH00        BM_BRFBB0.FBBH00%TYPE;   --病人费别编号
    ls_YBLBBH        IC_YBBRLB.YBLB00%TYPE;   --病人所属医保中心名称对应的编号
    ls_YBBRLB        BM_YBBRLB.YBBRLB%TYPE;   --医保病人类别
    ls_YBZXLB        BM_YBBRLB.YBZXLB%TYPE;   --医保病人类别
    ls_SYYBYKT       char(1);                 --是否使用医保一卡通
    ls_YXQFGH        BM_YBBRLB.YXQFGH%type;   --是否离休病人
    ls_YBHKBRCS      char(1);                 --医保换卡病人参数
    ls_YDYBYBLB      varchar2(100);            --异地医保病人类别
    ls_YDYBSFYYKT    varchar2(50);            --异地医保病人是否用一卡通
    ls_LXBRSFSK      char(1);                 --离休病人锁卡参数

BEGIN
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGY      2008.12.10   判断医保病人是一卡通用户还是换卡病人
-- YANGY      2009.01.07   判断医保病人是否为离休病人
-- YANGY      2009.06.22   龙岩人民医院异地医保病人需要换卡
-- YANGY      2009.06.24   龙岩人民医院异地医保比较特殊，增加参数控制设置异地医保病人类别。
-- dsm        2011.08.29   南平异地医保医保使用一卡通
-- dsm        2012.01.05   南平离休病人使用锁卡操作 for MZSF-20111231-001
    select nvl(trim(max(value0)),'Y') into ls_SYYBYKT  from xt_xtcs00 where name00='SF_SFSYYBYKT';
    if ls_SYYBYKT = 'N' then
      RETURN '2';
    end if;

    select nvl(trim(max(value0)),'0') into ls_YBHKBRCS  from xt_xtcs00 where name00='SF_YBHKBRGXHCS';
    select nvl(trim(max(value0)),'0') into ls_LXBRSFSK  from xt_xtcs00 where name00='SF_LXBRSFSK';
    select nvl(trim(max(value0)),'-1') into ls_YDYBYBLB  from xt_xtcs00 where name00='SF_YDYBYBLB';
    select nvl(trim(max(value0)),'N') into ls_YDYBSFYYKT  from xt_xtcs00 where name00='SF_YDYBSFYYKT';

    if (as_brid00=0) or (as_brid00 is null) then
        BEGIN
        select  YBLB00,YBZXLB,FBBH00  into  ls_YBLBBH,ls_YBZXLB,ls_FBBH00  from  IC_YBBRLB  where YBMC00 = trim(as_YBLB00);
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
               RETURN '0';
        END;
    else
        BEGIN
        select  max(trim(a.YBLB00)),max(trim(a.YBZXLB)),max(trim(a.FBBH00)),nvl(trim(max(YXQFGH)),'0') into
        ls_YBLBBH,ls_YBZXLB,ls_FBBH00,ls_YXQFGH from  IC_YBBRLB a, bm_brxxb0 b, bm_ybbrlb c
        where a.fbbh00=c.fbbh00 and a.ybzxlb=c.ybzxlb and a.fbbh00=b.fbbh00 and a.yblb00=b.yblb00
              and b.ybbrlb=c.ybbrlb and b.brid00= as_brid00;
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
               RETURN '0';
        END;
    end if;

    if ls_FBBH00<>3 then
        RETURN '4';
    end if;

    if ls_YBHKBRCS = '1' then
       if (trim(ls_YBZXLB)='1') and (instrb(','||ls_YDYBYBLB||',',','||trim(ls_YBLBBH)||',') > 0) then   --龙岩人民医院异地医保病人需要 换卡
         RETURN '2';
       end if;
    else
       if  (trim(ls_YBZXLB)='Z') then   --铁路医保和异地医保 换卡
         RETURN '2';
       end if;

       if ls_YDYBSFYYKT='Y' and (trim(ls_YBZXLB)='2') then --异地医保 一卡通
         RETURN '1';
       elsif ls_YDYBSFYYKT='N' and (trim(ls_YBZXLB)='2') then
         RETURN '2';
       end if;

       --再得到医保病人类别
       BEGIN                          --离休病人换卡
       select  nvl(trim(max(YXQFGH)),'0')  into  ls_YXQFGH  from  BM_YBBRLB  where BRLBMC = trim(as_BRZTQK) and YBZXLB = trim(ls_YBZXLB) and fbbh00 = ls_FBBH00;
       EXCEPTION
           WHEN OTHERS THEN
               RETURN '0';
       END;
       if trim(ls_YXQFGH)='1' and ls_LXBRSFSK='0' then
         RETURN '3';
       elsif  trim(ls_YXQFGH)='1' and ls_LXBRSFSK='1' then
         RETURN '1';
       end if;

    end if;
    return '1';
EXCEPTION
    when others        then
      raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',130));

END;