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.

How the PA Date and GL Date Are Determined in Oracle Projects

July 26, 2006 on 10:57 pm | by Marian Crkon | In How To Guides | 3 Comments |  Print |  Email

You need to know how the Project Accounting (PA) Date and the GL Date are assigned in Oracle Projects in order to understand how your project (P&L) reporting and GL reconciliation work. The dates are determined differently for different project transactions based on each transaction type. Oracle Projects determines the dates as follows:

Timecard, Usage, Miscellaneous, and Supplier Invoice Adjustments

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.

The GL date is set to the end date of the earliest GL period that includes or follows the PA date of the cost distribution line and has a status of Open or Future according to the period status in Oracle General Ledger (or in Oracle Projects when enhanced period processing features are enabled). Oracle Projects derives the GL Date when you run the cost distribution processes.

Expense Reports (created in iExpenses)

The Oracle Payables Invoice Import program uses one GL date for each expense report loaded into Oracle Payables from Oracle iExpenses. All project cost distribution lines for an expense report use the same GL date.The GL date is set to the end date of the earliest GL period that has a status of Open or Future according to the period status in Oracle General Ledger.

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.Oracle Projects derives the PA date for each expense report cost distribution line when you interface the expense reports from Oracle Payables.

Supplier Invoices Interfaced from Payables

The PA date is set to the transaction expenditure item date if that date falls in a PA period with a status of Open or Future. If the expenditure item date falls in a closed PA period, then the PA date is set to the start date of the earliest open or future enterable PA period that follows the expenditure item date.Oracle Projects derives the PA date for each supplier invoice cost distribution line when you interface the supplier invoice from Oracle Payables.

When you interface supplier invoices from Payables, Oracle Projects copies the GL date for each supplier invoice cost distribution line from the GL date entered for the invoice distribution in Oracle Payables. Oracle Projects derives the PA date for each supplier invoice cost distribution line when you interface the supplier invoice from Oracle Payables.

Draft Revenue

The PA date is set to the end date of the earliest PA period that includes or follows the revenue accrue through date and has a status of Open or Future.

The GL date is set to the end date of the earliest GL period that includes or follows the PA date of the draft revenue and has a status of Open or Future according to the period status in Oracle General Ledger (or in Oracle Projects when enhanced period processing features are enabled ). Projects derives accounting dates during the revenue generation process.

Draft Invoices

The PA date is set to the end date of the earliest PA period that includes or follows the invoice date and has a status of Open or Future.

The GL date is set to the end date of the earliest Open or Future Oracle Receivables GL period that includes or follows the invoice date of the draft invoice. The GL Date is derived when the invoice is generated.

Example

Let’s take for example a billable timecard entered in June 2006. And let’s assume the PA Period and GL Period are the same and JUN-06 is open:

• Expenditure Item Date = June 19 through June 23
• Week Ending Date = June 25 (Sunday is the of that week based on the Implementation Options)
• The PRC: Distribute Labor Costs process will assign the expenditure PA Dates and GL Dates the same as Expenditure Item Dates and both the PA Period and GL Period will be set to JUN-06.
• The PRC: Generate Draft Revenue process will assign the revenue PA Date and GL Date based on the Accrue-Through Date selected during the process (e.g. June 30, 2006) and both the PA Period and GL Period will be set to JUN-06.

Now, let’s assume the timecard was transferred between two projects and billed to a client in July 2006 and JUN-06 period was already closed in the PA calendar and GL calendar in Projects. The dates will be derived as follows:

• Expenditure Items Date will stay June 19 through June 23.
• Week Ending Date will stay June 25.
• The PRC: Distribute Labor Costs process will change the expenditure PA Date and GL Date to July 1, 2006, and the PA Period and GL Period will be changed to JUL-06.
• The PRC: Generate Draft Revenue process will change the revenue PA Date and GL Date to July 31, 2006 (based on an Accrue-Through Date selected during the process), and both the PA Period and GL Period will be changed JUL-06.
• Project Invoice that includes the timecard in have the PA Date and GL Date of July 31, 2006 (based on a bill-through date of July 31, 2006) and open AR period JUL-06.

Implementation Tip: In order to correctly report on such adjustments as described above and show all debits and credits (net zero transactions), pull your transaction amounts from the expenditure distributions table, and not the expenditure items table in your reporting data mart.

