Oracle® Financial Consolidation Hub User's Guide Release 11i Part No.
Oracle Financial Consolidation Hub User's Guide, Release 11i Part No. B16229-05 Copyright © 2005, 2007, Oracle. All rights reserved.
Contents Send Us Your Comments Preface 1 About Oracle Financial Consolidation Hub Oracle Financial Consolidation Hub Overview....................................................................... 1-1 2 The Oracle Financial Consolidation Hub Solution Quick Reference........................................................................................................................ 2-1 3 Setting Up Oracle Financial Consolidation Hub Oracle Financial Consolidation Hub Setup Overview..................
7 Setting Up Categories Overview................................................................................................................................... 7-1 Category Definitions................................................................................................................. 7-2 Selected User Interface Elements for Setting Up Categories................................................... 7-4 8 Setting Up Consolidation Methods Overview..............................................
Setting Up Consolidation Hierarchies.................................................................................... 13-2 Consolidation Hierarchy Structures....................................................................................... 13-6 Updating Consolidation Hierarchies...................................................................................... 13-7 Duplicate Hierarchies for Restatements and What-if Analysis............................................. 13-9 Multiple Parents ......
Intercompany Matching Report.............................................................................................. 18-3 Data Submission Trial Balance Report................................................................................... 18-4 Data Submission Data Loaded Report....................................................................................18-4 Data Submission Impacted Balances Report..........................................................................
WebADI Selected User Interface Elements.............................................................................. A-3 B Concurrent Programs Concurrent Programs................................................................................................................ B-1 C Business Events Business Events ........................................................................................................................ C-1 D Profile Options Profile Options..................................
Send Us Your Comments Oracle Financial Consolidation Hub User's Guide, Release 11i Part No. B16229-05 Oracle welcomes customers' comments and suggestions on the quality and usefulness of this document. Your feedback is important, and helps us to best meet your needs as a user of our products.
Preface Intended Audience Welcome to Release 11i of the Oracle Financial Consolidation Hub User's Guide. See Related Information Sources on page xii for more Oracle Applications product information. TTY Access to Oracle Support Services Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.
Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
that you have installed to learn about new documentation or documentation patches that you can download. About Documents are available on OracleMetaLink. User Guides Related to this Product • Oracle General Ledger User Guide: This guide contains the information you need to implement and set up Oracle General Ledger.
products). This guide also includes information on setting user profiles, as well as running and reviewing reports and concurrent processes. You can access this user's guide online by choosing Getting Started with Oracle Applications from any Oracle Applications help file. Installation and System Administration xiv • Oracle Applications Concepts: This guide provides an introduction to the concepts, features, technology stack, architecture, and terminology for Oracle Applications Release 11i.
Standards for Forms–Based Products. It also provides information to help you build your custom Oracle Forms Developer 6i forms so that they integrate with Oracle Applications. Other Implementation Documentation • Oracle Workflow Administrator's Guide: This guide explains how to complete the setup steps necessary for any Oracle Applications product that includes workflow– enabled processes, as well as how to monitor the progress of runtime workflow processes.
row in one table without making corresponding changes in related tables. If your tables get out of synchronization with each other, you risk retrieving erroneous information and you risk unpredictable results throughout Oracle Applications. When you use Oracle Applications to modify your data, Oracle Applications automatically checks that your changes are valid. Oracle Applications also keeps track of who changes information.
1 About Oracle Financial Consolidation Hub Oracle Financial Consolidation Hub Overview Oracle Financial Consolidation Hub brings together financial data from disparate sources to create a single, global view of financial information across the entire enterprise.
changing business requirements, offering an open architecture that rationalizes data from disparate systems into a single, actionable source of truth. Provides a Basis for Enterprise-Wide Performance Management Today's financial organizations are being called upon to achieve both strategic and operational excellence. After all, the best plans in the world are futile without effective execution; likewise, effective execution is wasted if it's based on flawed strategy.
2 The Oracle Financial Consolidation Hub Solution Quick Reference This chapter provides a quick reference to the common issues encountered in the implementation of a consolidation system. It is a starting point where you can review the issues, gain a high level understanding of how Financial Consolidation Hub handles the issues, and find a reference to the section in this user's guide where you can obtain further information.
Issue Functionality Reference Standardize data. Includes complete transformation, validation, and chart of accounts standardization capabilities. • Submitting Data, page 14-1 Submitting Data for External Entities, page 14-11 Transformation Rule Sets, page 14-10 Validation Rule Sets, page 14-10 • Setting up Enterprise Performance Foundation, page 5-1 Task 4: Value Sets, page 5-8 Task 8: Value Set Maps, page 5-12 Enforce security. Ability to secure who is authorized to load data.
Issue Functionality Reference Incremental consolidation processing. Incremental consolidation processes areas impacted by changes. • Submitting Consolidations, page 15-4 Monitor consolidation in real time. Icons display up to the minute status for all subsidiary and consolidation points. • Process Status, page 15-2 Compare and audit results. Store details for every consolidation run and you can audit this using drill-down reports. • Process History, page 15-4 Allow adjustments.
Reporting and Analysis Issue Functionality Reference Distribute consolidation results. Includes automatic distribution of online reports containing consolidation results. • Defining a Notify Task, Oracle Enterprise Planning and Budgeting User's Guide Allow ad-hoc analysis and drill-down. Integrated multi-dimensional reporting provides online drill-down, select, rotate, and graph capabilities.
3 Setting Up Oracle Financial Consolidation Hub Oracle Financial Consolidation Hub Setup Overview The following table shows the Oracle Financial Consolidation Hub setup checklist. Complete all required steps in the sequence indicated. Financial Consolidation Hub Setup Checklist Step Number Setup Step Step Type Description Set Up Roles, page 21-1 Required Used for notifications and security 2.
3-2 Step Number Setup Step Step Type Description 4. Set Up Categories, page 7-1 Optional Used to designate business processes within consolidation 5. Set Up Consolidation Methods, page 8-1 Optional Used to group consolidation rules together 6. Set Up Consolidation Rules, page 9-1 Optional Used to automate consolidation entries 7. Set Up Intercompany Rules, page 10-1 Optional Used for intercompany and intracompany eliminations 8.
4 Setting Up Users Overview To implement Oracle Financial Consolidation Hub, define users and assign them responsibilities. Each responsibility offers access to a different functionality in different products. This chapter lists the common responsibilities required to implement Financial Consolidation Hub. For details on the security surrounding users and responsibilities, see Security, page 211.
Responsibility Product Functionality Enterprise Planning and Budgeting Controller Enterprise Planning and Budgeting Pull consolidation data into Enterprise Planning and Budgeting and create analytical reports Enterprise Planning and Budgeting Security Administrator Enterprise Planning and Budgeting Set security settings to give your user access to Enterprise Planning and Budgeting components System Administrator Manage system configuration such as profile options, users and responsibilities XML P
5 Setting Up Oracle Enterprise Performance Foundation Overview Oracle's Corporate Performance Management applications, including Financial Consolidation Hub, share a common data model called Oracle Enterprise Performance Foundation. Enterprise Performance Foundation provides a storage area for managing reference data that contains dimensions, dimension members, hierarchies, and business data. Financial Consolidation Hub, Enterprise Planning and Budgeting, and Profitability Manager use this reference data.
Required Dimensions for Financial Consolidation Hub 1. Entity - Used to represent the legal entities or business units that you want to consolidate. See: Setting Up Entities, page 6-1 2. Company Cost Center Organization - The main organizational dimension by which you record accounting data. Company cost center organizations are subsets of entities. 3. Intercompany - Used to identify and eliminate intercompany activity. It shares the same values as the company cost center organization dimension.
local GAAP and one for corporate GAAP. This dimension can be used in conjunction with line item for these purposes. 5. Product - Used to identify the product associated with a financial balance. 6. Project - Used to identify the project associated with a financial balance. 7. User Dimension 1 to 10 - These dimensions can be enabled for any purposes not addressed by the above mentioned dimensions. Dimensions Used Internally by Financial Consolidation Hub 1.
Performance Foundation user interface; however, the Foundation tasks hide much of the complexity that is not relevant for using Financial Consolidation Hub. 2. Provides some initial configuration for Financial Consolidation Hub. Note: Some tasks may not be necessary depending on your specific implementation of Financial Consolidation Hub. In such cases, update the status of the task to Complete.
Column Select the dimensions that you want to set as active in Enterprise Performance Foundation. These dimensions will be the superset of the dimensions used by Financial Consolidation Hub and any other Oracle products that you use on top of Enterprise Performance Foundation. For Financial Consolidation Hub purposes, this will be the set of dimensions that are used for loading source data. See: Required Dimensions for Financial Consolidation Hub, page 5-2.
Used By Historical Rates You can define the dimensions that should be used in the historical rates definition. The dimensions you can choose are a subset of those you selected for Used by Processing. The dimensionality for Historical rates is specified in the Used by Historical Rates column. This column cannot be updated if Historical Rates already exist in the system.
Data Sets Attributes Dataset Balance Type Code Actual Dataset Update Flag Yes Dataset Production Flag Yes Dataset System Code Note: You can not create additional Actual balance types. You may only use the seeded ones. The following table shows attributes required for Encumbrance data sets.
Data Sets Attributes Dataset Update Flag Yes Dataset Production Flag Yes Dataset System Code Note: You can only create one data type per Dataset Budget Type ID. Note: You cannot create additional data types for Actual balances. Additionally, set the following three parameters for data types: • Enforce Balancing - When you perform consolidation on this data type, the application uses suspense handling to enforce balancing.
use different sets of values. If all financial data brought in through external systems is based on a single chart of accounts, or is transferred from Oracle General Ledger, you can omit this step. To transfer data from Oracle General Ledger to Enterprise Performance Foundation, see Using Oracle General Ledger with Enterprise Performance Foundation, page 5-17.
• Add dimension members to dimensions • Search for, view, and update any existing members A dimension is a classification scheme used to categorize or group business data. For example, data can be grouped by the line item, product, geographical area, customer, natural account, and time. A dimension contains dimension members. For example, the Line Item dimension might have dimension members of Total Assets and Fixed Assets.
list of attributes for a dimension member, use the Enterprise Performance Foundation Administrator responsibility, navigate to Configuration Rules > Dimension > Members. Here, you can use the Enterprise Performance Foundation user interface to view, update, and create dimension members. You can also use the WebADI spreadsheet loader.
See: WebADI spreadsheets, page A-1 Note: In the spreadsheet loader, you must enter top parents in a row with its value entered for the Parent and Child columns. Note: Creating consolidation hierarchies for the Entity dimension is done through a separate Consolidation Hierarchy user interface. See: Setting Up Consolidation Hierarchies, page 13-2 Task 8: Value Set Maps Task 8 is only necessary if you have multiple chart of accounts and need to create value set maps.
During the Data Preparation step of the consolidation process, Financial Consolidation Hub maps the source values into the specified parent values. You can version value set mappings, for example to take reorganizations into account. When you run consolidation, Financial Consolidation Hub applies the appropriate version based on the effective date for which you are consolidating. When you create a value set map, it creates a multi-value set dimension hierarchy in Enterprise Performance Foundation.
Line Item Dimension Intercompany I/C Accounts Payable - Canada Canada I/C Accounts Payable - Mexico Mexico And the company has the following balances. Company Line Item Intercompany Balance Canada I/C Accounts Payable None 100 Canada I/C Accounts Payable - Canada None 200 Canada I/C Accounts Payable - Mexico None 300 Canada Cash None -600 When you run consolidation, the process will identify any matches for the line item intercompany maps.
Task 10: Data Transformation and Validation Task 10 is only necessary if you perform transformation or validation on your data submissions and need to register additional tables and functions for the transformation and validation rules. For Task 10, you can register any tables or functions you want to use in Transformation Rule Sets and Validation Rule Sets. You can use these rules sets to perform automated transformation and validation of balances during data submission.
• Data Submission Trial Balance Template - To view the current data in Enterprise Performance Foundation for an entity. This may differ from the Data Submission Load Data Template because it represents the cumulative effect of all data submissions for a given entity in a given period. • Hierarchy Listing Template - To view a consolidation hierarchy. • Intercompany Matching Template - To view intercompany balances eliminated during a consolidation run.
When you click Complete, Financial Consolidation Hub launches the FCH: Module Initialization concurrent program. Monitor the concurrent program and verify that it completes successfully. Additional Setup Tasks Once you complete Tasks 1-12, perform the following additional setup tasks. 1. Set default values in Enterprise Performance Foundation. Using the Enterprise Performance Foundation Administrator responsibility, navigate to Configuration Rules > Dimension > Definition.
Foundation User's Guide. Financial Consolidation Hub recommends the following setup when leveraging the Oracle General Ledger integration. Configuring FEM_BALANCES for Oracle General Ledger Integration To utilize integration with Oracle General Ledger, you must first configure and register the FEM_BALANCES table in Enterprise Performance Foundation to be used for integration. 1. In the Enterprise Performance Foundation responsibility, navigate to Administration>Registration>Tables and Columns.
dimension members are derived from segment values. Setting Up Dimension Rules Map each chart of accounts that you selected to be transferred to Enterprise Performance Foundation to Enterprise Performance Foundation dimensions. You can then synchronize segment values and hierarchies into Enterprise Performance Foundation as dimension members and hierarchies. For each dimension within Enterprise Performance Foundation, define and execute a mapping option.
Selected General Ledger Dimensions with Mapping Option Requirements Dimension Description Financial Consolidation Hub Requirements and Recommendations Company Cost Center Organization The Organization dimension enables you to copy values from a single segment, either Company or Cost Center, or the combination. You must map at least one segment to the Organization dimension. Although you can combine the company and cost center segments with the Organization dimension.
Dimension Description Financial Consolidation Hub Requirements and Recommendations Intercompany The Intercompany mapping depends upon the Organization mapping. If your chart of accounts has an intercompany segment, assign the intercompany segment to the Intercompany dimension. If you do not have a qualified intercompany segment, then assign a specific value. Note: The intercompany dimension can be populated within Financial Consolidation Hub using the Line Item Intercompany Maps functionality.
Dimension Description Financial Consolidation Hub Requirements and Recommendations Entity You can assign the Entity dimension to a single value or a single segment copy of the balancing segment value. Assign the Entity dimension to a single value if you want the chart of accounts to be the consolidation global value set combination in Financial Consolidation Hub. Note: If you are on FEM.D.1, this dimension will not be available for mapping.
to construct the dimension hierarchy. Financial Consolidation Hub enables you to define new hierarchy rules and new dimension hierarchy rule versions. Definition of a new rule creates a new hierarchy definition within Enterprise Performance Foundation. Creating a new version of the rule creates a new hierarchy version within Enterprise Performance Foundation. When you define the hierarchy rule, specify the top segment value.
6 Setting Up Entities Overview Entities are designed to provide a flexible framework to separate and organize operational data, consolidation processing, and consolidation results. Entities are the building blocks used to construct consolidation hierarchies.
consolidation entity includes a unique elimination entity and optionally a unique controlling operating entity. Elimination Entity This entity holds entries and adjustments produced during the consolidation process. A unique elimination entity is automatically created for each consolidation entity. Note: To create an elimination entity, create a consolidation entity and specify the elimination entity's name during the creation process.
Sample Consolidation Hierarchy Entity in Enterprise Performance Foundation When you create an entity in Financial Consolidation Hub, corresponding Enterprise Performance Foundation structures are automatically created. An entity in Financial Consolidation Hub corresponds to the same-named dimension member in the default value set for the Entity dimension.
Selected User Interface Elements for Setting Up Entities The following sections describe Selected user interface elements. Contact The selected contact is the recipient of Workflow notifications related to the entity. This leverages the role based model available in the Oracle User Management. See: Oracle Applications System Administrator's Guide - Security See: Impact Analysis, page 15-2 Secure by Role If you select to secure by role, only the roles that you specify can access and update the entity.
source system. Note: If the source system for actuals is not Oracle General Ledger, the source systems for any additional data types cannot be Oracle General Ledger. Ledger To create an operating entity with an Oracle General Ledger source system, you must select a ledger in Oracle General Ledger from which to pull balances.
If you specify additional data types, they will all use the same functional currency as the one you specify for actuals. Note: You can load data in a currency that is different from the functional currency, in case you are running translation on an external system. Transformation Rule Set To create an operating entity with a third party General Ledger source system, you can select a transformation rule set. A transformation rule set transforms the data when you submit it.
Each company can own multiple organizations that are used in account balances for the entity. Note: If you create an organization for an entity after the entity is created, you must synchronize the organization assignments. To synchronize, click Update for the entity. Base Organization To create an operating entity, select a base organization for the entity. The base organization must be one of the organizations of the selected companies.
entities. Note: You can create an operating entity and assign as a controlling entity to a consolidation entity at the same time within the spreadsheet. Note: The spreadsheet loader does not support adding additional data types to entities. To add additional data types, update the entity through the application. If there are errors in the spreadsheet, the spreadsheet is rejected. All errors must be corrected before you run spreadsheet loader.
7 Setting Up Categories Overview Categories control how a consolidation is processed and reported. They provide a means of grouping process logic and specifying parameters impacting the output of such logic. Customers can leverage categories to track and analyze their consolidated results. The following table shows seeded categories in a default sequence. Seeded Categories Default Sequence Category 1. Data Preparation 2. Intracompany 3. Translation 4. Intercompany 5. Acquisition and Disposal 6.
User-Defined Categories You can define categories as needed. The following situations illustrate scenarios where you may choose to define categories: • You need to adjust your investment in subsidiary accounts through the equity method of accounting. In this case, for example, you can create a category called Equity Pickup after the Translation category. If you need to reverse those adjustments, you can create an additional category called Equity Pickup Elimination and insert it after Minority Interest.
Impact of the Selected Option on Intracompany Balance Identification Option Selected Organizatio n Line Item Intercompany Organization Intracompany Transaction Only intercompany when organization and Intercompany are different. 01 2300 01 No Differ from Specific Value "00" 01 2300 01 Yes Translation Translation occurs after all categories that affect the child's balances are executed. The Translation category picks up all data from a child entity and translates it to the parent's currency.
Aggregation The Aggregation category creates consolidated financial results for each consolidation entity. It writes to the consolidation entity the summation of data for all children across all categories. The Aggregation category always occurs last. Selected User Interface Elements for Setting Up Categories The following sections describes selected user interface elements for the Set Up Category functionality.
Net to Retained Earnings at Year-End The Net to Retained Earnings at Year-End feature determines whether year-end processing should be applied to manual adjustments and consolidation rule entries for this category. The default setting is No. If you select Yes for the Net to Retained Earnings in the Year-End drop-down list, any income statement adjustments or consolidation rule entries for the applicable category are closed to retained earnings when crossing the fiscal year.
8 Setting Up Consolidation Methods Overview A consolidation method is a mechanism that defines the accounting method applied during consolidation. Consolidation rules are assigned to each consolidation method. A consolidation method is assigned to each parent child/relationship within the Consolidation Hierarchy user interface.
ownership percentage. None Consolidation - A consolidation method where an entity's balances can be used as input into a consolidation rule, but the entity's balances are not brought into the consolidated statements. Enable Consolidation Method If you disable a consolidation method, you cannot use it to create new relationships in a consolidation hierarchy but prior existing relationships are not affected.
9 Setting Up Consolidation Rules Overview A consolidation rule is a user-defined mechanism that enables you to automate the creation of consolidation entries during consolidation processing. Typical examples of entries generated by consolidation rules include the following: • Minority interest • Equity accounting You can also use consolidation rules to calculate acquisition and disposal entries. This chapter will focus on rules in the Minority Interest and User-Defined categories.
Enabled If you disable a consolidation rule, the application does not apply the consolidation rule during consolidation processing. Consolidation Method A consolidation rule is assigned to consolidation methods. See: Overview, page 8-1 See: Selected User Interface Elements for Setting Up Consolidation Methods, page 8-1 From Method/To Method If the consolidation rule is in the Acquisition and Disposal category, specify the original and new consolidation methods.
Seeded Tokens Used with the Minority Interest and User-Defined Categories Token Description ELIMTB Elimination Entity Balances CHILDTB Child Entity Balances %OWN Ownership Percentage at Consolidation %MI Minority Interest Ownership Percentage Formula Scope The formula scope identifies the source and target accounts for the formula. Source You can restrict source values by dimension. You can specify valid values from a list of all possible values or from a hierarchy.
Additional Data Types By default, you can run consolidations on actuals and averages. In addition, you can also run consolidations on any additional data type defined. During consolidation, consolidation rules may or may not be executed depending on the definition of the data type. Related Topics Task 4: Data Types, page 5-6 Examples of How Consolidation Rules Work The following examples illustrates how consolidation rules are applied during a consolidation process.
Sample Consolidation Hierarchy Minority Interest You want to calculate the minority interest for the relationship between APAC and Subsidiaries and Japan. Since the relationship is a Full consolidation type, 100% of the balances in Japan are aggregated to APAC and Subsidiaries. However, since APAC and Subsidiaries only owns 80% of Japan, you want a resulting entry that removes 20% of the profit of Japan.
Minority Interest Consolidation Rule Note: The User Interface does not show all of the Source values. The Source values chosen for Line Item are 4000 - Revenue and 5000 Expense. The minority interest consolidation rule finds any balances in the source and elimination entity whose Line Item is 4000-Revenue or 5000-Expense and creates a consolidation entry that is equal to Minority Interest Ownership multiplied by those balances.
Company Cost Center Organization Line Item Balance 11.200 4000-Revenue -500 11.100 5000-Expense 300 During consolidation, the consolidation rule produces the entry in the following table: Entry Produced by Consolidation Rule Company Cost Center Organization Line Item Balance 11.100 3600- Minority Interest B/S -40 11.100 5450- Minority Interest P/L 40 The consolidation rule identifies the balances with line items 4000-Revenue and 5000-Expense as source balances.
Eliminate Investment in Subsidiary You want to eliminate APAC and Subsidiaries' Investment in Subsidiary for Japan. Since the relationship is a Full consolidation type, 100% of the balances in Japan are aggregated to APAC and Subsidiaries. However, since APAC and Subsidiaries already records its Investment in Subsidiary for Japan, you want to reduce that investment against Japan's equity in order to avoid double counting.
Step 2: Eliminate Subsidiary Equity In step 2, the Japan's equity is eliminated. Since Japan is not fully owned, the portion of equity not owned will be calculated in step 3. The rule step finds any balances in the source entity whose Line Item is 3000 - Equity and creates a consolidation entry that is equal to those balances. Since a target line item is not specified, the consolidation entry is written to the same line item as the source, 3000 - Equity.
Step 3: Add Back Minority Equity In step 2, all of Japan's equity was eliminated. Since Japan is not fully owned by APAC and Subsidiaries, step 3 reduces that amount by the portion of equity not owned. The rule step finds any balances in the source entity whose Line Item is 3000 - Equity and creates a consolidation entry that is equal to the Minority Interest Ownership multiplied by those balances. The consolidation entry is written to the line item 3650 Minority Interest (B/S) in the elimination entity.
Company Cost Center Organization Line Item Balance 12.100 5000-Expense 400 Japan has the balances in the following table. Assume these balances have already been translated into the parent currency, USD. Japan's Balances Company Cost Center Organization Line Item Balance 11.100 1100-Cash 500 11.100 2100-Debt -200 11.200 3000-Equity -500 11.200 4000-Revenue -100 11.100 5000-Expense 300 During consolidation, the consolidation rule will produce a consolidation entry in each step.
Company Cost Center Organization Line Item Balance 11.100 3000 - Equity 500 The step identifies the balance in Japan with line item 3000 - Equity as the source balance. It produces a consolidation entry with Line Item 3000 - Equity, whose value is: -1 * (-500) = 500. The company cost center organization of the entry is set to the base organization of the source entity, 11.100. Step 3: Add Back Minority Equity Company Cost Center Organization Line Item Balance 11.
10 Setting Up Intercompany Rules Overview An intercompany rule is a mechanism that identifies intercompany activity to be matched and eliminated. Intercompany rules are applied during both intercompany and intracompany processing. During intracompany processing, rules eliminate activity within an operating entity. During intercompany processing, the rules eliminate activity between two entities.
whether the balances offset completely and therefore require a suspense entry for balancing. You can do matching by Company pair or by Organization pair, as specified in the Match Intercompany By drop-down list of the hierarchy. Intercompany Elimination Entry Intercompany elimination entries eliminate balances between entities to ensure that they are not included in consolidated results. When matching balances are found, the appropriate entries are created to eliminate the intercompany balances.
If the out of balance amount exceeds the threshold amount specified in the intercompany elimination rule, a warning is generated, but the consolidation is allowed to continue. You can inspect the entries in the Consolidation Process Status page and make appropriate adjustments if necessary. Additional Data Types By default, you can run consolidations on actuals and averages. In addition, you can also run consolidations on any additional data type defined.
Sample Consolidation Hierarchy Eliminate Receivables Against Payables You want to eliminate intercompany transactions between accounts payable and accounts receivable. This example focuses on transactions between Services and UK. Assume the conversion rate from GBP to USD is 2. Note: Intercompany eliminations always occur in the currency of the common parent. The following intercompany rule eliminates intercompany transactions between Accounts Receivable and Accounts Payable.
The intercompany rule looks for balances that contain the line items 1200 – Accounts Receivable, 2200 – Accounts Payable or 2210 – Accounts Payable Discounts, and whose intercompany value differs from 00. Services is assigned to the companies 3 and 4, which are assigned to the organizations 3.100, 3.200, 4.100 and 4.200. UK is assigned to company 9, which is assigned to the organizations 9.100 and 9.200. Services has the following balances in USD.
Balance Organization Line Item Intercompany S5 4.100 2200 – Accounts Payable 9.100 Debits Credits 450 UK has the following balances in GBP. Balance Organization Line Item Intercompany Debits U1 9.100 2200 – Accounts Payable 00 U2 9.100 1200 – Accounts Receivable 3.100 900 U3 9.100 1200 – Accounts Receivable 4.100 200 Credits 1100 Services and UK have the intercompany eliminations performed at the level of the common parent, Global and Subsidiaries.
are 3 – 9 and 4 – 9. Each grouping is balanced using the suspense account. Here are the intercompany entries produced. Intercompany entry: Companies 3 and 9 Organization Line Item Intercompany Debits 3.100 2200 – Accounts Payable 9.100 300 3.200 2210 – Accounts Payable Discounts 9.100 1500 9.100 1200 – Accounts Receivable 3.100 Credits 1800 The entry is balanced, so no balance is written to the suspense account.
Intercompany entry: Organizations 3.100 and 9.100 Organizatio n Line Item Intercompany Debits 3.100 2200 – Accounts Payable 9.100 300 9.100 1200 – Accounts Receivable 3.100 9.100 3201 – AR/AP Suspense 3.100 Credits 1800 1500 The entry is balanced by writing 1500 USD to the designated suspense account in the organization of the receivables entity. Since the suspense balance is above the threshold amount of 100 USD, the entry will produce a warning during consolidation.
Organization Line Item Intercompany Debits Credits 9.100 1200 – Accounts Receivable 4.100 400 9.100 3201 – AR/AP Suspense 4.100 50 The entry is balanced by writing -50 USD to the designated suspense account in the organization of the receivables entity. Since the suspense balance is below the threshold amount of 100 USD, the entry will not produce a warning during consolidation.
11 Setting Up Calendar Maps Overview A calendar map is a user-defined mechanism that defines the relationship between the periods of two different calendars. The calendar map is used during consolidation processing when the operating entity calendar is different from the consolidation hierarchy calendar. You assign each consolidation hierarchy a consolidation calendar. If an operational entity has trial balances based on a different calendar, you define a calendar map to relate the two calendars.
Period Name Data Range May-04 May 1, 2003 to May 31, 2003 Jun-04 June 1, 2003 to June 30, 2003 Jul-04 July 1, 2003 to July 31, 2003 Aug-04 August 1, 2003 to August 31, 2003 Sep-04 September 1, 2003 to September 30, 2003 Oct-04 October 1, 2003 to October 31, 2003 Nov-04 November 1, 2003 to November 30, 2003 Dec-04 December 1, 2003 to December 31, 2003 Jan-04 January 1, 2004 to January 31, 2004 Feb-04 February 1, 2004 to February 28, 2004 Mar-04 March 1, 2004 to March 31, 2004 Adj-04
Period Name Data Range Jun-04 June 1, 2004 to June 30, 2004 Jul-04 July 1, 2004 to July 31, 2004 Aug-04 August 1, 2004 to August 31, 2004 Sep-04 September 1, 2004 to September 30, 2004 Oct-04 October 1, 2004 to October 31, 2004 Nov-04 November 1, 2004 to November 30, 2004 Dec-04 December 1, 2004 to December 31, 2004 In Financial Consolidation Hub, the calendar map would look like the following figure. The periods are mapped according to their date ranges.
12 Setting Up Translation Overview Different entities can operate in different currencies, but for consolidation purposes, it is critical to standardize to a common currency. During consolidation processing, Financial Consolidation Hub automatically translates any entity's balances from the child's currency to the parent's currency if the two currencies are different. Translation is applied according to the rules specified in a translation method.
Assigning Translation Methods You assign translation methods at the level of the relationship between two entities. When you create or update a consolidation hierarchy, specify a translation method for any child entity whose currency differs from its parent's currency. See: Creating Consolidation Hierarchy Structures, page 13-6 Note: Once a relationship is part of a consolidation process, the translation method can no longer be updated.
amount. Historical rates or amounts thus override ending and average rates. Income Statement Mode You can specify whether you want Financial Consolidation Hub to use the YTD or PTD to translate income statement account balances. A YTD specification translates ending balances using the ending rate.
Entering Ending and Average Rates Ending and average rates are exchange rates expressed in decimal form that are used to translate the balances in one currency into another. Typically, ending rates apply to balance sheet accounts. Average rates typically apply to income and expense accounts. Note: Currency Rates Manager stores daily rates. When you run a consolidation, Financial Consolidation Hub uses the rate assigned to the last day of the period being processed.
Defining Historical Rates Before you run a consolidation process that uses historical rates, define historical rates where applicable. You enter historical rates or amounts for specific combinations of dimension values. The Historical Rates spreadsheet will show only the dimensions to be used for Historical Rates. For example, you may have a line item called Land, which you use to track the investment in land for each organization and/or entity.
specified for the hierarchy. Translation Example Vision: Canada uses Canadian Dollars (CAD) as its functional currency but consolidates to Vision: North Americas Group which is based in USD. Thus, Financial Consolidation Hub translates from CAD to USD during the consolidation process. Vision: Canada is assigned a currency type of Translation, which uses an ending rate type called Ending, an average rate type called Average, PTD mode for income statements, and YTD mode for equity.
Rates Used by Translation for Vision: Canada Entity Line Item Dr (CAD) Cr (CAD) Rate Vision: Canada Cash 1000 400 0.8 Vision: Canada Accounts Receivable 1200 500 0.8 Vision: Canada Accounts Payable 500 700 0.8 Vision: Canada Paid-in Capital 0 400 0.84 Vision: Canada Retained Earnings 0 300 0.84 Vision: Canada Revenue 0 2000 0.7 Vision: Canada Cost of Goods Sold 1100 0 0.7 Vision: Canada Salaries 400 0 0.
Entity Line Item Dr (CAD) Cr (CAD) Rate Dr (USD) Cr (USD) Vision: Canada Retained Earnings 0 300 0.84 0 252 Vision: Canada Revenue 0 2000 0.7 0 1400 Vision: Canada Cost of Goods Sold 1100 0 0.7 770 0 Vision: Canada Salaries 400 0 0.7 280 0 Vision: Canada Cumulative Translation Adjustment 0 0 N/A 0 58 In February, there is some activity, so you now look at both the PTD and YTD balances. The following table shows the February trial balance for Vision: Canada.
Entity Line Item PTD Dr (CAD) PTD Cr (CAD) YTD Dr (CAD) YTD Cr (CAD) Vision: Canada Cost of Goods Sold 100 0 1200 0 Vision: Canada Salaries 50 0 450 0 The January balances plus the February PTD balances equal the February YTD balances. You can now translate the February balances at the appropriate rates for February. Note the historical rate for equities has not changed, but the ending and average rates have. The following table shows the translation rates for February.
for income statement accounts, you multiply the YTD balances by the ending rate for balance sheet accounts and the PTD balances by the average rate for income statement accounts. The following table shows intermediate translation results for February.
Final Translation Results for February Entity Line Item PTD Dr (CAD) PTD Cr (CAD) YTD Dr (CAD) YTD Cr (CAD) Rate PTD Dr (USD) PTD Cr (USD) YTD Dr (USD) YTD Cr (USD) Vision: Canada Cash 0 0 1000 400 0.81 10 4 810 324 Vision: Canada Accounts Receivable 200 0 1400 500 0.81 174 5 1134 405 Vision: Canada Accounts Payable 0 0 500 700 0.81 5 7 405 567 Vision: Canada Paid-in Capital 0 0 0 400 0.84 0 0 0 336 Vision: Canada Retained Earnings 0 0 0 300 0.
Final Translation Results for February 12-12 Entity Line Item PTD Dr (CAD) PTD Cr (CAD ) YTD Dr (CAD) YTD Cr (CAD) Rat e PTD Dr (USD PTD Cr (US D) YTD Dr (USD ) YTD Cr (USD) Vision : Canad a Cash 0 0 1000 400 0.81 10 4 810 324 Vision : Canad a Account s Receivab le 200 0 1400 500 0.81 174 5 1134 405 Vision : Canad a Account s Payable 0 0 500 700 0.81 5 7 405 567 Vision : Canad a Paid-in Capital 0 0 0 400 0.
Entity Line Item Vision : Canad a Cumulat ive Translati on Adjustm ent PTD Dr (CAD) PTD Cr (CAD ) YTD Dr (CAD) YTD Cr (CAD) Rat e PTD Dr (USD PTD Cr (US D) YTD Dr (USD ) YTD Cr (USD) N/A 0 29 29 0 Setting Up Translation 12-13
13 Setting Up Consolidation Hierarchies Overview A consolidation hierarchy is a user-defined, date-effective structure that represents the parent/child relationships of entities that are processed during consolidation. When submitting a consolidation hierarchy for processing, the hierarchy relationship structure used is that effective on the last day of the period being processed.
Enterprise Performance Foundation, but not vice versa. A ledger is also created for the consolidation hierarchy, with the name in the format ledger. When you are pulling consolidation results into Analytical Reporting, select this ledger. Four data sets are also created for the hierarchy.
• Consolidation hierarchy attributes, which apply to the entire hierarchy. • Consolidation hierarchy structure, which defines the entities and parent-child relationships between them. Note: When you create a consolidation hierarchy, Financial Consolidation Hub launches the FCH: Hierarchy Initialization concurrent program. Monitor the request and verify that it completes successfully.
entities. • Currency - The currency for the consolidation results for the hierarchy. All of the entities' currencies are converted to this currency. • Consolidation Calendar - The calendar for the consolidation results for the hierarchy. All of the entities' calendars are converted to this calendar. • Calendar Group - The level of granularity for the consolidation calendar such as month, quarter, or year.
See: Example of How an Intercompany Rule Works, page 10-3 Balancing Options In the Balancing Options region of the attributes page, select additional balancing options and specify retained earnings and suspense handling accounts. You can select a dimension, in addition to entity, to balance results. Balancing is enforced during consolidation. The following examples are a few ways that consolidation can get out of balance: • The companies assigned to an entity are modified.
used for suspense handling. During consolidation, when results are out of balance, Financial Consolidation Hub will write to the suspense account to balance the results. You also specify a threshold amount and currency for suspense handling notifications and warnings. If the suspense amount is greater than the threshold amount or currency, a warning will be raised in the consolidation process and a notification sent to the appropriate contacts.
Translation Method If an entity has a different local currency than its parent, specify a translation method. See: Translation Method, page 12-1 Uploading Hierarchy Structures When you create or duplicate a hierarchy, you can create the hierarchy structure using a spreadsheet loader. Note: The spreadsheet loader is not a WebADI spreadsheet loader. The following rules apply to the hierarchy loader: • If default translation or consolidation methods are defined, these columns are optional.
changes entered will become effective. Caution: When entering changes to a hierarchy, specify the correct effective date at the top of the page. When you update a consolidation hierarchy, Financial Consolidation Hub will show an Impact Analysis page. This page will show the following: • The change history of the relationship that was updated. • Impacts to existing consolidation runs. • The Acquisition and Disposal Transaction created to track the update.
click on Details for the topmost entity. You can also view the consolidation hierarchy in PDF and Excel form by clicking on the respective icons. See: Hierarchy Listing Report, page 18-5 Duplicate Hierarchies for Restatements and What-if Analysis The Consolidation Hierarchy page enables you to duplicate hierarchies. You can duplicate hierarchies for a variety of purposes, including restatements and creation of what-if scenarios.
• At most one relationship for the entity may be set to a consolidation method of type Full. All other relationships for the entity must be set to consolidation methods of type None. This constraint forces the entity to be consolidated up just one branch of the consolidation hierarchy. You can also have consolidation rules to handle multiple parent scenarios. Set the Reprocess for Multiple Parents to yes in the category assigned to the rule.
Mexico has the following balances in Mexican Peso. Assume the conversion rate fro Mexican Peso to US dollars is 0.09. Organization Line Item 15.100 4000 - Revenue 15.100 1100 - Cash Debits Credits 10000 10000 First, Mexico is consolidated to North Americas Group. Its balances are converted to US dollars. Organization Line Item 15.100 4000 - Revenue 15.100 1100 - Cash Debits Credits 900 900 The minority interest consolidation rule produces the following entry in US dollars.
North Americas Group does not own Mexico through any other branches, so no multiple parent calculations are performed. Next, North Americas Group is consolidated to Americas Group. At this point, Mexico is owned through additional branches: Americas Group > Mexico, Americas Group > South Americas Group > Mexico and Americas Group > South Americas Group > Colombia > Mexico. Americas Group >Mexico Americas Group owns 10% of Mexico.
Organization Line Item Debits 15.100 3650 – Minority Interest B/S 43.2 15.100 5450 – Minority Interest P/L Credits 43.2 The consolidation rule will calculate the balance as the change in minority ownership times the trial balance: (-0.048) * 900 = -43.2. This balance is added back.
14 Submitting Data Overview Data Submission enables you to submit trial balances for operating entities. Depending on the source system, the methods for submitting balances are as follows: • Oracle Source - To source from Oracle General Ledger, you can automatically submit financial data from General Ledger to Financial Consolidation Hub. • External Source - These are all source systems other than Oracle General Ledger.
see the Oracle Enterprise Performance Foundation User Guide. Related Topics Setting Up Data Submission for Oracle Entities, page 14-4 Submitting Data for Oracle Sources, page 14-6 Setting Up Data Submission for External Entities, page 14-10 Submitting Data for External Entities, page 14-11 Monitoring Data Submission A data submission online interface allows you to execute and monitor data submissions.
Status Page You can click on the status icon to see the all of the parameters of the data submission. The status page also links to the concurrent request launched by the data submission and the view data loaded report. If you are diagnosing a problem, it may be listed on the concurrent request log and/or the view data loaded report. View Data Loaded For a data submission with an External source, you can view the balances that were loaded by that particular data submission.
that entity, period, currency, and balance type. After you lock an entity's data, no one else can upload data for that entity, period, currency, and balance type combination unless you unlock it. Note: You can only lock data submissions from External Sources. Setting Up Data Submission for Oracle Sources An entity and balance type that has an Oracle source is assigned a balances rule. Balances rules manage the flow of information between Oracle General Ledger and the Enterprise Performance Foundation.
Maintain Quarterly Balances It is recommended that you do not maintain quarterly balances. You can still report on quarter-to-date balances within Financial Consolidation Hub analytical reporting. Output Dataset If you select Actual balances type, you need to select an Output Dataset. Select Default as the output dataset. Note: Other Enterprise Performance Foundation applications, for example Enterprise Planning and Budgeting, may use different output datasets.
will result in a warning. Otherwise, you can create multiple balances rules for each budget. Encumbrances If you selected Encumbrance for Balance Type, specify which encumbrances in Oracle General Ledger you wish to bring over. When you run the balances rule, datasets will be created in Enterprise Performance Foundation with the same names as the Oracle General Ledger encumbrance names.
period, and balance type. When you perform the data submission, the assigned balances rule is used to access the appropriate balances in Oracle General Ledger. Note: The FCH Data Submission user interface keeps track of the concurrent request id of the data submission. However, in the case of data submissions that source from Oracle General Ledger, different concurrent request IDs are used to track the executed balances rule for each entity.
balances for the entity, period and balance type. If you need to resubmit a data submission and your balances rule only supports snapshots, you must undo your current set of synchronized data. Additionally, if you want to modify a balances rule, you must first undo all your data submissions for that rule and make the appropriate changes. To undo data, navigate to the Enterprise Performance Administrator responsibility: Process Management > Executed Rules.
Sample Consolidation Hierarchy Assume that the Corporation ledger has the following balancing segment values: • 16: Corporate East • 17: Corporate West • 18: Distribution • 19: Reporting Adjustments You create an incremental balances rule that includes the balancing segment values 16, 17, and 18. You do not want reporting adjustments in your consolidation results, so you do not include 19. You assign Corporation companies 16 and 17, and assign Distribution company 18.
period, the balances rule runs in snapshot mode and synchronizes both Corporation and Distribution. After the above submission, new journals are posted to company 16 in February. The Data Submission page indicates an Impacted status for Corporation. Distribution's status remains unchanged. To integrate this new data into consolidation, you perform data submission for Corporation. This time the balances rule runs in incremental mode and accesses changes in companies 16 and 17.
Lookup Table Rule A lookup table rule defines a table of valid dimension member values for specified dimensions. You can select multiple columns in the lookup table to compare against and specify whether these columns match a column in the data loaded or match a fixed value. If a trial balance does not have a dimension member value that is in the lookup table, then the data submission fails. Before you can use a lookup table rule for data validation, you must create and register it .
a different Entity, Period, Balance Type, Currency (if Functional Currency Load), Execution Mode, and Amount Type. You cannot update the Currency Type and Measure options as those options affect the spreadsheet layout. Note: Ensure that the load identifier is unique prior to performing any loads. You can enter any unique value to the load identifier. After you submit a load, it will be visible in the Data Submission Monitor.
are checked. Note: Input the dimension member codes in the spreadsheet. Selected User Interface Elements for Submitting Data for External Sources Selected user interface elements for submitting data for entities with External sources are described in the following sections. Balance Type You can submit actual or average balances for the data submission. You can also submit data for any additional data types that you have defined and assigned to the entity.
but does not remove any rows from previous uploads that do not appear in the latest set. Amount Type For the Amount Type drop-down list, you specify whether you are loading period activity or ending balances. If you select ending balances, the corresponding period activity is automatically calculated, and vice versa. Note: A data submission for the first period of the year will always have the amount type of ending balances.
15 Running Consolidations Overview Financial Consolidation Hub enables you to perform statutory and management consolidations with the click of a button. The Consolidation Monitor enables you to monitor your current consolidations, review historical results, submit consolidations, and prepare your results for analytical reporting. Monitoring Consolidation Status The consolidation monitor provides you a view of the consolidation points within your hierarchy for a specific period and balance type.
• Impacted - Applies when some of the subsidiaries have Impacted data submissions status. Within the monitor, you can drill-down on the data status to see which entities have submitted data or are in the process of submitting data. Note: If data submission is undone, that is equivalent to the data submission being not started. See: View Status, page 14-2 Process Status The process status is updated automatically whenever you run a consolidation.
• Updating an acquisition or disposal elimination, which has already been processed. • Changing The Translation Rates, Which Have Already Been Used By Translation. • Changing historical rates. • Updating trial balances for operational entities. • Reconsolidating a prior period for which the current period's results have been altered. • Running consolidation for one subsidiary, and not across the entire hierarchy. • Updating the value set map.
• In the Data Preparation category, you can view which dimension members of an operating entity have not been mapped to the consolidation chart of accounts. See: Value Set Map Validation Report, page 18-6 • Useful error messages if the category results in an error. Viewing Results After the consolidation process completes, you can review the results at each level of the consolidation hierarchy by clicking the appropriate View Results icon.
you should run consolidation in Full mode to ensure the new rules are applied uniformly across the hierarchy. When you submit a consolidation, you can select an EPB business process to automatically run after consolidation is complete. You can use this parameter to automatically show the latest consolidation results into Analytical Reporting. Note: You may only select business processes that are set to run by procedural call and are made effective.
16 Adjustments Overview Adjustments are one time or recurring entries that are entered manually. Examples of adjustments include the following: Example 1 You work in corporate headquarters on the consolidation team. It is quarter end and you have obtained trial balances from all the subsidiaries and started running consolidation. You discover that slight changes need to be made to a subsidiary trial balance, yet the subsidiary controller is unavailable.
spreadsheet is uploaded, the system verifies that each dimension member is valid and that the entry is balanced. Balancing is only enforced depending on the data type options. Unlike the data submission spreadsheet loader, the dimension member combinations on each row of the adjustment spreadsheet loader do not need to be unique.
XML Tag Value p_end_cal_period_id Calendar Period name, you can leave this blank for adjustments that will go on forever p_currency_code Currency name p_process_code "All runs for period" or "Next run only" Values for Lines Region (gcs_entry_lines) XML Tag Value description Free form text field company_cost_center_org_i d Company Cost Center Organization name line_item_id Line Item name channel_id Channel name customer_id Customer name financial_elem_id Financial Element name natural_a
XML Tag Value user_dim4_id User Dimension 4 name user_dim5_id User Dimension 5 name user_dim6_id User Dimesnion 6 name user_dim7_id User Dimension 7 name user_dim8_id User Dimension 8 name user_dim9_id User Dimension 9 name user_dim10_id User Dimension 10 name intercompany_id Intercompany name ytd_debit_balance_e Debit balance ytd_credit_balance_e Credit balance Note: Some tags may not be visible based on your particular FCH configuration. 3.
Updating Adjustments You can update adjustment entries either through WebADI spreadsheets or XML files. After the update, if the original entry has been processed it will be renamed for auditing purposes. If you update adjustments in a period where consolidations have been run, the consolidation results are impacted. To incorporate the adjustment impact, you must run the Consolidation Process. The adjustments are then incorporated into consolidated balances.
Source or Parent, only operating entities are available. If the Output Entity is Elimination, only elimination entities are available. You can submit balances for the organizations of all entities that are below the ownership branch of the selected entity. Balance Type Actual and average, as well as all additional data types defined are available. Start and End Period You can specify the period range in which you want to apply the adjustment.
Selected User Interface Elements To perform write back, set Do you want to write back entries in GL to Yes in the Upload window of the adjustment WebADI spreadsheet. Then set the parameters for where the General Ledger journal is to be written. Target Ledger The Ledger to which you add the adjustment. You can select from all ledgers that are assigned in Enterprise Performance Foundation.
Once the concurrent request completes, the write journal is available to view, edit, and post. The journal will have a name in the format Consolidation Reporting . If you perform multiple write backs of an adjustment to the same target ledger in General Ledger, it will first write a reversal entry to eliminate the original write backs to that ledger.
17 Accounting for Acquisitions and Disposals Overview Accounting for acquisitions and disposals is a way to tie changes in the consolidation hierarchy to consolidation entries. Each time a consolidation hierarchy structure changes, you can associate acquisition and disposal entries to the change. You can manually enter Acquisition and Disposal entries or generate them through Acquisition and Disposal rules.
Ownership. A transaction of type Share Issue with Acquisition has the following additional parameters. • Post Share Issue Ownership (%) - The ownership percentage for the entity after the share issue and before the acquisition. • Post Share Issue Consolidation Method - The consolidation method for the entity after the share issue and before the acquisition. These parameters are used as inputs for Acquisition and Disposal Rules.
properties will be set to the following. • From Consolidation Method - The original consolidation method of the entity, and the one that is visible when you view the consolidation hierarchy for an effective date before the change. • To Consolidation Method - You will specify this method in the To Consolidation Method field. This field does not impact the structure of the consolidation hierarchy. • Change Type - Adding an entity will have the change type as Disposal.
• Post Share Issue Consolidation Method - Specify the consolidation method for the entity after the share issue and before the acquisition. This field does not impact the structure of the consolidation hierarchy. • Post Acquisition Ownership (%) - Specify the final ownership percentage for the entity that is visible when you view the consolidation hierarchy for an effective date after the change.
Note: The subsidiary trial balance is not the same thing as the data submission trial balance. The subsidiary trial balance is used solely as a source for Acquisition and Disposal rules. The data submission trial balance represents the operating balances of the entity, and is used as the source of consolidation rules and is aggregated during consolidation. The subsidiary trial balance is submitted in the consolidation chart of accounts in the parent entity's currency.
See: Setting Up Consolidation Rules, page 9-1 Selected User Interface Elements for Acquisition and Disposal Rules Category For an Acquisition and Disposal rule, select one of the following change types. • Acquisition • Disposal • Share Issue • Acquisition Linked to a Share Issue The category of the rule must match the change type of the Acquisition and Disposal transaction for the rule to be executed for that transaction.
Variable Description Source NAV Net asset value (assets less liabilities) Generated from Subsidiary Trial Balance %TO New ownership percentage Entered when Updating Consolidation Hierarchy %FROM Old ownership percentage Entered when Updating Consolidation Hierarchy ADTB Acquisition and disposal trial balance Entered in Subsidiary Trial Balance PRETB Pre-share issue trial balance Entered in Subsidiary Trial Balance POSTTB Post-share issue trial balance Entered in Subsidiary Trial Balance
18 Viewing Reports Overview Oracle Financial Consolidation Hub provides various reports to monitor and track the consolidation process. These reports will allow you to do the following: • Report on consolidation results. • Diagnose issues that may have occurred during the consolidation process. • View the company and accounting setup. Several reports have drilldown capabilities.
direct children. • Balance Type - Actual, Average, or any user-defined data types. You can access this report in the Consolidation Monitor or the Reporting User Interface. See: Viewing Results, page 15-4 You can customize this report by modifying the XML Publisher template. See:Task 11: XML Publisher Templates, page 5-15. In the Consolidation Trial Balance Report, you can drill down on balances for operating entities.
You can customize this report by modifying the XML Publisher template. See Task 11: XML Publisher Templates, page 5-15 In the Consolidation Entries Report, you can drill down on balances for any report that is not in the Minority Interest category or any user defined categories. Intercompany Matching Report An intercompany transaction occurs when two companies in the same consolidation hierarchy have a financial transaction.
Data Submission Trial Balance Report The Data Submission Trial Balance report shows the pre-consolidated trial balances of an entity. If multiple data submissions were performed for the same entity, period, currency and balance type, the Data Submission Trial Balance Report will show the accumulated result of all the data submissions. The Data Submission Trial Balance Report has the following parameters: • Entity - An operating entity for which data was submitted.
• Period - The period for which data was submitted. • Balance Type - Actual, Average, or any user-defined data types. See: Submitting Data, page 14-1 You can access this report in the Data Submission User Interface. For more information on accessing this report, see View Data Loaded, page 14-3. You can customize the report by modifying the XML Publisher template. See: Task 11: XML Publisher Templates, page 5-15.
• Entity - A consolidation entity in the selected hierarchy. The report will show the consolidation entity and all of its descendents. You can access this report in the Setup Hierarchy User Interface. You can also run the report using the concurrent program FCH: Hierarchy Listing Report. For more information on accessing this report, see Viewing Consolidation Hierarchies, page 13-8. You can customize the report by modifying the XML Publisher template. See: Task 11: XML Publisher Templates, page 5-15.
• Entity- The entity of consolidation hierarchy for the adjustment written back. • Category- The category of the adjustment in Financial Consolidation Hub. • Target Ledger- The ledger in Oracle General Ledger to which the adjustment is written back. • Target Ledger Period- The period in Oracle General Ledger to which the adjustment is written back.
19 Setting Up Analytical Reporting Overview Oracle's Corporate Performance Management Suite uses the analytical capabilities embedded in Oracle 9i and 10g. Analytical reporting enables you to create ad-hoc reports, set performance measures, distribute reports, and pivot and drill on dimensions. For more information on Analytical Reporting, see the Oracle Enterprise Planning and Budgeting User's Guide.
Note: Ensure that each dimension hierarchy contains all the dimension members of interest. If a dimension member is not included in the hierarchy, analytical reporting will not report on any balances using that dimension member. Creating Functional Dimension Definition Rule for Intercompany If you selected the Intercompany dimension as Used by Reporting, you must create a functional dimension definition rule in order to see the dimension in Enterprise Planning and Budgeting (EPB).
Field Description Dimension Company cost center organization Functional Dimension Name Company cost center organization Table Name Select the data table that you had selected in FCH Foundation Task 12: Analytical Reporting, page 5-16. Column Name Company Cost Center Organization 4. Click Add. 5. The values you entered disappears, as you will define an additional functional dimension name.
you will be presented with the following tabs: General • Business Area Name - Enter a unique business area name. See: Task 1: Processing Keys, page 5-4 • Functional Dimension Rule- If you had enabled the Intercompany dimension as Used by Reporting, you must create a functional dimension rule to represent the Intercompany dimension, and select that rule in this parameter.
• Currency - You do not need to refine this dimension. • Entity - Select the hierarchies that correspond to your consolidation hierarchies. The hierarchies are named in the format effective since . See: Consolidation Hierarchy in Enterprise Performance Foundation, page 13-1 Note: Ensure that there are no consolidation result balances for parent values in the dimension hierarchies, with the exception of the entity dimension.
If your consolidation chart of accounts changes later on, you have to refresh the business area to pull the changes into Enterprise Planning and Budgeting. Here are some common changes: • Create or modify a dimension hierarchy for use in reporting. • Create or modify dimension members in the consolidation chart of accounts. • Modify your consolidation hierarchy Note: You do not need to refresh the business area if the changes do not affect the consolidation chart of accounts.
to pull in multiple balance types, for example actual and budget, or consolidation results from multiple consolidation hierarchies, you will have to create multiple business processes. To create a business process, use the Enterprise Planning and Budgeting Controller responsibility and navigate to Administration > Business Processes. If you have access to multiple business areas, you will be prompted to select a business area.
are balances, the business process will not bring those balances into EPB. Solve You can update how balances are calculated for each line item. The default behavior is for child values to pull the balances from Enterprise Performance Foundation (Loaded) and for parent values to sum their children's balances (Hierarchy Total). If you have Net Profit being the parent of Revenue and Expense, you have to update Net Profit to make it equal to Revenue minus Expense. To do so, perform the following steps: 1.
all parameters as default 2. Solve - This calculates the balances for parent values in the dimension hierarchies. Leave all parameters as default. 3. Set Current Process Run - Each time you run a business process, it creates a new view. This task will create a view named - Current Instance, which will always point to the latest view for the business process.
to always point at the most current view. See Schedule, page 19-9 If your consolidation results change in Financial Consolidation Hub, you have to run the business process again to pull in the new balances. To run a business process, click Draft for the business process, then Make Effective. This will launch the following concurrent requests one at a time: 1. ZPB : BP Validation CP 2. ZPB: Make Analysis Cycle Instance for Workflow 3. ZPB: Load Data 4. ZPB: Run Solve 5.
Creating and Sharing Reports After successful completion of a business process, you can use the results in analytical reports and graphs. You can create, save, open, and modify reports. Reports exist in an online file system of folders that allows other designated users to access your reports. When you finish adjusting the content and format of the report, click Save. You can place the report in the Public Folder or any other folder you have access to .
5. Select the line items you want to view in the report. 6. Click Continue. 7. You will see each reporting dimension you selected in business process under the Dimensions tab. For each dimension, you can select the values you want to view in the report. See: Dimensions, page 19-4 8. Click Finish. You will have now created an analytical report. Editing a Report There are many ways to edit an analytical report. Here are a few common ways to modify reports.
• Calculations - You can insert calculations on the data in your report. To insert calculations, click the Calculation link in the Tools region. For more information on ways to manipulate analytical reports, please refer to the Enterprise Planning and Budgeting User's Guide. Sharing Reports You can share reports with other users by setting the proper security for reports and folders. You can control which users can view or overwrite reports, and which ones can change the security settings.
20 Setting Up Excel Add-in Overview The Excel Add-in is an Excel-based analytical reporting tool that allows you to access Analytical Reports via Excel. Using the Excel Add-in, you have access to the core functionality of Analytical Reporting like creating and modifying reports with additional features like drilldown to source data and saving to computer. In addition, you can leverage standard Excel functionality to further modify your reports.
Add-in. Download the setup file and install the Excel Add-in onto your computer. Note: If you have an earlier version of the Excel Add-in, you must first uninstall it. The Excel Add-in has the following system requirements: • Operating System: Microsoft Windows 2000 or above • Microsoft Excel: 2000 or above • .NET Framework Redistributable Package: 1.1 or above Once Excel Add-in is installed, an additional menu in Excel called Consolidation gets added.
Report Types You can perform two types of modifications to a report in the Excel Add-in: • Analytical Report modifications, for example which dimension show up in the columns and rows. These modifications are outlined in Modifying Reports, page 205. • Excel modifications such as adding Excel formulas and charts. The Excel Add-in supports the following two types of reports to capture the different types of modifications.
The Excel Add-in uses the same online folder system. This allows reports to be shared between users and between the Enterprise Planning and Budgeting user interface and the Excel Add-in. The Excel Add-in utilizes the Analytical Reporting security model to determine which reports you have access to. To save a report, perform the following steps: 1. Select the Report > Save As function under the Consolidation menu. 2. Select a name and online folder to save the report into.
1. Select the Report > Create function under the Consolidation menu. 2. Select the views you want to use in the report. Click Next. 3. For each reporting dimension, select a saved selection. Click Next. For information on creating saved selections in the Excel Add-in. See: Creating Saved Selections, page 20-8 Note: The Enterprise Planning and Budgeting user interface allows you to select members for each dimension during the process of report creation.
For dimensions in the page level of a report, you can select a particular value. If multiple values are available, the page level value is a list. When you select a value, the report will refresh to show the balances that correspond to the selected value. Export to PDF You can export a report to PDF format. To do so, select the Report > Export to PDF function under the Consolidation menu. Note: The Export to PDF functionality currently does not support images.
different page level item value, the formulas and charts will automatically read the new balances and get updated. You can use Excel modifications in conjunction with analytical report modifications. However, if you perform any analytical report modifications other than expanding and collapsing hierarchies or changing page level item values, the existing Excel modifications are removed.
Saved Selections The Excel Add-in allows you to create and manage saved selections independently of reports. Note: This feature is specific to the Excel Add-in. Enterprise Planning and Budgeting only allows you to create and manage saved selections within the report user interface. Creating Saved Selections To create a saved selection, perform the following steps: 1. Select the Saved Selection > Create function under the Consolidation menu. 2. Select a dimension.
Note: The Excel Add-in can only pull values from a selected dimension hierarchy. It can not pull values from existing saved selections. 4. Select a name and online folder to save the saved selection into. Optionally enter a description for the saved selection. You may overwrite any saved selection for which you have write access. You can also set the security access for the saved selection. Your user will automatically be given full access.
20-10 • Enable Trace - Select the check box to enable tracing. A trace file will be written that logs the operations of the Excel Add-in. This trace file can be used to debug any problems that occur when using the Excel Add-in. • Trace Level - Select the trace level to be ERROR, DEBUG, or STATEMENT. ERROR will only record to the trace file errors that occur during the operation of the Excel Add-in, while STATEMENT will record each individual statement executed.
21 Security Overview Security is a feature that enables individuals to access, load, or consolidate data based on their responsibility within an organization. For example, consolidation managers at corporate headquarters may need access to the entire corporate hierarchy, whereas those at a subsidiary may only be allowed access to the data related to their region.
Assigning Roles You can assign entities and consolidation hierarchies to a role in the Role page.
Scenario 1 If Joe Smith only has access to consolidation entity Corporate and Subsidiaries , then he has access to the consolidation results for the entire branch under Corporate and Subsidiaries, including Operations and Subsidiaries. Scenario 2 If Joe Smith only has access to operating entity Corporation, then he can load data and enter operating adjustments for Corporation only.
Related Topics Overview, page 21-1 Function-Based Security Function-based security is related to the responsibility you select when you log in. Responsibilities include specific functions, user interface elements, and menus, which determine the pages to which you have access. If a responsibility you log into does not include specific functions, user interface elements, or menus, those items are not visible to you.
22 Integrating with Oracle Internal Controls Manager Overview As much as Sarbanes-Oxley has helped to restore investor confidence, the law continues to weigh down public companies. The costs associated with compliance are significant, and the time and effort required are keeping some finance executives away from more strategic initiatives. Companies will need to build reliable and repeatable compliance processes if they want to create a sustainable approach to compliance.
4. Create ICM Business Process and Financial Statement: The previous steps will make the selected consolidation hierarchy available to these core Internal Control Manager components. Note: These setup tasks utilize the Internal Controls Manager Super User and Signing Officer responsibilities. Initial Setup Tasks for Internal Controls Manager Integration Before you can use Internal Controls Manager with Financial Consolidation Hub, you must perform the following initial setup tasks. 1.
the Oracle Internal Controls Manager Implementation Guide. Using the Consolidation Manager responsibility, navigate to Setup>Auditable Unit. This page allowsyou to create, view, and modify auditable units. Enabling Certification for a Consolidation Hierarchy To enable a consolidation hierarchy for Internal Controls Manager certification, do the following: 1. Set the Used for Certification hierarchy option to Yes. Note: You can only have one consolidation hierarchy enabled for certification at a time.
Parameter Value Financial Statement Name of Consolidation Hierarchy Included Certifications The business process you created in step 1 Note: You can create as many financial statement certifications for the financial statement in each period as you want in Internal Controls Manager, but Financial Consolidation Hub only shows the status for the latest certification in the consolidation monitor. For this reason, we recommend you to create one certification in each period.
in Internal Controls Manager. From this page, you can perform updates while still being able to navigate back to Financial Consolidation Hub. For more information, see the Oracle Internal Controls Manager Implementation Guide. Note: Percentages shown in the pages are derived from the percentages of organization-process pairs that were certified with no issues. For example, entity Vision Corporate Operations has two auditable units Finance and HR.
A WebADI Spreadsheets WebADI Spreadsheets Financial Consolidation Hub utilizes WebADI spreadsheets to allow you to upload and manage moderate amounts data. WebADI spreadsheets give Excel a live connection to your instance, allowing you to both pull information from the environment and upload it within Excel. This chapter provides in detail how Financial Consolidation Hub uses WebADI spreadsheets.
1. Set Security Level to Medium on the Security Level tab. 2. Select, Trust all installed add-ins and templates, on the Trusted Sources tab. Excel 2003 Navigate to Tools > Macro > Security 1. Set Security Level to Medium on the Security Level tab. 2. Select, Trust all installed add-ins and templates, on the Trusted Publishers tab. 3. Select, Trust access to Visual Basic Project, on the Trusted Publishers tab.
• Selected values are in the list of valid values pulled from the environment. • All required fields have been filled. • There are no duplicate rows. • Any other requirement based on the task being performed. If WebADI finds any errors in the spreadsheet, it stops the upload and mark the errors in the spreadsheet. If it does not find any errors, it begins to upload the data. Once the data is uploaded, you receive a confirmation message and all uploaded rows are marked with a green smiley face.
spreadsheet. Second, in some cells you may now select from a list of values. Dropdown Box Cells When you need to select a value from a list of values for a cell, you may be presented with a list of valid values in the form of a dropdown box. Double Click Cells If a cell requires you to select a value from a list of values and there is no dropdown box, you can see a list of valid values by double clicking the cell. The list of values is pulled directly from the environment.
B Concurrent Programs Concurrent Programs Financial Consolidation Hub leverages the concurrent manager for high volume and background processes. The following details are on the concurrent programs used within Financial Consolidation Hub: Note: All Financial Consolidation Hub concurrent programs are prefixed with "Financial Consolidation Hub: ". Acquisition and Disposal Entry Import The A&D Entry Import program is called automatically when a manual adjustment is uploaded for an A&D transaction.
Reporting step has been completed. You can run this program manually using the Submit Request window for a specific consolidation hierarchy, period, and balance type. See: Task 12: Analytical Reporting, page 5-16 Consolidation Entries Write back The Consolidation Entries Write back program is called automatically when uploading an adjustment that was selected to be written back to Oracle GL. The consolidation entry will be converted to the chart of accounts format and written to GL_INTERFACE.
See: Task 8: Value Set Maps, page 5-12 Dimension Transfer The Analytical Reporting chapter mentions that if you need to report on the created by object dimension, it must mapped to a user dimension in the Analytical Reporting Task of Foundation. In this case, the Dimension Transfer program pushes the dimension members from the created by object dimension to the user dimension. It is called automatically by the Consolidation Data Transfer program.
manually using the Submit Request window. See: Spreadsheet Loader, page 14-11 Maintain Entity Value Set The Maintain Entity Value Set program takes entities defined in the Consolidation Entity User Interface and propagates them back to the FCH_ICM_ENTITY_VALUE_SET value set in the Oracle Application Object Library (AOL). This program is called immediately after an entity or set of entities is created. It will propagate all the entities: a) consolidation, b) elimination, and c) operating to the value set.
Set Internal Controls Manager Profile Option Values The Set Internal Controls Manager Profile Option Values program is used to set key profiles in Internal Controls Manager to facilitate the integration between the structures in Financial Consolidation Hub and Internal Controls Manager. You must call this program manually using the Submit Request window. It should be executed before you start configuring Internal Controls Manager.
XML Generator The XML Generator program is used to pre-generate the data submission and consolidation trial balance in XML format for optimal performance to render the PDF or Excel document. The program is immediately launched after Data Submission completes, and whenever consolidation completes for a specific consolidation entity. The program can be re-run for Data Submission by clicking on the Renegerate XML button. You should not call this program manually using the Submit Request window.
C Business Events Business Events Financial Consolidation Hub leverages business events raised by General Ledger and Enterprise Performance Foundation for inter-product communication and internally to allow customers to subscribe to activities of interest during the consolidation close process. Financial Consolidation Hub Events Pristine Data Loaded or Altered oracle.apps.gcs.pristinedata.
Acquisition and Disposal Created or Altered oracle.apps.gcs.transaction.acqdisp.update The A&D Created or Altered event is raised whenever the rules engine automatically generates an elimination entry or a manual elimination is created. Financial Consolidation Hub ships a subscription to this event to check if any consolidation runs are impacted by a new elimination entry. Manual Adjustment Created or Altered oracle.apps.gcs.transaction.adjustment.
D Profile Options Profile Options Financial Consolidation Hub leverages several profile options shipped by Enterprise Performance Foundation, Enterprise Planning and Budgeting, and Application Object Library. The profile options can be accessed using the System Administrator responsibility, Profile > System.
FND: Debug Log Level If an error occurs in Financial Consolidation Hub and debugging must be performed, this profile must be set to STATEMENT. Enterprise Performance Foundation Profile Options For more detailed information on Enterprise Performance Foundation profile options, please refer to the Enterprise Performance Foundation User's Guide. FEM: Signage Methodology For Financial Consolidation Hub, the Signage Methodology must be set to Standard General Accounting Principals.
BNE Server Log Filename This is a free-form text value to enter the file name that contains the logging information. BNE Server Log Path This must be a directory accessible from the Web Server tier. Ensure that the Apache and Jserv processes have write permissions to this directory.
Index A C acquisition and disposal Calculation, 17-4 Create Accounting for Consolidation Hierarchy Changes, 17-2 Manual Entry, 17-4 overview, 17-1 seeded category, 7-3 adjustments definition, 16-1 disabling, 16-5 example 1, 16-1 example 2, 16-1 examples, 16-1 overview, 16-1 updating, 16-5 aggregation seeded category, 7-4 analytical reporting preparing data, 15-5 average rates, 12-4 average rate type, 12-2 calendar map example, 11-1 overview, 11-1 setting up, 11-1 categories acquisition and disposals, 7-3
setting up, 13-2 specifying balancing options, 13-5 specifying intercompany options, 13-4 specifying retained earnings, 13-5 specifying security options, 13-4 specifying suspense handling, 13-5 updating, 13-7 View, 13-8 consolidation hierarchy attributes defining, 13-3 consolidation hierarchy structure, 13-1 creating , 13-6 consolidation methods overview, 8-1 selected user interface elements, 8-1 setting up, 8-1 consolidation process data status, 15-1 locking results, 15-4 monitoring, 15-1 process history,
Enterprise Performance Foundation overview, 5-1 setting up, 5-1 using with Oracle General Ledger, 5-17 entities selected user interface elements, 6-4 setting up, 6-1 entry intercompany elimination, 10-2 intracompany elimination, 10-2 equity mode period-to-date, 12-2 year-to-date, 12-2 example calendar map, 11-1 Multiple Parents, 13-10 Oracle entities, 14-8 Excel Add-in Configuration Options , 20-9 Creating Reports, 20-2 Creating Saved Selection, 20-8 Installing, 20-1 Logging, 20-1 Modifying Reports, 20-2 Mo
assignments, 5-18 Line Item Intercompany Maps, 5-13 locking consolidation process, 15-4 look up table rule, 14-11 M matching intercompany balances, 10-1 minority interest seeded category, 7-3 O operating entity securing, 21-2 setting up, 6-1 Oracle entities example, 14-8 P period-to-date equity mode, 12-2 income statement mode, 12-3 PL/SQL function rule, 14-11 process history consolidation process, 15-4 process status consolidation process, 15-2 Q quick reference implementation issues, 2-1 R reporting
setting up analytical reporting, 19-1 balances rules, 14-4 calendar maps, 11-1 categories, 7-1 consolidation entity, 6-1 consolidation hierarchies, 13-2 consolidation methods, 8-1 consolidation rules, 9-1 controlling entity, 6-1 data submission for external entities, 14-10 data submission for Oracle entities, 14-4 elimination entity, 6-2 Enterprise Performance Foundation, 5-1 Enterprise Performance Foundation tasks, 5-3 entities, 6-1 intercompany rules, 10-1 operating entity, 6-1 reporting, 5-16 translation
creating, 5-12 value sets creating, 5-8 viewing consolidation process, 15-4 W WebADI Spreadsheets Downloading, A-2 Overview, A-1 Requirements, A-1 Uploading, A-2 what-if analysis and restatements duplicate hierarchies, 13-9 X XML Publisher templates specifying, 5-15 Y year-to-date equity mode, 12-2 income statement mode, 12-3 Index-6
