Concurrent jobs that should run periodically

In a production environment, some concurrent jobs are necessary to keep the system running smoothly. Below list covers good candidates.

USER_CONCURRENT_PROGRAM_NAME        CONCURRENT_PROGRAM_NAME
Workflow Background Process                               FNDWFBG
Workflow Control Queue Cleanup                            FNDWFBES_CONTROL_QUEUE_CLEANU
Workflow Directory Services User/Role Validation     FNDWFDSURV
Purge Obsolete Workflow Runtime Data                  FNDWFPR
Purge Concurrent Request and/or Manager Data      FNDCPPUR (one on request logs)
Purge Concurrent Request and/or Manager Data      FNDCPPUR (one on manager logs)
Delete Diagnostic Statistics                                   DELDIAGSTAT
Purge Logs and Closed System Alerts                    FNDLGPRG
Purge Inactive Sessions                                    FNDDLTMP (delete data from table ICX_SESSIONS)
Request Set Gather Statistics Schemas (weekly run)  Gather Schema Statistics

One of the important ones is to collect statistics. A Request Set can be created for that by
Requests => Set => name it "Gather Statistics Schemas (weekly run)"
Then, Define Stages => Requests =>
Enter a Seq number and select seeded Program "Gather Schema Statistics" for each schema (AR, GL, XLA, APPS, APPLSYS, ...). Make sure the Owner is sysadmin, otherwise sysadmin may be unable to see it. If the parallel Degree 16 is not specified, it will take the default from the database (I saw 32 in a run).

If job "Purge Concurrent Request and/or Manager Data" is not scheduled to run regularly, table fnd_env_context may become very big. You may have to truncate the table as the only option of reducing its size.

References:
- What Are Concurrent Reports That Should Be Scheduled Periodically [ID 1066117.1] .
- Why is The "Purge Inactive Sessions" Concurrent Program Not Deleting Records From ICX_SESSIONS? [ID 1073768.1].

Below query find all scheduled concurrent requests (or CM requests scheduled by a user):

SELECT
    fr.responsibility_key responsibility,
    fcp.concurrent_program_name,
    fcpt.user_concurrent_program_name,
    decode(fcr.status_code,
        'A', 'Waiting',
        'B', 'Resuming',
        'C', 'Normal',
        'D', 'Cancelled',
        'E', 'Error',
        'F', 'Scheduled',
        'G', 'Warning',
        'H', 'On Hold',
        'I', 'Normal',
        'M', 'No Manager',
        'Q', 'Standby',
        'R', 'Normal',
        'S', 'Suspended',
        'T', 'Terminating',
        'U', 'Disabled',
        'W', 'Paused',
        'X', 'Terminated',
        'Z', 'Waiting') status,
    decode(fcr.phase_code,
        'C', 'Completed',
        'I', 'Inactive',
        'P', 'Pending',
        'R', 'Running') phase,
    fcr.request_id,
    fcr.parent_request_id,
    fu.user_name requestor,
    to_char(fcr.requested_start_date, 'MON-DD-YYYY HH24:MM:SS') START_DATE,
    fcr.completion_text
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs fcp,
    fnd_concurrent_programs_tl fcpt,
    fnd_user fu,
    fnd_responsibility fr
WHERE
    --fcr.status_code in ('Q', 'I') and
    --fcr.hold_flag = 'N' and
    fcr.requested_start_date > sysdate and
    fu.user_id = fcr.requested_by and
    fcr.concurrent_program_id = fcp.concurrent_program_id and
    fcr.concurrent_program_id = fcpt.concurrent_program_id and
    fcr.responsibility_id = fr.responsibility_id
    -- and fu.user_name = 'UserName'
order by fcr.requested_start_date,  fcr.request_id;

SQL scripts show R12 Profile Options

Below query will find the value on Profile option(s). It is modified from a script in Doc ID 1159313.1 "Unable To Authenticate Session Error When Login to Different R12 Instance in Same IE Session". Exact profile name is in table fnd_profile_options_tl .

SELECT p.profile_option_name, fpot.user_profile_option_name NAME,
decode(v.level_id,
             10001,'SITE',
             10002, (select 'App:'||a.application_short_name
                            from fnd_application a
                          where a.application_id = v.level_value),
             10003, (select 'Resp:'||f.RESPONSIBILITY_KEY
                            from fnd_responsibility f
                          where f.responsibility_id = v.level_value),
             10004, (select 'User:'||u.user_name
                            from fnd_user u
                          where u.user_id = v.level_value),
             10005, (select 'Server:'||n.node_name
                            from fnd_nodes n
                          where n.node_id = v.level_value),
             10006, (select 'Org:'|| o.name
                            from hr_operating_units o
                          where o.organization_id = v.level_value),
             'NOT SET') PROF_LEVEL,
              nvl(v.profile_option_value,'NOT SET') profile_option_value, fpot.description
