create or replace view vw_yj_yw0000 (brid00, zyghid, yjksbh, yjdjh0, yjfzh0, zyhghh, brxm00, brybmc, yexm00, xb0000, csrq00, brnl00, zlxmid, zlxmjc, kdysxm, kdksmc, kdksbh, kdrq00, jzbz00, bwmc00, aprq00, apsj00, zdmc00, yzcfid, xmzt00, mzzybz, jglr00, bz0000, apbz00, zje000, sfxyap, kdys00, zdid00, sl0000, zxsl00, dcbz00, sfjz00, zt0000, mzzy00, bgysxm, jgbgrq, zxrq00, zxsj00, sfdjh0, cxdjh0, jgbgbz, lrxm00, kdsj00, yjlrbz, kdbq00, zxrxm0, sm0000, zldccf, cfid00, bbid00, cxsl00, txm000, bs0000, zs0000, brlxdh, zxwxts, sylsh0, fbbh00, lbbh00, zxr000, sgzxcs, kdyyid, zxys00, zxysxm, ch0000, kmh000, kdrqsj, zxrqsj, ypnm00, yjksmc, dw0000, bbzt00, jytxm0, jclbid, bgurl0, yxurl0, jczid0, yyid00, hbxhid, yyzt00, hbdjh0, sqid00, yybz00, jcsqdh, sfztmc, lbmc00, ghrq00, jzysxm, ghks00, ghksmc, dqbq00, dqbqmc, dqks00, dqksmc, rycwh0, yszid0) as select Y.BRID00, --病人id Y.ZYGHID, Y.YJKSBH, --医技科室编号 Y.YJDJH0, --医技单据号 Y.YJFZH0, --分组号 Y.ZYHGHH, --住院挂号号 Y.BRXM00, --病人姓名 (select YBMC00 from IC_YBBRLB where YBLB00=D.YBLB00 and FBBH00=D.FBBH00) BRYBMC, --医保名称 Y.YEXM00, --婴儿姓名 Y.XB0000, --性别 Y.CSRQ00, --出生日期 substrb(SF_XT_CSRQTONL(Y.CSRQ00,y.KDRQ00),1,8) BRNL00, --病人年龄 Y.ZLXMID, --诊疗项目id Y.ZLXMJC, --诊疗项目简称 Y.KDYSXM, --开单医生姓名 B.BMMC00, --开单科室名称 Y.KDKSBH, --开单科室编号 Y.KDRQ00, --开单日期 Y.JZBZ00, --急诊标志 Y.BWMC00, --部位名称 Y.APRQ00, --安排日期 Y.APSJ00, --安排时间 --substr(trim(decode(Y.zdmc00,null,sf_yf_getzd0(y.zyhghh),y.zdmc00)),1,100) zdmc00, --诊断名称 substr(SF_YJ_JBZDMC(Y.YJDJH0), 1, 100) 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'), --已经安排排标志 Y.ZJE000, -- --NVL(X.APBZ00,'N'),--是否需要安排标志 nvl((select X.APBZ00 from XT_KSZXXM X where X.ZXKSBH=Y.YJKSBH and X.ZLXMID=Y.ZLXMID and rownum=1),'N'),--是否需要安排标志 Y.KDYS00, -- Y.ZDID00, -- Y.SL0000, -- Y.ZXSL00, -- Y.DCBZ00, -- DECODE(Y.JZBZ00, NULL, '否', 'Y', '是', 'N', '否', '否'), DECODE(Y.XMZT00,'0',decode(sign(Y.SL0000),1,'未收费','申请退费'),'1',decode(sign(Y.SL0000),1,'未收费','申请退费'), '2',decode(sign(Y.SL0000),1,'已收费','已退费'),'3',decode(sign(Y.SL0000),1,'已执行','已退费'),'4','已报告','5','疑退','6','已作废','7','已退费','T','申请退费','9','未审核'), DECODE(Y.MZZYBZ,'1','住院','门诊'), Y.BGYSXM, -- Y.JGBGRQ, -- Y.ZXRQ00, -- Y.ZXSJ00, -- Y.SFDJH0, -- Y.CXDJH0, -- --X.JGBGBZ, (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, Y.CXSL00, Y.TXM000, NVL(Y.TSBSMC,(select BS0000 from YS_XMSQD0 where SQID00(+)=Y.SQID00)) BS0000, NVL(Y.ZSDYMC,(select ZS0000 from YS_XMSQD0 where SQID00(+)=Y.SQID00)) ZS0000, D.BRDH00 BRLXDH, (select ZXWXTS from BM_BMBM00 where BMBH00(+)=Y.YJKSBH) ZXWXTS, --技科室执行时显示的温馨提示 SYLSH0, D.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'), B.YYID00, NVL(Y.ZXYS00, Y.KDYS00), (select ZWXM00 from BM_YGBM00 where YGBH00=NVL(Y.ZXYS00,Y.KDYS00)) ZXYSXM, Y.CH0000, (select KMH000 from IC_YHXX00 where BRID00=D.BRID00 AND ZT0000='1') 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) 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 BBZT00, --(select REQUISITION_ID from HIS_REQUISITION where HIS_ID=to_char(Y.YJDJH0) and rownum=1) JYTXM0, 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 JYTXM0, nvl(E.JCLBID,0), (select BGURL0 from BM_JCLB00 where JCLBID=E.JCLBID) BGURL0, (select YXURL0 from BM_JCLB00 where JCLBID=E.JCLBID) YXURL0, nvl(E.JCZID0,0), nvl(Y.YYID00,0), Y.HBXHID, nvl((select YYZT00 from YJ_YYBRB0 where YYID00=Y.YYID00 ),'-1') YYZT00, Y.HBDJH0, Y.SQID00, decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00, --预约标志 decode(nvl(Y.YYID00,0),0,'',(select JCSQDH from YJ_YYBRB0 where YYID00=Y.YYID00 and YYZT00<>'2')) JCSQDH, --检查申请单号 DECODE(Y.XMZT00,'0',decode(sign(Y.SL0000),1,'未收费','申请退费'),'1',decode(sign(Y.SL0000),1,'未收费','申请退费'), '2',decode(sign(Y.SL0000),1,'已收费','已退费'),'3',decode(sign(Y.SL0000),1,'已收费','已退费'),'4',decode(sign(Y.SFDJH0),1,'已收费','未收费'),'5','疑退','6','已作废','7','已退费','T','申请退费','9','未审核') SFZTMC, --收费状态名称 (select LBMC00 from BM_ZLXMLB where LBBH00=E.LBBH00) LBMC00, --类别名称 decode(Y.MZZYBZ,'0',(select GHRQGG from VW_SF_BRGHXX where GHID00=Y.ZYGHID and rownum=1),'') GHRQ00, --挂号日期 decode(Y.MZZYBZ,'0',(select JZYSXM from VW_SF_BRGHXX where GHID00=Y.ZYGHID and rownum=1),'') JZYSXM, --就诊医生 decode(Y.MZZYBZ,'0',(select GHKS00 from VW_SF_BRGHXX where GHID00=Y.ZYGHID and rownum=1),'') GHKS00, --挂号科室 decode(Y.MZZYBZ,'0',(select GHKSMC from VW_SF_BRGHXX where GHID00=Y.ZYGHID and rownum=1),'') GHKSMC, --挂号科室名称 decode(Y.MZZYBZ,'1',(select DQBQ00 from VW_ZY_BRXXB0 where ZYID00=Y.ZYGHID and rownum=1),'') DQBQ00, --当前病区 decode(Y.MZZYBZ,'1',(select DQBQMC from VW_ZY_BRXXB0 where ZYID00=Y.ZYGHID and rownum=1),'') DQBQMC, --当前病区名称 decode(Y.MZZYBZ,'1',(select DQKS00 from VW_ZY_BRXXB0 where ZYID00=Y.ZYGHID and rownum=1),'') DQKS00, --当前科室 decode(Y.MZZYBZ,'1',(select DQKSMC from VW_ZY_BRXXB0 where ZYID00=Y.ZYGHID and rownum=1),'') DQKSMC, --当前科室名称 decode(Y.MZZYBZ,'1',(select RYCWH0 from VW_ZY_BRXXB0 where ZYID00=Y.ZYGHID and rownum=1),'') RYCWH0, --床位号 Y.YSZID0 from YJ_YW0000 Y, --XT_KSZXXM X, BM_BMBM00 B, BM_BRXXB0 D, BM_ZLZD00 E where Y.BRID00=D.BRID00 and Y.YJKSBH<>0 --and Y.YJKSBH=X.ZXKSBH(+) and Y.ZLXMID=X.ZLXMID(+) and Y.KDKSBH=B.BMBH00 and Y.ZLXMID=E.ZLXMID and Y.fjdjbz not in('3','4')