Saskatchewan Year End Benefit Liability Extraction (PRACØK)

The Saskatchewan Year End Benefit Liability Extraction process generates an export file name %%%%_######.CSV where %%%% is the payroll ID code and ###### is the job number of the process run. This comma delimited text file is generated at the location defined by the logical SRB$EXPORT.  The exact file name and location where the file was generated is included on the top of the report. The EXCEL icon is available on the second screen for the immediate viewing of the extract results.

This process generates a text file containing 17 fields as defined by the Census Data Requirements V2, January 2009. The sick taken was added as of 2017.

Active employees with no bank balance and a Hire date earlier than the pay period end date from the ending pay period for the range of period to report will be omitted from the process.  These omissions are reported in the processing log.

This extraction process would be run per employee group, per payroll. The results would then be imported into the supplied spreadsheet tool.

Additional information or adjustments would be done within the spreadsheet.

The export file includes the following 17 fields:

• Employee number

• Employee Classification

• Gender, 1 for male and 2 for female

• Employee’s birth date

• Date of Hire  

• Seniority Date

• Annual Earning

• Employment Status

• Hours Worked Per Day – as per entered.

• Number of Days Worked Per Year

• Sick Leave balance at end of first year, in days

• Sick Leave balance at end of one prior to first year, in days

• Sick leave balance at end of two prior to first year, in days

• Vacation balance at end of first year, in days

• Vacation balance at end of one prior to first year, in days

• Vacation balance at end of two prior to first year, in days

• Paid Time Off bank balance at end of first year, in days

• Total sick time taken (based on back withdrawals) for the period range supplied, in days

This option may be setup on the Custom Users Reports Profile, for example,  

YEAR END EXTRACTION/SRB$OPSEXE:PRAC0K

This option would then be run from the Custom Users Report Menu.

DEFAULT PROMPT STORAGE & RETREIVAL See Appendix J.

This process includes the option to maintain multiple sets of stored prompts.

Name the prompt sets by defining a title (up to 10 characters with a description of 30 characters).

On the first prompt click the Report Defaults icon to select an existing set of prompt values. You can delete prompt sets or change the description from the Report Default icon.

BATCH AND PRINT QUEUE SCREEN See Appendix B.

Enter Employee Classification

• Enter the Employee Classification as defined by the Ministry. Field number two.

Enter Field(s) In Sequence For Date Of Hire

• C = CURRENT HIRE

• O = ORIGINAL HIRE DATE

• S = SENIORITY DATE

The Date of Hire can be taken from the Current Hire, Original Hire or Seniority date fields.  The order you enter the codes C,O or S will determine which order and fields will be used.  For example, if you enter O,C, the system will look for the employee’s Original Hire and when empty will use the employee’s Current Hire date field.

By default the Current Hire is used.  This value relates to field number five.

Use Date Of Hire When No Seniority Date

• Enter ‘Y’ or <CR> to have the system use the date loaded as the Date Of Hire (field number five) as the Seniority date when no Seniority date exists on the employee.

• Enter ‘N’ when the Seniority date should be left when the employee’s Seniority date is empty.

This value relates to field number six.

Enter Starting Pay Period

• Enter the first pay period to define the year to be reported. This range of periods defines the year to date.

Enter End Pay Period

• Enter the ending pay period, to define the year to be reported.

Enter YTD Hours Pay code Include/Exclude

• Enter 'E' or <CR> to indicate the following list of pay codes will be excluded from the accumulation of actual hours worked taken from the employee’s history.

Enter YTD Hours Pay Codes

• Enter 1 to 25 pay codes to include/exclude from the list of pay codes to determine the accumulated actual hours worked.

Enter Cycle For Employee Selection

• Enter the pay cycle codes for employee selection based on the master function line. Approximately 11 pay cycles may be selected.  Employee’s whose master function line pay cycle is not in this list will be omitted from this process.

Additional Employee Selection By Code

 Enter a 2-character code to define which field will be used for employee/function line selection

You are prompted to enter one or more of that code or value. You may enter valid codes or any combination using the wild card '?'. An employee must have one function line that meets the selection criteria to be included on this process.

FTE From Function Line Divided By Grid Hours  

This FTE generation only applies when there is no percent employed.

• Enter 'Y' when the system should calculate the employee’s FTE by dividing the function line hours by the hours from the salary grid database (grid point 1).  This feature will only apply when the Percentage Employed is not applicable.  This may be for all employees/function lines or only Hourly or Part-time function lines.

