Volume 8 -2, Mar-2004

By Tracy Wood,2014-04-18 17:47
11 views 0
[Chart: ABS Company Consolidation Structure]. Now let's discuss the steps to set up the consolidation. One ? Define the Parent or Consolidated Set of Books

     Tips and Techniques

    Volume 8 -2, Mar-2004 In this issue From the Editor

    Welcome to the year‟s first issue of this newsletter. From the Editor

    We were all pre-occupied during January and could not bring out the Functional scheduled first edition in time.

    All About Consolidations The response from readers is good this time, as you can see from the articles in

    this edition. I thank the contributors for sharing their experiences. I also New in Financials Family

    Pack E: FSG Report Name request all members to contribute regularly to this newsletter. I believe you Display in Concurrent will find the articles in this edition interesting and useful. Manager Satyakanth.


    How to set Org Context for Multi-Org environment

    Coordinator’s Corner How to get a full list of

    patches applied.

    How to get a base table or Volunteer Opportunities 2004 view from a screen where

    ? Speakers to give 35-40 minute main presentations. “Record History” is not

    available ? Facilitators to lead the informal breakout sessions. General Deadline for contributions to May newsletter April 30 Post Implementation Credit is given to all contributors.

    Productivity ? Submit in a Word document attached to an email. From OAUG ? Length 2 4 paragraphs. Maximum 300 words.

    Brenda Carlton, OAUG Business Atl-OAUG Coordinator Atl-OAUG Structure

    thMar. 19 Agenda

    Highlights: May 20th Agenda

    Coordinator’s Corner

    Atl-OAUG, tips and techniques Functional

    consolidated financial results of transactions that All About Consolidations originate in multiple sets of books? If the answer is

    yes, then Oracle‟s consolidation feature can Perhaps your company has shifted operations to

    significantly improve your ability to produce such another country or acquired a new subsidiary.

    results. The complexity of your consolidation Perhaps you want to bring your foreign operations

    structure is not a factor because you can set up into the same database. No business is immune to

    parent accounts and organize the values of your changes in the global marketplace. If you have

    balancing segment that represent your businesses to operations or at least significant financial

    mirror the legal and management structure of your transactions in foreign currencies it is most likely

    corporation. that you operate with multiple sets of books. It is

    even possible that you operate multiple sets of Consolidations in Oracle books within the same functional currency. If this is

    The remainder of this article will describe how to the case you should take advantage of the

    use Oracle‟s consolidation features. consolidation features of Oracle‟s General Ledger. If

    you‟re haven‟t done so yet, this article may help you. If you haven‟t already done so, diagram the It will explain the why‟s and how‟s of consolidations. structure of your consolidation including your

    elimination points. Do you have multiple The purpose of using Oracle consolidations is to

    consolidation levels? Are all consolidated results move financial results from one set of books to

    reported in the same currency? Some organizations another. The most common reason for setting up

    may need a consolidation of financial results in additional sets of books is to transact business in a

    Euros and in USD. Other important issues to different currency. Other reasons for setting up

    discuss before setting up any consolidations are: additional sets of books include using a different

    chart of accounts, operating with a different ? How frequently will you consolidate financial calendar, or using different document sequences for results? Some businesses may require a flash the same transaction source. report for sales or other critical operating

    barometers. From a business standpoint, consolidations occur

    with multiple businesses or legal entities that ? Will you consolidate budget amounts in operate under the same corporate umbrella. Many addition to actual results? corporations prepare consolidations and don‟t have

    any foreign operations. In such cases all the ? What level of detail do you need in the transactions occur in the same currency, consolidated set of books balances or

    nevertheless, a consolidation of financial results is transactions from the subsidiary sets of books? important. The best tool to prepare consolidations Most organizations consolidate balances and use in this case is the Financial Statement Generator. It account drilldowns to view source transactions. can prepare financial statements that display the ? When is the first period you want to consolidate individual businesses and then sum them up to financial results? The first period you show a consolidated result. The elimination of consolidate, you should use LTD numbers to intercompany sales or other transactions can be establish beginning balances. recorded by setting aside a unique value in the

    balancing segment. In this way, the individual ? Do all of the subsidiary set of books use the business can view their results and corporate can same chart of accounts? If not, then you will consolidate accounts that eliminate intercompany have to prepare a mapping of the account values. transactions. In this case, there is no need to use the ? Do you use Multiple Reporting Currency (MRC) consolidation functionality. or Currency Translations to translate The critical factor in deciding to use Oracle‟s transactions from a foreign currency to your consolidation feature is do you need to report reporting currency?

    Volume 8 -2, Mar-2004, Page 2 of 9

    Atl-OAUG, tips and techniques

