create or replace view sd_hospital.vw_jy_yw0000_zydzbl as
select
  JYYWID,  --流水ID:SEQ_JY_YW0000_LSID00
  YJDJH0,  --医技单据号 
  YJFZH0,  --
  A.ZLXMID,  --
  (select ZLXMJC from BM_ZLZD00 where ZLXMID=A.ZLXMID) ZLXMJC,
  (select XXMS00 from BM_ZLZD00 where ZLXMID=A.ZLXMID) XXMS00,
  (select ZYZBID from BM_ZLZD00 where ZLXMID=A.ZLXMID) ZYZBID, 
  ZYGHID,  --住院挂号ID  
  ZYHGHH,  --住院(ZYH000)或门诊(GHH000)号(冗余)
  A.MZZYBZ,  --'0'为门诊'1'为住院
  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, --
  (select XM0000 from ZY_BRXXB0 where A.BRID00=BRID00 and rownum = 1) BRXM00, --病人姓名(冗余)
  XB0000,   --性别(冗余)
  decode(XB0000,'M','男','F','女','') XBMC00, --性别名称
  CSRQ00,   --出生日期(冗余)  
  --NL0000 ,  --年龄(冗余)
  --trim(substr(SF_JY_CSRQTONL_NYXS(CSRQ00),1,10)) NL0000, --年龄
  trim(substr(NL0000||decode(NLDW00,'Y','岁','D','天','M','月','W','周'),1,10)) NL0000,
  ' ' NLDW00,   --年龄单位        
  ' ' NLDWMC, 
  CH0000,  --*床号(冗余)  
  YBH000,  --检验样本号
  YXBZ00,  --优先标志 0-普通 1-紧急   
  ZDID00,  --诊断内部码(对应XT_ICD900.ICD900)
  substr(SF_JY_GET_ZDMC00(A.YJDJH0,A.ZDID00),1,50) ZDMC00,  --诊断名称
  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, --标本名称
  BBSM00,  --标本说明(标本采集的条件说明，如饭前、饭后一小时等)        
  BBCJL0,  --标本采集量
  BBCJR0,  --标本采集人
  (select ZWXM00 from BM_YGBM00 where YGBH00=A.BBCJR0) CJRXM0, --采集人姓名
  BBSDR0,  --标本采集人
  (select ZWXM00 from BM_YGBM00 where YGBH00=A.BBSDR0) SDRXM0, --收到人姓名
  BBSDRQ,  --标本收到日期(即样本签收日期)
  BBSDSJ,  --标本收到时间
  substr(nvl(BBSDRQ,to_char(sysdate,'YYYYMMDD')),1,4)||'.'||
  substr(nvl(BBSDRQ,to_char(sysdate,'YYYYMMDD')),5,2)||'.'||
  substr(nvl(BBSDRQ,to_char(sysdate,'YYYYMMDD')),7,2)||' '||substr(BBSDSJ,1,5) SDRQSJ ,
  BBCYRQ,  --标本采样日期
  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) CYRQ00,
  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(PYRQ00,PYSJ00,BBCYRQ,BBCYSJ,TXM000,BBSDRQ,BBSDSJ,A.MZZYBZ),1,18)  CYRQSJ,
  SQRQ00,  --申请日期
  substr(SQRQ00,1,4)||'.'||substr(SQRQ00,5,2)||'.'||substr(SQRQ00,7,2) SQ0000,
  SQSJ00,  --申请时间
  SQKS00,  --申请科室 
  (select BMMC00 from BM_BMBM00 where BMBH00=A.SQKS00) SQKSMC, --申请科室名称
  SQBQ00,  --申请病区
  (select BMMC00 from BM_BMBM00 where BMBH00=A.SQBQ00) SQBQMC, --申请病区名称 
  SQYS00,  --申请医生
  decode(SQYS00,0,null,(select ZWXM00 from BM_YGBM00 where YGBH00=A.SQYS00)) SQYSXM,  --申请医生姓名
  PYRQ00,  --排样日期
  decode(PYRQ00,null,null,substr(PYRQ00,1,4)||'.'||substr(PYRQ00,5,2)||'.'||substr(PYRQ00,7,2)) PY0000,   
  PYSJ00,  --排样时间 
  BBSDRQ||YBH000 WYYBH0   ,    
  JGZT00,  --结果状态          
  decode(JGZT00,'0','已申请','1','已采集','2','已签收','3','已退回','4','已排样','5','已处理','6','已审核','7','已删除','8','已打印','9','已取单','未知') JGZTMC,
  THYY00,  --退回原因
  CXSHYY,  --撤销审核原因
  CBGRQ0,  --出报告日期
  decode(CBGRQ0,null,null,substr(CBGRQ0,1,4)||'.'||substr(CBGRQ0,5,2)||'.'||substr(CBGRQ0,7,2)||' '||CBGSJ0) BG0000,    
  CBGSJ0,  --出报告时间  
  BGR000,  --报告人(报告签发者或检验操作者)
  nvl(BGRXM0,(select ZWXM00 from BM_YGBM00 where YGBH00=A.BGR000)) BGRXM0,  --报告人姓名   
  BGJYR0,  --报告人结果校对者
  nvl(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,
  SFBZ00,  --计价标志(0-未计价 1-已计价，收费程序使用)           
  decode(SFBZ00,'0','未计价','1','已计价','2',decode(JGZT00,'7','已退费','已收费'),'3',decode(JGZT00,'7','已冲销','已执行'),'6','已作废','未知') SFBZMC,  
  BGDYBZ,  --报告打印标志 0-未打印 1-已打印
  TXM000,  --条形码 
  decode(TXM000,'','0000000000',TXM000) TXM001,  --报告条形码
  substr(TXM000,1,2) TXMQZ0,  --条形码前缀
  (select TXMYSZ from JY_TXMLB0 where TXMQZ0=substr(TXM000,1,2)) TXMYSZ,  --条形码颜色值
  YBLX00,
  JYDLY0,  --检验单来源 (0 :医生开具, 1:检验科开... )(目的:检验单,是否允许修改)
  DYBZ00,
  YBHBBZ,  --样本合并标志'0'表示此次的检验结果,不参与合并,'1':表示可以参与合并
  MZKID0, 
  YQDH00,
  (select TYDWMC from BM_BRXXB0 where BRID00=A.BRID00) TYDWMC,
  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,
  LRY000,       --录入医生
  (select ZWXM00 from BM_YGBM00 where YGBH00=A.LRY000) LRYXM0, --录入医生姓名 
  BGCKR0,
  (select ZWXM00 from BM_YGBM00 where YGBH00=A.BGCKR0) CKRXM0, --
  BGCKSJ,
  SGCJQZ,
  (select TMSFWK from BM_ZLZD00 where ZLXMID=A.ZLXMID) SFTMWK, --采集时条码是否为空    
  KWH000,
  CJDYRQ,
  CJBZ00,
  BBBZ00,  --标本备注  --jvs
  --(select ZJE000 from YJ_YW0000 where YJDJH0=A.YJDJH0) SFJE00,
  SF_JY_GET_ZJE000(A.YJDJH0,A.ZLXMID) SFJE00,
  QDXH00,
  (select SJ0000 from JY_JYYQZD where YQDH00 = A.YQDH00) SJ0000, --试剂
  TDJTTM,
  SFJM00,
  SFYB00,
  CZY000,  --操作员
  nvl(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 ,  --取单前缀
  SFKY00,    --是否科研
  (select LCPY00 from JY_XJJGPY where YBH000=A.YBH000 and PYRQ00=A.PYRQ00) LCPY00,  --临床评语,细菌 
  TMSFSF,
  nvl(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(BBCYRQ,BBCYSJ,BBSDRQ,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(YBH000,PYRQ00) YQMC00,
  SF_JY_GETXJALL(A.PYRQ00,A.YBH000) XJALL0,
  decode(A.MZZYBZ,'0',nvl((select decode(SFCZ00,'0','初诊','复诊') from SF_BRXXB0 where GHID00=A.ZYGHID) ,''),'') CFZBZ0,
  SFLSTD,
  nvl(JYBGRS,ZZBGRS) DYRQSJ,
  nvl((select ZWXM00 from BM_YGBM00 where YGBH00=A.JYBGDY),ZZBGDY) BGDYR0,
  SF_JY_UNION_ZDMC00(ZYGHID) SYZDMC ,      --病人所有诊断
  decode(JGZT00,'8','补打','') SFBD00,   --是否补打
  QSBZ00 ,          --签收备注
  JYJS00 ,          --建议解释
  decode(nvl(DYCS00,0),0,'','补打') BDXX00,
  SF_JY_GETJYJS(PYRQ00,YBH000) ZJJYJS,
  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细菌
from JY_YW0000 A,BM_ZLZD00 C
  where a.zlxmid=c.zlxmid
  and c.lbbh00='4';