EBS : Worflow related queries

1. To check workflow mailer service current status

  sqlplus apps/<apps_pwd>
  select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

  sqlplus apps/<apps_pwd>
  select component_status
    from apps.fnd_svc_components
   where component_id =
    (select component_id
     from apps.fnd_svc_components
     where component_name = 'Workflow Notification Mailer');

  Values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

3. Starting Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       select component_id
      into m_mailerid
      from fnd_svc_components
      where component_name = 'Workflow Notification Mailer';
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);        -- Starting the Workflow Mailer
      commit;
  end;
  /


4. Stopping Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       select component_id
      into m_mailerid
      from fnd_svc_components
      where component_name = 'Workflow Notification Mailer';
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);        -- Stopping the Workflow Mailer
      commit;
  end;
  /


5. To find the open notifications count by message_type

select message_type, mail_status, count(*) from wf_notifications where status = 'OPEN' GROUP BY MESSAGE_TYPE, MAIL_STATUS

6. To find the alerts notifications we need to query WF_NOTIFICATION_OUT queue

select corr_id, retry_count, msg_state, count(*) from applsys.aq$wf_notification_out group by corr_id, msg_state, retry_count order by count(*) desc;

EBS : FRM-92100

Error:

While java is loading FRM-92100 error is coming in all Forms.

Solution:

1. Take the backup of $CONTEXT_FILE
2. Change the context file parameter "s_forms_jvm_start_options", and add parameter:
-Doracle.net.disableOob=true
FROM:
<forms_jvm_start_options oa_var="s_forms_jvm_start_options">-server -verbose:gc -Xmx256M -Xms64M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/d01/UAT/inst/apps/UAT_erpapp02/ora/10.1.3/j2ee/forms/config/jazn.xml</forms_jvm_start_options>
TO:
<forms_jvm_start_options oa_var="s_forms_jvm_start_options">-server -verbose:gc -Xmx256M -Xms64M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/d01/UAT/inst/apps/UAT_erpapp02/ora/10.1.3/j2ee/forms/config/jazn.xml -Doracle.net.disableOob=true</forms_jvm_start_options>
3. Run Autoconfig
4. Restart the application services

Reference : Intermittently Occurs FRM-92100 For All EBS Forms With "recv failed" Error Code (Doc ID 1908029.1)

EBS : Login page alignment issue as Arabic login

Error:

When the login page is changed to arabic login, Some of the login areas are displayed nearly out of the browser window and USERNAME and PASSWORD fields are aligned on the left hand side vs centered.

Solution:

1) Log into the application as the "System administrator" responsibility
2) Choose Profile > System
3) Click in the USER field and add the username GUEST
4) Set the following profile options at user level

Personalize Self-Service Defn = No

5) Save the changes
6) Bounce the E-Business suite web tier services
7) Re-test the login issue

Reference : Main Login Page Layout Issue In BIDI Languages such as Arabic and Hebrew (Doc ID 453279.1)

EBS : For specific users after login Null pointer exceptions

Error:

## Detail 0 ##
java.lang.NullPointerException
at java.util.Hashtable.put(Unknown Source)
at oracle.apps.fnd.common.DBPreferenceStore.load(DBPreferenceStore.java:162)
at oracle.apps.fnd.wf.worklist.webui.NtfWorklistCO.processRequest(NtfWorklistCO.java:185)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)


Solution:

CREATE TABLE FND_USER_PREFERENCES_BKP AS SELECT * FROM FND_USER_PREFERENCES;
UPDATE FND_USER_PREFERENCES SET PREFERENCE_VALUE='NtfSubject,' WHERE USER_NAME='&User_name' AND PREFERENCE_NAME='ADVANCED_WL_SORT_PREF';
COMMIT;

Reference: You have encountered an unexpected Error for Some Users Login. Stacktrace shows java.lang.NullPointerException related to NtfWorklistCO.class (Doc ID 1684285.1)

DB : Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed

Error:
oratest@myserver database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 61931 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 19999 MB    Passed
Checking monitor: must be configured to display at least 256 colors
    >>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.    Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

Continue? (y/n) [n] n

User Selected: No

Exiting Oracle Universal Installer, log for this session can be found at /d01/oracle/app/oraInventory/logs/installActions2015-06-24_10-03-30AM.log
[oratest@myserver database]$

Solution:

As a root user in VNC type xhost + to continue the installation
[root@myserver TEST]# xhost +
access control disabled, clients can connect from any host

DB : Installing oracle database fails with inventory owner permission denied

When installing new database as "oratest" new user on the existing server with other databases running in it the installation failed with below error

Error:

[oratest@myserver database]$ ./runInstaller
You do not have sufficient permissions to access the inventory '/d01/oracle/app/oraInventory'. Installation cannot continue. It is required that the primary group of the install user is same as the inventory owner group. Make sure that the install user is part of the inventory owner group and restart the installer.: Permission denied
[oratest@myserver database]$

Solution:
Change the oratest user group to same as the group of oraInventory

As a root user change the "oratest" group to "dba" as below

useradd -G dba oratest

EBS : dbTier autoconfig fails with afdbprf.sh adcrobj.sh

Error:

adcvmlog.xml renamed to /oradb/oracle/11.2.0.3/appsutil/log/TEST_drebsdb01/06080947/adcvmlog.xml.06080948


[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /oradb/oracle/11.2.0.3/appsutil/install/TEST_drebsdb01
      afdbprf.sh              INSTE8_PRF         1

  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /oradb/oracle/11.2.0.3/appsutil/install/TEST_drebsdb01
      adcrobj.sh              INSTE8_APPLY       1


AutoConfig is exiting with status 2

AutoConfig execution completed on Mon Jun  8 09:48:27 2015

Time taken for AutoConfig execution to complete : 0 mins  49 secs


Solution:

Enter the correct apps password while running autoconfig and check whether APPS account is locked by connecting as SYSDBA
SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME='APPS';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------------
APPS                           LOCKED                           08-JUN-15

SQL> alter user apps account unlock;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME='APPS';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------------
APPS                           OPEN

now re-run the autoconfig in dbTier