Upload Changes

Upload Changes from a Table Layout

When a user clicks the Upload Changes button for a Table layout, here's what happens:

The add-in checks the table for pending changes. If there are no pending changes, upload is skipped.
If a Pre-Upload macro is configured, it is invoked. If the macro throws an exception or returns any value other than true, the upload process quits.
For pending Create and Update operations, the rows are first validated locally, for example, data type, required, and so on (see Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel). Any failures are marked as failed and skipped (these rows do not produce requests on the business object service).
Pending changes are generally processed as follows:
1. Updates result in a PATCH or PUT request on the item path.
2. Creates result in a POST request on the collection path.
3. Deletes result in a DELETE request on the item path.
4. Rows marked for action result in a POST request on the item action path.
Note:
Batch processing is handled differently. See Upload Changes Using Batch Requests.
Success and failure is noted in the Status column. Errors and warnings are cached and displayed in the Status Viewer when the user clicks on a given row. Here is a sample of the Status Viewer showing two warnings on a successful update:
Successful Create rows are updated from the service and converted into existing rows that can be edited.
Successful Delete rows are removed from the Excel worksheet.

The request payload for Create and Update operations includes a value (possibly empty) for every column in the table, except those for read-only fields and custom action payload fields (see Upload Changes for Custom Actions). If configured, empty values are skipped. See Omit Empty Values During Upload.

The add-in does not track which columns have been altered, it always sends values for the entire row.

The add-in may send up to four requests to the service at a time for improved performance (on different threads). As a result, the order in which the rows are sent to the server is non-deterministic. It is not guaranteed to be in the same order as in the table.

Upload Changes from a Form-Over-Table Layout

When a user clicks the Upload Changes button for a Form-Over-Table layout, here's what happens:

The add-in checks the form for a pending Update or pending Create operation and the table for pending changes. If there are no pending form or table changes, upload is skipped.
If a Pre-Upload macro is configured, it is invoked. If the macro throws an exception or returns any value other than true, the upload process quits.
When the form has a pending Update or pending Create operation:
For a pending Update:
1. A GET request is sent to the parent's item path.
2. Form field values (for example, data type, required, and so on) are validated; read-only fields are skipped. If validation failures exist, the upload is stopped and no subsequent REST requests are made.
3. When all form fields are valid, a request (PATCH/PUT) is sent to the parent's item path. The payload for this request contains the values of all form fields that have been changed. If configured, empty values are skipped. See Omit Empty Values During Upload.
For a pending Create:
1. Form field values (for example, data type, required, and so on) are validated (see Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel); read-only fields are skipped. If validation failures exist, the upload is stopped and no subsequent REST requests are made.
2. A POST request is sent to the parent's collection path. The payload for this request contains a value (possibly empty) for every editable form field in the form. If configured, empty values are skipped. See Omit Empty Values During Upload.
The child table is not involved in this step.
The results of the form upload are reflected in the status viewer immediately.
If the form upload fails, the add-in stops and does not attempt an upload on the child table.
If the form upload succeeds, the add-in proceeds with the child table as follows:
1. Checks the child table for pending changes, creates, deletes, custom actions, etc.
2. If changes are found, the child table upload proceeds in the same manner as a Table layout upload with one important difference: the child business object's paths are used for each request.
  The add-in ensures that the parameters in the child business object paths are replaced with the correct values during the table upload operation.

If the form and table both have pending changes, there are a minimum of two requests: one for the form and one (or more) for the child table.

The form and child table changes are never sent in a single request. In particular, sending a single request where the payload contains values from the parent form fields along with an array of values from the child table rows is not supported.

Success and failure is noted in the Status column. Errors and warnings are cached and displayed in the Status Viewer when the user clicks on a given item. Here is a sample of the Status Viewer showing two warnings on a successful update:

Upload Changes for Custom Actions

For Oracle business object REST API services that expose custom actions on the item path, the user can mark rows so that the custom action is called on those rows during the upload. For custom actions that require payload fields, table columns that correspond to those fields must be added.