FROM
  fnd_profile_options p,
  fnd_profile_option_values v,
  fnd_profile_options_tl fpot
WHERE
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name = fpot.profile_option_name
-- and p.profile_option_name = 'FND_DIAGNOSTICS'
-- and fpot.user_profile_option_name like 'FND: D%'
and fpot.user_profile_option_name like 'Signon%'
order by 1, v.level_id;

A query to find ALL profile Option values (modified from a script in Doc ID 2026081.1):

select n.user_profile_option_name NAME,
       decode(v.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User',
               10005, 'Server',
               10006, 'Organization',
               10007, 'ServResp',
               'UnDef') LEVEL_SET,
       decode(to_char(v.level_id),
              '10001', '',
              '10002', app.application_short_name,
              '10003', rsp.responsibility_key,
              '10004', usr.user_name,
              '10005', svr.node_name,
              '10006', org.name,
              '10007', 'depends=',
              v.level_id) "CONTEXT",
       v.profile_option_value VALUE,
       (select n.node_name
          from
               fnd_nodes n
         where
               n.node_id=level_value2) Server,
               decode(v.LEVEL_VALUE,
               -1, 'Default',
               rsp.responsibility_key) Resp,
               decode(LEVEL_VALUE_APPLICATION_ID,
               -1, 'Default',
               app.application_short_name) Application
  from fnd_profile_options p,
       fnd_profile_option_values v,
       fnd_profile_options_tl n,
       fnd_user usr,
       fnd_application app,
       fnd_responsibility rsp,
       fnd_nodes svr,
       hr_operating_units org
 where p.profile_option_id = v.profile_option_id (+)
   and p.profile_option_name = n.profile_option_name
  -- and (upper(v.profile_option_value) like '%HTTP%')   -- if you want to find the value, enable this line
  -- and p.profile_option_name like 'APPLICATIONS_HOME%'   -- if you want to find the name
   and    usr.user_id (+) = v.level_value
   and    rsp.application_id (+) = v.level_value_application_id
   and    rsp.responsibility_id (+) = v.level_value
   and    app.application_id (+) = v.level_value
   and    svr.node_id (+) = v.level_value
   and    org.organization_id (+) = v.level_value
 order by name, level_set;

A change in Profile option can make the EBS run very differently. Below script will find Profile options that were changed within one day.

 SELECT '''' || a.user_profile_option_name
       || ''' Was Updated with value '
       || b.profile_option_value mesg,
       (SELECT user_name
             FROM apps.fnd_user u
            WHERE u.user_id = b.last_updated_by) who , b.last_update_date, b.level_value
  FROM apps.fnd_profile_options_vl a,
       apps.fnd_profile_option_values b,
       apps.fnd_user c
 WHERE a.profile_option_id = b.profile_option_id
   AND b.last_updated_by = c.user_id
   AND (   b.last_update_date > SYSDATE - 1   -- <- put a different number here if needed
        OR b.creation_date > SYSDATE - 1
       );
NOTE: Not sure why, but this query is not accurate sometimes (when the value was changed to null).

NOTES: If you have to change a Profile option but the EBS webpage is not available, try FND_PROFILE package (for example):

declare value Boolean;

begin

value := FND_PROFILE.SAVE('SIGNON_PASSWORD_CASE','1','SITE');

end;

/

Clean Java cache

Sometimes, changes to Profile options do not take effect until Apache server is bounced. This delay may be due to Java Caching.  Java caching provides a powerful, flexible, easy to use mechanism for storing database results and other Java objects in-memory for repeated usage, thereby improving application performance.

Steps for cleaning Java cache (do NOT do it in a busy production system):

1. Log into EBS with the Functional Administrator responsibility
2. Choose the Core Services Tab => Caching Framework Sub-Menu
3. Proceed to choose 'Global Configuration' from the left hand side menu that appears
4. In the far right choose 'Clear all Cache' button

Oracle documents on EBS Caching:

1. Note 759038.1 How To Clear The Cache Using Functional Administrator?
2. Note 455194.1 Diagnosing database invalidation issues with Java Cache for eBusiness Suite
3. Note 742107.1 How To Clear Caches (Apache/iAS, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite?
4. Note 275879.1 Oracle Applications Java Caching Framework Developer’s Guide Release 11i