Results for “per_all_people_d”

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

Overview

PER_ALL_PEOPLE_D is an APPS-owned database view in the Oracle E-Business Suite Human Resources (PER) product, with a status of VALID in both 12.1.1 and 12.2.2. The view is documented in ETRM as being "Used by Datetrack History," which identifies its primary purpose: exposing the datetracked (date-effective) history of person records rather than only the current active row. In Oracle EBS, the PER_PEOPLE_F table stores person data in a date-effective format, where each person is represented by multiple rows spanning EFFECTIVE_START_DATE and EFFECTIVE_END_DATE ranges. The "_D" suffix in the view name conventionally indicates a "Dated" or datetrack history structure, in contrast to the "_F" (Full/current) base object. PER_ALL_PEOPLE_D therefore presents person rows across their full effective-dating timeline, making it suitable for history reporting, audit, and point-in-time analysis of employee and applicant data. Because it also resolves code values through HR_LOOKUPS and applies row-level security via HR_SECURITY, the view is a practical source for reporting integrations, BI Publisher datasets, and custom concurrent programs that need historical person attributes together with their decoded meanings.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects: FND_USER (synonym), HR_API (package), HR_GENERAL (package), HR_LOOKUPS (view), HR_PERSON_NAME (package), HR_SECURITY (package), PER_PEOPLE_F (view), PER_PERSON_TYPES (synonym), and PER_PERSON_TYPES_TL (synonym). At the core of the view is PER_PEOPLE_F, which supplies the person attributes and the effective start/end date range for each history row. PER_PERSON_TYPES and PER_PERSON_TYPES_TL provide the person type definition and its translated (language-specific) values, joined using USERENV('LANG') so that only the current session language is returned. HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE is invoked to resolve the user person type for each row based on the effective end date and person ID. The HR_LOOKUPS view is joined multiple times with outer joins to decode attributes such as marital status, nationality, sex, title, work schedule, student status, and others into their display meanings. FND_USER supplies the last-updated-by user name. HR_SECURITY, HR_API, HR_GENERAL, and HR_PERSON_NAME are referenced as supporting packages, with HR_SECURITY governing secure access to person records.

Key Columns

• PERSON_ID — Unique identifier for the person (employee or applicant).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date range defining each datetracked history row; central to historical and point-in-time reporting.
• LAST_NAME, FIRST_NAME, MIDDLE_NAMES, FULL_NAME, KNOWN_AS, PRE_NAME_ADJUNCT, SUFFIX, PREVIOUS_LAST_NAME — Name components and prior name values.
• EMPLOYEE_NUMBER — The assigned employee number.
• DATE_OF_BIRTH, NATIONAL_IDENTIFIER — Personal identification details.
• EMAIL_ADDRESS, OFFICE_NUMBER, INTERNAL_LOCATION, MAILSTOP — Contact and location attributes.
• HR6.MEANING — The decoded meaning of the WORK_SCHEDULE lookup (HR6 aliases HR_LOOKUPS on LOOKUP_TYPE = 'WORK_SCHEDULE' joined to PEOPLE.WORK_SCHEDULE). This is the column relevant to the user's search term.
• HR1–HR5, HR7–HR10.MEANING — Decoded meanings for home office, marital status, nationality, sex, title, student status, and related lookups.
• FTE_CAPACITY — Full-time equivalent capacity for the person.
• LAST_UPDATE_DATE, FUSER.USER_NAME — Audit information identifying when and by whom the record was last changed.

Common Use Cases and Queries

The view is typically used when historical, date-effective person data or decoded lookup values are required. A frequent requirement is reporting the work schedule assigned to a person over time; the view exposes the WORK_SCHEDULE lookup meaning directly through the HR6 alias. A representative query is:

SELECT person_id, first_name, last_name, employee_number, effective_start_date, effective_end_date, hr6.meaning work_schedule FROM apps.per_all_people_d WHERE person_id = :p_person_id ORDER BY effective_start_date;

Other practical scenarios include extracting a current snapshot (filtering where SYSDATE is between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE), building full history/audit extracts for a person, decoding demographic attributes for downstream analytics, and assembling HR datasets for integrations and BI Publisher reports. Because the view incorporates HR_SECURITY, results are restricted according to the querying user's security profile, so it should be queried from within an application session context rather than directly as a privileged schema for authorization-sensitive reporting.