Loading

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

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

Sridevi Koduru said...
July 7, 2016 at 12:49 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.