39Benefits Data Extract

This chapter contains the following:

You can extract worker enrollment results, designated dependents and beneficiaries, and corresponding rates and coverages. You create an extract definition using the Manage Extract Definitions task in the Data Exchange work area.

Consider these aspects before you create an extract definition for benefits data:

  • Extract type

  • Data groups

  • Extract record sequence

Extract Type

When you create an extract definition, ensure that you select Benefits Carrier from the Extract Type list.

Data Groups

Create a PER_EXT_SEC_PERSON_UE (Person) data group and select it as the root data group. The following benefits data groups are available:

  • BEN_EXT_ENRT_RSLT_UE (Enrollment Results)

  • BEN_EXT_ENRT_DPNT_UE (Dependents)

  • BEN_EXT_ENRT_BNF_UE (Beneficiaries)

  • BEN_EXT_ENRT_RTCVG_UE (Rates and Coverages)

Extract Record Sequence

After you create the data groups, ensure that you sequence the data groups to identify which data group the application processes next:

  • Person

  • Dependents

  • Beneficiaries

  • Enrollments

  • Rates and coverages

This topic covers how to set up benefit plan carriers and offerings so that you can then generate and transmit enrollment data extracts. You extract benefits enrollment information into a single XML file for each benefits carrier.

To configure the benefits data extract, you complete these tasks in the Plan Configuration work area:

  1. Create the plan carrier and configure extract settings.

  2. Add the benefits extract plan type name.

  3. Add the benefits extract plan code.

  4. Add the benefits extract option name.

Creating Plan Carrier

Set up carrier data and configure extract options in the Plan Configuration work area that apply whenever you run the extract for that carrier.

  1. In the Tasks panel drawer, click Manage Plan Carriers to open the Manage Plan Carriers page. On the Mapping tab, you can view the mapping of lookup codes to the lookup value that you transmit to carriers. Edits to mapping values affect all plan carriers that use the lookup.

  2. On the Search Results toolbar, click the Create button.

  3. Enter the carrier information. In the Active field, select Active.

  4. Enter the extract options.

    The following table provides comments to help you with your extract field entries and selections.

    Field Comments

    Extract Type

    Select whether to run a full extract or extract only the changes since the previous extract.

    Generally, you run a full extract after an enrollment period closes and enrollments are completed. You run subsequent extracts on a periodic or scheduled basis, in either full or changes only mode.

    Output File Name

    Obtain the value that you enter here from the extract file recipient.

    Processing Frequency

    A common practice is to schedule your extracts to run after your regular payroll runs. You might want to set the processing frequency accordingly.

    Processing Type

    By default, all extracts have the same format, regardless of which carrier receives the extract. You can contract with Oracle's partner, BenefitsXML, to have a carrier's extract data transformed and delivered to the carrier, according to its specifications. For more information about BenefitsXML, see http://www.benefitsxml.com.

    If you don't use this partner, you can transform and deliver the extract data file directly to each of your plan carriers, according to their specifications.

    Upload Custom Layout

    This button is available if you select the Custom layout processing type.

    • Specify the layout for the individual carrier. For details, see the Benefits Extract: User-Defined Layout topic

  5. Enter the file transfer details, which you obtained from the extract file recipient. You can transmit extract files directly to the recipient. Alternatively, you can transmit extract files to the Oracle cloud, using its file transfer details, and have your recipient download its extract from there.

    Field Comments

    Host

    The name of the server to which you transmit the recipient's extract files.

    Remote Folder

    Path on the host to the location where your transmission places the extract file.

    User Name

    Part of the sign-in credentials required for your transmission to access the host.

    Password

    Part of the sign-in credentials required for your transmission to access the host.

  6. Click Save and Close to return to the Manage Plan Carriers page.

Adding Benefits Extract Plan Type Name

Complete these steps in the Plan Configuration work area to identify each type of plan included in the extract file for the recipient.

  1. In the Tasks panel drawer, click Manage Plan Types to open the Manage Plan Types page.

  2. Search for and click the plan type, for example, Medical.

  3. On the Plan Type Definition section Actions menu, select Update.

  4. Enter the valid benefits extract plan type name, for example, Health.

  5. Click Save and Close to return to the Manage Plan Types page.

