Search Results jai_inv_subinv_dtls




Overview
Explore in AI Chat

Overview

JAI_INV_SUBINV_DTLS is a table owned by the JA schema and delivered as part of the Asia/Pacific Localizations module in Oracle EBS 12.1.1 and 12.2.2. The table stores information about subinventories belonging to a specific inventory organization and physical location. It extends the standard Oracle Inventory subinventory model by associating each subinventory with a location and by capturing regional attributes such as BONDED and TRADING, which are relevant to customs, excise, and bonded-warehouse reporting requirements across Asia/Pacific legislations.

From a modeling perspective, the heuristic Data Vault classification for this object is link. The table primarily resolves relationships between an inventory organization (HR_ALL_ORGANIZATION_UNITS) and a location (HR_LOCATIONS_ALL), qualified by the subinventory name. It is not a standalone hub because it references external business entities rather than defining its own, and it carries limited descriptive context rather than a full satellite payload of historical attributes.

Key Information Stored

The documented physical schema contains 11 columns. The most significant are:

  • ORGANIZATION_ID — identifies the inventory organization that owns the subinventory; foreign key to HR_ALL_ORGANIZATION_UNITS.
  • LOCATION_ID — identifies the physical location associated with the subinventory; foreign key to HR_LOCATIONS_ALL.
  • SUB_INVENTORY_NAME — the descriptive name of the subinventory within the organization and location.
  • BONDED — indicates whether the subinventory is treated as a bonded (customs-controlled) warehouse.
  • TRADING — identifies whether the subinventory is used for trading purposes, supporting localization reporting.
  • OBJECT_VERSION_NUMBER — the optimistic-locking column used by the Oracle EBS framework.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns that track record provenance and change history.

The surrogate primary key is defined by JAI_INV_SUBINV_DTLS_PK, which is a composite key over (ORGANIZATION_ID, LOCATION_ID, SUB_INVENTORY_NAME). Because the same unique index also functions as the business-key candidate, the primary key and the natural business key coincide in the documented metadata.

Common Use Cases and Queries

Typical usage centers on identifying subinventories by organization and location, and on reporting bonded or trading subinventories for local regulatory purposes. A common query pattern joins the table to organization and location tables:

  • Resolving organization names: join ORGANIZATION_ID to HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID.
  • Resolving location details: join LOCATION_ID to HR_LOCATIONS_ALL.LOCATION_ID.
  • Filtering for bonded warehouses: WHERE BONDED = 'Y'.
  • Filtering for trading subinventories: WHERE TRADING = 'Y'.
  • Reporting the subinventory landscape per organization: grouping by ORGANIZATION_ID and LOCATION_ID.

These patterns are used in localization reporting extracts, customs reconciliations, and integration routines that need to resolve the physical location of a subinventory for a given inventory organization.

Related Objects

The following objects are most directly related through the documented foreign keys and shared subinventory identification:

  • HR_ALL_ORGANIZATION_UNITS — referenced via JAI_INV_SUBINV_DTLS.ORGANIZATION_ID.
  • HR_LOCATIONS_ALL — referenced via JAI_INV_SUBINV_DTLS.LOCATION_ID.
  • MTL_SECONDARY_INVENTORIES — the standard subinventory definition table, joined on organization and subinventory name.
  • MTL_PARAMETERS — defines the inventory organization and is joined on ORGANIZATION_ID.
  • MTL_ITEM_LOCATIONS — links subinventories to locations and is joined on organization, subinventory, and location.

Together these objects provide the organizational, locational, and inventory context required to interpret the rows held in JAI_INV_SUBINV_DTLS.