Oracle Oracle Financial Consolidation Hub, User Manual

Page 1: ...Oracle Financial Consolidation Hub User s Guide Release 11i Part No B16229 05 March 2007 ...

Page 2: ...uisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restri...

Page 3: ... Setting Up Oracle Financial Consolidation Hub Oracle Financial Consolidation Hub Setup Overview 3 1 4 Setting Up Users Overview 4 1 5 Setting Up Oracle Enterprise Performance Foundation Overview 5 1 Tasks 5 3 Using Oracle General Ledger with Oracle Enterprise Performance Foundation 5 17 6 Setting Up Entities Overview 6 1 Selected User Interface Elements for Setting Up Entities 6 4 Spreadsheet Loa...

Page 4: ...olidation Rule Steps 9 2 Balancing a Consolidation Rule 9 3 Additional Data Types 9 4 Examples of How Consolidation Rules Work 9 4 Eliminate Investment in Subsidiary 9 8 10 Setting Up Intercompany Rules Overview 10 1 Matching 10 1 Suspense Handling 10 2 Additional Data Types 10 3 Example of How an Intercompany Rule Works 10 3 11 Setting Up Calendar Maps Overview 11 1 12 Setting Up Translation Over...

Page 5: ...ts for Submitting Data for External Sources 14 13 15 Running Consolidations Overview 15 1 Submitting Consolidations 15 4 Preparing Data for Analytical Reporting 15 5 16 Adjustments Overview 16 1 Updating Adjustments 16 5 Disabling Adjustments 16 5 Selected User Interface Elements 16 5 Write back to General Ledger 16 6 17 Accounting for Acquisitions and Disposals Overview 17 1 Create Accounting for...

Page 6: ...elections 20 8 Security 20 9 Excel Add in Configuration Options 20 9 21 Security Overview 21 1 Role Based Security 21 1 Function Based Security 21 4 22 Integrating with Oracle Internal Controls Manager Overview 22 1 Setup for Integration with Internal Controls Manager 22 1 Creating Auditable Units 22 2 Enabling Certification for a Consolidation Hierarchy 22 3 Creating Business Process and Financia...

Page 7: ...vii 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 D 1 Index ...

Page 8: ......

Page 9: ...ts the title and part number of the documentation and the chapter section and page number if available Note Before sending us your comments you might like to check that you have the latest version of the document and if any concerns are already addressed To do this access the new Applications Release Online Documentation CD available on Oracle MetaLink and www oracle com It contains the most curre...

Page 10: ......

Page 11: ...formation available to users of assistive technology This documentation is available in HTML format and contains markup to facilitate access by the disabled community Accessibility standards will continue to evolve over time and Oracle is actively engaged with other market leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers ...

Page 12: ... Consolidation Hierarchies 14 Submitting Data 15 Running Consolidations 16 Adjustments 17 Accounting for Acquisitions and Disposals 18 Viewing Reports 19 Setting Up Analytical Reporting 20 Setting Up Excel Add in 21 Security 22 Integrating with Oracle Internal Controls Manager A WebADI Spreadsheets B Concurrent Programs C Business Events D Profile Options Related Information Sources You can choose...

Page 13: ...s users on how to use Oracle XML Publisher to create customized reports from the Oracle E Business Suite Oracle Web Applications Desktop Integrator User Guide ADI is an integral part of the Oracle General Ledger application ADI is a spreadsheet based extension of General Ledger and Oracle Assets that offers full cycle accounting within the comfort and familiarity of a spreadsheet This guide descri...

Page 14: ...acle Applications Refer to this guide if you are upgrading your Oracle Applications Release 10 7 or Release 11 0 products to Release 11i This guide describes the upgrade process and lists database and product specific upgrade xi tasks You must be either at Release 10 7 NCA SmartClient or character mode or Release 11 0 to upgrade to Release 11i You cannot upgrade to Release 11i directly from releas...

Page 15: ... and a detailed description of database tables forms reports and programs for a specific Oracle Applications product This information helps you convert data from your existing applications and integrate Oracle Applications data with non Oracle applications and write custom reports for Oracle Applications products Oracle eTRM is available on OracleMetaLink Oracle Applications Message Reference Manu...

Page 16: ...cations 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 If you enter information into database tables using database tools you may store invalid information You also lose the ability to track who has changed your information because SQL Plus and other database t...

Page 17: ... to comply with more stringent financial reporting requirements while seeking faster access to critical business information Compounding these pressures are disparate financial systems complex ownership structures and multiple business units cost centers or consolidation structures that make consolidating financial data a manual time consuming and resource intensive process Financial Consolidation...

Page 18: ...ile without effective execution likewise effective execution is wasted if it s based on flawed strategy Achieving such corporate alignment requires a single enterprise view of the firm s financial position Financial Consolidation Hub creates this view by integrating multiple subsidiaries and investments from a variety of countries with disparate accounting systems ownership structures charts of ac...

Page 19: ... section in this user s guide where you can obtain further information For ease of presentation the issues are organized in the following tables into three categories data submission consolidation processing and reporting and analysis Data Submission Issue Functionality Reference Use data from Oracle General Ledger Provides direct access to data in Oracle General Ledger Setting up Enterprise Perfo...

Page 20: ...e page 6 4 Specifying Security Options page 13 4 Role Based Security page 21 1 Consolidation Processing Issue Functionality Reference Consolidate based upon different structures and accounting methods Consolidation hierarchies model different structures and methods Setting Up Consolidation Methods page 8 1 Consolidation Rule Steps page 9 2 Support date effective structures Consolidation hierarchie...

Page 21: ... rules automate both intercompany and intracompany eliminations Matching page 10 1 Automatically create consolidation entries You can automatically create entries using consolidation rules Consolidation Rule Steps page 9 2 Perform currency standardization Automatically performed any time an entity s currency differs from that of its parent Overview page 12 1 Reconcile legal and management results ...

Page 22: ...ut Business Process Administration Oracle Enterprise Planning and Budgeting User s Guide Support date effective reorganizations Easily edit and view multiple organization hierarchies in the same data model Dimension Hierarchies page 5 11 Enable time series and variance analysis Administrators or end users can define and save formulas for use in reports Working with Calculations Oracle Enterprise P...

Page 23: ...the sequence indicated Financial Consolidation Hub Setup Checklist Step Number Setup Step Step Type Description Oracle Applications System Administrator or Oracle User Management 1 Set Up Roles page 21 1 Required Used for notifications and security Oracle Financial Consolidation Hub 2 Set Up Enterprise Performance Foundation page 5 1 Required Used to define dimensionality and reference data 3 Set ...

Page 24: ...9 1 Optional Used to automate consolidation entries 7 Set Up Intercompany Rules page 10 1 Optional Used for intercompany and intracompany eliminations 8 Set Up Calendar Maps page 11 1 Optional Used to designate the consolidation calendar and standardize other calendars 9 Set Up Translation page 12 1 Optional Used to translate or remeasure data to a parent s currency Skip this step if are implement...

Page 25: ...age 21 1 To implement Financial Consolidation Hub you may require access to the following products or functionality as listed in the following table Responsibility Product Functionality Consolidation Manager Financial Consolidation Hub Set up Financial Consolidation Hub perform data submissions and run consolidation Legal Entity Controller Financial Consolidation Hub Perform data submissions Enter...

Page 26: ...d responsibilities XML Publisher Administrator XML Publisher Create new report templates General Ledger Super User Oracle General Ledger Super User access to Oracle General Ledger only necessary if you are consolidating operating balances in Oracle General Ledger Note This is only a list of the typical responsibilities needed to implement Financial Consolidation Hub It does not take into account a...

Page 27: ... using spreadsheet loaders and open interfaces EPF comes with a set of pre defined fact tables a table called FEM_BALANCES Balances table that is used to store financial balances loaded from Oracle General Ledger or any other financial system and 20 tables called FEM_DATA1 to FEM_DATA20 to be used for other purposes Financial Consolidation Hub uses the Balances table in two key ways Reads from the...

