Payables to Ledger Reconciliation Report
The Payables to Ledger Reconciliation Report reconciles the accounts-payable subledger to the general ledger liability balance, listing the documents that explain any difference — unaccounted invoices, entries transferred but not posted, and manual journals hitting the liability account. It is the report finance teams fight with most at close.
The question it answers
Does the AP subledger tie to the GL liability account, and if not, which documents explain the gap?
Run note · BIP run High-volume GL extracts can exceed BI Publisher's online output limit and time out. Run it as a scheduled process (ESS) with output bursted to file or email rather than online preview, and bound it by ledger and period.
Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.
Sample build of the Payables to Ledger Reconciliation Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Document | Supplier | Subledger Amount | Accounted Amount | Gl Amount | Difference |
|---|---|---|---|---|---|
| Sample | Acme Industrial | $1,240,500.00 | $1,240,500.00 | $1,240,500.00 | Sample |
| — | Northwind Trading | $842,150.75 | $842,150.75 | $842,150.75 | — |
| Sample | Globex Holdings | $96,400.00 | $96,400.00 | $96,400.00 | Sample |
| — | Initech LLC | $1,005,233.10 | $1,005,233.10 | $1,005,233.10 | — |
| Sample | Umbrella Corp | $58,720.40 | $58,720.40 | $58,720.40 | Sample |
| Sample | Acme Industrial | $1,240,500.00 | $1,240,500.00 | $1,240,500.00 | Sample |
The report stacks the AP subledger balance against the GL liability balance and lists every document that breaks the tie. A clean close shows zero in the Difference column.
Out of balance by $42,196 across 9 invoices — accounted in the subledger but their journals were transferred and not yet posted to GL, the classic close blocker.
Traced from AP_INVOICE_DISTRIBUTIONS_ALL through XLA_AE_HEADERS to GL_BALANCES: post the open transfer batch and re-run Create Accounting; the variance clears.
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
| Element | Type | Definition |
|---|---|---|
| AP_INVOICE_DISTRIBUTIONS_ALL | dimension | dimension |
| XLA_AE_HEADERS | dimension | dimension |
| GL_BALANCES | dimension | dimension |
| GL_CODE_COMBINATIONS | dimension | dimension |
| Subledger Amount | measure | measure |
| Accounted Amount | measure | measure |
| Gl Amount | measure | measure |
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| AP_INVOICES_ALL | 63 | 15 |
| AP_INVOICE_DISTRIBUTIONS_ALL | 59 | 11 |
| XLA_AE_HEADERS | 16 | 19 |
| GL_BALANCES | Setup / configuration table — joined for reference, not exposed for analytics | |
| GL_CODE_COMBINATIONS | 7 | 61 |