create or replace view vw_zs_yzdy00_new
(zyid00, yexm00, lb0000, clbz00, dmbz00, yzlb00, yzzt00, yzid00, yzmxid, qysj00, qyyshs, yfnr00, yzxx00, lylxx0, tysj00, tyyshs, bz0000, qyrq00, tzrq00, pxsj00, zxrq00, tqsj00, lrrq00, lrsj00, dypc00, bqh000, hdryxm, yzdynr, qysj01, tqsjfs, zxrqsc, zxsjsc, tzdypc, kzys00, hdrxm0, tzys00, tzrxm0, zyts00, ypczfl, zt0000, yzdy01, yzdy02, blbz00, month0, day000, time00, bmmc00, xm0000, xb0000, rycwh0, zyh000, nl0000, czpc00, ths000, tm0000, tt0000, zxmd00, zxhdsj, lzday0, lmonth, kzysqm, tzysqm, kzqmct, tzqmct, zxmont, zxdate, zxsj00, tzmont, tzdate, tzsj00, zxhsqm, zxqmct, tzhsqm, tzhsct, tzhsxm)
as
--2018.04.18 把药品取消字移到最前面
select     A.ZYID00,                              --住院ID
               A.YEXM00,                              --婴儿姓名
               '0',                                   --药品
               A.CLBZ00,                              --处理标志长期'0'短期'1'
               A.DMBZ00,                              --毒麻标志'0'正常'1'毒麻
               to_number(A.YPLBBH),                   --药品类别
               decode(A.YZZT00,'2',decode(sign(to_number(A.ZXRQ00)-to_number(to_char(sysdate,'YYYYMMDD'))),0,'9',1,'9','2'),A.YZZT00),      --医嘱状态
               A.YZID00,                              --医嘱ID
               B.YZMXID,                              --医嘱明细ID
               substr(A.QYRQ00,3,2)||'.'||substr(A.QYRQ00,5,2)||'.'||substr(A.QYRQ00,7,2)||' '||
               substr(A.QYSJ00,1,5), --substr(A.LRSJ00,1,6),
               --decode(sign(to_number(substr(A.LRSJ00,1,2))-12),1,to_char(to_number(substr(A.LRSJ00,1,2))-12)||'Pm',substr(A.LRSJ00,1,2)||'Am'),--起用日期+起用时间
               trim(C.ZWXM00)||'/'||trim(NVL(A.HDRXM0,'')),                        --开嘱医生
               --用法信息
               decode(A.YPLBBH,'2',to_char(A.ZQCYTS)||'贴','') --草药贴数
                   ||decode(A.YPLBBH,'2',decode(A.JYCS00,0,'','代煎'||to_char(A.JYCS00)||'贴'),'') --草药煎药次数
                   ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次
                   ||trim(A.YPYFMC) --用法
                   ||decode(A.CLBZ00,'0','',decode(A.ZQS000,1,'',to_char(A.ZQS000)||trim(A.ZQDW00))),--短期周期数
               --药品信息
               trim(B.YPMC00)--药品名称
                   --||decode(b.YPGG00,'','','['||trim(B.YPGG00)||']')--药品规格
                   ||decode(B.MCSL00,0,'',decode(substr(to_char(B.MCSL00),1,1),'.','0','')||to_char(B.MCSL00)||trim(B.JLDW00)) --每次数量
                   ||trim(B.PSMC00)                      --皮试名称
                   ||'<sup>'||trim(B.XMYFMC)||'</sup>' --草药细目名称
                   ||decode(b.YZCLLB,'自备药','[备]','') --处理
                   ||decode(A.YPLBBH,'2','',trim(B.BZ0000)), --备注


