Search Results matl_rep_id

Overview

PS_MATL_DTL is a table owned by the GMP schema (Process Manufacturing Process Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. Per the ETRM documentation, the object is classified as VALID and is described as holding "temporary data about the report." This characterization is significant: PS_MATL_DTL functions as a staging or transient persistence layer for material planning reports rather than as a master or transactional source of truth. During the execution of process planning material report programs, the application writes pre-aggregated demand and supply figures into this table, typically for subsequent retrieval, formatting, or printing.

Under the supplied heuristic Data Vault classification (satellite-leaning), PS_MATL_DTL would model as a satellite attached to a parent hub or link, given that it stores descriptive, report-scoped attributes keyed to a report header and inventory item. The narrow, report-specific nature of the payload and its temporary lifecycle reinforce this satellite interpretation rather than a hub or link role.

Key Information Stored

The table is physically documented with 32 columns in ETRM 12.2.2. The composite primary key PS_MATL_DTL_PK is defined on MATL_REP_ID, ITEM_ID, and PERD_END_DATE; this same unique index serves as the principal business-key candidate. MATL_REP_ID is a foreign key to the parent header, PS_MATL_HDR.

The most operationally important columns include:

• MATL_REP_ID — Identifier linking each detail row to its report header record; the scope/organizing key for the report run.
• ITEM_ID / INVENTORY_ITEM_ID — The item for which planning quantities are reported; both appear as documented columns.
• ORGANIZATION_ID — Warehouse or inventory organization context for the reported figures.
• WHSE_CODE — Warehouse code associated with the material balance.
• PERD_NAME / PERD_END_DATE — Planning period label and its ending date; the period component of the primary key.
• QTY_ON_HAND — On-hand quantity at the start of the planning period.
• TOTAL_DEMAND — Aggregated demand, derived from components such as SALES_ORDERS, FORE_CAST, SCHED_INGRED, FIRM_INGRED, and PLND_INGRED.
• ENDING_BAL — Projected closing balance after supply and demand are netted.
• PLANNING_CLASS / PLANNING_CATEGORY_ID — Classification attributes used to group or filter planning output.
• NET_SS_REQMT — Net safety-stock requirement.

Supply-side columns such as PO_RECEIPTS, SCHED_PROD, PLND_PROD, PLND_TRANSFER_IN, and PLND_PURCHASE complete the planning picture, mirroring the demand columns to yield the ending balance.

Common Use Cases and Queries

Typical use is reporting and diagnostics for the process planning material report. A representative query retrieves the planning picture for a report run by joining to the header:

• SELECT d.ITEM_ID, d.PERD_NAME, d.QTY_ON_HAND, d.TOTAL_DEMAND, d.ENDING_BAL FROM GMP.PS_MATL_DTL d WHERE d.MATL_REP_ID = :p_report_id ORDER BY d.ITEM_ID, d.PERD_END_DATE;
• Reconciling demand versus supply by period for a given item and warehouse using WHSE_CODE and ORGANIZATION_ID filters.
• Auditing temporary report output to verify that totals match underlying MRP/DRP results before report publication.

Because the table is temporary, queries should treat rows as run-scoped and disposable; stale rows may be purged between report executions.

Related Objects

The documented FK relationship defines the following dependencies:

• PS_MATL_HDR — Parent header table; joined via PS_MATL_DTL.MATL_REP_ID = PS_MATL_HDR.MATL_REP_ID. This is the only explicitly documented foreign key.
• PS_MATL_DTL_PK — Primary key/unique index on (MATL_REP_ID, ITEM_ID, PERD_END_DATE), enforcing one row per item per period per report.
• Inventory and planning base objects referenced logically through ITEM_ID / INVENTORY_ITEM_ID and ORGANIZATION_ID (e.g., MTL_SYSTEM_ITEMS_B, MTL_PARAMETERS) support validation and reporting joins.
• Process Planning report concurrent programs that populate and consume PS_MATL_DTL are the principal runtime dependents.