Ever wanted to run an alert on non prod but not bring up the periodic scheduler?

Under Alert Manager, just use the Request -> Check functionality to schedule the alert to run sysdate + 10 mins.

Then go into view -> requests and you’ll see your alert name as scheduled; e.g.

"Alert Name" (Check Periodic Alert)

Click on view details -> schedule and you can then periodically schedule this as a one off concurrent program.

Oracle Alerts; one of the e-Business Suite's most robust and often (over) used modules. For whatever reason Oracle haven't invested any time in the module for many many years, but I bet your installation still uses them and I bet they are relied upon for a mixture of business flow and system alerting alike.

So back in the 80s when the module was written, it was fine to send the email alerts in fixed font. Heck, everything back then was using fixed fonts for plain text emails and with a bit of tweeking, you could get your alert columns to line up nicely.

Fast forward to 2011. Erm, anyone notice that without HTML, the alerts just look plain ugly? Add to that Windows 7 and Outlook 2010 and all of a sudden you lose your Courier New fixed font. Those alerts that were all nicely lined up are now unreadable. Wouldn't it be nice if Oracle allowed you to send Oracle Alerts in HTML format? Yes, it would be. Well, I was thinking that in R12 they may have made it available, then when I was recently reviewing Alerts in R12, I noticed no improvement. Time to build your own.

So what's the high level steps? 

1. Write a concurrent program that will:

• Parse the email alert and output to the concurrent program output an XML
• Set the concurrent program to output XML
• Attach a dynamic RTF that will output the alert, complete with dynamic columns
• Write a control file to parse the XML for sender info and smtp server out
• Switch the alert to use the concurrent program instead of in-built message

Over the next few weeks I'll be expanding this post to detail each of the steps. If you're keen on the solution, leave a message and i'll see if I can get it up quicker.

So i'm thinking of upgrading one day to R12.1.x and I got a request through the other day for an adhoc interface, like the ones you would have handled in the 90s via a DB link or a remote procedure call. I got thinking. Wonder if 11.5.10.2 RUP7 could do anything even remotely clever to make my solution SOA aware?
 
Fast forward 15+ hours of Googling and MOS searching plus an internal call with Oracle themselves. Answer, well, not really, not unless you want to install the SOA suite for 11i. Erm, well, why would I do that? That's going to be an interesting conversation with the procurement team isn't it? Me: "I want a copy of the SOA suite and licences please for our 11i instance". Procurement: "That's got to be expensive. What's the RTO?" Me: "Well, not much really, but one day, trust me, one day it will pay for itself". Producment: "No".
 
So I've gone for the next best thing. A Web Service running on a vanilla Oracle Application Server that's connected to my e-Biz database. It's not rocket science, but will allow me to provide a solution that can be plugged into R12.1.x iRep in seconds.

So how do you run a simple web service on e-Biz Oracle Apps? Here's the steps that will save you many an hour on Google:
 

1. Download a copy of jDeveloper. Don't worry about trying to match your version with e-Business Suite, that's only important if you want to do OAF development. For the purposes of this article, I downloaded version 10.1.3.3.0.
2. Follow this step by step tutorial to create your webservice. You'll need to swap out the local OAS for your server OAS and you'll need to swap the local DB with your oracle e-biz database connection credentials.
   http://st-curriculum.oracle.com/obe/jdev/obe1013jdev/10131/wsfromplsqlpackage/devwsfrom%20plsql.htm

By the end of the tutorial you'll have a working web service hittin the e-biz database and running on an externally hosted OAS. When you migrate to r12 and iRep you can simply then plug your pl/sql function into the custom section of the repository and click "create web service".
Any questions or comments about this post, just ask!
 

Er, did anyone notice 6,423 emails just got sent out from Test1? Hmmm, wonder where they went? Let me check.... .... .... errrrr .... looks like they went out to external suppliers.....  eh.... awkward.
 
Right, well, we've probably all had this at least once in our career. Here's how to ensure it only ever happens once!

1. When you clone your production -> test environment ALWAYS (I mean script it so that it ALWAYS happens) , set your override email notification email address to a dummy address. Therefore if something (one) does run a process on the test environment that invokes the mailer, you will, at the very worst, annoy a colleague.
2. Write a script to nullify all email addresses when cloning to your test environment. I always nullify ap_vendor_sites_all.remittance_email and if you're worried about internal emails, look at wf_user_roles.email_address.
3. Step 1 above will avoid the majority of email issues, but if you have leveraged the XML Bursting functionality (see post further down), this functionality does not (unfortunately) recognise or acknowledge the override email address setting. Here's what I do to get around the problem:

• Setup a site level profile on production, called it something like XX: Production SID. Set the value of this profile to your production database SID value.
• This works great until you have to actually test on non prod. That's where you need to be very careful and ensure you have followed step 2 above. The key to remember is if the system doesn't hold an email address, then it can't possibly email. When you need to test, have your test team enter test email addresses and then proceed.

There, done. Now relax and be confident that you will never get "that call" from your vendor management team asking why you sent out 23 remittance advices from 2004 to a very important customer.... again.
 
Hey, look on the bright side, at least you didn't send dunning letters..... did you.

Nightmare. You've made changes to a custom pl/sql package that is referenced by the POAPPRV workflow and all of a sudden all PO Workflows grind to a halt with the error:

