CREATE OR REPLACE FUNCTION SF_XK_GETJYJG
(
  PZYGHID in number,
  PBGXMID in varchar2
 ,PBGXMID1 in varchar2
)
return varchar2 is
  lsJYJG00 JY_JYJG00.jyjg00%type;
  V_LISCJDM  varchar2(10);
  strSQL1 varchar2(1000);
  varBGXMID varchar2(100);
--Person           Date          Comments
--chenyw           2013.06.14    创建函数 for LKSX-20130611-001
--dsm              2016.02.03    增加结果单位 for ZYYS-20160126-002
--dsm              2017.06.29    瑞美LIS接口(注意需要重新设置XK_JYBGXM) for LKSX-20170607-001
--dsm              2017.09.18    JYJK_JYJG00.JYJG00-->JYJK_JYJG00.BGJYJG for ZYYS-20170918-001
--dsm              2018.07.18    由于JYJK_JYJG00.JYYWID是varchar2的所以用不上索引 for ZYYS-20180717-002
begin
  --处理BGXMID使之合理化
  select nvl(max(trim(VALUE0)),'0') into V_LISCJDM from 	XT_XTCS00 where name00='XT_LISCJDM'   ;
  varBGXMID:=PBGXMID;
  if (trim(varBGXMID)='') then
   return '';
  end if;
  if (substr(varBGXMID,1,1)=',') then
    varBGXMID:=trim(substr(varBGXMID,2,length(varBGXMID)));
  end if;

  if (substr(varBGXMID,-1,1)=',') then
    varBGXMID:=trim(substr(varBGXMID,1,length(varBGXMID)-1));
  end if;
  if V_LISCJDM='0' then
    strSQL1:='select JYJG00 from  (select  JYJG00 from VW_XK_LISJG00 where MZZYBZ=''1'' and ZYGHID='||to_char(PZYGHID)||  -- JYJG00||JGDW00  
            ' AND BGXMID in ('||varBGXMID||')   order by PYRQ00 desc ) where  rownum=1';
  	
  else 
    strSQL1:='select JYJG00 from  (select BGJYJG||JGDW00 JYJG00 from JYJK_JYJG00 a,YJ_YW0000 b  where a.YJDJH0=b.YJDJH0 and b.MZZYBZ=''1'' and ZYGHID='||to_char(PZYGHID)||
            ' AND BGXMID in ('||varBGXMID||')   order by PYRQ00 desc ) where  rownum=1';
  end if;
  begin
      execute immediate strSQL1 into lsJYJG00 ;
  exception
  when others then
        lsJYJG00:='';
  end;
  return lsJYJG00;
end;
/