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.
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.
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
errbufandretcode. The errbuf parameter is used to return error messages. Theretcodeis 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.
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/bindirectory.A host program registered in the Receivables application would be stored in the
$AR_TOP/bindirectory.An SQL*Plus script registered in a custom application would be stored in the custom application top directory, for example, the
$XXHR_TOP/sqldirectory. 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/Langdirectory.
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.
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.
To register an application, perform the following steps:
1. Log in to Oracle with the System Administrator responsibility.
2. Navigate to Application | Register and the Applications window will open as shown in the following screenshot:
3. Enter a new record with the following data:
Application:
XXHR Custom Application
Short Name :
XXHR
Basepath :
XXHR_TOP
Description :
XXHR Custom Application
4. Save the record.
We will now configure the executable that our concurrent program will execute at runtime.
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.
1. Log in to Oracle with the Application Developer responsibility.
2. Navigate to Concurrent | Executable and the Concurrent Program Executable window will open, as shown in the following screenshot:
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_program4. Click the Save button in the toolbar (or Ctrl + S) to save the record.
5. Exit the form.
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.
In this recipe, we will configure our first concurrent program. The concurrent program will run the executable that we have just defined.
1. Log in to Oracle and select the Application Developer responsibility.
2. Navigate to Concurrent | Program and the Concurrent Programs window will open, as shown in the following screenshot:
3. Enter data as shown in the following table:
Item name
Item value
Program
XXHR First Concurrent Program
Short Name
XXHR_FIRST_CONC_PROGApplication
XXHR Custom Application
Description
XXHR First Concurrent Program
Executable Name
XXHR1001
4. Click the Save button in the toolbar (or Ctrl + S) to save the record.
5. Exit the form.
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.