[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002:  2018: Unable 
to generate the notification XML. Caused by: 2020: Error when  getting 
notification content. Caused by: ORA-04061: existing state of has been  
invalidated ORA-04061: existing state of package body 
"XXX.XXXXX(PackageNameXXX"  has been invalidated ORA-04065: not 
executed, altered or dropped package body  "XXX.XXXXX(PackageNameXXX" 
ORA-06508: PL/SQL: could not find program unit being  called

If you check on MOS, it will show all sorts of hits for this error and in my experience none of them are actually accurate. Steps to resolve:

1. In eBiz, just go to the "Workflow Manager"
2. Click oon the "Service Components" (the green tick)
3. Stop the "Workflow Inbound Notifications Agent Listener"
4. Start the "Workflow Inbound Notifications Agent Listener" 5. Go back to the notification(s) that is causing the issue and retry the activity.

Notification will be regenerated and the workflow will continue.

If you are upgrading JREs, or just want to know who uses forms based responsibilities, here's a sample SQL. Note, it assumes you have auditing switched on at the form level.

SELECT lower(user_name)
  FROM fnd_user e
 WHERE trunc(sysdate) < nvl(end_date,sysdate+1) 
   AND EXISTS
    (SELECT 'x'
       FROM apps.FND_USER_RESP_GROUPS_DIRECT a,
            apps.fnd_responsibility_tl b,
            fnd_responsibility b1,
            FND_LOGIN_RESPONSIBILITIES fl,
            FND_LOGINS U
      WHERE a.end_date is null --active responsibility
        AND b.language = 'US'
        AND a.responsibility_id = b.responsibility_id
        AND b1.responsibility_id = b.responsibility_id
        AND b1.application_id = b.application_id
        AND b1.version = '4'  --forms based responsibilities
        AND e.user_id = a.user_id 
        AND fl.responsibility_id = b.responsibility_id
        AND fl.login_id = u.login_id
        AND fl.resp_appl_id = b1.application_id
        AND trunc(fl.start_time) > trunc(sysdate-185)) -- 6 months      

If you have ever interrogated the per_people_f and per_all_assignments_f tables, you will eventually find yourself banging your head on the table when you continually get more than row for a given person_id. It's because you have to worry about effective_dates and active assignments. Quite often even when you think you've catered for everything, you still end up with multiple rows, courtesy of HR assigning multiple active assignments each with the same start and and dates.

Recenty I stumbled across the view: apps.hr_employees_current_v which is used on a form within the HR application. When I looked at the source that made up the view, I found:

CREATE OR REPLACE FORCE VIEW apps.hr_employees_current_v (business_group_id,
                                                          organization_id,
                                                          employee_id,
                                                          assignment_id,
                                                          last_update_date,
                                                          last_updated_by,
                                                          last_update_login,
                                                          creation_date,
                                                          created_by,
                                                          employee_num,
                                                          full_name,
                                                          first_name,
                                                          middle_name,
                                                          last_name,
                                                          prefix,
                                                          location_id,
                                                          supervisor_id,
                                                          set_of_books_id,
                                                          default_code_combination_id,
                                                          expense_check_address_flag,
                                                          email_address,
                                                          work_telephone,
                                                          inactive_date,
                                                          attribute1,
                                                          attribute2,
                                                          attribute3,
                                                          attribute4,
                                                          attribute5,
                                                          attribute_category
                                                         )
AS      
   SELECT p.business_group_id, a.organization_id, p.person_id,
          a.assignment_id, p.last_update_date, p.last_updated_by,
          p.last_update_login, p.creation_date, p.created_by,
          p.employee_number, p.full_name, p.first_name, p.middle_names,
          p.last_name, p.title, a.location_id, a.supervisor_id,
          a.set_of_books_id, a.default_code_comb_id,
          p.expense_check_send_to_address, p.email_address, p.work_telephone,
          b.actual_termination_date, p.attribute1, p.attribute2, p.attribute3,
          p.attribute4, p.attribute5, p.attribute_category
     FROM per_people_f p,
          per_assignments_f a,
          per_periods_of_service b,
          per_assignment_status_types past
    WHERE    
      p.business_group_id = (SELECT NVL (MAX (fsp.business_group_id), 0)
                                   FROM financials_system_params
                                  where  fsp)
      AND a.person_id = p.person_id
      AND a.period_of_service_id = b.period_of_service_id
      AND a.primary_flag = 'Y'
      AND TRUNC (SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
      AND TRUNC (SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
      AND p.employee_number IS NOT NULL
      AND a.assignment_type = 'E'
      AND a.assignment_status_type_id = past.assignment_status_type_id
      AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');      

When I removed the references to the org context views and also got rid of the business group sub select, I ended up with: 

CREATE OR REPLACE FORCE VIEW apps.hr_employees_current_v1 (business_group_id,
                                                          organization_id,
                                                          employee_id,
                                                          assignment_id,
                                                          last_update_date,
                                                          last_updated_by,
                                                          last_update_login,
                                                          creation_date,
                                                          created_by,
                                                          employee_num,
                                                          full_name,
                                                          first_name,
                                                          middle_name,
                                                          last_name,
                                                          prefix,
                                                          location_id,
                                                          supervisor_id,
                                                          set_of_books_id,
                                                          default_code_combination_id,
                                                          expense_check_address_flag,
                                                          email_address,
                                                          work_telephone,
                                                          inactive_date,
                                                          attribute1,
                                                          attribute2,
                                                          attribute3,
                                                          attribute4,
                                                          attribute5,
                                                          attribute_category
                                                         )
AS      
   SELECT p.business_group_id, a.organization_id, p.person_id,
          a.assignment_id, p.last_update_date, p.last_updated_by,
          p.last_update_login, p.creation_date, p.created_by,
          p.employee_number, p.full_name, p.first_name, p.middle_names,
          p.last_name, p.title, a.location_id, a.supervisor_id,
          a.set_of_books_id, a.default_code_comb_id,
          p.expense_check_send_to_address, p.email_address, p.work_telephone,
          b.actual_termination_date, p.attribute1, p.attribute2, p.attribute3,
          p.attribute4, p.attribute5, p.attribute_category
     FROM per_people_f p,
          per_all_assignments_f a,
          per_periods_of_service b,
          per_assignment_status_types past
    WHERE a.person_id = p.person_id
      AND a.period_of_service_id = b.period_of_service_id
      AND a.primary_flag = 'Y'
      AND TRUNC (SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
      AND TRUNC (SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
      AND p.employee_number IS NOT NULL
      AND a.assignment_type = 'E'
      AND a.assignment_status_type_id = past.assignment_status_type_id
      AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');      

I then ran the following sql against the new vew, to determine if there were any duplicates:

select employee_id, count(*) from apps.hr_employees_current_v1
group by employee_id having count(*) > 1      

returns 0 rows. I imagine it's because of the joins to per_periods_of_service and per_assignment_status_types - I have never joined to such tables before, nor did I know of their existence.

Is this a valid view to use to get the latest per_people_f and assignment details for a given employee? Any feedback welcome.

There's lots of chat on the forums about the theories to achieve this, but I hadn't seen a full example anywhere so I whipped up one for myself. The example below will send the output or log file (or both) of the concurrent program that was the last to fire in a request set. Typically you would include it after every concurrent program in the request set that you wanted to email. Or you can use it independently and supply the request id that you with to burst. I'm no expert with java (in fact this is my first stab at it), so please highlight any deficiencies!

package xx.oracle.apps.ap.cp.request;

import java.io.*;
import java.sql.*;
import oracle.apps.fnd.util.*;
import oracle.apps.xdo.delivery.*;
import oracle.apps.fnd.cp.request.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.apps.xdo.delivery.smtp.Attachment;

public class xx_burst_output implements JavaConcurrentProgram
{
/**
     * A generic method made to return the profile value of the passed profile
     * name.
     */
    private String getProfileValue(String profile) {
        String l_debug_mode = null;
        OracleCallableStatement lStmt = null;

        try {
            lStmt = (OracleCallableStatement) mConn.prepareCall(mProfileValue);
            lStmt.setString(2, profile);
            lStmt.registerOutParameter(1, java.sql.Types.VARCHAR, 0, 255);
            lStmt.execute();
            l_debug_mode = lStmt.getString(1);
            lStmt.close();

        } catch (SQLException s) {
        } finally {
            try {
                if (lStmt != null)
                    lStmt.close();
            } catch (SQLException e) {
            }
        }
        return (l_debug_mode);
    }

  public static void main(String out_file, String log_file, String subj, String from, 
                          String to, String cc,String bcc, 
                          String out_filename,String log_filename,String server, 
                          String file_type) throws Exception
  {
    // create delivery manager instance
    DeliveryManager dm = new DeliveryManager();
    // create a delivery request
    DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
    // set email subject
    req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, subj);
    // set SMTP server host
    req.addProperty(DeliveryPropertyDefinitions.SMTP_HOST, server);
    // set the sender email address
    req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM, from);
    // set the destination email address
    req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, to);
    // set the destination bcc address

    if (cc != null && !cc.equals("")){ 
      req.addProperty(DeliveryPropertyDefinitions.SMTP_CC_RECIPIENTS, cc);
    }
      // set the destination bcc address

    if (bcc != null && !bcc.equals("")){
       req.addProperty(DeliveryPropertyDefinitions.SMTP_BCC_RECIPIENTS, bcc);
    }

    //  set the content type of the email body       
    req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE, "application/pdf");
    // set the document file name appeared in the email

    if ( file_type.equals("BOTH")){
      File file=new File(out_file);
       if ( file.exists()) {

       // create Attachment 
       Attachment m = new Attachment();
       // add attachments 
       m.addAttachment(
          out_file,            // file to deliver
          out_filename,                     // file name appears in the email
          "application/pdf");                // content type

       // add the attachment to the request
       req.addProperty(DeliveryPropertyDefinitions.SMTP_ATTACHMENT, m); }

     req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, log_filename);
      //  set the document to deliver
      req.setDocument(log_file);}

  else if (file_type.equals("LOG")) {
     req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, log_filename);
     req.setDocument(log_file);}
  else {
      req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME, out_filename);
      req.setDocument(out_file);
   }
   
    // submit the request
    req.submit();
    // close the request
    req.close(); 

  } 

   String smtp_subject;
   String smtp_from;
   String smtp_to;
   String smtp_cc;
   String smtp_bcc;
   String smtp_out_filename;
   String smtp_log_filename;   
   String out_filename;
   String log_filename;   
   String v_profile;
   String v_db;
   String smtp_server;
   String file_type;
   String par_request_id;

   OracleCallableStatement lStmt = null;

    String mProfileValue = " Declare l_value     Varchar2(50); " + " BEGIN "
            + " :1 := FND_PROFILE.VALUE(:2); " + " END;";

    Connection mConn = null;   
    
    // Debug Flag
    private String mDebugFlag;

   public void runProgram(CpContext pCpContext)
   {

       OracleCallableStatement lStmt1 = null;
        
        // get LogFile object from CpContext
        LogFile lLF = pCpContext.getLogFile();

        lLF.writeln("Program Started.... ", LogFile.STATEMENT);  

        // get ReqCompletion object from CpContext
        ReqCompletion lRC = pCpContext.getReqCompletion();

        int request_id = pCpContext.getReqDetails().getRequestId();
   
        // get the JDBC connection object
        Connection mJConn = pCpContext.getJDBCConnection();

        String lsmtp_server = 
        "select fscpv.parameter_value "+
        "  from fnd_svc_comp_params_tl fscpt "+
        "      ,fnd_svc_comp_param_vals fscpv "+
        "      ,fnd_svc_components fsc " +
         "where fscpt.parameter_id = fscpv.parameter_id "+ 
           "and fscpv.component_id = fsc.component_id " +
           "and fscpt.display_name = 'Outbound Server Name' "+
           "and fsc.component_name = 'Workflow Notification Mailer'";
         try
        {
           PreparedStatement lStmt = mJConn.prepareStatement(lsmtp_server);
           ResultSet lRs = lStmt.executeQuery();         

          while( lRs.next() )
           {
                smtp_server = lRs.getString(1);
           } 
         
        }

        catch (Exception e) { 
          lLF.writeln("Exception lsmtp_server " +lsmtp_server, LogFile.STATEMENT); 
          lLF.writeln("Exception lsmtp_server " +e.toString(), LogFile.STATEMENT); 
        }
                
        // get parameter list object from CpContext
        ParameterList lPara = pCpContext.getParameterList();

        // get OutFile object from CpContext
        OutFile lOF = pCpContext.getOutFile();
    
        lLF.writeln("This Concurrent Request ID: " + request_id, LogFile.STATEMENT);  

        String outputFileName= pCpContext.getOutFile().getFileName();  

        String lQuery =
            " select outfile_name, logfile_name " +
            " from fnd_concurrent_requests a " +
            " where request_id =  " +
            " (select max(request_id) " +
            "   from fnd_concurrent_requests b "+
            "   where priority_request_id = (select priority_request_id  " +
            "                                  from fnd_concurrent_requests c" +
            "                                 where request_id = :1 )" +
            "     and request_id < :1 " +
            "     and request_type != 'S')";

        String pQuery =
            " select outfile_name, logfile_name " +
            " from fnd_concurrent_requests a " +
            " where request_id = :1 ";       

        while (lPara.hasMoreElements()) 
        { 
                NameValueType aNVT = lPara.nextParameter(); 
                if ( aNVT.getName().equals("SUBJECT") ) 
                     smtp_subject = aNVT.getValue(); 

                if ( aNVT.getName().equals("SMTP_FROM") ) 
                      smtp_from  = aNVT.getValue(); 

                if ( aNVT.getName().equals("SMTP_TO_RECIPIENTS") ) 
                       smtp_to = aNVT.getValue(); 

                if ( aNVT.getName().equals("SMTP_CC_RECIPIENTS") ) 
                    smtp_cc = aNVT.getValue(); 

                if ( aNVT.getName().equals("SMTP_BCC_RECIPIENTS") ) 
                      smtp_bcc = aNVT.getValue(); 

                if ( aNVT.getName().equals("SMTP_CONTENT_LOG_FILENAME") ) 
                      smtp_log_filename = aNVT.getValue();    

                if ( aNVT.getName().equals("SMTP_CONTENT_OUT_FILENAME") ) 
                      smtp_out_filename = aNVT.getValue();                          

                if ( aNVT.getName().equals("OUT_LOG_BOTH") ) 
                      file_type = aNVT.getValue();    

                if ( aNVT.getName().equals("REQUEST_ID") ) 
                      par_request_id = aNVT.getValue();
                    //  int par_request_id_num = Integer.parseInt(par_request_id);
        } 

        try
        {

     lLF.writeln("*************************************************", LogFile.STATEMENT); 
     lLF.writeln("* Param Subject: " + smtp_subject, LogFile.STATEMENT); 
     lLF.writeln("* Param From: " + smtp_from, LogFile.STATEMENT); 
     lLF.writeln("* Param To: " + smtp_to, LogFile.STATEMENT); 
     lLF.writeln("* Param CC: " + smtp_cc, LogFile.STATEMENT); 
     lLF.writeln("* Param BCC: " + smtp_bcc, LogFile.STATEMENT); 
     lLF.writeln("* Param Out Filename: " + smtp_out_filename, LogFile.STATEMENT);  
     lLF.writeln("* Param Log Filename: " + smtp_log_filename, LogFile.STATEMENT);  
     lLF.writeln("* Param Override request ID: " + par_request_id, LogFile.STATEMENT);
     lLF.writeln("* Param Out File, Log File or Both: " + file_type, LogFile.STATEMENT);
     lLF.writeln("* Derived SMTP Server: " + smtp_server, LogFile.STATEMENT); 
     lLF.writeln("*************************************************", LogFile.STATEMENT); 

     
          if (!par_request_id.equals("")) { 
           PreparedStatement lStmt = mJConn.prepareStatement(pQuery);
           lStmt.setInt(1, Integer.parseInt(par_request_id) ); 
           ResultSet lRs = lStmt.executeQuery(); 
 
           while( lRs.next() )
           {
                out_filename = lRs.getString(1);
                log_filename = lRs.getString(2);
           }      
          }      
        else {
           PreparedStatement lStmt = mJConn.prepareStatement(lQuery);
           lStmt.setInt(1, request_id );
           ResultSet lRs = lStmt.executeQuery(); 
 
           while( lRs.next() )
           {
                out_filename = lRs.getString(1);
                log_filename = lRs.getString(2);
           } 
         lStmt.close();
        }

        String base_out_name = (new File(out_filename)).getName();
        String base_log_name = (new File(log_filename)).getName();        

        if (smtp_log_filename.equals("") || smtp_log_filename.equals(null))
        {
          smtp_log_filename = base_log_name;
        }
         if (smtp_out_filename.equals("") || smtp_out_filename.equals(null))
        {
          smtp_out_filename = base_out_name;
        }
        if (file_type.equals("BOTH")) {      
          lLF.writeln("Log Filename selected for burst: " + log_filename, LogFile.STATEMENT);
          lLF.writeln("Filename renamed to: " + smtp_log_filename, LogFile.STATEMENT);
          
          lLF.writeln("Out Filename selected for burst: " + out_filename, LogFile.STATEMENT);
          lLF.writeln("Filename renamed to: " + smtp_out_filename, LogFile.STATEMENT);
        }
        else if (file_type.equals("LOG")) {
          lLF.writeln("Log Filename selected for burst: " + log_filename, LogFile.STATEMENT);
          lLF.writeln("Filename renamed to: " + smtp_log_filename, LogFile.STATEMENT);
        }
        else {
          lLF.writeln("Out Filename selected for burst: " + out_filename, LogFile.STATEMENT);
          lLF.writeln("Filename renamed to: " + smtp_out_filename, LogFile.STATEMENT);
        }      
      try { 
        main(out_filename,log_filename, smtp_subject,
        smtp_from,smtp_to,smtp_cc,smtp_bcc,smtp_out_filename, smtp_log_filename,
        smtp_server, file_type );
        lLF.writeln("Burst complete.... ", LogFile.STATEMENT); 
       }
        catch (Exception e) { 
        lLF.writeln("Exception 1 " +e, LogFile.STATEMENT); 
      }
        
       lRC.setCompletion(ReqCompletion.NORMAL, "Request Completed Normal");   

       lLF.writeln("Program Ended.... ", LogFile.STATEMENT);
       
        }
        catch (SQLException e)
        {
              lRC.setCompletion(ReqCompletion.ERROR, e.toString());
        }
        finally
        {
              pCpContext.releaseJDBCConnection();
        }
    }
}

