CREATE OR REPLACE VIEW VW_YS_SYJBDJ_MZJK AS
select
A.DJID00,A.KPH000,A.JBBM00,A.MZZYBZ,A.SFFZ00,
A.YLJGMC,A.ZYGHID,A.BRID00,A.ZYH000,decode(mzzybz,'0',A.MZH000,a.zyh000) mzh000,
A.HZXM00,A.BRXB00,A.BRCSRQ,A.SFZH00,A.BRHYZK,
A.BRLXDH,decode(A.BRLS00,'本市其他地区','本市其他县区','本省其他城市','本省其他市',A.BRLS00) brls00,A.DZSF00,A.DZDJS0,A.DZXQ00,
A.BRLXDZ||A.DZC000 as BRLXDZ,decode(A.BRZY00,'商务服务','商业服务',A.BRZY00) BRZY00,A.BRZYQT,A.JHRXM0,A.FBRQ00,
A.FBSJ00,A.JZRQ00,A.JZSJ00,A.SWRQ00,A.SWSJ00,
A.GZDWMC,A.CZRQ00,A.ZQYKSS,A.KSSMC0,A.ZDMC00,
A.BRTW00,A.OTCS00,
nvl(A.FXCS00,'1') FXCS00,
A.QTZZ00,A.QTXH00,
A.QTHX00,A.QTXNXG,A.QTMNXT,A.QTSJXT,A.QTPXZZ,
A.BS0000,A.CZY000,(SELECT zwxm00 FROM BM_YGBM00 WHERE YGBH00=NVL(A.JZYS00,A.CZY000)) JZYS00,A.SYZZ01,A.SYZZ02,
A.SYZZ03,A.SYZZ04,A.SYZZ05,A.SYZZ06,A.SYZZ07,
A.SYZZ08,A.SYZZ09,A.SYZZ10,A.SYZZ11,A.SYZZ12,
A.SYZZ13,A.SYZZ14,A.SYZZ15,A.SYZZ16,A.SYZZ17,
A.SYZZ18,A.SYZZ19,A.SYZZ20,A.SYZZ21,A.SYZZ22,
A.SYZZ23,A.SYZZ24,A.SYZZ25,A.SYZZ26,A.SYZZ27,
A.SYZZ28,A.SYZZ29,A.SYZZ30,A.SYZZ31,A.SYZZ32,
A.SYZZ33,A.SYZZ34,A.SYZZ35,A.SYZZ36,A.SYZZ37,
A.SYZZ38,A.SYZZ39,A.SYZZ40,A.SYZZ41,A.SYZZ42,
A.SYZZ43,A.SYZZ44,A.SYZZ45,A.SYZZ46,A.SYZZ47,
A.SYZZ48,A.SYZZ49,A.SYZZ50,A.SYZZ51,A.SYZZ52,
A.SYZZ53,A.SYZZ54,A.SYZZ55,A.SYZZ56,A.SYZZ57,
A.SYZZ58,A.SYZZ59,A.SYZZ60,A.SYZZ61,A.SYZZ62,
A.SYZZ63,A.SYZZ64,A.SYZZ65,A.SYZZ66,A.SYZZ67,
A.SYZZ68,A.SYZZ69,A.SYZZ70,A.SYZZ71,A.SYZZ72,
A.SYZZ73,A.SYZZ74,A.SYZZ75,A.SYZZ76,A.SYZZ77,
A.SYZZ78,A.SYZZ79,A.SYZZ80,A.SYZZ81,A.SYZZ82,
A.SYZZ83,A.SYZZ84,A.SYZZ85,A.SYZZ86,A.SYZZ87,
A.SYZZ88,A.SYZZ89,A.QTCBZD,A.SYZZ90,A.SYZZ91,
A.SYZZ92,A.SYZZ93,A.SYZZ94,A.SYZZ95,A.SYZZ96,
A.SYZZ97,A.QTJWBS,A.SFHYSP,A.SFBBCJ,A.ZT0000,
A.CBZDHW,A.CBZDBL,
nvl(A.TBR000,(select JZYSXM from SF_BRXXB0 where GHID00=a.ZYGHID)) TBR000,
A.TBRQ00,A.TBSJ00,
A.XZZ000,A.MPH000,A.LX0000,A.SYZZXB,A.GUID00,
nvl(B.SPMC00,'无') SPMC00,
(select mc0000 from bm_tyzd00 where zdmc00 ='食源性疾病登记-暴露信息-食品分类' and bh0000=b.spfl00) SPFL00,
(select mc0000 from bm_tyzd00 where zdmc00 ='食源性疾病登记-暴露信息-加工或包装方式' and bh0000=b.jgbzfs) JGBZFS,
nvl(B.SPPP00,'无') SPPP00,nvl(B.SCCJ00,'无') SCCJ00,
decode((SELECT COUNT(*) FROM bm_tyzd00 bm where  bm.zdmc00 = '食源性疾病登记-暴露信息-进食场所类型' and bm.bh0000 = B.JSCSlX), 0 ,'其他',1,
(SELECT bm.mc0000 FROM bm_tyzd00 bm where  bm.zdmc00 = '食源性疾病登记-暴露信息-进食场所类型' and bm.bh0000 = B.JSCSlX)
)   JSCS00,
(select mc0000 from bm_tyzd00  where zdmc00 ='食源性疾病登记-暴露信息-进食场所类型' and  bh0000=b.JSCSLX ) JSCSLX,
decode((SELECT COUNT(*) FROM bm_tyzd00 bm where  bm.zdmc00 = '食源性疾病登记-暴露信息-购买场所类型' and bm.bh0000 = B.gmcslx), 0 ,'其他',1,
(SELECT bm.mc0000 FROM bm_tyzd00 bm where  bm.zdmc00 = '食源性疾病登记-暴露信息-购买场所类型' and bm.bh0000 = B.gmcslx)
)   GMDD00,
B.JSRQ00,B.JSSJ00,
nvl(B.JSRS00,'1') JSRS00,
B.QTRQK0,B.SFCY00,
nvl(B.GMJNJW,'未知') GMJNJW,
nvl(B.GMDZSF,'未知') GMDZSF,
nvl(B.GMDZCS,'未知') GMDZCS,
nvl(B.GMDZXQ,'未知') GMDZXQ,
nvl(B.GMDZXZ||B.GMDZ00,'未知') GMDZ00,
nvl(B.JSJNJW,'未知') JSJNJW,
nvl(B.JSDZSF,'未知') JSDZSF,
nvl(B.JSDZCS,'未知') JSDZCS,
nvl(B.JSDZXQ,'未知') JSDZXQ,
nvl(B.JSDZXZ||B.JSDZ00,'未知') JSDZ00,
(select mc0000 from bm_tyzd00  where zdmc00 ='食源性疾病登记-暴露信息-购买场所类型' and  bh0000=b.GMCSLX ) GMCSLX,
decode(a.Mzzybz,'0',(select b.yyid00 from sf_brxxb0 c ,bm_bmbm00 b  where ghid00 = a.zyghid and c.jzks00=b.bmbh00 ),
(select b.yyid00 from zy_brxxb0 c ,bm_bmbm00 b  where zyid00 = a.zyghid and c.dqks00=b.bmbh00)) YYID00,
c.bblx00,c.bbsl00,c.bbdw00
from YS_SYJBDJ A , YS_SYSPMX B ,ys_sybbcj C  where A.DJID00 = B.DJID00(+) and a.djid00=c.djid00(+)
and a.ZT0000='2' and a.djid00 not in (
select djid00 from ys_syjbdj
where  scsbyy   in ('门诊号已存在') );