Search Results payment_status_lookup_code




Overview
Explore in AI Chat

Overview

PN_PAYMENT_SCHEDULES_ALL is a core transactional table within the Oracle E-Business Suite Property Manager (PN) module. It stores payment and billing schedules applicable to both landlord and tenant leases. In the Property Manager data model, this table acts as the central repository where scheduled lease payments or billing obligations are recorded, approved, transferred, and tracked through their lifecycle. Each row represents a discrete payment or billing schedule entry tied to a specific lease and, optionally, a specific lease change event.

From a Data Vault modeling perspective, the heuristic classification of this table is satellite-leaning. The table is anchored by a surrogate primary key (PAYMENT_SCHEDULE_ID) and carries several foreign keys pointing to hub-like business entities such as PN_LEASES_ALL, PN_LEASE_CHANGES_ALL, and FND_USER. This structure suggests a satellite attached to the lease hub, with descriptive and status attributes that evolve over time. The table exists in both the 12.1.1 and 12.2.2 releases with a documented physical schema of 33 columns owned by the PN schema.

Key Information Stored

The table's primary key is the surrogate key PAYMENT_SCHEDULE_ID, which is also enforced by the unique index PN_PAYMENT_SCHEDULES_U1, making it the definitive business-key candidate. The most significant columns include:

  • PAYMENT_SCHEDULE_ID — surrogate primary key uniquely identifying each schedule row.
  • LEASE_ID — foreign key to PN_LEASES_ALL, linking the schedule to its parent lease.
  • LEASE_CHANGE_ID — foreign key to PN_LEASE_CHANGES_ALL, associating the schedule with a lease modification event.
  • SCHEDULE_DATE — the effective date on which the scheduled payment or billing applies.
  • PAYMENT_STATUS_LOOKUP_CODE — lookup code indicating the current status of the schedule (e.g., pending, approved, transferred).
  • APPROVED_BY_USER_ID — foreign key to FND_USER identifying the approving user.
  • APPROVAL_DATE — timestamp of when the schedule was approved.
  • TRANSFERRED_BY_USER_ID — foreign key to FND_USER identifying the user who transferred the schedule.
  • TRANSFER_DATE — timestamp of transfer activity.
  • PERIOD_NAME — the accounting period associated with the schedule.
  • ORG_ID — operating unit identifier supporting multi-org access control.
  • ON_HOLD — flag indicating whether the schedule is currently on hold.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield columns for extensible client-specific data.
  • Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) for audit tracking.

Common Use Cases and Queries

Typical reporting scenarios include retrieving all schedules for a given lease, tracking approval and transfer workflows, and auditing payment status by accounting period. A representative query joins the schedule to its parent lease:

  • Listing pending schedules by lease: SELECT PAYMENT_SCHEDULE_ID, SCHEDULE_DATE, PAYMENT_STATUS_LOOKUP_CODE FROM PN_PAYMENT_SCHEDULES_ALL WHERE LEASE_ID = :lease_id AND PAYMENT_STATUS_LOOKUP_CODE = 'PENDING';
  • Identifying approved schedules within a period: filtering by PERIOD_NAME and APPROVAL_DATE to reconcile billing activity.
  • Auditing transfers: joining TRANSFERRED_BY_USER_ID to FND_USER to report who transferred schedules and when.
  • Multi-org reporting: constraining by ORG_ID in conjunction with operating unit security profiles.
  • Hold analysis: querying rows where ON_HOLD = 'Y' to flag schedules requiring attention.

Related Objects

The most significant objects related to PN_PAYMENT_SCHEDULES_ALL are defined by its foreign key relationships:

  • PN_LEASES_ALL — joined via LEASE_ID; the parent lease record.
  • PN_LEASE_CHANGES_ALL — joined via LEASE_CHANGE_ID; captures lease amendments driving schedule creation.
  • FND_USER — joined via APPROVED_BY_USER_ID and TRANSFERRED_BY_USER_ID for workflow and audit reporting.
  • PN_PAYMENT_ITEMS_ALL — references this table via PAYMENT_SCHEDULE_ID; holds the individual payment line items generated from each schedule.

These relationships make PN_PAYMENT_SCHEDULES_ALL the pivot between lease agreements, lease changes, and the granular payment items that flow into financial accounting.