Compile this to a class file, load it to your custom $JAVA_HOME area, register the concurrent program with the parameters: 

           "SUBJECT"                      -- subject of the email 
           "SMTP_FROM"                    -- who's sending it?
           "SMTP_TO_RECIPIENTS"           -- to whom the email is addressed
           "SMTP_CC_RECIPIENTS"           -- optional carbon copy (separated by commas)
           "SMTP_BCC_RECIPIENTS"          -- optional blind carbon copy (separated by commas)
           "SMTP_CONTENT_LOG_FILENAME"    -- optional log file rename
           "SMTP_CONTENT_OUT_FILENAME"    -- optional out file rename
           "OUT_LOG_BOTH"                 -- send "LOG", "OUT" or "BOTH"
            "REQUEST_ID"                   -- override request_id (sending this output, log)

and include it in the request set as a new stage right after the stage you want to burst the output of. Bob's your uncle.

Improvement suggestions (code or other) are welcome.

Was horrified this morning when a production set fired outside of the scheduled request time.
Here's how to reproduce on 11.5.10.2 RUP7:
Note: This applies to both request sets and individual requests (please test yourself).

1. log into your local 11.5.10.2 environment as sysadmin
2. schedule the active users report to run on specific days every monday to friday from now ** SEE NOTE AT BOTTOM
3. let it run and complete
4. you will see it return to "Pending Scheduled" status
5. click on view details button
6. update the priority to something else, say 10 or 60, whatever it's irrelevant
7. click ok
8. notice the "Transaction complete: 1 records applied and saved"
9. Hit refresh and notice that the report has fired immediately without reason.
** It's important to set it to run on specific days, monday to friday. This issue seems to only appear when choosing this option.
Expected result: only fire when the schedule says it should fire! I'll let you know if I get a bug number.

