System Extracts

System Extracts

System extracts is one of the most innovative and powerful tool that can be used across Payroll and Benefits space to create and manage interfaces. As we know, interfaces are the files we generate to communicate with the business partners. We have our firm, and we are maintaining our Payroll and Benefits system. Now, we must let our Carriers know, the enrolments that our Participants have. Again, we must update them about the changes happening to the existing Enrolments, the drops and the new enrolments too. How do we do that? We and the Carrier sit together, and decide on a standard layout with which we are going to put in the details and select the data that we want the file to contain. Then we decide a frequency at which the data is going to be sent. We also decide the type of the data, should it have all the enrolments / just the drops and new enrolments. That file is called an Interface. To design an interface, we can code it all with PL/SQL, fetch all the data, and sort it in the required format and write it on to a file. Oracle has taken the PL\SQL in the background and given a handle to the users to use System Extracts. With Extracts we can build and maintain our own interfaces, and all it takes is just few hours. It's easy and reliable.

The Extract works with three different mainstays:

  • Extract Definition: This is where we define the Extract and attach the criterion and layout with it.
  • Extract Criterion: This is to define the population that needs to be pulled.
  • Extract Layout: This is to define what to pull, like the data to be printed on the interface file.

Let's take a bottom up approach, and first learn the layout.

Extract Layout

Extract Layout is the place where we define the data to be pulled on to the interface file. Now, if we take an example, the vendor/ carrier might ask for Personal details (Name, Address, Social Security Number/ NI Number, Date of Birth etc), Enrolment Details (Plan Type name, Plan Name, Program Name, Option tier name, Benefit Amount, Premium Amount etc) and lot many other Details like Dependents, Premiums as well. It is usually requested in a Record Format, where each record is based on one particular level. We will discuss more about it.


First of all, the Extract layout is always divided into three distinct sections:

  • File Layout
  • Record Layout
  • Data Element


Responsibility: HRMS Manager

Navigation: Mass Information eXchange: MIX -> System Extract -> Layout Definition


Data Element

These are the smallest unit in the Extract. Each Data Element holds two things, one data value and one level. The Data value is nothing but the data that we would like to get on the extract file. Examples would be the Person Full Name, Enrolment Plan Name etc. Now in the two examples we have here, one is for the name of the Person, and another is for the enrolled plan, right? Imagine, we are pulling the record for Joe; and we are reporting Joe's enrolments in 3 Plan types, namely Medical, Dental and Vision. If Joe's enrolment plan name is to be pulled, the system will fetch the data thrice for three different Plan Types right? So it will be executed once for each enrolment; let's call that level as Enrolment Level. We can say Enrolment Plan record is at Enrolment level. If we see the Person Full Name now, the data is needed to be pulled once for each person. So the Level is Person Level.

(Figure 6.44 – Data Elements)

So those are two distinct levels, Enrolment Level and Person Level. If we try to pull the Person Full Name at enrolment level, the Person full name is going to be pulled once for each enrolment that the Extract is pulling. Similarly if we pull the enrolment in the Person level, the system might get confused and not pull any data. So every Data value has a one or more possible levels at which the data should be fetched. Figure 6.44 – Data Elements. What are the different data Values available? There are many possibilities; the different types of data field types are:

  1. Field: These are the seeded Data Values given by Oracle. There are slightly more than one thousand seeded data values present in the set. Each one of them comes with a repeating level. We can simply pick one of these and use it directly with or without any changes to it. The Possible changes with fields are: We can set a Max Length of the field, so that if System returns a bigger value than the desired one, it trims it off to the desired length. The other allowed change is we can set a default value. If the filed returns a Null, then the default value will be used in that place. Both the modifications are optional.
  2. Decoded Field: This is slightly advanced version of the field type. With this type, we can choose the data field that we want to use, again on top of it, we can use decode for the same. For an example, if we wish to Print only the first letters of the Plan Type Names, we can just use a Field Value for the Plan Type name, and then use the decode functionality. The Decode section takes two values: "If Value is" and "Replace with". This makes it very simple to understand and use. The Decoded Field does not allow a Max Length modifier; however we can use a Default value in the "Other Values" field.
  3. String: This is very simple. This one just takes a String Value to be printed. This is used in cases where we want something to be hard coded. We can use a Max length modifier here. All the Strings run with a Null Level / No Level.
  4. Payroll Balance: With this we can report the Payroll Defined Balances. We can modify the results using a Format Mask, a Justification Mask (Left/Right) and a Default Value with "When Null Use"
  5. Record Calculation: This is the most complex type of Data Field and the least used one. This one is to do with the calculations related to other Data Elements. We can select the list of data elements and the mathematical function that we want to perform on the Data Elements. We can also use a Format Mask and a Justification Mask. This runs with No Level.
  6. Total: This is similar to the Record Calculation; however this just counts/ Sums the record. Unlike Record Calculations it’s not capable of other mathematical functions. This runs with No Level.
  7. Rule: This one is simple. If we need a specific business logic that does not get satisfied with any of the types then we can just use a Rule. While defining a Data Element rule in the FF screen, we have to choose a level, and the level stays with the Rule when attached. We can use modifiers like, Format Mask, Justification Mask, Default Value and Max Length. We can also use a Rule Parameter that can be called from the FF itself. 

