Search Results min_minmax_quantity




Overview
Explore in AI Chat

Overview

The MTL_ITEM_SUB_INVENTORIES_ALL_V view is an Oracle EBS Inventory (INV) dictionary object owned by the APPS schema. In the ETRM metadata for 12.2.2 the object is classified as a VIEW with VALID status, and its documented description is the terse notation "10SC ONLY," indicating that the view is intended for a restricted set of supply-chain flows rather than for general-purpose subinventory reporting. The view consolidates item-level subinventory definitions from MTL_ITEM_SUB_INVENTORIES and enriches each row with descriptions and status information drawn from the item master, the secondary inventory definition, and material status tables. It also flattens organization identifiers into human-readable organization codes and names, which makes it convenient for reporting and for integrations that must resolve an item's sourcing organization without performing additional joins.

Because it exposes SOURCE_TYPE, SOURCE_ORGANIZATION_ID, and SOURCE_SUBINVENTORY, the view is directly relevant to the user search term "source_organization": the underlying SOURCE_ORGANIZATION_ID column is joined outward to HR_ORGANIZATION_UNITS and MTL_PARAMETERS to return the source organization's name and organization code.

Underlying Base Objects

The documented base objects referenced by the view are:

  • MTL_ITEM_SUB_INVENTORIES (SYNONYM) — the driving table, aliased S, supplying item, organization, subinventory, planning, and sourcing attributes.
  • MTL_SECONDARY_INVENTORIES (SYNONYM) — aliased SUB, supplying the subinventory description and status.
  • MTL_SYSTEM_ITEMS (SYNONYM) — aliased I, supplying the item description.
  • MTL_MATERIAL_STATUSES_VL (VIEW) — aliased MMS, supplying the material status code.
  • HR_ORGANIZATION_UNITS (VIEW) — aliased HOU, supplying the source organization name.
  • MTL_PARAMETERS (SYNONYM) — aliased MP, supplying the source organization code.
  • HR_GENERAL and HR_SECURITY (PACKAGEs) — supporting security and organization-access logic invoked through the HR organization views.

The joins to HR_ORGANIZATION_UNITS, MTL_PARAMETERS, and MTL_MATERIAL_STATUSES_VL are all outer joins, so rows whose source organization or material status is not yet defined are still returned. The view is further filtered to subinventories whose SUBNINVENTORY_TYPE is 1 (storage) or null.

Key Columns

Common Use Cases and Queries

Because the view resolves the source organization to both a code and a name, it is commonly used to report item sourcing setups per subinventory, to audit min-max planning definitions, and to feed downstream integrations that require a readable source organization. A representative query is:

  • SELECT inventory_item_id, item_description, organization_id, secondary_inventory, source_type, source_organization_id, organization_code, name, source_subinventory FROM mtl_item_sub_inventories_all_v WHERE source_organization_id IS NOT NULL;
  • SELECT organization_id, secondary_inventory, COUNT(*) FROM mtl_item_sub_inventories_all_v GROUP BY organization_id, secondary_inventory;
  • SELECT inventory_item_id, organization_code, source_subinventory, min_minmax_quantity, max_minmax_quantity FROM mtl_item_sub_inventories_all_v WHERE inventory_planning_code = 2 ORDER BY organization_code, inventory_item_id;

Note that the "10SC ONLY" description in the ETRM metadata restricts the applicability of this view to the relevant process configuration; consumers should confirm that their implementation uses the corresponding functionality before depending on the view for general inventory reporting.