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
15
-
Pre-requisites for working on Development or use of WEBADIs
-
Set Profile Option
Check the value for profile option named BNE Allow No Security Rule and set the value to Yes
-
Enable the Macros options for your excel sheet
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
-
Creation of a new Upload Integrator
-
Business requirement
-
We need to develop a new integrator which will be used to upload data into person EIT.
-
Create the Wrapper Package
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.
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
-
Define the Custom Integrator
Navigation -> Desktop Integrator -> Create Document -> Integrator -> HR Integrator Setup
HR Integrator setup is a pre-defined integrator which is provided by Oracle to create new custom integrators
Click on Next.
Set Content as None > Click Next.
Click on Create Document
Enter below Details and click on Oracle -> Upload available under toolbar
Filed 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
-
Define the Layout for the integrator
Navigation -> Desktop Integrator -> Define Layout -> Select your custom integrator name
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
- Data Entry Rows – Using this property we can define the default number of rows which the ADI will create when initialized, in 11i we user had to insert the new row in case of access data
- Read Only – Using this feature we can define that which of the columns in our integrator will be available as read only
- Width – Using this option we can define the width of each of the columns which will be available under the custom ADI
- Move Up/Down – we can re-arrange the order of display of column in our integrator
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.
-
Defining the Form Function for Integrator
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:
- Define the custom Form Function
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
-
Assign Function to Custom Menu
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 data
Comments
thanks in advance
regards
vicky
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 ..
Can you please mention that which responsibility you are using to define the layout?
Kind Regards,
Sanjay
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.
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
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,
Dhamo daran
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
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?
Tha nks,
Bhavesh
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
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
fnd_g lobal 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
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
functi ons. Add this menu to the main menu attached to the responsibilitie s 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.
Importa nt: You must add this function to the main menu attached to
the responsibilitie s of any users who need access to Web ADI from
an HRMS application window.
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.
Ba sed 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?
Thank you for sharing the excellent information.
Wh ile 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.F ileNotFoundExce ption: $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.
Kin dly let me know your suggestion for the same.
Thanks
A jit
/u01/app/appxxx find/xxxfindapp l/bne/11.5.0/up load
Ask the sysadmin what the path should be in your companies case.
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.
Tha nks again,
Jyoti
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
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$.emp loyee_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
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
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.
T hanks,
Avinash
I got solution of my problem after visiting the oracle thred "https://forums .oracle.com/for ums/thread.jspa ?messageID=1923 998"
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--& gt;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
Thanks for this article.
I have a quick question though. How do you do the mappings from the spreadsheet to your procedure 'upload_trainin g_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,
Mobee n
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
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
I am also facing same problem "The webpage cannot be found".
Please suggest.
Than ks
Avinash
There is an Oracle provided web adi for salary management, so you don't really need to create a custom one.
Regards,
Aryaan Khan
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
What should i do about this error?
This Article is give an idea on webadi creation . Can you send me the step by step creation of User creation & Responsibility Creation through WEB ADI. Thanks in Advance..
Regards
Katamaiah
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
RSS feed for comments to this post