CREATE OR REPLACE VIEW SD_HOSPITAL.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, TJRXM0, CFDGS0, DPDJ00, 
GWDJ00

 )
 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,
       a.JCZSL0 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, 
       (select ZWXM00 from BM_YGBM00 where h.TJR000 = YGBH00) TJRXM0,
       (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,
       i.dpdj00,i.gwdj00
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,
       a.JCZSL0 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,           
       (select ZWXM00 from BM_YGBM00 where h.TJR000 = YGBH00) TJRXM0,
       (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,
       i.dpdj00,i.gwdj00
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)
/