CREATE OR REPLACE FUNCTION SF_YJ_GETYBGGBZTS
(   p_zlxmid in number,  --诊疗项目ID
    p_fbbh00 in number,	 --费别编号
    p_ybzxlb in varchar2 --医保中心类别 
)
return  varchar2 is
  TmpStr  Varchar2(500);
  tmpStr1 varchar2(500);
  tmpStr2 varchar2(500);
  Cursor C_YBGG00 is select distinct c.ypgg00 from XT_ZLSFGX a,bm_ybsfdy b,bm_ybsfxm c
    where a.zlxmid=p_zlxmid and b.fbbh00+0=p_fbbh00 and b.ybzxlb=p_ybzxlb and b.sfyp00='N' and b.sfxmid=a.sfxmid and c.xmbh00=b.ybxmbh
    and c.fbbh00+0=b.fbbh00 and c.ybzxlb=b.ybzxlb and c.ypgg00 is not null;

  Cursor C_YBBZ00 is select distinct nvl(b.bz0000,c.bz0000) bz0000 from XT_ZLSFGX a,bm_ybsfdy b,bm_yysfxm c
    where a.zlxmid=p_zlxmid and b.fbbh00+0=p_fbbh00 and b.ybzxlb=p_ybzxlb and b.sfyp00='N' and b.sfxmid=a.sfxmid 
          and (b.bz0000 is not null  or c.bz0000 is not null)
          and b.sfxmid=c.sfxmid;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- liuj      20131205      Create;
-- dsm       20150625      BM_YBSFXM.BZ0000字段为空时，则取BM_YYSFXM.BZ0000。for YJ-20150416-001
-- qks       2016.07.06    截取返回值长度。 by YJ-20160707-001 
begin
	TmpStr:='';
	tmpStr1:='';
	tmpStr2:='';
  for ybgg in C_YBGG00 loop
    if TmpStr1 is null then
       TmpStr1:='规格说明:'||ybgg.ypgg00;
    else
       TmpStr1:=substrb(TmpStr1||';'||ybgg.ypgg00,1,500);
    end if;
  end loop;
  for ybbz in C_YBBZ00 loop
    if TmpStr2 is null then
       TmpStr2:=substrb('备注：'||ybbz.bz0000,1,500);
    else
       TmpStr2:=substrb(TmpStr2||';'||ybbz.bz0000,1,500);
    end if;
  end loop;
  tmpstr:=substrb(tmpstr1||tmpstr2,1,500);
  return TmpStr;
end SF_YJ_GETYBGGBZTS;
/