CREATE OR REPLACE VIEW VW_BA_BRZYFY_SE AS
select
 A.LSH000,A.BAH000,A.ZYCS00,A.ZYH000,A.BRXM00,A.BRXB00,A.BRCSRQ,
 to_char(A.BRNL00) BRNL00,       --病人年龄
 A.HYZK00,A.ZY0000,A.BRLY00,A.CSSF00,A.CSXS00,A.MZ0000,
 A.GJ0000,A.SFZH00,A.GZDW00,A.GZDH00,A.GZYB00,A.JTDZ00,A.JTDH00
,A.JTYB00,A.LXRXM0,A.LXRDZ0,A.LXRDH0,A.LXRGX0,A.ZDFS00,A.RYQK00
,A.RYRQ00,A.RYSJ00,A.RYKB00,A.RYKBMC,A.RYBF00,A.ZKKB00,A.ZKKBMC,A.CYRQ00
,A.CYSJ00,A.CYKB00,A.CYKBMC,A.CYBF00,A.ZYTS00
,A.QZRQ00,A.QZSJ00,A.BLZD00,A.GMYW00,A.HBSAG0
,A.HCVAB0,A.HIVAB0,A.BAZL00,A.SFSJ00,A.DYBL00,A.XX0000
,A.RH0000,decode(A.SXFY00,'0','未输','1','有','2','无') SXFY00,A.HXB000,A.XXB000,A.XJ0000,A.QX0000,A.SXQT00,A.ZKRQ00
,A.BAZT00,A.SRRK00,A.LRRY00,A.LRRYMC,A.JFFS00,A.ZJE000,A.BZ0000
,A.ZYLSH0,A.CARDNO,A.EJKS00,A.EJKSMC,A.SSZDWY,DECODE(A.RYTJ00,'1','急诊','2','门诊','3','其他医疗机构转入','其他') RYTJ00,A.CYFS00
,A.RYQZL0,A.QJFF00,A.SFWZ00
,A.SFYNQK
,A.ZKBF00,A.BLH000,A.ZYMZCY,A.ZYRYCY,A.SWGBYY,A.SWRQ00
,A.SWSJ00,A.SFCRB0,A.CRBFL0,A.SFBG00,A.KZR000,A.KZRMC0,A.FZR000
,A.FZRMC0,A.ZZYS00,A.ZZYSMC,A.ZYYS00,A.ZYYSMC,A.JXYS00,A.JXYSMC,A.YJSYS0,A.YJSMC0
,A.SXYS00,A.SXYSMC,A.ZKYS00,A.ZKYSMC,A.ZKHS00,A.ZKHSMC,A.SHBZ00,A.DYRQ00
,A.DYCZ00,A.KYBL00,A.SYFY00,A.SSDYL0,A.ZLDYL0,A.ZDDYL0,A.ZCDYL0
,A.YBKH00,A.YBID00,A.YBMC00,A.YBZYH0,A.RYTJ01,A.CYFS01,A.RYQZL1
,decode(A.ZLLB00,'1','中医','1.1','中医','1.2','民族医','2','中西医','3','西医') ZLLB00,A.ZZZYZ1,A.QJFF01,A.SFWZ01,A.SFJZ01,A.YNQK01,A.SFCRB1
,A.CRBFL1,A.SFBG01,A.SSDYBL,A.ZLDYBL,A.JCDYBL,A.FSRQ00,A.FSSJ00,A.FSR000
,A.KYBL01,A.SYFY01,A.SXFY01,A.JTDZ01,A.LXRDZ1,A.SHRXM0,A.SHRQ00
,A.SHSJ00,A.SCTJYS,A.SCTJRQ,A.SCTJSJ,A.JKDAH0,A.BRNLMM,A.BRNLDD,A.YECSTZ
,A.YERYTZ,A.CSDS00,A.JGSF00,A.JGXS00,A.XZZSF0,A.XZZDQ0,A.XZZXS0
,A.XZZXC0,A.XZZDH0,A.XZZYB0,A.JTDZDQ,A.JTDZXS,A.BLZDBM
,A.SFYWGM,A.ZRHS00,A.ZRHSMC,A.ZYYLJG,A.ZZYJH0,A.ZZYMD0,A.HMRYQD
,A.HMRYQH,A.HMRYQM,A.HMRYHD,A.HMRYHH,A.HMRYHM,A.SSZDBM,A.LCLJ00
,A.LCLJ01,A.ZLSB00,A.ZLSB01,A.ZLJS00,A.ZLJS01,A.BZSH01,A.SSSFTB
,A.YLFKFS,A.FKFSMC,A.FKCZ00,A.FKFZ00,A.FKLDY0,A.FKHLQK,A.JHJTPH
,A.JHJPY0,A.YMSYH0,A.YMSYR0,A.YMSYZ0,A.YMSYE0,A.YMSYQT,A.FSBAJK
,A.BAWCBZ,A.FSBARQ,A.CSDZDM,A.CYJHMD
,A.CYJH00
,A.QSRQ00,A.QSSJ00
,A.QSR000
,A.LYFS00
,A.JTDZSF || A.JTDZDQ || A.JTDZXS || A.JTDZ01 JTDZSF,--户口地址
A.JTDZSF || A.JTDZDQ || A.JTDZXS HKDZSSQ, --户口地址省市区
A.XZZSF0 || A.XZZDQ0 || A.XZZXS0 XZZSSQ,--现住址省市区
A.XZZSF0 || A.XZZDQ0 || A.XZZXS0 ||A.XZZXC0 XZZ000 --现住址


