1. Reports User Guide
  2. Appendix: Report Layouts

A Appendix: Report Layouts

This appendix summarizes the suite of core reports supplied with the Reports module. Following the list of reports are details of common aspects of the report layouts and formatting.

Some common features are as follows:

  • Where Product Record or Product Specification reports include Site data as default or additional fields, such as Business Categories or Retailer Contacts, the data is from the related primary sites, not secondary sites.

  • By default, some Product Specification reports may include deleted specifications. Specifications are set to a Deleted status rather than actually being deleted, allowing for them to be undeleted by an administrator. For reports that include all specification statuses, a version of the report can be created with a Specification Status filter set to exclude deleted specifications. In addition to the Deleted status, it may be desirable to also exclude those at Not Progressed, Superseded, Delisted, Archived, or any of the Draft statuses.

  • When selecting users in a filter, such as a Technologist, Project Manager, or Created By name, the user selector includes a text search on name (including the email address in the case of the fields being concatenated), for an individual user to be selected from the returned list. Alternatively, a partial text search can be applied using the Contains operator.

  • For reports that include Project Activities, the default activity name field/filter (also within the lists of linked records) is the name of the activity's template. As the template name may be overridden in the Activity record, that field is available as an optional field/filter.

Note:

Certain reports have predefined filters applied to include a specific specification type, such as:

  • The Produce specific variants of reports.

  • The Product Requirement Reports (CR42 to CR46) which have a variant per specification type.

  • The specification type-specific Label Claims Statements reports (CR54 to CR57).

To make changes to the system-delivered filters, take a copy of the system-delivered data source or design, and edit the filters as required.

For further details of each report layout and its fields and filters, see Oracle Retail Brand Compliance Management Cloud Service Report Layouts.

Standard Reports

Table A-1 Standard Reports

Report Description

CR01 Ingredients Where Used

Based on the Food Recipe & RM and FNF Formulation & RM sections, the report searches Product Specifications for the specified ingredients.

Can search for multiple countries, or multiple ingredients. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified ingredients, or to locate specifications that contain any of the specified ingredients.

CR02 Ingredients Country of Origin

Based on the Food Recipe & RM and FNF Formulation & RM sections, the report searches Product Specifications for ingredients from specified countries of origin, or specific ingredients from specified countries of origin.

Can search for multiple countries, or multiple ingredients and countries. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified countries/ingredients, or to locate specifications that contain any of the specified countries/ingredients.

CR03 Ingredient Suppliers

Based on the Food Recipe & RM and FNF Formulation & RM sections, the report searches Product Specifications containing any ingredients from the specified Raw Material Suppliers, or for specific ingredients from the specified Raw Material Suppliers.

Can search for multiple suppliers or ingredients. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified ingredients from the suppliers, or to locate specifications that contain any of the specified ingredients from the suppliers.

CR04 Ingredients Usage Quantity

Two versions: a) Ingredients Usage Quantity; b) Ingredients Usage Quantity (per Business Category).

Based on the Food Recipe & RM and FNF Formulation & RM sections, the report searches Product Specifications for specific Ingredients, to show the Quantities (Total %), plus Product Declared Quantity.

Option is available to group the output by Business Category (full path).

Can search for multiple ingredients, where the specification contains any of the specified ingredients.

This report query should cover both the Allergens tab and the Contains tab in the Food or BWS specifications, and the Additional Information page in the Constructed and Formulated specifications.

From this report query, users can create separate report outputs for a particular type of specification, or tabs within the specification (Allergens or Contains) if they wish, based on the questions they filter on.

CR05 Raw Materials Sustainability

Based on the Food Recipe & RM and FNF Formulation & RM sections, the report searches Product Specifications for specific Ingredients for the specified Sustainability categories.

Can search for multiple ingredients and sustainability categories, where the specification contains any of the specified ingredients for the specified sustainability categories.

CR06 Dietary, Allergen, Additional Info - Present/Free From

Based on the Dietary and Allergy and Additional Info sections, for all specification types except Produce, the report searches Product Specifications for specific allergens being either present or not present.

Options are available to either include or exclude items with Cross Contamination risk, Present on same line, or Present on same plant.

Originally intended to be pivoted, to show allergens as columns rather than rows, as the allergens are configurable per portal, this core report will report the allergens as rows rather than columns. Clients may build their own pivoted variants of the report using the Soft-Coded Columns.

Can search for multiple allergens. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified allergens, or to locate specifications that contain any of the specified allergens.

CR07 Dietary, Allergen - Suitable For

Based on the Dietary and Allergy section, the report searches Food Product Specifications for the specified Suitable For questions being answered Yes or No.

Originally intended to be pivoted, to show allergens as columns rather than rows, as the allergens are configurable per portal, this core report will report the allergens as rows rather than columns. Clients may build their own pivoted variants of the report using the Soft-Coded Columns.

Can search for multiple questions. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified Suitable For questions, or to locate specifications that contain any of the specified Suitable For questions.

CR08 Nutrient Profile by Product

Based on the Nutrition section, the report searches Food, Produce, and BWS Product Specifications for those meeting a particular nutrition profile, by specifying nutrient and minimum/maximum values, such as to find the products which are greater than 10% Fat and 20% sugar.

Option is available to define whether the values are Per 100 on pack g or Per Serving on pack.

Originally intended to be pivoted, to show nutrients as columns rather than rows, as the nutrients are configurable per portal, this core report will report the nutrients as rows rather than columns. Clients may build their own pivoted variants of the report using the Soft-Coded Columns.

Can search for multiple nutrients. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified nutrients with a matching min/max value, or to locate specifications that contain any of the specified nutrients with a matching min/max values.

Note: The report will be pivoted to have one row per Product, with the selected Nutrients appearing as columns.

To obtain all products, the Nutrient filters can be set to, for example, >0.

CR09 Nutrient Targets

Two versions: a) Nutrient Targets (per Product); b) Nutrient Targets (per Target Category).

Based on the Nutrition section, the report searches active specifications which meet or do not meet their nutrient targets, filtered by Nutrient and Nutrient Target Category (achieved by the Nutrient Target Filter).

