Search Results pn_payment_terms_history




Overview
Explore in AI Chat

Overview

The PN_PAYMENT_TERMS_HISTORY table is a core data object within the Property Manager (PN) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It serves as a historical audit trail for changes made to payment terms associated with lease agreements. In the context of lease management, payment terms define the financial obligations, such as rent amounts, payment schedules, and escalation clauses. This table captures a versioned record of these terms whenever a lease is amended or a distribution is modified, ensuring a complete and auditable financial history for each lease. Its primary role is to support accurate financial reporting, rent adjustments, and the reconstruction of lease payment obligations at any point in time.

Key Information Stored

The table's structure is designed to link historical term snapshots to specific lease events. The primary key, TERM_HISTORY_ID, uniquely identifies each historical record. Critical foreign key columns establish the necessary relationships to contextualize the data: LEASE_ID links to the master lease record in PN_LEASES_ALL; PAYMENT_TERM_ID references the specific payment term definition in PN_PAYMENT_TERMS_ALL that was in effect; LEASE_CHANGE_ID connects to the formal lease amendment in PN_LEASE_CHANGES_ALL that triggered the history record; and DISTRIBUTION_CHANGE_ID ties to distribution history in PN_DISTRIBUTIONS_HISTORY for changes related to cost allocations. While the provided metadata does not list all columns, the table typically stores the term details (like rate, frequency, and escalation details) as they existed at the time of the change, along with effective date ranges.

Common Use Cases and Queries

A primary use case is auditing the chronological evolution of rent payments for a specific lease. Financial analysts run queries against this table to generate reports showing rent changes over the lease term, which is essential for reconciliation and forecasting. Another critical use case is during the lease adjustment process, where historical term data is referenced to calculate prorations or true-ups. A common SQL pattern involves joining to the lease and payment term tables to get a readable history.

SELECT l.lease_num,
       pth.effective_start_date,
       pth.effective_end_date,
       pt.term_name,
       pt.frequency_code,
       pt.rate
FROM   pn_payment_terms_history pth,
       pn_leases_all l,
       pn_payment_terms_all pt
WHERE  pth.lease_id = l.lease_id
AND    pth.payment_term_id = pt.payment_term_id
AND    l.lease_id = :p_lease_id
ORDER BY pth.effective_start_date;

Related Objects

The PN_PAYMENT_TERMS_HISTORY table is centrally connected to several key Property Manager tables, as documented by its foreign key constraints:

  • PN_PAYMENT_TERMS_ALL: References via PAYMENT_TERM_ID. This is the source definition for the historical term snapshot.
  • PN_LEASES_ALL: References via LEASE_ID. Anchors the history to the master lease agreement.
  • PN_LEASE_CHANGES_ALL: References via LEASE_CHANGE_ID. Links the term change to a formal lease amendment document.
  • PN_DISTRIBUTIONS_HISTORY: References via DISTRIBUTION_CHANGE_ID. Connects term changes to modifications in cost distribution schedules.
  • PN_ADJUSTMENT_DETAILS: Has a foreign key to TERM_HISTORY_ID. This indicates that adjustment calculations are directly tied to specific historical term records, making this table vital for the adjustment process.