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,  --病人ID 
ZYID00,  --ZYID00
ZYH000,  --ZYH000
XM0000,  --BRXM00
JLDW00,  --剂量单位 
YZID00,  --医嘱ID
YZMXID,  --医嘱明细ID
XMYFMC,  --细目用法
ZCXSL0,  --总冲销数量
CXCSL0,  --冲销次数量
FYKSRQ,  --服药开始日期
FYJZRQ,  --服药截止日期
SFZDBY,  --摆药途径 '0'手工'1'自动摆药'2'静配'3'干预为手工
BQBMBH,  --病区部门编号
CH0000,  --床号
XB0000,  --性别
BQMC00,  --病区名称
KSMC00,  --科室名称
NL0000,  --年龄
ZDMC00,  --诊断名称
FYRQSJ,  --摆药日期时间
BYTJMC,  --摆药条件
SHRXM0,  --审方人姓名
CFYZID,  --处方医嘱ID(门诊药房打印处方编号)
MXTYJE,  --明细退药金额
ZHL000,  --转换率
YPNM00,  --药品内码
YPZLXX,  --药品总量信息
YZNR01,  --显示字段2(用法)
FYRXM0,  --发药人姓名
YPDLBH,
YFLBID,
ZYDJS0,
MJBZ00,  --1毒麻,2精二
chzyh0,
PC0000,
YPZL01,
YFMC00,  --药房名称
SQYSXM,  --双签医生签名
YYZF00,  --嘱咐
CLZ000,  --长短嘱
SFBD00,  --是否补打
QWM000,  --区位号
SCCJZW,  --生产产家
YYMC00,  --医院名称
FZZD00,  --分组字段
DYRQ00,  --打印日期
DYSJ00,  --打印时间
FYRXM1,  --发药人姓名（不包含胸卡号）
CSL100,  --次剂量（中间计量单位）
PYSM00,
GZDJ00,
ZQCS00, --周期次数
YYTS00   --用作出院带药计算用药天数
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- gzj         2018.08.03  create for YF9-20180803-003
-- gzj         2018.08.06  允许打印冲销单据 for YF9-20180712-001
-- gzj         2018.01.03  整合省人民的视图内容 for YF9-20180919-001
-- jiangzy     2019.07.09  视图vw_yf_zybydymx增加PC0000和YPZL01字段 for YF9-20190708-002
-- gzj         2019.07.11  修改TS0000字段取值 YF9-20190710-001
-- gzj         2019.08.07  增加字段YFMC00 for YF9-20190731-002
-- liwm        2019.10.18  省人民增加双签医生签名sqysxm以及沙县增加嘱咐yyzf00、长短嘱clz000; for YF9-20191017-003
-- linshu      2019.11.21  增加了是否补打字段 for YF9-20191120-002 
-- linshu      2019.12.09  增加了区位号:QWM000;生产产家：SCCJZW; by YF9-20191121-001
-- chb         2020.01.06  增加了医院名称:YYMC00;分组字段：FZZD00; for YF9-20191226-003
-- luowf       2020.04.13  工程xzh归档增加DYRQ00,DYSJ00,FYRXM1  for  	YF9-20200409-001 
-- luowf       2020.04.16  增加字段次剂量CSL100 采用中间计量单位显示数量 for 	YF9-20200413-002 
-- luowf       2020.04.20  增加字段拼音首码PYSM00    for 	YF9-20200413-004  
-- linshu      2020.08.19  增加字段GZDJ00 by YF9-20200816-001  
-- ---------   ----------  ------------------------------------------- 
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 --用药天数
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  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 --用药天数
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)