-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_BRXXB0 -- Generated 2025-05-19 12:08:15 from SD_HOSPITAL@GLGQYY CREATE OR REPLACE VIEW vw_zy_brxxb0 ( zyid00, brid00, brblh0, ybkh00, zyybkh, ybid00, zyybid, icztmc, brlxdh, brztqk, mrn000, zyh000, brrycs, ybzyh0, ybzycs, xm0000, xb0000, csrq00, csrq00_fm, brnl00, fbbh00, fbmc00, lbbh00, ryrq00, rysj00, ryrqsj, ryczy0, czyxm0, ryzd00, ryzdmc, rysqk0, rylb00, zymd00, ryfs00, rybq00, rybqmc, rzks00, rzksmc, rycwh0, pxcwh0, dqbq00, dqbqmc, bqyyid, bqyymc, bqxhly, dqks00, dqksmc, yybmdz, rcyfh0, cyrq00, cysj00, cyrqsj, cyzdmc, cyczy0, cyygxm, zzys00, zzysxm, zrys00, zrysxm, sxys00, sxysxm, hljbid, hljbmc, brzt00, brztmc, cydjrq, cydjsj, djrqsj, szys00, szysxm, yblb00, ybmc00, ybbhsb, sfxnh0, nhlbmc, ybzxlb, ybbrlb, ssmc00, shjg00, cyxj00, gsdjh0, ybqh00, yksh00, zkjcbz, sjcyrq, cyjsrq, cyjssj, sjryrq, sjrysj, jzrqsj, cslx00, cslxmc, shbzh0, fyhjje, yszid0, dqljid, yylbid, yykbrq, qkdjbh, dblb00, dblbmc, blcjrq, cyczrq, sjcysj, zghs00, zghsxm, zrhlz0, zhzye0, brlbmc, fbxx00, pysm00, wbsm00, sfyxjs, sqid00, sqmc00, cwzgbz, ewm001, fmbqbh, syfmrq, hyts00, zrzybz, rylbbz, dbzbm0, dbzmc0, dbzbz0, ffbzbm, ffbzbz, tybzbz, ybjhbz, jshdzt, hdztmc, hdrq00, hdsj00, hdr000, hdrxm0, wjsje0, fyzje0, yjjzye, yjkyje, bryjze, scbllx, scblmc, cdrgfs, cdfsmc, ylzzdh, chsfdm, chsfmc, chsfxx, chsqdm, chsqmc, chsqxx, chxqdm, chxqmc, chxqxx, zyqtxx, zylsh0, sfjzfp, sfyljz, sffpmc, fpjzzt, yljzzt, hcdbbm, cyzd00, fpbzbm, fpbzmc, qrurl0, zytsbz, sfzz00, sfzzmc, yljzsf, tssxmc, tesl00, sysslb, jhsyss, hsjcqk, hsjcsm, djlsh0, byzd01, fyshbz, fyshmc, ybryrq, ybrysj, ybcyrq, ybcysj, ckts00, syssmc, ffbzmc, bzsfje, dbzde0, qtjhbz, bke413, bka561, brxm00, bae465, baemc0, gjbzbm, gjbzmc, bz0000, ybsdlb, med_type, psn_no, aae140, fpsc_no, matn_type, birctrl_type, latechb_flag, geso_val, fetts, fetus_cnt, pret_flag, birctrl_matn_date, cyzzd0, cyzzdm, trum_flag, rel_ttp_flag, mdtrt_grp_type, rjssbz, insuplc_admdvs, card_sn, psn_cert_type, psn_cert_name, certno, psn_type_code, psn_type_name, prfs_code, prfs_name, patn_rlts_code, patn_rlts_name, coner_name, coner_tel, coner_addr, dqzxmc, sqybk0, sqybmc, wphdj0, wphmc0, discreditstate, sxrybz, hkxxdz, gmjl00 ) AS select a.ZYID00, a.BRID00, b.BRBLH0, decode(nvl(a.YBKH00,'0'),'0',b.YBKH00,a.YBKH00) YBKH00, a.YBKH00 as ZYYBKH, --住院医保卡号 decode(nvl(nvl(a.YBID00,a.SHBZH0),'0'),'0',b.YBID00,nvl(a.YBID00,a.SHBZH0)) YBID00, a.YBID00 as ZYYBID, --住院医保id b.ICZTMC, b.BRLXDH, nvl(a.psn_type_name,b.BRZTQK) as BRZTQK, a.MRN000, a.ZYH000, a.BRRYCS, a.YBZYH0, a.YBZYCS, a.XM0000, a.XB0000, a.CSRQ00, decode(a.CSRQ00,null,'',substr(a.CSRQ00,1,4)||'.'||substr(a.CSRQ00,5,2)||'.'||substr(a.CSRQ00,7,2)) as CSRQ00_FM,--格式化出生日期 substr(SF_XT_CSRQTONL_EX(b.BRCSRQ,b.BRCSSJ),1,10) as BRNL00 , a.FBBH00, (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00, --费别名称 a.LBBH00, a.RYRQ00, a.RYSJ00, a.RYRQ00||' '||a.RYSJ00 as RYRQSJ, a.RYCZY0, a.CZYXM0, (select substrb(SF_ZY_BRRYCYZDXX(a.ZYID00,0,0),1,20) from dual) as RYZD00, --入院诊断编码 (select substrb(SF_ZY_BRRYCYZDXX(a.ZYID00,0,1),1,100) from dual) as RYZDMC, --入院诊断名称 a.RYSQK0, a.RYLB00, a.ZYMD00, a.RYFS00, a.RYBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.RYBQ00) as RYBQMC, --入院病区名称 a.RZKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.RZKS00) as RZKSMC, --入住科室名称 a.RYCWH0, substr(SF_XT_GETPXCWH(a.RYCWH0),1,10) as PXCWH0 , --排序用床位号 a.DQBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC, --当前病区名称 (select YYID00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as BQYYID, --当前病区所在分院ID (select YYQC00 from XT_YYXX00 where YYID00=(select YYID00 from BM_BMBM00 where BMBH00 = a.DQBQ00)) as BQYYMC, --当前病区所在医院分院名称 (select nvl(max(ZYXHLY),0) from XT_YYXX00 where YYID00=(select YYID00 from BM_BMBM00 where BMBH00 = a.DQBQ00)) as BQXHLY, --当前病区所在院区住院序号来源 a.DQKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC, --当前科室名称 (select YYBMDZ from BM_BMBM00 where BMBH00 = a.DQBQ00) as YYBMDZ, --入住科室楼层+朝向 a.RCYFH0, a.CYRQ00, a.CYSJ00, a.CYRQ00||' '||a.CYSJ00 as CYRQSJ, (select substrb(SF_ZY_BRRYCYZDXX(a.ZYID00,1,1),1,100) from dual) as CYZDMC, --出院诊断 a.CYCZY0, a.CYYGXM, a.ZZYS00, a.ZZYSXM, a.ZRYS00, a.ZRYSXM, a.SXYS00, a.SXYSXM, a.HLJBID, (select ZLXMJC from BM_ZLZD00 where ZLXMID = a.HLJBID and rownum = 1) as HLJBMC, --护理级别名称 a.BRZT00, decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, a.CYDJRQ, a.CYDJSJ, a.CYDJRQ||' '||a.CYDJSJ as DJRQSJ, --出院登记日期时间 a.SZYS00, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM, --收治医生姓名 a.YBLB00, c.YBMC00, --医保名称 nvl(c.YBBHSB,'N') as YBBHSB, --医保包含商保 nvl(c.SFXNH0,'0') as SFXNH0, --是否新农合 decode(c.SFXNH0,'0','大中心','1','农合','2','独立分中心','3','异地医保','4','异地农合','未分类') as NHLBMC , --农合类别名称 a.YBZXLB, a.YBBRLB, a.SSMC00, a.SHJG00, a.CYXJ00, a.GSDJH0, a.YBQH00, a.YKSH00, a.ZKJCBZ, a.SJCYRQ, a.CYJSRQ, a.CYJSSJ, a.SJRYRQ, a.SJRYSJ, a.SJRYRQ||' '||a.SJRYSJ as JZRQSJ, a.CSLX00, --农合传送类型,1实时结算,0传明细 decode(a.CSLX00,'0','传明细','1','实时结算','') as CSLXMC, nvl(b.BRZJBH,a.SHBZH0) as SHBZH0, a.FYHJJE, a.YSZID0, a.DQLJID, a.YYLBID, a.YYKBRQ, a.QKDJBH, a.DBLB00, a.DBLBMC, a.BLCJRQ, a.CYCZRQ, a.SJCYSJ, a.ZGHS00, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGHS00) as ZGHSXM, --主管护士姓名 a.ZRHLZ0, --责任护理组 (select nvl(sum(ZYE000-TYJE00),0) from ZY_BRZHXX where ZYID00=a.ZYID00 and SFDJ00='0' ) as ZHZYE0, --账户余额 d.BRLBMC, decode(c.YBJGJC,d.BRLBJC,c.YBJGJC,c.YBJGJC||'('||d.BRLBJC||')') as FBXX00, --费别信息 nvl(a.PYSM00,b.PYSM00) as PYSM00, --拼音首码 nvl(a.WBSM00,b.WBSM00) as WBSM00, --五笔首码 nvl(SFYXJS,'1') as SFYXJS, --是否允许结算,‘0’护士不允许 ‘1’允许 ‘2’允许患者提前结算 a.SQID00, --社区服务中心ID (select SQMC00 from BM_SQZD00 where SQID00=a.SQID00) as SQMC00, --社区服务中心名称 d.CWZGBZ, --床位最高标准 a.ZYID00 as EWM001, --碗带二维码 a.FMBQBH, --生育分娩病情编号 nvl((select max(YECSRQ) from BQ_JSXX00 where ZYID00=a.ZYID00),a.SYFMRQ) SYFMRQ, --生育分娩日期 amc020 (select decode(nvl(HYZS00,0),0,0,nvl(HYZS00,0)*7+nvl(SYTS00,0)) from BQ_JSXX00 where ZYID00=a.ZYID00 and rownum=1) as HYTS00, --怀孕天数 bmc041 a.ZRZYBZ, --择日住院标志: 0非择日,1择日住院,2择日转正式住院 c.RYLBBZ, --入院类别标志：1：传值 01，02，03..;2：传普通，家庭病床 3和空:人民医院模式，默认为3 a.DBZBM0, --单病种编码 a.DBZMC0, --单病种名称 decode(a.DBZBM0,null,'','单') as DBZBZ0, --单病种病人标识 a.FFBZBM, --付费病种编码 decode(a.FFBZBM,null,'','单') as FFBZBZ, --付费单病种病人标识 decode(nvl(a.FFBZBM,a.DBZBM0),null,'','单') as TYBZBZ, --通用单病种病人标识 a.YBJHBZ, --医保收费交互标识 0未发送 1已发送未接收 2已接收 a.JSHDZT, --出院结算核对状态 0未核对 1已核对；默认0 decode(a.JSHDZT,'0','未核对','1','已核对') as HDZTMC, --出院结算核对状态名称 a.HDRQ00, --核对日期 a.HDSJ00, --核对时间 a.HDR000, --核对人 (select ZWXM00 from BM_YGBM00 where YGBH00 = a.HDR000) as HDRXM0, --核对人姓名 (select nvl(max(WJSJE0),0) from ZY_BRZHXX where ZYID00 =a.ZYID00) as WJSJE0, --未结算费用金额 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00 =a.ZYID00) as FYZJE0, --费用总金额 (select nvl(max(ZYE000),0) from ZY_BRZHXX where ZYID00 =a.ZYID00) as YJJZYE, --预交金总余额 SF_ZY_KYJE00(a.ZYID00,0) as YJKYJE, --可用预交金金额 (select nvl(sum(JFJE00),0) from ZY_BRJFB0 where ZYID00 =a.ZYID00 and PLBH00 in (4,8) ) as BRYJZE, --病人预交总金额 a.SCBLLX, --C-DRG病历类型 (select MC0000 from BM_TYZD00 where ZDMC00='CDRG病历类型' and BH0000 =a.SCBLLX) as SCBLMC, --C-DRG病历类型名称 a.CDRGFS, --CDRG发送状态 0未发送 1已发送 decode(a.FBBH00,3,decode(a.CDRGFS,'1','CDRG已上传','CDRG未上传'),'') as CDFSMC, --CDRG发送状态名称 a.YLZZDH, --新农合跨省结算--医疗转诊单号 SF_XT_RETURNINDEXEH(a.CHSFXX,'|', 1) as CHSFDM, --参合省代码 SF_XT_RETURNINDEXEH(a.CHSFXX,'|', 2) as CHSFMC, --参合省名称 a.CHSFXX, --参合省代码和名称 SF_XT_RETURNINDEXEH(a.CHSQXX,'|', 1) as CHSQDM, --参合市代码 SF_XT_RETURNINDEXEH(a.CHSQXX,'|', 2) as CHSQMC, --参合市名称 a.CHSQXX, --参合市代码和名称 SF_XT_RETURNINDEXEH(a.CHXQXX,'|', 1) as CHXQDM, --参合县代码 SF_XT_RETURNINDEXEH(a.CHXQXX,'|', 2) as CHXQMC, --参合县名称 a.CHXQXX, --参合县代码和名称 substrb(a.ZYQTXX,1,200) as ZYQTXX, --新农合跨省结算--住院其他信息(步骤状态) a.ZYLSH0, --新农合跨省结算--住院流水号 a.SFJZFP, --是否精准扶贫 1：是，0：否 a.SFYLJZ, --是否医疗救助 Y或1: 是, N或0: 否 decode(a.SFJZFP,'1','是','0','否') as SFFPMC, --是否精准扶贫 1是0否 decode(a.SFJZFP,'1','精准扶贫','') as FPJZZT, --扶贫救助病人状态 decode(upper(a.SFYLJZ),'1','医疗救助','Y','医疗救助','') as YLJZZT, --医疗救助状态 a.HCDBBM, --单病种另行收费耗材打包编码 (select substrb(SF_ZY_BRRYCYZDXX(a.ZYID00,1,0),1,20) from dual) as CYZD00, --出院诊断编码 a.FPBZBM, -- 扶贫病种编码 a.FPBZMC, -- 扶贫病种名称 (select nvl(max(QRURL0), '') from BQ_BRBSM0 where ZYID00 = a.ZYID00) QRURL0, --腕带床头卡 (select BZBM00 from ZY_BRBZB0 where ZYID00 = a.ZYID00 and BZLB00 = '01' and rownum=1) as ZYTSBZ, --住院特殊病种编号 nvl(a.SFZZ00,'0') as SFZZ00, --是否转诊 0否 1是 decode(a.SFZZ00,'1','是','否') as SFZZMC, --是否转诊名称 b.YLJZSF, --医疗救助身份 b.TSSXMC, --特殊属性名称 substr(SF_ZY_GETTESL(a.ZYID00,0),1,20) as TESL00, --胎儿数量 amc028 (select FMFSBH from BQ_JSXX00 where ZYID00=a.ZYID00 and rownum=1) as SYSSLB, --手术生育类别 amc026 BM_TYZD00.ZDMC00="手术生育类别" (select JHSYSS from BQ_JSXX00 where ZYID00=a.ZYID00 and rownum=1) as JHSYSS, --计划生育手术类别 amc029 BM_TYZD00.ZDMC00="计划生育手术类别" (select HSJCQK from ZY_HSJCQK where ZYID00=a.ZYID00) as HSJCQK, --核酸检测情况： 1.急危重，未查核酸 2.急危重，核酸已查待报 decode((select HSJCQK from ZY_HSJCQK where ZYID00=a.ZYID00),'1','急危重，未查核酸','2','急危重，核酸已查待报') as HSJCSM, --核酸检测情说明 (select DJLSH0 from ZY_GSSBDJ where ZYID00 = a.ZYID00 and rownum=1) as DJLSH0, --工伤明细流水号 bke297 nvl(b.BYZD01,'否') as BYZD01, --是否先诊疗后付费对象（是否城乡特困人员），取值是或否 nvl(a.FYSHBZ,'0') as FYSHBZ, --费用审核标识 0未审核1已审核 decode(a.FYSHBZ,'1','费用已审核','费用未审核') as FYSHMC, --费用审核名称 a.YBRYRQ, --医保入院日期 a.YBRYSJ, --医保入院时间 a.YBCYRQ, --医保出院日期 a.YBCYSJ, --医保出院时间 SF_XT_GETDATEDIF(a.CYDJRQ,to_char(sysdate,'yyyymmdd'),'D') as CKTS00, --出科日期天数（相对于当天） (select max(FMFS00) from BQ_JSXX00 where ZYID00=a.ZYID00 and rownum=1) as SYSSMC, --手术生育名称 amc026 BM_TYZD00.ZDMC00="手术生育类别" (select BZMC00 from BM_BZSFBZ where FBBH00=a.FBBH00 and YBZXLB=a.YBZXLB and FFBZBM=a.FFBZBM and rownum=1) FFBZMC, --付费病种名称(省属单病种) (select BZSFJE from BM_BZSFBZ where FBBH00=a.FBBH00 and YBZXLB=a.YBZXLB and FFBZBM=a.FFBZBM and rownum=1) BZSFJE, --付费病种收费金额(省属单病种) (select YBHDJE from BM_DBZDEB where BRLBMC=decode(a.FBBH00,1,'自费',3,'医保','') and YBZXLB=a.YBZXLB and DBZBM0=a.DBZBM0 and rownum=1) DBZDE0, --单病种定额(政和模式) a.QTJHBZ, --千天计划标志 nvl(a.BKE413,'N') BKE413, --是否新生儿（Y是/N否） a.BKA561, --新生儿入院类型 a.XM0000 as BRXM00, --病人姓名 SF_XT_GETDBZGJBZBMMC(a.FFBZBM,a.DBZBM0,a.FBBH00,a.YBZXLB,'1') as BAE465, -- SF_XT_GETDBZGJBZBMMC(a.FFBZBM,a.DBZBM0,a.FBBH00,a.YBZXLB,'2') as BAEMC0, -- nvl(SF_XT_GETDBZGJBZBMMC(a.FFBZBM,a.DBZBM0,a.FBBH00,a.YBZXLB,'1'),a.FFBZBM) as GJBZBM, nvl(SF_XT_GETDBZGJBZBMMC(a.FFBZBM,a.DBZBM0,a.FBBH00,a.YBZXLB,'2'),(select BZMC00 from BM_BZSFBZ where FBBH00=a.FBBH00 and YBZXLB=a.YBZXLB and FFBZBM=a.FFBZBM and rownum=1)) GJBZMC, substrb(b.BZ0000,1,200) as BZ0000, --备注 nvl(c.YBSDLB,0) as YBSDLB, --医保属地类别 nvl(a.MED_TYPE,SF_XT_GETGJYBZDByMC0000('med_type',a.RYLB00,'1')) as MED_TYPE, --医疗类别 nvl(a.PSN_NO,b.PSN_NO) as PSN_NO, --人员编号 nvl(a.AAE140,b.AAE140) as AAE140, --险种类型 a.fpsc_no, --计划生育服务证号 a.SYSSLB as matn_type, --生育手术类别 a.JHSYSS as birctrl_type, --计划生育手术类别 a.latechb_flag, --晚育标志 a.geso_val, --孕周数 a.fetts, --胎次 a.TESL00 as fetus_cnt, --胎儿数 a.pret_flag, --早产标志 a.SYFMRQ as birctrl_matn_date, --计划生育手术或生育日期 (select ZDM000 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='3' and rownum=1) as CYZZD0, --出院主诊断编码 (select substr(ZDMC00,1,20) from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='3' and rownum=1) as CYZZDM, --出院主诊断名称 a.trum_flag, --外伤标志 a.rel_ttp_flag, --涉及第三方标志 a.mdtrt_grp_type, --就诊人群类型 a.RJSSBZ, --日间手术标志 a.insuplc_admdvs, --[国家医保]参保行政区划 a.card_sn, --[国家医保]医保卡识别码 a.psn_cert_type, --[国家医保]人员证件类型 a.psn_cert_name, --[国家医保]人员证件名称 a.certno, --[国家医保]参保证件号 a.psn_type_code, --[国家医保]人员类型 a.psn_type_name, --[国家医保]人员类型名称 a.prfs_code, --[国家医保]职业编号 a.prfs_name, --[国家医保]职业名称 a.patn_rlts_code, --[国家医保]联系人关系编号 a.patn_rlts_name, --[国家医保]联系人关系名称 a.coner_name, --联系人姓名 a.coner_tel, --联系人电话 a.coner_addr, --联系人地址 (select FZXMC0||'('||trim(DQMC00)||')' from BM_DQFZX0 where XZQH00 = nvl(nvl(a.insuplc_admdvs,b.XZQH00),c.XZQH00)) as DQZXMC, --地区分中心名称 (select max(SQYBK0) from ZY_BRQTXX where ZYID00=a.ZYID00 and rownum=1) as SQYBK0, --是否收取医保卡, 0未收取1收取 decode((select max(SQYBK0) from ZY_BRQTXX where ZYID00=a.ZYID00 and rownum=1),'1','收取','0','未收取','不确定') as SQYBMC, --是否收取医保卡状态名称, 空不确定0未收取1收取 a.WPHDJ0, --无陪护等级 (select max(DJMC00) from BM_WPHDJB where DJID00=a.WPHDJ0) as WPHMC0, --无陪护等级名称 (select DiscreditState from BM_BRQTXX where BRID00=a.BRID00 and rownum=1) as DiscreditState, --失信状态(空值:非失信人员 0:欠费 1:已缴清 2:撤销) decode((select DiscreditState from BM_BRQTXX where BRID00=a.BRID00 and rownum=1),'0','是','否') as SXRYBZ, --失信人员标志, DiscreditState='0'时“是”， 否则“否” (select max(HKXXDZ) from BM_BRQTXX where BRID00=a.BRID00 and rownum=1) as HKXXDZ, --户口详细地址 SF_XT_BRGMJL_DZBL('0',a.BRID00,null,null) GMJL00 --过敏记录 from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c,BM_YBBRLB d where a.BRID00+0 = b.BRID00 and a.YBLB00 = c.YBLB00 and a.FBBH00 = c.FBBH00 and a.FBBH00 = d.FBBH00 and a.YBBRLB = d.YBBRLB and c.YBZXLB = d.YBZXLB and a.RYRQ00 >= '19000101' / -- End of DDL Script for View SD_HOSPITAL.VW_ZY_BRXXB0