AR Past Due Invoice – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Detail past due AR invoice report with invoice number, days past due, amount past due and currency code

Report Parameters

Operating Unit, Order By, As of Date, Days Late Low, Days Late High, Balance Due Low, Balance Due High, Collector, Customer Name, Customer Name not in, Customer Number, Type Low, Salesperson/Agent Low

Oracle EBS Tables Used

hr_all_organization_units_vl, ar_payment_schedules_all, ra_customer_trx_all, ra_cust_trx_types_all, hz_cust_accounts, hz_parties, ra_salesreps, hz_customer_profiles, ar_collectors

Report Categories

Enginatics

AR Aging

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output AR Past Due Invoice - demo 1 16-Jun-2025 182210.xlsx
Blitz Report™ XML Import AR_Past_Due_Invoice.xml
Full SQL on Enginatics www.enginatics.com/reports/ar-past-due-invoice/

AR Past Due Invoice - Case Study & Technical Analysis

Executive Summary

The AR Past Due Invoice report is a targeted collections tool designed to identify and list specific customer invoices that have exceeded their payment terms. Unlike summary aging reports that group debts into time buckets, this report provides a granular, line-item view of delinquent transactions, calculating the exact number of days each invoice is overdue. It is essential for prioritizing collection efforts and improving cash flow.

Business Challenge

Effective cash collection requires knowing exactly which debts are overdue and by how much.

Solution

The AR Past Due Invoice report empowers the collections team by:

Technical Architecture

The report focuses on the payment schedule of open transactions.

Key Tables & Joins

Logic

  1. Filter: Selects transactions where STATUS = 'OP' (Open) and AMOUNT_DUE_REMAINING > 0.
  2. Time Check: Filters where DUE_DATE < :As_Of_Date.
  3. Calculation: \(\text{Days Late} = \text{As Of Date} - \text{Due Date}\)
  4. Sorting: Typically ordered by Customer and then by Days Late (descending) to highlight the worst offenders.

Parameters

FAQ

Q: Does this report show invoices that are due today? A: Typically, “Past Due” implies Due Date < As of Date. If an invoice is due today, it is usually not considered past due until tomorrow.

Q: Are disputed invoices included? A: Yes, unless specifically excluded by customization. A disputed invoice is still legally past due until the dispute is resolved or a credit is issued.

Q: What happens if I run this for a past date? A: The report will show what was past due on that date, based on the open balance at that time (if the query logic supports historical “As of” reconstruction, though standard versions often look at current open items relative to a past date).

© 2026 Enginatics