Search Results apps_ts_archive




Overview
Explore in AI Chat

Overview

The ICX_POR_CTX_TL table is a core technical component within the Oracle iProcurement (ICX) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. Its primary role is to support the Advanced Search functionality for catalog items. The table implements a 'detail-datastore' for an Oracle Text (InterMedia) index, which enables complex, full-text linguistic searches across multilingual item descriptions and attributes. It is a translation table (indicated by the '_TL' suffix) that stores language-specific searchable text strings, with each record corresponding to a single language. The table is stored in the APPS_TS_ARCHIVE tablespace, a detail noted in the user's search context.

Key Information Stored

The table's structure is designed to feed the Oracle Text engine. While the provided metadata does not list specific column names, the documentation states it stores "CTX_ string (with section tags)" and that each record populates only one of these CTX_ columns based on language. The key relational column is RT_ITEM_ID, which serves as the foreign key to join to its master table, ICX_POR_ITEMS_TL. This relationship allows the search index to be synchronized with the transactional item data. The table's storage is characterized by a large number of system-generated LOB indexes (e.g., SYS_IL0000149130C00001$$), all uniquely stored in the APPS_TS_ARCHIVE tablespace, which manage the text data stored in the CTX_ columns.

Common Use Cases and Queries

This table is primarily accessed indirectly by the Oracle Text search engine during iProcurement catalog searches. Direct manual queries are uncommon but may be necessary for diagnostic or maintenance tasks. A typical use case involves investigating the text data stored for a specific item. A sample query would join to the master translation table using the documented RT_ITEM_ID key:

  • SELECT itl.item_description, ctx.* FROM icx.icx_por_items_tl itl, icx.icx_por_ctx_tl ctx WHERE itl.rt_item_id = ctx.rt_item_id AND itl.language = 'US' AND itl.item_id = :p_item_id;

Another critical scenario is monitoring the growth and storage of this table within the APPS_TS_ARCHIVE tablespace, as the LOB data for global, multilingual deployments can be substantial.

Related Objects

The ICX_POR_CTX_TL table has a direct and documented master-detail relationship with the ICX_POR_ITEMS_TL table. The joining key is RT_ITEM_ID from ICX_POR_CTX_TL to ICX_POR_ITEMS_TL. This table is a foundational datastore for one or more Oracle Text indexes, which are the objects that applications and users actually query. Administrators should also be aware of dependencies on the APPS_TS_ARCHIVE tablespace for storage. Performance and management of the associated LOB indexes (listed in the metadata) are also intrinsically tied to this table.