Setting Oracle APPS(E-Biz) context in Stored Procedure(PL/SQL)

What is e-biz context?

While viewing or updating any data in Oracle APPS we need to login. But sometimes developers view/update data from stored procedures (PL/SQL). So in order to get the accurate results, it’s always better to mimic the db that we are accessing from a legitimate FND user with a proper responsibility. So to make sure the db is mimicked, we need to set the e-biz context (Setting FND User and Responsibility) before accessing any data.

Why do we need to set the e-biz context or what are the uses of setting e-biz context?

Most of the time while calling Oracle supplied APIs or custom APIs or even accessing some views, we might need to set the e-biz context. Below are the some of the reasons why we need to set the context.

• If the calling API is inserting, updating or deleting any table data, then the WHO columns will be populated properly. 
• Some of the results of the APIs are depending upon the security profile attached to the responsibility. For example in Oracle HRMS, based in the security profile attached to the responsibility, the data displayed will differ. So setting the context will ensue that we get correct data in return. 
• Selecting data from the secured views like PER_PEOPLE_V and PER_ASSIGNMENTS_V doesn't yield any results if the e-biz context is not set.

What are the mandatory things we need to set as part of e-biz context?

While accessing Oracle APPS, we need to login using FND User and then we need to select a responsibility before accessing any functionality. So just to replicate the same we need to set below values before accessing any data or calling any API.

• FND User
• FND Responsibility

How to set the e-biz context from Stored Procedure(PL/SQL)?

Oracle has already provided below API to set the e-biz context in your stored procedure.

FND_GLOBAL.APPS_INITIALIZE(USER_ID IN NUMBER,

                            RESP_ID IN NUMBER,

                            RESP_APPL_ID IN NUMBER,

                            SECURITY_GROUP_ID IN NUMBER DEFAULT 0,

                            SERVER_ID IN NUMBER DEFAULT -1)

How do I know what is my USER_ID and RESP_ID?

You can find USER_ID from FND_USER table if you know your FND user name using below SQL.

SELECT USER_ID FROM FND_USER WHERE USER_NAME = '<Your FND User Name>'

And you can find RESP_ID and RESP_APPL_ID from FND_RESPONSIBILITY table if you know your responsibility Key or name using below SQLs.

SELECT RESPONSIBILITY_ID, APPLICATION_ID FROM FND_RESPONSIBILITY WHERE RESPONSIBILITY_KEY = '<Responsibility Key>'

SELECT RESPONSIBILITY_ID, APPLICATION_ID FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = '<Responsibility Name>'

Finally how to set the e-biz context after getting all required values?

FND_GLOBAL.APPS_INITIALIZE is used for initializing the Oracle Applications context before calling any public  API's or custom build APIs in Oracle E-business suite.

Oracle HRMS uses security views to display the data based on the responsibility the user is accessing. If you are using same secured views in your pl/sql, then while calling those APIs it gives wrong values as the e-biz context is not set.

Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function. We are passing the mandatory parameters. You can pass other parameters depending on your requirement.

FND_GLOBAL.APPS_INITIALIZE(USER_ID=> <Your User ID>,

                                              RESP_ID  => <Your Responsibility ID>,

                                              RESP_APPL_ID => <Your Responsibility Application ID>);

Then call your your respective API or get the results from secured views. 

Please let me know if I miss any points. I am happy to add here if it's relevant.