|Oracle Public Sector Financials (International) User Guide|
Part Number E13418-03
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:
|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)|
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.
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
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.
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.
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.
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.
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.
The business rules for drill-down mapping are as follows:
Only one drill-down structure can exist at a given time.
The mapping view must be complete before running a query.
The business rules for drill-down inquiry are as follows:
The user must be able to choose options as shown in the table below, at the outset of an inquiry.
|Ledger||User must select the ledger to enquire about the budgets related to the ledger.|
|Period||User must select any period: Future, Open, or Closed|
|Budget||User must choose the budget to query.|
|Commitment||User must choose the commitments that are included in the queried figures. Including commitments means that the queried actuals include outstanding commitments to account for unpaid purchases.|
All journals must be posted to be visible in the Hierarchical Drill-Down Inquiry system.
Drill-down is provided for up to five levels only.
On drilling down to the lowest segment defined for the drill-down, the relevant journal lines are displayed for actual balance types only.
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.
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.
|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.
|Level||Segment||View Balances for Combination|
|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|
Copyright © 1996, 2010, Oracle and/or its affiliates. All rights reserved.