Search Results pn_tenancy_usage_type




Overview
Explore in AI Chat

Overview

APPS.PN_TENANCIES_HISTORY_V is a reporting and integration view in Oracle Property Manager (PN), part of the Oracle E-Business Suite ETRM (Enterprise Asset and Real Estate Management) family. It exposes the historical record of tenancy assignments — the link between a tenant (customer), a lease, and a specific property location — enriched with decoded lookup meanings and denormalized customer and site information. The view is the historical counterpart to the operational PN_TENANCIES view, sourced from PN_TENANCIES_HISTORY, and is typically used for audit reporting, occupancy trend analysis, charge-back reconciliation, and downstream data extracts where the current-state tenancy record is insufficient.

The view is owned by APPS and is available in both 12.1.1 and 12.2.2. It is read-only by convention; no DML should be issued against it. A key naming distinction for users searching on pn_tenancy_usage_type is that the underlying column in PN_TENANCIES_HISTORY is TENANCY_USAGE_LOOKUP_CODE, while the view exposes the human-readable decoded value as TENANCY_USAGE_TYPE via a join to FND_LOOKUPS.

Underlying Base Objects

The view is defined over the following documented base objects:

  • PN_TENANCIES_HISTORY (synonym) — the primary driving table; supplies the historical tenancy rows and nearly all stored columns.
  • PN_TENANCIES (synonym) — referenced in the tenancy lineage; PN_TENANCIES_HISTORY generally retains superseded or archived versions of PN_TENANCIES records.
  • PN_LOCATIONS_ALL (synonym) — source of the property/location context against which the tenancy is recorded.
  • FND_LOOKUPS (view) — joined three times to decode TENANCY_USAGE_LOOKUP_CODE, RECOVERY_TYPE_CODE, and RECOVERY_SPACE_STD_CODE into their meanings.
  • HZ_PARTIES, HZ_CUST_ACCOUNTS, and HZ_CUST_SITE_USES_ALL (synonyms) — TCA tables joined on CUSTOMER_ID and CUSTOMER_SITE_USE_ID to denormalize party name, account number, and site-use location.
  • FND_GLOBAL (package) — used for session/security context (for example, ORG_ID and user identity) within the view definition or its dependent logic.

Because it reads from history plus TCA and lookups, the view incurs multiple joins and should be filtered aggressively on lease, location, or date range.

Key Columns

Common Use Cases and Queries

Typical scenarios include: historical occupancy and vacancy reporting by property; tenancy usage-type distribution analysis; lease recovery configuration audits; and tenant-facing statements requiring historical area and share figures.

Example — active/historical tenancy usage types for a lease:

SELECT tenancy_id,
       lease_id,
       location_id,
       tenancy_usage_lookup_code,
       tenancy_usage_type,
       occupancy_date,
       expiration_date,
       status
FROM   apps.pn_tenancies_history_v
WHERE  lease_id = :p_lease_id
ORDER  BY occupancy_date DESC;

Example — breakdown by tenancy usage type:

SELECT tenancy_usage_type,
       COUNT(*) tenancy_count
FROM   apps.pn_tenancies_history_v
WHERE  occupancy_date BETWEEN :p_from_date AND :p_to_date
GROUP  BY tenancy_usage_type
ORDER  BY tenancy_count DESC;

Example — current tenant detail with customer information:

SELECT h.customer_name,
       h.customer_number,
       h.location_id,
       h.tenancy_usage_type,
       h.tenants_proportionate_share,
       h.lease_rentable_area
FROM   apps.pn_tenancies_history_v h
WHERE  h.primary_flag = 'Y'
AND    h.status = 'ACTIVE';

Queries should filter on LEASE_ID, LOCATION_ID, or OCCUPANCY_DATE whenever possible to limit the join cost against PN_TENANCIES_HISTORY and the TCA lookup chain.