Results for “item_pk”

6 results




Overview
Explore in AI Chat

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

Overview

MTH_ITEMS_ERR is a staging and diagnostic table that belongs to the MTH schema, the database owner for Oracle Manufacturing Operations Center (MTH), a component of the Oracle E-Business Suite manufacturing analytics and shop-floor data collection stack. As its description states, the table is an error table for MTH_ITEMS_D, the item dimension interface/staging table. During manufacturing operations center loads, item master records are read from source systems, staged in MTH_ITEMS_D, validated, and then promoted to the target item dimension. Rows that fail validation — for example, missing mandatory attributes, invalid unit of measure references, or rejected cross-references to the EBS item master — are diverted into MTH_ITEMS_ERR so that the load process can continue while preserving a complete audit trail of failed records for remediation.

From a Data Vault modeling perspective, the mined relationship structure classifies MTH_ITEMS_ERR as a standalone table with no foreign-key dependencies to parent hubs or links. This suggests that, rather than modeling it as a dependent satellite of the item hub, it is best treated as an independent error/audit hub or a staging artifact keyed on the errored item surrogate. Its 58 documented columns mirror the MTH_ITEMS_D item staging structure and append error metadata, reinforcing the staging-table interpretation.

Key Information Stored

The documented primary key is MTH_ITEMS_ERR_U1, defined on the single column ITEM_PK. ITEM_PK is the surrogate identifier for the errored item record — the same key that would have been used to insert into the item dimension had the record passed validation. Because the primary key is an artificial surrogate rather than a natural business key, ITEM_PK should not be used as a lookup on source systems; the business-key candidates for an item are the combination of SOURCE_ORG_CODE, ITEM_NAME, and EBS_ITEM_ID with EBS_ORG_ID.

  • ITEM_PK — surrogate primary key uniquely identifying the errored staging row.
  • SYSTEM_FK — foreign key to the source system/instance definition that produced the item record.
  • SOURCE_ORG_CODE and EBS_ORG_ID — the inventory organization context of the item.
  • ITEM_NAME, EBS_ITEM_ID, DESCRIPTION, BASE_ITEM — item identity attributes carried from the source.
  • PRIMARY_UOM, SECONDARY_UOM — unit of measure values that failed or require secondary validation.
  • UNIT_WEIGHT, WEIGHT_UOM, UNIT_VOLUME, VOLUME_UOM — physical characteristic fields subject to load validation.
  • REPROCESS_READY_YN — the remediation flag indicating whether a corrected row can be resubmitted to MTH_ITEMS_D.
  • ERR$$$_ERROR_ID, ERR$$$_ERROR_REASON, ERR$$$_SEVERITY, ERR$$$_OPERATOR_NAME, ERR$$$_ERROR_OBJECT_NAME, and ERR_CODE — the diagnostic payload describing why the row was rejected.
  • ERR$$$_AUDIT_RUN_ID and ERR$$$_AUDIT_DETAIL_ID — the load run and detail record that generated the error, enabling run-level error reporting.
  • USER_ATTR1 through USER_ATTR30 and USER_MEASURE1 through USER_MEASURE5 — extensibility columns for customer-defined item attributes and measures.

Common Use Cases and Queries

The primary use case is error triage during item dimension loads. A manufacturing analyst reviews MTH_ITEMS_ERR by load run, corrects the underlying source data, and flips REPROCESS_READY_YN to Y before rerunning the interface. Typical query patterns include:

  • Listing all errors for a given load run: SELECT ITEM_PK, ITEM_NAME, ERR$$$_ERROR_REASON, ERR$$$_SEVERITY FROM MTH_ITEMS_ERR WHERE ERR$$$_AUDIT_RUN_ID = :run_id;
  • Identifying records ready for reprocessing: SELECT * FROM MTH_ITEMS_ERR WHERE REPROCESS_READY_YN = 'Y';
  • Finding errors by organization: SELECT ITEM_NAME, EBS_ITEM_ID, ERR_CODE FROM MTH_ITEMS_ERR WHERE SOURCE_ORG_CODE = :org_code;
  • Error trend reporting by severity and operator: SELECT ERR$$$_SEVERITY, COUNT(*) FROM MTH_ITEMS_ERR GROUP BY ERR$$$_SEVERITY;

These queries support data-quality dashboards, load success-rate metrics, and root-cause analysis of interface failures.

Related Objects

The following objects are the most significant consumers or counterparts of MTH_ITEMS_ERR based on the documented relationships and the MTH interface architecture:

  • MTH_ITEMS_D — the item staging/dimension table whose failed records are diverted into MTH_ITEMS_ERR; joined on ITEM_PK.
  • MTH_ITEMS — the target item dimension populated by successful loads; ITEM_PK is the shared surrogate.
  • MTH_SYSTEMS — referenced via SYSTEM_FK, defining the source system metadata.
  • MTH_AUDIT_RUN (or equivalent run control table) — joined on ERR$$$_AUDIT_RUN_ID.
  • MTH_ITEMS_INTERFACE / MTH_ITEMS_LOAD — interface programs that read, validate, and route rows between MTH_ITEMS_D and MTH_ITEMS_ERR.

Because no FK constraints were mined, these relationships are resolved at the application/integration layer rather than enforced by the database.