SELECT pp.name as x_payroll_month, pg.name as x_payrollgroup,he.employee_number as x_employee_number, he.x_english_name as x_english_name,he.name as x_name,he.hiredate as x_hiredate,he.identification_id as x_identification_id, le.name as x_legal_entity,he. department_id as x_ou_code,hf.name as x_function_name,het.name as x_type_name, (CASE he.work_activity WHEN 'in_service' THEN '在职' WHEN 'turn_over' THEN '离职' WHEN 'on_boarding' THEN '预入职' WHEN 'declined' THEN '放弃入职' END) as x_work_activity, he.work_email as x_work_email, he.personal_email as x_personal_email,he.x_ess_mail_new, pp.payoff_day-INTERVAL'1 month' as x_last_month_payoff_day, pp.payoff_day as x_payoff_day, pgab.name as x_period_rule, py.name as x_payroll_year, '' as x_email1, '' as x_email2, '' as x_email3, '' as x_email4, '' as x_email5, '' as x_email6 from hr_payroll_group pg left join hr_employee_payroll_group_relation hepgr on pg.id=hepgr.payroll_group_id left join hr_employee he on he.id = hepgr.employee_id left join legal_entity le on le.id=he.legal_entity left join hr_function hf on hf.id=he.function_id left join hr_employee_type het on het.id=he.employee_type_rep left join period_group_account_book pgab on pgab.id=pg.account_book_id left join payroll_year py on py.account_book_id = pgab.id left join payroll_period pp on pp.payroll_year_id = py.id where (to_char(pp.date_start,'yyyy-mm') >= hepgr.current_tax_month) and (to_char(pp.date_start,'yyyy-mm') >= to_char(he.hiredate,'yyyy-mm')) or hepgr.payroll_valid_to isnull