Results for “dunning_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The IEX_DUNNING_TRANSACTIONS table belongs to the IEX – Collections module of Oracle E-Business Suite and is documented in ETRM for releases 12.1.1 and 12.2.2. It stores the transaction-level detail produced by a collections dunning run. Where a dunning definition (IEX_DUNNINGS) describes the recurring letters, calls, or correspondence strategy, IEX_DUNNING_TRANSACTIONS records each individual customer transaction that has been captured, staged, and processed against that strategy. The table therefore acts as the operational fact store for dunning activity at the transaction level.

The key in ETRM is IEX_DUNNING_TRANSACTIONS_PK, defined on DUNNING_TRX_ID. A second unique index, IEX_DUNNING_TRANSACTIONS_U1, is also defined on DUNNING_TRX_ID, so the column functions simultaneously as the surrogate primary key and as the only documented business-key candidate. The metadata classifies this object heuristically as standalone in Data Vault terms; in a dimensional or Data Vault model it may be treated as a hub-like object carrying its own identity, with the surrounding dunning and cross-reference tables acting as related dimensions or links rather than as parent hubs in a strict hierarchy. The schema contains 12 documented columns.

Key Information Stored

• DUNNING_TRX_ID – Surrogate primary key of the dunning transaction; also the unique-key candidate in IEX_DUNNING_TRANSACTIONS_U1.
• DUNNING_ID – Foreign key to IEX_DUNNINGS; identifies the dunning definition or strategy instance under which the transaction was processed.
• CUST_TRX_ID – References the customer transaction (typically an AR transaction) that the dunning action targets.
• PAYMENT_SCHEDULE_ID – Identifies the payment schedule line associated with the transaction, allowing dunning at the installment level rather than only the transaction header.
• AG_DN_XREF_ID – Foreign key to IEX_AG_DN_XREF; relates the dunning transaction to the aging/dunning cross-reference record used in scoring and correspondence generation.
• STAGE_NUMBER – Indicates the dunning stage or step at which the transaction was captured.
• CREATED_BY, CREATION_DATE – Standard audit columns recording who created the row and when.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard audit columns for the most recent modification.
• OBJECT_VERSION_NUMBER – Optimistic locking column used by the OAF/BC4J framework to detect concurrent updates.

Common Use Cases and Queries

Typical reporting against this table includes dunning history by customer, stage-level counts, and reconciliation between dunning runs and the underlying receivables. A common pattern joins the transaction rows back to their dunning definition and to the cross-reference record to trace why a transaction was selected:

• List all dunning transactions for a given dunning definition: SELECT * FROM iex_dunning_transactions WHERE dunning_id = :p_dunning_id.
• Trace dunning activity for a specific customer transaction: SELECT dunning_trx_id, dunning_id, stage_number FROM iex_dunning_transactions WHERE cust_trx_id = :p_cust_trx_id.
• Join to the dunning definition for descriptive reporting: SELECT t.dunning_trx_id, d.name FROM iex_dunning_transactions t, iex_dunnings d WHERE t.dunning_id = d.dunning_id.
• Join to IEX_AG_DN_XREF on AG_DN_XREF_ID to retrieve aging and dunning scoring context.
• Audit and volume trending using CREATION_DATE and LAST_UPDATE_DATE, for example counting rows created per dunning run period.

Because the table is insert-intensive during collection runs, queries should generally be filtered by DUNNING_ID or CUST_TRX_ID, both of which are supported by the foreign-key relationships documented above.

Related Objects

• IEX_DUNNINGS – Parent dunning definition; joined via IEX_DUNNING_TRANSACTIONS.DUNNING_ID = IEX_DUNNINGS.DUNNING_ID.
• IEX_AG_DN_XREF – Aging/dunning cross-reference; joined via IEX_DUNNING_TRANSACTIONS.AG_DN_XREF_ID = IEX_AG_DN_XREF.AG_DN_XREF_ID.
• Receivables transaction tables (for example RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL) – referenced indirectly through CUST_TRX_ID and PAYMENT_SCHEDULE_ID.
• Collections workbench and dunning concurrent programs in the IEX module, which populate and consume this table.
• The IEX_DUNNING_TRANSACTIONS_PK and IEX_DUNNING_TRANSACTIONS_U1 indexes, which enforce the primary and unique key on DUNNING_TRX_ID.