New Whitepaper: Defining Web Applications Desktop Integrators That Return Error Messages

Oracle Web Application Desktop Integrator (Web ADI) is Oracle E-Business Suite's solution for integrating E-Business Suite applications with desktop applications such as Microsoft Excel, Word and Projects.  "Integrators" encapsulate the metadata and other information needed to integrate a particular Oracle E-Business Suite task with a desktop application.  You can use the Desktop Integration Framework (DIF) to create custom integrators for Oracle Web ADI in Oracle E-Business Suite Release 12.1.2. The ability to create custom importers was added in EBS 12.1.3.

I am pleased to announce the release of a new white paper that provides a step-by-step tutorial on how to use the Desktop Integration Framework to define a Microsoft Excel-based integrator.  The example in the tutorial shows how to define an importer that returns error messages for any spreadsheet rows that failed to import into an E-Business Suite database. It describes the steps in 3 phases: 

  • Preparing that database and application objects

This phase provides the sample code to create a custom table and PL/SQL package which will be used for importing the data from a Microsoft Excel spreadsheet into an E-Business Suite table. It also describes the steps to create an FND Lookup code and its value, which will be used to map error codes and their corresponding messages.

  • Phase A: Defining an integrator that downloads and uploads data

This phase provides the steps to create a basic integrator using the Web ADI Desktop Integration Framework. It describes the steps to define the integrator's Interface, Content, Uploader, Layout, and Mapping.

  • Phase B: Defining an integrator importer that returns error messages

This phase provides the steps to define importer. It extends the integrator definition to process the data uploaded in the interface table and return error messages back to the desktop document for any rows that failed to import.

Screenshot of SQL query definition screen

The intended audience of this document is custom desktop integrator developers who are familiar with Oracle E-Business Suite and Oracle Web Applications Desktop Integrator.

Your feedback is welcome

We are very interested in hearing about your experiences with this new tool.  Please post your comments here or drop me an email at email.jpg

Download the new whitepaper

The white paper is available in two places -- Oracle Learning Library (OLL) and My Oracle Support:

References

Related Articles

Comments:

Will this work if you are not using a table based interface? I am using an API to upload data to a table and seem to be unable to link the upload rows to the error rows returned by importer. I'm noticing that my upload rows use the 'PROCEDURE.ROW_ID' format and the error rows use 'TABLE.ROW_ID'.

Posted by guest on July 24, 2012 at 07:21 AM PDT #

Dear Guest,

Yes, you can return error messages for API based interface as well. Ensure to define a document row: interface attribute mapping importer rule as part of Importer step of Integrator wizard.

You may refer "To define a document row: interface attribute mapping importer rule" section in Oracle E-Business Suite Desktop Integration Framework Developer's Guide (Part No: E15877-02).

Thanks,
Rekha

Posted by Rekha Ayothi on July 25, 2012 at 12:03 AM PDT #

I thought the process would get more tedious and intricate but thanks to you for providing how to use the Desktop Integration Framework to define a Microsoft Excel-based integrator. I am likely to run it and would come back with some more inputs from my end.....

Posted by php web development company on July 26, 2012 at 04:45 AM PDT #

Hi,
I did test followed the PDF file, all things are ok except the last step.
If I didn't do "Error Row Definition" and "Error Message Lookup" rules setup, all things are ok.
All data in table are OK.
When I defined "Error Row Definition" and "Error Message Lookup" rule, and tested the demo, I will get the error "An error has occurred while running an API import. The ERRORED_ROWS step 20003:ER_500165, parameter number 1 must contain the value BIND in attribute 1.";
Please help.

Posted by Ben on September 11, 2012 at 02:31 AM PDT #

Hi, Ben,

I'm sorry to hear that you've encountered an issue with this.

We can provide general conceptual guidance here, but I'm afraid that this blog isn't the best place to get technical support for specific issues like the one that you're working through.

Your best bet would be to log a formal Service Request via My Oracle Support (formerly Metalink) to get one of our specialists engaged.

Please feel free to forward your Service Request number to me if it gets stuck in the support process for some reason.

Regards,
Steven

Posted by guest on September 11, 2012 at 08:41 AM PDT #

Hi Rekha,

I am using R12.1.3 application.We have requirement to develope excel sheet having LOV for some mandatory columns.

