Results for “pn_opex_recon_crdt_all”

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

Overview

PN_OPEX_RECON_CRDT_ALL is a Property Manager (PN) transaction table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores critical date information associated with operating expense (opex) reconciliations. Each row represents a scheduled or actual milestone that governs the lifecycle of an opex reconciliation — for example, dates driving the reconciliation window, escalation cutoffs, or expiration events. The table sits at the intersection of two parent entities: the reconciliation header (PN_OPEX_RECON_ALL) and the seeded or user-defined critical date definition (PN_OPEX_CRITICAL_DATES_ALL), making it a dependent child that resolves a many-to-many-style association between reconciliations and critical date types.

From a heuristic Data Vault perspective, the mined FK structure classifies this table as standalone, but the presence of a surrogate key plus two foreign keys (RECON_ID and CRITICAL_DATE_ID) strongly suggests a link-style saturating entity — it links a reconciliation to a critical date definition while carrying its own descriptive and audit attributes. The unique index PN_OPEX_RECON_CRDT_U1 on DATE_ID reinforces that the surrogate key, not the composite FK pair, is the row identifier.

Key Information Stored

The table contains 28 documented columns. The most significant are:

• DATE_ID — surrogate primary key (constraint PN_OPEX_RECON_CRDT_PK). It is also the sole column in the unique index PN_OPEX_RECON_CRDT_U1, making it the documented business-key candidate for row identity.
• RECON_ID — foreign key to PN_OPEX_RECON_ALL; identifies the parent operating expense reconciliation.
• CRITICAL_DATE_ID — foreign key to PN_OPEX_CRITICAL_DATES_ALL; identifies which critical date definition this row instantiates.
• DATE_TYPE — classifies the row's critical date category.
• DT_OVR — the overridden (effective) date value applied to the reconciliation.
• DT_EXP — the expected or expiration date value for the critical date.
• ORG_ID — operating unit identifier, enabling multi-org (MOAC) security and reporting.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS DFF (descriptive flexfield) block for extensible, client-specific date metadata.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS WHO columns providing audit lineage.

Common Use Cases and Queries

This table supports reconciliation monitoring, escalation scheduling, and date-dependent reporting. Typical queries join to the parent reconciliation to report upcoming deadlines per lease or property:

• Reconciliation date listing:
  SELECT c.DATE_ID, c.RECON_ID, c.DATE_TYPE, c.DT_OVR, c.DT_EXP
FROM PN_OPEX_RECON_CRDT_ALL c
WHERE c.RECON_ID = :recon_id
ORDER BY c.DT_EXP;
• Overdue critical dates:
  SELECT r.RECON_ID, c.DATE_TYPE, c.DT_EXP
FROM PN_OPEX_RECON_CRDT_ALL c, PN_OPEX_RECON_ALL r
WHERE c.RECON_ID = r.RECON_ID
AND c.DT_EXP < SYSDATE;
• Critical date definition lookup:
  SELECT c.DATE_ID, d.CRITICAL_DATE_ID, d.DATE_TYPE
FROM PN_OPEX_RECON_CRDT_ALL c, PN_OPEX_CRITICAL_DATES_ALL d
WHERE c.CRITICAL_DATE_ID = d.CRITICAL_DATE_ID;
• DFF reporting extracts flattening the ATTRIBUTE columns for property-specific date tracking.
• Multi-org filtered extracts using ORG_ID for operating-unit-specific reconciliation dashboards.

Related Objects

• PN_OPEX_RECON_ALL — parent reconciliation header; joined via RECON_ID.
• PN_OPEX_CRITICAL_DATES_ALL — critical date definition master; joined via CRITICAL_DATE_ID.
• PN_OPEX_RECON_CRDT_PK — primary key constraint on DATE_ID.
• PN_OPEX_RECON_CRDT_U1 — unique index confirming business-key identity on DATE_ID.
• Other PN_OPEX_RECON_* children (line, detail, and distribution tables) that share the RECON_ID parent and feed reconciliation processing.
• Standard PN reconciliation APIs and concurrent programs that read critical date rows to drive date-based processing.