Custom Web ADI

How to import data into custom table using Oracle Web ADI?

What is Web ADI (Applications Desktop Integration)?
Web Applications Desktop Integration (Web ADI) is an enterprise framework for integrating the Oracle E-Business Suite with desktop applications. It’s a Self-Service Oracle Application that lets you download and upload selected data between EBS products and Microsoft Excel. It allows you to modify bulk data in Microsoft Excel, working either online or offline, and upload the modified data using Web ADI. It also allows you to generate Microsoft Word documents for mail merges.

What’s Coming for Web ADI?
Are you interested in integrating EBS application with desktop application using Oracle’s Web ADI framework? Are you interested in creating custom Web ADI integrators with the ability to define validations, lookups, and business rules? Well, you’re in the right place!
We will show you step by step setup process to integrate Web ADI for custom table so that you can import your excel data into custom table.
==============================
Step1: Create custom table
==============================

 CREATE TABLE XX_IMPORT_DATA
(
  ATTRIBUTE1   VARCHAR2(100 BYTE),
  ATTRIBUTE2   VARCHAR2(100 BYTE),
  ATTRIBUTE3   VARCHAR2(100 BYTE),
  ATTRIBUTE4   VARCHAR2(100 BYTE),
  ATTRIBUTE5   VARCHAR2(100 BYTE),
  ATTRIBUTE6   VARCHAR2(100 BYTE),
  ATTRIBUTE7   VARCHAR2(100 BYTE),
  ATTRIBUTE8   VARCHAR2(100 BYTE),
  ATTRIBUTE9   VARCHAR2(100 BYTE),
  ATTRIBUTE10  VARCHAR2(100 BYTE)
);
==============================
Step2: Register custom table
==============================
BEGIN

    –Register table using (ad_dd.register_table and ad_dd.register_column)
    XX_FND_UTIL_PKG.REGISTER_DB_OBJECT(P_APPL_SHORT_NAME =>’OKC’,
                                      P_OBJECT_NAME =>upper(‘XX_IMPORT_DATA’),
                                      P_OBJECT_TYPE =>’T’,
                                      P_START_SEQ =>1,
                                      P_INTERVAL =>1
                                      );                                     
END;
=====================================================================================
Step3: Create Web ADI Integration using BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR
           Create Web ADI Interface using BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_TABLE
       Create Web ADI default layout using BNE_INTEGRATOR_UTILS.CREATE_DEFAULT_LAYOUT
=====================================================================================

DECLARE

   P_INTEGRATOR_CODE        VARCHAR2 (30);
   P_INTERFACE_CODE         VARCHAR2 (30);
   P_LAYOUT_CODE            VARCHAR2 (30);
   P_APPLICATION_ID         NUMBER (15);
   P_OBJECT_CODE            VARCHAR2 (20);
   P_INTEGRATOR_USER_NAME   VARCHAR2 (240);
   P_LANGUAGE               VARCHAR2 (4);
   P_SOURCE_LANGUAGE        VARCHAR2 (4);
   P_USER_ID                NUMBER (15);
   P_INTERFACE_TABLE_NAME   VARCHAR2 (50);
   P_INTERFACE_USER_NAME    VARCHAR2 (240);
   P_FORCE                  BOOLEAN;
   P_ALL_COLUMNS            BOOLEAN := FALSE;

BEGIN

   P_APPLICATION_ID         := 510;
   P_LANGUAGE               := ‘US’;
   P_SOURCE_LANGUAGE        := ‘US’;
   P_USER_ID                := 1137;
   P_OBJECT_CODE            := ‘XX_IMPORT_DATA’;
   —ADI details
   P_INTEGRATOR_USER_NAME   := ‘XX Custom Import’;
   P_INTERFACE_TABLE_NAME   := ‘XX_IMPORT_DATA’; — Name of the Database staging table.
   P_INTERFACE_USER_NAME    := ‘XX Custom Import’;

   — Name of the table that will be displayed in
   P_FORCE                  := FALSE;
   P_ALL_COLUMNS            := FALSE;

 
    –Create Integrator
     BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR (P_APPLICATION_ID,
                                           P_OBJECT_CODE,
                                           P_INTEGRATOR_USER_NAME,
                                           P_LANGUAGE,
                                           P_SOURCE_LANGUAGE,
                                           P_USER_ID,
                                           P_INTEGRATOR_CODE);

  
   –Create Interface
   BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_TABLE (P_APPLICATION_ID,
                                                    P_OBJECT_CODE,
                                                    P_INTEGRATOR_CODE,
                                                    P_INTERFACE_TABLE_NAME,
                                                    P_INTERFACE_USER_NAME,
                                                    P_LANGUAGE,
                                                    P_SOURCE_LANGUAGE,
                                                    P_USER_ID,
                                                    P_INTERFACE_CODE);

  
   –Create Default Layout
   BNE_INTEGRATOR_UTILS.CREATE_DEFAULT_LAYOUT (P_APPLICATION_ID,
                                               P_OBJECT_CODE,
                                               P_INTEGRATOR_CODE,
                                               P_INTERFACE_CODE,
                                               P_USER_ID,
                                               P_FORCE,
                                               P_ALL_COLUMNS,
                                               P_LAYOUT_CODE);
 

   COMMIT;

exception
when others then
dbms_output.put_line(sqlerrm);
END;
==============================
Step4: Create Document
==============================
4.1 Logon to oracle application using Oracle Web ADI Responsibility and select option “Create Document”


4.2 Select your excel version from the list.

4.3 Select custom integrator name “XX Custom Import” from the list.


4.4 Use default layout.


4.5 Use default Content.


4.6 Click on the “Create Document” button.


4.7 Now Web ADI program creating data template.

4.8 Enter dummy data and try to upload using “Upload” option.

4.9 Web ADI Data upload utility will show you confirmation message with record status.

4.10 Now verify data in custom table using SQL command.

 


Now your custom Web ADI integration is ready to go!

This entry was posted in Oracle E-Business Suite, Web ADI.

6 Responses to Custom Web ADI

  1. Earle says:

    good information, incredible webpage design, carry on the good work

  2. Raj says:

    Thanks for that posting. I also developed a custom integrator using the above steps. I want to show the error message from the backend APIs after the records are processed. For ex, if the 2nd row failed validation, i want to show that in the messages column of the 2nd row. Similarly, the success message if the row was inserted successfully. How do you implement this?

  3. Sophia says:

    Excellent information. I have a question…

    Do we need to create these definitions in apps schema?

    Thanks

  4. Tushar says:

    I have created the custom table
    registered the table and columns in Apps
    created custom integrator,interface with api
    when i select the integrator in webadi responsibility and try to create the layout it does not display column list.
    Also if I go ahead with the default layout I get the excell sheet created with 0 columns.
    can you please help me why I do not get the column list in web adi layout page or in the excell docuement

  5. jjsai says:

    Fantastic info, THANKS for sharing