Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Join us on Facebook
  • Register

Search Courses

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.

Anil Passi

Comments   

+1 #1 Gayathri 2006-12-13 00:00
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.

Re gards
Gayathri
Quote
0 #2 balkrishna.bihade 2006-12-13 00:00
no one can give explanation as simple as this

i have got what i am searching for

thanks
Quote
+1 #3 Gayathri 2006-12-13 00:00
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.

Re gards
Gayathri
Quote
0 #4 balkrishna.bihade 2006-12-13 00:00
no one can give explanation as simple as this

i have got what i am searching for

thanks
Quote
0 #5 Sastry 2006-12-15 00:00
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
Sas try
Quote
0 #6 Sastry 2006-12-15 00:00
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
Sas try
Quote
0 #7 Wei Siang 2006-12-16 00:00
Keep up the good postings, Anil! I have really learned a lot from reading your blog.
Quote
0 #8 Wei Siang 2006-12-16 00:00
Keep up the good postings, Anil! I have really learned a lot from reading your blog.
Quote
0 #9 Parag M 2007-01-02 00:00
Pay_action_inte rlocks, pay_action_para meters (Payroll Parameters) are some of the imp Payroll tables.
Quote
0 #10 Parag M 2007-01-02 00:00
Pay_action_inte rlocks, pay_action_para meters (Payroll Parameters) are some of the imp Payroll tables.
Quote
0 #11 Kumar 2007-05-21 00:00
Sir, Simply superb explanation.
c an 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
Quote
0 #12 Soma 2007-06-26 00:00
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.

Regard s,
Soma
Quote
0 #13 Anil Passi 2007-06-26 00:00
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.

Than ks
Anil
Quote
0 #14 harikishan 2007-07-08 17:18
Hi Anil

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

1)whe re 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.
Th anks
Hari
Quote
0 #15 mohan 2007-07-16 12:26
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.ka tepalli
Quote
0 #16 SachinShirke 2007-08-24 14:09
hi anil,
this is ramesh,i want to know the api tables in HRMS. can you help me regarding this.
Quote
0 #17 yasser 2007-09-06 06:25
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
Quote
0 #18 Ahmed_hassan 2007-12-26 12:31
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
Quote
0 #19 vmrathnam 2008-03-01 10:55
Hi anil,
this is Rathnam,i want to core HR Material can you help me regarding this.
Quote
0 #20 vinay kumar 2009-06-06 08:47
HI Anil,

i need help from u..which table holds the earnings and deduction values..b'coz i've a requirement to calculate the netpay..i'm unable to find a table to get that payroll component values...plz do the need full.. thanx in advance
Quote
0 #21 hussain43 2010-01-07 05:08
Hi anil.

Please briefly expalin how to add earning,casual leaves columns in hrms.
Thanx in advance.
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner