Query to find open, unapplied Credit Memos in EBS Accounts Receivables (AR)

As part of a series of useful, simple queries for Oracle Receivables, here is a simple query to find open, unapplied credit memos. This is a simplified version of a query that I used for programmatically applying open Credit Memos to an invoice.

There are many other additions you can make to the query to more accurately specify what you are looking for. For example, if you want Debit Memos instead, simply change the Class column in the WHERE clause to ‘DM.’ This is meant to get you off the ground.

If you need help constructing a more specific, feel free to comment below!


select aps.*
from ar_payment_schedules_all aps,
hz_cust_accounts hca
where aps.customer_id = hca.cust_account_id
and aps.class = 'CM'
and aps.status = 'OP'
and hca.account_number = 'CUSTOMER NUMBER HERE'
order by aps.last_update_date asc;

The same information this query returns can be found using Transactions Summary in the Recievables Manager responsibility.

Query to find open invoices in EBS Accounts Receivables (AR)

As part of a series of useful, simple queries for Oracle Receivables, here is a simple query to find open receivables invoices. There are many other additions you can make to the query to more accurately specify what you are looking for. This is meant to get you off the ground.

If you need help constructing a more specific, feel free to comment below!


select aps.*
from ar_payment_schedules_all aps,
hz_cust_accounts hca
where aps.customer_id = hca.cust_account_id
and aps.class = 'INV'
and aps.status = 'OP'
and hca.account_number = 'Customer Number Here'
order by aps.last_update_date desc

The same information this query returns can be found using Transactions Summary in the Recievables Manager responsibility.

Series: Simple EBS Accounts Receivables Queries

Many times when developing PL/SQL customizations or automations for Oracle Applications it’s useful to have a repertoire of simple queries that might be needed to either quickly test whether or not something worked, or for use in cursors when creating automations.

I wanted to share these queries because at least with the search terms I used at the time of needing them, they were not easily found. Hopefully they will help someone. I will post them to this blog as time permits and add them to this list.

Please keep in mind that these queries will probably be very simplified. They’re mainly intended to help get someone off of the ground when creating a cursor or testing, rather than be a copy-and-paste solution. Feel free to comment on the query’s blog post if you need any help.

List of Queries