Can search for multiple nutrients. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified nutrients which meet the target, or to locate specifications that contain any of the specified nutrients which meet the target.

CR10 Products Country of Origin

Based on the Other Labelling Copy section, the report searches Product Specifications for products from specified countries of origin. Can search for multiple countries.

CR11 CNF Materials Country of Origin

Based on the CNF Specifications Components section, the report searches for Product Specifications containing any materials from specified countries of origin, or specified components from specified countries of origin.

Can search for multiple countries, or multiple components and countries, and specify All or Any.

CR12 Product Country of Manufacturing Origin

Two versions: a) Product Country of Manufacturing; b) Product Country of Manufacturing (per Country).

Based on the Main Details section, the report searches Product Specifications (all types) for those where the primary site's location country matches a selected country.

Option is available to group the output by Country.

Can search for multiple countries, locating specifications where the primary sites are located in any of the specified countries.

CR13 Product Certificates - Used on Pack

Two versions: a) Product Certificates - Used on Pack; b) Product Certificates - Used on Pack (per Certificate).

Based on the Other Labelling Copy section, the report searches Product Specifications where the Certificates table contains the specified certificate type.

Option is available to group the output by Certificate Type.

Only certificates that appear on pack are included (that is, where the Used on Pack option is number, icon, or both).

CR14 Product Shelf Life

Two versions: a) Product Shelf Life [for Food and BWS specifications]; b) Product Shelf Life (Produce).

Based on the Storage section, the report searches Product Specifications where the Supplier or Business Category matches the specified value. The report lists details from the Pack Coding table.

Can search for multiple suppliers or business categories.

CR15 Packaging Components

Based on the Packaging section, the report searches Product Specifications (all types except Produce) for those containing any Packaging Components from specified Raw Material Suppliers, Countries of Origin, or Materials.

Can search for multiple suppliers, countries, or materials, locating specifications that match the specified selections.

CR16 Packaging Components (Produce)

The report searches Produce Product Records for those containing any Packaging Components from specified Raw Material Suppliers, Countries of Origin, or Materials.

Can search for multiple suppliers, countries, or materials, locating specifications that match the specified selections.

Note: A filter is applied to the system-delivered Data Source record, to only include Produce specifications.

CR17 Surveillance Testing Requirements - Product Attributes (As Sold)

Two versions: a) Surveillance Testing Requirements - Product Attributes (As Sold) for non-Produce specifications; b) Surveillance Testing Requirements - Product Attributes (As Sold - Produce) for Produce.

Based on the Finished Product Standards section, the report searches Product Specifications (all types) for those containing any Product Surveillance Classifications that match the specified value. The report lists details from the FPS Attributes tables.

The report is from the perspective of the Specification, but pulls Surveillance Classifications from the Product record (except for Produce, which holds the surveillance classifications in the specification's Main Details section).

CR18 Surveillance Testing Requirements - Product Attributes (As Consumed)

Based on the Finished Product Standards section, the report searches Product Specifications (Food types) for those containing any Product Surveillance Classifications that match the specified filter values. The report lists details from the FPS Attributes tables. The report only includes products that have surveillance classifications present.

The report is from the perspective of the Specification, but pulls Surveillance Classifications from the Product record.

CR19 Surveillance Testing Requirements - Physical Standards

Two versions: a) Surveillance Testing Requirements - Physical Standards for non-Produce specifications; b) Surveillance Testing Requirements - Physical Standards (Produce) for Produce.

Based on the Finished Product Standards section, the report searches Product Specifications (all types) for those containing any Product Surveillance Classifications that match the specified value. The report lists details from the Physical Attributes tables.

CR20 Surveillance Testing Requirements - Chemical Standards

Two versions: a) Surveillance Testing Requirements - Chemical Standards for non-Produce specifications; b) Surveillance Testing Requirements - Chemical Standards (Produce) for Produce.

Based on the Finished Product Standards section, the report searches Product Specifications (all types) for those containing any Product Surveillance Classifications that match the specified value. The report lists details from the Chemical Attributes tables.

The report is from the perspective of the Specification, but pulls Surveillance Classifications from the Product record (except for Produce, which holds the surveillance classifications in the specification's Main Details section).

CR21 Surveillance Testing Requirements - Microbiological Standards

Two versions: a) Surveillance Testing Requirements - Microbiological Standards for non-Produce specifications; b) Surveillance Testing Requirements - Microbiological Standards (Produce) for Produce.

Based on the Finished Product Standards section, the report searches Product Specifications (all types) for those containing any Product Surveillance Classifications that match the specified value. The report lists details from the Microbiological Attributes tables.

The report is from the perspective of the Specification, but pulls Surveillance Classifications from the Product record (except for Produce, which holds the surveillance classifications in the specification's Main Details section).

CR22 Surveillance Classifications

Two versions: a) Surveillance Classifications for non-Produce specifications; b) Surveillance Classifications (Produce).

The report searches Product Records (all types) for those containing any Product Surveillance Classifications that match the specified value.

The report is from the perspective of the Product Record (except for Produce, which holds the surveillance classifications in the specification's Main Details section).

CR23 Surveillance Test Report Summary

Four versions: a) Surveillance Test Report Summary and b) Surveillance Test Report (per Supplier) for non-Produce specifications; c) Surveillance Test Report Summary (Produce) and d) Surveillance Test Report (per Supplier - Produce) for Produce.

The report searches Surveillance Test Reports based on the selected filters.

Filter the date of sampling on either a date range or a relative date.

Option is available to group the output by Supplier.

CR24 Surveillance Results by Date Range

The report provides a KPI trend analysis of completed surveillance reports, by supplier.

A monthly analysis of the number of completed Test Reports, within a date range is presented as table of the count of those with a completion date, sorted by test discipline, grouped by month of completion.

Option is available to show the results by Test Score.

CR25 Surveillance Billing Report

