Oracle HRMS Payroll Tables

Oracle HRMS Payroll Tables

The tables that are date-tracked will 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

Hence these are 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 ?

1. To display the name of Element in Reports

2. When payroll runs, the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.

PAY_ELEMENT_LINKS_F - Payroll Element Links

To 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.

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

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.

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.

PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F - Tables effected when element entry is done

These two tables are 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.

PAY_ELEMENT_ENTRY_VALUES_F

This table stores a reference to PAY_ELEMENT_ENTRIES_F. In plain English, this table captures the entry value for the elements.

The Input Value is stored in SCREEN_ENTRY_VALUE. The name suggests that it stores the Formatted Screen value. However, I can assure you that SCREEN_ENTRY_VALUE stores the non formatted value. For example screen might show HH:MM as 03:30, but SCREEN_ENTRY_VALUE will have 3.5

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

Where can I commonly join INPUT_VALUE_ID to ?

To the payroll run results value table, i.e. PAY_RUN_RESULT_VALUES

You can also join to PAY_COSTS, if you wish to work out which input value contributed to a specific Payroll Costed Amount.