BN_SNAP_PLAN

(SQL Table)
 Index Back

Snapshot EE/Dep Benefit Data

BN_SNAP_PLAN is a "snapshot" of employee enrollments, as well as depdent coverage and beneficiary allocations as of a user specified date. This table is used by the sample Carrier Interface extract process. An index on SETID and VENDOR_ID was added to improve the performance of the HIPAA EDI 834 Extract process.

  • Parent record: BN_SNAP_JOB

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
    3 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
    4 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
    01=default
    5 PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.
    6 COVERAGE_ELECT Character(1) VARCHAR2(1) NOT NULL Coverage Election
    E=Elect
    T=Terminate
    W=Waive
    7 EFFDT Date(10) DATE Effective Date
    8 ENROLLMENT_DT Date(10) DATE Original Enrollment Date
    9 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan
    10 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    11 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    12 GROUP_NBR Character(30) VARCHAR2(30) NOT NULL Group Number
    13 COVRG_CD Character(2) VARCHAR2(2) NOT NULL Coverage Code

    Prompt Table: COVRG_CD_TBL

    14 COVERAGE_TYPE Character(1) VARCHAR2(1) NOT NULL This field is used to define the coverage level.
    1=Plan Level
    2=Employee Level
    15 BN_FORMULA_ID Character(10) VARCHAR2(10) NOT NULL This field is key for defining the formula and formula can be uniquely identified by this field.
    16 ENROLLE_TYPE Character(1) VARCHAR2(1) NOT NULL The Field is used to determine the insuerd person type.
    1=Coverage Employee
    2=Coverage Dependents
    17 SUM_DEP_COVG Character(1) VARCHAR2(1) NOT NULL This Field determines the if need to sum the dependent coverage amount or not.
    18 COVERAGE_BEGIN_DT Date(10) DATE Coverage Begin Date
    19 COVERAGE_ELECT_DT Date(10) DATE Coverage Elect Date
    20 DEDUCTION_BEGIN_DT Date(10) DATE Deduction Begin Date
    21 ANNUAL_PLEDGE Signed Number(10,2) DECIMAL(8,2) NOT NULL Annual Pledge
    22 FLAT_DED_AMT Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amount
    23 PCT_GROSS Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross
    24 FLAT_DED_AMT_ATAX Number(9,2) DECIMAL(8,2) NOT NULL Flat Deduction Amt After-Tax
    25 PCT_GROSS_ATAX Number(7,3) DECIMAL(6,3) NOT NULL Percent of Gross After-Tax
    26 VOLUNTARY_AMT Number(11,2) DECIMAL(10,2) NOT NULL Voluntary Contribution Amount
    27 VOLUNTARY_PCT Number(7,3) DECIMAL(6,3) NOT NULL Voluntary Contrib Pct
    28 VACN_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Vacation Buy/Sell Hours
    29 VACN_BUY_FLAT_AMT Number(8,2) DECIMAL(7,2) NOT NULL Vacation Buy Flat Amount
    30 VACN_SELL_FLAT_AMT Number(8,2) DECIMAL(7,2) NOT NULL Vacation Sell Flat Amount
    31 BENEF_PCT Number(3,0) SMALLINT NOT NULL Percent Beneficiary Paid
    32 FLAT_AMOUNT Signed Number(9,0) DECIMAL(8) NOT NULL Flat Amount
    33 FACTOR_XSALARY Number(5,3) DECIMAL(4,3) NOT NULL Factor
    34 EXCESS Character(1) VARCHAR2(1) NOT NULL Excess
    N=No
    Y=Yes
    35 CONTINGENT Character(1) VARCHAR2(1) NOT NULL Contingent
    36 DED_CUR Signed Number(12,2) DECIMAL(10,2) NOT NULL Current Deduction
    37 BN_PREMIUM_AMT Signed Number(12,2) DECIMAL(10,2) NOT NULL This is the "total" premium for a benefit plan - that is, the amount that must be remitted to the provider.
    38 PAY_END_DT Date(10) DATE Pay Period End Date
    39 CALCULATED_BASE Number(11,2) DECIMAL(10,2) NOT NULL Coverage Base
    40 PREMIUM_BASE Number(11,2) DECIMAL(10,2) NOT NULL Premium Base
    41 HLTH_PROVIDER_ID Character(30) VARCHAR2(30) NOT NULL Health Provider ID
    42 PREVIOUSLY_SEEN Character(1) VARCHAR2(1) NOT NULL Previously Seen
    43 DEPBEN_RIDER_FLG Character(1) VARCHAR2(1) NOT NULL Court Order Flag
    44 CALC_RULES_ID Character(4) VARCHAR2(4) NOT NULL Calculation Rules Table ID
    45 ORIG_ENROLL_DT Date(10) DATE Original enrollment date