Results for “previous_employer_id”

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

Overview

The PQH_FR_VALIDATION_PERIODS table resides in the HR schema and belongs to the PQH - Public Sector HR product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the period-level detail rows associated with a single validation record for an individual. Where the parent validation captures the overall outcome and identity of a validation event, this table decomposes that event into discrete, dated periods — each with its own duration attributes, frequency, and status. This structure supports French public-sector statutory validation logic, in which an individual's prior service, employment history, or eligibility must be evaluated across multiple calendar spans that may originate from different previous employers.

In Data Vault modeling terms, the mined foreign-key structure suggests a satellite-leaning classification. The table carries a single-column surrogate primary key and descriptive, time-bound attributes, which is characteristic of a satellite attached to a parent hub or link. It should not be treated as an independent hub; its rows derive their business meaning entirely from the validation to which they belong.

Key Information Stored

The table comprises 19 documented columns. The most significant are:

• VALIDATION_PERIOD_ID — surrogate primary key, enforced by unique index PQH_FR_VALIDATION_PERIODS_PK. It uniquely identifies each period row and is the only documented business-key candidate; no alternate unique index exists on the natural attributes.
• VALIDATION_ID — foreign key to PQH_FR_VALIDATIONS, linking the period to its owning validation event.
• PREVIOUS_EMPLOYER_ID — foreign key to PER_PREVIOUS_EMPLOYERS, identifying the prior employer whose service the period represents.
• START_DATE and END_DATE — the effective boundaries of the validation period.
• NORMAL_HOURS and FREQUENCY — the workload and recurrence basis used in validation calculations.
• PERIOD_YEARS, PERIOD_MONTHS, and PERIOD_DAYS — the decomposed duration of the period.
• VALIDATION_STATUS — the processing state of the individual period.
• ASSIGNMENT_CATEGORY — the category under which the period is classified for validation purposes.
• COMMENTS — free-text annotations recorded by the validating user.
• OBJECT_VERSION_NUMBER — optimistic locking column for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical reporting requirements include retrieving all periods for a validation, aggregating total validated service per individual, and reconciling period durations against their start and end dates.

Retrieve all periods for a given validation:

SELECT validation_period_id, start_date, end_date,
       normal_hours, frequency, validation_status
FROM   hr.pqh_fr_validation_periods
WHERE  validation_id = :p_validation_id
ORDER BY start_date;

Join to the parent validation and to the previous employer for a consolidated report:

SELECT v.validation_id, p.start_date, p.end_date,
       e.employer_name, p.period_years, p.period_months
FROM   hr.pqh_fr_validation_periods p,
       hr.pqh_fr_validations      v,
       hr.per_previous_employers  e
WHERE  p.validation_id = v.validation_id
AND    p.previous_employer_id = e.previous_employer_id;

Aggregate validated duration per validation:

SELECT validation_id,
       SUM(period_years)  total_years,
       SUM(period_months) total_months,
       SUM(period_days)   total_days
FROM   hr.pqh_fr_validation_periods
GROUP BY validation_id;

These queries support audit trails, statutory reporting, and data correction exercises. Because the table holds no natural unique key, consumers should always constrain by VALIDATION_ID rather than by date ranges alone.

Related Objects

• PQH_FR_VALIDATIONS — parent table; joined on VALIDATION_ID.
• PER_PREVIOUS_EMPLOYERS — joined on PREVIOUS_EMPLOYER_ID to resolve prior-employer detail.
• PER_ALL_PEOPLE_F — resolves the individual associated with the parent validation.
• PQH_FR_VALIDATION_PERIODS_PK — the unique index enforcing the surrogate key.
• PQH_FR_VALIDATIONS_PK — primary key of the parent, used to validate referential integrity during loads.

No public PL/SQL API is documented specifically for this table; it is accessed predominantly through the Public Sector HR validation forms and through direct SQL for reporting and data migration.