Go to primary content
Oracle Product Lifecycle Analytics Operational Data Reference Manual
Release 3.5
E70276-01
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

2 OPLA Staging Schema Data Model

This chapter includes information on the types of tables and columns in the OPLA Staging schema, including the naming conventions used.

Naming Conventions for OPLA Staging Schema Tables

OPLA Staging Schema Database table names follow a three-part naming convention - PREFIX_NAME_SUFFIX.

Standard Table Prefixes in Staging Schema Tables

The prefix indicates the part of the Agile PLM Database schema to which a table belongs.

Table 2-1 Standard Table Prefixes in Staging Schema Tables

Prefix Descriptions

ODM_

Operational Data Mart tables


Standard Table Suffixes in Oracle Agile PLM BI Tables

The suffix indicates the type of the table in the Agile PLM BI MDS schema.

Table 2-2 Standard Table Suffixes in Oracle Agile PLM BI Tables

Suffix Description

_MAP

Bridge tables that resolve multi-list attribute data.

_D

Dimension table for list attributes in PLM.

_TEMP

These are reserved temporary tables used by internal ETL process.

Note: Do not alter or delete these tables.

_CTL

Control tables


Table Types in OPLA Staging Schema

The Published Data Model comprises the following:

  1. "ETL Control Entities"

  2. "Metadata and Translation Entities"

  3. "Dimension Tables"

  4. Bridge Tables ("Map Tables")

  5. Data Entities:

ETL Control Entities

Table 2-3 ETL Control Entities

Entity Name Description

DM_LOAD_CTL

Stores ETL run type, status. Updated during ETL process and is Read-Only for others.

ODM_LISTDIM_CTL

Stores List dimension table information for lists.

ODM_LISTDIM_CTL_BKP

Used by configuration tool

PARAMETER

Table to store variables that control ETL process

VERSION

Stores Version information about PLM, OS, DB server, Datamart


Metadata and Translation Entities

Table 2-4 Metadata and Translation Entities

Entity Name Description

ODM_ATTR_METADATA

Table describes attribute metadata of Agile PLM objects.

ODM_CLASS_METADATA

Table describes class metadata Agile PLM objects.

ODM_FLEXCOLS_METADATA

Table describes flex attribute metadata for Agile PLM objects.

ODM_WORKFLOW_METADATA

Table describes metadata for all configured workflows and their process steps within Agile PLM.

ODM_DATA_DICTIONARY

Stores data dictionary for Data Mart table including source and target mapping details.


Dimension Tables

In Data Mart, dimension tables are created for different types of lists. The ODM_LISTDIM_CTRL table enables you to identify the dimension type.

The ODM_LISTDIM_CTRL table is as follows:

Table 2-5 ODM_LISTDIM_CTRL

IND_FLAG List Type

1

Normal Lists

2

Cascade Lists

3

Dynamic Lists

4

For a list no entry values in PLM


Table 2-6 Columns in Dynamic and Normal lists

ENTRY_ID Primary key

ENTRY_ID

Primary key

ENTRY_VALUE

Dimension member name

LIST_ID

Parent list ID

CREATE_DATE

Created On

UPDATE_DATE

Last Updated On

DELETE_FLAG

Identify whether this object is soft deleted. The value is 1 for soft delete.

LANG_ID

Language ID


Cascade List Dimension Entities

Table 2-7 Cascade List Dimension Entities

Column Comments

ENTRY_ID

Unique Identifier for dimension member

ENTRY_VALUE

Member Name

CASCADE_VALUE

Cascaded Path of the List Entries with Hierarchy

CASCADE_PATH

Cascaded Path of the Lists with Hierarchy

PARENT_ENTRY

Parent Entry of the Entry_ID

LEVEL0

Hierarchy of Entry_ID (Parent Entry ID)

LEVEL1

Hierarchy of Entry_ID (Child of LEVEL0 Entry_ID)

LEVEL2

Hierarchy of Entry_ID (Child of LEVEL1 Entry_ID)

LEVEL3

Hierarchy of Entry_ID (Child of LEVEL2 Entry_ID)

LEVEL4

Hierarchy of Entry_ID (Child of LEVEL3 Entry_ID)

LIST_ID

Agile PLM List ID

IS_LEAF

Identifier of the leaf node: 1-Yes 0-No

DELETE_FLAG

Identify if this is a deleted object: 1-Deleted 0-Not Deleted

LANG_ID

Language ID


The following table lists the dimension tables available in Data Mart:

Table 2-8 Dimension tables available in Data Mart

Entity Name PLM List ID Description

ODM_8704_CURRENCY_D

8704

Currencies

ODM_ABWF_ACT_DUR_D

2000010054


ODM_ABWF_ACT_RSC_D

2000010055


ODM_ABWF_PROC_PHS_D

200010057


ODM_ABWF_WRK_ITEM_D

2000010056


ODM_ACT_INACT_D

7117


ODM_ACTION_D

3876

Action List

ODM_ACTION_STATUS_D

18414

Action Status

ODM_ACTION_TYPE_D

201

Action Type List

ODM_ACTIVITY_RSRC_TYPE_D



ODM_ACTIVITY_STATE_D

19983

Activity State List

ODM_ACTIVITY_TYPE_OBJ_D



ODM_AGILEUSER_TYPE_OBJ_D

11605

Agile PLM User

ODM_ALLOW_ESCDEPAPP_D

3750


ODM_AML_D

11936

Site AML List

ODM_AML_LIST_D

12068

Site Specific Bom/AML List

ODM_AML_ON_BUY_D

11935

AML on Buy List

ODM_AML_PREF_STATUS_D

2249

AML Preferred Status

ODM_AML_STATUS_D

4559

AML_STATUS

ODM_APPROVE_GROUP_D



ODM_ATT_CHKOUT_STATUS_D

6396


ODM_ATTACH_LIFECYCLE_PHASE_D

6333

Attachments

ODM_ATTACH_TYPE_D

4682

Attach Type List

ODM_AUDIT_RESULT_D

6820

Audit Result

ODM_AUDIT_TYPE_OBJ_D

4428

Audit Type Object

ODM_BUYER_D

8934

Buyer

ODM_BUYER_RESP_SUPP_D

4590


ODM_CALC_COMPLIANCE_D

2000001019

Calculated Compliance

ODM_CALENDAR_LIBRARY_D

2000025603

Calendar Library

ODM_CAPA_TYPE_OBJ_D

4928

CAPA Object

ODM_CATEGORY10_LIST_D

2000000192

Category 10 List

ODM_CATEGORY7_LIST_D

2000000189

Category 7 List

ODM_CATEGORY8_LIST_D

2000000190

Category 8 List

ODM_CATEGORY9_LIST_D

2000000191

Category 9 List

ODM_CHANGE_ANALYST_D

730

Change Analysts

ODM_CHANGE_CATEGORY_D

411

Change Category

ODM_CHANGE_OBJ_D

8223

All Change Objects

ODM_CHG_ORDERS_TYPE_OBJ_D

6000

Change Orders Object

ODM_CHG_REQS_TYPE_OBJ_D

7000

Change Request Object

ODM_CHNG_FUNCTION_D

331

Change Function

ODM_COMMODITIES_ITEMS_D

12941

Commodities for Items

ODM_COMMODITY_TYPE_D

2000004868

Commodity Type

ODM_COMPLIANCE_MGR_D

2000004949

Compliance Manager

ODM_COMPONENT_ENGRS_D

750

Component Engineers

ODM_COMPOSITION_TYPE_D

2000007577

Composition Type

ODM_CONTINENT_D

4598

Continent

ODM_COST_STATUS_D

18781

Cost Statuses

ODM_COUNTRY_D

365

Country

ODM_CURRENCY_D

8704

Currencies

ODM_CUSTOMER_D

2000000107

Customer List

ODM_CUSTOMER_LIFECYCLE_PHASE_D

5799

Customers

ODM_CUSTOMER_OBJ_D

6505

All Customer Objects

ODM_CUSTOMER_TYPE_OBJ_D

4983

Customer Type

ODM_DATEFORMAT_D

4475

Date Formats

ODM_DECLARATIONS_OBJ_D

2000002155

All Declaration Objects

ODM_DECLARE_COMPLIANCE_D

2000001394

Declared Compliance

ODM_DEPENDENCIES_TYPE_D

18506


ODM_DEPENDENCY_TYPE_D

2000007820


ODM_DEVIATION_TYPE_D

737

DEV Type List

ODM_DISC_PRIORITY_LIST_D

18412

Discussion Priority List

ODM_DISCUSSION_OBJ_D

8229

All Discussion Objects

ODM_DISPOSITION_D

431

Disposition

ODM_DIVISION_D

2000000105

Division List

ODM_DOC_CATEGORY_D

321

Document Category

ODM_DOCUMENT_TYPE_D

739

Doc Type List

ODM_DOCS_TYPE_OBJ_D

9000

Document Objects

ODM_DURATION_TYPE_D

18792

Duration Types

ODM_ENCODE_TYPE_D

2000007049

Encoded Language List

ODM_EXEMPTIONS_D

2000001449

Exemptions

ODM_EXTRACTION_FORMAT_D

13045

Extraction Format

ODM_FILE_FOLDERS_OBJ_D

8220

All File Folder Objects

ODM_FILE_FLDR_TYPE_OBJ_D

6159

File Folder Type ojbect

ODM_FILE_PRDITY_PREF_D

2000011016


ODM_GATES_TYPE_OBJ_D

18387

Gates Objects

ODM_GERMAN_D

31123

German List

ODM_HISTORY_ACTION_D

4506

HistoryActionList

ODM_HIST_RPT_FF_TYPE_OBJ_D

6367


ODM_HISTORICAL_RPT_TYPE_D

7114


ODM_INCORP_STATUS_D

706

Incorporation Status

ODM_IPC_FILE_D

2000005842

IPC File Type

ODM_IPC_FORM_D

2000005839

IPC Form Type

ODM_IPC_LEGAL_D

2000007596

IPC Legal Definition Type

ODM_IPC_MSL_D

2000005847

IPC J-STD-020 MSL Rating

ODM_IPC_NUMBER_PROCESS_D

2000005846

IPC Number of Processing Cycles

ODM_IPC_SUPPLIER_D

2000007595

IPC Supplier Acceptance

ODM_IPC_TERMINAL_BASE_D

