This page (revision-32) was last changed on 26-Nov-2021 10:22 by JEscott

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
32 26-Nov-2021 10:22 14 KB JEscott to previous
31 26-Nov-2021 10:22 14 KB JEscott to previous | to last
30 26-Nov-2021 10:22 14 KB JEscott to previous | to last
29 26-Nov-2021 10:22 14 KB JEscott to previous | to last
28 26-Nov-2021 10:22 14 KB JEscott to previous | to last
27 26-Nov-2021 10:22 14 KB JEscott to previous | to last
26 26-Nov-2021 10:22 14 KB JEscott to previous | to last
25 26-Nov-2021 10:22 14 KB JEscott to previous | to last
24 26-Nov-2021 10:22 14 KB JEscott to previous | to last
23 26-Nov-2021 10:22 14 KB JEscott to previous | to last
22 26-Nov-2021 10:22 14 KB JEscott to previous | to last
21 26-Nov-2021 10:22 14 KB JEscott to previous | to last

Page References

Incoming links Outgoing links
UBEF APA INTERFACE
UBEF APA INTERFACE

Version management

Difference between version and

At line 1 removed 2 lines
[{TableOfContents }]
At line 5 changed one line
The Generate Benefit/Payroll Interface (UBEF) screen may be used to generate a pension interface file for the province of Alberta.
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.
At line 7 changed 2 lines
!!Required Set Up
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.
At line 10 changed 2 lines
!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. An example would be 'PENSION'.
!!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.
At line 13 changed 2 lines
!Step 2- Define Plan Definitions in [IBPN]
Each APA pension plan must be set up and identified with a 'PENSION' plan type. 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.
At line 16 removed 2 lines
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 either PSPP, MEPP, or MSRP as appropriate.
At line 20 changed one line
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'.
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'. (See UBEF Processing section 5.11 for further details of each Coverage).
At line 24 changed 2 lines
!Step 3 - Define User Fields in [IMUF]
A new '[PN Employer Number|PN-EMPLOYER NUMBER (UDF)]' user-defined field must be set up for each APA pension plans. This employer number is assigned by the Alberta Pension Administration.
!!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.
At line 27 removed 11 lines
The '[PN Employer Number|PN-EMPLOYER NUMBER (UDF)]' UDF must be defined in [IMUF] for the table [P2K_BE_PLAN_DEFINITIONS] with the following fields:
[User Field|USER_FIELD_NAME]:PN-EMPLOYER NUMBER
[Prompt|FIELD_PROMPT]:PN Employer Number
[Type|FIELD_TYPE]:Char
[Format|FORMAT_MASK]:999
At line 40 changed 2 lines
!Step 4 - Define Pay Elements in [IPPE]
A number of [pay elements|PAY ELEMENTS] must be set up for the APA pension system. These [pay elements|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|PAY ELEMENTS] names that are used in the standard supplied [IDIF] definitions. These [Pay Element|PAY ELEMENTS] names can be modified, and new ones added, as long as the corresponding [IDIF] definition is also modified accordingly).
!!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).
At line 43 changed one line
The [APA Elements|APA ELEMENTS] page displays the list of necessary [pay elements|PAY ELEMENTS] required.
[APA ELEMENTS]
At line 45 changed 2 lines
!Step 5 - Define Usercalcs in [IMUC]
A number of [UserCalcs|USERCALC] may be required to process any exceptions to the standard function. You are responsible for writing and maintaining your own [UserCalcs|USERCALC].
!!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.
At line 48 changed 2 lines
!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.
!!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.
At line 51 changed one line
[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.
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.
At line 53 changed one line
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).
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).
At line 55 changed one line
You should copy (use Copy Definition) definitions into a different Interface code and then enter your own company data.
You should copy [use Copy Definition] definitions into a different Interface code and then enter your own company data.
At line 41 added 11 lines
![IDIF] Field Descriptions:
;[Code|??]: This is a user-defined Interface Code. If the IDIF layout is supplied the Interface Code begins with HL$
;[Interface Type|??]:This must be 35 - EEM/EAS Interface
;[File Format|??]: This field allows you to select one of the File Formats from the look up list
;[Form Type|??]: Please select 'Not Specified' from the look up list. Form Type is not used for UBEF
;[Description|??]: This is a user-defined description
;[File Name|??]: This field specifies the default file name, otherwise the file name is entered on UBEF parameter
;[File Creation Number|??]: This field is not used
;[Taxation Level|??]: Please select 'Not Specified' from the look up list. Taxation Level is not used for UBEF
;[Record Number|??]; This is a user-assigned record number. Each record number generates a new line in the interface file.
;:The following are suggested Record Numbers, however, you can set up your own numbers.
At line 58 changed one line
The interface file layout for the Alberta Pension Administration is constructed so that 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'.
||Record Number ||Interface File Records Generated
|0 |Qualify Records
|1 - 29| Header Records
|30 - 69 |Detail Records
|70 - 99 |Trailer Records
At line 59 added 39 lines
;[Field Number|??] This is a user assigned number
;[Name|??]: This is a user assigned field name
;[XML Tag|??]: This field is used for XML File Format only (Optional). If the XML Tag is not specified for XML File, then the Name field is used as XML Tag
;[Beginning Position|??]: If the file format = 'Fixed Format', you must specify the Beginning position
;[Ending Position|??]: If the file format = 'Fixed Format', you must specify the Ending position
;:These positions must be continuous without gap, otherwise an error situation will occur
;:Beginning/Ending positions are not needed for XML and other file format
;[Field Type|??]: This field can be 'Char', 'Number' or 'Date'
;[Variable Name |??];Select from the lexicon X_INF_VARIABLE_NAME 35nnnn, referring to lexicon list above
;[Sort Sequence|??]: This field specifies the sort sequence for this Record #, Field # and is used for Record # with Record Type = 'Detail Record' only
;:For Record # with Record Type = 'Detail Level 1' to 'Detail Level 5', it uses the same sort from Record Type = 'Detail Record' so that the Detail Level n records are followed after the 'Detail Record'
;:e.g. Interface File need to sort by: Department, Employee First Name, Last Name
;:For the Fld# entry that specify 'Department', enter sort sequence: 100
;:For the Fld# that specify 'Employee First Name', enter sort sequence: 150
;:For the Fld# that specify 'Employee Last Name', enter sort sequence: 200
;:All sort sequences must be entered on entries with the same record number
;:Sort sequence is not supported for XML file format because XML file may specify many different Record # to generate XML begin and end tags and the XML tags are not able to sort along with the data
;[Format Mask|??]: This field is applicable to Numeric and Date field
;[Record Type|??]: This must be used for this Interface Type. Lexicon [X_RECORD_TYPE].
;[Record Identifier|??]: This field is not used for this Interface Type
;[Constant Value |??]:If the variable name = 'Constant Value', this field specifies the Constant Value to be used
;:If the variable name = 'UserCalc', this field specifies the UserCalc Name
;:If the variable name = 'Statistics Amt', this field specifies the Statistics Code
;:If the variable name = 'Element', this field specifies the Element Code
;:If the variable name = 'Total Value', this field specifies the Rec# and Field # of the Detail Records to be totaled
;:e.g. Record # 50 Field 70 is to be totaled and Total option = 'Calculate Total', then the Trailer record can report the 'Total Value', Constant value should be 5070
;[Print Zero Rule|??]: This is applicable to Numeric field only; use for Record Type = 'Detail Record'. If amount is zero, this field indicates to include this amount in the interface file or not
;:e.g. if the field is from 'Statistics Amount' and the amount is zero, you may not want to report this amount
;:If all numeric amounts are zero within one Record# and the Print Zero Rule are set to 'Do not Print zeros' for each field, then this entry will not be reported on interface file. Lexicon [X_PRINT_ZERO_RULE]
;[XML Tag|??]: option This is applicable to XML file, if the variable name = 'XML begin Tag'or 'XML end Tag', this field option indicates if the XML Tag should be written. Lexicon [X_XML_TAG_OPTION]
;[Accum Option|??]: This is applicable to the Numeric field only and is used for Record Type = 'Detail Record'
;:This indicates if this Record #, Field # amount should be accumulated or not
;:e.g. if the field is a 'Pay Number' or 'Check Number', then do not accumulate
;:If the field is 'Element Value' or 'PC Amount', then you may want to accumulate to avoid writing too much detail per element or per pay component
;[Total Option|??]: This is applicable to the Numeric field only and is used for Record Type = Detail Record, Detail Level
;:This indicates if this Record #, Field # amount should be totaled for Header or Trailer record
;:e.g. If the field is a 'FTE' or 'Rate of Pay', then do not totaled
;:If the field is 'Statistics Amount' or 'UserCalc Amount', then you may want to calculate the total for Trailer record. Lexicon [X_TOTAL_OPTION]
;[Derivation Expression|??]: This field specifies the derivation expression for the variable
At line 61 changed 8 lines
;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.
!!Interface File Layout for the Alberta Pension Administration
The file is pipe delimited
At line 70 changed 140 lines
The user is now ready to run the UBEF to create an 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|USERCALC] 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 COMPONENTS], [Pay Element|PAY ELEMENTS] names, and [UserCalc|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|PAY ELEMENTS]:
**PEN EARN TOTAL - this [element|PAY ELEMENTS] is a complex element that contains [elements|PAY ELEMENTS] PEN-PD-EARNS and PEN-UNPD-EARNS
**Each of these [elements|PAY ELEMENTS] has a corresponding Hours Element which is required for reporting
*Not included in calculation but needed for report are the following [elements|PAY 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|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|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|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|PAY COMPONENTS]
** The Member (EE) amount is stored in a [Pay Component|PAY COMPONENTS]
** 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|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|PAY ELEMENTS] 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|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 & Filters
||Report Parameters||
|Entity| Mandatory. You must enter an entity
|From Date| Mandatory. Enter a Pay Period Begin Date
|To Date| Mandatory. Enter a Pay Period End Date
|Interface Code| Mandatory. Enter the Interface code from the [IDIF] screen
|Interface Level |Mandatory. Specify the level for the Interface file to be produced; this must be '10'. 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| Optional. 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.
|Sort by Active/Inactive| Optional. Check this toggle to sort inactive employees first.
|Sort Employees By|Optional. 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 |Optional. This field provides an option to create the file
|Directory Name |Optional. Enter a valid directory name
|File Name|Optional. Enter a file name or use the default file name from [IDIF] screen
|Exception Level |Optional. 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
||Report Filters||
|People List Code| Optional. Please specify if People list is to be used
|Person Code| Optional. Enter one or more Person Codes
|Location| Optional. Enter one or more Location codes
|Authorization| Optional. Enter one or more Authorization Areas
|Department| Optional. Enter one or more Department Codes
|Org Level Type|Optional. Enter one or more Org Level Types
|Org Level |Optional. Enter one or more Org Level within Org Level Types
|Unit| Optional. Enter one or more Unit codes
|Group |Optional. Enter one or more Group codes
|Payroll| Optional. Enter one or more Payroll Codes
|Employment Status| Optional. Enter one or more Employment Statuses
|Termination Reason| Optional. Enter one or more Termination Reasons
|Plan Type| Optional. This field is applicable if Interface Level = 'BE plans'
|Plan| Optional. This field is applicable if Interface Level = 'BE plans'
----
![Notes|Edit:Internal.UBEF+APA+INTERFACE]
[{InsertPage page='Internal.UBEF+APA+INTERFACE' default='Click to create a new notes page'}]
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'.
This page (revision-32) was last changed on 26-Nov-2021 10:22 by JEscottTop