Find Workflow Notification Mailer
SQL> SELECT email_address,name, nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference)FROM wf_rolesWHERE name like upper('%xxx%');
SQL> select NOTIFICATION_ID,STATUS,MAIL_STATUS from WF_NOTIFICATIONS where TO_USER='UserName';
SQL> SELECT status,mail_status,MESSAGE_TYPE,MESSAGE_NAME,ITEM_KEY from WF_NOTIFICATIONS WHERE notification_id='2732013';
SQL> select mail_status,count(*) from WF_NOTIFICATIONS group by mail_status;
SQL> select NOTIFICATION_ID,STATUS,MAIL_STATUS from WF_NOTIFICATIONS where TO_USER='UserName';
SQL> SELECT status,mail_status,MESSAGE_TYPE,MESSAGE_NAME,ITEM_KEY from WF_NOTIFICATIONS WHERE notification_id='2732013';
SQL> select mail_status,count(*) from WF_NOTIFICATIONS group by mail_status;
COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 = EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_DEFERRED
GROUP BY CORRID, STATE;
SET LINES 150
COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 =
EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_NOTIFICATION_OUT
GROUP BY CORRID, STATE;
COL COMPONENT_NAME FORMAT A60
COL COMPONENT_STATUS FORMAT A20
COL STARTUP_MODE FORMAT A20
SET LINES 120
SET PAGES 1000
SELECT COMPONENT_NAME, COMPONENT_STATUS, STARTUP_MODE FROM FND_SVC_COMPONENTS;
SET LINES 160
COL COMPONENT_NAME FORMAT A30
COL PARAMETER_NAME FORMAT A40
COL PARAMETER_VALUE FORMAT A40
SELECT A.COMPONENT_ID, A.COMPONENT_NAME, B.PARAMETER_ID, C.PARAMETER_NAME, B.PARAMETER_VALUE
FROM FND_SVC_COMPONENTS A, FND_SVC_COMP_PARAM_VALS B, FND_SVC_COMP_PARAMS_B C
WHERE A.COMPONENT_ID = B.COMPONENT_ID AND B.PARAMETER_ID = C.PARAMETER_ID
AND UPPER(A.COMPONENT_NAME) LIKE '%MAILER%'
ORDER BY A.COMPONENT_ID, A.COMPONENT_NAME, C.PARAMETER_NAME;
How to change Apps 11i form color change after cloning
To differentiate dev/test from prod, we can change the form color by changing some profile options.
Profile option change to be done:
1. Set the Java Look and Feel profile option value to oracle. If you set it to generic, it will take the default values and you wont be able to set your own color.
2. Then set Java Color Scheme profile option value to any of the LOVs as per your choice.
Remember to set this at site level
NOTE: It won't get effect immediately, you have to log out from
application and again you have to relogin to view color change effect
Profile option change to be done:
1. Set the Java Look and Feel profile option value to oracle. If you set it to generic, it will take the default values and you wont be able to set your own color.
2. Then set Java Color Scheme profile option value to any of the LOVs as per your choice.
Remember to set this at site level
NOTE: It won't get effect immediately, you have to log out from
application and again you have to relogin to view color change effect
Rolling Oracle Clusterware Upgrade 10.2.0.3 to 10.2.0.4
1. Stop both DB nodes
2. Shut down all listeners
3. Log in as the root user Shutdown the CRS crsctl stop crs
Go to following location and check the current CRS version.
cd /CRS/app/10.2.0/bin
./crsctl query crs softwareversion
./crsctl query crs activeversion
Unzip 10.2.0.4 patchset 6810189
Run ./runInstaller
WelcomeScreen
choose the CRS_home and Click Next
selection the CRS_home will automatically select and upgrade the CRS software
Click Next
Click Next
Click Install
10.2.0.4 binaries automatically installed in all nodes. So please run the root102.sh in all the database nodes.
The following instructions are displayed on the Oracle Universal Installer screen:
To complete the installation of this patch set, perform the following tasks on each node:
Log in as the root user and enter the following command to shut down the Oracle Clusterware:
# CRS_home/bin/crsctl stop crs
Run the root102.sh script to automatically start the Oracle Clusterware on the patched node:
# CRS_home/install/root102.sh
Database Node 1
Please follow the same steps to rest of the all nodes.
The following instructions are displayed on the Oracle Universal Installer screen:
To complete the installation of this patch set, perform the following tasks on each node:
Log in as the root user and enter the following command to shut down the Oracle Clusterware:
# CRS_home/bin/crsctl stop crs
Run the root102.sh script to automatically start the Oracle Clusterware on the patched node:
# CRS_home/install/root102.sh
Database Node 2
After completing the above steps, please check the CRS version.
./crsctl query crs softwareversion
CRS software version on node [oradbtest1] is [10.2.0.4.0]
./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.3.0]
2. Shut down all listeners
3. Log in as the root user Shutdown the CRS crsctl stop crs
Go to following location and check the current CRS version.
cd /CRS/app/10.2.0/bin
./crsctl query crs softwareversion
./crsctl query crs activeversion
Unzip 10.2.0.4 patchset 6810189
Run ./runInstaller
WelcomeScreen
choose the CRS_home and Click Next
selection the CRS_home will automatically select and upgrade the CRS software
Click Next
Click Next
Click Install
10.2.0.4 binaries automatically installed in all nodes. So please run the root102.sh in all the database nodes.
The following instructions are displayed on the Oracle Universal Installer screen:
To complete the installation of this patch set, perform the following tasks on each node:
Log in as the root user and enter the following command to shut down the Oracle Clusterware:
# CRS_home/bin/crsctl stop crs
Run the root102.sh script to automatically start the Oracle Clusterware on the patched node:
# CRS_home/install/root102.sh
Database Node 1
Please follow the same steps to rest of the all nodes.
The following instructions are displayed on the Oracle Universal Installer screen:
To complete the installation of this patch set, perform the following tasks on each node:
Log in as the root user and enter the following command to shut down the Oracle Clusterware:
# CRS_home/bin/crsctl stop crs
Run the root102.sh script to automatically start the Oracle Clusterware on the patched node:
# CRS_home/install/root102.sh
Database Node 2
After completing the above steps, please check the CRS version.
./crsctl query crs softwareversion
CRS software version on node [oradbtest1] is [10.2.0.4.0]
./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.3.0]
Notifications Not Being Received After Autoconfig Is Run
SolutionTo implement the solution, please execute the following steps:
1. Log into the Oracle Applications Manager.
2. Click on Site Map.
3. Click on Notification Mailer under Workflow.
4. Click on the Edit button for the active mailer.
5. Click Next to step 3 that lists the Inbound EMail Account and Outbound EMail Account.
6. Confirm that the servers names are correct with the IMAP and SMTP servers and change as needed.
7. Click Next and then Click on Finish.
8. Stop and re-start the workflow mailer services within OAM and test.
9. If the issue is resolved, please migrate the solution as appropriate to other environments.
Permanent solutions:
Autoconfig Overwriting The SMTP Server Name For The Java Mailer
[applmgr@test]$ cat UAT_test.xmlgrep s_smtphost
hostname oa_var= s_smtphost test hostname
[applmgr@test]$ cat UAT_test.xmlgrep s_smtpdomainname
domain oa_var=s_smtpdomainname doyen.com domain
1. Update the following parameters in context file on the Concurrent Manager Application Tier node under oa_smtp_server in OAM to the correct Outbound Server Name assigned to the Workflow Mailer.
Example
SMTP Server Host (s_smtphost)
hostname oa_var= s_smtphost mailtest hostname
Email Server Domain (s_smtpdomainname)
2. The next time autoconfig runs on the node it will contain correct setting.
1. Log into the Oracle Applications Manager.
2. Click on Site Map.
3. Click on Notification Mailer under Workflow.
4. Click on the Edit button for the active mailer.
5. Click Next to step 3 that lists the Inbound EMail Account and Outbound EMail Account.
6. Confirm that the servers names are correct with the IMAP and SMTP servers and change as needed.
7. Click Next and then Click on Finish.
8. Stop and re-start the workflow mailer services within OAM and test.
9. If the issue is resolved, please migrate the solution as appropriate to other environments.
Permanent solutions:
Autoconfig Overwriting The SMTP Server Name For The Java Mailer
[applmgr@test]$ cat UAT_test.xmlgrep s_smtphost
[applmgr@test]$ cat UAT_test.xmlgrep s_smtpdomainname
domain oa_var=s_smtpdomainname doyen.com domain
1. Update the following parameters in context file on the Concurrent Manager Application Tier node under oa_smtp_server in OAM to the correct Outbound Server Name assigned to the Workflow Mailer.
Example
SMTP Server Host (s_smtphost)
Email Server Domain (s_smtpdomainname)
2. The next time autoconfig runs on the node it will contain correct setting.
Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
1. Download the JRE Plug-in interoperability patch - 6863618
2. Download the Sun JRE Plug-in -
1. jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe
or
2. jre-6u12-windows-i586-p.exe
3. Rename the JRE Plug-in and Place it on Webserver
jre-1_5_0_17-windows-i586-p.exe
X means current updated version,
Example
jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe
If you have more then one apps node, need to place all nodes.
4. Shutdown Apps tier
5. Place the JRE 5 Plug-in on the Web Application Tier
Use FTP or WINSCP Tool
Move the jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe file to the web application tier and place it in all apps nodes
$COMMON_TOP/util/jinitiator
6. Enable Maintenance Mode
We have forms 18 patchset, so that i applied following patch for JRE upgrade, If you have less then forms patch 18, first you should upgrade forms 18 or later. If you have forms 19 or later please follow the metalink note id: 290807.1
Developer 6i Patchset 18 Users
Developer 6i Patchset 18 (6.0.8.27.x) users must apply the forms focus fix patches 7362389 and 5884875.
7. Apply Prerequisite Patche 7362389
Note: Just follow the patch readme.
8. Apply Prerequisite Patch 5884875
Note: Just follow the patch readme.
8. Apply JRE Interoperability Patch - 6863618 - Use adpatch
Apply the Patch Driver
If you are upgrading to the JRE native plug-in for the first time, please apply the interop patch using AutoPatch.
Multiple Application-Tier Web Server Nodes
If you have multiple web application tier server nodes, repeat the steps above to apply the patch driver in the JRE Interoperability Patch against all other web application tier server nodes.
9 . Run below script on command prompt
Run the txkSetPlugin.sh script from the//fnd/bin/ directory, against the web node of your middle tier, where is the directory in which you unzipped this patch.
Run the txkSetPlugin.sh Script
$txkSetPlugin.sh 15017
It will ask following input from you, please provide right path.
/u01/oracle/appltestAPPSORA.env
/u01/oracle/appltest/admin/DBA_oraappsdba.xml
Password for the APPS user in the database :apps
Example:
[applmgr@testoraapps bin]$ txkSetPlugin.sh 15017
/u01/oracle/testappl/fnd/11.5.0/bin
Starting interoperability upgrade script...
Sending logfile output to:
/u01/oracle/testappl/admin/log/txkSetPlugin.log
Using the context file:
/u01/oracle/testappl/admin/u01_oraappsu01.xml
Using APPLSYS file:
/u01/oracle/testappl/APPSu01_oraappsu01.env
Enter the password for the Oracle database APPS user [APPS] : apps
Using /u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/bin/java to execute java programs
Getting J2SE parameter values...
Copy oaj2se.exe to html dir...
Copying /u01/oracle/u01comn/util/jinitiator/j2se15017.exe to /u01/oracle/u01comn/html/oaj2se.exe ...
Backing up the Application Context File...
Backing up /u01/oracle/testappl/admin/u01_oraappsu01.xml to /u01/oracle/testappl/admin/u01_oraappsu01.xml.prej2se15017
Using APPSWEB file /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg
Backing up the appsweb file...
Backing up /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg to /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg.prej2se15017
Updating XML context file with new parameters...
Updated Plug-in type to jdk
Updated JInitiator version name to 1.5.0_17
Updated JInitiator mimetype to 1.5.0_17
Updated JInitiator classid to CAFEEFAC-0015-0000-0017-ABCDEFFEDCBA
Updated J2SE mimetype to 1.5.0_17
Updated J2SE Classid to CAFEEFAC-0015-0000-0017-ABCDEFFEDCBA
The log file for this session is located at: /u01/oracle/testappl/admin/u01_oraappsu01/log/04280720/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using APPL_TOP location : /u01/oracle/testappl
Classpath : /u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/jre/lib/rt.jar:/u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/lib/dt.jar:/u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/lib/tools.jar:/u01/oracle/u01comn/java/appsborg2.zip:/u01/oracle/u01comn/java
Using Context file : /u01/oracle/testappl/admin/u01_oraappsu01.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring ABM_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
Upgrading j2se15017 for Oracle Workflow...
Oracle Workflow upgraded successfully...
Done
10. Disable Maintenance Mode
11. Startup your apps nodes
11. Login apps, first it will take time to download for JRE.
Reverting to a Previous JRE Plug-in/JInitiator Version
If you wish to revert back to previously installed version of JRE to be used in your Application, please re-install the required version by re-running the txkSetPlugin script with the appropriate parameters. This will automatically overwrite the JRE parameters in the configuration files.
To revert back to a version of JInitiator, you must also run the txkSetPlugin script with the appropriate parameters and not the old jinit.sh or jinit.cmd scripts that were used for JInitiator upgrades previously. Using JInitiator 1.3.1.28 as an example the command would be;
UNIX Users
$ txkSetPlugin.sh 13121
Reference:
Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
Doc ID: 290807.1
2. Download the Sun JRE Plug-in -
1. jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe
or
2. jre-6u12-windows-i586-p.exe
3. Rename the JRE Plug-in and Place it on Webserver
jre-1_5_0_17-windows-i586-p.exe
X means current updated version,
Example
jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe
If you have more then one apps node, need to place all nodes.
4. Shutdown Apps tier
5. Place the JRE 5 Plug-in on the Web Application Tier
Use FTP or WINSCP Tool
Move the jre-1_5_0_17-windows-i586-p.exe to j2se15017.exe file to the web application tier and place it in all apps nodes
$COMMON_TOP/util/jinitiator
6. Enable Maintenance Mode
We have forms 18 patchset, so that i applied following patch for JRE upgrade, If you have less then forms patch 18, first you should upgrade forms 18 or later. If you have forms 19 or later please follow the metalink note id: 290807.1
Developer 6i Patchset 18 Users
Developer 6i Patchset 18 (6.0.8.27.x) users must apply the forms focus fix patches 7362389 and 5884875.
7. Apply Prerequisite Patche 7362389
Note: Just follow the patch readme.
8. Apply Prerequisite Patch 5884875
Note: Just follow the patch readme.
8. Apply JRE Interoperability Patch - 6863618 - Use adpatch
Apply the Patch Driver
If you are upgrading to the JRE native plug-in for the first time, please apply the interop patch using AutoPatch.
Multiple Application-Tier Web Server Nodes
If you have multiple web application tier server nodes, repeat the steps above to apply the patch driver in the JRE Interoperability Patch against all other web application tier server nodes.
9 . Run below script on command prompt
Run the txkSetPlugin.sh script from the
Run the txkSetPlugin.sh Script
$txkSetPlugin.sh 15017
It will ask following input from you, please provide right path.
/u01/oracle/appltestAPPSORA.env
/u01/oracle/appltest/admin/DBA_oraappsdba.xml
Password for the APPS user in the database :apps
Example:
[applmgr@testoraapps bin]$ txkSetPlugin.sh 15017
/u01/oracle/testappl/fnd/11.5.0/bin
Starting interoperability upgrade script...
Sending logfile output to:
/u01/oracle/testappl/admin/log/txkSetPlugin.log
Using the context file:
/u01/oracle/testappl/admin/u01_oraappsu01.xml
Using APPLSYS file:
/u01/oracle/testappl/APPSu01_oraappsu01.env
Enter the password for the Oracle database APPS user [APPS] : apps
Using /u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/bin/java to execute java programs
Getting J2SE parameter values...
Copy oaj2se.exe to html dir...
Copying /u01/oracle/u01comn/util/jinitiator/j2se15017.exe to /u01/oracle/u01comn/html/oaj2se.exe ...
Backing up the Application Context File...
Backing up /u01/oracle/testappl/admin/u01_oraappsu01.xml to /u01/oracle/testappl/admin/u01_oraappsu01.xml.prej2se15017
Using APPSWEB file /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg
Backing up the appsweb file...
Backing up /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg to /u01/oracle/u01comn/html/bin/appsweb_u01_oraappsu01.cfg.prej2se15017
Updating XML context file with new parameters...
Updated Plug-in type to jdk
Updated JInitiator version name to 1.5.0_17
Updated JInitiator mimetype to 1.5.0_17
Updated JInitiator classid to CAFEEFAC-0015-0000-0017-ABCDEFFEDCBA
Updated J2SE mimetype to 1.5.0_17
Updated J2SE Classid to CAFEEFAC-0015-0000-0017-ABCDEFFEDCBA
The log file for this session is located at: /u01/oracle/testappl/admin/u01_oraappsu01/log/04280720/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using APPL_TOP location : /u01/oracle/testappl
Classpath : /u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/jre/lib/rt.jar:/u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/lib/dt.jar:/u01/oracle/u01comn/util/java/1.5/jdk1.5.0_11/lib/tools.jar:/u01/oracle/u01comn/java/appsborg2.zip:/u01/oracle/u01comn/java
Using Context file : /u01/oracle/testappl/admin/u01_oraappsu01.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring ABM_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
Upgrading j2se15017 for Oracle Workflow...
Oracle Workflow upgraded successfully...
Done
10. Disable Maintenance Mode
11. Startup your apps nodes
11. Login apps, first it will take time to download for JRE.
Reverting to a Previous JRE Plug-in/JInitiator Version
If you wish to revert back to previously installed version of JRE to be used in your Application, please re-install the required version by re-running the txkSetPlugin script with the appropriate parameters. This will automatically overwrite the JRE parameters in the configuration files.
To revert back to a version of JInitiator, you must also run the txkSetPlugin script with the appropriate parameters and not the old jinit.sh or jinit.cmd scripts that were used for JInitiator upgrades previously. Using JInitiator 1.3.1.28 as an example the command would be;
UNIX Users
$ txkSetPlugin.sh 13121
Reference:
Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
Doc ID: 290807.1
After Cloning DB Tier Service Name Shows PROD Name
After Cloning DB Tier, Service Name shows like this. This wrong, So we need do the folloing steps.
Error Message:
sql > show parameter service
service_names string UAT, SYS$APPLSYS.WF_CONTROL.PROD.DOYENSYS.COM
Solutions:
Step 1: Bounce the Apps Tier.
Then Go to respective Directory.
/UAT/oracle/uatappl/fnd/11.5.0/patch/115/sql
$ ls -lrt wfctqrec.sql
-----wfctqrec.sql
step 2:
SQL> sqlplus apps/passwd
SQL> @wfctqrec.sql applsys passwd
Step 3: Bounce the DB and Listener in Both RAC Nodes
Setp 4: Start Both Nodes and Listener
Step 5: Now check the show parameter service, It shows
After finishing above tasks, its changed.
sql > show parameter service
service_names string UAT, SYS$APPLSYS.WF_CONTROL.UAT.DOYENSYS.COM
OR
sql > show parameter service
service_names string UAT
Error Message:
sql > show parameter service
service_names string UAT, SYS$APPLSYS.WF_CONTROL.PROD.DOYENSYS.COM
Solutions:
Step 1: Bounce the Apps Tier.
Then Go to respective Directory.
/UAT/oracle/uatappl/fnd/11.5.0/patch/115/sql
$ ls -lrt wfctqrec.sql
-----wfctqrec.sql
step 2:
SQL> sqlplus apps/passwd
SQL> @wfctqrec.sql applsys passwd
Step 3: Bounce the DB and Listener in Both RAC Nodes
Setp 4: Start Both Nodes and Listener
Step 5: Now check the show parameter service, It shows
After finishing above tasks, its changed.
sql > show parameter service
service_names string UAT, SYS$APPLSYS.WF_CONTROL.UAT.DOYENSYS.COM
OR
sql > show parameter service
service_names string UAT
Subscribe to:
Posts (Atom)