Page 28: ...eriod Based on the accounting calendar for your organization 6 Currency Identifies the currency for each balance Each entity typically has one base currency and the financial data loaded for that entity is denominated in that currency If an entity is consolidated to a higher level entity with a different currency Financial Consolidation Hub automatically translates from one currency to the other O...

Page 29: ...e object dimension is handled internally See Setting Up Categories page 7 1 2 Currency Type Used to distinguish financial data that is entered in a specific currency from balances that are translated to a specific currency 3 Data Set Used to group different types of data Financial Consolidation Hub automatically creates a data set for each hierarchy and balance type 4 Ledger Used to identify the s...

Page 30: ...nistrator responsibility navigate to Processes Management Requests Submit Request Run the Sign Extended Account Types concurrent program 2 Set negative format to using a negative sign Using the System Administrator responsibility navigate to Profile System Set the Currency Negative Format profile option to XXX FCH reports will now represent negative numbers using the negative sign If any other opt...

Page 31: ... specific information in your consolidation process may be unnecessary In this case Financial Consolidation Hub aggregates across all values of this dimension during processing To set a dimension active for consolidation processing select the Used by Processing column for the dimension Used By Analytical Reporting From the set of processing dimensions you must then specify those to be used for ana...

Page 32: ...tion The preview provides the recommended ordering of columns for optimal performance for Financial Consolidation Hub Your database administrator may further refine the index definition if other applications are being leveraged See Oracle Enterprise Performance Foundation User Guide Task 3 Data Types Task 3 must be the third step you do in Financial Consolidation Hub after completing Task 2 For Ta...

Page 33: ...d ones The following table shows attributes required for Encumbrance data sets Data Sets Attributes Dataset Balance Type Code Encumbrance Dataset Encumbrance Type ID User specified Dataset Update Flag Yes Dataset Production Flag Yes Dataset System Code User specified Note You can only create one data type per Dataset Encumbrance Type ID Budget data sets require the following attributes Data Sets A...

Page 34: ...lue sets in Enterprise Performance Foundation For Task 4 you can do the following Create value sets for dimensions Search for and view any existing value sets A value set is a list of dimension members that belong to a particular dimension Value Sets are used to separate dimension members into meaningful sets For example a company may perform accounting according to local and corporate standards I...

Page 35: ...ocess based on mappings defined by the user in Task 8 Value Set Maps You can select the seeded default global value set combination or you can create a global value set combination if needed Once you click Complete Financial Consolidation Hub will launch the FCH Module Initialization concurrent program Monitor this concurrent program and verify that it completes successfully You can create a globa...

Page 36: ...mns for display code name description and value set If you are working with the following dimensions the spreadsheet loaders include additional columns Line Item An additional column is available to select the account type Natural Account An additional column is available to select the account type Company Cost Center Organization Additional columns are available to enter the company and cost cent...

Page 37: ...he following Create dimension hierarchies for processing and reporting Search and view and search and update any existing dimension hierarchies A dimension hierarchy is a structure of dimension members that is organized by parent child relationships Dimension hierarchies can have multiple versions all of which have an effective date range If you are using Oracle General Ledger then the segment val...

Page 38: ...different accounting systems in different regions of the world Financial Consolidation Hub enables you to standardize on a chart of accounts using value set mapping capabilities Value set maps enable Financial Consolidation Hub to automate the standardization of data from multiple charts of accounts to a single consolidated chart of accounts If your organization uses a single global chart of accou...

Page 39: ...tions using Line Item or Account dimensions For Task 9 you can do the following Create Line Item Intercompany Maps Update existing Line Item Intercompany Maps You use these maps where you track some of your intercompany balances according to particular line item values A Line Item Intercompany Map specifies the relationship between a line item value and an intercompany value in the consolidation c...

Page 40: ...0 Canada Cash None 600 When you run consolidation the process will identify any matches for the line item intercompany maps It will then change the Intercompany value based on the maps Company Line Item Intercompany Balance Canada I C Accounts Payable None 100 Canada I C Accounts Payable Canada Canada 200 Canada I C Accounts Payable Mexico Mexico 300 Canada Cash None 600 You can create or update L...

Page 41: ...solidation Hub launches the FCH Publishing Utility concurrent request to produce the report You can then view the output of the request to see the report This prevents the Financial Consolidation Hub user interface from timing out when the application production produces a large report You can also specify the XML Publisher templates you want to use for formatting reports Financial Consolidation H...

Page 42: ...ing analytical reporting Task 12 enables you to use and view your consolidated results in an OLAP environment Specify a data table in Enterprise Performance Foundation This table is used as an interface table to move consolidation results into analytical reporting When you run consolidation the results are pushed into the data table Analytical reporting can then pull the consolidation results from...

Page 43: ...Module Initialization concurrent program Using Oracle General Ledger with Oracle Enterprise Performance Foundation Financial Consolidation Hub facilitates the synchronization of data from Oracle General Ledger This comprises the following setup components ledger assignments dimension rules hierarchy rules and balances rules For information on balances rules see Setting Up Data Submission for Oracl...

Page 44: ...rm the actions listed in the expansion After your System Administrator performs the actions Oracle General ledger should appear in one of the regions Ledger Assignments To use a ledger from Oracle General Ledger in Enterprise Performance Foundation assign the ledger Defining Dimension Rules Dimension rules define how General Ledger charts of accounts relate to Enterprise Performance Foundation dim...

Page 45: ...egment 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 The dimension mapping has the following options for each dimension Copy Values from Single Segment Assign a Default Value Concatenate Values from Multiple Segments Financial Consolidation Hub recomm...

Page 46: ...ompany 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 Financial Consolidation Hub recommends that you map the company segment to the Organization dimension and the cost center segment to a user defined dimension for reporting purposes Note For Financial C...

Page 47: ...y Maps functionality See Task 9 Line Item Intercompany Maps page 5 13 Line Item The Line Item mapping can copy values from a single segment or concatenate up to three segments Map the natural account segment to the Line Item dimension Financial Consolidation Hub recommends concatenation of segments to the Line Item dimension only if you have dependent segments on the natural account segment Natura...

Page 48: ... the new value set is in the OGL value set DIM number format If a dimension copies from multiple segment the new value set is in the OGL value set1 OGL value set 2 DIM number format After you run the dimension rules you can transfer dimension hierarchies and balances to Enterprise Performance Foundation You can also undo dimension integration Delete or undo any associated dimension hierarchies or ...

Page 49: ...atically generate dimension hierarchies based on General Ledger segment value hierarchies The selected hierarchy rule version creates a dimension member hierarchy The hierarchy name is in the format Hierarchy for Rule Hierarchy Rule To keep a version of the previous dimension hierarchy define a new dimension hierarchy rule version Note Add the users running dimension hierarchy rules to the Default...

Page 50: ......

Page 51: ...rating entity Consolidation entity Elimination entity Operating Entity This entity contains operating balances that are loaded from General Ledger or other financial systems using a data submission See Submitting Data for Oracle Entities page 14 6 Controlling Entity This entity is an operating entity associated with a consolidation entity The consolidation entity controlling entity relationship is...

Page 52: ... 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 Example In the following hierarchy APAC and Subsidiaries is a consolidation entity It has a controlling entity APAC HQ The associated elimination entity is A...

Page 53: ...on member in the default value set for the Entity dimension Note An entity created in Financial Consolidation Hub has specific parameters attached to it that you can view in Enterprise Performance Foundation but not vice versa A ledger is also created for the entity with the name in the format entity name ledger Note A ledger is not created for an entity in the case where the entity is an operatin...

