Oracle EBS Workflow Email Approvals are not working in R12.2.6

Issue:

It has been observed that workflow email approvals are not working on Oracle EBS R12.2.6.
When user approve any PO through mail, its get stuck in INBOX of mailer account.

Cause:

Workflow mailer is not able to connect to IMAP account causing notifications to get stuck in INBOX of mailer account.

This is bug identified by an Oracle

IMAP server is running in SSL mode and listening on 993 port.

Below errors are captured in the Workflow Mailer log file:

oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.openNewConnection]:Unable to connect to the email store when opening the new connection at attempt #2

Solution:

Please apply the patch(Applicable for R12.2.x) as mentioned in below Note and retest:

Doc ID 2201170.1 : Workflow Notifications Are Not Getting Processed From INBOX

Reference Notes:

Doc ID 2051827.1: Configuring Oracle E-Business Suite Workflow Mailer with TLS for Microsoft Office365, Gmail, or Cloud Based Email Services for 12.1.3 and 12.2
Doc ID 2077434.1: Configuring an Oracle Workflow Notification Mailer with Cloud E-Mail Servers
Doc ID 578578.1: What Is The Correct Value of Mailer Parameter "HTML Agent" In R12?

Assign the Workflow System Administrator Privilege to a specific user or to all users

By default, the Workflow System Administrator is set to SYSADMIN which allows access to all workflow functions for all users associated to the Workflow Administration role.

Query to Check:

select *
from wf_resources
where name = 'WF_ADMIN_ROLE'; 

Sample Output:

To change the Workflow System Administrator to the role intended to have administrator privileges, perform the following steps: 

Navigate to Responsibility -> "Workflow Administrator Web Applications"
-> Click on "Administrator Workflow"
-> Administration  > Workflow Configuration

Select the user intended in the Workflow System Administrator field and Apply

OR

Set the value to ' * ' to grant administrative privileges to all users and Apply


For Specific User: Select User as required and Apply

For All Users: Set the value to ' * ' 

Note :
The Workflow System Administrator privilege is granted to all users which is not recommended for Production Environments. You can set it for non-prod instances if needed.

R12.2: E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'

Issue:

R12.2 E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'.

When attempting to access the XML Gateway Inbound URL  -

http://<hostname>:<port>/webservices/ECXOTAInbound

Getting below error:

The URL with localhost is working i.e. on VNC Server

http://localhost:8000/webservices/ECXOTAInbound

Cause:

By default, AOL/J diagnostic pages and ECXOTAInbound Servlet Page are only accessible from the local host where Oracle HTTP Server is running.

We can verify it by checking content of the s_admin_ui_access_nodes parameter in $CONTEXT_FILE.

$ grep -i s_admin_ui_access_nodes $CONTEXT_FILE

<admin_ui_access_nodes oa_var="s_admin_ui_access_nodes">localhost</admin_ui_access_nodes>

Solution:

To resolve the issue, perform the following steps :

1.    Comment out the ECXOTAInbound entries in $FND_TOP/admin/template/trusted_conf_FMW.tmp

These are the entries:

<Location ~ "^(/)+webservices(/)+ECXOTAInbound">
   Order deny,allow
   Deny from all
   Allow from %s_admin_ui_access_nodes%
</Location>

Comment out as below:

# <Location ~ "^(/)+webservices(/)+ECXOTAInbound">
#  Order deny,allow
#  Deny from all
#  Allow from %s_admin_ui_access_nodes%
# </Location>

2.     Run autoconfig so the changes are processed.

3. Bounce the oafm services or all application tier services

4. Check          $IAS_ORACLE_HOME/instances/EBS_web_<SID>_OHS1/config/OHS/EBS_web_<SID>/trusted.conf
to verify if the changes are reflected.

5. Try to access the XML Gateway Inbound URL http://<hostname>:<port>/webservices/ECXOTAInbound

This should work now.

Note: As a temporary workwround we can just try to comment out the lines in $IAS_ORACLE_HOME/instances/EBS_web_<SID>_OHS1/config/OHS/EBS_web_<SID>/trusted.conf then bounce oafm services if autoconfig cannot be executed.

Additional Checks:

Query to check if OTA is running:

select machine,action, decode(count(*),0,'Error: OTA is Not Running','OTA is Running')
from gv$session
where action like '%OXTA%'
group by machine, action; 

Reference Notes: 

