# Oracle EBS Employee Table and HRMS Tables: Date-Tracked SQL Generator

> Source: https://netray.co/tools/oracle-ebs-hrms-employee-tables-and-date-tracked-sql
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates a current-employee query over PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, organizations, jobs and supervisors with the date-track filters done right.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- EBS developer
- HRMS analyst
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### As-of date expression

**Key:** as_of

**Type:** text

**Default:** TRUNC(SYSDATE)

**Help:** Use TRUNC(SYSDATE) for today, or TO_DATE('2026-03-31','YYYY-MM-DD') for a past date.

#### Business group ID

**Key:** bg_id

**Type:** number

**Default:** 202

**Help:** BUSINESS_GROUP_ID from HR_ALL_ORGANIZATION_UNITS. 202 is the Vision demo value.

#### Last name filter

**Key:** name_like

**Type:** text

**Default:** %

**Help:** Use % for everyone or SMITH% for a prefix.

#### Max rows

**Key:** limit_rows

**Type:** number

**Default:** 200

**Blocks:**

#### Current employees with assignment, organization, job and supervisor

**Body:** SELECT p.person_id,
       p.employee_number,
       p.full_name,
       p.email_address,
       a.assignment_id,
       a.assignment_number,
       o.name        AS organization,
       j.name        AS job,
       s.full_name   AS supervisor,
       p.effective_start_date AS person_from,
       a.effective_start_date AS assignment_from
FROM   per_all_people_f      p
       JOIN per_all_assignments_f a
         ON a.person_id = p.person_id
        AND a.assignment_type = 'E'
        AND a.primary_flag = 'Y'
        AND {{as_of}} BETWEEN a.effective_start_date AND a.effective_end_date
       LEFT JOIN hr_all_organization_units o
         ON o.organization_id = a.organization_id
       LEFT JOIN per_jobs_vl j
         ON j.job_id = a.job_id
       LEFT JOIN per_all_people_f s
         ON s.person_id = a.supervisor_id
        AND {{as_of}} BETWEEN s.effective_start_date AND s.effective_end_date
WHERE  p.business_group_id = {{bg_id}}
 AND   p.current_employee_flag = 'Y'
 AND   p.last_name LIKE UPPER('{{name_like}}')
 AND   {{as_of}} BETWEEN p.effective_start_date AND p.effective_end_date
ORDER BY p.full_name
FETCH FIRST {{limit_rows}} ROWS ONLY;

#### Full history of one person (all date-tracked rows)

**Body:** SELECT person_id, employee_number, last_name, first_name,
       effective_start_date, effective_end_date
FROM   per_all_people_f
WHERE  person_id = :person_id
ORDER BY effective_start_date;

#### Link an application user to the employee record

**Body:** SELECT u.user_name, u.employee_id AS person_id, p.full_name
FROM   fnd_user u
       JOIN per_all_people_f p
         ON p.person_id = u.employee_id
        AND {{as_of}} BETWEEN p.effective_start_date AND p.effective_end_date
WHERE  u.end_date IS NULL OR u.end_date > {{as_of}};

**Notes:** Verify names against your release in the eTRM. FETCH FIRST needs Oracle Database 12c or later; on 11g use ROWNUM. Last name values are stored in upper or mixed case depending on setup. Run against a non-production copy first and respect HR data access rules.

## Content

**Intro:** The Oracle EBS employee table is PER_ALL_PEOPLE_F, but a plain select returns every historic version of every person. This generator builds the join to assignments, organization, job and supervisor with the date-track filter applied to each table.

**Sections:**

#### How to use the generator

**Body:** Set the as-of date, business group and an optional last name filter, then copy the query into SQL Developer or TOAD. The as-of expression is reused in every date-tracked join, which is the part people most often forget. Leave it as TRUNC(SYSDATE) for current data, or enter a past date to see who worked where at that time. The second and third blocks show the full history of a person and the link between FND_USER and the employee.

#### The core HRMS tables

**Body:** People live in PER_ALL_PEOPLE_F (one row per person per date range, key PERSON_ID plus EFFECTIVE_START_DATE). Jobs and positions of work are in PER_ALL_ASSIGNMENTS_F, which holds ORGANIZATION_ID, JOB_ID, POSITION_ID, LOCATION_ID, GRADE_ID and SUPERVISOR_ID. Employment spans are in PER_PERIODS_OF_SERVICE. Reference tables include HR_ALL_ORGANIZATION_UNITS, PER_JOBS, HR_ALL_POSITIONS_F, HR_LOCATIONS_ALL, PER_ADDRESSES and PER_PHONES.

**Bullets:**

- PER_ALL_PEOPLE_F: person, name, employee number, email
- PER_ALL_ASSIGNMENTS_F: organization, job, position, supervisor
- PER_PERIODS_OF_SERVICE: hire and termination dates
- FND_USER.EMPLOYEE_ID: links a login to PERSON_ID

#### Date-track patterns that avoid duplicates

**Body:** A date-tracked table keeps one row for each period a version of the data was true. Any join to it needs a filter such as the as-of date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Without it, a person with five name or assignment changes appears five times. Also filter ASSIGNMENT_TYPE = 'E' and PRIMARY_FLAG = 'Y' to avoid contingent worker and secondary assignment rows, and use CURRENT_EMPLOYEE_FLAG for active employees.

#### Common mistakes

**Body:** Using the unsuffixed PER_PEOPLE_F or PER_ASSIGNMENTS_F views without knowing they depend on session date and business group. Joining supervisor with no date filter. Treating EMPLOYEE_NUMBER as unique across business groups. Reading personal data without the access controls your HR team requires. Counting rows from PER_ALL_PEOPLE_F as headcount, which includes ex-employees, applicants and contingent workers.

**Cta:** If HR and operations teams keep asking IT for the same employee and approval-chain reports, a private AI layer over EBS can answer them on your own servers while honoring existing access roles.

**Faq:**

#### What is the employee table in Oracle EBS?

**Answer:** PER_ALL_PEOPLE_F holds people, including employees, ex-employees, applicants and contingent workers. Employment details such as organization, job and supervisor are in PER_ALL_ASSIGNMENTS_F. Both are date-tracked, so every query needs an effective date filter.

#### Why do I get duplicate employees in my query?

**Answer:** Because date-tracked tables keep a row for every historic version. Add AS-OF BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE to each date-tracked table in the join, and restrict assignments to the primary employee assignment.

#### How do I link an EBS user to an employee?

**Answer:** FND_USER.EMPLOYEE_ID stores the PERSON_ID of the linked person. Join it to PER_ALL_PEOPLE_F with the date filter. Some users, such as service accounts, have no employee link, so use a left join if you need all users.

#### Does this work for contingent workers?

**Answer:** Not directly. Contingent workers use assignment type C and the current npw flag instead of current employee flag. Change the assignment type and flag in the where clause to report on them, subject to your HR data rules.

## Search terms

oracle ebs employee table, oracle ebs hrms tables, per_all_people_f, per_all_assignments_f, oracle apps employee query, oracle hrms date tracked tables, oracle ebs employee supervisor query, oracle ebs employee sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json