Search Results amount_adjusted

Overview

The IBE_AR_PAYMENT_SCHEDULES_V view is a reporting object within the Oracle E-Business Suite iStore (IBE) module. It exposes accounts receivable payment schedule data joined with customer transaction header information, presenting installment-level detail alongside invoice-level attributes such as transaction number, transaction date, currency, and shipping method. The view is designed to surface outstanding and settled balances for customer invoices and credit memos, including original freight, tax, and line-item amounts alongside amounts due, applied, credited, and adjusted. The inclusion of the FREIGHT_ORIGINAL column in this view is a notable characteristic, as payment schedule data is commonly queried in receivables and collections reporting to reconcile billed freight against amounts collected. In the ETRM metadata, the view is documented as "Not implemented in this database," indicating that it is a seeded, dictionary-defined object that may not be deployed in every environment, or that it exists as a reference definition used for upgrade and patch lineage rather than a live database object.

Underlying Base Objects

Although the ETRM metadata records no explicitly documented referenced base objects, the embedded view text identifies the source tables without ambiguity. The view is defined over AR_PAYMENT_SCHEDULES (aliased PS), RA_CUSTOMER_TRX (aliased CT), and AR_LOOKUPS (aliased AL_CLASS). The relationship between AR_PAYMENT_SCHEDULES and RA_CUSTOMER_TRX is established on CUSTOMER_TRX_ID, linking each installment to its parent transaction header. The AR_LOOKUPS join is a lookup-type restricted join, with the view enforcing AL_CLASS.LOOKUP_TYPE = 'INV/CM' and PS.CLASS = AL_CLASS.LOOKUP_CODE, so that the transaction class is resolved to its user-facing meaning for invoice and credit memo classes. The view definition applies a GROUP BY over the majority of selected columns, with aggregation applied to DAYS_PAST_DUE (SUM of a DECODE expression) and DUE_DATE (MIN). This aggregation supports one output row per payment schedule record while still reflecting the earliest installment due date and a cumulative days-past-due measure.

Key Columns

• ROW_ID and PAYMENT_SCHEDULE_ID — the row identifier of the transaction record and the primary identifier of the payment schedule installment.
• TRX_NUMBER, TRX_DATE, INVOICE_CURRENCY_CODE — the customer transaction number, transaction date, and invoice currency, sourced from RA_CUSTOMER_TRX.
• AL_CLASS_MEANING — the decoded lookup meaning for the transaction class, distinguishing invoices from credit memos.
• CT_PURCHASE_ORDER, WAYBILL_NUMBER, SHIP_VIA — order and shipment references inherited from the transaction header.
• DAYS_PAST_DUE, DUE_DATE — aggregated aging indicators; DAYS_PAST_DUE uses TRUNC(SYSDATE) - PS.DUE_DATE but returns NULL when AMOUNT_DUE_REMAINING is zero, while DUE_DATE returns the earliest due date in the group.
• AMOUNT_DUE_ORIGINAL, AMOUNT_DUE_REMAINING — the original and outstanding balance amounts on the installment.
• AMOUNT_LINE_ITEMS_ORIGINAL, TAX_ORIGINAL, FREIGHT_ORIGINAL — the original line, tax, and freight components of the installment. FREIGHT_ORIGINAL is particularly relevant for queries reconciling billed freight against collections.
• AMOUNT_ADJUSTED, AMOUNT_APPLIED, AMOUNT_CREDITED, DISCOUNT_TAKEN_EARNED — adjustment, application, credit, and earned discount amounts.
• CUSTOMER_ID, CUSTOMER_SITE_USE_ID, CUSTOMER_TRX_ID, CASH_RECEIPT_ID — customer, site, transaction, and receipt identifiers.
• CLASS, STATUS, RECEIPT_CONFIRMED_FLAG — transaction class, schedule status, and receipt confirmation flag.

Common Use Cases and Queries

This view is most often used in receivables and collections reporting for iStore-managed transactions, particularly where merchants require visibility into unpaid balances, aging, and the composition of an invoice by line-item, tax, and freight amounts. A representative query retrieving freight and outstanding balances for a customer follows:

SELECT TRX_NUMBER, TRX_DATE, INVOICE_CURRENCY_CODE, DUE_DATE, DAYS_PAST_DUE, AMOUNT_DUE_ORIGINAL, FREIGHT_ORIGINAL, AMOUNT_DUE_REMAINING FROM IBE_AR_PAYMENT_SCHEDULES_V WHERE CUSTOMER_ID = :p_customer_id AND STATUS = 'OP' ORDER BY DUE_DATE;

Because the view aggregates on DUE_DATE and DAYS_PAST_DUE, reports that require installment-level aging should account for the GROUP BY behavior. The view is useful for extracting freight, tax, and line-item breakout for reconciliation against receipt applications recorded in AR_CASH_RECEIPTS through the CASH_RECEIPT_ID column. Where the object is reported as not implemented, alternative direct queries against AR_PAYMENT_SCHEDULES joined to RA_CUSTOMER_TRX should be used.