Oracle E-Business Suite R12 Core Development and Extension Cookbook

5 (1 reviews total)
By Andy Penver
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Creating Concurrent Programs

About 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.

Publication date:
May 2012
Publisher
Packt
Pages
480
ISBN
9781849684842

 

Chapter 1. Creating Concurrent Programs

In this chapter, we will cover the following:

  • Defining a concurrent program

  • Making a concurrent program available to a user

  • Adding a concurrent program parameter

  • Creating a value set

  • Creating dependent parameters

  • Adding messages to the concurrent program log file

  • Reporting to the concurrent program output file

  • Scheduling the concurrent program

  • Creating multiple concurrent programs

  • Creating request sets

  • Installing the database objects

  • Creating a HOST concurrent program

Introduction

Concurrent programs are commonly used within Oracle E-Business Suite as a mechanism to run an executable. Concurrent programs allow users to pass parameters to the executable, which enables it to behave in different ways. We are going to go through some recipes configuring and running concurrent programs. We are also going to show how you can view and write to log files and output files to record what is happening whilst the program is running. Before we get into that, there is a bit of important background knowledge that needs to be highlighted.

Firstly, in release 12, there are additional options when defining a concurrent program to integrate with business events. You can now raise an event at various points throughout the concurrent program lifecycle. This is an extremely useful feature for extending e-Business Suite. For example, developers can subscribe to events and from the event we can launch workflows, send messages to an advanced queue, or launch PL/SQL procedures.

There are some basic concepts that need to be explained before we get into creating concurrent programs and such like. In Oracle EBS, there are users defined on the system and that's how we gain access to the application. There are some pre-configured users such as sysadmin and we can also create our own users each time we want to provide access to someone else on the system. Users are assigned one or more responsibilities and these determine the access we have to the system in terms of the forms we can go into, the programs we can run, and the data we can see. A responsibility will have a menu associated with it which will define the forms that can be accessed. It will also have a request group associated with it which will determine which concurrent programs the responsibility can execute. This is a very basic overview and you are probably already familiar with these concepts. However, if you want to understand more then I suggest you do some more reading of the System Administration guides from the Oracle Release 12 Documentation Library portal available on the Internet.

A few other important points to note are as follows:

  • The responsibility that you will need to configure nearly everything relating to a concurrent program is Application Developer. The only thing you cannot do with this responsibility is give access to users. For this you will need to use the System Administrator responsibility. (Assign these responsibilities to your user if you do not have them already.)

  • You can launch concurrent programs through an online request, for it to be scheduled automatically or to be triggered programmatically by using Oracle's built-in APIs.

  • Concurrent programs can also be configured so that they can be made incompatible with other concurrent programs meaning that they cannot run simultaneously.

  • You can also group concurrent programs together and form a request set. This feature is useful if you need to create concurrent programs that are linked in some way but need to be run in a certain manner.

  • Concurrent programs can also be scheduled to run on a certain date or to be run on an ongoing basis indefinitely.

We are now ready to get started and in this chapter we will create a concurrent program that launches a PL/SQL procedure. We will look at the parameters that are used internally and we will also add our own parameters using value sets and a dependent value set. In addition, we will write to the log and output files to show runtime information used for logging and reporting. We will schedule the concurrent programs to run automatically at specified time intervals and will look at creating multiple concurrent programs and run them together as a request set. At the end of the chapter we will look at creating a different type of concurrent program.

 

Introduction


Concurrent programs are commonly used within Oracle E-Business Suite as a mechanism to run an executable. Concurrent programs allow users to pass parameters to the executable, which enables it to behave in different ways. We are going to go through some recipes configuring and running concurrent programs. We are also going to show how you can view and write to log files and output files to record what is happening whilst the program is running. Before we get into that, there is a bit of important background knowledge that needs to be highlighted.

Firstly, in release 12, there are additional options when defining a concurrent program to integrate with business events. You can now raise an event at various points throughout the concurrent program lifecycle. This is an extremely useful feature for extending e-Business Suite. For example, developers can subscribe to events and from the event we can launch workflows, send messages to an advanced queue, or launch PL/SQL procedures.

There are some basic concepts that need to be explained before we get into creating concurrent programs and such like. In Oracle EBS, there are users defined on the system and that's how we gain access to the application. There are some pre-configured users such as sysadmin and we can also create our own users each time we want to provide access to someone else on the system. Users are assigned one or more responsibilities and these determine the access we have to the system in terms of the forms we can go into, the programs we can run, and the data we can see. A responsibility will have a menu associated with it which will define the forms that can be accessed. It will also have a request group associated with it which will determine which concurrent programs the responsibility can execute. This is a very basic overview and you are probably already familiar with these concepts. However, if you want to understand more then I suggest you do some more reading of the System Administration guides from the Oracle Release 12 Documentation Library portal available on the Internet.

A few other important points to note are as follows:

  • The responsibility that you will need to configure nearly everything relating to a concurrent program is Application Developer. The only thing you cannot do with this responsibility is give access to users. For this you will need to use the System Administrator responsibility. (Assign these responsibilities to your user if you do not have them already.)

  • You can launch concurrent programs through an online request, for it to be scheduled automatically or to be triggered programmatically by using Oracle's built-in APIs.

  • Concurrent programs can also be configured so that they can be made incompatible with other concurrent programs meaning that they cannot run simultaneously.

  • You can also group concurrent programs together and form a request set. This feature is useful if you need to create concurrent programs that are linked in some way but need to be run in a certain manner.

  • Concurrent programs can also be scheduled to run on a certain date or to be run on an ongoing basis indefinitely.

We are now ready to get started and in this chapter we will create a concurrent program that launches a PL/SQL procedure. We will look at the parameters that are used internally and we will also add our own parameters using value sets and a dependent value set. In addition, we will write to the log and output files to show runtime information used for logging and reporting. We will schedule the concurrent programs to run automatically at specified time intervals and will look at creating multiple concurrent programs and run them together as a request set. At the end of the chapter we will look at creating a different type of concurrent program.

 

Defining a concurrent program


In our first recipe, we are going to create an executable and then define a concurrent program that launches the executable. The executable in this example is a PL/SQL package that we want to run. We will start off with a little introduction and look at the types of concurrent programs we can create, the location of executable files on the server, and how to find the output and log files. We will then register a custom application which we need to register our executable and concurrent programs with. Therefore, this recipe will comprise of the following:

  • Introducing concurrent programs

  • Register a custom application

  • Configure an executable

  • Configure a concurrent program

We do not need any additional development tools to configure a concurrent program. However, the executable that we will be launching will have been developed using the appropriate development tool — Reports Developer, SQL Developer, Java, and so on.

Introducing concurrent programs

There are a number of different types of executables that can be triggered using concurrent programs. We are going to briefly discuss each one as a bit of background before we get started.

Types of concurrent programs

The different types of concurrent programs that we can create are as follows:

  • Oracle Reports: This option is, like the name suggests, used for launching Oracle reports developed using reports builder. It is quite common to have requirements to extend an Oracle standard report. There are a number of options when configuring a concurrent program that specifically relate to Oracle Reports, such as the output format, saving and printing options, columns/rows, and style.

  • PL/SQL stored procedures: These types of concurrent programs call a database stored procedure. There are two mandatory parameters when calling procedures from a concurrent program, which are errbuf and retcode. The errbuf parameter is used to return error messages. The retcode is used to return the status of the concurrent program. PL/SQL procedures are stored on the database in the apps schema so this is where Oracle will look for the stored procedure at runtime. The recipes in this chapter have examples of PL/SQL concurrent programs so we will explain this in detail throughout the chapter.

  • Host script: This is a program that is used to launch a shell script and is commonly used to perform operating system actions such as copying or moving files. Some important points to note are that the program needs to be put in the appropriate BIN ($PROD_TOP/bin) directory. Oracle, by default, uses the first four parameters so any parameters used in the shell script will need to start with the fifth parameter ($5, $6 ... , and so on). We will go through this in more detail later in the chapter as we will be creating a concurrent program calling a host file in one of the examples.

  • Immediate: This is for backward compatibility only and was used to launch a subroutine. It is now recommended to use a PL/SQL Procedure or spawned process.

  • Java stored procedures: As the name suggests, these are executables written using Java. A concurrent program is then defined to execute the code. When interacting with the database, PL/SQL Procedures are still the best way to do any processing as it is a language specifically designed to do so. You can perform many tasks using PL/SQL or Java but there are certain tasks where Java would be the better choice, especially when it comes to interacting with the operating system or third party databases.

  • Multi Language Function: The execution file is an MLS function used for running programs in multiple languages. This means that a concurrent program can be submitted multiple times, each time in a different language.

  • SQL*Loader: This is a utility to be able to load bulk data into Oracle E-Business Suite. It uses a data file and a control file. The data file is the data and the control file is the definition of the fields in the data file. There is a third file which is the parameter file, which is also used to pass any additional parameters when processing begins. There are three files produced when processing completes, a log file, a discard file, and a bad file. The log file provides information about the execution of the load. The bad file is written to if there are any records that are rejected throughout the execution of the load. The discard file is written to when there are any discarded records during the load execution, for example, when selectively loading rows.

  • SQL*Plus: This is used to run an SQL*Plus script or anonymous block. It will get executed as if you were running the script on the command line through SQL*Plus.

  • Spawned: This is a program that is usually run on the operating system, such as C or Pro*C.

  • Perl Concurrent Program: This is used for executing programs written in CGI Perl. CGI stands for Common Gateway Interface and Perl is the most common language for writing CGI scripts. It is used for scripting, programming web interfaces/development, and is great for parsing.

