Oracle EBS Endeca Extension - Error encounter in new Installation

If integration is not done properly below are the list of possible errors which you might encounter :

1.     Endeca Menus are not enable:

Error Message/Description:

Endeca related menus are not available/ visible in EBS application.

Cause:

Required roles are not assigned to responsibility and grant are not given to the role to view Endeca pages.

Solution:

Add roles to responsibilities and provide grants as well. Refer oracle note id Doc ID 1614014.1 -> APPENDIX B: adding roles to responsibilities and setting security.

2.     Profile option FND_ENDECA_INTEGRATOR_URL is not defined:

Error Message /Description:

When we run concurrent program from EBS it got error out saying Profile value is not set for FND_ENDECA_INTEGRATOR_URL.

Cause:

Profile option is not set.

Solution:

Set value: Clover URL.

3.     Concurrent Program Completed with Error :

Error Message/Description:

Server returned HTTP response code: 401 for URL:

Cause:

Not able to ping URL from EBS.

Solution:

 Validate clover login credential stored in FND vault.

SQL to validate credential in FND vault:

select fnd_vault.get ('endeca_integrator_admin','clover_userName') from dual;

                select fnd_vault.get ('endeca_integrator_admin','clover_password')  from dual;

(Note: If there is no data in above sql queries then you have to run script to store clover credential in FND vault. For more information please refer Document ID: 1470151.1 )

        

       4.         Graphs are Finished but no Data in EBS Endeca pages:

                         Error Message/Description :

 Unable to execute the EQL query that generates the chart data. See the application log for details, or contact a system administrator.

Cause:

Configuration issue.

Solution:

Validate EidConfig.properties , portal-ext.properties and Studio.log and confirm each values mention to build EBS – Endeca integration.

Check value for FND_SESSION_MANAGEMENT using below Sql query value is case sensitive.

Select FND_SESSION_MANAGEMENT.getsessioncookiename() from dual .

Verify same in portal-ext.properties and eidconfig.properties file if any discrepancy found update value and bounces studio manager.

File location:

/u01/Oracle/quickInstall/EidConfig.properties

/u01/Oracle/Middleware/user_projects/domains/portal-ext.properties.

5.     Clover graph fail to load data for sandbox: AP

Error Message/Description:

                Clover graph failed to load data for AP.

Cause:

Endeca Aging Template not set to any templatei.e “AP: Endeca Aging Template”.

Solution:

Perform Setup for Template and set profile option value “ AP: Endeca Aging Template”

Oracle EBS Endeca Extension - Pre requisits

Basic Use of Endeca:

Oracle Identity Manager (OIM) is a provisioning solution that works with E-Business Suite, People Soft and other third-party systems, and provides the management activities, business processes, and technologies governing the creation, modification and deletion of user access rights and privileges across an organizations IT systems. By automating these activities, organizations gain better control over user access rights, enforce organizational security policies and ensure adherence to regulatory standards, Oracle Access Manager provides the Single Sign-On capabilities across E-Business Suite core module, i Modules and BI and other integrated applications. In this presentation, we will discuss the functional, technical approach and architecture to integrate E-Business Suite with Oracle Identity Manager. We will share case-studies of E-Business Suite and Oracle Identity Management integration implementations.

Administer EBS and Endeca Data:

Pre-Requisites :

Ensure you comply with following pre-requisites before you proceed with the rest of

the integration steps:

1. Installed and completed common configuration as described in Installing Oracle

E-Business Suite Extensions for Oracle Endeca, Release 12.2 V5 (Doc ID: 1614014.1).

2. Installed additional patches required for Oracle Projects Extensions for Oracle

Endeca as described in Oracle Projects Extensions for Oracle Endeca Product

Configuration Notes Document ID: 1470151.1

Filtering Components in Oracle Endeca :

Oracle E-Business Suite Extensions for Oracle Endeca provide you various filtering options to enable you to filter and search for data.

These filtering components are:

Search Box - Power users can configure the search to determine the data source, how to determine a matching record, and whether to support type-ahead. End users can use the search box to enter keyword to conduct a search. If multiple search configurations are available, end users can first select the search configuration that they want to use.The Search Within check box enables a search limited to the currently displayed data.

Guided Navigation - use this component to use attribute values to filter data. End users can select values in order to refine the current data to only include records with those values. For some attributes, end users can select multiple values. They also may be able to do negative refinement, to only include records that do not have a selected value.

Concurrent ETL Graphs:

To enable concurrent ETL graphs:

You must enable Oracle Endeca ETL graphs to be run from the Oracle E-Business Suite as a concurrent request.

