Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Oracle E-Business Suite R12 Core Development and Extension Cookbook

You're reading from   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.

Arrow left icon
Product type Paperback
Published in May 2012
Publisher Packt
ISBN-13 9781849684842
Length 480 pages
Edition 1st Edition
Languages
Arrow right icon
Author (1):
Arrow left icon
Andy Penver Andy Penver
Author Profile Icon Andy Penver
Andy Penver
Arrow right icon
View More author details
Toc

Table of Contents (12) Chapters Close

Oracle E-Business Suite R12 Core Development and Extension Cookbook
Credits
About the Author
About the Reviewers
1. www.PacktPub.com
2. Preface
1. Creating Concurrent Programs 2. Oracle Forms FREE CHAPTER 3. Advanced Oracle Forms 4. Forms Personalization 5. Workflow 6. Utilities

Creating a value set


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

Create a value set

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.

How to do it...

To create a value set complete the following tasks:

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

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

    Item name

    Item value

    Value Set Name

    XXHR_ORG_LIST_VS

    Description

    List of HR Organisations

    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 form will appear, as shown in the following screenshot:

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

    Item name

    Value

    Type

    Size

    Table Application

    Human Resources

      

    Table Name

    HR_ALL_ORGANIZATION_UNITS

      

    Value

    ORGANIZATION_ID

    Varchar2

    22

    Meaning

    NAME

    Varchar2

    240

    ID

    ORGANIZATION_ID

    Number

    22

    Where/Order By

    SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL (date_to, SYSDATE) ORDER BY name

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

There's more...

We are just going to explore what happens to the information we store in this screen and how it performs at runtime.

What happens to the data entered?

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.

Create a new parameter for the concurrent program

We are now going to add a new parameter and use the value set we have just created in the previous recipe.

How to do it...

To create a new 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. 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

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

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

How it works...

We have added a new parameter that will be passed to our concurrent program. The parameter will use the list of organizations we created in the previous task. We must now add the parameter to the PL/SQL package, which we will do next.

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_3.pks and XXHREEBS_1_3.pkb. You can compile the package provided or add the parameter to the code manually below.

How to do it...

To add the parameter, take 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. In the code editor, scroll down the package specification until you reach the First_Concurrent_Program procedure definition.

  4. 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. 5. Compile the package specification and ensure it compiles without error.

  6. 6. Now we need to make the same addition to the package body. Open the package body.

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

  9. 9. The program specification will look like the following:

  10. 10. The program body will look like the following:

How it works...

We have now added the parameter p_org_id to the concurrent program definition and also amended the code to add the parameter to the package specification and body.

Run the concurrent program

Now we want to run the concurrent program testing that the concurrent program still runs successfully if the organization parameter is passed in.

How to do it...

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

  4. 4. Select an organization from the list of values for the Organization parameter and then click OK.

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

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

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

How it works...

We have tested that the parameter we have added has been passed to the procedure. We don't at present do anything with it but we just want to ensure that the organization identifier is being passed.

lock icon The rest of the chapter is locked
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image