Search Results dunning_level

Overview

AR_DUNNING_HISTORY_V is a read-only reporting view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It exposes consolidated dunning history information for the Oracle Receivables (AR) module. Dunning is the collections process by which an organization issues progressively firmer reminder letters to customers whose receivables have become past due. This view presents the outcome of that process: which payment schedules were dunned, on what date, at what dunning level, using which letter, and the interest amount associated with each dunned installment.

The view is not a transactional entry point. It is intended for inquiry, reporting, and integration scenarios where a flat, denormalized record of dunning activity is required without navigating the normalized correspondence, letter set, letter, and payment schedule tables individually. Because the underlying query joins AR_CORRESPONDENCES to determine the specific correspondence record, the object is closely related to the term "ar_correspondences," which is the parent table capturing all customer correspondences in Receivables.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following APPS synonyms, each resolving to the corresponding AR base table:

• AR_CORRESPONDENCES — the driving table, aliased COR. Supplies CORRESPONDENCE_DATE, CORRESPONDENCE_ID, REFERENCE1 (dunning letter set), REFERENCE2 (dunning letter), and ROWID.
• AR_CORRESPONDENCE_PAY_SCHED — aliased CPS. Links each correspondence to the payment schedules dunned and supplies STAGED_DUNNING_LEVEL, AMOUNT_ACCRUE, and AMOUNT_UNACCRUE.
• AR_DUNNING_LETTER_SETS — aliased DLS. Provides the letter set NAME, DUNNING_TYPE, and DUNNING_LETTER_SET_ID.
• AR_DUNNING_LETTERS — aliased DL. Provides LETTER_NAME keyed by DUNNING_LETTER_ID.
• AR_PAYMENT_SCHEDULES — aliased PS. Supplies the PAYMENT_SCHEDULE_ID.
• AR_SYSTEM_PARAMETERS — aliased SP. Supplies the ACCRUE_INTEREST flag used in the DECODE expression.

The join is driven by equality between DLS.DUNNING_LETTER_SET_ID and COR.REFERENCE1, between DL.DUNNING_LETTER_ID and COR.REFERENCE2, and between CPS.CORRESPONDENCE_ID and COR.CORRESPONDENCE_ID, with PS.PAYMENT_SCHEDULE_ID equal to CPS.PAYMENT_SCHEDULE_ID. Critically, the view filters on COR.PRELIMINARY_FLAG = 'N', so only finalized dunning correspondences are returned; preliminary or draft dunning letters are excluded.

Key Columns

• PAYMENT_SCHEDULE_ID — identifier of the dunned installment in AR_PAYMENT_SCHEDULES.
• DUNNING_DATE — the correspondence date on which the dunning letter was generated.
• DUNNING_LEVEL — the staged dunning level at which the customer was dunned.
• LETTER_NAME — the name of the dunning letter used.
• NAME — the name of the dunning letter set.
• DUNNING_TYPE — classification of the dunning letter set.
• DUNNING_LETTER_SET_ID — foreign key to AR_DUNNING_LETTER_SETS.
• CORRESPONDENCE_ID — foreign key to AR_CORRESPONDENCES.
• INTEREST_AMT — the accrued or unaccrued interest amount, resolved by the DECODE on SP.ACCRUE_INTEREST.
• ROW_ID — the ROWID of the underlying AR_CORRESPONDENCES row.

Common Use Cases and Queries

The view is used to report dunning activity by customer, level, letter, or period, and to feed downstream collections analytics. A typical query retrieves all dunning history for a given payment schedule:

• SELECT dunning_date, dunning_level, letter_name, name, dunning_type, interest_amt FROM ar_dunning_history_v WHERE payment_schedule_id = :p_schedule_id ORDER BY dunning_date;
• SELECT dunning_level, COUNT(*) FROM ar_dunning_history_v WHERE dunning_date BETWEEN :start_date AND :end_date GROUP BY dunning_level;

Because the view joins AR_SYSTEM_PARAMETERS without a restricting predicate, interest amounts reflect the current system parameter setting at query time. Reports should therefore be treated as reflecting present accrual configuration rather than the configuration in effect when the dunning letter was issued.