Oracle HRMS Payroll Tables
In this article I will try to cover some important tables in Oracle HRMS and Oracle Payroll. The idea is to get you started technically with minimal Functional information. Once the basic knowledge is there, advanced knowledge can be built upon.
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.
See Elements Basics article that explains why Element Links are necessary.
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 showHH: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.
What is the difference between quick-pay and payroll run?
Quickpay is a functionality(available from assignment screen), by which you can run the payroll for single person assignment. It uses the same executable as that of actual Payroll run.
PAY_PAYROLL_ACTIONS - What is a payroll action?
Well, just about anything you make the Oracle Payroll engine do, it records an entry in PAY_PAYROLL_ACTIONS.
What are the possible actions?
To name a few:-
Costing
Quickpay
Payroll Run
Magnetic Transfer [synonym to EOY-End of Year run]
Transfer to GL......etc
A column named ACTION_TYPE [validated by lookup type ACTION_TYPE] is used to store the type of action.
Why does PAY_PAYROLL_ACTIONS contain PAYROLL_ID, ELEMENT_SET_ID and ASSIGNMENT_SET_ID?
The Payroll process[conc short name PYUGEN] can be run for a specific Payroll i.e. for people enrolled to Monthly or Weekly payroll.
Hence Payroll_id is stored to capture parameter details.
A payroll action can also be restricted to a specific group of Elements, via ELEMENT_SET_ID.
A group of Elements are defined using a table PAY_ELEMENT_SETS, for example you wish to group all bonus related elements together.
Ditto with ASSIGNMENT_SET_ID, as that identifies a set of Assignment Records.
Assignment sets can be defined on the basis of criteria[HR_ASSIGNMENT_SET_CRITERIA] or assignments can be manually added to Assignment set[HR_ASSIGNMENT_SET_AMENDMENTS]
pay_payroll_actions does not store reference to the assignment record.
Which Assignment Records were included in Payroll Actions :- PAY_ASSIGNMENT_ACTIONS
pay_assignment_actions contains an entry for each Assignment_id that was eligible during Payroll Action.
For example if you run a quickpay, an entry for that specific assignment_id will be created in pay_assignment_actions.
Obviously this table has a column named assignment_id.
You can drill down from Assignment Action screen to view Payroll Run Results and Payroll Balances, for the specific assignment.
Hence both PAY_RUN_BALANCES and PAY_RUN_RESULTS reference ASSIGNMENT_ACTION_ID.
NOTE: ASSIGNMENT_ACTION_ID is the primary key of PAY_ASSIGNMENT_ACTIONS.
Also note that entries in this table are created by Concurrent Processes, hence this table is never updated by end user from screens. Hence there is no date-tracking on this table.
What is payroll run results?
As you would know, when payroll process runs, it reads the element entries for the assignment in pay_assignment_actions. For those element entries, payroll engine either uses the entry value in "pay value" or it kicks off a fast formula if the element has a ff attached to that. The end result is that each eligible element gets a result. These results are stored in pay run result tables.
What are the payroll run results tables?
Pay_run_results
Pay_run_result_values
Obviously the values are stored per element in Pay_run_results.
The input value used/derived by payroll engine is stored in Pay_run_result_values/
