Oracle Fusion Table Relationships Explained: ERP, HCM, and Procurement SQL Joins

Writing SQL in Oracle Fusion Cloud is rarely difficult because of SQL syntax itself. The real challenge is understanding how Oracle Fusion tables relate to each other. Which ID joins to which? Which tables are date-effective? Where do HCM, Finance, and Procurement connect? Why does one missing filter suddenly multiply your result set by 10x?

Oracle Fusion contains thousands of tables and views spread across HCM, ERP, Procurement, Projects, SCM, and Financials. Unlike traditional on-prem Oracle EBS environments, developers usually do not have direct access to database diagrams or visible foreign key relationships.

This guide explains the most important Oracle Fusion table relationships for HCM, ERP / Finance, Procurement, and cross-module reporting. It also covers the most common SQL join mistakes Oracle Fusion developers make - and how to avoid them.

Why Oracle Fusion SQL Feels Difficult

Thousands of database objects

Oracle Fusion contains tens of thousands of tables, views, packages, and metadata objects. Even experienced Oracle developers often struggle to identify the correct table, the correct join path, and the correct effective-date logic.

No visible foreign keys

Oracle Fusion applications do not expose traditional schema diagrams to reporting developers. Foreign key relationships are not obvious from the UI. Developers infer relationships through column naming conventions, Oracle documentation, metadata exploration, and experience.

Date-effective data structures

Tables ending with _F or _M store historical versions of records. Without effective-date filtering, joins return historical rows alongside current rows - causing duplicates and inflated row counts.

Cross-module complexity

HCM, Finance, Procurement, and Projects each use separate data models. Cross-module SQL requires understanding how shared entities connect: people, suppliers, organizations, ledgers, cost centers, business units, and chart-of-account structures.

Oracle Fusion HCM Table Relationships

Oracle Fusion HCM is built around three core concepts:

Entity	Purpose
Person	Who the worker is
Assignment	What the worker does
Period of Service	Employment lifecycle

Understanding these three entities unlocks most HCM reporting scenarios.

Core HCM Join Relationships

Table	Primary Key	Common Join	Purpose
`PER_ALL_PEOPLE_F`	PERSON_ID	-	Worker master record
`PER_PERSON_NAMES_F`	PERSON_ID	PERSON_ID	Worker names
`PER_ALL_ASSIGNMENTS_M`	ASSIGNMENT_ID	PERSON_ID	Job, department, manager
`PER_PERIODS_OF_SERVICE`	PERSON_ID	PERSON_ID	Hire and termination dates
`PER_JOBS_F`	JOB_ID	JOB_ID	Job definitions
`HR_ALL_ORGANIZATION_UNITS_F`	ORGANIZATION_ID	ORGANIZATION_ID	Departments
`HR_LOCATIONS_ALL_F`	LOCATION_ID	LOCATION_ID	Work locations
`CMP_SALARY`	ASSIGNMENT_ID	ASSIGNMENT_ID	Salary information

Example: Employee Directory Query

This is one of the most common Oracle Fusion HCM join patterns.

SELECT
    p.person_number,
    n.display_name,
    a.assignment_number,
    j.name                      AS job_title,
    org.name                    AS department_name,
    loc.location_name,
    pos.date_start              AS hire_date
FROM
    per_all_people_f            p
    JOIN per_person_names_f     n   ON n.person_id         = p.person_id
                                    AND n.name_type         = 'GLOBAL'
                                    AND TRUNC(SYSDATE)
                                        BETWEEN n.effective_start_date
                                            AND n.effective_end_date
    JOIN per_all_assignments_m  a   ON a.person_id         = p.person_id
                                    AND a.primary_flag      = 'Y'
                                    AND TRUNC(SYSDATE)
                                        BETWEEN a.effective_start_date
                                            AND a.effective_end_date
    JOIN per_jobs_f             j   ON j.job_id            = a.job_id
                                    AND TRUNC(SYSDATE)
                                        BETWEEN j.effective_start_date
                                            AND j.effective_end_date
    JOIN hr_all_organization_units_f org
                                    ON org.organization_id = a.organization_id
                                    AND TRUNC(SYSDATE)
                                        BETWEEN org.effective_start_date
                                            AND org.effective_end_date
    JOIN hr_locations_all_f     loc ON loc.location_id     = a.location_id
                                    AND TRUNC(SYSDATE)
                                        BETWEEN loc.effective_start_date
                                            AND loc.effective_end_date
    JOIN per_periods_of_service pos ON pos.person_id       = p.person_id
                                    AND pos.actual_termination_date IS NULL
