The Feature

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.

Customer Billing Using Oracle Projects with Oracle Receivables White Paper

April 2, 2006 on 8:55 pm | by Marian Crkon | In Worth Noting | Enter Comments |  Print |  Email

I have received the Project Accounting News Letter today with links to several Metalink Knowledge Browsers (Metalink user login required). The Projects Billing Knowldge Browser includes a link to an old, but still useful white paper by G. R. Kinra, C.P.A. called the Customer Billing using Oracle Projects with Oracle Receivables.

The paper addresses Customer Billing for “contract” projects, using Oracle Projects with Oracle Receivables. It includes a review of the interface between Oracle Projects and Oracle Receivables, a discussion of the accounting treatment for revenue and receivable accounts, and relevant Oracle Projects and Receivables setup options.

To receive the Project Accounting News Letter , send an email to panews_us@oracle.com.

Oracle Project Billing – Show Me the Money!

March 17, 2006 on 10:41 pm | by Marian Crkon | In Reviews | 2 Comments |  Print |  Email

Oracle Project Billing is another core application in the Oracle Projects Suite 11i.10. It provides the ability to define revenue and invoicing rules for your projects, generate revenue, create invoices, and integrate with other Oracle Applications. It enables you to enter project customers and contacts with whom you have negotiated and contracted project work, define customer agreements (purchase orders, service agreements, contracts, etc.) from your customers and fund projects with those agreements. It provides ways to generate revenue using various methods including time and materials, percent complete, and cost plus, and create draft invoices from detail transactions and milestones for online approval by your project or accounting managers. Project Billing can automatically generate accounting entries for your revenue and invoices, and interface revenue to Oracle General Ledger and invoices to Oracle Receivables while maintaining a detail audit trail.

What it Can Do…

Billing Configuration

• Set up billing information, including customer, billing cycles, sales credit receivers, and retention terms
• Define billing methods for different contract projects or by top task for invoicing
• Set up bill rates for time and materials projects
• Define employee, job title and non-labor bill rates
• Assign and override bill rates and discounts

Agreements and Funding

• Define agreement types and templates
• Fund at project or task level
• Fund different projects across operating units
• Revaluate funding to match currency fluctuations
• Control billing by project or top task
• Define Customers by project or top task
• Enter agreements, bill rates, and events in any currency
• Configure hard and soft funding (revenue budget) limits
• Define currency conversion attributes
• Funding Multiple Projects and Tasks

Revenue Accruals

• Accrue revenue using time and materials, cost reimbursable, and fixed price methods
• Accrue revenue for a single project or range of projects
• Calculate potential revenue by percent spent or percent complete
• Accrue costs in the same period that that revenue is generated
• Review and adjust revenue accrual online

Invoice Generation

• Generate invoices based on defined milestones
• Use flexible invoice line formats to summarize invoice lines by different attributes
• Review and adjust invoices online
• Create Discounts, credit memos and write-offs
• Drill down from invoice to detailed transactions online
• Generate inter-project invoices to share costs and revenue between projects

Billing Analysis

• Create and view billing review reports
• Create and view billing process flow reports
• Invoice Flow Detail and Summary Reports
• Potential Revenue Summary Report
• Project Billing Status Report
• Revenue Flow Detail Report
• Analyze Billing Status in Funding Inquiry

Integration

Oracle Project Billing integrates with other Oracle applications including General Ledger, Payables, Project Contracts, and Receivables, as well with third-party applications through open, standards-based architecture.

What Works Great…

• AutoAccounting generates revenue and invoice accounting entries based on pre-defined rules to hidden from end users. It’s great to have an ability to define separate rules for different accounting functions.

• Integration with Other Applications is definitely a strength. Once you define your rules, integration with Project Costing, General Ledger and Receivables is effortless.

• Inter-Company and Inter-Project Billing is very useful for companies who need it.

• Multi-Currency Support for costing and billing is great.

• Online and Self-Service Inquiries including Funding Inquiry, Review Revenue, Invoice Review, and Project Status Inquiry make it easy to review project billing balances at glance. I noticed the Search button (Flash Light) does not work consistently in all Find windows.

• Potential Labor Revenue Report is a great way to see what labor revenue will be recognized, or needs to be fixed before it can be recognized.

• Billing Extensions provide a great way to extend the functionality of Project Billing. I also like the fact that more and more extensions are becoming the standard billing functionality.

• Revenue Audit Report is very helpful when reconciling project revenue to General Ledger.

• Unbilled Receivable/Unearned Revenue Views are great for reconciling revenue and receivable balances.

… and What Could Work Better

• Agreements Upload It is great to have the new API to upload agreements; but a new WebADI upload integrator would even better.

• Events Upload Same as above, the new API is great, but a WebADI upload integrator is still needed.

• Find Agreements The Agreements form needs a Find window with Customer, Agreement Number, Agreement Type, and Project Number as parameters.

• AutoAccounting is a great feature, but there are also several limitations, which sometimes make it frustrating to use. For example:

• Lookup Sets Having no validation in lookup sets provides great flexibility and maintenance nightmare. Just imagine a business with several hundred to several thousands departments. Imagine you change hundreds department names in HR. Since there is no API, or interface, you have to replicate all name changes in your lookup sets manually. Why not build parameters into lookup sets similar to AutoAccounting Rule parameters? You could store intermediate values based on value IDs, not actual values, so that any changes would be automatically reflected in lookup sets.
• Receivable Accounts could use more parameters to be able to create more robust rules.
• Error Reporting during revenue and invoice generation processes could be better. The “Invalid AutoAccounting” error just doesn’t provide enough detail.

