Results for “reservable_type”

50+ results




Overview
Explore in AI Chat

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

Overview

MTL_CATALOG_SEARCH_ITEMS is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the transient result set produced by catalog and item search operations. When a user performs a keyword or attribute-based search against the item master through the iProcurement, Purchasing, or Inventory catalog search interface, the matching items are materialized into this table and subsequently retrieved for display. The table therefore functions as a server-side result cache, binding a logical search invocation (identified by GROUP_HANDLE_ID) to the specific inventory items returned.

The object is owned by the INV schema and is documented as VALID in the ETRM repository. Mined from its foreign key topology, the table exhibits satellite-leaning characteristics in a Data Vault modeling sense: it holds descriptive, query-dependent attributes (description, UOM, reservable type) attached to the item/organization business key rather than acting as an independent hub of business entities. This classification is a heuristic suggestion; functionally the table is a staging/cache construct rather than a durable master data store.

Key Information Stored

The table comprises six documented columns. The composite primary key MTL_CATALOG_SEARCH_ITEMS_PK spans GROUP_HANDLE_ID, INVENTORY_ITEM_ID, and ORGANIZATION_ID, and a unique index MTL_CATALOG_SEARCH_ITEMS_U1 covers the same three columns, making them the business-key candidates for the row.

  • GROUP_HANDLE_ID — Surrogate handle identifying a single search invocation; all rows sharing this value belong to one result set. Generated when the search executes and used to retrieve the cached results.
  • INVENTORY_ITEM_ID — Foreign key to MTL_SYSTEM_ITEMS_B, identifying the item returned by the search.
  • ORGANIZATION_ID — Together with INVENTORY_ITEM_ID, forms the composite foreign key to MTL_SYSTEM_ITEMS_B, scoping the item to a specific inventory organization.
  • DESCRIPTION — Descriptive text for the item as presented in search results, avoiding a join back to the item master for display purposes.
  • PRIMARY_UOM_CODE — The primary unit of measure for the returned item, used in result rendering and quantity context.
  • RESERVABLE_TYPE — Indicates the reservability classification of the item, relevant to downstream requisition and ordering flows.

The denormalized DESCRIPTION, PRIMARY_UOM_CODE, and RESERVABLE_TYPE columns distinguish this as a satellite-style projection: attributes are cached at search time rather than resolved on demand.

Common Use Cases and Queries

The dominant use case is retrieval of a cached search result set for UI display. A typical query joins the cache to the item master to enrich results with additional attributes:

  • Retrieve a result set: SELECT * FROM mtl_catalog_search_items WHERE group_handle_id = :handle;
  • Enrich with item master detail: SELECT csi.inventory_item_id, csi.organization_id, csi.description, msi.item_number FROM mtl_catalog_search_items csi JOIN mtl_system_items_b msi ON csi.inventory_item_id = msi.inventory_item_id AND csi.organization_id = msi.organization_id WHERE csi.group_handle_id = :handle;
  • Diagnostics: counting rows per GROUP_HANDLE_ID reveals search result sizes and can assist in tuning catalog search performance.
  • Reporting: aggregating frequent item hits across handles supports demand and sourcing analysis, though the table is transient and should not be treated as an audit source.
  • Housekeeping: because rows are ephemeral, DBAs periodically purge stale handles to control table growth.

Related Objects

  • MTL_SYSTEM_ITEMS_B — Referenced via the composite foreign key (INVENTORY_ITEM_ID, ORGANIZATION_ID); the authoritative item master supplying validated item and organization context.
  • MTL_SYSTEM_ITEMS_TL — Provides translated descriptions and item names for display in multilingual search results.
  • MTL_ITEM_CATEGORIES / MTL_CATEGORIES_B — Category-based search filtering frequently resolves through these category assignment tables.
  • MTL_UNITS_OF_MEASURE — Validates and describes the PRIMARY_UOM_CODE value cached in each row.
  • MTL_PARAMETERS — Supplies organization-level defaults and reservable-type behavior relevant to the RESERVABLE_TYPE column.
  • Catalog search concurrent programs and the iProcurement/Purchasing search APIs — Populate and consume MTL_CATALOG_SEARCH_ITEMS, generating GROUP_HANDLE_ID values and deleting results after display.