Search Results pn_lease_role_type




Overview
Explore in AI Chat

Overview

APPS.PN_CONTACT_ASSIGN_HISTORY_V is a reporting view in the Oracle Property Manager (PN) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It presents a unified, denormalized record of contact assignments associated with leases and lease changes, merging current assignments held in PN_CONTACT_ASSIGNMENTS with their archived counterparts in PN_CONTACT_ASSIGN_HISTORY. A discriminating column, CURRENT_FLAG, distinguishes the two sources: rows sourced from PN_CONTACT_ASSIGNMENTS are stamped with 'Y', while rows from PN_CONTACT_ASSIGN_HISTORY are stamped with 'N'.

Because the view resolves surrogate keys, lookup meanings, and company/site names within a single SELECT, it is well suited to inquiry screens, concurrent reports, and integration extracts that must present contact-to-lease relationships historically rather than only at their present state.

Underlying Base Objects

The view is defined as a UNION ALL of two similarly projected queries. The first draws from PN_CONTACT_ASSIGNMENTS; the second from PN_CONTACT_ASSIGN_HISTORY. Both queries join across:

The join predicates require PCA.LEASE_ID IS NOT NULL, PCA.LOCATION_ID IS NULL, and matching COMPANY_SITE_ID and COMPANY_ID across the assignment and site tables.

Key Columns

  • CONTACT_ASSIGNMENT_ID — Primary identifier of the assignment record in the source table.
  • CURRENT_FLAG — 'Y' for rows from PN_CONTACT_ASSIGNMENTS; 'N' for rows from PN_CONTACT_ASSIGN_HISTORY.
  • LEASE_ID / LEASE_CHANGE_ID / NEW_LEASE_CHANGE_ID — Lease context; NEW_LEASE_CHANGE_ID is NULL for current rows and populated from history.
  • CONTACT_ASSIGN_HISTORY_ID — NULL for current rows; populated in history rows.
  • COMPANY_ID / COMPANY_NAME / COMPANY_SITE_ID / SITE_NAME — Company and site identity resolved via PN_COMPANIES_ALL and PN_COMPANY_SITES_ALL.
  • LEASE_ROLE_TYPE / LEASE_ROLE — The role code (PCS.LEASE_ROLE_TYPE) and its FND_LOOKUPS.MEANING, resolved through lookup type PN_LEASE_ROLE_TYPE.
  • STATUS — Assignment status value.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — Descriptive flexfield segments.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The view is most often queried to report the historical contact-role assignments for a lease, or to reconcile current assignments against their history. The presence of LEASE_ROLE and LEASE_ROLE_TYPE directly supports the search term pn_lease_role_type, since the role meaning is decoded via the PN_LEASE_ROLE_TYPE lookup.

Example: list all assignments and their history for a given lease.

  • SELECT contact_assignment_id, lease_id, lease_change_id, contact_assign_history_id, lease_role, lease_role_type, company_name, site_name, status, current_flag FROM apps.pn_contact_assign_history_v WHERE lease_id = :lease_id ORDER BY creation_date;

Example: identify active (current) assignments only.

  • SELECT lease_id, company_site_id, site_name, lease_role FROM apps.pn_contact_assign_history_v WHERE current_flag = 'Y';

Example: report contacts grouped by lease role across a company.

  • SELECT company_name, lease_role, COUNT(*) total FROM apps.pn_contact_assign_history_v WHERE current_flag = 'Y' GROUP BY company_name, lease_role;