-- Start of DDL Script for Function SD_HOSPITAL.SF_SST_GETICKH00
-- Generated 3-一月-2018 8:33:24 from SD_HOSPITAL@WLPT

CREATE OR REPLACE 
FUNCTION sf_sst_getickh00(P_CARDNO  char)
-- MODIFICATION HISTORY
-- Person      Date        Comments
--zhangyc     2012.04.27  create
--zhangyc     2014.01.01  重新整理
--zhangyc     2014.07.25  旧医保根据卡面号取院内的ickh00
-- ---------   ----------  -------
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); 
begin
	LS_COUNT0:=0;
	begin
       select upper(trim(value0)) into LS_ICKQZMC from  XT_XTCS00 where name00='SST_ICKQZMC';
        exception
       when others  then
        LS_ICKQZMC:=NULL;
  end; 
     select length(P_CARDNO)into LS_CarNum from dual; 
     if LS_CarNum=9 then  ---社保卡
      Select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00@wb_his where KLDH00  in ('YB-SBK');
     elsif LS_CarNum=12 then --医保卡
     Select count(0) into LS_COUNT0 from  IC_KLDM00@wb_his 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@wb_his where KLDH00 in  ('YB-MMRK','YB-FJYB','YB-FZYB','YB-CPUK') and rownum=1; 
      else
      	Select trim(KHQZBH) into LS_KHQZBH from  IC_KLDM00@wb_his where KLDH00  in ('YB-SBK');
      end if;
      else
         LS_KHQZBH:='';
     end if;
     LS_ICKH00:=LS_KHQZBH||P_CARDNO;
   
   --增加校验，如果找不到病人信息在根据KMH000取ickh00  
     select Count(*) into LS_COUNT0  from IC_YHXX00@wb_his where ICKH00 = LS_ICKH00 and ZT0000='1';
     if LS_COUNT0<=0 then
     	 begin
        select ICKH00 into  LS_ICKH00 from IC_YHXX00@wb_his where KMH000=P_CARDNO and zt0000='1';
        exception
       when others  then
        LS_ICKH00:=LS_KHQZBH||P_CARDNO;
      end; 
     end if;    
   return trim(LS_ICKH00);
end;
/



-- End of DDL Script for Function SD_HOSPITAL.SF_SST_GETICKH00