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.