create or replace view vw_yj_ywjj00 as select c.YJDJH0, C.BRXM00, c.ZLXMID, c.ZLXMJC, D.BRBLH0, C.XB0000, C.NL0000, C.QSRQ00||' '||C.QSSJ00 QSRQSJ, (select ZWXM00 from BM_YGBM00 where YGBH00=C.QSR000) SMRXM0, (select XKH000 from BM_YGBM00 where YGBH00=C.QSR000) SMRXKH, c.SMKSBH, (select BMMC00 from BM_BMBM00 where BMBH00=c.SMKSBH ) SMKSMC, (select ZYZBMC from BM_JYZYZB E,BM_ZLZD00 F where F.ZLXMID=C.ZLXMID and E.ZYZBID=F.ZYZBID) ZYZBMC, C.TXM000, decode(C.XMZT00,'0','已申请','1','已计价','2','已收费','3','已收费','4','已报告','5','疑退','6','已作废','7','已退费') XMZTMC from YJ_YW0000 C,BM_BRXXB0 D where C.BRID00=D.BRID00 order by QSRQSJ; create or replace view vw_yj_yw0000 as select y.BRID00, --病人id y.ZYGHID, --住院挂号ID y.YJKSBH, --医技科室编号 y.YJDJH0, --医技单据号 y.YJFZH0, --分组号 y.ZYHGHH, --住院挂号号 decode(y.YEXM00,null,y.BRXM00,y.YEXM00||'('||y.BRXM00||')') as BRXM00, --病人姓名 decode(y.MZZYBZ,'1',(select YBMC00 from IC_YBBRLB i,ZY_BRXXB0 j where i.FBBH00 = j.FBBH00 and i.YBLB00 = j.YBLB00 and j.ZYID00 = y.ZYGHID),(select YBMC00 from IC_YBBRLB where YBLB00=d.YBLB00 and FBBH00=d.FBBH00)) as BRYBMC, --医保名称(门诊住院分开取) y.YEXM00, --婴儿姓名 y.XB0000, --性别 y.CSRQ00, --出生日期 substrb(SF_XT_CSRQTONL(y.CSRQ00,y.KDRQ00),1,8) as BRNL00, --病人年龄 y.ZLXMID, --诊疗项目id y.ZLXMJC, --诊疗项目简称 y.KDYSXM, --开单医生姓名 b.BMMC00 as KDKSMC, --开单科室名称 y.KDKSBH, --开单科室编号 y.KDRQ00, --开单日期 y.JZBZ00, --急诊标志 y.BWMC00, --部位名称 y.BQZY00, --病情摘要、开单目的 y.APRQ00, --安排日期 y.APSJ00, --安排时间 substr(SF_YJ_JBZDMC(y.YJDJH0), 1, 100) as ZDMC00, --诊断名称 y.YZCFID, --处方号 decode(y.DCBZ00, '0',null, y.YZCFID) y.XMZT00, --项目状态 y.MZZYBZ, --门诊住院标志 y.JGLR00, --结果 y.BZ0000, --备注 decode(y.APRQ00, null,'N','','N','Y') as APBZ00, --已经安排排标志 y.ZJE000, -- nvl((select X.APBZ00 from XT_KSZXXM X where X.ZXKSBH=y.YJKSBH and X.ZLXMID=y.ZLXMID and rownum=1),'N') as SFXYAP,--是否需要安排标志 y.KDYS00, -- y.ZDID00, -- y.SL0000, -- y.ZXSL00, -- y.DCBZ00, -- decode(y.JZBZ00, null, '否', 'Y', '是', 'N', '否', '否') as SFJZ00, decode(y.XMZT00,'0',decode(sign(y.SL0000),1,'未收费','申请退费'), '1',decode(sign(y.SL0000),1,'未收费','申请退费'||decode(y.TFSHRQ,null,'','(已审核)')), '2',decode(sign(y.SL0000),1,'已收费','已退费')||decode(sign(y.CXSL00),1,'(已被申退)',''), '3',decode(sign(y.SL0000),1,'已执行','已退费')||decode(sign(y.CXSL00),1,'(已被申退)',''), '4','已报告', '5','疑退', '6','已作废', '7','已退费', 'T','申请退费', '9','未审核') as ZT0000, --医技项目状态 decode(y.MZZYBZ,'1','住院','门诊') as MZZY00, --门诊住院状态名称 y.BGYSXM, -- y.JGBGRQ, -- y.ZXRQ00, -- y.ZXSJ00, -- y.SFDJH0, -- nvl(y.CXDJH0,0) CXDJH0, -- (select X.JGBGBZ from XT_KSZXXM X where X.ZXKSBH=y.YJKSBH and X.ZLXMID=y.ZLXMID and rownum=1) as JGBGBZ, -- y.LRXM00, -- y.KDSJ00, -- y.YJLRBZ, -- y.KDBQ00, -- y.ZXRXM0, -- y.SM0000, -- y.ZLDCCF, -- y.CFID00, -- y.BBID00, -- (select BBMC00 from BM_BBZD00 where BBID00 = y.BBID00 and rownum=1) as BBMC00, --标本名称 y.CXSL00, -- y.SL0000-nvl(y.CXSL00,0) as SJSL00, --实际数量 y.TXM000, -- nvl(y.TSBSMC,(select BS0000 from YS_XMSQD0 where SQID00(+)=y.SQID00)) as BS0000, -- nvl(y.ZSDYMC,(select ZS0000 from YS_XMSQD0 where SQID00(+)=y.SQID00)) as ZS0000, -- d.BRDH00 as BRLXDH, -- (select ZXWXTS from BM_BMBM00 where BMBH00(+)=y.YJKSBH) as ZXWXTS, --技科室执行时显示的温馨提示 y.SYLSH0, -- decode(y.MZZYBZ,'1',(select FBBH00 from ZY_BRXXB0 where ZYID00 = y.ZYGHID),d.FBBH00) as FBBH00, -- -- e.LBBH00, -- y.ZXR000, -- (select nvl(sum(ZXCS00),0) from YJ_YWZXJL where YJDJH0=y.YJDJH0 and ZXR000 is not null and ZXLB00<>'4') as SGZXCS, b.YYID00 as KDYYID, nvl(y.ZXYS00, y.KDYS00) as ZXYS00, (select ZWXM00 from BM_YGBM00 where YGBH00=nvl(y.ZXYS00,y.KDYS00)) as ZXYSXM, decode(y.MZZYBZ,'1',nvl((select CH0000 from BM_CWK000 where ZYID00=y.ZYGHID and rownum=1),y.CH0000),y.CH0000) as CH0000, --床号 (select KMH000 from IC_YHXX00 where BRID00=d.BRID00 and ZT0000='1' and rownum=1) as KMH000, decode(y.KDRQ00, null, null,substr(y.KDRQ00,3,2)||'-'||substr(y.KDRQ00,5,2)||'-'||substr(y.KDRQ00,7,2)||' '||substr(y.KDSJ00,1,5)) KDRQSJ, --开单日期时间 decode(y.ZXRQ00, null, null,substr(y.ZXRQ00,3,2)||'-'||substr(y.ZXRQ00,5,2)||'-'||substr(y.ZXRQ00,7,2)||' '||substr(y.ZXSJ00,1,5)) ZXRQSJ, --执行日期时间 y.YPNM00, --皮试药品内码 (select BMMC00 from BM_BMBM00 where BMBH00 = y.YJKSBH) as YJKSMC, --医技科室名称 y.DW0000, case when y.MZZYBZ<>'1' then (select decode(REQUISITION_STATE,'reported','报告完成','preinspect','对照完成','printed', decode(y.DYRQTM,null,'条码生成','条码打印'),'canceled','条码撤销','state','声明','deleted','条码删除', 'returned','标本退回','inspecting','检验确认','samplinged','采集确认', 'received','接收确认','appended','项目追加','未定义') from HIS_REQUISITION where HIS_ID=to_char(y.YJDJH0) and rownum=1) else (select decode(STATE0,'reported','报告完成','preinspect','对照完成','printed', decode(y.DYRQTM,null,'条码生成','条码打印'),'canceled','条码撤销','state','声明','deleted','条码删除', 'returned','标本退回','inspecting','检验确认','samplinged','采集确认', 'received','接收确认','appended','项目追加','未定义') from YJ_BBZJB0 where YJDJH0=to_char(y.YJDJH0) and rownum=1) end as BBZT00, --标本状态 case when y.Mzzybz<>'1' then (select REQUISITION_ID from HIS_REQUISITION where HIS_ID=to_char(y.YJDJH0) and rownum=1) else (select TXM000 from YJ_BBZJB0 where YJDJH0=to_char(y.YJDJH0) and rownum=1) end as JYTXM0, --检验条形码 nvl(e.JCLBID,0) as JCLBID, (select BGURL0 from BM_JCLB00 where JCLBID=e.JCLBID) as BGURL0, --报告URL (select YXURL0 from BM_JCLB00 where JCLBID=e.JCLBID) as YXURL0, --影像URL nvl(e.JCZID0,0) as JCZID0, nvl(y.YYID00,0) as YYID00, y.HBXHID, nvl((select YYZT00 from YJ_YYBRB0 where YYID00=y.YYID00 ),'-1') as YYZT00, y.HBDJH0, y.SQID00, decode(nvl(y.YYID00,0),0,'','已预约') as YYBZ00, --预约标志 decode(nvl(y.YYID00,0),0,'',(select JCSQDH from YJ_YYBRB0 where YYID00=y.YYID00 and YYZT00<>'2')) as JCSQDH,--检查申请单号 decode(y.XMZT00,'0',decode(sign(y.SL0000),1,'未收费','申请退费'), '1',decode(sign(y.SL0000),1,'未收费','申请退费'||decode(y.TFSHRQ,null,'','(已审核)')), '2',decode(sign(y.SL0000),1,'已收费','已退费'), '3',decode(sign(y.SL0000),1,'已收费','已退费'), '4',decode(sign(y.SFDJH0),1,'已收费','未收费'), '5','疑退','6','已作废','7','已退费','T','申请退费','9','未审核') as SFZTMC, --收费状态名称 (select LBMC00 from BM_ZLXMLB where LBBH00=e.LBBH00) as LBMC00, --类别名称 decode(y.MZZYBZ,'0',(select GHRQGG from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHRQ00, --挂号日期 decode(y.MZZYBZ,'0',(select JZYSXM from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as JZYSXM, --就诊医生 decode(y.MZZYBZ,'0',(select GHKS00 from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHKS00, --挂号科室 decode(y.MZZYBZ,'0',(select GHKSMC from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHKSMC, --挂号科室名称 decode(y.MZZYBZ,'1',(select DQBQ00 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQBQ00, --当前病区 decode(y.MZZYBZ,'1',(select DQBQMC from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQBQMC, --当前病区名称 decode(y.MZZYBZ,'1',(select DQKS00 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQKS00, --当前科室 decode(y.MZZYBZ,'1',(select DQKSMC from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQKSMC, --当前科室名称 decode(y.MZZYBZ,'1',(select RYCWH0 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as RYCWH0, --床位号 y.YSZID0, --医生组ID y.SJBBMC, --送检标本名称 y.HBZYXM, --合并住院项目标志 1代表合并 0或者空为不合并 y.BWID00, --部位ID nvl(y.SFZF00,'0') as SFZF00, --是否自费 y.TFSHR0, --退费审核人 (select ZWXM00 from BM_YGBM00 where YGBH00 = y.TFSHR0) as TFSHXM, --退费审核人姓名 y.TFSHRQ, --退费审核日期 y.TFSHSJ, --退费审核时间 y.TFSHRQ||' '||y.TFSHSJ as TSRQSJ, --退费审核日期时间 y.KYJCJG, --抗原检测结果 y.SFHXYZ, --是否回写医嘱：0不回写，1在扣费时回写医嘱（BQ_YJYZ00） decode(y.SFHXYZ,'1','是','否') as HXYZMC, --是否回写医嘱(是、否) e.ZYZBID, --检验专业组别内部号 BM_JYZYZB.ZYZBID e.SFQZX0, --是否抢执行 decode(sign(y.SL0000),1,null,decode(y.SFDJH0,0,0,1)) as SFGZ00, --冲销单是否收费（1冲销单已收费，0冲销单未收费） decode(y.HBZYXM,'2','是','否') as HBZYXM_MC, --是否门转住 y.YQJJBZ, decode(y.YQJJBZ,'1','是','否') as YQJJBZ_MC, y.DBTXM0, --打包条形码 e.CXGYS0, --采血管颜色 e.CXGXX0, --采血管信息 (select ZYZBMC from BM_JYZYZB f where ZYZBID=e.ZYZBID) ZYZBMC, e.Qjydsj, (select ZWXM00 from bm_ygbm00 where YGBH00=y.QSR000) QSR000, y.qsrq00||' '||y.qssj00 as QSRQSJ from YJ_YW0000 y, BM_BMBM00 b, BM_BRXXB0 d, BM_ZLZD00 e where y.BRID00 = d.BRID00 and y.YJKSBH <> 0 and y.KDKSBH = b.BMBH00 and y.ZLXMID = e.ZLXMID and nvl(y.FJDJBZ,'0') not in('3','4');