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,yyzf00,clz000) 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 yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐20191017 decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000 --长短嘱20191017 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 yyzf00 from BQ_YPYZ00 where yzid00=a.yzid00) yyzf00,--嘱咐20191017 decode(a.clbz00,'0','长嘱','1','短嘱' ) clz000 --长短嘱20191017 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) ;