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

In Oracle Fusion Cloud, the difficult part of SQL is usually not the syntax. It is choosing the correct relationship and the correct grain. A column with the same-looking ID can represent a person, assignment, work relationship, supplier, party, purchase order schedule, accounting distribution, or subledger entity. Joining at the wrong level can return valid-looking SQL with completely wrong totals.

This guide focuses on the relationship paths Oracle documents for HCM, Financials, and Procurement, and on the practical rules that make those joins reliable in BI Publisher and other physical-SQL reporting. The examples deliberately distinguish business keys from physical keys, current-state joins from historical joins, and header-level relationships from line, schedule, and distribution-level relationships.

The most important rule

Do not join at a higher grain just because the column exists there. Use the deepest common key that represents the business event you are reporting. A PO header join may be enough for a document list; three-way matching usually needs schedule, distribution, or receipt transaction keys.

Think in Grain Before You Think in Tables

Identity grain

Person, supplier, customer party, ledger, project, and other master identities.

Document grain

Invoice header, journal header, purchase order header, requisition header.

Detail grain

Invoice line, journal line, PO line, shipment schedule, accounting distribution.

Temporal grain

Date-effective versions and, for some _M objects, multiple changes on the same day.

Before adding a join, write down what one output row is supposed to represent. If one row means "one employee assignment," joining a person to every work relationship or every historical assignment version will immediately break that grain.

Oracle Fusion HCM Relationships

The HCM worker model is easier to understand when you separate person, work relationship, and assignment. PERSON_ID is the identity axis, but it is not always the safest join key for employment lifecycle data.

Object	Important key / grain	Typical relationship
`PER_ALL_PEOPLE_F`	PERSON_ID + effective dates	Person-level date-effective attributes
`PER_PERSON_NAMES_F`	PERSON_NAME_ID + effective dates	Join by PERSON_ID; choose the required NAME_TYPE
`PER_ALL_ASSIGNMENTS_M`	ASSIGNMENT_ID + dates + ELC + sequence	PERSON_ID, PERIOD_OF_SERVICE_ID, JOB_ID, ORGANIZATION_ID, LOCATION_ID
`PER_PERIODS_OF_SERVICE`	PERIOD_OF_SERVICE_ID	Join from assignment using PERIOD_OF_SERVICE_ID
`PER_JOBS_F_VL`	JOB_ID + effective dates	Language-aware job name for ASSIGNMENT.JOB_ID
`HR_ALL_ORGANIZATION_UNITS`	ORGANIZATION_ID	Current language-aware organization name
`HR_LOCATIONS_ALL_F_VL`	LOCATION_ID + effective dates	Language-aware location name
`CMP_SALARY`	SALARY_ID; indexed by ASSIGNMENT_ID/date range	Salary history by ASSIGNMENT_ID

PER_ALL_ASSIGNMENTS_M is not an ordinary _F table.

It allows multiple changes on the same day. For a normal current or as-of assignment snapshot, date filtering alone can still return more than one physical row. Use EFFECTIVE_LATEST_CHANGE='Y' when your intended grain is the final assignment state for that effective day.

Example: Current Primary Employee Assignment

SELECT p.person_number,
       n.display_name,
       a.assignment_number,
       j.name                    AS job_name,
       org.name                  AS department_name,
       loc.location_name,
       pos.date_start            AS work_relationship_start
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.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE)
          BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN per_jobs_f_vl j
       ON j.job_id = a.job_id
      AND TRUNC(SYSDATE)
          BETWEEN j.effective_start_date AND j.effective_end_date
LEFT JOIN hr_all_organization_units org
       ON org.organization_id = a.organization_id
LEFT JOIN hr_locations_all_f_vl loc
       ON loc.location_id = a.location_id
      AND TRUNC(SYSDATE)
          BETWEEN loc.effective_start_date AND loc.effective_end_date
LEFT JOIN per_periods_of_service pos
       ON pos.period_of_service_id = a.period_of_service_id
WHERE  TRUNC(SYSDATE)
       BETWEEN p.effective_start_date AND p.effective_end_date
ORDER BY n.display_name

This query intentionally asks for the primary employee assignment. That is a business choice, not a generic duplicate-removal trick. If the report needs contingent workers, nonworkers, secondary assignments, or every active work relationship, change the assignment predicates rather than blindly keeping PRIMARY_FLAG='Y'.