How To Enable Oracle Project Status Change Workflow

July 18, 2006 on 8:30 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

In Oracle Projects, you can use the Project Status Change Workflow to automatically route project status changes for approval. The Project Status Workflow needs to be enabled for the project status and the project type. When a project status changes, the seeded PA Project Workflow sends an approval notification to the requesting employee’s HR supervisor who will receive a notification (or an email if WF Mailer is enabled) to change the project status. You can customize the PA Project Workflow according to your business requirements. For more details, see the Oracle Projects Implementation Guide (Oracle OTN login required).

Below is a sample workflow of project status changes. A Project Accountant creates a project and manually sets the project status to Submitted. The workflow process is initiated. A Project Accounting Manager (Project Accountant’s HR supervisor) receives an email requesting his/her approval of a new project. If the manager approves and the workflow process is successful, the project status is changed to Approved.

Similarly, when a project needs to be closed, the Project Accountant will manually change the status from Approved to Pending Close. A second workflow process sends an email to the PA Manager, verifies that the manager approved the project closure, and changes the status to Closed.

Following are the required configuration steps to enable the Project Status changes by Workflow:

Enable Project Types for Workflow Project Status Changes

As Project Billing Superuser, navigate to Setup > Projects > Project Types. Define your project type, or update an existing one. Select the ‘Use Workflow for Project Status Changes’ flag to initiate Workflow for all workflow-eligible project statuses in projects with this project type.

Project Statuses for Workflow Status Changes

As Project Billing Superuser, navigate to Setup > System > Statuses. In our example above, we would need to enable following statuses for workflow: Submitted and Pending Close. The value for Item Type is always ‘PA Project Workflow’ for Process is ‘Project’. Using the Success Status and Failure Status is how you control the next status based on the approver’s action.

Few comments in closing. Using the Project Status Change Workflow is great if you create and approve a lot of projects, or project creation is de-centralized. It enforces consistent rules and keeps an audit trail of changes. Having an automatic emails and notifications sent out by the system brings transparency to the process.

Some of the features we did not like included users’ inability to ”withdraw” a project status change. Once you choose a project type that is enabled for workflow, the process is triggered and you cannot “roll it back”. Also, and this applies to any Oracle Workflow notification, if there is anything wrong with your setup, for instance an employee does not have a HR supervisor, or user account is not associated with an employee, or user name changes were not synchronized in the Workflow tables, the approval notifications are routed to SYSADMIN without any acknowledgement to you.

The workflow messages can be little misleading. After a successful status change, the message always says “Project Approved”, even though the status change was to close the project. It would be more meaningful to say: “…project status was successfully changed to ‘actual status name’…”

Favorite Fusion Features – Project Costing

June 9, 2006 on 9:18 pm | by Marian Crkon | In Ideas and Opinions | Enter Comments |  Print |  Email

Which features would you like to become your Favorite Features in Oracle Fusion? If you are an expert user with real-life, hands-on experience and have ideas on how to improve the applications; if you had to customize a module to get the features you need, or additional functionality would save you time and money, submit your strategic requests via the OAUG Enhancement Request System (ERS) today! As an Oracle Applications Users Group (OAUG) member and/or Oracle Applications user, Oracle is requesting your feedback on strategic improvements to current Oracle E-Business Suite functionality for their initial Fusion releases.

Alternatively, if you want to get involved but don’t know how, if you are not an OAUG member, or none of the available options works for you, then let’s also try something different. Let’s keep a weblog of improvement tips, enhancement requests and your best features in Oracle, PeopleSoft, JD Edwards and Siebel applications you want to have in Oracle Fusion. Let’s have discussions about what enhancements and features make most sense, and then log Fusion Requests before the Oracle’s June deadline. There won’t be any wrong questions or bad ideas (or at least, we’ll let the “group intelligence” decide). Your involvement can be as easy as providing comments with your ideas to this post.
Below is a list of improvement tips and enhancement ideas based on Oracle Project Costing 11i.10 as provided by the Feature authors and readers:

• Project and Task WebADI Upload Integrator. Provide Excel integration (WebADI Integrator) for uploading projects and tasks using the existing project and task APIs.

