OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Finance Tables Reference

Writing a finance report or reconciliation in EBS starts with knowing which table holds what. This list maps the core GL, AP, AR, FA, SLA and PO tables to what they store and the columns you join on.

Download:

Table	Module	What it holds	Key columns to join on
GL_LEDGERS	GL	One row per ledger with currency, calendar and chart of accounts.	LEDGER_ID, CURRENCY_CODE, CHART_OF_ACCOUNTS_ID, PERIOD_SET_NAME
GL_CODE_COMBINATIONS	GL	Every account combination of the accounting flexfield.	CODE_COMBINATION_ID, CHART_OF_ACCOUNTS_ID, SEGMENT1 to SEGMENT30
GL_PERIODS	GL	Accounting calendar periods.	PERIOD_SET_NAME, PERIOD_NAME, START_DATE, END_DATE
GL_PERIOD_STATUSES	GL	Open or closed status of each period per application and ledger.	APPLICATION_ID, LEDGER_ID, PERIOD_NAME, CLOSING_STATUS
GL_JE_BATCHES	GL	Journal batches and their posting status.	JE_BATCH_ID, NAME, STATUS
GL_JE_HEADERS	GL	Journal entry headers: source, category, period, status.	JE_HEADER_ID, JE_BATCH_ID, LEDGER_ID, PERIOD_NAME, JE_SOURCE, JE_CATEGORY, STATUS
GL_JE_LINES	GL	Journal lines with debit and credit amounts.	JE_HEADER_ID, JE_LINE_NUM, CODE_COMBINATION_ID, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR
GL_BALANCES	GL	Period balances by ledger, account, currency and balance type.	LEDGER_ID, CODE_COMBINATION_ID, CURRENCY_CODE, PERIOD_NAME, ACTUAL_FLAG
GL_INTERFACE	GL	Staging table for Journal Import.	STATUS, LEDGER_ID, USER_JE_SOURCE_NAME, ACCOUNTING_DATE, GROUP_ID
GL_DAILY_RATES	GL	Daily currency conversion rates.	FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE, CONVERSION_RATE
GL_JE_SOURCES_TL	GL	Journal source names and keys.	JE_SOURCE_NAME, USER_JE_SOURCE_NAME, LANGUAGE
GL_JE_CATEGORIES_TL	GL	Journal category names and keys.	JE_CATEGORY_NAME, USER_JE_CATEGORY_NAME, LANGUAGE
AP_SUPPLIERS	AP	Supplier header records.	VENDOR_ID, VENDOR_NAME, SEGMENT1 (supplier number)
AP_SUPPLIER_SITES_ALL	AP	Supplier sites per operating unit.	VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE, ORG_ID
AP_INVOICES_ALL	AP	Invoice headers.	INVOICE_ID, VENDOR_ID, INVOICE_NUM, INVOICE_AMOUNT, ORG_ID
AP_INVOICE_LINES_ALL	AP	Invoice lines.	INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE
AP_INVOICE_DISTRIBUTIONS_ALL	AP	Accounting distributions for invoice lines.	INVOICE_DISTRIBUTION_ID, INVOICE_ID, DIST_CODE_COMBINATION_ID
AP_PAYMENT_SCHEDULES_ALL	AP	Due dates and amounts remaining per invoice.	INVOICE_ID, PAYMENT_NUM, DUE_DATE, AMOUNT_REMAINING
AP_HOLDS_ALL	AP	Holds placed on invoices and their release.	INVOICE_ID, HOLD_LOOKUP_CODE, RELEASE_LOOKUP_CODE
AP_CHECKS_ALL	AP	Payments issued to suppliers.	CHECK_ID, CHECK_NUMBER, VENDOR_ID, AMOUNT
AP_INVOICE_PAYMENTS_ALL	AP	Link between invoices and payments.	INVOICE_ID, CHECK_ID
AP_INVOICES_INTERFACE	AP	Staging table for Payables Open Interface Import (invoice headers).	INVOICE_ID, INVOICE_NUM, VENDOR_NUM, SOURCE
AP_INVOICE_LINES_INTERFACE	AP	Staging table for invoice lines in the Open Interface.	INVOICE_ID, INVOICE_LINE_ID, AMOUNT
AP_INTERFACE_REJECTIONS	AP	Reasons records were rejected during Open Interface Import.	PARENT_TABLE, PARENT_ID, REJECT_LOOKUP_CODE
RA_CUSTOMER_TRX_ALL	AR	Receivables transaction headers: invoices, credit memos, debit memos.	CUSTOMER_TRX_ID, TRX_NUMBER, BILL_TO_CUSTOMER_ID, ORG_ID
RA_CUSTOMER_TRX_LINES_ALL	AR	Transaction lines.	CUSTOMER_TRX_LINE_ID, CUSTOMER_TRX_ID, LINE_TYPE
RA_CUST_TRX_LINE_GL_DIST_ALL	AR	Accounting distributions for AR transactions.	CUST_TRX_LINE_GL_DIST_ID, CUSTOMER_TRX_ID, CODE_COMBINATION_ID
AR_PAYMENT_SCHEDULES_ALL	AR	Open items and amounts due remaining.	PAYMENT_SCHEDULE_ID, CUSTOMER_TRX_ID, AMOUNT_DUE_REMAINING, DUE_DATE
AR_CASH_RECEIPTS_ALL	AR	Customer receipts.	CASH_RECEIPT_ID, RECEIPT_NUMBER, AMOUNT
AR_RECEIVABLE_APPLICATIONS_ALL	AR	Applications of receipts to transactions.	RECEIVABLE_APPLICATION_ID, CASH_RECEIPT_ID, APPLIED_CUSTOMER_TRX_ID
RA_INTERFACE_LINES_ALL	AR	Staging table for AutoInvoice.	INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, BATCH_SOURCE_NAME
HZ_PARTIES	TCA	Trading Community parties (customers, suppliers, people).	PARTY_ID, PARTY_NAME, PARTY_TYPE
HZ_CUST_ACCOUNTS	TCA	Customer accounts linked to parties.	CUST_ACCOUNT_ID, PARTY_ID, ACCOUNT_NUMBER
FA_ADDITIONS_B	FA	Asset master data.	ASSET_ID, ASSET_NUMBER, ASSET_CATEGORY_ID
FA_BOOKS	FA	Asset cost and depreciation attributes per book.	BOOK_TYPE_CODE, ASSET_ID, COST, DATE_PLACED_IN_SERVICE
FA_DEPRN_SUMMARY	FA	Depreciation totals per asset, book and period.	BOOK_TYPE_CODE, ASSET_ID, PERIOD_COUNTER
XLA_TRANSACTION_ENTITIES	SLA	Subledger transactions that have accounting.	ENTITY_ID, APPLICATION_ID, LEDGER_ID, SOURCE_ID_INT_1
XLA_AE_HEADERS	SLA	Subledger accounting entry headers and GL transfer status.	AE_HEADER_ID, APPLICATION_ID, LEDGER_ID, PERIOD_NAME, GL_TRANSFER_STATUS_CODE
XLA_AE_LINES	SLA	Subledger accounting entry lines.	AE_HEADER_ID, AE_LINE_NUM, CODE_COMBINATION_ID, ACCOUNTED_DR, ACCOUNTED_CR
PO_HEADERS_ALL	PO	Purchase order headers.	PO_HEADER_ID, SEGMENT1 (PO number), VENDOR_ID, ORG_ID
PO_LINES_ALL	PO	Purchase order lines.	PO_LINE_ID, PO_HEADER_ID, LINE_NUM, ITEM_ID
PO_DISTRIBUTIONS_ALL	PO	PO accounting distributions.	PO_DISTRIBUTION_ID, PO_HEADER_ID, CODE_COMBINATION_ID
RCV_TRANSACTIONS	PO	Receiving transactions against POs.	TRANSACTION_ID, PO_HEADER_ID, TRANSACTION_TYPE

