create or replace procedure SP_YS_GETMSXYZ
(
    P_GHH000 in varchar2, --挂号号
    P_BRID00 in number,   --病人ID
    P_CZSJ00 in varchar2, --查询时间
    P_MSXYZ0 out varchar2 -- 处方信息
)
-- Person      Date        Comments
-- ruanbh      2024.04.03  create 获取描述性医嘱 MZYS9-20231227-005
as
    V_RETURN varchar2(2000);--函数返回值
    VLHCOUNT number(5);--小标题计数器
    VCOUNT0  number(5);
    cursor  C_YS_YJHIS_MSXCF  is                                          --描述性医嘱
    select A.CFMC00,A.SL0000,A.BZ0000,A.BWMC00  from VW_YS_YJCFHISTORY_MZDZBL A where A.GHH000 = P_GHH000 and A.BRID00 = P_BRID00 and A.KDRQSJ > P_CZSJ00 and A.CFBZ00 = '2' and A.CFLB00 ='9' and A.SFLB00='-1' and A.DCBZ00 = '0' and (A.FJDJBZ = '0' or A.FJDJBZ = '9') and A.YPNM00 not in(select a.ZLXMID from BM_ZLZD00 a,BM_YSLB00 b where a.ZLXMID=b.ZLXMID) and A.ZT0000 not in('已作废','退费申请','已退药','已退费','退费确认') order by CFBZ00,CFLSH0 desc,KDRQSJ,CFLB00,MXID00;
begin
  V_RETURN := '';
  VLHCOUNT := 1;
  --如果该病人有描述性医嘱的话，在进行获取描述性医嘱字符串
  select count(1) into VCOUNT0 from VW_YS_YJCFHISTORY_MZDZBL A where A.GHH000 = P_GHH000 and A.BRID00 = P_BRID00 and A.KDRQSJ > P_CZSJ00 and A.ZT0000 not in('已作废','退费申请','已退药','已退费','退费确认');
  if VCOUNT0>0 then
    select count(*) into VCOUNT0 from VW_YS_YJCFHISTORY_MZDZBL A where A.GHH000 = p_ghh000 and A.BRID00 = P_BRID00 and A.KDRQSJ > P_CZSJ00 and A.CFBZ00 = '2' and A.cflb00 ='9' and A.SFLB00='-1' and A.DCBZ00 = '0' and (A.FJDJBZ = '0' or A.FJDJBZ = '9') and A.YPNM00 not in(select a.ZLXMID from BM_ZLZD00 a,BM_YSLB00 b where a.ZLXMID=b.ZLXMID) and A.ZT0000 not in('已作废','退费申请','已退药','已退费','退费确认') order by CFBZ00,CFLSH0 desc,KDRQSJ,CFLB00,MXID00;
    if VCOUNT0>0 then
      for ZLSSMC in C_YS_YJHIS_MSXCF loop
          V_RETURN := V_RETURN || to_char(VLHCOUNT) || '  ' || ZLSSMC.CFMC00 || '  ' || ZLSSMC.BZ0000 || '  ' ||ZLSSMC.BWMC00 || chr(10);
          --小标题加1
          VLHCOUNT := VLHCOUNT + 1;
      end loop;
    end if;
    V_RETURN := V_RETURN || chr(10);
  end if;
  --手术
  select count(1) into VCOUNT0 from SS_YW0000 where ZYHGHH=P_GHH000;
  if VCOUNT0>0 then
    for VROW in (select substr(A.YYSSRQ,5,2)||'月'||substr(A.SSQSRQ,7,2)||'日' as YYSSRQ,substr(A.SSQSSJ,1,5)||'分' as YYSSSJ,A.MZMC00,B.SSXMJC from SS_YW0000 A,SS_YWMX00 B where A.SSDH00=B.SSDH00 and A.ZYHGHH=P_GHH000 and MZZYBZ='0') loop
      V_RETURN:=V_RETURN||'拟'||VROW.YYSSRQ||VROW.YYSSSJ||'在'||VROW.MZMC00||'下行'||VROW.SSXMJC||' (描述)'|| chr(10);
    end loop;
  end if;
  P_MSXYZ0 := V_RETURN;
exception
  when others   then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,220));
end;