Results for “ctx_ptb”

18 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ICX_POR_CTX_TL is an Oracle iProcurement (ICX) table created to support Advanced Search capabilities within the E-Business Suite. Functionally, it implements the detail-datastore for an Oracle Text (intermedia) index. The table stores the CTX_ searchable string — a concatenated, section-tagged document fragment — that the Text index consumes when building and maintaining the searchable content for iProcurement catalog items. Because the indexed content is stored separately from the base item records, this table acts as a dedicated staging surface for full-text search rather than as a transactional store.

The table is registered in schema ICX with a documented physical schema of 40 columns in ETRM 12.2.2, and it exists with VALID status. It is present in both the 12.1.1 and 12.2.2 releases, since the iProcurement Advanced Search architecture did not fundamentally change between those versions.

From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as satellite-leaning. This is a modeling suggestion rather than a physical designation: the table carries descriptive, version-adjacent content keyed by a parent item identifier plus language, and it holds no independent business identity of its own. In Data Vault terms it behaves as a descriptive satellite hanging off the item hub, with RT_ITEM_ID and LANGUAGE forming its composite grain.

Key Information Stored

The most important structural element is the primary key ICX_POR_CTX_TL_PK, defined over (RT_ITEM_ID, LANGUAGE). RT_ITEM_ID is the surrogate identifier of the master item record, and LANGUAGE qualifies which linguistic version of the indexed text is stored. Together they provide the uniqueness constraint that the Text datastore requires.

The remaining documented columns fall into two groups. The first group comprises the language-specific CTX_ columns, each holding the section-tagged search string for a particular locale — for example CTX_US (U.S. English), CTX_GB, CTX_CA, CTX_AR, CTX_ZHS and CTX_ZHT (Simplified and Traditional Chinese), CTX_JA, CTX_KO, CTX_DE, CTX_FR, CTX_ES, CTX_PT and CTX_PTB, CTX_IT, CTX_NL, CTX_PL, CTX_RU, CTX_TR, CTX_SV/SF-style Scandinavian variants, and others across the 40-column schema. The second group comprises the RT_ITEM_ID and LANGUAGE key columns and the SEQUENCE column, which orders the stored fragments.

Notably, the documented unique indexes are all Oracle Text domain indexes (SYS_IL...$$ objects) created on the CTX_ columns rather than business-key candidates. The true business key is expressed solely through ICX_POR_CTX_TL_PK; there is no separate alternative unique constraint on the item identifier alone.

Common Use Cases and Queries

The primary use case is Advanced Search across iProcurement catalog items, where the Text index on the CTX_ columns resolves keyword and section-scoped queries. A typical diagnostic query checks whether indexed content exists for a given item and language:

  • SELECT rt_item_id, language, sequence FROM icx.icx_por_ctx_tl WHERE rt_item_id = :item_id AND language = :lang;
  • SELECT COUNT(*) FROM icx.icx_por_ctx_tl GROUP BY language; — used to verify per-locale coverage.
  • Joining back to the master to confirm synchronization: SELECT t.rt_item_id FROM icx.icx_por_ctx_tl t, icx.icx_por_items_tl m WHERE t.rt_item_id = m.rt_item_id AND t.language = m.language.
  • Full-text probing via the Text index, for example using CONTAINS on a CTX_ column to confirm section tags resolve correctly during index maintenance.

Administrators also use row counts and last-updated checks here when troubleshooting stale or missing search results after concurrent program runs.

Related Objects

The most significant related object is ICX_POR_ITEMS_TL, the documented master of this table. The foreign key ICX_POR_CTX_TL.RT_ITEM_ID and ICX_POR_CTX_TL.LANGUAGE both reference ICX_POR_ITEMS_TL, making the join on RT_ITEM_ID and LANGUAGE the canonical relationship.

  • ICX_POR_ITEMS_TL — master item translation table; join on RT_ITEM_ID and LANGUAGE.
  • ICX_POR_ITEMS_B / item base tables — supply the item identity that flows through the master.
  • Oracle Text index objects (SYS_IL...$$ indexes on the CTX_ columns) — the datastore consumers.
  • iProcurement Advanced Search concurrent programs and public APIs that populate and refresh this datastore.
  • ICX catalog and category assignment tables that drive which items become searchable.

Because the table is a detail-datastore satellite, it should generally be treated as read-mostly reference data and not updated outside the iProcurement indexing process.