Creating webservices in PL/SQL for SOA Gateway eBS R12

Creating webservices in PL/SQL for SOA Gateway eBS R12

To create a webservice in eBS R12 you can use the Integrated SOA Gateway. To do this you have to follow the following steps

Step 1: Create database package with annotation

First create a database package with extension pls (!) and the following annotation


CREATE OR REPLACE PACKAGE XXXORAWS AS
/* $Header: XXXORAWS $ */
/*#
* Read Oracle User information webservice
* @rep:scope public
* @rep:product FND
* @rep:displayname General Oracle Webservices for Service Integration
* @rep:category BUSINESS_ENTITY ORACLEWS
*/

Step 2: Create database package procedure with annotation

Now create a procedure for example to create orders through a webservice. In this example we have an order header and lines in one field (XML input). Note that you can pass tables and record constructs as well,

PROCEDURE W_Order (
    OrderHeaderRec   IN T_OrderHeaderRec ,
    OrderLinesTbl    IN T_OrderLineTbl,

but if you call this from other applications they may not always support that!

/*#
* This procedure is for creating the order web service
* @param Account_Name          Account_Name
* @param Tax_Reference_Number  Tax_Reference_Number
* @param Ship_Account_Number   Ship_Account_Number
* @param Ship_Address      Ship_Address
* @param Ship_PC    Ship_PC
* @param Ship_City       Ship_City
* @param Ship_State       Ship_State
* @param Ship_Country_Code     Ship_Country_Code
* @param Bill_Account_Number Bill_Account_Number
* @param Bill_Address      Bill_Address
* @param Bill_PC    Bill_PC
* @param Bill_City       Bill_City
* @param Bill_State       Bill_State
* @param Bill_Country_Code     Bill_Country_Code
* @param Order_Number          Order_Number
* @param ordered_date          ordered_date
* @param Order_Amount     Order_Amount
* @param Currency              Currency
* @param ORDER_TYPE_CODE       ORDER_TYPE_CODE
* @param Cr_Card_Pay_Status    Cr_Card_Pay_Status
* @param Cr_Card_Acceptance    Cr_Card_Acceptance
* @param Email_Address         Email_Address
* @param Tax_Perc     Tax_Perc
* @param Tax_Amount     Tax_Amount
* @param Discount_codes    Discount_codes
* @param Po_Number             Po_Number
* @param OrderLines Orderlines
* @param x_output XML Return Message
* @rep:displayname Create webshop/TRP order
* @rep:scope public
* @rep:lifecycle active
*/
 Procedure W_OrderWS (
  Account_Name          IN VARCHAR2
, Tax_Reference_Number  IN VARCHAR2
, Ship_Account_Number   IN VARCHAR2
, Ship_Address      IN VARCHAR2
, Ship_PC    IN VARCHAR2
, Ship_City       IN VARCHAR2
, Ship_State   IN VARCHAR2
, Ship_Country_Code     IN VARCHAR2
, Bill_Account_Number IN VARCHAR2
, Bill_Address      IN VARCHAR2
, Bill_PC    IN VARCHAR2
, Bill_City       IN VARCHAR2
, Bill_State   IN VARCHAR2
, Bill_Country_Code     IN VARCHAR2
, Order_Number          IN VARCHAR2
, ordered_date          IN DATE
, Order_Amount      IN NUMBER
, Currency              IN VARCHAR2
, ORDER_TYPE_CODE       IN VARCHAR2
, Cr_Card_Pay_Status    IN VARCHAR2
, Cr_Card_Acceptance    IN VARCHAR2
, Email_Address         IN VARCHAR2
, Tax_Perc       IN NUMBER
, Tax_Amount   IN NUMBER
, Discount_codes  IN VARCHAR2
, PO_Number             IN VARCHAR2
,  OrderLines    IN CLOB
 ,x_output              OUT VARCHAR2);
 

Step 3: Create your database logic

Now your procedure can do anything with the given input data ofcourse. In our case it returns the status using something like this

 TYPE
   T_XML_Outputs IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

 
 G_XML_OUTPUT   T_XML_Outputs;
 G_XML_OUTPUT_ID NUMBER := 1;
 G_MAX_LENGTH   NUMBER := 32000;
 G_XML_ERROR    VARCHAR2(1000);
 G_XML_LENGTH   NUMBER := 0;
 G_ERROR_PNT    VARCHAR2(240);

...

 XXXORAWS.Output_To_Webservice (XXORACLEWS.xml_header
         || Chr(10) || '  <status>' || l_status || '</status>'
         || Chr(10) || '  <errorcode>' || l_error_code || '</errorcode>'
         || Chr(10) || '  <msg>' || l_msg || '</msg>'
         || Chr(10) || '  <debug_msg>' || l_debug_msg || '</debug_msg>'
                                    || Chr(10) || '  <DEBUG_MESSAGES>' || g_debug_messages || '</DEBUG_MESSAGES>'    
         || Chr(10) || '  <operation>W_OrderWS</operation>'
         || Chr(10) || '   </oracle>');

And the output to webservice function is something like this .. it depends on what you want to output if this is a large text or not. Most write webservices output only the status, but we also creat read services that display a listof something and that output can be quite long.

PROCEDURE Output_To_Webservice(
    p_add_xml VARCHAR2 )
IS
BEGIN
  -- We split the output in pieces. In most cases we only need one piece.
  XXXORAWS.G_XML_LENGTH                                                                            := XXXORAWS.G_XML_LENGTH                       + NVL (LENGTH (p_add_xml),0);
  IF NVL (LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID)),0) + NVL (LENGTH (p_add_xml),0) < XXXORAWS.G_MAX_Length
    --    IF XXXORAWS.G_XML_LENGTH < XXXORAWS.G_MAX_LENGTH * XXXORAWS.G_XML_OUTPUT_ID
    THEN
    NULL;
  ELSE
    XXXORAWS.G_XML_OUTPUT_ID                := XXXORAWS.G_XML_OUTPUT_ID + 1;
    XXXORAWS.G_XML_OUTPUT (G_XML_OUTPUT_ID) := NULL; -- Init
  END IF;
  XXXORAWS.G_ERROR_PNT                             := 'Add XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
  XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) := XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) || p_add_xml;
  XXXORAWS.G_ERROR_PNT                             := 'Added XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
 END Output_To_Webservice;
