Oracle E-Business Suite with Desktop Integration

Exclusive offer: get 50% off this eBook here
Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook

Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook — Save 50%

A practical step-by-step guide to develop end-to-end extensions to Oracle E-Business Suite Release 12, with detailed illustrations and explanations with this book and ebook.

$32.99    $16.50
by Andy Penver | September 2013 | Cookbooks Enterprise Articles

The article explains how we can integrate EBS, personalize and develop OA Framework pages with EBS, and to show how we can use BI Publisher to create and mail merge documents within EBS. This article by Andy Penver, author of Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook, explains the procedure to integrate with EBS using desktop integrator.

(For more resources related to this topic, see here.)

Getting started with desktop integration

We are now going to create a menu, request group, and responsibility that will be used for the integrators, which we will create in this article. To do this, we will perform the following:

  • Configure a menu
  • Create a new request group
  • Create a new responsibility
  • Assign the desktop integration responsibility to a user

Configure a menu

The following article will configure a menu, which will be attached to our new responsibility we are going to create. This will determine the concurrent programs and forms we will be able to access.

How to do it...

To create a menu, perform the following steps:

  1. Log in to Oracle with the Application Developer responsibility.
  2. Navigate to Application | Menu, and the Menus window will open.
  3. Enter data as shown in the following table for the master record:

    Item Name

    Item Value

    Menu

    XXHR_DI_MENU

    User Menu Name

    Test Desktop Integration Menu

    Menu Type

    Standard

    Description

    Test Desktop Integration Menu

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

    Seq

    Prompt

    Submenu

    Function

    Description

    Grant

    10

    View Requests


    View All Concurrent Requests

     

    Select this

    20

    Submit Requests


    Requests: Submit

     

    Select this

    The screen should now look like the following:

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

How it works...

We have created a menu that has the standard concurrent request functions added to it, so that we can run and view our concurrent program. The menu is assigned to a responsibility, and this is what a user will see when they switch to the responsibility associated with the menu. We have assigned the View Requests and Submit Requests functions to this menu, as we want to allow users to run concurrent programs from this menu.

Create a new request group

When we define a responsibility, we can also assign a request group to it. This is a list of concurrent programs or request sets that the responsibility will see, when they run a concurrent request through the Standard Request Submission (SRS) form. We need to add a request group that will have our concurrent program in it.

How to do it...

To create a request group, perform the following:

  1. Log in to oracle with the System Administrator responsibility.
  2. Navigate to Security | Responsibility | Request to open the Request Groups window.
  3. Enter data as shown in the following table for the master record:

    Item Name

    Item Value

    Group

    XXHR DI Request Group

    Application

    XXHR Custom Application

    Code

    XXHR_REQUEST_GROUP

    Description

    XXHR DI Group

    The following screenshot shows the form with the request group data entered:

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

How it works...

The request group will contain the concurrent programs that we want the user to be permitted to run. We need to assign the request group to a responsibility, and this will allow that responsibility to access the concurrent programs associated with the request group.

Create a new responsibility

Now, we will create a new responsibility that we can associate the menu we have just created to.

How to do it...

Perform the following steps to create a new responsibility called XXHR Desktop Integration.

  1. Log in to oracle with the System Administrator responsibility.
  2. Navigate to Security | Responsibility | Define, and the Responsibilities window will open.
  3. Enter data as shown in the following table for the master record:

    Item Name

    Item Value

    Responsibility Name

    XXHR Desktop Integration

    Application

    XXHR Custom Application

    Responsibility Key

    XXHRDINT

    Description


    Name (in Data Group)

    Standard

    Application (in Data Group)

    Service

    Menu

    Test Desktop Integration Menu

    Name (in Request Group)

    XXHR DI Group

    The Application field in Request Group will inherit the Application from the Request Group we have previously created and will be populated automatically.

    The screen will now look like the following:

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

How it works...

The responsibility we have just created can now be added to a user to provide access to the menu and request groups that we have created.

Assign the desktop integration responsibilities to a user

Now we are going to assign the responsibility to our user.

How to do it...

To create a new user, perform the following steps:

  1. Log in to oracle with the System Administrator responsibility.
  2. Navigate to Security | User | Define, and the Users window will open.
  3. Query back and add the following responsibilities to your user:
    • Desktop Integration
    • Desktop Integration Manager
    • Desktop Integrator
    • XXHR Desktop Integration
    • System Administrator
    • Application Developer

    The screen should look similar to the following:

How it works...

