SQL Queries Tables in Oracle Fusion HCM

 In Oracle Fusion Human Capital Management (HCM), various tables store information related to employees, payroll, benefits, and other HR functions. Here are some key tables in Oracle Fusion HCM and sample SQL queries to illustrate how to retrieve data from these tables:

Key Tables in Oracle Fusion HCM

1. PER_PEOPLE_F

   • Contains information about individuals in the organization.
   • Key columns: PERSON_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, GENDER, etc.

2. PER_JOBS_F

   • Stores job information for employees.
   • Key columns: JOB_ID, JOB_NAME, DEPARTMENT_ID, LOCATION_ID, START_DATE, END_DATE, etc.

3. PER_ASSIGNMENTS_F

   • Details of employee assignments including job, department, and location.
   • Key columns: ASSIGNMENT_ID, PERSON_ID, JOB_ID, DEPARTMENT_ID, LOCATION_ID, ASSIGNMENT_STATUS, etc.

4. PER_LOCATIONS

   • Contains location details.
   • Key columns: LOCATION_ID, LOCATION_NAME, ADDRESS, CITY, STATE, COUNTRY, etc.

5. PER_DEPARTMENTS

   • Information about departments within the organization.
   • Key columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID, etc.

6. PAY_ELEMENT_ENTRIES_F

   • Stores payroll element entries, including earnings and deductions.
   • Key columns: ELEMENT_ENTRY_ID, PERSON_ID, ELEMENT_ID, PAYMENT_AMOUNT, DEDUCTION_AMOUNT, START_DATE, END_DATE, etc.

7. PAY_PAYROLLS_F

   • Contains payroll run information.
   • Key columns: PAYROLL_ID, PAYROLL_NAME, PAYROLL_STATUS, PAYROLL_RUN_DATE, etc.

8. PER_GRADES

   • Details of job grades and salary ranges.
   • Key columns: GRADE_ID, GRADE_NAME, MIN_SALARY, MAX_SALARY, etc.

9. PER_BENEFIT_PROGRAMS

   • Information about benefit programs available to employees.
   • Key columns: BENEFIT_PROGRAM_ID, PROGRAM_NAME, PROGRAM_TYPE, START_DATE, END_DATE, etc.

10. PER_LEAVE_ENTITLEMENTS

    • Contains data on employee leave entitlements.
    • Key columns: ENTITLEMENT_ID, PERSON_ID, LEAVE_TYPE, ENTITLEMENT_AMOUNT, BALANCE, etc.

Sample SQL Queries

1. Retrieve Basic Employee Information

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    p.DATE_OF_BIRTH,
    j.JOB_NAME,
    d.DEPARTMENT_NAME,
    l.LOCATION_NAME
FROM
    PER_PEOPLE_F p
    JOIN PER_ASSIGNMENTS_F a ON p.PERSON_ID = a.PERSON_ID
    JOIN PER_JOBS_F j ON a.JOB_ID = j.JOB_ID
    JOIN PER_DEPARTMENTS d ON a.DEPARTMENT_ID = d.DEPARTMENT_ID
    JOIN PER_LOCATIONS l ON a.LOCATION_ID = l.LOCATION_ID
WHERE
    p.ACTIVE = 'Y'
ORDER BY
    p.LAST_NAME;

2. Get Payroll Information for Employees

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    e.ELEMENT_ID,
    e.PAYMENT_AMOUNT,
    e.DEDUCTION_AMOUNT,
    pa.PAYROLL_NAME,
    pa.PAYROLL_RUN_DATE
FROM
    PAY_ELEMENT_ENTRIES_F e
    JOIN PER_PEOPLE_F p ON e.PERSON_ID = p.PERSON_ID
    JOIN PAY_PAYROLLS_F pa ON e.PAYROLL_ID = pa.PAYROLL_ID
WHERE
    pa.PAYROLL_RUN_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    p.LAST_NAME;

3. Retrieve Leave Entitlements for Employees

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    l.LEAVE_TYPE,
    l.ENTITLEMENT_AMOUNT,
    l.BALANCE
FROM
    PER_LEAVE_ENTITLEMENTS l
    JOIN PER_PEOPLE_F p ON l.PERSON_ID = p.PERSON_ID
WHERE
    1 = 1
ORDER BY
    p.LAST_NAME;

4. Get Job Information and Salary Grades

SELECT
    j.JOB_ID,
    j.JOB_NAME,
    g.GRADE_NAME,
    g.MIN_SALARY,
    g.MAX_SALARY
FROM
    PER_JOBS_F j
    JOIN PER_GRADES g ON j.GRADE_ID = g.GRADE_ID
ORDER BY
    j.JOB_NAME;

5. List Employees and Their Benefit Programs

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    b.PROGRAM_NAME,
    b.PROGRAM_TYPE
FROM
    PER_PEOPLE_F p
    JOIN PER_BENEFIT_PROGRAMS b ON p.PERSON_ID = b.PERSON_ID
ORDER BY
    p.LAST_NAME, b.PROGRAM_NAME;

Notes

• Refer to the Oracle Fusion HCM implementation documentations for the latest updates. 
• For large datasets, consider optimizing queries with appropriate indexing and filtering to improve performance.

These tables and queries should help you manage and analyze various aspects of HCM data within Oracle Fusion Applications and develop the objects accordingly.