Search Results per_person_type_usages_f_uk1

Overview

The HR.PER_PERSON_TYPE_USAGES_F table is the effective-dated intersection entity that records what type of person a given person is within the Oracle EBS Human Resources system — for example, Employee, Applicant, Contingent Worker, or any user-defined person type. It sits at the boundary between the person record and the person type definition, and it is the definitive source for determining a person's classification at a point in time. In Oracle EBS 12.1.1 and 12.2.2 this table carries date-tracked history, which is why the physical table name uses the _F suffix; the corresponding _F is a date-tracked entity whose logical view (PER_PERSON_TYPE_USAGES) exposes the currently effective rows.

A heuristic Data Vault classification mined from the foreign key and index structure suggests this object behaves as a link — an associative table anchored on two business entities (PERSON and PERSON_TYPE) with an effective-dating and audit payload. It is described as standalone in the vault heuristic, since the documented columns carry no further outbound foreign keys beyond PERSON_ID and PERSON_TYPE_ID.

Key Information Stored

The table contains 46 documented columns. The most significant are:

• PERSON_TYPE_USAGE_ID (NUMBER(15), mandatory) — system-generated primary key. It is the surrogate identifier and is also the driving column of the primary key index PER_PERSON_TYPE_USAGES_F_PK.
• PERSON_ID (NUMBER(15), mandatory) — foreign key to the person table; identifies the individual whose classification is being recorded.
• PERSON_TYPE_ID (NUMBER(15), mandatory) — foreign key to the person types table; identifies the classification being assigned.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE (DATE, mandatory) — the date-tracked range over which the classification is valid. These columns are part of the primary key and of every unique index.
• OBJECT_VERSION_NUMBER — optimistic locking counter, incremented on each update.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program and request context for the last change.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30 — the descriptive flexfield (DFF) segment columns, allowing customer-specific extensions of the person type usage record.

The surrogate key is PERSON_TYPE_USAGE_ID plus the two effective dates, as declared by PER_PERSON_TYPE_USAGES_F_PK. The principal business-key candidate is the unique index PER_PERSON_TYPE_USAGES_F_UK1 on (PERSON_ID, PERSON_TYPE_ID, EFFECTIVE_START_DATE) — this is the constraint that prevents a person from being assigned the same person type twice over an overlapping effective window, and it is the index most commonly referenced by name in Oracle EBS documentation and error messages. A secondary non-unique index, PER_PERSON_TYPE_USAGES_F_FK2, supports lookups by PERSON_TYPE_ID alone.

Common Use Cases and Queries

Reporting on workforce composition frequently joins this table to PER_ALL_PEOPLE_F and PER_PERSON_TYPES to produce headcount by classification:

SELECT pt.user_person_type, COUNT(*)
FROM   per_person_type_usages_f ptu,
       per_person_types pt
WHERE  ptu.person_type_id = pt.person_type_id
AND    TRUNC(SYSDATE) BETWEEN ptu.effective_start_date
                          AND ptu.effective_end_date
GROUP BY pt.user_person_type;

Effective dating makes "as of" reporting straightforward — filter by the target date rather than relying on the logical view. Payroll and benefits validation routines, and the person-type-specific DFF setup, all read this table to determine eligibility. A frequent troubleshooting scenario is a unique constraint violation on PER_PERSON_TYPE_USAGES_F_UK1, which indicates an attempt to create a duplicate person type assignment for the same person and start date; this is typically resolved by correcting the effective dates or by using the standard person management forms rather than direct DML.

Related Objects

• HR.PER_ALL_PEOPLE_F — joined on PERSON_ID; supplies the person identity and name.
• HR.PER_PERSON_TYPES — joined on PERSON_TYPE_ID; supplies the classification definition and system person type.
• HR.PER_PERSON_TYPE_USAGES — the effective-dated logical view over this table, commonly queried by reports and forms.
• PER_PERSON_TYPE_USAGES_F_PK / _UK1 / _FK2 — the primary, unique business-key, and foreign-key indexes described above.
• PER_PERSON_TYPE_USAGES_F_DF — the descriptive flexfield associated with the ATTRIBUTE columns.
• PER_PERSON_TYPE_USAGE_ID — the sequence/surrogate source for the primary key.