create or replace view vw_mz_brzysq as
select
    a.ZYSQID ,
    a.BRID00 ,
    (select ZYH000 from ZY_BRXXB0 where a.ZYID00=ZYID00) ZYH000,
    b.BRXM00 ,
    nvl(b.PYSM00,substr(SF_XT_GETPYSM(b.BRXM00),1,6)) as PYSM00 ,
    nvl(b.WBSM00,substr(SF_XT_GETWBSM(b.BRXM00),1,6)) as 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,100) 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,
    a.JSBZ00 ,
    a.JSRQ00 ,
    a.BQH000 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.BQH000) as BQMC00 ,
    (select YYID00 from BM_BMBM00 where BMBH00 = a.BQH000) as BQYYID,
    a.KSH000 ,
    (select BMMC00 from BM_BMBM00 where BMBH00 = a.KSH000) as KSMC00 ,
    (select KSWZ00 from BM_KSFB00 where KSBH00=a.BQH000) as KSWZ00,
    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,'') as BRLXDH,   --病人联系电话
    b.BRZJBH,  --身份证号
    b.TYDWMC,  --单位名称
    b.BRLXDZ,  --病人联系地址
    substr(SF_XT_CSRQTONL(b.BRCSRQ,''),1,20) as NL0000,  --病人年龄
    b.BRHYZK,   --婚姻状况
    decode(a.TZYXRQ,null,null,substrb(a.TZYXRQ,1,4)||'年'||substrb(a.TZYXRQ,5,2)||'月'||substrb(a.TZYXRQ,7,2)||'日') as TZRQNY,  --预约有效日期
    decode(to_char(to_date(a.TZYXRQ,'yyyymmdd'),'D'),1,'星期日',2,'星期一',3,'星期二',4,'星期三',5,'星期四',6,'星期五',7,'星期六') as TZYXXQ,  --预约星期几
    a.SFYXGC,   --是否允许挂床
    nvl(a.ZRZYBZ,'0') as ZRZYBZ,   --择日住院标志
    decode(nvl(a.ZRZYBZ,'0'),'0','否','1','是') as SFZRZY,  --是否择日住院: ''0''否,''1''是
    decode(nvl(a.ZYID00,0),0,'N',decode(nvl(a.ZRZYBZ,'0'),'0','N',(select nvl(max('Y'),'N') from zy_brxxb0 where ZYID00=a.ZYID00 and BRZT00 like '1%' and to_char(sysdate-3,'YYYYMMDD')>=RYRQ00))) as SFHSJS, --字体是否使用红色颜色警示
    nvl(a.RJSSBZ,'0') RJSSBZ,  --日间手术标志 1:是，0否
    a.YSYJJ0,   --预收预交金
    a.HSJCQK,   --核酸检查情况： 1.急危重，未查核酸  2.急危重，核酸已查待报
    decode(a.HSJCQK,'1','急危重，未查核酸','2','急危重，核酸已查待报') as HSJCSM, --核酸检查情说明
    b.brztqk,    --病人职退情况
    b.brcsrq,    --出生日期
    b.BRLXXM,    --联系人姓名
    b.BRLXGX,    --联系人电话
    b.BRJTDZ,    --病人家庭地址
    substr(a.sqrq00,1,4)||'年'||substr(a.sqrq00,6,1)||'月'||substr(a.sqrq00,7,2)||'日' rq0000
from YS_BRZYSQ a,BM_BRXXB0 b,IC_YBBRLB c
  where a.BRID00 = b.BRID00
    and b.YBLB00 = c.YBLB00
    and b.FBBH00 = c.FBBH00;