CREATE OR REPLACE VIEW VW_BQ_YZLL00
(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, yfid00, bbid00, czpc00, ksh000, yzhdsj, yzyzm0, ycxbz0, spzt00, spztmc)
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,3,6)||' '||A.QYSJ00,                      --起用日期+起用时间
               trim(C.ZWXM00)||'/'||trim(A.LRRXM0)||'/'||trim(A.HDRXM0),             --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               decode(A.YPLBBH,'2',A.CFMC00||' '||to_char(A.ZQCYTS)||'贴','') --草药贴数
                   ||decode(A.YPLBBH,'2',decode(A.JYCS00,0,'','代煎'||to_char(A.JYCS00)||'贴' )||SF_BQ_CALZYYYRQ(A.YYRQ00,A.ZQCYTS,A.CLBZ00),'') --草药煎药次数
                   ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次
                   ||decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','[首'||to_char(SRCS00)||']'))
                   ||trim(A.YPYFMC)||decode(a.yplbbh,'2',trim(A.PCMC00),'') --用法20200318
                   ||decode(A.CLBZ00,'0','',decode(A.ZQS000,1,'',to_char(A.ZQS000)||trim(A.ZQDW00))) YFNR00,--短期周期数
               --药品信息
               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(ZFFSBZ,'Y',' (自费)',null), --备注
               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))), --停止医生+停止人姓名
               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,--录入时间
               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),
               (select F.ZWXM00 from BM_YGBM00 F where F.ygbh00=A.TZSHR0 and rownum=1) TZSHR0,
               a.TSYS00,
               '' CJKS00,
               a.SRCS00,
               a.YFID00,
               '' BBID00,
               a.czpc00,
               a.KSH000,
               substr(A.HDRQ00,3,6)||' '||A.HDSJ00,
               a.YZYZM0,
               nvl(a.YCXBZ0,'0'),
               b.SPZT00,
               decode(nvl(b.SPZT00,'0'),'0','','1','已提交未审批','2','审批通过','3','审批不通过','4','双签名通过') SPZTMC
      from BQ_YPYZ00 a,
           BQ_YPYZMX b,
           BM_YGBM00 c,
           BM_YGBM00 d,
           zy_brxxb0 e
      where b.YZID00 = a.YZID00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.KZYS00
      and d.YGBH00(+) = a.TZYS00
      and a.zyid00=e.zyid00
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,3,6)||' '||a.QYSJ00,          --起用日期+起用时间
               trim(b.ZWXM00)||'/'||trim(a.LRRXM0)||'/'||trim(a.HDRXM0), --开嘱医生+录入人姓名+核对人姓名
               --用法信息
               '',
               --治疗项目信息
               --trim(a.ZLXMJC)||' '  --治疗项目名称
               trim(SF_ZS_GETFYYZMC(a.yzmxid))||' ' --治疗项目名称
               ||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))) --周期数
               ||'['||d.BMMC00||'/'
               ||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)))||']'
               ||(select decode(nvl(a.SQID00,0),0,'[未申请]','[已申请'||a.SQID00||']') from BM_ZLZD00 e,XT_XTCS00 f where ZLXMID=a.ZLXMID and nvl(e.ZYSQID,0)>0 and a.ZLXMID>0 and f.NAME00='ZS_YSXMSQD' and VALUE0='Y')  YZXX00,
               '',
--               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(C.ZWXM00,null,'',trim(C.ZWXM00)||decode(a.TZRXM0,null,'','/'||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,--录入时间
               --substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间
               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',
--               (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,
               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),
               (select F.ZWXM00 from BM_YGBM00 F where F.ygbh00=A.TZSHR0 and rownum=1) TZSHR0,
               -1 TSYS00,
               to_char(a.CJKS00) CJKS00,
               -1 as SRCS00,
               '0' YFID00,
               a.BBID00,
               a.czpc00,
               a.KSH000,
               substr(A.HDRQ00,3,6)||' '||A.HDSJ00,
               ' ' YZYZM0,
               nvl(a.YCXBZ0,'0') YCXBZ0,
               '0'  SPZT00,
               ' ' SPZTMC

      from BQ_YJYZ00 a,
           BM_YGBM00 b,
           BM_YGBM00 c,
           BM_BMBM00 d,
           zy_brxxb0 e
      where b.YGBH00(+) = a.KZYS00  and a.YZZT00<>'7'
      and c.YGBH00(+) = a.TZYS00
      and d.BMBH00(+) = a.ZXKS00
      and a.zyid00=e.zyid00
;