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 43 changed 31 lines
|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
|[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
At line 76 added 5 lines
----
![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