Search Results ps_matl_hdr




Overview
Explore in AI Chat

Overview

PS_MATL_HDR is a temporary working table owned by the GMP schema (Process Manufacturing Process Planning) in Oracle EBS 12.1.1 and 12.2.2. As its ETRM description states, it holds "temporary data about the report" — specifically, the header-level records generated during execution of the Process Planning material availability and sourcing report. During report processing, the planning engine populates this table to stage the item/category combinations that will be evaluated for material requirements and substitutions before detailed results are written to companion detail tables. Because the content is transient, the table is typically truncated between report runs rather than treated as a permanent transactional store.

From a Data Vault modeling perspective, the FK structure classifies PS_MATL_HDR as hub-leaning: its composite primary key of MATL_REP_ID and ITEM_ID behaves much like a natural/business key for a report-item relationship, with downstream detail tables acting as satellites that depend on it. This is a heuristic suggested by the mined key relationships, not a declared design intent.

Key Information Stored

The seven documented columns partition into the report-identity keys, item/category descriptors, and organizational context:

  • MATL_REP_ID — Report instance identifier; the first component of the composite primary key PS_MATL_HDR_PK, grouping all rows belonging to a single execution/parameter set of the material report.
  • ITEM_ID — Item identifier; the second component of the composite primary key, uniquely pairing a report run with a specific item.
  • INVENTORY_ITEM_ID — The inventory master reference for the item, used to join planning data back to MTL_SYSTEM_ITEMS_B.
  • ORGANIZATION_ID — The inventory organization context in which the item is being planned or sourced.
  • PLANNING_CLASS — Classification code controlling how the item is treated in the planning/report run.
  • PLANNING_CATEGORY_ID — Category used for planning (e.g., sourcing or substitution grouping).
  • CATEGORY_ID — The item category reference applied during the report.

The surrogate/technical key is PS_MATL_HDR_PK (MATL_REP_ID, ITEM_ID); besides it, no separate single-column surrogate exists, so MATL_REP_ID + ITEM_ID doubles as the business-key candidate via the unique index of the same name.

Common Use Cases and Queries

Typical usage centers on diagnosing report output and validating what the planning engine staged. A representative query retrieves the staged items for a report run:

SELECT h.matl_rep_id, h.item_id, h.organization_id,
       h.planning_class, h.inventory_item_id
FROM   gmp.ps_matl_hdr h
WHERE  h.matl_rep_id = :report_id
ORDER BY h.item_id;

Joining to the detail table exposes the report's granular results:

SELECT h.item_id, d.*
FROM   gmp.ps_matl_hdr h,
       gmp.ps_matl_dtl d
WHERE  h.matl_rep_id = d.matl_rep_id
AND    h.item_id    = d.item_id;

Common scenarios include: auditing a specific material report run by MATL_REP_ID; verifying that an item (INVENTORY_ITEM_ID/ORGANIZATION_ID) was included; reconciling substitution/sourcing output via PS_UBKT_DTL; and troubleshooting report performance or truncation cycles. Because rows are temporary, reports should filter by MATL_REP_ID and never assume persistence across runs.

Related Objects

PK/FK metadata identifies the significant dependencies:

  • PS_MATL_DTL — child detail table joined on MATL_REP_ID and ITEM_ID; holds the line-level report results.
  • PS_UBKT_DTL — child detail table joined on MATL_REP_ID and ITEM_ID; stores related "ubkt" (sourcing/substitution) detail.
  • MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID to resolve item descriptions.
  • MTL_ITEM_CATEGORIES / category tables — joined via CATEGORY_ID and PLANNING_CATEGORY_ID for category descriptions.
  • ORG_ORGANIZATION_DEFINITIONS — joined via ORGANIZATION_ID for organization validation.

These relationships confirm PS_MATL_HDR's role as the hub of the material report staging model, with PS_MATL_DTL and PS_UBKT_DTL functioning as its dependent satellites.