/*               decode(A.CLBZ00,'0','',decode(A.YPLBBH,'2','',decode(B.LYL000,0,'[无]',
                   decode(substr(to_char(B.LYL000),1,1),'.','0','')||to_char(B.LYL000)||trim(B.ZYFYDW)))),--短期领药量*/

              /* decode(A.CLBZ00,'0','',decode(i.yflbid,0,decode(A.YPLBBH,'2',''
                ,decode(B.LYL000,0,'[无]',decode(substr(to_char(B.LYL000),1,1),'.','0','')||to_char(B.LYL000)||trim(B.ZYFYDW))),'')),--短期领药量,非口服不显示*/
                '',


               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间
               decode(trim(D.ZWXM00),'','',trim(D.ZWXM00)||'/')
                   ||trim(A.TZRXM0),           --停止医生+停止人姓名
               trim(A.YYZF00),                        --备注
               A.QYRQ00,                              --起用日期
               A.TZRQ00,                              --停止日期
               A.PXSJ00,                              --排序时间
               A.ZXRQ00,                              --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5), --提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00, --录入日期
               A.LRSJ00,  --录入时间
               DYPC00,--
               a.BQH000, --病区
               e.zwxm00,
               rpad(b.YZNR00,b.YZNRCD,' ')||nvl(b.YZNR02,b.YZNR01),
               substr(A.LRRQ00,3,2)||'.'||substr(A.LRRQ00,5,2)||'.'||substr(A.LRRQ00,7,2)||' '||
               substr(A.LRSJ00,1,5) QYSJ01, --启用时间（煤矿中心医院用）
               a.TQSJ00 TQSJFS, --提取时间
               a.zxrqsc,       --首次执行日期
               a.zxsjsc,        --首次执行时间
               nvl(a.TZDYPC,0), --停嘱打印频次
               trim(C.ZWXM00),  --开嘱医生
               trim(NVL(A.HDRXM0,'')),   --核对人姓名
               decode(trim(D.ZWXM00),'','',trim(D.ZWXM00)), --停止医生
               trim(A.TZRXM0), --停止人姓名
               '×'||a.zqcyts||'剂'
               ||'('|| to_char(to_date(A.YYRQ00,'yyyymmdd'),'fmmm')
               ||'.'|| to_char(to_date(A.YYRQ00,'yyyymmdd'),'fmdd') ||
               decode(a.zqcyts,1,')','-'
               || to_char(trunc(to_date(A.YYRQ00,'yyyymmdd'),'dd')+a.zqcyts-1,'fmmm') ||'.'
               || to_char(trunc(to_date(A.YYRQ00,'yyyymmdd'),'dd')+a.zqcyts-1,'fmdd')  ||')' ), --中药贴数
               h.ypczfl, --药品材质分类
               ' '||A.YYZF00||' '||A.YPYFMC, --嘱托 预留
              decode(a.yzzt00,'6','(取消)')|| sf_bq_yztsbz(b.yzmxid,0)||
               decode(b.YZCLLB,'自备药','[备]','')||
               B.YPMC00
                 ||decode(b.YPGG00,'','','['||trim(B.YPGG00)||']')--药品规格
                ||trim(B.PSMC00)
                ||DECODE(A.CLBZ00,'0','',DECODE(B.YPLBBH,2,'',DECODE(I.YFLBID,0,' '||B.LYL000||B.ZYFYDW,4,' '||B.LYL000||B.ZYFYDW,'')))||' '||
               DECODE(substr(to_char(B.MCSL00),1,1),'.','0','')||to_char(B.MCSL00)||
               B.JLDW00||' '||a.bz0000 ||decode(a.clbz00,'0','',decode(a.yzzt00,'6',substr(a.TZSJ00,0,5)||a.TZRXM0||'/'||(select ZWXM00 from BM_YGBM00 s where a.tzshr0=s.ygbh00))),
               nvl(b.yznr02,b.yznr01),
               '', --补录标志( 药品由过程带出 )
               substr(A.QYRQ00,5,2),   --月
               substr(A.QYRQ00,7,2),   --日
               substr(A.QYSJ00,1,5),    --时刻
               K.BMMC00,L.XM0000,L.XB0000,J.CH0000,L.ZYH000,sf_xt_csrqtonl(L.CSRQ00,L.RYRQ00)NL0000,
               a.CZPC00,a.TZRXM0  ,
               decode(a.YZZT00,'3',substr(A.TZRQ00,7,2)||'/ '||substr(A.TZRQ00,5,2),''),
               decode(a.YZZT00,'3',substr(A.TZSJ00,1,5),substr(nvl(A.YTZSJ0,A.TZSJ00),1,5)),
               decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,7,2)||'/ '||substr(A.HDRQSJ,5,2)) ,
               trim(decode(HDRQSJ,null,'',substr(HDRQSJ,9,6))),decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,7,2)),
               decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,5,2)),
               (select img000 from ZS_YSTPQM where ygbh00=c.ygbh00) KZYSQM,
               (select img000 from ZS_YSTPQM where ygbh00=d.ygbh00) TZYSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=c.ygbh00) KZQMCT,
               (select count(1) from ZS_YSTPQM where ygbh00=d.ygbh00) TZQMCT,
               substr(A.ZXRQ00,5,2) ZXMONT,   --月
               substr(A.ZXRQ00,7,2) ZXDATE,   --日
               substr(A.ZXSJ00,1,5) ZXSJ00,    --时刻
               substr(A.TZRQ00,5,2) TZMONT,   --月
               substr(A.TZRQ00,7,2) TZDATE,   --日
               substr(A.TZSJ00,1,5) TZSJ00,    --时刻
               (select img000 from ZS_YSTPQM where ygbh00=e.ygbh00) ZXHSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=e.ygbh00) ZXQMCT,
               (select img000 from ZS_YSTPQM where ygbh00=m.ygbh00) TZHSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=m.ygbh00) TZHSCT,
               m.ZWXM00 TZHSXM
      from BQ_YPYZ00 a,
           BQ_YPYZMX b,
           BM_YGBM00 c,
           BM_YGBM00 d,
           BM_YGBM00 e,
           BM_YD0000 h,
           BM_YPYF00 i,
           BQ_BRLDXX J,BM_BMBM00 K,ZY_BRXXB0 L,BM_YGBM00 m
      where b.YZID00 = a.YZID00
      and b.ypnm00=h.ypnm00
      and i.yfid00(+) = a.yfid00
      and c.YGBH00(+) = a.KZYS00 and e.YGBH00(+) = a.HDR000
      and d.YGBH00(+) = a.TZYS00
      AND  J.ZYID00=L.ZYID00
      AND J.KSH000=K.BMBH00
      AND A.ZYID00=J.ZYID00
      AND J.QSZT00 IN ('转入','新入','换入','入院','入科','迁入','择日')
      AND TO_DATE(NVL(J.JSRQ00,'99991231')||NVL(J.JSSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(J.qsczrq,'99991231')||NVL(J.qsczsj,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')<=0
      and ( (a.yzzt00 in ('0','1','2','3','5','6','8')) or (a.yzzt00='4' and a.dypc00<>0) )
      and m.YGBH00(+) = a.TZSHR0
union all
    select     A.ZYID00,  --住院ID
               A.YEXM00,  --婴儿姓名
               '1',       --非药品医嘱
               A.CLBZ00,  --处理标志长期'0'短期'1'
               '0',       --
               A.YZLB00,--医嘱类别
               decode(a.YZZT00,'2',decode(sign(to_number(a.TQRQ00)-to_number(to_char(sysdate,'YYYYMMDD'))),0,'9',1,'9','2'),a.YZZT00),      --医嘱状态
               A.YZMXID,--医嘱明细ID
               A.YZMXID,--医嘱明细ID
               substr(A.QYRQ00,3,2)||'.'||substr(A.QYRQ00,5,2)||'.'||substr(A.QYRQ00,7,2)||' '||
               substr(A.QYSJ00,1,5),--substr(A.LRSJ00,1,6),
               --decode(sign(to_number(substr(A.LRSJ00,1,2))-12),1,to_char(to_number(substr(A.LRSJ00,1,2))-12)||'Pm',substr(A.LRSJ00,1,2)||'Am'),--起用日期+起用时间
               trim(b.ZWXM00)||'/'||TRIM(NVL(a.HDRXM0,'')), --开嘱医生
               --用法信息
               '',
               --治疗项目信息
               trim(a.ZLXMJC)||''  --治疗项目名称
               ||decode(a.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理
               ||trim(a.PCMC00)||' ' --频次
               ||decode(a.MCSL00,1,'',decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000))||' ' --每次数量
               ||decode(a.CLBZ00,0,decode(a.TS0000,0,'',to_char(a.TS0000)||trim(a.TSDW00)),decode(a.TS0000,1,'',to_char(a.TS0000)||trim(a.TSDW00))),
               '',
               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间
               decode(trim(C.ZWXM00),'','',trim(C.ZWXM00)||'/')
                   ||trim(A.TZRXM0),                  --停止医生+停止人姓名
               trim(A.BZ0000),                             --备注
               A.QYRQ00,                   --起用日期
               A.TZRQ00,                   --停止日期
               A.PXSJ00,                   --排序时间
               A.TQRQ00,                   --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5),--提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00, --录入日期
               A.LRSJ00,  --录入时间
               DYPC00, --打印批次
               a.BQH000, --病区
               a.HDRXM0,
               --治疗项目信息
               trim(a.ZLXMJC)||' '  --治疗项目名称
               ||decode(a.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理
               ||trim(a.PCMC00)||' ' --频次
               ||decode(a.MCSL00,1,'',decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000))||' ' --每次数量
               ||decode(a.CLBZ00,0,decode(a.TS0000,0,'',to_char(a.TS0000)||trim(a.TSDW00)),decode(a.TS0000,1,'',to_char(a.TS0000)||trim(a.TSDW00)))
               ||decode((select trim(value0) from xt_xtcs00 where trim(name00)='BQ_YZNRSFDYBZ'),'Y',decode(a.bz0000,null,'','('||a.bz0000||')'),''),--备注
               substr(A.LRRQ00,3,2)||'.'||substr(A.LRRQ00,5,2)||'.'||substr(A.LRRQ00,7,2)||' '||
               substr(A.LRSJ00,1,5) QYSJ01, --启用时间（煤矿中心医院用）
               a.TQSJ00 TQSJFS, --提取时间
               a.zxrqsc,       --首次执行日期
               a.zxsjsc,        --首次执行时间
               nvl(a.TZDYPC,0), --停嘱打印频次
               trim(b.ZWXM00),  --开嘱医生
               TRIM(NVL(a.HDRXM0,'')),   --核对人姓名
               decode(trim(C.ZWXM00),'','',trim(C.ZWXM00)), --停止医生
               trim(A.TZRXM0), --停止人姓名
               '', --中药贴数
               '', --药品材质分类
               '', --嘱托 预留
                --诊疗项目信息
        trim(SF_ZS_GETFYYZMC_ZYDZBL(a.yzmxid))||' '  --治疗项目名称
               ||decode(a.yzlb00,'6','('|| to_number(substr(yyssrq,5,2))||'月'||to_number(substr(yyssrq,7,2))||'日'||substr(yysssj,0,5) ||' '||e.mzmc00 ||')','')--( 手术时间 麻醉名称)
               ||decode(a.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理
               ||trim(a.PCMC00)||' ' --频次
               ||decode(a.MCSL00,1,'',decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000))||' ' --每次数量
               ||decode(a.CLBZ00,0,decode(a.TS0000,0,'',to_char(a.TS0000)||trim(a.TSDW00)),decode(a.TS0000,1,'',to_char(a.TS0000)||trim(a.TSDW00)))
               ||decode((select trim(value0) from xt_xtcs00 where trim(name00)='BQ_YZNRSFDYBZ'),'Y',decode(a.bz0000,null,'','('||a.bz0000||')'),'')
               ||decode(a.clbz00,'1','',decode(sign(to_date(nvl(a.sjqsrq,a.qyrq00),'yyyyMMdd')-to_date(a.qyrq00,'yyyyMMdd')),1,'[首0]',''))
               ||decode(a.clbz00,'0','',decode(a.yzzt00,'6',a.tzrq00||''||substr(a.TZSJ00,0,5) ||''||a.TZRXM0||'/'||(select ZWXM00 from BM_YGBM00 s where a.tzshr0=s.ygbh00)))
               ||decode(instr(a.zlxmjc,'院内会诊'),1,d.bmmc00,''), --首0
               '',
               decode(a.blbz00,'Y','(补)',''),--补录标志
               substr(A.QYRQ00,5,2),   --月
               substr(A.QYRQ00,7,2),   --日
               substr(A.QYSJ00,1,5),    --时刻
               K.BMMC00,L.XM0000,L.XB0000,J.CH0000,L.ZYH000,sf_xt_csrqtonl(L.CSRQ00,L.RYRQ00)NL0000,
               a.CZPC00,a.TZRXM0
               , decode(a.YZZT00,'3',substr(A.TZRQ00,7,2)||'/ '||substr(A.TZRQ00,5,2),'')
               , decode(a.YZZT00,'3',substr(A.TZSJ00,1,5),substr(nvl(A.YTZSJ0,A.TZSJ00),1,5))
               , decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,7,2)||'/ '||substr(A.HDRQSJ,5,2))
               , trim(decode(HDRQSJ,null,'',substr(HDRQSJ,9,6)))
               , decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,7,2))
               , decode(a.HDRQSJ,null,'',substr(A.HDRQSJ,5,2)),
               (select img000 from ZS_YSTPQM where ygbh00=b.ygbh00) KZYSQM,
               (select img000 from ZS_YSTPQM where ygbh00=c.ygbh00) TZYSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=b.ygbh00) KZQMCT,
               (select count(1) from ZS_YSTPQM where ygbh00=c.ygbh00) TZQMCT,
               substr(A.TQRQ00,5,2) ZXMONT,   --月
               substr(A.TQRQ00,7,2) ZXDATE,   --日
               substr(A.TQSJ00,1,5) ZXSJ00,    --时刻
               substr(A.TZRQ00,5,2) TZMONT,   --月
               substr(A.TZRQ00,7,2) TZDATE,   --日
               substr(A.TZSJ00,1,5) TZSJ00,    --时刻
               (select img000 from ZS_YSTPQM where ygbh00=f.ygbh00) ZXHSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=f.ygbh00) ZXQMCT,
               (select img000 from ZS_YSTPQM where ygbh00=m.ygbh00) TZHSQM,
               (select count(1) from ZS_YSTPQM where ygbh00=m.ygbh00) TZHSCT,
                m.ZWXM00 TZHSXM
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           SS_YW0000 e,
           BQ_BRLDXX J,BM_BMBM00 K,ZY_BRXXB0 L,BM_YGBM00 f,BM_YGBM00 m
      where b.YGBH00(+) = a.KZYS00
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and e.yzmxid(+)=a.yzmxid
      AND  J.ZYID00=L.ZYID00
      AND J.KSH000=K.BMBH00
      AND A.ZYID00=J.ZYID00
      AND (J.QSZT00 IN ('转入','换入','入院','入科','迁入')
      AND TO_DATE(NVL(J.JSRQ00,'99991231')||NVL(J.JSSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(J.qsczrq,'99991231')||NVL(J.YSZSJ0,J.QSSJ00),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')<=0
      OR  J.QSZT00 = '新入'
      AND TO_DATE(NVL(J.JSRQ00,'99991231')||NVL(J.JSSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(L.RYRQ00,'99991231')||NVL(L.RYSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')<=0
      OR  J.QSZT00='择日' and  not exists(select 1 from bq_brldxx where zyid00=J.ZYID00 and QSZT00='新入')
      AND TO_DATE(NVL(J.JSRQ00,'99991231')||NVL(J.JSSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(L.RYRQ00,'99991231')||NVL(L.RYSJ00,J.qsczsj),'yyyyMMddHH24:mi:ss')-TO_DATE(A.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')<=0)
      and ( (a.YZZT00 in ('0','1','2','3','5','6','8')) OR (a.YZZT00='4' AND a.DYPC00<>0) )
      and f.YGBH00(+) = a.hdr000
      and m.YGBH00(+) = a.tzshr0
;