• Project Maintenance. Maintaining projects and their options could be a drag. Yes, there are APIs to create and update projects, tasks and budgets, but in order to use them you have to create your own custom code. Many companies rely on manual project maintenance by their users. Similar to Quick Project Entry function, which is used to quickly set up projects, why not create a Quick Project Update function, which would update them? It would be great, for instance, for updating project dates, task dates and transaction controls dates when the project duration changes. You may also need to change burden schedules, price rule schedules, or any other project or task-level attributes. You get the idea…

• Expanded Project Administration. This one is similar to the one above, but it is more related to the changes to you need to make across several projects. The Project Administration functionality currently allows you to change only Project Owning Organization. It would be great to have better selection criteria like project type, customer, project manager, project classification, etc.to choose what projects need to be transferred from one organization to another. Also, it would be fantastic to be able to change additional projectattributes like Key Members, Customers, or Project Classifications using the Project Administration.

• Improved Project Allocations Rules. Project Allocations is a great feature but it could use a little more flexibility in defining rules on how to create new transactions. Being able to define only one Expenditure Organization and one Expenditure Type per rule makes it very difficult to define flexible accounting rules for allocation transactions. Having a separate AutoAccounting Function for allocations would also help.

• Validated AutoAccounting Lookup Sets. We all use them as mapping tables between project attributes and chart of accounts values. Having no validation in lookup sets gives you great flexibility to map anything you want. However, no validation also creates a maintenance nightmare. Why not build parameters into lookup sets, similar to AutoAccounting Rule parameters? You could store lookup values based on IDs, not values. That way, if or instance, HR organizations change in HR, so would their representations in the lookup sets in Projects.

• Expanded AutoAccounting Functions for Cost Clearing Accounts. The clearing account functions in AutoAccounting could use more parameters in order to create more robust and flexible rules for the cost clearing accounts. Add additional parameters like expenditure type, expenditure category, service type, project classification, etc.

• Better Error Handling for AutoAccounting Errors during Cost Processing. Many of us spend endless hours trying to figure out why costing processes fail due to “Invalid AutoAccounting” errors. Provide more detail AutoAccounting errors in the cost distribution processes.

• Expenditure Item Adjustments. Provide a way to “transfer” expenditures between Expenditure Organizations, and between Expenditure Types (similar to the existing transfer between projects and tasks). It is a major headache, especially with transactions imported into Projects (timecards, expense reports, supplier invoices) to not to be able to change these two parameters.

• Find Expenditure Types Function. It would be great to have a search function in the Expenditure Types form with Expenditure Type, Expenditure Category, Revenue Category, UOM and Expenditure Type Class as search parameters would be very helpful. Also, provide a folder/export function so that we can easily export expenditure types and their attributes. The IMP: Expenditure Type Listing is pretty useless.

• Rate Schedule Upload. Provide Excel integration (Web ADI Upload Integrator) for creating and updating Rate Schedules. It would also be nice to be able to end-date the schedules you no longer need.

Which features would you like to become your favorite features in Oracle Fusion? Granted, it may take two years before you get them, if ever, but this is your opportunity to provide your own improvement ideas and enhancement requests. The voting to determine “best features” starts in July 2006.

Sample Operational Analysis Questionnaires for Oracle Financials and Projects

May 17, 2006 on 9:20 pm | by Marian Crkon | In How To Guides | 21 Comments |  Print |  Email

During an operational analysis phase of an ERP implementation project you need to establish a detail project scope and business requirements. Attached you will find several sample questionnaires that may help you conduct discussions with your business users about how to implement Oracle eBusiness Suite applications. Use the documents as samples to define your own list of questions.

Operational Analysis Questionnaire – Accounts Payable

Operational Analysis Questionnaire – Accounts Receivable

Operational Analysis Questionnaire – Contingent Worker Management

Operational Analysis Questionnaire – Fixed Assets

Operational Analysis Questionnaire – General Ledger

Operational Analysis Questionnaire – Project Accounting

Operational Analysis Questionnaire – Resource Management

Operational Analysis Questionnaire – Time and Expenses

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

Sample DataLoad Templates for Oracle Projects

March 11, 2006 on 5:22 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

