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.