PER_PERSONS and PER_ALL_PEOPLE_F: Where Person Data Diverges

November 9, 2026 19 min read

Oracle HCM Tables

PER_PERSONS and PER_ALL_PEOPLE_F can both appear in an Oracle Fusion HCM person query, but they represent different date-tracking behavior and row grain. Choose the source according to the attribute and reporting date you need, or the same PERSON_ID can lead to misleading historical results and duplicate rows.

Key Takeaways

PER_PERSONS is the non-date-tracked person source; PER_ALL_PEOPLE_F represents person data in effective-dated rows.
Use PERSON_ID to relate person records. Treat PERSON_NUMBER as a business-facing identifier, not a substitute join key.
For a point-in-time report, filter every effective-dated table to the same deliberate as-of date.
Check the meaning and row grain of each attribute before choosing a source. Columns with similar names can have different storage or history behavior.
A person can match multiple name, assignment, or person-type rows. Define the intended output grain before joining.
Use DISTINCT only when it is part of a deliberate result rule, not to cover up an unexplained one-to-many join.

How PER_PERSONS and PER_ALL_PEOPLE_F differ

Treat PER_PERSONS as the non-date-tracked person source and PER_ALL_PEOPLE_F as the date-effective person source. They are not interchangeable versions of the same table: one represents person-level identity data, while the other can represent that person across effective-date intervals.

PERSON_ID is the key to relating the person across these tables. The same person can have a single person-level identity row and multiple effective-dated rows, so a join on PERSON_ID alone does not guarantee one output row per person.

Attributes exposed in both places can have different storage or history behavior. Decide which table supplies the requested attribute for the report’s as-of date, then verify its column definition and effective-dating semantics.

When you need to inspect Oracle HCM table and column definitions, Oracle Cloud Tables provides a schema search interface across Oracle Fusion Cloud modules. Use that lookup to check the actual column definitions before committing to a source.

Source	Date behavior	Use it for
`PER_PERSONS`	Non-date-tracked	Person-level identity attributes whose meaning does not depend on an effective date
`PER_ALL_PEOPLE_F`	Date-effective	Person attributes evaluated for a reporting date, including the applicable effective interval

Find the right source for each person attribute

Start with the field the report needs, not the table that looks familiar. Use PERSON_ID as the person-level join key; use PERSON_NUMBER as a business-facing identifier for display or reporting, not as a replacement for the key.

For a point-in-time person report, retrieve the effective-dated attribute and its EFFECTIVE_START_DATE and EFFECTIVE_END_DATE from PER_ALL_PEOPLE_F. Those dates show which interval the selected row represents.

For a non-date-tracked identity attribute, use PER_PERSONS. For PERSON_NUMBER or BUSINESS_GROUP_ID, compare the exposed column definitions and choose the source whose semantics fit the report; similarly named columns can have different meanings.

Requested field or data	Source decision	What to verify
`PERSON_ID`	Use it to relate person-level records.	Confirm the join preserves the intended row grain.
`PERSON_NUMBER`	Choose the exposed definition that fits its business use.	Check its meaning and scope; do not use it as the person join key.
`BUSINESS_GROUP_ID`	Choose the source whose definition matches the report.	Compare the exposed column definitions; they can describe different behavior.
Person attributes as of a date	`PER_ALL_PEOPLE_F`	Use its effective dates to identify the row for the requested interval.
Non-date-tracked identity attributes	`PER_PERSONS`	Confirm the requested attribute is defined there and does not require historical values.

If you cannot identify the right source from the attribute name, inspect the table and column definitions before building the report. The HCM data model separates person, name, assignment, and person-type data, so a familiar-sounding field can still belong to a different grain or history pattern.

Join person rows to names, assignments, and person types

Relate PER_PERSONS to PER_ALL_PEOPLE_F with PERSON_ID, then constrain the date-effective table when the report should show the person at one point in time. Apply a separate effective-date condition to PER_PERSON_NAMES_F; name rows have their own grain and can produce more than one match for a person.

Join PER_ALL_ASSIGNMENTS_F only when the report needs assignment data. A person can have multiple assignments, so apply assignment-level conditions separately and decide whether the output should contain one row per person or one row per person-assignment.

Use PER_PERSON_TYPE_USAGES_F when the report needs person-type usage. Person-type usage and assignment records are related data, not one-to-one person attributes.

When the join columns are unclear, the Join Path Finder is a tool for finding join columns between two table names. It suits this step because it helps identify the relationship to validate before you add more tables to the query.

This example returns a person-assignment result when matching assignments exist. It aligns each effective-dated join to the same bind date, while leaving assignment data optional.

SELECT
    ppf.person_id,
    ppf.person_number,
    ppf.effective_start_date,
    ppf.effective_end_date,
    n.display_name,
    a.assignment_id