Now, we know the types; let's look at the different Repeating levels that are available in data elements:

The repeating levels tell the system about the frequency in which the data is going to be fetched. For example, a data value with Eligibility level will be fetched once for each eligible comp object. All the repeating levels are self explanatory by their names. The levels have a priority hierarchy in which they can be sorted. Like, Person level has a lower precedence than Enrolment Level. So a Person Full Name can be pulled on Enrolment level (although it will pull the same data repeatability for each enrolment); however the enrolment Plan name cannot be pulled on the Person level. Here is the list of available repeating levels sorted from lowest priority to the highest priority.

  1. No Level
  2. Person
  3. Beneficiary
  4. Contact
  5. Eligible Dependent
  6. Covered Dependent
  7. Eligibility
  8. Enrolment
  9. Action Item
  10. Flex Credit
  11. Run Results
  12. Element
  13. Premium


Record Layout

This is the next higher level of the design. A record is a collection of the Data elements. The repeating level of the record will be the dominated by the data element with the highest repeating level. So if there is a record at person level that means all the data elements being used in the record layout are either at Person Level or at a level with lower precedence than the Person Level. Now, if we go and add a data element with Enrolment level to the same Extract Record, the repeating level of the record will change from Person Level to the Enrolment Level.

We know that a Record is a collection of Data element with a designated Repeating level. There are five types of Records though.

  • Header: The Header Record. Usually prints the information of the Extract Type, the recipient etc.
  • Sub-Header: In a usual Extract, this comes just after the header. This stores the details about the particular run of the extract. Data like Date of Processing, Type of Extract: Test / Production etc.
  • Trailer: This is the record at the end of the Extract. This again contains data about the carrier and the extract, usually the metadata.
  • Sub-Trailer: This is either placed at the end just before the Trailer Record, or at a count of a particular set of records. Like after every 10,000 records there will be a Sub Trailer.
  • Detail: This is the usual record that contains the actual data. All other levels usually run with no Levels and this one might run with any possible level. Usually an extract holds more than one detail records.

Let's see how to configure a Record.


Name of the Record Layout


Choose a type of the record

Repeating Level

This is auto populated based on the elements we choose.


This is just another identifier for the XML based user interfaces/ reports. Can leave blank.


The Sequence of the data element, This depicts the order in which the Data elements will be evaluated by the system.

Data Element Name

Name of the Data Element to be attached in the Record.

Start Position

We can use a position at which the data element is going to start on the file. The End Position is the start position of the next data element - 1 position.


If we wish to make it a delimited file, we can choose a delimiter here. The resultant will be separated value file like CSV (Comma Separated Values). Usually we use either a positioned file / Delimited file. However Oracle does not stop us to use both the values.


This flag marks the data element as Required. If the data element pulls NULL, the record is considered void, and does not get printed. So Data Elements with Required Flag on, always have a default value.


If this flag is checked, the Data element does not get printed on the file. Usually these kinds of data elements are placed to help other data elements in the record.

Data Element

This opens the Data element definition for the Data element in the selected sequence.

Advanced Conditions

We will be learning about the Advanced Conditions later in this chapter.


File Layout

Now, as we have already defined the Record Layout, it’s time to club the record in a format and get the file design ready. The file layout is a combination of records. Usually we put a Header, a Trailer and one or more Detail Records in between to populate the File Layout. See Figure 6.45 – Extract Layout.

(Figure 6.45 – Extract Layout)


Name of the File Layout


This is just another identifier for the XML based user interfaces/ reports. Can leave blank.


The Sequence of the Record Layout, This depicts the order in which the Records will be evaluated by the system.

Record Name

Name of the Record to be attached in the File Layout.

Prevent Duplicates

If this flag is checked, it makes sure there are no two records printed with exactly the same data in them.


This flag marks the Record as Required. If the record does not pull any rows, the record is considered void, and does not get printed.


If this flag is checked, the record does not get printed on the file. 

Merge Changes

If this flag is checked, it merges the changes of two records if most of the data in both the rows are same.

Record Layout

This opens the Record definition for the Record in the selected sequence.


We can sort the records based on the data elements used in the record layout.

Advanced Conditions

We will be learning about the Advanced Conditions later in this chapter.


In case we wish to print the records in a set of groups, we can use an element to group the records together on the element value. It is as good as the Group By clause in SQL.


Advanced Conditions

We have seen options to attach advanced conditions at the File Layout and the Record Layout. Let's see the functionality of Advanced Conditions. These are the conditions that can be attached on any Data element / Record; and an action can be attached to the conditions. The logic is simple; if the related condition is a pass the attached action gets fired. Let's see how it's configured.


The Sequence of the Condition

Data Element Name

Name of the Data Element in the current record on which the condition will be established.


The condition is based on the set of operators. These are a set of Arithmetic and Logical operators.


