While Upgrading EBusiness Suite from 11i- Whether to go for 12.1 or 12.2 ?

EBusiness Users/Customers now can upgrade from 11i or 12.0 to 12.1 or 12.2.

A Direct Upgrade is possible from 11.5.9 or higher to 12.1

A Direct Upgrade is possible from 11.5.10 CU2 or higher to 12.2

Oracle Lifetime Support LifeCycle for Oracle EBusiness Products-

R12.1 includes major architectural improvements to the Financial Products to support Global and Shared service operations. It also includes significant enhancements to other Product areas such as -
Procurement
Supply Chain Management
Asset LifeCycle
Service
Human Capital Management

It provides increased opportunity for process improvement and automation

For more on that- see 12.1 and 12.2 Release Content Documents(RCDs)

Upgrade from 11i to 12.1 involves significant changes in FINANCIALS Data Model and functionality, an 11i to R12 upgrade will require greater involvement from Business Process owners and functional analysts than was required prior. Upgrade from 11.5.9 to 11.5.10 was more tech focussed.

Upgrade from 11i to 12.2 includes-
Financial Architectural Changes
Some specific 12.2 technical changes-

• Move to Online Patching
• Uptake of Weblogic Server
• Upgrade to 11gR2 Database
• Required Switch from Oracle Single Sign-On to Oracle Access Manager

Customers at release levels below 11.5.9 should consider a reimplementation

When Should You Choose 12.2?

Consider targeting 12.2 if any of the following circumstances applies to your organization:

 You need the high availability provided by Online Patching.

 You want the functional enhancements that are in 12.2.

 You prefer to invest in a single project to upgrade directly to 12.2.

You want the functional enhancements that are in 12.2.

ERP

 Enhanced Labor Costing with Projects and Payroll

 Project Cost Breakdown Structure

 Contract Lifecycle Management (CLM) for Public Sector: Electronic Contract File, Concurrent Modifications, and more

 Channel Revenue Management: Simplified HTML Flows

Oracle APPS- Purge Workflow email notifications from the Oracle Workflow queue so the Email is not sent

So we have a scenario typically when we create a cloned environment. The Workflow mailer has not been running. Which may have caused  a large number of e-mail notifications to accumulate in the queue.  How does one prevent these from being sent when the mailer is started.

1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table.  Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';  

 

This should show which notifications are waiting to be e-mailed.  

 

One can use the BEGIN_DATE column to help narrow down the ones not to send if one only wants to stop the e-mails from a specific date range.

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The
mailer will think the e-mail has already been sent and it will not send it again.. (Users can
still reply to the notification from the worklist page in the applications).
Example:

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL'; 

OR

update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and MAIL_STATUS = 'MAIL';

 

 

This will update all notifications waiting to be sent by the mailer.

2. Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr  

 

Example Syntax:

sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys

3. Now start the mailer.  

 

 

 

Concurrent Manager Status Shows 'System Hold Fix Manager before resetting counters'

Concurrent Manager Status Shows 'System Hold Fix Manager before resetting counters' 

User complained his program is not running and shows No Manager. You checked and Standard Manager seems to be down.

You ran adcmctl.sh and it show status could not be found.

What to do ??

Do the following-

1. Stop all middle tier services including the concurrent managers.
    Please make sure that no FNDLIBR, FNDSM, or any dead process is running.

2. Stop the database.

3. Start the database.

4. cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"
$ adrelink.sh force=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit). Article- ID : 134007.1
Title: CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
    select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
    CONCURRENT_QUEUE_NAME like 'FNDSM%';
7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Expire All FND_USER Passwords?


“Expire all FND_USER passwords”
This is available starting in RUP4. Requires Patch 4676589 ATG RUP 4.
The script to expire all passwords in the fnd_user table is $FND_TOP/sql/AFCPEXPIRE.sql.  It can be executed from SQL*Plus or as a Concurrent Program:
sqlplus -s APPS/<pwd> @AFCPEXPIRE.sql
or
Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords
This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program. The user will need to create a new password upon the next login.

 

FND_USER script

Purpose-

Can be used to create an FND_USER for any developer that joins the team

This simple script will set all the desired admin responsibilities and all common profile options for a given FND_USER.
Salient features of this simple script are
1. If a user is inactive, that FND_USER will be re-activated
2. If any of their Sysadmin or WF Admin or Application Developer or Functional Admin responsibilities are inactive, those responsibilities will be re-activated
3. Add commonly used responsibilities will be assigned to the user
3. Personalization, Diagnostic and Utilities profile options will be assigned to this user.
4. Password expiration will be removed from the FND_USER

In a nutshell, use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.
In case a user does not exist, then new user will be created with default password being oracle123

SCRIPT BELOW

################################################################################

Please find the script below


Save this as a SQL File, and it will prompt for parameter User Name



DECLARE
  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');
  result    BOOLEAN;
  v_user_id INTEGER;

  FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
    p_check c_check%ROWTYPE;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    IF c_check%FOUND
    THEN
      /*Yes, it exists*/
      CLOSE c_check;
      RETURN TRUE;
    END IF;
    CLOSE c_check;
    RETURN FALSE;
  END check_fu_name;

