Wednesday, October 6, 2010

How to Find Out Scheduled Concurrent Requests in Oracle Applications 11i

Through this query you will find all the scheduled concurrent program that are running on Oracle Apps.
---------------------------------------------------------------------------------
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
cr.resubmit_interval
' '
cr.resubmit_interval_unit_code
' FROM '
cr.resubmit_interval_type_code
' OF PREV RUN',
'ONCE', 'AT :'
TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Friday, May 28, 2010

Concurrent Programs Fail To Submit The Next Schedule Run Even After Completing Successfully

Currently, we are facing similar problem in Production instance. Oracle has shared a good document with detail explanation.
You can also login into metalink.oracle.com and search for document: 1061218.1
I have copied document detailed from metalink.
Symptoms
Concurrent request will not be re-scheduled. The reason the jobs stop rescheduling themselves is because the schedule is removed from the fnd_conc_release_classes table.
Cause
What happens is this:
1. Purge process is scheduled to purge the request each 7 day (as example)
2. We submit a scheduled requets (X1). (A record is created in table fnd_conc_release_classes with field OWNER_REQ_ID populated with X1)
3. The request X1 is executed a reschedule a new request (X2.) the record in fnd_conc_release_classes get update the field OWNER_REQ_ID as X2.
4. The request X2 is executed, when the system schedule the new request X3 and the request finish in warning with the error message, but at the end of the process the update of table fnd_conc_release_classes fail, then the request X3 is scheduled but the table fnd_conc_release_classes has value X2 in field OWNER_REQ_ID.
The request continue scheduling (X4,X5,Xn) but at the 7 days the FNDCPPUR purge request X2 and the record in table fnd_conc_release_classes. The last scheduled (Xn) request executed has not any error message and we can not get any error, because the requested that origin the issue was purged, we can only check that does not exists a record in table fnd_conc_release_classes with the RELEASE_CLASS_ID that have the record of request Xn in fnd_concurrent_requests.
Solution
This issue is described in Bug 7329963 FAILING CONCURRENT SCHEDULES. This bug is still open and Development works on it.Until now there is no fix for the error handling. The fix is expected for 11.5.10.8RUPBut as a workaround you may find out why a scheduled request fails with warning.If non of the scheduled requests fail, then the re-scheduling will work correctly.