Book Image

Oracle E-Business Suite R12 Core Development and Extension Cookbook

By : Andy Penver
Book Image

Oracle E-Business Suite R12 Core Development and Extension Cookbook

By: Andy Penver

Overview of this book

Oracle's suite of applications is used by many major businesses and public sector organizations throughout the world. The book will show you how to build different types of extensions with different toolsets with Oracle E-Business Suite R12. It will take you from start to finish with fully working examples. This book will show you how to extend Oracle E-Business Suite Release 12. You will learn highly desirable skills on how to extend the application and develop your expertise. The book will provide detailed information about why things have to be done in certain ways, and will take you through the process of how to get started, what tools are needed, how to develop working examples, and how to deploy them within the application. Learn how to extend Oracle E-Business Suite (EBS) Release 12. There are detailed examples to work through, such as how various components are configured and how we can extend standard functionality. The book focuses on core development and extension and each chapter will introduce a topic before going through working examples from start to finish. There are plenty of detailed screen shots throughout each chapter giving clear instructions of what we are doing and why. Each recipe will develop a solution that will utilize core components to that topic. The Oracle E-Business Suite R12 Core Development and Extension Cookbook focuses on starting an extension right from the beginning, to deploying it within E-Business Suite. At the end of each chapter the reader will have a good understanding of what they need to do for each area to take away, and start using it in practice. Each chapter will detail how to build an extension in the supported manner and also comes with complete fully tested code, and scripts that can be downloaded.
Table of Contents (12 chapters)
Oracle E-Business Suite R12 Core Development and Extension Cookbook
About the Author
About the Reviewers


Extending Oracle E-Business Suite

This book is about extending Oracle E-Business Suite (EBS). Throughout the book there are detailed examples to work through with tips and explanations about how various components are configured and how we can extend standard functionality. The 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 core development and extension and each chapter will go through a topic from start to finish. 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 that will focus on starting an extension right from the beginning to deploying it within E-Business Suite. By the end of each chapter, the reader will have a good understanding of what they need to do for each topic to be able to take away the knowledge gained and start using it in practice. Each chapter will explain in detail how to build an extension in a supported manner. 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 and PL/SQL 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 wide range of 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 EBS 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 the behavior is through configuration. For example, extending Oracle through profile options, values sets, and Descriptive Flexfields are ways to extend apps through configuration. In addition, Oracle also provides a screen that allows us to extend Oracle core forms. This screen is known as the personalization screen and is a powerful feature that allows us to change the 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 extensions that require writing code, creating new objects, or even extending or replacing existing objects. These type 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 that Oracle EBS can be modified; 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 re-applied when a newer version of the object is released by Oracle.

Customizations by modification are not supported by Oracle and should be avoided at all costs. There are very few occasions where there is a real business justification for an intrusive customization. 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 incorrectly. 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. This is available from Oracle's website and you should pay particular attention to the concepts, user, developer and user interface guides. Another great resource is Oracle Support, 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 also 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 have been around a while, you can nearly always find examples or documentation relating to your specific requirements.

There are nearly always numerous business processes and solutions available to satisfy them. The best way to provide a solution is to have an understanding of what methods 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 which 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.

Understanding the EBS architecture

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 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 filesystem 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 a 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 browsers own JVM. The application tier (also known as the middle tier) processes all of the business logic. It comprises 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 will own the application node filesystem (the applmgr user) and the other will own the database node filesystem (the Oracle user). When installed on Windows, there is one OS user that owns the filesystem. The following diagram shows the basic architecture:

The directory structure of the Applications Tier and Database Tier will help to understand where files are stored. The following directory structure should help you understand where various files are stored within EBS:

The following table shows the directory structures of some core directories on the applications tier and the database tier:

Apps Tier







This directory is the base directory where all of the product tops are stored.



This directory is where all forms binaries 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.

DB Tier







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 and so on. 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 filesystem on the APPS tier, each product has its own filesystem. The filesystem has the same root directory as the product username in Oracle. For example, there will be a directory structure for Payables (AP) that has a root directory of AP. There is one very important schema called the APPS schema. The APPS schema owns all of the code such as packages, triggers, views, and 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. The custom schema will need to be prefixed with XX so that other developers know it's a custom schema. Usually, an onsite DBA will perform this task but we will go through this in one of the recipes we have in the final chapter.

If you would like to understand more relating to the core concepts of EBS, then you can get detailed information from the Oracle Applications Concepts Release 12 from the Release 12 Documentation Library. You can get the documentation from Oracle by following this link:

Before we start

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 a 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 through understanding the fundamentals is essential to providing the best solutions. Oracle uses many tools and technologies in its suite of applications, of which there are too many to cover in this book alone. However, I am working on a second book which will focus on Integration, OA Framework, and BI Publisher.

What this book covers

Chapter 1, Creating Concurrent Programs, looks at some recipes relating to concurrent programs. Essentially, a concurrent program is used to launch a program. The program could be a PL/SQL package, a report, a host script, or a Java program, for example. The chapter will detail the full list of programs that can be launched. The recipes will focus on creating concurrent programs and defining parameters. They will also show how we can run, schedule, and output data throughout a concurrent program lifecycle. At the end of this chapter, we will have built a concurrent program that runs a PL/SQL program and a host script that sends an e-mail. We will have passed parameters to our executable and written to both output files and log files. We will also schedule the concurrent program and create a request set that allows us to run multiple programs together.

