The Feature

How to Convert Legacy Expenditure Balances to Oracle Projects

October 12, 2007 on 3:58 pm | by Marian Crkon | In How To Guides | 3 Comments |  Print |  Email

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.