Find the SQL behind List of Values(LOV) of an Ebusiness Suite screen

Oracle database V$ views can be used to find the SQL behind List of Values on an Oracle eBusiness Suite screen.

This is illustrated with the below example. We can find the SQL behind the LOV attached to the "Application" field in the below screen without going through the Forms code or examining the trace file.

 

Step 1 :- Find the name of the Oracle fmb file. This can be found navigating to menu Help  --> About Oracle Applications.

Step 2 :- Invoke the List of values.Make sure that the query is executed. Sometimes, invoking the LOV may not execute the query until the filter condition is entered and Find button is clicked.

Step 3 :- Keep the screen in the above state. Open the database session connecting to APPS schema and execute the below SQL.


SELECT (SELECT to_char(sql_fulltext) FROM v$sqlarea WHERE sql_id = ses.prev_sql_id)
FROM v$session ses,
     v$sqlarea sq
WHERE ses.module LIKE '%&form_name%'AND client_identifier = '&user_name'AND sq.sql_id(+) = ses.sql_id;


Enter the parameter values

Form_name :- Name of the form from Step 1
User_name  :- Oracle EBS user name

The query will return the last SQLstatement run on the database session which ideally should be SQL behind the LOV. This can be used to find other SQLs inside the form as well.