Search Results per_contracts




Overview
Explore in AI Chat

Overview

PER_CONTRACTS is a date-effective view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PER (Human Resources) product module. It presents contract records for persons and assignments in a form that resolves the effective-dating logic stored in the underlying contract tables, so that consumers see one logical contract row with derived, context-sensitive attributes. Rather than exposing the raw dated rows held in the base table, the view wraps several calls to the HR_CONTRACT_API package, allowing start date, end date, status meaning, and other derived values to be returned according to a supplied effective date and the record's own status.

The view is documented as VALID in ETRM 12.2.2 and remains functionally the same across 12.1.1 and 12.2.2. Its principal role is to serve as the reporting and integration surface for contract information used by HR, payroll-related analysis, and downstream customizations. Because it is a view rather than a table, it incurs no separate storage and reflects the current state of the underlying contract data and API logic at query time.

Underlying Base Objects

The documented referenced base objects for PER_CONTRACTS are FND_SESSIONS (exposed as a synonym), the HR_CONTRACT_API package, and PER_CONTRACTS_F (also a synonym). PER_CONTRACTS_F is the effective-dated base table holding the physical contract rows, including the surrogate key CONTRACT_ID, the effective start and end dates, the person reference, and the various descriptive and coded columns. The view selects from this table, aliased internally as PCF1, and joins against FND_SESSIONS (FS) to obtain the session effective date used by the API calls.

HR_CONTRACT_API is a PL/SQL package invoked repeatedly within the view's SQL text. It supplies derived values such as GET_ACTIVE_START_DATE, GET_ACTIVE_END_DATE, GET_PPS_START_DATE and GET_PPS_END_DATE, GET_MEANING for translating lookup codes to display meanings, and related helpers. This design means the view is not a simple projection but a data-dependent construct whose output can vary with the session effective date and the record status.

Key Columns

The view exposes identifying attributes including ROWID, CONTRACT_ID, PERSON_ID, BUSINESS_GROUP_ID, and REFERENCE, along with OBJECT_VERSION_NUMBER for optimistic locking in integrated applications. Effective dating is represented by the derived ACTIVE_START_DATE and ACTIVE_END_DATE columns alongside the raw EFFECTIVE_START_DATE and EFFECTIVE_END_DATE from PER_CONTRACTS_F. Person-position-seniority style dates are supplied via GET_PPS_START_DATE and GET_PPS_END_DATE.

Coded columns carry both the stored value and an API-resolved meaning: TYPE with CONTRACT_TYPE meaning, STATUS with CONTRACT_STATUS, STATUS_REASON with CONTRACT_STATUS_REASON, DOC_STATUS with DOCUMENT_STATUS, START_REASON with CONTRACT_START_REASON, END_REASON with CONTRACT_END_REASON, and DURATION_UNITS with a QUALIFYING_UNITS meaning. The user search term duration_units maps directly to the view column DURATION_UNITS, the coded period qualifier applied to the DURATION value. Extension tracking is provided through NUMBER_OF_EXTENSIONS, EXTENSION_REASON, EXTENSION_PERIOD, and EXTENSION_PERIOD_UNITS, the latter also translated through QUALIFYING_UNITS. The view additionally carries ten-plus descriptive flexfield columns prefixed CTR_INFORMATION, retained for contract-specific attribute capture.

Common Use Cases and Queries

Typical uses include contract status reporting, duration analysis, and integration extracts that must respect effective dating. The following query returns active contract attributes for a person, using the derived dates and the QUALIFYING_UNITS meaning for duration:

  • SELECT contract_id, person_id, active_start_date, active_end_date, duration, duration_units, HR_CONTRACT_API.GET_MEANING(duration_units, 'QUALIFYING_UNITS') duration_meaning FROM per_contracts WHERE person_id = :person_id;
  • Counting contracts by status meaning: SELECT HR_CONTRACT_API.GET_MEANING(status, 'CONTRACT_STATUS') status_meaning, COUNT(*) FROM per_contracts GROUP BY HR_CONTRACT_API.GET_MEANING(status, 'CONTRACT_STATUS');
  • Filtering on the coded duration unit: SELECT contract_id, duration, duration_units FROM per_contracts WHERE duration_units = :unit_code AND effective_end_date = HR_CONTRACT_API.GET_MAX_EFFECTIVE_END_DATE;

Because the view invokes HR_CONTRACT_API per row, query performance depends on effective-date maintenance and indexing of PER_CONTRACTS_F. For large extracts, restricting by PERSON_ID or BUSINESS_GROUP_ID and avoiding unnecessary meaning lookups improves response time.