Chapter 2, Oracle Forms, will include developing an Oracle form. Forms have been used in Oracle Applications right from the original release. It started out with forms 2.0, which was character based. Then came forms 4.5 and with it a richer user interface that included widgets such as buttons and toolbars. It is now up to version 10 in Release 12. At the end of the chapter, readers will have developed a form and deployed it within EBS. The form will be built using Oracle Developer 10g and will use the template form that Oracle provides for developers to use. We will go through the changes we need to make to the template form, and develop a form that we can implement for our scenario. The form will have a master-detail relationship. The objects we create will use Oracle's property classes to look and feel like all other professional forms. We will look at the built-in functions that Oracle provides and why we use them instead of the standard forms developer built-in functions. We will also add a number of triggers to the form which will perform actions such as setting the standard WHO fields, inserting update, and deleting records. We will deploy the form in Oracle EBS showing how we transfer and compile the form and how we configure the form and function definitions in EBS.

Chapter 3, Advanced Oracle Forms, looks at implementing more advanced Oracle EBS features such as a query entry screen, messages, tabs, and Descriptive Flexfields. The form will also have a number of widgets such as the standard date lookup and we will also create our own lookup using a query from a record group. We will also show how we can set up a zoom to the form from a standard form. We will also show how we can pass parameters to our form that will allow us to automatically query data upon opening it. As we develop the form we will deploy and test each of the features we add and at the end of the chapters on forms we will have a completed form deployed within the application. The form will have implemented many of the features we would normally require in our custom forms.

Chapter 4, Forms Personalization, discusses configuring forms personalization. Traditionally, changes made to Oracle EBS professional forms were made using the custom library, which requires technical knowledge. However, since version 11.5.10, Oracle has introduced forms personalization as a way of immediately changing the behavior of Oracle standard forms through configuration. Personalization are protected and are generally supported when patches are applied. As they change the behavior of a standard form it is important to re-test any personalization after any patches are applied. This is a must if the patch has introduced new versions of any forms you have personalized. We will demonstrate that to implement personalization you do not have to be technical but a basic understanding of Oracle Forms will help. You can perform some complex functionality through personalization as well but you would need to have a more technical background for this.

At the end of the personalization chapter we will have been through several examples. We will change properties that rename item labels, move items around, disable fields, and rename tab labels. We will also perform some more advanced tasks than just changing properties. We will add a new menu item to launch a form and we will perform validation by calling a database package. We will display user messages to make the user interface functionality more specific to business terminology and processes. We will use personalization to perform activities we used to do in the custom library.

Chapter 5, Workflow, looks at developing and deploying and Oracle workflow within EBS. Oracle Workflow is heavily integrated with Oracle EBS to automate and integrate business processes. Oracle supports workflow extensions as long as they are done in a supported manner. The tool that is used is Oracle Workflow Builder, which is a GUI interface that allows us to drag and drop objects to build or modify processes. At the end of this chapter, users will have a good understanding of how to create a new workflow and deploy it in EBS. We will create functions that call database packages and look at some of the PL/SQL functions used when developing workflows. We will examine the statuses returned back to the workflow from our code and perform activities based upon the responses. We will also create our own lookups and show how they are used within the code and how they are mapped in a process diagram. We will learn a little about advanced queues and how we can create our own queues and trigger events that we can subscribe to. We will build sub processes and add activities such as sending notifications that require responses. We will have created several attributes and store information in the attributes. We will also show how we can monitor and test workflow processes in EBS. Once we have completed the workflow we will examine the role of the workflow background engine and how we deploy our workflow.

Chapter 6, Utilities, looks at a number of utilities that you will find extremely useful when moving extensions from one environment to another. 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. There are a number of utilities Oracle provides to make life easier for us. These are used to upload, extract, and bulk load data among other things. It is important to remember that Oracle has spent years developing its applications and there are a great many reusable objects and it is important to use them wherever possible.

What you need for this book

Oracle uses a great many tools to develop extensions. The tools we will mostly focus on in this book are as follows:

  • Access to an EBS R12 environment

  • Oracle Forms Developer

  • SQL Developer

  • Oracle Workflow Builder

Other utility programs we will be using are:

  • WinSCP

  • Putty

  • Text Editor

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

  • SQL

  • Unix shell scripting

Other languages such as Java, Pro*C, and Pro*COBOL are also used within EBS but fall outside the scope of this book.

Who this book is for

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 detail 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: "The message will get sent to the e-mail address of the employee linked to a role in the WF_ROLES table."

A block of code is set as follows:

IF :per_societies.subs_hold = ‘Y' THEN

Any command-line input or output is written as follows:

WFLOAD apps/<password> 0 Y FORCE $XXHR_TOP/install/ch5/XXHRIABS.wft

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: "clicking the Next button moves you to the next screen".


Warnings or important notes appear in a box like this.


Tips and tricks appear like this.

Reader feedback

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 , and mention the book title through 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

Customer support

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.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit 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, 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 to our website, or added to any list of existing errata, under the Errata section of that title.


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 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 if you are having a problem with any aspect of the book, and we will do our best to address it.