CREATE OR REPLACE VIEW VW_ZS_YZLL00_ZYDZBL
(zyid00, yexm00, lb0000, clbz00, dmbz00, yzlb00, yzzt00, yzid00, yzmxid, qysj00, qyyshs, yfnr00, yzxx00, lylxx0, tysj00, tyyshs, bz0000, qyrq00, tzrq00, pxsj00, zxrq00, tqsj00, lrrq00, lrsj00, bqh000, zdid00, zdmc00, bqzy00, bwmc00, tsys00, bb0000, zxysxm, cjks00, srcs00, tzrxm0, ksh000, yzly00, kzys00, lrrxm0, ypnm00, yyrq00, pspc00, ypczfl, yfbmbh, zxksmc, bbid00, zqs000, kssjb0, ypmc00, mzmc00, ssqsrq, ssqssj, ssdh00, czpc00, ypyfmc, ljyzbz)
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,6)||' '||substr(A.QYSJ00,1,5),                      --起用日期+起用时间
               substr(A.QYRQ00,3,6)||' '||Rpad(trim(A.QYSJ00),8,':00'),         ----起用日期+起用时间
               trim(C.ZWXM00)||decode(trim(A.HDRXM0),'','','/'||trim(A.HDRXM0)),             --开嘱医生+核对人姓名
               --用法信息
               decode(A.YPLBBH,'2',to_char(A.ZQCYTS)||'贴','') --草药贴数
                   ||decode(A.YPLBBH,'2',decode(A.JYCS00,0,decode(A.DJFSMC,null,'自煎',A.DJFSMC),'代煎'||to_char(A.JYCS00)||'贴'),'') --草药煎药次数
                   ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次
                   ||decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','[首'||to_char(SRCS00)||']'))
                   ||trim(A.YPYFMC)--用法
                   ||(select ' ('||jyfsmc||')' from Ys_jyfs00 where bh0000=a.jyfs00)--煎药方式
                   ||decode(A.CLBZ00,'0','',decode(A.ZQS000,1,'',to_char(A.ZQS000)||trim(A.ZQDW00))),--短期周期数
               --药品信息
                   decode(a.pcmc00,'s.t.','(急)','')
                   ||b.tznxh0--同组内序号
                   ||decode(a.yzlx00,1,'(术)',2,'(血)','')
                  -- || decode(f.zbqmc0,'4+7','(4+7)',null,'')||decode(instr(B.YPMC00,'取消'),0,decode(f.YPXZ00,1,'(基)',2,'(省基)',3,'',4,''),'')||decode(f.gwypfl,'0',null,'(高危)')||trim(B.YPMC00)--药品名称
                   ||decode(f.zbqmc0,'4+7','(4+7)',null,'')||decode(instr(B.YPMC00,'取消'),0,'')||decode((select nvl(max(trim(VALUE0)),'0') from XT_XTCS00 where NAME00='ZS_SRCSMRFS'),'2',decode(f.YPXZ00,1,'(基)',2,'(省基)',3,'',4,''),'')||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)                      --皮试名称
                   ||trim(B.XMYFMC) --草药细目名称
                   ||decode(b.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') --处理
                   ||trim(B.BZ0000) --备注
                   ||decode(f.lbbh00,2,decode(f.ypczfl,1,'颗粒',2,' 全成分',' 草药'),'')||decode(a.blbz00,'Y','(补)',''),--药品材质分类
               decode(A.CLBZ00,'0',decode(sign((select count(*) from XT_XTCS00 where name00='ZS_CQYZZJSSFTXYPSL' and value0<>'0')),1,
                   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.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),             --停用日期+停用时间  by ZYYS-20111219-001
               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)),             --停用日期+停用时间
               --trim(D.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名
               trim(D.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.ZWXM00)),--停止医生+停止人姓名
               trim(A.YYZF00)||decode(b.zffsbz,'Y','自费'),                        --备注
               A.QYRQ00,                              --起用日期
               A.TZRQ00,                              --停止日期
               A.PXSJ00,                              --排序时间
               A.ZXRQ00,                              --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5), --提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00,
               substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.BQH000, --病区
               A.ZDID00 ZDID00,
               A.ZDMC00 ZDMC00,
               '' BQZY00,
               '' BWMC00,
               a.TSYS00,
               '' BB0000,
               '' ZXYSXM,
               '' CJKS00,
               a.SRCS00,
               a.TZRXM0,
               a.KSH000,
               a.YZLY00,
               a.KZYS00,
               a.LRRXM0,
               b.YPNM00,
               a.yyrq00,--用药日期
               a.pspc00,--配送批次
               f.ypczfl,--药品材质分类
               a.yfbmbh,--药房部门编号
               (select bmmc00 from bm_bmbm00 where bmbh00 = a.yfbmbh) zxksmc,
               '' bbid00,
               nvl(a.ZQS000,1),
               nvl(f.kssjb0,0),
               f.ypmc00,
               '', --麻醉名称
               '',--手术开始日期
               '',--手术开始时间
               null, --手术单号
               a.CZPC00,    --重整批次
               a.YPYFMC,
               --nvl((select '径内' from LJ_YZMX00 where WBMXID=b.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外')  LJYZBZ,
               decode(nvl(B.JHMXID,0),0,'径外','径内')  LJYZBZ--径内径外判断
      from BQ_YPYZ00 a,
           BQ_YPYZMX b,
           BM_YGBM00 c,
           BM_YGBM00 d,
           BM_YD0000 f,
           BM_YGBM00 e
      where b.YZID00 = a.YZID00
      and c.YGBH00(+) = a.KZYS00
      and d.YGBH00(+) = a.TZYS00
      and b.ypnm00=f.ypnm00
      and e.YGBH00(+) = a.TZSHR0
      and A.LRRXM0 not in  (select ZWXM00 from BM_YGBM00 where BMBH00 in ('481','482','483','484','485','486','507','663','470','725','700','764', '766', '768'))
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,6)||' '||substr(a.QYSJ00,1,5),          --起用日期+起用时间
               substr(A.QYRQ00,3,6)||' '||Rpad(trim(A.QYSJ00),8,':00'),         ----起用日期+起用时间
               trim(b.ZWXM00)||decode(a.HDRXM0,'','','/'||trim(a.HDRXM0)), --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               '',
               --治疗项目信息
               --decode(a.jzbz00,'Y','(急)',decode(a.pcmc00,'s.t.','(急)',''))||
               trim(SF_ZS_GETFYYZMC_ZYDZBL(a.yzmxid))
               ||decode((select value0 from xt_xtcs00 where name00='YS_SFXMKQYY'),'Y',decode(a.zlxmid,0,'(描述)'),trim(nvl((select ' ' from  bq_yjyzjj f where  f.yzmxid = a.yzmxid and rownum =1),'(描述)')))
               ||' '  --治疗项目名称
               ||decode((select nvl(VALUE0,'N') from XT_XTCS00 where NAME00 = 'ZS_YZXXSFXSBBLX'),'Y', A.BB0000,null)||' '--标本类型
               ||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(a.clbz00,'1','',decode(sign(to_date(nvl(a.sjqsrq,a.qyrq00),'yyyyMMdd')-to_date(a.qyrq00,'yyyyMMdd')),1,'[首0]',''))||decode((select SFWSXM from BM_ZLZD00 where zlxmid=a.zlxmid),'1','（外送）') --首0
               ||'['||d.BMMC00||']'||decode(a.blbz00,'Y','(补)',''),
               '',
--               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间  by ZYYS-20111219-001
               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)),             --停用日期+停用时间
               --trim(C.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名
               trim(C.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.ZWXM00)),--停止医生+停止人姓名
               trim(A.BZ0000)||decode(a.zffsbz,'Y','自费'),                            --备注
               A.QYRQ00,                   --起用日期
               A.TZRQ00,                   --停止日期
               A.PXSJ00,                   --排序时间
               A.TQRQ00,                   --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5),--提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00,
               substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.BQH000, --病区
               a.ZDID00,
               a.ZDMC00,
               a.BQZY00,
               a.BWMC00,
               -1 TSYS00,
               a.bb0000,
