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.