2000005845

IPC Terminal Base Alloy

ODM_IPC_TERMINAL_PLATING_D

2000005844

IPC Terminal Plating / Grid Array Material

ODM_IPC_UNIT_D

2000005843

IPC Unit Type

ODM_IPC1752_1_DECL_D

2000005841

IPC 1752-1 Declaration Type

ODM_IPC1752_2_DECL_D

2000005840

IPC 1752-2 Declaration Type

ODM_ITEM_CHANGE_FILTER_D

16914

Item Change Filter List

ODM_ITEM_FAILUREMODE_D

21620

Failure Mode List - [Part]

ODM_ITEM_LIFECYCLE_PHASE_D

1514

Items

ODM_ITEM_OBJ_D

8222

All Item Objects

ODM_ITEM_SITES_QUOTE_D

6043

Item_Sites Quote_As List

ODM_ITEM_STATE_D

4566

ITEM_STATE

ODM_ITMMREV_STATE_D

4558

ITEMREV_STATE

ODM_JGPSSI_UNIT_D

2000007462

JGPSSI Units

ODM_JGPSSI_Y_N_D

2000007463

JGPSSI Yes/No

ODM_JIG_LEVEL_D

2000011094

JIG Level

ODM_LANGUAGE_DISPLAY_D

4519


ODM_LAUNCH_YEAR_LIST_D

2000000108

Launch Year List

ODM_LAYOUT_ACCESS_D

7113


ODM_LAYOUT_PAPER_ORI_D

7118

Paper Layout

ODM_LAYOUT_PAPER_SIZE_D

7119

Paper Size

ODM_LIST_HISTORY_ACTION_D

44546


ODM_LIST_MAIN_TOOLBAR_D

2345

Main Toolbar Design

ODM_LIST_WF_EVENT_D

4457

Workflow Events List

ODM_LIST_WORKWEEK_D

5968

Days of the Week

ODM_LISTS_D

8819

Roles List

ODM_LOCATION_D

4542

Location

ODM_MAKE_BUY_D

12065

Make/Buy

ODM_MANUFACTURER_OBJ_D

8224

All Manufacturer Objects

ODM_MANUFACTURER_PART_OBJ_D

8225

All Manufacturer Part Objects

ODM_MANU_PARTS_TYPE_OBJ_D

1843

Manufacturer Parts Type Objects

ODM_MANUFACTURERS_TYPE_OBJ_D

1442

Manufacturers Type Objects

ODM_MASS_DISCLOSURE_D

2000011081

Disclosure Types List

ODM_MATERIAL_LIFECYCLE_PHASE_D

2000004147

Material

ODM_MFR_ORDERS_TYPE_OBJ_D

1450

MCO Objects

ODM_MFRPART_LIFECYCLE_PHASE_D

1515

Manufacturer Parts

ODM_MCO_TYPE_D

742

MCO Type List

ODM_MFR_LIFECYCLE_PHASE_D

1516

Manufacturers

ODM_MFR_TYPE_D

740

Mfr Type List

ODM_MFRPART_TYPE_D

741

Mfr Part Type List

ODM_NCR_TYPE_OBJ_D

4895

NCR Type Objects

ODM_NEWS_TYPE_D

18406

News Type List

ODM_NOTIFY_LIST_D



ODM_NUMBER_FORMAT_D

4452

Number Format List

ODM_ORGANIZATION_TYPE_OBJ_D

17570

Organization Type Objects

ODM_ORG_STATUS_D

4516

ORG_STATUS

ODM_ORG_SUPPLIER_D

4545

ORG_SUPPLIER_TYPE

ODM_ORG_TYPE_D

4515

ORG_TYPE

ODM_ORGANIZATION_OBJ_D

13555

All Organization Objects

ODM_OUTBOUND_RESPONSE_D

12353

Outbound Response List

ODM_OVERALL_STATUS_D

18820

Overall Statuses

ODM_OVERLAY_ICON_D

8974

Overlay Icon List

ODM_PART_CATEGORY_D

311

Part Category

ODM_PART_COMPLIANCE_D

2000002025

Part Compliance List

ODM_PART_FAMILY_COMM_OBJ_D

2000007001

All Part Family Commodity Objects

ODM_PART_FAMILY_OBJ_D

2000004418

All Part Family Objects

ODM_PART_FAMILY_TYPE_OBJ_D

2000004409


ODM_PART_TYPE_D

735

Part Type List

ODM_PARTFY_LIFECYCLE_PHASE_D

2000004283

Part Family

ODM_PARTS_TYPE_OBJ_D

10000

Parts Type List Objects

ODM_PKG_CATEGORY_D

412

Package Categories

ODM_PLANNER_D

8933

Planner

ODM_PR_DISPOSITION_D

5131

PR Disposition List

ODM_PR_TYPE_OBJ_D

4878

PR Type List Object

ODM_PREFERRED_CLIENT_D

13261

Preferred Client List

ODM_PREFERRED_INBOX_D

7121


ODM_PREFERRED_PRG_D

7123


ODM_PREFERRED_STA_PRG_D

7122


ODM_PRG_HIST_ACT_D

2000007020


ODM_PRICE_ADMIN_OBJ_D

8524

Price Administrators

ODM_PRICE_CHGORDERS_TYPE_OBJ_D

10516

Price Change Orders Type List Objects

ODM_PRICE_LIFECYCLE_PHASE_D

10897

Price

ODM_PRICE_OBJ_D

8523

All Price Objects

ODM_PRICE_SCEN_D

4597


ODM_PRICEPOINT_PRD_D

4565


ODM_PRIORITY_D

12868

Priority

ODM_PROD_LINE_D

2000000106

Product Line List

ODM_PRODUCT_LINES_D

291

Product Lines

ODM_PROGRAM_TYPE_D

2000000103

Program Type List

ODM_PROGRAMS_OBJ_D

8228

All Program Objects

ODM_PROJECT_LIFECYCLE_PHASE_D

10905

Projects

ODM_PROJECT_LINE_ITEM_D

4527


ODM_PROJECT_TYPE_D

4531


ODM_PSR_CATEGORY_D

5129

Problem Report Category

ODM_PSR_OBJ_D

8227

All PSR Objects

ODM_PSR_PR_PRODUCTLINE_D

5948

Product Lines(PSR)

ODM_PSR_SEVERITY_D

6629

PSR Severity List

ODM_PUB_ACTION_D

203

Publisher Actions List

ODM_PUB_PRICE_TYPE_OBJ_D

10321

Publisher Price Type List Object

ODM_QCR_CATEGORY_D

6819

QCR Category

ODM_QCR_OBJ_D

8226

All QCR Objects

ODM_QUALITY_ADMIN_OBJ_D

5198

Quality Administrators

ODM_QUALITY_ANALYST_OBJ_D

5199

Quality Analysts

ODM_QUALITY_STATUS_D

18783

Quality Statuses

ODM_QUOTE_HIST_TYPE_OBJ_D

10338

Quote History type list ojbects

ODM_READINESS_STATE_D

4591

Readines State

ODM_REASONCODE_D

351

Reason Code

ODM_REDLINE_TYPE_D

16917

Redline Type

ODM_REF_DESIGN_IND_D

2000007046

Reference Designator Range Indicator List

ODM_REGION_D

4599

Region/Province/State

ODM_REGION_LIST_D

2000000104

Region List

ODM_REGIONS_D

4518

Time zones of the Region

ODM_RELEASE_TYPE_D

4539

RELEASE_TYPE

ODM_REPOST_SCH_EXE_FQN_D

7116

Report scheduling

ODM_REPORT_SCH_STATE_D

7115


ODM_RESPONSE_D

4537

RESPONSE

ODM_REQ_LIST_D

3877


ODM_REQUESTS_QUOTE_D

16441

Requests for Quote

ODM_RESOURCE_TYPE_D

2000000748

Resource Type List

ODM_RESPONSE_BID_DECI_D

4541

Bid responses

ODM_RESPONSE_BID_STATE_D

4579

Bid response status

ODM_RESPONSE_EDITMODE_D

8211

Response Editing modes

ODM_RESPONSE_LIFECYCLE_PHASE_D

10915

Responses

ODM_RESPONSE_LINE_ITEM_D

4526

Response Line Item

ODM_RESPONSE_LINE_STATUS_D

4564

RESPONSE_LINE_STATUS

ODM_RESPONSE_STATE_D

4536

RESPONSE_STATE

ODM_RESPONSE_TYPE_D

4552

RESPONSE_TYPE

ODM_RESRC_STATUS_D

18782

Resource Statuses

ODM_REV_OBJECT_D

7090

REV_OBJECT

ODM_RFQ_LIFECYCLE_PHASE_D

10910

RFQ

ODM_RFQ_LINE_STATUS_D

4550

RFQ_LINE_STATUS

ODM_RFQ_OBJ_D

8521

All RFQ Objects

ODM_RFQ_RESPN_D

16635

RFQ Responses

ODM_RFQ_RESPONSE_OBJ_D

8522

All RFQ Response Objects

ODM_RFQ_STATE_D

4533

RFQ_STATE

ODM_RFQ_TYPE_D

4534

RFQ_TYPE

ODM_RLSHIP_TYPE_D

2000007913

Relationship types

ODM_ROHS_DEC_D

2000005848

RoHS Declaration

ODM_ROHS_EXEMPTION_D

2000007599

RoHS Exemption

ODM_ROLES_D

4523

ROLES

ODM_ROLL_UP_D

2000004142

Rollup Selection

ODM_ROW_PER_PAG_D

12884

Number of rows per page

ODM_SEARCHES_D

8821

Searches list

ODM_SEVER_LOC_D

13300

Server Location

ODM_SEVERITY_D

6504

Severity

ODM_SHIP_FROM_D

10311

Ship From

ODM_SHIP_LOCATION_OBJ_D

17501

Ship To Location List

ODM_SITE_CHGORDERS_TYPE_OBJ_D

11907

Site Change Orders type list objects

ODM_SITE_COLOR_D

13389

Colors list

ODM_SITE_ERP_D

11934

Site ERP List

ODM_SITE_LIFECYCLE_PHASE_D

11962

Sites

ODM_SITES_OBJ_D

13166

All Sites Objects

ODM_SITES_TYPE_OBJ_D

11760

Sites type list objects

