Total Pageviews

May 25, 2015

5/25/2015 12:19:00 PM

Oracle 9i/10g/11g Database ->Database Tier of e-Business Suite Architecture
Oracle 9i Application Server->Application Tier of e-Business Suite Architecture

Oracle Developer Suite

Oracle Forms Builder 6i               Oracle Reports Builder 6i
Create new forms.                          Create/modify reports.

  •   Create, browse, or alter objects
  •   Graphically build, execute, and tune queries.
  •    Edit/Debug PL/SQL and stored procedures.
  •   Find and fix database problems.

  • Oracle Discoverer --Business Intelligence Reporting
  • Oracle Discoverer 4i Plus --Create Workbooks for generating BI Reports
  • Oracle Discoverer 4i Viewer --Use BI Reports Created by Discoverer Plus

  • Oracle Workflow Builder --Create new work flows, Modify seeded EBS work flows.
  • Oracle XML & e-Commerce Gateway Set up EDI and XML interfaces

Now Oracle also introduced sql developer,it is free tool.

Oracle Workflow -

It is a key component of the Oracle E-Business Suite process management architecture. It
provides an end-to-end business process management solution that includes business process modeling, business process execution and integration, business process monitoring, and business process intelligence. Oracle Workflow Builder is a graphical tool that lets workflow developers create, view, or modify a business process with simple drag and drop operations. The Workflow Engine embedded in the Oracle Database implements process definitions at runtime. The Notification System sends notifications to and processes responses from users in a workflow.

Oracle XML and e-Commerce Gateway –
 Oracle XML Gateway integrates with the Oracle E-Business Suite to create or consume XML messages based on application business events without the use of translator.
Oracle e-Commerce Gateway provides a common, standards-based approach for EDI integration between the Oracle E-Business Suite and third party applications & used to extract or import ASCII flat files by integrating with Oracle E-Business

The tools listed in the previous sections are used for development of the following :
 Development of extensions to augment existing Oracle Apps business functionalities
 Customize existing business functionalities to suit unique business requirements
 Develop Interfaces between existing legacy/third party systems and Oracle Apps system
 Data Conversion / migration of legacy data to suit Oracle Apps system
 Develop business critical Reports for business intelligence



CEMLI Defined as

All of which refer to recommended ways in which the standard solutions could be modified, if required.
“Oracle defined the CEMLI Framework, which translates into 20 classes of extensions that customers can add to their Oracle systems.”
The framework was established by Oracle On Demand to categorize customizations, meaning software that had to be added by the customer or implementer to the Oracle applications.
Another classic example of software that must be added to the system unique to a specific application is a localization. Localizations are country-specific requirements statutory and country-specific business practices that are not included in the standard software package.


All interfaces and data migration processes follow the same logic and always require the same type of validations. The only difference is which Open Interface you have to populate or if no Open Interface is available, which API or application tables you have to maintain.

What are the common tasks required for an interface?

• Receive and check existence of the input data file (name and directory);
• Check Unix environment (i.e. Unix directories and permissions);
• What to do with input data file? (Delete, keep, compress, archive);
• Where do you want to archive your data file?
• Display the input parameters and steps information in the log file;
• Perform data validations (field lengths and formats, derive ids, check existing of suppliers, customers, GL accounts, and so on);
• Produce a report of invalid records with meaningful messages;
• Transfer validated data into Oracle Open Interface tables;
And finally
• Run Oracle standard Open Interface process.
The 3 main tasks can be summarised in the three following steps:
• Load data file SQL*Loader
• Perform Validations PL/SQL
• Transfer Data PL/SQL

What are User hooks?

API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance,a user may want to implement User Hooks for one of the following reasons:

 To extend the validation of data beyond what the standard system provides.
 To maintain data held in extra customer specific tables (not part of Oracle Applications) as it is entered.
User Hooks allow extra business logic to be inserted in exactly the right place in the application without needing to bespoke any of the standard APIs.
Upgrades are no problem as the hooks will be regenerated during the upgrade process. However,
Oracle reserves the right to change the HR schema at any time, which may necessitate modifications to customized PL/SQL procedures.

The main advantages of hooks over custom library are that they only need to be implemented in one place whereas custom library modifications could conceivably have to be made on several clients.
They are also immediately available to any interface that calls the API. For example, Forms, Self Service, Data Pump,etc.

3. Disadvantages
A customized procedure called by a User Hook will only work if the API that the call has been added to is executed. It sounds obvious.

However, at the time of writing there are still a large number of forms that don't call APIs. The Enter Person Information form is one of them.
Therefore, any hook added to the Create Employee API will not be executed from this form.
It is policy to eventually convert all existing forms to call APIs but this will not be a short term process.

Unfortunately, there is no list available which indicates which forms call APIs and which do not. The only certainty is that all new forms will call APIs.

A sensible approach to any implementation of configuration logic, therefore, if required to be executed by a standard form, is for the user to use a hook if the form calls a supported API, and custom library if not.

4. Implementing User Hooks
There are basically 4 steps to implementing API User Hooks.
1. Choose the API you wish to hook some extra logic to.
2. Write the PL/SQL procedure that you wish to be called by the hook.
3. Register or associate the procedure you have written with one or more specific user hooks.
4. Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook specified in 3.

Related Posts Plugin for WordPress, Blogger...