Columns listed are the ones most reports join on, not the full column list. Release 12 tables ending in _ALL hold data for every operating unit, so filter on ORG_ID or query the MO-secured synonym without _ALL after setting the MO context. Confirm against the Oracle eTRM for your exact release.

Ask your EBS finance data in plain English

See how a read-only assistant answers questions from these tables, with the SQL shown, on your own servers.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the table

Search by module code (GL, AP, AR) to see one subledger at a time, or by a word such as 'interface' to find staging tables. Use the key columns as your join path. A typical trace starts at GL_JE_LINES, joins to GL_JE_HEADERS on JE_HEADER_ID, then follows the subledger link through GL_IMPORT_REFERENCES to XLA_AE_LINES and the source transaction.

Filter by module to focus a reconciliation
Look for 'interface' to find staging and rejection tables
Export to Excel as a data dictionary for your team

The Release 12 data flow

In Release 12, subledgers no longer post straight to GL. Payables, Receivables, Fixed Assets and Purchasing create accounting through Subledger Accounting, stored in XLA_AE_HEADERS and XLA_AE_LINES. The Transfer to GL step moves those entries into GL_INTERFACE, and Journal Import turns them into journals in GL_JE_HEADERS and GL_JE_LINES. GL_TRANSFER_STATUS_CODE on XLA_AE_HEADERS tells you whether an entry has reached the GL.

Multi-org and _ALL tables

Tables ending in _ALL hold rows for all operating units. A query against AP_INVOICES_ALL without an ORG_ID filter returns every operating unit, which is a common reason finance totals do not match a screen. Either filter on ORG_ID, or initialize the apps context with a responsibility and use the secured synonym AP_INVOICES, which applies the MO policy for you.

Common mistakes

Direct updates to these tables are unsupported and can corrupt accounting. Use them for reading and for reconciliations, and use the public APIs and open interfaces to change data. Another frequent issue is joining GL_BALANCES without the ACTUAL_FLAG and currency filters, which mixes budgets, encumbrances and foreign currency amounts.

Frequently Asked Questions

Which table holds journal lines in Oracle EBS?

GL_JE_LINES holds journal lines. Join to GL_JE_HEADERS on JE_HEADER_ID for period, source and status, and to GL_CODE_COMBINATIONS on CODE_COMBINATION_ID for the account segments.

What is the difference between AP_INVOICES_ALL and AP_INVOICES?

AP_INVOICES_ALL is the base table with data for all operating units. AP_INVOICES is a synonym or view that applies multi-org security for the current context. Use the _ALL table with an ORG_ID filter when running outside an initialized session.

How do I know whether a subledger entry reached the GL?

Check GL_TRANSFER_STATUS_CODE on XLA_AE_HEADERS. Entries not yet transferred need the Transfer Journal Entries to GL program. Then confirm Journal Import has created journals from the GL_INTERFACE rows.

Can I update these tables with SQL?

No. Oracle does not support direct updates to base tables, and doing so can break accounting integrity and support. Use the standard forms, open interfaces, and published APIs, and keep SQL for reading and reconciliation.

Netray can put a read-only natural language layer over your EBS finance tables so analysts ask for a trial balance or an unposted journal list without writing SQL.

Talk to an Expert Explore More Free Tools