select 
    emp.employee_number as 员工工号,
    emp.name as 员工姓名,
    origin_unit_trans.value as 原业务单元,
    origin_division_trans.value as 原分部,
    origin_asssitant_dep_trans.value as 原总助分管部门,
    origin_dep1_trans.value as 原一级部门,
    origin_dep2_trans.value as 原二级部门,
    origin_branch_dep_trans.value as 原支公司部门,
    origin_dep_trans.value as 原部门,
    origin_position_trans.value as 原岗位,
    origin_job_trans.value as 原职务,
    origin_job_sequence_trans.value as 原职务序列,
    origin_job_grade_trans.value as 原职等,
    origin_job_level_trans.value as 原职级,
    origin_cost_center_trans.value as 原AD成本中心名称,
    job_transfer.x_cost_center_code as 原AD成本中心编码,
    job_transfer.x_cost_center_type as 原AD成本中心类型,
    job_transfer.x_cost_center_line as 原AD成本中心条线,
    origin_xz_cost_center_trans.value as 原人员编制成本中心名称,
    job_transfer.x_xz_cost_center_code as 原人员编制成本中心编码,
    job_transfer.x_xz_cost_center_type as 原人员编制成本中心类型,
    job_transfer.x_xz_line as 原人员编制成本中心条线,
    origin_primary_cost_center_trans.value as 原主成本中心,
    origin_primary_cost_center.code as 原主成本中心编码,
    origin_center_line.percentage as 原主成本中心分摊比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA001' then
        temp_salary_archive.payroll_item ->> 'PA001'
    else null end as 日实习津贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA002' then
        temp_salary_archive.payroll_item ->> 'PA002'
    else null end as 基本工资,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA003' then
        temp_salary_archive.payroll_item ->> 'PA003'
    else null end as 计薪月数,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA004' then
        temp_salary_archive.payroll_item ->> 'PA004'
    else null end as 非销售_月度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA005' then
        temp_salary_archive.payroll_item ->> 'PA005'
    else null end as 非销售_季度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA006' then
        temp_salary_archive.payroll_item ->> 'PA006'
    else null end as 非销售_月度绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA007' then
        temp_salary_archive.payroll_item ->> 'PA007'
    else null end as 非销售_季度绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA008' then
        temp_salary_archive.payroll_item ->> 'PA008'
    else null end as 非销售_年度目标薪酬,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA009' then
        temp_salary_archive.payroll_item ->> 'PA009'
    else null end as 非销售_诚信履约奖,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA010' then
        temp_salary_archive.payroll_item ->> 'PA010'
    else null end as 非销售_诚信履约奖_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA011' then
        temp_salary_archive.payroll_item ->> 'PA011'
    else null end as 非销售_月度理赔绩效,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA012' then
        temp_salary_archive.payroll_item ->> 'PA012'
    else null end as 非销售_月度理赔绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA013' then
        temp_salary_archive.payroll_item ->> 'PA013'
    else null end as 非销售_中长期激励,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA014' then
        temp_salary_archive.payroll_item ->> 'PA014'
    else null end as 非销售_中长期激励_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA015' then
        temp_salary_archive.payroll_item ->> 'PA015'
    else null end as 机构班子_月度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA016' then
        temp_salary_archive.payroll_item ->> 'PA016'
    else null end as 机构班子_季度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA017' then
        temp_salary_archive.payroll_item ->> 'PA017'
    else null end as 机构班子_月度绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA018' then
        temp_salary_archive.payroll_item ->> 'PA018'
    else null end as 机构班子_季度绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA019' then
        temp_salary_archive.payroll_item ->> 'PA019'
    else null end as 机构班子_年度目标薪酬,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA020' then
        temp_salary_archive.payroll_item ->> 'PA020'
    else null end as 机构班子_诚信履约奖,   
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA021' then
        temp_salary_archive.payroll_item ->> 'PA021'
    else null end as 机构班子_诚信履约奖_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA022' then
        temp_salary_archive.payroll_item ->> 'PA022'
    else null end as 机构班子_月度理赔绩效,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA023' then
        temp_salary_archive.payroll_item ->> 'PA023'
    else null end as 机构班子_月度理赔绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA024' then
        temp_salary_archive.payroll_item ->> 'PA024'
    else null end as 机构班子_中长期激励,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA025' then
        temp_salary_archive.payroll_item ->> 'PA025'
    else null end as 机构班子_中长期激励_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA026' then
        temp_salary_archive.payroll_item ->> 'PA026'
    else null end as 公积金补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA027' then
        temp_salary_archive.payroll_item ->> 'PA027'
    else null end as 定向性私车公用补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA028' then
        temp_salary_archive.payroll_item ->> 'PA028'
    else null end as 住房补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA029' then
        temp_salary_archive.payroll_item ->> 'PA029'
    else null end as 特殊津贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA030' then
        temp_salary_archive.payroll_item ->> 'PA030'
    else null end as 异地派遣补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA031' then
        temp_salary_archive.payroll_item ->> 'PA031'
    else null end as 其他补贴1,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA032' then
        temp_salary_archive.payroll_item ->> 'PA032'
    else null end as 其他补贴2,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA033' then
        temp_salary_archive.payroll_item ->> 'PA033'
    else null end as 其他补贴3,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA034' then
        temp_salary_archive.payroll_item ->> 'PA034'
    else null end as 其他补贴4,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA035' then
        temp_salary_archive.payroll_item ->> 'PA035'
    else null end as 其他补贴5,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA036' then
        temp_salary_archive.payroll_item ->> 'PA036'
    else null end as 机构班子_计薪月数,
    new_unit_trans.value as 新业务单元,
    new_division_trans.value as 新分部,
    new_asssitant_dep_trans.value as 新总助分管部门,
    new_dep1_trans.value as 新一级部门,
    new_dep2_trans.value as 新二级部门,
    new_branch_dep_trans.value as 新支公司部门,
    new_dep_trans.value as 新部门,
    new_position_trans.value as 新岗位,
    new_job_trans.value as 新职务,
    new_job_sequence_trans.value as 新职务序列,
    new_job_grade_trans.value as 新职等,
    new_job_level_trans.value as 新职级,
    new_cost_center_trans.value as 新AD成本中心名称,
    job_transfer.x_new_cost_center_code as 新AD成本中心编码,
    job_transfer.x_new_cost_center_type as 新AD成本中心类型,
    job_transfer.x_new_cost_center_line as 新AD成本中心条线,
    new_xz_cost_center_trans.value as 新人员编制成本中心名称,
    job_transfer.x_new_xz_cost_center_code as 新人员编制成本中心编码,
    job_transfer.x_new_xz_cost_center_type as 新人员编制成本中心类型,
    job_transfer.x_new_xz_line as 新人员编制成本中心条线,
    new_primary_cost_center_trans.value as 新主成本中心,
    new_primary_cost_center.code as 新主成本中心编码,
    new_center_line.percentage as 新主成本中心分摊比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA001' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA001'
    else null end as 新日实习津贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA002' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA002'
    else null end as 新基本工资,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA003' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA003'
    else null end as 新计薪月数,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA004' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA004'
    else null end as 新非销售_月度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA005' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA005'
    else null end as 新非销售_季度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA006' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA006'
    else null end as 新非销售_月度绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA007' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA007'
    else null end as 新非销售_季度绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA008' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA008'
    else null end as 新非销售_年度目标薪酬,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA009' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA009'
    else null end as 新非销售_诚信履约奖,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA010' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA010'
    else null end as 新非销售_诚信履约奖_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA011' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA011'
    else null end as 新非销售_月度理赔绩效,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA012' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA012'
    else null end as 新非销售_月度理赔绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA013' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA013'
    else null end as 新非销售_中长期激励,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA014' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA014'
    else null end as 新非销售_中长期激励_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA015' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA015'
    else null end as 新机构班子_月度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA016' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA016'
    else null end as 新机构班子_季度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA017' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA017'
    else null end as 新机构班子_月度绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA018' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA018'
    else null end as 新机构班子_季度绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA019' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA019'
    else null end as 新机构班子_年度目标薪酬,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA020' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA020'
    else null end as 新机构班子_诚信履约奖,   
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA021' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA021'
    else null end as 新机构班子_诚信履约奖_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA022' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA022'
    else null end as 新机构班子_月度理赔绩效,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA023' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA023'
    else null end as 新机构班子_月度理赔绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA024' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA024'
    else null end as 新机构班子_中长期激励,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA025' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA025'
    else null end as 新机构班子_中长期激励_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA026' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA026'
    else null end as 新公积金补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA027' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA027'
    else null end as 新定向性私车公用补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA028' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA028'
    else null end as 新住房补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA029' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA029'
    else null end as 新特殊津贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA030' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA030'
    else null end as 新异地派遣补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA031' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA031'
    else null end as 新其他补贴1,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA032' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA032'
    else null end as 新其他补贴2,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA033' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA033'
    else null end as 新其他补贴3,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA034' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA034'
    else null end as 新其他补贴4,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA035' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA035'
    else null end as 新其他补贴5,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA036' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA036'
    else null end as 新机构班子_计薪月数    
