The Feature

How To Capitalize Legacy CIP Costs in Oracle Projects

August 10, 2010 on 8:55 pm | by Marian Crkon | In How To Guides, Ideas and Opinions | Enter Comments |  Print |  Email

Here is a quick summary of steps to convert legacy CIP costs on capital projects and capitalize them into assets. The instructions are applicable to both: Oracle EBS 11i and R12. This article assumes that you have already configured all prerequisites in your Oracle Project Costing and Fixed Assets. You will need Project Costing Super User and Fixed Assets Manager responsibilities, or the customized versions there of.

Define Capital Projects

Let’s start with defining the project template. This is where you can leverage a lot of good Oracle functionality to your benefit.  The details will vary in every instance, but in general, you need to complete the following steps:

PA1: Define Capital Project Template – Header

Navigate to Setup > Project > Project Templates to create a new template. Define the header-level information including the Number, Name, Project Type (make sure it is a Capital  project type class – you won’t be able to change it later), Organization, and Description.  If you want project created from this template to have a fixed duration (e.g. 12 months), define the Trans Duration. The system will populate the Project Finish dates based on the Project Start date and this duration for new projects.

PA2: Define Capital Project Template – Quick Entry Fields

Click on Setup Quick Entry. Define the fields for quick entry of projects. These are the “bare minimum fields” to enter when you copy a template into a project.

PA3: Define Capital Project Template – Tasks

Click on the blue box next to the Tasks option (I know, it not very intuitive), or click on Detail (while the cursor is on Tasks). Define your tasks, or work breakdown structure (WBS). Also determine which tasks should be capitalizable or not, i.e. can collect CIP costs to become fixed assets.

PA4: Define Capital Project Template – Define Project Assets

In this step, you can pre-define fixed assets you anticipate to complete as part of projects created from this template. Basically, you do here what you’d normally do in FA Mass Additions. You can use the Create Project Assets DataLoad to upload a higher volume of assets…

PA5: Define Capital Project Template – Assign Project Assets

Depending on your implementation approach, you assign default assets at project or task level. This functionality determines how the capitalizable expenditures will be assigned to future assets. In order to automate this, you probably want to assign assets at the task level so that you have one-to-one relationship between the costs and the assets in your tasks. The other options would be the costs in multiple tasks to be summarized into one asset at the project level… Refer to the Asset Summary and Detail Grouping Options in the Oracle Project Costing User Guide. You can use the Create Task Level Asset Assignments.

PA6: Define Capital Project Template – Create Task Transaction Controls

Following the example of the task-level asset assignment we defined in step PA5 above, it is critical, that the right CIP costs get collected in the right tasks. In other words, we want building-related costs be collected in building-specific task to capitalize them into a building asset assigned to the same task. To prevent invalid entries, you can create Transaction Controls. These are, basically, conditions that determine what expenditure types or categories are allowed in what projects or tasks. Again, use this sample Create Task Transaction Controls DataLoad to prepare and execute your upload. The trick will be finding the right tasks based on their WBS level. And one note about the Transaction Controls: make sure the Control Start Date and End Date are consistent with your Task Start Date and Finish Date.

PA6: Define Capital Project Template – Set Task-Level Capitalizable Flag

This is an optional step, and this should be taken care of by your Transaction Controls. However, you can make all expense tasks non-capitalizable by setting the Capitalizable flag to null… You can use this Update Expense Task Capitalizable Flag DataLoad.

PA7: Create Projects

Using the template created in steps PA1-6, create your capital projects. The quick entry fields will vary depending on your template, but as always, you can use the Create Projects DataLoad.

PA8: Upload Legacy CIP Costs

Depending on your conversion strategy, you can use a custom conversion program, or WebADI spreadsheet to upload your legacy CIP costs. Check this article for detail on how to convert legacy costs and how to resolve a couple of “features” during the Transaction Import.

PA9: Update the Project Status Inquiry Amounts

As Project Costing Super User, run the PRC: Update Project Summary Amounts

PA10: Complete Asset Definitions

Based on your implementation strategy and business process, you might be required to complete asset definitions in Projects before asset lines can be generated and interfaced to Oracle Assets. As Project Costing Super User, navigate to Capitalization > Capital Projects. Query up your project(s), click on Assets > Asset Details. Complete the asset definitions, just like you would in the Prepare Mass Additions.

