SQL to identify the query being executed by the concurrent program currently

How do you identify which SQL is running behind the concurrent program at a particular instant?
    The SQL given below lists out the request id, SID, concurrent program name and the concurrent program start time currently being executed in the instance.

  SELECT fcr.request_id,
                  vs.SID,
                  fcpt.user_concurrent_program_name,
         TO_CHAR (fcr.actual_start_date,
                  'mm/dd/rrrr hh24:mi:ss') time_started
    FROM fnd_concurrent_requests fcr,
                  fnd_concurrent_programs_tl fcpt,
                  v$session vs,
                  v$process vp
   WHERE fcpt.concurrent_program_id = fcr.concurrent_program_id
     AND fcpt.application_id = fcr.program_application_id
     AND fcpt.LANGUAGE = 'US'
     AND phase_code = 'R'
     AND status_code = 'R'
     AND vs.audsid = oracle_session_id
     AND vp.addr = vs.paddr
ORDER BY actual_start_date;



Get the SID for the concurrent program for which you want to see the SQL currently being executed and pass it to the SQL below.

  SELECT sql_text
    FROM SYS.v_$sqltext
   WHERE hash_value = (SELECT sql_hash_value
                         FROM SYS.v_$session
                        WHERE SID = &v_sid)
ORDER BY piece;

Get Oracle Application's URL from backend

Most of the times, we tend to forget to add the URL of the Oracle instances and we search where to find it.

Well instead of searching through papers or files you can directly execute the query given below to get the URL of the instance.
SELECT home_url
   FROM icx_parameters;

APIs to Delete Concurrent Program

Many a times we had defined concurrent program wrongly and wondered how to delete it.

 

Well Oracle does provide an API to accomplish it.

API to delete the program

fnd_program.delete_program('Short Name', 'Application');

API to delete the executable 

fnd_program.delete_executable('Short Name', 'Application');

Do remember to issue the commit after you are done.