BN_SNAP_PER

(SQL Table)
 Index Back

Snapshot Personal EE/Dep Data

BN_SNAP_PER contains biographical information for each employee processed in the Enrollment Reporting Snapshot process. The data contained in the record is data in effect as of a user-specified "As Of" date. In addition to containing employee data, there is also a row for each dependent/beneficiary having any rows in the BN_SNAP_PLAN enrollment table.

  • Parent Record Of BN_SNAP_JOB

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
    01=default
    3 EFFDT Date(10) DATE Effective Date
    4 NAME_EFFDT Date(10) DATE This is the EFFDT of Name of Employee/Subscriber in HIPAA EDI File.
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    7 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    8 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    9 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
    10 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
    11 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
    12 BIRTHDATE Date(10) DATE Date of Birth
    13 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    14 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
    C=Common-Law
    D=Divorced
    E=Separated
    H=Head of Household
    L=DissDeclLost Civil Partner
    M=Married
    P=Civil Partnership
    S=Single
    T=Surviving Civil Partner
    U=Unknown
    V=Dissolved Civil Partnership
    W=Widowed
    15 MAR_STATUS_DT Date(10) DATE This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */
    16 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
    17 COVERED_PERSON_TYP Character(2) VARCHAR2(2) NOT NULL Covered Person Type: Added fields for Benefits ERISA implementation. Groups relationship codes into categories, i.e. Son, Daughter => Child
    AC=Adult Child
    CH=Child
    DP=Domestic Partner
    EE=Employee
    NQ=Non-Qualified Dependent
    OD=Other Qualified Dependent
    SP=Spouse
    SS=US Same-Sex Spouse
    XS=ExSpouse
    18 SMOKER Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee or dependent is a smoker.
    N=Non Smoker
    Y=Smoker
    19 SMOKER_DT Date(10) DATE Smoker Status Date
    20 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
    N=No
    Y=Yes
    21 STUDENT_STATUS_DT Date(10) DATE Student Status Date
    22 MEDICARE_EFFDT Date(10) DATE This is the Medicare Subscription Date for members in HIPAA EDI File.
    23 MEDICARE_A_IND Character(1) VARCHAR2(1) NOT NULL Medicare Type A indicator

    Default Value: N

    24 MEDICARE_B_IND Character(1) VARCHAR2(1) NOT NULL Medicare Type B Indicator

    Default Value: N

    25 MEDICARE_ELIG_RSN Character(1) VARCHAR2(1) NOT NULL Medicare Eligibility Reason. Used in the Loop 2000 INS of the HIPAA file.
    0=Age
    1=Disability
    2=End Stage Renal Disease
    26 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
    27 ADDR_EFFDT Date(10) DATE Address Effective Date
    28 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    29 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    30 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    31 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    32 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    33 CITY Character(30) VARCHAR2(30) NOT NULL City
    34 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    35 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    36 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    37 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    38 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    39 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    40 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    41 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    42 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    43 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    44 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    45 ADDR_OTHER_EFFDT Date(10) DATE Other Address EFFDT
    46 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

    Prompt Table: COUNTRY_TBL

    47 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
    48 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
    49 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
    50 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
    51 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
    52 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
    53 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

    Prompt Table: STATE_OTH

    54 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
    55 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
    56 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
    57 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
    AB=House Boat Reference
    WW=House Trailer Reference
    58 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
    59 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
    60 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
    61 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other

    Y/N Table Edit

    62 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
    63 HOME_PHONE Character(24) VARCHAR2(24) NOT NULL Home Phone #
    64 DT_OF_DEATH Date(10) DATE Date of Death