Export data from Excel to Table using custom Web ADI Integrator

Case: User needs a function XXFunc in responsiblity XXResp which creates Excel document and allows user to upload data from Excel sheet to custom Table.

Pre-Req

For Microsoft Excel 2002 or 2003:
1.From the Tools menu, select Macro, then Security, and then Security Level.
2.In the Security Level tab, select the High option.
3.In the Trusted Sources or Trusted Publishers tab, select the Trust access to Visual Basic Project option.

For Microsoft Excel 2007:
1.Choose the Microsoft Office button, and then choose Excel Options. In the Trust Center category, choose Trust Center Settings, and then the Macro Settings category.
2.Select the Disable all macros except digitally signed macros option.

3.Select the Trust access to the VBA project object model option.

Set profile option 'BNE Allow No Security Rule' to Yes at user level. The default value is No.

Add responsibilities 'Desktop Integration Manager', 'Desktop Integrator' to the current user.

Steps

1.Create custom table 'XX_ONT_WEBADI_TEST'. Create custom API 'XX_ONT_WEBADI_TEST_PKG' which validates and inserts data into custom table.
2.Create Integrator 'XX ONT ADI Test Integrator'.
3.Create Interface 'XX ONT ADI Test Interface' to the above integrator and set custom API 'XX_ONT_WEBADI_TEST_PKG' to the interface.
4.Create Contents(Optional).
5.Create Layout 'XX ONT ADI Test Layout' and set to integrator 'XX ONT ADI Test Integrator'.
6.You can test at this stage from Desktop Integrator responsiblity.

7.Create Function 'XX ONT ADI Test Func' and set integrator to the function. Attach this function to the menu under resposibility.

8.Navigate to Resposibility->Menu->XX ONT ADI Test Func. Opens OA Page. Click on Create Document button opens Excel Sheet.

 

Detailed Steps

1.Create custom table 'XX_ONT_WEBADI_TEST'. Create custom API 'XX_ONT_WEBADI_TEST_PKG' which validates and inserts data into custom table. Paremeters in procedure Load_Prc() should be the columns in the table which you want to update using Web ADI integrator. 

2.Create Integrator 'XX ONT ADI Test Integrator'.

Navigation: Desktop Integration Manager(R)->Create Integrator
Enter name, internal name and application.

Must select 'Display In Create Document Page' checkbox which allows current integrator available for creating document(Excel). Click Next.

3.Create Interface 'XX ONT ADI Test Interface' to the above integrator and set custom API 'XX_ONT_WEBADI_TEST_PKG' to the interface. Click Apply.

In next screen, click Select radio button of interface 'XX ONT ADI Test Interface' and wait. This reads API given in the interface and loads procedure parameters as attributes. Click Next.

4.Create Contents(Optional). Click Submit.

5.Create Layout 'XX ONT ADI Test Layout' and set to integrator 'XX ONT ADI Test Integrator'.

Navigation: Desktop Integrator(R)->Define Layout

Select integrator 'XX ONT ADI Test Integrator' from dropdown. Click Go.

This screen shows exisitng layouts if there are any. Click on Create button to create one.

Provide layout name as 'XX ONT ADI Test Layout' and select no.of headers 1. Click Next.

This picks integrator attributes as layout fields. Select Placement as 'Line' and provide default values if there are any required. Click Apply.

Layout created successfully.

6.Test from Desktop Integrator responsiblity.

Navigation: Desktop Integrator(R)->Create Document

Select integrator as XX ONT ADI Test Integrator. Click Next.

In Review stage, Click Create Document button. This opens popup window to Open/Save a file WebADI.xls. Click Open button.

This opens Excel sheet with format given in layout 'XX ONT ADI Test Layout'. Close Download OA window.

Enter Data into excel sheet. To upload data into table, navigate to Add-Ins(M)->Oracle->Upload from excel sheet menu on top. This opens Upload window. Click on Upload button. Once data is successully uploaded, excel sheet shows green icon for each line indicates no errors in upload.

Check same from backend.

7.Create Function 'XX ONT ADI Test Func' and set integrator to the function. Attach this function to the menu under resposibility.

Navigation: Application Developer(R)->Application->Function

Create function as shown in below screens.

In the next screen you need to set form parameter. Modify below string and set correct integrator, layout and content details.

bne:page=BneCreateDoc&bne:language=US&bne:reporting=N&bne:viewer=BNE:EXCEL2000,2003,2007&bne:integrator=XXONT:XXGF_TEST1_INTG_INTG&bne:layout=XXONT:XXGF_TEST1_INTG_DFLT&bne:content=XXONT:XXGF_TEST1_INTG_CNT

Add function 'XX ONT ADI Test Func' to menu. Prompt given as 'ADI Test Load'.

8.Navigate to Resposibility->Menu->ADI Test Load. This opens OA page. Click on Create Document button to open new excel sheet with layout.

Oracle Web ADI

Oracle WEB ADI- Oracle Web Application Desktop Integrator  

