Searching for the navigation of a form in Oracle Application

Many times it happened to me that I know that there is some custom or standard form out there in some navigation under some responsibility of Oracle Applications. There is no direct way of finding this from front end or through a simple back end query. I was working with a new project where there were a lot of custom Menu entries which I did not know the navigation. So finally I wrote the following query.





SELECT LPAD ('*', (LEVEL - 1) * 2, '*') || prompt NAVIGATION,

DECODE (menu_type,'R','Responsibility',NULL) Type

FROM (SELECT prompt,

LEVEL menu_level,

TYPE menu_type,

menu_id,

sub_menu_id

FROM ( SELECT prompt,

menu_id,

sub_menu_id,

function_id,

'M' TYPE

FROM fnd_menu_entries_vl

WHERE prompt IS NOT NULL

UNION

SELECT responsibility_name,

0,

menu_id,

-1,

'R'

FROM fnd_responsibility_vl

WHERE NVL (end_date, SYSDATE + 1) > SYSDATE) fme

START WITH UPPER (prompt) LIKE Upper('&Menu_Name')

CONNECT BY PRIOR menu_id = sub_menu_id)

START WITH menu_type = 'R'

CONNECT BY PRIOR sub_menu_id = menu_id




Just give the approximate name for the menu item as input parameter and the query will output all available navigations to this menu item.

Script to register Table and Columns in Oracle Applications

Last day I was trying to register a flex field on a custom table. I could not find my custom table in the Tables LOV. Then it occurred to me that I have to register my custom table for it to be available in any LOV in apps. So I navigated to the Table Registration screen to find it read only .

After a bit of browsing I found that I had to use an API for the purpose. So I used ad_dd.register_table API to do the same. That was not the end and now I had to register each of my 40 plus columns one by executing ad_dd.register_column API. I knew there should be an easier way. I googled but could not find the code to register the table as well as columns in one short.

Finally I wrote the script myself.
CREATE OR REPLACE PROCEDURE register_table (
table_name VARCHAR2,
application_short_name VARCHAR2
)
AS
status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )
IS
SELECT column_name,
data_type,
data_length,
nullable,
ROWNUM,
data_precision,
data_scale
FROM all_tab_columns
WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE )
IS
SELECT constraint_name,
table_name,
status
FROM all_constraints
WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (
p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE
)
IS
SELECT acc.constraint_name,
acc.column_name,
acc.POSITION
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.table_name = p_table_name
AND ac.constraint_type = 'P'
AND ac.owner = p_application_short_name;
BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_tab_type => 'T'
);

FOR r_columns IN c_columns (table_name)
LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_col_name => r_columns.column_name,
p_col_seq => r_columns.ROWNUM,
p_col_type => r_columns.data_type,
p_col_width => r_columns.data_length,
p_nullable => r_columns.nullable,
p_translate => 'N',
p_precision => r_columns.data_precision,
p_scale => r_columns.data_scale
);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,
'ENABLED', 'Y',
'N'
)
INTO status
FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,
p_key_name => r_constraints.constraint_name,
p_tab_name => table_name,
p_description => 'Primary Key for Table '|| table_name,
p_key_type => 'D',
p_audit_flag => 'N',
p_enabled_flag => status
);
END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '||
r_constraint_columns.column_name||
' for Constraint '||
r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,
p_key_name => r_constraint_columns.constraint_name,
p_tab_name => table_name,
p_col_name => r_constraint_columns.column_name,
p_col_sequence => r_constraint_columns.POSITION
);
END LOOP;

END register_table;

The procedure will register the table, all the columns of the table and the primary key in Apps. It can be executed as
begin
register_table('XXAP_TEST_TABLE','SQLAP');
end;

Now that the script is in place, I could register the table and columns in single shot and I could get the table and columns in the LOVs.