Search Results activity_number




Overview
Explore in AI Chat

Overview

APPS.AR_CM_ACTIVITIES_V is a reporting and integration view in the Oracle E-Business Suite Receivables module that consolidates credit memo activity against a customer's payment schedules. It presents a unified picture of two distinct transaction types: credit memo applications and credit memo adjustments. The view is owned by the APPS schema and is available in both EBS 12.1.1 and 12.2.2. Its principle design goal is to expose a consistent, denormalized set of columns describing what happened to an open receivable item regardless of whether the activity originated as an application of a credit memo or as a manual adjustment. Because the view materializes transaction numbers, classes, dates, amounts, and originator customer identifiers in one place, it is frequently used within customer-facing inquiry screens, collection workbenches, and custom reports. The activity_number column, which returns the credit memo transaction number or the adjustment number, is a common search target for users tracing a specific credit against a receivable.

Underlying Base Objects

The view definition is a UNION ALL of two projection branches over three documented base objects plus one PL/SQL utility package. Both branches join AR_PAYMENT_SCHEDULES (aliased PS) against activity records. The first branch reads AR_RECEIVABLE_APPLICATIONS (aliased APP) restricted to STATUS = 'APP', APPLICATION_TYPE = 'CM', and DISPLAY = 'Y', and is labelled activity_source 'CM APPLICATION'. The second branch reads AR_ADJUSTMENTS (aliased ADJ) and is labelled 'CM ADJUSTMENT'; it substitutes a NULL for activity_ps_id and uses the adjustment identifier as activity_id. The package ARPT_SQL_FUNC_UTIL supplies lookup meanings through get_lookup_meaning for the transaction class and for the approval/status codes, translating the coded values into readable text. The documented synonyms AR_PAYMENT_SCHEDULES, AR_RECEIVABLE_APPLICATIONS, and AR_ADJUSTMENTS resolve to their underlying AR base tables (AR_PAYMENT_SCHEDULES, AR_RECEIVABLE_APPLICATIONS, and AR_ADJUSTMENTS respectively), while the view itself is defined with a UNION ALL that prevents merge or updatable behavior.

Key Columns

  • activity_number — the credit memo transaction number (from PS.TRX_NUMBER in the application branch) or the adjustment number (ADJ.ADJUSTMENT_NUMBER in the adjustment branch). This is the primary user-facing identifier searched by the "activity_number" query.
  • activity_source — literal 'CM APPLICATION' or 'CM ADJUSTMENT', distinguishing the origin of the row.
  • activity_id — the applied customer transaction identifier or the adjustment identifier.
  • activity_class — the class value of the payment schedule or the literal 'ADJ' for adjustments.
  • activity_date / activity_amount — the apply date and applied or adjusted amount.
  • original_amount, remaining_amount, line_amount, tax_amount, freight_amount, charges_amount, total_amount — the receivable item's financial breakdown, carried from AR_PAYMENT_SCHEDULES.
  • customer_id, payment_schedule_id, customer_trx_id, activity_tsn — linkage keys back to the customer, schedule, transaction, and terms sequence.

Common Use Cases and Queries

Typical scenarios include reconciliation of credit memos applied to invoices, collection analysis of remaining amounts after credit activity, and drilling from a customer balance to the originating credit. A representative query filtered on the activity number is shown below.

SELECT activity_number,
       activity_source,
       activity_date,
       activity_amount,
       total_amount,
       remaining_amount
FROM   apps.ar_cm_activities_v
WHERE  activity_number = :p_activity_number;

Analysts also aggregate credit activity by customer or by source to measure the volume of adjustments versus applications:

SELECT activity_source,
       COUNT(*)          activity_count,
       SUM(activity_amount) total_activity
FROM   apps.ar_cm_activities_v
WHERE  customer_id = :p_customer_id
GROUP  BY activity_source;

Because the view is a read-only UNION ALL over AR payment schedules, applications, and adjustments, it should be used for inquiry and reporting rather than for transactional updates. Performance is best when queries restrict on activity_number, customer_id, or activity_date to limit the work performed by the underlying joins.