Miscellaneous Formula Functions
Here are the formula functions that you can use to manipulate messaging data or retrieve values from user-defined tables:
ESS_LOG_WRITE
ESS_LOG_WRITE(log_message)
Outputs a log message to the Enterprise Scheduler log file.
Refer to Formula Logging section in Improve Formula Performance, when considering using the ESS_LOG_WRITE function. This function should be used sparingly as it can impact system performance.
GET_LOOKUP_MEANING
GET_LOOKUP_MEANING(lookup_type, lookup_code [, default_meaning])
Returns the lookup meaning for the lookup type and code. The default meaning can be provided to return a value if the lookup type and code combination isn't valid. An error will be raised if the lookup type and code combination isn't valid, and no default meaning is provided.
GET_MESG, GET_FND_MESG
GET_MESG(appname, msgname [, token1, value1] [, token2, value2] [, token3, value3] [, token4, value4] [, token5, value5] )
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.
GET_RATE
GET_RATE(from_currency, to_currency, rate_type[,default_rate])
Returns the rate between the two currencies for a given conversion date and rate type. The return type is NUMBER.
Contexts: EFFECTIVE_DATE (text) is the conversion date.
Parameters:
-
FROM_CURRENCY (text): An example would be USD.
-
TO_CURRENCY (text): An example would be GBP.
-
RATE_TYPE (text): The source of a currency conversion rate, such as user defined, spot, corporate, or fixed.
Note: During journal entry, the conversion rate is provided automatically by the General Ledger based on the selected conversion rate type and currency, unless the rate type is user. For user rate types, you must enter the conversion rate. Define additional rate types as needed. Set your most frequently used rate type as the default. -
DEFAULT_RATE (number): The rate that's returned if GET_RATE can’t get a rate. A typical value for this would be 1.0.
GET_RATE calls a General Ledger product API. The API retrieves the rate from the General Ledger daily rates or fixed rates, where appropriate.
GET_TABLE_VALUE
GET_TABLE_VALUE(table_name, column_name, row_value [,default_value])
GET_TABLE_VALUE(table_name, column_name, row_value, effective date)
Returns the value of a cell in a user-defined table on the effective date of the session or process. The first three text operands identify the cell. An optional fourth parameter does one of the following, depending on its data type:
-
Text: Returns a text default value if no data is found.
-
Date: Returns the value of the cell on the specified date.
Here’s an example of this function:
GET_TABLE_VALUE('WAGE RATES',
'Wage Rate', Rate_Code, 'DEFAULT'): Returns the row_value
for Wage Rate or DEFAULT if it doesn’t find a row.
GET_VALUE_SET
GET_VALUE_SET(value_set_code, bind)
Returns the first record of the given value set for the bind passed, whereby the bind
needs to be in this format: <Separator character> <equal
character><first parameter name><equal
character><value><Separator character><second parameter
name><equal character><value><Repeat the same for more bind
parameters>
The return value is up to 100 characters long and is a value set id column value.
GET_VALUE_SET_ID is an alternative name for GET_VALUE_SET.
Here's an example of this function:
ASG_NUMBER=GET_VALUE_SET('SAMPLE_GET_ASG_NUM',
'|=PERSON_ID='''||POSITION3||'''') Passes data to a fast formula function to
retrieve assignment number given PERSON_ID whose value is set from the
position3 variable.
GET_VALUE_SET_VALUE
GET_VALUE_SET_VALUE(value_set, code, bind)
The return value is up to 255 characters long and is a value set value column value. The parameters and value set configuration are the same as for GET_VALUE_SET.
GET_VALUE_SET_IDS
GET_VALUE_SET_IDS(value_set, code, bind)
GET_VALUE_SET_IDS corresponds to GET_VALUE_SET_ID, but returns a TEXT_NUMBER array of values instead of a TEXT value. The parameters and value set configuration are the same as for GET_VALUE_SET.
GET_VALUE_SET_CODES is an alternative name for GET_VALUE_SET_IDS.
GET_VALUE_SET_VALUES
GET_VALUE_SET_VALUES(value_set, code, bind)
GET_VALUE_SET_VALUES corresponds to GET_VALUE_SET_VALUE, but returns a TEXT_NUMBER array of values instead of a TEXT value. The parameters and value set configuration are the same as for GET_VALUE_SET.
GET_VALUE_SET_MEANINGS is an alternative name for GET_VALUE_SET_VALUES.
HR_TRACE(expr)
Outputs a trace message.
HR_TRACE.