FROM per_persons p
JOIN per_all_people_f ppf
  ON ppf.person_id = p.person_id
LEFT JOIN per_person_names_f n
  ON n.person_id = ppf.person_id
 AND :p_as_of_date BETWEEN n.effective_start_date
                       AND n.effective_end_date
LEFT JOIN per_all_assignments_f a
  ON a.person_id = ppf.person_id
 AND :p_as_of_date BETWEEN a.effective_start_date
                       AND a.effective_end_date
WHERE :p_as_of_date BETWEEN ppf.effective_start_date
                         AND ppf.effective_end_date

The join conditions do not force one row per person. If more than one name or assignment row matches the date, the result can contain multiple rows; apply the report’s name or assignment selection rule when it needs a narrower grain.

Apply one as-of date consistently

Bind one reporting parameter, such as :p_as_of_date, and use it for every effective-dated table in the query. The standard interval test is :p_as_of_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE.

Apply the condition independently to PER_ALL_PEOPLE_F, PER_PERSON_NAMES_F, PER_ALL_ASSIGNMENTS_F, and any other date-effective table you join. Filtering only the person row can leave out-of-period name or assignment rows in the result.

Define what “current” means by choosing the as-of date deliberately. A query run today does not automatically mean that future-dated rows belong in a current snapshot; the date parameter determines which effective interval qualifies.

The same pattern works for historical and future snapshots. Bind the date for the desired snapshot and return rows whose effective intervals contain it, rather than relying on an unspecified current-row assumption.

WHERE :p_as_of_date BETWEEN ppf.effective_start_date
                         AND ppf.effective_end_date

Keep the bind as a date value and apply the interval condition to each relevant table alias. That makes the reporting date explicit and keeps the predicates easy to audit.

Diagnose duplicate person rows by checking result grain

Your query returns multiple rows for one person. That may be valid, but first state the intended output grain: one row per person, one row per person-name, or one row per person-assignment.

Then count the matching effective-dated rows in each joined table for the selected as-of date. A person can legitimately match multiple name, assignment, or person-type rows, and mismatched date predicates can multiply results even when every join uses PERSON_ID.

If the report needs one row per person, define how to select or aggregate multirow related data. For example, specify the required name type or choose a deliberate assignment-selection rule, then implement that rule in the query.

DISTINCT is not a fix for an unclear join. It can hide repeated projected values while leaving the underlying one-to-many relationship unresolved, and it can discard meaningful differences between related rows.

Keep person-level fields separate from assignment-level fields. An additional assignment row is not a second person; it is a separate assignment record at a different grain.

Choose the source and access path for the report

Use PER_PERSONS for non-date-tracked person identity data and PER_ALL_PEOPLE_F for person data evaluated at an effective date. Use both only when the report needs both types of information.

Before adding a column, establish its table, grain, and date behavior. This prevents person attributes from being confused with name or assignment attributes, especially when the report combines several Oracle HCM tables.

Table selection and reporting access are separate decisions. Build the logical query around the correct relationships, then use an access method supported and secured for the Fusion reporting context.

For OTBI reporting, work with the subject area and logical query capabilities available to your reporting role. Use BI Publisher or another approved reporting route when the report requirements call for a different query approach; do not confuse knowledge of the physical table model with permission or support for direct database access.

Frequently Asked Questions

What is the difference between PERSON_ID and PERSON_NUMBER?

PERSON_ID is the key used to relate a person’s records in the HCM data model. PERSON_NUMBER is the business-facing identifier intended for operational use, display, or reporting.

How can a query retain a person when an optional assignment row does not match?

Use an outer join and put the assignment’s matching conditions in the join condition, not in a final WHERE clause that rejects null assignment rows. This allows the person row to remain in the result with null assignment columns.

What should a report do when no PER_ALL_PEOPLE_F row is effective on the requested as-of date?

Decide whether that person should be excluded or shown with missing effective-dated details, and make the report behavior explicit. Do not silently substitute a different interval, because that changes the meaning of the requested snapshot.

Can PER_PERSONS alone provide a person’s historical attribute values?

No, a non-date-tracked row does not provide a sequence of effective-dated historical versions by itself. For historical values, use a source that preserves the relevant history or a separately retained snapshot or audit record.

Why can a person appear more than once after joining PER_PERSON_NAMES_F?

Check the name context and effective-date conditions, then inspect the matching rows to identify which join condition needs refinement.

Conclusion

PER_PERSONS and PER_ALL_PEOPLE_F serve different roles: one is non-date-tracked, while the other represents person data across effective dates. Choose the source by attribute and reporting date, join through PERSON_ID, and align date predicates across every effective-dated table.

That is where PER_PERSONS and PER_ALL_PEOPLE_F: Where Person Data Diverges becomes a practical query decision. Define the result grain before joining, then investigate each one-to-many relationship instead of hiding it with DISTINCT.