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,
   tt000,
   zxmd00,
   zxhdsj,
   lzday0,
   lmonth )
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)||' '||
               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, --嘱托 预留
               sf_bq_yztsbz(b.yzmxid,0)||
               decode(b.YZCLLB,'自备药','[备]','')||
               B.YPMC00||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 ,
               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  ,
                --    substr(A.QYRQ00,5,2),   --月
               --substr(A.QYRQ00,7,2),   --日
             --  substr(A.QYSJ00,1,5),    --时刻
               decode(a.YZZT00,'3',substr(A.TZRQ00,7,2)||'/ '||substr(A.TZRQ00,5,2),'')
               --  decode(a.YZZT00,'3',substr(A.TZRQ00,7,2)||'/ '||substr(A.TZRQ00,5,2),substr(nvl(A.YTZRQ0,A.TZRQ00),7,2)||'/ '||substr(nvl(A.YTZRQ0,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))
      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.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(J.QSRQ00,'99991231')||NVL(J.QSSJ00,'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) )
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(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,3,6)||' '||substr(A.TZSJ00,1,5),substr(nvl(A.YTZRQ0,A.TZRQ00),3,6)||' '||substr(nvl(A.YTZSJ0,A.TZSJ00),1,5))
                --  ,decode(a.YZZT00,'3',substr(A.TZRQ00,7,2)||'/ '||substr(A.TZRQ00,5,2),substr(nvl(A.YTZRQ0,A.TZRQ00),7,2)||'/ '||substr(nvl(A.YTZRQ0,A.TZRQ00),5,2))
                        , 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))
      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.Qyrq00||A.Qysj00,'yyyyMMddHH24:mi:ss')>=0
      AND TO_DATE(NVL(J.QSRQ00,'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.QSSJ00),'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) )
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZS_YZDY00_NEW