SQL Query to Extract Employee Information's in Oracle cloud: Oracle Cloud SQL query to Extract Employee Information's

SQL Query to Extract Employee Information's in Oracle cloud: Oracle Cloud SQL query to Extract Employee Information's

In this post , I am sharing the Oracle Cloud HCM Query to Extract Employee Information's. Oracle cloud Employee related tables are quite changed in Cloud as compared to Oracle ERP. Here below I am sharing these new HCM Cloud related tables to extract Employee Information's in Oracle Cloud.

 

 

 

8 Important Employees Table to Extract Employee Information in Oracle Cloud

1.per_all_people_f
2.per_all_assignments_m

3.per_jobs_f

4.per_jobs_f_tl
5.GL_CODE_COMBINATIONS
6.PER_EMAIL_ADDRESSES
7.per_assignment_supervisors_f
8.per_person_names_f
9.HR_ORGANIZATION_UNITS_F_TL

 

 

SQL Query to Extract Employee Information in Oracle Cloud

SELECT papf.person_number EMPLOYEE_NUMBER,
   pjft.name    jobname,
PAAM.ASSIGNMENT_NUMBER EMP_ASSIGNMENT_NUMBER,
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6 EXP_ACCOUNT,
PAAM.EFFECTIVE_START_DATE ASSG_EFFECTIVE_START_DATE,
PEA.EMAIL_ADDRESS,
   ppnf.full_name supervisor_name,
 papf2.person_number sUPERVISOR_NUMBER,
hauft.NAME BusinessUnit,
PAPF.START_DATE HIRE_DATE
FROM   per_all_people_f papf,
 per_all_people_f papf2,
       per_all_assignments_m paam,
       per_jobs_f pjf,
   per_jobs_f_tl pjft,
GL_CODE_COMBINATIONS GCC,
PER_EMAIL_ADDRESSES PEA,
 per_assignment_supervisors_f pasf,
  per_person_names_f ppnf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE  papf.person_id = paam.person_id

AND    TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

AND    paam.primary_assignment_flag = 'Y'

AND    paam.assignment_type = 'E'

and    paam.effective_latest_change = 'Y'
and paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id

AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

AND    paam.job_id = pjf.job_id

AND    TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date
AND PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
AND    papf.person_id = pasf.person_id

AND    pasf.manager_type = 'LINE_MANAGER'

AND    ppnf.person_id = pasf.manager_id

AND  papf2.person_id = pasf.manager_id

AND    TRUNC(SYSDATE) BETWEEN papf2.effective_start_date AND papf2.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

AND    pjf.job_id = pjft.job_id

AND hauft.NAME=nvl(:P_BU_NAME,hauft.NAME)

AND    pjft.language = 'US'
AND    ppnf.name_type = 'GLOBAL'

AND    TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND hauft.organization_id = paam.business_unit_id

order by papf.person_number asc,pjft.name asc nulls first