Results for “phone_number_long”

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

Overview

SO_CONTACTS_ACTIVE_V is a reporting and integration view in the Oracle E-Business Suite Order Entry (OE) module. It exposes active customer contacts together with their primary telephone information, flattening a parent-child relationship between the RA_CONTACTS and RA_PHONES tables into a single, denormalized result set. Because it is a view rather than a table, it carries no stored data of its own; it is a saved SELECT statement that is resolved at runtime against the underlying Receivables (AR) base tables.

The view is particularly useful for the field it exposes as PHONE_NUMBER_LONG. This column concatenates the area code, phone number, and extension into a single formatted string, which simplifies downstream consumption by reports, interfaces, and integrations that require a display-ready telephone value rather than its discrete components. The view filters to only active contacts, making it well suited to operational and analytical queries that should not surface inactive or obsolete contact records.

Underlying Base Objects

The view is defined over two Receivables base tables: RA_CONTACTS and RA_PHONES. RA_CONTACTS supplies the contact identity and descriptive attributes (last name, first name, job title, contact ID, customer ID, and status). RA_PHONES supplies the telephony attributes (area code, phone number, extension, primary flag, and phone status).

The join is an outer join from RA_CONTACTS to RA_PHONES, keyed on CONTACT_ID, with additional predicates restricting the phone rows to primary phones (NVL(RPH.PRIMARY_FLAG(+), 'N') = 'Y') and active phones (NVL(RPH.STATUS(+), 'A') = 'A'). The outer join ensures that contacts are returned even when no qualifying phone row exists. The view restricts the contact side with RC.STATUS = 'A' so that only active contacts are returned.

Note that the ETRM metadata for this object states "Not implemented in this database" and lists no documented referenced base objects for that specific extraction. The definition above reflects the documented view text provided in the ETRM excerpt.

Key Columns

• LAST_NAME — Contact's family name from RA_CONTACTS.
• FIRST_NAME — Contact's given name from RA_CONTACTS.
• FULL_NAME — Concatenation of LAST_NAME and FIRST_NAME with a comma separator.
• JOB_TITLE — The contact's job title.
• AREA_CODE — Telephone area code from the primary phone record.
• PHONE_NUMBER — The raw telephone number.
• EXTENSION — Telephone extension, if any.
• PHONE_NUMBER_LONG — Formatted telephone string using DECODE logic: "(AREA) NUMBER" with " EXT EXTENSION" appended when an extension exists. This is the column most often searched for and reported against.
• CONTACT_ID — Primary key of the contact, used for joins to other contact-based objects.
• CUSTOMER_ID — Identifier of the customer associated with the contact.

Common Use Cases and Queries

Typical uses include customer contact directories, order-entry supporting reports, and outbound integration extracts where a single formatted phone string is required.

Listing active contacts with formatted phone numbers for a customer:

SELECT full_name, job_title, phone_number_long
FROM   so_contacts_active_v
WHERE  customer_id = :p_customer_id;

Searching specifically for the long phone value:

SELECT contact_id, full_name, phone_number_long
FROM   so_contacts_active_v
WHERE  phone_number_long IS NOT NULL;

Joining back to order headers by contact or customer for reporting:

SELECT v.full_name, v.phone_number_long, o.order_number
FROM   so_contacts_active_v v, oe_order_headers_all o
WHERE  v.customer_id = o.customer_id
AND    v.contact_id  = o.contact_id;

Because PHONE_NUMBER_LONG is derived, filters and comparisons should be applied to the discrete AREA_CODE, PHONE_NUMBER, and EXTENSION columns where exact matching is required.