Search Results pn_opex_critical_dates_pk

Overview

PN.PN_OPEX_CRITICAL_DATES_ALL is a Property Manager (PN) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the critical date definitions attached to operating expense (opex) agreements. Critical dates represent the contractual milestones that drive escalation, reconciliation, notification, and renewal processing for leased or managed properties — for example the date on which a landlord must be notified, a reconciliation period that must be closed, or an escalation anniversary that must be applied. The table is owned by the PN schema and is documented as VALID in the ETRM repository.

Because organizations operate across multiple operating units, the table is suffixed _ALL and carries an ORG_ID column, indicating it is a multi-org (partitioned by operating unit) object. Rows are keyed by the surrogate primary key PN_OPEX_CRITICAL_DATES_PK, defined on CRITICAL_DATE_ID.

The heuristic Data Vault classification mined from the foreign-key structure labels this object as standalone. In Data Vault modeling terms this suggests the table behaves as a self-contained hub-like entity: it is not a pure junction/link between two other hubs in the mined graph, nor is it modeled as a dependent satellite. Where a formal Data Vault design is desired, CRITICAL_DATE_ID would serve as the hub business key, with descriptive attributes such as type, timing, and the ATTRIBUTE1–15 flex columns held in a satellite.

Key Information Stored

The table exposes a documented physical schema of 30 columns. The most operationally significant are:

• CRITICAL_DATE_ID — surrogate primary key; the unique identifier for each critical date record and the column referenced by dependent tables.
• AGREEMENT_ID — foreign key to the parent operating expense agreement; the anchor that ties each critical date to a specific opex contract.
• CRITICAL_DATE_TYPE_CODE and CRITICAL_DATE_TYPE_NAME — the coded value and descriptive name of the critical date category (for example notification, escalation, or reconciliation types).
• TIME_UNIT and TIME_UNIT_CODE — the unit of measure (days, months, years) applied when offsetting the critical date.
• WHEN_CODE — the timing rule that determines when the critical date falls relative to the agreement or event.
• EVENT_CODE — the triggering event that the critical date is evaluated against.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns, used to capture client-specific critical date attributes without schema change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the WHO audit columns required on all EBS transactional tables.
• ORG_ID — the operating unit that owns the row, enforcing multi-org security.

A unique index, PN_OPEX_CRT_DTS_U1, is documented on CRITICAL_DATE_ID; this reinforces the surrogate key but does not by itself constitute a composite business key across agreement and date type.

Common Use Cases and Queries

The primary use case is opex critical date reporting and alerting. Property administrators query this table to drive notification calendars, reconciliation schedules, and escalation workflows. A typical join retrieves all critical dates for a given agreement:

• Operational dashboards listing upcoming critical dates filtered by ORG_ID and CRITICAL_DATE_TYPE_CODE.
• Reconciliation processing that links critical dates to reconciliation records via CRITICAL_DATE_ID.
• Escalation automation keyed on EVENT_CODE and WHEN_CODE to derive the actual due date from TIME_UNIT.
• Data extraction and DFF reporting using ATTRIBUTE_CATEGORY plus ATTRIBUTE1–ATTRIBUTE15. A representative pattern:

SELECT c.critical_date_id, c.agreement_id, c.critical_date_type_name, c.when_code, c.event_code FROM pn_opex_critical_dates_all c WHERE c.org_id = :p_org_id AND c.agreement_id = :p_agreement_id;

Related Objects

• PN_OPEX_RECON_CRDT_ALL — dependent reconciliation critical date table; joins on CRITICAL_DATE_ID, the documented inbound foreign key.
• PN_OPEX_AGREEMENTS_ALL — the parent agreement entity referenced through AGREEMENT_ID (the natural business anchor).
• PN_OPEX_CRITICAL_DATES_PK — the primary key constraint/index backing CRITICAL_DATE_ID.
• PN_OPEX_CRT_DTS_U1 — the unique index on CRITICAL_DATE_ID.
• Property Manager opex reconciliation and escalation concurrent programs that consume critical date rows during period processing.