Converting Benefit Enrollments#

The primary table for conversion of benefit information into the application is the Benefit Enrollment records seen from the IBEN screen. These records are loaded into the INF_CV_ENROLLMENT table and will be converted into the P2K_BE_ENROLLMENTS, P2K_BE_ENROLLMENT_DETAILS and P2K_BE_ENROLLMENT_COMPONENTS. Since the information in the interface tables contains data that will be stored into several tables within the application schema, it is likely that multiple records will have to be loaded for each benefit plan that each employee may be enrolled into.

The following table defines the layout of the INF_CV_ENROLLMENTS table:

Name	Null?	Type	Notes
ENTITY_CODE	NOT NULL	VARCHAR2(16)	Used to derive link to EMPLOYMENT record
PERSON_CODE	NOT NULL	VARCHAR2(16)	Used to derive link to EMPLOYMENT record
EMPLOYMENT_TYPE		VARCHAR2(30)	Used to derive link to EMPLOYMENT record
HIRE_DATE		DATE	Used to derive link to EMPLOYMENT record
PLAN_CODE	NOT NULL	VARCHAR2(16)	Must be a valid benefit plan from IBPN
EFFECTIVE	NOT NULL	DATE	Date of enrollment into the plan. This should not be the BOT.
EXPIRY	NOT NULL	DATE	Last day the enrollment record is active for, or EOT if current.
CHANGE_CODE		VARCHAR2(16)	Must be a valid change reason from IDCR
COVERAGE_CODE		VARCHAR2(16)	Must be a valid coverage code from IBPN
FREQUENCY_CODE		VARCHAR2(16)	Only filled in to override IBPN frequency setting
PARTICIPATION_RULE		VARCHAR2(30)	Must be a valid lexicon entry. Typically 00 or Optional
COVERAGE_STEP_CODE		VARCHAR2(16)	Only used if Coverage rates are stepped
DESCRIPTION		VARCHAR2(50)
UNOFFICIAL		VARCHAR2(30)	Must be a valid lexicon entry. Typically 1 or Official
ENROLLMENT_STATUS		VARCHAR2(30)	Must be a valid lexicon entry. Typically 01 or Enrolled
ELIGIBILITY_START_DATE		DATE	Set to EFFECTIVE. This is recalculated when processed
ELIGIBILITY_END_DATE		DATE	Set to EXPIRY. This is recalculated when processed
PREMIUM_START_DATE		DATE	Set to EFFECTIVE. This is recalculated when processed
PREMIUM_END_DATE		DATE	Set to EXPIRY. This is recalculated when processed
PLAN_REG_NUMBER		VARCHAR2(50)	Set to value for plan from IBPN. Used for remittance reports
EVIDENCE_OF_INSURANCE		VARCHAR2(4)	Check mark field. Set to 0, N, NO or NULL to turn off.
PERCENTAGE		NUMBER(5,2)
BC_CODE		VARCHAR2(16)	Must be a valid Benefit Component Code from IBBC
AMOUNT		NUMBER(18,6)	Set to the initial value for the BC Code. This will be recalculated.
AMOUNT_OVERRIDE		NUMBER(18,6)	Set to the value for the BC Code. This will not be recalculated.

If the BC Code values can be derived from other information already loaded into the database (such as the employees wage rate), there is no need to load more than one record for each benefit plan/coverage per employee. Most benefit enrollment records can be loaded in without setting the value for the BC Code or amount fields. The next time the enrollment record is processed through a pay cycle and the unofficial record is created, all the Benefit components amounts will be calculated based on the rules defined for each.

However, these calculations can vary for each plan and coverage each plan/coverage definition within the application will have to be checked to know if a BC Code and Amount value will be needed during the conversion of the data.

Enrollment into any benefit plan will require a minimum of one record for each employee, plan and coverage combination within the application. If the plan and coverage combination does not need any BC Code values converted, there will be only one record which will have a null value in the BC Code and Amount fields. If the plan and coverage combination requires amounts to be loaded and converted into the ENROLLMENT records, the BC Code, Amount and Amount Override fields will need values. There may be one or more records in the interface table based on the number of BC Code values that need to be converted.

If any of the following BC Codes are being used with the associated rule, a record may need to be loaded and converted for this BC Code with a value:

BC_CODE	BC_RULE_CODE	BC_DESC	BR_DESC
B0300	03	Employee Wage Rate	Use Enrollment Wage Rate
B0320	09	Eligible Earnings/Amount (before cap)	Use Enrollment Amount
B0360	01	Eligible Earnings/Amount (after cap)	Replace Enrollment Value
B0360	02	Eligible Earnings/Amount (after cap)	Accumulate Enrollment Value
B0361	01	Lump Sum Leave Pay Earnings	Replace Enrollment Value
B0363	01	Prior Period Adjustment Earnings	Replace Enrollment Value
B0365	01	Retroactive Salary Earnings	Replace Enrollment Value
B0366	01	Special Compensation Amount	Replace Enrollment Value
B0368	01	Worker's Compensation Earnings	Replace Enrollment Value
B0400	02	Coverage Calculation Method	Use Coverage from Enrollment
B0500	02	Coverage Step Method	Use Enrollment Step
B0600	03	Premium Rate	Use Value from Enrollment
B0610	03	Premium Rate 2	Use Value from Enrollment
B0620	02	Premium Calculation Method	Use Premium Value on Enrollment
B0695	02	Survivor Amount	Use Value from Enrollment
B0800	03	Contribution Rate	Use Value from Enrollment
B0810	02	Contribution Method	Use Contribution Amt on Enrollment
B0840	01	Contribution Amount	Replace Value on Enrollment
B0840	02	Contribution Amount	Accumulate Value to Enrollment
B1000	03	Deduction Rate	Use Value from Enrollment
B1010	02	Deduction Method	Use Deduction Amt from Enrollment
B1040	01	Deduction Amount	Replace Enrollment Value
B1040	02	Deduction Amount	Accumulate Enrollment Value
B1070	02	CSB Deduction (Non-RRSP option)	Enrollment
B1072	02	CSB Deduction (RRSP option)	Enrollment
B1210	02	Taxable Benefit Method	Use Taxable Benefit on Enrollment
B1210	03	Taxable Benefit Method	Use Taxable Benefit on Enrollment
B1300	01	Bond Denomination	Specified Value or from Enrollment
B1310	01	Deduction Amount	Specified Value or from Enrollment
B1320	01	Amount Available for Bond Purchase	Enrollment Total + Current Deduction
B1340	01	Value of Bonds Purchased	Specified Value from Enrollment

One thing to keep in mind when converting ENROLLMENT records is how this information would be keyed into benefit elections on the IBEL screen. If the only piece of information needed in IBEL to enroll an employee is the plan code and coverage code, no BC Codes or Amounts are needed to convert these records.

---

Notes #

Click to create a new notes page