BC Year End Benefit Liability Extraction Process (PRACØA)

The BC Year End Benefit Liability Extraction process generates an export file name %%%%_####.CSV where %%%% is the payroll ID code and #### is the employee group identifier defined. This comma delimited text file is generated at the location defined by the logical SRB$EXPORT. The EXCEL icon is available on the second screen for immediate viewing of the extract results. A second file %%%%_RETIRE_####.CSV is created for all employees meeting the retirement selection criteria, based on Retirement/Termination date and selected by Employee Type code.

This process generates a text file containing the fields as defined by the Actuarial Study Instructions, Census Data Requirements. This version is as of the March 2006 for the October 2006 extraction.

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

Additional information or adjustments would be done within the spreadsheet.

The export file includes the following fields:

Employee number or SIN
Employee Group Identifier
Gender, Male, Female or blank
Employees birth date
Date of Hire
Seniority Date
Hourly Earnings Rate
Daily Earnings Rate
Annual Earning
FTE
Employment Status
Hours Worked Per Day
Scheduled Hours Per Year
Actual Hours Per Year
Vested Sick Leave in days
Vested Grand-fathered Sick Leave in days
Frozen Annual Earning (zero)
Sick Days used in Past Year
Vested Vacation in days
Vested Overtime in days
Non Vested Sick Leave in days
TOC, 1 if Active on TOCS payroll, 0 if not
Pensionable Service (zero)
10 or 12 Month

The Retiree export file includes the following fields:

Employee number or SIN
Employee Group Identifier
Gender, Male, Female or blank
Employee’s birth date
Retirement/Termination Date
Sick at Retirement, in Days
Sick as of June 30th prior to Retirement, in Days
Sick as of June 30^th 2 years prior to Retirement, in Days
Sick as of June 30^th 3 years prior to Retirement, in Days

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

YEAR END EXTRACTION/SRB$OPSEXE:PRAC0A

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

Include Employee # or SIN

Enter 'E' to report the employee's number or 'S' to report the employee's SIN. Field number one.

Enter Employee Group Identifier

Enter the Employee Group Identifier number as defined by the Ministry. Field number two. This value will be used in the name of the export file.

Enter TOC Payroll ID

Enter the TOC Payroll Identifier

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 employees Original Hire and when empty will use the employees 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 employees 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 define the year to date as well as the storage bank balances.

Enter End Pay Period

Enter the ending pay period, to define the year to be reported. This pay period determines where the balance of the storage bank values will be taken from.

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 employees 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. Employees whose master function line pay cycle is not in this list will be omitted from this process.

Additional Employee Selection By Code

LOCATION CODE	LC	COST CENTER	CS
SALARY GRID	GR	OCCUPATION CODE	OC
PAY TYPE	PT	EMPLOYEE TYPE	ET
PAY CODE	PC	SHIFT CODE	SC

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 employees 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 employees 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 employees Hours Worked Per Day can be derived from 1/10th of the function line hours.
Enter 'N' when this is not a biweekly payroll.

Hours Per Day From Employee Type

Enter 'Y' to use the Hours Per Day set on the Employee Type Code in the database.
Enter 'N' to not use the Hours Per Day set on the Employee Type Code. Hours as per function line will be used.

Enter Default Hours Per Day

Field nine requires Hours Worked Per Day.
Enter the default number of hours per day. This default hours per day will be adjusted per employee by prorating by the employees 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 overtime 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 Vested Sick Leave

Enter up to 25 storage banks to define the storage banks applicable for vested sick leave, related to field 12. The employee's sick leave hours/days will be determined by accumulating the total balance(s) for the storage banks defined as per the ending pay period defined.

The Sick Days used in the Past year will be accumulated base on these storage banks (as well as Non Vested Sick Leave) totaling all storage bank withdrawals for the range of periods defined. Only storage bank entries with types WD, WJ or CW will be included. This relates to field 15.

Enter Storage Banks For Non Vested Sick Leave

Enter up to 25 storage banks to define the storage banks applicable for non vested sick leave, related to field 18.

The Sick Days used in the Past year will be accumulated base on these storage banks (as well as Vested Sick Leave) totaling all storage bank withdrawals for the range of periods defined. Only storage bank entries with types WD, WJ or CW will be included. This relates to field 15.

Enter Storage Banks For Vested Grandfather Sick

Enter up to 25 storage banks to define the storage banks applicable for Grandfathered sick leave, related to field 13.

Enter Storage Banks For Vested Vacation Time

Enter up to 25 storage banks to define the storage banks applicable for vacation time, related to field 16.

Enter Storage Banks For Vested Overtime

Enter up to 25 storage banks to define the storage banks applicable for overtime, related to field 17.

Enter Assignment Types for LTD

Enter the list of Assignment Types for LTD leave selection.

Enter Assignment Types for MAT

Enter the list of Assignment Types for Maternity leave selection.

Enter Assignment Types for LOA

Enter the list of Assignment Types for LOA leave selection.

Enter Employee Type for Retiree Selection

Enter the Employee Type for Retiree selection reporting. Define a retirement Employee Type code to be applied to all retired employee’s master function lines. This will allow these employees to be reported on the separate Retirement extract.
Leave this field blank when reporting of retired employees in not applicable.

ASOF Termination Date for Retiree Report

Enter the Termination Date for Retiree selection reporting. Employees Terminated prior to this date will be omitted.
This field will be omitted when no Employee Type for Retiree is defined.

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 overtime. 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 overtime. 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 or SIN.
Gender, Male and Female. Blank if gender is not M of 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.
Hourly Earnings Rate. Defined if employee's cycle code - days or hours is H.
Daily Earnings Rate. Defined if employee's cycle code - days or hours is D.
Annual Earnings, as per the selected function lines, including premiums.
FTE, 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. TOC report 0 FTE
Employment status, Active, LTD, MAT, LOA
Scheduled hours, annualized hours as per master function line.
Scheduled hours, annualized hours as per selected function lines.
Actual hours, accumulated hours for range of periods defined. (Day payrolls converted to hours using the Hours Per Day).
Employee Group Identifier.
Vested Sick Leave balance as per ending pay period, in days..
Grandfathered sick leave as per ending pay period, in days.
Frozen Earnings - undefined.
Sick days used in past year, accumulated withdrawals from sick bank for year defined.
Mode of sick leave, 1 for hours and 2 for days.
Vested Vacation bank balance as per ending pay period, in days.
Vested Overtime bank balance as per ending pay period, in days.
Non Vested Sick leave as per ending pay period, in days.
10 or 12 Month employee. Pulls from cycle code field 'number of salary periods' ). If equals 12, 24, 25, or 27 reports as 12 months. Anything else reports as 10.