Results for “apps_ts_archive”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
ICX.ICX_POR_CTX_TL is a translation table within the Oracle E-Business Suite Internet Computing Components (ICX) schema. It exists specifically to support the Advanced Search functionality of the Procurement Buyer's Workbench and related iProcurement catalog browsing features. The table implements the detail-datastore component of an Intermedia full-text index, holding the fully indexed, section-tagged text extracted from purchasable items. The "TL" suffix confirms it is a translation (multi-language) table, and each row corresponds to exactly one language, populating a single language-specific CTX_ column.
From a Data Vault modeling perspective, the provided metadata's heuristic classification is satellite-leaning. This is a reasonable suggestion: the table carries descriptive, non-key context (the indexed text) that hangs off the purchaser-facing item master, and the RT_ITEM_ID / LANGUAGE pair functions as the natural business key of the parent rather than as an independent hub identifier.
Key Information Stored
The table comprises 40 documented columns. The primary key is ICX_POR_CTX_TL_PK, a composite of RT_ITEM_ID and LANGUAGE, which distinguishes the surrogate join key from the language qualifier. RT_ITEM_ID is a foreign key to ICX.ICX_POR_ITEMS_TL and identifies the parent item record; LANGUAGE determines which CTX_ column is populated for that row.
The substantive payload is distributed across roughly thirty language-specific CLOB columns, each holding the Intermedia-indexed text with embedded section tags. Examples from the documented metadata include CTX_US for US English, CTX_GB, CTX_FRC, CTX_ESA, and CTX_PTB, alongside numerous European, Asian, and Middle Eastern variants such as CTX_JA, CTX_KO, CTX_ZHS, CTX_ZHT, CTX_AR, and CTX_IW. Supporting columns include SEQUENCE, which orders the text fragments, and the twenty-six SYS_IL LOB indexes, each unique and resident in APPS_TS_ARCHIVE. The table itself resides in the APPS_TS_ARCHIVE tablespace with PCTFREE 10, matching the user's search term and indicating an archival/read-mostly storage posture.
Common Use Cases and Queries
The principal use case is supporting keyword-driven catalog search, where Oracle Text queries the Intermedia index built over the CTX_ columns rather than the base item descriptions. A typical diagnostic query joins the detail store back to its master to retrieve the indexed text for a given language:
SELECT c.rt_item_id, c.ctx_us FROM icx.icx_por_ctx_tl c WHERE c.rt_item_id = :item_id AND c.language = 'US';- Language coverage reporting: aggregate row counts grouped by LANGUAGE to confirm which locales are indexed.
- LOB storage auditing: query DBA_LOBS for table_name = 'ICX_POR_CTX_TL' to validate segment sizes in APPS_TS_ARCHIVE.
- Integrity checks: identify rows whose populated CTX_ column does not match their LANGUAGE value, or orphans with no matching RT_ITEM_ID.
- Index maintenance: rebuild or synchronize the associated Oracle Text index after bulk item loads.
Related Objects
The most significant dependent relationship is the master-detail link to ICX.ICX_POR_ITEMS_TL, joined on RT_ITEM_ID. This parent supplies the item identity and description context absent from the CTX table itself. Related objects include the Oracle Text (Intermedia) index definitions associated with the CTX_ columns, the ICX.ICX_POR_ITEMS base table that underlies the translation layer, and the iProcurement and Procurement Buyer's Workbench search APIs that consume these indexed results. Verification scripts and ETRM diagnostics frequently reference ICX_POR_ITEMS_TL and the SYS_IL LOB segments when troubleshooting search or storage issues.
-
TABLE: ICX.ICX_POR_CTX_TL 12.2.2
-
TABLE: ICX.ICX_POR_CTX_TL 12.1.1
-
TABLE: CS.CS_REPAIRS_ALL 12.2.2
-
TABLE: HRI.HRI_MB_ABV 12.1.1
-
TABLE: HRI.HRI_MB_ABV 12.2.2
-
TABLE: BIM.BIM_EDW_LEADS_F 12.2.2
-
TABLE: CS.CS_REPAIRS_ALL 12.1.1
-
TABLE: BIM.BIM_EDW_LEADS_F 12.1.1
-
TABLE: OSM.AS_PROMOTIONS_ALL 12.1.1
-
TABLE: OSM.AS_PROMOTIONS_ALL 12.2.2
-
TABLE: IBY.IBY_G_T_MESSAGES 12.1.1
-
TABLE: AR.RA_CONTACTS 12.1.1
-
TABLE: AR.RA_ADDRESSES_ALL 12.1.1
-
TABLE: PV.PV_COUNTRY_RSBLTS 12.1.1
-
TABLE: PV.PV_COUNTRY_RSBLTS 12.2.2
-
TABLE: AR.RA_CONTACTS 12.2.2
-
TABLE: OSM.AS_TODO_LISTS_ALL 12.1.1
-
TABLE: OSM.AS_TODO_LISTS_ALL 12.2.2
-
TABLE: AR.RA_PHONES 12.2.2