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.



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.



Example of how the hyperlinks are coded in the template:
$url&TransLE=:TRANSACTING_LE&TradLE=:TRADING_PARTNER_LE&rows=All??target=_top

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.



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.