CREATE OR REPLACE VIEW VW_JY_TXMCJD AS
select distinct B.ZLXMID,B.MZZYBZ,B.ZYGHID,trim(B.CH0000) CH0000,B.YEXM00,
decode(nvl((select trim(value0) from xt_xtcs00 where name00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',nvl(B.YEXM00,B.BRXM00),B.BRXM00)BRXM00,
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 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_XXMS00(TXM000) XMMSMC,C.YBTJ00,C.YBSM00,B.BBDYBZ,B.JGZT00,
DECODE(B.MZZYBZ,'0','门诊','住院') BRLB00,
(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=B.SQKS00 AND MZZYBZ = '0'
 UNION
 SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=B.SQBQ00 AND 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    --申请医生姓名
From JY_YW0000 B,VM_JY_TXMDY0 C
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 ((substr(nvl(txm000,'000000000000'),1,2)=c.txmqz0 ) or (substr(nvl(txm000,'000000000000'),1,2)='00' ))
UNION
select distinct B.ZLXMID,B.MZZYBZ,B.ZYGHID,TRIM(B.CH0000) CH0000,B.YEXM00,
decode(nvl((select trim(value0) from xt_xtcs00 where name00='JY_YEJYXMBBCJDSFXSYEXM'),'N'),'Y',nvl(B.YEXM00,B.BRXM00),B.BRXM00)BRXM00,
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 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_XXMS00(TXM000) XMMSMC,C.YBTJ00,C.YBSM00,B.BBDYBZ,B.JGZT00,
DECODE(B.MZZYBZ,'0','门诊','住院') BRLB00,
(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=B.SQKS00 AND MZZYBZ = '0'
 UNION
 SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=B.SQBQ00 AND 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
From JY_YW0000 B,VM_JY_TXMDY0 C
where B.JGZT00 IN ('0','1','3')
  AND ((B.SFBZ00 IN ('3') 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 ((substr(nvl(txm000,'000000000000'),1,2)=c.txmqz0 ) or (substr(nvl(txm000,'000000000000'),1,2)='00' ))
;