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

Searching for Text in Stored Procedures and Functions in MySQL

While debugging packages and procedures in an Oracle database I have become accustomed to searching through the source of all packages and procedures in the database. To do this in Oracle, one would query the Data Dictionary ALL_SOURCE (or DBA_SOURCE, depending on permissions) like so:

SELECT * 
FROM all_source 
WHERE lower(text) 
        LIKE '%search-string-in-lower-case-here%';

This is generally a quick and dirty way to search all procedures, packages, and functions for something like a custom error string or calls to other procedures (which can also be done with ALL_DEPENDENCIES)

MySQL stores routines a bit differently than Oracle, but this is still possible. Since MySQL does not have dependency tracking functionality, if one makes a change to a stored function and wants to find all other stored procedures that call that function, one would query the PROC table in the MYSQL database like so:

SELECT * 
FROM mysql.proc 
WHERE lower(CONVERT(body using utf8)) 
        LIKE '%stored-function-name-in-lowercase-here%';

Gotchas about this query:

The reason I’m posting this to my blog mainly is because there are two somewhat not-straightforward things about this query that should be noted. First of all, please note that the query converts the body of the routine to lower case. This is done because calls made in the DDL of routines can be made in any case, since SQL syntax is case-insensitive.

With that being said, due to MySQL storing routine’s bodies as a BLOB type, and since the BLOB type is stored in binary format, it is necessary to convert the body to a character set (like UTF8) using something like CONVERT(body using utf8) before using the LOWER() function on it.

For more information about converting BLOB text please see the MySQL bug note about it here.

Amazon EC2 Free Tier Billing – Data Transfer, Ubuntu Server 11.04

Update: Please note that this post is no longer applicable now that Amazon has stopped charging for inbound data.
— ——
Recently I have signed up for Amazon EC2’s free usage tier, and I am really enjoying the service. I don’t use it for much, but I have been tinkering with it while considering using it to host a personal project. After installing one of the official Ubuntu Server images offered by Canonical, I have started noticing some charges for data transfer outside of the free usage tier’s coverage.

$0.010 per GB – regional data transfer – in/out/between EC2 Avail Zones or when using public/elastic IP addresses or ELB – $0.01

This was confusing, as I do not have an Elastic IP (a static IP they allow you to use “elastically” along all of your instances) assigned to me. The charges are very minuscule due to my usage, but each month so far has resulted in a 1 cent charge. While it isn’t enough of a charge to worry about, upon further research I believe I was able to find the source of these charges.

The cause:

While Amazon EC2 includes 15GB in/out of bandwidth to use as one pleases for free, bandwidth is calculated separately between other Amazon EC2 instances. In my case, Canonical’s Ubuntu Server image was set to download new updates and packages from their EC2 instance. Although this instance was also on the East Coast, bandwidth was still metered and charged at a special rate outside of the Free Tier’s allowances.

The solution:

If one were running a server outside of the free usage tiers, this bandwidth metered would be cheaper than downloading packages from a standard, non-Amazon mirror; however if you would like your updates to be part of the free 15GB Amazon gives free tier customers changes can be made to /etc/apt/sources.list. Simply remove the URL to the EC2 mirror and replace it with a public one such as http://mirror.cc.columbia.edu/pub/linux/ubuntu/archive/.

Installing Glassfish on Ubuntu Server 11.04 (No GUI)

Because the self-extracting shell script used to install Glassfish requires the use of a GUI by default, installing Glassfish on a system like Ubuntu Server is not quite a straightforward is one may think.

To install Glassfish without a GUI, one must use a not-so-known silent install mode. To do this, an answer file (a text file that contains installation settings) must be supplied using argument -a, and argument -s must be supplied. For example:

./glassfish-3.1-unix.sh -a answerFileName -s

To generate an answer file, however, one must run the installer in trial mode:

./glassfish-3.1-unix.sh -n answerFileName

The only problem with this is that generating the answer file in trial mode also requires the use of a GUI. In my case, I wanted to install Glassfish on my Natty Ubuntu Server on Amazon EC2, which does not have a GUI. Using another Ubuntu machine I generated an answer file to install using silent mode (see above).

If you’d like to use the answer file I used: Click here. This answer file has the most basic settings set, with an admin password of “password.”

Hopefully this saves someone a little time!