Chapter 2 – HR Import Templates and Processes
HR Import rules
There are several rules that apply universally to definitions based on HR Import templates.
1. Spreadsheets that are password protected cannot be used to import data.
2. Single spreadsheets only can be used for each import and formula such as
Vlookup, that reference other spreadsheets, will produce errors and should not be used.
3. When importing an update an empty value for a field on the definition will not overwrite the existing values for that field in the database.
4. A single asterisk (*) used in a field on the HR Import definition is interpreted in all cases as ‘Update this field to empty’. If however this is set for a mandatory field, the row will be rejected.
Note: An asterisk cannot be used as the default value in the Default field.
5. ‘Current appointment’ means ‘current appointment for current employees’ or
‘appointment at termination date for terminated employees’.
6. All HR Import processes are identified by the data. There are no process specific fields on the templates.
7. Multiple HR Import definitions can be created with different defaults. For example, one definition could be created for weekly staff with a default pay group of “WKLY” and one for monthly staff with a default pay group of
“MNTHLY”. This would allow the import of weekly and monthly staff without actually supplying a pay group within the source spreadsheet.
8. Definitions must include all required fields for the template upon which the definition is based regardless of the nature of the process or update being performed by the import.
9. The same rules of validation for narrow import will apply to HR Import including referential integrity. For example, it is important that a new starter is processed using the Employee template before any further imports for that employee are imported and validated.
Template Required and Optional Fields
Definitions are based on HR Import templates. Each HR Import template contains ‘tables’
that equate to tables on the ADP freedom database. Each ‘table’ contains fields that equate to the fields in the tables on the database. Some tables are required. Some tables are optional. Required tables contain both required fields and optional fields. Optional tables
Chapter 2 – HR Import Templates and Processes
Import rows will fail if:
• all of the required fields, for all required tables, are not completed for a definition.
• all of the required fields are not completed for any optional tables being submitted for import.
Figure 2 - 1 – Template diagram
Template
Required Table 1 Required Table 2 Optional Table 1 Optional Table 2 Required field 1 Required field 1 Required field 1 Required field 1 Required field 2 Optional field 1 Required field 2 Required field 2 Required field 3 Optional field 2 Required field 3 Optional field 1 Optional field 1 Optional field 1 Optional field 2 Optional field 2 Optional field 2 Optional field 3
Optional field 3 Optional field 3
For example, in the template diagram above:
• Required Table 1 and its Required fields 1, 2 and 3 as well as Required Table 2 and its Required field 1 must be completed.
• For Required Tables 1 and 2 the user can also submit any or all of the Optional fields.
• The user may import Optional Table 1 and/or Optional Table 2.
• If the user decides to submit data for Optional Table 1, Required fields 1, 2 and 3 for that table must be completed and Optional fields 1, 2 and/or 3 may be completed.
• If the user decides to submit data for Optional Table 2, Required fields 1 and 2 for that table must be completed and Optional fields 1, 2 and/or 3 may be completed.
Chapter 2 – HR Import Templates and Processes
HR Import Templates and Associated Processes
This table shows the HR Import templates that are available as column, and the different employee processes they can be used for on the left.
Table 2 - 1 – Templates and associated processes
Appointment
Chapter 2 – HR Import Templates and Processes
Appointment template
Definitions based on the Appointment template are used to:
• create appointments.
• update appointments.
• close appointments.
The Appointment HR Import template consists of the following tables and their associated fields:
Note: Fields showing in blue or with an asterisk (*) are required fields for the template.
When creating or updating non-primary appointments, values for at least the required fields must be included on the definition.
Table 2 - 2 – Tables and fields in the Appointment template
Page/TABLE Field/COLUMN
HR ID (HR_ID)*
Appointment (APPOINTMENT) Business unit (BU_CODE)*
Job (JOB_CODE)*
Start date (APPT_START_DATE)*
End date (APPT_END_DATE) Cost centre (COST_CENTRE_CODE) GL bus code (GL_BUS_CODE) Grade (GRADE_CODE) Location (LOC_CODE)
Primary appointment (PRIM_APPT_IND) Reports to (REPORTS_TO)
Use ezLaborManager flag (USE_EZLM_IND)
New appointments
If BU code, Job code and Appointment Start date are imported with an HR ID where an employment record exists but no appointment record exists, a new appointment is created.
A new appointment will also be created if a new value is imported for any or all of the BU code, Job code or Appointment Start date fields for an existing HR ID with an existing employment record and additional existing appointment record(s).
At the time of import, if an existing payroll profile record is in effect as of the Appointment Start date for a new appointment, the profile is associated with the appointment. If no payroll profile exists at the time that the appointment is created, the appointment is not assigned to a payroll profile.
Chapter 2 – HR Import Templates and Processes
If the position derived from the imported BU Code and Job Code for an appointment matches multiple positions created by that BU Code and Job Code combination already on the database, the position with the most recent start date will be used.
Appointment updates
If only one appointment exists for that HR ID, and the Person ID, Employment No., BU Code, Job Code and Start date on the Appointment page matches the information being imported, updates are made to the appointment using the imported information.
Closing appointments
If the HR ID, Appointment Start date, BU Code, Job Code and Appointment End date are imported for an appointment where no End date previously existed, the appointment is closed.
Rejected appointment import rows
If there are no employment records matching the HR ID used on the spreadsheet, the imported rows will be rejected.
If there are multiple matching employee records matching the HR ID used on the spreadsheet, the imported row(s) are rejected.
If the position derived from the imported BU Code and Job Code for an appointment do not match a position created by that BU Code and Job Code combination already on the
database, the imported row will be rejected.
Importing the ezLaborManager indicator on Appointments
The Appointments table includes an USE_EZLM_IND column that only applies when the ezLaborManager integration component is enabled in ADP freedom. It is used to identify employees for whom selected shared details are kept in sychronisation between freedom and the ezLaborManager application.
Regardless of the type of import used (narrow or wide), the following conditions apply whenever importing a value to the USE_EZLM_IND column:
• There is no way of hiding an import column based on a whether or not an ADP component is enabled, so if a value of ‘Yes’ exists on an import file for this column and the ezLaborManager integration component is not enabled, it will be rejected.
• A ‘Yes’ will only be accepted in this column for an employee’s primary Appointment.
• If the column is omitted, or no value is specified, a value of ‘No’is assumed.
Chapter 2 – HR Import Templates and Processes
Earnings and Deductions template
Definitions based on this template are used to update employee earnings and deductions.
Note: Fields showing in blue or with an asterisk (*) are required fields for the template.
When updating earnings and deductions, values for at least the required fields must be included on the definition.
Table 2 - 3 – Tables and fields in the Earnings and Deductions template
Page/TABLE Field/COLUMN
HR ID (HR_ID)*
Earnings (EE_EARNINGS) Earnings (EARN_CODE)
Table search value (EARN_TABSRCH_CODE) Amount (EE_EARN_DFLT_AMT)
Quantity (EE_EARN_DFLT_QTY) Rate (EE_EARN_DFLT_RATE) Effective from (EE_EARN_ST_DATE) Effective to (EE_EARN_END_DATE)
Earnings Status Code (EE_EARNSTATUS_CODE)
Deductions & ER Contrib (EE_DEDUCTION)
Deduction (DED_CODE)
Table search value (DED_TABSRCH_CODE) Amount (EE_DED_DFLT_AMT)
Percentage (EE_DED_DFLT_RATE) Effective to (EE_DED_END_DATE) Goal (EE_DED_GOAL)
Goal to date (EE_DED_GOAL_TO_D) Max. limit (EE_DED_LIMIT)
Print balance on payslip (EE_DED_PRINT_BAL) Effective from (EE_DED_ST_DATE)
Recover deduction goal balance from leaver (EE_RECOVER_BALANCE_IND)
Chapter 2 – HR Import Templates and Processes
Employee template
Definitions based upon the Employee template can be used to create new employees and update a basic set of employee detail records.
In addition, the following processes can also be performed using the Employee template:
• New starters
• Rehires
• Leavers
• Appointment updates (for primary appointments only)
• Pay group transfers (for primary appointments only)
• Payroll profile transfers
• P45/P46 additions or updates
Note: One definition based on the Employee template can contain rows for multiple processes as long as the requirements of each process are met.
The Employee HR Import template consists of the following tables and their associated fields:
Note: Fields showing in blue or with an asterisk (*) are required fields for the template.
When performing any of the updates or processes associated with the Employee template, values for at least the required fields must be included on the definition.
Table 2 - 4 – Tables and fields in the Employee template
Page/TABLE Field/COLUMN
HR ID (HR_ID)*
Effective date (EFFECTIVE_DATE)*
Appointment (APPOINTMENT) Appointment FTE (APPT_FTE) Business unit (BU_CODE)
Cost centre (COST_CENTRE_CODE) GL bus code (GL_BUS_CODE) Grade (GRADE_CODE) Job (JOB_CODE) Location (LOC_CODE) Reports to (REPORTS_TO)
Use ezLaborManager flag (USE_EZLM_IND) (see note on page 2-5)
Appointment Working Hours (APPT_WORK_HOURS)
Weekly hours worked
(WEEKLY_CONTRACTED_HOURS)
Chapter 2 – HR Import Templates and Processes
Page/TABLE Field/COLUMN
(Primary appointment only) Hours employee is contracted to work (CONTRACTED_HOURS)
Number of ‘Periods’ in which hours must be worked
(INTERVAL_AMOUNT)
Period of time hours must be worked – Weeks (W), Months (M), Years (Y) only
(INTERVAL_UNITS)
Shift pattern (if applicable for agreement) (SHIFT_PATTERN_CODE)
Working Hours Agreement – Terms of Contract (WORK_HRS_AGREEMENT_CODE)
Note: When importing a passport number, a passport entry is added to the
EE_ID_DOCUMENT table for the passport number provided. The document type is set to the type for which the
DEFAULT_FPS_PASSPORT_IND is set in the ID Document codes.
Payment Method (see Note 1)
(EE_NET_DISBURSEMENT_RULE)
Account name (BANK_ACCT_NAME) Account no (BANK_ACCT_NO) Sort code (BANK_SORT_CODE) Pay method (PAY_METHOD_CODE) Building society roll no (ROLL_NO)
Payroll Profile (EE_PAYROLL_PROFILE) Current Payroll Company (COMPANY_CODE) Automatic Standard Pay
(EE_AUTOGEN_REG_IND) Pay group (PAY_GROUP_CODE)
Pay point for payslips (PAY_POINT_CODE)
Rate Details (EE_RATE) (Note: BASE rate changes only)
Base pay rate (EE_PAYRATE)
Employee Standard Hours (EE_STD_HRS) Pay Frequency (PAY_FREQ_CODE) Rate Effective Date (RATE_EFF_FROM) – defaults to EFFECTIVE_DATE
Rate Change Reason
(EE_RATE_CHANGE_REASON)
Reason for Rate Change
(RATE_CHANGE_REASON_CODE)
Chapter 2 – HR Import Templates and Processes
Page/TABLE Field/COLUMN
Statutory Detail (EE_STATUTORY) Employee code (EMPLOYEE_CODE)
Tax Details (P45 and P46 only) (EE_TAX_DETAIL)
Date of Leaving (DATE_OF_LEAVING) Employers Pay Reference
(EERS_PAYREF_NO)
Employers Tax District
(EERS_TAX_DISTRICT_NO)
Source of the change (P45 or P46 only) (EMP_CHANGE_SOURCE)
Employer Tax District (USER_DISTRICT)
Employment Details (EMPLOYMENT) Continuous service date (CONT_SERV_DATE) Normal hours worked
(CONTRACTUAL_HOURS_BAND_CODE) Employee status code (EE_STATUS_CODE) Termination Date (EMPL_TERM_DATE) Full time/Part time (FULL_PART_TIME) Irregular payment pattern
Employment User Defined Fields (EMPLOYMENT_USER)
Dates 1 to 6 (DATE1 to DATE6)
Numbers 1 to 6 (NUMBER1 To NUMBER6) Text 1 to 8 (TEXT1 to TEXT8)
Chapter 2 – HR Import Templates and Processes
Page/TABLE Field/COLUMN
Pension Enrolment
(PENSION_SCHEME_MEMBERSHIP)
Workplace Pension Opt Out Date (OPT_OUT_DATE)
Workplace Pension Opt Out Period End date (OPTOUT_PERIOD_END_DATE)
Date of joining scheme (PENMEM_DOJ) Date of leaving scheme (PENMEM_DOL)
Stakeholder policy no
(PENMEM_STKPOLICY_NO) Scheme code (SCHEME_CODE)
Note: The Opt Out Date and Opt Out Period End Date should only be entered in the Workplace Pension Reform component is enabled.
Person (PERSON) Date of birth (BIRTH_DATE) Place of birth (BIRTH_PLACE) Date of death (DEATH_DATE)
Died in service indicator (DIED_IN_SERVICE) First name (FIRST_NAME)
Preferred name (PREFERRED_NAME) Title (TITLE_CODE)
Addresses (PERSON_ADDRESS) Address (line 1) (ADDRESS_LINE_1) Address (line 2) (ADDRESS_LINE_2) Address (line 3) (ADDRESS_LINE_3) Address type (ADDRESS_TYPE_CODE)
Chapter 2 – HR Import Templates and Processes
Page/TABLE Field/COLUMN
Town/City (TOWN)
Note: See Appendix A – Country Codes and Appendix B – United Kingdom County Codes for the import codes used for countries and
counties.
Note 1: By default the HR Import process will reject changes to an employee's payment