Results for “mtl_physical_inventory_tags”

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

Overview

MTL_PHYSICAL_INVENTORY_TAGS is the Inventory (INV) transactional table that stores physical inventory tag definitions within Oracle E-Business Suite 12.1.1 and 12.2.2. A physical inventory count begins when tags are generated for a given physical inventory and organization; each row in this table represents a single tag, capturing the tag number, the item and locator expectations printed on the tag, and, once the count is entered, the quantities physically observed by the counting personnel. The table therefore sits at the center of the count-to-adjustment flow, bridging the count event recorded in MTL_PHYSICAL_INVENTORIES and the approval/adjustment process that ultimately drives MTL_PHYSICAL_ADJUSTMENTS. It supports lot, serial, revision, locator, subinventory, cost group, and License Plate Number (LPN) contexts, which makes it the primary staging point for both on-hand snapshot data and counted results.

From a modeling perspective, the mined Data Vault classification for this object is a link. This is consistent with its role: the table carries foreign keys to many independent business entities (organizations, items, locators, lots, serials, LPNs, cost groups) and represents associations and events rather than a single descriptive hub.

Key Information Stored

The table contains 51 documented columns. The most operationally significant are:

• TAG_ID — surrogate primary key (MTL_PHYSICAL_INVENTORY_TAGS_PK).
• TAG_NUMBER — the human-readable tag identifier; combined with ORGANIZATION_ID and PHYSICAL_INVENTORY_ID it forms the business-key unique index MTL_PHYSICAL_INVENTORY_TAGS_U2.
• PHYSICAL_INVENTORY_ID — the parent physical inventory event.
• ORGANIZATION_ID — the inventory organization in which the count occurs.
• INVENTORY_ITEM_ID — the item expected/counted on the tag; foreign key to MTL_SYSTEM_ITEMS_B.
• TAG_QUANTITY, TAG_UOM, TAG_QUANTITY_AT_STANDARD_UOM, STANDARD_UOM — the counted quantity and unit-of-measure conversions.
• TAG_SECONDARY_QUANTITY, TAG_SECONDARY_UOM, TAG_QTY_AT_STD_SECONDARY_UOM, STANDARD_SECONDARY_UOM — secondary UOM quantities for dual-UOM organizations.
• SUBINVENTORY and LOCATOR_ID — the storage location context.
• LOT_NUMBER, LOT_EXPIRATION_DATE, REVISION, SERIAL_NUM — lot/serial/revision controls for controlled items.
• COUNTED_BY_EMPLOYEE_ID — the employee who recorded the count.
• VOID_FLAG — indicates a voided tag.
• ADJUSTMENT_ID — links the tag to its resulting physical adjustment.
• PARENT_LPN_ID, OUTERMOST_LPN_ID, COST_GROUP_ID — warehouse management and costing context.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) and the DFF attribute columns ATTRIBUTE1–ATTRIBUTE15.

Common Use Cases and Queries

Typical scenarios include querying all tags for an active physical inventory, listing tags by tag number, and reconciling counts against system on-hand before approval. Because TAG_NUMBER is uniquely scoped to organization and physical inventory, lookups commonly use MTL_PHYSICAL_INVENTORY_TAGS_U2.

A representative query lists counted tags for an organization:

• SELECT t.tag_number, t.inventory_item_id, t.subinventory, t.locator_id, t.tag_quantity, t.counted_by_employee_id
• FROM mtl_physical_inventory_tags t
• WHERE t.organization_id = :org_id
• AND t.physical_inventory_id = :pi_id
• AND nvl(t.void_flag,'N') = 'N';

Joining to MTL_SYSTEM_ITEMS_B for item descriptions, MTL_ITEM_LOCATIONS for locator names, and MTL_PHYSICAL_ADJUSTMENTS via ADJUSTMENT_ID supports variance and adjustment reporting. Void tags (VOID_FLAG = 'Y') are typically excluded from physical count reconciliation.

Related Objects

The most significant related objects and join columns are:

• MTL_PHYSICAL_INVENTORIES — parent via ORGANIZATION_ID and PHYSICAL_INVENTORY_ID.
• MTL_PHYSICAL_ADJUSTMENTS — joined via ADJUSTMENT_ID.
• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_ITEM_LOCATIONS — joined via LOCATOR_ID and ORGANIZATION_ID.
• MTL_SECONDARY_INVENTORIES — joined via SUBINVENTORY and ORGANIZATION_ID.
• MTL_LOT_NUMBERS — joined via INVENTORY_ITEM_ID, ORGANIZATION_ID, LOT_NUMBER.
• MTL_SERIAL_NUMBERS — joined via SERIAL_NUM and INVENTORY_ITEM_ID.
• MTL_ITEM_REVISIONS_B — joined via INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION.
• WMS_LICENSE_PLATE_NUMBERS — joined via PARENT_LPN_ID and OUTERMOST_LPN_ID.
• CST_COST_GROUPS — joined via COST_GROUP_ID.
• WF_MAILER_TAGS — references this table via TAG_ID.