CREATE OR REPLACE VIEW VW_BQ_YZLL00_ZYYDHL
(zyid00, rycwh0, yexm00, lb0000, clbz00, dmbz00, yzlb00, yzzt00, yzid00, yzmxid, qysj00, qyyshs, yfnr00, yzxx00, lylxx0, tysj00, tyyshs, bz0000, qyrq00, tzrq00, pxsj00, zxrq00, tqsj00, lrrq00, lrsj00, yzly00, bqh000, cydjrq, bb0000, zxksmc, zxysmc, kzysmc, tzshr0, tsys00, cjks00, srcs00, pspc00, sfps00, zxsjd0, yzcxyy, yfid00, ypyfmc, kzys00, psid00, ypxmmc, ypbm00, mcsl00, pcmc00, zqs000, jldw00, sfwsf0, brxm00, brzt00, djsfqs, yfbmbh, yfbmmc, mzmc00, ssqsrq, ssqssj, yyrq00, zxzt00, jscx00, pspccs, yzlx00, jzbz00, ksh000, hdrqsj, yflbid, hzid00, wzdj00, ypnm00, jkztmc, zyh000, dqbq00, sjzxrq, spztmc, tzys00, lrrqsj, hdr000, fhr000, fhrqsj)
AS
SELECT     A.ZYID00,                              --住院ID
               E.RYCWH0,                              --床号
               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,5,2)||'-'||substr(A.QYRQ00,7,2)||' '||substr(A.QYSJ00,1,5),--起用日期+起用时间
               --trim(C.ZWXM00)||'/'||trim(A.LRRXM0)||'/'||trim(A.HDRXM0),             --开嘱医生+录入人姓名+核对人姓名
               trim(A.LRRXM0)||'/'||nvl(A.TJYSXM,(select trim(ZWXM00) from BM_YGBM00 where A.TJYS00=YGBH00))||'/'||trim(A.HDRXM0)||decode((select count(1) from XT_XTCS00 where NAME00='BQ_SFQYYZFH' and VALUE0='Y'),0,'','/'||A.FHRXM0), --录入医师/提交医师/核对护士
               --用法信息
               ' ' ||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.CLBZ00,'1',decode((select VALUE0 from XT_XTCS00 where name00='BQ_CXYZTJQXQZ'),'Y',decode(instr(SF_BQ_ZXZT00(a.ZYID00,a.YZID00,0),'(退)'),0,'','(取消)'),''),'')
                    ||decode(a.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)',''))
                    ||decode(a.blbz00,'Y','(补)','')
                    ||b.TZNXH0--同组内序号
         --||decode(F.GWYPFL,'0',null,'(高危)')
                    ||decode(f.WZDJ00,'1','(高危)',decode(F.GWYPFL,'0',decode(nvl(F.GWDJ00,'-'),'-','','0','','(高危)'),'(高危)'))
                    ||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,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') --处理
                   ||decode(nvl((select value0 from xt_xtcs00  where name00='BQ_YZNRSFYCYFZF'),'N'),'Y','',trim(a.BZ0000)) --备注
                   ||trim(b.BZ0000) --备注
                   --||decode(nvl((select value0 from xt_xtcs00  where name00='BQ_SFXSYFZF'),'N'),'Y',decode(trim(a.bz0000),null,trim(a.YYZF00),''),'') --是否显示嘱咐
                   ||decode(f.LBBH00,2,decode(f.YPCZFL,2,' 全成分',' 草药'),'')||decode(b.ZFFSBZ,'Y','(自费)'),--药品材质分类
               decode(A.CLBZ00,'0',decode(sign((select count(*) from XT_XTCS00 where name00='BQ_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),             --停用日期+停用时间
               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(D.ZWXM00,null,'',trim(D.ZWXM00)||decode(a.TZRXM0,null,'','/'||trim(A.TZRXM0))), --停止医生+停止人姓名
               decode(D.ZWXM00,null,'',trim(D.ZWXM00)||decode(g.ZWXM00,null,'','/'||trim(g.ZWXM00))), --停止医生+停止人姓名
               trim(A.YYZF00),                        --备注
               A.QYRQ00,                              --起用日期
               A.TZRQ00,                              --停止日期
               A.PXSJ00,                              --排序时间
               A.ZXRQ00,                              --执行日期
               --substr(A.TQRQ00,3,6)||' '||substr(A.TQSJ00,1,5), --提取日期
               A.TQRQ00||' '||A.TQSJ00, --提取日期
               A.LRRQ00,--录入时间
               substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.YZLY00, --医嘱来源 '0'护士'1'医生'2'手术
               a.BQH000, --病区
               e.CYDJRQ,
               '' BB0000,
               '',
               '',
               trim(C.ZWXM00),
               g.ZWXM00 TZSHR0,
               a.TSYS00,
               '' CJKS00,
               a.SRCS00,
               a.PSPC00,
               a.SFPS00,
               a.Zxsjd0,
               a.YZCXYY,
               a.YFID00,
               a.YPYFMC,
               a.KZYS00,
               b.PSID00,
               b.YPMC00,
               f.YPBM00,
               b.MCSL00,
               a.PCMC00,
               a.ZQS000,
               b.JLDW00,
               nvl((select decode(nvl(g.DJH000,0),0,'0','1') from  YF_YZYPSQ g where g.ZYID00=a.ZYID00 and g.YZID00 = a.YZID00 and a.CLBZ00 = '1'
               and g.CLBZ00='1' and rownum = 1),'0') SFWSF0,
               e.XM0000 BRXM00,
               e.BRZT00 BRZT00,
               a.DJSFQS DJSFQS,--代煎是否全送 0：否 1：是
               --decode(a.YFBMBH,null,'',to_char(a.YFBMBH)),
               --(select BMMC00 from BM_BMBM00 where BMBH00 = a.YFBMBH) YFBMMC,
         decode(NVL((select value0 from XT_XTCS00 where name00='BQ_SFQYTZYPBTYF' and value0='Y'),'N'),'Y',decode(b.yfbm00,null,to_char(a.YFBMBH),to_char(b.yfbm00)), decode(a.YFBMBH,null,'',to_char(a.YFBMBH))),
               (select BMMC00 from BM_BMBM00 where BMBH00 = decode(NVL((select value0 from XT_XTCS00 where name00='BQ_SFQYTZYPBTYF' and value0='Y'),'N'),'Y',nvl(b.yfbm00,a.yfbmbh),a.yfbmbh)) YFBMMC,       
               '', --麻醉名称
               '',--手术开始日期
               '',--手术开始时间
               a.YYRQ00,--用药日期
               SF_BQ_ZXZT00(a.ZYID00,a.YZID00,0,b.YZMXID) ZXZT00,--执行状态,
               decode(a.PCMC00,'s.t.','急','')
               ||decode(a.YZLX00,1,'术',2,'血','')
               ||decode(b.YZCLLB,'出院带药','出','') JSCX00,--急、术、出、血字样分别代表急诊、手术、出院、输血
               (select ceil(ZQCS00/HSZQCD*24) from BM_PC0000 where PCID00=a.PCID00 and rownum=1) PSPCCS,--配送批次次数
               a.YZLX00, --医嘱类型0:普通医嘱1:手术医嘱2:输血医嘱
               a.JZBZ00, --急诊标志Y急诊
               a.KSH000,
               a.HDRQSJ,
               NVL(h.YFLBID,-1),
               -1 --会诊id
               ,decode(f.WZDJ00,'1','高危',decode(F.GWYPFL,'0',decode(nvl(F.GWDJ00,'-'),'-','','0','','高危'),'高危')) WZDJ00--危重等级
           ,b.ypnm00 
           ,null JKZTMC
           ,e.ZYH000 --住院号
           ,e.DQBQ00
           ,decode((select max(VALUE0) from XT_XTCS00 where NAME00='BQ_YZZXRQLY')
                   ,'2',(select max(Z.QSRQ00) from BQ_YZZXMX Z where a.YZID00=Z.YZID00)) SJZXRQ
           ,decode(nvl(b.SPZT00,'0'),'0','','1','已提交未审批','2','审批通过','3','审批不通过,可双签','4','双签名通过','5','审核不通过') SPZTMC,
           A.TZYS00,
           substr(A.LRRQ00,5,2)||'-'||substr(A.LRRQ00,7,2)||' '||substr(A.LRSJ00,1,5) LRRQSJ,
           A.HDR000,
           A.FHR000,
           A.FHRQ00 ||' ' ||A.FHSJ00
      from BQ_YPYZ00 a,
           BQ_YPYZMX b,
           BM_YGBM00 c,
           BM_YGBM00 d,
           ZY_BRXXB0 e,
           BM_YD0000 f,
           BM_YGBM00 g,
           BM_YPYF00 h
      where b.YZID00 = a.YZID00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.KZYS00
      and d.YGBH00(+) = a.TZYS00
      and a.ZYID00=e.ZYID00
      and b.YPNM00=f.YPNM00
      and g.YGBH00(+) = a.TZSHR0
      and h.YFID00(+) = A.YFID00
     -- and g.YZID00(+) = a.YZID00
UNION ALL
    select     A.ZYID00,  --住院ID
               E.RYCWH0,  --床号
               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,5,2)||'-'||substr(A.QYRQ00,7,2)||' '||substr(A.QYSJ00,1,5),--起用日期+起用时间
               --trim(b.ZWXM00)||'/'||trim(a.LRRXM0)||'/'||trim(a.HDRXM0), --开嘱医生+录入人姓名+核对人姓名
               trim(A.LRRXM0)||'/'||nvl(A.TJYSXM,(select trim(ZWXM00) from BM_YGBM00 where A.TJYS00=YGBH00))||'/'||trim(A.HDRXM0)||decode((select count(1) from XT_XTCS00 where NAME00='BQ_SFQYYZFH' and VALUE0='Y'),0,'','/'||A.FHRXM0), --录入医师/提交医师/核对护士
               --用法信息
               '',
               --治疗项目信息
                decode(A.CLBZ00,1,decode((select VALUE0 from XT_XTCS00 where name00='BQ_CXYZTJQXQZ'),'Y',decode(instr(SF_BQ_ZXZT00(a.ZYID00,a.YZMXID,1),'(退)'),0,'','(取消)'),''),'')
               --decode(a.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)',''))
               ||trim(SF_ZS_GETFYYZMC_ZYDZBL(a.YZMXID))
               ||decode(instr(A.ZLXMJC,'(描述)'),0,decode((select VALUE0 from XT_XTCS00 where name00='BQ_MSXYZALBXS'),'Y',decode(A.YZLB00,9,'(描述)'),trim(nvl((select ' ' from  BQ_YJYZJJ f where  f.YZMXID = a.YZMXID and rownum =1),'(描述)'))),'')
               ||' '  --治疗项目名称
               ||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))) --周期数
               ||trim(A.BZ0000)||decode(a.ZFFSBZ,'Y','(自费)')
               ||a.BB0000
               ||(select MZMC00||' '||SSQSRQ||' '||SSQSSJ from SS_YW0000 where ZYGHID=a.ZYID00 and MZZYBZ='1' and YZMXID=a.YZMXID and SSZT00<>'9' and rownum=1)--麻醉名称
               ||decode(a.CLBZ00,'1','',decode(sign(to_date(nvl(a.SJQSRQ,a.QYRQ00),'yyyyMMdd')-to_date(a.QYRQ00,'yyyyMMdd')),1,'[首0]','')) --首0
               ||a.BWMC00
               ||decode((select SFWSXM from BM_ZLZD00 where ZLXMID=a.ZLXMID),'1','（外送）'),
               '',
               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(C.ZWXM00,null,'',trim(C.ZWXM00)||decode(a.TZRXM0,null,'','/'||trim(A.TZRXM0))), --停止医生+停止人姓名
               decode(C.ZWXM00,null,'',trim(C.ZWXM00)||decode(f.ZWXM00,null,'','/'||trim(f.ZWXM00))), --停止医生+停止人姓名
               trim(A.BZ0000),                             --备注
               A.QYRQ00,                   --起用日期
               A.TZRQ00,                   --停止日期
               A.PXSJ00,                   --排序时间
               A.TQRQ00,                   --执行日期
               A.TQRQ00||' '||A.TQSJ00, --提取日期
               A.LRRQ00,--录入时间
               substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间
               a.YZLY00, --医嘱来源 '0'护士'1'医生'2'手术
               a.BQH000, --病区
               e.CYDJRQ,
               a.BB0000,
               d.BMMC00 ZXKSMC,
               decode((select Trim(VALUE0) from XT_XTCS00 where NAME00='BQ_YZLLSFAZXQKXSZXYS'),'Y',
                       decode(a.CLBZ00,'0',(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),
                             (select ZXRXM0 from YJ_YW0000 where YJDJH0 in (select max(YJDJH0) from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID))),
               (select ZWXM00 from BM_YGBM00 where YGBH00=nvl(a.ZXYS00,a.KZYS00))) ZXYSMC,
               trim(B.ZWXM00),
               F.ZWXM00 TZSHR0,
               -1 TSYS00,
               to_char(a.CJKS00) CJKS00,
               -1 as SRCS00,
               '' PSPC00,
               '' SFPS00,
               '' ZXSJD0,
               a.YZCXYY,
               '' YFID00,
               '' YPYFMC,
               a.KZYS00,
               null,
               a.ZLXMJC,
               '' YPBM00,
               a.MCSL00,
               a.PCMC00,
               a.TS0000,
               a.DW0000,
                nvl((select decode(nvl(f.SFDJH0,0),0,'0',
                  nvl((select decode(nvl(h.SFDJH0,0),0,'1','0') from  YJ_YW0000 h where
                  h.ZYGHID=f.ZYGHID and h.MZZYBZ=f.MZZYBZ and h.CXDJH0=f.YJDJH0 and  rownum = 1),'1')
                ) from  YJ_YW0000 f where  f.YZCFID = a.YZMXID
               and a.ZYID00=f.ZYGHID and f.MZZYBZ='1' and f.FJDJBZ='0' and a.CLBZ00 = '1' and rownum = 1),'1') SFWSF0,
               e.XM0000 BRXM00,
               e.BRZT00 BRZT00,
               '' DJSFQS,--代煎是否全送 0：否 1：是
               to_char(a.ZXKS00) YFBMBH,
               d.BMMC00 YFBMMC,
              -- (select MZMC00 from SS_YW0000 where ZYGHID=a.ZYID00 and MZZYBZ='1' and YZMXID=a.YZMXID  and SSZT00<>'9' and rownum=1),--麻醉名称
              -- (select SSQSRQ from SS_YW0000 where ZYGHID=a.ZYID00 and MZZYBZ='1' and YZMXID=a.YZMXID  and SSZT00<>'9' and rownum=1),--手术开始日期
              -- (select SSQSSJ from SS_YW0000 where ZYGHID=a.ZYID00 and MZZYBZ='1' and YZMXID=a.YZMXID  and SSZT00<>'9' and rownum=1),--手术开始时间
               (select s.mzmc00 from ss_yw0000 s,ss_ywmx00 w where (s.yzmxid=a.yzmxid or w.yzmxid = a.yzmxid) and s.ssdh00 = w.ssdh00 and s.zyghid=a.zyid00 and rownum=1),--麻醉名称
               (select s.ssqsrq from ss_yw0000 s,ss_ywmx00 w where (s.yzmxid=a.yzmxid or w.yzmxid = a.yzmxid) and s.ssdh00 = w.ssdh00 and s.zyghid=a.zyid00 and rownum=1),--手术开始日期
               (select s.ssqssj from ss_yw0000 s,ss_ywmx00 w where (s.yzmxid=a.yzmxid or w.yzmxid = a.yzmxid) and s.ssdh00 = w.ssdh00 and s.zyghid=a.zyid00 and rownum=1),--手术开始时间
               
               a.SJQSRQ,
               SF_BQ_ZXZT00(a.ZYID00,a.YZMXID,1) ZXZT00,--执行状态
                decode(a.JZBZ00,'Y','急',decode(a.PCMC00,'s.t.','急',''))
               ||decode(a.YZLX00,1,'术',2,'血','') JSCX00,--急、术、出、血字样分别代表急诊、手术、出院、输血
               -1 PSPCCS,
               a.YZLX00, --医嘱类型0:普通医嘱1:手术医嘱2:输血医嘱
               a.JZBZ00, --急诊标志Y急诊
               a.KSH000,
               a.HDRQSJ,
               1,
               nvl(a.HZID00,-1) --会诊id
               ,'' WZDJ00  --危重等级
           ,a.zlxmid
           ,SF_BQ_GETJKZTMC(a.ZYID00,a.YZMXID) JKZTMC
           ,e.ZYH000 --住院号
           ,e.DQBQ00
           ,a.TQRQ00 SJZXRQ
           ,'' SPZTMC,
           A.TZYS00,
           substr(A.LRRQ00,5,2)||'-'||substr(A.LRRQ00,7,2)||' '||substr(A.LRSJ00,1,5) LRRQSJ,
           A.HDR000,
           A.FHR000,
           A.FHRQ00 ||' ' ||A.FHSJ00
      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           ZY_BRXXB0 e,
           BM_YGBM00 f
      where b.YGBH00(+) = a.KZYS00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and a.ZYID00=e.ZYID00
      and f.YGBH00(+) = a.TZSHR0