Enable or Create User Hooks, JTF_USER_HOOKS

API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance, a user may want to implement User Hooks for one of the following reasons:

To extend the validation of data beyond what the standard system provides.
To maintain data held in extra customer specific tables (not part of Oracle Applications) as it is entered.
To send alerts when particular events happen within Oracle Application
User Hooks offer similar functionality to the Custom Library, but at the database end rather than the front end.

Advantages
User Hooks allow extra business logic to be inserted in exactly the right place in the application without needing to bespoke any of the standard APIs. Upgrades are no problem as the hooks will be regenerated during the upgrade process. However, Oracle reserves the right to change the HR schema at any time, which may necessitate modifications to customized PL/SQL procedures.

The main advantages of hooks over custom library are that they only need to be implemented in one place whereas custom libary modifications could conceivably have to be made on several clients. They are also immediately available to any interface that calls the API. For example, Forms, Self Service, Data Pump, etc.


Enabling USER HOOKS
The Package CS_SERVICEREQUEST_PUB makes a call to the CS_SERVICEREQUEST_CUHK User Hook. This call is made depending on values stored in the table JTF_USER_HOOKS. For this call to be made, a row with Package Name as CS_SERVICEREQUEST_PUB and an appropriate API Name and the Enabled Flag set to 'Y' should be present in the Table.

Implementation Steps:
1) First you have to identify the particular API, which you are looking to modify/customize. The list of APIs, which support this concept, can be found in following table –
a)CRM Related APIs – Jtf_User_Hooks
You can use the following Query –
Select Pkg_Name,Api_Name ,processing_Type ,Execute_Flag From Jtf_User_Hooks

If User Hook Package has no Package Body
This is the Customer User Hook API. The Customers can add customization procedures here for Pre and Post Processing. Oracle only supplies the spec file.It is left to the customer to create the body for their customizations.

Example :You will need to make your own insert(s) into the JTF_USER_HOOKS table.

Example: Lets say you want to call your custom procedure after the charge details have been created. So, you want CS_CHARGE_DETAILS_CUHK.Create_Charge_Details_Post to be executed. To do this, you need to insert the following values into the jtf_user_hooks table

INSERT INTO jtf_user_hooks(USER_HOOK_ID

,PKG_NAME
,API_NAME
,PRODUCT_CODE
,PROCESSING_TYPE
,EXECUTE_FLAG
,USER_HOOK_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5)
VALUES(1000000 -- A large value so that no conflict
,'CS_CHARGE_DETAILS_PVT' -- Package Name
,'Create_Charge_Details' -- Procedure Name
,'CS' -- For Service
,'A' -- After, i.e. Create_charge_details_post
-- Replace A with B in the above line if you want to use Pre-User hook.
,'Y' -- Execute Flag
,'C' -- Customer User Hook
,1234 -- fnd_global.user_id
,sysdate
,1234 -- fnd_global.user_id
,sysdate
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,NULL);

JBO Date Conversion in OAF

Cast java.util.Date to oracle.jbo.domain.Date
1). dbDate= new oracle.jbo.domain.Date(new java.sql.Timestamp(inputDate.getTime());

2). Sample method
public oracle.jbo.domain.Date convertUtilToJboDate(java.util.Date pJavaDate)
{
return new oracle.jbo.domain.Date(new Timestamp(pJavaDate.getTime()));
}

Cast oracle.jbo.domain.Date to java.util.Date
1). Sample method
public java.util.Date convertJboToUtilDate(oracle.jbo.domain.Date pJboDate)
{
return new Date(pJboDate.timestampValue().getTime());
}

OA Framework Interview Questions

http://anuj-oaffaq.blogspot.com/

Oracle apps standard query

To Find invalid objects:
select owner, object_name, object_type,status from all_objects
where status != 'VALID' order by owner, object_type, object_name;

Validate username/password combination:
select fnd_web_sec.validate_login('AK185109','ak185109') from dual ;

If it return N then look the error message by this query
select fnd_message.get from dual;

Find the Instance:
select NODE_NAME, NODE_ID , SERVER_ID , SERVER_ADDRESS from FND_NODES;

Validate the ICX_PARAMETERS table:
select * from icx_parameters;

Find the Responsiblity :
select responsibility_id,responsibility_key,responsibility_name,description from fnd_responsibility_vl where responsibility_key = 'SERVICE'

Find the Menu, Function:
SELECT * fnd_responsibility_vl a,
fnd_menu_entries_vl b,
fnd_form_functions_vl c
where a.responsibility_id=20638
and a.menu_id=b.menu_id
and b.function_id=c.function_id

AQ, Agent and Subscription Creation Script

------------------------------------------------------------------------
-- 1. Check whether the Qtable exist, if not then create
-------------------------------------------------------------------------
l_qtable_name:='QTABLENAME';
l_q_name:='QUEUENAME';

SELECT COUNT(*)
INTO v_q_table_count
FROM user_tables
WHERE table_name = l_qtable_name;

DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => l_qtable_name, multiple_consumers=> TRUE, queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');
DBMS_AQADM.CREATE_QUEUE (queue_name => l_q_name, queue_table =>l_qtable_name,retention_time=> 86400);
DBMS_AQADM.START_QUEUE (queue_name => l_q_name);

v_q_subscriber := sys.aq$_agent('USERNAME', NULL, NULL);
dbms_aqadm.add_subscriber(queue_name => l_q_name, subscriber => v_q_subscriber);
DBMS_OUTPUT.PUT_LINE('Subscribed to USER = USERNAME' );

Open Cursor Per session

declare cursor c1 is
select distinct(s.sid) sid,s.serial# serial, a.value cumulative, c.value current_open
from v$sesstat a, v$statname b,v$sesstat c, v$statname d,v$session s
where a.statistic# = b.statistic#
and c.statistic# = d.statistic#
and s.sid=a.sid
and s.sid=c.sid
and b.name = 'opened cursors cumulative'--current'
and d.name = 'opened cursors current'
and s.machine like '%machine name%'
and s.program like 'JDBC%'
and s.module like 'JDBC%'
order by s.sid;
begin
for eachrow in c1 loop
dbms_output.put_line(to_char(systimestamp,'DDMMYYYYhh24mi')||' '||eachrow.sid||' '||eachrow.serial||eachrow.cumulative||' '||eachrow.current_open);
end loop;
end ;

JDR UTILITY in OAF

In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?

The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter.

and Through the JDR API you can get all this information from the database with the help of these statement..

jdr_utils.listdocuments('/oracle/apps/cs/', TRUE);
jdr_utils.listdocuments('/oracle/apps/cs/pagging//server/',TRUE);
jdr_utils.listcustomizations(p_document => '/oracle/apps/cs/pagging/webui/CustomPaggingPG');
jdr_utils.printdocument(p_document => '/oracle/apps/cs/pagging/server/CustomPageAM');
jdr_utils.printdocument(p_document => '/oracle/apps/cs/pagging/webui/CustomMailPG');
jdr_utils.deletedocument(p_document => '/oracle/apps/cs/pagging/server/CustomPageAM');