Oracle Consolidation Setup Steps

    The steps to set up and use the consolidation

    features in Oracle are:

    1. Define the parent or consolidated set of books.

    2. Map the values from the subsidiary set of books

    to the parent set of books.

    3. Define the eliminations.

    American Bureau of Shipping

    The American Bureau of Shipping (ABS) is a not-for-

    profit organization with world-wide operations and

     offers a good illustration of how the consolidation

    process works. ABS is one of the world's leading Now let‟s discuss the steps to set up the ship classification societies. Their primary purpose is consolidation to determine the structural and mechanical fitness of One Define the Parent or Consolidated Set ships and other marine structures for their intended of Books purpose. They operate three divisions, ABS

    Americas, ABS Europe, and ABS Pacific and all of ABS uses multiple balancing segment values in the these regions manage operations in multiple same set of books. Though they have a set of books countries and currencies within their region. The for their US operations, they elected to create a financial results are reported in the same database separate set of books for their consolidations. with a set of books defined for each unique currency. Consolidations don‟t require a separate set of books Consolidations are done only in US Dollars. All but security, reporting or other requirements may operations use the same calendar and chart of suggest a separate set of books. Obviously if ABS accounts with Business Unit being the balancing had elected not to create a separate set of books they segment. would choose an existing set of books where the

    currency was the one in which they wanted to report The reasons ABS has for consolidating financial consolidated results. results are to obtain those results more quickly,

    more reliably and with more uniformity than before. ABS created parent values in their balancing As ABS was preparing to use consolidations, Mike segment to represent each of their regional centers Deo, the controller at ABS indicated that his staff as well as one for their total consolidation. See the was being pressed more and more by management accompanying illustration: to provide more timely and accurate results so that management could be more responsive to situations

    in any part of the world. Parent Though they maintain over a hundred sets of books Value Description for these divisions and for affiliated companies,

    we‟ll illustrate just a small portion in order to 010 ABS Consolidated

    simplify the discussion. For our example we‟ll say 100 Americas Consolidated that ABS uses 12 sets of books in two divisions in the

    organization structure shown in the following 200 EMEA Consolidated

    illustration. (The actual values shown have been 300 Asia/Pacific Consolidated changed and those encased with double lines

    represent parent values). The consolidation process will create individual

    account balances in the US set of books (in USD) for all their operations. ABS will use security rules to

    restrict improper access of the data by the various [Chart: ABS Company Consolidation Structure] Volume 8 -2, Mar-2004, Page 3 of 9 regional managers and by those who should only

    Atl-OAUG, tips and techniques

    have access to the regular US operations. follows: Two Map the values from the subsidiary set of books to the parent set of books. Parent Subsidiary

    ABS had several options in mapping the account Flexfield Action Segment values from the subsidiary set of books to the Segment Value parent‟s. They could map segments or they could

    map accounts. The mapping actions that they could Business Unit Assign Value 110 perform when mapping segments are: Cost Center Copy Value From Business Unit

    ? Copy Value From: Copy all values in the Account Copy Value From Account subsidiary set of books to the same values in

    consolidated set of books. (The segments do not Product Assign Value 0000

    have to use the same value set, but must use the Future Assign Value 0000 same segment values).

    ABS elected not to do this because in numerous ? Assign Single Value: Assign one specific value cases a subsidiary set of books had more than one that will be used for the parent segment. You business unit and that would have meant using must enter the value that the parent chart of Rollup Rules making the process more complicated accounts will use. than they wanted. If your various sets of books use

    ? Use Rollup Rules From: Map values from your different accounting flexfields, you may need a more

    subsidiary segments to your parent segments complicated mapping. The best rule to keep in mind

    using the rule specified in the Rollup Rules here is to keep it simple. You will need to create a

    region. mapping for each subsidiary set of books you are

    going to consolidate. ABS decided that they wanted to keep the account

    values of the consolidated results the same as their When it comes to executing the consolidation, you subsidiary account values. This decision was made submit one consolidation for each mapping. easier by the fact that all operations at ABS use the However, Oracle allows you to define mapping sets same chart of accounts. which help tremendously if you consolidate many

     sets of books. ABS defined three mapping sets, one [Figure: Mapping Subsidiary to Parent Set of Books] for each region, that way one region wouldn‟t have

    to wait on another to begin their consolidation.

    Three Define the Eliminations

    This step is optional but it is useful. ABS created

    Elimination Sets that enabled Oracle to

    automatically calculate and prepare elimination

    journal entries. They identified the account(s) to be

    eliminated (the Source) and the account in the

    elimination ledger to which the balance should be

    posted. They used parent accounts in the Source

    field to reduce the number of changes that would be

    required by the addition of future business units.

     [Figure: Sample Elimination Account] An option they considered was to create one

    segment value for the consolidated business unit

    (this is the balancing segment) and to make each

    subsidiary a unique cost center. They would keep

    the accounts intact but assign a zero value to their Volume 8 -2, Mar-2004, Page 4 of 9 product line. The mapping to do that would be as

    Atl-OAUG, tips and techniques

