How to define a Web ADI Custom Integrator:

Oracle E-Business Suite has a powerful desktop integration tool known as Oracle Web Applications Desktop Integrator, called as Web ADI. Oracle Web ADI is a framework that provides Oracle E-Business Suite integration with desktop utilities like Excel, Word, and Project. Web ADI allows data to be uploaded and downloaded from Oracle E-Business Suite using desktop applications. Users can create custom integrators to load data into interface or staging tables using Excel or Word. Integrators to load data into interface or staging tables using Excel or Word. This article explains with an example how to define an integrator whose importer returns error messages for any spreadsheet rows that failed to import. Create Integrator Define Layout Create Document Upload Document Before creating custom integrator, we have to create a staging table, Sequence and a wrapper procedure or function, which will be called from excel spreadsheet and inserts data into staging table. An advantage of using wrapper procedure is we can write custom business/user logic/validations, which will validate data entered in excel before inserting into interface tables. Error output of records failed during validation/insertion will be notified in excel spreadsheet for each line. Please see Appendix A for scripts Create Integrator: The Integrator defined in this example performs these operations Creates a data document as a formatted spread sheet on the desktop Validates the entered data before upload Uploads data from the document to the staging table Processes the data, returns the Error or Success Message Log into Oracle E-Business Suite as a user with access to the ‘Desktop Integration Manager’ responsibility. Select the Create Integrator menu option.
A meaningful name and an internal name are required to be given. Enter a unique internal name for the integrator. Do not select the Reporting Integrator option, because this integrator will not be able to upload the data back to the database. Assign the ‘Desktop Integration – Create Document’ security rule (internal code BNE_ADI_CREATE_DOCUMENT) to the sample integrator. This security rule is attached to the ‘Desktop Integration’ responsibility menu.
Click the Next button to continue with defining the interface. Provide the Interface Details. Interface Name: UPLOAD_BUDGETS Interface Type: Function Package Name: ERPS_BUDGET_UPLOAD Procedure/Function Name: main Return Type: VARCHAR2 Check the Retrieve error information check box in order to display the error messages in the created Web Adi document. API Returns: Error Message Press Apply.
Oracle E-Business Suite Desktop Integrator displays the attributes when you select the interface. Interface attributes can be updated and deleted. You typically update interface attributes to: Check or Uncheck the Enable and Display Option Add Default values Sequence: The sequence is used to make sure each upload operation will set a unique identifier for the rows being uploaded. Constant: A fixed text Environment Variable: Sysdate Define LOV values and prompts
For creating LOV’s we need to first create Components that has to be associated with the LOV. Creating Components:
Press on Create Component Button to create a component
The details in the Component Definition have to be specified. Press Apply.
After specifying the Parameter Name and Parameter Value details press Apply
After creating the component press the Update Icon of the interface attribute to which you want to add the Lov and press Apply after the details are mentioned as in the below screen shot along with the component name that we have created above.
Likewise we can create LOV’s for the interface attribute fields. Press Next.
Press Next ignoring the Content creation that we will create later. Create an Uploader The uploader defines conditions that affect the upload operation at runtime, when the integrator is executed. Select “From Template” option from the uploader LOV and press create that creates an uploader.
The parameters created by the template are basic uploader parameters. If these parameters are not in the uploader, Oracle Web Applications Desktop Integrator will assume default values at integrator runtime. We can update the Parameters default values.
Press Next.
Submit the Integrator definition in order to save it. Define Layout A layout will decide which columns will be displayed in the desktop application document and how those columns will be displayed, including their formatting. Go to Desktop Integration  Define Layout.
Select the integrator in the list and click the Define Layout button. In the Layout page, select the integrator name, and click the Go button.
Click the Create button to create the first layout.
Enter the Layout Name and Number of Header Columns
Press Next. For the required fields change the placement from Context to Line and click Next. Define the order of the fields using Move Up and Move Down icons. You can also define the width of the cells on this page.
Click Apply to save the layout definition. Make sure the Macros are enabled in Excel sheet to get the Add-Ins to upload the data before you Create the Document. The Excel Settings are: Open Microsoft Excel –> File –> Options –> Trust Center –> Trust Center Settings
Make sure to check “Allow documents on a network to be trusted”.

Check only “Enable all controls without restrictions and without prompting”. All other options should be unchecked.
Check “Enable all macros” and “Trust access to the VBA project object model” options.
Check only “Enable Protected View for Outlook attachments” and “Enable Data Execution Prevention mode” options. All other options should be unchecked.

Check “Show the Message Bar in all applications when achieve content, such as ActiveX controls and macros, has been blocked”.
Check the “Enable all Data Connections” and “Enable automatic update for all Workbook Links” options only.
Create Document Go to Desktop Integration Manger Responsibility and click on Create document to run the integrator to create the document template.

Enter all the required fields and number of rows, after the Formatted spread sheet downloaded using Create Document. Click on Add-Ins in Menu bar and click Upload option. Check the required options to validate only the flagged rows. This will check the defined validations in the Interface. Click on Upload button.