ODM_SIZE_D

421

Size

ODM_SRC_PROJECT_OBJ_D

8520

All Sourcing Project Objects

ODM_SOURCING_PRJ_D

16433

Sourcing Projects

ODM_SPEC_LIFECYCLE_PHASE_D

2000004150

Specification

ODM_SPEC_VALIDATION_D

2000001531

Spec Validation

ODM_SPECIFICATION_OBJ_D

2000001991

All Specification Objects

ODM_STATE_D

353

State List

ODM_STATE_PROV_REGION_D

12645

State/Province/Region List

ODM_STATUS_LISTS_D

3921

Status Lists

ODM_STATUS_TYPE_D

3644

Status Type Selection

ODM_STOPSHIPS_TYPE_D

738

SS Type List

ODM_SUBSCRIBERS_D

12825

Subscribers

ODM_SUBSTANCES_OBJ_D

2000002154

All Substance Objects

ODM_SUBSTANCE_REPORT_D

2000002499

Substance Reporting

ODM_SUPP_OFFER_RAT_D

4522

Supplier Offering Rating

ODM_SUPPLIER_AML_STATUS_D

4560

SUPPLIER_AML_STATUS

ODM_SUPPLIER_GROUP_D

17669

Supplier Group Type

ODM_SUPPLIER_LIFECYCLE_PHASE_D

10901

Suppliers

ODM_SUPPLIER_RESP_STATUS_D

4562

Supplier Response Status

ODM_SUPPLIER_TYPE_D

4554

SUPPLIER_TYPE

ODM_SUPPLIERS_OBJ_D

17667

All Supplier Objects

ODM_SUPPLIER_OFFER_TYPE_D

17504

List of Supplier Offering Types

ODM_TIMEFORMAT_D

4476

Time formats

ODM_TIMEZONE_D

4474

List of TimeZone display strings

ODM_TRANSMISSION_STATUS_D

12823

Transmission Status List

ODM_TRANSPORT_TERMS_D

4587

Transportation Terms

ODM_UOM_D

2000001990

Units of Measure

ODM_USER_CATEGORY_D

9279

User Category

ODM_USER_GROUP_OBJ_D

13048

All User Group Objects

ODM_USER_GROUP_TYPE_D

12082

User Group Type List

ODM_USER_GROUP_TYPE_OBJ_D

12082

User Group Type Objects

ODM_USERS_OBJ_D

361

All User Objects

ODM_YES_NO_D

2000003226

Yes/No Cost List

ODM_YES_NO_SELECTION_D

451



Map Tables

Map tables are created for every multi-list attribute for all the supported classes. These tables are created dynamically during ETL run.

The following table lists the columns in the Map tables:

Table 2-9 Map Tables

Column Name Description

OBJECT_ID

Object ID

ENTRY_ID

Primary Key


The following table lists the various Map tables in OPLA Operational Schema:

Table 2-10 Map tables in OPLA Operational Schema

Entities Description

ODM_ASL_MAP


ODM_ATTACHMENT_MAP

Stores Mapping information about the attachments

ODM_CACHE_FILE_MAP1


ODM_DEV_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_ECO_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_ECR_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_MATERIAL_DECLARATION_MAP

Stores Mapping information about the material declaration.

ODM_MCO_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_OBJECT_MAP

Stores Mapping information about the objects.

ODM_PART_FAMILY_MAP

Stores Mapping information about part family.

ODM_PCO_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_PRODUCT_LINE_MAP

Stores Mapping information about product lines.

ODM_RENDITION_FILE_MAP

Stores Mapping information about rendition files

ODM_SCO_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_SS_SUPPLIER_MAP

Stores Mapping information about the suppliers.

ODM_SUBSTANCE_MAP

Stores Mapping information about substance and other PLM Classes

ODM_THUMBNAIL_FILE_MAP


ODM_THUMBNAIL_OBJECT_MAP


ODM_VERSION_FILE_MAP


ODM_WF_DEF_SIGNOFF_MAP



Subclass Views

These are views which contain information about Subclass-specific attributes. These subclass views are available for Agile PLM modules such as PQM, PC, PPM, PCM, and PG&C.

Summary Views

These are views which contain all the data related to Agile classes which includes Cover page, Page Two, Page Three and Flex attributes. These class views are available for Agile PLM modules such as PQM, PC, PPM, PCM and PG&C.

P4 Tables

These are class level tables which contains data relates to all user defined attributes (Flex Attributes). These class level tables are available for Agile PLM modules such as PQM, PC, PPM, PCM and PG&C.

Data Entities, Common

Table 2-11 Data Entities, Common

Entity Name Description

ODM_ACTION_ITEM

Stores the Action item related information used in Discussion and Activity objects

ODM_ADMINMSATT

Stores assignment/default values for Workflows, Roles, Privileges, Criteria, Filter and Viewable Tabs

ODM_AGILEUSER

Stores information about Users

ODM_ASL_MAP

This table maps Suppliers (in the ORGANIZATION table) to Items and Manufacturer Parts.

ODM_ATT_DESIGNS

Stores the File Folders Design Information

ODM_ATT_FILE_FLDR

This table support attachment being the file folder class object

ODM_ATT_HIST_RPT_FF

This table support attachment being the History Report File Folder class object

