SQL to get details of Concurrent Program (FND_CONC_REQ_SUMMARY_V)

Hi Friends,

I have framed a sql query with help of my technical friends to get below details

1. Request id

2. Concurrent program full name

3. Concurrent program short name

4. Start time, and End time.

5. Runtime

6. Parameters passed to the request

7. Submitted by

8. Status code and Phase code

SELECT request_id,
user_concurrent_program_name Concurrent_Program_Full_Name,
program_short_name Concurrent_Program_Short_Name,
actual_start_date,
actual_completion_date,
FLOOR( ( (actual_completion_date – actual_start_date) * 24 * 60 * 60)
/ 3600)
|| ‘ HOURS ‘
|| FLOOR( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
|| ‘ MINUTES ‘
|| ROUND( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600
– (FLOOR( ( ( (actual_completion_date – actual_start_date)
* 24
* 60
* 60)
– FLOOR( ( (actual_completion_date
– actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
* 60)))
|| ‘ SECS ‘
time_difference,
argument_text Parameter,
requestor submited_by,
status_code,
phase_code
FROM FND_CONC_REQ_SUMMARY_V

Concurrent Manager showing status “System Hold, Fix Manager before resetting counters”.

Hi friends,

Recently after EBS upgrade to 12.1.3 CM were not functional and was showing status “System Hold, Fix Manager before resetting counters”.

Issue:
Concurrent Manager showing status “System Hold, Fix Manager before resetting counters”.

Solution:
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Go to cd $FND_TOP/bin
$ adrelink.sh force=y link_debug=y “fnd FNDLIBR”
$ adrelink.sh force=y link_debug=y “fnd FNDFS”
$ adrelink.sh force=y link_debug=y “fnd FNDCRM”
$ adrelink.sh force=y link_debug=y “fnd FNDSM”

3. Run the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).
Note 134007.1 CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables

4. Start the concurrent manager.

5. Retest the issue.

Reference :
SCHEDULE/PRERELEASER MANAGER STATUS : SYSTEM HOLD, FIX MANAGER BEFORE RESETTING [ID 985835.1]

Command to remove ^M character from a file on Unix

perl -p -i -e ‘s/\r//’ *

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;