Category Archives: Oracle HRMS E-Business Suite

Oracle HRMS E-Business Suite 11i

How to Delete assignments and person in HRMS

Hi

The following code will help us out t delete an applicant/employee form the system.But if u are deleting the employee then make sure that
he/she must not attached to active payroll

CREATE OR REPLACE PROCEDURE xx_delete_person (p_person_id NUMBER)
IS
l_person_org_manager_warning VARCHAR2 (200);
l_message VARCHAR2 (4000);
BEGIN
fnd_file.put_line (fnd_file.output,
‘<—————–Purge Person—————->’
);
fnd_file.put_line (fnd_file.output,
‘On Date: ‘ || TO_CHAR (SYSDATE, ‘DD/MON/YY’)
);
fnd_file.put_line (fnd_file.output, ‘Person Id:’ || p_person_id);
hr_person_api.delete_person
(p_validate => FALSE,
p_effective_date => SYSDATE,
p_person_id => p_person_id,
p_perform_predel_validation => FALSE,
p_person_org_manager_warning => l_person_org_manager_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_message := SUBSTR (SQLERRM, 1, 4000);
fnd_file.put_line
(fnd_file.output,
‘<—————– Purge Person On Error —————->’
);
fnd_file.put_line (fnd_file.output,
‘On Date: ‘ || TO_CHAR (SYSDATE, ‘DD/MON/YY’)
);
fnd_file.put_line (fnd_file.output, ‘Person Id:’ || p_person_id);
fnd_file.put_line (fnd_file.output, ‘Error Rreason :’ || l_message);
END xx_delete_person;

I hope this will may help you.

Thank You, Muzzamil Khan

muzzamil.khan@gmail.com

About Oracle Fast Formula

Oracle FastFormula is a simple way to write formulas using English words & basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language.

Common tables
SELECT *
FROM all_objects
WHERE object_type = ‘TABLE’ AND object_name LIKE ‘FF%’

FF_FUNCTIONS
FF_FUNCTION_PARAMETERS
FF_FORMULAS_F
FF_FORMULA_TYPES
FF_DATABASE_ITEMS
FF_GLOBALS_F

Uses of Oracle FastFormula
In HRMS, Oracle FastFormula is used for validation, to perform calculations, and to specify rules. Here are some examples.

In Payroll, you use formulas to:
• Validate element inputs
• Calculate element pay values and run results during payroll processing
• Specify the rules for skipping an element during payroll processing
• Perform legislative checks during a payroll run

In Compensation and Benefits Management, you use formulas to:
• Specify the rules for Paid Time Off accrual plans, such as how much time is accrued and over what period, when new starters are eligible to begin accruing time, and how much time can be carried over to the next accrual term.
• Define custom calculations for benefits administration.
• Calculate the duration of an absence given the start and end dates and times.
• Create rules for benefits administration such as eligibility determination.

In People Management, fast formulas are used to:
• Check that element entry values are valid for an assignment
• Specify the criteria for including an assignment in an assignment set and to edit assignment sets
• Configure the people management templates in a number of ways such as supplying additional information to be available from fields on the template and validating field entries.
• Define collective agreements
• Generate custom global person number sequences for employees, applicants, and contingent workers.

Components of Formulas
Formulas are made up of a number of different components. These can include assignment statements, different types of input including database items, functions, nested expressions, and conditions.

1) Assignment and Return Statements.
To start with a simple example, suppose you wanted to calculate the pay value for the element Wage by multiplying the number of hours an employee works each week by hourly rate. You could write this formula:
wage = hours_worked * hourly_rate
RETURN wage
The first line is an Assignment statement that simply assigns a value to the element Wage. The second line is a Return statement that passes back the Wage value to the payroll run.

2) Constants and Variables.
In this example, the Wage value is calculated, but it could be a constant value, such as: wage = 200. To calculate the Wage value, Oracle FastFormula needs to get values for the variables hours_worked and hourly_rate.

3) Data Types.
Both variables and constants can be one of three data types:
• Numeric
• Text
• Date

4) Types of Input.
Values for the variables hours_worked and hourly_rate can be populated using three ways:
• Receiving them as input when the formula is called.
• Finding the values in the database from database items.
• Using global values, which you enter in the Globals window.

To use a database item or global value in your formula, you simply refer to it by name. You can browse through lists of database items in the Formulas window. To use a value passed to the formula at run time, you must write an Inputs statement.

 

