Results for “physical_inventory_name”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_PHYSICAL_INVENTORIES is the foundational definition table for physical inventory counts in Oracle E-Business Suite Inventory (INV). Owned by the INV schema, it stores one row per physical inventory event scheduled or executed within an inventory organization. Each row captures the parameters, tolerances, account assignments, and workflow controls that govern how a count is performed, frozen, tagged, adjusted, and approved. Because Oracle EBS supports both 12.1.1 and 12.2.2 with essentially identical INV schema semantics, this table remains stable across those releases and is central to reconciliation, audit, and period-close reporting.
From a Data Vault modeling perspective, the metadata's heuristic classification of this table is hub-leaning. Its composite primary key — ORGANIZATION_ID plus PHYSICAL_INVENTORY_ID — behaves like a natural business key that uniquely identifies each physical inventory occurrence, while descriptive and control columns can be modeled as satellite attributes. This structure makes the table a natural candidate for a hub with one or more attached satellites.
Key Information Stored
The documented physical schema contains 46 columns. The most significant are:
- PHYSICAL_INVENTORY_ID and ORGANIZATION_ID — the composite primary key (
MTL_PHYSICAL_INVENTORIES_PK) and the documented unique indexMTL_PHYSICAL_INVENTORIES_U1. Together they uniquely identify a physical inventory and its owning organization.ORGANIZATION_IDis also a foreign key toMTL_PARAMETERS. - PHYSICAL_INVENTORY_NAME and DESCRIPTION — user-facing identifier and descriptive text for the count.
- PHYSICAL_INVENTORY_DATE and FREEZE_DATE — the scheduled count date and the date on which inventory balances were frozen.
- LAST_ADJUSTMENT_DATE and TOTAL_ADJUSTMENT_VALUE — the date and monetary value of the most recent adjustment posted against the count.
- DEFAULT_GL_ADJUST_ACCOUNT — foreign key to
GL_CODE_COMBINATIONS, defining the accounting flexfield used for adjustment postings. - APPROVAL_REQUIRED, APPROVAL_TOLERANCE_POS, and APPROVAL_TOLERANCE_NEG — control flags and positive/negative tolerance thresholds governing approval workflow.
- COST_VARIANCE_POS and COST_VARIANCE_NEG — monetary variance limits for cost-based approval.
- NEXT_TAG_NUMBER and TAG_NUMBER_INCREMENTS — control generation of physical inventory tag numbers.
- ALL_SUBINVENTORIES_FLAG, EXCLUDE_ZERO_BALANCE, EXCLUDE_NEGATIVE_BALANCE, and DYNAMIC_TAG_ENTRY_FLAG — scope and behavior flags for the count.
- NUMBER_OF_SKUS — count of stock-keeping units included in the physical inventory.
- Standard audit columns (
CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) and theATTRIBUTE_CATEGORY/ATTRIBUTE1–15flexfields.
Common Use Cases and Queries
The table underpins physical inventory setup, execution, and reporting. A typical query retrieves all physical inventories for an organization:
SELECT physical_inventory_id, physical_inventory_name, physical_inventory_date, freeze_date, total_adjustment_value FROM mtl_physical_inventories WHERE organization_id = :org_id ORDER BY physical_inventory_date DESC;- Reconciliation reporting joins to adjustment and tag tables to compute count completeness and variance exposure.
- Audit queries examine
APPROVAL_REQUIRED, tolerance fields, and audit columns to confirm controls were applied. - Period-close reporting aggregates
TOTAL_ADJUSTMENT_VALUEandLAST_ADJUSTMENT_DATEby organization and date.
Related Objects
The documented foreign-key relationships define the principal supporting objects:
- MTL_PARAMETERS — parent for
ORGANIZATION_ID, providing organization defaults. - MTL_PHYSICAL_ADJUSTMENTS — child via
ORGANIZATION_IDandPHYSICAL_INVENTORY_ID; holds posted count adjustments. - MTL_PHYSICAL_INVENTORY_TAGS — child via the same key; stores individual tag entries.
- MTL_PHYSICAL_SUBINVENTORIES — child via the same key; defines subinventories included in the count.
- GL_CODE_COMBINATIONS — referenced through
DEFAULT_GL_ADJUST_ACCOUNTfor accounting validation.
These relationships make MTL_PHYSICAL_INVENTORIES the coordination point for physical inventory execution and post-count accounting in Oracle Inventory.
-
Physical inventory definitions
-
- Retrofitted
APPS.INVFV_PHYSICAL_ADJUSTMENTS·↳ GL_CODE_COMBINATIONS·↳ HR_ALL_ORGANIZATION_UNITS·↳ HR_GENERAL·Explore INV module →
-
- Retrofitted
APPS.INVBV_PHYSICAL_INVENTORIES·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →
-
- Retrofitted
APPS.INVFV_PHYSICAL_INV_COUNTS·↳ HR_ALL_ORGANIZATION_UNITS·↳ HR_GENERAL·↳ HR_PERSON_NAME·Explore INV module →
-
- Retrofitted
APPS.INVBV_PHYSICAL_INVENTORIES·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →
-
Physical inventory definitions
-
- Retrofitted
APPS.INVFV_PHYSICAL_INVENTORIES·↳ HR_ALL_ORGANIZATION_UNITS·↳ MTL_PARAMETERS·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →
-
- Retrofitted
APPS.INVFV_PHYSICAL_INVENTORIES·↳ HR_ALL_ORGANIZATION_UNITS·↳ MTL_PARAMETERS·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →
-
View based on table MTL_PHYSICAL_INVENTORIES. Has all the Physical Inventory Setup Information.
APPS.MTL_PHYSICAL_INVENTORIES_V·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →
-
- Retrofitted
APPS.INVFV_PHYSICAL_INV_COUNTS·↳ HR_ALL_ORGANIZATION_UNITS·↳ HR_GENERAL·↳ HR_PERSON_NAME·Explore INV module →
-
- Retrofitted
APPS.INVFV_PHYSICAL_ADJUSTMENTS·↳ GL_CODE_COMBINATIONS·↳ HR_ALL_ORGANIZATION_UNITS·↳ HR_GENERAL·Explore INV module →
-
View based on table MTL_PHYSICAL_INVENTORIES. Has all the Physical Inventory Setup Information.
APPS.MTL_PHYSICAL_INVENTORIES_V·↳ MTL_PHYSICAL_INVENTORIES·Explore INV module →