UBEF - ALBERTA PENSION ADMINISTRATION (APA) SYSTEM#

The Generate Benefit/Payroll Interface (UBEF) screen may be used to generate a pension interface file for the province of Alberta. The following describes the necessary set up required in order to create this type of interface.

Step 1 - Define Plan Type in IBPT#

A plan type must be set up for the Alberta Pension Administration (APA) system. The plan type may be defined by the user, but all pension plans associated with APA must be identified as being in this plan type. The following example illustrates the set up of 'PENSION' plan type in the system.

Step 2- Define Plan Definitions in IBPN#

Each APA pension plan must be set up and identified with a 'PENSION' plan type (as illustrated in this document). The plan code can be defined by the users, but keep in mind that the 4th-7th characters of the plan name will be used to report to the Alberta Pension Administration.

There are currently three pension plans being supported, they are PSPP, MEPP, and MSRP plans. Since this system passes only the 4-7th characters of the plan code to the Alberta Pension Board, therefore it is mandatory that those positions of the Plan code includes eitherPSPP, MEPP, or MSRP as appropriate.

Please note that it is possible to define the plan code in other ways, in this case, the IDIF file definition must be changed accordingly or it will not produce the desired result.

Due to different contribution rules which apply to different employment statuses, each of these pension plans should have four coverage codes. The currently supported coverage codes are 'Active', 'LTD', 'Leave', and 'Leave No-cont'.

Only record types 18-4, 18-A, 18-L, 18-M, and 18-W as defined by APA is supported at this time.

Step 3 - Define User Fields in IMUF#

A new 'PN-EMPLOYER NUMBER' user-defined field must be set up for each APA pension plans. This employer number is assigned by the Alberta Pension Administration.

The 'PN-EMPLOYER NUMBER' UDF must be defined in IMUF for the table P2K_BE_PLAN_DEFINITIONS with the following fields:

User Field
PN-EMPLOYER NUMBER
Prompt
PN Employer Number
Type
Char
Format
999

Once this UDF is correctly set up, it can then be access through the IBPN screen using the 'Edit User Fields' button in the tool bar.

Step 4 - Define Pay Elements in IPPE#

A number of pay elements must be set up for the APA pension system. These pay elements will be used in the IDIF definition in order to obtain the correct Pensionable Salary and Contributions. (This list contains suggested Pay Element names that are used in the standard supplied IDIF definitions. These Pay Element names can be modified, and new ones added, as long as the corresponding IDIF definition is also modified accordingly).

APA ELEMENTS

Step 5 - Define Usercalcs in IMUC#

A number of UserCalcs may be required to process any exceptions to the standard function. You are responsible for writing and maintaining your own UserCalcs.

Step 6 - Define Interface Format in IDIF#

HL$ABPERS-184 is provided in the IDIF screen as an example of a Pipe Delimited interface file for generating Record Type 18-4 for the Alberta Pension Administration.

HL$ABPERS-184C is also provided in IDIF as an example of a Comma Delimited file for generating an audit report of Record Type 18-4.

Standard IDIF definitions are provided for the other supported record types (HL$ABPERS-18A and HL$ABPERS-18AC for record type 18-A; HL$ABPERS-18L and HL$ABPERS-18LC for record type 18-L; HL$ABPERS-18M and HL$ABPERS-18MC for record type 18-M; HL$ABPERS-18W and HL$ABPERS-18WC for record type 18-W).

You should copy (use Copy Definition) definitions into a different Interface code and then enter your own company data.

IDIF FIELD DEFINITIONS

Interface File Layout for the Alberta Pension Administration#

The file is pipe delimited

Each file contains one header record and multiple detail records. The Transaction code and Detail type for the header record is always '94-1' for all files, while the Transaction code and Detail type for the detail records can be either '18-4', '18-A', '18-L', '18-M', or '18-W'.

UBEF APA Interface File Layout

Supported Transaction Types for the Province of Alberta#

Transaction
Type		 Description
94-1 Batch Header Record, a record of this type will be created
together with all other transaction types in the file.
18-4 Annual Year End Detail Record
18-A Reports Leave without Salary: earnings and service
18-L Reports the Leave of Absence detail, or adjustments to this
detail, where the member has continued to contribute
while on leave
18-M Reports Parenting Leave without Salary: earning and service
18-W Reports the Parenting Leave of Absence detail, or adjustment
to this detail, where the member has continued to contribute
while on leave.

