-- Start of DDL Script for View SD_HOSPITAL.VW_ZS_YZDY00_NEW_HQM
-- Generated 2021-01-13 09:30:27 from SD_HOSPITAL@fw2

CREATE OR REPLACE VIEW vw_zs_yzdy00_new_hqm (
   cfmc00,
   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,
   kzysqm,
   tzysqm,
   kzqmct,
   tzqmct,
   zxmont,
   zxdate,
   tzmont,
   tzdate,
   tzsj00,
   zxhsqm,
   zxqmct,
   tzhsqm,
   tzhsct,
   tzhsxm,
   zxhdsj,
   lzday0,
   lmonth )
AS
select
A.CFMC00,
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,8), --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)), --备注
                '',
               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')  ||')' ),*/ --中药贴数
             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.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',a.tzrq00||''||substr(a.TZSJ00,0,5)||'/'||a.TZRXM0||'/'||(select ZWXM00 from BM_YGBM00 s where a.tzshr0=s.ygbh00))) ,
               nvl(B.YZNR01,B.YZNR02),
               '', --补录标志( 药品由过程带出 )
               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,
               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,
               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)),

               (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00))  KZYSQM,
               (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=d.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00))  TZYSQM,

               (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) KZQMCT,
               (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=d.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) TZQMCT,
               --(select count(1) from ZS_YSTPQM where YGBH00=D.YGBH00) TZQMCT,
               substr(A.ZXRQ00,5,2) ZXMONT,   --月
               substr(A.ZXRQ00,7,2) ZXDATE,   --日

               CASE WHEN (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=d.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               AND (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=M.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               THEN NULL ELSE substr(A.TZRQ00,5,2) END TZMONT,
               CASE WHEN (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=d.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               AND (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=M.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               THEN NULL ELSE substr(A.TZRQ00,7,2) END TZDATE,
               CASE WHEN (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=d.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               AND (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=M.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) IS NULL
               THEN NULL ELSE substr(A.TZSJ00,1,5) END TZSJ00,
               --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 IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=e.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) ZXHSQM,
              -- (select count(1) from ZS_YSTPQM where YGBH00=E.YGBH00) ZXQMCT,
                (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=e.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) ZXQMCT,
              -- (select IMG000 from ZS_YSTPQM where YGBH00=M.YGBH00) TZHSQM,
                (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=M.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) TZHSQM,
              -- (select count(1) from ZS_YSTPQM where YGBH00=M.YGBH00) TZHSCT,
                (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=M.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)) ZXQMCT,
               M.ZWXM00 TZHSXM,
NVL2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=e.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)),trim(decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,9,6))),''),
NVL2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=e.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)),decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,7,2)),''),
NVL2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=e.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZID00)),decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,5,2)),'')
      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 a.zyid00=L.Zyid00
      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
'' CFMC00,
    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,8),--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),    --时刻
               (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,
               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,
               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)),

               (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=b.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID))  KZYSQM,
               (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID))  TZYSQM,
               --(select count(1) from ZS_YSTPQM where YGBH00=B.YGBH00) KZQMCT,
                (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=b.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) KZQMCT,
             --  (select count(1) from ZS_YSTPQM where YGBH00=C.YGBH00) TZQMCT,
                (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) TZQMCT,
               substr(A.TQRQ00,5,2) ZXMONT,   --月
               substr(A.TQRQ00,7,2) ZXDATE,   --日
               case when (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               and (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=m.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               then null else  substr(A.TZRQ00,5,2) end TZMONT,
               case when (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               and (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=m.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               then null else  substr(A.TZRQ00,7,2) end TZDATE,
               case when (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=c.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               and (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=m.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) is null
               then null else  substr(A.TZSJ00,1,5) end TZSJ00,
               --substr(A.TZRQ00,5,2) TZMONT,   --月
               --substr(A.TZRQ00,7,2) TZDATE,   --日
               --substr(A.TZSJ00,1,5) TZSJ00,    --时刻
               (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=F.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) ZXHSQM,
             --  (select count(1) from ZS_YSTPQM where YGBH00=F.YGBH00) ZXQMCT,
               (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=f.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) ZXQMCT,
              -- (select IMG000 from ZS_YSTPQM where YGBH00=M.YGBH00) TZHSQM,
                 (SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=m.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) TZHSQM,
               --(select count(1) from ZS_YSTPQM where YGBH00=M.YGBH00) TZHSCT,
                (SELECT count(1) FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=m.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)) TZHSCT,
                M.ZWXM00 TZHSXM,
               nvl2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=F.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)),trim(decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,9,6))),''),
nvl2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=F.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)),decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,7,2)),''),
nvl2((SELECT IMG000 FROM ZS_YSTPQM t ,BM_SZZSXX n where t.ygbh00=n.ygbh00 and n.zszt00='1' and n.SXRQ00<=a.lrrq00 and t.YGBH00=F.YGBH00 and exists(select 1 from zs_dzqmjl where lyid00=A.YZMXID)),decode(A.hdrq00||' '||A.hdsj00,null,'',substr(A.hdrq00||' '||A.hdsj00,5,2)),'')
      from BQ_YJYZ00 A,
           BM_YGBM00 B,
           BM_YGBM00 C,
           BM_BMBM00 D,
           SS_YW0000 E,
           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 A.ZYID00=L.ZYID00
      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
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZS_YZDY00_NEW_HQM