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:

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

Extracting Product Records

To extract Product Records:

1. Open a list view of Product Records or Produce Product Records.

2. Use the Advanced Search facility to filter the contents to the products 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 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:

1. Open a list view of Specifications or Produce Specifications.

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

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

   Figure 8-2 Confirm Specifications Data Extract Dialog Box

   

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

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

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

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

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

Table 8-1 Data Extract Report Output Details Page

Details	Description
Report Name	The name of the generated data extract 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

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 8-5 New Extract Criteria Page

   

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

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 8-6 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 8-7 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 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). Note: The Project Title filter uses the Contain Text operator to select projects by title, based on a partial text string match rather than having to select individual projects. Where the filter fails to match any projects, no projects will be output in the extract. It is not necessary to amend the filter if new Projects are added, as it will automatically pick up new Projects if they match the text string.
   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.

4. 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:

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

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

3. View the filters.

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

   Figure 8-8 Report Schedule - Filters

   

4. Select the Report Output folder.

   The Save Output To selector will display the sub-folders of the Project & 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 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.

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

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. Complete the list so the entries correlate to the Users listed in the Users column.
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