Results for “pn_contacts_all”

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

Overview

PN_CONTACTS_ALL is a Property Manager (PN) module table in Oracle E-Business Suite, owned by the PN schema. It stores contact information identified at the site level of a service provider. In the context of the ETRM Property Manager product, this table serves as the operational store for individuals associated with a specific company site — such as a landlord contact, lessor representative, or service provider point of contact — and is central to site-level communication and correspondence workflows.

From a Data Vault modeling perspective, the heuristic classification for PN_CONTACTS_ALL is satellite-leaning. This suggests the table behaves primarily as a descriptive satellite attached to a parent business entity (the company site), carrying descriptive attributes and change-tracked context rather than acting as a standalone hub of independent business keys. Practitioners designing integration or warehouse models should treat PN_CONTACTS_ALL as descriptive context hanging off PN_COMPANY_SITES_ALL, with CONTACT_ID as the technical identifier rather than a durable business key.

Key Information Stored

The table contains 31 documented columns in the 12.2.2 schema. The most significant include:

• CONTACT_ID — the surrogate primary key, enforced by PN_CONTACTS_PK and also indexed by PN_CONTACTS_U1 as a unique business-key candidate.
• COMPANY_SITE_ID — foreign key to PN_COMPANY_SITES_ALL, anchoring each contact to a specific site of a service provider.
• LAST_NAME, FIRST_NAME, JOB_TITLE — the person's name and role.
• EMAIL_ADDRESS, MAIL_STOP — electronic and physical routing details.
• PRIMARY_FLAG — indicates the principal contact for the site.
• STATUS — lifecycle state of the contact record.
• ORG_ID — multi-org operating unit discriminator.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns.

Common Use Cases and Queries

Typical reporting requirements include listing all contacts for a given site, identifying the primary contact, and reconciling contacts against their phone numbers. A representative query retrieving contacts with their site context is:

• SELECT c.contact_id, c.first_name, c.last_name, c.email_address, c.primary_flag FROM pn_contacts_all c WHERE c.company_site_id = :site_id AND c.status = 'A';
• Joining to PN_PHONES_ALL to resolve telephone details: SELECT c.last_name, p.phone_number FROM pn_contacts_all c, pn_phones_all p WHERE p.contact_id = c.contact_id;
• Filtering the primary site contact: SELECT * FROM pn_contacts_all WHERE company_site_id = :site_id AND primary_flag = 'Y';

Because ORG_ID participates in the record, multi-org security policies should be applied in any custom extraction to avoid cross-operating-unit leakage.

Related Objects

The FK metadata establishes two principal relationships that govern dependent objects:

• PN_COMPANY_SITES_ALL — referenced via PN_CONTACTS_ALL.COMPANY_SITE_ID; the parent site entity that scopes each contact.
• PN_PHONES_ALL — references PN_CONTACTS_ALL via PN_PHONES_ALL.CONTACT_ID; holds telephone records belonging to each contact.

Beyond these documented foreign keys, Property Manager logic typically interacts with company and site master data through the PN company/site hierarchy, and contact records may be surfaced through related address, party, or correspondence objects. Any extension or interface touching PN_CONTACTS_ALL should preserve the CONTACT_ID surrogate key and honor the unique index PN_CONTACTS_U1 to maintain integrity with PN_PHONES_ALL and the wider Property Manager data model.