Top SQL Queries Asked in Interview (Oracle EBS R12)
Oracle e-Business Suite (EBS) R12, a robust ERP solution, enables businesses to manage their operations efficiently. However, to fully leverage its potential, mastering SQL queries is essential. So we will cover some of the most useful and important SQL queries for Oracle R12, focusing on real-world scenarios involving multiple tables. We’ve also included top sql queries asked in interview and examples from the Sysadmin, Application Developer(FND), Procedure-to-Pay, Order-to-Cash, Schedule-to-Delivery, and Finance modules. Moreover you can use python to connect Oracle database and easily use automation in some usefull and daily monitoring queries. Lastly, you need to know Oracle Active Session History as well as V$SQL views details for database performance monitoring and improvement.
You should review the article for avoiding some common SQL mistakes.
Table of Contents
Top SQL Queries Asked in Interview
Query to Fetch User and assigned Responsibilities
SQL to Find Responsibilities Attached to User
Query to Fetch Concurrent Program Details
Concurrent Program Parameters Query
SQL Query to Get Concurrent Program Schedule
Query to Find Request Group Attached to Responsibility
Find Out the Program with in Request Set and its Details
Get details of all Concurrent Programs used by a Request Set
Concurrent Request Details for a Specific Date or User
Query to Find Profile Option Values at All Levels in Oracle Apps R12
Query to Find Menu and Request Set of Responsibility in Oracle R12
SQL to Find Functions and Submenu in a Responsibility
SQL Query to Get Oracle Menus and User Function Name of Main Menu
Query to Fetch User and assigned Responsibilities
This query retrieves the list of responsibilities assigned to a specific user in Oracle Apps R12. Oracle user responsibility query is one of the top sql queries asked in any interview.
-- Oracle R12 SQL Query to Fetch User's Responsibilities
-- Best SQL Query for Sysadmins to check user access
SELECT fu.user_name,
frt.responsibility_name,
fur.start_date,
fur.end_date
FROM fnd_user fu,
fnd_user_resp_groups_direct fur,
fnd_responsibility_tl frt,
fnd_responsibility fr
WHERE fu.user_name = 'user_name'
AND fu.user_id = fur.user_id
AND fur.responsibility_id = fr.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.language=userenv('Lang')Query to Find Responsibilities Attached to User in Oracle apps
You can prepare a SQL query to fetch users with specific responsibility in Oracle EBS R12. Just use the below query to fetch users with specific responsibility oracle ebs. You need to change the responsibility in below query to get the required details.
-- Oracle R12 SQL Query to Fetch Users with specific Responsibilities
-- Best SQL Query for Sysadmins to check who has access to responsibilities
SELECT fu.user_name,
frt.responsibility_name,
fur.start_date,
fur.end_date
FROM fnd_user fu,
fnd_user_resp_groups_direct fur,
fnd_responsibility_tl frt,
fnd_responsibility fr
WHERE frt.responsibility_name = 'System Administrator'
AND fu.user_id = fur.user_id
AND fur.responsibility_id = fr.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.language=userenv('Lang')
AND nvl(fur.end_date, SYSDATE+1) > SYSDATEQuery to Fetch Concurrent Program Details in Oracle EBS
In your day to day business operation you need a query to fetch concurrent program details in oracle ebs. Retrieve information about concurrent programs, executable name, executable program name/file Name, Oracle Reports rdf file name, Shell script name, PL/SQL package procedure name, SQL*Plus file name using the below query to fetch concurrent program details oracle ebs.
-- Oracle R12 SQL Query to Concurrent Program Details
-- Best SQL Query to get Oracle apps program details
SELECT fcpt.user_concurrent_program_name,
fcp.concurrent_program_name,
fat.application_name,
fet.executable_name,
fate.application_name exe_application,
flv.meaning execution_method,
fet.execution_file_name,
fcp.enable_trace
FROM fnd_concurrent_programs_vl fcpt,
fnd_concurrent_programs fcp,
fnd_application_vl fat,
fnd_executables fet,
fnd_application_vl fate,
fnd_lookup_values_vl flv
WHERE fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fate.application_id
AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND flv.lookup_code = fet.execution_method_code
AND fcpt.user_concurrent_program_name='Active Users'Concurrent Program Parameters Query in Oracle Apps R12
You need to know the query to get concurrent program parameters query in Oracle apps. Below query to get Concurrent Program’s Parameters, prompt name, enabled flag, required flag, display flag, value sets name and default Values in Oracle Apps R12.
-- Oracle R12 SQL Query to Concurrent Program with parameters
-- Best SQL Query to get Oracle apps concurrent program parameters
SELECT fcpl.user_concurrent_program_name program_name,
fcp.concurrent_program_name short_name,
par.column_seq_num seq,
par.end_user_column_name parameter,
par.form_left_prompt prompt,
par.enabled_flag,
par.required_flag,
par.display_flag,
ffv.flex_value_set_name
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl par,
fnd_flex_value_sets ffv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name like 'Pick Selection List Generation - SRS%'
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffv.flex_value_set_id = par.flex_value_set_id
ORDER BY fcpl.user_concurrent_program_name, par.column_seq_numSQL Query to Get Concurrent Program Schedule in Oracle apps
The Oracle E-Business Suite (EBS) is a comprehensive and extensive enterprise resource planning (ERP) system. Thousands of cuncurrent program scheduled to do the business operation. You need to know if all the required concurrent program are scheduled correctly. Sometime after maintaince on weekend few program’s scheuled got cancelled. So you need a sql query to get concurrent program schedule in Oracle apps R12.
Below query to check scheduled jobs in Oracle r12. It will give you all the details required to know the scheduled concurrent programs in Oracle EBS R12.
-- Oracle R12 SQL Query to Concurrent Program Schedule
-- Best SQL Query to get Oracle apps program schedule
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM fnd_concurrent_programs_tl cp,
fnd_concurrent_requests cr,
fnd_user fu,
fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;Query to Find Request Group Attached to Responsibility in Oracle apps
Sometime you need to run a concurrent program without knowing the responsibility name in Oracle Apps. Knowing responsibility attached to concurrent program is needed to analyze a program or testing a program performance.
So you have to know the sql query to find request group attached to responsibility in Oracle apps. Below query will give the Request Group, Responsibility Name associated with a concurrent program in Oracle EBS R12.
-- Oracle R12 SQL Query to know request group attached to a responsibility
-- Best SQL Query to get Oracle apps request group attached to a responsibility
SELECT fcp.user_concurrent_program_name program_name,
fr.responsibility_name,
frg.request_group_name,
fa.application_name
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_responsibility_vl fr,
fnd_concurrent_programs_vl fcp,
fnd_application_vl fa
WHERE frg.request_group_id=frgu.request_group_id
AND fr.request_group_id=frgu.request_group_id
AND frg.application_id=fr.application_id
AND frgu.request_unit_id=fcp.concurrent_program_id
AND frgu.request_unit_type='P'
AND frg.application_id=fa.application_id
--AND fa.application_name='Shipping Execution'
AND fcp.user_concurrent_program_name like 'Pick Selection List Generation - SRS%'Query to Find Out the Program with in Request Set and its Details in EBS R12
You can find out the request set name of given concurrent Program and the details of programs attached in the Request Set.
When concurrent program needs to run through the Request Set instead of single request for a specific business requirement. You can find the request set associated with the Concurrent Program. This is how to find Request set of a Concurrent Program from backend.
-- Oracle R12 SQL Query to know request set for a concurrent program
-- Best SQL Query to get Oracle apps request set for a concurrent program
SELECT DISTINCT user_request_set_name,
user_concurrent_program_name
FROM fnd_request_sets_tl frs,
fnd_request_set_programs frsp,
fnd_concurrent_programs_tl fcp
WHERE frsp.concurrent_program_id = fcp.concurrent_program_id
AND frs.request_set_id = frsp.request_set_id
AND Upper(fcp.user_concurrent_program_name) = Upper(:prog_name)Query to Get details of all Concurrent Programs used by a Request Set
Below query will give the details of all Concurrent programs used by a Request Set.
-- Oracle R12 SQL Query to know all the concurrent programs attached in Request set
-- Best SQL Query to get Oracle apps concurrent programs attached in Request set
SELECT rs.user_request_set_name "Request Set",
frss.user_stage_name "Stage",
frss.display_sequence "Sequence",
frsp.sequence PSeq,
cp.user_concurrent_program_name "Program"
FROM apps.fnd_request_sets_vl rs,
apps.fnd_req_set_stages_form_v frss,
applsys.fnd_request_set_programs frsp,
apps.fnd_concurrent_programs_vl cp
WHERE rs.application_id = frss.set_application_id
AND rs.request_set_id = frss.request_set_id
AND rs.user_request_set_name = 'Request Set Name'
AND frss.set_application_id = frsp.set_application_id
AND frss.request_set_id = frsp.request_set_id
AND frss.request_set_stage_id = frsp.request_set_stage_id
AND frsp.program_application_id = cp.application_id
AND frsp.concurrent_program_id = cp.concurrent_program_id
AND rs.end_date_active IS NULL
ORDER BY 3,4Concurrent Request Details for a Specific Date or User
Retrieve details of concurrent requests for a specific date or user in Oracle EBS R12
-- Oracle R12 SQL Query to get concurrent programs scheduled today
-- Best SQL Query to get Oracle apps concurrent programs scheduled today
SELECT fcr.request_id,
fcp.user_concurrent_program_name,
fcr.phase_code,
fcr.status_code,
fcr.request_date,
fcr.actual_start_date,
fcr.actual_completion_date
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_vl fcp
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.request_date >= TRUNC(SYSDATE - 1)
ORDER BY fcr.request_date DESC; Query to Find Profile Option Values at All Levels in Oracle Apps R12
You need to know the Profile Options Value in Oracle EBS R12 in SQL query. Below query to find profile option values at all levels in Oracle Apps R12.
-- Oracle R12 SQL Query to get profile option values at any levels
-- Best SQL Query to get Oracle apps profile option values at any levels
SELECT SUBSTR(fpo.profile_option_name,1,25) INTERNAL_NAME,
SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
DECODE(fpov.level_id, 10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User',
10005,'Server',
10007,'Server+Resp',
fpov.level_id) LEVELl,
DECODE(fpov.level_id, 10001,'Site',
10002, fa.application_short_name,
10003, fr.responsibility_name,
10004, fu.user_name,
10005, fn.node_name,
10007, fnm.node_name||' + '
||fr.responsibility_name,
fpov.level_id) LEVEL_VALUE,
NVL(fpov.profile_option_value,'Is Null') VALUE,
to_char(fpov.last_update_date, 'DD-MON-YYYY HH24:MI') LAST_UPDATE_DATE,
dd.USER_NAME LAST_UPDATE_USER
FROM fnd_profile_option_values fpov,
fnd_responsibility_tl fr,
fnd_application fa,
fnd_user fu,
fnd_profile_options fpo,
fnd_nodes fn,
fnd_nodes fnm,
fnd_responsibility_tl frt,
fnd_user dd,
fnd_profile_options_tl pot
WHERE fpo.profile_option_name = 'ICX_FORMS_LAUNCHER'
AND fpo.PROFILE_OPTION_NAME = pot.profile_option_name (+)
AND fpo.profile_option_id = fpov.profile_option_id (+)
AND fpov.level_value = fr.responsibility_id (+)
AND fpov.level_value = fa.application_id (+)
AND fpov.level_value = fu.user_id (+)
AND fpov.level_value = fn.node_id (+)
AND fpov.LEVEL_VALUE_APPLICATION_ID = frt.responsibility_id (+)
AND fpov.level_value2 = fnm.node_id (+)
AND fpov.LAST_UPDATED_BY = dd.USER_ID (+)
AND pot.LANGUAGE = 'US'
AND fr.LANGUAGE='US'
ORDER BY fpo.profile_option_nameQuery to Find Menu and Request Set of Responsibility in Oracle R12
Below SQL query to find Menu and Request Set of Responsibility in Oracle EBS R12
-- Oracle R12 SQL Query to get menu attached to a responsibility
-- Best SQL Query to get Oracle apps menu attached to a responsibility
SELECT fr.responsibility_name, fm.menu_name , frg.request_group_name
FROM fnd_responsibility_vl fr,
fnd_menus fm,
fnd_request_groups frg
WHERE fr.menu_id = fm.menu_id
AND frg.request_group_id=fr.request_group_id
AND fr.responsibility_name='Receivables Manager'SQL to find Functions and Submenu in a Responsibility
You can get the Menu and Submenu list against a responsibility in Oracle EBS R12 using below useful SQL query.
-- Oracle R12 SQL Query to find functions, submenus, menus to a responsibility
-- Best SQL Query to get Oracle apps functions, submenus, menus to a responsibility
SELECT frv.responsibility_name,
menu.menu_name,
fmev.entry_sequence,
fmev.prompt,
fmev.description,
sub_menu.user_menu_name sub_menu_name,
sub_menu.description sub_menu_description,
ffft.user_function_name,
fmev.grant_flag
FROM fnd_menu_entries_vl fmev,
fnd_menus menu,
fnd_menus_tl sub_menu,
fnd_form_functions_tl ffft,
fnd_responsibility_vl frv
WHERE frv.menu_id = menu.menu_id
AND menu.menu_id = fmev.menu_id
AND sub_menu.menu_id(+) = fmev.sub_menu_id
AND sub_menu.language(+) = 'US'
AND ffft.function_id(+) = fmev.function_id
AND ffft.language(+) = 'US'
AND frv.responsibility_name= 'Application Developer'
ORDER BY fmev.entry_sequence;SQL Query to Get Oracle Menus and User Function Name of Main Menu
Query below will give tree structure menus and functions details associated with a menu. You can see it as in the responsibility navigator.
-- Oracle R12 SQL Query to find functions of main menu
-- Best SQL Query to get Oracle apps functions of main menu
SELECT
lpad(
' ', 6 *(level - 1)
)
|| menu_entry.entry_sequence sequence,
lpad(
' ', 6 *(level - 1)
)
|| menu.user_menu_name submenu_descrition,
lpad(
' ', 6 *(level - 1)
)
|| func.user_function_name function_description,
menu.menu_id,
func.function_id,
menu_entry.grant_flag grant_flag,
decode(
menu_entry.sub_menu_id, NULL, 'FUNCTION',
decode(menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
) type
FROM fnd_menu_entries menu_entry,
fnd_menus_vl menu,
fnd_form_functions_vl func
WHERE menu_entry.sub_menu_id = menu.menu_id (+)
AND menu_entry.function_id = func.function_id (+)
AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_vl menu2
WHERE menu2.user_menu_name = 'AR_NAVIGATE_GUI'
)
CONNECT BY menu_entry.menu_id = prior menu_entry.sub_menu_id order siblings by menu_entry.entry_sequenceHow to Find Trace File for Concurrent Request ID?
Below query is used to get the trace file name with the location for a trace file generated by a concurrent program in Oracle Apps R12. This is how to find trace file for concurrent request id in Oracle Apps R12.
SELECT req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc'
trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request --Request ID
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_idQuery to get Oracle Apps R12 instance Cloned Date
You to know the date when last time your development or uat instance cloned from production system. Below query will give the exat date when your Oracle Apps instanced cloned.
SELECT resetlogs_time
FROM v$database; Conclusion: Top SQL Queries Asked in Interview
Above SQL Queries are asked in in top interview. You should use good sql client for query execution. We are continue to update the important Oracle R12 queries. You can suggest us to include any sepecific query.
Pingback: Common SQL Mistakes in Oracle (and How to Avoid Them)
Pingback: Mastering Oracle Performance Tuning with tkprof – Enodeas
Pingback: Oracle EBS Efficiency using Python Automation(python and Oracle)
Pingback: Python Script to Connect to Oracle Database, Run Query