Practice Notes: Payroll Fast Formula

Fast formulas exist not only for payroll calculation but also for many other purposes. One of them being input value validation i.e. using these type of formulas we will be able to validate the screen entry values of element's input values.

We can add these validations against particular elements in

Form Functions(ctrl + L) -> element entries

Here search for the person and select the element's entry value button and in the popup form, horizontal scroll to formula text box and from the LOV select the formula which validates the screen entries against this input value.

Such custom formula can be created in

Form Functions(ctrl + L) -> write formula

Create the formula with formula type as Element Input Validation. A very useful article on element input validation formulas can be found in oracle manual here.

Two basic rules for writing an input value validation formula are

1. There should be only one INPUT for the formula named ENTRY_VALUE of text data type i.e. INPUTS are entry_value(text)
2. entry_value input can be converted to number types using to_num conversion functions for any validation
3. There should be a mandatory return parameter called formula_result that can take either ‘S’ (success) or ‘E’ (error)

Let us consider an example of validating an entry value which must be atleast 10 i.e. consider an HRA element where we will be using this validation to enforce a rule that the employee gets a minimum of 10% HRA. A simple formula validation may be as follows

INPUTS are Entry_Value(text)

FORMULA_RESULT = ‘S’

IF TO_NUM(Entry_Value) < 10 THEN

(

FORMULA_RESULT = ‘E’

FORMULA_MESSAGE = ‘Entry Value must be minimum 10 and above’

)

RETURN FORMULA_RESULT, FORMULA_MESSAGE




After attaching this above formula against hra_pct of test_hra and when used for an employee, the above validation is fired whenever we enter some screen entry value for the hra_pct. Notice that when hra_pct value entered is less than 10, application throws error from the formula_message but eventually saves the transaction.

The formula validation we try to do will merely give warning to the user and no way prevent one from overriding the formula validation.

Practice Notes: Payroll QuickPay

Try running quick pay for an employee with an effective date in the past. The quick-pay results in error even though there is no payroll was run for this employee. Let situation be evaluated with a simple example.

On 27th Apr 2010, we create following objects as of 01-Jan-2000:

• Create two recurring elements called test_salary and test_hra. The input values for test_salary are Pay Value and Basic Salary. Similarly for test_hra, Pay Value and hra_pct are the input values
• Create a salary basis test_salary_basis including test_salary as its element
• Write a formula test_formula which reads basic salary of test_salary and hra_pct of test_hra and computes the total salary which is equal to (monthly_salary + monthly_salary*hra_pct/100)
• Attach this formula to test_salary and return the total_salary to Pay Value of test_salary using formula results form
• Link both the element to a payroll test_payroll
• Create a new employee 'Mahindrasingh Dhoni', create address and attach the test_salary_basis to his assignment. Add test_payroll to his payroll
• Create a new salary for this employee effective from 01-Jan-2000. say $10000 basic per month
• From Element entries form, add test_hra element to this employee
• Run the quick pay for this employee as of 31-jan-2000

Above process typically results in error. The above process works perfectly when the same steps are repeated for effective date of today and for future periods whereas it does not work backwards even though we have not run any payroll for this employee later than 31-Jan-2000

Inference:

It is best to do all setup works in the past and start running the payroll from current date.

Short Writeup: Fast Formula - Overview

Oracle Fast Formula can be written using formula form found in

Navigation -> Total Compensation -> Basic -> Fast Formula -> Write Formula

Once written, usually Fast formulas are attached to elements of Payroll for any computation. But FF can be of any type including payroll, accruals etc.

Different Components of Fast Formulas:

1. Input Statement
2. Assignment Statements
3. Return Statements

Data Types:
There are three types of data available in fast formula. They are text, numeric and date types. By default a variable is of numeric type. If the data type is not numeric then it must be specified like

INPUTS are x,y (date),z (text)

Fast Formula Variables:
Fast formula variables are of three types. Local, Global and database variables.

Variables usually get their values when values are input to them during a formula call or through database objects.

Formula Structure:
A simple formula may look like this with input, assignment and return statements.

INPUTS are x,y

x = y + 1

RETURN x

Note:

• RETURN statement is used to return the specific value to outside usually a payroll run
• There can be many RETURN statement in a formula but only one INPUTS statement is allowed in a formula
• Values of input variables can not be changed
• When the element input value is passed to the formula, the input name must be same as that of the input values of the element and the multiple words must be joined by an underscore.

Contexts:
Contexts are similar to environment variables. Since fast formulas are attached to elements, the contexts change for each assignment. Typical context info include

• Business group
• Element and element links
• Employee and his assignments

Order of processing:
Calculation begins from inner most braces starting left to right.

Conditional Statements:

We can use If-else statement like we use it in any other languages.


IF THEN
(

statements

)
ELSE
(

statements

)