<<UPDATE: Bug 10366158 raised for this, it also exists on R12 to 12.1.3 >>
<<UPDATE: Patch 10366158 has been released for this and I've tested it and it's working as expected > 

Sometimes it's necessary to compare profile options across levels. The SQL below will compare across two responsibilities, but can be altered to compare at other levels.

SELECT a.user_profile_option_name, 
       a.profile_option_value, 
       b.user_profile_option_name, 
       b.profile_option_value 
  FROM ((SELECT c1.responsibility_name,
                c2.user_profile_option_name,
                decode(level_id,10001,'Site', 10002,'Appl', 10003,'Resp',
                10004,'User', 10005,'Server', 10006,'Organization', level_id) "Level",
                profile_option_value, 
                level_value lvl_val
           FROM fnd_profile_option_values a1, 
                fnd_profile_options b1,
                fnd_responsibility_tl c1,
                fnd_profile_options_tl c2
          WHERE a1.profile_option_id = b1.profile_option_id
            AND c1.responsibility_id = nvl(a1.level_value,c1.responsibility_id) 
            AND c1.responsibility_name = 'XXXX'
            and b1.profile_option_name = c2.profile_option_name 
            and c2.language = 'US'
            AND c1.language = 'US')) a,
        (SELECT c1.responsibility_name, 
                c2.user_profile_option_name,
                decode(level_id,10001,'Site', 10002,'Appl', 10003,'Resp',
                10004,'User', 10005,'Server', 10006,'Organization', level_id) "Level",
                profile_option_value, 
                level_value lvl_val
           FROM fnd_profile_option_values a1, 
                fnd_profile_options b1,
                fnd_responsibility_tl c1,
                fnd_profile_options_tl c2
          WHERE a1.profile_option_id = b1.profile_option_id
            AND c1.responsibility_id = nvl(a1.level_value,c1.responsibility_id) 
            AND c1.responsibility_name = 'XXXXX'
            and b1.profile_option_name = c2.profile_option_name 
            and c2.language = 'US'
            AND c1.language = 'US') b
 WHERE a.user_profile_option_name  = b.user_profile_option_name (+)
   AND (a.profile_option_value != nvl(b.profile_option_value,a.profile_option_value) 
        OR b.profile_option_value IS NULL);
        