Can you please provide me the steps to create LOV for Excel sheet columns in WEB ADI.

Also let me know if there are any integrator present to do Time card entry in OTL(Oracle time & Labor).

Thanks & Regards,
Nagesh Bhupal

Posted by guest on September 25, 2012 at 10:52 AM PDT #

Hi, Rekha,

At the end of phase B, there are 13 rows in table BNE_TEST_IMPR_INTERFACE:
>>> 7 rows have error code in result.
>>> 6 rows have ‘SUCC’ in result

But the screenshot at page 49, there is not GREEN icon for SUCCESS row.

Why?
Is it a bug?
How to return green icon for success row when error row exists?

Thx!
Best Regards,
jasper

Posted by guest on November 02, 2012 at 12:51 AM PDT #

Hi Nagesh,

Apologies for delay in response.

Please refer following recorded online course for steps to create LOV in custom integrator:
Implement and Use Oracle E-Business Suite Desktop Integration Framework (http://oukc.oracle.com/static05/opn/metalinklogin/?t=offering&c=765622276)

I am sorry, I may not be able to answer your query about OTL integrators. I suggest you refer OTL documentation or, log formal Service Request against OTL for information.

Thanks,
Rekha

Posted by Rekha Ayothi on November 02, 2012 at 03:06 AM PDT #

Hi Jasper,

Thanks for raising this query.. it is a good question!!

Let me try to answer your question:
As of today in R12.1.3, when you click 'Upload' in Web ADI spreadsheet, Web ADI will upload rows (all or flagged ones) from spreadsheet to respective Interface and commit it, only if all of them are valid. That is, if any row fails the validation rules defined in integrator, then Web ADI will rollback the uploaded rows and no row will be committed in the Interface.

Now let us look at the example in white paper. As 7 out of 13 rows are invalid, none of them are uploaded to the Interface. Upload error screenshot in page 48 conveys the same. It says, '7 rows are invalid' and 'No rows are uploaded'.

Success message with green icon will appear only if row passes the validation rules and, if it is successfully committed in the Interface. Hence, green icon is not displayed for the remaining 6 rows. It is not a bug!

Having said this, in the upcoming major EBS release, we may provide an option to commit each uploaded row if it is valid. Considering the example, you may be able to upload the remaining 6 valid rows to the Interface, and you may see green icon for successfully uploaded rows.

Hope this helps,
Thanks,
Rekha

Posted by Rekha Ayothi on November 02, 2012 at 04:01 AM PDT #

Hi, Rekha,

Thanks for your reply.

Yes, this solution is good for “UPLOAD”.

BUT, I think, it's not for “IMPORT”.

For example: Create inventory material transaction via WebADI.

Scenario is as follows:

STEP 1
Upload data from excel file to interface table “MTL_TRANSACTIONS_INTERFACE” and commit.
(All rows are inserted successful)

STEP 2
IMPORTER submits synchronous concurrent request “Process transaction interface” to process data in interface table. (Mostly rows are processed successful and one row is fail because of shortage of on-hand quantity.)

STEP 3
The fail row is captured by import rule “Error Row Definition” and feedback to excel file.

STEP 4
End user got a message said “No rows are uploaded” and it seems all rows were rollbacked.

BUT, IT’S NOT TRUE!

STEP 5
End user fix error row and upload ALL ROWS again...

BIG ISSUE => DUPLICATE MATERIAL TRANSACTION !!!

8-( ....

Best Regards,
Jasper

Posted by jasper on November 05, 2012 at 04:40 AM PST #

Hi Jasper,

It is applicable for "import". That is, if one of the rows fail the synchronous import program rules, then all the rows are rolled back (including the correct ones). Hence, the message "No rows are uploaded" is true!!

If you are experiencing it differently, I suggest you log a formal service request in My Oracle Support, our Support team will help you.

Thanks,
Rekha

Posted by Rekha Ayothi on November 06, 2012 at 04:17 AM PST #

Hi, rekha,

Maybe it's difficult to rollback. Because:

1. The synchronous concurrent request submitted by importer is another session and controlled by concurrent manager.
2. “Process transaction interface”, an EBS standard program, doesn't support rollback all rows when error row exists.

OK, I'll log a SR for this issue.

Thank you very much for your help!

Have a nice day.

Best Regards,
jasper

Posted by guest on November 06, 2012 at 07:45 PM PST #

Hi Rekha,

Is there anyway in WEB ADI to create long list of values using table type validation (Pop List).

Ex: Employee Full Name list

Thanks in advance!!

Posted by guest on January 16, 2013 at 01:16 PM PST #

Dear Guest,

This is a good question. We need to be careful about the word 'long' in 'long list of values'. Let me try to answer this by splitting the question:
1. Can I create list of values using table type validation (Pop List)?
Ans: Yes, you can. You can define table type LOV from Components section and, while associating the component to an interface attribute (or column), select the LOV type as 'Pop List'.

2. Can I create 'long' list of values as Pop List using table type validation?
Ans: I am not sure how many values you mean by 'long'. Irrespective of 'table type validation', I suggest Pop List should be used when number of values is less than 30. If it is more than 30, use standard LOV.

Thanks,
Rekha

Posted by Rekha Ayothi on January 17, 2013 at 05:20 AM PST #

Hi ,

I am following the user guide but stuck at the Importer section .
1) I have used PL/SQL API in integartor instead of using Table and it worked fine for uploading data to interface tables.
However when I setup Importer, I do not see source as ' Interface Table'
while defining error message lookup.

Please let me know what actually the sources. means here
I am confused with the definition of sources please explain so that i can use the correct source for my program

I am in Version 12.3

Thanks
VIjay

Posted by guest on May 06, 2013 at 04:32 AM PDT #

Hi Vijay,

In Define Error Message Lookup Rule page, Source field signifies the possible ways to get Parameter value. Parameter value can be obtained from Environment Variable, Import or Upload Parameter.

For details, please refer section 'Define an error message lookup importer rule' in Desktop Integration Framework's Developer Guide (http://docs.oracle.com/cd/B53825_08/current/acrobat/121bnedg.pdf )

Thanks,
Rekha

Posted by Rekha Ayothi on May 06, 2013 at 06:22 AM PDT #

Hi Rekha,

yes , You are correct , that's exactly my requirement.

Also I went through your video and its really very very useful. Thank you so much for making this video .

I was trying to create table LOV validation using your video but looks like it is incomplete. Where did you mentioned the table name ? How does the creating component window get to know which table is required to do validation. can you please help me in this.

Thanks
Vijay

-------------------------------------------------------------------
Hi Vijay,

Glad to hear that you are planning to use Desktop Integration Framework.

Let me know if my understanding of your requirement is correct. Here is what you intend to achieve:
1. Upload data from Excel spreadsheet to Custom table
2. Validate data in Custom table
3. Move valid data from Custom table to Oracle EBS Open Interface tables
4. Run Oracle EBS import program to move data from EBS Open Interface tables to EBS Base tables

Also, request you to respond from your official email address along with your company name.

Thanks,
Rekha

------------------------------------------------------------------------

Hi Rekha,

I got your email id through the forum "blogs.oracle.com" and requesting you to provide me some insight on using WEB ADI integrator.

I am able to use the WEB ADI custome integrator to upload data to custom staging tables but from there I am not able to import them to Oracle tables . I can create a package which will load the data to oracle interface tables and then kick off standard interface program , but not sure where to define this package in WEB ADI integrator.

Can you please let me know where I need to define this package - is it in Importer section
and if yes then , how my program knows that all uploaded rows are validated and the import program has to run to move data from interface tables to standard table.

There are three stages of my program
Step1) User upload the data through Excel sheets-
This will be validated by my program defined in Integrator section package.procedure
and also it will be inserted to custom table ..lest say XXB_WEB_ADI_TEST

Step2) . if all data is good..the data should move to oracle standard interface tables.

Step3. Once data loaded ..automatically run program to move this data to oracle tables using statndard import program .

I am able to do first step . but really confused on second and third step...Can you please help out.

Posted by guest on May 07, 2013 at 06:00 PM PDT #

Hi Rekha,

How can we use environment variable in Table LOV,

I want to perform below operation in Table LOV where COndition

responsibility_id=$env$.respid

can I use environment as above ? I tried using like this but then when I run upload process , I got error
oracle.apps.bne.exception.BneSQLException: Cannot execute SQL Statement.

Any idea??

Thanks
Vijay

Posted by Vijay on June 11, 2013 at 07:41 AM PDT #

Hi Vijay,

responsibility_id = $env$.respid is indeed the right usage of environment variable for responsibility id in Where clause of Table LOV.

Please log an SR in My Oracle Support, our Support Engineers will help you.

Thanks,
Rekha

Posted by Rekha Ayothi on June 12, 2013 at 12:25 AM PDT #

Hi Rekha ,

Thank you very much . My issue is resolved .
Looks like it was due to some other join condition .

I have one more question , if you can help .
I found some seeded java validator classes in $JAVA_TOP , but I am not sure where are the source code for these java files. Is there any other location where all source files are stored ?

I am working on dependent LOV and wanted to get some hlep from the seeded validators and SQl java files.

Thanks
Vijay

Posted by Vijay Karakoti on June 14, 2013 at 03:24 PM PDT #

Hi Rekha

Following on from some previous comments above I have the same issue as mentioned by Vijay earlier.

Following the white paper on this - http://www.oracle.com/webfolder/technetwork/tutorials/obe/ebs/ebs121/atg/ImporterAPIReturningMsgs.pdf and in the Define Error Message Lookup Rule page the paper shows setting the source to Interface Table, like Vijay I do not have this as an option and I don't see it mentioned in the dev guide.

I only have Environment Variable, Import or Upload Parameter which come from the standard lookup.

Can you advise if Interface Table should be available or is this an error in the white paper? If it should be available is this a patch to BNE?

Thanks

Robert

Posted by guest on October 02, 2013 at 12:10 AM PDT #

Hi Robert,

Please apply following consolidated patch:
Oracle Web Applications Desktop Integrator 12.1.3 Release Update Patchset 1 (Patch 9790225, R12.BNE.B)
https://updates.oracle.com/download/9790225.html

Do let me know, if Interface Table option does not appear after applying above patch.

Thanks,
Rekha

Posted by Rekha Ayothi on October 11, 2013 at 07:36 AM PDT #

Hi,

What is the complete list of Environment Variables available

Also, i have an issue when i try to open the Define Parameter link. It gives as message as -

Please resolve the following error to continue.
You do not have permission to access this functionality.

Anybody else too faced the same issue

Regards
Pradeep

Posted by guest on October 30, 2013 at 08:45 AM PDT #

Hi Pradeep,

Environment variables can be used to retrieve value for various parameters, or it can be used within SQL query (for Content, Importer Rules). In general, following environment variables can be used:
$env$.appid
$env$.userid
$env$.language
$env$.respid
$env$.sessionid

Refer Oracle E-Business Suite Desktop Integration Framework Developer's Guide for R12.2 [E22005-07]
http://docs.oracle.com/cd/V39571_01/current/acrobat/122bnedg.pdf

Please raise formal Service Request with My Oracle Support for above mentioned 'Define Parameter' link issue.

Thanks,
Rekha

Posted by Rekha Ayothi on October 31, 2013 at 12:51 AM PDT #

Hi,
I did test followed the PDF file, all things are ok except the last step.
When I defined "Error Row Definition" and "Error Message Lookup" rule, and tested the demo, I get the error "An error has occurred while running an API import. The ERRORED_ROWS :ER_500796 parameter number 1 must contain the value BIND in attribute 1."; I have applied the patch 9790225 no luck

Need urgent Help...

Posted by guest on February 27, 2014 at 04:10 AM PST #

Hi Guest,

I am sorry to hear that you are facing this issue. Please file a formal Service Request in My Oracle Support. One of our Support Engineers will help you.

Thanks,
Rekha

Posted by Rekha Ayothi on February 27, 2014 at 05:14 AM PST #

Hi Rekha,

I have overcome the perivous error and now i'm facing the new error in Error Looup message with these errors
•An error has occurred while running an API import. 231:TEST_IMPORTER_ERRL
SQL statement returned no rows when values are expected.

My setups are

select meaning FROM FND_LOOKUPS WHERE LOOKUP_TYPE ='XX_TEST_ERROR' AND LOOKUP_CODE =$PARAM$.RESULT

Parameter Name Reference Name Data type source value

RESULT RESULT VARCHAR2 Interface Table XX_TEST_IMPR_INTERFACE.RESULT

Any clue what i'm missing. Pls Guide me

Thanks

Suresh.

Posted by guest on February 27, 2014 at 07:21 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today