Search Results catalog_status_flag




Overview
Explore in AI Chat

Overview

The view APPS.MTL_CATALOG_SEARCH_ITEMS_ALL_V is an Inventory (INV) module database object that exposes catalog search item data across organizations. In Oracle E-Business Suite 12.1.1 and 12.2.2, it is documented in ETRM under the owner/schema APPS with a status of VALID and a description of "10SC ONLY." This designation indicates that the object is scoped to a specific catalog or seeded configuration context rather than being a general-purpose, customer-facing view. It is used primarily for reporting and integration where a consolidated, organization-aware listing of catalog search items is required, including the item's catalog status and approval status. The view is not a base table; it is a stored SQL definition that joins three underlying objects to produce a denormalized result set.

Underlying Base Objects

The view is defined over three documented base objects, each referenced through APPS synonyms: MTL_CATALOG_SEARCH_ITEMS, MTL_PARAMETERS, and MTL_SYSTEM_ITEMS_B. Its view text performs an equi-join on ORGANIZATION_ID and INVENTORY_ITEM_ID. Specifically, MTL_CATALOG_SEARCH_ITEMS (aliased A) supplies the group handle, item identifier, description, organization, reservable type, primary UOM, and item catalog group. MTL_PARAMETERS (aliased B) provides the organization code through ORGANIZATION_CODE, keyed by ORGANIZATION_ID. MTL_SYSTEM_ITEMS_B (aliased C) contributes the segment-based TEM_NUMBER (from SEGMENT1), the ITEM_CATALOG_GROUP_ID, CATALOG_STATUS_FLAG, and APPROVAL_STATUS. The join condition is A.ORGANIZATION_ID = B.ORGANIZATION_ID AND A.ORGANIZATION_ID = C.ORGANIZATION_ID AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID, ensuring that each row is resolved to a valid organization and item master record.

Key Columns

  • GROUP_HANDLE_ID — Identifier linking catalog search results to a search group handle.
  • INVENTORY_ITEM_ID — Surrogate key of the inventory item; part of the join to the item master.
  • ITEM_NUMBER (TEM_NUMBER) — Item segment value (SEGMENT1 from MTL_SYSTEM_ITEMS_B), presented as the item number.
  • DESCRIPTION — Item description from the catalog search source.
  • ORGANIZATION_ID — Operating unit/inventory organization identifier used in all joins.
  • RESERVABLE_TYPE — Indicates the reservable nature of the catalog search item.
  • ORG_CODE — Organization code derived from MTL_PARAMETERS.ORGANIZATION_CODE.
  • PRIMARY_UOM_CODE — Primary unit of measure for the item.
  • ITEM_CATALOG_GROUP_ID — Catalog group to which the item belongs.
  • CATALOG_STATUS_FLAG — The flag the user searched for; indicates the catalog status of the item, central to filtering active versus inactive catalog entries.
  • APPROVAL_STATUS — Approval status of the item within the catalog context.

Common Use Cases and Queries

Typical usage involves reporting on catalog search items by organization and filtering by catalog status or approval status. A representative query follows:

SELECT organization_id,
       org_code,
       item_number,
       description,
       primary_uom_code,
       catalog_status_flag,
       approval_status
FROM   apps.mtl_catalog_search_items_all_v
WHERE  catalog_status_flag = 'Y'
ORDER BY org_code, item_number;

Integration scenarios may join the view back to MTL_SYSTEM_ITEMS_B or MTL_ITEM_CATALOG_GROUPS for additional attribute resolution, though the view already exposes the most commonly referenced columns. Because the documented description states "10SC ONLY," implementers should validate availability and row scope against their specific EBS 12.1.1 or 12.2.2 instance before relying on it as a general catalog reporting source.