Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: June 2011

Oracle E-Business Suite 11i on Oracle VirtualBox

Sometime ago I had to recover my Windows 7 laptop and had to format it along the way. Not much of a problem that except that I had this Oracle E-Business Suite(EBS) 11i instance running on VMware and I forgot to back up the virtual machine. Thankfully it was a Vision instance and I had not done much on it, hence the loss was negligible. I had been planning to re-install EBS 11i since then but never managed to find the time until today. In the meantime I have been trying out Oracle VirtualBox on both Windows 7(64-bit) and Windows XP hosts and found it to be much better than VMware for most of my purposes. As of now, I have

  • Ubuntu, Debian, Fedora, Chrome OS and RHEL 4(with a Oracle 10g database installed on it) guests running on the Windows XP host
  • RHEL 4.5(with Oracle EBS 11i installed on it) guest running on the Windows 7(64-bit) host

OK, enough rambling already. I had an earlier installation of a RHEL 4 update 5 guest on the Windows 7(64-bit) system using Oracle VirtualBox(4.0.2). This document seemed to be quite perfect for the Oracle EBS 11i installation today. After applying all the the required patches and creating the users and groups, I saw that version 4.0.8 of VirtualBox was available. Upgrading from 4.0.2 to 4.0.8 was easy, just download the installer from http://www.virtualbox.org/wiki/Downloads and run it. It will upgrade the existing version.

The laptop I am using has 3GB of RAM(pathetic isn’t it) . I decided to allocate exactly half of the available memory for the virtual machine(VM). Having used more than half the system memory for one of my VMs earlier and having seen the way in which VirtualBox Manager hung during the installation, I knew better. I allocated around 120GB of dynamic storage for the VM since I would not be using a staging area for the EBS installation. Note that dynamic storage here indicates that the storage space can only increase. For instance, if I allocate 120GB to my VM and it uses 10GB initially, then only 10GB of space will be used up in the actual hard drive. When the VM swells up to 120GB then the actual hard drive will also show a usage of 120GB. However, if at a later stage I delete files from the VM so that it decreases in size to 50GB, the hard drive will still show that 120GB of space has been taken up in the hard drive. Also note that RHEL4 guests require IDE controller in order to work properly. Check here for supported VirtualBox guests.

The rest of the installation follows the document mentioned earlier. Instead of creating a staging directory, I installed from the ISO images to save around 24GB of space in the VM. There was this issue while using ISO images though.

VirtualBox could not unmount the previous ISO image each time I tried to use a new one, I had to select the force option. Using the CD/DVD drive from the host also leads to the same problem.

With the installation done, I needed to make sure that I could access Oracle EBS from the Windows 7(64-bit) host. This should be very easy if you use the ‘Host Only Adapter’ option for networking. But since I has not patched up my system to the required level, I had to tweak Internet Explorer 8 so that I could open Oracle EBS 11i Forms without the browser crashing each time. Following are the list of changes I had to make:

  1. Disabled each and every add-on
  2. Added the Oracle EBS URL to the list of Trusted Sites
  3. Ran Internet Explorer as administrator
  4. Unchecked the ‘Enable memory protection to help mitigate online attacks’ option in Internet Explorer.

Update: Using JInitiator 1.3.1.26 seems to be offer a more glitch-free experience 🙂

Advertisements

Sample code for creating task using jtf_tasks_pub.create_task API

Here is a working(at least in my instance) sample of creating a Task against a specific Service Request using the jtf_tasks_pub.create_task API.

create or replace procedure xxx_create_task as
  v_user_id       number;
  v_resp_id       number;
  v_appl_id       number;
  l_msg_count     number;
  l_msg_data      varchar2(2000);
  l_return_status varchar2(1);
  l_task_id       number;
  v_resource_id   number;
  v_incident_id   number;
