CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_MZCRDJ_ALL
 (
 DJID00, MZID00, GHH000, BRXM00, HEJZXM, BRZJBH, BRXB00, BRCSRQ, CSRQN0, 
CSRQY0, CSRQR0, TYDWMC, BRLXDH, BRLS00, DZSF00, DZDJS0, DZXQ00, DZXZJD, 
DZC000, DZMPH0, BRLXDZ, BRZY00, FBRQ00, FBRQ01, ZDRQ00, ZDRQ01, ZDSJ00, 
ZDSJ01, SWRQ00, BLFL01, BLFL02, TKYS00, TKYSXM, TKRQ00, TKRQ01, JBMC00, 
JCZCR0, BZ0000, CZY000, CZRQ00, CZSJ00, SFDJ00, JZKS00, DQKSMC, HY0000, 
WHCD00, CRLY00, NL0000, SFSW00, JZRQ00, SFQR00, QRR000, QRRXM0, SFYYE0, 
JZYSXM, JZYS00, BRNL00, CRB001, CRB002, CRB003, CRB004, CRB005, CRB006, 
CRB007, CRB008, CRB009, CRB010, CRB011, CRB012, CRB013, CRB014, CRB015, 
CRB016, CRB017, CRB018, CRB019, CRB020, CRB021, CRB022, CRB023, CRB024, 
CRB025, CRB026, CRB027, CRB028, CRB029, CRB030, CRB031, CRB032, CRB033, 
CRB034, CRB035, CRB036, CRB037, CRB038, CRB039, CRB040, CRB041, CRB042, 
CRB043, CRB044, CRB045, CRB046, CRB047, CRB048, CRB049, CRB050, CRB051, 
CRB052, CRB053, CRB054, CRB055, CRB056, CRB057, CRB058, CRB059, CRB060, 
CRB061, CRB062, CRB063, CRB064, SFDY01, SFDY02, SFDY03, SFDY04, SFDY05, 
SFDY06, SFDY07, SFDY08, SFDY09, SFDY10, SFDY11, SFDY12, SFDY13, SFDY14, 
SFDY15, SFDY16, SFDY17, SFDY18, SFDY19, SFDY20, SFDY21, SFDY22, SFDY23, 
SFDY24, SFDY25, SFDY26, SFDY27, SFDY28, SFDY29, SFDY30, SFDY31, SFDY32, 
SFDY33, SFDY34, SFDY35, SFDY36, SFDY37, SFDY38, SFDY39, SFDY40, SFDY41, 
SFDY42, SFDY43, SFDY44, SFDY45, SFDY46, SFDY47, SFDY48, SFDY49, SFDY50, 
SFDY51, SFDY52, SFDY53, SFDY54, SFDY55, SFDY56, SFDY57, SFDY58, SFDY59, 
SFDY60, SFDY61, SFDY62, SFDY63, SFDY64, SFZH00, SEX_M, SEX_F, CSRQ00, BRLS01, 
BRLS02, BRLS03, BRLS04, BRLS05, BRLS06, BRZY01, BRZY02, BRZY03, BRZY04, 
BRZY05, BRZY06, BRZY07, BRZY08, BRZY09, BRZY10, BRZY11, BRZY12, BRZY13, 
BRZY14, BRZY15, BRZY16, BRZY17, BRZY18, BRZY19, BRZY20, BLFL11, BLFL12, 
BLFL13, BLFL14, BLFL15, BLFL21, BLFL22, KPBH00, KPLX00, QTCRB0, DZJBMC, 
TKYY00, KPLX01, KPLX02, ZDXXID, TKSJ00, SFCRB0, GHZD00, FBMC00, BRNL0S, 
CRBLB0, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, 
CRB073, CRB074, CRB075, SFDY65, SFDY66, SFDY67, SFDY68, SFDY69, SFDY70, 
SFDY71, SFDY72, BRSZNL, SFDY73, SFDY74, SFDY75, CRB076, CRB077, CRB078, 
CRB079, CRB080, CRB081, CRB082, CRB083, SFDY76, SFDY77, SFDY78, SFDY79, 
SFDY80, SFDY81, SFDY82, SFDY83, XZZDID, XZZDMC, ZZDID0, ZZDMC0, FZZDID, 
FZZDMC, JZSJ00, BGDWMC, BGDWDH, ZDMC00, CRB087, SFDY87, CRB086, SFDY86, 
CRB088, SFDY88, CRB089, SFDY89, CRB090, SFDY90, CRB091, SFDY91, BRZY21, 
BRID00, CRB092, SFDY92, CRB093, SFDY93, BZNLXZ, CRB094, SFDY94, CRB095, 
SFDY95, CRB096, SFDY96, CRB097, SFDY97, CRB098, SFDY98, CRB109, SFDY109

 )
 AS 
