Search Results ap_trial_bal




Overview
Explore in AI Chat

Overview

AP_TRIAL_BAL is a table owned by the AP schema within the Oracle E-Business Suite Payables module. According to the ETRM documentation metadata, its status is VALID in the data dictionary, but its description is explicitly recorded as "No longer used." This designation indicates that the table is a legacy artifact retained for backward compatibility, historical data migration, or schema stability, rather than an active participant in the current Payables processing model. It was originally intended to hold trial balance information — summarised vendor invoice balances by accounting code combination — but its functionality has been superseded by other Payables and General Ledger reporting structures in releases 12.1.1 and 12.2.2.

From a heuristic Data Vault modelling perspective, AP_TRIAL_BAL is best classified as a link. The classification is mined from its foreign key structure: the table sits at the intersection of four independent business entities — vendors (PO_VENDORS), invoices (AP_INVOICES_ALL), sets of books (GL_SETS_OF_BOOKS_11I), and accounting code combinations (GL_CODE_COMBINATIONS). Rather than acting as a descriptive hub or satellite around a single business key, it records the association between these entities along with a small number of numeric measures, which is the defining characteristic of a link construct.

Key Information Stored

The documented physical schema for 12.2.2 lists eight columns. The most significant are as follows:

  • INVOICE_ID — Foreign key to AP_INVOICES_ALL, identifying the invoice to which the trial balance row relates.
  • VENDOR_ID — Foreign key to PO_VENDORS, identifying the supplier associated with the balance.
  • CODE_COMBINATION_ID — Foreign key to GL_CODE_COMBINATIONS, defining the accounting distribution used to accumulate the amount.
  • SET_OF_BOOKS_ID — Foreign key to GL_SETS_OF_BOOKS_11I, anchoring the record to a specific ledger.
  • INVOICE_AMOUNT — The original invoice amount recorded against the distribution.
  • REMAINING_AMOUNT — The unpaid or unapplied balance outstanding for that invoice/distribution combination.
  • ORG_ID — The operating unit identifier, supporting multi-org partitioning.
  • REQUEST_ID — The concurrent request that populated the row, useful for traceability of batch runs.

No surrogate primary key column is documented in the ETRM excerpt; the table appears to rely on the composite of its foreign key columns as an implicit business key. Any unique index definitions would need to be confirmed directly against the dictionary in a live instance.

Common Use Cases and Queries

Because the table is marked "No longer used," it should not be treated as a source of truth for current Payables reporting. Nevertheless, DBAs and migration teams encounter it in the following scenarios:

  • Verifying whether legacy data must be purged or archived before an upgrade from 12.1.1 to 12.2.2.
  • Reconciling historical trial balance figures during a data conversion or audit exercise.
  • Identifying orphaned rows via the foreign key relationships as part of a data cleanup project.

A typical diagnostic query joining the parent tables might take the following form:

SELECT tb.invoice_id, tb.vendor_id, tb.code_combination_id,
       tb.invoice_amount, tb.remaining_amount, tb.org_id
FROM   ap.ap_trial_bal tb
WHERE  tb.set_of_books_id = :p_sob_id
AND    tb.remaining_amount > 0;

For any live reporting requirement, AP_INVOICE_DISTRIBUTIONS_ALL and its associated views should be used instead, as they represent the supported and maintained source of invoice distribution balances.

Related Objects

The following objects are referenced by or depend upon AP_TRIAL_BAL through the documented foreign key relationships:

  • AP_INVOICES_ALL — joined on AP_TRIAL_BAL.INVOICE_ID; the primary source of invoice header data.
  • PO_VENDORS — joined on AP_TRIAL_BAL.VENDOR_ID; supplies supplier detail stored in the legacy PO schema.
  • GL_SETS_OF_BOOKS_11I — joined on AP_TRIAL_BAL.SET_OF_BOOKS_ID; defines the ledger context.
  • GL_CODE_COMBINATIONS — joined on AP_TRIAL_BAL.CODE_COMBINATION_ID; provides account segment values.

These four parent tables collectively define the link semantics of AP_TRIAL_BAL and should be evaluated together when assessing the table's residual value in any historical or migration context.