PA11: Generate Asset Lines

As Project Costing Super User, run the PRC: Generate Asset Lines for a Range of Projects process. Watch the Date In Service parameter and PA Date parameter to make sure you select the right lines to generate.

PA12: Interface Asset Lines to Assets

As Project Costing Super User, run the PRC: Interface Assets to Oracle Assets process. It will create mass additions in FA MASS ADDITIONS table.

FA13: Post Mass Additions

If you required complete asset definition in Projects, your mass additions are ready to be posted. If asset lines are incomplete, you need to complete them in Prepare Mass Additions. In any case, once ready, log in as Fixed Assets Manager and navigate to Mass Additions > Post Mass Additions to create assets.

At this point, your legacy CIP costs were capitalized into fixed assets. Obviously, this summary is  simplified; refer to the Oracle Project Costing User Guide and Oracle Assets User Guide for more details.

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.

How to Convert Legacy Revenue and Invoices to Oracle Projects

September 3, 2006 on 9:58 am | by Marian Crkon | In How To Guides | 4 Comments |  Print |  Email

This is a brief overview of how to convert project revenue and invoice balances from your legacy systems to Oracle Projects. You start this data conversion process after your Oracle Financials and Projects modules were fully configured and your prerequisites were created. When migrating from your legacy billing 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, bill and report on your client projects.

Oracle Project Billing application performs two main functions: it generates project revenue and project customer invoices. For the purposes of this article, let’s take for example a scenario when you go live with Oracle Projects on January 1, 2007, and let’s assume we need to convert legacy revenue and invoices in summary. When we extract the balances from the legacy system, we would summarize them by project, task, organization, revenue type, and date. The balances will be created and processed as billing events in Oracle Projects. Hope you will find the overview useful.

Complete Financial and HR configuration Prerequisites:

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

Step 2 (AR): Create customers, customer sites and customer contacts. Each billable project has to be associated with a customer, bill-to site, ship-to site and a billing contact.

