Results for “pm_matl_dtl”

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

Overview

PM_MATL_DTL is a Process Manufacturing (GME) table that stores batch or FPO (Flow Production Order) material details within Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the line-level detail store for materials consumed, produced, or otherwise associated with a batch. Each row represents a single material line tied to a parent batch header record. The ETRM metadata notes that the table is not implemented in the documented database instance, meaning it may be conditionally deployed depending on whether Process Manufacturing Process Execution is licensed and configured.

Under the heuristic Data Vault classification derived from its foreign key topology, PM_MATL_DTL is described as satellite-leaning. In modeling terms, this suggests the table behaves primarily as a descriptive satellite attached to a batch hub (PM_BTCH_HDR), carrying attributes that change or accumulate per material line rather than acting as an independent business hub or a pure many-to-many link. Practitioners designing a Data Vault layer over EBS should treat PM_BTCH_HDR as the hub and PM_MATL_DTL as a satellite keyed by batch context plus line identity.

Key Information Stored

The table’s identity and relationships are anchored by two documented primary keys. PM_MATL_DTLI1 is defined on LINE_ID, and PM_MATL_DTL_PK is defined on the composite of BATCH_ID and LINE_ID. The surrogate line identifier is LINE_ID; the composite (BATCH_ID, LINE_ID) serves as the practical business-key candidate that guarantees uniqueness within a batch.

• LINE_ID — surrogate primary key and unique line identifier (PM_MATL_DTLI1).
• BATCH_ID — foreign key to PM_BTCH_HDR, identifying the parent batch or FPO.
• ITEM_ID — foreign key to IC_ITEM_MST, the inventory item for the material line.
• ITEM_UM — primary unit of measure, foreign key to SY_UOMS_MST.
• ITEM_UM2 — secondary unit of measure, foreign key to SY_UOMS_MST.
• TEXT_CODE — foreign key to PM_TEXT_HDR, linking descriptive text to the material line.

Because the ETRM metadata lists only relational columns rather than full column definitions, quantity, scrap, and transaction-timing attributes should be confirmed against the deployed dictionary before use.

Common Use Cases and Queries

Typical reporting scenarios include batch material reconciliation, FPO consumption versus expected yield, and unit-of-measure conversion auditing. A common query pattern joins the detail table to its batch header and item master:

• Retrieve all material lines for a batch: SELECT LINE_ID, ITEM_ID, ITEM_UM, ITEM_UM2 FROM PM_MATL_DTL WHERE BATCH_ID = :batch_id;
• Resolve item descriptions: join PM_MATL_DTL to IC_ITEM_MST on ITEM_ID.
• Resolve unit-of-measure labels: join ITEM_UM and ITEM_UM2 to SY_UOMS_MST.
• Attach descriptive text: join TEXT_CODE to PM_TEXT_HDR.

These joins support batch genealogy, cost roll-up, and production reporting where material detail must be tied back to the batch and the item master.

Related Objects

PM_MATL_DTL participates in a dense web of foreign key relationships. The most significant related objects are:

• PM_BTCH_HDR — parent batch header; joined via PM_MATL_DTL.BATCH_ID. PM_BTCH_HDR also references PM_MATL_DTL through PARENTLINE_ID.
• IC_ITEM_MST — item master; joined via ITEM_ID.
• SY_UOMS_MST — unit-of-measure master; joined via ITEM_UM and ITEM_UM2.
• PM_TEXT_HDR — text header; joined via TEXT_CODE.
• PM_BTCH_PKG — batch package referencing BULK_BATCH_ID and BULK_LINE_ID.
• PM_ROUT_MTL — routing material table referencing BATCH_ID and BATCHLINE_ID.