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_FThis 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/
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_FThis 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/
No comments:
Post a Comment