1. In Oracle E-Business Suite, set the profile option FND_ENDECA_CLOVER_URL tohttp://<endeca_hostname>:7006/clover.

2. Run the script 'storeCloverLoginInFndVault.sh' to add Clover login credentials in EBS Fnd Vault. 

The path is /u01/Oracle/quickInstall/bin/ in Build-12 QI of 12.2.3 V5. Implementation and Integration 2-3

Note: The script automates the creation of the Clover login credentials process and prompts you to provide an EBS DB apps schema password, clover application user name and password.When you enter the values, the script connects to the EBS database using apps credentials, and stores the clover application login credentials in EBS FND_VAULT.

3. The responsibility 'EBS-Endeca Administrator' allows an Oracle E-Business Suite user to access the concurrent program 'Run ETL graphs'. This provides the ability to run Full or Incremental graphs for a specified application sandbox. The system administrator can add this responsibility to any Oracle E-Business Suite user.

4. The 'EBS-Endeca Administrator' responsibility provides the following menu options for the 'Run ETL graphs' concurrent program:

Submit Requests :

Clicking 'Submit Requests' enables users to launch a concurrent request. After selecting 'Single Request' and clicking 'OK', the submit request form displays.After selecting 'Run Clover ETL Graphs' into the Name field, users are prompted to specify the application 'Sandbox' (e.g. eam or icx-iproc) and the'Graph Type' (Full.grf or Incremental.grf). Users can submit requests to run

Immediately by clicking 'Submit'. After submitting a request, the status of this request can be tracked using the 'View Requests' and 'Monitor' menu options.

Schedule :

Clicking 'Schedule' allows users to schedule one or more requests to launch the concurrent program 'Run Clover ETL Graphs'. Users are prompted to specify the application 'Sandbox' (e.g. eam or icx-iproc) and the 'Graph Type' (Full.gr for Incremental.grf). Users can submit the request to run immediately by clicking 'Submit' or can specify additional concurrent request options by navigating through the options pages after clicking 'Next'. After submitting are quest, the status of this request can be tracked using the 'View Requests' and'Monitor' menu options.

Monitor :

This menu option allows users to access the list of concurrent requests that they have submitted in the Oracle E-Business Suite. By default 'All My Requests'displays requests submitted within the last seven days. You can modify the query criteria by clicking the 'Advanced Search' button to specify requests to display. Users can click the 'Details' icon and then the 'View Log' button to view the concurrent request log file for the submitted concurrent request.

View Requests :

2-4 Oracle E-Business Suite Extensions for Oracle Endeca Integration and System Administration Guide Clicking 'View Requests' launches the View Requests form in the Oracle E-Business Suite. This enables users to search for a specific request or all of the concurrent requests. Clicking 'View Log' displays the log file for that concurrent request.

Note: If the concurrent request for a longer running Full Load Graph displays as 'Completed, Error', then you can access the log file to determine the Run ID in Clover and the URL for the clover UI, and access the 'Executions History' through the Clover UI.

Assigning Administrator Role to E-Business Users :

Oracle E-Business Suite users do not have the Endeca Studio Administrator role assigned by default and therefore cannot modify Endeca Pages. If you have a requirement to allow E-Business Suite users to modify Endeca pages, then you must assign the Administrator role to the users.

To assign Administrator role to E-Business Suite users:

1. Navigate to Endeca Studio (see your System Administrator to obtain the Endeca Studio URL, user id, and password).

2. Login as Studio Admin (admin@oracle.com). The default password is welcome123.

3. Go to the Control Panel.

4. Click the Users link.

5. Click on EBS User Name.

6. Click on the Roles link.

7. Click Select Link.

8. Select the Administrator Role.

9. Click the Save button.

The E-Business Suite user should now have administrator privileges and access to Endeca pages.

 For Example:

Setup and Configuration Steps

To set up Oracle Order Management Extensions for Oracle Endeca, you must complete the following steps:

1. Set Access Control: This is for assigning UMX roles and updating access grants.

UMX Role                                                                              Internal Code Name

Order Management Endeca Access Role   UMX|ONT_ENDECA_ACCESS_ROLE

2. Schedule Setup for Full Endeca Refresh:

This is specifically to load data into Endeca server,this can be done using ‘EBS-Endeca Administrator’ responsibility from EBS. We can either schedule ‘Run Clover ETL Graphs’ Concurrent program or just run one time for loading existing data. When you are submitting the first time use graph type as ‘FullLoadConfig.grf’ and sandbox you can select according to module for which we need to load data for example ‘ont’ for Orader management.

