Results for “mtl_physical_adjustments”

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

Overview

MTL_PHYSICAL_ADJUSTMENTS is an Oracle Inventory (INV) table that stores the adjustment records generated during a physical inventory count cycle. When a physical inventory is scheduled, tags are entered and approved, and the resulting variances between the system on-hand quantity and the counted quantity are written to this table. Each row represents a single adjustment line — the reconciliation of one item, at one subinventory/locator/lot/serial combination — for one physical inventory event in one organization.

The table sits between the count-capture stage and the transaction-posting stage of the physical inventory workflow. Rows are created when a physical inventory is approved, and they drive the subsequent material transactions that actually move the on-hand balances. The documented primary key is MTL_PHYSICAL_ADJUSTMENTS_PK on ADJUSTMENT_ID, with a secondary unique index MTL_PHYSICAL_ADJUSTMENTS_U2 on (ORGANIZATION_ID, PHYSICAL_INVENTORY_ID, ADJUSTMENT_ID).

From a Data Vault modeling perspective, the heuristic classification of this table is satellite-leaning. It is a descriptive, transaction-scoped record that carries quantity measures and context attributes attached to a physical inventory event and an inventory item, rather than a pure hub or link. Modelers should treat the physical inventory plus item combination as the natural business grain, with ADJUSTMENT_ID as the technical surrogate.

Key Information Stored

The 35 documented columns cluster into identity, quantity, and control attributes:

• ADJUSTMENT_ID — surrogate primary key (MTL_PHYSICAL_ADJUSTMENTS_PK); the technical identifier also referenced by dependent objects.
• ORGANIZATION_ID, PHYSICAL_INVENTORY_ID — business-key candidates and the FK linkage back to the parent physical inventory in MTL_PHYSICAL_INVENTORIES.
• INVENTORY_ITEM_ID — the item being adjusted; participates in FKs to MTL_SYSTEM_ITEMS_B, MTL_LOT_NUMBERS, and MTL_ITEM_REVISIONS_B.
• SUBINVENTORY_NAME, LOCATOR_ID — the storage location context of the adjustment.
• REVISION, LOT_NUMBER, LOT_EXPIRATION_DATE, SERIAL_NUMBER — revision, lot, and serial control attributes driving the item/lot/serial FKs.
• SYSTEM_QUANTITY, COUNT_QUANTITY, ADJUSTMENT_QUANTITY — the core measures: on-hand at count time, the counted result, and the resulting variance (system minus count).
• SECONDARY_SYSTEM_QTY, SECONDARY_COUNT_QTY, SECONDARY_ADJUSTMENT_QTY — the equivalent dual-unit-of-measure quantities for items under secondary UOM control.
• APPROVAL_STATUS, APPROVED_BY_EMPLOYEE_ID, AUTOMATIC_APPROVAL_CODE, TEMP_APPROVER — approval workflow and routing state.
• GL_ADJUST_ACCOUNT — the accounting flexfield (FK to GL_CODE_COMBINATIONS) charged for the adjustment.
• COST_GROUP_ID, ACTUAL_COST — costing context (FK to CST_COST_GROUPS) and the cost basis applied.
• PARENT_LPN_ID, OUTERMOST_LPN_ID, LOT_SERIAL_CONTROLS — WMS license-plate and control-level attributes.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program and audit who-columns (LAST_UPDATE_DATE, CREATED_BY, etc.).

Common Use Cases and Queries

The most common use is reconciling physical count variances for a completed cycle. A typical pattern joins the adjustment to the parent physical inventory to report net shrinkage or gain by subinventory:

• Variance reporting: SELECT ORGANIZATION_ID, SUBINVENTORY_NAME, INVENTORY_ITEM_ID, SUM(ADJUSTMENT_QUANTITY) FROM MTL_PHYSICAL_ADJUSTMENTS WHERE PHYSICAL_INVENTORY_ID = :pi_id GROUP BY ... to compute net adjustments per item/location.
• Posting verification: Join to MTL_MATERIAL_TRANSACTIONS on PHYSICAL_ADJUSTMENT_ID to confirm every approved adjustment produced a corresponding inventory transaction.
• Approval monitoring: Filter on APPROVAL_STATUS and APPROVED_BY_EMPLOYEE_ID to identify adjustments pending approval or automatically approved.
• Accounting/GL analysis: Group by GL_ADJUST_ACCOUNT and ACTUAL_COST to value adjustments for the general ledger.
• Tag traceability: Join to MTL_PHYSICAL_INVENTORY_TAGS on ADJUSTMENT_ID to trace an adjustment back to the original count tag.

Related Objects

The following are the most significant related objects, joined on the documented keys:

• MTL_PHYSICAL_INVENTORIES — parent header; join on ORGANIZATION_ID and PHYSICAL_INVENTORY_ID.
• MTL_MATERIAL_TRANSACTIONS — child posting records; join on MTL_MATERIAL_TRANSACTIONS.PHYSICAL_ADJUSTMENT_ID = ADJUSTMENT_ID.
• MTL_PHYSICAL_INVENTORY_TAGS — source count tags; join on ADJUSTMENT_ID.
• MTL_SYSTEM_ITEMS_B — item definition; join on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_SECONDARY_INVENTORIES — subinventory validation; join on SUBINVENTORY_NAME and ORGANIZATION_ID.
• MTL_LOT_NUMBERS / MTL_SERIAL_NUMBERS / MTL_ITEM_REVISIONS_B — lot, serial, and revision control; join on INVENTORY_ITEM_ID, ORGANIZATION_ID, plus LOT_NUMBER, SERIAL_NUMBER, or REVISION.
• GL_CODE_COMBINATIONS — accounting flexfield; join on GL_ADJUST_ACCOUNT.
• CST_COST_GROUPS and WMS_LICENSE_PLATE_NUMBERS — cost group and license-plate context; join on COST_GROUP_ID, PARENT_LPN_ID, and OUTERMOST_LPN_ID.