CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_BRZYSQ
 (
 ZYSQID, BRID00, BRXM00, PYSM00, WBSM00, BRXB00, FBBH00, FBMC00, FBXX00, 
SQKS00, SQKSMC, YSGZH0, SZYSXM, SQRQ00, SQSJ00, SQRQSJ, SQYXBZ, JSBZ00, 
JSRQ00, BQH000, BQMC00, KSH000, KSMC00, SQYYID, CH0000, ZDMC00, ZDM000, 
WDWID0, ZYMD00, RYBQ00, RYFS00, RYLX00, ZYMDDM, RYBQDM, RYFSDM, ZYID00, 
ZT0000, ZTMC00, YYZYRQ, YYZYSJ, YYRQSJ, YYH000, YYCZRQ, YYCZSJ, CZRQSJ, 
YYCZY0, YYCZYM, TZRQ00, TZSJ00, TZRQSJ, TZCZY0, TZCZYM, TZYXRQ, TZYXSJ, 
YXRQSJ, SFDJY0, SFDJYM, SFDJRQ, SFDJSJ, DJRQSJ, BQSZY0, BQSZYM, BQSZRQ, 
BQSZSJ, SZRQSJ, ZFCZY0, ZFCZYM, ZFRQ00, ZFSJ00, ZFRQSJ, YYSSRQ, BQSZXZ, 
BQSZMC, ZXDJBZ, ZXDJMC, YYDJBZ, QXYY00, SFCGJC, SFTSJC, YSZID0, YSZMC0, 
SQYSDH, ZGYS00, ZGYSXM, ZGYSDH, KMH000, BRBLH0, BRLXDH, BRZJBH, TYDWMC, 
BRLXDZ, NL0000, BRHYZK, TZRQNY, TZYXXQ, SFYXGC, ZRZYBZ, SFZRZY, RJSSBZ, 
HSJCQK, YYBMDZ, HSJCSM

 )
 AS 
