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
-- ruanbh      2023.01.12    JGZT00增加4 MZYSSJ5-20240111-003
return varchar2 is
  Vxmmc00 varchar2(1500);    --项目名称
  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;