Results for “per_all_assignments_f”

50+ results




Overview
Explore in AI Chat

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

Overview

PER_ALL_ASSIGNMENTS_F is the core Human Resources table in Oracle E-Business Suite Release 12.1.1 and 12.2.2 that stores the assignment records for employees, applicants, and contingent workers. An assignment is the operational link between a person and the organization, job, position, grade, payroll, and location at which they work. Where PER_ALL_PEOPLE_F holds the person record, PER_ALL_ASSIGNMENTS_F holds the employment relationship itself — the assignment number, the effective-dated placement details, and the terms and conditions attached to the engagement. The table is owned by the HR schema and is a date-tracked (datetracked) table, meaning every row carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE and multiple versions of the same assignment may coexist across time.

The primary key PER_ASSIGNMENTS_F_PK is composed of ASSIGNMENT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, so the surrogate assignment identifier is only unique when combined with the effective date range. Based on the foreign key structure mined by ETRM, this table is best modelled heuristically as a Data Vault link: it connects a person to the many reference hubs (organization, job, position, grade, location, payroll) and carries descriptive attributes such as normal hours, probation details, and notice periods. The documented physical schema contains 116 columns.

Key Information Stored

The most significant columns fall into three groups. The identity group comprises ASSIGNMENT_ID (surrogate primary key component), ASSIGNMENT_NUMBER, PERSON_ID, and PERIOD_OF_SERVICE_ID, which ties the assignment back to the continuous period of service for the person. The organizational placement group includes ORGANIZATION_ID, BUSINESS_GROUP_ID, SOURCE_ORGANIZATION_ID, JOB_ID, POSITION_ID, GRADE_ID, LOCATION_ID, PEOPLE_GROUP_ID, PAYROLL_ID, PAY_BASIS_ID, and ESTABLISHMENT_ID — each one a foreign key to the respective HR or Payroll lookup table. The terms and conditions group includes ASSIGNMENT_TYPE, ASSIGNMENT_CATEGORY, ASSIGNMENT_SEQUENCE, PRIMARY_FLAG, MANAGER_FLAG, ASSIGNMENT_STATUS_TYPE_ID, NORMAL_HOURS, FREQUENCY, PROBATION_PERIOD, PROBATION_UNIT, DATE_PROBATION_END, NOTICE_PERIOD, NOTICE_PERIOD_UOM, TIME_NORMAL_START, TIME_NORMAL_FINISH, and WORK_AT_HOME. The effective-dating columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are part of the unique business-key candidate PER_ASSIGNMENTS_F_PK. The descriptor columns ASS_ATTRIBUTE1 through ASS_ATTRIBUTE30, ASS_ATTRIBUTE_CATEGORY, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and OBJECT_VERSION_NUMBER support the standard EBS WHO columns and DFF extension mechanism.

Common Use Cases and Queries

Typical reporting scenarios include headcount and turnover analysis, supervisory hierarchy reporting, position-vacancy reconciliation, and payroll costing. Because the table is date tracked, queries must constrain effective dates to return the row in effect on a given date. A standard pattern for current assignments is:

  • SELECT paaf.assignment_id, paaf.assignment_number, paaf.person_id, paaf.organization_id, paaf.job_id, paaf.position_id, paaf.grade_id, paaf.supervisor_id FROM per_all_assignments_f paaf WHERE SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
  • Joining PER_ALL_PEOPLE_F on PERSON_ID for the person's name and employee number.
  • Joining PER_JOBS, PER_ALL_POSITIONS, PER_GRADES, and HR_ALL_ORGANIZATION_UNITS to resolve the placement reference codes.
  • Filtering PRIMARY_FLAG = 'Y' and current ASSIGNMENT_STATUS_TYPE_ID for active primary assignments.
  • Joining PER_PERIODS_OF_SERVICE on PERIOD_OF_SERVICE_ID to obtain hire date and service continuity.

Related Objects

The following are the most significant tables related through documented foreign keys:

  • PER_PERIODS_OF_SERVICE — joined via PERIOD_OF_SERVICE_ID; defines the continuous service record.
  • HR_ALL_ORGANIZATION_UNITS — joined via BUSINESS_GROUP_ID, ORGANIZATION_ID, SOURCE_ORGANIZATION_ID, and ESTABLISHMENT_ID.
  • PER_JOBS — joined via JOB_ID; supplies the job definition.
  • PER_ALL_POSITIONS — joined via POSITION_ID.
  • PER_GRADES — joined via GRADE_ID; with PER_CAGR_GRADES_DEF via CAGR_GRADE_DEF_ID.
  • HR_LOCATIONS_ALL — joined via LOCATION_ID.
  • PER_ASSIGNMENT_STATUS_TYPES — joined via ASSIGNMENT_STATUS_TYPE_ID for status validation.
  • PAY_PEOPLE_GROUPS — joined via PEOPLE_GROUP_ID; and PER_PAY_BASES via PAY_BASIS_ID for payroll processing.
  • PER_ALL_PEOPLE_F — related through PERSON_ID (application-level relationship) for person attributes.

The table is the primary source object for the Assignment business component in the HRMS API layer, and it is heavily referenced by the Oracle Payroll, Oracle Time and Labor, and Oracle Advanced Benefits modules, all of which read assignment rows to determine eligibility and costing.