JDeveloper – Updating a ViewObject’s Query Offline

While maintaining some JAX-WS Web Services I built using ADF Business Components (such as View Objects, Entity Objects, Application Modules, etc), I found a way to edit a View Object’s query without having a connection to the JDeveloper project’s database. This is useful for me because I work from home and do not always have easy access to the databases we work with, but still want to get a bit of work done, but it’s not quite obvious you can do this without directly editing the XML file.

To edit a ViewObject’s query offline:

  1. Open the View Object
  2. Go to the Query side tab
  3. Click the pencil icon above the query to open the query (you’ll be told you’re not connected– click OK)
  4. Make your changes and press Apply or OK.
  5. Here you’ll be told you’re not connected again, close the window by pressing X, but don’t click ok.
If you follow the instructions above, particularly closing the dialog rather than pressing OK, your ViewObject’s query should be edited! Just make sure to test it when you have access to the database again!

 

Screenshot of Offline View Object editing

Make sure not to press OK here! Just close the dialog.

 

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.