An example is a service that displays all countries in Oracle


------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
  Procedure R_CountryWS
  IS
    CURSOR C_Countries
    IS
    SELECT t.territory_code
    , Initcap (t.nls_territory) nls_territory
    , T.Territory_Short_Name
    , T.Description
    FROM   FND_TERRITORIES_VL T
    WHERE T.Obsolete_Flag = 'N'
    ORDER BY t.territory_short_name
    ;
  BEGIN
    l_status := Fnd_Api.g_ret_sts_success;
    l_msg    := null;
       XXXORAWS.Output_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
                '  <countries>' || Chr(10));

    FOR C IN C_Countries
      LOOP
   XXXORAWS.Output_To_Webservice ('    <country>' ||  Chr(10) ||
          '      <code>' || C.Territory_Code || '</code>' || Chr(10) ||
          '      <nlscode>' || C.NLS_Territory || '</nlscode>' || Chr(10) ||
          '      <name>' || C.Territory_Short_Name || '</name>' || Chr(10) ||
          '      <description>' || C.Description || '</description>' || Chr(10) ||
          '    </country>' || Chr(10));
      END LOOP; -- Countries

   XXXORAWS.Output_To_Webservice ('  </countries>' || Chr(10) || xml_footer);
    exception
        when others then
           l_status := fnd_api.g_ret_sts_error;
           l_msg    := sqlerrm;
           l_error_code := G_COUNTRY_ERROR;
           XXXORAWS.Error_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
              xml_footer);
  END; -- R_CountryWS

And this would be exposed in our package header like this. Note that you don't have to add the annotation in the package body.

  ------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
/*#
* This procedure returns a list of countries
* @param x_output XML Return Message
* @rep:displayname Oracle Country List
* @rep:scope public
* @rep:lifecycle active
*/  
  Procedure R_CountryWS (x_output OUT CLOB)
    ;

Step 4 : Install the service

I usually create a driver script where I prompt the user to enter the version of the webservice. If left empty we do not re-install the service.

rm -f L*.log
if [ "${WEB_VERSION}" ]
then
echo "Loading webservice package" >> $LOGFILE
echo "Version is "$WEB_VERSION >> $LOGFILE
#cp XXXORAWS.pls $INSTALLATION_DIR/install/sql
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=SYSADMIN XXX:install/sql:XXXORAWS.pls:$WEB_VERSION=/$TWO_TASK/apps/apps_st/appl/xxx/12.0.0/install/sql/XXXORAWS.pls
$FND_TOP/bin/FNDLOAD $APPS_USER/$APPS_PASS 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XXXORAWS_pls.ildt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
for i in `ls -dF L*.log`
  do
   cat $i >> $LOGFILE
  done
else
  echo "Not reinstalling webservices." >> $LOGFILE
fi

Step 5 : Generate and deploy the webservice

Login as Integrated SOA Gateway responsibility as SYSADMIN and navigate to the Integration Repository. Perform a search using the button on the right. Choose Advanced Options and look for interface type Custom. This should show our webservice.


Open the service and click [Generate WSDL]. If you do not see this button you are not logged in as SYSADMIN.

Now choose Deploy button on the left.If you have added a new function it should be granted to the user you use for accessing the webservices (usually one specific EBS user with no responsibilities). before you can use it. Click on the function you want to change and grant the user.

  

 

 Step 6: Test webservice

Use  View WSDL to see the wsdl and remove ?wsdl. Now you get to the test page.
Here you can test the service. It's good practice to create one generic service that actually does nothing except for return the name of the database so you always know you are pointing at the right environment and that the service is working.

You have to pass the username/password and responsibility key. You can see these up in EBS with a responsibility without any menu items so the user can't login.

Choose invoke at the bottom and see the output in P_OUTPUT to see the result.