Search Results pqp_assignment_attributes_pk

Overview

PQP_ASSIGNMENT_ATTRIBUTES_F is the DateTracked table within the Oracle HR schema that stores public sector payroll attributes at the assignment level. It is owned by the HR schema and belongs to the PQP (Public Sector Payroll) product family, which supports statutory payroll requirements for public sector employers such as local authorities, education authorities, and government departments. The table captures information that has no natural home in the core HR assignment model but is essential to public sector payroll processing, including contract type and detailed work pattern definitions.

As a DateTracked ( _F ) table, PQP_ASSIGNMENT_ATTRIBUTES_F follows the Oracle HRMS effective-dating pattern: each row carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns, and multiple dated versions of the same logical record can coexist. The companion table PQP_ASSIGNMENT_ATTRIBUTES holds non-dated information, a standard split in the HRMS datetrack architecture. Based on the FK relationship data, the object is classified heuristically as a standalone satellite: it depends on the parent assignment entity but is not a pure junction or hub, and it should be modeled as a satellite hanging off the assignment business key.

Key Information Stored

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

• ASSIGNMENT_ATTRIBUTE_ID — the surrogate primary key column, populated from a sequence. Together with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE it forms the unique index PQP_ASSIGNMENT_ATTRIBUTES_F_PK (also documented as PQP_ASSIGNMENT_ATTRIBUTES_PK).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack effective dating pair. These are part of the primary key because, unlike a conventional surrogate key, the datetrack pattern requires the date range to disambiguate versions.
• ASSIGNMENT_ID — the foreign reference to the assignment (PER_ALL_ASSIGNMENTS_F) this attribute row describes. This is the true business key candidate at the parent level.
• BUSINESS_GROUP_ID — the HR business group that owns the row, used for security and multi-organization filtering.
• CONTRACT_TYPE — the public sector contract classification (e.g., permanent, fixed-term, casual) used to drive payroll rules.
• WORK_PATTERN and START_DAY — definition of the employee's work pattern and the reference start day, used for pro-rating and entitlement calculations.
• COMPANY_CAR / PRIVATE_CAR attributes — a family of columns (PRIMARY_COMPANY_CAR, COMPANY_CAR_CALC_METHOD, COMPANY_CAR_RATES_TABLE_ID, PRIVATE_CAR, PRIVATE_CAR_RATES_TABLE_ID, and related benefit-in-kind flags) supporting UK P11D and company car benefit processing.
• TP_IS_TEACHER, TP_SAFEGUARDED_GRADE, TP_ELECTED_PENSION, TP_FAST_TRACK, TP_HEADTEACHER_GRP_CODE — teachers' pension scheme attributes.
• LGPS_* columns (LGPS_PROCESS_FLAG, LGPS_EXCLUSION_TYPE, LGPS_PENSIONABLE_PAY, LGPS_MEMBERSHIP_NUMBER) — Local Government Pension Scheme processing attributes.
• AAT_* columns — twenty descriptive flexfield attribute columns plus a context column (AAT_ATTRIBUTE_CATEGORY), enabling customer-specific extensions.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER providing audit and optimistic locking.

Common Use Cases and Queries

Typical reporting scenarios include extracting contract type on the current effective date, listing assignments with company car benefits for P11D reporting, and validating teachers' pension flags for the annual return. A standard current-row query uses sysdate between the effective dates:

• SELECT paaf.assignment_id, paaf.contract_type, paaf.work_pattern FROM hr.pqp_assignment_attributes_f paaf WHERE paaf.assignment_id = :p_assignment_id AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
• Joining to PER_ALL_ASSIGNMENTS_F on ASSIGNMENT_ID to combine public sector attributes with standard assignment and person data.
• Filtering by BUSINESS_GROUP_ID for multi-business-group environments.
• Extracting AAT_ATTRIBUTE1 through AAT_ATTRIBUTE20 for statutory custom reporting where configured.

All datetrack-aware queries must apply the sysdate predicate or the equivalent DateTrack API (HR_API or the datetrack views) to avoid returning historical versions.

Related Objects

• PER_ALL_ASSIGNMENTS_F — the parent DateTracked assignment table; join on ASSIGNMENT_ID.
• PQP_ASSIGNMENT_ATTRIBUTES — the companion non-dated table holding static attribute rows for the same assignment.
• PER_ALL_PEOPLE_F — the person record reached through the assignment.
• HR_ALL_ORGANIZATION_UNITS / HR_ORGANIZATION_UNITS_F — the parent business group referenced via BUSINESS_GROUP_ID.
• PAY_ELEMENT_TYPES_F and payroll element entries — consume contract type and pension attributes to drive payroll calculations.
• LGPS and Teachers' Pension statutory interfaces — read LGPS_* and TP_* columns during pension return generation.
• FF (Flexfield) API and AAT_* columns — the descriptive flexfield definition tied to AAT_ATTRIBUTE_CATEGORY.