Adding Benefits Extract Plan Code

Complete these steps in the Plan Configuration work area to link the plan to the extract file recipient.

  1. In the Tasks panel drawer, click Manage Benefits Plan Details to open the Manage Benefits Plan Details page.

  2. Search for and click the plan that you want to link to the extract file recipient.

  3. Click Next to open the Edit Plan Additional Configuration page.

  4. On the Configuration Details section Actions menu, select Update.

  5. Enter the benefits extract plan code, which the extract file recipient provided to you for this specific plan.

  6. Click Save and Close to return to the Plans tab.

Adding Benefits Extract Option Name

Complete these steps in the Plan Configuration work area to identify each option included in the extract file for the recipient.

  1. In the Tasks panel drawer, click Manage Benefit Options to open the Manage Benefit Options page.

  2. Search for and click the participant option, for example, Participant Only.

  3. On the Basic Details section Actions menu, select Update.

  4. Enter the valid benefits extract option name, for example, Employee Only.

  5. Click Save and Close to return to the Manage Benefit Options page.

Next Steps

After you complete the previous tasks, you're ready to generate and transmit the extract data. The details of this process are covered in the Generating and Transmitting Benefits Data Extract for Plan Carriers: Procedure topic.

Oracle partner BenefiX provides valid extract names that you can enter for benefits plan types and options. You can add values to this list, as required.

Benefit Extract Plan Type Names

Valid benefits extract names for plan types:

  • 24 Care

  • Dental

  • Dental Capitation

  • Exclusive Provider Organization

  • Health

  • Health Maintenance Organization

  • Hearing

  • Long Term Care

  • Long Term Disability

  • Mail Order Drug

  • Major Medical

  • Medicare Risk

  • Mental Health

  • Point of Service

  • Preferred Provider Organization

  • Prescription Drug

  • Preventative Care

  • Short Term Disability

  • Utilization Review

  • Vision

Benefits Extract Option Names

Valid benefits extract names for options

  • Children Only

  • Dependents Only

  • Employee and Children

  • Employee and Five or More Dependents

  • Employee and Four or More Dependents

  • Employee and One Dependent

  • Employee and One or More Dependents

  • Employee and Spouse

  • Employee and Three Dependents

  • Employee and Three or More Dependents

  • Employee and Two Dependents

  • Employee and Two or More Dependents

  • Employee Only

  • Family

  • Individual

  • Not Applicable

  • Spouse and Children

  • Spouse Only

  • Two Party

  • Employee and Domestic Partner

  • Domestic Partner and Children

  • Domestic Partner Only

  • Employee and Spouse or Domestic Partner

  • Child or Children of a Domestic Partner

You can extract benefits enrollment information into a single XML file for each benefits carrier and transmit it to the carrier. This topic covers how to generate and transmit the extract file.

Note: If a carrier provides more than one plan, the single extract contains information related to all plans provided by that carrier.

Example: Four different carriers provide ten plans. You run four separate extracts, one for each carrier.

To generate and view the extract, you complete these tasks in the Evaluation and Reporting work area.

  1. Submit the extract request.

  2. View and transmit the extract details.

Prerequisites

You must first configure your plan carriers, or extract recipients, and add the relevant extract data to the appropriate plan types, plans, and options. The details of this process are covered in the Configuring Benefits Data Extract for Plan Carriers: Procedure topic.

Submitting Extract Request

To run and monitor extracts, in the Evaluation and Reporting work area:

  1. In the Tasks panel drawer, click Extract Benefits Data to open the Extract Benefits Data page.

  2. On the Search Results toolbar, click Submit.

  3. Enter the extract request options.

    Field Comments

    Extract Type

    For a particular carrier, you can select whether to run a full extract or extract only the changes since you ran the previous extract.

    • Generally, you run a full extract after an enrollment period closes and enrollments are completed.

    • You run subsequent extracts on a periodic or scheduled basis, in either full or changes only mode. Common practice is to schedule your extracts to run after your regular payroll runs.

    Transmit

    You can transmit the extract as part of the extract request, or after the requested extract completes and before or after you view the extract details.

  4. Click Submit to submit your process and return to the Extract Benefits Data page.

