/
Mass Load

Mass Load

The user would need the MODULE_IMPORT and both CREATE and UPDATE for a given entity in order to use mass load.  To check or add, go to SYSTEM/ROLE.

 

The Mass Load program allows a district to import data into the USPS Redesign from an outside source. This may include, but is not limited to, Excel, Access, Lotus and other third party software's that have the option to export data in a CSV (Comma Separated Values) format. The Mass Load program reads the data, imports and validates it, then writes it to the USPS file structures, thus eliminating manual entry. This might be useful for a district converting to USPS or for a district processing mass updates to varying fields.

All possible field values are outlined in the Miscellaneous notes section, under the Mass Load Criteria. Any records with fields not meeting the USPS possible values validity checks are rejected. A message is written to the reports indicating what record included invalid data.

The CSV being read by the Mass Load options can include the fields in any order. The file must contain a header record which includes all the pre-defined field names (Req Column Desc) as the first record in the import file. The pre-defined field names are included in the Mass Load Criteria found in the Miscellaneous notes section of this chapter. The remaining records in the file represent the data values being loaded.

The Custom Report Creator  option under Reports can be used to create a report containing current data. Example-Payroll Items-you are wanting to create a file to use for loading a new rate. (If need more help with creating Detail Reports, Click 'Custom Report Creator')  The Custom Report Creator can be used to create a file with the appropriate headers that are required to be used when processing Mass Load to import data. To do so, Select the Object you wish to import. Once the object is selected, choose the Identifying fields under the appropriate Properties that will be needed to make the changes desired. (Below are lists of header definitions for each Object)

Once you have selected the correct identifying fields (view table below), desired.

The detail report writer will also allow you to select and filter current identifying fields under the Configure Filters tab.

The  Generate Report tab allows you to create the report.

Under the Format option Select the Excel-FieldNames. Enter in a file name and click generate report. This will create an Excel file you can now edit and then Save As a csv file and then can be used to import data using the Utilities/Mass Load option.

Example of Excel-FieldNames:

 

Using the Mass Load Feature

 

Go to Utilities and Select Mass Load

 

  • USPS Load File-Click 

    and locate the file you wish to load. The USPS Load File can  be an import file supplied by a third party software as well as a custom created report.  The file must be in comma separated values (CSV) format.

  • Importable Entities-Select from the drop down 

     the Entity you wish to load the data into.

  • Click 

 

MASS LOAD CRITERIA

