Results for “pn_contact_assignments_v”

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

Overview

PN_CONTACT_ASSIGNMENTS_V is an APPS-owned database view in the Oracle E-Business Suite Property Manager (PN) module. It presents contact assignment records that link property lease contacts to companies, company sites, and lease roles. The view is declared VALID in the ETRM metadata and is available in both Oracle EBS 12.1.1 and 12.2.2. Its primary purpose is to expose denormalized, reporting-friendly contact assignment data by joining the transactional contact assignment table to company, site, lookup, and contact master data in a single queryable object. Instead of requiring report developers and integrators to reconstruct the multi-table join themselves, the view encapsulates the relationship logic, including outer-joined primary contact resolution and lease role decoding through the FND_LOOKUPS framework.

The view acts as a read-only presentation layer suited to ad hoc reporting, Oracle Discoverer or BI Publisher data models, custom concurrent programs, and inbound/outbound integration extracts. Because it is a view rather than a table, it reflects the current state of the underlying transactional records at query time.

Underlying Base Objects

The view is defined over the following documented objects:

• PN_CONTACT_ASSIGNMENTS (synonym to the base transactional table) — the driving table, aliased PCA, holding the contact assignment records.
• PN_COMPANIES_ALL (synonym) — aliased PNC, supplying company name.
• PN_COMPANY_SITES_ALL (synonym) — aliased PCS, supplying site name, lease role type, and address identifier.
• PN_CONTACTS_ALL (synonym) — aliased CON, supplying the primary contact name via an outer join.
• FND_LOOKUPS (view) — aliased FLV, decoding the lease role type into a meaningful value using lookup type PN_LEASE_ROLE_TYPE.
• FND_GLOBAL (package) — referenced by the environment for session context such as ORG_ID resolution.

The join predicates enforce that the lease identifier is not null, that company sites and companies align on COMPANY_ID, and that the primary contact flag equals 'Y'. The contact join is an outer join, so contact assignments without a matching primary contact still appear with a null CONTACT_NAME. Multi-organization filtering is applied through ORG_ID.

Key Columns

• CONTACT_ASSIGNMENT_ID — primary identifier of the contact assignment record.
• COMPANY_ID / COMPANY_NAME — company associated with the assignment and its descriptive name.
• COMPANY_SITE_ID / SITE_NAME — the specific company site, with its descriptive name.
• LEASE_ROLE_TYPE / LEASE_ROLE — the encoded lease role type and its decoded meaning from PN_LEASE_ROLE_TYPE lookup.
• LEASE_ID / LEASE_CHANGE_ID — the lease and, where applicable, the lease change driving the assignment.
• LOCATION_ID — property location reference.
• STATUS — current status of the contact assignment.
• CONTACT_NAME — concatenation of the primary contact's first and last name.
• ADDRESS_ID — address reference derived from the company site.
• ORG_ID — operating unit identifier for multi-org security.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield (DFF) context and segment values.
• Audit columns — ROW_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

A typical reporting scenario lists active contact assignments for a lease, including decoded roles and contact names:

• Lease contact listing: SELECT contact_assignment_id, company_name, site_name, lease_role, contact_name FROM pn_contact_assignments_v WHERE lease_id = :lease_id AND status = 'ACTIVE' ORDER BY lease_role;
• Site contact roster: SELECT site_name, lease_role, contact_name FROM pn_contact_assignments_v WHERE company_site_id = :site_id;
• Multi-org extract: SELECT * FROM pn_contact_assignments_v WHERE org_id = :org_id AND lease_change_id IS NULL;
• DFF-driven analysis: SELECT attribute_category, attribute1, attribute2, contact_name FROM pn_contact_assignments_v WHERE attribute_category IS NOT NULL;

Because the view outer-joins PN_CONTACTS_ALL on the primary flag, queries should account for possible null CONTACT_NAME values. For integration extracts, filter on ORG_ID and STATUS, and use LAST_UPDATE_DATE for incremental pulls.