create or replace function SF_PY_CFMXBZ1
-- MODIFICATION HISTORY
-- Person    Date        Comments
-- zhangwz   2013.04.07  create
-- zhangwz   2013.06.22  增加返回对协定处方的处理
-- qks       2014.12.27  协定处方明细药品次剂量大于1,2015.02.12 注释掉
-- qks       2015.02.13  协定处方备注：由于原来的剂数改为药品总量
-- qks       2017.11.07  协定处方备注：去掉名称中最后一个“膏”字; for MZPY9-20171107-001
-- qks       2017.11.27  协定处方备注：要求去掉膏方名称后面的总量以及单位; for YF9-20171127-001
(
  Pcflsh0  varchar2            --处方流水号
)
return varchar2 is  -- 
  lv_return     varchar2(200);
  --lv_ysxm00     YF_MZCF00.YSXM00%type;  --医生姓名
  lv_cfsm00     YF_MZCF00.CFSM00%type;  --处方说明  如中药的十八反十八胃
  cursor C_CFMXBZ is select distinct BZ0000 from YF_MZCFMX where CFLSH0=Pcflsh0 and trim(BZ0000) is not null;
  --'('||b.ZYTS00||'剂)'
  --||'('||a.YPZSL0||a.CFDW00||')' 
  cursor C_XDCFSM is select distinct decode(substr(c.YPMC00,-1),'膏',substr(c.YPMC00,1,length(c.YPMC00)-1),c.YPMC00) XDCFSM from YF_MZCFMX a,YF_MZCF00 b,BM_YD0000 c where a.CFLSH0=Pcflsh0 and a.CFLSH0=b.CFLSH0
                     and a.YPNM00=c.YPNM00 and exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00);
begin 
  lv_return:='';
  for CFMXBZ in C_CFMXBZ loop    
    if lv_return is null then
      lv_return:=substr(CFMXBZ.BZ0000,1,200);
    else 
      lv_return:=substr(lv_return||'；'||CFMXBZ.BZ0000,1,200);
    end if;  
  end loop;
    
  --select YSXM00,trim(CFSM00) into lv_ysxm00,lv_cfsm00 from YF_MZCF00 where CFLSH0=Pcflsh0;
  
  if lv_cfsm00 is not null then 
  begin
    lv_return:='';
  end;
 -- elsif lv_return is not null then
 -- begin
   -- lv_return:=lv_return||' ('||lv_ysxm00||')';
 -- end;
  end if; 
  
  for XDCFSM in C_XDCFSM loop    
    if lv_return is null then
      lv_return:=substr(XDCFSM.XDCFSM,1,200);
    else 
      lv_return:=substr(lv_return||'；'||XDCFSM.XDCFSM,1,200);
    end if;  
  end loop;
  
  return lv_return;
end;