Browser version script Skip Headers

Oracle® Fusion Applications Compensation Management Implementation Guide
11g Release 1 (11.1.4)
Part Number E20376-04
Go to contents  page
Contents
Go to Previous  page
Previous
Go to previous page
Next

18 Define Elements, Balances, and Formulas

This chapter contains the following:

Manage Element Classifications

Manage Fast Formulas

Manage Elements

Manage Element Classifications

Element Classification Components: How They Work Together

Elements are grouped into primary classifications, such as Earnings and Voluntary Deductions. In a human resources department, you can use the primary classifications to identify groups of elements for information and analysis purposes. In a payroll department, the classifications control processing, including the sequence in which elements are processed and the balances they feed.

Primary Classifications

Oracle Fusion provides you with these primary classifications and some balances, mainly to reflect tax legislation. They are designed to meet the legislative requirements of your country, so you cannot change them. You can create additional balances to be fed by any of the primary classifications.

Secondary Classifications

You can define secondary classifications to feed your own user defined balances. These secondary classifications are subsets of the primary classifications. In some legislations, secondary classifications have been predefined. As with primary classifications, you cannot remove or change any predefined secondary classifications, and you cannot disable any of the predefined balance feeds created for them.

Costing

If the classification is Costable, you can select any costing option for elements when you define the element links. If the classification is Distributable, you can create a distribution set from elements of this classification over which you can distribute costs (such as overheads). You can also view the cost type for elements in the classification, that is, whether they debit or credit the accounts they feed.

Frequency Rules

The payroll run uses a frequency rule to determine in which pay periods it processes a recurring element. You can view which date the payroll run uses, by default, to assess frequency rules in your localization. You can select a different date when you define a frequency rule.

Element Processing Sequence: How it is Determined

Payroll runs process elements in a predefined sequence, which you can determine.

How Processing Order Is Determined

An element's primary classification defines a default processing priority for the element in payroll runs. Lower priority numbers process first

Overriding Default Processing Priority

Most classifications also have a priority range. When you define an element in these classifications, you can overwrite its default processing priority with another number from the range. This is useful if you need to establish the order in which the element processes with respect to other elements in the classification

Sometimes you must prioritize the processing of certain element entries for an individual person. For example, you may need to determine the precise order in which deductions taken for wage attachments process for a person. You can enter a subpriority number for element entries.

Manage Fast Formulas

Using Formulas: Explained

Fast formulas are generic expressions of calculations or comparisons that you want to repeat with different input variables.

You can use fast formulas to:

Calculate Payrolls

Write payroll calculations and skip rules for elements that you define to represent earnings and deductions. Associate more than one formula with each element to perform different processing for employee assignments with different statuses. You will define elements and formulas for earnings and deductions with highly complex calculations requiring a number of different calls to the database.

Define the Rules for Paid Time Off Accrual Plans

Edit the delivered accrual type formulas or write your own. Each accrual plan needs two formulas: one to calculate the gross accrual and the other to return information to the PTO carry over process.

Define Custom Calculations for Benefits Administration

Configure your plan design to the requirements of your enterprise. For example, you can write a formula to calculate benefits eligibility for those cases where eligibility determination is most complex.

Validate Element Inputs or User-Defined Tables

Validate user entries into element input values using lookups or maximum and minimum values. However, for more complex validations write a formula to check the entry. Also, use a formula to validate entries in user tables.

Edit the Rules for Object Group Population for Elements or People

Define a payroll relationship group, HR relationship group, or element group by defining a formula based on the criteria entered. If you want to change the sequence in which the criteria are checked for each assignment, edit the formula.

Calculate Absence Duration

Calculate the duration of an absence from the start and end dates.

Using Formula Components: Explained

When developing a formula you must understand formula language, the rules that the application imposes on the formula, and the calculation requirements. Formulas are created using various components.

Formula components include:

Note

There are several other components used in formulas. These include literals, database items, working storage area, calls to other formulas, functions, and operators.

To illustrate how each component is used in a formula, suppose you wanted to calculate the pay value for the element WAGE by multiplying the number of hours an employee works each week by the hourly rate. The formula can be written as follows:

WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

 

Assignment Statements

The first line is an assignment statement that simply assigns a value to the element WAGE.

Return Statements

The second line is a return statement that passes back the WAGE value to the payroll run. A return statement can be used to stop the formula execution without passing back any values.

Variables

Local variables occur in a single formula only. You can change a local variable within the formula by assigning a value to it using an assignment statement. To calculate the WAGE value, the formula needs to get the value for the variable HOURS_WORKED.

You can use local variables to store data in a formula. You might want to hold data temporarily while you perform some other calculations, or to pass data back to the application. Below is an example showing the use of a variable, ANNUAL_LEAVE.

/* Formula: Annual Leave Formula */
IF YEARS_SERVICE >= 10
THEN
ANNUAL_LEAVE = 25
ELSE
ANNUAL_LEAVE = 20 + FLOOR (YEARS_SERVICE/2)
RETURN ANNUAL_LEAVE

 

Input Statements

The HOURS_WORKED could be an input value of the element WAGE. To pass the element input values to the formula during processing, define an inputs statement as follows:

INPUTS ARE HOURS_WORKED
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

 

Note

This is a payroll application example. The name used in the input statement must be the same as the name of the element input value, and multiple words must be joined by underscores. Other input statements that have nothing to do with elements would have their own rules for formula input variables. In this example, the input variable HOURS_WORKED is numeric. If the input variable is not numeric, you must specify the type. For example,

INPUTS ARE START_DATE (DATE)

 

Expressions

Each function or calculation is one expression, and you can nest expressions to create more complex calculations. Brackets can be used to control the order in which calculations are performed. Expressions within brackets are evaluated first. Within nested brackets, evaluation proceeds from the least inclusive set to the most inclusive set. When brackets are not used the following hierarchal order or execution is implied: multiplication and division then addition and subtraction.

Expressions combine constants and variables with operators (+, -, *, /), array methods, and functions to return a value of a certain data type. For example, the expression (3 + 2) returns a value of 5, and is of NUMBER data type. The format of an expression is:

SUBEXPRESSION [operator SUBEXPRESSION ...]

 

A number of sub-expressions can combine in a single expression. For example, the sub-expressions (3 + 2) and MONTHS_BETWEEN(start_date, end_date) can combine in a single expression as follows:

(3 + 2) + MONTHS_BETWEEN(start_date, end_date)

 

Expressions can also be used inside functions, such as:

salary = GREATEST(minimum_wage, (hourly_rate * hours_worked))

 

Operands in an expression are usually of the same data type which is the data type of the expression as a whole. For example, in the following expression all the operands are numeric and the expression itself is numeric:

GREATEST(MINIMUM_WAGE, (HOURLY_RATE * HOURS_WORKED)) + BONUS

 

The operands for the above expression are BONUS, and the return value from GREATEST. The arguments for GREATEST are separate expressions.

Conditions

Conditions are used to process expressions based on whether a certain condition occurs. For example,

TRAINING_ALLOWANCE = 0 
IF (AGE < 20) THEN
TRAINING_ALLOWANCE = 30

 

This formula checks if the condition (AGE < 20) is true or false. If it is true, the formula processes the statement that follows the word THEN. If the condition is not true, the formula ignores this statement.

Comments

Use comments to explain how all or part of a formula is used. Also, you can change some formula lines into comments until they are ready to be used. Comments are designated by the comment delimiters of /* and */. Anything written inside these delimiters is a comment. You can place comments anywhere within a formula. The beginning of a formula should contain the following comments:

An example of a comment is:

/* Use this formula to determine the bonus percentage for staff */
INPUTS ARE SALARY_AMOUNT,
START_DATE (DATE),
END_PERIOD_DATE (DATE),
BONUS_PERCENTAGE /* Decided at board level. */

 

Note

Do not put a comment within a comment. This causes a syntax error when the formula is compiled.

Formula Performance Improvements: Explained

When writing formulas there are a number of techniques to follow to endure your formulas are easy to read, use, understand, and process efficiently.

Variable Names and Aliases

To improve readability, use names that are brief yet meaningful. Use aliases if the names of database items are long. Name length has no effect on performance or memory usage.

Inputs Statements

Use INPUTS statements rather than database items whenever possible. It speeds up the running of your payroll by eliminating the need to access the database for the input variables.

An example of inefficient formula without INPUTS statement is:

SALARY = SALARY_ANNUAL_SALARY / 12
RETURN SALARY

 

An example of efficient use of INPUTS statements is:

INPUTS ARE ANNUAL_SALARY
SALARY = ANNUAL_SALARY / 12
RETURN SALARY

 

Database Items

Do not refer to database items until you need them. People sometimes list at the top of a formula all the database items the formula might need, thinking this helps the formula process more quickly. However, this in fact slows processing by causing unnecessary database calls.

An example of an inefficient use of database items is:

S = SALARY
A = AGE
IF S < 20000 THEN
IF A < 20 THEN
TRAINING_ALLOWANCE = 30
ELSE
TRAINING_ALLOWANCE = 0

 

An example of an efficient use of database items is:

IF SALARY < 20000 THEN
IF AGE < 20 THEN
TRAINING_ALLOWANCE = 30
ELSE
TRAINING_ALLOWANCE = 0

 

The first example always causes a database fetch for AGE whereas the second only fetches AGE if salary is less than 20000.

Balance Dimensions

Wherever possible, use balance dimensions for single assignments only in formulas. Multiple assignments require more calculation time, leading to slower processing time. The number of multiple assignments in a payroll is not normally high, and the presence of a small number does not lead to any significant increase in overall processing time. However, there could be a problem if you unnecessarily link balance dimensions for multiple assignments into general formulas.

Formula Statements: Explained

Statements are instructions that a formula carries out.

When working with statements, it is important to have knowledge of:

When using statements in a formula you must enter them in the following order: alias statements, default statements, input statements, then other statements.

Statement Types

Use the alias statement to define another name, or alias, for existing variables. Declare aliases for database items and global values. An example of an alias statement is:


Statement

Example

Description

ALIAS

ALIAS name1 AS name2

ALIAS OVERTIME_QUALIFYING_LENGTH_OF_SERVICE AS OT_QLS

 

Allows a different name to be used instead of a database item name. Database items are named by the system and sometimes these names are too long to conveniently use in a formula. Use the ALIAS statement to shorten the name of a database item. Once the ALIAS is created, use it instead of the database item name.

Using an alias is more efficient than assigning the database item to a local variable with a short name.

ASSIGNMENT

variable = expression

array[index] = expression

RATE = HOURLY_RATE + 14
WAGE = HOURS_WORKED * RATE

 

Assigns an expression value to a variable or an array variable at an index position. A formula evaluates the expression on the right hand side of the statement, and places its result in the variable you name on the left hand side. The left side of an assignment statement must always be a local variable because a formula can only change the value of local variables.

Within a CHANGE-CONTEXTS statement only contexts may be assigned values. External to a CHANGE-CONTEXTS statement only input, output, and local variables may be assigned values.

CHANGE-CONTEXTS

CHANGE_CONTEXTS

