Oracle EBS-Forms: How to know query behind LOV

In this article, let us understand how to quickly get the query behind any list of values on Oracle Form.

Use Case Scenario:

As an example, we need to know the query behind the Nationality list of values on Employee screen.

Solution:

• Go to respective form on the Application and get the form name.

        Navigation: Help >> About Oracle Applications

• Open the respective list of values (Nationality) on the Employee Form.
• Keep the list of values screen open and run the below query on database by passing the above form name that gives the query behind the list of values that is opened currently.

            SELECT TO_CHAR(sq.sql_fulltext) 
               FROM v$session ses, 
                            v$sqlarea sq
            WHERE ses.module LIKE '%PERWSHRG%'
                  AND sq.sql_id = ses.prev_sql_id;

Oracle SQL: CASE construct in WHERE clause with IN clause

In this article, let us understand how useful CASE construct in the below mentioned scenario. This would be a very common use case that would arise when we need to pass parameters and fetch records based on condition.

Use Case Scenario:

Employee table has employee records from different departments eg: Accounts, IT, Finance and we need to fetch employee details based on department name however, parameter (p_dept_name) do have the value "All" that should fetch employee records from all departments.

Solution:

SELECT e.name "Employee Name", d.name  "Department Name"

   FROM emp e, dept d

WHERE e. dep_id = d.dept_id

      AND 1 = (CASE WHEN p_dept_name = 'All' AND d.name IN ('Accounts','IT','Finance') 

                                    THEN 1

                                    WHEN p_dept_name != 'All' AND d.name = p_dept_name

                                    THEN 1

                        END);