ODM_ATTACHMENT_MAP

Relationship mapping between Attachment, Version and Files tables. This table implements the many-to-many relationship between business objects and attachments. The table contains three foreign keys, attach_id, parent_id, parent_class and parent_id2 that will represent the relationship between an attachment and a business object. In addition, the table will also include a set of flex fields.

ODM_CHANGE_HISTORY

Stores user actions on routable objects

ODM_CONVERSION

Currency conversion rates stored in this table are used to convert currency values to the selected corporate currency.

ODM_CUSTOMER

This is the main customer record.

ODM_DISCUSSION

Stores discussion objects and its replies

ODM_ESCALATIONS

Stores workflow escalations for users.

ODM_FILE_INFO

Stores the information of the File.

ODM_FILES

Each row in the table represents a single file of a version of an attachment object.

ODM_FOLDER_STRUCTURE

Stores the Structure of the Folder which will hold all the Objects.

ODM_ITEM_DOCS

This is the main item table for Documents.

ODM_ITEM_HISTORY

Stores all user actions that have acted upon all non- routable objects

ODM_ITEM_PARTS

This is the main item table for Parts.

ODM_LANGTABLE

Stores the attribute contents or values with the Language ID.

ODM_MANU_BY

Stores relationship instances between agile parts and manufacturer parts.

ODM_MANU_PARTS

Stores relationship instances between agile parts and manufacturer parts.

ODM_OBJECTACL

Store each object level access control information

ODM_MANUFACTURERS

Stores Information about Manufacturers

ODM_MATERIAL_DECLARATION_MAP

Identifies the price declared on the item

ODM_NOTIFICATION

Stores information about notifications

ODM_ORGANIZATION

Store information about organization

ODM_PAGE_THREE

Stores the Page Three information of all the objects

ODM_PART_FAMILY

Stores Part Group details

ODM_PART_FAMILY_MAP

Stores the details of the mapping of Part Family.

ODM_PRICE_PUB_PRICE

Stores the published price information

ODM_PRICE_QUOTE_HIST

Stores the Historical Price Information.

ODM_PRODUCT_LINE

Stores the information of product line

ODM_PRODUCT_LINE_MAP

Stores the Product Line mapping Information.

ODM_RELATIONSHIP

You can specify ad hoc relationships between Agile objects by setting up relationships between them. Such a relationship is created when you add an object to a table in the Relationships tab of another object. Classes of objects that can be referenced on a Relationship tab include routable objects (which always have a Relationships tab), items, manufacturers, manufacturer parts, suppliers, customers, projects, RFQs, and supplier responses.

ODM_REV

Stores item revision information. This is the instance of the revision of the object

ODM_SIGNOFF

Stores user signoff status details.

ODM_SITE

Stores PCM Ship to Locations

ODM_SITES

Sites represent manufacturing locations.

ODM_USER_ASSIGNMENT

Stores relationship between user and roles - Appears to be an associative entity.

ODM_USER_GROUP

Stores User Groups as defined in the admin interface.

ODM_USER_ROLE

Stores the user roles.

ODM_USER_SITE

Associative entity between AGILEUSER and SITES

ODM_USER_USAGE_HISTORY

History of when users log in and out of system ID Primary key.

ODM_VERSION

Each row in the table represents a single version of an attachment object.

ODM_VERSION_FILE_MAP

This table will store the info of each row in Files Tab for different versions. This table will also implement the many-to-many relationship between files and versions. Different row in different version in the files tab may point to the same physical files. This is to avoid duplicating the physical file content when the file is unchanged when we create a new version.

ODM_WF_DEF_SIGNOFF_MAP

Map table to identify a user associated with the workflow state is Approver/Observer

ODM_WORKFLOW_PROCESS

Stores workflow status occurring sequence for all the workflow routable objects.


Data Entities, PPM

Table 2-12 Data Entities, PPM

Entity Name Description

ODM_ACTIVITY_BASELINE

Stores baselines for activity objects

ODM_ACTIVITY_DEPENDENCY

Stores Activity dependency relationship between Activity records. TO_ID is dependant on the FROM_ID with both ID's being ACTIVITY records.

ODM_APPROVAL_ITEMS

Stores Gates object's approval items

ODM_BASELINE_REV

Stores item revision information. This is the instance of the revision of the object

ODM_FLEX_FIELD

Stores flex fields of action item This is used to extend ACTION_ITEM and NEWS

ODM_NEWS

Stores news objects for Activity and Gates object. This is used in PE product

ODM_PRG_ACTIVITY

Stores the Program Activities Information

ODM_PRG_GATES

Stores information about Gates Object

ODM_PROGRAM_HISTORY

Stores history logs for Activity, Gates and Discussion objects

ODM_TEAM

Stores the Resources information associated with Programs

ODM_TEAM_BASELINE

Stores the Resources information of every baselined activity for all versions.


Data Entities, PC

Table 2-13 Data Entities, PC

Entity Name Description

ODM_BOM

Stores Bill of material related information

ODM_CHNG_CHG_ORDERS

Stores information about change order objects

ODM_CHNG_CHG_REQS

Stores information about change request objects

ODM_CHNG_DEVIATION

Stores information about deviation objects

ODM_CHNG_MFR_ORDERS

Stores the changes regarding the manufacturer orders.

ODM_CHNG_PRICE_CHGORDERS

Stores information about price change order objects

ODM_CHNG_SITE_CHGORDERS

Stores information about site change order objects

ODM_CHNG_STOP_SHIPS

Stores information about stop ships objects

ODM_ITEM_SITES

Store relationship between item and sites - Not a pure associative entity.

ODM_REFDESIG

Stores reference designator labels.


Data Entities, PQM

Table 2-14 Data Entities, PQM

Entity Name Description

ODM_PSR_NCR

Stores the Non Conformance Reports

ODM_PSR_NCR_ITEM

Mapping between NCR object and its Affected Items

ODM_PSR_PR

Stores the Problem Reports

ODM_PSR_PR_ITEM

Mapping between PR object and its Affected Items

ODM_QCR_AUDIT

Stores the Audits

ODM_QCR_AUDIT_ITEM

Mapping between an Audit object and its Affected Items

ODM_QCR_CAPA

Stores the Corrective and Preventive Actions

ODM_QCR_CAPA_ITEM

Mapping between a CAPA object and its Affected Items


Data Entities, PCM

Table 2-15 Data Entities, PCM

Entity Name Description

ODM_BUYER_LINE_CARD

Stores information about Supplier and Projects regarding RFQs

ODM_GEOGRAPHY_ROUTING

Stores Geographical routing information about sourcing projects

ODM_OBJECT_LOCKS

stores information about object locks

ODM_PRICEENTRY

Stores Pricing information for Sourcing projects

ODM_PRICELINE

Stores Pricing information for Sourcing projects

ODM_PRICEPOINT

Stores Pricing information for Sourcing projects

ODM_PRICE_HISTORY

Stores History information about pricing in sourcing project

ODM_PROJECT

Stores All Sourcing Projects.Class ID 16433

ODM_RESPONSE

Stores All RFQs.Class ID 16635

ODM_RESPONSE_HEADER

Stores All RFQ Response.Class ID 16441

ODM_RESPONSE_RFQ_MAPPING

Stores mapping information about RFQ and responses.

ODM_RFQ_COMMENTS

Stores comments about RFQs

ODM_RFQ_COMMENT_RECIPIENT

Stores comments about RFQ's from suppliers

ODM_RFQ_HEADER

Stores All RFQ's.Class ID 16635

ODM_RFQ_LINE

Stores information sourcing project product line

ODM_SC_AML

Stores all relationship about Item to Mfr Item Map for Sourcing Projects

ODM_SC_BOM

Stores information about the original and delta boms for sourcing projects

ODM_SC_COMMENT

Stores comments about sourcing projects

ODM_SC_COST_QTY_ROLLUP

Stores information about Cost rollup for sourcing Projects

ODM_SC_DELTA_BOM

Stores the delta bom for the given parent item in blob

ODM_SC_ITEM

Stores item information related to sourcing projects

ODM_SC_PROJECT_SITES

Stores information about Project and Sites for sourcing projects

ODM_SC_SUPPLIERS

Stores information about Partner assignment for items in the Sourcing Projects

ODM_SC_WORK_IN_PROGRESS

Stores information about Sourcing project completion status

ODM_SUPPLIER_LINE_CARD

Stores information about Supplier and Projects regarding RFQs


Data Entities, PGC

Table 2-16 Data Entities, PGC

Entity Name Description

ODM_AGGR_SUBSTANCE

Stores Aggregated Information about Substances

ODM_COMPLIANCE

Stores Compliance information about Substances

ODM_MATERIAL_DCLRNS_HGNUS

Stores All Homogeneous Material Declarations.Class ID 2000002395

ODM_MATERIAL_DCLRNS_IPC_1

Stores All IPC 1752-1 Material Declarations.Class ID 2000005977

ODM_MATERIAL_DCLRNS_IPC_2

Stores All IPC 1752-2 Material Declarations.Class ID 2000005735

ODM_MATERIAL_DCLRNS_JGPSSI

Stores All JGPSSI Material Declarations.Class ID 2000002423

ODM_MATERIAL_DCLRNS_PRTS

Stores All Parts Material Declarations.Class ID 2000002367

ODM_MATERIAL_DCLRNS_SBTNCE

Stores All Substance Material Declarations.Class ID 2000002882

ODM_MATERIAL_DCLRNS_SUPPLR

Stores All Supplier Material Declarations.Class ID 2000002395

ODM_REGULATION

Stores All Specifications.Class ID 2000001992

ODM_RGULTN_P2_MAPPING

Stores information specifications

ODM_SUBSTANCE_GROUPS

Stores All Substance Groups.Class ID 2000001119

ODM_SUBSTANCE_MAP

Stores Mapping information about substance and other PLM Classes

ODM_SUBSTANCE_MATERIALS

Stores All Substance Materials.Class ID 2000002467

ODM_SUBSTANCE_SUBPARTS

Stores All Substance Sub Parts.Class ID 2000002445

ODM_SUBSTANCE_SUBSTANCES

Stores All Substance Substances.Class ID 2000001018


Standard Column Suffixes in Agile PLM DM Tables

Suffixes have been used to indicate columns that must contain specific values.

