## Functions Used in Oracle HRMS

In previous article of Fast Formula, we understood the concept, its usage and different types of Fast Formulas in Oracle HRMS. In this article i am going to explain the functions that we can use in Fast formula to make the desired logic. Following are different types of functions that can be used in the formula

Text Functions

Date Functions

Data Conversion Functions

Number Functions

Functions to Get values from Tables

External Formula Function

# Text Functions

## CHR

CHR(n)

The CHR function returns the character having the binary equivalent to number operand n in the database character set.

Example

/* CHR (10) used to add a newline to the end of REPORT_TEXT2. */

REPORT_TEXT2 = ‘Warning the Transaction Limit has been exceeded’ Back to Top

## DEBUG

DEBUG(expr)

This function accepts a string and uses a DBMS_OUTPUT statement to output the string to the console. Use this function when you are testing a new formula to track its

processing and identify where it is failing.Back to Top

## GREATEST

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

GREATEST_OF(expr, expr [, expr] . . .)

The GREATEST function compares the values of all the text string operands. It returns the value of the operand that is alphabetically last. If there are two or more operands that meet the criteria, Oracle FastFormula returns the first.Back to Top

## INITCAP

INITCAP(expr)

The INITCAP function 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.Back to Top

## INSTR

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

The INSTR searches expr1 beginning with its nth character for the nth occurrence of expr2 and returns the position of the character in expr1 that is the first character of this occurrence. If n is negative, Oracle FastFormula 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 Oracle FastFormula 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 (if expr2 does not appear m times after the nth character of expr1) the return value is 0.Back to Top

## INSTRB

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 database character set, INSTRB is equivalent to INSTR.Back to Top

## LEAST

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

LEAST_OF(expr, expr [, expr] . . .)

The LEAST function compares the values of all the text string operands. It returns the value of the operand that is alphabetically first. If there are two or more operands that meet the criteria, Oracle FastFormula returns the first.Back to Top

## LENGTH

LENGTH(expr)

The LENGTH function returns the number of characters in the text string operand expr. Note: The data type of the result of this function is numeric.Back to Top

## LENGTHB

LENGTHB(char)

The LENGTHB function returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.Back to Top

## LOWER

LOWER(expr)

The LOWER function returns the string operand expr with all letters lowercase. The return value has the same datatype as the argument expr.Back to Top

## LPAD

(expr, n [,pad])

The LPAD function 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 LPADreturns 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 )Back to Top

## LTRIM

(expr [,set])

The LTRIM function returns the text string operand expr with all the leftmost characters that appear in set removed. The default for set is a blank. If none of the leftmost 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’)Back to Top

## REPLACE

(expr, search_string [,replacement_string])

The REPLACE function returns the text string operand expr with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is NULL, expr is returned. REPLACE allows you to substitute one string for another as well as to remove character strings.

Example:

SELECT REPLACE (‘JACK and JUE’,’J’,’BL’) “Changes”

FROM DUAL

Changes

————–

BLACK and BLUE

## RPAD

(expr, n [,pad])

The RPAD function 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 RPADreturns 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 )Back to Top

## RTRIM

(expr [,set])

The RTRIM function returns the text string operand expr with all the rightmost characters that appear in set removed. The default for set is a blank. If none of the

rightmost 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’)

## SUBSTRING

SUBSTR(expr, m [,n])

SUBSTRING(expr, m [,n])

The SUBSTRING function returns a substring of the text string operand expr of length n characters beginning at the mth character. If you omit the third operand, the substring starts from m and finishes at the end of expr.

Note: The first operand is a text operand. The second and third operands are numeric operands. The resulting data type of this function is text.

Tip: Always check string length before you start to substring.

For 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 …

Or, to check if Tax_code is a string of at least two characters starting

with ‘GG’:

IF Tax_code LIKE ‘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.Back to Top

## TRANSLATE

(expr, from, to)

The TRANSLATE function 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. Oracle FastFormula interprets the empty string as null, and if this function has a null argument, it returns null.Back to Top

## TRIM

TRIM(trim_character FROM trim_source)

The TRIM function allows you to trim heading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, you must enclose it in single quotes. You can specify LEADING or TRAILING to remove leading or trailing characters. If you specify none of these, both leading and trailing characters are removed equal to trim_character.Back to Top

## UPPER

UPPER(expr)

The UPPER function converts a text string to upper case. Back to Top

# Numeric Functions

## ABS

ABS(n)

The ABS function 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) returns 17Back to Top

## CALCULATE_HOURS_WORKED

CALCULATE_HOURS_WORKED(n, date1, date2, standard_frequency)

The CALCULATE_HOURS_WORKED function returns the total number of hours worked in a given date range. The function works by calculating the total number of hours worked for an employee between date1 and date2, taking into account that the employee works n hours in the standard working period standard_frequency. This parameter gives the unit of measurement for the standard working period. It can be one of:

• W (weekly)

• M (monthly)

• Y (yearly)

Example:

CALCULATE_HOURS_WORKED (40, 01-FEB-2003, 28-FEB-2003, W) returns 160

This indicates that the employee has worked 160 hours in the month of February 2003, based on a 40-hour week and taking into account the number of working days in that month.Back to Top

## FLOOR

FLOOR(n)

The FLOOR function returns the integer part of a numeric operand n. If the value of the operand contains information after the decimal point, Oracle

FastFormula discards that information and returns a whole number.

Example:

FLOOR(35.455) returns 35Back to Top

## GREATEST

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

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

The GREATEST function compares all the operands and returns the largest value.Back to Top

## LEAST

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

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

The LEAST function compares all the operands and returns the smallest value.Back to Top

## POWER

POWER(m,n)

Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.Back to Top

## ROUND

ROUND(n [, m])

The ROUND function rounds off a numeric value n to m decimal places and a date depending on the format of m. For numeric values, the first operand is the value Oracle FastFormula rounds off, the second the number of places Oracle FastFormula rounds off to. For dates, ROUND returns n rounded to the unit specified by the format model of m such as Year or Day. Refer to the SQL Language Reference Manual for details of the

valid formats you can specify.

Examples:

ROUND(2.3401, 2) returns 2.34

ROUND (2.3461, 2) returns 2.35

ROUND (TO_DATE(’27-OCT-1992′, ‘DD-MON-YYYY’), ‘YEAR’) returns 01-JAN-1993Back to Top

## ROUNDUP

ROUNDUP(n [, m])

ROUND_UP(n [, m])

The ROUNDUP function rounds a numeric value n up to m decimal places. The first operand is the value to be rounded up, the second the number of places to round to. If the digits after the rounding point are zero, the value is unchanged. If the digits are not zero, the value is incremented at the rounding point.

Examples:

ROUND_UP(2.3401, 2) returns 2.35

ROUND_UP(2.3400, 2) returns 2.34.Back to Top

## TRUNC

TRUNC(n [, m])

TRUNCATE(n [, m])

The TRUNC function rounds a numeric value n down to m decimal places. The first operand is the value to be rounded down, the second the number of places to round to. TRUNC also returns n with the time portion of the day truncated to the unit specified by the format model of m. If you omit m, d is truncated to the nearest day The default model, ‘DD’, returns the date rounded or truncated to the day with a time of midnight. Oracle FastFormula drops all digits (if any) after the specified truncation point.

Examples:

TRUNC(2.3401, 2) returns 2.34.

TRUNC(TO_DATE(’27-OCT-1992′, ‘DD-MON-YYYY’), ‘YEAR’) returns 01-JAN-1992Back to Top

# Date Functions

# Date Functions

## ADD_DAYS

ADD_DAYS(date, n)

The ADD_DAYS function adds a number of days to a date. The resulting date accords with the calendar. Note: Oracle FastFormula ignores any fractional part of the number n.

Example:

ADD_DAYS (’30-DEC-1990′ (date), 6) returns 5 JAN 1991Back to Top

## ADD_MONTHS

ADD_MONTHS(date, n)

The ADD_MONTHS function adds a number of months to a date. The resulting date accords with the calendar. Note: Oracle FastFormula ignores any fractional part of the number n.Back to Top

## ADD_YEARS

ADD_YEARS(date, n)

The ADD_YEARS function adds a number of years to a date. The resulting date accords with the calendar. Note: Oracle FastFormula ignores any fractional part of the number n.Back to Top

## GREATEST

GREATEST(date1, date2[, date3] . . .)

The GREATEST function compares all the operands and returns the latest date.Back to Top

## LAST_DAY

LAST_DAY(d)

The LAST_DAY function returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.Back to Top

## LEAST

LEAST(date1, date2 [, date3] . . .)

The LEAST function compares all the operands and returns the earliest date.Back to Top

## DAYS_BETWEEN

DAYS_BETWEEN(date1, date2)

The DAYS_BETWEEN function returns the number of days between two dates. If the later date is first, the result is a positive number. If the earlier date is first, the result is a negative number. The number returned is also based on the real calendar.

Note: The result is a numeric data type.

Example:

DAYS_BETWEEN(‘1995/06/27 00:00:00’ (date), ‘1995/07/03 00:00:00’ (date)) returns -5Back to Top

## MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)

The MONTHS_BETWEEN function returns the number of months between two dates. If the later date is first, the result is a positive number. If the earlier date is first, the result is a negative number. The number returned is also based on the real calendar. If the result is not a whole number of months (that is, there are some days as well), the days part is shown as a decimal.

Note: The result is a numeric data type.Back to Top

## NEW_TIME

NEW_TIME(d, zl, z2)

Returns the date and time in zone z2 when the date and time in zone z1 are d. The arguments z1 and z2 can be any one of these text strings:

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 Daylght Time

GMT Grenwich 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 TimeBack to Top

## NEXT_DAY

NEXT_DAY(d, expr)

The NEXT_DAY function returns the date of the first weekday named by expr that is later than the date d. The argument expr must be a day of the week in your session’s date language. The return value has the same hours, minutes, and seconds component as the argument d.

Back to Top

# Data Conversion Functions

Use data conversion functions to convert from one data type to another data type. For example, you could have an expression returning a number value for salary, which you want to include in a printed message (that is, a character value). To print the number as part of the message, you need to convert the value of salary from a number to a character value, using the TO_TEXT function.

## CONVERT

(expr, dest_char_set [,source_char_set])

The CONVERT function converts a character string from one character set to another. The expr argument is the value to be converted. The dest_char_set argument is the name of the character set to which expr is converted. The source_char_set argument is the name of the character set in which expr is stored in the database. The default value is the database character set.Back to Top

## INSTR

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

The INSTR function searches expr1 beginning with its nth character for the mth occurrence of expr2 and returns the position of the character in expr1 that is the first

character of this occurrence. If n is negative, Oracle FastFormula counts and searches backwards.Back to Top

## NUM_TO_CHAR

NUM_TO_CHAR(n, format)

Converts the number n from number data type to text data type using the specified format. This function is equivalent to the SQL TO_CHAR function. For example:

NUM_TO_CHAR(amount, ‘$9,990.99’) This returns the amount with a leading dollar sign, commas every three digits, and two decimal places. Refer to the SQL Language Reference Manual for a full list of the valid number formats you can specify.Back to Top

## TO_DATE

TO_DATE (expr [, format])

Converts the expression expr of text data type to a date data type. The text expression must be of the form ‘YYYY/MM/DD HH24:MI:SS’ if no format is provided. The day and year must be in numeric form. For example:

* legal */

date_1 = TO_DATE (’12 January 89′, ‘DD Month YY’)

/* illegal */

date_1 = TO_DATE (’12 January Nineteen-Eighty-Nine’,’DD Month Year’)

Note: When assigning date variables from constants it is much more efficient to say:

date_1 = ‘1989/01/12 00:00:00′(date)

Note: The text expression must be in the format of either YYYY/MM/DD HH24:MI:SS or DD-MON-YYYY if no format is provided.Back to Top

## TO_NUMBER

TO_NUM(expr)

TO_NUMBER(expr)

Converts the expression expr of text data type to a number data type. The expression must represent a valid number. So for example, you cannot convert an expression such as `Type 24′ but you can convert the text expression `1234′. For decimal values, you must always use a period as a decinal point, for example ‘4.5’.Back to Top

## TO_TEXT

TO_TEXT(n) TO_TEXT (date1 [, format])

TO_CHAR(n) TO_CHAR(date1 [, format])

DATE_TO_TEXT(n) (date1 [, format])

The TO_TEXT function converts:

• the number n from number data type to text data type. The default number format has the decinal point as a period, for example ‘4.5’.

• the date date1 from date data type to text data type. The optional format should be a text string like ‘DD/MM/YYYY’. The default format is ‘YYYY/MM/DD HH24:MI:SS’.

For example:

birthdate = ’21-JAN-1960′ (date)

mesg = ‘Birthdate is: ‘ + TO_CHAR (birthdate)

/* sets mesg to ‘Birthdate is: 1960/01/21 00:00:00’ */

mesg = ‘Birthdate is: ‘ + TO_CHAR (birthdate, ‘DD-MON-YY’)

/* sets mesg to ‘Birthdate is: 21-JAN-60’ */

mesg = ‘Birthdate is: ‘ + TO_CHAR (birthdate, ‘DD Month Year’)

/* sets mesg to ‘Birthdate is: 21 January Nineteen-Sixty’ */ Back to Top

# Functions to Get values from Tables

## GET_LOOKUP_ MEANING

GET_LOOKUP_MEANING(lookup_type , lookup_code)

The GET_LOOKUP_MEANING function enables Oracle FastFormula to translate a lookup code into a meaning. This can be used for any descriptive flexfield items or

developer flexfield items that are based on lookups.

Example:

GET_LOOKUP_MEANING (‘ETH_TYPE’, PEOPLE_GB_ETHNIC_ORIGIN)Back to Top

## GET_TABLE_VALUE

GET_TABLE_VALUE(table_name, column_name, row_value [,effective date])

The GET_TABLE_VALUE function returns the value of a cell in a user-defined table. The three text operands, which identify the cell (table_name, column_name, and

row_value), are mandatory. The date operand is optional. If it is not supplied, the function returns the cell value as of the effective date. You cannot use this function in formulas for user table validation or QuickPaint reports.

Example:

GET_TABLE_VALUE(‘WAGE RATES’, ‘Wage Rate’, Rate_Code)Back to Top

## RAISE_ERROR

RAISE_ERROR(application_ID, message name)

This function allows you to raise a functional error message from within a formula. It accepts an Application ID and the message_name of an Oracle Applications error

message to raise.

Example:

ERROR = RAISE_ERROR(800, ‘error_name’)Back to Top

## RATES_HISTORY

RATES_HISTORY(element or rate type name, date, element or rate type indicator, time dimension)

This function uses information stored in the UK Element Attribution Information EIT and information about the assignment’s contract type to calculate a payment rate as of the given date and expressed for the selected time dimension (such as hourly or annual). If the element or rate type indicator is R, the function sums the rates for all elements classified with the given rate type (which is stored against the element in the Rate Type Information EIT).

The time dimension parameter must be A (annual), D (daily), H (hourly), or P (periodic). The element or rate type parameter must be R (rate type) or E (element).

The function can also adjust the returned rate for FTE and length of service, if these factors are set to Yes in the Element Attribution Information.Back to Top

# External Formula Function

External Functions refers to logic written in database package and calling in fast formula by defining it through Define Function in Oracle HRMS. I shall write a seperate article on it.

**Related Links:**

Fast Formula Overview

Data Types Used in Oracle Fast Formula

Interview Questions