WorkFlow Issue
===============

As there are huge number of OMERROR and WFERROR which are OPEN  and CANCELLED MAIL for User sysadmin, the issue is occuring.
Hence to start with, we shall reduce the number of WFERROR and OMERROR for user on

***DISCLAIMER***
The Item type will forcefully abort/end-date ALL OPEN types'
Hence this is totally intended to meet your specific business requirement,so kindly take appropriate backups '

Backup tables:
WF_NOTIFICATIONS
applsys.wf_notification_out
WF_ITEMS

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

1. UPDATE all OPEN and CANCELLED records WFERROR and OMERROR records to CLOSED.

For WFERROR:

SQL>UPDATE WF_NOTIFICATIONS SET STATUS='CLOSED' ,MAIL_STATUS='SENT' where recipient_role = 'SYSADMIN' and MESSAGE_TYPE ='WFERROR'

For OMERROR:

SQL>UPDATE WF_NOTIFICATIONS SET STATUS='CLOSED' ,MAIL_STATUS='SENT' where recipient_role = 'SYSADMIN' and MESSAGE_TYPE ='OMERROR''
commit;

commit;

----------------------------------------------------------------------------------------------
2. Deleting ONLY WFERROR type in READY status under mailer queue:

SQL>delete from applsys.wf_notification_out where CORRID ='<BELOW CORRID>'
SQL>COMMIT;

CORRID:
APPS:WFERROR:RETRY _ONLY 0 = Ready 1365
APPS:WFERROR:DEFAULT_EVENT_ERROR 0 = Ready 606536
APPS:WFERROR:DEFAULT_EVENT_EXT_ERROR 0 = Ready 16
APPS:WFERROR:RESET_ERROR_MESSAGE 0 = Ready 9971

APPS:OMERROR:OMERROR_MSG 0 = Ready 12673

Example:

SQL>delete from applsys.wf_notification_out where CORRID ='APPS:WFERROR:DEFAULT_EVENT_ERROR'


=============================================================

3. Purging CLOSED, Obsolete records of WFERROR, Later can be done for OMERROR as well.

  1. Run the following query to determine how many item instances are there for WFERROR which are
  currently not end dated..

  SQL> SELECT COUNT(*) FROM WF_ITEMS WHERE END_DATE IS NULL AND ITEM_TYPE = 'WFERROR';

  2. Run the following PL/SQL script to end date
=========================================================================================================
Get a hash Value of Process ID (client pid)
=========================================================================================================
col program for a15
col machine for a15
col terminal for a15
col sid   for  9999
col serial#  for 9999999
set lines 152

select s.sid,s.serial#,'*'||s.process||'*'  Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,
s.program || s.module,s.action,s.terminal,s.machine, s.status, --s.last_call_et s.last_call_et/3600, s.taddr
from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

===================================================================
Using the PID Get a Query running by a USER
=============================================
select s.username, s.status,s.SQL_ADDRESS,t.SQL_TEXT,s.last_call_et/3600
 from v$session s,v$sqltext t
 where t.ADDRESS=s.SQL_ADDRESS and s.sid=&sid order by piece;
===================================================================
To See the profile values for a particular USER
===============================================
select
n.profile_option_name,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
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', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE,
usr.user_name
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 usr.user_name in ('&UserName')
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 1,2,3,6;
=====================================================
select name, to_char(sysdate,'DD-MM-YYYY hh24:mi:ss') from V$DATABASE;

Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)
============================================================================
update /*+parallel (a,8) */ fnd_profile_option_values a
2 set a.profile_option_value= replace(a.profile_option_value,'appsnidcprod-nmcmotor.oracle.com','appsnidcprod.nmcmotor.com')
3 where upper(a.profile_option_value) like upper('%appsnidcprod-nmcmotor.oracle.com%');

========================================
Starting MWA server & dispatcher:
cd $MWA_TOP/bin
./mwactl.sh start 30002
./mwactl.sh start_dispatcher &

cd $MWA_TOP/bin
./mwactl.sh -login sysadmin/simple4u stop_force 30002 &
./mwactl.sh stop_dispatcher &