Here we can use the normal conditional operators and logical operators(AND,OR,NOT). There is one more clause called 'WAS DEFAULTED' which when used in if condition, results in true condition if the particular value passed is null.

For eg.

DEFAULT for x is 10

IF x WAS DEFAULTED THEN

(

statements

)

ELSE

(

statements

)

When no value/null value is passed to x then the condition evaluates to TRUE oterwise FALSE.

Comments:
The commenting is similar to that of pl/sql and C++
/* Comments */

Aliases:
The database items are often longer and referencing them in formulas becomes tedious without aliases.
ALIAS database_name AS alias_name

Fast Formula Functions:
FF Functions can be of following types.

• Text Functions
• Numeric Functions
• Date Functions
• Data conversion Functions
• NULL Functions

Database items:
Database items are the procedures that exist in database. These items can be referenced in fast formula to arrive at the result values. There are two types of database items.

1. Static DB Items: are predefined and include standard type of information. For example, date of birth of an employee.
2. Dynamic DB Items: are generated by the definitions of elements, balances, absence types, flex field segments etc. For example, for each of the input value entry of an element one DB Item is created with preceding element name.

Short Writeup: Oracle Payroll Balances

Balances in oracle payroll is nothing but addition/subtraction of 'run result values'. Considering a lay man example of showing year to date salary payment made for particular employee, it may be computed through summing up run result values generated after each payroll through specified time period or one could use Balances.

We could define our own balance or use one of the seeded balance to take particular element feeds.

Navigation -> Total Compensation -> Basic -> Banlances

Again, balances can be fed by individual elements or through element classification(at the larger level). We should note that both are mutually exclusive. Basic balance information is stored in PAY_BALANCE_TYPES table in R12 .

Dimension:
Dimensions talk about period context of balance i.e. in our previous example, 'one financial year' forms the dimension. similarly dimension can be quarter to date, month to date types. Dimension details are stored in PAY_BALANCE_DIMENSIONS.

Balance Feeds:
As we saw previously, balance feed is nothing but how element types are used to feed balances. The input values of element type is linked to balance type via PAY_BALANCE_FEEDS_F table.

Defined Balances:
Balances by itself does not hold the accrued balance values. Because the context in which balance is going to be used may change depending on the dimension with which we query it. Hence PAY_DEFINED_BALANCES links the valid combination of balance types and dimension types. This table has defined_balance_id as the primary key which will be extensively referred in latest balances.

Balance Dimension Types:
Usually balances are associated at the assignment level. But there are five different types of dimensions based on which the balances may be used differs.

1. N type - Not Fed, Not stored
2. F type - Fed, but not stored
3. A type - stored at assignment level
4. P type - stored at person level
5. R type - Run level balances

The balances for N and F types are not stored in the database tables whereas it is stored for other types.

Latest Balance:
In R12, the latest balances, be it in assignment level or at person level are stored commonly in a table called PAY_LATEST_BALANCES. The latest balances at assignment level are stored in PAY_ASSIGNMENT_LATEST_BALANCES and linked to PAY_LATEST_BALANCES through latest_balance_id. This table also holds assignment_id and assignment_action_id also. Similarly PAY_PERSON_LATEST_BALANCES is also linked to PAY_LATEST_BALANCES.

Retrieving Code Combination ID for any key flex fields

It may required to validate the segments of key flex fields and retrieve the code combination id so that such ids may be used in APIs. Typical example includes validating soft coding key flex fields(in update_emp_asg), job,position, cost allocation key flex etc in element links API.

During conversion/interfacing type of projects we may be required to validate the kff descriptions and get back the code combination id. In such cases the following procedure may be useful. It internally uses hr_kflex_utility.ins_or_sel_keyflex_comb API

---
--- Name : validate_keyflex
--- Description : takes the kff desciptions concatenated by ~
--- and returns the code combination id. Other
--- mandatory parameters to be supplied are
--- flex code and flex num
--- Note : p_error_msg_io is just varchar2 type to take back
--- generated error messages.
---

PROCEDURE validate_keyflex(
p_concat_des_i IN VARCHAR2
,p_flex_code_i IN fnd_id_flex_segments.id_flex_code%TYPE
,p_flex_num_i IN fnd_id_flex_segments.id_flex_num%TYPE
,p_error_msg_io IN OUT VARCHAR2
,p_cost_allocation_keyflex_id_o OUT pay_element_links_f.cost_allocation_keyflex_id%TYPE
,p_concat_segments_o OUT VARCHAR2
)
IS
l_id_concat_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
l_application_shortname fnd_application.application_short_name%TYPE;

TYPE flex_des_table_type IS TABLE OF fnd_flex_values_vl.description%TYPE
INDEX BY PLS_INTEGER;

TYPE flex_value_table_type IS TABLE OF fnd_flex_values.flex_value%TYPE
INDEX BY PLS_INTEGER;