Page 54: ...ole Based Security page 21 1 Image File You can upload an image file to represent the entity The image file must be in GIF format and be no larger than 16 x 16 pixels Enable Entity If you disable an entity you cannot include the entity in new hierarchies Disabling an entity has no effect on an existing hierarchy where the entity is already included Note If you disable a consolidation entity s cont...

Page 55: ...eneral Ledger source system you must select a value set combination based on the chart of accounts used in that source system See Task 4 Value Sets page 5 8 If you specify additional data types they will all use the same value set combination as the one you specify for actuals Calendar Hierarchy To create an operating entity with a third party General Ledger source system you must select a calenda...

Page 56: ...alidation rule set checks the data when you submit it If you specify additional data types each additional data type can have a different validation rule set See Validation Rule Sets page 14 10 Additional Data Types To create an operating entity set it up for actuals and averages data types You can also assign additional data types to an operating entity such as budgets Additional data types are s...

Page 57: ...rganizations assigned to them See Minority Interest page 7 3 Elimination Entity To create a consolidation entity you must enter a unique name for the elimination entity The elimination entity is automatically created with that name Controlling Entity To create a controlling entity you can select an existing operating entity to be the controlling entity for the consolidation entity Note An operatin...

Page 58: ...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 If the spreadsheet has no errors it launches the FCH Load Entities concurrent request ...

Page 59: ...d 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 Minority Interest 7 Aggregation ...

Page 60: ...erating data for consolidation by transforming the data to a standard consolidation chart of accounts and calendar Additionally it aggregates data to the consolidation processing dimensions in cases where consolidation processing does not use all the dimensions of the submitted data During Data Preparation if Financial Consolidation Hub determines that an operating entity is using a global value s...

Page 61: ...nates balances from transactions that occur between entities in the consolidation hierarchy Eliminations are written to the elimination entity of the first common parent in the consolidation hierarchy The Intercompany category can be placed anywhere after Translation See Intercompany Rule page 10 1 Acquisition and Disposal The Acquisition and Disposal category enables users to automate accounting ...

Page 62: ...es the balances of the parent entity For example equity pickup The parent entity needs to write up or down its investment in a subsidiary based on the subsidiary s profitability Elimination entity The adjustment or entry updates the balances of the elimination entity which does not affect the operational balances of any of the operating entities This is the case for most consolidation only adjustm...

Page 63: ...be recalculated each time the overall effective ownership of a child changes This occurs when a child is partially owned by multiple parents within a consolidation hierarchy Minority interest is a common example A consolidation entity may own 70 of a subsidiary but the remaining 30 is owned by another entity within the same hierarchy While it is customary to recognize minority interest for the con...

Page 64: ......

Page 65: ... Setting Up Consolidation Methods page 8 1 Selected User Interface Elements for Setting Up Consolidation Methods Selected user interface elements are described below Consolidation Types Consolidation types specify the method of consolidation Financial Consolidation Hub provides the following consolidation types Full consolidation Proportional consolidation None consolidation Full Consolidation A c...

Page 66: ...ou to view all the consolidation rules assigned to the consolidation method See Setting Up Consolidation Rules page 9 1 From Ownership To Ownership Percentage You can optionally specify an ownership percentage range for the consolidation method The consolidation method is automatically defaulted when the ownership percentage falls within the range in creating a consolidation hierarchy For example ...

Page 67: ...in a later chapter See Creating Consolidation Entries page 17 4 Selected User Interface Elements for Setting Up Consolidation Rules The following are selected user interface elements for setting up consolidation rules Category Financial Consolidation Hub assigns a consolidation rule to a category This determines when to run the rule and where to write the results The application runs consolidation...

Page 68: ...solidation rule contains one or more consolidation rule steps A consolidation rule step comprises a formula and a formula scope Note The sequencing of rule steps does not impact processing Formulas A formula is a combination of tokens and mathematical operators used to calculate the balance of the consolidation entry Tokens are variables whose values are resolved during consolidation processing ba...

Page 69: ...ch dimension the user specifies a specific value otherwise the system uses the same value as the source account Offset You can specify an offset account if you did not specify the same as source option for any target values If you specify an offset account then an offset balance is written to the offset account to balance any consolidation entries created by the rule step Balancing a Consolidation...

Page 70: ... 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 The figure below illustrates the hierarchy for the examples The examples focus on Vision Consolidation Subsidiary and its subsidiary Japan APAC and Subsi...

Page 71: ...ation 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 Assume that the minority interest consolidation rule is in the minority interest category that enables you to track each child separately functionality and writes to the elimination entity The fol...

Page 72: ...ltiplied by those balances The consolidation entry is written to the specified account combination in the elimination entity and an offset entry is written to balance the consolidation entry The company cost center organizations 11 100 and 11 200 are assigned to Japan and 11 100 is its base organization APAC HQ the controlling entity of APAC and Subsidiaries has the base organization 12 100 Japan ...

Page 73: ...lidation rule produces a consolidation entry with Line Item 3650 Minority Interest B S for the sources whose value is 0 2 500 300 40 Additionally the consolidation rule produces an offset entry with Line Item 5345 Minority Interest P L to balance the consolidation entry The company cost center organization of the entry is set to the base organization of the source entity 11 100 If Track Each Child...

Page 74: ...g consolidation rule is in the equity accounting category that enables you to track each child separately functionality and writes to the elimination entity The consolidation rule will have three steps Step 1 Eliminate Investment in Subsidiary For simplicity assume that APAC and Subsidiaries paid the market value for Japan Therefore its investment in Japan is equal to Japan s equity times its owne...

Page 75: ...wned 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 ...

Page 76: ... Interest Ownership multiplied by those balances The consolidation entry is written to the line item 3650 Minority Interest B S in the elimination entity The company cost center organizations 11 100 and 11 200 are assigned to Japan and 13 100 is its base organization APAC HQ has the company cost center organizations 12 100 and 12 200 and has the base organization 12 100 APAC HQ has the balances in...

Page 77: ... 11 100 5000 Expense 300 During consolidation the consolidation rule will produce a consolidation entry in each step Step 1 Eliminate Investment in Subsidiary Company Cost Center Organization Line Item Balance 11 100 1820 Investment in Subsidiaries 400 The step identifies the balance in Japan with line item 3000 Equity as the source balance It produces a consolidation entry with Line Item 1820 Inv...

Page 78: ...of the source entity 11 100 Step 3 Add Back Minority Equity Company Cost Center Organization Line Item Balance 11 100 3650 Minority Interest B S 100 The step identifies the balance in Japan with line item 3000 Equity as the source balance It produces a consolidation entry with Line Item 3650 Minority Interest B S whose value is 0 2 500 100 The company cost center organization of the entry is set t...

Page 79: ...h and eliminate Accounts to use when creating suspense entries for unbalanced eliminations A suspense threshold tolerance for unbalanced eliminations An intercompany rule applies to all the consolidation hierarchies defined for your instance This eliminates the need to repeatedly define and assign identical intercompany elimination rules to multiple hierarchies If an entity records intercompany ba...

Page 80: ...ve a path to a common parent entity that does not include a consolidation type of None Intracompany Elimination Entry Intracompany elimination entries offset balances occurring between different business units or companies within the same operating entity To eliminate a balance under intracompany processing entries must be the following A line item that is in an enabled intercompany rule From the ...

Page 81: ...n run consolidations on actuals and averages In addition you can also run consolidations on any additional data type defined During consolidation intercompany eliminations may or may not be executed depending on the definition of the data type See Task 4 Data Types page 5 6 Example of How an Intercompany Rule Works The following example illustrates how intercompany rules are applied during a conso...

Page 82: ...sactions 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 Assume the hierarchy option Match Intercompany by is Company Also assume the intracompany option Identify Balances When ...

Page 83: ...nies 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 Submitted Balances Balance Organization Line Item Intercompany Debits Credits S1 3 100 1100 Cash and Cash Equivalents 9 100 2350 S2 3 100 2200 Accounts Payable 00 100 S3 3 100 2200 Accounts Pay...

