This page (revision-5) 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
5 26-Nov-2021 10:22 8 KB JEscott to previous
4 26-Nov-2021 10:22 8 KB JEscott to previous | to last
3 26-Nov-2021 10:22 7 KB JEscott to previous | to last
2 26-Nov-2021 10:22 4 KB JEscott to previous | to last
1 26-Nov-2021 10:22 4 KB JEscott to last

Page References

Incoming links Outgoing links
CONVERTING BENEFIT ENROLLMENTS
CONVERTING BENEFIT ENROLLMENTS

Version management

Difference between version and

At line 5 changed one line
The following table defines the layout of the INF_CV_ENROLLMENTS table:
The following table defines the layout of the [INF_CV_ENROLLMENTS] table:
At line 8 changed 25 lines
|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|BC_CODE] from [IBBC]
|AMOUNT | | NUMBER(18,6)| Set to the initial value for the [BC Code|BC_CODE]. This will be recalculated.
|AMOUNT_OVERRIDE| | NUMBER(18,6)| Set to the value for the [BC Code|BC_CODE]. This will not be recalculated.
|[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|BC_CODE] from [IBBC]
|[AMOUNT] | | NUMBER(18,6)| Set to the initial value for the [BC Code|BC_CODE]. This will be recalculated.
|[AMOUNT_OVERRIDE]| | NUMBER(18,6)| Set to the value for the [BC Code|BC_CODE]. This will not be recalculated.
At line 34 changed one line
If the [BC Code|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|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.
If the [BC Code|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|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|BENEFIT COMPONENTS] amounts will be calculated based on the rules defined for each.
At line 36 changed one line
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|BC_CODE] and Amount value will be needed during the conversion of the data.
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|BC_CODE] and [Amount|AMOUNT] value will be needed during the conversion of the data.
At line 38 changed one line
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|BC_CODE] values converted, there will be only one record which will have a null value in the [BC Code|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|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|BC_CODE] values that need to be converted.
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|BC_CODE] values converted, there will be only one record which will have a null value in the [BC Code|BC_CODE] and [Amount|AMOUNT] fields. If the plan and coverage combination requires amounts to be loaded and converted into the ENROLLMENT records, the [BC Code|BC_CODE], [Amount|AMOUNT] and [Amount Override|AMOUNT_OVERRIDE] fields will need values. There may be one or more records in the interface table based on the number of [BC Code|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|BC-B0300] | 03 | Employee Wage Rate | Use Enrollment Wage Rate
|[B0320|BC-B0320] | 09 | Eligible Earnings/Amount (before cap)| Use Enrollment Amount
|[B0360|BC-B0360] | 01 | Eligible Earnings/Amount (after cap) | Replace Enrollment Value
|[B0360|BC-B0360] | 02 | Eligible Earnings/Amount (after cap) | Accumulate Enrollment Value
|[B0361|BC-B0361] | 01 | Lump Sum Leave Pay Earnings | Replace Enrollment Value
|[B0363|BC-B0363] | 01 | Prior Period Adjustment Earnings | Replace Enrollment Value
|[B0365|BC-B0365] | 01 | Retroactive Salary Earnings | Replace Enrollment Value
|[B0366|BC-B0366] | 01 | Special Compensation Amount | Replace Enrollment Value
|[B0368|BC-B0368] | 01 | Worker's Compensation Earnings | Replace Enrollment Value
|[B0400|BC-B0400] | 02 | Coverage Calculation Method | Use Coverage from Enrollment
|[B0500|BC-B0500] | 02 | Coverage Step Method | Use Enrollment Step
|[B0600|BC-B0600] | 03 | Premium Rate | Use Value from Enrollment
|[B0610|BC-B0610] | 03 | Premium Rate 2 | Use Value from Enrollment
|[B0620|BC-B0620] | 02 | Premium Calculation Method | Use Premium Value on Enrollment
|[B0695|BC-B0695] | 02 | Survivor Amount | Use Value from Enrollment
|[B0800|BC-B0800] | 03 | Contribution Rate | Use Value from Enrollment
|[B0810|BC-B0810] | 02 | Contribution Method | Use Contribution Amt on Enrollment
|[B0840|BC-B0840] | 01 | Contribution Amount | Replace Value on Enrollment
|[B0840|BC-B0840] | 02 | Contribution Amount | Accumulate Value to Enrollment
|[B1000|BC-B1000] | 03 | Deduction Rate | Use Value from Enrollment
|[B1010|BC-B1010] | 02 | Deduction Method | Use Deduction Amt from Enrollment
|[B1040|BC-B1040] | 01 | Deduction Amount | Replace Enrollment Value
|[B1040|BC-B1040] | 02 | Deduction Amount | Accumulate Enrollment Value
|[B1070|BC-B1070] | 02 | CSB Deduction (Non-RRSP option) | Enrollment
|[B1072|BC-B1072] | 02 | CSB Deduction (RRSP option) | Enrollment
|[B1210|BC-B1210] | 02 | Taxable Benefit Method | Use Taxable Benefit on Enrollment
|[B1210|BC-B1210] | 03 | Taxable Benefit Method | Use Taxable Benefit on Enrollment
|[B1300|BC-B1300] | 01 | Bond Denomination | Specified Value or from Enrollment
|[B1310|BC-B1310] | 01 | Deduction Amount | Specified Value or from Enrollment
|[B1320|BC-B1310] | 01 | Amount Available for Bond Purchase | Enrollment Total + Current Deduction
|[B1340|BC-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|PLAN_CODE] and [coverage code|COVERAGE_CODE], no [BC Codes|BC_CODE] or [Amounts|AMOUNT] are needed to convert these records.
----
![Notes|Edit:Internal.CONVERTING+BENEFIT+ENROLLMENTS]
[{InsertPage page='Internal.CONVERTING+BENEFIT+ENROLLMENTS' default='Click to create a new notes page'}]
This page (revision-5) was last changed on 26-Nov-2021 10:22 by JEscottTop