Oracle APEX - Condition Builder Plugin

Sharing a region plugin ‘Condition Builder’, which enables you to develop pages by which your users can build conditions like filter- and rule conditions. It makes use of jQuery QueryBuilder. It has customizations for styling, incorporating of APEX components like Date Picker and Popup LOV, and the building of the actual conditions is done server-side in PL/SQL. Below example is especially set up as to show all possible kind of expressions and inputs.

Upon ‘Build’, it generates:

• a Condition Definition in JSON format. This one is send to the server, where the other outputs are generated.

• 

  a Display Condition: a human readable form

• 

  a Filter Condition

• 

  a Rule Condition

• 

  a list of Parameters

Filter Conditions typically are used in SQL where clauses. Below applies it as a Report Filter to an Interactive Report.

The Condition Builder is configured here with Inline Dialog template. Upon apply, the Display Condition is used to show as Report Filter. The Filter Condition is added to the query, and the parameters are used to assign the runtime values to the bind variables. All critical stuff is done server-side in PL/SQL as to further defend against SQL injection, next to the use of prepared statements.

Rule Conditions typically are used in Rules which consist of a condition part and an action part. To evaluate the rule, next to the Rule Condition there is a Context. Examples are an Order, a Project or a Claim. Below example is around a Car Insurance Claim. The Claim has a workflow, and one of the activities (Fraud Investigation Review) has a so called Applicability Rule. The Rule defines when the workflow activity is applicable, where the condition refers the Claim attributes. Upon evaluating the Applicability Rule, the outcome is set as an Applicability Flag on the Activity Instance.

To run a Rule Condition against a Context, an 'evaluate' pl/sql function is available. Upon changing the Applicability Condition or any of the Claim Attributes, the rule can be re-evaluated. Other examples of rules in the context of workflow are ‘Release Rules’ (when an activity appears as a task in the tasks list) and ‘Mandatory Rules’ (conditions for when an activity is mandatory to be executed or not). ‘Workflow’ here is meant as the general concept as you might apply in Case Management applications, so not particular APEX workflow.

So using the Condition Builder, you can enable your user to create (complex) Report Filters, configure Business Logic, define Workflow Rules, etc.

Try some hands-on? See example page.