Results for “record_end_date”

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

Overview

The HRFV_PERSON_ASSIGNMENT_HISTORY view is a business view template from which the flexfield view is generated within the Oracle E-Business Suite Human Resources (PER) module. It resides in the APPS schema and holds a VALID status. As a business view template, it serves as the underlying definition against which Oracle HRMS flexfield-enabled views are dynamically constructed, allowing reporting and integration layers to consume a consistent, date-effective representation of person and assignment data without directly querying the transactional assignment tables.

In Oracle EBS 12.1.1 and 12.2.2, this view presents a combined historical picture of a person's period of service aligned with their assignment records. It is primarily intended as a reporting and integration artifact, giving downstream consumers a denormalized projection of person attributes and assignment attributes together, each carrying their own effective date ranges. This makes it suited for HR extract processes, analytics, and interface programs that must reconcile workforce history across time.

Underlying Base Objects

The view is defined over a documented set of base objects, most of which appear as synonyms. The principal drivers are PER_ALL_PEOPLE_F, which supplies the person-level effective-dated attributes such as name, employee number, and date of birth, and PER_ASSIGNMENTS_F (exposed as a view), which supplies assignment-level effective-dated data. PER_PERIODS_OF_SERVICE provides the period-of-service context that bounds a person's employment history.

A wide array of supporting objects enrich the projection: HR_ALL_ORGANIZATION_UNITS_TL and HR_ALL_POSITIONS_F_TL contribute organization and position descriptions; PER_JOBS_TL, PER_GRADES_TL, and PER_PERSON_TYPES_TL provide translated lookups for job, grade, and person type; and PER_ASSIGNMENT_STATUS_TYPES and PER_ASSIGNMENT_STATUS_TYPES_TL supply assignment status information. Related descriptive data comes from PER_PHONES, PER_COLLECTIVE_AGREEMENTS, PER_PAY_BASES, PAY_ALL_PAYROLLS_F, PAY_PEOPLE_GROUPS, PER_RECRUITMENT_ACTIVITIES, and PER_ALL_VACANCIES. Location and territory data are sourced from HR_LOCATIONS_ALL_TL and FND_TERRITORIES_TL.

Logic and security are supplied by the database packages HR_BIS, HR_GENERAL, HR_SECURITY, and HRI_BPL_PERSON_TYPE. HR_BIS is used extensively for decode lookups and to locate information within BIS, while HR_SECURITY enforces row-level access consistent with the user's security profile.

Key Columns

The view exposes both person-level and assignment-level effective dates, which are central to its history semantics. PEO.EFFECTIVE_START_DATE and PEO.EFFECTIVE_END_DATE define the person period, while ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE define the assignment period. These are combined into RECORD_START_DATE (the greatest of the two start dates) and RECORD_END_DATE (the least of the two end dates) to represent the overlapping valid window for each row.

• BUSINESS_GROUP_NAME — the name of the business group derived from BGRT.NAME.
• PERSON_NAME, EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME, MIDDLE_NAMES, PREFERRED_NAME — core person identification attributes from PER_ALL_PEOPLE_F.
• GENDER, MARITAL_STATUS, NATIONALITY, REGISTERED_DISABLED, SECOND_PASSPORT — decoded values produced via HR_BIS.BIS_DECODE_LOOKUP.
• DATE_OF_BIRTH, NATIONAL_IDENTIFIER, APPLICANT_NUMBER, EMAIL_ADDRESS — personal and contact identifiers.
• MAIL_DESTINATION, OFFICE_NUMBER, INTERNAL_LOCATION, MAILSTOP — work location and mailing attributes.
• DATE_OF_BIRTH, RESUME_EXISTS, RESUME_LAST_UPDATED, PREFERRED_NAME — additional person attributes used in reporting.

Decode logic is applied selectively, so the view returns NULL for a decoded value when the underlying source column itself is NULL, preserving data completeness.

Common Use Cases and Queries

Typical uses include workforce history extracts, headcount and tenure analysis, and reconciliation between person and assignment records over time. Because the view is a flexfield template, it is frequently a starting point for a generated flexfield view rather than a direct source of descriptive flexfield data.

A simple query to list current and historical person-assignment records for a business group might read:

• SELECT business_group_name, employee_number, person_name, record_start_date, record_end_date FROM apps.hrfv_person_assignment_history WHERE record_start_date >= :start_date ORDER BY employee_number, record_start_date;
• SELECT employee_number, person_name, preferred_name, gender, nationality FROM apps.hrfv_person_assignment_history WHERE business_group_name = :bg AND record_end_date = (SELECT MAX(record_end_date) FROM apps.hrfv_person_assignment_history h2 WHERE h2.employee_number = hrfv_person_assignment_history.employee_number);

Queries should account for row-level security enforced through HR_SECURITY and for the effective-dated overlap logic embedded in RECORD_START_DATE and RECORD_END_DATE. As always, run against a consistent reporting schema or use bind variables to avoid unnecessary full scans of the underlying person and assignment tables.