Know Oracle Payroll Tables

All the HRMS/Payroll tables that are date-tracked will must have two columns:-

Effective_start_date

Effective_end_date


All the objects in Oracle HRMS or Payroll that end with _x have a where clause where sysdate between Effective_start_date AND Effective_end_date, therefore you can use the views that return records as of SYSDATE.

The primary keys of the date tracked columns includes Effective_start_date & Effective_end_date.

PAY_ELEMENT_TYPES_F - Payroll Elements

Firstly, we create some elements, which get created in table PAY_ELEMENT_TYPES_F. The primary key is a combination of element_type_Id along with Date Track columns.

When will you join to pay_element_types_f
To display the name of Element in Reports or when you need develop adhoc query for payroll runs. Normally when Payroll is run , the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.

2.PAY_ELEMENT_LINKS_F - Payroll Element Links

This table make payroll elements eligible to a group of people, you create Element Links.The Primary key is ELEMENT_LINK_ID with date-track columns.

When will you commonly use element_link_Id ?

1. When querying on Element Entry[PAY_ELEMENT_ENTRIES_F], a join can be made using ELEMENT_LINK_ID

2. The reason Oracle uses ELEMENT_LINK_ID in Element Entry to work out Costing Segments based on Payroll Costing Hierarchy.

3.PER_ALL_PEOPLE_F - Employee record

It is well known that Employee records are stored in PER_ALL_PEOPLE_F. Its a date track table with primary key being person_Id. This table also has party_Id, because Oracle creates a party in TCA as soon as a record in per_all_people_f gets created.

Main usage of per_all_people_f:-

1. To get the name of the person
2. To get the date of birth or tax Id of the person
Note:- The application uses PER_PEOPLE_F, as that is a secured view layer on top of PER_ALL_PEOPLE_F

4.PER_ALL_ASSIGNMENTS_F - Assignment table

This is the most central table in Oracle Payroll. Payroll engine uses this table as the main driver.

Why so: Because Element Entries are stored against Assignment record.

This table is date-tracked, with primary key being assignment_Id

Usage of per_all_assignments_f?

1. Find position_Id, hence position, or grade, the organization for the persons assignment.

2. It has foreign key to person_id. Each person Id can have no more than one primary assignment at any given point in time.

3. Pay run results and also the pay_assignment actions refers to this table.

5.PER_PERSON_TYPES - Person type

This is the master table for Person Types. Some examples of Person Types are Employees, Casuals, Applicants etc.

The primary key is person_type_id.

But please do not try joining this with person_type_id in per_all_people_f.

Instead join that to per_person_type_usages_f

_x will give you person_type usage as of SYSDATE.

For any other date, use the classic p_date between effective_start_date and effective_end_date.
6.PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F

This Tables effected when element entry is done,two tables get inserted into when fresh Element Entries are created.

PAY_ELEMENT_ENTRIES_F

Each Element that gets attached to an Assignment will have an entry in PAY_ELEMENT_ENTRIES_F.

For each assignment you will have one or more records in PAY_ELEMENT_ENTRIES_F table.

It is logical that PAY_ELEMENT_ENTRIES_F has following columns

Assignment_id

Element_link_id

ELEMENT_TYPE_ID

This table is date-tracked too. Please do not ask my where there was a need to store both ELEMENT_TYPE_ID and also ELEMENT_LINK_ID in this table.

Just storing the ELEMENT_LINK_ID could suffice. However, i guess Oracle did so for Performance reasons.

7.PAY_ELEMENT_ENTRY_VALUES_F

This table stores a reference to PAY_ELEMENT_ENTRIES_F.

This table is date-tracked, and its primary key is INPUT_VALUE_ID.

Hope this helps