select 
    a.ZYSQID ,
    a.BRID00 ,
    b.BRXM00 ,
    nvl(b.PYSM00,substr(SF_XT_GETPYSM(b.BRXM00),1,6)) PYSM00 ,
    nvl(b.WBSM00,substr(SF_XT_GETWBSM(b.BRXM00),1,6)) WBSM00 ,
    b.BRXB00 ,
    b.FBBH00 ,
    (select FBMC00 from BM_BRFBB0 where FBBH00 = b.FBBH00) as FBMC00 ,
    substr(SF_ZY_GETFBXX00(b.FBBH00,c.YBZXLB,b.YBLB00,b.YBBRLB),1,50) as FBXX00 ,     --费别信息
    a.SQKS00 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.SQKS00) as SQKSMC ,
    a.YSGZH0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.YSGZH0) as SZYSXM ,
    a.SQRQ00 ,
    a.SQSJ00 ,
    a.SQRQ00||' '||a.SQSJ00 as SQRQSJ,
    decode(sign(to_date(a.SQRQ00,'YYYYMMDD') + SF_XT_STRTONUMVALUE(SF_XT_GETXTCSVALUE('ZY_MZYSZYSQRQYXTS','7'),7) - to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD')),-1,0,1) as SQYXBZ, --申请有效标识 1有效(未过期) 0无效(过期)        
    a.JSBZ00 ,
    a.JSRQ00 ,
    a.BQH000 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.BQH000) as BQMC00 ,
    a.KSH000 ,
    d.BMMC00 as KSMC00 ,
    d.YYID00 as SQYYID ,  --申请科室所属医院ID    
    a.CH0000 ,
    a.ZDMC00 ,
    a.ZDM000 ,
    a.WDWID0 ,
    a.ZYMD00 ,
    a.RYBQ00 ,
    a.RYFS00 ,
    a.RYLX00 ,
    a.ZYMDDM ,
    a.RYBQDM ,
    a.RYFSDM ,
    a.ZYID00 ,
    a.ZT0000 ,
    decode(a.ZT0000,'0','门诊申请入院','1','门诊申请通过(无需预约)','2','预约未通知','3','预约已通知',
           '7','作废',decode((select substrb(BRZT00,1,1) from ZY_BRXXB0 where ZYID00=a.ZYID00),'0','预约','1','收费处已登记入院','2','在院','3','出院登记','4','已出院','5','出院欠费','9','取消入院','未定义')) as ZTMC00 ,  --申请状态    
    a.YYZYRQ ,
    a.YYZYSJ ,
    a.YYZYRQ||' '||a.YYZYSJ as YYRQSJ ,   --预约住院日期时间
    a.YYH000 ,
    a.YYCZRQ ,
    a.YYCZSJ ,
    a.YYCZRQ||' '||a.YYCZSJ as CZRQSJ ,   --预约操作日期时间
    a.YYCZY0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.YYCZY0) as YYCZYM ,    
    a.TZRQ00 ,
    a.TZSJ00 ,
    a.TZRQ00||' '||a.TZSJ00 as TZRQSJ ,  --通知日期时间
    a.TZCZY0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.TZCZY0) as TZCZYM ,
    a.TZYXRQ ,
    a.TZYXSJ ,
    a.TZYXRQ||' '||a.TZYXSJ as YXRQSJ ,  --通知有效日期时间
    a.SFDJY0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SFDJY0) as SFDJYM ,
    a.SFDJRQ ,
    a.SFDJSJ ,
    a.SFDJRQ||' '||a.SFDJSJ as DJRQSJ ,  --收费等级日期时间
    a.BQSZY0 ,
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.BQSZY0) as BQSZYM ,
    a.BQSZRQ ,
    a.BQSZSJ ,
    a.BQSZRQ||' '||a.BQSZSJ as SZRQSJ ,  --病区收治日期时间
    a.ZFCZY0 ,
    (select ZWXM00||'/'||XKH000 from BM_YGBM00 where YGBH00 = a.ZFCZY0) as ZFCZYM ,
    a.ZFRQ00 ,
    a.ZFSJ00 ,
    a.ZFRQ00||' '||a.ZFSJ00 as ZFRQSJ,    --作废日期时间
    a.YYSSRQ ,  --预约手术日期
    a.BQSZXZ ,  --病区收治选择
    decode(a.BQSZXZ,'0','本病区','1','仅限本病区所在大楼','2','无收治限制') as BQSZMC, --病区收治选择名称
    a.ZXDJBZ ,  --入院中心登记标志
    decode(a.ZXDJBZ,'1','检查','2','回家','3','住院通知') as ZXDJMC,  --入院中心登记名称
    a.YYDJBZ ,  --预约登记标志
    a.QXYY00 ,  --预约取消原因  
    decode(a.ZXSQCG,'0','否','1','是') as SFCGJC,  --入院中心申请常规检查: ''0''否,''1''是
    decode(a.ZXTSJC,'0','否','1','是') as SFTSJC,  --入院中心特殊检查: ''0''否,''1''是
    a.YSZID0 ,  --医疗组ID
    (select YSZMC0 from ZS_YSZ000 where YSZID0=a.YSZID0) as YSZMC0,  --医疗组名称
    (select DH0000 from BM_YGBM00 where YGBH00 = a.YSGZH0) as SQYSDH , --申请医生电话
    a.ZGYS00 ,  --主管医生
    (select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGYS00) as ZGYSXM , --主管医生姓名
    (select DH0000 from BM_YGBM00 where YGBH00 = a.ZGYS00) as ZGYSDH , --主管医生电话
    (select KMH000 from ic_yhxx00 where brid00=b.brid00 and zt0000='1' and rownum=1) as KMH000,  --卡号
    b.BRBLH0,  --病历号
    b.BRLXDH||decode(b.BRLXDH,null,null,' ')||replace(b.BRDH00,b.BRLXDH,'')  BRLXDH,   --病人联系电话
    b.BRZJBH,  --身份证号
    b.TYDWMC,  --单位名称
    b.BRLXDZ,  --病人联系地址
    substr(SF_XT_CSRQTONL(b.BRCSRQ,''),1,20) NL0000,  --病人年龄
    b.BRHYZK,   --婚姻状况
    decode(a.TZYXRQ,null,null,substrb(a.TZYXRQ,1,4)||'年'||substrb(a.TZYXRQ,5,2)||'月'||substrb(a.TZYXRQ,7,2)||'日') TZRQNY,  --预约有效日期
    decode(to_char(to_date(a.TZYXRQ,'yyyymmdd'),'D'),1,'星期日',2,'星期一',3,'星期二',4,'星期三',5,'星期四',6,'星期五',7,'星期六') TZYXXQ,  --预约星期几
    a.SFYXGC,
    nvl(a.ZRZYBZ,'0') ZRZYBZ,   --择日住院标志
    decode(nvl(a.ZRZYBZ,'0'),'0','否','1','是') as SFZRZY,  --是否择日住院: ''0''否,''1''是
    nvl(a.RJSSBZ,'0') RJSSBZ,  --日间手术标志 1:是，0否
    A.HSJCQK, --核酸检查情况： 1.急危重，未查核酸  2.急危重，核酸已查待报
    d.yybmdz,  --科室地址
    decode(A.HSJCQK,'1','急危重，未查核酸','2','急危重，核酸已查待报') HSJCSM --核酸检查情况说明
from YS_BRZYSQ a,BM_BRXXB0 b,IC_YBBRLB c,BM_BMBM00 d 
  where a.BRID00 = b.BRID00 
    and b.YBLB00 = c.YBLB00
    and a.KSH000 = d.BMBH00(+)
/