Click for sample file - two relocations (expenses) on one voucher

Please refer to the Multiple Move Voucher Flexible Header Field Column Definition spreadsheet for a list of the supported fields, unsupported fields and business rules.

Note: Run the Equus Configuration Audit: Lookup Maintenance report and add Group Code as the report filter to view the details for a selected CLASSIFICATION table including the display values to use in the import.

Each header row includes any number of field-definition columns. The value in the column identifies a specific field of data that will be imported (the import will expect that the subsequent data rows will have a value intended for that field in the same column). The format for specifying a field is “<table>!<field>”. For example, the value “VOUCHER!PAYEE_TYPE” indicates that this column will provide values for the PAYEE_TYPE field of the VOUCHER table. All of the field definitions in all of the header rows follow this format.

The first row is the “voucher” header row. The first column of this row must specify that it is the voucher header by containing the value “VOUCHER”. Each subsequent column should specify a specific field in one of the voucher tables, using the format given above. Any of the supported tables that begin with the word “VOUCHER” can have fields identified in this header row.

The second row is the “expense” header row. The first column of this row must specify that it is the expense header by containing the value “EXPENSE.” The next TWO columns of the header row should NOT identify fields. These two columns are placeholders for the relocation identification information that will be a part of each expense data row (each expense must be associated with exactly one relocation, and these two fields together will indicate which relocation should be used). The second column should contain the value “Identifier”, and the third column should contain the value “Value”. Each subsequent column should specify a specific field in one of the Entry or Expense tables, using the format given above.

The third row can be the first data row (details below). Optionally, the third row can be another header row. If service tax information will be imported, then this third row will contain the service tax header row. In that case, the first column should contain the value “ServiceTax”. Each subsequent column should specify a specific field in the ENTRY_EXPENSE_SERVICE_TAX table, using the format given above.

The rows following the header rows are all data rows. Each data row follows the format defined in one of the header rows. The first column of the data row indicates which header row defines its format. The value contained in this column should exactly match the value contained in the first column of the desired header row. For example, a data row that will provide voucher information should have the word “VOUCHER” in the first column, and every subsequent column should contain a value that should be imported into the corresponding field as defined in the voucher header row. The same is true of expense and service tax data rows (they should contain the words “EXPENSE” or “SERVICETAX” in the first column, and appropriate data in subsequent columns).

Each voucher data row imports a new voucher record. Any expense rows that follow that voucher row will each import a new expense tied to that voucher. There is no limit to the number of expense rows that can follow a voucher row. A voucher row that follows an expense row signals the creation of a new voucher record, and all subsequent expense rows will belong to this new voucher.

Service Taxes are attached to expenses in the same way that expenses are attached to a voucher. Service Tax data rows should follow expense rows, and the resultant service tax record will be attached to the expense that it follows. There is no limit to the number of service tax rows that can follow an expense row.

The Expense data row begins with three special columns. The first column contains the word “Expense” and identifies the row as an Expense data row (the same way the other data row types are identified). The second two columns work together to identify the relocation with which the expense will be associated (every expense must be associated with exactly one relocation). A relocation has a whole collection of fields that can be used to identify it, and the second column in an expense row indicates which of these identifier fields will be used. The third column provides the value that should match that field in a relocation record.

For example, the “From Employee Id” field appears on the relocation screen.

The value that field contains should be unique for every relocation in the system. In order to associate an expense with a relocation, the second column of the expense data row would contain the value “FHID” (to indicate that “From Employee ID” is used as the identifier) and the third column would contain the actual Employee ID value. The import would then look up the relocation record by From Employee ID, and associate the imported expense with that record.

Here is a table of all the possible relocation identifiers, and which code should be entered into the Identifier column (the second column of an expense row) in order to use it:

Generally speaking, the import can update any field in any of the supported tables other than the ID field and any foreign key fields that link these tables to each other. For lookup-type fields (any field that appears as a drop-down list on the screen) the value given to the import is the full text of the value the user sees on the screen (NOT the ID or code that is looked up and stored in the database). A country or a currency field should be imported using the appropriate three-letter symbol (not the full name). A field that appears as a checkbox on the screen should be imported with a value of “Yes” if the box should be checked and “No” otherwise.

There is a lengthy list of validation rules that the data must pass in order to avoid being rejected by the import. These same rules apply to the data entered through the screens. A complete list of these rules is, however, beyond the scope of this document. The following is a list of the most important required fields:

1) VOUCHER - The first column of the voucher row must contain the word “VOUCHER” and identifies the row as a voucher data row.

2) VOUCHER!MULTI_MOVE - [ YES ] Since the import currently only supports multi-move vouchers, this field should always have the value 'Yes' (classification group code for the field is EQBOOL)

3) VOUCHER!PAYEE_TYPE - [Vendor] Since the import currently only supports multi-move vouchers, this field should always have the value 'Vendor'. (classification group code for the field is EQPETP)

4) VOUCHER!PAYEE_VENDOR_ID - Supply the value in the SYSTEM_NUMBER_ field of the VENDOR table

5) VOUCHER!VOUCHER_STATUS_ID - Supply a value from the NAME column of the VOUCHER_STATUS table such as DRAFT, APPROVAL IN PROGRESS, APPROVED.

6) VOUCHER!SUBMITTED_CURRENCY_MODE - Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQVCMD such SINGLE or MULTIPLE

1) EXPENSE - The first column of the expense voucher row must contain the word “EXPENSE” and identifies the row as an Expense data row.

2) Identifier - The following assignment/relocation level data fields can be used to identify the assignment/relocation for which you are trying to add import vouchers and expenses.

3) Value – The Corresponding value of the assignment identifier selected.

4) ENTRY!ENTRY_TYPE_ID- Supply a value from the NAME column of the ENTRY_TYPE table. Usually it is 'Expense'

5) ENTRY!ACCOUNT_ID - Supply a string that concatenates the values in the ACCOUNT_NUMBER and SUB_ACCOUNT_NUMBER fields of the ACCOUNT table (separated by a period “.” Character). If SUB_ACCOUNT_NUMBER is null, just give the ACCOUNT_NUMBER, otherwise give
ACCOUNT_NUMBER.SUB_ACCOUNT_NUMBER. If these values have been overridden at the company level, supply the company-overridden values (for the company with which the given assignment is associated).

6) ENTRY!SOURCE - Supply the value in the DISPLAY_VALUE column of the
CLASSIFICATION table for group code EQEXSC (Billing Automation, Billing, Compensation Worksheet Line Item, Interface, Import, Manual Entry, Recurring Payment, Vendor Integration)

7) ENTRY!AMOUNT - Supply the amount of the expense.

8) ENTRY!CURRENCY - Needs to be a GROW 3-digit currency code. Matches to the CODE column of the CURRENCY table.

9) ENTRY_EXPENSE!SUBMITTED_AMOUNT - Supply the amount of the expense.

10) ENTRY_EXPENSE!CURRENCY- Needs to be a GROW 3-digit currency code.
Matches to the CODE column of the CURRENCY table.

11) ENTRY_EXPENSE!SUBMITTED_TO_ENTRY_EXCHANGE_RATE – Supply the Exchange Rate, use 1 if the Currency submitted is the same as entered.

12) ENTRY_EXPENSE!SUBMITTED_TO_ENTRY_EXCHANGE_RATE_DATE – Supply the Exchange Rate Date