Input Statements
In our Wage example, suppose that hours_worked is an input value to the element Wage. To pass the element input values to the formula during a payroll run, you define
an Inputs statement, as follows:
INPUTS ARE hours_worked
wage = hours_worked * hourly_rate
RETURN wage
The name you use in the Inputs statement must be the same as the name of the element input value, and multiple words must be joined by underscores. In this example, the input value hours_worked is numeric. If the input value is not numeric, you must tell Oracle FastFormula whether it is text or date. For example:
INPUTS ARE start_date (date)

 

Database Items
Suppose that hourly_rate is a standard rate taken from the Grade Rates table. This is an example of a database item. A database item has a label, or special piece of code, telling Oracle FastFormula the path to take to access the data. These items include both information unique to your enterprise, which you hold in flexfield segments & standard information such as assignment numbers and grades etc. In the Formulas window, you pick database items from a list. There are two types of DB Items: Static and Dynamic.

 

 

Static database items are shipped with the system and you cannot modify them.
Dynamic database items are created by Oracle HRMS processes whenever you define new elements or other related entities.

Element Database Items:
When you define a new element, Oracle HRMS runs a process to create a number of related database items for it. To ensure easy recognition of these items, the process adds the element name to each one. It also creates further database items for each pay and input value you use .

Global Variables
Use global values to store information that does not change often, but you refer to frequently, such as Company Name, or company-wide percentages used to calculate certain types of bonus. You define the global value and change its value using the Globals window.

Local Variables
Local variables exist in one formula only. You can change the value of a local variable by assigning it a value in an Assignment statement. In the Wage example, the variable wage itself is a local variable. It receives a value within the formula by the Assignment statement:
wage = hours_worked * hourly_rate

Functions
Oracle FastFormula provides functions that manipulate data in different ways.
GREATEST,INITCAP,LEAST LENTH,INSTR,LOWER,RTRIM,LTRIM,ABS,REPLACE SUBSTRING,TRANSLATE, CALCULATE_HOURS_WORKED,FLOOR,ROUND,TRUNC, ETC( For full list refer the Fast Formula Guide).
There are special functions that convert variables from:
• numbers to text (TO_TEXT)
• dates to text (TO_TEXT)
• text to date (TO_DATE)
• text to number (TO_NUM)

Nested Expressions
Each function or calculation is one expression, and you can nest expressions to create more complex calculations. You must use brackets to make clear to Oracle FastFormula the order in which the calculations are performed. For example:
ANNUAL_BONUS = trunc(((((salary_amount/100)*
bonus_percentage)/183)*(days_between(end_period_date,
start_date) + 1)), 2)
Oracle FastFormula begins calculating inside the brackets and from left to right, in the
following steps:

1. salary_amount/100
2. 1. * bonus_percentage
3. 2. / 183
4. days_between (end_period_date, start_date)
5. 4. + 1
6. 3. * 5.
7. TRUNC(6.,2)

Incorporating Conditions
In Wage element example, only one value is returned, and it is calculated in the same way for every assignment. However you may need to perform different calculations depending on the particular group of employee assignments, or the time of the year, or some other factors. You can do this by incorporating conditions in your formula.

Simple Conditions
For example:
IF age < training_allowance =” 30″ training_allowance =” 0″>

 

IF (DAYS_BETWEEN(end_period_date, start_date)+1) >= threshold_value WAS DEFAULTED
There is a special type of condition called WAS DEFAULTED. Use this to test whether a default value has been placed in an input value or database item. Default values are placed using the Default statement. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ‘Warning: hourly rate defaulted’
In this example, if the database item hourly_rate is empty (NULL), the formula uses the default value of 3.00 and issues a warning message.

Combined Conditions
You can combine conditions using the logical operators AND, OR, NOT.

Commenting Formula
You must include comments in your formulas to make them easier to read and understand what the formula does. For example, you can name the formula as:

/* Formula: Calculates Duration of Absence */
Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.

 

Alias Statements
Sometimes DB Item names are too long to conveniently use in a formula. Set up an alternative shorter name to use within the formula. For example: ALIAS as_qualifying_length_of_service AS as_los In the rest of the formula, you can use the alias (in this example, as_los) as if it were
the actual variable (as_qualifying_length_of_service).

Default Statements
It is used to set a default value for an input value or a database item. The formula uses the default value if the database item is empty or no input value is provided when you run the formula. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ’Warning: hourly rate defaulted’
This example sets a default of 3.00 for the database item hourly_rate. If hourly_rate is empty (NULL) in the database, the formula uses the default value of 3.00. The formula uses the ’WAS DEFAULTED’ test to detect when a default value is used, in which case it issues a warning message.

Important: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labeled Default Required. This check box is checked for database items that can be empty. The Database Items window appears when you choose the Show Items button on the Formulas window.

