Search Results per_contracts_f_pk

Overview

PER_CONTRACTS_F is a date-tracked (datetrack) table in the HR schema of Oracle E-Business Suite, owned by the PER (Human Resources) product. It stores the details of a person's contract of employment — the contractual instrument that governs the terms, duration, status, and documentary lifecycle of an individual's engagement with the enterprise. Each row represents a version of a contract as of a particular effective period, which is why the physical name carries the "_F" suffix and the primary key includes effective dates. The table is central to workforce contract administration, contract lifecycle tracking, and any reporting requirement that must reconcile an assignment to the legal contract under which it is performed.

From a Data Vault modeling perspective, the FK structure mined from the metadata suggests a satellite-leaning classification. The table's identity is anchored by CONTRACT_ID (the parent/business key), while the effective-dated rows carry descriptive attributes that change over time. In Data Vault terms, CONTRACT_ID behaves as a hub key, and the effective-dated attribute columns behave as satellite payload. That is a heuristic suggestion, not a documented Oracle construct, and should be validated against the organization's actual integration and history requirements.

Key Information Stored

The surrogate and business-key structure is explicit in the metadata. PER_CONTRACTS_F_PK is composed of CONTRACT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, making the effective-dated triplet both the primary key and the unique business-key candidate. CONTRACT_ID is the stable identifier for the contract itself; the two date columns scope each version of the record.

• CONTRACT_ID — unique contract identifier; the stable anchor across all dated versions.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, establishing the enterprise/legislative context.
• PERSON_ID — the person to whom the contract applies.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack validity bounds; part of the PK.
• REFERENCE, TYPE, STATUS, STATUS_REASON — the contract's external reference, classification, current status, and justification for that status.
• DOC_STATUS, DOC_STATUS_CHANGE_DATE — documentary lifecycle of the physical contract record.
• DESCRIPTION, CONTRACTUAL_JOB_TITLE, PARTIES — narrative and role-defining attributes.
• DURATION, DURATION_UNITS — the contract term and its unit of measure.
• START_REASON, END_REASON — audit context for why the contract began or terminated.
• NUMBER_OF_EXTENSIONS, EXTENSION_REASON, EXTENSION_PERIOD, EXTENSION_PERIOD_UNITS — extension history and terms.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–20 — the standard Oracle descriptive flexfield.
• CTR_INFORMATION_CATEGORY and CTR_INFORMATION1–20 — contract-specific flexfield segments.
• OBJECT_VERSION_NUMBER — optimistic locking column.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard audit columns.

Common Use Cases and Queries

Typical usage includes retrieving the currently effective contract for a person (constraining the datetrack window to SYSDATE), reporting contract status distribution by business group, and auditing extension history. A representative pattern selecting the active contract row is:

SELECT c.contract_id, c.person_id, c.reference, c.type, c.status, c.duration, c.duration_units
FROM   per_contracts_f c
WHERE  c.person_id = :p_person_id
AND    SYSDATE BETWEEN c.effective_start_date AND c.effective_end_date;

Reporting queries frequently join to PER_ALL_PEOPLE_F on PERSON_ID and to HR_ALL_ORGANIZATION_UNITS on BUSINESS_GROUP_ID. Datetrack-aware tools should use the standard date-track API rather than direct DML, because inserts and updates must maintain the effective-date continuity enforced by the PK.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by PER_CONTRACTS_F.BUSINESS_GROUP_ID; the FK in the documented metadata.
• PER_ALL_PEOPLE_F — the person master; joined via PERSON_ID.
• PER_ALL_ASSIGNMENTS_F — the assignment record that carries the contract context; commonly joined via PERSON_ID.
• PER_CONTRACTS_F_PK — the primary key constraint enforcing the effective-dated uniqueness.
• PER_CONTRACT_EXTRA_INFO (and related flexfield/extra-info tables) — hold supplementary contract detail.
• PER_CONTRACTS_API / HR_CONTRACT_API — the public APIs used to create and maintain contract records while preserving datetrack integrity.
• PER_CONTRACT_V / PER_CONTRACTS — views projecting the dated rows into a current or business-key view.
• PER_PERSON_TYPES / PER_PERSON_TYPE_USAGES_F — person type context often configured alongside contract type.

Because the table is effective-dated, all related reporting and integration logic should respect both the CONTRACT_ID identity and the effective window to avoid returning superseded versions.