The Feature

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:

24 Comments »

RSS feed for comments on this post. TrackBack URI

1. Hi,

   I follow your concept and I see it will work when you have the same number of periods in a calaendar. But I’m representing a company that wants to add an extra period to the calendar. That is, they wish to have a 14 period calendar instead of the one 13 period that they presently have (i.e 2 adjustment periods). As the number of periods per calendar year is controlled in the calendar definition, it seems impossible for me to add the extra period in the manner you describe because there is a screen check to make sure I cannot add any more periods than defined. Is there a solution for this or do I need to recreate the new set of books as Oracle describe?

   Many Thanks, Dean

   Comment by Dean McCarthy — December 18, 2007 #

2. Hi Dean,

   Thanks for stopping by. I am afraid the only way “official” way to add periods to the existing calendar is to define a new period type and set up a new SOB, which uses the new type. However, if you only want to add adjusting periods, you could update the number of periods for the respective period type (e.g. from 12 to 13 periods) in the table, and define the “missing” periods in the existing fiscal years… Obviously, I did not just recommend this alternative
   Marian

   Comment by Marian — December 18, 2007 #

3. Hi Marian

   Please advise on how to delete the periods in Projects. Can I do it through application or I need to delete from the back-end. If I delete from back-end will it impact other modules.

   Thanks and hope to receive your reply.

   Comment by Chong — February 5, 2008 #

4. You can delete future PA periods from the table if they weren’t used yet. You should do this after your original GL periods were re-defined in GL so that you can copy new periods to the PA Periods table. This should not impact other applications.

   Comment by Marian — February 5, 2008 #

5. I can’t re-defined my GL periods from Application as it does not allow me to change although it is still with the status ‘Never Open’. As such, do you mean that I have to re-define GL periods through back-end.
   Thanks.

   Comment by Chong — February 5, 2008 #

6. No, you should not re-define the GL periods in the table. If no balances, budgets or budget versions exist in the GL periods, you should be able to re-define them in the Calendar form.

   Comment by Marian — February 5, 2008 #

7. I have tried to re-define the GL periods in the Calendar form but error message prompted ‘You can only modify periods that have never been used’. I have check and there is no balances, budgets or budget versions exist in the GL periods. The form I access is Setup:Financials:Calendars:Accounting. When you mention re-define, do you mean create a new calendar or change the existing calendar.
   Thanks.

   Comment by Chong — February 5, 2008 #

8. Also, in the steps you mention that to update the period statuses to never open. In which form can I change the status.
   Thanks

   Comment by Chong — February 5, 2008 #

9. If you have periods, which are not in the Never Opened status, you will not be able to update them in the Calendar form. We updated the period status in the table.

   Comment by Marian — February 6, 2008 #

10. If we update the period status in the table, which table should we update? Once we change the status in that table, will there be any impact to any of the applications?

    Thanks and appreciate your response.

    Comment by Chong — February 21, 2008 #

11. [...] Last fall, I had an opportunity to spend some time in beautiful Portland, OR, assisting a talented group at PPM Energy with their calendar changes in Oracle Applications. PPM energy builds and manages wind turbines and sells alternative energy. I invited two of their key Oracle users – Lori Thibodeaux, an applications manager, and Kevin Kosub, a senior business analyst, to a have conversation about their lives with Oracle. Read on for few excerpts below. [...]

    Pingback by The Feature » Conversation with the Team from PPM Energy — March 6, 2008 #

12. We are on 11i and a frew of our GL periods have been setup incorrectly. We are also using Contracts, and have some going out to 2019. Can we change/adjust our GL periods? If so, what needs to be done? Thank you!

    Comment by ALam — May 7, 2009 #

13. Hi Marian,

    Great article and I believe that if you push hard enough you will also get a solution from Support. I have a client that I had to address a similar issue with last year, and we also had budgets and there was data in the budget year.

    We ended up getting scripts that enabled us to revert back to a situation where it seemed to be in a “Never Opened” state, though we had to do this using SQL, but we managed it.

    And yes, we did Test, Test, Test and Test – yes we did this 4 times before we felt comfortable doing this without skipping a beat.

    To top it all I had to do this with two clients in the past year, bummer, not because they were bought out, but because someone made a mistake.

    Comment by Mohan Iyer — May 7, 2009 #

14. This is good site with information on Oracle Application. Very Useful

    Comment by Samir Rane — May 29, 2009 #

15. Hi

    The client wants to change it’s Fiscal Calender from Financial Year Ending 31 August to FY ending 30 September. What route do you recommend, a one month year or is a 13 month year possible, if so, are there any steps or procedures available.

    Alroy

    Comment by Alroy Foster — June 1, 2009 #

16. Hi,
    Issue:
    I ‘don’t see 2010 Calendar from oracle apps (GL manager—Setup—Flexfield—Accounting Calendar ) and I’m not able to create 2010 calendar form Oracle apps, When I tried to create from apps error is calendar already exists. “ APP-FND-01206: This record already exists. You entered a duplicate value or sequence of values that must be unique for every record “ , I checked the backend system table and found that the 2010 calendar exists for half year(gl_period_statuses).
    Only option is backup table gl_period_statuses and delete 2010 period year using below script.
    “delete from gl_period_statuses where period_year like ’2010′; commit
    Is it Ok to delete records from above table only for period like 2010 from backend?
    Please advise.

    Lak

    Comment by Lak — December 11, 2009 #

17. did not see any solution to change calendar with out re-implementing when periods are opened as future and encumbrance year is opened.

    appreciate if some one could share something on this.

    Comment by ahmed — January 30, 2010 #

18. HI, we want to change our period dates. Right now we use a 4-4-5 Jan to Dec but going forward we would like to do a monthly Jan to Dec. So period names and number of periods are staying the same but the dates are changing.
    Would we have to do the whole change through Oracle support/ scripts updates or would there be an easier front end approach.
    Thanks

    Comment by Kate — March 4, 2010 #

19. Kate,

    If these are future-dated periods that were never used, you can re-define them without problems. However, if there are budgets, or journal lines, or the periods are already used by sub-ledgers, then the update would be more involved, along the lines of the description provided in the articles above.

    Marian

    Comment by Marian — March 23, 2010 #

20. Our company wants to switch from a 9/30 fiscal year end to a calendar year. How could we go about adding the additional three months to the current calendar? All of the current fiscal year calendars have budget data in them already. Would we need to change the AR, AP OPM calendars or would the GL calendar only be the issue?

    Comment by Rob — March 26, 2010 #

21. Hi,

    Is it applicable only for cases where the users have not started using the system because period will only be in Never opened status when no one has used it and no transactions done??

    Please larify on this part.

    Comment by Rakesh — September 23, 2010 #

22. Could you detail the testing needed after running a calendar change like this? How do we now GL, AR, AP and more are truly healthy transaction and integrity wise? Is there a work-around for periods that are opened due to previous transactions?

    Comment by Bill Pierce — November 1, 2010 #

23. Bill,

    Exact testing scenarios would be determined by your configurations, but in general, you need to enter and process transactions in the respective modules, transfer their journal entries to GL and close the periods. Then in GL, post the imported journals and close the periods. Ideally close the whole fiscal year…

    Marian

    Comment by Marian — November 1, 2010 #

24. Hi,
    Issue :
    Can i change period status from “opened” to “never open”.
    I really appreciate if someone could share something about this issue…
    Thanks

    Comment by hery — January 4, 2011 #

Leave a comment