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