Results for “pqh_fr_emp_stat_situations”

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

Overview

HR.PQH_FR_EMP_STAT_SITUATIONS is a transactional table within the Public Sector HR (PQH) product family of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores the statutory situations ("situations statutaires") assigned to individual civil servants in the French Public Sector. In French public-sector HR terminology, a statutory situation defines the administrative position of an employee — for example, secondment, detachment, parental leave, or availability — as recognized by the employing administration. Each row therefore captures one such situation for one person, bounded by provisional and actual date ranges and governed by approval and renewal logic.

The table is registered in the HR schema and carries the standard EBS audit and descriptive-flexfield infrastructure, evidenced by the mandatory WHO columns and thirty numbered ATTRIBUTE columns gated by ATTRIBUTE_CATEGORY. The ETRM metadata classifies this object heuristically as standalone in Data Vault terms. That classification is a modeling suggestion only: the table does not behave as a pure hub, link, or satellite because it mixes identifying keys, descriptive attributes, and a single outgoing foreign key within one physical structure. A Data Vault implementation would typically resolve it into a hub keyed on the natural person identifier, a link to the statutory situation reference, and one or more satellites for the dated and descriptive facts.

Key Information Stored

The surrogate primary key is EMP_STAT_SITUATION_ID, enforced by the unique index PQH_FR_EMP_STAT_SITUATIONS_PK. This column uniquely identifies each statutory-situation record and is the documented business-key candidate; no other unique index is reported.

The most significant columns fall into four groups:

• Identity and classification: STATUTORY_SITUATION_ID references the statutory situation definition, and PERSON_ID identifies the civil servant concerned.
• Effective dating: PROVISIONAL_START_DATE, PROVISIONAL_END_DATE, ACTUAL_START_DATE, and ACTUAL_END_DATE distinguish the planned window from the dates that took effect, which is essential for retroactive adjustments.
• Workflow and continuity: APPROVAL_FLAG indicates whether the situation has been approved; RENEWAL_FLAG and RENEW_STAT_SITUATION_ID link a renewal to the record it supersedes; SECONDED_CAREER_ID supports seconded-career tracking.
• Administrative context: CONTACT_PERSON_ID, CONTACT_RELATIONSHIP, EXTERNAL_ORGANIZATION_ID, and COMMENTS capture the counterparty and free-text administration details. Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER provide audit and optimistic locking.

Common Use Cases and Queries

Typical usage includes generating an employee's statutory history, validating that approved situations do not overlap, and reporting headcount by statutory situation for a given period. The following patterns are representative:

• Current situation for a person: filter on PERSON_ID where ACTUAL_START_DATE <= SYSDATE and (ACTUAL_END_DATE IS NULL OR ACTUAL_END_DATE >= SYSDATE).
• Join to the definition table on STATUTORY_SITUATION_ID to obtain the situation name and attributes for reporting.
• Pending-work queue: select rows where APPROVAL_FLAG is not approved, ordered by PROVISIONAL_START_DATE.
• Renewal chains: self-join on RENEW_STAT_SITUATION_ID = EMP_STAT_SITUATION_ID to trace successive periods.
• Period-based extracts: constrain on the intersection of ACTUAL_START_DATE/ACTUAL_END_DATE with the reporting window.

Because the layout is date-driven, date-range predicates should always be applied before joining to person or organization tables to limit the result set.

Related Objects

The documented foreign key relationship ties this table directly to the statutory situation definition. Additional related objects reflect standard EBS integration points:

• PQH_FR_STAT_SITUATIONS — referenced via STATUTORY_SITUATION_ID; holds the situation definitions.
• PER_ALL_PEOPLE_F — joined on PERSON_ID to resolve the civil servant's identity and effective-dated assignment context.
• PQH_FR_EMP_STAT_SITUATIONS (self) — joined via RENEW_STAT_SITUATION_ID for renewal lineage.
• PER_ALL_ASSIGNMENTS_F — correlated through PERSON_ID to align statutory situations with assignment periods.
• HR_ALL_ORGANIZATION_UNITS — referenced by EXTERNAL_ORGANIZATION_ID to identify the external administration.
• Public Sector HR concurrent programs and PL/SQL APIs that validate, approve, and renew statutory situations operate against this table.

Any custom reporting should respect the WHO audit columns and the flexfield structure, and should treat EMP_STAT_SITUATION_ID as the immutable technical key rather than relying on person-and-date combinations.