The Feature

Attending the Oracle OpenWorld 2007

Here is that time of the year again when Oracle opens its doors to its customers and partners and invites them all to the biggest party on the block, the Oracle OpenWorld 2007, November 11-15th at Moscone Center in San Francisco. It is also that time of the year when Oracle professionals have to decide whether to go to San Francisco, or not, and what to expect if we go there.

This is my first time at the OOW. Oracle made my decision somewhat easier this time by inviting Oracle bloggers to attend the conference. I appreciate the gesture and the recognition of a vibrant blogging community. Take this as my first disclosure for anything I write from the OOW and know that Oracle waived my registration fee.

The OOW 2007 promises to be bigger and better than ever [I am not sure bigger is better in this case]. The conference motto is “Learn, Experience and Connect” and it will be interesting to see how that works out with 1,600 planned sessions in five days! In addition to some usual agenda items like executive keynotes and solution sessions, expert tracks and sessions, partner demos, hands-on labs, etc. I am excited to see few conference experiments. It will be interesting to check out:

OpenWorld Connect

The Open World Connect is an online tool for attendees to network with peers, schedule meetings, establish and join interest groups, chat in real time, etc. I believe you can only sign up for the OOW Connect if you are a registered conference attendee. However, there are several RSS feeds you can subscribe to stay connected with the happennigs at the conference:

• Oracle Technology Network (OTN)
• Oracle Partners Network (OPN)
• Oracle Applications Users Group
• IOUG
• E-Business Suite Financials
• eBusiness Suite 12i Upgraders
• Oracle Bloggers

OpenWorld Unconference

This one should be interesting. For the first time ever at Oracle OpenWorld, attendees will have the opportunity to directly participate by presenting their own session or workshop on a topic they’re passionate about, in an informal, interactive setting.

What I Hope For

I am hoping to learn about new features, functionality, and solutions in eBusiness Suite 11i and 12. I would also like to know the current status of Fusion Applications, especially in the light of the rumored resignation of John Wookey. I am also hoping to get more involved with the SIG groups and meet in person many of my peers that I only know by name.

When reviewing the eBusiness Suite tracks, several sessions caught my attention, including:

• OAUG XML Publisher SIG The OAUG XML Publisher SIG will discuss topics on Oracle XML Publisher, a Java-based product within the Oracle Fusion Middleware family.

• OAUG Accounts Payable SIG A discussion on current AP Best Practices, the latest in AP Technologies and an opportunity to interact with members of the Oracle Product Management team.

• OAUG Projects SIG The OAUG Projects SIG will address topics of interest to those using Oracle Projects applications with opportunities for networking and sharing ideas.

• OAUG Fusion Council The OAUG Fusion Council assists in keeping the user community current on the vision for next-generation enterprise technologies, applications and services that will revolutionize business. This involves PeopleSoft, E-Business Suite and JD Edwards.

• Oracle E-Business Suite Financials Release 12 and Beyond In addition to new architectural components, Oracle E-Business Suite Financials Release 12 has hundreds of other new features. I am planning to attend this session to gain an understanding of the value these new features offer customers, and get a glimpse of what’s being planned for the next Oracle E-Business Suite Financials release.

• Oracle and Web 2.0: Applying the Principles of Social Networks, Collaboration, and the New Web to How Oracle Does Business This is the session I am most excited about! The Web 2.0 technology such as wikis, blogs, and tagging are just enablers for a new kind of organization. … [we should] hear about the trends that matter and see live examples of how Oracle is improving its operations inside the strategy organization via the new Web.

• Real Estate Store Construction and Refurbishment with Oracle Projects and Oracle Property Manager Learn how Oracle Projects and Oracle Financials can manage the full lifecycle of a facility: from initial site selection through construction, store opening, and refurbishment.

• Oracle Projects: You’ve Got Questions; We’ve Got Answers In-depth answers to the most frequently asked questions regarding the latest functionality available in Oracle Projects. It highlights real-world examples gathered from customers and partners.

• Leverage the Power of Billing Extensions in Oracle Projects: Five Common Requirements Met This session shows how to solve five common problems by using extensions.

• Personalizing and Extending Oracle E-Business Suite by Using the Oracle Applications Framework This case study examines best practices learned in implementing, personalizing, and extending an Oracle E-Business Suite application by use of the Oracle Applications Framework.

Hope to see you in San Francisco! If you are not planning on attending, stay connected by using the available online resources, including this and other Oracle blogs…

How to Convert Legacy Expenditure Balances to Oracle Projects

Here is another piece on converting legacy balances into Oracle Projects. To see how to convert your legacy project revenue and invoice balances, check here.

Before you start the legacy cost data conversion process, your Oracle Financials and Projects modules need to be fully configured and your prerequisites created. When migrating from your legacy system to Oracle Projects, you may need to bring your ending legacy balances as your opening balances in Oracle. This will allow you to seamlessly manage and report on your project activity.

