Book Image

Oracle E-Business Suite R12 Core Development and Extension Cookbook

By : Andy Penver
Book Image

Oracle E-Business Suite R12 Core Development and Extension Cookbook

By: Andy Penver

Overview of this book

Oracle's suite of applications is used by many major businesses and public sector organizations throughout the world. The book will show you how to build different types of extensions with different toolsets with Oracle E-Business Suite R12. It will take you from start to finish with fully working examples. This book will show you how to extend Oracle E-Business Suite Release 12. You will learn highly desirable skills on how to extend the application and develop your expertise. The book will provide detailed information about why things have to be done in certain ways, and will take you through the process of how to get started, what tools are needed, how to develop working examples, and how to deploy them within the application. Learn how to extend Oracle E-Business Suite (EBS) Release 12. There are detailed examples to work through, such as how various components are configured and how we can extend standard functionality. The book focuses on core development and extension and each chapter will introduce a topic before going through working examples from start to finish. There are plenty of detailed screen shots throughout each chapter giving clear instructions of what we are doing and why. Each recipe will develop a solution that will utilize core components to that topic. The Oracle E-Business Suite R12 Core Development and Extension Cookbook focuses on starting an extension right from the beginning, to deploying it within E-Business Suite. At the end of each chapter the reader will have a good understanding of what they need to do for each area to take away, and start using it in practice. Each chapter will detail how to build an extension in the supported manner and also comes with complete fully tested code, and scripts that can be downloaded.
Table of Contents (12 chapters)
Oracle E-Business Suite R12 Core Development and Extension Cookbook
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface

Adding a concurrent program parameter


In this recipe, 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. This can be used by the executable to impact the way it performs the intended task. We are going to add the parameter to the PL/SQL package and then change the return code based upon the value we pass in.

We will be adding a parameter to the concurrent program we have already created. 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. The following tasks will be performed in this recipe:

  • 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

We are now going to add a parameter to the concurrent program to pass the date on which the request was run.

How to do it...

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

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

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

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

    Note

    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.

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

  9. 9. Exit the form.

How it works...

We have now added a date parameter to the concurrent request. When we run the concurrent request, Oracle uses the records entered here to dynamically create a parameter screen. We can add parameters that are mandatory and based on a list of values if we wish. Also, we can hide a parameter to the user by checking the Display checkbox if required. We are going to add some more parameters to the concurrent program in the next few tasks but first we must add the parameter we just created to the procedure we call in the executable.

Amend the XXHREEBS package to add the new parameter

Now we are going to amend the XXHREEBS package to accept the new parameter we have configured. The parameter we configured is P_RUN_DATE.

Getting ready

We will need to use SQL Developer to amend the package so if you have not already done so, install SQL Developer. You can download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index-098778.html.

How to do it...

We are now going to amend the database package to add the parameter we have just configured, as follows:

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

  2. 2. In the navigator, expand the Packages node and select the XXHREEBS package.

    Note

    You can add a filter on the Packages node in SQL Developer which will refresh the list much quicker. To do this highlight the Packages node and click on the filter icon. In the Filter window, select Name | Like | XXHR% in the filter criteria region and click ok.

  3. 3. The package specification will be opened in the editor as shown in the following screenshot:

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

  5. 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. 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. 7. Compile the package specification by clicking the compile icon ( )in the editor toolbar.

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

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

How it works...

Now we have created a new parameter and added it to our PL/SQL package executed by our concurrent program.

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

We are now going to add some code to the package to change the return status of the concurrent program. We will return a status of success if the date parameter we enter when we run the concurrent program is equal to the current date. When the date parameter is before the current date we will return a status of warning and if the date parameter is after today's date then we will return an error status.

Getting ready

We are going to amend the XXHREEBS package body to determine the return status of the concurrent program based upon the date parameter P_RUN_DATE that is passed in. The code for the following recipe is available in the following files: XXHREEBS_1_2.pks (specification) and XXHREEBS_1_2.pkb (body). The following are the steps taken to add the code to the existing XXHREEBS package.

How to do it...

To amend the package, 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. 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. 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;
    

    Note

    Downloading example code

    You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

  5. 5. The package body should now look similar to the following screenshot, which shows the three changes to the code we have made:

  6. 6. Compile the First_Concurrent_Program package body.

How it works...

We have edited the package to add some rules, so that the concurrent program will complete either with success, a warning, or an error depending upon the value of the date parameter we run the concurrent program with.

Testing the concurrent program

Now we want to run the concurrent program testing the logic we have added in the PL/SQL code. If the run date parameter is entered as the current date then the concurrent program will complete successfully. If the concurrent program runs with a run date prior to today then the concurrent program will complete with a warning. If the concurrent program runs with a future date then it will complete with an error.

How to do it...

To test the logic we have added, perform the following steps:

  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) and click OK.

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

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

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

How it works...

We have tested that we have been able to add the parameter to the concurrent program. We have also tested that it still completes successfully.

There's more...

Now we want to test the other conditions so try submitting the concurrent program again but enter a date less than the current date. The program should complete with a warning. Finally, run the program again with a future date and the concurrent program should complete with an error.