select
    decode(B.CZRQ00, null, '', B.DJID00) DJID00,    --登记ID
    A.GHID00 MZID00,        --挂号ID
    A.GHH000,       --挂号号
    decode(B.CZRQ00, null, C.BRXM00, B.HZXM00) BRXM00,      --患者姓名
    B.HEJZXM,       --患儿家长姓名
    decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00) BRZJBH,      --身份证号
    decode(B.CZRQ00, null, C.BRXB00, B.BRXB00) BRXB00,      --性别
    decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ) BRCSRQ,      --病人出生日期
    substr(B.BRCSRQ,1,4) CSRQN0,        --病人出生日期年
    substr(B.BRCSRQ,5,2) CSRQY0,             --病人出生日期月
    substr(B.BRCSRQ,7,2) CSRQR0,        --病人出生日期日
    --to_date(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),'YYYYMMDD') CSRQ01,        --病人出生日期01
    decode(B.CZRQ00, null, C.TYDWMC, B.TYDWMC) TYDWMC,      --病人工作单位
    decode(B.CZRQ00, null, C.BRLXDH, B.BRLXDH) BRLXDH,      --病人联系电话
    B.BRLS00,       --病人隶属： --本县区  本市其它县区  本省其它地市  其他省  港澳台  外籍
    B.DZSF00,       --地址省份
    B.DZDJS0,       --地址地级市
    B.DZXQ00,       --地址县区
    B.DZXZJD,       --地址乡镇街道
    B.DZC000,               --地址村
    B.DZMPH0,       --地址门牌号
    decode(trim(nvl(B.BRLXDZ,'')),'', C.BRJTDZ, B.BRLXDZ) BRLXDZ,       --病人联系地址
    decode(B.CZRQ00, null, C.BRZY00, B.BRZY00) BRZY00,      --病人职业
    decode((select upper(substrb(nvl(max(VALUE0),'Y'),1,1)) from XT_XTCS00 where NAME00='YS_SFMRFBRQ'),'Y',nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),to_char(sysdate,'YYYYMMDD'))),nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),''))) FBRQ00,
    nvl(to_date(B.FBRQ00,'YYYYMMDD'),sysdate) FBRQ01, --发病日期01
    decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00) ZDRQ00,      --诊断日期
    to_date(decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00),'YYYYMMDD') ZDRQ01,      --诊断日期01
    decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00) ZDSJ00,      --诊断时间
    --to_date(decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00),'HH24:MI:SS') ZDSJ01,  --诊断时间01
    to_date(decode(B.CZRQ00, null, A.JZRQ00||A.JZSJ00, B.ZDRQ00||B.ZDSJ00),'YYYYMMDD HH24:MI:SS') ZDSJ01,
    decode(B.SWRQ00, null,'',substr(B.SWRQ00,1,8)) SWRQ00,      --死亡日期
    --nvl(B.SWRQ00,sysdate,to_date(B.SWRQ00,'YYYYMMDD')) SWRQ01,    --死亡日期01
    B.BLFL01,       --病例分类01
    B.BLFL02,       --病例分类02
    B.TKYS00,       --填卡医生
    (select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.TKYS00) TKYSXM,  --填卡医生姓名
    B.TKRQ00,       --填卡日期
    nvl(to_date(B.TKRQ00,'YYYYMMDD'),sysdate) TKRQ01,       --填卡日期01
    --B.JBMC00,     --疾病名称
    decode(B.CZRQ00, null,E.JBMC00, B.JBMC00) JBMC00, --疾病名称
    B.JCZCR0,       --密切接触者有无相同症状 0:无 1：有
    B.BZ0000,       --备注
    B.CZY000,       --操作员
    B.CZRQ00,       --操作日期
    B.CZSJ00,       --操作时间
    --decode(B.CZRQ00,null,'未登记','已登记') SFDJ00, --是否已登记
    --case when nvl(B.MZID00,0)>0 and D.ZZDBZ0='3' then '已登记'
    case when nvl(B.MZID00,0)>0 and D.ZZDBZ0='3' then '已登记'
         when nvl(B.MZID00,0)>0 and B.JBMC00=D.ZDMC00 then '已登记'
    else '未登记'
    end     SFDJ00,
    A.JZKS00,       --当前科室
    (select BMMC00 from BM_BMBM00 where BMBH00(+) = A.JZKS00) DQKSMC,  --当前科室名称
    B.HY0000, --婚姻
    B.WHCD00, --文化程度
    B.CRLY00,  --传染来源
    substr(trim(SF_YF_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000,
    nvl(B.SFSW00,'N') SFSW00, --是否死亡
    A.JZRQ00,                 --就诊日期
    nvl(B.SFQR00,'N') SFQR00,  --是否确认
    B.QRR000,                  --确认人
    (select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.QRR000) QRRXM0, --确认人姓名
    decode((select count(1) from BQ_JSXX00 where BRID00=A.BRID00),0,'0','1') SFYYE0,        --是否有婴儿 0：无   1：有
    D.JZYSXM, --诊断医生
    D.JZYS00,
    SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)) BRNL00,
    CRB001,CRB002,CRB003,CRB004,CRB005,CRB006,CRB007,CRB008,CRB009,CRB010,
    CRB011,CRB012,CRB013,CRB014,CRB015,CRB016,CRB017,CRB018,CRB019,CRB020,
    CRB021,CRB022,CRB023,CRB024,CRB025,CRB026,CRB027,CRB028,CRB029,CRB030,
    CRB031,CRB032,CRB033,CRB034,CRB035,CRB036,CRB037,CRB038,CRB039,CRB040,
    CRB041,CRB042,CRB043,CRB044,CRB045,CRB046,CRB047,CRB048,CRB049,CRB050,
    CRB051,CRB052,CRB053,CRB054,CRB055,CRB056,CRB057,CRB058,CRB059,CRB060,
    CRB061,CRB062,CRB063,CRB064,
    decode(CRB001,'-1','√','1','√','') SFDY01,decode(CRB002,'-1','√','1','√','') SFDY02,decode(CRB003,'-1','√','1','√','') SFDY03,decode(CRB004,'-1','√','1','√','') SFDY04,decode(CRB005,'-1','√','1','√','') SFDY05,
    decode(CRB006,'-1','√','1','√','') SFDY06,decode(CRB007,'-1','√','1','√','') SFDY07,decode(CRB008,'-1','√','1','√','') SFDY08,decode(CRB009,'-1','√','1','√','') SFDY09,decode(CRB010,'-1','√','1','√','') SFDY10,
    decode(CRB011,'-1','√','1','√','') SFDY11,decode(CRB012,'-1','√','1','√','') SFDY12,decode(CRB013,'-1','√','1','√','') SFDY13,decode(CRB014,'-1','√','1','√','') SFDY14,decode(CRB015,'-1','√','1','√','') SFDY15,
    decode(CRB016,'-1','√','1','√','') SFDY16,decode(CRB017,'-1','√','1','√','') SFDY17,decode(CRB018,'-1','√','1','√','') SFDY18,decode(CRB019,'-1','√','1','√','') SFDY19,decode(CRB020,'-1','√','1','√','') SFDY20,
    decode(CRB021,'-1','√','1','√','') SFDY21,decode(CRB022,'-1','√','1','√','') SFDY22,decode(CRB023,'-1','√','1','√','') SFDY23,decode(CRB024,'-1','√','1','√','') SFDY24,decode(CRB025,'-1','√','1','√','') SFDY25,
    decode(CRB026,'-1','√','1','√','') SFDY26,decode(CRB027,'-1','√','1','√','') SFDY27,decode(CRB028,'-1','√','1','√','') SFDY28,decode(CRB029,'-1','√','1','√','') SFDY29,decode(CRB030,'-1','√','1','√','') SFDY30,
    decode(CRB031,'-1','√','1','√','') SFDY31,decode(CRB032,'-1','√','1','√','') SFDY32,decode(CRB033,'-1','√','1','√','') SFDY33,decode(CRB034,'-1','√','1','√','') SFDY34,decode(CRB035,'-1','√','1','√','') SFDY35,
    decode(CRB036,'-1','√','1','√','') SFDY36,decode(CRB037,'-1','√','1','√','') SFDY37,decode(CRB038,'-1','√','1','√','') SFDY38,decode(CRB039,'-1','√','1','√','') SFDY39,decode(CRB040,'-1','√','1','√','') SFDY40,
    decode(CRB041,'-1','√','1','√','') SFDY41,decode(CRB042,'-1','√','1','√','') SFDY42,decode(CRB043,'-1','√','1','√','') SFDY43,decode(CRB044,'-1','√','1','√','') SFDY44,decode(CRB045,'-1','√','1','√','') SFDY45,
    decode(CRB046,'-1','√','1','√','') SFDY46,decode(CRB047,'-1','√','1','√','') SFDY47,decode(CRB048,'-1','√','1','√','') SFDY48,decode(CRB049,'-1','√','1','√','') SFDY49,decode(CRB050,'-1','√','1','√','') SFDY50,
    decode(CRB051,'-1','√','1','√','') SFDY51,decode(CRB052,'-1','√','1','√','') SFDY52,decode(CRB053,'-1','√','1','√','') SFDY53,decode(CRB054,'-1','√','1','√','') SFDY54,decode(CRB055,'-1','√','1','√','') SFDY55,
    decode(CRB056,'-1','√','1','√','') SFDY56,decode(CRB057,'-1','√','1','√','') SFDY57,decode(CRB058,'-1','√','1','√','') SFDY58,decode(CRB059,'-1','√','1','√','') SFDY59,decode(CRB060,'-1','√','1','√','') SFDY60,
    decode(CRB061,'-1','√','1','√','') SFDY61,decode(CRB062,'-1','√','1','√','') SFDY62,decode(CRB063,'-1','√','1','√','') SFDY63,decode(CRB064,'-1','√','1','√','') SFDY64,
    to_multi_byte(decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00)) SFZH00,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'男','√','') SEX_M,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'女','√','') SEX_F,
    substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),1,4)||'年'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),5,2)||'月'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),7,2)||'日' CSRQ00,
    decode(BRLS00,'本县区','√','') BRLS01,
    decode(BRLS00,'本市其它县区','√','') BRLS02,
    decode(BRLS00,'本省其它地市','√','本省其它城市','√','') BRLS03,
    decode(BRLS00,'外省','√','') BRLS04,
    decode(BRLS00,'港澳台','√','') BRLS05,
    decode(BRLS00,'外籍','√','') BRLS06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'幼托儿童','√','') BRZY01,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'散居儿童','√','') BRZY02,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'学生（大中小学）','√','') BRZY03,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'教师','√','') BRZY04,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'保育员及保姆','√','保育及保姆','√','') BRZY05,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'餐饮食品业','√','') BRZY06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'公共场所服务员','√','') BRZY07,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'商业服务','√','') BRZY08,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'医务人员','√','') BRZY09,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'工人','√','') BRZY10,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'民工','√','') BRZY11,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'农民','√','') BRZY12,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'牧民','√','') BRZY13,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'渔(船)民','√','') BRZY14,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'海员及长途驾驶员','√','') BRZY15,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'干部职员','√','') BRZY16,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'离退人员','√','') BRZY17,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'家务及待业','√','') BRZY18,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'不详','√','') BRZY19,
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'','√') BRZY20,
    decode(BLFL01,'临床诊断病例','√','') BLFL11,
    decode(BLFL01,'实验室确诊病例','√','') BLFL12,
    decode(BLFL01,'疑似病例','√','') BLFL13,
    decode(BLFL01,'病原携带者','√','') BLFL14,
    decode(BLFL01,'阳性检测','√','') BLFL15,
    decode(BLFL02,'急性','√','') BLFL21,
    decode(BLFL02,'慢性','√','') BLFL22,
    KPBH00,
    KPLX00,
    QTCRB0,
    DZJBMC,
    TKYY00,
    decode(trim(KPLX00),'1.初步报告','√','初次报告','√','') KPLX01,
    decode(trim(KPLX00),'2.订正报告','√','订正报告','√','') KPLX02,
    B.ZDXXID,
    B.TKSJ00,
    E.SFCRB0,
    substr(SF_YS_GETGHZD(A.GHID00),1,100)  GHZD00,
    (select FBMC00 from BM_BRFBB0 where FBBH00=C.FBBH00) FBMC00,
    substr(SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),A.GHRQ00),1,8) as BRNL0S,
    decode(E.SFCRB0,'1','甲类','2','乙类','3','丙类','4','其它',' ') CRBLB0,
    CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075,
    decode(CRB065,'-1','√','1','√','') SFDY65,decode(CRB066,'-1','√','1','√','') SFDY66,decode(CRB067,'-1','√','1','√','') SFDY67,
    decode(CRB068,'-1','√','1','√','') SFDY68,decode(CRB069,'-1','√','1','√','') SFDY69,decode(CRB070,'-1','√','1','√','') SFDY70,
    decode(CRB071,'-1','√','1','√','') SFDY71,decode(CRB072,'-1','√','1','√','') SFDY72,
    B.BRSZNL                   BRSZNL,   --病人实足年龄
    decode(CRB073,'-1','√','1','√','') SFDY73,   --实足年龄单位(年)
    decode(CRB074,'-1','√','1','√','') SFDY74,   --实足年龄单位(年)
    decode(CRB075,'-1','√','1','√','') SFDY75,    --实足年龄单位(年)
    CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083,
    decode(CRB076,'-1','√','1','√','') SFDY76,decode(CRB077,'-1','√','1','√','') SFDY77,
    decode(CRB078,'-1','√','1','√','') SFDY78,decode(CRB079,'-1','√','1','√','') SFDY79,decode(CRB080,'-1','√','1','√','') SFDY80,
    decode(CRB081,'-1','√','1','√','') SFDY81,decode(CRB082,'-1','√','1','√','') SFDY82,decode(CRB083,'-1','√','1','√','') SFDY83,
    decode(E.SFCRB0,'0','',A.XZZDID),
    decode(E.SFCRB0,'0','',A.XZZDMC),
    decode(D.ZZDBZ0,'3',D.ICD900,'') ZZDID0,
    decode(D.ZZDBZ0,'3',D.ZDMC00,'') ZZDMC0,
    decode(D.ZZDBZ0,'3','',D.ICD900) FZZDID,
    decode(D.ZZDBZ0,'3','',D.ZDMC00) FZZDMC,
    A.JZSJ00,                 --就诊时间
    B.BGDWMC,
    B.BGDWDH,
    D.ZDMC00,
    CRB087, decode(CRB087,'-1','√','1','√','') SFDY87, --利福平赖药
    CRB086, decode(CRB086,'-1','√','1','√','') SFDY86, --AFP疾病
    CRB088, decode(CRB088,'-1','√','1','√','') SFDY88, --病毒性肝炎丁型
    CRB089, decode(CRB089,'-1','√','1','√','') SFDY89, --埃博拉出血热
    CRB090, decode(CRB090,'-1','√','1','√','') SFDY90, --肠出血性大肠杆菌感染性腹泻
    CRB091, decode(CRB091,'-1','√','1','√','') SFDY91,  --中东呼吸综合征（MERS）
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'其他',b.brzy00) BRZY21,
    A.BRID00,
    B.CRB092, decode(B.CRB092,'-1','√','') SFDY92,--寨卡病毒病
    B.CRB093, decode(B.CRB093,'-1','√','') SFDY93,
    nvl(E.BZNLXZ,200) BZNLXZ,
    B.CRB094,decode(B.CRB094,'-1','√','') SFDY94,
    B.CRB095,decode(B.CRB095,'-1','√','') SFDY95,
    B.CRB096,decode(B.CRB096,'-1','√','') SFDY96,
    B.CRB097,decode(B.CRB097,'-1','√','') SFDY97,
    B.CRB098,decode(B.CRB098,'-1','√','') SFDY98,
    B.CRB109,decode(B.CRB109,'-1','√','') SFDY109
