Using ADF BC Declarative Built-in Rules (Part 9 of 10): Script Expression validator

The last set of validators consist of the "Script Expression" validator and the "Method" validator which can be defined either at the entity level or the attribute level to validate either the entire entity object or a specific entity attribute.

The "Script Expression" validator enables you to validate either the entire entity object or a specific entity attribute using a Groovy expression that returns either true, meaning that the validation is successful or false to define that the validation has failed.

The "Script Expression" validator has some very interesting characteristics which we will see in practice using an example by Frank Nimphius (https://blogs.oracle.com/jdevotnharvest/entry/using_groovy_in_entity_validation).

I will be using the HR schema and the employees table to define an attribute level validation using a "Script Expression" to define a departmental salary budget and to ensure that the cumulative salary of employees per department does not exceed their respective departmental salary budget.

I have created an ADF Fusion Web Application and created the basic business components that I will be using in this demo; two entity objects based on the Employees and Departments HR tables, default view objects based on the two entity objects and a default application module.

Next let’s define the validation on the "Salary" attribute of the Employees entity object to ensure that the cumulative salary of employees per department does not exceed their respective departmental salary budget. So on the  “Business Rules” tab of the Employees entity object, right-click on the "Salary" attribute and select "New Validator". This will open the “Add Validation Rule” editor for defining the validation specifics.

In the “Type” combo select “Script Expression” and in the "Expression" I will type in my Groovy expression to implement my business rule. The logic for implementing my departmental salary budget rule is as follows:

DepartmentSalaryBudget – (AllSalariesInDepartment + newEmployeeSalary) + currentEmployeeSalary < 0

To retrieve the current employee salary and the new employee salary we can use the oldValue and newValue Groovy script keywords respectively.

Another nice feature with the "Script Expression" validator is that you can use the ternary operator to implement functionality that is similar to SQL’s NVL() function. We will use this functionality to populate a department's salary budget, for example, "departmentSalaryBudget = (DepartmentId == 10 ? 50000 : 100000)". A better approach would be to create a new database column in the "Departments" table to hold the salary budget for each department.
The last part of the puzzle is to calculate all the salaries in a specific department. To do so we will have to use the default accessors that have been created by the default "EmpDeptFkAssoc" association.

So to calculate the total salary of all employees in a department we have to use the "Departments1" accessor to navigate to from a specific employee to his department and from the department use the "Employees1" accessor to retrieve all department employees and use the "sum" aggregate function on the "Salary" attribute, for example, "Departments1.Employees1.sum('Salary')".

One last point on the "Script Expression" validator is that your expression can either return true or false or use the "adf.error.raise" or "adf.error.warn" functions to display a message that is defined in your message bundle.

Putting it all together, the department salary budget business rule validation should look like the following:

I've decided not to use the build-in Groovy functions for displaying the error message but to use the "tradition" failure handling tab functionality. Therefore, in the "Failure Handling" tab define a failure message and click "OK". In my example I have used a message token expression to construct a dynamic error message, passing to the failure message the salary increase by using again the oldValue and newValue keywords to compute the salary increase.

If you inspect the Employee’s entity source code you will see that JDeveloper added an ExpressionValidatorBean tag to the entity's XML definition file.

Run the Application module to test the "Script Expression" validator and open the "EmployeesView1" view object. By default the first record displayed is "Steven King" that belongs to the "Executive" department (department id 90). Based on our business rule definition, the Executive department has a salary budget of $100,000.

The current Executive department salary total is $58,000 (do a "select sum(salary) from employees where department_id = 90"). If you increase Steven King's salary from $24,000 to $66,000 you should notice that there isn't any error raised (the department's total salary is exactly $100,000).

Do a rollback so that Steven King's salary get's back to $24,000 and try to update his salary to $66,001 (which should total the department's salary to $100,001). The validation should fail and you should see your custom error message.

Download sample application: Script Expression