create or replace function SF_YS_ZLXMXX
(
P_GHH000 in varchar2  --挂号号
--p_cfxx00 out varchar2 -- 处方信息
)
-- caobin     2020.08.28   获取病人治疗方案信息  MZDZBLS-20200827-001
--luoyuanping 2021.01.19   方案导入病历模板根据记录单实际治疗情况导入 禅道需求165
return  varchar2 is
  V_RETURN varchar2(2000);--函数返回值
  V_ZLXMID varchar2(32);--治疗项目ID
  ZLFAMC varchar2(32);--治疗项目ID
  --p_cfxx00  varchar2(2000); -- 处方信息
----------获取治疗项目名称
cursor CUR_ZLXMMX is
select a.ID0000,a.MC0000 from 
(select ZLFABH as ID0000, '治疗周期：' || ZLZQSZ ||decode(ZLZQDW, '1', '日', '2', '月', '3', '年', '4', '周') AS MC0000
from SD_ZLGLXT.ZL_FADJFA A where A.ID0000 = V_ZLXMID
union all
select PLID00, '治疗频率：' || PLMC00 from SD_ZLGLXT.ZL_FADJFA A, SD_ZLGLXT.ZL_FADJPL B where A.ID0000 = B.FADJFA and A.ID0000 =V_ZLXMID
union all
select ZLXMBH, ZLXMMC || '(' || ZLXMSM || ')'   from SD_ZLGLXT.ZL_FADJFA A, SD_ZLGLXT.ZL_FADJXM C  where A.ID0000 = C.FADJFA
and trim(A.ID0000) = trim(V_ZLXMID))a,(select extractValue(value(i),'/input-faxq') ID0000
     from ( select * from (select * from sd_zlglxt.ZL_ZLJLB0 where MZH000=P_GHH000 and SFSC00='1' order by CJSJ00 desc )where rownum=1) x  ,
     table(XMLSequence(extract(x.GXSXJH,'/custom/input-faxq'))) i)b
     where a.ID0000=b.ID0000;

--------------获取治疗项目医嘱
cursor CUR_ZLXMJC is
select a.ZLXMID,a.ZLXMJC from (select distinct B.ZLXMID, B.ZLXMJC from sd_hospital.SF_BRXXB0 A, sd_hospital.YJ_YW0000 B, sd_hospital.BM_ZLZD00 C where A.GHH000 = B.ZYHGHH and B.ZLXMID = C.ZLXMID and C.LBBH00 = 5
and A.GHH000 = P_GHH000)a,
(select extractValue(value(i),'/input-checkbox') ZLXMID
     from ( select * from (select * from sd_zlglxt.ZL_ZLJLB0 where MZH000=P_GHH000 and sfsc00='1' order by CJSJ00 desc )where rownum=1) x  ,
     table(XMLSequence(extract(x.GXSXJH,'/custom/input-checkbox'))) i)b
     where a.ZLXMID=b.ZLXMID
;

begin
-------获取治疗项目ID
select distinct B.ID0000 into V_ZLXMID
--, B.ZLFAID, B.ZLFAMC,C.GHH000
from SD_ZLGLXT.ZL_FADJ00 A, SD_ZLGLXT.ZL_FADJFA B, SF_BRXXB0 C
where A.ID0000 = B.FADJID
and A.HZID00 = C.BRID00
and C.GHH000 = P_GHH000
and A.SFSC00 = '1'
and A.SFQY00 = '1'
and A.MZH000 = P_GHH000;
--------获取治疗方案
select distinct B.ZLFAMC into ZLFAMC
--, B.ZLFAID, B.ZLFAMC,C.GHH000
from SD_ZLGLXT.ZL_FADJ00 A, SD_ZLGLXT.ZL_FADJFA B, SF_BRXXB0 C
where A.ID0000 = B.FADJID
and A.HZID00 = C.BRID00
and C.GHH000 = P_GHH000
and A.SFSC00 = '1'
and A.SFQY00 = '1';
  V_RETURN := V_RETURN||'治疗计划'||chr(10);
  V_RETURN := V_RETURN||'治疗方案:'||ZLFAMC||chr(10);
for ITEM in CUR_ZLXMMX loop

 -- if ITEM.MC0000 is not null then
          V_RETURN := V_RETURN || ITEM.MC0000|| '、'||chr(10);
        --  V_RETURN := item.MC0000;
    --    end if;
 end loop;
--  V_RETURN:= V_RETURN||'医嘱内容'||chr(10);
--for ITEM in CUR_ZLXMJC loop

--if ITEM.ZLXMJC is not null then
--             V_RETURN := V_RETURN ||ITEM.ZLXMJC|| '、'||chr(10);
--end if;
--end loop;


return V_RETURN;

end ;
/