Import Purchase Order - Mods

This feature allows for the importing of Purchase Order (PO) Mod information into the Unanet system.

This import can be used to add new Purchase Order Mods and update existing Purchase Order Mods. This import cannot be used to delete an existing PO Mod, however, using the available purge option, you can instruct the import to first remove any existing Detail records associated with a particular Summary level entry (based on Document Number). Note that only the Mod detail lines will be purged - related Original POs will retain their detail lines. When inserting new entries, you can use the Group Id field to identify multiple input records as belonging to the same PO. Detail lines can only be added or deleted through the import.

While the Purchase Orders are comprised of multiple record types (i.e., Summary and Detail 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).

When updating a PO Mod that is in APPROVED or CLOSED status, the following fields can be updated on this import:

Summary Profile section: Mod_Number, PO_Owner, and Purchaser fields only
Summary Form section: No fields
Summary Other section: All fields
Detail Lines section: No fields (Note: detail lines can be added or deleted for INUSE status, not updated)

When updating a PO Mod that has been changed to INUSE status during the import and has detail lines that are not purged first, the following fields can be updated on this import:

Summary Profile section: All fields on this import
Summary Form section: No fields
Summary Other section: All fields
Detail Lines section: No fields (Note: detail lines can be added or deleted, not updated)

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 the Project Financials family of Unanet licenses.

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

Document_Number

This field identifies the collection of records in the import file that are to be associated with a particular PO. It can be manually specified for a new PO document.

Maximum Length: 15 characters

Note: If you are supplying the PO document number instead of using the system generated number, be aware that the system generated document number format is 10 digit numbers padded with leading zeroes. Using those types of numbers for your manually entered number could cause an error later on where the system tries to generate the same number.

Note: Document numbers are treated as case insensitive. For example, if you import a PO with a manually entered number of "ABC1" and then later import another PO with document number "abc1", this second record will be flagged as a duplicate and will not be added as a new record. Instead, if a PO with number "ABC1" exists, then that record will get updated with the data provided in "abc1".

Group_Id

The value supplied in this field is never saved to the database and is only used for the purposes of grouping records into specific PO 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.

Orig_Document_Number

REQUIRED. The document number of the original, open PO to which this Mod is related.

Mod_Number

This is the PO Mod number and it can be empty. Allowable numbers are 1 to 9999. You can use the special tag !BLANK! to remove an existing entry in this field.

Begin_Date

REQUIRED ON ADD. This is the period of performance begin date.

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

End_Date

REQUIRED ON ADD. This is the period of performance end date.

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

Required_By_Date

The required by date is the date by which the requestor would like to purchase the items. You can use the special tag !BLANK! to remove an existing entry in this field.

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

Purchase_Amount

REQUIRED ON ADD. This field is the purchase amount of this PO modification.

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

Total_Expected_Value

The number supplied can be zero or positive. You can use the special tag !BLANK! to remove an existing entry in this field.

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

Reference

The intended use of this field is to capture any identifying external reference number to associate with the document. You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 25 characters

Description

REQUIRED ON ADD. This is the text description of the PO.

Maximum Length: 128 characters

Document_Date

REQUIRED ON ADD. This is the date the PO was created.

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

Commitment_Date

REQUIRED ON ADD. This is the date on which the purchase amount for approved POs will display in commitment reports (Project Cost Summary report and Project Commitments Details report). This date cannot be before the original PO's commitment date.

The Commitment 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: MM/dd/yyyy See Allowable Date Formats for more options.

Payment_Terms

This field identifies the Payment Terms for this PO.

This value must match an existing Payment Term 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. The master list of available options is defined by the Administrator on the Admin >> Setup >> Payment Terms screen.

VI_Overage

REQUIRED ON ADD. Allowable values are "L" for limit, "D" for don't limit, or "F" for funding cap adjustment.

If VIs are associated with this PO:

if the original PO summary VI Overage is "don't limit", then this field must be "D" or "F".
if the original PO summary VI Overage is "funding cap adjustment", then this field must be "F".

PO_Owner

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

Purchaser

REQUIRED ON ADD. If this field is populated, then the person specified must have a Purchaser role with the PO's owning organization.

Internal_Comments

Internal comments are comments that are applicable inside your organization. You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 2000 characters

External_Comments

External comments are comments that will print on the Purchase Order forms. You can use the special tag !BLANK! to remove an existing entry in this field.

Maximum Length: 2000 characters

Dtl_Line_Type

This is the PO detail line type. Allowed values are "L" for labor, "E" for expense, and "I" for item.

Dtl_PO_Line_Id

This creates a new PO Mod line from an existing approved PO line. Values can be from number 1 to 9999.

Note: The Dtl_Line_Id field can also be added to a PO - Mods export and then imported. These fields have different meanings.

Dtl_PO_Line_Id is related to the original PO lines that are moved down to the PO Mod.
The Dtl_Line_Id will have the original PO line ID if it is related to an original PO line or will have a new line ID for detail lines added in the Mod that are not tied to the original detail lines. Dtl_PO_Line_Id will be blank for new detail lines not tied to the original PO.

