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.
