Simple Custom WebADI Integrator with 12.1.2 Desktop Integration Manager

Desktop Integrator (WebADI) Integrates Oracle Ebusiness suite with desktop applications like Excel and Word. Starting from Release 12.1.2, Oracle introduced new responsibility 'Desktop Integration Manager' which allows developers to create custom web adi using UI.

Below are the simple steps to create.
Step1: Create custom table and sequence(In case if you want to pass sequential number to any columns)


create table xxsw_testadi_tbl
( seq_no NUMBER,
  item_number VARCHAR2(240),
 pos_uom VARCHAR2(1),
 status VARCHAR2(20)
);

create sequence xxsw_test_seq;

Step2: While inserting records from web adi either an API can be used to perform any business validation or insert directly into table. Assuming that we use API in most cases. Code here to create sample package procedure. Note that for every record in your Excel, this package will be executed.



CREATE OR REPLACE PACKAGE xx_testadi_pkg
AS
   PROCEDURE LOAD_ADI (p_seq_no        IN     NUMBER,
                       p_item_number   IN     VARCHAR2,
                       p_pos_uom       IN     VARCHAR2,
                       p_status        IN     VARCHAR2,
                       errbuf             OUT VARCHAR2,
                       retcode            OUT NUMBER);
END xx_testadi_pkg;
/
CREATE OR REPLACE PACKAGE BODY XX_TESTADI_PKG
AS
   PROCEDURE LOAD_ADI (p_seq_no        IN     NUMBER,
                       p_item_number   IN     VARCHAR2,
                       p_pos_uom       IN     VARCHAR2,
                       p_status        IN     VARCHAR2,
                       errbuf             OUT VARCHAR2,
                       retcode            OUT NUMBER)
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     --Business Validations can be added here
      INSERT INTO xxsw_testadi_tbl
           VALUES (p_seq_no,
                   p_item_number,
                   p_pos_uom,
                   p_status);

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         raise_application_error (
            -20116,
            'Error - Cannot insert/update size code mappings');
   END LOAD_ADI;
END xx_testadi_pkg;



Step3: Create a Form Function
Navigation --> Application Developer --> Application --> Function

Function: XXSW_TEST_ADI
User Function Name: XXSW_TEST_ADI
Type: SSWA servlet function
HTML Call: BneApplicationService
Save and Close
Note: Parameter has to update with Integrator code and Layout. Will come back here later.

Step4: Add the Function to Menu. Save and Close

Step5.1: Define integrator.
Goto Desktop Integration Manager --> Create Integrator and Enter values

Integrator Name: XXSW_TEST_ADI

Internal Name: XXSW_TEST_ADI

Application: Custom Application

Enabled: Yes

Display in Create Document Page: Yes(Check Box)

Add these two functions: 

--> Desktop Integration - Create Document (BNE_ADI_CREATE_DOCUMENT)

--> XXSW_TEST_ADI

Click NEXT

Step5.2: Define Interface

Interface Name: XXSW_TEST_ADI_INT
Interface Type: API - Procedure
Note: Table and API-Function are also available here. Since we wrote a package procedure, use the API-Procedure option
Package Name:XX_TESTADI_PKG
Procedure/Function Name: LOAD_ADI
API Returns: Error Message
Note: FND Message Code also available here. In Order to use this, create message in application developer and use it in package
Note: Make sure you click on 'Apply' before clicking on 'Next'

Step5.3: Click the 'Select' Radio Button of Interface XXSW_TEST_ADI_INT and wait for few seconds
in order to refresh the page

--> This will display all the IN and OUT parameters of the package procedure. Delete the
      OUT attributes using Delete button
--> Change the Prompt if Required.
--> Click on Update and Select 'Not Null' check box in order make mandatory during upload
Click NEXT twice to go Step 4.
Step5.3: Define Uploader
Uploader: From Template
Uploader Name: XXSW_TEST_WEBADI - Uploader
Upload Parameters:  For bne:Validation, enable the check box for Displayed, Enabled and Required. For rest of them Uncheck

Skip the Importer Section
Click  Submit.

Step5.4: Define Layout

Navigation: Desktop Integration Manager -> Manage Integrators -> Define Layout -> Select the Integrator

--> XXSW_TEST_ADI --> Click GO and CREATE

Layout Name: XXSW_TEST_ADI_LAYOUT
Number of Headers:1
NEXT
Change the Placement to 'Line'
In order to generate a sequence number for column SEQ_NO, write sql in Default
Value field and choose SQL as Default Type
select xxsw_test_seq.NEXTVAL from dual

APPLY

Step 6: Update Function Parameters
Run the below SQL Query:
SELECT INTEGRATOR_APP_ID || ':' || INTEGRATOR_CODE INTEGRATORS, APPLICATION_ID || ':' || LAYOUT_CODE LAYOUTS FROM BNE_LAYOUTS_B WHERE INTEGRATOR_APP_ID = 20003 ORDER BY 1, 2

From the Results of above sql, copy the Integrator and layout to below string:
bne:page=BneCreateDoc&bne:integrator=20003:XXSW_TEST_ADI_XINTG&bne:layout=20003:XXSW_TEST_ADI_LAYOUT

Goto Application Developer --> Application --> Function --> Query for XXSW_TEST_ADI
function --> 'Form' tab, Enter the above string and SAVE

Setups are completed now. Its time to test