To find the concurrent program submission interval

SELECT req.request_id
, decode (prg.user_concurrent_program_name, 'Report Set', 'Report Set:' || req.description, prg.user_concurrent_program_name) AS name
, argument_text as parameters
, req.resubmit_interval
, nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')) AS schedule_type
, decode (nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')),
'Periodically', 'Every ' || req.resubmit_interval || ' ' || lower(req.resubmit_interval_unit_code) || ' from ' || lower(req.resubmit_interval_type_code) || ' of previous run',
'Once', 'At :' || to_char (req.requested_start_date, 'DD-MON-RR HH24:MI'), 'Every: ' || crc.class_info) as schedule
, fus.user_name as owner
, to_char(requested_start_date,'DD-MON-YYYY HH24:MI:SS') as next_submission
FROM
apps.fnd_concurrent_programs_tl prg
, apps.fnd_concurrent_requests req
, apps.fnd_user fus
, apps.fnd_conc_release_classes crc
WHERE 1=1
AND prg.application_id = req.program_application_id
AND prg.concurrent_program_id = req.concurrent_program_id
AND req.requested_by = fus.user_id
AND req.phase_code = 'P'
AND req.requested_start_date > sysdate 
AND prg.language = 'US'
AND crc.release_class_id(+) = req.release_class_id
AND crc.application_id(+) = req.release_class_app_id
ORDER BY name

To find out profile option set at which level

select substr(ot.user_profile_option_name,1,35),
substr(o.profile_option_name,1,30),
decode(level_id,10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User') lev,
decode(level_id,10001, null, 10002, a.application_name, 10003, r.responsibility_name, 10004, u.user_name) lev_value,
r.RESPONSIBILITY_ID,
v.profile_option_value opt_value
from fnd_profile_option_values v,
fnd_profile_options o,
fnd_profile_options_tl ot,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_user u
where LEVEL_VALUE = decode(level_id, 10004, u.user_id, 10003, r.responsibility_id, 10002, a.application_id,10001,0)
and v.profile_option_id = o.profile_option_id
and v.application_id = o.application_id
and a.application_id (+) = v.level_value
and r.responsibility_id (+) = v.level_value
--and o.application_id = 275
and u.user_id (+) = v.level_value
and ot.profile_option_name = o.profile_option_name
and nvl(ot.language,'US') = 'US'
and sysdate between nvl(o.start_date_active, sysdate)
and nvl(o.end_date_active,sysdate)
and v.profile_option_value is not null
and ot.user_profile_option_name like 'PJI_%Debug%' order by 1

Query to find the concurrent program status which ran today

SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) = trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   and fcp.user_concurrent_program_name = 'Program name'
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
 ORDER BY fcr.request_date,
          fcr.request_id DESC;
     

When responsibility added to user and it is not showing in the web based application then run the below query to see the responsibility added or not.

Newly added responsibilities not being shown is another symptom of this delay in Java Cache processing

Run the following script to verify that the data is correct:

set linesize 155;
set pagesize 200;
set verify off;

column USER_ID format 9999999999;
column RESP_ID format 9999999999;
column RESP_NAME format a60;
column RESP_APP_ID format 9999999999;
column SEC_GRP_ID format 9999999999;

select fu.user_id, fo.RESPONSIBILITY_ID resp_id, RESPONSIBILITY_NAME resp_name, fo.RESPONSIBILITY_APPLICATION_ID resp_app_id, fo.SECURITY_GROUP_ID sec_grp_id
from fnd_user_resp_groups fo, fnd_responsibility_vl fv, fnd_user fu
where fu.user_id=fo.user_id
and fu.user_name='<affected_user>'
and fo.RESPONSIBILITY_ID=fv.RESPONSIBILITY_ID
and fo.RESPONSIBILITY_APPLICATION_ID=fv.APPLICATION_ID;