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);    

OAF-Dependent Message Choice List in an Advanced Table

In this article, let us understand how to achieve dependent list in an advanced table where one of the fields of each row of the table should show different list based on the other lists selected value.

Use case Eg: 

• State is the first message choice list in an advanced table.
• City is the second message choice list (dependent on State) in an advanced table.
• City list should show the values based on selected state for each row differently.  

Solution: 

• Create a New VO for State list- XxStateListVO
• Sample Query: SELECT  state_id, state_name FROM xx_states;
• Create a New VO for City list- XxCityListVO by binding (positional) the state_id which would be the filter criteria for cities list.
• Sample Query: SELECT city_id,city_name,state_id FROM xx_cities WHERE state_id=:1;
• Add instances of both VO's to the Application Module.
• Now on the page create an advanced table region with your preferred id eg: advancedTableRN with State (eg: stateItem) and City (eg: cityItem) as the columns with type as Message Choice.
• For stateItem messageChoice, set the below properties.
• Picklist View Instance: XxStateListVO1 
• Picklist Display Attribute: StateName
• Picklist Value Attribute: StateId
• Action Type: Fire Partial Action
• Event: stateSelectEvent
• Submit: True
• For cityItem messageChoice, set the below properties.
• Picklist View Definition: xx.oracle.apps.loc.XxCityListVO (complete path of VO Definition)
• Picklist View Instance: XxCityListVO1 
• Picklist Display Attribute: CityName
• Picklist Value Attribute: CityId
• Write the below code in the Process Request of respective page controller.
• OAMessageChoiceBean stateBean=(OAMessageChoiceBean)webBean.findChildRecursive("stateItem");
• stateBean.setPickListCacheEnabled(Boolean.FALSE);
• OAAdvancedTableBean locTable = (OAAdvancedTableBean) webBean.findChildRecursive("advancedTableRN");
• OAMessageChoiceBean cityBean=(OAMessageChoiceBean) locTable .findChildRecursive("cityItem");
• cityBean.setListVOBoundContainerColumn(0,locTable ,"stateItem");
• Write the below code in the Process Form Request to catch the State Selected Event and execute the city VO query.
• xxLocAMImpl locAm = (xxLocAMImpl ) pageContext.getApplicationModule(webBean);    
• String event = pageContext.getParameter(EVENT_PARAM);       
• String rowRef = pageContext.getParameter(EVENT_SOURCE_ROW_REFERENCE);
• Row currentRow = locAm .findRowByRef(rowRef); 
    
• if ("stateSelectEvent".equals(event)){        
• String stateId = currentRow.getAttribute("StateId")!=null? currentRow.getAttribute("StateId").toString():null;    
• locAm.filterCities(stateId);   }
• Write the below code to declare the above invoked method "filterCities" in the application module implementation file.
•  public void filterCities(String pStateId){
•         XxCityListVOImpl cityVo = getXxCityListVO1();
•         cityVo .setWhereClauseParam(0,pStateId);
•         cityVo .executeQuery();
•     }
 

Solution for the error "Oracle Discoverer Desktop has encountered a problem and needs to close. We are sorry for the inconvenience."

Sometimes we will not be able to launch the Discoverer Desktop from our PC due to the error "Oracle Discoverer Desktop has encountered a problem and needs to close. We are sorry for the inconvenience" .

This error will not be resolved even if we try to re-install the Discoverer. Follow the below simple step to resolve this error.

1. Go to C:\Windows >> Try to find the dis51usr.INI file >> Rename that file or Delete that file.

2. You should now be able Launch the Discoverer Desktop successfully.

Note: Once  after launching the Discoverer Desktop, a new dis51usr.INI appears in C:\
Windows.

Query to find the Direct Responsibilities attached to a User

select
urg.user_id,
fu.user_name,
urg.responsibility_id,
fr.responsibility_name
from
fnd_user_resp_groups_direct urg,
fnd_responsibility_tl fr,
fnd_user fu
where
urg.user_id=fu.user_id and
urg.responsibility_id=fr.responsibility_id and
fu.user_name='<user_name>'

Query to find the Execution File Name of any Concurrent Program

select
a.application_id,
a.user_concurrent_program_name,
c.executable_name,
c.execution_file_name
from
fnd_concurrent_programs_tl a,
fnd_concurrent_programs b,
fnd_executables c
where a.user_concurrent_program_name ='<Concurrent_Program_Name>'
and a.concurrent_program_id=b.concurrent_program_id
and b.executable_id=c.executable_id

Query to know the Attributes(DFF Fields) Actual Column Names in Oracle tables

SELECT

b.APPLICATION_TABLE_NAME,

b.DESCRIPTIVE_FLEXFIELD_NAME,

a.DESCRIPTIVE_FLEX_CONTEXT_CODE,

a.APPLICATION_COLUMN_NAME,

a.END_USER_COLUMN_NAME

FROM

FND_DESCR_FLEX_COLUMN_USAGES a,

FND_DESCRIPTIVE_FLEXS_vl b

WHERE

b.APPLICATION_TABLE_NAME in (UPPER('<table_name>')) AND

b.DESCRIPTIVE_FLEXFIELD_NAME=a.DESCRIPTIVE_FLEXFIELD_NAME AND b.APPLICATION_ID=a.APPLICATION_ID