prompt 根据卡号或姓名身份证获取院内ICKH00
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
--linzetao    2022.04.26  PPZCJBH=00：安康码 77：互联网医院的动态电子就诊卡  GGMK9EX-20220421-001
--linzetao    2022.04.29  处理PPZCJBH=00：安康码业务    MZSF9EX-20220418-001
--lwl         2023.01.05  增加如果有IC卡号为身份证号病人取IC卡号为身份证号的BRID00，如果没有取病人最大BRID00  MZSF9EX-20230105-002
  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);--卡号
  LS_DQTIME  varchar(20);--时间和日期
  LS_CZRQ00  varchar2(8);--操作日期
  LS_CZSJ00  varchar2(8);--操作时间
  LS_TEPTIME varchar(20);--时间和日期
  LS_TIMENUM number(12,4);--时间差
begin
  LS_CZRQ00:=to_char(sysdate,'YYYYMMDD');
  LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS');
  LS_DQTIME:=to_char(sysdate,'YYYYMMDDHH24MISS');
  LS_TIMENUM:=0; 

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

--00：安康码 77：互联网医院的动态电子就诊卡---begin---
if PPZCJBH  in ('00')  then-- 00：安康码
 LS_CARDNO:=substrb(nvl(PCARDNO,PBRZJBH),1,50);
elsif PPZCJBH  in ('77')  then--77：互联网医院的动态电子就诊卡
    --互联网医院的动态电子就诊卡 例如：04211025030001260797，0421102503为04月21日10点25分03秒；0001260797为就诊卡号】 医院要求解码规则：取扫码时间的时间戳相减差值±300，±300以内验证成功，取10位时间戳之后的就诊卡号为就诊介质
    if PCARDNO  is null then
        return '';
    else
     begin
        LS_TIMENUM:=to_number(to_char(sysdate,'MMDDHH24MISS'))-to_number(substrb(PCARDNO,1,10));
       exception
   when others  then
    LS_TIMENUM:=99999999;
   end;
   if  LS_TIMENUM>=-300 and  LS_TIMENUM<=300 then
    LS_CARDNO:=substrb(PCARDNO,11,50);
   else
    return '';
   end if;
    end if;
end if;
--00：安康码 77：互联网医院的动态电子就诊卡---end---

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;
    if nvl(LS_TEMPNUM,0)>1 then  
         
      begin
           select  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 length(ICKH00)=18;       --取IC卡号为身份证号的BRID00 
      exception
      when others  then
            LS_BRID00:=0;
      end;              
      
     if trim(LS_BRID00) is  null then 
       begin   --取最大病人ID
            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;
       exception
       when others  then
            LS_BRID00:=0;
       end;        
     end if;
       
     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;

/
show error;