Oracle E-Business Suite R12 Core Development and Extension Cookbook: Building extensions in Oracle E-Business Suite is greatly simplified when you follow the step-by-step instructions in this book. Whether novice or pro, this is a great tutorial with over 60 recipes and stacks of screenshots.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Application | Register and the Applications window will open as shown in the following screenshot:

3. Enter a new record with the following data:

4. Save the record.

1. Log in to Oracle with the Application Developer responsibility.

2. Navigate to Concurrent | Executable and the Concurrent Program Executable window will open, as shown in the following screenshot:

3. Enter data as shown in the following table:

4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

5. Exit the form.

1. Log in to Oracle and select the Application Developer responsibility.

2. Navigate to Concurrent | Program and the Concurrent Programs window will open, as shown in the following screenshot:

3. Enter data as shown in the following table:

4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

5. Exit the form.

1. Log in to Oracle with the Application Developer responsibility.

2. Navigate to Application | Menu and the Menus window will open, as shown in the following screenshot:

3. Enter data as shown in the following table for the master record:

4. Enter data as shown in the following table for the detail records:

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

6. Exit the form.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Security | Responsibility | Request and the Request Groups window will open as shown in the following screenshot:

3. Enter data as shown in the following table for the master record:

4. Now we are going to add the concurrent program we created in the Defining a concurrent program recipe. Navigate to the Requests region and enter data as shown in the following table for the detail record:

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

6. Exit the form.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Security | Responsibility | Define and the Responsibilities window will open as shown in the following screenshot:

3. Enter data as shown in the following table for the master record:

4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

5. Exit the form.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Security | User | Define and the Users window will open, as shown in the following screenshot:

3. Enter data as shown in the following table:

4. In the Direct Responsibilities tab, add XXEBS Extending e-Business Suite to the responsibility field.

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

6. Exit the form.

1. Log in to Oracle with the user that has the XXEBS Extending e-Business Suite responsibility assigned to it.

2. Navigate to Submit Requests | Submit and a Submit a New Request window will open, as shown in the following screenshot:

3. Select Single Request and click the OK button.

4. The Submit Request screen will open, as shown in the following screenshot:

5. Click on the Name field and select XXHR First Concurrent Program from the list of values.

6. Click on the Submit button.

7. A decision box will appear informing us that the request has been submitted.

8. Click on the No button as we do not wish to submit another request at this time.

9. Exit the form.

1. Navigate to View | Requests, as shown in the following screenshot:

2. The Find Requests window will appear, as shown in the following screenshot:

3. Click on the Find button in the bottom-right hand side corner.

4. The Requests window will appear and you will see the concurrent program that was executed, as shown in the following screenshot:

5. You can see that the concurrent program has been completed successfully.

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. Type XXHR First% into the Program field and press the Ctrl + F11 keys together to execute the query.

5. The concurrent program we created in an earlier recipe will be returned, as shown in the following screenshot:

6. Click on the Parameters button to open the Concurrent Program Parameters window, as shown in the following screenshot:

7. Enter the following details as per the following table:

Item name	Item value
Seq	10
Parameter	P_RUN_DATE
Description	Concurrent program run date
Enabled
Value Set	FND_STANDARD_DATE
Default Type	Current Date
Required
Display
Display Size	11
Description Size	50
Concatenated Description Size	25
Prompt	Run Date

8. Click the Save button in the toolbar (or Ctrl + S) to save the record.

9. Exit the form.

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 as shown in the following screenshot:

4. Now edit the package specification by clicking on the package specification in the Packages node as shown in the following screenshot.

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 ( )in the editor toolbar.

8. The package specification will now look like the following:

9. 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:

10. 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

11. Compile the First_Concurrent_Program package body.

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 a 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. The package body should now look similar to the following screenshot, which shows the three changes to the code we have made:

6. Compile the First_Concurrent_Program package body.

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.)

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:

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:

6. 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.

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_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. Click the Save button in the toolbar (or Ctrl + S) to save the record.

7. The completed screen will look like the following screenshot:

1. Open SQL Developer and connect to the apps user.

2. Navigate to Packages and select the XXHREEBS package.

3. In the code editor, scroll down the package specification until you reach the First_Concurrent_Program procedure definition.

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. 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_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. Compile the First_Concurrent_Program package body.

9. The program specification will look like the following:

10. The program body will look like 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 Date parameter to the default date (which is the current date).

4. Select an organization from the list of values for the Organization parameter 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.)

1. Log in to Oracle with the Application Developer responsibility.

2. Navigate to Application | Validation | Set and the Values Sets window will open.

3. Enter the details as shown in the following table:

4. Click on the Edit Information button and the Validation Table Information window will open.

5. Enter the details as shown in the following table:

6. Click the Save button in the toolbar (or Ctrl + S) to save the record.

7. Click on the Test button to test the code entered.

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	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

1. Open SQL Developer and connect to the apps user.

2. Navigate to Packages and select the XXHREEBS package.

3. Now edit the package specification.

4. Scroll down the package specification until you reach the First_Concurrent_Program procedure definition.

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. Compile the package specification.

7. Open the package body to add the p_person_id parameter to the body.

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. Compile the First_Concurrent_Program package body.

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 set to the default date (which is the current date).

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. Select an employee record from the list of values for the Person parameter and then click OK.

6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

7. Navigate to View Requests and click on the find button (to find all requests).

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).

1. Log in to Oracle with the Application Developer responsibility.

2. Navigate to Profile and the Profiles window will open, as shown in the following screenshot:

3. Enter data as shown in the following table:

4. Click the Save button in the toolbar (or Ctrl + S) to save the record.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Profile | System and the Find System Profile Values window will open as shown in the following screenshot:

3. In the Profile field type XXHR Write to Concurrent Program Logfile and click the Find button.

4. Set the Profile Option Name at Site level to Yes, as shown in the following screenshot:

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

1. Open SQL Developer and connect to the apps user.

2. Navigate to Packages and select the XXHREEBS package.

3. Now edit the package specification.

4. Declare a constant variable called cv_write_to_log and assign to it the value of the profile option XXHR_WRITE_LOGFILE, shown as follows:

5. Compile the package specification.

6. Edit the package body.

7. Add a procedure called write_log, shown as follows:

8. Compile the package body.

1. Open SQL Developer and connect to the apps user.

2. Navigate to Packages and select the XXHREEBS package.

3. Now edit the package body.

4. Write to the log file with calls to write_log in our First_Concurrent Program procedure as follows:

5. Compile the package body.

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).

4. Select Vision Corporation from the list of values for the Organization parameter and then click OK.

5. Select an employee record from the list of values for the Person parameter and then click OK.

6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

7. Navigate to View Requests and click on the Find button (to find all requests).

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.)

1. Click on the View Log button in the View Requests screen and a browser window will open.

2. You will see that the messages we put into the First_Concurrent_Program package have been written to the log file as highlighted in the following image:

1. Open SQL Developer and connect to the apps user.

2. Navigate to Packages and select the XXHREEBS package.

3. Now edit the package body.

4. Add a new procedure called write_output, shown as follows:

5. Compile the package body.

1. Edit the procedure First_Concurrent_Program and add the cursor as shown in the following image:

1. Edit the First_Concurrent_Program procedure and add a cursor for loop to get the data. Make some calls to the write_output procedure to add the information to the output file as shown in the following image:

2. Compile the package body.

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 set to the default date (which is the current date).

4. Select Vision Corporation from the list of values for the Organization parameter and then click OK.

5. Select an employee record from the list of values for the Person parameter and then click OK.

6. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

7. Navigate to View Requests and click on the Find button (to find all requests).

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.)

1. Click on the View Output button and a browser window will open.

