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

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

Using a formatted Excel template that identifies editable fields is more efficient than entering multiple line items manually in the system.

Limitations

The following limitations apply:

• Summary Payment Applications and non-workflow Payment Application BPs are not supported.
• This function is not available for Group by Commit Codes.
• You cannot add new lines unless the workflow step explicitly allows.
• You cannot use the template if SOV Breakdown lines are present in the record.
• The import fails if you change the template structure, tab order, or columns. Do not rename the tabs.
• An invalid line item prevents processing of the other line items in the BP record.
• You cannot export or import line items with breakdown.
• Picker data elements (DEs) cannot be exported, but you can import them for updates.
• Up to five import jobs can be uploaded concurrently for different projects/shells. Only one import job can be uploaded within a project and a Payment Application BP.
• The system limits the total number of imports per record.

Prerequisites

Before you start, ensure that:

• The BP is a classic Payment Application BP configured with Individual Commit Line Items.
• 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, the following permissions are enabled:
  • To modify line items, enable Allow Modification Line Items.
  • To add or delete line items, enable Allow Add/Remove/Copy Line Item.
• At least one line item exists in the record, either added manually or imported earlier.

Procedure

To update line items using Excel:

1. Go to the project/shell tab, and switch to User mode.
2. In the left Navigator, select Logs, and then select Payment Application.
3. In the Payment Application BP log, select and open the target BP record.
4. Select the Line Items tab.
5. From the toolbar, select Export Line Item Data ().
   The system downloads an Excel file that contains all SOV rows with line items.
6. Edit the Excel file.
   Ensure that the worksheet order and column structure match the received template. The Import Data Template must be the second worksheet, and Line Item ID and Ref. must be the first two columns.

   1. Update the editable fields as needed, such as Quantity, Amount This Period, Retainage This Period, and so on.
   2. In the Action column, select one of the following values:
      • To update existing lines or add values to SOV lines that are not currently in the record, select Update.
      • To remove a line item, if the workflow step allows it, select Delete.
      • If you do not want to modify a line item, leave it blank.

      Note:

      • Do not edit read-only columns.
      • Do not change the column order, rename columns, duplicate columns, or remove columns.
      • Do not add rows that do not match record line items unless the BP step explicitly allows it.
   3. Save the updated Excel file.
7. Import the updated template.
   1. Return to the classic Payment Application BP record.
   2. From the toolbar, select Import () , and then select Update From Microsoft Excel File.
   3. Drag the updated Excel file to the import area, and then select Upload.
      The system processes only those lines whose Action column is set to Update or Delete. After processing:

      • The grid view shows added and updated line items, and deleted line items are removed from view.
      • Calculated and formula fields, as well as upper form summaries, are updated.
      • Errors such as invalid references, duplicates, or attempts to modify restricted columns prevent completion. If errors occur, the system prompts you to download a corrected version of the template with error messages in the first column.
8. Review the results.
   1. Review the Audit Log of the record.
   2. If problems occur, review the error prompts or the downloaded Excel file for details.