Search Results iex_query_temp_xref

Overview

IEX_QUERY_TEMP_XREF is a table owned by the IEX schema within the Oracle E-Business Suite Collections module (Advanced Collections). It functions as a cross-reference table that links Advanced Collections SQL queries to the XDO (XML Publisher / BI Publisher) templates used to render them. In practical terms, it defines which template is applied to which saved collection query definition, providing the bridge between the query logic stored in the collections engine and the presentation layer managed by the XDO / XML Publisher framework.

The table carries a documented primary key constraint, IQTX_PK, defined on the QUERY_TEMP_ID column, and a documented unique index, IEX_QUERY_TEMP_XREF_U2, defined on the (QUERY_ID, TEMPLATE_ID) column pair. Based on the heuristic Data Vault classification supplied in the metadata — "standalone" — this object is best modeled as an independent satellite or reference table rather than a hub or link, since it does not participate in a classic parent/child FK fan-out of its own. It is referenced by other tables (notably IEX_XML_REQUEST_HISTORIES) but does not itself reference parent tables through foreign keys, reinforcing its role as a lookup/reference entity.

Key Information Stored

The table contains nine documented columns in the 12.2.2 schema. The most operationally significant are:

• QUERY_TEMP_ID — the surrogate primary key (IQTX_PK) uniquely identifying each query-to-template cross-reference row.
• QUERY_ID — identifies the Advanced Collections SQL query definition. Along with TEMPLATE_ID, it forms the business-key unique index IEX_QUERY_TEMP_XREF_U2.
• TEMPLATE_ID — identifies the XDO / XML Publisher template associated with the query; the second component of the U2 business key.
• CREATED_BY, CREATION_DATE — standard WHO columns capturing the creating user and timestamp.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns tracking the most recent modification and the login session that performed it.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF framework to detect concurrent updates.

The distinction between the surrogate key (QUERY_TEMP_ID) and the business-key candidate (QUERY_ID + TEMPLATE_ID) is important: applications resolving a template for a given query should query on the U2 columns, while downstream referential links such as those from IEX_XML_REQUEST_HISTORIES use QUERY_TEMP_ID.

Common Use Cases and Queries

Typical scenarios involve determining which XML Publisher template will be used when a collections query is executed, or auditing the query/template mapping after a configuration change, patch, or template migration.

• Retrieve all template mappings for a specific query:
  SELECT query_temp_id, template_id FROM iex.iex_query_temp_xref WHERE query_id = :p_query_id;
• Find queries using a given template:
  SELECT query_id FROM iex.iex_query_temp_xref WHERE template_id = :p_template_id;
• Join to configuration or history tables to trace which template rendered a delivered correspondence: join QUERY_TEMP_ID to IEX_XML_REQUEST_HISTORIES.QUERY_TEMP_ID.
• Reporting on stale mappings by inspecting LAST_UPDATE_DATE to identify entries not modified since a given release upgrade.

Related Objects

The most significant related objects, grounded in the documented FK relationship data, are:

• IEX_XML_REQUEST_HISTORIES — references this table via QUERY_TEMP_ID → IEX_QUERY_TEMP_XREF.QUERY_TEMP_ID (or its primary key). This child table records XML request execution history for correspondence generation.
• XDO templates (XDO_TEMPLATES_B / XDO_LOBS) — resolution of TEMPLATE_ID to the underlying XML Publisher template definition.
• Advanced Collections query definitions — the parent query objects identified by QUERY_ID.

Because the object is classified as standalone, its integration is predominantly through application logic (the Collections correspondence engine) rather than declared foreign-key constraints, so impact analysis should be driven by the U2 business key and the QUERY_TEMP_ID reference from IEX_XML_REQUEST_HISTORIES.