Assigning these responsibilities to your user will mean that they will be displayed when you log in to the system. Each responsibility will have a menu, which will give users access to different functionalities of the system. In this case, we have provided access to the Desktop Integrator functions that will allow us to create and administer a new integrator. Notice that in release 12.1.3 of EBS, the login screen now has a different menu structure. It is more like a folder structure, which is much easier to navigate with.

The following screenshot is what we will see when we log in to Oracle EBS:

Configuring the browser and MS Office settings

When we create an integrator, the generation is performed through a browser. To allow this to happen, we must ensure that the browser allows certain functionality to be switched on. In this case, we must enable a security setting if using an Internet Explorer browser. In this article, we will perform the following:

  • Configure the browser
  • Configure MS office security settings

Configure the browser

In this article, we will set the Internet Explorer browser settings.

How to do it...

To set the browser settings, perform the following:

  1. Open an Internet Explorer browser window.
  2. Click on Internet Options
  3. Click on the Security tab.
  4. In the security page, click on the Internet zone as shown in the following screenshot:

  5. Now, click on the Custom level… button.
  6. Scroll down Security Settings until you get to Scripting.
  7. Set the Allow status bar updates via script radio button to Enable as shown in the following screenshot:

  8. Click on OK.
  9. Click on Yes when prompted with the Are you sure you want to change the settings for this zone warning message.
  10. Finally, click on OK to close the Internet Options dialog box.
  11. Restart the browser, so that the new settings can take effect.

How it works...

We must change some browser settings to allow integrators to be created, as the integrator creation user interface is browser based.

Configure MS Office security settings

When an integrator is created by Oracle, it uses VBA code in the background. By default, the security settings in Microsoft Office does not allow VBA code to be run. Therefore, we must change the security settings to allow the VBA code to be run.

How to do it...

To configure the MS Office settings, perform the following:

  1. Open Microsoft Excel (Office 2010).
  2. Click on the File tab and select Options from the menu.
  3. Select Trust Center and then click on Trust Center Settings….

  4. Click on Macro Settings and check the Trust access to the VBA project object model checkbox as shown in the following screenshot:

How it works...

When we create integrators, there are a number of macros that Oracle uses, which run in the background. If we do not set the Macro Settings, the integrator will not be created.

Registering a table and its columns within Oracle E-Business Suite

We need to register our table that we are going to load data into within EBS. This is required when we create the integrator, so that we can see the table definition in the user interface. We will run the script to register the XXHR_PARTY_UPLOAD table and all of its columns.

How to do it...

To run the script to register the XXHR_PARTY_UPLOAD table, perform the following:

  1. Start SQL Developer and open the XXHR_PARTY_UPLOAD_REG.sql file available from the download bundle.
  2. Click the run script icon from the toolbar as shown in the following screenshot.

    You can run the script in SQL*Plus or another development tool such as TOAD if you prefer.

Now we have run the script to register the table, we can check that it has been successfully registered in EBS.

How it works...

To register the table, we must use the AD_DD package that is provided by Oracle, as the form does not allow users to enter records. The script has been provided and this has been run to register the database table. Let's have a look at the syntax; an example of the code is shown as follows:

EXECUTE AD_DD.REGISTER_TABLE('XXHR', 'XXHR_PARTY_UPLOAD', 'T');

The parameters are as follows:

Parameter

Meaning

P_APPL_SHORT_NAME

The application short name of the table. This is our custom application XXHR.

P_TAB_NAME

Table name (this needs to be in uppercase).

P_TAB_TYPE

Type Of Table. We will nearly always use T.

Likewise, we have added each item and we did this by calling the AD_DD.REGISTER_COLUMN procedure for each column.

An example would be as follows:

EXECUTE AD_DD.REGISTER_COLUMN('XXHR', 'XXHR_PARTY_UPLOAD', 'TXN_ID',1,
'NUMBER', 38, 'N', 'N');

The parameters are as follows:

Parameter

Meaning

P_APPL_SHORT_NAME

The application short name of the table. This is our custom application XXHR.

P_TAB_NAME

Table name (this needs to be in uppercase).

P_COL_NAME

Type of table. we will nearly always use T.

P_COL_SEQ

This is a unique number of the columns.

P_COL_TYPE

This parameter is the type of column, for example, NUMBER or DATE etc.

P_COL_WIDTH

This parameter specifies the width of the column.

P_NULLABLE

This parameter is a Y/N parameter to determine if NULL values are allowed.

P_TRANSLATE

This parameter is a Y/N parameter to determine if the values will be translated by Oracle.

Checking the table has been registered in Oracle

We have run the script to register our table in EBS. Now we will log in to Oracle to check that the table has been registered successfully.

How to do it...

