Results for “pn_option_status_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view APPS.PN_OPTIONS_HISTORY_V is a consolidated reporting object within the Oracle Property Manager (PN) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It presents the full lifecycle history of lease options — rights such as renewals, terminations, expansions, and purchase options that a lessee may exercise against a leased property. The view merges current option records with their archived historical versions so that report authors and integration developers can retrieve a unified chronological picture of every option associated with a lease.

The view is owned by the APPS schema and is typically consumed by Property Manager inquiry screens, concurrent reports, and custom extensions. Because it is a UNION ALL construct, each physical option may appear multiple times: once as the current active row and once for each historical revision preserved in the history table. The synthetic column CURRENT_FLAG distinguishes the live record from archival snapshots.

Underlying Base Objects

The ETRM metadata documents the following referenced objects:

• PN_OPTIONS (synonym) — the transactional table holding the active lease option definitions. It supplies the majority of the columns in the first branch of the UNION.
• PN_OPTIONS_HISTORY (synonym) — the audit/history table that stores superseded versions of option rows.
• FND_LOOKUPS (view) — joined twice, once as alias tlook for lookup type PN_LEASE_OPTION_TYPE and once as alias olook for lookup type PN_OPTION_STATUS_TYPE, to resolve descriptive meanings.
• MTL_UNITS_OF_MEASURE (synonym) — outer-joined (uom.uom_code (+)) to translate the option's unit of measure code into a descriptive name.
• FND_GLOBAL (package) — referenced by the view metadata, typically to supply the current user or application context in the history branch.

The first SELECT returns current rows from PN_OPTIONS with CURRENT_FLAG = 'Y' and NULL placeholders for OPTION_HISTORY_ID and NEW_LEASE_CHANGE_ID. The UNION ALL then appends the corresponding rows from PN_OPTIONS_HISTORY, supplying the actual history identifier values.

Key Columns

• OPTION_ID — primary identifier linking the option to its parent lease.
• LEASE_ID and LEASE_CHANGE_ID — identify the lease and any associated change order.
• OPTION_TYPE_CODE / OPTION_TYPE — the lookup code and its translated meaning (renewal, expansion, termination, purchase, etc.).
• OPTION_STATUS_LOOKUP_CODE / OPTION_STATUS_TYPE — the status code and the decoded meaning drawn from PN_OPTION_STATUS_TYPE. This is the column users reference when searching for pn_option_status_type.
• START_DATE, EXPIRATION_DATE, and OPTION_TERM — the derived term computed as TRUNC(expiration_date) - TRUNC(start_date) + 1.
• OPTION_SIZE and UOM_CODE / UNIT_OF_MEASURE — the quantified option measurement.
• OPTION_EXER_START_DATE, OPTION_EXER_END_DATE, OPTION_ACTION_DATE, OPTION_COST, OPTION_AREA_CHANGE, OPTION_REFERENCE, OPTION_NOTICE_REQD, and OPTION_COMMENTS — exercise-window, financial, and descriptive attributes.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard flexfield descriptor columns.
• CURRENT_FLAG — 'Y' for the active option row, distinguishing it from history snapshots.
• OPTION_HISTORY_ID, NEW_LEASE_CHANGE_ID — populated only for historical rows.

Common Use Cases and Queries

Typical scenarios include audit reporting on option status transitions, reconciliation of historical option changes, and extraction feeds into property management dashboards. Because the option status meaning is already decoded in the view, consumers avoid additional joins to FND_LOOKUPS.

To retrieve the current status of every option on a lease:

SELECT option_id, option_num, option_type, option_status_type,
       start_date, expiration_date, option_term
FROM   apps.pn_options_history_v
WHERE  lease_id = :p_lease_id
AND    current_flag = 'Y'
ORDER BY option_num;

To trace the status history of a specific option:

SELECT option_id, option_status_type, last_update_date,
       current_flag, option_history_id
FROM   apps.pn_options_history_v
WHERE  option_id = :p_option_id
ORDER BY last_update_date;

The view is read-only and should not be used for DML. Reports requiring only active options should filter on CURRENT_FLAG = 'Y' to avoid duplicate rows introduced by the UNION ALL.