The upload process starts. You will see some upload progress messages. At the end, if the integrator has been well defined, you will receive a successful upload message indicating the number of uploaded records.
The data gets uploaded into the database table successfully. Appendix CREATE SEQUENCE erps_budget_batch_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER CACHE 20 / CREATE TABLE erps_upload_budgets_stg ( ledger_id NUMBER NULL, submitted_by NUMBER NULL, budget_owner NUMBER NULL, approver VARCHAR2(250) NULL, budget_type VARCHAR2(250) NULL, budget_category NUMBER NULL, budget_name VARCHAR2(250) NULL, parent_budget VARCHAR2(250) NULL, description VARCHAR2(350) NULL, budget_amount NUMBER NULL, budget_holdback_amount NUMBER NULL, currency VARCHAR2(100) NULL, start_date_active DATE NULL, end_date_active DATE NULL, territory VARCHAR2(150) NULL, product_eligibility VARCHAR2(150) NULL, budget_currency VARCHAR2(150) NULL, account_type VARCHAR2(150) NULL, account VARCHAR2(150) NULL, org_id NUMBER NULL, responsibility_id NUMBER NULL, creation_date DATE NOT NULL, created_by NUMBER NOT NULL, last_update_date DATE NOT NULL, last_updated_by NUMBER NOT NULL, last_update_erpsn NUMBER NULL, attribute1 VARCHAR2(250) NULL, attribute2 VARCHAR2(250) NULL, attribute3 VARCHAR2(250) NULL, attribute4 VARCHAR2(250) NULL, attribute5 VARCHAR2(250) NULL, status VARCHAR2(250) NULL, batch_id NUMBER NULL, fund_id NUMBER NULL, action VARCHAR2(100) NULL, budget_category_name VARCHAR2(120) NULL, budget_owner_name VARCHAR2(360) NULL, ledger VARCHAR2(150) NULL ) / CREATE OR REPLACE package erps_budget_upload as function MAIN ( P_SUBMITTED_BY VARCHAR2 , P_LEDGER_ID VARCHAR2 , P_BUDGET_OWNER VARCHAR2 , P_APPROVER VARCHAR2 , P_BUDGET_TYPE VARCHAR2 , P_BUDGET_CATEGORY VARCHAR2 , P_BUDGET_NAME VARCHAR2 , P_DESCRIPTION VARCHAR2 , P_PARENT_BUDGET VARCHAR2 , P_BUDGET_AMOUNT VARCHAR2 , P_BUDGET_HOLDBACK_AMOUNT VARCHAR2 , P_CURRENCY VARCHAR2 , P_START_DATE_ACTIVE DATE , P_END_DATE_ACTIVE DATE , P_STATUS VARCHAR2 , P_BATCH_ID NUMBER ) return varchar2; end erps_BUDGET_UPLOAD; / CREATE OR REPLACE package body erps_budget_upload as function MAIN ( P_SUBMITTED_BY VARCHAR2 , P_LEDGER_ID VARCHAR2 , P_BUDGET_OWNER VARCHAR2 , P_APPROVER VARCHAR2 , P_BUDGET_TYPE VARCHAR2 , P_BUDGET_CATEGORY VARCHAR2 , P_BUDGET_NAME VARCHAR2 , P_DESCRIPTION VARCHAR2 , P_PARENT_BUDGET VARCHAR2 , P_BUDGET_AMOUNT VARCHAR2 , P_BUDGET_HOLDBACK_AMOUNT VARCHAR2 , P_CURRENCY VARCHAR2 , P_START_DATE_ACTIVE DATE , P_END_DATE_ACTIVE DATE , P_STATUS VARCHAR2 , P_BATCH_ID NUMBER ) return varchar2 AS v_err_msg varchar2(2000); BEGIN IF P_END_DATE_ACTIVE < P_START_DATE_ACTIVE THEN v_err_msg := ‘Start date should be greater than end date’; RETURN v_err_msg; END IF; INSERT INTO erps_UPLOAD_BUDGETS_STG ( SUBMITTED_BY , LEDGER , BUDGET_OWNER_NAME , APPROVER –, BUDGET_TYPE , BUDGET_CATEGORY_NAME , BUDGET_NAME , DESCRIPTION , PARENT_BUDGET , BUDGET_AMOUNT , BUDGET_HOLDBACK_AMOUNT , CURRENCY , START_DATE_ACTIVE , END_DATE_ACTIVE , STATUS , BATCH_ID , CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATED_BY , LAST_UPDATE_erpsN ) values ( to_number(P_SUBMITTED_BY) , P_LEDGER_ID , P_BUDGET_OWNER , P_APPROVER –, P_BUDGET_TYPE , P_BUDGET_CATEGORY , P_BUDGET_NAME , P_DESCRIPTION , P_PARENT_BUDGET , P_BUDGET_AMOUNT , P_BUDGET_HOLDBACK_AMOUNT , P_CURRENCY , P_START_DATE_ACTIVE , P_END_DATE_ACTIVE , P_STATUS , P_BATCH_ID , SYSDATE , fnd_global.user_id , SYSDATE , fnd_global.user_id , fnd_global.user_id ); RETURN NULL; EXCEPTION WHEN OTHERS THEN RETURN v_err_msg||SQLERRM; END; end erps_BUDGET_UPLOAD; /