Results for “pn_contacts_v”

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

Overview

PN_CONTACTS_V is a seeded Oracle E-Business Suite view owned by the APPS schema and delivered with the Property Manager (PN) product. In Oracle EBS 12.1.1 and 12.2.2, it presents contact records associated with property or company sites in a denormalized, reporting-friendly form. Rather than forcing consumers to join PN_CONTACTS to FND_LOOKUPS manually, the view resolves the contact's job title lookup code into its display meaning through the JOB_TITLE_MEANING column, and it also carries the multi-organization ORG_ID context required by Property Manager's operating unit model.

The view is read-only infrastructure intended for inquiry, reporting, and integration rather than for direct DML. The user search term mail_stop maps directly to the MAIL_STOP column, which is surfaced unchanged from the underlying PN_CONTACTS table.

Underlying Base Objects

The documented base objects referenced by PN_CONTACTS_V are FND_LOOKUPS, PN_CONTACTS, and the FND_GLOBAL package. PN_CONTACTS supplies every contact-level attribute in the view. FND_LOOKUPS is joined with an outer join (the (+) operator on both of its predicates) restricted to LOOKUP_TYPE = 'PN_JOB_TITLE', matched against PN_CONTACTS.JOB_TITLE on LOOKUP_CODE. Because the join is outer, contacts whose JOB_TITLE is null or does not match a seeded lookup still appear, with JOB_TITLE_MEANING returned as null. FND_GLOBAL is referenced during view resolution to supply session context, notably the ORG_ID used to scope results to the current operating unit.

In 12.1.1 and 12.2.2 the maintenance and security characteristics of PN_CONTACTS are identical, so the view behaves consistently across both releases; the primary difference between the releases lies in the surrounding technology stack rather than in this view definition.

Key Columns

• ROW_ID — the ROWID of the underlying PN_CONTACTS row, used for row-level identification.
• CONTACT_ID — primary key of the contact record.
• COMPANY_SITE_ID — the company or property site to which the contact is attached.
• MAIL_STOP — internal mail routing designation for the contact; the searched column.
• LAST_NAME / FIRST_NAME / JOB_TITLE — contact identification and the raw job title lookup code.
• JOB_TITLE_MEANING — the decoded description from FND_LOOKUPS for lookup type PN_JOB_TITLE.
• EMAIL_ADDRESS / PRIMARY_FLAG — electronic contact detail and designation of the primary contact for the site.
• STATUS — active/inactive state of the contact record.
• ORGANIZATION_ID (ORG_ID) — operating unit context controlling visibility.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard EBS descriptive flexfield columns.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include property site contact reports, mail routing and correspondence extracts, customer-site roster listings, and integration feeds that require a contact's job title in decoded form. Multi-org awareness means queries should generally be executed with an operating unit initialized via FND_GLOBAL.APP_INITIALIZE or an equivalent session setup so that ORG_ID resolves correctly.

Contacts for a given site:

SELECT contact_id, last_name, first_name, mail_stop, job_title_meaning
FROM   apps.pn_contacts_v
WHERE  company_site_id = :p_site_id
AND    NVL(status,'A') = 'A'
ORDER  BY primary_flag DESC, last_name;

Mail stop lookup:

SELECT contact_id, last_name, first_name, mail_stop, email_address
FROM   apps.pn_contacts_v
WHERE  UPPER(mail_stop) LIKE UPPER(:p_mail_stop)||'%';

Because the view is a query-only construct, all DML against contact data should be directed to PN_CONTACTS through the supported Property Manager APIs or concurrent programs rather than attempted against the view.