Table-Based Configuration Rules
Overview
Oracle CPQ provides a point-and-click editor to define recommendation and constraint rules that access Data Tables, without resorting to BMQL. Table-based rules enable:
- Ability to return attribute values along with table data
- Query Debugger
- Point and Click UI for BMQL
Table-based rules fire on the user side when the condition is met. However, if no results are returned from the query then there is no impact on the action attributes. When multiple rows are returned for an action attribute, all returned values are recommended or constrained.
Administration
Creating a Table-based Rule
- Navigate to the Supported Product Families page.
- Click Admin to go to the Admin Home Page.
Click Catalog Definition in the Products section.
The Supported Products page opens.
Confirm that Product Families is listed in the Navigation column, and click List.
The Supported Product Families page opens.
-
For the Product Family for which you want to create a Table-based rule, select Recommendations in the Navigation column and click List.
The Recommendation: Rules List page opens.
- Click Add to create a new rule.
-
Enter the rule Properties..
This section is where you will enter basic details about the rule. For example, the name, status and where it will be applied.
Name |
Enter the name of the rule. This is required. |
Variable Name |
Enter a variable name for the rule. The Variable Name field populates automatically. Variable names can only contain alpha-numeric characters and underscores. The entry can be changed before saving, but after saving the value is read-only.This is required. |
Description |
Enter a description, if desired. |
Status |
Determine the status of the rule:
- Active: Rule is fired when the condition is met.
- Internal: Rule is active only for FullAccess users; it is inactive for all other users.
- Inactive: Rule is not fired, even if the condition is met.
|
Edit Start/End Dates |
Select the box to open a date selector to set how long the rule will remain in the chosen status. If no duration is selected, the rule will remain on the selected status until you, or another FullAccess user, changes it. |
AJAX Enabled |
Upon checking this box, you are enabling AJAX functionality for the rule. This means that the rule will run without refreshing the page or requiring an action be performed.
- Prior to 19B, AJAX functionality is only available for Hiding and Constraint rules.
-
Beginning in Oracle CPQ 19B, Recommendation Rules can be AJAX enabled for JET Configuration UIs. AJAX functionality is only provided for JET Configuration UIs, this functionality is not available for legacy Configuration UIs.
|
-
Select the Condition Type.
The condition section allows you to select the logic that specifies when a rule should run. There are three options: Always True, Simple Condition and Advanced Condition.
Configuration Context Attribute
Beginning in Oracle CPQ 24B the Config Operation Context (_config_operation_context
) configuration system attribute can be used to identify the operation that initiated the current Configuration session. This read only system attribute will reflect unique values for new configurations, reconfigurations, search flows, and ABO Modify actions.
The Config Operation Context system attribute is available for use in all Configuration Rule type conditions and in BML advanced conditions and/or actions. For example, when a Config Operation Context attribute is selected in a simple condition, the possible values (new
, reconfigure
, modify_asset
, and search_flow
) can be selected in the Values drop-down.
The Config Operations Context attribute can be used in Configuration Rules to allow Administrators to better manage the configuration experience if necessary. For example, if an administrator would like to define a rule that should only execute for a new configuration, or resulting from a modify asset action, they can use this new attribute to control execution.
-
Select an Apply rule to value:
- Configuration: Apply the rule to the Configuration. This is the default value.
- Selection: Apply the rule to Search Flows.
- Both: Apply the rule to both the Configuration and Search Flows.
-
Select Table Lookup as your Action Type. A field of four tabs opens.
If you are creating a Recommended Item rule, there will be an option to
Split Items.
- In the Select Table tab, select the Data Table from which you would like to retrieve data.
-
Click the Specify Filter for Table Rows tab and select to Evaluate the Row with:
Array attributes used in the filter will execute for each array index. Also, array attributes with the same control attribute can be used in the same filter.
-
Enter the order of operations in the Row Grouping section.
If you only have one condition, you will still need to enter the value of 1 in this field.
- Click into the Value cell and select attribute values from the drop-down or enter a static value. This is based on what you selected in Step 8.
- Click on the Select Attributes to Populate tab.
-
Click Add Row to add:
- The attribute(s) whose value you will populate with table data.
- The table column that will populate the attribute
- Set Type (None, Set or Forced Set), and
- Message Type (Static or from a Table Column).
For Recommendation Rules: Specify the action attribute and the table column from which the recommendation value will be chosen. In addition, select a Set type for the rule.
For Constraint Rules: Specify the action attribute(s) and the constrained value. For Menu action attributes, choose the constraint type: Constrain or Allow.
- Click the Test Table Results tab.
-
Select the Column and Value to test and click Run.
The Output is the information from the table that will be returned when a user selects Meat Lovers as the Specialty Type (in this case).
Creating a Recommended Item Table-based Rule
Creating a Recommended Item Table Based rule is similar to how you'll use tables in Recommendations and Constraints. The main difference is instead of the 3rd tab being Select Attributes to populate, you'll have a Map Item Properties tab.
- Follow Steps 1-10 from Creating a Table-based Rule.
-
Click the Map Item Properties tab. Here you will map attributes, static values or table columns to the item, quantity, comment and price (for example, return string columns).
HTML and Date attributes cannot be used as inputs for Map Items Properties.
Array attributes can only be mapped to a return column when at least one array attribute with the same control was specified in the filter. The same index used in the filter will also be used to populate the mapping results.
Item |
Table Column |
You must map directly to a table column, such as Part. |
Quantity |
Table Column
Static Entry
Attribute
|
You can map to a table column, you can define a value, or you can map to a configuration attribute.
Note: If you map the Quantity field to a Table Column, the table column must be of an integer data type.
|
Comment |
Table Column
Static Entry
Attribute
|
You can map to a table column, you can define a value, or you can map to a configuration attribute. |
Price |
Table Column
Attribute
Item Master
|
You can map to a table column, you can map to an attribute, or you can map directly to the items master (parts database). |
-
Click the Test Table Results tab.
In the Input Section, the Table Column will already be set (and read-only).
-
Enter the Value to test and click Run.
Action Attributes in Table-based Rules
The supported action attributes are Text, Integer, Float, Currency, HTML and Data type configuration attributes.
Text |
String |
All string values |
|
Integer |
Integer |
All integer values |
|
Float |
Float |
All float values |
|
Date |
String |
Values in database date format:
yyyy-MM-dd hh:mm:ss
|
|
HTML |
String |
All string values |
Not supported in constraint rules. |
Currency |
Float |
All float values |
For multi-currency applications, exchange rate is applied on the values returned for the currency attribute. |
Text Single Select Menu |
String |
All text values |
|
Integer Single Select Menu |
Integer |
All integer values |
|
Float Single Select Menu |
Float |
All float values |
|
Text Multi Select Menu |
String |
All text values |
Multiple values can be recommended or constrained if '~' delimited values are returned. |
Integer Multi Select Menu |
Integer |
All integer values |
|
Float Multi Select Menu |
Float |
All float values |
|
Array Attributes in Table-based Rule Actions
Recommendation and Constraint table-based rule functionality allows arrays to be used in actions. Use recommendation rules to set values or use constraint rules to constraint values of individual array indexes. Use the value(s) of an array set to pull information from a Data Table by mapping an array attribute to a Data Table column. The column will be used in the filter to evaluate the array attribute value. For a single array attribute, each index will be evaluated individually against the Data Table.
- If there are no array attributes in the Filter tab, the results will be applied to array attributes at all indexes in the result tab.
- Set types will remain the same as current recommendation rules (None, Set, Force Set).
- No match for a particular index will leave the index value and the set type 'as is'.
- The first row that matches the filter will set the array attribute index.
- Constraint behavior for each array attribute will be the same as current constraint rules (Allow, Do Not Allow).
- If there are no matches from the Data Table, for a particular index, constraints will not be applied to that index.
- If no array attributes are in the filter that calls the Data Table, then all indexes of the array attribute will be constrained based off of the returned value.
- The filter will be compared against all rows in the table and all matches will constrain the related index.
- If you specify only non-array attributes in the Table Query condition, the action will apply to all indexes of the specified array attribute.
Simple table-based rules operate per index; they will not operate across indexes. Attribute type limitations are consistent with existing functionality. The table below represents how rules will operate depending on the condition and filter.
Always True
|
Static Values
|
Map first returned row to non-array attribute. |
Map first row to each index of array. Same value for every index. |
Map first row to each index of array and to the Non-Array. |
Always True
|
Non-Array Attributes
|
Map first returned row to non-array attribute. |
Map first row to each index of array. Same value for every index. |
Map first row to each index of array and to the Non-Array. |
Always True
|
Array Attributes
|
All indexes run filter; The recommended value will be the last array index value.
|
All indexes will run filter. Each index applies the first row of the index's filter results. Only indexes with results will perform the recommendation or constraint. |
All indexes will run filter. Each index applies the first row of the index's filter results. Only indexes with results will perform the recommendation or constraint. Non-Array attribute is set to the last result returned by the last index. |
Non –Array Attributes
|
Static Values
|
Map first row returned to Non-array attribute. |
Map first row to each index of array. Same value for every index |
Map first row to each index of array and to the Non-Array. |
Non-Array Attributes
|
Non-Array Attributes
|
Map first row returned to Non-array attribute. |
Map first row to each index of array. Same value for every index |
Map first row to each index of array and to the Non-Array. |
Non-Array Attributes
|
Array Attributes
|
All indexes run filter; The recommended value will be the last array index value. |
All indexes will run filter. Each index applies the first row of the index's filter results. Only indexes with results will perform the recommendation or constraint. |
All indexes will run filter. Each index applies the first row of the index's filter results. Only indexes with results will perform the recommendation or constraint. Non-Array attribute is set to the last result returned by the last index. |
Notes
Only "Equals" and "Not Equals" operators are supported for querying blank values on text type attributes. If any other operator is used for blank values, the query will assume that the operator is "Equals" for "Contains", "Starts With" and "Ends With".
Similarly, the query will assume the operator is "Not Equals" for "Not Contains", "Not Starts With" and "Not Ends With" when querying for blank values.
You can specify multiple action attributes in the Select Attributes to Populate tab for any table-based rule. This behaves similar to standard configuration rules, which can have multiple action attributes where the rules fire per action attribute and not all action attributes have to be present in the flow for the rule to fire for one particular attribute. The rule fires if the condition is met and the condition attribute and the action attribute are in the flow.
All table data must be deployed to see the correct results in the Debugger.
Duplicate values returned by the query are removed and only unique values are recommended or constrained.
When deploying configuration, you can deploy Data Tables that are used in Table-based rules, which have undeployed changes.
You can include Data Tables that are used in Table-based rules in Configuration test to production. If the Data Table does not exist in production, it will be uploaded to the Default folder. If the Data Table exists in production, then the test site data will be appended to the production site data for that table.
If multiple messages are returned, then all messages are displayed in a comma delimited list to the end user, no matter which value is chosen by the user.
Array attribute type limitations are consistent with existing functionality. For example, if the table column is of numeric data type, then the array attribute(s) must be numeric.
In cases where quantity <=0, quantity will be shown as Qty = 1.
Prices will show as the following values in these situations:
- On Request<=0 : Non Number value that is not the keyword “Base”
- Not Defined: No price at all, or blank
- Default Unit PriceKeyword “Base” is found in the Table
Blank queries are not supported for numeric data types.
Simple table based rules operate per index; they will not operate across indexes.
Only array attributes from the same set can be used in the Filter and Mapping tabs. Mixing and matching of array attributes across sets is not allowed.
Related Topics
See Also