Working Effectively with Oracle’s Public APIs

Finding, Understanding, Using, Tuning, and Extending Presented by Rob Lepanto, API Wizard

About the Presenter, Rob Lepanto

• • • • • 17 years working with Oracle Apps/EBS Project manager and techno-functional consultant President of the NYC-OAUG (geo group) API Guru at API Wizard www.linkedin.com/in/RobLepanto

Presentation Overview

• • • • • • What’s an API and how are they used How to identify available APIs Working with APIs When APIs fail Improving API performance Extensions and private APIs

Presentation Goal

• By the end of this presentation, you should have a better understanding of how you can find and use public APIs to improve business processing at your organization

What’s an API?

• API stands for 'Application Programming Interface' • A software program which serves as an intermediary or interface between two other software programs or between a software program and data • APIs are used in a very wide array of contexts with today’s technology and often mean slightly different things in each.

Oracle EBS: Public API vs Private API

• Public APIs have been created by Oracle for customer use.

• Supported / safe • Documented • Relatively simple to use • Private/internal APIs are created by Oracle for its internal processing • Not supported • Use at your own risk

How are public APIs used?

Usage

Interface two systems Custom applications Load or update bulk data API Wizard

Example

Synch customers with third-party application such as salesforce.com

A custom set of forms to more easily manage service contracts.

Update inventory items across dozens of orgs To streamline data entry

Why is it important to use Public APIs?

Along with open interfaces, public APIs represent the only safe, supported way to enter data in Oracle EBS aside from Oracle forms.

"(Public) APIs are guaranteed to maintain the integrity of the database… Invalid data cannot be entered into the system and existing data is protected from incorrect alterations." Oracle Implementation Guide

Great mysteries The Voynich Manuscript The Beale Ciphers Who built Stonehenge and why?

Where do I find a list of Public APIs?

• No single, complete source of API information • You have to employ a variety of approaches to find the public APIs that you are looking for • Hard to find but there are a lot of them: • Over 1,500 in R11, over 2,500 in R12 • Some processes have both public APIs and Open Interfaces • • Some processes have one or the other A few processes have neither

How to find Public APIs?

Place to look

Oracle Integration Repository Oracle Documentation Web Search My Oracle Support Your services provider API Wizard Query DBA Objects

Description

Either online at irep.oracle.com or using Integration Repository Responsibility (R12 only) docs.oracle.com

Search terms: Oracle API and what you’re looking for. For example, 'Oracle API Receipts' Knowledge Base, Forum, raise an SR Should have knowledge or way to get information Has a knowledge base which it shares A powerful do-it-yourself option, described in detail on next slide.

Querying DBA Objects

Use standard naming conventions and some ingenuity to find public APIs SELECT distinct object_name pkg, procedure$ proc FROM WHERE dba_objects obj, sys.argument$ args obj.object_id = args.obj# AND AND AND obj.object_type = 'PACKAGE' obj.owner = 'APPS' ( obj.object_name

OR obj.object_name

) LIKE LIKE '%API%' '%PUB%' AND AND args.procedure$ args.argument

LIKE LIKE '%ITEM%' '%LEAD_TIME_LOT_SIZE%' ORDER BY object_name, procedure$ Results: The public APIs generally have the term 'API' or 'PUB' in them, so this is a good way to narrow down your search This is part of the procedure name, generally somewhat descriptive to the task at hand. In this case, I want to update item info, so I’ll use 'ITEM' in my search to reduce the resultset This is the name of a parameter in the API. You’re probably guessing here but oftentimes API parameters are similar to the name of the field in the table where the data is stored. You can also look in the forms using Help > Examine

De-Facto Public API Standards

Standard

Package.procedure

Standard input parameters Standard output parameters

Description

Most public APIs are package.procedures (rather than stand-alone procedures). There are some package.functions.

These input parameters are included in the majority of public APIs: p_api_version p_init_msg_list p_commit These output parameters are included in the majority of public APIs: Initializations Flexfield Support Internal identifiers Owned by 'apps' p_return_status / x_return_status p_msg_count / x_msg_count p_msg_data / x_msg_data User, Responsibility, Application, Organization, MO_Global Ability to process flexfields Internal Oracle IDs rather than common values (i.e. party_id rather than party_name) Unlike other objects like tables, which are owned by individual module schemas, all of the APIs are owned by the 'apps' schema

No quite standards but fairly common

Standard

p_object_version_ number No status parameter No messages parameter Special messages

Description

Required by several public APIs. For example, required when updating parties/customers and employees.

Some public APIs don’t provide a status parameter, so how do you know if they were processed successfully?

They write out to fnd_msg or another utility rather than have an output messages parameter User, Responsibility, Application, Organization, MO_Global

Error message handling

• Messages • Number of messages controls how you retrieve them for most APIs. As a general rule, most APIs have a p_msg_count or x_msg_count parameter that gives you the message count • Single message • Generally go to output parameter p_msg_data (or x_msg_data) • Multiple messages • Generally written to fnd message buffer, • Must retrieve with fnd_msg_pub.get

Public API Failures

"I have not failed. I've just found 10,000 ways that won't work." - Thomas Edison

Types of API Failures

Failure Type Description

Failure to make valid API Call API not executed because call is invalid, typical error is:

How to address

1.

Check data-types of values passed and data types of parameters.

Validation failure ORA-0650 Wrong number of types of arguments Data does not pass validation and does not create or update data 2.

1.

2.

3.

4.

5.

If that’s not it, then look at documentation or look at the API code (TOAD and SQL*Developer make this easy) to find required parameters (those that do not have default values).

Check the error messages, some are fairly explicit. If error message is not meaningful, try to eliminate non-required parameters and then add back one at a time until error occurs.

Check the documentation Search Raise an SR

Public API Performance

"Whenever you are asked if you can do a job, tell 'em, 'Certainly I can!' Then get busy and find out how to do it." - Theodore Roosevelt

• You can’t improve API performance… can you?

• Performance techniques • • • • • Traces and indexes Parallel execution Coordinating activity Data preparation Commit frequency • • • • Use internal IDs Pre-validate Pinning Patches

Extending functionality with Wrappers

• Wrappers • Program which provides a programming shell within which one or more APIs, validations, and output translations can occur.

• Why use wrappers?

• Often takes multiple APIs to complete a business process • Allows addition of logic, validations, and workflows • Requires programming effort but process efficiency usually makes it a good investment

Private APIs

"Risk comes from not knowing what you're doing." -Warren Buffett

• • When do private APIs come into play?

When there are business process you need to perform outside of the forms and there’s no public API… but you’ve found a private API. Should you use it? What are the risks?

• Risks • Use of private APIs is not supported by Oracle • May not work as you expect • Benefits • Significantly better than direct table writes • Not really that difficult to fully vet and test Do Oracle customers use private APIs?

• Yes. In my experience working with many (50+) organizations that make use of public APIs, approximately half also use private APIs.

Conclusion

• Oracle has invested heavily in exposing APIs to its customers, which is evident by the big increase in public APIs between R11 (1,500) and R12 (2,500) • • • • Public APIs provide a safe and effective way to perform Oracle EBS transactions much faster and more efficiently than standard forms or custom programs While it isn’t always easy to find them, there are public APIs for nearly every business process. With some ingenuity and elbow grease you can find them and learn how to use them You can put them in wrappers for a more complete business process and you can improve their performance when needed This is the 'right' (i.e. safe and supported) way to work with your Oracle EBS data

For more information

• To see white paper that was the source of data for this presentation, please go to: www.api-wizard.com/oaug_api_paper.html

• To email Rob Lepanto