How to create a custom integration interface in SOA Gateway

14 August 2011

This example will illustrate on how to develop a simple interface using PL/SQL. The Integration Repository will recognize integrations through annotations in the code (details about annotations can be found in the Integrated SOA Gateway Developer’s Guide, Appendix A).

You can annotate *.pls and *.pkh files.
For PL/SQL packages, only the package spec should be annotated. Do not annotate the body.

Example of a annotated package spec:

CREATE OR REPLACE PACKAGE xx_test_soagway AS
/* $Header: $ */
/*#
* This package returns different data from Financials (GL).
* @rep:scope public
* @rep:product gl
* @rep:displayname xx_test_soagway
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY GL_ACCOUNT_COMBINATION
*/

/*#
* Returns CCID
* @param P_SEGMENT1 varchar2 Segment 1
* @param P_SEGMENT2 varchar2 Segment 2
* @param P_SEGMENT3 varchar2 Segment 3
* @param P_SEGMENT4 varchar2 Segment 4
* @param P_SEGMENT5 varchar2 Segment 5
* @return CCID
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Return CCID
*/
FUNCTION get_ccid (P_SEGMENT1 IN VARCHAR2,
                   P_SEGMENT2 IN VARCHAR2,
                   P_SEGMENT3 IN VARCHAR2,
                   P_SEGMENT4 IN VARCHAR2,
                   P_SEGMENT5 IN VARCHAR2) RETURN NUMBER;

END xx_test_soagway;

Note: The annotation BUSINESS_ENTITY is where in the Integration Repository your custom integration will be found. The below queries will help you find the correct BUSINESS_ENTITY. It’s not possible to add your own BUSINESS_ENTITYs in the current version.

SELECT * FROM fnd_lookup_assignments WHERE lookup_type = 'BUSINESS_ENTITY'
SELECT * FROM fnd_lookups WHERE lookup_type = 'BUSINESS_ENTITY'

In the example above a package called xx_test_soagway is created with one function called get_ccid. The function will return a code combination id for the segment parameters that we are using in the request. The package body is saved on the local hard drive as xx_test_soagway.pls.

The next step is to create an iLDT file (Integration Repository loader file) that we should use to upload to the Integration Repository. The file is created with a tool called Integration Repository Parser (IREP Parser), the tool will validate the file against the annotation standards. Before the iLDT file can be created xx_test_soagway.pls need to be transferred to the server (as applmgr). In this case we will put the file in $GL_TOP/patch/115/sql and /tmp.

Run the command to generate the iLDT file:
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_test_soagway.pls:12.0=/tmp/xx_test_soagway.pls

Note: If you are generating a new iLDT file for an already uploaded interface you need to add a higher version number then the last uploaded.
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_test_soagway.pls:12.1=/tmp/xx_test_soagway.pls

When the iLDT file is successfully created we can upload it to the Integration Repository using:
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xx_test_soagway_pls.ildt

Successfully uploaded to the Integration Repository

Now the package needs to be created in the database

CREATE OR REPLACE PACKAGE xx_test_soagway AS
/* $Header: $ */
/*#
* This package returns different data from Financials (GL).
* @rep:scope public
* @rep:product gl
* @rep:displayname xx_test_soagway
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY GL_ACCOUNT_COMBINATION
*/

/*#
* Returns CCID
* @param P_SEGMENT1 varchar2 Segment 1
* @param P_SEGMENT2 varchar2 Segment 2
* @param P_SEGMENT3 varchar2 Segment 3
* @param P_SEGMENT4 varchar2 Segment 4
* @param P_SEGMENT5 varchar2 Segment 5
* @return CCID
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Return CCID
*/
FUNCTION get_ccid (P_SEGMENT1 IN VARCHAR2,
                   P_SEGMENT2 IN VARCHAR2,
                   P_SEGMENT3 IN VARCHAR2,
                   P_SEGMENT4 IN VARCHAR2,
                   P_SEGMENT5 IN VARCHAR2) RETURN NUMBER;

END xx_test_soagway;