from job_transfer
left join hr_employee emp on emp.id = job_transfer.employee_id
left join hr_business_unit origin_unit on origin_unit.id = job_transfer.business_unit_id
left join ir_translation as origin_unit_trans on origin_unit_trans.res_id = origin_unit.id and origin_unit_trans.lang='zh_CN' and origin_unit_trans.name='hr.business.unit,name'
left join hr_division origin_division on origin_division.id = job_transfer.division_id
left join ir_translation as origin_division_trans on origin_division_trans.res_id = origin_division.id and origin_division_trans.lang='zh_CN' and origin_division_trans.name='hr.division,name'
left join hr_department origin_asssitant_dep on origin_asssitant_dep.id = job_transfer.x_general_assistant_department
left join ir_translation as origin_asssitant_dep_trans on origin_asssitant_dep_trans.res_id = origin_asssitant_dep.id and origin_asssitant_dep_trans.lang='zh_CN' and origin_asssitant_dep_trans.name='hr.department,name'
left join hr_department origin_dep1 on origin_dep1.id = job_transfer.x_first_department
left join ir_translation as origin_dep1_trans on origin_dep1_trans.res_id = origin_dep1.id and origin_dep1_trans.lang='zh_CN' and origin_dep1_trans.name='hr.department,name'
left join hr_department origin_dep2 on origin_dep2.id = job_transfer.x_second_department
left join ir_translation as origin_dep2_trans on origin_dep2_trans.res_id = origin_dep2.id and origin_dep2_trans.lang='zh_CN' and origin_dep2_trans.name='hr.department,name'
left join hr_department origin_branch_dep on origin_branch_dep.id = job_transfer.x_branch_department
left join ir_translation as origin_branch_dep_trans on origin_branch_dep_trans.res_id = origin_branch_dep.id and origin_branch_dep_trans.lang='zh_CN' and origin_branch_dep_trans.name='hr.department,name'
left join hr_department origin_dep on origin_dep.id = job_transfer.department_id
left join ir_translation as origin_dep_trans on origin_dep_trans.res_id = origin_dep.id and origin_dep_trans.lang='zh_CN' and origin_dep_trans.name='hr.department,name'
left join hr_position origin_position on origin_position.id = job_transfer.position_id
left join ir_translation as origin_position_trans on origin_position_trans.res_id = origin_position.id and origin_position_trans.lang='zh_CN' and origin_position_trans.name='hr.position,name'
left join job_classification origin_job on origin_job.id = job_transfer.job_id
left join ir_translation as origin_job_trans on origin_job_trans.res_id = origin_job.id and origin_job_trans.lang='zh_CN' and origin_job_trans.name='job.classification,name'
left join job_sequence origin_job_sequence on origin_job_sequence.id = job_transfer.job_sequence_id
left join ir_translation as origin_job_sequence_trans on origin_job_sequence_trans.res_id = origin_job_sequence.id and origin_job_sequence_trans.lang='zh_CN' and origin_job_sequence_trans.name='job.sequence,name'
left join job_grade origin_job_grade on origin_job_grade.id = job_transfer.job_grade_id
left join ir_translation as origin_job_grade_trans on origin_job_grade_trans.res_id = origin_job_grade.id and origin_job_grade_trans.lang='zh_CN' and origin_job_grade_trans.name='job.grade,name'
left join job_level origin_job_level on origin_job_level.id = job_transfer.job_level_id
left join ir_translation as origin_job_level_trans on origin_job_level_trans.res_id = origin_job_level.id and origin_job_level_trans.lang='zh_CN' and origin_job_level_trans.name='job.level,name'
left join cost_center as origin_cost_center on origin_cost_center.id = job_transfer.x_cost_center
left join ir_translation as origin_cost_center_trans on origin_cost_center_trans.res_id = origin_cost_center.id and origin_cost_center_trans.lang='zh_CN' and origin_cost_center_trans.name='cost.center,name'
left join cost_center as origin_xz_cost_center on origin_xz_cost_center.id = job_transfer.x_xz_cost_center_id
left join ir_translation as origin_xz_cost_center_trans on origin_xz_cost_center_trans.res_id = origin_xz_cost_center.id and origin_xz_cost_center_trans.lang='zh_CN' and origin_xz_cost_center_trans.name='cost.center,name'
left join cost_center_distribution_adjustment_line as origin_center_line on origin_center_line.transfer_id = job_transfer.id and origin_center_line.is_primary = true and origin_center_line.line_type = 'before'
left join cost_center as origin_primary_cost_center on origin_primary_cost_center.id = origin_center_line.cost_center_id
left join ir_translation as origin_primary_cost_center_trans on origin_primary_cost_center_trans.res_id = origin_primary_cost_center.id and origin_primary_cost_center_trans.lang='zh_CN' and origin_primary_cost_center_trans.name='cost.center,name'
left join (
    select 
        job_transfer.id as transfer_id,
        json_object_agg(pre_archive.code, COALESCE(pre_salary_archive.amount, 0.00)) as payroll_item
    from job_transfer
    left join job_transfer_salary_archive as pre_salary_archive on pre_salary_archive.pre_job_transfer_id = job_transfer.id
    left join payroll_archive_config as pre_archive on pre_archive.id = pre_salary_archive.payroll_item_id
    -- left join ir_translation as pre_archive_trans on pre_archive_trans.res_id = pre_archive.id and pre_archive_trans.lang='zh_CN' and pre_archive_trans.name='payroll.archive.config,name'
    where job_transfer.x_bp_state = 'done' and job_transfer.active = true and job_transfer.is_adjust_salary = true
    group by job_transfer.id
    union all
    select 
        job_transfer.id as transfer_id,
        null as payroll_item
    from job_transfer
    left join job_transfer_salary_archive as pre_salary_archive on pre_salary_archive.pre_job_transfer_id = job_transfer.id
    left join payroll_archive_config as pre_archive on pre_archive.id = pre_salary_archive.payroll_item_id
    where job_transfer.x_bp_state = 'done' and job_transfer.active = true and job_transfer.is_adjust_salary = false
    group by job_transfer.id
) as temp_salary_archive on temp_salary_archive.transfer_id = job_transfer.id
left join hr_business_unit new_unit on new_unit.id = job_transfer.new_business_unit_id
left join ir_translation as new_unit_trans on new_unit_trans.res_id = new_unit.id and new_unit_trans.lang='zh_CN' and new_unit_trans.name='hr.business.unit,name'
left join hr_division new_division on new_division.id = job_transfer.new_division_id
left join ir_translation as new_division_trans on new_division_trans.res_id = new_division.id and new_division_trans.lang='zh_CN' and new_division_trans.name='hr.division,name'
left join hr_department new_asssitant_dep on new_asssitant_dep.id = job_transfer.x_new_general_assistant_department
left join ir_translation as new_asssitant_dep_trans on new_asssitant_dep_trans.res_id = new_asssitant_dep.id and new_asssitant_dep_trans.lang='zh_CN' and new_asssitant_dep_trans.name='hr.department,name'
left join hr_department new_dep1 on new_dep1.id = job_transfer.x_new_first_department
left join ir_translation as new_dep1_trans on new_dep1_trans.res_id = new_dep1.id and new_dep1_trans.lang='zh_CN' and new_dep1_trans.name='hr.department,name'
left join hr_department new_dep2 on new_dep2.id = job_transfer.x_new_second_department
left join ir_translation as new_dep2_trans on new_dep2_trans.res_id = new_dep2.id and new_dep2_trans.lang='zh_CN' and new_dep2_trans.name='hr.department,name'
left join hr_department new_branch_dep on new_branch_dep.id = job_transfer.x_new_branch_department
left join ir_translation as new_branch_dep_trans on new_branch_dep_trans.res_id = new_branch_dep.id and new_branch_dep_trans.lang='zh_CN' and new_branch_dep_trans.name='hr.department,name'
left join hr_department new_dep on new_dep.id = job_transfer.new_department_id
left join ir_translation as new_dep_trans on new_dep_trans.res_id = new_dep.id and new_dep_trans.lang='zh_CN' and new_dep_trans.name='hr.department,name'
left join hr_position new_position on new_position.id = job_transfer.new_position_id
left join ir_translation as new_position_trans on new_position_trans.res_id = new_position.id and new_position_trans.lang='zh_CN' and new_position_trans.name='hr.position,name'
left join job_classification new_job on new_job.id = job_transfer.new_job_id
left join ir_translation as new_job_trans on new_job_trans.res_id = new_job.id and new_job_trans.lang='zh_CN' and new_job_trans.name='job.classification,name'
left join job_sequence new_job_sequence on new_job_sequence.id = job_transfer.new_job_sequence_id
left join ir_translation as new_job_sequence_trans on new_job_sequence_trans.res_id = new_job_sequence.id and new_job_sequence_trans.lang='zh_CN' and new_job_sequence_trans.name='job.sequence,name'
left join job_grade new_job_grade on new_job_grade.id = job_transfer.new_job_grade_id
left join ir_translation as new_job_grade_trans on new_job_grade_trans.res_id = new_job_grade.id and new_job_grade_trans.lang='zh_CN' and new_job_grade_trans.name='job.grade,name'
left join job_level new_job_level on new_job_level.id = job_transfer.new_job_level_id
left join ir_translation as new_job_level_trans on new_job_level_trans.res_id = new_job_level.id and new_job_level_trans.lang='zh_CN' and new_job_level_trans.name='job.level,name'
left join cost_center as new_cost_center on new_cost_center.id = job_transfer.x_new_cost_center
left join ir_translation as new_cost_center_trans on new_cost_center_trans.res_id = new_cost_center.id and new_cost_center_trans.lang='zh_CN' and new_cost_center_trans.name='cost.center,name'
left join cost_center as new_xz_cost_center on new_xz_cost_center.id = job_transfer.x_new_xz_cost_center_id
left join ir_translation as new_xz_cost_center_trans on new_xz_cost_center_trans.res_id = new_xz_cost_center.id and new_xz_cost_center_trans.lang='zh_CN' and new_xz_cost_center_trans.name='cost.center,name'
left join cost_center_distribution_adjustment_line as new_center_line on new_center_line.transfer_id = job_transfer.id and new_center_line.is_primary = true and new_center_line.line_type = 'after'
left join cost_center as new_primary_cost_center on new_primary_cost_center.id = new_center_line.cost_center_id
left join ir_translation as new_primary_cost_center_trans on new_primary_cost_center_trans.res_id = new_primary_cost_center.id and new_primary_cost_center_trans.lang='zh_CN' and new_primary_cost_center_trans.name='cost.center,name'
left join (
    select 
        job_transfer.id as transfer_id,
        json_object_agg(after_archive.code, COALESCE(after_salary_archive.amount, 0.00)) as after_payroll_item
    from job_transfer
    left join job_transfer_salary_archive as after_salary_archive on after_salary_archive.aft_job_transfer_id = job_transfer.id
    left join payroll_archive_config as after_archive on after_archive.id = after_salary_archive.payroll_item_id
    -- left join ir_translation as after_archive_trans on after_archive_trans.res_id = after_archive.id and after_archive_trans.lang='zh_CN' and after_archive_trans.name='payroll.archive.config,name'
    where job_transfer.x_bp_state = 'done' and job_transfer.active = true and job_transfer.is_adjust_salary = true
    group by job_transfer.id
    union all
    select 
        job_transfer.id as transfer_id,
        null as after_payroll_item
    from job_transfer
    left join job_transfer_salary_archive as after_salary_archive on after_salary_archive.aft_job_transfer_id = job_transfer.id
    left join payroll_archive_config as after_archive on after_archive.id = after_salary_archive.payroll_item_id
    where job_transfer.x_bp_state = 'done' and job_transfer.active = true and job_transfer.is_adjust_salary = false
    group by job_transfer.id
) as aft_temp_salary_archive on aft_temp_salary_archive.transfer_id = job_transfer.id
where job_transfer.x_bp_state = 'done' and job_transfer.active = true
--and emp.employee_number = 'LM03936'

