Web ADI Integrators

1
Web ADI Integrators

• Load Data with Excel

2

• Brian Pellot
• Independent consultant
• 9 years Oracle Applications experience
• Upgrades and implementations
• Numerous modules
• Functional with technical background

3
Topics

• Web ADI
• Traditional Custom Import Process
• Web ADI Custom Import Process
• Web ADI Components
• Creating a Custom Integrator
• Examples
• Other Options and Features

4
Web ADI History

• Application Data Interchange
• GLDI GL specific
• ADI
• Multiple versions
• PC based application
• Assets and GL
• Concurrent request and reporting functionality
• Web ADI
• Completely web based
• Only data export and load no reporting
• Many applications ICM, HR (replaced ADE), GL,
  others
• Extendable
• New integrators
• Custom Layouts

5
Web ADI Basic User Process

• Create a spreadsheet
• From a form
• From a menu option
• Populate or change the spreadsheet
• Upload to Oracle
• Fix errors if needed

6
Create a Spreadsheet from a Form

• Some forms have an export data function.
• This exports to a spreadsheet using Web ADI.
• Some additional pages ask for Excel version and
  format.

7
Create a spreadsheet
Select a layout

• A spreadsheet is created and populated from the
  screen.
• Enter the new, desired information to upload.

8
Upload from a spreadsheet

• After new information is entered, select upload
  from the Oracle menu.

9
Upload from a spreadsheet

• After upload you see the success or failure of
  your data.

10
Extendibility

• Oracle seeds specific exports and loads.
• The spreadsheet formats, called layouts, can be
  changed or additional ones can be added via
  setups.
• Each data export or load (i.e. proposed salaries,
  GL journals) uses an integrator.
• A programmer can create a new integrator with
  some simple setups and possibly a simple program.

11
Traditional Custom Import Process

• Create a flat file
• Transfer flat file to DB server
• Load into temp table
• Custom program
• Read temp table and loop through records
• Call Oracle API
• Mark temp table as to what is done/not done
• Report on errors
• Method to correct/clear errors

12
Traditional Custom Import Build Units

• Transfer process
• Unix script
• Custom temporary table
• Load control file
• Custom program
• Registered concurrent program

13
Web ADI Import Process

• Create a flat file
• Transfer flat file to DB server
• Load into temp table
• Custom program (may not be needed)
• Read temp table and loop through records
• Call Oracle API
• Mark temp table as to what is done/not done
• Report on errors
• Method to correct/clear errors

14
Web ADI Custom Import Build Units

• Transfer process
• Unix script
• Custom temporary table
• Load control file
• Custom program (maybe)
• Registered concurrent program
• Web ADI integrator setup
• Web ADI layout setup

15
Web ADI Components
Data Tables
Spreadsheet
Oracle API
Integrator (setups point to API)

• Or,
• If the Oracle API input is too complicated.

Spreadsheet
Integrator (setups point to custom API)
Data Tables
16
Creating a Custom Integrator

• Perform one-time Web ADI setup and some security
  setups.
• Identify the Oracle provided API you will be
  using. This will be a procedure within a
  package.
• hr_job_api.create_job
• hr_position_api.create_position
• Determine if you need a custom wrapper. You may
  need a custom wrapper if
• The API uses internal ids that would mean nothing
  to the user. i.e. organization_id or job_id.
• The integrator needs logic like if the record
  does not exist create it, otherwise update it.
• The integrator needs to call multiple APIs like
  first create the entry and then create the entry
  values.
• Write wrapper if you need it.
• Set up the Integrator
• Create the integrator
• Create a function for the integrator
• Add the function to your menu
• Associate the function to the integrator
• Define the layout(s)

17
Example 1Create Jobs Integrator

• Purpose Load Jobs for a one-time conversion to
  Oracle
• Use API hr_job_api.create_job
• create_job
• (p_validate in
  boolean default false
• ,p_business_group_id in
  number
• ,p_date_from in date
• ,p_job_group_id in
  number
• ,p_segment1 in
  varchar2 default null
• ,p_segment2 in
  varchar2 default null
• ,p_segment3 in
  varchar2 default null
• ,p_attribute1 in
  varchar2 default null
• ,p_job_definition_id in out
  nocopy number
• ,p_name out
  nocopy varchar2
• )
• Looking at the procedure definition, only a few
  parameters are required and most are meaningful
  to an analyst doing the conversion.
• Not creating a wrapper.

18
Example 1Create the Integrator

• Web ADI uses Web ADI spreadsheets to load
  integrator definitions.
• Responsibility Desktop Integration
• Menu Option Create Document
• Select Integrator HR Integrator Setup

19
HR Integrator Setup Spreadsheet

• Use the spreadsheet to define an integrator.
• Associates the integrator and the API

20
HR Integrator Function Setup

• Create a form function in Application Developer
  responsibility. (copy one of Oracles)
