-- Start of DDL Script for View SD_HOSPITAL.VW_BQ_YZLL00_ZYYDHL -- Generated 24-十月-2025 15:46:06 from SD_HOSPITAL@ZPXDEYY -- Drop the old instance of VW_BQ_YZLL00_ZYYDHL DROP VIEW vw_bq_yzll00_zyydhl / 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, wzdj00, ypnm00, jkztmc, zyh000, dqbq00, sjzxrq, spztmc, tzys00, lrrqsj, hdr000, fhr000, fhrqsj, gwdj00, yjtzid, yjtzmc ) 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,1,4)||'-'|| 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((select count(1) from XT_XTCS00 where NAME00='BQ_SFQYYZFH' and VALUE0='Y'),0,'','/'||A.FHRXM0), --录入医师/提交医师/核对护士 --用法信息 ' ' ||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_BQ_ZXZT00(a.ZYID00,a.YZID00,0),'(退)'),0,'','(取消)'),''),'') ||decode(a.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)','')) ||decode(a.blbz00,'Y','(补)','') ||b.TZNXH0--同组内序号 --||decode(F.GWYPFL,'0',null,'(高危)') ||decode(f.WZDJ00,'1','(高危)',decode(F.GWYPFL,'0',decode(nvl(F.GWDJ00,'-'),'-','','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,'正常','','自备药','[备]','嘱托','[嘱]','出院带药','[出]','基数药','[基]','门诊带药','[门]','领药','[领]','应急领药','[急]','') --处理 ||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(b.SFMFBZ,'M','(免费)'), 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.TQSJ00, --提取日期 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, decode(NVL((select value0 from XT_XTCS00 where name00='BQ_SFQYTZYPBTYF' and value0='Y'),'N'),'Y',decode(b.yfbm00,null,to_char(a.YFBMBH),to_char(b.yfbm00)), 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',nvl(b.yfbm00,a.yfbmbh),a.yfbmbh)) YFBMMC, '', --麻醉名称 '',--手术开始日期 '',--手术开始时间 a.YYRQ00,--用药日期 SF_BQ_ZXZT00(a.ZYID00,a.YZID00,0,b.YZMXID) 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 ,decode(f.WZDJ00,'1','高危',decode(F.GWYPFL,'0',decode(nvl(F.GWDJ00,'-'),'-','','0','','高危'),'高危')) WZDJ00--危重等级 ,b.ypnm00 ,null JKZTMC ,e.ZYH000 --住院号 ,e.DQBQ00 ,decode((select max(VALUE0) from XT_XTCS00 where NAME00='BQ_YZZXRQLY') ,'2',(select max(Z.QSRQ00) from BQ_YZZXMX Z where a.YZID00=Z.YZID00)) 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.HDR000, A.FHR000, A.FHRQ00 ||' ' ||A.FHSJ00, f.GWDJ00, null YJTZID, null YJTZMC 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,1,4)||'-'|| substr(A.QYRQ00,5,2)||'-'||substr(A.QYRQ00,7,2)||' '||substr(A.QYSJ00,1,5),--起用日期+起用时间 --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)||decode((select count(1) from XT_XTCS00 where NAME00='BQ_SFQYYZFH' and VALUE0='Y'),0,'','/'||A.FHRXM0), --录入医师/提交医师/核对护士 --用法信息 '', --治疗项目信息 decode(A.CLBZ00,1,decode((select VALUE0 from XT_XTCS00 where name00='BQ_CXYZTJQXQZ'),'Y',decode(instr(SF_BQ_ZXZT00(a.ZYID00,a.YZMXID,1),'(退)'),0,'','(取消)'),''),'') --decode(a.JZBZ00,'Y','(急)',decode(a.PCMC00,'s.t.','(急)','')) ||trim(SF_ZS_GETFYYZMC_ZYDZBL(a.YZMXID)) ||decode(instr(A.ZLXMJC,'(描述)'),0,decode((select VALUE0 from XT_XTCS00 where name00='BQ_MSXYZALBXS'),'Y',decode(A.YZLB00,9,'(描述)'),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(a.ZFFSBZ,'Y','(自费)') ||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 ||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,--录入时间 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),--手术开始时间 (select s.MZMC00 from SS_YW0000 s,SS_YWMX00 w where (s.YZMXID=a.YZMXID or w.YZMXID = a.YZMXID) and s.SSDH00 = w.SSDH00 and s.ZYGHID=a.ZYID00 and s.SSZT00<>'9' and rownum=1),--麻醉名称 (select s.SSQSRQ from SS_YW0000 s,SS_YWMX00 w where (s.YZMXID=a.YZMXID or w.YZMXID = a.YZMXID) and s.SSDH00 = w.SSDH00 and s.ZYGHID=a.ZYID00 and s.SSZT00<>'9' and rownum=1),--手术开始日期 (select s.SSQSSJ from SS_YW0000 s,SS_YWMX00 w where (s.YZMXID=a.YZMXID or w.YZMXID = a.YZMXID) and s.SSDH00 = w.SSDH00 and s.ZYGHID=a.ZYID00 and s.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 ,'' WZDJ00 --危重等级 ,a.zlxmid ,SF_BQ_GETJKZTMC(a.ZYID00,a.YZMXID) 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.HDR000, A.FHR000, A.FHRQ00 ||' ' ||A.FHSJ00, '' GWDJ00, a.YJTZID, a.YJTZMC 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