Step 7 - Run UBEF To Create APA Interface File#

Processing Information#

One file will be created each time the UBEF function is executed; each execution will be for one plan and for one transaction type only. One file will be generated for each pension plan and transaction type combination.

For the three supported plans and the five supported transaction types, a total of 15 interface files will be generated (i.e. one interface file for the PSPP plan for transaction type 18-4, one interface file for the MEPP plan for transaction type 18-4, one interface file for the MSRP plan for transaction type 18-4, one interface file for the PSPP plan for transaction type 18-A, one interface file for the MEPP plan for transaction type 18-A, one interface file for the MSRP plan for transaction type 18-A, one file for the PSPP plan for transaction type 18-L, etc.…).

To generate an audit report, the UBEF function must be executed a second time, using a 'Comma Delimited' Interface File format to create a file with a file extension of '.csv'. This file can then be viewed through an Excel spreadsheet.

UBEF Calculations#

UBEF - Pension Interface Calculation (Example Only)

PSPP-PLAN

This example shows some example UserCalcs that are required to be written and maintained by user.

This PN-PSPP plan example will have three example coverage codes: CONTRIBUTION, LTD and NO-CONTRIBUTION. The PC codes, Pay Element names, and UserCalc names used in this example are for illustration purpose only and should be modified as required.

PN-PSPP - CONTRIBUTION Coverage
  • For Pension calculation earnings will be derived from the following element:
    • PEN EARN TOTAL - this element is a complex element that contains elements PEN-PD-EARNS and PEN-UNPD-EARNS
    • Each of these elements has a corresponding Hours Element which is required for reporting
  • Not included in calculation but needed for report are the following elements: PEN NC EARN and PEN NC HOURS
  • Coverage was calculated as follows:
    • Coverage = Factor * Eligible Earnings
    • Factor = 1
    • Therefore, the coverage is always the eligible earnings
  • Two premium rates were used to define the premium
  • The premium amount for coverage up to and including 1580.77 was calculated at a premium rate of 0.0617
  • The premium amount for coverage beyond 1580.77 up to 4320.00 was calculated at a premium rate of 0.0881
  • A UserCalc (suggested name BE-PENS-CAP) will have to be written to cap the employee's earnings at 4320.00
  • When the employee has reached 35 years of service, they must be expired from the CONTRIBUTION in coverage and placed in the NO-CONTRIBUTION coverage.
  • When electing them into the new plan, the effective date used will be the start date of the next pay period
  • They must still be reported as being enrolled in the PSPP plan but are no longer making any contributions
  • The effective date will be set to the employee's hire date
  • A UserCalc will be written to automatically set the expire date to be:
    • Expire Date = Hire Date + 35 Years - 1Day
  • Need to be able to report total pensionable hours and earnings. The following PC's will be defined:
    • PC 980 (PSPP-Con-Hours) - this is calculated via a UserCalc PR-PENCONT-HRS and is based on the amount in PC 1980 being greater than 0
    • PC 1980 (PSPP-Con-Earns)
    • This plan is matched 100% by the ER
    • The Employer amount is stored in a Pay Component
    • The Member (EE) amount is stored in a Pay Component
    • PC's 1980 and 980 are used for reporting and verification purposes only
Example 1:
Maximum Biweekly Cap 1 = 1580.77
Maximum Biweekly Cap 2 = 4320.38
Premium rate up to Cap 1= 0.0617
Premium Rate up to Cap 2 = 0.0881
Employee's Biweekly Earnings = 1200.00
Premium = (1200.00 * 0.0617) = 74.04
Employer pays 74.04
Member (EE) pays 74.04

Example 2:
Maximum Biweekly Cap 1 = 1580.77
Maximum Biweekly Cap 2 = 4320.38
Premium rate up to Cap 1= 0.0617
Premium Rate up to Cap 2 = 0.0881
Employee's Biweekly Earnings = 2000.00
Premium 1 = (1580.77 * 0.0617) = 97.53
Premium 2 = (419.23 *0.0881) = 36.93
Total Premium = 97.53 + 36.93 = 134.46
Employer Pays = 134.46
Member (EE) Pays = 134.46