Oracle EBS - Endeca Extension for Release 12.2 : Basic Introduction

Introduction

What is Endeca ?

It is Data discovery platform to search and filter data as per requirement.

               

Why Endeca extensions to EBS

It enables companies to optimize operational decisions and improve process efficiency with real time access.                    

Access to real time data to show operational reporting.

On-line dashboard to key metrics.

Ease of review of the related content.

Oracle Endeca Information Discovery offers a complete solution for agile data discovery across the enterprise, empowering business user independence in balance with IT governance.

This unique platform offers fast, intuitive access to both traditional analytic sources, leveraging existing enterprise investments, and non-traditional data, allowing organizations to achieve unprecedented visibility into all their information, to drive growth while saving time and reducing cost.

Only Oracle delivers a complete enterprise platform that includes powerful self-service discovery, enabling faster and more confident decisions, reducing the IT backlog, and increasing innovation.

Oracle Endeca Information Discovery (EID) is a sophisticated data discovery platform that searches for and filters data. It is based on a patented hybrid search-analytical database, and gives IT a centralized platform to rapidly deploy interactive analytic applications, and keep pace with changing business requirements while maintaining information governance. Oracle Endeca Information Discovery provides enhanced display capabilities using metrics, graphs and charts. It provides further drill down capabilities using tag cloud, data attributes, and granular dimensions.

Oracle Endeca Information Discovery provides the following features:

• Range Filters

• Guided Navigation

• Customized charts to represent data graphically

• Collection set of results tables

• Tag Clouds

• Dashboard metrics

 

Oracle Endeca Information Discovery Architecture:

Oracle Endeca Information Discovery has three tiers:

Oracle Endeca Server :

This hybrid search/analytical database is at the heart of Oracle Endeca Information Discovery, providing unprecedented flexibility in combining diverse and changing data as well as strong performance in analyzing that data. Oracle Endeca Server has the performance characteristics of in-memory architecture coupled with a highly intelligent approach to using disk, optimizing available resources and avoiding being memory-bound. Oracle Endeca Server is also used extensively as an interactive search engine on many major e-commerce and media websites.

Oracle Endeca Information Discovery Integrator:

Integrator is a suite of industrial strength data management tools that makes it easy for business users and IT to acquire, ingest, and enrich information. In addition to self-service data loading, OEID Integrator is a powerful visual environment for data integration that includes the Information Acquisition System (IAS) for gathering content from file systems, content management systems, and websites; and out-of-the-box ETL purpose-built for incorporating data from a wide array of sources, including Oracle BI Server. Oracle Endeca Web Acquisition Toolkit is a web-based graphical ETL tool that allows IT to enter a URL, collect content, and add structure to it as part of the data acquisition process. Connectivity to data is also available through Oracle Data Integrator (ODI).

Oracle Endeca Information Discovery Studio :

The front end to Endeca Server, Studio is a rich visual application composition environment that provides drag-and-drop authoring to create highly interactive, personal and enterprise-class information discovery applications. Studio also includes self-service data provisioning, which gives business users the ability to add their own data, connect to existing gold-standard enterprise sources, and combine them. Studio enables allows IT to create application templates for self-service and ensure that data security is maintained.

These components combine to provide a powerful discovery platform that empowers business users and IT equally. From IT-provisioned applications with myriad discovery components exposing data from several sources, to the personal, incrementally-evolving application developed by a business user, EID enables the discovery of critical insights, whatever the data, and whatever the question.

The magic starts with Endeca Server, the revolutionary database that drove Endeca’s success across e-commerce, enterprise search, and data discovery.

 

Why EBS Endeca Extensions?

Business Drivers: 

• Access to real time data
• On-line dashboard to key metrics
•  Ease of review of the related content

IT Drivers:

• No customization
• Fast Implementation
• Intuitive user interface

Benefits for Everyone

•       Function and Data Security is honored in EBS Endeca similar to EBS

•       Full featured search across different data sources

•       Contextual navigation keeps the focus on the subject being analyzed

•       Interactive visualizations

End User Benefits

Answers to New Questions:

Ability to explore data & find answers to unanticipated questions without structured queries.

Real Time Insight:

Quick access to operational information based on structured & unstructured EBS data.

Improved User Productivity:

Cross-organization and flex field searching enables users to more rapidly access data needed for day-to-day decisions & actions.

Administrator Benefits:

Low Cost: 

• No Change to EBS Database
• No new Employee Training

Rapid Deployment:

• Out-of-the-Box UI and EBS security integration.

