Import Project Invoice Setup

This feature allows for the importing of project invoice setup information.  Generally speaking, you can use this import to enable and disable invoice generation for a project, as well as provide and maintain the various configuration settings for Lead and Stand-alone projects.  Most fields are ignored if supplied for a Contributor project.

Roles

This screen is available to users having any of the following roles: Administrator and P&R 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

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

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 to which the Invoice settings are being established.  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.

The projects to which you are establishing invoice setup details must have a billable Billing Type (i.e., FP, CP or TM).

3

C

Generate_Invoice

REQUIRED on ADD.  Valid values include Y or N.  This field will determine if the current project will be eligible to participate in the Add Invoices process.

On Add, Y is the only valid value.  Receiving an N during an Add process would result in the entire record being rejected.

On Update, if this value is N, the record will be rejected if any values are provided in any fields other than Project_Org_Code and Project_Code.

If this field is N, the record will be rejected if values are supplied for any fields below this field.

4

D

Invoicing_Option

When configuring a project to be available for invoicing, you will need to define whether it is being invoiced as a stand-alone project or whether it participates in an Invoice Group.  

Available options include:

  • S - Stand-alone  (default value if nothing provided during add)
  • L - Lead
  • C - Contributor

 

If you update a Lead Project to become a Stand-alone or Contributor project, any existing Contributor projects associated with the Lead Project will be modified to become Standalone Projects. If you update a Contributor Project to become a Lead or Stand-alone project, the project will no longer be associated with its former Lead Project.

5

E

Lead_Project_Org_Code

CONDITIONALLY REQUIRED on ADD.  This field is used to indicate the Project Org Code of the Lead project to which this current project is associated as a Contributor. This field is used in conjunction with the Lead_Project_Code.

When the Invoicing_Option is C (Contributor), this field is required and the value supplied must be an existing active Organization Code.  When the Invoicing_Option is other than C, this record will reject if a value is supplied in this field.

If the Use Financial Organizations (unanet.financials.use_financial_orgs) property is enabled:

  • this Lead Org must be defined as a Customer for the current project's Legal Entity

6

F

Lead_Project_Code

CONDITIONALLY REQUIRED on ADD.  This field is used to indicate the Project Code of the Lead project to which this current project is associated as a Contributor. This field is used in conjunction with the Lead_Project_Org_Code.

When the Invoicing_Option is C (Contributor), this field is required and the value supplied must be an existing active Lead Project Code.  When the Invoicing_Option is other than C, this record will reject if a value is supplied in this field.

If the Use Financial Organizations (unanet.financials.use_financial_orgs) property is enabled:

  • the current Project's Owning Organization must be a Financial Org

  • the current Project must belong to the same Legal Entity as the Lead Project

7

G

Primary_Invoice_Format

CONDITIONALLY REQUIRED on ADD.  This is a required field when configuring non-Contributor projects.  For Contributor projects, any value supplied will be ignored.  

The value supplied in this field must exactly match the Name of an existing active format as defined on the Admin >> Setup >> Invoice Formats screen.

Changing the Invoice Format on a project will have no impact to any existing draft or completed invoices and will only affect the layout of future invoices.

8

H

Additional_Invoice_Formats

This is an optional field that can include a comma separated listing of additional invoice formats that should be generated along with the Primary invoice format. This field applies only to non-Contributor projects and will be ignored if this is a Contributor project.
 
The value supplied in this field must exactly match the Name of an existing active format as defined on the Admin >> Setup >> Invoice Formats screen, but cannot include the name already supplied as the Primary_Invoice_Format.
  

Note:

  • You can use the special tag !BLANK! to remove all of the existing additional invoice formats.

  • You can use the special tag !APPEND! to add additional invoice formats to an existing project.  "!APPEND!,format3,format4"  -- this entire string must be enclosed in double quotes (note that if you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually).

  • Similarly, if you would like to remove one or several additional invoice formats from an existing project, you can use the special tag !REMOVE!.   "!REMOVE!,format2,format3"  -- this entire string must be enclosed in double quotes.

 

See the examples below for syntax help.

9

I

Invoice_Number_Format

CONDITIONALLY REQUIRED on ADD.  This field is required when adding a new non-Contributor record, and only applies to non-Contributor projects.  Any value provided will be ignored if this is a Contributor project.

The value supplied in this field must exactly match the Name of an existing active number format as defined on the Admin >> Setup >> Invoice Number Formats screen.

Changing the Invoice Number Format on a project will have no impact to any existing draft or completed invoices and will only affect the layout of future invoices. However, you can modify the Invoice Number on an invoice once it has been generated (via the List Invoices >> Edit Setup screens).

10

J

Payment_Terms

This field is used to identify the Payment Terms to be associated with a project's invoice.