Doc ID 2397274.1: Unable to Access XML Gateway ECXOTAInbound Page after Applying AD / TXK Delta 10 Patches
Doc ID 2335074.1: R12 E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'
Doc ID 2324193.1: R12 E-Business Suite XML Gateway Users Are Unable To Access Due To 'Error 403 Forbidden' When Accessing /webservices/ECXOTAInbound To Start Inbound XML Gateway Process

OBIEE 12c: How To Change The Repository (RPD) Password

We can Change the Oracle BI Repository Password Using the obieerpdpwdchg Utility

Use the following steps to Change The Repository (RPD) Password:

1. Navigate to the obieerpdpwdchg utility, which is located under $BI_DOMAIN_HOME/bitools/bin directory.

Type the following arguments for obieerpdpwdchg:

-I name_and_path_of_existing_repository
-O name_and_path_of_new_repository

2. Then, enter the current (old) password and the new password when prompted. The repository password must be longer than five characters and cannot be empty.

$ obieerpdpwdchg -I my_repos.rpd -O my_changed_repos.rpd

Please enter the repository password:

Please enter a new repository password:

Note that passwords are masked on the command line unless you include the -C option in the command to disable masking.

3. Use the uploadrpd command to upload the repository to Oracle BI Server.

Please see this link to - OBIEE 12c: Download and Upload Repository(RPD) Commands

OBIEE 12c: Download and Upload Repository(RPD) Commands

OBIEE 12c: Download Repository(RPD) Command :

Use the downloadrpd command to download the repository used by the service instance.

Execute the utility through a launcher script, datamodel.sh on UNIX and datamodel.cmd on Windows.

Location of the launcher script:

$DOMAIN_HOME/bitools/bin/datamodel.sh

Command:

./datamodel.sh downloadrpd -O /<RPD PATH>/<RPD_file_name_to_download.rpd> -U <weblogic user> -p <Weblogic Passwd> -si <service_instance>

Example:

$ cd $DOMAIN_HOME/bitools/bin/

$ ./datamodel.sh downloadrpd -O /tmp/myrpd_latest.rpd -U weblogic -p welcome123 -si ssi

This will prompt for RPD Password. Please provide RPD passwd as per your instance.


OBIEE 12c: Upload Repository(RPD) Command :

Use the uploadrpd command to upload the repository to Oracle BI Server.

execute the utility through a launcher script, datamodel.sh on UNIX and datamodel.cmd on Windows.


Location of the launcher script:
$DOMAIN_HOME/bitools/bin/datamodel.sh

Command:

./datamodel.sh uploadrpd -I /<RPD PATH>/<RPD_file_name_to_upload.rpd> -SI <service_instance> -U <weblogic user> -P <Weblogic Passwd>

Example:

$ cd $DOMAIN_HOME/bitools/bin/

$ ./datamodel.sh uploadrpd -I /tmp/myrpd_updated.rpd -SI ssi -U weblogic -P welcome123

This will prompt for RPD Password. Please provide RPD passwd as per your instance.

How to unlock the OID Superuser Account 'orcladmin'

Unlock OID super user account "orcladmin" with below command

Set the environment to point at the Identity Management home and execute below command

$ORACLE_HOME/ldap/bin/oidpasswd connect=oiddb unlock_su_acct=true 
OID DB user password: 
OID super user account unlocked successfully.

You will be prompted for OID DB user (ODS Schema) password.

Note : OIDPASSWD works by connecting directly to the ODS schema and modifying what is required for the operation being run.

Query to get XML Template Code details from Database

As you know we upload the XML Templates from XML Publisher Administrator responsibility, below is the navigation :

XML Publisher Administrator  Home  -> Templates

Please use below query to check the details in the backend database after the upload.

SELECT B.TEMPLATE_CODE,
  a.FILE_NAME,
  TO_CHAR(a.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') "CREATION_DATE",
  TO_CHAR(a.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI:SS') "LAST_UPDATE_DATE"
FROM APPS.XDO_LOBS a,
  APPS.XDO_TEMPLATES_B B
WHERE a.LOB_CODE     = B.TEMPLATE_CODE
AND a.FILE_NAME = 'TEMP_CODE_ABC.rtf'

--AND B.TEMPLATE_CODE IN('TEMP_CODE_ABC')
AND a.XDO_FILE_TYPE  = 'RTF';

Update the FILE_NAME and TEMPLATE_CODE parameters according to requirement.