Executable file location

It is worth noting that at runtime the executable will need to be placed at a specific location. Files are stored in the $PRODUCT_TOP of the application they are registered with. Under this directory, they will be in the directory related to the type of program it is. The following are a few examples of where executables will need to be stored:

  • A spawned program registered in the Payables application would be stored in the $AP_TOP/bin directory.

  • A host program registered in the Receivables application would be stored in the $AR_TOP/bin directory.

  • An SQL*Plus script registered in a custom application would be stored in the custom application top directory, for example, the $XXHR_TOP/sql directory. If you are unfamiliar with the custom top discussed here then do not worry as we will discuss this throughout the book.

  • A report that was registered in the Inventory application would be stored in the $INV_TOP/Reports/Lang directory.

Output and log files

Throughout the chapter we will be looking at the output and log files generated by Oracle EBS when we run a concurrent program. These files are produced during execution and we can write log messages or output messages throughout processing. The output file is used to produce output for the user to see what has been processed and the log file is used to write more technical or debugging messages, which may not necessarily be understood by the end user. An additional way to debug is to use APIs to write to the FND_LOG_MESSAGES table.

The location of log files can be found by querying the FND_CONCURRENT_REQUESTS table. The two columns you are looking for are logfile_name and outfile_name:

SELECT logfile_name, outfile_name
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = <request_id>

The results of this query will give you the location of the output files for a given request_id. Alternatively, you can search for the files manually in the directory listed in the UTL_FILE_DIR parameter in the init.ora file. The log file will start with an l and the output file with an o. Both will be followed by the request_id and both will have a .req file extension.

Note

Downloading the example code

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.

Register a custom application

We must register a new application in Oracle E-Business Suite to register our custom configuration with. A new application allows us to isolate custom code and/or configuration from the standard Oracle Applications code and configuration. When we define a custom application, we register the custom application name, application short name, application basepath, and application description with the Oracle Application Object Library. This is used to configure custom application objects such as responsibilities and forms as belonging to our custom application. This identification with the custom application allows any extensions to be preserved during upgrades. The application basepath is an operating system profile variable that points to the directories where the application object library will look to find the files associated with the custom application. At this point we are just going to configure our custom application. We will create the file structure and create a custom schema later in the book.

How to do it...

To register an application, perform the following steps:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Application | Register and the Applications window will open as shown in the following screenshot:

  3. 3. Enter a new record with the following data:

    Application:

    XXHR Custom Application

    Short Name :

    XXHR

    Basepath :

    XXHR_TOP

    Description :

    XXHR Custom Application

  4. 4. Save the record.

How it works

We have now registered a custom application called XXHR Custom Application. We can see that the application short name and the base path are defined here.

Configure an executable

We will now configure the executable that our concurrent program will execute at runtime.

Getting ready

The responsibility we need to configure the concurrent program is Application Developer. The code for the following recipe is available in the following files: XXHREEBS_1_1.pks (specification) and XXHREEBS_1_1.pkb (body). Follow the readme_1_1.txt to install this package.

How to do it...

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Executable and the Concurrent Program Executable window will open, as shown in the following screenshot:

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Executable

    XXHR1001

    Short Name

    XXHR1001

    Application

    XXHR Custom Application

    Description

    Executable created for concurrent program recipe

    Execution Method

    PL/SQL Stored Procedure

    Execution File Name

    xxhreebs.first_concurrent_program

    Note

    Please note that any fields that are not specified in this table should be left as their default value.

  4. 4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  5. 5. Exit the form.

How it works...

We have defined an executable that will be launched by the concurrent program we are about to configure next. The executable has to be created before we can configure the concurrent program. This executable is calling a database package which at present has very little in it. When calling a PL/SQL package from a concurrent program there are two mandatory parameters. These are errbuf and retcode. We must return a value that represents a completion status and this is done by assigning a value to the retcode parameter. If we look at the package specification we can see that there are three constant variables defined, called SUCCESS, WARNING, and FAILED. These are set to 0, 1, and 2 respectively and we will assign a constant variable to the retcode out parameter to return a value to the concurrent manager upon completion. Valid return values for the retcode parameter are as follows:

0 - Success
1 - Success & warning
2 - Error

We have assigned constant variables to make the code easier to read. The other parameter that is returned is the errbuf parameter. We can assign text to this parameter so that we can return error messages if any occur at runtime back to the concurrent program, so it will be displayed in the log file.

Configure a concurrent program

In this recipe, we will configure our first concurrent program. The concurrent program will run the executable that we have just defined.

How to do it...

  1. 1. Log in to Oracle and select the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open, as shown in the following screenshot:

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Program

    XXHR First Concurrent Program

    Short Name

    XXHR_FIRST_CONC_PROG

    Application

    XXHR Custom Application

    Description

    XXHR First Concurrent Program

    Executable Name

    XXHR1001

    Note

    Please note that any fields that are not defined in this table should be left as their default value.

  4. 4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  5. 5. Exit the form.

How it works...

So now we have configured the executable and also defined the concurrent program that launches the executable. These are the basic steps required to configure a concurrent program. As you will see there are a number of other regions on the screen and some buttons, and we will be looking at some of these later in the chapter. The next step is to run the concurrent program.

 

Making a concurrent program available to a user


We have created our first concurrent program but now we want to run it. That is exactly what we are going to do in this recipe. However, before we can run it we need to do some configuration. The concurrent program needs to be assigned to a request group and the request group needs to be assigned to a responsibility. The responsibility will have a menu that calls the concurrent request functions. So we are going to perform the following tasks so that we can access and run our concurrent program:

  • Configure a menu

  • Register a custom application

  • Create a new request group

  • Create a new responsibility

  • Assign the responsibility to a user

  • Run the concurrent program

  • View the request

Note

Please note that it may well be the case that there is an existing responsibility, menu, and request group already defined.

Configure a menu

This recipe will configure a menu which will be attached to the new responsibility we are going to create. This will determine the concurrent programs and forms we will be able to access.

How to do it...

To create a menu, perform the following steps:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Application | Menu and the Menus window will open, as shown in the following screenshot:

  3. 3. Enter data as shown in the following table for the master record:

    Item name

    Item value

    Menu

    XXHR_TEST_MENU

    User Menu Name

    Test Menu

    Menu Type

    Standard

    Description

    Test Menu

  4. 4. Enter data as shown in the following table for the detail records:

    Seq

    Prompt

    Submenu

    Function

    Description

    Grant

    10

    View Requests

     

    View All Concurrent Requests

    View Requests

     

    20

    Submit Requests

     

    Requests: Submit

    Submit Requests

     
  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

    Note

    Please note that the submenu item allows us to inherit existing menus. In this case, Oracle has already built a generic menu that we can add to our menu called Requests Menu - Other Responsibilities.

  6. 6. Exit the form.

How it works...

The menu is what a user will see in the navigator when they are assigned a responsibility that has our menu assigned to it. More specifically the user will see the Prompt value which when selected will launch the function assigned to it. The menu can also be assigned a submenu. If you add a submenu the whole menu will be inherited and any functions it contains. We have created a simple menu that has the standard concurrent request functions added to it, so that we can run and view our concurrent program.

There's more...

We can see the menu structure that we have created and how it may look to the user.

Viewing a menu structure

If after we have saved the menu we wish to see how it will look, we can click on the View Tree button from the Menus screen. It will open a new window which will show us the menu we have just created. The following screenshot shows what we have just created (the menu has been fully expanded):

Create a new request group

When we define a responsibility we can also assign a request group to it. A request group is a list of concurrent programs or request sets that a responsibility will see when they run a concurrent request. We are going to add a request group that will have our concurrent program in it.

How to do it...

To create a request group perform the following:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open as shown in the following screenshot:

  3. 3. Enter data as shown in the following table for the master record:

    Item name

    Item value

    Group

    XXHR Request Group

    Application

    XXHR Custom Application

    Code

    XXHR_REQUEST_GROUP

    Description

    XXHR Request Group

  4. 4. Now we are going to add the concurrent program we created in the Defining a concurrent program recipe. Navigate to the Requests region and enter data as shown in the following table for the detail record:

    Type

    Name

    Application

    Program

    XXHR First Concurrent Program

    XXHR Custom Application

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now created a request group that contains our concurrent program. When we assign the request group to our responsibility the concurrent program will appear as a list of concurrent programs when we want to run a request. The responsibility only has access to programs in the request set assigned to it.

