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.

Projects, Templates Form Does Not Let You Update More Than 25 Records

June 30, 2010 on 9:57 am | by Marian Crkon | In Feature of the Week | 3 Comments |  Print |  Email

You can encounter this feature in Oracle Projects 11.5.10, form PAXPREPR, version 11.5.640.

Background

I am building up a project template and using DataLoad to create project and task options like project assets, task-level transaction  controls, or task-level asset assignments.  There are several hundreds of these to upload, so DataLoad seems a perfect method.

The Feature

For some reason, however, and I am not even able to properly articulate this, I am unable to upload more than about 25 records at one time. When it reaches some limit, the form simply freezes up, and you have to shut down the browser and re-open your Oracle Applications session.

Has any of you experienced this? What is causing it? I do not believe it is caused by some DataLoad limitation, I’ve used it to upload hundreds and thousands of records in “one run”.

Any ideas will be greatly appreciated. This is truly driving me insane.

Recording Project-Related Receipt Accruals in Oracle Projects

November 25, 2008 on 9:27 pm | by Marian Crkon | In Feature of the Week | 3 Comments |  Print |  Email

This feature is for anyone using Oracle Purchasing, Payables and Projects 11i and beyond. I’ve been asked today to provide a solution for recording project-related receipt accruals in Oracle, and was very pleased when I found out this was a standard functionality. A summary of my research [from four different Oracle user guides] is available below.

Recording Project-Related Receipt Accruals

Oracle Purchasing records the received goods as delivered for your project when they are delivered and assigned to a project-related purchase order distribution line. When a purchase order line is flagged to accrue on receipt and the purchased goods are delivered to an expense destination, you can interface receipt accrual accounting entries from Oracle Purchasing to Oracle Projects as actual expenditure transactions. This allows you to recognize the cost to your project in the period in which it is incurred rather than in the period in which it is invoiced.

Interfacing Receipt Accruals to Projects

To interface receipt accruals from Oracle Purchasing to Oracle Projects, use the PRC: Interface Supplier Costs process in Oracle Projects. The process selects transactions based on the project-related values entered on PO distribution lines.

The process first retrieves all eligible accounted, project-related receipt accrual information, supplier invoice distributions (including non-recoverable tax lines) and all payment discounts that are distributed to project-related distributions. The process then interfaces the amounts to Oracle Projects.

The interfaced items are grouped into expenditure batches as follows:

• All invoice distributions, excluding non-recoverable tax lines, are included in one batch.
• Non-recoverable tax lines are grouped into a second batch
• Payment Discounts are included in a third batch, and
• Receipt accruals for project related items with a destination type of Expenses are grouped in the fourth batch.

Each time you run Interface Supplier Costs process, Oracle Projects generates reports you can use to track the interfaced supplier invoices distribution lines, receipt accruals, as well as those invoice lines and receipt accruals that are rejected during interface from Oracle Payables and Oracle Purchasing, respectively. The following reports show you the results of this process. These reports show amounts in the functional and transaction (AP invoice) currencies.

• The Interface Project Costs from Payables Report. Lists all invoice distribution lines and receipt accrual transactions that were successfully interfaced to Oracle Projects. In addition, summary information is provided to display the total number and total costs of the interfaced transactions.
• The Interface Project Costs from Payables Exception Report. Lists all invoice distribution lines and receipt accrual transactions that failed to interface to Oracle Projects during the process. For each transaction that fails to transfer, output reports list the rejection reason.

Submitting the Interface Supplier Costs Process

The PRC: Interface Supplier Costs process retrieves the following items and interfaces them from Oracle Purchasing and Oracle Payables to Oracle Projects:

• All eligible accounted, project-related supplier invoice distributions
• All eligible receipt accrual information from Oracle Purchasing
• Tax lines for project-related intercompany invoices
• All eligible accounted payment discounts that are distributed to project-related invoice distributions