sec$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'
================================================================================
adodfcmp odffile=/pnob1i/applmgr/1200/pji/12.0.0/patch/115/odf/pjit130.odf
userid=applsys/Welcom777 changedb=yes priv_schema=system/Hue62Ka mode=tables touser=apps/Welcom777
===================================================================================================
find . -mtime -4
select dbms_metadata.get_ddl('TABLE','FND_LOGINS','APPLSYS') from dual;
select 'alter system kill session ''' || sid||','||serial#||''' IMMEDIATE ' || ';' from v$session where status ='INACTIVE';
select 'alter PACKAGE ''' || OWNER||'.'|| OBJECT_NAME|| '''  '  ||  'compile body ;' from dba_objects where status ='INVALID';
select to_char(STARTUP_TIME,'dd-mon-yyyy hh24:mi:ss') from v$instance;
224266.1 How to Change the DBID and the DBNAME by using NID
601807.1   Upgrade 11g
============================================================================================
select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_ID=(Select PROFILE_OPTION_ID from fnd_profile_options where PROFILE_OPTION_NAME ='SIGNON_PASSWORD_HARD_TO_GUESS');
============================================================================================
TROUBLESHOOT LOGIN PAGE / APACHE
================================

$ cd $LOG_HOME
$ find . -name 'application.log'
./ora/10.1.3/j2ee/oacore/oacore_default_group_2/application.log
./ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
./ora/10.1.3/j2ee/oafm/oafm_default_group_1/application.log


$FND_TOP/admin/template/custom/httpd_conf_1013.tmp
$INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf

When the issue occur check the log files apart from access and error_log

sec_case_sensitive_logon             boolean     FALSE

==============================================================
Related metalink notes to enable additional debugging

419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12
422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN

==============================================================================
PRINTERS
=========

lpstat -t|grep -i 4_
lpstat -a
telnet hostname port
telnet 69.25.45.13 515 "Whether port is open"
tcptraceroute 69.25.45.13 515
netstat -rn
[AMD64] appkinci@auohskinc06 > /usr/local/git/bin/tcptraceroute 69.25.45.12 515

==================================================================================

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./6196748

Level 12 trace for a concurrent prog  Doc ID 295963.1

select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=13787589;
update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=13787589;
commit;

apscheck.sql from note 246150.1

1.Login as system owner and run:

alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;
alter user apps account unlock;
==================================================================================
select text from dba_source where name='XXMSC_INVENTORY_TRANSFER' and type='PACKAGE';
select text from dba_source where name='XXMSC_INVENTORY_TRANSFER' and type='PACKAGE BODY';

Here is an important update on the CPU Patch process, need to be followed for all customers as part of migration/implementation:

***************************************************************************************************
==============================
package version
===============

select text from all_source where name = '&pkg_name' and line < 7;

==================================================================

select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
========================================================

SET MARKUP HTML ON

SPOOL RESP_MF_BUY_file.xls;

====================================
Update override address: afsvcpup.sql

select COMPONENT_ID,COMPONENT_NAME from FND_SVC_COMPONENTS where COMPONENT_NAME like '%Workflow Notification%';

COMPONENT_ID     COMPONENT_NAME
-------------    ------------------------------
       10006     Workflow Notification Mailer

select PARAMETER_ID,DISPLAY_NAME from FND_SVC_COMP_PARAMS_TL where DISPLAY_NAME like '%Test%Address%';

PARAMETER_ID     DISPLAY_NAME
-------------    --------------------
       10057     Test Address


update FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE='gleue.srik@google.com' where COMPONENT_ID=10006 and PARAMETER_ID =10057;

1 row updated.

SQL> commit;


select PARAMETER_VALUE from FND_SVC_COMP_PARAM_VALS  where COMPONENT_ID=10006 and PARAMETER_ID =10057;

===========================================
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc
$ORACLE_HOME/OPatch/opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc

When ICM is started on mid tier it launches 1367 fndlibr processes. This is causing a DB crash.
Configure ICM for the correct sizing.

[AMD64] apdrfnyi@auohsrfny02 > ps -elf|grep -i fndlibr|wc -l
1367
================================================================================


*******************************************
Please perform the following step before running 12.1.1 upgrade driver to improve the performance of XLIFFImporter java objects as suggested by support.

Reff :SR 3-1679349151

Apply the following patches instead before the Rel 12.1.1 driver is applied since these patches should have the performance fixes.

· Patch 9179588 (9179588:R12.AD.B) è Merge this patch with Patch 7461070 (R12.AD.B.1) and then apply

· Patch 7303029 (7303029:12.1.0) èPatch needs to be applied in  preinstall mode.

**************************************************************************************

select 'New Invalid objects', object_name from dba_objects where status='INVALID' and object_name not in (select object_name from apps.invalids_TKAD51);

select 'New Disabled Triggers',trigger_name from dba_triggers where status='DISABLED' and trigger_name not in (select trigger_name from disabled_TKAD51);

**************************************************************************************
Module installation mode has been changed without problems for Alerts, Cash Management, Payments and Manufacturing.

-- XLA status -- Find a Status of any Product
SQL> select fpi.application_id, fpi.status, fpi.patch_level from fnd_product_installations fpi where fpi.application_id = 602;

APPLICATION_ID S PATCH_LEVEL
-------------- - ------------------------------
           602 S R12.XLA.A.6


Reg Sub ledger Accounting (XLA), if customer is licensed for other applications that uses SLA, e.g. AR, AP, Costing, PA...etc, then
fnd_product_installations.Status should be 'S'. If they are licensed for FSAH (Financial Services Accounting Hub), then
fnd_product_installations.Status should be 'I'.
**************************************************************************************
Select a.APPLICATION_ID, i.STATUS
from fnd_application ,fnd_product_installations i
where a.APPLICATION_ID=i.APPLICATION_ID
and a.APPLICATION_SHORT_NAME ='&applshortname'

**************************************************************************************
Status of RAC

cd /oracrs/oracle/product/111/bin
./crs_stat -t
./crs_stop -all
./crs_start -all

===============================================================

1. FND Message Log

1) Enable the FND Debug Log
a)  Navigate to System Administrator responsibility
b) Set the following  profiles at the USER level:
FND: Debug Log Enabled = YES
FND: Debug Log  Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module =  %

2) Using a SQL client run the following query and Note down this  log_seq
select max(log_sequence) from fnd_log_messages;

3) Reproduce  the issue

4) Run the follow using the logseq number from step 2 and  upload in spreadsheet format
Select * from fnd_log_messages where  log_sequence > &log_seq_noted_above order by  log_sequence;
===============================================================

Apache Clearing Cache in R12  742107.1

or else

$ grep s_jsp_main_mode $CONTEXT_FILE
         <jsp_debug_parameters oa_var="s_jsp_main_mode">recompile</jsp_debug_parameters>

then u can just remove _pages and bring apache
if u dont find recompile then u need to run ojspCompile.pl
then we have run this script


rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*
===============================================================
Check APPS_MODE or stand alone
============================================================================
SQL> SELECT SP_VALUE FROM EUL5_US.EUL5_APP_PARAMS WHERE APP_ID = 1016;

SP_VALUE
--------------------------------------------------------------------------------
1

if "1" then it is APPS_MODE or else stand alone

sqlplus -s apps/****** @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/****** @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

frmcmp_batch module=ARXTWMAI.fmb userid=apps/X1yMF2mg output_file=/tbic1i/applmgr/1200/ar/12.0.0/forms/US/ARXTWMAI.fmx compile_all=special

CREATE PUBLIC DATABASE LINK R11toR12 CONNECT TO apps IDENTIFIED BY xxxxx USING 'TMANGI';
SQL> select * from dba_db_links where DB_LINK like '%R11%';


select HOST_NAME,INSTANCE_NAME from v$instance@R11TOR12;

SQL> select sysdate from dual@r11tor12;

Toget Diagnostics for Applications
==================================
"Diagnostics: Apps Check" Concurrent programe. File listing multiple file versions for FA.


Check whether #Patch DriverApplied NLS

col DRIVER_FILE_NAME for a16
set linesize 300

select apd.driver_file_name,to_char(apd.creation_date,'DD-MON-YYYY:HH24:MI:SS') "Started",to_char(apd.last_update_date,'DD-MON-YYYY:HH24:MI:SS') "Last_Updated",apd.DRIVER_TYPE_C_FLAG "C",apd.DRIVER_TYPE_D_FLAG "D",apd.DRIVER_TYPE_G_FLAG "G" ,apd.MERGED_DRIVER_FLAG "Merged", apdl.language "Language" from ad_patch_drivers apd, ad_patch_driver_langs apdl where apd.patch_driver_id=apdl.patch_driver_id and apd.driver_file_name like '%&1%';

#CHECK ANY FILE VERSION FROM A SQL

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from
AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and A.FILENAME = '&filename'
group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME;

select application_id, application_short_name, basepath from applsys.FND_APPLICATION where APPLICATION_ID = 13015;

select application_id, product_version, tablespace, index_tablespace from applsys.FND_PRODUCT_INSTALLATIONS where application_id = 13015;


Gather Schema Statistics can be scheduled with the following parameters.

Gather Schema Statistics ALL:10::NOBACKUP::LASTRUN:GATHER::Y

To Relink any Specific Executable
adrelink.sh force=y ranlib=y "pay PYUGEN"
adrelink.sh force=y ranlib=y "fnd FNDLIBR"