The value supplied in this field must exactly match the Name of an existing active payment term as defined on the Admin >> Setup >> Payment Terms screen.  The days value defined with a particular payment term will be used to calculate an invoices Due Date.

If no value is provided when adding a new entry, this value will be defaulted as follows:

  • Identify the active Customer Profile entry for this Project (considering the project's Project Org and Legal Entity for Project Owning Org), select the active Payment Terms identified in that entry (could be a specific Customer Profile entry or the All Legal Entities entry).
  • If Customer Profile had no Payment Terms for that entry, use System Default Payment Terms (as specified on the Admin >> Setup >> Payment Terms screen)
  • If no System Default Payment Terms designated, the entire record will be rejected.

 

Changing the Payment Terms on a project's invoice setup will have no impact to any existing draft or completed invoices and will only affect the information on future invoices. However, you can modify the Payment Terms on an invoice once it has been generated (via the List Invoices >> Edit Setup screens).

11

K

Bill_To_Contact

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Bill_To_Contact encountered as you work your way up this project org's org tree hierarchy.

When updating an existing record:

  • You can reset to the current default contact using the special tag !DEFAULT!

  • You can remove the contact all together using the special tag !BLANK!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any value other than the default.

This field will be ignored if supplied for a Contributor project.

12

L

Bill_To_Address

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Bill_To_Address encountered as you work your way up this project org's org tree hierarchy.  

  • If no default address has been configured, the record will be rejected as a Bill To address is required for invoicing.

 

When updating an existing record:

  • You can reset to the default address using the special tag !DEFAULT!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any address other than the default.

This field will be ignored if supplied for a Contributor project.

13

M

Ship_To_Contact

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Ship_To_Contact encountered as you work your way up this project org's org tree hierarchy.

When updating an existing record:

  • You can reset to the current default contact using the special tag !DEFAULT!

  • You can remove the contact all together using the special tag !BLANK!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any contact other than the default.

This field will be ignored if supplied for a Contributor project.

14

N

Ship_To_Address

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Ship_To_Address encountered as you work your way up this project org's org tree hierarchy.

When updating an existing record:

  • You can reset to the current default address using the special tag !DEFAULT!

  • You can remove the contact all together using the special tag !BLANK!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any address other than the default.

This field will be ignored if supplied for a Contributor project.

15

O

Remit_To_Contact

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Remit_To_Contact encountered as you work your way up this project org's org tree hierarchy.

When updating an existing record:

  • You can reset to the current default contact using the special tag !DEFAULT!

  • You can remove the contact all together using the special tag !BLANK!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any contact other than the default.

This field will be ignored if supplied for a Contributor project.

16

P

Remit_To_Address

When Adding a new record:

  • The contents of this field are ignored and the project will be configured with the first default Remit_To_Address encountered as you work your way up this project org's org tree hierarchy.  

  • If no default address has been configured, the record will be rejected as a Remit To address is required for invoicing.

 

When updating an existing record:

  • You can reset to the default address using the special tag !DEFAULT!

  • You can leave the field empty in which case no change will occur to any existing value.

 

This import cannot be used to associate any address other than the default.

This field will be ignored if supplied for a Contributor project.

17

Q

Invoice_Delivery_Method

This field is used to indicate the current default method for sending invoices for this project. This setting can be modified but will only impact future generated invoices.  This field will be ignored if supplied for a Contributor project.

Valid values include:

  • E  (for email)
  • P  (for Printing)

 

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

18

R

Email_Message_Template

CONDITIONALLY REQUIRED on ADD.  When the Invoice_Delivery_Method is E, this field is required, otherwise it can optionally be provided.  This field will be ignored if supplied for a Contributor project.  

The value supplied in this field must exactly match the Name of an existing active Email Template of type Invoice Send, as defined on the Admin >> Setup >> Miscellaneous >> Email Messages screen.  

You can use the special tag !BLANK! to remove an existing entry in this field (when it is not required).

19

S

To_Email_List

CONDITIONALLY REQUIRED on ADD.  When the Invoice_Delivery_Method is E, this field is required, otherwise it can optionally be provided.  This field will be ignored if supplied for a Contributor project.  

The value supplied in this field will be one to many valid email addresses separated by commas.  When providing multiple addresses you will need to enclose the entire field in double quotes (note that if you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually).

When updating an existing record, any value you supply in this field will replace all existing values in the database (i.e., there is no append or replace functionality supported with this field).

You can use the special tag !BLANK! to remove an existing entry in this field (when it is not required).

20

T

CC_Email_List

This field is optional and will be ignored if supplied for a Contributor project.  

The value supplied in this field will be one to many valid email addresses separated by commas.  When providing multiple addresses you will need to enclose the entire field in double quotes (note that if you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually).

When updating an existing record, any value you supply in this field will replace all existing values in the database (i.e., there is no append or replace functionality supported with this field).

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

21

U

BCC_Email_List

This field is optional and will be ignored if supplied for a Contributor project.  

The value supplied in this field will be one to many valid email addresses separated by commas.  When providing multiple addresses you will need to enclose the entire field in double quotes (note that if you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually).

When updating an existing record, any value you supply in this field will replace all existing values in the database (i.e., there is no append or replace functionality supported with this field).

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

22

V

Req_Delivery_Receipt

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the emails will be sent with a Delivery Receipt request.  Whether the receiving email service supports this feature or the end user agrees to send the requested receipt will be dependent on the receiving party.

23

W

Req_Read_Receipt

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the emails will be sent with a Read Receipt request.  Whether the receiving email service supports this feature or the end user agrees to send the requested receipt will be dependent on the receiving party.

24

X

Show_Project_Org_Code

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the Project Org Code will precede the Project Code in the header section of the invoice (if the Project Code is being displayed).  

25

Y

Show_Project_Code

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the Project Code will appear in upper right of the header section of the invoice.

26

Z

Show_Project_Title

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the Project Title will appear in upper right of the header section of the invoice.

27

AA

Show_Project_Funded_Value

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, the project's Funded Value (as defined by the Project Manager on the project profile Budget tab) will appear in upper right of the header section of the invoice.

28

AB

Show_Company_Logo

Valid values include Y or N (if not provided, N is the default for a new entry).   If the current project is a 'Contributor', the value supplied in this field will be ignored.

When set to Y, a logo will appear in upper left of the header section of the invoice.  This field works in conjunction with the Company_Logo field (where the desired logo must be identified).

29

AC

Company_Logo

CONDITIONALLY REQUIRED on ADD.  This field is required if the Show_Company_Logo field is Y.  If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
The value supplied in this field must exactly match the Name of an existing active image of type "Financial Forms" as defined on the Admin >> Setup >> Image screen.

30

AD

Show_Contract_Number

Valid values include Y or N (if not provided, N is the default for a new entry).    If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, an additional value will appear in upper right of the header section of the invoice. This field works in conjunction with the Contract_Number field (where the desired contract number must be identified).

31

AE

Contract_Number

CONDITIONALLY REQUIRED on ADD.  This field is required if the Show_Contract_Number field is Y.  If the current project is a 'Contributor', the value supplied in this field will be ignored.

You can use the special tag !BLANK! to remove an existing entry in this field (if the Show_Contract_Number is not Y).
 
You can provide any alpha-numeric value.
   

Maximum Length: 25 characters

32

AF

Show_Order_Number

Valid values include Y or N (if not provided, N is the default for a new entry).    If the current project is a 'Contributor', the value supplied in this field will be ignored.
 
When set to Y, an additional value will appear in upper right of the header section of the invoice.  If the invoice format being used specifies 'Group by Project', this value will also be displayed within the body of the invoice along with this project's details.

33

AG

Order_Number

CONDITIONALLY REQUIRED on ADD.  This field is required if the Show_Order_Number field is Y. It is valid to show the Order Number for a Contributor even if the Lead is configured to not show the Order Number.

You can use the special tag !BLANK! to remove an existing entry in this field (if the Show_Order_Number is N or if this is a 'Contributor' project).  
 
You can provide any alpha-numeric value.
    

Maximum Length: 25 characters

34

AH

Description

The value entered in this field will be copied to the 'Description' area on an invoice (which is displayed just below the invoice header information on the first page of the invoice).  This value can be removed or overwritten on an invoice by invoice basis (while adding or editing a draft invoice).

If the invoice format being used specifies 'Group by Project', this value will also be displayed within the body of the invoice along with this project's details.
 
You can use the special tag !BLANK! to remove an existing entry in this field.
 

Maximum Length: 2000 characters

35

AI

Invoice_Memo

The value entered in this field will be copied to the 'Memo' area on an invoice (which is displayed just above the Remit To information at the end of the invoice).  This value can be removed or overwritten on an invoice by invoice basis (while adding or editing a draft invoice).

If the invoice format being used specifies 'Group by Project', this value will also be displayed within the body of the invoice along with this project's details.   

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

Maximum Length: 2000 characters

36

AJ

Proj_Reminder_Enabled

Valid values include "Y" or "N". Defaults to "N" if not provided. This must be "Y" if you want reminder emails to be sent.

37

AK

Proj_Reminder_Frequency

CONDITIONALLY REQUIRED. If Proj_Reminder_Enabled is "Y", then this field is required. It must be an integer greater than or equal to zero.

38

AL

Proj_Reminder_Email_Message_Template

CONDITIONALLY REQUIRED. If Proj_Reminder_Enabled is "Y", then this field is required. This must be an existing template name with a message type of "Unpaid Invoice Reminder".

 

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 Project Invoice Setup 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.

 

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

 

Related Topics