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

Error messages on EBS Forms

1. "XXXX is not a valid responsibility for the current user. Please contact your System Administrator."

- First possibility is that this responsibility has an End date for this user. Check user's account.

- After adding a responsibility to a user, the user may receive above message. And the attached responsibility may not appear in the Home Page until the Apache is bounced.

One solution could be to clear the cache:
Navigate to Functional administrator > Core Service > Caching Framework > Global Configuration > Clear All Cache

Another place to check:
Search for profile 'Applications Start Page'. Delete the value set at user level (if any).

Also make sure Workflow Service Components are up and running (OAM -> Workflow Manager (dropdown) -> Service Components):
   . Workflow Deferred Agent Listener
   . Workflow Java Deferred Agent Listener

2. "Function not available to this responsibility. Change responsibilities or contact System Administrator"

- If it is on a custom form, make sure its path CUSTOM_TOP is added to $INST_TOP/ora/10.1.2/forms/server/default.env file.

- Use submenu item Help --> Diagnostics --> Examine.  Then, click the "Examine Fields and variable values" to troubleshoot. Reference Doc ID 1079951.1.

- If this message shows in R12.1.3 while trying to access Help --> Diagnostics, change the profile "Utilities:Diagnostics" to Yes at user or responsibility level. Please see Doc ID 1200743.1.

3.  If  R12.1 "adformsctl.sh start" gets error, check logs:

$LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log

When the Form service is starting, it checks whether latest formsapp.ear is deployed. If it finds a newer version, Form service will take longer to start and say message:
==========================================
*** Latest formsapp.ear has NOT been deployed ***
Deploying the latest EAR file...
==========================================
See DocID 1210660.1 (Forms Startup Script adformsctl.sh Shows Deployment Message While Starting Forms Services).

4.  How to deploy Forms EAR (Enterprise ARchive) file, such as formsapp.ear (under $ORACLE_HOME/forms/j2ee) ?

DocID 397174.1 (Deploying a New EAR File in Oracle Applications Release 12) gives two key steps:
1) Edit file (with a temp password) - $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml
2) Run deployment - $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
    (if all work, it shall say "No error encountered." at the end)

DocID 1945012.1 also gives a way to manually deploy formsapp.ear (for Oracle Forms ?? vs Oracle EBS R12). But I never try it:
$ dcmctl redeployApplication -f $ORACLE_HOME/forms/j2ee/formsapp.ear -a formsapp -co OC4J_BI_Forms

Use adctrl to stop a worker of adpatch

1.  Skip a script when it has been "Running" status for too long

During patching, a program, such as cstpostimportaad.sql, stays in "Running" status forever (Doc ID 1453612.1). To stop and skip it (and you may re-run the program after adpatch finishes):

$ adctrl
==> 4. Tell manager that a worker failed its job
==> 3. Tell worker to quit
==> 6. Restart a worker on the current machine. (Otherwise, worker keeps staying in "Assigned")

2.  A worker stuck in "Failed" status.  How to stop the worker and skip this program?

Worker  Code      Context             Filename           Status
----------  --------  ----------------------  -------------------  --------------
       3  Done   AutoPatch R120     EGOSILDU.sql   Failed

$ adctrl
==> 8    (Note: 8 is NOT listed on the menu)
 
       Now, the status becomes:
       3  Done  AutoPatch R120     EGOSILDU.sql    Skip & restart

==> 6. Restart a worker on the current machine

In another case, after I manually uploaded a failed workflow file to database (Doc ID 2581597.1), I used adcrtl to continue the adpatch session:

Worker  Code      Context             Filename           Status

----------  --------  ----------------------  -------------------  --------------

     2  Run       AutoPatch R120     POSSPAPM.wft     FAILED

==> 8
Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : 2
Status changed to 'Skip & restart' for worker 2.
Review the messages above, then press [Return] to continue.

Once above steps in adctrl were done, adpatch automatically continued to next jobs.

3.  How to re-start "adpatch" from scratch?

Sometimes, you have to start adpatch again to clean all memories kept in previous run, such as change of a schema password in the middle of adpatch session. Even using "3. Tell worker to quit" on all workers, adpatch will still run on OS level. You have to use "kill -9" to stop it.