Import Vendor Payment

This feature allows for the importing of Vendor Payment information into the Unanet system.

While this import is primarily used to insert new entries, it can also be used for limited updating as well. Existing entries for updating will be identified by a value in the Document Number field, whereas new entries will have no value in that field. This import cannot be used to delete an existing Vendor Payment document, however, using the available purge option, you can instruct the import to first remove any existing Detail and Paid Document records associated with a particular Summary level entry (based on Document Number). When inserting new entries, you can use the Group Id field to identify multiple input records as belonging to the same Vendor Payment document.

While the Vendor Payments are comprised of multiple record types (i.e., Summary, Detail, and Paid Document records), importing these records is accomplished via a single record layout. As such, the fields related to the Summary information are ignored on all but the first record (for each distinct Group Id encountered). Further, on any single import record, you can include either Detail or Paid Document values, but not both.

See Processing Rules and field descriptions below for additional information about insert and update restrictions.

Roles

This screen is available to users having any of the following roles: Administrator.

License

This feature is available with any Unanet edition.

Performance Tip Note: Depending on the number of users in your installation and other factors such as the overall performance of your platform, the activity of importing one or many files may take up to several minutes. Should this be the case with your installation, you may want to consider running these processes at non-peak times to minimize database contention.

Topics covered on this help page include:

Import File Record Layout (field by field descriptions)
Import File Format (note about csv layout and option to import a subset of columns using header values)
Excel Template (spreadsheet template containing column headings)
Import Screen (invoking the import via the user interface)
Processing Rules (limitations and rules followed)

Import File Record Layout

IMPORTANT: Note that there are multiple places within this documentation that say you MUST use double quotes for the strings in your import. If you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually. Manually adding double quotes within Excel would result in multiple sets of double quotes in the resulting .csv file and thus cause an error (should you attempt to import that resulting file). Please see Excel Tips for more information.

Field Header Name

Required/Comments

Legal_Entity_Org_Code

REQUIRED ON ADD. This code uniquely identifies the Legal Entity Organization to which the Vendor Payment belongs. This value must match an existing Legal Entity Organization Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

This value cannot be changed on an existing Vendor Payment.

Document_Number

REQUIRED ON UPDATE. When attempting to update an existing entry, this field is required and must match an existing Vendor Payment document number (and that document must be in either the INUSE or SUBMITTED status). This is the field that is used to identify the collection of records in the import file that are to be associated with a particular vendor payment document (during an update process).

Group_Id

The value supplied in the field is never saved to the database and is only used for the purposes of grouping records into specific Vendor Payment documents.

When both a Group_Id and Document_Number are provided, the Group_Id field will be ignored.
All records having the same Group_Id will be grouped into a single document.
All records having no Group_Id supplied (and no Document_Number supplied) will be grouped into a single document.

Document_Date

The Document Date is the date to be associated with the Financial Document. If not provided, it will default to the current system date (when creating a new entry).

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Post_Date

REQUIRED ON ADD. The Post Date is the date to which you desire to have a financial document posted. This date reflects the date to which various accounts are updated and for which the business transaction will appear in various financial reports.

The Post Date supplied must fall within a Fiscal Period that is open for transaction entry (unless the "Allow changes to closed fiscal periods" option has been selected).

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Vendor_Org_Code

REQUIRED ON ADD. This field is used to supply the Vendor Organization to which the vendor payment transaction is to be posted. This value must match an existing organization defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

The organization supplied must have the following characteristics:

The organization must be active
The organization must be defined as an active Vendor Profile entry:
- either specifically for the Legal Entity of the Vendor Payment being entered, or
- an "All Legal Entities" entry

Payment_Amount

This field is used to supply the total amount of the payment. The number supplied can be zero, positive or negative and will be defaulted to 0 if no value is supplied.

Prior to submitting a Vendor Payment, the sum of the amounts applied in the Paid Documents and Details associated with this Vendor Payment cannot be:

greater than a positive Payment Amount
less than a negative Payment Amount

This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right.

Payment_Method

REQUIRED ON ADD. This field specifies the payment method associated with the Vendor Payment. The available values for this field are managed on the Admin >> Setup >> Payment Method screen. This value must match an existing payment method defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

Only those values that are designated as Active and Allowed on Vendor Payments will be accepted.

