Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, December 13, 2012

Workflow Mailer Notification not sending out mails

Today Amanda pinged me about an issue with Workflow Notification Mailer in an R12.1.3 instance.  Mails were not going out.  I checked the SMTP with commandline:

telnet justanexample.com 25
mail from:john.doe@justanexample.com
rcpt from:jane.doe@justanexample.com
data
Subject: Test mail from smtp commandline of justanexample.com
Test.
.

The mail was going out fine.  So there was no issue on the unix Sendmail side. I suggested that we check the configuration inside Apps.  A search on support.oracle.com revealed this article:

What to Review When Notifications are not Emailed (Outbound Processing)? [ID 1051421.1]

We ran the query in this article:

1. Make sure the user who you sent the notification to has an email address in the wf tables. There are 2 ways to review this:


a. In the Define Users form, query the user and make sure the user has an email address in this form.

b. Execute the following API to review if that email was synchronized to the workflow tables. The script will request the user name as a parameter:

set serveroutput on
declare
recipient_role VARCHAR2(100);
display_name VARCHAR2(200);
email VARCHAR2(1000);
notification_pref VARCHAR2(100);
installed VARCHAR2(1);
language VARCHAR2(100);
territory VARCHAR2(100);
orig_system VARCHAR2(100);
orig_system_id number;

begin
recipient_role := '&username';

WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email,
notification_pref,
language, territory,
orig_system, orig_system_id, installed);
dbms_output.put_line('display_name='
display_name);
dbms_output.put_line('email='
email);

dbms_output.put_line('language='
language);
dbms_output.put_line('territory='
territory);
dbms_output.put_line('notification_pref='
notification_pref);
end;
/
Enter value for username: SYSADMIN

old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

Email was coming up blank.

So I queried the fnd_user table:

select user_name,email_address
from fnd_user
where user_name='SYSADMIN';

USER_NAME

--------------------------------------------------------------------------------
EMAIL_ADDRESS
--------------------------------------------------------------------------------
SYSADMIN
jane.doe@justanexample.com

To sycnrhonize email address in workflow tables,  I executed the sqls given in the article
How To Run The Workflow Directory Services Concurrent Program From The SQLplus Prompt [ID 1213304.1]

1. Sync responsibility role data into the Workflow table:

begin
fnd_user_resp_groups_api.sync_roles_all_resp_secgrps(TRUE);
end;

2. Synchronize WF LOCAL tables:
exec WF_LOCAL_SYNCH.BulkSynchronization('ALL');

3. Workflow Directory Services User/Role Validation:
exec wf_maintenance.ValidateUserRoles(p_BatchSize => null, p_check_dangling => TRUE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);

Then I re-ran the first query
/

Enter value for username: SYSADMIN
old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

The result was still the same.

So I decided to check the WF tables:

select table_name,column_name

from dba_tab_columns
where column_name='EMAIL_ADDRESS' and
table_name like 'WF%'
SQL> /




TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
WF_LOCAL_ROLES EMAIL_ADDRESS
WF_LOCAL_ROLES_OLD EMAIL_ADDRESS
WF_LOCAL_ROLES_STAGE EMAIL_ADDRESS
WF_LOCAL_USERS EMAIL_ADDRESS
WF_ALL_ROLES_VL EMAIL_ADDRESS
WF_ALL_ROLE_LOV_VL EMAIL_ADDRESS
WF_AMV_APPR_ROLES EMAIL_ADDRESS
WF_AMV_CHN_ROLES EMAIL_ADDRESS
WF_ENG_LIST_ROLES EMAIL_ADDRESS
WF_FND_RESP_ROLES EMAIL_ADDRESS
WF_FND_USR_ROLES EMAIL_ADDRESS
WF_GBX_ROLES EMAIL_ADDRESS
WF_HZ_GROUP_ROLES EMAIL_ADDRESS
WF_PER_ROLE_ROLES EMAIL_ADDRESS
WF_POS_ROLES EMAIL_ADDRESS
WF_PQH_ROLE_ROLES EMAIL_ADDRESS
WF_ROLES EMAIL_ADDRESS
WF_ROLE_LOV_VL EMAIL_ADDRESS
WF_USERS EMAIL_ADDRESS
WF_USER_LOV_VL EMAIL_ADDRESS

20 rows selected.

On a whim, I updated the email address in the WF_LOCAL_ROLES table:

