Order Capture User Hooks

I. Introduction
II. Vertical and Custom Hooks
III. Implementing Hooks
IV. Returning Errors
V. Enabling and Disabling Hooks
VI. Notes and Recomendations

Scope

I. Purpose

User Hooks are a feature that allows customizing the application in a way that is less invasive and that can be easily disabled for diagnosing problems with the customization. Hooks are code that is conditionallyexecuted by some application packages. The condition is that the hook has to be enabled.
Hook calls are interleaved in some of Order Capture (ASO) APIs, mostly on public APIs. There are two types of Hooks:
Pre-processing Hooks: Are hooks that execute (if enabled) before the API does any actual processing. These are good for modifying/adding information so the API can proceed with that change in place. These hooks are of type B (from Before)
Post-processing Hooks: re hooks that execute (if enabled) after the API did all processing. Post hooks are usually good for cleanup of a pre-hook or for further processing after the vanilla API has finished work. These hooks are of type A (from After).
In an API that support hooks the following structure is observed:

PROCEDURE <proc name>
<variables>
BEGIN
...
IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C')) THEN
<CALL TO PRE-HOOK>
<ERROR HANDLING>
END IF;
...
<ACTUAL API PROCESSING CODE>
...
IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C')) THEN
<CALL TO POST-HOOK>
<ERROR HANDLING>
END IF;
...
END;

[Top]

Details

II. Vertical and Custom Hooks

Custom hooks are available for implementation customizations. Vertical hooks are reserved for Oracle Development use.

Vertical hooks are called the same as custom hooks but the last parameter instead of a 'C' is a 'V'. These hooks should not be enabled or disabled by customer and the state must be left unchanged. Also no custom code should be implemented in Vertical hooks.

The actual name of the package.procedure of a hook is found in the API calling the hook. For example in the procedure ASO_QUOTE_PUB.delete_quote (asopqteb.pls) you can see:

IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C')) THEN
ASO_QUOTE_CUHK.Delete_quote_PRE(
P_Qte_Header_Id => l_qte_header_id,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count ,
X_Msg_Data => x_msg_data
);

IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C')) THEN
ASO_QUOTE_CUHK.Delete_quote_POST(
P_Qte_Header_Id => l_qte_header_id,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );

Thus the pre and post “delete_quote” custom hooks are named:

ASO_QUOTE_CUHK.delete_quote_pre (asocqtes.pls)
And
ASO_QUOTE_CUHK.delete_quote_post (asocqtes.pls)

 The Quoting User Hooks are automatically populated in the JTF_USER_HOOKS table according to Quoting Implementation Guide :

"For user hooks to work properly, the following SQL scripts must run:
■ asocruhk.sql — The Populate Script populates data into the JTF_USER_HOOKS table. This script is run automatically."

 If by any reason the user hooks are not available in the table, please run manually the script above.

[Top]

III. Implementing Hooks

Only the package specification of the hook (Package: ASO_QUOTE_CUHK) is seeded in the application. Package body must be created (and corresponding custom code added).
All package specification procedures must at least be implemented as a NULL procedure to avoid package body compilation errors.

Example:

If seeded package spec for a hook is:

Create or replace package xyz_hook as
Procedure action1_pre(<params>);
Procedure action1_post(<params>);
Procedure action2_pre(<params>);
Procedure action2_post(<params>);
End;

And if you want to implement action1_pre, then body must look like:

Create or replace package body xyz_hook as

Procedure action1_pre(<params>) as
<variables>
BEGIN
  x_return_status := fnd_api.g_ret_sts_success;
  <Custom Code>
END;

Procedure action1_post(<params>) as
BEGIN
NULL;
END;

Procedure action2_pre(<params>) as
BEGIN
NULL;
END;

Procedure action2_post(<params>) as
BEGIN
NULL;
END;

End;

[Top]

IV. Returning Errors

Although typically the application can manage an exception thrown by a custom hook, it is recommended to return errors posting then on the FND Message Stack and returning an error in the corresponding parameter:

IF <error condition> THEN
FND_MESSAGE.Set_Name('<APPL>', '<MESSAGE NAME>');
FND_MSG_PUB.ADD;
FND_MESSAGE.Set_Name('<APPL>', '<MESSAGE NAME>');
x_msg_data := FND_MESSAGE.get;
x_msg_count := 1;
x_return_status := FND_API.G_RET_STS_ERROR;
END IF;

