create or replace function SF_JY_UNION_XMMC00_XXMS00 (PTXM000 IN VARCHAR2)
return varchar2 is
  Vxmmc00 varchar2(2000);   --项目名称
  v_SFSMFUZY xt_xtcs00.value0%type;
  v_TXMZLXMZJCD  xt_xtcs00.value0%type;
  int_TXMZLXMZJCD number(5,0);
  ls_yyid00   xt_yyxx00.yyid00%type;
  ls_yjdjh0   jy_yw0000.yjdjh0%type;

  cursor C_xmmc00 is select DISTINCT XXMS00 ZLXMJC from JY_yw0000 A,BM_ZLZD00 B
    where txm000=PTXM000 AND A.ZLXMID=B.ZLXMID AND A.JGZT00<>'7';
  cursor C_xmmc01 is select DISTINCT trim(substr(XXMS00,0,decode(instr(trim(XXMS00),'[',1),0,length(XXMS00),instr(trim(XXMS00),'[',1)-1))) ZLXMJC from JY_yw0000 A,BM_ZLZD00 B
    where txm000=PTXM000 AND A.ZLXMID=B.ZLXMID AND A.JGZT00<>'7';
--Person           Date         Comments
--chenyw           2015.05.05   创建项目名称取值BM_ZLZD00.XXMS00  for LIS-20150504-001

begin
  IF PTXM000 IS NULL THEN
    RETURN NULL;
  END IF;
  begin
         select trim(value0) into v_SFSMFUZY from xt_xtcs00 where name00='JY_JYJBSFXMZY';
    exception
    when others then
       v_SFSMFUZY:='N';
  end;

  begin
         select to_char(trim(value0)) into int_TXMZLXMZJCD from xt_xtcs00 where name00='JY_TXMZLXMZJCD';
    exception
    when others then
       v_TXMZLXMZJCD:='N';
  end;

  if v_SFSMFUZY='Y' then
   for xm in C_xmmc01 loop
      if lengthb(Vxmmc00) > 2000 then
         Vxmmc00 := Substrb(trim(Vxmmc00),1,2000);
         exit;
      end if;
      if v_TXMZLXMZJCD='N' then
         if Vxmmc00 is null then
            Vxmmc00:=trim(xm.ZLXMJC);
         else
            Vxmmc00:=trim(Vxmmc00)||'|'||trim(xm.ZLXMJC);
         end if;
      else
         if Vxmmc00 is null then
            Vxmmc00:=Substrb(trim(xm.ZLXMJC),1,int_TXMZLXMZJCD);
         else
            Vxmmc00:=trim(Vxmmc00)||'|'||Substrb(trim(xm.ZLXMJC),1,int_TXMZLXMZJCD);
         end if;
       end if;
  end loop;
  else
  for xm in C_xmmc00 loop
      if lengthb(Vxmmc00) > 2000 then
         Vxmmc00 := Substrb(trim(Vxmmc00),1,2000);
         exit;
      end if;
      if v_TXMZLXMZJCD='N' then
         if Vxmmc00 is null then
            Vxmmc00:=trim(xm.ZLXMJC);
         else
            Vxmmc00:=trim(Vxmmc00)||'|'||trim(xm.ZLXMJC);
         end if;
      else
         if Vxmmc00 is null then
            Vxmmc00:=Substrb(trim(xm.ZLXMJC),1,int_TXMZLXMZJCD);
         else
            Vxmmc00:=trim(Vxmmc00)||'|'||Substrb(trim(xm.ZLXMJC),1,int_TXMZLXMZJCD);
         end if;
       end if;
  end loop;
  end if;
  ----龙岩人民医院专用
  select yyid00 into ls_yyid00 from xt_yyxx00;
  if ls_yyid00='229112' then
    select yjdjh0 into ls_yjdjh0 from jy_yw0000 a where a.txm000=PTXM000 and rownum=1;
    begin
    select yj.bz0000 into Vxmmc00 from YJ_YW0000 y ,bq_yjyz00 yj where y.yzcfid = yj.yzmxid and y.yjdjh0 =ls_yjdjh0  and rownum=1;
     exception
    when others then
       Vxmmc00:='';
  end;
  end if;


  return trim(Vxmmc00);
end;