12 Rolling Up Compliance Data Using External Rollup

The External Rollup solution revolves around the idea of extracting PG&C data from Agile database into an external aggregation engine; rolling it up and updating it back to Agile database.

Prior to this release, Agile PG&C allowed two types of rollups: A rollup that could be done at will with the Calculate Compliance button, and a scheduled rollup that works more like a nightly task; these are the internal rollups described in the previous chapter. The External Rollup feature of release 9.3.3 aims to achieve the same level of functionality using your company's own rollup or aggregation engine.

The 9.3.3 solution works as outlined below:

A customer gathers compositions for Items (manufacturer parts) from suppliers, then adds the items to product BOMs and attaches a REACH specification at the product level. The REACH specification is marked "External" for external rollup and the product BOMs as "Shippable". A scheduled external rollup task when started scans the PG&C database for records of BOMs that need to be rolled up and collects them. The database is further scanned for compositions at the leaf nodes of the BOM.

Another simpler approach is introduced, which extracts any item that has a specification marked external. Data is sent to external rollup tables and a rollup is performed. Here we ignore the "Is Shippable" flag and only check if a specification is attached that is marked as "External". Be it an item or a product BOM, it will get rolled up in the external rollup.

All of this data gets extracted to some tables in the Agile database; but in a new tablespace. Data transformations are performed on this dataset residing in the external rollup tables to help the rollup or aggregation engine do rollups. Once the data is ready, the external rollup or aggregation engine starts the rollup task. The dataset gets locked till the rollup is over or a flag based approach is used to keep data consistency between Agile and external rollup tables. All business logic (REACH regulation related logic) required for the rollup is executed as PL/SQL scripts with the rollup. Customers can modify the scripts or use a custom rollup engine on the external rollup tables to do rollups.

On completion of the rollup, the results are written in to external rollup tables. The results are then pulled in to PG&C-related tables through server or PX code to mark the items or product BOMs compliant or non-compliant against the specification. If custom attributes are involved in rollup, customers can execute custom code independent of the rollup task against external rollup tables to retrieve and update results into Agile.

Note:

To avoid bottlenecks around pulling data back in to Agile, customers with huge amounts of substance data and high volume of compositions to be created, can bring the flags (Compliant, Non-Compliant, etc.) back to Agile leaving the real substance level details in external tables and use a URL PX on the Item/Manufacturer Part to show the substance of the active composition.

This release provides new SDK APIs for customers to implement a process extension (PX) to extract data for a given product BOM and its compositions. These APIs can invoke the code on the server (the same code that executes the scheduled server task) that extracts the data, push it to the external database, and run a rollup. Once the rollup is completed, the results can be updated back on to the product BOM by SDK API.

Refer to Appendix D, "Reference Documentation for External Compliance Rollups"for the logic used in the external rollups.

12.1 External Compliance Rollups

External rollups provide a solution to deliver REACH compliance data.

Unlike the internal rollup engine, which cannot be modified, the external rollup engine is a PL/SQL script shipped with Agile schema; customer can follow the data schema and modify this with their own rollup logic.

Three actions which trigger the external rollup are described below.

12.1.1 Actions Triggering External Rollup

Three types of actions trigger the external rollup:

External Rollup Task: Configured in Java Client > Admin tab > Server Settings > Task Configuration > External Rollup Task. When the task is triggered the server calls the external rollup engine to search out all objects which need to do external rollup. [Object with external specification and its Need Compliance Check flag is 'Yes']
On Demand: This works the same as internal rollup, with the user clicking the Calculate Compliance button or routing Declaration Status from 'Open to Supplier' to 'Submit to Manager'. If there is an external specification associated to the object or added to declaration, the server triggers the external rollup engine to do rollup for this object. Logic described in the previous chapter on Internal Rollups applies.
Composition Rollups: Occurs within declarations having an external specification whenever the declaration changes status (except from 'Pending' to 'Non-Released' status). Also, at the end of importing compositions into items or manufacturer parts, the system starts the rollup.

12.1.1.1 Archived External Compositions

Number of Archived External Compositions is a parameter set in Admin > Server Settings > Preferences used to limit the number of archived external compositions.

If users run external rollup (by task or On demand) while creating new external compositions, the server checks the value of 'Number of Archived External Compositions' for each external specification. If the Archived External Compositions number exceeds the admin setting, the oldest (based on last update time) external composition is deleted.

This setting is described in more detail in Number of Archived External Compositions Preference in Appendix A, "Configuring Product Governance & Compliance".

12.2 Manual External Rollup using Excel Add-In

And additional manual method of using external rollup, the concept and the logic behind external rollups is described in detail in Appendix D, "Reference Documentation for External Compliance Rollups."

12.2.1 Excel Add-In Use Case

Here's a high level view of how a user performs an external rollup using the the Excel Add-In:

Open the Excel Rollup template and log in to the server.
Search for a part (three search options: Latest Released Rev, Released Rev and Pending Rev) or a manufacturer part, and selects the external specification, then loads the data.
Click Rollup the Data in the spreadsheet and get compliance status.
Click Submit Data, which updates the Calculated Compliance in the specification in PG&C.