create or replace view vw_jy_yw0000 as select A.JYYWID, --流水ID:SEQ_JY_YW0000_LSID00 A.YJDJH0, --医技单据号 A.YJFZH0, -- A.ZLXMID, -- b.ZLXMJC, substr(SF_JY_UNION_TXM000(A.TXM000),1,50) ZLXMZH, b.XXMS00, decode((select nvl(zyzbid,'') from jy_jyyqzd where yqdh00=substr(A.ybh000,1,2)),'',b.zyzbid,(select nvl(zyzbid,'') from jy_jyyqzd where yqdh00=substr(A.ybh000,1,2))) zyzbid, --(select ZLXMJC from BM_ZLZD00 where ZLXMID=A.ZLXMID) ZLXMJC, --(select XXMS00 from BM_ZLZD00 where ZLXMID=A.ZLXMID) XXMS00, --(select zyzbid from JY_JYYQZD where yqdh00 in (select YQDH00 from JY_zlbgdy where zlxmid=A.zlxmid) and rownum=1) zyzbid, --(select zyzbid from bm_zlzd00 where zlxmid=A.ZLXMID) zyzbid, A.ZYGHID, --住院挂号ID A.ZYHGHH, --住院(ZYH000)或门诊(GHH000)号(冗余) A.MZZYBZ, --'0'为门诊'1'为住院 A.SFTJ00, --是否体检 decode(A.MZZYBZ,'0','门诊','住院') BRLX00, --病人类型 (select BMMC00 from SF_BRXXB0 T1,BM_BMBM00 T2 where A.ZYGHID=T1.GHID00 and A.MZZYBZ='0' and T1.GHKS00=T2.BMBH00)|| (select BMMC00 from ZY_BRXXB0 T1,BM_BMBM00 T2 where A.ZYGHID=T1.ZYID00 and A.MZZYBZ='1' and T1.DQKS00=T2.BMBH00) SZKS00, A.BRID00, --病人ID (冗余) (select substr(ICKH00,1,100) from IC_YHXX00 where BRID00=A.BRID00 and ZT0000='1' and rownum = 1) ICKH00, (select BRBLH0 from BM_BRXXB0 where BRID00=A.BRID00) BRBLH0, --病人病历号 (select FBBH00 from BM_BRXXB0 where BRID00=A.BRID00) FBBH00, -- (select FBMC00 from BM_BRFBB0 B,BM_BRXXB0 C where B.FBBH00=C.FBBH00 and C.BRID00=A.BRID00) FBMC00, -- --A.BRXM00, --病人姓名(冗余) --nvl(A.YEXM00,A.BRXM00) BRXM00, nvl(A.YEXM00,A.BRXM00) BRXM00, (select BRXM00 from BM_BRXXB0 where BRID00=A.BRID00) GQBRXM, SF_JY_GETPYSM( nvl(A.YEXM00,A.BRXM00)) XMPYSM, SF_JY_GETWBSM( nvl(A.YEXM00,A.BRXM00)) XMWBSM, A.YEXM00, --婴儿姓名 A.XB0000, --性别(冗余) decode(A.XB0000,'M','男','F','女','') XBMC00, --性别名称 A.CSRQ00, --出生日期(冗余) --NL0000 , --年龄(冗余) --trim(substr(SF_JY_CSRQTONL_NYXS(CSRQ00),1,10)) NL0000, --年龄 trim(substr(A.NL0000||decode(A.NLDW00,'Y','岁','D','天','M','月','W','周'),1,10)) NL0000, ' ' NLDW00, --年龄单位 ' ' NLDWMC, A.CH0000, --*床号(冗余) A.YBH000, --检验样本号 A.YXBZ00, --优先标志 0-普通 1-紧急 A.ZDID00, --诊断内部码(对应XT_ICD900.ICD900) substr(SF_JY_GET_ZDMC00(A.YJDJH0,A.ZDID00),1,50) ZDMC00, --诊断名称 A.SLJDDM, --病人状态 PK:JY_SLJDZD.SLJDDM (select SLJDMC from JY_SLJDZD where SLJDDM=A.SLJDDM) SLJDMC, --生理阶段名称 A.BBID00, --标本ID (select BBMC00 from BM_BBZD00 where BBID00=A.BBID00) BBMC00, --标本名称 A.BBSM00, --标本说明(标本采集的条件说明，如饭前、饭后一小时等) A.BBCJL0, --标本采集量 A.BBCJR0, --标本采集人 (select ZWXM00 from BM_YGBM00 where YGBH00=A.BBCJR0) CJRXM0, --采集人姓名 A.BBSDR0, --标本采集人 (select ZWXM00 from BM_YGBM00 where YGBH00=A.BBSDR0) SDRXM0, --收到人姓名 A.BBSDRQ, --标本收到日期(即样本签收日期) A.BBSDSJ, --标本收到时间 substr(nvl(A.BBSDRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'|| substr(nvl(A.BBSDRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'|| substr(nvl(A.BBSDRQ,To_char(sysdate,'YYYYMMDD')),7,2)||' '||substr(A.BBSDSJ,1,5) SDRQSJ , A.BBCYRQ, --标本采样日期 substr(nvl(A.BBCYRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'|| substr(nvl(A.BBCYRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'|| substr(nvl(A.BBCYRQ,To_char(sysdate,'YYYYMMDD')),7,2) CYRQ00, A.BBCYSJ, --标本采样时间 --substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'|| --substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'|| --substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),7,2)||' '||BBCYSJ CYRQSJ, --采样日期时间 substr(sf_jy_getBBCYRQSJ(A.PYRQ00,A.PYSJ00,A.BBCYRQ,A.BBCYSJ,A.TXM000,A.BBSDRQ,A.BBSDSJ,A.MZZYBZ),1,20) CYRQSJ, A.SQRQ00, --申请日期 substr(A.SQRQ00,1,4)||'.'||substr(A.SQRQ00,5,2)||'.'||substr(A.SQRQ00,7,2) SQ0000, A.SQSJ00, --申请时间 A.SQKS00, --申请科室 (select BMMC00 from BM_BMBM00 where BMBH00=A.SQKS00) SQKSMC, --申请科室名称 A.SQBQ00, --申请病区 (select BMMC00 from BM_BMBM00 where BMBH00=A.SQBQ00) SQBQMC, --申请病区名称 A.SQYS00, --申请医生 decode(A.SQYS00,0,NULL,(select ZWXM00 from BM_YGBM00 where YGBH00=A.SQYS00)) SQYSXM, --申请医生姓名 A.PYRQ00, --排样日期 decode(A.PYRQ00,null,null,substr(A.PYRQ00,1,4)||'.'||substr(A.PYRQ00,5,2)||'.'||substr(A.PYRQ00,7,2)) PY0000, A.PYSJ00, --排样时间 A.BBSDRQ||A.YBH000 WYYBH0 , A.JGZT00, --结果状态 decode(A.JGZT00,'0','已申请','1','已采集','2','已签收','3','已退回','4','已排样','5','已处理','6','已审核','7','已删除','8','已打印','9','已取单','未知') JGZTMC, A.THYY00, --退回原因 A.CXSHYY, --撤销审核原因 A.CBGRQ0, --出报告日期 decode(A.CBGRQ0,null,null,substr(A.CBGRQ0,1,4)||'.'||substr(A.CBGRQ0,5,2)||'.'||substr(A.CBGRQ0,7,2)||' '||CBGSJ0) BG0000, A.CBGSJ0, --出报告时间 A.BGR000, --报告人(报告签发者或检验操作者) nvl(A.BGRXM0,(select ZWXM00 from BM_YGBM00 where YGBH00=A.BGR000)) BGRXM0, --报告人姓名 BGJYR0, --报告人结果校对者 nvl(A.BGJYXM,(select ZWXM00 from BM_YGBM00 where YGBH00=A.BGJYR0)) JYRXM0, --校对者姓名 (select DZQM00 from BM_ZJ0000 where YGBH00=A.BGJYR0 and A.MZZYBZ='0') DZQM00, --电子签名 SF_JY_GET_ZJE000(A.YJDJH0,A.ZLXMID) ZJE000, A.SFBZ00, --计价标志(0-未计价 1-已计价，收费程序使用) decode(A.SFBZ00,'0','未计价','1','已计价','2',decode(jgzt00,'7','已退费','已收费'),'3',decode(jgzt00,'7','已冲销','已执行'),'6','已作废','未知') SFBZMC, A.BGDYBZ, --报告打印标志 0-未打印 1-已打印 A.TXM000, --条形码 decode(A.TXM000,'','0000000000',TXM000) TXM001, --报告条形码 substr(A.TXM000,1,2) TXMQZ0, --条形码前缀 (select TXMYSZ from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) TXMYSZ, --条形码颜色值 A.YBLX00, A.JYDLY0, --检验单来源 (0 :医生开具, 1:检验科开... )(目的:检验单,是否允许修改) A.DYBZ00, A.YBHBBZ, --样本合并标志'0'表示此次的检验结果,不参与合并,'1':表示可以参与合并 A.MZKID0, A.YQDH00, (select TYDWMC from BM_BRXXB0 where BRID00=A.BRID00) TYDWMC, A.DYBZ00||decode((select TRIM(VALUE0) from XT_XTCS00 where NAME00='JY_SFDYDWMC'),'N',NULL, decode((select TYDWMC from BM_BRXXB0 where BRID00=A.BRID00),NULL,NULL,'单位: '|| (select TYDWMC||BRDH00 from BM_BRXXB0 where BRID00=A.BRID00))) BZDWMC, A.LRY000, --录入医生 (select ZWXM00 from BM_YGBM00 where YGBH00=A.LRY000) LRYXM0, --录入医生姓名 A.BGCKR0, (select ZWXM00 from BM_YGBM00 where YGBH00=A.BGCKR0) CKRXM0, -- A.BGCKSJ, A.SGCJQZ, (select TMSFWK from BM_ZLZD00 where ZLXMID=A.ZLXMID) SFTMWK, --采集时条码是否为空 A.KWH000, A.CJDYRQ, A.CJBZ00, A.BBBZ00, --标本备注 --jvs --(select ZJE000 from YJ_YW0000 where YJDJH0=A.YJDJH0) SFJE00, SF_JY_GET_ZJE000(A.YJDJH0,A.ZLXMID) SFJE00, A.QDXH00, (select SJ0000 from JY_JYYQZD where YQDH00 = A.YQDH00) SJ0000, --试剂 A.TDJTTM, A.SFJM00, A.SFYB00, A.CZY000, --操作员 nvl(A.CZYXM0,(select ZWXM00 from BM_YGBM00 where YGBH00=A.CZY000)) CZYXM0, --操作员姓名 (select YJKSBH from YJ_YW0000 where YJDJH0=A.YJDJH0) YJKSBH, decode(A.MZZYBZ,'0','2',(select substr(BRZT00,1,1) from ZY_BRXXB0 where ZYID00=A.ZYGHID)) BRZT00, (select BGYSZ0 from JY_JYYQZD where YQDH00=A.YQDH00) BGYSZ0, --报告颜色值 (select QZMC00 from JY_QDXHQZ where CJXQ00=(select to_char(to_date(B.BBCYRQ,'yyyymmdd'),'day') from JY_YW0000 B where B.YJDJH0 = A.YJDJH0 and rownum = 1)) QZMC00 , --取单前缀 A.SFKY00, --是否科研 (select LCPY00 from JY_XJJGPY where YBH000=A.YBH000 and PYRQ00=A.PYRQ00) LCPY00, --临床评语,细菌 A.TMSFSF, nvl(A.DYCS00, 0) DYCS00, --打印次数 (select DZQM00 from BM_ZJ0000 where YGBH00=A.BGR000) BGRQM0, --报告人签名 (select DZQM00 from BM_ZJ0000 where YGBH00=A.CZY000) CZYQM0, --操作员签名 (select DZQM00 from BM_ZJ0000 where YGBH00=A.BGJYR0) FHZQM0, --复核者签名 SF_JY_GETQSRQSJ(A.BBCYRQ,A.BBCYSJ,A.BBSDRQ,A.BBSDSJ) QSRQSJ, (select SDRQ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1) HGSDRQ, --护工收到标本日期 (select SDSJ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1) HGSDSJ, --护工收到标本时间 (select SDRQ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='2'and rownum=1) HGSCRQ, --护工标本送到检验科日期 (select SDSJ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='2'and rownum=1) HGSCSJ, --护工标本送到检验科时间 (select ZWXM00 from BM_YGBM00 where YGBH00=(select SCR000 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1)) HGXM00, --护工姓名 A.CJKSBH , --采集科室编号 SF_JY_GETYQMC(A.YBH000,A.PYRQ00) YQMC00, SF_JY_GETXJALL(A.PYRQ00,A.YBH000) XJALL0, SF_JY_GETSYXJ(A.PYRQ00,A.YBH000) SYXJ00,--所有细菌 decode(A.MZZYBZ,'0',nvl((select decode(SFCZ00,'0','初诊','复诊') from sf_brxxb0 where ghid00=A.zyghid) ,''),'') CFZBZ0, A.SFLSTD, nvl(A.JYBGRS,A.ZZBGRS) DYRQSJ, nvl((select ZWXM00 from BM_YGBM00 where YGBH00=A.JYBGDY),A.ZZBGDY) BGDYR0, SF_JY_UNION_ZDMC00(A.ZYGHID) SYZDMC , --病人所有诊断 decode(A.JGZT00,'8','补打','') SFBD00, --是否补打 A.QSBZ00 , --签收备注 A.JYJS00 , --建议解释 decode(nvl(A.DYCS00,0),0,'','补打') BDXX00, SF_JY_GETJYJS(A.PYRQ00,A.YBH000) ZJJYJS, (select decode(lengthb(trim(BRLXDH)),11,BRLXDH,decode(BRDH00,'0',null,BRDH00)) from BM_BRXXB0 where BRID00=A.BRID00) BRDH00 , A.ZZJFS0, --自助机发送 A.SFXWFS, --是否向外发送 A.ZZBGRS, A.SFCTAT, --是否超出TAT设置时间 A.JYQSJ0, --检验前时间 A.JYZSJ0, --检验中时间 A.JYHSJ0, --检验后时间 decode(A.YXBZ00,'N','否','Y','是','否') SFJZ00, --是否急诊 A.SJRQ00,--送检日期 A.SJSJ00, --送检时间 A.BGCKCS, --报告查看超时 decode(a.mzzybz,'1','1',decode(a.SQKS00,'38','1',decode(a.ZLXMID,'76584','1','82368','1',A.DXSFFS))) DXSFFS, --短信是否发送 A.YYID00, (select BMMC00 from BM_BMBM00 where BMBH00=A.CJKSBH and rownum=1) CJKSMC, --采集科室名称 A.TMDYR0, (select e.zyzbmc from bm_jyzyzb e where e.zyzbid=(select zyzbid from bm_zlzd00 where zlxmid=A.ZLXMID)) ZYZBMC, (select ZWXM00 from BM_YGBM00 where YGBH00=A.TMDYR0) TMDYXM, decode((select 2 from JY_JYYQZD b where YQMC00 like 'BICLIAISON%' and b.YQDH00 = A.YQDH00 and rownum <= 1),2,'2', '1') JYLX00, --1检验 2细菌 --decode(nvl(trim(A.YYID00),'0'),'0',(select YYID00 from BM_BMBM00 where BMBH00=A.SQKS00),A.YYID00) ZXYYID, A.YYID00 ZXYYID, SFFS02, A.SFWJZ0,--是否危急值，0非危急值，1危急值 A.TXKWJZ,--提醒查看危急值,0未提醒1已提醒，默认0 (select BRZJBH from BM_BRXXB0 where BRID00=A.BRID00) BRZJBH,--身份证号 (select BH0000 from BM_BMBM00 where BMBH00=A.SQKS00) BH0000,--部门编号 A.XBJS00,--骨髓细菌计数 A.CKFKNR,--危急值反馈内容 (select BRJTDZ from BM_BRXXB0 where BRID00=A.BRID00) BRJTDZ,--病人家庭地址 (select BRLXDZ from BM_BRXXB0 where BRID00=A.BRID00) BRLXDZ, --病人联系地址 A.TSYBBH, A.SFYC00, A.WJZWCK, --危急值未查看提醒标志 (select DZQM00 from BM_ZJ0000 where YGBH00=decode((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_SFSYSJYSSH'),'Y',SF_JY_GETMRYG0(A.BGR000,A.YQDH00),A.BGJYR0)) SHRQM0,--审核人签名 A.SFYBWR from JY_YW0000 A,BM_ZLZD00 B where A.ZLXMID=B.ZLXMID(+) ;