begin

  --the task is being created against Service Request#1000004940
  select incident_id
    into v_incident_id
    from cs_incidents_all_b
   where incident_number = '1000004940';

  --Service Request#1000004940 already has some tasks, the task which we are going to create will
  --have the same owner as the previous tasks
  select distinct owner_id
    into v_resource_id
    from jtf_tasks_vl
   where source_object_id = v_incident_id;

  --required for fnd_global.apps_initialize()
  select user_id
    into v_user_id
    from apps.fnd_user
   where upper(user_name) in ('TEST_USER');

  --required for fnd_global.apps_initialize()
  select fa.application_id, frt.responsibility_id
    into v_appl_id, v_resp_id
    from apps.fnd_user_resp_groups  furg,
         apps.fnd_application       fa,
         apps.fnd_responsibility_tl frt
   where fa.application_short_name = 'CSS'
     and upper(frt.responsibility_name) =
         upper('Test Responsibility')
     and fa.application_id = frt.application_id
     and furg.responsibility_application_id = fa.application_id
     and furg.responsibility_id = frt.responsibility_id
     and furg.user_id = v_user_id
     and rownum = 1;

  fnd_global.apps_initialize(user_id      => v_user_id,
                                  resp_id      => v_resp_id,
                                  resp_appl_id => v_appl_id);

  --call the API
  jtf_tasks_pub.create_task(p_api_version             => 1.0,
                            p_init_msg_list           => fnd_api.g_true,
                            p_commit                  => fnd_api.g_false,
                            p_task_name               => 'My Task',
                            --this is the task_type_id for 'My Task' obtained from jtf_task_types_vl
                            p_task_type_id            => 11269,
                            --task_staus_id obtained from jtf_task_statuses_vl
                            p_task_status_id          => 10,
                            --task_priority_id obtained from jtf_task_priorities_vl
                            p_task_priority_id        => 3,
                            p_owner_type_code         => 'RS_EMPLOYEE',
                            p_owner_id                => v_resource_id,
                            p_show_on_calendar        => 'Y',
                            p_planned_start_date      => sysdate,
                            p_planned_end_date        => sysdate,
                            p_source_object_type_code => 'SR',
                            p_source_object_id        => v_incident_id,
                            p_source_object_name      => '1000004940',
                            p_date_selected           => 'P',
                            x_return_status           => l_return_status,
                            x_msg_count               => l_msg_count,
                            x_msg_data                => l_msg_data,
                            x_task_id                 => l_task_id);

  if l_return_status <> fnd_api.g_ret_sts_success then
    dbms_output.put_line('Return Status aa= ' || l_return_status);
    dbms_output.put_line('Return Status dd= ' || fnd_api.g_ret_sts_success);
    dbms_output.put_line('l_msg_count aa= ' || l_msg_count);
    if l_msg_count > 0 then
      l_msg_data := null;
      for i in 1 .. l_msg_count loop
        l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get(1, 'F');
        dbms_output.put_line('l_msg_data aa= ' || l_msg_data);
      end loop;
      fnd_message.set_encoded(l_msg_data);
      dbms_output.put_line(l_msg_data);
    end if;
    rollback;
  else
    dbms_output.put_line('Task Id = ' || l_task_id);
    dbms_output.put_line('Return Status = ' || l_return_status);
    commit;
  end if;
end;

On executing the procedure in my system, I get the following output.

SQL> set serveroutput on;
SQL>
SQL> begin
  2  xxx_create_task;
  3  end;
  4  /

Task Id = 157917
Return Status = S

PL/SQL procedure successfully completed

SQL>

Invoking a Web Service from seeded Oracle EBS form through PL/SQL

The scenario : Invoking a Web Service hosted in a SAP PI server from a seeded Oracle E-Business Suite form

The solution : Writing a PL/SQL function which invokes the Web Service and calling the function from the seeded form. The PL/SQL function will use the UTL_HTTP package to invoke the Web Service through a HTTP POST request.

The Steps : Generate the expected SOAP Request and Response XML documents for the Web Service. There are several tools which you can use to get the Request and Response XML documents. I found soapUI to be the best(read easiest) of the lot. All you need to do is to create a new project in soapUI based on the WSDL and generate a MockService. soapUI will generate the Request XML for you which you can then run to get the Response XML.

Next we will create the PL/SQL function which will invoke the Web Service.

create or replace function xxx_sample_ws(p_em_amount number)
  return varchar2 is
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req     utl_http.req;
  http_resp    utl_http.resp;
  resp         XMLType;
  exp_resp     XMLType;
  endpoint_url varchar2(1000);
  username CONSTANT varchar2(10) := 'user_100';
  password CONSTANT varchar2(10) := 'pass_100';
  pname varchar2(100);
  outs  varchar2(10000) := null;