A handy SQL:

SELECT user_profile_option_name,
       decode(level_id,10001,'Site', 10002,'Appl', 10003,'Resp',
       10004,'User', 10005,'Server', 10006,'Organization', level_id) "Level",
       profile_option_value, 
       level_value lvl_val
  FROM fnd_profile_option_values, 
       fnd_profile_options_vl
 WHERE profile_option_name = 'XXX'
   AND fnd_profile_option_values.profile_option_id = fnd_profile_options_vl.profile_option_id;

Following another superb post by Gareth Roberts in this blog, I began a project to migrate the old and rather antiquated remittance advices produced by workflow into a more modern looking and flexible pdf solution. Gareth's post is complete in all respects, but I did make a few changes here and there and thought it would help everyone if I noted them down here for all to benefit.

The first major difference is the xml bursting control file. In this example you can see how I managed to get around the issue of the email body being contained all in one line. The trick here is to use CDATA HTML as the body. Although this is probably documented somewhere in a manual on page 423 or thereabouts, I didn't spot it. The other difference is the filter section. Here I filter out different templates based on a filter I am passing in the XML. This technique is quite standard but I didn't find many examples around, so I am posting this for all to see here.

What this control file doesn't show is at the end of the subject="${C_SUBJECT}"> line you need to have <![CDATA[ then start with your normal html. To close the HTML, use </html>]]>.

The filter syntax can be used to filter out XML and apply different templates. For example: 

<xapi:template type="rtf" location="xdo://SQLAP.XXBSRA.en.00/?getSource=true" 
filter="/XXBSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS[C_FILTER='XX:XX']"></xapi:template>

will use the template stored under SQLAP.XXBSRA.en.00 (note the 00 means that no localisation has been applied).

<xapi:template type="rtf" location="xdo://SQLAP.XXBSRA1.en.00/?getSource=true" 
filter="/XXBSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS[C_FILTER='XX:XX']"></xapi:template>

will use the SQLAP.XXBSRA1.en.00 template. Send down a C_FILTER='XX:XX' to control which templates are used.

The second major difference was how I fired off the solution. My requirement was to mimic the default solution provided by Oracle which is that when "a" payment is made, be it a manual payment (quick payment) or a batched payment, the remittance advice should be sent. Gareth's solution didn't touch on how to actually fire off the burst, apart from the automation section detailing how to automatically fire the bursting concurrent program from the after report trigger. So I turned back to the old faithful BES (Business Event System).

Step 1. Disable the standard event subscription sitting against oracle.apps.ap.payment (the one which calls ap_payment_event_wf_pkg.rule_function) . 

Step 2. Create a new event subscription to point towards a new package/function (as detailed below in step 3). Setup the new subscription to run at phase 20, rule data = KEY.

Step 3. Create a new package under the apps schema with a function. A sample package can be found at this link. This function will strip out the check_id and org_id from the incoming event subscription and will identify if the check_id is part of a quick payment or a batch and will fire the report using the correct parameters

The benefits of this approach are:

1. It's similar to the existing standard oracle functionality

2. The user just thinks that the XXX Separate Remittance Advice is another step in the payment processing (user can see the remittance advice programs running just after the confirmation of the payment batch).

3. It's impossible to burst the same quick payment or batch twice (duplicate emails to suppliers), as it's the internal subscription which will call the report, somewhat more reliable than relying on users! 

Live in production with over 40,000 remittance emails sent, as yet - no issues.

Often asked, not so easy to find out. Here's an SQL that will help:

  SELECT distinct frg.request_group_name, 
         fav.application_name ,
         ft.user_concurrent_program_name     
    FROM fnd_application_vl fav,
         fnd_request_groups frg,
         fnd_request_group_units frgu ,        
         fnd_concurrent_programs_tl ft   
   WHERE frgu.request_group_id = frg.request_group_id
     AND frgu.request_unit_id = ft.concurrent_program_id
     AND ft.language = 'US'     
     AND t.user_concurrent_program_name LIKE 'XX%'
     AND fav.application_id = frg.application_id

Came across a great post by Gareth Roberts about validating email addresses on forms. Two things:
1. I never knew about the regex_substr function!
2. I never knew it was so easy to implement.

I ended up using his solution to enforce multiple email addresses and simply used the exact solution, except I used the following regex to validate the multiple emails separated only by commas:

\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*([,]\s*\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)*

It's a headache to read but basically validates that email1@email.com,email2@email.com, email3@email.com is fine. email1@email.com;email2@email.com isn't and email@email isn't either.

The last step of our multi-node setup was to ensure that the logs were available from both nodes. So we setup the shared mount and expected it to just "work". I should have guessed that it wouldn't, so I spent the next couple of hours digging through the doco - only to come across this in a RAC installation guide (note, it was nowhere to be found in the PCP setup doco):

In order to have log and output files available to each node during an extended node failure, each log and out directory needs to be made accessible to all other CP nodes in the cluster (placed on shared disk).

In order to view log and output files from a failed node during an outage/node failure, the FNDFS_<HOST> entry in tnsnames.ora under the 8.0.6 ORACLE_HOME location on each node should be configured using an ADDRESS_LIST following the example below, to provide connect time failover. This entry should be placed in the tnsnames.ora file on each node that supports Concurrent Processing, with the local node first in
the ADDRESS_LIST entry.

FNDFS_coe-srv5-pc=(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr5_pc)(PORT=1231))
(ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr7_pc)(PORT=1232)))
(CONNECT_DATA=(SID=FNDFS))
)

You might be thinking that including a Reverse Proxy on top of a Load Balancer might be a bit of a strange topoplogy, but that's exactly what I was asked to manage recently, as part of a stress and volume test HA build. Below I have detailed the config change steps that were required and note some of the "inconsistencies" in the Oracle Metalink notes. Hopefully, someone out there won't have to go through the same pain that I had to go through.

You can use Rapid Clone to clone a node and add it to the existing Applications system, a process also known as scale up or scale out. The new node can run the same services as the source node, or different services. Follow the instructions in the Application tier part of sections 19.2 of the Rapid Clone for R12 document. Prepare the source system, copy it to the new node and configure it by running the adcfgclone.pl on the new node.

• After adcfgclone.pl completes on the new node, source the Applications environment and run the following commands on the target system:

$ cd $COMMON_TOP/clone/bin
$ perl adaddnode.pl

Note: if you receive the error: Adaddnode.Pl failed with: ORA-00001: unique constraint (APPLSYS.AD_APPL_TOPS_U2) violated

Please refer to metalink note: [ID 394615.1]