Create a new responsibility

Now to create our new responsibility that will run the concurrent program.

How to do it...

Perform the following steps to create a new responsibility called XXEBS Extending e-Business Suite:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | Responsibility | Define and the Responsibilities window will open as shown in the following screenshot:

  3. 3. Enter data as shown in the following table for the master record:

    Item name

    Item value

    Responsibility Name

    XXEBS Extending e-Business Suite

    Application

    XXHR Custom Application

    Responsibility Key

    XXEBSEEBS

    Description

    XXEBS Extending e-Business Suite

    Data Group: Name

    Standard

    Application

    XXHR Custom Application

    Menu

    Test Menu

    Request Group: Name

    XXHR Request Group

  4. 4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  5. 5. Exit the form.

How it works...

We have now created a responsibility that has the menu we created earlier and our request group assigned to it.

Assign the responsibility to a user

Now we are going to create a user and assign the responsibility we created to the user.

How to do it...

To create a new user, perform the following steps:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | User | Define and the Users window will open, as shown in the following screenshot:

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    User Name

    TEST01

    Password

    <enter your password>

    Description

    TEST01 User

    Password Expiration

    <None>

    Note

    Please note that when you enter a password and press the Tab key the cursor will remain in the password field as you have to enter the password again.

  4. 4. In the Direct Responsibilities tab, add XXEBS Extending e-Business Suite to the responsibility field.

    Note

    You can also just type the first few characters and then the Tab button. If there is more than one record then a list of values will appear. In this case, you could have typed XXEBS and then the Tab key.

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now created a user so that we have access to the responsibility we created. If you already have a user that you have created, you can add the responsibility to that user if you prefer.

Run the concurrent program

Now we can run the concurrent program we have created.

How to do it...

To run the concurrent program, perform the following steps:

  1. 1. Log in to Oracle with the user that has the XXEBS Extending e-Business Suite responsibility assigned to it.

  2. 2. Navigate to Submit Requests | Submit and a Submit a New Request window will open, as shown in the following screenshot:

  3. 3. Select Single Request and click the OK button.

  4. 4. The Submit Request screen will open, as shown in the following screenshot:

  5. 5. Click on the Name field and select XXHR First Concurrent Program from the list of values.

  6. 6. Click on the Submit button.

  7. 7. A decision box will appear informing us that the request has been submitted.

  8. 8. Click on the No button as we do not wish to submit another request at this time.

  9. 9. Exit the form.

How it works...

The request has now been submitted. The next time the concurrent manager runs in the background it will execute the request. We now want to view the request to see if it has completed successfully.

View the request

We want to see the outcome of the concurrent request and we can do this from the menu.

How to do it...

To view the request, perform the following:

  1. 1. Navigate to View | Requests, as shown in the following screenshot:

  2. 2. The Find Requests window will appear, as shown in the following screenshot:

  3. 3. Click on the Find button in the bottom-right hand side corner.

  4. 4. The Requests window will appear and you will see the concurrent program that was executed, as shown in the following screenshot:

  5. 5. You can see that the concurrent program has been completed successfully.

    Note

    The Requests screen does not automatically refresh so you will need to click on the Refresh Data button to refresh the screen until the Phase has changed to Completed.

How it works...

We have now run the request we created. If you want to see details about the concurrent request then click on the View Details button. If you want to see any output click on the View Output button. The output file will often say there is no output as it will only display data if the executable program writes to the output file. If you wish to see log messages then click on the View Log button. Unless the executable program has written to the log file, you will see generic information about the concurrent request that is written by Oracle for every request. Developers often write information to the output and log files relating to the executable program that has been run.

See also...

Adding messages to the concurrent program log file.

Reporting to the concurrent program output file.

 

Adding a concurrent program parameter


In this recipe, we are going to add a parameter to the concurrent program XXHR First Concurrent Program. Parameters allow the users to pass values into the concurrent program. This can be used by the executable to impact the way it performs the intended task. We are going to add the parameter to the PL/SQL package and then change the return code based upon the value we pass in.

We will be adding a parameter to the concurrent program we have already created. We will also need to add the parameter to the code in the XXHREEBS package and we will use SQL Developer to make these changes. The following tasks will be performed in this recipe:

  • Adding a parameter to the concurrent program

  • Amend the XXHREEBS package to add the new parameter

  • Amend the XXHREEBS to change the completion status of the concurrent program

  • Testing the concurrent program

Adding a parameter to the concurrent program

We are now going to add a parameter to the concurrent program to pass the date on which the request was run.

How to do it...

To add a parameter, perform the following steps:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open.

  3. 3. Press the F11 key to enter a query.

  4. 4. Type XXHR First% into the Program field and press the Ctrl + F11 keys together to execute the query.

  5. 5. The concurrent program we created in an earlier recipe will be returned, as shown in the following screenshot:

  6. 6. Click on the Parameters button to open the Concurrent Program Parameters window, as shown in the following screenshot:

  7. 7. Enter the following details as per the following table:

    Item name

    Item value

    Seq

    10

    Parameter

    P_RUN_DATE

    Description

    Concurrent program run date

    Enabled

    Value Set

    FND_STANDARD_DATE

    Default Type

    Current Date

    Required

    Display

    Display Size

    11

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Run Date

    Note

    The value entered in the Parameter field is the name of the parameter we will define in the PL/SQL package in the next task.

    The value entered in the Value Set field is a pre-defined value set. It is for selecting a date and we are going to re-use this rather than create our own.

  8. 8. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  9. 9. Exit the form.

How it works...

We have now added a date parameter to the concurrent request. When we run the concurrent request, Oracle uses the records entered here to dynamically create a parameter screen. We can add parameters that are mandatory and based on a list of values if we wish. Also, we can hide a parameter to the user by checking the Display checkbox if required. We are going to add some more parameters to the concurrent program in the next few tasks but first we must add the parameter we just created to the procedure we call in the executable.

Amend the XXHREEBS package to add the new parameter

Now we are going to amend the XXHREEBS package to accept the new parameter we have configured. The parameter we configured is P_RUN_DATE.

Getting ready

We will need to use SQL Developer to amend the package so if you have not already done so, install SQL Developer. You can download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index-098778.html.

How to do it...

We are now going to amend the database package to add the parameter we have just configured, as follows:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. In the navigator, expand the Packages node and select the XXHREEBS package.

    Note

    You can add a filter on the Packages node in SQL Developer which will refresh the list much quicker. To do this highlight the Packages node and click on the filter icon. In the Filter window, select Name | Like | XXHR% in the filter criteria region and click ok.

  3. 3. The package specification will be opened in the editor as shown in the following screenshot:

  4. 4. Now edit the package specification by clicking on the package specification in the Packages node as shown in the following screenshot.

  5. 5. Scroll down the package specification code until you reach the First_Concurrent_Program procedure definition, shown as follows:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER);
    
  6. 6. Now add the parameter to the code after the first two mandatory parameters, errbuf and retcode. The program definition will look like the following:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2);
    
  7. 7. Compile the package specification by clicking the compile icon ( )in the editor toolbar.

  8. 8. The package specification will now look like the following:

  9. 9. Now we need to add the parameter to the package body, so click the open body icon as shown in the following screenshot and a new tab will open displaying the package body in the editor:

  10. 10. Scroll down to the First_Concurrent_Program procedure definition and add the p_run_date parameter to the list of parameters, shown as follows:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2) IS
    
  11. 11. Compile the First_Concurrent_Program package body.

How it works...

Now we have created a new parameter and added it to our PL/SQL package executed by our concurrent program.

Amend the XXHREEBS package to change the completion status of the concurrent program

We are now going to add some code to the package to change the return status of the concurrent program. We will return a status of success if the date parameter we enter when we run the concurrent program is equal to the current date. When the date parameter is before the current date we will return a status of warning and if the date parameter is after today's date then we will return an error status.

Getting ready

We are going to amend the XXHREEBS package body to determine the return status of the concurrent program based upon the date parameter P_RUN_DATE that is passed in. The code for the following recipe is available in the following files: XXHREEBS_1_2.pks (specification) and XXHREEBS_1_2.pkb (body). The following are the steps taken to add the code to the existing XXHREEBS package.

How to do it...

To amend the package, perform the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. Add the following variable to capture the run date passed in and convert it to a date:

    v_run_date DATE := TO_DATE(p_run_date,'YYYY/MM/DD HH24:MI:SS');
    
  4. 4. Edit the First_Concurrent_Program procedure by replacing the line of code retcode := SUCCESS; with the following code after the BEGIN statement:

    IF TRUNC(v_run_date) = TRUNC(SYSDATE) THEN
    retcode := SUCCESS;
    ELSIF TRUNC(v_run_date) < TRUNC(SYSDATE) THEN
    retcode := WARNING;
    ELSIF TRUNC(v_run_date) > TRUNC(SYSDATE) THEN
    retcode := FAILED;
    END IF;
    

    Note

    Downloading example code

    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.

  5. 5. The package body should now look similar to the following screenshot, which shows the three changes to the code we have made:

  6. 6. Compile the First_Concurrent_Program package body.

How it works...

We have edited the package to add some rules, so that the concurrent program will complete either with success, a warning, or an error depending upon the value of the date parameter we run the concurrent program with.

Testing the concurrent program

Now we want to run the concurrent program testing the logic we have added in the PL/SQL code. If the run date parameter is entered as the current date then the concurrent program will complete successfully. If the concurrent program runs with a run date prior to today then the concurrent program will complete with a warning. If the concurrent program runs with a future date then it will complete with an error.

How to do it...

To test the logic we have added, perform the following steps:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date parameter to the default date (which is the current date) and click OK.

  4. 4. Click on the Submit button and when prompted to submit a new request, select No and the form will close down.

  5. 5. Navigate to the View Requests window and click on the Find button (to find all requests).

  6. 6. You should see that the concurrent program we just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed.)

How it works...

We have tested that we have been able to add the parameter to the concurrent program. We have also tested that it still completes successfully.

There's more...

Now we want to test the other conditions so try submitting the concurrent program again but enter a date less than the current date. The program should complete with a warning. Finally, run the program again with a future date and the concurrent program should complete with an error.

 

Creating a value set


In this recipe, we are going to add a parameter that uses a value set. A value set is a list of values and they are commonly used to extend e-Business Suite and in this recipe we are going to create a value set that is a list of organizations.

We are going to perform the following tasks:

  • Create a value set

  • Create a new parameter for the concurrent program

  • Modify the executable to accept the new parameter

  • Run the concurrent program

Create a value set

We are now going to create the value set that we will use as a list of values for our next parameter. The value set will contain a list of organizations.

How to do it...

To create a value set complete the following tasks:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Application | Validation | Set, and the Values Sets window will open as shown in the following screenshot:

  3. 3. Enter the following details as per the following table:

    Item name

    Item value

    Value Set Name

    XXHR_ORG_LIST_VS

    Description

    List of HR Organisations

    List Type

    List of Values

    Security Type

    No Security

    Format Type

    Char

    Maximum Size

    20

    Validation Type

    Table

  4. 4. Click on the Edit Information button and the Validation Table Information form will appear, as shown in the following screenshot:

  5. 5. Enter the following details as per the following table:

    Item name

    Value

    Type

    Size

    Table Application

    Human Resources

      

    Table Name

    HR_ALL_ORGANIZATION_UNITS

      

    Value

    ORGANIZATION_ID

    Varchar2

    22

    Meaning

    NAME

    Varchar2

    240

    ID

    ORGANIZATION_ID

    Number

    22

    Where/Order By

    SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL (date_to, SYSDATE) ORDER BY name

      
  6. 6. Click on the Save button in the toolbar (or Ctrl + S) to save the record.

  7. 7. Click on the Test button to test the code entered.

How it works...

We have created a lookup that we can use in our concurrent program when we define the next parameter. The list will contain a list of organizations from the HR_ALL_ORGANIZATION_UNITS table.

There's more...

We are just going to explore what happens to the information we store in this screen and how it performs at runtime.

What happens to the data entered?

Essentially this screen dynamically creates an SQL statement which is used to generate the list of values. The table columns region defines the values that are visible to the user and the value that is passed to the concurrent program. The Name value is what is displayed to the user. If there is no value entered for ID then it is passed as the parameter value, otherwise the value in ID is passed. The ID field is hidden from the user. In addition, if the value set is already used, that is, already mapped to a concurrent program, it cannot be modified.

Create a new parameter for the concurrent program

We are now going to add a new parameter and use the value set we have just created in the previous recipe.

How to do it...

To create a new parameter, perform the following steps:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open.

  3. 3. Press the F11 key to enter a query.

  4. 4. Query back the XXHR First Concurrent Program concurrent program and click the parameters button.

  5. 5. Add a new parameter with the following details:

    Item name

    Value

    Seq

    20

    Parameter

    P_ORG_ID

    Description

    Organization ID

    Enabled

    Value Set

    XXHR_ORG_LIST_VS

    Default Type

     

    Required

    Display

    Display Size

    20

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Organization

  6. 6. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  7. 7. The completed screen will look like the following screenshot:

How it works...

We have added a new parameter that will be passed to our concurrent program. The parameter will use the list of organizations we created in the previous task. We must now add the parameter to the PL/SQL package, which we will do next.

Modify the executable to accept the new parameter

We are now going to add the parameter to the executable, which is the package called XXHREEBS.First_Concurrent_Program. If you wish to view the code then it can be found in the files XXHREEBS_1_3.pks and XXHREEBS_1_3.pkb. You can compile the package provided or add the parameter to the code manually below.

How to do it...

To add the parameter, take the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. In the code editor, scroll down the package specification until you reach the First_Concurrent_Program procedure definition.

  4. 4. Now add the parameter to the code AFTER the p_run_date parameter. The program definition will look like the following:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2,
    p_org_id IN VARCHAR2);
    
  5. 5. Compile the package specification and ensure it compiles without error.

  6. 6. Now we need to make the same addition to the package body. Open the package body.

  7. 7. Scroll down to the First_Concurrent_Program procedure definition and add p_org_id to the list of parameters, as shown in the following code:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2,
    p_org_id IN VARCHAR2) IS
    
  8. 8. Compile the First_Concurrent_Program package body.

  9. 9. The program specification will look like the following:

  10. 10. The program body will look like the following:

How it works...

We have now added the parameter p_org_id to the concurrent program definition and also amended the code to add the parameter to the package specification and body.

Run the concurrent program

Now we want to run the concurrent program testing that the concurrent program still runs successfully if the organization parameter is passed in.

How to do it...

To test the changes perform the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date parameter to the default date (which is the current date).

  4. 4. Select an organization from the list of values for the Organization parameter and then click OK.

  5. 5. Click on the Submit button and when prompted to submit a new request, select No and the form will close down.

  6. 6. Navigate to View Requests and click on the find button (to find all requests).

  7. 7. You should see that the concurrent program we have just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed.)

If you now look at the following screenshot at the request once it has completed, the parameters field has an extra value which is the ID (your ID may well be different to the one in the screenshot dependent upon the organization you selected) of the parameter we have added:

How it works...

We have tested that the parameter we have added has been passed to the procedure. We don't at present do anything with it but we just want to ensure that the organization identifier is being passed.

 

Creating dependent parameters


In this next recipe, we are going to create a new parameter but this time we are going to make it dependent upon the parameter we created in the previous recipe.

We will be adding a parameter to the concurrent program we have already created. We will also be looking to change the package XXHREEBS so we will need to use SQL Developer to make these changes. The following tasks will be performed in this recipe:

  • Create a dependent value set

  • Create a new parameter for the concurrent program

  • Modify the executable to accept the new parameter

  • Run the concurrent program

Create a dependent value set

We are now going to create a dependent value set. It will select a list of employees who belong to the organization chosen in the Organization parameter.

How to do it...

Perform the following steps to create the value set:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Application | Validation | Set and the Values Sets window will open.

  3. 3. Enter the details as shown in the following table:

    Item name

    value

    Value Set Name

    XXHR_PERSON_DEP_VS

    Description

    List of HR person records dependent upon organization

    List Type

    List of Values

    Security Type

    No Security

    Format Type

    Char

    Maximum Size

    20

    Validation Type

    Table

  4. 4. Click on the Edit Information button and the Validation Table Information window will open.

  5. 5. Enter the details as shown in the following table:

    Item name

    value

    Type

    Size

    Table Application

    Human Resources

      

    Table Name

    per_people_f ppf, per_assignments_f paf

      

    Value

    ppf.national_identifier

    Char

    20

    Meaning

    ppf.full_name||'- Employee Number : '||ppf.employee_number

    Varchar2

    240

    ID

    ppf.person_id

    Number

    10

    Where/Order By

    TRUNC(SYSDATE) BETWEEN ppf.effective_start_date and ppf.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date AND ppf.person_id = paf.person_id AND paf.primary_flag = 'Y'

    and paf.assignment_type = 'E' AND paf.organization_id = :$FLEX$.XXHR_ORG_LIST_VS

      

    Note

    We should just examine the syntax in the Where/Order By field. The clause joins the two tables we have defined in the table name field. The syntax I want to focus on is the following line:

    AND paf.organization_id = :$FLEX$.XXHR_ORG_LIST_VS

    The $FLEX$ indicates that we want to base this upon another value set. It is followed by the name of the value set we want this to be dependent upon. A parameter that uses the value set must exist in another parameter defined in the concurrent program.

  6. 6. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  7. 7. Click on the Test button to test the code entered.

How it works...

We have created a value set that is dependent upon another value set. We have already defined the p_org_id parameter that uses the XXHR_ORG_LIST_VS value set. When we run the concurrent program the parameter that uses this value set will appear disabled until a value has been entered in the dependent parameter.

Create a new parameter for the concurrent program

We are now going to add a new parameter called person_id and use the value set we have just created for the parameter.

How to do it...

Perform the following steps to add the new parameter:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open.

  3. 3. Press the F11 key to enter a query.

  4. 4. Query back the XXHR First Concurrent Program concurrent program and click the parameters button.

  5. 5. Add a new parameter with the following details:

    Item name

    Value

    Seq

    30

    Parameter

    P_PERSON_ID

    Description

    Person ID

    Enabled

    Value Set

    XXHR_PERSON_DEP_VS

    Default Type

     

    Required

    Display

    Display Size

    20

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Person

The parameter should now look like the following screenshot:

How it works...

We have configured the P_PERSON_ID parameter that is dependent upon the organization parameter. This can be achieved by referencing the Organization value set with the :$FLEX$.XXHR_ORG_LIST_VS syntax.

Modify the executable to accept the new parameter

We are now going to add the parameter to the executable, which is the package called XXHREEBS.First_Concurrent_Program. If you wish to view the code then it can be found in the files XXHREEBS_1_4.pks and XXHREEBS_1_4.pkb. You can compile the package provided or use the following instructions to add the parameter manually.

How to do it...

To add the new parameter to the code, perform the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. Now edit the package specification.

  4. 4. Scroll down the package specification until you reach the First_Concurrent_Program procedure definition.

  5. 5. Now add the parameter to the code AFTER the p_org_id parameter. The program definition will look like the following:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2,
    p_org_id IN VARCHAR2,
    p_person_id IN NUMBER);
    
  6. 6. Compile the package specification.

  7. 7. Open the package body to add the p_person_id parameter to the body.

  8. 8. Scroll down to the First_Concurrent_Program procedure definition and add p_person_id to the list of parameters, as shown in the following code:

    PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    p_run_date IN VARCHAR2,
    p_org_id IN VARCHAR2,
    p_person_id IN NUMBER) IS
    
  9. 9. Compile the First_Concurrent_Program package body.

How it works...

We have added the new parameter to the package specification and body. When we run the concurrent program the Person parameter will be dependent upon the organization that is selected.

Run the concurrent program

Now we want to run the concurrent program testing that the concurrent program still runs successfully if the person_id parameter is passed in.

How to do it...

To run the concurrent program, perform the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date parameter set to the default date (which is the current date).

    Note

    You will notice that the Person parameter is disabled. This is because it is dependent upon the Organization field and will remain disabled until an organization has been entered.

  4. 4. Select an organization from the list of values for the Organization parameter and then click OK. (If using a Vision instance select 'Vision Corporation', for example.)

  5. 5. Select an employee record from the list of values for the Person parameter and then click OK.

  6. 6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  7. 7. Navigate to View Requests and click on the find button (to find all requests).

  8. 8. You should see that the concurrent program we just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed).

How it works...

We have now added the dependent parameter to the concurrent program. This allows us to control values that are entered by the users.

 

Adding messages to the concurrent program log file


In this recipe, we are going to write to the log file to show the values of parameters that are passed into the executable. This is useful as we need a way of debugging and logging activity when a program is being executed. We can also write other messages to the log file of the concurrent program to report activity when the executable runs.

The tasks we are going to perform in this recipe are as follows:

  • Create a profile option to turn logging on or off

  • Set the profile option value

  • Add a procedure to write to the log file

  • Writing to the log file

  • Running the concurrent program

  • Viewing the log file

Create a profile option to turn logging on or off

Firstly we are going to create a profile option. This profile option is a simple profile that can be set to 'Y' or 'N'. If it is set to 'Y' then we can use it to write to the log file and if it is set to 'N' then we can choose not to write to the log file.

How to do it...

To create a new profile, perform the following:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Profile and the Profiles window will open, as shown in the following screenshot:

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Name

    XXHR_WRITE_LOGFILE

    Application

    XXHR Custom Application

    User Profile Name

    XXHR Write to Concurrent Program Logfile

    Description

    XXHR Write to Concurrent Program Logfile

    SQL Validation

    SQL="SELECT MEANING \"Yes or No\", LOOKUP_CODE

    INTO :visible_option_value,

    :profile_option_value

    FROM fnd_common_lookups

    WHERE lookup_type = 'YES_NO'"

    COLUMN="\"Yes or No\"(*)"

  4. 4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

How it works...

We have created a profile which will allow users to turn logging information on or off. Profile options can be a really useful way of setting values in our code without having to hard code.

There's more...

There are often a number of ways to implement extensions or ways of doing things. As an example we could have created an additional parameter that uses a Yes/No value set that allows the user to write to the log file at runtime by setting a parameter value. It really depends upon how you want the program to run.

Set the profile option value

We will now set the profile to 'Yes' so that we can add code to our program that writes information to a log file.

How to do it...

To set the profile option value, perform the following:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Profile | System and the Find System Profile Values window will open as shown in the following screenshot:

  3. 3. In the Profile field type XXHR Write to Concurrent Program Logfile and click the Find button.

    Note

    You could have also just typed XXHR% and clicked the Find button. Then you would retrieve all of the profile options beginning with XXHR. You can use the wildcard (%) to search for specific text such as %HR%Write%Log%, which will also be just as acceptable and will help if you cannot remember the full name of the profile you are looking for. Just remember it is also case sensitive.

  4. 4. Set the Profile Option Name at Site level to Yes, as shown in the following screenshot:

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

How it works...

We have now created a profile option that we can use to turn logging on or off after we have added code to our program to use the profile option value.

Add a procedure to write to the log file

We are now going to add the code to the executable, which is our package called XXHREEBS. If you wish to view the code then it can be found in the files XXHREEBS_1_5.pks and XXHREEBS_1_5.pkb. You can compile the package provided or use the following instructions to add the code manually.

How to do it...

To add a new procedure, perform the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. Now edit the package specification.

  4. 4. Declare a constant variable called cv_write_to_log and assign to it the value of the profile option XXHR_WRITE_LOGFILE, shown as follows:

    Note

    Note that it is the Profile Option Name that we use and not the User Profile Option Name which is what we see when we set the profile option value.

  5. 5. Compile the package specification.

  6. 6. Edit the package body.

  7. 7. Add a procedure called write_log, shown as follows:

  8. 8. Compile the package body.

How it works...

We have added a procedure to our program that will allow us to write messages to the concurrent program log file. This can be switched on or off by changing a profile option value.

Writing to the log file

Now we can add some messages to the First_Concurrent_Program procedure to write to the log file. We have already set the profile option to 'Yes' so every time we call the write_log procedure, the executable will write a message to the log file. So now we will add some messages to the log file. A good place to start would be to display the values that are being passed in as parameters.

How to do it...

To add messages to the log file perform the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. Now edit the package body.

  4. 4. Write to the log file with calls to write_log in our First_Concurrent Program procedure as follows:

  5. 5. Compile the package body.

How it works...

We have added code to write messages to the log file. We have also written the values of the parameters that are passed in to the program to the log file, so that a developer will find it easy to debug the program if there are errors.

Run the concurrent program

Now we want to run the concurrent program testing that the code we have just added writes messages to the log file.

How to do it...

To run the concurrent program, do the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date parameter to the default date (which is the current date).

  4. 4. Select Vision Corporation from the list of values for the Organization parameter and then click OK.

  5. 5. Select an employee record from the list of values for the Person parameter and then click OK.

  6. 6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  7. 7. Navigate to View Requests and click on the Find button (to find all requests).

  8. 8. You should see that the concurrent program we just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed.)

How it works...

We have run the concurrent program again to test that the log messages are written to the log file.

Viewing the log file

Once the concurrent program has completed successfully we can view the log file. Let's now have a look at the log file and see the messages that have been written to it.

How to do it...

To view the messages written to the log file perform the following:

  1. 1. Click on the View Log button in the View Requests screen and a browser window will open.

  2. 2. You will see that the messages we put into the First_Concurrent_Program package have been written to the log file as highlighted in the following image:

How it works...

The messages have been written to the log file, providing valuable information about events that have occurred at runtime.

 

Reporting to the concurrent program output file


Now that we have written to the log file to view log information, we want to write more user-friendly information to the user. We will make this more formatted so that it provides useful information to the user about the processing that has taken place.

