8 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, Site, Product Record, and Product Specification. 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 Project & Activity Brief Extract data extract type is also available for extracting Brief questions and answers for Projects and Activities. The extract is generated as an XLSX Excel spreadsheet and is stored in the Report Outputs library.
Extracting Suppliers
To extract Suppliers:
-
Open a list view of Suppliers.
-
Use the Advanced Search facility to filter the contents to the suppliers you wish to extract.
-
Select the Extract Data action. The Confirm Data Extract dialog box appears.
Figure 8-1 Confirm Data Extract Dialog Box
-
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.
-
Click the icon to select a report output folder where the generated data file is to be saved.
-
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:
-
Open a list view of Sites.
-
Use the Advanced Search facility to filter the contents to the sites you wish to extract.
-
Select the Extract Data action. The Confirm Data Extract dialog box appears.
-
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.
-
Click the icon to select a report output folder where the generated data file is to be saved.
-
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.
Extracting Product Records
To extract Product Records:
-
Open a list view of Product Records or Produce Product Records.
-
Use the Advanced Search facility to filter the contents to the products you wish to extract.
-
Select the Extract Data action. The Confirm Data Extract dialog box appears.
-
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.
-
Click the icon to select a report output folder where the generated data file is to be saved.
-
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 Product Records list view.
A Product Record 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.
Product Records Extract File Contents
The CSV file name is ProductRecordExtract for non-Produce Product Records and ProduceProductRecordExtract for Produce Product Records, with the batch job number appended.
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 Product 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 Specifications) are not output.
Columns that relate to a glossary (such as Business Category) contain the Description (not the Code) of the glossary record.
Extracting Product Specifications
To extract Product Specifications or Produce Product Specifications:
-
Open a list view of Specifications or Produce Specifications.
-
Use the Advanced Search facility to filter the contents to the specifications you wish to extract.
-
Select the Extract Data action. The Confirm Specification Data Extract dialog box appears.
Figure 8-2 Confirm Specifications Data Extract Dialog Box
-
Select the specification type. If no specifications of the selected type appear in the list view, the extract file will be empty. This field is omitted if extracting Produce specifications, otherwise it is mandatory.
-
Select which section's data is to be extracted. The options are filtered based on the selected Specification Type. If no specifications of the selected type that appear in the list view contain the selected section type, the extract file will be empty. The field is mandatory.
-
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.
-
Click the icon to select a report output folder where the generated data file is to be saved.
-
To continue with the extract, click Ok. To return to the Confirm dialog box, click Cancel.
A submission confirmation message box is shown. Click Cancel to return to the Specifications list view.
A Product Specification 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.
Product Specifications Extract File Contents
The CSV file name is ProductSpecificationExtract, with the batch job number appended.
The file contains details for the selected specification section.
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 Specification section will be output, including any custom fields, but excluding change history data.
In order to identify the specification, the left most columns of the output for any section will consist of the following from the Main Details section:
Specification Name; Spec Number; Version; Product Coverage (Quantity/Product Number/Alt. Product No. (comma-separated if multiple); Brand; Business Categories; Supplier Name & Code; Site Name & Code (comma-separated if multiple); Product Technologist; Specification Status.
The individual rows of tables are output as separate rows (the parent data is not repeated). For example, a specification with a recipe containing two ingredients, each with two raw materials would output as:
1 |
Spec 1 details |
Ingredient A details |
Ingredient A raw material 1 details |
2 |
Ingredient A raw material 2 details |
||
3 |
Ingredient B details |
Ingredient B raw material 1 details |
|
4 |
Ingredient B raw material 2 details |
||
5 |
Spec 2 details |
Ingredient A details |
Ingredient A raw material 1 details |
6 |
Ingredient A raw material 2 details |
||
7 |
Ingredient B details |
Ingredient B raw material 1 details |
|
8 |
Ingredient B raw material 2 details |
Columns that relate to a glossary (such as Business Category) contain the Description (not the Code) of the glossary record. Columns that have translations contain the value in the selected language, regardless of the specification's Pack Copy Language.
Where a specification has multiple values in fields such as Technologist or Business Category, the values are concatenated into a single cell using a comma as the separator.
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 8-3 Data Extract Report Output Details Page
![Thisshows the Data Extract Report Output Details page. Thisshows the Data Extract Report Output Details page.](img/extracts-report-output.png)
Table 8-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.
Project & Activity Brief Extracts
A data extract of Project and Activity Brief data can be scheduled as a report, utilizing pre-configured criteria for filtering Projects, Activities, and their Brief questions.
The Project & Activity Brief Extracts glossary is used to configure reusable data extract criteria. When scheduling the report, setting the report type as a Project & 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 Project and Activity records, and their Brief questions and answers, as defined by the extract criteria. The generated extract is output to a Project & 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 Projects and Activities list views. The output is also placed in the Project & 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 Projects, Activities, and questions from the associated Project and Activity Briefs and current Brief template glossary records.
To access the Extract Criteria, select the Manage Project & Activity Brief Extracts option in the Reporting Admin area. This is only accessible to users with the Advanced Reporting Administrator authority profile.
Figure 8-4 Extract Criteria Page
![This figure shows the Extract Criteria page. This figure shows the Extract Criteria page.](img/extract_criteria_page.png)
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:
-
Select the New action. The New Extract Criteria page appears.
Figure 8-5 New Extract Criteria Page
-
Enter the details.
Table 8-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.
-
Optionally add any filters to be applied to the extract criteria. See Filters.
-
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 8-6 Extract Criteria Details
![This figure shows the Extract Criteria Details page. This figure shows the Extract Criteria Details page.](img/edit-extract-details.png)
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 8-7 Extract Criteria Details - Filters
![This figure shows the Extract Criteria Details for filters. This figure shows the Extract Criteria Details for filters.](img/extract-criteria-details-filters.png)
To add a filter to an extract criteria:
-
Open the extract criteria in edit mode and navigate to the Filters page.
-
Add a row to the Questions tables and click Add.
-
Enter the details.
Table 8-3 Filters
Details Description Project Filters
Optionally choose any specific Projects to be included in the extract by selecting any combination of Project Titles, Project Status, Project Templates, Project Types, Business Categories, and Project 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).
Project Extract Questions
When adding a row to the Project Extract Questions table, a list of Project 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.
-
Save the changes and exit. If no Project or Activity questions have been selected, validation will show a warning message.
Scheduling the Report
To schedule a Project & Activity Brief Extract report, follow Creating a Report Schedule, with the following additional steps:
-
Select Project & Activity Brief Extract as the Report Type.
-
Select the Extract Criteria.
When Project & 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 Project & 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.
-
-
View the filters.
The filters from the selected extract criteria are shown; no additional filters may be applied.
Figure 8-8 Report Schedule - Filters
-
Select the Report Output folder.
The Save Output To selector will display the sub-folders of the Project & Activity Brief Extracts folder.
-
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 projects 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.
-
View the generated report in the Report Outputs library.
The Project & Activity Brief Extract reports are output to a Project & 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.
Project & Activity Brief Extract File Contents
The Project & 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 Projects and Activities, and the Brief questions and answers for the required extract criteria. Projects are included based on the Project filters, regardless of the Activity filters; the Activity filters determine which Activities (if any) are included.
Briefs are associated to Projects and Activities through their respective Template glossary records. Project 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 8-9 Project & Activity Brief Extract Layout
![This figure shows the Project & Activity Brief Extract layout. This figure shows the Project & Activity Brief Extract layout.](img/project-activity-brief-extract-layout.png)
Table 8-4 Project & Activity Brief Extract Layout
Column | Source Location | Comments |
---|---|---|
Project ID |
Project → Project Details → Details |
|
Parent Project |
Project → Project Details → Details |
|
Project Title |
Project → Project Details → Details |
|
Template Used |
Project → Project Details → Details |
|
Template Type |
Project → Project Details → Details |
|
Status |
Project → Project Details → Details |
|
Master Project |
Project → Project Details → Details |
|
Proposed - Launch Date |
Project → Project Schedule → Project Dates |
|
Project Type |
Project → Project Details → Details |
|
Master Concept |
Project → Project Details → Details |
|
Project Concept |
Project → Project Details → Details |
|
Target Consumer |
Project → Project Details → Details |
|
Project Manager |
Project → Project Details → Details |
|
Proposed Products |
Project → Project Details → General Product Details |
|
Product Range Information |
Project → Project Details → General Product Details |
|
Packaging |
Project → Project Details → General Product Details |
|
Spec Type |
Project → Project Details → General Product Details |
|
Brand |
Project → Project Details → General Product Details |
|
Sub Brand |
Project → Project Details → General Product Details |
|
Categories |
Project → Project Details → General Product Details |
Multiples concatenated. Full paths for each category. |
New Category Comments |
Project → Project Details → General Product Details |
|
Supplier |
Project → Project Details → General Product Details |
|
Sites |
Project → Project Details → General Product Details |
Multiples concatenated. |
Role |
Project → Teams |
Concatenated list of User Roles. |
Users |
Project → Teams |
Concatenated list of Users. |
The following columns are repeated per the Extract Criteria Project brief name/question. A pattern match to the Project Brief Name/Question based on the Extract Criteria Project Brief Name/Question, for each Project, 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 project. |
||
Section Name |
Project → Brief → Section Name (Correlating to the Extract Criteria Question) |
This is the Section field-set name in the Project Brief, corresponding to the particular Question from the Extract Criteria. |
Question (Correlating to the Extract Criteria Project Question) |
Project → 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 Project. The project information will be repeated for the number of activities against each project. Note: The activities against a project can be filtered out by the Extract Criteria filters. |
Comments |
Project → 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 projects 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 Project columns |
||
Activity Name |
Project → Activity → Activity Details → Details |
Multiple Activities can be selected in the filter. The extract will output separate rows per Activity, with the Project details repeated on each row. |
Proposed End Date |
Project → Activity → Activity Details → Details |
|
Actual End Date |
Project → Activity → Activity Details → Details |
|
Proposed Start |
Project → Activity → Activity Details → Details |
|
Actual Start Date |
Project → Activity → Activity Details → Details |
|
Activity Status |
Project → Activity → Activity Details → Details |
|
Sub Status |
Project → Activity → Activity Details → Details |
|
Completed By |
Project → 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 |
Project → 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) |
Project → 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 project can be filtered out by the Extract Criteria filters. |
Comments |
Project → 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 |
Project → Project Schedule → Project Schedule |
|
Proposed Launch Date |
Project → Project Schedule → Project Dates |
|
Actual Launch Date |
Project → Project Schedule → Project Dates |
|
Proposed Year |
Project → Project Schedule → Project Dates |
|
Actual Year |
Project → Project Schedule → Project Dates |
|
Proposed Week |
Project → Project Schedule → Project Dates |
|
Actual Week |
Project → Project Schedule → Project Dates |
|
Proposed Start Date |
Project → Project Schedule → Project Dates |
|
Actual Start Date |
Project → Project Schedule → Project Dates |
|
Proposed End Date |
Project → Project Schedule → Project Dates |
|
Actual End Date |
Project → Project Schedule → Project Dates |
|
Code |
Project → Activity → Activity Details → Details |
|
Duration Days |
Project → Activity → Activity Details → Details |
|
Sequence |
Project → Activity → Activity Details → Details |
|
Checkpoint Only |
Project → Activity → Activity Details → Details |
|
Critical Path? |
Project → Activity → Activity Details → Details |
|
Is a Key? |
Project → Activity → Activity Details → Details |
|
Is a Gate? |
Project → Activity → Activity Details → Details |
|
Auto Gate? |
Project → Activity → Activity Details → Details |
|
Use Artwork |
Project → Activity → Activity Details → Details |
|
Activity Type |
Project → Activity → Activity Details → Activity Type |
|
Record |
Project → Activity → Activity Details → Activity Type |
|
Started Status |
Project → Activity → Activity Details → Activity Type |
|
Completed Status |
Project → Activity → Activity Details → Activity Type |