Results for “per_contracts_x”

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

Overview

APPS.PER_CONTRACTS_X is a date-effective (public) view owned by the APPS schema and registered under the PER — Human Resources product family. Its primary purpose is to expose the current, effective version of each contract record stored in the underlying contract table, resolving the multi-row, date-tracked structure of the base entity into a single row per contract that is valid for the current system date. The view is central to any reporting or integration that needs to read a person's contract without having to apply the effective-dating logic manually.

Because it is a date-effective view, the filter TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE is applied in the view definition. Only contract rows whose effective date range brackets the current date are returned. This makes PER_CONTRACTS_X a reliable source for point-in-time reporting of active contract data. In Oracle EBS 12.1.1 and 12.2.2, such views are the recommended read interface for date-tracked HR entities, since they encapsulate the standard effective-dating rules and avoid duplication of that logic in customer code.

Underlying Base Objects

The view is defined over two documented base objects:

• PER_CONTRACTS_F (referenced via the APPS synonym) — the date-tracked base table that physically stores contract rows, including the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns used for the date-effective filter, and the placeholder columns CTR_INFORMATION1 through CTR_INFORMATION20 and ATTRIBUTE1 through ATTRIBUTE20.
• HR_CONTRACT_API (package) — invoked in the view's SELECT list through several functions: HR_CONTRACT_API.GET_ACTIVE_START_DATE, HR_CONTRACT_API.GET_ACTIVE_END_DATE, HR_CONTRACT_API.GET_PPS_START_DATE, and HR_CONTRACT_API.GET_PPS_END_DATE. These derive the active and PPS date ranges dynamically at query time.

Because the view calls API functions, its columns ACTIVE_START_DATE, ACTIVE_END_DATE, PDS_START_DATE, and PDS_END_DATE are computed rather than stored. This is important for performance tuning: the API calls execute per row returned, so restricting rows before selecting these columns benefits query cost.

Key Columns

• CONTRACT_ID — primary key of the contract record.
• PERSON_ID — the person to whom the contract belongs.
• ACTIVE_START_DATE / ACTIVE_END_DATE — the active date range returned by the API for the contract's current status.
• PDS_START_DATE / PDS_END_DATE — the placement/period-of-service dates derived for the person.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating envelope used by the view filter.
• STATUS, STATUS_REASON, DOC_STATUS, DOC_STATUS_CHANGE_DATE — contract lifecycle and document tracking.
• REFERENCE, TYPE, DESCRIPTION, DURATION, DURATION_UNITS, CONTRACTUAL_JOB_TITLE, PARTIES — descriptive contract attributes.
• START_REASON, END_REASON, NUMBER_OF_EXTENSIONS, EXTENSION_REASON, EXTENSION_PERIOD, EXTENSION_PERIOD_UNITS — extension/renewal data.
• CTR_INFORMATION_CATEGORY and CTR_INFORMATION1 … CTR_INFORMATION20 — descriptive flexfield (DFF) context and segment columns. The column CTR_INFORMATION1 is the first contract DFF segment, populated per the enabled CTR_INFORMATION_CATEGORY.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 … ATTRIBUTE20 — additional DFF/attribute storage.
• OBJECT_VERSION_NUMBER and audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE).

Common Use Cases and Queries

The view is typically used to report current contracts for a person, to expose DFF values such as CTR_INFORMATION1, and to feed downstream integrations requiring effective-dated contract data.

• List active contracts for a person:

  SELECT contract_id, person_id, status, active_start_date, active_end_date
  FROM   apps.per_contracts_x
  WHERE  person_id = :p_person_id;

• Read the CTR_INFORMATION1 DFF segment for a contract:

  SELECT contract_id, ctr_information_category, ctr_information1
  FROM   apps.per_contracts_x
  WHERE  contract_id = :p_contract_id;

• Report contracts by category and status:

  SELECT ctr_information_category, status, COUNT(*)
  FROM   apps.per_contracts_x
  GROUP  BY ctr_information_category, status;

Since the view performs implicit date-effective filtering, queries against it always return the contract version effective as of the current date; historical versions must be read from PER_CONTRACTS_F directly.