The tasks we are going to perform in this recipe are as follows:

  • Add a procedure to write to the output file

  • Add a cursor to get some data

  • Add code to fetch data and write to the output file

  • Run the concurrent program

  • View the concurrent program output file

Add a procedure to write to the output file

We are now going to add the code to the executable, which is our package called XXHREEBS. If you wish to view or copy the code then it can be found in the file XXHREEBS_1_6.pkb. You can compile the package body provided or use the following instructions to add the code manually.

How to do it...

To add a procedure to write to the output file perform the following steps:

  1. 1. Open SQL Developer and connect to the apps user.

  2. 2. Navigate to Packages and select the XXHREEBS package.

  3. 3. Now edit the package body.

  4. 4. Add a new procedure called write_output, shown as follows:

  5. 5. Compile the package body.

How it works...

We have now added a procedure to write messages to the concurrent program output file.

Add a cursor to get some data

We are now going to add a cursor to our procedure to get some data back so that it can be displayed in the output file.

How to do it...

To write a cursor to retrieve some data perform the following steps:

  1. 1. Edit the procedure First_Concurrent_Program and add the cursor as shown in the following image:

Note

Remember that the cursor is available in the download bundle in the XXHREEBS_1_6.pkb package.

How it works...

We have added a cursor that will return data at runtime. We want to format the data and display it in the output file.

Add code to fetch data and write to the output file

We are now going to add a cursor for loop to our procedure to get some data back, so that it can be written to the output file.

How to do it...

To add code to fetch data and write to the output file do the following:

  1. 1. Edit the First_Concurrent_Program procedure and add a cursor for loop to get the data. Make some calls to the write_output procedure to add the information to the output file as shown in the following image:

  2. 2. Compile the package body.

How it works...

For the employee record that we have entered in the person parameter we will fetch the record from the database and write the details to the output file.

Running the concurrent program

Now we want to run the concurrent program testing that the code we have just added writes messages to the log file.

How to do it...

To run the concurrent program to view the output file, perform the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date parameter set to the default date (which is the current date).

  4. 4. Select Vision Corporation from the list of values for the Organization parameter and then click OK.

  5. 5. Select an employee record from the list of values for the Person parameter and then click OK.

  6. 6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  7. 7. Navigate to View Requests and click on the Find button (to find all requests).

  8. 8. You should see that the concurrent program we just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed.)

How it works...

We have run the concurrent program to test the code that we added to write to the output file. We can now view the output to ensure that the layout is as expected.

View the concurrent program output file

Now we want to view the output file to see the messages we have written to it.

How to do it...

To open the output file to test our changes do the following:

  1. 1. Click on the View Output button and a browser window will open.

  2. 2. You will see that the messages we put into the First_Concurrent_Program package have been written to the output file as shown in the following image:

How it works...

As you can see we have generated formatted data that we have written to the output file.

 

Scheduling a concurrent program


There are two ways in which a concurrent program can be executed; it can be launched manually by a user or it can be scheduled to run automatically either on a one off basis or on a regular basis. In the next recipe, we are going to schedule the concurrent program we have created. It is important to note that when we schedule a concurrent program there is no user there to select the values for the parameters that are defined. Therefore, any required parameters we have added must have a default value. This can be done when entering the concurrent program but we are going to default a value for the organization.

We are going to complete the following tasks in this recipe:

  • Add default values for any required parameters

  • Schedule the concurrent program

Add default values for any required parameters

We are now going to add default values for the parameters in our concurrent program that are required.

Getting ready

In our concurrent program XXHR First Concurrent Program we have three parameters:

  • P_RUN_DATE

  • P_ORG_ID

  • P_PERSON_ID

The first parameter P_RUN_DATE is not required and is already defaulted to the current date. The second parameter P_ORG_ID is required and so we are going to default this to Vision Corporation.

How to do it...

To add default values to our concurrent program parameters complete the following steps:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open.

  3. 3. Press the F11 key to enter a query.

  4. 4. Query back the XXHR First Concurrent Program concurrent program and click the parameters button.

  5. 5. Click on the P_ORG_ID parameter and enter the following details to set the default parameter:

    Item name

    Item value

    Default Type

    SQL Statement

    Default Value

    SELECT organization_id FROM hr_all_organization_units WHERE name = 'Vision Corporation'

  6. 6. Click the Save button in the toolbar (or Ctrl + S) to save the record.

How it works...

We have set a default value for the Organization parameter in the concurrent program so that it can be scheduled.

Schedule the concurrent program

Now we are going to schedule the concurrent program to run on a daily basis for two days.

How to do it...

To schedule the concurrent program do the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR First Concurrent Program concurrent program and leave the Run Date and Organization parameters set to their default values and click on OK:

  4. 4. Now click on the Schedule button. The screen dynamically changes when you click on each item of the Run the Job radio group.

  5. 5. Click Periodically.

  6. 6. Set Re-Run to every 1 Day(s).

  7. 7. Enter an End At value 2 days from the current date.

  8. 8. Check the Increment date parameters each run.

The steps are shown in the following screenshot:

  1. 1. Click on OK and the window will close.

  2. 2. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  3. 3. Navigate to View Requests and click on the Find button (to find all requests).

  4. 4. You should see that the concurrent program we just submitted has submitted two requests. The first will be set to run immediately and the second concurrent program is Pending with a status of Scheduled, as shown in the following screenshot:

Note

You will notice that the date in the Parameters field has automatically been incremented because we checked the Increment date parameters each run checkbox.

How it works...

We have now scheduled our concurrent program for two days and the program will run automatically unless it is cancelled before the last run has completed.

 

Creating multiple concurrent programs


It is quite common to have a requirement where we need to configure a concurrent program that is similar to an existing concurrent program. To prevent us having to configure the concurrent program from the beginning, Oracle has created a button that allows us to copy an existing concurrent program. In the next recipe, we are going to create a new concurrent program that is based upon the program we have already created.

The tasks we need to perform to achieve this are the following:

  • Copy an existing concurrent program

  • Add a concurrent program to a request set

Copy an existing concurrent program

We are going to copy the existing concurrent program and use the same parameters. We are also going to change the default parameter for the Organization parameter so that it defaults to 'Vision Operations'.

How to do it...

To copy an existing concurrent program definition, do the following:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open.

  3. 3. Press the F11 key to enter a query.

  4. 4. Type in XXHR First% into the Program field and press the Ctrl + F11 keys to execute the query.

  5. 5. The concurrent program we created in an earlier recipe will be returned.

  6. 6. Now click on the Copy to button and the Copy to window will open, as shown in the following screenshot:

  7. 7. Enter the data as shown in the following table:

    Item name

    Item value

    Program

    XXHR Second Concurrent Program

    Short Name

    XXHR_SECOND_CONC_PROG

    Application

    XXHR Custom Application

    Include Incompatible Programs

    Include Parameters

  8. 8. Click the OK button and the window will close and you will be returned to the Concurrent Programs window. You will notice that the concurrent program is now the new one we have just copied to, XXHR Second Concurrent Program.

  9. 9. Now click on the Parameters button and the Concurrent Program Parameters window will open.

  10. 10. Navigate to the parameter P_ORG_ID.

  11. 11. Change the select statement in the 'Default Value' field to the following:

    SELECT organization_id
    FROM hr_all_organization_units
    WHERE UPPER(NAME) = 'VISION OPERATIONS'
    

    Note

    This query assumes that you are using a Vision instance. Modify the organization name in the query if you are using a different development environment.

  12. 12. Click the Save button to commit the changes.

How it works...

We have now made a copy of an existing concurrent program. We can then amend the details as required to suite our requirements.

Add a concurrent program to a request set

We are now going to add our concurrent program to our request set, so that it will be available from our responsibility when we run a new request.

How to do it...

To add the second concurrent program to our request group, perform the following:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open.

  3. 3. Query back the XXHR Request Group request group.

  4. 4. Now we are going to add the second concurrent program we created in the Requests region. Enter data as in the following table for the detail records:

    Type

    Name

    Application

    Program

    XXHR Second Concurrent Program

    XXHR Custom Application

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now added our second concurrent program to our request set assigned to the XXEBS Extending e-Business Suite responsibility.

 

Creating request sets


In this next recipe we are going to link the concurrent programs and run them together as a request set.

We are going to complete the following tasks in this recipe:

  • Run request set wizard

  • Add a request set to a request group

  • Run the request set

Run request set wizard

To create a request set we are going to run a wizard. The request set allows us to link concurrent programs together. Concurrent programs can be run sequentially or in parallel as a group. They can also be dependent upon the outcome of another program within the request set.

How to do it...

