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.

ClosedView the JSON data type for Commerce attributes supported within array sets.


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.

ClosedView 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.

ClosedBML 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>

ClosedBML 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}

ClosedReference Example

"feeArraySet" is a main document array set, triggered from the "amount" array attribute in row number "2"

1_feeArraySet/2/amount

ClosedAuto 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.

SyntaxString 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;

ClosedModify Array Attribute Value Example

ClosedCalculate Total Value Example

ClosedInsert a New Row Example

ClosedFind and Update an Attribute Value Example

ClosedDelete a Row Example

Notes

Note: Auto update changes to array attributes are not highlighted at the browser side in 18D.

Related Topics

Related Topics Link IconSee Also