Oracle Fusion OTBI Subject Areas: SQL Table Reference for BI Publisher Developers

When reproducing Oracle Fusion OTBI reports in BI Publisher or direct SQL, knowing which base tables each subject area commonly sources from is a critical starting point. OTBI abstracts the schema complexity for report builders - but SQL developers who need to go beyond OTBI's limits must understand what data those subject areas are drawing from.

Important context: OTBI subject areas are built on top of Oracle BI logical models and secured view objects rather than directly querying base tables. Between an OTBI column and a database table there are typically several layers: the RPD physical layer, logical model, view objects, and security predicates. The mappings below represent the most common underlying tables SQL developers work with when reproducing OTBI reports - not a guaranteed one-to-one technical mapping.

This reference covers the most commonly used Oracle Fusion OTBI subject areas across HCM, Finance, and Procurement - listing the base tables developers typically query when building SQL equivalents.

HCM Subject Areas

OTBI Subject Area	Primary SQL Tables	Key Join Column
Workforce Management - Worker Assignment Real Time	PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, PER_PERSON_NAMES_F	PERSON_ID
Workforce Management - Position Real Time	HR_ALL_POSITIONS_F, PER_ALL_ASSIGNMENTS_M	POSITION_ID
Payroll - Payroll Results Real Time	PAY_RUN_RESULTS, PAY_RUN_RESULT_VALUES	PAYROLL_RELATIONSHIP_ID
Workforce Management - Absence Real Time	ANC_PER_ABS_ENTRIES, ANC_ABSENCE_TYPES_F	ABSENCE_ENTRY_ID
Compensation - Salary Details Real Time	CMP_SALARY, PER_ALL_ASSIGNMENTS_M	ASSIGNMENT_ID
Talent Management - Performance Goal Real Time	HRM_GOAL_VERSIONS, HRM_GOALS	GOAL_ID

Finance Subject Areas

OTBI Subject Area	Primary SQL Tables	Key Join Column
General Ledger - Journal Real Time	GL_JE_HEADERS, GL_JE_LINES	JE_HEADER_ID
General Ledger - Account Balance Real Time	GL_BALANCES, GL_CODE_COMBINATIONS	CODE_COMBINATION_ID
Payables Invoices - Transactions Real Time	AP_INVOICES_ALL, AP_INVOICE_LINES_ALL	INVOICE_ID
Payables Payments - Payments Real Time	AP_CHECKS_ALL, AP_INVOICE_PAYMENTS_ALL	CHECK_ID
Receivables - Transactions Real Time	RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL	CUSTOMER_TRX_ID
Fixed Assets - Asset Transactions Real Time	FA_ADDITIONS_B, FA_BOOKS, FA_DEPRN_SUMMARY	ASSET_ID

Procurement Subject Areas

OTBI Subject Area	Primary SQL Tables	Key Join Column
Purchasing - Purchase Orders Real Time	PO_HEADERS_ALL, PO_LINES_ALL	PO_HEADER_ID
Purchasing - Receipts Real Time	RCV_SHIPMENT_HEADERS, RCV_TRANSACTIONS	SHIPMENT_HEADER_ID
Purchasing - Suppliers Real Time	AP_SUPPLIERS / POZ_SUPPLIERS, AP_SUPPLIER_SITES_ALL / POZ_SUPPLIER_SITES_ALL_M	VENDOR_ID
Spend - Spend Transactions Real Time	PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL	PO_DISTRIBUTION_ID

How OTBI Abstracts SQL Joins

When you drag a field from the Workforce Management - Worker Assignment Real Time subject area in OTBI, the underlying engine executes a join similar to this:

SELECT
    n.display_name,
    p.person_number,
    a.assignment_number,
    a.job_id,
    a.location_id,
    a.effective_start_date,
    a.effective_end_date
FROM
    per_all_people_f p
    JOIN per_all_assignments_m a
        ON  a.person_id = p.person_id
        AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
        AND a.assignment_type = 'E'
        AND a.primary_flag    = 'Y'
    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
WHERE
    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date

OTBI handles the effective-date filters automatically. In direct SQL, you must add these predicates manually - otherwise you get duplicate rows for each date range.

