How to add custom integration in Oracle Integration Repository

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/sqland /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