Results for “pn_var_constr_templates_all”

27 results




Overview
Explore in AI Chat

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

Overview

PN_VAR_CONSTR_TEMPLATES_ALL is a Property Manager (PN) module table in Oracle EBS 12.1.1 and 12.2.2 that stores constraint template definitions used in variable lease and agreement structuring. It resides in the PN schema and is documented as VALID with 13 columns. The table acts as a definitional or setup entity that captures template-level constraint rules, which are subsequently applied to individual agreement drafts. Its primary key is PN_VAR_CONSTR_TEMPLATES_PK, defined on CONSTR_TEMPLATE_ID.

From a Data Vault modeling perspective, the mined FK structure suggests this object behaves as a hub rather than a link or satellite. Although the heuristic classification lists it as "standalone," the presence of a unique, single-column primary key (CONSTR_TEMPLATE_ID) and an inbound reference from PN_VAR_CONSTR_DEFAULTS_ALL indicates a stable business entity whose descriptive attributes could be modeled as a satellite. The reference to PN_VAR_TEMPLATES_ALL via AGREEMENT_TEMPLATE_ID further reinforces its role as a constrained business key referencing a template hub. This classification is offered as a modeling suggestion, not a documented designation.

Key Information Stored

The table's surrogate primary key is CONSTR_TEMPLATE_ID, also implemented as unique index PN_VAR_CONSTR_TEMPLATES_U1, making it the business-key candidate for the constraint template. The following columns carry the substantive business meaning:

  • CONSTR_TEMPLATE_ID — surrogate and business key uniquely identifying each constraint template.
  • CONSTR_DETAIL_NUM — sequence or detail number distinguishing individual constraint lines within a template.
  • AGREEMENT_TEMPLATE_ID — foreign key to PN_VAR_TEMPLATES_ALL, associating the constraint template with its parent variable agreement template.
  • CONSTR_CAT_CODE — constraint category code, classifying the type of constraint being template-defined.
  • TYPE_CODE — code identifying the specific constraint type within a category.
  • AMOUNT — numeric value representing the constraint threshold or boundary.
  • COMMENTS — free-text description of the constraint template.
  • ORG_ID — operating unit identifier supporting multi-org (MOAC) data partitioning.
  • CREATION_DATE, CREATED_BY — standard audit columns recording initial insert metadata.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — audit columns capturing the most recent maintenance.

The ORG_ID column confirms that constraint templates are operating-unit-specific, and queries must respect MOAC security context. The audit columns follow standard EBS WHO column conventions.

Common Use Cases and Queries

Typical usage centers on validating or reporting the constraint definitions that govern variable agreements. A common query retrieves all constraint templates attached to a given agreement template:

  • SELECT c.CONSTR_TEMPLATE_ID, c.CONSTR_DETAIL_NUM, c.CONSTR_CAT_CODE, c.TYPE_CODE, c.AMOUNT FROM PN_VAR_CONSTR_TEMPLATES_ALL c WHERE c.AGREEMENT_TEMPLATE_ID = :template_id AND c.ORG_ID = :org_id;
  • Reporting the count of constraint templates per category: SELECT CONSTR_CAT_CODE, COUNT(*) FROM PN_VAR_CONSTR_TEMPLATES_ALL GROUP BY CONSTR_CAT_CODE;
  • Audit and change tracking: SELECT CONSTR_TEMPLATE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY FROM PN_VAR_CONSTR_TEMPLATES_ALL ORDER BY LAST_UPDATE_DATE DESC;
  • Joining to defaults to detect templates lacking default values: SELECT t.CONSTR_TEMPLATE_ID FROM PN_VAR_CONSTR_TEMPLATES_ALL t WHERE NOT EXISTS (SELECT 1 FROM PN_VAR_CONSTR_DEFAULTS_ALL d WHERE d.CONSTR_TEMPLATE_ID = t.CONSTR_TEMPLATE_ID);

These patterns support setup validation, migration reconciliation, and operational reporting on lease constraint configuration.

Related Objects

The most significant related objects, derived from the documented FK structure, are:

  • PN_VAR_TEMPLATES_ALL — referenced by PN_VAR_CONSTR_TEMPLATES_ALL.AGREEMENT_TEMPLATE_ID; the parent variable agreement template.
  • PN_VAR_CONSTR_DEFAULTS_ALL — references this table via CONSTR_TEMPLATE_ID; holds default values per constraint template.
  • PN_VAR_CONSTR_TEMPLATES_PK — the primary key constraint/index on CONSTR_TEMPLATE_ID.
  • PN_VAR_CONSTR_TEMPLATES_U1 — the unique index validating the business key.
  • PN schema constraint categories (CONSTR_CAT_CODE lookups) — code validation for constraint categorization.

Together these objects form the constraint definition, default, and template hierarchy used throughout Property Manager variable agreement processing.