Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: Integration

Custom Commands in WinSCP

The WinSCP tool provides a couple of handy features which allow users to execute custom commands directly on the server without having to open a separate terminal.
One such feature is the Command Line which you can enable from Options – Command Line. This will display the command line at the bottom of the GUI where you can execute arbitrary commands. The restriction is that you cannot enter any commands which require user-inputs or involve data transfer. You can enter commands like ‘ls -ltr’ to list files and the results will be displayed in a console window which pops up to display the output.
The second feature can be enabled from Options – Toolbars – Customer Command Buttons. Once enabled, a set of pre-existing commands such as Execute, Touch, Grep etc will be displayed. So if , for example, you want to execute a shell script on the remote server, you select the appropriate file and then click on the Execute command. The shell script will get executed. This saves you from the trouble of having to open a new terminal and then typing the command to execute the script.
However, the most interesting thing about this feature is the little gear symbol. Click on this gear symbol and you will realise that you can modify the pre-existing commands and add your own ones. This is especially useful when you have to do some repetitive task such as compiling a form or uploading a workflow in Oracle EBS.

Custom commands overview

You can write you own custom command to compile a form or to upload a workflow file. So the next time you make changes to the form or workflow you can just copy it to the server, select the file on the server and click on your custom command. You do not have to perform the additional step of opening a server session on PuTTY to execute the compile or upload command. The screenshot below shows the details of the XXUploadWF custom command which I use to upload workflow files.

Custom command details

Another useful custom command I have defined is XXCompile_Form. The command for this is


frmcmp_batch module="!"; userid=apps/temp4 output_file=`echo "!"; | awk '{print substr($0,0,index($0, ".fmb")-1)}'`.fmx module_type=form compile_all=special 

It compiles the selected .fmb file(in an EBS R12 environment) and generates an .fmx file of the same name.

More details on writing custom commands for WinSCP is available here.

Use the Oracle Database to Fetch Cricket Scores Through PL/SQL

There I was working at my desk, one fine afternoon, while simultaneously trying to keep track of an ongoing match between India and Sri Lanka. Now, I do not particularly like the desktop widgets some cricket websites offer and opening up a browser window after every 10 minutes was a bit too distracting (both for me as well as my colleagues). So I wrote this little script to fetch the scores from the ESPNcricinfo RSS feed which allows me view the latest scores without having to move away from my PL/SQL editor. The added advantage is that to the untrained eye, it would seem like I was hard at work while I secretly check out the scores! Of course, for all of these the database needs to be able to connect to the Internet.

Many PL/SQL editors allow you to refresh a query automatically at fixed intervals, so you can convert this script into a function and then use it in a query. That way, you do not even have to execute the script every time you want to refresh the scores.

So without much ado, here is the code which is pretty self-explanatory and has lots of room for improvement.  Note that the team whose score you want goes into the v_my_team variable, partial names would also work.

set serveroutput on;
DECLARE
 l_http_request   utl_http.req;
 l_http_response  utl_http.resp;
 l_clob           CLOB;
 l_text           VARCHAR2(32767);
 v_count          NUMBER          := 1;
 v_score          VARCHAR2(100);
 v_score_my_team  VARCHAR2(1000)  := NULL;
 v_my_team        VARCHAR2(100)   := 'India'; --'ALL' --The team whose score you want or 'ALL' for all ongoing matches
BEGIN
 -- Initialize the CLOB.
 dbms_lob.createtemporary(l_clob, FALSE);

 -- Make a HTTP request and get the response.
 -- We are using the RSS feed from Cricinfo
 --l_http_request  := utl_http.begin_request('http://rss.cricinfo.com/rss/livescores.xml'); --RSS feed changed 21-Oct-16
 l_http_request  := utl_http.begin_request('http://static.cricinfo.com/rss/livescores.xml');
 l_http_response := utl_http.get_response(l_http_request);

 -- Copy the response into the CLOB.
 BEGIN
   LOOP
     utl_http.read_text(l_http_response, l_text, 32767);
     dbms_lob.writeappend (l_clob, LENGTH(l_text), l_text);
   END LOOP;
 EXCEPTION
   WHEN utl_http.end_of_body THEN
     utl_http.end_response(l_http_response);
 END;

  -- Convert the CLOB to XMLType and extract scores the from the XML
  WHILE XMLType(l_clob).existsNode('/rss/channel/item[' || v_count || ']') = 1
  LOOP
    v_score := XMLType(l_clob).extract('/rss/channel/item[' || v_count || ']/title/text()').getStringVal();
    --Store all scores, if 'ALL' is specified
    IF UPPER(v_my_team) = 'ALL' THEN
      --If this is the first value, do not use a comma
      IF v_score_my_team IS NULL THEN
        v_score_my_team := v_score;
      ELSE
        v_score_my_team := v_score_my_team||', '||v_score;
      END IF;
    ELSE
      -- If the score is for your team, store it in a variable
      -- Providing a value of 'Australia' to the variable v_my_team would fetch scores involving both Australia and Australia A.
      -- We will display all matching scores for such cases.
      IF UPPER(v_score) LIKE '%'||UPPER(v_my_team)||'%' THEN
        --If this is the first value, do not use a comma
        IF v_score_my_team IS NULL THEN
          v_score_my_team := v_score;
        ELSE
          v_score_my_team := v_score_my_team||chr(10)||chr(13)||v_score;
        END IF;
      END IF;
    END IF;

      v_count := v_count + 1;
  END LOOP;
  -- Display comma-separated scores
  dbms_output.put_line(NVL(v_score_my_team, 'Sorry, there are no ongoing matches involving '||INITCAP(v_my_team)));

EXCEPTION
 WHEN OTHERS THEN
   utl_http.end_response(l_http_response);
   -- Release the resources associated with the temporary LOB.
   dbms_lob.freetemporary(l_clob);
   dbms_output.put_line (utl_http.get_detailed_sqlerrm);
   dbms_output.put_line (SQLERRM);
END;

Notes:

  1. The database Access Control List should allow network access for this to work.
  2. In scores involving two innings, for example 129/10 & 13/2, the ampersand is not displayed properly.
  3. The scores can also be fetched directly through the query below provided the XML file fetched is not more than 2000 bytes. This is because the REQUEST function used in the query returns up to the first 2000 bytes of the data retrieved from the given URL.
SELECT * FROM (
SELECT extractvalue(column_value, '/item/title') score
FROM TABLE(XMLSequence(XMLTYPE(utl_http.request('http://rss.cricinfo.com/rss/livescores.xml')).EXTRACT('/rss/channel/item')))
)

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!