Friday, December 8, 2023

How to Find Base Tables related to Application Composer Custom Pages in Oracle Fusion Cloud

In the last post, we saw how to create custom pages in Fusion Cloud.

Now, lets see how to locate the base tables related to the custom pages/objects created using Application Composer.

Option 1:

-> Navigate to Application Composer

-> Navigate to Metadata Manager

-> Click Generate under Configuration Report section

You will see a pop-up asking for selecting objects and metadata types

Select all default values and click Generate

You will see this message

Once completed, Navigate to Export option and select Excel

This will download an excel file.

Open the excel file, Filter the data and look for your custom object e.g. Customer Details

Now let's click on the Object Name

This will take us to the tab associated with our custom object.

Here we will be able to see the base table and column details

Option 2:

Let's see how we can derive the table details via query approach.

- First let's find the usage_id based on our custom objects 'CustomerDetails%'

select *
from fusion.adf_extensible_table_usage
where ENTITYDEF_FULLNAME like '%CustomerDetails%'

- Link Table Ids and link to below query

select *
from fusion.adf_extensible_table where TABLE_ID IN (300000000030505,300000000051618)

Final Query:

select *
from adf_extensible_table where table_id in (select table_id from adf_extensible_table_usage where context_column_value like 'Customer%Detail%')

- Now if we query this table and filter for our custom object under attribute_category, we will see all the information entered via our custom page

We can use these tables to build custom BI reports in order to visualize / extract the data for end-users.