TE EXPENSE REPORT PREPROCESSOR

Use this preprocessor to import expense reports from an ASCII file and create vouchers. You can import both open payables and paid vouchers in this screen. You can view and edit the transactions loaded by this preprocessor in the Enter A/P Vouchers screen in Costpoint Accounts Payable. In addition, you can also use the Print Voucher Edit report function to review them.

When the system processes the import file, it sorts the incoming Time & Expense records primarily by expense report record and secondarily by expense report line. It will format all numbers and dates in the file in the U.S. format, regardless of the currency used.

The application then creates vouchers by grouping the expense report information. Once it has grouped and validated the vouchers, the Costpoint upload process adds permanent voucher numbers in accordance with the setting selected.

Each time you use this screen, the system automatically creates and prints edit reports, error reports, and files. As a result, you will have printed copies of both the vouchers created and the error records.

This application is separately licensed for the current release of Costpoint, and an Ongoing Support Plan (OSP) is available for routine maintenance and upgrades. Customization is priced separately.

You must complete the TE Suspense Setup screen before using this preprocessor. If you have multiple companies in the same database, you must have a separate upload file for each company.

Only one user at a time can run this application. This preprocessor accepts pay currencies, pay amounts, and/or pay exchange rate dates.

The following fields are validated by company: Vendor, Vendor Employee, 1099 Flag, Pay Vendor, Pay Vendor Address Code, Project, Project Abbrev Code, Project Account Abbrev Code, Organization, Organization Abbrev Code, Voucher/Fiscal Year, Account Entry Group, Subperiod Journal Status, AP Account, Cash Account, Sales/Use Tax Code, Reference ID, General Labor Category, and Project Labor Category.

The following fields are controlled by company: Suspense Values, Voucher Settings, Vendor Settings, and Multicurrency Settings.

Input File

Use this group box to choose the path and file names of your import file as well as the file delimiter.

Select

Use this pushbutton to identify the path and file names of your import file. If you select this pushbutton, an Open File dialog box will display and you can browse for your file.

File Delimiter

Use this drop-down box to select a file delimiter. The valid values are: "Comma," "Tab," "Semicolon," and "Tilde." The default selection is "Comma."

Note (International users): The data can contain non-English characters, provided they are in the ASCII character set. Before creating an ASCII text (.TXT) file, be sure that your keyboard language is set to "EN" (United States English) to prevent the application from generating errors.

We recommend the use of .TXT and .CSV file-naming conventions.

Accounting Period

Use this group box to choose the fiscal year, period, and subperiod to use as the default posting period for all uploaded vouchers. These will not override values in the input file, but will be used if the fiscal year, period, and/or subperiod in the input file are blank.

Voucher Numbering Method

Use this group box to select a method for numbering vouchers:

Use Last System Voucher Number

Select this radio button to sequentially assign unique voucher numbers when importing to the Costpoint tables.

The application uses the Last System Voucher Number from the A/P Voucher Settings screen in Costpoint Accounts Payable to determine the next voucher number. This selection is the default.

Note: If you select this radio button, the Costpoint system-wide setting for Voucher Numbering method in the A/P Voucher Settings screen in Costpoint Accounts Payable must be set to System, and the Last System Voucher Number field must be greater than or equal to zero.

Supply Beginning Voucher Number

Select this radio button to enter a beginning voucher number (up to nine digits) in the data field directly below this radio button.

Unique voucher numbers are assigned sequentially when you import, starting with the beginning voucher number you entered. A validation will occur before the vouchers are imported into Costpoint to ensure that the voucher number/fiscal year combination does not already exist.

Warning: Assigning voucher numbers when importing can take significantly longer than using the voucher numbers provided in the input file. The length of time required for the import also depends upon the number of vouchers being uploaded.

Create a Separate Voucher of Each

Use this group box to specify the criteria for creating separate vouchers.

Vendor/Expense Report

Select this radio button to create a voucher for each vendor and expense report header using the associated detail. Depending on the transaction or pay currencies selected, you can create more than one voucher. This selection is the default.

Vendor/Expense Report/Line

Select this radio button to create a voucher for each vendor, expense header, and line detail. If you select this option, an expense report with five detail lines will create five separate vouchers (one per line).

Defaults

Use this group box to specify the rate group, freeze rates, or to mark vouchers as approved.

Rate Group

Use this drop-down box to select the rate group that will be used to retrieve groups of exchange rates. These groups of exchange rates will use rate information from only one source. The application uses these rates to compute functional amounts using the transactional amounts in the input file.

You originally created rate groups in the Maintain Exchange Rate Groups screen in Costpoint Multicurrency.

Freeze Rates

Select this checkbox to freeze the rate on the voucher. If you freeze the rate, the transactional and pay rates cannot be changed.

Mark Vouchers as Approved

Select this checkbox to approve all vouchers that are created.

Upload File Layout /Recommended Input File

Header Format for Upload Files

Business Name	Field Type/ Length	Required/ Optional	Comments	Format Value(s)
1 - External System Code	Character 1	Required	Required for Time & Expense use only
2 - External Batch ID	Character 10	Required	Required for Time & Expense use only
3 - External Expense Report ID	Character 12	Required	Required for Time & Expense use
4 - External Expense ID	Number 3	Required	Required for Time & Expense use only
5 - External Employee ID	Character 20	Required	Required for Time & Expense use only
6 - External TS Schedule	Character 10	Required	Required for Time & Expense use only
7 - External TS Year	Character 4	Required	Required for Time & Expense use only
8 - External TS Period	Character 3	Required	Required for Time & Expense use only
Record Type	Character 1	Required		"H"
Expense Report ID	Character 12	Required	Used for Invoice ID will be added to VCHR_HDR & VCHR_HDR_HS.
Expense Report Date	Character 10	Required	Used for Voucher Invoice Date.	YYYY-MM-DD
Vendor ID	Character 12	Required
Place Expense Voucher(s) on Hold Flag	Character 1	Optional	Default is "N."	"Y," "N," or blank
VAT Tax Date	Character 10	Optional	Default is Expense Report Date from input file.	YYYY-MM-DD
VAT Tax ID	Character 20	Optional	Default from VEND table.
Notes	Character 254	Optional	Default is null.

Detail Format

Business Name		Field Type	Required/Optional	Comment	Format/Value(s)
1 - External System Code		Character 1	Required	Required for Time & Expense use only
2 - External Batch ID		Character 10	Required	Required for Time & Expense use only
3 - External Expense Report ID		Character 12	Required	Required for Time & Expense use
4 - External Expense ID		Number 3	Required	Required for Time & Expense use only
5 - External Employee ID		Character 20	Required	Required for Time & Expense use only
6 - External TS Schedule		Character 10	Required	Required for Time & Expense use only
7- External TS Year		Character 4	Required	Required for Time & Expense use only
8- External TS Period		Character 3	Required	Required for Time & Expense use only
Record Type		Character 1	Required		"D"
Expense Report ID		Character 12	Required	Required for Time & Expense use and used as the Invoice ID. It will be added to VCHR_HDR & VCHR_HDR_HS.
Expense Report Line Number		Number 3	Required		999
Expense Date		Character 10	Required	Used as the Exchange Rate Date.	YYYY-MM-DD
Account		Character 15	Required
Organization		Character 20	Required
Project ID		Character 30	Optional	Default is null.
Reference Number 1		Character 20	Optional	Default is null.
Reference Number 2		Character 20	Optional	Default is null.
Transactional Currency Code		Character 3	Required
Expense Amount - Transactional Currency		Number 15	Required	This amount includes sales tax.	-99999999999.99
Taxable Code		Character 1	Optional	Default is "N."	"N," "S," "U"
Tax Code		Character 6	Optional	Required only if Taxable Code is "S" or "U."
Sales Tax Amount - Transactional Currency		Number 15	Optional	Default will be "0" when Taxable Code is "N." This is the transactional Sales Tax/VAT amount. Will be required if Taxable Code is "S."	-99999999999.99
Use Tax Amount – Transactional Currency		Number 15	Optional	Default is "0" when Taxable Code is "N." This is the transactional Use/Reverse tax amount. Will be required if Taxable Code is "U."	-99999999999.99
1099 Flag		Character 1	Optional	Default is based on values in the Maintain Vendors screen.	"Y," "N," or blank
1099 Miscellaneous Type Code		Character 6	Optional	Default is based on values in the Maintain Vendors screen.
Expense Line Description		Number 30	Optional	Default is Account description.
	Pay Currency Code	Character 3	Optional	Currency for payment. Default is Transactional Currency Code.
	Pay Amount - Pay Currency	Number 15	Optional	Will be required if Pay Currency is not equal Transactional Currency and no Rate Date is provided.	-99999999999.99
	Pay Exchange Rate Date	Character 10	Optional	Will be required if Pay Currency is specified, different from the Transactional Currency, and no Pay Amount is provided.	YYYY-MM-DD
	Pay Vendor	Character 12	Optional	Default is provided in the Maintain Vendors screen.
	Pay When Paid Flag	Character 1	Optional	Default is provided in the Maintain Vendors screen.	"Y," "N," or blank
Recovery Amount - Transactional		Number 15	Optional	Default is "0." Cannot be entered if Taxable Code is "N." This is the transactional VAT recovery amount. If entered, the Recovery Amount will override the Recovery Percentage.	-99999999999.99
Recovery Percentage		Number 6	Optional	Default will be "0" if Taxable Code is "N." Default will be taken from SALES_TAX table if Taxable Code is "S" or "U." Cannot be entered if Taxable Code is "N."	999.99
Notes		Character 254	Optional	Default is null.

Processing Details

The application checks the voucher temporary tables to ensure all rows are imported. If rows have not been imported, the system displays a message on the screen and you can continue or cancel the process. If you continue, the temporary tables will be cleared.
Records from the input file are read and inserted into the temporary tables.
Defaults are applied to fields that are blank in the input file and have a "default provided" status.
Validations are performed. (See "Validations Performed" section below for details.)
Once the validations are completed, expenses with errors are written to the error file. If there is an error on one line of an expense report (header or detail), all lines of the expense report are rejected and written to the error file. These rows are then deleted from the temporary tables. Temporary tables have only valid rows in them.
Field processing and calculations are performed.
The error report is printed. If there are errors, the system will display a message. If there are no errors, the error report will indicate that no records were found.
An error file is also created (to send back to Deltek Time & Expense).

Error File Layout

An error file is created in the following layout, similar to the upload file. It includes the first eight Time & Expense fields in the header record and other data defaults required by Time & Expense. Because the error file is directly downloaded into Time & Expense, it must be in a comma-separated format.

The CSV error file is saved with the following naming convention: EXP_REJECTION_YYYMMDDHHMMSS.CSV.

The directory to which the error file will be written is the directory specified in the Select field.

Note: If one voucher within an expense report has an error and cannot be uploaded, all vouchers with the same expense number will be included as errors and will be on the Error report. Time & Expense cannot accept partial expense report records.

Business Name	Field Type	Field Value/Default	Info/Description
Version	Number 1	Set to "1."	For Time & Expense use only
Import Code	Character 1	Set to "B."	For Time & Expense use only
3- External Expense ID	Number 3	Set to value from upload file.	For Time & Expense use only
4- External Expense Report ID	Character 12	Set to value from upload file.	For Time & Expense use only
Rejection Reason	Character 234	Set to value from error message.	For Time & Expense use only

Application of Defaults

Once all records from the import file have been read and copied, defaults (as selected in the TE Suspense Setup screen) are applied to any blank fields.

Validations performed

Once completed records are available, the validation process begins. All valid vouchers created from this application are assigned a voucher type of "TE."

For uploads from ASCII files (delimited), records failing validation will be written to an error file and a printed error report.

Note: If there is an error on any line of a voucher (header, detail, or vendor labor), all lines of the voucher AND all vouchers with the same Expense Report ID will be rejected and written to the error file.

The error report contains the voucher number, expense report ID, fiscal year, record type, field, contents, and error message. The error file is generated in the same format as the Upload file and has the same prefix with a suffix of ". ERR". In addition, each error record is appended with the first eight Time & Expense fields in the header record. These Time & Expense fields are: External System Code, External Expense Report ID, External Expense ID, External TS Schedule, External TS Year, External TS Period. With errors properly recorded and reported, rejected records are then deleted. This leaves only valid rows for processing.

VALIDATIONS FOR TE PREPROCESSOR INPUT FILE

COLUMN MAPPINGS FOR AP VOUCHER TABLES

VOUCHERS CREATED

CURRENCY CALCULATIONS

REPORTS

SCREEN ERROR MESSAGES

TECHNICAL DETAILS

INPUT FILE (USER-NAMED)