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) 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), --开嘱医生+录入人姓名+核对人姓名 --用法信息 ' ' ||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.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)','')) ||decode(a.blbz00,'Y','(补)','') ||b.TZNXH0--同组内序号 ||decode(F.GWYPFL,'0',null,'(高危)') ||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(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.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, '', --麻醉名称 '',--手术开始日期 '',--手术开始时间 a.YYRQ00,--用药日期 SF_BQ_ZXZT00(a.ZYID00,a.YZID00,0) 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 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), --开嘱医生+录入人姓名+核对人姓名 --用法信息 '', --治疗项目信息 --decode(a.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)','')) trim(SF_ZS_GETFYYZMC(a.YZMXID)) ||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) ||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]',''))||decode((select SFWSXM from BM_ZLZD00 where zlxmid=a.zlxmid),'1','（外送）') , --首0 '', 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.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),--手术开始时间 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 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;