union all

select
    emp.employee_number as 员工工号,
    emp.name as 员工姓名,
    origin_unit_trans.value as 原业务单元,
    origin_division_trans.value as 原分部,
    origin_asssitant_dep_trans.value as 原总助分管部门,
    origin_dep1_trans.value as 原一级部门,
    origin_dep2_trans.value as 原二级部门,
    origin_branch_dep_trans.value as 原支公司部门,
    origin_dep_trans.value as 原部门,
    origin_position_trans.value as 原岗位,
    origin_job_trans.value as 原职务,
    origin_job_sequence_trans.value as 原职务序列,
    origin_job_grade_trans.value as 原职等,
    origin_job_level_trans.value as 原职级,
    null as 原AD成本中心名称,
    null as 原AD成本中心编码,
    null as 原AD成本中心类型,
    null as 原AD成本中心条线,
    null as 原人员编制成本中心名称,
    null as 原人员编制成本中心编码,
    null as 原人员编制成本中心类型,
    null as 原人员编制成本中心条线,
    null as 原主成本中心,
    null as 原主成本中心编码,
    null as 原主成本中心分摊比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA001' then
        temp_salary_archive.payroll_item ->> 'PA001'
    else null end as 日实习津贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA002' then
        temp_salary_archive.payroll_item ->> 'PA002'
    else null end as 基本工资,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA003' then
        temp_salary_archive.payroll_item ->> 'PA003'
    else null end as 计薪月数,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA004' then
        temp_salary_archive.payroll_item ->> 'PA004'
    else null end as 非销售_月度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA005' then
        temp_salary_archive.payroll_item ->> 'PA005'
    else null end as 非销售_季度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA006' then
        temp_salary_archive.payroll_item ->> 'PA006'
    else null end as 非销售_月度绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA007' then
        temp_salary_archive.payroll_item ->> 'PA007'
    else null end as 非销售_季度绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA008' then
        temp_salary_archive.payroll_item ->> 'PA008'
    else null end as 非销售_年度目标薪酬,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA009' then
        temp_salary_archive.payroll_item ->> 'PA009'
    else null end as 非销售_诚信履约奖,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA010' then
        temp_salary_archive.payroll_item ->> 'PA010'
    else null end as 非销售_诚信履约奖_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA011' then
        temp_salary_archive.payroll_item ->> 'PA011'
    else null end as 非销售_月度理赔绩效,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA012' then
        temp_salary_archive.payroll_item ->> 'PA012'
    else null end as 非销售_月度理赔绩效_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA013' then
        temp_salary_archive.payroll_item ->> 'PA013'
    else null end as 非销售_中长期激励,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA014' then
        temp_salary_archive.payroll_item ->> 'PA014'
    else null end as 非销售_中长期激励_占基薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA015' then
        temp_salary_archive.payroll_item ->> 'PA015'
    else null end as 机构班子_月度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA016' then
        temp_salary_archive.payroll_item ->> 'PA016'
    else null end as 机构班子_季度绩效_标准值,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA017' then
        temp_salary_archive.payroll_item ->> 'PA017'
    else null end as 机构班子_月度绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA018' then
        temp_salary_archive.payroll_item ->> 'PA018'
    else null end as 机构班子_季度绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA019' then
        temp_salary_archive.payroll_item ->> 'PA019'
    else null end as 机构班子_年度目标薪酬,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA020' then
        temp_salary_archive.payroll_item ->> 'PA020'
    else null end as 机构班子_诚信履约奖,   
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA021' then
        temp_salary_archive.payroll_item ->> 'PA021'
    else null end as 机构班子_诚信履约奖_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA022' then
        temp_salary_archive.payroll_item ->> 'PA022'
    else null end as 机构班子_月度理赔绩效,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA023' then
        temp_salary_archive.payroll_item ->> 'PA023'
    else null end as 机构班子_月度理赔绩效_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA024' then
        temp_salary_archive.payroll_item ->> 'PA024'
    else null end as 机构班子_中长期激励,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA025' then
        temp_salary_archive.payroll_item ->> 'PA025'
    else null end as 机构班子_中长期激励_占目标年薪比例,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA026' then
        temp_salary_archive.payroll_item ->> 'PA026'
    else null end as 公积金补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA027' then
        temp_salary_archive.payroll_item ->> 'PA027'
    else null end as 定向性私车公用补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA028' then
        temp_salary_archive.payroll_item ->> 'PA028'
    else null end as 住房补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA029' then
        temp_salary_archive.payroll_item ->> 'PA029'
    else null end as 特殊津贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA030' then
        temp_salary_archive.payroll_item ->> 'PA030'
    else null end as 异地派遣补贴,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA031' then
        temp_salary_archive.payroll_item ->> 'PA031'
    else null end as 其他补贴1,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA032' then
        temp_salary_archive.payroll_item ->> 'PA032'
    else null end as 其他补贴2,    
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA033' then
        temp_salary_archive.payroll_item ->> 'PA033'
    else null end as 其他补贴3,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA034' then
        temp_salary_archive.payroll_item ->> 'PA034'
    else null end as 其他补贴4,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA035' then
        temp_salary_archive.payroll_item ->> 'PA035'
    else null end as 其他补贴5,
    case when temp_salary_archive.payroll_item is not null and temp_salary_archive.payroll_item::jsonb ? 'PA036' then
        temp_salary_archive.payroll_item ->> 'PA036'
    else null end as 机构班子_计薪月数,
    null as 新业务单元,
    null as 新分部,
    null as 新总助分管部门,
    null as 新一级部门,
    null as 新二级部门,
    null as 新支公司部门,
    null as 新部门,
    null as 新岗位,
    null as 新职务,
    null as 新职务序列,
    null as 新职等,
    null as 新职级,
    null as 新AD成本中心名称,
    null as 新AD成本中心编码,
    null as 新AD成本中心类型,
    null as 新AD成本中心条线,
    null as 新人员编制成本中心名称,
    null as 新人员编制成本中心编码,
    null as 新人员编制成本中心类型,
    null as 新人员编制成本中心条线,
    null as 新主成本中心,
    null as 新主成本中心编码,
    null as 新主成本中心分摊比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA001' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA001'
    else null end as 新日实习津贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA002' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA002'
    else null end as 新基本工资,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA003' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA003'
    else null end as 新计薪月数,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA004' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA004'
    else null end as 新非销售_月度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA005' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA005'
    else null end as 新非销售_季度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA006' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA006'
    else null end as 新非销售_月度绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA007' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA007'
    else null end as 新非销售_季度绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA008' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA008'
    else null end as 新非销售_年度目标薪酬,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA009' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA009'
    else null end as 新非销售_诚信履约奖,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA010' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA010'
    else null end as 新非销售_诚信履约奖_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA011' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA011'
    else null end as 新非销售_月度理赔绩效,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA012' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA012'
    else null end as 新非销售_月度理赔绩效_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA013' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA013'
    else null end as 新非销售_中长期激励,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA014' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA014'
    else null end as 新非销售_中长期激励_占基薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA015' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA015'
    else null end as 新机构班子_月度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA016' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA016'
    else null end as 新机构班子_季度绩效_标准值,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA017' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA017'
    else null end as 新机构班子_月度绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA018' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA018'
    else null end as 新机构班子_季度绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA019' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA019'
    else null end as 新机构班子_年度目标薪酬,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA020' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA020'
    else null end as 新机构班子_诚信履约奖,   
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA021' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA021'
    else null end as 新机构班子_诚信履约奖_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA022' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA022'
    else null end as 新机构班子_月度理赔绩效,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA023' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA023'
    else null end as 新机构班子_月度理赔绩效_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA024' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA024'
    else null end as 新机构班子_中长期激励,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA025' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA025'
    else null end as 新机构班子_中长期激励_占目标年薪比例,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA026' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA026'
    else null end as 新公积金补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA027' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA027'
    else null end as 新定向性私车公用补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA028' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA028'
    else null end as 新住房补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA029' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA029'
    else null end as 新特殊津贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA030' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA030'
    else null end as 新异地派遣补贴,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA031' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA031'
    else null end as 新其他补贴1,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA032' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA032'
    else null end as 新其他补贴2,    
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA033' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA033'
    else null end as 新其他补贴3,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA034' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA034'
    else null end as 新其他补贴4,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA035' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA035'
    else null end as 新其他补贴5,
    case when aft_temp_salary_archive.after_payroll_item is not null and aft_temp_salary_archive.after_payroll_item::jsonb ? 'PA036' then
        aft_temp_salary_archive.after_payroll_item ->> 'PA036'
    else null end as 新机构班子_计薪月数    
from salary_adjustment
left join hr_employee emp on emp.id = salary_adjustment.employee_id
-- 业务单元
left join hr_business_unit origin_unit on origin_unit.id = salary_adjustment.business_unit_id
left join ir_translation as origin_unit_trans on origin_unit_trans.res_id = origin_unit.id and origin_unit_trans.lang='zh_CN' and origin_unit_trans.name='hr.business.unit,name'
-- 分部
left join hr_division origin_division on origin_division.id = salary_adjustment.division_id
left join ir_translation as origin_division_trans on origin_division_trans.res_id = origin_division.id and origin_division_trans.lang='zh_CN' and origin_division_trans.name='hr.division,name'
-- 总助分管部门
left join hr_department origin_asssitant_dep on origin_asssitant_dep.id = salary_adjustment.x_general_assistant_department
left join ir_translation as origin_asssitant_dep_trans on origin_asssitant_dep_trans.res_id = origin_asssitant_dep.id and origin_asssitant_dep_trans.lang='zh_CN' and origin_asssitant_dep_trans.name='hr.department,name'
-- 一级部门
left join hr_department origin_dep1 on origin_dep1.id = salary_adjustment.x_central_branch
left join ir_translation as origin_dep1_trans on origin_dep1_trans.res_id = origin_dep1.id and origin_dep1_trans.lang='zh_CN' and origin_dep1_trans.name='hr.department,name'
-- 二级部门
left join hr_department origin_dep2 on origin_dep2.id = salary_adjustment.x_central_branch_department
left join ir_translation as origin_dep2_trans on origin_dep2_trans.res_id = origin_dep2.id and origin_dep2_trans.lang='zh_CN' and origin_dep2_trans.name='hr.department,name'
-- 支公司部门
left join hr_department origin_branch_dep on origin_branch_dep.id = salary_adjustment.x_branch_department
left join ir_translation as origin_branch_dep_trans on origin_branch_dep_trans.res_id = origin_branch_dep.id and origin_branch_dep_trans.lang='zh_CN' and origin_branch_dep_trans.name='hr.department,name'
-- 部门
left join hr_department origin_dep on origin_dep.id = salary_adjustment.department_id
left join ir_translation as origin_dep_trans on origin_dep_trans.res_id = origin_dep.id and origin_dep_trans.lang='zh_CN' and origin_dep_trans.name='hr.department,name'
-- 岗位
left join hr_position origin_position on origin_position.id = salary_adjustment.position_id
left join ir_translation as origin_position_trans on origin_position_trans.res_id = origin_position.id and origin_position_trans.lang='zh_CN' and origin_position_trans.name='hr.position,name'
-- 职务
left join job_classification origin_job on origin_job.id = salary_adjustment.job_id
left join ir_translation as origin_job_trans on origin_job_trans.res_id = origin_job.id and origin_job_trans.lang='zh_CN' and origin_job_trans.name='job.classification,name'
-- 职务序列
left join job_sequence origin_job_sequence on origin_job_sequence.id = salary_adjustment.job_sequence_id
left join ir_translation as origin_job_sequence_trans on origin_job_sequence_trans.res_id = origin_job_sequence.id and origin_job_sequence_trans.lang='zh_CN' and origin_job_sequence_trans.name='job.sequence,name'
-- 职等
left join job_grade origin_job_grade on origin_job_grade.id = salary_adjustment.job_grade_id
left join ir_translation as origin_job_grade_trans on origin_job_grade_trans.res_id = origin_job_grade.id and origin_job_grade_trans.lang='zh_CN' and origin_job_grade_trans.name='job.grade,name'
-- 职级
left join job_level origin_job_level on origin_job_level.id = salary_adjustment.job_level_id
left join ir_translation as origin_job_level_trans on origin_job_level_trans.res_id = origin_job_level.id and origin_job_level_trans.lang='zh_CN' and origin_job_level_trans.name='job.level,name'

