Search Results iex_open_int_hst




Overview
Explore in AI Chat

Overview

The IEX_OPEN_INT_HST table, owned by the IEX schema, is a Collections module history table within Oracle E-Business Suite Release 12.1.1 and 12.2.2. It tracks the lifecycle of open interest records processed through Collections workflows, capturing the request-to-processing cycle along with associated reviews, recalls, and status transitions. In Advanced Collections, "open interest" activities represent pending collections actions or requests logged against delinquent customers or late payment entries; this table preserves the audit history of those interactions.

Columns such as OBJECT1_ID1 and OBJECT1_ID2 serve as generic to-object references, allowing a record to point at a source object (for example, a customer, transaction, or dispute) through JTOT (Java Tool for Object Tagging) infrastructure. The presence of ORG_ID, REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID indicates the table participates in multi-org aware, concurrent-program-driven batch processing.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is "standalone." No parent FKs were documented, so this table is not naturally a hub or link subordinate; it is best treated as a satellite-like history table keyed by a surrogate identifier. Analysts modeling the Collections domain should note this classification is heuristic and should validate downstream integration requirements before normalizing.

Key Information Stored

The primary key is IEX_IOH_PK on the ID column, a system-generated surrogate key. The major business and auditing columns include:

  • ID — surrogate primary key uniquely identifying each history row.
  • OBJECT1_ID1 / OBJECT1_ID2 — composite reference identifying the source object linked via the JTOT framework.
  • JTOT_OBJECT1_CODE — code indicating the type of referenced object.
  • ACTION — the collections action recorded (for example, a request or recall).
  • STATUS — current processing state of the open interest record.
  • COMMENTS — free-text collector or analyst notes.
  • REQUEST_DATE / PROCESS_DATE — when the action was requested and when it was processed.
  • EXT_AGNCY_ID — external collection agency reference where relevant.
  • REVIEW_DATE / RECALL_DATE — dates of review and recall events.
  • AUTOMATIC_RECALL_FLAG / REVIEW_BEFORE_RECALL_FLAG — Y/N control flags governing recall behavior.
  • ORG_ID — operating unit for multi-org partitioning.
  • REQUEST_ID / PROGRAM_ID / PROGRAM_APPLICATION_ID — concurrent program execution context.
  • OBJECT_VERSION_NUMBER — optimistic locking token.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard who-column auditing, plus fifteen ATTRIBUTE (flexfield) columns.

Common Use Cases and Queries

Typical reporting scenarios include tracing an open interest's progression from request to recall, identifying unreviewed records past a review date, and reconciling automatic recall activity. A representative query joining the history to the JTOT object reference might read:

  • List active open interest requests for an org:
    SELECT id, object1_id1, object1_id2, jtot_object1_code, action, status, request_date, process_date FROM iex.iex_open_int_hst WHERE org_id = :org_id AND status = 'ACTIVE';
  • Find records pending review:
    SELECT id, review_date, automatic_recall_flag FROM iex.iex_open_int_hst WHERE review_before_recall_flag = 'Y' AND review_date > SYSDATE;
  • Audit concurrent program batch runs:
    SELECT request_id, program_id, program_application_id, status, count(*) FROM iex.iex_open_int_hst GROUP BY request_id, program_id, program_application_id, status;

Because the table is history-oriented, queries should generally filter on LAST_UPDATE_DATE or PROCESS_DATE to limit scope, and DBAs should rely on ORG_ID predicates to preserve multi-org security.

Related Objects

Documentation for IEX_OPEN_INT_HST identifies no explicit foreign keys, so related objects are inferred from shared IEX Collections conventions and column semantics rather than declared constraints:

  • JTOT objects / keyword tagging — the OBJECT1_ID1, OBJECT1_ID2 and JTOT_OBJECT1_CODE columns resolve to the general object tagging infrastructure.
  • IEX_OPEN_INT (or equivalent current-state table) — the transactional counterpart whose audit trail this history table retains.
  • FND_CONCURRENT_REQUESTS — joinable on REQUEST_ID for program run details.
  • FND_APPLICATION / FND_CONCURRENT_PROGRAMS / FND_PROGRAM — resolve PROGRAM_APPLICATION_ID and PROGRAM_ID.
  • HR_OPERATING_UNITS / FND_ORG — resolve ORG_ID.
  • AR customers and transactions — reachable through the JTOT object reference, not a direct FK.

Given the absence of documented foreign keys, integration should be validated against the specific ETRM 12.2.2 schema in each environment before relying on implicit joins.