drop view employee_view;
--员工视图
  CREATE OR REPLACE VIEW employee_view AS
  SELECT distinct
    he.id AS "id",
    he.employee_number AS "employee_number",
    he.name AS "name",
    he.username AS "username",
    grs.id AS "gender_id",
    grs.code AS "gender_code",
    COALESCE(ir_grs.value, grs.name, '') AS "gender_name",
    per_pp.date_of_birth AS "date_of_birth",
    rn.id AS "nation",
    rn.code AS "nation_code",
    rn.name AS "nation_name",
    crs.id AS "card_type",
    crs.code AS "card_type_code",
    COALESCE(ir_crs.value, crs.name, '') AS "card_type_name",
    per_pp.national_id AS "national_id",
    he.email AS "email",
    he.email AS "original_start_date",
    hl.id AS "location_id",
    hl.code AS "location_id_code",
    COALESCE(ir_hl.value, hl.name, '') AS "location_id_name",
    es.id AS "employee_status",
    es.code AS "employee_status_code",
    COALESCE(ir_es.value, es.name, '') AS "employee_status_name",
    et.id AS "employee_type",
    et.code AS "employee_type_code",
    COALESCE(ir_et.value, et.name, '') AS "employee_type_name",
    hbu.id AS "business_unit_id",
    hbu.code AS "business_unit_code",
    COALESCE(ir_hbu.value, hbu.name, '') AS "business_unit_name",
    hd.id AS "department_id",
    hd.code AS "department_code",
    COALESCE(ir_hd.value, hd.name, '') AS "department_name",
    hp.id AS "position_id",
    hp.code AS "position_code",
    COALESCE(ir_hp.value, hp.name, '') AS "position_name",
    mhe.id AS "manager_employee_id",
    mhe.employee_number AS "manager_employee_id_employee_number",
    mhe.name AS "manager_employee_id_name",
    he.hire_date AS "hire_date",
    he.termination_date AS "termination_date",    
    he.x_phone AS "x_phone",
    he.image_url AS "image_url",
    he.business_phone AS "business_phone",
    he.sequence AS "sequence",
    ej.legal_entity_id AS "legal_entity_id",
    ej.job_level AS "job_level",
jl.code as "job_level_code",
    pss.code As "political_status"
  FROM per_personal pp    
  LEFT JOIN hr_employee he ON pp.person_id=he.person_id  
  LEFT JOIN res_selection grs ON pp.gender=grs.id    
  LEFT JOIN ir_translation ir_grs
    ON ir_grs.res_id = grs.id AND
       ir_grs.name = 'res.selection,name' AND
       ir_grs.lang = 'zh_CN'  
  LEFT JOIN res_nation rn ON pp.nation=rn.id    
  LEFT JOIN per_person per_pp ON pp.person_id=per_pp.id    
  LEFT JOIN res_selection crs ON per_pp.card_type=crs.id    
  LEFT JOIN ir_translation ir_crs
    ON ir_crs.res_id = crs.id AND
       ir_crs.name = 'res.selection,name' AND
       ir_crs.lang = 'zh_CN'               
  LEFT JOIN emp_job ej ON he.id=ej.employee_id and ej.eroad_current_record_id is not null 
  LEFT JOIN employee_status es ON ej.employee_status=es.id 
  LEFT JOIN ir_translation ir_es
    ON ir_es.res_id = es.id AND
    ir_es.name = 'employee.status,name' AND
    ir_es.lang = 'zh_CN'     
  LEFT JOIN employee_type et ON ej.employee_type=et.id
  LEFT JOIN ir_translation ir_et
    ON ir_et.res_id = et.id AND
    ir_et.name = 'employee.type,name' AND
    ir_et.lang = 'zh_CN'     
  LEFT JOIN hr_department hd ON ej.department_id=hd.id and hd.eroad_current_record_id is not null 
  LEFT JOIN ir_translation ir_hd
    ON ir_hd.res_id = hd.id AND
    ir_hd.name = 'hr.department,name' AND
    ir_hd.lang = 'zh_CN'    
  LEFT JOIN hr_business_unit hbu ON ej.business_unit_id=hbu.id and hbu.eroad_current_record_id is not null 
  LEFT JOIN ir_translation ir_hbu
    ON ir_hbu.res_id = hbu.id AND
    ir_hbu.name = 'hr.business.unit,name' AND
    ir_hbu.lang = 'zh_CN'     
  LEFT JOIN hr_position hp ON ej.position_id=hp.id and hp.eroad_current_record_id is not null 
  LEFT JOIN ir_translation ir_hp
    ON ir_hp.res_id = hp.id AND
    ir_hp.name = 'hr.position,name' AND
    ir_hp.lang = 'zh_CN'    
  LEFT JOIN hr_location hl ON ej.location_id=hl.id
  LEFT JOIN ir_translation ir_hl
    ON ir_hl.res_id = hl.id AND
       ir_hl.name = 'hr.location,name' AND
       ir_hl.lang = 'zh_CN' 
  LEFT JOIN hr_employee mhe ON ej.manager_employee_id=mhe.id
  LEFT JOIN political_status pss on pp.political_status=pss.id    
   LEFT JOIN job_level jl ON ej.job_level=jl.id
  where 
   pp.eroad_current_record_id is not null
   AND pp.eroad_start_date <= current_date
   and he.id is not null
  order by employee_number 

;



