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.

USPS-R always use 'true'/'false' (lowercase) for all boolean fields (flags). Example-Employee--odjfReportable, reportToEmis



Object to be importedIdentifying fields that must be included in the fileOptional 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:

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:

    • HealthInsurance
    • MovingExpense
    • TaxableBenefits
    • FringeBenefits
    • LifeInsurance
    • AdoptionAssistance
    • DependentCare
    • ThirdPartyPay
    • VehicleLease
    • TotalGross
    • ApplicableGross (this replaces TaxableGross in Adjustment Journal Mass Loading only)
    • AmountWithheld
    • ApplicableAnnuities
    • BoardAmount
    • EarnedIncomeCredit
    • AdditionalWithholding
    • RehiredRetireeBoardAmount
    • RehiredRetireeAmountWithheld
    • RehiredRetireeGross
    • OdjfsGross
    • OdjfsWeeks
    • SersRetirementDays
    • SersRetirementHours
    • StrsRetirementDays
    • StrsRetirementHours
    • EmisAttendance
    • EmisAbsence
    • BoardPickupAmount
    • AdvancedSickLeave


payrollItem.position.number (if applicable)

description

imported

  • false

 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 

  • example 0.00


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


















id = the database id

  • required ONLY when updating an employee's EMPID
  • leave blank when creating new Employee

number

name.lastName (to add)

name.firstName (to add)





























































































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:

customFields.workPhone.value

customFields.homePhone.value

customFields.homePhoneUnlisted.value

primaryEmailAddress

secondaryEmailAddress

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

General:

maritalStatus

archived

  • true
  • false

customFields.eligibleForRetirement.value

  • true
  • false

emailDirectDeposit

  • true
  • false

