The Feature

Tips and Tricks for Oracle FSGs

April 29, 2008 on 2:17 pm | by Melanie Cameron | In Feature of the Week, How To Guides | 1 Comment |  Print |  Email

Financial Statement Generators (FSG’s) are a necessary evil at any company running Oracle’s E-Business Suite General Ledger. Row Sets, Column Sets, Content Sets, Parents accounts or Ranges, Publish with XML, ADI. . .the combinations and possibilities are endless. And the decisions you make when creating and maintaining your FSGs can affect their ease of use and maintainability in the future.

See my Tips and Tricks for Oracle FSGs white paper for Best Practices, Tips and Tricks to making this task a little more manageable and less time consuming. When working with FSGs, perhaps the most important thing to remember is the FSG functionality is old. I have been a heavy user of General Ledger for 13 years, and except for generating outputs, it is basically the same.

That means that the majority of older installs (implemented prior to 11i) had one-off patches that greatly affected the way FSGs behave. A report created three years ago that is copied or mimicked for a new report-well, they just give different results. Research shows the new report is behaving as Oracle documentation explains it should, but the old report is not.

So I leave you with my biggest tip of the paper: Tip: If it works, don’t change it! See the while paper for more details…

Oracle Intercompany Inside Out – Conversation with David Haimes from Oracle

January 8, 2008 on 8:27 pm | by Marian Crkon | In Conversations | 1 Comment |  Print |  Email

David: Hi Marian

Marian: Hello there! Thank you for checking in. Are you ready to chat for a while?

Yes; I even closed down Twitter!

Happy new year! Hope you had wonderful holidays!

Thanks. I enjoyed the holidays and am looking forward to 2008.

Well, let me start with a traditional question and ask about your Oracle beginnings.

I joined Oracle UK in 1997 as an Apps Engineer. Oracle was and still is a very attractive employer in the UK because it has a significant amount of development there. I think back then working in a global team was maybe not so common but we worked as part of a team based in the UK and US building the Financials features. It was a very strong team; I was interviewed by Terrance Wampler, now VP of Strategy and I coached Tim Dexter, who at that time was a Financials engineer but is now of BI Publisher Fame. There is still a lot of application and middleware development in the UK. Many UK IT jobs are consulting or support and Oracle has one of the biggest UK development centers I am aware of.

I went to Bracknell a couple of times while working for Oracle Central Europe in mid 90s…

I started in the Bracknell ‘Ring’ Office…

I understand you live in SFO now. What are you currently responsible at Oracle?

I moved to San Francisco in early 2000. Oracle was kind enough to transfer me. I am currently responsible for the development of a couple of Financials features/products, specifically Legal Entity and Intercompany. They are part of the Financials architecture, so I get good insight into other products and work with many other development teams, which I enjoy.

I understand the legal entity and intercompany underwent pretty significant changes in R12, isn’t it right?

Yes, they were pulled out and each made into their own product in R12. Intercompany, or Advanced Global Intercompany System (AGIS), takes some features from GL in 11i and builds them out into a pretty nice product. Legal Entity was really only implied in Financials in 11i, now it is very explicit and we can use that to drive tax calculation and reporting (via eTax) and fiscal reporting, which are both very big deals for our customers.

OK, let’s pretend I am a typical R11i customer using the intra-company segment in GL. What are the key advantages of upgrading to the new R12 functionality?

So let’s assume you are in a single set of books and the balancing segments value (BSV aka Company Code) represents your LE. I am leaving aside Legal Entity for now and talking about Intercompany.

OK.

In 11i, if your transactions in the sub-ledger are out of balance by BSV, they can be balanced automatically when they hit the GL, but that makes it pretty difficult to reconcile Intercompany as the balancing only happens after transfer to GL (which might be summarized) and posting. So you really don’t know what transactions generated those intercompany journal lines you see in GL.

In R12 you get balancing of your transactions at the transaction level, when SLA accounts them. So I can go into AP for example look at a payables invoice and see the Intercompany accounting lines right there on the invoice, as you can imagine that will make reconciliation of my Intercompany somewhat easier.

That is something that comes ‘for free’ out of the box, you need no extra set up etc. We also built a very nice BI Publisher based online reporting tool. It looks at the GL balances in your Intercompany accounts and right there in the UI you can click on the balances and drill all the way to the transactions.

I am sure many of my friends who are responsible for reconciling GL to sub-ledgers will be thrilled to hear that.

I have done a blog post about that and there is one due with more detail on how we did it technically with more details of the reports… When I did a demo of this functionality to some Oracle sales consultants and partners, I got a round of applause that felt pretty good. I never had that before during a demo…

