COBRApoint QB Import Specification



|[pic] |

|QB Import Specification |

|(COBRA Qualifying Event) |

|DEFINES IMPORT VERSION 1.2 |

| |

| |

| |

|Revised March 2021 | Benefit Strategies, LLC |

| |

| |

QB Import Specification

Table of Contents

1 Introduction 1

2 Definition of Terms 1

2.1 QB – Qualified Beneficiary 1

2.2 CSV – Comma Separated Values file 1

2.3 Import Version 1

2.4 Line Identifier 1

3 Import File Process 2

4 Import File Layout 2

5 Import Line Identifiers (Section Definitions) 3

5.1 [VERSION] 3

5.2 [QB] 3

5.3 [QBEVENT] 4

5.4 [QBPLANINITIAL] 7

5.5 [QBPLAN] 8

5.6 [QBDEPENDENT] - If Applicable 9

5.7 [QBDEPENDENTPLANINITIAL] 11

5.8 [QBSUBSIDYSCHEDULE] - If Applicable 11

5.9 [QBPLANMEMBERSPECIFICRATEINITIAL] - If Applicable 11

5.10 [QBPLANMEMBERSPECIFICRATE] - If Applicable 12

6 Data Types 12

7 Important Items to Note 13

8 Sample Import File 14

QB Import Specification

Introduction

This specification will explain the format and function of the import file used to systematically load QBs into the COBRApoint system.

Definition of Terms

This document uses various terms that may have different meanings to different audiences. Section 2 will help to explain these terms in the context of this document.

1 QB – Qualified Beneficiary

A QB is an individual who has experienced a COBRA qualifying event and is eligible to enroll in COBRA coverage. The importing of a QB into the COBRApoint system results in a COBRA Specific Rights Notice being generated.

2 CSV – Comma Separated Values file

A CSV file is a text file that contains values on each row that are separated by commas.

3 Import Version

Each file should include the [VERSION] being used at the top of the file. An example of what this should look like is provided below.

[VERSION],1.2

4 Line Identifier

Each text line that is imported must have as its first field a “Line Identifier” (see sections 4 and 5 below). The line identifier defines what fields come next on the text line.

Import File Process

A CSV import file should be created that matches the specifications defined in this document and be encoded in ANSI format. Please note, you should NOT use Excel to edit or save these files as Excel will alter the format of large numeric columns (such as SSN) and make them unreadable to COBRApoint. This import file might be built with a simple text editor (such as Notepad) or might be generated from an export program written for an existing database.

This file should be set up as new changes only. No duplicate data from previous files should be passed.

Import File Layout

The “line identifiers” and their meanings are listed below:

|Line Identifier |Required to Complete |Description |

| |a QB | |

