Sunday, August 14, 2011

How to create a custom integration interface in SOA Gateway

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

74 comments:

  1. Excellent article. Never saw a more well documented blog on a piece of functionality that is new.

    ReplyDelete
    Replies
    1. 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

      Delete
    2. I also agree. Very informative.

      Delete
    3. Hello Daniel

      Can you provide us sample code for out variable in payload.

      Delete
  2. Fantastic. Other blogs our there seem to regurgitate the manual. This is a godsend.

    ReplyDelete
  3. 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

    ReplyDelete
    Replies
    1. 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

      Delete
  4. Hi Daniel,

    thanks for your reply and suggestion.

    Regards,
    Ram

    ReplyDelete
  5. 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

    ReplyDelete
  6. I am getting below error while invoking web service
    java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: For input string: ""

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. daxesh.nirma@gmail.comSeptember 14, 2012 at 5:35 PM

      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

      Delete
  7. 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

    ReplyDelete
    Replies
    1. 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?

      Delete
    2. 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.

      Delete
  8. 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

    ReplyDelete
    Replies
    1. Try to set your out parameter to XML Type or CBLOB/CLOB.

      Regards,
      Daniel

      Delete
    2. Good one! Thanks for the help Daniel.

      Greetings,

      Charif

      Delete
  9. 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

    ReplyDelete
  10. Very well written Daniel

    ReplyDelete
  11. 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?

    ReplyDelete
    Replies
    1. Please see one my other posts on how set the output parameter to xml type.

      Delete
  12. 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?

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. 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?

      Delete
    3. 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).

      Delete
  13. 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

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. 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

      Delete
  14. This comment has been removed by the author.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. 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 ?

    ReplyDelete
    Replies
    1. Sorry, I have not seen this error but I would investigate the Soa Gateway installation and yhe environment variables.

      Delete
    2. I am new for all about these stuffs. what do you mean yhe enviroment vars. ?
      sorry for wasting ur time :/

      Delete
    3. 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

      Delete
    4. 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.

      Delete
    5. This comment has been removed by the author.

      Delete
    6. 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.

      Delete
    7. This comment has been removed by the author.

      Delete
    8. This comment has been removed by the author.

      Delete
    9. 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 .

      Delete
    10. 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...

      Delete
  17. Hi Daniel,
    How do i delete a custom service from the integration repository

    ReplyDelete
  18. 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.

    ReplyDelete
  19. 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

    ReplyDelete
    Replies
    1. Sorry, I don't understand what you mean. Can you clarify?

      Delete
  20. 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

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Hi Daniel,
      Thanks for your comments

      Regards
      Govindharaju G

      Delete
    3. 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

      Delete
    4. 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

      Delete
    5. 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

      Delete
    6. 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/

      Delete
    7. 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

      Delete
    8. It makes me happy to hear that you have solved you problems and are moving forward :)

      Delete
    9. Hi Govind,

      Is it possible to you to share the steps to install perl module in EBS R12.1.2?

      Thanks,
      Anil

      Delete
  21. 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

    ReplyDelete
    Replies
    1. 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

      Delete
  22. 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

    ReplyDelete
  23. Hi Daniel,

    Sorry, I meant EBS version 12.2.3

    Thanks,
    Eddie

    ReplyDelete
  24. Hello Daniel,
    Very good article... I had created WS successfully using this article long back.Keep it up, thank you.

    Regards,
    Dakshesh Patel

    ReplyDelete
  25. 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.

    ReplyDelete
    Replies
    1. 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 :)

      Delete
  26. 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?

    ReplyDelete
  27. 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...

    ReplyDelete
  28. 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

    ReplyDelete
  29. Very good article... I had created WS successfully using this article long back.Keep it up, thank you.

    ReplyDelete
  30. 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

    ReplyDelete
    Replies
    1. Recheck pls file
      1.Any white spaces
      2.all annotation tags are in accordance with syntax
      3.Semicolons are properly ended after annotation.

      Delete
  31. 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

    ReplyDelete
  32. This comment has been removed by the author.

    ReplyDelete
  33. 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

    ReplyDelete

javascript:void(0)