Creating User Hooks

Injecting custom code to Oracle Public APIs

The blog Introduction to User Hooks discussed about what is user hook, it's uses and how to identify the user hooks etc. This blog is about how create user hook in order to inject custom code to Oracle supplied public APIs.

Oracle provides user hook APIs to create, update and delete user hooks as shown below

• HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL : This API is used to create a user hook.
• HR_API_HOOK_CALL_API.UPDATE_API_HOOK_CALL : This API is used to update existing user hook.
• HR_API_HOOK_CALL_API.DELETE_API_HOOK_CALL : This API is used to create a delete existing user hook.

After deciding on what API User hook you are going to use, first create your custom code as a package procedure. Lets say its CUSTOM_PKG.CUSTOM_USER_HOOK_PROC.

For the sake of this blog lets assume that, we are planning to create user hook for SIT(Special Information Type) After Insert. Below are the steps involved in creating a user hook.

Create User Hook: 

Create a user hook using the API HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL. As we have already identified the user hook to be created, we need to get the hook id which is stored in the table HR_API_HOOKS. The hook id can be found using below query(HOOKS.API_HOOK_ID)

SELECT hooks.api_module_id, 
       hooks.api_hook_id, 
       hooks.hook_package, 
       hooks.hook_procedure 
FROM hr_api_hooks hooks, 
     hr_api_modules modules
WHERE modules.module_package = 'HR_SIT_API'
AND hooks.api_module_id = modules.api_module_id
AND hooks.hook_procedure like 'CREATE%'
AND hooks.api_hook_type = 'AP'; --After Process

After the hook id is identified, we need to call below procedure to create user hook. Commit your changes after it's executed. It inserts data into the table HR_API_HOOK_CALLS.

DECLARE
  l_api_hook_call_id      number;
  l_object_version_number NUMBER;
BEGIN
  hr_api_hook_call_api.create_api_hook_call
      (p_validate             => FALSE,
       p_effective_date       => TO_DATE('01-Jan-1951','DD-MON-YYYY'),
       p_api_hook_id          => 2889, --This is for After Insert Hook
       p_api_hook_call_type   => 'PP',
       p_sequence             => 4010, 
       p_enabled_flag         => 'Y',
       p_call_package         => 'CUSTOM_PKG',
       p_call_procedure       => 'CUSTOM_USER_HOOK_PROC',
       p_api_hook_call_id     => l_api_hook_call_id,
       p_object_version_number=> l_object_version_number);
END;

In the above API calls, all of the parameters are self explnatory except p_sequence.
Oracle recommends to use more than 2000 for sequence number as upto 2000 is being used by Oracle. You can add more than one user hook to an API, in that case make sure the sequence number is specified properly, for each hook creation, so that they execute in the order of your preference.

Generate User Hook:

After the user hook is successfully created with above call, the code needs to be generated in order to inject our custom code into the public API. There are two ways to generate hook package body

• Oracle provides an api to create user hook package body. Call the API hr_api_user_hooks_utility.create_hooks_one_module(p_api_module_id) with respective module id(in our case, it's 1283, as shown in above picture/sql). Sample code is shown below.
  

  BEGIN
    hr_api_user_hooks_utility.create_hooks_one_module(
                              p_api_module_id => 1283);
  END;
  

• Oracle provide a pre-processor script to generate hook package body. To generate the hook package body, in R12, run pre-processor script located at $PER_TOP/patch/115/sql/hrahkone.sql where as in 11i the pre-processor script is located at $PER_TOP/admin/sql/hrahkone.sql. This script asks for API_MODULE_ID parameter, so provide the API_MODULE_ID from above picture/sql.

It now injects custom code into the package procedure HR_SIT_API.CREATE_SIT_A. The package and procedure can be identified from above picture/sql. Now open this package and see if your custom code is present or not. If it's not present, then there must be something wrong so check carefully.

Note for 11i: The pre-processor script in 11i is located at $PER_TOP/admin/sql/hrahkone.sql. The remaining steps are same as above.

Introduction to User Hooks

Introduction to User Hooks

User hooks allows to add custom functionality to oracle supplied public API. Oracle EBS supply lot if public API to Insert/Update/Delete data in Oracle EBS tables. While doing so, the user might want to perform some custom validations/processing. This can be achieved with the help of User Hooks.

Why do I need a user hook if I can achieve the same using database triggers?
Oracle recommends not to use any database triggers on any of the EBS Tables as it tampers the integrity of the data because most of the tables in EBS are interrelated and an API is able to perform the operations on all related tables in a designated way.

How to identify if the API has user hook?
Run below SQL to find out if user hook is available for the API.
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE '<Public API Package Name>'

Ex: Below query returns the hooks available for Special Information Types(SIT)
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE 'HR_SIT_API'

If the above query returns more than one row, then it has user hooks enabled.

Can I perform custom processing on INSERT/UPDATE/DELETE operations?
Yes, it's possible but the API should have provision for the same. In the above example, it returns three rows with MODULE_NAME as CREATE_SIT, UPDATE_SIT and DELETE_SIT. So in this case it's possible to introduce custom processing for all above operations.

Like database triggers, can we execute our custom code before/after the operation?
Yes, it's possible and it's called Hooks. There are two types of hooks as shown below

• Before Process(BP): Before the processing starts, it executes custom code first.
• After Process(AP) : After the processing is completed, it executes custom code.

How to identify the hooks available for an API?

User Hooks are stored in a table called HR_API_HOOKS. This table has a column called Hook Type with values of BP(Before Process), AP(After Process) to identify type of hook.

Ex: Below query returns the hooks available for HR_SIT_API

SELECT HOOKS.* FROM HR_API_HOOKS HOOKS, HR_API_MODULES MODULES 

WHERE MODULES.MODULE_PACKAGE LIKE 'HR_SIT_API'

AND   HOOKS.API_MODULE_ID = MODULES.API_MODULE_ID;

Are user hooks upgrade safe?

Yes, custom code introduced using user hooks is upgrade safe. Might need to regenerate the user hooks if it's really required.

The next blog Creating User Hooks, discuss about how to create User Hook.

DML Operations using Query based View Object

Recently there is a request in Oracle Forums/Spaces on how to save data into db tables using View Object(which is not based on Entity Object(EO)).

We already know that only EO based VOs can handle DML operations. If the query is complex, it may not be feasible to create EOs(and associations if any) and then base your VO on the EOs. In this case, it's better to create VO directly based on your complex query and then implement the DML operations by calling PL/SQL APIs from the VO. This way you can even do DML operations on more than one table. For this to achive Oracle has already provided a class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl. By default, when we create any view object, it always extends oracle.apps.fnd.framework.server.OAViewObjectImpl which doesn't support any DML operations. In our case we need to create our View Object by extending OAPlsqlViewObjectImpl

Note: The class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl has been deprecated. So implement this method only if you don't have alternative.

Below are the steps to achieve the desired result

1. Create VO(MyVO) based on query as usual and generate the MyVOImpl and MyVORowImpl.
2. Now go to MyVOImpl.java and change the extends to OAPlsqlViewObjectImpl. So your VOImpl should look like below

public class MyVOVOImpl extends OAPlsqlViewObjectImpl

3. Now go to MyVORowImpl.java and change the extends to OAPlsqlViewRowImpl

public class MyVORowImpl extends OAPlsqlViewRowImpl

4. MyVORowImpl has three methods to carry DML operations insertRow, updateRow, and deleteRow(inherited from OAPlsqlViewRowImpl).
5. Override insertRow(byte)/insertRow and call respective PL/SQL procedure to insert row into the table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.
6. Override updateRow(byte)/updateRow and call respective PL/SQL procedure to update row in table. As this is RowImpl class you will get all values using their getter methods.Check javadoc for information about these two methods.
7. Override deleteRow(byte)/deleteRow and call respective PL/SQL procedure to delete row in table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.

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

How to run PTO Carryover process for one employee

In Oracle HRMS, if we run the PTO Carryover process, it runs for all employees. But as a developer you might need to run carryover for one employee or set of employees for testing purposes. Below are the steps to follow to achieve the same. This method involves changing Oracle supplied package so please make sure to revert back your changes once your testing is complete.

• Open the package PAY_US_PTO_CO_PKG and go to the function PTO_CARRY_OVER_FOR_PLAN.
• Go to the cursor CSR_GET_ASSIGNMENT and add a condition to filter based on assignment id as asg.assignment_id = <assignment_id> or asg.assignment_id IN (<assignment_id1>, <asignment_id2>, etc)
• Optional: Turn on Logging by inserting/updating a row into PAY_ACTION_PARAMETERS table with LOGGING parameter with a value G. It means that, when you run the PTO Carryover process, the log messages in the package are displayed as Concurrent program log messages which can be viewed by clicking on View Log button after the Carryover process is completed. You can even add more log messages in the package but make sure you remove them once your testing is complete.
• Run PTO Carryover process as normal but now it runs only for one employee. Check the log messages if you enable logging
• Don't forget to remove your changes once the testing is done.

FYI: Oracle has already published a Note#261617.1 on how to run carryover for one employee. In this note, they haven't mentioned about the package which needs to be changed but about a file where the package resides. So we need to open that file and get the package name and then follow the other instructions as mentioned in the note.

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

Launching Web Page From Oracle APPS Forms

We already know that OAF page can be launched from Oracle Applications PUI. We can also launch a web page or custom built html from Oracle Applications PUI. Below steps explains you on how to achieve the same.

Note: In 11i, we used to achieve this functionality by creating a FND function of type SSWA plsql function that opens a new window (Kiosk Mode) or SSWA plsql function. But from R12, these types should not be used. So we have to follow below procedure to launch a web page or custom html from Oracle Applications  PUI.

1) Create an .htm file calling the external link, for instance "xxpage.htm". 

