Personnel Data Load allows you to load HR personnel data from an input file.
Input File format
The first line of the file should give the database table to be populated.
Possible values are:
pp_organisation - for personnel records
pp_events - for appraisals, disciplinaries, grievances, salary history records, and any user defined pp_events types
pp_orgtypes - for contacts, beneficiaries, and correspondence
pp_benefits - for benefits
pp_orgtime - for holidays, absences, short absences and lateness records, as well as Accrual Plans available with the benefits on the Personnel Workbench.
pp_course_at - for training course/assessment attendance records.
pp_planenroll - for the benefits on the Personnel Workbench. Special Note: To allow the ability to maintain Cobra enrolments, you can load enrolments for Terminated personnel.
pp_medical - for Drug Tests administered to employees. This is related to hr_medical below. Please see the more detailed Special Notes on Drug Testing below.
hr_medical - for the type of Drug Tests administered. This is related to pp_medical above. Please see the more detailed Special Notes on Drug Testing below.
pp_quals - for employee qualifications such as training/assessments, qualifications, and memberships.
pp_employer - for employment verifications.
The second line can optionally be a comma-separated list of field/value pairs that applies to all records to be created/updated.
For example, to load just salary history data, you could begin the file with:
pp_events
ppe_type=HRSAL
The next line should be a comma-separated list of fields for the table data to be loaded. This list - together with any field/value pairs for the whole file - must include a key to identify individual records.
For pp_organisation records, this can be either:
ppo_seq - a unique sequence number for the records;
ppo_ni_no -SS number;
hrp_id - the HR ID; or
at least one of ppo_surname, ppo_1stname, ppo_2ndname, ppo_dob - the surname, first name, middle names, and date of birth.
For all the other tables, the ppo_seq field must be included, but the values for this can be either the sequence number, the SS number, the HR ID, or the person's name.
The pp_events table additionally requires the ppe_type field, which should be one of the following:
HRAPP - for appraisals
HRDIS - for disciplinaries
HRGRE - for grievances
HRSAL - for salary history records
UD - for your own user defined type
The pp_orgtypes table requires the ppt_type field, which should be one of the following:
CON - for contacts
BEN - for beneficiaries
REF - for correspondence
The pp_orgtypes table for contacts requires the pqy_deptype field, which should be one of the following:
S - for Spouse
L - for Domestic Partner
F - for Former Spouse
D - for dependant
O - for Other
The pp_benefits table requires either the ppb_type field, which should be a benefit type you have defined using Lookup Codes, or - even better - the hrb_seq field, which specifies the exact benefit (as defined in Benefits) and is essential to create a new benefit record.
The pp_orgtime table requires the pph_type field, which should be one of the following:
HOLBULK - for a holiday period
ABSBULK - for an absence period
HRHOL - for an individual holiday day within an already existing bulk holiday period
HRABS - for an individual absence day within an already existing bulk absence period
HRSHORTABS - for short absences
HRLATE - for lateness records
Any code defined under Lookup Codes for Accrual Plan Type when using benefit plans under the Personnel Workbench.
Note that when creating a bulk holiday/absence period, the individual days within that period are created automatically for you. Also, when loading holidays, be aware that if the pph_authby field (authorised by) is not included, the holiday is treated as a new holiday request rather than a booked holiday.
The pp_course_at table requires fields to specify the particular course/assessment event (stored in table pp_course) the attendance is for. You can include any of the fields from the pp_course table in the load file to identify this, and at a minimum you should include the course code (crs_code) and start date (crs_date). If no pp_course can be found that matches the provided values, a new course/assessment event record is created for the attendance.
The subsequent lines in the load file should each contain a comma-separated list of values for these fields (one line per database record). If the key field values provided match a single existing database record, the load routine attempts to update this record. If the key values do not match a record, a new record is created for the data. If they match multiple records, you receive an error message for the line.
For any field that is held in the database as a code or sequence number, but is ordinarily displayed as an associated description (for example, fields that use lookup codes), the value in the load file can be either the code or the description. The only exception to this is the ppo_seq field for the pp_organisation table, since other fields in this table may be used to identify an existing record.
Example Load file
The file below could load paid holidays for John Smith and Peter Jones, which have been authorised by Sarah West:
pp_orgtime
pph_type=HOLBULK,pph_payable=yes,pph_reason=HOLENT
ppo_seq,pph_date,pph_to,pph_authby
John Smith,23/07/2007,03/08/2007,Sarah West
Peter Jones,22/08/2007,31/08/2007,Sarah West
Examples of Setting Up .csv Files to Load Extra (cex_*) Fields
Example 1: Loading the extra field cex_cha__5 in the Personnel table is shown in the example below, where the cex_type is set to the table and the cex_key* fields are set to the key for the table - in this case, the value of the ppo_seq field:
co_extra
kco,cex_type,cex_key1,cex_cha__5
0,pp_organisation,2153,Thing 1
0,pp_organisation,3085,Thing 2
Example 2: Loading extra fields against the Contacts table (spouse, dependants, emergency contacts) is shown in the example below. In this case, cex_type is set to pp_orgtypes, cex_key1 is set to the value of the ppo_seq, cex_key2 is set to the contact type (CON), cex_key3 thru cex_key6 are set to the surname, first name, middle name, suffix, the key field to the table, and cex_cha__1 is set to the user defined data:
co_extra
kco,cex_type,cex_key1,cex_key2,cex_key3,cex_key4,cex_key5,cex_key6,cex_cha__1
0,pp_orgtypes,2153,CON,Goering,Robert ,Edward,Jr,[email protected]
0,pp_orgtypes,3085,CON,Ford,Jonathan,James,Jr,[email protected]
0,pp_orgtypes,3085,CON,Ford,Jonathan,,,[email protected]
Special Notes on Drug Testing and Sample Load files
The hr_medical and pp_medical tables are closely related: hr_medical is a parent table to pp_medical. Both tables have a sequence number (hrm_seq) as the key value that represents a unique number for the type of drug test administered. The hr_medical data should be loaded first, followed by the pp_medical data. If pp_medical data is loaded first, the load could fail because the system has no corresponding hr_medical data to relate the pp_medical data to.
The hr_medical layout is shown here:
hr_medical
hrm_seq,hrm_tested,hrm_tdate,hrm_notes
6,Cause,07/11/08,"Converted On: 05/04/10"
The pp_medical file layout for personnel tested under the Drug Test entered above is shown here:
pp_medical
ppo_seq,hrm_seq,ppm_tdate,ppm_reason,ppm_disposition,ppm_dispute,ppm_found,ppm_level,ppm_notes,ppm_notif,ppm_ldate,ppm_result,ppm_id,ppm_specimen_type,ppo_dept,job_num,ppo_mgr,ppo_location,ppo_jobtitle,pqm_xml^CONV
001682108,6,07/11/08,"Cause","A",no,"THC","","TRADE CODE SPECIFIC FOR CAUSE",yes,07/17/08,"P","","URINE","400","41034","","","832","C100504"
Load Captions
There are five different types of Captions available. In the PrimeLearn environment, they are listed as follows (and on the screen below).
CH = Character
LO = Logical
DA = Date
DE = Decimal
IN = Integer
On the Personnel Workbench, you can see all of the Captions, as shown below.
If you turn on the debugger (Ctrl+Shift+C) and go to the Section/Fields section, you will see the screen below.
Then search (Ctrl+F) for Caption:
These will be the field names on the Personnel Data Load sheet.