Viewing and Transmitting Extract Details

You can view, query, and download extracted records for a specific extract run after it completes. You can also transmit the extract after the requested extract completes and before or after you view the extract details.

  1. In the Search Results section of the Extract Benefits Data page, click the Request ID for the most recent extract request for the plan carrier.

  2. Review, query by example, and download to a spreadsheet the extracted data, as appropriate.

  3. Click Done to return to the Extract Benefits Data page.

  4. In the Search Results section, click the Transmit icon button for the most recent extract request.

An implementor or developer can create a custom layout to transform the format of extracted benefits enrollment data to match the specifications of a particular carrier. This topic provides:

  • Descriptions of the tags that you require to create the custom layout

  • Table aliases and a sample custom layout

    The custom layout becomes the default layout for the plan carrier after you upload it to the plan carrier's extract options.

The following code shows the structure of the XML tags in the custom layout.

<Layout
 <Table
  Record Type
  <Field
   Name
   Source
   Width
   Padding
  Field>
 Table>
 <Table
  <Field
   Name
   Source
   Width
   Padding
  Field>
  -----------
  More fields
  -----------
 Table>
 <Table
  -----------
  More tables
  -----------
 Table>
Layout>

The data source for a field on the custom layout can be a:

  • Column on the benefits extract staging tables

  • Column on one of the other tables listed in the Source tag description

  • Constant into which you enter the exact value

Tip: To identify table column names, you can use the data model query builder in Oracle BI Publisher. Search for the table name and view the columns.

XML Tag Descriptions

This section describes each XML tag and lists its attributes, elements (subtags), and parent tags.

Layout

Description: Root tag.

Attributes: None

Elements (Subtags) Parent Tag

Table

None

Table

Description: Specifies the database table from which to extract the data.

Attributes: 1. tableName: Supported values = {BEN_EXTRACT_REQ_DETAILS,BEN_EXTRACT_REQUEST, DUAL,PER_ALL_PEOPLE_F,PER_PERSONS,PER_ALL_ASSIGNMENTS_M,PER_PEOPLE_LEGISLATIVE_F,BEN_PL_F,BEN_PL_TYP_F,BEN_OPT_F,BEN_PGM_F }

Elements (Subtags) Parent Tag

1. RecordType

2. Field: See Field tag description

Layout

Record Type

Description: Specifies how to delimit or lay out the data in the extract file.

Attributes: Supported values: FIXEDWIDTH, CSV

Note: Anything other than CSV is delimited as FIXEDWIDTH by default.
Elements (Subtags) Parent Tag

None

Table

Field

Description: Corresponds to one column in the extracted document. Source the text in this column from a database table, an SQL function, or a constant.

Attributes: None

Elements (Subtags) Parent Tag
  1. Name

  2. Source

  3. Width

  4. Padding

Table

Name

Description: Name of the field

Attributes: None

Elements (Subtags) Parent Tag

None

Field

Source

Description: Specifies the source of data for the current field.

  • If the source is a table, the value passed is the column name.

  • If multiple tables are involved, use a fully qualified column name.

The list of allowed tables includes the table aliases.

SQL functions in place of column names: Values in this tag are treated as column names if the type is set to TABLE. The column name is used directly while constructing a query, so an SQL function can be used on a column.

  • Example 1

    <Source type="TABLE">GENDER_FLAG</Source>

  • Example 2

    <Source type="TABLE">DECODE(GENDER_FLAG,'F',1,2)</Source>

