CREATE or replace FUNCTION SF_YS_YKDWZXXMTS(ad_KSBH00 in number,  --科室
                                            ad_BRID00 in number  --病人ID
                                            )
return varchar2 is
  LS_TSTS00  XT_XTCS00.VALUE0%type;
  LS_RETURN  varchar2(255);
  LS_MINTS0  number(4);
  LS_MAXTS0  number(4);
  LS_TMPSTR  varchar2(4);
  I   number;
  J   number;
  cursor KSWZXXM is 
   select  KDKSBH,KDYS00,KDRQ00,ZLXMJC,floor(sysdate-to_date(KDRQ00,'YYYYMMDD')) YXTS00 from YJ_YW0000 a where MZZYBZ='0'
     and xmzt00 in ('0','1')   
     and KDKSBH=ad_KSBH00 and BRID00=ad_BRID00 and floor(sysdate-to_date(KDRQ00,'YYYYMMDD')) between LS_MINTS0 and LS_MAXTS0;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm      2012.04.25    create for MZYS-20120412-001
-- zhangwz  2012.05.09    解决如果病人需要提示的内容太多的时候参数LS_RETURN赋值超界报错导致的程序不能进行. by MZYS-20120509-002.

begin
	 LS_RETURN:=' ';
	 begin
     select trim(VALUE0) into LS_TSTS00 from XT_XTCS00 where NAME00='YS_WZXYJXMTSXQ';	
   exception
     when others then
       LS_TSTS00:='0'   ;
   end;
   i:=instr(LS_TSTS00,'-');   
   if (LS_TSTS00<>'0') and i>0 then 
     LS_TMPSTR:=substr(LS_TSTS00,1,i-1);
   	 LS_MINTS0:=to_number(LS_TMPSTR);
   	 J:=length(trim(LS_TSTS00));
   	 LS_TMPSTR:=substr(LS_TSTS00,i+1,j-i);
   	 LS_MAXTS0:=to_number(LS_TMPSTR);
   	 I:=0;
   	 for KSWZX in KSWZXXM loop
   	   I:=I+1;
   	   if LS_RETURN=' ' then
   	     LS_RETURN:=KSWZX.KDRQ00||' ' ||KSWZX.ZLXMJC;
   	   elsif lengthb(LS_RETURN||CHR(10)||KSWZX.KDRQ00||' ' ||KSWZX.ZLXMJC)<=200 then
   	     LS_RETURN:=LS_RETURN||CHR(10)||KSWZX.KDRQ00||' ' ||KSWZX.ZLXMJC;
   	   elsif lengthb(LS_RETURN||CHR(10)||KSWZX.KDRQ00||' ' ||KSWZX.ZLXMJC)>200  then
   	     j:=-1;
   	   end if;  
   	 end loop;
   end if ;	
   if LS_RETURN<>' ' then LS_RETURN:='病人存在如下未执行项目：'||Chr(10)||LS_RETURN;
   end if;
   if J=-1 then LS_RETURN:=LS_RETURN||Chr(10)||'......';
   end if;
   LS_RETURN:=LS_RETURN;
   return LS_RETURN;

end;
/