Search Results tem_number

Overview

APPS.MTL_CATALOG_SEARCH_ITEMS_ALL_V is a reporting and integration view in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It exposes catalog search results generated by the Oracle iProcurement and Oracle Purchasing catalog search infrastructure, joining staged search results against item master and inventory organization data. The view presents a denormalized, query-ready record set that combines the transient catalog search staging table with the descriptive attributes of the item and its owning inventory organization.

The view is owned by the APPS schema and is documented in ETRM as being defined over the synonyms MTL_CATALOG_SEARCH_ITEMS, MTL_PARAMETERS, and MTL_SYSTEM_ITEMS_B. Its primary role is to make catalog search results readable without requiring the consumer to resolve the GROUP_HANDLE_ID mechanism or rejoin the item master manually.

Underlying Base Objects

The view text confirms three sources joined on organization and item identity:

• MTL_CATALOG_SEARCH_ITEMS (A) — the driving table holding staged catalog search result rows keyed by GROUP_HANDLE_ID, INVENTORY_ITEM_ID, and ORGANIZATION_ID.
• MTL_PARAMETERS (B) — the inventory organization definition, joined on ORGANIZATION_ID to supply ORGANIZATION_CODE.
• MTL_SYSTEM_ITEMS_B (C) — the item master, joined on ORGANIZATION_ID and INVENTORY_ITEM_ID to supply SEGMENT1, catalog group, and status attributes.

All three are referenced as APPS synonyms in the documented metadata. The join predicates are strictly organization-scoped, since MTL_SYSTEM_ITEMS_B is organization-specific in EBS.

Key Columns

• GROUP_HANDLE_ID — identifier grouping the rows produced by a single catalog search execution.
• INVENTORY_ITEM_ID — the item's internal unique identifier.
• TEM_NUMBER — aliased from C.SEGMENT1; this is the item number, which corresponds directly to the user's search term "tem_number" and is the column most frequently referenced by reporting tools.
• DESCRIPTION — item description as staged in the catalog search results.
• ORGANIZATION_ID and ORG_CODE — the inventory organization identifier and its code (from MTL_PARAMETERS.ORGANIZATION_CODE).
• RESERVABLE_TYPE — the staging-level reservable indicator for the search result.
• PRIMARY_UOM_CODE — the item's primary unit of measure.
• ITEM_CATALOG_GROUP_ID, CATALOG_STATUS_FLAG, and APPROVAL_STATUS — catalog group assignment and status indicators from the item master, useful for filtering purchasable or approved items.

Common Use Cases and Queries

Typical uses include validating catalog search output, diagnosing why an item appears or fails to appear in iProcurement search, and building custom catalog extracts. A straightforward query by item number is:

• SELECT inventory_item_id, tem_number, description, org_code, primary_uom_code, approval_status FROM apps.mtl_catalog_search_items_all_v WHERE tem_number = 'ITEM-1001';
• SELECT group_handle_id, COUNT(*) FROM apps.mtl_catalog_search_items_all_v GROUP BY group_handle_id;
• SELECT tem_number, org_code, catalog_status_flag, approval_status FROM apps.mtl_catalog_search_items_all_v WHERE organization_id = :org_id AND approval_status = 'A';

Because the driving rows originate from catalog search staging, results reflect a specific search execution rather than a complete item listing; for full item master reporting, MTL_SYSTEM_ITEMS_B or the item master views should be used instead.