CREATE OR REPLACE VIEW vw_zs_yzll00_zydzbl ( zyid00, yexm00, lb0000, clbz00, dmbz00, yzlb00, yzzt00, yzid00, yzmxid, qysj00, qyyshs, yfnr00, yzxx00, lylxx0, tysj00, tyyshs, bz0000, qyrq00, tzrq00, pxsj00, zxrq00, tqsj00, lrrq00, lrsj00, bqh000, zdid00, zdmc00, bqzy00, bwmc00, tsys00, bb0000, zxysxm, cjks00, srcs00, tzrxm0, ksh000, yzly00, kzys00, lrrxm0, ypnm00, yyrq00, pspc00, ypczfl, yfbmbh, zxksmc, bbid00, zqs000, kssjb0, ypmc00, mzmc00, ssqsrq, ssqssj, ssdh00, czpc00, ypyfmc, ljyzbz ) 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.QYRQ00,3,6)||' '||Rpad(trim(A.QYSJ00),8,':00'), --substr(A.QYRQ00,3,6)||' '||substr(A.QYSJ00,1,5), --起用日期+起用时间 trim(C.ZWXM00)||decode(trim(A.HDRXM0),'','','/'||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.pcmc00,'s.t.','(急)','') ||b.tznxh0--同组内序号 ||decode(a.yzlx00,1,'(术)',2,'(血)','') || decode(f.zbqmc0,'4+7','(4+7)',null,'')||decode(f.YPXZ00,1,'(基)',2,'(省基)',3,'',4,'')||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,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') --处理 ||trim(B.BZ0000) --备注 ||decode(f.lbbh00,2,decode(f.ypczfl,2,' 全成分',' 草药'),''),--药品材质分类 decode(A.CLBZ00,'0',decode(sign((select count(*) from XT_XTCS00 where name00='ZS_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), --停用日期+停用时间 by ZYYS-20111219-001 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)), --停用日期+停用时间 --trim(D.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名 trim(D.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.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.BQH000, --病区 A.ZDID00 ZDID00, A.ZDMC00 ZDMC00, '' BQZY00, '' BWMC00, a.TSYS00, '' BB0000, '' ZXYSXM, '' CJKS00, a.SRCS00, a.TZRXM0, a.KSH000, a.YZLY00, a.KZYS00, a.LRRXM0, b.YPNM00, a.yyrq00,--用药日期 a.pspc00,--配送批次 f.ypczfl,--药品材质分类 a.yfbmbh,--药房部门编号 (select bmmc00 from bm_bmbm00 where bmbh00 = a.yfbmbh) zxksmc, '' bbid00, nvl(a.ZQS000,1), nvl(f.kssjb0,0), f.ypmc00, '', --麻醉名称 '',--手术开始日期 '',--手术开始时间 null, --手术单号 a.CZPC00, --重整批次 a.YPYFMC, --nvl((select '径内' from LJ_YZMX00 where WBMXID=b.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外') LJYZBZ, decode(nvl(B.JHMXID,0),0,'径外','径内') LJYZBZ--径内径外判断 from BQ_YPYZ00 a, BQ_YPYZMX b, BM_YGBM00 c, BM_YGBM00 d, BM_YD0000 f, BM_YGBM00 e where b.YZID00 = a.YZID00 and c.YGBH00(+) = a.KZYS00 and d.YGBH00(+) = a.TZYS00 and b.ypnm00=f.ypnm00 and e.YGBH00(+) = a.TZSHR0 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,6)||' '||Rpad(trim(A.QYSJ00),8,':00'), --substr(a.QYRQ00,3,6)||' '||substr(a.QYSJ00,1,5), --起用日期+起用时间 trim(b.ZWXM00)||decode(a.HDRXM0,'','','/'||trim(a.HDRXM0)), --开嘱医生+录入人姓名+核对人姓名 --用法信息 '', --治疗项目信息 --decode(a.jzbz00,'Y','(急)',decode(a.pcmc00,'s.t.','(急)',''))|| trim(SF_ZS_GETFYYZMC_ZYDZBL(a.yzmxid)) ||decode((select value0 from xt_xtcs00 where name00='YS_SFXMKQYY'),'Y',decode(a.zlxmid,0,'(描述)'),trim(nvl((select ' ' from bq_yjyzjj f where f.yzmxid = a.yzmxid and rownum =1),'(描述)'))) ||' ' --治疗项目名称 ||decode((select nvl(VALUE0,'N') from XT_XTCS00 where NAME00 = 'ZS_YZXXSFXSBBLX'),'Y', A.BB0000,null)||' '--标本类型 ||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.clbz00,'1','',decode(sign(to_date(nvl(a.sjqsrq,a.qyrq00),'yyyyMMdd')-to_date(a.qyrq00,'yyyyMMdd')),1,'[首0]','')) --首0 ||'['||d.BMMC00||']', '', -- substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5), --停用日期+停用时间 by ZYYS-20111219-001 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)), --停用日期+停用时间 --trim(C.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名 trim(C.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.ZWXM00)),--停止医生+停止人姓名 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.BQH000, --病区 a.ZDID00, a.ZDMC00, a.BQZY00, a.BWMC00, -1 TSYS00, a.bb0000, -- e.zwxm00, decode(a.CLBZ00,'0',(d.BMMC00||'/'||(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)), (d.BMMC00||'/'||(select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))) ), to_char(a.CJKS00) CJKS00, -1 as SRCS00, a.TZRXM0, a.KSH000, a.YZLY00, a.KZYS00, a.LRRXM0, -1, a.SJQSRQ,--用药日期 '', '', 0, d.bmmc00 zxksmc, a.bbid00, nvl(a.ts0000,1), 0 kssjb0, a.zlxmjc, (select mzmc00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--麻醉名称 (select ssqsrq from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始日期 (select ssqssj from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始时间 (select ssdh00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术单号 a.CZPC00, '', --nvl((select '径内' from LJ_YZMX00 where WBMXID=a.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外') LJYZBZ, --径内径外判断 decode(nvl(A.JHMXID,0),0,'径外','径内') LJYZBZ--径内径外判断 from BQ_YJYZ00 a, BM_YGBM00 b, BM_YGBM00 c, BM_BMBM00 d, BM_YGBM00 e where b.YGBH00(+) = a.KZYS00 and c.YGBH00(+) = a.TZYS00 and d.BMBH00(+) = a.ZXKS00 and e.YGBH00(+) = a.TZSHR0 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,6)||' '||substr(a.QYSJ00,1,5), --起用日期+起用时间 trim(b.ZWXM00)||decode(a.HDRXM0,'','','/'||trim(a.HDRXM0)), --开嘱医生+录入人姓名+核对人姓名 --用法信息 '', --治疗项目信息 f.YPMC00|| '['||f.YPGG00 ||/*'/'||f.MZFYDW||*/ ']' || f.ZSL000||f.MZFYDW, '', -- substr(A.TZRQ00,3,6)||' '||substr(A.TZSJ00,1,5), --停用日期+停用时间 by ZYYS-20111219-001 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)), --停用日期+停用时间 --trim(C.ZWXM00)||decode(trim(A.TZRXM0),'','','/'||trim(A.TZRXM0)),--停止医生+停止人姓名 trim(C.ZWXM00)||decode(trim(e.ZWXM00),'','','/'||trim(e.ZWXM00)),--停止医生+停止人姓名 trim(A.BZ0000), --备注 A.QYRQ00, --起用日期 A.TZRQ00, --停止日期 to_char(to_date(A.PXSJ00,'HH24:mi:ss')+1/(24*60*60),'HH24:mi:ss'), --排序时间 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.BQH000, --病区 a.ZDID00, a.ZDMC00, a.BQZY00, a.BWMC00, -1 TSYS00, a.bb0000, -- e.zwxm00, decode(a.CLBZ00,'0',(d.BMMC00||'/'||(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)), (d.BMMC00||'/'||(select zxrxm0 from yj_yw0000 where yjdjh0 in (select max(yjdjh0) from yj_yw0000 where zyghid=a.zyid00 and yzcfid=a.yzmxid))) ), to_char(a.CJKS00) CJKS00, -1 as SRCS00, a.TZRXM0, a.KSH000, a.YZLY00, a.KZYS00, a.LRRXM0, -1, a.SJQSRQ,--用药日期 '', '', 0, d.bmmc00 zxksmc, a.bbid00, nvl(a.ts0000,1), 0 kssjb0, a.zlxmjc, (select mzmc00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--麻醉名称 (select ssqsrq from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始日期 (select ssqssj from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术开始时间 (select ssdh00 from ss_yw0000 where yzmxid=a.yzmxid and zyghid=a.zyid00 and rownum=1),--手术单号 a.CZPC00, '', --nvl((select '径内' from LJ_YZMX00 where WBMXID=a.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外') LJYZBZ, --径内径外判断 decode(nvl(A.JHMXID,0),0,'径外','径内') LJYZBZ--径内径外判断 from BQ_YJYZ00 a, BM_YGBM00 b, BM_YGBM00 c, BM_BMBM00 d, BM_YGBM00 e, VW_YS_ZLDCYP f where b.YGBH00(+) = a.KZYS00 and c.YGBH00(+) = a.TZYS00 and d.BMBH00(+) = a.ZXKS00 and e.YGBH00(+) = a.TZSHR0 and a.ZLXMID=f.ZLXMID /