First Part of Web ADI

In the first part we have learnt how to create a simple Web ADI integrator to enter the basic details of an employee.  In that example we created an integrator to upload and update information in oracle application. In this example we will learn how can we first extract the data from oracle itself and then modify the same in our local machine and then upload it back to oracle.

» In case if you have missed the first part Oracle Web Adi : Explained step by step with diagrams and case study we suggest you to read it first.

Business Requirement

Let assume that requirement is to make sure following fields are always populated for all employees, they seems to critical and legislative requirement of client.  They also want to make sure that there is a regular process for HR department to periodically check and identify all employees for whom this information is missing.

RelatedPosts

Enhancing Learner Experience: A Guide to Category and Topic Communities

779

Learn to Configure and Use Self-Service Community

991

1. Email address
2. Title
3. Date of birth

For the simplicity only three fields have been mentioned over here, however, there could be many more fields like Payroll_Name, Supervisor_Name, Work_Pattern, National_Identifier_Number, Location, Country_of_Employment etc.

Business solution (using web adi)

The easiest solution to this is to create an integrator which will first identify all such employees for whom the above three field are missing. Once the employees are identified, HR team can enter the required filed in the same integrator and then upload it to oracle.  They can also run/access the integrator on a regular basis (say bi-weekly) so that the data are not missing for any employees. The integrator will only identify employees for whom data are missing so HR team need not to go through each person record to identify such employees.

Steps to develop Web ADI integrator

Step 1:  Compile the given sample view.

create or replace view XX_TEST_UPDT_EMP
as select *
from apps.per_all_people_f papf
where 1 = 1
and sysdate between papf.effective_start_date and papf.effective_end_date
and current_employee_flag = 'Y'
and (papf.date_of_birth is null
    or email_address is null
    or title is null)

Step 2:  Set the profile option “BNE Allow No Security Rule” to yes as given in the fig1.

Step 3:  Select the responsibility “Desktop Integration”

Step 4:  Click on “Create Document” link.

Step 5: Following window will appear. Select Excel 2003 as viewer as shown in the fig.2

Step 6:  Click on next. Following window will appear. Select “HR Integrator Setup” as an integrator. This is the integrator given by oracle to create any new integrator. As shown in the Fig.3.

Step 7: Click on next. Following screen will appear.  Select “Integrator Setup” as a layout as shown in the fig.4.

Step 8: Click on next. Select “None” as content as shown in the Fig.5.

Step 9: Click on the next.  A review page will appear as shown in the Fig.6. Click on “Create Document”

Step 10:  Open the excel sheet that pops- up and then enter the details as follows in the excel sheet. And then click on upload.

» Please see the troubleshooting in case of any issue (bottom of the post)

Step 11: Select “Validate Before Upload ” and then click on the “Upload” in the next screen.

Step 12: Once successfully uploaded you will get a confirmation.

Step 13: Now we need to define a mapping.

Navigate to “Desktop Integration” » “Define Mapping”. A screen will appear. Select the integrator “XX_TEST_UPDT_EMP” we have just created as shown in the fig.7

Step 14: Click on go and then you will find that a default Mapping has already been created by Oracle. As shown in the fig.8

Step 15: we don’t really need to update this but just to check click on Update button. You will see the mapping between source and target column as shown in the figure fig.9

Step 16: Now define the layout for this integrator. This has already been covered in the previous document.  Please see the document in case of any concern and create a layout as follows.

• Layout Name : XX_TEST_UPDT_EMPLN
• Layout Key : XX_TEST_UPDT_EMPLK

Step 17:  In the next screen you will get two fields Required Fields and Optional Fields. (see fig.10)

Enter the details as follows and click on apply.

» Required Field

» Optional Field

Select only following items

Step 18: Now create a function, as given below, for this integrator and attach it to responsibility (steps are given in the first part)

Description

• Function : XX_TEST_UPDT_EMP
• User Function name : XX UPDATE Employee Record

Properties

• Type : SSWA servlet function

Form

• Parameter : bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2003&bne:reporting=N&bne:integrator=USER_NAME:XX_TEST_UPDT_EMP&bne:noreview=Yes

Web HTML

• apps.bne.webui.BneApplicationService

Step 19: Now let’s select the function (after attaching the function to responsibility). You will get following screen

Step 20: Click on Continue. You will get following confirmation message and all the employees will be selected or whom either Title, Email_Address or Date_Of_Birth  filed has not been populated

» The employees selected in the excel sheet are coming from the view XX_TEST_UPDT_EMP

Step 21: Now you can populate all missing information for selected employees and then clink on Add-Ins » Oracle » Upload. This will upload all missing information to oracle. However, if there is any error while uploading the record it will be displayed under the message column. If all the records are uploaded successfully and then when you click the function/integrator again you will get no record this is because there will be no employee for whom the mentioned fields are missing.

Step 22: For example we are just uploading a single employee (Employee number: 1003) as her Tile is missing. We are going to give the title MS. And then click on Add-Ins » Oracle » Upload (As shown in the Fig. 13)

Step 23: We got following confirmation with a smiley.

Step 23: Let’s check whether it has been actually updated in oracle or not.

Step 24: As you can see from above figure (Fig.15) the record has been updated successfully.

Trouble shooting for Web ADI

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

If you are getting this error while creating integrator please follow the below steps

1. Goto System Administrator–>Application–>Menu and query for “DESKTOP INTEGRATION MENU“
2. for this menu add the function “HR Integrator Step“

Unable to open excel sheet.

You might need to enable the Macros before performing the steps. You may perform this as your first step.

1. Open an empty Excel sheet
2. Click on Excel Option
3. Click on trust center setting
4. Click on macros setting
5. Select the option “Enable all macros (not recommended; potentially dangerous code can run)” and “Trust access to the VBA project object model”

Read Web ADI Troubleshooting : Solve all excel related problems like “Run-time error 91″  for comprehensive knowledge on web ADI troubleshooting.

Hope you have enjoyed learning Web ADI, Happy Learning!!