Though not evident in the accompanying experience and nearly 4 years of that with 11i.

    organization chart, ABS has different levels of consolidation and so they have to process their

    eliminations in stages, which means that they had to New in Financials Family Pack E: FSG create multiple elimination sets one for each stage. Report Name Display in Concurrent They process and post the lowest levels first and Manager then proceed to the next stage and so on until all the

    eliminations are posted. The Financial Statement Generator (FSG) report

    name is now displayed when you review your Processing Consolidations concurrent requests. This allows you to quickly and

    easily identify your FSG reports in the Concurrent Once the consolidations mappings and elimination

    Manager without having to view each report's sets are created you can begin to process your

    output. consolidations. The final installment of this subject

    will illustrate the features to help you process your Read about this new feature and many others via consolidations and tools that are available to take Metalink. Click on E-Business 11i button. Select advantage of the data now that it is all in one set of „About Documents‟. Scroll down to Financials. books. Select About Oracle Financials Family Pack E (Note

    243637.1). Scroll down to the bottom and select a Concluding part will be in the next edition of this

    financial module. FSG enhancements are located newsletter.

    under „Financial Report Generator‟. Brenda Carlton, Don Driggs, BOSS Corporation Oracle Financials Consultant,

     Don Driggs is an Oracle financials consultant

    working for BOSS Corporation with 7 years


    From the SQL Prompt: How to set Org Context for Multi-Org

    environment EXEC

    apps.fnd_client_info.set_org_context(2In a multi-org environment, if you are trying to 5); query from views, you may not get any data (e.g.,

    „SELECT * from pa_projects‟ will not retrieve any In TOAD: rows). Execute the following PL/SQL block. In order to get the data from the views, we need to BEGIN setup a desired organization id by executing


    apps.fnd_client_info.set_org_context(2procedure. By doing this, you will get data from the

    5); views related to that particular organization only.

    END; To determine which organizations have been set up

    as business groups, use the per_business_groups If you are connecting any schema other than APPS, view: you may want to check the grant execute

    permissions on fnd_client_info package. SELECT * FROM apps.per_business_groups

    To automatically execute the procedure: Here is the actual syntax of the procedure: As an

    example, 25 is the value for the parameter Volume 8 -2, Mar-2004, Page 5 of 9 For the SQL*Plus Client on your PC: Organization ID.

    Atl-OAUG, tips and techniques

