create or replace view vw_jk_yb4101ajsqdxx as
select
'' psn_no, --人员编号
A.ZYH000 zyid00,--HIS住院唯一ID
A.LSH000 mdtrt_id,--就诊ID
(select h.YBKH00 from  zy_brxxb0 h where h.ZYID00 = a.LSH000 and rownum <=1 ) hi_no,--医保编号
A.ZYH000 medcasno,--病案号
'' dcla_time,--申报时间
nvl((select UPPER(GJDM00) from XT_GJ0000  where MC0000=A.GJ0000),'CHN') ntly,--国籍
decode(A.ZY0000,'国家公务员','11','专业技术人员','13','职员','17','企业管理人员','21','工人','24','农民','27','学生','31','现役军人','37','自由职业者','51','个体经营者','54','无业人员','70','退(离)休人员','80','其他','90','90') prfs,--职业
substrb(A.XZZXC0,1,49) curr_addr,--现住址
replace(A.GZDW00,'\','') emp_name,--单位名称
replace(A.GZDW00,'\','') emp_addr,--单位地址
A.GZDH00 emp_tel,--单位电话
A.JTYB00 poscode,--邮编
A.LXRXM0 coner_name,--联系人姓名
decode(A.LXRGX0,'本人或户主','01','配偶','10','子','20','女','30','孙子、孙女或外孙子、外孙女','40','外孙女','40','孙女','40','孙子','40','父母','50','祖父母或外祖父母','60','祖父母','60','兄、弟、姐、妹','70','兄、弟','70','兄妹','70','姐妹','70','姐、妹','70','外祖父母','60','其他','80','80') patn_rlts,--与患者关系
nvl(substrb(A.LXRDZ0,1,49),'-') coner_addr,--联系人地址
A.LXRDH0 coner_tel,--联系人电话
'' nwb_adm_type,--新生儿入院类型
decode(A.YECSTZ,'0','',A.YECSTZ) nwb_bir_wt,--新生儿出生体重
decode(A.YERYTZ,'0','',a.YERYTZ) nwb_adm_wt,--新生儿入院体重
decode(A.YECSTZ,'0','',A.YECSTZ) nwbbirwt,--新生儿出生体重
decode(A.YERYTZ,'0','',a.YERYTZ) nwbadmwt,--新生儿入院体重
decode(A.YECSTZ,'0','',A.YECSTZ) mul_nwb_bir_wt,--多新生儿出生体重
decode(A.YERYTZ,'0','',a.YERYTZ) mul_nwb_adm_wt,--多新生儿入院体重
'' opsp_diag_caty,--门诊慢特病诊断科别
'' opsp_mdtrt_date,--门诊慢特病就诊日期
DECODE(A.RYTJ00,'1','1','2','2','3','3','9','9') adm_way,--入院途径
--nvl('12','') trt_type,--治疗类别
--治疗类别 1.中医(1.1 中医 1.2民族医) 2.中西医 3.西医     国家版治疗类别 21 中医 22民族医 30.中西医 10.西医
--decode(A.ZLLB00,'1','21','2','30','3','10','10') trt_type,--治疗类别
nvl((case when (exists (select 1 from BA_SYFYB0 b where a.lsh000=b.zyid00 and (b.zcyf10>0 or b.zyzl00>0)))  then '30' when  (exists (select 1 from BQ_BASZDB c where c.zyid00=a.lsh000 and c.zdlb00 in ('a') )) then '30' else '10' end),'10') trt_type,--治疗类别
replace((substrb(A.RYRQ00,1,4)||'-' ||substrb(A.RYRQ00,5,2)||'-'||substrb(A.RYRQ00,7,2)||' '||A.RYSJ00 ),'-- ','') adm_time,--入院时间
--replace(A.ZKKB00,';','→') refldept_dept,--转科科别
replace((select gjybks from bm_bmbm00 c where c.BMBH00=a.zkkb00) ,';','→') refldept_dept,--转科科别

replace((substrb(A.CYRQ00,1,4)||'-' ||substrb(A.CYRQ00,5,2)||'-'||substrb(A.CYRQ00,7,2)||' '||A.CYSJ00 ),'-- ','') dscg_time,--出院时间
a.rykb00 adm_caty,--入院科别
A.CYKB00 dscg_caty,--出院科别
SF_BA_GETLCZDB0_DIP(A.LSH000,'1',2,1) otp_wm_dise,--门（急）诊诊断（西医诊断）
SF_BA_GETLCZDB0_DIP(A.LSH000,'1',0,1) wm_dise_code,--西医疾病代码
'' otp_tcm_dise,--门（急）诊诊断（中医诊断）
'' tcm_dise_code,--中医疾病代码
replace( round(nvl(A.HXJSJ0,0)/24)||'/'||mod(nvl(A.HXJSJ0,0),24)||'/0','0/0/0','') vent_used_dura,--呼吸机使用时长
replace(nvl(A.HMRYQD,0)||'/'||nvl(A.HMRYQH,0)||'/'||nvl(A.HMRYQM,0),'0/0/0','') pwcry_bfadm_coma_dura,--颅脑损伤患者入院前昏迷时长
replace(nvl(A.HMRYHD,0)||'/'||nvl(A.HMRYHH,0)||'/'||nvl(A.HMRYHM,0),'0/0/0','') pwcry_afadm_coma_dura,--颅脑损伤患者入院后昏迷时长

/*nvl(a.TJHLTS,0) spga_nurscare_days,--特级护理天数
nvl(a.YJHLTS,0) lv1_nurscare_days,--一级护理天数
nvl(a.EJHLTS,0) scd_nurscare_days,--二级护理天数
nvl(a.SJHLTS,0) lv3_nurscare_days,--三级护理天数*/

(select case when (sum(xmsl00)/24)<1 then round(sum(xmsl00)/24) else floor(sum(xmsl00)/24) end from zy_fymx00 fy where fy.zyid00=a.lsh000 and (fy.XMMC00 like '%特级护理%')) spga_nurscare_days, --特级护理天数
(select nvl(sum(xmsl00),0) from zy_fymx00 fy where fy.zyid00=a.lsh000 and fy.XMMC00 like '%Ⅰ级护理%') lv1_nurscare_days,--一级护理天数
(select nvl(sum(xmsl00),0) from zy_fymx00 fy where fy.zyid00=a.lsh000 and fy.XMMC00 like '%Ⅱ级护理%') scd_nurscare_days,--二级护理天数
(select nvl(sum(xmsl00),0) from zy_fymx00 fy where fy.zyid00=a.lsh000 and fy.XMMC00 like '%Ⅲ级护理%') lv3_nurscare_days,--三级护理天数

decode(A.LYFS00,'医嘱离院','1','医嘱转院','2','医嘱转社区卫生服务机构','3','非医嘱离院','4','死亡','5','其他','9') dscg_way,--离院方式
A.ZYYLJG acp_medins_name,--拟接收机构名称
(select acp_optins_code from BA_HOSPITAL where acp_medins_name=trim(a.zyyljg) and rownum=1) acp_optins_code,--拟接收机构代码
'' bill_code,--票据代码
'' bill_no,--票据号码
'' biz_sn,--业务流水号
decode(A.ZZYJH0,'有','2','无','1','1') days_rinp_flag_31,--出院31天内再住院计划标志
A.ZZYMD0 days_rinp_pup_31,--出院31天内再住院计划目的
--A.FZR000 chfpdr_code,--主诊医师代码
(select ybygdm from bm_ygbm00 yg where yg.YGBH00=A.FZR000) chfpdr_code,--主诊医师代码
'' setl_begn_date,--结算开始日期
'' setl_end_date,--结算结束日期
'' medins_fill_dept,--医疗机构填报部门
'' medins_fill_psn,--医疗机构填报人
--A.ZRHS00 resp_nurs_code,--责任护士代码
(select ybygdm from  bm_ygbm00 yg where (yg.YGBH00=A.ZRHS00 or yg.xkh000=a.zrhs00) and yg.zwxm00=a.zrhsmc) resp_nurs_code,--责任护士代码
'' stas_type,--状态分类
'' hi_paymtd --医保支付方式
from BA_BRZYXX A
where


(a.SHBZ00='2'
and a.SCSHRQ = to_char(sysdate,'YYYYMMDD')
and exists (select 1 from VW_ZY_YBJSQD_JZB000 t where t.ZYID00=a.LSH000) )
or ( a.SHBZ00='2' and exists (select 1 from VW_ZY_YBJSQD_JZB000 t where t.ZYID00=a.LSH000 and t.JZRQ00=to_char(sysdate,'YYYYMMDD')   ) )



/*
(a.shbz00='2'
and a.scshrq >= '20250101' and a.scshrq <= '20250126'
and exists (select 1 from VW_ZY_YBJSQD_JZB000 t where t.ZYID00=a.lsh000) )
or ( a.shbz00='2' and exists (select 1 from VW_ZY_YBJSQD_JZB000 t where t.ZYID00=a.lsh000 and t.CYRQ00>='20250101' and t.CYRQ00<='20250126'   ) )
*/

/*

a.shbz00='2' and A.zyh000 in (


'912146'





) */
;