create or replace view vw_yf_zybydymx
(ypqlpc, ypmc00, ypgg00, csl000, pcmc00, ypzsl0, jyzsl0, sfzsl0, jczsl0, ghzsl0, qldw00, ypyf00, lsdj00, ypje00, ts0000, yyrq00, ysxm00, ckdh00, brid00, zyid00, zyh000, xm0000, jldw00, yzid00, yzmxid, xmyfmc, zcxsl0, cxcsl0, fyksrq, fyjzrq, sfzdby, bqbmbh, ch0000, xb0000, bqmc00, ksmc00, nl0000, zdmc00, fyrqsj, bytjmc, shrxm0, cfyzid, mxtyje, zhl000, ypnm00, ypzlxx, yznr01, fyrxm0, ypdlbh, yflbid, zydjs0, mjbz00, chzyh0, pc0000, ypzl01, yfmc00, sqysxm, yyzf00, clz000, sfbd00, qwm000, sccjzw, yymc00, fzzd00, dyrq00, dysj00, fyrxm1, csl100, pysm00, gzdj00, zqcs00, yyts00, pdxh00, csl200, lbbh00, kcdwsl, csl001, ysxm01, zqdw00, csl002, cslnsb, cxslns, xmyf01, brzjbh, scph00, sjdj00, mzdj00, dmjbh0, cfdgs0)
as
select a.YPQLPC,
       i.YPMC00,
       i.YPGG00,
       decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)||a.JLDW00 CSL000,
       a.PCMC00,
       a.YPZSL0 YPZSL0,
       nvl(a.JYZSL0,0) JYZSL0,  --借药总数量(一张汇总单里面同一个药品只有一行有记录)
       a.SFZSL0 SFZSL0,
       0 JCZSL0,
       a.GHZSL0 GHZSL0,
       a.QLDW00,
       a.YPYFMC||' '||a.YFSMMC YPYF00,
       a.LSDJ00,
       round(a.YPZSL0*a.LSDJ00,2) YPJE00,
       (select nvl(max(ZQS000),1) from BQ_YPYZ00 where YZID00=a.YZID00) TS0000,
       substr(a.YYRQ00,1,4)||'-'||substr(a.YYRQ00,5,2)||'-'||substr(a.YYRQ00,7,2),
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0) YSXM00,
       a.CKDH00,
       b.BRID00,
       b.ZYID00,  --ZYID00
       b.ZYH000,  --ZYH000
       b.XM0000,  --BRXM00
       a.JLDW00,
       a.YZID00,
       a.YZMXID,
       a.XMYFMC,
       round(a.ZCXSL0*a.ZYTS00,2) as ZCXSL0,
       decode(a.ZCXSL0,0,null,'(退'||to_char(round(a.ZCXSL0/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXCSL0,
       to_char(to_date(A.YYRQ00,'YYYYMMDD'),'Mondd')||'日' FYKSRQ, --服药开始日期
       to_char(to_date(A.YYRQ00,'YYYYMMDD') + nvl(A.ZYTS00,1) - 1,'Mondd')||'日' FYJZRQ, --服药截止日期
       a.BYTJ00 SFZDBY,
       a.BQBMBH,
       a.CH0000,
       b.XB0000,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.BQBMBH) BQMC00,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.DQKS00) KSMC00,
       substr(trim(SF_XT_CSRQTONL(b.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000,
       substr(trim(sf_yf_getzybrzdxx(b.ZYID00,'1')),1,100) ZDMC00,
       substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ,
       (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJMC,
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.SHR000) SHRXM0,
       a.CFYZID,
       round(a.ZCXSL0*a.LSDJ00,2) MXTYJE,
       a.ZHL000,
       a.YPNM00,
       substr(SF_YF_YPSLZH(a.YPZSL0*a.ZHL000,a.YPNM00,3),1,50) YPZLXX,
       nvl((select YZNR01 from bq_ypyzmx where yzmxid=a.yzmxid),a.YPYFMC) as YZNR01, --显示字段2(用法)
       (select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM0,
       a.YPDLBH,
       a.YFLBID,
       a.zydjs0||'剂,代煎费'||(select hjje00 from zy_fymx00 where djh000=a.FJFDJH and XMBH00='25730') zydjs0,
       (select decode(DPDJ00||MZDJ00||decode(SJDJ00,'0','0','1','1','2'),'000','0','002','2','1') from BM_YD0000 where YPNM00=a.YPNM00) as MJBZ00,  --1毒麻,2精二
       a.CH0000||b.ZYH000  CHZYH0,
       (select nvl(max(sm0000),'') from bm_pc0000 where pcid00=a.pcid00 and rownum=1) pc0000,
       a.YPZSL0 YPZL01,
       (select bmmc00 from bm_bmbm00 where bmbh00=a.yfbmbh) yfmc00,
       (select g.zwxm00 from bq_ypyz00 f,bm_ygbm00 g where f.sqysbh=g.ygbh00 and a.yzid00=f.yzid00 and tsys00 in(6,7)and rownum=1) sqysxm, --双签医生签名
       (select yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐
       decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000,  --长短嘱
       decode(nvl(h.DYCS00,0),0,'','补打') SFBD00,
       (select QWM000 from YF_YPKCXX where YPNM00=i.YPNM00 and qwm000 is not null and yfbmbh=a.yfbmbh and rownum =1 ) QWM000,  --区位号
       (select SCCJZW from BM_YPSCCJ where SCCJBH = i.SCCJBH ) SCCJZW, --生产厂家
       (select yyQC00 from xt_yyxx00 where yyid00= (select YYID00 from bm_bmbm00 where bmbh00=a.yfbmbh and rownum=1) AND ROWNUM=1) YYMC00,  --医院名称
       A.CH0000||B.ZYID00  FZZD00,  --分组字段
       A.DYRQ00,
       A.DYSJ00,
       (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM1, --发药人姓名不带胸卡号
       substr(SF_YF_GETYPKCZLXX(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000),12,i.K2J000,i.ZJJL2J,i.KCDW00,i.ZJJLDW,i.JLDW00)||i.ZJJLDW,1,20) CSL100,   --次剂量显示中间计量单位
       i.PYSM00,
       (select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00,
       (select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) zqcs00,   --周期次数
       round(((a.ypzsl0*a.zhl000)/(a.csl000*(select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1))),0) yyts00, --用药天数
       (select pdxh00 from YF_YPKCXX x where x.yfbmbh=a.yfbmbh and x.ypnm00=a.ypnm00 and rownum=1) pdxh00, --盘点序号 - 药房维护的
       to_char((case when trim(i.ZYFYDW) in ('片','粒') then round(a.CSL000/i.Z2J000,2) else a.CSL000 end),'fm9990.00')||(case when trim(i.ZYFYDW) in ('片','粒') then trim(i.ZYFYDW) else trim(i.JLDW00) end) CSL200, --次剂量显示中间计量单位2
       i.LBBH00,
       case when instr(rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$','')),'.')>0 then
        YPZSL0||QLDW00
       else
         rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$',''))|| trim(I.KCDW00)
       end KCDWSL,
       case when a.JLDW00=I.ZJJLDW then
           ''
       else
          '('||rtrim(regexp_replace(to_char(A.CSL000/I.ZJJL2J,'FM9999990.99'),'\.$',''))|| trim(I.ZJJLDW)||')'
       end CSL001,
       decode((select count(1) from bq_ypyz00 f where a.yzid00=f.yzid00 and tsys00 >5),0,'',(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0 )) YSXM01,
       (select  zqdw00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) ZQDW00,
       decode (i.lbbh00,'2',to_char(round(a.CSL000,1)),decode (sign (A.CSL000-1),-1,to_char (a.CSL000,'FM99990.99999' ),A.CSL000))||a.JLDW00 CSL002,
       round(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)*nvl(i.nsbl00,1),1)||a.JLDW00 CSLNSB,
       decode(a.ZCXSL0*nvl(i.nsbl00,1),0,null,'(退'||to_char(round(a.ZCXSL0*nvl(i.nsbl00,1)/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXSLNS,
       decode(a.xmyfmc,'','', '('||a.XMYFMC||')') XMYF01,
       --i.dmjbh0  dmjbh0,
       nvl(c.brzjbh,'空') brzjbh,
       ( select M.SCPH00 from YF_YPCKMX M where M.CKDH00 = h.CKDH00 and M.YPNM00 = A.YPNM00 and rownum = 1 ) SCPH00,
       i.SJDJ00,
       i.MZDJ00 ,i.DMJBH0,
       (select (case when MZDJ00 <> 0 then '麻、精一' when SJDJ00 = 1 then '麻、精一' when SJDJ00 = 2 then '精二' when DPDJ00 <> 0 then '毒' else '' end) from bm_yd0000 where a.ypnm00 = ypnm00) cfdgs0
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i,bm_brxxb0 c
where a.ZYID00=b.ZYID00 and a.CKDH00=h.CKDH00
  and a.YPNM00=i.YPNM00
  and c.brid00 = b.brid00
  and not exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00)
  union all
select
       a.YPQLPC,
       (select ypmc00 from BM_YD0000 where ypnm00=d.MXYPNM) ypmc00,
       (select YPGG00 from BM_YD0000 where ypnm00=d.MXYPNM) YPGG00,
       trim(decode(substr(abs(d.YPSL00*d.ZHXS00),1,1),'.','0','')||to_char(round(d.YPSL00*d.ZHXS00,2)))||
         (select JLDW00 from bm_yd0000 where ypnm00=d.MXYPNM) CSL000,
       a.PCMC00,
       a.YPZSL0 YPZSL0,
       nvl(a.JYZSL0,0) JYZSL0,  --借药总数量(一张汇总单里面同一个药品只有一行有记录)
       a.SFZSL0 SFZSL0,
       0 JCZSL0,
       a.GHZSL0 GHZSL0,
       a.QLDW00,
       a.YPYFMC||' '||a.YFSMMC YPYF00,
       a.LSDJ00,
       round(a.YPZSL0*a.LSDJ00,2) YPJE00,
       (select nvl(max(ZQS000),1) from BQ_YPYZ00 where YZID00=a.YZID00) TS0000,
       substr(a.YYRQ00,1,4)||'-'||substr(a.YYRQ00,5,2)||'-'||substr(a.YYRQ00,7,2),
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0) YSXM00,
       a.CKDH00,
       b.BRID00,
       b.ZYID00,  --ZYID00
       b.ZYH000,  --ZYH000
       b.XM0000,  --BRXM00
       a.JLDW00,
       a.YZID00,
       a.YZMXID,
       a.XMYFMC,
       round(a.ZCXSL0*a.ZYTS00,2) as ZCXSL0,
       decode(a.ZCXSL0,0,null,'(退'||to_char(round(a.ZCXSL0/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXCSL0,
       to_char(to_date(A.YYRQ00,'YYYYMMDD'),'Mondd')||'日' FYKSRQ, --服药开始日期
       to_char(to_date(A.YYRQ00,'YYYYMMDD') + nvl(A.ZYTS00,1) - 1,'Mondd')||'日' FYJZRQ, --服药截止日期
       a.BYTJ00 SFZDBY,
       a.BQBMBH,
       a.CH0000,
       b.XB0000,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.BQBMBH) BQMC00,
       (select bmmc00 from BM_BMBM00 where bmbh00=a.DQKS00) KSMC00,
       substr(trim(SF_XT_CSRQTONL(b.CSRQ00,to_char(sysdate,'YYYYMMDD'))),1,20) NL0000,
       substr(trim(sf_yf_getzybrzdxx(b.ZYID00,'1')),1,100) ZDMC00,
       substr(h.QRRQ00,1,4)||'-'||substr(h.QRRQ00,5,2)||'-'||substr(h.QRRQ00,7,2)||' '||h.QRSJ00 FYRQSJ,
       (select MC0000 from YF_BYTJ00 where id0000=h.BYTJID) BYTJMC,
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.SHR000) SHRXM0,
       a.CFYZID,
       round(a.ZCXSL0*a.LSDJ00,2) MXTYJE,
       a.ZHL000,
       d.MXYPNM,
       substr(SF_YF_YPSLZH(a.YPZSL0*a.ZHL000,a.YPNM00,3),1,50) YPZLXX,
       nvl((select YZNR01 from bq_ypyzmx where yzmxid=a.yzmxid),a.YPYFMC) as YZNR01, --显示字段2(用法)
       (select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM0,
       a.YPDLBH,
       a.YFLBID,
       a.ZYDJS0||'剂,代煎费'||(select HJJE00 from ZY_FYMX00 where DJH000=a.FJFDJH and XMBH00='25730') ZYDJS0,
       (select decode(DPDJ00||MZDJ00||decode(SJDJ00,'0','0','1','1','2'),'000','0','002','2','1') from BM_YD0000 where YPNM00=a.YPNM00) as MJBZ00,  --1毒麻,2精二
       a.CH0000||b.ZYH000  CHZYH0,
       (select nvl(max(sm0000),'') from bm_pc0000 where pcid00=a.pcid00 and rownum=1) pc0000,
       a.YPZSL0 YPZL01,
       (select bmmc00 from bm_bmbm00 where bmbh00=a.yfbmbh) yfmc00,
       (select g.zwxm00 from bq_ypyz00 f,bm_ygbm00 g where f.sqysbh=g.ygbh00 and a.yzid00=f.yzid00 and tsys00 in(6,7)and rownum=1) sqysxm, --双签医生签名
       (select yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐
       decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000,  --长短嘱
       decode(nvl(h.DYCS00,0),0,'','补打') SFBD00,
       (select QWM000 from YF_YPKCXX where YPNM00=i.YPNM00 and qwm000 is not null and yfbmbh=a.yfbmbh and rownum =1 ) QWM000,  --区位号
       (select SCCJZW from BM_YPSCCJ where SCCJBH = i.SCCJBH ) SCCJZW, --生产厂家
       (select yyQC00 from xt_yyxx00 where yyid00= (select YYID00 from bm_bmbm00 where bmbh00=a.yfbmbh and rownum=1) AND ROWNUM=1) YYMC00,  --医院名称
       A.CH0000||B.ZYID00  FZZD00,  --分组字段
       A.DYRQ00,
       A.DYSJ00,
       (select ZWXM00 from BM_YGBM00 where YGBH00=h.QRR000) FYRXM1, --发药人姓名不带胸卡号
       trim(substr(abs(SF_YF_GETYPKCZLXX(d.YPSL00,12,i.K2J000,i.ZJJL2J,i.KCDW00,i.ZJJLDW,i.JLDW00))||i.ZJJLDW,1,20)) CSL100,    --次剂量使用
       i.PYSM00,
       (select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00,
       (select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) zqcs00,  --周期次数
       round(((a.ypzsl0*a.zhl000)/(a.csl000*(select  zqcs00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1))),0) yyts00, --用药天数
       (select pdxh00 from YF_YPKCXX x where x.yfbmbh=a.yfbmbh and x.ypnm00=a.ypnm00 and rownum=1) pdxh00, --盘点序号 - 药房维护的
       to_char((case when trim(i.ZYFYDW) in ('片','粒') then round(a.CSL000/i.Z2J000,2) else a.CSL000 end),'fm9990.00')||(case when trim(i.ZYFYDW) in ('片','粒') then trim(i.ZYFYDW) else trim(i.JLDW00) end) CSL200, --次剂量显示中间计量单位2
       i.LBBH00,
       case when instr(rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$','')),'.')>0 then
        YPZSL0||QLDW00
       else
         rtrim(regexp_replace(to_char(A.YPZSL0*A.ZHL000/I.K2J000,'FM9999990.99'),'\.$',''))|| trim(I.KCDW00)
       end KCDWSL,     --库存单位数量
       case when (select JLDW00 from BM_YD0000 where YPNM00=d.MXYPNM)=I.ZJJLDW then
           ''
       else
          '('||rtrim(regexp_replace(to_char(d.YPSL00*d.ZHXS00/I.ZJJL2J,'FM9999990.99'),'\.$',''))|| trim(I.ZJJLDW)||')'
       end CSL001,
       decode((select count(1) from bq_ypyz00 f where a.yzid00=f.yzid00 and tsys00 >5),0,'',(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0 ))YSXM01,
       (select  zqdw00 from bm_pc0000 where pcid00=a.pcid00 and rownum=1) ZQDW00,
       decode (i.lbbh00,'2',to_char(round(a.CSL000,1)),trim(decode(substr(abs(d.YPSL00*d.ZHXS00),1,1),'.','0','')||to_char(round(d.YPSL00*d.ZHXS00,2))))||(select JLDW00 from bm_yd0000 where ypnm00=d.MXYPNM) CSL002,
       round(decode(sign(a.CSL000-1),-1,to_char(a.CSL000,'FM99990.99999'),a.CSL000)*nvl(i.nsbl00,1),1)||a.JLDW00 CSLNSB,
       decode(a.ZCXSL0*nvl(i.nsbl00,1),0,null,'(退'||to_char(round(a.ZCXSL0*nvl(i.nsbl00,1)/a.ZYTS00,4),'FM99990.99999')||a.QLDW00||')') as CXSLNS,
       decode(a.xmyfmc,'','', '('||a.XMYFMC||')') XMYF01,
       --i.dmjbh0  dmjbh0,
       nvl(c.brzjbh,'空') brzjbh,
       ( select M.SCPH00 from YF_YPCKMX M where M.CKDH00 = h.CKDH00 and M.YPNM00 = A.YPNM00 and rownum = 1 ) SCPH00,
       i.SJDJ00,
       i.MZDJ00,DMJBH0,
       (select (case when MZDJ00 <> 0 then '麻、精一' when SJDJ00 = 1 then '麻、精一' when SJDJ00 = 2 then '精二' when DPDJ00 <> 0 then '毒' else '' end) from bm_yd0000 where a.ypnm00 = ypnm00) cfdgs0
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i,BM_XDCFWH d,bm_brxxb0 c
where a.ZYID00=b.ZYID00 and a.CKDH00=h.CKDH00
  and a.YPNM00=i.YPNM00 and a.YPNM00=d.XDYPNM and d.XDYPNM=i.ypnm00 and b.brid00 = c.brid00
  and exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00)
;