System Extracts

System extract 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 / system 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:

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

No Level
Person
Beneficiary
Contact
Eligible Dependent
Covered Dependent
Eligibility
Enrolment
Action Item
Flex Credit
Run Results
Element
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.

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)

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.

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)

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.