-- 调整前薪资项
left join (
    select 
        salary_adjustment.id as salary_adjust_id,
        json_object_agg(pre_archive.code, COALESCE(pre_salary_archive.amount, 0.00)) as payroll_item
    from salary_adjustment
    left join salary_adjustment_salary_archive as pre_salary_archive on pre_salary_archive.pre_salary_adjust_id = salary_adjustment.id
    left join payroll_archive_config as pre_archive on pre_archive.id = pre_salary_archive.payroll_item_id
    -- left join ir_translation as pre_archive_trans on pre_archive_trans.res_id = pre_archive.id and pre_archive_trans.lang='zh_CN' and pre_archive_trans.name='payroll.archive.config,name'
    where salary_adjustment.x_bp_state = 'done' and salary_adjustment.active = true
    group by salary_adjustment.id
) as temp_salary_archive on temp_salary_archive.salary_adjust_id = salary_adjustment.id

-- 调整后薪资项
left join (
    select 
        salary_adjustment.id as salary_adjust_id,
        json_object_agg(aft_archive.code, COALESCE(aft_salary_archive.amount, 0.00)) as after_payroll_item
    from salary_adjustment
    left join salary_adjustment_salary_archive as aft_salary_archive on aft_salary_archive.aft_salary_adjust_id = salary_adjustment.id
    left join payroll_archive_config as aft_archive on aft_archive.id = aft_salary_archive.payroll_item_id
    where salary_adjustment.x_bp_state = 'done' and salary_adjustment.active = true
    group by salary_adjustment.id
) as aft_temp_salary_archive on aft_temp_salary_archive.salary_adjust_id = salary_adjustment.id
where salary_adjustment.x_bp_state = 'done' and salary_adjustment.active = true