--               e.zwxm00,
               decode(a.CLBZ00,'0',(d.BMMC00||'/'||(select ZXRXM0 from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and ZXRQ00=to_char(sysdate,'YYYYMMDD') and
                               SL0000>CXSL00 and ZXRXM0 is not null and rownum=1)),
                      (d.BMMC00||'/'||(select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))) ),
               to_char(a.CJKS00) CJKS00,
               -1 as SRCS00,
               a.TZRXM0,
               a.KSH000,
               a.YZLY00,
               a.KZYS00,
               a.LRRXM0,
               -1,
               a.SJQSRQ,--用药日期
               '',
               '',
               0,
               d.bmmc00 zxksmc,
               a.bbid00,
               nvl(a.ts0000,1),
               0 kssjb0,
               a.zlxmjc,
               (select mzmc00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--麻醉名称
               (select ssqsrq from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始日期
               (select ssqssj from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始时间
               (select ssdh00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术单号
               a.CZPC00,
               '',
               --nvl((select '径内' from LJ_YZMX00 where WBMXID=a.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外')  LJYZBZ,  --径内径外判断
               decode(nvl(A.JHMXID,0),0,'径外','径内')  LJYZBZ--径内径外判断
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           BM_YGBM00 e
      where b.YGBH00(+) = a.KZYS00
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and e.YGBH00(+) = a.TZSHR0
      and A.LRRXM0 not in  (select ZWXM00 from BM_YGBM00 where BMBH00 in ('481','482','483','484','485','486','507','663','470','725','700','764', '766', '768'))
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,6)||' '||substr(a.QYSJ00,1,5),          --起用日期+起用时间
               substr(A.QYRQ00,3,6)||' '||Rpad(trim(A.QYSJ00),8,':00'),         ----起用日期+起用时间
               trim(b.ZWXM00)||decode(a.HDRXM0,'','','/'||trim(a.HDRXM0)), --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               '',
               --治疗项目信息
               f.YPMC00|| '['||f.YPGG00 ||/*'/'||f.MZFYDW||*/ ']' || f.ZSL000||f.MZFYDW||decode(a.blbz00,'Y','(补)',''),
               '',
--               substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5),             --停用日期+停用时间  by ZYYS-20111219-001
               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)),             --停用日期+停用时间
               --trim(C.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名
               trim(C.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.ZWXM00)),--停止医生+停止人姓名
               trim(A.BZ0000)||decode(a.zffsbz,'Y','自费'),                          --备注
               A.QYRQ00,                   --起用日期
               A.TZRQ00,                   --停止日期
               to_char(to_date(A.PXSJ00,'HH24:mi:ss')+1/(24*60*60),'HH24:mi:ss'),                   --排序时间
               A.TQRQ00,                   --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5),--提取日期
               A.TQRQ00, --提取日期
               A.LRRQ00,
               --substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.BQH000, --病区
               a.ZDID00,
               a.ZDMC00,
               a.BQZY00,
               a.BWMC00,
               -1 TSYS00,
               a.bb0000,
--               e.zwxm00,
               decode(a.CLBZ00,'0',(d.BMMC00||'/'||(select ZXRXM0 from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and ZXRQ00=to_char(sysdate,'YYYYMMDD') and
                               SL0000>CXSL00 and ZXRXM0 is not null and rownum=1)),
                      (d.BMMC00||'/'||(select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))) ),
               to_char(a.CJKS00) CJKS00,
               -1 as SRCS00,
               a.TZRXM0,
               a.KSH000,
               a.YZLY00,
               a.KZYS00,
               a.LRRXM0,
               -1,
               a.SJQSRQ,--用药日期
               '',
               '',
               0,
               d.bmmc00 zxksmc,
               a.bbid00,
               nvl(a.ts0000,1),
               0 kssjb0,
               a.zlxmjc,
               (select mzmc00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--麻醉名称
               (select ssqsrq from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始日期
               (select ssqssj from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始时间
               (select ssdh00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术单号
               a.CZPC00,
               '',
               --nvl((select '径内' from LJ_YZMX00 where WBMXID=a.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外')  LJYZBZ,  --径内径外判断
               decode(nvl(A.JHMXID,0),0,'径外','径内')  LJYZBZ--径内径外判断
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           BM_YGBM00 e,
           VW_YS_ZLDCYP f
      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.LRRXM0 not in  (select ZWXM00 from BM_YGBM00 where BMBH00 in (select BH0000 from BM_TYZD00 where ZDMC00='过滤护理医嘱科室'));