IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle in Oracle Fusion HCM tables (2026)

In 2026, your recruiting funnel does not “quietly” fill the database. It pours records into IRC_CANDIDATES, and if you do not control the join logic into PER_ALL_PEOPLE_F, your headcount and lifecycle metrics will drift off fast.

Key Takeaways: IRC_CANDIDATES vs PER_ALL_PEOPLE_F applicant lifecycle mapping

If you are building an applicant lifecycle report in oracle fusion hcm or oracle cloud hcm, this guide compares the tables you keep confusing: IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle. We also show you where report logic breaks (duplicates, inflated headcount, and wrong status timing) and what filters to add first.

Why IRC_CANDIDATES and PER_ALL_PEOPLE_F must be treated as two different lifecycle layers

The #1 cause of broken applicant lifecycle metrics is assuming IRC_CANDIDATES and PER_ALL_PEOPLE_F are the same “stage view” of the world.

IRC_CANDIDATES stores candidate-specific metadata for recruiting workflows. It links to PER_ALL_PEOPLE_F using person anchoring, so you can map candidate records to person records as applicants progress.

PER_ALL_PEOPLE_F is the cornerstone of person data in Oracle Fusion HCM. It is designed for historical tracking through date-effective history, meaning your report must respect the time slice you intend to analyze.

“Your query returns 3 rows for every employee. Your headcount report is off by 200%.” If you join candidates to person without selecting the correct effective slice, you get row multiplication, then your lifecycle counts look like a glitch.

The fix is not changing the join. The fix is choosing the correct date-effective semantics on the PER_ALL_PEOPLE_F side.

The Short Answer

IRC_CANDIDATES answers “what happened in recruiting.” PER_ALL_PEOPLE_F answers “who is this person and how their attributes change over time.” Your lifecycle query needs both, but it needs the right time filters and latest-change logic.

Core join pattern for oracle hcm tables: candidate to person with date-effective correctness

Start with the anchor, then lock the time slice. Candidate-to-person joins should be deterministic, not “whatever row happens to join.”

On the IRC_CANDIDATES side, you typically use the person identifier to locate the person in PER_ALL_PEOPLE_F. On the PER_ALL_PEOPLE_F side, you must apply effective dating rules and latest-change logic so you pull the state you actually want.

That exact column name is shown for PER_ALL_ASSIGNMENTS_M, but the principle is the same when you land on PER_ALL_PEOPLE_F for lifecycle reporting. We routinely see teams filter assignments but forget person history, and you get inconsistent lifecycle attributes.

In practice, your physical SQL typically follows this shape:

• Join IRC_CANDIDATES.PERSON_ID to PER_ALL_PEOPLE_F.PERSON_ID.
• Apply a consistent effective dating strategy on PER_ALL_PEOPLE_F for the reporting window you want.
• If you need current-state attributes for status at a point in time, use the latest-change indicator semantics.
• Do not “summarize after joining.” Summarize candidates first if you can, then attach person attributes.

“That single column eliminates 95% of duplicate row problems.” The same outcome applies when your query uses the correct effective slice for date-effective history. The goal is the same, stop your lifecycle counts from exploding because of historical rows.

And yes, this is where OTBI reporting and BI Publisher often feel different. OTBI subject areas can hide effective-dated details, while physical SQL exposes the raw mechanics. For that reason, teams that care about auditability usually standardize on a physical SQL pattern, then surface results in BI Publisher if needed.

If you’re planning the report layer, our OTBI reporting vs BI Publisher decision guide is the fastest way to align “what the UI exposes” with “what your lifecycle query needs.”

Where PER_ALL_PEOPLE_F breaks applicant lifecycle counts (and how to fix it)

The failure mode is always the same. You join to PER_ALL_PEOPLE_F, the person table returns multiple rows due to date-effective history, and your lifecycle aggregates get inflated.

“The problem is that most queries only want current state, and forgetting to filter for it is the #1 cause of inflated headcount numbers in Oracle HCM reporting.” For applicant lifecycle, it is worse, because you can mix recruiting events, offer events, and worker conversion events in a single report.

Here are the common mistakes we see when teams build IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle reports:

• Using PER_ALL_PEOPLE_F without effective dating: you match the wrong historical row, then attach wrong demographics or attributes to the candidate stage.
• Filtering assignments but not the person record: you correct tenure context but keep historical person rows, so lifecycle status and demographic attributes desync.
• Aggregating after wide joins: candidate-to-person-to-assignment joins create multiplicative rows. Summarize candidate events first, then enrich.
• Assuming OTBI “latest” maps to “latest effective row”: OTBI may present a current view, physical SQL still needs your filters.

“This is not a bug. It's the architecture.” Oracle’s date-effective history is correct by design. Your reporting has to choose which slice of history represents the lifecycle stage you are measuring in oracle fusion hcm.

Applicant lifecycle is not just person data: add assignments, jobs, and org context

