PER_PERSONS and PER_ALL_PEOPLE_F: What Each Row Represents comes down to one distinction: a person identity row is not the same thing as a date-effective version of a person record. Choose the wrong row grain, or skip the effective-date filter, and a query can count one person several times.
Key Takeaways
| Question | Answer |
|---|---|
| What does one PER_PERSONS row represent? | A person-level record, identified by PERSON_ID. |
| Why can PER_ALL_PEOPLE_F return several rows for one person? | It stores date-effective versions, each with an effective start and end date. |
| Which identifier should connect HCM records? | Use PERSON_ID as the relational key. Use PERSON_NUMBER as a readable identifier for output or search. |
| Where should a query retrieve a person’s name? | From PER_PERSON_NAMES_F, using that table’s own effective-date conditions for date-specific reporting. |
| How do you return the applicable person version? | Bind a report date and filter EFFECTIVE_START_DATE and EFFECTIVE_END_DATE around that date. |
| Does a person record prove that someone has an assignment? | No. Assignment and work relationship records represent employment context, with their own row grains. |
The row grain of each person table
Think in terms of row grain first. In PER_PERSONS, one row represents a person-level record, not a dated snapshot of that person’s record.
PER_ALL_PEOPLE_F is date-effective. One person can have multiple rows, with each row representing a version of the person record for a particular effective period. The same PERSON_ID can therefore appear across several rows without representing several people.
| Table | Example row | What the row represents |
|---|---|---|
| PER_PERSONS | PERSON_ID, PERSON_NUMBER | The person-level record for that identity. |
| PER_ALL_PEOPLE_F | PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE | A version of that person record effective during this date range. |
| PER_ALL_PEOPLE_F | PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE | A later version for the same person, effective from its start date. |
The dates in the example show the row pattern, not a claim that every person has exactly two effective-dated versions. The repeated PERSON_ID links both versions to the same person identity; the effective dates tell you which version applies during each period.
This infographic compares the row definitions for PER_PERSONS and PER_ALL_PEOPLE_F. Use it to see how the two tables represent people records.
Do not count rows in PER_ALL_PEOPLE_F as people until you have reduced the date-effective rows to the grain the report needs. If the report needs one row per person, first select the applicable version for the reporting date. If it needs a history, keep the versions and report them as versions.
Use PERSON_ID to connect records and PERSON_NUMBER to identify people
PERSON_ID is the relational key for connecting a person’s records across Oracle Fusion HCM. Use it to join PER_PERSONS to PER_ALL_PEOPLE_F and to related person, name, work relationship, and assignment records.
PERSON_NUMBER is designed to be readable in report output and useful as a search criterion. Show it to report consumers when it helps them recognize a person, but preserve PERSON_ID as the join key whenever the related records provide it.
Joining on PERSON_NUMBER when PERSON_ID is available makes the query follow a display identifier instead of the database relationship. That is an unnecessary source of ambiguity in joins, especially when a report combines person data with multiple date-effective tables.
When diagnosing repeats, select both identifiers alongside the effective dates:
SELECT person_id,
person_number,
effective_start_date,
effective_end_date
FROM per_all_people_f
WHERE person_number = :person_number
ORDER BY effective_start_date;
If PERSON_ID and PERSON_NUMBER stay the same while the effective-date boundaries differ, you are looking at versions for the same person. If PERSON_ID differs, you are looking at different person records, not merely another date-effective version of the same one.
Choose the table that owns the attribute you need
PER_PERSONS and PER_ALL_PEOPLE_F are person-level sources, but they serve different row requirements. Use PER_PERSONS when the report needs the person-level record; use PER_ALL_PEOPLE_F when it needs the person record as effective on a particular date or needs date-effective history.
A person’s name belongs in PER_PERSON_NAMES_F. Join it by PERSON_ID and apply the name table’s own effective-date conditions when the report asks for a name as of a specific date. A person can have more than one applicable name row under some report rules, so the query must also apply the required name-selection rule, such as the name type the report is designed to show.
Employment context belongs to work relationship and assignment records, not to the person identity row. A person row by itself does not establish that the person has an employee assignment.
Use PER_ALL_ASSIGNMENTS_F when the report needs assignment-level details. Its rows represent assignments, so one person can contribute multiple assignment rows; that is a different grain from one row per person or one row per effective-dated person version.
Filter date-effective rows for the report's as-of date
An as-of-date query should bind one reporting date and retain the person version whose effective interval contains it. The inclusive date test is:
effective_start_date <= :as_of_date
AND effective_end_date >= :as_of_date
Here is the basic join from a person-level record to the applicable PER_ALL_PEOPLE_F version:
SELECT p.person_id,
p.person_number,
papf.effective_start_date,
papf.effective_end_date
FROM per_persons p
JOIN per_all_people_f papf
ON papf.person_id = p.person_id
WHERE papf.effective_start_date <= :as_of_date
AND papf.effective_end_date >= :as_of_date;
PERSON_ID keeps the two sources connected to the same person. The date predicates select the version that applies on the requested date; they do not change the person’s identity.
Use the same reporting-date value when filtering PER_ALL_PEOPLE_F and other date-effective tables in a historical report, while using each table’s own effective-date columns. A name or assignment row has its own effective interval, and filtering only the person table does not automatically select the applicable name or assignment version.
For a current-state report, make the intended date explicit with a bound current date or a report parameter. This matters when future-dated records exist: the chosen date determines whether a scheduled future version is included or the report reflects the current point in time.
Join the tables without changing the intended output grain
Start with the row the report must return. A one-row-per-person report, a person-version history report, and an assignment report need different starting grains; one query shape cannot safely stand in for all three.
For a person-level result that needs the applicable date-effective person version, join PER_PERSONS to PER_ALL_PEOPLE_F by PERSON_ID and constrain the effective-dated rows before adding one-to-many records. Then inspect each additional relationship. Names, work relationships, and assignments can contribute multiple matching rows unless the query applies the right date and selection rules.
For a one-row-per-person output, check the final result instead of applying DISTINCT and hoping the duplicates disappear:
SELECT person_id,
COUNT(*) AS rows_per_person
FROM (
/* Place the complete report query here */
) q
GROUP BY person_id
HAVING COUNT(*) > 1;
Any returned PERSON_ID needs investigation. A one-to-many join may be multiplying the rows, or an effective-date or selection rule may be missing. If the report intentionally includes assignments, retain assignment identifiers or aggregate the assignment rows using a defined rule.
Diagnose repeated rows before treating them as duplicates
Repeated PERSON_ID values in PER_ALL_PEOPLE_F are not automatically duplicate data. Inspect PERSON_ID, PERSON_NUMBER, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE together; distinct date ranges can show separate effective-dated versions of one person record.
Then compare row counts before and after each join. A sudden increase identifies the join that changed the output grain. Check its join condition, its date predicates, and whether the related table is one-to-many for the report’s chosen date.
If assignment details are required, keep assignment identifiers visible while debugging. That makes it clear whether two rows represent separate assignments, separate person versions, or a join issue. If the final report must show one person row, aggregate assignment data deliberately according to the report’s business rule.
Do not use DISTINCT as the fix. DISTINCT can hide repeated-looking output while leaving an incorrect join or undefined row grain in place. This is not a bug. It's the architecture: person versions and related employment records have different grains, and the query has to account for them.
Frequently Asked Questions
Can a person have a row in PER_PERSONS without having an employee assignment?
Yes. Person identity and employee assignment are separate records, so a person can exist in PER_PERSONS without an employee assignment. Person records may support other HCM processes, including maintaining person information outside an employee assignment.
Can a report use a future as-of date to include future-dated person records?
Yes—set the report parameter to the future date whose person state you want to inspect.
What does a far-future EFFECTIVE_END_DATE signify in an effective-dated HCM table?
It commonly marks an open-ended effective interval, meaning the row has no later end date currently represented in that interval. It is an effective-date boundary convention, not a prediction that the person’s record will remain unchanged until that calendar date.
Should an as-of-date report use the report date or the database session date?
Use the date that defines the report’s intended point in time, and pass that date as a report parameter when reproducible output matters. A session date can vary between executions, so it may produce a different result from a report explicitly run for a historical or future date.
How should a report show assignment details but keep one row per person?
Define which assignment information belongs in the person-level output, then aggregate or select assignment rows according to that rule before joining them to the person result. For example, a report might summarize assignment counts or retain a designated assignment, but the rule must be explicit rather than an accidental consequence of the join.
Conclusion
PER_PERSONS represents person-level identity, while PER_ALL_PEOPLE_F represents date-effective versions of a person record. PERSON_ID connects those records, and EFFECTIVE_START_DATE plus EFFECTIVE_END_DATE determine which version applies on the reporting date.
For PER_PERSONS and PER_ALL_PEOPLE_F: What Each Row Represents, the practical rule is simple: decide the output grain first, bind the as-of date, and check each join for row multiplication. That keeps person counts, historical versions, and assignment details distinct in Oracle Fusion HCM reports.