CREATE OR REPLACE FUNCTION SF_BQ_BRZJTSXX(ad_ZYID00 in number
                                       )
return varchar2 is
  VBRXM00  BM_BRXXB0.BRXM00%type;
  VSFZH00  BM_BRXXB0.BRZJBH%type;
  VZT0000  YS_BRZZSQ.ZT0000%type;
  VID0000  YS_BRZZSQ.ID0000%type;
  VCOUNT   number(5);
  VCOUNT1   number(5);
  VSFJZFP   varchar2(10);
  VTSXX00  varchar2(30);
  PRAGMA AUTONOMOUS_TRANSACTION;
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- huangjy     2020.05.26   create for BQ-20200521-001
-- linshu      2020.10.09   扩展出参VTSXX00的长度有20改为30 by BQ-20200928-001   
begin
   VID0000:=0;
   VTSXX00:='';
   select count(*) into VCOUNT from YS_BRZZSQ ;
   if VCOUNT>0 then
     select BRXM00,BRZJBH into VBRXM00,VSFZH00 from BM_BRXXB0 where BRID00 in(select BRID00 from ZY_BRXXB0 where ZYID00=ad_ZYID00);
     begin
       select  ZT0000 ,ID0000 into VZT0000,VID0000 from YS_BRZZSQ where BRXM00=VBRXM00 and SFZH00=VSFZH00 and CZRQ00>=to_char(sysdate-100,'YYYYMMDD') and FZBZ00='1'  and rownum=1;
     exception
       when others then
        VID0000:=0;
     end;
     if VID0000>0 then
       VTSXX00:= '[转诊]';
       if VZT0000='0' then
         update YS_BRZZSQ set GHID00=ad_ZYID00 where ID0000=VID0000 and ZT0000='0';
         commit;
       end if;
     else
       VTSXX00:= '';
     end if;
   end if;
   VCOUNT1:=0;
   begin
      select nvl(MZLY00,' ') into VTSXX00  from BM_MZJZBR where BRSFZH in(select BRZJBH from BM_BRXXB0 b,ZY_BRXXB0 c where c.ZYID00=ad_ZYID00 and c.BRID00=b.BRID00 ) and YXBZ00='1' and rownum=1;
      VCOUNT1:=1;
   exception
     when others then
      VCOUNT1:=0;
      VTSXX00:='';
   end;
   select substr(SF_ZY_SFJZFP(ad_ZYID00,0,to_char(VCOUNT1),'0','0'),1,2) into VSFJZFP   from dual;
   if VSFJZFP='1' then
       VTSXX00:= VTSXX00||'[精准扶贫]';
   end if;
   return VTSXX00;
end;
/