Using Oracle FastFormula for Validation
Using Oracle FastFormula for Validation
You can use Oracle FastFormula to validate user entries into the element input values, and to user tables that you define. In both cases, you must write and validate the formula before you define the element or table, so
that you can select the formula from a list in the Element window or Columns window. In the Formulas window, select formula type Element Input Validation or User Table Validation. When writing either type of formula, you must observe the following rules:
o There must be one input value, of type text, and it must be called entry_value.
o The formula must set and return a local variable giving the status of the validation (success or error). This variable must be called formula_status and have the value ‘s’ (success) or ‘e’
(error).
o Optionally, the formula can also return a text variable giving an explanatory message. The returned message variable must be called formula_message and can contain any text. It can be
returned with both successful and unsuccessful statuses.
o The formula must not return any other results.
For an element input value validation formula, you must also observe the following rules:
o You cannot use the element’s other pay and input values in the formula.
o You cannot return a value to another pay or input value.
All entry values are stored in the database as text items. Therefore, if you want to validate an entry value as a date or number, you must use Oracle FastFormula’s conversion function to convert the text into a date or number type variable. For example:
TO_NUM (entry_value)
TO_DATE(entry_value,’DD-MON-YYYY’)
Examples
Checking an Element Entry
The formula below checks that the entry value of the Salary element does not exceed 200 000
/* Formula Name: Salary Range */
/* Formula Type: Element Input Validation */
INPUTS ARE entry_value (text)
IF TO_NUM(entry_value) > 200000
THEN
(
formula_status = ‘e’
formula_message = ‘Too much money . . . try again!’
)
ELSE
(
formula_status = ‘s’
formula_message = ‘Fine’
)
RETURN formula_status, formula_message
Checking a User Table Entry
The formula below checks that the deduction entered in the Union A column of the Union Dues table is
between 10.00 and 20.00.
/* Formula Name: Union A Dues Validation */
/* Formula Type: User Table Validation */
INPUTS ARE entry_value (text)
IF TO_NUMBER(entry_value) < 10.00 OR
THEN
(
TO_NUMBER(entry_value) > 20.00
formula_status = ‘e’
formula_message = ‘Error: Union A dues must be between
$10.00 and $20.00.’
)
ELSE
(
formula_status = ‘s’
formula_message = ‘ ‘
)
RETURN formula_status, formula_message
Defining an Element’s Input Values
You can define up to 15 input values for an element, using the Input Values window.
To define input values:
1. Set your effective date early enough to handle any historical element entries you want to make.
2. Enter or query the element in the Element window and choose the Input Values button.
3. Enter the name of the first input value. Remember that if you want to define a pay value, you must name it Pay Value.
4. Select the unit type of your input value (money, hours, character, date, number, or time). A Pay Value must have the unit type Money if the element is in a payments type classification.
5. You can use the Sequence field to change the order in which the input values appear in the Element Entries window.
6. Check the Required check box if all entries of the element must have a value for this input.
7. Check the User Enterable check box if users can enter a value for this input. Uncheck it if you want to ensure that the default value is entered for all employees.
8. Check the Database Item check box if the value can be used as a Database Item in formulas or QuickPaint inquiries.
Database Items are simple identifiers that the system uses to find specific items of information in the human resources database.
Default Values
To enter a default for an input value:
1. Enter the value in the Default field.
2. If you want existing entries to be updated whenever the default is changed, check the Hot Default check box. The input value must be required if you want to use this option.
A hot default appears in the Element Entries window in quotation marks. If the user overrides the default, subsequent changes to the default will not affect the entry.
Validation
To enter validation for an input value:
1. Do one of the following:
Enter maximum and minimum values for the input.
o Select a QuickCode Type to act as a lookup supplying a list of valid values.
o Select a formula to validate entries for this input value. Formulas can return messages to users about the success or failure of the validation.
2. Select Warning or Error to determine whether the system warns users that an entry is invalid or prevents them from saving an invalid entry. You cannot select a value if you chose a Lookup because a user cannot enter an invalid value for an input value validated by lookup.
Element Input Values
When you define an element, you must consider what information you want to record each time you make an entry of the element for an employee.
Typically, you would expect to record more than just the name of the element. For example, if you defined an element to record employee expenses, you might want to record the Type, as a code value, and the Claim Amount as a monetary value. In Oracle HRMS you define these values as input values for the element. When you define an element, you can define up to 15 input values for it. You decide which values you want to record and what limits, orvalidation, to apply to those values.
Input values can be numbers, text, dates, times, hours, or monetary values. You also decide whether each input is required or optional when an entry of the element is made for an employee.
Processing Input Values
Input values are so called because they are the inputs to calculations performed by Oracle Payroll. In a payroll run, formulas process the input values and other database information to produce run results.
For example, if your enterprise makes overtime payments, you might write a formula to calculate the payment amounts for each assignment from inputs of the overtime rate and the hours worked for the period. The payroll run then processes each assignment and produces the overtime payment amounts as run results.
Run results are used for other purposes besides summing the amount of employee pay. In the overtime example, the run result information can also be used for costing purposes and to track the actual hours of overtime worked.
The Pay Value
Oracle Payroll makes special use of the pay value to represent the result of processing an element for employee pay. The pay value is the amount paid to the employee from that element after payroll processing. You must define a pay value as one of the inputs for the element if you want Oracle Payroll to process an element for pay. You can have only one pay value for each element and it must have the name ‘Pay Value’.
You can enter a pay value directly as an input to the element. When you do so, no formula will fire during the payroll run to calculate any direct result for the element. Instead, the pay value you enter becomes the run result.
Validating Input Values
When you define inputs for an element, you also define the validation for each input value. The validation you define controls the values a user can enter. The options are to:
o provide a default value
o provide a minimum and maximum value range
o provide a fixed value
o provide a lookup list of valid values
o validate the input value using a formula
Using the formula option you can model complex business rules for validating entries. For example, for a bonus payment you might want to set a maximum bonus value that depends on length of service and current salary.
With Oracle’s formula writing tool, Oracle FastFormula, you can include conditional logic to validate input values using different criteria for different employees.
Recent Comments