Good for you! Let me get to your blog a little later. How about the Legal Entity? As far as I know, that concept was only present in HR organizations when you defined legal entities and operating units. What are the main functionality differences in R12?

In 11i, the Legal Entity was only defined in HR organizations, and many times in Financials, that Legal entity was entered as a dummy value. There were a lot of other disparate places, such as in set up for globalization features where more detailed Legal Information was stored.

What we have done in R12 is pull the Operating Unit and Legal Entity apart, so you can have many Legal Entities in a single operating unit if you choose. You can create your LE in an all new user interface, which is part of the Accounting Set Up Manager. You can also define where these LE are registered, maybe for a number of different state taxes, etc., which is going to drive calculation of taxes.

When you enter a transaction you can pick the owning LE of that transaction (or default it based on a set of rules rules) and the owning LE will determine the sales tax due, maybe the accounting and certainly the fiscal reports the transaction appears in. What we also do is allow you to map these real world Legal Entities to an accounting structure, such as my Ledgers and BSV, which will drive Intercompany

It sounds like the legal entity functionality is much more robust and flexible in R12, comparing to its “quasi” deployment in R11i. The Subledger Accounting, Legal Entity Configurator, Advanced Global Intercompany System, and E-Business Tax are all new products in R12. Could you please describe the process of how these new products got incepted? I am mostly curious about when and how the input from users and partners was incorporated…

This goes back a long time in history, Steve Miranda started these many years ago after a lot of discussion with customers. I was first involved in Intercompany when we had a Focus Group in 2003. We got some interested customers on board and asked them about what they were looking for, what problems we needed to solve, there were Focus groups for the other initiatives too.

To give you an idea how much work went into this, we had the focus group come and test Intercompany alpha in spring 2004. They came for a week, looked at what we had so far and we got input on what we needed to change and priorities for new features. So a week of in depth on site working with customers gave us a good idea of the problems they face. We have also pushed out our designs to the Focus and had web conferences or live meetings on a regular basis.

I always find it ironic when Oracle asks customers not to customize the applications. We all hate the customizations but are often forced to do them when standard functionality is either not available or is not good enough. People planning their implementation projects are often in an information vacuum because the new functionality is often not known until a new release is actually publicaly released. The scope of R12 finalized few years ago, but only last year people actually started figuring out what is in it and why it would be beneficial to upgrade. What would be your advice to people who want to get involved in the design of the next Oracle release, i.e. Fusion?

Firstly, we don’t say do not customize, we provide a number of pretty neat ways for you to customize. The key is not to be invasive. Use flexfields, custom library, business events and workflows etc. It is fair to say though that we cannot always publicly detail features, for legal and other reasons. Also, we have a number of customer programs, advisory boards and Focus groups run by our strategy organization that [customers] can request to join. I’m not sure of the exact criteria to join, but I know one is active participation. So get in touch with Oracle Strategy about that. I believe they also have a blog. The benefit of this program is you will sign all the non disclosure agreements, so there is more that can be shared.

All new R12 products we mentioned here today are documented in the R12 Financials Content Documents, User Guides and other sources. Where can users find out more about these products and features and gain best possible knowledge to implement and use them?

These days there are more touch points with Oracle Development than ever before. I encourage feedback and questions on my blog and I have taken some of those discussions offline if there was information about the business that the poster did not want to post publicly. The reason I started the blog was that even though the Oracle Open World is a great way to come and talk to the people who built the product, it happens just once a year.

That’s where I was going with my next question…

There are a lot more bloggers now at Oracle and a lot of development and strategy people are on Oracle Mix. I think we need to do a better job of letting the Oracle community know where to find us. Jake and the AppsLab team are doing a great job, but we still have some way to go.

I guess the main message to the community is: be pro-active and get involved… What are your plans for your Intercompany blog in 2008?

I wasn’t really sure where it was going when I started it just after the OOW. I wanted to see the response. In 2008 I want to take some time to write detailed posts with explanations of new features in R12, maybe with more depth or color than in some of the documentation available. I’ll be starting with Intercompany, LE and might move on to GL and SLA and other products as I get time. That is one part. If I can get a repository of useful posts up there and give readers a chance to ask questions and add comments and opinions then that alone I will see as a success.

And THANK YOU for that. For a product manager, a blog seems like a great way to have an ongoing discussion with your user community.

I’ll keep posting on technology, Oracle news and general items too, but I won’t be offended if people skip those ones. I agree – blogs are very powerful, I would point to Tim Dexter’s BI Publisher blog as a good example of this.

Good luck with all your good intentions. It seems like all bloggers reach some kind of blogging fatigue at some point… You have already mentioned some blogs today, what are your other favorite blogs in your reader?

