The OTIP Export Process Full HRIS Version generates the OTIP HRIS extract files for one or more payrolls based on the criteria of the Full HRIS multiple file format extracts.
The Full HRIS extract files Includes 3 separate sets of records. One Members record, 1 or more Positions Records and if applicable 1 or more Leave records, per employee. The position records are generated based on the employee’s current assignments. When two assignments have the same position code and same dates, those two assignments will be compressed into one position record. Assignments that map to a leave (as defined on the second page of the OTIP profile, per payroll) will generate a leave record as well as a position record. Terminated, Deceased and Retired employees will only report the Member record and do not report positions or leave records.
The comma separated HRIS extract files are named as follows, where B##### is the Employer Group Id, as per current payrolls OTIP profile. The TEST or PROD indicator is dependent on a field in the OTIP Profile. You will be warned when this extraction process is set to create a TEST extract file. The extract files are created at the location defined by the SRB$EXPORT logical and can be downloaded via the File Download process.
B#####_PROD_OTIP_MEM_YYYYMMDD.CSV or B#####_TEST_OTIP_MEM_YYYYMMDD.CSV
B#####_PROD_OTIP_POS_YYYYMMDD.CSV
B#####_PROD_OTIP_LEV_YYYYMMDD.CSV
Each time this process is run, the HRIS Global Cycle Number is incremented by 1 and this number is included in the header of the .CSV export files to be submitted to OTIP. The HRIS Global Cycle Number Override allows you to adjust this number as needed.
Employees with the HRIS Class code of 'N/A' will be omitted from this process without creating a rejection entry. Terminated employees are to be reported for 16 months based on the July school year. Employees terminated over the 16 months will be omitted from this process.
This OTIP Export process also creates an change version of the exported data. You can then adjust any value through the OTIP Export Changes and recreate the export files through this option using the Report Existing option.
This process validates employee entries. All rejected entries will be listed on a separate error/rejection listing along with the reason for the rejection. Rejected employees are not included In the HRIS export files.
The OTIP extraction files process contains 4 unique effective dates with related fields. The effective date categories are, Employment change, Status change, Scheduled hours change and Salary/Hourly rate change. The effective dates are flagged with a * in the below chart that details the HRIS export files. The effective date of these categories are refreshed for an employee when a change is made to any of the fields related to an effective date. The fields that apply to each date is flagged within the HRIS chart below.
When one of these trigger fields changes for an employee, the applicable effective date must be refreshed. This export process manages a file that holds these values and effective dates per employee. When this export process is run in create mode, and one of these values have changed for an employee, the applicable field is updated, and a new effective date is defined. The report for this process will list one line per employee, except when an effective date change has taken place, then the report will list two lines to document the change. Note: to facilitate reasonable reporting, when some fields such as the Client ID change, the change will be flagged but the actual change field will not be reported.
The default effective date (first one used) for each category is as taken from the following, when no initial default date is found, the Alternate Default Date from the prompts will be applied.
Employment Change effective date – HRIS Effective date, from employee’s Pay Screen
Work Status Change effective date – HRIS Effective date, from employee’s Pay screen.
Scheduled Hours/FTE Change effective date – Assignment start date
Salary Change effective date – Assignment salary start date
Leave Change effective date – Assignment start date for Leave assignments
|
Member Record |
|
|
Extracted definition |
|
Client Id |
1 |
|
OTIP Profile, primary or overrode by cycle, per payroll |
|
Employee’s OTIP Number |
|
|
Fed in from OIIP Benefits Upload |
|
Employee Number |
1 |
|
Employee’s payroll number |
|
Surname |
|
|
Employee’s Surname, per demographics |
|
First Name |
|
|
Employee’s first name |
|
Middle Name |
|
|
Employee’s middle name |
|
Current Hire Date |
|
|
Current Hire Date, or Original Hire if empty |
|
Group Id |
1 |
|
Group Id, 3rd field on OTIP profile, per payroll |
|
Class Code |
1 |
|
Class Code per employee’s Pay screen |
|
Plan Code |
|
|
Plan Code per employee’s Pay screen |
|
Province Of Work |
1 |
|
Fixed internally as ON |
|
Employment Effective Date |
* |
|
As per employee’s HRIS Effective date or Alternate Default Employment date. Any change to ‘1’ fields triggers date change. |
|
Work Status Code |
2 |
|
Status per profile mapping to Assignment Type or T when Terminated, I when On Leave |
|
Work Status Effective Date |
* |
|
HRIS Effective date or Termination date. Any change to ‘2’ work status code will trigger a date change. |
|
Birth Date |
|
|
Employee’s Birth date per demographics |
|
Gender |
|
|
Employee’s Gender, per demographics |
|
Address line 1 |
|
|
Employee’s address 1, per demographics |
|
Address line 2 |
|
|
Employee’s address 2, per demographics |
|
City |
|
|
Employee’s city, per demographics |
|
Province of residence |
1 |
|
Employee’s province, per demographics |
|
Country of residence |
|
|
Employee’s county, per demographics (CA for Canada) |
|
Postal Code |
|
|
Employee’s Postal Code, per demographics |
|
Phone Number 1 |
|
|
Employee’s phone number 1, per demographics |
|
Job Title |
|
|
HR Position Description for first Assignment or Occupation Code description when no assignments |
|
Language of preference |
|
|
As per OTIP profile setting |
|
Email address |
|
|
Employee’s email, per demographics |
|
Position Record(s) |
|
|
Extracted definition |
|
Client Id |
|
|
OTIP Profile, primary or overrode by cycle, per payroll |
|
Group Id |
|
|
Group Id, 3rd field on OTIP profile, per payroll |
|
Employee Number, payroll |
|
|
Employee’s payroll number |
|
Surname |
|
|
Employee’s Surname, per demographics |
|
First Name |
|
|
Employee’s first name |
|
Position ID |
|
|
HR position code per current assignments, see profile for omitted assignments |
|
Position Start Date |
|
|
Assignment start date |
|
Position End Date |
|
|
Assignment end date, if entered |
|
Position Description |
|
|
Position Description |
|
Position Classification |
|
|
Assignment Type field |
|
Union Affiliation |
|
|
Affiliation field per employee’s union deduction, union codes defined per OTIP profile |
|
Position Scheduled Hours |
1 |
|
Hours Per Week from Position Code data base, per assignment |
|
Assignment Scheduled Hours |
|
|
Assigned Hours, per assignment |
|
Scheduled hours / FTE effective date |
* |
|
Initial default from assignment start date. Any change to either ‘1’ fields triggers date change. |
|
FTE |
1 |
|
Assignment FTE or on hourly, assignment hours divided by positions hours. |
|
Hourly Rate |
2 |
|
Rate from assignment when affiliation is CUPE |
|
Salary / Hourly Rate Effective date |
* |
|
Initial Default is HR Assignment salary change date, Any change to either ‘2’ fields triggers date change. |
|
Annual Salary |
2 |
|
Annualized Salary per OTIP profile, per payroll |
|
Assignment Duration |
|
|
Default 12 or per employee’s pay cycle as defined per OTIP profile, per payroll. |
|
Annualized Hours |
|
|
Assignment hours per week times 52, divided by 12 times employee’s duration. |
|
Position Status |
|
|
A for Active or I for Inactive based on leaving setting on Assignment Type code, per assignment |
|
Vacation Allowance |
|
|
Calculated vacation pay from active function lines, per banks from OTIP profile, per payroll. |
|
Leave Record |
|
|
Extracted definition |
|
Client Id |
|
|
OTIP Profile, primary or overrode by cycle, per payroll |
|
Employee’s OTIP Number |
|
|
Group Id, 3rd field on OTIP profile, per payroll |
|
Employee Number, payroll |
|
|
Employee’s payroll number |
|
Surname |
|
|
Employee’s Surname, per demographics |
|
First Name |
|
|
Employee’s first name |
|
Position Id Description |
|
|
HR Position Description, per assignment |
|
Leave Reason Code |
|
|
Status per profile mapping to Assignment Type |
|
Leave Start Date |
|
|
Assignment start date |
|
Leave End Date |
|
|
Assignment end date, if entered |
|
Leave FTE |
|
|
Assignment FTE |
|
|
|
|
|
Create New Or Report Existing OTIP Records
Enter ‘C’ to create a new set of extract files. A new create would be run for each benefit cycle.
Enter ‘R’ to report on the existing export data. After a create has been done and manual adjustments are made through the OTIP Change process, then the re-report will report on the adjustment values, building new extract files containing the changes.
When 'C'reate a new export was selected:
Export Current or Selective Payrolls
Enter 'C' to generate an export file containing only the current payroll information.
Enter 'S' to enter one or more payroll ID codes. The payrolls selected must have the OTIP Profile defined and active.
Payroll Location IDs To Be Included
Enter one or up to 8 payroll Ids to be included in this export. The payrolls selected must have the OTIP profile setup and active, per payroll.
Alternate Default Employment Change Effective Date
Enter a date to represent the beginning of the payroll benefit cycle. This alternate default date will be used when a change is found on one of the employment fields and the employee’s HRIS date is not filled in.
Alternate Default Work Status Change Effective Date
Enter a date to represent the beginning of the payroll benefit cycle. This alternate default date will be used when a change occurs to the work status and the employee’s HRIS date is not filled in.
Alternate Default Scheduled Hours/FTE Effective Date
Enter a date to represent the beginning of the payroll benefit cycle. This alternate default date will be used when a change occurs to employee’s FTE or Schedule hours, if CUPE, and assignment date is not available or invalid.
Alternate Default Salary Change Effective Date
Enter a date to represent the beginning of the payroll benefit cycle. This alternate default date will be used when a change occurs to employee’s Annualized Salary or Hourly Rate, if CUPE, and assignment salary date is not available or invalid.
Sort Report By Employee Number Or Name Within Payroll
Enter 'E' to generate the report in employee number sequence per payroll.
Enter 'N' to generate the report in employee name sequence per payroll.
The report lists the following:
The report header includes the full export file name, including the HRIS cycle number used.
Employee number and name
Payroll Id, Client ID, Group Id, Plan Code and Union Affiliation
Line Status
Class Code
Employment Effective date
Work Status
Work Status Effective date
Position code
FTE or Scheduled hours, if CUPE
FTE/Hours effective date
Annualized Salary
Annualized Vacation, if applicable
Salary Effective date
Leave Code
Leave FTE
Leave effective date
Two lines per employee will print when an effective date change is made. The first line contains the original data that has changes, and the second line contains the new field values. Unchanged values will not display on the first line.
Total number of employees reported
Total number of Member records
Total number of Position records
Total number of Leave records
Total annualized salary and vacation reported.
Total number of employees bypassed.
The rejection report lists the following:
Employee number and name
Payroll Id
Rejection reason
Total number of rejected employees.