Results for “edw_mtl_ildm_pcmp_p”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OPI.EDW_MTL_ILDM_PCMP_LTC table is a level table within the Inventory Locator Dimension (ILDM) of the Operations Intelligence (OPI) data warehouse, which shipped with Oracle E-Business Suite 12.1.1 and remained in the 12.2.2 documentation set. It represents the PCMP (Process Company) level, meaning it stores the distinct Process Manufacturing company/organization dimension members that populate the inventory locator star schema. In the Oracle EBS architecture, this object exists in the OPI schema, which is dedicated to Operations Intelligence (now classified as obsolete in current ETRM metadata), rather than in the transactional OPM or INV schemas. It serves as a conformed dimension bridge that resolves the operational unit to a Process Company, enabling inventory fact tables to be sliced by company and operating unit along the ILDM hierarchy.

The heuristic Data Vault classification for this object is standalone, with no foreign key relationships mined from the schema. As a modeling suggestion, this indicates the table behaves as a detached dimension (or a hub-like entity), keyed on a surrogate company key with no navigational links to sibling levels enforced at the database level; any linkage to parent operating-unit levels is prescriptive rather than declarative and should be modeled explicitly if a Data Vault conversion is undertaken.

Key Information Stored

The table holds 16 documented columns. The most operationally significant are:

• OPM_COMPANY_PK — the natural business key of the Process Company; it forms the basis of unique index EDW_MTL_ILDM_PCMP_U1 and is the identifier most commonly joined to source OPM tables.
• OPM_COMPANY_PK_KEY — the surrogate/system-generated key; it constitutes the primary key constraint EDW_MTL_ILDM_PCMP_P and is the value propagated into downstream inventory fact tables.
• OPM_COMPANY_CODE — the internal short code used by Process Manufacturing for the company.
• OPM_COMPANY_NAME — the descriptive company name used for display and reporting.
• OPM_COMPANY_DP — a dense/display descriptor (or data-preserving attribute) variant of the company label, typically used where denormalized labeling is required.
• OPERATING_UNIT_FK_KEY — the surrogate reference tying the company level to an operating unit within the ILDM hierarchy.
• INSTANCE_CODE — the EBS instance or application context identifier, allowing multi-instance warehouses to segregate data.
• NAME and DESCRIPTION — the presentation-layer labels exposed in OPI reports.
• CREATION_DATE and LAST_UPDATE_DATE — standard EBS audit columns used to support incremental extraction and change detection.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — five flexfields reserved for customer-specific extension of the company dimension.

The distinction between the surrogate OPM_COMPANY_PK_KEY and the business-key candidate OPM_COMPANY_PK is essential: fact tables reference the surrogate, whereas transactional joins to OPM source data use the business key.

Common Use Cases and Queries

The primary scenario is resolving a Process Company label alongside inventory locator facts in OPI reporting. A typical pattern joins the dimension on its surrogate key:

• Reporting inventory balances grouped by company name: SELECT l.OPM_COMPANY_NAME, SUM(f.quantity) FROM ... f JOIN OPI.EDW_MTL_ILDM_PCMP_LTC l ON f.opm_company_pk_key = l.opm_company_pk_key GROUP BY l.OPM_COMPANY_NAME.
• Translating a transactional business key to the warehouse surrogate: joining on l.OPM_COMPANY_PK = :opm_company_pk.
• Operating-unit rollups using OPERATING_UNIT_FK_KEY to navigate up the ILDM hierarchy.
• Incremental refresh: filtering extraction calls by LAST_UPDATE_DATE or CREATION_DATE.
• Multi-instance consolidation: constraining on INSTANCE_CODE when several EBS instances feed a single warehouse.
• Custom attribute reporting against USER_ATTRIBUTE1–USER_ATTRIBUTE5 where customer flexfields carry segment mappings.

Because the object is documented as obsolete, these patterns remain valid for legacy OPI implementations but should not be used as a basis for new development in 12.2.2 or later.

Related Objects

The metadata documents no enforced foreign keys, so relationships are inferred from column naming and OPI/OPM conventions. The most significant associated objects include:

• EDW_MTL_ILDM_LTC (or equivalent ILDM root/level tables) — the parent dimension table whose OPERATING_UNIT_FK_KEY aligns with this table's operating-unit key.
• EDW_MTL_ILDM_*_LTC sibling level tables — other ILDM levels (e.g., inventory organization, subinventory, locator) sharing the OPM_COMPANY_PK_KEY prefix conventions.
• OPI Inventory fact tables — any ILDM-fact object joining on OPM_COMPANY_PK_KEY.
• OPM_SY_ORGANIZATIONS / OPM_ORGANIZATIONS — source Process Manufacturing organization tables supplying OPM_COMPANY_PK and company codes.
• HR_OPERATING_UNITS — source of the operating unit identifier mirrored by OPERATING_UNIT_FK_KEY.
• OPI ETL / concurrent programs — the OPI load routines that populate and refresh this dimension from OPM sources.

Collectively these objects support the ILDM hierarchal slicing of Process Manufacturing inventory data within the now-obsolete Operations Intelligence module.