Page 84: ...ady in USD UK s balances are converted to USD As specified before the conversion rate is 2 Balance Organization Line Item Intercompan y Debits Credits U1 9 100 2200 Accounts Payable 00 2200 U2 9 100 1200 Accounts Receivable 3 100 1800 U3 9 100 1200 Accounts Receivable 4 100 400 Intercompany eliminations will only be performed for balances whose line items are 1200 Accounts Receivable 2200 Accounts...

Page 85: ... Item Intercompany Debits Credits 4 100 2200 Accounts Payable 9 100 450 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 Match...

Page 86: ...on Intercompany entry Organizations 3 200 and 9 100 Organization Line Item Intercompany Debits Credits 3 200 2210 Accounts Payable Discounts 9 100 1500 3 200 3201 AR AP Suspense 9 100 1500 A suspense of 1500 USD is written to the organization of the receivables entity The suspense is above the threshold value and will cause a warning during consolidation Note An Intercompany elimination is perform...

Page 87: ...ts 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 ...

Page 88: ......

Page 89: ...endar and a target calendar A period represents a range of time such as month quarter or year A calendar map is defined for the first year and then used for all subsequent years You can map multiple source periods into the same target period You define a calendar map for the first year and Financial Consolidation Hub extrapolates the appropriate mapping for subsequent years Mapping Example Suppose...

Page 90: ...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 March 31 2004 to March 31 2004 The accounting calendar Accounting has the following format for 2004 Each year follows the same pattern Period Name Data Range Jan 04 January 1 2004 to January 31 2004 Feb 04 Februa...

Page 91: ...vember 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 For example Apr 04 in the fiscal calendar and Apr 03 in the accounting calendar both represent the date range Apr 01 2003 to Apr 30 2003 Also notice that the fiscal calendar periods Mar 04 and Adj 04 the...

Page 92: ......

Page 93: ...ion method You can define as many translation methods as needed due to differences in translation practices across different industries countries or ownership relationships Note Financial Consolidation Hub translation methods address both translation and remeasurement in accordance with FAS 52 and IAS 21 Translation Method A translation method is a collection of settings that determines the curren...

Page 94: ...s Common examples of rate types are Corporate and Spot though you can define additional rate types as needed You should have rate types which represent the ending and average rates needed for translation See Currency Rates Manager Oracle General Ledger User Guide Equity Mode You can specify whether you want Financial Consolidation Hub to use the Year to Date YTD balances or period activity PTD to ...

Page 95: ...tement account Financial Consolidation Hub translates that account using the historical rate or amount Historical rates or amounts thus override ending and average rates Use as Default You can designate a translation method as the default by selecting an option from the Use as Default drop down list in the Create Translation Method page When you construct a consolidation hierarchy the default tran...

Page 96: ...ne ending or average rates select Daily Rates from the menu to open the Daily Rates page See Currency Rates Manager Oracle General Ledger User Guide Related Topics Historical Rates page 12 4 Historical Rates Historical rates or historical amounts apply to a specific account and if they exist are automatically used by Financial Consolidation Hub instead of the ending or average rate The historical ...

Page 97: ...sion members enter either a historical rate or amount Historical rates are multiplied by the base currency balances to calculate translated balances Historical amounts are not multiplied they are used as is Rate Type defaults to Historical and you should not change this when you are entering historical rates Note If you enter in a historical amount the amount will be considered a credit not a debi...

Page 98: ...for January 2005 Vision Canada s Trial Balance for January 2005 Entity Line Item Dr CAD Cr CAD Vision Canada Cash 1000 400 Vision Canada Accounts Receivable 1200 500 Vision Canada Accounts Payable 500 700 Vision Canada Paid in Capital 0 400 Vision Canada Retained Earnings 0 300 Vision Canada Revenue 0 2000 Vision Canada Cost of Goods Sold 1100 0 Vision Canada Salaries 400 0 The ending rate in Janu...

Page 99: ...on Canada Salaries 400 0 0 7 Since the balance sheet was multiplied by the ending rate and the income statement by the average rate and equities by an historical rate the ending USD balances require a credit of 58 posted to Cumulative Translation Adjustment to ensure proper balancing as shown in the following table Translated Results for Vision Canada Entity Line Item Dr CAD Cr CAD Rate Dr USD Cr ...

Page 100: ...justment 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 Vision Canada Trial Balance for February Entity Line Item PTD Dr CAD PTD Cr CAD YTD Dr CAD YTD Cr CAD Vision Canada Cash 0 0 1000 400 Vision Canada Accounts Receivable 200 0 1400 500 Vision Canada Accounts Payable 0 0 500 7...

Page 101: ...e rates have The following table shows the translation rates for February Translation Rates for February Entity Line Item PTD Dr CAD PTD Cr CAD YTD Dr CAD YTD Cr CAD Rate Vision Canada Cash 0 0 1000 400 0 81 Vision Canada Accounts Receivable 200 0 1400 500 0 81 Vision Canada Accounts Payable 0 0 500 700 0 81 Vision Canada Paid in Capital 0 0 0 400 0 84 Vision Canada Retained Earnings 0 0 0 300 0 8...

Page 102: ... 1134 405 Vision Canada Accounts Payable 0 0 500 700 0 81 405 567 Vision Canada Paid in Capital 0 0 0 400 0 84 0 336 Vision Canada Retained Earnings 0 0 0 300 0 84 0 252 Vision Canada Revenue 50 400 50 2400 0 72 36 288 Vision Canada Cost of Goods Sold 100 0 1200 0 0 72 72 0 Vision Canada Salaries 50 0 450 0 0 72 36 0 Financial Consolidation Hub then calculates the PTD debits and credits for balanc...

Page 103: ... 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 84 0 0 0 252 Vision Canada Revenue 50 400 50 2400 0 72 36 288 36 1688 Vision Canada Cost of Goods Sold 100 0 1200 0 0 72 72 0 842 0 Vision Canada Salaries 50 0 450 0 0 72 36 0 316 0 Again since different rates are used for different accounts there...

Page 104: ... 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 84 0 0 0 336 Vision Canad a Retained Earnings 0 0 0 300 0 84 0 0 0 252 Vision Canad a Revenue 50 400 50 2400 0 72 36 288 36 1688 Vision Canad a Cost of Goods Sold 100 0 1200 0 0 72 72 0 842 0 Vision Canad...

Page 105: ...tting Up Translation 12 13 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 Cumulat ive Translati on Adjustm ent N A 0 29 29 0 ...

Page 106: ......

Page 107: ...which the entity is used Consolidation Hierarchy Structure A consolidation hierarchy structure comprises consolidation entities elimination entities and operating entities Each entity other than the top entity has relationship attributes that define the nature of its association with its parent or parents Consolidation Hierarchy in Enterprise Performance Foundation When you create a consolidation ...

Page 108: ...on results into Analytical Reporting select the data set Hierarchy Name Actual If you will be reporting on average balances also select the data set Hierarchy Name Average In addition two data sets are created for each additional data type that you have created Hierarchy Name Data Type Name Holds the consolidated results for the consolidation hierarchy and data type Number Data Type Name Used for ...

Page 109: ...tire hierarchy Defining consolidation hierarchy attributes includes specifying the following General attributes Security options Intercompany options Balancing options Retained earnings Suspense handling Note To view the retained earnings and suspense handling options for an existing hierarchy view the hierarchy and click Details for the topmost entity General Attributes In the top region of the a...

Page 110: ...rarchy Secure by Role Select No to allow all users access to the consolidation hierarchy Select Yes to specify which users have access to the hierarchy See Securing Consolidation Hierarchies page 21 2 Intercompany Options In the Intercompany Options region of the attributes page specify how intercompany balances are identified and matched Match Intercompany By Select to match intercompany by Compa...

