SQL to get details of Scheduled Concurrent Program


Hi Friends,

I have found a interesting sql query which gives details of scheduled concurrent requests in EBS.

/*SQL 1*/

SELECT r.request_id
, CASE
WHEN pt.user_concurrent_program_name = ‘Report Set’
THEN DECODE(
r.description
, NULL, pt.user_concurrent_program_name
, r.description
|| ‘ (‘
|| pt.user_concurrent_program_name
|| ‘)’
)
ELSE pt.user_concurrent_program_name
END job_name
, u.user_name requestor
, u.description requestor_description
, u.email_address
, frt.responsibility_name requested_by_resp
, r.request_date
, r.requested_start_date
, DECODE(
r.hold_flag
, ‘Y’, ‘Yes’
, ‘N’, ‘No’
) on_hold
, CASE
WHEN r.hold_flag = ‘Y’
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN r.hold_flag = ‘Y’
THEN r.last_update_date
END last_update_date
, r.argument_text PARAMETERS
, NVL2(
r.resubmit_interval
, ‘Periodically’
, NVL2(
r.release_class_id
, ‘On specific days’
, ‘Once’
)
) AS schedule_type
, r.resubmit_interval resubmit_every
, r.resubmit_interval_unit_code resubmit_time_period
, DECODE(
r.resubmit_interval_type_code
, ‘START’, ‘From the start of the prior run’
, ‘END’, ‘From the Completion of the prior run’
) apply_the_update_option
, r.increment_dates
, TO_CHAR((r.requested_start_date), ‘HH24:MI:SS’) start_time
FROM applsys.fnd_concurrent_programs_tl pt
, applsys.fnd_concurrent_programs pb
, applsys.fnd_user u
, applsys.fnd_user u2
, applsys.fnd_printer_styles_tl s
, applsys.fnd_concurrent_requests r
, applsys.fnd_responsibility_tl frt
WHERE pb.application_id = r.program_application_id
AND pb.concurrent_program_id = r.concurrent_program_id
AND pb.application_id = pt.application_id
AND r.responsibility_id = frt.responsibility_id
AND pb.concurrent_program_id = pt.concurrent_program_id
AND u.user_id = r.requested_by
AND u2.user_id = r.last_updated_by
AND s.printer_style_name(+) = r.print_style
AND r.phase_code = ‘P’
AND 1 = 1;

/*SQL 2*/

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
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 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;

SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ‘ (‘ || fcr.description || ‘)’, NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text “PARAMETERS”
, ‘——>’ dates
, TO_CHAR(fcr.request_date, ‘DD-MON-YYYY HH24:MI:SS’) requested
, TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’) requested_start
, TO_CHAR((fcr.requested_start_date), ‘HH24:MI:SS’) start_time
, ‘——>’ holds
, DECODE(fcr.hold_flag, ‘Y’, ‘Yes’, ‘N’, ‘No’) on_hold
, CASE
WHEN fcr.hold_flag = ‘Y’
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = ‘Y’
THEN fcr.last_update_date
END last_update_date
, ‘——>’ prints
, fcr.number_of_copies print_count
, fcr.printer
, fcr.print_style
, ‘——>’ schedule
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
‘Yes: ‘ || TO_CHAR(fcr.requested_start_date, ‘DD-MON-YYYY HH24:MI:SS’)
ELSE
‘n/a’
END run_once
, CASE WHEN fcrc.class_type = ‘P’ THEN
‘Repeat every ‘ ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ‘:’) – 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 1) + 1, 1),
‘N’, ‘ minutes’,
‘M’, ‘ months’,
‘H’, ‘ hours’,
‘D’, ‘ days’) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ‘:’, 1, 2) + 1, 1),
‘S’, ‘ from the start of the prior run’,
‘C’, ‘ from the completion of the prior run’)
ELSE
‘n/a’
END set_days_of_week
, CASE WHEN fcrc.class_type = ‘S’ AND instr(substr(fcrc.class_info, 33),’1′,1) > 0 THEN
‘Days of week: ‘ ||
decode(substr(fcrc.class_info, 33, 1), ‘1’, ‘Sun, ‘) ||
decode(substr(fcrc.class_info, 34, 1), ‘1’, ‘Mon, ‘) ||
decode(substr(fcrc.class_info, 35, 1), ‘1’, ‘Tue, ‘) ||
decode(substr(fcrc.class_info, 36, 1), ‘1’, ‘Wed, ‘) ||
decode(substr(fcrc.class_info, 37, 1), ‘1’, ‘Thu, ‘) ||
decode(substr(fcrc.class_info, 38, 1), ‘1’, ‘Fri, ‘) ||
decode(substr(fcrc.class_info, 39, 1), ‘1’, ‘Sat ‘)
ELSE
‘n/a’
end days_of_week
, CASE WHEN fcrc.class_type = ‘S’ AND instr(substr(fcrc.class_info, 1, 31),’1′,1) > 0 THEN
‘Set Days of Month: ‘ ||
decode(substr(fcrc.class_info, 1, 1), ‘1’, ‘1st, ‘) ||
decode(substr(fcrc.class_info, 2, 1), ‘1’, ‘2nd, ‘) ||
decode(substr(fcrc.class_info, 3, 1), ‘1’, ‘3rd, ‘) ||
decode(substr(fcrc.class_info, 4, 1), ‘1’, ‘4th, ‘) ||
decode(substr(fcrc.class_info, 5, 1), ‘1’, ‘5th, ‘) ||
decode(substr(fcrc.class_info, 6, 1), ‘1’, ‘6th, ‘) ||
decode(substr(fcrc.class_info, 7, 1), ‘1’, ‘7th, ‘) ||
decode(substr(fcrc.class_info, 8, 1), ‘1’, ‘8th, ‘) ||
decode(substr(fcrc.class_info, 9, 1), ‘1’, ‘9th, ‘) ||
decode(substr(fcrc.class_info, 10, 1), ‘1’, ’10th, ‘) ||
decode(substr(fcrc.class_info, 11, 1), ‘1’, ’11th, ‘) ||
decode(substr(fcrc.class_info, 12, 1), ‘1’, ’12th, ‘) ||
decode(substr(fcrc.class_info, 13, 1), ‘1’, ’13th, ‘) ||
decode(substr(fcrc.class_info, 14, 1), ‘1’, ’14th, ‘) ||
decode(substr(fcrc.class_info, 15, 1), ‘1’, ’15th, ‘) ||
decode(substr(fcrc.class_info, 16, 1), ‘1’, ’16th, ‘) ||
decode(substr(fcrc.class_info, 17, 1), ‘1’, ’17th, ‘) ||
decode(substr(fcrc.class_info, 18, 1), ‘1’, ’18th, ‘) ||
decode(substr(fcrc.class_info, 19, 1), ‘1’, ’19th, ‘) ||
decode(substr(fcrc.class_info, 20, 1), ‘1’, ’20th, ‘) ||
decode(substr(fcrc.class_info, 21, 1), ‘1’, ’21st, ‘) ||
decode(substr(fcrc.class_info, 22, 1), ‘1’, ’22nd, ‘) ||
decode(substr(fcrc.class_info, 23, 1), ‘1’, ’23rd,’ ) ||
decode(substr(fcrc.class_info, 24, 1), ‘1’, ’24th, ‘) ||
decode(substr(fcrc.class_info, 25, 1), ‘1’, ’25th, ‘) ||
decode(substr(fcrc.class_info, 26, 1), ‘1’, ’26th, ‘) ||
decode(substr(fcrc.class_info, 27, 1), ‘1’, ’27th, ‘) ||
decode(substr(fcrc.class_info, 28, 1), ‘1’, ’28th, ‘) ||
decode(substr(fcrc.class_info, 29, 1), ‘1’, ’29th, ‘) ||
decode(substr(fcrc.class_info, 30, 1), ‘1’, ’30th, ‘) ||
decode(substr(fcrc.class_info, 31, 1), ‘1’, ’31st. ‘)
ELSE
‘n/a’
END days_of_month
, CASE WHEN fcrc.class_type = ‘S’ AND substr(fcrc.class_info, 32, 1) = ‘1’ THEN
‘Yes’
ELSE
‘n/a’
END last_day_of_month_ticked
, fcrc.CLASS_INFO
FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_user fu
, applsys.fnd_user u2
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
, applsys.fnd_printer_styles_tl fpst
, applsys.fnd_conc_release_classes fcrc
, applsys.fnd_responsibility_tl frt
, apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = ‘CP_STATUS_CODE’
AND fcr.phase_code = ‘P’
AND 1=1
ORDER BY fu.description, fcr.requested_start_date asc;

Advertisements

One thought on “SQL to get details of Scheduled Concurrent Program

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s