Locate GLOGIN.SQL under ? Download „‟ from

    $ORACLE_HOME/sqlplus/admin or ? ftp://oracle-$ORACLE_HOME/plus** directory.$ORACLE_HOME will be something like „orant‟ OMPARE_TOOL/ and plus** will be something like „plus80‟)

    ? Run the program ( -h option shows the usage) Using a text editor, open GLOGIN.sql and add the

    following statement: ? The file, „report.txt‟ shows your current level of

    patchsets. exec apps.fnd_client_info.set_org_context(25)

    This program can be run for application versions of For TOAD: 10.7, 11 and 11i. Create a text file, autoorg.sql, on your local drive or For the more details, please check the Oracle on any network drive with the following code. document #139684.1 BEGIN How to get a base table or view from a apps.fnd_client_info.set_org_context screen where “Record History” is not (25); available END; In most of Oracle Forms-base screens, you can get a Select: View -> Options from the TOAD menu bar. base table by clicking „Help‟ and „Record History‟.

    When this functionality is not available, you can find Select „StartUp‟ from the navigation tree and enter the base table or view by doing this: the complete path and file name (e.g.,

    ? Click „Help‟ from the menu, and „Examine‟ C:\autoorg.sql) for „File to Autoexecute on new

    connections‟ option. ? Enter the „APPS‟ password. (It will show a pop-

    up window)

    Venkat Kancherla and Kevin Bailey, ? Enter „system‟ for „block‟ and „last_query‟ for Solution Beacon „field‟ ? The „value‟ will display a query that shows the How to get a full list of patches applied. base table or view

    Erik Shin, You can check the patch set levels for each family SageWise Group, Inc. pack or module in the application of your site as follows:


    working with your AR consultant, has received Post Implementation Productivity proper training and is now ready to support your

    companies AR needs. Or more probable your Productivity: The rate at which goods or services are

    consultants haven‟t let the door hit them on the way produced especially output per unit of labor.

    out and you are scrambling to understand why the So the “Go Live” has come and gone and with test scripts didn‟t identify any problems but in relatively few issues your company is now up and production your end users cant enter invoices. running on Oracle‟s E-Business Suite. The project

    Many of us are experienced in implementations and team is dismantling and Production Support is now

    have plenty of tips and tricks, but it is the transitioning to your full-time staff. If the project

    organizational structure behind the implementation team was setup correctly your full-time resources

    that will help you reap the real benefits of Oracle‟s were aligned with the appropriate consultants. For Volume 8 -2, Mar-2004, Page 6 of 9 E-Business Suite; Productivity. example your AR Functional Super-user was

    Atl-OAUG, tips and techniques

    One of the most significant benefits of implementing Oracle modules functionally and technically and enterprise class software is the increase in will be your database administrator. Never Productivity it brings to your business. Without Gonna Happen! So when the budget doesn‟t getting into the principals of economics, an increase allow for all the resources you need, supplement in Productivity directly hits your bottom line. But your support with a junior level resource. A with the complexity of Oracle‟s E-Business Suite it is junior level resource with minimal training can often Productivity that takes the first hit after Go handle most System Administrator Live. responsibilities and relieve your senior level

    staff so they can focus on your real issues. Here are a few tips to help you recognize your goal

    HINT: This person can troubleshoot the Oracle of Post Implementation Productivity.

    printing problems that no one really wants to ? Develop your own troubleshooting guides work on and he will be happy to do it! specifically for your business. When an issue ? Make sure that your support staff knows how to occurs in an Oracle Application module you work with Oracle Support. While “Effective” want smart troubleshooting. This means as few and “Oracle Support” sounds like an oxymoron people as possible working the issue. If you to most of us, working effectively with Oracle develop detailed troubleshooting guides support will directly increase your productivity. oriented for your business even the most Be sure to open a TAR only as the last resort and inexperienced user will have a chance to resolve only after an exhaustive search of Metalink. If the issue before involving your support staff. you must open a TAR be sure that all

    appropriate diagnostics have been run and all ? Create strict policy and procedure for reporting

    program names and versions have been Oracle Application issues. Inevitably a bug will

    identified, if it is a data corruption issue, be sure be identified by a user that will need to be

    to provide a spreadsheet with the problem data. handled by your most experienced support staff.

    Having good communication with your Oracle The steps taken between the identification of the

    Customer Advocate will also come in handy issue and its resolution is where productivity

    with those slow moving TAR‟S. takes the biggest hit. Try to minimize the

    amount of hands that review the issue. If your Oracle‟s E-Business Suite can provide your company user has reviewed the troubleshooting guide with tremendous benefits. But with the complexity identified in the previous step and still cannot and issues that come with support, many of these resolve the issue then they should know exactly benefits can become casualties. Be sure that one of who to contact and what information to provide. the goals of your organization is to reap the benefit How many times do you see an issue reported of Productivity. When this goal is met you have without the appropriate information and it takes provided your organization with true value. several iterations of emails and voicemails Scott Williamson before the troubleshooting can even begin?

     ? Leverage junior level resources. We all would

    like to hire one resource that knows all 200

    Volume 8 -2, Mar-2004, Page 7 of 9

    Atl-OAUG, tips and techniques

    From OAUG

     The Oracle Applications Users Group (OAUG), the knowledgebase for Oracle applications users worldwide,

    invites all Oracle applications users to share your ideas, innovations and solutions by submitting a presentation

    for the following events:

    Oracle Applications Technology Symposium (OATS)

    May 2004

    Cincinnati, Ohio USA

    Oracle Users Forum - Applications

    June 2004

    Paris, France

    OAUG Connection Point 2004 - The Point of Contact

    September 12 - 15, 2004

    Walt Disney World Swan and Dolphin Hotel Orlando,

    Florida, USA

    For more information on any OAUG event, visit our Web site at or call +1 404-240-0999.

    Lynne Weil, Geo/SIG Manager, Oracle Applications Users Group,

