Results for “area_code”

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

Overview

PN_PHONES_ALL is a Property Manager (PN) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores telephone contact information used to reach the contacts defined within the Property Manager application. According to the ETRM documentation, the table's description is "telecommuting information to reach a contact," reflecting its role as the operational repository for phone numbers, extensions, and related attributes associated with property management contacts.

The table resides in the PN schema, holds a VALID status, and contains 30 documented columns. Its single-column primary key, PN_PHONES_PK, is defined on PHONE_ID, and a unique index named PN_PHONES_U1 also exists on PHONE_ID. The table references PN_CONTACTS_ALL through the CONTACT_ID foreign key column, establishing a master-detail relationship between a contact and that contact's phone records.

Applying a heuristic Data Vault classification based on the mined foreign key structure, PN_PHONES_ALL is best modeled as satellite-leaning. The rationale is that the table is attached to a parent business entity (the contact in PN_CONTACTS_ALL) and carries descriptive, changing attributes such as phone number, area code, and phone type, rather than acting as a standalone hub or a pure many-to-many link.

Key Information Stored

The 30 documented columns comprise a surrogate identifier, a foreign key to the parent contact, descriptive phone attributes, standard EBS audit and multi-org columns, and a set of descriptive flexfield segments. The most operationally significant columns are:

• PHONE_ID — the surrogate primary key (PN_PHONES_PK) and the column underlying the unique index PN_PHONES_U1; also a business-key candidate by uniqueness.
• CONTACT_ID — the foreign key referencing PN_CONTACTS_ALL, tying each phone record to its owning contact.
• PHONE_TYPE — classifies the phone entry (for example, business, home, or mobile), enabling filtering and reporting by category.
• AREA_CODE — the telephone area code portion of the number.
• PHONE_NUMBER — the primary telephone number value.
• EXTENSION — the extension associated with the number.
• PRIMARY_FLAG — indicates which phone record is the preferred or primary number for the contact.
• STATUS — the active/inactive state of the phone record.
• ORG_ID — the operating unit column supporting multi-org partitioning.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and last modified each record and when.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield segments allowing customer-specific extension of the phone record.

Because the primary key is a surrogate (PHONE_ID) and the only other unique key is the same column, downstream SQL and ETL should treat PHONE_ID as the reliable row identifier and use CONTACT_ID plus PRIMARY_FLAG or PHONE_TYPE for business-level de-duplication rules.

Common Use Cases and Queries

Typical reporting and integration scenarios include retrieving a contact's preferred number, listing all numbers for a contact, and joining phone data to contact and property data for directory or notification reporting. A representative query for the primary number of each contact is:

SELECT c.contact_id, p.phone_type, p.area_code, p.phone_number, p.extension
FROM   pn_phones_all p, pn_contacts_all c
WHERE  p.contact_id = c.contact_id
AND    p.primary_flag = 'Y'
AND    p.status = 'A'
AND    p.org_id = :p_org_id;

A second pattern lists all active numbers grouped by type for a given contact:

SELECT phone_type, area_code || '-' || phone_number || 
       DECODE(extension, NULL, '', ' x' || extension) AS full_phone
FROM   pn_phones_all
WHERE  contact_id = :p_contact_id
AND    status = 'A'
ORDER BY primary_flag DESC, phone_type;

In ETL or data-migration contexts, PHONE_ID is the natural source key for incremental loads, with LAST_UPDATE_DATE driving delta extraction. In reporting, the table is commonly joined to contact master data to produce contact directories, and ORG_ID restricts results to the appropriate operating unit.

Related Objects

The following objects are most significant in relation to PN_PHONES_ALL, based on the documented foreign key and primary key relationships:

• PN_CONTACTS_ALL — the parent contact table, joined on PN_PHONES_ALL.CONTACT_ID = PN_CONTACTS_ALL.CONTACT_ID; the primary dependency for this table.
• PN_PHONES_PK — the primary key constraint on PHONE_ID that enforces the surrogate identifier.
• PN_PHONES_U1 — the unique index on PHONE_ID, also serving as a business-key candidate for the table's identifier.

Because the metadata documents only these direct relationships, the contact master table PN_CONTACTS_ALL and its primary-key and unique-index constraints constitute the core referenced set. Any application views, concurrent programs, or interfaces that consume Property Manager contact data will typically traverse PN_CONTACTS_ALL and PN_PHONES_ALL together, using CONTACT_ID as the join column and PHONE_ID as the row-level identifier.