Results for “pn_var_constraints_all”

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

Overview

PN_VAR_CONSTRAINTS_ALL is a Property Manager (PN) schema table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores constraints applied to a period for the calculation of variable rent. In lease and property management scenarios, variable rent is typically tied to a metric such as percentage of sales or a threshold amount, and the constraint rows held in this table define the bounds, categories, and effective dates that govern how the variable rent amount is computed for a given period.

From a Data Vault modeling perspective, the relationship data mined from the foreign key structure classifies this object heuristically as a link. This is a reasonable suggestion: the table primarily associates a period with constraint templates, constraint defaults, and agreement templates, and it carries transaction-level constraint attributes. It therefore mediates relationships between multiple parent entities rather than serving as a pure descriptive hub or a satellite of a single business key.

Key Information Stored

The table contains 34 columns and is owned by the PN schema in ETRM 12.2.2. The most significant columns include:

• CONSTRAINT_ID — the surrogate primary key, enforced by PN_VAR_CONSTRAINTS_PK and the unique index PN_VAR_CONSTRAINTS_U1. This is the documented business-key candidate.
• CONSTRAINT_NUM — the user-facing constraint number, used for identification and reference in forms and reports.
• PERIOD_ID — foreign key to PN_VAR_PERIODS_ALL, identifying the variable rent period to which the constraint applies.
• CONSTR_CAT_CODE — the constraint category code, indicating the classification of the constraint.
• TYPE_CODE — the constraint type, distinguishing the nature of the bound or limit being applied.
• AMOUNT — the monetary or quantitative value associated with the constraint.
• CONSTR_START_DATE and CONSTR_END_DATE — the effective date range during which the constraint is active for the period.
• AGREEMENT_TEMPLATE_ID — foreign key to PN_VAR_TEMPLATES_ALL, linking the constraint to the governing variable rent agreement template.
• CONSTR_TEMPLATE_ID — foreign key to PN_VAR_CONSTR_TEMPLATES_ALL, identifying the constraint template from which the row was derived.
• CONSTR_DEFAULT_ID — foreign key to PN_VAR_CONSTR_DEFAULTS_ALL, identifying default constraint values.
• ORG_ID — the operating unit identifier supporting multi-org data partitioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns for extensible, customer-defined attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — the standard EBS audit columns.

Common Use Cases and Queries

Typical scenarios include reporting variable rent constraints by period, auditing the constraints applied to a specific agreement, and tracing constraint values back to their templates and defaults. A common query joins the table to its parent period table:

• Listing all constraints for a given period: SELECT c.CONSTRAINT_NUM, c.CONSTR_CAT_CODE, c.TYPE_CODE, c.AMOUNT, c.CONSTR_START_DATE, c.CONSTR_END_DATE FROM PN_VAR_CONSTRAINTS_ALL c WHERE c.PERIOD_ID = :period_id;
• Retrieving constraints for an agreement template: filter on AGREEMENT_TEMPLATE_ID.
• Comparing constraint rows against their source templates via CONSTR_TEMPLATE_ID and CONSTR_DEFAULT_ID to detect overrides.
• Operating-unit-scoped reporting using ORG_ID, and leveraging ATTRIBUTE columns for custom DFF reporting.

Related Objects

The table depends on and references several PN objects through documented foreign keys:

• PN_VAR_PERIODS_ALL — joined on PN_VAR_CONSTRAINTS_ALL.PERIOD_ID. Defines the variable rent period context.
• PN_VAR_CONSTR_TEMPLATES_ALL — joined on CONSTR_TEMPLATE_ID. Provides the constraint template definitions.
• PN_VAR_CONSTR_DEFAULTS_ALL — joined on CONSTR_DEFAULT_ID. Supplies default constraint values.
• PN_VAR_TEMPLATES_ALL — joined on AGREEMENT_TEMPLATE_ID. Identifies the governing agreement template.

These four parent tables, together with the PN_VAR_CONSTRAINTS_PK and PN_VAR_CONSTRAINTS_U1 indexes, constitute the principal dependency footprint for integration, reporting, and data migration work involving variable rent constraints.