Many human resource departments outsource at least some of their compensation and benefits administration processing to third-party carriers, payroll providers, or other vendors. Thus, there is a great need for reliable data exchange between plan sponsors and these third parties.
You can use System Extract to extract records for a variety of plans, including compensation and benefits plans and absence plans. You can also use or modify predefined HR/Payroll extracts to gather employee and payroll data, such as the details of earnings, deductions, and information elements. You can use the extract reports for internal reporting, such as listing all employees in receipt of a bonus, or for transfer to third-party providers, such as pension providers. See: HR/Payroll Extracts
Note: System Extract does not actually remove any data from the source.
To extract records from the database for transmission to a third party, you must define the criteria that determine which records are extracted. You can restrict extract data according to a wide range of criteria such as organization, plan, and data that has changed since the previous extract. You must also define the layout of the extracted records, including the data elements and date ranges.
After you create your extract definition, you run the system extract concurrent process to create your extract results. You can then review these results, including any errors that occurred during the process. When you are satisfied with the results of the extract, you can transfer the extract file to the medium by which you transmit or store the extract results.
The system extract process consists of four main steps, outlined below:
You create an extract layout definition to control the data elements that are included in the extract and the format of the extract results.
An extract layout consists of data elements and their formatting, record layouts that define which data elements appear in the header, subheader, detail, trailer, and sub-trailer areas of the extract, and a file layout that specifies the order of the records.
Your extract layout also indicates the sort order of the records and any conditional record inclusion criteria that you define.
For example, you could create an extract layout definition for the enrollment results of the medical plans offered by your organization. You define data elements such as the participant's first name, middle name, last name, and the plan they elected. Then, in your record layout, you arrange these data elements within five areas: detail, header, sub-header, trailer, and sub-trailer. Finally, you create a file layout by sequencing these record layouts.
Data elements are individual data components which contain values that you include in an extract. Different data element types can contain different values. You use your data element definitions in conjunction with your extract criteria definitions to restrict the data that is included in the extract. The data element types are as follows:
Fields are most often data extracted from a database field, but can also include common header and trailer totals, subheader elements for non-person data, dates, and other filler information. You cannot update the list of fields delivered with the system.
Decoded Fields display a different value in the extract than is stored in the database. For example, if you store the values for Male and Female as M and F, you can set the values as Male and Female.
Record Calculations calculate the total of two or more data elements in a record. You can add, subtract, multiply, or divide extracted data.
Strings are groups of characters such as words or sentences.
Rules enable you to create data elements not included in the supplied list of data elements.
Totals appear in the header or trailer of a record. You can sum the results of any data element or include a count of the number of records. You can add conditions to display the sum of data elements only where the value is equal to a specified value.
You can format different data element types in different ways. Depending on the data element type, you can specify justification, format mask, maximum length, and other formats.
If the seeded format masks are insufficient, you can add format masks to the system's number and date lookups. You use a lookup code that begins with 'N' for numeric format masks or 'D' for date format masks. You cannot define your own format mask for phone numbers or social security numbers.
Note: A system administrator familiar with Oracle formats should enter this information.
You can sort the data elements in a record by up to four levels based on any data element you include in the extract. Sorting can occur on each record in a file layout.
You specify the data elements to be included in the header, subheader, detail, trailer, and sub-trailer portions of your record layout. Typically, the header and trailer portions of a record contain control information, such as file identifying information, dates, and totals. Detail records contain the content and values of an extract. Subheader elements contain non-person data, such as organizations and positions, that you use to order person information contained in the detail portion of the layout.
You can specify Required data elements. If a required data element is missing, the entire record is written to the error log during the extract process and no extract result is created for that record.
You can also choose to hide a data element from an extract record when you run the write process. Hidden data elements appear in the extract result after you run the extract process, but are hidden when you initiate the extract write process. Hiding enables you to use a sensitive field such as salary to determine included or excluded records without exposing the field value to unauthorized personnel.
You define a file layout to create the sequence (from right to left) in which the records display in an extract. Sequence numbers are unique.
The extract criteria you select determine the data that is extracted by filtering the data. Data that does not meet your extract criteria is not extracted. You can select from several groupings of criteria that you combine to create an extract criteria profile.
People limits your extract results to an individual or to people who meet the criteria you select, such as assignment location, organization, or benefits group.
Benefits limits your extract results to a particular plan or reporting group, including dependents.
Changes limits the extract to data that has changed according to a number of criteria types such as element and input values.
Payroll limits the extract to specified types of payroll and element entries.
Communications limits the extract to specific communication types and dates.
Premium enables you to extract premiums for a single month or for a range of dates, or restrict a premium extract to the last occurrence of a record update.
Compensation limits the data extract to specific plans that you have defined in Compensation Workbench.
Sub Header limits the data extract based on non-person data, such as organization, position, or payroll, that you include in the sub header region of the extract file layout.
Advanced criteria creates an extract for a change event when you have complex criteria for selecting records; you can select records for a change event over one or more periods of time, and by the actual date and/or effective date of change.
The extract definition combines a layout (what you have chosen to include) and a criteria profile (what you have chosen to restrict), enabling you to reuse layouts and profiles in different combinations. Here you define parameters for the system extract concurrent process that you run to create the extract result.
You can output the extract data as a text file or an XML file.
See: XML-Enabled System Extracts
You extract the data by running a pair of concurrent processes, the Extract Process and the Extract Write Process. While you are testing your extract, you run the processes separately, but after testing you will typically run them together in a request set. You can run various reports to view the results.
You run the Extract Process from the Concurrent Manager to create results for an extract definition. After you run the extract process, you can view the results in the Extract Results window. The extract process generates a summary report and an error report. You can run the Extract Audit Report to view a limited selection of records from the extract results.
You run the Extract Layout Report to extract just your layout definition with no data. This is useful for reviewing the format of the layout. Benefit carriers may also find this information useful in helping them import the extract result into their systems.
Use the Extract Results Data Purge process to remove unwanted data from the extract results tables. Purging unneeded data helps save table and disk space.
See: Purging System Extract Results
The Extract Process automatically runs the Extract Summary report (you can also run the report separately for any existing extract). Run the Extract Error Report to view any errors that occurred during the extract process.
The Extract Results window displays detailed information about an extract. If your security profile prevents access to certain person information, the window hides those person records. However, summary totals display based on the entire population, regardless of your security profile.
You can view (but neither change nor delete) the change events for a person from the Change Event Log, shared with Oracle Payroll.
To maintain application performance, you should periodically purge unwanted data from the extract change log tables by running the Extract Change Event Log Purge process.
See: Purging the System Extract Change Log
Once you are satisfied with the results of your extract, you run the Extract Write Process from the Concurrent Manager to save the output of the extract to the directory and file you specified in your extract definition.
See: Running the System Extract Concurrent Processes
You can migrate a system extract to another business group or database instance. To save time during implementation, build a system extract once, then copy the data elements, record and file layouts, and extract criteria that comprise the extract definition.
Note: To copy an extract within a business group, use the Extract Definition window. To modify a seeded extract, you must copy it and use the copy.