Query To Fetch Concurrent Program Names Based On The ValueSet

SELECT   b.user_concurrent_program_name
  FROM    fnd_descr_flex_col_usage_vl a,
         fnd_concurrent_programs_vl b
 WHERE  a.flex_value_set_id = ( SELECT    flex_value_set_id
    FROM    fnd_flex_value_sets
 WHERE    flex_value_set_name = '&Value_Set_Name')
   AND     a.descriptive_flexfield_name = '$SRS$.' || b.concurrent_program_name

Analyzing The Queries Through Concurrent Program Request ID (Performance Testing)

Query : 1
SELECT  user_concurrent_program_name,
c.request_id,
s.sid,
p.spid,
s.process,
s.osuser,
s.username,
s.program,
s.status,
logon_time,
last_call_et
FROM v$session s, 
v$process p, 
apps.fnd_concurrent_requests c,
apps.fnd_concurrent_programs_tl ct 
WHERE oracle_process_id=p.spid 
AND s.paddr=p.addr 
AND ct.concurrent_program_id=c.concurrent_program_id
AND request_id='&Request_Id';

Query : 2

SELECT    sql_text,
          lockwait
FROM      v$session a, v$sql b
WHERE    a.sql_address = b.address
AND         a.sql_hash_value = b.hash_value
AND         a.sid = '&SID';

Queries To Find & Release Locks At Object Level

Search Locks For the Object

Step :1) SELECT  session_id 
             FROM     dba_ddl_locks
             WHERE  name = '<object_name_here>';

Look For the SID and SERIAL# Combination For The Corresponding SESSION_ID
Step :2) SELECT  sid,serial# 
             FROM     v$session 
             WHERE  sid = '<session_id>';

Query To Kill DB Session
Step :3) alter system kill session '<sid>,<serial#>';

Query To Find Concurrent Program Attached To a Responsibility

SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
  AND     frg.request_group_id = fr.request_group_id
  AND     frgu.request_group_id = frg.request_group_id
  AND     fcpt.concurrent_program_id = frgu.request_unit_id
  AND     fcpt.user_concurrent_program_name = '&conc_program_name'
ORDER BY 1,2,3,4

Deleting Concurrent Program In Oracle Apps

DECLARE

   v_program_short_name    VARCHAR2 (200);

   v_application                   VARCHAR2 (200);

BEGIN

   v_program_short_name := 'My_Concurrent_Program';

   v_application                := 'My_Application';

   apps.fnd_program.delete_program

               (program_short_name      => v_program_short_name,

                application                     => v_application

               );

   COMMIT;

END;

Second Max Salary Query

SELECT DISTINCT (A.SAL) 

FROM SCOTT.EMP A 

WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);