Eight versions: a) Surveillance Billing Report, b) Surveillance Billing Report (per Supplier), c) Surveillance Billing Report (per Test Discipline), and d) Surveillance Billing Report (per Supplier and Test Discipline) for non-Produce specifications; e) Surveillance Billing Report (Produce), f) Surveillance Billing Report (per Supplier - Produce), g) Surveillance Billing Report (per Test Discipline - Produce), and h) Surveillance Billing Report (per Supplier and Test Discipline - Produce) for Produce.

The report searches Product Records (or the Product Specification Main Details section for Produce) by date range and supplier, showing test results and costs/billing details. Total cost is calculated by date range.

Option is available to group the output by Supplier and/or Test Discipline.

CR26 Audits/Visits by Date Range

The report provides a KPI trend analysis of completed Audits & Visits within a specified date range by supplier.

A monthly analysis of the number of completed Audits/Visits, between specified date range is presented as table of the count of those with a completion date, sorted by audit/visit type, grouped by month of completion.

Option is available to show the results by Audit Type.

CR27 Sites with Audits/Visits Due or Overdue

Three versions: a) Sites with Audits/Visits Due or Overdue; b) Sites with Audits/Visits Due or Overdue (per Supplier); c) Sites with Audits/Visits Due or Overdue (per Audit/Visit Type).

The report searches due or overdue Audit/Visit, listing them by Site. The number of days due or overdue is calculated (+/- relative to the current date).

Option is available to group the output by Supplier or by Audit/Visit Type.

Separate reports can be run for due and overdue.

CR28 Audits/Visits with Issues Overdue

Three versions: a) Audits/Visits with Issues Overdue; b) Audits/Visits with Issues Overdue (per Supplier); c) Audits/Visits with Issues Overdue (per Audit/Visit Type).

The report searches Audits & Visits that have overdue Issues, listing them by Site. The number of days overdue is calculated (+/- relative to the current date).

Option is available to group the output by Supplier or by Audit/Visit Type.

CR29 Scorecards by Date Range

The report provides a KPI trend analysis of completed Scorecards within a specified date range by supplier.

A monthly analysis of the number of completed Scorecards, between the specified date ranges is presented as a time series chart.

Option is available to show the results by Scorecard Type.

CR30 Sites with Scorecards Overdue

Three versions: a) Sites with Scorecards Overdue; b) Sites with Scorecards Overdue per Supplier; c) Sites with Scorecards Overdue per Score Type.

The report searches Scorecards that are overdue, listing them by Site. The number of days overdue is calculated (+/- relative to the current date).

Option is available to group the output by Supplier or by Scorecard Type.

CR31 Site References (Certificates) - All Valid / Overdue by Type

Two versions: a) Site References (Certificates) - All Valid/Overdue by Type; b) Site References (Certificates) - All Valid/Overdue by Type (per Supplier); c) Site References (Certificates) - All Valid/Overdue by Type (per Certificate Type).

The report searches Sites that have certificates (references).

Option is available to group the output by Supplier or by Certificate Type.

Two separate reports list certificates that are either overdue or valid (a certificate is considered to be overdue if its expiry date is passed the current date).

CR32 Supplier/Site Contacts

The report searches Contacts by role, listing the Supplier or Site Contact details by contact role for those that match the specified role.

The granularity of the report is Contact, so the output is a single row per contact, with their roles concatenated within supplier roles and site roles columns. Provides a similar output to the Download to Excel action on the Contacts list view. Site code, name, type and technologist details are concatenated in the event of multiple sites.

The output is a single row per contact, showing all the contact’s supplier/site roles – for example, if the filter was Main Contact, the output only include contacts that had that role, but the column in the report would show all the contacts’ roles.

CR33 Project Status/Performance Summary

Four versions: a) Project Status/Performance Summary; b) Project Status/Performance Summary (per Supplier); c) Project Status/Performance Summary (per Business Category); d) Project Status/Performance Summary (per Project Manager).

The report searches Projects, with option to filter by a range of project launch dates.

Option is available to group the output by Supplier, by Business Category, or by Project Manager.

If the project is overdue, the number of days overdue is calculated (based on the current date).

No. Days Overdue = the actual end date minus the proposed end date, using the date the query is executed if the actual end date is not set. The column will be empty/null if the result of the calculation is less than or equal to 0 (that is, that the proposed end date is after or the same as the actual end date / the date the query is executed.)

CR34 Project Activity Status/Performance Summary

Four versions: a) Project Activity Status/Performance Summary; b) Project Activity Status/Performance Summary (per Supplier); c) Project Activity Status/Performance Summary (per Business Category); d) Project Activity Status/Performance Summary (per Project Manager).

The report searches Project Activities, with option to filter by a range of project launch dates.

Option is available to filter by one or more specific activity types.

Option is available to group the output by Supplier, by Business Category, or by Project Manager.

If the activity is overdue, the number of days overdue is calculated (based on the current date).

The report also includes Activities which are not yet Overdue, but where the Due Date is within a specified period from the date the report is run, using a relative date filter.

The report can be used to show the name of the last (based on sequence) critical path activity completed and the name of the next (based on sequence) critical path activity due, plus the scheduled start and completion dates.

CR35 Project Resource

The report presents a bar chart of Projects, showing the number of working days for Teams, Roles and Individuals, based on the Activities associated with the Responsible users, by date range of activity scheduled start and end dates.

Specify a date range as a relative value, such as this week, this month, or the next x months.

The output can be narrowed by Project Type; Business Category; Project Name; Activity Name.

CR36 Active Specifications Summary

Two versions: a) Active Specifications Summary; b) Active Specifications Summary (Multiples Breakdown).

Both reports utilize pivot tables and, for this reason, the recommended report output is Excel as this format manages the layout when the data causes the tables to exceed the page width.

a) Shows counts for each specification type/category grouping, by active date or period. The final total will always match the actual number of specifications. Separate tables give totals per date or period (month and year) of approval by different groupings:

  • Specifications Approved by Date (per Technologist)

  • Specifications Approved by Period (per Technologist)

  • Specifications Approved by Date (per Business Category)

  • Specifications Approved by Period (per Business Category)

  • Specifications Approved by Date (per Country Where Sold)

  • Specifications Approved by Period (per Country Where Sold)

  • Specifications Approved by Date (per Supplier)

  • Specifications Approved by Period (per Supplier)

