Example of a Custom Layout for Benefits Extract

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

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
Name Source Width 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:

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.
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>