To create a request set using the wizard complete the following tasks:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Requests | Set and the Request Set window will open, as shown in the following screenshot:

  3. 3. Click the Request Set Wizard button.

  4. 4. Select the radio button called Sequentially (One After Another) and then click Next, as shown in the following screenshot:

  5. 5. Click on radio button called Continue Processing. This is what we want the request set to do if any of the programs end with a status of Error:

  6. 6. We now need to enter the details of our request set in the wizard as shown in the following table and click Next:

    Item name

    Item value

    Set

    XXHR20001

    Application

    XXHR Custom Application

    Description

    XXHR Employee By Organization

  7. 7. We now want to print the output files as each request finishes, so select As Each Request in the Set Completes and click Next as follows:

  8. 8. Now add the concurrent programs that we want to run in the request set so add the two programs we have created, XXHR First Concurrent Program and XXHR Second Concurrent Program, and click Finish as shown in the following screenshot:

  9. 9. The following message will appear; click OK:

  10. 10. The request set is then automatically created and the completed set will appear something similar to the following screenshot. We are going to first look at the Define Stages screen and the Link Stages screen to check the configuration:

  11. 11. Click on the Define Stages button to check that the concurrent programs are configured as required:

  12. 12. Check that the screen is configured as we expected and then close the Stages window to navigate back to the Request Set window.

  13. 13. Click on the Link Stages button and the Link Stages window will open as shown in the following screenshot:

How it works...

We have now created a request set using the request set wizard. We can now run the request set, and the concurrent programs will run as we have defined them in the request set.

Add a request set to a request group

We will now add our request set to the request group we have associated with the XXEBS Extending e-Business Suite responsibility.

How to do it...

To add the request set perform the following steps:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open.

  3. 3. Query back the XXHR Request Group request group.

  4. 4. Now we are going to add the request set we created in the Requests region. Enter data as in the following table in the Requests block:

    Type

    Name

    Application

    Set

    XXHR20001

    XXHR Custom Application

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

Okay so now we have added the request set to our request group. Next we are going to run the request set.

Run the request set

Now we want to run the concurrent request set.

How to do it...

To run the request set take the following steps:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and click the OK button as shown in the following screenshot:

  3. 3. Navigate to the Request Set field and select the XXHR20001 request set from the list of values and click OK.

  4. 4. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  5. 5. Navigate to View Requests and click on the Find button (to find all requests) and you will see the request set as shown in the following screenshot:

  6. 6. You should see that the request set we just submitted is running.

If you click the refresh button you will see the stages of the request set complete as they are executed. Once the request set has completed you will see three records in the Requests block. One for the set and one each for the concurrent programs in the request set as shown in the following screenshot:

Note

Note: Remember to click the refresh button as the page does not refresh automatically, so if you see a program still has a phase of Running you will need to click the refresh button until the phase is Completed.

How it works...

We have now run the request set and can see that we can group concurrent programs to be run together.

 

Installing the database objects


We are going to create the database objects for the next examples, by using a script provided called 4842_01_01.sh In the next examples we will be configuring a concurrent program that calls a host program. The script we are going to run will create a sequence, synonym, and package for our host program call. We will also create a package that will handle all of the database transactions such as insert, update, and delete. Finally, we will create a sequence that will be used to generate a unique number for new filenames. The following provides details of how to run the script.

How to do it...

To create the database objects required for this chapter perform the following tasks:

  1. 1. Create a local directory c:\packt\scripts\ch1 where the scripts are downloaded and extracted to.

  2. 2. Open Putty and connect to the application tier user.

  3. 3. Create a new directory on the application tier under $XXHR_TOP/install as follows:

    cd $XXHR_TOP/install
    mkdir ch1
    
    
  4. 4. Navigate to the new directory ch1 as follows:

    cd ch1
    
    
  5. 5. Open WinSCP and transfer the files from c:\packt\scripts\ch1 on your local machine to the $XXHR_TOP/install/ch1 directory on the application server, as shown in the following screenshot:

  6. 6. In Putty, change the permissions of the script with the following command:

    chmod 775 4842_01_01.sh
    
    
  7. 7. Run the following script to create all of the objects by issuing the following command:

    ./4842_01_01.sh apps/apps
    
    
  8. 8. The script checks that all of the files are present in your $XXHR_TOP/install/ch1 directory and will prompt you to continue if they are all there, so type Y and press return.

  9. 9. After the script has completed, check the XXHR_4842_01_01.log file for errors. (It will be created in the same directory $XXHR_TOP/install/ch1.)

  10. 10. Run the following query to check that all of the objects have been created successfully:

    SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
    FROM ALL_OBJECTS
    WHERE OBJECT_NAME LIKE 'XXHR_EMAIL%'
    OR OBJECT_NAME LIKE 'XXHR%BATCH%'
    ORDER BY 1, 2
    
  11. 11. This is shown in the following screenshot:

How it works...

We have created a number of objects that we are going to use in the coming recipes. We have created a synonym to the apps user as all of our objects need to be accessed by this user. The sequence we have created will generate a unique number each time a new file is created. Finally, the package we have created contains all of the procedures and functions relating to database activity for calling our host program.

 

Creating a HOST concurrent program


In this recipe, we are going to create two concurrent programs. The first concurrent program will call the second concurrent program through PL/SQL. The first concurrent program will also generate a file and place it in a directory on the database server. The second concurrent program will call a host program that will get the file we just created and will e-mail it to an e-mail account. To complete the recipe we will perform the following tasks:

  • Creating a PL/SQL executable

  • Configuring a concurrent program to call the PL/SQL executable

  • Creating a HOST executable

  • Configuring a concurrent program to call the HOST executable

  • Adding concurrent programs to a menu

  • Creating an OUT directory

  • Creating a symbolic link

  • Testing a host concurrent program

Creating a PL/SQL executable

The first thing we will do is create an executable that calls a PL/SQL package called xxhr_email_file_pkg.process_main. We will look into what the package does later on, so for now we just want to configure it.

How to do it...

To create the executable to call a database package perform the following steps:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Executable and the Concurrent Program Executable window will open.

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Executable

    XXHR Generate File

    Short Name

    XXHR_GENERATE_FILE

    Application

    XXHR Custom Application

    Description

    Generates a file that is emailed

    Execution Method

    PL/SQL Stored Procedure

    Execution File Name

    XXHR_EMAIL_FILE_PKG.process_main

    Note

    Note: Any fields that are not specified in this table should be left as their default value.

  4. 4. The form should now look like the following screenshot:

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now created a concurrent program executable that will launch a PL/SQL package called xxhr_email_file_pkg.process_main.

Configuring a concurrent program to call the PL/SQL executable

In the following recipe, we will configure our concurrent program that calls the executable we have just defined.

How to do it...

To configure the concurrent program, perform the following:

  1. 1. Log in to Oracle and select the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open, as shown in the following screenshot.

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Program

    XXHR Generate File and Email

    Short Name

    XXHR_GEN_AND_EMAIL

    Application

    XXHR Custom Application

    Description

    Generate XML file and email it

    Executable Name

    XXHR_GENERATE_FILE

    Note

    Note: Any fields that are not defined in this table should be left as their default value.

  4. 4. The form should now look like the following:

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Click on the Parameters button to open the Parameters window.

  7. 7. Enter a first parameter with the following details:

    Item name

    Value

    Seq

    10

    Parameter

    P_SUBJECT

    Description

    Subject

    Enabled

    Value Set

    240 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Subject

  8. 8. The Parameter screen should now look like the following screenshot:

  9. 9. Enter a second parameter with the following details:

    Item name

    Value

    Seq

    20

    Parameter

    P_EMAIL_TO

    Description

    Email Address

    Enabled

    Value Set

    100 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Email Address To

  10. 10. The screen should now look like the following screenshot:

  11. 11. Save and exit the form.

How it works...

Okay, so now we have configured the executable and also defined the concurrent program that launches the executable.

Creating a HOST executable

Now we will create an executable that calls a HOST file called xxhr_send_email_file. The name is case sensitive so ensure it is in lower case. Also the host file does not have any extension.

How to do it...

To create the executable to call a HOST file, perform the following:

  1. 1. Log in to Oracle with the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Executable and the Concurrent Program Executable window will open.

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Executable

    XXHR Email File From Unix

    Short Name

    XXHR_EMAIL_FILE_FROM_UNIX

    Application

    XXHR Custom Application

    Description

    XXHR Email File From Unix

    Execution Method

    Host

    Execution File Name

    xxhr_send_email_file

    Note

    Note: Any fields that are not specified in this table should be left as their default value.

  4. 4. The form should now look like the following screenshot:

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now created a concurrent program executable that will launch a HOST file called xxhr_send_email_file.

Configuring a concurrent program to call the HOST executable

In the following recipe, we will configure our concurrent program that calls the executable we have just defined.

How to do it...

