Search Results ar_correspondence_pay_sched




Overview
Explore in AI Chat

Overview

APPS.AR_DUNNING_HISTORY_V is a reporting view in the Oracle E-Business Suite Receivables (AR) module that consolidates dunning (collections correspondence) activity against customer payment schedules. Dunning is the process by which Receivables generates reminder letters to customers whose open items have reached defined aging thresholds or staged dunning levels. This view exposes the historical, finalized dunning output: which letter was produced, when, at what dunning level, and for which payment schedule, providing a single queryable source for collections and credit-management reporting.

The view is defined with a deduplicating filter of COR.PRELIMINARY_FLAG = 'N', meaning it returns only correspondence records that have been finalized rather than those held in a preliminary (pre-generation or preview) state. This makes it suitable for audit, customer-contact history, and dunning-effectiveness analysis. It is a join-only view with no aggregation, so it retains one row per finalized dunning correspondence per payment schedule.

Underlying Base Objects

Per the documented ETRM metadata, the view is owned by APPS and is defined over six referenced base objects, all accessed via public synonyms: AR_CORRESPONDENCES, AR_CORRESPONDENCE_PAY_SCHED, AR_DUNNING_LETTERS, AR_DUNNING_LETTER_SETS, AR_PAYMENT_SCHEDULES, and AR_SYSTEM_PARAMETERS.

The join logic ties these together through the correspondence record:

  • AR_CORRESPONDENCES (alias COR) is the driving table, holding the correspondence header, date, the preliminary flag, and reference columns used to identify the dunning letter set and letter.
  • AR_DUNNING_LETTER_SETS (alias DLS) joins via DLS.DUNNING_LETTER_SET_ID = COR.REFERENCE1.
  • AR_DUNNING_LETTERS (alias DL) joins via DL.DUNNING_LETTER_ID = COR.REFERENCE2.
  • AR_CORRESPONDENCE_PAY_SCHED (alias CPS) joins via CPS.CORRESPONDENCE_ID = COR.CORRESPONDENCE_ID, linking letters to specific payment schedules and storing the staged dunning level and dunned amounts.
  • AR_PAYMENT_SCHEDULES (alias PS) joins via PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID.
  • AR_SYSTEM_PARAMETERS (alias SP) has no join predicate; it is crossed to read the ACCRUE_INTEREST system-level flag for amount selection.

Key Columns

  • PAYMENT_SCHEDULE_ID — Identifier of the dunned payment schedule (open item), keyed to AR_PAYMENT_SCHEDULES.
  • CORRESPONDENCE_DATE — Date the dunning correspondence was generated.
  • STAGED_DUNNING_LEVEL — The dunning level at which the item was selected, from AR_CORRESPONDENCE_PAY_SCHED.
  • LETTER_NAME — Name of the specific dunning letter used.
  • NAME (DLS.NAME) — Name of the dunning letter set.
  • DUNNING_TYPE — Classification of the dunning letter set.
  • DUNNING_LETTER_SET_ID — Identifier of the letter set applied.
  • CORRESPONDENCE_ID — Unique correspondence identifier, the linkage key across the correspondence tables.
  • Decoded amount (unnamed) — A DECODE expression returning CPS.AMOUNT_ACCRUE when the ACCRUE_INTEREST system parameter equals 'Y', otherwise CPS.AMOUNT_UNACCRUE. This yields the appropriate dunned amount based on the interest-accrual setting.
  • ROWID — ROWID of the correspondence row, exposed for direct-row addressing.

Common Use Cases and Queries

The view supports collections history reporting, dunning effectiveness analysis, and reconciliation of correspondence output. Because AR_DUNNING_HISTORY_V filters out preliminary records, it is typically preferred over querying AR_CORRESPONDENCES directly when only finalized dunning is relevant. Sample query listing dunning history for a customer's open items:

  • SELECT payment_schedule_id, correspondence_date, staged_dunning_level, letter_name, name, dunning_type FROM apps.ar_dunning_history_v WHERE correspondence_date >= :from_date ORDER BY correspondence_date DESC;
  • SELECT staged_dunning_level, COUNT(*) FROM apps.ar_dunning_history_v GROUP BY staged_dunning_level; — distribution of dunned items by level.
  • Join PAYMENT_SCHEDULE_ID back to AR_PAYMENT_SCHEDULES or AR_CUSTOMERS to attribute dunning activity to a customer and currency for exposure reporting.

Since the search term ar_correspondences maps directly to the driving table COR, this view is the recommended entry point for reporting the dunning subset of correspondence data without rebuilding the multi-table join manually.