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, bbid00, ljyzbz, byyy00, czpc00, kssyf0, kssyfm, sqid00, yfbmbh, kssjb0, ypyfmc) 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'), --起用日期+起用时间 trim(LRRXM0)|| '/'|| trim(C.ZWXM00)||decode(trim(A.HDRXM0),'','','/'||trim(A.HDRXM0)), --开嘱医生+核对人姓名 --用法信息 decode(A.YPLBBH,'2', a.cfmc00||' ') --处方名称 ||decode(A.YPLBBH,'2',to_char(A.ZQCYTS)||'贴','') --草药贴数 ||decode(A.YPLBBH,'2',decode(A.JYCS00,0,'','代煎'||to_char(A.JYCS00)||'贴'),'') --草药煎药次数 ||decode(A.YPLBBH,'2',decode(nvl(A.ZYMRCS,0),0,'','每日'||to_char(A.ZYMRCS)||'次'),'') --中药每日次数 ||decode(A.YPLBBH,'2','',trim(A.PCMC00)||' ') --频次 ||decode(A.YPLBBH,'2','('|| to_char(to_date(A.YYRQ00,'yyyymmdd'),'fmmm') ||'.'|| to_char(to_date(A.YYRQ00,'yyyymmdd'),'fmdd') || decode(a.zqcyts,1,')','-' || to_char(trunc(to_date(A.YYRQ00,'yyyymmdd'),'dd')+a.zqcyts-1,'fmmm') ||'.' || to_char(trunc(to_date(A.YYRQ00,'yyyymmdd'),'dd')+a.zqcyts-1,'fmdd') ||')' )) --中药贴数 ||decode(A.YPLBBH,'2','',decode(SRCS00,-1,'','[首'||to_char(SRCS00)||']')) ||trim(A.YPYFMC) --用法 ||decode(A.CLBZ00,'0','',decode(A.ZQS000,1,'',to_char(A.ZQS000)||trim(A.ZQDW00))),--短期周期数 --药品信息 SF_BQ_YZTSBZ(B.YZMXID,0)|| 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(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(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, --病区 '' ZDID00, '' ZDMC00, '' BQZY00, '' BWMC00, a.TSYS00, '' BB0000, '' ZXYSXM, '' CJKS00, a.SRCS00, a.TZRXM0, a.KSH000, a.YZLY00, a.KZYS00, a.LRRXM0, b.YPNM00, '' BBID00, nvl((select '径内' from LJ_YZMX00 where WBMXID=b.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外') LJYZBZ, nvl((select BYYY00 from LJ_YZMX00 where WBMXID=b.YZMXID and rownum=1),'') BYYY00, a.CZPC00, b.KSSYF0, nvl((select MC0000 from BM_TYZD00 where ZDMC00='抗生素用法' and BH0000=b.KSSYF0),'')||SF_ZS_GETKJSQMC(b.YZMXID) KSSYFM , 0 SQID00, a.YFBMBH, --药房部门编号 nvl(f.kssjb0,0) kssjb0,--抗生素级别 a.YPYFMC from BQ_YPYZ00 a, BQ_YPYZMX b, BM_YGBM00 c, BM_YGBM00 d, BM_YD0000 f where b.YZID00 = a.YZID00 and c.YGBH00(+) = a.KZYS00 and d.YGBH00(+) = a.TZYS00 and b.ypnm00=f.ypnm00 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'), --起用日期+起用时间 trim(LRRXM0)|| '/'|| trim(b.ZWXM00)||decode(a.HDRXM0,'','','/'||trim(a.HDRXM0)), --开嘱医生+录入人姓名+核对人姓名 --用法信息 '', --治疗项目信息 trim(SF_ZS_GETFYYZMC(a.yzmxid))||' ' --治疗项目名称 ||decode(a.YZCLLB,'正常','','自备','[备]','嘱托','[嘱]','门诊处理','[门]','') --处理 ||trim(a.PCMC00)||' ' --频次 | |case when trim(a.DW0000)= '小时' or a.MCSL00!='1' then decode(substr(a.MCSL00,1,1),'.','0','')||trim(a.MCSL00)||'*'||trim(a.DW0000)||' ' end||' ' --每次数量 ||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||']' ||(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), --停用日期+停用时间 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(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.BBID00, nvl((select '径内' from LJ_YZMX00 where WBMXID=a.YZMXID and (JHMXID>0 or nvl(YZFWLB,0)>0) and rownum=1),'径外') LJYZBZ, nvl((select BYYY00 from LJ_YZMX00 where WBMXID=a.YZMXID and rownum=1),'') BYYY00, a.CZPC00, '' KSSYF0, '' KSSYFM, nvl((select nvl(a.SQID00,0) from BM_ZLZD00 where ZLXMID=a.ZLXMID and nvl(ZYSQID,0)>0) ,-1), 0 YFBMBH, --药房部门编号 0 kssjb0,--抗生素级别 '' from BQ_YJYZ00 a, BM_YGBM00 b, BM_YGBM00 c, BM_BMBM00 d where b.YGBH00(+) = a.KZYS00 and c.YGBH00(+) = a.TZYS00 and d.BMBH00(+) = a.ZXKS00 ;