create or replace view vw_jy_txmcjd as
select distinct B.ZLXMID,B.MZZYBZ,B.ZYGHID,trim(B.CH0000) CH0000,B.YEXM00,B.SFTJ00,
         --decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',nvl(B.YEXM00,B.BRXM00),B.BRXM00)BRXM00,
         decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',
         nvl(B.YEXM00,(select brxm00 from bm_brxxb0 where brid00 = b.brid00))
         ,(select brxm00 from bm_brxxb0 where brid00 = b.brid00))BRXM00,  ----自定义取bm_brxxb0.brxm00 -wuwq 20210901
         
         decode(B.XB0000,'M','男','F','女','') XB0000,trim(B.ZYHGHH) ZYHGHH,B.YJDJH0,
         decode(B.YXBZ00,'Y','急诊','') JZBZ00,B.BBCYRQ,B.BBCYSJ,decode(B.TXM000,null,'0',trim(B.TXM000)) TXM000,B.SQKS00 KDKS00,B.SQBQ00 KDBQ00,B.SQRQ00 KDRQ00,
         B.CSRQ00,SF_JY_CSRQTONL(B.CSRQ00,B.SQRQ00) NL0000,B.SFBZ00,
         decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_JYJBSFXMZY'),'N'),'Y',decode(C.TXMMC0,'送检','送检',''),C.TXMMC0) TXMMC0,
         (select BBMC00 from BM_BBZD00 where BBID00=B.BBID00) BBMC00,
         substr(B.BBCYRQ,1,4)||'-'||substr(B.BBCYRQ,5,2)||'-'||substr(B.BBCYRQ,7,2)||' '||B.BBCYSJ CYRQSJ,
         --SF_JY_UNION_XMMC00(TXM000) XMMC00,
         SF_JY_UNION_XMMC00(B.TXM000)||decode(instr(SF_JY_UNION_XMMC00(B.TXM000),'|'),'0',decode(B.CJBZ00,'\',null,'0',null,null,null,'('||B.CJBZ00||')'),null) XMMC00,
        -- SF_JY_UNION_XMMC00_XXMS00(B.TXM000) XMMSMC,
         --DECODE(B.CJBZ00,'\',NULL,'0',NULL,NULL,NULL,B.CJBZ00) XMMSMC,
         (select yj.bz0000 from YJ_YW0000 y ,bq_yjyz00 yj where y.yzcfid = yj.yzmxid and y.yjdjh0 = b.yjdjh0 and rownum=1) XMMSMC,
         C.YBTJ00,C.YBSM00,B.BBDYBZ,B.JGZT00,
         decode(B.MZZYBZ,'0','门诊','住院') BRLB00,
         (select BMMC00 from BM_BMBM00 where BMBH00=B.SQKS00 and B.MZZYBZ='0'
          union
          select BMMC00 from BM_BMBM00 where BMBH00=B.SQBQ00 and B.MZZYBZ='1') BMMC00,
         B.CJKSBH,
         B.SQYS00,
         decode((select trim(VALUE0) from XT_XTCS00 where NAME00 ='JY_BQDYTMSFGXCYSJ' and rownum=1),'N',B.TMDYRQ,B.BBCYRQ) TMDYRQ,
         decode((select trim(VALUE0) from XT_XTCS00 where NAME00 ='JY_BQDYTMSFGXCYSJ' and rownum=1),'N',B.TMDYSJ,B.BBCYSJ) TMDYSJ,
         substr(B.TMDYRQ,1,4)||'-'||substr(B.TMDYRQ,5,2)||'-'||substr(B.TMDYRQ,7,2)||' '||B.TMDYSJ  DYRQSJ,
         (select ZWXM00 from BM_YGBM00 where YGBH00=B.SQYS00) SQYSXM,    --申请医生姓名
         B.SQRQ00,
         B.SQSJ00,
         B.BRID00,
         B.BBID00,
         D.YZCFID,
         D.YJKSBH
  from JY_YW0000 B,VM_JY_TXMDY0 C,YJ_YW0000 D
  where B.JGZT00 in ('0','1','3')
    and B.SFBZ00 in ('0','1','2','3')
    and B.MZZYBZ='1'
    and B.ZLXMID=C.ZLXMID
    and B.YJDJH0=D.YJDJH0
    and nvl(D.HBZYXM,'0')<>'2'
    and ((substr(nvl(B.TXM000,'000000000000'),1,2)=C.TXMQZ0) or (substr(nvl(B.TXM000,'000000000000'),1,2)='00'))
  union
  select distinct B.ZLXMID,B.MZZYBZ,B.ZYGHID,trim(B.CH0000) CH0000,B.YEXM00,B.SFTJ00,
         --decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',nvl(B.YEXM00,B.BRXM00),B.BRXM00) BRXM00,
         decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',
         nvl(B.YEXM00,(select brxm00 from bm_brxxb0 where brid00 = b.brid00)),
         (select brxm00 from bm_brxxb0 where brid00 = b.brid00)) BRXM00,       ----自定义取bm_brxxb0.brxm00  wuwq 20210901
         decode(B.XB0000,'M','男','F','女','') XB0000,trim(B.ZYHGHH) ZYHGHH,B.YJDJH0,
         decode(B.YXBZ00,'Y','急诊','') JZBZ00,B.BBCYRQ,B.BBCYSJ,decode(B.TXM000,null,'0',trim(B.TXM000)) TXM000,B.SQKS00 KDKS00,B.SQBQ00 KDBQ00,B.SQRQ00 KDRQ00,
         B.CSRQ00,SF_JY_CSRQTONL(B.CSRQ00,B.SQRQ00) NL0000,B.SFBZ00,
         decode(nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_JYJBSFXMZY'),'N'),'Y',decode(C.TXMMC0,'送检','送检',''),C.TXMMC0) TXMMC0,
         (select BBMC00 from BM_BBZD00 where BBID00=B.BBID00) BBMC00,
         substr(B.BBCYRQ,1,4)||'-'||substr(B.BBCYRQ,5,2)||'-'||substr(B.BBCYRQ,7,2)||' '||B.BBCYSJ CYRQSJ,
         SF_JY_UNION_XMMC00(B.TXM000) XMMC00,
         --SF_JY_UNION_XMMC00_XXMS00(B.TXM000) XMMSMC,
         --DECODE(B.CJBZ00,'\',NULL,'0',NULL,NULL,NULL,B.CJBZ00) XMMSMC,
         (select yj.bz0000 from YJ_YW0000 y ,bq_yjyz00 yj where y.yzcfid = yj.yzmxid and y.yjdjh0 = b.yjdjh0 and rownum=1) XMMSMC,
         C.YBTJ00,C.YBSM00,B.BBDYBZ,B.JGZT00,
         decode(B.MZZYBZ,'0','门诊','住院') BRLB00,
         (select BMMC00 from BM_BMBM00 where BMBH00=B.SQKS00 and B.MZZYBZ='0'
          union
          select BMMC00 from BM_BMBM00 where BMBH00=B.SQBQ00 and B.MZZYBZ='1') BMMC00,
         B.CJKSBH,
         B.SQYS00,
         decode((select trim(VALUE0) from XT_XTCS00 where NAME00 ='JY_BQDYTMSFGXCYSJ' and rownum=1),'N',B.TMDYRQ,B.BBCYRQ) TMDYRQ,
         decode((select trim(VALUE0) from XT_XTCS00 where NAME00 ='JY_BQDYTMSFGXCYSJ' and rownum=1),'N',B.TMDYSJ,B.BBCYSJ) TMDYSJ,
         substr(B.TMDYRQ,1,4)||'-'||substr(B.TMDYRQ,5,2)||'-'||substr(B.TMDYRQ,7,2)||' '||B.TMDYSJ  DYRQSJ,
         (select ZWXM00 from BM_YGBM00 where YGBH00=B.SQYS00) SQYSXM,
         B.SQRQ00,
         B.SQSJ00,
         B.BRID00,
         B.BBID00,
         D.YZCFID,
         D.YJKSBH
  from JY_YW0000 B,VM_JY_TXMDY0 C,YJ_YW0000 D
  where B.JGZT00 in ('0','1','3')
    and ((--B.SFBZ00 in ('3')        
    B.SFBZ00 in ('3','2')          --wuwq 20210901
    or ((select count(*) from XT_XTCS00 where NAME00='JY_DYTXMYQSSFJXSF' and VALUE0='N')=1)
          or (substr(JJBZ00,1,4)='ggtj'))
         or ((select count(*) from XT_XTCS00 where NAME00='JY_QYWEBFWBRLX' and VALUE0 in('P','H','T'))=1))
    and B.MZZYBZ = '0'
    and B.ZLXMID=C.ZLXMID
    and B.YJDJH0=D.YJDJH0
    and nvl(D.HBZYXM,'0')<>'2'
    and ((substr(nvl(B.TXM000,'000000000000'),1,2)=C.TXMQZ0) or (substr(nvl(B.TXM000,'000000000000'),1,2)='00'))
;