<APPL> and <Message Name> must be defined in the FND message dictionary: Application Developer Application Messages. Here a language independent message name is defined and a language dependent message text is entered. The application will show the error on the language the user has currently selected.


[Top]

V. Enabling and Disabling Hooks

Once the package body of the hook is implemented and compiled into the database, the hook can be enabled.

There is no user interface for enabling/disabling hooks. The procedure must be done directly in the database with SQL*Plus.

The table JTF_USER_HOOKS holds the hook information. To activate a hook the following statement must be used:

UPDATE JTF_USER_HOOKS
SET EXECUTE_FLAG = 'Y'
WHERE PKG_NAME = 'ASO_QUOTE_PUB'
AND API_NAME = upper('&ENTER_API_NAME')   --<< 'CREATE_QUOTE' or 'UPDATE_QUOTE'
AND PROCESSING_TYPE = '&ENTER_PROC_TYPE'  --<< 'B' for PRE or 'A' for POST
AND USER_HOOK_TYPE = 'C';

COMMIT;

To disable a hook, simply use the same SQL above but instead of execute_flag = 'Y' use execute_flag = 'N'.

With the same purpose the script $ASO_TOP/patch/115/sql/asoenuhk.sql is provided.


[Top]

VI. Notes and Recomendations

- Never change the execution_flag of a HOOK_TYPE 'V'

- Package name is the name of the API package where hook will execute. Eg: ASO_QUOTE_PUB

- Api name is the name of the API where hook will execute. Eg: DELETE_QUOTE

- Processing type is when the hook will execute. Enter B (pre-hook) or A (post-hook).

- According to DEV's recommendation in bug 9434049 , make sure that all the output parameters are populated correctly for implemented user hooks. In case the return status is not populated correctly for an enabled user hook, Quoting Forms will most likely error out or produce incorrect results.

- As of 11.5.10 Quoting does NOT fire Quote Line hooks. Quote Header Hooks need to be used instead, and will get line information for lines in line tables. HTML Quoting still fires Line Hooks independently. Explanation is:

* Forms Quoting calls Aso_quote_pub.update_quote and this does Fire Quote Header Hooks.


* Calls aso_quote_headers_pvt.update_quote, which in turn calls  ASO_QUOTE_LINES_PVT.Create_Quote_Lines . ASO_QUOTE_LINES_PVT does NOT fire Quote Line Hooks. Only ASO_QUOTE_PUB fires Line Hooks.


* The solution for scanning line events is as follows.


* Code a hook for UPDATE_QUOTE_PRE (or POST)

* Scan the Line Table for the operation being performed: CREATE, UPDATE or DELETE

* For example, should it be needed to perform code before a line delete

PROCEDURE Update_quote_PRE(...) is
BEGIN
FND_MSG_PUB.initialize;
For i IN 1..P_Qte_Line_Tbl.count LOOP
IF P_Qte_Line_Tbl (i).OPERATION_CODE = 'DELETE' THEN

_____ your code here _______

END IF;
END LOOP;
END;

- When a user hook is fired at line level ASO code does not fully populate the quote header IN parameter, p_qte_header_rec. Instead only the quote header is populated and the rest of the information can be extracted from aso_quote_headers_all using this ID. If different custom code needs to be executed at quote header and quote line level, a simple check should be implemented to determine whether the user hook was fired at quote header or quote line level :

PROCEDURE Update_quote_PRE(...) is
BEGIN
FND_MSG_PUB.initialize;
IF p_qte_header_rec.quote_number = FND_API.G_MISS_NUM

---- operation done at line level

ELSE

--- operation done at header level
END IF;
END;

- In order to be able to use the user hooks for Credit Check feature, please apply patch 8417451 for Release 11.

- Copy quote operation does NOT fire any user hooks, due to the fact that the operation is not being handled by ASO_QUOTE_PUB package, but instead it is done in ASO_COPY_QUOTE_PUB, which doesn't have an integration with user hooks. Enhancement Request bug 3506612 is already logged on this issue.

Note: Do not try to implement commit or full rollback statements in your custom PL/SQL. This will interfere with the API processing and will generate an error.

 

Thanks

Srini