Attributes:

  1. type:

    • Supported values = {TABLE, CONSTANT}

      • TABLE specifies that the data comes from a database table.

      • CONSTANT specifies that the data is given in the value column of this tag.

  2. table: Use this tag only if the intended column isn't from the table given in the tableName attribute of this Table tag. If this tag isn't used, the column is searched for in the table given in tableName.

    • Supported values:

      • {BEN_EXTRACT_REQ_DETAILS

      • PER_ALL_PEOPLE_F

      • PER_PERSONS

      • PER_ALL_ASSIGNMENTS_M

      • PER_PEOPLE_LEGISLATIVE_F

      • BEN_PL_F

      • BEN_PL_TYP_F

      • BEN_OPT_F

      • BEN_PGM_F

Elements (Subtags) Parent Tag

None

Field

Width

Description: Specifies the intended width of this field in the extract file. The number passed is the number of character spaces on the file.

Attributes: Supported values are positive integers.

Elements (Subtags) Parent Tag

None

Field

Padding

Description: Specifies the alignment of data in each column.

Attributes: Supported values: {LEFT, RIGHT}

Elements (Subtags) Parent Tag

None

Field

Table Aliases

Allowed Table Alias

BEN_EXTRACT_REQ_DETAILS

REQ

PER_ALL_PEOPLE_F

PEO

PER_PERSONS

PER

PER_ALL_ASSIGNMENTS_M

ASG

PER_PEOPLE_LEGISLATIVE_F

LEG

BEN_PL_F

PLN

BEN_PL_TYP_F

TYP

BEN_OPT_F

OPT

BEN_PGM_F

PGM

Sample XML Layout

<?xml version="1.0" encoding="utf-8"?>
<Layout> 
<Table tableName="DUAL"> 
  <RecordType>FIXEDWIDTH</RecordType> 
  <Field> 
    <Name>"Record Type"</Name> 
    <Source type="CONSTANT">001</Source> 
    <Width>3</Width> 
    <Padding>Left</Padding> 
  </Field> 
</Table> 
<Table tableName="BEN_EXTRACT_REQ_DETAILS"> 
  <RecordType>CSV</RecordType> 
  <Field> 
    <Name>"Last Name"</Name> 
    <Source type="TABLE">LAST_NAME</Source> 
    <Width>25</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"First Name"</Name> 
    <Source type="TABLE">FIRST_NAME</Source> 
    <Width>50</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Filler"</Name> 
    <Source type="CONSTANT">XXXXXXXXXX</Source> 
    <Width>10</Width> 
    <Padding>None</Padding> 
  </Field> 
  <Field> 
    <Name>"Plan Name"</Name> 
    <Source type="TABLE">PLAN</Source> 
    <Width>70</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Coverage Start Date"</Name> 
    <Source type="TABLE">COVERAGE_START_DATE</Source> 
    <Width>15</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"SSN"</Name> 
    <Source type="TABLE">NATIONAL_IDENTIFIER</Source> 
    <Width>12</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Gender"</Name> 
    <Source type="TABLE">DECODE(GENDER_FLAG,'F',1,2)</Source> 
    <Width>1</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Person Number"</Name> 
    <Source type="TABLE" table="PER_ALL_PEOPLE_F">PERSON_NUMBER</Source> 
    <Width>30</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Country of Birth"</Name> 
    <Source type="TABLE" table="PER_PERSONS">COUNTRY_OF_BIRTH</Source> 
    <Width>30</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Assignment type"</Name> 
    <Source type="TABLE" table="per_all_assignments_m">assignment_type</Source> 
    <Width>30</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Legislation code"</Name> 
    <Source type="TABLE" table="per_people_legislative_f">LEG.LEGISLATION_CODE</Source> 
    <Width>30</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Legislation code"</Name> 
    <Source type="TABLE" table="ben_pl_f">PLN.PL_ID</Source> 
    <Width>30</Width> 
    <Padding>Left</Padding> 
  </Field> 
</Table> 
<Table tableName="DUAL"> 
  <RecordType>FIXEDWIDTH</RecordType> 
  <Field> 
    <Name>"Record Type"</Name> 
    <Source type="CONSTANT">999</Source> 
    <Width>3</Width> 
    <Padding>Left</Padding> 
  </Field> 
  <Field> 
    <Name>"Record Type"</Name> 
    <Source type="SYSTEM">RECORDCOUNT</Source> 
    <Width>3</Width> 
    <Padding>Left</Padding> 
  </Field> 
</Table>
</Layout>