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
PER_PEOPLE_F
- Contains information about individuals in the organization.
- Key columns:
PERSON_ID
,FIRST_NAME
,LAST_NAME
,DATE_OF_BIRTH
,GENDER
, etc.
PER_JOBS_F
- Stores job information for employees.
- Key columns:
JOB_ID
,JOB_NAME
,DEPARTMENT_ID
,LOCATION_ID
,START_DATE
,END_DATE
, etc.
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.
PER_LOCATIONS
- Contains location details.
- Key columns:
LOCATION_ID
,LOCATION_NAME
,ADDRESS
,CITY
,STATE
,COUNTRY
, etc.
PER_DEPARTMENTS
- Information about departments within the organization.
- Key columns:
DEPARTMENT_ID
,DEPARTMENT_NAME
,MANAGER_ID
,LOCATION_ID
, etc.
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.
PAY_PAYROLLS_F
- Contains payroll run information.
- Key columns:
PAYROLL_ID
,PAYROLL_NAME
,PAYROLL_STATUS
,PAYROLL_RUN_DATE
, etc.
PER_GRADES
- Details of job grades and salary ranges.
- Key columns:
GRADE_ID
,GRADE_NAME
,MIN_SALARY
,MAX_SALARY
, etc.
PER_BENEFIT_PROGRAMS
- Information about benefit programs available to employees.
- Key columns:
BENEFIT_PROGRAM_ID
,PROGRAM_NAME
,PROGRAM_TYPE
,START_DATE
,END_DATE
, etc.
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.
No comments:
Post a Comment