• Billing Rates Upload and Maintenance Bill Rate Schedules could use a new API and WebADI upload integrator. Typically you maintain several hundreds rates in a typical rate schedule (e.g. by job title).
• Revenue Reporting There are no revenue reports (similar to expenditure detail and expenditure summary) by revenue type, revenue category, etc.
• Unbilled Expenses Reporting It would be useful to have a report showing what billable expenses are to be billed (similar to Potential Labor Revenue report).

For more information about Oracle Project Billing, refer to other Oracle resources:

• Oracle Project Billing Data Sheet with an overview and summary of key features.

• Oracle Project Billing User Guide 11i (OTN login is required)

How To Get a List of Project Billing Rates

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

Sometime you may need to get a list of previously used billing rates (e.g. during the last year) in order to determine the future rates. Since there is no standard report in Oracle Project Billing 10i.10 that provides that information, here is a simple query to accomplish the task.

Bill Rates by Job Title Query

How To Find Unbilled Project Expenses

March 14, 2006 on 9:06 am | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a couple of tips on how to find unbilled project expenditures in Oracle Projects (11i.10). There is an excellent online Expenditure Inquiry screen, which lets you submit searches with a whole lot of parameters and define your own folders to export expenditure transactions into Excel.

But if the volume is too big to export, or you need to find unbilled expenditures across broader parameters like Project Type, Project Organization, Customer, or Expenditure Type Classes, use an ad-hoc SQL query. I provided a sample below.

Unbilled Project Expenditures

3 Suggestions to Improve the Percent Complete Form

March 10, 2006 on 4:54 pm | by Marian Crkon | In Ideas and Opinions | Enter Comments |  Print |  Email

It must have been a day off at Oracle when the Percent Complete form (Oracle Projects 10.5.10) was created. It somehow fell through the cracks. It is very, how shall I say, impractical. The form’s main purpose in Oracle Projects is very simple – to enter percent complete amounts for percent complete billing or revenue recognition. Ignoring the current bug, which prevents you from saving the record and proceeding (to be resolved on Metalink), the form shows you how little emphasis was placed on business usability and user friendliness. What is really annoying is the fact that you cannot do even the most basic functions like delete or inactive records enterred in error, and you see values entered previously.

To give Oracle some credit, they provided a new API named UPDATE_PROGRESS, which lets you upload the percent amounts. However, in order to use it, you need to create custom code to upload the amounts.

So here are my three humble suggestions to improve the Percent Complete Entry process:

• Enable an option to delete or end-date percent comlete amounts

• Provide an audit trail of previously entered amounts. An audit report would also be nice.

• Provide a Web ADI Integrator for uploading percent complete amounts directly from Excel.

How To Calculate Revenue Without Actually Generating It

March 9, 2006 on 12:16 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

In Oracle Project Billing (10.5.10), you can calculate forecast labor revenue for your time and material projects without running the actual revenue generation process. Navigate to the Submit Requests window (Other > Requests Run) and submit the PRC: Compute Forecast Labor Revenue process.

The system generates a report that shows revenue forecasts and potential exceptions, when for instance timecards are not costed, or there are missing billing rates, or projects are not funded, etc. Currently, you can run the report by project and date for billable labor only (no expenses).
Enhancement Tip:

Rename the request from PRC to MGT – it is a report, not a program. Also, provide additional parameters like Organization, Customer, or Project Manager. Under the Exception Report, show exception reasons.

How to Bill Project Manager under a Different Billing Title

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

You may be asked this question by your Oracle Project Billing users.

Question:

Can you bill Project Manager under a different billing title?

Answer:

Yes you can. Make sure to distinguish between a Project Manager Role (e.g. Project Manager, Program Manager, etc.) and a Billing Title (determined by employee’s HR assignment). You bill for employee’s time based on Job Title from HR assignment.

If you need to bill for employee’s time with a different title than his HR title, you can override the title in the project definition (Projects, Templates window). Navigate to Projects > Options > Bill Rates and Discount Overrides Option Job Assignment Overrides. In the example below, project manager Michael Johnson will be billed as Designer.

Oracle Projects Invoice Review Report Prints a Blank Page

February 28, 2006 on 1:25 pm | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

You may encounter this feature if using Oracle Project Billing (10.5.10).

The Feature

You are running the MGT: Invoice Review report for draft project invoices before they get approved, released and interfaced to AR. Even though there are draft invoices, they do not show up on the report.

The Fix

It turns out the problem is caused by the project manager being invalid employee in Human Resources. Not exactly the most intuitive way to find out. Assign a valid employee as new project manager, and re-run the MGT: Invoice Review report.

Customer Agreements “Disappear” if Created by Contingent Workers

February 8, 2006 on 12:12 pm | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

You may encounter this feature if you have contingent workers managing your customer agreements in Oracle Projects 10.5.10.

The Feature

If a customer agreement is created by a contingent worker (Person Type = Contingent Worker in HR), it “disappears”. It exists in the agreements table, but you cannot retrieve it in the Agreements window. Also, you cannot generate revenue and invoices for projects funded by such agreements.

The Workaround

Update the agreements table and set the agreement owner to be employee instead of contingent worker. You can use the following update script:

UPDATE pa_agreements_all
SET owned_by_person_id = ‘employee person_id‘
WHERE owned_by_person_id = ‘contingent worker person id‘

Please provide your comments, or contact me at marian.crkon@itsafeature.com if you have any questions.