Page 111: ...y organization Balance by an Additional Dimension Select a dimension to balance by that dimension Note Balancing options do not apply to intracompany and intercompany elimination entries since they balance by entity only Note If you are consolidating a user defined data type balancing may or may not be enforced depending on the definition of the data type See Task 3 Data Types page 5 6 Retained Ea...

Page 112: ... method Translation method When you are creating or duplicating a hierarchy you can define the consolidation hierarchy structure manually in the user interface or by uploading the information from a spreadsheet Ownership Percentage The ownership percentage specifies how much the entity is owned by its parent The ownership percentage cannot exceed 100 In the case of an entity having multiple parent...

Page 113: ...olidation Hierarchy Define Structure page Warning The existing structure for the hierarchy you are creating will be replaced by a spreadsheet upload After you upload a hierarchy the hierarchy structure is validated before it is accepted and made available If there are any errors an error message is displayed and the entire structure is not accepted You can view the upload errors by downloading the...

Page 114: ...ion to create an acquisition or disposal accounting entry to associate with the change Remove an entity To create an accounting entry select Yes for Create Accounting and specify a To Consolidation Method Add an entity To create an accounting entry select Yes for Create Accounting and specify a From Consolidation Method Update an entity To create an accounting entry select in the Create Accounting...

Page 115: ...e other Therefore you can create as many duplicate hierarchies as you need and modify them in different ways to reflect various what if scenarios Restatements are an example of when duplicate hierarchies may be useful For example you may need to restate financials after a significant year end merger to provide a view of consolidated results as if the two companies operated as a single enterprise f...

Page 116: ...on entry is written based on the consolidation rule and the additional ownership in the currency of the consolidation entity Note Multiple Parent reprocessing only affects consolidation rules which use the OWN and MI tokens Multiple Parents Example In the following hierarchy Mexico has multiple parents Americas Group North Americas Group South Americas Group and Colombia Assume the consolidation m...

Page 117: ...Revenue 10000 15 100 1100 Cash 10000 First Mexico is consolidated to North Americas Group Its balances are converted to US dollars Organization Line Item Debits Credits 15 100 4000 Revenue 900 15 100 1100 Cash 900 The minority interest consolidation rule produces the following entry in US dollars Organization Line Item Debits Credits 15 100 3650 Minority Interest B S 270 15 100 5450 Minority Inter...

Page 118: ...hip times the trial balance 0 10 900 90 This balance is added back Americas Group South Americas Group Mexico Americas Group owns 60 of South Americas Group which owns 7 of Mexico Thus Americas Group owns 60 7 4 2 of Mexico through this branch The minority interest consolidation rule will produce the following consolidation entry in the currency of Americas Group US dollars Organization Line Item ...

Page 119: ...n Line Item Debits Credits 15 100 3650 Minority Interest B S 43 2 15 100 5450 Minority Interest P L 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 ...

Page 120: ......

Page 121: ...e format you use in Financial Consolidation Hub Open Interface Tables You can push data from your source system into open interface tables and then push the data into Financial Consolidation Hub This chapter will cover Oracle and external sources See Open Interface Tables page B 1 Additional Data Types If you have additional data types assigned to the entity you can run data submission for those d...

Page 122: ...plies only to data submissions that source from Oracle General Ledger The status is impacted if the balances in Oracle General Ledger have changed after the data submission was performed Completed The data submission completed successfully The balances have been loaded into Financial Consolidation Hub Warning The data submission completed but there is a warning The balances may or may not have bee...

Page 123: ...ce Click Acrobat Excel icons to view the Data Submission Trial Balance Report The trial balance is the accumulation of all the data submission performed for that entity period currency and balance type In addition you can click on the eyeglasses icon to go to a searchable online interface See Data Submission Trial Balance Report page 18 4 Regenerate XML In the event that a Data Submission Trial Ba...

Page 124: ...presents the recommended setup of balances rules for Financial Consolidation Hub Note You may run the balances rule in either the Enterprise Performance Foundation user interface or the Financial Consolidation Hub Data Submission user interface Running the rule will cause the results to show up in both user interfaces However rules run in the Enterprise Performance Foundation user interface that r...

Page 125: ...ion for a given period The snapshot mode is intended for one data submission for a given period and currency See Snapshot Mode page 14 7 See Incremental Mode page 14 7 Include Balancing Segment Values Include all of the balancing segment values that you want to consolidate Budgets If you selected Budget for Balance Type specify which budgets in Oracle General Ledger you wish to bring over When you...

Page 126: ...encumbrances do not have balances for a period the Financial Consolidation Hub data submission will result in a warning Otherwise you can create multiple balances rules for each encumbrance Currency Type Select the type of balances to synchronize Cumulative Functional The total balance for the account summed across all currencies represented in the functional currency Entered and Functional Equiva...

Page 127: ...a warning or error will not show up in the Financial Consolidation Hub user interface Note Users running data submission for Oracle sources must be added to the Default and Data Integration folders Snapshot Mode Note It is recommended that you not use the snapshot mode for data submissions When you perform a data submission for an entity whose balances rule uses snapshot mode data submissions for ...

Page 128: ...anges To undo data navigate to the Enterprise Performance Administrator responsibility Process Management Executed Rules Search for the balances rule select it and click Undo You can track the status of the undone Data Submission in the Data Submission user interface Oracle Source Example Entities with Oracle sources can source from any combination of balancing segment values in a ledger in Oracle...

Page 129: ...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 Additionally you assign the incremental balances rule to both entities Suppose you then want to synchronize the balances for Corporation and Distribution for the first time in the month of February Through the Data Submiss...

Page 130: ...and validation rule sets to entities See Transformation Rule Set page 6 6 See Validation Rule Sets page 14 10 Transformation Rule Sets You can use a transformation rule set to convert submitted data to a standardized chart of accounts See Setting Up Interface Data Transformer Oracle General Ledger User Guide Before you can use a lookup table or PL SQL function for data transformation you must crea...

Page 131: ...ta for External Sources For entities with external sources you can submit data for a given entity period currency and balance type You submit data for external entities using a spreadsheet loader which can be accessed from the Data Submission page Each data submission is for a period entity currency and balance type and you can perform multiple data submissions for the same parameters Each externa...

Page 132: ...ur consolidation dimensions and you therefore do not need to apply transformation rules Before the spreadsheet is uploaded the system verifies that each dimension member is valid and that the spreadsheet is balanced Note Input the dimension member names in the spreadsheet Note Double click a cell to see a list of valid values Pre Transformation Spreadsheet Format The pre transformation spreadsheet...

Page 133: ...ct Net In this case Financial Consolidation Hub expects debit balances to be entered as positive numbers and credit balances to be entered as negative Currency Type If you select the Functional Currency Only option you must specify a functional currency in the Currency field In the spreadsheet loader fill in the trial balances in that currency If you select the Transactional Currency option in the...

Page 134: ...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 Add Comment Before submitting data you can enter applicable comments in the Submit Data Parameters page After submitting data entered comments appear on the Data Submission Status page ...

Page 135: ... trial balance loads by the subsidiaries as well as the consolidation process status Data Status The data status is updated automatically whenever a subsidiary loads data or a structural change is made to the consolidation hierarchy The data status can be the following Not Started Applies when none of the subsidiaries have loaded their trial balance Incomplete Applies when some but not all of the ...

Page 136: ...es there are missing setup components such as missing rates or a suspense threshold violation when performing intercompany eliminations Error occurs when a key setup component of Financial Consolidation Hub or Enterprise Performance Foundation has not been set properly The Impacted status enables the consolidation manager to quickly see the changes that have occurred that impacts the most recent s...

Page 137: ...nsolidation Hub Each category has a status associated with it as follows N A Consolidation entries are not generated by the category Not Started The category was not processed by a consolidation run In Progress The category is currently being processed by a consolidation run Completed The category was successfully processed by a consolidation run Warning The category was processed by a consolidati...

