Running the Enrollment Reporting Snapshot Process
The Enrollment Reporting Snapshot process extracts data that you specified on the Enrollment Snapshot Options page from the database and writes it to the BN_SNAP_PER, BN_SNAP_JOB, and BN_SNAP_PLAN tables. Each time that you run the Enrollment Reporting Snapshot process, the data in the snapshot tables is cleared and new information is written.
Every time that the Enrollment Reporting Snapshot or Carrier Report processes are run, a record is written to the BN_SNAP_HIST table. You can review this log using the Review Interface Run History page.
This section provides an overview of database record layout details and lists the pages used to run the Enrollment Reporting Snapshot process.
Page Name |
Definition Name |
Usage |
---|---|---|
Refresh Benefits Snapshot |
RUN_BEN100A |
Extract information from the Manage Base Benefits business process tables into the BN_SNAP_PER, BN_SNAP_JOB, and BN_SNAP_PLAN tables. |
Review Interface Run History |
BN_SNAP_HIST |
Review a history of when the Enrollment Reporting Snapshot, the Carrier Interface Extract, and the Carrier Interface Supplier Integration processes were run. |
Snapshot History Detail |
BN_SNAP_HIST_DTL |
Review additional information about the history record. |
Review Snapshot Information |
BN_SNAP_PLAN |
Review the information extracted by the Enrollment Reporting Snapshot process. |
Enrollment Snapshot Detail |
BN_SNAP_PLAN_DTL |
View additional information regarding the selected enrollment or allocation record. |
The following tables are populated by the Enrollment Reporting Snapshot process and used by the Carrier Interface Extract and Carrier Interface Supplier Integration processes. You need this information when creating a provider-specific SQR (Structured Query Report) extract.
BN_SNAP_PER
This table includes one row per participant (employee, dependent/beneficiary, and so on).
COLUMN |
KEY |
NOTES |
---|---|---|
EMPLID |
Yes |
|
DEPENDENT_BENEF |
Yes |
Blank for employees. Populated for dependents and beneficiaries. |
EFFDT |
No |
The as of date specified by the user on the run control page. The value is the same for every row. Simplifies finding related displays and descriptions. |
NAME |
||
LAST_NAME |
Alt |
|
FIRST_NAME |
||
NAME_PREFIX |
||
NATIONAL_ID |
Primary national ID. If the employee, dependent, or beneficiary has not been assigned a primary ID, the field is blank. |
|
BIRTHDATE |
||
SEX |
||
RELATIONSHIP |
Blank for employees. |
|
SMOKER |
Y/N |
|
STUDENT |
Y/N; Applies only to dependents and beneficiaries. |
|
STUDENT_STATUS_DT |
Applies only to dependents and beneficiaries. |
|
DISABLED |
Applies only to dependents and beneficiaries. |
|
COUNTRY |
||
ADDRESS1 |
||
ADDRESS2 |
||
ADDRESS3 |
||
ADDRESS4 |
||
CITY |
||
NUM1 |
||
NUM2 |
||
HOUSE_TYPE |
||
ADDR_FIELD1 |
||
ADDR_FIELD2 |
||
ADDR_FIELD3 |
||
COUNTY |
||
STATE |
||
POSTAL |
||
GEO_CODE |
||
IN_CITY_LIMIT |
||
HOME_PHONE |
BN_SNAP_JOB
One row exists per employee/benefit record/COBRA event; no rows exists for dependents and beneficiaries. For an employee with multiple jobs, all job-related information is taken from the employee's primary job for the indicated benefit record.
COLUMN |
KEY |
NOTES |
---|---|---|
EMPLID |
Yes |
|
BENEFIT_RCD_NBR |
Yes |
|
COBRA_EVENT_ID |
Yes |
Nonzero indicates a COBRA enrollment event for this employee ID/benefit record number. |
EMPL_RCD |
Primary job as of the date entered on the run control page. |
|
JOB_EFFDT |
Effective date of the primary job on the Job table. |
|
JOB_EFFSEQ |
Effective sequence of the primary job on the Job table. |
|
EFFDT |
No |
The as of date entered on the run control page. The value is the same for every row. Simplifies finding related displays and descriptions. |
SERVICE_DT |
Primary job. |
|
BENEFIT_PROGRAM |
||
COMPANY |
Primary job. |
|
PAYGROUP |
Primary job. |
|
CURRENCY_CD |
Currency code for the benefit program. |
|
PAY_SYSTEM_FLG |
Primary job. |
BN_SNAP_PLAN
This table includes one row per employee-level enrollment and one row per dependent/beneficiary attachment to an enrollment.
Column |
Key |
Notes |
---|---|---|
EMPLID |
Yes |
|
DEPENDENT_BENEF |
Yes |
A blank entry indicates an employee-level enrollment. A populated entry indicates a dependent enrollment or beneficiary allocation. |
BENEFIT_RCD_NBR |
Yes |
This is the EMPL_RCD field from the enrollment pages. |
COBRA_EVENT_ID |
Yes |
A nonzero entry indicates a COBRA event for this employee ID/benefit record number. |
PLAN_TYPE |
Yes |
|
COVERAGE_ELECT |
Yes |
Must be a key to allow for implied terminations. |
EFFDT |
No |
The as of date specified by the user on the run control page. The value is the same for every row. Simplifies finding related displays and descriptions. |
BENEFIT_PLAN |
Coverage_Elect = E indicates an enrollment. Coverage_Elect = T indicates that the plan is being terminated. If the field is blank and Coverage_Elect = T or W, no prior enrollment was made. |
|
SETID |
Comes from the benefit plan definition. It is blank for 7x plans. |
|
VENDOR_ID |
Blank for 7x plans. |
|
GROUP_NBR |
Blank for 7x plans. |
|
COVRG_CD |
Applies only to 1x plan types. |
|
LIFE_ADD_COVRG |
Applies only to 2x plan types. This is the code from the plan definition, not an employee enrollment. |
|
COVERAGE_BEGIN_DT |
For enrollments, indicates the first day of coverage. For terminations, indicates the first day that coverage is no longer in effect. |
|
COVERAGE_ELECT_DT |
Indicates the date that this enrollment/termination was entered into the system. |
|
DEDUCTION_BEGIN_DT |
For enrollments, indicates the first day that deductions and credits are in effect. For terminations, it indicates the first day that deductions and credits should stop. |
|
ENROLLMENT_DT |
Original enrollment date (coverage begin date) for this plan. This is the earliest continuous coverage begin date for this same benefit plan, ignoring change of coverage code (1x) and coverage amount (2x). Populated only if the INCLUDE_ORIG option is selected, and only for employee-level rows where COVERAGE_ELECT = E. |
|
ANNUAL_PLEDGE |
Applies only to 6x plan types. |
|
FLAT_DED_AMT |
Applies only to 4x plan types. Zero if employee is not contributing on a before-tax basis or contributing a percent of earnings. |
|
PCT_GROSS |
Applies only to 4x plan types. Zero if employee is not contributing on a before-tax basis or contributing a flat amount. |
|
FLAT_DED_AMT_ATAX |
Applies only to 4x plan types. Zero if employee is not contributing on an after-tax basis or contributing a percent of earnings. |
|
PCT_GROSS_ATAX |
Applies only to 4x plan types. Zero if employee is not contributing on an after-tax basis or contributing a flat amount. |
|
VOLUNTARY_AMT |
Applies only to 8x plan types. Zero if employee is not contributing a voluntary flat amount or is contributing a voluntary percentage of earnings. |
|
VOLUNTARY_PCT |
Applies only to 8x plan types. Zero if employee is not contributing a voluntary percentage of earnings or is contributing a voluntary flat amount. |
|
VACN_HOURS |
Applies only to 9x plan types. |
|
VACN_BUY_FLAT_AMT |
Applies only to 9x plan types. |
|
VACN_SELL_FLAT_AMT |
Applies only to 9x plan types. |
|
BENEF_PCT |
Applies only to beneficiary records in plan types 2x, 4x, or 8x. Zero if the beneficiary allocation is a flat amount. |
|
FLAT_AMOUNT |
See the Flat Amount Field section following this table. |
|
FACTOR_XSALARY |
Applies only to employee records for 2x plan types. This field contains the salary factor coverage defined at either the plan level or employee level. |
|
EXCESS |
Applies only to 2x, nondependent plans and 8x dependent records. A Y in the field indicates that this beneficiary is to receive any excess benefit distribution. |
|
CONTINGENT |
Applies only to 2x, nondependent plans and 8x dependent records. A Y in the field indicates that this beneficiary is a contingent beneficiary. |
|
DED_CUR |
The amount of the last payroll deduction calculated for this enrollment. This field is populated only if INCLUDE_DED is selected. |
|
PAY_END_DT |
The pay end date of the last payroll deduction calculated for this enrollment. This field is populated only if INCLUDE_DED is selected. |
|
CALCULATED_BASE |
Applies only to 2x and 3x plans and only if Calculate Life/Disability Covg is selected on the Enrollment Snapshot Options page. This field is zero for dependents and beneficiaries and for employees with Sum of Dependent Coverage elections for Dependent Life/AD&D plans. This field indicates the calculated benefit amount with respect to coverage minimum/maximum, multiple jobs, and rounding rules for coverage purposes. |
|
PREMIUM_BASE |
Applies only to 2x and 3x plans and only if Calculate Life/Disability Covg is selected on the Enrollment Snapshot Options page. This field is zero for dependents and beneficiaries and for employees with Sum of Dependent Coverage elections for dependent life/AD&D plans. This field indicates the calculated benefit amount with respect to coverage minimum/maximum, multiple jobs, and rounding rules for premium purposes. |
|
HLTH_PROVIDER_ID |
Applies only to 1x plan types. This field indicates the primary care physician. |
|
PREVIOUSLY_SEEN |
Applies only to 1x plan types. |
|
DEPBEN_RIDER_FLG |
Applies only to dependents and beneficiaries. Y indicates that this coverage or allocation is court-ordered. |
|
CALC_RULES_ID |
Applies only to 2x and 3x plans and only if Calculate Life/Disability/Covg is selected on the Enrollment Snapshot Options page. Identifies the calculation rule the system uses when calculating CALCULATED_BASE and PREMIUM_BASE. |
Understanding the Flat Amount Field
This field has different meanings depending upon the plan type and the plan context. In general, this field represents a flat amount of Life/AD&D coverage defined at either the plan level or the employee level; the amount may be in addition to a factor of salary coverage. For dependent life and dependent AD&D plans, it may represent the amount of coverage elected for a particular dependent. For nondependent Life/AD&D plans and pension plans, it may represent a flat amount beneficiary allocation.
The following tables show how this field is used in each context.
Nondependent Life/AD&D Plan Types
This table shows how the Flat Amount field is used with nondependent Life and ADD plan types. Each column represents the value of LIFE_ADD_COVRG, which defines how the coverage is calculated for a plan.
Participant |
Flat Amount |
Flat + Factor |
EE-Specified |
Special Calc |
Sum of Dependents |
---|---|---|---|---|---|
Employee |
Flat amount of coverage |
Flat amount portion of total coverage |
Flat amount, or flat amount portion of total coverage |
Zero (not applicable) |
Zero (not applicable) |
Dependent/Beneficiary |
Beneficiary allocation (if flat amount) |
Beneficiary allocation (if flat amount) |
Beneficiary allocation (if flat amount) |
Beneficiary allocation (if flat amount) |
Zero (not applicable) |
Dependent Life and Dependent AD&D
This table shows how the Flat Amount field is used with dependent Life and ADD plan types:
Participant |
Flat Amount |
Flat + Factor |
EE-Specified |
Special Calc |
Sum of Dependents |
---|---|---|---|---|---|
Employee |
Flat amount of coverage for each dependent |
Flat amount portion of total coverage for each dependent |
Flat amount, or flat amount portion of total coverage for each dependent |
Zero (not applicable) |
Zero |
Dependent/Beneficiary |
Zero (not applicable) |
Zero (not applicable) |
Zero (not applicable) |
Zero (not applicable) |
This dependent's coverage |
Pension Plans
This table shows how the Flat Amount field is used with pension plan types:
Participant |
Description |
---|---|
Employee |
Zero (not applicable) |
Dependent/Beneficiary |
Beneficiary allocation (if flat amount allocation is elected) |