Table Registration API

You register your custom application tables using a PL/SQL procedure in the AD_DD package.

Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.
Procedures in the AD_DD Package

1. Procedure REGISTER_TABLE

procedure register_table ( p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

2. Procedure REGISTER_COLUMN

procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

3. Procedure DELETE_TABLE

procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);

4. Procedure DELETE_COLUMN

procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

To find out the table associated with which application, You can use these queries

select * from fnd_tables where table_name = 'CS_INCIDENTS_ALL_B'
select * from fnd_application where application_id = 170

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 ;