Page 138: ... If the consolidation process completes with no warnings or errors then the results may be locked This prevents any re runs of the consolidation If you need to reconsolidate locked results you must unlock them first Unlocking results is a function that can be secured to a select group of users Process History Consolidation is typically an iterative process You can run consolidations every day or m...

Page 139: ... business process to run by procedural call see Schedule page 19 9 The concurrent program FCH Submit Consolidation enables you to schedule consolidation runs for a specific hierarchy business process balance type and period combination The parameters for the concurrent program are identical to the online interface When you run consolidation it launches the following concurrent requests You can mon...

Page 140: ......

Page 141: ...to eliminate intercompany profit or loss in inventories between two subsidiaries The elimination needs to reflect inventory not yet sold to third parties The subsidiary s general ledger systems do not capture this information Therefore an intercompany rule cannot be executed during consolidation processing to produce the required eliminations Consequently you enter an adjustment to eliminate an es...

Page 142: ...djustments into Financial Consolidation Hub at the same time through a zip file of XML files Financial Consolidation Hub will perform the same validation it does for uploading adjustments via WebADI and each adjustment will be tracked on the Adjustment Main Page user interface just like WebADI adjustments 1 Navigate to the Consolidation Adjustment page 2 Click Upload via XML To view the XML format...

Page 143: ...ry_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_account_id Natural Account name product_id Product name project_id Project name task_id Task name user_dim1_id User Dimension 1 name user_dim2_id User ...

Page 144: ...top or by specifying a URL accessible from the Web Server supporting your E Business Suite installation Note The file you upload must be a zip file containing one or many XML files Each XML file corresponds to an adjustment 4 Click Process to run the FCH XML Uploader concurrent program FCH will perform the same validations on each adjustment as it does for WebADI adjustments Any invalid adjustment...

Page 145: ...able it Selected User Interface Elements Adjustments are entered using a WebADI spreadsheet loader Selected user interface elements for adjustments are described as follows Line Description When you are entering balances into the adjustment spreadsheet you may optionally add a description for each row Hierarchy Adjustments are hierarchy specific and are not propagated across hierarchies Category A...

Page 146: ...ry adjustment in Financial Consolidation Hub It will only be incorporated in the next consolidation run If you run consolidation a second time the adjustment will no longer show up Write back to General Ledger When you upload an adjustment into Financial Consolidation Hub you are given the option to have the adjustment be written back to General Ledger The adjustment will be written as a journal w...

Page 147: ...types in Oracle General Ledger except User The last day of the write back period is used as the rate date Note If the adjustment is in the same currency as the Target Ledger functional currency Financial Consolidation Hub will ignore the conversion rate type you select Running Write back If you enable write back it will be performed when you execute the adjustment upload An additional concurrent r...

Page 148: ...ou 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 Updating Write back Adjustment If you update an adjustment that was submitted for write back and enable the updated journal for write back Financial Consolidation Hub will first write back a reversal entry to eliminat...

Page 149: ...isition and Disposal entries are generated in the Acquisition and Disposal category Acquisition and Disposal is broken up into four categories or types Acquisition Disposal Share Issue Share Issue with Acquisition An acquisition and disposal transaction has the following parameters From Consolidation Method The original consolidation method applied to the relationship To Consolidation Method The n...

Page 150: ...rm more than one change to the same hierarchy relationship on the same effective date only the last change will be available to associate Acquisition and Disposal entries See Updating Consolidation Hierarchies page 13 7 Add Entity If you add an entity to a consolidation hierarchy enable Acquisition and Disposal entries by setting Create Accounting to Yes The Acquisition and Disposal properties wil...

Page 151: ...nge To Consolidation Method The consolidation method that you specify for the entity and the one that is visible when you view the consolidation hierarchy for an effective date after the change If you do not change the consolidation method the From Consolidation Method and To Consolidation Method will be the same Change Type Specify the change type in the Create Accounting list For the change type...

Page 152: ...oader When you upload the spreadsheet it verifies that the accounts are valid and that the entry is balanced Note If you generate an Acquisition and Disposal entry through a calculation you can manually update the entry When you generate an entry through a calculation it will erase any existing entry See WebADI Spreadsheets page A 1 Calculating Acquisition and Disposal Entries You can have Financi...

Page 153: ...eet loader When the spreadsheet is uploaded it verifies that the account values are valid Note The subsidiary trial balance does not need to be balanced This allows you to load only the balances relevant to acquisition and disposal Acquisition and Disposal Rules Acquisition and Disposal Rules are used to automatically create the accounting associated with changes in your legal structure During con...

Page 154: ...n both the categories Share Issue and Acquisition Linked to a Share Issue is executed To Method Select a consolidation method The To Method of the rule must match the To Method of the Acquisition and Disposal transaction for the rule to be executed for that transaction From Method Select a consolidation method The From Method of the rule must match the To Method of the Acquisition and Disposal tra...

Page 155: ...e 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 The tokens PRETB and POSTB are for use in rules in the Acquisition Linked to a Share Issue category The token ADTB is for use ...

Page 156: ......

Page 157: ...d currency If the entity sources from Oracle General Ledger you can drill into Oracle General Ledger to the subledger level For the specific conditions under which drilldown is enabled in reports refer to the following sections on each report Consolidation Trial Balance Report The Consolidation Trial Balance Report shows the consolidation results of a consolidation run for a consolidation entity b...

Page 158: ...idation Entries Report has the following parameters Consolidation Hierarchy The hierarchy for which you run the consolidation Period The period for which you run the consolidation Consolidation Entity A consolidation entity in the selected consolidation hierarchy Category The category of the consolidation entry that you want to view Entity The entity for which the consolidation entry was produced ...

Page 159: ...parameters Consolidation Hierarchy The hierarchy for which you run the consolidation Period The period for which you run the consolidation Entity An operating entity in the selected consolidation hierarchy The report will show the intercompany eliminates between the selected entity and the selected counter entity Counter Entity You can select a counter entity to only list intercompany eliminations...

Page 160: ...eport in the Data Submission User Interface For more information on accessing this report View Trial Balance page 14 3 You can customize the report by modifying the XML Publisher template See Task 11 XML Publisher Templates page 5 15 In the Data Submission Trial Balance Report you can drill down on balances for data submissions that source from Oracle General Ledger Note The Data Submission Trial ...

Page 161: ...he Data Submission Impacted Balances Report has the following parameters Entity An operating entity for which data was submitted Load Identifier A unique string of characters used to identify the data submission Currency The currency of the data submission 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...

Page 162: ... consolidation Consolidation Entity A consolidation entity in the selected consolidation hierarchy Entity An operating entity in the selected consolidation hierarchy that is a descendent by the selected consolidation entity Period The period for which you run the consolidation You can access this report in the Consolidation Monitor You can also run the report using the concurrent program FCH Value...

Page 163: ...nt balances into the functional currency of the target ledger The rate type may be blank if no conversion is necessary Target Category The category in Oracle General Ledger to which the adjustment is written back Currency The functional currency of the target ledger The adjustment balances are automatically converted to this currency You can access this report in the Adjustment Writeback Details u...

Page 164: ......

Page 165: ...User s Guide Setting Up Analytical Reporting Note The following setup steps are recommended for consolidation data only If you are using analytical reporting for other applications please see the appropriate user guides To set up analytical reporting perform the following procedures Initial Setup Before you can begin to set up analytical reporting you must first perform the following tasks 1 Compl...

Page 166: ...arate dimensions For more information on selecting the Intercompany dimension as Used by Reporting seeTask 1 Processing Keys page 5 4 Using the Enterprise Performance Foundation Administrator responsibility navigate to Configuration Rules Functional Dimension Definition To create a functional dimension rule Note These steps assume that your organization dimension is Company Cost Center Organizatio...