TYPE flex_value_set_table_type IS TABLE OF fnd_flex_values_vl.flex_value_set_id%TYPE
INDEX BY PLS_INTEGER;

CURSOR value_set_cur IS
SELECT flex_value_set_id
FROM fnd_id_flex_segments
WHERE id_flex_code=p_flex_code_i
AND id_flex_num = p_flex_num_i
ORDER BY segment_num;

flex_des_table flex_des_table_type;
flex_value_table flex_value_table_type;
flex_value_set_table flex_value_set_table_type;


l_index_des PLS_INTEGER;
l_index_val PLS_INTEGER;

l_count NUMBER := 1;
l_loc NUMBER := 1;
l_position NUMBER;
l_des VARCHAR2(40);

BEGIN
----------- Get the segment delimiter for the structure ----------------
SELECT concatenated_segment_delimiter
INTO l_segment_delimiter
FROM fnd_id_flex_structures
WHERE id_flex_code = p_flex_code_i
AND id_flex_num = p_flex_num_i;
--DBMS_OUTPUT.PUT_LINE('Find Delimiter: Passed');
------------ Get all value set id for the flex structure used ----------
OPEN value_set_cur;
FETCH value_set_cur BULK COLLECT INTO flex_value_set_table;
CLOSE value_set_cur;

--DBMS_OUTPUT.PUT_LINE('Get value sets: Passed');
------------ Populate Description table ----------------------------------
l_index_des := 1;
LOOP
l_position := instr(p_concat_des_i, '~', 1, l_loc);
EXIT WHEN l_position = 0;
l_des := substr(p_concat_des_i, l_count, l_position-l_count);
flex_des_table(l_index_des) := l_des;
l_count := l_position+1;
l_loc := l_loc +1;
l_index_des := l_index_des + 1;
END LOOP;
l_des := substr(p_concat_des_i, l_count, length(p_concat_des_i)-l_count+1);
flex_des_table(l_index_des) := l_des;
DBMS_OUTPUT.PUT_LINE('Get flex des table: Passed');

---------- Populate value table for corresponding description ---------
---------- and create value concat segments ----------------------------
l_index_des := NULL;
l_index_val := flex_des_table.FIRST;
l_index_des := flex_value_set_table.FIRST;

WHILE l_index_val IS NOT NULL AND l_index_des IS NOT NULL
LOOP
SELECT flex_value
INTO flex_value_table(l_index_val)
FROM fnd_flex_values_vl
WHERE flex_value_set_id = flex_value_set_table(l_index_des)
AND description = flex_des_table(l_index_val);

---Create concat segments with delimiter at the begining
l_id_concat_segments := l_id_concat_segments || l_segment_delimiter||flex_value_table(l_index_val);
l_index_val := flex_des_table.NEXT(l_index_val);
l_index_des := flex_value_set_table.NEXT(l_index_des);
END LOOP;

-- Remove the preceeding delimiter
l_id_concat_segments := substr(l_id_concat_segments, 2, length(l_id_concat_segments));

--DBMS_OUTPUT.PUT_LINE('Get concatenated flex ids: Passed');
----------- Get the Application Short Name ---------------------------
SELECT application_short_name
INTO l_application_shortname
FROM fnd_application
WHERE application_id = (
SELECT application_id
FROM fnd_id_flex_structures
WHERE id_flex_code = p_flex_code_i
AND id_flex_num = p_flex_num_i
);

--DBMS_OUTPUT.PUT_LINE('Get Application Short Name: Passed');
--DBMS_OUTPUT.PUT_LINE('Des Segments: '|| p_concat_des_i );
--DBMS_OUTPUT.PUT_LINE('ID Segments: '|| l_id_concat_segments);

----------- Call Kflex utility to get the code combination id ---------
apps.hr_kflex_utility.ins_or_sel_keyflex_comb(
p_appl_short_name => l_application_shortname
,p_flex_code => p_flex_code_i
,p_flex_num => p_flex_num_i
,p_concat_segments_in => l_id_concat_segments
,p_ccid => p_cost_allocation_keyflex_id_o
,p_concat_segments_out => p_concat_segments_o
);

DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Passed');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
p_error_msg_io := p_error_msg_io ||'KeyFlex:Ambiguous Values~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
WHEN NO_DATA_FOUND THEN
p_error_msg_io := p_error_msg_io ||'KeyFlex:Invalid parameter passed~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
WHEN others THEN
p_error_msg_io := p_error_msg_io ||' '|| SQLERRM|| '~';
DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed');
p_cost_allocation_keyflex_id_o := NULL;
p_concat_segments_o := NULL;
END validate_keyflex;

The above code simply assumes that the validation sets used by the kffs are of either independent/dependent types. And the code is open for scrutiny.