Transcript webADI Integrators - Darryl Carson . com

Web ADI Integrators
Load Data with Excel
Brian Pellot
 Independent consultant
 9 years Oracle Applications
experience
 Upgrades and implementations
 Numerous modules
 Functional with technical background
Topics
 Web
ADI
 Traditional Custom Import Process
 Web ADI Custom Import Process
 Web ADI Components
 Creating a Custom Integrator
 Examples
 Other Options and Features
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
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
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.

Create a spreadsheet
Select a layout
A spreadsheet is created and populated
from the screen.
 Enter the new, desired information to
upload.

Upload from a spreadsheet

After new information is entered, select
upload from the Oracle menu.
Upload from a spreadsheet

After upload you see the success or
failure of your data.
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.

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
Traditional Custom Import Build
Units
 Transfer
process
 Unix script
 Custom temporary table
 Load control file
 Custom program
 Registered concurrent program
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
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
Web ADI Components
Spreadsheet
Integrator
(setups point
to API)
Oracle API
Data
Tables
Or,
If the Oracle API input is too complicated….
Custom API
Spreadsheet
Integrator
(setups point
to custom API)
Oracle API
Data
Tables
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)
Example 1:
Create Jobs Integrator
Purpose: Load Jobs for a one-time conversion to Oracle

Use API hr_job_api.create_job
create_job
(p_validate
,p_business_group_id
,p_date_from
,p_job_group_id
,p_segment1
,p_segment2
,p_segment3
,p_attribute1
,p_job_definition_id
,p_name
)


in
boolean default false
in
number
in
date
in
number
in
varchar2 default null
in
varchar2 default null
in
varchar2 default null
in
varchar2 default null
in out nocopy number
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.
Example 1:
Create the Integrator
 Web
ADI uses Web ADI spreadsheets
to load integrator definitions.
 Responsibility: Desktop Integration
 Menu Option: Create Document
 Select Integrator: HR Integrator
Setup
HR Integrator Setup Spreadsheet
Use the spreadsheet to define an
integrator.
 Associates the integrator and the API

HR Integrator Function Setup




Create a form function in Application Developer
responsibility. (copy one of Oracle’s)
Add it to your menu.
Use the spreadsheet created with HR Maintain
Integrator Form Function Integration.
Associates the integrator and the function.
HR Integrator
What we’ve 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
HR Integrator Layout
Responsibility: Desktop Integration
 Menu Option: Define Layout
 Select your integrator

HR Integrator Layout - cont
Name your layout
 Can have more than one layout for each
integrator.

NOW,
THIS IS COOL!
HR Integrator Layout - cont

The parameters from the API (magically)
appear.
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).
Create a Spreadsheet
Responsibility: Desktop Integration
 Menu Option: Create Document
 Select Integrator: Your Integrator
 Complete the information.
 Upload the data.

Example 2:
Element 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.
Create a Wrapper
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
Oracle API
pay_element_entry_api.create_element_entry
(
p_effective_date
=> l_effective_date
,p_business_group_id => l_business_group_id
,p_assignment_id
=> l_assignment_id
,p_element_link_id
=> l_element_link_id
,p_entry_type
=> l_entry_type
,p_input_value_id1
=> l_input_value_id1
,p_input_value_id2
=> l_input_value_id2
,p_input_value_id3
=> l_input_value_id3
,p_input_value_id4
=> l_input_value_id4
,p_input_value_id5
=> l_input_value_id5
,p_entry_value1
=> p_entry_value1
,p_entry_value2
=> p_entry_value2
,p_entry_value3
=> p_entry_value3
,p_entry_value4
=> p_entry_value4
,p_entry_value5
=> p_entry_value5
,p_entry_value6
=> p_entry_value6
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
Integrator for the Wrapper
Define the integrator to call your
procedure (wrapper).
 Perform other setups for function and
menu.
 Create layout.

End Result
The spreadsheet columns are the wrapper
parameters.
 Complete the spreadsheet and perform
the upload.
 Integrator -> Wrapper -> Oracle APIs

Other Options
Load Open Interface Tables
Spreadsheet
Integrator
Custom
Procedure
Oracle Open
Interface
Table
Oracle Open
Interface
Custom
Procedure
Custom
Interface
Table
Custom
Interface
Load Custom Interface Tables
Spreadsheet
Integrator
Additional Features
Integrators can be equipped to:
 Export data from Oracle
 Use lists of values
 Associate with Oracle forms –
Tools/Export
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.
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

Questions
 My
contact information
Brian Pellot
[email protected]
Special thanks to
James Sanders and
Dayton Power and Light