CREATE OR REPLACE VIEW VW_XK_JCPXJL AS
select
       a.brid00,--20200421 WXX 按要求增加病人ID
       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,   --- 发血人签名
  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,
  (select TYKTSC from xk_brxx00 where brid00 = a.brid00 and TYKTSC is not null and rownum=1 ) KTSXJG,
  DECODE(FFID00,NULL,'',(select MC0000 from BM_TYZD00 where ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID00)||';')||
  DECODE(FFID01,NULL,'',(select MC0000 from BM_TYZD00 where ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID01)||';')||
  DECODE(FFID02,NULL,'',(select MC0000 from BM_TYZD00 where ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID02)) 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
where A.BRID00 = C.BRID00 and A.SQDH00 = B.SQDH00 and a.sqdh00 = d.sqdh00
  and b.xdbh00 = d.xdbh00
  and d.cxbz00 = 'Z'
;