The Feature

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

Sample Operational Analysis Questionnaires for Oracle Financials and Projects

May 17, 2006 on 9:20 pm | by Marian Crkon | In How To Guides | 19 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

How to Find Active Employees, Users and Responsibilities

May 17, 2006 on 11:01 am | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is a query you may find useful when maintainig users, doing SOX 404 controls audit, creating new users for existing employees in Oracle eBusiness Applications . Use it to find active employees, contingent workers and their user accounts and responsibilities. You will need to know the person_type_id for employees and contingent workers.

Employees, Users and Responsibilities Query

How to Get the Expense Report Template Details

May 8, 2006 on 10:13 pm | by Marian Crkon | In How To Guides | Enter Comments |  Print |  Email

Here is an ad-hoc query you may use to get the expense report template details from Accounts Payable, including the mapping of expense type names to GL accounts and project expenditure types. As of 11i.10, it is not possible to export this data from the Expense Report Templates form, nor there is any report that would provide this information. It is nice to have this listing in Excel when reviewing the expense types, making chart of account changes, or updating your accounting rules.

Expense Report Template Details Query

Go-Live Checklist for Oracle Financials

April 4, 2006 on 9:30 pm | by Marian Crkon | In How To Guides | 2 Comments |  Print |  Email

Following is a sample Oracle Financials Go-Live Checklist. It should give you an idea for a sequence of events that occur during the migration to Oracle Applications. The list does not include all application configuration steps, only the “eleventh hour” steps to take the system live. The example covers the applications we needed to set up, including Oracle General Ledger, Oracle Payables, Oracle Receivables, Oracle Projects, Oracle Assets, Oracle Time and Labor, Oracle Internet Expenses and Oracle System Administration. This list is by no means a “fit for all” solution. You can use it as reference to create your own checklist.

Complete HR configurations

1.1 HR Complete HR configuration setups

1.2 HR Complete HR configurations and conversions

• Define New Locations and Organizations
• Define the Organizational Hierarchy
• Define Job Titles and Positions
• Create Employees and Employee Assignments

1.3 AR: Get Customers extract from the Legacy System

1.4 AP: Get Suppliers from the Legacy System

Complete Configuration Changes for all Financial Modules: GL, AP, AR, PA, FA, OTL, IE, SA)

2.1 GL: Define Oracle Chart of Accounts with new values

2.2 GL: Define GL cross-validation rules to allow new values

2.3 AR: Define conversion Transaction Types

2.4 AR: Define AR Receipt Source

2.5 AR: Define lockbox

2.6 AP: Add new Supplier Types

2.7 AP: Add new Pay Groups

2.8 AP: Add a Employee Reimbursement Bank Account

2.9 IE: Enable Expenditure Types in Projects for Expense Report entry.

2.10 IE: Create Expense Report Template

2.11 IE: Define Financial Options (in Payables)

2.12 IE: Define Payables Options

2.13 IE: Assign Manager Signing Limits

2.14 IE: Set OIE Profile Options

2.15 IE: Enable the display of projects and task using AK Developer responsibility

2.16 IE: Set up an Offline Expense Spreadsheet

2.17 IE: Save an offline spreadsheet to the Production database

2.18 IE: Build the Project Expense Report Account Generator.

2.19 IE: Save the Project Expense Report Account Generator to Production database.

2.20 GL: Assign a custom workflow to the Project Expense Report Account Generator process

2.21 FA: Define new Asset Categories

2.22 FA : Define new Asset Locations

2.23 PA: Define Projects Implementation Options

2.24 PA: Add new Expenditure Categories

2.25 PA: Add new Revenue Categories

2.26 PA: Add new Expenditure Types

2.27 PA: Define Transaction Source

2.28 PA: Define Project Bill Rate Schedules

2.29 PA: Define Product Code

2.30 PA: Define Labor Costing Rules

2.31 PA: Define Burden Costing Rules

2.32 PA: Define Cross-Charge Rules

2.33 PA: Define new Event Types

2.34 PA: Enable Percent Complete Billing Extensions

2.35 PA: Define Invoice Formats

2.36 PA: Define Resource Lists with new values

2.37 PA: Define Project Classifications

2.38 PA: Define Project Types

2.39 PA: Define Project Templates

2.40 PA: Define Auto Accounting Lookup Sets

2.41 PA: Define Auto Accounting Rules for new transactions

2.42 PA: Define Auto Accounting Assignments

2.43 PA: Create Admin Projects

Perform Data Conversions of Master Data

3.1 AR: Review and clean up customers

3.2 AP: Review and clean up suppliers and contractors

3.3 AR: Upload customers to Oracle Receivables

3.4 AP: Upload suppliers to Oracle Payables

3.5 AR: Review and validate customers and contacts in Oracle Receivables

3.6 AP: Review and validate suppliers in Oracle Payables

3.7 HR: Upload contingent workers to Oracle HR. Associate contingent workers with AP suppliers

3.8 SA: Create users and assign to responsibilities

3.9 AR: Create customer billing contacts

3.10 FA : Run depreciation for Last Period in the Legacy System