Do not label PERIODS_OF_SERVICE.DATE_START as "enterprise hire date" without qualification.

It is the start of that work relationship. A person can have multiple work relationships over time, which is exactly why joining PERIODS_OF_SERVICE only on PERSON_ID is unsafe.

Oracle Fusion Financials Relationships

Financials contains several separate relationship axes. CODE_COMBINATION_ID is the accounting account axis, but it does not by itself mean that two rows represent the same transaction. Ledger, period, currency, source, and transaction grain still matter.

Relationship	Correct path
GL journal header → line	`GL_JE_HEADERS.JE_HEADER_ID → GL_JE_LINES.JE_HEADER_ID`
GL line → account combination	`GL_JE_LINES.CODE_COMBINATION_ID → GL_CODE_COMBINATIONS.CODE_COMBINATION_ID`
GL balance → account combination	`GL_BALANCES.CODE_COMBINATION_ID → GL_CODE_COMBINATIONS.CODE_COMBINATION_ID`, with ledger/period/currency/balance-type grain retained
AP invoice → supplier	`AP_INVOICES_ALL.VENDOR_ID → POZ_SUPPLIERS.VENDOR_ID → HZ_PARTIES.PARTY_ID`
AP invoice → payment	`AP_INVOICES_ALL → AP_INVOICE_PAYMENTS_ALL → AP_CHECKS_ALL`
AR transaction → customer account	`RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID → HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID`
Customer account → party name	`HZ_CUST_ACCOUNTS.PARTY_ID → HZ_PARTIES.PARTY_ID`

Example: Posted GL Journal Lines

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.ledger_id   = :p_ledger_id
AND    gjh.period_name = :p_period_name
AND    gjh.status      = 'P'
ORDER BY gjh.name,
         gjl.je_line_num

Filtering the ledger and period is not merely a performance choice; it also makes the report grain explicit. A code combination can be used across many journals and periods, so joining unrelated Financials objects only because they share a CCID is not transaction tracing.

AP to SLA to GL: the Accounting Trace

AP_INVOICES_ALL.INVOICE_ID

  ↓ XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1

XLA_TRANSACTION_ENTITIES.ENTITY_ID

  ↓ XLA_AE_HEADERS.ENTITY_ID + APPLICATION_ID

XLA_AE_HEADERS.AE_HEADER_ID

  ↓ XLA_AE_LINES.AE_HEADER_ID + APPLICATION_ID

XLA_AE_LINES.GL_SL_LINK_ID + GL_SL_LINK_TABLE

  ↓

GL_IMPORT_REFERENCES.GL_SL_LINK_ID + GL_SL_LINK_TABLE

  ↓ JE_HEADER_ID + JE_LINE_NUM

GL_JE_LINES

For Payables invoices, the XLA entity lookup is application/entity specific; do not assume XLA_AE_HEADERS.ENTITY_ID = AP_INVOICES_ALL.INVOICE_ID. The transaction first maps through XLA_TRANSACTION_ENTITIES. The final SLA-to-GL drillback should also match both GL_SL_LINK_ID and GL_SL_LINK_TABLE. GL_IMPORT_REFERENCES is populated when the journal source is configured to maintain import references, so availability and final grain can vary with accounting and transfer configuration.

Oracle Fusion Procurement Relationships

A purchase order is not one row. The useful reporting chain is header → line → schedule → distribution. Receipts and Payables matching can reference several points in that chain.

Object	Relationship	Reporting meaning
`PO_HEADERS_ALL`	PO_HEADER_ID	Purchasing document header
`PO_LINES_ALL`	PO_HEADER_ID	PO line
`PO_LINE_LOCATIONS_ALL`	PO_LINE_ID / PO_HEADER_ID	Shipment schedule
`PO_DISTRIBUTIONS_ALL`	LINE_LOCATION_ID / PO_LINE_ID / PO_HEADER_ID	Accounting/requester distribution
`RCV_TRANSACTIONS`	PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID	Receiving transaction
`AP_INVOICE_LINES_ALL`	PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID, RCV_TRANSACTION_ID	Matched AP invoice line
`POZ_SUPPLIERS`	VENDOR_ID → PARTY_ID	Supplier master; display name comes from HZ_PARTIES

