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
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.
To create a value set complete the following tasks:
1. Log in to Oracle with the Application Developer responsibility.
2. Navigate to Application | Validation | Set, and the Values Sets window will open as shown in the following screenshot:
3. Enter the following details as per the following table:
Item name
Item value
Value Set Name
XXHR_ORG_LIST_VSDescription
List of HR Organisations
List Type
List of Values
Security Type
No Security
Format Type
Char
Maximum Size
20
Validation Type
Table
4. Click on the Edit Information button and the Validation Table Information form will appear, as shown in the following screenshot:
5. Enter the following details as per the following table:
Item name
Value
Type
Size
Table Application
Human Resources
Table Name
HR_ALL_ORGANIZATION_UNITSValue
ORGANIZATION_IDVarchar222
Meaning
NAMEVarchar2240
ID
ORGANIZATION_IDNumber22
Where/Order By
SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL (date_to, SYSDATE) ORDER BY name6. Click on the Save button in the toolbar (or Ctrl + S) to save the record.
7. Click on the Test button to test the code entered.
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.
We are just going to explore what happens to the information we store in this screen and how it performs at runtime.
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.
We are now going to add a new parameter and use the value set we have just created in the previous recipe.
To create a new parameter, perform the following steps:
1. Log in to Oracle with the Application Developer responsibility.
2. Navigate to Concurrent | Program and the Concurrent Programs window will open.
3. Press the F11 key to enter a query.
4. Query back the XXHR First Concurrent Program concurrent program and click the parameters button.
5. Add a new parameter with the following details:
Item name
Value
Seq
20
Parameter
P_ORG_IDDescription
Organization ID
Enabled
Value Set
XXHR_ORG_LIST_VSDefault Type
Required
Display
Display Size
20
Description Size
50
Concatenated Description Size
25
Prompt
Organization
6. Click the Save button in the toolbar (or Ctrl + S) to save the record.
7. The completed screen will look like the following screenshot:
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.
To add the parameter, take the following steps:
1. Open SQL Developer and connect to the apps user.
2. Navigate to Packages and select the
XXHREEBSpackage.3. In the code editor, scroll down the package specification until you reach the
First_Concurrent_Programprocedure definition.4. Now add the parameter to the code AFTER the
p_run_dateparameter. 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. Compile the package specification and ensure it compiles without error.
6. Now we need to make the same addition to the package body. Open the package body.
7. Scroll down to the
First_Concurrent_Programprocedure definition and addp_org_idto 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
9. The program specification will look like the following:
10. The program body will look like the following:
Now we want to run the concurrent program testing that the concurrent program still runs successfully if the organization parameter is passed in.
To test the changes perform the following:
1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.
2. Navigate to Submit Requests and submit a single request.
3. Select the XXHR First Concurrent Program concurrent program and leave the
Run Dateparameter to the default date (which is the current date).4. Select an organization from the list of values for the
Organizationparameter and then click OK.5. Click on the Submit button and when prompted to submit a new request, select No and the form will close down.
6. Navigate to View Requests and click on the find button (to find all requests).
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:
