Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

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

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

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

questionHow 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:

dgreybarrow-2Important 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.

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

dgreybarrow-2 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

dgreybarrow-2 Understanding API’s structure

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

Oracleapps API Signature

  • 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 :

Oracleapps API Signature1

  • 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 ,

api signature

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

dgreybarrow-2 How to start with API Integration

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

API flow

  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

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

dgreybarrowSimilar Post

Related Posts

Posted in API Integration | 5 Comments »Email This Post Email This Post |

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
5 Responses
  1. Gareth Roberts Says:

    Nice! Would be good with a few links to sample APIs in iRep.

  2. Amol Says:

    >> Identify API availability, for this you can check in metalink or irep.

    Can you give a good example of how we should zero down our search of the API?

    Basically i am asking what are the parameters (functional) that you should look for in the API?

    As the Comments/Description of each API are very brief, should we go through the PLSQL code to check if its suitable???

  3. api Says:

    I want to know about the file upload api in forms .i want to know how to implement this api.if any material please tell me

  4. Marcos Pineda Says:

    I have performance issues using this API ?AR_INVOICE_API_PUB (around 90 seconds per document) is the “average” time of response??

  5. Sanjit Anand Says:

    90 sec is slightly higher should be less than 3-4 second with 10+ rev lines.

    Couple of things need to check.

    1. check the latest version of API
    2. what is version of Oracleapps you are using?
    3. Are you trying with different options with P_COMMIT , so you are directly dealing with P_COMMIT= TRUE, then it should not take longer.

    Hope this helps

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.