create or replace function SF_SST_GETICKH00_YKT_MZBL(P_CARDNO  char, P_IDNO in char default '')
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- cyhong       2020.06.03   create 通过卡面号获取卡号 for MZYSSJ5-20200603-002
-- chenna       2022.08.11   update 医保电子凭证是否可根据身份证号来获取卡号 for MZYSSJ5-20220811-003
-- ruanbh       2023.04.14   update 修复卡号不为空时也允许根据身份证获取卡号 MZYSSJ5-20230414-001
-- ---------   ----------  -------
return  varchar2 is
  LS_ICKH00  IC_YHXX00.ICKH00%TYPE;
  LS_ICKQZMC varchar2(50);
  LS_CarNum  number(5); --卡号位数
  LS_KHQZBH  varchar2(4);
  LS_COUNT0  number(5);
  E_CUSTM      exception;
  LS_ERROR     varchar2(200);
  LS_BRID00  number(10);
  YS_1KKZFZYX XT_XTCS00.VALUE0%TYPE;
  YS_SFZHHQKH XT_XTCS00.VALUE0%TYPE;
  LS_SFZHNum  number(5); --身份证号位数
begin
  LS_COUNT0:=0;
  begin
       select upper(trim(value0)) into LS_ICKQZMC from  XT_XTCS00 where name00='SST_ICKQZMC';
       select upper(trim(value0)) into YS_1KKZFZYX from  XT_XTCS00 where name00='YS_1KKZFZYX';
       exception
       when others  then
          LS_ICKQZMC:=NULL;
  end;
     select length(P_CARDNO)into LS_CarNum from dual;
     if (LS_CarNum=9) OR (LS_CarNum=10) then  ---社保卡
      Select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00 where KLDH00  in ('YB-SBK');
     elsif LS_CarNum=12 then --医保卡
     Select count(0) into LS_COUNT0 from  IC_KLDM00 where KLDH00 in  ('YB-MMRK','YB-FJYB','YB-FZYB','YB-CPUK') and rownum=1;
      if LS_COUNT0>0 then
        Select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00 where KLDH00 in  ('YB-MMRK','YB-FJYB','YB-FZYB','YB-CPUK') and rownum=1;
      else
        Select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00 where KLDH00  in ('YB-SBK');
      end if;
      else
         LS_KHQZBH:='';
     end if;
     LS_ICKH00:=LS_KHQZBH||P_CARDNO;
     
     --## 医保电子凭证是否可根据身份证号来获取卡号，Y:是  N:否 默认为N
     select nvl(max(trim(VALUE0)),'N') into YS_SFZHHQKH from XT_XTCS00 where NAME00='YS_YBDZPZSFKGJSFZHHQKH'; 
     select Count(*) into LS_COUNT0  from IC_YHXX00 where ICKH00 = LS_ICKH00 and ZT0000='1' and P_CARDNO<>'000000000' and P_CARDNO<>'000000000000';
     if LS_COUNT0=0 then
       select length(P_IDNO)into LS_SFZHNum from dual;
       if YS_SFZHHQKH='Y' and (LS_SFZHNum=15 or LS_SFZHNum=18) then
         select b.ICKH00 into LS_ICKH00 from bm_brxxb0 a,ic_yhxx00 b where a.FBBH00=3 and a.brid00=b.brid00 and b.zt0000='1' and A.BRZJBH =P_IDNO and rownum=1;
       end if;
     end if;
     
     --##2020.06.03 cyhong 手机三码1k卡作废之后是否通过规则转化取用有效卡号,Y是，N否，默认N
     if YS_1KKZFZYX='Y' then
       --增加校验，如果找不到有效的ICKHXX00，则通过KMH00查找病人ID，通过病人ID获取有效卡号
       select Count(*) into LS_COUNT0  from IC_YHXX00 where ICKH00 = LS_ICKH00 and ZT0000='1';
       if LS_COUNT0<=0 then
          begin
             select Count(*) into LS_COUNT0  from IC_YHXX00 where KMH000=P_CARDNO;
             if LS_COUNT0>0 then
               select BRID00 into  LS_BRID00 from IC_YHXX00 where KMH000=P_CARDNO;--根据卡面号获取病人ID
               select ICKH00 into LS_ICKH00 from IC_YHXX00 where BRID00 = LS_BRID00 and zt0000 = '1';--根据病人ID获取ICKH00
             else
               LS_ICKH00:= '';
             end if;
          exception
          when others  then
             LS_ICKH00:= '';
          end;
       end if;
     else 
       --增加校验，如果找不到病人信息在根据KMH000取ickh00
       select Count(*) into LS_COUNT0  from IC_YHXX00 where ICKH00 = LS_ICKH00 and ZT0000='1';
       if LS_COUNT0<=0 then
          begin
              select ICKH00 into  LS_ICKH00 from IC_YHXX00 where KMH000=P_CARDNO and zt0000='1';
          exception
          when others  then
              LS_ICKH00:=LS_KHQZBH||P_CARDNO;
          end;
       end if;
     end if;

     return trim(LS_ICKH00);

 exception
    when E_CUSTM then
      raise_application_error(-20907,LS_ERROR);
      rollback;
end;