Thank you. One thing I have enjoyed since starting my blog is reading the great stuff written on blogs such as yours. There is a lot of great information out there that I honestly was not aware of. So this is a pat on the back to all Oracle application bloggers out there. Check out the blogs I listed on my blogroll and also look at who I follow on Twitter to get an idea of what I am reading at the moment.

To be honest, I still feel like the blogs are only followed by small community of “geeks” who read and comment on each others’ posts. Others stumble upon them only when searching for something specific… But that’s all right. It is a move forward from newsletters received by email.

I think the community is growing and there are a lot of lurkers out there who read and never comment. People like to stay current.

Well, that’s all questions I had. What else would you like to talk about? We did not mention Mix or your SIG much…

Oracle Mix is a great opportunity for me to learn and hopefully for customers to get better information and influence direction. Right now, I don’t think anyone is sure how it will work out, but that’s also very exciting for me.

Thank you very much David.

It’s been great chatting with you!

Same here. Thank you for your time and best of luck to you in 2008, especially with those two small babies at home you mentioned in your post…

How to Change GL Calendar without Reimplementing Your Oracle Financials

October 5, 2007 on 8:02 am | by Marian Crkon | In How To Guides | 24 Comments |  Print |  Email

We had a pressing business need to change our corporate calendar from a fiscal year ending on March 31 to a calendar year ending on December 31 to be consistent with our parent company, which acquired us this year. Oracle’s recommendation in this situation is to create a new set of books and use the consolidation feature to move account balances from the original set of books to the new set of books. This assumes that the periods that need changing already have data or have a status other than Never Opened which is usually the case.

Creating a new set of books, however, becomes a big problem if General Ledger is not the only application you use. It means creating new operating units and replicating configurations in all your sub-ledgers. It also means supplier and customer sites and all open purchase orders, sales orders, invoices and other pending transactions must be converted. Basically, we are talking about re-implementing your system.

You Are Not Alone

Many other businesses find themselves in a similar situation and have no time or desire to re-implement their system to accommodate this request. The good news is that Oracle Support acknowledged this pressing need and came up with a couple of recommendations.

After reading the Metalink note 102460.1 and OAUG white paper on this subject, we were encouraged there was another way. Changing the GL calendar without defining a new SOB saved us from re-implementing our sub-ledgers in three operating units, which used the current set of books and calendar. The system allows you to make changes to the existing calendar when you meet the following conditions:

• All the periods are in a status of Never Opened.

• A budget year that includes the periods to be changed is no to yet opened.

• An encumbrance year that includes the periods to be changed is not yet opened.

• The periods to be changed have not yet been copied from the GL Calendar to Project Accounting.

The status of the periods for all applications is maintained in the gl_period_statuses table. There is a record for every period for every application. The status in this table of the periods being changed must be ‘N’ for Never Opened for every application. Most subsidiary financial applications can control their own periods and it is maintained in that table. The exception is Project Accounting, which in addition to having its period statuses maintained in the gl_period_statuses table, it also stores its project status information in the pa_periods_all table.

Looking at our GL_PERIOD_STATUSES table, there were future periods that were not in ‘Never Opened’ status in GL, Projects and Payables applications. In Projects because of the way the standard functionality copied the whole calendar from GL to PA. Luckily, we did not have any budgets or encumbrances posted to those future periods.

Here Is How To Do it

We did the following to change our existing corporate calendar:

In General Ledger

• Identified the future periods in calendar, which need to be redefined.

• Updated period statuses to ‘Never Opened’ for all applications and applicable SOBs.

• Identified and deleted any GL Balances in future periods.

• Re-defined our corporate calendar. In our case that meant defining three adjusting periods in year 2007 to make 2007 a short year ending with Dec-07, and re-defining future periods to match the calendar year. (You can use this DataLoad to do so).

In Projects

• • Deleted (old) future periods (beyond Jan-08) from Projects in all operating units
  • Copied newly-defined GL periods from GL to Projects
  • Create new Corporate Books. (Manually)
  • Create a new Prorate Conventions. (Manually)
  • Assign Asset Categories to new that were linked to the new corporate book. (DataLoad)
  • Extract and upload all active assets to the new corporate books. (SQL script to extract, and ADI to upload)
  • Closed all 2007 periods in all sub-ledgers and GL through Dec-07
  • Created transactions in Dec-07 in all sub-ledgers, including POs, invoices, expense reports, payments, INV transactions, AR transactions, cash receipts, timecards, project adjustments, and assets additions and retirements.
  • Ran depreciation for Dec-07.
  • Closed Dec-07 in all sub-ledgers and GL.
  • Opened Jan-2008 all sub-ledgers and GL and “rolled into” the next fiscal year.