customFields.gender.value

  • M (Male)
  • F (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:

customFields.accredDistrictExperience.value

customFields.authorizedExperience.value

customFields.buildingExperience.value

customFields.districtExperience.value

customFields.militaryExperience.value

customFields.nonOhioPrivateExperience.value

customFields.nonOhioPublicExperience.value

customFields.ohioPrivateExperience.value

customFields.ohioPublicExperience.value

customFields.principalExperience.value

customFields.purchasedExperience.value

customFields.retireSystemExperience.value

customFields.totalExperience.value

customFields.tradeExperience.value

Race:

customFields.primaryRace.value   **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

Standard Payroll:

checkDistribution

customFields.payrollCode1.value

customFields.payrollCode2.value

customFields.payrollDate1.value

customFields.payrollDate2.value

customFields.payrollText1.value

State Reporting:

customFields.ECEQualification.value

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • *

customFields.degreeType.value

  • 0
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

customFields.handicapStatus.value

customFields.longTermIllness.value

customFields.nonCertificateEmployeeID.value

customFields.otherCredentials.value

  • 1
  • 2
  • 3
  • 4
  • *

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


archived

  • FALSE
  • TRUE

positionDescription

jobStatus

  • Active
  • Inactive
  • Deceased
  • Terminated

payGroup.code

appointmentType

  • Certificated
  • Classified

buildingCode.code

departmentCode.code

customFields.extendedService.value

fte

hireDate

positionDate.startDate

positionDate.stopDate

customFields.raiseDate.value

terminationDate

supervisor.number

timesheetRequired

  • FALSE
  • TRUE

Eligibility Flags:

eligibleForPersonLeave

  • FALSE
  • TRUE

eligibleForSickLeave

  • FALSE
  • TRUE

eligibleForVacation

  • FALSE
  • TRUE

EMIS Related Information:

reportToEmis

  • FALSE
  • TRUE

customFields.positionCode.value - Position Code

customFields.stateReportingAppointmentType.value (only use the numbers)

  • 1 - Certificated
  • 2 - Classified
  • 3 - Internship
  • 5 - Veteran per ORC 3319.283

customFields.positionType.value   (only use the letters)

  • R - Regular
  • S - Supplemental (coaches,student activities, etc.)
  • T - Temporary

customFields.positionStatus.value   (only use the letters)

  • A - Contracted personnel – Agency
  • C - Active/continuing employee in same position in district
  • I - Contracted personnel
  • P - Leave of absence
  • U - No longer employed by district in this position

customFields.fullTimeEquivalence.value

  • Numeric field

customFields.specialEducationFullTimeEquivalence.value 

  • Numeric field

customFields.lowGrade.value (only use the numbers, letters, or two asterisks for Not Applicable.)

  • ** - Not Applicable   
  • 01 - First Grade
  • 02 - Second Grade
  • 03 - Third Grade
  • 04 - Fourth Grade
  • 05 - Fifth Grade
  • 06 - Sixth Grade
  • 07 - Seventh Grade
  • 08 - Eighth Grade
  • 09 - Ninth Grade
  • 10 - Tenth Grade
  • 11 - Eleventh Grade
  • 12 - Twelfth Grade
  • KG - Kindergarten
  • PS - Preschool

customFields.separationReason.value   (only use the numbers)

  • * - Not applicable. Employee is still employed by district.
  • 1 - Retirement
  • 3 - Employer initiated
  • 4 - Took a job outside the field of education
  • 5 - Resigned - Took another education job in Ohio
  • 6 - Resigned - Took another education job out of state
  • 7 - Resigned - Unknown or does not fit into options 4, 5 or 6
  • 8 - Employee accepted a new position in the district
  • 9 - Deceased

customFields.paraprofessional.value

  • *
  • N
  • Y

customFields.contractAmount.value 

customFields.contractWorkDays.value

customFields.hoursInTheDay.value

customFields.highGrade.value (only use the numbers, letters, or two asterisks for Not Applicable.)

  • ** - Not Applicable 
  • 01 - First Grade
  • 02 - Second Grade
  • 03 - Third Grade
  • 04 - Fourth Grade
  • 05 - Fifth Grade
  • 06 - Sixth Grade
  • 07 - Seventh Grade
  • 08 - Eighth Grade
  • 09 - Ninth Grade
  • 10 - Tenth Grade
  • 11 - Eleventh Grade
  • 12 - Twelfth Grade
  • KG - Kindergarten
  • PS - Preschool

customFields.separationDate.value

customFields.paraprofessionalHireDate.value

customFields.buildingIRN.value

customFields.experienceCurrentClass.value

Funding Source:

customFields.assignmentArea.value   (only use the numbers)

  • 999050 - Art Education K-8
  • 999140 - Title I Programs
  • 999270 - Preschool General Education
  • 999350 - Food Service
  • 999365 - LEP Instructional Program
  • 999370 - General Education K-12
  • 999380 - Gifted and Talented
  • 999412 - Preschool Special Education
  • 999414 - Special Education K-12
  • 999418 - Physical Education K-8
  • 999520 - Maintenance/Construction/Grounds
  • 999570 - Music Education K-8
  • 999725 - Vocational Special Education Coordinator Services
  • 999790 - Transportation Services
  • 999800 - Career-Technical Programs/Career Pathways
  • 999805 - Career-Technical Education-Apprenticeship Program
  • 999895 - Career Assessment Specialist Services

customFields.fundingSourceCode1.value   (only use the letters)

  • A - State Auxiliary Funds
  • B - Other State Funds
  • F - Federal Special Education Part - B IDEA Grant (school age)
  • G - Federal Title I Funds
  • I - State Poverty Based Assistance Funds
  • J - Federal Head Start Program Funds
  • L - Local/State Foundation Funds (Other than State Unit Funding)
  • N - TANF/OWF
  • O - Other Federal Funds
  • P - Federal Preschool Special Education Part - B IEDA Federal Grant
  • S - State Funds - Public Preschool Program
  • T - Private/Tuition
  • U - State Unit Funding (use Fund Source Z for Preschool Units)
  • X - Federal Reading First Grant
  • Y - Title III
  • Z - State Preschool State Unit Funding

customFields.percent1.value

customFields.fundingSourceCode2.value   (only use the letters)

  • A - State Auxiliary Funds
  • B - Other State Funds
  • F - Federal Special Education Part - B IDEA Grant (school age)
  • G - Federal Title I Funds
  • I - State Poverty Based Assistance Funds
  • J - Federal Head Start Program Funds
  • L - Local/State Foundation Funds (Other than State Unit Funding)
  • N - TANF/OWF
  • O - Other Federal Funds
  • P - Federal Preschool Special Education Part - B IEDA Federal Grant
  • S - State Funds - Public Preschool Program
  • T - Private/Tuition
  • U - State Unit Funding (use Fund Source Z for Preschool Units)
  • X - Federal Reading First Grant
  • Y - Title III
  • Z - State Preschool State Unit Funding

customFields.percent2.value

customFields.fundingSourceCode3.value   (only use the letters)

  • A - State Auxiliary Funds
  • B - Other State Funds
  • F - Federal Special Education Part - B IDEA Grant (school age)
  • G - Federal Title I Funds
  • I - State Poverty Based Assistance Funds
  • J - Federal Head Start Program Funds
  • L - Local/State Foundation Funds (Other than State Unit Funding)
  • N - TANF/OWF
  • O - Other Federal Funds
  • P - Federal Preschool Special Education Part - B IEDA Federal Grant
  • S - State Funds - Public Preschool Program
  • T - Private/Tuition
  • U - State Unit Funding (use Fund Source Z for Preschool Units)
  • X - Federal Reading First Grant
  • Y - Title III
  • Z - State Preschool State Unit Funding

customFields.percent3.value



Position-Custom Date field

employee.number

number

customFields.testingDate.value


Payroll Item Configuration (Select PayrollItemConfiguration Entity)

The import file can contain multiple payroll item configuration types in one import.  Click here for a sample load file.


Adopt Assistance Item Config

Annuity Item Config

Child Support Item Config

City Tax Item Config

Dependent Care Item Config

Employer Sers Item Config

Employer Strs Item Config

Federal Tax Item Config

Health Savings Account Item Config

Medical Saving Item Config

Medicare Tax Item Config

Ohio State Tax Item Config

Osdi Tax Item Config

Regular Item Config

Savings Bond Item Config

Sers Annuity Item Config

Sers Buy Back Annuity Item Config

Sers Item Config

Social Security Tax Item Config

State Tax Item Config

Strs Annuity Item Config

Strs Buy Back Annuity Item Config

Strs Item Config

Add a new Payroll Item Configuration:

payee.id OR payee.number (one of these)

code

type

  • AdoptionAssistance
  • Annuity
  • ChildSupport
  • CityTax
  • DependentCare
  • EmployerSers
  • EmployerStrs
  • FederalTax
  • HealthSavingsAccount
  • MedicalSavings
  • MedicareTax
  • OhioStateTax
  • OsdiTax
  • Regular
  • SavingsBond
  • SersAnnuity
  • SersBuybackAnnuity
  • Sers
  • SocialSecurityTax
  • StateTax
  • StrsAnnuity
  • StrsBuybackAnnuity
  • Strs

paymentCycle

  • None
  • EveryPayroll
  • Monthly
  • Quarterly
  • Annually


Modifying existing Payroll Items Configurations:

code

 

addGrossToPayablesReport

Adoption Assistance Item:

name

abbrev

w2abbrev

paymentCycle

suppressSsnId

employerHealthCoverage

voluntary

required

showOnCreateWizard


Annuity Item:

achSource

annuityType

cca

ccaDescription

certifiedObj

city

classifiedObj

designatedRoth

employerHealthCoverage

jobLevel

medicarePickup

name

nonWages125

osdiCode

otherObject

postalCode

printEmployerAmount

rita

ritaDescription

reportToCCA

required

showOnCreateWizard

state

stateId

payee.address.street1

payee.address.street2

payee.address.city

payee.address.state

payee.address.postalCode

payee.address.foreignAddress

payee.address.province

payee.address.country

payee.phone.internationalCode

payee.phone.number

payee.phone.extension

payee.phone.unlisted

payee.fax.internationalCode

payee.fax.number

payee.fax.unlisted

suppressSsnId

taxEmployerAmounts

taxEntityCode

taxNonCashEarn

voluntary

w2abbrev

Payroll Item (Select the PayrollItem Importable Entities)

The import file can contain multiple payroll items and employee's in one import.

Example of a Payroll Item Mass Load csv:

payItemMultipleCreateTest (1).csv



Adopt Assistance Item

Annuity Item

Child Support Item

City Tax Item

Dependent Care Item

Employer Sers Item

Employer Strs Item

Federal Tax Item

Health Savings Account Item

Medical Saving Item

Medicare Tax Item

Ohio State Tax Item

Osdi Tax Item

Regular Item

Savings Bond Item

Sers Annuity Item

Sers Buy Back Annuity Item

Sers Item

Social Security Tax Item

State Tax Item

Strs Annuity Item

Strs Buy Back Annuity Item

Strs Item































Payroll Item Error Adjustment

Use Importable Entities: payrollItemErrorAdjustment





Payroll Item Employer Error Adjustment

Use Importable Entities: payrollItemEmployerErrorAdjustment


configuration.code

employee.number

position.number - only needed if payroll item is deduction by job



























































code

employeeNumber

positionNumber

amount

date

description



code

employeeNumber

positionNumber

amount

date

description

employee.ssn

accountNumber

archived

  • FALSE
  • TRUE

For ACH fields - use the following below:  Dependent Care and HSA

  • achConfiguration.achAccountNumber
  • achConfiguration.achDirectDeposit
    • AutomatedDepositDemandCreditRecords
    • AutomatedDepositSavingsAccountCreditRecords
  • achConfiguration.achDestination.routingNumber
    • must be a valid routing number from ACH Destination grid
  • achConfiguration.achXrefCode

additionalWithHolding

advanceErrorAdjustment

Covid19 Fields

  • customFields.COVID19OthersAmount.value
  • customFields.COVID19EmergencyFamilyAmount.value
  • customFields.COVID19SelfAmount.value

deductionType  (for City Tax Item)

  • RESIDENCE
  • EMPLOYMENT
  • NOT_APPLICABLE

effectiveRetirmentDate

Employee Deduct Max Amount-use the following below:

  • employeeDeductMaxAmount.maxAmount
  • employeeDeductMaxAmount.specificDate
  • employeeDeductMaxAmount.deductMaxType

                    -Annual
                    -Fiscal
                    -SpecificDate

Employer Deduct Max Amount-use the following below:

  • employerDeductMaxAmount.maxAmount
  • employerDeductMaxAmount.specificDate
  • employerDeductMaxAmount.deductMaxType
        -Annual
-Fiscal
-SpecificDate

employee.name.firstName

employee.name.lastName

employee.name.middleName

employerRate -   (No comma in the amount Ex. 2000.00)

New W4 fields

useNewW4

      • True
      • False

filingStatus

      • SingleOrMarriedFilingSeparately
      • MarriedFilingJointlyOrQualifyingWidow
      • HeadOfHousehold

twoLikeJobs

dependentAmount

otherIncome

deductionAmount

fullOrPartTime

  • FullTime
  • PartTime

increasedCompensation

  • TRUE
  • FALSE

lastModifiedDate

maritalStatus

  • Married (case sensitive)
  • Single (case sensitive)

newEmployee

  • TRUE
  • FALSE

numberOfExemptions

osdiCode

PayrollItemType

payCycle

  • EveryPay
  • FirstPay
  • SecondPay
  • FirstAndSecondPay
  • FirstAndLastPay

pensionPlan

  • Automatic - Automatically check the pension plan box based on retirement
  • Yes - Yes, check the pension plan box
  • No - No, never check the pension plan box

percentOfGross

position.number - only needed if payroll item is deduction by job

rate -  (No comma in the amount Ex. 2000.00)

rateType

  • Fixed (case sensitive)
  • Percent (case sensitive)
  • Table (for Tax Tables) (case sensitive)

rehiredDate

rehiredRetiree

  • TRUE
  • FALSE


activeDateRange.startDate (not specific on how date is entered)

activeDateRange.stopDate (not specific on how date is entered)

surchargeExempt

  • TRUE
  • FALSE

type

  • Strs
  • Sers

typeOfBenefit


Retire Rehired Reporting - 450 Payroll Item

  • customFields.effectiveRetirementDate.value
  • customFields.ohioRetirementSystem.value
    • SERS
    • STRS
  • customFields.employmentComplianceWithORC3307353
    • (No, the requirements of Section 3307.3653 were not met)
    • (Yes, the requirements of Section 3307.353 were met)
  • customFields.employmentUnderORC3307353
    • (No, the retiree was not employee under Section 3307.3653)
    • (Yes, the retiree was employed under Section 3307.353)
  • customFields.typeOfBenefit
    • S  (Age of Service)
    • (Disability)







Attendance Journal

Load files may contain only Attendance, only Absences, or a combination of both Absences and Attendances.


Example of Attendance and Absence Journal Modify Template


Example of Attendance and Absence Journal Add Template

employee.number

id (only if modifying a current record)

position.number

compensation.code

type

  • Absence
  • Attendance

category (Absence)

  • Calamity
  • Dock
  • Holiday
  • JuryDuty
  • Military
  • Other
  • Personal
  • Professional
  • Sick
  • Unknown
  • Vacation

category (Attendance)

  • Attendance
  • Substituting

activityDate

  • YYYYMMDD

length


unit

  • Daily
  • Hourly
  • Weeks
  • None


appliedToBalance

appointmentType

  • Certificated
  • Classified

dayOfWeek

id (Compensation)

payDate

subCategory

substituteFor - (enter employee number of the substitute)

type

usasPostingIndicator

Leaves


Sick Leave

Personal Leave

Vacation Leave

If wanting to zero out the balances, use Mass Load Accumulation Transaction:

employeeNumber

leaveType

length  (enter a negative figure to zero out balance)


employee.number

type

  • SICK
  • VACATION
  • PERSONAL

The leaves will load if they are not eligible but will not display in the Leaves grid until the Eligibility for the leave is checked in Position grid.

Warnings will be given:

Warning: Employee XXXXXXXX is not eligible for XXXXXXX leave.  XXXXXXX leave entry was loaded but will not be displayed when viewing leaves for the employee.

Warning: Employee XXXXXXXX is not eligible for any leave. Leave entries were loaded for this employee, but this employee will not be displayed when viewing leaves.

You must provide employee.number and type in your load file.  If Leaves do not exist for a given employee during the load, then the leaves will be added.  If the employee already has Leaves, then they will be updated. 


For any fields that apply to sick leave only, you will have to provide the header name in the header row, and then supply a value for the sick leave record.  Vacation and personal should contain commas as placeholders for the sick leave only fields.

For example:

If during a modify load you change the leave unit for an employee's existing leave, from daily to hourly or vice versa, you will need to provide an additional field in your load file with a header value of hoursInDay.  This is a decimal value that will be used to convert the leave balance.  If converting from daily to hourly, the existing leave balance will be multiplied by the hoursInDay value to get the new balance.  If converting from hourly to daily, the existing leave balance will be divided by the hoursInDay value to get the new balance.  The hoursInDay must be between 1 and 8 and contain no more than 2 decimals.  To convert the balance, all leave types must have the same leave unit and hoursInDay in the load file.

If header column field is not included, then data is not changed or defaulted and all other fields are correct.

**NOTE**If a field is in the header and no value given for employee, then it defaults to a default value such as 0 if numeric (ex. accumPerMonth, maxLeaveAmount).

accumPerMonth

accumulateBasedOnHours

  • true
  • false

advancedUnitsUsed

currentServiceHours

employee.name.lastName

employee.name.firstName

leaveUnit

  • Daily
  • Hourly

maxAdvanceLeave

maxLeaveAmount

personalLeaveAccum

resetValue

serviceHoursAccumulator


FOR SICK LEAVE ONLY:

accumulateBasedOnHours

  • true
  • false

currentServiceHours

maxAdvanceLeave

serviceHoursAccumulator

Compensation Journal

Compensation

Headers that are required for compensation journal entry:

  • id  (this is a string of characters and numbers) 838679cb-e524-45a2-bdcb-4fdb900916f3 - This column header can be found under the More option.
  • position.employee.name.lastName
  • position.employee.name.firstName
  • position.employee.number
  • position.number
  • amountPaid - Big Decimal formatted to 2 decimals
  • amountEarned - Big Decimal formatted to 2 decimals
  • amountDocked - Big Decimal formatted to 2 decimals
  • daysWorked - Big Decimal formatted to 2 decimals
  • description
  • paidPaid - Integer (optional)

Once the Excel spreadsheet has been created, you must manually add in all the following headers:

  • amountPaid
  • amountEarned
  • amountDocked
  • daysWorked
  • description
  • paysPaid

In the spreadsheet file, you need to manually enter in the amount fields, days Worked and description. The amount fields and days worked cannot be blank. Enter zeros if no amount is needed.-Note: Adding zeros to these fields will not change the current value if one exists.

**NOTE**Once the spreadsheet file has been created and data has been filled in, go to Mass Load and select the CompensationJournal entity.


Payroll Accounts


The following will be checked on import:

  • Valid header
  • Employee with employee number exists
  • Position with employee number and position number exists
  • Expenditure Account with 8 account codes exists
  • Business Rules that occur for Payroll Accounts
    • Active Percentage accounts must total 100%

**NOTE** If you get a SEVERE Error, no data will be updated for anyone on the file


If wanting to create a spreadsheet to update current Payroll Accounts, use Custom Report Creator and select the Object, Payroll Account (No 's' at the end)

Below are the Properties that can be selected using Payroll Account and Report Options, Excel-FieldNames:

  • Number
  • Pos #
  • Payroll Account Id = id once created in Excel
    • use Expenditure Account id
  • Fund
  • Function
  • Object
  • Scc
  • Subject
  • Operational Unit
  • Instructional Level
  • Job
  • Rate
  • Active
  • Start Date
  • Stop Date
  • Employer Distribution
  • Leave Projection
  • Maximum

Once spreadsheet is created, column headers may need to be updated to match the correct field names to the right

positionNumber

employeeNumber

id -

  • The Id property of an existing pay account for an employee.
  • If left blank, a new pay account will be created.

rateType

  • Percent  (case sensitive)
  • Fixed  (case sensitive)

status

  • Active  (case sensitive)
  • Inactive   (case sensitive)
  • SpecificMiscellanous   (case senstive)
  • MaximumAmountInEffect   (case senstive)

Rate (once .csv is created, change the header to be 'chargeAmountOrPercent'

fund

func

obj

scc

subj

opu

il

job


startDate

  • YYYYMMDD
  • MMDDYYYY
  • YYYY/MM/DD
  • MM/DD/YYYY
  • YYYY-MM-DD
  • MM-DD-YYYY

stopDate

  • YYYYMMDD
  • MMDDYYYY
  • YYYY/MM/DD
  • MM/DD/YYYY
  • YYYY-MM-DD
  • MM-DD-YYYY

leaveProjection

  • true, True, TRUE   (NOT case sensitive)
  • false, False, FALSE  (NOT case sensitive)

employerDistribution

  • true, True, TRUE   (NOT case sensitive)
  • false, False, FALSE   (NOT case sensitive)

maximum   

  • **NOTE** Maximum amount needs to have the Status set as MaximumAmountInEffect
  • Maximum Amount as a dollar amount
    • Example;
      • 50.99
      • 25.50

sortOrder

  • Numerical order
    • Example:
      • 1
      • 2
      • 3
      • 4


Accumulation Transaction

Use Accumulation Transaction to clear out Balances, use a negative length.

employeeNumber

leaveType

  • SICK
  • PERSONAL
  • VACATION

length

positionNumber

transactionDate

  • YYYYMMDD
  • MMDDYYYY
  • YYYY/MM/DD
  • MM/DD/YYYY
  • YYYY-MM-DD
  • MM-DD-YYYY

description

employee.name.lastName

employee.name.firstName

leaveUnit

  • Daily
  • Hourly

Pay Distributions

**NOTE** If employee fails for 1 transaction, they will fail for all transactions for that employee. It will likely give same error message for all transactions for that employee

employeeNumber

code (this field cannot be updated to be something different in Mass Load or using Mass Change, has to be manually updated)

type

  • DIRDEP
  • CHECK

rateType

  • Percent (case sensitive)
  • Fixed (case sensitve)

rate

startDate

  • YYYYMMDD
  • MMDDYYYY
  • YYYY/MM/DD
  • MM/DD/YYYY
  • YYYY-MM-DD
  • MM-DD-YYYY

stopDate

  • YYYYMMDD
  • MMDDYYYY
  • YYYY/MM/DD
  • MM/DD/YYYY
  • YYYY-MM-DD
  • MM-DD-YYYY

priority

Direct Deposit Specific Headers

  • routingNumber
  • transferType
    • PayrollACHTransfer
    • HealthSavingsAccountACH
  • transferCode
    • 001
  • accountNumber
  • abbreviation
  • directDepositType
    • Required for creating Direct Deposits
    • Valid Options
      • AutomatedDepositDemandCreditRecords
      • PrenotificationOfDemandCreditAuthorization
      • AutomatedDepositSavingsAccountCreditRecords
      • PrenotificationOfSavingsCreditAuthorization

**NOTE** The transferType and transferCode are used together to find the ACH Source for a direct deposit.

User Adjustment Payables Ledger

(Process Outstanding Payables/Payables Adjustments)

Headers (all required) -  This mass load will only CREATE new items, you cannot use this to update existing payables:
  • employeeNumber
  • positionNumber -
    • Header is required, but the value can be left blank.  Is only used to find a payroll item by Code, Employee and Position.  If the payroll item being loaded doesn't have a position number, then it will search by Code and Employee
  • payrollItemCode
  • description
    • If no description is added, it will provide “User Adjustment Payables Mass Load” as the description
  • employerAmount
  • employeeAmount

Future Pay Amount


**NOTE**

If wanting to update different fields for different employees on the same load, you will need to put the old (current) values wherever you don’t want it updated.  You must supply a value for most fields because null value does not work for most fields. 


To add and update Future Pay transactions in one upload, you would have to supply all the values in the current because null values (,,) will give errors for most fields such as true, false.  Some have default values if not supplied.

Add Future Pay Amount

  • id - needs to be in the load file with no value entered
  • employeeNumber
  • positionNumber
  • compensation.code
  • payType - 

    • AdoptionAssistance
    • Dock
    • Irregular - (Cannot be used when employee is in Advance)
    • LifeInsurancePremium
    • Miscellaneous
    • NonCashTaxableBenefit
    • NonTaxableReimbursement
    • Overtime
    • PayOffAccrued
    • Regular - (Cannot be used when employee is in Advance)
    • Retro
    • ShiftPremium
    • Termination
  • units
  • rate - if rate is not supplied in csv file, it uses the compensation rate for that employee/position



Update Future Pay Amount

  • id - (this is a string of characters and numbers) 838679cb-e524-45a2-bdcb-4fdb900916f3 - This column header can be found under the More option.
  • employeeNumber
  • positionNumber
  • compensation.code
  • payType - 

    • AdoptionAssistance
    • Dock
    • Irregular
    • LifeInsurancePremium
    • Miscellaneous
    • NonCashTaxableBenefit
    • NonTaxableReimbursement
    • Overtime
    • PayOffAccrued
    • Regular
    • Retro
    • ShiftPremium
    • Termination


effectiveDate - in YYYYMMDD format

supplementalTaxOption

  • None
  • ApplyAnnuitiesToRegular
  • ApplyannuitiesToSupplemental

rate - if rate is not supplied in csv file, it uses the compensation rate for that employee/position

retireHours

description

appliesForRetirement

  • true, True, TRUE   (NOT case sensitive)
  • false, False, FALSE  (NOT case sensitive)

supplemental

  • true
  • false

specificPayAccount.rateType - (Must include in file if using Specific Pay Accounts)

  • Percent
  • Fixed

specificPayAccount.amountCharged - (Must include in file if using Specific Pay Accounts)

specificPayAccount.accountOrXrefCode

  • (provide expenditure account xRef OR full account code)
  • Pay accounts will be added to the employee's Pay Accounts (a Specific Pay Account) if none exists for employee/position/pay account.
  • If a Specific pay Account with a matching expenditure account already exists (but with a different charge amount) this will NOT create a new Specific Pay Account.  It will see that available Specific Pay Account and use the existing one.

specificPayAccount.leaveProjection - (Must include in file if using Specific Pay Accounts)

  • true, True, TRUE   (NOT case sensitive)
  • false, False, FALSE  (NOT case sensitive)

specificPayAccount.employerDistribution - (Must include in file if using Specific Pay Accounts)

  • true, True, TRUE   (NOT case sensitive)
  • false, False, FALSE  (NOT case sensitive)

When adding specific accounts, you have to provide either the xref code of the expenditure account OR the full account code (no hyphens between account dimensions). 

If you are updating a future pay and including a specific account, if the account you provide in the load file already exists on the future pay, then the specific account will be updated with the account values you provide in the load file.  If the account does not exist on the future pay, it will be added as a new specific account.

ACH Destination

Core>ACH Destination

routingNumber (Must be 9 characters)

description


Payee

Example of Payee Mass Load Headers

Payee Mass Load Headers.txt

id

  • always required when updating
  • leave blank if crating new Payee

number

name

secondName

electronicPayment

archived

adress.street1

address.street2

address.city

address.state

address.postalCode

address.foreignAddress

address.province

address.country

phone.number

phone.internationalCode

phone.extension

phone.unlisted

fax.number

fax.internationalCode

fax.extension

fax.unlisted

Warnings and Errors

No Position found for # , Employee #XXXXXXXXX

'For input string: "" - error given when load values for employee id or position number are not supplied

Missing required headers

  • Standard mass load error that reports missing required headers

Length cannot equal zero

No Leaves found for Employee#$(employeeNumber)

No Position found for #$positionNumber, Employee#$employeeNumber

No Leave found for type $leaveType for Employee#$employeeNumber

Absence

Cannot calculate absence length - no active compensations found for Employee XXXXXXXXX - Position Null- check they have an active compensation or a Leave Unit (Daily or Hourly) selected in the employee's Leaves

Can not find entity reference - Make sure Employee ID is correct

Compensation is advanced.  New earnings (REG) may not be added to the compensation.

Irregular pay type affects contract amount paid for advanced compensation, can not be used.

Unable to convert '2022408' to Calendar for property activityDate

  • date should be '20220408'

Payroll Account Errors

  • Error: Payroll Account percentages must total 100% from Rule 'Ensure Payroll Accounts with rate type percent add up to 100% in org.ssdt_ohio.usps.model.payrollaccounts' 
    • Payroll Account must equal 100%.  Update in Payroll Account for employee
    • If updating a Payroll Account, same Position Number, for an employee in a file. for example, adding a stop date, but also adding a new Payroll Account, same Position number, for this same employee, old account has to be Inactive, and the new account needs to be Active.
  • Position with number XX not found for Employee #XXXXXXXXX - No Position# number found for this employee.  Verify for correct Position# or add new position
  • Employee with number NOACCT not found - Employee has not Payroll Account associated with Position #
  • No expenditure account found for account  codes null-null-null-null-null-null - payroll account missing in spreadsheet
    • If loading more than one Payroll account for an Employee on the same Position#, the mass Load will fail for ALL Lines for this employee only.  Correct and reload.

Pay Distributions Errors

  • DirectDepositDistribution.directDepositType: may not be null (Value: 'null') -If one line fails for an employee, then all transactions for that employee will fail.  Research and correct wrong transaction
  • Missing Identifying Properties for Pay Distributions.  Headers should contain the following [ employeeNumber, code, type, rateType, rate ] - verify all header names are entered 
  • Employee with number XXXXXXXXX not found-Verify employee number is correct
  • Rate Type is required to load Pay Distributions - Verify Rate Type is entered correctly as False or True (case sensitive)
  • Cannot convert Rate Type - Verify Rate Type is entered correctly as False or True (case sensitive)


Payroll Item

  • Payroll Item type Regular does not equal Payroll Item Configuration type Annuity for code XXX - error means there is a mismatch of the Payroll Item Configuration 'Type' to the Payroll Item 'Type'.  Verify which it should be and correct in Payroll Item Configuration or Payroll Item 'Type'

Payroll Item Configuration

  • Payroll Item Configuration Type is required for add
  • Payee ID or Payee Number is required for add
  • No enum constant org.ssdt_ohio.usps.model.constants.PayrollItemType.XXXXXX
    • wrong 'Type'  name has been entered

Leaves

  • Warning: Employee XXXXXXXX is not eligible for XXXXXXX leave.  XXXXXXX leave entry was loaded but will not be displayed when viewing leaves for the employee.

  • Warning: Employee XXXXXXXX is not eligible for any leave. Leave entries were loaded for this employee, but this employee will not be displayed when viewing leaves.

  • Cannot Load Leaves: 
    • Employer Number is required to load Leaves
    • Type is required to load Leaves
  • Leave units for sick, vacation, and personal are not the same
    • Leave Unit for employee must be the same for all Leave types, either all Hourly or all Daily
  • Leave type unsupported:  Vacation or Sick or Personal
    • All need to be in CAPS  VACATION or SICK or PERSONAL
  • Missing Identifying Properties for Leaves.  headers should contain the following  (employee.number, type)
    • Add employee.number and type to spreadsheet

Compensation

  • Position #null for Employee #null not found
  • Legacy Compensations can not be created in USPS.  Legacy type is only valid when updating an existing Legacy Compensation
  • ContractCompensation.payPlan:  may not be null (Value: 'null'), Contractcompensation.payUnit: may not be null (Value: 'null')
  • NonContractCompensation.payPlan: may not be null (Value: 'null'), NoncontractCompensation.payUnit: may not be null (Value: 'null')


Future Pay Amount

  • Boolean value must contain true or false:  value provided
  • Compensation is advanced.  New earnings (REG) may not be added to the compensation.
  • Employee with XXXXXXXXX not found - No employee is found
  • Employee with # not found - If no employeeNumber is given, then get error that Employee with # is not found
  • FuturePayAmount.supplementalTaxOptionValid:  Supplemental Tax Type must be false (Value: 
  • Irregular pay type affects contract amount paid for advanced compensation, can not be used.
  • No Future Pay Amount record found for id (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
  • No Position found for #99, Employee #XXXXXXXXX - No Position 99 for employee #XXXXXXXXX
  • Pay Type 'Regular' must apply for retirement - If appliesforRetirement is set to null and if Regular Pay type, it must be 'True'
  • Pay Type 'Irregular' must apply for retirement.
  • Pay Type 'Shift Premium' must apply for retirement
  • Specific Pay Account: Missing Amount Charged for new specific pay account with code XXXXXXXXXXXXXXXX for employee: XXXXXXXXX, position #
  • Specific Pay Account: Missing Rate Type for new specific pay account with code XXXXXXXXXXXXXXXXXXX for employee: XXXXXXXXX, position #
  • Specific Pay Account: Missing Leave Projection & Employer Distribution for new specific pay account with code XXXXXXXXXXXXXXXXXX for employee: XXXXXXXXX, position #
  • Specific Pay Account: Missing Leave Projection for new specific pay account with code XXXXXXXXXXXXXXXXX for employee: XXXXXXXXX, position #
  • Specific Pay Account: No expenditure account found (XXXXXXXXXXXXXXXXXXXXX) for employee: XXXXXXXXX, position #
  • Warning: Specific pay account properties exist in load file with no specific account code for employee. XXXXXXXX, position:  X, compensation code: xxxxxx-A. specific pay account not loaded - A Account code is needed in order to update any Specific Pay Account fields.  The 'Specific pay Account' fields did not update, verify Amount Charged, Leave Projection, Employer Distribution fields are correct. Correct and reload.


Position 

  • Position saved with no retirement code - if adding new Position, retirementCode is required