Oracle Financials Cloud OTBI Subject Area Documentation

What is OTBI?

Oracle Transactional Business Intelligence (OTBI) is a realtime analytics solution embedded directly into Oracle Cloud applications. OTBI enables business users to create, view, and share analytical reports without needing a separate data warehouse. In the context of Oracle Financials Cloud, OTBI provides a set of prebuilt subject areas that expose the transactional data model of the financial suite.

Each subject area represents a logical grouping of tables, columns, and relationships that reflect a particular business process such as General Ledger, Accounts Payable, or Cash Management. The documentation that accompanies these subject areas explains the purpose of each field, its data type, any pertinent security considerations, and the recommended usage patterns for report authors.

Core Financial Subject Areas

The following table lists the most frequently used OTBI subject areas for Oracle Financials Cloud. Click a subject area name to view its detailed documentation.

GL General Ledger

The GL subject area includes all ledger entries from the journal entry tables, along with supplemental dimensions such as accounting codes, cost centers, and segment values. Key columns include JournalEntryId, LedgerYear, LedgerPeriod, DebitAmount, CreditAmount, and BalancingSegment. The documentation details the relationship between ledger entries and the underlying chart of accounts, as well as the security filter that restricts users to the ledgers they are authorized to view.

AP Accounts Payable

AP subject area surfaces vendor invoices, credit memos, payment batches, and related approval data. Important columns are InvoiceNumber, SupplierName, InvoiceAmount, PaymentStatus, and DueDate. The documentation explains the status flow (e.g., Validated Approved Paid) and provides guidance on handling multicurrency invoices.

AR Accounts Receivable

AR captures customer invoices, receipts, and adjustments. Primary fields include CustomerNumber, ReceiptNumber, InvoiceAmount, OutstandingAmount, and DunningLevel. Notable notes in the documentation cover the impact of cash applications on aging buckets and the role of the Revenue Recognition flag in the context of the new revenue rules.

CA Cash Management

Cash Management provides a view of bank accounts, bank statements, and cash forecasts. Columns such as BankAccountId, StatementDate, OpeningBalance, ClosingBalance, and UnreconciledAmount are documented with examples of reconciliation logic. The documentation also lists the predefined Cash Position KPI that can be used directly in OTBI dashboards.

FP Fixed Assets

The Fixed Assets subject area includes asset master data, depreciation runs, and disposal events. Key fields are AssetNumber, AssetCategory, AcquisitionCost, AccumulatedDepreciation, and NetBookValue. The documentation points out the bookvstax distinction and explains the effect of the Deferred Tax component on reporting.

PM Project Management

Project Management ties financial transactions to project structures. Important columns are ProjectId, TaskId, PlannedCost, ActualCost, and EarnedValue. The documentation discusses the use of Revenue Recognition Method and how projected cash flows are calculated based on the Milestone schedule.

PS Purchasing

Purchasing provides visibility into purchase orders, receipts, and supplier contracts. Essential columns include PurchaseOrderNumber, ItemDescription, OrderedQuantity, ReceivedQuantity, and UnitPrice. Documentation notes include the Buyer Group security filter and the process for handling Split Receipts across multiple lines.

Navigating the Documentation

The OTBI subject area documentation is accessible via the Help menu in the Financials Cloud application, or directly through the Oracle Help Center. Each subject area is organized into three parts:

• Overview Highlevel description of the business purpose.
• Data Model Diagram of tables, key relationships, and column definitions.
• Usage Guidance Tips on building reports, applying security filters, and common pitfalls.

For developers, the SQL> view inside the Data Model tab provides the exact column names that can be used in the OTBI expression editor. When working with multicurrency environments, the documentation highlights the Currency Conversion field that must be selected to ensure amounts are expressed in a consistent reporting currency.

Common Reporting Queries

Below are a few frequently requested reports with the corresponding subject area and a short query example. The example uses the OTBI expression syntax (pseudoSQL) for illustration.

1. YeartoDate (YTD) Trial Balance

SELECT    LedgerYear,    LedgerPeriod,    AccountNumber,    SUM(DebitAmount) - SUM(CreditAmount) AS BalanceFROM GL_JournalEntriesWHERE    LedgerYear = TO_NUMBER(:CurrentYear)GROUP BY LedgerYear, LedgerPeriod, AccountNumberORDER BY AccountNumber        

2. Vendor Invoice Aging (AP)

SELECT    SupplierName,    InvoiceNumber,    InvoiceAmount,    DueDate,    CURRENT_DATE - DueDate AS DaysPastDueFROM AP_InvoicesWHERE    PaymentStatus = 'Open'    AND CURRENT_DATE - DueDate > 0ORDER BY DaysPastDue DESC        

3. Cash Position Summary (CA)

SELECT    BankAccountId,    SUM(OpeningBalance) AS OpeningBalance,    SUM(ClosingBalance) AS ClosingBalance,    SUM(UnreconciledAmount) AS UnreconciledFROM CA_BankStatementsWHERE StatementDate BETWEEN :StartDate AND :EndDateGROUP BY BankAccountId        

All of the fields used in the examples are documented in the respective subject area pages, including the data type, whether the column is a filterable attribute, and any required joins that the OTBI engine automatically creates.

Best Practices for OTBI Report Development

• Use PreBuilt Measures First OTBI provides a library of standard measures (e.g., Sum(Amount), Count(Distinct InvoiceNumber)) that are optimized for performance. Custom calculations should be added only when necessary.
• Apply Security Filters Early The documentation outlines the default data access filters for each subject area. Adding them to the query reduces the amount of data transferred and prevents accidental exposure of restricted rows.
• Leverage the Currency Conversion Attribute For multinational reports, always select the ConvertedAmount field and specify the ReportingCurrency parameter to get consistent results.
• Minimize Joins Across Subject Areas While OTBI can automatically join tables across subject areas, each additional join can increase query latency. Where possible, rely on a single subject area that already contains the needed attributes.
• Document Your Report Logic Include a description field in the report definition that references the relevant OTBI documentation page. This makes maintenance easier for future analysts.

Additional Resources

Beyond the builtin documentation, the following resources can help you master OTBI for Oracle Financials Cloud:

• Oracle Learning Library OTBI Learning Paths
• Oracle Community Finance Cloud Forums
• Financials Cloud API and Integration Guide
• Webinars hosted by Oracle Application Cloud experts focus on report performance tuning and security segregation.

Staying uptodate with new releases is crucial, as Oracle frequently adds new subject areas or augments existing ones. Subscribing to the Release Updates email will ensure you receive the latest schema changes and documentation revisions.