Search Results icx_por_cat_data_source_uk1

Overview

ICX_POR_CATEGORY_DATA_SOURCES is a table owned by the ICX schema within Oracle iProcurement (Oracle E-Business Suite 12.1.1 and 12.2.2). It stores a list of external category data sources — that is, references to non-Oracle repositories (for example external punchout suppliers, procurement content providers, or third-party catalog services) from which category content is drawn into iProcurement. Its purpose is to map internal category keys to the identity of the external source and the corresponding key used within that source, enabling iProcurement to resolve where a given category originated and how it should be retrieved or refreshed.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault model. In modeling terms, because no foreign-key dependencies are documented, the table is best treated as an independent reference table rather than as an explicit hub, link, or satellite, though its business key naturally functions in a hub-like role for external category source identity.

Key Information Stored

The documented physical schema contains thirteen columns. The most significant are the following.

• EXTERNAL_SOURCE — Identifies the external category data source (the provider or system name). This column is the first component of the primary key and is the attribute most commonly referenced by the search term "external_source."
• EXTERNAL_SOURCE_KEY — The identifier of the category as understood by that external source. Together with EXTERNAL_SOURCE it forms the unique business key.
• CATEGORY_KEY — The internal iProcurement category key that the external source entry resolves to.
• RT_CATEGORY_ID — The internal RT (reference term) category identifier associated with the mapping.
• REQUEST_ID — The concurrent request that created or last updated the row.
• PROGRAM_APPLICATION_ID and PROGRAM_ID — Identify the concurrent program responsible for the row's last update.
• PROGRAM_UPDATE_DATE — The date the concurrent program processed the record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns capturing who created and last modified the row and when.

Two unique indexes are documented: ICX_POR_CAT_DATA_SOURCE_UK1 (the primary key on EXTERNAL_SOURCE and EXTERNAL_SOURCE_KEY) and ICX_POR_CATEGORY_DATA_SRCS_U1 (on the same two columns, serving as an alternate unique business-key candidate). The uniqueness constraint ensures a single mapping per (source, source key) pair. No surrogate single-column key is documented; the composite pair serves as the business key.

Common Use Cases and Queries

Typical scenarios include determining which categories are sourced from a particular external provider, validating that every external key resolves to an internal category, and troubleshooting content refresh failures. A common query filters by source:

SELECT external_source, external_source_key, category_key, rt_category_id
FROM icx.icx_por_category_data_sources
WHERE external_source = :p_source;

To find categories lacking a mapped internal key, filter on CATEGORY_KEY IS NULL; to audit recent maintenance, use LAST_UPDATE_DATE or REQUEST_ID against concurrent request history. Reporting use cases include external source coverage reports, mapping exception extracts for data stewards, and reconciliation between procurement content loads and category assignments.

Related Objects

The ETRM metadata documents this table as standalone with no foreign-key dependencies; relationships are therefore inferred from shared key columns rather than enforced constraints.

• ICX_POR_CATEGORIES — Related via CATEGORY_KEY, holding the internal category definitions.
• ICX_POR_CATEGORIES_TL — Provides translated category names for reporting.
• ICX_POR_CATALOGS — Links external catalog content that references category sources.
• POZ_CATEGORIES / FND_FLEX_VALUES — Source-of-truth category and value-set definitions referenced by CATEGORY_KEY.
• FND_CONCURRENT_REQUESTS — Joined on REQUEST_ID to trace load jobs.
• FND_CONCURRENT_PROGRAMS — Joined via PROGRAM_APPLICATION_ID and PROGRAM_ID to identify processing programs.

Related PL/SQL APIs and concurrent programs governing external category content typically write and read exactly these columns, making the table a central reference point for external source integration in iProcurement.