prompt 1.016 VW_YJ_YW0000 医技业务 create or replace view VW_YJ_YW0000 as -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2013.05.08 重新整理 -- lintj 2013.09.29 加 JCZID0, YYID00 -- zhangwz 2013.11.15 加 SQID00, YYBZ00 -- qks 2014.08.18 加 JCSQDH -- qks 2016.03.02 增加字段SFZTMC by YJ9-20160303-001 -- qks 2016.03.15 SFZTMC取值改为跟ZT0000类似，只修改XMZT00=4取值; by YJ9-20160316-001 -- qks 2016.12.03 多个项目执行后，打印项目执行单速度非常慢; by YJ9-20161204-001 -- qks 2017.12.18 增加 LBMC00; by MZSF9-20171216-003 -- gzj 2019.01.10 增加GHKS00,GHKSMC,DQBQ00,DQBQMC,DQKS00,DQKSMC,RYCWH0 YJ9-20181228-001 -- liwm 2019.03.27 增加YSZID0 YJ9-20190301-001 -- liwm 2019.06.29 病区护士检验采用"集成平台"模式后，因为采用新表YJ_BBZJB0,不再更新同步his_requisition,导致住院病人【标本状态】一栏都为“条码对照”。现场跟踪不到对应查询语句 YJ9-20190617-001 -- liwm 2019.09.30 中间表改取YJ_BBZJB0。VW_YJ_YW0000视图对于住院部分，要改取YJ_BBZJB0 YJ9-20190930-002 -- linshu 2019.12.19 增加送检标本名称 SSMZ9-20191216-001 -- linzetao 2020.02.20 增加合并住院项目标志YJ_YW0000.HBZYXM YJ9-20200219-001 -- linzetao 2020.07.21 增加部位id栏位(BWID00) YJ9-20200716-001 -- jlg 2020.10.15 处理如果是婴儿的项目，则姓名改为婴儿姓名（母亲姓名）的显示方法 YJ9-20201015-001 -- jlg 2020.11.11 住院病人床号改从BM_CWK000获取 YJ9-20201111-001 -- linbin 2021.04.15 增加是否自费栏位(SFZF00) SSMZ9-20210325-001 -- jlg 2021.04.18 将条件 y.FJDJBZ not in('3','4') 改为nvl(y.FJDJBZ,'0') not in('3','4') YJ9-20210418-003 -- jlg 2021.08.05 增加TFSHR0,TFSHRQ,TFSHSJ等退费审核相关字段 YJ9-20210802-001 -- jlg 2021.11.10 重新梳理下本视图，把具体字段名称分解到各取值行中，不再用专门的字段名称 SSMZ9-20211109-001 -- chenrh 2023.07.28 增加JZQRR0,JZQRXM等退费相关字段 YJ13H-20230728-001 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, --病人姓名 (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.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,'已收费','已退费'), '3',decode(sign(y.SL0000),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, -- y.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, y.CXSL00, 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, -- 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') 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, (select YXURL0 from BM_JCLB00 where JCLBID=e.JCLBID) as YXURL0, 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, --合并住院项目标志 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.JZQRR0, --记账人 (select ZWXM00 from BM_YGBM00 where YGBH00 = y.JZQRR0) as JZQRXM --记账人姓名 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') / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%