• Wed ADI automatically imports data into your Web ADI spreadsheets from the Oracle E-Business Suite or from a text file. Imported information can be quickly modified in Excel, validated, and uploaded to the Oracle E-Business Suite.
• This feature can be useful when migrating data from a legacy system to the Oracle E-Business Suite.
• All data in the spreadsheet can be validated against Oracle E-Business Suite business rules before it is uploaded.
• You can use the layout functionality to determine what fields appear in your spreadsheet, where they appear, and if they contain default values. These definitions can be saved, reused, and modified as needed.

Concepts

Integrator – This is the definition that stores the information about the action the user wishes to perform. For example, downloading specific data to a spreadsheet for viewing or to modify and upload back to the database. For ADE sers, this equated to the Style.

API – Application Programming Interface. This is the pl/sql interface by which data is validated and uploaded into Oracle HRMS. You would associate an api with an integrator if it was intended to either create new data in the application or update data that had been previously downloaded. See Oracle HRMS Configuring,Reporting and System Administration manual for a list of supported apis.

View – A view is an object by which you can query data on a table or tables. If the action you are intending to perform involves downloading data, you must associate a view with the integrator. If you are using Create style APIs, you do not require a view. You can elect to use a seeded view, for example, PER_PEOPLE_V, to use with your integrator. However, if the integrator is for updating data, then it is recommended that you create your own views and remember to include the OBJECT_VERSION_NUMBER, and any other In/Out parameters used by your chosen API.

One thing to beware of is the use of Aliases in views if you are downloading from a form. For example,if you use an alias for Applicant_Name called Starter, and then use a restrictive query in the form on Applicant Name before running the integrator in Web ADI, you will get the following error in the BNE log.

BneBaseSQL.executeBneQuery: Exception while running query. Error Code: 904, Message: ORA-00904: "APPLICANT_NAME": invalid identifier

The column APPLICANT_NAME has been overwritten by the alias in the definition loaded into the BNE tables.

Layout – This is where the user selects the columns to be displayed in the spreadsheet or Word document from the API and/or View used by the integrator. An integrator can have more than one layout defined for it. You can choose which one to use when you create your document.

Mapping – the mapping definition links the data source to the api columns. If no view is specified against the integrator or no text file used to load data, then no mapping is needed. When the data source is a view, the mapping is created automatically, however if a text file is being used then a mapping needs to be manually defined to associate each column in the file (source) to the relevant api parameter (target). See example c) in section, A Step by Step Guide to Creating HRMS Integrators. 

Pre-req

For Microsoft Excel 2002 or 2003:

1.From the Tools menu, select Macro, then Security, and then Security Level.
2.In the Security Level tab, select the High option.

3.In the Trusted Sources or Trusted Publishers tab, select the Trust access to Visual Basic Project option.

For Microsoft Excel 2007:
1.Choose the Microsoft Office button, and then choose Excel Options. In the Trust Center category, choose Trust Center Settings, and then the Macro Settings category.
2.Select the Disable all macros except digitally signed macros option.
3.Select the Trust access to the VBA project object model option.

Set profile option 'BNE Allow No Security Rule' to Yes at user level. The default value is No.

Add responsibilities 'Desktop Integration Manager', 'Desktop Integrator' to the current user.

Debugging

You can get a log file to help diagnose an error in Web ADI. The following profile options need to be set:

BNE: Server Log Filename: Use this profile option to change the name of the Web ADI log file on the middle tier. If this profile option is not set, bne.log is used.

BNE: Server Log Path: This profile option can be used to set the directory for the Web ADI log file on the applications server. If this profile option is not set, then the FND_TOP/log directory is used.

BNE: Server Log Level: This profile option determines the level of detail that is recorded in the Web ADI log file. The valid values are noted below. The level of granularity and amount of information recorded to the log file increases as you move down the list.

• CRITICAL_ERROR: - Messages that are the result of a system failure. The integrity and reliability of the system is in doubt as a result of this error.
• ERROR: - Messages that are the result of an unexpected error in the system. It is possible to recover from these errors; the system may still be in a usable state. Setting the profile option to this value will include CRITICAL_ERROR messages in the log file.
• WARNING: - Messages trapped by the application.These errors were handled by the application but the system administrator should be aware of them occurring. Setting the profile option to this value will include ERROR and CRITICAL_ERROR messages as well.
• INFORMATION: - Additional messaging is added to the log file that includes processing information. Setting the profile option to this value includes CRITICAL_ERROR, WARNING, and ERROR log messages. This is the setting that is used by Web ADI if the profile option is not set at the site level.
• DETAIL: - Messages that summarize what is written to the log file. Setting the profile option to this value includes CRITICAL_ERROR, WARNING, ERROR, and INFORMATION log messages.
• TRACE: - Messaging that generated detailed debugging statements. Setting the profile option to this value includes CRITICAL_ERROR, WARNING, ERROR, INFORMATION and DETAIL log messages.  

Main Tables and API

BNE_INTEGRATORS_TL
BNE_INTERFACES_TL
BNE_INTERFACE_COLS_TL
BNE_CONTENTS_TL
BNE_MAPPINGS_TL
BNE_LAYOUTS_TL

BNE_LAYOUT_COLS

API to create custom web adi programatically..

BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR
BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_API
BNE_INTEGRATOR_UTILS.CREATE_DEFAULT_LAYOUT
BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV