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
Functions Used in Fast Formula
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.