Key Things SQL Developers Need to Know

_F suffix = Date-Effective Tables

Tables ending in _F are effective-dated. They contain EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Always add a BETWEEN predicate or you will see one row per date version.

_M suffix = Versioned Tables

Tables ending in _M also follow date-effectivity patterns, similar to _F tables. PER_ALL_ASSIGNMENTS_M is the most common example in HCM.

_ALL suffix = Cross-Org Tables

Many _ALL tables contain data across multiple business units, organizations, or ledgers - but the required filter differs by module. Depending on the table, you may need ORG_ID, BUSINESS_UNIT_ID, LEDGER_ID, PRC_BU_ID, or security predicates applied by the environment. Note that some tables like PER_ALL_PEOPLE_F and GL_JE_HEADERS carry _ALL in their name but behave differently from Payables or Procurement _ALL tables - always check the module-specific filter requirements.

Why SQL Results Don't Match OTBI

This is one of the most common problems Oracle Fusion SQL developers encounter: a SQL query and an OTBI report using what appear to be the same fields return different row counts or totals. Understanding why this happens is as important as knowing which tables to join.

OTBI automatically applies security predicates

OTBI enforces Oracle Fusion data security rules - HCM Security Profiles, Data Access Sets, Procurement Business Units - automatically. Direct SQL bypasses these unless you add the same filters manually. A SQL query against PER_ALL_ASSIGNMENTS_M with no security predicates will return more rows than the OTBI subject area.

OTBI automatically filters effective-dated records

When you use a date-effective field in an OTBI report, the subject area logic applies the effective date filter for you. In direct SQL, omitting TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date on every effective-dated table produces row multiplication - often silently.

OTBI may aggregate data at a different grain

OTBI subject areas sometimes pre-aggregate or deduplicate at the logical layer. A direct SQL query at the base table grain may return more rows than OTBI because OTBI has already collapsed them through its logical model before rendering results.

OTBI uses logical calculations not visible in base tables

Some OTBI measures are derived through the RPD logical layer - combining columns, applying currency conversion, or referencing lookup values - in ways that have no direct equivalent in a single base table column. Reproducing these in SQL often requires additional joins or CASE expressions.

OTBI subject areas often source from secured views, not base tables

Between an OTBI column and the physical database there are typically multiple layers: View Objects, secured views, and the BI RPD physical layer. The base tables listed in this reference are what SQL developers query directly - they are not necessarily the exact objects OTBI itself reads from.

Explore OTBI-to-SQL Mappings Faster with FusionLens SQL

🔍

Schema Navigator

Browse Oracle Fusion tables by module and see which columns map to OTBI subject area fields - without guessing.

📤

One-Click Export

Export SQL results for side-by-side validation against OTBI subject area outputs - no manual copy-paste.

🕐

SQL History

Save and compare SQL equivalents as you build your OTBI-to-SQL mapping reference - no rebuilding queries from scratch.

Try FusionLens SQL Free

OTBI vs BI Publisher vs Direct SQL

These three tools serve different purposes in Oracle Fusion reporting. Understanding which layer each one operates at explains why they produce different results - and why knowing the underlying tables matters most for SQL developers and BI Publisher authors.

Tool	Uses OTBI Subject Areas	Queries Base Tables Directly	Security Applied By
OTBI	Yes	No - reads via RPD logical layer	Automatically (via subject area)
BI Publisher Data Model	No	Yes	Developer must add manually
Direct SQL / FusionLens SQL	No	Yes	Developer must add manually

This is why OTBI and a SQL query against the same tables can return different row counts: OTBI automatically enforces effective date filters, security profiles, and logical aggregations - SQL does none of this unless you write it explicitly.

Final Thoughts

OTBI subject areas are a useful abstraction for report consumers - but SQL developers who understand the underlying table relationships can go further: writing custom joins, adding calculated columns, handling effective dates correctly, and building BI Publisher data models that OTBI alone cannot produce.

Use this reference as a starting point when translating OTBI analysis designs into direct SQL. Always account for security predicates, effective-date filters, and logical aggregations that OTBI applies automatically but SQL does not.