Hierarchical Drill-Down Inquiry Process

Overview

Hierarchical Drill-Down Inquiry is an extension to Oracle Financials that enables the user to query budgets, actuals, commitments, and projections in a top-down approach aggregated at a high organizational level and gradually drilling down to view figures for specific areas and actual journal lines.

Hierarchical Drill-Down Inquiry requires a drill-down mapping structure for the selected ledger. This structure determines the accounting segments and grouping order for summarizing ledger balances.

Hierarchical Drill-Down Inquiry consists of a set of pre-determined mathematical formulae that operate in a top-down, drill-down method on posted General Ledger budget, encumbrance, and actual journals.

These drill-down mapping formulae are organized into the following sections:

The formula are shown in the following table:

Drill-Down Mapping Formulae
Calculation Derivation
Period-to-Date Actuals (Period-to-Date Actual Debits - Period-to-Date Actual Credits)
Year-to-Date Actuals (Year-to-Date Actual Debits - Year-to-Date Actual Credits)
Period-to-Date Budget (Period-to-Date Budget Debits - Period-to-Date Budget Credits)
Year-to-Date Budget (Year-to-Date Budget Debits - Year-to-Date Budget Credits)
Full Year Budget (Full Year Budget Debits - Full Year Budget Credits)
Full Year Estimate (Year-to-Date Actuals / Year-to-Date Budget) * Full Year Budget
Period-to-Date Budget Variance (Period-to-Date Budget - Period-to-Date Actuals)
Year-to-Date Budget Variance (Year-to-Date Budget - Year-to-Date Actuals)
Projection (Full Year Budget - Year-to-Date Budget + Year-to-Date Actuals)
Projected Variance (Projection - Full Year Budget)
Percentage Variance (Projection Variance / Full Year Budget) * 100
Overspend Indicator (Set to'*' where Projection Variance > 0)

Prerequisites

Hierarchical Drill-Down Inquiry functionality does not impact any core Oracle Financials modules. Hierarchical Drill-Down Inquiry is a standalone requirement and an addition to the General Ledger module.

Hierarchical Drill-Down Inquiry Process Flow Diagram

The diagram below shows the process flow for implementing Hierarchical Drill-Down Inquiry, as described in the accompanying text.

Hierarchical Drill-Down Inquiry Process Flow Diagram

the picture is described in the document text

Hierarchical Drill-Down Inquiry Process

Hierarchical Drill-Down Inquiry enhances standard General Ledger functionality.

Hierarchical Drill-Down Inquiry does not require altering any default or standard Oracle Financials processing.

Hierarchical Drill-Down Inquiry interrogates existing posted General Ledger journals that must be entered as standard using General Ledger functionality.

Determine Drill-Down Structure

The overall number of segments must be defined within the organization's chart of accounts for the ledger in question, before interrogating General Ledger. This requires an analysis of the organization's requirements and is not within the scope of this document.

Set Up Drill-Down Structure

The physical implementation and setup of the predetermined drill-down policy must be defined.

The required drill-down level structure must be mapped to the chart of accounts segment.

For information on setting up the drill-down structure, see Setting Up Drill-Down Levels, Oracle Public Sector Financials (International) Implementation Guide.

Interrogate Drill-Down Structure

This concerns the interrogation of General Ledger and use of the fixed formulae within the drill-down structure.

The Drill-Down Inquiry window queries General Ledger according to the search criteria entered and displays the calculated figures based on the fixed formula for each drill-down line.

For information on interrogating the drill-down structure, see Hierarchical Drill-Down Inquiry Procedure.

Maintain Drill-Down Structure

The drill-down structure can be changed at any time, but only one structure can exist at a given time.

For information on maintaining the drill-down structure, see Setting Up Drill-Down Levels, Oracle Public Sector Financials (International) Implementation Guide.

Hierarchical Drill-Down Inquiry Business Rules

Drill-Down Mapping Business Rules

The business rules for drill-down mapping are as follows:

Drill-Down Inquiry Business Rules

The business rules for drill-down inquiry are as follows:

Hierarchical Drill-Down Inquiry Example

Local government authorities require a method of tracking income and expenditure against budgets for business reasons. Government moves towards a decentralized approach and also the open book accounting concept made this an essential requirement within the local government sector.

A top-down approach is required to ascertain the impact and expenditure on the financial state of the company.

Although the Hierarchical Drill-Down Inquiry feature is primarily intended for the public sector, it is a requirement that may be applicable to many organizations that use budgetary control.

Set Up Drill-Down

The first stage of setting up drill-down is defining the segments of the customer's chart of accounts that relate to each level of the drill-down structure using the Setup Drill-Down Levels window.

The table below shows an example of mapping each drill-down level to a chart of accounts segment.

Mapping Drill-Down Levels to Chart of Accounts Segments
Drill-Down Level Chart of Accounts Segment
Level 1 Segment A
Level 2 Segment B
Level 3 Segment C
Level 4 Segment D
Level 5 Segment E

The drill-down structure shown in the Mapping Drill-Down Levels to Chart of Accounts Segments table is interrogated in the drill-down inquiry window as shown in the table below.

Hierarchy Viewing Structure Example
Level Segment View Balances for Combination
1 A Not applicable
2 B Not applicable
3 C A, B, and C values
4 D A, B, C, and D values
5 E A, B, C, D, and E values
Below level 5   display journal lines making up level 5 balances