To check that the table has been registered in EBS, perform the following:

  1. Log in to Oracle with the Application Developer responsibility.
  2. Navigate to Application | Database | Table, and the Tables window will open.
  3. Press F11 to enter a query.
  4. Enter XXHR_PARTY_UPLOAD in the table name field and press Ctrl + F11 to execute the query.

How it works...

We can see that the table has been registered correctly using the script that we ran. This will mean that the table will be available to the integrator UI. The following screenshot shows the table we have registered in EBS, which means the scripts we ran have been completed successfully:

Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook A practical step-by-step guide to develop end-to-end extensions to Oracle E-Business Suite Release 12, with detailed illustrations and explanations with this book and ebook.
Published: March 2013
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

Creating a custom integrator

We are now going to create a custom integrator. To this we are to perform a number of actions. We will first need to define the integrator before, and then define the interface. We will then create a layout for the integrator will then show how to generate it. Once we have done this, we can then look at the editing attributes, amending titles, and adding things, like lists of values. In this article, we will therefore perform the following tasks:

  • Configure the integrator
  • Configure the interface
  • Create a layout for the integrator
  • Generate the integrator
  • Edit the attributes
  • Add the lists of values

Configure the integrator

We are now going to configure the integrator. There are different objects that an integrator can be based upon. In this example, we are going to base the integrator upon the table we created and registered in EBS.

How to do it...

To create a custom integrator, perform the following:

  1. Log in to Oracle with the Desktop Integration Manager responsibility.
  2. Select Create Integrator from the menu.
  3. In Step 1 of 5, in the Integrator Information region, enter the following details:
    • Integrator Name: Party Upload
    • Internal Name: XXHR_PARTY_UPLOAD
    • Application: XXHR Custom Application
    • Reporting Only: Unchecked
    • Enabled: Yes
    • Display in create document page: Checked
  4. When completed click the Next button.

Configure the interface

When we configure the interface, we will be defining a name for the interface and the type of the interface. For example, the type of the interface could be a PL/SQL procedure or based upon a database table that we can upload data to. We can also define the return values from the interface, such as an error message.

How to do it...

To create the interface, perform the following:

  1. In Step 2 of 5, in the Integrator Information region, enter the following details:
    • Interface Name: Party Upload
    • Interface Type: Table
    • Table Name: XXHR_PARTY_UPLOAD

  2. Click on Apply.

    The screen will then display a message to say that the interface has been created successfully, as shown in the following screenshot:

  3. If you now click on the radio button for the Party Upload interface, you will see that the attributes are imported from the table definition. The screen will refresh automatically and the attributes will be displayed in the Interfaces screenshot as follows:

  4. At this point, click on the Submit button and the integrator will be saved. When defining the integrator it is better to save it regularly as the screen quite often crashes. That's not to say that it will in your environment, as you may have a later version of the page. After the Submit button is clicked, the screen will return to the manage integrators page as shown in the following screenshot:

    This screen can be opened at any time by navigating to Desktop Integration Manager | Manage Integrators.

At this point, we have done all we need to do to create our integrator. We will add some more advanced features later, but let's now generate the integrator in its basic form. However, before we can do this, we need to create a layout for the integrator.

Create a layout for the integrator

We are now going to define the layout for the integrator. Before we can preview our integrator, we must create a layout. Here, we can define whether the attributes are formatted horizontally or vertically. We can also define the width of the fields for the headings.

How to do it...

To create a layout for the integrator, perform the following:

  1. Log in to oracle with the Desktop Integrator responsibility.
  2. Select Define Layout from the menu.
  3. In the Select Integrator screen, select the Party Upload integrator from the list of values, and click on the Go button as follows:

  4. In the next screen, click on the Create button.
  5. In the Layout Name field, type in Party Upload Layout and click on Next as follows.

    Leave Number of Headers as 1.

  6. Check the following records to be displayed in the integrator and leave the Placement field to Line for each of the checked attributes.

    In the next page, we can set some attributes about how the way the page behaves. We can choose to protect the sheet, change the stylesheet, or choose to apply filters for the page. We can also define how many rows are created for the integrator by setting a number for the data entry rows field. We can also define the order in which the fields are displayed to the user.

    Set the values as shown below:

    • Protect Sheet: Yes
    • Style Sheet : Default
    • Apply Filters: Yes
    • Data Entry Rows: 10

  7. Click on Apply.

How it works…

This screen allows us to create a layout for our integrator. We can change the order of attributes and set properties here as well.

Generate an integrator

We will now generate the integrator that will allow us to upload data into our table.

How to do it…

