Following the header row are one or more data rows. Each data row shall contain five mandatory columns in addition to one or more data columns. The data columns must exactly match the layout defined in the header row. The five mandatory columns are as follows:
Column 1 - Employee Record Command - [ INSERT ][ UPDATE ][AUTO][ IGNORE ]
INSERT - Informs the system that Employee data will be inserted into the system
UPDATE - Informs the system that Employee data will be updated in the system
AUTO – Will evaluate if the Employee exists or needs to be created.
Note – the auto option can only be used for the employee when the system is configured to allow manual entry of Employee System Number.
IGNORE - Forces the system to ignore all data pertaining to the Employee tables
Column 2 - Assignment Record Command - [ INSERT ][ UPDATE ][AUTO][ IGNORE ]
INSERT - Informs the system that Assignment data will be inserted into the system
UPDATE - Informs the system that Assignment data will be updated in the system
AUTO - Will evaluate if the Assignment exists or needs to be created.
Note – the auto option can only be used for the assignment when the system is configured to allow manual entry of the Assignment Number.
IGNORE - Forces the system to ignore all data pertaining to the Assignment tables
Column 3 - Employee System Number - This value uniquely identifies an employee. It is required for an Update or AUTO command. For an insert operation it is optional, but only if the system is configured for Auto-Populating.
Column 4 - Assignment Number - This value uniquely identifies an assignment, and is sometimes referred to as the "Relocation System Number." It is required for an UPDATE or AUTO command. A value is also required for INSERT operations when the system is not configured to autopopulate this field. If the system is configured for auto-population, a value of “NEW” will cause the system to create the Assignment Number with the same value the Assignment System Number while leaving this field blank will cause the system to assign a unique alphanumeric value.
For assignment updates, the Assignment Number value is generally required for the import to correctly identify the update target. One exception to this involves the Assignment Identifier field and is documented below. Another exception is when business-rule based assignment selection logic is employed. This allows the system to make a best guess as to the correct target assignment based on particular sets of business rules. This is engaged by supplying a special value in the Assignment Number field. The trigger value and associated matching rules are:
Trigger Value
|
Matching Rules
|
<AUTO1>
|
|
<AUTO2>
|
|
<AUTO3>
|
|
Column 5 - Assignment Identifier – This value is optional. The column exists to handle a specific use case. The value you supply will not be stored in the Equus Platform database, but will instead be used to link two or more rows together within the Import. If you wish to insert a new assignment (or a new employee and assignment combination), and you further wish to let the system auto-populate the Assignment Number, and you further want to add multiple addresses or contact information records to the new assignment, then you will need to supply a value for this column. Pick any value you like (alphanumeric), but make sure that every row of data that should serve as an update to this newly-created assignment has the same value in this column, and that no row of data that should update a different assignment has this same value.
There are a several possible combinations of the above commands. The allowed combinations for the four mandatory columns are as follows:
Employee Command
|
Assignment Command
|
Employee Number
|
Assignment Number
|
Behavior
|
AUTO
|
AUTO
|
Required
|
Required
|
Will insert the Employee if the Emp Num does not exist or will update if it does. Will insert the Assignment if the Assign Num does not exist or will update if it does.
|
INSERT
|
INSERT
|
Optional
|
Optional
|
Inserts into both Employee and Assignment tables
|
AUTO
|
INSERT
|
Required
|
Optional
|
Will insert the Employee if the Emp Num does not exist or will update if it does. Will insert the Assignment.
|
UPDATE
|
INSERT
|
Required
|
Optional
|
Updates Employee table and Inserts into Assignment table
|
UPDATE
|
UPDATE
|
Required (unless an Assignment Identifier value is provided instead)
|
Required (unless an Assignment Identifier value is provided instead)
|
Updates both Employee and Assignment tables
|
INSERT
|
IGNORE
|
Optional
|
Ignored by system
|
Inserts Employee info into the system - Ignores all Assignment values
|
IGNORE
|
INSERT
|
Ignored by system
|
Optional
|
Inserts Assignment info into the system - Ignores all Employee values
|
UPDATE
|
IGNORE
|
Required
|
Ignored by system
|
Updates Employee tables - Ignores all Assignment values
|
IGNORE
|
UPDATE
|
Ignored by system
|
Required (unless an Assignment Identifier value is provided instead)
|
Updates Assignment tables - Ignores all Employee values
|
IGNORE
|
IGNORE
|
Ignored by system
|
Ignored by system
|
Ignores the entire row of data
|
Be aware that all tables other than EMPLOYEE, EMPLOYEE_CF, and EMPLOYEE_PASSPORT are considered part of the assignment, so a value of “IGNORE” in the assignment command column will cause all of those fields to be ignored. Also, the assignment command only needs to be set to “INSERT” if the assignment itself is new, not if any of the data in any of the other tables are new. Any inserts to any of the other tables are considered updates to the assignment. For example, if a data row should insert a new address into the ASSIGNMENT_MAILING_ADDRESS table for an assignment that already exists, the assignment command should be set to UPDATE. The code logic will determine that an insert is necessary for the subordinate data, and won’t generate an error. This is true for the following tables:
ASSIGNMENT_CF
ASSIGNMENT_COMPENSATION_BONUS and ASSIGNMENT_COMPENSATION_BONUS_CF
ASSIGNMENT_COMPENSATION_POLICY and ASSIGNMENT_COMPENSATION_POLICY_CF
ASSIGNMENT_COMPENSATION_SALARY and ASSIGNMENT_COMPENSATION_SALARY_CF
ASSIGNMENT_COST_CENTER and ASSIGNMENT_COST_CENTER_CF
ASSIGNMENT_DEPENDENT and ASSIGNMENT_DEPENDENT_CF
ASSIGNMENT_EMPLOYEE_CONTACT
ASSIGNMENT_FAMILY_SIZE
ASSIGNMENT_MAILING_ADDRESS and ASSIGNMENT_MAILING_ADDRESS_CF
RELOCATION_POLICY_FIELD_EXCEPTION and RELOCATION_POLICY_FIELD_EXCEPTION_CF
RELOCATION_SCREEN_CONTACT and RELOCATION_SCREEN_CONTACT_CF
SPOUSE_DOMESTIC_PARTNER and SPOUSE_DOMESTIC_PARTNER_CF
ASSIGNMENT_TAX_INPUT_OVERRIDE
Note, this import does not support importing to the Inactive field (ASSIGNMENT!INACTIVE).
The sixth column, and all subsequent columns, should contain data (or be blank when appropriate). The field order should exactly match the field order specified in the header row, as should the field count.
In cases where an update is performed (the identified record already exists), any field which is left blank in the data row will be ignored. Such fields will not be given blank values in the database (they will not be updated at all). If you need the field to be set to “NULL” in the database, then specify the literal text “<blank>” in the value of the field. In the case of inserts, blank fields and fields that contain the text “<blank>” will both result in NULL values in the database.