Highly Configurable:

• Delivered UI components

How data populates in oracle Tables - O2C Flow

--Create Item
Select * from Mtl_system_items_b  where segment1 like' '

--Check On hand Quantity of Item

Select * from Mtl_onhand_quantities_detail  where INVENTORY_ITEM_ID ='2027' --TRANSACTION_QUANTITY

-- Create Sales Order --

--Enter order details
select flow_status_code from Oe_order_headers_all  where ORDER_NUMBER =160000003 ;-- ENTERED
select flow_status_code from Oe_order_lines_all where HEADER_ID =20018 ;-- ENTERED

-- Booked Sales Order

select flow_status_code from Oe_order_headers_all  where ORDER_NUMBER =160000003 ;-- BOOKED
select flow_status_code from Oe_order_lines_all where HEADER_ID =20018 ;-- Awaiting Shipping
select * from wsh_delivery_details where SOURCE_HEADER_ID= 20018 ;-- Delivery id not generated and RELEASED_STATUS = 'R' (Ready to Release)

--Pick Release
select * from wsh_delivery_details where SOURCE_HEADER_ID= 20018 ;-- Delivery id generated and RELEASED_STATUS = 'Y' (Pick Confirm ..there is one more status in between this i.e. S: Pick release)
select * from wsh_new_deliveries where DELIVERY_ID=10009;
select * from mtl_txn_request_headers where REQUEST_NUMBER ='8001' ;--get this value form Concurrent program of Pick release
select * from mtl_txn_request_lines where HEADER_ID='8001'
select * from mtl_reservations where INVENTORY_ITEM_ID='2027' --we will get some data

--Ship Conform
select * from wsh_delivery_details where SOURCE_HEADER_ID= 20018 ;-- RELEASED_STATUS = 'C' (Ready to Release)
select flow_status_code from Oe_order_headers_all  where ORDER_NUMBER =160000003 ;-- BOOKED
select flow_status_code from Oe_order_lines_all where HEADER_ID =20018 ;--Shipped
select * from mtl_reservations where INVENTORY_ITEM_ID='2027'; -- No data
Select * from Mtl_onhand_quantities_detail  where INVENTORY_ITEM_ID ='2027' ;--check quantity
select * from mtl_material_transactions where INVENTORY_ITEM_ID ='2027';

--Auto invoice import
select * from ra_interface_lines_all where INTERFACE_LINE_ATTRIBUTE1 ='160000003'; -- INTERFACE_LINE_ATTRIBUTE1 is Order number
select * from ra_customer_trx_all where TRX_NUMBER ='100021' --TRX_NUMBER is invoice number
select * from ra_customer_trx_lines_all where CUSTOMER_TRX_ID ='56002';
select * from ra_cust_trx_line_gl_dist_all where CUSTOMER_TRX_LINE_ID ='44014';
select event_id from ra_cust_trx_line_gl_dist_all where CUSTOMER_TRX_LINE_ID ='44014';
select * from ar_distributions_all;

--Create Accounting
select * from xla_events where EVENT_ID =56022;
select * from xla_ae_headers where EVENT_ID =56022;
select * from xla_ae_lines where AE_HEADER_ID =13008;
Select * from GL_JE_BATCHES  where NAME like' '; -- Get batch name form concurrent program
Select * from GL_JE_HEADERS  where JE_BATCH_ID=9006;
Select * from GL_JE_LINES  where JE_HEADER_ID =9006;

--Create Receipt
Select * from AR_CASH_RECEIPTS_ALL  where RECEIPT_NUMBER like'CRP2-1'; -- Create receipt manually
Select * from AR_CASH_RECEIPT_HISTORY_ALL  where CASH_RECEIPT_ID=5000;
Select * from AR_RECEIVABLE_APPLICATIONS_ALL where CASH_RECEIPT_ID=5000;
select * from AR_PAYMENT_SCHEDULES_ALL where  CASH_RECEIPT_ID=5000;
select * from ar_distributions_all;
--Create Accounting
select * from xla_events where EVENT_ID =56022;
select * from xla_ae_headers where EVENT_ID =56022;
select * from xla_ae_lines where AE_HEADER_ID =13008;
Select * from GL_JE_BATCHES  where NAME like''; -- Get batch name form concurrent program
Select * from GL_JE_HEADERS  where JE_BATCH_ID=;
Select * from GL_JE_LINES  where JE_HEADER_ID =;

Various techniques to store Debug log message to handle Errors - Part 3

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I have used debug file generation technique which generates one common log file for debug messages with time stamp. Which help us to debug errors in code or we can also track code execution flow.