begin

  --The SOAP Request XML document obtained from soapUI
  --the parameters are embedded into the request
  soap_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://rapdrp.com/updateemd/transactional/model/1.0/" xmlns:ns1="http://rapdrp.com/common/bodcomponents/transactional/model/1.0/">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:UpdateEMDReq>
         <ns:ApplicationArea>
            <!--Optional:-->
            <ns1:Sender>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Sender>
            <!--Optional:-->
            <ns1:Receiver>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:CompanyName>?</ns1:CompanyName>
               <!--Optional:-->
               <ns1:CompanyId>?</ns1:CompanyId>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Receiver>
            <!--Optional:-->
            <ns1:Reference>
               <!--Optional:-->
               <ns1:BODCreationDateTime>?</ns1:BODCreationDateTime>
               <!--Optional:-->
               <ns1:BusinessProcessId>?</ns1:BusinessProcessId>
               <!--Optional:-->
               <ns1:MessageId>?</ns1:MessageId>
               <!--Optional:-->
               <ns1:CorrelationId>?</ns1:CorrelationId>
               <!--Zero or more repetitions:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
               <!--Optional:-->
               <ns1:Username>?</ns1:Username>
               <!--Optional:-->
               <ns1:Token>?</ns1:Token>
            </ns1:Reference>
         </ns:ApplicationArea>
         <ns:DataArea>
            <!--Optional:-->
            <ns:ApplicationNo>?</ns:ApplicationNo>
            <!--Optional:-->
            <ns:ApplicationDate>?</ns:ApplicationDate>
            <!--Optional:-->
            <ns:ErnestMoneyAmt>'||p_em_amount||'</ns:ErnestMoneyAmt>
            <!--Optional:-->
            <ns:ApplicantName>?</ns:ApplicantName>
         </ns:DataArea>
      </ns:UpdateEMDReq>
   </soapenv:Body>
</soapenv:Envelope>';

  --the endpoint URL of the Web Service
  endpoint_url := 'http://10.95.244.75:8000/sap/xi/engine?type=entry' || '&' ||
                  'sap-user=user_100' || '&' || 'sap-password=pass_100' || '&' ||
                  'version=3.0' || '&' ||
                  'Sender.Service=CB_SZPDRP_DEV_ORACRM' || '&' ||
                  'Interface=urn%3Awb.szpdrp.oracrm.updateemd%5EUpdateEMD_OB';

  -- if proxy server is involved, use the following for authentication
  -- where 'username' and 'passwd' are the credentials required to access the proxy server
  -- with IP address 'ip_addredd' at port number 'port'
  -- utl_http.set_proxy('http://username:passwd@ip_address:port');

  --begin HTTP request
  http_req := utl_http.begin_request(endpoint_url, 'POST', 'HTTP/1.1');

  --set basic HTTP authentication
  --not required for this case as the endpoint URL contains the username/password
  --utl_http.set_authentication(http_req, username, password);
  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));

  --The SOAP action for the Web Service can also be obtained from soapUI
  utl_http.set_header(http_req,
                      'SOAPAction',
                      'http://sap.com/xi/WebService/soap1.1');
  utl_http.write_text(http_req, soap_request);
  http_resp := utl_http.get_response(http_req);
  utl_http.read_text(http_resp, soap_respond);
  utl_http.end_response(http_resp);
  resp := XMLType.createXML(soap_respond);

  --extract the value of the relevant element from the SOAP Response XML
  --Google 'Oracle XMLType' for more information
  begin
    exp_resp := resp.extract('//ns1:Status/text()',
                             'xmlns:ns1="http://rapdrp.com/updateemd/transactional/model/1.0/"');
    outs     := exp_resp.getStringVal();

  exception
    when others then
      outs := SQLERRM;
  end;

  --return the extracted value or the error details
  return outs;

  --in case of exceptions return the error details
exception
  when others then
    outs := SQLERRM;
    return outs;
end;

You can now call the function from the seeded Form through Form Personalization to invoke the Web Service and display an appropriate message to the user if the invocation was successful.

Nice friendly message for the user!