• Enter ‘N’ when employee’s with no Percentage Employed should default to 1.00 FTE.

This value relates to field number eight.

Hours Per Day 1/10 of Function Line (Biweekly)

Field nine requires Hours Worked Per Day.

• Enter ‘Y’ when this is a biweekly payroll and the employee’s Hours Worked Per Day can be derived from 1/10th of the function line hours.

• Enter ‘N’ when this is not a biweekly payroll.

Enter Default Hours Per Day 

Field nine requires Hours Worked Per Day.

• Enter the default number of hours per day.  The default hours per day is reported as is and is not reduced by the employee’s FTE. The intent is this is the number of hours per day and the Mercer process multiplies this value by the employee’s FTE.

Storage Banks In Hours Or Days 

• Enter 'H' to indicate the storage banks values are in hours.

• Enter 'D' to indicate the storage banks values are in days.

The sick, vacation and paid time off bank values are to be reported as a number of days.  When the payroll storage bank values are Hours, the employees’ values will be converted to Days using the Hours Worked Per Day (field 9).

Enter Storage Banks For Sick Leave

• Enter up to 25 storage banks to define the storage banks applicable for sick leave, related to field 11, 12 and 13. The employee's sick leave hours/days will be determined by taking the closing balance from the end of the year for 2008, 2007 and 2006.  Hours are converted to days based on the employee’s number of hours per day.

Enter Storage Banks For Vacation Time 

• Enter up to 25 storage banks to define the storage banks applicable for vacation time, related to field 14, 15 and 16.  The employee’s vacation hours/days will be determined by taking the closing balance from the end of the year for 2008, 2007 and 2006. Hours are converted to days based on the employee’s number of hours per day.

Enter Storage Banks For Paid Time Off 

• Enter up to 25 storage banks to define the storage banks applicable for paid time off, related to field 17.

Enter The First Bank Year To Report 

• Enter the first or most current bank year to be reported.  This year plus two year back will be reported.

Report Master Function Line Or All Active Lines 

• Enter 'M' or <CR> to use only the master function line pay rate and hours as the earnings rate and scheduled hours.

• Enter 'A' to include all active function lines rates and hours for determination of the earnings rate and scheduled hours to be reported. Function lines must be active as of the current date to be included.

Enter Function Line Pay Code Include/Exclude 

• Enter 'E' or <CR> to indicate the following list of pay codes will be included or excluded in the accumulation of function line hours and earnings.

Enter Function Line Pay Codes

• Enter 1 to 25 pay codes to include/exclude from the list of pay codes to determine the accumulated of function lines hours and annualized earnings.

Report Terminated Employees With A Balance

• Enter 'Y' or <CR> to include Terminated employees that have an outstanding balance in sick leave, vacation or paid time off. Terminated employees with no storage bank balances will be omitted.

• Enter 'N' to omit Terminated employees regardless of the outstanding storage bank balances.

Report On Leave Employees With A Balance

• Enter 'Y' or <CR> to include On Leave employees that have an outstanding balance in sick leave, vacation or paid time off. On Leave employees with no storage bank balances will be omitted.

• Enter 'N' to omit On Leave employees regardless of the outstanding storage bank balances.

The following is extracted:

• Employee Number

• Gender, 1 for male and 2 for female. Blank if gender is not M nor F.

• Date of Birth, as per employee's birth date.

• Date of hire, as per employee's current, original or seniority date.

• Seniority date, as per employee's seniority date on the pay master screen.

• Annual Earnings, as per the selected function lines, including premiums.

• FTE, Employment status, 1.0 for full time and 0 up to 1.0 for part time. As per percent employed, when applicable. Optional FTE generated by dividing function line hours by salary grid hours.  Default to 1.0 FTE.

• Hours Per Day – as per entered (NOT reduced by FTE).

• Scheduled hours, annualized hours as per selected function lines.

• Sick Leave balance as per end of first year, in days.

• Sick Leave balance as per end of one prior to first year, in days.

• Sick Leave balance as per end of two prior to first year, in days.

• Vacation bank balance as per end of first year, in days.

• Vacation bank balance as per end of one prior to first year, in days.

• Vacation bank balance as per end of two prior to first year, in days.

• Paid time off bank balance as per end of first year, in days.