create or replace function SF_BA_GETLCZDB0Copy
-- MODIFICATION HISTORY
-- Person    Date        Comments
-- xuexc   2021.05.12    Create 过滤出院其他诊断    BAGL-20210419-004
(
  PZYID00  in    number ,
  PZDLB00  in    char   ,
  PFHLX00  in    number , --返回类型 0 ZDM000 1 RYBQ00 2 ZDMC00 3 ZLXG00
  PXH0000  in    number
)
return varchar2
is
  VZDMC00  BQ_BRZDXX.ZDMC00%type;  --按顺序号取诊断的编码，主要是BQ_BRZDXX.PLXH00这个字段的值不靠谱，导致传接口有的时候数据传不过去
begin
  begin
   if Pfhlx00=0 then
      select ZDM000 into Vzdmc00 from (select rownum XH0000,a.* from ( 
      -- select decode(ZDM000,'Z03.800y001','Z03.800','Z03.800x001','Z03.800',xt.icd101) ZDM000 from BQ_BRZDXX zd,Xt_Icddyb xt
      --where ZYID00=Pzyid00 and zdm000 is not null and zd.ZDLB00=Pzdlb00 and xt.icd100=zd.zdm000 order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ;
      select xt.icd101 ZDM000 from BQ_BRZDXX zd,Xt_Icddyb xt
      where ZYID00=Pzyid00 and zdm000 is not null and zd.ZDLB00=Pzdlb00 and (xt.icd100=zd.zdm000) 
      --and zdm000  not in(select zdbm00 from BA_WTFJM0 wt where wt.zdbm00=zd.zdm000 /*and zd.zdlb00='4'*/ and wt.sfyx00='1' )
      and not exists (select 1
                        from BA_WTFJM0 wt
                       where wt.zdbm00 = xt.icd101 
                         and wt.sfyx00 = '1')
      order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ;
     
    elsif Pfhlx00=1 then
      select decode(RYBQ00,'有','1','临床未确定','2','情况不明','3','无','4','1','1','2','2','3','3','4','4') into Vzdmc00 from (select rownum XH0000,a.* from (select RYBQ00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000;
    elsif Pfhlx00=2 then
      select jbmc00 into Vzdmc00 from (select rownum XH0000,a.* from (select xt.jbmc01 jbmc00 from BQ_BRZDXX zd ,Xt_Icddyb xt where ZYID00=Pzyid00 and zd.ZDLB00=Pzdlb00 and (xt.icd100=zd.zdm000) 
       --and jbmc00 not in (select zdmc00 from BA_WTFJM0 wt where wt.zdbm00=zd.zdm000 /*and zd.zdlb00='4'*/ and wt.sfyx00='1') 
       and not exists (select 1
                        from BA_WTFJM0 wt
                       where wt.zdbm00 = xt.icd101 
                         and wt.sfyx00 = '1')
       order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ;
      
      
    elsif Pfhlx00=3 then
      select decode(ZLXG00,'1','1','2','2','3','3','4','4','5','9','6','9','7','9','8','9') into Vzdmc00 from (select rownum XH0000,a.* from (select ZLXG00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000;
    elsif Pfhlx00=5 then
    select ZDRQ00 into Vzdmc00 from (select rownum XH0000,a.* from (select ZDRQ00 from BQ_BRZDXX where ZYID00=PZYID00 and ZDLB00=PZDLB00 order by PLXH00,ID0000) a) where XH0000=PXH0000;
    elsif Pfhlx00=6 then
      select decode(ZLXG00,'1','1','2','2','3','3','4','4','9') into Vzdmc00 from (select rownum XH0000,a.ZLXG00 from (select ZLXG00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000;
    end if;
  exception
      when others then Vzdmc00 := '';
  end;
  return VZDMC00;
end;