Choose the lowest common key.

For "which invoices reference this PO?" a header relationship can be acceptable. For matching, receipt, quantity, charge-account, or project analysis, prefer schedule/distribution/receipt keys because a single PO can contain many lines, schedules, and distributions.

Example: PO Distribution to Matched AP Invoice Line

SELECT ph.segment1              AS po_number,
       pl.line_num,
       pll.shipment_num,
       pod.distribution_num,
       ps.segment1              AS supplier_number,
       hp.party_name            AS supplier_name,
       ai.invoice_num,
       ail.line_number          AS invoice_line_number,
       ail.amount               AS invoice_line_amount
FROM   po_headers_all ph
JOIN   po_lines_all pl
       ON pl.po_header_id = ph.po_header_id
JOIN   po_line_locations_all pll
       ON pll.po_header_id = ph.po_header_id
      AND pll.po_line_id   = pl.po_line_id
JOIN   po_distributions_all pod
       ON pod.po_header_id     = ph.po_header_id
      AND pod.po_line_id       = pl.po_line_id
      AND pod.line_location_id = pll.line_location_id
JOIN   poz_suppliers ps
       ON ps.vendor_id = ph.vendor_id
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id
JOIN   ap_invoice_lines_all ail
       ON ail.po_distribution_id = pod.po_distribution_id
JOIN   ap_invoices_all ai
       ON ai.invoice_id = ail.invoice_id
WHERE  ph.approved_flag = 'Y'
AND    ph.prc_bu_id     = :p_prc_bu_id
ORDER BY ph.segment1,
         pl.line_num,
         pll.shipment_num,
         pod.distribution_num,
         ai.invoice_num,
         ail.line_number

This query is deliberately at PO distribution / AP invoice-line grain. Do not aggregate AP_INVOICES_ALL.INVOICE_AMOUNT over this result without rethinking the grain: the same invoice header amount can repeat across multiple matched lines and distributions.

Receipt Matching

RCV_TRANSACTIONS carries PO header, line, schedule, and distribution identifiers, and AP invoice lines can carry RCV_TRANSACTION_ID. If the business question is "which receipt was invoiced?", the receipt transaction ID is stronger evidence than merely finding the same PO header on both sides.

Cross-Module Joins: Use Real Foreign Keys, Not Segment Guessing

Cross-module SQL is useful, but the join still has to be a documented relationship. A common error is to compare an internal numeric ID to a chart-of-accounts segment value just because the report calls both fields "cost center."

Example: Current Salary with Assignment Default Account

SELECT n.display_name,
       a.assignment_number,
       cs.salary_amount,
       cs.currency_code,
       gcc.concatenated_segments AS assignment_default_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.assignment_type = 'E'
      AND a.primary_flag = 'Y'
      AND a.effective_latest_change = 'Y'
      AND TRUNC(SYSDATE)
          BETWEEN a.effective_start_date AND a.effective_end_date
LEFT JOIN cmp_salary cs
       ON cs.assignment_id = a.assignment_id
      AND cs.salary_approved = 'Y'
      AND TRUNC(SYSDATE) BETWEEN cs.date_from AND cs.date_to
LEFT JOIN gl_code_combinations gcc
       ON gcc.code_combination_id = a.default_code_comb_id
WHERE  TRUNC(SYSDATE)
       BETWEEN p.effective_start_date AND p.effective_end_date
ORDER BY n.display_name

PER_ALL_ASSIGNMENTS_M.DEFAULT_CODE_COMB_ID is a foreign key to GL_CODE_COMBINATIONS.CODE_COMBINATION_ID. It must not be compared to SEGMENT2 or any other segment. Which segment represents cost center is customer-specific and depends on the chart-of-accounts structure.

Security is a separate dimension.

A physically valid cross-module join is not automatically a secure report. In BI Publisher physical SQL, selecting directly from base tables is not automatically restricted by Fusion data-security profiles. Use Oracle secured list views where required and design authorization scope explicitly. HCM salary data, for example, has a dedicated secured list view.

Seven Relationship Rules That Prevent Most Oracle Fusion SQL Errors

1. Do not confuse an ID with a business number

PO_HEADER_ID is not PO number; CUSTOMER_TRX_ID is not transaction number; VENDOR_ID is not supplier number.

2. Preserve the intended grain

