Employee Listing Report (SQL Script)

Human Capital Management (HCM)
1

This employee listing report query retrieves current employee detials like hire date, Title, job, employee status, expense account, employee location, details, helping HR teams track the employees details.

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project) 
 --This query retrieves employee details
  DISTINCT HAOU.NAME ORGANIZATION_NAME,
  PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
  PEX.FULL_NAME EMPLOYEE_NAME,
  PAPF.START_DATE HIRE_DATE,
  PEX.TITLE EMPLOYEE_TITLE,
  PEX.EMAIL_ADDRESS EMPLOYEE_EMAIL_ADDRESS,
  PJF.FULL_PART_TIME JOB_TYPE,  
  PAAM.ASSIGNMENT_NUMBER EMPLOYEE_ASSIGNMENT_NUMBER,
  PAAM.ASSIGNMENT_TYPE EMPLOYEE_ASSIGNMENT_TYPE,
  PJF.JOB_CODE,
  PJF.ACTIVE_STATUS EMPLOYEE_STATUS,
  PJF.BENCHMARK_JOB_FLAG, 
  NVL((SELECT 
  GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 || '-' || GCC.SEGMENT5 || '-' || GCC.SEGMENT6
  FROM   GL_CODE_COMBINATIONS GCC 
  WHERE PEX.DEFAULT_CODE_COMB_ID = GCC.CODE_COMBINATION_ID),'No Expense Account') EXPENSE_ACCOUNT,
  HLA.ADDRESS_LINE_1,
  HLA.ADDRESS_LINE_2,
  HLA.ADDRESS_LINE_3,
  HLA.ADDRESS_LINE_4,
  HLA.FLOOR_NUMBER,
  HLA.BUILDING,
  HLA.REGION_1,
  HLA.REGION_2,
  HLA.REGION_3,
  HLA.TOWN_OR_CITY,
  HLA.COUNTRY,
  HLA.POSTAL_CODE,
  HLA.LONG_POSTAL_CODE
FROM
  PER_ALL_PEOPLE_F PAPF,
  PER_ALL_ASSIGNMENTS_M PAAM,
  PER_EMPLOYEES_X PEX,
  PER_JOBS_F PJF,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  HR_LOCATIONS_ALL HLA
WHERE 1=1
  AND PAPF.PERSON_ID = PAAM.PERSON_ID
  AND PAPF.PERSON_ID = PEX.PERSON_ID
  AND PAAM.JOB_ID = PJF.JOB_ID(+)
  AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
  AND PEX.LOCATION_ID = HLA.LOCATION_ID(+)
  AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE  AND PAAM.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE  AND PAPF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PJF.EFFECTIVE_START_DATE   AND PJF.EFFECTIVE_END_DATE
 ORDER BY PAPF.PERSON_NUMBER