Friday, February 20, 2015
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.
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.
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:
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.