Page 167: ...olumn Name Intercompany When you define a business area assign this functional dimension definition rule to include the Intercompany dimension for that business area Set Up Business Area The business area pulls in the Ledger setup data from Oracle Enterprise Performance Foundation into Oracle Enterprise Planning and Budgeting One business area represents one chart of accounts Since Financial Conso...

Page 168: ...ierarchy in Enterprise Performance Foundation page 13 1 Dimensions Add all of the dimensions that you selected to be Used by Reporting in Foundation Task 1 Processing Keys If you do not want to report on one of the reporting dimensions you can remove it in the business process Note You do not need to select the dataset dimension It is automatically included and handled under the Data Sets tab You ...

Page 169: ...erarchy data type but a name of number data type These data sets are used for backend processing and should not be selected Delete a Business Area To delete a business area use the Enterprise Planning and Budgeting Controller responsibility Business Area tab Select the business area and click delete The business area and all related business processes and reports can be deleted Run Business Area A...

Page 170: ...y and reenter it 1 Select the dimensions by which you want to enable security You will be able to restrict information to users according to this dimension Typically this is the entity dimension Click Apply and Enterprise Planning and Budgeting will run the concurrent request ZPB SPL write back ZPB SPL Writeback Monitor the concurrent request and wait for it to finish 2 Exit the Enterprise Plannin...

Page 171: ... Optimize Storage for Sparse Data Leave this option selected unless your data is dense over the Line Item dimension Dimensions Time Dimension Select the period range for which you want to pull balances Additional Dimensions Select the dimensions that you want to report on You will be able to select any dimension you had added to the Business Area under the Dimensions tab See Dimensions page 19 4 N...

Page 172: ...erprise Planning and Budgeting from aggregating along the entity dimension Aggregation has already occurred during consolidation processing 1 For a line item which has a source of Loaded click update 2 Click Next 3 Update Solve Dimension Handling For the Entity Dimension select Exclude From Solve Click Next 4 Update Solve Input and Output Selections For the Entity dimension select Use Input as Out...

Page 173: ...un just once You can also set a business process to be run by procedural call which allows it to be automatically run after you run consolidation See Submitting Consolidations page 15 4 Note If you set a business process to be run by procedural call you will need to make it effective before it can be automatically run after consolidation 2 Number of Process Run Stored Each time you run the busines...

Page 174: ...ew you will have to first exit the Enterprise Planning and Budgeting Controller responsibility then reenter it Note If the business process does not launch the concurrent requests automatically use the System Administrator responsibility and run the concurrent request Workflow Background Process with the parameters Process Deferred and Process Timeout set to Yes Note If you update a business proce...

Page 175: ...ting use a different methodology to make report definition easier and more compliant with published financial statements No negative sign is used unless the balance is opposite what would be normally expected For example a liability balance in FCH of 100 would show up as a balance of 100 in Analytical Reporting The signage is converted to the Analytical Report format when the consolidation results...

Page 176: ...ions If you select values in each dimension and want to reuse that grouping of values you can save it as a saved selection First select the grouping as outlined in Select Values in Each Dimension Click Save As for the dimension Layout Determine which dimensions show up in the rows and columns of the chart region of the report All remaining dimensions can either be a page level item or a hidden ite...

Page 177: ...curity for reports and folders You can control which users can view or overwrite reports and which ones can change the security settings To set the security privileges for a report or folder use the Enterprise Planning and Budgeting Controller responsibility and navigate to Documents Reports Find the report or folder in the online folder system and click Properties Then expand the Privileges regio...

Page 178: ......

Page 179: ... methodology to make report definition easier and more compliant with published financial statements No negative sign is used unless the balance is opposite what would be normally expected For example a liability balance in Financial Consolidation Hub of 100 would show up as a balance of 100 in Analytical Reporting The signage is converted to the Analytical Report format when the consolidation res...

Page 180: ...he same login and security model as the online Analytical Reporting Use the same user name and password that you use for Financial Consolidation Hub and Analytical Reporting and you will have access to the same reports you would in the Enterprise Planning and Budgeting user interface After you log in you are prompted to select a business area Once you select a business area you can work with repor...

Page 181: ...tical reports include both Analytical report modifications and Excel modifications You can create open and modify Formatted Analytical reports in the Excel Add in only These reports cannot be opened or created in the Enterprise Planning and Budgeting user interface However an Analytical Report can be modified and saved as a Formatted Analytical report Note When you save a Formatted Analytical repo...

Page 182: ...ermines the formatting of the report You can modify an existing template or create new ones to incorporate different formatting such as fonts colors and images The Excel Add in comes with the default template OracleTemplate xls 3 Click Open Reports On Computer The Excel Add in allows you to save reports to your computer To save the report to your computer save the Excel spreadsheet using the Save ...

Page 183: ...drop to reorder them Click OK Once a report is created you can save it to the Enterprise Planning and Budgeting online folder system Note You must save a created report to the Enterprise Planning and Budgeting online folder system in order to access it later Note You can only connect one report to the environment at a time If you have a report connected to the environment then create or open anoth...

Page 184: ... Selection See Creating Saved Selections page 20 8 Note The Enterprise Planning and Budgeting user interface allows you to select members for a dimension without saving it as a saved selection The Excel Add in however does not allow you to do this Layout To change the layout of the report select the Report Layout function under the Consolidation menu Drag and drop which dimensions will show up in ...

Page 185: ... The balance corresponds to detail non parent values for all dimensions in the rows and columns of the report If the entity dimension is in the rows or columns the balance must correspond to an operating entity If the calendar is in the rows or columns the balance must correspond to a detail value Note This feature is specific to the Excel Add in and does not exist in the Enterprise Planning and B...

Page 186: ...mension members that you want to include in the saved selection You can perform the following functions Search for values You can search for available values within the dimension hierarchy Select a search result to see its location in the hierarchy Add values To add a value to the saved selection drag and drop it from the Available region to a location in the dimension hierarchy in the Selected re...

Page 187: ...zes the Analytical Reporting security model Any report or saved selection that you have access to in the Enterprise Planning and Budgeting user interface the same access is also applicable to you in the Excel Add in as well When you save a report or saved selection your user is given full access During the save process you can set the security access by clicking the Security button You can grant e...

Page 188: ...o 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 DEBUG will provide a level of detail between ERROR and STATEMENT Trace File Select the location and name of the...

Page 189: ...nsolidation process Financial Consolidation Hub enables you to secure data using either or both of the following security methods Role based security Function based security Security for Enterprise Planning and Budgeting EPB reporting is not discussed in this chapter Financial Consolidation Hub security does not transfer to EPB For information on security for EPB reporting see Administering Securi...

Page 190: ... you to grant the privileges of viewing and updating entity attributes Securing Consolidation Hierarchies Securing consolidation hierarchies enables you to grant the privileges of viewing modifying and running consolidations to a specific set of users for that hierarchy Securing Consolidation Entities Securing consolidation entities enables you to grant the privilege of viewing consolidation resul...

Page 191: ... Corporation then he can load data and enter operating adjustments for Corporation only Scenario 3 If Joe Smith has access to consolidation entity Corporation and Subsidiaries and to consolidation entity EMEA and Subsidiaries he has access to the branch under Corporation and Subsidiaries as well as to the branch under EMEA and Subsidiaries Scenario 4 If Joe Smith has access to consolidation entity...

Page 192: ...heir associated functions Seeded Responsibilities with Associated Functions Seeded Responsibility Functions Entity Controller submits data for operating entities Consolidation Manager creates and updates all setup elements submits data for operating entities submits consolidations views consolidation reports Note For information on responsibilities relating to analysis of consolidation results see...

Page 193: ...atements The integration between Internal Controls Manager and Financial Consolidation Hub allows companies to review and assess the adequacy of controls in their business processes For more information on Internal Controls Manager see the Oracle Internal Controls Manager Implementation Guide Setup for Integration with Internal Controls Manager The integration between Internal Controls Manger and ...