Each Object to be imported, has to be created on a separate .CSV file. The Identifying Fields below have to entered exactly how they are written. For more Identifying and Optional fields, please use the Custom Report Writer to find the field names. 'Select Object' to open up the Property names and hover over the name with your mouse pointer: example. ('RegularItemConfig' Object - Employer Health Coverage identifying field would be employerHealthCoverage.

Mass Load will always attempt to change the value in the database with the value (blank or not) in the import file.  So, therefore, any blank/empty values in a column in the import file will result in setting the value in the database to empty.  In the case of numeric values, empty equates to zero, for strings, it will either be null or an empty string.  There is no defaulting, it will set the value to what the user specifies.

If a new record is being created in the database, and a field that is NOT in the import file would normally be defaulted (as when adding via the UI), then it will end up with default values for certain fields.

Mass Load will ignore blank lines and lines with just commas (no data) in the file.

 

Object to be imported

Identifying fields that must be included in the file

Optional fields

Object to be imported

Identifying fields that must be included in the file

Optional fields

Compensation

Allows all Compensations (Non Contract, Contract and Legacy Compensations to be updated

Contract and Non Contract Compensations can be added (created) but Legacy CANNOT be added

 

To Update Compensation record:

code  (this replaces the id of the compensation)  Has to match what the employee's code is exactly. 

position.number

position.employee.number

type

  • Legacy

  • Contract

  • NonContract

 

To Add a new Compensation record:

code - (enter unique code: Example 1234-33. cannot be left blank) 

position.number

position.employee.number

type

  • Contract

  • NonContract

payUnit

  • Daily

  • Hourly

payPlan

  • Biweekly

  • Semimonthly

  • Monthly

Compensation Optional fields

Compensation:

type

jobCalendar.description

jobCalendar.type

jobCalendar.archived

description (compensation)

label (No apostrophe is allowed in the label name)

dateRange.startDate (compensation)

dateRange.stopDate (compensation)

payPlan

  • Biweekly

  • Semimonthly

  • Monthly

payUnit

  • Daily

  • Hourly

unitAmount

retirementHours

strsAdvance

  • true

  • false

supplementalTaxOption

  • ApplyAnnuitiesToSupplemental

  • ApplyAnnuitiesToRegular

  • None

archived

  • TRUE

  • FALSE

contractDaysWorked

hoursInDay

primaryCompensation

  • true

  • false

Contract:

payPerPeriod

contractAmount  (No comma in the amount - 2000.00)

contractObligation  (No comma in the amount - 2000.00)

paysInContract

retroNextPay

stretchPay

  • true

  • false

Salary Schedule:

salaryScheduleColumn - Only Numeric

salaryScheduleId - Alpha and Numeric

salaryScheduleStep - Only Numeric

State Reporting:

reportToEmis

customFields.localContractCode.value

Historical Context:

calendarDate.startDate

calendarDate.stopDate

Legacy Compensations Headers:

  • paysPaid

  • contractWorkDays

Adjustment Journal

Keep in mind if the adjustment should apply to a specific payroll item you will need to supply the Payroll Item Code and Position Number (if position level payroll item) to correctly create adjustments

Adjustments can only be created via mass load. You will not be able to update existing adjustments via mass load.

 

employee.number

payrollItem.code

amount

transactionDate

type - The Type column must contain one of the following values:

 

payrollItem.position.number (if applicable)

description

imported

  • false

applyToEmployerPickup  (Affects Quarter Report for Medicare Pickup employee's applicable gross, check 'Quarter To Date' box)

 monthToDate     *(See note below)

 quarterToDate    *(See note below)

 yearToDate         *(See note below)

 fiscalYearToDate  *(See note below)

 

*The 'default' value for all 4 to date flags is TRUE, so if there are no columns listed specifying the load for to date data to be FALSE, it will always be TRUE.  Even if the columns are not listed on the report.

Example- you are needing to change just YTD data. You would make sure that each record for YTD is set to TRUE. You would also need to have the MTD, FYTD and QTD columns on the spreadsheet with FALSE listed so that only the YTD data is updated.

Employee

 

*Note*

  • Enter dates as MM/DD/YYYY

  • For dollar amounts, omit the $ sign 

    • For example 100.00

 

INFO:  If updating Employee ID to SSN, the Old History attached to the Employee ID will remain

number

name.lastName (to add)

name.firstName (to add)

 

Required Only when updating employee's EMPID:

id = the database id

 

Optional Employee fields

id = the database id

  • required ONLY when updating an employee's EMPID

  • leave blank when creating new Employee

Identification:

ssn

  • XXXXXXXXX (String of digits no dashes or slashes)

customFields.credentialID.value

customFields.emisId.value

Name:

name.lastName

name.firstName

name.middleName

name.suffix

Legal Name:

legalName.lastName

legalName.firstName

legalName.middleName

legalName.suffix

Address:

address.street1

address.street2

address.city

address.state

address.postalCode

address.foreignAddress

address.province

address.country

Contact:

workPhone

homePhone

homePhoneUnlisted

  • true

  • false

primaryEmailAddress

secondaryEmailAddress

otherEmailAddress   **SERS New Hire report uses this field for reporting**

General:

maritalStatus

  • Unstated

  • Married

  • Single

archived

  • true

  • false

customFields.eligibleForRetirement.value

  • true

  • false

emailDirectDeposit

  • true

  • false

gender

  • Male

  • Female

newHireReportedODJFS

  • true

  • false

odjfsReportable

  • true

  • false

osdiCode

customFields.partTime.value

  • true

  • false

reportToEmis

  • true

  • false

customFields.spouseFirstName.value

customFields.subDays.value

Dates:

birthDate

hireDate

lastPaidDate

odjfsHireDate

terminationDate

Evaluation:

customFields.lastEvaluation.value

customFields.nextEvaluation.value

Experience: Note: cannot be left blank, enter 0

accredDistrictExperience

authorizedExperience

buildingExperience

districtExperience

militaryExperience

nonOhioPrivateExperience

nonOhioPublicExperience

ohioPrivateExperience

ohioPublicExperience

principalExperience

purchasedExperience

retireSystemExperience

totalExperience

tradeExperience

Race:

primaryRace   **required if adding new Employee**

  • A - Asian

  • B - Black or African American (Non-Hispanic)

  • H - Hispanic

  • I - American Indian or Alaskan Native

  • M - Multiracial

  • N - Not Specified

  • P - Native Hawaiian or Other Pacific Islander

  • W - White, Non-Hispanic

race.americanIndianAlaskaNative

  • true

  • false

race.asian

  • true

  • false

race.black

  • true

  • false

race.nativeHawaiianPacificIslander

  • true

  • false

race.white

  • true

  • false

race.hispanicLatinoOption

  • HispanicOrLatino

  • NotHispanicOrLatino

  • Unknown (Information has not been recollected)

Standard Payroll:

checkDistribution

customFields.payrollCode1.value

customFields.payrollCode2.value

customFields.payrollDate1.value

customFields.payrollDate2.value

customFields.payrollText1.value

State Reporting:

eceQualification (leaving blank will clear this field)

  • ASTERISK - Not Applicable

  • ONE - Associated in Early Childhood Education or Child Development

  • TWO - Bachelor in Early Childhood Education or Childhood Development

  • THREE - Enrolled in an Associate Degree program in Early Childhood ED

  • FOUR - Enrolled in Bachelors Degree program in Early Child Ed, Child Devel

  • FIVE - Enrolled in Masters or higher Degree program in Early Childhood Ed

  • SIX - Masters or higher in Early Child Ed, Child Develop or Approved Relat

customFields.degreeType.value

  • 0 - Non Degree

  • 1 - Associate

  • 2 - Bachelors

  • 3 - Masters

  • 4 - Education Specialist

  • 5 - Doctorate

  • 6 - Other

  • 7 - Less Than High School Diploma

  • 8 - High School Diploma

  • 9 - GED Diploma

customFields.handicapStatus.value

customFields.longTermIllness.value

customFields.nonCertificateEmployeeID.value

otherCredentials (leaving blank will clear this field)

  • ASTERISK - Not Applicable (default)

  • ONE - Worked toward a CDA (Child Development Associate)

  • TWO - CDA Completed

  • THREE - No CDA AND not working toward a CDA

  • FOUR - Working toward an Associate Degree

customFields.semesterHours.value

 

Date Code

Core>Date Code>Date Text Field 

name

propertyName

 

group

Employee -Custom Date Fields

 

number

customFields.testDate.date (Date to be imported)

customFields.testDate.dateText (Description)

 

In this example 'testDate' is the property name of the Date Detail to be loaded.  This part of the header will be different for each Date. This can be found on the Date Codes view.

Example a Date Code called "Fingerprinting" is created

Example of date header- customFields.fingerprinting.date.

Example of text header-customFields.fingerprinting.dateText

 

Multiple dates can be imported per employee per file as long as each date has the correct header.

 

Position

employee.number - Employee Number

number - Pos #

retirementCode (only when adding a new position) - Retirement Code

  • SERS

  • STRS

  • None

 

Optional Position fields

archived

  • FALSE

  • TRUE

positionDescription

jobStatus

  • Active

  • Inactive

  • Deceased

  • Terminated

payGroup.code

appointmentType

  • Certificated