2. You will see that the messages we put into the First_Concurrent_Program package have been written to the output file as shown in the following image:

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. Click on the P_ORG_ID parameter and enter the following details to set the default parameter:

6. Click the Save button in the toolbar (or Ctrl + S) to save the record.

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 and Organization parameters set to their default values and click on OK:

4. Now click on the Schedule button. The screen dynamically changes when you click on each item of the Run the Job radio group.

5. Click Periodically.

6. Set Re-Run to every 1 Day(s).

7. Enter an End At value 2 days from the current date.

8. Check the Increment date parameters each run.

1. Click on OK and the window will close.

2. Click on the Submit button and when prompted to submit a new request select No and the form will close down.

3. Navigate to View Requests and click on the Find button (to find all requests).

4. You should see that the concurrent program we just submitted has submitted two requests. The first will be set to run immediately and the second concurrent program is Pending with a status of Scheduled, as shown in the following screenshot:

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. Type in XXHR First% into the Program field and press the Ctrl + F11 keys to execute the query.

5. The concurrent program we created in an earlier recipe will be returned.

6. Now click on the Copy to button and the Copy to window will open, as shown in the following screenshot:

7. Enter the data as shown in the following table:

Item name	Item value
Program	XXHR Second Concurrent Program
Short Name	XXHR_SECOND_CONC_PROG
Application	XXHR Custom Application
Include Incompatible Programs
Include Parameters

8. Click the OK button and the window will close and you will be returned to the Concurrent Programs window. You will notice that the concurrent program is now the new one we have just copied to, XXHR Second Concurrent Program.

9. Now click on the Parameters button and the Concurrent Program Parameters window will open.

10. Navigate to the parameter P_ORG_ID.

11. Change the select statement in the 'Default Value' field to the following:

SELECT organization_id
FROM hr_all_organization_units
WHERE UPPER(NAME) = 'VISION OPERATIONS'

12. Click the Save button to commit the changes.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Security | Responsibility | Request and the Request Groups window will open.

3. Query back the XXHR Request Group request group.

4. Now we are going to add the second concurrent program we created in the Requests region. Enter data as in the following table for the detail records:

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

6. Exit the form.

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Requests | Set and the Request Set window will open, as shown in the following screenshot:

3. Click the Request Set Wizard button.

4. Select the radio button called Sequentially (One After Another) and then click Next, as shown in the following screenshot:

5. Click on radio button called Continue Processing. This is what we want the request set to do if any of the programs end with a status of Error:

6. We now need to enter the details of our request set in the wizard as shown in the following table and click Next:

7. We now want to print the output files as each request finishes, so select As Each Request in the Set Completes and click Next as follows:

8. Now add the concurrent programs that we want to run in the request set so add the two programs we have created, XXHR First Concurrent Program and XXHR Second Concurrent Program, and click Finish as shown in the following screenshot:

9. The following message will appear; click OK:

10. The request set is then automatically created and the completed set will appear something similar to the following screenshot. We are going to first look at the Define Stages screen and the Link Stages screen to check the configuration:

11. Click on the Define Stages button to check that the concurrent programs are configured as required:

12. Check that the screen is configured as we expected and then close the Stages window to navigate back to the Request Set window.

13. Click on the Link Stages button and the Link Stages window will open as shown in the following screenshot:

1. Log in to Oracle with the System Administrator responsibility.

2. Navigate to Security | Responsibility | Request and the Request Groups window will open.

3. Query back the XXHR Request Group request group.

4. Now we are going to add the request set we created in the Requests region. Enter data as in the following table in the Requests block:

5. Click the Save button in the toolbar (or Ctrl + S) to save the record.

6. Exit the form.

1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.

2. Navigate to Submit Requests and click the OK button as shown in the following screenshot:

3. Navigate to the Request Set field and select the XXHR20001 request set from the list of values 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 View Requests and click on the Find button (to find all requests) and you will see the request set as shown in the following screenshot:

6. You should see that the request set we just submitted is running.