Page 194: ...rforms certification by a line item hierarchy and or organization hierarchy This step determines the line item hierarchy 2 Using the System Administrator responsibility navigate to Profile System set the following profile options Profile Value AMW Default Calendar The consolidation calendar of the consolidation hierarchy you want to certify with Internal Controls Manager AMW Subsidiary Value Set f...

Page 195: ...run the Import Natural Accounts concurrent request Once a hierarchy has been enabled for certification additional certification fields will show up in the consolidation monitor for that hierarchy See Monitoring Financial Statement Certification Status page 22 4 See Monitoring Organization Certification Status page 22 4 Creating Business Process and Financial Statement By performing the previous ta...

Page 196: ...tab of the underlying financial statement certification in Internal Controls Manager 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 Monitoring Organization Certification Status The consolidation monitor provides you an alternative view of the certification by organizat...

Page 197: ...rcentages 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 Finance has two processes associated with it one of which has been certified with no issues Human Resource has three processes associated with it two of which have been certified with no ...

Page 198: ......

Page 199: ...ncial Consolidation Hub uses WebADI spreadsheets Financial Consolidation Hub uses WebADI spreadsheet loaders for the following tasks Create and Update Dimension Members Create and Update Dimension Hierarchies Create and Update Value Set Maps Create and Update Historical Rates Create and Update Line Item Intercompany Maps Create and Update Adjustments Create and Update Acquisition and Disposal Entr...

Page 200: ...tically downloaded into your excel and a connection to your environment is established Furthermore instead of opening Excel you can choose to save an excel file to your computer You can then open the file at your leisure with the same effect Note When you open a WebADI spreadsheet Excel may prompt you that the file contains macros If this happens click Enable Macros Note It is recommended that you...

Page 201: ...ck the upload process Uploading WebADI Spreadsheets Selected User Interface Elements Rows to Upload You can choose to upload all rows that exist in the spreadsheet or to upload only rows that are flagged in the Upl column Duplicate Resolution Profile The WebADI spreadsheet does not allow you to upload two identical rows Note In the case of uploading balances it ignores the balance amount and does ...

Page 202: ... value from a list of values and there is no dropdown box you can see a list of valid values by highlighting the cell then clicking List of Values from the Oracle menu The list of values is pulled directly from the environment Upload From the Oracle menu select Upload to upload all of the data you have entered See Uploading WebADI Spreadsheets page A 2 Monitor From the Oracle menu select Monitor t...

Page 203: ...uest window See Manually Entering Acquisition and Disposal Entries page 17 4 Acquisition and Disposal Trial Balance Import The A D Trial Balance Import program is called automatically when a trial balance is uploaded for an A D transaction The concurrent program takes the data from the spreadsheet and calls the Rules Processor to generate an elimination entry You should not call this program manua...

Page 204: ...the appropriate data checks and calculations After completion of the workflow the core Foundation program is called to move the data from FEM_BAL_INTERFACE_T to FEM_BALANCES If the load is successful calls are made to check if any consolidation runs are impacted update the data status and run the XML Generator concurrent program If the entity is an Oracle entity the core Balances Rule Engine is ca...

Page 205: ...e Setting Up Consolidation Hierarchies page 13 2 Hierarchy Listing Report The Hierarchy Listing Report program is available to generate a Hierarchy Listing Report of a consolidation hierarchy as of a specific date It is called automatically when you click on the PDF or Excel icon for a hierarchy You can call this program manually using the Submit Request window See Hierarchy Listing Report page 18...

Page 206: ...undation is completed and again when the Analytical Reporting Task in Foundation is completed If you make any changes in a non standard manner this program must be called manually to regenerate the Financial Consolidation Hub packages and indices See Task 5 Consolidation Global Value Set Combination page 5 9 See Task 12 Analytical Reporting page 5 16 PDF Generation The PDF Generation program is ca...

Page 207: ...named Financial Consolidation Hub Operating Entity The Item key for each of the workflows is the entity name followed by the request id in parenthesis See Running Consolidations page 15 1 Update Entity Organizations The Update Entity Organizations program is called to assign the organization dimension members correctly to an entity Each entity is assigned company values in the entity user interfac...

Page 208: ...rogram 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 For more information on data submission see Submitting Data page 14 1 For more information on running c...

Page 209: ...is event to check if any consolidation runs are impacted by the new or incremental data load Consolidation Hierarchy Altered oracle apps gcs setup hierarchy update The Hierarchy Altered event is raised whenever the consolidation hierarchy is updated Financial Consolidation Hub ships a subscription to this event to check if any consolidation runs are impacted by an ownership consolidation method or...

Page 210: ...ns are impacted by the uploaded adjustment General Ledger Events Posting Completed oracle apps gl journals journal post General Ledger raises this business event whenever a journal has been posted Financial Consolidation Hub subscribes to this event to support the functionality to track whenever balances have changed in Oracle General Ledger that have not been synchronized with the Enterprise Perf...

Page 211: ...ust be to set to None in order to support the drilldown capabilities within Financial Consolidation Hub FND Validation Level This option must be to set to None in order to support the drilldown capabilities within Financial Consolidation Hub FND Debug Log Enabled If an error occurs in Financial Consolidation Hub and you need to send development the debug log you must set this profile option to Yes...

Page 212: ...tion user interface FEM FEM Top To run the Enterprise Performance Foundation Refresh Engine this profile must map to the location of the FEM_TOP The Refresh Engine will delete all user created data in both the Consolidation and Enterprise Performance Foundation Schemas Enterprise Planning and Budgeting Profile Options For more detailed information on Enterprise Planning and Budgeting profile optio...

Page 213: ...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 ...

Page 214: ......

Page 215: ... balancing consolidation rule 9 3 balancing options specifying 13 5 C calendar map example 11 1 overview 11 1 setting up 11 1 categories acquisition and disposals 7 3 aggregation 7 4 data preparation 7 2 definitions 7 2 intercompany 7 3 intracompany 7 2 minority interest 7 3 seeded 7 2 selected user interface elements 7 4 setting up 7 1 translation 7 3 user defined 7 2 condition rule 14 10 consoli...

Page 216: ...13 6 D data preparation seeded category 7 2 data status consolidation process 15 1 data submission for external entities 14 11 for operating entities 14 1 for Oracle entities 14 6 Monitoring 14 2 undo after data submission errors 14 7 using incremental mode 14 7 using snapshot mode 14 7 data submission for external entities selected user interface elements 14 13 setting up 14 10 data submission fo...

Page 217: ...with financial reporting requirements 1 1 provides a basis for Enterprise Wide Performance Management 1 2 quick reference 2 1 setting up checklist 3 1 financial reporting requirements 1 1 formulas consolidation rule step 9 2 formula scope consolidation rule step 9 3 G General Ledger Write back to GL 16 6 H hierarchy structures uploading 13 7 historical rates 12 4 defining 12 5 selected user interf...

Page 218: ...ta Submission Trial Balance 18 4 Hierarchy 18 5 Intercompany Matching 18 3 set map validation 18 6 Responsibility Consolidation Manager 4 1 Enterprise Performance Foundation Administrator 4 1 Enterprise Planning and Budgeting Controller 4 1 General Ledger Super User 4 1 Legal Entity Controller 4 1 System Administrator 4 1 XML Publisher Administrator 4 1 restatements and what if analysis duplicate ...

Page 219: ... 6 7 spreadsheet pre transformation format 14 12 standard format 14 12 spreadsheet loader for entering balances data 14 11 setting up entities 6 7 suspense handling intercompany rule 10 2 specifying 13 5 T tasks setting up Enterprise Performance Foundation 5 3 transferring data from Oracle General Ledger to Enterprise Performance Foundation 5 17 transferring data from Oracle General Ledger to Ente...

Page 220: ...dation 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 ...