| Oracle® Retail Brand Compliance Management Cloud Service Reports User Guide Release 18.0 E99003-04 |
|
 Previous |
 Next |
| Oracle® Retail Brand Compliance Management Cloud Service Reports User Guide Release 18.0 E99003-04 |
|
Previous |
Next |
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.
|
Note: Certain reports have predefined filters applied to include a specific specification type, such as:
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.
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 ingredients, and specify All or Any, where All will locate specifications containing all of the specified ingredients, and Any will 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. Can specify All or Any, where All will locate specifications containing all of the specified countries/ingredients, and Any will 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. Can specify All or Any, where All will locate specifications containing all of the specified ingredients from the suppliers, and Any will 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. Can specify All or Any, where All will locate specifications containing all of the specified allergens, and Any will 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. Can specify All or Any, where All will locate specifications containing all of the specific Suitable For questions being answered as specified, and Any will locate specifications that contain any of the specific Suitable For questions being answered as specified. |
|
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. Can specify All or Any, where All will locate specifications containing all of the specified nutrients with a matching min/max value, and Any will locate specifications that contain any of the specified nutrients with a matching min/max value. 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. Can specify All or Any, where All will locate specifications containing all of the specified nutrients which meet the target, and Any will locate specifications that contain any of the specified nutrients that meet the target. |
|
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. |
|
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. |
|
CR18 Surveillance Testing Requirements - Product Attributes (As Consumed) |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
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 |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
CR23 Surveillance Test Report Summary |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
CR24 Surveillance Results by Date Range |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
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. |
|
CR31 Site References (Certificates) - All Valid / Overdue by Type |
Two versions: a) Site References (Certificates) - All Valid/Overdue by 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). |
|
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. |
|
CR40 Produce Specification Acceptance |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
CR41 Produce Ingredients Country of Origin |
This report template has been released in error. This will be re-released in a future version, please do not use this template. |
|
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 |
|
|
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); 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. |
|
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:
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. Can specify All or Any, where All will locate specifications containing all of the specified nutrients with a matching min/max value, and Any will 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). |
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:
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.
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:
Multi-pack specifications may have separate sections for each component, with sections named accordingly:
A multi-site specification has two Recipe sections, one for each of its sites:
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:
A multi-size specification has two Recipe sections - one relates to one product variant, the other relates to two:
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).
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 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.
For building reports that present the nutrients as dynamic columns, when selecting columns in the Data Source record using the Nutrients query, the soft-coded columns will appear as: {prefix value} - {spec type} - {question type} - {question}
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.
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.
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>
