<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
This website has now moved to http://apps2fusion.com





Oracle HRMS Payroll Tables | Print |  E-mail
Written by Anil Passi   
Tuesday, 12 December 2006
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/


I hope this covers some key tables in Oracle HRMS and Payroll. However if you wish to add to this list, please do so via comments.



Comments (19)add
...
written by Gayathri , December 13, 2006
THanks Anil! I guess this Basic Article will help people who need to learn Oracle HRMS afresh in their projects !Atleast readin your article gives me some knowledge on some new tables and interest to read more articles.

Regards
Gayathri
...
written by balkrishna.bihade , December 13, 2006
no one can give explanation as simple as this

i have got what i am searching for

thanks
...
written by Gayathri , December 13, 2006
THanks Anil! I guess this Basic Article will help people who need to learn Oracle HRMS afresh in their projects !Atleast readin your article gives me some knowledge on some new tables and interest to read more articles.

Regards
Gayathri
...
written by balkrishna.bihade , December 13, 2006
no one can give explanation as simple as this

i have got what i am searching for

thanks
...
written by Sastry , December 15, 2006
Thanks Anil for the info. I got clear idea on some of these tables. Can you eloborate on assignment set and element set and where they are applied. what is the significance of the views ending with _tl and _v

Thanks
Sastry
...
written by Sastry , December 15, 2006
Thanks Anil for the info. I got clear idea on some of these tables. Can you eloborate on assignment set and element set and where they are applied. what is the significance of the views ending with _tl and _v

Thanks
Sastry
...
written by Wei Siang , December 16, 2006
Keep up the good postings, Anil! I have really learned a lot from reading your blog.
...
written by Wei Siang , December 16, 2006
Keep up the good postings, Anil! I have really learned a lot from reading your blog.
...
written by Parag M , January 02, 2007
Pay_action_interlocks, pay_action_parameters (Payroll Parameters) are some of the imp Payroll tables.
...
written by Parag M , January 02, 2007
Pay_action_interlocks, pay_action_parameters (Payroll Parameters) are some of the imp Payroll tables.
...
written by Kumar , May 21, 2007
Sir, Simply superb explanation.
can you also explain something about balances and also about taxes...
Sir, can we find any document which explains the tables and their columns description in HRMS.
In ETRM, its not clear.
Thanks
--Kumar
...
written by Soma , June 26, 2007
Hi Anil,
I have gone thorugh the useful links provided by you regarding the Oracle Payroll Tables. I am a business analyst and would want to get some help regarding the tables that I should refer with respect to the Payroll Setup or Payroll Process Configuration. From the list of Oracle Payroll and HRMS tables provided by you, which are the tables used for the Payroll process setup?

I would be grateful to you if you help me out in this.

Regards,
Soma
...
written by Anil Passi , June 26, 2007
Hi Soma

You can check the record history in those config screens and that will tell you the name of table being used by that particular screen.

Thanks
Anil
...
written by harikishan , July 08, 2007
Hi Anil

I find your articles are very useful.I have the following doubts in Payroll.

1)where is Employee attendance is taken into consideration while calculating payroll? In which screen or what is the methodology to capture the employee attendance and leaves Oracle HRMS and linked with Payroll?

2)How to specify the payroll period Ex: If my payroll period is 15th of every month to 15th of next month. Do we have to define a calender for this If so, where to define ?

Your help would be highly appreciated.
Thanks
Hari
...
written by mohan , July 16, 2007
Hi Anil,
I am working on HRMS payroll.the present doubt i got over here is i would like to find the employee monthly salary (it may be any month of any year).What are the main considerations.what are the things i have to follow when i am calculating this.
Regards & Thanks
Mohan.katepalli
...
written by ramesh , August 24, 2007
hi anil,
this is ramesh,i want to know the api tables in HRMS. can you help me regarding this.
...
written by yasser , September 06, 2007
Dear Anil,
I run the Payroll of an assignment set contains 9 employees it gives me result differs from the result i got by running the Quick Pay for the same employees with the same sequence
in the Payroll Run , Do you have any Idea.
Regards,
Yasser
need help from u plz mr.Anil
written by Ahmed_hassan , December 26, 2007
i need to know all the name of all the lookup for example
i want to know where that field take the data from whitc lookup ?
is that in ur hand my friend ?
plz send to me on my email and thx
Oracle HRMS CoreHr Material
written by vmrathnam , March 01, 2008
Hi anil,
this is Rathnam,i want to core HR Material can you help me regarding this.
You must be logged in to a comment. Please register if you do not have an account yet.

busy