Search Results pn_options_history




Overview
Explore in AI Chat

Overview

The PN_OPTIONS_HISTORY table is a core data object within the Oracle E-Business Suite Property Manager (PN) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to serve as an audit and historical tracking repository for modifications made to lease option details. In the context of lease management, options are contractual clauses granting rights, such as renewal or expansion, which are critical for financial forecasting and compliance. This table systematically captures the state of an option before and after a change event, ensuring a complete audit trail. It is integral to maintaining data integrity and supporting lease administration, accounting reconciliations, and strategic portfolio analysis by preserving the evolution of key lease terms over time.

Key Information Stored

While the provided metadata does not list all columns, the defined relationships and primary key structure reveal the essential data points. The table is anchored by the OPTION_HISTORY_ID, a unique system-generated identifier serving as the primary key. Crucially, it stores foreign key references that contextualize each historical record: the OPTION_ID links to the PN_OPTIONS_ALL table to identify the specific option being modified; the LEASE_ID references PN_LEASES_ALL to tie the history to the master lease agreement; and the LEASE_CHANGE_ID links to PN_LEASE_CHANGES_ALL, which documents the overarching change event that triggered the option modification. The table likely contains columns capturing the previous and new values for critical option attributes—such as option type, exercise date, notice period, and associated rental terms—along with standard who columns (CREATED_BY, CREATION_DATE) to record the audit details.

Common Use Cases and Queries

A primary use case is generating audit reports for lease compliance and internal controls, tracing how and when specific option terms were altered. Financial analysts may query this history to understand the impact of option changes on future liability projections. A common SQL pattern involves joining to related master tables to produce a readable history report. For example:

  • SELECT poh.OPTION_HISTORY_ID, l.LEASE_NUM, o.OPTION_NAME, poh.CREATION_DATE
    FROM PN_OPTIONS_HISTORY poh,
    PN_LEASES_ALL l,
    PN_OPTIONS_ALL o
    WHERE poh.LEASE_ID = l.LEASE_ID
    AND poh.OPTION_ID = o.OPTION_ID
    AND l.LEASE_ID = :p_lease_id
    ORDER BY poh.CREATION_DATE DESC;

This query retrieves the chronological history of option changes for a given lease, which is essential for lease abstraction validation and during lease renegotiations.

Related Objects

The PN_OPTIONS_HISTORY table has documented foreign key relationships with three key Property Manager tables, as per the provided metadata:

  • PN_LEASES_ALL: Linked via PN_OPTIONS_HISTORY.LEASE_ID. This is the master lease table.
  • PN_LEASE_CHANGES_ALL: Linked via PN_OPTIONS_HISTORY.LEASE_CHANGE_ID. This table records formal lease amendment events.
  • PN_OPTIONS_ALL: Linked via PN_OPTIONS_HISTORY.OPTION_ID. This is the current definition table for all lease options.

These relationships position PN_OPTIONS_HISTORY as a dependent child table, ensuring referential integrity. It is queried in conjunction with these parent tables to provide full historical context for any lease option analysis or reporting.