Skip Headers

Agile Product Lifecycle Management Product Governance and Compliance User Guide Release 9.3.3 E39296-01 |
| Contents

Previous |

# D External Rollups

This appendix includes more details about External Rollups, which were created to deliver REACH compliance. Unlike regular internal rollups, which are build into Agile, the external rollup task can be modified by users (see Modifying the External Rollup Script).

Excel rollups occur automatically via rollup tasks or actions from the user, such as workflowing a declaration to the 'Open to Supplier' status, but they also are performed manually via the Excel Add-In.

## D.1 Rollup Tasks

As part external rollups, a new server task called External Rollup Task has been created in Admin > Server Settings > Task Configuration. This task operates independently from the internal rollup task. It must be configured to set the time of type of nightly rollup. Actions by the user in the PG&C user interface trigger declaration release and compliance calculation.

For nightly rollups, a server task collects all the objects that need rollup and does compliance rollup on all of them. It executes at the time set by the administrator. When the rollup is complete users receive a notification.

Calculate Compliance occurs as synchronous rollup based on the user selecting Calculate Compliance from the Actions menu.

Declaration Release rollup occurs when a user workflows a declaration to Released status.

For information on configuring the External Rollup Task, see Rollup Task Settings in Appendix A, "Configuring PG&C".

## D.2 Excel-Based Rollup as an Alternative to External Rollups

An alternative method to the automated external rollup is delivered via Excel-based rollup. This method is described in Excel for REACH Rollup later in this appendix.

## D.3 Defining Approach to Compliance Management

Compliance rollups can be managed by logic built into the Agile PG&C application; we use the shorthand "internal engine" for this approach to compliance management. The internal engine determines compliance at the lowest level within the top level object (part or top level assembly) such that it compares the amount of a substance within each sub-object (part or subpart) against the weight/mass of that sub-object. The rollup is then the worst case scenario of the compliance statuses of the individual sub-objects.

Examples:

- Compliance status of subparts rolls up to set compliance of a part.

- Compliance status of parts rolls up to set compliance of top level assembly.

Alternatively, compliance rollups can be managed by customer-originating systems; we use the shorthand "external engine" for this approach to compliance management. The external engine determines compliance for the top level object (part or top level assembly) such that it rolls up the amount of a substance from all of the sub-objects in the top level object and compares it to the weight/mass of that entire top level object.

Examples:

- Compliance status of a part is the total weight/mass of a substance in all the subparts compared to the weight/mass of the part.

- Compliance status of a top level assembly is the total weight/mass of a substance in all the BOM parts compared to the weight/mass of the top level assembly.

## D.4 Concepts

A few concepts in PLM (Product Lifecycle Management) and SCM (Supply Chain Management) are described below. It is important to understand these concepts, as they play a very crucial role in this proposed feature or solution for REACH rollup.

### D.4.1 Alternate IPNs or Items

In manufacturing world, it very common to have a two parts which are interchangeable while building a product. In other words, there are parts, which are of the same F3-form, fit and function where one can be used instead of the other.

Agile, today, does not have a very efficient or mandated way of handling this in a BOM structure. Each customer handles this scenario differently. A very common way of handling this is to put both parts on the BOM of the product and mark one part as alternate with a quantity zero.

### D.4.2 Alternate Manufacturer Parts

Agile allows customers to add more than one manufacturer part (MPN) to an item (IPN). These manufacturer parts are of the same F3 - form, fit and function. In production line or on shop floor at the manufacturing site of the product, these parts will get used interchangeably based on inventory and many other decision factors.

Considering the fact that this is a very common scenario in the industry, we have to take this into account while calculating compliance as any of one the attached manufacturer part can end up in the final product.

### D.4.3 Active Compositions or Declarations

A chemical composition of a given part is called a composition in PG&C. These are gathered using the object type called declarations. A part can have multiple compositions and more than one of them can be active.

If we were to generalize, two active compositions on the same part can differentiated by looking at which supplier and specification (an object used in PGC to capture compliance regulation thresholds on regulated substances) it belongs to.

Consider an example where a MPN - MPN0023 is being sourced from two different suppliers - ACME and EMS. Assume compliance data or compositions are being gathered for two regulations - REACH1 and REACH2. To gather compliance data (composition) two declarations can be raised, one for ACME and another for EMS, with REACH1 and REACH2 on the MPN - MPN0023. Upon supplier responses agile would have four compositions for the same MPN.

- REACH1 composition by ACME

- REACH2 composition by ACME

- REACH1 composition by EMS