The process first populates the Transaction Import Interface table, creating an expenditure item and cost distribution line for each invoice or payment distribution line, and an expenditure for each invoice. This process also checks for original items being adjusted when processing adjusting items from Oracle Payables, to ensure that every negative expenditure item adjusts a valid original expenditure item. If an original matching item is found, the process next checks to ensure that the original item is not already adjusted to have a net zero amount.

Select ‘Yes’ or ‘Accrued Cost Only’ in the Interface Supplier Invoices parameter if you want to interface supplier invoices. When this parameter is set to Yes or Accrued Cost Only, project–related supplier invoice costs are interfaced from Oracle Payables to Oracle Projects.

If an invoice distribution is matched to a purchase order line that is flagged to accrue on receipt and the receipt accrual information is interfaced to Projects, only additional invoice amounts are interfaced. These amounts can include tax or freight charges added during invoice entry, or price and exchange rate variances.

If Accrued Cost Only is selected, only additional invoice amounts are interfaced even when the receipt accrual information is not interfaced to projects. But if an invoice distribution is matched to a purchase order line that is flagged to accrue on receipt and fully interfaced to Projects, then for any invoice distribution matched to this purchase order line, the whole invoice amount will be interfaced to projects irrespective of the selected option.

Similarly, if an invoice distribution is matched to a purchase order line that is flagged to accrue on receipt and for which only additional amounts have been interfaced to projects, then for any invoice distribution matched to this purchase order line, only additional invoice amounts will be interfaced to projects irrespective of the selected option.

Select ‘Yes’ in the Interface Receipt Accruals parameter to interface receipt accruals. When this parameter is set to Yes, receipt accruals for project-related items with a destination type of Expense are interfaced from Oracle Purchasing to Oracle Projects.

Note: Receipt accrual entries are not interfaced if the received items are invoiced and the invoice amounts are interfaced to Projects.

Receipt Accrual Adjustments

You cannot adjust the expenditure item in Oracle Projects when the item is interfaced from Oracle Purchasing (i.e. receipt accrual).

If the invoice is matched to an accrue on receipt purchase order line and the invoice line (rather than the purchase order receipt) is interfaced, then the invoice line can be adjusted in Oracle Projects.

After you have made adjustments to supplier invoice items, you must send the adjustment information back to Oracle Payables so the Payables distribution lines match what is recorded in Oracle Projects. Oracle Payables will interface adjustments that affect the GL account number to Oracle General Ledger. You run the following processes in Oracle Projects for supplier invoice adjustments:

• PRC: Distribute Supplier Invoice Adjustment Costs
• PRC: Interface Supplier Invoice Adjustment Costs to Payables

If you need to change the invoice amount, supplier, or expenditure type, organization, or item date for a supplier invoice line, reverse the line and create a new line in Oracle Payables.

Troubleshooting the Projects to Receivables Interface

November 17, 2008 on 9:10 pm | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

This feature is for anyone using Oracle Projects and Receivables 11.5.10 and beyond. There were several new features introduced to Projects 10.5.10 (Family Pack M), which now require additional [not quite documented] configurations for the Receivables interface to work. I find myself scrambling to make the interface work everytime I run it the first time in a new installation or a new operating unit. Here are few quick tipts I consolidated from the Metalink notes and user guides to remember what to do the next time…

Open Periods

Make sure to open respective accounting periods in Receivables. As Receivables Manager, navigate to Control > Accounting > Open/Close Periods.

Retention Error

You may not be using retention or multi-currency in Project Billing, but you still receive the “Transaction Code: TRA OU Level Retention Accounting flag N… Some transactions are disabled. Please Check auto-accounting setup” error when running the PRC: Interface Invoices to Receivables program (PATTAR).

To resolve the error you need to enable the AutoAccounting Assignments for Unbilled Retention Account and Rounding Account.

As Projects Billing Super User:

• Navigate to Setup > AutoAccounting > Assign
• Query up function ‘Revenue and Invoice Accounts’
• Define Segment Rule Pairings for the Unbilled Retention Account
• Define Segment Rule Pairings for the Rounding Account
• Make sure to enable the Function Transactions!

Again, you need to do this even if you do not intend to enable retention and multi-currency billing in your Implementation Options.

Sales Credit Type Rejection

You might get a ‘No sales credit type at Implementation or Projec Level’ rejection when running the PRC: Interface Invoices to Receivables program (PATTAR).

In order to resolve this error as Projects Billing Super User:

• Navigate to Implementation Options
• Select ‘Exchange Rate Type’ in the Currency tab
• Select ‘Sales Credit Type’ in the Billing tab

Transaction Source and Transaction Type Errors

The seeded Project Transaction Source and Project Transaction Types might be incomplete. Make sure to review and update these for your setting you defined in the Implementation Options > Billing tab. The source It is going to be ‘PROJECTS INVOICES’ (if new 11i or R12 implementation, or ‘PA INVOICES’ if upgraded from 10.7) and Transaction Types are going to be ‘Projects Invoice’ and ‘Projects Credit Memo’ (11i and beyond) or ‘PA Invoice’ and ‘PA Credit Memo’ (10.7) respectively.

As Receivables Manager:

• Navigate to Transaction Sources: Setup > Transactions > Sources
• Query up your Transactions Source you defined in the Implementation Options > Billing tab
• Make sure the Reference Field Default Value field = ‘interface_header_attribute1′
• Make sure the Standard Transaction Type = ‘Projects Invoice’
• Make sure the Credit Meno Batch Source = ‘PROJECTS INVOICES’
• Also navigate to Transaction Types: Setup > Transactions > Types
• Query up ‘Projects Invoice’ transaction type
• Make sure the Credit memo Type = ‘Projects Credit Memo’

Interview with Randy Egger, the Father of Oracle Projects

February 4, 2008 on 2:06 pm | by Marian Crkon | In Conversations | 2 Comments |  Print |  Email

Let me introduce Mr. Randy Egger, one of the original creators of Oracle Projects, and the president and CEO of Projects Partners. We spoke over the phone about his company and its role in the Oracle ecosystem. Below are few excerpts from our conversation.

How did you end up an Oracle professional?

Back in the early 80′s, I was involved with the selection of a relational database that my firm could use in conjunction with application development. We selected Oracle and at that time the RDMBs level was at V3. I had studied and taken classes from CJ Date and Ted Cod.

When you were used to coding in assembly language using a b-tree structure for data management, Oracle was a godsend! I joined Oracle in 1986 (employee number 2048!), and was part of the 50 person professional services group. After one year of that, I ran the IT group for Sales when it turned a billion dollars, and then ultimately landed in Application Development where I spearheaded Project Accounting [now Oracle Projects] development.

Ten and a half years ago, I started Project Partners where we specialize in the implementation of Oracle Projects as well as provide supplemental products to expand its usage.

What are your current role and responsibilities?

I am a president and CEO of Project Partners LLC.

Do you get to do a lot of selling?

I am involved in ALL areas of the business.

Tell us about the beginnings of Project Partners. When did you know the firm “has made it”?

I started the firm out of my house 10+ years ago. Since I was the Product Director for Oracle Projects, I knew that the marketplace needed quality consulting services for Project centric firms. I also knew that Oracle Projects could use some extra features that would facilitate its usage. Knowing that EVERY company is a project company, and most companies just didn’t know it yet, motivated me into this market. I never ponder on whether this company ‘has made it’ or not.

So it is journey, not a destination.

Exactly. We simply put our best foot forward and work very hard at making companies successful with their usage of Oracle Projects. Success breeds success. We now have worked with close to 300 clients and the list is growing very rapidly. We work with about an average of 30-40 clients at any point in time. We have a wealth of information at our disposal.

I assume you speak with many Oracle customers. What do you see as their main concerns and needs these days?