I was little harsh on DataLoad in my last DataLoad story so here is to redeem myself. I use DataLoad anytime I have to create or update more than 10 records. Usually, DataLoad is an excellent option when other options like Web ADI are not available, feasible or practical, and the volume of data to upload isn’t too big (under 1,000 records). Last week for example, we had to update a couple of attributes on 200 project tasks. The volume was too small to justify creating a custom script and use the Oracle API, and too big to do it manually. So the DataLoad came to the rescue!

Below are several sample DataLoad templates for Oracle Projects I used before with descriptions and navigation instructions. Note that these are only samples. You may need to modify the spreadsheets based on your application configuration. The blue columns represent DataLoad commands, the yellow columns is where you paste your data. Hope you will find the templates useful.

Create Project Roles

Create Project Roles used in project Key Member assignments.

Navigation: Project Billing Super User: Projects > Setup > Projects > Roles

Start Field: Name

DataLoad: Create Project Roles

Create Project Tasks

Use this DataLoad for creating a small volume of project tasks. I use it all the time when creating Project Templates. Use CREATE_TASK or UPDATE_TASK APIs with a custom script to create larger volumes of tasks.

Navigation: Project Billing Super User: Setup > Project Templates > Tasks

Start Field: Task Number

DataLoad: Create Project Tasks

Create Agreements

Navigation: Project Billing Super User: Billing > Agreements

Start Field: Customer Name

DataLoad: Create Agreements

Baseline Projects in Funding Inquiry

Note that you can baseline funding in the Funding Inquiry screen only if the Baseline Funding Without Budget option is enabled for your projects.

Navigation: Project Billing Super User: Billing > Funding Inquiry.

Start Field: Project

DataLoad: Baseline Project in Funding Inquiry

Create Labor Costing Overrides Define labor rate overrides by employee. These rates are used by the labor distribution process to calculate labor costs.

Navigation: Project Billing Super User: Setup > Costing > Labor > Labor Costing Overrides.

Start Field: Employee Name

DataLoad: Create Labor Costing Overrides

Create Transfer Price Schedules

Define the transfer price schedule lines for your cross-charge rules. Manually define schedule name and description, and start the DataLoad at Line Number field.

Navigation: Project Billing Super User: Setup > Costing > Cross-Charge > Transfer Price Schedules

Start Field: Line Number

DataLoad: Create Transfer Price Schedules

Create Non-Labor Rate Schedule

Define non-labor bill rates and markups used by the revenue and invoice generation processes in Oracle Project Billing. Manually define the schedule header, click on Non-Labor tab, and start at Expenditure Type field.

Navigation: Project Billing Super User: Setup > Expenditures > Rate Schedules

Start Field: Expenditure Type

DataLoad: Create Non-Labor Rate Schedule

Create Project Employee Bill Rate Overrides

Define project specific employee billing rate overrides used by the revenue and invoice generation processes in Oracle Project Billing.

Navigation: Project Billing Super User: Projects > Options > Bill Rates and Discount Overrides > Employee Bill Rate and Discount Overrides

Start Field: Employee Name

DataLoad: Create Project Employee Bill Rate Overrides

Create Project Lookup Set

Define project lookup sets used in AutoAccounting rules or Account Generator. Manually define lookup set Name, and start at the Intermediate Value field.

Navigation: Project Billing Super User: Projects > Setup > AutoAccounting > Lookup Sets

Start Field: Intermediate Value

DataLoad: Create Project Lookup Set

Update Project Lookup Set

Similar to the DataLoad above, but this one updates the existing lookup sets values.
Navigation: Project Billing Super User: Projects > Setup > AutoAccounting > Lookup Sets

Start Field: Intermediate Value

DataLoad: Update Project Lookup Set

Create Contingent Worker Expenses as Pre-Approved Batch

The preferred option for upload project transactions is the Transaction Import WebADI Integrator. However, as of the release 10.5.10, it does not distinguish between Person Types, and does not successfully upload Contingent Worker transactions.

Navigation: Project Billing Super User: Expenses > Pre-Approved Batches > Enter

Start Field: Employee Name

DataLoad: Create Contingent Worker Expenses as Pre-Approved Batch

Create Percent Complete Amounts

Navigation: Project Billing Super User: Project Status Inquiry > Percent Complete

Start Field: Project Number

DataLoad: Create Percent Complete Amounts

Visit DataLoad website for product information, free downloads, user guides and more sample spreadsheets.