WHERE
    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
ORDER BY
    n.display_name

Important HCM join rules

Always filter NAME_TYPE - PER_PERSON_NAMES_F stores GLOBAL, LOCAL, LEGAL, and MAIDEN name types. Without name_type = 'GLOBAL' you may get duplicate rows per person.
Always filter PRIMARY_FLAG - employees can have multiple assignments. Without primary_flag = 'Y' employees with secondary assignments appear multiple times.
Apply effective-date filters on every _F / _M table - missing just one can multiply rows dramatically.

Oracle Fusion Finance Table Relationships

Oracle Fusion Financials revolves around ledgers, journal entries, account combinations, AP invoices, AR transactions, and subledger accounting. The most important Finance join axis is CODE_COMBINATION_ID - which links accounting transactions to the chart of accounts.

Core Finance Join Relationships

Table	Join Key	Purpose
`GL_JE_HEADERS`	JE_HEADER_ID	Journal header
`GL_JE_LINES`	JE_HEADER_ID	Journal lines
`GL_CODE_COMBINATIONS`	CODE_COMBINATION_ID	Account segments
`GL_BALANCES`	CODE_COMBINATION_ID	Period balances
`AP_INVOICES_ALL`	VENDOR_ID	AP invoices
`AP_INVOICE_LINES_ALL`	INVOICE_ID	Invoice lines
`AP_SUPPLIERS`	VENDOR_ID	Supplier master
`HZ_CUST_ACCOUNTS`	PARTY_ID	Customer accounts
`HZ_PARTIES`	PARTY_ID	Customer / supplier party names

Example: GL Journal Query

SELECT
    gjh.period_name,
    gjh.name                    AS journal_name,
    gjl.je_line_num,
    gcc.concatenated_segments   AS account,
    gjl.accounted_dr,
    gjl.accounted_cr
FROM
    gl_je_headers               gjh
    JOIN gl_je_lines            gjl ON gjl.je_header_id       = gjh.je_header_id
    JOIN gl_code_combinations   gcc ON gcc.code_combination_id = gjl.code_combination_id
WHERE
    gjh.status = 'P'
ORDER BY
    gjh.period_name,
    gjh.name

Oracle Fusion Procurement Table Relationships

Procurement flows through a chain of related documents - PO Header to PO Line to Shipment Schedule to Receipt to AP Invoice. Understanding this lifecycle is essential for Procurement reporting.

Core Procurement Join Relationships

Table	Join Key	Purpose
`PO_HEADERS_ALL`	PO_HEADER_ID	Purchase order header
`PO_LINES_ALL`	PO_HEADER_ID	Purchase order lines
`PO_LINE_LOCATIONS_ALL`	PO_LINE_ID	Shipment schedules
`RCV_TRANSACTIONS`	PO_HEADER_ID / PO_LINE_ID	Receipts
`AP_INVOICE_LINES_ALL`	PO_HEADER_ID	AP invoice linkage
`AP_SUPPLIERS`	VENDOR_ID	Supplier information

Example: PO to AP Invoice Relationship

This join chain is commonly used for procurement reconciliation, unmatched invoices, three-way matching analysis, and spend reporting.

SELECT
    ph.segment1                 AS po_number,
    s.vendor_name,
    ai.invoice_num,
    ai.invoice_amount
FROM
    po_headers_all              ph
    JOIN ap_suppliers           s   ON s.vendor_id      = ph.vendor_id
    JOIN ap_invoice_lines_all   ail ON ail.po_header_id = ph.po_header_id
    JOIN ap_invoices_all        ai  ON ai.invoice_id    = ail.invoice_id
WHERE
    ph.authorization_status = 'APPROVED'

Cross-Module Oracle Fusion SQL Joins

Cross-module reporting is where Oracle Fusion SQL becomes especially powerful. OTBI subject areas typically isolate modules, but SQL can join across them directly - enabling scenarios like HCM + Finance, Procurement + AP, Projects + Procurement, and Payroll + GL.