How to Compile FastFormula?
After creating or editing a formula in the Formula window, choose the Verify button to compile it.
If you need to compile many formulas at the same time, you can run the concurrent program “Bulk Compile Formulas process” in the Submit Requests window.

Formula Errors
There are two types of error that can occur when using Oracle FastFormula:
• Verify-time errors occur in the Formulas window when you run the formula verification. An error message explains the nature of the error. Common verify-time errors are syntax errors resulting from typing mistakes.

• Run-time errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. The basic Oracle FastFormula errors that can occur at run-time are:

• Uninitialized Variables: An uninitialized local variable is one that has no value when the formula runs. The term ’uninitialized’ means you have not assigned any value to the variable before you try to use it. This causes an error in all statements except the Return statement. For example:
IF (tax_band < tax =” salary”> 2000)
THEN tax = salary / 10
IF tax > 1000
THEN…
This formula fails with an ’Uninitialized variable’ message (for the variable tax) if the
tax band is set to 2000.

• Divide by Zero: Dividing a number by zero is an operation that provides no logical result. If this situation ever arises, Oracle FastFormula passes a code back to the application indicating an error (the application then takes the appropriate action).Always check for the possibility of a divide by zero error if there is any chance it could occur. For example, the formula:
x = salary/contribution_proportion
produces an error if the contribution proportion is set to zero. In this formula, check
for the divide by zero condition as follows:
IF contribution_proportion = 0
THEN

 

(

 

message = ‘The contribution proportion is not valid.’

 

RETURN message

 

)
ELSE x = salary/contribution_proportion

• No Data Found: A database item supposed to be in the database was not found. This
represents an error in the application data.

• Too Many Rows: The database item definition within the application caused more than one value to be fetched from the database.

• Value Exceeded Allowable Range: This can occur for a variety of reasons such as:

• exceeding the maximum allowable length of a string (which is 240 characters)
• rounding up a number to an excessive number of places, for example, round (1,100)
• using an invalid date, for example, 39-DEC-1990.

• Invalid Number: This occurs only when a database item contains an item that does
not make sense as a number.

• Null Data Found: A database item was found to have a null value when it should have had a non-null value. Use the Default statement for database items marked as Default Required in the Database Items window.

How to write simple Fast Formulas in Oracle HRMS

Assignment
Write a formula to calculate the travel payment Pay Value by multiplying Distance by the company travel rate, which is stored as a global value. How to define Global valuehas been already covered before.

Writing Simple Formulas
Assume that a company has defined an element called ‘Travel Payment’ with an input value called ‘Distance’. Write a formula to calculate the travel payment Pay Value by multiplying Distance by the company travel rate, which is stored as a global value.

Name the formula XX_TRAVEL_PAYMENT, where xx is your group identifier.

Steps to write a Simple Formulas
1. Navigate to the Formula window using your local Super HRMS Manager responsibility.
Total Compensation > Basic > Write Formulas
2. Set your effective date to 1 January 2000.
3. Enter the name XX_TRAVEL_PAYMENT, where xx is your group identifier.


4. Select Oracle Payroll in the Type field.
5. Select the Edit button.

6. Write your formula in the Edit Formula window, then select the Verify button.
7. When the formula verifies successfully, close the Edit Formula window.
8. Save your work.

Your formula should be similar to the following-

DEFAULT FOR distance IS 0
INPUTS ARE distance

travel_payment = distance * XX _TRAVEL_RATE

RETURN travel_payment

Note: To complete the setup for the travel payment, you would:
• Define a formula processing rule to associate the formula with the Travel Payment element.
• Define a formula result rule to return the travel_payment formula result to the element’s Pay Value.

 

Writing Conditional Logic in Fast Formulas

Writing Conditional Logic
Let us assume that the company has updated the Travel Payment element so that it now has a Pay Value and two input values: distance and override travel rate. Most employees receive the travel rate stored in the global value, but there is the discretion to override it with a special rate using the override travel rate input value.

1. Update your Travel Payment formula to use the override travel rate, if it has been entered, and otherwise to use the global value.

Hint: Use the WAS DEFAULTED condition.

Writing Conditional Logic
1. Navigate to the Formula window using your local Super HRMS Manager responsibility.
Total Compensation > Basic > Write Formulas
2. Set your effective date to 1 January 2000.
3. Query: XX_TRAVEL_PAYMENT, where xx is your group identifier.
4. Select the Edit button.
5. Add the conditional logic to your formula, and verify it.
6. When the formula verifies successfully, close the Edit Formula window.
7. Save your work.
8. Select Correction.

