Search Results pn_contact_assign_history




Overview
Explore in AI Chat

Overview

The PN_CONTACT_ASSIGN_HISTORY table is a core data object within the Oracle E-Business Suite (EBS) Property Manager (PN) module. Its primary function is to provide a historical audit trail for changes made to contacts associated with a lease. In the context of lease management, contacts represent individuals or roles (e.g., property manager, tenant representative, legal counsel) linked to the involved parties. This table systematically captures the assignment and modification of these contacts over the lifecycle of a lease agreement, ensuring a complete record for compliance, reporting, and analytical purposes. It is a critical component for maintaining data integrity and understanding the evolution of stakeholder relationships tied to a property lease.

Key Information Stored

The table's structure is designed to link a specific contact assignment event to the relevant lease and organizational entities while recording the nature of the change. The primary key, CONTACT_ASSIGN_HISTORY_ID, uniquely identifies each historical record. Essential foreign key columns establish the context of the change: LEASE_ID links to the master lease record in PN_LEASES_ALL; CONTACT_ASSIGNMENT_ID references the specific contact assignment in PN_CONTACT_ASSIGNMENTS_ALL; and COMPANY_ID and COMPANY_SITE_ID connect to the party and its site in PN_COMPANIES_ALL and PN_COMPANY_SITES_ALL, respectively. Furthermore, the LEASE_CHANGE_ID column can associate the contact modification with a formal lease amendment or change document recorded in PN_LEASE_CHANGES_ALL, while LOCATION_ID ties the assignment to a specific property location from PN_LOCATIONS_ALL.

Common Use Cases and Queries

A primary use case is auditing the complete history of contacts for a given lease, which is vital for dispute resolution or understanding past communications. For example, a report showing all contact changes for a lease would join this table to the lease and contact assignment tables. Another common scenario is generating a report of all contacts assigned during a specific lease amendment period by filtering on LEASE_CHANGE_ID. System triggers or application logic within Property Manager typically populate this table automatically whenever a user creates, updates, or ends a contact assignment via the standard UI or API.

Sample Query Pattern:
SELECT hist.*, lease.lease_num, party.party_name
FROM pn_contact_assign_history hist,
pn_leases_all lease,
hz_parties party
WHERE hist.lease_id = lease.lease_id
AND hist.company_id = party.party_id
AND lease.lease_id = :p_lease_id
ORDER BY hist.creation_date;

Related Objects

The PN_CONTACT_ASSIGN_HISTORY table maintains defined foreign key relationships with several core Property Manager tables, as documented in the ETRM:

  • PN_LEASES_ALL via LEASE_ID: The master lease record to which the contact history is linked.
  • PN_CONTACT_ASSIGNMENTS_ALL via CONTACT_ASSIGNMENT_ID: The specific contact assignment record that was modified.
  • PN_COMPANIES_ALL via COMPANY_ID: The organization (party) associated with the contact.
  • PN_COMPANY_SITES_ALL via COMPANY_SITE_ID: The specific site or address of the associated company.
  • PN_LEASE_CHANGES_ALL via LEASE_CHANGE_ID: The formal lease change document that may have prompted the contact update.
  • PN_LOCATIONS_ALL via LOCATION_ID: The property location related to the contact assignment.

These relationships are essential for constructing accurate joins in historical reports and for understanding the data model's referential integrity.