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:
$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.

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.

4 Responses

  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…

    Like

  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.

    Thanks,
    Sunvin.

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s