create or replace view vw_jhemr_cdrg_insurance as select c.YBKH00 as CARDNO, --卡号 a.SFZHAO, --身份证号 a.XMING0, --姓名 a.FWWDMC, --医疗机构名称 a.YLFKFS, --医疗付费方式 b.YBZYH0 as ZYLSH0, --医保住院流水号 nvl(a.YY_ZYLSH0,a.ZYH000) as YY_ZYLSH0, --医院住院流水号 a.ZYCS00, --住院次数 + /* a.YYBAH0, --医院病案号*/ nvl(to_char(b.zyid00),a.YYBAH0) as YYBAH0, --医院病案号 a.XBIE00, --性别 decode(a.XBIE00,'1','男','2','女') as XBIEMC, --性别名称 + a.CSRQ00, --出生日期：年月日 decode(a.BRNL00,0,null,a.BRNL00) BRNL00, --年龄 decode(nvl(a.brnl00,0),0,decode(round((TO_DATE(b.RYRQ00||b.rysj00,'yyyy-mm-dd hh24:mi:ss')-to_date(b.CSRQ00||'00:00:01','yyyy-mm-dd hh24:mi:ss'))/31,2),0,0.01,round((TO_DATE(b.RYRQ00||b.rysj00,'yyyy-mm-dd hh24:mi:ss')- to_date(b.CSRQ00||'00:00:01','yyyy-mm-dd hh24:mi:ss'))/31,2)),'') bzyzsnl, --(年龄不足1周岁的)年龄(月) --decode(nvl(a.brnl00,0),0,round((TO_DATE(b.RYRQ00||b.rysj00,'yyyy-mm-dd hh24:mi:ss')-to_date(b.CSRQ00||'00:00:01','yyyy-mm-dd hh24:mi:ss'))/30,3),'') bzyzsnl, --(年龄不足1周岁的)年龄(月) -- a.BZYZSNL, --(年龄不足1周岁的)年龄(月) nvl(a.XSECSTZ,a.CSTZ00) as XSECSTZ, --新生儿出生体重(克) nvl(a.XSERYTZ,a.RYTZ00) as XSERYTZ, --新生儿入院体重(克） a.RYTJ00, --入院途径 decode(a.RYTJ00,'1','急诊','2','门诊','3','其他医疗机构转入','9','其他') as RYTJMC, nvl(a.RYSJ00,a.ZYRQ00) as RYSJ00, --入院时间：年月日 a.RYSJS0, --入院时间：时 a.RYKB00, --入院科别 a.RYBS00, --入院病区 + a.ZKSJ10, --转科时间1：年月日 a.ZKSJS1, --转科时间1：时 a.ZKKB00, --转科科别1 a.ZKSJ20, --转科时间2：年月日 a.ZKSJS2, --转科时间2：时 a.ZKKB20, --转科科别2 a.ZKSJ30, --转科时间3：年月日 a.ZKSJS3, --转科时间3：时 a.ZKKB30, --转科科别3 a.RZCCU0, --入住重症监护室CCU（小时） a.RZNICU, --入住重症监护室NICU（小时） a.RZEICU, --入住重症监护室EICU（小时） a.RZSICU, --入住重症监护室SICU（小时） a.RZPICU, --入住重症监护室PICU（小时） a.RZQTJHS, --入住重症监护室其他（小时） a.QJCS00, --抢救（次） a.QJCGCS, --抢救成功（次） nvl(a.HXJSYSJ,a.HXJSY0) as HXJSYSJ, --呼吸机使用时间（小时） --nvl(a.CYSJ00,a.CYRQ00) as CYSJ00, --出院时间：年月日 --b.cydjrq CYSJ00, --出院时间：年月日 decode(substr(b.BRZT00,1,1),'2',nvl(e.YCRQ00,b.CYDJRQ),b.CYDJRQ) as CYSJ00, a.CYSJS0, --出院时间：时 a.CYKB00, --出院科别 a.CYBS00, --出院病区 + a.LYFS00, --离院方式 decode(a.LYFS00,'1','医嘱离院','2','医嘱转院','3','医嘱转社区卫生服务机构/乡镇卫生院','4','非医嘱离院','5','死亡','9','其他') as LYFSMC, nvl(a.SJZYTS,a.ZYTS00) as SJZYTS, --实际住院(天) a.SJHL00, --三级护理（天） a.EJHL00, --二级护理（天） a.YJHL00, --一级护理（天） a.TJHL00, --特级护理（天） (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00) as ZFY000, --住院费用(元)：总费用 (select nvl(sum(ZFJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00) as ZFJE00, --自付金额 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (101)) as YLFUF0, --综合医疗服务类：(1)一般医疗服务费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (102)) as ZLCZF0, --综合医疗服务类：(2)一般治疗操作费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (103)) as HLF000, --综合医疗服务类：(3)护理费 0 as ZCF000, --综合医疗服务类：(4)诊察费 0 as PTCWF0, --综合医疗服务类：(5)普通床位费 0 as ZZJHCWF, --综合医疗服务类：(6)重症监护床位费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (104)) as QTFY00, --综合医疗服务类：(7)其他费用 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (105)) as BLZDF0, --诊断类：(8)病理诊断费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (106)) as SYSZDF, --诊断类：(9)实验室诊断费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (107)) as YXXZDF, --诊断类：(10)影像学诊断费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (108)) as LCZDXMF, --诊断类：(11)临床诊断项目费 /* (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (109)) as FSSZLXMF, --治疗类：(12)非手术治疗项目费 2019.06.28*/ (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (109,201)) as FSSZLXMF, --治疗类：(12)非手术治疗项目费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (201)) as WLZLF0, --临床物理治疗费 /* (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (110)) as SSZLF0, --治疗类：(13)手术治疗费 2019.06.28*/ (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (110,211,212)) as SSZLF0, --治疗类：(13)手术治疗费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (211,110)) as MZF000, --麻醉费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (212)) as SSF000, --手术费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (111)) as KFF000, --康复类：(14)康复费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (112,303,306)) as ZYZLF0, --中医类:(15)中医治疗费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (113)) as XYF000, --西药类:(16)西药费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (113) and XMBH00 in (select YPNM00 from BM_YD0000 where KSSJB0>0)) as KJYWF0, --抗菌药物费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (114)) as ZCYF00, --中药类:(17)中成药费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (115)) as ZCYF10, --中药类:(18)中草药费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (116)) as XF0000, --血液和血液制品类:(19)血费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (117)) as BDBLZPF, --血液和血液制品类:(20)白蛋白类制品费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (118)) as QDBLZPF, --血液和血液制品类:(21)球蛋白类制品费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (119)) as NXYZLZPF, --血液和血液制品类:(22)凝血因子类制品费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (120)) as XBYZLZPF, --血液和血液制品类:(23)细胞因子类制品费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (121)) as HCYYCLF, --耗材类:(24)检查用一次性医用材料费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (122)) as YYCLF0, --耗材类:(25)治疗用一次性医用材料费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (123)) as YCXYYCLF, --耗材类:(26)手术用一次性医用材料费 (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and BAXMID in (124)) as QTF000, --其他类：(27)其他费 a.SYMC00, --临床诊断术语（主要诊断术语） a.SYBM00, --临床诊断术语编码（主要诊断术语编码） a.SYMC01, --临床诊断术语1 a.SYBM01, --临床诊断术语1编码 a.SFZY01, --是否治疗1 a.SYMC02, --临床诊断术语2 a.SYBM02, --临床诊断术语2编码 a.SFZY02, --是否治疗2 a.SYMC03, --临床诊断术语3 a.SYBM03, --临床诊断术语3编码 a.SFZY03, --是否治疗3 a.SYMC04, --临床诊断术语4 a.SYBM04, --临床诊断术语4编码 a.SFZY04, --是否治疗4 a.SYMC05, --临床诊断术语5 a.SYBM05, --临床诊断术语5编码 a.SFZY05, --是否治疗5 a.SYMC06, --临床诊断术语6 a.SYBM06, --临床诊断术语6编码 a.SFZY06, --是否治疗6 a.SYMC07, --临床诊断术语7 a.SYBM07, --临床诊断术语7编码 a.SFZY07, --是否治疗7 a.SYMC08, --临床诊断术语8 a.SYBM08, --临床诊断术语8编码 a.SFZY08, --是否治疗8 a.SYMC09, --临床诊断术语9 a.SYBM09, --临床诊断术语9编码 a.SFZY09, --是否治疗9 a.SYMC10, --临床诊断术语10 a.SYBM10, --临床诊断术语10编码 a.SFZY10, --是否治疗10 a.SYMC11, --临床诊断术语11 a.SYBM11, --临床诊断术语11编码 a.SFZY11, --是否治疗11 a.SYMC12, --临床诊断术语12 a.SYBM12, --临床诊断术语12编码 a.SFZY12, --是否治疗12 a.SYMC13, --临床诊断术语13 a.SYBM13, --临床诊断术语13编码 a.SFZY13, --是否治疗13 a.SYMC14, --临床诊断术语14 a.SYBM14, --临床诊断术语14编码 a.SFZY14, --是否治疗14 a.SYMC15, --临床诊断术语15 a.SYBM15, --临床诊断术语15编码 a.SFZY15, --是否治疗15 a.CCHIBM1, --CCHI编码1 a.CCHIMC1, --CCHI名称1 a.CCHIBM2, --CCHI编码2 a.CCHIMC2, --CCHI名称2 a.CCHIBM3, --CCHI编码3 a.CCHIMC3, --CCHI名称3 a.CCHIBM4, --CCHI编码4 a.CCHIMC4, --CCHI名称4 a.CCHIBM5, --CCHI编码5 a.CCHIMC5, --CCHI名称5 a.CCHIBM6, --CCHI编码6 a.CCHIMC6, --CCHI名称6 a.CCHIBM7, --CCHI编码7 a.CCHIMC7, --CCHI名称7 a.CCHIBM8, --CCHI编码8 a.CCHIMC8, --CCHI名称8 a.SCBLLX, --病例类型 (select MC0000 from BM_TYZD00 where ZDMC00='CDRG病历类型' and BH0000 =a.SCBLLX) as SCBLMC, --C-DRG结算病历类型名称 a.LXRDH0, --联系人电话 a.BRID00, --病人ID a.ZYID00, --住院ID b.BRZT00, --病人住院状态 decode(substr(b.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, nvl(b.CDRGFS,a.CDRGFS) as CDRGFS, --CDRG发送状态 0未发送 1已发送，默认0 /* decode(nvl(a.CDRGFS,b.CDRGFS),'1','已发送','0','未发送','未知') as FSBSMC, --CDGR发送状态名称 2019.07.14修改*/ decode(nvl(b.CDRGFS,a.CDRGFS),'1','已发送','0','未发送','未知') as FSBSMC, --CDGR发送状态名称 nvl(b.DRGFSR,a.DRGFSR) DRGFSR, --DRG发送人id (select ZWXM00 from BM_YGBM00 where YGBH00 = nvl(b.DRGFSR,a.DRGFSR)) as FSRXM0, nvl(b.RGFSSJ,a.RGFSSJ) RGFSSJ, --CDRG发送时间 b.sjcyrq||' '||b.sjcysj as cydjrqsj, a.ZHXGSJ, --数据最后修改时间（写入中间表或调用存储过程的时间） a.CBCWF0, --超标床位费 /*(select nvl(sum(hjje00),0) from zy_fymx00 where zyid00=a.ZYID00 and xmbh00 in (select sfxmid from BM_YBCBDY where sfxmid<>20059)) cwhcf0,*/ (select nvl(sum(HJJE00),0) from ZY_FYMX00 where ZYID00=a.ZYID00 and XMBH00 in (select SFXMID from BM_YBCBDY where FBBH00=b.FBBH00 and YBZXLB=b.YBZXLB)) as CWHCF0, --a.CWHCF0, --除外耗材费 b.DQBQ00, --病人当前病区 d.YYID00 as BQYYID, --病人病区所属医院ID (select sign(count(*)) from ZY_JZB000 where ZYID00 = a.ZYID00 and JZZT00 ='0' and HJJE00>0) as SFJS00, --是否结算 0未结算 1已结算 (select decode(sign(count(*)),1,'已结算','未结算') from ZY_JZB000 where ZYID00 = a.ZYID00 and JZZT00 ='0' and HJJE00>0) as SFJSMC, --是否结算 0未结算 1已结算 (select nvl(YBSM09,'') from ZY_JZB000 where ZYID00 = a.ZYID00 and JSLX00='1' and CXJZDH=0 and JZZT00 ='0') as CDRGZB, --CDRG组别 (select nvl(sum(TCJJZF),0) from ZY_JZB000 where ZYID00 = a.ZYID00) as JSTCJE, --结算统筹支付金额 (select nvl(sum(ZFJE00),0) from ZY_JZB000 where ZYID00 = a.ZYID00) as JSZFJE, --结算自付金额 (select nvl(sum(GRZHZF),0) from ZY_JZB000 where ZYID00 = a.ZYID00) as GRZHZF, --个人账户支付 (select nvl(sum(YBYL05),0) from ZY_JZB000 where ZYID00 = a.ZYID00) as YBYL05, --医院支付 (select decode(b.FBBH00,1,0,sum(nvl(YBYL39,0))) from ZY_JZB000 where ZYID00 = a.ZYID00) as ZBBZFY, --组别标准费用 substr(SF_XT_GETTIMEINTERVAL(b.SJCYRQ||' '||b.SJCYSJ,b.RGFSSJ,-876000,0),1,10) as CQQSC0, --是否出区前上传CDRG （CDRG上传时间 < 出科登记时间，则是，否则否） substr(SF_XT_GETTIMEINTERVAL(b.SJCYRQ||' '||b.SJCYSJ,b.RGFSSJ,0,6),1,10) as CQH6XS --是否出区后6小时内上传CDRG （出科登记时间 < CDRG上传时间 < 出科登记时间+6小时，则是，否则否） from JHEMR_MEDICAL_INSURANCE a,ZY_BRXXB0 b,BM_BRXXB0 c,BM_BMBM00 d,ZY_YCKCZB e where a.ZYID00 = b.ZYID00(+) and b.BRID00 = c.BRID00 and b.DQBQ00 = d.BMBH00 and a.ZYID00 = e.ZYID00(+) and b.ybzyh0!='0' and b.sjryrq>='20171201' -- and b.cyrq00 is null and (b.brzt00<>'20' or e.ycrq00 is not null ) --AND ( B.CYDJRQ!='20991231'OR b.sjcyrq!='20991231') --AND b.sjcyrq!='20991231' and b.dbzbm0 is null and b.ybzyh0 not like 'YD%';