JET Transaction Line Item .XLSX Export and Import

Upon executing the Export action, users can download the Transaction Line Items with all the attributes currently accessible to them. The export includes all their viewable information from the Line Item Grid as determined by their user profile settings. If a Line Item Grid column is hidden from the end user by a Commerce access rule or a Line Item Grid view filter, that data is exported.

Sample Exported Excel File

The exported Excel filename includes the Transaction ID (bs_id) and date of the export request. For example, 3022225963_2019-12-18.xlsx. Within the .XLSX file, the Excel sheet is given the Transaction ID (bs_id).

The first row of the Excel sheet is a header row that provides the Line Item Grid column labels. Each subsequent row corresponds to a specific line item and the cells in the row contain the values for the labeled columns.

The first column of the Excel sheet is an "Identifier" column. This column provides encoded hierarchical information regarding Model and part relationships for the line items. This information is for internal use and should not be modified.

In the above example, column K is labeled "Hidden From You" with the row values showing "Hidden Value" to indicate this type of hidden data. However, if the end user's participant profile hides an attribute for the current step they are on, that information is not exported.

Oracle CPQ 20A provided the ability to export Line Item Grid data to a Microsoft Excel (.XLSX) file on an end user's local computer. The export included all columns and line items that the end user had access to for a given Transaction. Beginning with Oracle CPQ 21B, Export Line Item action contains an additional option to allow the export of only the Line Item Grid data that is visible to the end user on the current step, taking into account Commerce access rules and Line Item filter rules. Administrators can choose to continue using the current functionality of exporting all the Line Item Grid data or use the new option.

The following example quote provides administrator-defined filters for the end user. When the Line Item Grid filters are selected, only the Line Item Grid rows and columns that meet the filtered criteria are visible to the end user.

If the end user clicks on Export Line Items, the visible line item data is provided in the Excel export output file.

The exported .XLSX file includes all the end user's accessible data from the Transaction's Line Item Grid as determined by their user access setup on the CPQ environment. If a Line Item Grid column is accessible to the user's participant profile in Steps Administration, but is hidden from the view through a Commerce access rule or a Line Item Grid view filter, that data is still exported. For details about how user access setup impacts export .XSLX file content, see Export JET Transaction UI Line Item Grid to .XLSX File Format.

The .XLSX filename includes the Transaction ID (bs_id) and date of the export request. For example, 3022225963_2019-12-18.xlsx. Within the .XLSX file, the sheet name is the Transaction ID (bs_id).

The first row of the Excel sheet is a header row that provides the Line Item Grid column labels. Each subsequent row corresponds to a specific line item and the cells in the row contain the values for the labeled columns.

The first column of the Excel sheet is an "Identifier" column. This column provides encoded hierarchical information regarding Model and part relationships for the line items. This information is for internal use and should not be modified.

In the above example, column K is labeled "Hidden From You" with the row values showing "Hidden Value" to indicate this type of hidden data. However, if the end user's participant profile hides an attribute for the current step they are on, that information is not exported.

The following list defines the supported data modifications and helpful user hints when using this import feature.

• An exported line item data .XLSX file with the Transaction's proper identification information is required for successful import.
• A Model configuration can be imported as an unconfigured Model. Multi-level items, such as mandatory line items or child items, are not supported for import. This is accomplished by downloading the.XLSX file from a quote where the model was added from Configuration, updating that file and then uploading the updated file to the quote. A Reconfigure action must be performed to configure the Model on the Transaction.
• Root-level parts can be added by import into a Transaction's Line Item Grid. When adding a new line item, the identifier column should be empty for that row in the .XLSX file.
• Read-only field modifications are ignored on import.
• Only attributes that the end user has permissions to modify, according to their user profile, can be modified via .XLSX file import.
• Advanced Modify for an Update Line Items action is ignored when executing the Import Line Items action.
• Modifying the sequence number and/or group sequence number of line item data is not supported.
• Excel may automatically adjust string values to be numeric values in the .XLSX file causing the import to fail. For example, in CPQ a menu item label string value of "6" may be automatically adjusted in Excel to be a number value of "6.00". Oracle CPQ doesn't recognize "6.00" as a valid menu label value and fails the import. To avoid these types of issues, we recommend setting the data types and formatting of values in the .XLSX file to match those defined within the Line Item Grid.
• For Multi-Select Menu attribute values, each separate value is expected to be separated by a new line. In Excel this is done by using Alt+Enter (PC) or Option+Enter (Mac) while editing a cell.
• If a row or multiple rows in the exported .XLSX file are not being updated, the end user may delete those rows from the exported spreadsheet, make the necessary modifications to rows in the spreadsheet, and then import that spreadsheet to the Transaction. By doing this, only changes to those rows that are in the spreadsheet are made. However, it is necessary for the end user to ensure no empty rows are included between two populated rows.
• Import uses the current user's language when importing.
• Currency attributes are treated as float attribute types.