Step 3 (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 4 (HR): Include new project organizations to your project organizational hierarchy.

Step 5 (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.

Complete Project Accounting Configuration Prerequisites:

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

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

Step 8 (PA): Assign projects to correct customers, sites and contacts. All three are needed to generate revenue and invoices.

Step 9 (PA): Create customer agreements and funding allocations. Baseline project funding by either:

• Baseline the funding in Funding Inquiry screen (if you allowed ‘Baseline Funding Without Budget’ option during the project type definition, or…

• Create Approved Revenue Budget and baseline it in the Budgets screen.

If you are converting revenue from multiple years, make sure to open prior periods (e.g. DEC-05 for 2005 revenue) in order to determine the correct PA Dates and GL Dates. See the How GL Date and PA Date Are Determined in Oracle Projects article. If you do not open respective past periods, your GL and PA dates for all balances will be in DEC-06, and your project reporting may be inaccurate (if based on either PA or GL date).

Convert Legacy Revenue Balances:

Step 10 (PA): Disable revenue transfer to General Ledger. Navigate to the Implementation Options screen and disable the Transfer Revenue to GL flag. This will prevent your legacy revenue balances from being sent to Oracle General Ledger since they were already posted in your legacy system.

Step 11 (PA): Create legacy revenue balances as Revenue Events (Revenue Amount is populated). For high volume uploads, create a custom script and use Oracle Event APIs. For smaller volumes of several hundreds to few thousands of events, use DataLoad. Unfortunately, there is no Oracle Web ADI Upload for project revenue events available at this time (Release 11i.10).

Step 12 (PA): Generate revenue for legacy revenue events. Run the PRC: Generate Draft Revenue for a Range of Projects process through the last day the period (e.g. 31-DEC-2006).

Step 13 (PA): Run the Interface Revenue to General Ledger. This will set the legacy revenue balances to look like interfaced to GL. If you disabled the GL Transfer option in Step 10, no journal entries will be created.

Step 14 (PA): Turn back on the Transfer Revenue to General Ledger implementation option. Navigate to the Implementation Options screen and enable the Transfer Revenue to GL flag.

Enhancement Tips: It would be very useful to have ‘Revenue Sources’ in Oracle Projects, similar to Transaction Sources for expenditures) to manage the revenue conversions – control events are accrued, accounted for, billed, etc.

Covert Legacy Invoice Balances:

We need to distinguish between billed and unbilled legacy invoices. Billed invoices are those that were already printed and sent to the clients. Unbilled invoices are those that were generated in the legacy system, but were not printed and sent out. It is desirable to completely process your legacy invoices in the legacy system, so that you only need to convert the billed legacy invoices.

In the approach described below, we will create summarized legacy invoices as billing events and invoices in Oracle Projects. In order to prevent the balances from becoming open AR balances in Receivables, we will need to delete them in Oracle Receivables. Unfortunately, Oracle Projects 11i.10 does not provide an implementation option to disable the transfer of project invoices to AR. Follow the following steps to complete the conversion:

Step 15 (PA): Create legacy invoice balances as Billing Events (Bill Amount is populated). This can be done in one upload with the Revenue Balances described in Step 3.2.

Step 16 (PA): Generate draft invoices for legacy invoice balances. Run the PRC: Generate Draft Invoices for a Range of Projects process. Use the last day of the respective period (e.g. 31-DEC-2006) as Through Date and Invoice Date.

Step 17 (PA): Approve and release draft invoices.

Step 18 (PA): Interface legacy project invoices to Oracle Receivables. Run the PRC: Submit Streamline Interface process with the XI: Interface Interfaces to Receivables option.

Step 19 (AR): Delete the legacy project invoices in Oracle Receivables. As Receivables Manager, navigate to the Transactions form. Find, incomplete and delete the legacy project invoices. Optionally, use another DataLoad to delete the invoices.

Unpaid Legacy AR Invoices

What we converted above was a summary of billed customer invoices for each project. Some of them have already been paid, some have not in Receivables. You will need to convert any unpaid AR invoices as open AR transactions in Oracle Receivables.

Unbilled Billable Items

Also, what still needs to be converted is any unbilled billable items like billable timewcards, expense reports, etc. This will be discussed in an article about converting legacy expenditure balances.

How to Set Transaction Status During Transaction Import

March 8, 2006 on 4:57 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

You may need to know this when importing expenditure transactions into Oracle Projects. Follow these steps to import project expenditure transactions:

• Upload transactions into Oracle Projects interface table (using WebADI, or some custom program)

• Run the Transaction Import process from Oracle Projects

If transactions get rejected during the Transaction Import process, they are set to a rejected status (R) in the PA_TRANSACTION_INTERFACE table. In order for the transactions to become eligible for the next import, errors need to be corrected and status needs to be set to a pending (P).

You can fix data errors in the Review Transactions form. But unlike with FA Mass Additions, or other Oracle interfaces, users are unable to set the Transaction Status in the Review Transactions form. It takes a phone call into a DBA to run a simple custom Set Transaction Import Status script to make the transactions eligible for import again. Many clients register such script as a separate concurrent program.

I Will Love DataLoad if I Have To

March 2, 2006 on 8:11 pm | by Marian Crkon | In Reviews | Enter Comments |  Print |  Email

I was really surprised to see that a white paper about DataLoad is scheduled to be presented at the upcoming OAUG conference COLLABORATE 06 in Nashville, Tennessee (April 23 – 27, 2006). When did DataLoad become the official Oracle integration tool?

I embraced DataLoad in its early days in late nineties and it saved my rear many times. However, all this time I’ve been thinking of DataLoad as a provisional “copy and paste” solution until Oracle, or somebody else, comes up with a better idea. I guess there is a place for a nimble data management tool like DataLoad for uploading smaller volumes of data. But I would not recommend it for anything more than 10 to 1,000 records. I’d rather see Oracle Web ADI Integrators or “Define-Your-Own-Integrator” integrator for all existing Oracle Applications APIs. I hope Oracle stays committed to enabling integration between MS Excel and Oracle Applications.

Meanwhile, I will love DataLoad. I will keep creating new templates and keep watching for hours as DataLoad copies and pastes data into the applications. While you are waiting with me, visit DataLoad for product information, user guides, sample spreadsheets and free downloads.