-- Start of DDL Script for View SD_HOSPITAL.VW_BQ_YZLL00_ZYYDHL -- Generated 2021-01-25 10:57:55 from SD_HOSPITAL@30 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, tzrxm0, hshtrq, bwmc00 ) 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(to_char(A.QYRQ00||' '||A.QYSJ00),3,16),--起用日期+起用时间 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)||'贴')||SF_BQ_CALZYYYRQ(A.YYRQ00,A.ZQCYTS,A.CLBZ00),'') --草药煎药次数 ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次 ||decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','[F'||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), a.TZRXM0, a.hshtrq, '' 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(to_char(A.QYRQ00||' '||A.QYSJ00),3,16),--起用日期+起用时间 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) ||decode((select LBBH00 from BM_ZLZD00 where ZLXMID=a.zlxmid and rownum=1),'4',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 ||(select '['||bmmc00||']' from bm_bmbm00 where bmbh00=a.ZXKS00), '', 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, a.TZRXM0, a.hshtrq, a.bwmc00 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 / -- End of DDL Script for View SD_HOSPITAL.VW_BQ_YZLL00_ZYYDHL