Search Results manual_adjustment_date

Overview

The AR_LINE_EXCEPTIONS_V view is a Receivables (AR) reporting object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is classified as a complex, non-updatable view whose documented purpose is to hold contingency information for a deferred billing line whose revenue has been adjusted. In practical terms, the view surfaces deferred revenue lines that were manually overridden rather than scheduled automatically, exposing the resulting unearned revenue, recognized revenue, and credit memo balances associated with each adjusted line.

Within the EBS data model, the view functions as a reconciliation and audit aid. Standard deferred accounting schedules generate revenue recognition entries automatically; when a user applies a manual override to a deferred line, the schedule no longer follows the deterministic rule-driven path. AR_LINE_EXCEPTIONS_V isolates precisely those lines, joining the original transaction, the accounting rule, and both the receivables and revenue GL distributions. It is therefore used primarily for analytical reporting, period-end review of manual revenue adjustments, and integration extracts where downstream systems require a consolidated picture of exception lines.

Underlying Base Objects

The view is defined over the following documented base objects:

• AR_DEFERRED_LINES (synonym) — the deferred billing line records, aliased as LRS, holding amount due, the collectible and manual override flags, and the last update date.
• RA_CUSTOMER_TRX_LINES (synonym) — the transaction lines (RCTL) supplying line number, description, and the linked accounting rule.
• RA_CUSTOMER_TRX (synonym) — the transaction header (RCT) providing the transaction number, transaction date, and primary salesperson.
• RA_RULES (synonym) — the revenue recognition rule master (RR), outer-joined to the transaction line.
• RA_CUST_TRX_LINE_GL_DIST (synonym) — used twice, once as GLDIST restricting to the latest receivables distribution and once as GLDIST2 restricting to non-account-set revenue and unearned distributions.
• GL_SETS_OF_BOOKS (view) — the ledger definition supplying set of books name, ID, and currency.
• HZ_CUST_ACCOUNTS and HZ_PARTIES (synonyms) — the customer account number and party name.
• ARP_BAL_UTIL and ARPT_SQL_FUNC_UTIL (packages) — invoked inline to return the line credit memo balance and format the reference number and salesperson name.

The driving predicate is LRS.LINE_COLLECTIBLE_FLAG = 'N' AND LRS.MANUAL_OVERRIDE_FLAG = 'Y', which restricts the result set to non-collectible deferred lines carrying a manual override.

Key Columns

• SET_OF_BOOKS_NAME / SET_OF_BOOKS_ID / CURRENCY_CODE — ledger context for the line.
• CUSTOMER_NAME / CUSTOMER_NUMBER — bill-to party name and account number.
• TRX_NUMBER / TRX_DATE — originating transaction identifier and date.
• REC_GL_DATE / GL_DATE — GL date of the receivables distribution and of the revenue/unearned distribution respectively.
• REFERENCE_NUMBER — the AR reference number derived via ARPT_SQL_FUNC_UTIL.GET_REFERENCE.
• LINE_NUMBER / DESCRIPTION / LINE_AMOUNT — line identity and the original accounted amount due.
• RULE_NAME / SALESREP_NAME — revenue recognition rule and primary salesperson.
• MANUAL_ADJUSTMENT_DATE — the last update date of the deferred line, marking when the manual override occurred.
• UNEARNED_REVENUE / REVENUE / CREDIT_MEMOS — computed measures aggregating unearned and recognized amounts and the line credit memo balance from ARP_BAL_UTIL.GET_LINE_CM.

Common Use Cases and Queries

Typical uses include period-end exception review, deferred revenue reconciliation, and audit justification for manual adjustments. A representative query follows:

• List all manual overrides for a ledger and date range:

  SELECT trx_number, trx_date, customer_name, line_number,
         line_amount, unearned_revenue, revenue, manual_adjustment_date
  FROM   apps.ar_line_exceptions_v
  WHERE  set_of_books_id = :ledger_id
  AND    manual_adjustment_date BETWEEN :start_date AND :end_date;

• Total recognized versus unearned revenue by customer for reconciliation:

  SELECT customer_number, customer_name,
         SUM(revenue) revenue, SUM(uneared_revenue) unearned,
         SUM(credit_memos) credit_memos
  FROM   apps.ar_line_exceptions_v
  GROUP  BY customer_number, customer_name;

• Isolate a rule associated with large exception lines for policy review:

  SELECT rule_name, COUNT(*) lines, SUM(line_amount) amount
  FROM   apps.ar_line_exceptions_v
  WHERE  line_amount > 10000
  GROUP  BY rule_name;

Because the view embeds many aggregate functions (MAX and SUM) and correlated package calls, queries should filter on indexed identifiers such as set_of_books_id, trx_number, or manual_adjustment_date to avoid full scans of the underlying AR deferred lines and GL distributions.