All specification types can be reported on; each row gives count columns per specification type and category grouping with totals per row and per column.

b) Shows a full breakdown for each specification type/category, but only for categories where multiples can occur, by active date or period. Where a specification has multiples within a specific category, this will be output for each multiple so it will exceed the actual number of specifications if there are multiples. Separate tables give totals for each category:

  • Specifications Approved by Date (per Business Category)

  • Specifications Approved by Period (per Business Category)

  • Specifications Approved by Date (per Country Where Sold)

  • Specifications Approved by Period (per Country Where Sold)

CR37 Products without an Active Specification

The report searches Product Records (all types except Produce) that do not have an active Product Specification associated.

CR38 Sites without a Completed Audit/Visit

The report searches Sites that do not have a completed Audit or Visit of the specified type.

The report also includes Sites with a Completed Audit/Visit of the specified type, but where the Completion Date is older than a specified period from the date the report is run, using a relative date filter.

CR39 Sites without a Completed Scorecard

The report searches Sites that do not have a completed Scorecard of the specified type.

The report also includes Sites with a Completed Scorecard of the specified type, but where the Completion Date is older than a specified period from the date the report is run, using a relative date filter.

CR40 Produce Specification Acceptance

Based on Produce Specifications, and the Supplier Information section, the report returns a list of Produce Specifications which meet the filters applied.

The report includes basic details from the Produce Specification Main Details section, plus details from the Supplier Information section.

The details are taken from the Specification, not the Product Record, as the Product Record is not created until after the Specification has been accepted.

Note: A filter is applied to the system-delivered Data Source record, to only include Produce specifications.

CR41 Produce Ingredients Country of Origin

Based on the Produce Specifications Product Requirements section, the report searches for specifications containing any Ingredients from specified countries of origin, or specified ingredients from specified countries of origin.

Can search for multiple countries, or multiple ingredients and countries, and specify All or Any.

Since the Ingredient Country of Origin is held as a table within the Product Sourcing table, the report will contain multiple rows for each applicable country found.

Where the report shows the months per country, these will be a comma separated list.

Note: A filter is applied to the system-delivered Data Source record, to only include Produce specifications.

CR42 Product Requirement Profile / Tender Report - Food

Selected details from product specifications presented as a form type layout. The report may contain one or many (up to 10) specifications. The available filters are based on those of the Specification API.

The report presents a separate page per specification. If a specification contains multiple occurrences of a section, those sections are repeated within the report, with any section names shown.

Note: The core report templates only include sections for the report's specification type. So, for example, the Food report will only contain Food specification sections. If a Food specification also has for example FNF sections, they will not appear in the Food report. The client can create a custom version of the report template to include sections of other specification types in the report if required.

The report includes some static text, which is configured within the RTF template.

CR43 Product Requirement Profile / Tender Report - FNF

CR44 Product Requirement Profile / Tender Report - CNF

CR45 Product Requirement Profile / Tender Report - Produce

CR46 Product Requirement Profile / Tender Report - BWS

CR47 Specification Status History

Detail of the specification's status change history, from the Status Change History table in the Change History section.

CR48 Audit/Visit Issue Status

Three versions: a) Audit/Visit Issue Status; b) Audit/Visit Issue Status (per Supplier); c) Audit/Visit Issue Status (per Audit/Visit Type).

Similar to CR28 Audits/Visits with Issues Overdue, but includes Issues at any status (per the filter), rather than just Overdue Issues.

Option is available to group the output by Supplier or by Audit/Visit Type.

CR49 Audit/Visit Summary

Three versions: a) Audit/Visit Summary; b) Audit/Visit Summary (per Supplier); c) Audit/Visit Summary (per Audit/Visit Type).

The report searches for Audits/Visits based on the selected Filters.

Issue Details can be included, but overall granularity is by Audit.

Option is available to group the output by Supplier or by Audit/Visit Type.

CR50 Audit/Visit Certificate

The report searches for Audits/Visits based on the selected Filters.

Non Conformance/Issue details are included, but overall granularity is by Audit. The report presents a page per Audit/Visit.

Option is available to group the output by Supplier or by Audit/Visit Type.

A maximum of 10 Audits/Visit may be selected per run.

CR51 Scorecard Summary

Three versions: a) Scorecard Summary; b) Scorecard Summary (per Supplier); c) Scorecard Summary (per Scorecard Type).

The report searches for Scorecards based on the selected Filters.

Option is available to group the output by Supplier or by Scorecard Type.

CR52 New Suppliers

The report lists Supplier records with a Created On date that is newer than a specified period from the date the report is run, using a relative date filter.

CR53 Nutrient Front of Pack Profile by Product

Based on the Nutrition section, the report searches Food, Produce, and BWS Product Specifications for those meeting a particular nutrition Front of Pack profile, by specifying any of the following:

  • Nutrient and minimum/maximum values - Option available to determine whether values are Per 100g on pack or Per Serving on pack.

  • Nutrient and %RI/DV minimum/maximum values.

  • Front of Pack Score.

Originally intended to be pivoted, to show nutrients as columns rather than rows, as the nutrients are configurable per portal, this core report will report the nutrients as rows rather than columns. Clients may build their own pivoted variants of the report using the Soft-Coded Columns.

Can search for multiple nutrients. The AND/OR logic in the filter statement builder can be used to locate specifications containing all of the specified nutrients with a matching min/max value, or to locate specifications that contain any of the specified nutrients with a matching min/max value.

CR54 Label Claims & Statements - Food

Report is based on the OLC Section of the Food Specification, and finds any Specifications which contain the Claims specified in the filters.

There will be one report query to accommodate all Food Spec claims. Since most claim fields in the OLC section are tables, including multiple claims and filers in the same report output will result in duplication of rows, therefore users should create separate reports per claim.

CR55 Label Claims & Statements - Formulated Non Food

Report is based on the OLC Section of the Formulated Non Food Specification, and finds any Specifications which contain the Claims specified in the filters.

