Web adi integrators l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Web ADI Integrators PowerPoint PPT Presentation


  • 675 Views
  • Updated On :
  • Presentation posted in: General

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

Related searches for Web ADI Integrators

Download Presentation

Web ADI Integrators

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Web adi integrators l.jpg

Web ADI Integrators

Load Data with Excel


Slide2 l.jpg

Brian Pellot

  • Independent consultant

  • 9 years Oracle Applications experience

  • Upgrades and implementations

  • Numerous modules

  • Functional with technical background


Topics l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Upload from a spreadsheet

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


Upload from a spreadsheet9 l.jpg

Upload from a spreadsheet

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


Extendibility l.jpg

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 l.jpg

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 l.jpg

Traditional Custom Import Build Units

  • Transfer process

  • Unix script

  • Custom temporary table

  • Load control file

  • Custom program

  • Registered concurrent program


Web adi import process l.jpg

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 l.jpg

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 l.jpg

Custom API

Oracle API

Web ADI Components

Or,

If the Oracle API input is too complicated….

Data

Tables

Spreadsheet

Integrator

(setups point

to API)

Oracle API

Spreadsheet

Integrator

(setups point

to custom API)

Data

Tables


Creating a custom integrator l.jpg

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 l.jpg

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


Example 1 create the integrator l.jpg

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 l.jpg

HR Integrator Setup Spreadsheet

  • Use the spreadsheet to define an integrator.

  • Associates the integrator and the API


Hr integrator function setup l.jpg

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 l.jpg

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 l.jpg

HR Integrator Layout

  • Responsibility: Desktop Integration

  • Menu Option: Define Layout

  • Select your integrator


Hr integrator layout cont l.jpg

HR Integrator Layout - cont

  • Name your layout

  • Can have more than one layout for each integrator.


Slide24 l.jpg

NOW,

THIS IS COOL!


Hr integrator layout cont25 l.jpg

HR Integrator Layout - cont

  • The parameters from the API (magically) appear.


Hr integrator layout cont26 l.jpg

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 spreadsheet27 l.jpg

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 l.jpg

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 l.jpg

Create a Wrapper

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

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


Integrator for the wrapper l.jpg

Integrator for the Wrapper

  • Define the integrator to call your procedure (wrapper).

  • Perform other setups for function and menu.

  • Create layout.


End result l.jpg

End Result

  • The spreadsheet columns are the wrapper parameters.

  • Complete the spreadsheet and perform the upload.

  • Integrator -> Wrapper -> Oracle APIs


Other options l.jpg

Other Options

Load Open Interface Tables

Oracle Open

Interface

Table

Spreadsheet

Integrator

Custom

Procedure

Oracle Open

Interface

Load Custom Interface Tables

Custom

Interface

Table

Spreadsheet

Integrator

Custom

Procedure

Custom

Interface


Additional features l.jpg

Additional Features

Integrators can be equipped to:

  • Export data from Oracle

  • Use lists of values

  • Associate with Oracle forms – Tools/Export


Gotchas l.jpg

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 l.jpg

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 l.jpg

Questions

  • My contact informationBrian Pellot

    [email protected]

Special thanks to James Sanders and

Dayton Power and Light


  • Login