Tuesday, April 19, 2016

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:


/* $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





* 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



                   P_SEGMENT2 IN VARCHAR2,

                   P_SEGMENT3 IN VARCHAR2,

                   P_SEGMENT4 IN VARCHAR2,



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


Wednesday, May 27, 2015

Oracle EBS Endeca Extension - Question and Answer

Q:  Does it require separate licensing from oracle to run it? How do we do this?
A:  Customers need to pay for two things:
·         License the desired E-Business Suite Extensions for Endeca & Endeca Discovery Foundation Endeca Discovery Foundation for Oracle license allows customers to use all the components of Endeca Information Discovery (EID) software: Endeca Server, Integrator Server, and Studio Server.

Q:  Can we install Endeca for just one module or is it applied over the application?
A:  Each Endeca Extension is licensed separately, so you can install Endeca Extension for a given module.

Q:  How can endeca be installed does it require separate tech. consultancy team?
A:  As with any implementation, this is up to the customer whether to use a system integrator.  Oracle does have existing Partners who have experience should this be the direction desired, but it is not a requirement.

Q: What does it take to integrated to Endeca? After installing Endeca patch, do we need effort from development team to integrate to existing EBS.
A:  Integration is delivered out of the box for R12.1.3 and R12.2  Be on EBS R12.1.3 or 12.2  License the EBS Extensions for Endeca products & Endeca Foundation as previously mentioned. Get additional hardware to support in-memory processing where you can deploy EBS-Endeca to setup this instance. Apply EBS-Endeca patches on the EBS instance. Download EBS-Endeca Quick Installer which contains all the EID components and EBS artifacts (ETL, UIs, EBS Security code etc.).Pair EBS instance with EBS-Endeca instance and perform the setups documented in the installation documentation.

Q:  So from iProcurement screen i can create PR? Is there any affect from work order for those items that are not in stock?
A:  This is standard eAM functionality to allow PRs to be added to the WO from iProcurement.

Q:  Is the shopping cart with images part of endeca or is it 12.2?
A:  The shopping cart has always been part of iProcurement.

Q:  Do we (Internal Employees vs. External Customers) have a vision instance to work on? Can you give us this vision link?
A:  Oracle employees can demonstrate the EBS Extensions for Endeca from their GSE instance.  For customer, once the Endeca Extensions are installed, the Vision content can be exposed in Endeca.

Q:  Does endeca need weblogic or is it a patch on EBS application server?
A:  The Endeca Extensions are delivered as an EBS patch that includes EID,  Endeca Discovery Foundation.  When you download the patches, EBS Endeca Quick Installer will include Weblogic at no additional license fee.

Q:   What is the cost for Endeca?
A:  You will need to contact your Application Sales Manager to get pricing.

Q:  Is Endeca is available for all the modules?
A:  Endeca Extensions are available for the modules as shown in the presentation. To date there are 21 Extensions and other are on the Procuct Development Roadmap.

Q:  Can you please touch base on how to pull the data from various sources for ex. Order data from Clould, istore, om and from some custom tables? also can you pl brief on the maintenance?
A:  Maintenance is like any other licensed application.  Patches are provided to fix issues whether they be EBS or Endeca.  
     Customers can add additional data content from E-Business Suite schema from the licensed product to a licensed E-Business Suite Extensions for Endeca application.
     Customers can only modify the Endeca Server datastores that are shipped by EBS (they cannot create net new ones). 
    Full use license is required if customers want to add additional data content from any other systems to a licensed E-Business Suite Extensions for Endeca application or build new content (i.e new application).

Q:  How is Endeca different than OBIEE(Oracle BI)?
A:  BI is typically about trending and about measuring against known KPIs.  Endeca-EBS integration is all about finding answers to NEW questions fast. OBIA is more about getting proven answers to known questions.

Q:  What does an install of this include?  How big of a project is it?
A:  Implementation Steps: In this example below, instructions are for eAM-Endeca, but steps are similar for other modules.
·         Step 1: Apply eAM-Endeca patches in the EBS instance (details about patches for different modules are there in the installation doc)
·         Step 2: Use EBS-Endeca quick install to setup Endeca instance. EBS-Endeca quick install is a zip file which has
·         Step 3: Enable Endeca pages for eAM
·         Step 4: Run ETL in the Endeca instance to load data from EBS (eAM Views) to Endeca Data Store in the Endeca Server.

Q:  What is the learning curve for ENDECA for new users?  How long to learn?
A:  The Endeca Extensions provide a very intuitive user interface.  Minimal training is required due to the information rich navigation experience. 

Q:  You said you can search item enterprise wide with multi org. How can you restrict certain items from end users based on global existence of application? 
A:  Endeca Extensions honor the EBS security in place. The example shown was based on Org Access Control.  If the security is part of EBS, then it will be honored in Endeca Extensions – is a user cannot see the data in EBS, they will not be able to see it in Endeca Extension pages.

Q:  Can Endeca search for data from Images (.jpg)?
A:  Out of the box, EBS Extensions will not search images.  It is possible, through full use license of Endeca.

Q:  What technology is used in building the Endeca pages?  How does Endeca handle oracle patching and possible impacts to the Endeca pages?
A:  Endeca is the technology used to build the pages.  Patching is as with any other application.  There can be EBS patches and Endeca patches that would be applied.  Patches are validated before they are released to ensure quality.

Q:  If I integrate other system using informational link, can I have ability to display message when other system is down?
A:  If you are loading data into the EBS Extensions from other sources, this requires full use license of Endeca.  In that case, if the source system is down, the next ETL will fail, so the data will not refresh.   The UI’s will still work, but the data will not be refreshed.

Q:  How will the search criteria change, if we are using punchout process in iProcurement where we don't have any catalogs locally?
A:  Endeca search will work exactly the same whether internal or external catalogs.

Q:  What are the development tools utilized for Endeca?
A:  Endeca has its own tools.  We use browser to design UI with Admin Access.  To design or modify ETL there is an Integrator Designer. There is a YouTube video available for illustration: http://youtu.be/MBTQXNYBaBc?list=PLEOdbv_KaxnvbKypiUDfhSW6agJvJ30ds

Q: Does EBS admin user make global changes across their particular Endeca extension?
A:  Only Admin User can change the pages, but other users with the same responsibility will have the same change impact.

Q:  We have Exadata machine, so do we need exalytics to run endeca?
A:  Endeca Extensions can be run in any Linex machine.  Refer to installation document: Installing Oracle E-Business Suite Extensions for Oracle Endeca, Release 12.1 V5 (Doc ID 1614014.1) 

Q:  I was trying to look some Endeca Implementing documents, all tries were giving me only the installation process. Where can I find the Endeca user documentation for iProcurement?
A:  Document Reference Links R12.1.3
·         Oracle E-Business Suite Extensions for Oracle Endeca Integration and System Administration Guide
Release 12.1 V5                                                                      
Part Number E37993-08                                                
·         Installing Oracle E-Business Suite Extensions for Oracle Endeca, Release 12.1 V5 (Doc ID 1683053.1)
·         Oracle E-Business Suite Extensions for Oracle Endeca Release Notes for Release 12.1 V5 (Doc ID 1920133.1)
A:  Document Reference Links R12.2
·         Oracle E-Business Suite Extensions for Oracle Endeca Integration and System Administration Guide
Release 12.2 V5                                                                 
Part Number E49519-09                                                
·         Installing Oracle E-Business Suite Extensions for Oracle Endeca, Release 12.1 V5 (Doc ID 1614014.1)
·         Oracle E-Business Suite Extensions for Oracle Endeca Release Notes for Release 12.1 V5 (Doc ID 1663193.1)

Q:  In today presentation it mentioned " Replace some customization " what do u mean by this?
A: In the example I spoke of, the customer shared with us that they had some planned customization for inventory, Item compare, as I recall.  After seeing the functionality provided with the Endeca Extensions, they no longer needed to develop this.

Q:  Once Endeca Foundation is installed, can it be used across EBS and Data Warehouse?
A: The Data Warehouse can be considered another external data source from which you can load data into the Endeca Data Domain (Store).

Q:  Can we integrate one Endeca install with multiple EBS instances?
A:  Not out of the box. However, through custom ETL this could be accomplished.

Q:  Is it possible to install Endeca standalone and connect to various system sources as EBS, PeopleSoft etc.?
A: The Endeca Extensions are an EBS product.  However, Oracle sells Endeca as a technology that can be licensed for development of applications.

Q:  Can we just purchase the Endeca extension without the Discovery foundation?
A:  No. License requirements include the Endeca Extension for the respective application and EID, Endeca Discovery Foundation.