update wf_local_roles

set email_address='Jane.Doe@justanexample.com'
where name='SYSADMIN;

and re-ran the verificaiton query:
/

Enter value for username: SYSADMIN
old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=jane.doe@justanexample.com
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

I asked Amanda to test notification mailer now, and sure enough it worked.

For now it is good. However, we need to figure out why the email is not getting synchronized to WF tables automatically.  I did find another article on support.oracle.com which advises the same thing we did. Though it says that it is applicable to 11i, but it works for R12.1 too:

Notify Function When Running Concurrent Request Not Sending Notification to User [ID 1370390.1]


Applies to:

Oracle Concurrent Processing - Version: 11.5.10.2 to 11.5.10.2 - Release: 11.5 to 11.5

Information in this document applies to any platform.

Symptoms

When viewed from OAM, the Workflow Mailer Service is running well.

From the concurrent request submission form (FNDRSRUN), submit any request with "Option" of "Notify the following People", but after running the concurrent request, the user cannot receive the notification.

Cause

The cause of the issue was found to be an invalid or missing email address for the user.

Solution

1. From the request log, verify the notification id has been generated and make a note of the id.

2. Check that the notification mail_status is "MAIL", using the following select statement, which means the notification is in processing.

select NOTIFICATION_ID, STATUS, MAIL_STATUS from wf_notifications where NOTIFICATION_ID = '423828';

3. Using the script identified below together with the notification id to check the notification detail information:

sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql

This will identify a missing vaild email address while sending notification.

4. Using following select to find if the user has been defined with a valid email address:

select user_name, email_address from fnd_user where user_name = '';

5. Verify the email address is not missing in view wf_roles as following:

select display_name, email_address from wf_roles where display_name = '';

6. Manually update table wf_local_roles to set the user a valid email address:

update WF_LOCAL_ROLES set email_address = ''where display_name = '';

commit;

7. User should now receive the notification normally.

Wednesday, November 28, 2012

ORA-06512: at "APPS.FND_CORE_LOG", line 318

Shoaib pinged me today. He was getting this error duing AutoConfig on DB node:

begin

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.FND_CORE_LOG", line 318
ORA-06512: at "APPS.FND_CORE_LOG", line 62
ORA-06512: at "APPS.FND_CORE_LOG", line 456
ORA-06512: at "APPS.FND_PROFILE", line 110
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 7

A search on My Oracle Support revealed R12: ORA-06502: PL/SQL: numeric or value error trying to update table fnd_profile_option_values [ID 1501822.1]


However the error described was similar, but not same:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "APPS.FND_PROFILE", line 731
ORA-06512: at "APPS.FND_PROFILE", line 963
ORA-06512: at "APPS.FND_PROFILE", line 3282
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 33

If you compare the two, you'll see that line numbers are different in the error we were facing.

So I decided to check the code:

select line,text
dba_source
where name='FND_CORE_LOG'
and line between 316 and 321

316

if UTL_DIR is null and P_DIRECTORY is null then
317
-- Then determine the utl_file_dir value.
318
select translate(ltrim(value),',',' ')
LINE
----------
TEXT
--------------------------------------------------------------------------------
319
into TEMP_DIR
320
from v$parameter
321
where lower(name) = 'utl_file_dir';

I checked for the length of the variable TEMP_DIR:

select line,text from dba_source where name='FND_CORE_LOG' and text like '%TEMP_DIR%'


SQL> /
LINE
----------
TEXT
--------------------------------------------------------------------------------
309
TEMP_DIR varchar2(512);

So TEMP_DIR is a 512 character variable. Then I took the length of the value which was being stuffed into it:

SQL> select length(translate(ltrim(value),',',' '))

2 from v$parameter
3 where lower(name) = 'utl_file_dir';

LENGTH(TRANSLATE(LTRIM(VALUE),',',''))
--------------------------------------
1069

It is clear now that if you try to stuff a 1069 character string into a 512 character variable, you'll get
ORA-06502: PL/SQL: numeric or value error: character string buffer too small


So I asked Shoaib to modify the utl_file_dir to keep it within the 512 characters limit, as we should not change Oracle code by modifying oracle provided seeded packages like APPS.FND_CORE_LOG.

Once Shoaib, reduced the no. of directories listed in utl_file_dir and the character count reduced to less than 512, autoconfig succeeded without errors.