Your fast formula should be similar to the following:

DEFAULT FOR distance IS 0
DEFAULT FOR override_travel_rate IS 0
INPUTS ARE distance, override_travel_rate
IF override_travel_rate WAS DEFAULTED
THEN
travel_payment = distance * XX_COMPANY_TRAVEL_RATE
ELSE
travel_payment = distance * override_travel_rate
RETURN travel_payment

 

 

How to add Functions to Fast Formulas

 

 

Assignment
Edit your Travel Payment formula to ensure that the result is always rounded to two decimal places.
Using the Round Function Edit your Travel Payment formula to ensure that the result is always rounded to two decimal places. It can be done by using functions in Fast Formulas.

Using the Round Function
1. Navigate to the Formula window using your local Super HRMS Manager responsibility. Total Compensation > Basic > Write Formulas
2. Set your effective date to 1 January 2000.
3. Query XX_TRAVEL_PAYMENT, where xx is your group identifier.
4. Select the Edit button.
5. Add the Round function to your formula, and verify it.
6. When the formula verifies successfully, close the Edit Formula window.
7. Save your work.
8. Select Correction.

Your formula should be similar to the following:
DEFAULT FOR distance IS 0
DEFAULT FOR override_travel_rate IS 0
INPUTS ARE distance, override_travel_rate
IF override_travel_rate WAS DEFAULTED
THEN
travel_payment = ROUND((distance * XX_COMPANY_TRAVEL_RATE),2)
ELSE
travel_payment = ROUND((distance * override_travel_rate),2)
RETURN travel_payment

 

 

 

 

 

Fast Formula to Validate User Tables in Oracle HRMS

 

In this practice you will learn how to write a fast formula that validates the user table. For this you need to first write a validation formula and then select the formula in the Columns window and test it by entering values outside the valid range in the Table Values window.

Writing Formulas to Validate User Tables
Write a validation formula that returns an error and a message if a user enters a value less than 10 or greater than 50 in the Dues column of your Union Dues user table. Name the formula XX_CHECK_UNION_DUES, where XX is your group identifier.

Select the formula in the Columns window, and test it by entering values outside the valid range in the Table Values window.

Writing Formulas to Validate User Tables
1. Navigate to the Formula window using your local Super HRMS Manager responsibility.
(N) Total Compensation > Basic > Write Formulas
2. Set your effective date to 1 January 2000.
3. Enter XX_CHECK_UNION_DUES in the Name field.
4. Select User Table Validation in the Type field.
5. Select the Edit button.
6. Write your formula, and verify it.
7. When the formula verifies successfully, close the Edit Formula window.
8. Save your work.

Formula
Your formula should be similar to the following:

INPUTS ARE entry_value(text)
IF TO_NUM(entry_value) <> 50
THEN
(formula_status = ‘e’
formula_message = ‘Union Dues must be between 10 and 50. Please re-enter.’)
ELSE
formula_status = ‘s’
RETURN formula_status, formula_message

Selecting the Validation Formula in the Columns Window
9. Navigate to the Table Structure window using your local Super HRMS Manager responsibility.
(N) Other Definitions > Table Structure
10. Set your effective date to 1 January 2000.
11. Query: xx Union Dues in the Name field, where xx is your group identifier.
12. Select the Columns button.
13. Select your validation formula in the Formula field.
14. Save your work.

Testing Your Validation Formula
15. Navigate to the Table Values window using your local Super HRMS Manager responsibility.
(N) Other Definitions > Table Values
16. Set your effective date to 1 January 2000.
17. Query: xx Union Dues, where xx is your group identifier.
18. Select Query By Example – Run from the View menu.
19. In the Union A row of the Values region, enter 5 in the Value field. Tab to the next field. Your error message should be displayed.
20. Re-enter 30 and tab to the next row.
21. Select Correction.
22. Enter 60 in the Value field and tab to the next field. Again, your error message should be displayed.
23. Re-enter 32.
24. Select Correction.
25. Save your work.

 

 

Oracle Fast Formula Tutorial-Defining User Tables and Accessing Table Values

You should now be familiar with Oracle Fast Formulas. In the following tutorial you will learn how to define user tables and access table values in Fast Formulas.

Assignment
Create a user table called xx Union Dues (where xx is your group identifier), using the following information.Enter 30 and 32 as Value against column dues.

Row1-Union A 30
Row2-Union B 32

Then write a formula called XX_UNION_DUES to get the appropriate deduction from the table, using the input Union.

Tasks to be carried out
Defining User Tables and Accessing Table Values

Imagine a company has defined a deduction for union dues. The deduction element has one input value (Union) for entering the name of the union to which the employee subscribes: Union A or Union B.

1.Create a user table called xx Union Dues (where xx is your group identifier), using the following information.

Union A 30
Union B 32

2.Then write a formula called XX_UNION_DUES to get the appropriate deduction from the table, using the input Union.

Solution – Defining User Tables and Accesing Table Values
Defining the Table Structure

1. Navigate to the Table Structure window using your local Super HRMS Manager responsibility.
• (N) Other Definitions > Table Structure
2. Set your effective date to 1 January 2000.
3. Enter xx Union Dues in the Name field, where xx is your group identifier.
4. Select Match in the Match Type field.
5. Select Number in the Key Units of Measure field.
6. Enter Union in the Row Title field.
7. Save your work.
8. Select the Columns button.
9. Enter Dues in the Name field of the Columns window.
10. Save your work.
11. Close the Column window.
12. Select the Rows button.
13. Enter 1 in the Sequence field and Union A in the Exact field of the Rows window.
14. Select New from the File menu.
15. Enter 2 in the Sequence field and Union B in the Exact field of the next row.
16. Save your work.
Entering Table Values
17. Navigate to the Table Values window using your local Super HRMS Manager responsibility.
• (N) Other Definitions > Table Values
18. Set your effective date to 1 January 2000.
19. Query: xx Union Dues, where xx is your group identifier.
20. In the Values region, select Union A in the Exact field and enter 30 in the Value field.
21. Tab to the next row and select Union B in the Exact field and enter 32 in the Value field.
22. Save your work.
Writing the Formula
23. Navigate to the Formula window using your localSuper HRMS Manager responsibility.
• (N) Total Compensation > Basic > Write Formulas
24. Set your effective date to 1 January 2000.
25. Enter the name XX_UNION_DUES, where xx is your group identifier.
26. Select Oracle Payroll in the Type field.
27. Select the Edit button.
28. Write your formula in the Edit Formula window, then select the Verify button.
29. When the formula verifies successfully, close the Edit Formula window.
30. Save your work.
Formula
Your formula should be similar to the following:
DEFAULT FOR union IS ‘ ‘
INPUTS ARE union(text)
deduction = 0
IF NOT union WAS DEFAULTED
THEN
deduction = TO_NUMBER(GET_TABLE_VALUE(‘xx_union_dues’,
‘dues’, union))
RETURN deduction

Note: To complete the setup for the union dues deduction, you would:

• Define a formula processing rule to associate the formula with the Union Dues Deduction element
• Define a formula result rule to return the union_dues formula result to the element’s Pay Value

 

 

How to Define or Setup Global Value in HRMS

To set-up the different rates for employee reimbursement options you define global value. Here in this example I will define a global value called XX_TRAVEL_RATE, where xx is your group identifier. Enter a value for the amount of money paid per mile or kilometer for reimbursement of business travel expenses. The screenshot is itself self explanatory.

Follow the simple steps for defining a Global Value

1. Navigate to the Globals window using your local Super HRMS Manager responsibility. Total Compensation > Basic > Global Values
2. Set the effective date to 1 January 2000.
3. Enter the name XX _TRAVEL_RATE, where XX is your group identifier.
4. Enter a description, like ‘Travel reimbursement rate per mile’ just for an easier identification.
5. Select Number in the Type field.
6. Enter a value, such as 7.5.
7. Save your work.

 

How to Generate Employee Number through Programming in Oracle HRMS

There are following steps need to follow to generate employee number through programming:

Step #1:

Create the FUNCTION “Get_Next_Employee_Number”:

CREATE OR REPLACE Function APPS.Dafz_Get_Next_Employee_Number(p_Business_Group_Id In Number,

p_Emp_Type In Varchar2)

Return Varchar2 Is

v_Emp_No Varchar2(100) := ‘0000’;

Begin

If (p_Emp_Type = ‘EMP’) Then

Begin

Select ‘0’ || Max(To_Number(Papf.Employee_Number) + 1)

Into v_Emp_No

From Per_All_People_f Papf

Where Papf.Employee_Number Is Not Null

And Papf.Employee_Number Not Like ‘XX%’

And Papf.Employee_Number Like ‘0%’

And Papf.Business_Group_Id = p_Business_Group_Id;

Exception

When Others Then

v_Emp_No := ‘NULL';

End;

End If;

Return v_Emp_No;

End;

/

STEP # 2

Change the “HR:Business Group” & “HR: Security Profile” profile setting to “Setup Business Group

HR Business Group Profile Setting