During the upload operation (which may also include update, create, and delete tasks), the add-in performs the following steps for each marked row:

Creates the payload by collecting the cell values for each custom action field column and adding the value to a simple JSON object (member name/value pairs) in the payload. If configured, empty values are skipped. See Omit Empty Values During Upload.
The entire payload body follows this example format:
```
{
  "rejectionReasonCode": "Other: contact approver",
  "notes": "Details with manager"
}
```
- There is no other content in the POST request body (no action name, no array of argument values).
- If any values from these columns are invalid (missing when required, incorrect data type, Excel formula error), the row is omitted from the Upload and marked as failed. See Data validation in Managing Data Using Oracle Visual Builder Add-in for Excel
Prepares the request:
- REST-Framework-Version header added (see Configure the REST-Framework-Version)
- Content-Type header added based on each custom action's Request Media Type property on the Custom Action Editor (available from the Business Object Editor > Custom Actions tab)
Makes the request:
- Sends the POST to the custom action path (POST is the only HTTP method supported for invoking custom actions)
- See the note below about batch requests.
Processes the response:
- For 200 response status, the row is marked as succeeded.
- For 400 response status, the row is marked as failed, and the response payload is parsed for Oracle business object REST API service error content; error details can be seen in the Status Viewer pane.
- A 412 response status indicates that the row was modified by some other agent or user after it was downloaded to the Excel table; such a status is treated as a row-level error
Cell values in action rows are not refreshed. If the custom method logic in the service has altered any values in the row, those changes will not be reflected in the table row until the next download.

If the response payload for the Custom Action request includes a "result" member with a simple value such as a string or number, then the add-in displays that result in the Status Viewer. The response payload for a successful invocation should have a result member in the following format:
```
{
 "result": "<return-value-from-custom-action-method>"
}
```
For example, after invoking the custom action "Reject" on an Expense Report table row, the REST service may return a result such as "result": "Your confirmation code is 26281.". This result is displayed in the Status Viewer, as shown here:

See Executing a Custom Action in the Developing Fusion Web Applications with Oracle Application Development Framework.

For information on custom actions, see Custom Actions.

Upload Changes Using Batch Requests

Oracle Visual Builder Add-in for Excel supports the use of batch requests for uploading changes to Oracle business object REST API services.

During an upload operation, the add-in sends multiple rows per request using a batch API. Rows marked for Update, Create, Delete, and custom actions are included in the batch requests.

For other REST service types, the add-in sends just one row per request.

The add-in uses an algorithm to determine the optimal size of the payload (number of rows) for a batch request as the upload operation proceeds. This is done to optimize performance and avoid timeouts. It works like this:

The add-in sends an initial set of batch requests using 10 rows per batch.
The add-in measures the response times for those initial requests and calculates the approximate number of rows that can be processed in 10 seconds.
Using the new value, the add-in sends one or more batches to the service.
The add-in recalculates the number of rows after each set of batch requests, using the target of 10 seconds per request, until all rows have been processed.

Note:

The target of 10 seconds is not guaranteed, since actual response times vary depending on the REST service business logic, server load, network latency, and so on.

If a batch request contains one or more errors, no changes are made by the service. In that case, a second batch request containing only the rows that succeeded during the first batch request is sent. If the second batch request fails, the add-in falls back to sending one row per request. For more information, see Making Batch Requests.

If a set of changes includes custom actions and those actions have batch enabled, the changes are included in a batch request. However, if one or more rows is marked for a custom action that has batch disabled, the add-in reverts to sending the changes row by row. (See also Batch Mode for Custom Actions.)

If you configure the add-in for parallel requests, it sends up to 4 batch requests in parallel depending on your settings. See Configure Parallel Requests During Upload.

Upload Changes Using Upsert Mode

Oracle business object REST API services support an optional "upsert" mode that you can use when uploading changes to your REST service.

Ensure that your REST services business object supports upsert mode before you enable it.