A lot of companies are adopting a Project Management approach to running projects but have little practical experience with the discipline. We are helping companies to formally become project-centric, i.e. help with establishing a PMO office, processes, etc.

Another big concern surrounds usability. Oracle Projects is VERY feature rich and most business problems faced by project centric firms can be solved using the software. The problem is simply with the amount of steps, screens, and processes it takes to accomplish a specific business need. We are trying to help people to simplify the product to fit their business needs. It is about taking a complex product with many features and making it easy to use.

User experience is an issue with Oracle applications. Specific industries have specific terminology and processes. Software built around specific industries were successful and well adopted by the users.

I also believe, from a user experience perspective, it hurt Oracle to be anti-Microsoft back in the 90s.

Yes, if Microsoft did something, Oracle had to do the opposite.

And companies that adopted Microsoft standards became standards; like SAP for instance. The [Oracle] applications look and feel different and it takes time for new and old users to use them properly.

But it changed with the acquisitions. They made Oracle much better in the application space….

What kind of products and services does Project Partners offer? What distinguishes you from other consulting firms?

We offer EXPERT Oracle Projects consultants that have lots of industry experience. Since our company focus is in Projects, we work collaboratively to insure that all of our clients can benefit from our collective knowledge. We are NOT a body shop … the company cares about the success of our Oracle Projects implementations. Ninety nine percent of our clients can be referenced.

We also develop products that help serve the Oracle Projects community. We current have 4 products that satisfy Projects needs:

• OP3 – Integration with Primavera Project Schedules
• PIC Reporting – A comprehensive Project based reporting tool
• PIC Invoicing – Simplifies invoice formats as well as Invoice Consolidation
• PIC UI – A simplified user interface to Oracle Projects and other modules using spreadsheets

A lot of people still do not understand the integration between Oracle Projects and Primavera. They think they can do it themselves then find it more complex than it seems. We put a lot of time and effort into the integration. And we are doing a lot of work getting ready for the next generation Oracle Applications.

PIC Reporting and Invoicing are our best known secrets.

People assume it is in the core product.

When people buy Oracle E-Business Suite applications, they get an almost complete solution. Partner solutions are acquired by clients once they know enough about the core products to identify those areas in which gaps remain between their business needs and the core software. The Oracle ecosystem is a healthy one, in which Oracle delivers more than 80% of the fit, and partners deliver the rest.

PIC User Interface is our new product that will be formally launched later this year. We are taking Microsoft Excel and integrating it with Oracle [Project Management and Resource Management]. There will be workbooks working two-way between Excel and Oracle forms. Using PIC User Interface we consolidated 12 Oracle screens into one. This will revolutionalize the industry. We are getting some tremendous feedback. We have focused on two industries so far – Professional Services organizations and Engineering and Construction…

As an Oracle partner, you are filling gaps in Oracle’s product offering. Let’s take PIC with its reporting and invoicing enhancements, for example. Why is it that Oracle can’t deliver this fundamental functionality in its core products?

I believe that a lot the issues will be resolved within Fusion. Oracle Projects is a VERY complex application that crosses many industries. A project (or job) is a cultural thing to many companies. Project Managers have their own way of managing a project, and in this, they closely mirror artists. It is that creative power that can make or break a project (or project manager). You need tools that can help this process. You can have many many different types of projects within one organization and yet another set of project types in another organization within the same company. This is a tough problem to solve when you want one consolidated view that shows the health of a company that is made up of projects.

Which Oracle releases are your products currently compatible with?

We are compatible with most releases, specifically 11, 11i, 12.

How are you getting ready for Fusion?

We are currently studying the underlying technology. Our next generation Project Management integration software will be based on Fusion technology.

What is the best way for users to become familiar with your products?

By going to our website, attending the Oracle OpenWorld and OAUG COLLABORATE conferences, and requesting real-time demos. We also plan to offer webinars.

I guess that’s one of the reasons we are talking today.