|[QB] |( |The main QB record |

|[QBEVENT] |( |The QB’s qualifying event information |

|[QBPLANINITIAL] / [QBPLAN] |( |Defines a single client plan that the QB is eligible for |

| | |under COBRA. There must be at least one of these lines in |

| | |the import file for each QB, but there may be several for |

| | |each QB if the QB is eligible for more than one plan. |

| | | |

|[QBDEPENDENT] | |Defines a single dependent that is also eligible for COBRA |

| | |under the QB. There may be several of these lines for a QB |

| | |if there are multiple dependents under this QB. |

| | | |

|[QBDEPENDENTPLANINITIAL] | |Defines a single QB plan that a dependent is enrolled on for|

| | |this QB. These records must be under the appropriate |

| | |[QBDEPENDENT] record and there may be multiple of these for |

| | |a single dependent. |

| | | |

|[QBSUBSIDYSCHEDULE] | |Defines a single record of a subsidy schedule for a QB. |

| | |These are optional and there may be several of these lines |

| | |to define a schedule for multiple insurance types. |

| | | |

|[QBPLANMEMBERSPECIFICRATEINITIAL] / [QBPLANMEMBERSPECIFICRATE] | |If a QB is under a plan that has a rate type of “Member |

| | |Specific Rates”, then these lines are used to describe the |

| | |rate to be used for this QB. These records should be |

| | |immediately after the [QBPLANINITIAL] / [QBPLAN] record that|

| | |they refer to. |

| | | |

Import Line Identifiers (Section Definitions)

The Line Identifiers that were explained in the section above each expect various fields to follow them on their respective line. The tables below define each of these fields for each of the Line Identifiers.

Required fields will need to have a value after the comma that corresponds to the data type specified. Non-required fields may be left blank (nothing appearing between the commas).

|[VERSION] |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|VersionNumber |

|Integer |

|( |

|Use “1.2” for this import specification. |

| |

| |

|[QB] |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|ClientName |

|Text - 100 |

|( |

|The unique Client Name assigned in COBRApoint. |

| |

|ClientDivisionName |

|Text - 50 |

|( |

|The unique Client Division Name assigned in COBRApoint. |

| |

|Salutation |

|Text - 35 |

| |

|MR, MRS, MS, MISS, DR |

| |

|FirstName |

|Text - 50 |

|( |

| |

| |

|MiddleInitial |

|Text - 1 |

| |

| |

| |

|LastName |

|Text - 50 |

|( |

| |

| |

|SSN |

|SSN |

|( |

|Social Security Number |

| |

|IndividualID |

|Text - 50 |

| |

|Employee ID # |

| |

|Email |

|Email |

| |

| |

| |

|Phone |

|Phone |

| |

| |

| |

|Phone2 |

|Phone |

| |

| |

| |

|Address1 |

|Text - 50 |

|( |

| |

| |

|Address2 |

|Text - 50 |

| |

| |

| |

|City |

|Text - 50 |

|( |

| |

| |

|StateOrProvince |

|Text - 50 |

|( |

| |

| |

|PostalCode |

|Text - 35 |

|( |

| |

| |

|Country |

|Text - 50 |

| |

|Leave blank if the QB resides in the USA |

| |

|PremiumAddressSameAsPrimary |

|Boolean |

|( |

|Always set to True |

| |

|PremiumAddress1 |

|Text - 50 |

| |

|[Deprecated – do not use] |

| |

|PremiumAddress2 |

|Text - 50 |

| |

|[Deprecated – do not use] |

| |

|PremiumCity |

|Text - 50 |

| |

|[Deprecated – do not use] |

| |

|PremiumStateOrProvince |

|Text - 50 |

| |

|[Deprecated – do not use] |

| |

|PremiumPostalCode |

|Text - 35 |

| |

|[Deprecated – do not use] |

| |

|PremiumCountry |

|Text - 50 |

| |

|[Deprecated – do not use] |

| |

|Sex |

|Sex |

|( |

|F, M, U, Female, Male, Undisclosed |

| |

|DOB |

|Date |

|( |

|Date of Birth |

| |

|TobaccoUse |

|Text - 35 |

|( |

|YES, NO, UNKNOWN |

| |

|EmployeeType |

|Text - 35 |

|( |

|FTE, PTE, UNKNOWN |

| |

|EmployeePayrollType |

|Text - 35 |

|( |

|EXEMPT, NONEXEMPT, UNKNOWN, SALARY, HOURLY, EXECUTIVE |

| |

|YearsOfService |

|Integer |

| |

|Not used currently and only informational |

| |

|PremiumCouponType |

|Text - 35 |

|( |

|COUPONBOOK |

| |

|UsesHCTC |

|Boolean |

|( |

|True if this QB uses the Health Care Tax Credit (HCTC) |

| |

|Active |

|Boolean |

|( |

|Should always be set to TRUE |

| |

|AllowMemberSSO |

|Boolean |

| |

|This field should be left blank. |

| |

|BenefitGroup |

|Text - 50 |

| |

|Can be used for reporting purposes. |

| |

|AccountStructure |

|Text - 50 |

| |

|Can be used for reporting purposes. |

| |

|ClientSpecificData |

|Text - 50 |

| |

|Can be used for reporting purposes. |

| |

|SSOID |

|Text - 50 |

| |

|This field should be left blank. |

| |

| |

|[QBEVENT] |

|There are three different types of events. A breakdown is included below. The field details for this line type are then listed below the table. |

| |

|Note: When an employee terminates, it is very important that the correct event type value is passed on the QB file. Please note, TERMINATION is meant to be used for |

|voluntary terminations only. If an employee is involuntarily terminated, the INVOLUNTARYTERMINATION event type value should be passed. |

| |

| |

|Type |

|Number Months of COBRA |

|File Event Type Value |

|Event Type Definition |

| |

|Employee |

|18 |

|TERMINATION |

|Use for voluntary termination only! The member is no longer employed with the employer that provides the plan because the member resigned. |

| |

|Employee |

|18 |

|INVOLUNTARYTERMINATION |

|The member is no longer employed with the employer that provides the plan because the employer dismissed the member. |

| |

|Employee |

|18 |

|TERMINATIONWITHSEVERANCE |

|The member is no longer employed with the employer that provides the plan because the employer dismissed the member with compensation pay. |

| |

|Employee |

|18 |

|RETIREMENT |

|The member is no longer employed with the employer that provides the plan because the member has concluded their career. |

| |

|Employee |

|18 |

|REDUCTIONINFORCE |

|The member is no longer employed with the employer that provides the plan because the employer diminished its employees. |

| |

|Employee |

|18 |

|REDUCTIONINHOURS-STATUSCHANGE |

|The member works less than full time for the employer that provides the plan for various reasons. |

| |

|Employee |

|18 |

|REDUCTIONINHOURS-ENDOFLEAVE |

|The member works less than full time for the employer that provides the plan because the member is on an authorized absence from their employment. |

| |

|Employee |

|18 |

|LOSSOFELIGIBILITY |

|The member is no longer qualified to participate in the plan through their employer for various reasons. |

| |

|Employee |

|18 |

|BANKRUPTCY |

|The member is no longer qualified to participate in the plan through their employer because the employer legally declared financial insolvency. |

| |

|Employee |

|18 |

|RETIREEBANKRUPTCY |

|The member is no longer qualified to participate in the plan through their employer because the member has concluded their career and the employer has legally declared|

|financial insolvency. |

| |

|Employee |

|18 |

|WORKSTOPPAGE |

|The member is not actively employed with the employer that provides the plan because the member is part of a cessation of work by a group of employees as a means of |

|protest. |

| |

|Dependent |

|36 |

|DIVORCELEGALSEPARATION |

|A dependent requires their own record to maintain their coverage under the plan because they are divorced or separated from the employee member under whom they |

|originally qualified for the plan. |

| |

|Dependent |

|36 |

|DEATH |

|A dependent requires their own record to maintain their coverage under the plan because the employee member under whom they originally qualified for the plan is |

|deceased. |

| |

|Dependent |

|36 |

|INELIGIBLEDEPENDENT |

|A dependent requires a record because they no longer qualify for coverage under the employee member's plan but they do qualify for COBRA coverage. For example, if a |

|dependent child reaches the age limit for coverage under their parent's plan, the dependent child can elect COBRA coverage under the plan. |

| |

|Dependent |

|36 |

|MEDICARE |

|A dependent requires their own record to maintain their coverage under the plan because the employee member under whom they originally qualified for the plan qualifies|

|for coverage under Medicare. |

| |

|Military |

|24 |

|USERRA-TERMINATION |

|The member is not actively employed or employed less than full time with the employer that provides the plan because the member is performing military duty. The |

|member's continued status as an employee of the employer is protected by the Uniformed Services Employment and Reemployment Rights Act (USERRA) which states that |

|individuals performing military duty of more than 30 days may elect to continue employer sponsored health care for up to 24 months. |

| |

|Military |

|24 |

|USERRA-REDUCTIONINHOURS |

|The member is not actively employed or employed less than full time with the employer that provides the plan because the member is performing military duty. The |

|member's continued status as an employee of the employer is protected by the Uniformed Services Employment and Reemployment Rights Act (USERRA) which states that |

|individuals performing military duty of more than 30 days may elect to continue employer sponsored health care for up to 24 months. |

| |

| |

| |

| |

| |

| |

| |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|EventType |

|Text - 35 |

|( |

|TERMINATION |

| |

|INVOLUNTARYTERMINATION |

| |

|TERMINATIONWITHSEVERANCE |

| |

|RETIREMENT |

| |

|REDUCTIONINFORCE |

| |

|REDUCTIONINHOURS-STATUSCHANGE |

| |

|REDUCTIONINHOURS-ENDOFLEAVE |

| |

|LOSSOFELIGIBILITY |

| |

|BANKRUPTCY |

| |

|RETIREEBANKRUPTCY |

| |

|WORKSTOPPAGE |

| |

|DIVORCELEGALSEPARATION |

| |

|DEATH |

| |

|INELIGIBLEDEPENDENT |

| |

|MEDICARE |

| |

|USERRA-TERMINATION |

| |

|USERRA-REDUCTIONINHOURS |

| |

| |

| |

| |

|EventDate |

|Date |

|( |

|The Qualifying Event Date that the Event Type occurred on / last day on active coverage. Do not adjust for plan benefit termination types, just use the actual date of |

|the event. |

| |

| |

|EnrollmentDate |

|Date |

|( |

|Original enrollment date of the member’s current medical plan. |

| |

| |

|EmployeeSSN |

|SSN |

|( |

|Employee’s SSN. Required if the Event Type is a dependent event. |

| |

| |

|EmployeeName |

|Text - 100 |

|( |

|Employee’s Name. Required if the Event Type is a dependent event. |

| |

| |

|SecondEventOriginalFDOC |

|Date |

| |

|DEPRECATED – any value will be ignored |

| |

| |

|[QBPLANINITIAL] |

|The [QBPLANINITIAL] record type enters a QB initially on a plan with a coverage level. |

| |

|Note: There are two types of plan lines available for importing a QB’s plans, [QBPLANINITIAL] and [QBPLAN]. The preferred type is [QBPLANINITIAL] as it is the |

|simplest way to import a QB plan. The [QBPLAN] line type that’s mentioned below in section 5.5 would be used if a client has bundled plans. |

| |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|PlanName |

|Text – 50 |

|( |

|The unique Client Plan Name |

| |

|CoverageLevel |

|Text – 35 |

|( |

|The value for this field depends on the tier structure of each individual plan that’s built in our system. |

| |

|The following is a full list of the values that that the system accepts: |

| |

|EE, EE+SPOUSE, EE+DOMESTICPARTNER, EE+CHILD, EE+CHILDREN, EE+FAMILY, EE+1, EE+2, EE+3, EE+4, SPOUSEONLY, SPOUSE+CHILD, SPOUSE+CHILDREN, CHILDONLY, CHILDRENONLY, |

|CHILDONLY18OROLDER, CHILDRENONLY18OROLDER, EE+1CHILD, EE+2CHILDREN, EE+3CHILDREN, EE+4CHILDREN, EE+5ORMORECHILDREN, EE+SPOUSE+1CHILD, EE+SPOUSE+2CHILDREN, |

|EE+SPOUSE+3CHILDREN, EE+SPOUSE+4CHILDREN, EE+SPOUSE+5ORMORECHILDREN, SPOUSE+1CHILD, SPOUSE+2CHILDREN, SPOUSE+3CHILDREN, |

|SPOUSE+4CHILDREN, SPOUSE+5ORMORECHILDREN |

| |

| |

| |

|NumberOfUnits |

|Decimal |

| |

|Leave this field blank if the plan is non units based. |

| |

| |

| |

|Example [QBPLANINITIAL] line: |

| |

|[QBPLANINITIAL],Medical Plan,EE+FAMILY,, |

| |

| |

|[QBPLAN] |

|The [QBPLAN] record is used by clients that have bundled plans. Normal QB plan records should be entered with the [QBPLANINITIAL] record instead (see section 5.4 |

|above). |

| |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|PlanName |

|Text – 50 |

|( |

|The unique Client Plan Name |

| |

|StartDate |

|Date |

|( |

|The start date that the QB will begin coverage on this plan. This should be set to the First Day of COBRA for the Plan in the field above. |

| |

|EndDate |

|Date |

| |

|Optional, the end date the QB will cease coverage on this plan. This should be set to the Last Day of COBRA for the Plan in the field above. |

| |

|CoverageLevel |

|Text – 35 |

|( |

|The value for this field depends on the tier structure of each individual plan that’s built in our system. |

| |

|The following is a full list of the values that that the system accepts: |

| |

|EE, EE+SPOUSE, EE+DOMESTICPARTNER, EE+CHILD, EE+CHILDREN, EE+FAMILY, EE+1, EE+2, EE+3, EE+4, SPOUSEONLY, SPOUSE+CHILD, SPOUSE+CHILDREN, CHILDONLY, CHILDRENONLY, |

|CHILDONLY18OROLDER, CHILDRENONLY18OROLDER, EE+1CHILD, EE+2CHILDREN, EE+3CHILDREN, EE+4CHILDREN, EE+5ORMORECHILDREN, EE+SPOUSE+1CHILD, EE+SPOUSE+2CHILDREN, |

|EE+SPOUSE+3CHILDREN, EE+SPOUSE+4CHILDREN, EE+SPOUSE+5ORMORECHILDREN, SPOUSE+1CHILD, SPOUSE+2CHILDREN, SPOUSE+3CHILDREN, |

|SPOUSE+4CHILDREN, SPOUSE+5ORMORECHILDREN |

| |

| |

| |

|FirstDayOfCOBRA |

|Date |

| |

|The First Day of COBRA. Unless you wish to override the calculated FDOC, this field should be left blank. If left blank COBRApoint will determine the FDOC based on the|

|EventDate and the plan benefit termination type. |

| |

| |

| |

|LastDayOfCOBRA |

|Date |

| |

|The Last Day of COBRA. This field should be left blank. COBRApoint will determine the LDOC based on the FDOC and COBRADurationMonths. |

| |

| |

| |

|COBRADurationMonths |

|Integer |

| |

|The Number of months of COBRA is usually determined by the Event type. This may be left blank (preferred) and COBRApoint will determine the correct number of months. |

|It is typically 18 months, but can be extended on Dependent Event Types and USERRA Event Types. |

| |

| |

| |

|DaysToElect |

|Integer |

| |

|The Number of Days the QB has to Elect Coverage under COBRA. This may be left blank (preferred) and COBRApoint will determine the correct number of days. It is |

|typically 60 days. |

| |

| |

| |

|DaysToMake1stPayment |

|Integer |

| |

|The Number of Days the QB has to make their 1st full payment under COBRA. This may be left blank (preferred) and COBRApoint will determine the correct number of days. |

|It is typically 45 days. |

| |

| |

| |

|DaysToMakeSubsequentPayments |

|Integer |

| |

|The Number of Days the QB has to make their 1st full payment under COBRA. This may be left blank (preferred) and COBRApoint will determine the correct number of days. |

|It is typically 45 days. |

| |

| |

| |

|ElectionPostmarkDate |

|Date |

| |

|Always leave blank |

| |

|LastDateRatesNotified |

|Date |

| |

|Always leave blank |

| |

|NumberOfUnits |

|Decimal |

| |

|Leave this field blank if the plan is non units based. |

| |

| |

|SendPlanChangeLetterForLegacy |

|Boolean |

|( |

|This field should be set to False. |

| |

|PlanBundleName |

|Text – 50 |

| |

|Conditionally required for plans that are part of a bundle. |

| |

| |

| |

| |

| |

|Example [QBPLAN] line: |

| |

|[QBPLAN],Medical Plan,12/1/2011,,EE+FAMILY,,,,,,,,,,,, |

| |

|[QBDEPENDENT] - If Applicable |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|SSN |

|SSN |

| |

|Social Security Number |

| |

|Relationship |

|Text – 35 |

|( |

|SPOUSE, CHILD, DOMESTICPARTNER |

| |

|Salutation |

|Text – 35 |

| |

|MR, MRS, MS, MISS, DR |

| |

|FirstName |

|Text – 50 |

|( |

| |

| |

|MiddleInitial |

|Text – 1 |

| |

| |

| |

|LastName |

|Text – 50 |

|( |

| |

| |

|Email |

|Email |

| |

| |

| |

|Phone |

|Phone |

| |

| |

| |

|Phone2 |

|Phone |

| |

| |

| |

|AddressSameAsQB |

|Boolean |

|( |

|Set to True if the Dependent’s address is the same as the QB’s address. |

| |

|Note: Leaving the field blank defaults to False during import. |

| |

|If False is listed in this field, a separate SRN will be mailed to both the QB as well as each individual dependent event if the same address is listed. This will |

|result in the client being charged for each notice. |

| |

| |

|Address1 |

|Text – 50 |

| |

| |

| |

|Address2 |

|Text – 50 |

| |

| |

| |

|City |

|Text – 50 |

| |

| |

| |

|StateOrProvince |

|Text – 50 |

| |

| |

| |

|PostalCode |

|Text – 35 |

| |

| |

| |

|Country |

|Text – 50 |

| |

|Leave empty if the Dependent resides in the USA |

| |

| |

|EnrollmentDate |

|Date |

| |

|Original enrollment date of the dependent’s medical plan. |

| |

| |

|Sex |

|Sex |

| |

|M or F (This is required if the dependent is on a Sex based plan that sets rates based on the dependent’s Sex) |

| |

| |

|DOB |

|Date |

| |

|Date of Birth (This is required if the dependent is on an Age based plan that sets rates based on the dependent’s Age) |

| |

| |

|IsQMCSO |

|Boolean |

|( |

|True if the Dependent is under a Qualified Medical Child Support Order (QMCSO) |

| |

| |

|[QBDEPENDENTPLANINITIAL] |

|The [QBDEPENDENTPLANINITIAL] record type enters a QB Dependent initially on a plan. |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|PlanName |

|Text - 50 |

|( |

|The unique Client Plan Name |

| |

| |

|[QBSUBSIDYSCHEDULE] - If Applicable |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|InsuranceType |

|Text – 35 |

|( |

|MEDICAL, DENTAL, VISION, PHARMACY, FSA, HCRA, EAP, GAP, 401k, LIFE, NULIFE, MSA, PBA, HSA, NUOTHER1, NUOTHER2, GRPLIFE, NUGRPLIFE, VOLLIFE, NUVOLLIFE, CANCER, MERP, |

|DEPLIFE1, DEPLIFE2, DEPLIFE3, NUDEPLIFE1, NUDEPLIFE2, NUDEPLIFE3, MEDSTURIDER1, MEDSTURIDER2, MEDSTURIDER3, LTD, AD&D, CHIROPRACTIC, VEBA, CUSTOMBILLING, |

|LTDNONUNITBASED, LTDUNITBASED, STDNONUNITBASED, STDUNITBASED, |

|CRITICALILLNESS, ACCIDENTNONUNITBASED, ACCIDENTUNITBASED, VOLUNTARYOTHER, UOTHER1, UOTHER2, UOTHER3 |

| |

|SubsidyAmountType |

|Text – 35 |

|( |

|FLAT, PERCENTAGE |

| |

|StartDate |

|Date |

|( |

|Start date of the subsidy |

| |

|EndDate |

|Date |

|( |

|End date of the subsidy |

| |

|Amount |

|Money |

|( |

|Amount if FLAT or Percentage if PERCENTAGE of the subsidy. For example, use “50” if it is a 50% subsidy and the SubsidyAmountType is set to PERCENTAGE |

| |

|SubsidyType |

|Text – 35 |

| |

|EMPLOYER (defaults to EMPLOYER) |

| |

| |

| |

|[QBPLANMEMBERSPECIFICRATEINITIAL] - If Applicable |

|The [QBPLANMEMBERSPECIFICRATEINITIAL] record type is used to enter a member specific rate for a plan. [QBPLANMEMBERSPECIFICRATEINITIAL] must only be used with a |

|corresponding [QBPLANINITIAL] line. |

| |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|PlanName |

|Text – 50 |

|( |

|Unique Client plan name |

| |

|Rate |

|Decimal |

|( |

|The amount of the member specific rate |

| |

|[QBPLANMEMBERSPECIFICRATE] - If Applicable |

|The [QBPLANMEMBERSPECIFICRATE] record type is used to enter a member specific rate for a plan. [QBPLANMEMBERSPECIFICRATE] must only be used with a corresponding |

|[QBPLAN] line. |

| |

| |

|Column Name |

|Data Type |

|Required |

|Accepted Values |

| |

|PlanName |

|Text – 50 |

|( |

|Unique Client plan name |

| |

|StartDate |

|Date |

|( |

|The start date of the member specific rate on the plan. This should be set to the First Day of COBRA (FDOC) for the Plan in the field above. |

| |

| |

|EndDate |

|Date |

| |

|End Date of the member specific rate |

| |

|Rate |

|Decimal |

|( |

|The amount of the member specific rate |

| |

| |

| |

Data Types

The data types that are used in the various import sections are defined below. Please note, the fields in the import file are all text fields (since it is a CSV file) and that the definitions below explain the formatting of the text in that field.

|Data Type |Definition |

|Text |Can be any text value up to the number of characters specified. If the text contains a comma, then the entire field should be enclosed in|

| |double quotes (“). |

| | |

| |Examples |

| | |

| |Client Name: “Benefit Strategies, LLC” |

| |Last Name: “Doe, Jr.” |

|Integer |Can be any number from 0 to 32,767. No punctuation |

|Date |Entered as MM/DD/YYYY, where MM is the month, DD is the day and YYYY is the year. You can use a single digit or two digit leading zero |

| |for the month and day. The year should always be entered as 4 digits. |

|Boolean |True = "1", "Y", "YES", "T" or "TRUE", False="0", "N", "NO", "F" or "FALSE" |

|Money |Format as number with one period, no commas and always have at least one digit to the left of the period and 2 digits to the right of the|

| |period. IE: 12 cents would be entered 0.12 |

|SSN |Format as 9 digits – can also include the dashes. Note that leading zeros are required if the SSN begins with zeros. |

|Sex |Single character where M = Male, F = Female, U = Undisclosed. |

|Phone |Format as 10 digit |

|Email |Text – 100, with at least one “@” and at least one “.” after the @ symbol. |

|Decimal |Format as number with one period, no commas and have at least one digit to the left of the period and up to 4 digits after the period. |

| |Example. 0.1234 |

Important Items to Note

Details regarding DEATH, DIVORCELEGALSEPARATION, and MEDICARE dependent events:

• If the spouse was covered on all the same plans as the children, list the spouse as the QB on the file and list the children as dependents under the spouse. 

• If the spouse wasn’t covered on all the same plans as the children, list all the dependents each as a separate QB on the file with EE as the coverage level.

• If a spouse wasn’t covered on any plans and just children are listed as dependents, list each child as a separate QB on the file with EE as the coverage level.

If FSA hasn't been overspent, employees as well as dependents have the right to continue FSA coverage via COBRA.  This pertains to both employee and dependent events. 

• For employee events, the coverage level field on the QB plan line should list the true coverage level if possible and list a plan line for it under each dependent.

• For dependent events, if each dependent is sent with his/her own separate QB record, EE should be listed as the coverage level.  If the spouse is listed as the QB with the children listed as dependents, the coverage level should be listed as either EE+CHILDREN or EE+FAMILY depending on the client's account structure and a plan line should be listed for it under each dependent.

Sample Import File

Below demonstrates the layout of a sample QB file containing three individuals experiencing COBRA qualifying events.

[VERSION],1.2

[QB],TestClient,TestDivision,,John,,Doe,999-99-1111,,,6039999999,,967 Elm St,,Manchester,NH,03101,,T,,,,,,,M,07/07/1995,Unknown,Unknown,Unknown,,COUPONBOOK,False,True,,,,,,

[QBEVENT],TERMINATION,01/27/2017,01/01/2015,999-99-1111,John Doe,,

[QBPLANINITIAL],HP PPO Medical,EE+FAMILY,

[QBPLANINITIAL],Aetna Dental,EE+SPOUSE,

[QBPLANINITIAL],VSP Vision,EE+SPOUSE,

[QBPLANINITIAL],BSLLC Flexible Spending Account,EE

[QBPLANMEMBERSPECIFICRATEINITIAL],BSLLC Flexible Spending Account,45.14

[QBDEPENDENT],999-99-1234,CHILD,,Jill,,Doe,,,,T,,,,,,,,F,09/15/2015,F

[QBDEPENDENTPLANINITIAL],HP PPO Medical

[QBDEPENDENT],999-99-5678,SPOUSE,,Jane,,Doe,,,,T,,,,,,,,F,08/15/1996,F

[QBDEPENDENTPLANINITIAL],HP PPO Medical

[QBDEPENDENTPLANINITIAL],Aetna Dental

[QBDEPENDENTPLANINITIAL],VSP Vision

[QB],TestClient,TestDivision,,Sally,,Sample,999-99-2222,,,6038888888,,1 Test St,,Manchester,NH,03104,,T,,,,,,,F,04/01/1960,Unknown,Unknown,Unknown,,COUPONBOOK,False,True,,,,,,

[QBEVENT],DIVORCELEGALSEPARATION,01/24/2017,01/01/2010,999-99-4321,Bob Sample,,

[QBPLANINITIAL],HP PPO Medical,EE,

[QBPLANINITIAL],Aetna Dental,EE,

[QB],TestClient,TestDivision,,William,,Test,999-99-3333,,,6037777777,,99 Test Ln,,Manchester,NH,03104,,T,,,,,,,F,11/12/1984,Unknown,Unknown,Unknown,,COUPONBOOK,False,True,,,,,,

[QBEVENT],REDUCTIONINFORCE,02/15/2017,01/01/2013,999-99-3333,William Test,,

[QBPLANINITIAL],HP PPO Medical,EE,

[QBPLANINITIAL],Aetna Dental,EE,

[QBSUBSIDYSCHEDULE],MEDICAL,PERCENTAGE,3/1/2017,6/30/2017,100,,

[QBSUBSIDYSCHEDULE],DENTAL,PERCENTAGE,3/1/2017,6/30/2017,100,,

Example of format for client that has bundled plans:

[QB],TestClient,TestDivision,,Michael,,Doe,999-99-4444,,,6039999999,,967 Elm St,,Manchester,NH,03101,,T,,,,,,,M,01/01/1980,Unknown,Unknown,Unknown,,COUPONBOOK,False,True,,,,,,

[QBEVENT],TERMINATION,10/26/2017,01/01/2017,999-99-4444,Michael Doe,,

[QBPLAN],Delta Dental,11/01/2017,,EE,,,,,,,,,,FALSE,,

[QBPLAN],EyeMed Vision,11/01/2017,,EE,,,,,,,,,,FALSE,,

[QBPLAN],UHC Medical,11/01/2017,,EE,,,,,,,,,,FALSE,UHC Medical and Express Scripts RX

[QBPLAN],Express Scripts RX,11/01/2017,,EE,,,,,,,,,,FALSE,UHC Medical and Express Scripts RX

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download