Oracle apps query to find incompatible programs blocking a concurrent request

•By admin

Oracle apps query to find incompatible programs blocking a concurrent request
Oracle EBS incompatible concurrent requests sql query
Concurrent program incompatibility query for pending standby requests

Run the below query if you see any concurrent program in pending standby status for long time.
The below query returns the incompatible program requests running due to which the request is in standby status.
SQL Querysql
1SELECT fsrn.program,
2       fsrn.request_id, -- fstr.concurrent_program_id,
3fstr.program incompatible_program,
4                                                      fstr.request_id incompatible_prog_req_id,
5
6  (SELECT meaning
7   FROM apps.fnd_lookups
8   WHERE lookup_type = 'CP_PHASE_CODE'
9     AND lookup_code = fstr.phase_code) phase,
10
11  (SELECT meaning
12   FROM apps.fnd_lookups
13   WHERE lookup_type = 'CP_STATUS_CODE'
14     AND lookup_code = fstr.status_code) status,
15                                                      ROUND ((fstr.actual_completion_date - fstr.actual_start_date) * 24 * 60, 2) time_taken_minutes,
16                                                      DECODE (fstr.actual_completion_date,
17                                                              NULL,
18                                                              ROUND ((SYSDATE - fstr.actual_start_date) * 24 * 60, 2)) running_for_minutes,
19                                                             fstr.actual_start_date,
20                                                             fstr.actual_completion_date
21FROM apps.fnd_concurrent_program_serial fcs,
22     apps.fnd_conc_req_summary_v fstr,
23     apps.fnd_conc_req_summary_v fsrn
24WHERE 1 = 1 -- running_concurrent_program_id = 123123
25AND fsrn.request_id = 123456 --Request id which is in standby status
26--AND fsrn.program = '' --Or concurrent program name which is in standby status
27AND to_run_concurrent_program_id = fstr.concurrent_program_id
28  AND running_concurrent_program_id = fsrn.concurrent_program_id
29  AND (fstr.actual_start_date BETWEEN fsrn.requested_start_date AND fsrn.actual_start_date
30       OR fstr.actual_completion_date BETWEEN fsrn.requested_start_date AND fsrn.actual_start_date)-- AND fsrn.phase_code = 'P' --Pending
31-- AND fstr.status_code != 'D' -- not cancelled
32ORDER BY fstr.actual_start_date;

Related posts: