Steps to expose custom PL SQL APIs as a SOA Gateway Webservice

SOA Gateway is an integral part of Ebusiness Suite R12. IT can expose EBS Functionality as SOAP based webservices which includes XML gateway, Business Service Objects, Concurrent Programs, PL/SQL API's, Business Events, E Commerce Gateway, Open Interface Tables/Views etc.

The steps to be followed to expose a  PL/SQL script as SOA Gateway webservice is shown below:

1.      Include the annotations at the package level and procedure/function level to the PL\SQL Spec.

Example:

create or replace <Package_Name> AS

/* $Header: $ */

/*#

* @rep:scope public

* @rep:product <Object Name>

* @rep:lifecycle active

* @rep:displayname <Name of the package>

* @rep:compatibility S

* @rep:category BUSINESS_ENTITY <Object>

*/

PROCEDURE <procedure_name>(

Input1  IN  VARCHAR2,

output1   OUT VARCHAR2,

)

/*#

* @param Input1 <description of input>

* @param output1 Return <output1 details>

* @rep:displayname <procedure_name>

*/;

END <procedure_name>;

/

SHOW ERRORS

EXIT

2. Move the PL\SQL Package(.pls files) to the Unix Machine using winscp or putty.
3. Ensure to have irep_parser.pl to be present in Unix Machine.
4. Execute the following command to create an iLdt file for package

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=<admin username> <Object name>:patch/115/sql:<Package Name>.pls:12.0=<pls file location>/< Package Name.pls>

Where

Object name is the name of the object where the services will be registered.

            patch/115/sql: stands for the path
             <package_name>.pls: file name 

            It is not required to physically place the file in that path.

<pls file location>/< Package Name.pls> - Physical file path

5.      Execute the following command to upload the iLdt file to Intergrated SOA Gateway Responsibility:

$FND_TOP/bin/FNDLOAD <UNAME>/<PWD> 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct <pls file name>.ildt CUSTOM_MODE=FORCE

6.                 To generate and deploy the service in multi node environment please login to each node and execute the following commands (with admin privileges)

$FND_TOP/bin/soagenerate.sh irepname=<internal name or package name>

7.      Check the ServiceGenerator.log file for service generated successfully or not. (with admin priveleges)

$FND_TOP/bin/soadeploy.sh irepname=<internal name or package name>

8.      Check the ServiceDeploy.log file for service is deployed successfully or not.

9.      In R12 front End Application select Intergrated SOA  Gateway Responsibility, search for the object where this API was deployed.

10.  Select the Deployed Service and open the details of the same. Now give the grant for the Procedure listed. Select the checkbox for the procedure listed and select “Create Grant”. In the next screen we can give the individual grant access or to all the users.

11.  Click on view WSDL and check if the WSDL is accessible.