BML Support for Commerce Transaction Arrays
Overview
Oracle CPQ provides BML support for Transaction Arrays. This section describes JSON data types and null value interpretations for array attributes. It also provides information for selecting and modifying array attributes, array attribute auto updates, and array set size control attributes. Oracle CPQ supports the jsonarrayrefid function, which provides a more efficient method to call large array sets.
JSON Data Types for Array Attributes
Array sets can contain Boolean, Currency, Date, Float, Integer, Language, Single Select and Multi-Select Menus, Text, and Text Area type attributes. When using BML, JSON objects are used to reference and modify these attributes.
View the JSON data type for Commerce attributes supported within array sets.
Boolean
|
boolean
|
Returns either a True or False
|
Currency, Float
|
number: double
|
Used to represent values with a fractional part
|
Date
|
string: date
|
This field uses the Oracle database date pattern
- For Example: yyyy-MM-dd HH:mm:ss
Oracle CPQ BML scripting format is also accepted
- For Example: MM/dd/yyyy HH:mm:ss .
|
Integer
|
number: integer
|
Used to represent values without a fractional part
|
Text, Text Area, Single Select Menu, Language
|
string
|
Values are enclosed in double quotes
|
Multi-Select Menu
|
JSON array of strings
|
Alternatively, a comma-delimited string is also accepted.
|
Null Value Interpretations
When using BML functions for Transaction Arrays, the following values are interpreted as null values:
- null
- "" (an empty string for String data types)
- absent fields
Selecting and Modifying Array Attributes
The expected format for a multi-select menu attribute is a JSON array of strings. However, multi-select menu array attributes can also be set using comma-delimited string.
View preferred, alternate, and unacceptable BML samples
-
Preferred method to set Multi-Select Menu attributes
colors = jsonarray("[\"red\", \"blue\"]"); // ["red", "blue"]
jsonput(row, "colors", colors)
-
Alternate method to set Multi-Select Menu attributes
jsonput(row, "colors", "red,blue");
-
Unacceptable method to set Multi-Select Menu attributes
jsonput(row, "colors", "red~blue");
Select Array Sets as Input of BML Functions
Administrators can select array sets as input for BML functions in Library Functions, Advanced Conditions and Advanced Actions within Commerce Access rules, Advanced Conditions with Approvals, and Default Data within attributes. The BML Syntax for Array Sets and Multi-Select Menus sections provide syntax protocol for selecting array sets and array set values.
BML Syntax for Array Sets
Array sets are formatted as JSON arrays in BML.
For example: The following BML sample displays the JSON array for the "feeArraySet".
// feeArraySet: array set variable name
print feesArraySet; // [{"_row_number": 1, "amount":10,"type":"gas"},
// {"_row_number": 2, "amount":50,"type":"water"}]
Referencing a sub-document array set from a main document is similar to referencing a line item attribute.
For example: The following BML sample displays a reference to the sub-document "phoneArraySet" array from the main document.
// lines: sub-document variable name
// phoneArraySet: variable name of array set within a sub-document
for line in lines {
phones = line.phoneArraySet;
print phones; // [{"_row_number": 1, "number":"650-456-1234","type":"Home"},
// {"_row_number": 2, "number":"510-568-2358","type":"Office"}]
}
Note: For performance reason, avoid looping through line items to access either sub-document attributes or sub-document array sets. Performance must be thoroughly validated before deploying to production.
Modify Array Sets using BML
Administrators can use BML functions to retrieve or modify array set values for Advanced Modify functions for modify actions, Advanced Defaults for documents, Auto Update - Before Formulas for documents, and Auto Update - After Formulas for documents. This section provides syntax for array set variables and describes row number behavior.
BML Syntax for Array Set Variables
The JSON string name or array reference ID can be used to return a JSON array.
JSON String Format
<document number>~<array set variable name>~<json string name>
JSON Array Reference ID Format
<document number>~<array set variable name>~<json array reference ID>
BML Example:
In this sample, the fee amount is retrieved, set to a new value, and the modified array set is returned.
// feeArraySet: array set variable name
print feesArraySet; // [{"_row_number": 1, "amount":10, "type":"gas"},
// {"_row_number": 2, "amount":50, "type":"water"}]
feeSize = jsonarraysize(feeArraySet);
indices = range(feeSize);
for index in indices {
fee = jsonarrayget(feesArraySet, index, "json");
// Get the fee amount
feeAmount = jsonget(fee,"amount", "float");
// Set the fee amount to a new value
jsonput(fee, "amount", feeAmount*2);
}
return "1~feesArraySet~" + jsonarraytostr(feesArraySet);
Row Number Behavior for BML Replacements
The following items describe array row number behavior when BML is used to replace array set values.
- Zero or no row numbers indicate a row insert. When these values are received, a temporary negative row number is automatically generated when the row is inserted into an array set.
- Positive and negative row number values are used to replace an existing row or insert a new row into the array set.
- When a modify action is invoked, negative row numbers are replaced with an actual row numbers before saving to the database.
Note: When an array set is updated by BML, the new value of the array set is used to replace the old. Rows are inserted as is. No additional defaults are applied to the inserted rows. This behavior is different from REST web services, which use delta changes and, therefore, apply defaulting to each new row.
Array Set Size Control Attributes
When an array size control attribute is updated, the size of the array set is adjusted accordingly. Conversely, after the BML output changes an array set, the array size control attribute is automatically updated.
As a best practice, avoid updating both the array size control attribute and the array set in BML. When this occurs, the last update will override the first update.
Array Attribute Auto Updates
Auto updates can be triggered on a Commerce attribute or a commerce array attribute if the attribute is enabled for Auto Update. For Example: When the end user is on the main document page, the main document auto update BML's are executed during AJAX.
BML Syntax to Reference Trigger Attributes for Auto Updates
{containerDocNumber}_{arraySetVarName}/{arraySetRowNumber}/{arrayAttrVarName}
Reference Example
"feeArraySet" is a main document array set, triggered from the "amount" array attribute in row number "2"
1_feeArraySet/2/amount
Auto Update BML Example
In the following example, the main document Auto Update BML will update the "feeAmount" field whenever the "feeDiscount" field is auto updated.
// result = ...; -- update the result from some other code snippet
// Auto update triggered by feeArraySet feeDiscount array attribute
if (startswith(_auto_update_triggered_from, "1_feeArraySet/")and endswith(_auto_update_triggered_from, "feeDiscount")) {
triggerTxt = substring(_auto_update_triggered_from, len("1_feeArraySet/"));
triggers = split(triggerTxt, "/");
triggerAttrRowNumber = atoi(triggers[0]);
triggerAttrVarName = triggers[1];
feeSize = jsonarraysize(feeArraySet);
indices = range(feeSize);
for index in indices {
oneFee = jsonarrayget(feeArraySet, index, "json");
if (jsonget(oneFee, "_row_number", "integer") == triggerAttrRowNumber) {
discount = jsonget(oneFee, "feeDiscount", "float");
amount = 300 * (100 - discount) / 100.;
jsonput(oneFee, "feeAmount", amount);
break;
}
}
result = result + "|1~feeArraySet~" + jsonarrayrefid(feeArraySet);
}
return result;
jsonarrayrefid BML Function
The jsonarrayrefid function returns a unique reference ID to represent the input JSON array. When interacting with large array sets, administrators should use the jsonarrayrefid function instead of the jsonarraytostr function. Using the array reference id improves performance for large array sets by minimizing back-and-forth conversion between JSON arrays and strings.
Syntax: String jsonarrayrefid(JsonArray jsonArrayIdentifier)
This function provides a more efficient method to pass a JSON array to the output of Commerce advanced modify, auto update or advanced default functions to set the value of an array set.
- The input argument must be a JSON array.
- The return value is a unique ID string that represents the input object.
The following BML sample returns the "feeJsonArray" using its corresponding reference id.
// feeJsonArray is a JSON array
// feeArraySet is the main doc array set variable name
return "1~feesArraySet~" + jsonarrayrefid(feeJsonArray);
When jsonarrayrefid is not used in the return statement of the BML, be aware that the JSON reference ID refers to the input object by reference. As a result, any subsequent changes made to the input object are included.
result = "1~feesArraySet~" + jsonarrayrefid(feeJsonArray);
jsonarrayremove(feeJsonArray, 0);
return result; // feeArraySet does not include the removed first row.
Use Cases
For the following examples, feesArraySet is an array set attribute on the Quote level and phoneArraySet is an array set attribute on the lineItem level with document_number=2;
Modify Array Attribute Value Example
The following example doubles the fee amounts.
// feeArraySet: the variable name for the input array set
print feesArraySet; // [{"_row_number": 1, "amount":10,"type":"gas"},
// {"_row_number": 2, "amount":50,"type":"water"}]
feeSize = jsonarraysize(jsonFees);
indices = range(feeSize);
for index in indices {
fee = jsonarrayget(jsonFees, index, "json");
// Get fee amount
feeAmount = jsonget(fee,"amount", "float");
// Set fee amount
jsonput(fee, "amount", feeAmount*2);
}
return "1~feesArraySet~" + jsonarraytostr(feesArraySet);
Calculate Total Value Example
The following example calculates the total fees.
// feeArraySet: the variable name for the input array set
print feesArraySet; //[{"_row_number": 1, "amount":10,"type":"gas"},
// {"_row_number": 2, "amount":50,"type":"water"}]
feeSize = jsonarraysize(feesArraySet);
indices = range(feeSize);
total=0;
for index in indices {
fee = jsonarrayget(feesArraySet, index, "json");
feeAmount = jsonget(fee,"amount", "float");
total = total + feeAmount;
}
return "1~totalFeeAmount~" + string(total);
Insert a New Row Example
The following example adds a new fee to the array.
// feeArraySet: the variable name for the input array set
print feesArraySet; // [{"_row_number": 1, "amount":10,"type":"gas"},
// {"_row_number": 2, "amount":50,"type":"water"}]
newFee = json();
jsonput(newFee,"amount",80);
jsonput(newFee,"type","internet");
jsonarrayappend(feesArraySet, newFee);
print feesArraySet; // [{"_row_number": 1, "amount":10,"type":"gas"},
// {"_row_number": 2, "amount":50,"type":"water"},
// {"amount":80,"type":"internet"}]
// The new row's row number will be generated once inserted
Find and Update an Attribute Value Example
The following example locates and updates a phone number.
phones = line.phoneArraySet;
print phones; // [{"number":"650-456-1234","type":"Home"},{"number":"510-568-2358","type":"Office"}]
phoneSize = jsonarraysize(phones);
indices = range(phoneSize);
for index in indices {
phone = jsonarrayget(phones, index, "json");
if (jsonget(phone, "type") == "Home") {
jsonput(phone,"number","408-129-6589");
break;
}
}
print jsonPhone; // [{"number":"408-129-6589","type":"Home"},{"number":"510-568-2358","type":"Office"}]
Delete a Row Example
The following example removes the water row.
// feeArraySet: the variable name for the input array set
print feesArraySet; // [{"amount":10,"type":"gas"},{"amount":50,"type":"water"}]
feeSize = jsonarraysize(feesArraySet);
indices = range(feeSize);
for index in indices {
oneFee=jsonarrayget(feesArraySet, index, "json");
if (jsonget(oneFee,"type") == "water") {
jsonarrayremove(feesArraySet, index);
break;
}
}
print feesArraySet; // [{"amount":10,"type":"gas"}]
Notes
Note: Auto update changes to array attributes are not highlighted at the browser side in 18D.
Related Topics
See Also