Import Invoice

This feature allows for the importing of invoices, to add additional items to a new or existing invoice. The invoice will be created if it is not an existing draft invoice. If the existing invoice is in draft status, the additional items will be added, otherwise the import will fail. This import is located under the Financials - Invoicing & Posting submenu. You can choose "Draft" or "Completed" as the resulting status, which will apply to all invoices on the import. This import does not support updating or deleting additional items. Also note, this feature does not allow command line import.

Note: Only one draft invoice is allowed per project. If multiple invoices need to be imported for one project, you must choose Completed Status on the import screen. Furthermore, when Completed Status is chosen and there are multiple additional items to import on a single invoice, additional items must be grouped together by invoice number.

For example, the import file provides 3 additional items for a single invoice.  Two are on rows 3 and 4 of the import file and the other one is on row 10. Rows 3 and 4 will result in the creation of a single completed invoice with two additional items.  Row 10 will result in an error. 

Roles

This screen is available to users having any of the following roles: Administrator,  P&R Administrator. Billing Manager, and Billing Manager Alternate.

Billing Manager and Billing Manager Alternate must have access to the primary project for which the invoice is being created.

License

This feature is available with the Project Portfolio license.

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.

 

You may also be interested in:

 

Topics covered on this help page include:


Import File Record Layout

 

 

Field Header Name

Required/Comments

1

A

Project_Org_Code

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

2

B

Project_Code

ALWAYS REQUIRED.  This Project Code identifies the project that is associated to the invoice. This value must match an existing Project Code value defined in your system.  If the Project you are trying to import does not already exist in the database, the entire record will be rejected. If the invoice option for the project is not Standalone or Lead, then an error will occur.  If the project has Lead and Contributor projects, the Lead project code should be used in this field.

3

C

Invoice_Number

ALWAYS REQUIRED. If the invoice number does not exist, then a new invoice will be created.

Note: When Completed Status is chosen and there are multiple additional items to import on a single invoice, additional items must be grouped together by invoice number.

For example, the import file provides 3 additional items for a single invoice.  Two are on rows 3 and 4 of the import file and the other one is on row 10. Rows 3 and 4 will result in the creation of a single completed invoice with two additional items.  Row 10 will result in an error. 

Note: Invoice numbers are treated as case insensitive. For example, if you import an invoice with a manually entered number of "ABC1" and then later import another invoice with invoice number "abc1", this second record will be flagged as a duplicate and will be rejected.

4

D

Invoice_Date

REQUIRED on ADD. If the invoice date doesn't match the existing value on the draft invoice, then the provided invoice date will override the draft date.

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

5

E

Billing_Through_Date

REQUIRED on ADD. If the billing through date doesn't match the existing value on the draft invoice, then the provided billing through date will override the draft date.  

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

6

F

Post_Date

REQUIRED on ADD.  A post date in the closed fiscal period will cause an error. If the post date doesn't match the existing value on the draft invoice, then the provided post date will override the draft date. If multiple additional items are provided for the same invoice number, the first value will be used.

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

7

G

Description

If a description is provided in multiple rows for the same invoice number, the first value will be used. You can use the special tag !BLANK! to remove an existing entry in this field. This field will default to the lead project's description if not provided.

Maximum Length: 2000 characters

8

H

Memo

If a memo is provided in multiple rows for the same invoice number, the first value will be used. You can only update the lead project's memo. You can use the special tag !BLANK! to remove an existing entry in this field. This field will default to the lead project's memo if not provided.

Maximum Length: 2000 characters

9

I

Addl_Item_Type

ALWAYS REQUIRED. Item must exist and be active in Unanet first, otherwise an error will occur.

10

J

Addl_Item_Project_Org_Code

ALWAYS REQUIRED. This value must match an existing Project Organization Code value defined in your system. The additional item project must either match the invoice project or be set up as a contributor project to the invoice project.

11

K

Addl_Item_Project_Code

ALWAYS REQUIRED. This value must match an existing Project Code value defined in your system.

12

L

Addl_Item_Task_Name

ALWAYS REQUIRED. This field is required if the project is managed at the task level, otherwise the field can be blank. The task must exist in the system.

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.

13

M

Addl_Item_Description

ALWAYS REQUIRED. This is the additional item description field.

Maximum Length: 128 characters

14

N

Addl_Item_Amount

ALWAYS REQUIRED. This is the additional item amount field. It can be zero, positive, or negative.

Format  NUMBER (18,2) -- for example "01234567890.12 ".

15

O

Billing_Period_From_Date

This date will be displayed on invoices with a format that has Show Billing Period Dates Instead of Billing Through Date selected. If provided, it must be less than or equal to Billing_Through_Date.

You can use the special tag !BLANK! to remove an existing entry in this field when editing an invoice (not valid when adding an invoice).

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

 

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.

Examples:


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  Invoice screen may look like:

 

Field Descriptions:

Field

Description

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.

Resulting Status

Choose 'Draft' or 'Completed' as the resulting status. This status will apply to all invoices in the import.

 

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 Invoice user feedback screen.  The rejected record file name will be Invoice Errors.csv. 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).

 

Related Topics