Dtl_PR_Line_Id

This creates a new PO line from an existing approved PR line or associates PO to the PR summary. Values can be numeric from 1 to 9999, "PR", or blank. The "PR" value indicates that the PR does not have detail lines and the PO is related to the PR summary. This can also reference an existing PR line of an existing approved PO line referenced in dtl_po_line_id.

Dtl_Description

This is the description for the detail line. If this detail line is created from an original PO line, then this field must match the PO line value.

Maximum Length: 128 characters

Dtl_Begin_Date

REQUIRED for detail lines. This date must be on or after the PO summary begin date. If this detail line is created from a PR line, then this field must match the PR line value.
If there are any posted VIs associated with this detail line, then this date must be on or before the posted VI lines' begin dates.
If there are PO assignments associated with this detail line, then this date must be on or before the PO assignments' begin dates.

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

Dtl_End_Date

REQUIRED for detail lines. This date must be on or before the PO summary end date. If this detail line is created from a PR line, then this field must match the PR line value.
If there are any posted VIs associated with this detail line, then this date must be on or after the posted VI lines' end dates.
If there are PO assignments associated with this detail line, then this date must be on or after the PO assignments' end dates.

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

Dtl_Required_By_Date

The required by date is the date by which the requestor would like to purchase the items.

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

Dtl_Control_VI_Hours_Quantity

Allowable values are "Y" or "N". If this detail line is created from an original PO line, then this field must match the PO line value.

Dtl_Hours

CONDITIONALLY REQUIRED. This is a required field for the labor detail line hours if Dtl_Line_Type is "L". If dtl_control_vi_hours_quantity is "Y", then this value must be greater than zero. If this detail line is created from a PR line, then this field must match the PR line value. Negative values are only allowed if your resulting status is INUSE and dtl_control_vi_hours_quantity is "N".

PR control PO amount rules and VI overage rules apply to this field. See Purchase Order Labor Detail Line for more information about this field.

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

Dtl_Quantity

CONDITIONALLY REQUIRED. This is a required field for the item detail line quantity if Dtl_Line_Type is "I". This field will be rounded based on the precision level of the Unit of Measure (UOM). If dtl_control_vi_hours_quantity is "Y", then this value must be greater than zero. If this detail line is created from a PR line, then this field must match the PR line value. Negative values are only allowed if your resulting status is INUSE and dtl_control_vi_hours_quantity is "N".

PR control PO amount rules and VI overage rules apply to this field. See Purchase Order Item Detail Line for more information about this field.

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

Dtl_Labor_Cost_Rate

CONDITIONALLY REQUIRED. This is a required field for the detail labor cost rate if Dtl_Line_Type is "L". This value can be zero or positive. If this detail line is created from an original PO line, then this field must match the PO line value.

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

Dtl_Item_Cost_Rate

CONDITIONALLY REQUIRED. This is a required field for the detail item cost rate if Dtl_Line_Type is "I". This value can be negative. If this detail line is created from an original PO line, then this field must match the PO line value.

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

Dtl_Amount

This field is used to supply the Amount associated with the Detail line item. This field can be zero, positive, or negative (only allowed if your resulting status is INUSE). If this detail line is created from a PR line, then this field must match the PR line value.
PR control PO amount rules and VI overage rules apply to this field. See Purchase Order detail lines sections for more information about this field.

This field accepts a numeric value up to 18 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. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value. Otherwise, if not created from another line or the corresponding summary/line value is empty, then this field must match the PO summary project if it is populated.

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.
This field must match the summary level project org code, if it is specified.

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. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value. Otherwise, if not created from another line or the corresponding summary/line value is empty, then this field must match the PO summary project if it is populated.

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 for this PO
Those that allow expense reporting (if the detail line type is expense)
Those that allow time reporting (if the detail line type is labor)
Those that allow item reporting (if the detail line type is item)

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.
This field must match the summary level project code, if it is specified.

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. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value. Otherwise, if not created from another line or the corresponding summary/line value is empty, then this field must match the PO summary project if it is populated.

This value must match an existing 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 detail line type is expense, then tasks will be limited to those that allow expense reporting, either at the task level or at the related project level.

If detail line type is labor, then tasks will be limited to those that allow time reporting, either at the task level or at the related project level.

If detail line type is item, then tasks will be limited to those that allow item reporting, either at the task level or at the related project level.

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.

This field must match the summary level task name, if it is specified.

Dtl_Labor_Category

CONDITIONALLY REQUIRED. Detail labor category is required if the project requires a labor category. Labor category must be valid for the project. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

Dtl_Exp_Type

CONDITIONALLY REQUIRED. This is a required field if the detail line type is expense type (dtl_line_type = "E"). This field is not required for labor or item lines (dtl_line_type = "L" or "I").

If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

The expense type supplied must have the following characteristics:

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_Item_Code

CONDITIONALLY REQUIRED. This is a required field for the item detail line item code if Dtl_Line_Type is "I". If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