--以下病案附页
,(select decode( B.Jllclj,'1','是','2','否',B.Jllclj) from BA_BRZYFY b where b.zyid00=a.lsh000)  Jllclj
,(select B.QJCS00 from BA_BRZYFY b where b.zyid00=a.lsh000)  QJCS00--抢救次数
,(select B.CGCS00 b from BA_BRZYFY b where b.zyid00=a.lsh000) CGCS00 --成功次数
,(select  decode( B.JXDBFF,'1','是','2','否',B.JXDBFF) from BA_BRZYFY b where b.zyid00=a.lsh000 ) JXDBFF
,(select B.YNGRMC from BA_BRZYFY b where b.zyid00=a.lsh000 ) Grxjmc
,(select decode( B.Grxjmc,'1','是','2','否',B.SFYYGR) from BA_BRZYFY b where b.zyid00=a.lsh000) SFYYGR
,(select decode( B.SFSX00,'1','是','2','否',B.SFSX00)  from BA_BRZYFY b where b.zyid00=a.lsh000) SFSX00
,(select decode( B.ZYSFBW,'1','是','2','否',B.ZYSFBW) from BA_BRZYFY b where b.zyid00=a.lsh000) ZYSFBW
,(select B.sfswbl from BA_BRZYFY b where b.zyid00=a.lsh000) sfswbl
,(select b.DBZBM0 from BA_BRZYFY b where b.zyid00=a.lsh000) DBZBM0
,(select b.DBZMC0 from BA_BRZYFY b where b.zyid00=a.lsh000) DBZMC0
,(select b.DBZFF0 from BA_BRZYFY b where b.zyid00=a.lsh000) DBZFF0
,(select b.LCLJBM  from BA_BRZYFY b where b.zyid00=a.lsh000) LCLJBM
,(select  b.LCLJMC from BA_BRZYFY b where b.zyid00=a.lsh000) LCLJMC
,(select decode( B.SFTSBL,'1','是','2','否',' ') from BA_BRZYFY b where b.zyid00=a.lsh000) SFTSBL
,(select decode( B.SFCRFF,'1','是','2','否',' ') from BA_BRZYFY b where b.zyid00=a.lsh000) SFCRFF
,(select decode( B.CRFFLX,'1','肝衰竭','2','呼吸衰竭（呼吸机依赖）','3','脊髓损伤后遗症','4','颅脑损伤康复','5','脑血管意外恢复期','6','肿瘤晚期对症治疗','7','家庭病床','8','精神病住院',' ') from BA_BRZYFY b where b.zyid00=a.lsh000) CRFFLX

