How to get table/Column data in oracle apps from front end (FND_TABLES,FND

Friday, May 15, 2015

How to get table/Column data in oracle apps from front end (FND_TABLES,FND_VIEWS)

Being a technical guy, it’s always easy to view the table and view details via database tools like TOAD, SQLDEVELOPER. But, it’s good to know that we can view the metadata details of Oracle apps tables via front end also. This post explains the same.

Further, if you have to define a descriptive flex field based on the custom table. Then, we have to register the custom table details in Oracle apps fnd tables.

Database Table Details:

Step 1:

Then Query for the table. For example: RA_CUSTOMER_TRX_ALL

Last Query is :

SELECT ROWID,
       application_id,
       table_id,
       table_name,
       user_table_name,
       description,
       hosted_support_style,
       table_type,
       initial_extent,
       next_extent,
       min_extents,
       max_extents,
       pct_free,
       pct_used,
       pct_increase,
       ini_trans,
       max_trans,
       auto_size,
       last_update_login,
       last_updated_by,
       created_by,
       creation_date,
       last_update_date
FROM   fnd_tables
WHERE  ( table_name LIKE 'RA_CUSTOMER_TRX_ALL' )
ORDER  BY application_id,
          table_name

Step 2: Press the Button Indexes to view index details

Step 3: Go Back to main Screen and Press the Button “Primary Keys”

Last Query:

SELECT ROWID,
       primary_key_sequence,
       primary_key_id,
       table_id,
       application_id,
       column_id,
       last_update_login,
       last_updated_by,
       created_by,
       last_update_date,
       creation_date
FROM   fnd_primary_key_columns
WHERE  ( primary_key_id = 656 )
       AND ( table_id = 52566 )
       AND ( application_id = 222 )
ORDER  BY primary_key_sequence

Step 4: Go back to main screen and Press the Button “Foreign Keys”

Last Query:

SELECT ROWID,
       foreign_key_sequence,
       cascade_value,
       application_id,
       column_id,
       foreign_key_id,
       table_id,
       last_update_login,
       last_updated_by,
       created_by,
       last_update_date,
       creation_date
FROM   fnd_foreign_key_columns
WHERE  ( application_id = 222 )
       AND ( foreign_key_id = 9485 )
       AND ( table_id = 52566 )
ORDER  BY foreign_key_sequence

Database View Details:

Last Query:

SELECT ROWID,

       column_sequence,
       column_name,
       view_id,
       last_updated_by,
       last_update_login,
       last_update_date,
       creation_date,
       created_by,
       application_id
FROM   fnd_view_columns
WHERE  ( view_id = 35915 )
       AND ( application_id = 0 )
ORDER  BY column_sequence;

In Future posts, I will try to give scripts for registering custom tables

Oracle Apps Knowledge Sharing

Friday, May 15, 2015