Developed sample application - CalculatedComplex.zip is based on HR schema available in Oracle XE database and implements logic that allows to edit two calculated columns - MinSalary and MaxSalary.
Calculation rule applied for MinSalary:
- IF (MaxEmployeeSalary - AvgEmployeeSalary <>
- IF (AvgEmployeeSalary + MaxEmployeeSalary > MaxSalary) THEN update(MaxSalary)
MaxEmployeeSalary and AvgEmployeeSalary are values calculated in EmployeesQueryView:
Calculations for both columns are done in Backing bean, methods are invoked through dummy binding to ReadOnly property:
All calculations related to MinSalary are done in getCalculatedMinSalary() method. Based on JobId parameter are calculated AvgEmployeeSalary and MaxEmployeeSalary values, and if specified condition is satisfied MinSalary is updated:
MaxSalary is calculated in exactly the same way as MinSalary.
Now I will show how it works. Let's say we want to focus on PU_CLERK salary:
If in Employees table we set Salary values for PU_CLERK to 1900, 1600, 1700, 1600, 1500 and press Save button - MinSalary in Jobs table for PU_CLERK is updated to 240:
MaxSalary is not updated, because AvgEmployeeSalary + MaxEmployeeSalary = 3560 and this is less comparing to current 8000. So, user can set MaxSalary manually to let's say 5000 and this value will be stored in database:
When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.