create or replace
PACKAGE BODY xx_test_soagway AS

  FUNCTION get_ccid (P_SEGMENT1 IN VARCHAR2,
                     P_SEGMENT2 IN VARCHAR2,
                     P_SEGMENT3 IN VARCHAR2,
                     P_SEGMENT4 IN VARCHAR2,
                     P_SEGMENT5 IN VARCHAR2) RETURN NUMBER AS

  v_ccid    NUMBER;

  BEGIN

    SELECT code_combination_id INTO v_ccid
    FROM gl_code_combinations
    WHERE NVL(segment1, ' ') = NVL(P_SEGMENT1, ' ')
    AND   NVL(segment2, ' ') = NVL(P_SEGMENT2, ' ')
    AND   NVL(segment3, ' ') = NVL(P_SEGMENT3, ' ')
    AND   NVL(segment4, ' ') = NVL(P_SEGMENT4, ' ')
    AND   NVL(segment5, ' ') = NVL(P_SEGMENT5, ' ')
    AND   enabled_flag = 'Y';

    RETURN v_ccid;

  EXCEPTION
    WHEN no_data_found THEN
      RETURN -99;
    WHEN too_many_rows THEN
      RETURN -90;
  END get_ccid;
END xx_test_soagway;

When the code have been successfully compiled we need to service enable the new integration interface. Go back to the xx_test_soagway PL/SQL interface and click on the button Generate WSDL.

After the generation of the WSDL a link will appear in the interface page. The next step is to deploy the web service and is simply done by clicking the Deploy button.

When the web service is deployed it is possible to Redploy and Undeploy the service.

The last thing before we can invoke the web service is to set the Grant, this is done by clicking the Create Grant button.

In this example set the Grantee Type to All users.

Before the web service can be invoked we need to restart the Application server and clear the cache.

Restart

cd $INST_TOP/admin/scripts
adoafmctl.sh stop
adoacorectl.sh stop

adoafmctl.sh start
adoacorectl.sh start

Clear cache
Use responsibility Functional Administrator and go to Core Services -> Caching Framework -> Global Configuration -> Clear all cache.

Now the web service is deployed and ready to be invoked.

By using the tool soapUI it is easy to unit test the newly created service (download from www.soapui.org).
Open soapUI and create a new project.

Paste the WSDL link from the E-Business Suite Integration Repository and select some options in the SOAP UI new project dialog.

Add username and password (enduser in E-Business Suite, encrypted password not supported at the moment) and add parameters for segment 1-5. Then click the submit request (top left corner in Request 1 window) to invoke the web service. The response will be shown in the right side of the window.

Sources

Integrated SOA Gateway Developer’s Guide (February 2010)
Oracle E-Business Suite R12.1 – Integrated SOA Gateway Provides Native, Out-Of-The-Box SOAP Based Web Services
Integration Repository Customization
Oracle E-Business Suite – Integration

68 comments

Archived from the original blog on Blogger. New comments are closed for now.

Arnab8 Feb 2012
Excellent article. Never saw a more well documented blog on a piece of functionality that is new.
1. Daniel BorgströmAuthor13 Mar 2012
  Thank you :)
2. Prabhakar18 May 2012
  I totally agree with Arnab. Very nicely written blog.. It was very useful for me when writing my custom SOA Gateway interface.
  
  Thanks to you Daniel.
  
  Regards,
  Prabhu
3. mnabil23 Jul 2013
  I also agree. Very informative.
4. mpkirankumar1 Sept 2016
  Hello Daniel
  
  Can you provide us sample code for out variable in payload.
Anonymous25 May 2012
Fantastic. Other blogs our there seem to regurgitate the manual. This is a godsend.
Anonymous13 Jul 2012
A good article, but I bumped into a issue in SoapUI in the reponse tab.
I followed your example and encountered the following error:

wsse:InvalidSecurity
Missing <wsse:Security> in SOAP Header

I made sure that username and password are entered correctly.

Any suggestions to solve this?

Regards,
Ram
1. Daniel BorgströmAuthor17 Jul 2012
  Hi Ram,
  
  I would start looking in the request properties but I have not come across this problem. I did a quick googling and got quite a few results, maybe you can start there.
  
  Good luck!
  
  Regards,
  Daniel
Anonymous18 Jul 2012
Hi Daniel,

thanks for your reply and suggestion.

Regards,
Ram
Anji10 Aug 2012
Hi,

This is the best explanation I have ever seen on SOA gateway start.
I am not getting any output (procedure not executing) if I don't pass SOA Header (resp, resp application & Security Group).

Any idea why it is behaving like this. I see those are optional and we are not planning to assign any responsibilities to user accounts.

Have a good day.

Thanks
Anji
1. Daniel BorgströmAuthor8 Sept 2012
  Sorry, haven't seen that error.
daxesh7 Sept 2012
I am getting below error while invoking web service
java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: For input string: ""
1. Daniel BorgströmAuthor8 Sept 2012
  Are you getting this error in SOAP UI? The only thing I can think of is that you are not permitted to communicate between the client and server.