To create the interface, perform the following:

  1. Log in to Oracle with the Desktop Integration Manager responsibility.
  2. Select Manage Integrator from the menu.
  3. Type Party% in the Integrator Name field and click on the Go button.
  4. Select the Party Upload integrator and click on the Preview button as follows:

  5. In the Select Viewer screen, choose the version of Microsoft Excel from the list of values, and then click on the Next button.

  6. In the Document Creation Review window, check the summary of the values you have entered, and then click the Create Document button.

  7. The file will now be downloaded to your desktop. You will be prompted to download the file called the WebADI.xls, so click on the Open button.

  8. Once Microsoft Excel has opened, you may be prompted to Enable Editing. If so, click the Enable Editing button as follows:

  9. You may also be prompted with a Security Warning. If so, click the Enable Content button as follows:

    After a short while, you'll be prompted that the document has been created as shown in the following screenshot:

    Once the process is finished, you will see that the Microsoft Excel spreadsheet has created the integrator. We can see that the headings are displayed as we defined them in the layout for the integrator. Later, we will go back and edit the headings so that they are more meaningful for the user.

    We are now going to test the integrator, enter some dummy data into columns, and upload the data to ensure that the data is inserted into the database table. At present, we have not put any validation on the fields, so we can enter any data to test the upload process.

  10. Enter some data into each of the columns (just enter some data relevant to the field as shown in the next screenshot).
  11. Now click on the Oracle button to bring up a submenu.
  12. Now select the Upload item from the submenu.

  13. When the Upload to Oracle Applications dialogue screen is displayed, click on the Upload button as shown in the following screenshot:

    After a short period, Confirmation will be displayed as shown in the next screenshot. Dismiss the Confirmation screen by clicking the Close button as shown in the next screenshot. We can see that the Confirmation screen shows that the one row has been successfully uploaded. Later, we will go and check that the record has been inserted into the database table.

  14. Open SQL Developer, perform the following query to check that the row we uploaded using the integrator has been inserted into the database table:

How it works…

The integrator can be used to insert some data into a database table. At present, it is fairly basic, but later, we'll add some more advanced features to the integrator, such as lists of values and validation to some of the fields. We can even create our own custom upload screen, which will also be discussed later.

Edit the attributes

Now, we are going to edit the attributes in the integrator to have more meaningful headings and to use lists of values.

How to do it…

To edit the attributes for integrator, perform the following:

  1. Log in to Oracle with the Desktop Integration Manager responsibility.
  2. Select Manage Integrator from the menu.
  3. Type Party% in the Integrator Name field, and click on the Go button.
  4. Select the Party Upload integrator and click the Edit icon as shown in the following screenshot:

  5. In Step 1 of 5, click on Next.
  6. In Step 2 of 5, click on the XXHR Party Upload Int interface and wait for a short while for the attributes to be displayed.

    Once the attributes are displayed as shown in the following screenshot, we will edit the attributes by clicking on the update icon. We will do this to edit the headings of each of the attributes for our integrator.

    We can see that the value for Prompt Left is already set to COUNTRY. If our integrator had our attributes displayed vertically, we would edit this column to make the prompt more suitable for the user. However, we have to find our layout to display our attributes horizontally in the Excel spreadsheet. Therefore, we have to define the attribute heading by clicking the update icon and editing the Prompt Above field.

  7. For the COUNTRY attribute, click on the update icon.
  8. Scroll down the Interface Attribute Definition window until the Prompt Above field is displayed.
  9. Set the Prompt Above field to Country as shown in the following screenshot:

  10. When completed, click on the Save button.

    Now, repeat this process and add meaningful headings for all of the other attributes. For example, go to the P_PHONE attribute and change the Prompt Above field to Phone. We can also add text to be displayed to show the user formatting required, or indeed any other help text we feel may be required.

How it works…

We should now have meaningful headings for all of the attributes. This process should be done after we have tested that the integrator works properly. Also, it is advisable to update a few headings at a time and then save the integrator, as the interface occasionally does crash. If you are updating a large number of attributes, this can be rather annoying, as you have to start all over again.

Add the lists of values

In this article, will be editing the attributes to add a list of values. We want to do this where we want the users to enter structured data based upon a list of values. Therefore, a user can only input data that we want them to. This means that validation for valid values is automatically done for the user and we prevent the users entering erroneous data.

How to do it…