Absolutely.

What do people not know about Projects Partners but should?

We operate globally; we now have operations in India and plan to expand into the Middle East and Europe. We have experts in technology as well as the complex FP-M data model. Our core competency is ORACLE PROJECTS (both functionally and technically). We manage full eBusiness Suite implementations for project centric companies. We handle small, medium, and large implementations.

Our best known secrets are probably PIC Invoicing and PIC UI. We believe that PIC UI will make Oracle Projects implementations VERY successful, especially for those companies that are trying to “field enable” Projects.

A couple of your associates recently started a company blog. Why?

Blogs will serve as an easier way of getting informal information out to our user base faster. It is a part of our knowledge transfer. One of our goals is to educate current and potential users on the benefits and the best ways to use the Oracle Projects products. Everything we do is geared towards this goal – including the Blog, access to our experts for quick questions, meeting with people formally and informally at conferences, etc… It’s this desire to share our knowledge that led us to sponsor the Center for Projects Excellence at the OAUG annual conferences. We staff the conference booth with our very experienced consultants, so that customers can come by and ask us their toughest questions, and get answers at no cost. We enjoy the open question and answer exchange – it’s one of the ways we prove that we are indeed The Experts in Oracle Projects!

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 Use Oracle Account Generator for Project-Related Transactions

April 4, 2007 on 6:06 pm | by Marian Crkon | In How To Guides | 6 Comments |  Print |  Email

They say that if you know flexfields, you know Oracle Financials. I dare to say that if you know AutoAccouting and Account Generators, you understand Oracle Projects. For the lucky ones out there who are faced with an opportunity to set up these tools, here are some insights and experiences I’d like to share…

Check out this white paper I prepared for COLLABORATE07, which explains how to use the seeded Project Account Generators. Oracle provides these generators for you to start building your own processes for project-related transactions. It explains how Oracle initiates the Account Generators, what rules to follow when customizing them, and how to assign your customized processes to your accounting transactions. It also provides a detail step-by-step example of customizing a selected project-related transaction. The paper assumes that the reader is familiar with Oracle Financials and Projects accounting concepts and terminology and a working knowledge of Oracle Workflow Builder tool. Hope you find this information useful.

Account Generators are workflows that provide selected Oracle Applications the ability and flexibility to automatically generate accounting flexfield combinations using your own business rules. Some Oracle Account Generators come pre-seeded in the applications with pre-built rules. However, all Project Account Generators for project-related transactions must be always customized. Automatic generation of account combinations based on project-related information entered by users improves the accuracy and ease of data entry. When the business requirements change and new needs arise, Project Account Generator processes will have to be modified.

Using Oracle Projects? Check Out Project Information Center

January 24, 2007 on 8:55 pm | by Marian Crkon | In Worth Noting | Enter Comments |  Print |  Email

If you are an Oracle Projects customer, check out a couple of interesting products from Projects Partners branded as Project Information Center. There are some great features for Project Billing and general project reporting, and it makes you wonder why this functionality hasn’t yet become part of the standard Oracle Projects functionality…

Project Information Center (PIC) is an easy-to-use, web-based composite application for project-centric businesses. PIC extends the functionality of Oracle E-Business Suite Applications. As a server-based product with a browser interface, enterprise-wide roll-out of PIC is simple and total cost of ownership is low.

• PIC Invoicing delivers custom invoice formats integrated with Oracle Project Billing, Oracle Receivables and Oracle XML Publisher, to enable you to bill your customers in their preferred form.

• PIC Reporting provides complex reporting that spans multiple projects and operating units, and detailed project and task reporting. More significantly, PIC’s Financial Statement Generator easily creates complex financial reports, like Profit and Loss Statements, at various levels of flexible hierarchies, across heterogeneous systems.

Project Information Center (PIC)â„¢ Invoicing

