CREATE OR REPLACE VIEW SD_HOSPITAL.VW_JY_YW0000
 (
 JYYWID, YJDJH0, YJFZH0, ZLXMID, ZLXMJC, XXMS00, ZYZBID, ZYGHID, ZYHGHH, 
MZZYBZ, SFTJ00, BRLX00, SZKS00, BRID00, ICKH00, BRBLH0, FBBH00, FBMC00, 
BRXM00, YEXM00, XB0000, XBMC00, CSRQ00, NL0000, NLDW00, NLDWMC, CH0000, 
YBH000, YXBZ00, ZDID00, ZDMC00, SLJDDM, SLJDMC, BBID00, BBMC00, BBSM00, 
BBCJL0, BBCJR0, CJRXM0, BBSDR0, SDRXM0, BBSDRQ, BBSDSJ, SDRQSJ, BBCYRQ, 
CYRQ00, BBCYSJ, CYRQSJ, SQRQ00, SQ0000, SQSJ00, SQKS00, SQKSMC, SQBQ00, 
SQBQMC, SQYS00, SQYSXM, PYRQ00, PY0000, PYSJ00, WYYBH0, JGZT00, JGZTMC, 
THYY00, CXSHYY, CBGRQ0, BG0000, CBGSJ0, BGR000, BGRXM0, BGJYR0, JYRXM0, 
DZQM00, ZJE000, SFBZ00, SFBZMC, BGDYBZ, TXM000, TXM001, TXMQZ0, TXMYSZ, 
YBLX00, JYDLY0, DYBZ00, YBHBBZ, MZKID0, YQDH00, TYDWMC, BZDWMC, LRY000, 
LRYXM0, BGCKR0, CKRXM0, BGCKSJ, SGCJQZ, SFTMWK, KWH000, CJDYRQ, CJBZ00, 
BBBZ00, SFJE00, QDXH00, SJ0000, TDJTTM, SFJM00, SFYB00, CZY000, CZYXM0, 
YJKSBH, BRZT00, BGYSZ0, QZMC00, SFKY00, LCPY00, TMSFSF, DYCS00, BGRQM0, 
CZYQM0, FHZQM0, QSRQSJ, HGSDRQ, HGSDSJ, HGSCRQ, HGSCSJ, HGXM00, CJKSBH, 
YQMC00, XJALL0, CFZBZ0, SFLSTD, DYRQSJ, BGDYR0, SYZDMC, SFBD00, QSBZ00, 
JYJS00, BDXX00, ZJJYJS, BRDH00, ZZJFS0, SFXWFS, ZZBGRS, SFCTAT, JYQSJ0, 
JYZSJ0, JYHSJ0, SFJZ00, SJRQ00, SJSJ00, BGCKCS, DXSFFS, CJKSMC

 )
 AS 
select
    a.JYYWID,  --流水ID:SEQ_JY_YW0000_LSID00
    a.YJDJH0,  --医技单据号
    a.YJFZH0,  --
    a.ZLXMID,  --
    b.ZLXMJC,
    b.XXMS00,
    decode(b.zyzbid,'',(select zyzbid from jy_jyyqzd where yqdh00=substr(a.ybh000,1,2)),b.zyzbid) 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,
    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,18)  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,
    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, --报告查看超时
    A.DXSFFS,    --短信是否发送
    (select BMMC00 from BM_BMBM00 where BMBH00=A.CJKSBH and rownum=1) CJKSMC  --采集科室名称
from JY_YW0000 A,bm_zlzd00 B
where A.ZLXMID=B.ZLXMID(+)
/