Search Results work_pattern

Overview

PQP_ASSIGNMENT_ATTRIBUTES_D is an APPS-owned DateTrack history view within the Oracle E-Business Suite Public Sector Payroll (PQP) product, specifically the UK local government and teaching payroll localization. The view exposes the full DateTrack change history of the table PQP_ASSIGNMENT_ATTRIBUTES_F, which stores assignment-level attributes such as contract type, work pattern, safeguarded pay protections, pension election flags, and local government pension scheme (LGPS) processing indicators. Because _F tables in Oracle HRMS hold only the currently effective dated row plus future-dated rows within the DateTrack model, the _D (dated history) view is the mechanism by which prior, superseded versions of those rows remain queryable. In Oracle EBS 12.1.1 and 12.2.2 the view is status VALID and is partitioned by the same effective start and end date columns that drive DateTrack, allowing point-in-time reporting, audit reconstruction, and integration extracts that must reflect an assignment's attributes as they stood on a chosen date rather than as they stand today. The view is read-only and is not a substitute for the base table in transactional processing; it is intended for reporting and enquiry.

Underlying Base Objects

The view is defined with an outer join pattern across a set of base objects documented in ETRM 12.2.2 metadata: PQP_ASSIGNMENT_ATTRIBUTES_F (SYNONYM), PQP_VEHICLE_DETAILS (SYNONYM), PAY_USER_TABLES (SYNONYM), PAY_RATES (SYNONYM), PER_SPINAL_POINTS (SYNONYM), FND_USER_VIEW (VIEW), HR_LOOKUPS (VIEW), and the HR_API package. PQP_ASSIGNMENT_ATTRIBUTES_F supplies the driving rows and all DateTrack-effective columns. Four correlated outer joins to PAY_USER_TABLES resolve the company car, secondary company car, private car rates, and private car essential user lookup tables referenced by ID columns on the base row. Three outer joins to PQP_VEHICLE_DETAILS resolve the primary, secondary, and private car vehicle records. HR_LOOKUPS is joined with LOOKUP_TYPE fixed to 'PQP_GB_TEACHER_JOB_STATUS' to decode the TP_IS_TEACHER flag. FND_USER_VIEW resolves the LAST_UPDATED_BY user identifier to a username, and PAY_RATES and PER_SPINAL_POINTS resolve the safeguarded rate and safeguarded spinal point IDs to descriptive values. All joins are outer (+) so that rows lacking a related lookup or rate are still returned.

Key Columns

• ASSIGNMENT_ATTRIBUTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — primary key and DateTrack effective dating; the basis for point-in-time queries.
• CONTRACT_TYPE, WORK_PATTERN, START_DAY — core assignment working-arrangement attributes.
• TP_JOB_STATUS — teacher job status meaning derived from HR_LOOKUPS via the PQP_GB_TEACHER_JOB_STATUS lookup type.
• TP_SAFEGUARDED_GRADE, TP_SAFEGUARDED_RATE_TYPE, TP_SAFEGUARDED_RATE_NAME, TP_SAFEGUARDED_SPINAL_POINT — safeguarded pay protection details, with rate and spinal point names resolved through PAY_RATES and PER_SPINAL_POINTS.
• TP_ELECTED_PENSION, TP_FAST_TRACK, TP_HEADTEACHER_GRP_CODE — teachers' pension election and headteacher group indicators.
• LGPS_PROCESS_FLAG, LGPS_EXCLUSION_TYPE, LGPS_PENSIONABLE_PAY, LGPS_TRANS_ARRANG_FLAG, LGPS_MEMBERSHIP_NUMBER — local government pension scheme processing and membership attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns; the latter resolved to USER_NAME through FND_USER_VIEW.

Common Use Cases and Queries

The view is typically used to reconstruct the state of an assignment's pay and pension attributes as of a past date, to audit changes to safeguarded pay or LGPS flags, and to feed downstream extracts that must not double-count DateTrack history. A representative query returning the effective-dated attributes for a single assignment is:

SELECT assignment_attribute_id, effective_start_date, effective_end_date, contract_type, work_pattern, tp_safeguarded_grade, tp_safeguarded_rate_name, lgps_process_flag, last_updated_by, last_update_date FROM apps.pqp_assignment_attributes_d WHERE assignment_attribute_id = :p_assignment_attribute_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the view returns every dated version of each attribute row, reports that require only current values must filter on the effective date range, while history or audit reports deliberately omit that predicate. Joining this view to PER_ALL_ASSIGNMENTS_F on the assignment identifier allows assignment-attribute history to be combined with assignment history for full point-in-time reconstruction.