This is one of the operands of the condition. The Data Element is compared to this value with the Operator.

And / OR

This is a conjunction between two conditions.


An appropriate action is chosen from the available list. The Action can be of two types:

  • If Condition is False
  • If Condition is True

Based on that, the different possible actions are 

  • Exclude the Person and Signal Warning
  • Exclude the Person
  • Exclude the Record
  • Signal Warning


There are two other tabs as well. 

  • Record inclusion based on change event: We can include a change event here. The Record will be pulled only if it’s associated with the same change event.
  • Record inclusion based on payroll event: It is similar to the change event record; however this is related to the Payroll Events.

This completes the configuration of the Extract Layout Design. It’s time to define some criteria.


Extract Criteria

The criterion is the place where we define the population, we want to include in the Extract file. The screen is similar to the eligibility Profiles that includes a lot of tabs where we can define the conditions. If the conditions pass the Person/ enrolment is considered for the extract, based on the repeating level. There are a lot of conditions; however we will go through the important ones. See Figure 6.46 – Extract Criteria.

Responsibility: HRMS Manager

Navigation: Mass Information eXchange: MIX -> System Extract -> Criteria Definition

(Figure 6.46 – Extract Criteria)

The most frequently used criteria in People tab are:

  • Person Type Usage
  • Person Benefit Group
  • Person Full Name
  • Person Assignment Set
  • Person Started Life Event Name
  • Rule

The Full Name and the Assignment set is used only in cases of testing; while we want to run the Extract only on a set of people.

Now, in the Benefits tab, the important criteria are:

  • Enrolment Plan Name
  • Enrolment Status
  • Enrolment Coverage during Period
  • Enrolment Suspended Flag
  • Enrolment Life Event Status
  • Eligible Data Link
  • Eligible Life Event Status


Let's go to Communications Tab now. This tab is mostly used for the Communication Type Extracts. These are one of the most widely used extract types. Once the communication is triggered, we can then create an extract to pull the data from the backend. Then the Extract can be the input for any system that can use the data and create letters to be sent. Let's see the most frequently used criteria in Communications space

  • Person Communication - Type: To list the communications to be included as part of this extract.
  • Person Communication Provided - To Be Sent Date: To pull the records for communications to be sent in a particular time frame.
  • Person Communication provided - Sent Date: To not pull the records for which the kit has already been sent.

There are a lot of other tabs in there, which can be used to restrict the criteria with many conditions.

Extract Definition

This is the definition of the Extract. All the important information about the Extract is populated here, which are going to be used in by the system while processing the Extract. Let's look at the ingredients that go into an Extract definition.

Responsibility: HRMS Manager

Navigation: Mass Information eXchange: MIX -> System Extract -> Extract Definition

(Figure 6.47 – Extract Definition)

Extract Name

The name of the Extract


Type of the Extract. It could be one of the following: Full Profile / Changes Only / Communication/ CWB

XML Tags

This is just another identifier for the XML based user interfaces/ reports. Can leave blank.

Inclusion Criteria

Choose the criteria that we want to associate with the Extract.

File layout

Choose the Layout that we want to link with the Extract

Output Directory

The directory path, where the generated output file will be stored.

Output File name

The name of the output file.

Output Type

The type of Output file. We can choose file extensions here. If left blank, it takes it as no extension.

Display Output File in Write Process

This flag enables us to see the output file once the Extract Write Process (The Concurrent Program that writes the output file) is complete.

Append Concurrent request Id to file name

This flag tells the system, whether to add the concurrent request id to the file name or not. This is used to avoid generating files with the same name, which causes overwriting on the disk.

Submit Write Process automatically

This flag tells the system, whether to kick off the write process automatically or to consider another request from the user to write the Extract on file.

Special Handling for Dependents

By checking this, the system maps dependents with their own person id, and uses the same for extraction. Mostly used in ANSI, as ANSI runs the dependent separately.

Update Communication Sent Date

This is used for Communication Extracts. This tells system to populate the sent date once the Person's communication record is extracted.

Post Processing Rule

This rule is fired once the extraction is done, and just before the write process is to be executed. Users can put logic in these rules to manipulate the output.

Copy Extract

The copy Extract Functionality enables the users to create a copy of the definition, criteria and layout of the extract in seconds with a new name suffix given there. Later we can change the name of the extract and modulate it based on our requirements.


Processing an Extract

Extracts are processed through a concurrent program "Extract Process". The process flow is:

  • Get to the Extract Definition and get the Criterion and the Layout
  • Process the Criterion to select the Persons/ enrolment / eligibility (Based on the Repeating Level)
  • Run the Extract Layout through the repeating levels.
  • Process the Data Element First, then the Advanced Conditions
  • Similarly, Get the record pulled, apply advanced conditions and sort the Records.
  • Now, go to the Extract Definition and apply the Post Processing Rules.

The next step is to call the "Extract Write Process". This is either called by the system or request separately by the user, based on the flag in the Extract Definition. The Extract Write Process then writes the data in the mentioned file in the mentioned path.