For the purposes of this article, let’s take for example a scenario when you go live with Oracle Projects on January 1, 2008, and let’s assume we need to convert legacy cost balances in summary. When we extract the balances from the legacy system, we would summarize them by project, task, organization, expenditure type, and date. The balances will be created and processed as miscellaneous transactions in Oracle Projects.

Step 1 (GL): Review your chart of account values. If needed, add new values for revenue accounts, companies, departments, products, etc.

Step 2 (HR): Define your project organizations. Classify organizations that will own projects as Project/Task Owning Organizations, and classify organizations that will incur revenue as Project Expenditure/Event Organizations.

Step 3 (HR): Include new project organizations to your project organizational hierarchy.

Step 4 (HR): Create your employees and employee assignments. Each project has to have a valid employee assigned as project manager in order to generate client invoices.

Step 5 (PA): Review and update project structures: project types, project templates, expenditure categories, revenue categories, expenditure types, lookup sets…

Step 6 (PA): Create your projects and tasks. Make sure to select a correct project type for each project, i.e. (Time and Material, Fixed Price, Cost Plus, etc.) You cannot change project type once you generated project costs.

Note:

If you are converting costs from multiple years, make sure to open prior periods (e.g. DEC-06 for 2006 costs) in Projects in order to determine correct PA and GL Dates! See more details on PA and GL dates in this article. If you do not open respective prior periods, transaction GL and PA dates will be assigned in the current opened period, and your project reporting may be inaccurate.

Step 7 (PA): Define your Transaction Source. Transaction sources identify the source of external transactions you import into Oracle Projects using Transaction Import. You can define a new the transaction source ‘Conversion’ to identify your legacy costs from from your legacy system. When you create a transaction source, you control the Transaction Import processing by the options that you select. Here are few recommended settings for some key options:

• Transaction Source - Define your transaction source, e.g. ‘Conversion’.

• Default Expenditure Type Class - The system uses the default expenditure type class that you assign to a transaction source if an expenditure type class is not specified in the interface table. Enter ‘Miscellaneous Transaction’.

• Raw Cost GL Accounted - Select this option to indicate whether transactions imported from this transaction source have already been accounted for in GL. Oracle Projects expects that the external system has already posted the raw cost to the appropriate debit and credit accounts. None of the Oracle Projects processes will transfer these costs to GL or AP. If you enable tis option, you will also need to provide valid debit and credit code combination IDs. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Import Raw Cost Amounts - When a transaction source has this option enabled, the raw cost amount of the transactions has already been calculated (costed) in your external system. None of the Oracle Projects processes will calculate raw cost amounts for these transactions.

• Import Burdened Amounts - When this option is selected for a transaction source, Oracle Projects expects the external system to provide burdened costs. If the transaction does not have a burdened cost amount, Transaction Import will reject the transaction. When you select this option, the Import Raw Cost Amounts option is automatically selected.

• Allow Duplicate Reference - Enable this option to allow multiple transactions with this transaction source to use the same original system reference. If you enable this option, you cannot uniquely identify the item by transaction source and original system reference.

• Allow Interface Modifications - This option allows you to modify rejected transactions in the Review Transactions window after the import process is completed.

• Purge After Import - If you select this option, items successfully imported from the transaction source are automatically purged from the interface table when the import process is completed.

• Allow Reversals - If you enable this option, Oracle Projects allows reversals of expenditure batches or expenditure items for the transaction source. When you enable this option, the Allow Adjustments option is automatically enabled. Disable reversals for legacy balances, which should not change, i.e. balances posted in prior periods, and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Allow Adjustments - If you enable this option, you can adjust imported transactions in Oracle Projects after you load them via Transaction Import. Disable adjustments for legacy balances, which should not change, i.e. balances posted in prior periods and enable it for balances, which need to be processed as “new transactions” in Oracle Projects, e.g. unbilled billable transactions or uncapitalized capitalizable transactions.

• Processing Set Size - Enter the size of the processing set. The value entered indicates the number of records to be processed in each set. When interfacing large amounts of data, you can reduce the impact of unexpected errors by processing transactions in sets. The import process issues a database commit after each set is complete. If an error occurs and a rollback is issued, only the transactions in the current set are affected.

• Effective Dates - make sure your ‘From’ effective date is before your oldest expenditure item date you intend to upload.

Step 8 (PA): Disable cost transfer to General Ledger. Navigate to the Implementation Options screen and disable the Interface Usage Costs flag. This will prevent your legacy cost balances from being interfaced to Oracle General Ledger since the assumption is they were already posted by your legacy system.

Step 9 (WebADI): Create a WebADI integrator for Project Transaction Import. You can either use one of the seeded Project Integrators, or define your own, which matches your Transaction Source.

Step 10 (PA): Upload legacy cost balances as miscellaneous expenditure transactions. For high volume uploads, create a custom script and upload your data directly into PA_TRANSACTION_INTERFACE table. For medium-size uploads of several thousands records, use the WebADI Upload spreadsheet you created in Step 9 above.

