Results for “csc_reverse_phone_v”

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

Overview

APPS.CSC_REVERSE_PHONE_V is a VALID database view owned by the APPS schema and registered under the CSC – Customer Care product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose is to "give reverse phone number from HZ_CONTACT_POINTS table." In practice, the view exposes a normalized, character-reversed rendering of the concatenated telephone attributes (country code, area code, and phone number) stored in the TCA contact points repository, filtered to contact points whose owner is a party.

Rather than being a general-purpose telephony interface, the view is a targeted utility. Reverse-string ordering allows telephone numbers to be sorted or matched from the least significant (rightmost) digit rather than the most significant, which is useful for suffix matching and deduplication scenarios where the trailing digits carry the most discriminating information. Because it is read-only and carries no PL/SQL logic, it can be joined directly into custom reports, concurrent programs, and inbound/outbound integration extracts without triggering business logic in the base table.

Underlying Base Objects

The documented ETRM metadata lists a single referenced base object: the synonym HZ_CONTACT_POINTS, resolved in the APPS schema. HZ_CONTACT_POINTS is the TCA (Trading Community Architecture) table that stores all contact point records for parties, locations, and other entities, including phone, email, fax, URL, and EDI addresses. The view restricts its source set to rows where OWNER_TABLE_NAME = 'HZ_PARTIES', so only contact points attached directly to a party record are surfaced; contact points whose owner is a party site or another entity are excluded by the view definition itself.

Because the view reads through a synonym to the HZ base object, it inherits the standard TCA data model and Multi-Org-independent security characteristics of HZ_CONTACT_POINTS. It does not perform any joins or lookups back to HZ_PARTIES; the OWNER_TABLE_ID column is simply carried forward as the party identifier and must be joined manually to HZ_PARTIES.PARTY_ID when party detail is required.

Key Columns

• REVERSE_PHONE_NUMBER — The core output. Defined as REVERSE(TRANSLATE(PHONE_COUNTRY_CODE||PHONE_AREA_CODE||PHONE_NUMBER, '1@_.!"#$%&()''*+, -/:;<=>?[\]^`{|}~ ', '1')). The TRANSLATE removes spaces and virtually all punctuation from the concatenated phone components and maps the numeric prefix to the literal '1'; REVERSE then inverts the resulting character string. The result is a punctuation-free, reversed digit string suitable for suffix-oriented comparison.
• OWNER_TABLE_ID — The primary key value of the owning entity. Because the view filters on OWNER_TABLE_NAME = 'HZ_PARTIES', this is the party identifier (HZ_PARTIES.PARTY_ID) for every row returned. This is the column most commonly searched for by users querying "owner_table_id" against this view.
• LAST_UPDATE_DATE — Standard audit column indicating when the underlying contact point row was last modified. Useful for incremental extracts, change-detection feeds, and data-quality reconciliation between EBS and downstream systems.

Common Use Cases and Queries

The principal scenario is matching an inbound or legacy telephone number against TCA party contact points when only the trailing digits are reliable, or when punctuation and spacing in stored values prevent straightforward equality comparison. A second scenario is reverse lookup from a phone number to a party identifier, typically the first step before joining to HZ_PARTIES for name and account information.

To retrieve all phone records for a specific party:

SELECT reverse_phone_number, owner_table_id, last_update_date
FROM   apps.csc_reverse_phone_v
WHERE  owner_table_id = :party_id;

To locate a party from a reversed search value, applying the same normalization the view uses:

SELECT v.owner_table_id, p.party_name
FROM   apps.csc_reverse_phone_v v,
       apps.hz_parties p
WHERE  v.reverse_phone_number LIKE apps.reverse_phone_number(:search_phone)||'%'
AND    p.party_id = v.owner_table_id;

For incremental feeds, filter on LAST_UPDATE_DATE greater than the last extract watermark. Note that because the view returns no carrier, phone-line-type, or contact-point-purpose columns, users needing those attributes must join back to HZ_CONTACT_POINTS on OWNER_TABLE_ID and the original phone attributes.