Search Results sys_il0000402263c00038




Overview
Explore in AI Chat

Overview

The table IEX.IEX_XML_REQUEST_HISTORIES is a transactional history table owned by the IEX (Intelligence in Execution / Collections) schema in Oracle EBS 12.1.1 and 12.2.2. It records the lifecycle of XML-based document generation and delivery requests produced by Oracle XML Publisher (XDO) on behalf of the Advanced Collections module. Each row captures a single delivery attempt of a generated document — typically a dunning letter, delinquency notice, or statement — including the XML payload, the rendered document, the delivery method, the destination, and the final delivery status.

From a Data Vault modeling perspective, this object is classified heuristically as a hub. It carries a distinct business identifier (XML_REQUEST_ID) along with descriptive attributes such as status, method, destination, and object linkage. In practice it functions as a standalone transactional record rather than a dependent child of another entity, though it references several dimension-like parents through foreign keys.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Non-unique indexes support lookups by creation date, delivery status/worker, and request ID. Because it stores multiple large object (LOB) columns, six LOB indexes (SYS_IL...) are defined on the Binary and Character Large Object columns to manage out-of-line LOB storage.

Key Information Stored

The most significant columns in this table include:

  • XML_REQUEST_ID – The surrogate key and unique business identifier; the request ID returned from XDO when the document was generated.
  • QUERY_TEMP_ID – The template identifier used to produce the document; foreign key to IEX_QUERY_TEMP_XREF.
  • STATUS – The delivery status of the request (e.g., queued, sent, failed).
  • METHOD – The delivery channel: email, fax, or printer.
  • DESTINATION – The delivery target (email address, phone number, or printer name).
  • DOCUMENT – BLOB holding the rendered document output.
  • XMLDATA – CLOB containing the XML data assembled for the template.
  • HTML_DOCUMENT – The HTML rendition of the delivered document.
  • OBJECT_TYPE / OBJECT_ID – The type and identifier of the Collections object being delivered.
  • VIEW_BY – The level at which the document was delivered: customer, account, site, or delinquency.
  • CUST_ACCOUNT_ID – The delivered customer account; foreign key to HZ_CUST_ACCOUNTS.
  • CUST_SITE_USE_ID – The delivered customer site-use identifier.
  • DELINQUENCY_ID – The delivered delinquency; foreign key to IEX_DELINQUENCIES_ALL.
  • PARTY_ID – The delivered party identifier.
  • REQUEST_ID / CONC_REQUEST_ID – Concurrent request identifiers associated with the delivery job.
  • CREATION_DATE / LAST_UPDATE_DATE – Standard audit columns tracking when the record was created and last modified.

Note that the six unique LOB indexes (SYS_IL0000402263C00004$$, SYS_IL0000402263C00005$$, SYS_IL0000402263C00022$$, SYS_IL0000402263C00038$$, SYS_IL0000402263C00040$$, and SYS_IL0000402263C00041$$) are infrastructure artifacts for the LOB segments, not business-key candidates. The meaningful unique identifier is XML_REQUEST_ID.

Common Use Cases and Queries

Collections administrators and support analysts query this table to audit whether a dunning letter or delinquency notice was successfully generated and delivered to a customer. Common reporting scenarios include failed-delivery investigations, reprint requests, and compliance audits of outbound customer correspondence.

A typical query retrieves the status and destination for a given delinquency:

SELECT h.XML_REQUEST_ID, h.STATUS, h.METHOD,
       h.DESTINATION, h.CREATION_DATE
FROM   IEX.IEX_XML_REQUEST_HISTORIES h
WHERE  h.DELINQUENCY_ID = :delinquency_id
ORDER  BY h.CREATION_DATE DESC;

To identify failed or stuck deliveries by status and worker:

SELECT STATUS, WORKER_ID, COUNT(*)
FROM   IEX.IEX_XML_REQUEST_HISTORIES
GROUP  BY STATUS, WORKER_ID;

Because the DOCUMENT, XMLDATA, and HTML_DOCUMENT columns are LOBs, de-referencing them should be avoided in high-volume scans. Filter by indexed columns (CREATION_DATE, STATUS, REQUEST_ID) first, then retrieve the LOB content for the handful of rows of interest.

Related Objects

The following are the most significant objects related to this table, based on the documented foreign-key relationships:

  • IEX.IEX_QUERY_TEMP_XREF – Parent of QUERY_TEMP_ID; maps delivery templates.
  • HZ_CUST_ACCOUNTS – Parent of CUST_ACCOUNT_ID; the customer account receiving the document.
  • IEX.IEX_DELINQUENCIES_ALL – Parent of DELINQUENCY_ID; the delinquency driving the collection action.
  • FND_CONCURRENT_REQUESTS – Related through REQUEST_ID and CONC_REQUEST_ID for concurrent program execution tracking.
  • XDO (XML Publisher) delivery tables – Related through XML_REQUEST_ID, which originates from XDO at document generation time.

Together these objects allow analysts to trace a document from source delinquency through template, generation, and final delivery to the customer account.