Project Information Center Invoicing (PIC Invoicing) is a wizard-driven invoice formatting solution for project-centric businesses. This add-on solution is fully integrated with Oracle Project Billing, Oracle Receivables and Oracle XML Publisher, and provides a new approach for creating invoice formats for Oracle Project Billing users. Business Users can use desktop word processing tools to create invoice layout and formats. The PIC wizard provides predefined data sources that retrieve summary and detailed information from the database – users don’t need to understand Oracle Projects, Receivables and HR tables.

Using PIC Invoicing, you can define multiple groups like labor, non-labor and vendor invoices within an invoice with separate grouping and sorting criteria for each group. You can use one or more fields like expenditure category, revenue category, tasks, or user defined elements like flexfields for grouping and sorting each section. Invoice lines can be displayed in a user-defined order, e.g. display all labor, followed by burden amounts applied to labor type only, then non-labor items and their burden amounts, and then common burden amounts. You can override certain fields or add free-format text. This flexibility is balanced by controls to specify which fields can be overridden.

You can consolidate multiple invoices by master project, customer, ACRN or user defined parameter. You can create industry, or customer-specific invoice formats like AIA billing formats for E&C, T&M and fixed price contracts for PSA, government forms such as SF1034, SF1035 and DD250 formats. PIC Invoicing provides pre-built templates for several standard forms. You can also create invoice packages that include a summary form, detailed forms and itemized back-ups such as labor and other direct costs. PIC Invoicing will produce the final invoices for presentation to your customer in PDF, Excel, text and HTML formats, and invoices can be emailed based upon business rules.

Project Information Center (PIC)â„¢ Reporting

Project Information Center Reporting (PIC Reporting) is an easy-to-use, tailorable reporting and information delivery solution for project-centric businesses. PIC Reporting provides complex reporting that spans multiple projects and operating units and detailed project and task reporting. More significantly, PIC’s Financial Statement Generator easily creates complex financial reports, like Profit and Loss Statements, at various levels of flexible hierarchies. PIC seamlessly integrates with Oracle Applications and this high-performance web-based solution is very intuitive to use. With minimal training, users can access all the information necessary to support their business role, including financial, project status, aging, bookings and backlog. These high-resolution reports can be saved in whole or in part, and are presented in XML, PDF, text or Excel format.

A single PIC report can integrate and present information from multiple sources, including packaged applications like Oracle, legacy apps, data warehouses, XML and ODBC data sources, desktop applications and flat files. PIC provides native connectivity to popular databases like Oracle, SQL Server, Sybase and Progress, in addition to ODBC/JDBC connectivity. The PIC server also supports access to Enterprise Java Bean, CORBA, COM or C++ objects. PIC has a generic reporting infrastructure based on pl/sql procedures, views, and the PIC metadata model. PIC’s middleware insulates end user reports from underlying data model changes in the Oracle Applications.

For more information about Project Information Center products, refer to the PIC Invoicing Fact Sheet and PIC Reporting Fact Sheet.

Source: Project Partners website.

How to Reclass Projects and Tasks En Masse

December 20, 2006 on 9:20 pm | by Marian Crkon | In Feature of the Week, How To Guides | 1 Comment |  Print |  Email

Darn it. You manually set up your projects and you found out some attributes were configured incorrectly. Now what? There is good news. You can use the Project Administration feature (Oracle Projects 11i) to update Project and Task Organizations in one, easy update.

The way it works is that you create an ‘update batch’ of changes you want to apply. As Project Billing Super User [or Costing Super User], navigate to Project Administration > Mass Update Batches and generate a mass update batch:

• Enter a Batch Name, Description, and Effective Date for the batch.
• In the Generate Detail Lines region of the window, enter the selection criteria to select the projects and tasks you want to update.
• Choose Generate Detail Lines to generate the mass update batch lines.
• Review and/or revise the mass update batch by choosing Details.
• When ready to execute the mass update, Submit the batch. The system submits a concurrent request, which completes the update.

