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

1. Pre-requisites for working on Development or use of WEBADIs

1. Set Profile Option

Check the value for profile option named BNE Allow No Security Rule and set the value to Yes

2. 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

2. Creation of a new Upload Integrator

1. 1. Business requirement

We need to develop a new integrator which will be used to upload data into person EIT.

2. 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

3. 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

4. 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.

5. 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:

1. 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

6. 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

Add comment