Tuesday, 15 July 2025

Oracle Fusion Sub Ledger Accounting Tables with Sample SQL Queries

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_HEADERS and XLA_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.


Posted by at