CREATE OR REPLACE VIEW VW_YF_ZYBYDYDJ AS
select
  a.ZYDJS0,
  a.CKDH00,
  c.BRID00,
  f.BRBLH0,
  f.BRJTDZ,
  (select BMMC00 from bm_bmbm00 where bmbh00=a.SSKS00) KSMC00,
  (select BMMC00 from bm_bmbm00 where bmbh00=a.BQBMBH) BQMC00,
  a.BRXM00,
  a.CH0000,
  c.XB0000,
  substr(trim(SF_XT_CSRQTONL(c.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000,
  substr(trim(SF_XT_GETZYZD(c.ZYID00)),1,40) ZDMC00,
  a.ZYH000,
  f.BRDH00,
  substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ,
  (select ZWXM00 from BM_YGBM00 where ygbh00=a.ysgzh0)  YSXM00,
  (select ZWXM00 from BM_YGBM00 where YGBH00=h.HDR000)  SHRXM0,
  (select ZWXM00 from BM_YGBM00 where YGBH00=h.TJR000)  TJRXM0,
  (select ZWXM00 from BM_YGBM00 where YGBH00=h.HDR000)  HDRXM0,
  (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000)  FYRXM0,
  (select decode(M.YBJGJC,N.BRLBJC,M.YBJGJC,M.YBJGJC||'('||N.BRLBJC||')')
    from IC_YBBRLB M,BM_YBBRLB N where f.FBBH00=M.FBBH00 and f.YBLB00=M.YBLB00
     and f.YBBRLB=N.YBBRLB and f.FBBH00=N.FBBH00 and M.YBZXLB=N.YBZXLB and ROWNUM=1) FBMC00, --费别名称
  (select BMMC00 from bm_bmbm00 where bmbh00=a.YFBMBH) YFMC00,
  (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJ00,
  a.CFYZID,
  '1'||decode(mod(length(a.CFYZID),2),0,'0','')||to_char(a.CFYZID) as YZTXM0,
  a.BYTJ00 SFZDBY,
  (select Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='2' and rownum=1) RYZD00,
  (SELECT Trim(to_char(wm_concat(ZDMC00))) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00 in ('h','i') ) ZYZD00,--中药诊断
  a.ZYID00,
  h.FYDLXH,
  decode(sign(nvl(h.DYCS00,0)-1),1,'重制','') as DYCZ00,
  decode(upper(substrb(a.PCMC00,1,3)),'S.T','急','') as KFKSMC,
  A.ZYTS00*SF_PY_XDCFZYTS(a.YPQLPC,1) ZYTS00, --中药贴数
  ' ' TYJE00,
  decode(d.LBBH00,2,a.YPYFMC,a.ZJYFMC) ZJYFMC,
  substrb(SF_YF_GetSFSDJF(2, A.YZID00),1,10) ZYDJF0, --中药代煎费
  decode(a.YPDLBH,'2',(select JYFSMC from YS_JYFS00 AA,BQ_YPYZ00 BB where AA.BH0000=BB.JYFS00 and BB.YZID00=a.YZID00),'') JYFS00, --煎药方式
  case when a.YPDLBH='2' and (d.YPCZFL='2' or a.ZYDJS0>0) then '早晚各1包'
  else '分两次' end as ZYMRCS,--中药每日次数
  case when a.YPDLBH='2' and d.YPCZFL='2' then '每日1剂(2包)'
  else '每日1剂' end as ZYJS00, --中药剂数
  decode(d.LBBH00,2,1,0) DYMB00,      --打印模板
  decode(a.YPDLBH,2,a.YFSMMC,' ') ZYYFSM,  --中药嘱咐
  decode(a.YPDLBH,2,decode(sign(a.ZYDJS0),1,'代煎',decode(d.YPCZFL,'2','全成分',' ')),' ') as ZYDJCZ,  --中药代煎材质分类
  decode(d.LBBH00,2,SF_YF_CFYYZFMXBZ(a.YZID00),'') as CFMXBZ,  --处方明细说明
  'Z'||decode(mod(length(a.CFYZID),2),0,'0','')||to_char(a.CFYZID) as YZTXM1,
  (select BMMC00 from bm_bmbm00 where bmbh00=a.DQKS00) DQKSMC,
  a.YZID00,
  a.CXBZ00,
  (SELECT Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='g' and rownum=1) as RYZYZD,   --入院中医诊断
  decode((select max(ZDMC00) from BQ_YPYZ00 where YZID00=a.YZID00),'',nvl(a.zdmc00,(select Trim(ZDMC00) from BQ_BRZDXX WHERE ZYID00=c.ZYID00 and ZDLB00='2' and rownum=1)),(select max(ZDMC00) from BQ_YPYZ00 where YZID00=a.YZID00)) as DMZD00,
  decode(nvl(h.DYCS00,0),0,'','补打') SFBD00,

 
  (select ZWXM00||yszjhm from BM_YGBM00 where YGBH00=h.QRR000)  fyzjxm,
   (select ZWXM00||yszjhm from BM_YGBM00 where YGBH00=h.HDR000)  shzjxm
from
YF_YZYPSQ a,ZY_BRXXB0 c,BM_YD0000 d,BM_BRXXB0 f,YF_YPCKD0 h
where a.ZYID00=c.ZYID00 and a.YPNM00=d.YPNM00
and c.BRID00=f.BRID00 and a.CKDH00=h.CKDH00;