Search Results pn_opex_agreements_all




Overview
Explore in AI Chat

Overview

PN_OPEX_AGREEMENTS_ALL is the master table in the Oracle Property Manager (PN) module of Oracle E-Business Suite that stores the operating expense (opex) agreement definitions governing how recoverable operating costs are administered for a lease or tenancy. Each row represents a single opex agreement — sometimes called a recovery agreement, expense clause agreement, or CAM/opex arrangement — configured for a given tenancy, and the table holds both the agreement's identifying attributes and the extensive set of method or "overview" parameters that drive expense recovery calculations: payment frequency, caps, stops, base-year rules, proration bases, fiscal-year construction allowances, and escalation or indexation limits.

The metadata does not explicitly state a Data Vault classification. Based on the documented foreign keys — TENANCY_ID to PN_TENANCIES_ALL and TERM_TEMPLATE_ID to PN_TERM_TEMPLATES_ALL — the table behaves structurally like a satellite hanging off the tenancy hub, with TERM_TEMPLATE_ID suggesting a link to a reusable term template. Treating it as a satellite (tenancy as hub, agreement as descriptive/contextual attributes) is the most natural modeling suggestion, though its own AGREEMENT_ID surrogate and unique index also support treating it as a distinct hub in its own right.

Key Information Stored

Common Use Cases and Queries

The primary scenario is opex reconciliation and lease abstraction reporting. Analysts reconcile clause data against invoices and expense statements, verify caps, floors and stops are applied correctly, and validate base-year and proration terms. Sample query for all active agreements for a tenancy:

SELECT a.agreement_id, a.agr_num, a.agr_name,
       a.agr_start_date, a.agr_end_date,
       a.recon_pay_freq_code, a.payment_type_code,
       a.stop_amt, a.sy_cap_pct, a.sy_min_pct
FROM   pn_opex_agreements_all a
WHERE  a.tenancy_id = :p_tenancy_id
AND    a.agr_status_code = 'ACTIVE'
AND    a.org_id = :p_org_id;

Another common pattern joins to PN_TENANCIES_ALL and PN_TERM_TEMPLATES_ALL to produce a clause-level inventory or to feed a recoverability/subledger analysis. Reporting frequently filters on AGR_END_DATE to enumerate expiring agreements, and on AGR_NUM or AGR_NAME for the user's search. DFF attributes (ATTRIBUTE1–15) frequently carry client-specific clauses for reconciliation auditors.

Related Objects

  • PN_TENANCIES_ALL — referenced by PN_OPEX_AGREEMENTS_ALL.TENANCY_ID; the enclosing tenancy record.
  • PN_TERM_TEMPLATES_ALL — referenced by PN_OPEX_AGREEMENTS_ALL.TERM_TEMPLATE_ID; the reusable term template.
  • PN_LEASES_ALL — related via LEASE_ID, though not documented as a formal FK.
  • PN_EXPENSE_CLAUSES / PN_OPEX_AGREEMENT_TERMS — dependent child tables typically keyed by AGREEMENT_ID.
  • PN_RECONCILIATIONS / PN_EXPENSE_RECONCILIATIONS — reconciliation transactions that consume the agreement's payment and cap rules.
  • PN_PAY_ITEMS / PN_OPEX_ESTIMATES — payment and estimated payment records derived from agreement parameters.
  • PN_OPEX_AGREEMENTS_PK — the primary key constraint; PN_OPEX_AGR_AGREEMENT_U1 — the documented unique index.
  • ORG_ORGANIZATION_DEFINITIONS — joined via ORG_ID for operating unit validation.