Oracle Financial Analytics: General ledger to sub-ledgers navigation

.

When implementing Oracle Financial Analytics, it has been noticed that it is a common requirement to enable navigation from general ledger to sub-ledgers reports to obtain information on specific transactions. One of the main reasons for this is to reconcile general ledger to individual sub-ledgers.

The general ledger is the master set of accounts that summarise the sub-ledger transactions. It is used to add information into the financial statements and journal entries.  A sub-ledger is a detailed record of transactions for an individual account that contains details of transactions for an account.

Sub-ledgers serve as support for amounts posted to the general ledger. It is important to reconcile the general ledger balances to the sub-ledger balances on a regular basis to spot possible discrepancies.

In this blog article, I will describe how this process can be approached in OBI Apps. I will describe the OBI Apps data model and the configuration required. Also, I will describe the creation of sample navigation from general ledger to sub-ledger by the creation of an intermediate analysis.

The animation below shows the expected navigation from GL Account balance to Payables details.

Financial Analytics

Note: This information applies when the data source is Oracle EBS.

OBI Apps allows this navigation by default using the ‘GL Journal ID’ column as a link between subject areas. It is available in general ledger transaction subject area and in the following sub-ledgers (In some cases, ‘GL Journal ID’ has to be exposed to the presentation layer):

  • Accounts Payable
  • Accounts Receivable
  • Revenue
  • Purchase Orders
  • Fixed Assets (Available in OBIA 11g)

Understanding the OBIA model

‘GL Journal ID’ is a unique identifier of an EBS journal entry line and it is available in the linkage table W_GL_LINKAGE_INFORMATION_G.

Sub-ledger transaction tables are linked to W_GL_LINKAGE_INFORMATION_G at source distribution level.

Finantial Analytics

For each sub-ledger transaction fact, there is a logical “transaction detail” dimension comprising the transaction fact and the W_GL_LINKAGE_INFORMATION_G table, ‘GL Journal ID’ is available in this dimension. The figure below shows the physical source “Fact_W_AP_XACT_F_GLJournalID” of the logical dimension “Dim – AP Transaction Details”.

Finantial Analytics

With this configuration, it is possible to have ‘GL Journal ID’ available in the sub-ledger transaction subject areas. And since ‘GL Journal ID’ is also available in “Financial - GL Detail Transactions” subject area, it is possible to configure the navigation from general ledger to sub-ledger transactions by using the OBIEE Navigation functionality.

Creating GL to sub-ledger navigation

‘GL Journal ID’ is available in the two subject areas that we want to link, but the level of detail between the source and the target reports are not the same, which is why it is necessary to create an intermediate analysis to link the two reports. This process is described in this section.

  1. Create the GL report and enable navigation.

Using the subject area “Financial - GL Detail Transactions”, create a new analysis that shows the Balance by Accounting Document Source of a selected account for a selected period. Accounting Document Source is used to identify the sub-ledger source.

Finantial Analytics

  1. Create an Intermediate analysis that contains the ‘GL Journal ID’.

Using the subject area “Financial - GL Detail Transactions”, create an analysis that contains the ‘GL Journal ID’ column. Account and period filters need to be created as well. This report will be used to filter the sub-ledger Transactions.

Finantial Analytics

  1. Create a detailed sub-ledger report

Using one of the sub-ledger subject areas, create a new analysis and filter the results by existing report. Use the analysis created in step 2 as the “Saved Analysis”.

 

image 6 Finantial Analytics

  1. Enable navigation

In the source report (created in step 1), using Action Links, enable navigation to the different sub-ledger reports. The example below shows the navigation to AP Transaction report when Account Document source is Payables.

Finantial Analytics

In conclusion, the OBI Apps data model is prepared to enable the link between General ledger and sub-ledger reports, but it has to be configured at analysis level. This feature can be used for the reconciliation process between General ledger and sub-ledgers.

If you want to go further, create a report that shows summaries sourced by general ledger, a second column that shows sub-ledger totals and a third column that shows the difference between these two amounts, if the value of this column is not ‘0’ navigate to details to discover the discrepancies.

 

OBIEE 10g High Availability

.

Sometimes there is a need to have OBIEE system operating during planned or unplanned downtime. Because of this reason OBIEE provides a high availability capability: it is possible to configure all OBIEE components in order to offer high availability. In this entry I am going to describe the process of configuring a high available OBIEE 10g system.

Before going to the configuration I want to clarify some concepts:

Availability is the degree to which an application, service, or function is accessible on demand. Based on this concept we can conclude that High Availability is a system continuously operational as long as possible.

High availability systems can be categorized into local high availability systems which are configured in a single data center and disaster recovery solutions systems which are usually geographically distributed. OBI Server only can be configured in the first case; all components of the BI Cluster Server feature must reside on the same LAN.

High availability comes from redundant systems and components. Local high availability solutions can be categorized, by their level of redundancy, into active-active where two or more active instances are working at the same time and active-passive where only one instance is active and others are in standby.

OBIEE provides for high availability through the use of native clustering, failover and load balancing capabilities. Let’s analyze the OBIEE 10g components and how they are communicated in a clustered environment.

Continue reading this post >

How to create a custom Siebel Report using BI Publisher

.

If you’ve ever worked with Siebel Business Applications, you should know that these come with predefined reports composed of integration objects and layouts.

These applications can be customized by modifying an integration object and/or RTF template when necessary. Sometimes the predefined report doesn’t match with user requirements (based on our experience this happens most of the time), however, there exists the possibility of creating a Siebel Report from scratch.

This article is a road map on how to create a custom Siebel Report in a connected mode.

Continue reading this post >

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav