7 Data Extracts

This chapter describes the facilities for extracting the full contents of certain records to Comma-Separated Value (CSV) format files. The files can be viewed as spreadsheets or processed for further analysis by an external system.

The records that can be exported to CSV are Supplier and Site. Users with the Advanced Reporting Administrator authority profile have access to the export options.

The extract options are available from the list views of the supported record types. The Advanced Search facility can be used to filter the list view to the desired set of records to extract. The extract is run as a batch job, with an email notification being sent on completion. The generated CSV files are stored in the Report Outputs library.

In order to avoid the generation of excessively large CSV files, a size limit is imposed, based on the setting of the portal's File Attachment Size Limit system parameter.

A Process & Activity Brief Extract data extract type is also available for extracting Brief questions and answers for Processes and Activities. The extract is generated as an XLSX Excel spreadsheet and is stored in the Report Outputs library.

Extracting Suppliers

To extract Suppliers:

1. Open a list view of Suppliers.

2. Use the Advanced Search facility to filter the contents to the suppliers you wish to extract.

3. Select the Extract Data action. The Confirm Data Extract dialog box appears.

   Figure 7-1 Confirm Data Extract Dialog Box

   

4. Click the icon to select a language for which locale-dependent fields are to appear in the extract file. The default is your locale/language. This is a mandatory field.

5. Click the icon to select a report output folder where the generated data file is to be saved.

6. To continue with the extract, click Ok. To return to the list view, click Cancel.

   A submission confirmation message box is shown. Click Ok to return to the Suppliers list view.

   A Supplier-Site Data Extract batch job is submitted to create the extract file. On completion, you will receive an email notification with a link to the file in the Data Extracts area of the Report Outputs (see Viewing Extract Files).

   Note:

   In the event of the maximum file size being exceeded, the extract file will truncated at the end of the last complete record and a final row output, reading: FILE TRUNCATED - SIZE LIMIT EXCEEDED. In this case, the file name will be prefixed: * INCOMPLETE.

Suppliers Extract File Contents

The same extract file is used for both supplier and site data extracts. The CSV file name is SupplierExtract, with the batch job number appended.

The file contains a row for each of the supplier's sites, but for the supplier extract, the only site data included is the site name (both local and business languages), site code, and site status.

The first row will contain the unique system text for the field; the second row will contain the system text specifically for reporting (both shown in the selected language). The first column will contain an incrementing sequence number, starting from 1 (this is of use to reset the file to the original sequence after making changes in Excel). The second column will contain an automatically assigned unique compound key based on the record type and field.

All fields on the Supplier record will be output, including any custom fields, but excluding change history data. The contents of any embedded list views (such as the list of Contacts) are not output.

The individual rows of tables are output as separate rows (the parent data is not repeated). For example, a supplier with two sites, each with two entries in their References table would output as:

1	Supplier A details	Supplier A, Site 1 details	Site A-1	Reference 1
2				Reference 2
3		Supplier A, Site 2 details	Site A-2	Reference 1
4			Site A-2	Reference 2
5	Supplier B details	Supplier B, Site 1 details	Site B-1	Reference 1
6				Reference 2
7		Supplier B, Site 2 details	Site B-2	Reference 1
8			Site B-2	Reference 2

Columns that relate to a glossary (such as Business Category) contain the Description (not the Code) of the glossary record. Contact information is included, but not contact roles.

Extracting Sites

To extract Sites:

1. Open a list view of Sites.

2. Use the Advanced Search facility to filter the contents to the sites you wish to extract.

3. Select the Extract Data action. The Confirm Data Extract dialog box appears.

4. Click the icon to select a language for which locale-dependent fields are to appear in the extract file. The default is your locale/language. This is a mandatory field.

5. Click the icon to select a report output folder where the generated data file is to be saved.

6. To continue with the extract, click Ok. To return to the list view, click Cancel.

   A submission confirmation message box is shown. Click Ok to return to the Sites list view.

   A Supplier-Site Data Extract batch job is submitted to create the extract file. On completion, you will receive an email notification with a link to the file in the Data Extracts area of the Report Outputs (see Viewing Extract Files).

   Note:

   In the event of the maximum file size being exceeded, the extract file will truncated at the end of the last complete record and a final row output, reading: FILE TRUNCATED - SIZE LIMIT EXCEEDED. In this case, the file name will be prefixed: * INCOMPLETE.

Site Extract File Contents

The same extract file is used for both supplier and site data extracts (see Extracting Suppliers). The CSV file name is SiteExtract, with the batch job number appended.

The file contains a row for each site, with the parent supplier name (both local and business languages) and supplier code on the first site row.

All fields on the Site record will be output, including any custom fields, but excluding change history data. The contents of any embedded list views (such as the list of Contacts) are not output. The Growers List table is excluded.

Note:

If a full combination of supplier and site data is required, two separate Supplier and Site extract files must be created and manually merged using the common Supplier Code as the key.

Viewing Extract Files

The generated data files are stored as Report Output records in a similar way as report and KPI outputs. See Viewing Report Outputs.

To access the Data Extract files, select the Report Outputs option in the Reports navigation menu.

The extract files are located in the Data Extracts area, as a library of folders. The folder is specified when the extract is submitted.

Locate an extract file by navigating through the folder hierarchy, or using the search box. The columns can be sorted in ascending or descending sequence. The Report Name column shows the name of the extract file.

To see the details for an extract, double-click the row. A tab opens with the details. You can also open the actual file clicking the icon in the Open column, then choosing to open or save the file.

Report Output Details Tab

Figure 7-2 Data Extract Report Output Details Page

Table 7-1 Data Extract Report Output Details Page

Details	Description
Report Name	The name of the generated data extract CSV file.
Report Type	This will show as Data Extract.
Ran On	The date and time the extract ran.
Duration	The length of time it took to generate the file.
Results	The number of records in the file.
Scheduled By	The name of the user who scheduled the extract.
Output	A list of the actual extract files, held as attachments, with columns showing an icon of the file format, the file name, the report description, the size of the file in bytes, the name of the user who scheduled the extract, and the date it was created.

Click the icon in the Download column to view the extract, then choose to open or save the file.

Process & Activity Brief Extracts

A data extract of Process and Activity Brief data can be scheduled as a report, utilizing pre-configured criteria for filtering Processes, Activities, and their Brief questions.

The Process & Activity Brief Extracts glossary is used to configure reusable data extract criteria. When scheduling the report, setting the report type as a Process & Activity Brief Extract allows for the extract criteria to be selected.

The extract is created as a spreadsheet, containing columns that represent data from the Process and Activity records, and their Brief questions and answers, as defined by the extract criteria. The generated extract is output to a Process & Activity Brief Extracts folder in the Report Outputs library. The user is emailed a link to the report output on completion.

Note:

A less comprehensive version of the Brief Extract is available for selection from the Processes and Activities list views. The output is also placed in the Process & Activity Brief Extracts folder in the Report Outputs library, and a link to the report output is emailed to the user on completion. Section Name is not visible in the Brief Extract confirmation step as it is not available to select as a list view field, however it is included in the generated output.

Maintaining the Extract Criteria

An Extract Criteria glossary is used to maintain a library of reusable extract criteria for applying filters to the Brief Extract reports, by optionally selecting Processes, Activities, and questions from the associated Process and Activity Briefs and current Brief template glossary records.

To access the Extract Criteria, select the Manage Process & Activity Brief Extracts option in the Reporting Admin area. This is only accessible to users with the Advanced Reporting Administrator authority profile.

Figure 7-3 Extract Criteria Page

To see the details for a specific extract criteria, double-click the row. A tab opens with the details. You can also select the View or Edit actions.

Note:

Extract Criteria glossary records cannot be imported and exported using the Import and Export list view actions.

Creating an Extract Criteria

To create a new extract criteria:

1. Select the New action. The New Extract Criteria page appears.

   Figure 7-4 New Extract Criteria Page

   

2. Enter the details.

   Table 7-2 New Extract Criteria Page

   Details	Description
   Code	Enter a unique uppercase code for this extract criteria. Mandatory field.
   Version	The version will default to 1. Read-only field.
   Status	This will default to Draft. Read-only field.
   Description	Enter the description for this extract criteria in the supported languages. The description for the default language is mandatory.

3. Optionally add any filters to be applied to the extract criteria. See Filters.

4. Save the changes and exit.

Editing an Extract Criteria

To edit an extract criteria, select the extract criteria on the Extract Criteria page and then select the Edit action. The extract criteria record opens in edit mode in a new tab.

Extract Criteria are maintained as a versioned glossary. When the extract criteria is created, it is assigned version 1 and the status will be Draft. At this status, the code, version, and status may not be edited.

Figure 7-5 Extract Criteria Details

Activating the Extract Criteria

Use the Activate action to activate the extract criteria. The status is changed to Active, making the extract criteria available for use in report schedules. The Create New Version action can be used to create a new version at Draft status, with the version automatically incremented by 1.

Deactivating the Extract Criteria

Use the Deactivate action to deactivate the extract criteria. The status is changed to Deactivated, making it unavailable for use in report schedules. Any existing active report schedules that use the extract criteria will continue to do so. At this status, no fields may be edited (see the note below), but the Create New Version action can be used to create a new version at Draft status, with the version automatically incremented by 1.

Note:

Users with the Oracle Authorized Administrator authority profile can edit the record at any status, however the code, version, status, and system delivered flag remain locked.

Deleting the Extract Criteria

Use the Delete action to delete the extract criteria. The extract criteria may only be deleted if it is not active and not in use by any report schedules.

Filters

Use the Filters page to define the extract criteria to be applied as the filter when generating the Brief Extract report.

Figure 7-6 Extract Criteria Details - Filters

To add a filter to an extract criteria:

1. Open the extract criteria in edit mode and navigate to the Filters page.

2. Add a row to the Questions tables and click Add.

3. Enter the details.

   Table 7-3 Filters

   Details	Description
   Process Filters	Optionally choose any specific Processes to be included in the extract by selecting any combination of Process Titles, Process Status, Process Templates, Process Types, Business Categories, and Process Managers. If none are selected, all will be included (subject to other filters).
   Activity Filters	Optionally choose any specific Activities to be included in the extract by selecting any combination of Activity Templates and Responsible Roles. If none are selected, all will be included (subject to other filters).
   Process Extract Questions	When adding a row to the Process Extract Questions table, a list of Process Briefs is presented for the selection of their questions.
   Activity Extract Questions	When adding a row to the Activity Extract Questions table, a list of Activity Briefs is presented for the selection of their questions.

4. Save the changes and exit. If no Process or Activity questions have been selected, validation will show a warning message.

Scheduling the Report

To schedule a Process & Activity Brief Extract report, follow Creating a Report Schedule, with the following additional steps:

1. Select Process & Activity Brief Extract as the Report Type.

2. Select the Extract Criteria.

   When Process & Activity Brief Extract is selected as the Report Type, the following fields become visible (in place of the Report Design and Report Design Status fields):

   • Extract Criteria - a mandatory single selection of the extract criteria to be used, from the Process & Activity Brief Extracts glossary.

   • Extract Criteria Status - this indicates whether the schedule is using the latest version of the selected extract criteria or not. This functions in the same way as the Report Design Status field when scheduling a standard report.

3. View the filters.

   The filters from the selected extract criteria are shown; no additional filters may be applied.

   Figure 7-7 Report Schedule - Filters

   

4. Select the Report Output folder.

   The Save Output To selector will display the sub-folders of the Process & Activity Brief Extracts folder.

5. Save and submit the report schedule.

   Note:

   The output format is an Excel xlsx spreadsheet, so the Format field is not selectable.

   If the number of processes exceeds 800, the report generation may fail, in which case a message will be shown in the report history page of the Report Output record.

6. View the generated report in the Report Outputs library.

   The Process & Activity Brief Extract reports are output to a Process & Activity Brief Extracts folder in the Report Outputs library. Custom sub-folders may be created within this area, per the other report output types.

   The Report Output record shows the extract criteria filters that were applied to generate the report.

Process & Activity Brief Extract File Contents

The Process & Activity Brief Extract report is created as an Excel XLSX spreadsheet.

The column headings are configurable system text, allowing for customization.

The generated report contains all Processes and Activities, and the Brief questions and answers for the required extract criteria. Processes are included based on the Process filters, regardless of the Activity filters; the Activity filters determine which Activities (if any) are included.

Briefs are associated to Processes and Activities through their respective Template glossary records. Process Templates can have multiple Briefs linked to them, but Activity Templates only allow a single Brief.

The following provides a guide to the extract layout.

Figure 7-8 Process & Activity Brief Extract Layout

Table 7-4 Process & Activity Brief Extract Layout

Column	Source Location	Comments
Process ID	Process → Process Details → Details
Parent Process	Process → Process Details → Details
Process Title	Process → Process Details → Details
Template Used	Process → Process Details → Details
Template Type	Process → Process Details → Details
Status	Process → Process Details → Details
Master Process	Process → Process Details → Details
Proposed - Launch Date	Process → Process Schedule → Process Dates
Process Type	Process → Process Details → Details
Master Concept	Process → Process Details → Details
Process Concept	Process → Process Details → Details
Target Consumer	Process → Process Details → Details
Process Manager	Process → Process Details → Details
Role	Process → Teams	Concatenated list of User Roles.
Users	Process → Teams	Concatenated list of Users.
The following columns are repeated per the Extract Criteria Process brief name/question. A pattern match to the Process Brief Name/Question based on the Extract Criteria Process Brief Name/Question, for each Process, determines whether the fields are populated or blank. If a question appears more than once under the same brief/section, the information in these fields will be concatenated (ordered the same across the fields). If there is no match, then all the fields will be blank for the process.
Section Name	Process → Brief → Section Name (Correlating to the Extract Criteria Question)	This is the Section field-set name in the Process Brief, corresponding to the particular Question from the Extract Criteria.
Question (Correlating to the Extract Criteria Process Question)	Process → Brief → Answer (Correlating to the Extract Criteria Question)	This is populated with the Answer, if completed, otherwise it will be blank. The extract will contain at least one row per Process. The process information will be repeated for the number of activities against each process. Note: The activities against a process can be filtered out by the Extract Criteria filters.
Comments	Process → Brief→ Answer → Comments (Correlating to the Extract Criteria Question)	This column will only be included in the extract if the Answer given against one of the processs requires a mandatory comment. If the Answer for the question requires a mandatory comment, this field will be populated with the mandatory comment. The existing brief extract uses the question as a prefix to comments. This field will also incorporate the prefix.
End of Repeated Process columns
Activity Name	Process → Activity → Activity Details → Details	Multiple Activities can be selected in the filter. The extract will output separate rows per Activity, with the Process details repeated on each row.
Proposed End Date	Process → Activity → Activity Details → Details
Actual End Date	Process → Activity → Activity Details → Details
Proposed Start	Process → Activity → Activity Details → Details
Actual Start Date	Process → Activity → Activity Details → Details
Activity Status	Process → Activity → Activity Details → Details
Sub Status	Process → Activity → Activity Details → Details
Completed By	Process → Activity → Activity Details → Details
The following columns are repeated per the Extract Criteria Activity brief name/question. A pattern match to the Activity Brief Name/Question based on the Extract Criteria Activity Brief Name/Question, for each Activity, determines whether the fields are populated or blank. If a question appears more than once under the same brief/section, the information in these fields will be concatenated (ordered the same across the fields). If there is no match, then all the fields will be blank for the Activity.
Section Name	Process → Activity → Brief → Section Name (Correlating to the Extract Criteria Question)	This is the Section field-set name in the Activity Brief, corresponding to the particular Question from the Extract Criteria.
Activity Question (Correlating to the Extract Criteria Activity Question)	Process → Activity → Brief →Answer (Correlating to the Extract Criteria Question)	This is populated with the Answer, if completed, otherwise it will be blank. Note: The activities against a process can be filtered out by the Extract Criteria filters.
Comments	Process → Activity → Brief→ Answer → Comments (Correlating to the Extract Criteria Question)	This column will only be included in the extract if the Answer given against one of the activities requires a mandatory comment. If the Answer for the question requires a mandatory comment, this field will be populated with the mandatory comment. The existing brief extract uses the question as a prefix to comments. This field will also incorporate the prefix.
End of Repeated Activity columns
Planned Working Days	Process → Process Schedule → Process Schedule
Proposed Launch Date	Process → Process Schedule → Process Dates
Actual Launch Date	Process → Process Schedule → Process Dates
Proposed Year	Process → Process Schedule → Process Dates
Actual Year	Process → Process Schedule → Process Dates
Proposed Week	Process → Process Schedule → Process Dates
Actual Week	Process → Process Schedule → Process Dates
Proposed Start Date	Process → Process Schedule → Process Dates
Actual Start Date	Process → Process Schedule → Process Dates
Proposed End Date	Process → Process Schedule → Process Dates
Actual End Date	Process → Process Schedule → Process Dates
Code	Process → Activity → Activity Details → Details
Duration Days	Process → Activity → Activity Details → Details
Sequence	Process → Activity → Activity Details → Details
Checkpoint Only	Process → Activity → Activity Details → Details
Critical Path?	Process → Activity → Activity Details → Details
Is a Key?	Process → Activity → Activity Details → Details
Is a Gate?	Process → Activity → Activity Details → Details
Auto Gate?	Process → Activity → Activity Details → Details
Use Artwork	Process → Activity → Activity Details → Details
Activity Type	Process → Activity → Activity Details → Activity Type
Record	Process → Activity → Activity Details → Activity Type
Started Status	Process → Activity → Activity Details → Activity Type
Completed Status	Process → Activity → Activity Details → Activity Type