Example:

In this example we have created one common procedure which appends all text messages (debug Message) to common log file which is placed in our server (location of file and name are predefined in common procedure). Main motive of common procedure is to open file and append log messages. We can call this procedure from any package to append log messages, we have to pass only log message.It will automatically add times tamp and message will get append to log file.

Create directory

SQL> CREATE DIRECTORY Test_dir AS '/appl/gl/user'';

SQL> GRANT READ ON DIRECTORY Test_dir TO PUBLIC;

Executable

CREATE OR REPLACE PROCEDURE xx_test_debug_message (p_str VARCHAR2)

AS

v_file_type     UTL_FILE.FILE_TYPE;

v_file_path        VARCHAR2(100);

v_file_name      VARCHAR2(30) := ‘TEST_DEBUG_LOG’

intval    BINARY_INTEGER;

strval    VARCHAR2 (256);

paramtype        BINARY_INTEGER;

BEGIN

paramtype := DBMS_UTILITY.get_parameter_value ('utl_file_dir', intval, strval);

g_file_path := strval;

v_file_type := UTL_FILE.FOPEN('v_file_path','v_file_name','a');      

UTL_FILE.PUT_LINE(v_file_type,TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'||':'|| p_str));      

UTL_FILE.FCLOSE(v_file_type);

EXCEPTION     

WHEN OTHERS THEN          

DBMS_OUTPUT.PUT_LINE ('ERROR ' || TO_CHAR (SQLCODE) || SQLERRM);          

NULL; 

END XX_TEST_DEBUG_MESSAGE; 

Testing

Declare

l_test VARCHAR2 (30):='test data';

BEGIN

xx_test_debug_message (l_test);

END;

(Note: This is very basic example which can be enhanced as per business requirements for e.g. you can set some debug level and control these messages.)

Various techniques to store Debug log message to handle Errors - Part 2

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I am using one common framework to handle errors as well as progress of execution flow. To achieve this functionality I have created three custom tables one common package which is used across the instance.

Below are the Required Database Objects:

Error Handling Tables:

xx_process_log:

This table is used to track the progress of Procedure/Function execution flow. This is first table where data is inserted having statuses like‘Start’ and after execution ‘Complete’ Or ‘Error’.

It has one unique message id which is shared by other two tables to link messages i.e. for unique reference.

xx_message_log:

This Table is used to store random user friendly messages like DBMS_OUTPUT.PUT_LINE. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

xx_error_log:

This Table is used to store standard Errors i.e. exception message with sqlcode and sqlerrm. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

Common Package:

xx_common_error_handle:

This package is used to Store logic for Inserting data into three error handling tables. It has three separate procedures to insert data into xx_process_log,xx_message_log and, xx_error_log.

Mandatory Common Procedure in all packages where we have to use this Framework:

xx_process_track_log:

This Procedure is common and mandatory in all Packages where we have to use Error handling framework. This is called by each Procedure or Function at start and at end to update the record into table xx_process_log.

When first time we called, it will insert details (as per your requirement you can keep in parameter) in table xx_process_log with status ‘Start’ and it has one sequence which generate unique message id and store in table. Also this message id is required OUT parameter, as we will use this message id as a reference while inserting data into table’s xx_message_log and, xx_error_log.

(Note: This is very basic example which can be enhanced as per business requirements for

e.g.

a) You can use some profile options or lookup to control these messages.

b) You can send email notification when error occurs

c) You can send data to dashboard for display using business events.

)

Various techniques to store Debug log message to handle Errors - Part 1

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I have created Error table, sequence (to generate unique message id) and one procedure. This procedure is used to insert the log messages into error table. So whenever we have to capture some debug message (user define) we can call this procedure and this will log messages in error table.

Create Sequence

CREATE sequence xx_log_mesg_sminvalue 1 start with 1 increment BY 1 NOCACHE;

Create table

CREATE TABLE xx_error_message

  (

Text_message  VARCHAR2(100),

Mesaage_id    NUMBER,

Cretion_date DATE   

  );

Executable:

CREATE OR REPLACE PROCEDURE XX_log_message(p_str VARCHAR2)

AS

BEGIN

  INSERT

  INTO xx_error_message

  (      Text_message,

Mesaage_id,

Cretion_date    )

    VALUES

( p_str ,

xx_log_mesg_s.nextval ,

      SYSDATE    );

  COMMIT;

END XX_log_message;

Testing

DECLARE

  l_test VARCHAR2(30) :='test data';

BEGIN

XX_log_message(l_test);

END;