create or replace function SF_SF_GET_PREFIX_ICKH00
(
 PCARDNO  in varchar2            ,--不带前缀卡号(原始卡号)
 PBRXM00  in varchar2            ,--病人姓名
 PBRZJBH  in varchar2            ,--病人证件号码
 PYBID00  in varchar2 default '' ,--医保ID
 PBRID00  in number   default 0  ,--病人ID
 PINTXML  in varchar2 default '' ,--其它参数(XML)
 PPZCJBH  in varchar2 default 'YWSL' --凭证厂家编码 
)
return  varchar2 is
-- MODIFICATION HISTORY
-- Person      Date        Comments
--zhangyc     2021.04.28  created   by  MZSF9-20210429-004
--zhangyc     2021.05.12  增加入参PPZCJBH凭证厂家编码 by MZSF9-20210512-001
--zhangyc     2021.07.07  如果根据姓名和身份证查下到多人时暂时返回最后一个病人 BY   MZSF9-20210707-002
--zhangyc     2021.08.03  如果PCARDNO=000000000 按空值处理  by MZSF9-20210730-001
  LS_ICKH00  IC_YHXX00.ICKH00%type; --ickh
  LS_BRXM00  BM_BRXXB0.BRXM00%type; --病人姓名
  LS_BRZJBH  BM_BRXXB0.BRZJBH%type; --证件号码
  LS_YBKH00  BM_BRXXB0.YBKH00%type; --医保卡号
  LS_YBID00  BM_BRXXB0.YBID00%type; --医保ID
  LS_TEMPNUM number(12);
  LS_BRID00  number(12);
  LS_CARDNO  varchar2(50);--卡号
begin

if PCARDNO='000000000' then
    LS_CARDNO:=null;
else
    LS_CARDNO:=substrb(PCARDNO,1,50);
end if;

if PBRID00>0 then 
 begin
    select  B.ICKH00  into  LS_ICKH00  from  BM_BRXXB0 a,IC_YHXX00 B  where  A.BRID00 =B.BRID00  and B.ZT0000='1' and B.BRID00=PBRID00;
 exception
  when others  then
   LS_ICKH00:=null;
 end;
else
    if LS_CARDNO is not null then 
        LS_ICKH00:=SF_SF_CARDNO_ICKH00(LS_CARDNO); --获取前缀卡号
    end if; 
     if LS_ICKH00 is not null then  --根据转化的卡号获取基本信息
        begin
         select  A.YBKH00,A.YBID00,A.BRXM00,A.BRZJBH  into  LS_YBKH00,LS_YBID00,LS_BRXM00,LS_BRZJBH  from  BM_BRXXB0 a,IC_YHXX00 B  
         where  A.BRID00 =B.BRID00 and B.ZT0000='1' and  B.ICKH00=LS_ICKH00;
        exception
    when others  then
     LS_YBKH00:=null;
     LS_YBID00:=null;
     LS_BRXM00:=null;
     LS_BRZJBH:=null;
    end;
    --判断姓名
    if trim(LS_BRXM00) is not null and trim(PBRXM00) is not null and trim(LS_BRXM00)<>trim(PBRXM00)  then 
     begin
      select  B.ICKH00  into  LS_ICKH00  from  BM_BRXXB0 a,IC_YHXX00 B  
      where  A.BRID00 =B.BRID00  and B.ZT0000='1' and a.BRXM00=PBRXM00 and a.BRZJBH=PBRZJBH and trim(A.YBID00)=PBRZJBH;
     exception
     when others  then
      LS_ICKH00:=null;
     end;
     end if;
   else --卡号为空根据身份证号查询
     select  count(1)  into  LS_TEMPNUM  from  BM_BRXXB0 a,IC_YHXX00 B  
      where  A.BRID00 =B.BRID00  and B.ZT0000='1' and a.BRXM00=PBRXM00 and a.BRZJBH=PBRZJBH and b.kldh00<>'XNK';
    if nvl(LS_TEMPNUM,0)>1 then
        --取最大病人ID
     begin
      select  max(B.BRID00)  into  LS_BRID00  from  BM_BRXXB0 a,IC_YHXX00 B  
      where  A.BRID00 =B.BRID00  and B.ZT0000='1' and a.BRXM00=PBRXM00 and a.BRZJBH=PBRZJBH and b.kldh00<>'XNK';
     exception
     when others  then
      LS_BRID00:=0;
     end;   
     if LS_BRID00>0 then
        begin
       select  B.ICKH00  into  LS_ICKH00  from  BM_BRXXB0 a,IC_YHXX00 B  
       where  A.BRID00 =B.BRID00  and B.ZT0000='1' and a.BRID00=LS_BRID00;
      exception
      when others  then
       LS_ICKH00:=null;
      end;  
     end if;    
    else
     begin
      select  B.ICKH00  into  LS_ICKH00  from  BM_BRXXB0 a,IC_YHXX00 B  
      where  A.BRID00 =B.BRID00  and B.ZT0000='1' and a.BRXM00=PBRXM00 and a.BRZJBH=PBRZJBH;
     exception
     when others  then
      LS_ICKH00:=null;
     end;   
    end if;
    end if;
end if; 
   return trim(LS_ICKH00);   
end;