-- Start of DDL Script for View SD_HOSPITAL.VW_ZS_YZDY00_NEW
-- Generated 8-八月-2017 9:55:33 from SD_HOSPITAL@FW2

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,
   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), --备注
/*         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), --停止人姓名
           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.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),    --时刻
           K.BMMC00,nvl(A.YEXM00,L.XM0000),L.XB0000,J.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  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) )
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.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)||' ' --频次
              ||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),    --时刻
               K.BMMC00,nvl(A.YEXM00,L.XM0000),L.XB0000,J.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  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) )
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZS_YZDY00_NEW