CREATE OR REPLACE FUNCTION SF_JY_UNION_XMMC00_BBK_MZDZBL
(
  PTXM000   in  varchar2,
  PMZZYBZ   in  char,
  PZYHGHH   in  varchar2,
  Pksrq00   in  varchar2 default '',
  Pjsrq00   in  varchar2 default '',
  Pzlxmid   in  varchar2 default '0',
  PJGLX00   in  varchar2 default '',  --用于QueryResult查询结果
  PYBH000   in  varchar2
)
-- modification history
-- Person      Date          Comments
-- ruanbh      2023.02.28    create 获取检验项目名称 MZYSSJ5-20230224-001
return varchar2 is
  Vxmmc00 varchar2(500);    --项目名称
  cursor C_xmmc00 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where TXM000=PTXM000 and A.YBH000=PYBH000 and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('1','2');--DECODE(MZZYBZ,'0','1','2')
  cursor C_xmmc01 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where A.MZZYBZ=PMZZYBZ and A.YBH000=PYBH000 and ZYHGHH=PZYHGHH and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('1','2')
      and A.BBCYRQ between Pksrq00 and Pjsrq00 ;--=DECODE(MZZYBZ,'0','1','2')
  cursor C_xmmc02 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where TXM000=PTXM000 and A.YBH000=PYBH000 and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('1','2') and A.ZLXMID in (Pzlxmid);--DECODE(MZZYBZ,'0','1','2')
  cursor C_xmmc03 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where A.MZZYBZ=PMZZYBZ and A.YBH000=PYBH000 and ZYHGHH=PZYHGHH and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('1','2')
      and A.BBCYRQ between Pksrq00 and Pjsrq00  and A.ZLXMID in (Pzlxmid);--=DECODE(MZZYBZ,'0','1','2')
  cursor C_xmmc04 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where TXM000=PTXM000 and A.YBH000=PYBH000 and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('4','6','8','9');
  cursor C_xmmc05 is select distinct ZLXMJC from JY_YW0000 A,BM_ZLZD00 B
    where A.MZZYBZ=PMZZYBZ and A.YBH000=PYBH000 and ZYHGHH=PZYHGHH and A.ZLXMID=B.ZLXMID and A.JGZT00 in ('6','8','9');
begin
  if Pzlxmid='0' then
    if PTXM000 is null then
      if (PJGLX00='1') then
         for xm in C_xmmc05 loop
           if lengthb(Vxmmc00) > 500 then
                Vxmmc00 := Substrb(Vxmmc00,1,500);
                exit;
           end if;
           if Vxmmc00 is null then
              Vxmmc00:=trim(xm.ZLXMJC);
           else
              Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
           end if;
           end loop;
      else
           for xm in C_xmmc01 loop
           if lengthb(Vxmmc00) > 500 then
                Vxmmc00 := Substrb(Vxmmc00,1,500);
                exit;
           end if;
           if Vxmmc00 is null then
              Vxmmc00:=trim(xm.ZLXMJC);
           else
              Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
           end if;
           end loop;
      end if;
    else
       if (PJGLX00='1') then
         for xm in C_xmmc04 loop
           if lengthb(Vxmmc00) > 500 then
                Vxmmc00 := Substrb(Vxmmc00,1,500);
                exit;
           end if;
           if Vxmmc00 is null then
              Vxmmc00:=trim(xm.ZLXMJC);
           else
              Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
           end if;
           end loop;
       else
         for xm in C_xmmc00 loop
           if lengthb(Vxmmc00) > 500 then
                Vxmmc00 := Substrb(Vxmmc00,1,500);
                exit;
           end if;
           if Vxmmc00 is null then
              Vxmmc00:=trim(xm.ZLXMJC);
           else
              Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
           end if;
         end loop;
       end if;
  end if;
  else
    if PTXM000 is null then
        for xm in C_xmmc02 loop
        if lengthb(Vxmmc00) > 500 then
             Vxmmc00 := Substrb(Vxmmc00,1,500);
             exit;
        end if;
        if Vxmmc00 is null then
           Vxmmc00:=trim(xm.ZLXMJC);
        else
           Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
        end if;
        end loop;
    else
    for xm in C_xmmc03 loop
        if lengthb(Vxmmc00) > 500 then
             Vxmmc00 := Substrb(Vxmmc00,1,500);
             exit;
        end if;
        if Vxmmc00 is null then
           Vxmmc00:=trim(xm.ZLXMJC);
        else
           Vxmmc00:=Vxmmc00||'|'||trim(xm.ZLXMJC);
        end if;
    end loop;
    end if;
  end if;
  return Vxmmc00;
end;