prompt 原始转化成院内带前缀卡号 SF_SF_CARDNO_ICKH00
create or replace function SF_SF_CARDNO_ICKH00
(
  PCARDNO  varchar2    --不带前缀卡号(原始卡号)
)
return  varchar2 is
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- liwm        2019.02.27  移植5.0   by  MZSF9-20190227-002 
-- linzetao    2023.02.06  增加身份证号做为卡号按照医保卡类处理   MZSF9-20230129-001
-- zhangyc     2023.09.22  注释掉KMH000=PCARDNO and zt0000='1';查询条件 by MZSF9-20230925-001
-- zhangyc     2023.10.31  用KMH000=PCARDNO 增加条件  and KLDH00 in ('SLE4442')
  LS_ICKH00  IC_YHXX00.ICKH00%type;
  LS_CARNUM  number(5); --卡号位数
  LS_KHQZBH  varchar2(4);
  LS_COUNT0  number(5);
begin
    LS_COUNT0:=0;
  select length(PCARDNO)into LS_CARNUM from dual; 
  if LS_CARNUM in (9,10,18) then  ---社保卡
    select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00 where KLDH00  in ('YB-SBK');
  elsif LS_CARNUM in (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;
  elsif LS_CARNUM in (13) then --莆田市民健康卡号 
     LS_KHQZBH:='PTYB';
  else
      LS_KHQZBH:='';
  end if;
   LS_ICKH00:=LS_KHQZBH||PCARDNO;
   --/*
   --增加校验，如果找不到病人信息在根据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=PCARDNO and zt0000='1' and KLDH00 in ('SLE4442');
     exception
     when others  then
        LS_ICKH00:=LS_KHQZBH||PCARDNO;
    end; 
   end if; 
   --*/      
   return trim(LS_ICKH00);   
end;
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%