CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZS_YZDY00_SJBB
 (
 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, TZSJ00, CZPC00, 
KZYSQM, TZYSQM, CFMC00, ZXRQMM, ZXRQDD, ZXSJ00, ZXRXM0, TZRQMM, TZRQDD, 
TZSJHH, LRRQMM, LRRQDD, LRSJHH

 )
 AS 
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)||' '||A.QYSJ00,
           --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,'自备药','[备]','') --处理
           ||trim(B.BZ0000), --备注
                '',
           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), --停止人姓名
           decode(A.YPLBBH,'2',
              '×'||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.ClBZ00,'1', decode(A.YZZT00,'4','(取消)',''))||
           SF_BQ_YZTSBZ(B.YZMXID,0)||
           --decode(b.YZCLLB,'自备药','[备]','')||
           B.YPMC00||trim(B.PSMC00)||
           DECODE(A.CLBZ00,'0','',' '||B.LYL000||B.ZYFYDW||decode((select 1 from bm_tyzd00 where zdmc00='SIG频次' and BH0000=to_char(a.pcid00) and yxbz00='1'),1,'',' '||'sig'))
          -- decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','F['||to_char(SRCS00)||']'))||  --首日次数
          -- DECODE(A.CLBZ00,'0','',DECODE(B.YPLBBH,2,'',DECODE(I.YFLBID,0,' '||B.LYL000||B.ZYFYDW,4,' '||B.LYL000||B.ZYFYDW,  DECODE(A.CLBZ00,'0','',DECODE(B.YPLBBH,2,'',
            -- case  when I.YPYFMC in (select MC0000 from bm_tyzd00 where ZDMC00='打印领药量药品用法'  )   then  ' '||B.LYL000||B.ZYFYDW     else '' end
          -- )))))
          ||' '||''||
           DECODE(substr(to_char(B.MCSL00),1,1),'.','0','')||to_char(B.MCSL00)||B.JLDW00||
           decode(b.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') YZDY01, --处理
          --  decode(b.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','门诊处理','[门]',''), --处理
           --nvl(B.YZNR02,B.YZNR01)  YZDY02,
           nvl(B.YZNR01,B.YZNR02)  YZDY02,
           '', --补录标志( 药品由过程带出 )
           substr(A.QYRQ00,5,2),   --月
           substr(A.QYRQ00,7,2),   --日
           substr(A.QYSJ00,1,5),    --时刻
           (select BMMC00 from bm_bmbm00 K where k.bmbh00 in (select J.Ksh000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1)
               )BMMC00,
           nvl(A.YEXM00,L.XM0000),L.XB0000,
           (select CH0000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1
               )CH0000,
           L.ZYH000,SF_XT_CSRQTONL(L.CSRQ00,L.RYRQ00)NL0000
           ,substr(A.TZSJ00,1,5)TZSJ00,
           a.CZPC00,
           (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.kzys00)KZYSQM,
           (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.TZYS00)TZYSQM,
           a.CFMC00, --中药处方名称
           (select  substr(M.ZXRQ00,5,2)  from BQ_YZZXJL m where m.YZMXID=B.YZMXID  and SCJSRQ is null and rownum=1  ) ZXRQMM , --执行日期-月
           (select  substr(M.ZXRQ00,7,2)  from BQ_YZZXJL m where m.YZMXID=B.YZMXID  and SCJSRQ is null and rownum=1  ) ZXRQDD , --执行日期-日
           (select  substr(M.Zxsj00,1,5)  from BQ_YZZXJL m where m.YZMXID=B.YZMXID  and SCJSRQ is null and rownum=1  ) ZXSJ00 , --执行日期-时刻
            (select  decode(M.ZXRXM0,'超级用户',A.Hdrxm0,m.ZXRXM0)  from BQ_YZZXJL m where m.YZMXID=B.YZMXID and SCJSRQ is null and rownum=1  ) ZXRXM0 , --执行护士
           substr(A.TZRQ00,5,2)  TZRQMM , --停止日期-月
           substr(A.TZRQ00,7,2)  TZRQDD , --停止日期-日
           substr(A.TZSJ00,1,5)   TZSJHH,   --停止日期-时刻
           substr(A.LRRQ00,5,2) LRRQMM,   --录入时间-月
           substr(A.LRRQ00,7,2) LRRQDD,   --录入时间-日
           substr(A.LRSJ00,1,5) LRSJHH   --录入时间-时刻
      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
      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 a.zyid00=L.Zyid00
      --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.LRRQ00||A.LRSJ00,'yyyyMMddHH24:mi:ss')>=0
      --and TO_DATE(nvl(J.QSRQ00,'99991231')||nvl(J.YSZSJ0,J.QSSJ00),'yyyyMMddHH24:mi:ss')-TO_DATE(A.LRRQ00||A.LRSJ00,'yyyyMMddHH24:mi:ss')<0
      and ( (A.YZZT00 in ('0','1','2','3','5','6','8')) or (A.YZZT00='4' and A.DYPC00<>0) )
      and  A.YZID00 not in ('21431202','21431209','21431212')
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)||' '||A.QYSJ00,
               --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)||' ' --频次
              ||case when A.DW0000= '小时' or a.MCSL00!='1'   then decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000) end ||' ' --每次数量
               ||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)||' ' --频次
               ||case when a.DW0000= '小时' or a.MCSL00!='1'   then decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000) end ||' ' --每次数量
               ||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(replace(decode(a.clbz00,0,replace(SF_ZS_GETFYYZMC(a.yzmxid),'(取消)',''),SF_ZS_GETFYYZMC(a.yzmxid)),'(描述)',''))||' '  --治疗项目名称

               ||decode(A.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','')   --处理
               ||trim(A.PCMC00)||' ' --频次
              ||case when a.DW0000= '小时' or a.MCSL00!='1'   then decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||trim(a.DW0000) end ||' ' --每次数量
               ||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,'', (case  when instr(a.ZLXMJC, '出院') > 0 then ''  else  '(' || a.bz0000 || ')' end )),'')
               ||decode(a.clbz00,'1','',decode(sign(to_date(nvl(a.sjqsrq,a.qyrq00),'yyyyMMdd')-to_date(a.qyrq00,'yyyyMMdd')),1,'[F0]',''))
               ||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),    --时刻
               (select BMMC00 from bm_bmbm00 K where k.bmbh00 in (select J.Ksh000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1)
               )BMMC00,
               nvl(A.YEXM00,L.XM0000),L.XB0000,
               (select CH0000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1
               )CH0000,
               L.ZYH000,SF_XT_CSRQTONL(L.CSRQ00,L.RYRQ00)NL0000,
               substr(a.tzsj00,1,5)tzsj00,
               a.CZPC00,
               (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.kzys00)KZYSQM,
               (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.TZYS00)TZYSQM,
                ''  Cfmc00 , --中药处方名称
               (select  substr(m.ZXRQ00,5,2)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1 ) ZXRQMM , --执行日期-月
               (select  substr(m.ZXRQ00,7,2)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXRQDD , --执行日期-日
               (select  substr(m.Zxsj00,1,5)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXSJ00 , --执行日期-时刻
               (select  decode(m.ZXRXM0,'超级用户',A.Hdrxm0,m.ZXRXM0) from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXRXM0 , --执行护士
               substr(A.TZRQ00,5,2)  TZRQMM , --停止日期-月
               substr(A.TZRQ00,7,2)  TZRQDD , --停止日期-日
               substr(A.TZSJ00,1,5)  TZSJHH,   --停止日期-时刻
               substr(A.LRRQ00,5,2) LRRQMM,   --录入时间-月
               substr(A.LRRQ00,7,2) LRRQDD,   --录入时间-日
               substr(A.LRSJ00,1,5) LRSJHH   --录入时间-时刻
      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
      where B.YGBH00(+) = A.KZYS00
      and C.YGBH00(+) = A.TZYS00
      and D.BMBH00(+) = A.ZXKS00
      and E.YZMXID(+)=A.YZMXID
      and A.ZYID00=L.ZYID00
      --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.LRRQ00||A.LRSJ00,'yyyyMMddHH24:mi:ss')>=0
      --and TO_DATE(nvl(J.QSRQ00,'99991231')||nvl(J.YSZSJ0,J.QSSJ00),'yyyyMMddHH24:mi:ss')-TO_DATE(A.LRRQ00||A.LRSJ00,'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.LRRQ00||A.LRSJ00,'yyyyMMddHH24:mi:ss')>=0
      --and TO_DATE(nvl(L.RYRQ00,'99991231')||nvl(L.RYSJ00,'23:59:59'),'yyyyMMddHH24:mi:ss')-TO_DATE(A.LRRQ00||A.LRSJ00,'yyyyMMddHH24:mi:ss')<=0)
      and ( (A.YZZT00 in ('0','1','2','3','5','6','8')) or (A.YZZT00='4' and A.DYPC00<>0) )
      and (not exists(select 1 from  BM_TYZD00 where  ZDMC00='医嘱打印过滤项目' and  a.ZLXMID=BH0000)  and YZLB00<>'2'
     and a.LRRXM0 not in   (select ZWXM00 from BM_YGBM00 where BMBH00 in (select BH0000 from BM_TYZD00 where ZDMC00='过滤护理医嘱科室')) )
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)||' '||A.QYSJ00,
               --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,'')), --开嘱医生
               --用法信息
               '',
               --治疗项目信息
              f.YPMC00|| '['||f.YPGG00 ||/*'/'||f.MZFYDW||*/ ']' || f.ZSL000||f.MZFYDW||decode(a.blbz00,'Y','(补)',''), --首0,
               '',
               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,
               --治疗项目信息
               f.YPMC00|| '['||f.YPGG00 ||/*'/'||f.MZFYDW||*/ ']' || f.ZSL000||f.MZFYDW||decode(a.blbz00,'Y','(补)',''),
               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), --停止人姓名
               '', --中药贴数
               '', --药品材质分类
               '', --嘱托 预留
                --诊疗项目信息
              --治疗项目信息
               f.YPMC00|| '['||f.YPGG00 ||/*'/'||f.MZFYDW||*/ ']' || f.ZSL000||f.MZFYDW||decode(a.blbz00,'Y','(补)',''), --首0
               '',
               decode(A.BLBZ00,'Y','(补)',''),--补录标志
               substr(A.QYRQ00,5,2),   --月
               substr(A.QYRQ00,7,2),   --日
               substr(A.QYSJ00,1,5),    --时刻
               (select BMMC00 from bm_bmbm00 K where k.bmbh00 in (select J.Ksh000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1)
               )BMMC00,
               nvl(A.YEXM00,L.XM0000),L.XB0000,
               (select CH0000 from BQ_BRLDXX J where J.Zyid00=L.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
                 )
                 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 rownum=1
               )CH0000,
               L.ZYH000,SF_XT_CSRQTONL(L.CSRQ00,L.RYRQ00)NL0000,
               substr(a.tzsj00,1,5)tzsj00,
               a.CZPC00,
               (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.kzys00)KZYSQM,
               (select m.img000 from ZS_YSTPQM m where m.ygbh00=a.TZYS00)TZYSQM,
                ''  Cfmc00 , --中药处方名称
               (select  substr(m.ZXRQ00,5,2)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1 ) ZXRQMM , --执行日期-月
               (select  substr(m.ZXRQ00,7,2)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXRQDD , --执行日期-日
               (select  substr(m.Zxsj00,1,5)  from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXSJ00 , --执行日期-时刻
               (select  decode(m.ZXRXM0,'超级用户',A.Hdrxm0,m.ZXRXM0) from BQ_YZZXJL m where m.YZMXID=A.YZMXID and SCJSRQ is null and rownum=1  ) ZXRXM0 , --执行护士
               substr(A.TZRQ00,5,2)  TZRQMM , --停止日期-月
               substr(A.TZRQ00,7,2)  TZRQDD , --停止日期-日
               substr(A.TZSJ00,1,5)  TZSJHH,   --停止日期-时刻
               substr(A.LRRQ00,5,2) LRRQMM,   --录入时间-月
               substr(A.LRRQ00,7,2) LRRQDD,   --录入时间-日
               substr(A.LRSJ00,1,5) LRSJHH   --录入时间-时刻
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           BM_YGBM00 e,
           VW_YS_ZLDCYP f,
           ZY_BRXXB0 L
      where b.YGBH00(+) = a.KZYS00
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and e.YGBH00(+) = a.TZSHR0 and a.ZLXMID=f.ZLXMID
      and a.zyid00=l.zyid00
      and ( (A.YZZT00 in ('0','1','2','3','5','6','8')) or (A.YZZT00='4' and A.DYPC00<>0) )
      and (not exists(select 1 from  BM_TYZD00 where  ZDMC00='医嘱打印过滤项目' and  a.ZLXMID=BH0000)  and YZLB00<>'2'
     and a.LRRXM0 not in  (select ZWXM00 from BM_YGBM00 where BMBH00 in (select BH0000 from BM_TYZD00 where ZDMC00='过滤护理医嘱科室')) )
/