--手术
,(select ssm000 from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSBM --主手术编码
,(select ssmc00 from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSMC --主手术名称
,(select ssrq00 from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) BASZSSSJ --主手术日期
,(select SSYSMC from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSSZ --主手术术者
,(select IYSMC0 from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSYZ --主手术一助
,(select IIYSMC from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSEZ
,(select SSZL00 from ba_brssxx ss where ss.lsh000=a.lsh000 and ssxh00='1' and rownum=1) ZSSJB
,(select aa.qkyhdj from ba_basssb aa where aa.lsh000 =A.lsh000 and rownum=1) yhdj00
,(select ZDMC00 from BQ_BRZDXX ZD where ZD.ZYID00=A.LSH000 and ZD.ZDLB00='3' and rownum=1) LCCYXYZYZD--临床出院西医主要诊断
,SF_BA_GETLCZDB0(A.LSH000,'3',0,1) LCCYXYZYZDJBBM --临床出院西医主要诊断疾病编码
,SF_BA_GETLCZDB0(A.LSH000,'3',1,1) LCCYXYZYZDRYBQ --临床出院西医主要诊断入院病情
,SF_BA_GETLCZDB0(A.LSH000,'a',2,1) LCCYZYZDZB --临床出院中医诊断主病
,SF_BA_GETLCZDB0(A.LSH000,'a',0,1) LCCYZYZDZBJBBM  --临床出院中医诊断主病疾病编码
,SF_BA_GETLCZDB0(A.LSH000,'a',1,1) LCCYZYZDZBRYBQ  --临床出院中医诊断主病入院病情
,SF_BA_GETLCZDB0(A.LSH000,'b',2,1) LCCYZYZDZZ --临床出院中医诊断主证
,SF_BA_GETLCZDB0(A.LSH000,'b',0,1) LCCYZYZDZZJBBM--临床出院中医诊断主证疾病编码
,SF_BA_GETLCZDB0(A.LSH000,'b',1,1) LCCYZYZDZZRYBQ--临床出院中医诊断主证入院病情
,SF_BA_GETBASZDB(A.LSH000,'3',2,1) BASCYXYZYZD --病案室出院西医主要诊断
,SF_BA_GETBASZDB(A.LSH000,'3',0,1) BASCYXYZYZDJBBM --病案室出院西医主要诊断疾病编码
,SF_BA_GETBASZDB(A.LSH000,'3',1,1) BASCYXYZYZDRYBQ --病案室出院西医主要诊断入院病情
,SF_BA_GETBASZDB(A.LSH000,'a',2,1) BASCYZYZDZB --病案室出院中医诊断主病
,trim(SF_BA_GETBASZDB(A.LSH000,'a',0,1)) BASCYZYZDZBJBBM  --病案室出院中医诊断主病疾病编码
,SF_BA_GETBASZDB(A.LSH000,'a',1,1) BASCYZYZDZBRYBQ  --病案室出院中医诊断主病入院病情
,SF_BA_GETBASZDB(A.LSH000,'b',2,1) BASCYZYZDZZ --病案室出院中医诊断主证
,trim(SF_BA_GETBASZDB(A.LSH000,'b',0,1)) BASCYZYZDZZJBBM--病案室出院中医诊断主证疾病编码
,SF_BA_GETBASZDB(A.LSH000,'b',1,1) BASCYZYZDZZRYBQ--病案室出院中医诊断主证入院病情
,(select decode(zlxg00,'1','治愈','2','好转','3','未愈','4','死亡','4A','5','24小时死亡','6','其他','7','正常分娩','8','计划生育',zlxg00) from bq_baszdb where zyid00=A.LSH000 and zdlb00='3' and rownum=1) BASZYZDZLXG

,(select ZDMC00 from BQ_BASZDB ZD where ZD.ZYID00=A.LSH000 and ZD.ZDLB00='d' and rownum=1) MJZZYZD --门(急)诊诊断(中医诊断)
,(select ZDMC00 from BQ_BASZDB ZD where ZD.ZYID00=A.LSH000 and ZD.ZDLB00='d' and rownum=1) MJZZYZDJBBM --疾病编码
,(select ZDMC00 from BQ_BASZDB ZD where ZD.ZYID00=A.LSH000 and ZD.ZDLB00='1' and rownum=1) MJZXYZD --门(急)诊诊断(西医诊断)
,(select ZDMC00 from BQ_BASZDB ZD where ZD.ZYID00=A.LSH000 and ZD.ZDLB00='1' and rownum=1) MJZXYZDJBBM --疾病编码
,FY.ZYZFY0 ZFY, --总费用
FY.ZFJE00 ZFJE, --自付金额
FY.YBYLFW YLFWF, --一般医疗服务费
FY.ZYBZLZ BZLZF, --中医辨证论治费
FY.ZYBZHZ ZYBLZHZF,
FY.YBZLCZ ZLCZF, --一般治疗操作费
FY.HLF000 HLF, --护理费
FY.QTFY00 QTFY,--综合医疗服务类(1)其他费用
FY.BLZDF0 BLZDF, --病理诊断费
FY.SYSZDF ZDF, --实验室诊断费
FY.YXXZDF YXXZDF, --影像学诊断费
FY.LCZDXM LCZDXMF, --临床诊断项目费
FY.FSSZLF FSSZLXMF, --非手术治疗项目费
FY.LCWLZL ZLF, --临床物理治疗费
FY.SSZLF0 SSZLF, --手术治疗费
FY.MZF000 MZF, --麻醉费
FY.SSF000 SSF, --手术费
FY.KFF000 KFF, --康复费
FY.ZYZD00 ZYL_ZYZD,--中医诊断
FY.ZYZL00 ZYZL,--中医治疗
FY.ZYWZ00 ZYWZ,--中医外治
FY.ZYGS00 ZYGS,--中医骨伤
FY.ZCYJF0 ZCYJF,--针刺与灸法
FY.ZYTNZL ZYTNZL,--中医推拿治疗
FY.ZYGCZL ZYGCZL,--中医肛肠治疗
FY.ZYTSZL ZYTSZL,--中医特殊治疗
FY.ZYQT00 ZYQT,--中医其他
FY.ZYTSTP ZYTSTPJG,--中医特殊调配加工
FY.BZSSF0 BZSS,--辨证施膳
FY.XYF000 XYF, --西药费
FY.KJYWF0 KJYWF, --抗菌药物费
FY.ZCYF00 ZCYF, --中成药费
FY.ZYZJF0 ZYZJF,--医疗机构中药制剂费
FY.ZCYF10 ZCYF1, --中草药费
FY.XF0000 XF, --血费
FY.BDBLZP BDBLZPF, --白蛋白类制品费
FY.QDBLZP QDBLZPF, --球蛋白类制品费
FY.NXYZZP NXYZLZPF, --凝血因子类制品费
FY.XBYZZP XBYZLZPF, --细胞因子类制品费
FY.JCYCLF JCYYCLF, --检查用一次性医用材料费
FY.ZLYCLF YYCLF, --治疗用一次性医用材料费
FY.SSYCLF SSYCXCLF, --手术用一次性医用材料费
fy.SYSZDF,
FY.QTF000 QTF, --其他费
(SELECT DECODE(SFRJSS,'1','是','2','否','否') FROM BA_BRZYFY WHERE A.LSH000=ZYID00) SFRJSS,
(SELECT DECODE(SFRICU,'1','是','2','否','否') FROM BA_BRZYFY WHERE A.LSH000=ZYID00) SFRICU,
(SELECT ZZJHS0 FROM BA_BRZYFY WHERE A.LSH000=ZYID00) ZZJHS0,
(SELECT JICURQ FROM BA_BRZYFY WHERE A.LSH000=ZYID00) JICURQ,
(SELECT JICUSJ FROM BA_BRZYFY WHERE A.LSH000=ZYID00) JICUSJ,
(SELECT CICURQ FROM BA_BRZYFY WHERE A.LSH000=ZYID00) CICURQ,
(SELECT CICUSJ FROM BA_BRZYFY WHERE A.LSH000=ZYID00) CICUSJ,
(SELECT HXJYSJ FROM BA_BRZYFY WHERE A.LSH000=ZYID00) HXJYSJ
from BA_BRZYXX A
 join BA_SYFYB0 FY on A.LSH000=FY.ZYID00
;