CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ13_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, WZDJ00, YPNM00, JKZTMC, ZYH000, DQBQ00, SJZXRQ, SPZTMC, TZYS00, LRRQSJ, CZPC00 ) 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 A.QYRQ00|| ' ' || A.QYSJ00,-- substr(A.QYRQ00,5,2)||'-'||substr(A.QYRQ00,7,2)||' '||substr(A.QYSJ00,1,5),--起用日期+起用时间 --trim(C.ZWXM00) || '/' || trim(A.LRRXM0) || '/' || trim(A.HDRXM0), --开嘱医生 + 录入人姓名 + 核对人姓名 trim(A.LRRXM0) || '/' || nvl(A.TJYSXM, (select trim(ZWXM00) from BM_YGBM00 where A.TJYS00 = YGBH00))|| '/' || 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.CLBZ00,'1', decode((select VALUE0 from XT_XTCS00 where name00 = 'BQ_CXYZTJQXQZ'),'Y',decode(instr(SF_BQ13_ZXZT00(a.ZYID00, a.YZID00, 0), '(退)'), 0, '', '(取消)'),''),'') || decode('', 'Y', '(急)', decode(a.PCMC00, 's.t.', '(急)', '')) || decode(a.blbz00, 'Y', '(补)', '') || ''--同组内序号 -- || decode(F.GWYPFL, '0', null, '(高危)') || decode(f.WZDJ00,'1','(高危)',decode(nvl(F.GWYPFL,'0'),'0',decode(nvl('','-'),'-','','0','','(高危)'),'(高危)')) || trim(B.YPMC00)--药品名称 || decode(b.YPGG00, '', '', '[' || trim(B.YPGG00) || ']')--药品规格 || decode(nvl((select value0 from xt_xtcs00 where name00 = 'BQ_YZNRSFXSJL'), 'N'),'N', 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(b.ZFFSBZ, 'Y', '(自费)'),--药品材质分类 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))), --停止医生 + 停止人姓名 decode((select count(1) from XT_XTCS00 where name00 = 'BQ_YZLBSFXSZF' and value0='Y'),1,A.YYZF00,trim(B.BZ0000)),--备注 A.QYRQ00, --起用日期 A.TZRQ00, --停止日期 A.PXSJ00, --排序时间 A.ZXRQ00, --执行日期 --substr(A.TQRQ00, 3, 6) || ' ' || substr(A.TQSJ00, 1, 5), --提取日期 A.TQRQ00 || ' ' || A.TQSJ00, --提取日期 A.LRRQ00,--录入时间 A.LRRQ00 || ' ' || 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, '' DJSFQS,--代煎是否全送 0：否 1：是 --decode(a.YFBMBH,null,'', to_char(a.YFBMBH)), --(select BMMC00 from BM_BMBM00 where BMBH00 = a.YFBMBH) YFBMMC, decode(nvl((select value0 from XT_XTCS00 where name00 = 'BQ_SFQYTZYPBTYF' and value0 = 'Y'), 'N'),'Y', to_char(a.YFBMBH), decode(a.YFBMBH, null, '', to_char(a.YFBMBH))), (select BMMC00 from BM_BMBM00 where BMBH00 = decode(nvl((select value0 from XT_XTCS00 where name00='BQ_SFQYTZYPBTYF' and value0='Y'),'N'),'Y',a.yfbmbh,a.yfbmbh)) YFBMMC, '', --麻醉名称 '',--手术开始日期 '',--手术开始时间 a.YYRQ00,--用药日期 SF_BQ13_ZXZT00(a.ZYID00, a.YZID00, 0) ZXZT00,--执行状态, decode(a.PCMC00, 's.t.', '急', '') || decode(a.YZLX00, 1, '术', 2, '血', '') || decode(b.YZCLLB, '出院带药', '出', '') ||(select '皮' from XT_XTCS00 where name00='BQ_SFXSPSBZ' and value0='Y' and b.psid00 is not null) JSCX00,--急、术、出、血字样分别代表急诊、手术、出院、输血 (select ceil(ZQCS00 / HSZQCD * 24) from BM_PC0000 where PCID00 = a.PCID00 and rownum = 1) PSPCCS,--配送批次次数 a.YZLX00, --医嘱类型0:普通医嘱1: 手术医嘱2: 输血医嘱 '' as JZBZ00, --急诊标志Y急诊 a.KSH000, a.hdrq00||' '||a.hdsj00, -- a.HDRQSJ, nvl(h.YFLBID, -1), -1, --会诊id decode(f.WZDJ00, '1', '高危', '') WZDJ00--危重等级 ,b.ypnm00 ,null JKZTMC ,e.ZYH000--住院号 ,e.DQBQ00 ,B.TQRQ00 SJZXRQ ,decode(nvl(b.SPZT00,'0'),'0','','1','已提交未审批','2','审批通过','3','审批不通过,可双签','4','双签名通过','5','审核不通过') SPZTMC ,A.TZYS00 ,substr(A.LRRQ00,5,2)||'-'||substr(A.LRRQ00,7,2)||' '||substr(A.LRSJ00,1,5) LRRQSJ ,a.czpc00 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 A.QYRQ00 || ' ' || A.QYSJ00,--起用日期 + 起用时间 --trim(b.ZWXM00) || '/' || trim(a.LRRXM0) || '/' || trim(a.HDRXM0), --开嘱医生 + 录入人姓名 + 核对人姓名 trim(A.LRRXM0) || '/' || nvl(A.TJYSXM, (select trim(ZWXM00) from BM_YGBM00 where A.TJYS00 = YGBH00))|| '/' || trim(A.HDRXM0), --录入医师 / 提交医师 / 核对护士 --用法信息 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 , --治疗项目信息 decode(A.CLBZ00, 1, decode((select VALUE0 from XT_XTCS00 where name00 = 'BQ_CXYZTJQXQZ'), 'Y', decode(instr(SF_BQ13_ZXZT00(a.ZYID00, a.YZMXID, 1), '(退)'), 0, '', '(取消)'), ''),'') --decode(a.JZBZ00, 'Y', '(急)', decode(a.PCMC00, 's.t.', '(急)', '')) || trim(SF_BQ13_GETFYYZMC(a.YZMXID)) --|| trim(nvl((select ' ' from BQ_YJYZJJ f where f.YZMXID = a.YZMXID and rownum = 1), '(描述)')) || decode(a.yzlb00,9,decode(instr(nvl(trim(SF_BQ13_GETFYYZMC(a.YZMXID)),' '),'描述'), 0, '(描述)', ''),'') || ' '--治疗项目名称 || decode(a.YZCLLB, '正常', '', '自备', '[备]', '嘱托', '[嘱]', '门诊处理', '[门]', '')--处理 || trim(A.BZ0000) || decode(a.ZFFSBZ, 'Y', '(自费)') || decode(a.BB0000,'','','['||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)--麻醉名称 || a.BWMC00 || decode((select SFWSXM from BM_ZLZD00 where ZLXMID = a.ZLXMID),'1','（外送）') , '', 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.TQSJ00, --提取日期 A.LRRQ00,--录入时间 A.LRRQ00 || ' ' || 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, '' AS 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_BQ13_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.hdrq00||' '||a.hdsj00,-- a.HDRQSJ, 1, nvl(a.HZID00, -1), --会诊id '' WZDJ00--危重等级 ,a.zlxmid ,SF_BQ_GETJKZTMC(a.ZYID00, a.YZMXID,a.clbz00) JKZTMC ,e.ZYH000--住院号 ,e.DQBQ00 ,a.TQRQ00 SJZXRQ ,'' SPZTMC ,A.TZYS00 ,substr(A.LRRQ00,5,2)||'-'||substr(A.LRRQ00,7,2)||' '||substr(A.LRSJ00,1,5) LRRQSJ ,a.czpc00 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 /