Results for “pn_options_v”

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

Overview

PN_OPTIONS_V is a reporting and inquiry view owned by the APPS schema in Oracle E-Business Suite, belonging to the Property Manager (PN) product family. It presents lease option records — the contractual rights a lessee or lessor holds to renew, terminate, expand, or otherwise modify a lease — in a fully denormalized, user-readable form. The base transactional table PN_OPTIONS stores option data using coded values and foreign key identifiers; the view resolves those codes into descriptive meanings and derives additional calculated attributes, eliminating the need for downstream reports, concurrent programs, and integrations to perform their own joins and lookups.

In the 12.2.2 documentation the view is listed with a VALID status in the APPS schema, and its text confirms a multi-table join across the option table, two lookup views, and a units-of-measure synonym. Because the view is a stored SQL statement rather than a table, it carries no independent storage, no indexes of its own, and no triggers; all performance characteristics are inherited from the underlying objects.

Underlying Base Objects

The documented dependencies are:

• PN_OPTIONS (SYNONYM) — the primary base table, aliased OPT, supplying all option rows and the majority of exposed columns. The synonym resolves to the APPS-owned transactional table in the PN schema.
• FND_LOOKUPS (VIEW) — referenced twice, as TLOOK and OLOOK, to translate OPTION_TYPE_CODE and OPTION_STATUS_LOOKUP_CODE into their display meanings.
• MTL_UNITS_OF_MEASURE (SYNONYM) — joined with an outer-join operator (UOM.UOM_CODE (+) = OPT.UOM_CODE) so that options without an assigned unit of measure still return rows.
• FND_GLOBAL (PACKAGE) — the standard EBS context package, referenced by the view for session and organizational context resolution, including ORG_ID.

Both lookup joins are inner joins constrained by lookup type — PN_LEASE_OPTION_TYPE and PN_OPTION_STATUS_TYPE — so an option whose code is missing from FND_LOOKUPS is not returned by the view.

Key Columns

Identity and audit columns include ROW_ID (the base table ROWID), OPTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. Lease linkage is provided by LEASE_ID and LEASE_CHANGE_ID, while OPTION_NUM distinguishes multiple options on the same lease.

Descriptive and derived columns include:

• OPTION_TYPE / OPTION_TYPE_CODE and OPTION_STATUS_TYPE / OPTION_STATUS_LOOKUP_CODE — the decoded and coded forms of the type and status lookups.
• START_DATE, EXPIRATION_DATE, OPTION_TERM — the term is computed as TRUNC(EXPIRATION_DATE) - TRUNC(START_DATE) + 1, giving an inclusive day count.
• OPTION_SIZE, UOM_CODE, UNIT_OF_MEASURE — the option quantity and its decoded unit.
• OPTION_EXER_START_DATE, OPTION_EXER_END_DATE, OPTION_ACTION_DATE — the exercise window and the date the option was acted upon.
• OPTION_COST, OPTION_AREA_CHANGE, OPTION_REFERENCE, OPTION_NOTICE_REQD, OPTION_COMMENTS — commercial terms and free-text notes.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 and ORG_ID — the standard descriptive flexfield columns and multiorg partition column.

Common Use Cases and Queries

Typical consumers include lease administration reports, option expiry alerts, and integration extracts feeding a data warehouse. Because the view supplies decoded values, it is well suited to read-only reporting rather than to updates.

Listing active options expiring in the next ninety days:

SELECT option_id, lease_id, option_num, option_type,
       option_status_type, expiration_date, option_term
  FROM apps.pn_options_v
 WHERE option_status_lookup_code = 'ACTIVE'
   AND expiration_date BETWEEN SYSDATE AND SYSDATE + 90
 ORDER BY expiration_date;

Summarizing options by type for a given operating unit:

SELECT option_type, COUNT(*) option_count
  FROM apps.pn_options_v
 WHERE org_id = :p_org_id
 GROUP BY option_type;

Note that the view is subject to the standard MOAC and lookup constraints of its base objects; queries should filter on ORG_ID where multiorg security applies, and users must hold the appropriate PN responsibility and data access to return meaningful results.