Customize the Data Models of seeded Oracle Fusion Applications reports

This article further expands on another article I wrote: "Customize the layouts of seeded Oracle Fusion Applications reports". The previous article talks about modifying only the visual aspects of the report, but not the underlying data.

There might be some cases wherein an organization would like to use the Seeded Report functions of Oracle Fusion, but may be required to add more details (such as a Descriptive FlexField, or another column in the Table).

In my experience, I've been tasked to add the name of the Legal Entity of seeded reports from Receivables (such as Invoices, Statements and Credit Memos), Procurement (such as Purchase Orders and Requisitions), Collections (such as Dunning Letters) and Payments (such as a Separate Remittance Advice).

The best and recommended way to customize seeded reports is to use BI Publisher's "Customize" function. But before you can actually modify the report, you have to first know the following:

1. Identify under what group / pillar the report belongs to (Ex. Financials, HCM, Procurement)
2. Identify the report's functionality (Ex. Invoice Report, Payroll, Purchase Order)
3. Identify if the report is being called from the application screen via a function button, a menu, etc (more on this later).
4. Familiarity with the expected report output and its data.
5. Have the sufficient roles assigned such as BI Author and BI Administrator

For this example, we will modify the seeded Receivables Invoice report's underlying SQL.

To begin customizing a seeded Oracle Fusion Report, you may execute the following steps:

1. Login to Oracle Fusion Applications using your Username and Password
2. Click on the browser's address bar and change the URL to below to go to the BI Publisher Enterprise console



3. Once inside BI Publisher Enterprise console, go to "Catalog" to access the Reports Catalog


4. On the Folders pane, Expand "Shared Folders", then select the application that contains the Report to be modified (ex. "Financials"), then open the folder of the report's functionality (ex. "Receivables")


5. Once inside the folder, choose the correct report and click on "More", then choose "Customize". Note that you may have to go deeper into the sub-folders to see the correct report. In this case, one has to navigate to the following folders: Receivables > Bill Presentment and look for the "Invoice Print Template" report:


6. The Customize function will effectively copy the said report and folder structure under the "Custom" folder. However, note that this step only copies the report and its seeded layouts to the Custom folder, but still uses the seeded Data Model. Go back to the Reports Catalog by clicking on the "Catalog" link on the BI Publisher Enterprise's Global Area:



7. On the Folders pane, Expand "Shared Folders" and further down to the "Custom" Folder. Again, select the application that contains the Report to be modified (ex. "Financials"), then open the folder of the report's functionality (ex. "Receivables"). You will now see the copy of the Report ("Invoice Print Template") that is ready to be customized.


8. This Custom folder will also contain the customized data model. To proceed, click on the "+" Icon and add a new Folder called "Data Model". Please note that this folder name and structure is recommended by Oracle and using a different approach might result into data loss during upgrades or patches.


9. Go back to the folder where the seeded report is placed (Shared Folders > Financials > Receivables > Bill Presentment) and go further down a sub-folder also named "Data Models". This will contain the seeded Data Models of each corresponding Report.


10. Select the Data Model of the seeded report and click on "More" > "Copy". Then go into the Custom Data Model Folder (Shared Folders > Custom > Financials > Receivables > Bill Presentment > Data Model) and click on the "Paste" Icon.




11. Go back on folder higher (Shared Folders > Custom > Financials > Receivables > Bill Presentment) and select the Report and click on "Edit":



12. On the report page. Hover over the Data Model link and you will see that it points to the seeded Data Model (/Financials/Receivables/Bill Presentment/Data Models/Transactions Print Data Model). To point this to the custom Data Model, click on the search Icon and navigate to the custom Data Model.




13. Once selected, hover once more on the Data Model link and you will see that it now points to the custom Data Model (/Custom/Financials/Receivables/Bill Presentment/Data Models/Transactions Print Data Model). Note that Oracle does not recommended to change the custom Data Model's name as it will impact how the seeded report is being run.


14. Proceed to click on the Data Model link and you will be directed to the Data Model console. Here, you can modify, add or remove data sets, add parameters, Triggers, List of Values (LOVs) and Bursting Options.

For more information on modifying Data Models, you may check out the Oracle Documentation on BI Reports Customization. For more information on the usage of seeded tables in Oracle Fusion, check out the technical documentation on Oracle Tables and Views.

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)