Excel Format for Creating Line Items for BP Records

Use the More Actions menu to create line items. Select Export Line Item Template, then select Microsoft Excel.

Note:

This option is not available in the bidder portal. A bidder can import or update existing lines in the Requestor package only through CSV import.

The Export Line Item Template option is available only when the integration interface is defined for the BP record. If the BP record does not have a defined integration interface, Unifier does not display the option. The option is available only when the BP record is in Edit mode. The Export Line Item Template option is available whether or not the Upper Form property allows line items. This option is not available in the End step of a Workflow BP.

Excel Template File

When you export the template file, the exported file name has the following format: [BPName]_Line Items_Template

Note:

The file name always uses the source string (BP name) without spaces.

For example: If the BP name is Daily Reports, the template file name will be: DailyReports_Line Items_Template.xlsx.

Excel Template File Worksheets

The exported template file includes the following worksheets:

• Instructions: This worksheet is generated automatically, and you cannot rename it. It contains information about entering BP record and line item data. The content of the worksheet depends on the BP type.
• Imported Data Template: This worksheet is generated automatically, and you cannot rename it. Unifier always sets it as the second worksheet in the template file. It contains the data required to create a BP record and line items. The top section of this worksheet depends on the BP type.

  Note:

  The name of this worksheet is translated according to the user’s language preference.
• <Other User-Defined>:A user may add additional worksheets for reference. Unifier does not process these worksheets during import. An end user can add multiple worksheets.

For Generic Line Item BPs

• The first row includes this static informational text: Line Item Tabs along with the respective detail form fields
• The rows that follow list all line item tabs, including hidden tabs.
• All required fields include the asterisk symbol (*).
• After the line item tab fields, the following rows contain the fields that the user can enter for the BP record or line item. The first row in the data entry section includes this static informational text: Enter data below
• The D row lists all fields from all tabs.
• The Tab Name field is the label of the first column, followed by the detail form fields.

For Cost BPs of Summary Payment Application SOV type

The first row includes this static informational text: Line Item Tabs along with the respective detail form fields

The rows that follow list all line item tabs.

All required fields include the asterisk symbol (*).

After the D row, a CA row appears. This row includes the asterisk symbol (*) for all required fields in the cost allocation details.

Entering Data in the Excel Template File

The following rules apply when you enter data in the Excel template file.

• Use the Import Data Template option to enter data and create records or records with line items.

  Note:

  Review the information on the Instructions worksheet to reduce import errors.
• When you enter data, you can do the following:
  • Enter data in all fields.
  • Use Excel features to copy column values from another worksheet.
  • Use Excel formulas to calculate values in columns.

    Note:

    If you enter a decimal value in the Line No. field of the imported template, Unifier ignores the decimal value and treats the row as a new line item. Unifier creates a new line item after the import. This behavior applies to both Excel and CSV imports.

Creating Line Items by Importing the Excel Template Data

The Import option appears only if you can create BP records. The suboptions within Import appear only if the Upper Form properties allow line items. These suboptions include:

• Data From CSV File
• Data From Microsoft Excel File

To create line items by importing Excel template data:

1. From the toolbar, select Actions, select Import, and then select Data From Microsoft Excel File.
2. Follow the prompts.

   Note:

   Spend-type BPs with configurable manager do not support line item imports.

Uploading

When you upload an Excel file in the Upload Microsoft Excel File window (similar to the CSV window), use the Select File option to select the Excel file, and then select Next to continue with the upload. See below for information about adding attachments.

Validating Microsoft Excel File Content

Unifier performs validations at multiple levels during the file import process. First, it verifies that all required fields for line item creation contain values.

If validation fails, the import fails and the imported file contains error information. After initial validation succeeds, Unifier processes the file further.

Errors and Corrections

If any row contains an error, Unifier stops the import and does not create any BP records or line items. When the import file contains errors, Unifier displays the following message: Import could not be completed. There are errors in this template file. These errors can be seen in the Import Data Template worksheet. Do you want to download the file and fix the errors? Click 'Yes' to download the file and 'No' to upload a different template file.

Adding Attachments

As with CSV file imports, you can add attachments during the import process. In the Add Attachments step, use the Upload Microsoft Excel File window to add attachments to the BP record or line item. The Review section shows uploaded files and files that are waiting to be uploaded.

For example, after you upload the Microsoft Excel file, all attachments listed in the file appear in the Files to be Uploaded section. As you upload each file, its status changes from Waiting for File to Ready to be uploaded.

In the Review section, sort the Status column as needed.

When you select Upload, Unifier uploads the attachments to the BP record or line item, as required.

Validating Added Attachments

When you upload attachments, Unifier performs the following validations:

• If the Attachments column in the Excel file does not contain data, Unifier processes the file without attachments.
• If you specify a name for the Attachments column at the BP record- or line item-level, Unifier ignores the column headings.

To validate a line item-level Excel import:

When you import a line item from a Line Item tab, and Upload documents from My Computer is not selected in the design, you cannot add attachments. The Attachments table displays this message: Uploading of Attachments is not allowed.

If the Excel file references files in the Attachments column and Unifier cannot find those files in the attachment payload, the Attachments table displays this message: Some files are still waiting to be uploaded. Do you want to proceed without uploading those files? Select OK to create the line items without the missing attachments. Select Cancel to return to the Upload Attachments step.

If the attachment payload includes files that do not appear in the Files to be uploaded section of the Excel file, Unifier does not upload those files. In this case, the Status value does not change in the Review section for those files.

After a successful import, Unifier creates the line items and refreshes the line item log with the newly created line items.

For Document Type BPs, each line item can include only one attached file.

Audit Log

Each line item created through Excel import is audited. The audit attributes are:

• Event: <Tab Name>: Line Item created through Microsoft Excel Import
• Action: Add <Short Description>