CREATE OR REPLACE FUNCTION SF_ZS_GETXKJYJG
(
  PZYGHID in number,
  PBGXMID in varchar2
)
return varchar2 is
  lsJYJG00 JY_JYJG00.jyjg00%type;
  strSQL1 varchar2(1000);
  varBGXMID varchar2(100);
  vjqrq00   char(8);
  vbrid00  number(10);
--Person           Date          Comments
--caishb           2021.06.10    create   for ZYDZBL-20210609-006

begin
  --处理BGXMID使之合理化

  varBGXMID:=PBGXMID;
  if (trim(varBGXMID)='') then

   return '';
  end if;
  if (substr(varBGXMID,1,1)=',') then
    varBGXMID:=trim(substr(varBGXMID,2,length(varBGXMID)));
  end if;

  select ryrq00,brid00 into vjqrq00,vbrid00 from zy_brxxb0 where zyid00=PZYGHID;

  if (substr(varBGXMID,-1,1)=',') then
    varBGXMID:=trim(substr(varBGXMID,1,length(varBGXMID)-1));
  end if;

   strSQL1:='select JYJG00 from  (select JYJG00 from VW_XK_LISJG00 where brid00='||to_char(vbrid00)||
              ' AND BGXMID in ('||varBGXMID||') and pyrq00>=to_char(TO_DATE('''||vjqrq00||''',''YYYYMMDD'')-2 ,''YYYYMMDD'') order by PYRQ00||PYSJ00 desc ) where  rownum=1';

 -- strSQL1:='select JYJG00 from  (select JYJG00||JGDW00||''(''||BGRQ00||''  ''||BGSJ00||'')'' JYJG00 from VW_XK_LISJG00 where brid00='||to_char(vbrid00)||
             -- ' AND BGXMID in ('||varBGXMID||') and pyrq00>=to_char(TO_DATE('''||vjqrq00||''',''YYYYMMDD'')-2 ,''YYYYMMDD'') order by PYRQ00||PYSJ00 desc ) where  rownum=1';

  begin
      execute immediate strSQL1 into lsJYJG00 ;
  exception
  when others then
        lsJYJG00:='';
  end;

  return lsJYJG00;

end;