We
need to develop a new integrator which will be used to upload data into
person EIT. Assume we have defined one EIT to store Training test
details in Oracle which was one of the important inputs for Confirmation
of employees. Creating data for each employee for each training using
the seeded form was a time consuming task and as a result of which we
had provided them an excel interface to upload data into system.
1. Pre-requisites for working on Development or use of WEBADIs 2
1.1 Set Profile Option 2
1.2 Enable the Macros options for your excel sheet 2
2. Creation of a new Upload Integrator 4 2.1 Business requirement 4 2.2 Create the Wrapper Package 4 2.3 Define the Custom Integrator 6 2.4 Define the Layout for the integrator 9 2.5 Defining the Form Function for Integrator 13 2.6 Assign Function to Custom Menu Check the value for profile option named BNE Allow No Security Rule and set the value to Yes Select Excel option to set the properties Click on Trust Centre -> Trust center Settings Select Macro Settings -> Enable all macros and check the option Trust access to the VBA Project object model Apply and save your settings Assign responsibility Oracle WEBADI or Desktop Integrator to do the development of new WEBADIs Navigation -> Application Developer -> Application -> Function Type - SSWA servlet function Parameter - bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2003&bne:reporting=N&bne:integrator=USER_NAME:XX
- Upload Training Details&bne:noreview=Yes Integrator user name can be derived from bne_integrators_vl table with help of following query: select integrator_code, user_name from bne_integrators_vl where 1 = 1 and user_name like 'XX%Train%' order by creation_date desc Save the Function definition Go and add the function into the custom responsibility Navigation-> Application Developer -> Application -> Menu -> Search for your menu where you want to add this ADI Add,
Prompt and Form function created and save. To check the availability of
the function, navigate to the responsibility where its been added Click next and Create document and your custom integrator is available to user for uploading dataPre-requisites for working on Development or use of WEBADIs
Set Profile Option
Enable the Macros options for your excel sheet
Creation of a new Upload Integrator
We need to develop a new integrator which will be used to upload data into person EIT. Business requirement
We
will have to define/create a wrapper package which will act as middle
layer between your ADI excel sheet and Oracle to perform the desired
action.Create the Wrapper Package
I have attached a sample code which will create record into EIT for your reference
create or replace PACKAGE xx_hr_webadi_pkg
AS
PROCEDURE upload_training_details (
p_employee_number VARCHAR2,
p_test_name VARCHAR2,
p_test_status VARCHAR2,
p_test_score VARCHAR2 DEFAULT NULL,
p_hear_rating VARCHAR2 DEFAULT NULL,
p_exam_date VARCHAR2 DEFAULT NULL,
p_exam_expiry_date VARCHAR2 DEFAULT NULL,
p_instruct_1 VARCHAR2,
p_instruct_2 VARCHAR2,
p_instruct_3 VARCHAR2,
p_instruct_4 VARCHAR2,
p_instruct_5 VARCHAR2,
p_instruct_6 VARCHAR2,
p_instruct_7 VARCHAR2 DEFAULT NULL,
p_instruct_8 VARCHAR2 DEFAULT NULL,
p_instruct_9 VARCHAR2 DEFAULT NULL,
p_instruct_10 VARCHAR2 DEFAULT NULL,
p_instruct_11 VARCHAR2 DEFAULT NULL,
p_instruct_12 VARCHAR2 DEFAULT NULL,
p_instruct_13 VARCHAR2 DEFAULT NULL,
p_instruct_14 VARCHAR2 DEFAULT NULL,
p_instruct_15 VARCHAR2 DEFAULT NULL,
p_instruct_16 VARCHAR2 DEFAULT NULL,
p_instruct_17 VARCHAR2 DEFAULT NULL,
p_instruct_18 VARCHAR2 DEFAULT NULL,
p_instruct_19 VARCHAR2 DEFAULT NULL,
p_instruct_20 VARCHAR2 DEFAULT NULL
);
END xx_hr_webadi_pkg;
/
Package Body Definition
create or replace PACKAGE BODY xx_hr_webadi_pkg
AS
PROCEDURE upload_training_details (
p_employee_number VARCHAR2,
p_test_name VARCHAR2,
p_test_status VARCHAR2,
p_test_score VARCHAR2 DEFAULT NULL,
p_hear_rating VARCHAR2 DEFAULT NULL,
p_exam_date VARCHAR2 DEFAULT NULL,
I
have added these instruct parameter to add some instructions to my
integrator. We will see the significance of it in the later part of the
document
p_exam_expiry_date VARCHAR2 DEFAULT NULL,
p_instruct_1 VARCHAR2,
p_instruct_2 VARCHAR2,
p_instruct_3 VARCHAR2,
p_instruct_4 VARCHAR2,
p_instruct_5 VARCHAR2,
p_instruct_6 VARCHAR2,
p_instruct_7 VARCHAR2 DEFAULT NULL,
p_instruct_8 VARCHAR2 DEFAULT NULL,
p_instruct_9 VARCHAR2 DEFAULT NULL,
p_instruct_10 VARCHAR2 DEFAULT NULL,
p_instruct_11 VARCHAR2 DEFAULT NULL,
p_instruct_12 VARCHAR2 DEFAULT NULL,
p_instruct_13 VARCHAR2 DEFAULT NULL,
p_instruct_14 VARCHAR2 DEFAULT NULL,
p_instruct_15 VARCHAR2 DEFAULT NULL,
p_instruct_16 VARCHAR2 DEFAULT NULL,
p_instruct_17 VARCHAR2 DEFAULT NULL,
p_instruct_18 VARCHAR2 DEFAULT NULL,
p_instruct_19 VARCHAR2 DEFAULT NULL,
p_instruct_20 VARCHAR2 DEFAULT NULL
)
IS
l_person_extra_info_id NUMBER;
l_object_version_number NUMBER;
l_information_type VARCHAR2 (100) := 'XX_TRAINING_DTLS';
l_person_id NUMBER;
l_error_flag VARCHAR2 (10) := 'NO';
e_emp_no_null EXCEPTION;
e_invalid_emp EXCEPTION;
e_year_null EXCEPTION;
BEGIN
IF p_employee_number IS NULL
THEN
l_error_flag := 'YES';
RAISE e_emp_no_null;
ELSE
BEGIN
SELECT person_id
INTO l_person_id
FROM per_all_people_f
WHERE employee_number = p_employee_number
AND business_group_id = fnd_global.per_business_group_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'YES';
RAISE e_invalid_emp;
END;
END IF;
IF p_test_name IS NULL OR p_test_status IS NULL
THEN
l_error_flag := 'YES';
RAISE e_year_null;
END IF;
IF l_error_flag = 'NO'
THEN
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => l_person_id,
p_information_type => l_information_type,
p_pei_information_category => l_information_type,
p_pei_information1 => p_test_name,
p_pei_information2 => p_test_status,
p_pei_information4 => p_test_score,
p_pei_information5 => p_hear_rating,
p_pei_information6 => p_exam_date,
p_pei_information7 => p_exam_expiry_date,
p_person_extra_info_id => l_person_extra_info_id,
p_object_version_number => l_object_version_number
);
END IF;
EXCEPTION
WHEN e_emp_no_null
THEN
raise_application_error (-20000, 'Employee Number is a Mandatory Parameter.'
);
WHEN e_invalid_emp
THEN
raise_application_error (-20000, 'Employee Number provided doesnt exists, Please check and reload.');
WHEN e_year_null
THEN
raise_application_error
(-20000, 'Test Name and Status parameter is Mandatory. Please re-enter and upload again');
END upload_training_details;
END xx_hr_webadi_pkg;
/
Compile the Package and Body definition
Navigation -> Desktop Integrator -> Create Document -> Integrator -> HR Integrator SetupDefine the Custom Integrator
HR Integrator setup is a pre-defined integrator which is provided by Oracle to create new custom integrators
Content as None
Click on Create Document
Enter below Details and click on Oracle -> Upload available under toolbarFiled Name Value Metadata Type CREATE Application Short Name PER Integrator User Name XX - Upload Training Details View Name Form Name GENERAL API Package Name XX_hr_webadi_pkg API Procedure Name upload_training_details Interface User Name XX - Upload Training Details Interface Parameter List Name training_details API Type PROCEDURE
The system will show a Confirmation stating the upload of definition is successful
Navigation -> Desktop Integrator -> Define Layout -> Select your custom integrator nameDefine the Layout for the integrator
Click on Create and give a Layout name with some naming convention, which may be used later for reference
The layout is divided into two important sections-
1. Required fields, where all mandatory parameters need to be aligned as per the requirement.
Instruct parameters can be used to provide some tips to users who are using this WEBADI for uploading data
2. Optional fields are non mandatory fields, we need to select the ones which needs to be available in our custom integrator
The
instruct parameters added in the wrapper package will be available
during the Layout definition and we can add user friendly tips which
will be available for user reference
Click Next – To define certain important layout features – This functionality is available only to R12 users
Once you are done click on Apply to save the Layout definition, system will give a confirmation message as below
Now you WEBADI will be available for use using seeded WEB ADI responsibility
Navigation -> Desktop Integrator-> Create Document -> Search for the custom integrator which you created
Click next and click on create the document which will now download your custom integrator
Instructions added in layout definition are available as tips for users
We can see the instructions and the columns available in our excel integrator ready for use.
Now
this ADI is available for use under the seeded responsibility, In order
to give the access of the same to users through custom responsibility,
follow the following steps:Defining the Form Function for Integrator
Assign Function to Custom Menu
Subscribe to this comment's feedwritten by Rama2011 , March 10, 2011
written by Vikram , March 13, 2011
I followed the steps mentioned by you .. but while defining layout and selecting my custom integrator i am getting error as "Error
Please resolve the following error to continue.
You do not have permission to access this functionality.
"
please do let me know how we can resolve this issue ..
written by sanjaysingh , March 23, 2011
Can you please mention that which responsibility you are using to define the layout?
Kind Regards,
Sanjay
written by VRaju , April 20, 2011
Can you please tell us how to find the total number of records entered in the spreadsheet. Also, is there a way to stop the records from getting uploaded even if one record is in error?
Thanks in advance,
Vidya.
written by sanjaysingh , April 23, 2011
WEBADI as a seeded behavior will not upload any records if any one of the record gets errored out.
Only if you have included a commit command in the wrapper package, only then it would load the previous records into database. So as a best practice never include any commit statements in the wrapper package.
Just try the above solution and let me know in case of any issues.
Kind Regards,
Sanjay
written by JD , April 24, 2011
Is it possible to load data into a custom table directly instead of using PL/SQL wrapper? If so, how do you make the custom table available for the custom integrator?
Thanks,
Dhamodaran
written by sanjaysingh , April 26, 2011
It is not possible to load data directly into a Custom table using WEBADI. But there are other tools using which you can populate data directly into custom table which we mostly use in all data migrations. Example - You can use Data Loader utility or if you are using TOAD for accessing database then using TOAD also you can push data directly into table Navigation path - Database -> Import -> Import Table data.
You can also load data into your custom table using ADI but you will have to create a warpper which will insert you data into your custom table.
Kind Regards,
Sanjay Singh
written by Bhavesh Shah , May 11, 2011
It works beautifully, encountered one issue though, I tried to get last_updated_by, login_id through fnd_global in my custom package but it returns null, what it makes me think that the application is not initialized through WebADI. Am I missing something?
Thanks,
Bhavesh
written by Kendell , May 16, 2011
I'm working on an 11.5.10 Oracle Application and while I'll need to create a custom process in the future, I'm trying to get the seeded upload process to work first. I get an error message when trying to create the intergator, by selecting ("HR INTERGATOR SETUP"), I get a message "You do not have permission to access this functionality." Upon reading this blog, I have checked that BNE Allow No Security Rule is set "Yes". Is there some else I need to check, are there permissions do I need to setup for my user? BTW, version of excel is 2010, is that going to cause a problem for me?
Thanks advance for any advice you can give me.
Kendell
written by sanjaysingh , May 18, 2011
Can you please check that the Menu is having the function Named "Create Document" and "HR Integrator Setup" been added. If not please add this function to your menu and it will resolve your issue.
Kind Regards,
Sanjay
written by sanjaysingh , May 18, 2011
fnd_global works perfectly with WEBADI, I have myself used it in multiple instance, can you please elaborate the issue you are facing and what exactly you are trying to get.
Kind Regards,
Sanjay
written by Kendell , May 19, 2011
A self-service menu "HRMS ADI Document Manager Menu", is supplied for use
with Web ADI. This contains the Document Management menu option and required
functions. Add this menu to the main menu attached to the responsibilities of any
users who need access to the Document Management features.
3. The supplied function "Web ADI Create Document" gives access to Web ADI from
any HRMS window.
Important: You must add this function to the main menu attached to
the responsibilities of any users who need access to Web ADI from
an HRMS application window.
written by Kendell Ellington , May 19, 2011
Thank you for your answer, however I still don't have access. I get the same error message. Below are all the steps that I have done, based on all the different documenation found in metalink and on the web...
Note I know ADI works here because it works for GL, but everyone else is unavailable.
1. I haven't created any PL/SQL procedures or ran any scripts.
2. I created a user and assigned the user the WEB ADI and US Super HRMS User Responsibility.
3. Based on the documentation and other blogs I did the following.
* I Checked the value for profile option named BNE Allow No Security Rule and set the value to Yes
* I looked up the menu for the US Super HRMS User responsibility (US SHRMS Navigator)
* Went to Application Developer - Application - Menu Queried (US SHRMS Navigator) in the User Menu Name field
* Based on the documenation in mentalink I added 4 functions (Create Document, Web ADI Layout Definition, Web ADI Text Mapping Definition and HRMS Create Document.
I added 2 submenu entries HR ADI Seeded Integrator Form Functions and HR Integrator Setup Integrator.
Based on doing this I thought I would be able to use all of the HR intergators listed but I still don't have access to any of them. Are there any other set up steps that I'm missing in order to get this to work?
written by Ajit Samal , June 09, 2011
Thank you for sharing the excellent information.
While I am trying to follow the above steps ,I encountered an error as
"Fatal Error : Please have your system administrator view the bne.log file.
java.io.FileNotFoundException: $BNE_TOP/upload bnevc5DIkf5.xml(No such file or Directory)"
I have verifed all the prerequisite profile options for WebADI and It seems to be OK.Directory is available but the above mentioed file has not generated.
Kindly let me know your suggestion for the same.
Thanks
Ajit
written by Kendell , June 09, 2011
/u01/app/appxxxfind/xxxfindappl/bne/11.5.0/upload
Ask the sysadmin what the path should be in your companies case.
written by Jyoti Gurbuxani , June 30, 2011
The document is explained so well that it actually motivates to develop a WebADI. We have a requirement of creating a webADI for sales order in OM.
I am really looking forward to implement it using the steps that you have mentioned.
Thanks again,
Jyoti
written by Saumya Jain , August 05, 2011
I am working on creating a download/upload integrator in R12. The requirement is such that first there will be a custom search page and depending on the search criteria filled by user in it, the data will be downloaded to the excel sheet. Can you please provide some pointers on how to acheive this functionality of filtering the data and passing the filtered data to the excel sheet?
Thanks,
Saumya
written by sanjaysingh , September 07, 2011
Sorry for the delayed response.
You can definately achieve this by using the feature "Define Standalone Query"
Using this feature you can add filter based on the parameters define by you for your WEB ADI.
The brief steps for achieving this is as follows:
1. Define Stand alone query - Navigation -> Desktop Integrator -> Create Document -> Integrator -> HR Maintain Standalone Query -> Click next next and in content page enter your integrator user name and downlaod the document
2. Example if your WEB ADI is downloading all employee data and if you want to downlaod a specific employee data you can plan to add employee number as one of the parameter to you webadi
In you downloaded document under SQL where Clause - where employee_number = nvl($PARAM$.employee_number, employee_number)
1st parameter name - employee_number
1st parameter type - Varchar2
1st parameter prompt - Employee Number
3. Upload the document
4. Test the working of the new filter condition - Now when you create the document, you will see that the webadi is asking for parameter Employee Number from you.
Hope this helps you.
Regards,
Sanjay
written by Morme , October 04, 2011
But the loading screen I return error and the message column shows "Default values have not been defined for this country".
You know how to tell the webadi that the charge is correct (as a concurrent error_code field which tells you (0) successful (1) warning (3) error)
regards
written by Avinash.mca , October 13, 2011
This is really a good artical and is helpful for many beginers like me. Thanks for all your hard work. Wish you go high in your career path.
I have been going through this artical and now stuck while craeting custom integrator and am getting the error
"Please resolve the following error to continue.
You do not have permission to access this functionality."
from the above post it seems there are few other also facing the similar problem but there is no clear steps to overcome from this. You further help on this will be much appreciated.
Thanks,
Avinash
written by Avinash.mca , October 13, 2011
I got solution of my problem after visiting the oracle thred "https://forums.oracle.com/forums/thread.jspa?messageID=1923998"
Following contents from teh blog helped me to resolve the issue
------------
Hi Rahman,
To Resolve the error "you do not have permissions to access this function" perform the below steps.
Goto system Adminstrator-->Application-->Menu
and query for "DESKTOP INTEGRATION MENU"
for this menu add the "HR Integrator Step" then you will not get the Above error.
-------------
Thanks Avinash
written by Mobeen , November 08, 2011
Thanks for this article.
I have a quick question though. How do you do the mappings from the spreadsheet to your procedure 'upload_training_details'?
I am trying to build an integrator for a mass supervisor update and would need to pass in the values from the excel spreadsheet into the custome procedure that i write.
Any help would be much appreciated on this.
Best Regards,
Mobeen
written by electronic ballast , November 18, 2011
Ningbo Zhengjia Electric Appliance Co., Ltd is a professional R & D manufacturer and exporter of electronic ballast for fluorescent lamps(FL) and Compact Fluorescent Lamps(PL).
written by Avinash1 , December 02, 2011
I would like to know how can we create a drop-down for any column for example i would like to select the value either "Yes" or "No". so for this purpose how can i create lov so that user do not enter any other value by chance.
Thanks,
Avinash
written by block machine , January 11, 2012
Ningbo Yinzhou Nuoya Cement Block Machine Factory, a professional manufacturer and exporter of all kinds of Single and Multi Spring brick machine,concrete brick machine,block machine,We are a member of Building Block Association with excellent and advanced technology.
written by Nakhla - Nhawi , January 17, 2012
I did all steps, but when I run the function, I got this error "The webpage cannot be found".
in addition I would like to know how to export all data related to salary management and enable an custom integrator to update the salary.
thank you all in advice.
Nakhla Al-Hawi
written by Avinash1 , March 15, 2012
I am also facing same problem "The webpage cannot be found".
Please suggest.
Thanks
Avinash
written by Aryaan Khan , April 10, 2012
There is an Oracle provided web adi for salary management, so you don't really need to create a custom one.
Regards,
Aryaan Khan
written by André Boni , April 28, 2012
I done everything following the steps of this post but I am going to Desktop Integrator responsibility->Create Document, it not list my custom web adi (named X1-Training Details). Someone can help me? Is necessary to check some profile, menu or function?
thks
written by RJ , August 23, 2012
What should i do about this error?
thanks in advance
regards
vicky