create or replace view vw_xk_sxjlxx
(pxrqm0, shrqm0, fxrqm0, sqdh00, brxm00, brxb00, zdxx00, rhxx00, brblh0, brnl00, zyh000, ch0000, lczdmc, sxxz00, qxrxm0, fjrxm0, pxrxm0, fxrxm0, sqysxm, jyrxm0, jybgrq, jyrq00, qxrq00, qxsj00, fjxxjg, jcpxjg, ktsxjg, qtjcjg, gxzxx0, bqmc00, ksmc00, npjcpx, jcpxff, ckxx00, ndjcpx, zyhblh, fxdh00, sxl000, pxrxkh, fxrxkh, zyid00, mzzybz, pxbz00, yxfxyy, qtsyjg, cxsj00, sxsl00, sxdw00, sxcf00, pxfhxm, brid00)
as
select
    (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,   --发血人签名  wxx.2017.09.02
    A.SQDH00,                                                      --申请单号
    nvl(A.YSBRXM,B.XM0000) as BRXM00,                              --病人姓名
    B.XB0000 as BRXB00,                                            --病人性别
    B.ZDXX00,                                                      --正定血型
    B.RHXX00,                                                      --RH血型
    --decode(A.MZZYBZ,'住院',A.ZYH000,(select BRBLH0 from BM_BRXXB0 where BRID00=A.BRID00)) as BRBLH0, --病人病历号 (select BRBLH0 from BM_BRXXB0 where BRID00=A.BRID00)
    decode(A.MZZYBZ,'住院',A.ZYH000,A.YZMXID) as BRBLH0, --病人病历号 (select BRBLH0 from BM_BRXXB0 where BRID00=A.BRID00)
    --substr(SF_XT_CSRQTONL(B.CSRQ00,A.SQRQ00),1,8) as BRNL00,        --病人年龄
    nvl((select substr(SF_XT_CSRQTONL(C.BRCSRQ,A.SQRQ00),1,8) from BM_BRXXB0 C where C.BRID00=B.BRID00 and rownum=1),substr(SF_XT_CSRQTONL(B.CSRQ00,A.SQRQ00),1,8)) as BRNL00,        --病人年龄
    A.ZYH000,                                                          --住院号
    A.CH0000,                                                          --床号
    A.LCZDMC,                                                          --临床诊断
    A.SXSZ00 as SXXZ00,                                                --输血性质
    A.QXRXM0,                                                          --取血人姓名
    B.FJRXM0,                                                          --复检人姓名
    (select ZWXM00 from BM_YGBM00 where YGBH00=A.PXYGBH) as FJRXM0,    --配血人姓名
    (select ZWXM00 from BM_YGBM00 where YGBH00=A.FXRBH0) as FXRXM0,    --发血人姓名
    (select ZWXM00 from BM_YGBM00 C where C.YGBH00(+)=A.SQYSBH) SQYSXM,--申请医生姓名
    (select ZWXM00 from BM_YGBM00  where YGBH00(+)=B.JYYGBH) JYRXM0,   --检验人姓名
    (substr(B.JYBGRQ,1,4)||'-'||substr(B.JYBGRQ,5,2)||'-'||substr(B.JYBGRQ,7,2)||' '||substr(B.JYBGSJ,1,5)) JYBGRQ,    ---检验报告日期时间
    (substr(B.JYBGRQ,1,4)||'-'||substr(B.JYBGRQ,5,2)||'-'||substr(B.JYBGRQ,7,2))   JYRQ00,     ---检验日期
    A.QXRQ00,                                                       --发血日期
    A.QXSJ00,                                                       --发血时间
    --B.ZDXX00||' RH:'||B.RHXX00   as   FJXXJG,                     --复检血型结果
    --B.ZDXX00||'型 Rh(D):'||B.RHXX00   as   FJXXJG,                --复检血型结果
    nvl((select trim(FJXDXX)||'型 Rh(D):'||trim(FJXDRH) from XK_JCPXBG where SQDH00=A.SQDH00 and rownum=1
        and nvl(trim(FJXDXX),' ')<>' ' and nvl(trim(FJXDRH),' ')<>' '),B.ZDXX00||'型 Rh(D):'||B.RHXX00)   as   FJXXJG,                       --复检血型结果
    SP_XK_JCPXJG0(SQDH00) as   JCPXJG,                              --交叉配血结果
    nvl(B.TYKTSC,'阴性') as  KTSXJG,                                --抗体筛选结果
    ' ' as  QTJCJG,                                                 --其它检查结果
    ' ' as  GXZXX0,                                                 --供血者血型
    nvl(A.BQMC00,(select m.BMMC00 from BM_BMBM00 m,BM_BQKS00 n where m.BMBH00=n.BQH000 and n.KSH000=A.SQKS00
                    and exists (select 1 from BQ_BRLDXX where ZYID00=A.ZYID00 and BQH000=m.BMBH00) and rownum=1) ), --病区名称
    --20200421 wxx 外院用血取院外单位名称
    decode (a.sfywyx,'1',a.wydwmc,a.ksmc00) KSMC00,                                                         --科室名称
    --KSMC00,                                                         --科室名称
    SP_XK_JCPXJG0_NP(SQDH00),
    (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),
     (select XX0000 from XK_XYCK00 where SQDH00=A.SQDH00 and rownum=1) CKXX00,
    SP_XK_JCPXJG0_ND(SQDH00),
    decode(A.MZZYBZ,
          '住院','住院号:'||A.ZYH000,
          '门诊','病历号:'||(select BRBLH0 from BM_BRXXB0 where BRID00=B.BRID00 and rownum=1),' '),
    FXDH00,
    A.SXSL00||A.SXDW00,
    (select XKH000 from BM_YGBM00 where YGBH00=A.PXYGBH) as PXRXKH, --配血人胸卡号
    (select XKH000 from BM_YGBM00 where YGBH00=A.FXRBH0) as FXRXKH,   --发血人胸卡号
    A.ZYID00,
    A.MZZYBZ,
    A.PXBZ00,
    A.YXFXYY,
     nvl(b.QTSYJG,(select QTSYJG from XK_JCPXBG where SQDH00=a.SQDH00 and rownum=1)) QTSYJG,  --其它实验结果
    (select CXSJ00 from XK_XYRK00 RK, XK_XYCK00 CK where RK.XDBH00=CK.XDBH00 and CK.SQDH00=A.SQDH00 and rownum=1) as CXSJ00, --采血时间
    A.SXSL00,
    A.SXDW00,
    A.SXCF00,
    A.PXFHXM,
    A.BRID00
  from XK_SXSQ00 A,XK_BRXX00 B
  where A.BRID00=B.BRID00
;