To add lists of values, perform the following:

  1. Log in as Desktop Integration Manager as we have done previously and query back the Party Upload integrator.
  2. In Step 1 of 5, click on Next.
  3. In Step 2 of 5, click on the XXHR Party Upload Int interface and wait for a short while for the attributes to be displayed, as we did in the previous receipe.

    The first list of values we are going to add will be a list of countries for the country attribute.

  4. Click on the update icon for the COUNTRY attribute.
  5. To add a list of values, enter the following details:
    • Validation Type: Table
    • ID Column: TERRITORY_CODE
    • Meaning Column: TERRITORY_SHORT_NAME
    • Desc Column: TERRITORY_SHORT_NAME
    • Validation Entity: FND_TERRITORIES_VL
    • Where Clause: 1=1 (Note: Where clause cannot be null that's why we put 1=1)
    • Lov Type: Pop List

    The following screenshot shows the Lov configuration as we have just defined it:

  6. Click on Save.

    Now, we are going to create another list of values for the PHONE_TYPE attribute.

  7. Click on the update icon for the PHONE_TYPE attribute.
  8. Set the following field values:
    • Validation Type : Table
    • ID Column: LOOKUP_CODE
    • Meaning Column: MEANING
    • Desc Column: DESCRIPTION
    • Validation Entity: FND_LOOKUP_VALUES
    • Where Clause: lookup_type = 'PHONE_LINE_TYPE' AND enabled_flag = 'Y'
    • Lov Type: Pop List

    We have now created a list of values for the PHONE_LINE_TYPE attribute. Now we are going to create another list of values for the salutation attribute. This attribute is called Title.

  9. Click on the update icon for the Title attribute.
  10. Set the following field values:
    • Validation Type: Table
    • ID Column: LOOKUP_CODE
    • Meaning Column: MEANING
    • Desc Column: DESCRIPTION
    • Validation Entity: AR_LOOKUPS
    • Where Clause: lookup_type(+) = 'CONTACT_TITLE' and enabled_flag = 'Y'
    • Lov Type: Pop List

  11. Click on Save.

How it works…

We use lists of values to minimize user data entry errors. We can preview the integrator to see the changes we have made. As we have done previously, log in to the Manage Desktop Integrator responsibility and query back the party upload integrator. Click on the Preview button and generate the integrator as we have done before. Once the WebADI.xls integrator has been created, we can see the titles we have edited. If we click on the title field, we can see that there is now a list of values that we can select from, as shown in the following screenshot:

Let's now create a new test record and upload that to the database. Add some dummy data to the first line of integrator using the lists of values which we created, and enter free text for the other fields. Click on Oracle upload to upload the data into the database table.

Now, switch to SQL Developer and perform the same query as we did previously. We can now see that there are two records in the database table. If we examine the second record closely, we can see that the PERSON_TITLE field has been formatted by the list of values. We can see the PHONE_TYPE field also has a formatted value returned from the list of values associated with the attribute in the integrator, as show in the following screenshot:

Now that we are happy with our integrator, we can save it to a desktop just as we would save any other Excel spreadsheet by navigating to File | Save As. Please note that we need to save the file as a type, Excel macro enabled template as shown in the following screenshot:

Summary

This article explained the process involved to integrate with EBS using desktop integrator. It has also put forward the procedure to use Excel-based spreadsheets to view and upload data to EBS.

Resources for Article:


Further resources on this subject:


Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook A practical step-by-step guide to develop end-to-end extensions to Oracle E-Business Suite Release 12, with detailed illustrations and explanations with this book and ebook.
Published: March 2013
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

About the Author :


Andy Penver

Andy Penver currently lives and works in the U.K. as a solution architect for a large public sector client. He studied at Christ Church (University of Kent). He has over 18 years of experience in working with Oracle E-Business Suite. He has worked in both the private and public sectors, and has a strong technical background. He has led and managed teams of consultants through the full project lifecycles on some very large programs throughout the U.K. and Europe. Andy has been heavily involved in two large-scale, award-winning implementations of a shared service centre.

Andy is currently the Managing Director of his own consultancy, NU-TEKK Limited.

Andy has previously authored and published a book called Oracle E-Business Suite R12 Core Development and Extension Cookbook.

Books From Packt


Mastering Oracle Scheduler in Oracle 11g Databases
Mastering Oracle Scheduler in Oracle 11g Databases

 Oracle 11g Anti-hacker's Cookbook
Oracle 11g Anti-hacker's Cookbook

 Instant Oracle Database and PowerShell How-to [Instant]
Instant Oracle Database and PowerShell How-to [Instant]

Oracle Enterprise Manager 12c Administration Cookbook
Oracle Enterprise Manager 12c Administration Cookbook

 Oracle JDeveloper 11gR2 Cookbook
Oracle JDeveloper 11gR2 Cookbook

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

 Oracle Information Integration, Migration, and Consolidation
Oracle Information Integration, Migration, and Consolidation

Oracle E-Business Suite R12 Supply Chain Management
Oracle E-Business Suite R12 Supply Chain Management


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
t
q
2
d
L
z
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software