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:

   • "Data Entities, Common"

   • "Data Entities, PPM"

   • "Data Entities, PC"

   • "Data Entities, PQM"

   • "Data Entities, PCM"

   • "Data Entities, PGC"

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"

• "Product Portfolio Management"

• "Product Quality Management"

• "Product Governance and Compliance"

• "Product Cost Management"

Product Collaboration

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

Change Orders

Figure 2-1 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

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 Manufacturer Parts

Figure 2-4 Item Manufacturer Parts entity relationship diagram

Item Rev Sites

Figure 2-5 Item Rev Sites entity relationship diagram

Item Rev Site BOM Redlines

Figure 2-6 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

Discussions

Figure 2-8 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 – Items

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

PSR – NCR Relationship

Figure 2-11 PSR – NCR entity relationship diagram

PSR – PR

Figure 2-12 PSR – PR entity relationship diagram

PSR – PR – Items

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

PSR – PR Relationships

Figure 2-14 PSR – PR Relationships entity relationship diagram

QCR – Audits

Figure 2-15 QCR – Audits entity relationship diagram

QCR – Audits – Items

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

QCR – Audit Relationships

Figure 2-17 CR – Audit Relationships entity relationship diagram

QCR – CAPA

Figure 2-18 CR – CAPA entity relationship diagram

QCR – CAPA Items

Figure 2-19 QCR – CAPA Items entity relationship diagram

QCR – CAPA Relationships

Figure 2-20 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

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

Substances

Figure 2-23 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

Request for Quote

Figure 2-25 Request for Quote entity relationship diagram

RFQ Response

Figure 2-26 RFQ Response entity relationship diagram