Candidate stage is recruiting, but applicant lifecycle usually becomes “person lifecycle” once a hire, transfer, or position change happens. That is where PER_ALL_ASSIGNMENTS_F and PER_ALL_ASSIGNMENTS_M stop being optional.

PER_ALL_ASSIGNMENTS_F manages assignment-level data for employees, reflecting job and position history with date-effective attributes. It is frequently joined to PER_ALL_PEOPLE_F to build a complete employee lifecycle picture.

PER_ALL_ASSIGNMENTS_M is the historical, effective-dating version of assignments. If you need long-running tenure history, the M-suffix table pattern is often the right place to start.

Now apply the same discipline: effective dating and latest-change logic on the assignment side. If you only fix duplicates on person and ignore duplicates on assignments, you can still get lifecycle counts that do not reconcile.

Here’s a practical enrichment flow for oracle hr tables lifecycle reporting:

1. Build a candidate event set from IRC_CANDIDATES (application created, submitted, status changes).
2. Join to PER_ALL_PEOPLE_F using person identity and an effective slice.
3. When the lifecycle crosses into worker state, join to PER_ALL_ASSIGNMENTS_F for current job and org context, or PER_ALL_ASSIGNMENTS_M for historical tracking.
4. Attach additional context tables only after your candidate-to-person grain is correct.

If you want the structure and relationships for the tables involved, treat our table pages like your hcm data model workbook. For example, start with PER_ALL_PEOPLE_F, then move to PER_ALL_ASSIGNMENTS_F when you need assignment-linked lifecycle fields.

When OTBI reporting works, and when physical SQL is the only production-ready option

This is where teams waste time. They try to force a lifecycle query into OTBI subject areas when the data model needed for the exact logic is not exposed.

“Most compliance reporting requires physical SQL because OTBI subject areas don't expose the raw legislative data needed for EEO-1 and ACA calculations.” Even if your lifecycle report is not EEO-1, the same principle applies when lifecycle logic depends on raw table fields and exact date-effective behavior.

So how do you decide?

• If you only need “report-friendly current state” attributes for a dashboard, OTBI reporting can work.
• If you need strict effective-dating slices and deterministic deduping across IRC_CANDIDATES vs PER_ALL_PEOPLE_F, physical SQL is usually production-ready SQL.
• If you need to deliver the results consistently in BI Publisher, generate the dataset in SQL, then render using your reporting template.

Also, if you are ingesting lifecycle staging data via an ETL process, keep the pipeline semantics consistent. Our Oracle HCM benefits tables guide discusses why Oracle’s table complexity changes how you should design data loaders and reconciliation, especially for longitudinal analytics like applicant lifecycle.

And if you are using an HDL data loader approach, the integration needs to respect candidate-to-person mapping rules. Otherwise, you will reproduce the same duplicates downstream, just later in the pipeline.

Operational enhancements for applicant lifecycle: address, location, and party context

Once the core lifecycle counts are correct, you can enrich them. This is the part most teams do too early, then they can’t debug duplicates because everything is multiplied.

Useful enrichment tables from the oracle hr tables universe include:

• PER_ADDRESSES_F for date-effective address history, useful for location-aware compliance and reporting.
• HR_LOCATIONS_ALL for location context that pairs with assignments and person data.
• HZ_PARTIES as a party container for enriched person and cross-module context.

This enrichment is often tied to demographic, geographic, or compliance reporting, but the critical rule stays the same. Lock your grain first (candidate event to person identity), then attach these attributes.

If you are mapping codes (race/ethnicity, job categories, similar classifications), use a maintained code reference approach. Our lookup codes reference helps you avoid the “mapping drift” that makes 2026 analytics disagree with yesterday’s exports.

Practical report examples for IRC_CANDIDATES vs PER_ALL_PEOPLE_F lifecycle tracking in 2026

Let’s make this concrete. Your lifecycle report almost always needs two layers of logic: recruiting stages from IRC_CANDIDATES, and person-state attributes from PER_ALL_PEOPLE_F.

Example 1, candidate volume to hire conversion (2026):

• Count candidate records and group by candidate stage from IRC_CANDIDATES.
• Map candidates to persons via IRC_CANDIDATES.PERSON_ID to PER_ALL_PEOPLE_F.PERSON_ID.
• When a person enters worker lifecycle, join assignments using PER_ALL_ASSIGNMENTS_F for job and org context.
• Validate row counts at each grain boundary before you add more columns.

Example 2, demographic lifecycle snapshots:

• Pull demographics from PER_ALL_PEOPLE_F, using effective dating slices that match the candidate stage date you are analyzing.
• Attach the demographic attributes to the candidate event grain, do not attach them to the wrong historical person row.
• If the query must be stable for OTBI reporting consumption, test equivalent logic in physical SQL first.

“We built HCM Tables specifically because Oracle's schema is so complex that even experienced HCM developers need a dedicated search engine for it (14,950 tables, 1.2 million columns). That complexity has a real cost.” The cost is debugging time. Avoid it by building lifecycle queries with explicit grain, explicit effective slices, and controlled joins.

Deployment guidance: BI Publisher, OTBI reporting, and safe operationalization

After your query logic is correct, operationalize it without breaking lifecycle integrity.

• BI Publisher: Use the query dataset as a deterministic input. Keep parameter binding explicit so your reporting window stays consistent across retries.
• OTBI reporting: Use it for quick validation, then reconcile. If lifecycle numbers must match compliance-grade exports, physical SQL is your source of truth.
• ETL and loaders (including HDL data loader patterns): Keep your join and effective-slice rules identical between load time and report time. Otherwise, the same candidate maps to different person attribute states.

Also, if you are using formulas or calculations in the HCM layer, watch out for logic built around variable-hour employee status. “Getting variable-hour employee status wrong creates IRS exposure. The PAY_ query logic is non-obvious.” That warning is payroll-related, but the general risk applies to lifecycle calculations too, any logic that changes per time slice can invalidate lifecycle reporting when joins are not anchored correctly.

Our compliance-oriented table SQL packs are designed for that kind of operational need. See compliance SQL pack if you want a production-ready pattern library that respects Oracle HCM table semantics.

Recruiting data shows candidate volume dwarfs eventual hire counts across the applicant lifecycle.

Conclusion: the correct lifecycle model for IRC_CANDIDATES vs PER_ALL_PEOPLE_F in oracle fusion hcm (2026)

IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle only works when you treat recruiting events and person history as different layers, then join them with explicit date-effective correctness.

Use IRC_CANDIDATES to measure candidate progression, use PER_ALL_PEOPLE_F to attach date-effective person attributes, and only then enrich with assignments like PER_ALL_ASSIGNMENTS_F or historical views like PER_ALL_ASSIGNMENTS_M. That’s how you keep lifecycle counts stable in oracle cloud hcm, accurate in oracle fusion hcm, and consistent across OTBI reporting and BI Publisher.

And if you are debugging duplicates, remember the rule of thumb: fix the grain boundary first, then apply effective slice logic. It eliminates 95% of the “why does my headcount look wrong” fire drills, because this is not a bug. It’s the architecture.

Frequently Asked Questions about IRC_CANDIDATES vs PER_ALL_PEOPLE_F applicant lifecycle tracking

How do I join IRC_CANDIDATES to PER_ALL_PEOPLE_F in oracle hcm tables without duplicating rows?

Join on the person identifier, then apply consistent date-effective filtering and latest-change semantics on PER_ALL_PEOPLE_F. “Your query returns 3 rows for every employee. Your headcount report is off by 200%.” If you skip effective slicing, duplicates are guaranteed.

Should I build applicant lifecycle reports using OTBI reporting or physical SQL against PER_ALL_PEOPLE_F?

If you need deterministic effective-dating behavior and tight lifecycle event-to-person mapping, physical SQL is safer for IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle. OTBI reporting can validate quickly, but it may not expose the raw legislative and effective-dated mechanics you need for precise lifecycle logic.

What is the best table to use for person history in oracle fusion hcm when tracking the applicant lifecycle?

Use PER_ALL_PEOPLE_F. It is the cornerstone of person data and supports historical tracking through date-effective history, which is exactly what your applicant lifecycle snapshots require in 2026.

Why do candidate lifecycle counts change when I add job or assignment data from PER_ALL_ASSIGNMENTS_F?

Because assignment history can multiply rows if you join at the wrong grain or filter incorrectly on effective dates. For lifecycle tracking, summarize candidate events first, then attach assignment context from PER_ALL_ASSIGNMENTS_F using the same effective time window.

What does “date-effective history” mean for PER_ALL_PEOPLE_F in applicant lifecycle reporting?

It means the same person can have multiple attribute rows across time, so your lifecycle query must pick the correct time slice. If you do not, you attach the wrong person attribute state to the correct candidate stage, and your lifecycle metrics drift.

Do I need a BI Publisher dataset or can I rely on OTBI reporting for applicant lifecycle dashboards in oracle cloud hcm?

If your lifecycle metrics must match compliance-grade exports, build the dataset once using physical SQL and serve it through BI Publisher. Use OTBI reporting only for validation or when your logic fits what the subject areas expose.

Where does HDL data loader fit into IRC_CANDIDATES vs PER_ALL_PEOPLE_F lifecycle tracking?

HDL data loader patterns matter when you stage recruiting and person data into a warehouse or reporting layer. Keep the same join keys and effective-slice rules you use in Oracle, otherwise you will reproduce duplicates and attribute mismatches later in the lifecycle pipeline.

Conclusion in one line

IRC_CANDIDATES vs PER_ALL_PEOPLE_F: tracking the applicant lifecycle is a join-of-layers problem, fix the effective slice and grain first, then enrich, and you will stop the inflated lifecycle counts.