STEP # 3

Define Function in Oracle HRMS Responsibility, switch to “UAE HRMS Responsibility” and open “DEFINE FUNCTION”

Create New Function and calling the above function in it:

Define Function
 

 Step # 4

Switch to Responsibility “UAE HRMS Manager”  then Total Compensation –> Basic –> Write Formulas, Search for “EMP_NUMBER_GENERATION

EMP NUM GEN

Edit this and update with below Fast Formula code:

/* ——————————————————————————————————-*/
/* NAME: EMP_NUMBER_GENERATION */
/* Returns the Next Employee Number.
/* Developed By: M Muzzammil Khan
/* Dated: 01-May-2013 */
/*——————————————————————————————————-*/
DEFAULT FOR Person_number IS ‘0000’
DEFAULT FOR Party_ID IS 0
DEFAULT FOR Person_ID IS 0
DEFAULT FOR National_ID IS ‘0000’
DEFAULT FOR Date_Of_Birth IS ‘1900/01/01 00:00:00′ (date)
DEFAULT FOR Hire_Date IS ‘1900/01/01 00:00:00′ (date)

INPUTS ARE
Legislation_Code (text),
Person_Type (text),
Person_number (text),
Party_id,
Person_id,
Date_of_birth (date),
Hire_date (date),
National_ID (text)

Next_number = ‘0000’
IF person_type = ‘EMP’ then
(
Next_Number = DAFZ_Get_Next_Employee_Number(81,Person_Type)
)
RETURN Next_number

 

 STEP # 5

Save and compile the Fast formula and then try to create new staff from HRMS responsibility, you will see it will generate the new staff # through programming.

Feel free to contact me if further details required.

Thanks, Muzzammil Khan

 

 

 

IMPORTANT BASE TABLES FOR ORACLE HRMS R12

 Important Base tables for ORACLE HRMS r12

 Some of the very important base tables in HRMS are as follows:
1. per_all_people_f
This table will provide us the very basic information about the employee. The very data from the first screen we see when we open the ‘People -> Enter and Maintain’ form goes into this table. 

2. per_all_assignments_f
This table will store all the information which is been entered in the employee assignment form. 

3. per_addresses
This table will store all the information which is been entered in the employee address form. 

4. per_pay_proposals
This table will store all the information which is been entered in the employee salary form. 

5. per_person_types_tl
This table is used to find the type of the employee. This table is linked with the per_all_people_f with the person_type_id to find out the type of person.

6. per_jobs_tl
This table will contain the various types of JOBS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct job name from the employee.

7. per_grades_tl
This table will contain the various types of GRADES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct grade name from the employee.

8. hr_locations_all
This table will contain the various LOCATIONS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct location name from the employee.

9. pay_all_payrolls_f
This table will contain the various types of PAYROLLS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct payroll name from the employee.

10. per_pay_bases
This table will contain the various types of PAY BASES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct pay basis name from the employee.

11. per_assignment_status_types_tl
This table will contain the various types of assignment types in oracle. The assignment types generally would be ‘Active Assignment’ etc.. This table is been linked with the per_all_assignments_f table to retrieve the correct job name from the employee.

12. per_person_type_usages_f
This table will store the correct person type of the particluar employee. We should never depend on the person type present in the per_all_people_f table. Instead we need to link the person_id with this table and get the correct person type.

Regards, Muzzamil Khan

Sr. Oracle HCM Consultant

Oracle Payroll Costing Concept

To understand Payroll Costing concept, I would first discuss the Costing Allocation Key Flex field, during the business group definition it is being created and map with Cost Allocation KFF with following segments normally

• Company
• Cost Centre
• Natural Account

During the configuration of Cost Allocation KFF the most important concept is to set the Flex Field Qualifiers at each segment level, we have the five levels available for each segment i.e.

• Payroll
• Balancing
• Organization
• Element Link
• Assignment
• Element Entry

For the Company segment following setting can be applied

Payroll Checked
Balancing Checked

This setting will explain that the Company Account will be input at Payroll level (debit information) and balancing information will goes into credit information and on every segment level it is mandatory to define Balancing.

For the Cost Centre segment, I can set the following setting

Payroll Checked
Organization checked
Balancing checked

This setting will explain that the Cost Centre Account information will be input at Payroll and Organization level (debit information) and balancing information will goes into credit information and on every segment level it is mandatory to define Balancing.

For the Natural Account segment, I can choose the following setting

Element Link Checked
Assignment Checked
Element Entry Checked
Balancing Checked


This setting will explain that the Natural Account information will be input at following levels

