!!!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|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.

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. 

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.

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.