Adding a Concurrent Program Parameter

This note is taken from Oracle E-Business Suite R12 Core Development and Extension Cookbook by Andy Penver

We are going to add a parameter to the concurrent program XXHR First Concurrent Program. Parameters allow the users to pass values into the concurrent program.

We will also need to add the parameter to the code in the XXHREEBS package and we will use SQL Developer to make these changes.

• Adding a parameter to the concurrent program
• Amend the XXHREEBS package to add the new parameter
• Amend the XXHREEBS to change the completion status of the concurrent program
• Testing the concurrent program

Adding a parameter to the concurrent program:

1. Log in to Oracle with the Application Developer responsibility
2. Navigate to Concurrent >> Program to open the Concurrent Programs window
3. Press the F11 key to enter a query
4. Type XXHR First% into the Program field and press the Ctrl + F11 to execute the query
5. Click on the Parameters button to open the Concurrent Program Parameters window
6. Enter:

• Seq: 10
• Parameter: P_RUN_DATE
• Description: Concurrent program run date
• Enabled: Yes
• Value Set: FND_STANDARD_DATE
• Default Type: Current Date
• Required: No
• Display: Yes
• Display Size: 11
• Description Size: 50
• Concatenated Description Size: 25
• Prompt: Run Date

Save and exit the form

The value entered in the Parameter field is the name of the parameter we will define in the PL/SQL package in the next task. The value entered in the Value Set field is a pre-defined value set. It is for selecting a date and we are going to re-use this rather than create our own.

Amend the XXHREEBS package to add the new parameter

1. Open SQL Developer and connect to the apps user.
2. In the navigator, expand the Packages node and select the XXHREEBS package.
3. The package specification will be opened in the editor
4. Now edit the package specification by clicking on the package specification in the Packages node
5. Scroll down the package specification code until you reach the First_Concurrent_Program procedure definition, shown as follows: PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2, retcode OUT NUMBER);
6. Now add the parameter to the code after the first two mandatory parameters, errbuf and retcode. The program definition will look like the following: PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2, retcode OUT NUMBER, p_run_date IN VARCHAR2);
7. Compile the package specification by clicking the compile icon.
8. Now we need to add the parameter to the package body, so click the open body icon as shown in the following screenshot and a new tab will open displaying the package body in the editor
9. Scroll down to the First_Concurrent_Program procedure definition and add the p_run_date parameter to the list of parameters, shown as follows: PROCEDURE First_Concurrent_Program (errbuf OUT VARCHAR2, retcode OUT NUMBER, p_run_date IN VARCHAR2) IS
10. Compile the First_Concurrent_Program package body

Amend the XXHREEBS package to change the completion status of the concurrent program

1. Open SQL Developer and connect to the apps user
2. Navigate to Packages and select the XXHREEBS package
3. Add the following variable to capture the run date passed in and convert it to date: v_run_date DATE := TO_DATE(p_run_date,' YYYY/MM/DD HH24:MI: SS');
4. Edit the First_Concurrent_Program procedure by replacing the line of code retcode := SUCCESS; with the following code after the BEGIN statement: IF TRUNC(v_run_date) = TRUNC(SYSDATE) THEN retcode := SUCCESS; ELSIF TRUNC(v_run_date) < TRUNC(SYSDATE) THEN retcode := WARNING;ELSIF TRUNC(v_run_date) > TRUNC(SYSDATE) THEN retcode := FAILED; END IF;
5. Compile the First_Concurrent_Program package body

Testing the concurrent program

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 Date parameter to the default date (which is the current date) and click OK.
4. Click on the Submit button and when prompted to submit a new request, select No, and the form will close down
5. Navigate to the View Requests window and click on the Find button (to find all requests)
6. 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)