OAUG Business

Atl-OAUG Structure

    Position Person Email address Coordinator Brenda Carlton, Consultant Web Server & Internet Services David Rincon, The DBA Group,


    Web Mistress Mary Lou Weiss, Solution Beacon Newsletter Editor Satyakanth Abbaraju, BOSS Corp

    Erik Shin, SageWise Group, Inc Technical Session Coordinator /


    thMar. 19 Agenda

    8:30 12:00

    Vendor Spotlight: Application Data Lifecycle Management for Jim Walker Improved Performance and Lower Storage TCO Outerbay Technologies Member Spotlight: CTI Molecular Imaging Walter Cromer, CTI

    Alicia Hoekstra and John Stouffer, To Patch or Not to Patch ... It's Not a DBA Question Solution Beacon BREAKOUT SESSIONS

    Technical: Care and Feeding of 11i John Stouffer, Solution Beacon Oracle Collections Module Chris Thornhill, Oracle

    Volume 8 -2, Mar-2004, Page 8 of 9

    Atl-OAUG, tips and techniques

    Robert Rentz, Useful Tricks for Oracle Workflow SSI Worldwide Oracle Projects Family Pack M (Applications Release 11.5.11) and

    Beyond Julian Kozicki, Great Projects

Host: OuterBay Technologies

    Location: Villa Christina, 45 Perimeter Summit Blvd, Atlanta, Georgia 30319

    Reservations: Jenny Yang,, 408.340.1284


Highlights: May 20th Agenda

    ? Member Spotlight: McKee Foods

    ? Oracle Enterprise Planning and Budgeting Module

    ? Technical: Physical/Logical Standby Databases with 11i Finapps

    ? GL Intercompany

    ? Sarbannes-Oxley Discussion

    Host: Computer Associates

    Location: Villa Christina, 45 Perimeter Summit Blvd, Atlanta, Georgia 30319 Reservations: Keli McPherson,, 770-916-3682

    Volume 8 -2, Mar-2004, Page 9 of 9

Report this document

For any questions or suggestions please email