Step 11 (PA): Run Transaction Import. When using WebADI, you can submit this step during your upload. Oracle lets you fix any rejections either in your upload spreadsheet, or in the interface table using the Review Transactions form.

Note

One comment about Transaction Import Status. Even though you can correct your most of your rejections in the Review Transactions screen, Oracle Projects does not let you change transaction status from Rejected (R) to Pending (P) in the Projects Transactions Interface table. Use this Set Transaction Import Status script to re-set the status.

Step 12 (PA): Distribute transaction costs. Run the PRC: Distribute Usage and Miscellaneous Costs process to calculate the amounts and generate account distribution lines. Make sure to run this process with the right period open and the right Through Date to assign correct PA and GL dates to your legacy transactions.

Step 13 (PA): Run the interface to General Ledger. Run the PRC: Interface Usage and Miscellaneous Costs to General Ledger process. This will set the legacy cost balances to look like they are interfaced to GL. If you disabled the GL Transfer option in Step 10, no journal entries will be created. Alternatively, you can run the PRC: Submit Interface Streamline Processes with option ‘DXU: Distribute/Interface Usage and Misc. Costs to GL’ to complete steps 12 and 13 together.

Step 14 (PA): Turn back on the GL interface you disabled in Step 8. Navigate to the Implementation Options screen and enable the Interface Usage Costs flag.

And that’s it. The exact sequence of steps will, of course, depend on your company’s specific business. Hopefully, this article provided a good starting point for understanding this process.

Call for Presentations for COLLABORATE 08 in Denver

In case you missed it, here is an announcement from the OAUG about the COLLABORATE 08 presentation deadlines.

Share your ideas, innovations and solutions during the COLLABORATE 08 conference. The call for presentations for the OAUG Forum is open and the deadline for submission is October 28, 2007, at 11:59 p.m. EDT.

All presenters submitting papers by the October 28, 2007, submission deadline will be entered into the Presenter Incentive Program drawing to win round-trip air travel to Denver, Colorado and accommodations in a deluxe hotel suite during COLLABORATE 08. Second and third place prizes will also be awarded.

Grand Prize:

• Round-trip air travel to Denver, Colorado, for one, coach, continental US only
• Accommodations in a deluxe hotel suite during COLLABORATE 08, five nights

Second Prize:

• Accommodations in a deluxe hotel suite during COLLABORATE 08, five nights

Third Prize:

• Round-trip air travel to Denver, Colorado, for one, coach, continental US only

The OAUG is seeking your knowledge and experiences on all Oracle Applications, including Oracle E-Business Suite, PeopleSoft, Siebel, Hyperion, Oracle Retail, Oracle Communications and MetaSolv Software, as well as applications technology. Please consider sharing your knowledge with others; submit your presentation today!

For more information about COLLABORATE 08 - OAUG Forum tracks, specific industry- or product-related areas of emphasis, presenter requirements and the presentation submission process, please refer to the call for presentations on the COLLABORATE 08 OAUG conference Web site.

Important Paper Submission Dates and Deadlines

• October 28, 2007, 11:59 p.m. EDT: Presentation abstracts due.
• January 14, 2008: Accepted presenters notified by the OAUG.
• February 29, 2007: All presentation materials including white paper and PowerPoint presentations are due.

See you in Denver!

How to Change GL Calendar without Reimplementing Your Oracle Financials

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


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:

• 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)

Test, Test and Test Again

To test the modified calendar, we did the following:

• 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.

How to Override Asset Depreciation

I am not actually sure when this feature became available but, to my delight, I found out today that you can retroactively override existing depreciation amounts for standalone and group assets calculated by Oracle Assets. Hence, depreciation override is my “feature of the week” this week.

Before running depreciation or performing adjustments, you must provide the necessary information in the Depreciation Override window or the FA_DEPRN_OVERRIDE table. Indicate whether the override is for depreciation or adjustments. When running depreciation, the system will upload and use the depreciation amounts provided in the interface table. You have to set the profile option FA: Enable Depreciation Override to Yes in order to use this feature.

Here is how to enter a depreciation override amounts using the Depreciation Override window:

1. As Fixed Assets Manager, navigate to Depreciation > Override.

2. In the Find Assets window, you can search for assets for which you need to change depreciation. If you did not use the Find Assets window, enter the asset number, book, and period of the asset in the rows of Depreciation Override window.

3. In Depreciation Override window, enter the override depreciation amount in the Depreciation field, or enter the override bonus depreciation amount the Bonus Depreciation field.

4. Select the adjustment type of Depreciation or Adjustment in the Use By field. The Status field displays the current status of the override record, which may be New, Post, or Posted. Set the status to ‘Post’ to submit your override. If the status is Post or Posted, you cannot update the record, you can only delete the record and reenter the updated record.

5. Save your work.

6. Run What-If Analysis or Projection to review the estimated depreciation amounts for that period. The depreciation run will pick and process your overrides in the ‘Post’ status and will change them to the ‘Posted’ status.