Example: Employee Salary with Cost Center

SELECT
    n.display_name,
    org.name                    AS department_name,
    cs.salary_amount,
    gcc.concatenated_segments   AS cost_center_account
FROM
    per_all_people_f            p
    JOIN per_person_names_f     n   ON n.person_id         = p.person_id
                                    AND n.name_type         = 'GLOBAL'
                                    AND TRUNC(SYSDATE)
                                        BETWEEN n.effective_start_date
                                            AND n.effective_end_date
    JOIN per_all_assignments_m  a   ON a.person_id         = p.person_id
                                    AND a.primary_flag      = 'Y'
                                    AND TRUNC(SYSDATE)
                                        BETWEEN a.effective_start_date
                                            AND a.effective_end_date
    JOIN hr_all_organization_units_f org
                                    ON org.organization_id = a.organization_id
                                    AND TRUNC(SYSDATE)
                                        BETWEEN org.effective_start_date
                                            AND org.effective_end_date
    LEFT JOIN cmp_salary        cs  ON cs.assignment_id    = a.assignment_id
    LEFT JOIN gl_code_combinations gcc
                                    ON gcc.segment2        = a.default_code_comb_id
WHERE
    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date

Cross-module joins like this are extremely difficult in OTBI without custom subject areas or additional modeling.

Common Oracle Fusion Join Mistakes

1. Missing effective-date filters

The most common Oracle Fusion SQL mistake. Missing filters on _F or _M tables produce duplicate historical rows and inflate row counts dramatically.

2. Missing PRIMARY_FLAG

Without primary_flag = 'Y' on PER_ALL_ASSIGNMENTS_M, employees with multiple assignments appear multiple times in the result set.

3. Missing NAME_TYPE

Without name_type = 'GLOBAL', PER_PERSON_NAMES_F returns multiple name records per person - one for each name type stored.

4. Incorrect customer / supplier join paths

TCA relationships require two hops: AR_PAYMENT_SCHEDULES_ALL to HZ_CUST_ACCOUNTS to HZ_PARTIES. Skipping the intermediary table causes incorrect joins or missing results.

5. Cartesian joins

One missing join condition can multiply millions of rows accidentally. Always validate joins incrementally, checking row counts after each addition.

Best Practices for Oracle Fusion SQL Joins

Build Queries Incrementally

Start with one table. Add joins one at a time. Validate row counts after every join. If the count unexpectedly doubles or multiplies, investigate the join condition before continuing.

Learn the Core Join Axes

Some join keys appear everywhere across Oracle Fusion modules. Mastering these dramatically speeds up SQL development:

Join Key	Purpose
`PERSON_ID`	HCM worker identity
`ASSIGNMENT_ID`	HCM assignment
`ORGANIZATION_ID`	Departments
`VENDOR_ID`	Suppliers
`PARTY_ID`	TCA entities (customers, suppliers)
`CODE_COMBINATION_ID`	GL accounts
`PO_HEADER_ID`	Procurement headers

Understand Module Boundaries

HCM, ERP, Procurement, SCM, and Projects each have different data model design patterns. Knowing where modules connect is often more important than knowing SQL syntax itself.

Explore Oracle Fusion Table Relationships with FusionLens SQL

📂

Schema Navigator

Browse Oracle Fusion tables and explore column structures interactively - understand join patterns before writing SQL.

⚡

SQL Autocomplete

Autocomplete for Oracle Fusion table names, column names, and join conditions — fewer trial-and-error joins.

📤

One-Click Export

Export query results in one click - build your own Oracle Fusion table relationship reference documentation.

Try FusionLens SQL Free

Final Thoughts

Understanding Oracle Fusion table relationships is the foundation of effective SQL development. Most Oracle Fusion SQL problems are not caused by SQL syntax - they come from incorrect joins, missing effective-date filters, misunderstood module boundaries, and hidden relationship paths.

Once developers understand the core Oracle Fusion join structures across HCM, Finance, and Procurement, writing reliable SQL becomes dramatically easier. Strong Oracle Fusion SQL skills ultimately come down to one thing: understanding how the data model connects together.

Try FusionLens SQL to explore Oracle Fusion schemas, discover join paths, and build correct SQL across HCM, Finance, and Procurement modules.