CREATE OR REPLACE VIEW VW_YS_BRZYSQ 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,
    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.YSYJJ0,    --预算预交金
   (SELECT SUM(JFJE00) FROM ZY_BRJFB0 WHERE JFLBID='2' AND BRID00=A.BRID00 ) YJJE00,--预交金额
TO_CHAR(SYSDATE, 'YYYY' ) XTN000,--系统年
TO_CHAR(SYSDATE, 'MM' ) XTY000,--系统月
TO_CHAR(SYSDATE, 'DD' ) XTR000,--系统日
SUBSTR(A.SQRQ00,1,4) SQRQN0,--申请日期年
SUBSTR(A.SQRQ00,5,2) SQRQY0,--申请日期月
SUBSTR(A.SQRQ00,7,2) SQRQR0,--申请日期日
B.BRXZSF,--病人现住省份
DECODE(A.RYBQ00,'急','√','') RYBQJ0,--入院病情急
DECODE(A.RYBQ00,'危','√','') RYBQW0,--入院病情危
DECODE(A.RYBQ00,'一般','√','') RYBQYB,--入院病情一般
DECODE(B.FBBH00,'1','√','') FBXXZF,--费别信息自费
DECODE(B.FBBH00,'3','√','') FBXXYB --费别信息医保

FROM YS_BRZYSQ A,BM_BRXXB0 B,IC_YBBRLB C,BM_BMBM00 D
  WHERE A.BRID00 = B.BRID00
    AND B.FBBH00 = C.FBBH00
    AND B.YBLB00 = C.YBLB00
    AND A.KSH000 = D.BMBH00(+);