Element Link
Assignment
Element Entry
Balancing

Element Link, Assignment, Element Entry level information will directly hit the debit account and balancing information will goes into credit Account and on every segment level it is mandatory to define Balancing.

After this step we map Cost Allocation KFF Segments with GL Flex field Segments

In Oracle payroll, Costing information can be input at five levels

• Payroll
• Organization
• Element Link
• Assignment
• Element Entry

Now based on the above configuration, the following codes will be define at Payroll level

Company 01
Cost Centre 000

For example if Business Group has four Departments i.e. Finance, HR, IT, Operations then on every organization the following information will be define at the Organization level

Finance Cost Centre 001
HR Cost Centre 002
IT Cost Centre 003
Operations Cost Centre 004

Note: Here the Cost Centre information will override with information available on Payroll Level

Payroll Level 000
Finance Org Level 001

So the system will pick the value of 001 instead of 000.

Finally following information need to define at every Element Link level, for example if Business Group has only three elements links then Natural Account need to define at every link

Basic Salary 2345
Housing Allowance 2346
Transport Allowance 2347

For Assignment and Element Entry levels, the Natural Accounts codes will not be defined at these levels , but if need to override the link level information then the Natural Account can be enter at assignment or Entry Level. Following the is Override information, if we defined all segment on every level then

The following Entry will be hit to GL

Debit 04.003.2222
Credit 01.000.6766

Because Element Entry Level will override all the above mention levels but this is only for example, if we see this table in our scenario then the value will be shown like this

In this case the following Entry will hit to GL accounts

Debit 01.001.2222
Credit 01.000.6766


Now based on the above configuration the Cost Breakdown report will be shown like this

Cost Summary Breakdown Report– Cost Centre wide

Cost Centre Code Cost Centre Amount
001 Finance xxxx
002 HR xxxx
003 IT xxxx
004 Operation xxxx
Total xxxx.xx

Cost Breakdown Report– Element wide.

Regards,

Muzzamil Khan ( Team Lead HRMS)

Oracle HRMS Payroll Interview Questions

Dear All,

For more Questions / information about Oracle HCM please visit my following blogger:

More About Oracle HRMS

Q1) What is the difference between a Job and a Position?

Job is general one, whereas Position is specific term to its role and responsibilities.

JOB:            MANAGER (generic term), SOFTWARE DEVELOPER

 POSITION:  Finance Manager, HR Manager, Junior SOFTWARE ENGINEER, (this is position which is                               specific to the role to be played.

Q2) What are the difference between extra information type (EIT) and special information type (SIT)?

EIT and SIT Both are provided by Oracle to Capture Extra Information. Basic differences would be EIT is similar like a DFF and also defined using DFF Definition Screen. SIT is KFF and defined using Personal Analysis KFF Definition screen.

SIT generally used at Person Level, EIT can be defined at PERSON, ASSIGNMENT, CONTACT, ELEMENT, LOCATION and JOB LEVEL.

There are 2 columns date_to and date_from in SIT while no such columns are there in EIT.

Q3) Tell me the name of important Key Flex-Fields (KFFs) in Oracle HRMS?

  • Job KFF,
  • Grade KFF,
  • People Group KFF,
  • Position KFF,
  • Cost Allocation KFF,
  • Comptence KFF

Q4) List of some Important Tables in HRMS

Per_all_people_F,

per_person_types,

per_person_type_usages,

per_addresses,

Per_all_assignments_f,

per_jobs,

per_job_Definitions,

per_grades,

per_grade_definitions,

hr_all_positions,

hr_all_position_definitions,

hr_all_locations,

pay_all_payrolls_F,

pay_element_entries_F,

pay_elements_links_F,

Q5) What is the difference between both SECURED VIEWS and NON SECURED VIEWS?

1.Secured views display information only for the current period

2.Unsecured views is used to get the information from the entire rows

Q6) Define APIs and use in Oracle HRMS?

API’s are used in HR to insert the data into the Base tables. As it’s very secured system, the user does not have the facility to copy the data directly into the Base tables. When we write the inbound interfaces / use WebAdI, the systems will use the APIs to store the data into system. The API are published by oracle with number of parameters. The different types of parameters are IN / INOUT / OUT of these parameters few are mandatory, without which the process won’t complete. Generally when we use API we give data FOR : Object Version Number, Effective Date, P_Validate

HR_EMOYEE_api example: hr_employee_api.create_employee

hr_organization_api Example: hr_organization_api.create_organization

hr_assignment_api

Q7) What are processing types of element?

Elements are nothing but the components of the salary.