Table 2-17 Standard column suffixes in Agile PLM DM tables

Suffix Value

_CD

This column value is based on the contents of A_LIST_ENTRY.ENTRY_CD (originally LISTENTRY.ENTRYID) (this is not a FK).

For a column with a Dynamic List whose values are not stored in List Entry table, we can treat it as a normal FK to an Agile table.

_DATE

This column contains a date or datetime value

_FLAG

This column contains a numeric value for Boolean value; 1 indicates True or Yes

0 indicates False or No

_ID

This column contains ID values.


Entity Relationship Diagrams

The Entity Relationship Diagrams are categorized into the following groups:

Product Collaboration

The following are the various Entity Relationship diagrams specific to Product Collaboration:

Change Orders

Figure 2-1 Change Orders entity relationship diagram

Change Orders entity relationship diagram

Note:

For ECR, MCO, and Stop Ships the same relationships apply.

Change Orders Signoff

Figure 2-2 Change Orders Signoff entity relationship diagram

Change Orders Signoff entity relationship diagram

Note:

N o t e For ECR, MCO, and Stop Ships the same relationships apply.

Item Parts

Figure 2-3 Item Parts entity relationship diagram

Item Parts entity relationship diagram

Item Manufacturer Parts

Figure 2-4 Item Manufacturer Parts entity relationship diagram

Item Manufacturer Parts entity relationship diagram

Item Rev Sites

Figure 2-5 Item Rev Sites entity relationship diagram

Item Rev Sites entity relationship diagram

Item Rev Site BOM Redlines

Figure 2-6 Item Rev Site BOM Redlines entity relationship diagram

Item Rev Site BOM Redlines entity relationship diagram

Product Portfolio Management

The following are the various Entity Relationship diagrams specific to Product Portfolio Management:

Activities

Figure 2-7 Activities entity relationship diagram

Activities entity relationship diagram

Discussions

Figure 2-8 Discussions entity relationship diagram

Discussions entity relationship diagram

Product Quality Management

The following are the various Entity Relationship diagrams specific to Product Quality Management:

PSR – NCR

Figure 2-9 PSR – NCR entity relationship diagram

PSR – NCR entity relationship diagram

PSR – NCR – Items

Figure 2-10 PSR – NCR – Items entity relationship diagram

PSR – NCR – Items entity relationship diagram

PSR – NCR Relationship

Figure 2-11 PSR – NCR entity relationship diagram

PSR – NCR entity relationship diagram

PSR – PR

Figure 2-12 PSR – PR entity relationship diagram

PSR – PR entity relationship diagram

PSR – PR – Items

Figure 2-13 SR – PR – Items entity relationship diagram

SR – PR – Items entity relationship diagram

PSR – PR Relationships

Figure 2-14 PSR – PR Relationships entity relationship diagram

PSR – PR Relationships entity relationship diagram

QCR – Audits

Figure 2-15 QCR – Audits entity relationship diagram

QCR – Audits entity relationship diagram

QCR – Audits – Items

Figure 2-16 QCR – Audits – Items entity relationship diagram

QCR – Audits – Items entity relationship diagram

QCR – Audit Relationships

Figure 2-17 CR – Audit Relationships entity relationship diagram

CR – Audit Relationships entity relationship diagram

QCR – CAPA

Figure 2-18 CR – CAPA entity relationship diagram

CR – CAPA entity relationship diagram

QCR – CAPA Items

Figure 2-19 QCR – CAPA Items entity relationship diagram

QCR – CAPA Items entity relationship diagram

QCR – CAPA Relationships

Figure 2-20 QCR – CAPA Relationships entity relationship diagram

QCR – CAPA Relationships entity relationship diagram

Product Governance and Compliance

The following are the various Entity Relationship diagrams specific to Product Governance and Compliance:

Declaration

Figure 2-21 Declaration entity relationship diagram

Declaration entity relationship diagram

Note:

ER Diagrams are the same as above for the other declaration tables such as ODM_MATERIAL_DCLRNS_IPC_1, ODM_MATERIAL_DCLRNS_IPC_2, ODM_MATERIAL_DCLRNS_JGPSSI, ODM_MATERIAL_DCLRNS_PRTS, ODM_MATERIAL_DCLRNS_SBTNCE, ODM_MATERIAL_DCLRNS_SUPPLR, ODM_MATERIAL_DCLRNS_HGNUS

Specifications

Figure 2-22 Specifications entity relationship diagram

Specifications entity relationship diagram

Substances

Figure 2-23 Substances entity relationship diagram

Substances entity relationship diagram

Note:

ER Diagrams are the same as above for other substances tables such as ODM_SUBSTANCE_GROUPS, ODM_SUBSTANCE_MATERIALS, ODM_SUBSTANCE_SUBPARTS, and ODM_SUBSTANCE_SUBSTANCES.

Product Cost Management

The following are the various Entity Relationship diagrams specific to Product Cost Management:

Sourcing Project

Figure 2-24 Sourcing Project entity relationship diagram

Sourcing Project entity relationship diagram

Request for Quote

Figure 2-25 Request for Quote entity relationship diagram

Request for Quote entity relationship diagram

RFQ Response

Figure 2-26 RFQ Response entity relationship diagram

RFQ Response entity relationship diagram