(context1 = expression1 [,context2 = expression2 ] ...

CHANGE_CONTEXTS(AREA1 = TAX_REPORTING_UNIT_INCOME_TAX_JURISDICTION_GEOGRAPHY_ID)
(
  CHANGE_CONTEXTS(DEDUCTION_TYPE = 'SBJ_TO_REGULAR_TAX')
  (
    L_TAXATION_METHOD = 'NONE'
    EXECUTE('TAXABILITY_RULE_EXISTS')
    IF GET_OUTPUT('TR_EXISTS', 'N') = 'Y' THEN
      L_TAXATION_METHOD = 'REGULAR_TAX'
  ) /* DEDUCTION_TYPE context change undone here. */
) /* AREA1 context change undone here. */

 

Allows one or more contexts to be changed within a formula. The new values are assigned by one or more ASSIGNMENT statements.

DEFAULT

DEFAULT FOR variable IS literal

DEFAULT_DATA_VALUE FOR variable IS literal

DEFAULT FOR HOURLY_RATE IS 3.00
INPUTS ARE HOURLY_RATE
X = HOURS_WORKED * HOURLY_RATE

 

The DEFAULT FOR statement allows a value to be used for a formula input if a value is not provided. The DEFAULT FOR statement allows a value to be used for a database item if the database item value is not found, or if a non-array database item value is NULL.

The DEFAULT_DATA_VALUE FOR statement allows a value to be used for an array database item where individual data values are NULL.

Some database items are defined to require a default value because they could return no data or NULL values from the database.

EXIT

EXIT

FOUND = -1 /* -1 is not a valid index for A. */
I = A.FIRST(-1)
WHILE (A.EXISTS(I)) LOOP
(
  /* EXIT-clause for early exit. */
  IF A[I] = KEY THEN
  (
    FOUND = I
    /* Exit the loop. */
    EXIT;
  )
  I = A.NEXT(I,-1)
)

 

Used to immediately exit from the enclosing WHILE loop. The EXIT statement cannot be used outside of a WHILE loop.

FORMULA CALLING

SET_INPUT(input [,value])

EXECUTE(formula)

The formula RATE_FORMULA is called to get a value for HOURLY_RATE. RATE_FORMULA.

SET_INPUT('UNIT','Hourly')
EXECUTE('RATE_FORMULA')
HOURLY_RATE = GET_OUTPUT('RATE',0.0)
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

 

Instead of writing large formulas, common calculations can be put into their own smaller formulas. These calculation formulas can then be called from other formulas that need the calculation.

IF

IF condition THEN statements

IF condition THEN statements ELSE statements

IF (AGE < 20) THEN
  TRAINING_ALLOWANCE = 30
ELSETRAINING_ALLOWANCE = 40

 

Allows one or more statements to be executed provided a condition evaluates as true. The IF ELSE statement also specifies a set of statements to execute if the condition evaluates to false.

The IF statement is the only statement that can have other statements nested within it, including other IF statements.

INPUT

INPUTS ARE input1 [, input2] ...

INPUTS ARE HOURS_WORKED
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

 

Lists the input variables for the formula. There is only one INPUT statement in a formula.

RETURN

RETURN [ output1 ] [,output2] ...

INPUTS ARE HOURS_WORKED
IF HOURS_WORKED <= 10 THEN
(
  RETURN
  /* This is ignored. */
  BONUS = 10
)
/* This is executed if HOURS_WORKED > 10. */
BONUS = 50
RETURN BONUS

 

Causes a formula to stop executing immediately. A formula output variable must appear in the RETURN statement that stopped the formula for its value to be returned to the caller. Multiple return statements are allowed in a formula.

WHILE

WHILE condition LOOP statements

In this example, 'A' is an array variable with a numerical index.

/* -1234 is not a valid index for A in this instance, so use as default. */
NI = A.FIRST(-1234)
WHILE A.EXISTS(NI) LOOP
(
  VA = A[NI] /* Do some processing with element at index NI. */
  NI = A.NEXT(NI,-1234) /* Go to next index. */
)

 

The WHILE loop executes a number of statements as long as a condition evaluates to true.

To prevent endless looping, an error occurs if the WHILE statement loop performs an excessive number of iterations.

WORKING STORAGE

WSA_DELETE([item]) - Deletes values from the storage area.

WSA_EXISTS(item[,type]) - Determine if an item exists .

WSA_GET(item, value) - Fetches values from the storage area.

WSA_SET(item, value) - Sets values from the storage area.

In the following example, a number of rates are set up:

/* Formula: RATE_SETTER */
WSA_SET('RATE:HOURLY1',3.5)
WSA_SET('RATE:HOURLY2',4.0)
WSA_SET('RATE:HOURLY3',4.5)
WSA_SET('RATE_FLAG','Y') /* Flag to say that the rates have been set. */

 

Use the working storage area statements to store reference data.

Ordering Statements

Statements need to be placed in a formula in the following order:

  1. ALIAS statements, if any

  2. DEFAULT statements, if any

  3. INPUT statements, if any

  4. Other statements

Grouping Statements

If you want to group more than one statement, under IF - THEN statement, ELSE clauses, WHILE-loop, or CHANGE_CONTEXTS, enclose the group of statements within brackets. In the absence of brackets, the preceding statement only applies to the first statement.

Correct example:

I = A.FIRST
WHILE (A.EXISTS(I)) LOOP
(
  A[I] = I
  I = A.NEXT(I,-1)
)

 

Incorrect example:

I = A.FIRST
WHILE (A.EXISTS(I)) LOOP
  A[I] = I
  I = A.NEXT(I,-1) /* This is not executed as part of the loop. */

 

Classes of Variables: Explained

Formula variables can have frequently changing values. The variable's data type determines the type of information it holds. You do not have to specify what type you want a variable to be. The formula works out the type from how you use the variable. For example, if you set a variable to 'J. Smith', this is interpreted as a TEXT variable. The system also warns you if you try to perform any inconsistent operations, such as trying to add a number to a text string.

There are three classes of variables:

Variable values can be changed using an assignment statement and referenced within expressions. However, if a variable has not been given a value when referenced, the formula will raise an error.

Naming Variables: Explained

There are two naming schemes for variables. In both cases, the maximum size of a variable name is 255 characters.

In the first naming scheme, variables have names comprising one or more words, joined by underscores. The words must each start with a letter and can be followed by a combination of letters, and digits.

In the second naming scheme, variable names begin and end with double quotes ("). Between the quotes, any printable characters can be used such as "This is a quoted variable name!". Note that any word consisting of only digits could be mistaken for numbers.

Formulas are not case sensitive. For example, the variable named EMPLOYEE_NAME is the same as the variable employee_name.

The following reserved words cannot be used as the names of variables:

ALIAS
AND
ARE
AS
CHANGE_CONTEXTS
DEFAULT
DEFAULT_DATA_VALUE
DEFAULTED
ELSE
EMPTY_DATE_NUMBER
EMPTY_NUMBER_NUMBER
EMPTY_TEXT_NUMBER
EMPTY_DATE_TEXT
EMPTY_NUMBER_TEXT
EMPTY_TEXT_TEXT
EXIT
FOR
IF
INPUTS
IS
LIKE
LOOP
NEED_CONTEXT
NOT
OR
RETURN
THEN
USING
WAS
WHILE

 

Formula Data Types

DATE
DATE_NUMBER
DATE_TEXT
NUMBER
NUMBER_NUMBER
NUMBER_TEXT
TEXT
TEXT_NUMBER
TEXT_TEXT

 

Array Methods

COUNT
DELETE
EXISTS
FIRST
LAST
NEXT
PREVIOUS
PRIOR

 

Built-in Calls

CONTEXT_IS_SET
EXECUTE
GET_CONTEXT
GET_OUTPUT
IS_EXECUTABLE
SET_INPUT
WSA_DELETE
WSA_EXISTS
WSA_GET
WSA_SET

 

Database Items: Explained

Database items exist in the application database and have associated code that the system uses to find the data. All database items are read-only variables. An attempt to write to a database item causes a compiler error.

Database item values cannot be changed within a formula. Database items exist in the application database and have a label, hidden from users, which the system uses to find the data. Database items are specific to the context in which you use them.

There are two types of database items:

Static Database Items

Static database items are predefined. They include standard types of information, such as the sex, birth date, and work location of an employee, or the start and end dates of a payroll period.

Dynamic Database Items

Dynamic database items are generated from your definitions of:

Array Database Items

There are also array database items. Array database items have an index type of NUMBER with indexes starting at 1 and increasing by 1 without gaps.

/* 1 is the starting index for an array database item. */
I = 1
WHILE DBI.EXISTS(I) LOOP
(
  V = DBI[I] /* Do some processing with element at index I. */
  I = I + 1 /* Array database items indexes go up in steps of 1. */
)

 

The default data value for a statement is used to set a default value in the case where an array database item could return a NULL value for an element. This is an extension of standard database item behavior. There can only be one default data value for a statement for each array database item and it must appear at the start of the formula.

An example of a default data value for a statement:

DEFAULT_DATA_VALUE FOR A IS 0
INPUTS ARE B, C

 

An example of an array database item usage error case:

 /* Array database item A. */
A[1] = 1
 A = B
A.DELETE(1)
A.DELETE

 

Formula Operators: Explained

An expression may contain arithmetic operators, which determine how variables and literals are manipulated. For example, the operator + indicates that two items are added together. It is also used for string concatenation.

Types of Operators

The operator types are described in the following table.


Operator

Description

Example

+

Addition

A = B + 1

+

| |

String concatenation

A = 'Hello ' + 'World'

B = 'Hello ' || 'World'

-

Subtraction

A = B - 1

-

Unary minus

A = -B

*

Multiplication

A = B * C

/

Division

A = B / C

Using Operators

The arithmetic operators, subtraction, multiplication, and division, can only be used with numeric operands. The addition operator can be used with numeric or text operands. The operands can be variables, literals, or sub-expressions. A formula error occurs if:

Expressions are evaluated in order from left to right. The unary minus has precedence over the other operators because it applies directly to a single sub-expression. The multiplication and division operators take precedence over addition and subtraction. For example, the expression 1 + 2 * 3 evaluates to 7 rather than 9. Brackets can be used to change precedence. For example, (1 + 2) * 3 evaluates to 9.

Literals: Explained

Every piece of information that you can manipulate or use in a fast formula is a literal.

There are four types of literals:

Numeric Literals

Enter numeric literals without quotes. Precede negative numbers with a minus sign (-). Numbers may have a decimal component after a decimal point. Do not use exponents and floating point scientific notations. Do not use commas or spaces in a number.

Examples of numeric literals are:

Text Literals

Enclose text literals in single quotes. They may contain spaces. You can represent the single quote character in a text constant by writing it twice (''). Note that this is not the same as the double quote (").

Examples of text literals are:

Date Literals

Enclose dates in single quotes and follow immediately with the word date, in brackets. Use the format YYYY-MM-DD"T" HH:MI:SS.FFF"Z", YYYY-MM-DD HH24:MI:SS, or DD-MON-YYYY. It is recommended that you use the first two formats if you want to compile the formula under different language settings.

Examples of date literals are:

Array Literals

An array holds multiple values that can be accessed using the corresponding index values. Arrays literals are defined only for an empty array of each type.

The array types are:

Examples of array literals are:

Formula Variable Data Types: How They Are Determined

The data type of a variable can be numeric, text or date. The data type determines the type of information the variable holds. You do not have to specify the variable type. Formulas determine the type from how you use the variable. For example, if you set a variable to 'J. Smith', this is interpreted as a text variable. The system also warns you if you try to perform any inconsistent operations, such as trying to add a number to a text string.

Settings That Affect Variable Data Types

A formula will fail to compile where variables are used inconsistently or incorrectly. Examples of such errors are:

How Variable Data Types Are Determined

The rules that determine the variable data type in a formula are processed in the following order:

  1. The variable can be an input you name in the input statement. For example:

    INPUTS ARE SALARY_AMOUNT,
    START_DATE (DATE),
    FREQUENCY (TEXT)

     

    If you do not specify the variable data type in the statement, the formula assumes it has data type number.

    The variable data type can be determined from a DEFAULT FOR statement such as:

    DEFAULT FOR A IS EMPTY_NUMBER_NUMBER /* A is a NUMBER_NUMBER array variable. */

     

    The type can also be determined from a DEFAULT_DATA_VALUE statement:

    DEFAULT_DATA_VALUE FOR B IS 0 /* B is a NUMBER_NUMBER database item. */

     

    The DEFAULT_DATA_VALUE statement applies to array database items. Array database items have a NUMBER index type and the type of default data value determines the array's value type.

  2. The formula searches the list of database items. If it is in the list, the data type is known.

  3. If the variable appears in a context handling statement then the formula searches the list of contexts. If it is in the list, then the formula knows the data type, otherwise an error is raised.

  4. If the variable is not a database item or a context, then it is treated as a local variable. The data type is determined by the way in which the variable is used. For example:

    A = 'abc' /* A is a TEXT variable. */

     

Array Variables: Explained

Formulas have array variables holding date, number, or text values. If the data type cannot be determined then the data type is defaulted to number.

Arrays are similar to PL/SQL index-by tables. Do not specify the array size limit. Arrays are provided for convenience and excessive use and large arrays will result in excessive memory consumption.

The index types are either text or number. Text indexes are upper case unique. Gaps in index value sequences are permitted. Number indexes are truncated to remove any fractional part. An array may be iterated in index forwards or backwards. Methods are provided to get the first and last indexes and to get the next or prior index given an index. A method is also provided to test the existence of an index.

Array types are specified as <DATA TYPE>_<INDEX TYPE> giving: NUMBER_NUMBER, NUMBER_TEXT, DATE_NUMBER, DATE_TEXT, TEXT_NUMBER, and TEXT_TEXT. Arrays can be used for input, output, and local formula variables. Contexts cannot be array types. Formula functions cannot return arrays nor take array parameters. Methods for returning first, last, next, prior indexes take a default value to be used if the required indexes do not exist. These methods return the index data type. An attempt to delete a value at a nonexistent index does not cause an error. An attempt to reference an array value at a nonexistent index causes an error to be raised. The array method syntax does not work directly with the array literal values. For example, it is not possible to use a construct such as EMPTY_DATE_NUMBER.COUNT.

Array Methods

Examples of array method syntax:


Array Method

Description

Usage Example

<name> [ <index value> ]

Get the value for an index.

V = A[1]

<name> . FIRST( <default value> )

Get the first index for an array. The default value is returned if the array is empty.

I = A.FIRST(-1)

<name> . LAST( <default value> )

Get the last index for an array.

L = B.LAST(' ')

<name> . EXISTS( <index value> )

Conditional checking if a value exists at an index. The default value is returned if the array is empty.

IF A.EXISTS(1) THEN

<name> . NEXT( <index value> , <default index value> )

Get the next index given an index position. The default value is returned if there is no next index.

N = A.NEXT(1)

<name> . PRIOR( <index value> , <default index value> )

Get the prior index given the index position. The default value is returned if there is no prior index.

P = B.PRIOR('Two')

<name> , COUNT

Numeric method to count the array elements.

C = A.COUNT

<name , DELETE( <index value> )

Delete the element at an index position.

B.DELETE('three')

<name> , DELETE()

Delete all elements.

B.DELETE()

Iterating Through an Array

In the following example, A is an array variable with a NUMBER index. -1234 is known to be an invalid index for A so it is used as a default value when the FIRST and NEXT calls cannot find an index.

/* -1234 is not a valid index for A in this instance, so use as default. */
NI = A.FIRST(-1234)
WHILE A.EXISTS(NI) LOOP
(
  VA = A[NI] /* Do some processing with element at index NI. */
  NI = A.NEXT(NI,-1234) /* Go to next index. */
)

 

The following example does the same thing for array variable B with a TEXT index.

/* 'No Index' is not a valid index for A in this instance, so use as default. */
TI = B.FIRST('No Index')
WHILE B.EXISTS(TI) LOOP
(
  VB = B[TI] /* Do some processing with element at index TI. */
  TI = B.NEXT(TI, 'No Index') /* Go to next index. */
)
The following example iterates backwards from through an array C with a NUMBER index.
/* -1234 is not a valid index for C in this instance, so use as default. */
NI = C.LAST(-1234)
WHILE C.EXISTS(NI) LOOP
(
  VC = C[NI] /* Do some processing with element at index NI. */
  NI = C.PRIOR(NI,-1234) /* Go to prior index. */

 

Formula Contexts: Explained

A formula executes within an application-specific execution context. Formula context variables specify the formula execution context.

Examples of contexts are:

Context values act as SQL bind values when database item values are fetched from the database. They can also be passed into formula function calls.

Context Value Setting

The application code calling a formula usually sets all the context values. For some complex applications, such as the payroll run, the code only sets the contexts necessary to meet general processing requirements. A payroll run sets contexts for the legislative data group, date earned, the payroll being processed, the payroll relationship, payroll actions, and the person being processed. Payroll formulas also use additional contexts whose setting is country-specific. For example, the jurisdiction area and tax code context values are localization-specific and are set within formulas using a variety of mechanisms.

Formula Context-Handling Statements

If a variable appears in a context handling statement the formula searches the list of contexts. The variable must appear in the contexts list, otherwise the formula raises an error. The data type is held with the context list entry.

Formula context handling statements are described below.

Working Storage Area: Explained

The working storage area is a mechanism for storing global values across formulas. The values are accessed by name. The names are case-independent.

There are four working storage area call methods:

The working storage area methods are described in the below table.


Method

Description

WSA_EXISTS(item [, type])

Test whether or not the item called item exists in the storage area. If type is specified then the item must be of the same type. The valid values for type are one of the strings: DATE, DATE_NUMBER, DATE_TEXT, NUMBER, NUMBER_NUMBER, NUMBER_TEXT, TEXT, TEXT_NUMBER, or TEXT_TEXT.

WSA_DELETE([item])

Delete the item called item. If a name is not specified then all storage area data is deleted.

WSA_SET(item, value)

Set the value for the item called item. Any existing item of the same name is overwritten.

WSA_GET(item, default-value)

Gets a value for the item called item. If there is no item called item then default-value is returned. The data type of default-value is the expected data type for item.

Calling a Formula from a Formula: Explained

A formula can be called from another formula. This enables some modularity in formula organization. The called formula name, and any formula input or output names are specified as TEXT values. The names are case-independent.

When the formula runs, checks are performed to ensure the called formula can be executed, and whether the specified input and output data types are correct. The IS_EXECUTABLE call determines whether an executable formula with a specified name exists. The formula must be compiled and visible according to data partitioning requirements. Also, it must be valid as of the effective date of calling formula execution. Payroll code imposes extra restrictions based on formula type combinations.

This table describes the methods used when calling a formula from a formula.


Method

Description

EXECUTE(formula)

Execute the called formula.

GET_OUTPUT(output, default-value)

Get the value for the called output after calling a formula. If there is no formula output called output or it is not set then a default value is returned. The data type of default value is the expected data type for output.

IS_EXECUTABLE(formula)

Test whether the called formula is executable.

SET_INPUT(input [,value])

If a value is provided, set the value for the called input before calling a formula to value. The data type of the value is the expected data type for input. If the value is not provided then the input is passed as unset to the formula.

Note

Formula inputs set using SET_INPUT persist as long as no EXECUTE or GET_OUTPUT calls are made. Output values from a called formula persist as long as no SET_INPUT or new EXECUTE calls are made. Any saved input or output values are also removed when the calling formula exits.

There are three stages in calling a formula:

Set the Inputs

Use a SET_INPUT call for each formula input and context whose value needs to be explicitly set for the formula call. It is not necessary to specify all formula inputs and contexts. If a formula input is not specified, it is unset in the called formula. If not specified, context values are inherited from the calling formula. Context values can be explicitly unset in the called formula by using the single argument SET_INPUT call.

Call the Formula

Use an EXECUTE call to call a formula. Any extra inputs specified in SET_INPUT calls are ignored. Errors are raised if the formula is not executable, if the called formula is already executing, or if an input variable's data type, as specified using SET_INPUT, does not match its actual data type within the formula.

Get the Formula Outputs

Use one or more GET_OUTPUT calls to fetch outputs from the last formula call. An error is raised if an output variable's data type, as specified using GET_OUTPUT, does not match its actual data type within the formula. A GET_OUTPUT call has a default value that is returned when the specified output does not exist, or was not set by the called formula.

Formula Errors

Formula Compilation Errors: Explained

Compilation errors display in the Manage Fast Formulas page when you compile the formula. The formula compiler returns line numbers starting at 1 from the beginning of a formula, and character positions starting at 1 from the beginning of a line in its error messages. The compiler aborts compilation when an error is encountered.

Common Compilation Errors

This table lists the type and description of several common formula compilation errors.


Formula Error

Description

Syntax Error

The formula text violates the grammatical rules for the formula language. An example is using IF1 instead of IF for an IF statement.

Incorrect Statement Order

ALIAS, DEFAULT, or INPUT statements come after other statements.

Misuse of ASSIGNMENT Statement

Occurs when any of these conditions occurs:

  • An ASSIGNMENT assigns a value to a database item.

  • A context is assigned a value externally to a CHANGE-CONTEXTS statement.

  • A non-context variable is assigned a value within a CHANGE-CONTEXTS statement.

Misuse of ALIAS Statement

An ALIAS statement may only be used for a database item.

Missing DEFAULT Statement

A database item with defaulting specified must have a DEFAULT statement.

Misuse of DEFAULT Statement

A DEFAULT statement is specified for a variable other than an input or database item.

Uninitialized Variable

The compiler detects that a variable is uninitialized when used. The compiler cannot do this in all cases. This error often occurs when you want to use a database item, but a database item is not available in the formula.

Missing Function Call

A function call is not recognized. The combination of return type, function name, and parameter types does not match any available function.

Incorrect Operator Usage

An instance of a formula operator use does not match the permitted uses of that operator.

For example, the + operator has two permitted uses. The operands are both of data type NUMBER, or both of data type TEXT.

Inconsistent Data Type Usage

A formula variable is being used as if it is of more than one data type. Or a database item or context is being used with the wrong data type.

For example, Variable A is assigned a NUMBER value at the start of the formula, but a TEXT value later in the formula.

EXIT Statement Not Within WHILE Loop

A condition that eventually becomes false, or an EXIT call for exiting the loop does not exist.

Misuse of Context

A variable is used as a context, or a context is used as a variable.

For example, AREA1 is assigned a value as an ordinary variable, but later in the formula AREA1 used as a context in a GET_CONTEXT call.

Formula Execution Errors: Explained

Fast formula execution 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. These errors contain the formula line number where the error occurs.

Formula Execution Errors

This table lists the type and description of each formula execution error.


Formula Error

Description

Uninitialized Variable

Where the formula compiler cannot fully determine if a variable or context is initialized when it is used, it generates code to test if the variable is initialized.

When the formula executes and the variable or context is not initialized an error is raised.

Divide by Zero

Raised when a numeric value is divided by zero.

No Data Found

Raised when a non-array type database item unexpectedly fails to return any data. If the database item can return no data then it should allow defaulting.

This error is also raised from within a formula function. The cause is an error in the formula function code.

Too Many Rows

Raised when a non-array type database item unexpectedly returns more than a single row of data. The cause is an incorrect assumption made about the data being accessed.

This error can also be raised from within a formula function. The cause is an error in the formula function code.

NULL Data Found

Raised when a database item unexpectedly returns a NULL data value. If the database item can return a NULL value then defaulting is allowed.

Value Exceeded Allowable Range

Raised for a variety of reasons, such as exceeding the maximum allowable length of a string.

Invalid Number

Raised when an attempt is made to convert a non numeric string to a number.

User Defined Function Error

Raised from within a formula function. The error message text is output as part of the formula error message.

External Function Call Error

A formula function returned an error, but did not provide any additional information to the formula code. The function might have output error information to the logging destination for the executing code.

Function Returned NULL Value

A formula function returned a NULL value.

Too Many Iterations

A single WHILE loop, or a combination of WHILE loops, has exceeded the maximum number of permitted iterations. The error is raised to terminate loops that could never end. This indicates a programming error within the formula.

Array Data Value Not Set

The formula attempted to access an array index that has no data value. This is an error in the formula code.

Invalid Type Parameter for WSA_EXISTS

An invalid data type was specified in the WSA_EXISTS call.

Incorrect Data Type For Stored Item

When retrieving an item using WSA_GET, the items actual data type does not match that of the stored item. This is an error within the calling formula.

Called Formula Not Found

The called formula could not be resolved when attempting to call a formula from a formula. This could be due to an error in the calling formula, or because of installation issues.

Recursive Formula Call

An attempt was made to call a formula from itself. The call could be directly or indirectly via another called formula. Recursive formula calling is not permitted.

Input Has Different Types In Called and Calling Formulas

When calling a formula from a formula, the actual formula input data type within the called formula does not match the data type specified from the calling formula.

Output Has Different Types In Called and Calling Formulas

When calling a formula from a formula, the actual formula output data type within the called formula does not match the data type specified from the calling formula.

Too Many Formula Calls

There are two many formula from formula calls. This is due to a problem with the formulas.

Formula Functions

Formula Functions: Explained

Functions manipulate data in different ways and always return a value. They are restricted to simple data types of date, number, and text. Some functions work on only one type of data, some can work on two, others work on more than one data types.

A function is specified by its name, return data type, parameter data types, and parameter usage behavior. The general form of a function is:

NAME-OF-FUNCTION(operand,operand,...)

 

Parameters can be optional or mandatory. They may be repeated any number of times, such as with the GREATEST function. The formula compiler resolves functions by matching function calls against function specifications. You can use multiple functions with the same name within a formula provided that they have different return or parameter data types.

Functions can work one or multiple data types:

Note

There are additional formula functions for specific applications, such as payroll, benefits, or compensation.

Text Formula Functions

The following formula functions manipulate text data.

CHR(n)

Returns the character having the binary equivalent to a number operand n in the ASCII character set.

GREATEST(expr, expr [,expr]....)

Compares the values of all the text string operands. It returns the value of the last string in alphabetic order.

INITCAP(expr)

Returns the expression expr with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

INSTR(expr1, expr2 [,n [,m]])

Searches expr1 beginning with its nth character for the mth occurrence of expr2 and returns the character position in expr1 for the first character of this occurrence. If n is negative, INSTR counts and searches backward from the end of expr1. The value of m must be positive. The default values of both n and m are 1, meaning INSTR begins searching at the first character of expr1 for the first occurrence of expr2. The return value is relative to the beginning of expr1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (expr1 does not appear m times after the nth character of expr1) the return value is 0.

INSTRB(expr1, expr2 [,n [,m]])

The same as INSTR, except that n and the return value are expressed in bytes, rather than in characters. For a single-byte character set, INSTRB is equivalent to INSTR.

LEAST(expr, expr [,expr]...)

Compares the values of all the text string operands. Returns the first string in alphabetic order from among its operands.

LENGTH(expr)

Returns the number of characters in the text string operand expr.

LENGTHB(expr)

Returns the length of expr in units of bytes.

LOWER(expr)

Converts a text string to lower case.

LPAD(expr, n [,pad])

Returns the text string operand expr left-padded to length n with the sequence of characters in pad. The default for pad is a blank. If expr is longer than n, then LPAD returns the portion of expr that fits in n.

Examples:

/* A is set to 'XYXYXhello' */
A = LPAD ('hello, 10, 'XY')
/* A is set to 'hell' */
A = LPAD ('hello', 4 )

 

LTRIM(expr [,set])

Returns the text string operand expr with all the left-most characters that appear in set removed. The default for set is a blank. If none of the left-most characters of expr appear in set then expr is returned.

Examples:

/* A is set to 'def' */
A = LTRIM ('abcdef','abc')
/* A is set to 'abcdef' */
A = LTRIM ('abcdef','bc')

 

REPLACE(expr, search [,replacement])

Returns the text string operand expr with every occurrence of search replaced with replacement. If replacement is omitted, all occurrences of search are removed. Use REPLACE to substitute one string for another as well as to remove character strings.

Example:

/* Set A to 'BLACK and BLUE'. */
A = REPLACE('JACK and JUE', 'J', BL')

 

RPAD(expr, n [,pad])

Returns the text string operand expr right-padded to length n with the sequence of characters in pad. The default for pad is a blank. If expr is longer than n, then RPAD returns the portion of expr that fits in n.

Examples:

/* A is set to 'helloXYXYX' */
A = RPAD ('hello, 10, 'XY')
/* A is set to 'hell' */
A = RPAD ('hello', 4 )

 

RTRIM(expr [,set])

Returns the text string operand expr with all the right-most characters that appear in set removed. The default for set is a blank. If none of the right-most characters of expr appear in set then expr is returned.

Examples:

/* A is set to 'abc' */
A = RTRIM ('abcdef','def')
/* A is set to 'abcdef' */
A = RTRIM ('abcdef','de')

 

SUBSTR(expr, m [,n]) or SUBSTRING(expr, m [,n])

SUBSTRING returns a substring of the text string operand expr of length n characters beginning at the mth character. If n is negative, SUBSTR counts backwards from the end of expr. If you omit the n, the substring starts from m and finishes at the end of expr.

Example:

/* Check that the tax code starts with GG */
IF length(Tax_code) <= 2
THEN
(message = 'Tax code is too short'
RETURN message
)
IF substr( Tax_code, 1, 2) = 'GG' THEN ...

 

SUBSTRB((expr, m [,n])

The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.

TRANSLATE(expr,from,to)

Returns the text string operand expr with all occurrences of each character in from replaced by its corresponding character in to. Characters in expr that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in expr, they are removed from the return value.

TRIM(expr)

Trims leading and trailing spaces from a character string.

UPPER(expr)

Converts a text string to upper case.

Numeric Formula Functions

The following formula functions manipulate numeric data.

ABS(n)

Returns the magnitude of a numeric operand n as a positive numeric value. If the value of the operand is positive, its value returns unchanged. If the operand is negative then the value's sign inverts, and the value returns as a positive number.

Example:

ABS (-17)

 

It returns 17.

FLOOR(n)

Returns the integer part of a numeric operand n. If the value of the operand contains information after the decimal point, FLOOR discards that information and returns a whole number.

Example:

FLOOR(35.455)

 

It returns 35.

GREATEST(n, n [, n] ...) or GREATEST_OF(n, n [, n] ...)

Compares all the operands and returns the largest value.

LEAST(n, n [, n] ...) or LEAST_OF(n, n [, n] ...)

Compares all the operands and returns the smallest value.

MOD(m, n)

Returns the remainder from dividing m by n.

POWER(m, n)

Returns m raised to the nth power.

ROUND(m [,n])

Rounds m to n decimal places. The default number of decimal places is 0.

Examples:

ROUND(2.3401, 2)

 

It returns 2.34.

ROUND (2.3461, 2)

 

It returns 2.35.

ROUNDUP(m [,n]) or ROUND_UP(m [,n])

Rounds m up to n decimal places. The default number of places is 0.

Examples:

ROUND_UP(2.3401, 2)

 

It returns 2.35.

ROUND_UP(2.3400, 2)

 

It returns 2.34.

TRUNC(n [,m]) or TRUNCATE(n [,m])

Truncates m down to n decimal places. The default number of places is 0.

Examples:

TRUNC(2.3401, 2)

 

It returns 2.34.

Date Formula Functions

The following formula functions manipulate date data.

ADD_DAYS(date, n)

Adds n whole days to date.

Example:

ADD_DAYS ('30-DEC-1990' (date), 6)

 

It returns 5 JAN 1991.

ADD_MONTHS(date, n)

Adds n whole months to date.

ADD_YEARS(date, n)

Adds n whole years to date.

DAYS_BETWEEN(date1, date2)

Returns the number of days between date1 and date2. If date1 is later than date2 then the result is a positive number. If date1 is earlier than date2 then the result is a negative number.

Example:

DAYS_BETWEEN('1995/06/27 00:00:00' (date), '1995/07/03 00:00:00' (date))

 

It returns - 5.

GREATEST(date, date [, date] ...)

Compares its operands and returns the latest date.

LAST_DAY(date)

Returns the last day of the month containing date.

LEAST(date, date [, date] ...)

Compares the operands and returns the earliest date.

MONTHS_BETWEEN(date1, date2)

Returns the number of months between date1 and date2. If date1 is later than date2, the result is a positive number. If date1 is earlier than date2, the result is a negative number. The return value has a numeric data type that can contain a fraction if the dates do not differ by a whole number of months.

NEW_TIME(date, zone1, zone2)

Returns the date and time in zone zone2 when the date and time in zone zone1 are date.

The arguments zone1 and zone2 can be any one of the standard text strings such as:


Time Zone

Description

AST or ADT

Atlantic Standard or Daylight Time

BST or BDT

Bering Standard or Daylight Time

CST or CDT

Central Standard or Daylight Time

EST or EDT

Eastern Standard or Daylight Time

GMT

Greenwich Mean Time

HST or HDT

Alaska-Hawaii Standard Time or Daylight Time

MST or MDT

Mountain Standard or Daylight Time

NST

Newfoundland Standard Time

PST or PDT

Pacific Standard or Daylight Time

YST or YDT

Yukon Standard or Daylight Time

NEXT_DAY(d, expr)

Returns the first date following d of the weekday named by expr.

ROUND(date [,format])

Returns the result of rounding date according to format. The default format is DDD, which represents the nearest day.

TRUNC(date [,format])

Returns the result of truncating date according to format. The default format is DDD, which represents a whole day.

Data Conversion Formula Functions

The following formula functions perform data conversions.

DATE_TO_TEXT(date [,format]), TO_CHAR(date [,format]), and TO_TEXT(date [,format])

Converts date to a character string with format specified by format. The default format is the application canonical format.

NUM_TO_CHAR(n, format)

Converts the number n to a character string in the specified format. This function is equivalent to the SQL TO_CHAR function.

TO_CHAR(n) and TO_TEXT(n)

Converts the number n to a character string in canonical number format.

TO_DATE (expr [, format])

Converts the character string expr in the specified format to a date. If no format is specified then expr must be in canonical format.

TO_NUMBER(expr) and TO_NUM(expr)

Converts the character string expr to a number. The character string must be in canonical number format. A period is used for the decimal point, such as 1.234. Negative numbers are preceded with a minus, such as -1.234.

Miscellaneous Formula Functions

The following formula functions manipulate messaging data.

GET_MESG(appname, msgname [, token1, value1] [, token2, value2] [, token3, value3] [, token4, value4] [, token5, value5] ) and GET_FND_MESG(appname, msgname [, token1, value1] [, token2, value2] [, token3, value3] [, token4, value4] [, token5, value5] )

Returns an expanded version of the application message specified using appname, msgname and up to five pairs of message tokens and their corresponding values.

HR_TRACE(expr)

Outputs a trace message.

Note

It is more efficient to us a application-specific logging function than HR_TRACE.

Writing a Fast Formula using Expression Editor: Worked Example

This example demonstrates how to create a fast formula that groups executive workers for reporting and processing. All executive workers are in department EXECT_10000. Once the formula is created it will be added as object group parameters so that only those workers in department EXECT_10000 are used in processing.

The following table summarizes key decisions for this scenario:


Decisions to Consider

In This Example

Is the formula for a specific legislative data group?

Yes, InVision

What is the formula type for this formula?

Payroll Relationship Group

Creating a Fast Formula Using the Expression Editor

  1. On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
  2. On the Manage Fast Formula page, click the Create icon to create a new formula.
  3. On the Create Fast Formula page, complete the fields as shown in this table.

    Field

    Value

    Formula Name

    Executive Payroll Relationship Grp

    Formula Type

    Payroll Relationship Group

    Description

    Executive Workers

    Legislative Data Group

    Vision LDG

    Effective Start Date

    1-Jan-2010


  4. Click Continue.
  5. In the Formula Details section, click Add After to add a row to enter the fields in this table.

    Conjunction

    User Name

    Data Type

    Operand

    Value

    IF

    DEPARTMENT

    Character

    =

    'EXECT_10000'

    Then

    SELECT_EMP

    Character

    =

    'YES'

    ELSE

    SELECT_EMP

    Character

    =

    'NO'


  6. Click Compile.
  7. Click Save.

Writing a Fast Formula Using Formula Text: Worked Example

This example demonstrates, using the text editor, how to create a fast formula that returns the range of scheduled hours for managers and a different range for other workers.

The following table summarizes key decisions for this scenario:


Decisions to Consider

In This Example

Is the formula for a specific legislative data group?

No, this is a global formula that can be used by any legislative data group.

What is the formula type for this formula?

Range of Scheduled Hours

Are there any contexts used in this formula?

No

Are there any database item defaults?

Yes, ASG_JOB

Are there any input value defaults?

No

What are the return values?

MIN_HOURS, MAX_HOURS, FREQUENCY

Creating a Fast Formula Using the Text Editor to Determine a Manager's Scheduled Hours

  1. On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
  2. On the Manage Fast Formula page, click the Create icon to create a new formula.
  3. On the Create Fast Formula page, complete the fields as shown in this table.

    Field

    Value

    Formula Name

    Manager Range of Scheduled Hours

    Formula Type

    Range of Scheduled Hours

    Description

    Manager's Range of Hours

    Effective Start Date

    1-Jan-2010


  4. Click Continue.
  5. Enter the following formula details in the Formula Text section:
    /* DATABASE ITEM DEFAULTS BEGIN */
    DEFAULT FOR asg_job IS ' '
    /* DATABASE ITEM DEFAULTS END */
    JOB_1 = ASG_JOB
    IF JOB_1 = 'Manager' then
    (MIN_HOURS = 25
    MAX_HOURS = 40
    FREQUENCY = 'H')
    else
    (MIN_HOURS = 20
    MAX_HOURS = 35
    FREQUENCY = 'H')
    return MIN_HOURS, MAX_HOURS, FREQUENCY

     

  6. Click Compile.
  7. Click Save.

Formulas for Accrual Plans

Accrual Plan Rules: Points to Consider

You can specify the following accrual plan rules on the Accrual Plan page in accordance with the leave policy of your enterprise:

Note

If you do not specify values for any of the above plan rules, the accrual plan uses the default values in the associated accrual formula.

Accrual Start Rule

You use an accrual start rule to determine when newly enrolled employees start to accrue time. For example, some accrual plans allow new hires to accrue time from the date of their hire. If the predefined start rules that are available on the Accrual Plan page do not meet your requirements, you can add your own rule directly in the accrual formula.

Accrual Term and Frequency

You can specify the type of the accrual term and its length during which employees accrue time. For example, you can define an accrual term of one calendar year that restarts on January 1, or on the employee's annual hire date. You define the frequency at which employees accrue time during an accrual period, for example, two days every pay period, or two days every month.

Ineligibility Period

You can define a period, such as six months from the date of hire in which newly hired employees accrue time, but not use it until the end of the period. Although you can define the ineligibility period in the Accrual Plan page, if you have more complex rules to incorporate, you can include those rules in the accrual formula.

Gross Accrual Maintenance

Oracle Fusion Global Payroll users can choose to store gross accruals in a payroll balance. The advantage is that the gross accruals are calculated since the last payroll run, and not for the entire accrual term, thus reducing the number of calculations.

Accrual Bands

Use accrual bands to define accrual benefits for employees. You can create as many bands as you require.

Net Entitlement Rules

The accrual plan generates the net accrual calculation for enrolled employees. By default, the absence types that you associate with the accrual plan appear as deductions in the calculation. However, you can include other elements to customize the calculation.

Accrual Start Date for New Hires: How It Is Calculated

By default, the payroll accrual formulas (Accrual Payroll Calculation formula and Accrual Payroll Balance Calculation formula) start the accrual from January 1, and the simple accrual formulas (Accrual Simple Multiplier and Accrual Simple Balance Multiplier), from June 1.

Settings That Affect Accrual Start Date

The formula uses the start rule you specified for the accrual plan in the Accrual Plan page.

How the Start Rules Are Interpreted

On the basis of the start rule, the formula calculates the start date from the employee's hire date and compares it with the plan enrollment date. Accrual begins on whichever of these two dates is later.

The following table describes how the formula interprets each start rule.


Start Rule

How the Formula Interprets It

Hire Date

Accruals begin from the first full pay period following the hire date. If the hire date is on the first day of the pay period, the participant starts to accrue time as of that date.

For example, if the hire date of a participant on a monthly payroll falls on January 10, 2011, and the pay period starts on the first of every month, accruals start on February 1, 2011. If the hire date falls on January 1, 2011, then the participant starts to accrue time on the same day.

Beginning of calendar year

Accruals begin from the start of the year following the year of hire.

For example, a participant with a hire date of January 1, 2011 starts to accrue time from January 1, 2012.

Six months after hire

Accruals begin from the first full pay period after the six-month anniversary of the hire date. If the six-month anniversary falls on the first day of the pay period, the participant starts to accrue time as of that date.

For example, a participant on a semi-monthly payroll who is hired on 9 February, 2011 completes six months service on 9 August, 2011. The participant starts to accrue time on the first day of the second pay period in August. If the hire date was on 1 February, 2011, the participant starts to accrue time on the first day of the first pay period in August.

The period of ineligibility does not apply to accrual plans using this start rule.

Changing Start Rules

There are two ways to change the start rules:

Accrual Formulas: Critical Choices

The accrual formula calculates the gross accrual on the basis of time that employees accrue in each accrual period. The type of accrual formula that you want to associate with your accrual plan depends on how you want to implement the following plan rules:

Use the following table to decide which formula to select for your accrual plan. For example, if you want your employees to accrue time per payroll period and you want to maintain gross accruals using a payroll balance, you must base your formula on the Accrual Payroll Balance Calculation formula.


Plan Rule Implementation

Accrual Payroll Calculation Formula

Accrual Payroll Balance Calculation Formula

Accrual Simple Multiplier Formula

Accrual Simple Balance Multiplier Formula

Accruals per payroll period

Yes

Yes

No

No

Accruals per calendar period, such as month

No

No

Yes

Yes

Gross accrual maintenance using payroll balance

No

Yes

No

Yes

Note

The Accrual Payroll Calculation formula and the Accrual Simple Multiplier formula incorporate the same rules as the accrual formulas that use balances, except that they cannot be called from the payroll run to maintain a balance of gross accruals.

Accrual Formulas That Support Payroll Balances

Use the following table to compare the differences in the default calculation methods of the Accrual Payroll Balance Calculation formula and the Accrual Simple Balance Multiplier formula. You can also use the table to check if you can change a particular plan rule on the Accrual Plan page


Plan Rule

Default Calculation Method (Accrual Payroll Balance Calculation)

Default Calculation Method (Accrual Simple Balance Multiplier)

Changeable on Accrual Plan Page

Length of accrual term

One year.

One year.

Yes

Accrual term start date

January 1.

Accrual calculations restart at the beginning of each calendar year.

June 1.

Accrual calculations restart at the beginning of each June.

Yes

Accrual frequency

On the basis of the enrolled employee's pay periods.

For example, employees on a monthly payroll accrue time on the last day of their pay period, independently of payroll runs.

Monthly.

Yes, if you select Simple as the accrual frequency type.

Accrual amount

2 days per pay period.

2 days per month.

Yes

Ceiling

20 days.

20 days.

Yes

Maximum carryover

20 days.

20 days.

Yes

Length of service calculation (for accrual bands and ineligibility period)

Uses continuous service date (if present) or hire date.

Uses continuous service date (if present) or hire date.

No, but you can enter the continuous service date in the accrual plan element using the Manage Element Entries page.

Accrual start date for new hires

Based on start rules that you can select when you create the accrual plan.

Based on start rules that you can select when you create the accrual plan.

Yes

Period of ineligibility

Based on the period that you can define in the Accrual Plan page. Accrued time is not credited to the employee until the end of the ineligibility period.

Based on the period that you can define in the Accrual Plan page. Accrued time is not credited to the employee until the end of the ineligibility period.

Yes

Effective dates of carried-over time

Sets the effective start date to December 31 of the accrual term year that was processed by the Calculate Carry-Over process. Carried-over time expires a year later.

For example, the carried-over time with the effective start date December 31, 2010 expires on December 31, 2011.

Sets the effective start date to May 31 of the accrual term year that was processed by the Calculate Carry-Over process. Carried-over time expires a year later.

For example, the carried-over time with the effective start date May 31, 2010 expires on May 31, 2011.

Yes. The Calculate Carry-Over process calculates the effective dates according to the carryover expiry duration that you specify.

Calculation of gross accruals

Sums accruals in all full pay periods that end on or before the calculation date in the current accrual term.

The formula also considers in its calculation any employee termination date, and changes in assignment status.

Sums accruals in all full months that end on or before the calculation date in the current accrual term.

The formula also considers in its calculation the employee termination date (if present).

No

Calculation of gross accruals for suspended assignments

Calculates the number of active working days of the assignment in the payroll period, multiplies the normal accrual rate by the number of active days, and divides it by the number of total working days, to prorate the accrual.

Does not process changes in assignment status

No

Custom Accrual Formulas: Points to Consider

If you decide to customize the predefined accrual plan formulas, you can incorporate your own plan rules, using functions and database items to access extra inputs. However, there are constraints on what you can change in the formulas.

Using Predefined Formulas

You must take a copy of any predefined formula that you want to use and associate the copy with your accrual plan. This approach enables you to refer to the predefined formula if the changes you make to your own formula do not work to your expectations.

Using Extra Inputs

Use the predefined accrual formula functions and database items to access extra inputs for calculations in your custom accrual plan formulas. You can define and register any additional functions you require to incorporate your plan rules. However, your formula must use the same input statements and return statements that exist in the predefined formula.

Incorporating Accrual Calculations

The predefined accrual formulas contain calculations to ensure that the employee is entitled to accrue time. Although you can include your own calculations in the custom formula, your formula must include the following calculations:


Calculation

Description

Termination date

Check whether there is a termination date for the assignment. If the termination date is before the calculation date, calculate the accrual as of the termination date. If your formula does not handle partial accrual periods, check whether the termination date is before the end of the first accrual period; if yes, set gross accrual to zero.

Enrollment end date

Check whether an end date exists for the assignment's enrollment in the plan. If the end date is before the calculation date, calculate the accrual as of the end date. If your formula does not handle partial accrual periods, check whether the enrollment end date is before the end of the first accrual period; if yes, set gross accrual to zero.

Calculation date

Check whether the calculation date is before the end of the first accrual period; if yes, set gross accrual to zero (unless your formula handles partial accrual periods).

Hire date

Check the employee's hire date or continuous service date. If your formula handles partial accrual periods, check that this date is before the calculation date, and if not, set the gross accrual to zero. If your formula does not handle partial periods, check that this date is before the start of the last full accrual period used in the current calculation. If the employee has not worked for a full accrual period before the calculation date, set the gross accrual to zero.

Start date for newly enrolled employees

Check when the employee must start to accrue time. This is typically the date of enrollment in the plan or, if your formula does not handle partial accrual periods, the first period starting on or after the date of enrollment in the plan. If this date (or period) is after the calculation date (or period), set the gross accrual to zero.

Ineligibility period

Check if an ineligibility period exists. Set the gross accrual to zero if the ineligibility period is still in force on either of these dates:

  • Calculation date

  • End date of the last accrual period used in the calculation (if your formula does not handle partial accrual periods)

Inactive assignments

Check whether the employee's assignment is active throughout the period for which you want to calculate accruals. Depending on your plan rules, your employees might not accrue time when their assignments are inactive, or they might accrue time at a reduced rate during this period. You can use the GET_ASG_INACTIVE_DAYS function to check the assignment status on each day from the start date to the end date of the period and return the number of inactive working days.

Turning Accrual Plan Rules Into Formula Statements: Examples

Although you can incorporate accrual plan rules from the Manage Accrual Plans page, you can incorporate more complex rules in the accrual formula directly. Use these examples to incorporate your plan rules into the Accrual Simple Multiplier formula.

Caution

Before modifying a predefined formula, you must copy it first and then modify the copy.

Defining Additional Start Rules for New Hires

If you want to use an accrual start rule other than the predefined ones, you must first define the new rule as a value for the PER_ACCRUAL_START_RULE lookup type. Then modify the formula section that determines the accrual start rule on the basis of the following sample statements:

     .
     .
     .
ELSE IF (ACP_START_RULE = <your new lookup value>) THEN
        (
     First_Eligible_To_Accrue_Date  = <your new calculation to determine the start date>
     )

 

Defining a Different Rule to Determine Accrual Rate

To determine the accrual rate, the predefined formula by default considers the amount of time that an employee must accrue in the accrual term and the number of accrual periods, both of which you can define on the Accrual Plan page. You can override the default calculation. In the following example, part-time employees accrue 3 hours for every 75 hours worked:

Accrual_Rate = ACP_HOURS_WORKED / 75

 

In the above statement, ACP_HOURS_WORKED is a database item that you must add to the accrual plan element input value that contains the number of hours worked by the enrolled employee.

Defining Accrual Amounts in Advance

The accrual amount that you define on the Accrual Plan page enables enrolled employees to accrue time each period. If, for example, you want employees to accrue their full entitlement of 20 days at the start of every calendar year, use the following basic formula:

INPUTS ARE
Calculation_Date (date)

Accrued_amt = 20

Effective_start_date = to_date('0101'||to_char(calculation_date, 'YYYY'),'DDMMYYYY')
Effective_end_date = to_date('3112'||to_char(calculation_date, 'YYYY'),'DDMMYYYY')
Accrual_end_date = to_date('0101'||to_char(calculation_date, 'YYYY'),'DDMMYYYY')

RETURN Accrued_amt,
       Effective_start_date,
       Effective_end_date,
       Accrual_end_date

 

Note

The above formula does not contain ineligibility rules or start rules, and does not calculate the accrual for part years (for example, for employees joining the plan midway through a year).

Using a Different Database Item for Continuous Service Date

The predefined formula uses the continuous service date (if it was entered for the enrolled employee) to determine when a newly hired employee begins to accrue time. The formula uses the ACP_CONTINUOUS_SERVICE_DATE database item for this purpose. If you are using a different database item for the continuous service date, then replace the database item used in the following formula lines with the new database item:

IF (ACP_CONTINUOUS_SERVICE_DATE WAS DEFAULTED) THEN
(
	Continuous_Service_Date = ACP_HIRE_DATE
)
ELSE IF(ACP_CONTINUOUS_SERVICE_DATE > Calculation_Period_SD) THEN
(
	Total_Accrued_Amt = 0
	Continuous_Service_Date = ACP_CONTINUOUS_SERVICE_DATE
)
ELSE
(
	Continuous_Service_Date = ACP_CONTINUOUS_SERVICE_DATE
)
.
.
.
IF Continuous_Service_date = ACP_CONTINUOUS_SERVICE_DATE THEN
(
	Actual_Accrual_Start_Date = Continuous_service_Date
)

 

Adding Rules for Suspended Assignments

If you want employees to accrue no time (or accrue time differently) while on certain types of leave, such as maternity leave or study leave, use the GET_ASG_INACTIVE_DAYS function to check the status of the assignment, and include the appropriate rules. In the following example, employees do not accrue time while their assignment is inactive:

Assignment_Inactive_Days = GET_ASG_INACTIVE_DAYS(Period_SD, Period_ED)
IF Assignment_Inactive_Days <> 0 THEN
	(
		Working Days = GET_WORKING_DAYS(period_SD, Period_ED)
		IF Working_Days = Assignment_Inactive_Days THEN
			(
				Multiplier = 0
			)
		ELSE
			(
				Multiplier = 1 - (Assignment_Inactive_Days / Working Days)
			)
	)

 

Accrual Formula Type

If you plan to write your own accrual formula for use with accrual plans, you must ensure that the formula uses the same input and return statements that exist in the predefined formula.

You can use functions and database items to access additional inputs. Contexts are available to the formulas automatically.

Contexts

Without using inputs or database items, you can directly access the following values for use with functions in formulas of type Accrual:

Input Variables

The following table lists the input variables that you must use in your accrual formula.


Input Variable

Description

Calculation_Date

Date through which you want to calculate the gross accrual

Accrual_Start_Date (only if you are using a payroll balance)

Date when accrual calculations must start. If this value is null, accrual calculations start from the beginning of the accrual term.

Accrual_Latest_Balance (only if you are using a payroll balance)

Latest accrual balance for the accrual term up to the day before the start of the accrual calculations (Accrual_Start_Date). A payroll balance stores the latest balance.

Return Values

The following table lists the return values that you must use in your accrual formula.


Return Value

Description

Enrollment_Start_Date

Date when the employee has enrolled in the accrual plan

Enrollment_End_Date

Date when the accrual plan enrollment expires

Continuous_Service_Date

The service date that was entered using an input value on the accrual plan element

Accrual_Start_Date

Start date of the accrual term. Depending on plan rules, this date may be the plan enrollment date, hire date, adjusted service date, or other date.

Accrual_End_Date

Date through which you want to calculate the gross accrued time. However, if the employee was terminated, this return value contains the termination date. If the employee has left the plan, this value contains the end date of the plan's element entry.

Accrual_Rate

Amount of time that the employee accrues per year. The value that is returned depends on accrual bands.

Ceiling_Amt

The maximum time that the employee can accrue. The value that is returned depends on accrual bands.

Max_Carryover

Maximum time that the employee can carry over to the next accrual term. The value that is returned depends on accrual bands.

Accrued_Amt

Gross accrued time for the current accrual term

Entitled_Amt

Net accrual for the current accrual term

Pay_Rate_Per_Unit

The monetary value of one unit of time that the employee has accrued

Currency_Code

The currency code used to express the monetary value of one unit of accrued time.

Accrual Carryover Formula Type

If you plan to write your own carryover formula for use with accrual plans, you must ensure that the formula uses the same input and return statements that exist in the predefined formula.

You can use functions and database items to access additional inputs. Contexts are available to the formulas automatically.

Input Variables

The following table lists the input variables that you must use in your carryover formula.


Input Variable

Description

calculation_date

Any date within an accrual term

accrual_term

Method to calculate the time to carry over. Specify PREVIOUS as the value for this input if you want the formula to calculate carryover for the previous accrual term (before the calculation date). Specify CURRENT if you want the formula to calculate carryover for the accrual term that the calculation date spans.

Return Values

The following table lists the return values that you must use in your carryover formula.


Return Value

Description

max_carryover

Amount of time the employee can carry over to the next accrual term.

effective_date

Last day of the accrual term for which the carryover is calculated

expiry_date (optional)

Date when the carryover expires if employees do not use it

Accrual Ineligibility Formula Type

If you plan to write your own ineligibility formula for use with accrual plans, you must ensure that the formulas use the same input and return statements that exist in the predefined formula

Input Variables

You must specify as input the calculation date that indicates the effective date of the enrolled employee's accrual plan element entry.

Return Values

You must specify the assignment_eligible return value. The value will be set to Y (eligible) or N (ineligible).

Accrual Plan Formula Functions

You can use the predefined accrual formula functions for calculations in your custom accrual plan formulas.

You can create and register any additional functions you require to incorporate your plan rules.

CALCULATE_PAYROLL_PERIODS ( )

The function uses the payroll id context. It calculates the number of payroll periods in one year for that payroll, and sets the global variable PAYROLL_YEAR_NUMBER_OF_PERIODS to that value.

For example, the function sets the global variable to 12 for a calendar month payroll.

GET_ABSENCE (calculation date, start date)

Returns the total number of whole absences (having start and end dates) that an accrual plan covers between a date interval.

For example, the following formula statement returns the total number of absences that were taken between January 1, 2010 and 31 December, 2010.

TOTAL_ABSENCE = GET_ABSENCE ('01-JAN-2010(date), '31-DEC-2010'(date))

 

Parameters

Start Date

Date when you want to start searching for absences.

End Date

Date up to which you want to search for absences.

GET_CARRY_OVER (calculation date, start date)

Returns the number of days or hours recorded on the carryover element entry with an effective date on or between the two input dates. If more than one element entry is effective between these dates, the function sums the hours or days.

Carryover element entries may also have an expiry date, after which any unused carryover time is lost. If the calculation date is after the expiry date, the function calculates the absences between the start date and the calculation date. If the total absence duration equals or exceeds the carryover, the function returns the total carryover because all of the time was used before it expired. If the total duration is less than the total carryover, the function returns the total duration of the absences taken before the carried over time expired. The rest of the carried over time is lost.

For example, if an employee carried over ten days, and takes six days of leave up to the expiry date, the function returns six. The employee loses the four days of carried over time remaining after the expiry date.

GET_NET_ACCRUAL (calculation date, plan id, accrual start date, accrual latest balance)

Returns the net accrual at the calculation date.

Parameters

Calculation Date

Date through which you want to calculate the net accrual.

Plan ID

Identifies the accrual plan to use to calculate the net accrual.

Accrual Start Date

Start date of the accrual term.

Accrual Latest Balance

Latest gross accrual stored in a payroll balance.

RESET_ACCRUALS ( )

Returns a value (Y or N) that indicates whether the PTO_RESET_ACCRUALS pay action parameter was set.

GET_OTHER_NET_CONTRIBUTION (calculation date, start date)

Returns between two dates the total amount of time stored in elements that you added to the net calculation on the Accrual Plan page. This calculation does not consider absence entries stored in the absence element or carried-over time stored in the carryover element.

GET_PAYROLL_PERIOD (date)

Determines the payroll period that spans the specified date and assigns values to the following global variables:

Use the GET_DATE and GET_NUMBER functions to retrieve the values from the global variables.

GET_ACCRUAL_BAND (years_of_service)

Determines the appropriate accrual band for the specified length of service of the employee. The function assigns values to the following global variables:

The following statements show how to use this function then use the GET_NUMBER function to retrieve the values it sets in the global variables:

   .
   .
   .
   .
IF ( GET_ACCRUAL_BAND(Years_Service) = 0 THEN

Annual_Rate = GET_NUMBER('ANNUAL_RATE')
Upper_Limit = GET_NUMBER('UPPER_LIMIT')
Ceiling = GET_NUMBER('CEILING')  

ELSE  

( <your statements to include error processing> )

)

 

GET_ASSIGNMENT_STATUS (date)

Determines the status of an assignment at a given date. The function assigns values to the following global variables:

For example, the following statement returns the status of the assignment on 01 January, 2011:

ERROR = GET_ASSIGNMENT_STATUS ('01-JAN-2011' (date))

GET_ASG_INACTIVE_DAYS (period start date, period end date)

Returns the number of working days between the input dates when the assignment status was inactive.

GET_EARLIEST_ASGCHANGE_DATE (p_legislative_data_group_id, p_pay_assignment_id, p_event_group, p_start_date, p_end_date, p_recalc_date)

Returns the earliest date when changes to the assignment were first detected between two dates.

Parameters

p_legislative_data_group_id

The legislative data group ID of the assignment.

p_pay_assignment_id

The assignment's pay assignment ID

p_event_group

The name of the event group that contains the events you want to track

p_start_date

Date when you want to start scanning for retrospective changes to the assignment

p_end_date

Date through which you want to scan for retrospective changes to the assignment

p_recalc_date

Date, normally today's date or the effective date you selected, on which you want to scan for retrospective events that may have occurred during the input dates.

GET_PERIOD_DATES (base date, UOM, calculation date, number)

Returns the start and end dates of the accrual period that spans the input date. The function assigns the start date to the PERIOD_START_DATE global variable, and the end date, to the PERIOD_END_DATE global variable.

For example, assume that an accrual plan was set up to allow employees to accrue time once in two months in a calendar year. The following usage of the GET_PERIOD_DATES function populates the PERIOD_START_DATE global variable with 1-MAR-2010, and the PERIOD_END_DATE global variable, with 30-APR-2010.

GET_PERIOD_DATES('1-JAN-2010', 'M', '15-APR-2010', 2)

If the calculation date is earlier than the base date, then the function calculates the accrual periods backwards from the base date.

Parameters

Base Date

Date to start calculating the accrual period dates from. Usually, this is the start date of the accrual term.

Calculation Date

Date for which you want to determine the accrual period that spans it.

UOM

Type of division that determines accrual periods. Valid units are D (days), M (months), W (weeks).

Number

The duration of the accrual period.

GET_START_DATE (accrual start date, start of accrual term)

Returns the date when the accrual formula must start calculating accruals.

If you are using a payroll balance and there are retrospective absence entries that have not already been used in an accrual calculation, the function returns the earliest start date of these entries.

However, in the predefined formula, if any unprocessed retrospective element entries are found, the formula always calculates accruals from the beginning of the accrual term.

If you are not using a payroll balance, the function returns the start date of the accrual term.

GET_WORKING_DAYS (start date, end date)

Returns the number of working days between the input dates

CALCULATE_HOURS_WORKED (p_std_hours, p_std_freq, p_range_start, p_range_end)

Returns the number of working hours between two dates.

For example, assuming that the assignment works 40 hours per week, the following statement returns the number of working hours between 01 January, 2010 and 31 January, 2010:

E = CALCULATE_HOURS_WORKED (40, "Weekly", '01-Jan-2010', '31-Jan-2010')

The values that you can specify for the p_std_freq parameter are:

PUT_MESSAGE (expression)

Adds a message for debugging purposes.

For example, use this function to generate a message from the accrual formula if an employee is not eligible to accrue time.

GET_ELEMENT_ENTRY ( )

Returns a value that indicates whether an assignment has enrolled in the accrual plan. the value 1 indicates that an element entry exists, and the value 0 indicates that no element entry exists.

GET_PAYROLL_DETAILS (p_payroll_id, p_date_in_period)

Returns payroll details, such as the start date of the payroll period, the end date, and the number of the payroll period that spans the input date. The function accepts the following parameters:

Parameters

p_payroll_id

The ID of the payroll

p_date_in_period

The date for which you want to determine the payroll details

GET_PAYROLL_DTRANGE (p_payroll_id)

Returns the start and end dates of the specified payroll.

GET_PAYROLL_ID (p_pay_assignment_id, p_payroll_id, p_date_in_period)

Returns the effective payroll ID for an assignment on an input date.

GET_RETRO_ELEMENT ( )

Retrieves retrospective elements to be tagged as processed.

GET_TERMINATION_DATE (p_assignment_id)

Returns the termination date of an assignment and sets the PER_TERMINATION_DATE context with this value.

SET_ACCRUAL_BALANCE (p_pay_assignment_id, p_element_type, p_element_type_id, p_input_value_id, p_accrual_band_id, p_absence_type_id, p_absence_attendance_id, p_start_date, p_end_date, p_contributed_amt)

Sets the accrual balance details. The function accepts the following parameters:

Parameters

p_pay_assignment_id

The assignment for which you want to set the accrual balance details

p_element_type

The type of element for which you want to set the details. Valid types are Accrual, Carryover, Absence, or other types of elements you may have added to the net calculation rules when you created the accrual plan.

p_element_type_id

The ID of the element for which you want to set the balance details

p_input_value_id

The ID of the input value of the element for which you want to set the balance details

p_accrual_band_id

The ID of the accrual band that applies to the assignment

p_absence_type_id

The name of the absence type. This parameter is applicable when you want to set details for the absence element type.

p_absence_attendance_id

The ID of the absence record. This parameter is applicable when you want to set details for the absence element type.

p_start_date

If you are using this function to set details for the Absence element type, then set the value of this parameter to the start date of the absence. For setting details for the Carryover element type, specify the effective date of the carried over time.

p_end_date

If you are using this function to set details for the Absence element type, then set the value of this parameter to the end date of the absence. For setting details for the Carryover element type, specify the date when the carried over time expires.

p_contributed_amt

If you are using this function to set details for the additional elements you added in the net calculation rules, set the value of this parameter to the total amount of time recorded in those elements.

Formulas for Absence Benefit Plans

Participation and Rate Eligibility Formula Type for Absence Entitlement Plans

The Evaluate Absence Plan Participation process runs the Participation and Rate Eligibility formula to enroll eligible employees in an absence benefit plan. The formula output indicates whether an absence entitlement plan exists for the type of absence recorded.

You must associate the formula with an eligibility profile of an absence benefit plan. The formula must belong to the Participation and Rate Eligibility formula type. Provide a meaningful name for the formula so that you can easily identify it.

Contexts

The following contexts are available to this formula.

Database Items

The following database items are available to formulas of this type.

Return Values

The following return value is available to this formula.


Return Value

Data Type

Required

Description

ELIGIBLE

Char

Yes

The value of this variable is Y if an entitlement plan exists for the type of absence recorded. The value is N if no entitlement plan exists.

Errors

If any other return value is passed back to the formula, then you can process errors with BEN_91329_FORMULA_RETURN.

Sample Formula

You can either write your own formula, or use the following text to create the formula on the Fast Formula page:




DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID  IS -987123654
DEFAULT FOR BEN_PLN_PL_ID IS -987123654

l_yn = 'N'
l_error = 0
l_absence_type_lookup_code = ' '
l_absence_type_list_name = ' '
l_truncated_yes_no = ' '
l_error_message = ' '
l_absence_type_meaning = ' '
l_absence_category = ' '



l_pl_id = BEN_PLN_PL_ID
l_absence_type_id = BEN_ABS_ABSENCE_TYPE_ID
l_abs_typ_id = to_char(l_absence_type_id)


l_absence_type_meaning = BEN_CHK_ABS_TYPE (l_abs_typ_id,l_yn)

IF NOT ISNULL(l_absence_type_meaning) = 'Y' THEN
(
  l_yn = 'Y'
)

ELIGIBLE = l_yn
RETURN ELIGIBLE

 

Extra Inputs Formula Type for Absence Benefit Plans

When an employee records a long term absence, the Evaluate Absence Plan Participation process enrolls the employee in an absence benefit plan and runs the Extra Inputs formula to update the payroll element with details, such as the absence type, absence start and end dates, and the ID of the absence benefit plan that the employee was enrolled in.

You must associate the formula with a benefit rate for an absence benefit plan. The formula must belong to the Extra Input formula type. Provide a meaningful name for the formula so that you can easily identify it.

Contexts

The following contexts are available to this formula:

Database Items

The following database items are available to formulas of this type:

Input Variables

The following input values are available to this formula.


Input Value

Data Type

Required

Description

BEN_ABS_IV_ABSENCE_ATTENDANCE_ID

Char

Yes

Absence record ID

BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID

Char

Yes

Absence type ID

BEN_ABS_IV_DATE_START

Char

Yes

Absence start date

BEN_ABS_IV_DATE_END

Char

Yes

Absence end date

BEN_ABS_IV_ABSENCE_DAYS

Char

Yes

Absence duration

Return Values

The following return values are available to this formula.


Return Value

Data Type

Required

Description

l_absence_id

Char

Yes

Absence record ID

l_plan_id

Char

Yes

Absence benefit plan ID that the employee enrolled in

l_absence_start_date

Char

Yes

Absence start date

l_absence_end_date

Char

Yes

Absence end date

l_absence_type

Char

Yes

Type of absence recorded

Errors

If type casting of variables causes errors, then you can process those errors with BEN_92311_FORMULA_VAL_PARAM.

Sample Formula

You can either write your own formula, or use the following text to create the formula in the Fast Formula page:

/*
Set default values for database items.
*/
DEFAULT FOR BEN_ABS_ABSENCE_TYPE IS '_DEFAULT_'
DEFAULT FOR BEN_PLN_PL_ID IS -987123654

/* Other database items.
DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID IS -987123654
DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_CODE IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_ID IS -987123654
DEFAULT FOR BEN_ABS_REASON IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_REASON_CODE IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_REASON_ID IS -987123654
DEFAULT FOR BEN_ABS_DATE_START IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_ABS_DATE_END IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_ABS_SICKNESS_START_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_ABS_SICKNESS_END_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_ABS_DATE_NOTIFIED IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SMP_DUE_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SMP_MPP_START_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SMP_ACTUAL_BIRTH_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SMP_LIVE_BIRTH_FLAG IS 'Y'
DEFAULT FOR BEN_SSP_EVIDENCE_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SSP_EVIDENCE_SOURCE IS '_DEFAULT_'
DEFAULT FOR BEN_SSP_MEDICAL_TYPE IS 'SICKNESS'
DEFAULT FOR BEN_SSP_EVIDENCE_STATUS IS 'ACTIVE'
DEFAULT FOR BEN_SSP_SELF_CERTIFICATE IS 'N'
DEFAULT FOR BEN_ABS_ACCEPT_LATE_NOTIFICATION_FLAG IS 'Y'
DEFAULT FOR BEN_ABS_PREGNANCY_RELATED_ILLNESS IS 'N'
DEFAULT FOR BEN_SMP_NOTIFICATION_OF_BIRTH_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SSP_EVIDENCE_RECEIVED_DATE IS '1951/01/01 00:00:00'(DATE)
DEFAULT FOR BEN_SSP_ACCEPT_LATE_EVIDENCE_FLAG IS 'Y'
*/

/*
Set default values for formula inputs.
*/
DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_ID IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_DATE_START IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_DATE_END IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABSENCE_DAYS IS '_DEFAULT_'

/* Other available inputs.
DEFAULT FOR BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABSENCE_HOURS IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_DATE_NOTIFICATION IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_END IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_START IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_SSP1_ISSUED IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_LINKED_ABSENCE_ID IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_SICKNESS_START_DATE IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_SICKNESS_END_DATE IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_MATERNITY_ID IS '_DEFAULT_'
DEFAULT FOR BEN_PIL_IV_PER_IN_LER_ID IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE_CATEGORY IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE1 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE2 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE3 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE4 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE5 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE6 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ATTRIBUTE7 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION_CATEGORY IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION1 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION2 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION3 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION4 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION5 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION6 IS '_DEFAULT_'
DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION7 IS '_DEFAULT_'
*/

/*
Declare input values.

Use the following naming convention for the inputs:
BEN_ABS_IV_
*/

INPUTS ARE BEN_ABS_IV_ABSENCE_ATTENDANCE_ID(TEXT)
,BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
,BEN_ABS_IV_DATE_START(TEXT)
,BEN_ABS_IV_DATE_END(TEXT)
,BEN_ABS_IV_ABSENCE_DAYS(TEXT)

/* Other available inputs
,BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID(TEXT)
,BEN_ABS_IV_ABSENCE_HOURS(TEXT)
,BEN_ABS_IV_DATE_NOTIFICATION(TEXT)
,BEN_ABS_IV_DATE_PROJECTED_END(TEXT)
,BEN_ABS_IV_DATE_PROJECTED_START(TEXT)
,BEN_ABS_IV_SSP1_ISSUED(TEXT)
,BEN_ABS_IV_LINKED_ABSENCE_ID(TEXT)
,BEN_ABS_IV_SICKNESS_START_DATE(TEXT)
,BEN_ABS_IV_SICKNESS_END_DATE(TEXT)
,BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS(TEXT)
,BEN_ABS_IV_MATERNITY_ID(TEXT)
,BEN_PIL_IV_PER_IN_LER_ID(TEXT)
,BEN_ABS_IV_ATTRIBUTE_CATEGORY(TEXT)
,BEN_ABS_IV_ATTRIBUTE1(TEXT)
,BEN_ABS_IV_ATTRIBUTE2(TEXT)
,BEN_ABS_IV_ATTRIBUTE3(TEXT)
,BEN_ABS_IV_ATTRIBUTE4(TEXT)
,BEN_ABS_IV_ATTRIBUTE5(TEXT)
,BEN_ABS_IV_ATTRIBUTE6(TEXT)
,BEN_ABS_IV_ATTRIBUTE7(TEXT)
,BEN_ABS_IV_ABS_INFORMATION_CATEGORY(TEXT)
,BEN_ABS_IV_ABS_INFORMATION1(TEXT)
,BEN_ABS_IV_ABS_INFORMATION2(TEXT)
,BEN_ABS_IV_ABS_INFORMATION3(TEXT)
,BEN_ABS_IV_ABS_INFORMATION4(TEXT)
,BEN_ABS_IV_ABS_INFORMATION5(TEXT)
,BEN_ABS_IV_ABS_INFORMATION6(TEXT)
,BEN_ABS_IV_ABS_INFORMATION7(TEXT)
*/

/*
Initialise standard default values.
*/
l_null                         = RPAD('X',0,'Y')
l_default                      = '_DEFAULT_'
l_default_date                 = '1951/01/01 00:00:00'(date)
l_default_canonical_date       = '1951/01/01 00:00:00'
l_default_number               = -987123654
l_default_canonical_number     = '-987123654'

l_absence_id_iv = BEN_ABS_IV_ABSENCE_ATTENDANCE_ID

/* 1. Check that a default value was not used for the absence attendance ID.

If an absence attendance id was not found, the default value is used.
This may occur if this formula is used in a plan that does not have an absence 
"context" available. Ensure that you select Absence as the option type of the 
associated plan type. Ensure that you select Absence as the type of the associated 
life event reasons.
*/
IF NOT l_absence_id_iv = l_default THEN
(

l_absence_id = TO_NUMBER(l_absence_id_iv)
l_plan_id = BEN_PLN_PL_ID
l_absence_start_date_dt = BEN_ABS_IV_DATE_START
l_absence_type = BEN_ABS_ABSENCE_TYPE
l_absence_end_date = BEN_ABS_IV_DATE_END
)
ELSE
(
l_absence_id                   = l_default_number
l_plan_id                      = l_default_number
l_absence_start_date           = l_null
l_absence_end_date             = l_null
l_absence_type                 = l_null
)

RETURN l_absence_id
,l_plan_id
,l_absence_start_date
,l_absence_end_date
,l_absence_type


 

Rate Value Calculation Formula Type for Absence Benefit Plans

When an employee records a long term absence, the Evaluate Absence Plan Participation process enrolls the employee in an absence benefit plan and runs the Rate Value Calculation formula to determine the enrolled employee's length of service. The process uses the length of service to determine the entitlement bands that apply during the absence period.

You must create the formula for the standard benefit rate that you want to associate with the absence benefit plan. The formula must belong to the Rate Value Calculation formula type. Provide a meaningful name for the formula so that you can easily identify it.

Contexts

The following contexts are available to this formula:

Database Items

The database items available to this formula are based on the employee's assignment ID.

Return Values

The following return value is available to this formula.


Return Value

Data Type

Required

Description

LENGTH_OF_SERVICE

Number

Yes

A number that indicates the length of service of the enrolled employee.

Sample Formula

You can either write your own formula, or use the following text to create the formula in the Fast Formula page:

/*
Set default values.
*/

DEFAULT FOR BEN_ABS_DATE_START IS '1951/01/01 00:00:00'(date)
DEFAULT FOR PER_ASG_REL_DATE_START IS '1951/01/01 00:00:00'(date)

/*
Initialize standard default values.
*/
l_null = RPAD('X',0,'Y')
l_default = '_DEFAULT_'
l_default_date = '1951/01/01 00:00:00'(date)
l_default_canonical_date = '1951/01/01 00:00:00'
l_default_number = -987123654
l_default_canonical_number = '-987123654'
l_length_of_service = -987123654

/* 
Determine the absence start date and the employee hire date.
*/
l_absence_start_date = BEN_ABS_DATE_START
l_employee_hire_date = PER_ASG_REL_DATE_START

/*
Check that an absence start date is available for processing.

If an absence start date was not found, the default value is used.
This may occur if this formula is used in a plan that does not have 
an absence "context" available. Ensure that you select Absences as 
the option type of the associated plan type. Ensure that you select 
Absence as the type of the associated life event reasons.
*/
IF NOT l_absence_start_date = l_default_date THEN
(
/* 
Check that an absence start date is available to process.

If an employee hire date was not found, the default value is used.
This may occur if the person was not an eligible person type.
Check the associated eligibility profile to ensure that only persons 
belonging to the Employee person type are selected for plan enrollment.
*/
IF NOT l_employee_hire_date = l_default_date THEN
(
  /*
  Calculate the length of service. 
  */

  l_length_of_service = FLOOR( MONTHS_BETWEEN (l_absence_start_date, l_employee_hire_date)))
)

LENGTH_OF_SERVICE = l_length_of_service
RETURN LENGTH_OF_SERVICE

 

Formulas for Individual Compensation Plans

Compensation Start Date Formula Type

The Compensation Start Date formula can be used to determine the start date of compensation awarded using an individual compensation plan.

When setting up a compensation plan, you can select the defined formula after you select Formula as the Compensation Start Date value.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type:


Input

Data Type

Required

Description

CMP_IV_EFFECTIVE_DATE

Varchar2

N

Input effective date

CMP_IV_ASSIGNMENT_ID

Number

N

Assignment ID

CMP_IV_PERSON_ID

Number

N

Person ID

CMP_IV_PLAN_ID

Number

N

Plan ID

CMP_IV_OPTION_ID

Number

N

Option ID

Return Values

The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

COMPENSATION_START_DATE

Varchar2

N

Compensation Start Date

Errors

CMP_VC_ALLOC_FORMULA_FAILURE - Error occurred during formula processing.

Sample Formula

This sample formula calculates the compensation start date to be 3 months later from the given date.

/*********************************************************
FORMULA NAME : Compensation Start Date Formula 
FORMULA TYPE : Compensation Start Date 
DESCRIPTION  : Formula that returns Compensation Start Date to be 3 month later of the given date
*******************************************************/ 
/*=========== DATABASE ITEM DEFAULTS BEGIN ==============*/ 
N/A 
/*=========== DATABASE ITEM DEFAULTS ENDS ===============*/ 
/*=========== FORMULA SECTION BEGIN =====================*/

INPUTS ARE CMP_IV_EFFECTIVE_DATE (text)
DEFAULT FOR CMP_IV_EFFECTIVE_DATE IS '4012/01/01'

l_date = ADD_MONTHS(TO_DATE(CMP_IV_EFFECTIVE_DATE, 'YYYY/MM/DD'),3)
compensation_start_date = TO_CHAR(ldate, 'YYYY/MM/DD') 

RETURN compensation_start_date
/*=========== FORMULA SECTION END =======================*/

 

Compensation End Date Formula Type

The Compensation End Date formula can be used to determine the end date of compensation awarded using an individual compensation plan.

When setting up a compensation plan, you can select the defined formula after you select Formula as the Compensation End Date value.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type:


Input

Data Type

Required

Description

CMP_IV_EFFECTIVE_DATE

Varchar2

N

Input effective date

CMP_IV_ASSIGNMENT_ID

Number

N

Assignment ID

CMP_IV_PERSON_ID

Number

N

Person ID

CMP_IV_PLAN_ID

Number

N

Plan ID

CMP_IV_OPTION_ID

Number

N

Option ID

Return Values

The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

COMPENSATION_END_DATE

Varchar2

N

Compensation End Date

Errors

CMP_VC_ALLOC_FORMULA_FAILURE - Error occurred during formula processing.

Sample Formula

This sample formula calculates the compensation end date to be 6 months later of the given date.

/*********************************************************
FORMULA NAME : Compensation End Date Formula 
FORMULA TYPE : Compensation End Date 
DESCRIPTION  : Formula that returns Compensation End Date
*******************************************************/ 
/*=========== DATABASE ITEM DEFAULTS BEGIN ==============*/ 
N/A 
/*=========== DATABASE ITEM DEFAULTS ENDS ===============*/ 
/*=========== FORMULA SECTION BEGIN =====================*/

INPUTS ARE CMP_IV_EFFECTIVE_DATE (text)
DEFAULT FOR CMP_IV_EFFECTIVE_DATE IS '4012/01/01'

l_date = ADD_MONTHS(TO_DATE(CMP_IV_EFFECTIVE_DATE, 'YYYY/MM/DD'),6)
compensation_end_date = TO_CHAR(ldate, 'YYYY/MM/DD')

RETURN compensation_end_date
/*=========== FORMULA SECTION END =======================*/

 

Formulas for Workforce Compensation Plans

Compensation Currency Selection Formula Type

The Compensation Currency Selection formula determines the currency associated with a workforce compensation component.

You select the formula on the Configure Compensation Components page.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type.


Input

Data Type

Required

Description

CMP_IV_PLAN_ID

Number

Y

Plan ID

CMP_IV_ASSIGNMENT_ID

Number

Y

Assignment ID

CMP_IV_PERIOD_ID

Number

Y

Period ID

CMP_IV_COMPONENT_ID

Number

Y

Component ID

CMP_IV_PLAN_START_DATE

Date

Y

Plan Start Date

CMP_IV_PLAN_END_DATE

Date

Y

Plan End Date

Return Values

Use predefined names for return variables. The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

L_CURR_CODE

Char

N

Currency code from the formula

Sample Formula

This sample formula determines if a person is selected for a plan based on their assignment_id.

/*******************************************************************
FORMULA NAME : Compensation Currency Selection Formula
FORMULA TYPE : Compensation Currency Selection 
DESCRIPTION  : It returns the currency code based on 
  component_id.
*******************************************************************/

 

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/ 
INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number), CMP_IV_PERIOD_ID (number), CMP_IV_COMPONENT_ID (number)
/*=========== INPUT VALUES DEFAULTS ENDS======================*/

 

/*================ FORMULA SECTION BEGIN =======================*/ 
                          
      DEFAULT FOR CMP_IV_COMPONENT_ID IS 0
      
      l_curr_code = 'XXX'
      
      IF (CMP_IV_COMPONENT_ID = 489) THEN
      (
       l_curr_code = 'USD'
      )
      ELSE IF (CMP_IV_COMPONENT_ID = 490) THEN
      (
       l_curr_code = 'GBP'
      )
      
      RETURN l_curr_code      

/*================ FORMULA SECTION END =======================*/

 

Compensation Default and Override Formula Type

The Compensation Default and Override formula determines the default values populated in a column for a workforce compensation plan.

You select the formula when you configure the worksheet display for a column using the Default Values tab on the Define Column Properties window.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type.


Input

Data Type

Required

Description

CMP_IV_PLAN_ID

Number

Y

Plan ID

CMP_IV_PERIOD_ID

Number

Y

Period ID

CMP_IV_COMPONENT_ID

Number

Y

Component ID

CMP_IV_ITEM_NAME

Char

Y

Item Name

CMP_IV_PERSON_ID

Number

Y

Person ID

CMP_IV_PLAN_START_DATE

Date

Y

Plan Start Date

CMP_IV_PLAN_END_DATE

Date

Y

Plan End Date

Return Values

Use predefined names for return variables. The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

L_DEFAULT_VALUE

Number/Char/Date

Y

Default value from the formula

L_DATA_TYPE

Char

Y

Data type of the column

Sample Formula

This sample formula determines if a person is selected for a plan based on their assignment_id.

/*******************************************************************
FORMULA NAME : Compensation Default and Override Formula
FORMULA TYPE : Compensation Default and Override 
DESCRIPTION  : Defaults the value of a column based on its 
  item_name.
*******************************************************************/ 

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/ 
INPUTS ARE CMP_IV_PLAN_ID (number), CMP_IV_PERIOD_ID (number), CMP_IV_COMPONENT_ID (number), CMP_IV_ITEM_NAME (text) 
/*=========== INPUT VALUES DEFAULTS ENDS======================*/ 

/*================ FORMULA SECTION BEGIN =======================*/ 
                          
      DEFAULT FOR CMP_IV_ITEM_NAME IS 'YYYYYYY'
      
      L_DEFAULT_VALUE = to_char(0) 
      L_DATA_TYPE = 'NONETYPE' 

      //Valid L_DATA_TYPE values can be -
//NUMBER, CHAR, DATE.
      
IF (CMP_IV_ITEM_NAME = 'AmountComp1') THEN
      (
       L_DATA_TYPE = 'NUMBER'
       L_DEFAULT_VALUE = to_char(3333)
      )
      ELSE IF (CMP_IV_ITEM_NAME = 'AmountComp2') THEN
      (
       L_DATA_TYPE = 'NUMBER'
       L_DEFAULT_VALUE = to_char(7777)
      )
      ELSE
      (
       L_DATA_TYPE = 'NONETYPE' 							
       L_DEFAULT_VALUE = to_char(-999)
      )
      
      RETURN L_DATA_TYPE, L_DEFAULT_VALUE

/*================ FORMULA SECTION END =======================*/

 

Compensation Hierarchy Determination Formula Type

The Compensation Hierarchy Determination formula determines the hierarchy for an associated workforce compensation plan.

You select the formula on the Configure Plan Details page.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type.


Input

Data Type

Required

Description

CMP_IV_ASSIGNMENT_ID

Number

Y

Assignment ID

CMP_IV_PLAN_ID

Number

Y

Plan ID

CMP_IV_PERIOD_ID

Number

Y

Period ID

CMP_IV_PLAN_START_DATE

Date

Y

Plan Start Date

CMP_IV_PLAN_END_DATE

Date

Y

Plan End Date

Return Values

Use predefined names for return variables. The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

L_PERSON_ID

Number

Y

Person ID of manager

L_ASSIGNMENT_ID

Number

Y

Assignment ID of manager

Sample Formula

This sample formula determines the manager of a person when the assignment_id is passed.

/*******************************************************************
FORMULA NAME : Compensation Hierarchy Determination Formula
FORMULA TYPE : Compensation Hierarchy Determination
DESCRIPTION  : Hierarchy determination fast formula which is based on assignment_id
*******************************************************************/ 

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/ 
INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number), CMP_IV_PERIOD_ID (number)
/*=========== INPUT VALUES DEFAULTS ENDS======================*/ 

/*================ FORMULA SECTION BEGIN =======================*/ 
                          
      DEFAULT FOR CMP_IV_ASSIGNMENT_ID IS 0
    
      L_PERSON_ID = '0'
      L_ASSIGNMENT_ID = '0'
      
      if ( CMP_IV_ASSIGNMENT_ID = 100000008154060 ) THEN
      (
       L_PERSON_ID = to_char(-999)    //-999 indicates top level 
//Manager.
       L_ASSIGNMENT_ID = to_char(-999)
      )
      ELSE
      (
       L_PERSON_ID = to_char(100000008153756)
       L_ASSIGNMENT_ID = to_char(100000008154060)
      )                      
      
      RETURN L_PERSON_ID , L_ASSIGNMENT_ID

/*================ FORMULA SECTION END =======================*/

 

Compensation Person Selection Formula Type

The Compensation Person Selection formula determines whether a person is selected for an associated workforce compensation plan.

You select the formula when you run the Start Workforce Compensation Cycle process.

Contexts

The following contexts are available to formulas of this type:

Database Items

Database items are not available to formulas of this type.

Input Variables

The following input variables are available to formulas of this type.


Input

Data Type

Required

Description

CMP_IV_ASSIGNMENT_ID

Number

Y

Assignment ID

Return Values

Use predefined names for return variables. The following return variables are available to formulas of this type.


Return Value

Data Type

Required

Description

L_SELECTED

Char

N

Y or N

Sample Formula

This sample formula determines if a person is selected for a plan based on their assignment_id.

/*******************************************************************
FORMULA NAME : Compensation Selection Formula
FORMULA TYPE : Compensation Person Selection 
DESCRIPTION  : Assignment_id based selection fast formula 
*******************************************************************/

 

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/ 
INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number)
/*=========== INPUT VALUES DEFAULTS ENDS======================*/

 

/*================ FORMULA SECTION BEGIN =======================*/ 
                          
      DEFAULT FOR CMP_IV_ASSIGNMENT_ID IS 0
      
      l_selected = 'Y'
      
      /* 100000008154095 - Ariel.Aimar@oracle.com - GBI data*/
      
      if (CMP_IV_ASSIGNMENT_ID = 100000008154095) THEN
      (
        l_selected = 'N'
      )
      else
      (
        l_selected = 'Y'
      )
      
      RETURN l_selected
/*================ FORMULA SECTION END =======================*/

 

FAQs for Manage Fast Formulas

When do I run the Compile Formula process?

If you need to compile many fast formulas at the same time, you can run the Compile Formula process on the Submit a Process or Report page. Also, if you make any changes to a function after you have compiled a formula that uses it, you need to recompile the formula for the changes to take effect.

What's the difference between a formula compilation error and an execution error?

Compilation errors occur in the Manage Fast Formulas page when you compile the formula. An error message explains the nature of the error. Common compilation errors are syntax errors resulting from typing mistakes.

Execution 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.

Manage Elements

Elements: How They Work in Salary, Absence, Benefits, and Payroll

Elements are building blocks that help determine the payment of base pay, benefits, absences, and other earnings and deductions. The components of elements are set up differently based on how the element is to be used.

Element build blocks for Base Pay Management,
Absence and Accruals, Benefits, and Payroll applications

Base Pay Management

To manage base pay, you attach a single earning element to each salary basis to hold base pay earnings, and assign a salary basis to each worker. When a manager or compensation specialist enters a base pay amount for a worker, the amount is written to the payroll element input value associated with the worker's salary basis and used in payroll processing to generate payment amounts.

Absence and Accruals

You can manage employee absences and leave time. To facilitate reporting and analysis of employee absences, you can distinguish between absence categories, absence types, and absence reasons. You can associate an absence type with an element to maintain an absence balance for an employee. You can associate absence types and other elements with accrual plans to determine the net accrual of an employee.

Benefits

Attach elements at various levels in the benefits object hierarchy to create deductions and earnings that can be processed in a payroll run to calculate net pay.

Payroll

For Oracle Fusion Global Payroll, you define earning and deduction elements, such as bonus and overtime earnings and involuntary deductions. These elements incorporate all the components required for payroll processing, including formulas, balances, and formula result rules.

Elements: Explained

Elements are the building blocks of payroll and benefits. There is no limit to the number of elements you can define. You define the policies or business rules that govern the allocation of these elements to your workers.

Elements can represent:

Oracle Fusion supplies many predefined elements while additional elements are generated when you define certain types of compensation and payroll elements through templates.

Predefined Elements

The predefined elements are specific to your localization. They typically include deductions for tax and wage attachments. You cannot make any changes to these predefined elements.

Element Creation

You can create many earnings and deductions from element templates. The templates include the elements, balances, balance feeds, and formulas required for payroll processing. You can configure any of these definitions to match your specific business requirements.

The components of an element's definition are available for entry based on the primary and secondary classification you select, for example a standard earning. This diagram illustrates element definition components and what is defined in each component.

Element definition components

For example, you can define an element called Wage, for hourly paid workers. You classify the element in the predefined classification Earnings, which determines when it is processed in the payroll run and what payroll balances it feeds.

You must specify at least one input value, in this case Hours Worked, which must be entered in each payroll period. If required, you can define multiple input values with fixed values, defaults, or validation.

You associate a formula with the element, to calculate the wage for the payroll period. A simple formula might be hours worked, from the input value, multiplied by an hourly rate, from compensation information on the employment record. You define who is eligible for the element by assigning eligibility criteria to various components in the persons employment record, such as grade, payroll, salary basis, or organization. In this example, the wage element is available to all persons on the weekly payroll.

Maintaining Elements: Explained

After you have defined and used an element, updates to the element are limited to ensure the integrity of the element for retroactive processing and the balances of the input values. You cannot remove existing input values or add new ones if you have created entries for the element. To add an input value to an element before you create any element entries, set the element entries effective date to the element's start date.

You can make the following changes to an element that has been previously processed:

Determining Entry Values for an Element: Critical Choices

You can select rules for the element's entry value to define how you can update the element entries. The options are:

Automatic Entry

Entry values can be automatically added in element entries in three ways.

  1. Elements can be defined to default an input value at creation. The user defining the element can specify the entry value to be defaulted when an element entry is created. Users can override or change the default at any time. Changes to this type of a default value on the element do not affect existing element entries.

  2. Elements can be defined to default an input value at run time. When this is selected, the element will automatically default the element entry value. `This value can be updated if needed. A change to the default value for an input value set to default at run time will automatically change the value to be applied at run time for everybody with an entry for that element.

  3. Some entry values are automatically created by a service or process used with compensation, benefits, or formula results.

Important

An element with automatic entry allowed cannot allow multiple entries in the same period.

Allow Multiple Entries in Same Period

This option allows you to give a person more than one entry of the element in the same pay period. For example, if you enter overtime hours on a weekly basis for monthly-paid persons, you might need to give a person five entries of an overtime element in each period.

If you are creating a net-to-gross element, you must select Allow multiple entries in same period.

Additional Entry

This option allows you to add an occasional one time entry for recurring elements. This additional entry can override or add to the normal entry amount.

Element Result Rule Options: Explained

At minimum, an element needs one standard processing rule. This identifies the formula the payroll run uses to process the element for persons with an active employment record. It is also the default formula for other assignment statuses. However, you can define additional processing rules if you need to use different formulas for assignments at other statuses. For example, you could have two rules for a Wages element: Standard Wages and Paid Training Leave.

You can add one or more of the following optional results rules to an element:

For all formula result types except Direct Result or Message, select the target element name to which you want to pass the formula result. This element must have a processing priority causing it to process after the element sending the result.

For the formula result types Direct Result, Indirect Result, and Target Indirect, select the target input value to update.

Direct Result Rule

This is the element's run result, or a direct result updating one of the element's input values.

Indirect Result Rule

This result passes as an element entry to another nonrecurring element not yet processed.

Message Rule

The formula issues messages under certain conditions. For example, a formula can check a loan repayment balance and, if the balance is zero, issue the message "Loan is repaid."

There are three severity levels for a message rule:

Order Indirect Rule

This result updates the subpriority of the element you select in the Target Element Name field.

Stop

This formula result uses the Date Earned of the payroll run to put an end date on a recurring entry of this or another element (which must be defined with Allow Multiple Entries not selected).

Target Indirect

This result updates recurring entries of this or another element on the effective date of the payroll run. The receiving element must be defined with Allow Multiple Entries not selected unless you are passing a recurring element's entries to itself, that is updating another entry of the same element. With this result rule, any future-dated changes to the entry will be overwritten by the results of the current payroll run.

Determining the Element's Latest Entry Date: Critical Choices

The element's latest entry date determines how element entries process after a person is terminated. The options are:

Note

These are the predefined options, you can create others that fit your business needs.

Final Close

This option allows the element to stay open for entries beyond a persons last day worked. For example, you want the element to stay open to pay a severance package to a terminated person.

Last Standard Earning Date

For recurring elements, this option stops all element entries on the date the person leaves. For nonrecurring elements, this option stops all element entries at the end of the pay period in which the person leaves, or on the date the assignment ends if this is earlier.

Last Standard Process Date

The last standard process date defaults to the last day of the pay period in which the person is terminated, but you can set it to a later period when you terminate a person. It enables all recurring and nonrecurring element entries to stop on the last standard process date or on the date the assignment ends, if this is earlier.

Note

This option is only available for Fusion Global Payroll users.

Element Eligibility: Explained

Element eligibility determines which people are eligible for an element. To determine eligibility, you assign element eligibility criteria to the components that persons must have to receive entries of the element. While some elements may represent compensation, deductions, and equipment available to all persons, many elements are available only to certain groups of persons. For example, your enterprise might provide company cars only to persons in the Sales Department. Eligibility criteria rule out the possibility of persons getting element entries by mistake. For example, you might want to give a production bonus only to those persons who work full time in Production and are on the weekly payroll. To do this you would define eligibility criteria for the element Production Bonus and the combination of the Production organization, the Full-Time assignment category, and the Weekly payroll.

Eligibility Criteria

Element eligibility can be assigned by many different criteria.

Note

In order to enter an element for a worker, you must define element eligibility for every element. This must be done for predefined elements and those you define.

Multiple Rules of Eligibility

You can define more than one eligibility criteria for each element but there must be no overlap between them. For example, you could create one criteria for the combination of grade A and the job Accountant. However, you could not create one criteria for grade A and a second for the job Accountant. This would imply that an accountant on grade A is eligible for the same element twice. If you have more than one criteria for an element, you can enter different default values, qualifying conditions, and costing information for each eligibility group.

Maintaining Element Eligibility: Explained

Absence Processing in Payroll Runs: Critical Choices

When you create an absence element for an absence type, your choice of element classification determines how absence entries display on the statement of earnings. You must use one of the following classifications:

Using the Information or Absence Classification

Use either of these classifications if you want to:

Note

Although both the Information classification and Absence classification work in the same way, you can select either classification depending on your reporting requirement.

Since payroll runs do not process elements in the Information or Absence classifications, you can use an Earnings element to manage the calculation and payment of absences. For example, you can define a skip rule for an Earnings element that triggers processing when it finds an entry for the absence element. The payroll formula associated with the Earnings element uses the database item of the absence element to retrieve the total number of absences. Then the formula uses another database item to retrieve the salary or hourly rate to calculate the total absence pay for the period and, if necessary, reduce the regular earnings.

The following figure depicts the relation between the Absence element, Earnings element, skip rules, and the payroll formula associated with the Earnings element:

Relation between the absence element,
earnings element, skip rules, and the payroll formula associated with
the earnings element

Using the Standard Earnings Classification

Use this classification if you want to:

This approach creates a one-line entry on the statement of earnings for each absence type. For example, you can use this classification if your employees submit timecards, and you want absences taken by these employees to show on the statement of earnings.

Recurring and Nonrecurring Absence Elements: Critical Choices

When you set up an absence type, you can determine how absences are processed in payroll runs. Choose the type of element to associate with the absence type:

Using a Nonrecurring Absence Element

Nonrecurring absence elements are valid for the payroll period in which the absence starts. The application creates the entry only when you enter the absence end date. The element entry records the full value of the absence duration even if the end date falls beyond the payroll period.

For example, if you enter an absence that starts on May 24 and ends on June 5 for someone on a monthly payroll, the element entry is dated May 1 to May 31 and records the full value of the absence duration (13 days).

Using a Recurring Absence Element

Use a recurring absence element if you want the payroll run to process absences that have not ended. To process the absence element and calculate the absence duration in each payroll period, you use a payroll formula that handles proration.

Recurring absence element entries start on the absence start date and end on the absence end date (if there is an end date). If the absence starts or ends in the middle of a payroll period, the payroll run detects and processes the absence using the proration functionality.

Setting Up an Earnings Element: Worked Example

The example shows how payroll managers create a regular earnings element using an element template.

First you create an earning element then update it to allow for multiple entries.

Creating an Earnings Element

  1. In the Payroll Calculation work area, click Manage Elements.
  2. Click Create.
  3. Complete the fields, as shown in this table.

    Field

    Value

    Legislative Data Group

    Your Legislative Data Group

    Primary Classification

    Standard Earnings

    Secondary Classification

    Regular


  4. Click Continue.
  5. On the Basic Details page, complete the fields, as shown in this table.:

    Question

    Answer

    Name

    REGULAR SALARY

    Reporting Name

    Regular Salary.

    Effective Date

    1/1/2010

    Input Currency

    US Dollar

    Should every person eligible for the element automatically receive it?

    No.

    What is the earliest entry date for this element?

    First Standard Earnings Date

    What is the latest entry date for this element?

    Last Standard Earning Date

    At which employment level should this element be attached?

    Assignment Level

    Does the element recur each payroll period, or does it require explicit entry?

    Recurring

    Process the element only once in each payroll period?

    Yes

    Can a person have more than one entry of the element in a payroll period?

    No

    Process and pay element separately or with other earnings elements?

    Process and pay with other earnings


  6. Click Next.
  7. On the Additional Details page, complete the fields, as shown in this table.

    Question

    Answer

    What is the calculation rule?

    Flat Amount

    How do you want to derive the amount?

    Entered value

    What is the time-basis for this element?

    Periodically

    Is this element subject to proration?

    No

    Is this element subject to retroactive changes?

    No

    Use this element to calculate a gross amount from a specified net amount?

    No

    Should this element reduce regular earnings?

    No


  8. Click Next.
  9. Verify the information is correct.
  10. Click Submit.

Reviewing an Earnings Element

On the Element Summary page, review the newly created element details for accuracy.

  1. Review the basic details for the earnings element, for example Element Name, Classification, and Description.
  2. In the Standard Rules section, verify that the element is recurring.
  3. Verify that the employment level is assignment level.
  4. In the Currency section, verify that the currency is US Dollars.

Updating an Earnings Element

On the Element Summary page, update the newly created element details.

  1. Click Edit Element.
  2. Select today's date.
  3. Click Continue.
  4. Scroll to the Standard Rules region.
  5. In the Entry Options section, select the Allow multiple entries in same period option.
  6. Click Save.
  7. In the Element Overview section, select the expand arrow.
  8. Expand the Input Value folder.
  9. Select Pay Value.
  10. Select Edit Element.
  11. Select today's day on the window, and click Continue.
  12. In the Element Overview section, Select Actions, Create Element Eligibility Criteria.
  13. On the Element Eligibility name field, enter REGULAR SALARY ELIG.
  14. In the Eligibility Criteria section, select All payrolls eligible.
  15. Click Save.
  16. Click Submit.

FAQs for Manage Elements

What's the difference between a recurring and nonrecurring elememt?

A recurring element has an entry that applies in every payroll period until the entry is ended.

Note

A base pay element associated with a salary basis must be recurring.

A nonrecurring element has an entry that applies in one pay period only. It is only processed once per payroll period. The dates of the pay period are determined by the payroll to which the person is assigned.

Note

A net-to-gross element must be nonrecurring.

What's an element's skip rule?

A skip rule is a formula that determines the circumstances in which an element should be processed. If the conditions of the formula are met, then the element is processed. Otherwise the element is skipped from processing.

When does an element get processed with a processing option of process once per period?

An element processes entries only in the first payroll run of each period for this element.

If this option is not available for your localization, you can select a skip rule to process this element once each period.

What happens if the Closed for Entry option is selected on an element?

It prevents all new element entries for the element. Selecting this option will not affect any existing element entries.

Use caution with this feature. When hiring, terminating, or updating assignments, this option will prevent element entry creation for the element, even if the element is used for automatic entries.

What happens if I override an element entry that has a runtime default set at the element's definition?

If you override it, then any subsequent changes to the default value on the element or element eligibility definition will not affect the element entry. However, you can clear your entry if you want to restore the default value.