Using Microsoft Excel to Update Line-Items in Classic Payment Application BP Records

You can export existing line-item data from a classic Payment Applications business process (BP) record from Unifier, make updates in Microsoft Excel, and then import the changes back into the system.

By leveraging a formatted Excel template that identifies which fields are editable, this procedure is more efficient for editing multiple line items than compared to a manual in-system entry.

Limitations

The following limitations apply:

• Summary Payment Applications and non-workflow Payment Application BPs are not supported.
• This functionality is not available for Group by Commit Codes.
• You cannot add new lines unless the workflow step explicitly allows.
• The template cannot be used if SOV Breakdown lines are present in the record.
• The import will fail if you change the template structure, tab order, or columns. Do not rename the tabs.
• An erroneous line item will impede the processing of all other line items in the BP record.
• You cannot export or import line items with breakdown.
• Picker data elements (DEs) cannot be exported, but can be imported for updates.
• A maximum of 5 import jobs can be uploaded concurrently for different projects/shells, whereas only 1 import job can be uploaded within a project and a Payment Application BP
• Total imports per record are subject to system limits.

Prerequisites

• Only classic Payment Application BPs set up with Individual Commit Line Items are supported.
• The Payment Application BP is associated with a workflow.
• You have the required permissions to update, export, and import Payment Application BP records.
• For the current workflow step, you have the following permissions :
  • To modify line items, Allow Modification line items is enabled.
  • To add or delete line items, Allow Add/Remove/Copy line item is enabled.
• At least one line item exists in the record (added manually or previously imported).

Procedure

To update line items in a classic Payment Application business record using Microsoft Excel:

1. Export line-item data from a Payment Applications BP record.
   1. Go to the project or shell, and switch to User mode. b.
   2. In the left Navigator, select Logs, and then select the Payment Applications BP sub-node.

      If you have a customized left Navigator, navigate as required to access the relevant Payment Applications BP Record.

   3. In the Payment Applications BP log (right pane), select the targeted Payment Applications BP record.
   4. Open the selected Payment Applications BP record, and select the Line Items tab.
   5. To download the Microsoft Excel file with all SOV rows containing line items, from the toolbar, select Export Line Item Data ().
2. Edit the Excel file.
   Ensure the template’s worksheet order and column structure matches the format as received. The Import Data Template must be the second worksheet, with Line Item ID, and Ref., as the first two columns.

   1. In the Excel file, update editable fields as needed, such as Quantity, Amount This Period, Retainage This Period, and so on.

      Note:

      • Do not edit the read-only columns (grey columns).
      • Do not modify column order, rename, duplicate, or remove columns.
      • Do not add new rows that do not match record line items unless the BP step explicitly allows this.
   2. In the Action column, select one of the following values:
      • To update existing lines or add value to SOV lines that are not currently in the record, select Update.
      • To remove a line item (if allowed by step permissions), select Delete.
      • If you do not wish to modify a line item, leave it blank.
   3. After finalizing all entries, save the updated Excel file.
3. Import the updated template.
   1. Return to the classic Payment Applications BP record (in step 1c).
   2. From the toolbar, select Import () , and then select Update From Microsoft Excel File .
   3. Drag and drop the Excel file you updated in the previous step, and then select Upload.
      The system processes only those lines whose Action column is set to Update or Delete. After processing:

      • The grid view displays added and updated line items, with any deleted line items removed from view.
      • Calculated/formula fields and upper form summaries are updated accordingly.
      • Any errors (such as invalid references, duplicates, or attempt to modify restricted columns) will prevent completion. If errors are detected, you will be prompted to download a corrected version of your template with error messages in the first column.
4. After import, perform the following checks:
   1. Review the Audit Log of the record.
   2. If problems occur, review error prompts or refer to the re-downloaded Excel sheet for specifics.