| Agile Product Lifecycle Management Product Governance and Compliance User Guide Release 9.3.3 E39296-04 |
|
 Previous |
| Agile Product Lifecycle Management Product Governance and Compliance User Guide Release 9.3.3 E39296-04 |
|
Previous |
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.
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 "Configuring Product Governance & Compliance."
An alternative method to the automated external rollup is delivered via Excel-based rollup. This method is described later in this appendix in "Excel Sample for REACH Rollup."
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.
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.
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.
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.
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 be 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.
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.
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:
Extracting Objects that need rollup from the PG&C database (Extract Data PL/SQL)
Storing rollup data in staging database tables
Running compliance rollup using Rollup PL/SQL (Can be replaced with other rollup engines of choice that can work with tables)
Pulling results back in to Agile PG&C tables
These steps are explained more in the following paragraphs.
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.
Agile PLM stores multiple revisions of any given item. But, not all of them have to be retrieved for REACH rollup.
All items or products, Manufacturer Parts and Part Groups with one or more specification marked external are extracted.
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.
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.
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.
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).
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 is ignored.
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'
New composition (not external composition) is added
MDO Publish
Import a composition
MDO publish with PF (has part association)
Composition is Activated
A composition (not external composition) is archived/activated
Save as item/mfr part/PF with specification
Create new revision
Add a specification or set the declared compliance with the flag set to Yes
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.
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 is shipped as a package, named agile_pgc_external_rollup.
The 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, and so on. 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 loads the BOS structure and calculates 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 picks up worst case for each leaf component object (MPN/BOM) and stores 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 is 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.
BOM and BOS data are 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
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
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
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
For each composition external rollup engine has met during the process, the engine loads 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 is not deleted/truncated.
COMP_ID
SUBSTANCE_ID
SUBSTANCE_NUMBER
SUBSTANCE_MASS
Records 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
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
While doing rollup the engine picks 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
The staging database contains tables required to store extracted data, and the rollup results.
The rules described below apply to the PL/SQL based rollup solution.
Once you extract the data from Agile, it must be transformed or normalized.
This component resides at the database level as a stored procedure.
The 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.
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.
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".
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.
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 2013, 2010, 2007, and 2003 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. |
In this section you will set up login for the Excel sample and configure the web service.
Open an empty Excel workbook. Notice the Agile PGC menu.
Select Login from the Agile PLM menu.
From the Login dialog box, click the Options... button.
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:
Click OK.
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.
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:
Click Search from the Agile PGC menu.
In the Object Type drop-down, select one of the following:
Items (Latest Released Rev)
Items (Pending Rev)
Manufacturer Parts
In the Keyword field, enter search criteria, then click Search. Records matching the search criteria display in the Search Results section.
Select one row. The related external specification loads automatically in the Specification section.
Select one row of the loaded external specification. The related compositions load automatically in the Declarations and Compositions section.
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.
An Excel worksheet containing is displayed. The selected data is represented on three tabs:
Rollup or FMD
Specification
Data - default selected sheet
To roll up the data, select Rollup from the Agile PGC menu. Compliance is calculated and displayed on the worksheet tabs.
To submit the calculated compliance to Agile, select Submit Data from the Agile PGC menu.
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:
Mass rollup, followed by a BOS rollup
Comparison of weight percentage
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.
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.
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.'
|
 Copyright © 2010, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|