There will be one report query to accommodate all Formulated Non Food Spec claims. Since most claim fields in the OLC section are tables, including multiple claims and filers in the same report output will result in duplication of rows, therefore users should create separate reports per claim.

CR56 Label Claims & Statements - Constructed Non Food

Report is based on the OLC Section of the Constructed Non Food Specification, and finds any Specifications which contain the Claims specified in the filters.

There will be one report query to accommodate all Constructed Non Food Spec claims. Since most claim fields in the OLC section are tables, including multiple claims and filers in the same report output will result in duplication of rows, therefore users should create separate reports per claim.

CR57 Label Claims & Statements - BWS

Report is based on the OLC Section of the BWS Specification, and finds any Specifications which contain the Claims specified in the filters.

There will be one report query to accommodate all BWS Spec claims. Since most claim fields in the OLC section are tables, including multiple claims and filers in the same report output will result in duplication of rows, therefore users should create separate reports per claim.

CR58 Project Summary

Four versions: a) Project Summary; b) Project Summary (per Supplier); c) Project Summary (per Business Category); d) Project Summary (per Project Manager).

The report searches Projects, based on the selected filters.

If the project is overdue, the number of days overdue is calculated (based on the current date).

CR59 Project Activity Summary

The report searches Activities, based on the selected filters.

If the Activity is overdue, the number of days overdue is calculated (based on the current date).

CR60 Global Network Bus Errors

The report is a listing of any errors that have occurred during the synchronization of data within the Global Network Bus (GNB) portal. As the GNB is only accessible by Oracle Hosting, it is not possible for system administrators or Oracle Support to monitor for integration issues. This report can be scheduled to run on the Global or Satellite portals.

If any of the following web services listed in the Web Service Log have a status of Failed or Internal Failure in the past 24 hours (assuming scheduled to run every 24 hours), the error is reported.

The following API web services are included in the reports referenced below:

  • ProductRecordRestService - Product Record Failures Report

  • MyArtworkRestService - MyArtwork Record Failures Report

  • ContactRestService - Contact Record Failures Report

  • ProductSpecificationRestService - Product Specification Record Failures Report

  • TaskRestService - Task Record Failures Report

  • SiteRestService - Site Record Failures Report

  • SSORestService - Single Sign On Failures Report

  • SupplierRestService - Supplier Record Failures Report

  • UrgentItemsRestService - Urgent Item Record Failures Report

  • UserRestService - User Record Failures Report

  • externalUserDetails - Soap Reporting Single Sign On Failures Report

  • getProductSpecificationV1 - Soap Product Specification Record Failures Report

  • ProductServiceImplService - Soap Product Record Failures Report

CR61 Supplier Attachments

Based on Attachments in the Supplier record, the report searches Suppliers where the specified filters are met, and returns a list of Attachment details.

If no attachments are found for a Supplier, no result is returned for that Supplier.

CR62 Site Attachments

Based on Attachments in the Site record, the report searches Sites where the specified filters are met, and returns a list of Attachment details.

If no attachments are found for a Site, no result is returned for that Site.

CR63 Specification Attachments

Based on Attachments section of all types of Specification, the report searches Product Specifications where the specified filters are met, and returns a list of Attachment details.

If no attachments are found for a Specification, no result is returned for that specification.

CR64 BWS Product Characterisation Statements

Three versions: a) BWS Product Characterisation Statements (Country of Origin); b) BWS Product Characterisation Statements (Age/Vintage); c) BWS Product Characterisation Statements (Additional Information).

The report is based on the Product Characterisation section for BWS Specs, and finds any Specifications which meet the specified filters, including Product Country of Origin.

CR65 BWS Ingredient Composition & Origin

The report is based on the Product Composition tab in BWS Specifications, and finds any Specifications which meet the specified filters, including Ingredient and Ingredient Country.

CR66 Packaging Recycling Icons

The report is based on the Recycling Icons table, in the Packaging section of all types of specification (except Produce), and finds any specifications that meet the specified filters, including the Recycling Icon Text.

CR67 Product Shelf Life (FNF)

Based on the FNF Storage section, the report searches Product Specifications where the Supplier or Business Category matches the specified value.

The report lists details from the Stability table.

Can search for multiple suppliers or business categories.

CR68 Produce Secondary Sites

The report searches Produce Product Records for those containing any Secondary Sites for specified Functions or Countries.

CR69 Produce Treatments & Allergens

The report searches Produce Product Records for those containing any specified Post Harvest Treatments or Allergens.

CR70 Site Sourcing Details & Growers

Based on the Sourcing Details tab in Site records, the report returns Sourcing and Grower List details for Sites meeting the specified filter values.

CR71 Counter Ticket Details

Two versions: a) Counter Ticket Details; b) Counter Ticket Details Additional Details.

The report is based on the Counter Ticket section, and finds any Specifications meet the specified filters, including Product Country of Origin.

CR72 Specification Summary – Line Level Data

The report is based on the Main Details section for all specification types, providing summary line data for specifications meeting the specified values, including Status, Spec Type and Business Categories.

CR73 Active Products Summary – Line Level Data

The report is based on the Product Record details for all active Product Records (including Produce), providing summary line data for products meeting the specified values, including, Spec Type and Business Categories.

CR74 New Sites

The report lists Site records with a Created On date that is newer than a specified period from the date the report is run, using a relative date filter.

CR75 Site Audit Summary

The report provides a high-level summary of Site records, showing details from the Last and Next Audits of each audit/visit types.

The report shows multiple rows per site, dependent upon the number of Audit/Visit Types against the site. For each Audit/Visit Types:

  • The last audit/visit is based on the latest change history Changed On date from the audit details where the status is Completed, Failed or Abandoned.

  • The next audit/visit is based on the earliest change history Changed On date where the status is Scheduled, In Progress, Awaiting Corrective Action, Awaiting Sign-off or Awaiting Amendment.

A status of Not Progressed is ignored.

CR76 Temporary Specifications

The report is based on Temporary Specifications for all specification types.

Data is only returned for specifications that have one or more Temporary specifications.

Suggested Filters include the Temporary specification’s Effective From and Effective To Dates.

CR77 Advanced Packaging Components

Based on the Advanced Packaging section, the report searches Product Specifications (all types) for those containing any Packaging Components with specified attributes, for example specified Suppliers, Countries of Origin, or Materials.

Can search for multiple suppliers, countries, or materials, locating specifications that match the specified selections.

CR78 Advanced Packaging Recycling Advice

Report is based on the Recycling Advice table, in the Advanced Packaging section of all types of specification, finding any that meet the specified filters, including the Recycling Advice Icon.

CR79 Advanced Packaging Other Recycling Icons

Report is based on the Recycling Icons table, in the Advanced Packaging section of all types of specification, finding any that meet the specified filters, including the Recycling Icon Text.

CR80 Advanced Packaging Additional Info

Based on the Additional Packaging Information tab, in the Advanced Packaging section for all specification types, the report searches Product Specifications for responses to specific questions.

The report output is capable of being pivoted, to show the questions as columns rather than rows, using soft coded columns and the use of quotation marks.

Since the questions are configurable per portal, the core report output will show the questions as rows, but clients may build their own pivoted variants of the report using the soft-coded columns.

User can search on multiple questions in a single report.

CR85 Sites with Audit-Visit

The report lists Audits/Visits of a specified type, by Site.

The search can be filtered to only include Audits/Visits at a particular status. If searching for completed Audits/Visits, a further refinement can be made using the completion date – for example, to list sites that have an Ethics audit, which has been completed in the last 12 months.

This report can be used as a basis for determining which active sites do not have the specified type of Audit/Visit. This is achieved by running a separate report, using the Sites data source query, to generate a list of active sites. The two outputs can then be crosschecked in Excel, or similar tool, to determine which sites appear in the active list, but not in the list of those with a completed Audit/Visit.

CR87 Food Other Labelling Copy

The report is based on the OLC section of the Food Specification. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the Other Labelling Copy data source query. Tabular data will not be included, other than for the Shipping Case Codes, EAN/Barcodes, and Catch Weight Increments fields.

Two alternative versions of the query/data source are provided, which output separate rows for the contents of the EAN/Barcode and Shipping Case Code tables, for the creation of custom variants of the report.

OLC EAN Barcodes: A row per entry in the EAN/Barcodes table, with any Shipping Case Code values concatenated together and repeated for each row.

OLC Shipping Case Codes: A row per entry in the Shipping Case Codes table, with any EAN/Barcodes values concatenated together and repeated for each row.

CR88 BWS Other Labelling Copy

The report is based on the OLC section of the BWS Specification. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the Other Labelling Copy data source query. Tabular data will not be included, other than for the Shipping Case Codes, EAN/Barcodes, and Catch Weight Increments fields.

Two alternative versions of the query/data source are provided, which output separate rows for the contents of the EAN/Barcode and Shipping Case Code tables, for the creation of custom variants of the report.

OLC EAN Barcodes: A row per entry in the EAN/Barcodes table, with any Shipping Case Code values concatenated together and repeated for each row.

OLC Shipping Case Codes: A row per entry in the Shipping Case Codes table, with any EAN/Barcodes values concatenated together and repeated for each row.

CR89 FNF Other Labelling Copy

The report is based on the OLC section of the FNF Specification. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the Other Labelling Copy data source query. Tabular data will not be included, other than for the Shipping Case Codes and EAN/Barcodes fields.

Two alternative versions of the query/data source are provided, which output separate rows for the contents of the EAN/Barcode and Shipping Case Code tables, for the creation of custom variants of the report.

OLC EAN Barcodes: A row per entry in the EAN/Barcodes table, with any Shipping Case Code values concatenated together and repeated for each row.

OLC Shipping Case Codes: A row per entry in the Shipping Case Codes table, with any EAN/Barcodes values concatenated together and repeated for each row.

CR90 CNF Other Labelling Copy

The report is based on the OLC section of the CNF Specification. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the Other Labelling Copy data source query. Tabular data will not be included, other than for the Shipping Case Codes and EAN/Barcodes fields.

Two alternative versions of the query/data source are provided, which output separate rows for the contents of the EAN/Barcode and Shipping Case Code tables, for the creation of custom variants of the report.

OLC EAN Barcodes: A row per entry in the EAN/Barcodes table, with any Shipping Case Code values concatenated together and repeated for each row.

OLC Shipping Case Codes: A row per entry in the Shipping Case Codes table, with any EAN/Barcodes values concatenated together and repeated for each row.

CR91 Produce Other Labelling Copy

The report is based on the OLC section of the Produce Specification. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the Other Labelling Copy data source query. Tabular data will not be included, other than for the EAN/Barcodes and Catch Weight Increments fields.

An alternative OLC EAN Barcodes version of the query/data source is provided, which outputs separate rows for the contents of the EAN/Barcode table, for the creation of custom variants of the report.

CR92 OLC Printer Details

The report is based on the OLC section of any specification type. It contains any specifications which meet the filter criteria entered by the user. Filters may be based on any fields available in the OLC Printer Details data source query.

Blank rows in the Printers table (those where the Printer Name mandatory field is missing) are omitted from the report.

Note:

Reports CR38 and CR39 are superseded by report CR85.

Reports Layout and Formatting

This section describes the common rules applied to the core reports, in terms of content layout and formatting. Further information on the styling of the core reports can be found in Appendix: Designing RTF Templates.

The following topics are covered in this section:

Multiple Rows and Concatenation in Tabular Reports

Depending on the level of granularity of the report, occurrences of multiple rows of data will either be output as separate rows, or concatenated into single cells.

For example, if producing a list of specifications that have a particular allergen present, a specification with two size variants (100g and 500g), both of which are manufactured in two different sites, the report would typically contain a row for each variant, each with the details of the two sites concatenated.

A report listing ingredient details where the level of granularity is ingredient, will output a row representing the ingredient in the Recipe table, with its associated details from the Raw Materials table being concatenated within their columns (as the ingredient to raw material relationship is one-to-many). If the level of granularity of the ingredients report was raw material, then a row would be output for each row in the Raw Materials table.