BEGIN
  IF NOT (check_fu_name(p_user_name => v_user_name))
  THEN
    fnd_user_pkg.createuser(x_user_name                  => v_user_name
                           ,x_owner                      => ''
                           ,x_unencrypted_password       => 'oracle123'
                           ,x_session_number             => v_session_id
                           ,x_start_date                 => SYSDATE - 10
                           ,x_end_date                   => SYSDATE + 100
                           ,x_last_logon_date            => SYSDATE - 10
                           ,x_description                => 'BNitin'
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000
                           ,x_employee_id                => NULL /*Change this id by running below SQL*/
                            /*   
                                                             SELECT person_id
                                                                   ,full_name
                                                             FROM   per_all_people_f
                                                             WHERE  upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
                                                             GROUP  BY person_id
                                                                      ,full_name
                                                             */
                           ,x_email_address => ' abc@gmail.com'
                           ,x_fax           => ''
                           ,x_customer_id   => ''
                           ,x_supplier_id   => '');
   dbms_output.put_line ( 'FND_USER Created' ) ;
  ELSE
    fnd_user_pkg.updateuser(x_user_name                  => v_user_name
                           ,x_owner                      => 'CUST'
                           ,x_end_date                   => fnd_user_pkg.null_date
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000);
   dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;                          
  END IF;
  SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = v_user_name;
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FND_FUNC_ADMIN'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'SYSADMIN'
                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FNDWF_ADMIN_WEB'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'APPLICATION_DEVELOPER'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
 
    fnd_user_pkg.addresp(username       => v_user_name,
                       resp_app       => 'ICX',
                       resp_key       => 'PREFERENCES',
                       security_group => 'STANDARD',
                       description    => 'BNitin dbawalkspot.com',
                       start_date     => sysdate - 1,
                       end_date       => null);

 result := fnd_profile.save(x_name        => 'APPS_SSO_LOCAL_LOGIN'
                            ,x_value       => 'BOTH'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_CUSTOM_OA_DEFINTION'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_HIDE_DIAGNOSTICS'
                            ,x_value       => 'N'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

                           
  COMMIT;
END;
/

################################################################################

Using Lightweight MLS With Oracle E-Business Suite Release 12.1.3


There was a requirement where we had to store the item's descriptions in 5 languages(Countries of business for us). This was only required for reporting purposes. It was not required to have forms and self service pages in other languages.

Here the feature of Lightweight MLS came in very handy. Its introduced in 12.1.3

What it does- IT ENABLES ADDITIONAL LANGUAGES WITHOUT THE NEED TO APPLY THE NLS PATCH

Steps to enable MLS-

1. From OAM go to License Manager and activate the desired language
2. From AD Admin -Choose 'Maintain Application Database Entities' option and then the 'Maintain Multi-Lingual Tables' task
3. Run the message synchronization script-$FND_TOP/patch/115/sql/AFMSGSYNC.sql

$ sqlplus apps/apps @AFMSGSYNC.sql

NOTE: This script synchronizes the language data in FND_NEW_MESSAGES table
    with the base language data.

    Press RETURN to continue.

    Currently, following languages are installed:

    CODE NAME                     STATUS
    ---- ------------------------ ------------
    US   American English         Base
    D    German                   Install
    FRC  Canadian French          Install

    Please choose Oracle Language code from the installed languages.
    For example JA for Japanese. This language data will be synchronized
    with the base language data.

    Language Code: D

    You chose Spanish (D).
    Is this correct language to synchronize [Yes] ?

    Reading FND_NEW_MESSAGES table to see if language data exists.

    No Spanish language data exists in FND_NEW_MESSAGES table.

    Base language data will be copied to this language. Do you wish to proceed [No] ? Yes

    Synchronization is in progress...

    Synchronization has been done.
    Please do a 'commit' if changes are OK. Otherwise, do a 'rollback'.

    SQL> commit;

    Commit complete. 

Bounce the application tier services and we have light weight MLS enabled 

Upgrading to Oracle Identity Management (11.1.1.5.0)

Upgrading to Oracle Identity Management (11.1.1.5.0)


Understanding the Oracle Identity Management Installation

1. Overview and Structure of Oracle Identity Management 11g Installation  

Oracle Identity Management 11g includes two distinct suites
-Oracle Identity Management 11g Release 1 (11.1.1.5.0)

-Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0)




Oracle Identity Management 11g Release 1 (11.1.1.5.0)

To install Oracle Identity Management 11g Release 1 (11.1.1.5.0) you must install
Oracle Identity Management 11g Release 1 (11.1.1.2.0) first.

To install Oracle Identity Management 11g Release 1 (11.1.1.2.0), use ofm_idm_win_
11.1.1.2.0_32_disk1_1of1.zip (for Windows) or ofm_idm_linux_
11.1.1.2.0_32_disk1_1of1.zip (for Linux) comprising the following products:
– Oracle Internet Directory (OID)
– Oracle Virtual Directory (OVD)
– Oracle Directory Services Manager (ODSM)
– Oracle Directory Integration Platform (ODIP)
– Oracle Identity Federation (OIF)
Then you must patch your Oracle Identity Management 11.1.1.2.0 to Oracle Identity
Management 11.1.1.5.0 using the ofm_idm_win_11.1.1.5.0_32_disk1_
1of1.zip (for Windows) or ofm_idm_linux_11.1.1.5.0_32_disk1_1of1.zip
(for Linux)


Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0)
    

To install Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0), use ofm_
iam_generic_11.1.1.5.0_disk1_1of1.zip comprising the following Oracle Identity and Access Management 11.1.1.5.0 products:


– Oracle Identity Manager (OIM)
– Oracle Access Manager (OAM)
– Oracle Identity Navigator (OIN)
– Oracle Adaptive Access Manager (OAAM)
– Oracle Entitlements Server (OES)


Structure of the Installation

You can install both of the Oracle Identity Management products under a common
Middleware Home directory. When you install these suites on the same machine, two
Oracle Home directories are created on the machine .


For example, the first one, IDM_Home can be the IDM_Home
directory for

• Oracle Internet Directory
• Oracle Virtual Directory
• Oracle Directory Services Manager
• Oracle Directory Integration Platform
• Oracle Identity Federation

The second one, IAM_Home can be the IDM_Home directory for

Oracle Identity Manager
Oracle Access Manager
Oracle Adaptive Access Manager