To configure the concurrent program, perform the following:

  1. 1. Log in to Oracle and select the Application Developer responsibility.

  2. 2. Navigate to Concurrent | Program and the Concurrent Programs window will open as shown in the following screenshot.

  3. 3. Enter data as shown in the following table:

    Item name

    Item value

    Program

    XXHR Email File

    Short Name

    XXHR_GEN_EMAIL_FILE

    Application

    XXHR Custom Application

    Description

    XXHR Email File

    Executable Name

    XXHR_EMAIL_FILE_FROM_UNIX

    Note

    Note: Any fields that are not defined in this table should be left as their default value.

  4. 4. The form should now look like the following screenshot:

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Click on the Parameters button to open the Parameters window.

  7. 7. Enter a first parameter with the following details:

    Item name

    Value

    Seq

    10

    Parameter

    Subject

    Description

    Subject

    Enabled

    Value Set

    240 Characters

    Default Type

    Constant

    Default Value

    Email Generated from EBS

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Subject

  8. 8. Enter a second parameter with the following details:

    Item name

    Value

    Seq

    20

    Parameter

    Email Address

    Description

    Email Address

    Enabled

    Value Set

    100 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Email Address

  9. 9. Enter a third parameter with the following details:

    Item name

    Value

    Seq

    30

    Parameter

    Filename

    Description

    Filename

    Enabled

    Value Set

    240 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Filename

  10. 10. Enter a fourth parameter with the following details:

    Item name

    Value

    Seq

    40

    Parameter

    Directory

    Description

    Directory

    Enabled

    Value Set

    240 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Directory

  11. 11. Enter a fifth parameter with the following details:

    Item name

    Value

    Seq

    50

    Parameter

    Sent From

    Description

    Sent From

    Enabled

    Value Set

    240 Characters

    Required

    Display

    Display Size

    30

    Description Size

    50

    Concatenated Description Size

    25

    Prompt

    Sent From

  12. 12. The parameter screen should now look like the following. Note: you need to scroll down to see the fifth parameter.

  13. 13. Save and exit the form.

How it works...

Okay, so now we have configured the executable and also defined the concurrent program that launches the executable. The concurrent program has five parameters which will be passed into the host file.

Adding concurrent programs to a request group

When we defined our responsibility called XXEBS Extending e-Business Suite we assigned a request group to it called XXHR Request Group. We are going to add our concurrent programs, so that they will be available from the responsibility.

How to do it...

To update our request group perform the following steps:

  1. 1. Log in to Oracle with the System Administrator responsibility.

  2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open.

  3. 3. Query back the XXHR Request Group we created earlier in the chapter.

  4. 4. Now we are going to add the concurrent programs we created. Add the two concurrent programs as per the following table:

    Type

    Name

    Application

    Program

    XXHR Generate File and Email

    XXHR Custom Application

    Program

    XXHR Email File

    XXHR Custom Application

  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

  6. 6. Exit the form.

How it works...

We have now added the concurrent programs to the request group used by the XXEBS Extending e-Business Suite responsibility. Our concurrent programs will appear in a list of concurrent programs when we want to run a request. The responsibility only has access to the programs in the request set assigned to it.

Creating an OUT directory

Our PL/SQL package uses a PL/SQL utility called UTL_FILE. We are going to write a file to the database server using this utility but we first need to see which directories we have access to. We will then create a name for a directory that we can use as a name for that directory.

How to do it...

To create a named directory, perform the following:

  1. 1. Open SQL Developer and connect as the apps user.

  2. 2. Run the following query to see what directories the file utility has access to:

    SELECT * FROM dba_directories order by directory_name
    
  3. 3. We can see that there is a directory called /usr/tmp, which is the one we will use to write our file to.

  4. 4. We want to create our own name for it, however; to do this run the following command:

    CREATE OR REPLACE DIRECTORY XXHR_XML_OUT AS '/usr/tmp'
    /
    
    
  5. 5. If you run the query again you should see that our entry now exists in the table dba_directories, as shown in the following screenshot:

How it works...

We have created a name for a directory path that we will use to write a file to in our PL/SQL package later on. The name XXHR_XML_OUT is used instead of having to code the file path in our code, and if we need to change the directory we will not have to change our PL/SQL package.

Creating a symbolic link

We are now going to transfer our file over to the application tier in the $APPLBIN directory under the PRODUCT TOP directory. We will then make a symbolic link using the execution filename (without an extension) to fndcpesr, which is located in the $FND_TOP/$APPLBIN directory.

How to do it...

To install our host file, perform the following:

  1. 1. Open WinSCP and transfer the xxhr_send_email_file.prog to the $XXHR_TOP/$APPLBIN directory on the application tier as shown in the following screenshot:

  2. 2. Open Putty and connect to the application tier with the OS user that owns the application tier. (Ensure that the environment is set — refer to Chapter 6 about setting the environment.)

  3. 3. Navigate to the $XXHR_TOP/$APPLBIN directory by typing the following command:

    APPS Tier> cd $XXHR_TOP/$APPLBIN
    
    
  4. 4. Change the permissions of the xxhr_send_email_prog file with the following command:

    APPS Tier> chmod 775 xxhr_send_email_prog
    
    
  5. 5. Create a symbolic link for the file with the following command:

    APPS Tier> ln -s $FND_TOP/bin/fndcpesr xxhr_send_email_file
    
    
  6. 6. Check that the symbolic link has been created with the following command:

    APPS Tier> ls -al
    
    
  7. 7. You can see that the symbolic link has been created as shown in the following screenshot:

How it works...

We have copied our host program over to the bin directory of the application we registered the executable with. We have given correct permissions to the .prog file and then created a symbolic link for the file.

Testing a host concurrent program

We are now going to test the concurrent program but before we do we will take a look at some of the code in the package and the host program we have configured, to see what is happening.

Getting started...

The concurrent program XXHR Generate File and Email will execute the PL/SQL package called XXHR_EMAIL_FILE_PKG.process_main. This package calls three procedures in the following order:

  • generate_xml: This procedure is called first and creates an XML message and stores the message in a CLOB variable.

  • create_xml_file: This procedure is called next and takes the data in the CLOB and creates a file on the server for the named directory we created earlier called XXHR_XML_OUT.

  • email_xml_file: This procedure is called last and submits a concurrent program through a PL/SQL command. The concurrent program that it runs is the XXHR Email File we created to call our host program. The host program gets the file from the server and e-mails it using the UNIX uuencode command.

The host concurrent program called XXHR Email File launches the host file we created on the application tier. The first four parameters in the host file are reserved for oracle. Therefore, the first parameter that we pass from our concurrent program is actually the fifth parameter in the host file. The host file validates the parameters that we pass in and then sends an e-mail using the UNIX uuencode utility. Now we want to run the concurrent program testing that the file is generated on the server and then e-mailed by calling the second concurrent program.

Note

Note: The concurrent program that uses the UTL_FILE utility will write to a directory on the database server. However, the host program accesses the application tier. Therefore, we must write the file to a directory that is shared by the application tier and the database tier.

How to do it...

To run the concurrent program, do the following:

  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

  2. 2. Navigate to Submit Requests and submit a single request.

  3. 3. Select the XXHR Generate File and Email concurrent program.

  4. 4. Fill in the parameters as shown in the following table:

    Subject

    Email sent from EBS

    Email Address To

    <enter your email address here>

    Note

    Note: you will need to enter your own e-mail address in the Email Address To parameter.

  5. 5. Click OK to submit the request as shown in the following screenshot:

  6. 6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

  7. 7. Navigate to View Requests and click on the Find button (to find all requests).

  8. 8. We can see in the following screenshot that there are two concurrent requests. The first is the one that we launched. The second is the program that is launched from our database package through PL/SQL:

  9. 9. You should see that the concurrent program we just submitted has completed successfully. (If it is still Pending then click the refresh button until the status is Completed.)

    We can see from the following screenshot that the e-mail has been received at the e-mail address passed as a parameter when we launched the concurrent program:

How it works...

We have run the concurrent program XXHR Generate File and Email, which in turn launched the XXHR Email File concurrent program. Both concurrent programs appear in the Requests screen. The file was generated on the database server and the host program picked up the file and e-mailed it to the e-mail address passed in as a parameter.

About the Author

  • Andy Penver

    Andy Penver currently lives and works in the U.K. as a solution architect for a large public sector client. He studied at Christ Church (University of Kent). He has over 18 years of experience in working with Oracle E-Business Suite. He has worked in both the private and public sectors, and has a strong technical background. He has led and managed teams of consultants through the full project lifecycles on some very large programs throughout the U.K. and Europe. Andy has been heavily involved in two large-scale, award-winning implementations of a shared service centre. Andy is currently the Managing Director of his own consultancy, NU-TEKK Limited. Andy has previously authored and published a book called Oracle E-Business Suite R12 Core Development and Extension Cookbook.

    Browse publications by this author

Latest Reviews

(1 reviews total)
good A++++++++++++++++++++++++++++++++++++++++++++
Book Title
Unlock this full book FREE 10 day trial
Start Free Trial