Employee Job Import Interface logic

This section describes how the standard Employee Job Import Interface converts employee job CSV data into the Workforce Management system. This section references field specifications in Employee Job Interface field specification.

The Employee Job Import file is a CSV text file with columns enclosed in quotes if there are commas in the column field value. The records in the file are processed sequentially from top to bottom.

EMP_NAME (column 1)

The EMP_NAME field identifies the employee that you are adding, updating, or deleting the job assignment for. If a value is not provided, then an error is returned.

JOB_NAME (column 2)

The JOB_NAME field identifies the job that is being assigned to the employee or whose assignment is being updated or deleted. If a value is not provided, then an error is returned.

EMPJOB_RATE_INDEX (column 3)

The EMPJOB_RATE_INDEX field specifies the job rate index. If a value is not provided, then 0 is used for the ADD action and the existing value is not modified for the UPDATE action. If this field is set to null using the ampersand (&) character, then an error is returned.

If a job rate index is provided, then the corresponding job rate must be defined in the labor metrics setup for this job. For the ADD and UPDATE actions, the validation considers the start date in the import file to correspond with the effective start date of the job rate index. For the UPDATE action, the validation also considers the ADJ_START_DATE in the import file to correspond with the effective start date of the job rate index, if provided.

Note: This differs from the current behavior on the maintenance form, which does not validate on updates.

EMPJOB_RATE (column 4)

The EMPJOB_RATE field specifies the employee job rate. If a value is not provided, then 0 is used for the ADD action and the existing value is not modified for the UPDATE action. If this field is set to null using the ampersand (&) character, then an error is returned.

START_DATE (column 5) and END_DATE (column 6)

The START_DATE and END_DATE fields specify the start date and end date of the employee's job assignment. If a value is not provided, then an error is returned.

ADJ_START_DATE (column 7) and ADJ_END_DATE (column 8)

The ADJ_START_DATE and ADJ_END_DATE fields are optional and specify the adjusted start date and end date of the employee's job assignment. If a value is not provided, then the existing job assignment dates are not updated.

ACTION (column 9)

The ACTION field specifies the action to be performed for the job assignment. The action code is not case-sensitive: Add, ADD, and ADd all evaluate to the same action.

This table describes the behavior of the ACTION field:

Action Description
ADD Adds a new job assignment to the employee.

If a row does not exist for the employee based on JOB_NAME for the start and end date, then a new row is added. If there are any existing jobs with overlapping dates, then the dates are adjusted.

If a row already exists for JOB_NAME for the start and end date, then an error is returned.

UPDATE Searches the job assignments of an employee and updates accordingly:
  • If the EMPJOB_WORKASSIGNMENT field is not empty in the import file, then:
    • The job assignments are updated based on JOB_NAME and EMPJOB_WORKASSIGNMENT where the dates of the existing and incoming job assignments overlap.
    • If any of the other provided fields do not match, then a new employee job row is created with these new values. The dates on the existing rows are adjusted.
    • If the JOB_NAME and start/end dates in the import file match an existing job assignment whose EMPJOB_WORKASSIGNMENT is empty, then the fields of the existing job assignment are updated to the values in the import data. This includes the EMPJOB_WORKASSIGNMENT field.
  • If the EMPJOB_WORKASSIGNMENT field is empty in the import file, then:
    • The job assignments are updated based on JOB_NAME where the start date or end date overlaps the new start and end dates in the import file.
    • If any of the other provided fields do not match, then a new employee job row is created with these new values. The dates on the existing rows are updated.
    • If a record in the import file matches an existing job assignment whose EMPJOB_WORKASSIGNMENT has a value, then an error is returned.
  • When the ADJ_START_DATE or ADJ_END_DATE fields are specified, the incoming import file must have the exact dates for the existing data to be updated. Otherwise, the import cannot match the exact record for which the dates are to be adjusted. In addition, if any other values are provided, then these values completely replace any existing values along with the date adjustment.
  • If the job assignment does not exist, then an error is returned.
  • Records are merged into one job assignment if the resulting date periods are contiguous or overlapping. This assumes all the fields including the UDFs are the same.
    Note: The maintenance form does not currently merge employee job rows even when all values are the same and the dates are contiguous. Such rows are merged by the import.
  • For an existing row, the UPDATE action does not change the JOB_NAME value. To change the job for a work assignment, you must specify the ADD action and not UPDATE. See Example 5: Changing job for a work assignment.
DELETE Deletes the job assignment of an employee during the specified date range.

For deletes, the incoming files must provide all the necessary fields including exact start and end dates. This is required to match the exact record that needs to be deleted.

If a record in the import file with an empty EMPJOB_WORKASSIGNMENT matches an existing job assignment whose EMPJOB_WORKASSIGNMENT has a value, then an error is returned.

If there is no matching job assignment, then no action is taken.

EMPJOB_PREFERRED (column 10)

The EMPJOB_PREFERRED field is optional and can be used to distinguish between preferred and non-preferred jobs. If a value is not provided, then null is used for the ADD action and the existing value is not modified for the UPDATE action. If the value is to be set to blank (null), then the ampersand (&) character must be used.

EMPJOB_WORKASSIGNMENT (column 11)

The EMPJOB_WORKASSIGNMENT field is optional and can be used by the source system to reference their own job assignments. If a value is not provided, then null is used for the ADD action and the existing value is not modified for the UPDATE action. If the value is to be set to blank (null), then the ampersand (&) character must be used.

If the value exceeds 20 characters, then an error is returned.

EMPJOB_FLAG (column 12)

The EMPJOB_FLAG field is optional and can be used to specify 5 Flag values. In the import file, the existing 5 Flag fields for each employee job should be specified in sequential order. If a Flag value is not to be added or modified, then it must be left blank in the import file. If a Flag value is to be set to blank (null), then the ampersand (&) character must be used. For example, Y&N Y.

If the number of Flags is not 5, then an error is returned.

EMPJOB_UDF1..5 (columns 13 to 17)

The EMPJOB_UDF fields are optional and can be used to specify up to 5 UDF values. If a value is not provided, then null is used for the ADD action and the existing value is not modified for the UPDATE action. If the value is to be set to blank (null), then the ampersand (&) character must be used.