Results for “arfv_collection_calls”

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

Overview

ARFV_COLLECTION_CALLS is a reporting view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It exposes the collection call activity recorded in Oracle Receivables, presenting one row per customer call logged against a collector, a customer account, a contact, and a site use. The "FV" prefix identifies it as a Receivables reporting view intended for inquiry, operational reporting, and integration consumption rather than for transaction entry. It is the natural source for collection workbench style extracts, since it joins the call header to its free-form note text, translated lookup meanings, party names, contact names, collector names, and organization unit names in a single flattened result set. Because it resolves foreign keys internally, report authors and downstream extract programs do not need to replicate the lookups against AR_LOOKUPS or the TCA party model.

Underlying Base Objects

The view is defined over the following documented base objects: AR_CUSTOMER_CALLS, AR_NOTES, AR_COLLECTORS, HR_ALL_ORGANIZATION_UNITS, HZ_CUST_SITE_USES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_ORG_CONTACTS, HZ_PARTIES, and HZ_RELATIONSHIPS. All are referenced through APPS synonyms. AR_CUSTOMER_CALLS is the driving table, aliased CCA, holding the call record. AR_NOTES is outer-joined on CUSTOMER_CALL_ID to supply the note text, and HZ_CUST_SITE_USES is outer-joined on SITE_USE_ID to supply the location. HZ_PARTIES and HZ_CUST_ACCOUNTS provide the customer name. The contact name is derived from an inline subquery that walks HZ_CUST_ACCOUNT_ROLES, HZ_RELATIONSHIPS, HZ_ORG_CONTACTS, and HZ_PARTIES to resolve a contact role to a person name. AR_COLLECTORS supplies the collector, and HR_ALL_ORGANIZATION_UNITS the operating unit name.

Key Columns

• CALL_DATE — The date the collection call took place. The view also derives the fractional time-of-day component as (CALL_DATE - TRUNC(CALL_DATE)). This is the column most commonly searched and filtered.
• CUSTOMER_CALL_ID — Primary key of the underlying call record and the join key to AR_NOTES.
• STATUS — Translated through AR_LOOKUPS (PAYMENT_SCHEDULE_STATUS) to a decoded meaning such as Open or Closed.
• REASON_CODE, CALL_OUTCOME, FOLLOW_UP_ACTION — Each decoded through AR_LOOKUPS using the CUSTOMER_RESPONSE_REASON, CALL_OUTCOME, and FOLLOW_UP lookup types respectively.
• COMPLETE_FLAG — Decoded through the YES/NO lookup.
• FOLLOW_UP_DATE, PROMISE_DATE, PROMISE_AMOUNT, COLLECTION_FORECAST, FORECAST_DATE — Commitment and forecasting attributes captured on the call.
• Customer, contact, collector, and location names — Party name, concatenated contact first and last name, collector name, and site use location, each truncated where required.
• IDS — CUSTOMER_ID, CONTACT_ID, COLLECTOR_ID, and ORG_ID.
• WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY.

Common Use Cases and Queries

The view supports collection productivity reporting, call outcome analysis, promise-to-pay tracking, and exception reporting for overdue follow-ups. Because CALL_DATE carries no time component in its truncated form, date-range filters are straightforward.

• Calls logged in a period: SELECT customer_call_id, call_date, collector_name, party_name, call_outcome_meaning FROM arfv_collection_calls WHERE call_date BETWEEN :p_from AND :p_to ORDER BY call_date;
• Outstanding follow-ups: SELECT customer_call_id, call_date, follow_up_date, party_name, collector_name FROM arfv_collection_calls WHERE complete_flag = 'N' AND follow_up_date < SYSDATE;
• Promise-to-pay exposure: SELECT party_name, promise_date, promise_amount, collector_name FROM arfv_collection_calls WHERE promise_date IS NOT NULL AND promise_date >= TRUNC(SYSDATE);
• Recent call history for one customer: SELECT call_date, call_outcome_meaning, note_text FROM arfv_collection_calls WHERE customer_id = :p_customer_id ORDER BY call_date DESC;

Note that the view is a reporting construct over OLTP tables; heavy or unbounded queries should be constrained by ORG_ID and CALL_DATE to avoid full scans of AR_CUSTOMER_CALLS.