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 Snapshot 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)