2. daxesh.nirma@gmail.com14 Sept 2012
  Hi Danial,
  Even if i am calling that web service form Oracle database, SOAP UI or From Seeded oracle apps server i ma getting same error java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: For input string: "".
  
  Regards
  Dakshesh patel
Anonymous12 Sept 2012
Hi Daniel,

Thank you for the detailed article. I am experimenting trying to add my custom PL/SQL package to iRep in a 12.1.3 Vision instance and cannot get the irep_parser to parse my annotated package spec. The main difference is that I have multiple functions and procedures within the package that are to be published to iRep.

The parser fails as soon as it hits the second procedure definition:

# Interface Repository Annotation Processor, 12.0.0

#
# Generating annotation output.
# Processing file '/tmp/xxmcsrpubs.pls'.
# Using YAPP-based parser.
# Found a package-level annotation for 'XXMC_SERVICE_REQUEST_PUB'.
# Found a detail-level annotation...
# Found a procedure named 'CREATE_SR'.
/tmp/xxmcsrpubs.pls:0:/tmp/xxmcsrpubs.pls:56:Unrecognized SQL*Plus command 'PROCEDURE'.

# Warning: Not generating iLDT for '/tmp/xxmcsrpubs.pls', fatal error during parse.
# Done all files.

MOS and the documentation have not shed any light on what might be wrong with the annotation. Have you successfully parsed a package with multiple functions/procedures?

Thanks,

Jeff
1. Anonymous12 Sept 2012
  Quick update...
  
  I removed all but one of the procedures from the spec to simplify the test and now the irep_parser fails when it hits the END statement. This is a valid package spec.
  
  SET VERIFY OFF
  WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
  WHENEVER OSERROR EXIT FAILURE ROLLBACK;
  
  CREATE OR REPLACE
  PACKAGE XXMC_SERVICE_REQUEST_PUB AUTHID CURRENT_USER AS
  /* $Header: xxmcsrpubs.pls $ */
  /*#
  * You can use this custom public interface to create and update a service request
  * specfic to the GCSS-MC Rapid Request process.
  * This interface applies to all service request business rules.
  *
  * @rep:scope public
  * @rep:product XXMC
  * @rep:displayname GCSS-MC Service Request Processing
  * @rep:category BUSINESS_ENTITY CS_SERVICE_REQUEST
  */
  /*#
  * Creates a new "Rapid Request" service request and related information such as service request notes.
  *
  * @param p_correlation_id varchar2 attribute11
  * @param p_system_id varchar2 attribute12
  * @param p_uic varchar2 cust_account_number
  * @param p_und varchar2 urgency_of_need
  * @param p_reported_date varchar2 reported_date
  * @param p_summary varchar2 problem_summary
  * @param p_reqd_delivery_date varchar2 required_delivery_date
  * @param p_req_supply_tbl varchar2 xxmc_sr_req_supply_tab
  * @param p_req_service_tbl varchar2 xxmc_sr_req_service_tab
  * @param p_notes varchar2 notes
  * @param x_return_code number return_code
  * @param x_return_status varchar2 return_status
  * @rep:category BUSINESS_ENTITY CS_SERVICE_REQUEST
  * @rep:scope public
  * @rep:lifecycle active
  * @rep:displayname Create GCSS-MC Service Request
  */
  PROCEDURE CREATE_SR (p_correlation_id IN VARCHAR2,
  p_system_id IN VARCHAR2,
  p_uic IN VARCHAR2,
  p_und IN VARCHAR2 DEFAULT 'C',
  p_reported_date IN VARCHAR2 DEFAULT NULL,
  p_summary IN VARCHAR2,
  p_reqd_delivery_date IN VARCHAR2 DEFAULT NULL,
  p_req_supply_tbl IN XXMC_SR_REQ_SUPPLY_TAB DEFAULT NULL,
  p_req_service_tbl IN XXMC_SR_REQ_SERVICE_TAB DEFAULT NULL,
  p_notes IN VARCHAR2 DEFAULT NULL,
  x_return_code OUT NOCOPY NUMBER,
  x_return_status OUT NOCOPY VARCHAR2);
  
  END XXMC_SERVICE_REQUEST_PUB;
  /
  COMMIT;
  EXIT;
  
  Any thoughts?
2. Anonymous13 Sept 2012
  I found my issue. The parser got hung up because I split "CREATE OR REPLACE" and "PACKAGE" across two lines. Everything parsed successfully after I removed the CR/LF.
Anonymous1 Mar 2013
Hi Daniel,

Thanks for the nice article! But I have a question and hope you have an answer.

I have a requirement in which we need to return a XML message which contains the result of a SQL query. My question is how can we implement this as we can only find examples in which only a single number or varchar is returned with the name of the package function as a XML tag.

Can you think of a solution?

Regards,

Charif
1. Daniel BorgströmAuthor1 Mar 2013
  Try to set your out parameter to XML Type or CBLOB/CLOB.
  
  Regards,
  Daniel
2. Anonymous1 Mar 2013
  Good one! Thanks for the help Daniel.
  
  Greetings,
  
  Charif
Anonymous6 Mar 2013
Hi Daniel,

The CLOB return works! I now only have the issue that the xml result starts with the <![CDATA[ tag.

Did you encounter this before? Do you have a solution for this?

Kind regards,

Charif
Anonymous3 Apr 2013
Very well written Daniel
Anonymous10 Apr 2013
Hi Daniel,

Very good example.but we need to return result of SQL query? How to set out parameter to XML Type or CBLOB/CLOB?
1. Daniel BorgströmAuthor10 Apr 2013
  Please see one my other posts on how set the output parameter to xml type.
RK26 May 2013
Hi Daniel,
When validating the annotation file, the below command is used

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin cocar:/patch/115/sql:ABC_A:12.0=path of ABC_A where ABC_A is the annotation file

But in our unix box, there is no 'perl' folder within bin. Is it because i don't have access to that folder or anything else?

When i execute the above command, i am getting error as" can't locate strict.pm "

Any Ideas?
1. Daniel BorgströmAuthor26 May 2013
  Sorry RK, have not seen this one. But I would start to investigate the variables $ IAS_ORACLE_HOME and $ FND_TOP. Make sure that they are working and pointing correctly.
2. RK27 May 2013
  Hi Daniel,
  Appreciate your time. Actually,i was able to see irep_parser.pl in $FND_TOP/bin. But when i try to traverse to $IAS_ORACLE_HOME/perl/bin/perl, I could go only till $IAS_ORACLE_HOME/perl/bin. There is no 'perl' folder in 'bin'. I was just wondering if it was because of missing patch. Can you see 'perl' folder inside 'bin' in your system?
  
  2. Also, while creating the annotation file, should the product name given(gl in your article) be same as the product name given in
  $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_test_soagway.pls:12.0=/tmp/xx_test_soagway.pls?
  I am building a custom integration interface in Account Receivables module. So , would it be AR in both the cases?
3. Daniel BorgströmAuthor27 May 2013
  About the missing perl folder google '"perl folder" oracle r12' and you will find some info. I have not seen this issue before and I have no access to any R12 systems at the moment.
  
  I think the gl part is referring to the $GL_TOP folder. If you are doing AR you should but the file in $AR_TOP/patch/115/sql and /tmp (see instructions in the beginning of the blog post).
Sujatha4 Jul 2013
Hi Daniel,

I am trying to generate iLDT file but getting error.

Please see the package specification. there is no business event in my case.

CREATE OR REPLACE PACKAGE XXMTOINT84_lookup AS
/* $Header: $ */
/*#
* This package is used to give lookuo values to Seiebel
* @rep:scope public
* @rep:product AR
* @rep:lifecycle active
* @rep:displayname XXMTOINT84_lookup
* @rep:compatibility S
*/
/**
* This package used to give ookup values to INT84
* @param p_party_id
* @param px_lob
* @param x_transaction_Number
* @param x_transaction_Type
* @param x_transaction_Balance
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname xx_text_proc
*/
Procedure xx_text_proc(P_Party_Id In Varchar2,
Px_Lob In Out Nocopy Varchar2,
x_transaction_Number Out Nocopy Number,
x_transaction_Type Out Nocopy Varchar2,
x_transaction_Balance Out Nocopy Number);
END XXMTOINT84_lookup;
/

*************************************Successfully******************************************
Moved this file into server on below path:
/u01/r12/apps/DEV10/apps/apps_st/appl/ar/12.0.0/patch/115/sql/XXMTOINT84.pls
Hee there is no tmp folder so I created temp folder and moved it to below folder
/u01/r12/apps/DEV10/apps/apps_st/appl/ar/12.0.0/patch/115/tmp/XXMTOINT84.pls

***********************Completed*********************************************
While creating ILDT file in my environment thee is no path setup for $IAS_ORACLE_HOME/ and $FND_TOP.

While searching I got below paths for perl location and irep_parser.pl location.
So I used below command for creating iLDT file.

/u01/r12/apps/DEV10/apps/tech_st/10.1.2/perl/bin/perl /u01/r12/apps/DEV10/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl -g -v -username=sysadmin ar:patch/115/sql:XXMTOINT84.pls:12.0=/tmp/XXMTOINT84.pls

But I am facing following error:

Can't locate strict.pm in @INC (@INC contains: /project/ias904/src/build904/perl561/perl/bin/AIX/lib/5.6.1/aix /project/ias904/src/build904/perl561/perl/bin/A IX/lib/5.6.1 /project/ias904/src/build904/perl561/perl/bin/AIX/lib/site_perl/5.6.1/aix /project/ias904/src/build904/perl561/perl/bin/AIX/lib/site_perl/5.6.1 / project/ias904/src/build904/perl561/perl/bin/AIX/lib/site_perl .) at /u01/r12/apps/DEV10/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 13.
BEGIN failed--compilation aborted at /u01/r12/apps/DEV10/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 13.

Can you please advise me why this is happening.

Thanks,
Sujatha
1. Daniel BorgströmAuthor5 Jul 2013
  You are saying that you have no paths to $IAS_ORACLE_HOME and $FND_TOP?
  
  I would start to look into why you have no paths assigned to the env. variables mentioned above.
2. RK5 Jul 2013
  Hi Sujatha,
  Strict is a perl module which the command is not able to locate. This is a generic error. The possible reason is because the paths are not set. You need to login as a user (like appldev or similar) which has the paths set and then run this command. Also, I believe that you need not physically place the file in /u01/r12/apps/DEV10/apps/apps_st/appl/ar/12.0.0/patch/115/sql/XXMTOINT84.pls
3. Anonymous26 Apr 2021
  Hello,
  I am having the same error, how did you solve it please?
Anonymous18 Jul 2013
I encountered this error while creating ilDT file command :

Can't locate Class/MethodMaker.pm in @INC (@INC contains: /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/perl /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/xml/orc115 /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/bin/ /media/disk/oracle/VIS/apps/tech_st/10.1.3/perl/lib/5.8.3/i686-linux-thread-multi /media/disk/oracle/VIS/apps/tech_st/10.1.3/perl/lib/5.8.3 /media/disk/oracle/VIS/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3/i686-linux-thread-multi /media/disk/oracle/VIS/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3 /media/disk/oracle/VIS/apps/apps_st/appl/au/12.0.0/perl /media/disk/oracle/VIS/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl .) at /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
BEGIN failed--compilation aborted at /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
Compilation failed in require at /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.
BEGIN failed--compilation aborted at /media/disk/oracle/VIS/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.

how do I solve that problem ? and there are lots of directory such gl/patch/115/sql, am I suppose to copy .pls file to all ?
1. Daniel BorgströmAuthor18 Jul 2013
  Sorry, I have not seen this error but I would investigate the Soa Gateway installation and yhe environment variables.
2. Anonymous18 Jul 2013
  I am new for all about these stuffs. what do you mean yhe enviroment vars. ?
  sorry for wasting ur time :/
3. Anonymous18 Jul 2013
  I have copied mine(.pls file) to /tmp but do not know which directory provide the exact location patch/115/sql. I want to use ONT not GL.
  
  thanks already
4. RK18 Jul 2013
  Looks like something is missing in SOA gateway installation. Make sure that the required patches are in your system. Environment variables are the ones like IAS_ORACLE_HOME ,FND_TOP etc. If you do cd $IAS_ORACLE_HOME and then execute pwd, you should see the oracle home directory. For ONT it will be ont:/patch/115/sql.
5. Anonymous19 Jul 2013
  enviroment variables are OK. And finally, I put my .pls file into $ONT_TOP/patch/115/sql.
  still same error comes up. I am gonna check my required patches.
6. mandy25 Sept 2013
  Hi Daniel,
  
  I have created webservice as per given steps . I can see this webservice as generated and deployed from "SOA integration repository" responsibility, but when I click on "View WSDL" link. it gives me {HTTP:404 error --The webpage cannot be found.} error, Any idea on this issue? Please share .
7. Daniel BorgströmAuthor27 Sept 2013
  Hm, not easy to say why but I would investigate the URL that is not accessable. Does it look as you expect? Are you using load balancers? Is there any firewalls? Seems like some network problem but I don't have any clear answer. Maybe restart of apps.server will help...
RK26 Sept 2013
Hi Daniel,
How do i delete a custom service from the integration repository
Daniel BorgströmAuthor27 Sept 2013
Sorry, I have no solution for that but as it's supported to create new entries it should also be possible to delete. Unfortuanlly I don't have access to a R12 env as I'm working with other things now. I found https://forums.oracle.com/thread/1047447 but it's an old entry. Try conntacting Oracle directly if you don't find anything.
Anonymous30 Oct 2013
Hi Daniel,
Am new to EBS and I have some java program like addition, division etc...Now i want to upload all the programs in EBS Integrated Repository. I don't know how to upload my files. Can u explain how i do
1. Daniel BorgströmAuthor30 Oct 2013
  Sorry, I don't understand what you mean. Can you clarify?
Anonymous31 Oct 2013
How to develop a simple interface using JAVA instead of PL/SQL. Is it possible? If it so please let me know the steps to creating JAVA Customs APIs in EBS . I have read the article Oracle E-Business Suite Integrated SOA Gateway Developer's Guide. But i couldn't understand and they were not mentioned how to load java files in EBS. So please create your own steps to follow. Thanks for your understanding.

Regards
Govindharaju G
1. Daniel BorgströmAuthor31 Oct 2013
  I have not created any other interfaces that them based on PL/SQL. To be honest I have never had the need, all I wanted to do was esaly done with PL/SQL. In most cases it'as about Webservice enable something in the database, custom or standard EBS things.
  
  In the Dev.Guide there is nothing about this so I guess it cannot be done. But you can load your Java Classes into the database (Java Stored Procedure) and then use those to expose as Webservices. See http://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm. You basically load the Java to the database and then create a PL/SQL wrapper which access the Java.
  
  I have worked with Java Stored Procedures and it works great but I have not used this approach to expose them as a Webservice through ISG. But, I don't see why it should not work.
  
  Hope that helps.
2. Anonymous5 Nov 2013
  Hi Daniel,
  Thanks for your comments
  
  Regards
  Govindharaju G
3. Anonymous8 Nov 2013
  Hi Daniel,
  when i tried this below command i got error in putty console
  Command :
  $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_test_soagway.pls:12.0=/home/applvis/xx_test_soagway.pls
  
  Error
  
  Can't locate Class/MethodMaker.pm in @INC (@INC contains: /Software/vision/applv is/apps/apps/apps_st/appl/fnd/12.0.0/perl /Software/vision/applvis/apps/apps/app s_st/appl/fnd/12.0.0/xml/orc115 /Software/vision/applvis/apps/apps/apps_st/appl/ fnd/12.0.0/perl/FND/irep/repo /Software/vision/applvis/apps/apps/apps_st/appl/fn d/12.0.0/bin/ /Software/vision/applvis/apps/apps/tech_st/10.1.3/perl/lib/5.8.3/i 686-linux-thread-multi /Software/vision/applvis/apps/apps/tech_st/10.1.3/perl/li b/5.8.3 /Software/vision/applvis/apps/apps/tech_st/10.1.3/perl/lib/site_perl/5.8 .3/i686-linux-thread-multi /Software/vision/applvis/apps/apps/tech_st/10.1.3/per l/lib/site_perl/5.8.3 /Software/vision/applvis/apps/apps/apps_st/appl/au/12.0.0/ perl /Software/vision/applvis/apps/apps/tech_st/10.1.3/Apache/Apache/mod_perl/li b/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/b in/Linux/Opt/lib/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/per l58/bin/Linux/Opt/lib/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/l ib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/ bin/Linux/Opt/lib/site_perl/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux /Opt/lib/site_perl .) at /Software/vision/applvis/apps/apps/apps_st/appl/fnd/12. 0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
  BEGIN failed--compilation aborted at /Software/vision/applvis/apps/apps/apps_st/ appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
  Compilation failed in require at /Software/vision/applvis/apps/apps/apps_st/appl /fnd/12.0.0/bin/irep_parser.pl line 37.
  BEGIN failed--compilation aborted at /Software/vision/applvis/apps/apps/apps_st/ appl/fnd/12.0.0/bin/irep_parser.pl line 37.
  
  Can u upload video for develop a simple interface using PL/SQL ?
  
  Thanks
  Govindh
4. Daniel BorgströmAuthor8 Nov 2013
  Sorry, I don't have access to any R12 environment at the moment as I for the past years have been working more with SOA.
  
  But, check this:
  https://forums.oracle.com/message/10773444
  https://forums.oracle.com/message/10254761
5. Anonymous28 Nov 2013
  Hi Daniel,
  We have successfully created PL/SQL custom API by your post. Thank u so much.
  Above mentioned " Can't locate Class/MethodMaker.pm in @INC (@INC " error occurred due to perl modules not installed in my EBS. Now we installed and we can generate WSDL file.
  
  But,
  we can not test in SAOPUI with this WSDL. SOAPUI throws some security exception but we mentioned all security (User Name Token). Please find the below error in SOAPUI.
  
  Error Description Responsibility key is not valid.
  Error Details oracle.apps.fnd.soa.util.SOAException: InvalidResponsibilityKey: Invalid Responsiblity KeyInvalidResponsibilityKey: Invalid Responsiblity Key at oracle.apps.fnd.soa.util.SOAContext.convertHeaderNamesToId(SOAContext.java:166) at oracle.apps.fnd.soa.provider.SOAProvider.processMessage(SOAProvider.java:277) at oracle.j2ee.ws.server.provider.ProviderProcessor.doEndpointProcessing(ProviderProcessor.java:956) at oracle.j2ee.ws.server.WebServiceProcessor$1.run(WebServiceProcessor.java:358) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at oracle.j2ee.ws.server.WebServiceProcessor.invokeEndpointImplementation(WebServiceProcessor.java:355) at oracle.j2ee.ws.server.provider.ProviderProcessor.doRequestProcessing(ProviderProcessor.java:466) at oracle.j2ee.ws.server.WebServiceProcessor.processRequest(WebServiceProcessor.java:114) at oracle.j2ee.ws.server.WebServiceProcessor.doService(WebServiceProcessor.java:96) at oracle.j2ee.ws.server.WebServiceServlet.doPost(WebServiceServlet.java:194) at javax.servlet.http.HttpServlet.service(HttpServlet.java:763) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713) at
  
  Kindly advice for above this issue.. OR if u have any document for How to test in SOAPUI pls share me.
  
  Thanks and Regards
  Govindh
6. Daniel BorgströmAuthor29 Nov 2013
  In the SOAP header you have a tag which can hold the responsibility value. In my example I don't have any value there (see screen shot above) but if you provide a value it must be a valid one.
  
  Se below on some information about the error:
  http://aabhishek.wordpress.com/about/oracle-ebs-integrated-soa-gateway-isg/troubleshooting-service-invocation/
7. Anonymous23 Dec 2013
  Hi Daniel,
  We successfully completed EBS SOA Gateway using PLSQL Custom API. Next we are going to implement JAVA Custom API. Thanks for your post..
  
  Thanks
  Govindh
8. Daniel BorgströmAuthor23 Dec 2013
  It makes me happy to hear that you have solved you problems and are moving forward :)
9. Anonymous26 Mar 2015
  Hi Govind,
  
  Is it possible to you to share the steps to install perl module in EBS R12.1.2?
  
  Thanks,
  Anil
kaushek2 May 2014
Hi Daniel,

We have a specific requirement whereby we are trying to integrate Peoplsoft HR recruitement process with Oracle EBS HRMS module.
Data from Peoplesoft needs to be pushed to Oracle HRMS for new employee creation.

We planned to use Oracle SOA Integrated Gateway to accomplish this requirement using Custom APIs built on top of Seeded API.
At one point down the line we came across BPEL process being required and deploying this on the BPEL PM server. This demands licensing of Oracle SOA 11g Suite and we want to bypass this and want to be independent of any additional licensing. We have Oracle Application R12.1.3 running on Apps server.
What can be done if we don't want to go the BPEL way and still be able to integrate Peoplsoft with Oracle EBS after generating the WSDL link and deploying through the integration repository?

Can we use SOAP UI as an alternative to this?

Note: Peoplesoft side would be the triggering point for this integration, from where they would be able to invoke the webservice and send the data as XML file.

Any help on this would be greatly appreciate.

Thanks & Regards,
Kaushek
1. Daniel BorgströmAuthor4 May 2014
  Hi,
  
  My first question is why you possible need to BPEL process? Is there some specific logic that requires that?
  
  If it's just an integration between Peoplesoft and EBS (point-to-point) i would expose your custom API as a webservice and then call it directly from Peoplesoft. My knowledge in Peoplesoft is very limited but I guess it runs on an Oracle database, from there (PL/SQL) it's easy to call the webservice you expose in EBS.
  
  SOAP UI is just a tool you use to test your webservices.
  
  BR Daniel
Anonymous31 May 2014
Hi Daniel,

Not sure if you did integrate ISG with EBS version 10.2.3 and FMW 11.1.1.7. If so, let's chat via email and help me out to set it up, and will pay you too. My email is eddiesengola@yahoo.com

Thanks,
Eddie
Anonymous31 May 2014
Hi Daniel,

Sorry, I meant EBS version 12.2.3

Thanks,
Eddie
daxesh30 Jun 2014
Hello Daniel,
Very good article... I had created WS successfully using this article long back.Keep it up, thank you.

Regards,
Dakshesh Patel
Anonymous10 Jul 2014
Hi Daniel,

This is a very good article.

Do you know if it is possible to declare the custom package in a custom schema (not APPS)?. Because after I upload the .idlt and when I try to generate WSDL, I'm getting an error (Already try the same package in APPS and it works).

Tks in advance,

Pablo.
1. Daniel BorgströmAuthor11 Jul 2014
  I have not tried it but I guess it should work. Create synonym and grants on your package to APPS and then try again.
  
  Good luck :)
Manuel29 Apr 2015
Hi Daniel i have a problem, when i tried to donwload iLDT, i got this:

[proyecto@lapebs31 ~]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=SYSADMIN gl:/patch/115/sql:xx_test_soagway.pls:12.0=/tmp/xx_test_soagway.pls
Can't locate Class/MethodMaker.pm in @INC (@INC contains: /dev150/apps/apps_st/appl/fnd/12.0.0/perl /dev150/apps/apps_st/appl/fnd/12.0.0/xml/orc115 /dev150/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo /dev150/apps/apps_st/appl/fnd/12.0.0/bin/ /dev150/apps/tech_st/10.1.3/perl/lib/5.8.3/i686-linux-thread-multi /dev150/apps/tech_st/10.1.3/perl/lib/5.8.3 /dev150/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3/i686-linux-thread-multi /dev150/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3 /dev150/apps/apps_st/appl/au/12.0.0/perl /dev150/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3/i686-linux-thread-multi /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl/5.8.3 /ade/smayer_perl58_main_linux/perl58/bin/Linux/Opt/lib/site_perl .) at /dev150/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
BEGIN failed--compilation aborted at /dev150/apps/apps_st/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
Compilation failed in require at /dev150/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.
BEGIN failed--compilation aborted at /dev150/apps/apps_st/appl/fnd/12.0.0/bin/irep_parser.pl line 37.

any idea of this? how i can solve this? me i miss something?
Anonymous20 May 2015
Hi,

Thank you for a detailed explaination of this topic, it helped us create custom Webservcies easily.
One question I have is, do we have to use a business event to create the custom webservcies under?
We want to create some read utilities for MFG , PA etc so that our thirdparty applications can read from
EBS using these Webservciesand hence they do not belong under any business event..
Please advice...
Anonymous20 May 2015
Manuel,

More then likely you do not have the 3 required perl modules installed.
This is the solution give by Oracle to us when we faced similar issue...

Use the solution in Note 1079218.1 to be sure these modules are correctly installed.

Also, If this is a multi-node environment then be 100% sure you have follow Note 1081100.1 - Configuring Oracle E-Business Suite Integrated SOA Gateway Release 12.1.2 and Release 12.1.3 in a Multinode Environment
Anonymous12 Jan 2016
Hi Daniel ,

While generating Ildt file for .Pls , I facing the below Error , I am messing any setup please help on the same

# Generating annotation output.
# Processing file '/u52/ar121/ATL04/apps/apps_st/appl/gl/12.0.0/patch/115/sql/glijelnb.pls'.
# Warning: A @rep:scope tag is not in '/u52/ar121/ATL04/apps/apps_st/appl/gl/12.0.0/patch/115/sql/xx_test_soagway.pls, skipping.
# Warning: Not generating iLDT for /u52/ar121/ATL04/apps/apps_st/appl/gl/12.0.0/patch/115/sql/xx_test_soagway.pls, file to be ignored.
# Done all files.

Regards,
Dinaker
1. Anonymous16 Mar 2016
  Recheck pls file
  1.Any white spaces
  2.all annotation tags are in accordance with syntax
  3.Semicolons are properly ended after annotation.
Anonymous16 Mar 2016
Thanks in advance ..How to make parameter as mandatory by using annotation ??
eg : * @param P_SEGMENT1 varchar2 Segment 1 ,whatever code after variable(P_SEGMENT1) it will take as description.can you help in this
mpkirankumar1 Sept 2016
Hello Daniel

With the above article we could successfully deploy and invoke our service(REST) based on PL/SQL API, but I have an issue we have changed the definition of one of the Procedure in API in DB, we have regenerated iLDT file and re-deployed the service, Service is working as expected but in in EBS API procedure definition is still shows old one, though we re-generated iLDT,FNDLOAD and re-deployed service, and bounced the middle tier(application tier) still we are seeing old definition in EBS screen, can you please suggest how this will be fixed ( Service is working per new changes but only in EBS screens I am seeing old definition).

mpkirankumar