from
    SF_BRXXB0 A, YS_CRBDJ0 B, BM_BRXXB0 C, YS_BRZDXX D, XT_ICD900 E
where A.GHID00=B.MZID00 and A.BRID00=C.BRID00
     and D.ICD900=E.ICD900 and D.GHID00=A.GHID00 and B.JBMC00=E.JBMC00
     and instr(D.zdmc00,'复诊')<=0

union
select
    decode(B.CZRQ00, null, '', B.DJID00) DJID00,    --登记ID
    A.GHID00 MZID00,        --挂号ID
    A.GHH000,       --挂号号
    decode(B.CZRQ00, null, C.BRXM00, B.HZXM00) BRXM00,      --患者姓名
    B.HEJZXM,       --患儿家长姓名
    decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00) BRZJBH,      --身份证号
    decode(B.CZRQ00, null, C.BRXB00, B.BRXB00) BRXB00,      --性别
    decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ) BRCSRQ,      --病人出生日期
    substr(B.BRCSRQ,1,4) CSRQN0,        --病人出生日期年
    substr(B.BRCSRQ,5,2) CSRQY0,             --病人出生日期月
    substr(B.BRCSRQ,7,2) CSRQR0,        --病人出生日期日
    --to_date(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),'YYYYMMDD') CSRQ01,        --病人出生日期01
    decode(B.CZRQ00, null, C.TYDWMC, B.TYDWMC) TYDWMC,      --病人工作单位
    decode(B.CZRQ00, null, C.BRLXDH, B.BRLXDH) BRLXDH,      --病人联系电话
    B.BRLS00,       --病人隶属： --本县区  本市其它县区  本省其它地市  其他省  港澳台  外籍
    B.DZSF00,       --地址省份
    B.DZDJS0,       --地址地级市
    B.DZXQ00,       --地址县区
    B.DZXZJD,       --地址乡镇街道
    B.DZC000,               --地址村
    B.DZMPH0,       --地址门牌号
    decode(trim(nvl(B.BRLXDZ,'')),'', C.BRJTDZ, B.BRLXDZ) BRLXDZ,       --病人联系地址
    decode(B.CZRQ00, null, C.BRZY00, B.BRZY00) BRZY00,      --病人职业
    decode((select upper(substrb(nvl(max(VALUE0),'Y'),1,1)) from XT_XTCS00 where NAME00='YS_SFMRFBRQ'),'Y',nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),to_char(sysdate,'YYYYMMDD'))),nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),''))) FBRQ00,
    nvl(to_date(B.FBRQ00,'YYYYMMDD'),sysdate) FBRQ01, --发病日期01
    decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00) ZDRQ00,      --诊断日期
    to_date(decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00),'YYYYMMDD') ZDRQ01,      --诊断日期01
    decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00) ZDSJ00,      --诊断时间
    --to_date(decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00),'HH24:MI:SS') ZDSJ01,  --诊断时间01
    to_date(decode(B.CZRQ00, null, A.JZRQ00||A.JZSJ00, B.ZDRQ00||B.ZDSJ00),'YYYYMMDD HH24:MI:SS') ZDSJ01,
    decode(B.SWRQ00, null,'',substr(B.SWRQ00,1,8)) SWRQ00,      --死亡日期
    --nvl(B.SWRQ00,sysdate,to_date(B.SWRQ00,'YYYYMMDD')) SWRQ01,    --死亡日期01
    B.BLFL01,       --病例分类01
    B.BLFL02,       --病例分类02
    B.TKYS00,       --填卡医生
    (select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.TKYS00) TKYSXM,  --填卡医生姓名
    B.TKRQ00,       --填卡日期
    nvl(to_date(B.TKRQ00,'YYYYMMDD'),sysdate) TKRQ01,       --填卡日期01
    --B.JBMC00,     --疾病名称
    decode(B.CZRQ00, null,E.JBMC00, B.JBMC00) JBMC00, --疾病名称
    B.JCZCR0,       --密切接触者有无相同症状 0:无 1：有
    B.BZ0000,       --备注
    B.CZY000,       --操作员
    B.CZRQ00,       --操作日期
    B.CZSJ00,       --操作时间
    --decode(B.CZRQ00,null,'未登记','已登记') SFDJ00, --是否已登记
    --case when nvl(B.MZID00,0)>0 and D.ZZDBZ0='3' then '已登记'
    case when nvl(B.MZID00,0)>0 and D.ZZDBZ0='3' then '已登记'
         when nvl(B.MZID00,0)>0 and B.JBMC00=D.ZDMC00 then '已登记'
    else '未登记'
    end     SFDJ00,
    A.JZKS00,       --当前科室
    (select BMMC00 from BM_BMBM00 where BMBH00(+) = A.JZKS00) DQKSMC,  --当前科室名称
    B.HY0000, --婚姻
    B.WHCD00, --文化程度
    B.CRLY00,  --传染来源
    substr(trim(SF_YF_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000,
    nvl(B.SFSW00,'N') SFSW00, --是否死亡
    A.JZRQ00,                 --就诊日期
    nvl(B.SFQR00,'N') SFQR00,  --是否确认
    B.QRR000,                  --确认人
    (select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.QRR000) QRRXM0, --确认人姓名
    decode((select count(1) from BQ_JSXX00 where BRID00=A.BRID00),0,'0','1') SFYYE0,        --是否有婴儿 0：无   1：有
    D.JZYSXM, --诊断医生
    D.JZYS00,
    SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)) BRNL00,
    CRB001,CRB002,CRB003,CRB004,CRB005,CRB006,CRB007,CRB008,CRB009,CRB010,
    CRB011,CRB012,CRB013,CRB014,CRB015,CRB016,CRB017,CRB018,CRB019,CRB020,
    CRB021,CRB022,CRB023,CRB024,CRB025,CRB026,CRB027,CRB028,CRB029,CRB030,
    CRB031,CRB032,CRB033,CRB034,CRB035,CRB036,CRB037,CRB038,CRB039,CRB040,
    CRB041,CRB042,CRB043,CRB044,CRB045,CRB046,CRB047,CRB048,CRB049,CRB050,
    CRB051,CRB052,CRB053,CRB054,CRB055,CRB056,CRB057,CRB058,CRB059,CRB060,
    CRB061,CRB062,CRB063,CRB064,
    decode(CRB001,'-1','√','1','√','') SFDY01,decode(CRB002,'-1','√','1','√','') SFDY02,decode(CRB003,'-1','√','1','√','') SFDY03,decode(CRB004,'-1','√','1','√','') SFDY04,decode(CRB005,'-1','√','1','√','') SFDY05,
    decode(CRB006,'-1','√','1','√','') SFDY06,decode(CRB007,'-1','√','1','√','') SFDY07,decode(CRB008,'-1','√','1','√','') SFDY08,decode(CRB009,'-1','√','1','√','') SFDY09,decode(CRB010,'-1','√','1','√','') SFDY10,
    decode(CRB011,'-1','√','1','√','') SFDY11,decode(CRB012,'-1','√','1','√','') SFDY12,decode(CRB013,'-1','√','1','√','') SFDY13,decode(CRB014,'-1','√','1','√','') SFDY14,decode(CRB015,'-1','√','1','√','') SFDY15,
    decode(CRB016,'-1','√','1','√','') SFDY16,decode(CRB017,'-1','√','1','√','') SFDY17,decode(CRB018,'-1','√','1','√','') SFDY18,decode(CRB019,'-1','√','1','√','') SFDY19,decode(CRB020,'-1','√','1','√','') SFDY20,
    decode(CRB021,'-1','√','1','√','') SFDY21,decode(CRB022,'-1','√','1','√','') SFDY22,decode(CRB023,'-1','√','1','√','') SFDY23,decode(CRB024,'-1','√','1','√','') SFDY24,decode(CRB025,'-1','√','1','√','') SFDY25,
    decode(CRB026,'-1','√','1','√','') SFDY26,decode(CRB027,'-1','√','1','√','') SFDY27,decode(CRB028,'-1','√','1','√','') SFDY28,decode(CRB029,'-1','√','1','√','') SFDY29,decode(CRB030,'-1','√','1','√','') SFDY30,
    decode(CRB031,'-1','√','1','√','') SFDY31,decode(CRB032,'-1','√','1','√','') SFDY32,decode(CRB033,'-1','√','1','√','') SFDY33,decode(CRB034,'-1','√','1','√','') SFDY34,decode(CRB035,'-1','√','1','√','') SFDY35,
    decode(CRB036,'-1','√','1','√','') SFDY36,decode(CRB037,'-1','√','1','√','') SFDY37,decode(CRB038,'-1','√','1','√','') SFDY38,decode(CRB039,'-1','√','1','√','') SFDY39,decode(CRB040,'-1','√','1','√','') SFDY40,
    decode(CRB041,'-1','√','1','√','') SFDY41,decode(CRB042,'-1','√','1','√','') SFDY42,decode(CRB043,'-1','√','1','√','') SFDY43,decode(CRB044,'-1','√','1','√','') SFDY44,decode(CRB045,'-1','√','1','√','') SFDY45,
    decode(CRB046,'-1','√','1','√','') SFDY46,decode(CRB047,'-1','√','1','√','') SFDY47,decode(CRB048,'-1','√','1','√','') SFDY48,decode(CRB049,'-1','√','1','√','') SFDY49,decode(CRB050,'-1','√','1','√','') SFDY50,
    decode(CRB051,'-1','√','1','√','') SFDY51,decode(CRB052,'-1','√','1','√','') SFDY52,decode(CRB053,'-1','√','1','√','') SFDY53,decode(CRB054,'-1','√','1','√','') SFDY54,decode(CRB055,'-1','√','1','√','') SFDY55,
    decode(CRB056,'-1','√','1','√','') SFDY56,decode(CRB057,'-1','√','1','√','') SFDY57,decode(CRB058,'-1','√','1','√','') SFDY58,decode(CRB059,'-1','√','1','√','') SFDY59,decode(CRB060,'-1','√','1','√','') SFDY60,
    decode(CRB061,'-1','√','1','√','') SFDY61,decode(CRB062,'-1','√','1','√','') SFDY62,decode(CRB063,'-1','√','1','√','') SFDY63,decode(CRB064,'-1','√','1','√','') SFDY64,
    to_multi_byte(decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00)) SFZH00,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'男','√','') SEX_M,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'女','√','') SEX_F,
    substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),1,4)||'年'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),5,2)||'月'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),7,2)||'日' CSRQ00,
    decode(BRLS00,'本县区','√','') BRLS01,
    decode(BRLS00,'本市其它县区','√','') BRLS02,
    decode(BRLS00,'本省其它地市','√','本省其它城市','√','') BRLS03,
    decode(BRLS00,'外省','√','') BRLS04,
    decode(BRLS00,'港澳台','√','') BRLS05,
    decode(BRLS00,'外籍','√','') BRLS06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'幼托儿童','√','') BRZY01,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'散居儿童','√','') BRZY02,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'学生（大中小学）','√','') BRZY03,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'教师','√','') BRZY04,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'保育员及保姆','√','保育及保姆','√','') BRZY05,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'餐饮食品业','√','') BRZY06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'公共场所服务员','√','') BRZY07,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'商业服务','√','') BRZY08,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'医务人员','√','') BRZY09,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'工人','√','') BRZY10,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'民工','√','') BRZY11,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'农民','√','') BRZY12,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'牧民','√','') BRZY13,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'渔(船)民','√','') BRZY14,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'海员及长途驾驶员','√','') BRZY15,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'干部职员','√','') BRZY16,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'离退人员','√','') BRZY17,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'家务及待业','√','') BRZY18,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'不详','√','') BRZY19,
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'','√') BRZY20,
    decode(BLFL01,'临床诊断病例','√','') BLFL11,
    decode(BLFL01,'实验室确诊病例','√','') BLFL12,
    decode(BLFL01,'疑似病例','√','') BLFL13,
    decode(BLFL01,'病原携带者','√','') BLFL14,
    decode(BLFL01,'阳性检测','√','') BLFL15,
    decode(BLFL02,'急性','√','') BLFL21,
    decode(BLFL02,'慢性','√','') BLFL22,
    KPBH00,
    KPLX00,
    QTCRB0,
    DZJBMC,
    TKYY00,
    decode(trim(KPLX00),'1.初步报告','√','初次报告','√','') KPLX01,
    decode(trim(KPLX00),'2.订正报告','√','订正报告','√','') KPLX02,
    B.ZDXXID,
    B.TKSJ00,
    E.SFCRB0,
    substr(SF_YS_GETGHZD(A.GHID00),1,100)  GHZD00,
    (select FBMC00 from BM_BRFBB0 where FBBH00=C.FBBH00) FBMC00,
    substr(SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),A.GHRQ00),1,8) as BRNL0S,
    decode(E.SFCRB0,'1','甲类','2','乙类','3','丙类','4','其它',' ') CRBLB0,
    CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075,
    decode(CRB065,'-1','√','1','√','') SFDY65,decode(CRB066,'-1','√','1','√','') SFDY66,decode(CRB067,'-1','√','1','√','') SFDY67,
    decode(CRB068,'-1','√','1','√','') SFDY68,decode(CRB069,'-1','√','1','√','') SFDY69,decode(CRB070,'-1','√','1','√','') SFDY70,
    decode(CRB071,'-1','√','1','√','') SFDY71,decode(CRB072,'-1','√','1','√','') SFDY72,
    B.BRSZNL                   BRSZNL,   --病人实足年龄
    decode(CRB073,'-1','√','1','√','') SFDY73,   --实足年龄单位(年)
    decode(CRB074,'-1','√','1','√','') SFDY74,   --实足年龄单位(年)
    decode(CRB075,'-1','√','1','√','') SFDY75,    --实足年龄单位(年)
    CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083,
    decode(CRB076,'-1','√','1','√','') SFDY76,decode(CRB077,'-1','√','1','√','') SFDY77,
    decode(CRB078,'-1','√','1','√','') SFDY78,decode(CRB079,'-1','√','1','√','') SFDY79,decode(CRB080,'-1','√','1','√','') SFDY80,
    decode(CRB081,'-1','√','1','√','') SFDY81,decode(CRB082,'-1','√','1','√','') SFDY82,decode(CRB083,'-1','√','1','√','') SFDY83,
    decode(E.SFCRB0,'0','',A.XZZDID),
    decode(E.SFCRB0,'0','',A.XZZDMC),
    decode(D.ZZDBZ0,'3',D.ICD900,'') ZZDID0,
    decode(D.ZZDBZ0,'3',D.ZDMC00,'') ZZDMC0,
    decode(D.ZZDBZ0,'3','',D.ICD900) FZZDID,
    decode(D.ZZDBZ0,'3','',D.ZDMC00) FZZDMC,
    A.JZSJ00,                 --就诊时间
    B.BGDWMC,
    B.BGDWDH,
    D.ZDMC00,
    CRB087, decode(CRB087,'-1','√','1','√','') SFDY87, --利福平赖药
    CRB086, decode(CRB086,'-1','√','1','√','') SFDY86, --AFP疾病
    CRB088, decode(CRB088,'-1','√','1','√','') SFDY88, --病毒性肝炎丁型
    CRB089, decode(CRB089,'-1','√','1','√','') SFDY89, --埃博拉出血热
    CRB090, decode(CRB090,'-1','√','1','√','') SFDY90, --肠出血性大肠杆菌感染性腹泻
    CRB091, decode(CRB091,'-1','√','1','√','') SFDY91,  --中东呼吸综合征（MERS）
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'其他',b.brzy00) BRZY21,
    A.BRID00,
    B.CRB092, decode(B.CRB092,'-1','√','') SFDY92,--寨卡病毒病
    B.CRB093, decode(B.CRB093,'-1','√','') SFDY93,
    nvl(E.BZNLXZ,200) BZNLXZ,
    B.CRB094,decode(B.CRB094,'-1','√','') SFDY94,
    B.CRB095,decode(B.CRB095,'-1','√','') SFDY95,
    B.CRB096,decode(B.CRB096,'-1','√','') SFDY96,
    B.CRB097,decode(B.CRB097,'-1','√','') SFDY97,
    B.CRB098,decode(B.CRB098,'-1','√','') SFDY98,
    B.CRB109,decode(B.CRB109,'-1','√','') SFDY109
