Search Results ap_ael_sl_inv_v

Overview

AP_AEL_SL_INV_V is a Payables subledger accounting view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that exposes accounted invoice lines together with the descriptive ("displayed") names of their associated lookup codes. It belongs to the family of AP_AEL_SL_*_V views, which present subledger journal entries created by the Subledger Accounting (SLA) engine. This view specifically covers transactions of class 'INV' (Invoices) originating from the Payables application.

The view's role is to resolve technical codes — such as the transaction class, transaction type, accounting line type, and GL transfer status — into user-facing text via the DISPLAYED_FIELD column drawn from EBS lookup tables (aliased L1 through L12). This makes the view suitable for reconciliation reports, accounting inquiries, audit extracts, and downstream integrations that require readable SLA accounting data rather than raw lookup codes.

Underlying Base Objects

The ETRM metadata notes that no base objects are formally documented for this view and it is "Not implemented in this database," meaning the definition is version-dependent and may not exist in every environment. The view text, however, reveals the core contributing tables:

• AP_AE_HEADERS (AEH) — provides the accounting header context: SET_OF_BOOKS_ID, ORG_ID, ACCOUNTING_DATE, AE_HEADER_ID, and GL_TRANSFER_FLAG.
• AP_AE_LINES (AEL) — supplies the accounted line details: ACCOUNTED_DR/CR, ENTERED_DR/CR, CODE_COMBINATION_ID, currency conversion columns, AE_LINE_TYPE_CODE, SUBLEDGER_DOC_SEQUENCE_ID, SOURCE_TABLE, SOURCE_ID, and THIRD_PARTY_ID.
• AP_INVOICES (I) — when AEL.SOURCE_TABLE = 'AP_INVOICES', supplies INVOICE_NUM, INVOICE_TYPE_LOOKUP_CODE, and INVOICE_DATE.
• AP_INVOICE_DISTRIBUTIONS (D) — referenced in the AE_LINE_REFERENCE decode for distribution-sourced lines.
• AP_TAX_CODES_ALL (AT), PO_VENDORS (V), and PO_VENDOR_SITES_ALL (VS) — provide tax code, supplier name, supplier number, and supplier site.
• FND_DOCUMENT_SEQUENCES (FD) — supplies DOC_SEQUENCE_NAME.
• Lookup/display tables aliased L1–L12 — joined to translate codes into DISPLAYED_FIELD text.

Key Columns

• TRX_CLASS / TRX_CLASS_NAME — fixed as 'INV' with a displayed name resolved via L8.
• TRX_TYPE_NAME, TRX_TYPE_C, TRX_NUMBER_C, TRX_NUMBER_DISPLAYED — invoice type and number in coded and displayed forms.
• ACCOUNTED_DR / ACCOUNTED_CR, ENTERED_DR / ENTERED_CR — accounted and entered debit/credit amounts.
• ACCT_LINE_TYPE / ACCT_LINE_TYPE_NAME — the accounting line type code and its displayed text (L3).
• AE_LINE_REFERENCE — a DECODE-built composite string referencing the event number, AE line number, and either the invoice number or distribution line number.
• GL_TRANSFER_STATUS / GL_TRANSFER_STATUS_NAME / TRANSFER_STATUS_DETAIL_NAME — GL transfer flag and its displayed descriptions (L4, L9).
• THIRD_PARTY_* — supplier type, ID, number, name, and site.
• CODE_COMBINATION_ID, CURRENCY_CODE, CURRENCY_CONVERSION_RATE/TYPE/DATE — accounting flexfield and currency conversion context.

Common Use Cases and Queries

Typical uses include reconciling Payables SLA entries to GL, auditing accounted invoice lines with readable status text, and feeding downstream reporting.

SELECT TRX_NUMBER_DISPLAYED,
       TRX_TYPE_NAME,
       ACCT_LINE_TYPE_NAME,
       ACCOUNTED_DR,
       ACCOUNTED_CR,
       GL_TRANSFER_STATUS_NAME,
       THIRD_PARTY_NAME
FROM   AP_AEL_SL_INV_V
WHERE  ACCOUNTING_DATE BETWEEN :p_from AND :p_to
  AND  SET_OF_BOOKS_ID = :p_sob;

SELECT AE_LINE_REFERENCE, TRANSFER_STATUS_DETAIL_NAME
FROM   AP_AEL_SL_INV_V
WHERE  GL_TRANSFER_STATUS = 'N';

Because the view is not implemented in every database, availability should be verified against the specific EBS instance before relying on it in custom code.