3.11 FA : Get Assets file from the Legacy System

3.12 PA: Extract projects from the Legacy System

3.13 PA: Get project funding amounts

3.14 PA: Ger billing rates for T&M projects

3.15 PA: Upload projects to Oracle Projects

3.16 OTL and iExpenses Go-Live

Execute Conversion of Opening Balances

4.1 AR: Get unapplied AR receipts from the Legacy System

4.2 AR: Get pending AP invoices from the Legacy System

4.3 FA: Close Last Period in Oracle Assets

4.4 FA: Upload legacy assets to Oracle Assets

4.5 PA: Complete Last Period Billing in the Legacy System

4.6 PA: Complete Last Period Billing in Oracle

4.7 GL: Close Last Period in the Legacy System GL

4.8 GL: Close Last Period in Oracle General Ledger

4.9 AR: Get open AR transactions from the Legacy System

4.10 AR: Upload open AR transactions to Oracle Receivables. (Make sure you use Transaction Type with ‘Post to GL’ flag set to ‘No’)

4.11 AR: Upload unapplied AR receipts to Oracle Receivables

4.12 AR: Interface open AR cash balances in GL

4.13 AR: Reverse opening AR cash balances in GL

4.14 PA: Get “Billed” project balances from the Legacy System

4.15 PA: Upload project funding amounts to Oracle Projects. Baseline funding and create revenue budgets.

4.16 PA: Upload bill rate schedules to Oracle Projects

4.17 PA: Get “Unbilled” project items from the Legacy System

4.18 PA: Get opening project expenditure balances from the Legacy System

4.19 PA: Get opening project revenue balances from the Legacy System

4.20 PA: Disable the Interface Usage Costs to GL flag in Projects Implementation Options

4.21 PA: Upload opening project expenditure balances as Miscellaneous Expenditures to Oracle Projects

4.22 PA: Distribute Miscellaneous Costs in Oracle Projects. Run the interface usage and miscellaneous costs to GL process. No JE should be created.

4.23 PA: Disable the Interface Revenue to GL flag in Projects Implementation Options

4.24 PA: Disable the ‘Open Receivable’ and ‘Post to GL’ flags for ‘Projects Invoice’ transaction type in Oracle Receivables

4.25 PA: Upload opening project revenue balances as Revenue Events to Oracle Projects (Revenue Amount is populated)

4.26 PA: Generate project revenue in Oracle Projects. Run the GL interface processes. No JE should be created.

4.27 PA: Upload Billed Project Balances as Billing Events to Oracle Projects (Bill Amount is populated)

4.28 PA: Generate draft project invoices. Interface draft invoices to receivables them to Oracle Receivables. No AR transactions should be generated.

4.29 FA Upload assets to Oracle Assets

4.30 AP: Upload pending AP invoices to Oracle Payables

4.31 AP: Interface AP invoices to General Ledger, reverse the JE.

4.32 OA Set the Revenue to General Ledger flag in Projects Implementation Options back to ‘Yes’

4.33 AR: Set the ‘Open Receivable’ and ‘Post to GL’ flags for ‘Projects Invoice’ transaction type in Oracle Receivables to ‘Yes’.

4.34 PA: Upload legacy unbilled timecards as timecards to Oracle Projects

4.35 PA: Distribute legacy labor costs in Oracle Projects

4.36 PA: Upload unbilled expense reports and other costs as Miscellaneous Transactions to Oracle Projects

4.37 PA: Distribute legacy miscellaneous costs transactions in Oracle Projects

4.38 PA: Generate revenue for unbilled legacy transactions

Start Regular Processing in Oracle Production

5.1 PA: Import new timecards from OTL to Projects

5.2 AP: Validate and import from iExpenses to Payables

5.3 PA: Start running the project processes

5.4 AR: Star applying cash in Oracle Receivables

5.5 GL: After First Period is closed in Assets, create a reversing JE in Oracle GL to reverse asset cost and depreciation through Last Period

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

Find Active Employees, Contingent Workers and Users Query

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

Here is a query you may find useful when maintaing users in Oracle. Use it to find active employees, contingent workers and their user accounts in Oracle Applications. You will need to know person_type_id for employees and contingent workers.

Employees and Users Query.zip

GL Date in Expenditure Inquiry is Misleading

March 2, 2006 on 10:40 am | by Marian Crkon | In Feature of the Week | Enter Comments |  Print |  Email

Using the GL Date for reporting and online inquiries in Oracle Projects may be misleading. Oracle Projects generates additional distribution lines to account for any expenditure adjustments, and it assigns the GL Date from based on the current open GL period from the latest distribution line. If you adjusted expenditure items within the same project and task, Oracle Projects only creates reversing distribution line, and no reversing expenditure item.

In the example below, a January timecard line was adjusted in February (with January already closed), and even though the accounting total in February was net zero, the timecard line now shows up in February using the GL Date. If you use GL Date in your reports, this item now shows up in February.

You should use the Expenditure Item Date for utilization reporting and billing in Projects because that’s when the work actually occurred. Use the distribution lines totals, not the expenditure items totals if you use the GL Date in your financial reporting from Projects.

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