• The number of error messages to display in the JET Transaction UI defaults to 5. This default value can be modified by the administrator. Refer to Enable Line Item Grid .XLSX Import for instructions.

Validation of data provided in the .XLSX import file occurs once the Import Line Items action is invoked. Error and warning messages, including a summary message with the total number of errors found, displays at the top of the Transaction page. There are two types of messages that may result from this validation:

• Error – An error message results when the data in the .XLSX file is not able to be processed into the Transaction's Line Item Grid. If any error messages occur, the import fails and none of the line item data is imported. Error messages help the end user identify necessary changes required for successful import of the .XLSX file. Common causes of error messages include:
  • Transaction's Identification metadata in Column 1 is invalid
  • Column labels are incorrect or invalid
  • Cell values are an invalid data type or formatted incorrectly
  • Cell value does not match user's current language or currency
  • The .XLSX file contains an empty cell that clears an attribute value that must be populated. (e.g. Boolean attributes should have a 'true' or 'false' value)
    
  • Empty row between populated rows
  • New part is not found in Parts List
• Warning – A warning message results when a modification in the .XLSX import file is not supported by the import feature. The warning message includes the location of the unsupported modification and lets the end user know that the update was skipped during import. Warning messages do not prevent valid modifications in the .XLSX file from being imported. Common causes of warning messages include:
  • Multi-level Line Items are not supported for import
  • The first worksheet in the .XLSX file is empty

A sales manager wants to change a quantity of one of the line items and add a new line item to a Transaction.

1. Navigate to the Transaction.

2. Click Export to Excel action button to export the line item data to an .XLSX file. The .XLSX file is downloaded and can be saved to the sales manager's local drive.

   

3. Open .XLSX file and modify file to change an existing line item quantity and add the new line item.

   

4. Save the .XLSX file.

5. Navigate to the same Transaction.

   

6. Click Import from Excel action button. The Import Transaction Line Items window displays.

7. Drag and drop the .XLSX file into the file box or click in the box to navigate through the File Manager to upload the .XLSX file.

   

8. Click Import. The file is imported, validated for errors, updated accordingly, and modifications automatically saved to the Transaction.

   

1. Administrator must create an Export Line Items Commerce action to facilitate exporting line item information from the current Transaction.

   Refer to the Adding Commerce Actions procedure and video for detailed instructions.

2. Once the Export Line Items action is created, administrators must add this action to an Action Bar in the JET Transaction Layout for end customer access.

   Refer to Commerce Layout Editor - Actions section for details on adding an action to the JET layout.

To import a Transaction's Line Item Grid .XLSX file, an administrator must perform the following steps:

1. Navigate to the Processes page.

   Admin Home > Commerce and Documents > Process Definition

2. Select Documents from the Navigation drop-down for the applicable process, and click List. The Documents List page opens.
3. Select Actions from the Navigation drop-down for the Transaction, and click List. The Actions List page opens.

4. Click Add. The Admin Action page opens.

   

5. Enter the Label and Variable Name for the Action.
6. Select Import Line Items from the Action Type drop-down.
7. Click Add. The Action is added and the Admin Action tabs appear.

8. Review and update the settings within the Admin Action tabs.

   Refer to Commerce Actions for detailed information.

   • The General tab provides the Max Displayed Errors field. This field designates the number of error messages to display in the JET Transaction UI. The default value is 5.
   • When an existing line item is updated during import, the Modify tab settings are applied to the selected attributes for the Main document and Sub-Document.
   • When a new line item is added during the import, the Initialization tab settings are applied to the line item.

   

9. Click Apply or Update to save the settings.

10. Once the Import Line Items Commerce action is created, administrators must add this action to an Action Bar in the JET Transaction Layout for end customer access.

    Refer to Commerce Layout Editor > Actions section for details on adding an action to the JET layout.