• Add it to your menu.
• Use the spreadsheet created with HR Maintain
  Integrator Form Function Integration.
• Associates the integrator and the function.

21
HR Integrator

• What weve done so far
• Create integrator name
• Associate integrator with an API
• Create a function, put on menu
• Associate integrator with a function
• Next
• Create a layout
• Create a spreadsheet
• Use it

22
HR Integrator Layout

• Responsibility Desktop Integration
• Menu Option Define Layout
• Select your integrator

23
HR Integrator Layout - cont

• Name your layout
• Can have more than one layout for each integrator.

24

• NOW,
• THIS IS COOL!

25
HR Integrator Layout - cont

• The parameters from the API (magically) appear.

26
HR Integrator Layout cont
Add constants and defaults
Specify what should appear as columns

• Select which should appear in your spreadsheet
  and where (header or line).

27
Create a Spreadsheet

• Responsibility Desktop Integration
• Menu Option Create Document
• Select Integrator Your Integrator
• Complete the information.
• Upload the data.

28
Example 2Element Entries Integrator

• Purpose Load Element Entries through a
  spreadsheet
• Use multiple APIs
• pay_element_entry_api.create_element_entry
• pay_element_entry_api.update_element_entry
• To call these we need to
• Decode element and input value names
• Determine if the element already exsits
• Need to create a wrapper.

29
Create a Wrapper
Oracle API pay_element_entry_api.create_element_e
ntry ( p_effective_date
gt l_effective_date
,p_business_group_id gt l_business_group_id
,p_assignment_id gt
l_assignment_id ,p_element_link_id
gt l_element_link_id
,p_entry_type gt l_entry_type
,p_input_value_id1 gt
l_input_value_id1 ,p_input_value_id2
gt l_input_value_id2
,p_input_value_id3 gt l_input_value_id3
,p_input_value_id4 gt
l_input_value_id4 ,p_input_value_id5
gt l_input_value_id5
,p_entry_value1 gt p_entry_value1
,p_entry_value2 gt p_entry_value2
,p_entry_value3 gt
p_entry_value3 ,p_entry_value4
gt p_entry_value4
,p_entry_value5 gt p_entry_value5
,p_entry_value6 gt p_entry_value6

• Wrapper
• PROCEDURE dpl_create_element(
• p_effective_date in date
• ,p_employee_number in varchar2
• ,p_element_name in varchar2
• ,p_entry_type in varchar2
• ,p_input_value_1 in varchar2 default
  null
• ,p_input_value_2 in varchar2 default
  null
• ,p_input_value_3 in varchar2 default
  null
• ,p_input_value_4 in varchar2 default
  null
• ,p_input_value_5 in varchar2 default
  null
• ,p_entry_value1 in varchar2 default
  null
• ,p_entry_value2 in varchar2 default
  null
• ,p_entry_value3 in varchar2 default
  null
• ,p_entry_value4 in varchar2 default
  null
• ,p_entry_value5 in varchar2 default
  null

Wrapper Calls Oracle APIs

• Wrapper
• Performs logic
• Converts user data entered on the spreadsheet to
  the ids and formats needed by Oracle API
• Employee number is converted to assignment id.
• Element Name is converted to element link id.
• Calls Oracle API
• The wrapper becomes a new API
• Create an integrator to use the wrapper

30
Integrator for the Wrapper

• Define the integrator to call your procedure
  (wrapper).
• Perform other setups for function and menu.
• Create layout.

31
End Result

• The spreadsheet columns are the wrapper
  parameters.
• Complete the spreadsheet and perform the upload.
• Integrator -gt Wrapper -gt Oracle APIs

32
Other Options
Load Open Interface Tables
Oracle Open Interface Table
Spreadsheet
Custom Procedure
Integrator
Oracle Open Interface
Load Custom Interface Tables
Custom Interface Table
Spreadsheet
Custom Procedure
Integrator
Custom Interface
33
Additional Features

• Integrators can be equipped to
• Export data from Oracle
• Use lists of values
• Associate with Oracle forms Tools/Export

34
Gotchas

• Pop-up blocker can block Web ADI
• Excel can not be open when you are creating a
  spreadsheet.
• Macro security in Excel must be medium and VB
  must be allowed.
• Error handling with Web ADI look at seeded
  integrators for examples.
• Protection on the Web ADI sheets is difficult.

35
Notes of Interest

• 360105.1 Understanding and Using Web ADI in
  Oracle HRMS - Includes A Step by Step Guide to
  Creating HRMS Integrators
• 228527.1 - "Web ADI for Oracle HRMSImplementation
  and Configuration Information"
• Web ADI Implementation Guide - bne115ig.pdf
• Web ADI User Guide - bne115ug.pdf

36
Questions

• My contact informationBrian Pellot
• bpellot_at_earthlink.net

Special thanks to James Sanders and Dayton
Power and Light