- REACH2 composition by EMS

All the above unique compositions can be active at the same time and are considered while doing rollups.

### D.4.4 Manufacturer Part on Root Node of BOM (Product)

There are situations where customers associate a manufacturer part to the root node of an assembly. This is to say the product or the BOM is equivalent of the manufacturer part.

In PG&C if a manufacturer part is associated to a product or an assembly, we ignore all of its BOM and rollup the compliance information associated with the manufacturer part.

## D.5 External Rollup Flow

PL/SQL based REACH rollup solution focuses on enabling BOM and substance rollup in a set of external rollup tables. Multiple components join together to become the whole REACH rollup solution.

The External Rollup works by:

1. Extracting Objects that need rollup from the PG&C database (Extract Data PL/SQL)

2. Storing rollup data in staging database tables

3. Running compliance rollup using Rollup PL/SQL (Can be replaced with other rollup engines of choice that can work with tables)

4. Pulling results back in to Agile PG&C tables

These steps are explained more in the following paragraphs.

### D.5.1 Extracting Data

This component allows extraction and transformation of data from PG&C. The data is extracted at a database level to avoid any performance barriers of Java or Agile SDK.

Data extraction out of Agile is performed using PL/SQL procedures.

- This component resides at the database level as a stored procedure and should not be embedded in Agile server or java code.

- Your database administrator can export data out of PG&C database using this procedure by providing object identifiers (for example part number) that are visible in the UI.

#### D.5.1.1 Data Extraction Rules

Agile PLM stores multiple revisions of any given item. But, not all of them have to be retrieved for REACH rollup.

#### D.5.1.2 External Specifications

All items or products, Manufacturer Parts and Part Groups with one or more specification marked external are extracted.

#### D.5.1.3 Need Compliance Check Flag

Check if Need Compliance Check Flag is set.

#### D.5.1.4 Revisions

Only "Latest" and "Pending" revisions are extracted.

#### D.5.1.5 BOM, AML and BOS

For each item or product, their BOMs and or AMLs (corresponding Manufacturer Parts) and their Bill of Substances are extracted. If it is a single item, AML and Bill of substances are exported. If it is a product, the BOM, AML and Bill of substances are exported.

#### D.5.1.6 Mass

If the declared mass is not available no rollup can occur. When mass is not available on any given item, exclude the item from extraction into external rollup tables. RoHS rollup occurs the same as in prior releases.

A script will be provided to find the list of objects that were not successfully extracted to the staging table so that customers can fix the associated data problem and rerun the extract logic.

#### D.5.1.7 Compositions

PG&C allows multiple compositions for any given item or manufacturer part.

- Only "Active" compositions are extracted.

- Bill of substances are extracted - Materials, Subparts, Substance Groups, Substances, etc. Conversion is done during extract data.

#### D.5.1.8 Compliance Status Normalization

Normalize the 'Compliant', 'Non-Compliant' and 'Missing Info' flag data with the below given rules when extracting data to external rollup tables.

- If the declared compliance is marked as 'Compliant', treat this as '0' PPM.

- If the declared compliance is marked as 'Non-Compliant', treat this as threshold PPM + 1.

- For 'Missing Info', as per worst-case scenario, we will treat this as threshold PPM + 1 (as this is unknown, the worst case scenario is it can be non-compliant).

#### D.5.1.9 Order of Precedence

Follows the same logic that exists for internal rollups (9.3.1).

- Declared Compliance, Declared PPM, Calculated PPM (Mass of the item is required for this).

- For Substance Group without any declared compliance, declared PPM and declared mass value, use the values of declared PPM, declared mass for the child substances under the substance group. In this case, the order of precedence to calculate the mass of substance group will be the declared mass for the substance followed by the declared PPM of the substance. Declared Compliance value for the substance will be ignored.

#### D.5.1.10 Data Consistency

After the data is extracted, the 'Need Compliance Check' flag is updated to 'Processing'. After external rollup is completed, this flag will be set as 'No'.

Following is the existing logic to reset the external specification's 'Need Compliance Check' flag to 'Yes'

1. New composition(not external composition) is added

   - MDO Publish

     - Import a composition

     - MDO publish with PF (has part association)

     - Composition is Activated

2. A composition(not external composition) is archived/activated

3. Save as item/mfr part/PF with specification

4. Create new revision

5. Add a specification or set the declared compliance with the flag set to Yes

#### D.5.1.11 Calculate Compliance and Declaration Release

In external rollup, a server based queue mechanism allows multiple users to submit Calculate Compliance requests at the same time. Clicking Actions > Calculate Cpliance submits a job in the queue. Users can navigate to other objects without waiting for the job to complete.

Asynchronous rollup occurs, and records are processed using First In First Out method.

#### D.5.1.12 Modifying the External Rollup Script

This external rollup engine is a PL/SQL script shipped with Agile schema. Customers can follow the data schema and modify this with their own rollup logic.

To modify the PL/SQL script, contact Agile Support. The script will be shipped as a package, named agile_pgc_external_rollup.

### D.5.2 Database Tables

The staging database contains all the tables needed to store extracted data, to do rollup and to store the rollup results:

- FACT_TABLE - Record the leaf objects along with their compositions info including Qty, composition specification, disclosure type, etc. On demand and declaration release use this table. The data out of date will be deleted according to job id.

- FACT_TABLE_TASK - Same as FACT_TABLE but is only used for scheduled task type rollup. Considering performance data is truncated directly during the process.

- RESULT_OBJECT_COMPLIANCE - Record the top level objects and final rollup results (compliant or non-compliant) in this table. Each top object generates one extract id.

- RESULT_SUBSTANCE_COMPLIANCE - Record the detail rollup results for every extract id (substances related to external specification, calculated total weight & PPM & compliance result of each substance). The results data will be kept in this table for 31 days.

- ROLLUP_COMPOSITION - For each composition external rollup engine has met during the process, the engine will load the BOS structure and calculate the substance mass according to certain logic. Record these handled data in this table to cache the substance info of composition so that this table will never be deleted/truncated.

- ROOT_OBJECT - Record the info of top level objects and related external specification in current external rollup task.

- SPECIFICATION_SUBSTANCES - Record the external specification and substances & Threshold PPM defined upon it.

- tmp_rollup - While doing rollup the engine will pick up worst case for each leaf component object (MPN/BOM) and store substance SUM value of all leaf objects to this temporary table. It is used to simplify the rollup SQL and improve performance. Once the session ends it will be truncated by Oracle DB.

If your company chooses to customize its own external rollup engine, knowing all the fields in each table is necessary. The fields included in each table are listed on the following pages. The fields can be enabled using Java Client.

#### D.5.2.1 Fact Table

BOM and BOS data is reflected in the Fact Table. Composition information such as quantity, composition specification, disclosure type, etc. are included. Out of date data is deleted according to job id. On demand and declaration release use this table.

ROW_ID

JOB_ID

FACT_ID

COMPONENT_SEQ

COMPONENT_ID

COMPONENT_CLASS

COMPONENT_NUMBER

QUANTITY

COMPOSITION_ID

COMPOSITION_NUMBER

COMP_SPEC_ID

COMP_SPEC

COMP_SPEC_STATUS

COMP_FMD

#### D.5.2.2 Fact Table Task

Same as FACT_TABLE but is only used for scheduled task type rollup. Considering performance data is truncated directly during the process.

ROW_ID

JOB_ID

FACT_ID

COMPONENT_SEQ

COMPONENT_ID

COMPONENT_CLASS

COMPONENT_NUMBER

QUANTITY

COMPOSITION_ID

COMPOSITION_NUMBER

COMP_SPEC_ID

COMP_SPEC

COMP_SPEC_STATUS

COMP_FMD

#### D.5.2.3 Result Object Compliance Table

The table records the top level objects and final rollup results (compliant or non-compliant). Each top object generates one extract id. This data is reflected back as direct composition in PG&C.

EXTRACT_ID

JOB_ID

ROOT_OBJECT_ID

CHANGE_ID

SITE_ID

CLASS_ID

COMPOSITION_ID

SPECIFICATION_ID

COMPLIANCE_STATUS

FMD

UPDATE_TO_SERVER

JOB_TYPE

NOTES

CREATED

LAST_UPD

RESULT_OBJECT_COMPLIANCE_PK

#### D.5.2.4 Result Substance Compliance Table

Contents in this table are shown on Direct Composition > View Substances in Agile. The table records the detail rollup results for every extract id (substances related to external specification, calculated total weight & PPM & compliance result of each substance). The results data is kept in this table for 31 days.

ID

EXTRACT_ID

JOB_ID

SUBSTANCE_ID

CALCULATED_MASS

UOM"

CALCULATED_PPM

COMPLIANCE_STATUS

RESULT_SUBSTANCE_COMPLIAN_PK

RESULT_SUBSTANCE_COMPLIAN_FK1

RESULT_OBJECT_COMPLIANCE

#### D.5.2.5 Rollup Composition

For each composition external rollup engine has met during the process, the engine will load the BOS structure and calculate the substance mass according to certain logic. Record these handled data in this table to cache the substance info of composition so that this table will never be deleted/truncated.

COMP_ID

SUBSTANCE_ID

SUBSTANCE_NUMBER

SUBSTANCE_MASS

#### D.5.2.6 Root Object Table

Record the info of top level objects and related external specification in current external rollup task.

EXTRACT_JOB_ID

ID

ROOT_OBJECT_ID

ROOT_OBJECT_NUMBER

ROOT_OBJECT_CLASS

CHANGE_ID

REV

SITE_ID

SPECIFICATION_ID

SPECIFICATION

OBJECT_MASS

JOB_TYPE

ROOT_OBJECT_PK

#### D.5.2.7 Specification Substances Table

This table records the external specification and substances & threshold PPM defined upon it.

ID

SPECIFICATION_ID

SPECIFICATION_NAME

SUBSTANCE_ID

SUBSTANCE_NAME

CAS_NUMBER

LIFECYCLE_PHASE

THRESHOLD_MASS_PPM

REPORTING

SPECIFICATION_SUBSTANCES_PK

#### D.5.2.8 tmp_rollup Table

While doing rollup the engine will pick up worst case for each leaf component object (MPN/BOM) and store substance SUM value of all leaf objects to this temporary table. It is used to simplify the rollup SQL and improve performance. Once the session ends it will be truncated by Oracle DB.

JOB_ID

FACT_ID

SUBSTANCE_ID

SUBSTANCE_NUMBER

SPECIFICATION_ID

SUBSTANCE_MASS

PPM

### D.5.3 Staging Database

xxxx

#### D.5.3.1 Transformation Rules

The rules described below apply to the PL/SQL based rollup solution.

Once the data is extracted from agile, it has to be transformed or normalized.

- This component resides at the database level as a stored procedure.

- Your database administrator can export data out of PG&C database using this procedure by providing object identifiers (for example part number) that are visible in the UI.

The rules to be followed are:

a. All weights are converted to grams.

b. Each Rev (Latest Released or Pending) of an Item or a Product (root level assembly) and its BOM becomes an individual product.

#### D.5.3.2 Staging table Rules

a. At any given point in time, there are only product hierarchies affected by changes and their where used available in the staging tables.

b. For a fact change or BOM structure change, all data is wiped out and re-created in staging tables.

c. All items or product BOMs with a specification that are marked External are brought over when deploying new.

### D.5.4 Rolling up Data

A PL/SQL procedure rolls up the extracted object data and composition data from the staging database tables, and passes the data back to PG&C, where objects are updated. Objects that have been updated via External Rollup are noted as such. For example, in a Declaration on the Specifications tab, the new field Rollup Engine is updated with "External".

### D.5.5 Publishing Results

For new Compositions pulled back to PG&C:

- Compositions are marked "External Compositions" and were created using APIs as opposed to updating the database directly.

- Compliance Status/Flag is returned.

- Retain Substance Level Results are retained in External Rollup Result Tables.

- Substance Level detail in the UI is shown by using a URL process extension on objects.

## D.6 Excel Sample for REACH Rollup

The Excel rollup provides a manual, non-automated approach to REACH rollup described earlier in this appendix. Excel rollup has been enhanced to support REACH rollup through web services.

Excel for REACH rollup works as follows: Users with the certain privileges can extract compliance data for an item containing an External Specification to Excel, roll up compliance, then submit the results back to Agile.

This section provides a sample for REACH rollup, instructions for use, and logic used in the rollup.

The REACH rollup supports Excel 2003 and 2007 versions. The rollup is not supported on a single sign on environment.

You can find the rollup for download at samplecode.oracle.com in the Agile PLM section. Follow instructions on the site to download and install the rollup.

Before using the Excel sample, the user is expected to do a basic setup of Microsoft Office runtime and executing a registry file to add the web service URL of Agile PLM in to the local machine's registry. Check with your administrator if you need assistance.

Excel rollup works within the current framework of Agile PLM, so the user should have read and discover privileges on items, manufacturer parts, declaration, part groups, subparts, materials, substance groups, substances, composition tabs, etc. In addition, to submit the rollup status, the user must have modify privilege for the Declared Compliance attribute for the specification row that is selected for rollup (found on the Compliance tab).

Once the file is opened, you will see the standard Excel menus as well as an Agile menu under Add-Ins. The Agile menu includes the following options, some of which are disabled as you perform various tasks:

Login | Logs in to the Rollup add-in.
--- | ---
Search | Opens a search dialog which you can use to locate items or manufacturer parts.
Rollup | Rolls up the data. See REACH Rollup Logic for more information on the rollup logic.
Submit Data | Submits the calculated data in the Excel worksheets to Agile PLM
Clear Data | Removes all data in the Excel worksheets.
Logout | Logs out of the Rollup add-in.

### D.6.1 Logging Into Excel Rollup

In this section you will set up login for the Excel sample and configure the web service.

1. Open an empty Excel workbook. Notice the Agile PGC menu.

2. Select Login from the Agile PLM menu.

3. From the Login dialog box, click the Options... button.

4. Use the dialog box that displays to enter the web service URL. Enter the following in the URL field to set up the connection server:

   `http://bej301253.cn.oracle.com:7001/CoreService/services`

5. Click OK.

6. In the Login dialog box, enter admin as the User Name and agile as the Password, then click Log In. A successful login enables the Search, Clear Data and Logout functions. You can now begin using the rollup.

### D.6.2 Using Excel Rollup

After logging in, the user can use the sample to:

- Search for and select items or manufacturer parts.

- Select a declaration and an external specification for the declaration.

- Load a composition.

- Roll up the data.

- Submit the data to Agile PLM.

These steps are demonstrated below. Please note that the screen and report samples are for informational purposes only.

To use Excel Rollup:

1. Click Search from the Agile PGC menu.

2. In the Object Type drop-down, select one of the following:

   - Items (Latest Released Rev)

   - Items (Pending Rev)

   - Manufacturer Parts

3. In the Keyword field, enter search criteria, then click Search. Records matching the search criteria display in the Search Results section.

4. Select one row. The related external specification loads automatically in the Specification section.

5. Select one row of the loaded external specification. The related compositions load automatically in the Declarations and Compositions section.

6. Select a composition, then click the Load Data button to load the composition data to Excel. If you do not select a composition and click Load Data, all compositions are loaded and a confirmation message displays prompting you to load all compositions. Click OK.

7. An Excel worksheet containing is displayed. The selected data is represented on three tabs:

   - Rollup or FMD

   - Specification

   - Data - default selected sheet

8. To roll up the data, select Rollup from the Agile PGC menu. Compliance is calculated and displayed on the worksheet tabs.

9. To submit the calculated compliance to Agile, select Submit Data from the Agile PGC menu.

   For more information on how rollups are calculated, see REACH Rollup Logic.

### D.6.3 REACH Rollup Logic

This section describes the algorithms used for REACH rollup.

Threshold PPM is loaded from the specification.

The logic existing in the excel weight rollup is re-used to rollup the weight.

The rollup process has two sub-processes:

1. Mass rollup, followed by a BOS rollup

2. Comparison of weight percentage

#### D.6.3.1 Mass Rollup

The mass rollup process traverses the item's BOM/AML and for every parent it adds up the mass of its children.

The mass rollup process also adds the mass on every intermediate level assembly. The quantity of the leaf nodes and sub-assemblies on the BOM are taken into consideration while calculating the mass.

#### D.6.3.2 BOS Rollup

If there are substance groups or materials or subparts, the substances weights are converted using conversion factors.

Substances are not repeated. For example if lead is found on multiple items on the BOM, all their weights are added up and reported against the lead row in the table on the first worksheet.

Substances that are not tracked on the specification, but on the BOS of items/manufacturer parts/part groups, are totaled on to one line item - 'Substances Not Tracked'.

Notes:

- This process does not calculate substance weights on every intermediate level assembly; only on the assembly/item selected in the search.

- For MPNs with multiple compositions it uses the worst case; if all are equally bad then use the heaviest.

- For Items with multiple AML entries it uses the worst case; if all are equally bad then use the heaviest.

#### D.6.3.3 Compare Weight Percentage

REACH regulation states the weight of any given substance that is being tracked can only be 0.1% of the weight of the assembly/item.

Once the data has been rolled up, to calculate the compliance for each row of the substance the following logic is used:

- Compare the weight of the substance against the weight of the part/assembly and if it is above 0.1% weight of the part/assembly (Mass column), the substance row is non-compliant. Otherwise, the part/assembly is compliant.

- If the mass value of the part/assembly is missing, a rollup cannot be performed and an error message is issued to the user: 'Mass of the <partnumber> is missing. Please update it in agile. <partnumber> is substituted with the part/assembly number from top block.'