Search Results arbv_ar_payment_schedules

Overview

ARBV_AR_PAYMENT_SCHEDULES is a read-only Oracle EBS view owned by the APPS schema in the Receivables (AR) product. It exposes the payment schedule records that drive installment, due date, discount, dunning, dispute, and collection tracking for Receivables transactions such as invoices, debit memos, credit memos, chargebacks, and commitments. Each row represents one payment schedule line attached to a transaction, carrying both the original and remaining amounts by component (line, freight, tax, receivables charges) together with the accounting dates and status attributes used throughout collections and accounting processing.

The view is described in the ETRM metadata as "-Retrofitted", indicating that it was created to provide a stable, backwards-compatible interface over the underlying AR_PAYMENT_SCHEDULES table. Because it embeds Oracle Forms/Lookups translation syntax in the view text (annotations prefixed with "_LA:"), the view resolves lookup meanings at query time for Class, Status, In Collection flag, Follow Up code, and Exclude From Dunning flag, returning user-readable meanings rather than raw codes. This makes it convenient for reports and integrations that require descriptive values without additional joins to AR_LOOKUPS.

The user search term "unearned_discount_taken" maps directly to the view column UNEARNED_DISCOUNT_TAKEN, which is the counterpart of EARNED_DISCOUNT_TAKEN. Discount taken is split between earned and unearned portions to support accounting and reconciliation when a payment is applied before or after the discount eligibility window.

Underlying Base Objects

The view is defined over two objects documented in the ETRM metadata:

• AR_PAYMENT_SCHEDULES (SYNONYM) — the primary base object. All payment schedule attributes are drawn from this table aliased PS. It is the join driver, and the view is effectively a decoded, restricted projection of it.
• AR_CONS_INV (SYNONYM) — the consolidated billing invoice table, aliased CI. It is joined with an outer join (PS.CONS_INV_ID = CI.CONS_INV_ID (+)) to supply CONS_BILLING_NUMBER, ensuring payment schedules not tied to consolidated billing are still returned.

The view carries the WITH READ ONLY clause, confirming that no DML is permitted through it. Columns are grouped in the view text under UNIQUE ATTRIBUTES, REGULAR ATTRIBUTES, IDS, and WHO COLUMNS, with descriptive column aliases such as GENERAL_LEDGER_DATE, ORIGINAL_AMOUNT_DUE, BALANCE_DUE, and the two discount columns replacing the underlying table names.

Key Columns

• PAYMENT_SCHEDULE_ID — unique identifier of the payment schedule line; the view's UNIQUE attribute and primary key candidate.
• DUE_DATE — the scheduled due date for the installment.
• "_LA:CLASS" and "_LA:ITEM_STATUS" — decoded lookup meanings for transaction class and payment schedule status.
• EARNED_DISCOUNT_TAKEN and UNEARNED_DISCOUNT_TAKEN — the earned and unearned portions of discount taken; directly relevant to the user's search term and to discount accounting.
• GENERAL_LEDGER_DATE, GL_DATE_CLOSED, ACTUAL_DATE_CLOSED — accounting and close dates for the schedule.
• ORIGINAL_AMOUNT_DUE, BALANCE_DUE, BALANCE_DUE_IN_FUNCTIONAL_CURR — original amount, remaining balance, and functional-currency balance.
• ORIGINAL_LINE_AMOUNT, REMAINING_LINE_AMOUNT, ORIGINAL_FREIGHT, REMAINING_FREIGHT, ORIGINAL_TAX, REMAINING_TAX, RECEIVABLE_CHARGES_* — component-level original and remaining balances.
• AMOUNT_CREDITED, AMOUNT_IN_DISPUTE, DISPUTE_DATE — credit and dispute tracking attributes.
• CURRENCY and exchange attributes (invoice currency code, exchange rate, type, date) plus ORG_ID and the CUSTOMER_*, CASH_RECEIPT_ID, CUSTOMER_TRX_ID, and TERM_ID identifiers.
• Collection and dunning columns: CALL_DATE_LAST, FOLLOW_UP_CODE_LAST, PROMISE_DATE_LAST, PROMISE_AMOUNT_LAST, STAGED_DUNNING_LEVEL, and the decoded In Collection, Follow Up, and Exclude From Dunning meanings.
• CONS_BILLING_NUMBER — consolidated billing number sourced from AR_CONS_INV.

Common Use Cases and Queries

Typical uses include aging and balance reporting, discount analysis, collections follow-up, dispute tracking, and integration extracts. A common requirement is to analyze discount taken, splitting earned from unearned amounts.

SELECT payment_schedule_id,
       due_date,
       original_amount_due,
       balance_due,
       earned_discount_taken,
       unearned_discount_taken
FROM   apps.arbv_ar_payment_schedules
WHERE  class = 'INV'
  AND  unearned_discount_taken > 0;

To list open balances by transaction for a specific organization and currency:

SELECT customer_trx_id,
       payment_schedule_id,
       due_date,
       balance_due,
       balance_due_in_functional_curr
FROM   apps.arbv_ar_payment_schedules
WHERE  org_id = :p_org_id
  AND  invoice_currency_code = :p_currency
  AND  balance_due > 0
ORDER BY due_date;

For collections and consolidated billing reporting, the decoded lookup meanings reduce the need to join AR_LOOKUPS directly, and the outer join guarantees that transactions without consolidated billing are still included. Because the view is read-only and exposes no DML, it is suitable for reference queries, custom reports, and data extraction, while all maintenance must be performed against the underlying AR_PAYMENT_SCHEDULES table.