Bank_Account

REQUIRED ON ADD. The Bank Account field is used to indicate the bank account to which a Payment is to be posted (i.e., from which the payment is issued or to which it is deposited). The list of available options are defined on the Admin >> Setup >> Bank Account screen. This value must match an existing bank account d defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

The bank account supplied must have the following characteristics:

must be an active bank account
must be associated with an organization that is within the Legal Entity provided
must have a Bank Account Type of either 'Bank' or 'Other'

Payee

This optional field is used to identify the Payee name(s) that will be printed on a Vendor Payment check. When no Payee is defined, the Vendor Organization Name will be used.

You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 128 characters

Check_Reference_Number

CONDITIONALLY REQUIRED. This field is intended to capture any identifying external reference number or text to associate with the document.

If the supplied Payment_Method is of type 'Manual Check', this value is required.
If the supplied Payment_Method is of type 'System Check', any value supplied in this field will be ignored, as the check generation process will supply a value for this attribute.

You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 50 characters

Description

This optional field is intended to capture any identifying code or value from an external source that you would like to enter and track along with the document.

You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 128 characters

Comments

This optional field can be used to record a comment for the current financial document.

You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 2000 characters

Dtl_Acct

CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert a Vendor Payment Detail entry. This field is used to supply the Detail record Account to which the vendor payment transaction is to be posted. This value must match an existing account as defined on the Admin >> Setup >> Accounts screen. This value is required if the current record is intended to insert a Vendor Payment Detail entry.

The account supplied must have the following characteristics:

The account must be active
Transaction entry allowed must be enabled
If specified on the account setup, Transaction Entry Allowed - Begin Date must be:
- <= Post_Date
- <= Reversing_Date
If specified on the account setup, Transaction Entry Allowed - End Date must be:
- >= Post_Date
- >= Reversing_Date
If the account has been configured to be available for only a 'Select' number of Legal Entities, at least one of the specified organizations must have a legal entity equal the supplied Legal Entity

If the Account provided does not require project related details for the transaction, the record will be rejected if any values are supplied in the Dtl_Proj_Org_Code, Dtl_Proj_Code, Dtl_Task_Name, Dtl_Proj_Type, or Dtl_Exp_Type fields.

Dtl_Org_Code

CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert a Vendor Payment Detail entry. This field is used to supply the Detail record Organization to which the vendor payment transaction is to be posted. This value must match an existing organization as defined on the Organization >> Profile and Financials tabs.

The organization supplied must have the following characteristics:

The organization must be a Financial Organization
The organization must be active
Transaction entry allowed must be enabled
If specified on the organization, Transaction Entry Allowed - Begin Date must be:
- <= Post_Date
- <= Reversing_Date
If specified on the organization, Transaction Entry Allowed - End Date must be:
- >= Post_Date
- >= Reversing_Date
The organization must belong to the same legal entity as the supplied legal entity
The organization must be valid for the supplied account (as specified on the Admin >> Setup >> Account screen -- where 'All' or a 'Selected' number of organizations are defined as available for charging).

Dtl_Reference

This is an optional field intended to capture any identifying external reference number or text to associate with the Detail line item.

If not provided, this will default to the value provided in the Reference field (from the Summary area of the record).

Maximum Length: 50 characters

Dtl_Description

This is an optional field intended to capture any identifying code or value from an external source that you would like to enter and track along with the Detail line item.

If not provided, this will default to the value provided in the Description field (from the Summary area of the record).

Maximum Length: 128 characters

Dtl_Transaction_Date

This date is the date to be associated with the Detail line item.

If not provided, this will default to the value provided in the Document_Date field (from the Summary area of the record).

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Dtl_Amount

CONDITIONALLY REQUIRED. This field is used to supply the Amount associated with the Detail line item. This field can be zero, positive or negative.

This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right.

Dtl_Proj_Org_Code

CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field.

This is the Project Organization code associated with the Dtl_Proj_Code supplied in the next field. The combination of these two values must match an existing Project Org / Project Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field.

Dtl_Proj_Code

CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field.

This is the Project Code associated with the Dtl_Proj_Org_Code supplied in the previous field. The combination of these two values must match an existing Project Org / Project Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected.

Valid projects will be limited to:

Those that have an Owning Organization that belongs to the same Legal Entity as the Legal Entity defined on the Financial Document
Those that are associated with a Project Status having the Active indicator enabled

If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field.

Dtl_Task_Name

CONDITIONALLY REQUIRED. The value for this field will be required if the project (from above) is set to require task level expense reporting.

This value must match an existing active Task Name value in your system (belonging to the Dtl_Proj_Code listed above). If the Task Name you are trying to import does not already exist in the database, the entire record will be rejected.

If the Task has an Owning Organization defined, it must belong to the same Legal Entity as the Project's Legal Entity.

If a task is not a top-level task (e.g., it has subtasks), you must include a comma separated list of each task starting at the top level down. The following is an example of a valid task tree and how you would reflect each task using this import.

Task Tree Access String

1. Car Repair	"Car Repair"
1.1 Tires	"Car Repair,Tires"
1.2 Paint	"Car Repair,Paint"
1.2.1 Prep work	"Car Repair,Paint,Prep work"
1.2.2 Patching	"Car Repair,Paint,Patching"
1.3 Engine	"Car Repair,Engine"
1.3.1 Tune-up	"Car Repair,Engine,Tune-up"
1.3.1 Rebuild	"Car Repair,Engine,Rebuild"

See the Excel Tips regarding the use of double quotes and more.

If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field.

Dtl_Proj_Type

The project type supplied must have the following characteristics:

Must be active.
Must match an existing project type in the system.
If not provided, default to the task's project type (if task is provided and has a project type, otherwise default to project's project type).

If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field.

Dtl_Exp_Type

The expense type supplied must have the following characteristics:

Expense Types having the Enabled indicator checked (as defined on the Admin >> Setup >> Expense Types >> Summary tab).
Expense Type must be a valid choice for the project (as defined on the Project >> Expense Types screen).
Expense Types having the financial document line item's account specified in:

- the expense type's Cost Account field (as defined on the Admin >> Setup >> Expense Types >> Summary tab), or
- the expense type / project type mapping --for the financial document line item expense type and project type values (as defined on the Admin >> Setup >> Expense Types >> Accounts >> Cost Accounts screen)

If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field.

Dtl_Person_Username

This field optionally associates a named user to a financial document detail line item. Any active user can be associated with a financial document detail line item.

Paid_Doc_Type

CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert or update a Vendor Payment Paid Document entry.

Valid values accepted include: VI or VP (for vendor invoice or vendor payment)

Paid_Doc_Number

CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert or update a Vendor Payment Paid Document entry. The combination of Paid_Doc_Type and Paid_Doc_Number will uniquely identify the desired financial document.

The document number supplied must have the following characteristics:

Must identify an existing posted Vendor Invoice or Vendor Payment document number
The identified Paid Document must belong to the same Vendor and same Legal Entity as the supplied Summary level Legal Entity

Paid_Payment_Amount

This field is used to supply the payment amount for a particular Paid Document entry. The number supplied can be zero, positive or negative and will be defaulted to 0 if no value is supplied.

On any single record, the Paid_Payment_Amount and Paid_Discount_Amount values cannot both be zero.

This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right.

Paid_Discount_Amount

This field is used to supply the discount amount for a particular Paid Document entry. The number supplied can be zero, positive or negative and will be defaulted to 0 if no value is supplied.

On any single record, the Paid_Payment_Amount and Paid_Discount_Amount values cannot both be zero.

If a value is supplied in this field and the Paid_Doc_Type is VP, the entire record will be rejected.

This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right.

Note: Check out the Unanet Data Model for specific field data types, lengths, and other attributes.

Import File Format

The file to import must be saved in a comma delimited format. The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.

Importing a Sub-Set of Columns using a Field Header Record

If you are not using the default column layout sequence as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicate what data is contained in each column). The specific Header Names for each column are listed in the table above.

Excel Template

You can create the comma delimited import file with any number of tools. For those interested in using an Excel spreadsheet to create the file, you can download an Excel Template with predefined headers.

See the Excel Tips regarding the use of double quotes and more.

Import Screen

Depending on your property settings the Import Vendor Payment screen may look like:

Field Descriptions:

Field	Description
Type of Import	Select the appropriate import type from the list of available types of imports.
File to Import	Enter (or browse for) the file that you would like to import.
Output Options	Specify the level of detail included in the output.
Vendor Payments	This option instructs the import to completely delete all associated Detail and Paid Document records for each distinct Vendor Payment being updated (based on Document #). Upon processing the first record having a different Document #, all associated Detail and Paid Document entries will be deleted, and then subsequent records having that same Document # will be processed and inserted. Essentially, this is a means to replace all Vendor Payment details. Note that the Summary portion of the document will remain and its values would have been updated based on the values included on that record. When not checking this option (thus attempting to append additional entries), any imported Paid Document entries will update any matching existing saved entries.
Resulting Status	This option controls whether each Vendor Payment will end up with a status of INUSE or SUBMITTED. In order for the document to be placed in the SUBMITTED status, the following additional validations must pass (otherwise, the document will remain in the INUSE status regardless of this setting): Sum of all Credit Amounts must match the sum of all Debit amounts. None of the records associated with the document being imported encountered any errors. The default Resulting Status will be INUSE.
Fiscal Period	This option controls whether or not entries processed in the current file can make changes to a closed fiscal period.

The import process is triggered by pressing the Import button. All Warnings and Error messages are written to the screen and can then be saved if desired. While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database. The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the Import results page. The rejected record file name will be the import name (from the Type of Import drop-down on the Admin>> Import page) , concatenated with a space and "Errors.csv" (e.g., Item - Master Errors.csv, Alternate.csv, etc.). The file will be placed in the Unanet temporary directory (which is defined by the Temp Directory (Fully Qualified Directory Name) (unanet.temp_directory) Unanet property). You can also click on the Download the error file link at the bottom of the Import results page (sample image follows).

Processing Rules

When importing a number of records associated with the same Vendor Payment document (i.e., having the same Group Id on new records, or having the same Document Number on existing records), the Summary values from the first record encountered will be used and Summary values from subsequent records will be ignored (for that group / document).

Prior to submitting a Vendor Payment, the sum of the amounts applied in the Paid Documents and Details associated with this Vendor Payment cannot be:

greater than a positive Payment Amount
less than a negative Payment Amount

This import can be used to import Vendor Payment Summary, Details, and Paid Document attributes, but on any single line, only Details or Paid Document attributes can be provided. As such, if any single import record has values in any Dtl_* fields and the Paid_Doc_Type field, the record will be rejected.

When you are not using the Purge First option (thus attempting to append additional entries), any imported Paid Document entries will update any matching existing saved entries.

Posted, Voided, and Voiding documents cannot be modified. Updating a document in the Submitted status will first change its status to Inuse (although, using the Resulting Status option, it can be returned to the Submitted status).

Application document for an existing vendor payment cannot be imported.

If a record encounters an error, a message will be displayed but the import will continue and subsequent records for that same Vendor Payment will be processed. Note that should an error happen, the resulting status for the entire Vendor Payment will always be INUSE, regardless of the screen selection.

Validations

While you can save a financial document violating certain rules, you will not be permitted to submit or post the document until they are resolved. If any of the validations fail for any row on the document, the entire document cannot be posted.

Standard financial document validations:
- Only System Administrators can post into closed Fiscal Periods / Years
- When posting a recurring document and the document is not the last in the series, the post process will attempt to automatically create the next occurrence into the next fiscal period. The post will be prevented if the next fiscal period does not exist

Standard financial document validations for Summary level information:
- Legal Entity must be a financial organization
- Legal Entity must still be defined as Legal Entity at time of posting

Standard financial document validations for each Detail row:

The row's Account must be active
Transaction Entry must be allowed for the Account
Document's Post Date must be within the allowed transaction date range for Account
Account must still be valid for the Organization
Organization must be active
Organization must belong to this document's Legal Entity
Transaction entry must be allowed for Organization
Document's Post Date must be within the allowed transaction date range for Organization
Project must be provided if Account requires project
Project must not be provided if Account does not require project
Project provided must be active
Project's Owning Organization must belong to this document's Legal Entity
Task must be provided if project is defined as "requires tasks for expense reporting"
Task provided must be active
When Project provided, Project Type must be provided
When Project provided, Expense Type must be provided
Expense Type must be valid for the Account
Expense Type must be valid for the project
Item must be valid for the project