Few important queries for the concurrent programs

Query to find the responsibilities to which the request is been assigned
SELECT DISTINCT *
FROM fnd_responsibility_tl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM fnd_responsibility_vl
WHERE request_group_id IN (
SELECT request_group_id
FROM fnd_request_group_units
WHERE request_unit_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name
= :Concurrent_Program_name))
AND end_date IS NULL)
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name

Query to find the application name
SELECT * FROM fnd_application "application name"
WHERE application_id IN (SELECT application_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query to find the concurrent program short name
SELECT *
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name)

Query to find the execution file name for the request
SELECT *
FROM fnd_executables
WHERE executable_id=(SELECT executable_id
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query to find the requests groups
SELECT * FROM fnd_request_groups "requests groups"
WHERE request_group_id IN (SELECT request_group_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query for identifying if it is a child process
SELECT *
FROM fnd_concurrent_requests
WHERE parent_request_id IS NOT NULL
AND program_application_id = 20003
AND concurrent_program_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name LIKE
:Concurrent_Program_name)

Delete the programs from the application
begin
fnd_program.delete_program('program short name','schema name');
fnd_program.delete_executable('program short name','schema name');
commit;
end;

Sql query to fetch users assigned to a responsibility
select fu.user_name
from FND_USER_RESP_GROUPS fur,
        fnd_responsibility_tl fr,
        fnd_user fu
where fr.language = 'US'
and fr.responsibility_name like :responsibility_name
and fr.responsibility_id = fur.responsibility_id
and fu.user_id = fur.user_id

Sql query to fetch concurrent programs in a request set
SELECT user_concurrent_program_name
FROM fnd_concurrent_programs_tl
WHERE concurrent_program_id IN (
SELECT concurrent_program_id
FROM fnd_request_set_programs
WHERE request_set_id =
(SELECT request_set_id
FROM fnd_request_sets_tl
WHERE UPPER (user_request_set_name) =
UPPER ('&REQUEST_SET_NAME')
AND LANGUAGE = 'US'))
AND LANGUAGE = 'US'

Sql query to fetch all concurrent program in pending status
select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31), fnd_conc_release_classes c where c.class_type = 'S' and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

 Sql query to find scheduled concurrent programs

  SELECT *

    FROM apps.FND_CONC_REQ_SUMMARY_V

   WHERE     phase_code = 'P'

         AND status_code IN ('I', 'Q')

         AND (NVL (request_type, 'X') != 'S')

         AND requested_start_date >= SYSDATE

ORDER BY program_short_name DESC;