Creating Custom Reports with BI Publisher - Table Structures

Publication Date: 06/07/2019

Share This Article

In this tutorial, we will focus more on how the tables are structured in Oracle Fusion Cloud (FSCM), and how to extract and link tables for reporting. For general instructions on how to use BI Publisher to create reports, please refer to the tutorial "Creating A Custom Report Using BI Publisher".

Data needs to be extracted into a data model first, and then linked to a report template in BI Publisher. While Oracle does offer documentations for the tables, this is assuming that you know which table you are extracting data from.

From experience, however, knowing the exact table where the specific data is located is not always the norm. In fact, people may be spending more time finding the table and the data than time spent on actually extracting it. This tutorial will help users find the table names more quickly and efficiently.

EXTRACTING TABLE AND COLUMN NAMES FROM FSCM

If you are new to FSCM, and don’t know what tables are there, I recommend using the statement below (in the text-area) to extract all the column and table names from FSCM.

After using the above statement to extract all the names, you should then export those column and table names to an Excel spreadsheet for future reference purpose. These tables follow a certain naming convention that we can use to search for a column (assuming that we have a general idea of which module the data resides in).

For example, all the data from the AP module are stored in tables that start with "AP_" are shown below:

Common PO Module table names are shown below:

Some other common table names are shown below:

The most common table names used for reporting have been shown in this article. For more information, feel free to send us a message using the form below, and one of our consultants will get back to you shortly.