Using BIP for Interactive Reconciliation Reports

Written by Bidisha Silveira

I’m going to give a little more detail on how the Interactive Financial reports David described here were put together. In 4 simple steps (with pictures!)

Step 1:

The Reconciliation Data Extract background program is submitted which collects all the data and generates an xml file

Step 2:

To view the reconciliation data we use a page built in OAFramework. The BI Publisher region to view the formatted report is embedded in this page.

When you click submit the xml file generated from Step 1 along with details of the layout template and the type of output required (eg Excel or html or pdf) is passed to the BI Publisher engine. The BI Publisher engine processes this information and displays the formatted output on the current page.

Recon 1

Now you are ready to go. The first report displays the summary of the Payables and Receivables balances between a pair of trading Legal Entities.

In order to drill down further, the hyperlinks on the Payables Receivables Balance amounts can be clicked.

Step 3:

The hyperlinks in the Reconciliation Summary region link back to the same page, containing as parameters the details of the Transacting and Trading Legal Entity for which the details have been requested.

recon 2

Example of how the hyperlinks are coded in the template:

The controller for the Reconciliation OAFramework page looks at the parameters and determines which template has to be used to show the next level of details.

A new window is opened to display the second level of the reconciliation details. The name of the template, details of the xml file created in step 1 and the type of output required is passed to the BI Publisher engine. The BI Publisher engine displays the formatted output based on the parameters passed.

To view the journals that make up the Receivables and Payables balance, the hyperlinks on the Amount fields can be clicked.

Step 4:

As before, the hyperlinks in the ‘Summary by Source’ region link back to the same page, containing as parameters the details of the Transacting Legal Entity, Trading Legal Entity, Source and Category for which the details have been requested.

Recon 3

Example of how the hyperlink is coded in the template:
$url&TransLE=:SRC_TRANS_LE&TradLE=:SRC_TRAD_LE&Curr=:TRX_CURR&JrnlSrc=:JOURNAL_SOURCE&JrnlCat=:JOURNAL_CATEGORY &AcctType=R??target=_top

This gives a really powerful online report that is very easy for users to configure by just modifying the templates.

Author: David Haimes

I'm Senior Director in the Oracle Research and Development Organization, with close to 20 years working in various roles on the development of the Financial Management product suite.  Since the summer of 2016 my focus is exclusively on working with customers and longer-term design work, particularly around next-generation functional and technical architecture. My task is to figure out NOW what the financial management system of the next 3, 5 or more years should look like and start working toward it.  At the moment the majority of my time is spent working on Blockchain or Distributed Ledger Technologies (DLT), leading the effort for all of SaaS applications.  I'm also interested in AI, Machine Learning and new UX and interaction paradigms such as chat bots. I started out in Oracle UK and found my way out to Oracle's Redwood Shores, California HQ in May 2000.  My previous role was product owner for Fusion Accounting Hub, General Ledger, Intercompany and Legal Entity products in Oracle Fusion Financials and eBusiness Suite General Ledger. I have also worked on EMEA Globalizations, Federal and Public Sector Financials, XML Payments and a variety of projects on other products down the years.

4 thoughts on “Using BIP for Interactive Reconciliation Reports”

  1. Hi could u pls suggest me hw can i apply an intercompany receipt (a customer belong 2 companies and send one cheque against his outstanding balance in 2 comapanies). In R12 environment…

    Pls reply…


  2. Hi,

    I need to call BI publisher engine with required parameter through URL to generate pdf report. How can I do that.
    My xml data is coming from view object of oaf and Layout template is RTF from XML Publisher. Through URL I need to download this pdf dynamically by passing required parameter can you please help me.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s