Results for “ar_ael_gl_inv_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AR_AEL_GL_INV_V is a Receivables (AR) subledger view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes invoice-related accounting distributions in a flattened, general-ledger-ready format, serving as the AR counterpart to the Accounting Events / Subledger Accounting extraction model that feeds the Create Accounting process, the GL Subledger Journal Import, and reconciliation reports such as the AR to GL Reconciliation. The view consolidates transaction header attributes, distribution lines, general ledger journal headers, conversion metadata, and third-party (customer) context into a single denormalized row per distribution line. Because it carries both entered and accounted debit/credit amounts alongside journal header references, it is widely used for period-end reconciliation, audit trails, and custom AEL-style reporting where a direct join to AR and GL base tables would otherwise be required.

Underlying Base Objects

ETRM documents AR_AEL_GL_INV_V as being defined over the following base objects (or their APPS synonyms): AR_DISTRIBUTIONS_ALL, AR_LOOKUPS, AR_RECEIVABLE_APPLICATIONS_ALL, AR_VAT_TAX_ALL, FND_DOCUMENT_SEQUENCES, GL_DAILY_CONVERSION_TYPES, GL_IMPORT_REFERENCES, GL_JE_HEADERS, HZ_CUST_ACCOUNTS, HZ_CUST_SITE_USES_ALL, HZ_PARTIES, MTL_UNITS_OF_MEASURE, RA_BATCH_SOURCES_ALL, RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL, RA_RULES, and RA_SALESREPS_ALL.

The primary driving tables are RA_CUSTOMER_TRX_ALL (invoice header), RA_CUST_TRX_LINE_GL_DIST_ALL (distribution lines), and GL_JE_HEADERS (accounting journal header). Supporting joins resolve lookup meanings from AR_LOOKUPS, customer identity from HZ_CUST_ACCOUNTS / HZ_PARTIES, document sequence data from FND_DOCUMENT_SEQUENCES, and currency conversion details from GL_DAILY_CONVERSION_TYPES. The view therefore sits at the intersection of Receivables transaction data and the General Ledger journal representation of those transactions.

Key Columns

• JE_HEADER_ID, JE_LINE_NUM — Reference to the GL_JE_HEADERS row and line number associated with the event.
• APPLICATION_ID — Fixed value 222 for Receivables, identifying the subledger source.
• SET_OF_BOOKS_ID, ORG_ID — Ledger and operating unit context.
• TRX_CLASS, TRX_TYPE_N, TRX_TYPE_NAME — Transaction class code, type identifier, and descriptive name.
• TRX_NUMBER, TRX_NUMBER_DISPLAYED, TRX_DATE — Invoice number and transaction date.
• DOC_SEQUENCE_ID, DOC_SEQUENCE_NAME, DOC_SEQUENCE_VALUE — Document sequence attribution for audit.
• ACCT_LINE_TYPE, ACCT_LINE_TYPE_NAME — The distribution account class (for example, REC, TAX, FREIGHT) and its meaning.
• CODE_COMBINATION_ID — The GL accounting flexfield account for the distribution line.
• CURRENCY_CODE — Invoice currency.
• ENTERED_DR / ENTERED_CR — Entered debit and credit amounts derived by decoding ACCOUNT_CLASS and the sign of AMOUNT.
• ACCOUNTED_DR / ACCOUNTED_CR — Accounted debit and credit amounts using ACCTD_AMOUNT.
• CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_RATE — Rate information applied to the transaction.
• THIRD_PARTY_TYPE, THIRD_PARTY_ID — Customer identifier context.

Common Use Cases and Queries

Typical scenarios include reconciling AR invoice distributions to GL balances for a given ledger and period, tracing a specific invoice number to its accounting entries, and exporting entered versus accounted amounts for audit support. A basic reconciliation query follows:

• SELECT trx_number, trx_date, acct_line_type_name, entered_dr, entered_cr, accounted_dr, accounted_cr FROM ar_ael_gl_inv_v WHERE set_of_books_id = :ledger AND trx_date BETWEEN :start_date AND :end_date;
• To isolate a single invoice: SELECT trx_number, je_header_id, je_line_num, code_combination_id, entered_dr, entered_cr FROM ar_ael_gl_inv_v WHERE trx_number = :invoice_number;
• To reconcile distributions to GL by account: SELECT code_combination_id, SUM(accounted_dr) dr, SUM(accounted_cr) cr FROM ar_ael_gl_inv_v WHERE org_id = :org_id AND trx_date BETWEEN :p_start AND :p_end GROUP BY code_combination_id;

Because the view spans numerous large base tables, filters on ORG_ID, SET_OF_BOOKS_ID, and TRX_DATE should be applied to reduce the execution cost of these queries.