The system will also update all related burdening and cross-charge schedules and take the new organizations into account after the change.

Enhancement Tip

Unfortunately, the Mass Update functionality is only available to update Project and Task Organizations at this point (11i). It would be great if additional attributes were available, including Start Date and Completion Date, Key Members, Project Classifications, Service Type, etc. Judging by the fact that the ‘Attribute’ field has a drop-down menu, that was probably an original intention, but it was never implemented.

Also, the batch control functions are currently not arranged in any logical order and were confusing to the users. Arranging the functions left-to-right, or right-to-left according to follow their logical work flow would make more sense.

Sample Ad-Hoc SQL Queries for Oracle Projects

October 18, 2006 on 9:33 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks with the Oracle Projects data. I am by no means a SQL guru, these are simple queries that provided me with information I needed at different occasions. I am posting the queries here to find them when I need them for my next assignment and share them with those of you who may find them useful.

Labor Billing Rates

Find labor bill rates used to calculate T&M labor revenue for the existing labor transactions (timecards).

Billing Rates by Job

Billable Expenditure Items by Customer

Find billable expenditure items by customer. The Expenditure Inquiry screen provides a great way to search and export expenditures, however, it lacks a search by customer.

Billed Expenses by Customer

Create OTL Tasks View

This is a sample script with an example of how to modify the list of available tasks in OTL based on Service Type.

Create OTL PA_ONLINE_TASKS_V View

Fixed Price Projects without Activity

We use the percent complete revenue recognition method for fixed price projects. We rely on the percent complete amounts to trigger the revenue accrual generation each month. This query provides revenue managers with the status of activity in the fixed price projects to determine whether the revenue should be generated, or projects be closed.

Fixed Price Projects without Activity

Future-Dated OTL Timecards

Find timecards entered into future periods.

Future-Dated Timecards

Future-Dated Expense Report Items

Find project-related distribution items in AP with Expenditure Item Dates in the future. Most expense policies do not allow submitting expenses for reimbursement until they actually occur. However, iExpenses [OIE 11i.10] lets users enter, approve and pay future-dated expense items, and they get rejected during the Interface Expense Reports from Payables process. This query should help you identify such items and extract them into a user-friendly file.

Future-Dated Expense Report Items

Project Agreements and Funding

Find existing project agreements and funding allocations.

Project Agreements and Funding

Project Agreements Created by Contingent Workers

Find all project agreements created by contractors (contingent workers). There was a bug in Oracle Projects 11i.10, which would cause agreements not to show if created by contingent workers.

Project Agreements Owned by Contingent Workers

Project Configuration Details

Find projects with their configuration details including the owning organization, status, customer, key members, etc.

Project Configuration Details

Project Cross-Charge Transaction Details

Find project transactions cross-charged between different providing and receiving organizations.

Project Cross-Charge Transactions

Project Customers

Find customers and contacts used on the existing contract projects.

Project Customers

Project Invoices

Find project invoices by status, project type, etc.

Project Invoices

Project Percent Complete Amounts

Find project percent complete amounts. Also, get a list of projects with percent complete amounts but no revenue generated.

Project Percent Complete

Project Revenue Events

Find project billing and revenue events.

Project Revenue Events

Project Key Members

Find projects with specific key members, e.g. those who need to be replaced by new ones.

Projects with Key Member to Replace

Update Transaction Import Status

Update the Transaction Status in the Projects Transactions Interface table. When importing external project transactions, they may get rejected and you have to set them to ‘Pending’ status in order to make them eligible for next import.

Set Transaction Import Status

Unbille Project Expenditures

Find billable project expenditures, which have not been billed yet. Surprisingly, there is no standard report showing this information (11i.10).

Unbilled Project Expenditures

Uninterfaced Project Expenditure Adjustmemts

Find more information expenditure distribution lines, which show up as Projects exceptions when trying to close the Projects GL and PA periods.

Uninterfaced Project Expenditure Adjustments