Search Results icx_por_ctx_tl




Overview
Explore in AI Chat

Overview

The ICX_POR_CTX_TL table is a technical repository within the Oracle iProcurement (ICX) module, specifically designed to enable Advanced Search functionality. Its primary role is to serve as a detail datastore for an Oracle Text (InterMedia) index. The table is architected to store a specialized, pre-formatted text string containing searchable content from transactional data, which is then indexed to facilitate complex, full-text queries within the iProcurement application. It operates as a child table, with ICX_POR_ITEMS_TL as its master, ensuring the searchable text is directly associated with specific procurement item translations.

Key Information Stored

The table's structure is optimized for its role as a Text index datastore. The critical columns include RT_ITEM_ID, which is the foreign key linking to the master item translation record in ICX_POR_ITEMS_TL, and LANGUAGE, which identifies the translation language of the stored text, supporting the multilingual nature of the table (indicated by the '_TL' suffix). The core data is held in a column designed to store the CTX_ string. This string is not raw item description data; it is a purpose-built text field containing concatenated and tagged content from various source attributes, formatted with specific section tags (e.g., <ITEM_NUMBER>, <DESCRIPTION>) that allow the Oracle Text index to perform precise, field-aware searches within the iProcurement Advanced Search interface.

Common Use Cases and Queries

The primary use case is the backend support for the iProcurement "Advanced Search" feature, where users can perform keyword searches across items, descriptions, and other catalog attributes. Direct manipulation or querying of this table by functional users or standard reports is uncommon, as access is typically mediated through the Oracle Text index and the application's search UI. From a technical or administrative perspective, queries might investigate the integrity of the search infrastructure or analyze indexed content. A sample diagnostic query would join to the master items table to see what text is indexed for a given item:

  • SELECT itl.item_description, ictx.ctx_string FROM icx_por_ctx_tl ictx JOIN icx_por_items_tl itl ON ictx.rt_item_id = itl.rt_item_id AND ictx.language = itl.language WHERE itl.item_id = :p_item_id;

Data in this table is populated and maintained by application processes, likely concurrent with updates to the master ICX_POR_ITEMS_TL table or during a scheduled synchronization of the Oracle Text index.

Related Objects

ICX_POR_CTX_TL has defined dependencies within the iProcurement schema, primarily through its foreign key relationships. The documented relationships are:

  • Master Table: ICX_POR_ITEMS_TL - This is the primary parent table. The relationship is enforced via a foreign key where ICX_POR_CTX_TL.RT_ITEM_ID and ICX_POR_CTX_TL.LANGUAGE reference columns in ICX_POR_ITEMS_TL. This ensures every indexed text string is tied to a specific procurement item translation.
  • Oracle Text Index - Although not a traditional database object with a formal FK, the table's existence is predicated on an Oracle Text index (likely named something like ICX_POR_CTX_IDX) configured to use this table as its detail datastore. This index is the consumer of the CTX_STRING data.
  • Table: ICX_POR_CTX_TL 12.1.1

    owner:ICX,  object_type:TABLE,  fnd_design_data:ICX.ICX_POR_CTX_TL,  object_name:ICX_POR_CTX_TL,  status:VALID,  product: ICX - Oracle iProcurement ,  description: ICX_POR_CTX_TL is created for Advanced Search capabilities. The table implements the 'detail-datastore' for intermedia index. This table stores the CTX_ string (with section tags) and has ICX_POR_ITEMS_TL as its Master (rt_item_id ,  implementation_dba_data: ICX.ICX_POR_CTX_TL , 

  • Table: ICX_POR_CTX_TL 12.2.2

    owner:ICX,  object_type:TABLE,  fnd_design_data:ICX.ICX_POR_CTX_TL,  object_name:ICX_POR_CTX_TL,  status:VALID,  product: ICX - Oracle iProcurement ,  description: ICX_POR_CTX_TL is created for Advanced Search capabilities. The table implements the 'detail-datastore' for intermedia index. This table stores the CTX_ string (with section tags) and has ICX_POR_ITEMS_TL as its Master (rt_item_id ,  implementation_dba_data: ICX.ICX_POR_CTX_TL , 

  • Table: ICX_POR_ITEMS_TL 12.2.2

    owner:ICX,  object_type:TABLE,  fnd_design_data:ICX.ICX_POR_ITEMS_TL,  object_name:ICX_POR_ITEMS_TL,  status:VALID,  product: ICX - Oracle iProcurement ,  description: Stores information and root descriptors for translatable data for each item in the catalog. Root descriptor columns are in the format Axxxx where xxxx is an integer. Some of the root descriptors are seeded by Oracle while others may be g ,  implementation_dba_data: ICX.ICX_POR_ITEMS_TL , 

  • Table: ICX_POR_ITEMS_TL 12.1.1

    owner:ICX,  object_type:TABLE,  fnd_design_data:ICX.ICX_POR_ITEMS_TL,  object_name:ICX_POR_ITEMS_TL,  status:VALID,  product: ICX - Oracle iProcurement ,  description: Stores information and root descriptors for translatable data for each item in the catalog. Root descriptor columns are in the format Axxxx where xxxx is an integer. Some of the root descriptors are seeded by Oracle while others may be g ,  implementation_dba_data: ICX.ICX_POR_ITEMS_TL ,