Example 3:
Maximum Biweekly Cap 1 = 1580.77
Maximum Biweekly Cap 2 = 4320.38
Premium rate up to Cap 1= 0.0617
Premium Rate up to Cap 2 = 0.0881
Employee's Biweekly Earnings = 5000.00
Premium 1 = (1580.77 * 0.0617) = 97.53
Premium 2 = (2739.61 *0.0881) = 241.36
The amount of 2739.61 was derived from the following: Maximum Biweekly Cap 2 -Maximum Biweekly Cap 1 = 4320.38-1580.77 = 2739.61
Total Premium = 338.89
Employer Pays = 338.89
Member (EE) Pays = 338.89

PN-PSPP - LTD Coverage
  • Separate coverage code is used in order to calculate the employer portion at 200% (which includes the value of employer and employee contributions combined and is required for payroll processing) and ensure that it is divisible by 2.
  • The report should split this amount into two to be displayed separately (as this is required for year end reporting)
  • When an employee goes on LTD, the effective date to be used on the election record will be their actual date of leave
  • PC's 982 and 1982 are for reporting and verification purposes only
  • PC 982 (PSPP-LTD-Hours) - this is calculated via a UserCalc PR-PEN-CONTHRS and is based on the amount in PC 1982 being greater than 0
  • PC 4150 contains the Employer amount

PN-PSPP - NO-CONTRIBUTION Coverage
  • When an employee goes on a specific type of leave or their 35 years of service is up, they will need to be enrolled into this coverage
  • There are no premium, employer or employee amounts calculated
  • The employee must still be reported on the pension report
  • Hours and earnings PC's (listed above) will be created to report on
  • An element called 'PEN NC EARN' is used to determine the eligible earnings amount
  • PC 981 and PC 1981 are used for reporting and verification purposes only
  • PC 981 is populated via a UserCalc PR-PEN-CONT-HRS and is based on the amount in PC 1981

UBEF Sample Report#

A spreadsheet will be provided which includes all data columns that were recorded in the interface file.

To generate this report, you must run UBEF a second time, using a 'Comma Delimited' Interface file format, and create a file with a file extension of '.csv'. Once the.csv file is created, you can then view this file through an Excel spreadsheet, and delete those data columns that are of no use.

Report Parameters#

Report Parameters
Entity You must enter an entity
Mandatory
From Date Enter a Pay Period Begin Date
To Date Enter a Pay Period End Date
Interface Code Enter the Interface code from the IDIF screen
Mandatory
Interface Level Specify the level for the Interface file to be produced;
this must be '10'.
Mandatory
Lexicon X_INTERFACE_LEVEL values are:
01 - EEM + EASD (not applicable for UBEF processing)
02 - EEM + EASD + Contacts (not applicable for UBEF processing)
10 - BE Plans
11 - BE + Contacts (not applicable for UBEF processing)
ER Addr Location This allows you to enter an overridden location to retrieve the employer’s address.
If this is not specified, the entity's location address is used.
Optional
Sort by Active/Inactive Check this toggle to sort inactive employees first.
Sort Employees By You should sort employees by Last Name/First Name, however, you may sort by the
employee's pay destination or sort by Person code
Create File This field provides an option to create the file
Directory Name Enter a valid directory name
File Name Enter a file name or use the default file name from IDIF screen
Exception Level You should always run with the Exception level unless for tracing
In Trace Level 1, the employee header with Person Code and Employee Name are
displayed with performance time, this can be used for Progress bar or to locate
employee with problem
In Trace Level 2, the BE plans are displayed after employee header information
People List Code Please specify if People list is to be used (Optional)
Person Code Enter one or more Person Codes (Optional)
Location Enter one or more Location codes(Optional)
Authorization Enter one or more Authorization Areas (Optional)
Department Enter one or more Department Codes (Optional)
Org Level Type Enter one or more Org Level Types (Optional)
Org Level Enter one or more Org Level within Org Level Types (Optional)
Unit Enter one or more Unit codes (Optional)
Group Enter one or more Group codes (Optional)
Payroll Enter one or more Payroll Codes (Optional)
Employment Status Enter one or more Employment Statuses (Optional)
Termination Reason Enter one or more Termination Reasons (Optional)
Plan Type This field is applicable if Interface Level = 'BE plans' (Optional)
Plan This field is applicable if Interface Level = 'BE plans' (Optional)
This particular version was published on 26-Nov-2021 10:22 by JEscott.Top