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 dxsybz from bm_yd0000 where ypnm00=a.ypnm00)||(select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00, --修改内容1
       (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
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i
where a.ZYID00=b.ZYID00 and a.CKDH00=h.CKDH00
  and a.YPNM00=i.YPNM00
  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 dxsybz from bm_yd0000 where ypnm00=a.ypnm00)||(select GZDJ00 from YF_YPKCXX where YPNM00=a.YPNM00 and yfbmbh=h.yfbmbh ) GZDJ00,--修改内容2
       (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
from YF_YZYPSQ a,ZY_BRXXB0 b,YF_YPCKD0 h,BM_YD0000 i,BM_XDCFWH d
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 exists (select 1 from BM_XDCFWH where XDYPNM=a.YPNM00)


--跟踪语句
select rownum XH,c.* from (select a.YPMC00,a.YPGG00,a.YPJE00,b.PYSM00,(select SCCJZW from BM_YPSCCJ where SCCJBH = b.SCCJBH) SCCJZW,
 substr(SF_YF_YPSLZH(a.YPZSL0,a.YPNM00,3),1,50) YPZSL0, substr(SF_YF_YPSLZH(a.SFZSL0,a.YPNM00,3),1,50) SFZSL0,decode(a.JYZSL0,0,'',
 substr(SF_YF_YPSLZH(a.JYZSL0,a.YPNM00,3),1,50)) JYZSL0, decode(a.GHZSL0,0,'',substr(SF_YF_YPSLZH(a.GHZSL0,a.YPNM00,3),1,50)) GHZSL0,
 decode(a.SYJYZL,0,'',substr(SF_YF_YPSLZH(a.SYJYZL,a.YPNM00,3),1,50)) SYJYZL, decode(a.JCZSL0,0,'',substr(SF_YF_YPSLZH(a.JCZSL0,a.YPNM00,3),1,50)) 
 JCZSL0,a.LSDJ00,a.QLDW00,a.YPZSL1,   (a.SFZSL0/b.K2J000)  SFZSL1, substr(SF_YF_YPSLZH(a.ZCXSL0,a.YPNM00,3),1,50) ZCXSL0, 
   substr(SF_YF_YPSLZH(a.SFZSL0,a.YPNM00,2),1,50) SFXSL0,substr(sf_yf_ypslzh(a.SFZSL0*b.K2ZJJL,a.YPNM00,1),1,50)||b.ZJJLDW JJSL00,QWM000,
   a.GZDJ00,b.ypbm00,a.PDXH00 from (select YPNM00,YPMC00,YPGG00,LSDJ00,QLDW00,PYSM00,SUM(YPZSL0) YPZSL1,SUM(YPZSL0*ZHL000) 
   YPZSL0,SUM((YPZSL0-nvl(JCZSL0,0)-nvl(GHZSL0,0))*ZHL000) SFZSL0,SUM(ZCXSL0*ZHL000) ZCXSL0, sum(JYZSL0*ZHL000) JYZSL0,sum(nvl(GHZSL0,0)*ZHL000)
    GHZSL0,SUM((JYZSL0-nvl(GHZSL0,0))*ZHL000) SYJYZL,sum(nvl(JCZSL0,0)*ZHL000) JCZSL0,SUM(YPJE00) YPJE00,1 as QWM000,PYSM00,GZDJ00 
    ,max(PDXH00) PDXH00 from VW_YF_ZYBYDYMX a where  SFZDBY<>'1' and CKDH00= '1976357' GROUP BY YPNM00,YPMC00,YPGG00,LSDJ00,QLDW00,PYSM00,GZDJ00 ) 
    a,bm_yd0000 b where a.ypnm00=b.ypnm00 order by a.QWM000,b.DXSYBZ,b.LBBH00,b.JXBH00,b.YPBM00) c   order by c.GZDJ00;