When upsert mode is enabled, the REST service should behave as follows: first, it examines the incoming payload of a create row request. Next, if the payload contains enough information to match an existing row, the operation updates that existing item. If there is no match, the operation should create a new row. See Updating or Creating Resource Items (Upsert) for more information.

Note:

The exact behavior of upsert mode is determined by the service.

You enable upsert mode by selecting the Use Upsert Mode for Create from the General tab of the Business Object Editor. After you enable it, refresh any layouts in the workbook that are based on that business object.
The Business Object Editor with Use Upsert Mode for Create enabled

When upsert mode is enabled, the add-in sends the HTTP header, Upsert-Mode: true, with POST requests for single-row creates. For batch mode, the add-in sets the operation value for each batch part to "upsert" instead of "create".

Omit Empty Values During Upload

You can configure Oracle Visual Builder Add-in for Excel to omit fields with empty values during upload.

Request payloads for Create and Update operations normally include a value for every column in the table (except those for read-only fields and custom action payload fields) even if the value is empty or null. Null values can potentially cause validation errors.

To avoid these errors, you can configure the add-in to omit null values by selecting the Omit from payload if value is empty check box in the Business Object Field Editor.

When selected, the add-in won't include the field in the payload during upload if the value in the field is empty.

The add-in sets the default value for the Omit from payload if value is empty check box based on the "nullable" property in the OpenAPI v3 (OA3) document for this field. If "nullable" is true, the check box is deselected and null values are uploaded; if "nullable" is false (or the property is missing), then the check box is selected and null values are omitted from the upload.

Note:

If this check box is selected (null values are skipped), you won't be able to change a "non-null" value to a null value for a field in an existing row.

Data Consistency

When a workbook uses an Oracle business object REST API service that supports data consistency verification using an entity tag (Etag) mechanism, the add-in detects and reacts to the following scenario:

User A downloads information from a business object into a table in their integrated workbook.
User B downloads the same information into a table in their integrated workbook, edits it, and uploads changes.
User A then edits the same information (downloaded in Step 1) and uploads the changes.
The add-in provides the service with the necessary information (entity tags) to prevent User A's changes from overwriting those changes made by User B. Instead, when the server detects such a change, its response allows the add-in to display an error message similar to the following for any such rows in the table:
```
This row has been modified by another user. Please download before editing.
```
If you see this message, you'll have to discard your changes by downloading the latest data and then redoing your changes as needed.

For information about the entity tag (ETag) mechanism, see Data Consistency Tasks in Accessing Business Objects Using REST APIs.

If your workbook uses other types of REST services, the last writer wins. So, for the scenario just outlined, User A’s changes in Step 3 will overwrite the changes of User B in Step 2.

Configure Parallel Requests During Upload

You can configure Oracle Visual Builder Add-in for Excel to send multiple requests in parallel during upload to improve performance and shorten the overall time to upload a large set of changes.

The add-in allows you to set from 1 to 4 requests in parallel. If you leave the setting on the default (4), the add-in sends four requests at the same time during upload instead of one request after another. If you choose a value of "1", the add-in sends only one request at a time during upload.

This feature is available for both layout types and can be set independently for each one.

Parallel requests behave differently than batch requests. With a batch request, the add-in sends the changes for multiple rows in each request; With parallel requests, the add-in sends multiple requests at the same time, regardless of whether the request contains one row or multiple rows.

Note:

While the add-in collects batches of rows in "table order" (from top to bottom), the threading used for parallel requests may result in batches of rows arriving at the REST service endpoint "out of order". For example, batch 7 may arrive at the endpoint before batch 6.

Open the Layout Designer for a worksheet and then click the Advanced tab.
Under Upload, drag the Parallel Requests slider to your desired setting.

We recommend setting the value to 4 parallel requests (the default) during upload. However, some services are not compatible with parallel requests due to the their internal business logic. For such services, set this value to "1". Keep in mind that this setting generally results in slower upload performance.

Note:
Take care to test thoroughly in a realistic environment when adjusting this value. Performance may actually degrade if you have too many parallel requests, as may happen if you have multiple users performing uploads at about the same time.