<html>
<head>
<meta http-equiv="Refresh" content="0; URL=http://google.com">
</head>
</html>

or a custom html content

<html>
<body> 
<b>Our custom page</b>
</body>
</html>

2) Put this file under $OA_HTML directory. Check the permissions of the file

3) Go to System Administrator responsibility
4) Define a new fnd function as follows
- Enter function name and user function name 
- Function Type : 'JSP Interoperable with OA'
- HTML Call : xxpage.htm  

5) Search for the FND menu where you want to add access to our custom html. Add the newly created function to the menu with a proper name(displays in the navigator).

6) Clear cache and launch the page from Navigator. It's inevitable to clear the cache or else it raises a security exception upon launching the page.

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

Assigning Enter Key to a button on OAF Page

In lot of web pages if we press enter, then it submits the page. Similarly we can also submit the page on pressing Enter key in our Custom OAF pages. If there are more than one button then we can assign Enter key to one of the button. This way the user can submit the page by just pressing on Enter key. Below code gives you an idea on how to assign enter key to a button(page can contain any number of submit buttons). It's obvious that we can only assign Enter key to one button in a page. So when the user presses enter key, the page gets submitted and execute the respective code.

Note: When we implement Search bean in OAF, by default Enter key is assigned to Go button in the search bean. So when the user clicks on enter search gets executed automatically.

import java.util.Hashtable;
import oracle.apps.fnd.framework.webui.OABoundValueEnterOnKeyPress;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    OAPageLayoutBean page = pageContext.getPageLayoutBean();

    Hashtable applyParams = new Hashtable(); 
    /*There is a button in the page with id 'Apply' and
    we are assigning Enter key to this Apply button.*/
    applyParams.put ("Apply", "VAL1");
    page.setAttributeValue(
          OAWebBeanConstants.ON_KEY_PRESS_ATTR,
          new OABoundValueEnterOnKeyPress(pageContext,
                "DefaultFormName", // enclosing form name
                applyParams, // request parameters
                false, // client unvalidated
                false)); // server unvalidated
}

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);

    /*When the user clicks on Enter key then the page 
    consider it like Apply button is pressed.*/
    if (pageContext.getParameter("Apply") != null)
    {
       --Do your work here 
    
    }
}

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

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.