select 工号,姓名,证件号码,一级部门,二级部门,三级部门,四级部门,五级部门,岗位,工作地,入职时间,离职日期,合同签订单位,缴费地区,取暖费社保月, EMPLOYEE_NUMBER1,取暖费单位比例,取暖费个人比例,取暖费单位基数,取暖费个人基数,取暖费开始时间,取暖费结束时间,取暖费单位缴纳额,取暖费个人缴纳额, 养老保险单位比例,养老保险个人比例,养老保险单位基数,养老保险个人基数,养老保险开始时间,养老保险结束时间,养老保险单位缴纳额,养老保险个人缴纳额, 医疗保险单位比例,医疗保险个人比例,医疗保险单位基数,医疗保险个人基数,医疗保险开始时间,医疗保险结束时间,医疗保险单位缴纳额,医疗保险个人缴纳额, 生育保险单位比例,生育保险个人比例,生育保险单位基数,生育保险个人基数,生育保险开始时间,生育保险结束时间,生育保险单位缴纳额,生育保险个人缴纳额, 大病医保单位比例,大病医保个人比例,大病医保单位基数,大病医保个人基数,大病医保开始时间,大病医保结束时间,大病医保单位缴纳额,大病医保个人缴纳额, 工伤保险单位比例,工伤保险个人比例,工伤保险单位基数,工伤保险个人基数,工伤保险开始时间,工伤保险结束时间,工伤保险单位缴纳额,工伤保险个人缴纳额, 失业保险单位比例,失业保险个人比例,失业保险单位基数,失业保险个人基数,失业保险开始时间,失业保险结束时间,失业保险单位缴纳额,失业保险个人缴纳额, 公积金单位比例,公积金个人比例,公积金单位基数,公积金个人基数,公积金开始时间,公积金结束时间,公积金单位缴纳额,公积金个人缴纳额, 补充公积金单位比例,补充公积金个人比例,补充公积金单位基数,补充公积金个人基数,补充公积金开始时间,补充公积金结束时间,补充公积金单位缴纳额,补充公积金个人缴纳额, 养老滞纳金单位比例,养老滞纳金个人比例,养老滞纳金单位基数,养老滞纳金个人基数,养老滞纳金开始时间,养老滞纳金结束时间,养老滞纳金单位缴纳额,养老滞纳金个人缴纳额, 补充工伤保险单位比例,补充工伤保险个人比例,补充工伤保险单位基数,补充工伤保险个人基数,补充工伤保险开始时间,补充工伤保险结束时间,补充工伤保险单位缴纳额,补充工伤保险个人缴纳额, 残疾保障金单位比例,残疾保障金个人比例,残疾保障金单位基数,残疾保障金个人基数,残疾保障金开始时间,残疾保障金结束时间,残疾保障金单位缴纳额,残疾保障金个人缴纳额, null as 档案管理费, null as 利息滞纳金, null as 一次性收费, view2.取暖费单位缴纳额+view2.养老保险单位缴纳额+view2.医疗保险单位缴纳额+view2.生育保险单位缴纳额+view2.大病医保单位缴纳额+view2.工伤保险单位缴纳额+view2.失业保险单位缴纳额+view2.公积金单位缴纳额+view2.补充公积金单位缴纳额+view2.养老滞纳金单位缴纳额+view2.补充工伤保险单位缴纳额+view2.残疾保障金单位缴纳额 as 公司合计, view2.取暖费个人缴纳额+view2.养老保险个人缴纳额+view2.医疗保险个人缴纳额+view2.生育保险个人缴纳额+view2.大病医保个人缴纳额+view2.工伤保险个人缴纳额+view2.失业保险个人缴纳额+view2.公积金个人缴纳额+view2.补充公积金个人缴纳额+view2.养老滞纳金个人缴纳额+view2.补充工伤保险个人缴纳额+view2.残疾保障金个人缴纳额 as 个人合计, 服务费, view2.取暖费单位缴纳额+view2.养老保险单位缴纳额+view2.医疗保险单位缴纳额+view2.生育保险单位缴纳额+view2.大病医保单位缴纳额+view2.工伤保险单位缴纳额+view2.失业保险单位缴纳额+view2.公积金单位缴纳额+view2.补充公积金单位缴纳额+view2.养老滞纳金单位缴纳额+view2.补充工伤保险单位缴纳额+view2.残疾保障金单位缴纳额+view2.取暖费个人缴纳额+view2.养老保险个人缴纳额+view2.医疗保险个人缴纳额+view2.生育保险个人缴纳额+view2.大病医保个人缴纳额+view2.工伤保险个人缴纳额+view2.失业保险个人缴纳额+view2.公积金个人缴纳额+view2.补充公积金个人缴纳额+view2.养老滞纳金个人缴纳额+view2.补充工伤保险个人缴纳额+view2.残疾保障金个人缴纳额+服务费 as 总计, '' 备注, view2.取暖费个人缴纳额+view2.养老保险个人缴纳额+view2.医疗保险个人缴纳额+view2.生育保险个人缴纳额+view2.大病医保个人缴纳额+view2.工伤保险个人缴纳额+view2.失业保险个人缴纳额+view2.补充公积金个人缴纳额+view2.养老滞纳金个人缴纳额+view2.补充工伤保险个人缴纳额+view2.残疾保障金个人缴纳额 as 社保个人合计, view2.公积金个人缴纳额 as 公积金个人合计, view5.ptd 公积金额外自费, view2.养老保险单位缴纳额+view2.医疗保险单位缴纳额+view2.失业保险单位缴纳额+view2.生育保险单位缴纳额+view2.工伤保险单位缴纳额 社保公司部分, view2.公积金单位缴纳额 as 公积金公司部分合计, view2.养老保险单位缴纳额+view2.医疗保险单位缴纳额+view2.失业保险单位缴纳额+view2.生育保险单位缴纳额+view2.工伤保险单位缴纳额+view2.公积金单位缴纳额 as 公司公积金社保总计, view4.unit_name 社保缴纳单位 from (select emp.employee_number as "工号", emp.name as "姓名", person.national_id AS "证件号码", emp_job.x_department2 "一级部门", emp_job.x_department3 "二级部门", emp_job.x_department4 "三级部门", emp_job.x_department5 "四级部门", emp_job.x_department6 "五级部门", emp_job.x_businesstitle "岗位", hl.name "工作地", emp_job.hire_date "入职时间", emp.termination_date "离职日期", 0 as 档案管理费, 0 as 利息滞纳金, 0 as 一次性收费, 0 as 服务费, le.name "合同签订单位" from hr_employee emp left join emp_job on emp_job.employee_id=emp.id left join per_person person on person.id=emp.person_id left join hr_location hl on hl.id=emp_job.location_id left join legal_entity le on le.id = emp_job.legal_entity_id where emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= current_date AND (emp_job.eroad_end_date >= current_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL ) view1 left join (select employee_number employee_number1, sum(case when insurance_code='SI003' then company_payment_percent else 0 end) as 取暖费单位比例, sum(case when insurance_code='SI003' then individual_payment_percent else 0 end) as 取暖费个人比例, sum(case when insurance_code='SI003' then company_basis else 0 end) as 取暖费单位基数, sum(case when insurance_code='SI003' then employee_basis else 0 end) as 取暖费个人基数, max(case when insurance_code='SI003' then effective_start_date end) 取暖费开始时间, max(case when insurance_code='SI003' then effective_end_date end) 取暖费结束时间, sum(case when insurance_code='ER_BPI' then company_payment_percent else 0 end) as 养老保险单位比例, sum(case when insurance_code='ER_BPI' then individual_payment_percent else 0 end) as 养老保险个人比例, sum(case when insurance_code='ER_BPI' then company_basis else 0 end) as 养老保险单位基数, sum(case when insurance_code='ER_BPI' then employee_basis else 0 end) as 养老保险个人基数, max(case when insurance_code='ER_BPI' then effective_start_date end) 养老保险开始时间, max(case when insurance_code='ER_BPI' then effective_end_date end) 养老保险结束时间, sum(case when insurance_code='ER_BMI' then company_payment_percent else 0 end) as 医疗保险单位比例, sum(case when insurance_code='ER_BMI' then individual_payment_percent else 0 end) as 医疗保险个人比例, sum(case when insurance_code='ER_BMI' then company_basis else 0 end) as 医疗保险单位基数, sum(case when insurance_code='ER_BMI' then employee_basis else 0 end) as 医疗保险个人基数, max(case when insurance_code='ER_BMI' then effective_start_date end) 医疗保险开始时间, max(case when insurance_code='ER_BMI' then effective_end_date end) 医疗保险结束时间, sum(case when insurance_code='ER_MI' then company_payment_percent else 0 end) as 生育保险单位比例, sum(case when insurance_code='ER_MI' then individual_payment_percent else 0 end) as 生育保险个人比例, sum(case when insurance_code='ER_MI' then company_basis else 0 end) as 生育保险单位基数, sum(case when insurance_code='ER_MI' then employee_basis else 0 end) as 生育保险个人基数, max(case when insurance_code='ER_MI' then effective_start_date end) 生育保险开始时间, max(case when insurance_code='ER_MI' then effective_end_date end) 生育保险结束时间, sum(case when insurance_code='ER_SMI' then company_payment_percent else 0 end) as 大病医保单位比例, sum(case when insurance_code='ER_SMI' then individual_payment_percent else 0 end) as 大病医保个人比例, sum(case when insurance_code='ER_SMI' then company_basis else 0 end) as 大病医保单位基数, sum(case when insurance_code='ER_SMI' then employee_basis else 0 end) as 大病医保个人基数, max(case when insurance_code='ER_SMI' then effective_start_date end) 大病医保开始时间, max(case when insurance_code='ER_SMI' then effective_end_date end) 大病医保结束时间, sum(case when insurance_code='ER_WRI' then company_payment_percent else 0 end) as 工伤保险单位比例, sum(case when insurance_code='ER_WRI' then individual_payment_percent else 0 end) as 工伤保险个人比例, sum(case when insurance_code='ER_WRI' then company_basis else 0 end) as 工伤保险单位基数, sum(case when insurance_code='ER_WRI' then employee_basis else 0 end) as 工伤保险个人基数, max(case when insurance_code='ER_WRI' then effective_start_date end) 工伤保险开始时间, max(case when insurance_code='ER_WRI' then effective_end_date end) 工伤保险结束时间, sum(case when insurance_code='ER_UI' then company_payment_percent else 0 end) as 失业保险单位比例, sum(case when insurance_code='ER_UI' then individual_payment_percent else 0 end) as 失业保险个人比例, sum(case when insurance_code='ER_UI' then company_basis else 0 end) as 失业保险单位基数, sum(case when insurance_code='ER_UI' then employee_basis else 0 end) as 失业保险个人基数, max(case when insurance_code='ER_UI' then effective_start_date end) 失业保险开始时间, max(case when insurance_code='ER_UI' then effective_end_date end) 失业保险结束时间, sum(case when insurance_code='ER_PHF' then company_payment_percent else 0 end) as 公积金单位比例, sum(case when insurance_code='ER_PHF' then individual_payment_percent else 0 end) as 公积金个人比例, sum(case when insurance_code='ER_PHF' then company_basis else 0 end) as 公积金单位基数, sum(case when insurance_code='ER_PHF' then employee_basis else 0 end) as 公积金个人基数, max(case when insurance_code='ER_PHF' then effective_start_date end) 公积金开始时间, max(case when insurance_code='ER_PHF' then effective_end_date end) 公积金结束时间, sum(case when insurance_code='ER_SPHF' then company_payment_percent else 0 end) as 补充公积金单位比例, sum(case when insurance_code='ER_SPHF' then individual_payment_percent else 0 end) as 补充公积金个人比例, sum(case when insurance_code='ER_SPHF' then company_basis else 0 end) as 补充公积金单位基数, sum(case when insurance_code='ER_SPHF' then employee_basis else 0 end) as 补充公积金个人基数, max(case when insurance_code='ER_SPHF' then effective_start_date end) 补充公积金开始时间, max(case when insurance_code='ER_SPHF' then effective_end_date end) 补充公积金结束时间, sum(case when insurance_code='SI006' then company_payment_percent else 0 end) as 养老滞纳金单位比例, sum(case when insurance_code='SI006' then individual_payment_percent else 0 end) as 养老滞纳金个人比例, sum(case when insurance_code='SI006' then company_basis else 0 end) as 养老滞纳金单位基数, sum(case when insurance_code='SI006' then employee_basis else 0 end) as 养老滞纳金个人基数, max(case when insurance_code='SI006' then effective_start_date end) 养老滞纳金开始时间, max(case when insurance_code='SI006' then effective_end_date end) 养老滞纳金结束时间, sum(case when insurance_code='SI005' then company_payment_percent else 0 end) as 补充工伤保险单位比例, sum(case when insurance_code='SI005' then individual_payment_percent else 0 end) as 补充工伤保险个人比例, sum(case when insurance_code='SI005' then company_basis else 0 end) as 补充工伤保险单位基数, sum(case when insurance_code='SI005' then employee_basis else 0 end) as 补充工伤保险个人基数, max(case when insurance_code='SI005' then effective_start_date end) 补充工伤保险开始时间, max(case when insurance_code='SI005' then effective_end_date end) 补充工伤保险结束时间, sum(case when insurance_code='SI004' then company_payment_percent else 0 end) as 残疾保障金单位比例, sum(case when insurance_code='SI004' then individual_payment_percent else 0 end) as 残疾保障金个人比例, sum(case when insurance_code='SI004' then company_basis else 0 end) as 残疾保障金单位基数, sum(case when insurance_code='SI004' then employee_basis else 0 end) as 残疾保障金个人基数, max(case when insurance_code='SI004' then effective_start_date end) 残疾保障金开始时间, max(case when insurance_code='SI004' then effective_end_date end) 残疾保障金结束时间 from view_benefits_employee_rule where effective_start_date <= current_date and (effective_end_date >= current_date or effective_end_date IS NULL) group by employee_number1) view3 on view1.工号 = view3.employee_number1 left join (select employee_number, max(case when insurance_code='SI003' then insurance_month end) as 取暖费社保月, sum(case when insurance_code='SI003' then individual_calculate_result else 0 end) as 取暖费个人缴纳额, sum(case when insurance_code='SI003' then company_calculate_result else 0 end) as 取暖费单位缴纳额, max(case when insurance_code='ER_BPI' then insurance_month end) as 养老保险社保月, sum(case when insurance_code='ER_BPI' then individual_calculate_result else 0 end) as 养老保险个人缴纳额, sum(case when insurance_code='ER_BPI' then company_calculate_result else 0 end) as 养老保险单位缴纳额, max(case when insurance_code='ER_BMI' then insurance_month end) as 医疗保险社保月, sum(case when insurance_code='ER_BMI' then individual_calculate_result else 0 end) as 医疗保险个人缴纳额, sum(case when insurance_code='ER_BMI' then company_calculate_result else 0 end) as 医疗保险单位缴纳额, max(case when insurance_code='ER_MI' then insurance_month end) as 生育保险社保月, sum(case when insurance_code='ER_MI' then individual_calculate_result else 0 end) as 生育保险个人缴纳额, sum(case when insurance_code='ER_MI' then company_calculate_result else 0 end) as 生育保险单位缴纳额, max(case when insurance_code='ER_SMI' then insurance_month end) as 大病医保社保月, sum(case when insurance_code='ER_SMI' then individual_calculate_result else 0 end) as 大病医保个人缴纳额, sum(case when insurance_code='ER_SMI' then company_calculate_result else 0 end) as 大病医保单位缴纳额, max(case when insurance_code='ER_WRI' then insurance_month end) as 工伤保险社保月, sum(case when insurance_code='ER_WRI' then individual_calculate_result else 0 end) as 工伤保险个人缴纳额, sum(case when insurance_code='ER_WRI' then company_calculate_result else 0 end) as 工伤保险单位缴纳额, max(case when insurance_code='ER_UI' then insurance_month end) as 失业保险社保月, sum(case when insurance_code='ER_UI' then individual_calculate_result else 0 end) as 失业保险个人缴纳额, sum(case when insurance_code='ER_UI' then company_calculate_result else 0 end) as 失业保险单位缴纳额, max(case when insurance_code='ER_PHF' then insurance_month end) as 公积金社保月, sum(case when insurance_code='ER_PHF' then individual_calculate_result else 0 end) as 公积金个人缴纳额, sum(case when insurance_code='ER_PHF' then company_calculate_result else 0 end) as 公积金单位缴纳额, max(case when insurance_code='ER_SPHF' then insurance_month end) as 补充公积金社保月, sum(case when insurance_code='ER_SPHF' then individual_calculate_result else 0 end) as 补充公积金个人缴纳额, sum(case when insurance_code='ER_SPHF' then company_calculate_result else 0 end) as 补充公积金单位缴纳额, max(case when insurance_code='SI006' then insurance_month end) as 养老滞纳金社保月, sum(case when insurance_code='SI006' then individual_calculate_result else 0 end) as 养老滞纳金个人缴纳额, sum(case when insurance_code='SI006' then company_calculate_result else 0 end) as 养老滞纳金单位缴纳额, max(case when insurance_code='SI005' then insurance_month end) as 补充工伤保险社保月, sum(case when insurance_code='SI005' then individual_calculate_result else 0 end) as 补充工伤保险个人缴纳额, sum(case when insurance_code='SI005' then company_calculate_result else 0 end) as 补充工伤保险单位缴纳额, max(case when insurance_code='SI004' then insurance_month end) as 残疾保障金社保月, sum(case when insurance_code='SI004' then individual_calculate_result else 0 end) as 残疾保障金个人缴纳额, sum(case when insurance_code='SI004' then company_calculate_result else 0 end) as 残疾保障金单位缴纳额, social_city_name 缴费地区 from (select aa.insurance_code,aa.employee_number,aa.insurance_month,individual_calculate_result,company_calculate_result,social_city_name from view_benefits_employee_record vb left join ( select insurance_code,employee_number,max(insurance_month) insurance_month from view_benefits_employee_record group by insurance_code,employee_number) aa on aa.employee_number = vb.employee_number and aa.insurance_code = vb.insurance_code and aa.insurance_month = vb.insurance_month) bb where bb.employee_number notnull group by employee_number,social_city_name) view2 on view3.employee_number1=view2.employee_number left join (select emp.employee_number as employee_number2, max(sa.unit_name) unit_name from hr_employee emp left join benefits_employee_rule_information benefit on benefit.employee_id = emp.id left join benefits_company_account sa on sa.id = benefit.social_insurance_account_unit_id where sa.type = 1 group by employee_number) view4 on view4.employee_number2=view1.工号 left join (select res.employee_number employee_number3,vv.ptd,res.date_integer,res.balance_item_code from view_payroll_balance_summary_result vv left join ( select employee_number,max(date_integer) date_integer,balance_item_code from view_payroll_balance_summary_result where summary_type=2 and balance_item_code = 'Should_send' group by balance_item_code,employee_number) res on res.employee_number = vv.employee_number and vv.balance_item_code = res.balance_item_code and vv.date_integer=res.date_integer where res.balance_item_code notnull) view5 on view5.employee_number3 = view1.工号