Oracle HR / Payroll Important Tables

oracle apps / EBS  R12

Oracle HR / Payroll Important 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 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.

PAY_COSTS

The Costing  process populates the PAY_COSTS table with the run result value (costed value).

PAY_COST_ALLOCATION_KEYFLEX

Cost code is stored on the PAY_COST_ALLOCATION_KEYFLEX table in the ‘concatenated_segments’ column, this is referenced using the ‘cost_allocation_keyflex_id’ from PAY_COSTS.

 Example costing Query: 

select pav.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,pca.CONCATENATED_SEGMENTS,
decode(pc.DEBIT_OR_CREDIT,’D’,pc.COSTED_VALUE) Debit,
decode(pc.DEBIT_OR_CREDIT,’C’,pc.COSTED_VALUE) Credit
from
per_people_f ppf,
pay_assignment_actions_v pav,
pay_costs pc,
PAY_COST_ALLOCATION_KEYFLEX pca,
pay_element_types_f pet,
pay_run_results prr,
pay_run_result_values prrv
where ppf.EMPLOYEE_NUMBER=pav.ASSIGNMENT_NUMBER
and trunc(pav.EFFECTIVE_DATE) between :p_start_date and :p_end_date
–and pav.PERIOD_NAME = ‘4 2013 Calendar Month’
and pav.ASSIGNMENT_ACTION_ID=pc.ASSIGNMENT_ACTION_ID
and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID
and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID
and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and :p_end_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
and :p_end_date between ppf.EFFECTIVE_START_DATE and ppf.EFFECTIVE_END_DATE