Results for “base_year”

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

Overview

PN_REC_AGR_LINCONST_ALL is the Recovery Constraint setup table in the Oracle EBS Property Manager (PN) module. It stores constraint definitions applied against recovery agreement lines, governing how recovery amounts are calculated and capped within tenant recovery processing. Each row associates a constraint with a specific recovery agreement line and defines the constraint's scope, relation, comparison value, and effective date range. The table is org-striped, carrying an ORG_ID column that partitions data by operating unit and enforces multi-org security through the standard HR_ALL_ORGANIZATION_UNITS reference.

From a Data Vault modeling perspective, and based on the mined foreign key structure, this table is best classified heuristically as a link. It resides on the transactional intersection between recovery agreement lines, organizations, and index history headers, rather than acting as a standalone hub of business entities or a pure descriptive satellite.

Key Information Stored

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

• REC_AGR_LINCONST_ID — the surrogate primary key (PN_REC_AGR_LINCONST_PK) uniquely identifying each constraint row. It also appears in the unique index PN_REC_AGR_LINCONST_U1, making it the documented business-key candidate.
• REC_AGR_LINE_ID — foreign key to PN_REC_AGR_LINES_ALL, tying the constraint to its parent recovery agreement line.
• START_DATE / END_DATE — the effective date range during which the constraint applies.
• SCOPE — defines the dimension or range to which the constraint is applied.
• RELATION — the relational operator (for example, equals, greater than, less than) governing the comparison.
• VALUE — the threshold or comparison value used with RELATION.
• CPI_INDEX — foreign key to PN_INDEX_HISTORY_HEADERS, indicating the index series used for escalation or adjustment.
• BASE_YEAR — the reference year against which index-based calculation is anchored.
• CONSTR_ORDER — sequencing column controlling the order in which multiple constraints are evaluated.
• ORG_ID — operating unit identifier, foreign key to HR_ALL_ORGANIZATION_UNITS.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns for extensible, customer-defined attributes.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard who-columns for row-level auditing.

Common Use Cases and Queries

Typical use cases include reporting on the constraints attached to a recovery agreement, validating that overlapping effective dates do not conflict for the same line, and tracing escalation behavior back to the referenced CPI index. A representative query joining the constraint to its parent line is:

• SELECT c.REC_AGR_LINCONST_ID, c.REC_AGR_LINE_ID, c.SCOPE, c.RELATION, c.VALUE, c.START_DATE, c.END_DATE FROM PN_REC_AGR_LINCONST_ALL c WHERE c.REC_AGR_LINE_ID = :line_id AND SYSDATE BETWEEN c.START_DATE AND c.END_DATE;
• SELECT c.CONSTR_ORDER, c.SCOPE, c.RELATION, c.VALUE FROM PN_REC_AGR_LINCONST_ALL c WHERE c.ORG_ID = :org_id ORDER BY c.CONSTR_ORDER;

Because the table includes DFF columns, reporting queries frequently pivot ATTRIBUTE_CATEGORY and the ATTRIBUTE1–15 values to expose customer-specific constraint metadata.

Related Objects

• PN_REC_AGR_LINES_ALL — parent recovery agreement lines; joined on REC_AGR_LINE_ID. This is the principal relationship, and it is referenced twice in the documented foreign key set.
• HR_ALL_ORGANIZATION_UNITS — joined on ORG_ID to resolve the operating unit.
• PN_INDEX_HISTORY_HEADERS — joined on CPI_INDEX to retrieve index history for escalation calculations.
• PN_REC_AGR_LINCONST_PK — the primary key constraint enforcing uniqueness of REC_AGR_LINCONST_ID.
• PN_REC_AGR_LINCONST_U1 — the unique index supporting the documented business key.

These relationships confirm that PN_REC_AGR_LINCONST_ALL functions as a dependent child of recovery agreement line setup, referencing organization and index master data to fully qualify each recovery constraint definition.