To enable the load balancer (in this case, it's the reverse proxy sitting on top of the load balancer), we had to make the following changes:

Login to primary NODE

Take a backup of the original context file at location $CONTEXT_FILE

Edit $CONTEXT_FILE

search s_webentryurlprotocol & change to https
Search s_webentryhost change to the reverse proxy hostname (only the hostname)
Search s_webentrydomain change to domain (fully qualified)
Search s_active_webport change to the reverse proxy port
Searcg s_login_page change to https://reverseproxy.fullyqualified domain:port/OA_HTML/AppsLogin
Search s_external_url Change to https://reverseproxy.fullyqualified domain:port
search s_enable_sslterminator Remove the #  
search s_long_running_jvm change from true to false 

The two configurations above in bold do not apear in the load balancing note that oracle provides.

Run the autoconfig ($ADMIN_SCRIPTS_HOME/adautocfg.sh)
Restart application tier services

REPEAT STEPS for SECONDARY APP NODE

To check that the load balancer is actually balancing, you can monitor the $LOG_HOME/ora/10.1.3/Apache/access_logs*

Use tail -f to watch the access logs as you log into each of the nodes.

The Oracle Applications Manager (OAM) is an inbuilt and "free" component to the Oracle E-Business Suite. For years I had been hearing mixed reviews of its capabilities and has dismissed it as yet another component that would be forgotten about. Recently, I was asked to find a viable solution to monitor concurrent request jobs (not managers), out of the box and without using the antiquated Oracle Alerts module. After a lot of Googling and some digging around on Metalink, I found that the OAM had undergone a fairly major renovation as part of a late patch to 11i.

It's now possible with R12 to use OAM to monitor concurrent jobs, which should signal another nail in the coffin for the antiquated Oracle Alerts module.

If you go to the OAM dashboard and then click on setup you will see down the left hand side a menu with "Concurrent Requests" on it. If you click this, you will see a page similar to the one below:

In the example above, I have specified that I wish to be alerted if either of the programs runs for longer than 1 minute. So I simply followed the wizard, ensured that my notification mailer was correctly configured and then ran the "Gather Schema Statistics" program, knowing full well that it will take longer than one minute. Sure enough, I received an email a short while later, the output of it is below: 

This is a pretty good start, but the obvious questions are raised: 

1. Can a reference (concurrent request id) be added to the native alert which is generated by OAM (BES alert) to help us reconcile the alert to the concurrent request?
2. Can alerts be sent to different email addresses depending on which alert is fired (say at a concurrent request level) or at a status level?
3. Is it possible to include or embed the report or log from the failed concurrent program to the email alert (and subsequent notification)?
4. Is it possible to alter the alert msg by alert type (i.e. if an error occurs, then have different static text to when a warning occurs)?

I have asked Oracle to provide feedback on these questions and will update the blog when I get that feedback.

 <<UPDATE: I never got that feedback, so if anyone out there has experience setting up OAM to achieve the requirement above, do leave a comment!  >>

As an Oracle E-Business Suite consultant with a hybrid skill set of technical and functional, I often get asked to jump in and manage performance related issues. Recently I came across a handy tool developed by Oracle called SQLTXPLAIN. I was rather shocked that I had not come across this tool before, as for years it seemed that SQL performance tuning was a black art that only the most experienced could surmount.

Oracle Metalink Note: 215187.1 details the installation and options for execution, but I thought it would be worthwhile uploading a sample output here to allow everyone to quickly see the output that this tool can generate.

For a sample output, please click on this link. 

In terms of its effectiveness, I believe that this tool can help to bridge the gap betweeen the project management teams dealing with stress and volume test phases of the project and the performance tuning DBAs.
 

So you've got the CEO knocking on your door asking why it's taking 3 mins to open up his PO notification.

 The following SQLs are useful to identify long running SQLs:

To find out the long operations 
-----------------------------------------------------------
select l.sid,
l.serial#,
s.username,
l.start_time,
l.time_remaining,
l.elapsed_seconds
from v$session_longops l,
v$session s

This is a good one for finding long operations (e.g. full table scans). 
If it is because of lots of short operations, nothing will show up.
-----------------------------------------------------------
COLUMN percent FORMAT 999.99 


SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
-----------------------------------------------------------
This script provides information on active SQL.
-----------------------------------------------------------
select s.sid,
s.status,
s.process,
s.osuser,
a.sql_text,
p.program
from v$session s,
v$sqlarea a,
v$process p
where s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.schemaname='APPS'
and s.status='ACTIVE'

-----------------------------------------------------------
Capturing the sql in a long running query from v$sql
-----------------------------------------------------------

SELECT username, sql_text, sofar, totalwork, units FROM v$sql,
v$session_longops
WHERE sql_address = address
AND sql_hash_value = hash_value
ORDER BY address, hash_value, child_number;
-----------------------------------------------------------
The below query will show the currently running for more than 60 seconds. 
-----------------------------------------------------------
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text 
from v$session s join v$sqltext_with_newlines q on s.sql_address = q.address 
where status='ACTIVE' and type <>'BACKGROUND' 
and last_call_et> 60 order by sid,serial#,q.piece 
-----------------------------------------------------------

This one shows SQL that is currently "ACTIVE":-
-----------------------------------------------------------
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM' and s.username <> 'SYS'
order by s.sid,t.piece
-----------------------------------------------------------

This shows locks. Sometimes things are going slow, but it's because it is blocked 
waiting for a lock:
-----------------------------------------------------------
select
  object_name, 
  object_type, 
  session_id, 
  type,                 -- Type or system/user lock
  lmode,        	-- lock mode in which session holds lock
  request, 
  block, 
  ctime                 -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/
-----------------------------------------------------------

So you're locked out of PROD and quite rightly, but when they clone to TEST, you need to get sysadmin but only have the apps pwd. Here's how to do it:

begin
fnd_user_pkg.addresp(username => 'USERNAME'
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE
,end_date => SYSDATE+1000);
end;

The reviver's sole purpose is to revive the Internal Concurrent Manager (ICM) when it dies because the database connection isn't available. When the ICM no longer gets a database connection it kills itself and spawns the reviver. The reviver will look every 30 seconds to see if it can logon to the database as APPS. When it does it restarts the ICM. The reviver is started by/when starting the ICM by passing a parameter reviver="enabled".

Well, this is what the user manual says anyway. When I tested this feature, it simply didn't work on a fresh install of r12.1.1. Test included: 
Step 1 - check number of FNDLIBR processes running

$ps- ef | grep FNDLIBR
8

Step 2 - shutdown immediate the database

sql> shutdown immediate

Step 3 - check to see if the reviver process has been kicked off

$ps -ef | grep reviver | grep -v grep
<<nothing>>

Step 4- check how many FNDLIBR are running now

$ps -ef | grep FNDLIBR | grep -v grep
<<nothing>

ICM down and no automatic restart.

What the manual doesn't mention is that if the database has been taken down in a coordinated manner (i.e. shutdown or shutdown immediate), then the reviver functionality doesn't work. The reviver functionality is only intended to bring back the internal manager in the event of an unexpected database outage (db failure or similar).

“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.

As of Oracle 10g R1, Oracle have decided to change the default profile to set the failed_login_attempts =10. Yesterday, we had a scenario whereby a developer attempted to guess the APPS password 10 times. Consequently, the APPS tier ground to a halt, even stopping the login page from coming up. After identifiying the issue and unlocking the account, the system returned to normal. A quick scroll through the best practices guide (Note: 189367.1 Best Practices for Securing the E-Business Suite) shows the following: 

IMPLEMENT TWO PROFILES FOR PASSWORD MANAGEMENT

The database provides parameters to enforce password management policies. However, some of the database password policy parameters could lock-out the E-Business Suite. Because of this, we make specific recommendations for or against using certain management features depending upon schema type.

>What does it all mean? Create two database profiles: one for middle-tier application schemas and one for human beings. Assign middle-tier application schemas to the first profile (with unlimited profiles) and all accounts used by administrators to the second profile.

Symptom

If you find the following error in the log file:

end std out.
ld.so.1: sqlplus: fatal: libclntsh.so.10.1: open failed: No such file or directory

Then it is due to a dce conflict.

Solution

cd /usr/lib
mv libdce.so libdce.so.orig

remove oracle home
untar original oracle home from source

re-do the db tier clone adcfgclone.pl dbTier step

cd /usr/lib
mv libdce.so.orig libdce.so

nb: This is only applicable I believe to Solaris Sparq 9

Oracle provides a mechanism to export personalisations, but does not provide a way to migrate those exports across environments. The main reason being that if you have personalisations exisiting at the responsibility level, Oracle hard code the responsibility ID into the export, rather than the responsibility KEY. Recently I wrote a shell script to take a tar file export from the source environment and load it into the target environment, without needing connectivity to the source. There is probably an easier way to do it, but it works for me.

It extracts the tar file, loops through the xml files, greps out the responsibility key, logs onto the target database to find the corresponding id, rectifies the filesystem with the new id, loads the xml into the database using the new responsibility id.

Assumptions: 

• Your $DPL_DIR is set to the directory you want to deploy from.
• Your tar file is called "personalizations.tar"
• Your apps password is stored in $PASS
• You have set your $LOG_DIR
   

Note that this script is for testing purposes only and cannot be used in production.

##########################################################################
# Load Personalizations
##########################################################################
load_personalizations ()
{
echo "**********************************************"
echo "Executing Personalizations Stage"
echo "**********************************************"
DATE_TIME=`date +%d%m%Y%H%M`
echo "Making Personalisation Staging Area: \$DPL_DIR/install/bin/per"
PER_DIR=$DPL_DIR/install/bin/per
if [ ! -r "$PER_DIR" ]; then
   mkdir $PER_DIR
fi
if [ -r $PER_DIR/oracle ]; then
  echo "Removing previous oracle personalisation home"
  rm -rf $PER_DIR/oracle
fi
if [ -r $DPL_DIR/install/bin/personalizations.tar ]; then
    cp $DPL_DIR/install/bin/personalizations.tar $PER_DIR
    cd $PER_DIR
    tar -xf $PER_DIR/xxtlspersonalizations.tar
    if [ $? = 0 ]; then
      echo "Successfully Extracted TAR"
    else
      echo "Error Extracting TAR: "$PER_DIR/personalizations.tar
    fi
    cd $DPL_DIR
else
  echo "Personalization TAR File Does Not Exist in: "$DPL_DIR/install/bin/personalizations.tar
fi

find $PER_DIR/oracle -name "*.xml" -print | sed 's/^.\///g' > $PER_DIR/tmp_file.txt
if [ -r  $PER_DIR/out.txt ]; then
   rm $PER_DIR/out.txt
fi
echo "Building New Filesystem Responsibility IDs"
echo "Working\c"
number=0;
for resp in `cat $PER_DIR/tmp_file.txt`; do
   resp1=`cat $resp | grep "responsibilityKey" | awk -F"=" \
         '{ print $7}' | awk -F" " '{print $1}' | sed 's/"//g'`
   if [[ $number = *+(0|2|4|6|8) ]];then
     echo ".\c"
   fi
   rep_id=`sqlplus -s /nolog <<-EOF
   connect apps/${PASS}
   set heading off
   select responsibility_id
   from fnd_responsibility
   where lower(responsibility_key)='${resp1}';
   exit;
EOF`
   resp2a=`dirname $resp`
   resp3a=`dirname $resp2a`
   final_rep=`echo $rep_id | sed 's/Connected. //'`
   if [ "${final_rep}" == "no rows selected" ]; then
     echo "Failed to match responsibility: "$resp1
     log_error "PERZ MATCH RESP"
   fi
   if [ $resp2a != $resp3a"/"$final_rep ]; then
       echo "mv "$resp2a" "$resp3a"/"$final_rep >> $PER_DIR/out.txt
   fi
   number=`expr $number + 1`
done
echo " "
cat $PER_DIR/out.txt | sort | uniq > $PER_DIR/final_per.sh
rm $PER_DIR/out.txt
rm $PER_DIR/tmp_file.txt
chmod +x $PER_DIR/final_per.sh
echo "Successfully Built final_per.sh"
echo "Copying final_per.sh to log directory for future reference"
cp $PER_DIR/final_per.sh $LOG_DIR/final_per.$DATE_TIME.log
sh $PER_DIR/final_per.sh
find $PER_DIR/oracle -name "*.xml" -print | sed 's/^.\///g' > $PER_DIR/tmp_file.txt
for file in `cat $PER_DIR/tmp_file.txt`; do
  tping=`tnsping $TWO_TASK | grep "Attempting to contact" \
        | sed 's/Attempting to contact //g'`
  cp $file $PER_DIR
  short_name=`basename $file`
  dipname=`dirname $file`
  new_base=`echo $dipname | sed 's@'${PER_DIR}'@@'`
  string2="java oracle.jrad.tools.xml.importer.XMLImporter $short_name -username "'"
'apps'"'" -password "'"'******'"'" -dbconnection "'"'$tping'"'" -rootdir . -rootPack
age $new_base"
  echo "JAVA LOAD COMMAND: "$string2
  cd $PER_DIR
  java oracle.jrad.tools.xml.importer.XMLImporter $short_name -username "apps" -pass
word $PASS -dbconnection "$tping" -rootdir . -rootPackage $new_base | tee $LOG_DIR/$
short_name.$DATE_TIME.log
  if [ $? = 0 ]; then
     echo "Successfully Loaded Personalisation: "$short_name
  else
     echo "Failed to Load Personalisation: "$short_name
  fi
  cd $DPL_DIR
done
rm $PER_DIR/tmp_file.txt
}

Whilst managing the installation of r12.04, our build team came across the following issues:

• Datafiles, archive, log files can only be nominated into one directory. It's not possible to specify multiple directories for each datafile when using the rapid installer. This can be resolved later.
• NFS mount issue. Ensure that when you create the nfs mount that you don't change the name of the mount directory (with a mv "mount name"). This will cause the installer to fail.
• Got to 75% then error with zip file. This turned out to be a corrupt zip file within the staging area. Run md5 check on unzipped stage. Metalink notes: 802195.1 & 467263.1
• Received  error:  logs/ora/10.1.2/install/make_06031211.logcat: cannot open $ORACLE_HOME/lib32/ldflags Autoconfig Failed. This was the first indication of a major installation failure. Turned out that the ldflags was pointing to: /d2/R12/ab/apps/RUP4XB6/ which of course didn't exist on this host. My first inclination was that this is an internal oracle box. When I looked further i noticed that lots of files in the $ORACLE_HOME/lib & lib32 directories were pointing to the wrong (non existent) locations. After much debate with Oracle Support, it turned out to be an issue with the oracle homes on that box. After the failed install (mentioned above), I had cleaned the failed install by following the oracle note: 110372.1. This note does not mention cleaning the oracle homes, it only suggests removing the global inventory. If you are in a co-hosted environment, it is not possible to remove the global inventory; therefore I used the OUI to remove the OHs before retrying the install. After cleaning the OHs, the install finally succeeded.