Search Results mtl_organizations




Overview
Explore in AI Chat

Overview

MTL_ORGANIZATIONS is an APPS-owned reporting view in the Oracle E-Business Suite Inventory (INV) module that consolidates organization master data, location details, set of books information, and an aggregated on-hand inventory valuation into a single denormalized result set. Unlike the base definitions maintained in ORG_ORGANIZATION_DEFINITIONS and HR_LOCATIONS, this view is designed to present a human-readable, business-friendly picture of each inventory organization. It is classified as a VIEW with VALID status in both Oracle EBS 12.1.1 and 12.2.2, and its structure is consistent across those releases.

The defining characteristic of MTL_ORGANIZATIONS is that it does not merely join descriptive attributes. It also computes an ORG_INFO column that concatenates the organization name, address lines, city, region, postal code, country, and a localized "ON HAND INVENTORY VALUE" label together with the truncated sum of on-hand quantity multiplied by burdened item cost. A separate VALUE column exposes the same numeric total without the descriptive wrapper. This dual presentation makes the view suitable both for direct display in reports and for numeric aggregation in downstream queries.

Underlying Base Objects

The view is defined over a mixture of base tables, synonyms, and views. The documented referenced objects are: CST_ITEM_COSTS (SYNONYM), FND_CURRENCIES (SYNONYM), GL_SETS_OF_BOOKS (VIEW), HR_GENERAL (PACKAGE), HR_LOCATIONS (VIEW), HR_ORGANIZATION_UNITS (VIEW), HR_SECURITY (PACKAGE), MTL_ONHAND_QUANTITIES_DETAIL (SYNONYM), MTL_SYSTEM_ITEMS_B (SYNONYM), and ORG_ORGANIZATION_DEFINITIONS (VIEW).

ORG_ORGANIZATION_DEFINITIONS supplies the organization identity and set of books linkage; MTL_SYSTEM_ITEMS_B and CST_ITEM_COSTS provide item and cost context; MTL_ONHAND_QUANTITIES_DETAIL supplies transaction quantities; HR_LOCATIONS and HR_ORGANIZATION_UNITS provide address and unit data. FND_CURRENCIES and GL_SETS_OF_BOOKS resolve the currency symbol and ledger name. The HR_GENERAL and HR_SECURITY packages are referenced implicitly through the HR views to enforce organization-level security, meaning rows returned can be filtered by the caller's HR security profile.

All joins to the on-hand and item tables are outer joins (indicated by the (+) operator), so organizations with no inventory transactions or no item cost records are still returned, with the value defaulting to zero via NVL.

Key Columns

  • ORGANIZATION_ID — Primary identifier of the inventory organization.
  • ORGANIZATION_CODE — Short code used throughout INV transactions.
  • ORGANIZATION_NAME — Descriptive organization name.
  • ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, COUNTRY, ZIP_CODE — Location attributes sourced from HR_LOCATIONS.
  • SET_OF_BOOKS_ID, SET_OF_BOOKS_NAME — Ledger context for the organization.
  • SYMBOL — Currency symbol from FND_CURRENCIES.
  • ORG_INFO — Fully formatted organization profile including address and on-hand value text.
  • VALUE — Truncated numeric on-hand inventory value.

Common Use Cases and Queries

Typical uses include organization lookup lists in custom concurrent programs, inventory valuation dashboards, and cross-validation of organization and ledger assignments. A representative query selects the core descriptive and valuation columns:

  • SELECT organization_id, organization_code, organization_name, set_of_books_name, value FROM mtl_organizations ORDER BY organization_code;
  • SELECT organization_code, org_info FROM mtl_organizations WHERE value > 0;
  • SELECT o.organization_code, o.city, o.country, o.symbol, o.value FROM mtl_organizations o WHERE o.set_of_books_id = :ledger_id;

Because the view aggregates on-hand quantity against cost, queries returning ORG_INFO or VALUE should be treated as summary-level reporting artifacts rather than transactional sources. For precise valuation at a point in time, the underlying CST_ITEM_COSTS and MTL_ONHAND_QUANTITIES_DETAIL tables remain authoritative.