from
    SF_BRXXB0 A, YS_CRBDJ0 B, BM_BRXXB0 C, YS_BRZDXX D, XT_ICD900 E
where A.GHID00=B.MZID00(+) and A.BRID00=C.BRID00
     and D.ICD900=E.ICD900 and D.GHID00=A.GHID00 --and D.ZDMC00=E.JBMC00  for  MZHS-20131212-001
     and instr(D.zdmc00,'复诊')<=0
  and not exists(select 1 from ys_crbdj0 where mzid00=a.ghid00)
union
select
    decode(B.CZRQ00, null, '', B.DJID00) DJID00,    --登记ID
    A.GHID00 MZID00,        --挂号ID
                A.GHH000,       --挂号号
    decode(B.CZRQ00, null, C.BRXM00, B.HZXM00) BRXM00,      --患者姓名
    B.HEJZXM,       --患儿家长姓名
    decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00) BRZJBH,      --身份证号
    decode(B.CZRQ00, null, C.BRXB00, B.BRXB00) BRXB00,      --性别
    decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ) BRCSRQ,      --病人出生日期
    substr(B.BRCSRQ,1,4) CSRQN0,        --病人出生日期年
    substr(B.BRCSRQ,5,2) CSRQY0,             --病人出生日期月
    substr(B.BRCSRQ,7,2) CSRQR0,        --病人出生日期日
    --to_date(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),'YYYYMMDD') CSRQ01,        --病人出生日期01
    
    decode(B.CZRQ00, null, C.TYDWMC, B.TYDWMC) TYDWMC,      --病人工作单位
    decode(B.CZRQ00, null, C.BRLXDH, B.BRLXDH) BRLXDH,      --病人联系电话
    B.BRLS00,       --病人隶属： --本县区  本市其它县区  本省其它地市  其他省  港澳台  外籍
    B.DZSF00,       --地址省份
    B.DZDJS0,       --地址地级市
    B.DZXQ00,       --地址县区
    B.DZXZJD,       --地址乡镇街道
    B.DZC000,               --地址村
    B.DZMPH0,       --地址门牌号
    decode(trim(nvl(B.BRLXDZ,'')),'', C.BRJTDZ, B.BRLXDZ) BRLXDZ,       --病人联系地址
    decode(B.CZRQ00, null, C.BRZY00, B.BRZY00) BRZY00,      --病人职业
    decode((select upper(substrb(nvl(max(VALUE0),'Y'),1,1)) from XT_XTCS00 where NAME00='YS_SFMRFBRQ'),'Y',nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),to_char(sysdate,'YYYYMMDD'))),nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where A.GHID00=f.GHID00 and rownum = 1 ),''))) FBRQ00,
    nvl(to_date(B.FBRQ00,'YYYYMMDD'),sysdate) FBRQ01, --发病日期01
    decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00) ZDRQ00,      --诊断日期
    to_date(decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00),'YYYYMMDD') ZDRQ01,      --诊断日期01
    decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00) ZDSJ00,      --诊断时间
    --to_date(decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00),'HH24:MI:SS') ZDSJ01,  --诊断时间01
    to_date(decode(B.CZRQ00, null, A.JZRQ00||A.JZSJ00, B.ZDRQ00||B.ZDSJ00),'YYYYMMDD HH24:MI:SS') ZDSJ01,
    decode(B.SWRQ00, null,'',substr(B.SWRQ00,1,8)) SWRQ00,      --死亡日期
    --  nvl(B.SWRQ00,sysdate,to_date(B.SWRQ00,'YYYYMMDD')) SWRQ01,  --死亡日期01
    B.BLFL01,       --病例分类01
    B.BLFL02,       --病例分类02
    B.TKYS00,       --填卡医生
    (select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.TKYS00) TKYSXM,  --填卡医生姓名
    B.TKRQ00,       --填卡日期
    nvl(to_date(B.TKRQ00,'YYYYMMDD'),sysdate) TKRQ01,       --填卡日期01
    --B.JBMC00,     --疾病名称
    decode(B.CZRQ00, null,E.JBMC00, B.JBMC00) JBMC00, --疾病名称
    B.JCZCR0,       --密切接触者有无相同症状 0:无 1：有
    B.BZ0000,       --备注
    B.CZY000,       --操作员
    B.CZRQ00,       --操作日期
    B.CZSJ00,       --操作时间
    --decode(B.CZRQ00,null,'未登记','已登记') SFDJ00, --是否已登记
    case when nvl(B.MZID00,0)>0 and D.ZZDBZ0='3' then '已登记'
      when nvl(B.MZID00,0)>0 and B.JBMC00=D.ZDMC00 then '已登记'
    else '未登记'
    end     SFDJ00,
    A.JZKS00,       --当前科室
    (select BMMC00 from BM_BMBM00 where BMBH00(+) = A.JZKS00) DQKSMC,  --当前科室名称
    B.HY0000, --婚姻
    B.WHCD00, --文化程度
    B.CRLY00,  --传染来源
    substr(trim(SF_YF_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000,
    nvl(B.SFSW00,'N') SFSW00, --是否死亡
    A.JZRQ00,                 --就诊日期
    nvl(B.SFQR00,'N') SFQR00,  --是否确认
    B.QRR000,                  --确认人
    (
      select ZWXM00 from BM_YGBM00 where YGBH00(+) = B.QRR000) QRRXM0, --确认人姓名
      decode((select count(1) from BQ_JSXX00 where BRID00=A.BRID00),0,'0','1') SFYYE0,      --是否有婴儿 0：无   1：有
      D.JZYSXM, --诊断医生
      D.JZYS00,
      SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)
    ) BRNL00,
    CRB001,CRB002,CRB003,CRB004,CRB005,CRB006,CRB007,CRB008,CRB009,CRB010,
    CRB011,CRB012,CRB013,CRB014,CRB015,CRB016,CRB017,CRB018,CRB019,CRB020,
    CRB021,CRB022,CRB023,CRB024,CRB025,CRB026,CRB027,CRB028,CRB029,CRB030,
    CRB031,CRB032,CRB033,CRB034,CRB035,CRB036,CRB037,CRB038,CRB039,CRB040,
    CRB041,CRB042,CRB043,CRB044,CRB045,CRB046,CRB047,CRB048,CRB049,CRB050,
    CRB051,CRB052,CRB053,CRB054,CRB055,CRB056,CRB057,CRB058,CRB059,CRB060,
    CRB061,CRB062,CRB063,CRB064,
    decode(CRB001,'-1','√','1','√','') SFDY01,decode(CRB002,'-1','√','1','√','') SFDY02,decode(CRB003,'-1','√','1','√','') SFDY03,decode(CRB004,'-1','√','1','√','') SFDY04,decode(CRB005,'-1','√','1','√','') SFDY05,
    decode(CRB006,'-1','√','1','√','') SFDY06,decode(CRB007,'-1','√','1','√','') SFDY07,decode(CRB008,'-1','√','1','√','') SFDY08,decode(CRB009,'-1','√','1','√','') SFDY09,decode(CRB010,'-1','√','1','√','') SFDY10,
    decode(CRB011,'-1','√','1','√','') SFDY11,decode(CRB012,'-1','√','1','√','') SFDY12,decode(CRB013,'-1','√','1','√','') SFDY13,decode(CRB014,'-1','√','1','√','') SFDY14,decode(CRB015,'-1','√','1','√','') SFDY15,
    decode(CRB016,'-1','√','1','√','') SFDY16,decode(CRB017,'-1','√','1','√','') SFDY17,decode(CRB018,'-1','√','1','√','') SFDY18,decode(CRB019,'-1','√','1','√','') SFDY19,decode(CRB020,'-1','√','1','√','') SFDY20,
    decode(CRB021,'-1','√','1','√','') SFDY21,decode(CRB022,'-1','√','1','√','') SFDY22,decode(CRB023,'-1','√','1','√','') SFDY23,decode(CRB024,'-1','√','1','√','') SFDY24,decode(CRB025,'-1','√','1','√','') SFDY25,
    decode(CRB026,'-1','√','1','√','') SFDY26,decode(CRB027,'-1','√','1','√','') SFDY27,decode(CRB028,'-1','√','1','√','') SFDY28,decode(CRB029,'-1','√','1','√','') SFDY29,decode(CRB030,'-1','√','1','√','') SFDY30,
    decode(CRB031,'-1','√','1','√','') SFDY31,decode(CRB032,'-1','√','1','√','') SFDY32,decode(CRB033,'-1','√','1','√','') SFDY33,decode(CRB034,'-1','√','1','√','') SFDY34,decode(CRB035,'-1','√','1','√','') SFDY35,
    decode(CRB036,'-1','√','1','√','') SFDY36,decode(CRB037,'-1','√','1','√','') SFDY37,decode(CRB038,'-1','√','1','√','') SFDY38,decode(CRB039,'-1','√','1','√','') SFDY39,decode(CRB040,'-1','√','1','√','') SFDY40,
    decode(CRB041,'-1','√','1','√','') SFDY41,decode(CRB042,'-1','√','1','√','') SFDY42,decode(CRB043,'-1','√','1','√','') SFDY43,decode(CRB044,'-1','√','1','√','') SFDY44,decode(CRB045,'-1','√','1','√','') SFDY45,
    decode(CRB046,'-1','√','1','√','') SFDY46,decode(CRB047,'-1','√','1','√','') SFDY47,decode(CRB048,'-1','√','1','√','') SFDY48,decode(CRB049,'-1','√','1','√','') SFDY49,decode(CRB050,'-1','√','1','√','') SFDY50,
    decode(CRB051,'-1','√','1','√','') SFDY51,decode(CRB052,'-1','√','1','√','') SFDY52,decode(CRB053,'-1','√','1','√','') SFDY53,decode(CRB054,'-1','√','1','√','') SFDY54,decode(CRB055,'-1','√','1','√','') SFDY55,
    decode(CRB056,'-1','√','1','√','') SFDY56,decode(CRB057,'-1','√','1','√','') SFDY57,decode(CRB058,'-1','√','1','√','') SFDY58,decode(CRB059,'-1','√','1','√','') SFDY59,decode(CRB060,'-1','√','1','√','') SFDY60,
    decode(CRB061,'-1','√','1','√','') SFDY61,decode(CRB062,'-1','√','1','√','') SFDY62,decode(CRB063,'-1','√','1','√','') SFDY63, decode(CRB064,'-1','√','1','√','') SFDY64,
    to_multi_byte(decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00)) SFZH00,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'男','√','') SEX_M,
    decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'女','√','') SEX_F,
    substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),1,4)||'年'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),5,2)||'月'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231','        ',B.BRCSRQ)),7,2)||'日' CSRQ00,
    decode(BRLS00,'本县区','√','') BRLS01,
    decode(BRLS00,'本市其它县区','√','') BRLS02,
    decode(BRLS00,'本省其它地市','√','本省其它城市','√','') BRLS03,
    decode(BRLS00,'外省','√','') BRLS04,
    decode(BRLS00,'港澳台','√','') BRLS05,
    decode(BRLS00,'外籍','√','') BRLS06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'幼托儿童','√','') BRZY01,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'散居儿童','√','') BRZY02,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'学生（大中小学）','√','') BRZY03,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'教师','√','') BRZY04,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'保育员及保姆','√','保育及保姆','√','') BRZY05,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'餐饮食品业','√','') BRZY06,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'公共场所服务员','√','') BRZY07,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'商业服务','√','') BRZY08,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'医务人员','√','') BRZY09,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'工人','√','') BRZY10,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'民工','√','') BRZY11,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'农民','√','') BRZY12,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'牧民','√','') BRZY13,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'渔(船)民','√','') BRZY14,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'海员及长途驾驶员','√','') BRZY15,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'干部职员','√','') BRZY16,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'离退人员','√','') BRZY17,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'家务及待业','√','') BRZY18,
    decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'不详','√','') BRZY19,
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'','√') BRZY20,
    decode(BLFL01,'临床诊断病例','√','') BLFL11,
    decode(BLFL01,'实验室确诊病例','√','') BLFL12,
    decode(BLFL01,'疑似病例','√','') BLFL13,
    decode(BLFL01,'病原携带者','√','') BLFL14,
    decode(BLFL01,'阳性检测','√','') BLFL15,
    decode(BLFL02,'急性','√','') BLFL21,
    decode(BLFL02,'慢性','√','') BLFL22,
    KPBH00,
    KPLX00,
    QTCRB0,
    DZJBMC,
    TKYY00,
    decode(trim(KPLX00),'1.初步报告','√','初次报告','√','') KPLX01,
    decode(trim(KPLX00),'2.订正报告','√','订正报告','√','') KPLX02,
    B.ZDXXID,
    B.TKSJ00,
    E.SFCRB0,
    substr(SF_YS_GETGHZD(A.GHID00),1,100)  GHZD00,
    (select FBMC00 from BM_BRFBB0 where FBBH00=C.FBBH00) FBMC00,
    substr(SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ),A.GHRQ00),1,8) as BRNL0S,
    decode(E.SFCRB0,'1','甲类','2','乙类','3','丙类','4','其它',' ') CRBLB0,
    CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075,
    decode(CRB065,'-1','√','1','√','') SFDY65,decode(CRB066,'-1','√','1','√','') SFDY66,decode(CRB067,'-1','√','1','√','') SFDY67,
    decode(CRB068,'-1','√','1','√','') SFDY68,decode(CRB069,'-1','√','1','√','') SFDY69,decode(CRB070,'-1','√','1','√','') SFDY70,
    decode(CRB071,'-1','√','1','√','') SFDY71,decode(CRB072,'-1','√','1','√','') SFDY72,
    B.BRSZNL                   BRSZNL,   --病人实足年龄
    decode(CRB073,'-1','√','1','√','') SFDY73,   --实足年龄单位(年)
    decode(CRB074,'-1','√','1','√','') SFDY74,   --实足年龄单位(年)
    decode(CRB075,'-1','√','1','√','') SFDY75,   --实足年龄单位(年)
    CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083,
    decode(CRB076,'-1','√','1','√','') SFDY76,decode(CRB077,'-1','√','1','√','') SFDY77,
    decode(CRB078,'-1','√','1','√','') SFDY78,decode(CRB079,'-1','√','1','√','') SFDY79,decode(CRB080,'-1','√','1','√','') SFDY80,
    decode(CRB081,'-1','√','1','√','') SFDY81,decode(CRB082,'-1','√','1','√','') SFDY82,decode(CRB083,'-1','√','1','√','') SFDY83,
    decode(E.SFCRB0,'0','',A.XZZDID),
    decode(E.SFCRB0,'0','',A.XZZDMC),
    decode(D.ZZDBZ0,'3',D.ICD900,'') ZZDID0,
    decode(D.ZZDBZ0,'3',D.ZDMC00,'') ZZDMC0,
    decode(D.ZZDBZ0,'3','',D.ICD900) FZZDID,
    decode(D.ZZDBZ0,'3','',D.ZDMC00) FZZDMC,
    A.JZSJ00,                 --就诊时间
    B.BGDWMC,
    B.BGDWDH,
    D.ZDMC00,
    CRB087, decode(CRB087,'-1','√','1','√','') SFDY87, --利福平赖药
    CRB086, decode(CRB086,'-1','√','1','√','') SFDY86, --AFP疾病
    CRB088, decode(CRB088,'-1','√','1','√','') SFDY88, --病毒性肝炎丁型
    CRB089, decode(CRB089,'-1','√','1','√','') SFDY89, --埃博拉出血热
    CRB090, decode(CRB090,'-1','√','1','√','') SFDY90, --肠出血性大肠杆菌感染性腹泻
    CRB091, decode(CRB091,'-1','√','1','√','') SFDY91,  --中东呼吸综合征（MERS）
    decode(instr(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他'),0,'其他',b.brzy00) BRZY21,
    A.BRID00,
    B.CRB092, decode(B.CRB092,'-1','√','') SFDY92,--寨卡病毒病
    B.CRB093, decode(B.CRB093,'-1','√','') SFDY93,
    nvl(E.BZNLXZ,200) BZNLXZ,
    B.CRB094,decode(B.CRB094,'-1','√','') SFDY94,
    B.CRB095,decode(B.CRB095,'-1','√','') SFDY95,
    B.CRB096,decode(B.CRB096,'-1','√','') SFDY96,
    B.CRB097,decode(B.CRB097,'-1','√','') SFDY97,
    B.CRB098,decode(B.CRB098,'-1','√','') SFDY98,
    B.CRB109,decode(B.CRB109,'-1','√','') SFDY109
from
    SF_BRXXB0 A, YS_CRBDJ0 B, BM_BRXXB0 C, YS_BRZDXX D, XT_ICD900 E,BM_TYZD00 F
where A.GHID00=B.MZID00(+) and A.BRID00=C.BRID00 and A.GHID00=D.GHID00
      and instr(D.ZDMC00,'复诊')<=0 and ((D.ICD900 is null) or (exists (select 1 from XT_ICD900 where ICD900=D.ICD900 and JBMC00<>D.ZDMC00)))
      and F.ZDMC00='门诊自定义诊断传染病字眼' and F.BH0000 is not null and F.YXBZ00='1'
      and instr(D.ZDMC00,F.MC0000)>0 and rpad(F.BH0000,10)=E.ICD900
/