Search Results iex_cases_all_b




Overview
Explore in AI Chat

Overview

IEX_CASES_ALL_B is the base table that stores the master record of a Collections case within the Oracle E-Business Suite Collections (IEX) module. In EBS 12.1.1 and 12.2.2, the Collections product uses this table to persist the core attributes of each case opened against a delinquent customer, disputed transaction, or credit-risk situation. Every case that a collections agent creates, assigns, monitors, or closes is ultimately anchored to a row in this table, identified by the surrogate primary key CAS_ID.

The table is owned by the IEX schema and is documented as VALID. It carries 42 physical columns and one unique index, IEX_CASES_ALL_B_U1, on CAS_ID. A second key, the IEX_CASES_ALL_B_PK constraint, is also defined on CAS_ID, confirming that the surrogate identifier is both the primary and the unique business key candidate at the physical level. The table also carries a foreign key from SECURITY_GROUP_ID to FND_SECURITY_GROUPS, which supports multi-tenant data isolation and security-group-based access filtering.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, i.e., not clearly a hub, link, or satellite on the basis of its FK footprint alone. In practice, the structure—a single natural business key (CASE_NUMBER) plus a large set of descriptive and audit attributes—makes it a strong candidate for modeling as a hub (CAS_ID) with one or more satellites carrying status, ownership, and attribute columns.

Key Information Stored

The most significant columns fall into three groups. The first is identity: CAS_ID is the surrogate primary key; CASE_NUMBER is the user-facing case number; ORIG_CAS_ID links a case to an originating or predecessor case; and PARTY_ID ties the case to the customer or party record in the trading community model.

The second group describes case state and lifecycle. STATUS_CODE, CLOSE_REASON, CASE_STATE, and ACTIVE_FLAG together indicate whether a case is open, closed, or in a particular workflow state. CASE_ESTABLISHED_DATE and CASE_CLOSING_DATE record when the case was opened and closed. OWNER_RESOURCE_ID and ACCESS_RESOURCE_ID identify the collections agent who owns the case and the resource granted access to it.

The third group supports estimation, audit, and extensibility. PREDICTED_RECOVERY_AMOUNT and PREDICTED_CHANCE capture the agent’s expectation of recovery. ORG_ID scopes the row to an operating unit, and SECURITY_GROUP_ID scopes it to a security group. The standard EBS audit columns—CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE—record who created and last touched each row and which concurrent program processed it. ATTRIBUTE_CATEGORY through ATTRIBUTE15 provide the standard DFF (descriptive flexfield) extension points.

Common Use Cases and Queries

Typical reporting queries join IEX_CASES_ALL_B to IEX_CASE_OBJECTS or IEX_CASE_CONTACTS to list the transactions and contacts tied to each case. A common pattern for an agent worklist is:

  • SELECT c.case_number, c.status_code, c.owner_resource_id, c.case_established_date FROM iex_cases_all_b c WHERE c.active_flag = 'Y' AND c.org_id = :org_id;
  • Case aging: SELECT case_number, ROUND(SYSDATE - case_established_date) age_days FROM iex_cases_all_b WHERE status_code = 'OPEN';
  • Recovery forecasting: aggregate PREDICTED_RECOVERY_AMOUNT by OWNER_RESOURCE_ID to project recovery by agent.
  • Audit/change tracking: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to trace recent case changes.

Reports are frequently built as BI Publisher or OAF-based Collections dashboards, and the table is a primary source for the Collections workbench.

Related Objects

Because CAS_ID is the primary key of IEX_CASES_ALL_B, several child tables reference it through a CAS_ID foreign key:

  • IEX_BANKRUPTCIES (CAS_ID) — bankruptcy details linked to a case.
  • IEX_CASE_DEFINITIONS (CAS_ID) — case definition/configuration rows.
  • IEX_CASE_CONTACTS (CAS_ID) — contacts associated with the case.
  • IEX_CASE_OBJECTS (CAS_ID) — the transactions, disputes, or other objects attached to the case.
  • IEX_WRITEOFFS (CAS_ID) — write-off records tied to the case.
  • OKL_OPEN_INT_ALL (CAS_ID) — open interest records from the leasing module that reference the case.
  • FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for security-group filtering.

These relationships make IEX_CASES_ALL_B the central anchor for the Collections case data model, and any extraction or integration that needs case context should treat CAS_ID as the join key to these dependent tables.