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

When you rebuild an Oracle Fusion OTBI analysis in BI Publisher physical SQL, the first question is usually: which tables sit behind this subject area? That is a useful question, but it needs one important qualification. An OTBI subject area is not a database synonym and it rarely maps one-to-one to a single Fusion table.

The most reliable way to translate an OTBI analysis into SQL is to start with the subject area's documented transactional grain, then identify the physical tables needed for the attributes you selected. That distinction matters: a subject area may expose header, line, schedule, distribution, accounting, and dimension attributes while its measurable fact sits at only one of those grains.

How to Read the Mapping Tables

HCM Subject Areas

Finance Subject Areas

Procurement and Receiving Subject Areas

A Worker Assignment SQL Reconstruction Example

The SQL below is a practical physical-SQL reconstruction of the core current-assignment grain. It is not a claim that OTBI generates this SQL internally, and it intentionally does not force PRIMARY_FLAG='Y' because the Worker Assignment Real Time subject area can return multiple worker assignments.

SELECT
    p.person_number,
    n.display_name,
    a.assignment_number,
    a.assignment_type,
    a.assignment_status_type,
    a.business_unit_id,
    a.organization_id,
    a.position_id,
    a.job_id,
    a.location_id
FROM per_all_assignments_m a
JOIN per_all_people_f p
  ON p.person_id = a.person_id
 AND :p_as_of_date BETWEEN p.effective_start_date
                       AND p.effective_end_date
LEFT JOIN per_person_names_f n
  ON n.person_id = a.person_id
 AND n.name_type = 'GLOBAL'
 AND :p_as_of_date BETWEEN n.effective_start_date
                       AND n.effective_end_date
WHERE :p_as_of_date BETWEEN a.effective_start_date
                        AND a.effective_end_date
  AND a.effective_latest_change = 'Y'
  AND a.assignment_type IN ('E', 'C', 'N', 'P');

Oracle documents those assignment-type and latest-change filters for the subject area. If your business report needs only the primary employee assignment, that is an additional business filter — for example ASSIGNMENT_TYPE='E' and PRIMARY_FLAG='Y' — not a generic OTBI mapping rule.

What the Common Fusion Suffixes Actually Tell You

Why Physical SQL Doesn't Match OTBI

OTBI vs BI Publisher Physical SQL

A Practical OTBI-to-SQL Validation Workflow

1. Identify the exact subject area name. Similar-sounding names often represent different grains and business models.
2. Read Oracle's Transactional Grain and Time Reporting sections. These are more reliable than guessing from presentation-folder names.
3. Identify the lowest fact grain used by your analysis. Header, line, schedule, distribution, payment distribution, and accounting distribution are not interchangeable.
4. Build the physical SQL at that grain first. Add descriptive dimensions after the base row count is understood.
5. Reproduce the as-of-date logic. For _M HCM tables, consider latest-change/sequence semantics as well as effective dates.
6. Match security context intentionally. Don't use raw-table row counts as a security-equivalent benchmark against OTBI.
7. Check non-table/reporting sources. Essbase cubes and reporting extract tables can make a direct table comparison invalid.
8. Compare totals and row counts at the same grain. A mismatch after adding a one-to-many join may be correct multiplication, not an OTBI defect.

Technical References

• Oracle HCM: Workforce Management - Worker Assignment Real Time
• Oracle HCM: Payroll - Payroll Run Results Real Time
• Oracle Financials: General Ledger - Journals Real Time
• Oracle Financials: General Ledger - Balances Real Time
• Oracle Financials: Payables Invoices - Transactions Real Time
• Oracle Procurement: Procurement - Purchasing Real Time
• Oracle Procurement: Descriptive Flexfields for Business Intelligence
• Oracle HCM: BI Publisher Secured List Views

Final Thoughts

The useful skill is not memorizing that one OTBI subject area “equals” one table. It is learning to identify the subject area's business grain, time behavior, security context, and reporting source — then building physical SQL that reproduces those rules deliberately.

Once you approach OTBI this way, mismatched row counts become much easier to diagnose. You can tell whether the difference comes from a wrong join, a date-effective row, a distribution-level fact, security, an extract process, a cube-backed measure, or simply a different reporting grain.