Remember, you should not use this solution if any of the periods you wish to modify are in use. This means if the period has a status of something other than Never Opened, or if the period is in an open budget year or open encumbrance year you will not be able to change it on the form.

Fixed Assets

We use use Oracle Assets with asset calendar starting on April 1, 2001. We had about 2,000 assets in our corporate books, which are associated with the set of books and the fiscal calendar ending on March 31. After considering several alternatives including not doing anything, we decided it was best to create a new calendar to be consistent with General Ledger. We couldn’t simply adjust the current calendar. We need to create a new asset calendar.

After creating a new asset calendar we also had to:

Test, Test and Test Again

To test the modified calendar, we did the following:

Favorite Fusion Features – General Ledger

June 14, 2006 on 10:06 pm | by Marian Crkon | In Ideas and Opinions | 1 Comment |  Print |  Email

By now you probably know what this is about. The time has come to think about what you want in Oracle Fusion. If you are an expert user with real-life, hands-on experience and have ideas on how to improve the applications; if you had to customize a module to get the features you need, or additional functionality would save you time and money, submit your strategic requests via the OAUG Enhancement Request System (ERS) today! As an Oracle Applications Users Group (OAUG) member and/or Oracle Applications user, Oracle wants your feedback on strategic improvements to current Oracle E-Business Suite functionality for their initial Fusion releases.

Alternatively, if the option above does not work for you, then let’s also try something different. Let’s keep a weblog of improvement tips, enhancement requests and your best features in Oracle, PeopleSoft, JD Edwards and Siebel applications you want to have in Oracle Fusion. Let’s have discussions about what enhancements and features make most sense, and then log Fusion Requests before the Oracle’s June deadline. There won’t be any wrong questions or bad ideas (or at least, we’ll let the “group intelligence” decide). Your involvement can be as easy as providing comments with your ideas to this post.

Boy, what can you fundamentally change in GL? By now we know that 4Cs are coming in Release 12. And a couple of people had few more ideas. Below is a list of improvement tips and enhancement ideas based on Oracle General Ledger 11i.10 as submitted by Oracle users at the OAUG Enhancement Requests portal. Use the portal to provide you own enhancement requests, or submit comments to this post to do the same.

Note: I believe the idea of Fusion Requests is to submit enhancement requests on top of the existing releases, not to request the existing features. Somebody correct me if I am wrong. I suppose it never hurts to reiterate what your “best features” are.

• Allow Separate Sequential Numbering by Balancing Segment. Submitted by Gerard Fuller. In several European countries (example Germany) the law requires us to maintain separate sequential numbering sequences by Legal Entity. Most organizations model legal entities by the Balancing Segment in General Ledger. We therefore need to be able to maintain and assign separate sequential number sequences by Balancing Segment. Currently this is only possible by Set of Book and Journal Category. Set of Books is too high level and prevents distinguishing between entities (i.e. Balancing Segments). Using Category requires us to create as many categories as we have legal entities, and assign them. This is quickly unworkable once we have more than a few entities per set of books.

• Data Integrity between Submodules and GL. Submitted by Cyndie Winrow. Each individual transaction is posted only once, eliminating data redundancy and the need to reconcile multiple ledgers.

• Carry Forward Encumbrances at Year End. Submitted by Cyndie Winrow. In Oracle, the carry forward process does not create journals, and not at period activity, but rather creates a view for inquiry and reporting with designated amounts as beginning balances. I believe that this functionality eliminates the need for “period zero” as currently used in PeopleSoft, unless there are other uses for it of which we are unaware.

• Interfund Balancing Capability. Submited by Cyndie Winrow. Allows set up for assignment of default natural account (nacct) segment values for fund balancing that can be specified to balance multiple journal sources and categories.

• Financial Statement Generator (FSG). Submitted by Cyndie Winrow. Used to generate financial reports based on data in general ledger. FSG is an integral part of the general ledger application. Many standard reports are provided in the general ledger application, such as trial balance reports, account analysis reports, and budget to actual reports. FSG gives great flexibility in allowing for the creation of customized reports. It is an easy-to-use tool and can be utilized and maintained without substantial technical resources. Define your reports with reusable report objects, making it easy to create new reports from components already defined. Design custom reports. Schedule reports to run automatically. Produce ad hoc reports. Print reports to tab-delimited files for easy import into client-based spreadsheet programs.

• Reporting Segments (Attributes) Off the Accounting Flexfield. Submitted by Cyndie Winrow. Used for required reporting to regulatory and appropriations agencies.

Which features would you like to become your favorite features in Oracle Fusion? Granted, it may take two years before you get them, if ever, but this is your opportunity to provide your own improvement ideas and enhancement requests. The voting to determine “best features” starts in July 2006.