This book is about the ways in which we can extend Oracle E-Business Suite (EBS) and focuses on more recent tools and technology. Its primary focus is to show how we can integrate with EBS, personalize and develop OA Framework pages with EBS, and to show how we can use BI Publisher to create and mail merge documents within EBS. The book has many detailed examples to work through with tips and explanations about how various components can be configured and how we can extend standard functionality and the various ways in which we can do it. It complements my first book, Oracle E-Business Suite R12 Core Development and Extension Cookbook, which focuses on writing concurrent programs, the personalization and development of professional forms, creating workflows, and using common utilities and scripts within EBS.
This book is aimed at developers who are new to E-Business Suite, or those who are strong in one particular area and need to expand their knowledge in other areas. Experienced developers may also use the book to brush up on their skills, or to pick up tips that may help them. The book focuses on the newer technologies being used in the more recent releases of EBS. There are plenty of detailed screenshots throughout each chapter giving clear instructions. This helps to provide a clear and full understanding of what we are doing and why. Each topic will develop a solution to a scenario, and will show how we need to set up our development tools right through to deploying it within EBS.
At the end of each chapter, the reader should have developed a good understanding of the topic and will be able to take away the knowledge gained and start using it in practice. The book also comes with complete, fully-tested code and scripts that can be downloaded. The examples have been developed using a Vision instance of Oracle E-Business Suite Release 12 (12.1.1). It would be helpful to know some SQL, PL/SQL, XML, and Java, but it is not essential, as the code is already written and fully documented. We will be going through each chapter example step-by-step, so you will not be expected to write any code that is not detailed in the book.
If you are new to Oracle EBS or a consultant who has worked with Oracle E-Business Suite before, you will be aware that there are many technical components to consider; there are many features using a range of different tools and the footprint seems to be expanding all the time. One of the key features of EBS is its flexibility. Not all organizations are the same and there are many ways you can configure EBS to make it specific to an organization’s needs. Whenever possible, an organization should always attempt to use standard functionality to meet their requirements. However, there are occasions where business requirements cannot be met using the standard Oracle-provided functionality, and this can be anything from renaming a label on a screen, to automating a process that would take many hours for someone to process manually.
There are various ways in which you can change behavior in Oracle EBS. The simplest way to change behavior is through configuration. For example, extending Oracle through profile options, value sets, descriptive flexfields are ways to extend applications through configuration. In addition, Oracle also gives us the ability to change Oracle OA Framework pages through personalization. It is a powerful feature that allows us to change behavior without needing to change any standard objects. These are mechanisms provided by Oracle to change how the application looks, or how the application behaves. On the other hand, there are some extensions that require writing code, creating new objects, or even extending or replacing existing objects. These types of extensions are in addition to the code or objects that Oracle delivers.
A powerful feature of Oracle is the ability to extend EBS, but when doing this, there is a strict set of development standards that must be adhered to. There are two primary ways to modify Oracle EBS; the first is customization by extension, as we have just described, and the second is customization by modification. Customization by modification is where standard objects are changed, meaning that the change needs to be reapplied when a newer version of the object is released by Oracle.
Customizations by modification are not supported by Oracle and should always be avoided. There are rarely occasions where there is a real business justification for a customization by modification. There are often alternatives, such as exploring other solutions or looking at ways to change the requirements or processes. It is important to understand the difference between customization by extension and customization by modification, as the two terms are often used loosely. When we extend Oracle EBS by supported methods, the standard functionality is still supported by Oracle as it has not been amended by the extension, whereas customization by modifying a standard object means the standard object also becomes unsupported by them.
A worthwhile feature of Oracle EBS is that it uses a common toolset and also has a vast amount of documentation written about the supported ways in which you can extend the product. The first place to start would be the Oracle Release 12 Documentation Library. This provides many documents, all relating to Oracle EBS. Another essential resource is Oracle Support (previously known as Metalink), which is a portal provided by Oracle for support, documentation, white papers, and patches among many other things related to EBS. To access the portal, you need to register and you also need to provide a support identifier, which is only provided when there is a support contract with Oracle Support. So, if you are new to EBS or if you have been around for a while, you can nearly always find examples or documentation relating to your specific requirement. There are nearly always numerous business processes and solutions available to satisfy it. The best way to provide a solution is to have an understanding of what options are available. Having knowledge of the various ways in which we can extend EBS will give us a much better chance of coming up with better solutions. This book will provide recipes that will cover some simple and some more complex solutions. It will utilize a majority of the Oracle toolset and will, hopefully, broaden your knowledge. Expanding our knowledge of the toolset will allow us to provide a more varied set of solutions, resulting in having a better chance of providing a better, more robust solution for a given problem.
Before we really get into the book it is important to understand the E-Business Suite architecture in Release 12. We need to understand where the files are kept and how it is installed. If you are a beginner, you will need this information to understand some key concepts and pick up some terminology that is often used when we discuss Oracle EBS.
In Release 12, there has been a significant change in the file system when it is installed. The change quite simply is used to segregate code, data, and configuration. This makes maintenance much easier. There will be a number of terms that you will encounter regularly when we discuss EBS. A server is a term for a number of processes that provide specific functionality on a single machine. A tier is a term used to describe a logical group of services, which can be on one or more physical machine. A machine is used to describe a computer or group of computers. A node is a group of computers that work closely together as a cluster.
Essentially, there are three tiers per instance of EBS. The Database Tier (DB Tier), the Application Tier (APPS Tier), and the Desktop Tier. The desktop tier is the client interface where users will connect to Oracle EBS through a web browser. When Oracle is first used, the browser will install a J2SE plugin which will use Oracle’s own Java Virtual Machine (JVM) rather than the browser's own JVM. The application tier (also known as the middle tier) processes all of the business logic. It comprises of three servers or service groups. These are Web Services which process requests from the desktop client. Then there are the Forms services which manage all of the listening and secure requests for Oracle forms. Then there is the concurrent processing server, which processes concurrent requests that are submitted.
When Oracle EBS is installed on all variants of UNIX, the install is performed by the root user. However, as part of the installation process there will be two OS users. One that will own the application node file system (the applmgr user) and the other will own the database node file system (the oracle user). When installed on Windows, there is one OS user that owns the file system. The following diagram shows the basic architecture:
The directory structure of the Application Tier and Database Tier will help to understand where the files are stored. The following directory structure should help to understand where various files are stored within EBS:
The following shows the directory structures of some core directories on the Applications Tier:
|
Profile |
Directory |
Description |
|---|---|---|
|
|
|
This directory is the base directory where all of the product tops are stored. |
|
|
|
This directory is where all forms are stored. |
|
|
|
This directory is where all libraries are stored. |
|
|
|
This is the base directory for scripts, log files, output files, and HTML files. |
|
|
|
This is the directory structure for the forms' server files. |
|
|
|
This is the directory structure for the web server files. |
|
|
|
This is the top directory for the configuration files. |
|
|
|
This is where admin scripts are stored to start and stop services. |
The following table describes where the files are stored on the Database Tier:
|
Profile |
Directory |
Description |
|---|---|---|
|
|
|
This directory is the base directory where all of the database files are stored. |
|
Data Files |
|
This directory is the base directory where all of the data files are stored. |
Within EBS, each product has its own database user within Oracle. The user will own the objects that store data, such as tables. This is in the form of an abbreviation of the product (AP for Account Payables and HR for Human Resources for example) and is referred to as a schema. The passwords are, by default, the same as the schema name, but this is nearly always changed after install. On the file system on the APPS tier, each product has its own file system. The file system has the same root directory as the product \ user name in Oracle. For example, there will be a directory structure for Payables (AP) that has a root directory of AP_TOP. There is one very important schema called the APPS schema. The apps schema owns all of the code, such as packages, triggers, views, java classes. Each product user has grants providing access to the APPS schema for all of its objects. Therefore, the APPS schema has access to all of the objects, that is the code and the objects owned by the product schemas. It is important that as a developer, you can access the APPS database schema. All objects that you will require access to are accessible from this schema. When we create custom objects, we need to create a custom schema and all of the data storing objects will reside in this schema. Usually, an on-site DBA will perform this task, but we will go through this in one of the recipes we have.
If you would like to understand more relating to the core concepts of EBS, you can get more detailed information from the Oracle Applications Concepts Release 12 from the Release12 Documentation Library. You can get the documentation from Oracle by following this link http://docs.oracle.com/cd/B53825_08/current/html/docset.html.
One final word before we get into the recipes. Hopefully, you will find the book extremely useful. There are thousands of pages written about how to extend and customize Oracle E-Business Suite. This book is not going to cover every solution to every problem. In fact, it will not even come close. The recipes are designed to take us through the different features of the tools and extensions that you may need to utilize in your own solutions. Use the book to get a feel for how each tool and product can be used. It will provide a core understanding of how things can be done and the standards that we need to adhere to. You will need to expand on what you learn here and apply the knowledge you gain to design a solution or solutions to your own scenarios. I personally am a great believer of learning through practice. Expanding your knowledge by understanding the fundamentals is essential to providing the best solutions. Oracle uses many tools and technologies in its suite of applications, of which there is too much to cover in this book.
Chapter 1, Personalizing OA Framework Pages, looks at some recipes that will implement personalizations to an OA framework page. We will look at a number of different types of personalization and the levels at which we can create personalizations. At the end of the chapter, readers will have an understanding of how we can personalize OA framework pages in EBS. Readers will understand which responsibilities are used to administer personalizations and which profile options need to be changed to view the personalization links. Throughout the chapter, we will be creating a variety of different examples that will give a broad understanding of what we can achieve through personalizing OA framework pages.
Chapter 2, Getting Started with OA Framework Pages, deals with the creation of OA Framework pages. We start off by looking at the architecture of OA Framework pages as it is important to understand this before we start any development. Further, we will go through the process of setting up our development environment and what we will need to do to get up and running. Towards the end of the chapter readers will have an understanding of the components required to develop and test an OA Framework page.
Chapter 3, Creating a Master Detail Page in OA Framework, will explain the process of creating a master detail page in the OA Framework. Readers will have an understanding of how we can create master detail pages in the OA framework. Finally, we will go through the process of deploying our page in Oracle E-Business Suite (EBS) and running it through the application.
Chapter 4, Adding a Creation Page and LOV Region in OA Framework, explains how to make changes to the OA Framework pages we have created. This chapter gives the readers an understanding of how they can create pages that interact with the database. We will also know how to use the debugger that is provided in JDeveloper.
Chapter 5, Advanced OA Framework, helps in learning some more advanced features of OA Framework pages which includes a lot more coding. The chapter will continue to develop the pages we have worked on so far.
Chapter 6, BI Publisher, looks at recipes that will explain how BI Publisher integrates with E-Business Suite to allow us to generate formatted data with a very feature rich output. We will look at how it integrates with commonly used desktop applications, such as Microsoft Word and Excel. At the end of the chapter, readers will understand how to set up our development environments. We will learn to create our report templates, and how to generate the XML data that the template requires to produce a report output in a number of different formats. We will look at how the report mail merges the data with Word documents, and how we can use a feature called Bursting to send formatted documents out by e-mail.
Chapter 7, Desktop Integration, looks at how we can integrate with E-Business Suite using the desktop integrator. Oracle E-Business Suite provides a framework that allows us to integrate with Microsoft Office-based tools. It is called Web ADI (Application Desktop Integrator). Users tend to be in favor of using the tools, as it integrates with the Microsoft Office tools we are all used to working with. At the end of this chapter readers will learn how we can use Excel-based spreadsheets to view and upload data to EBS. We will understand how data can be validated, and how we can include structured data entry methods, such as lists of values and drop down lists to spreadsheets. We will understand how we can create a new integrator to upload data and how to assign an integrator to a responsibility.
Chapter 8, Utilities, looks at a number of utilities that you will find extremely useful. We will show how to create a custom schema and how to configure it on EBS. We will also look at ways to use common utilities, which will reduce risk and save enormous amounts of time. Usually, the biggest reason for problems when migrating extensions or configuration is when there are human tasks. We will also look at how to start and stop an environment.
Oracle uses many great tools to develop extensions. The tools we will mostly focus on in this book are the following:
Oracle JDeveloper with OA Extension
SQL Developer
BI Publisher
Microsoft Excel
Other utility programs we will be using are:
WinSCP — a windows based FTP frontend
PuTTY — a UNIX command line window
JAD - a Java decompiler
There are a number of languages used for programming and writing scripts that Oracle supports. We are going to primarily focus on the following in this book:
PL/SQL
Java
SQL
XML
This book is written for individuals who want to learn how to develop extensions in Oracle E-Business Suite. If you are involved in development or supporting an E-Business Suite implementation, you should find this book very useful. The book gives detailed explanations, so minimal technical expertise is required. It is suitable for beginners who have little experience, or developers who may want to use the book to brush up on their skills.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: “You may also notice that there is also a ZIP file called oafch2.zip provided in the code bundle.”
A block of code is set as follows:
SELECT flv.lookup_code,
flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type = ‘XXHR_SOCIETY_LOV’Any command-line input or output is written as follows:
$JAVA_TOP/<customFolder>/oracle/apps/<ApplicationShortName>/<moduleName>/<subModuleName>/webui
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: “Obtain the version of EBS you are on. To do this, log on to EBS and click on the About this Page link.”.
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.