Oracle® Fusion
Applications Workforce Deployment Implementation Guide 11g Release 6 (11.1.6) Part Number E20379-06 |
Home |
Contents |
Book List |
Contact Us |
Previous |
Next |
This chapter contains the following:
Defining an Extract: Worked Example
Extract Components: How They Work Together
This example demonstrates the steps required to create an extract definition. Before you create an extract definition, you should understand the following details: the information that you want to extract; the structure in which the data must be extracted and how you want to deliver this data (including file format, delivery mechanism and frequency information).
FAST bank is a global organization with subsidiaries all over the world. As part of an external business reporting requirement, FAST bank is required to extract the department and employee details (grouped by department) across the entire company. This information must be sent to a third party in an XML file and to HR Managers in a PDF file using e-mail.
The following table summarizes the key decisions in this scenario:
Decisions to Consider |
In This Example |
---|---|
How many extracts do I need to create to produce this type of report? |
You create one extract definition to define a headcount report. |
What type of extract do I create? |
You create a HR Archive extract. |
How many blocks do I need to create? |
In this example there are 2 functional groups of information, therefore you create two blocks, one for department and one for employees. |
How many records do I need to create? |
You decide the number of records based on the sub-group of data elements within a block. In this example, you create two records for the department block:
You create one record for the employees block: Employee Details. |
How many data elements do I need to create? |
You decide the number of data elements based on the specific information required for that report. In this example, create the following data elements for the Department Details record:
For the Department Summary record, create the following data elements:
For the Employees Details record, create the following data elements:
|
Do I need to create any fast formulas? |
You can use fast formulas at the following levels:
|
The session effective date is an effective start date that applies to all date-effective interactions in the current session.
Effective Date
Legislative Block
Parameter Group
Report Category
Request ID
Start Date
Field Name |
Entry |
---|---|
Name |
Departments |
User Entity |
PER_EXT_SEC_ORGANIZATION_UE |
Root Block |
Yes (By selecting this checkbox you select this block as the starting point for the extract execution.) |
Field Name |
Entry |
---|---|
Name |
Employees |
User Entity |
PER_EXT_SEC_ASSIGNMENT_UE |
Root Block |
No |
Field Name |
Entry |
---|---|
Parent Block |
Departments |
Parent Block Database Item |
PER_EXT_ORG_ORGANIZATION_ID |
Block Database Item |
PER_EXT_ASG_ORG_ID |
Field |
Department Summary |
Department Details |
---|---|---|
Name |
Department Summary |
Department Details |
Effective Start Date |
1/1/00 |
1/1/00 |
Sequence |
20 |
10 |
Type |
Trailer Record |
Header Record |
Process Type |
Fast Formula |
Fast Formula |
Next Block |
NA |
Employees |
Field Name |
Entry |
---|---|
Name |
Employee Details |
Effective Start Date |
1/1/00 |
Sequence |
10 |
Type |
Detail Record |
Process Type |
Fast Formula |
An extract data element is an individual field of a record.
Field Name |
Data Element Entry |
Data Element Entry |
---|---|---|
Name |
Department Name |
Department Location |
Type |
Database item group |
Database item group |
Database Item Group |
Organization Name |
Organization Location Country |
Field Name |
Data Element Entry |
Data Element Entry |
Data Element Entry |
---|---|---|---|
Name |
Record Code |
Report Date |
Employee Count |
Data Type |
Text |
Date |
Number |
Type |
String |
Parameter Element |
Summary Element |
String Value |
999 |
NA |
NA |
Parameter |
Effective Date |
NA |
NA |
Aggregate Function |
NA |
NA |
Count |
Aggregate Record Name |
NA |
NA |
Employees Employee Details |
Field Name |
Data Element Entry |
Data Element Entry |
Data Element Entry |
---|---|---|---|
Name |
Full Name |
Gender |
Date of Birth |
Start Date |
1/1/00 |
1/1/00 |
1/1/00 |
Data Type |
Text |
Text |
Date |
Type |
Database Item Group |
Decoded database item group |
Database item group |
Database Item Group |
Person Full Name |
Person Gender |
Person Date of Birth |
Field Name |
Data Element Entry |
Data Element Entry |
Data Element Entry |
---|---|---|---|
Name |
Salary |
Bonus |
Tax rate |
Start Date |
1/1/00 |
1/1/00 |
1/1/00 |
Data Type |
Number |
Number |
Text |
Type |
Database item group |
Record Calculation |
Rule |
Database Item Group |
Assignment Salary Amount |
NA |
NA |
Calculation Expression |
NA |
Salary * 0.5 |
NA |
Rule |
NA |
NA |
FAST Bank Tax Rule |
You can define delivery options for an extract using a BI publisher template, with the following delivery file output types: PDF, XLS, XML, DOC, and the following delivery modes FTP, email, fax. You can also choose Documents of Record as the delivery mode. This delivery mode enables you to store the output in the database and allows employees to view online payslips from documents of record. If the XML output is split and burst as separate files, then you can select the bursting node. For example, if you want all employees to be sent an email with their payslip, then set the bursting node to Employee_ID.
Field |
Value |
Value |
---|---|---|
Start Date |
1/1/00 |
1/1/00 |
End Date |
12/31/12 |
12/31/12 |
BI Publisher Template |
ReportLayout |
EFTLayout |
Output Type |
|
EFT |
Delivery Type |
|
FTP |
Delivery Option Name |
Email to HR |
FTP to 3rd Party |
Output Name |
HeadcountReport |
EFTReport |
An extract definition automatically creates an extract process with the same name as the extract. The extract process enables you to define an execution sequence of multiple tasks, including pre and post tasks. You can use the Refine HCM Extracts task to view and modify the extract process, if required.
The HCM Extracts feature is a flexible tool for generating data files and reports. You use the extract components to define what information you want the application to extract and report on, and how the information is displayed, formatted and delivered. An extract definition consists of: one or more extract blocks, depending on how many logical entities you want to extract; one or more extract records depending on how many groups of information you want to collect; and one or more data elements depending on how many individual fields of data you want to extract.
An extract definition refers to the complete setup of an extract, that consists of extract blocks, criteria, records, data elements, advance conditions and output delivery options. You use HCM extracts to extract, archive, transform, report, and deliver high volumes of HCM data from the Fusion HCM database. You can view the extracted data in the UI before generating the final output. You can generate the output in the following formats:
CSV
XML
Excel
HTML
RTF
You can distribute the extracted information by email, fax and other delivery modes. Some common examples of extracts are: PDF payslips delivered to employees' mailboxes, payroll or benefits data transferred to third-party service providers, HR and talent data exchange between Fusion and legacy applications, for example in a coexistence scenario.
Extract blocks represent a business area or logical entity, for example person, assignment, or benefits. The application uses this information to retrieve the report data items. You define one block as the primary or root block and this block is the starting point of the data extraction.
Extract block links capture the association details between the current block and the parent block. The block links form the hierarchical relationship among the blocks.
Extract block criteria enables you to define a set of filtering conditions the application must perform on an extract block. You specify the criteria conditions using an expression or fast formula.
Extract records represent a grouping of related data or a physical collection of all fields required in the extract. For example, the Employee block can have records such as Basic Details, Pay Details, Location Details, and Primary Contact An extract record is a collection of data elements which you can organize in a required sequence. For example, if a block has 3 records, then you can specify the sequence in which the application processes the records. You can also select the next block to identify which block the application processes next.
Data elements are the individual fields inside the extract record. A data element is the lowest attribute level of a HCM extract and represents a piece of information, for example, person first name, person last name or person date of birth.
This figure demonstrates the hierarchy of information within a block definition. In this example, the block is a container for the logical group of data called Department. Within the Department block there are two records of department data called Department Information and Department Other Information (extract records). Within the subgroup of Department Information there are two elements of information called Department ID and Department Name. Within the subgroup of Department Other Information there are two elements of information called Department Location and Department Start Date. An extract definition is a hierarchy of information with data elements such as Department ID at the bottom.
You can use extract block criteria to define a set of filtering conditions the application performs on an extract block. For example, you can use database items in the fast formula to represent the town_or_city and primary_flag columns in the per_addresses table to restrict the data to people living in London only. The extract definition would then exclude people with a primary address of anywhere other than London. You can specify the criteria conditions using an expression or fast formula.