Where columns have contents concatenated, the separator is a comma, and if any item in the list is blank or null, it is replaced with the text Unknown, otherwise blank or null values show as blank in the report.

Suppliers

Suppliers are output as separate columns for code and name. A column containing the concatenated code and name is available to add as an optional additional column, and may be used where a report is grouped by supplier.

Sites

Sites are output as separate columns for code and name. A column containing the concatenated code and name is available to add as an optional additional column, and may be used where a report is grouped by site.

Where a specification has multiple specification level sites (those in the Main Details section), they are concatenated, alpha sorted by code, so that the concatenated codes and names correspond.

Where a specification has multiple specification section level sites (those in the Section Coverage section), they are output as separate rows.

Retailer Contacts

For the core reports, retailer contact details are output as separate columns containing Product Technologist names and Other Contact names. Multiples are concatenated, alpha sorted by name, so that if email or role columns are added the order corresponds.

Additional columns will be available for Contact Email and Contact Role. There may be up to three other retailer contacts configured for use, such as (such as Buyer and Product Development Manager); these columns will be soft-coded (see Soft-Coded Columns). A column will contain Contact Name and Contact Email concatenated.

Business Categories

Business Categories are output as separate columns for each level (the portal may have up to five levels configured). Multiples are concatenated, alpha sorted by the first level, so that the concatenated levels correspond. Reports contain separate columns for Lead and Other business categories, for where a portal is configured to use a separate single lead category and a list of other categories.

A column containing the full path is available to add as an optional additional column, and may be used where a report is to be grouped by business category.

Where Business Category appears as a default field in a core report, it is the full path. Where listed as an additional field that may be added, either the full path or individual levels can be used.

Nutrients

Nutrients are output as separate columns for nutrient description and unit. The values are those that appear in the Nutrition section data entry table. The nutrient's name and abbreviated name are available to add as optional additional columns.

Product Coverage Table

Where a product specification has multiple entries in the product coverage table, a separate row is output for each, with separate columns for product name, product number and quantity.

Multi-Specification Section Coverage

If a specification has multiples of a section, each occurrence that meets the selection criteria will be included.

The section's Section Name & Differentiator, and details of the Sites and/or Products that it covers will be shown in the Section Coverage columns, as shown in the following scenarios:

  • Specification has two Recipe sections, each contains the selected ingredient. The section name is concatenated with any differentiator, such as (v2) in this example:

    This graphic shows Multi-Specification Section Example 1.

  • Multi-pack specifications may have separate sections for each component, with sections named accordingly:

    This graphic shows the Multi-Specification Section Example 2.

  • A multi-site specification has two Recipe sections, one for each of its sites:

    This graphic shows Multi-Specification Section Example 3.

    This scenario, with multi-site-specific sections, is the only time sites cause the rows to be repeated.

  • A multi-size specification has two Recipe sections per variant (the main recipe and an alternative), resulting in a separate row for each:

    This graphic shows Multi-Specification Section Example 4.

  • A multi-size specification has two Recipe sections - one relates to one product variant, the other relates to two:

    This graphic shows Multi-Specification Section Example 5.

  • The Products Covered column concatenates as: [product name] ([quantity]) - [product number] : [alternative product number]. If it contains multiple variants, each set of values is contented with a comma (as in the above example).

Linked Records

Project Activities may be linked to certain other types of record within Brand Compliance, such as an activity to activate a Product Specification or to complete an Audit.

When records that are linked to a project are opened within the application, a Project Links page appears containing a list of any projects the record is linked to. Table A-2 shows the details available for reporting project record links.

Table A-2 Project Activity Linked Records

Linked Record Fields Available for Reporting

Product Record

Product Code, Product Title, Product Number, Alternative Product Number, Division

Specification

Spec Number, Spec Version, Spec Name

Audit/Visit

Supplier Code, Site Code, Audit Template, Due Date

Scorecard

Supplier Code, Site Code, Scorecard Type, Due Date

Site

Supplier Code, Site Code, Site Name

The link values are concatenated to give a single comma-separated set of values per Activity (or per Project, depending on granularity).

For the Product Record's product coverage details, the record links are multiplied by the number of products covered; this multiplication then gets rolled up so that it appears there are multiple Product Records for a given Activity, for example: Product Record 0001 (100g bag, 100g, 23), Product Record 0001 (25g bag, 25g, 24).

Note:

Where a report has Linked Project fields available to be included as additional columns, the available fields are Project Id, Project Name, Activity Id, and Activity Name (the name of the associated activity template).

Soft-Coded Columns

The soft-coded columns facility is provided to cater for the reporting of values as columns, where the number of columns is variable based on how the system has been configured. The areas initially covered by soft-coded columns are Nutrients, Dietary and Allergy Questions, and Retailer Contacts.

An example of the use of soft-coded columns would be for a report of product information that has a column for each nutrient, showing each product's value for the nutrients. As the list of nutrients is configurable and dynamic to the type of product, it is not feasible to design a report where the columns are hard-coded for specific nutrients, or to have a variable number of columns to cater for an unlimited number of nutrients. The soft-coded columns are available as a separate set of columns available for selection in the related data sources.

Nutrients

The Nutrition section of the Product Specification provides the nutritional definition of the product, based on the content values per nutrient. The list of nutrients is fully configurable.

When selecting columns in the Data Source record for a query that includes Nutrient values, they will appear as: {spec type} - {nutrient name} - {unit} (where {unit} identifies the unit of measure). The soft-coded columns to be provided per Nutrient are as follows (each column appears once per unique Nutrient glossary code):

  • Nutrient name

  • Unit

  • Per 100 value (raw)

  • Per serving value (raw)

  • Per 100 value (on pack)

  • Per serving value (on pack)

  • Per 100 value (as prepared)

  • Per serving value (as prepared)

  • % RI per 100

  • % RI per serving

  • % RI as prepared

  • Reference intake

  • Declared

  • Source of data

In the XML file generated by the query, the tag names of the nutrient related columns identify the specification type, nutrient and unit, for example:

<result>
    <SPEC_NUMBER>200123</SPEC_NUMBER>
    <SPEC_VERSION>1</SPEC_VERSION>
    <SPEC_TITLE>Breakfast Cereal</SPEC_TITLE>
    <SPEC_NUMBER>200123</SPEC_NUMBER>
    <FOOD_ENERGY_KJ_PER_100>1422</FOOD_ENERGY_KJ_PER_100>
    <FOOD_ENERGY_KJ_PER_SERV>1706</FOOD_ENERGY_KJ_PER_SERV>
    <FOOD_ENERGY_KJ_DECLARED>1422</FOOD_ENERGY_KJ_DECLARED>
    <FOOD_ENERGY_KCAL_PER_100>337</FOOD_ENERGY_KCAL_PER_100>
    <FOOD_ENERGY_KCAL_PER_SERV>404</FOOD_ENERGY_KCAL_PER_SERV>
    <FOOD_ENERGY_KCAL_DECLARED>337</FOOD_ENERGY_KCAL_DECLARED>
    <FOOD_FAT_G_PER_SERV>5.23</FAT_G_PER_SERV>
    <FOOD_FAT_G_PER_SERV>6.28</FAT_G_PER_SERV>
    <FOOD_FAT_G_DECLARED>6.28</FAT_G_DECLARED>
    ...
</result>

Dietary and Allergy Questions

The Dietary and Allergy section (Additional Product Info in the case of non-food) of the Product Specification provides the dietary and allergy definition of the product, based on the responses to sets of predefined questions - such as is the product suitable for vegetarians, or does the product contain egg. The list of questions is fully configurable.

For building reports that present the questions as dynamic columns, when selecting columns in the Data Source record using the Dietary & Allergens Section query, the soft-coded columns will appear as: {prefix value} - {specification type} - {question type} - {question} (where {prefix value} identifies the type of field, and {question type} identifies the question as an Allergens, Contains, or Suitable For type). Figure A-1 shows an example.

Figure A-1 Select D&A Question Columns

This figure shows the Select D&A Question Columns.

Where a question has multiple versions, the results will be collated into the same report column (based on the question code, specification type and question type).

The soft-coded columns provided per question are as follows (each column appears once per unique Dietary and Allergy/Additional Product Info glossary code):

  • Present in product

  • Present in the same plant

  • Present on the same line

  • Risk of cross contamination

  • Formal control program

  • Declared

  • Label declaration

  • Specify declaration

  • Source

In additional to the above D&A question fields, the query will return:

  • Spec Name

  • Spec Number

  • Spec Version

  • Spec Status

  • Spec Type

  • Product Coverage - Product Name

  • Product Coverage - Product Number

  • Product Coverage - Quantity

  • Product Coverage - Additional Product No.

  • Product Coverage - Division

  • Brand

  • Business Category

  • Lead Business Category

  • Supplier Code

  • Supplier Name

  • Site Code

  • Site Name

  • Technologist

  • Other Contacts

  • Section Coverage - Section Name

  • Section Coverage - Sites Covered

  • Section Coverage - Products Covered

In the XML file generated by the query, the tag names of the question related columns identify the specification type and the question type:

<result>
    <SPEC_NUMBER>200123</SPEC_NUMBER>
    <SPEC_VERSION>1</SPEC_VERSION>
    <SPEC_TITLE>Breakfast Cereal</SPEC_TITLE>
    <SPEC_NUMBER>200123</SPEC_NUMBER>
    <ALLQ_FOOD_ALLERGENS_GLUTEN_DECLARED_VAL>Yes</ALLQ_FOOD_ALLERGENS_GLUTEN_DECLARED_VAL>
    <ALLQ_FOOD_ALLERGENS_GLUTEN_PRESENT_IN_PRODUCT>No</ALLQ_FOOD_ALLERGENS_GLUTEN_PRESENT_IN_PRODUCT>    <ALLQ_FOOD_ALLERGENS_GLUTEN_SPECIFY>No</ALLQ_FOOD_ALLERGENS_GLUTEN_SPECIFY>
    ...
</result>

Retailer Contacts

The Retailer Contacts table within the Main Details section of the Product Specification defines the technologist and other retailer contacts responsible for the specification. The other contact roles are configurable (up to three roles), the core defaults being Buyer and Product Development Manager. Therefore, Technologist is a fixed column and the other contacts are soft-coded columns.

When selecting columns in the Data Source record for a query that includes retailer contacts, the column selector will include columns for:

  • Technologist - a single name of the associated Technologist.

  • Other Contacts - a concatenated list of the names of the other contacts (in alphabetical sequence).

  • Other Contacts - {role} - separate rows for each of the configured other contacts containing the name of the associated contact, where {role} is the contact role description. It is possible that multiple users may be present for the same role.

Figure A-2 shows an example with two other contacts: Buyer and Product Development Manager.

Figure A-2 Select Retailer Contact Columns

This figure shows the Select Retailer Contact Columns.

The following additional Retailer Contact columns will be available to add when designing a report:

  • Technologist Email - a single email address of the associated Technologist.

  • Contact Email - a concatenated list of the email addresses of the other contacts (the sequence corresponds to the list of names in the Other Contacts name column).

  • Contact Role - a concatenated list of the roles of the other contacts (the sequence corresponds to the list of names in the Other Contacts name column).

In the XML file generated by the query, the tag names of the other contacts identify the role:

<result>
  <SPEC_NUMBER>200123</SPEC_NUMBER>	
  <SPEC_VERSION>1</SPEC_VERSION>
  <SPEC_TITLE>Breakfast Cereal</SPEC_TITLE>
  <SPEC_NUMBER>200123</SPEC_NUMBER>
  <TECHNOLOGIST>Jane Doe</TECHNOLOGIST>
  <OTHER_CONTACTS>Joe Bloggs,John Smith</OTHER_CONTACTS>
  <OTHER_CONTACT_BUYER>Joe Bloggs</OTHER_CONTACT_BUYER>
  <OTHER_CONTACT_PRODUCT_DEVELOPMENT_MANAGER>John Smith</OTHER_CONTACT_PRODUCT_DEVELOPMENT_MANAGER>
  ...
</result>