A Newbie’s Guide to E-Business Suite Integration (by Custom Code using API’S!”)

Posted on September 8th, 2008 by Sanjit Anand | Print This Post | Email This Post

How can we Integrate OracleApps with Custom code by using public API’s? What skill is required for E-Business Suite Integration with API’s?

These are the question asked by someone who never exposed to OracleApps development environment.

This was topic I discussed with client IT department folks which includes people who recently inducted in Oracle Group from other technology area, college pass outs and other group like Middleware who are also responsible to putting some nut and bolts in Oracle.Thoughts to convert this in post for newbie and Inhouse IT folks who always struggling for right information for the API Integration.So here to go:

Important Points for those working first time with API’s

• First and foremost Important, you need to understand API Definition, which includes purpose, input parameter, output parameter, in/out parameter as well as default values for parameter
• Next is mapping : This is where you map input data from source/staging/temp table to input parameter
• Next is development of code , you should use cursor/temporary table to load data from user’s table and use Debugging information to detect and diagnose errors.

How Oracle API’s looks like:

• Package: These are the bundle of logically related types, variable, cursor and subprogram
• Packages consist of 2 part:
  • Specification - this is where you declare the types , constant, variables , exception, cursor and subprogram
  • Body : This will define code of the subprogram
• Procedure : These are subprogram without a return value ,sometime use ‘OUT’ parameter to return values
• Function : These are subprogram with a return value.

How Oracle API’s follow standard conventions

ALL public package that expose API’s are named as and follow this convention:

• <product>_<xxxxx>_PUB ending with ‘PUB”
• Oracle convention File are kept separately each for specification and body as
  • its kept as <Product code>XXXXXXS.pls and <Product code>XXXXXXB.pls for body

Understanding API’s structure

Lets understand a simple API , and see what attribute are important from developer prospective.

• This is part of AR_INVOICE_API_PUB.The Package name AR_INVOICE_API_PUB provides you more information like
  • AR_INVOICE_API_PUB -> belong to which product
  • AR_INVOICE_API_PUB -> belong to which Object
  • AR_INVOICE_API_PUB -> Object Type
  • AR_INVOICE_API_PUB -> the last 3 Char make sure this is PUBLIC API

For more details about API’s type, you can check out this post.

Here is some important details you can see :

• Parameter
  • p_<name> : Parameter start with P_ are normally IN parameter
  • x_<name> : Parameter start with X_ are normally OUT/IN OUT parameter
• Default Value
  • FND_API - these are return status value of boolean type. For this you need to check with package spec and see what are pre-defined constants
• Data Input
  • p_batch_source_rec - This is input to API’s is a single record, call API for every record to be loaded
  • p_trx_header_tbl - this will process Multiple records. In other words input to the API is a table of records, call API once for every set of records.
• Calling Procedure Name : these are calling Procedure name , which can be called as
  <pkg_name>.<api_name>. In above case ,

since this is part of AR_INVOICE_API_PUB therefore this is called as AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE

How to start with API Integration

If you are working first time with API integration with Oracle Apps, you should follow these steps.

1. Identify API availability, for this you can check in metalink or irep.
2. Once you got information about API’s , must cross check API’s is available for PUBLIC usage or not.
3. Map API input input parameter with user data.
4. Design and develop a code.
   • First thing you have to do is, you need to initialize Applications Information such as user_id,responsiblity_id and application_id if you are in R11i. For R12,there is slightly change in APPS initialization.
   • Define and drive the cursor to pull the user data.
   • Call the API’s
   • Try to Print return status and error message for user.
   • Put exception handling section to handle any exception raised during processing

Important Tips and Technique when you are using API’s

• You can set P_COMMIT to TRUE only when implicit commit is required.
• You can print X_RETURN_STATUS to the user’s output. These are values return once Program get completed.
  • S - Success
  • E - Error
  • U -Unexpected Error
• You need to initialized API by passing all mandatory parameter.
• All error message are printed in case of error
• You should handle exceptions in your code and if required use nested calls in case of multiple API’s are made
• Debugging should be turned On, there are different level which you can set as (P_DEBUG_LEVEL :=0|1|2|3)
• Avoid Simultaneous Call To The Same API From Two Different Processes , For The Same Item/ transaction To Prevent Deadlocks
• These convention for API’s works for all sub application products like finance , Manufacturing , CRM and HRMS.

Similar Post

• The world of Oracle API
• API’s or Open Interface
• E-Business Suite Integration Repository