1. Query to find Concurrent Program (Reports created,short name, output format other details) related information
SELECT cpv.user_concurrent_program_name "Concurrent Program Name",
cpv.concurrent_program_name "Program Short Name",
efv.application_name "Application",
cpv.enabled_flag "Enabled Flag",
-- cpv.description "Description",
cpv.output_file_type "Output Format",
fu.user_name "Created By (userid)",
-- ppf.full_name "Created By (username)",
DECODE(efv.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown') "Execution Method",
efv.executable_name "Executable Name",
efv.execution_file_name "Execution Filename"
FROM fnd_executables_form_v efv,
fnd_concurrent_programs_vl cpv,
fnd_user fu
-- per_all_people_f ppf
WHERE efv.executable_id = cpv.executable_id
AND efv.application_id = cpv.application_id
AND cpv.created_by = fu.user_id
-- AND fu.employee_id = ppf.person_id
AND cpv.user_concurrent_program_name = 'FUJ : Palace Payroll Summary Report' -- <change it>
-- Query to find to which request group the concurrent program report is attached
SELECT FRG.REQUEST_GROUP_NAME, FE.EXECUTION_FILE_NAME, FE.EXECUTABLE_NAME, frg.request_group_id
FROM FND_REQUEST_GROUP_UNITS FRGU, FND_CONCURRENT_PROGRAMS FCP , FND_REQUEST_GROUPS FRG
, FND_EXECUTABLES FE
WHERE FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FE.EXECUTABLE_ID = FCP.EXECUTABLE_ID
AND FE.EXECUTION_FILE_NAME = 'XXFUJSUMPAL'-- 'FUJ : Palace Payroll Summary Report'
-- Query to find Concurrent Program (Reports created,short name, output format other details) related information
SELECT frv.responsibility_name, frg.request_group_name,
frg.description descr, fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
and frv.request_group_id = 1410--'Palace HRMS Reports & Process' request group id
and fcpv.user_concurrent_program_name = 'FUJ : Palace Payroll Summary Report'--concurent report name
ORDER BY responsibility_name, frg.request_group_name, frg.description
----Request group with their resp-------
SELECT frv.responsibility_name,
frg.request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
-----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;
No comments:
Post a Comment