Search Results ego_item_text_tl




Overview
Explore in AI Chat

Overview

EGO_ITEM_TEXT_TL is a table in the EGO schema (Advanced Product Catalog) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to maintain the Oracle Text index used by Item Search. Oracle Text provides full-text search capabilities against item attributes, and this table stores the localized text strings that are indexed so that users can perform keyword and linguistic searches across the item master from the Product Workbench and related catalog search interfaces.

The table is registered as VALID and owned by EGO. From a Data Vault modeling perspective, the heuristic classification derived from the mined FK structure is standalone. This suggests the table functions as an independent entity without strong parent-child dependencies in the FK graph, aside from a single reference to MTL_ITEM_CATALOG_GROUPS_B. The inclusion of a LANGUAGE column and a localized unique key (ITEM_ID, ORG_ID, LANGUAGE, ITEM_CODE) reflects its role as a multilingual satellite of item search text, though the formal Data Vault classification remains standalone.

Key Information Stored

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

  • ITEM_ID — Identifies the item whose searchable text is stored; a leading column of the unique index.
  • ORG_ID — Operating unit / inventory organization context, ensuring text is scoped per organization.
  • LANGUAGE — The language of the stored text, enabling multilingual Oracle Text indexing.
  • ITEM_CODE — The item number or code, another business-key component in the unique index.
  • SOURCE_LANG — Indicates the source language from which the text was derived, supporting translation tracking.
  • TEXT — The primary indexable text string used by Oracle Text for item search.
  • TEXT2 — An additional text column, typically used for a supplementary searchable attribute or concatenated keywords.
  • ITEM_CATALOG_GROUP_ID — References MTL_ITEM_CATALOG_GROUPS_B, linking the text record to a catalog group.
  • INVENTORY_ITEM_ID — The inventory-level item identifier, correlating with MTL_SYSTEM_ITEMS_B.
  • ID_TYPE — Categorizes the type of identifier or text record being indexed.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified each row.

The documented unique index EGO_ITEM_TEXT_TL_U1 covers (ITEM_ID, ORG_ID, LANGUAGE, ITEM_CODE), which serves as the business-key candidate. A system-generated LOB index (SYS_IL0000325060C00015$$) exists, confirming that one of the columns — most likely TEXT or TEXT2 — is stored as a LOB/CLOB to hold large searchable content.

Common Use Cases and Queries

Typical uses involve diagnosing search behavior, validating indexed text, and reporting on searchable item descriptions. A common query retrieves the indexed text for a given item and language:

  • SELECT ITEM_ID, ORG_ID, LANGUAGE, TEXT, TEXT2 FROM EGO.ITEM_TEXT_TL WHERE ITEM_ID = :p_item_id AND LANGUAGE = 'US';
  • Joining to catalog groups: SELECT t.ITEM_ID, g.SEGMENT1 FROM EGO.ITEM_TEXT_TL t, MTL_ITEM_CATALOG_GROUPS_B g WHERE t.ITEM_CATALOG_GROUP_ID = g.CATALOG_GROUP_ID;
  • Auditing recent changes: SELECT ITEM_ID, LANGUAGE, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM EGO.ITEM_TEXT_TL ORDER BY LAST_UPDATE_DATE DESC;
  • Verifying multilingual coverage: SELECT ITEM_ID, COUNT(DISTINCT LANGUAGE) FROM EGO.ITEM_TEXT_TL GROUP BY ITEM_ID HAVING COUNT(DISTINCT LANGUAGE) < 2;

These patterns assist in troubleshooting missing search results, confirming index synchronization after item changes, and building operational reports on catalog content.

Related Objects

  • MTL_ITEM_CATALOG_GROUPS_B — Referenced via ITEM_CATALOG_GROUP_ID; defines the catalog group to which text records belong.
  • MTL_SYSTEM_ITEMS_B — The base item master; join on INVENTORY_ITEM_ID or ITEM_ID to obtain item attributes.
  • EGO_ITEM_TEXT_TL — The table itself plus its unique index EGO_ITEM_TEXT_TL_U1.
  • EGO_ITEM_SEARCH / EGO_ITEM_SEARCH_CRITERIA — Search infrastructure tables that consume the Oracle Text index maintained here.
  • EGO_ITEM_EXT_B / EGO_ITEM_EXT_TL — Extended item attribute tables commonly joined for richer search content.
  • FND_LANGUAGES — Provides valid values for the LANGUAGE column.
  • MTL_ITEM_CATEGORIES / MTL_CATEGORIES_B — Category assignments frequently combined with catalog group data for reporting.