Oracle Fusion SLA Tables with SQL Examples
🔍 Introduction
Oracle Subledger Accounting (SLA) is the rules-based engine that generates accounting entries for transactions from subledgers such as Payables, Receivables, and Assets. It provides a unified accounting model that feeds into the General Ledger (GL).
This post explores key SLA tables, explains how they interrelate, and provides practical SQL queries for reconciliation, reporting, and debugging.
📂 Key SLA Tables Overview
| Table Name | Description |
|---|---|
XLA_EVENTS |
Stores business events triggered in subledgers |
XLA_AE_HEADERS |
Header-level journal entry info |
XLA_AE_LINES |
Line-level accounting entries |
XLA_DISTRIBUTION_LINKS |
Maps SLA lines to source distributions like invoice lines |
XLA_TRANSACTION_ENTITIES |
Information about subledger transactions |
XLA_SUBLEDGER_SOURCES |
Defines source systems like Payables, AR, Assets |
XLA_LEDGER_OPTIONS |
Stores ledger-specific SLA options and setups |
🔗 How SLA Tables Are Related
- Transaction Entity: Stored in
XLA_TRANSACTION_ENTITIES - Event: Created in
XLA_EVENTS - Header & Line Entries:
XLA_AE_HEADERSandXLA_AE_LINES - Distribution Mapping: via
XLA_DISTRIBUTION_LINKS
🧪 Sample SQL Queries
1. 📘 SLA Journal Entries for a Payables Invoice
SELECT xah.ae_header_id,
xal.ae_line_num,
xal.accounted_cr,
xal.accounted_dr,
xal.code_combination_id,
gcc.segment1 AS company,
gcc.segment2 AS account,
xah.accounting_date,
xah.description
FROM xla_ae_headers xah
JOIN xla_ae_lines xal
ON xah.ae_header_id = xal.ae_header_id
JOIN gl_code_combinations gcc
ON xal.code_combination_id = gcc.code_combination_id
WHERE xah.entity_id IN (
SELECT xte.entity_id
FROM xla_transaction_entities xte
WHERE xte.source_id_int_1 = :invoice_id
AND xte.application_id = 200
)
AND xal.accounting_class_code IS NOT NULL;
2. 🔄 Distribution Line Mapping
SELECT xdl.source_distribution_type,
xdl.source_distribution_id_num_1,
xal.accounted_dr,
xal.accounted_cr,
xal.code_combination_id
FROM xla_distribution_links xdl
JOIN xla_ae_lines xal
ON xdl.ae_line_id = xal.ae_line_id
WHERE xdl.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND xdl.source_distribution_id_num_1 = :invoice_distribution_id;
🛠️ Use Cases for These Queries
- Trace invoice entries to General Ledger
- Reconcile SLA data with GL balances
- Debug incorrect journal entries or posting failures
- Build reports using BI Publisher or OTBI
📚 Conclusion
Oracle Fusion Subledger Accounting forms the foundation for robust financial tracking across subledgers. By understanding the XLA table structure and using SQL efficiently, technical users can generate powerful audit trails, improve reconciliation, and build end-to-end financial insights.
