CREATE OR REPLACE VIEW VW_XK_JCPXJL AS
select
       a.bqmc00,c.zyh000,decode(a.ch0000, '0', ' ', a.ch0000)  ch0001, decode (c.rhxx00,'阳性','阳性（+）','阴性','阴性（-）','待报') brfjrh,
       SP_XK_JCPXJG0(a.SQDH00) as jcpxjg,
       a.pxbz00,a.yxfxyy,a.mzzybz,d.xdbh00,d.xypzmc,d.xx0000 xdxx00,decode (d.RHXX00,'阳性','阳性（+）','阴性（-）') XDRHXX,
       d.ckdw00,d.cksl00,b.fjxdxx,b.fjxdrh,a.sxrxm0,   
       (substr(A.PXBGRQ,1,4)||'-'||substr(A.PXBGRQ,5,2)||'-'||substr(A.PXBGRQ,7,2)||' '||substr(A.PXBGSJ,1,5)) PXRQSJ,      --配血日期时间
       (substr(a.QXRQ00,1,4)||'-'||substr(a.QXRQ00,5,2)||'-'||substr(a.QXRQ00,7,2)||' '||substr(a.QXSJ00,1,5)) qxrqsj,      --取血日期时间
       decode ((substr(a.SXRQ00,1,4)||'-'||substr(a.SXRQ00,5,2)||'-'||substr(a.SXRQ00,7,2)||' '||substr(a.SXSJ00,1,5)),'-- ','',(substr(a.SXRQ00,1,4)||'-'||substr(a.SXRQ00,5,2)||'-'||substr(a.SXRQ00,7,2)||' '||substr(a.SXSJ00,1,5))) sxrqsj,
        (SELECT DZQM00 FROM BM_ZJ0000 WHERE YGBH00=a.pxygbh) PXRQM0,   ---配血人签名
        (SELECT DZQM00 FROM BM_ZJ0000 WHERE YGBH00=A.Pxqrr0) SHRQM0,   ---审核人签名
        (SELECT DZQM00 FROM BM_ZJ0000 WHERE YGBH00=a.fxrbh0) FXRQM0,   --- 发血人签名
    --------------增加上方字段----------------------20170901.wxx
    a.sqdh00, c.xm0000, c.xb0000, a.sxcf00, sf_xt_csrqtonl (c.csrq00,null) nl0000,
    c.zdxx00, mrn000, a.ksmc00, a.ch0000, a.sxsz00, shysxm, pxfhxm, qxrxm0,qxrq00, qxsj00,FJRXM0,
    (select zwxm00 from bm_ygbm00 where ygbh00 = a.fxrbh0) fxrxm0,
    (select zwxm00 from bm_ygbm00 where ygbh00 = a.pxygbh) pxrxm0,
    nvl(c.TYKTSC,'阴性') as  KTSXJG,
    (select DECODE(FFID00,NULL,'',(select PXFFMC from VW_XK_PXFFBM where PXFFID=E.FFID00)||';')||
       DECODE(FFID01,NULL,'',(select PXFFMC from VW_XK_PXFFBM where PXFFID=E.FFID01)||';')||
       DECODE(FFID02,NULL,'',(select PXFFMC from VW_XK_PXFFBM where PXFFID=E.FFID02)||';') from XK_JCPXBG E where SQDH00=A.SQDH00 AND FFID00 IS NOT NULL AND ROWNUM=1) jcpxff,

    DECODE(A.MZZYBZ,'住院','住院号:'||C.zyh000,'门诊','病历号:'||(select brblh0 from bm_brxxb0 where brid00=c.brid00 and rownum=1),' ') ZYHBLH
  from XK_SXSQ00 A, XK_JCPXBG B, XK_BRXX00 C,XK_XYCK00 d ----增加关联d表
  where
  A.BRID00 = C.BRID00
  and A.SQDH00 = B.SQDH00
  and a.sqdh00 = d.sqdh00
  and b.xdbh00 = d.xdbh00
  and d.cxbz00 = 'Z';