Monday, November 26, 2012

java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter

Today Jayabharath Velugoti pinged me, about forms not launching in an 11i instance. When I tried reproducing the issue, I go this in my laptop java console:

 Following Exception occured: java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter

java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter
at oracle.forms.engine.Main.initDesktop(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


A search on support.oracle.com revealed this article: Opening Any Form Applications Gives: Illegal Argument For Color Scheme Applet Parameter. [ID 858367.1]

However this article was relevant to R12. Anyhow, I executed the query given in the article:

col NAME for a25

col LEV for a4
col CONTEXT for a30
col VALUE for a12
select po.profile_option_name "NAME",
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp
where po.profile_option_name like 'FND_COLOR_SCHEME'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE"

NAME LEV CONTEXT VALUE

------------------------- ---- ------------------------------ ------------
FND_COLOR_SCHEME SITE SWAN
FND_COLOR_SCHEME USER JOHNDOE SWAN
FND_COLOR_SCHEME USER JANEDOE SWAN

That didn't look right. This is an 11i instance. Swan interface is a new feature introduced in R12. So I asked Jayabharath about it and he said that this instance was an R12 instance and was recently cloned again as an 11i instance. That doesn't explain it as the source 11i instance didn't have SWAN. Possibly, someone did this by mistake. Anyways, I updated the values from SWAN to BLUE:   SQL> Declare
value Boolean;
Begin
value := fnd_profile.save('FND_COLOR_SCHEME','BLUE','SITE');
End;
/
2 3 4 5 6
PL/SQL procedure successfully completed.

SQL> UPDATE FND_PROFILE_OPTION_VALUES
2 SET PROFILE_OPTION_VALUE='BLUE'
3 WHERE PROFILE_OPTION_VALUE='SWAN';
2 rows updated.

SQL> COMMIT;

The query showed BLUE now:

NAME LEV CONTEXT VALUE

------------------------ ---- ------------------------------ ------------
FND_COLOR_SCHEME SITE BLUE
FND_COLOR_SCHEME USER JOHNDOE BLUE
FND_COLOR_SCHEME USER JANEDOE BLUE

Apache was bounced, and forms launched fine without this issue.

Thursday, November 1, 2012

Query for CPU patches

Here's my updated query to identify the CPU patches (April 2011 - October 2012) applied on your 11i or R12 instance:

column BUG format a8;

column PATCH format a60;
set linesize 100;
set pagesize 200;

select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
14321237, 'Oct 2012 CPU Patch for R12.1 + ATG_PF.B.Delta3',
14321239, 'Oct 2012 CPU Patch for R12.0.6',
14321240, 'Oct 2012 CPU Patch for 11i+RUP7',
14321241, 'Oct 2012 CPU Patch for 11i+RUP6',
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in
('14321237','14321239','14321240','14321240',
'13979374','13979377','13979372','13979375',
'13621942','13621941','13621940','13621939',
'13322561','12794416','13322559','13322557',
'12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913',
'11660357''11660356','11660355','11660354')
order by patch;

Thursday, June 28, 2012

No application machine is available to service any funcationality in DBA domain

After a recent clone of OTM loosely based on method described in http://otm62.blogspot.com, when we brought up the services, the following message appeared on accessing the OTM URL:

Error found handling the request.
No application machine is available to service any funcationality in DBA domain.

glog.exception.log was showing this error:

OTMActivationThread]
2012-06-28 13:36:25.728 0       Error   Exception       Server t3://otmapp1.example.com:9001 is not responding
  3:09 PM
[OTMActivationThread]
2012-06-28 13:36:53.804 0       Error   Exception       Could not notify APPSERVER2 of APPSERVER2 activation
[OTMActivationThread]
2012-06-28 13:39:07.491 306049  Error   Exception       cause.QueryFoundNoRecords
  3:09 PM


After going through the contents of glog.properties of both nodes, the cluster tables app_server, app_machine, app_server_machine for many hours, I noticed that the port number was incorrect in the MACHINE_URL column of app_machine. 

SQL> select MACHINE_URL from app_machine;

MACHINE_URL
--------------------------------------------------------------------------------
t3://otmapp1.example.com:9001
t3://otmapp2.example.com:9001
 
It had the apache port 9001, instead of the weblogic port 7001

So I updated app_machine table to change it to:

MACHINE_URL
--------------------------------------------------------------------------------
t3://otmapp1.example.com:7001
t3://otmapp2.example.com:7001


After correcting this, the services came up and we were able to login without issues.

Thursday, April 5, 2012

File too large zip error: Output file write failure

Recently while zipping a few directories for a clone, I got this error when the file size became greater than 2 GB.

File too large

zip error: Output file write failure (write error on zip file)

I tested the file system's ability to create files greater than 2 GB by creating a dummy 3GB file:

dd if=/dev/zero of=3gb-file bs=1024k count=3000

The 3 GB file got created without issues.

Then I checked the zip version.

$ zip -v
Copyright (c) 1990-2006 Info-ZIP - Type 'zip "-L"' for software license.
This is Zip 2.32 (June 19th 2006), by Info-ZIP.
Currently maintained by Onno van der Linden. Please send bug reports to
the authors using http://www.info-zip.org/zip-bug.html; see README for details.

So I checked http://www.info-zip.org

I clicked on the zip link: http://www.info-zip.org/Zip.html and found these lines:

Latest Release
New features in Zip 3.0, released 7 July 2008:


•large-file support (i.e., > 2GB)
•support for more than 65536 files per archive
•multi-part archive support
•bzip2 compression support
•Unicode (UTF-8) filename and (partial) comment support
•difference mode (for incremental backups)
•filesystem-synch mode
•cross-archive copy mode
•extended progress info and logging
•improved archive-fixing support
•improved streaming and piping
•improved command-line parser
•improved Unix FIFO support
•Unix 32-bit UIDs/GIDs (requires UnZip 6.0 to restore)

So I went to www.sunfreeware.com, downloaded zip 3.0 package and installed it on the server. Once zip 3.0 was installed, the zip command succeeded without errors

Thursday, March 22, 2012

What languages and currencies are enabled in EBS

Raghava asked me about the languages and currencies being used in a new instance which has gone live. For languages we have this query:

select language_code
from fnd_languages
where installed_flag='I'

For currencies, I told him that in EBS all currencies are enabled. I executed the following query:

select CURRENCY_CODE,symbol
from apps.fnd_currencies
where enabled_flag='Y'

It returned 71 rows.

Total rows in fnd_currencies was 249.

However, I thought of a more logical way of getting this data. That would be to query transaction tables. Which transaction table to query here ? I queried dba_segments for the largest objects:

select object_name,bytes
from dba_segments
order by bytes desc;

After the usual fnd_lobs etc., gl_interface table came up. So I created this query:

SQL> select currency_code,count(*)
from apps.gl_interface
group by currency_code; 2 3

CURRENCY_CODE COUNT(*)
--------------- ----------
VND xxx
EUR xxx
USD xxx
SGD xxx
JPY xxx


VND = Vietnamese Dollar
SGD = Singapore Dollar
JPY = Japanese Yen
EUR = Euro
USD = US Dollar

I feel that this query does give a good estimate about the currencies in use.

Monday, March 5, 2012

inspection.wsil shows incorrect hostname

In SOA Suite the inspection.wsil URL is:

http://soahostname:soaport/inspection.wsil

However this was showing the physical hostname of the server on which SOA Suite was installed. We had configured SOA to listen on virtual names. Rajiv Bhalla provided the solution to this problem by providing the My Oracle Support Article: Applications like Oracle IPM fail to retrieve WSDL's to SOA composites via URL's that use different protocol/hostname/port than they are configured to [ID 1290812.1]


Solution
1.Access the WebLogic server Admin console: e.g. http://host:port/console.


2.From the left pane, use the following path through the hierarchical menu to navigate to the configuration screen containing the "Front End" configuration

Domain
-> Environment
-> Servers
-> soa_server1
-> Protocols
-> HTTP

3.Review and adjust the values of the following entries:

Frontend Host: myLoadBalancerHost.myDomain
Frontend Port: 80
Frontend HTTPS Port: 443

You'll need to restart the SOA server for this to take effect, even though it says No restarts necessary.

Thursday, January 12, 2012

BEA-141281 unable to get file lock

./startManagedWebLogic.sh soa_server1
SOA Managed Server wouldn't start with this error:

BEA-141281 unable to get file lock, will retry

cd $MW_HOME/user_projects/domains/soa_domain/servers/soa_server1/tmp
rm *.lok

Retry command
./startManagedWebLogic.sh soa_server1