CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZS_YZDY_LNYY ( 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, TQSJFS, TZDYPC, YZNR01 ) 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.LRRQ00,3,2)||'.'||substr(A.LRRQ00,5,2)||'.'||substr(A.LRRQ00,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) --皮试名称 ||trim(B.XMYFMC) --草药细目名称 ||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)))),--短期领药量 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, substr(A.LRRQ00,3,2)||'.'||substr(A.LRRQ00,5,2)||'.'||substr(A.LRRQ00,7,2)||' '|| 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'),--起用日期+起用时间 --substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间 --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间 DYPC00,-- a.BQH000, --病区 e.zwxm00, --rpad(b.YZNR00,b.YZNRCD,' ')||b.YZNR01, rpad(b.YZNR00||decode(b.YZCLLB,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]',''),b.YZNRCD+4,' '),--去掉||b.YZNR01 a.TQSJ00 TQSJFS, --提取时间 nvl(a.TZDYPC,0), --停嘱打印频次 b.YZNR01 from BQ_YPYZ00 a, BQ_YPYZMX b, BM_YGBM00 c, BM_YGBM00 d, BM_YGBM00 e where b.YZID00 = a.YZID00 and c.YGBH00(+) = a.KZYS00 and e.YGBH00(+) = a.HDR000 and d.YGBH00(+) = a.TZYS00 and a.XYDYBZ='1' and a.YZZT00 in ('0','1','2','3','4','5','6','8') and b.yzcllb<>'领药' and ((DYPC00=0 and a.YZZT00<>'4') or (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.LRRXM0)||'/'||TRIM(NVL(a.HDRXM0,'')), --开嘱医生 --用法信息 '', --治疗项目信息 --e.XMMC00||' '-- trim(a.ZLXMJC)||' ' --治疗项目名称 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, substr(A.LRRQ00,3,2)||'.'||substr(A.LRRQ00,5,2)||'.'||substr(A.LRRQ00,7,2)||' '|| 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'),--起用日期+起用时间 --substr(A.LRRQ00,3,6)||' '||A.LRSJ00, --录入时间 --substr(A.LRRQ00,3,6)||' '||A.PXSJ00, --录入时间 DYPC00,-- a.BQH000, --病区 a.HDRXM0, --治疗项目信息 --decode(e.XMMC00,null,trim(a.ZLXMJC),e.XMMC00||' '--治疗/收费项目名称(存在0费用的描述医嘱) trim(a.ZLXMJC)||' '||decode(a.YZLB00,4,decode(nvl(a.BB0000,''),'','','('||trim(a.BB0000)||')'),'')||' ' --治疗项目名称 ||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.YEXM00,null,'','('||a.YEXM00||')')--婴儿 ||decode((select trim(value0) from xt_xtcs00 where trim(name00)='BQ_YZNRSFDYBZ'),'Y',decode(a.bz0000,null,'','备注：'||a.bz0000),'' ),--备注 a.TQSJ00 TQSJFS, --提取时间 nvl(a.TZDYPC,0), --停嘱打印频次 ' ' YZNR01 from BQ_YJYZ00 a, BM_YGBM00 b, BM_YGBM00 c, BM_BMBM00 d --,VW_ZS_YJYZJJ e where b.YGBH00(+) = a.KZYS00 --and a.YZMXID=e.YZMXID(+) and c.YGBH00(+) = a.TZYS00 and d.BMBH00(+) = a.ZXKS00 and a.XYDYBZ='1' and a.YZZT00 in ('0','1','2','3','4','5','6','8') and ((DYPC00=0 and a.YZZT00<>'4') or (DYPC00>0)) and not exists(select 1 from BM_TYZD00 where ZDMC00='医嘱打印过滤项目' and a.ZLXMID=BH0000) and YZLB00<>'2' /