Skip to the content.

AR Past Due Invoice – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Receivables · all Receivables reports

Overview

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

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, CESS Amount, CGST Amount, Custom Amount, IGST Amount, SGST Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CESS Rate, CGST Rate, IGST Rate, SGST Rate, CGST Non-Recoverable, CGST Recoverable, IGST Non-Recoverable, IGST Recoverable, SGST Non-Recoverable, SGST Recoverable, Reverse Charge Flag.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

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 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, AR Customer Upload, AR Customers and Sites, AR Receipt Analysis - Days Late, AR Disputed Invoice, AR Transactions and Payments, GL Account Analysis, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

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
SQL Source AR Past Due Invoice.sql
Full Report 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).


© Enginatics GmbH