Item must be valid for the project.

If the cost account of the item's expense type is not the same as the detail account and an account/project type/expense type mapping does not exist, the record will be rejected.

Dtl_UOM

CONDITIONALLY REQUIRED. If Dtl_Item_Code is populated, then this field must be a valid UOM as defined on the Admin Setup Items page. UOM must be valid for the item. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

Dtl_Person_Username

This associates a user to a PO detail line item. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

Dtl_Acct

This field is used to supply the Detail record Account. This value must match an existing account as defined on the Admin >> Setup >> Accounts screen. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

The account supplied must have the following characteristics:

Transaction entry allowed must be enabled
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 dtl_org_code is populated, then dtl_acct must support that org

The account specified must require a project for the following conditions:

if dtl_line_type is "L" for labor
if dtl_line_type is "I" for item
if dtl_line_type is "E" for expense and dtl_proj_org_code or dtl_exp_type is populated

For item detail lines:

If dtl_item_code is populated, then the item expense type cost account must be tied to the specified account, or the item expense type must have a project type tied to the specified account.

For expense detail lines:

If dtl_proj_org_code is populated and dtl_exp_type is empty, then the project must have an expense type whose cost account is tied to the specified account, or the account specified must match at least one cost account tied to any active expense type.

If dtl_proj_org_code is empty and dtl_exp_type is populated, the expense type cost account must be tied to the specified account, or expense type must have a project type tied to the account specified.

If dtl_proj_org_code and dtl_exp_type are populated, the expense type cost account must be tied to the account specified, or the project/task project type must be tied to an expense type whose cost account is tied to the account specified, or the account specified must match at least one cost account tied to any active expense type.

Dtl_Org_Code

This field is used to supply the Detail record Organization. This value must match an existing organization as defined on the Organization >> Profile and Financials tabs. If this detail line is created from a PR or PR line, then this field must match the PR summary or PR line value. If this detail line is created from an original PO line, then this field must match the PO line value.

The organization supplied must have the following characteristics:

The organization must be a Financial Organization
Transaction entry allowed must be enabled
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.

Maximum Length: 25 characters

Dtl_VI_Overage

REQUIRED for detail lines. Allowable values are "L" for limit, "D" for don't limit, or "F" for funding cap adjustment. If this detail line is created from an original PO line, then this field must match the PO line value.
If VIs are associated with this PO:

if the original detail line VI Overage is "don't limit", then this field must be "D" or "F".
if the original detail line VI Overage is "funding cap adjustment", then this field must be "F".

Dtl_Internal_Comments

Internal comments are comments that are applicable inside your organization.

Maximum Length: 2000 characters

Dtl_External_Comments

External comments are comments that will print on the Purchase Order forms.

Maximum Length: 2000 characters

User01

OPTIONALLY REQUIRED. Depending on how you have configured the user defined fields in your system, this field may be a Date, Numeric, String or

value that matches a Pick List entry. This field can be left blank if not configured to be required. You can use the special tag !BLANK! to remove an

existing entry in this field.

Maximum Length: 128 characters

Click here for more information about user defined fields for purchase orders.

User02

See note for User01 above

User03

See note for User01 above

User04

See note for User01 above

User05

See note for User01 above

User06

See note for User01 above

User07

See note for User01 above

User08

See note for User01 above

User09

See note for User01 above

User10

See note for User01 above

User11

See note for User01 above

User12

See note for User01 above

User13

See note for User01 above

User14

See note for User01 above

User15

See note for User01 above

User16

See note for User01 above

User17

See note for User01 above

User18

See note for User01 above

User19

See note for User01 above

User20

See note for User01 above

Approval_Group_Name

Upon processing the first record having a different Document #, the Approval_Group_Name will be imported from that first

record and applied to subsequent records, until the next Document #. Approval Group must be an existing Accounts Payable Approval Group.

Note: If approvals are disabled or an approval group is not provided, submission will be attempted without an approval group, resulting in a status of Submitted if successful.

Note: Check out the Unanet Data Model for specific field data types, lengths, and other attributes. Navigate to Unanet Support and search for "Data Model".

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.

Note: We recommend against using Excel for the PO imports. It tends to truncate the leading zeros from the document number fields.

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

Import Screen

Depending on your property settings the Import Purchase Order - Mods 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.
Purchase Orders	This option instructs the import to completely delete all associated Detail records for each distinct PO being updated (based on Document #). Upon processing the first record having a different Document #, all associated Detail 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 PO 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 initial record.
Resulting Status	This option controls whether each PO 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 validation must pass (otherwise, the document will remain in the INUSE status regardless of this setting): 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 PO (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).

This import can be used to import PO Summary and Details attributes. There should be a separate row in the import file for each detail line.

You cannot add detail lines in the following situations:

if summary level PO assignments exist for the open, original PO or any of its approved Mods
if the open, original PO or any of its approved Mods are used on a vendor invoice and there are no PO lines on the PO or its Mods

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).

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

Custom validations (via custom business rules or stored procedures) are ignored on PO imports.