Header → line → schedule → distribution is 1:N at each step. More rows may be correct.

3. Date-filter every effective object

Each _F/_M object in an as-of join needs its own temporal predicate.

4. Treat PRIMARY_FLAG as business semantics

Use it when you specifically want the primary assignment/work relationship, not as a generic DISTINCT substitute.

5. Use ELC on multi-change assignment snapshots

PER_ALL_ASSIGNMENTS_M can contain multiple same-day physical rows.

6. Resolve party names through TCA

Supplier and customer operational IDs often lead to HZ_PARTIES through POZ_SUPPLIERS or HZ_CUST_ACCOUNTS.

7. Never invent a cross-module bridge

Who columns, segment values, display numbers, and similarly named IDs are not foreign keys unless the model says they are.

Common Mistakes in the Original Patterns

Anti-pattern	Why it fails	Correct approach
`PER_JOBS_F.NAME`	The base table stores job attributes, not the language-aware NAME column.	Use `PER_JOBS_F_VL` when you need the display name.
`HR_ALL_ORGANIZATION_UNITS_F.NAME`	NAME is supplied through the translation layer.	Use a language-aware view or join the TL object correctly.
PERIODS_OF_SERVICE joined only by PERSON_ID	A person can have multiple work relationships.	Join assignment → `PERIOD_OF_SERVICE_ID`.
Date predicate only on `PER_ALL_ASSIGNMENTS_M`	Same-day multiple changes can remain.	Add `EFFECTIVE_LATEST_CHANGE='Y'` for final-state snapshots.
`AP_SUPPLIERS` as Fusion supplier master	Current Fusion supplier master is POZ/TCA based.	`POZ_SUPPLIERS → HZ_PARTIES`.
`PO_HEADERS_ALL.AUTHORIZATION_STATUS`	Not the current documented Fusion status column.	Use `DOCUMENT_STATUS` and/or `APPROVED_FLAG` according to the report question.
PO → invoice only by PO_HEADER_ID	Header-level relationship loses matching grain.	Use schedule, distribution, or receipt transaction keys when available.
`GCC.SEGMENT2 = ASSIGNMENT.DEFAULT_CODE_COMB_ID`	Compares a segment value to a code-combination surrogate key.	`GCC.CODE_COMBINATION_ID = DEFAULT_CODE_COMB_ID`.
AP invoice ID directly to XLA entity ID	Subledger entity IDs are separate XLA identities.	Resolve through `XLA_TRANSACTION_ENTITIES` using the application/entity source mapping.

A Practical Join-Validation Workflow

Define one-row meaning.
Person? Assignment? PO distribution? Receipt transaction? AP invoice line?

Verify each key in Oracle metadata.
Check the documented PK/FK, not a similarly named column.

Add temporal logic.
Use the same as-of date across date-effective dimensions and understand same-day sequencing.

Validate expected cardinality.
A 1:N join should increase rows; an unexplained N:M multiplication is the warning sign.

Validate amounts at their own grain.
Never sum a header total after joining it to multiple lines without deliberate de-duplication or re-aggregation.

Validate data security separately.
A correct join path does not automatically recreate OTBI/Fusion application security.

Explore Oracle Fusion Relationships with FusionLens SQL

Schema Navigator

Inspect Oracle Fusion tables, columns, descriptions, and keys before writing the join.

SQL Autocomplete

Write joins with Oracle Fusion table and column awareness instead of guessing relationship names.

Live Validation

Run the SQL against your own Fusion connection and validate row counts and grain before moving it to BI Publisher.

Try FusionLens SQL Free

Final Thoughts

Reliable Oracle Fusion SQL is primarily a data-model skill. Once the correct grain is explicit, most join decisions become much easier: use assignment IDs for assignment facts, period-of-service IDs for work relationships, distribution IDs for PO accounting grain, TCA party IDs for names, code-combination IDs for accounts, and XLA entity/link identifiers for accounting traceability.

The dangerous joins are the ones that look plausible: PERSON_ID where a work-relationship ID is required, PO_HEADER_ID where distribution grain is required, or a chart-of-accounts segment where a code-combination surrogate key is required. Validate the relationship first, then optimize the SQL.

For date-effective joins, see the Effective Date Handling guide. For Finance and Procurement-specific examples, see the Finance SQL guide and Procurement SQL guide.