For Example: Basic Salary, House Allowance, Transport Earning, Bonus, Loan Recovery etc.

There are 2 types of Processing

1. Recurring: if an entry of this element applies in every period until the entry is ended

2. Non Recurring: if an entry applies in one pay period only.

Q8) What are termination roles of an element?

Termination Rules of an Element:
a).Actual Termination:  For a nonrecurring element, select Actual Termination if you want the entries to close down at the end of the pay period in which the employee leaves.
b).Final Close: if you want the entries to stay open beyond the employee's leaving date so that you can continue to pay the employee.
c).The Last Standard Process date defaults to the last day of the pay period in which the employee is terminated, but you can set it to a later period when you terminate an employee.

Q9) What is costing?

Costing:
Recording the costs of an assignment for accounting or reporting purposes, Using Oracle Payroll, you can calculate and transfer costing information to your general ledger and into systems for project management or labor distribution.

Q10) What are 2 modes of date track in Oracle APPS?

There are two Date Track modes are as follow:
*Update
*Correction

Q11) Can a job has multiple positions? 

Job is generic. Yes a job can have multiple positions.

Q12) Can you call PL/SQL Package functions from within a Fast Formula?

Yes you can

–>how do we do this?

There is a Define Function screen in HR. In this screen you will register the PL/SQL as “External Function”.

Q13) Can we want to pass a parameter PAYROLL_ID to this external PL/SQL Function, how do we do it?

The “Define Function” screen has a button named “Context Usage”. This button opens up a window in which you can register the parameters.

Q14)  How do you debug a Fast Formula?

You can create a message variable in Fast Formula.

This message variable must also be registered as a Formula Result ( In “Formula Result Rule” window).

Q15)  What are the various levels where you can Set up Payroll Costing Accounts?Which levels take the highest precedence?

Element Entry => Highest

Assignment => Second Highest

Organization => Third Highest

Element Link => Fourth Highest

Payroll => Fifth Highest

Q16) Technically speaking, how do you know from tables that an Employee has been terminated?

(a) The Person_Type_ID in PER_PERSON_TYPE_USAGES_F will correspond to System Person Type EX_EMP

(b) A record gets created in table PER_PERIODS_OF_SERVICE with Actual_Termination_date being not null

Q17) How can you make Employee Number generation Automated based on business rule etc?

Use Fast Formula.

Q18) What is the difference between per_people_f and per_all_people_f?

PER_PEOPLE_F is a secured view on top of PER_ALL_PEOPLE_F. The secure view uses an API hr_security.show_person. This API internally checks for an entry in table PER_PERSON_LIST for the logged in person’s security profile. A concurrent program named “Security List Maintainence program” will insert records in PER_PERSON_LIST.

Q19) If you do personalization at both FUNCTION level and Responsibility level, which personalization has higher precedence?

Responsibility level. Responsibility level will override FUNCTION level personalization’s.

Q20) Say you have done a lot of Personalization’s to Self Service Screens. But all these Personalization’s were done in DEVELOPMENT environment. How will you migrate these personalization’s to PRODUCTION environment?

Before 11.5.7…Use AKLOAD

This is a java program given by Oracle. This is the only way you can script it.

In DEV you will do AKLOAD DOWNLOAD ( & other parameters)

In PRD you will do AKLOAD UPLOAD ( & other parameters)

On or after 11.5.10 Use responsibility “Functional Administrator”, then click

on Personalization Tab, and then click on Import/Export.

Next select the documents to be exported.

Go to the UNIX box and tar the personalization’s into a .tar file.

On the Production environment unzip this tar file in location as entered in profile “FND: Personalization Document Root Path”.

Setting up of this profile is mandatory or else Oracle Framework wouldn’t know where to search for the files to be imported.

Q21) How to restrict iRecruitment external visitors to access only responsibilities on the external server?

Set the Server Trust Level for the server to External, Set the Trust Level of the iRecruitment external responsibilities to External too.

Q22) Is SSHR a dependency for iRecruitment?

iRecruitment has no dependence on SSHR

Q23) Does iRecruitment support “Candidate Qualifying Questionnaires” where the candidate must answer vacancy-specific questions correctly prior to submitting an application?

iRecruitment currently does not support this function, For now this can be achieved by having a set of simple questions using flexfield to get those responses and a user hook to validate them as part of the apply process.

Q24) Do we track vacancy history?

iRecruitment does not track changes to the vacancy. If changes to a field are to be traced use the Audit capability in APPs.

Evaluate your HRMS / Payroll skill, Go for free test http://oraclehrmsoverview.com/hrms-online-test/